unit U_OrdImport;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, cxGraphics, cxCustomData, cxStyles, cxTL, cxMaskEdit, DB, ADODB,
  cxInplaceContainer, cxDBTL, cxControls, cxTLData, ComCtrls, ToolWin, StdCtrls,
  cxFilter, cxData, cxDataStorage, cxEdit, cxDBData, DBClient,
  cxGridCustomTableView, cxGridTableView, cxGridDBTableView, ExtCtrls,
  cxSplitter, cxGridLevel, cxClasses, cxGridCustomView, cxGrid,
  cxGridCustomPopupMenu, cxGridPopupMenu, RM_Dataset, RM_System, RM_Common,
  RM_Class, RM_GridReport, IdBaseComponent, IdComponent, IdTCPConnection,
  IdTCPClient, IdFTP, ShellAPI, IniFiles, cxCheckBox, cxCalendar, cxButtonEdit,
  cxTextEdit, cxDropDownEdit, cxPC, U_BaseHelp, cxLookAndFeels,
  cxLookAndFeelPainters, cxNavigator, dxDateRanges, dxBarBuiltInMenu, ComObj,
  U_BaseList, System.ImageList, Vcl.ImgList, dxScrollbarAnnotations,
  dxSkinsCore, dxSkinsDefaultPainters;

type
  TfrmOrdImport = class(TfrmBaseHelp)
    ToolBar1: TToolBar;
    TBRafresh: TToolButton;
    TBClose: TToolButton;
    ADOQueryTemp: TADOQuery;
    ADOQueryCmd: TADOQuery;
    ADO_1: TADOQuery;
    GPM_1: TcxGridPopupMenu;
    DS_1: TDataSource;
    CDS_1: TClientDataSet;
    cxStyleRepository1: TcxStyleRepository;
    cxStyle1: TcxStyle;
    OpenDialog1: TOpenDialog;
    ToolButton5: TToolButton;
    ToolButton1: TToolButton;
    ToolButton2: TToolButton;
    CDS_LM1: TClientDataSet;
    ToolButton3: TToolButton;
    cxgrdpmn1: TcxGridPopupMenu;
    CDS_2: TClientDataSet;
    DS_2: TDataSource;
    ADO_2: TADOQuery;
    CDS_LM2: TClientDataSet;
    cxGrid1: TcxGrid;
    Tv1: TcxGridDBTableView;
    v1OrdDate: TcxGridDBColumn;
    Tv1Column2: TcxGridDBColumn;
    Tv1Column4: TcxGridDBColumn;
    v1OrderNo: TcxGridDBColumn;
    v1DeliveryDate: TcxGridDBColumn;
    Tv1Column6: TcxGridDBColumn;
    Tv1Column1: TcxGridDBColumn;
    Tv1Column5: TcxGridDBColumn;
    Tv1Column8: TcxGridDBColumn;
    Tv1Column9: TcxGridDBColumn;
    cxGrid1Level1: TcxGridLevel;
    cxGrid2: TcxGrid;
    TV2: TcxGridDBTableView;
    cxGridDBColumn1: TcxGridDBColumn;
    v1PRTColor: TcxGridDBColumn;
    TV2Column4: TcxGridDBColumn;
    TV2Column3: TcxGridDBColumn;
    cxGridDBColumn4: TcxGridDBColumn;
    TV2Column1: TcxGridDBColumn;
    v1Column5: TcxGridDBColumn;
    VC_PRTPs: TcxGridDBColumn;
    v1PRTOrderQty: TcxGridDBColumn;
    v1OrderUnit: TcxGridDBColumn;
    cxGridDBColumn7: TcxGridDBColumn;
    cxGridLevel1: TcxGridLevel;
    TV2Column2: TcxGridDBColumn;
    TV2Column5: TcxGridDBColumn;
    TV2Column6: TcxGridDBColumn;
    TV2Column7: TcxGridDBColumn;
    TV2Column8: TcxGridDBColumn;
    TV2Column9: TcxGridDBColumn;
    TV2Column10: TcxGridDBColumn;
    TV2Column11: TcxGridDBColumn;
    TV2Column12: TcxGridDBColumn;
    TV2Column13: TcxGridDBColumn;
    TV2Column14: TcxGridDBColumn;
    Tv1Column3: TcxGridDBColumn;
    Tv1Column7: TcxGridDBColumn;
    TV2Column15: TcxGridDBColumn;
    TV2Column16: TcxGridDBColumn;
    TV2Column17: TcxGridDBColumn;
    TV2Column18: TcxGridDBColumn;
    Tv1Column10: TcxGridDBColumn;
    TV2Column19: TcxGridDBColumn;
    TV2Column20: TcxGridDBColumn;
    TV2Column21: TcxGridDBColumn;
    TV2Column22: TcxGridDBColumn;
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure TBCloseClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure TBRafreshClick(Sender: TObject);
    procedure ToolButton5Click(Sender: TObject);
    procedure ToolButton1Click(Sender: TObject);
    procedure ToolButton2Click(Sender: TObject);
    procedure ToolButton3Click(Sender: TObject);
  private
    { Private declarations }
    procedure InitGrid();
  public
    { Public declarations }
    FCTID: string;
  end;

var
  frmOrdImport: TfrmOrdImport;

implementation

uses
  U_DataLink, U_RTFun;

{$R *.dfm}

procedure TfrmOrdImport.InitGrid();
begin

  with ADO_1 do
  begin
    Close;
    SQL.Clear;
    sql.Add(' select A.*,B.Technics,B.FtyName,B.Follower from Trade_Plan_Main A left join Trade_Plan_Fty B on A.MainId=B.MainId where 2= 1 ');
    Open;
  end;
  SCreateCDS(ADO_1, CDS_1);
//  SInitCDSData(ADO_1, CDS_1);
  with ADO_2 do
  begin
    Close;
    SQL.Clear;
    sql.Add(' select * from Trade_Plan_Sub where 2= 1 ');
    Open;
  end;
  SCreateCDS(ADO_2, CDS_2);
//  SInitCDSData(ADO_2, CDS_2);

end;

procedure TfrmOrdImport.FormDestroy(Sender: TObject);
begin
  inherited;
  frmOrdImport := nil;
end;

procedure TfrmOrdImport.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  inherited;
  Action := cafree;

end;

procedure TfrmOrdImport.TBCloseClick(Sender: TObject);
begin

  Close;
end;

procedure TfrmOrdImport.FormShow(Sender: TObject);
begin
  inherited;
  ReadCxGrid(self.Caption + 'Tv1', Tv1, '合同管理');
  ReadCxGrid(self.Caption + 'Tv2', Tv2, '合同管理');
  with CDS_LM1 do
  begin
    FieldDefs.Clear;
    FieldDefs.Add('LXH', ftInteger, 0);
    FieldDefs.Add('lCode', ftString, 40);
    FieldDefs.Add('LName', ftString, 40);
    close;
    CreateDataSet;
  end;
  with CDS_LM2 do
  begin
    FieldDefs.Clear;
    FieldDefs.Add('LXH', ftInteger, 0);
    FieldDefs.Add('lCode', ftString, 40);
    FieldDefs.Add('LName', ftString, 40);
    close;
    CreateDataSet;
  end;
  InitGrid();
end;

procedure TfrmOrdImport.TBRafreshClick(Sender: TObject);
begin
  InitGrid();
end;

procedure TfrmOrdImport.ToolButton1Click(Sender: TObject);
begin
  WriteCxGrid(self.Caption + 'Tv1', Tv1, '合同管理');
  WriteCxGrid(self.Caption + 'Tv2', Tv2, '合同管理');
end;

procedure TfrmOrdImport.ToolButton2Click(Sender: TObject);
var
  maxno: string;
  hh: Integer;
begin
  hh := 0;
  try
    ADOQueryCmd.Connection.BeginTrans;
    if GetLSNo(ADOQueryTemp, maxno, 'DR', 'Trade_Plan_Main', 4, 0) = False then
      raise Exception.Create('取最大号失败！');
    CDS_1.DisableControls;
    CDS_2.DisableControls;

    with CDS_1 do
    begin
      First;
      while not eof do
      begin

        with ADOQueryCmd do
        begin
          Close;
          sql.Clear;
          sql.Add('select top 1 * from Trade_Plan_Main where OrderNo=' + quotedstr(Trim(CDS_1.fieldbyname('OrderNo').AsString)));
          open;
        end;
        if not ADOQueryCmd.IsEmpty then
          raise Exception.Create(PChar('订单号：' + Trim(ADOQueryCmd.fieldbyname('OrderNo').AsString) + ' 重复！'));
        with ADOQueryCmd do
        begin

          Append;
          FieldByName('Fillid').Value := Trim(Dcode);
          FieldByName('Filler').Value := Trim(DName);
          RTSetSaveDataCDS(ADOQueryCmd, Tv1, CDS_1, 'Trade_Plan_Main', 0);
          FieldByName('MainId').Value := Trim(CDS_1.fieldbyname('OrderNo').AsString);
          FieldByName('MasterOrderNo').Value := Trim(CDS_1.fieldbyname('OrderNo').AsString);
          FieldByName('status').Value := '0';
          Post;
        end;

        with ADOQueryCmd do
        begin
          Close;
          sql.Clear;
          sql.Add('select top 1 * from Trade_Plan_Fty where 1=2');
          open;
        end;
        with ADOQueryCmd do
        begin
          Append;
          RTSetSaveDataCDS(ADOQueryCmd, Tv1, CDS_1, 'Trade_Plan_Fty', 1);
          FieldByName('MainId').Value := Trim(CDS_1.fieldbyname('OrderNo').AsString);
          FieldByName('TPFId').Value := Trim(CDS_1.fieldbyname('OrderNo').AsString);
          Post;
        end;
        Next;
      end;
    end;

    with CDS_2 do
    begin
      First;
      hh := 1;
      while not eof do
      begin
        with ADOQueryCmd do
        begin
          Close;
          sql.Clear;
          sql.Add('select top 1 * from Trade_Plan_Sub where 1=2 ');
          open;
        end;
        with ADOQueryCmd do
        begin
          Append;
          RTSetSaveDataCDS(ADOQueryCmd, Tv2, CDS_2, 'Trade_Plan_Sub', 0);
          FieldByName('Subid').Value := maxno + inttostr(hh);
//          FieldByName('SerialNo').Value := hh;
          Post;
        end;
        hh := hh + 1;
        Next;
      end;
    end;

    ADOQueryCmd.Connection.CommitTrans;
    with ADOQueryCmd do
    begin
      Close;
      sql.Clear;
      sql.Add('exec P_BS_Com_UP_AllTable @UPType=''计划单号'' ');
      ExecSQL;
    end;
    CDS_2.EnableControls;
    CDS_1.EnableControls;
    application.MessageBox('提交成功', '提示');
    ModalResult := 1;
  except
    CDS_2.EnableControls;
    CDS_1.EnableControls;
    ADOQueryCmd.Connection.RollbackTrans;
    application.MessageBox(PChar(Exception(ExceptObject).Message), '提示信息', 0);
  end;
end;

procedure TfrmOrdImport.ToolButton3Click(Sender: TObject);
begin
  CDS_1.Delete;
end;

procedure TfrmOrdImport.ToolButton5Click(Sender: TObject);
var
  excelApp, WorkBook: Variant;
  i, j, k, LX, ExcelRowCount: integer;
  maxId, FCPID, FCPName, t1, t2, t3, FFID: string;
  conflag: integer;
begin

  try
    excelApp := CreateOleObject('Excel.Application');
    openDialog1.Filter := '*.CSV;*.xls';
    if opendialog1.Execute then
    begin
      WorkBook := excelApp.WorkBooks.Open(OpenDialog1.FileName);
    end
    else
      exit;
    excelApp.Visible := false;
    ExcelRowCount := WorkBook.WorkSheets[1].UsedRange.Rows.Count;
  except
    application.MessageBox('加载EXCEL错误！', '错误信息', MB_ICONERROR);
    exit;
  end;

  CDS_LM1.EmptyDataSet;

  for j := 0 to Tv1.ColumnCount - 1 do
  begin

    with CDS_LM1 do
    begin
      Append;
      FieldByName('LCode').Value := trim(Tv1.Columns[j].DataBinding.FieldName);
      FieldByName('LName').Value := trim(Tv1.Columns[j].Caption);
      Post;
    end;
  end;
  try
    for i := 1 to 50 do
    begin
      if trim(WorkBook.WorkSheets[1].Cells[1, i].value) = '' then
        continue;

      if trim(WorkBook.WorkSheets[1].Cells[1, i].value) = '订单号' then
      begin
        conflag := i;
      end;

      if CDS_LM1.Locate('LName', trim(WorkBook.WorkSheets[1].Cells[1, i].value), []) then
      begin
        with CDS_LM1 do
        begin
          Edit;
          FieldByName('LXH').Value := i;
          Post;
        end;
      end;
    end;
  except
    application.MessageBox('加载数组错误！', '错误信息', MB_ICONERROR);
    exit;
  end;
  if conflag = 0 then
  begin
    application.MessageBox('未检测到订单号！', '错误信息', MB_ICONERROR);
    exit;
  end;

  CDS_LM2.EmptyDataSet;
  for j := 0 to Tv2.ColumnCount - 1 do
  begin
    if not CDS_LM2.Locate('LName', trim(Tv2.Columns[j].Caption), []) then
    begin
      with CDS_LM2 do
      begin
        Append;
        FieldByName('LCode').Value := trim(Tv2.Columns[j].DataBinding.FieldName);
        FieldByName('LName').Value := trim(Tv2.Columns[j].Caption);
        Post;
      end;
    end;
  end;
  try
    for i := 1 to 50 do
    begin
      if trim(WorkBook.WorkSheets[1].Cells[1, i].value) = '' then
        continue;
      if CDS_LM2.Locate('LName', trim(WorkBook.WorkSheets[1].Cells[1, i].value), []) then
      begin
        with CDS_LM2 do
        begin
          Edit;
          FieldByName('LXH').Value := i;
          Post;
        end;
      end;
    end;
  except
    application.MessageBox('加载数组错误！', '错误信息', MB_ICONERROR);
    exit;
  end;

  try
    for i := 2 to ExcelRowCount do
    begin
      if trim(WorkBook.WorkSheets[1].Cells[i, conflag].Value) <> '' then
      begin
        if not CDS_1.Locate('OrderNo', WorkBook.WorkSheets[1].Cells[i, conflag].Value, []) then
        begin
          with CDS_1 do
          begin
            Append;
            CDS_LM1.First;
            while not CDS_LM1.Eof do
            begin
              if CDS_LM1.FieldByName('LXH').AsInteger > 0 then
                CDS_1.fieldbyname(CDS_LM1.FieldByName('LCode').AsString).Value := WorkBook.WorkSheets[1].Cells[i, CDS_LM1.FieldByName('LXH').AsInteger].Value;
              CDS_LM1.Next;
            end;
            Post;
          end;
        end;
      end;
    end;

    for i := 2 to ExcelRowCount do
    begin
      if trim(WorkBook.WorkSheets[1].Cells[i, conflag].Value) <> '' then
      begin
        with CDS_2 do
        begin
          Append;
          CDS_LM2.First;
          while not CDS_LM2.Eof do
          begin
            if CDS_LM2.FieldByName('LXH').AsInteger > 0 then
              CDS_2.fieldbyname(CDS_LM2.FieldByName('LCode').AsString).Value := WorkBook.WorkSheets[1].Cells[i, CDS_LM2.FieldByName('LXH').AsInteger].Value;
            CDS_LM2.Next;
          end;
          Post;
        end;
      end;
    end;

    WorkBook.Close;
    excelApp.Quit;
    excelApp := Unassigned;
    WorkBook := Unassigned;
  except
    WorkBook.Close;
    excelApp.Quit;
    excelApp := Unassigned;
    WorkBook := Unassigned;
    exit;
  end;

end;

end.