qc_qry_qcreport.pas

一个MRPII系统源代码版本

unit Qc_Qry_QcReport;

Interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Base_Qry, Menus, ExtPrintReport, Db, ActnList, AdODB, Grids, DBGridEh,
  StdCtrls, ExtCtrls, ComCtrls, ToolWin, jpeg;

Type
  TFrm_Qc_Qry_QcReport = Class(TFrm_Base_Qry)
    AdoQry_MaInvendorCode: TStringField;
    AdoQry_MainallItem: TStringField;
    AdoQry_MainallQc: TIntegerField;
    AdoQry_Mainillage: TIntegerField;
    AdoQry_MainQcpercent: TFloatField;
    AdoQry_MaindispQcpercent: TStringField;
    procedure FormDestroy(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Act_FilterExecute(Sender: TObject);
    procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
  private
    procedure SetMySql;
    { Private declarations }
  public
    { Public declarations }
    stArtMonth,endMonth:string;
    procedure InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);Override;
  end;

var
  Frm_Qc_Qry_QcReport: TFrm_Qc_Qry_QcReport;

implementation
uses  Qc_Qry_QcReport_Condition;
{$R *.DFM}

{ TFrm_Qc_Qry_QcReport }


{ TFrm_Qc_Qry_QcReport }

procedure TFrm_Qc_Qry_QcReport.InitForm(AdOConnection: TAdOConnection;
  ReadOnly: Boolean);
begin
  inherited;
  SetMySql;
  getdata;
  Frm_Sys_Condition:=TFrm_Qc_Qry_QcReport_Condition.Create(self);
end;

procedure TFrm_Qc_Qry_QcReport.FormDestroy(Sender: TObject);
begin
  inherited;
  Frm_Qc_Qry_QcReport:=nil;
end;

procedure TFrm_Qc_Qry_QcReport.FormCreate(Sender: TObject);
begin
  inherited;
  StArtMonth:=formatdatetime('yyyy.mm',date-366);
  endMonth:=formatdatetime('yyyy.mm',date);
end;

procedure TFrm_Qc_Qry_QcReport.Act_FilterExecute(Sender: TObject);
begin
  TFrm_Qc_Qry_QcReport_Condition(Frm_Sys_Condition).Lc_StArtMonth:=StArtMonth;
  TFrm_Qc_Qry_QcReport_Condition(Frm_Sys_Condition).Lc_EndMonth:=endMonth;
  TFrm_Qc_Qry_QcReport_Condition(Frm_Sys_Condition).Showmodal;
  if TFrm_Qc_Qry_QcReport_Condition(Frm_Sys_Condition).ModalResult=MrOk then
  begin
    StArtMonth:=TFrm_Qc_Qry_QcReport_Condition(Frm_Sys_Condition).Lc_StArtMonth;
    endMonth:=TFrm_Qc_Qry_QcReport_Condition(Frm_Sys_Condition).Lc_EndMonth;
    SetMySql;
    GetData;
  end;
  //  inherited;
end;

procedure TFrm_Qc_Qry_QcReport.SetMySql;
begin
  SelectFromSql:=' '+
  'select ff.VendorCode+'' ''+Vendor.VendorName VendorCode, '+
  '  allItem, '+
  '  allQc, '+
  '  illage, '+
  '  Qcpercent,'+
  '  dispQcpercent '+
  'from '+
  ' ( select VendorCode, '+
  '     ''汇总'' allItem, '+
  '     allQc, '+
  '     illage, '+
  '     convert(decimal(20,4),(allQc-illage))/convert(decimal(20,4),allQc)*100 as Qcpercent, '+
  '     convert(varchAr(50),convert(decimal(10,2),convert(decimal(20,4),(allQc-illage))/convert(decimal(20,4),allQc)*100))+''%'' as dispQcpercent '+
  '   from '+
  '    ( select '+
  '        VendorCode, '+
  '        case when allQc  is null then 0 else allQc end allQc, '+
  '        case when illage is null then 0 else illage end illage '+
  '      from '+
  '       ( select  '+
  '           cc1.VendorCode, '+
  '           cc1.allQc, '+
  '           cc2.illage '+
  '         from '+
  '          ( select  '+
  '              VendorCode,'+
  '              Count(*) as allQc '+
  '            from '+
  '             ( select distinct  '+
  '                 VendorCode, '+
  '                 QcBatchno '+
  '               from '+
  '                ( select * '+
  '                  from '+
  '                   ( select  '+
  '                       a.VendorCode, '+
  '                       b.QcBatchno '+
  '                     from oncheckBill a, '+
  '                          oncheckBillline b '+
  '                     where a.oncheckBillid=b.oncheckBillid '+
  '                       and b.Qcstatus<>0 '+
  '                       and Convert(varchAr(7),a.oncheckdate,102)>='''+StArtMonth+''' '+
  '                       and Convert(varchAr(7),a.oncheckdate,102)<='''+endMonth+''' '+
  '                    ) bb '+
  '                  where bb.QcBatchno is not null '+
  '                    and bb.QcBatchno<>'''' '+
  '                 ) aa '+
  '              ) cc '+
  '            group by VendorCode '+
  '        ) cc1 '+
  '    left join '+
  '      ( select VendorCode, '+
  '          Count(*) as illage '+
  '        from '+
  '        ( select distinct '+
  '            VendorCode,QcBatchno '+
  '          from '+
  '          ( select * '+
  '            from '+
  '             ( select a.VendorCode, '+
  '                 b.QcBatchno '+
  '               from oncheckBill a, '+
  '                    oncheckBillline b '+
  '               where a.oncheckBillid=b.oncheckBillid '+
  '                 and b.Qcstatus<>0 '+
  '                 and b.Qcstatus<>1 '+
  '                 and Convert(varchAr(7),a.oncheckdate,102)>='''+StArtMonth+''' '+
  '                 and Convert(varchAr(7),a.oncheckdate,102)<='''+endMonth+''' '+
  '              ) bb '+
  '            where bb.QcBatchno is not null '+
  '              and bb.QcBatchno<>'''' '+
  '           ) aa '+
  '       ) cc '+
  '  group by VendorCode '+
  '  ) cc2 '+
  '  on cc1.VendorCode=cc2.VendorCode '+
  '  ) dd '+
  '  ) ee  '+
  ' '+
  ' union '+
  ' select VendorCode, '+
  '   ee.ItemCode+'' ''+Item.ItemName allItem, '+
  '   allQc, '+
  '   illage, '+
  '   convert(decimal(20,4),(allQc-illage))/convert(decimal(20,4),allQc)*100 as Qcpercent, '+
  '   convert(varchAr(50),convert(decimal(10,2),convert(decimal(20,4),(allQc-illage))/convert(decimal(20,4),allQc)*100))+''%'' as dispQcpercent '+
  ' from '+
  '  ( select '+
  '      VendorCode, '+
  '      ItemCode, '+
  '      case when allQc  is null then 0 else allQc end allQc, '+
  '      case when illage is null then 0 else illage end illage '+
  '    from '+
  '     ( select cc1.VendorCode, '+
  '         cc1.ItemCode,  '+
  '         cc1.allQc, '+
  '         cc2.illage '+
  '       from '+
  '        ( select VendorCode, '+
  '            ItemCode, '+
  '            Count(*) as allQc '+
  '          from '+
  '           ( select distinct '+
  '               VendorCode, '+
  '               QcBatchno, '+
  '               ItemCode '+
  '             from '+
  '              ( select * '+
  '                from '+
  '                 ( select a.VendorCode , '+
  '                     b.QcBatchno , '+
  '                     b.ItemCode '+
  '                   from oncheckBill a, '+
  '                        oncheckBillline b '+
  '                   where a.oncheckBillid=b.oncheckBillid '+
  '                     and b.Qcstatus<>0 '+
  '                     and Convert(varchAr(7),a.oncheckdate,102)>='''+StArtMonth+''' '+
  '                     and Convert(varchAr(7),a.oncheckdate,102)<='''+endMonth+''' '+
  '                  ) bb '+
  '                where bb.QcBatchno is not null '+
  '                  and bb.QcBatchno<>'''' '+
  '               ) aa '+
  '            ) cc '+
  '           group by VendorCode,ItemCode '+
  '     ) cc1 '+
  ' left join '+
  '  (select VendorCode,ItemCode,Count(*) as illage '+
  '   from '+
  '    (select distinct VendorCode,QcBatchno,ItemCode '+
  '     from '+
  '      ( select * '+
  '        from '+
  '         ( select a.VendorCode , '+
  '             b.QcBatchno , '+
  '             b.ItemCode '+
  '           from oncheckBill a, '+
  '                oncheckBillline b '+
  '           where a.oncheckBillid=b.oncheckBillid '+
  '             and b.Qcstatus<>0 '+
  '             and b.Qcstatus<>1 '+
  '             and Convert(varchAr(7),a.oncheckdate,102)>='''+StArtMonth+''' '+
  '             and Convert(varchAr(7),a.oncheckdate,102)<='''+endMonth+''' '+
  '          ) bb '+
  '        where bb.QcBatchno is not null '+
  '          and bb.QcBatchno<>'''' '+
  '      ) aa '+
  '     ) cc '+
  '    group by VendorCode,ItemCode '+
  '   ) cc2 '+
  ' on cc1.VendorCode=cc2.VendorCode and cc1.ItemCode=cc2.ItemCode '+
  ' ) dd '+
  ' ) ee ,Item '+
  ' where ee.ItemCode=Item.ItemCode '+
  '  ) ff ,Vendor'+
  ' where ff.VendorCode=Vendor.VendorCode '+
  ' Order by VendorCode,allItem '+
  ' ';
end;

procedure TFrm_Qc_Qry_QcReport.DBGridEhGetCellParams(Sender: TObject;
  Column: TColumnEh; AFont: TFont; var Background: TColor;
  State: TGridDrawState);
begin
  inherited;
  if AdoQry_Main.fieldbyname('allItem').asString='汇总' then
  begin
    AFont.Name:='黑体';
    AFont.Style:=[fsBold];
  end;
end;

end.