mrp_qry_psimonth.pas

一个MRPII系统源代码版本

unit Mrp_Qry_PsiMonth;

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_PsiMonth = Class(TFrm_Base_Qry)
    Label2: TLabel;
    Label3: TLabel;
    DbTxt_ItemFlag: TDBText;
    DbTxt_MonthQty: TDBText;
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    AdoQry_MainYears: TStringField;
    AdoQry_Mainxsbz: TStringField;
    AdoQry_MainItemCode: TStringField;
    AdoQry_MainMonthTotalQty: TFloatField;
    AdoQry_Mainrpz: TFloatField;
    AdoQry_Mainfalg: TIntegerField;
    AdoQry_Mainfalg1: TIntegerField;
    AdoQry_MainItemName: TStringField;
    AdoQry_MainUomName: TStringField;
    AdoQry_MainDSDesigner1: TFloatField;
    AdoQry_MainDSDesigner2: TFloatField;
    AdoQry_MainDSDesigner3: TFloatField;
    AdoQry_MainDSDesigner4: TFloatField;
    AdoQry_MainDSDesigner5: TFloatField;
    AdoQry_MainDSDesigner6: TFloatField;
    AdoQry_MainDSDesigner7: TFloatField;
    AdoQry_MainDSDesigner8: TFloatField;
    AdoQry_MainDSDesigner9: TFloatField;
    AdoQry_MainDSDesigner10: TFloatField;
    AdoQry_MainDSDesigner11: TFloatField;
    AdoQry_MainDSDesigner12: TFloatField;
    AdoQry_MainItemFlag: TStringField;
    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);
    procedure DBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
  private
      { Private declarations }
  public
    MpsYear:string;
    check1:integer;
    conditionHint1,conditionHint2:string;
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Mrp_Qry_PsiMonth: TFrm_Mrp_Qry_PsiMonth;

implementation

uses Sys_Global, Mrp_Qry_PsiMonth_D, Public_Show, Mrp_Qry_PsiMonth_C,
  Mrp_Qry_UpRightMrp_D_Item, Mrp_Qry_GraphPsiMonth, Mrp_Qry_GraphPsiMonth1;

{$R *.DFM}

procedure TFrm_Mrp_Qry_PsiMonth.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var
  I,J,x,y:Integer;
  SqlText:String;
  s,UpdateField,UpdateField1:String;
  AdoQuery ,Tmp_AdoQuery:TAdoQuery;
begin
  Application.ProcessMessages;
  Inherited;
  Frm_Sys_Condition:= TFrm_Mrp_Qry_PsiMonth_C.Create(self);
  act_filter.Execute;
  Frm_Sys_Condition.Release;
  Frm_Sys_Condition:=nil;

  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.clear;
  AdoQry_Tmp.SQL.Text := 'Select Count(*) As Number from PSIYear Where Years='''+MpsYear+'''';
  AdoQry_Tmp.Open ;
  if (AdoQry_Tmp.fieldbyname('Number').AsInteger=0) or (check1=1) then
  begin
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.clear;
    AdoQry_Tmp.SQL.Text:='exec ResertPSIDMonth :Year';
    AdoQry_Tmp.Parameters.ParamByName('Year').Value:=MpsYear;
    AdoQry_Tmp.ExecSQL;
  end;


{  Frm_Public_Show.Lbl_Show.Caption:='正在统计数据，请稍等......';
  Frm_Public_Show.Visible:=True;                //________________
  Application.ProcessMessages;
  SelectFromSql:=' Select * from #tmpdayMpsR';
  Try
    SqlText:=' Create Table #TmpOblongdayMpsR ( '
            +' xsbz varchAr(20)  Null ,'
            +' ItemCode varChAr(20)  Null,'
            +' MonthTotalQty  float(12) Null,'  //Float(12)
            +' rpz float(12) Null,'
            +' falg1 integer Null,'
            +' falg integer Null,';
    For I:=1 To 11 Do
    begin
      SqlText:=SqlText+'['+IntToStr(I)+'月]   Float(12)  Null  ,';
    end;
    SqlText:=SqlText+'['+IntToStr(12)+'月]   Float(12)  Null )';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.ExecSQL;
  Except
    Sqltext:=' Drop Table #TmpOblongdayMpsR ';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.ExecSQL;
   SqlText:=' Create Table #TmpOblongdayMpsR ( '
            +' xsbz varchAr(20)  Null ,'
            +' ItemCode varChAr(20)  Null,'
            +' MonthTotalQty  float(12) Null,'
            +' rpz float(12) Null,'
            +' falg1 integer Null,'
            +' falg integer Null ,';
    For I:=1 To 11 Do
    begin
      SqlText:=SqlText+'['+IntToStr(I)+'月]   Float(12)  Null ,';
    end;
    SqlText:=SqlText+'['+IntToStr(12)+'月]   Float(12)  Null )';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.ExecSQL;
  end;
  Frm_Public_Show.Lbl_Show.Caption:='正在统计数据,已完成......1%!';
  Application.ProcessMessages;  //________________
  //
  try
    SqlText:=' Drop Table #TmPmpsR,#TmpInvInBillR,#TmpInvInBillTR,#TmpInvOutBillR,#TmpInvOutBillTR,#rfsslR,#jcR ';
    ExecuteSql(AdoQry_Tmp,SqlText,1);
  except
  end;
//---1．	提取这个年份所有的主生产计划记录[Mps]
  sqltext:=' Select MpsMonth,ItemCode,MpsDate,MpsStatus,MpsQty Into #TmPmpsR '
          +'                        From Mps with(index(Pk_Mps))  Where Year(MpsDate)='''+MpsYear+''' '
          +'                        and (MpsStatus =3 or  (MpsStatus =4  and MpsFinishQty<>0))';
  ExecuteSql(AdoQry_Tmp,SqlText,1);

//--2．	提取这个年份所有的主生产计划记录的生产入库记录[InvInBill、InvInBillLine] BillTypeCode=0104/0105
  SqlText:=' Select InvBillMonth,ItemCode,InvBillDate,InvBillQty   Into #TmpInvInBillR '
          +'          From InvInBill  with(index(InvInBillNo)) Join InvInBillLine with(index(Pk_InvInBillLine)) '
          +'          On InvInBill.InvBillId=InvInBillLine.InvBillId '
          +'          where Year(InvBillDate)='''+MpsYear+''' and BillTypeCode in (''0104'',''0105'')'
          +'          and exists (select * from #TmPmpsR where #TmPmpsR.ItemCode=InvInBillLine.ItemCode) ';
  ExecuteSql(AdoQry_Tmp,SqlText,1);

//--3．	提取这个年份所有的主生产计划记录的其他入库记录[InvInBill、InvInBillLine] BillTypeCode=0199
   sqltext:='  Select InvBillMonth,ItemCode,InvBillDate,InvBillQty   Into #TmpInvInBillTR '
           +'            From InvInBill  with(index(InvInBillNo)) Join InvInBillLine with(index(Pk_InvInBillLine)) '
           +'            On InvInBill.InvBillId=InvInBillLine.InvBillId '
           +'            where Year(InvBillDate)='''+MpsYear+''' and BillTypeCode =''0199'' '
           +'            and exists (select * from #TmPmpsR where #TmPmpsR.ItemCode=InvInBillLine.ItemCode) ';
    ExecuteSql(AdoQry_Tmp,SqlText,1);

//--4．	提取这个年份所有的主生产计划记录的销售出库记录[InvOutBill、InvOutBillLine] BillTypeCode= 0204/0203
  sqltext:=' Select InvBillMonth,ItemCode,InvBillDate,InvBillQty   Into #TmpInvOutBillR '
          +'            From InvOutBill with(index(InvOutBillNo)) Join InvOutBillLine with(index(Pk_InvOutBillLine)) '
          +'            On InvOutBill.InvBillId=InvOutBillLine.InvBillId '
          +'             where Year(InvBilldate)='''+MpsYear+''' and BillTypeCode in (''0204'',''0203'') '
          +'             and exists (select * from #TmPmpsR where #TmPmpsR.ItemCode=InvOutBillLine.ItemCode) ';
  ExecuteSql(AdoQry_Tmp,SqlText,1);
//--5．	提取这个年份所有的主生产计划记录的其它出库记录[InvOutBill、InvOutBillLine] BillTypeCode= 0299
  sqltext:=' Select InvBillMonth,ItemCode,InvBillDate,InvBillQty   Into #TmpInvOutBillTR '
          +'            From InvOutBill with(index(InvOutBillNo)) Join InvOutBillLine with(index(Pk_InvOutBillLine)) '
          +'             On InvOutBill.InvBillId=InvOutBillLine.InvBillId '
          +'             where Year(InvBillDate)='''+MpsYear+''' and BillTypeCode =''0209'' '
          +'             and exists (select * from #TmPmpsR where #TmPmpsR.ItemCode=InvOutBillLine.ItemCode) ';

  ExecuteSql(AdoQry_Tmp,SqlText,1);

 //===chb begin
  Tmp_AdoQuery:=TAdoQuery.Create(self);
  Tmp_AdoQuery.Connection :=Dbconnect;
  SQLText :='Select Distinct ItemCode From #TmPmpsR ';
  Tmp_AdoQuery.Close;
  Tmp_AdoQuery.SQL.Text:=SqlText;
  Tmp_AdoQuery.Open;
  x:=Tmp_AdoQuery.recordCount;
  Tmp_AdoQuery.First;
  Y:=1;
  while not Tmp_AdoQuery.Eof do
  begin
    Frm_Public_Show.Lbl_Show.Caption:='正在统计数据,已完成......'+FormatFloat('##0',(y/x)*30)+'%!';
    SQlText:=' select ItemCode,Sum(MpsQty) as MpsQty from #TmPmpsR  '
            +' where ItemCode='''+Tmp_AdoQuery.fieldbyname('ItemCode').asstring+''' '
            +' Group By ItemCode ';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.Open;
    while not AdoQry_Tmp.Eof do
    begin
    //主生产计划
      SqlText:=' Insert #TmpOblongdayMpsR '
            +' (xsbz,ItemCode,MonthTotalQty,falg1,falg) '
            +' Values('
            +'''主生产计划'','
            +''''+AdoQry_Tmp.fieldbyname('ItemCode').AsString+''','
            +''''+AdoQry_Tmp.fieldbyname('MpsQty').AsString+''','
            +''''+inttostr(y)+''','
            +' 1)';
      AdoQuery:=TAdoQuery.Create(nil);
      AdoQuery.Connection :=DbConnect;
      AdoQuery.Close;
      AdoQuery.SQL.Text:=SqlText;
      AdoQuery.ExecSQL;

      AdoQry_Tmp.Next;
    end;
//生产入库
    SQlText:=' select ItemCode,Sum(InvBillQty) as MpsQty from #TmpInvInBillR  '
            +' where ItemCode='''+Tmp_AdoQuery.fieldbyname('ItemCode').asstring+''' '
            +' Group By ItemCode ';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.Open;
    if AdoQry_tmp.Eof then
    begin
      SqlText:=' Insert #TmpOblongdayMpsR '
            +' (xsbz,ItemCode,falg1,falg) '
            +' Values('
            +'''生产入库'','
            +''''+Tmp_AdoQuery.fieldbyname('ItemCode').asstring+''','
            +''''+inttostr(y)+''','
            +' 2)';
      AdoQuery:=TAdoQuery.Create(nil);
      AdoQuery.Connection :=DbConnect;
      AdoQuery.Close;
      AdoQuery.SQL.Text:=SqlText;
      AdoQuery.ExecSQL;
    end;
    while not AdoQry_Tmp.Eof do
    begin
      SqlText:=' Insert #TmpOblongdayMpsR '
            +' (xsbz,ItemCode,MonthTotalQty,falg1,falg) '
            +' Values('
            +'''生产入库'','
            +''''+AdoQry_Tmp.fieldbyname('ItemCode').AsString+''','
            +''''+AdoQry_Tmp.fieldbyname('MpsQty').AsString+''','
            +''''+inttostr(y)+''','
            +' 2)';
      AdoQuery:=TAdoQuery.Create(nil);
      AdoQuery.Connection :=DbConnect;
      AdoQuery.Close;
      AdoQuery.SQL.Text:=SqlText;
      AdoQuery.ExecSQL;

      AdoQry_Tmp.Next;
    end;

//其他入库记录
    SQlText:=' select ItemCode,Sum(InvBillQty) as MpsQty from #TmpInvInBillTR  '
            +' where ItemCode='''+Tmp_AdoQuery.fieldbyname('ItemCode').asstring+''' '
            +' Group By ItemCode ';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.Open;
    if AdoQry_tmp.Eof then
    begin
      SqlText:=' Insert #TmpOblongdayMpsR '
            +' (xsbz,ItemCode,falg1,falg) '
            +' Values('
            +'''其他入库'','
            +''''+Tmp_AdoQuery.fieldbyname('ItemCode').asstring+''','
            +''''+inttostr(y)+''','
            +' 3)';
      AdoQuery:=TAdoQuery.Create(nil);
      AdoQuery.Connection :=DbConnect;
      AdoQuery.Close;
      AdoQuery.SQL.Text:=SqlText;
      AdoQuery.ExecSQL;
    end;

    while not AdoQry_Tmp.Eof do
    begin
      SqlText:=' Insert #TmpOblongdayMpsR '
            +' (xsbz,ItemCode,MonthTotalQty,falg1,falg) '
            +' Values('
            +'''其他入库'','
            +''''+AdoQry_Tmp.fieldbyname('ItemCode').AsString+''','
            +''''+AdoQry_Tmp.fieldbyname('MpsQty').AsString+''','
            +''''+inttostr(y)+''','
            +' 3)';
      AdoQuery:=TAdoQuery.Create(nil);
      AdoQuery.Connection :=DbConnect;
      AdoQuery.Close;
      AdoQuery.SQL.Text:=SqlText;
      AdoQuery.ExecSQL;

      AdoQry_Tmp.Next;
    end;
//销售出库
    SQlText:=' select ItemCode,Sum(InvBillQty) as MpsQty from #TmpInvOutBillR  '
            +' where ItemCode='''+Tmp_AdoQuery.fieldbyname('ItemCode').asstring+''' '
            +' Group By ItemCode ';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.Open;

    if AdoQry_tmp.Eof then
    begin
      SqlText:=' Insert #TmpOblongdayMpsR '
            +' (xsbz,ItemCode,falg1,falg) '
            +' Values('
            +'''销售出库'','
            +''''+Tmp_AdoQuery.fieldbyname('ItemCode').asstring+''','
            +''''+inttostr(y)+''','
            +' 4)';
      AdoQuery:=TAdoQuery.Create(nil);
      AdoQuery.Connection :=DbConnect;
      AdoQuery.Close;
      AdoQuery.SQL.Text:=SqlText;
      AdoQuery.ExecSQL;
    end;
    while not AdoQry_Tmp.Eof do
    begin
      SqlText:=' Insert #TmpOblongdayMpsR '
            +' (xsbz,ItemCode,MonthTotalQty,falg1,falg) '
            +' Values('
            +'''销售出库'','
            +''''+AdoQry_Tmp.fieldbyname('ItemCode').AsString+''','
            +''''+AdoQry_Tmp.fieldbyname('MpsQty').AsString+''','
            +''''+inttostr(y)+''','
            +' 4)';
      AdoQuery:=TAdoQuery.Create(nil);
      AdoQuery.Connection :=DbConnect;
      AdoQuery.Close;
      AdoQuery.SQL.Text:=SqlText;
      AdoQuery.ExecSQL;

      AdoQry_Tmp.Next;
    end;
 //其它出库
    SQlText:=' select ItemCode,Sum(InvBillQty) as MpsQty from #TmpInvOutBillTR  '
            +' where ItemCode='''+Tmp_AdoQuery.fieldbyname('ItemCode').asstring+''' '
            +' Group By ItemCode ';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:=SqlText;
    AdoQry_Tmp.Open;
    if AdoQry_tmp.Eof then
    begin