/****** Object:  StoredProcedure [dbo].[DomesticShipQuoteBoardWT]    Script Date: 2/22/2021 10:08:26 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[DomesticShipQuoteBoardWT]
(
	---mandatory parameters----
	@StrColumns varchar(max),
	@orderByClause As varchar(250),
	@filteredWhere as varchar(max),
	@RecordStart as varchar(20),
	@RecordEnd as varchar(20),
	@PrimaryKey as varchar(100),
	
	--------Custom parameter for this store procedure----------
	@blSpecialCustomer as VARCHAR(10),
	@CHBRefTypeId as varchar(30),
	@GetCurrentDateTime as varchar(30),
	@WTAKEY230 as varchar(50),
	@SeeCharges as varchar(20)
)
AS

DECLARE @StrSql AS varchar(max)
DECLARE @StrSql2 AS varchar(max)
DECLARE @strFrom AS varchar(max)
DECLARE @Columns AS varchar(max)
DECLARE @GroupByClause AS varchar(max)

Set @filteredWhere = REPLACE(@filteredWhere, 'UploadLink', '''''')
Set @filteredWhere = REPLACE(@filteredWhere, 'ServiceLevelId', 'H.FK_ServiceLevelId')
Set @filteredWhere = REPLACE(@filteredWhere, 'FK_RefTypeID', 'E.FK_RefTypeID')
Set @filteredWhere = REPLACE(@filteredWhere, 'ReferenceNo', 'E.ReferenceNo')
Set @filteredWhere = REPLACE(@filteredWhere, 'ShipRefNo', 'SR.ReferenceNo')
Set @filteredWhere = REPLACE(@filteredWhere, 'ShipRefType', 'SR.FK_RefTypeID')
Set @filteredWhere = REPLACE(@filteredWhere, 'ConRefNo', 'CR.ReferenceNo')
Set @filteredWhere = REPLACE(@filteredWhere, 'ConRefType', 'CR.FK_RefTypeID')

---------------------------------------------From ---------------------------------------------------------
SET @strFrom=' FROM dbo.tbl_ShipmentHeader H WITH (NOLOCK) INNER JOIN        
			dbo.tbl_Shipper S WITH (NOLOCK) ON PK_OrderNo=FK_ShipOrderNo INNER JOIN 
			dbo.tbl_Consignee C WITH (NOLOCK) ON PK_OrderNo=FK_ConOrderNo  LEFT OUTER JOIN 
			tbl_ShipmentHeaderAUX as AUX WITH (NOLOCK) ON H.PK_OrderNo = AUX.FK_OrderNo '

SET @strFrom=@strFrom + ' LEFT OUTER JOIN (select PK_OrderStatusId, Alias from tbl_Statuses WITH (NOLOCK)) T on H.FK_OrderStatusId=T.PK_OrderStatusId '

if @blSpecialCustomer='True'
	SET @strFrom=@strFrom + ' LEFT OUTER JOIN dbo.tbl_Customers D WITH (NOLOCK) ON PK_CustNo=H.FK_ControlCustomerNo '
else
	SET @strFrom=@strFrom + ' LEFT OUTER JOIN dbo.tbl_Customers D WITH (NOLOCK) ON PK_CustNo=H.BillNo ' 

SET @strFrom=@strFrom + ' LEFT OUTER JOIN tbl_Modes WITH (NOLOCK) on FK_ModeId=PK_ModeId LEFT OUTER JOIN 			
						(select FK_orderNo,sum(total) as Charges from tbl_shipmentAPAR WITH (NOLOCK) Where APARCode=''C'' group by FK_orderNo) F on H.PK_OrderNO=F.FK_OrderNo '

IF @blSpecialCustomer='True'
BEGIN 
	SET @strFrom=@strFrom + ' LEFT OUTER JOIN (SELECT FK_OrderNo, MAX(ReferenceNo) AS ReferenceNo, MAX(FK_RefTypeId) AS FK_RefTypeId FROM tbl_References WITH (NOLOCK)  WHERE CustomerType=''S'' and FK_RefTypeId=D.CHBRefTypeId Group BY FK_OrderNo) AS E ON E.FK_OrderNo=H.Pk_orderno '
END
IF @blSpecialCustomer='False'
BEGIN
	SET @strFrom=@strFrom + ' LEFT OUTER JOIN (SELECT FK_OrderNo, MAX(ReferenceNo) AS ReferenceNo, MAX(FK_RefTypeId) AS FK_RefTypeId FROM tbl_References WITH (NOLOCK) WHERE CustomerType=''B'' Group BY FK_OrderNo) AS E ON E.FK_OrderNo=H.Pk_orderno '
END
	
SET @strFrom=@strFrom + ' LEFT OUTER JOIN 
						(SELECT Top 1 * FROM  tbl_oceancost WITH (NOLOCK) where Consolidation<>''Y'') AS OC ON OC.FK_OrderNo=H.PK_OrderNo			
						LEFT OUTER JOIN 
						(select Top 1 FK_OrderNo, CASE WHEN CONVERT(Date, ArrivalDateTime3, 101) <> ''01/01/1900''  OR ArrivalDateTime3 is NULL  THEN CONVERT(Date, ArrivalDateTime3, 101) 
						ELSE CASE WHEN CONVERT(Date, ArrivalDateTime2, 101) <> ''01/01/1900''  OR ArrivalDateTime2 is NULL  THEN CONVERT(Date, ArrivalDateTime2, 101)  
						ELSE CASE WHEN CONVERT(Date, ArrivalDateTime1, 101) <> ''01/01/1900''  OR ArrivalDateTime1 is NULL  THEN CONVERT(Date, ArrivalDateTime1, 101) 
						ELSE  ''''
						END END END as ETAFinalDate	 from tbl_AirCost WITH (NOLOCK) where Consolidation<>''Y'') as AC ON AC.FK_OrderNo=H.PK_OrderNo
						LEFT OUTER JOIN (SELECT FK_OrderNo, MAX(ReferenceNo) AS ReferenceNo, MAX(FK_RefTypeId) AS FK_RefTypeId FROM tbl_References WITH (NOLOCK) WHERE CustomerType=''S'' Group BY FK_OrderNo) AS SR ON SR.FK_OrderNo=H.Pk_orderno
						LEFT OUTER JOIN (SELECT FK_OrderNo, MAX(ReferenceNo) AS ReferenceNo, MAX(FK_RefTypeId) AS FK_RefTypeId FROM tbl_References WITH (NOLOCK) WHERE CustomerType=''C'' Group BY FK_OrderNo) AS CR ON CR.FK_OrderNo=H.Pk_orderno '		
-------------------------------End From Clause------------------------------------------------------------------

-------------------------------Start Where clause---------------------------------------------------------------
if @blSpecialCustomer='True'
BEGIN
	DECLARE @StartStr AS integer
	SET @StartStr=case when PATINDEX('%CAST(Housebill AS varchar(500)) LIKE%',@filteredWhere) is NULL then 0 else PATINDEX('%CAST(Housebill AS varchar(500)) LIKE%',@filteredWhere) end
	--ISNULL(PATINDEX('%CAST(Housebill AS varchar(500)) LIKE%',@filteredWhere),0) --189
	IF @StartStr>0 
		SET @filteredWhere=dbo.SearchVal(@filteredWhere) --817161004
	SET @filteredWhere = @filteredWhere + ' and CustomerHB=''Y'' '
END
-------------------------------End Where clause----------------------------------------------------------------

--------------------------------------Start Columns List-------------------------------------------------------------
--It should be identical order to column order in grid. Below code include column list as well as column based on itemdatabound.
SET @Columns=' dbo.QuoteExpiredStatus(H.Pk_OrderNo,''' + @GetCurrentDateTime + ''',''' + @WTAKEY230 + ''') as QuoteStatus, '
SET @Columns=@Columns + ' (SELECT COUNT(FK_OrderNo) FROM tbl_ShipmentHazmatDesc WHERE FK_OrderNo=H.Pk_OrderNo) as HazmatPieceCount, ' 
SET @Columns=@Columns + ' (Select Top 1 InvPrinted from tbl_ShipmentAPAR WITH (NOLOCK) where APARCode=''C'' AND InvoiceSeqNo=0 AND FK_OrderNo=H.Pk_OrderNo) as InvPrinted, '
SET @Columns=@Columns + ' '''' as UploadLink,H.Pk_OrderNo,H.MasterQuoteNo,H.HandlingStation, '
	
if @blSpecialCustomer='True'
	SET @Columns = @Columns + ' CASE WHEN (D.CustomerHB=''Y'' and D.CHBRefTypeId IS NOT NULL) THEN 
							(SELECT TOP 1 ReferenceNo FROM tbl_References WITH (NOLOCK) WHERE CustomerType=''S'' and FK_RefTypeId=D.CHBRefTypeId and fk_orderno=H.Pk_orderno) ELSE  ''''	END as Housebill, '
	Else
	SET @Columns = @Columns + ' H.Housebill, '
	
SET @Columns = @Columns + ' H.OrgAirport,H.DestAirPort, '
SET @Columns = @Columns + ' CASE WHEN FK_ModeId=''OCEANEXPORT'' or FK_ModeId=''OCEANIMPORT'' THEN
								(SELECT Description FROM tbl_Ports WITH (NOLOCK) WHERE InternalCode=H.OrgAirport)
							ELSE
								(SELECT Description FROM tbl_Airports WITH (NOLOCK) WHERE PK_AirportId=H.OrgAirport)
							END as OrgToolTip, '
SET @Columns = @Columns + ' CASE WHEN FK_ModeId=''OCEANEXPORT'' or FK_ModeId=''OCEANIMPORT'' THEN
								(SELECT Description FROM tbl_Ports WITH (NOLOCK) WHERE InternalCode=H.DestAirPort)
							ELSE
								(SELECT Description FROM tbl_Airports WITH (NOLOCK) WHERE PK_AirportId=H.DestAirPort)
							END as DestToolTip, '
SET @Columns = @Columns + ' S.ShipName,S.ShipAlpha,C.ConName,C.ConAlpha, '
SET @Columns = @Columns + ' C.ConName + '', '' + C.ConAddress1  + '', ''+ C.ConAddress2 + '', '' + C.ConCity + '', '' + C.FK_ConState + '', '' + C.ConZip as ConToolTip, '
SET @Columns = @Columns + ' S.ShipName + '', '' + S.ShipAddress1  + '', ''+ S.ShipAddress2 + '', '' + S.ShipCity + '', '' + S.FK_ShipState + '', '' + S.ShipZip as ShipToolTip, '
SET @Columns = @Columns + ' CASE WHEN CONVERT(Date, H.ReadyDateTime, 101) = ''01/01/1900'' THEN '''' ELSE CONVERT(Date, H.ReadyDateTime, 101) END as ReadyDateTime, '	
SET @Columns = @Columns + ' CASE WHEN CONVERT(Date, H.ShipmentDateTime, 101) = ''01/01/1900'' THEN '''' ELSE CONVERT(Date, H.ShipmentDateTime, 101) END as ShipmentDateTime, '
SET @Columns = @Columns + ' CASE WHEN ModeName=''Air Export''  or ModeName=''Air Import'' Then  
								CASE WHEN (CONVERT(Date, AC.ETAFInalDate, 101) = ''01/01/1900'' Or  AC.ETAFInalDate is NULL) THEN 
								'''' ELSE CONVERT(Date, AC.ETAFInalDate, 101) END
							ELSE   
								CASE WHEN (CONVERT(Date, OC.ETAFInalDate, 101) = ''01/01/1900'' Or  OC.ETAFInalDate is NULL) THEN 
								'''' ELSE CONVERT(Date, OC.ETAFInalDate, 101) END
							END as ETAFinalDate, '
SET @Columns = @Columns + ' CASE WHEN CONVERT(Date, H.ScheduledDateTime, 101) = ''01/01/1900'' THEN '''' ELSE CONVERT(Date, H.ScheduledDateTime, 101) END as ScheduledDateTime, '
SET @Columns = @Columns + ' CASE WHEN H.shipquote=''Q'' and H.ShipmentType=''D'' THEN 
								CASE WHEN (SELECT LEFT(DisplayedSv,2) as DisplayedSv FROM tbl_QuoteReport WHERE QuoteNo=H.Pk_OrderNo)<>'''' THEN (SELECT LEFT(DisplayedSv,2) as DisplayedSv FROM tbl_QuoteReport WHERE QuoteNo=H.Pk_OrderNo) 
								ELSE H.FK_ServiceLevelId END 
							ELSE H.FK_ServiceLevelId END as ServiceLevelId, '

SET @Columns = @Columns + ' CASE WHEN T.Alias = '''' THEN H.FK_OrderStatusId ELSE T.Alias END as FK_OrderStatusId, '

SET @Columns = @Columns + '	ModeName,F.Charges, '
SET @Columns = @Columns + '	CASE WHEN SR.ReferenceNo is NULL THEN '''' ELSE SR.ReferenceNo END as ShipRefNo, CASE WHEN SR.FK_RefTypeID is NULL THEN '''' ELSE SR.FK_RefTypeID END as ShipRefType, '
SET @Columns = @Columns + '	CASE WHEN CR.ReferenceNo is NULL THEN '''' ELSE CR.ReferenceNo END as ConRefNo, CASE WHEN CR.FK_RefTypeID is NULL THEN '''' ELSE CR.FK_RefTypeID END as ConRefType, '
SET @Columns = @Columns + '	CASE WHEN E.ReferenceNo is NULL THEN '''' ELSE E.ReferenceNo END as ReferenceNo, CASE WHEN E.FK_RefTypeID is NULL THEN '''' ELSE E.FK_RefTypeID END as FK_RefTypeID, '
SET @Columns = @Columns + ' H.Pk_OrderNo as PrintHAWB, H.Pk_OrderNo as PrintLabel, H.Pk_OrderNo as PrintInvoice,H.Pk_OrderNo as PrintShipperConsigneeLabel,H.Pk_OrderNo as PrintRoutingLabel, '
SET @Columns = @Columns + '	H.PriorityCodeId, case when (SELECT Color FROM tbl_PriorityCode WHERE PriorityCodeId=H.PriorityCodeId) is null then '''' else (SELECT Color FROM tbl_PriorityCode WHERE PriorityCodeId=H.PriorityCodeId) end as PriorityColor,FK_ModeId, '
SET @Columns = @Columns + ' CASE WHEN (Select Count(FileName) From tbl_shipmentfile Where CustomerAccess=''Y'' AND FK_OrderNo=H.PK_OrderNo)>0 THEN ''Y'' ELSE ''N'' END as AttachFileVisible,H.ShipQuote,CASE WHEN  Aux.RevisedETA is Null THEN ''01/01/1900'' ELSE CONVERT(Date, Aux.RevisedETA, 101) END as RevisedETA, H.Status '
-----------------------------------------------------------------------------------------------------------------------------------

---------------------------------Putting Query Together-----------------------------------------------------------
--Mandatory format for paging
--SET @StrSql = 'SELECT DISTINCT * FROM (SELECT row_number() OVER (' + @orderByClause + ') AS RowNumber, * FROM (SELECT 55000 AS TotalRows,(SELECT count(' + --@PrimaryKey + ') ' + @strFrom + @filteredWhere + ') AS TotalDisplayRows,' 

SET @StrSql = 'SELECT * FROM (SELECT row_number() OVER (' + @orderByClause + ') AS RowNumber, * FROM (SELECT (SELECT count(DISTINCT ' + @PrimaryKey + ') ' + @strFrom + ') AS TotalRows,(SELECT count(' + @PrimaryKey + ') ' + @strFrom + @filteredWhere + ') AS TotalDisplayRows, '

SET @StrSql2 =  @Columns + @strFrom + @filteredWhere + ') RawResults) Results WHERE RowNumber BETWEEN ' + @RecordStart + ' AND ' + @RecordEnd + ''

--print(REPLACE(REPLACE(REPLACE(RTRIM(LTrim(@StrSql)), CHAR(13),''), CHAR(10),''),CHAR(9),''))
exec(@StrSql + @StrSql2)

--Declare @FinalSQL as varchar(max) = ''-- = (REPLACE(REPLACE(REPLACE(RTRIM(LTrim(@StrSql)), CHAR(13),''), CHAR(10),''),CHAR(9),'')) --+ @StrSql2
--set @FinalSQL = @StrSql + @StrSql2
-- PRINT SUBSTRING(@FinalSQL,0,4000)
-- PRINT SUBSTRING(@FinalSQL,4000,4000)
-- PRINT SUBSTRING(@FinalSQL,8000,4000)
-- PRINT SUBSTRING(@FinalSQL,12000,4000)
-- PRINT SUBSTRING(@FinalSQL,16000,4000)
-- PRINT SUBSTRING(@FinalSQL,20000,4000)
-- PRINT SUBSTRING(@FinalSQL,24000,4000)
-- PRINT SUBSTRING(@FinalSQL,28000,4000)
------------------------------------------------------------------------------------------------------------------
Return @@Error


GO