Measure/DataModules/StageCompareDm.pas

unit StageCompareDm;
{--------------------------------------
为减少内存占用，项目（当期数据）打开时，该单元不打开
仅在界面切换至审核比较数据时，更新加载该单元数据，当界面再次切换，应关闭该单元。
缺陷：每次用户点击切换界面至审核比较时，将消耗较多时间
--------------------------------------}

interface

uses
  SysUtils, Classes, sdDB, sdProvider, ADODB;

const
  TableDeleteSql = 'Drop Table StageCompare';

  TableCreateSql = 'Select BillsID, GatherQuantity As Quantity0, GatherTotalPrice As TotalPrice0,'+
                   '    GatherQuantity As Quantity1, GatherTotalPrice As TotalPrice1,'+
                   '    GatherQuantity As Quantity2, GatherTotalPrice As TotalPrice2,'+
                   '    GatherQuantity As Quantity3, GatherTotalPrice As TotalPrice3,'+
                   '    GatherQuantity As Quantity4, GatherTotalPrice As TotalPrice4,'+
                   '    GatherQuantity As Quantity5, GatherTotalPrice As TotalPrice5,'+
                   '    GatherQuantity As Quantity6, GatherTotalPrice As TotalPrice6,'+
                   '    GatherQuantity As Quantity7, GatherTotalPrice As TotalPrice7,'+
                   '    GatherQuantity As Quantity8, GatherTotalPrice As TotalPrice8,'+
                   '    GatherQuantity As Quantity9, GatherTotalPrice As TotalPrice9,'+
                   '    GatherQuantity As Quantity10, GatherTotalPrice As TotalPrice10,'+
                   '    GatherQuantity As Quantity11, GatherTotalPrice As TotalPrice11,'+
                   '    GatherQuantity As Quantity12, GatherTotalPrice As TotalPrice12,'+
                   '    GatherQuantity As Quantity13, GatherTotalPrice As TotalPrice13,'+
                   '    GatherQuantity As Quantity14, GatherTotalPrice As TotalPrice14'+
                   '  Into StageCompare From Refer Where 1=2';

  KeyCreateSql = 'Alter Table StageCompare Add Constraint PrimaryKey Primary Key (BillsID)';

  InsertSql = 'Insert Into StageCompare (BillsID, Quantity%d, TotalPrice%d)'+
              '    Select BillsID, GatherQuantity, GatherTotalPrice'+
              '  From %s';

  UpdateSql = 'Update StageCompare As S, %s As A'+
              '    Set S.Quantity%d = A.GatherQuantity, S.TotalPrice%d = A.GatherTotalPrice'+
              '  Where S.BillsID = A.BillsID';

type
  TStageCompareData = class(TDataModule)
    sdpStageCompare: TsdADOProvider;
    sddStageCompare: TsdDataSet;
  private
    FPhaseData: TObject;

    function CheckCompareExist: Boolean;

    procedure ExecuteSql(const ASql: string);

    procedure ReloadAllCompareData;
    procedure InsertLastesyStageData(AIndex: Integer; const ATableName: string);
    procedure UpdateHistoryStageData(AIndex: Integer; const ATableName: string);
  public
    constructor Create(APhaseData: TObject);
    destructor Destroy; override;

    procedure Open;
    procedure Close;
  end;

implementation

uses
  PhaseData;

{$R *.dfm}

{ TStageCompareData }

function TStageCompareData.CheckCompareExist: Boolean;
var
  Tables: TStrings;
begin
  Tables := TStringList.Create;
  try
    TPhaseData(FPhaseData).ADOConnection.GetTableNames(Tables);
    Result := Tables.IndexOf('StageCompare') > -1;
  finally
    Tables.Free;
  end;
end;

procedure TStageCompareData.Close;
begin
  sddStageCompare.Close;
end;

constructor TStageCompareData.Create(APhaseData: TObject);
begin
  inherited Create(nil);
  FPhaseData := APhaseData;
  sdpStageCompare.Connection :=  TPhaseData(FPhaseData).ADOConnection;
end;

destructor TStageCompareData.Destroy;
begin
  inherited;
end;

procedure TStageCompareData.ExecuteSql(const ASql: string);
var
  FQuery: TADOQuery;
begin
  FQuery := TADOQuery.Create(nil);
  try
    FQuery.Connection := TPhaseData(FPhaseData).ADOConnection;
    FQuery.SQL.Clear;
    FQuery.SQL.Add(ASql);
    FQuery.ExecSQL;
  finally
    FQuery.Free;
  end;
end;

procedure TStageCompareData.InsertLastesyStageData(AIndex: Integer;
  const ATableName: string);
var
  sSql: string;
begin
  sSql := Format(InsertSql, [AIndex, AIndex, ATableName]);
  ExecuteSql(sSql);
end;

procedure TStageCompareData.Open;
begin
  ReloadAllCompareData;
  sddStageCompare.Open;
end;

procedure TStageCompareData.ReloadAllCompareData;
var
  iStage: Integer;
begin
  if CheckCompareExist then
    ExecuteSql(TableDeleteSql);
  ExecuteSql(TableCreateSql);
  ExecuteSql(KeyCreateSql);
  with TPhaseData(FPhaseData) do
  begin
    InsertLastesyStageData(AuditCount, StageTableName[AuditCount]);
    if AuditCount > 0 then
      for iStage := 0 to AuditCount - 1 do
        UpdateHistoryStageData(iStage, StageTableName[iStage]);
  end;
end;

procedure TStageCompareData.UpdateHistoryStageData(AIndex: Integer;
  const ATableName: string);
var
  sSql: string;
begin
  sSql := Format(UpdateSql, [ATableName, AIndex, AIndex]);
  ExecuteSql(sSql);
end;

end.