qc_qry_qcreport1.pas

一个MRPII系统源代码版本

unit Qc_Qry_QcReport1;

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_QcReport1 = Class(TFrm_Base_Qry)
    AdoQry_tmp2: TAdoQuery;
    Label1: TLabel;
    Label2: TLabel;
    procedure Act_FilterExecute(Sender: TObject);
  private
    { Private declarations }
    stArtMonth,endMonth:string;
    fanwei:integer;
    procedure getQcReportdata;
  public
    { Public declarations }
    procedure InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);Override;
  end;

var
  Frm_Qc_Qry_QcReport1: TFrm_Qc_Qry_QcReport1;

implementation

uses Qc_Qry_QcReport_Condition;

{$R *.DFM}

procedure TFrm_Qc_Qry_QcReport1.InitForm(AdOConnection:TAdOConnection;ReadOnly:Boolean);
begin
  inherited;
  AdoQry_tmp2.Connection:=AdoQry_Main.Connection;
  Frm_Qc_Qry_QcReport_Condition:=TFrm_Qc_Qry_QcReport_Condition.Create(self);
  if Frm_Qc_Qry_QcReport_Condition.Showmodal=mrok then
  begin
    getQcReportdata;
  end;
end;

procedure TFrm_Qc_Qry_QcReport1.getQcReportdata;
begin
    stArtMonth:=Frm_Qc_Qry_QcReport_Condition.lc_StArtMonth;
    endMonth:=Frm_Qc_Qry_QcReport_Condition.lc_EndMonth;
    fanwei:=Frm_Qc_Qry_QcReport_Condition.tjfanwei;
    with AdoQry_tmp do
    begin
      Close;
      sql.text:='drop table #QcReport ';
      try
        ExecSQL;
      except
      end;
      Close;
      sql.text:='create table #QcReport(QcClass varchAr(40),'
                +'ItemCode varchAr(16),VendorCode varchAr(12),'
                +'allqty decimal(20,8),illqty decimal(20,8),allQc int,illQc int,'
                +'okrate decimal(10,2),czff varchAr(400) default '''','
                +'illdate varchAr(400) default '''')';
      ExecSQL;
      //先插入供应商及代码,从待检单中
      if fanwei<>2 then
      begin
        Close;
        sql.text:=' insert #QcReport(VendorCode,ItemCode) select distinct i.VendorCode,il.ItemCode '
                 +' from oncheckBill i,oncheckBillline il '
                 +' where i.oncheckBillid=il.oncheckBillid '
                 +' and i.oncheckMonth>='+QuotedStr(stArtMonth)
                 +' and i.oncheckMonth<='+QuotedStr(endMonth)
                 +' and il.Qcstatus<>0 '
                 +' Order by VendorCode,ItemCode ' ;
        ExecSQL;
      end;
      //从入库单中
      if fanwei<>1 then
      begin
        Close;
        sql.text:=' insert #QcReport(VendorCode,ItemCode) select distinct i.VendorCode,il.ItemCode '
                 +' from InvInBill i,InvInBillline il '
                 +' where i.InvBillid=il.InvBillid '
                 +' and i.InvBillMonth>='+QuotedStr(stArtMonth)
                 +' and i.InvBillMonth<='+QuotedStr(endMonth)
                 +' and i.VendorCode+il.ItemCode not in (select VendorCode+ItemCode from #QcReport) '
                 +' and i.fromCheckIn=0 and i.VendorCode is not null and i.pono is not null '
                 +' and il.InvBillqty>0 '
                 +' Order by VendorCode,ItemCode ' ;
        ExecSQL;
      end;

      //计算出检验批次数
      if fanwei<>2 then
      begin
        Close;
        sql.text:=' update #QcReport set allQc=b.QcCount from '
                 +' (select VendorCode,ItemCode,Count(*) as QcCount from '
                 +' (select distinct i.VendorCode,il.ItemCode,QcBatchno '
                 +' from oncheckBill i,oncheckBillline il '
                 +' where i.oncheckBillid=il.oncheckBillid '
                 +' and i.oncheckMonth>='+QuotedStr(stArtMonth)
                 +' and i.oncheckMonth<='+QuotedStr(endMonth)
                 +' and il.Qcstatus<>0 '
                 +' ) a '
                 +' group by VendorCode,ItemCode) b '
                 +' where #QcReport.VendorCode=b.VendorCode and #QcReport.ItemCode=b.ItemCode';
        ExecSQL;
      end;
      if fanwei<>1 then
      begin
        Close;
        sql.text:=' update #QcReport set allQc=isnull(allQc,0)+b.QcCount from '
                 +' (select VendorCode,ItemCode,Count(*) as QcCount from '
                 +' (select distinct i.VendorCode,il.ItemCode,i.InvBillid '
                 +' from InvInBill i,InvInBillline il '
                 +' where i.InvBillid=il.InvBillid '
                 +' and i.InvBillMonth>='+QuotedStr(stArtMonth)
                 +' and i.InvBillMonth<='+QuotedStr(endMonth)
                 +' and il.InvBillqty>0 '
                 +' and i.fromCheckIn=0 and i.VendorCode is not null and i.pono is not null '
                 +' ) a '
                 +' group by VendorCode,ItemCode) b '
                 +' where #QcReport.VendorCode=b.VendorCode and #QcReport.ItemCode=b.ItemCode';
        ExecSQL;
      end;
      //计算不合格批次数
      if fanwei<>2 then
      begin
        Close;
        sql.text:=' update #QcReport set illQc=b.QcCount from '
                 +' (select VendorCode,ItemCode,Count(*) as QcCount from '
                 +' (select distinct i.VendorCode,il.ItemCode,QcBatchno '
                 +' from oncheckBill i,oncheckBillline il '
                 +' where i.oncheckBillid=il.oncheckBillid '
                 +' and i.oncheckMonth>='+QuotedStr(stArtMonth)
                 +' and i.oncheckMonth<='+QuotedStr(endMonth)
                 +' and Qcstatus<>1 and Qcstatus<>0) a '
                 +' group by VendorCode,ItemCode) b '
                 +' where #QcReport.VendorCode=b.VendorCode and #QcReport.ItemCode=b.ItemCode';
        ExecSQL;
      end;
      //计算供方送货数
      if fanwei<>2 then
      begin
        Close;
        sql.text:=' update #QcReport set allqty=b.allqty from '
                 +' (select i.VendorCode,il.ItemCode,sum(il.oncheckqty) as allqty '
                 +' from oncheckBill i,oncheckBillline il '
                 +' where i.oncheckBillid=il.oncheckBillid '
                 +' and i.oncheckMonth>='+QuotedStr(stArtMonth)
                 +' and i.oncheckMonth<='+QuotedStr(endMonth)
                 +' and il.Qcstatus<>0 '    //0:未处理
                 +' group by VendorCode,ItemCode) b '
                 +' where #QcReport.VendorCode=b.VendorCode and #QcReport.ItemCode=b.ItemCode';
        ExecSQL;
      end;
      if fanwei<>1 then
      begin
        Close;
        sql.text:=' update #QcReport set allqty=isnull(#QcReport.allqty,0)+b.allqty from '
                 +' (select i.VendorCode,il.ItemCode,sum(il.InvBillqty) as allqty '
                 +' from InvInBill i,InvInBillline il '
                 +' where i.InvBillid=il.InvBillid '
                 +' and i.InvBillMonth>='+QuotedStr(stArtMonth)
                 +' and i.InvBillMonth<='+QuotedStr(endMonth)
                 +' and il.InvBillqty<>0 '
                 +' and i.fromCheckIn=0 and i.VendorCode is not null '
    //             +' and il.InvBillqty>0 '
    //             +' and i.fromCheckIn=0 and i.VendorCode is not null and i.pono is not null '  //2002.05.17
                 +' group by VendorCode,ItemCode) b '
                 +' where #QcReport.VendorCode=b.VendorCode and #QcReport.ItemCode=b.ItemCode';
        ExecSQL;
      end;

      //计算不合格数量
      if fanwei<>2 then
      begin
        Close;
        sql.text:=' update #QcReport set illqty=b.illqty from '
                 +' (select i.VendorCode,il.ItemCode,sum(il.oncheckqty) as illqty '
                 +' from oncheckBill i,oncheckBillline il '
                 +' where i.oncheckBillid=il.oncheckBillid '
                 +' and i.oncheckMonth>='+QuotedStr(stArtMonth)
                 +' and i.oncheckMonth<='+QuotedStr(endMonth)
                 +' and il.Qcstatus<>0 and il.Qcstatus<>1 '    //0:未处理
                 +' group by VendorCode,ItemCode) b '
                 +' where #QcReport.VendorCode=b.VendorCode and #QcReport.ItemCode=b.ItemCode';
        ExecSQL;
      end;

      //计算合格率
      Close;
      sql.text:=' update #QcReport set okrate=100-round((isnull(illQc,0)/convert(float,allQc))*100,0)'
                +' where allQc<>0 ';
      ExecSQL;
      //统计处置方法及不合格日期
      if fanwei<>2 then
      begin
        Close;
        sql.text:=' select i.VendorCode,i.oncheckdate,il.ItemCode,'
                  +' il.Qcstatus,il.QcBatchno,il.receivedqty'
                  +' from oncheckBill i,oncheckBillline il'
                  +' where i.oncheckBillid=il.oncheckBillid '
                  +' and i.oncheckMonth>='+QuotedStr(stArtMonth)
                  +' and i.oncheckMonth<='+QuotedStr(endMonth)
                  +' and il.Qcstatus<>0 and il.Qcstatus<>1 '
                  +' Order by VendorCode,ItemCode,QcBatchno';
        open;
        First;
        while not eof do
        begin
          AdoQry_tmp2.Close;
          case fieldbyname('Qcstatus').Value of
            2: AdoQry_tmp2.sql.text:=' update #QcReport set czff=czff+''拒收//'','
                                +' illdate=illdate+'
                                +quotedstr(copy(fieldbyname('oncheckdate').asstring,6,5))
                                +'+''//'''
                                +' where #QcReport.VendorCode='
                                +QuotedStr(fieldbyname('VendorCode').asstring)
                                +' and #QcReport.ItemCode='
                                +QuotedStr(fieldbyname('ItemCode').asstring);
            3: AdoQry_tmp2.sql.text:=' update #QcReport set czff=czff+''让步//'','
                                +' illdate=illdate+'
                                +quotedstr(copy(fieldbyname('oncheckdate').asstring,6,5))
                                +'+''//'''
                                +' where #QcReport.VendorCode='
                                +QuotedStr(fieldbyname('VendorCode').asstring)
                                +' and #QcReport.ItemCode='
                                +QuotedStr(fieldbyname('ItemCode').asstring);

            4: AdoQry_tmp2.sql.text:=' update #QcReport set czff=czff+''挑选//'','
                                +' illdate=illdate+'
                                +quotedstr(copy(fieldbyname('oncheckdate').asstring,6,5))
                                +'+''//'''
                                +' where #QcReport.VendorCode='
                                +QuotedStr(fieldbyname('VendorCode').asstring)
                                +' and #QcReport.ItemCode='
                                +QuotedStr(fieldbyname('ItemCode').asstring);

            5: AdoQry_tmp2.sql.text:=' update #QcReport set czff=czff+''试用//'','
                                +' illdate=illdate+'
                                +quotedstr(copy(fieldbyname('oncheckdate').asstring,6,5))
                                +'+''//'''
                                +' where #QcReport.VendorCode='
                                +QuotedStr(fieldbyname('VendorCode').asstring)
                                +' and #QcReport.ItemCode='
                                +QuotedStr(fieldbyname('ItemCode').asstring);

          else AdoQry_tmp2.sql.text:=' update #QcReport set czff=czff+''特采//'','
                                +' illdate=illdate+'
                                +quotedstr(copy(fieldbyname('oncheckdate').asstring,6,5))
                                +'+''//'''
                                +' where #QcReport.VendorCode='
                                +QuotedStr(fieldbyname('VendorCode').asstring)
                                +' and #QcReport.ItemCode='
                                +QuotedStr(fieldbyname('ItemCode').asstring);

          end;
          AdoQry_tmp2.ExecSQL;
          next;
        end;
      end;
      {
      if fanwei<>1 then
      begin
        Close;
        sql.text:=' select i.VendorCode,i.InvBilldate,il.ItemCode '
                  +' from InvInBill i,InvInBillline il'
                  +' where i.InvBillid=il.InvBillid '
                  +' and i.InvBillMonth>='+QuotedStr(stArtMonth)
                  +' and i.InvBillMonth<='+QuotedStr(endMonth)
                  +' and il.InvBillqty<0 '
                  +' and i.fromCheckIn=0 and i.VendorCode is not null and i.pono is not null '
                  +' Order by VendorCode,ItemCode,InvBilldate ';
        open;
        First;
        while not eof do
        begin
          AdoQry_tmp2.Close;
          AdoQry_tmp2.sql.text:=' update #QcReport set czff=czff+''退货//'','
                                +' illdate=illdate+'
                                +quotedstr(copy(fieldbyname('InvBilldate').asstring,6,5))
                                +'+''//'''
                                +' where #QcReport.VendorCode='
                                +QuotedStr(fieldbyname('VendorCode').asstring)
                                +' and #QcReport.ItemCode='
                                +QuotedStr(fieldbyname('ItemCode').asstring);
          AdoQry_tmp2.ExecSQL;
          next;
        end;
      end;  }
    end;
    //显示数据
    with AdoQry_Main do
    begin

      Close;
      sql.text:=' select Item.QcClass,#QcReport.ItemCode,Item.ItemName,'
               +' #QcReport.VendorCode,Vendor.VendorName,#QcReport.allqty,'
               +' #QcReport.illqty,#QcReport.allQc,'
               +' #QcReport.illQc,#QcReport.okrate,#QcReport.czff,'
               +' #QcReport.illdate '
               +' from #QcReport'
               +' left join Item on Item.ItemCode=#QcReport.ItemCode'
               +' left join Vendor on Vendor.VendorCode=#QcReport.VendorCode'
               +' Order by #QcReport.ItemCode,#QcReport.VendorCode';
      open;
    end;

end;

procedure TFrm_Qc_Qry_QcReport1.Act_FilterExecute(Sender: TObject);
begin
//  inherited;
  Frm_Qc_Qry_QcReport_Condition:=TFrm_Qc_Qry_QcReport_Condition.Create(self);
  if Frm_Qc_Qry_QcReport_Condition.Showmodal=mrok then
  begin
    getQcReportdata;
  end;

end;

end.