mrp_qry_oblongmps.pas

一个MRPII系统源代码版本

unit Mrp_Qry_OblongMps;

Interface

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

Type
  TFrm_Mrp_Qry_OblongMps = Class(TFrm_Base_Qry)
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    DbTxt_Month: TDBText;
    DbTxt_ItemFlag: TDBText;
    DbTxt_MonthQty: TDBText;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Act_FilterExecute(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure Act_LookExecute(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
      { Private declarations }
  public
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Mrp_Qry_OblongMps: TFrm_Mrp_Qry_OblongMps;

implementation

uses Sys_Global, Mrp_Qry_OblongMps_D, Public_Show;

{$R *.DFM}

procedure TFrm_Mrp_Qry_OblongMps.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var
  I,J,x,y:Integer;
  SqlText:String;
  s,UpdateField:String;
  AdoQuery:TAdoQuery;
begin
  Application.ProcessMessages;
  Inherited;
  Frm_Public_Show.Lbl_Show.Caption:='正在统计数据，请稍等......';
  Frm_Public_Show.Visible:=True;                //________________
  Application.ProcessMessages;
  SelectFromSql:=' Select * from #tmpdayMps';
 { For I:=1 To 31 Do
  begin
    SelectFromSql:=SelectFromSql+' Sum(#TmPmps.['+IntToStr(I)+'日]) As ['+IntToStr(I)+'日] ,'
  end;
  SelectFromSql:=SelectFromSql+' #TmpOblongdayMps.MpsMonth,#TmpOblongdayMps.ItemCode,Sum(MonthTotalQty) As MonthTotalQty,Item.ItemName,Uom.UomName,'
                +' Item.ItemCode+'''+' '+'''+Item.ItemName As ItemFlag '
                +' From #TmpOblongdayMps '
                +' left Join Item On #TmpOblongdayMps.ItemCode=Item.ItemCode '
                +' Left Join Uom On Item.UomCode=Uom.UomCode ';
  GroupByFields:=' #TmpOblongdayMps.ItemCode,Item.ItemName,Uom.UomName,#TmpOblongdayMps.MpsMonth,Item.ItemCode+'''+' '+'''+Item.ItemName';}
  Try
    SqlText:=' Create Table #TmpOblongdayMps ( '
            +' MpsMonth varchAr(7)  Null ,'
            +' ItemCode varChAr(20)  Null,'
            +' MonthTotalQty  varChAr(20) Null,'  //Float(12)
            +' MpsDate Datetime Null,';
    For I:=1 To 30 Do
    begin
      SqlText:=SqlText+'['+IntToStr(I)+'日]   Float(12)  Null ,';
    end;
    SqlText:=SqlText+'['+IntToStr(31)+'日]   Float(12)  Null )';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.ExecSQL;
  Except
    Sqltext:=' Drop Table #TmpOblongdayMps ';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.ExecSQL;
    SqlText:=' Create Table #TmpOblongdayMps ( '
            +' MpsMonth varchAr(7)  Null ,'
            +' ItemCode varChAr(20)  Null,'
            +' MonthTotalQty  varChAr(20) Null,'
            +' MpsDate Datetime Null,';
    For I:=1 To 30 Do
    begin
      SqlText:=SqlText+'['+IntToStr(I)+'日]   Float(12)  Null ,';
    end;
    SqlText:=SqlText+'['+IntToStr(31)+'日]   Float(12)  Null )';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.ExecSQL;
  end;
  Frm_Public_Show.Lbl_Show.Caption:='正在统计数据,已完成......1%!';
  Application.ProcessMessages;  //________________

 //===chb begin
  SQLText :='Select Distinct MpsMonth From Mps Order by MpsMonth';
  AdoQry_Main.Close;
  AdoQry_Main.SQL.Text:=SqlText;
  AdoQry_Main.Open;
  x:=AdoQry_Main.recordCount;
  AdoQry_Main.First;
  Y:=1;
  while not AdoQry_Main.Eof do
  begin
    Frm_Public_Show.Lbl_Show.Caption:='正在统计数据,已完成......'+FormatFloat('##0',(y/x)*70+30)+'%!';
    SQlText:=' Select ItemCode,Sum(MpsQty) As MpsQty,MpsMonth '
          +' From Mps  where Mpsstatus<4 '
          +' Group By ItemCode,MpsMonth '
          +' having MpsMonth='''+AdoQry_Main.fieldbyname('MpsMonth').AsString+'''';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.Open;
    while not AdoQry_Tmp.Eof do
    begin
      SqlText:=' Insert #TmpOblongdayMps '
            +' (MpsMonth,ItemCode,MonthTotalQty) '
            +' Values('
            +''''+AdoQry_Tmp.fieldbyname('MpsMonth').AsString+''','
            +''''+AdoQry_Tmp.fieldbyname('ItemCode').AsString+''','
            +''''+AdoQry_Tmp.fieldbyname('MpsQty').AsString+''')';
      AdoQuery:=TAdoQuery.Create(nil);
      AdoQuery.Connection :=DbConnect;
      AdoQuery.Close;
      AdoQuery.SQL.Text:=SqlText;
      AdoQuery.ExecSQL;

      AdoQry_Tmp.Next;
    end;
    SqlText:=' Select ItemCode,MpsMonth,MpsDate,Sum(MpsQty) As MpsQty '
            +' From Mps where Mpsstatus<4 '
            +' Group By ItemCode,MpsDate,MpsMonth '
            +' having MpsMonth='''+AdoQry_Main.fieldbyname('MpsMonth').AsString+'''';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.Prepared;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.Open;
    While Not AdoQry_Tmp.Eof Do
    begin
      Application.ProcessMessages;                                    //_____________________
      s:=Copy(FormatDateTime('yyyy.mm.dd',AdoQry_Tmp.fieldbyname('MpsDate').AsDateTime),9,10);
      Case StrToInt(S) Of
        1..9:UpdateField:='['+Inttostr(StrToInt(s))+'日]';
        10..31:UpdateField:='['+Inttostr(StrToInt(s))+'日]';
      end;
      SqlText:=' Update #TmpOblongdayMps '
              +' Set '+UpdateField+'='''+AdoQry_Tmp.fieldbyname('MpsQty').AsString+''''
              +' Where ItemCode='''+AdoQry_Tmp.fieldbyname('ItemCode').AsString+''''
              +' And MpsMonth='''+AdoQry_Tmp.fieldbyname('MpsMonth').AsString+'''';
      AdoQuery:=TAdoQuery.Create(nil);
      AdoQuery.Connection :=DbConnect;
      AdoQuery.Close;
      AdoQuery.Prepared;
      AdoQuery.SQL.Text:=SqlText;
      AdoQuery.ExecSQL;
      AdoQry_Tmp.Next;
    end;

   SqlText:=' Insert into #TmpOblongdayMps '
            +' (MpsMonth,ItemCode,MonthTotalQty) '
            +' Values('
            +''''+AdoQry_Main.fieldbyname('MpsMonth').AsString+''''+','
            +''''+'日合计'+''''+','
            +''''+'日合计'+''''+')';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.ExecSQL;

    SqlText:=' Select MpsMonth,MpsDate,Sum(MpsQty) As MpsQty '
            +' From Mps where Mpsstatus<4 '
            +' Group By MpsMonth,MpsDate '
            +' having MpsMonth='''+AdoQry_Main.fieldbyname('MpsMonth').AsString+'''';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text :=SqlText;
    AdoQry_Tmp.open;
    while not AdoQry_Tmp.eof do
    begin
      s:=Copy(FormatDateTime('yyyy.mm.dd',AdoQry_Tmp.fieldbyname('MpsDate').AsDateTime),9,10);
      Case StrToInt(S) Of
        1..9:UpdateField:='['+Inttostr(StrToInt(s))+'日]';
        10..31:UpdateField:='['+Inttostr(StrToInt(s))+'日]';
      end;
      SqlText:=' Update #TmpOblongdayMps '
              +' Set '+UpdateField+'='''+AdoQry_Tmp.fieldbyname('MpsQty').AsString+''''
              +' Where ItemCode='''+'日合计'+''' '
              +' And MpsMonth='''+AdoQry_Main.fieldbyname('MpsMonth').AsString+'''';
      AdoQuery:=TAdoQuery.Create(nil);
      AdoQuery.Connection :=DbConnect;
      AdoQuery.Close;
      AdoQuery.Prepared;
      AdoQuery.SQL.Text:=SqlText;
      AdoQuery.ExecSQL;

      AdoQry_Tmp.next;
    end;

    AdoQry_Main.Next;
    y:=y+1;
  end;
     SqlText:=' Insert into #TmpOblongdayMps '
            +' (MpsMonth,ItemCode,MonthTotalQty) '
            +' Values('
            +''''+'日总计'+''''+','
            +''''+'小计：'+''''+','
            +'null'+')';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.ExecSQL;

    SqlText:=' Select datepArt(dd,Mpsdate) as MpsDate,Sum(MpsQty) As MpsQty '
            +' From Mps where Mpsstatus<4 '
            +' Group By datepArt(dd,Mpsdate) ';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text :=SqlText;
    AdoQry_Tmp.open;
    X:= AdoQry_Tmp.RecordCount;
    Y:=1;
    while not AdoQry_Tmp.eof do
    begin
      Frm_Public_Show.Lbl_Show.Caption:='正在统计数据,已完成......'+FormatFloat('##0',(y/x)*30+70)+'%!';
      s:=AdoQry_Tmp.fieldbyname('MpsDate').AsString;
      Case StrToInt(S) Of
        1..9:UpdateField:='['+Inttostr(StrToInt(s))+'日]';
        10..31:UpdateField:='['+Inttostr(StrToInt(s))+'日]';
      end;
      SqlText:=' Update #TmpOblongdayMps '
              +' Set '+UpdateField+'='''+AdoQry_Tmp.fieldbyname('MpsQty').AsString+''''
              +' Where ItemCode='''+'小计：'+''' '
              +' And MpsMonth='''+'日总计'+'''';
      AdoQuery:=TAdoQuery.Create(nil);
      AdoQuery.Connection :=DbConnect;
      AdoQuery.Close;
      AdoQuery.Prepared;