Measure/Report/ProjGather/rpgGatherData.pas

unit rpgGatherData;

interface

uses
  ADODB, ProjGather, rpgGatherProjDm, rpgBillsDm, rpgBillsCalcDm, Classes,
  ScAutoUpdateUnit, ProjGatherTree;

type
  TrpgGatherData = class
  private
    FGatherFile: string;
    FConnection: TADOConnection;
    FQuery: TADOQuery;

    procedure ExecuteSql(const ASql: string);

    procedure ClearHistoryData;

    procedure CreateDataTables(AProjCount, ASProjCount: Integer);

    procedure SaveGatherProjInfo(AProjs, ASProjs: TList);
    procedure SaveBills(ATree: TProjGatherTree);
    procedure SaveBillsGatherCalc(ATree: TProjGatherTree);
    procedure SaveBillsProjCalc(ATree: TProjGatherTree; AProjIndex: Integer);
    procedure SaveBillsSpecialProjCalc(ATree: TProjGatherTree; AProjType: Integer);
    procedure SaveGatherData(AGather: TProjGather);

    procedure CalcDgnData(const ATableName: string);
    procedure CalcOtherData(AProjCount, ASProjCount: Integer);

    procedure TransposeProjCalc(AProjCount: Integer);

    function GetCurSpecialProjCount: Integer;
  protected
    procedure AddTables(AProjCount, ASProjCount: Integer; AUpdater: TScUpdater); virtual;
  public
    constructor Create;
    destructor Destroy; override;

    procedure WriteGatherData(AGather: TProjGather);

    procedure LoadRelaData(AProjectID: Integer);
    procedure UpdateDataBase(ASpecialProjTypes: TStrings);

    property GatherFile: string read FGatherFile;
    property Connection: TADOConnection read FConnection;
  end;

implementation

uses
  UtilMethods, SysUtils, Connections, ZhAPI, rProjGatherTables,
  ConditionalDefines, ProjectData, Globals;

{ TrpgGatherData }

procedure TrpgGatherData.AddTables(AProjCount, ASProjCount: Integer;
  AUpdater: TScUpdater);
var
  iProj: Integer;
begin
  AUpdater.AddTableDef(SGatherProj, @tdGatherProj, Length(tdGatherProj), False, False);
  AUpdater.AddTableDef(SBills, @tdBills, Length(tdBills), False, False);
  AUpdater.AddTableDef(SBills_Gather, @tdBills_Calc, Length(tdBills_Calc), False, False);
  for iProj := 0 to AProjCount - 1 do
    AUpdater.AddTableDef(SBills_Proj+IntToStr(iProj+1), @tdBills_Calc, Length(tdBills_Calc), False, False);
  AUpdater.AddTableDef(SBills_TransProj, @tdBills_Calc, Length(tdBills_Calc), False, False);
  for iProj := 0 to ASProjCount - 1 do
    AUpdater.AddTableDef(SBills_SProj+IntToStr(iProj+1), @tdBills_Calc, Length(tdBills_Calc), False, False);
end;

procedure TrpgGatherData.CalcDgnData(const ATableName: string);
const
  sFinalDgn = 'Update %s Set'+
              '    FinalDgnQuantity1 = DealDgnQuantity1 + CDgnQuantity1,'+
              '    FinalDgnQuantity2 = DealDgnQuantity2 + CDgnQuantity2';
  sDgnPrice1_2 = 'Update %s Set'+
                 '    DgnPrice1 = iif(DgnQuantity1 <> 0, TotalPrice/DgnQuantity1, 0),'+
                 '    DgnPrice1_Rc = iif(DgnQuantity1 <> 0, TotalPrice_Rc/DgnQuantity1, 0),'+
                 '    DgnPrice2 = iif(DgnQuantity2 <> 0, TotalPrice/DgnQuantity2, 0),'+
                 '    DgnPrice2_Rc = iif(DgnQuantity2 <> 0, TotalPrice_Rc/DgnQuantity2, 0),'+
                 '    FinalDgnPrice1 = iif(FinalDgnQuantity1 <> 0, AddGatherTotalPrice/FinalDgnQuantity1, 0),'+
                 '    FinalDgnPrice1_Rc = iif(FinalDgnQuantity1 <> 0, AddGatherTotalPrice_Rc/FinalDgnQuantity1, 0),'+
                 '    FinalDgnPrice2 = iif(FinalDgnQuantity2 <> 0, AddGatherTotalPrice/FinalDgnQuantity2, 0),'+
                 '    FinalDgnPrice2_Rc = iif(FinalDgnQuantity2 <> 0, AddGatherTotalPrice_Rc/FinalDgnQuantity2, 0)';
  sDgn = 'Update %s Set'+
         '  DgnQuantity = iif(DgnQuantity1 <> 0, iif(DgnQuantity2 <> 0, DgnQuantity1&''/''&DgnQuantity2, DgnQuantity1), ''''),'+
         '  DgnPrice = iif(DgnPrice1 <> 0, iif(DgnPrice2 <> 0, DgnPrice1&''/''&DgnPrice2, DgnPrice1), ''''),'+
         '  DgnPrice_Rc = iif(DgnPrice1_Rc <> 0, iif(DgnPrice2_Rc <> 0, DgnPrice1_Rc&''/''&DgnPrice2_Rc, DgnPrice1_Rc), ''''),'+
         '  DealDgnQuantity = iif(DealDgnQuantity1 <> 0, iif(DealDgnQuantity2 <> 0, DealDgnQuantity1&''/''&DealDgnQuantity2, DealDgnQuantity1), ''''),'+
         '  CDgnQuantity = iif(CDgnQuantity1 <> 0, iif(CDgnQuantity2 <> 0, CDgnQuantity1&''/''&CDgnQuantity2, CDgnQuantity1), ''''),'+
         '  FinalDgnQuantity = iif(FinalDgnQuantity1 <> 0, iif(FinalDgnQuantity2 <> 0, FinalDgnQuantity1&''/''&FinalDgnQuantity2, FinalDgnQuantity1), ''''),'+
         '  FinalDgnPrice = iif(FinalDgnPrice1 <> 0, iif(FinalDgnPrice2 <> 0, FinalDgnPrice1&''/''&FinalDgnPrice2, FinalDgnPrice1), ''''),'+
         '  FinalDgnPrice_Rc = iif(FinalDgnPrice1_Rc <> 0, iif(FinalDgnPrice2_Rc <> 0, FinalDgnPrice1_Rc&''/''&FinalDgnPrice2_Rc, FinalDgnPrice1_Rc), '''')';
begin
  ExecuteSql(Format(sFinalDgn, [ATableName]));
  ExecuteSql(Format(sDgnPrice1_2, [ATableName]));
  ExecuteSql(Format(sDgn, [ATableName]));
end;

procedure TrpgGatherData.CalcOtherData(AProjCount, ASProjCount: Integer);
var
  iProj: Integer;
begin
  CalcDgnData(SBills_Gather);
  for iProj := 0 to AProjCount - 1 do
    CalcDgnData(SBills_Proj+IntToStr(iProj+1));
  CalcDgnData(SBills_TransProj);
  for iProj := 0 to ASProjCount - 1 do
    CalcDgnData(SBills_SProj+IntToStr(iProj+1));
end;

procedure TrpgGatherData.ClearHistoryData;
var
  FTableList: TStringList;
  iIndex: Integer;
  sDeleteTableSql: String;
begin
  FTableList := TStringList.Create;
  try
    FConnection.GetTableNames(FTableList);
    iIndex := 0;
    while iIndex < FTableList.Count do
    begin
      if Pos('r_', FTableList.Strings[iIndex]) = 1 then
      begin
        sDeleteTableSql := Format('Drop Table %s', [FTableList.Strings[iIndex]]);
        ExecuteSql(sDeleteTableSql);
      end;
      Inc(iIndex);
    end;
  finally
    FTableList.Free;
  end;
end;

constructor TrpgGatherData.Create;
begin
  FGatherFile := GetTempFileName;
  CopyFileOrFolder(GetEmptyDataBaseFileName, FGatherFile);

  FConnection := TADOConnection.Create(nil);
  FConnection.LoginPrompt := False;
  FConnection.ConnectionString := Format(SAdoConnectStr, [FGatherFile]);
  FConnection.Open;

  FQuery := TADOQuery.Create(nil);
  FQuery.Connection := FConnection;
end;

procedure TrpgGatherData.CreateDataTables(AProjCount, ASProjCount: Integer);
var
  Updater: TScUpdater;
begin
  Updater := TScUpdater.Create;
  try
    Updater.ForceUpdate := True;
    Updater.Open('', FConnection, '', '');
    AddTables(AProjCount, ASProjCount, Updater);
    Updater.ExcuteUpdate;
  finally
    Updater.Free;
  end;
end;

destructor TrpgGatherData.Destroy;
begin
  FQuery.Free;
  FConnection.Free;
  if FileExists(FGatherFile) then
    DeleteFile(FGatherFile);
  inherited;
end;

procedure TrpgGatherData.ExecuteSql(const ASql: string);
var
  vQuery: TADOQuery;
begin
  vQuery := TADOQuery.Create(nil);
  try
    vQuery.Connection := FConnection;
    vQuery.SQL.Add(ASql);
    vQuery.ExecSQL;
  finally
    vQuery.Free;
  end;

  {FQuery.SQL.Clear;
  FQuery.SQL.Add(ASql);
  FQuery.ExecSQL;}
end;

function TrpgGatherData.GetCurSpecialProjCount: Integer;
var
  sgsTables: TStringList;
  iTable: Integer;
begin
  Result := 0;
  sgsTables := TStringList.Create;
  try
    FConnection.GetTableNames(sgsTables);
    for iTable := 0 to sgsTables.Count - 1 do
    begin
      if Pos(SBills_SProj, sgsTables.Strings[iTable]) > 0 then
        Inc(Result);
    end;
  finally
    sgsTables.Free;
  end;
end;

procedure TrpgGatherData.LoadRelaData(AProjectID: Integer);
const
  sCopyProperty = 'Select * Into r_ProjProperties'+
                  '  From ProjProperties In ''%s''';
var
  sFileName: string;
  vProjectData: TProjectData;
begin
  vProjectData := OpenProjectManager.FindProjectData(AProjectID);
  if Assigned(vProjectData) then Exit;

  try
    sFileName := GetTempFileName;
    vProjectData.SaveTempDataBaseFile(sFileName);

    ExecuteSql(Format(sCopyProperty, [sFileName]));
  finally
    if FileExists(sFileName) then
      DeleteFile(sFileName);
  end;
end;

procedure TrpgGatherData.SaveBills(ATree: TProjGatherTree);
var
  vBillsData: TrpgBillsData;
begin
  vBillsData := TrpgBillsData.Create(FConnection);
  try
    vBillsData.SaveDataTo(ATree, SBills);
  finally
    vBillsData.Free;
  end;
end;

procedure TrpgGatherData.SaveBillsGatherCalc(ATree: TProjGatherTree);
var
  vBillsCalcData: TrpgBillsCalcData;
begin
  vBillsCalcData := TrpgBillsCalcData.Create(FConnection);
  try
    vBillsCalcData.SaveGatherDataTo(ATree, SBills_Gather);
  finally
    vBillsCalcData.Free;
  end;
end;

procedure TrpgGatherData.SaveBillsProjCalc(ATree: TProjGatherTree;
  AProjIndex: Integer);
var
  vBillsCalcData: TrpgBillsCalcData;
begin
  vBillsCalcData := TrpgBillsCalcData.Create(FConnection);
  try
    vBillsCalcData.SaveProjDataTo(ATree, AProjIndex, SBills_Proj+IntToStr(AProjIndex+1));
  finally
    vBillsCalcData.Free;
  end;
end;

procedure TrpgGatherData.SaveBillsSpecialProjCalc(ATree: TProjGatherTree;
  AProjType: Integer);
var
  vBillsCalcData: TrpgBillsCalcData;
begin
  vBillsCalcData := TrpgBillsCalcData.Create(FConnection);
  try
    vBillsCalcData.SaveSpecialProjDataTo(ATree, AProjType, SBills_SProj+IntToStr(AProjType));
  finally
    vBillsCalcData.Free;
  end;
end;

procedure TrpgGatherData.SaveGatherData(AGather: TProjGather);
const
  sInsert = 'Insert Into %s Select * From %s';
var
  iProj: Integer;
begin
  SaveGatherProjInfo(AGather.CommonProj, AGather.SpecialProj);
  SaveBills(AGather.Tree);
  SaveBillsGatherCalc(AGather.Tree);
  for iProj := 0 to AGather.CommonProj.Count - 1 do
  begin
    SaveBillsProjCalc(AGather.Tree, iProj);
    ExecuteSql(Format(sInsert, [SBills_TransProj, SBills_Proj+IntToStr(iProj+1)]));
  end;
  for iProj := 0 to AGather.Tree.SpecialProjCount - 1 do
    SaveBillsSpecialProjCalc(AGather.Tree, iProj+1);
end;

procedure TrpgGatherData.SaveGatherProjInfo(AProjs, ASProjs: TList);
var
  vGatherInfoData: TrpgGatherProjData;
begin
  vGatherInfoData := TrpgGatherProjData.Create(FConnection);
  try
    vGatherInfoData.SaveDataTo(AProjs, ASProjs, SGatherProj);
  finally
    vGatherInfoData.Free;
  end;
end;

procedure TrpgGatherData.TransposeProjCalc(AProjCount: Integer);
const
  sInsert = 'Insert Into %s Select * From %s';
var
  iProj: Integer;
begin
  for iProj := 0 to AProjCount - 1 do
    ExecuteSql(Format(sInsert, [SBills_TransProj, SBills_Proj+IntToStr(iProj+1)]));
end;

procedure TrpgGatherData.UpdateDataBase(ASpecialProjTypes: TStrings);
const
  sUpdateSql = 'Insert Into %s (ID, ProjID, ProjType,' +
               '    OrgQuantity, OrgTotalPrice, OrgTotalPrice_Rc,' +
               '    MisQuantity, MisTotalPrice, MisTotalPrice_Rc,' +
               '    OthQuantity, OthTotalPrice, OthTotalPrice_Rc,' +
               '    Quantity, TotalPrice, TotalPrice_Rc,' +
               '    DgnQuantity1, DgnQuantity2, DgnQuantity,' +
               '    DgnPrice1, DgnPrice2, DgnPrice,' +
               '    DgnPrice1_Rc, DgnPrice2_Rc, DgnPrice_Rc,' +
               '    DealDgnQuantity1, DealDgnQuantity2, DealDgnQuantity,' +
               '    CDgnQuantity1, CDgnQuantity2, CDgnQuantity,' +
               '    FinalDgnQuantity1, FinalDgnQuantity2, FinalDgnQuantity,' +
               '    FinalDgnPrice1, FinalDgnPrice2, FinalDgnPrice,' +
               '    FinalDgnPrice1_Rc, FinalDgnPrice2_Rc, FinalDgnPrice_Rc,' +
               '    AddDealQuantity, AddDealTotalPrice, AddDealTotalPrice_Rc,' +
               '    AddQcQuantity, AddQcTotalPrice, AddQcTotalPrice_Rc,' +
               '    AddGatherQuantity, AddGatherTotalPrice, AddGatherTotalPrice_Rc,' +
               '    CurDealQuantity, CurDealTotalPrice, CurDealTotalPrice_Rc,' +
               '    CurQcQuantity, CurQcTotalPrice, CurQcTotalPrice_Rc,' +
               '    CurGatherQuantity, CurGatherTotalPrice, CurGatherTotalPrice_Rc,' +
               '    PreDealQuantity, PreDealTotalPrice, PreDealTotalPrice_Rc,' +
               '    PreQcQuantity, PreQcTotalPrice, PreQcTotalPrice_Rc,' +
               '    PreGatherQuantity, PreGatherTotalPrice, PreGatherTotalPrice_Rc,' + 
               '    EndDealQuantity, EndDealTotalPrice, EndDealTotalPrice_Rc,' +
               '    EndQcQuantity, EndQcTotalPrice, EndQcTotalPrice_Rc,' +
               '    EndGatherQuantity, EndGatherTotalPrice, EndGatherTotalPrice_Rc)' +
               '  Select ID, %d, %d,'+
               '    0, 0, 0,'+ // Org
               '    0, 0, 0,'+ // Mis
               '    0, 0, 0,'+ // Oth
               '    0, 0, 0,'+ // 台账
               '    0, 0, '''','+ // DgnQuantity
               '    0, 0, '''','+ // DgnPrice
               '    0, 0, '''','+ // DgnPrice_Rc
               '    0, 0, '''','+ // DealDgnQuantity
               '    0, 0, '''','+ // CDgnQuantity
               '    0, 0, '''','+ // FinalDgnQuantity
               '    0, 0, '''','+ // FinalDgnPrice
               '    0, 0, '''','+ // FinalDgnPrice_Rc
               '    0, 0, 0,'+ // AddDeal
               '    0, 0, 0,'+ // AddQc
               '    0, 0, 0,'+ // AddGather
               '    0, 0, 0,'+ // CurDeal
               '    0, 0, 0,'+ // CurQc
               '    0, 0, 0,'+ // CurGather
               '    0, 0, 0,'+ // PreDeal
               '    0, 0, 0,'+ // PreQc
               '    0, 0, 0,'+ // PreGather
               '    0, 0, 0,'+ // EndDeal
               '    0, 0, 0,'+ // EndQc
               '    0, 0, 0'+ // EndGather
               '  From r_Bills';
var
  Updater: TScUpdater;
  iProj, iSpecialProjCount: Integer;
begin
  iSpecialProjCount := GetCurSpecialProjCount;
  if ASpecialProjTypes.Count > iSpecialProjCount then
  begin
    Updater := TScUpdater.Create;
    try
      Updater.ForceUpdate := True;
      Updater.Open('', FConnection, '', '');
      for iProj := iSpecialProjCount to ASpecialProjTypes.Count - 1 do
        Updater.AddTableDef(SBills_SProj+IntToStr(iProj+1), @tdBills_Calc, Length(tdBills_Calc), False, False);
      Updater.ExcuteUpdate;
    finally
      Updater.Free;
    end;

    for iProj := iSpecialProjCount to ASpecialProjTypes.Count - 1 do
      ExecuteSql(Format(sUpdateSql, [SBills_SProj+IntToStr(iProj+1), -3, iProj+1]));
  end;
end;

procedure TrpgGatherData.WriteGatherData(AGather: TProjGather);
begin
  ClearHistoryData;
  CreateDataTables(AGather.Tree.ProjCount, AGather.Tree.SpecialProjCount);
  SaveGatherData(AGather);
  CalcOtherData(AGather.Tree.ProjCount, AGather.Tree.SpecialProjCount);
  // 集中处理TranProj时，最后一个标段数据丢失
  //TransposeProjCalc(AGather.Tree.ProjCount);
  if _IsDebugView then
    CopyFileOrFolder(FGatherFile, GetAppFilePath+'CommonProjGather.dat');
end;

end.