sal_qry_saleplanofdeptanalyse.pas

来自「一个MRPII系统源代码版本」· PAS 代码 · 共 450 行 · 第 1/5 页

    AdoQry_MainDiffrationAmount10: TFloatField;
    AdoQry_Mainrateration10: TFloatField;
    AdoQry_MaInPlansum_11: TFloatField;
    AdoQry_Mainrationsum_11: TFloatField;
    AdoQry_MainrealSaleAmount_11: TFloatField;
    AdoQry_MainDiffAmount11: TFloatField;
    AdoQry_MainratePlan11: TFloatField;
    AdoQry_MainDiffrationAmount11: TFloatField;
    AdoQry_Mainrateration11: TFloatField;
    AdoQry_MaInPlansum_12: TFloatField;
    AdoQry_Mainrationsum_12: TFloatField;
    AdoQry_MainrealSaleAmount_12: TFloatField;
    AdoQry_MainDiffAmount12: TFloatField;
    AdoQry_MainratePlan12: TFloatField;
    AdoQry_MainDiffrationAmount12: TFloatField;
    AdoQry_Mainrateration12: TFloatField;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
  public
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Sal_Qry_SalePlanOfDeptAnalyse: TFrm_Sal_Qry_SalePlanOfDeptAnalyse;

implementation
uses Sys_Global,Sal_Qry_SalePlanOfDeptAnalyse_C;
{$R *.DFM}
procedure TFrm_Sal_Qry_SalePlanOfDeptAnalyse.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var sqltext:string;
    Year:string;
    DeptCode1,DeptCode2:string;
begin
  Application.ProcessMessages;
  inherited;
    Year:='0';
    DeptCode1:='';
    DeptCode2:='';
  with TFrm_Sal_Qry_SalePlanOfDeptAnalyse_C.Create(self) do
  begin
    try
    AdoQry_tmp.Connection:=dbconnect;
    showmodal;
    if modalResult=mrok then
     begin
       Year:=edt_Year.text;
       DeptCode1:=edt_DeptCode1.Text;
       DeptCode2:=edt_DeptCode2.Text;
     end;
    finally
     Frm_Sal_Qry_SalePlanOfDeptAnalyse_C.Free;
     Frm_Sal_Qry_SalePlanOfDeptAnalyse_C:=nil;
    end;
  end;
  sqltext:=' select PlanYear,       '
          +#13+ '          Sal_SalePlan_Dept.DeptCode,   '
           +#13+'          Dept.DeptName,                '
           +#13+'          Sal_SalePlan_Dept.DeptCode+'' ''+Dept.DeptName as Deptflag,       '

           +#13+'          Plansum_Year,      '
           +#13+'          rationsum_Year,     '
           +#13+'          sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                   '
            +#13+'          -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleAmount,   '
            +#13+'         sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                     '
            +#13+'          -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )           '
            +#13+'         -Plansum_Year as DiffAmount,                                                                                          '
            +#13+ '         case when Plansum_Year=0 then 0                                                                                                     '
            +#13+ '         else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )               '
            +#13+ '               -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )      '
            +#13+ '              )/Plansum_Year *100 end as ratePlan,                                                                                 '
            +#13+ '         sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                     '
            +#13+ '          -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )           '
            +#13+ '         -rationsum_Year as DiffrationAmount,                                                                              '
            +#13+ '         case when rationsum_Year=0 then 0                                                                                                   '
            +#13+ '         else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )               '
            +#13+ '               -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )      '
            +#13+ '              )/rationsum_Year *100 end as rateration ,                                                                      '
            +#13+ '                                                                                                                                              '
            +#13+ '          Plansum_1,                                                                                                                          '
            +#13+ '          rationsum_1,                                                                                                                        '
            +#13+ '          sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=1 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )  '
            +#13+ '          -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=1 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end ) as realSaleAmount_1,   '
            +#13+ '         sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=1 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                                '
            +#13+ '          -sum(case when datepArt(yy,sa_BackShipOrder.Createdate)='+Year+' and datepArt(mm,sa_BackShipOrder.Createdate)=1 then isnull(sa_BackShipOrder.TotaltaxAmount,0) else 0 end )                                  '
            +#13+ '         -Plansum_1 as DiffAmount1,                                                                                                                                                               '
            +#13+  '        case when Plansum_1=0 then 0                                                                                                                                                                              '
            +#13+ '         else (sum(case when datepArt(yy,sa_ShipOrder.Createdate)='+Year+' and datepArt(mm,sa_ShipOrder.Createdate)=1 then isnull(sa_ShipOrder.TotaltaxAmount,0) else 0 end )                                          '