bas_bombatchreplace.pas

来自「一个MRPII系统源代码版本」· PAS 代码 · 共 886 行 · 第 1/2 页

unit Bas_BomBatchReplace;

Interface

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

Type
    TFrm_Bas_BomBatchReplace = Class(TFrm_Base_Panel)
    ToolButton1: TToolButton;
    TlBtn_Exit: TToolButton;
    DBGridEh1: TDBGridEh;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Lbl_ItemName1: TLabel;
    Label7: TLabel;
    Lbl_ItemName2: TLabel;
    Label9: TLabel;
    Lbl_ProjectName: TLabel;
    Lbl_ProjectName1: TLabel;
    CmBx_EScope: TComboBox;
    ExtEdt_ItemCode1: TExtEdit;
    ExtEdt_ItemCode2: TExtEdit;
    Extedt_ProjectCode: TExtEdit;
    AdoQry_Main: TAdoQuery;
    DataSource1: TDataSource;
    tlbtn_Preview: TToolButton;
    ToolButton4: TToolButton;
    tlbtn_Print: TToolButton;
    Action4: TAction;
    ToolButton5: TToolButton;
    Tlbtn_Hint: TToolButton;
    ToolButton7: TToolButton;
    ExtPrintReport1: TExtPrintReport;
    ToolButton2: TToolButton;
    ToolButton3: TToolButton;
    procedure CmBx_EScopeChange(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormActivate(Sender: TObject);
    procedure GetItemName(Sender: TObject);
    procedure CmBx_EScopeExit(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure CmBx_EScopeKeyPress(Sender: TObject; var Key: ChAr);
    procedure Act_PreviewExecute(Sender: TObject);
    procedure Act_PrintExecute(Sender: TObject);
    procedure ExtEdt_ItemCode1Exit(Sender: TObject);
    procedure Action4Execute(Sender: TObject);
    procedure ExtEdt_ItemCode2Exit(Sender: TObject);
    procedure Extedt_ProjectCodeExit(Sender: TObject);
    procedure ExtEdt_ItemCode2Change(Sender: TObject);
    procedure tlbtn_PreviewClick(Sender: TObject);
    procedure Act_HintExecute(Sender: TObject);
    procedure Act_nextExecute(Sender: TObject);
  private
    { Private declarations }
    Lc_TmpName:string;
    TmpAdoQry1,TmpAdoQry2:TAdoQuery;
    procedure BomBatchReplace(ItemCode1,ItemCode2,Range:String);                //根据条件成批修改Bom
    procedure ExpandBom(ItemCode: string);
    procedure SetReport;
    procedure GetTransValue(FieldName,OldValue:String;var NewValue:String);
    function SlaveCodeUsable(R_ItemCode:String):Boolean;
    function PickSlaveCode(InitCode:String):String;
    procedure checkv(pstr:string;sstr:string);                                  //循环校验
    function isfinalProduct(ItemCode1:string):boolean;                          // 判断物料代码是否为最终产品；
    procedure GetBomParam;//系统是否对Bom进行审核
  public
    { Public declarations }
    lc_Itemexitf:boolean;
    lc_Itemexitb:boolean;
    procedure InitForm(AdoConnection:TAdoConnection);
  end;
var
  Frm_Bas_BomBatchReplace: TFrm_Bas_BomBatchReplace;
  Temp_DBGridEh:TDBGridEh;
implementation

uses Sys_Global;

{$R *.DFM}

procedure TFrm_Bas_BomBatchReplace.InitForm(AdoConnection: TAdoConnection);
begin
  //SetDBConnect(AdoConnection);
  AdoQry_Tmp.Connection:=AdoConnection;
  AdoQry_Main.Connection:=AdoConnection;
  GetBomParam;
end;

procedure TFrm_Bas_BomBatchReplace.CmBx_EScopeChange(Sender: TObject);
begin
  inherited;
  if CmBx_EScope.text='全部物料' then
    Extedt_ProjectCode.text:='';
  Extedt_ProjectCode.Enabled:=not (CmBx_EScope.text='全部物料');
  Lbl_ProjectName.Caption:='';
end;

procedure TFrm_Bas_BomBatchReplace.FormCreate(Sender: TObject);
begin
  inherited;
  Lc_TmpName:='';
  CmBx_EScope.clear ;
  CmBx_EScope.Items.Add('全部物料');
  CmBx_EScope.Items.Add('指定一项物料');
end;


procedure TFrm_Bas_BomBatchReplace.BomBatchReplace(ItemCode1, ItemCode2,
  Range: String);
var
  str:string;//子项物料标识
  useCodestr:string;
begin
  useCodestr:=userCode;
  TmpAdoQry1:=TAdoQuery.Create(nil);
  TmpAdoQry1.Connection :=AdoQry_Tmp.Connection ;
  TmpAdoQry2:=TAdoQuery.Create(nil);
  TmpAdoQry2.Connection :=AdoQry_Tmp.Connection ;
  if Range='全部物料' then
  begin
    //查找ItemCode1父项标识；
    with TmpAdoQry1 do
    begin
      Close;
      sql.clear;
      sql.Add(' select Bom.ite_ItemCode+'' ''+Item.ItemName as ite_ItemCode '+
                     'from Bom left join Item '+
                     ' on Bom.ite_ItemCode=Item.ItemCode'+
                     '  where Bom.ItemCode='''+ItemCode1+'''');
      open;
    end;
    //查找ItemCode1子项标识；
    with  TmpAdoQry2 do
    begin
      Close;
      sql.clear;
      sql.Add(' select Bom.ite_ItemCode, Bom.ItemCode+'' ''+Item.ItemName as ItemCode '+
                     'from Bom left join Item '+
                     ' on Bom.ite_ItemCode=Item.ItemCode'+
                     '  where Bom.ite_ItemCode='''+ItemCode1+'''');
      open;
    end;
    //修改Bom资料(子项)；
    with AdoQry_Tmp do
    begin
      //保留更改Bom前的记录到表BomChangeInfo;
      Close;
      sql.clear;
      sql.Add('insert into BomChangeInfo '+
                    '(logdate,EmployeeCode,logaction ,Bomid,ite_ItemCode,ItemCode,'+
                    ' BomItemType,Bomqty,BomScrAp_Percent,Bomstatus,DeptCode,BommArk,remArk )'+
                    ' select  getdate() as a ,'+
                            ' '''+useCodestr+''' ,'+
                            ' ''3'' ,'+
                            ' Bom.Bomid,'+
                            ' Bom.ite_ItemCode,'+
                            ' Bom.ItemCode ,'+
                            ' Bom.BomItemType,'+
                            ' Bom.Bomqty,'+
                            ' Bom.BomScrAp_Percent,'+
                            ' Bom.Bomstatus,'+
                            ' Bom.DeptCode,'+
                            ' Bom.BommArk,'+
                            ' '''+ItemCode1+'''+''(子项)'' '+
                            ' from  Bom '+
                            ' where ItemCode='''+ItemCode1+'''');
      execsql;

      Close;
      sql.clear;
      sql.Add('update Bom '+
                      ' set ItemCode='''+ItemCode2+''' '+
                     ' where ItemCode='''+ItemCode1+'''');
      execsql;
      //判断ItemCode1是否有父项;
      Close ;
      sql.clear;
      sql.Add('select ItemCode '+
                      ' from  Bom '+
                      ' where ite_ItemCode='''+ItemCode1+'''');
      open;
      //修改Bom资料(父项)；
      if recordCount>0 then
      begin
        //保留更改前的Bom记录到表BomChangeInfo;
        Close;
        sql.clear;
        sql.Add('insert into BomChangeInfo '+
                      '(logdate,EmployeeCode,logaction ,Bomid,ite_ItemCode,ItemCode,'+
                      ' BomItemType,Bomqty,BomScrAp_Percent,Bomstatus,DeptCode,BommArk,remArk )'+
                      ' select  getdate() as a ,'+
                              ' '''+useCodestr+''' ,'+
                              ' ''3'' ,'+
                              ' Bom.Bomid,'+
                              ' Bom.ite_ItemCode,'+
                              ' Bom.ItemCode ,'+
                              ' Bom.BomItemType,'+
                              ' Bom.Bomqty,'+
                              ' Bom.BomScrAp_Percent,'+
                              ' Bom.Bomstatus,'+
                              ' Bom.DeptCode,'+
                              ' Bom.BommArk,'+
                              ' '''+ItemCode1+'''+''(父项)'' '+
                              ' from  Bom '+
                              ' where ite_ItemCode='''+ItemCode1+'''');
         execsql;

        Close;
        sql.clear;
        sql.Add('update Bom '+
                        ' set Ite_ItemCode='''+ItemCode2+''' '+
                        ' where ite_ItemCode='''+ItemCode1+'''');
        execsql;
      end;
    end;
  with AdoQry_tmp do
  begin
    Close;
    sql.clear;
    sql.Add('select Item.ItemCode+'' ''+Item.ItemName as ItemCodeName '+
                    '  from Item '+
                    '  where ItemCode='''+ItemCode2+'''');
    open;
    str:=fieldbyname('ItemCodeName').asstring;
    Close;
  end;
  if AdoQry_Main.Active =False then AdoQry_Main.Active:=True;
  if isfinalProduct(ItemCode1) then                    // 断物料代码是否为最终产品；
  begin
    //显示ItemCode1子项标识；
    AdoQry_Main.insert;
    AdoQry_Main.fieldbyname('pItemCodeName').asstring:='';
    AdoQry_Main.fieldbyname('sItemCodeName').asstring:=str;
    AdoQry_Main.post;
  end
  else
  begin
    //显示ItemCode1父项标识；
    TmpAdoQry1.First;
    while not TmpAdoQry1.Eof do
    begin
      AdoQry_Main.insert;
      AdoQry_Main.fieldbyname('pItemCodeName').asstring:=
        TmpAdoQry1.fieldbyname('Ite_ItemCode').asstring;
      AdoQry_Main.fieldbyname('sItemCodeName').asstring:=str;
      AdoQry_Main.post;
      TmpAdoQry1.next;
    end;
    //显示ItemCode1子项标识；
    TmpAdoQry2.First;
    while not TmpAdoQry1.Eof do
    begin
      AdoQry_Main.insert;
      AdoQry_Main.fieldbyname('pItemCodeName').asstring:=str;
      AdoQry_Main.fieldbyname('sItemCodeName').asstring:=
        TmpAdoQry2.fieldbyname('ItemCode').asstring;
      AdoQry_Main.post;
      TmpAdoQry2.next;
    end;
  end;
  TmpAdoQry1.free;
  TmpAdoQry2.free;

  end
  else
  begin
    with AdoQry_Main do
    begin
      Close;
      sql.clear;
      sql.Add(' exec expandpArtBom  '''+Trim(Extedt_ProjectCode.text)+''' ,'+
                ' '''+Trim(ExtEdt_ItemCode1.text)+''','+
                ' '''+Trim(ExtEdt_ItemCode2.text)+''','+
                ' '''+useCodestr+'''');
      Prepared;
      open;
    end;

     //根据条件展开某一个父项的Bom，替换其中子项的一个指定一项物料；
    //PArtReplace(Trim(Extedt_ProjectCode.text),ItemCode1,ItemCode2);
  end;
  //根据条件获得子项物料标识;
end;

procedure TFrm_Bas_BomBatchReplace.FormActivate(Sender: TObject);
begin
  inherited;
  CmBx_EScope.text:='全部物料';
  Extedt_ProjectCode.enabled:=False;
end;

procedure TFrm_Bas_BomBatchReplace.ExpandBom(ItemCode: string);
var

  StrSQLText:string;
  AdoQry_ExpBom:TAdoQuery;
begin
  AdoQry_ExpBom:=TAdoQuery.Create(nil);
  AdoQry_ExpBom.Connection:=AdoQry_Main.Connection;
  strsqltext:='select t.* , '+
                      'Item.ItemName  as ItemName '+
                      ' from '+
                      '( select Bom.Ite_ItemCode as ite_ItemCode,'+
                                ' Item.ItemName as ite_ItemName ,'+
                                ' Bom.ItemCode  as ItemCode'+
	                              ' from Bom  join Item on Bom.ite_ItemCode=Item.ItemCode) as t '+
                      ' join  Item on t.ItemCode=Item.ItemCode '+
                      ' Where t.Ite_ItemCode='''+ItemCode+'''';;
  With AdoQry_ExpBom Do
  begin
    Close;
    Sql.clear;
    Sql.Add(StrSQLText);
    Prepared;
    Open;
    while not AdoQry_ExpBom.eof do
    begin
      if AdoQry_tmp.active=False then AdoQry_tmp.active:= True ;
      AdoQry_tmp.insert;
      AdoQry_tmp.fieldbyname('Ite_ItemCode').asstring:=
        AdoQry_ExpBom.fieldbyname('Ite_ItemCode').asstring;
      AdoQry_tmp.fieldbyname('ite_ItemName').asstring:=
        AdoQry_ExpBom.fieldbyname('ite_ItemName').asstring;
      AdoQry_tmp.fieldbyname('ItemCode').asstring:=
        AdoQry_ExpBom.fieldbyname('ItemCode').asstring;
      AdoQry_tmp.fieldbyname('ItemName').asstring:=
        AdoQry_ExpBom.fieldbyname('ItemName').asstring;
      AdoQry_tmp.post;
      ExpandBom(AdoQry_ExpBom.fieldbyname('ItemCode').AsSTring);
      AdoQry_ExpBom.next;
    end;
    Close;
  end;
  AdoQry_ExpBom.Free;
end;

procedure TFrm_Bas_BomBatchReplace.GetItemName(Sender: TObject);
begin
 inherited;
  If (sender=Extedt_ItemCode1) or (sender=Extedt_ItemCode2) then
  begin
    If (Trim(Extedt_ItemCode1.text)<>'') or (Trim(Extedt_ItemCode2.text)<>'') then
      if  Trim(Extedt_ItemCode1.text)=Trim(Extedt_ItemCode2.text) then
      begin
        DispInfo('子项1和子项2的内容相同,请重新输入',3);
        Extedt_ItemCode2.setfocus;
        abort;
      end;
  end;
end;

procedure TFrm_Bas_BomBatchReplace.CmBx_EScopeExit(Sender: TObject);
begin
  inherited;
  if CmBx_EScope.text='指定一项物料' then
    Extedt_ProjectCode.SetFocus ;
end;

procedure TFrm_Bas_BomBatchReplace.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  inherited;
  Action:=cafree;
end;

procedure TFrm_Bas_BomBatchReplace.CmBx_EScopeKeyPress(Sender: TObject;
  var Key: ChAr);
begin
  inherited;
  abort;
end;

procedure TFrm_Bas_BomBatchReplace.Act_PreviewExecute(Sender: TObject);
begin
  inherited;
  if (AdoQry_Main.active=False )
     or ( (AdoQry_Main.active=True) and (AdoQry_Main.recordCount=0)) then
  begin
    DispInfo('没有数据可打印预览!',3);
    abort;
  end;
  SetReport;
  ExtPrintReport1.preview;
end;

procedure TFrm_Bas_BomBatchReplace.Act_PrintExecute(Sender: TObject);
begin
  inherited;
  if (AdoQry_Main.active=False ) or ( (AdoQry_Main.active=True)
     and (AdoQry_Main.recordCount=0)) then
  begin
    DispInfo('没有数据可打印!',3);
    abort;
  end;
  SetReport;
  ExtPrintReport1.print(self);
end;

procedure TFrm_Bas_BomBatchReplace.SetReport;
var
  i,j:integer;
begin
  inherited;
  ExtPrintReport1.DataSet :=nil;
  ExtPrintReport1.Headers.clear;
  i:=0;
  with ExtPrintReport1 do
  begin
    for j:=0 to DBGridEH1.Columns.Count-1 do
      if DBGridEH1.Columns[j].Visible then
      begin
        Headers.Add;
        Headers.Items[i].Caption :=DBGridEH1.Columns[j].Title.Caption;
        Headers.Items[i].FieldName :=DBGridEH1.Columns[j].FieldName;
        Headers.Items[i].DisplayWidth:=DBGridEH1.Columns[j].Width div (DBGridEH1.Columns[j].Font.Size-2);
        Headers.Items[i].Alignment :=DBGridEH1.Columns[j].Alignment;
//        if(Temp_DBGridEh.Columns[j].PickList.Count>0)and
//          (Temp_DBGridEh.Columns[j].KeyList.Count>0)then
//        begin
//          Headers.Items[i].style:=dstransform;
//          Headers.Items[i].TransformFunction:=GetTransValue;
//        end
//        else
//          SetColumnsStyle(i,DBGridEH.Columns[j].FieldName);
        inc(i);
      end;
    DataSet:=AdoQry_Main;

    AdoQry_Tmp.Close;
    AdoQry_Tmp.SQL.Text:='Select SysParamValueC '+
                         'From SysParam '+
                         'where SysParamCode=''Name0''';//Name0是使用本系统的客户的名称
    AdoQry_Tmp.Open;

    ExtPrintReport1.Title1:=AdoQry_Tmp.fieldbyname('SysParamValueC').AsString;
    AdoQry_tmp.Close;