sal_qry_saleplanofsaleranalyse.dfm

一个MRPII系统源代码版本

      '      -rationsum_9 as DiffrationAmount9,--实际较计划定额'
      '      case when rationsum_9=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=9 then isnull(sa_Sh' +
        'ipOrder.TotaltaxAmount,0) else 0 end )'
      
        '            -sum(case when datepArt(yy,sa_BackShipOrder.Createda' +
        'te)=2002 and datepArt(mm,sa_BackShipOrder.Createdate)=9 then isn' +
        'ull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/rationsum_9 end as rateration9 ,--计划定额完成率'
      ''
      '       Plansum_10,'
      '       rationsum_10,'
      
        '       sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 a' +
        'nd datepArt(mm,sa_ShipOrder.Createdate)=10 then isnull(sa_Shipor' +
        'der.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=10 then isnull(' +
        'sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleamoun' +
        't,--实际销售额'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=10 then isnull(sa_Shipord' +
        'er.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=10 then isnull(' +
        'sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -Plansum_10 as DiffAmount10,--实际较计划额'
      '      case when Plansum_10=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=10 then isnull(sa_S' +
        'hipOrder.TotaltaxAmount,0) else 0 end )'
      
        '            -sum(case when datepArt(yy,sa_BackShipOrder.Createda' +
        'te)=2002 and datepArt(mm,sa_BackShipOrder.Createdate)=10 then is' +
        'null(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/Plansum_10 end as ratePlan10,--计划额完成率'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=10 then isnull(sa_Shipord' +
        'er.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=10 then isnull(' +
        'sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -rationsum_10 as DiffrationAmount10,--实际较计划定额'
      '      case when rationsum_10=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=10 then isnull(sa_S' +
        'hipOrder.TotaltaxAmount,0) else 0 end )'
      
        '            -sum(case when datepArt(yy,sa_BackShipOrder.Createda' +
        'te)=2002 and datepArt(mm,sa_BackShipOrder.Createdate)=10 then is' +
        'null(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/rationsum_10 end as rateration10 ,--计划定额完成率'
      ''
      '       Plansum_11,'
      '       rationsum_11,'
      
        '       sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 a' +
        'nd datepArt(mm,sa_ShipOrder.Createdate)=11 then isnull(sa_Shipor' +
        'der.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=11 then isnull(' +
        'sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleamoun' +
        't,--实际销售额'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=11 then isnull(sa_Shipord' +
        'er.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=11 then isnull(' +
        'sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -Plansum_11 as DiffAmount11,--实际较计划额'
      '      case when Plansum_11=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=11 then isnull(sa_S' +
        'hipOrder.TotaltaxAmount,0) else 0 end )'
      
        '            -sum(case when datepArt(yy,sa_BackShipOrder.Createda' +
        'te)=2002 and datepArt(mm,sa_BackShipOrder.Createdate)=11 then is' +
        'null(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/Plansum_11 end as ratePlan11,--计划额完成率'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=11 then isnull(sa_Shipord' +
        'er.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=11 then isnull(' +
        'sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -rationsum_11 as DiffrationAmount11,--实际较计划定额'
      '      case when rationsum_11=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=11 then isnull(sa_S' +
        'hipOrder.TotaltaxAmount,0) else 0 end )'
      
        '            -sum(case when datepArt(yy,sa_BackShipOrder.Createda' +
        'te)=2002 and datepArt(mm,sa_BackShipOrder.Createdate)=11 then is' +
        'null(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/rationsum_11 end as rateration11 ,--计划定额完成率'
      ''
      '       Plansum_12,'
      '       rationsum_12,'
      
        '       sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 a' +
        'nd datepArt(mm,sa_ShipOrder.Createdate)=12 then isnull(sa_Shipor' +
        'der.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=12 then isnull(' +
        'sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleamoun' +
        't,--实际销售额'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=12 then isnull(sa_Shipord' +
        'er.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=12 then isnull(' +
        'sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -Plansum_12 as DiffAmount12,--实际较计划额'
      '      case when Plansum_12=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=12 then isnull(sa_S' +
        'hipOrder.TotaltaxAmount,0) else 0 end )'
      
        '            -sum(case when datepArt(yy,sa_BackShipOrder.Createda' +
        'te)=2002 and datepArt(mm,sa_BackShipOrder.Createdate)=12 then is' +
        'null(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/Plansum_12 end as ratePlan12,--计划额完成率'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=12 then isnull(sa_Shipord' +
        'er.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=12 then isnull(' +
        'sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -rationsum_12 as DiffrationAmount12,--实际较计划定额'
      '      case when rationsum_12=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=12 then isnull(sa_S' +
        'hipOrder.TotaltaxAmount,0) else 0 end )'
      
        '            -sum(case when datepArt(yy,sa_BackShipOrder.Createda' +
        'te)=2002 and datepArt(mm,sa_BackShipOrder.Createdate)=12 then is' +
        'null(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/rationsum_12 end as rateration12 --计划定额完成率'
      ''
      '      '
      'from Sal_SalePlan_Saler'
      
        'left join Employee on Sal_SalePlan_Saler.SaleEmployeeCode=employ' +
        'ee.EmployeeCode'
      
        'left join sa_ShipOrder on Sal_SalePlan_Saler.SaleEmployeeCode=sa' +
        '_ShipOrder.SaleEmployeeCode and sa_ShipOrder.assess=1'
      
        'left join sa_BackShipOrder on Sal_SalePlan_Saler.SaleEmployeecod' +
        'e=sa_BackShipOrder.SaleEmployeeCode and sa_ShipOrder.assess=1'
      'where Sal_SalePlan_Saler.PlanYear=2002'
      
        'group by PlanYear,Sal_SalePlan_Saler.SaleEmployeeCode,Employee.e' +
        'mployeeName ,Plansum_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 = '#tMpSalePlan_Saler_Qry'
    end
    object AdoQry_MaInPlansum_Year: TFloatField
      DisplayLabel = '年|计划额'
      FieldName = 'Plansum_Year'
      Origin = '#tMpSalePlan_Saler_Qry'
    end
    object AdoQry_Mainrationsum_Year: TFloatField
      DisplayLabel = '年|销售定额'
      FieldName = 'rationsum_Year'
      Origin = '#tMpSalePlan_Saler_Qry'
    end
    object AdoQry_MainrealSaleAmount: TFloatField
      DisplayLabel = '年|实际销售额'
      FieldName = 'realSaleAmount'
      Origin = '#tMpSalePlan_Saler_Qry'
      ReadOnly = True
    end
    object AdoQry_MainDiffAmount: TFloatField
      DisplayLabel = '年|实际较计划额'
      FieldName = 'DiffAmount'
      Origin = '#tMpSalePlan_Saler_Qry'
      ReadOnly = True
    end
    object AdoQry_MainratePlan: TFloatField
      DisplayLabel = '年|计划额完成率%'
      FieldName = 'ratePlan'
      Origin = '#tMpSalePlan_Saler_Qry'
      ReadOnly = True
    end
    object AdoQry_MainDiffrationAmount: TFloatField
      DisplayLabel = '年|实际较计划定额'
      FieldName = 'DiffrationAmount'
      Origin = '#tMpSalePlan_Saler_Qry'
      ReadOnly = True
    end
    object AdoQry_Mainrateration: TFloatField
      DisplayLabel = '年|计划定额完成率'
      FieldName = 'rateration'
      Origin = '#tMpSalePlan_Saler_Qry'
      ReadOnly = True
    end
    object AdoQry_MaInPlansum_1: TFloatField
      DisplayLabel = '一月|计划额'
      FieldName = 'Plansum_1'
      Origin = '#tMpSalePlan_Saler_Qry'
    end
    object AdoQry_Mainrationsum_1: TFloatField
      DisplayLabel = '一月|销售定额'
      FieldName = 'rationsum_1'
      Origin = '#tMpSalePlan_Saler_Qry'
    end
    object AdoQry_MainrealSaleAmount_1: TFloatField
      DisplayLabel = '一月|实际销售额'
      FieldName = 'realSaleAmount_1'
      Origin = '#tMpSalePlan_Saler_Qry'
      ReadOnly = True
    end
    object AdoQry_MainDiffAmount1: TFloatField
      DisplayLabel = '一月|实际较计划额'
      FieldName = 'DiffAmount1'
      Origin = '#tMpSalePlan_Saler_Qry'
      ReadOnly = True
    end
    object AdoQry_MainratePlan1: TFloatField
      DisplayLabel = '一月|计划额完成率%'
      FieldName = 'ratePlan1'
      Origin = '#tMpSalePlan_Saler_Qry'
      ReadOnly = True
    end
    object AdoQry_MainDiffrationAmount1: TFloatField
      DisplayLabel = '一月|实际较计划定额'
      FieldName = 'DiffrationAmount1'
      Origin = '#tMpSalePlan_Saler_Qry'
      ReadOnly = True
    end
    object AdoQry_Mainrateration1: TFloatField
      DisplayLabel = '一月|计划定额完成率%'
      FieldName = 'rateration1'
      Origin = '#tMpSalePlan_Saler_Qry'
      ReadOnly = True
    end
    object AdoQry_MaInPlansum_2: TFloatField
      DisplayLabel = '二月|计划额'
      FieldName = 'Plansum_2'
      Origin = '#tMpSalePlan_Saler_Qry'
    end
    object AdoQry_Mainrationsum_2: TFloatField
      DisplayLabel = '二月|销售定额'
      FieldName = 'rationsum_2'
      Origin = '#tMpSalePlan_Saler_Qry'
    end
    object AdoQry_MainrealSaleAmount_2: TFloatField
      DisplayLabel = '二月|实际销售额'
      FieldName = 'realSaleAmount_2'
      Origin = '#tMpSalePlan_Saler_Qry'
      ReadOnly = True
    end
    object AdoQry_MainDiffAmount2: TFloatField
      DisplayLabel = '二月|实际较计划额'
      FieldName = 'DiffAmount2'
      Origin = '#tMpSalePlan_Saler_Qry'
      ReadOnly = True
    end
    object AdoQry_MainratePlan2: TFloatField
      DisplayLabel = '二月|计划额完成率%'
      FieldName = 'ratePlan2'
      Origin = '#tMpSalePlan_Saler_Qry'
      ReadOnly = True
    end
    object AdoQry_MainDiffrationAmount2: TFloatField
      DisplayLabel = '二月|实际较计划定额'
      FieldName = 'DiffrationAmount2'
      Origin = '#tMpSalePlan_Saler_Qry'
      ReadOnly = True
    end
    object AdoQry_Mainrateration2: TFloatField
      DisplayLabel = '二月|计划定额完成率%'
      FieldName = 'rateration2'
      Origin = '#tMpSalePlan_Saler_Qry'
      ReadOnly = True
    end
    object AdoQry_MaInPlansum_3: TFloatField
      DisplayLabel = '三月|计划额'
      FieldName = 'Plansum_3'
      Origin = '#tMpSalePlan_Saler_Qry'
    end
    object AdoQry_Mainrationsum_3: TFloatField
      DisplayLabel = '三月|销售定额'
      FieldName = 'rationsum_3'
      Origin = '#tMpSalePlan_Saler_Qry'
    end
    object AdoQry_MainrealSaleAmount_3: TFloatField
      DisplayLabel = '三月|实际销售额'
      FieldName = 'realSaleAmount_3'