sal_qry_saleplanofsaleranalyse.pas

一个MRPII系统源代码版本

    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;
    AdoQry_MainSaleEmployeeCode: TStringField;
    AdoQry_MainEmployeeName: TStringField;
    AdoQry_MainEmployeeflag: TStringField;
    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_SalePlanOfSalerAnalyse: TFrm_Sal_Qry_SalePlanOfSalerAnalyse;

implementation
uses Sys_Global,Sal_Qry_SalePlanOfSalerAnalyse_C;
{$R *.DFM}
procedure TFrm_Sal_Qry_SalePlanOfSalerAnalyse.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var sqltext:string;
    Year:string;
    EmployeeCode1,EmployeeCode2:string;
begin
  Application.ProcessMessages;
  inherited;
    Year:='0';
    EmployeeCode1:='';
    EmployeeCode2:='';
  with TFrm_Sal_Qry_SalePlanOfSalerAnalyse_C.Create(self) do
  begin
    try
    AdoQry_tmp.Connection:=dbconnect;
    showmodal;
    if modalResult=mrok then
     begin
        Year:=edt_Year.text;
        EmployeeCode1:=edt_DeptCode1.Text;
        EmployeeCode2:=edt_DeptCode2.Text;
     end;
    finally
     Frm_Sal_Qry_SalePlanOfSalerAnalyse_C.Free;
     Frm_Sal_Qry_SalePlanOfSalerAnalyse_C:=nil;
    end;
  end;
  sqltext:=' select PlanYear,       '
          +#13+ '          Sal_SalePlan_Saler.SaleEmployeeCode,   '
           +#13+'          Employee.EmployeeName,                '
           +#13+'          Sal_SalePlan_Saler.SaleEmployeeCode+'' ''+Employee.EmployeeName as Employeeflag,       '

           +#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 )                                          '