Measure/DataModules/StageCompareDm.pas

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

interface

uses
  SysUtils, Classes, sdDB, sdProvider, ADODB, mDataRecord;

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';

  DeleteCacheSql = 'Delete From StageCompare Where '+
                   '  (IsNull(Quantity0) or Quantity0 = 0) and (IsNull(TotalPrice0) or TotalPrice0 = 0)'+
                   '   and (IsNull(Quantity1) or Quantity1 = 0) and (IsNull(TotalPrice1) or TotalPrice1 = 0)'+
                   '   and (IsNull(Quantity2) or Quantity2 = 0) and (IsNull(TotalPrice2) or TotalPrice2 = 0)'+
                   '   and (IsNull(Quantity3) or Quantity3 = 0) and (IsNull(TotalPrice3) or TotalPrice3 = 0)'+
                   '   and (IsNull(Quantity4) or Quantity4 = 0) and (IsNull(TotalPrice4) or TotalPrice4 = 0)'+
                   '   and (IsNull(Quantity5) or Quantity5 = 0) and (IsNull(TotalPrice5) or TotalPrice5 = 0)'+
                   '   and (IsNull(Quantity6) or Quantity6 = 0) and (IsNull(TotalPrice6) or TotalPrice6 = 0)'+
                   '   and (IsNull(Quantity7) or Quantity7 = 0) and (IsNull(TotalPrice7) or TotalPrice7 = 0)'+
                   '   and (IsNull(Quantity8) or Quantity8 = 0) and (IsNull(TotalPrice8) or TotalPrice8 = 0)'+
                   '   and (IsNull(Quantity9) or Quantity9 = 0) and (IsNull(TotalPrice9) or TotalPrice9 = 0)'+
                   '   and (IsNull(Quantity10) or Quantity10 = 0) and (IsNull(TotalPrice10) or TotalPrice10 = 0)'+
                   '   and (IsNull(Quantity11) or Quantity11 = 0) and (IsNull(TotalPrice11) or TotalPrice11 = 0)'+
                   '   and (IsNull(Quantity12) or Quantity12 = 0) and (IsNull(TotalPrice12) or TotalPrice12 = 0)'+
                   '   and (IsNull(Quantity13) or Quantity13 = 0) and (IsNull(TotalPrice13) or TotalPrice13 = 0)'+
                   '   and (IsNull(Quantity14) or Quantity14 = 0) and (IsNull(TotalPrice14) or TotalPrice14 = 0)';

type
  TStageCompareData = class(TDataModule)
    sdpStageCompare: TsdADOProvider;
    sddStageCompare: TsdDataSet;
    procedure sddStageCompareGetRecordClass(
      var ARecordClass: TsdRecordClass);
  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;

    function HasPhaseData(AID: Integer): Boolean;
  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;

function TStageCompareData.HasPhaseData(AID: Integer): Boolean;
var
  Rec: TStageCompareRecord;
begin
  Result := False;
  Rec := TStageCompareRecord(sddStageCompare.FindKey('idxID', AID));
  if not Assigned(Rec) then Exit;

  Result := (Rec.TotalPrice0.AsFloat <> 0)
         or (Rec.TotalPrice1.AsFloat <> 0)
         or (Rec.TotalPrice2.AsFloat <> 0)
         or (Rec.TotalPrice3.AsFloat <> 0)
         or (Rec.TotalPrice4.AsFloat <> 0)
         or (Rec.TotalPrice5.AsFloat <> 0)
         or (Rec.TotalPrice6.AsFloat <> 0)
         or (Rec.TotalPrice7.AsFloat <> 0)
         or (Rec.TotalPrice8.AsFloat <> 0)
         or (Rec.TotalPrice9.AsFloat <> 0)
         or (Rec.TotalPrice10.AsFloat <> 0)
         or (Rec.TotalPrice11.AsFloat <> 0)
         or (Rec.TotalPrice12.AsFloat <> 0)
         or (Rec.TotalPrice13.AsFloat <> 0)
         or (Rec.TotalPrice14.AsFloat <> 0);
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;
  if not Assigned(sddStageCompare.FindIndex('idxID')) then
    sddStageCompare.AddIndex('idxID', 'BillsID');
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;
  ExecuteSql(DeleteCacheSql);
end;

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

procedure TStageCompareData.sddStageCompareGetRecordClass(
  var ARecordClass: TsdRecordClass);
begin
  ARecordClass := TStageCompareRecord;
end;

end.