mrp_qry_psidate.pas

一个MRPII系统源代码版本

unit Mrp_Qry_PsiDate;

Interface

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

Type
  TFrm_Mrp_Qry_PsiDate = Class(TFrm_Base_Qry)
    Label2: TLabel;
    Label3: TLabel;
    DbTxt_ItemFlag: TDBText;
    DbTxt_MonthQty: TDBText;
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    AdoQry_MainMonths: 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_MainDSDesigner13: TFloatField;
    AdoQry_MainDSDesigner14: TFloatField;
    AdoQry_MainDSDesigner15: TFloatField;
    AdoQry_MainDSDesigner16: TFloatField;
    AdoQry_MainDSDesigner17: TFloatField;
    AdoQry_MainDSDesigner18: TFloatField;
    AdoQry_MainDSDesigner19: TFloatField;
    AdoQry_MainDSDesigner20: TFloatField;
    AdoQry_MainDSDesigner21: TFloatField;
    AdoQry_MainDSDesigner22: TFloatField;
    AdoQry_MainDSDesigner23: TFloatField;
    AdoQry_MainDSDesigner24: TFloatField;
    AdoQry_MainDSDesigner25: TFloatField;
    AdoQry_MainDSDesigner26: TFloatField;
    AdoQry_MainDSDesigner27: TFloatField;
    AdoQry_MainDSDesigner28: TFloatField;
    AdoQry_MainDSDesigner29: TFloatField;
    AdoQry_MainDSDesigner30: TFloatField;
    AdoQry_MainDSDesigner31: 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
    MpsMonth,Dates:string;
    check:Integer;
    conditionHint1,conditionHint2:string;
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;
    { Public declarations }
  end;

var
  Frm_Mrp_Qry_PsiDate: TFrm_Mrp_Qry_PsiDate;

implementation

uses Sys_Global, Mrp_Qry_PsiDate_D, Public_Show, Mrp_Qry_PsiDate_C,
  Mrp_Qry_UpRightMrp_D_Item, Mrp_Qry_GraphPsiDate, Mrp_Qry_GraphPsiDate1;

{$R *.DFM}

procedure TFrm_Mrp_Qry_PsiDate.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var
  I,J,x,y:Integer;
  SqlText:String;
  s,UpdateField,UpdateField1:String;
  AdoQuery ,Tmp_AdoQuery:TAdoQuery;
  sjdate:integer;
begin
  Application.ProcessMessages;
  Inherited;
  Frm_Sys_Condition:= TFrm_Mrp_Qry_PsiDate_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 PsiMonth Where Months='''+MpsMonth+'''';
  AdoQry_Tmp.Open ;
  if (AdoQry_Tmp.fieldbyname('Number').AsInteger=0) or (check=1)  then
  begin
    SqlText:=' Select Count(*) as sjdate  From Calendar '
          +' Where WorkDay=1 And Month(Sdate)=Month(convert(datetime,'''+dates+''')) '
          +' And Year(Sdate)=Year(convert(datetime,'''+dates+'''))';
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.clear;
    AdoQry_Tmp.SQL.Text := SqlText;
    AdoQry_Tmp.Open ;
    if AdoQry_Tmp.fieldbyname('sjdate').asinteger <> 0 then
      sjdate:=AdoQry_Tmp.fieldbyname('sjdate').asinteger
    else
    begin
      SqlText :=' select day(DateAdd(Month,1,'''+dates+''')-1) AS sjdate ';
      AdoQry_Tmp.Close;
      AdoQry_Tmp.SQL.clear;
      AdoQry_Tmp.SQL.Text := SqlText;
      AdoQry_Tmp.Open ;
      if AdoQry_Tmp.fieldbyname('sjdate').asinteger <> 0 then
        sjdate:=AdoQry_Tmp.fieldbyname('sjdate').asinteger ;
    end;
    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.clear;
    AdoQry_Tmp.SQL.Text:='exec ResertPsiDate :Month,:dates1,:sjdate';
    AdoQry_Tmp.Parameters.ParamByName('Month').Value:=MpsMonth;
    AdoQry_Tmp.Parameters.ParamByName('dates1').Value:=dates;
    AdoQry_Tmp.Parameters.ParamByName('sjdate').Value:=sjdate;
    AdoQry_Tmp.ExecSQL;
  end;



{  Frm_Public_Show.Lbl_Show.Caption:='正在统计数据，请稍等......';
  Frm_Public_Show.Visible:=True;                //________________
  Application.ProcessMessages;
  SelectFromSql:=' Select * from #tmpdayMps';
  Try
    SqlText:=' Create Table #TmpOblongdayMps ( '
            +' 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 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 ( '
            +' 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 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;  //________________
  //
  try
    SqlText:=' Drop Table #TmPmps,#TmpInvInBill,#TmpInvInBillT,#TmpInvOutBill,#TmpInvOutBillT,#rfssl,#jc ';
    ExecuteSql(AdoQry_Tmp,SqlText,1);
  except
  end;
//---1．	提取这个月份所有的主生产计划记录[Mps] 行状态=6、7且未结量<生产订货量
  sqltext:=' Select MpsMonth,ItemCode,MpsDate,MpsStatus,MpsQty Into #TmPmps '
          +'                        From Mps with(index(Pk_Mps)) Where MpsMonth='''+MpsMonth+''' '
          +'                        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 #TmpInvInBill '
          +'          From InvInBill  with(index(InvInBillNo)) Join InvInBillLine with(index(Pk_InvInBillLine)) '
          +'          On InvInBill.InvBillId=InvInBillLine.InvBillId '
          +'          where InvBillMonth='''+MpsMonth+''' and BillTypeCode in (''0104'',''0105'')'
          +'          and exists (select * from #TmPmps where #TmPmps.ItemCode=InvInBillLine.ItemCode) '
 // ExecuteSql(AdoQry_Tmp,SqlText,1);

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

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

  ExecuteSql(AdoQry_Tmp,SqlText,1);

 //===chb begin
  Tmp_AdoQuery:=TAdoQuery.Create(self);
  Tmp_AdoQuery.Connection :=Dbconnect;
  SQLText :='Select Distinct ItemCode From #TmPmps ';
  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 #TmPmps  '
            +' 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 #TmpOblongdayMps '
            +' (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:=' Insert #TmpOblongdayMps '
            +' (xsbz,ItemCode,MonthTotalQty,falg1,falg) '
            +' Select '
            +' ''主生产计划'','
            +' ItemCode,'
            +'Sum(MpsQty) as MpsQty ,'
            +''''+inttostr(y)+''','
            +' 1 from #TmPmps'
            +' where ItemCode='''+Tmp_AdoQuery.fieldbyname('ItemCode').asstring+''' '
            +' Group By ItemCode  ';
      AdoQuery:=TAdoQuery.Create(nil);
      AdoQuery.Connection :=DbConnect;
      AdoQuery.Close;
      AdoQuery.SQL.Text:=SqlText;
      AdoQuery.ExecSQL;



//生产入库
    SQlText:=' select ItemCode,Sum(InvBillQty) as MpsQty from #TmpInvInBill  '
            +' 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 #TmpOblongdayMps '
            +' (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
    else
    begin
      SqlText:=' Insert #TmpOblongdayMps '
              +' (xsbz,ItemCode,MonthTotalQty,falg1,falg) '
              +' Select '
              +' ''生产入库'','
              +' ItemCode,'
              +'Sum(InvBillQty) as MpsQty ,'
              +''''+inttostr(y)+''','
              +' 2 from #TmpInvInBill'
              +' where ItemCode='''+Tmp_AdoQuery.fieldbyname('ItemCode').asstring+''' '
              +' Group By ItemCode  ';
        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 #TmpOblongdayMps '
            +' (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 #TmpInvInBillT  '
            +' 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 #TmpOblongdayMps '
            +' (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
    else
    begin
      SqlText:=' Insert #TmpOblongdayMps '
              +' (xsbz,ItemCode,MonthTotalQty,falg1,falg) '
              +' Select '
              +' ''其他入库'','
              +' ItemCode,'
              +'Sum(InvBillQty) as MpsQty ,'
              +''''+inttostr(y)+''','
              +' 3 from #TmpInvInBillT'
              +' where ItemCode='''+Tmp_AdoQuery.fieldbyname('ItemCode').asstring+''' '
              +' Group By ItemCode  ';
        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 #TmpOblongdayMps '