USE [ztwl]

GO

/****** Object:  StoredProcedure [dbo].[SelectPage_StationTransferFee_Second]    Script Date: 04/11/2019 14:17:37 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[SelectPage_StationTransferFee_Second]

    @begincount varchar(50),       --开始条数

    @PageSize varchar(50) ,           --每页多少条记录

    @Where nvarchar(4000) =N'' , --条件语句

    @StartStationID varchar(50),            --始发分拨网点

    @SendStationID varchar(4000),            --发站网点

    @InStationID varchar(50)          --入库网点

as

BEGIN 

--------begin--组合发站参数临时表-----begin-----

DECLARE @Temp_Array varchar(max)

set @Temp_Array=@SendStationID

declare @Temp_Variable varchar(max)

create table #Temp_SendTable(Item varchar(max)) ---定义发站ID临时表

 while(LEN(@Temp_Array) > 0)

 begin if(CHARINDEX(',',@Temp_Array) = 0)

 begin set @Temp_Variable = @Temp_Array

 set @Temp_Array = ''

 end else begin set @Temp_Variable = LEFT(@Temp_Array,CHARINDEX(',',@Temp_Array)-1)

 set @Temp_Array = RIGHT(@Temp_Array,LEN(@Temp_Array)-LEN(@Temp_Variable)-1)

 end insert into #Temp_SendTable(Item) values(@Temp_Variable) end

------------组合发站参数------end--------------------

--- 第一部分：区域网点中转费

SELECT * INTO #Bas_FN_Area_StationConfig_Temp FROM (SELECT * from Bas_FN_Area_StationConfig_Daily WHERE 1=2   ) as x

  --1.1用始发分拨+发站网点+入库网点查询区域网点中转费报表；

  IF LEN(@StartStationID)>0

 BEGIN

    Insert into #Bas_FN_Area_StationConfig_Temp

        SELECT * from Bas_FN_Area_StationConfig_Daily WHERE UpBelongCenterID= @StartStationID

         and exists(select 1 from #Temp_SendTable(nolock) where #Temp_SendTable.Item=StartStationID) and InStationID=@InStationID and IfDel=0

       -- PRINT '有--始发分拨查询'

  end

  else

 BEGIN

      Insert into #Bas_FN_Area_StationConfig_Temp

     SELECT * from Bas_FN_Area_StationConfig_Daily WHERE    exists(select 1 from #Temp_SendTable(nolock) where #Temp_SendTable.Item=StartStationID) and InStationID=@InStationID and IfDel=0

      -- PRINT '无--始发分拨查询'

   end

   --1.2创建已有的+产品列表临时表

  SELECT * INTO #cityList_Temp FROM (SELECT a.FinalPlaceCityID , a.FinalPlaceCity,a.ProductID from #Bas_FN_Area_StationConfig_Temp a  ) AS y

 -- SELECT * from #Bas_FN_Area_StationConfig_Temp

--- 第二部分：网点中转费

--1.2发站网点+入库网点询+排除网点中转费重复的城市和产品

 SELECT * INTO #Bas_FN_StationConfig_Temp FROM (

 SELECT * from Bas_FN_StationConfig  a  WHERE     exists(select 1 from #Temp_SendTable(nolock) where #Temp_SendTable.Item=a.UpStationID) and a.StartStationID=@InStationID and a.IfDel=0

 and NOT  EXISTS  (SELECT b.FinalPlaceCityID from #cityList_Temp b WHERE a.FinalPlaceCityID=b.FinalPlaceCityID and a.ProductID=b.productID )

  ) as xx

-- select count(*) from #cityList_Temp

  --1.3 合并区域和网点费用 UpBelongCenterID,UpBelongCenter,QuoteBelongCenterID,QuoteBelongCenter,

    SELECT * INTO #Bas_FN_StationConfig_Finally_Temp FROM

   (

     SELECT  AreaCode,AreaName,StartStation as UpStation  ,StartStationID as UpStationID ,InStation as StartStation ,InStationID as StartStationID,FinalPlaceCity,FinalPlaceCityID,FinalPlaceProvince,FinalPlaceProvinceID ,Product,ProductID,

     InOutType,Limit,StartWeightOne,EndWeightOne,WeightPriceOne,VolumnPriceOne,StartWeightTwo,EndWeightTwo,WeightPriceTwo,VolumnPriceTwo,StartWeightThree,EndWeightThree,WeightPriceThree,VolumnPriceThree,StartWeightFour,EndWeightFour,WeightPriceFour,VolumnPriceFour,StartWeightFive,EndWeightFive,WeightPriceFive,VolumnPriceFive,StartWeightSix,EndWeightSix,WeightPriceSix,VolumnPriceSix,

       UpBelongCenterID,  UpBelongCenter,  QuoteBelongCenterID,  QuoteBelongCenter

      from #Bas_FN_Area_StationConfig_Temp

     UNION ALL

      SELECT   '' AS AreaCode,'' as AreaName,   UpStation,UpStationID,StartStation,StartStationID,FinalPlaceCity,FinalPlaceCityID,FinalPlaceProvince,FinalPlaceProvinceID ,Product,ProductID,

    CASE  Status WHEN 0 THEN '出港网点中转费' ELSE '进港网点中转费' END as InOutType,Limit,StartWeightOne,EndWeightOne,WeightPriceOne,VolumnPriceOne,StartWeightTwo,EndWeightTwo,WeightPriceTwo,VolumnPriceTwo,StartWeightThree,EndWeightThree,WeightPriceThree,VolumnPriceThree,StartWeightFour,EndWeightFour,WeightPriceFour,VolumnPriceFour,StartWeightFive,EndWeightFive,WeightPriceFive,VolumnPriceFive,StartWeightSix,EndWeightSix,WeightPriceSix,VolumnPriceSix,b.BelongCenterID as UpBelongCenterID,b.BelongCenter as UpBelongCenter,c.BelongCenterID as QuoteBelongCenterID,c.BelongCenter as QuoteBelongCenter

      FROM

       (SELECT   a.*,

     (SELECT top 1 aa.EndStationID from Bas_FinalPlace aa WHERE  aa.ifDelID=0 AND aa.QuoteCityID=a.FinalPlaceCityID) as EndStationID

    from #Bas_FN_StationConfig_Temp a   ) as aaa

      LEFT JOIN Bas_StationInformation b on aaa.UpStationID=b.StationID and b.IfDel=0

   LEFT JOIN Bas_StationInformation c on aaa.EndStationID=c.StationID and c.IfDel=0

     ) AS xyz

     --分页

     DECLARE @sql nvarchar(4000)

    set  @sql=' select * from ( SELECT top '+@PageSize+' ROW_NUMBER() Over(order by AreaCode desc ) as rownumid, a.* from #Bas_FN_StationConfig_Finally_Temp a

     '+@where+'     ) as pagetable where rownumid>'+@begincount

    PRINT @sql

     EXEC(@sql)

     --总数

    set  @sql='   SELECT COUNT(*) from  #Bas_FN_StationConfig_Finally_Temp '+@where+''

   EXEC(@sql)

	 DROP TABLE #Bas_FN_Area_StationConfig_Temp

	 DROP TABLE #cityList_Temp

	 DROP TABLE #Bas_FN_StationConfig_Temp

     DROP TABLE #Bas_FN_StationConfig_Finally_Temp

    DROP TABLE #Temp_SendTable

END