unit U_ReceivableImport;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, ToolWin, cxStyles, cxCustomData, cxGraphics, cxFilter,
  cxData, cxDataStorage, cxEdit, DB, cxDBData, cxGridLevel, cxClasses,
  cxControls, cxGridCustomView, cxGridCustomTableView, cxGridTableView,
  cxGridDBTableView, cxGrid, cxGridCustomPopupMenu, cxGridPopupMenu, ADODB,
  DBClient, ExtCtrls, StdCtrls, BtnEdit, cxButtonEdit, cxTextEdit,
  cxDropDownEdit, cxLookAndFeels, cxLookAndFeelPainters, cxNavigator, cxCalendar,
  dxSkinsCore, dxBarBuiltInMenu, ComObj, cxCheckBox, dxSkinBlack,
  dxSkinBlue, dxSkinBlueprint, dxSkinCaramel, dxSkinCoffee, dxSkinDarkRoom,
  dxSkinDarkSide, dxSkinDevExpressDarkStyle, dxSkinDevExpressStyle,
  dxSkinFoggy, dxSkinGlassOceans, dxSkinHighContrast, dxSkiniMaginary,
  dxSkinLilian, dxSkinLiquidSky, dxSkinLondonLiquidSky, dxSkinMcSkin,
  dxSkinMetropolis, dxSkinMetropolisDark, dxSkinMoneyTwins,
  dxSkinOffice2007Black, dxSkinOffice2007Blue, dxSkinOffice2007Green,
  dxSkinOffice2007Pink, dxSkinOffice2007Silver, dxSkinOffice2010Black,
  dxSkinOffice2010Blue, dxSkinOffice2010Silver, dxSkinOffice2013DarkGray,
  dxSkinOffice2013LightGray, dxSkinOffice2013White, dxSkinPumpkin,
  dxSkinSeven, dxSkinSevenClassic, dxSkinSharp, dxSkinSharpPlus,
  dxSkinSilver, dxSkinSpringTime, dxSkinStardust, dxSkinSummer2008,
  dxSkinTheAsphaltWorld, dxSkinsDefaultPainters, dxSkinValentine,
  dxSkinVS2010, dxSkinWhiteprint, dxSkinXmas2008Blue, dxSkinscxPCPainter;

type
  TfrmReceivableImport = class(TForm)
    DS_1: TDataSource;
    CDS_Import: TClientDataSet;
    GPM_1: TcxGridPopupMenu;
    ToolBar1: TToolBar;
    TSave: TToolButton;
    TBClose: TToolButton;
    ADOQueryTmp: TADOQuery;
    ADOQueryCmd: TADOQuery;
    ToolButton2: TToolButton;
    ToolButton4: TToolButton;
    OpenDialog1: TOpenDialog;
    CDS_LM: TClientDataSet;
    cxGrid1: TcxGrid;
    Tv1: TcxGridDBTableView;
    v1InvoiceNo: TcxGridDBColumn;
    v1InvoiceDate: TcxGridDBColumn;
    v1InvoiceType: TcxGridDBColumn;
    v1FactoryName: TcxGridDBColumn;
    v1Column1: TcxGridDBColumn;
    v1Column2: TcxGridDBColumn;
    v1Column3: TcxGridDBColumn;
    v1P_CodeName: TcxGridDBColumn;
    v1P_Spec: TcxGridDBColumn;
    v1ToFactoryName: TcxGridDBColumn;
    v1Qty: TcxGridDBColumn;
    v1Price: TcxGridDBColumn;
    v1QtyUnit: TcxGridDBColumn;
    v1Money: TcxGridDBColumn;
    v1PriceUnit: TcxGridDBColumn;
    v1Column4: TcxGridDBColumn;
    v1Column5: TcxGridDBColumn;
    v1note: TcxGridDBColumn;
    cxGridLevel1: TcxGridLevel;
    Tv1Column1: TcxGridDBColumn;
    cxGridPopupMenu1: TcxGridPopupMenu;
    procedure TBCloseClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure TSaveClick(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure ToolButton2Click(Sender: TObject);
    procedure ToolButton3Click(Sender: TObject);
    procedure ToolButton4Click(Sender: TObject);
    procedure v1InvoiceTypePropertiesButtonClick(Sender: TObject; AButtonIndex: Integer);
    procedure v1FactoryNamePropertiesButtonClick(Sender: TObject; AButtonIndex: Integer);
    procedure v1Column1PropertiesButtonClick(Sender: TObject; AButtonIndex: Integer);
    procedure v1Column2PropertiesButtonClick(Sender: TObject; AButtonIndex: Integer);
    procedure v1QtyPropertiesEditValueChanged(Sender: TObject);
    procedure v1MoneyPropertiesEditValueChanged(Sender: TObject);
    procedure v1Column4PropertiesEditValueChanged(Sender: TObject);
  private
    procedure initGrid();
    procedure SaveDate();
    { Private declarations }
  public
    FFFID: string;
    fInvoiceFlag: string;
    { Public declarations }
  end;

var
  frmReceivableImport: TfrmReceivableImport;

implementation

uses
  U_Fun, U_ZDYHelp, U_dataLink, U_ZdyAttachGYS;
{$R *.dfm}

procedure TfrmReceivableImport.SaveDate();
var
  MaxNo, FInvoiceID: string;
  XH: Integer;
begin
  ADOQueryCmd.Connection.BeginTrans;
  try
    with CDS_Import do
    begin
      first;
      XH := 1;
      if GetLSNo(ADOQueryTmp, MaxNo, 'DR', 'FD_Invoice', 4, 1) = False then
      begin
        Application.MessageBox('取最大号失败！', '提示', 0);
        Exit;
      end;

      while not eof do
      begin

        with ADOQueryCmd do
        begin
          close;
          sql.Clear;
          sql.add('select * from FD_Invoice where InvoiceNo=''' + trim(CDS_Import.fieldbyname('InvoiceNo').AsString) + '''');
          open;
          if isempty then
          begin
            append;
            ADOQueryCmd.FieldByName('Filler').Value := Trim(DName);
            ADOQueryCmd.FieldByName('Filltime').Value := SGetServerDateTime(ADOQueryTmp);
          end
          else
            edit;

          SSetSaveDataCDSNew(ADOQueryCmd, Tv1, CDS_Import, 'FD_Invoice', 1);
          fieldbyname('Invoiceflag').Value := strtoint('0');
          fieldbyname('chkstatus').AsString := '0';
          fieldbyname('ishd').Value := '未核对';
          fieldbyname('MainID').AsString := MaxNo + '-' + IntToStr(XH);
          post;
        end;
        XH := XH + 1;
        next;
      end;

      first;
      while not eof do
      begin

        if GetLSNo(ADOQueryTmp, FInvoiceID, 'FP', 'FD_InvoiceSub', 4, 1) = False then
        begin
          Application.MessageBox('取最大号失败！', '提示', 0);
          Exit;
        end;

        with ADOQueryCmd do
        begin
          close;
          sql.Clear;
          sql.add('select * from FD_InvoiceSub where InvoiceNo=''' + trim(CDS_Import.fieldbyname('InvoiceNo').AsString) + ''' ');
          sql.add('and InvoiceID=''' + trim(FInvoiceID) + '''');
          open;
        end;
        if ADOQueryCmd.IsEmpty then
        begin
          ADOQueryCmd.append;
        end
        else
        begin
          ADOQueryCmd.edit;
        end;

        ADOQueryCmd.fieldbyname('InvoiceNo').Value := trim(CDS_Import.fieldbyname('InvoiceNo').AsString);
        ADOQueryCmd.fieldbyname('InvoiceID').Value := FInvoiceID;
        SSetSaveDataCDSNew(ADOQueryCmd, Tv1, CDS_Import, 'FD_InvoiceSub', 2);
        ADOQueryCmd.fieldbyname('Qty').Value := CDS_Import.fieldbyname('Qty').AsFloat;
        ADOQueryCmd.fieldbyname('Price').Value := CDS_Import.fieldbyname('Price').AsFloat;
        ADOQueryCmd.fieldbyname('Money').Value := CDS_Import.fieldbyname('Money').AsFloat;
        ADOQueryCmd.fieldbyname('tax').Value := CDS_Import.fieldbyname('tax').AsFloat;
        ADOQueryCmd.fieldbyname('TaxMoney').Value := CDS_Import.fieldbyname('TaxMoney').AsFloat;
        ADOQueryCmd.Post;
        next;

      end;
    end;
    ADOQueryCmd.Connection.CommitTrans;

//    with ADOQueryCmd do
//    begin
//      close;
//      sql.Clear;
//      sql.add('delete from FD_InvoiceSub where InvoiceNo=''' + trim(CDS_Import.fieldbyname('InvoiceNo').AsString) + ''' ');
//      execsql;
//    end;

    application.MessageBox('保存成功！', '提示');
    Modalresult := 1;
  except
    ADOQueryCmd.Connection.RollbackTrans;
    application.MessageBox(PChar(Exception(ExceptObject).Message), '提示信息', 0);
  end;

end;

procedure TfrmReceivableImport.initGrid();
begin
  with ADOQueryTmp do
  begin
    close;
    sql.Clear;
    sql.Add('select *  from FD_Invoice A inner join FD_Invoicesub B on B.InvoiceNo=A.InvoiceNo where 1<>1');
    open;
  end;

  SCreateCDS20(ADOQueryTmp, CDS_Import);
  SInitCDSData20(ADOQueryTmp, CDS_Import);
end;

procedure TfrmReceivableImport.TBCloseClick(Sender: TObject);
begin
  writeCxGrid(Self.Caption, Tv1, '发票导入');
  Close;
end;

procedure TfrmReceivableImport.FormShow(Sender: TObject);
begin
  ReadCxGrid(Self.Caption, Tv1, '发票导入');
  with CDS_LM 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 TfrmReceivableImport.TSaveClick(Sender: TObject);
begin
  if CDS_Import.Locate('InvoiceNo', null, []) then
  begin
    application.MessageBox('发票号码不能为空', '提示');
    exit;
  end;

  if CDS_Import.Locate('InvoiceNo', '', []) then
  begin
    application.MessageBox('发票号码不能为空', '提示');
    exit;
  end;

  if CDS_Import.Locate('ToFactoryName', null, []) then
  begin
    application.MessageBox('对方单位不能为空', '提示');
    exit;
  end;

  if CDS_Import.Locate('ToFactoryName', '', []) then
  begin
    application.MessageBox('对方单位不能为空', '提示');
    exit;
  end;

  if CDS_Import.Locate('Money', null, []) then
  begin
    application.MessageBox('金额不能为空', '提示');
    exit;
  end;

  if CDS_Import.Locate('Money', 0, []) then
  begin
    application.MessageBox('金额不能为0', '提示');
    exit;
  end;

  if CDS_Import.Locate('taxMoney', null, []) then
  begin
    application.MessageBox('含税金额不能为空', '提示');
    exit;
  end;

  if CDS_Import.Locate('taxMoney', 0, []) then
  begin
    application.MessageBox('含税金额不能为0', '提示');
    exit;
  end;

  SaveDate();
end;

procedure TfrmReceivableImport.FormDestroy(Sender: TObject);
begin
  frmReceivableImport := nil;
end;

procedure TfrmReceivableImport.ToolButton2Click(Sender: TObject);
begin
  if CDS_Import.IsEmpty then
    exit;
  CDS_Import.delete;
end;

procedure TfrmReceivableImport.ToolButton3Click(Sender: TObject);
begin
  WriteCxGrid(Self.Caption, Tv1, '璐㈠姟绠＄悊');
end;

procedure TfrmReceivableImport.ToolButton4Click(Sender: TObject);
var
  excelApp, WorkBook: Variant;
  i, j, k, LX, ExcelRowCount: integer;
  maxId, FCPID, FCPName, t1, t2, t3, FFID: string;
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_LM.EmptyDataSet;

  for j := 0 to Tv1.ColumnCount - 1 do
  begin
    with CDS_LM 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 CDS_LM.Locate('LName', trim(WorkBook.WorkSheets[1].Cells[1, i].value), []) then
      begin
        with CDS_LM 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
      with CDS_Import do
      begin
        Append;
        CDS_LM.First;
        while not CDS_LM.Eof do
        begin
          if CDS_LM.FieldByName('LXH').AsInteger > 0 then
            CDS_Import.fieldbyname(CDS_LM.FieldByName('LCode').AsString).Value := WorkBook.WorkSheets[1].Cells[i, CDS_LM.FieldByName('LXH').AsInteger].Value;
          CDS_LM.Next;
        end;
//        CDS_Import.fieldbyname('OurCoNo').Value := '91330602MA2JTEXX4B';
//        CDS_Import.fieldbyname('OurCoName').Value := '圆中圆';
        Post;
      end;
    end;
    WorkBook.Close;
    excelApp.Quit;
    excelApp := Unassigned;
    WorkBook := Unassigned;
  except
    WorkBook.Close;
    excelApp.Quit;
    excelApp := Unassigned;
    WorkBook := Unassigned;
    exit;
  end;

end;

procedure TfrmReceivableImport.v1InvoiceTypePropertiesButtonClick(Sender: TObject; AButtonIndex: Integer);
begin
  try
    frmZDYHelp := TfrmZDYHelp.Create(Application);
    with frmZDYHelp do
    begin
      flag := 'InvoiceType';
      flagname := '发票类型';
      if ShowModal = 1 then
      begin
        Self.CDS_Import.Edit;
        Self.CDS_Import.FieldByName('InvoiceType').Value := Trim(ClientDataSet1.fieldbyname('ZDYName').AsString);
      end;
    end;
  finally
    frmZDYHelp.Free;
  end;
end;

procedure TfrmReceivableImport.v1FactoryNamePropertiesButtonClick(Sender: TObject; AButtonIndex: Integer);
begin
  try
    frmZDYHelp := TfrmZDYHelp.Create(Application);
    with frmZDYHelp do
    begin
      flag := 'DFTaiTou';
      flagname := '开票单位';
      if ShowModal = 1 then
      begin
        Self.CDS_Import.Edit;
        Self.CDS_Import.FieldByName('FactoryName').Value := Trim(ClientDataSet1.fieldbyname('ZDYName').AsString);
      end;
    end;
  finally
    frmZDYHelp.Free;
  end;
end;

procedure TfrmReceivableImport.v1Column1PropertiesButtonClick(Sender: TObject; AButtonIndex: Integer);
begin
  try
    frmZdyAttachGYS := TfrmZdyAttachGYS.Create(Application);
    with frmZdyAttachGYS do
    begin
      if ShowModal = 1 then
      begin
        Self.CDS_Import.Edit;
        Self.CDS_Import.FieldByName('TOFactoryName').Value := Trim(CDS_HZ.fieldbyname('ZdyName').AsString);
        Self.CDS_Import.FieldByName('FactoryName').Value := Trim(CDS_HZ.fieldbyname('ZdyName').AsString);
      end;
    end;
  finally
    frmZdyAttachGYS.Free;
  end;
end;

procedure TfrmReceivableImport.v1Column2PropertiesButtonClick(Sender: TObject; AButtonIndex: Integer);
begin
  try
    frmZDYHelp := TfrmZDYHelp.Create(Application);
    with frmZDYHelp do
    begin
      flag := 'SYRName';
      flagname := '公司抬头';
      if Trim(flag) = 'SYRName' then
      begin
        flag := 'OrdDefStr2';
        V1Name.Caption := '中文名称';
        V1Note.Caption := '英文名称';
        fnote := True;
      end;
      if ShowModal = 1 then
      begin
        Self.CDS_Import.Edit;
        Self.CDS_Import.FieldByName('SYRName').Value := Trim(ClientDataSet1.fieldbyname('ZdyName').AsString);
      end;
    end;
  finally
    frmZDYHelp.Free;
  end;
end;

procedure TfrmReceivableImport.v1QtyPropertiesEditValueChanged(Sender: TObject);
var
  mvalue, FFieldName: string;
begin
  mvalue := TcxTextEdit(Sender).EditingText;
  if Trim(mvalue) = '' then
  begin
    mvalue := '0';
  end;
  FFieldName := Trim(Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName);
  with CDS_Import do
  begin
    Edit;
    FieldByName(FFieldName).Value := Trim(mvalue);
    FieldbyName('Money').Value := format('%.2f', [FieldByName('Qty').AsFloat * FieldByName('Price').AsFloat]);
    FieldbyName('taxMoney').Value := format('%.2f', [FieldByName('money').AsFloat * (1 + FieldByName('tax').AsFloat / 100.00)]);
    Post;
  end;
  tv1.Controller.EditingController.ShowEdit();

end;

procedure TfrmReceivableImport.v1MoneyPropertiesEditValueChanged(Sender: TObject);
var
  mvalue, FFieldName: string;
begin
  mvalue := TcxTextEdit(Sender).EditingText;
  if Trim(mvalue) = '' then
  begin
    mvalue := '0';
  end;
  with CDS_Import do
  begin
    Edit;
    FieldByName('Money').Value := Trim(mvalue);
    if fieldbyname('Qty').AsFloat > 0 then
      FieldbyName('price').Value := format('%.4f', [FieldByName('money').AsFloat / FieldByName('Qty').AsFloat])
    else
      FieldbyName('price').Value := 0;
    FieldbyName('taxMoney').Value := format('%.2f', [FieldByName('money').AsFloat * (1 + FieldByName('tax').AsFloat / 100.00)]);
    Post;
  end;
  tv1.Controller.EditingController.ShowEdit();

end;

procedure TfrmReceivableImport.v1Column4PropertiesEditValueChanged(Sender: TObject);
var
  mvalue, FFieldName: string;
begin
  mvalue := TcxTextEdit(Sender).EditingText;
  if Trim(mvalue) = '' then
  begin
    mvalue := '0';
  end;
  FFieldName := Trim(Tv1.Controller.FocusedColumn.DataBinding.FilterFieldName);
  with CDS_Import do
  begin
    Edit;
    FieldByName(FFieldName).Value := Trim(mvalue);
    FieldbyName('taxMoney').Value := format('%.2f', [FieldByName('money').AsFloat * (1 + FieldByName('tax').AsFloat / 100.00)]);
    Post;
  end;
  tv1.Controller.EditingController.ShowEdit();
end;

end.