D6HECHUAN/信息资源/u_crm_import.~pas

unit u_crm_import;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, bsSkinCtrls, DB, DBTables, MemTable, dxExEdtr, dxCntner, dxTL,
  dxDBCtrl, dxDBGrid, dxDBTLCl, dxGrClms,ComObj,ADODB, kbmMemTable;

type
  Tfrm_crm_Import = class(TForm)
    OpenDialog1: TOpenDialog;
    bsSkinPanel1: TbsSkinPanel;
    bsSkinButton1: TbsSkinButton;
    DataSource1: TDataSource;
    dxDBGrid1: TdxDBGrid;
    bsSkinButton2: TbsSkinButton;
    dxDBGrid1Column1: TdxDBGridColumn;
    dxDBGrid1Column2: TdxDBGridColumn;
    dxDBGrid1Column3: TdxDBGridColumn;
    dxDBGrid1Column4: TdxDBGridColumn;
    dxDBGrid1Column5: TdxDBGridColumn;
    dxDBGrid1Column6: TdxDBGridCheckColumn;
    dxDBGrid1Column7: TdxDBGridCheckColumn;
    MemoryTable1: TkbmMemTable;
    MemoryTable1Field: TBooleanField;
    MemoryTable1Field2: TBooleanField;
    MemoryTable1Field3: TStringField;
    MemoryTable1EN: TStringField;
    MemoryTable1Field4: TStringField;
    MemoryTable1Field5: TStringField;
    MemoryTable1iGZCode: TStringField;
    MemoryTable1SCAC: TStringField;
    MemoryTable1Field6: TBooleanField;
    dxDBGrid1Column8: TdxDBGridCheckColumn;
    MemoryTable1Field7: TStringField;
    dxDBGrid1Column9: TdxDBGridCheckColumn;
    MemoryTable1Field8: TStringField;
    dxDBGrid1Column10: TdxDBGridColumn;
    procedure bsSkinButton1Click(Sender: TObject);
    procedure bsSkinButton2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frm_crm_Import: Tfrm_crm_Import;

implementation
uses u_main,u_data_share,my_sys_function;

{$R *.dfm}

procedure Tfrm_crm_Import.bsSkinButton1Click(Sender: TObject);
var
  Ext,corp:String;
  ExcelTemplet:Variant;
  i,j,itemno:integer;
  imp:boolean;

begin
  MemoryTable1.EmptyTable;
  MemoryTable1.Open;
  MemoryTable1.DisableControls;

  if OpenDialog1.Execute then
  begin
    itemno:=1;
    Ext:=lowercase(ExtractFileExt(OpenDialog1.FileName));
    if Ext='.xls' then
    begin
      ExcelTemplet:= CreateOleObject('Excel.Application');
      try
        ExcelTemplet.Workbooks.Open(OpenDialog1.FileName);
        imp:=true;
        corp:=VartoStr(ExcelTemplet.ActiveSheet.Cells[1,1].Value);
        if corp<>'<27><><EFBFBD><EFBFBD>' then begin
          MessageDlg('ѡ<><D1A1><EFBFBD><EFBFBD><EFBFBD>ļ<EFBFBD><C4BC><EFBFBD>ʽ<EFBFBD><CABD><EFBFBD>ԣ<EFBFBD>',mtWarning,[mbYes],0);
          imp:=false;
        end;
        if imp then begin
          I:=2;
          while VartoStr(ExcelTemplet.ActiveSheet.Cells[I,1].Value)<>'' do
          begin
            MemoryTable1.Append;
            MemoryTable1.FieldByName('<27><><EFBFBD><EFBFBD>').AsString:=VarTostr(ExcelTemplet.ActiveSheet.Cells[i,1].Value);
            MemoryTable1.FieldByName('EN').AsString:=VarTostr(ExcelTemplet.ActiveSheet.Cells[i,2].Value);

            MemoryTable1.FieldByName('<27>ᵥ<EFBFBD><E1B5A5>').AsString:=VarTostr(ExcelTemplet.ActiveSheet.Cells[i,3].Value);
            MemoryTable1.FieldByName('<27><><EFBFBD><EFBFBD>').AsString:=VarTostr(ExcelTemplet.ActiveSheet.Cells[i,4].Value);
            MemoryTable1.FieldByName('iGZ-Code').AsString:=VarTostr(ExcelTemplet.ActiveSheet.Cells[i,5].Value);
            MemoryTable1.FieldByName('SCAC').AsString:=VarTostr(ExcelTemplet.ActiveSheet.Cells[i,6].Value);
            if frm_data_share.t_crm_client_ship.Locate('IGZ<47><5A><EFBFBD><EFBFBD>',trim(MemoryTable1.FieldByName('SCAC').AsString),[]) then
              MemoryTable1.FieldByName('ϵͳ<CFB5>д<EFBFBD><D0B4><EFBFBD>').AsBoolean:=true;
            if frm_data_share.t_crm_client_ship.Locate('<27>ͻ<EFBFBD><CDBB><EFBFBD><EFBFBD><EFBFBD>',trim(MemoryTable1.FieldByName('<27><><EFBFBD><EFBFBD>').AsString),[]) then
              MemoryTable1.FieldByName('ϵͳ<CFB5>д<EFBFBD><D0B4>ڿͻ<DABF>').AsBoolean:=true;

            if frm_data_share.t_crm_client_ship.Locate('<27><><EFBFBD><EFBFBD>',trim(MemoryTable1.FieldByName('EN').AsString),[]) then
              MemoryTable1.FieldByName('ϵͳ<CFB5>д<EFBFBD><D0B4>ڴ<EFBFBD><DAB4><EFBFBD>').AsBoolean:=true;
            MemoryTable1.FieldByName('<27><>ʤ<EFBFBD>ͻ<EFBFBD>').AsString:=VarTostr(ExcelTemplet.ActiveSheet.Cells[i,7].Value);
            MemoryTable1.Post;
            i:=i+1;
          end;
        end;
      finally
        ExcelTemplet.DisplayAlerts := false;//<2F>Ƿ<EFBFBD><C7B7><EFBFBD>ʾ<EFBFBD><CABE><EFBFBD><EFBFBD>
        ExcelTemplet.Quit;//<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>˳<EFBFBD>
      end;
    end
    else
    begin
      showmessage('<27><>ѡ<EFBFBD><D1A1><EFBFBD><EFBFBD>չ<EFBFBD><D5B9>ΪXLS <20><><EFBFBD>ļ<EFBFBD>');
    end;
    MemoryTable1.EnableControls;
  end;
//  MessageDlg('<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ɣ<EFBFBD>', mtConfirmation, [mbYes],0);
end;

procedure Tfrm_crm_Import.bsSkinButton2Click(Sender: TObject);
var aAdoQuery,bAdoQuery:tAdoQuery;
begin
  if MemoryTable1.IsEmpty then exit;

  frm_data_share.t_crm_client_ship.Requery;  
  aAdoQuery:=CreateAdoQuery;
  with aAdoQuery do
  begin
    Close;Sql.Clear;
    sql.Add('Select * from t_crm_client');
    Open;First;
  end;
  bAdoQuery:=CreateAdoQuery;
  with bAdoQuery do
  begin
    Close;Sql.Clear;
    sql.Add('update t_crm_client set  IGZ<47><5A><EFBFBD><EFBFBD>=:IGZ<47><5A><EFBFBD><EFBFBD> ');
    sql.Add('where <20>ͻ<EFBFBD><CDBB><EFBFBD><EFBFBD><EFBFBD>=:<3A>ͻ<EFBFBD><CDBB><EFBFBD><EFBFBD><EFBFBD> ');
  end;

  with MemoryTable1 do
  try
    DisableControls;
    First;
    while not eof do
    begin
      if MemoryTable1.FieldByName('ѡ<><D1A1>').AsBoolean and (trim(MemoryTable1.FieldByName('<27><>ʤ<EFBFBD>ͻ<EFBFBD>').AsString)<>'') then
      begin
        if not frm_data_share.t_crm_client_ship.Locate('<27>ͻ<EFBFBD><CDBB><EFBFBD><EFBFBD><EFBFBD>',trim(MemoryTable1.FieldByName('<27><>ʤ<EFBFBD>ͻ<EFBFBD>').AsString),[]) then
        begin
           aAdoQuery.Insert;
           aAdoQuery.FieldByName('<27>ͻ<EFBFBD><CDBB><EFBFBD><EFBFBD><EFBFBD>').AsString:='<27><><EFBFBD><EFBFBD>';
           aAdoQuery.FieldByName('¼<><C2BC><EFBFBD><EFBFBD>').AsString:=employee;
           aAdoQuery.FieldByName('¼<><C2BC><EFBFBD><EFBFBD><EFBFBD><EFBFBD>').AsDateTime:=date;
           aAdoQuery.FieldByName('<27>ͻ<EFBFBD><CDBB><EFBFBD><EFBFBD><EFBFBD>').AsString:=MemoryTable1.FieldByName('<27><>ʤ<EFBFBD>ͻ<EFBFBD>').AsString;
           aAdoQuery.FieldByName('<27>ͻ<EFBFBD>ȫ<EFBFBD><C8AB>').AsString:=MemoryTable1.FieldByName('<27><>ʤ<EFBFBD>ͻ<EFBFBD>').AsString;
           aAdoQuery.FieldByName('<27><><EFBFBD><EFBFBD>').AsString:=MemoryTable1.FieldByName('SCAC').AsString;
           aAdoQuery.FieldByName('IGZ<47><5A><EFBFBD><EFBFBD>').AsString:=MemoryTable1.FieldByName('SCAC').AsString;
           aAdoQuery.FieldByName('<27><><EFBFBD><EFBFBD>˾').AsBoolean:=true;
           aAdoQuery.Post;
        end
        else
        begin
            bAdoQuery.Close;
            bAdoQuery.Parameters.ParamByName('<27>ͻ<EFBFBD><CDBB><EFBFBD><EFBFBD><EFBFBD>').value:=MemoryTable1.FieldByName('<27><><EFBFBD><EFBFBD>').AsString;
            bAdoQuery.Parameters.ParamByName('IGZ<47><5A><EFBFBD><EFBFBD>').value:=MemoryTable1.FieldByName('SCAC').AsString;
            bAdoQuery.ExecSQL;
        end;
      end;
      Next;
    end;


  finally
    MemoryTable1.EnableControls;
    aAdoQuery.Free;
    bAdoQuery.Free;
  end;

end;

end.