bas_qry_bomchange.pas

一个MRPII系统源代码版本

unit Bas_Qry_BomChange;

Interface

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

Type
  TFrm_Bas_Qry_BomChange = Class(TFrm_Base_Qry)
    AdoQry_BomChange: TAdoQuery;
    AdoQry_BomChangeBomItemType0: TIntegerField;
    AdoQry_BomChangeBomqty0: TBCDField;
    AdoQry_BomChangeBomScrAp_Percent0: TBCDField;
    AdoQry_BomChangeBomItemType1: TIntegerField;
    AdoQry_BomChangeBomqty1: TBCDField;
    AdoQry_BomChangeBomScrAp_Percent1: TBCDField;
    AdoQry_BomChangereal_ItemCode: TStringField;
    AdoQry_BomChangeBomChangetime: TDateTimeField;
    AdoQry_Mo: TAdoQuery;
    DataSource1: TDataSource;
    DataSource2: TDataSource;
    AdoQry_Momono: TStringField;
    AdoQry_MoMoLineno: TIntegerField;
    AdoQry_MoItemCode: TStringField;
    AdoQry_MoMoLinestatus: TIntegerField;
    AdoQry_Momoqty: TBCDField;
    AdoQry_MoMoNoFinishqty: TBCDField;
    AdoQry_Moconfirmtime: TDateTimeField;
    AdoQry_MoislinGliao: TStringField;
    Panel1: TPanel;
    DBGridEh1: TDBGridEh;
    Panel2: TPanel;
    AdoQry_BomChangeite_ItemName: TStringField;
    AdoQry_BomChangeItemName: TStringField;
    AdoQry_BomChangeite_ItemCode: TStringField;
    AdoQry_BomChangeItemCode: TStringField;
    AdoQry_Mocheckflag: TIntegerField;
    AdoQry_MoOrderType: TIntegerField;
    procedure DataSource1DataChange(Sender: TObject; Field: TField);
    procedure Act_autoExecute(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure DBGridEh1TitleClick(Column: TColumnEh);
  private
    Flag : Boolean;
    { Private declarations }
  public
    { Public declarations }
    procedure InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);Override;

  end;

var
  Frm_Bas_Qry_BomChange: TFrm_Bas_Qry_BomChange;

implementation
uses Sys_Global;
{$R *.DFM}
procedure TFrm_Bas_Qry_BomChange.InitForm(AdOConnection:TAdOConnection;ShowExtendColumn:Boolean);
var sqltext:string;
begin
  Application.ProcessMessages;
  inherited;
  Flag := False;
  AdoQry_BomChange.Connection := AdOConnection;
  AdoQry_Mo.Connection := AdOConnection;
  AdoQry_tmp.Connection := AdOConnection;
  TlBtn_Look.Visible:=True;
  TlBtn_Look.Enabled:=True;
  TlBtn_Look.Action:=act_auto;
  sqltext:='select real_ItemCode,BomChange.ite_ItemCode,BomChange.ite_ItemCode+'' ''+Isnull(Item.ItemName,'''') as ite_ItemName'+
                    ',BomChange.ItemCode,BomChange.ItemCode+'' ''+Isnull(i.ItemName,'''') as ItemName,BomItemType0,Bomqty0 '+
                    ',BomScrAp_Percent0, BomItemType1,Bomqty1,BomScrAp_Percent1,BomChange.BomChangetime '+
                    ' into #BomChange from BomChange '+
                    ' left join  Item on BomChange.ite_ItemCode=Item.ItemCode'+
                    ' left join Item i on BomChange.ItemCode=i.ItemCode '+
                   '   join (select m.ItemCode,m.confirmtime '+
                             ' from MoLine m,BomChange b'+
                      ' where m.confirmtime<b.BomChangetime'+
                      ' and m.ItemCode=b.real_ItemCode and MoLinestatus=6 '+
                      
                    ' union select p.ItemCode,p.confirmtime '+
                             ' from poline p,BomChange b'+
                      ' where p.confirmtime<b.BomChangetime '+
                      ' and p.ItemCode=b.real_ItemCode and p.polinestatus=6 ) a '+
                      ' on BomChange.real_ItemCode=a.ItemCode and BomChange.BomChangetime>a.confirmtime ';
  selectfromsql:='select * from #BomChange';
  Executesql(AdoQry_BomChange,sqltext,1);
  Executesql(AdoQry_BomChange,'select * from #BomChange',0);
  
end;

procedure TFrm_Bas_Qry_BomChange.DataSource1DataChange(Sender: TObject;
  Field: TField);
var
  str:string;
begin                   
  inherited;
  Try
    AdoQry_tmp.Close;
    AdoQry_tmp.SQL.Text:='drop table #tmp17';
    AdoQry_tmp.ExecSQL;
  except
  end;
  str:='';
  str:=AdoQry_BomChange.fieldbyname('real_ItemCode').asstring;
  with AdoQry_Mo do
  begin
    Close;
    sql.clear ;
    sql.Add('select 0 as CheckFlag,0 as OrderType,mono,MoLineno,m.ItemCode+'' ''+Item.ItemName as ItemCode, moqty ,MoNoFinishqty,MoLinestatus,m.confirmtime,'+
            quotedstr('未领料')+' as islinGliao'+
            ' into #tmp17 from MoLine m,BomChange b,Item '+
            ' where m.confirmtime<b.BomChangetime '+
            ' and m.ItemCode=Item.ItemCode and MoLinestatus=6  '+
            ' and m.ItemCode=b.real_ItemCode '+
            ' and m.ItemCode='''+str+''''+
            ' union '+
            ' select 0,1,pono,polineno ,p.ItemCode+'' ''+Item.ItemName as ItemCode,poqty,ponoFinishqty,polinestatus,p.confirmtime,'+
             quotedstr('未领料')+' as islinGliao'+
            ' from poline p,BomChange b,Item'+
            ' where p.confirmtime<b.BomChangetime '+
            ' and p.ItemCode=b.real_ItemCode '+
            ' and P.ItemCode=Item.ItemCode and polinestatus=6  '+
            ' and p.ItemCode='''+str+''''+
            ' update #tmp17 set islinGliao=''已领料''  from '+
            ' (select lTrim(rTrim(isnull(mono,'''')+isnull(pono,''''))) as mono,'+
            '  lTrim(rTrim(isnull(MoLineno,'''')+isnull(polineno,''''))) as MoLineno from InvOutBill'+
                          ' ) a '+
                          ' where #tmp17.mono=a.mono and #tmp17.MoLineno=a.MoLineno');
    ExecSQL;
    Close;
    sql.clear ;
    sql.Add('select * from #tmp17');
    open ;
  end;

end;

procedure TFrm_Bas_Qry_BomChange.Act_autoExecute(Sender: TObject);
var sqltext:string;
    AdoQry,AdoQry1:TAdoQuery;
begin
//  inherited;
  if AdoQry_Mo.RecordCount=0 then exit;
  if DispInfo('确认要重新产生领料清单吗？',2)<>'y' then exit;
  AdoQry:=TAdoQuery.Create(nil);
  AdoQry1:=TAdoQuery.Create(nil);
  AdoQry.Connection:=AdoQry_Mo.Connection;
  AdoQry1.Connection:=AdoQry_Mo.Connection;
  AdoQry.EnableBCD:=False;
  AdoQry1.EnableBCD:=False;
  createpanel(3,dbgrideh1);
  try
    AdoQry_Mo.First;
    while not AdoQry_Mo.Eof do
    begin 
      If AdoQry_Mo.fieldbyname('CheckFlag').AsInteger=0 then 
      begin
        AdoQry_Mo.Next;
        Continue;
      end;
      try
        AdoQry_Mo.Connection.beginTrans;
        if AdoQry_Mo.fieldbyname('OrderType').AsInteger=0 then
        begin
          sqltext:=' select convert(varchAr(800),'''') as itedir,* '
                  +'   into #tmPmnItemList from mnItemList   '
                  +' where mono='+quotedstr(AdoQry_Mo.fieldbyname('mono').asstring)
                  +'   and MoLineno='+inttostr(AdoQry_Mo.fieldbyname('MoLineno').asinteger)
                  +' Order by ItemListid  ';
          Executesql(AdoQry,sqltext,1)            ;
          sqltext:='select * from #tmPmnItemList'
                  +' Order by ItemListId ';
          Executesql(AdoQry1,sqltext,0);
          while not AdoQry1.Eof do
          begin
            sqltext:='update #tmPmnItemList'
                     +'   set itedir=case when Parentid=0 then ite_ItemCode '
                     +'              else (select top 1 itedir from #tmPmnItemList  '
                     +'                     where ItemListid='+inttostr(AdoQry1.fieldbyname('Parentid').asinteger)
                     +'                     )+'',''+ite_ItemCode end  '
                     +' where ItemListid='+inttostr(AdoQry1.fieldbyname('ItemListid').asinteger);
             Executesql(AdoQry_tmp,sqltext,1)               ;
      //       Executesql(AdoQry_tmp,'select * from #tmPmnItemList where ItemListid='+inttostr(AdoQry1.fieldbyname('ItemListid').asinteger),0);
             AdoQry1.Next;
          end;
     //     ExecuteSQl(AdoQry_Tmp,'select * Into tmPmnItemList from #TmPmnItemList',1);
          ReAutoBuildOrder(AdoQry_Mo.Connection,AdoQry_Mo.fieldbyname('mono').asstring,Trim(getCode(AdoQry_Mo.fieldbyname('ItemCode').AsString)),
                       IntToStr(AdoQry_Mo.fieldbyname('MoLineNo').AsInteger),'Add','Mo',AdoQry_Mo.fieldbyname('MoQty').AsFloat);
{            ExecuteSql(AdoQry_Tmp,'select * from #ReMnItemList',0);
            ShowMessage(IntToStr(AdoQry_Tmp.RecordCount));