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; 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: TsdDataRecord; begin Result := False; Rec := sddStageCompare.FindKey('idxID', AID); if not Assigned(Rec) then Exit; Result := (Rec.ValueByName('TotalPrice0').AsFloat <> 0) or (Rec.ValueByName('TotalPrice1').AsFloat <> 0) or (Rec.ValueByName('TotalPrice2').AsFloat <> 0) or (Rec.ValueByName('TotalPrice3').AsFloat <> 0) or (Rec.ValueByName('TotalPrice4').AsFloat <> 0) or (Rec.ValueByName('TotalPrice5').AsFloat <> 0) or (Rec.ValueByName('TotalPrice6').AsFloat <> 0) or (Rec.ValueByName('TotalPrice7').AsFloat <> 0) or (Rec.ValueByName('TotalPrice8').AsFloat <> 0) or (Rec.ValueByName('TotalPrice9').AsFloat <> 0) or (Rec.ValueByName('TotalPrice10').AsFloat <> 0) or (Rec.ValueByName('TotalPrice11').AsFloat <> 0) or (Rec.ValueByName('TotalPrice12').AsFloat <> 0) or (Rec.ValueByName('TotalPrice13').AsFloat <> 0) or (Rec.ValueByName('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; end; procedure TStageCompareData.UpdateHistoryStageData(AIndex: Integer; const ATableName: string); var sSql: string; begin sSql := Format(UpdateSql, [ATableName, AIndex, AIndex]); ExecuteSql(sSql); end; end.