unitloadxlswuliao.pas

BOM表

  with TADOQuery.Create(nil) do
  begin
    Connection := env.ADOConnection;
    SQL.Text := 'select distinct MaterailGuiNum ,MaterailGuiType from App_MaterailPrice ' +
                ' where MaterailProgID = ' + inttostr(ObjID);

    Open;
    First;
    while not Eof do
    begin
      ADOQueryObjects.Append;
      ADOQueryObjects.FieldByName('ObjectName').Value := FieldByName('MaterailGuiNum').AsString;
      ADOQueryObjects.FieldByName('ObjectShortName').Value := FieldByName('MaterailGuiType').AsString;
      ADOQueryObjects.FieldByName('ObjectTypeID').Value := GuiTypeID;
      ADOQueryObjects.FieldByName('ObjectParentID').Value := ObjID;
      ADOQueryObjects.FieldByName('ObjectOwnerID').Value := Env.OperatorID;
      ADOQueryObjects.FieldByName('ObjectCreateDate').Value := DateTimeToStr(now());
      ADOQueryObjects.Post;//(arAll);
      ADOQueryObjects.Edit;
      ADOQueryObjects.FieldByName('ObjectFid').Value :=ADOQueryObjects.FieldByName('ObjectID').AsInteger;
      ADOQueryObjects.Post;
      GuiID := ADOQueryObjects.FieldByName('ObjectID').AsInteger;
      with TADOQuery.Create(nil) do
      begin
        Connection := env.ADOConnection;
        Sql.Text := 'insert into App_GuiType '+'select '+intTostr(GuiID)+','''',ManuGuiNum from App_ManuGuiName '+
                     ' where ProgID = (select ObjectParentID from Pub_Objects where ObjectID = '+intTostr(ObjID)+') and ManuGuiNo = '''+ADOQueryObjects.FieldByName('ObjectName').Value+'''';

        SQL.Text := Sql.Text + 'Update App_MaterailPrice set ObjectID = '+ inttostr(GuiID) +
                    ' where isnull(ObjectID,0) =0 and MaterailGuiNum = ''' +
                    ADOQueryObjects.FieldByName('ObjectName').AsString + '''' +
                    ' and MaterailProgID = '+ inttostr(ObjID);
        ExecSQL;
        Free;
      end;

      with TADOQuery.Create(nil) do
      begin
        Connection := Env.ADOConnection;
        SQl.Text := 'update Pub_Objects set ObjectLink=' + IntToStr(GuiID) +
                    ', ObjectFID = ' +IntToStr(GuiID) +
                     ' where ObjectID = ' + IntToStr(GuiID);
        ExecSQL;
        Free;
      end;

     // GuiType := FieldByName('MaterailGuiType').AsString;
      CreateGuiPrice(GuiID,FieldByName('MaterailGuiNum').AsString,
                        FieldByName('MaterailGuiType').AsString);

      {with ADOQueryGuiType do
      begin
        Append;
        FieldByName('ObjectID').Value := GuiID;
        FieldByName('Gui_Type').Value := GuiType;
        FieldByName('Gui_No').Value := 1;
        Post;
      end; }

      Next;
    end;
    Free;
  end;

end;


procedure TFormLoadXls.CreateGuiPrice(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 1=2';
    open;
    Append;

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


    FieldByName('FuJianPrice').AsFloat := ModelLocate('辅件',PriceValue,TempGuiType);
    FieldByName('FJXS').AsFloat :=  ADOQueryPBD.FieldByName('PriceBDData').AsFloat;
    FieldByName('FJBZ').Value :=  ADOQueryPBD.FieldByName('PriceBDMethod').AsString;

    TempRenGong :=  PriceValue + FieldByName('FuJianPrice').AsFloat;
    FieldByName('RenGongPrice').AsFloat := ModelLocate('人工',TempRenGong,TempGuiType);
    FieldByName('RGXS').AsFloat :=  ADOQueryPBD.FieldByName('PriceBDData').AsFloat;
    FieldByName('RGBZ').Value :=  ADOQueryPBD.FieldByName('PriceBDMethod').AsString;

    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;

    FieldByName('QiGuanPrice').AsFloat := ModelLocate('企管',TempValue,'');
    FieldByName('QGXS').AsFloat :=  ADOQueryPBD.FieldByName('PriceBDData').AsFloat;
    FieldByName('QGBZ').Value :=  ADOQueryPBD.FieldByName('PriceBDMethod').AsString;

    TempValue := TempValue + FieldByName('QiGuanPrice').AsFloat;
    FieldByName('LiRunPrice').AsFloat := ModelLocate('利润',TempValue,'');
    FieldByName('LRXS').AsFloat :=  ADOQueryPBD.FieldByName('PriceBDData').AsFloat;
    FieldByName('LRBZ').Value :=  ADOQueryPBD.FieldByName('PriceBDMethod').AsString;

    TempValue := TempValue + FieldByName('LiRunPrice').AsFloat;
    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;



Function TFormLoadXls.ModelLocate(PBDName: string;value:Double; PBDModel : String) : Double;
var
 PBDMethod : string;
 SumValue : Double;
 FomulaString :string;

begin

  with ADOQueryPBD do
  begin
    if  Active then Close;
    Connection := env.ADOConnection;
    SQL.Text := 'select * from App_PriceBaseData where PriceBDName = ''' +
                PBDName + '''  and isnull(PriceBDModel,'''') =''' + PBDModel +'''' ;
    open;
  end;

  if ADOQueryPBD.RecordCount = 0 then
  begin
    with ADOQueryPBD do
    begin
      if  Active then Close;
      Connection := env.ADOConnection;
      SQL.Text := 'select * from App_PriceBaseData where PriceBDName = ''' + PBDName + '''' +
                  ' and isnull(PriceBDModel,'''')='''' ';
      open;
    end;
    PBDMethod := ADOQueryPBD.FieldByName('PriceBDMethod').AsString;
    if PBDMethod = '系数' then
    begin
      Result := Value * ADOQueryPBD.FieldByName('PriceBDData').AsFloat;
    end;
    if PBDMethod = '固定值' then
    begin
      Result := ADOQueryPBD.FieldByName('PriceBDData').AsFloat;
    end;
  end
  else
  begin
    PBDMethod := ADOQueryPBD.FieldByName('PriceBDMethod').AsString;
    if PBDMethod = '系数' then
    begin
      Result := Value * ADOQueryPBD.FieldByName('PriceBDData').AsFloat;
    end;
    if PBDMethod = '固定值' then
    begin
      Result := ADOQueryPBD.FieldByName('PriceBDData').AsFloat;
    end;
  end;

end;

procedure TFormLoadXls.CreateGuiBaseData(ObjID: Integer);
var
 i : integer;
begin

  with  ADOQueryGuiBD do
  begin
   Connection := env.ADOConnection;
   SQL.Text := ' select * from App_PriceBaseData '+
               ' where PriceBDIsPart = 1 and PriceBDClassCode in '+
               ' (select MaterailClassCode from App_MaterailPrice where MaterailProgID = '+
               inttostr(ObjID) + ')';
    Open;
    //showmessage(intTostr(ObjID));
    First;
  end;

  with TADOQuery.Create(nil) do
  begin
    Connection := env.ADOConnection;
    SQL.Text := 'select * from App_ProjectBaseData where ObjectID='+inttostr(ObjID);
    //showmessage(sql.Text);
    Open;
    First;
    while not ADOQueryGuiBD.Eof do
    begin
      if not Locate('PriceBDClassCode',ADOQueryGuiBD.FieldByName('PriceBDClassCode').AsString,[]) then
      begin
        Append;
        for i := 0 to ADOQueryGuiBD.FieldCount -1 do
          FieldS[i+1].Value := ADOQueryGuiBD.Fields[i].Value ;
        FieldByName('ObjectID').Value := ObjID;
        Post;

      end;
      ADOQueryGuiBD.Next;
      First;
    end;

    Free;
  end;

end;



procedure TFormLoadXls.UpdateStatus(ObjID: Integer);
begin
  with TADOQuery.Create(nil) do
  begin
    Connection := Env.ADOConnection;
    SQl.Text := ' update app_projects set ProType=''报价中'' '+
                'where ProID=(select ObjectParentId from Pub_Objects '+
                ' where ObjectID= '+intTostr(ObjID)+')';
    ExecSQL;
    Free;
  end;
end;

procedure TFormLoadXls.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;



end.