SQL Data Source=(local);Initial Catalog=TourWriter true f2ffb85a-760a-4768-ac93-751d00b64041 SupplierName System.String StateName System.String GradeName System.Integer NumberOfNights System.Integer Quantity System.Integer PaxCount System.Integer PaxCountTotalCurrentYear System.Integer BookedAmt System.Integer NumberOfNightsPrevious System.Integer QuantityPrevious System.Integer PaxCountPrevious System.Integer PaxCountTotalPreviousYear System.Integer BookedAmtPrevious System.Integer VoucherLogoFile System.String DataPath System.String ItineraryCurrencyFormatForTotal System.String ItineraryCurrencyFormatForTotalPrevious System.Integer PurchaseItemDetail ------------------------------------ Select Final.SupplierID,Final.SupplierName ,Replace(ItineraryCurrencyFormatForTotal,'.00','')ItineraryCurrencyFormatForTotal ,PaxCount ,Total ,Convert(INT,ROUND(IsNull(BookedAmt,0),0))BookedAmt ,NumberOfNights ,Quantity ,SupplierIDT2Previous ,SupplierNameT2Previous ,Replace(ItineraryCurrencyFormatForTotalPrevious,'.00','')ItineraryCurrencyFormatForTotalPrevious ,PaxCountPrevious ,TotalPrevious ,Convert(INT,ROUND(IsNull(BookedAmtPrevious,0),0))BookedAmtPrevious ,NumberOfNightsPrevious ,QuantityPrevious ,G.GradeExternalName GradeName, st.StateName,VoucherLogoFile,DataPath,PaxCountTotalCurrentYear,PaxCountTotalPreviousYear--.* FROM (--Final SELECT IsNULL(T1.SupplierID,T2Previous.SupplierIDT2Previous) SupplierID,IsNULL(T1.SupplierName,T2Previous.SupplierNameT2Previous)SupplierName, T1.ItineraryCurrencyFormatForTotal ,PaxCountTable.PaxCount ,PaxCountPreviousTable.PaxCountPrevious ,T1.Total,T1.BookedAmt, T1.NumberOfNights ,T1.Quantity ,T2Previous.* FROM ( --T1 Select SupplierID, SupplierName, CurrencyFormat ItineraryCurrencyFormatForTotal , SUM(NetFinalTotal) Total ,SUM(BookedAmt) BookedAmt ,Convert(INT,SUM(IsNULL(NumberOFDays,1) * IsNULL(QuantityTemp,1))) NumberOfNights ,Convert(INT,SUM(IsNULL(Quantity,1)))Quantity FROM (--tBookedAmt Select SupplierID, ItineraryID, 'RankItinerary' = Row_Number() Over (Partition By ItineraryID Order By ItineraryID), SupplierName,CurrencyFormat, PaxCount,NetFinalTotal,(P.NetBaseTotal - ((Select Top 1 IsNULL(DiscountUnits,0) FROM PurchaseItem pit WHERE pit.PurchaseItemID=P.PurchaseItemID) * IsNULL(P.NetBaseUnit,1) * IsNULL(P.UnitMultiplier,1)))BookedAmt ,CASE WHEN ServiceTypeName IN ('Accommodation','Rental Car') THEN Convert(INT,IsNULL(NumberOfDays,1)) Else 0 END NumberOfDays ,CASE WHEN ServiceTypeName IN ('Accommodation','Rental Car') THEN Convert(INT,IsNULL(Quantity,1)) Else 0 END QuantityTemp ,CASE WHEN ServiceTypeName NOT IN ('Accommodation','Rental Car') THEN Convert(INT,IsNULL(Quantity,1)) ELSE 0 END Quantity FROM PurchaseItemDetail P OUTER APPLY (Select TOP 1 CountryID from Country WHERE Country.CountryName=P.CountryName)c Where P.PurchaseItemStartDate >= @StartDate AND P.PurchaseItemStartDate < @EndDate AND (ServiceTypeID in @ServiceTypeIDList) AND (AgentID in @AgentIDList) AND (ItineraryStatusID IN @ItineraryStatusIDList) AND (RequestStatusID IN @RequestStatusIDList) AND (BranchID in @BranchIDList OR BranchID IS NULL) AND (DepartmentID in @DepartmentIDList OR DepartmentID IS NULL) AND (CountryID in @CountryIDList) )tBookedAmt Group BY SupplierID,SupplierName,CurrencyFormat )T1 --Previous T2 FULL OUTER JOIN ( Select SupplierID SupplierIDT2Previous, SupplierName SupplierNameT2Previous, CurrencyFormat ItineraryCurrencyFormatForTotalPrevious --, SUM(PaxCount)PaxCountPrevious ,SUM(NetFinalTotal) TotalPrevious ,SUM(BookedAmt) BookedAmtPrevious ,Convert(INT,SUM(IsNULL(NumberOFDays,1) * IsNULL(QuantityTemp,1))) NumberOfNightsPrevious ,Convert(INT,SUM(IsNULL(Quantity,1)))QuantityPrevious FROM ( --tBookedAmtPrevious Select SupplierID, ItineraryID,SupplierName, CurrencyFormat,PaxCount,NetFinalTotal, (P.NetBaseTotal - ((Select Top 1 IsNULL(DiscountUnits,0) FROM PurchaseItem pit WHERE pit.PurchaseItemID=P.PurchaseItemID) * IsNULL(P.NetBaseUnit,1) * IsNULL(P.UnitMultiplier,1)))BookedAmt ,CASE WHEN ServiceTypeName IN ('Accommodation','Rental Car') THEN Convert(INT,IsNULL(NumberOfDays,1)) Else 0 END NumberOfDays ,CASE WHEN ServiceTypeName IN ('Accommodation','Rental Car') THEN Convert(INT,IsNULL(Quantity,1)) Else 0 END QuantityTemp ,CASE WHEN ServiceTypeName NOT IN ('Accommodation','Rental Car') THEN Convert(INT,IsNULL(Quantity,1)) ELSE 0 END Quantity FROM PurchaseItemDetail P OUTER APPLY (Select TOP 1 CountryID from Country WHERE Country.CountryName=P.CountryName)c Where P.PurchaseItemStartDate >= DateAdd(yyyy,-1,@StartDate ) AND P.PurchaseItemStartDate < DateAdd(yyyy,-1,@EndDate ) AND (ServiceTypeID in @ServiceTypeIDList) AND (AgentID in @AgentIDList) AND (ItineraryStatusID IN @ItineraryStatusIDList) AND (RequestStatusID IN @RequestStatusIDList) AND (BranchID in @BranchIDList OR BranchID IS NULL) AND (DepartmentID in @DepartmentIDList OR DepartmentID IS NULL) AND (CountryID in @CountryIDList) )tBookedAmtPrevious Group BY SupplierID,SupplierName,CurrencyFormat )T2Previous ON T2Previous.SupplierIDT2Previous=T1.SupplierID AND T2Previous.ItineraryCurrencyFormatForTotalPrevious= T1.ItineraryCurrencyFormatForTotal --PAX count OUTER APPLY ( SELECT Sum(tPaxCount.PaxCount)PaxCount FROM ( Select DISTINCT ItineraryID,PaxCount,SupplierID,CurrencyFormat FROM PurchaseItemDetail P OUTER APPLY (Select TOP 1 CountryID from Country WHERE Country.CountryName=P.CountryName)c Where P.SupplierID=T1.SupplierID AND P.CurrencyFormat=T1.ItineraryCurrencyFormatForTotal AND P.PurchaseItemStartDate >= @StartDate AND P.PurchaseItemStartDate < @EndDate AND (ServiceTypeID in @ServiceTypeIDList) AND (AgentID in @AgentIDList) AND (ItineraryStatusID IN @ItineraryStatusIDList) AND (RequestStatusID IN @RequestStatusIDList) AND (BranchID in @BranchIDList OR BranchID IS NULL) AND (DepartmentID in @DepartmentIDList OR DepartmentID IS NULL) AND (CountryID in @CountryIDList) )tPaxCount )PaxCountTable --Pax Count Previous OUTER APPLY ( SELECT Sum(tPaxCountPrevious.PaxCount)PaxCountPrevious FROM ( Select DISTINCT ItineraryID,PaxCount,SupplierID ,CurrencyFormat FROM PurchaseItemDetail Pd OUTER APPLY (Select TOP 1 CountryID from Country WHERE Country.CountryName=Pd.CountryName)c Where Pd.SupplierID=T2Previous.SupplierIDT2Previous AND Pd.CurrencyFormat=T2Previous.ItineraryCurrencyFormatForTotalPrevious AND Pd.PurchaseItemStartDate >= DateAdd(yyyy,-1,@StartDate ) AND Pd.PurchaseItemStartDate < DateAdd(yyyy,-1,@EndDate ) AND (ServiceTypeID in @ServiceTypeIDList) AND (AgentID in @AgentIDList) AND (ItineraryStatusID IN @ItineraryStatusIDList) AND (RequestStatusID IN @RequestStatusIDList) AND (BranchID in @BranchIDList OR BranchID IS NULL) AND (DepartmentID in @DepartmentIDList OR DepartmentID IS NULL) AND (CountryID in @CountryIDList) )tPaxCountPrevious )PaxCountPreviousTable )Final LEFT OUTER JOIN Supplier s ON s.SupplierID = Final.SupplierID LEFT OUTER JOIN GradeExternal g ON s.GradeExternalID=g.GradeExternalID LEFT OUTER JOIN [State] st ON st.StateID=s.StateID OUTER APPLY ( SELECT TOP (1) CompanyName, StreetAddress, WorkPhone, Email1, Website, VoucherLogoFile, ClientFooter FROM Agent A LEFT JOIN AgentContact AC ON AC.AgentID = A.AgentID LEFT JOIN Contact C ON C.ContactID = AC.ContactID WHERE A.IsDefaultAgent = 1 ) AgentContact Outer Apply ( SELECT ExternalFilesPath DataPath FROM AppSettings ) DataPathTable OUTER APPLY ( SELECT SUM(PaxCount) PaxCountTotalCurrentYear FROM ( SELECT DISTINCT ItineraryID ,ISNULL(PaxCount, 0) PaxCount FROM PurchaseItemDetail P OUTER APPLY (Select TOP 1 CountryID from Country WHERE Country.CountryName=P.CountryName)c WHERE P.PurchaseItemStartDate >= @StartDate AND P.PurchaseItemStartDate < @EndDate AND (ServiceTypeID in @ServiceTypeIDList) AND (AgentID in @AgentIDList) AND (ItineraryStatusID IN @ItineraryStatusIDList) AND (RequestStatusID IN @RequestStatusIDList) AND (BranchID in @BranchIDList OR BranchID IS NULL) AND (DepartmentID in @DepartmentIDList OR DepartmentID IS NULL) AND (CountryID in @CountryIDList) )tableSum ) TotalPaxCurrentYear OUTER APPLY (SELECT SUM(PaxCount) PaxCountTotalPreviousYear FROM ( SELECT DISTINCT ItineraryID ,ISNULL(PaxCount, 0) PaxCount FROM PurchaseItemDetail P OUTER APPLY (Select TOP 1 CountryID from Country WHERE Country.CountryName=P.CountryName)c WHERE P.PurchaseItemStartDate >= DateAdd(yyyy,-1,@StartDate ) AND P.PurchaseItemStartDate < DateAdd(yyyy,-1,@EndDate ) AND (ServiceTypeID in @ServiceTypeIDList) AND (AgentID in @AgentIDList) AND (ItineraryStatusID IN @ItineraryStatusIDList) AND (RequestStatusID IN @RequestStatusIDList) AND (BranchID in @BranchIDList OR BranchID IS NULL) AND (DepartmentID in @DepartmentIDList OR DepartmentID IS NULL) AND (CountryID in @CountryIDList) )tableSum ) TotalPaxPreviousYear ORDER BY Final.SupplierName ----------------------------------- true 2.10259in 1.47991in 1.17in 0.57in 0.57in 0.57in 1.34512in 0.57in 0.57in 0.57in 1.33381in 1.04331in true Supplier Purchases Black 1pt 2pt 2pt 2pt 2pt 3 true true Black 1pt 2pt 2pt 2pt 2pt 2.60893in 2.92in 1.04331in true true 2pt 2pt 2pt 2pt External =IIF(Len(First(Fields!VoucherLogoFile.Value, "PurchaseItemDetail")) > 0, IIF(Instr(lcase(First(Fields!VoucherLogoFile.Value, "PurchaseItemDetail")), "https:") > 0, First(Fields!VoucherLogoFile.Value, "PurchaseItemDetail"), "file:///" & First(Fields!DataPath.Value , "PurchaseItemDetail") & First(Fields!VoucherLogoFile.Value, "PurchaseItemDetail")), "file:///" & "\Images\AgentLogo\TWTravelLogo.jpg") '"file:///" & Parameters!DefaultImagePath.Value & "\Images\AgentLogo\TWTravelLogo.jpg") FitProportional 7 0.23622in true true Black 1pt Yellow 2pt 2pt 2pt 2pt true true Black 1pt Yellow 2pt 2pt 2pt 2pt true true Black 1pt Yellow 2pt 2pt 2pt 2pt true true Black 1pt Yellow 2pt 2pt 2pt 2pt true true Black 1pt Yellow 2pt 2pt 2pt 2pt true true Black 1pt Yellow 2pt 2pt 2pt 2pt true true Black 1pt Yellow 2pt 2pt 2pt 2pt true true Black 1pt Yellow 2pt 2pt 2pt 2pt true true Black 1pt Yellow 2pt 2pt 2pt 2pt true true Black 1pt Yellow 2pt 2pt 2pt 2pt true true Black 1pt Yellow 2pt 2pt 2pt 2pt 0.23622in true true Black 2pt 2pt 2pt 2pt 2pt true true Black 2pt 2pt 2pt 2pt 2pt true true Black 2pt 2pt 2pt 2pt 2pt true true Black 2pt 2pt 2pt 2pt 2pt true true Black 2pt 2pt 2pt 2pt 2pt true true Black 2pt 2pt 2pt 2pt 2pt true true Black 2pt 2pt 2pt 2pt 2pt true true Black 2pt 2pt 2pt 2pt 2pt true true Black 2pt 2pt 2pt 2pt 2pt true true Black 2pt 2pt 2pt 2pt 2pt true true Black 2pt 2pt 2pt 2pt 2pt 0.27789in true true Black 1pt Black 1pt 2pt 2pt 2pt 2pt true true Black 1pt 2pt 2pt 2pt 2pt true true Black 1pt 2pt 2pt 2pt 2pt true true =DateAdd("yyyy",-1,DateValue(Parameters!StartDate.Value)).ToString("dd/MM/yyyy") + " - " + DateValue(DateAdd("yyyy",-1,Parameters!EndDate.Value)).ToString("dd/MM/yyyy") Textbox2 Black 1pt Black 2pt 2pt 2pt 2pt 4 true true =DateValue(Parameters!StartDate.Value).ToString("dd/MM/yyyy") + " - " + DateValue(Parameters!EndDate.Value).ToString("dd/MM/yyyy") Textbox29 Black 1pt Black Black 2pt 2pt 2pt 2pt 4 0.23622in true true SUPPLIER Black 1pt Black 1pt 2pt 2pt 2pt 2pt true true STATE Black 1pt 2pt 2pt 2pt 2pt true true GROUP Black 1pt 2pt 2pt 2pt 2pt true true NTS Textbox74 Black 1pt 2pt 2pt 2pt 2pt true true PAX Textbox8 Black 2pt 2pt 2pt 2pt true true QTY Textbox13 Black 2pt 2pt 2pt 2pt true true AMOUNT Textbox22 Black 2pt 2pt 2pt 2pt true true NTS Textbox74 Black 2pt 2pt 2pt 2pt true true PAX Textbox8 Black 2pt 2pt 2pt 2pt true true QTY Textbox13 Black 2pt 2pt 2pt 2pt true true AMOUNT Textbox22 Black 1pt 2pt 2pt 2pt 2pt 0.19685in true true =Fields!SupplierName.Value SupplierName LightGrey 1pt 2pt 2pt 2pt 2pt true true =Fields!StateName.Value StateName LightGrey 1pt 2pt 2pt 2pt 2pt true true =Fields!GradeName.Value GradeName LightGrey 1pt 2pt 2pt 2pt 2pt true true =IIF(Fields!NumberOfNightsPrevious.Value>0,Fields!NumberOfNightsPrevious.Value,"") NumberOfNightsPrevious LightGrey 1pt 2pt 2pt 2pt 2pt true true =Fields!PaxCountPrevious.Value PaxCountPrevious LightGrey 1pt 2pt 2pt 2pt 2pt true true =IIF(Fields!QuantityPrevious.Value>0,Fields!QuantityPrevious.Value,"") QuantityPrevious LightGrey 1pt 2pt 2pt 2pt 2pt true true =IIF(Fields!BookedAmtPrevious.Value>0,Fields!BookedAmtPrevious.Value,"") 'Replace(Format(Round(Fields!BookedAmtPrevious.Value,0),Fields!ItineraryCurrencyFormatForTotalPrevious.Value),".00","") BookedAmtPrevious LightGrey 1pt 2pt 2pt 2pt 2pt true true =IIF(Fields!NumberOfNights.Value>0,Fields!NumberOfNights.Value,"") NumberOfNights LightGrey 1pt 2pt 2pt 2pt 2pt true true =Fields!PaxCount.Value PaxCount LightGrey 1pt 2pt 2pt 2pt 2pt true true =IIF(Fields!Quantity.Value>0,Fields!Quantity.Value,"") Quantity LightGrey 1pt 2pt 2pt 2pt 2pt true true =IIF(Fields!BookedAmt.Value>0,Fields!BookedAmt.Value,"") '=Replace(Format(Round(Fields!BookedAmt.Value,0),Fields!ItineraryCurrencyFormatForTotal.Value),".00","") BookedAmt LightGrey 1pt 2pt 2pt 2pt 2pt 0.23622in true true Gainsboro 1pt Black 3pt 2pt 2pt 2pt 2pt true true Gainsboro 1pt Black 3pt 2pt 2pt 2pt 2pt true true Gainsboro 1pt Black 3pt 2pt 2pt 2pt 2pt true true Gainsboro 1pt Black 3pt 2pt 2pt 2pt 2pt true true Gainsboro 1pt Black 3pt 2pt 2pt 2pt 2pt true true Gainsboro 1pt Black 3pt 2pt 2pt 2pt 2pt true true Gainsboro 1pt Black 3pt 2pt 2pt 2pt 2pt true true Gainsboro 1pt Black 3pt 2pt 2pt 2pt 2pt true true Gainsboro 1pt Black 3pt 2pt 2pt 2pt 2pt true true Gainsboro 1pt Black 3pt 2pt 2pt 2pt 2pt true true Gainsboro 1pt Black 3pt 2pt 2pt 2pt 2pt 0.23622in true true Textbox67 Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true Textbox68 LightGrey 1pt Yellow 2pt 2pt 2pt 2pt true true Textbox69 LightGrey 1pt Yellow 2pt 2pt 2pt 2pt true true Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true Textbox73 LightGrey 1pt Yellow 2pt 2pt 2pt 2pt true true Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true Textbox79 LightGrey 1pt Yellow 2pt 2pt 2pt 2pt 0.23622in true GroupOuter true ="TOTAL" Textbox97 Black Black 1pt 2pt 2pt 2pt 2pt true true Textbox3 LightGrey 1pt 2pt 2pt 2pt 2pt true true Textbox5 LightGrey 1pt 2pt 2pt 2pt 2pt true true =Sum(Fields!NumberOfNightsPrevious.Value) Black Black 1pt 2pt 2pt 2pt 2pt true true =SUM(Fields!PaxCountPrevious.Value) Black Black 1pt 2pt 2pt 2pt 2pt true true =Sum(Fields!QuantityPrevious.Value) Black Black 1pt 2pt 2pt 2pt 2pt true true =Replace(Format(SUM(Round(Fields!BookedAmtPrevious.Value,0)),IIF(Len(Fields!ItineraryCurrencyFormatForTotalPrevious.Value)>0,Fields!ItineraryCurrencyFormatForTotalPrevious.Value,Fields!ItineraryCurrencyFormatForTotal.Value)),".00","") Textbox21 LightGrey 1pt 2pt 2pt 2pt 2pt true true =Sum(Fields!NumberOfNights.Value) Black Black 1pt 2pt 2pt 2pt 2pt true true =SUM(Fields!PaxCount.Value) Black Black 1pt 2pt 2pt 2pt 2pt true true =Sum(Fields!Quantity.Value) Black Black 1pt 2pt 2pt 2pt 2pt true true =Replace(Format(SUM(Round(Fields!BookedAmt.Value,0)),IIF(Len(Fields!ItineraryCurrencyFormatForTotal.Value)>0,Fields!ItineraryCurrencyFormatForTotal.Value,Fields!ItineraryCurrencyFormatForTotalPrevious.Value)),".00","") Textbox27 LightGrey 1pt 2pt 2pt 2pt 2pt 0.23622in true true ="TOTAL PAX" Textbox1 Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true Textbox6 LightGrey 1pt Yellow 2pt 2pt 2pt 2pt true true Textbox7 LightGrey 1pt Yellow 2pt 2pt 2pt 2pt true true Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true =First(Fields!PaxCountTotalPreviousYear.Value) Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true Textbox26 LightGrey 1pt Yellow 2pt 2pt 2pt 2pt true true Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true =First(Fields!PaxCountTotalCurrentYear.Value) Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true Textbox37 LightGrey 1pt Yellow 2pt 2pt 2pt 2pt 0.23622in true true TOTAL: Textbox97 Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true =Sum(Fields!NumberOfNightsPrevious.Value) Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true =Sum(Fields!PaxCountPrevious.Value) Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true =Sum(Fields!QuantityPrevious.Value) Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true =Sum(Fields!BookedAmtPrevious.Value) Textbox24 Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true =Sum(Fields!NumberOfNights.Value) Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true =Sum(Fields!PaxCount.Value) Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true =Sum(Fields!Quantity.Value) Black Black 1pt Yellow 2pt 2pt 2pt 2pt true true =Sum(Fields!BookedAmt.Value) Textbox24 Black Black 1pt Yellow 2pt 2pt 2pt 2pt true After =Fields!DataPath.Value =Fields!DataPath.Value true After =IIF(Len(Fields!ItineraryCurrencyFormatForTotal.Value)>0,Fields!ItineraryCurrencyFormatForTotal.Value,Fields!ItineraryCurrencyFormatForTotalPrevious.Value) =IIF(Len(Fields!ItineraryCurrencyFormatForTotal.Value)>0,Fields!ItineraryCurrencyFormatForTotal.Value,Fields!ItineraryCurrencyFormatForTotalPrevious.Value) true Before true PurchaseItemDetail 0cm 0.03in 8.65584cm 27.56263cm 8.65584cm 0.02657in 3.80539in 0.5cm 4.89591cm true true ="Page " & Globals!PageNumber & " of " & Globals!TotalPages 0.02657in 5.75687in 0.5cm 2.49126cm 1 21cm 29.7cm 29.7cm 21cm 1cm 1cm 1cm 1cm 1.27cm en-US true bb0ee6a1-9417-4d69-9271-2e33f062e7ad Cm