sal_qry_saleplanofitemanalyse.dfm

一个MRPII系统源代码版本

        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=10 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as r' +
        'ealSaleAmount10,--实际销售额'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=10 t' +
        'hen isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=10 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as rea' +
        'lSaleqty10,--实际销售数量'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=10 th' +
        'en isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=10 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '      -Plansum_10 as DiffAmount10,--实际较计划额'
      '      case when Plansum_10=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=10 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=10 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '           )/Plansum_10 end as ratePlan10,--计划额完成率'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=10 th' +
        'en isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=10 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '      -rationsum_10 as DiffrationAmount10,--实际较计划数量'
      '      case when rationsum_10=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=10 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=10 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '           )/rationsum_10 end as rateration10 ,--计划数量完成率'
      ''
      '       Plansum_11,'
      '       rationsum_11,'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=11 t' +
        'hen isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=11 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as r' +
        'ealSaleAmount11,--实际销售额'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=11 t' +
        'hen isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=11 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as rea' +
        'lSaleqty11,--实际销售数量'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=11 th' +
        'en isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=11 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '      -Plansum_11 as DiffAmount11,--实际较计划额'
      '      case when Plansum_11=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=11 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=11 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '           )/Plansum_11 end as ratePlan11,--计划额完成率'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=11 th' +
        'en isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=11 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '      -rationsum_11 as DiffrationAmount11,--实际较计划数量'
      '      case when rationsum_11=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=11 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=11 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '           )/rationsum_11 end as rateration11 ,--计划数量完成率'
      ''
      '       Plansum_12,'
      '       rationsum_12,'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=12 t' +
        'hen isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=12 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as r' +
        'ealSaleAmount12,--实际销售额'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=12 t' +
        'hen isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=12 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as rea' +
        'lSaleqty12,--实际销售数量'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=12 th' +
        'en isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=12 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '      -Plansum_12 as DiffAmount12,--实际较计划额'
      '      case when Plansum_12=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=12 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=12 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '           )/Plansum_12 end as ratePlan12,--计划额完成率'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=12 th' +
        'en isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=12 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '      -rationsum_12 as DiffrationAmount12,--实际较计划数量'
      '      case when rationsum_12=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=12 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=12 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '           )/rationsum_12 end as rateration12 --计划数量完成率'
      ''
      '      '
      'from Sal_SalePlan_Item'
      'left join Item on Sal_SalePlan_Item.ItemCode=Item.ItemCode'
      
        'left join #sa_ShipOrder on Sal_SalePlan_Item.ItemCode=#sa_Shipor' +
        'der.ItemCode '
      
        'left join #sa_BackShipOrder on Sal_SalePlan_Item.ItemCode=#sa_Ba' +
        'ckShipOrder.ItemCode '
      'where Sal_SalePlan_Item.PlanYear=2002'
      
        'group by PlanYear,Sal_SalePlan_Item.ItemCode,Item.ItemName ,Plan' +
        'sum_Year,rationsum_Year,'
      '         Plansum_1,rationsum_1,'
      '         Plansum_2,rationsum_2,'
      '         Plansum_3,rationsum_3,'
      '         Plansum_4,rationsum_4,'
      '         Plansum_5,rationsum_5,'
      '         Plansum_6,rationsum_6,'
      '         Plansum_7,rationsum_7,'
      '         Plansum_8,rationsum_8,'
      '         Plansum_9,rationsum_9,'
      '         Plansum_10,rationsum_10,'
      '         Plansum_11,rationsum_11,'
      '         Plansum_12,rationsum_12'
      ''
      ''
      ''
      ''
      ''
      ''
      '')
    Top = 257
    object AdoQry_MaInPlanYear: TIntegerField
      DisplayLabel = '年份'
      FieldName = 'PlanYear'
      Origin = '#Sal_SalePlan_Item_Qry'
    end
    object AdoQry_MainItemCode: TStringField
      DisplayLabel = '产品代码'
      FieldName = 'ItemCode'
      Origin = '#Sal_SalePlan_Item_Qry'
      Size = 16
    end
    object AdoQry_MainItemName: TStringField
      DisplayLabel = '产品名称'
      FieldName = 'ItemName'
      Origin = '#Sal_SalePlan_Item_Qry'
      Size = 80
    end
    object AdoQry_MainItemflag: TStringField
      DisplayLabel = '产品标识'
      FieldName = 'Itemflag'
      Origin = '#Sal_SalePlan_Item_Qry'
      ReadOnly = True
      Size = 97
    end
    object AdoQry_MaInPlansum_Year: TFloatField
      DisplayLabel = '年|计划额'
      FieldName = 'Plansum_Year'
      Origin = '#Sal_SalePlan_Item_Qry'
    end
    object AdoQry_Mainrationsum_Year: TFloatField
      DisplayLabel = '年|计划数量'
      FieldName = 'rationsum_Year'
      Origin = '#Sal_SalePlan_Item_Qry'
    end
    object AdoQry_MainrealSaleqty: TFloatField
      DisplayLabel = '年|实际销售数量'
      FieldName = 'realSaleqty'
      Origin = '#Sal_SalePlan_Item_Qry'
      ReadOnly = True
    end
    object AdoQry_MainrealSaleAmount: TFloatField
      DisplayLabel = '年|实际销售金额'
      FieldName = 'realSaleAmount'
      Origin = '#Sal_SalePlan_Item_Qry'
      ReadOnly = True
    end
    object AdoQry_MainDiffrationAmount: TFloatField
      DisplayLabel = '年|实际较计划数量'
      FieldName = 'DiffrationAmount'
      Origin = '#Sal_SalePlan_Item_Qry'
      ReadOnly = True
    end
    object AdoQry_Mainrateration: TFloatField
      DisplayLabel = '年|计划数量完成率%'
      FieldName = 'rateration'
      Origin = '#Sal_SalePlan_Item_Qry'
      ReadOnly = True
    end
    object AdoQry_MainDiffAmount: TFloatField
      DisplayLabel = '年|实际较计划金额'
      FieldName = 'DiffAmount'
      Origin = '#Sal_SalePlan_Item_Qry'
      ReadOnly = True
    end
    object AdoQry_MainratePlan: TFloatField
      DisplayLabel = '年|计划金额完成率%'
      FieldName = 'ratePlan'
      Origin = '#Sal_SalePlan_Item_Qry'
      ReadOnly = True
    end
    object AdoQry_MaInPlansum_1: TFloatField
      DisplayLabel = '一月|计划额'
      FieldName = 'Plansum_1'
      Origin = '#Sal_SalePlan_Item_Qry'
    end
    object AdoQry_Mainrationsum_1: TFloatField
      DisplayLabel = '一月|计划数量'
      FieldName = 'rationsum_1'
      Origin = '#Sal_SalePlan_Item_Qry'
    end
    object AdoQry_MainrealSaleAmount1: TFloatField
      DisplayLabel = '一月|实际销售金额'
      FieldName = 'realSaleAmount1'
      Origin = '#Sal_SalePlan_Item_Qry'
      ReadOnly = True
    end
    object AdoQry_MainrealSaleqty1: TFloatField
      DisplayLabel = '一月|实际销售数量'
      FieldName = 'realSaleqty1'
      Origin = '#Sal_SalePlan_Item_Qry'
      ReadOnly = True
    end
    object AdoQry_MainDiffAmount1: TFloatField
      DisplayLabel = '一月|实际较计划金额'
      FieldName = 'DiffAmount1'
      Origin = '#Sal_SalePlan_Item_Qry'
      ReadOnly = True
    end
    object AdoQry_MainratePlan1: TFloatField
      DisplayLabel = '一月|计划金额完成率%'
      FieldName = 'ratePlan1'
      Origin = '#Sal_SalePlan_Item_Qry'
      ReadOnly = True
    end
    object AdoQry_MainDiffrationAmount1: TFloatField
      DisplayLabel = '一月|实际较计划数量'
      FieldName = 'DiffrationAmount1'
      Origin = '#Sal_SalePlan_Item_Qry'
      ReadOnly = True
    end
    object AdoQry_Mainrateration1: TFloatField
      DisplayLabel = '一月|计划数量完成率%'
      FieldName = 'rateration1'
      Origin = '#Sal_SalePlan_Item_Qry'
      ReadOnly = True
    end
    object AdoQry_MaInPlansum_2: TFloatField
      DisplayLabel = '二月|计划额'
      FieldName = 'Plansum_2'
      Origin = '#Sal_SalePlan_Item_Qry'
    end
    object AdoQry_Mainrationsum_2: TFloatField
      DisplayLabel = '二月|计划数量'
      FieldName = 'rationsum_2'
      Origin = '#Sal_SalePlan_Item_Qry'
    end
    object AdoQry_MainrealSaleAmount2: TFloatField
      DisplayLabel = '二月|实际销售金额'
      FieldName = 'realSaleAmount2'
      Origin = '#Sal_SalePlan_Item_Qry'
      ReadOnly = True
    end
    object AdoQry_MainrealSaleqty2: TFloatField
      DisplayLabel = '二月|实际销售数量'
      FieldName = 'realSaleqty2'
      Origin = '#Sal_SalePlan_Item_Qry'
      ReadOnly = True
    end
    object AdoQry_MainDiffAmount2: TFloatField
      DisplayLabel = '二月|实际较计划金额'
      FieldName = 'DiffAmount2'
      Origin = '#Sal_SalePlan_Item_Qry'
      ReadOnly = True
    end
    object AdoQry_MainratePlan2: TFloatField
      DisplayLabel = '二月|计划金额完成率%'
      FieldName = 'ratePlan2'
      Origin = '#Sal_SalePlan_Item_Qry'
      ReadOnly = True
    end
    object AdoQry_MainDiffrationAmount2: TFloatField
      DisplayLabel = '二月|实际较计划数量'
      FieldName = 'DiffrationAmount2'
      Origin = '#Sal_SalePlan_Item_Qry'
      ReadOnly = True
    end
    object AdoQry_Mainrateration2: TFloatField
      DisplayLabel = '二月|计划数量完成率%'
      FieldName = 'rateration2'
      Origin = '#Sal_SalePlan_Item_Qry'
      ReadOnly = True
    end
    object AdoQry_MaInPlansum_3: TFloatField
      DisplayLabel = '三月|计划额'
      FieldName = 'Plansum_3'
      Origin = '#Sal_SalePlan_Item_Qry'
    end
    object AdoQry_Mainrationsum_3: TFloatField
      DisplayLabel = '三月|计划数量'
      FieldName = 'rationsum_3'
      Origin = '#Sal_SalePlan_Item_Qry'
    end
    object AdoQry_MainrealSaleAmount3: TFloatField
      DisplayLabel = '三月|实际销售金额'
      FieldName = 'realSaleAmount3'
      Origin = '#Sal_SalePlan_Item_Qry'
      ReadOnly = True
    end
    object AdoQry_MainrealSaleqty3: TFloatField
      DisplayLabel = '三月|实际销售数量'
      FieldName = 'realSaleqty3'
      Origin = '#Sal_SalePlan_Item_Qry'
      ReadOnly = True
    end
    object AdoQry_MainDiffAmount3: TFloatField
      DisplayLabel = '三月|实际较计划金额'