BillsEditor/DB/ScReportDM.pas

{*******************************************************************************
    单元名称： ScReportDM.pas

    单元说明： 报表数据源
               备注：ItemKind: 预清单1，清清单2，图纸工程量3，用于排序、过滤。

               创建一张临时表，所有报表数据都从这张临时表用SQL语句查得。
               表1：分项工程量清单：全部数据，预算项目节、清单子目号、图纸工程量。
               表2-1：工程项目清单：汇总项目的软件上部分看到的预、清（即不含图）。
               表2-2：工程项目清单(带细目)：汇总项目的分项工程量清单。即先汇总生成另外一个项目，再分项工程量清单。
               表3-1：工程量清单：清单子目号级清单汇总，不含图纸工程量（即去掉预部分，只要清部分，且汇总）。
               表3-2：工程量清单(带细目)：清单子目号级清单汇总，含图纸工程量。
               其中，表2-1、表2-2，已经脱离了自身，操作另外一个项目。

    作者时间： Chenshilong, 2011-01-07 18:20:56

*******************************************************************************}


unit ScReportDM;

interface

uses
  SysUtils, Classes, DB, ADODB, DBClient;

type
  TReportData = class(TDataModule)
    cdsProjData: TClientDataSet;
    aqSQL: TADOQuery;
    cdsProjDataBuildProjectID: TIntegerField;
    cdsProjDataBuildProjectName: TWideStringField;
    cdsProjDataProjectLocation: TWideStringField;
    cdsProjDataBuildUnit: TWideStringField;
    cdsProjDataAuthorUnit: TWideStringField;
    cdsProjDataBidder: TWideStringField;
    cdsProjDataAuthor: TWideStringField;
    cdsProjDataAuthorCertificate: TWideStringField;
    cdsProjDataAuditor: TWideStringField;
    cdsProjDataAuditorCertificate: TWideStringField;
    cdsProjDataBudgetProjectName: TWideStringField;
    cdsProjDataEditRange: TWideStringField;
    cdsProjDataEditDate: TDateTimeField;
    cdsProjDataMachineBBFeeRate: TFloatField;
    cdsProjDataFZFeeRate: TFloatField;
    cdsProjDataIsCalcGYFeeRate: TBooleanField;
    cdsProjDataRoadLength: TFloatField;
    cdsProjDataAvgMaintMonth: TFloatField;
    cdsProjDataRoadClass: TIntegerField;
    cdsProjDataIsNew: TBooleanField;
    cdsProjDataLandForm: TIntegerField;
    cdsProjDataDJZG: TFloatField;
    cdsProjDataYJZG: TFloatField;
    cdsProjDataNightZG: TFloatField;
    cdsProjDataRaiseRateByYear: TFloatField;
    cdsProjDataRaiseYear: TFloatField;
    cdsProjDataBuildManageFeeFile: TIntegerField;
  private
    FProject: TObject;
    FFirstGetData: Boolean;
    FConnection: TADOConnection;
    procedure SetProject(const Value: TObject);
    function GetReportPath: string;
//    procedure CreateReportTempTab;
  public
    { Public declarations }
//    function GetReportData(AReportName: string): Boolean;

    constructor Create(AOwner: TComponent); override;
    procedure RefreshData;

    property Project: TObject read FProject write SetProject;
    property ReportPath: string read GetReportPath;
    property FirstGetData: Boolean read FFirstGetData write FFirstGetData;
    property Connection: TADOConnection read FConnection write FConnection;

  end;

implementation

uses ScProjectManager, Forms, DataBase, ScProgressFrm;

{$R *.dfm}


constructor TReportData.Create(AOwner: TComponent);
begin
  inherited;
  FirstGetData := True;
end;

function TReportData.GetReportPath: string;
begin
  Result := ExtractFilePath(Application.ExeName) + 'BillsEditorReports\';
end;

procedure TReportData.SetProject(const Value: TObject);
begin
  FProject := Value;
  cdsProjData.CloneCursor(TProject(FProject).ProjPropertyDM.cdsProjData, True);
  FConnection := TProject(FProject).Connection;
  aqSQL.Connection := FConnection;
end;

{  速度太慢,取消临时表机制 //  chenshilong, 2011-11-08
procedure TReportData.CreateReportTempTab;
const
  sName: String = 'ReportTempTab';
var i: Integer;

  function IsExist(ATabName: string): Boolean;
  var vSL: TStringList;
  begin
    vSL := TStringList.Create;
    try
      FConnection.GetTableNames(vSL);
      if vSL.IndexOf(ATabName) < 0 then
        Result := False
      else
        Result := True;
    finally
      vSL.Free;
    end;
  end;
begin
  i := 0;
  with TProject(FProject).BillsData do
    CreateProgressForm(cdsBills.RecordCount + cdsDrawingQuantity.RecordCount);

  try
    if IsExist(sName) then
    begin
      aqSQL.SQL.Clear;
      aqSQL.SQL.Add('Drop Table ' + sName);
      aqSQL.ExecSQL;
    end;

    RefreshProgressForm('正在创建临时表……');
    aqSQL.SQL.Clear;
    aqSQL.SQL.Add(Format('Create Table %s (ID Integer, SerialNo Integer, ItemKind Integer, '
        + 'OrderNo Integer, Code Text(255), B_Code Text(255), Name Text(255), '
        + 'Units Text (50), Quantity Double, UnitPrice Double, TotalPrice Double, '
        + 'DesignQuantity Double, DesignQuantity2 Double, '
        + 'MemoStr Text (255), B_CodeAlpha Text (255))', [sName]));
    aqSQL.ExecSQL;

    aqSQL.SQL.Clear;
    aqSQL.SQL.Add('Select * From ' + sName);
    aqSQL.Open;

    RefreshProgressForm('正在生成临时表数据：');
    with TProject(FProject).BillsData do
    begin
      cdsBills.First;
      while not cdsBills.Eof do
      begin
        aqSQL.Append;
        aqSQL.FieldByName('ID').AsInteger := cdsBillsID.AsInteger;
        aqSQL.FieldByName('SerialNo').AsInteger := cdsBillsSerialNo.AsInteger;
        // ItemKind: 预清单1，清清单2，图纸工程量3，用于排序、过滤
        if Trim(cdsBillsB_Code.AsString) <> '' then
        begin
          aqSQL.FieldByName('ItemKind').AsInteger := 2;
          // BCodeAlpha字段用于报表3-2：工程量清单(带细目).fr3。作用是统计后按这个字段排序
          aqSQL.FieldByName('B_CodeAlpha').AsString := FormatBCodeAlpha(cdsBillsB_Code.AsString);
        end
        else
          aqSQL.FieldByName('ItemKind').AsInteger := 1;
        // OrderNo：排序字段，图纸工程量之间有先后排序
        aqSQL.FieldByName('OrderNo').AsInteger := 0;
        aqSQL.FieldByName('Code').AsString := cdsBillsCode.AsString;
        aqSQL.FieldByName('B_Code').AsString := cdsBillsB_Code.AsString;
        aqSQL.FieldByName('Name').AsString := cdsBillsName.AsString;
        aqSQL.FieldByName('Units').AsString := cdsBillsUnits.AsString;
        aqSQL.FieldByName('Quantity').AsCurrency := cdsBillsQuantity.AsCurrency;
        aqSQL.FieldByName('DesignQuantity').AsCurrency := cdsBillsDesignQuantity.AsCurrency;
        aqSQL.FieldByName('DesignQuantity2').AsCurrency := cdsBillsDesignQuantity2.AsCurrency;
        aqSQL.FieldByName('UnitPrice').AsCurrency := cdsBillsUnitPrice.AsCurrency;
        aqSQL.FieldByName('TotalPrice').AsCurrency := cdsBillsTotalPrice.AsCurrency;
        aqSQL.FieldByName('MemoStr').AsString := cdsBillsMemoStr.AsString;
        aqSQL.Post;
        Inc(i);
        AddProgressForm(1, '正在生成临时表数据：' + IntToStr(i));
        cdsBills.Next;
      end;

      cdsDrawingQuantity.First;
      while not cdsDrawingQuantity.Eof do
      begin
        aqSQL.Append;
        if cdsBillsLookup.Locate('ID', cdsDrawingQuantityBillsID.AsInteger, []) then
        begin
          // 图纸工程量的SerialNo取所属清单的SerialNo，以保证能和清单排在一起。
          aqSQL.FieldByName('SerialNo').AsInteger := cdsBillsLookupSerialNo.AsInteger;
           aqSQL.FieldByName('B_CodeAlpha').AsString := FormatBCodeAlpha(cdsBillsLookupB_Code.AsString);
        end;

        aqSQL.FieldByName('ItemKind').AsInteger := 3;
        aqSQL.FieldByName('OrderNo').AsInteger := cdsDrawingQuantitySerinalNo.AsInteger;
        aqSQL.FieldByName('Name').AsString := cdsDrawingQuantityName.AsString;
        aqSQL.FieldByName('Units').AsString := cdsDrawingQuantityUnits.AsString;
        aqSQL.FieldByName('Quantity').AsCurrency := 0;
        aqSQL.FieldByName('DesignQuantity').AsCurrency := cdsDrawingQuantityDQuantity1.AsCurrency;
        aqSQL.FieldByName('DesignQuantity2').AsCurrency := cdsDrawingQuantityDQuantity2.AsCurrency;
        aqSQL.Post;
        Inc(i);
        AddProgressForm(1, '正在生成临时表数据：' + IntToStr(i));
        cdsDrawingQuantity.Next;
      end;
    end;
  finally
    CloseProgressForm;
  end;
end;  }

{
function TReportData.GetReportData(AReportName: string): Boolean;
var sSQL: string;
  procedure ExecSQL(ASQL: string);
  begin
    aqReport.Close;
    aqReport.SQL.Clear;
    aqReport.SQL.Add(ASQL);
    aqReport.Open;
  end;
begin
  Result := False;
  // 这里加冒号判断是因为防止“表1-2”匹配“表1”等类似的情况

  // 表1：分项工程量清单.fr3, 表2-2：工程项目清单(带细目).fr3
  if (Pos('表1：', AReportName) > 0) or (Pos('表2-2：', AReportName) > 0) then
    sSQL := 'select * from ReportTempTab order by SerialNo, ItemKind, OrderNo'
  // 表2-1：项目工程量清单(标准).fr3
  else if Pos('表2-1：', AReportName) > 0 then
    sSQL := 'select * from ReportTempTab where ItemKind <> 3 order by SerialNo, ItemKind, OrderNo'
  // 表3-1：工程量清单(标准).fr3
  else if Pos('表3-1：', AReportName) > 0 then
    sSQL := 'Select B_Code, Name, Units, B_CodeAlpha, Sum(Quantity2) as Quantity, ' +
            'Sum(TotalPrice2) as TotalPrice, IIF(Quantity=0, 0, TotalPrice / Quantity) as UnitPrice ' +
            'from (select B_Code, Name, Units, B_CodeAlpha, ' +
            '0 as Quantity2, Sum(TotalPrice) as TotalPrice2 ' +
            'from ReportTempTab where ItemKind = 2 ' +
            'Group by B_Code, Name, Units, B_CodeAlpha ' +
            'union all ' +
            'select B_Code, Name, Units, B_CodeAlpha, ' +
            'Sum(Quantity) as Quantity2, 0 as TotalPrice ' +
            'from ReportTempTab ' +
            'where ItemKind = 2 and SerialNo < (select SerialNo from ReportTempTab where ID = 2) ' +
            'Group by B_Code, Name, Units, B_CodeAlpha) ' +
            'Group by B_Code, Name, Units, B_CodeAlpha ' +
            'Order by B_CodeAlpha, Name'

  // 表3-2：工程量清单(带细目).fr3
  // 数据库中，清单级清单用Quantity，图纸工程量用DesignQuantity，是两个互斥字段，一者有值
  // 则另一者必定为0。相加是为了报表显示共用一个字段。
  else if Pos('表3-2：', AReportName) > 0 then
    sSQL := 'select B_Code, Name, Units, B_CodeAlpha, ItemKind, ' +
            'Sum(Quantity2) as Quantity, Sum(TotalPrice2) as TotalPrice, ' +
            'IIF(Quantity=0, 0, TotalPrice / Quantity) as UnitPrice ' +
            'from(select B_Code, Name, Units, B_CodeAlpha, ItemKind, ' +
            'Sum(Quantity + DesignQuantity) as Quantity2, 0 as TotalPrice2 ' +
            'from ReportTempTab where (B_CodeAlpha <> '''') and ' +
            'SerialNo < (select SerialNo from ReportTempTab where ID = 2) ' +
            'Group by B_Code, Name, Units, B_CodeAlpha, ItemKind ' +
            'union all ' +
            'select B_Code, Name, Units, B_CodeAlpha, ItemKind, ' +
            '0 as Quantity2, Sum(TotalPrice) as TotalPrice2 ' +
            'from ReportTempTab where B_CodeAlpha <> ''''' +
            'Group by B_Code, Name, Units, B_CodeAlpha, ItemKind) ' +
            'Group by B_Code, Name, Units, B_CodeAlpha, ItemKind ' +
            'Order by B_CodeAlpha, ItemKind'
  else
  // 未定义的报表，不处理
    Exit;

  ExecSQL(sSQL);
  Result := True;
end;   }

procedure TReportData.RefreshData;
begin
  with TProject(FProject) do
  begin
    BillsData.SaveSerialNo;
    ChangedByReport := True;
  //  CreateReportTempTab;
  end;
end;

end.