配舱报表存储过程(3天编程时间)

USE [CJGL2005]
GO
/****** Object:  StoredProcedure [dbo].[PROC_CONTAINER_LOADED_REPORT]    Script Date: 2017/10/26 10:25:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[PROC_CONTAINER_LOADED_REPORT]
    -- Add the parameters for the stored procedure here
    --<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
    --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

 --   -- Insert statements for procedure here
    --SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
    --计算每个配舱单号的订单票数
    IF OBJECT_ID('tempdb..#订单票数') is not null
    drop table #订单票数
    SELECT 配舱确认单号,确认装箱日期,COUNT(*) AS 订单票数
    INTO #订单票数
    FROM dbo.配仓单明细表
    GROUP BY 配舱确认单号,确认装箱日期


    --SELECT * FROM #订单票数

    --2.创建每个配舱确认单号下的平均箱量
    --2.1CREATE TABLE
    IF OBJECT_ID('tempdb..#AVG_CONTAINER') is not null
    drop table #AVG_CONTAINER

    CREATE TABLE #AVG_CONTAINER
    (
    CARGO_PLAN_ORDER NVARCHAR(50),
    DEPARTMENT NVARCHAR(50),
    TOTAL_CONTAINER_QTY decimal(18,2),
    CONTAINER_TYPE NVARCHAR(100),
    COUNT_ORDER decimal(18,2),
    AVG_ORDER_CONTAINER decimal(18,2)
    )


    --2.2插入数据(从17年的9月开始)
    INSERT INTO #AVG_CONTAINER(CARGO_PLAN_ORDER,DEPARTMENT,TOTAL_CONTAINER_QTY,CONTAINER_TYPE,COUNT_ORDER)
    SELECT  配舱单信息表.配舱确认单号,
            配舱单信息表.生产部门,
            配舱单信息表.总箱量,
            配舱单信息表.箱型,
            #订单票数.订单票数
    FROM 配舱单信息表 LEFT JOIN #订单票数
    ON 配舱单信息表.配舱确认单号=#订单票数.配舱确认单号
    WHERE 配舱单装箱时间>='2017-9-1'

    --SELECT * FROM #AVG_CONTAINER
    --2.3算出每个配舱单号下的平均柜量
    UPDATE #AVG_CONTAINER
    SET AVG_ORDER_CONTAINER=TOTAL_CONTAINER_QTY/COUNT_ORDER


    if exists (select 1
    from  sysobjects
    where  id = object_id('BASIC_DATA_OF_REPORT')
    and   type = 'U')
    drop table BASIC_DATA_OF_REPORT

    SELECT  配仓单明细表.配舱确认单号,
            #AVG_CONTAINER.DEPARTMENT,
            配仓单明细表.订单号,
            配仓单明细表.确认装箱日期,
            配仓单明细表.确认结果,
            配仓单明细表.errorsolution AS 延期方式,
            配仓单明细表.errordeadline AS 延期截止,
            #AVG_CONTAINER.CONTAINER_TYPE,
            #AVG_CONTAINER.TOTAL_CONTAINER_QTY,
            #AVG_CONTAINER.COUNT_ORDER,
            #AVG_CONTAINER.AVG_ORDER_CONTAINER AS 箱量
    INTO BASIC_DATA_OF_REPORT
    FROM 配仓单明细表 LEFT JOIN #AVG_CONTAINER
    ON 配仓单明细表.配舱确认单号=#AVG_CONTAINER.CARGO_PLAN_ORDER
    WHERE 配仓单明细表.确认装箱日期>'2017-10-1'

    UPDATE BASIC_DATA_OF_REPORT SET 延期方式='正常装柜'
    WHERE 延期方式 IS NULL

    UPDATE BASIC_DATA_OF_REPORT SET 延期方式='未确认'
    WHERE 确认结果 ='未确认'

    --SELECT * FROM BASIC_DATA_OF_REPORT



    --加入总柜量和装规律
    --CALCULATE TOTAL_CONTAINER_QTY
    IF OBJECT_ID('[BASIC_DATA_OF_REPORT_GOURPED]','U') IS NOT NULL DROP TABLE [BASIC_DATA_OF_REPORT_GOURPED];
    CREATE TABLE [BASIC_DATA_OF_REPORT_GOURPED]
    (
        [DEPARTMENT] nvarchar(50),
        延期方式 nvarchar(50),
        确认装箱日期 DATETIME,
        箱量 decimal(18, 2)
    )
    INSERT INTO [BASIC_DATA_OF_REPORT_GOURPED]([DEPARTMENT],延期方式,确认装箱日期,箱量)
    SELECT [DEPARTMENT],'总柜量' AS 延期方式,[确认装箱日期],SUM([箱量]) AS 箱量
    FROM [BASIC_DATA_OF_REPORT]
    GROUP BY [DEPARTMENT],[确认装箱日期]
    UNION ALL
    SELECT [DEPARTMENT],[延期方式],[确认装箱日期],箱量
    FROM [BASIC_DATA_OF_REPORT]

    --CALCULATE CONTAINER CUBE UTILIZATION 
    IF OBJECT_ID('tempdb..#正常装柜') is not null
    DROP TABLE #正常装柜

    SELECT [DEPARTMENT],延期方式,确认装箱日期,SUM([箱量]) AS 箱量
    INTO #正常装柜
    FROM [BASIC_DATA_OF_REPORT_GOURPED]
    WHERE 延期方式='正常装柜'
    GROUP BY [DEPARTMENT],延期方式,确认装箱日期

    --SELECT * FROM #正常装柜

    --总归量选取
    IF OBJECT_ID('tempdb..#总柜量') is not null
    DROP TABLE #总柜量

    SELECT [DEPARTMENT],延期方式,确认装箱日期,箱量
    INTO #总柜量
    FROM [BASIC_DATA_OF_REPORT_GOURPED]
    WHERE 延期方式='总柜量'

    INSERT INTO [BASIC_DATA_OF_REPORT_GOURPED]([DEPARTMENT],延期方式,确认装箱日期,箱量)
    SELECT  #正常装柜.[DEPARTMENT],'装柜率',#正常装柜.确认装箱日期,
            CASE
            WHEN #总柜量.箱量=0 THEN 0
            WHEN #总柜量.箱量<>0 THEN #正常装柜.箱量/#总柜量.箱量
            END AS 箱量
    FROM #正常装柜 LEFT JOIN #总柜量
    ON #正常装柜.[DEPARTMENT]=#总柜量.[DEPARTMENT]
        AND #正常装柜.确认装箱日期=#总柜量.确认装箱日期

    --直接删除正常装柜的临时表,因为下面还要用到



    --总柜量的计算
    IF OBJECT_ID('[BASIC_DATA_OF_REPORT_TOTAL]','U') IS NOT NULL DROP TABLE [BASIC_DATA_OF_REPORT_TOTAL];
    SELECT [DEPARTMENT],[延期方式],SUM([箱量]) AS 箱量,'DATA' AS DATA_TYPE
    INTO [BASIC_DATA_OF_REPORT_TOTAL]
    FROM [BASIC_DATA_OF_REPORT_GOURPED]
    WHERE 延期方式<>'装柜率'
    GROUP BY [DEPARTMENT],[延期方式]
    ORDER BY [DEPARTMENT]
    --这里要修改的,不然要插入数据错误的
    ALTER TABLE [BASIC_DATA_OF_REPORT_TOTAL] ALTER COLUMN DATA_TYPE VARCHAR(100);

    IF OBJECT_ID('tempdb..#正常装柜_ALL') is not null
    DROP TABLE #正常装柜_ALL;
    SELECT [DEPARTMENT],[延期方式],[箱量]
    INTO #正常装柜_ALL
    --这是第二张临时表,存储
    FROM BASIC_DATA_OF_REPORT_TOTAL
    WHERE [延期方式]='正常装柜'



    --SELECT *
    --FROM [BASIC_DATA_OF_REPORT_TOTAL]
    --WHERE [延期方式]='总柜量'

    INSERT INTO BASIC_DATA_OF_REPORT_TOTAL([DEPARTMENT],[延期方式],箱量,DATA_TYPE)
    SELECT  [BASIC_DATA_OF_REPORT_TOTAL].[DEPARTMENT],
            '装柜率' AS 延期方式,
             CONVERT(DECIMAL(18,2),#正常装柜_ALL.[箱量]/[BASIC_DATA_OF_REPORT_TOTAL].[箱量]) AS 箱量,
            'PERSENT' AS DATA_TYPE
    FROM    [BASIC_DATA_OF_REPORT_TOTAL] LEFT JOIN #正常装柜_ALL
            ON [BASIC_DATA_OF_REPORT_TOTAL].[DEPARTMENT]=#正常装柜_ALL.[DEPARTMENT]
    WHERE [BASIC_DATA_OF_REPORT_TOTAL].[延期方式]='总柜量'
END