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: Integer);

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

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

    procedure TransposeProjCalc(AProjCount: Integer);
  protected
    procedure AddTables(AProjCount: Integer; AUpdater: TScUpdater); virtual;
  public
    constructor Create;
    destructor Destroy; override;

    procedure WriteGatherData(AGather: TProjGather);

    procedure LoadRelaData(AProjectID: Integer);

    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: 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);
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: Integer);
var
  iProj: Integer;
begin
  CalcDgnData(SBills_Gather);
  for iProj := 0 to AProjCount - 1 do
    CalcDgnData(SBills_Proj+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: Integer);
var
  Updater: TScUpdater;
begin
  Updater := TScUpdater.Create;
  try
    Updater.ForceUpdate := True;
    Updater.Open('', FConnection, '', '');
    AddTables(AProjCount, 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);
begin
  FQuery.SQL.Clear;
  FQuery.SQL.Add(ASql);
  FQuery.ExecSQL;
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.SaveGatherData(AGather: TProjGather);
var
  iProj: Integer;
begin
  SaveGatherProjInfo(AGather.Projs);
  SaveBills(AGather.Tree);
  SaveBillsGatherCalc(AGather.Tree);
  for iProj := 0 to AGather.Projs.Count - 1 do
    SaveBillsProjCalc(AGather.Tree, iProj);
end;

procedure TrpgGatherData.SaveGatherProjInfo(AProjs: TList);
var
  vGatherInfoData: TrpgGatherProjData;
begin
  vGatherInfoData := TrpgGatherProjData.Create(FConnection);
  try
    vGatherInfoData.SaveDataTo(AProjs, 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.WriteGatherData(AGather: TProjGather);
begin
  ClearHistoryData;
  CreateDataTables(AGather.Projs.Count);
  SaveGatherData(AGather);
  CalcOtherData(AGather.Projs.Count);
  TransposeProjCalc(AGather.Projs.Count);
  if _IsDebugView then
    CopyFileOrFolder(FGatherFile, GetAppFilePath+'CommonProjGather.dat');
end;

end.