Measure/Units/ExcelImport_Bills.pas

unit ExcelImport_Bills;

// 导入0号台账/分项清单

interface

uses
  DetailExcelImport, Classes, MCacheTree, sdDB, ProjectData;

type
  Tdei_CustomBills = class(TDetailExcelImport)
  private
    FCacheTree: TBillsCacheTree;
    FCurRow: Integer;
    FIsFirstPart: Boolean;

    FWithLevelCode: Boolean;
    FWithoutGclBills: Boolean;

    FBaseTree: TBillsCacheTree;
    FFixedIDNodes: TList;

    FCodeCol: Integer;
    FB_CodeCol: Integer;
    FNameCol: Integer;
    FUnitsCol: Integer;
    FPriceCol: Integer;
    FOrgQuantityCol: Integer;
    FMisQuantityCol: Integer;
    FOthQuantityCol: Integer;
    FDgnQuantity1Col: Integer;
    FDgnQuantity2Col: Integer;
    FDrawingCol: Integer;
    FMemoCol: Integer;
    FLevelCol: Integer;
  protected
    procedure BeginImport; override;
    procedure EndImport; override;

    function GetFixedIDNode(AID: Integer): TBillsCacheNode;
    function GetFixedID(ACode, AName: string): Integer;

    procedure CheckFixedIDNodes;

    procedure LoadColumnsFromHead;

    procedure LoadBaseTree(ATree: TBillsCacheTree);

    procedure LoadNode;
    procedure LoadNodes;

    procedure WriteNode(ADataSet: TsdDataSet; ANode: TBillsCacheNode);
    procedure WriteNodes(ADataSet: TsdDataSet);

    procedure Import; override;
  public
    procedure ImportFile(const AFileName: string; AWithLevelCode, AWithoutGclBills: Boolean); overload;
  end;

implementation

uses
  Forms, Controls, ProgressHintFrm, ConstUnit, UtilMethods, ExcelImport,
  SysUtils, mDataRecord, XLSAdapter;

{ Tdei_CustomBills }

procedure Tdei_CustomBills.BeginImport;
begin
  Screen.Cursor := crHourGlass;
  ShowProgressHint('导入Excel数据', 100, '读取Excel数据', 100);

  FCurRow := 1;
  FIsFirstPart := True;
  FCacheTree := TBillsCacheTree.Create;
  FCacheTree.NewNodeID := 101;
  // 以层次编号为依据，分隔用'.'，以项目节、清单编号为依据，分隔用'-'
  if FWithLevelCode then
    FCacheTree.SeparateChar := '.'
  else
    FCacheTree.SeparateChar := '-';
  FCacheTree.AutoSort := True;

  ProjectData.DisConnectTree;
  ProjectData.BillsData.DisableEvents;

  FBaseTree := TBillsCacheTree.Create;
  FBaseTree.NewNodeID := 101;
  FBaseTree.SeparateChar := '.';
  FFixedIDNodes := TList.Create;
end;

procedure Tdei_CustomBills.CheckFixedIDNodes;

  function GetHintStr(ANode: TBillsCacheNode): string;
  begin
    Result := '';
    if ANode.Code <> '' then
      Result := Result + '编号：' + ANode.Code + '；';
    if ANode.Name <> '' then
      Result := Result + '名称：' + ANode.Name + '；';
  end;

  function GetInvalidModel(ANoPM: Boolean; ACount: Integer): string;
  begin
    if ANoPM then
    begin
      if ACount > 1 then
        Result := '价差功能，部分报表'
      else
        Result := '价差功能'
    end
    else
      Result := '部分报表';
    Result := Result + '将不可使用' + #13#10 + '如有疑问，请联系纵横客服，企业QQ：800003850 电话：(0756)3850888';
  end;

var
  sgs: TStrings;
  iBase: Integer;
  vBase, vImport: TBillsCacheNode;
  sHint: string;
  bNoPM: Boolean;
begin
  bNoPM := False;
  sgs := TStringList.Create;
  try
    sgs.Add('缺少如下固定清单节点：');
    for iBase := 0 to FBaseTree.FixedIDNodes.Count - 1 do
    begin
      vBase := TBillsCacheNode(FBaseTree.FixedIDNodes.Items[iBase]);

      vImport := FCacheTree.FindFixedIDNode(vBase.ID);
      if not Assigned(vImport) then
      begin
        sgs.Add(GetHintStr(vBase));
        if vBase.ID = iPriceMarginID then
          bNoPM := True;
      end;
    end;
  finally
    if sgs.Count > 1 then
    begin
      sgs.Add(GetInvalidModel(bNoPM, sgs.Count - 1));
      WarningMessage(sgs.Text);
    end;
    sgs.Free;
  end;
end;

procedure Tdei_CustomBills.EndImport;
begin
  // For Test
  // FCacheTree.SaveTreeToFile('E:\Tree.txt');

  if FWithLevelCode then
    CheckFixedIDNodes;

  FFixedIDNodes.Free;
  FBaseTree.Free;
  ProjectData.BillsData.EnableEvents;
  ProjectData.ReConnectTree;
  FCacheTree.Free;
  ProjectData.BillsCompileData.CalculateAll;
  CloseProgressHint;
  Screen.Cursor := crDefault;
end;

function Tdei_CustomBills.GetFixedID(ACode, AName: string): Integer;
var
  i: Integer;
  Node: TBillsCacheNode;
begin
  Result := -1;
  for i := 0 to FBaseTree.CacheNodes.Count - 1 do
  begin
    Node := TBillsCacheNode(FBaseTree.CacheNodes.Items[i]);
    if (Node.Code = ACode) and (Node.Name = AName) then
    begin
      if Node.ID < 100 then
        Result := Node.ID;
      Break;
    end;
  end;
end;

function Tdei_CustomBills.GetFixedIDNode(AID: Integer): TBillsCacheNode;
var
  i: Integer;
  Node: TBillsCacheNode;
begin
  Result := nil;
  for i := 0 to FFixedIDNodes.Count - 1 do
  begin
    Node := TBillsCacheNode(FFixedIDNodes.Items[i]);
    if (AID = Node.ID) then
    begin
      Result := Node;
      Break;
    end;
  end;
end;

procedure Tdei_CustomBills.Import;
begin
  if FWithLevelCode then
    LoadBaseTree(FBaseTree)
  else
    LoadBaseTree(FCacheTree);
  LoadColumnsFromHead;
  LoadNodes;
  WriteNodes(ProjectData.BillsData.sddBills);
end;

procedure Tdei_CustomBills.ImportFile(const AFileName: string;
  AWithLevelCode, AWithoutGclBills: Boolean);
begin
  FWithLevelCode := AWithLevelCode;
  FWithoutGclBills := AWithoutGclBills;
  ImportFile(AFileName);
end;

procedure Tdei_CustomBills.LoadBaseTree(ATree: TBillsCacheTree);
var
  BaseImportor: TBillsExcelImport;
begin
  BaseImportor := TBillsExcelImport.Create(nil);
  try
    BaseImportor.ImportToTree(ATree, GetTemplateBillsFileName);
  finally
    BaseImportor.Free;
  end;
end;

procedure Tdei_CustomBills.LoadColumnsFromHead;
var
  iCol: Integer;
  sColName: string;
begin
  FCodeCol := -1;
  FB_CodeCol := -1;
  FNameCol := -1;
  FUnitsCol := -1;
  FPriceCol := -1;
  FOrgQuantityCol := -1;
  FMisQuantityCol := -1;
  FOthQuantityCol := -1;
  FDgnQuantity1Col := -1;
  FDgnQuantity2Col := -1;
  FDrawingCol := -1;
  FMemoCol := -1;
  FLevelCol := -1;

  for iCol := 1 to Excel.XlsFile.MaxCol do
  begin
    sColName := GetCellTrimStr(Excel.XlsFile, FCurRow, iCol);

    if Pos('项目节', sColName) > 0 then
      FCodeCol := iCol
    else if (sColName = '清单编号') or (Pos('子目号', sColName) > 0) then
      FB_CodeCol := iCol
    else if sColName = '名称' then
      FNameCol := iCol
    else if sColName = '单位' then
      FUnitsCol := iCol
    else if sColName = '单价' then
      FPriceCol := iCol
    else if (sColName = '清单数量') or (sColName = '工程量') or (sColName = '数量') or (sColName = '施工图数量') then
      FOrgQuantityCol := iCol
    else if (sColName = '设计错漏数量') then
      FMisQuantityCol := iCol
    else if (sColName = '其他错漏数量') then
      FOthQuantityCol := iCol
    else if (sColName = '设计数量1') or (sColName = '数量1') then
      FDgnQuantity1Col := iCol
    else if (sColName = '设计数量2') or (sColName = '数量2') then
      FDgnQuantity2Col := iCol
    else if sColName = '图号' then
      FDrawingCol := iCol
    else if sColName = '备注' then
      FMemoCol := iCol
    else if sColName = '层次编号' then
      FLevelCol := iCol;
  end;
  Inc(FCurRow);
end;

procedure Tdei_CustomBills.LoadNode;
var
  sLevelCode, sCode, sB_Code, sName: string;
  Node: TBillsCacheNode;
  vValue: Variant;
  iFixedID: Integer;
begin
  sLevelCode := GetCellTrimStr(Excel.XlsFile, FCurRow, FLevelCol);
  sCode := GetCellTrimStr(Excel.XlsFile, FCurRow, FCodeCol);
  sB_Code := GetCellTrimStr(Excel.XlsFile, FCurRow, FB_CodeCol);
  sName := GetCellTrimStr(Excel.XlsFile, FCurRow, FNameCol);

  // 含层次编号时，层次编号为空不导入
  // 不含层次编号时，仅导入第一部分，且项目节编号、清单编号均未空时不导入
  if FWithLevelCode then
  begin
    if sLevelCode = '' then Exit;
  end
  else
  begin
    if ((sCode = '') and (sB_Code = '')) or SameText(sCode, '2') or
        (Pos('第二部分', sName) > 0) then
    begin
      FIsFirstPart := False;
      Exit;
    end;
  end;

  if (sCode = '') and FWithoutGclBills then Exit;

  // 含层次编号时，以层次编号为依据新增节点；反之以项目节编号为依据新增节点
  if not FWithLevelCode then
  begin
    if (sCode <> '') then
      Node := FCacheTree.AddNodeByCode(sCode, -1)
    else
      Node := FCacheTree.AddLeafBillsNode(sB_Code);
  end
  else
  begin
    // 1. 从模板树中查询当前节点是否为固定ID，否则为-1
    iFixedID := GetFixedID(sCode, sName);
    // 2. 从导入树中查询是否添加过该固定ID，防止存在两个固定ID节点主键冲突
    //    如果已添加过固定ID节点，则其他节点未非固定ID节点
    Node := GetFixedIDNode(iFixedID);
    if Assigned(Node) then
      iFixedID := -1;
    // 3. 添加当前节点
    Node := FCacheTree.AddNodeByCode(sLevelCode, iFixedID);
    // 4. 如果当前添加的节点为固定ID节点，则添加到List中便于2快速查找
    if Node.ID < 100 then
      FFixedIDNodes.Add(Node);
  end;

  Node.Code := sCode;
  Node.B_Code := sB_Code;
  Node.Name := sName;
  Node.Units := GetCellTrimStr(Excel.XlsFile, FCurRow, FUnitsCol);
  Node.Price := GetCellFloat(Excel.XlsFile, FCurRow, FPriceCol);
  Node.OrgQuantity := GetCellFloat(Excel.XlsFile, FCurRow, FOrgQuantityCol);
  Node.MisQuantity := GetCellFloat(Excel.XlsFile, FCurRow, FMisQuantityCol);
  Node.OthQuantity := GetCellFloat(Excel.XlsFile, FCurRow, FOthQuantityCol);
  Node.DgnQuantity1 := GetCellFloat(Excel.XlsFile, FCurRow, FDgnQuantity1Col);
  Node.DgnQuantity2 := GetCellFloat(Excel.XlsFile, FCurRow, FDgnQuantity2Col);
  Node.DrawingCode := GetCellTrimStr(Excel.XlsFile, FCurRow, FDrawingCol);
  Node.MemoStr := GetCellTrimStr(Excel.XlsFile, FCurRow, FMemoCol);
end;

procedure Tdei_CustomBills.LoadNodes;
var
  iPos, iSubPos: Integer;
begin
  while (FCurRow <= Excel.XlsFile.MaxRow){ and FIsFirstPart }do
  begin
    LoadNode;
    Inc(FCurRow);
    iSubPos := FCurRow * 100 div Excel.XlsFile.MaxRow;
    iPos := iSubPos div 2;
    UpdateProgressPosition(iPos, iSubPos);
  end;
end;

procedure Tdei_CustomBills.WriteNode(ADataSet: TsdDataSet;
  ANode: TBillsCacheNode);
var
  Rec: TBillsRecord;
begin
  if ANode.Code <> '' then
    UpdateProgressHint('写入读取的Excel数据 ' + ANode.Code)
  else if ANode.B_Code <> '' then
    UpdateProgressHint('写入读取的Excel数据 ' + ANode.B_Code)
  else
    UpdateProgressHint('写入读取的Excel数据 ' + ANode.Name);

  Rec := TBillsRecord(ADataSet.Add);
  Rec.ID.AsInteger := ANode.ID;
  Rec.ParentID.AsInteger := ANode.ParentID;
  Rec.NextSiblingID.AsInteger := ANode.NextSiblingID;
  Rec.Code.AsString := ANode.Code;
  Rec.B_Code.AsString := ANode.B_Code;
  Rec.Name.AsString := ANode.Name;
  Rec.Units.AsString := ANode.Units;
  Rec.Price.AsFloat := PriceRoundTo(ANode.Price);
  Rec.OrgQuantity.AsFloat := QuantityRoundTo(ANode.OrgQuantity);
  Rec.MisQuantity.AsFloat := QuantityRoundTo(ANode.MisQuantity);
  Rec.OthQuantity.AsFloat := QuantityRoundTo(ANode.OthQuantity);
  Rec.DgnQuantity1.AsFloat := QuantityRoundTo(ANode.DgnQuantity1);
  Rec.DgnQuantity2.AsFloat := QuantityRoundTo(ANode.DgnQuantity2);
  Rec.DrawingCode.AsString := ANode.DrawingCode;
  Rec.MemoStr.AsString := ANode.MemoStr;
end;

procedure Tdei_CustomBills.WriteNodes(ADataSet: TsdDataSet);
var
  i, iPos, iSubPos: Integer;
begin
  UpdateProgressHint('写入读取的Excel数据', True);
  UpdateProgressPosition(50, 0);
  ADataSet.DeleteAll;
  for i := 0 to FCacheTree.CacheNodes.Count - 1 do
  begin
    WriteNode(ADataSet, TBillsCacheNode(FCacheTree.CacheNodes[i]));
    iSubPos := i*100 div FCacheTree.CacheNodes.Count;
    iPos := 50 + iSubPos div 2;
    UpdateProgressPosition(iPos, iSubPos);
  end;
  UpdateProgressPosition(100, 100);
end;

end.