sal_qry_saleplanofsaleranalyse.dfm

一个MRPII系统源代码版本

          FieldName = 'rationsum_8'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'realSaleAmount_8'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'DiffAmount8'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'ratePlan8'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'DiffrationAmount8'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'rateration8'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'Plansum_9'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'rationsum_9'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'realSaleAmount_9'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'DiffAmount9'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'ratePlan9'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'DiffrationAmount9'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'rateration9'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'Plansum_10'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'rationsum_10'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'realSaleAmount_10'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'DiffAmount10'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'ratePlan10'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'DiffrationAmount10'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'rateration10'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'Plansum_11'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'rationsum_11'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'realSaleAmount_11'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'DiffAmount11'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'ratePlan11'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'DiffrationAmount11'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'rateration11'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'Plansum_12'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'rationsum_12'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'realSaleAmount_12'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'DiffAmount12'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'ratePlan12'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'DiffrationAmount12'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'rateration12'
          PickList.Strings = ()
          KeyList.Strings = ()
        end>
    end
  end
  inherited ActionList: TActionList
    Left = 208
    Top = 208
  end
  inherited AdoQry_Main: TAdoQuery
    ConnectionString = 
      'Provider=SQLOLEDB.1;Password=mdrcmrm;Persist Security Info=True;' +
      'User ID=mdrcmrm;Initial Catalog=Mrpii_Standard;Data Source=192.1' +
      '68.0.151;Use procedure for Prepare=1;Auto Translate=True;Packet ' +
      'Size=4096;Workstation ID=XYB'
    SQL.Strings = (
      ''
      'select PlanYear,'
      '       Sal_SalePlan_Saler.SaleEmployeeCode,'
      '       Employee.EmployeeName,'
      
        '       Sal_SalePlan_Saler.SaleEmployeeCode+'#39' '#39'+Employee.Employee' +
        'Name as Employeeflag,'
      ''
      '       Plansum_Year,'
      '       rationsum_Year,'
      
        '       sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 t' +
        'hen isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) ' +
        'as realSaleAmount,--实际销售额'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 th' +
        'en isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -Plansum_Year as DiffAmount,--实际较计划额'
      '      case when Plansum_Year=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )'
      
        '            -sum(case when datepArt(yy,sa_BackShipOrder.Createda' +
        'te)=2002 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 e' +
        'nd )'
      '           )/Plansum_Year end as ratePlan,--计划额完成率'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 th' +
        'en isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -rationsum_Year as DiffrationAmount,--实际较计划定额'
      '      case when rationsum_Year=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )'
      
        '            -sum(case when datepArt(yy,sa_BackShipOrder.Createda' +
        'te)=2002 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 e' +
        'nd )'
      '           )/rationsum_Year end as rateration ,--计划定额完成率'
      ''
      '       Plansum_1,'
      '       rationsum_1,'
      
        '       sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 a' +
        'nd datepArt(mm,sa_ShipOrder.Createdate)=1 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)=1 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleAmount' +
        ',--实际销售额'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=1 then isnull(sa_Shiporde' +
        'r.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=1 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -Plansum_1 as DiffAmount1,--实际较计划额'
      '      case when Plansum_1=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=1 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)=1 then isn' +
        'ull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/Plansum_1 end as ratePlan1,--计划额完成率'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=1 then isnull(sa_Shiporde' +
        'r.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=1 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -rationsum_1 as DiffrationAmount1,--实际较计划定额'
      '      case when rationsum_1=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=1 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)=1 then isn' +
        'ull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/rationsum_1 end as rateration1, --计划定额完成率'
      ''
      '       Plansum_2,'
      '       rationsum_2,'
      
        '       sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 a' +
        'nd datepArt(mm,sa_ShipOrder.Createdate)=2 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)=2 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleAmount' +
        ',--实际销售额'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=2 then isnull(sa_Shiporde' +
        'r.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=2 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -Plansum_2 as DiffAmount2,--实际较计划额'
      '      case when Plansum_2=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=2 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)=2 then isn' +
        'ull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/Plansum_2 end as ratePlan2,--计划额完成率'
      
        '      sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2002 an' +
        'd datepArt(mm,sa_ShipOrder.Createdate)=2 then isnull(sa_Shiporde' +
        'r.TotaltaxAmount,0) else 0 end )'
      
        '       -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_BackShipOrder.Createdate)=2 then isnull(s' +
        'a_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '      -rationsum_2 as DiffrationAmount2,--实际较计划定额'
      '      case when rationsum_2=0 then 0'
      
        '      else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)=2' +
        '002 and datepArt(mm,sa_ShipOrder.Createdate)=2 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)=2 then isn' +
        'ull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )'
      '           )/rationsum_2 end as rateration2, --计划定额完成率'
      ''
      '       Plansum_3,'
      '       rationsum_3,'