hanxuntao
/
D6HECHUAN
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
master
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
D6HECHUAN/海运业务/u_op_seae_excel.~pas

432 lines
13 KiB
Plaintext
Raw Permalink Normal View History Unescape Escape

1
11 months ago
unit u_op_seae_excel;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
dxExEdtr, DB, dxTL, dxDBCtrl, dxDBGrid, dxCntner, StdCtrls, Mask,
wwdbedit, Wwdotdot, Wwdbcomb, bsSkinCtrls, Dialogs, Excel, ADODB,
BusinessSkinForm, DBCtrls;
type
Tfrm_op_seae_excel = class(TForm)
bsBusinessSkinForm1: TbsBusinessSkinForm;
t_excel_bill: TADOQuery;
t_excel_bill1: TDataSource;
bsSkinPanel1: TbsSkinPanel;
t_excel_bill_bill: TADOQuery;
t_excel_bill_bill1: TDataSource;
bsSkinPanel8: TbsSkinPanel;
bsSkinButton10: TbsSkinButton;
bsSkinButton11: TbsSkinButton;
bsSkinButton12: TbsSkinButton;
Excel1: TExcel;
t_op_ctn: TADOQuery;
bsSkinOpenDialog1: TOpenDialog;
SaveDialog: TSaveDialog;
bsSkinGroupBox2: TbsSkinGroupBox;
bsSkinPanel6: TbsSkinPanel;
bsSkinPanel7: TbsSkinPanel;
bsSkinButton7: TbsSkinButton;
bsSkinButton8: TbsSkinButton;
bsSkinButton9: TbsSkinButton;
t_excel_billEX_ID: TAutoIncField;
t_excel_billDSDesigner: TStringField;
t_excel_billDSDesigner2: TBlobField;
dxDBGrid2: TdxDBGrid;
dxDBGrid2Column1: TdxDBGridMaskColumn;
dxDBGrid2X: TdxDBGridMaskColumn;
dxDBGrid2Y: TdxDBGridMaskColumn;
bsSkinPanel9: TbsSkinPanel;
wwDBComboBox2: TwwDBComboBox;
DBEdit1: TDBEdit;
DBEdit2: TDBEdit;
dxDBGrid2Column4: TdxDBGridColumn;
DBEdit5: TDBEdit;
bsSkinButton1: TbsSkinButton;
bsSkinButton2: TbsSkinButton;
bsSkinButton4: TbsSkinButton;
bsSkinGroupBox3: TbsSkinGroupBox;
bsSkinPanel2: TbsSkinPanel;
dxDBGrid1: TdxDBGrid;
dxDBGrid1Column1: TdxDBGridMaskColumn;
Memo1: TMemo;
bsSkinButton13: TbsSkinButton;
t_op_edi: TADOQuery;
procedure FormShow(Sender: TObject);
procedure bsSkinButton1Click(Sender: TObject);
procedure bsSkinButton2Click(Sender: TObject);
procedure bsSkinButton4Click(Sender: TObject);
procedure t_excel_billPostError(DataSet: TDataSet; E: EDatabaseError;
var Action: TDataAction);
procedure t_excel_bill_billPostError(DataSet: TDataSet;
E: EDatabaseError; var Action: TDataAction);
procedure t_excel_billBeforeDelete(DataSet: TDataSet);
procedure bsSkinButton7Click(Sender: TObject);
procedure bsSkinButton8Click(Sender: TObject);
procedure bsSkinButton9Click(Sender: TObject);
procedure t_excel_bill_ctnBeforeInsert(DataSet: TDataSet);
procedure t_excel_bill_billBeforePost(DataSet: TDataSet);
procedure t_excel_bill_billAfterInsert(DataSet: TDataSet);
procedure bsSkinButton11Click(Sender: TObject);
procedure bsSkinButton12Click(Sender: TObject);
procedure bsSkinButton10Click(Sender: TObject);
procedure bsSkinButton13Click(Sender: TObject);
procedure t_excel_bill_billBeforeDelete(DataSet: TDataSet);
procedure t_excel_bill_billBeforeInsert(DataSet: TDataSet);
procedure t_excel_billBeforeEdit(DataSet: TDataSet);
procedure t_excel_billBeforeInsert(DataSet: TDataSet);
private
function GetShipEdiNO(Ship:String): String;
{ Private declarations }
public
{ Public declarations }
end;
var
frm_op_seae_excel: Tfrm_op_seae_excel;
implementation
uses u_main, my_sys_function, u_op_seae, u_data_share;
{$R *.dfm}
procedure Tfrm_op_seae_excel.FormShow(Sender: TObject);
begin
t_excel_bill.open;
t_excel_bill_bill.open;
if not if_open('170') then begin
bsSkinButton1.Enabled:=false;
bsSkinButton2.Enabled:=false;
bsSkinButton4.Enabled:=false;
bsSkinButton12.Enabled:=false;
bsSkinButton13.Enabled:=false;
bsSkinButton7.Enabled:=false;
bsSkinButton8.Enabled:=false;
bsSkinButton9.Enabled:=false;
end;
t_op_edi.Close;
t_op_edi.Parameters.ParamByName('<27><><EFBFBD><EFBFBD>').Value:=frm_op_seae.t_op_seae.fieldbyname('<27><><EFBFBD><EFBFBD>').asstring;
t_op_edi.Open;
end;
procedure Tfrm_op_seae_excel.bsSkinButton1Click(Sender: TObject);
begin
if employee<>'<27>¹<EFBFBD><C2B9><EFBFBD>' then
begin
showmessage('<27><><EFBFBD><EFBFBD>ϵϵͳ<CFB5><CDB3><EFBFBD><EFBFBD>Ա');
Exit;
end;
t_excel_bill.insert;
end;
procedure Tfrm_op_seae_excel.bsSkinButton2Click(Sender: TObject);
begin
if employee<>'<27>¹<EFBFBD><C2B9><EFBFBD>' then
begin
showmessage('<27><><EFBFBD><EFBFBD>ϵϵͳ<CFB5><CDB3><EFBFBD><EFBFBD>Ա');
Exit;
end;
table_post(t_excel_bill);
end;
procedure Tfrm_op_seae_excel.bsSkinButton4Click(Sender: TObject);
begin
if employee<>'<27>¹<EFBFBD><C2B9><EFBFBD>' then
begin
showmessage('<27><><EFBFBD><EFBFBD>ϵϵͳ<CFB5><CDB3><EFBFBD><EFBFBD>Ա');
Exit;
end;
table_delete(t_excel_bill);
end;
procedure Tfrm_op_seae_excel.t_excel_billPostError(DataSet: TDataSet;
E: EDatabaseError; var Action: TDataAction);
begin
showmessage('<27><>ʽ<EFBFBD><CABD><EFBFBD><EFBFBD><EFBFBD>ظ<EFBFBD>');
end;
procedure Tfrm_op_seae_excel.t_excel_bill_billPostError(DataSet: TDataSet;
E: EDatabaseError; var Action: TDataAction);
begin
showmessage('<27>ֶ<EFBFBD><D6B6><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ظ<EFBFBD>');
end;
procedure Tfrm_op_seae_excel.t_excel_billBeforeDelete(DataSet: TDataSet);
begin
if not if_open('170') then abort;
table_before_delete(t_excel_bill_bill,'<27>ӱ<EFBFBD>');
end;
procedure Tfrm_op_seae_excel.bsSkinButton7Click(Sender: TObject);
begin
t_excel_bill_bill.insert;
end;
procedure Tfrm_op_seae_excel.bsSkinButton8Click(Sender: TObject);
begin
table_post(t_excel_bill_bill);
end;
procedure Tfrm_op_seae_excel.bsSkinButton9Click(Sender: TObject);
begin
table_delete(t_excel_bill_bill);
end;
procedure Tfrm_op_seae_excel.t_excel_bill_ctnBeforeInsert(
DataSet: TDataSet);
begin
if not if_open('170') then abort;
table_before_insert(t_excel_bill);
end;
procedure Tfrm_op_seae_excel.t_excel_bill_billBeforePost(
DataSet: TDataSet);
begin
table_before_post(t_excel_bill_bill,'<27>ֶ<EFBFBD><D6B6><EFBFBD><EFBFBD><EFBFBD>');
table_before_post(t_excel_bill_bill,'<27><><EFBFBD><EFBFBD>X');
table_before_post(t_excel_bill_bill,'<27><><EFBFBD><EFBFBD>Y');
end;
procedure Tfrm_op_seae_excel.t_excel_bill_billAfterInsert(
DataSet: TDataSet);
begin
t_excel_bill_bill['EX_ID']:=t_excel_bill['EX_ID'];
end;
procedure Tfrm_op_seae_excel.bsSkinButton11Click(Sender: TObject);
begin
close;
end;
procedure Tfrm_op_seae_excel.bsSkinButton12Click(Sender: TObject);
begin
if t_excel_bill.IsEmpty then
exit;
if employee<>'<27>¹<EFBFBD><C2B9><EFBFBD>' then
begin
showmessage('<27><><EFBFBD><EFBFBD>ϵϵͳ<CFB5><CDB3><EFBFBD><EFBFBD>Ա');
Exit;
end;
if (t_excel_bill.state=dsinsert)or(t_excel_bill.state=dsedit)then
t_excel_bill.Post;
if bsSkinOpenDialog1.Execute then
begin
t_excel_bill.EDIT;
t_excel_billDSDesigner2.LoadFromFile(bsSkinOpenDialog1.FileName);
t_excel_bill.post;
end;
end;
procedure Tfrm_op_seae_excel.bsSkinButton10Click(Sender: TObject);
var
str:Tstringlist;
wide_str:widestring;
i:integer;
Y:INTEGER;
strtemp:string;
begin
if t_excel_bill.IsEmpty then
exit;
t_excel_billDSDesigner2.SaveToFile(ExtractFilePath(ParamStr(0))+t_excel_bill.fieldbyname('EX_ID').asstring+'.xls');
str:=Tstringlist.Create;
str.Clear;
excel1.Connect;
Excel1.Exec('[open('+'"'+ExtractFilePath(ParamStr(0))+t_excel_bill.fieldbyname('EX_ID').asstring+'.xls'+'"'+')]');
t_excel_bill_bill.First;
while not t_excel_bill_bill.eof do
begin
if t_excel_bill_bill.fieldbyname('<27>ֶ<EFBFBD><D6B6><EFBFBD><EFBFBD><EFBFBD>').asstring<>'<27><>װ<EFBFBD><D7B0><EFBFBD><EFBFBD>ϸ'then
begin
if trim(t_excel_bill_bill.fieldbyname('<27>ֶ<EFBFBD><D6B6><EFBFBD><EFBFBD><EFBFBD>').asstring)<>'<27>̶<EFBFBD>ֵ'then
begin
if (pos('<27><><EFBFBD><EFBFBD>',t_excel_bill_bill.fieldbyname('<27>ֶ<EFBFBD><D6B6><EFBFBD><EFBFBD><EFBFBD>').asstring)>0) and (t_excel_bill_bill.fieldbyname('<27>ֶ<EFBFBD><D6B6><EFBFBD><EFBFBD><EFBFBD>').asstring<>'<27><><EFBFBD><EFBFBD>Ա') then begin
strtemp:=t_excel_bill_bill.fieldbyname('<27>ֶ<EFBFBD><D6B6><EFBFBD><EFBFBD><EFBFBD>').asstring;
strtemp:=copy(strtemp,5,length(strtemp)-3);
Memo1.Text:=frm_data_share.t_load_employee.fieldbyname(Trim(strtemp)).asstring;
end else if t_excel_bill_bill.fieldbyname('<27>ֶ<EFBFBD><D6B6><EFBFBD><EFBFBD><EFBFBD>').asstring='<27><><EFBFBD>ѷ<EFBFBD>ʽ<EFBFBD><CABD><EFBFBD><EFBFBD>' then begin
strtemp:='<27><><EFBFBD><EFBFBD>';
if (frm_op_seae.t_op_seae2.DataSet.fieldbyname('<27><><EFBFBD>ѷ<EFBFBD>ʽ').asstring='FREIGHT PREPAID') THEN
strtemp:='Ԥ<><D4A4>';
if (frm_op_seae.t_op_seae2.DataSet.fieldbyname('<27><><EFBFBD>ѷ<EFBFBD>ʽ').asstring='FREIGHT COLLECT') THEN
strtemp:='<27><><EFBFBD><EFBFBD>';
Memo1.Text:=strtemp;
end else if t_excel_bill_bill.fieldbyname('<27>ֶ<EFBFBD><D6B6><EFBFBD><EFBFBD><EFBFBD>').asstring='<27><>վ<EFBFBD><D5BE><EFBFBD>˴<EFBFBD><CBB4><EFBFBD>' then begin
strtemp:=GetShipEdiNO(frm_op_seae.t_op_seae2.DataSet.fieldbyname('<27><>վ').asstring);
Memo1.Text:=strtemp;
end else if (t_excel_bill_bill.fieldbyname('<27>ֶ<EFBFBD><D6B6><EFBFBD><EFBFBD><EFBFBD>').asstring='<27>ڶ<EFBFBD>֪ͨ<CDA8><D6AA>') or (t_excel_bill_bill.fieldbyname('<27>ֶ<EFBFBD><D6B6><EFBFBD><EFBFBD><EFBFBD>').asstring='SI<53><49>ע')
or (t_excel_bill_bill.fieldbyname('<27>ֶ<EFBFBD><D6B6><EFBFBD><EFBFBD><EFBFBD>').asstring='ָ<><D6B8>ҵ<EFBFBD><D2B5>Ա') or (t_excel_bill_bill.fieldbyname('<27>ֶ<EFBFBD><D6B6><EFBFBD><EFBFBD><EFBFBD>').asstring='NAMEACCOUNT')
or (t_excel_bill_bill.fieldbyname('<27>ֶ<EFBFBD><D6B6><EFBFBD><EFBFBD><EFBFBD>').asstring='NAMEACCOUNTCODE') then begin
strtemp:=t_op_edi.fieldbyname(Trim(strtemp)).asstring;
Memo1.Text:=strtemp;
end else
Memo1.Text:=frm_op_seae.t_op_seae2.DataSet.fieldbyname(t_excel_bill_bill.fieldbyname('<27>ֶ<EFBFBD><D6B6><EFBFBD><EFBFBD><EFBFBD>').asstring).asstring;
for i:=0 to Memo1.Lines.Count-1 do
begin
excel1.putstr(t_excel_bill_bill.fieldbyname('<27><><EFBFBD><EFBFBD>Y').asinteger+i,
t_excel_bill_bill.fieldbyname('<27><><EFBFBD><EFBFBD>X').asinteger,
Memo1.Lines[i]);
end;
end
else
begin
excel1.putstr(t_excel_bill_bill.fieldbyname('<27><><EFBFBD><EFBFBD>Y').asinteger,
t_excel_bill_bill.fieldbyname('<27><><EFBFBD><EFBFBD>X').asinteger,
trim(t_excel_bill_bill.fieldbyname('<27>̶<EFBFBD>ֵ').asstring));
end;
end
else
begin
t_op_ctn.close;
t_op_ctn.Parameters[0].value:=frm_op_seae.t_op_seae2.DataSet.fieldbyname('<27><><EFBFBD><EFBFBD>').asstring;
t_op_ctn.open;
Y:=t_excel_bill_bill.fieldbyname('<27><><EFBFBD><EFBFBD>Y').asinteger;
t_op_ctn.first;
while not t_op_ctn.eof do
begin
wide_str:=t_op_ctn.fieldbyname('<27><><EFBFBD><EFBFBD>').asstring
+copy(' ',1,20-length(t_op_ctn.fieldbyname('<27><><EFBFBD><EFBFBD>').asstring))
+t_op_ctn.fieldbyname('<27><><EFBFBD><EFBFBD>').asstring;
excel1.putstr(Y,1,wide_str);
wide_str:=t_op_ctn.fieldbyname('<27><><EFBFBD><EFBFBD>').asstring+'X'
+t_op_ctn.fieldbyname('<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ʽ').asstring+' '
+t_op_ctn.fieldbyname('<27><><EFBFBD><EFBFBD>').asstring
+t_op_ctn.fieldbyname('<27><>װ').asstring;
excel1.putstr(Y,2,wide_str);
wide_str:=t_op_ctn.fieldbyname('<27><><EFBFBD><EFBFBD>').asstring+'KGS';
excel1.putstr(Y,5,wide_str);
wide_str:=t_op_ctn.fieldbyname('<27><><EFBFBD><EFBFBD>').asstring+'CBM';
excel1.putstr(Y,6,wide_str);
Y:=Y+1;
t_op_ctn.next;
end;
wide_str:=frm_op_seae.t_op_seae2.DataSet.fieldbyname('<27><><EFBFBD><EFBFBD>').asstring
+frm_op_seae.t_op_seae2.DataSet.fieldbyname('<27><>װ').asstring;
excel1.putstr(33,2,wide_str);
wide_str:=frm_op_seae.t_op_seae2.DataSet.fieldbyname('<27><><EFBFBD><EFBFBD>').asstring+'KGS';
excel1.putstr(33,5,wide_str);
wide_str:=frm_op_seae.t_op_seae2.DataSet.fieldbyname('<27><><EFBFBD><EFBFBD>').asstring+'CBM';
excel1.putstr(33,6,wide_str);
end;
t_excel_bill_bill.next;
end;
SaveDialog.Filter := '(*.xls)|*.xls';
SaveDialog.FileName := frm_op_seae.t_op_seae2.DataSet.fieldbyname('<27><><EFBFBD>ᵥ<EFBFBD><E1B5A5>').asstring+'.xls';
if SaveDialog.Execute then
begin
Excel1.Exec('[SAVE.AS("'+SaveDialog.FileName+'";1;"";false;"";false)]');
Excel1.Exec('[CLOSE(false)]');
Excel1.Exec('[open("'+SaveDialog.FileName+'")]');
Excel1.Exec('[APP.maxIMIZE()]'); //<2F><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>excel
end;
end;
procedure Tfrm_op_seae_excel.bsSkinButton13Click(Sender: TObject);
var
exname:string;
afield:Variant;
exid:integer;
aQuery:TAdoQuery;
begin
if t_excel_bill.IsEmpty then
exit;
if employee<>'<27>¹<EFBFBD><C2B9><EFBFBD>' then
begin
showmessage('<27><><EFBFBD><EFBFBD>ϵϵͳ<CFB5><CDB3><EFBFBD><EFBFBD>Ա');
Exit;
end;
exname:=t_excel_bill.fieldbyname('<27><>ʽ<EFBFBD><CABD><EFBFBD><EFBFBD>').AsString;
afield:=t_excel_bill.fieldbyname('<27>ļ<EFBFBD>').Value;
exid:=t_excel_bill.fieldbyname('EX_ID').asinteger;
t_excel_bill.Insert;
t_excel_bill.fieldbyname('<27><>ʽ<EFBFBD><CABD><EFBFBD><EFBFBD>').AsString:='<27><><EFBFBD><EFBFBD>'+exname;
t_excel_bill.fieldbyname('<27>ļ<EFBFBD>').Value:=afield;
t_excel_bill.post;
aQuery:=CreateAdoQuery;
try
with aQuery do begin
close;
sql.clear;
sql.Add('select * from t_excel_bill_bill where EX_ID='+inttostr(exid));
open;
first;
while not eof do begin
t_excel_bill_bill.insert;
t_excel_bill_bill.fieldbyname('<27>ֶ<EFBFBD><D6B6><EFBFBD><EFBFBD><EFBFBD>').Value:=aQuery.fieldbyname('<27>ֶ<EFBFBD><D6B6><EFBFBD><EFBFBD><EFBFBD>').Value;
t_excel_bill_bill.fieldbyname('<27><><EFBFBD><EFBFBD>X').Value:=aQuery.fieldbyname('<27><><EFBFBD><EFBFBD>X').Value;
t_excel_bill_bill.fieldbyname('<27><><EFBFBD><EFBFBD>Y').Value:=aQuery.fieldbyname('<27><><EFBFBD><EFBFBD>Y').Value;
t_excel_bill_bill.fieldbyname('<27>̶<EFBFBD>ֵ').Value:=aQuery.fieldbyname('<27>̶<EFBFBD>ֵ').Value;
t_excel_bill_bill.post;
next;
end;
end;
finally
freeandnil(aQuery);
end;
end;
procedure Tfrm_op_seae_excel.t_excel_bill_billBeforeDelete(
DataSet: TDataSet);
begin
if not if_open('170') then abort;
end;
procedure Tfrm_op_seae_excel.t_excel_bill_billBeforeInsert(
DataSet: TDataSet);
begin
if not if_open('170') then abort;
end;
procedure Tfrm_op_seae_excel.t_excel_billBeforeEdit(DataSet: TDataSet);
begin
if not if_open('170') then abort;
end;
procedure Tfrm_op_seae_excel.t_excel_billBeforeInsert(DataSet: TDataSet);
begin
if not if_open('170') then abort;
end;
function Tfrm_op_seae_excel.GetShipEdiNO(Ship: String): String;
var
aQuery:TADOQuery;
begin
aQuery:=CreateAdoQuery;
try
with aQuery do begin
Close;SQL.Clear;
SQL.Add('Select <20><><EFBFBD><EFBFBD>,EDI<44><49><EFBFBD><EFBFBD>,<2C><><EFBFBD>˴<EFBFBD><CBB4><EFBFBD>,<2C>ͻ<EFBFBD><CDBB><EFBFBD><EFBFBD><EFBFBD>,<2C>ͻ<EFBFBD>ȫ<EFBFBD><C8AB>,<2C><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,¼<><C2BC><EFBFBD><EFBFBD>,¼<><C2BC><EFBFBD><EFBFBD><EFBFBD><EFBFBD> from t_crm_client');
SQL.Add('where <20>ͻ<EFBFBD><CDBB><EFBFBD><EFBFBD><EFBFBD>='''+Ship+'''');
SQL.Add('order by <20><><EFBFBD><EFBFBD>');
Open;
if not IsEmpty then
Result:=Trim(FieldByName('<27><><EFBFBD>˴<EFBFBD><CBB4><EFBFBD>').asstring)
else
Result:='';
end;
finally
FreeAndNil(aQuery);
end;
end;
end.