Measure/Units/ProjectData.pas

unit ProjectData;

interface

uses
  Connections, BillsDm, PhaseData, UpdateDataBase, ZhAPI, ProjectProperty,
  PhaseCompareDm, DealPaymentDm, SearchDm, DealBillsDm, MainDataListDm,
  BillsGatherDm, BGLDm, StaffDm, BillsCompileDm, BillsMeasureDm,
  BillsBookmarkDm, UpFileManageUnit,
  Classes, SysUtils, ADODB, sdDB, Checker;

type
  
  TProjectData = class
  private
    // 临时文件夹，解压项目后存放文件的文件夹
    FTempFolder: string;
    // 项目管理界面看到的项目名称
    FProjectName: string;
    FFileName: string;
    FProjectID: Integer;
    FConnection: TEncryptConnection;
    FUpdator: TUpdateProjectDB;
    FIsNewFile: Boolean;

    FBillsData: TBillsData;
    FBillsCompileData: TBillsCompileData;
    FBillsMeasureData: TBillsMeasureData;
    FBillsBookmarkData: TBillsBookmarkData;
    FDealBillsData: TDealBillsData;
    FProjProperties: TProjProperties;

    FPhaseIndex: Integer;
    FPhaseData: TPhaseData;
    FPhaseCompareData: TPhaseCompareData;
    FBillsGatherData: TBillsGatherData;
    FDealPaymentData: TDealPaymentData;
    FBGLData: TBGLData;
    FStaffData: TStaffData;
    FSearchData: TSearchData;
    FMainListData: TMainListData;

    FCanUnlockInfo: Boolean;

    FWebID: Integer;
    FWebOwnerID: Integer;
    FWebAuthorID: Integer;
    FAuthorHasSubmited: Boolean;
    FProjectReadOnly: Boolean;
    FAttachmentData: TUpFiles;
    FCheckers: TCheckers;

    {For Open}
    procedure UpdateProjectDataBase;
    procedure CheckNewFile(AConnection: TADOConnection);
    procedure OpenLastPhaseData;
    procedure UpdateOldData;

    procedure SaveInfoToManager;

    procedure ExecuteSql(const ASql: string);
    // 备份0号台账的历史数据
    procedure CopyLedgerHistoryData(AAudit: Integer);
    // 备份各期的截止本期累计完成数据
    procedure CopyPhaseCompleteData;

    procedure SetPhaseIndexSimple(AIndex: Integer);

    // 锁定数据 -- Sql语句方式(使用时注意相应的DataMoudle应处于关闭状态)
    procedure LockBillsBaseData;
    procedure LockDealPaymentData;
    procedure LockBGLData;

    procedure UpdatePhaseData;

    procedure CloseAllData;
    procedure OpenAllData;

    function GetMainFileName: string;
    procedure SetPhaseIndex(const Value: Integer);
    function GetTempPath: string;
    function GetProjectFileName: string;
    function GetAllowInsert: Boolean;
    function GetBaseDataReadOnly: Boolean;
    function GetADOConnection: TADOConnection;
    procedure SetCheckers(const Value: TCheckers);
    function GetStageDataReadOnly: Boolean;
  public
    constructor Create;
    destructor Destroy; override;

    procedure Open(AProjRec: TsdDataRecord);
    procedure Save;
    //----------------------- Begin ---后台打开 ------------------------
    {总说明：
       为节省内存使用，对不同的后台打开分别写不同的方法
       按需使用，稍有不同，则新增后台打开方法
       变更(新增)方法须同步变更(新增)注释
    }
    {OpenForSubmit: ProjProperties, StaffData}
    procedure OpenForSubmit(const AFileName: string);
    procedure SaveAsForSubmit(const AFileName: string);
    {OpenForReply: ProjProperties, StaffData, PhaseData}
    procedure OpenForReply(const AFileName: string);
    procedure SaveForReply(const AFileName: string);
    {OpenForReceive: ProjProperties, StaffData, PhaseData, StageData}
    procedure OpenForReceive(const AFileName: string);
    procedure SaveForReceive(const AFileName: string);
    {SimpleOpen: 仅打开数据库[项目+最后一期]，不打开除属性以外的任何数据表（项目属性都是通过Sql语句修改）}
    procedure SimpleOpen(const AFileName: string);
    procedure SimpleSaveAs(const AFileName: string);
    {OpenForReport: BillsData, BillsCompileData, DealPaymentData, BGLData 报表仅读取数据不做任何修改}
    procedure OpenForReport(const AFileName: string);
    {OpenForReport2: OpenForReport的基础上打开最后一期数据}
    procedure OpenForReport2(const AFileName: string);
    {OpenForReport3: OpenForReport2的基础上打开最后一个审核人数据，打开BillsMeasureTree不打开BillsCompileTree，链接BillsMeasureTree跟StageData}
    procedure OpenForReport3(const AFileName: string);
    //-----------------------  End ---后台打开 ------------------------

    procedure SaveLastestPhaseMainData;

    procedure CreateNewPhase;

    {重设所有LookUpDataset连接}
    procedure ResetPhaseDataLink;
    {重设所有DataView小数位数显示}
    procedure ResetFloatDigitView;

    {锁定数据}
    // 上报
    procedure LockedDataForSubmit;
    // 终审批复
    procedure LockedDataForReply;
    {创建审核数据}
    procedure UpdateDataForReceive;

    // 上报文件
    procedure SubmitProject(const AFileName: string = '');
    // 批复文件
    procedure ReplyProject(const AFileName: string = '');
    // json文件：清单
    procedure ExportJson_Bills(const AFileName: string);
    procedure ExportJson_Common(const AFileName: string);

    // 断开所有树的链接
    procedure DisConnectTree;
    // 重新连接所有的树
    procedure ReConnectTree;

    {For Reports: 复制当期的全部数据到项目数据中}
    procedure CopyPhaseData;

    procedure CalculateAll;

    procedure ImportCloudTenderFile(const AFileName: string);

    function CheckPassword: Boolean;

    function CurUserIsOwner: Boolean;
    function CurUserIsAuthor: Boolean;
    function CurUserIsChecker: Boolean;

    property BillsData: TBillsData read FBillsData;
    property BillsCompileData: TBillsCompileData read FBillsCompileData;
    property BillsMeasureData: TBillsMeasureData read FBillsMeasureData;
    property BillsBookmarkData: TBillsBookmarkData read FBillsBookmarkData;
    property DealBillsData: TDealBillsData read FDealBillsData;
    property PhaseIndex: Integer read FPhaseIndex write SetPhaseIndex;
    property PhaseData: TPhaseData read FPhaseData;
    property ProjProperties: TProjProperties read FProjProperties;
    property PhaseCompareData: TPhaseCompareData read FPhaseCompareData;
    property BillsGatherData: TBillsGatherData read FBillsGatherData;
    property DealPaymentData: TDealPaymentData read FDealPaymentData;
    property BGLData: TBGLData read FBGLData;
    property StaffData: TStaffData read FStaffData;
    property SearchData: TSearchData read FSearchData;
    property MainListData: TMainListData read FMainListData;
    property AttachmentData: TUpFiles read FAttachmentData;   // 附件

    // 台账、合同支付
    property AllowInsert: Boolean read GetAllowInsert;
    property BaseDataReadOnly: Boolean read GetBaseDataReadOnly;
    property StageDataReadOnly: Boolean read GetStageDataReadOnly;

    property CanUnlockInfo: Boolean read FCanUnlockInfo write FCanUnlockInfo;

    property ProjectName: string read FProjectName;
    // 磁盘上存储该项目的文件名称
    property FileName: string read FFileName;
    property ProjectID: Integer read FProjectID;
    property MainFileName: string read GetMainFileName;
    property TempPath: string read GetTempPath;
    property ADOConnection: TADOConnection read GetADOConnection;

    property WebID: Integer read FWebID;
    property WebOwnerID: Integer read FWebOwnerID;
    property WebAuthorID: Integer read FWebAuthorID;
    property AuthorHasSubmited: Boolean read FAuthorHasSubmited write FAuthorHasSubmited;

    property ProjectReadOnly: Boolean read FProjectReadOnly;
    property Checkers: TCheckers read FCheckers write SetCheckers;
  end;

implementation

uses UtilMethods, Globals, ProjectCommands, sdIDTree, StageDm,
  ZJJLDm, PHPWebDm, XMLDoc, XMLIntf, ConstUnit, PasswordInputFrm;

{ TProjectData }

procedure TProjectData.CheckNewFile(AConnection: TADOConnection);
var
  FTableList: TStringList;
begin
  FTableList := TStringList.Create;
  try
    AConnection.GetTableNames(FTableList);
    FIsNewFile := FTableList.Count = 0;
  finally
    FTableList.Free;
  end;
end;

procedure TProjectData.CopyLedgerHistoryData(AAudit: Integer);
const
  sSql = 'Select ID as BillsID, Quantity, TotalPrice, QtyFlag, QtyFormula ' +
         'Into LedgerHistory%d '+
         'From Bills';
begin
  ExecuteSql(Format(sSql, [AAudit]));
end;

constructor TProjectData.Create;
begin
  FCheckers := TCheckers.Create;
  FTempFolder := GenerateTempFolder(GetTempFilePath);
  FConnection := TEncryptConnection.Create;
  FUpdator := TUpdateProjectDB.Create;
  FProjProperties := TProjProperties.Create(Self);
  FBillsData := TBillsData.Create(Self);
  FBillsCompileData := TBillsCompileData.Create(Self);
  FBillsMeasureData := TBillsMeasureData.Create(Self);
  FBillsBookmarkData := TBillsBookmarkData.Create(Self);
  FDealBillsData := TDealBillsData.Create(Self);
  FPhaseData := TPhaseData.Create(Self);
  FPhaseCompareData := TPhaseCompareData.Create(Self);
  FBillsGatherData := TBillsGatherData.Create(Self);
  FDealPaymentData := TDealPaymentData.Create(Self);
  FBGLData := TBGLData.Create(Self);
  FStaffData := TStaffData.Create(Self);
  FSearchData := TSearchData.Create(Self);
  FMainListData := TMainListData.Create(Self);
  FAttachmentData := TUpFiles.Create;
  FPhaseIndex := 0;
end;

procedure TProjectData.CreateNewPhase;
var
  sPhaseFileName: string;
begin
  FBillsData.LockedBaseData;
  FProjProperties.PhaseCount := FProjProperties.PhaseCount + 1;
  FProjProperties.AuditStatus := 0;
  FStaffData.UpdateDataForNewPhase;
  sPhaseFileName := Format('%s\Phase%d.dat', [TempPath, FProjProperties.PhaseCount]);
  CopyFileOrFolder(GetEmptyDataBaseFileName, sPhaseFileName);
end;

destructor TProjectData.Destroy;
begin
  FCheckers.Free;
  FMainListData.Free;
  FSearchData.Free;
  FStaffData.Free;
  FBGLData.Free;
  FDealPaymentData.Free;
  FPhaseCompareData.Free;
  FPhaseData.Free;
  FProjProperties.Free;
  FDealBillsData.Free;
  FBillsBookmarkData.Free;
  FBillsMeasureData.Free;
  FBillsCompileData.Free;
  FBillsData.Free;
  FAttachmentData.Free;
  FUpdator.Free;
  FConnection.Free;
  DeleteFileOrFolder(FTempFolder);
  inherited;
end;

function TProjectData.GetBaseDataReadOnly: Boolean;
begin
  if FProjProperties.PhaseCount = 0 then
    Result := False
  else
    Result := not AllowInsert;
end;

function TProjectData.GetAllowInsert: Boolean;
begin
  if FPhaseData.Active then
    Result := FPhaseData.AllowInsert
  else
    Result := True;
end;

function TProjectData.GetMainFileName: string;
begin
  Result := TempPath + 'Main.dat';
end;

function TProjectData.GetProjectFileName: string;
begin
  Result := GetMyProjectsFilePath + FProjectName;
end;

function TProjectData.GetTempPath: string;
begin
  Result := FTempFolder + '\';
end;

procedure TProjectData.LockedDataForReply;
begin
  LockBillsBaseData;
  LockDealPaymentData;
  FPhaseData.PhaseProperty.FinalAudit := True;
  // 有顺序限制，谨慎修改
  FProjProperties.AuditCompany := FStaffData.FinalStaffCompany;
  FProjProperties.AuditStatus := -1;
  FProjProperties.FinalAuditCount := FProjProperties.PhaseCount;
  FStaffData.LockedDataForReply;
  LockBGLData;
end;

procedure TProjectData.LockedDataForSubmit;
begin
  LockBillsBaseData;
  LockDealPaymentData;
  FProjProperties.AuditCompany := FStaffData.FinalStaffCompany;
  FStaffData.LockedDataForAudit;
  LockBGLData;
end;

procedure TProjectData.Open(AProjRec: TsdDataRecord);
begin
  {Open规则：在打开Phase或者设置PhaseIndex时，会设置多个LookUpDataset，
   故要求须在BillsData、DealPayment打开之后}
  FProjectName := AProjRec.ValueByName('Name').AsString;
  FFileName :=  GetMyProjectsFilePath + AProjRec.ValueByName('FileName').AsString;
  FProjectID := AProjRec.ValueByName('ID').AsInteger;
  FWebID := AProjRec.ValueByName('WebID').AsInteger;
  FWebOwnerID := AProjRec.ValueByName('WebOwnerID').AsInteger;
  FWebAuthorID := AProjRec.ValueByName('WebAuthorID').AsInteger;

  FAttachmentData.ProjectData := Self;
  FAttachmentData.LoadDatas;

  UpdateSysProgress(5, '正在打开项目');
  UnZipFile(FileName, TempPath);
  UpdateSysProgress(10, '正在打开项目');
  FConnection.Open(MainFileName);
  CheckNewFile(FConnection.Connection);
  UpdateSysProgress(15, '正在升级文件');
  UpdateProjectDataBase;
  UpdateSysProgress(20, '正在读取数据');
  FProjProperties.Open(FConnection.Connection);
  if FIsNewFile then
    FProjProperties.UpdateFlag := 1;
  UpdateOldData;
  ResetFloatDigitView;
  UpdateSysProgress(25, '正在读取数据');
  FBillsData.Open(FConnection.Connection);
  if FIsNewFile then
    FBillsData.InitBills;
  FBillsCompileData.Open;
  FBillsMeasureData.Open;
  UpdateSysProgress(65, '正在读取数据');
  FBillsBookmarkData.Open;
  UpdateSysProgress(70, '正在读取数据');
  FDealBillsData.Open(FConnection.Connection);
  UpdateSysProgress(80, '正在读取数据');
  FDealPaymentData.Open(FConnection.Connection);
  if FIsNewFile then
    FDealPaymentData.Init;
  UpdateSysProgress(90, '正在读取数据');
  OpenLastPhaseData;
  UpdateSysProgress(140, '正在读取数据');
  FBGLData.Open(FConnection.Connection);
  UpdateSysProgress(160, '正在读取数据');
  FStaffData.Open(FConnection.Connection);
  UpdateSysProgress(180, '正在读取数据');
  //FBillsGatherData.RefreshBills;
  FMainListData.Open(FConnection.Connection);
  UpdateSysProgress(200, '就绪');
end;

procedure TProjectData.OpenLastPhaseData;
begin
  if ProjProperties.PhaseCount = 0 then Exit;
  FPhaseIndex := ProjProperties.PhaseCount;
  FProjProperties.PhaseIndex := FPhaseIndex;
  FPhaseData.Open(Format('%sPhase%d.dat', [TempPath, FPhaseIndex]));
  ResetPhaseDataLink;
end;

procedure TProjectData.Save;
begin
  UpdateSysProgress(5, '正在保存数据');
  SaveLastestPhaseMainData;
  if FPhaseData.Active then
    FPhaseData.Save;
  UpdateSysProgress(20, '正在保存数据');
  FMainListData.Save;
  FDealPaymentData.Save;
  UpdateSysProgress(40, '正在保存数据');
  FStaffData.Save;
  UpdateSysProgress(50, '正在保存数据');
  FBGLData.Save;
  UpdateSysProgress(60, '正在保存数据');
  FDealBillsData.Save;
  UpdateSysProgress(80, '正在保存数据');
  FBillsCompileData.ReLockBaseData;
  UpdateSysProgress(85, '正在保存数据');
  FBillsData.Save;
  UpdateSysProgress(170, '正在保存数据');
  FProjProperties.Save;
  UpdateSysProgress(180, '正在保存数据');
  FConnection.Save;
  UpdateSysProgress(190, '正在保存数据');
  ZipFolder(FTempFolder, FileName);
  SaveInfoToManager;
  UpdateSysProgress(200, '就绪');
  ProjectManager.AddSaveTenderBackup(FProjectID);
end;

procedure TProjectData.SaveInfoToManager;
var
  InfoRec, PhaseRec: TsdDataRecord;
begin
  InfoRec := ProjectManager.sddProjectsInfo.FindKey('idxID', FProjectID);
  PhaseRec := MainListData.GetPhaseMainRecord(ProjProperties.PhaseCount);
  InfoRec.ValueByName('DealTotalPrice').AsFloat := FBillsData.Settlement[DealIndex];
  InfoRec.ValueByName('Deal_BGLTotalPrice').AsFloat := FBillsData.Settlement[DealIndex] + FBGLData.AllBGLTotalPrice;
  InfoRec.ValueByName('PhaseTotalPrice').AsFloat := PhaseRec.ValueByName('GatherTotalPrice').AsFloat;
  InfoRec.ValueByName('EndDealTotalPrice').AsFloat := PhaseRec.ValueByName('EndDealTotalPrice').AsFloat;
  InfoRec.ValueByName('EndChangeTotalPrice').AsFloat :=
      PhaseRec.ValueByName('EndQcTotalPrice').AsFloat + PhaseRec.ValueByName('EndPcTotalPrice').AsFloat;
  InfoRec.ValueByName('EndTotalPrice').AsFloat := PhaseRec.ValueByName('EndGatherTotalPrice').AsFloat;
  InfoRec.ValueByName('PreTotalPrice').AsFloat := PhaseRec.ValueByName('PreGatherTotalPrice').AsFloat;
  InfoRec.ValueByName('PhasePay').AsFloat := PhaseRec.ValueByName('PhasePay').AsFloat;
  InfoRec.ValueByName('PhaseCount').AsInteger := FProjProperties.PhaseCount;
  InfoRec.ValueByName('AuditStatus').AsInteger := FProjProperties.AuditStatus;
  ProjectManager.Save;
end;

procedure TProjectData.SetPhaseIndex(const Value: Integer);

  function GetPhaseFileName: string;
  begin
    Result := Format('%s\Phase%d.dat', [TempPath, FPhaseIndex]);
  end;

  procedure ReCreatePhaseData;
  begin
    if FPhaseData.Active then
      FPhaseData.Save;
    FPhaseData.Free;
    FPhaseData := TPhaseData.Create(Self);
  end;

begin
  SaveLastestPhaseMainData;
  FPhaseIndex := Value;
  ProjProperties.PhaseIndex := FPhaseIndex;
  ReCreatePhaseData;
  FPhaseData.Open(GetPhaseFileName);
  ResetPhaseDataLink;
  //BillsGatherData.RefreshBills;
end;

procedure TProjectData.SetPhaseIndexSimple(AIndex: Integer);
begin
  FPhaseIndex := AIndex;
  if FPhaseData.Active then
    FPhaseData.SimpleSave;
  FPhaseData.Free;
  FPhaseData := TPhaseData.Create(Self);
  FPhaseData.SimpleOpen(Format('%s\Phase%d.dat', [TempPath, FPhaseIndex]));
end;

procedure TProjectData.UpdateProjectDataBase;
begin
  FUpdator.Update(FConnection);
end;

procedure TProjectData.ResetPhaseDataLink;
begin
  BillsMeasureData.ResetPhaseStageLink;
  BillsMeasureData.ResetTreeNodeStageRec;
  DealPaymentData.ResetPhaseLink;
  BillsBookmarkData.ResetPhaseStageLink;
end;

procedure TProjectData.SimpleOpen(const AFileName: string);
begin
  FProjectID := -1;
  UnZipFile(AFileName, TempPath);
  FConnection.Open(MainFileName);
  FProjProperties.Open(FConnection.Connection);
  if ProjProperties.PhaseCount > 0 then
  begin
    FPhaseIndex := ProjProperties.PhaseCount;
    FPhaseData.SimpleOpen(Format('%sPhase%d.dat', [TempPath, FPhaseIndex]));
  end;
end;

procedure TProjectData.SimpleSaveAs(const AFileName: string);
begin
  if FPhaseData.Active then
    FPhaseData.SimpleSave;
  FProjProperties.Save;
  FConnection.Save;
  ZipFolder(FTempFolder, AFileName);
end;

procedure TProjectData.UpdateDataForReceive;
begin
  UpdatePhaseData;
end;

procedure TProjectData.UpdatePhaseData;
var
  iPhase: Integer;
begin
  FProjProperties.AuditStatus := FProjProperties.AuditStatus + 1;
  for iPhase := FProjProperties.FinalAuditCount + 1 to FProjProperties.PhaseCount do
  begin
    SetPhaseIndexSimple(iPhase);
    PhaseData.CreateNewAuditData;
    StaffData.UpdateDataForNewAudit;
  end;
end;

procedure TProjectData.ReplyProject(const AFileName: string = '');
var
  Replyor: TReplyProject;
begin
  Replyor := TReplyProject.Create(FProjectName, FFileName, FProjectID);
  try
    if AFileName = '' then
      Replyor.Execute
    else
      Replyor.ExportTo(AFileName);
  finally
    Replyor.Free;
  end;
end;

procedure TProjectData.SubmitProject(const AFileName: string = '');
var
  Submitor :TSubmitProject;
begin
  Submitor := TSubmitProject.Create(FProjectName, FFileName, FProjectID);
  try
    if AFileName = '' then
      Submitor.Execute
    else
      Submitor.ExportTo(AFileName);
  finally
    Submitor.Free;
  end;
end;

procedure TProjectData.ExportJson_Bills(const AFileName: string);

  function GetProjectName: string;
  var
    vNode: tsdIDTreeNode;
  begin
    vNode := ProjectManager.ProjectsTree.FindNode(FProjectID).Parent;
    Result := vNode.Rec.ValueByName('Name').AsString;
  end;


  // lkJSON 暂时未找到添加数组结构的办法，官方也没有提供相关的Demo，自己合成。
  // lkJSON 合成的长文本自动带有大量“\”转义字符，这点有些烦人。
  // 其实这么折腾，还不如自己直接合成。那为什么还要用它？自己写是很容易，但是
  // 要自己读JSON，那写起来就很麻烦了。
  function BillsStr: string;
  var vRec: TsdDataRecord;
    i: Integer;
    sSplit: string;

    function v(AFieldName: string): string;
    begin
      Result := vRec.ValueByName(AFieldName).AsString;
    end;

    // 杰哥那头不识别中文，将中文转成URL编码形式
    function URLEncode(const S: string; const InQueryString: Boolean): string;
    var
      Idx: Integer;
    begin
      Result := '';
      for Idx := 1 to Length(S) do
      begin
        case S[Idx] of
          'A'..'Z', 'a'..'z', '0'..'9', '-', '_', '.':
            Result := Result + S[Idx];
          ' ':
            if InQueryString then
              Result := Result + '+'
            else
              Result := Result + '%20';
        else
            Result := Result + '%' + SysUtils.IntToHex(Ord(S[Idx]), 2);
        end;
      end;
    end;

  begin
    for i := 0 to BillsMeasureData.BillsMeasureTree.Count - 1 do
    begin
      vRec := BillsMeasureData.BillsMeasureTree.Items[i].Rec;
      if i = 0 then
        sSplit := ''
      else
        sSplit := ',';
      Result := Result + sSplit + Format('{"Code":"%s","B_Code":"%s","Name":"%s",' +
        '"Units":"%s","Price":"%s","Quantity":"%s","TotalPrice":"%s"}',
        [v('Code'), v('B_Code'), v('Name'), v('Units'), v('Price'),
        v('Quantity'), v('TotalPrice')]);
    end;
  end;

var
  fPhasePay: Double;
  vSL: TStringList;
begin
  vSL := TStringList.Create;
  try
    vSL.Text := Format('{"DealTotalPrice":"%s","Bills":[%s]}',
      [FloatToStr(FBillsData.Settlement[DealIndex]), AnsiToUtf8(BillsStr)]);
    vSL.SaveToFile(AFileName);
  finally
    vSL.Free;
  end;
end;

procedure TProjectData.DisConnectTree;
begin
  BillsMeasureData.BillsMeasureTree.Active := False;
  BillsCompileData.BillsCompileTree.Active := False;
end;

procedure TProjectData.ReConnectTree;
begin
  BillsCompileData.ReConnectTree;
  BillsMeasureData.ReConnectTree;
end;

procedure TProjectData.CopyPhaseCompleteData;
const
  sInsertSql = 'Insert Into PhaseComplete (BillsID)' +
               '  Select ID' +
               '  From Bills where Bills.CreatePhaseID = %d';
  sUpdateSql = 'Update PhaseComplete Inner Join [Bills]' +
               '  On PhaseComplete.BillsID=Bills.ID' +
               '  Set PhaseComplete.Quantity%d=Bills.AddGatherQuantity,' +
               '      PhaseComplete.TotalPrice%d=Bills.AddGatherTotalPrice';
var
  iPhase: Integer;
  sSql: string;
begin
  for iPhase := FProjProperties.FinalAuditCount + 1 to FProjProperties.PhaseCount do
  begin
    SetPhaseIndexSimple(iPhase);
    // 插入本期新增的数据
    sSql := Format(sInsertSql, [iPhase]);
    ExecuteSql(sSql);
    // 更新所有截止本期完成计量数据
    sSql := Format(sInsertSql, [iPhase, iPhase]);
    ExecuteSql(sSql);
  end;
end;

procedure TProjectData.CopyPhaseData;

  procedure DeletePhaseTable;
  var
    FTableList: TStringList;
    iIndex: Integer;
    sDeleteTableSql: String;
  begin
    FTableList := TStringList.Create;
    try
      FConnection.Connection.GetTableNames(FTableList);
      iIndex := 0;
      while iIndex < FTableList.Count do
      begin
        if Pos('P_', 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;

  procedure CopyStageData(const AFileName, ASourceTable, AResultTable: string);
  const
    sCopySql = 'Select BillsID, DealQuantity, DealTotalPrice, QcQuantity, QcTotalPrice, QcBGLCode, QcBGLNum,' +
               '    PcQuantity, PcTotalPrice, PcBGLCode, PcBGLNum, GatherQuantity, GatherTotalPrice,' +
               '    EndDealQuantity, EndDealTotalPrice, EndQcQuantity, EndQcTotalPrice,' +
               '    EndPcQuantity, EndPcTotalPrice, EndGatherQuantity, EndGatherTotalPrice,' +
               '    PreDealQuantity, PreDealTotalPrice, PreQcQuantity, PreQcTotalPrice,' +
               '    PrePcQuantity, PrePcTotalPrice, PreGatherQuantity, PreGatherTotalPrice' +
               '  Into %s' +
               '  From %s' +
               '  In ''%s''';
  var
    sSql: string;
  begin
    sSql := Format(sCopySql, [AResultTable, ASourceTable, AFileName]);
    ExecuteSql(sSql);
  end;

  procedure CopyEmptyStageData(const AFileName, ASourceTable, AResultTable: string);
  const
    sCopySql = 'Select BillsID, DealQuantity, DealTotalPrice, QcQuantity, QcTotalPrice, QcBGLCode, QcBGLNum,' +
               '    PcQuantity, PcTotalPrice, PcBGLCode, PcBGLNum, GatherQuantity, GatherTotalPrice,' +
               '    EndDealQuantity, EndDealTotalPrice, EndQcQuantity, EndQcTotalPrice,' +
               '    EndPcQuantity, EndPcTotalPrice, EndGatherQuantity, EndGatherTotalPrice,' +
               '    PreDealQuantity, PreDealTotalPrice, PreQcQuantity, PreQcTotalPrice,' +
               '    PrePcQuantity, PrePcTotalPrice, PreGatherQuantity, PreGatherTotalPrice' +
               '  Into %s' +
               '  From %s' +
               '  In ''%s''' +
               '  Where 1=2';
  var
    sSql: string;
  begin
    sSql := Format(sCopySql, [AResultTable, ASourceTable, AFileName]);
    ExecuteSql(sSql);
  end;

  procedure AddSettlementData;
  const
    sAddSql = 'Insert Into P_Stage (BillsID,' +
              '    DealTotalPrice, QcTotalPrice, PcTotalPrice, GatherTotalPrice,' +
              '    EndDealTotalPrice, EndQcTotalPrice, EndPcTotalPrice, EndGatherTotalPrice,' +
              '    PreDealTotalPrice, PreQcTotalPrice, PrePcTotalPrice, PreGatherTotalPrice)' +
              '  Values (-2,'+
              '    %f, %f, %f, %f,'+
              '    %f, %f, %f, %f,'+
              '    %f, %f, %f, %f)';
  var
    sSql: string;
  begin
    with PhaseData.StageData do
      sSql := Format(sAddSql,
          [StageSettlement[1,1], StageSettlement[1,2], StageSettlement[1,3], StageSettlement[1,4],
           StageSettlement[2,1], StageSettlement[2,2], StageSettlement[2,3], StageSettlement[2,4],
           StageSettlement[3,1], StageSettlement[3,2], StageSettlement[3,3], StageSettlement[3,4]]);
    ExecuteSql(sSql);
  end;

  procedure UpdateZJJLData;
  const
    sUpdateSql = 'Update P_ZJJL' +
                 '  Set BGLCode = ''%s'', PegName = ''%s'', FBFXName = ''%s'',' +
                 '      UnitName = ''%s'', DrawingCode = ''%s''' +
                 '  Where ID = %d';
  var
    sSql: string;
    ZJJLInfoRec: TZJJLInfoRec;
  begin
    with PhaseData.ZJJLData do
    begin
      cdsZJJL.First;
      while not cdsZJJL.Eof do
      begin
        ZJJLInfoRec := GetInfoRec(cdsZJJLBillsID.AsInteger, cdsZJJLType.AsInteger);
        sSql := Format(sUpdateSql, [ZJJLInfoRec.BGLCode, ZJJLInfoRec.PegName,
            ZJJLInfoRec.FBFXName, ZJJLInfoRec.UnitName, ZJJLInfoRec.DrawingCode,
            cdsZJJLID.AsInteger]);
        ExecuteSql(sSql);
        cdsZJJL.Next;
      end;
    end;
  end;

  procedure CopyZJJLBillsData;
  var
    sSql: string;
  begin
    sSql := 'Select PZ.ID, B.B_Code, B.Name, B.Units, B.Price, PS.GatherQuantity As Quantity, PS.GatherTotalPrice As TotalPrice' +
            '  Into P_ZJJLBills'+
            '  From Bills As B, P_Stage As PS, P_ZJJL As PZ' +
            '  Where (PZ.BillsID = B.LeafXmjParentID) And (B.ID = PS.BillsID) And (B.IsLeaf=True)';
    ExecuteSql(sSql);
  end;

  procedure CopyZJJLData(const AFileName: string);
  var
    sSql: string;
  begin
    sSql := 'Select ID, BillsID, Code, CertificateCode, BillsCode, FormulaMemo, RelaFile,' +
            '   '''' As BGLCode, '''' As PegName, '''' As FBFXName, '''' As UnitName, '''' As DrawingCode' +
            '  Into P_ZJJL' +
            '  From ' + PhaseData.ZJJLData.atZJJL.TableName +
            '  In ' + Format('''%s''', [AFileName]);
    ExecuteSql(sSql);
    // 获取分部分项等实时统计字段值
    UpdateZJJLData;
    CopyZJJLBillsData;
  end;

  procedure CopyPhasePayData(const AFileName: string);
  const
    sCopySql = 'Select ID, TotalPrice%d As CurTotalPrice, EndTotalPrice%d As EndTotalPrice,' +
               '    PreTotalPrice%d As PreTotalPrice,' +
               '    TotalPrice0, EndTotalPrice0, PreTotalPrice0,' +
               '    TotalPrice1, EndTotalPrice1, PreTotalPrice1,' +
               '    TotalPrice2, EndTotalPrice2, PreTotalPrice2,' +
               '    TotalPrice3, EndTotalPrice3, PreTotalPrice3,' +
               '    TotalPrice4, EndTotalPrice4, PreTotalPrice4,' +
               '    TotalPrice5, EndTotalPrice5, PreTotalPrice5,' +
               '    TotalPrice6, EndTotalPrice6, PreTotalPrice6,' +
               '    TotalPrice7, EndTotalPrice7, PreTotalPrice7,' +
               '    TotalPrice8, EndTotalPrice8, PreTotalPrice8,' +
               '    TotalPrice9, EndTotalPrice9, PreTotalPrice9,' +
               '    TotalPrice10, EndTotalPrice10, PreTotalPrice10,' +
               '    TotalPrice11, EndTotalPrice11, PreTotalPrice11,' +
               '    TotalPrice12, EndTotalPrice12, PreTotalPrice12,' +
               '    TotalPrice13, EndTotalPrice13, PreTotalPrice13,' +
               '    TotalPrice14, EndTotalPrice14, PreTotalPrice14,' +
               '    TotalPrice%d As TotalPrice_F, EndTotalPrice%d As EndTotalPrice_F, PreTotalPrice%d As PreTotalPrice_F' +
               '  Into P_PhasePay' +
               '  From PhasePay' +
               '  In ''%s''';
  var
    sSql: string;
  begin
    with PhaseData do
      sSql := Format(sCopySql, [StageIndex, StageIndex, StageIndex, StageCount - 1, StageCount - 1, StageCount - 1, AFileName]);
    ExecuteSql(sSql);
  end;

  procedure CopyReportData(const AFileName: string);
  const
    sCopySql = 'Select Flag, SubFlag, SerialNo, Select1, Result1, Result2' +
               '  Into P_ReportData' +
               '  From ReportData' +
               '  In ''%s''';
  begin
    ExecuteSql(Format(sCopySql, [AFileName]));
  end;

  procedure CopyCurPhaseData;
  var
    sTempFile: string;
    i: Integer;
  begin
    sTempFile := GetTempFileName;
    try
      SimpleDecrypt(Format('%s\Phase%d.dat', [TempPath, FPhaseIndex]), sTempFile);
      // 复制当前阶段数据
      CopyStageData(sTempFile, PhaseData.StageData.sdpStage.TableName, 'P_Stage');
      // 复制原报、审核、批复数据
      // 其中审核、批复数据，如果存在则复制，如果不存在，则创建空表
      CopyStageData(sTempFile, 'Refer', 'P_Refer');
      for i := 1 to iMaxStageCount - 1 do
        if PhaseData.AuditCount >= i then
          CopyStageData(sTempFile, PhaseData.StageTableName[i],
              'P_Audit' + IntToStr(i))
        else
          CopyEmptyStageData(sTempFile, PhaseData.StageTableName[PhaseData.AuditCount],
              'P_Audit' + IntToStr(i));
      if PhaseData.StageCount > 1 then
        CopyStageData(sTempFile, PhaseData.StageTableName[PhaseData.AuditCount], 'P_Reply')
      else
        CopyEmptyStageData(sTempFile, PhaseData.StageTableName[PhaseData.AuditCount], 'P_Reply');

      AddSettlementData;
      CopyZJJLData(sTempFile);
      CopyPhasePayData(sTempFile);
      CopyReportData(sTempFile);
    finally
      DeleteFile(sTempFile);
    end;
  end;

  procedure CopyHistoryCompleteData;
  const
    sCopySql1 = 'Select BillsID, 0 As Quantity1, 0 As TotalPrice1,' +
                '    Quantity%d As Quantity2, TotalPrice%d As TotalPrice2' +
                '  Into P_Complete' +
                '  From PhaseComplete';
    sCopySqlN = 'Select BillsID, Quantity%d As Quantity1, TotalPrice%d As TotalPrice1,' +
                '    Quantity%d As Quantity2, TotalPrice%d As TotalPrice2' +
                '  Into P_Complete' +
                '  From PhaseComplete';
    sUnionCopySql1 = 'Select BillsID, 0 As Quantity1, 0 As TotalPrice1,' +
                     '    GatherQuantity As Quantity2, GatherTotalPrice As TotalPrice2' +
                     '  Into P_Complete' +
                     '  From P_Stage';
    sUnionCopySqlN = 'Select PS.BillsID As BillsID,' +
                     '   PC.Quantity%d As Quantity1,' +
                     '   PC.TotalPrice%d As TotalPrice1,' +
                     '   PC.Quantity%d + PS.GatherQuantity As Quantity2,' +
                     '   PC.TotalPrice%d + PS.GatherTotalPrice As TotalPrice2' +
                     '  Into P_Complete' +
                     '  From PhaseComplete As PC, P_Stage As PS';
  var
    sSql: string;
  begin
    if (ProjProperties.FinalAuditCount >= PhaseIndex) then
      if PhaseIndex <= 1 then
        sSql := Format(sCopySql1, [FPhaseIndex, FPhaseIndex])
      else
        sSql := Format(sCopySqlN, [PhaseIndex - 1, PhaseIndex - 1, PhaseIndex, PhaseIndex])
    else
      if PhaseIndex <= 1 then
        sSql := sUnionCopySql1
      else
        sSql := Format(sUnionCopySqlN, [PhaseIndex - 1, PhaseIndex - 1, PhaseIndex - 1, PhaseIndex - 1]);
    ExecuteSql(sSql);
  end;

begin
  Save;
  DeletePhaseTable;
  if FPhaseIndex > 0 then
    CopyCurPhaseData;
  FDealPaymentData.UpdateLinkSerialNo;
  //CopyHistoryCompleteData;
end;

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

function TProjectData.GetADOConnection: TADOConnection;
begin
  Result := FConnection.Connection;
end;

procedure TProjectData.ExportJson_Common(const AFileName: string);
var
  Rec: TsdDataRecord;
  vSL: TStringList;
  sBQHTJL, sBQSLBGJL, sJZSQLJWC, sBQWCJL, sLJWCJL, sTotalPrice: string;
begin
  vSL := TStringList.Create;
  try
    Rec := MainListData.GetPhaseMainRecord(ProjProperties.PhaseCount);

    sBQHTJL       := FloatToStr(Rec.ValueByName('DealTotalPrice').AsFloat);  // 本期合同计量
    sBQSLBGJL     := FloatToStr(Rec.ValueByName('QcTotalPrice').AsFloat);  // 本期数量变更计量
    sJZSQLJWC     := FloatToStr(Rec.ValueByName('PreGatherTotalPrice').AsFloat);  // 截止上期累计完成
    sBQWCJL       := FloatToStr(Rec.ValueByName('GatherTotalPrice').AsFloat);  // 本期完成计量
    sLJWCJL       := FloatToStr(Rec.ValueByName('EndGatherTotalPrice').AsFloat);  // 累计完成计量
    sTotalPrice   := FloatToStr(BillsData.Settlement[0] + Rec.ValueByName('EndQcTotalPrice').AsFloat);  // 0号台帐+截止本期累期变更计量
    vSL.Text := Format('{"BQHTJL":"%s","BQSLBGJL":"%s","JZSQLJWC":"%s","BQWCJL":"%s","LJWCJL":"%s","TotalPrice":"%s"}',
      [sBQHTJL, sBQSLBGJL, sJZSQLJWC, sBQWCJL, sLJWCJL, sTotalPrice]);
    vSL.SaveToFile(AFileName);
  finally
    vSL.Free;
  end;
end;

function TProjectData.CurUserIsAuthor: Boolean;
begin
  Result := PHPWeb.UserID = WebAuthorID;
end;

function TProjectData.CurUserIsChecker: Boolean;
begin
  Result := (PHPWeb.UserID <> WebOwnerID) and (PHPWeb.UserID <> WebAuthorID);
end;

function TProjectData.CurUserIsOwner: Boolean;
begin
  Result := PHPWeb.UserID = WebOwnerID;
end;

procedure TProjectData.SaveLastestPhaseMainData;
var
  Rec: TsdDataRecord;
begin
  if (FPhaseIndex = ProjProperties.PhaseCount) and PhaseData.IsLastStage then
  begin
    Rec := MainListData.GetPhaseMainRecord(FPhaseIndex);
    Rec.ValueByName('DealTotalPrice').AsFloat := PhaseData.StageData.StageSettlement[1, 1];
    Rec.ValueByName('QcTotalPrice').AsFloat := PhaseData.StageData.StageSettlement[1, 2];
    Rec.ValueByName('PcTotalPrice').AsFloat := PhaseData.StageData.StageSettlement[1, 3];
    Rec.ValueByName('GatherTotalPrice').AsFloat := PhaseData.StageData.StageSettlement[1, 4];
    Rec.ValueByName('EndDealTotalPrice').AsFloat := PhaseData.StageData.StageSettlement[2, 1];
    Rec.ValueByName('EndQcTotalPrice').AsFloat := PhaseData.StageData.StageSettlement[2, 2];
    Rec.ValueByName('EndPcTotalPrice').AsFloat := PhaseData.StageData.StageSettlement[2, 3];
    Rec.ValueByName('EndGatherTotalPrice').AsFloat := PhaseData.StageData.StageSettlement[2, 4];
    Rec.ValueByName('PreDealTotalPrice').AsFloat := PhaseData.StageData.StageSettlement[3, 1];
    Rec.ValueByName('PreQcTotalPrice').AsFloat := PhaseData.StageData.StageSettlement[3, 2];
    Rec.ValueByName('PrePcTotalPrice').AsFloat := PhaseData.StageData.StageSettlement[3, 3];
    Rec.ValueByName('PreGatherTotalPrice').AsFloat := PhaseData.StageData.StageSettlement[3, 4];
    Rec.ValueByName('PhasePay').AsFloat := PhaseData.PhasePayData.LastestPhasePay[1];
    Rec.ValueByName('EndPhasePay').AsFloat := PhaseData.PhasePayData.LastestPhasePay[2];
    Rec.ValueByName('PrePhasePay').AsFloat := PhaseData.PhasePayData.LastestPhasePay[3];
  end;
end;

procedure TProjectData.CalculateAll;
begin
  FBillsCompileData.CalculateAll;
  if not FPhaseData.StageDataReadOnly then
    FPhaseData.StageData.CalculateAll;
  FBillsMeasureData.CalculateAll;
end;

procedure TProjectData.ImportCloudTenderFile(const AFileName: string);

  function GetTempFile(const TempDir: string): string;
  var
    FXmlDocument: IXMLDocument;
    XmlNode, InfoXmlNode: IXMLNode;
    ChildNodes: IXMLNodeList;
  begin
    FXmlDocument := TXMLDocument.Create(nil) as IXMLDocument;
    try
      FXmlDocument.LoadFromFile(TempDir + '\Info.xml');
      FXmlDocument.Options := [doNodeAutoCreate,doNodeAutoIndent,doAutoPrefix,doNamespaceDecl];
      XmlNode := FXmlDocument.DocumentElement;
      ChildNodes := XmlNode.ChildNodes;
      InfoXmlNode := ChildNodes.FindNode('ProjectInfo');
      Result := TempDir + '\' + InfoXmlNode.Attributes['FileName'];
    finally
      FXmlDocument := nil;
    end;
  end;

  procedure ClearAllData;
  var
    sSql: string;
  begin
    sSql := 'Delete * From Bills';
    ExecuteSql(sSql);
    sSql := 'Delete * From DealBills';
    ExecuteSql(sSql);
    sSql := 'Delete * From ProjProperties';
    ExecuteSql(sSql);
    sSql := 'Delete * From DealPayment';
    ExecuteSql(sSql);
  end;

  procedure CopyData(const AFileName: string);
  const
    sBillsSql = 'Insert Into Bills (ID, ParentID, NextSiblingID,' +
                '    Code, B_Code, Name, Units, Alias, Price, NewPrice,' +
                '    OrgQuantity, OrgTotalPrice,' +
                '    DgnQuantity1, DgnQuantity2,'+
                '    Peg, DrawingCode, MemoStr, HasBookMark, MarkMemo)'+
                '  Select ID, ParentID, NextSiblingID,'+
                '    Code, B_Code, Name, Units, Alias, Price, NewPrice,' +
                '    OrgQuantity, OrgTotalPrice,' +
                '    DgnQuantity1, DgnQuantity2,'+
                '    Peg, DrawingCode, MemoStr, HasBookMark, MarkMemo'+
                '  From Bills In ''%s''';

    sDealBillsSql = 'Insert Into DealBills Select * From DealBills In ''%s''';

    sDealPaymentSql = 'Insert Into DealPayment (ID, Name, CalcType, IsMinus,'+
                      '    Formula, StartedPrice, SFormula, RangePrice, RFormula)'+
                      '  Select ID, Name, CalcType, IsMinus,'+
                      '    Formula, StartedPrice, SFormula, RangePrice, RFormula'+
                      '  From DealPayment In ''%s''';

    sPropertySql = 'Insert Into ProjProperties'+
                   '  Select * From ProjProperties In ''%s'''+
                   '  Where (Name <> ''PHASECOUNT'') and (Name <> ''UNLOCKINFOPASSWORD'') and (Name <> ''AUDITSTATUS'')'+
                   '    and (Name <> ''FINALAUDITCOUNT'') and (Name <> ''AUDITCOMPANY'') and (Name <> ''UPDATEFLAG'')';
  var
    sSql: string;
  begin
    sSql := Format(sBillsSql, [AFileName]);
    ExecuteSql(sSql);
    sSql := Format(sDealBillsSql, [AFileName]);
    ExecuteSql(sSql);
    sSql := Format(sDealPaymentSql, [AFileName]);
    ExecuteSql(sSql);
    sSql := Format(sPropertySql, [AFileName]);
    ExecuteSql(sSql);
  end;

  function CreateTempSourceFile: string;
  var
    TempDir, TempDir2: string;
  begin
    TempDir := GenerateTempFolder(GetTempPath);
    UnZipFile(AFileName, TempDir);
    try
      TempDir2 := GenerateTempFolder(GetTempPath);
      try
        UnZipFile(GetTempFile(TempDir), TempDir2);
        Result := GetTempFileName;
        SimpleDecrypt(TempDir2 + '\Main.dat', Result);
      finally
        DeleteFileOrFolder(TempDir2);
      end;
    finally
      DeleteFileOrFolder(TempDir);
    end;
  end;

  procedure BeforeCopyData;
  begin
    DisConnectTree;
    CloseAllData;
  end;

  procedure EndCopyData;
  begin
    OpenAllData;
    ReConnectTree;
    FProjProperties.UpdateFlag := 1;
    BillsCompileData.CalculateAll;
  end;

  function CheckFile: Boolean;
  var
    TempDir: string;
    Proj: TProjectData;
  begin
    TempDir := GenerateTempFolder(GetTempPath);
    UnZipFile(AFileName, TempDir);
    try
      Proj := TProjectData.Create;
      Proj.SimpleOpen(GetTempFile(TempDir));
      Result := Proj.ProjProperties.UpdateFlag = 1;
    finally
      Proj.Free;
      DeleteFileOrFolder(TempDir);
    end;
  end;

var
  sTemMainFile: string;
begin
  sTemMainFile := CreateTempSourceFile;
  BeforeCopyData;
  try
    ClearAllData;
    CopyData(sTemMainFile);
  finally
    EndCopyData;
    DeleteFileOrFolder(sTemMainFile);
  end;
end;

function TProjectData.CheckPassword: Boolean;
var
  sPassword: string;
begin
  if ProjProperties.UnlockInfoPassword = '' then
    Result := True
  else
  begin
    Result := InputPassword(sPassword);
    if Result then
    begin
      Result := sPassword = ProjProperties.UnlockInfoPassword;
      if not Result then ErrorMessage('您输入的密码不正确！');
    end;
  end;
end;

procedure TProjectData.SetCheckers(const Value: TCheckers);
begin
  FCheckers := Value;
end;

procedure TProjectData.OpenForSubmit(const AFileName: string);
begin
  FProjectID := -1;
  UnZipFile(AFileName, TempPath);
  FConnection.Open(MainFileName);
  FProjProperties.Open(FConnection.Connection);
  FStaffData.Open(FConnection.Connection);
end;

procedure TProjectData.SaveAsForSubmit(const AFileName: string);
begin
  FStaffData.Save;
  FProjProperties.Save;
  FConnection.Save;
  ZipFolder(FTempFolder, AFileName);
end;

procedure TProjectData.LockBillsBaseData;
const
  sLockInfoSql = 'Update Bills Set LockedInfo = True, LockedLevel = True';
  sLockNPSql = 'Update Bills Set LockedNewPrice = True Where NewPrice <> 0';
begin
  ExecuteSql(sLockInfoSql);
  ExecuteSql(sLockNPSql);
end;

procedure TProjectData.LockDealPaymentData;
const
  sLockDealSql = 'Update DealPayment Set Locked = True';
  sLockFormulaSql = 'Update DealPayment Set LockedFormula = True Where (Formula <> '''')';
begin
  ExecuteSql(sLockDealSql);
  ExecuteSql(sLockFormulaSql);
end;

procedure TProjectData.LockBGLData;
const
  sBGSql = 'Update BGL Set Locked = True';
  sBGBillsSql = 'Update BGBills Set Locked = True';
begin
  ExecuteSql(sBGSql);
  ExecuteSql(sBGBillsSql);
end;

procedure TProjectData.OpenForReport(const AFileName: string);
begin
  FProjectID := -1;
  UnZipFile(AFileName, TempPath);
  FConnection.Open(MainFileName);
  UpdateProjectDataBase;
  FProjProperties.Open(FConnection.Connection);
  UpdateOldData;
  FBillsData.Open(FConnection.Connection);
  FBillsCompileData.Open;
  FDealPaymentData.Open(FConnection.Connection);
  FBGLData.Open(FConnection.Connection);  
end;

procedure TProjectData.OpenForReply(const AFileName: string);
begin
  FProjectID := -1;
  UnZipFile(AFileName, TempPath);
  FConnection.Open(MainFileName);
  FProjProperties.Open(FConnection.Connection);
  if ProjProperties.PhaseCount > 0 then
  begin
    FPhaseIndex := ProjProperties.PhaseCount;
    FPhaseData.SimpleOpen(Format('%sPhase%d.dat', [TempPath, FPhaseIndex]));
  end;
  FStaffData.Open(FConnection.Connection);
end;

procedure TProjectData.SaveForReply(const AFileName: string);
begin
  FStaffData.Save;
  if FPhaseData.Active then
    FPhaseData.SimpleSave;
  FProjProperties.Save;
  FConnection.Save;
  ZipFolder(FTempFolder, AFileName);
end;

procedure TProjectData.OpenForReceive(const AFileName: string);
begin
  FProjectID := -1;
  UnZipFile(AFileName, TempPath);
  FConnection.Open(MainFileName);
  FProjProperties.Open(FConnection.Connection);
  if ProjProperties.PhaseCount > 0 then
  begin
    FPhaseIndex := ProjProperties.PhaseCount;
    FPhaseData.SimpleOpen(Format('%sPhase%d.dat', [TempPath, FPhaseIndex]));
  end;
  FStaffData.Open(FConnection.Connection);
end;

procedure TProjectData.SaveForReceive(const AFileName: string);
begin
  FStaffData.Save;
  if FPhaseData.Active then
    FPhaseData.SimpleSave;
  FProjProperties.Save;
  FConnection.Save;
  ZipFolder(FTempFolder, AFileName);
end;

procedure TProjectData.CloseAllData;
begin
  FBillsData.Close;
  FBillsCompileData.Close;
  FBillsMeasureData.Close;
  FDealBillsData.Close;
  FDealPaymentData.Close;
  FBGLData.Close;
  FStaffData.Close;
  FMainListData.Close;
end;

procedure TProjectData.OpenAllData;
begin
  FProjProperties.Open(FConnection.Connection);
  FBillsData.Open(FConnection.Connection);
  FBillsCompileData.Open;
  FBillsMeasureData.Open;
  FDealBillsData.Open(FConnection.Connection);
  FDealPaymentData.Open(FConnection.Connection);
  FBGLData.Open(FConnection.Connection);
  FStaffData.Open(FConnection.Connection);
  FMainListData.Open(FConnection.Connection);
end;

procedure TProjectData.UpdateOldData;

  procedure UpdateBills_OrgData;
  const
    sUpdateSql = 'Update Bills Set OrgQuantity = Quantity, OrgTotalPrice = TotalPrice';
  begin
    ExecuteSql(sUpdateSql);
  end;

begin
  if ProjProperties.UpdateFlag = 1 then Exit;
  ProjProperties.UpdateFlag := 1;
  UpdateBills_OrgData;
end;

procedure TProjectData.ResetFloatDigitView;

  procedure SetBillsCompileDigit;
  begin
    with FBillsCompileData.sdvBillsCompile do
    begin
      Columns.FindColumn('OrgQuantity').DisplayFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('MisQuantity').DisplayFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('OthQuantity').DisplayFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('Quantity').DisplayFormat := FProjProperties.QuantityFormat;

      Columns.FindColumn('DgnQuantity1').DisplayFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('DgnQuantity2').DisplayFormat := FProjProperties.QuantityFormat;

      Columns.FindColumn('OrgTotalPrice').DisplayFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('MisTotalPrice').DisplayFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('OthTotalPrice').DisplayFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('TotalPrice').DisplayFormat := FProjProperties.TotalPriceFormat;

      Columns.FindColumn('Price').DisplayFormat := FProjProperties.PriceFormat;
      Columns.FindColumn('DgnPrice').DisplayFormat := FProjProperties.PriceFormat;

      Columns.FindColumn('OrgQuantity').EditFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('MisQuantity').EditFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('OthQuantity').EditFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('Quantity').EditFormat := FProjProperties.QuantityFormat;

      Columns.FindColumn('DgnQuantity1').EditFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('DgnQuantity2').EditFormat := FProjProperties.QuantityFormat;

      Columns.FindColumn('OrgTotalPrice').EditFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('MisTotalPrice').EditFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('OthTotalPrice').EditFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('TotalPrice').EditFormat := FProjProperties.TotalPriceFormat;

      Columns.FindColumn('Price').EditFormat := FProjProperties.PriceFormat;
      Columns.FindColumn('DgnPrice').EditFormat := FProjProperties.PriceFormat;
    end;
  end;

  procedure SetBillsMeasureDigit;
  begin
    with FBillsMeasureData.sdvBillsMeasure do
    begin
      Columns.FindColumn('Quantity').DisplayFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('CurDealQuantity').DisplayFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('CurQcQuantity').DisplayFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('CurPcQuantity').DisplayFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('CurGatherQuantity').DisplayFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('EndDealQuantity').DisplayFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('EndQcQuantity').DisplayFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('EndPcQuantity').DisplayFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('EndGatherQuantity').DisplayFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('AddDealQuantity').DisplayFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('AddQcQuantity').DisplayFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('AddPcQuantity').DisplayFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('AddGatherQuantity').DisplayFormat := FProjProperties.QuantityFormat;

      Columns.FindColumn('DealDgnQuantity1').DisplayFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('DealDgnQuantity2').DisplayFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('CDgnQuantity1').DisplayFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('CDgnQuantity2').DisplayFormat := FProjProperties.QuantityFormat;

      Columns.FindColumn('TotalPrice').DisplayFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('CurDealTotalPrice').DisplayFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('CurQcTotalPrice').DisplayFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('CurPcTotalPrice').DisplayFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('CurGatherTotalPrice').DisplayFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('EndDealTotalPrice').DisplayFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('EndQcTotalPrice').DisplayFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('EndPcTotalPrice').DisplayFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('EndGatherTotalPrice').DisplayFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('AddDealTotalPrice').DisplayFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('AddQcTotalPrice').DisplayFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('AddPcTotalPrice').DisplayFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('AddGatherTotalPrice').DisplayFormat := FProjProperties.TotalPriceFormat;

      Columns.FindColumn('Price').DisplayFormat := FProjProperties.PriceFormat;
      Columns.FindColumn('NewPrice').DisplayFormat := FProjProperties.PriceFormat;

      Columns.FindColumn('Quantity').EditFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('CurDealQuantity').EditFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('CurQcQuantity').EditFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('CurPcQuantity').EditFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('CurGatherQuantity').EditFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('EndDealQuantity').EditFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('EndQcQuantity').EditFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('EndPcQuantity').EditFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('EndGatherQuantity').EditFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('AddDealQuantity').EditFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('AddQcQuantity').EditFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('AddPcQuantity').EditFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('AddGatherQuantity').EditFormat := FProjProperties.QuantityFormat;

      Columns.FindColumn('DealDgnQuantity1').EditFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('DealDgnQuantity2').EditFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('CDgnQuantity1').EditFormat := FProjProperties.QuantityFormat;
      Columns.FindColumn('CDgnQuantity2').EditFormat := FProjProperties.QuantityFormat;

      Columns.FindColumn('TotalPrice').EditFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('CurDealTotalPrice').EditFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('CurQcTotalPrice').EditFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('CurPcTotalPrice').EditFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('CurGatherTotalPrice').EditFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('EndDealTotalPrice').EditFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('EndQcTotalPrice').EditFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('EndPcTotalPrice').EditFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('EndGatherTotalPrice').EditFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('AddDealTotalPrice').EditFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('AddQcTotalPrice').EditFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('AddPcTotalPrice').EditFormat := FProjProperties.TotalPriceFormat;
      Columns.FindColumn('AddGatherTotalPrice').EditFormat := FProjProperties.TotalPriceFormat;

      Columns.FindColumn('Price').EditFormat := FProjProperties.PriceFormat;
      Columns.FindColumn('NewPrice').EditFormat := FProjProperties.PriceFormat;
    end;
  end;

begin
  SetBillsCompileDigit;
  SetBillsMeasureDigit;
end;

function TProjectData.GetStageDataReadOnly: Boolean;
begin
 if FPhaseData.Active then
    Result := FPhaseData.StageDataReadOnly
  else
    Result := True;
end;

procedure TProjectData.OpenForReport2(const AFileName: string);
begin
  FProjectID := -1;
  UnZipFile(AFileName, TempPath);
  FConnection.Open(MainFileName);
  UpdateProjectDataBase;
  FProjProperties.Open(FConnection.Connection);
  UpdateOldData;
  FBillsData.Open(FConnection.Connection);
  FBillsCompileData.Open;
  FDealPaymentData.Open(FConnection.Connection);
  FBGLData.Open(FConnection.Connection);
  if ProjProperties.PhaseCount > 0 then
  begin
    FPhaseIndex := ProjProperties.PhaseCount;
    FPhaseData.SimpleOpen(Format('%sPhase%d.dat', [TempPath, FPhaseIndex]));
  end;
end;

procedure TProjectData.OpenForReport3(const AFileName: string);
begin
  FProjectID := -1;
  UnZipFile(AFileName, TempPath);
  FConnection.Open(MainFileName);
  UpdateProjectDataBase;
  FProjProperties.Open(FConnection.Connection);
  UpdateOldData;
  FBillsData.Open(FConnection.Connection);
  FBillsMeasureData.Open;
  FDealPaymentData.Open(FConnection.Connection);
  FBGLData.Open(FConnection.Connection);
  if ProjProperties.PhaseCount > 0 then
  begin
    FPhaseIndex := ProjProperties.PhaseCount;
    FPhaseData.SimpleOpen2(Format('%sPhase%d.dat', [TempPath, FPhaseIndex]));
  end;
  FBillsMeasureData.ResetTreeNodeStageRec;
end;

end.