Notes

IF EXISTS (SELECT TOP 1 1 FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[GENERATEFLIGHTDAILY]') AND OBJECTPROPERTY(ID, N'IsProcedure') = 1)
DROP PROCEDURE [DBO].[GENERATEFLIGHTDAILY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



CREATE PROCEDURE [dbo].[GenerateFlightDaily]
@FlightSeasonId int,
@UserId varchar(20)

As
SET NOCOUNT ON;
--BEGIN TRANSACTION

-- khai bao bien
DECLARE @IsGenerated AS BIT, @FlightNo VARCHAR(10), @ArrDep VARCHAR(1)
DECLARE @LinkedFlightSeasonId AS INT, @LinkedFlightId AS INT
Declare @ST1 As Varchar(5), @ST2 As Varchar(5), @CompareTime as int
Declare @DayGap As Int, @Season varchar(3)
DECLARE @LinkedFlightNo AS VARCHAR(10)
DECLARE @MinDate AS DATETIME, @MaxDate AS DATETIME, @DateAfter AS varchar(5)
DECLARE @BaseStation VARCHAR(3)
Declare @OperationDate1 varchar(7), @OperationDate2 varchar(7)
Declare @FirstOperationDate1 int, @FirstOperationDate2 int
DECLARE @Type VARCHAR(10), @Type2 VARCHAR(10)
DECLARE @Carrier VARCHAR(2)

-- lay thong tin lich bay mua
SELECT @FlightNo = FlightNo, @IsGenerated = Generated, @ArrDep = ArrDep,
@LinkedFlightSeasonId = LinkedFlight, @ST1 = [Time], @Season = Season,
@MinDate = FirstDate, @MaxDate = LastDate, @DateAfter = [Time],
@BaseStation = ISNULL([BaseStation],'SGN'),
@OperationDate1 = OperationDate,
@Type = ISNULL([Type],''),
@Carrier = LEFT(FlightNo,2)
FROM FlightSeason WITH(NOLOCK)
WHERE FlightSeasonId = @FlightSeasonId

if right(@ST1,1) = '+'
set @MaxDate = dateadd(day, 1, @MaxDate)

-- lay thong tin chuyen link
SELECT @LinkedFlightSeasonId = FlightSeasonId,
@ST2 = [Time] ,
@LinkedFlightNo = FlightNo,
@OperationDate2 = OperationDate,
@Type2 = ISNULL([Type],'')
FROM FlightSeason WITH(NOLOCK)
WHERE FlightSeasonId = @LinkedFlightSeasonId

-- lay tat ca lich mua chuyen bay co cung flightno trong mua
select * into #tfseason from flightseason
where season = @season and status <> 'xxx' and flightno = @flightno

DECLARE @TempDate AS DATETIME, @DayWeek AS INT, @LinkedFlightDate AS DATETIME
DECLARE @Count AS INT

-- Min-Max date of flightseason
--if (@MinDate < GetDate())
--begin
-- Set @MinDate = cast(month(GetDate()) as varchar(2)) + '/' + cast(day(getdate()) as varchar(2)) + '/' + cast(year(getdate()) as varchar(4))
--end

If len(@DateAfter)>0 Set @DateAfter = Right(@DateAfter, 1)
SET @TempDate = @MinDate

Create Table #tempAllDate(fDate datetime)

-- lay cac ngay tu MinDate den MaxDate
-- MinDate la ngay bat dau NHO nhat cua cac lich mua co cung flightno hoac la ngay hien tai
-- MaxDate la ngay ket thuc LON nhat cua cac lich mua co cung flightno
WHILE (@TempDate <= @MaxDate)
BEGIN
insert into #tempAllDate (fDate) values (@TempDate)
SET @TempDate = DATEADD(DAY, 1, @TempDate)
END

-- lay cac ngay ma chuyen bay co hoat dong
create table #tempdate (fdate datetime, flightseasonid int)
if right(@st1,1) <> '+'
begin
insert into #tempdate (fdate, flightseasonid)
select a.*, b.FlightseasonId
from #tempAllDate a, #tfseason b
Where (a.fDate between b.firstdate and b.lastdate and b.operationdate like '%'+rtrim(ltrim(str(datepart(dw, a.fdate))))+'%' and b.[time] not like '%+')

end
else
begin
insert into #tempdate (fdate, flightseasonid)
select fdate, b.FlightseasonId
from #tempAllDate a, #tfseason b
Where
fdate between b.firstdate and b.lastdate and b.operationdate like '%'+rtrim(ltrim(str(datepart(dw, dateadd(day, 1, a.fdate)))))+'%'

end

drop table #tempAllDate


print 'update nhung chuyen > now ' + convert( varchar(30), getdate(), 13)
-- update nhung chuyen > now
Update Flight
Set
Flight.ACType = FlightSeason.ACType,
Flight.Route = FlightSeason.Route,
Flight.Codeshare = FlightSeason.Codeshare,
--Flight.ScheduledTime = case when [Time]>'0050' then left([Time],4) else [Time] end,
--Flight.ScheduledTime = case when right([Time],1) <> '+' then left([Time],4) else [Time] end,
Flight.ScheduledTime = [Time],
Flight.FlightType = FlightSeason.FlightType,
Flight.NatureOfFlight = '---',
Flight.Remarks = FlightSeason.Remarks,
Flight.LastModifiedBy = @UserId,
Flight.LastModifiedDate = GetDate(),
Flight.BaseStation = @BaseStation,
--Flight.FlightDateShow = case when [Time]>'0050' then fDate else dateadd(day, -1, fDate) END,
Flight.FlightDate = case when right([Time],1) <> '+' then fDate else dateadd(day, 1, fDate) end,
Flight.FlightSeasonType = ISNULL(FlightSeason.Type ,'')
From Flight, #tempDate, FlightSeason
Where #tempDate.FlightSeasonId = @FlightSeasonId
And Flight.FlightNo = @FlightNo
And Flight.FlightDate between @MinDate and @MaxDate -- them vao de loc cho nhanh
And Flight.FlightDateshow = fDate
And Flight.Status <> 'XXX'
And Flight.FlightDate > GetDate()
And Flight.ArrDep = FlightSeason.ArrDep
And FlightSeason.FlightSeasonId = @FlightSeasonId

-- SELECT * FROM #tempDate
-- RETURN;

-- insert nhung chuyen chua co
print 'insert nhung chuyen chua co ' + convert( varchar(30), getdate(), 13)
INSERT Flight(FlightNo, LinkedFlight, ArrDep, FlightDate, ACRegNo, ACType, Route, Codeshare, ScheduledTime, FlightType, NatureOfFlight, Status, Remarks, Source, ExtraInfo, LastModifiedBy, LastModifiedDate, BaseStation, FlightDateShow, FlightSeasonType )
SELECT FlightNo, 0 as LinkedFlight, ArrDep,
case when right([time],1)='+' then dateadd(day, 1, fDate) else fDate end as FlightDate,
ACRegNo, ACTyPe, Route, Codeshare, [Time],
FlightType, '---', 'OPN' as Status, '' as Remarks,
'SEA' as source,
ltrim(rtrim(str(@FlightSeasonId))) as extrainfo,
@UserId as lastmodifiedby,
GETDATE() as lastmodifieddate,
@BaseStation,
fDate as FlightDateShow,
ISNULL(Type,'')
FROM FlightSeason, #tempDate
WHERE FlightSeason.FlightSeasonId = @FlightSeasonId
And FlightSeason.FlightSeasonId = #tempDate.FlightSeasonId
And fDate not in (select FlightDateShow From Flight Where Status<>'XXX' And FlightNo = @FlightNo And ArrDep = @ArrDep AND FlightDateShow IS NOT NULL)


Set @FirstOperationDate1 = 0
Set @FirstOperationDate2 = 0
set @OperationDate1 = replace(@OperationDate1, '_', '')
set @OperationDate2 = replace(@OperationDate2, '_', '')

If len(@OperationDate1)>0
set @FirstOperationDate1 = cast(left(@OperationDate1, 1) as int)
If len(@OperationDate2)>0
set @FirstOperationDate2 = cast(left(@OperationDate2, 1) as int)

-- @daygap la so ngay chenh lech giua ngay den va ngay di
Set @DayGap = 0
-- neu cung ngay hoat dong thi kiem tra dua vao gio scheduledtime
if (@FirstOperationDate1 = @FirstOperationDate2)
begin
if (left(@FlightNo, 2) <> 'VN' and left(@FlightNo, 2) <> 'BL' and left(@FlightNo, 2) <> '0V' and left(@FlightNo, 2) <> 'VP' and left(@FlightNo, 2) <> 'P8') Begin
If @ArrDep = 'A'
begin
if ((right(@ST1,1)!='+' and right(@ST2,1)<>'+') Or (right(@ST1,1)='+' and right(@ST2,1)='+') )
And Dbo.CompareTime(@ST1, @ST2)>0
Set @DayGap = 1
else if (right(@ST1,1)!='+' and right(@ST2,1)='+')
Set @DayGap = 1
end Else If @ArrDep = 'D' begin
if ((right(@ST1,1)!='+' and right(@ST2,1)!='+') or (right(@ST1,1)='+' and right(@ST2,1)='+') )
and Dbo.CompareTime(@ST1, @ST2)<0
Set @DayGap = -1
else if (right(@ST1,1)='+' and right(@ST2,1)<>'+')
Set @DayGap = -1
end
End
end
else
begin
if (left(@FlightNo, 2) <> 'VN' and left(@FlightNo, 2) <> 'BL' and left(@FlightNo, 2) <> '0V' and left(@FlightNo, 2) <> 'VP') Begin
If @ArrDep = 'A' begin
If @FirstOperationDate1 < @FirstOperationDate2
set @DayGap = @FirstOperationDate2 - @FirstOperationDate1
Else
set @DayGap = @FirstOperationDate2 + 7 - @FirstOperationDate1
end Else If @ArrDep = 'D' begin
If @FirstOperationDate2 < @FirstOperationDate1
set @DayGap = @FirstOperationDate1 - @FirstOperationDate2
Else
set @DayGap = @FirstOperationDate1 + 7 - @FirstOperationDate2
end
End
end

PRINT(@DayGap)
-- PRINT(@FlightNo)
-- PRINT(@LinkedFlightNo)
-- PRINT(@flightseasonid)
-- PRINT(@LinkedFlightSeasonId)
-- update link flight
-- SET @DayGap = 0

-- Quan rem lai 25/3. Chuyen qua CURSOR de cai thien toc do
/*update flight
set flight.linkedflight = flight2.flightid,
flight.AccLinkedFlightId = flight2.flightid
from flight, flight flight2
where flight.flightno = @FlightNo and flight2.flightno = @LinkedFlightNo
and flight2.flightdate = dateadd(day, @DayGap, flight.flightdate)
and flight.status<>'xxx' and flight2.status<>'xxx'
and flight.source = 'SEA' and flight.extrainfo = @flightseasonid
and flight2.source = 'SEA' and flight2.extrainfo = @LinkedFlightSeasonId

-- update nguoc lai link flight
update flight
set flight.linkedflight = flight2.flightid,
flight.AccLinkedFlightId = flight2.flightid
from flight, flight flight2
where flight.flightno = @LinkedFlightNo and flight2.flightno = @FlightNo
and flight.flightdate = dateadd(day, @DayGap, flight2.flightdate)
and flight.status<>'xxx' and flight2.status<>'xxx'
and flight.source = 'SEA' and flight.extrainfo = @LinkedFlightSeasonId
and flight2.source = 'SEA' and flight2.extrainfo = @flightseasonid
*/

CREATE TABLE #tmpFlight( FlightId INT,
FlightNo VARCHAR(10),
FlightDate DATETIME,
STATUS VARCHAR(3),
Source varchar (3),
ExtraInfo varchar (50))
INSERT INTO #tmpFlight
SELECT FlightId , FlightNo , FlightDate , STATUS ,Source ,ExtraInfo
FROM Flight WHERE LEFT(FlightNo,2) = @Carrier AND [Status] <> 'XXX'

CREATE TABLE #tmpUpdate (FlightId INT,
LinkedFlightId INT )

INSERT INTO #tmpUpdate
SELECT flight.FlightID, flight2.flightid
from #tmpFlight flight, #tmpFlight flight2
where flight.flightno = @FlightNo and flight2.flightno = @LinkedFlightNo
and flight2.flightdate = dateadd(day, @DayGap, flight.flightdate)
and flight.source = 'SEA' and flight.extrainfo = @flightseasonid
and flight2.source = 'SEA' and flight2.extrainfo = @LinkedFlightSeasonId

--SELECT * FROM #tmpUpdate
--RETURN;

DECLARE @FlightId2 INT, @LinkedFlightId2 INT
DECLARE @Cur AS CURSOR
SET @Cur = CURSOR SCROLL KEYSET FOR
SELECT FlightId, LinkedFlightId FROM #tmpUpdate
OPEN @Cur
FETCH NEXT FROM @Cur INTO @FlightId2, @LinkedFlightId2
WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE Flight SET LinkedFlight = @LinkedFlightId2, AccLinkedFlightId = @LinkedFlightId2 WHERE FlightID = @FlightId2
UPDATE Flight SET LinkedFlight = @FlightId2,AccLinkedFlightId = @FlightId2 WHERE FlightID = @LinkedFlightId2
FETCH NEXT FROM @Cur INTO @FlightId2, @LinkedFlightId2
END
CLOSE @Cur
DEALLOCATE @cur

-- danh dau cho biet lich mua da generate roi
UPDATE FlightSeason
SET Generated = 1
WHERE FlightSeasonId = @FlightSeasonId

drop table #tempDate
drop table #tfseason
drop table #tmpUpdate
drop table #tmpFlight
--COMMIT TRANSACTION



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO