itemreplace.pas

BOM表

unit itemReplace;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, cxLabel, cxControls, cxContainer, cxEdit, cxTextEdit,
  cxMaskEdit, cxButtonEdit, StdCtrls,Global,ADODB,frmFindItem,UnitLoadXlsWuLiao;

type
  TFormReplace = class(TForm)
    cxButtonEditS: TcxButtonEdit;
    cxButtonEditD: TcxButtonEdit;
    cxLabel1: TcxLabel;
    cxLabel2: TcxLabel;
    btnCurrent: TButton;
    btnAll: TButton;
    btnCancel: TButton;
    procedure cxButtonEditSPropertiesButtonClick(Sender: TObject;
      AButtonIndex: Integer);
    procedure cxButtonEditDPropertiesButtonClick(Sender: TObject;
      AButtonIndex: Integer);
    procedure btnCurrentClick(Sender: TObject);
    procedure btnAllClick(Sender: TObject);
    procedure ReplaceItem(GuiID:integer;MaterailGuiNum:string;MaterailGuiType:string);
  private
    { Private declarations }
  public
    { Public declarations }
    FobjectIDR:integer;
    cInvCodeS,cInvCNameS,cInvNameS,cInvStdS,cInvAddCodeS,cInvCodeD,cInvCNameD,cInvNameD,cInvStdD,cInvAddCodeD:string;
    iInvSPriceValue,BaseXiShu : Double;
    MaterailGuiNum,MaterailGuiType:string;
    MaterailClass,MaterailClassCode,MaterailClassCodeS:string;
  end;

var
  FormReplace: TFormReplace;

implementation



{$R *.dfm}

procedure TFormReplace.cxButtonEditSPropertiesButtonClick(Sender: TObject;
  AButtonIndex: Integer);
begin
   with TFormFindItem.Create(self) do
   begin

     ADOQueryType.Connection := Env.ADOConnection;
     ADOQueryItem.Connection := Env.ADOConnection;
     boolReplace := True;
     FobjectID := FobjectIDR;

     if not ADOQueryType.Active then ADOQueryType.Open;
     if showmodal = mrOk then
     begin
       cInvCodeS := ADOQueryItem.FieldByName('cInvCode').asstring;
       cInvCNameS := ADOQueryItem.FieldByName('cInvCName').asstring;//电料类别
       cInvNameS := ADOQueryItem.FieldByName('cInvName').asstring;//电料名称
       cInvStdS := ADOQueryItem.FieldByName('cInvStd').asstring;  //电料型号
       cInvAddCodeS := ADOQueryItem.FieldByName('cInvAddCode').asstring; //标号
       MaterailClassCodeS := ADOQueryItem.FieldByName('cInvCCode').asstring;

      cxButtonEditS.Text := cInvCNameS+'/'+ cInvNameS+'/'+ cInvStdS;

      Free;
     end;

   end;
end;

procedure TFormReplace.cxButtonEditDPropertiesButtonClick(Sender: TObject;
  AButtonIndex: Integer);
begin
   with TFormFindItem.Create(self) do
   begin
     ADOQueryType.Connection := Env.ADOConnection;
     ADOQueryItem.Connection := Env.ADOConnection;
     if not ADOQueryType.Active then ADOQueryType.Open;
     if showmodal = mrOk then
     begin
       cInvCodeD := ADOQueryItem.FieldByName('cInvCode').asstring;
       cInvCNameD := ADOQueryItem.FieldByName('cInvCName').asstring;//电料类别
       cInvNameD := ADOQueryItem.FieldByName('cInvName').asstring;//电料名称
       cInvStdD := ADOQueryItem.FieldByName('cInvStd').asstring;  //电料型号
       cInvAddCodeD := ADOQueryItem.FieldByName('cInvAddCode').asstring; //标号
       MaterailClass := ADOQueryItem.FieldByName('cInvCName').asstring; //类别名称
       MaterailClassCode := ADOQueryItem.FieldByName('cInvCCode').asstring; //类别ID


      cxButtonEditD.Text := cInvCNameD+'/'+ cInvNameD+'/'+ cInvStdD;


      Free;
     end;

   end;
end;

procedure TFormReplace.btnCurrentClick(Sender: TObject);
var
  PriceId:integer;
begin
   if (trim(cxButtonEditS.Text)='') or (trim(cxButtonEditD.Text)='') then
   begin
     showmessage('查找元器件 和 替换元器件 必须要选择');
     exit;
   end;

   with TFormLoadXls.Create(self) do
   begin
      with ADOQueryBaseXiShu do
      begin
        Connection := env.ADOConnection;
        SQL.Text := 'select * from App_PriceBaseData where PriceBDIsPart = 1 ';
        open;
      end;

      with ADOQueryInvClass do
      begin
        Connection := env.ADOConnection;
        SQL.Text := 'select * from InventoryClass ';
        open;
      end;

      with ADOQueryCost do
      begin
        if Active then Close;
        Connection := Env.ADOConnection;
        SQL.Text := 'Select * from Inventory ';
        Open;
      end;

     // 取新元器件的成本和报价
     CostAndPrice(cInvCodeD,'');

     ///添加类别基础
     With TADoquery.Create(self) do
     begin
       Connection := Env.ADOConnection;

     ///更新元器件成本和报价
       Sql.Text := 'update App_MaterailPrice set MaterailName='''+cInvNameD+''','+
                   'MaterailType='''+cInvStdD+''',MaterailShortName='''+cInvAddCodeD+''',MaterailNum='''+cInvCodeD+''','+
                   'MaterailCost='+FloatTostr(iInvSPriceValue)+',MaterailCostSum=MaterailCount*'+FloatTostr(iInvSPriceValue)+','+
                   'MaterailPrice='+FloatTostr(BaseXiShu * iInvSPriceValue)+',MaterailPriceSum=MaterailCount*'+FloatTostr(BaseXiShu * iInvSPriceValue)+','+
                   'MaterailClass='''+MaterailClass+''',MaterailClassCode = '''+MaterailClassCode+''''+
                   ' where MaterailNum='''+cInvCodeS+''' and ObjectID='+intTostr(FobjectIDR);
       try
         ExecSql;

          Sql.Text := 'select ObjectParentID from Pub_Objects where ObjectID ='+intTostr(FobjectIDR);
          Open;
          PriceId :=FieldByName('ObjectParentID').AsInteger;
          CreateGuiBaseData(PriceId);

           ///判断是否要删除旧类别
          sql.Text := 'select * from App_MaterailPrice where MaterailClassCode='''+MaterailClassCodeS+''' and MaterailProgID='+ intTostr(PriceId);
          Open;
          if Eof then
          begin
            sql.Text := 'delete from App_ProjectBaseData where ObjectID='+intTostr(PriceID)+' and PriceBDClassCode='''+MaterailClassCodeS+'''';
            ExecSql;
          end;
          Close;

          UpdateGuiPrice(FobjectIDR,MaterailGuiNum,MaterailGuiType);


         //ExecSQL;
         //showmessage('替换成功');
       except
         showmessage('替换失败');
       end;
       Free;
     end;
   end;
end;

procedure TFormReplace.btnAllClick(Sender: TObject);
begin
   if (trim(cxButtonEditS.Text)='') or (trim(cxButtonEditD.Text)='') then
   begin
     showmessage('查找元器件 和 替换元器件 必须要选择');
     exit;
   end;

  ///列出当前报价中有改元器件的柜
  with Tadoquery.Create(self) do
  begin
    Connection := Env.ADOConnection;
    Sql.Text := ' SELECT DISTINCT ObjectID, MaterailGuiNum,MaterailGuiType '+
                ' FROM App_MaterailPrice where MaterailNum ='''+cInvCodeS+''' and MaterailProgID = '+
                ' (select ObjectParentID from Pub_Objects where ObjectID='+intTostr(FobjectIDR)+')';
    Open;
    First;
    While not Eof do
    begin
      ReplaceItem(FieldByName('ObjectID').AsInteger,FieldByName('MaterailGuiNum').AsString,FieldByName('MaterailGuiType').AsString);
      Next;
    end;
    Close;
    Free;
  end;

end;




procedure TFormReplace.ReplaceItem(GuiID: integer; MaterailGuiNum,
  MaterailGuiType: string);
var
  PriceId:integer;
begin
   if (trim(cxButtonEditS.Text)='') or (trim(cxButtonEditD.Text)='') then
   begin
     showmessage('查找元器件 和 替换元器件 必须要选择');
     exit;
   end;

   with TFormLoadXls.Create(self) do
   begin
      with ADOQueryBaseXiShu do
      begin
        Connection := env.ADOConnection;
        SQL.Text := 'select * from App_PriceBaseData where PriceBDIsPart = 1 ';
        open;
      end;

      with ADOQueryInvClass do
      begin
        Connection := env.ADOConnection;
        SQL.Text := 'select * from InventoryClass ';
        open;
      end;

      with ADOQueryCost do
      begin
        if Active then Close;
        Connection := Env.ADOConnection;
        SQL.Text := 'Select * from Inventory ';
        Open;
      end;
     // 取新元器件的成本和报价
     CostAndPrice(cInvCodeD,'');

     ///添加类别基础
     With TADoquery.Create(self) do
     begin
       Connection := Env.ADOConnection;

     ///更新元器件成本和报价
       Sql.Text := 'update App_MaterailPrice set MaterailName='''+cInvNameD+''','+
                   'MaterailType='''+cInvStdD+''',MaterailShortName='''+cInvAddCodeD+''',MaterailNum='''+cInvCodeD+''','+
                   'MaterailCost='+FloatTostr(iInvSPriceValue)+',MaterailCostSum=MaterailCount*'+FloatTostr(iInvSPriceValue)+','+
                   'MaterailPrice='+FloatTostr(BaseXiShu * iInvSPriceValue)+',MaterailPriceSum=MaterailCount*'+FloatTostr(BaseXiShu * iInvSPriceValue)+','+
                   'MaterailClass='''+MaterailClass+''',MaterailClassCode = '''+MaterailClassCode+''''+
                   ' where MaterailNum='''+cInvCodeS+''' and ObjectID='+intTostr(GuiID);
       try
         ExecSql;

          Sql.Text := 'select ObjectParentID from Pub_Objects where ObjectID ='+intTostr(GuiID);
          Open;
          PriceId :=FieldByName('ObjectParentID').AsInteger;
          CreateGuiBaseData(PriceId);

           ///判断是否要删除旧类别
          sql.Text := 'select * from App_MaterailPrice where MaterailClassCode='''+MaterailClassCodeS+''' and MaterailProgID='+ intTostr(PriceId);
          Open;
          if Eof then
          begin
            sql.Text := 'delete from App_ProjectBaseData where ObjectID='+intTostr(PriceID)+' and PriceBDClassCode='''+MaterailClassCodeS+'''';
            ExecSql;
          end;
          Close;

          UpdateGuiPrice(GuiID,MaterailGuiNum,MaterailGuiType);


         //ExecSQL;
         //showmessage('替换成功');
       except
         showmessage('替换失败');
       end;
       Free;
     end;
   end;

end;

end.