unitcost.pas

BOM表

unit UnitCost;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, dxExEdtr, DB, ADODB, StdCtrls, ExtCtrls, dxTL, dxDBCtrl,
  dxDBGrid, dxCntner;

type
  TFormCost = class(TForm)
    dxDBGridBOM: TdxDBGrid;
    dxDBGridBOMColumn1: TdxDBGridColumn;
    dxDBGridBOMColumn2: TdxDBGridColumn;
    dxDBGridBOMColumn3: TdxDBGridColumn;
    dxDBGridBOMColumn6: TdxDBGridColumn;
    dxDBGridBOMColumn4: TdxDBGridColumn;
    dxDBGridBOMColumn5: TdxDBGridColumn;
    Panel1: TPanel;
    ButtonAll: TButton;
    ButtonSigle: TButton;
    DSCostPlace: TDataSource;
    ADOQueryCostReplace: TADOQuery;
    dxDBGridBOMColumn7: TdxDBGridColumn;
    Button1: TButton;
    ADOQueryCost: TADOQuery;
    ADOQueryBaseXiShu: TADOQuery;
    ADOQueryInvClass: TADOQuery;
    ADOQueryGuiPrice: TADOQuery;
    dxDBGridBOMColumn8: TdxDBGridColumn;
    dxDBGridBOMColumn9: TdxDBGridColumn;
    procedure ButtonSigleClick(Sender: TObject);
    procedure ButtonAllClick(Sender: TObject);
    procedure CostAndPrice(IncCode,PriceObjectID : string);
    procedure UpdateGuiPrice(GuiObjID: Integer; GuiNum, GuiType: string);
    procedure Button1Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
  public
    { Public declarations }
    ReplaceAll : Boolean;
    //ObjID : string;
    iInvSPriceValue : Double;
    BaseXiShu : Double;
  end;

var
  FormCost: TFormCost;

implementation

uses UnitLoadXlsWuLiao, Global;

{$R *.dfm}

procedure TFormCost.ButtonSigleClick(Sender: TObject);
var
  cInvCodeD : String;
  cInvCCodeD : String;
  GuiObjID : Integer;
  cInvNameD : string;
  cInvTypeD  : string;
  cInvID : Integer;
  PriceId : Integer;
begin
  cInvID :=  ADOQueryCostReplace.FieldByName('ID').AsInteger;
  cInvCodeD := ADOQueryCostReplace.FieldByName('MaterailNum').Asstring;
  cInvCCodeD := ADOQueryCostReplace.FieldByName('MaterailClassCode').AsString;
  GuiObjID := ADOQueryCostReplace.FieldByName('ObjectID').AsInteger;
  cInvNameD  :=  ADOQueryCostReplace.FieldByName('MaterailName').AsString;

     CostAndPrice(cInvCodeD,IntToStr(GuiObjID));

     with TADOQuery.Create(nil) do
     begin
       Connection := env.ADOConnection;
       SQl.Text := 'Update App_MaterailPrice set MaterailCost = ' + FloatTostr(iInvSPriceValue)+
                   ',MaterailCostSum=MaterailCount*'+FloatTostr(iInvSPriceValue)+','+
                   'MaterailPrice='+FloatTostr(BaseXiShu * iInvSPriceValue)+
                   ',MaterailPriceSum=MaterailCount*'+FloatTostr(BaseXiShu * iInvSPriceValue)+
                   ',MaterailXiShu = ' + FloatTostr(BaseXiShu)+
                  // 'where ObjectID = ' + IntToStr(PriceObjID) + 'and MaterailNum = ''' + cInvCodeD + '''' ;
                  // ' and MaterailName = ''' + cInvNameD + '''' ;
                  'where id = ' + IntTostr(cInvID);
       Try
         ExecSQL;
         with TADOQuery.Create(nil) do
         begin
           Connection := Env.ADOConnection;
           SQL.Text := 'select * from App_MaterialPrice_Gui where ObjectID = ' + IntToStr(GuiObjID);
           Open;
           UpdateGuiPrice(GuiObjID, FieldByName('GuiNum').AsString , FieldByName('GuiType').AsString);
           Free;
         end;
         if not ReplaceAll then
           showmessage('成本刷新成功');
       Except
         if not ReplaceAll then
           showmessage('成本刷新失败');

       End;  

       Free;
     end;

end;

procedure TFormCost.ButtonAllClick(Sender: TObject);
begin
  ReplaceAll := True;
  with ADOQueryCostReplace do
  begin
    First;
    while not Eof do
    begin
      Try
         ButtonSigle.Click;
         Next;
      except
        showmessage(' 本条数据刷新失败 ');
      end;
    end;
    showmessage(' 成本数据刷新成功 ');
  end;
   ReplaceAll := False;
end;


procedure TFormCost.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
    if Active then Close;
    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 TFormCost.UpdateGuiPrice(GuiObjID: Integer; GuiNum,
  GuiType: string);
var
 CostValue : Double;
 PriceValue : Double;
 TempValue : Double;
 TempGuiType : String;
 TempRenGong : Double;

begin

  with TADOQuery.Create(nil) do
  begin
    Connection := env.ADOConnection;
    SQL.Text := ' select sum(MaterailCostSum) as CostSum,Sum(MaterailPriceSum) as PriceSum'+
                ' from App_MaterailPrice '+
                 ' where ObjectID = ' + inttostr(GuiObjID);
    Open;
    CostValue := FieldByName('CostSum').AsFloat;
    PriceValue := FieldByName('PriceSum').AsFloat;
    Free;
  end;

  with TADOQuery.Create(nil) do
  begin
    Connection := env.ADOConnection;
    SQL.Text := ' select distinct MaterailGuiType from App_MaterailPrice '+
                 ' where ObjectID = ' + inttostr(GuiObjID);
    Open;
    TempGuiType := FieldByName('MaterailGuiType').AsString;
    Free;
  end;

  with ADOQueryGuiPrice do
  begin
    Connection := env.ADOConnection;
    SQL.Text := 'select * from App_MaterialPrice_Gui where ObjectID='+inttostr(GuiObjID);
    open;


    //FieldByName('ObjectID').Value := GuiObjID;
    //FieldByName('GuiNum').Value := GuiNum;
    //FieldByName('GuiType').Value := GuiType;

    Edit;
    //FieldByName('FuJianPrice').AsFloat := ModelLocate('辅件',PriceValue,TempGuiType);
    //FieldByName('FJXS').AsFloat :=  ADOQueryPBD.FieldByName('PriceBDData').AsFloat;
    //FieldByName('FJBZ').Value :=  ADOQueryPBD.FieldByName('PriceBDMethod').AsString;
    if FieldByName('FJBZ').Value = '系数' then
      FieldByName('FuJianPrice').AsFloat := PriceValue * FieldByName('FJXS').AsFloat;


    TempRenGong :=  PriceValue + FieldByName('FuJianPrice').AsFloat;

    if FieldByName('RGBZ').Value = '系数' then
      FieldByName('RenGongPrice').AsFloat := TempRenGong * FieldByName('RGXS').AsFloat;
    //FieldByName('RenGongPrice').AsFloat := ModelLocate('人工',TempRenGong,TempGuiType);
    //FieldByName('RGXS').AsFloat :=  ADOQueryPBD.FieldByName('PriceBDData').AsFloat;
    //FieldByName('RGBZ').Value :=  ADOQueryPBD.FieldByName('PriceBDMethod').AsString;


    //showmessage(ADOQueryPBD.FieldByName('PriceBDMethod').AsString);

    //if ADOQueryPBD.FieldByName('PriceBDMethod').AsString = '系数' then
    //    FieldByName('GuiJiaPrice').AsFloat := ModelLocate('柜架',PriceValue,TempGuiType);
    //FieldByName('GJXS').AsFloat :=  ADOQueryPBD.FieldByName('PriceBDData').AsFloat;
    //FieldByName('GJBZ').Value :=  ADOQueryPBD.FieldByName('PriceBDMethod').AsString;

    TempValue := FieldByName('FuJianPrice').AsFloat + FieldByName('RenGongPrice').AsFloat +
                    FieldByName('GuiJiaPrice').AsFloat + PriceValue ;   //小计

    FieldByName('XJPrice').AsFloat := TempValue;

    if FieldByName('QGBZ').Value = '系数' then
      FieldByName('QiGuanPrice').AsFloat := FieldByName('QGXS').AsFloat * TempValue;
    //FieldByName('QiGuanPrice').AsFloat := ModelLocate('企管',TempValue,'');
    //FieldByName('QGXS').AsFloat :=  ADOQueryPBD.FieldByName('PriceBDData').AsFloat;
    //FieldByName('QGBZ').Value :=  ADOQueryPBD.FieldByName('PriceBDMethod').AsString;

    TempValue := TempValue + FieldByName('QiGuanPrice').AsFloat;

    if FieldByName('LRBZ').Value = '系数' then
      FieldByName('LiRunPrice').AsFloat := FieldByName('LRXS').AsFloat * TempValue;
    //FieldByName('LiRunPrice').AsFloat := ModelLocate('利润',TempValue,'');
    //FieldByName('LRXS').AsFloat :=  ADOQueryPBD.FieldByName('PriceBDData').AsFloat;
    //FieldByName('LRBZ').Value :=  ADOQueryPBD.FieldByName('PriceBDMethod').AsString;

    TempValue := TempValue + FieldByName('LiRunPrice').AsFloat;
    if FieldByName('SJBZ').Value = '系数' then
      FieldByName('ShuiJinPrice').AsFloat := FieldByName('SJXS').AsFloat * TempValue;

    //FieldByName('ShuiJinPrice').AsFloat := ModelLocate('税金',TempValue,'');
    //FieldByName('SJXS').AsFloat :=  ADOQueryPBD.FieldByName('PriceBDData').AsFloat;
    //FieldByName('SJBZ').Value :=  ADOQueryPBD.FieldByName('PriceBDMethod').AsString;

    FieldByName('HJPrice').AsFloat := TempValue + FieldByName('ShuiJinPrice').AsFloat;
    FieldByName('HJCost').AsFloat := TempValue + FieldByName('ShuiJinPrice').AsFloat;


    updateBatch(arAll);
    //Post;
  end;
end;


procedure TFormCost.Button1Click(Sender: TObject);
begin
  ModalResult := mrOK;

end;

procedure TFormCost.FormClose(Sender: TObject; var Action: TCloseAction);
begin
   ModalResult := mrOK;
end;

end.