unitloadxlswuliao.pas

BOM表

unit UnitLoadXlsWuLiao;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, dxExEdtr, cxControls, cxSSheet, dxCntner, dxTL,
  dxDBCtrl, dxDBGrid, dxDBTLCl, dxGrClms, DB, ADODB,Global, ComCtrls,
  Buttons, cxLabel, cxContainer, cxEdit, cxTextEdit, cxMaskEdit,
  cxDropDownEdit,DateUtils, ExtCtrls, OleServer, Excel2000,SHellApi;


type
  TFormLoadXls = class(TForm)
    OpenDialog1: TOpenDialog;
    ADOConnection1: TADOConnection;
    ADOConnXls: TADOConnection;
    DataSourceXls: TDataSource;
    ADOQueryXls: TADOQuery;
    ButtonAll: TButton;
    ADOQueryMaterial: TADOQuery;
    ADOQueryObjects: TADOQuery;
    ADOQueryCost: TADOQuery;
    ADOQueryGuiPrice: TADOQuery;
    ADOQueryPBD: TADOQuery;
    ADOQueryInvClass: TADOQuery;
    ADOQueryBaseXiShu: TADOQuery;
    ADOQueryGuiBD: TADOQuery;
    ADOQManuGui: TADOQuery;
    ADOQManuBom: TADOQuery;
    ADOQueryGuiType: TADOQuery;
    procedure FormDestroy(Sender: TObject);
    Procedure XslDataIntoMSSQL(GuiID: integer;ProgrammID : integer);
    procedure LoadGuiBom(ProgID : integer);
    procedure ButtonAllClick(Sender: TObject);
    procedure CreateGuiNum(ObjID : Integer);
    procedure CostAndPrice(IncCode,PriceObjectID : string);
    procedure CreateGuiPrice(GuiObjID : Integer;GuiNum,GuiType : string);
    procedure UpdateGuiPrice(GuiObjID : Integer;GuiNum,GuiType : string);
    Function ModelLocate(PBDName: string;value:Double;PBDModel : String): Double;
    procedure CreateGuiBaseData(ObjID : Integer);

    procedure UpdateStatus(ObjID : Integer);
  private
    { Private declarations }
    progressBar:TprogressBar;
    progressBarRect: TRect;
    
  public
    { Public declarations }
    ObjID : string;
    iInvSPriceValue : Double;
    BaseXiShu : Double;
    XlsFilePath : TStringList;
  end;
    

var
  FormLoadXls: TFormLoadXls;

implementation

uses StrUtils;

{$R *.dfm}    


procedure TFormLoadXls.FormDestroy(Sender: TObject);
begin
  if Assigned(XlsFilePath) then XlsFilePath.Free;
   Application.Handle := 0;
end;

Procedure TFormLoadXls.XslDataIntoMSSQL(GuiID: integer;
                       ProgrammID : integer
                        );
var
  i:integer;
  YQJPrice : string;
  YQJXS : Double;     //元器件系数
  strClass,strClassCode:string;
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;

  with ADOQManuBom do
  begin
    if Active then Close;
    Connection := env.ADOConnection;
    SQl.Text := 'select id,app_ManuBom.ManuGuiID,ManuBomFixPosition,ManuBomID,ManuBomShortName,'
              +'ManuBomName,ManuBomType,ManuBomCount,ManuBomMemo,ManuBomNum,App_ManuGuiName.ManuGuiNo,'
              +'App_ManuGuiName.ManuGuiType from app_ManuBom,App_ManuGuiName '
              +'where App_ManuGuiName.ManuGuiID =app_ManuBom.ManuGuiID and app_ManuBom.ManuGuiID = ' + IntToStr(GuiID);
    Open;
    First;
  end;

  with ADOQueryMaterial do
  begin
    Connection := Env.ADOConnection;
    if not Active then Open;
    while not ADOQManuBom.Eof  do
    begin
      Append;
      for i := 2 to ADOQManuBom.FieldCount -1 do
        FieldS[i].Value := ADOQManuBom.Fields[i].Value ;
      FieldByName('MaterailProgID').Value := ProgrammID;
      FieldByName('MaterailXiShu').Value := ADOQueryBaseXiShu.FieldByName('PriceBDData').AsVariant;  ////

      With TAdoquery.Create(self) do
      begin
        Connection := Env.ADOConnection;
        Sql.Text := ' SELECT InventoryClass.cInvCName, InventoryClass.cInvCCode '+
                    ' FROM Inventory INNER JOIN InventoryClass ON '+
                    ' Inventory.cInvCCode = InventoryClass.cInvCCode '+
                    ' WHERE Inventory.cInvCode = '''+ADOQManuBom.FieldByName('ManuBomNum').AsString+'''';
        open;
        if Not Eof then
        begin
          strClass := FieldByName('cInvCName').AsString;
          strClassCode := FieldByName('cInvCCode').AsString;
        end;
        Close;
        Free;
      end;

      FieldByName('MaterailClass').Value := strClass;  ////
      FieldByName('MaterailClassCode').Value := strClassCode;  ////

      CostAndPrice(FieldByName('MaterailNum').AsString,FieldByName('ObjectID').AsString);

      FieldByName('MaterailCost').Value := iInvSPriceValue;
      FieldByName('MaterailCostSum').Value := FieldByName('MaterailCount').AsInteger * iInvSPriceValue;
      FieldByName('MaterailPrice').Value := BaseXiShu * iInvSPriceValue;
      FieldByName('MaterailPriceSum').Value :=  BaseXiShu * FieldByName('MaterailCount').AsInteger * iInvSPriceValue;
      Post;

      ADOQManuBom.Next;
    end;
  end;

end;

procedure TFormLoadXls.CostAndPrice(IncCode,PriceObjectID : string);
begin

  with ADOQueryCost do
  begin
    if Active then Close;
    Connection := Env.ADOConnection;
    SQL.Text := 'Select * from Inventory where cInvCode = ''' + IncCode + '''';
    Open;
    if Not Eof then
      iInvSPriceValue := FieldByName('iInvSPrice').AsFloat
    Else
      iInvSPriceValue := 0;
   end;



  with TADOQuery.Create(nil) do
  begin
    Connection :=  env.ADOConnection;
    SQL.Text := 'select * from App_ProjectBaseData where ObjectID = ' + PriceObjectID +
                  ' and PriceBDClassCode = ''' + ADOQueryCost.FieldByName('cInvCCode').AsString +
                  '''' ;
    Open;
    if Not Eof then
      BaseXiShu := FieldByName('PriceBDData').AsFloat
    else
    begin
 
      with ADOQueryBaseXiShu do
      begin
        if Active then Close;
        Connection := env.ADOConnection;
        SQL.Text := 'select * from App_PriceBaseData where PriceBDIsPart = 1 ' +
                    ' and PriceBDClassCode = ''' + ADOQueryCost.FieldByName('cInvCCode').AsString + '''';
        open;
        if not Eof then BaseXiShu := ADOQueryBaseXiShu.FieldByName('PriceBDData').AsFloat
        else
          BaseXiShu := 0;
      end;
    end;
    Free;
  end;

    with ADOQueryInvClass do
    begin
      if Active then Close;
      Connection := env.ADOConnection;
      SQL.Text := 'select * from InventoryClass  where cInvCCode = ''' + ADOQueryCost.FieldByName('cInvCCode').AsString + '''';
      open;
    end;

end;

procedure TFormLoadXls.LoadGuiBom(ProgID : integer);
begin

  //
end;


procedure TFormLoadXls.ButtonAllClick(Sender: TObject);
var
  i,m,c : integer;
  Str,StrFileName : string;
  FileCount : integer;
  ProgrammID : integer;
  GuiCount : integer;
begin

  ProgrammID := ADOQueryObjects.FieldByName('ObjectID').AsInteger;

  with TADOQuery.Create(nil) do
  begin
    Connection := env.ADOConnection;
    SQL.Text := 'select count(*) as GuiCount from Pub_Objects where isnull(ObjectDeleteFlag,0)=0 ' +
               'and  ObjectParentID = ' + inttostr(ProgrammID);
    Open;
    GuiCount := FieldByName('GuiCount').AsInteger;
    Free;
  end;

  if GuiCount > 0 then
  begin
    if  MessageBox(self.Handle,'柜号数据已生成 确定要覆盖？','提示',MB_YESNO) = 6 then
    begin

      with TADOQuery.Create(nil) do
      begin
        Connection := env.ADOConnection;
        SQL.Text := 'update Pub_Objects  set ObjectDeleteFlag = 1  ' +
                    ' where ObjectParentID = ' + inttostr(ProgrammID);
        ExecSQL;
        Free;
      end;
      with TADOQuery.Create(nil) do
      begin
        Connection := env.ADOConnection;
        SQL.Text := 'delete App_MaterialPrice_Gui where Objectid in '+
                    '(select Objectid from App_MaterailPrice where '+
                    ' MaterailProgID = ' + inttostr(ProgrammID) + ')'  ;
        ExecSQL;
        Free;
      end;
      with TADOQuery.Create(nil) do
      begin
        Connection := env.ADOConnection;
        SQL.Text := 'delete App_MaterailPrice where MaterailProgID = ' + inttostr(ProgrammID);
        ExecSQL;
        Free;
      end;
      with TADOQuery.Create(nil) do
      begin
        Connection := env.ADOConnection;
        SQL.Text := 'delete App_ProjectBaseData where ObjectID = ' + inttostr(ProgrammID);
        ExecSQL;
        Free;
      end;

      with ADOQManuGui do
      begin
        if Active then Close;
        Connection := env.ADOConnection;
        SQL.Text := 'select * from app_ManuGuiName where  ShenPi = ''审批通过'' and ProgID = '+
                    ' (select ObjectParentID from Pub_Objects where ObjectID = ' +
                     ADOQueryObjects.FieldByName('ObjectID').AsString +
                    ' and IsNull(ObjectDeleteFlag,0) = 0 ) ';
        Open;
        First;
        while not  Eof do
        begin
          XslDataIntoMSSQL(ADOQManuGui.FieldByName('ManuGuiID').AsInteger,ProgrammID);//数据导入处理过程
          Next;
        end;
      end;

      CreateGuiNum(ProgrammID);   //生成柜号

      CreateGuiBaseData(ProgrammID); //生成基础数据

      UpdateStatus(ProgrammID);     //设置报价状态

        Showmessage(IntToStr(ADOQManuGui.RecordCount)+'个柜数据成功导入');

      SendMsgToIPensee('data:' + IntToStr(ProgrammID));

    end
    else
      Exit

  end
  else
  begin

    with ADOQManuGui do
    begin
      if Active then Close;
      Connection := env.ADOConnection;
      SQL.Text := 'select * from app_ManuGuiName where  ShenPi = ''审批通过'' and ProgID = '+
                  ' (select ObjectParentID from Pub_Objects where ObjectID = ' +
                   ADOQueryObjects.FieldByName('ObjectID').AsString +
                  ' and IsNull(ObjectDeleteFlag,0) = 0 ) ';
      Open;
      First;
      while not  Eof do
      begin
        XslDataIntoMSSQL(ADOQManuGui.FieldByName('ManuGuiID').AsInteger,ProgrammID);//数据导入处理过程
        Next;
      end;
    end;

    CreateGuiNum(ProgrammID);   //生成柜号

    CreateGuiBaseData(ProgrammID); //生成基础数据

    UpdateStatus(ProgrammID);     //设置报价状态

    Showmessage(IntToStr(ADOQManuGui.RecordCount)+'个柜数据成功导入');
   
    SendMsgToIPensee('data:' + IntToStr(ProgrammID));

  end;


end;




procedure TFormLoadXls.CreateGuiNum(ObjID : Integer);
var
  GuiTypeID : integer;      //柜的ObjectTypeID
  GuiID : integer;          //柜的ObjectID
  GuiType : string;
begin

  with TADOQuery.Create(nil) do
  begin
    Connection := env.ADOConnection;
    SQL.Text := 'Select * from Pub_ObjectType where ObjectTypeName = ''柜'' ';
    Open;
    GuiTypeID := FieldByName('ObjectTypeID').AsInteger;
    Free;
  end;

  with ADOQueryGuiType do
  begin
    Connection := env.ADOConnection;
    SQL.Text := 'Select * from App_GuiType where 1=2';
    Open;
  end;