|
|
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.
|