sal_qry_saleplanofitemanalyse.dfm

一个MRPII系统源代码版本

        Item
          FieldName = 'ratePlan7'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'DiffrationAmount7'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'rateration7'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'Plansum_8'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'rationsum_8'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'realSaleAmount8'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'realSaleqty8'
          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 = 'realSaleAmount9'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'realSaleqty9'
          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 = 'realSaleAmount10'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'realSaleqty10'
          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 = 'realSaleAmount11'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'realSaleqty11'
          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 = 'realSaleAmount12'
          PickList.Strings = ()
          KeyList.Strings = ()
        end
        Item
          FieldName = 'realSaleqty12'
          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 sa_ShipOrder.Createdate,sa_ShipOrderline.*'
      'into #sa_ShipOrder'
      'from   sa_ShipOrderline'
      
        'join   sa_ShipOrder on sa_ShipOrderline.Shipno=sa_ShipOrder.Ship' +
        'no'
      'and   datepArt(yy,createdate)=2002 and assess=1'
      '         '
      'select sa_BackShipOrder.Createdate,sa_BackShipOrderline.*'
      'into #sa_BackShipOrder'
      'from   sa_BackShipOrderline'
      
        'join   sa_BackShipOrder on sa_BackShipOrderline.BackShipno=sa_Ba' +
        'ckShipOrder.BackShipno'
      'and   datepArt(yy,createdate)=2002 and assess=1'
      ''
      ''
      ''
      ''
      'select PlanYear,'
      '       Sal_SalePlan_Item.ItemCode,'
      '       Item.ItemName,'
      '       Sal_SalePlan_Item.ItemCode+'#39' '#39'+Item.ItemName as Itemflag,'
      ''
      '       Plansum_Year,'
      '       rationsum_Year,'
      '       sum(isnull(#sa_ShipOrder.Shipqty,0))'
      
        '       -sum(isnull(#sa_BackShipOrder.Shipqty,0)) as realSaleqty,' +
        '--实际销售数量'
      '       sum(isnull(#sa_ShipOrder.taxAmount,0))'
      
        '       -sum(isnull(#sa_BackShipOrder.taxAmount,0)) as realSaleam' +
        'ount,--实际销售额'
      '      sum(isnull(#sa_ShipOrder.Shipqty,0) )'
      '       -sum(isnull(#sa_BackShipOrder.Shipqty,0) )'
      '      -rationsum_Year as DiffrationAmount,--实际较计划数量'
      '      case when rationsum_Year=0 then 0'
      '      else (sum(isnull(#sa_ShipOrder.Shipqty,0) )'
      '            -sum(isnull(#sa_BackShipOrder.Shipqty,0) )'
      '           )/rationsum_Year end as rateration,--计划数量完成率'
      ''
      '      sum(isnull(#sa_ShipOrder.taxAmount,0) )'
      '       -sum(isnull(#sa_BackShipOrder.taxAmount,0) )'
      '      -Plansum_Year as DiffAmount,--实际较计划金额'
      '      case when Plansum_Year=0 then 0'
      '      else (sum(isnull(#sa_ShipOrder.taxAmount,0) )'
      '            -sum(isnull(#sa_BackShipOrder.taxAmount,0) )'
      '           )/Plansum_Year end as ratePlan,--计划金额完成率'
      ''
      '       Plansum_1,'
      '       rationsum_1,'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=1 th' +
        'en isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=1 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as re' +
        'alSaleAmount1,--实际销售额'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=1 th' +
        'en isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=1 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as real' +
        'Saleqty1,--实际销售数量'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=1 the' +
        'n isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=1 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '      -Plansum_1 as DiffAmount1,--实际较计划额'
      '      case when Plansum_1=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=1 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=1 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '           )/Plansum_1 end as ratePlan1,--计划额完成率'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=1 the' +
        'n isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=1 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '      -rationsum_1 as DiffrationAmount1,--实际较计划数量'
      '      case when rationsum_1=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=1 then isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '            -sum(case when  datepArt(mm,#sa_BackShipOrder.Create' +
        'date)=1 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end )'
      '           )/rationsum_1 end as rateration1, --计划数量完成率'
      ''
      '       Plansum_2,'
      '       rationsum_2,'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=2 th' +
        'en isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=2 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end ) as re' +
        'alSaleAmount2,--实际销售额'
      
        '       sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=2 th' +
        'en isnull(#sa_ShipOrder.Shipqty,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=2 then isnull(#sa_BackShipOrder.Shipqty,0) else 0 end ) as real' +
        'Saleqty2,--实际销售数量'
      
        '      sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)=2 the' +
        'n isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'
      
        '       -sum(case when  datepArt(mm,#sa_BackShipOrder.Createdate)' +
        '=2 then isnull(#sa_BackShipOrder.taxAmount,0) else 0 end )'
      '      -Plansum_2 as DiffAmount2,--实际较计划额'
      '      case when Plansum_2=0 then 0'
      
        '      else (sum(case when  datepArt(mm,#sa_ShipOrder.Createdate)' +
        '=2 then isnull(#sa_ShipOrder.taxAmount,0) else 0 end )'