unit u_ch_invoice_bankdata;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, dxExEdtr, dxCntner, dxTL, dxDBCtrl, dxDBGrid, bsSkinCtrls, DB,
ADODB, ExtCtrls, RxLookup, StdCtrls, wwdbdatetimepicker, wwdblook, Menus,
Mask, bsSkinBoxCtrls, bsdbctrls,DateUtils, kbmMemTable, DBCtrlsEh,comobj,
ComCtrls, bsSkinTabs, dxDBTLCl, dxGrClms, cxDBLabel, cxControls,
cxContainer, cxEdit, cxLabel, dxGrClEx, cxCheckBox, Grids, DBGrids;
type
Tfrm_ch_invoice_bankdata = class(TForm)
PopupMenu1: TPopupMenu;
N1: TMenuItem;
bsSkinPanel3: TbsSkinPanel;
btn6_notify_exit: TbsSkinButton;
btn1_notify_search: TbsSkinButton;
btn2_notify_clear: TbsSkinButton;
bsSkinPanel4: TbsSkinPanel;
lbl14: TLabel;
bsknpnl1_m: TbsSkinPanel;
ds1_bankdata: TDataSource;
qry1_bankdata: TADOQuery;
bsknspltr1: TbsSkinSplitter;
bsknpnl1: TbsSkinPanel;
bsknpnl2: TbsSkinPanel;
bsknpnl3: TbsSkinPanel;
btn5_submit: TbsSkinButton;
ds2_bankdata_billno: TDataSource;
qry2_bankdata_billno: TADOQuery;
bsknspltr2: TbsSkinSplitter;
dxdbgrd1: TdxDBGrid;
bsknpnl4: TbsSkinPanel;
bsknpnl5: TbsSkinPanel;
dxdbgrd3_notify: TdxDBGrid;
dxdbgrdmskclmndxdbgrd3_notifyColumn1: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd3_notifyColumn2: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd3_notifyColumn3: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd3_notifyColumn4: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd3_notifyIN_ID: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd3_notifyColumn6: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd3_notifyColumn8: TdxDBGridMaskColumn;
dxdbgrdtclmndxdbgrd3_notifyColumn9: TdxDBGridDateColumn;
dxdbgrdmskclmndxdbgrd3_notifyColumn10: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd3_notifyColumn11: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd3_notifyColumn13: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd3_notifyColumn15: TdxDBGridMaskColumn;
dxdbgrdchckclmndxdbgrd3_notifyColumn17: TdxDBGridCheckColumn;
btn6_cancelMatch: TbsSkinButton;
btn3ImportExcel: TbsSkinButton;
dxdbgrd2: TdxDBGrid;
lbl1: TLabel;
lbl2: TLabel;
edt3_billNo: TEdit;
cbb1_custmer3: TRxDBLookupCombo;
lbl3: TLabel;
lbl4: TLabel;
connExcel: TADOConnection;
qry1Excel: TADOQuery;
ds1Excel: TDataSource;
btn1_unmatchedSearch: TbsSkinButton;
qry3_unmatched: TADOQuery;
ds3_unmatched: TDataSource;
lbl5: TLabel;
wdblkpcmb1_billHead: TwwDBLookupCombo;
lbl6: TLabel;
wdbdtmpckr1_billdate: TwwDBDateTimePicker;
lbl7: TLabel;
wdbdtmpckr2_billdate: TwwDBDateTimePicker;
btn2_clearSeachTJ2: TbsSkinButton;
dxdbgrdmskclmndxdbgrd2IN_ID: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column2: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column3: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column4: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column5: TdxDBGridMaskColumn;
dxdbgrdxtlkpclmndxdbgrd2Column6: TdxDBGridExtLookupColumn;
dxdbgrdmskclmndxdbgrd2Column7: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column8: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column10: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column15: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column16: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column17: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column18: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column19: TdxDBGridMaskColumn;
dxdbgrdcrncyclmndxdbgrd2Column21: TdxDBGridCurrencyColumn;
dxdbgrdmskclmndxdbgrd2Column23: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column25: TdxDBGridMaskColumn;
dxdbgrdchckclmndxdbgrd2Column26: TdxDBGridCheckColumn;
dxdbgrdmskclmndxdbgrd2Column27: TdxDBGridMaskColumn;
dxdbgrdtclmndxdbgrd2Column28: TdxDBGridDateColumn;
dxdbgrdmskclmndxdbgrd2Column30: TdxDBGridMaskColumn;
dxdbgrdchckclmndxdbgrd2Column31: TdxDBGridCheckColumn;
dxdbgrdmskclmndxdbgrd2Column32: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column34: TdxDBGridMaskColumn;
dxdbgrdtclmndxdbgrd2Column35: TdxDBGridDateColumn;
dxdbgrdmskclmndxdbgrd2Column36: TdxDBGridMaskColumn;
dxdbgrdtclmndxdbgrd2Column37: TdxDBGridDateColumn;
dxdbgrdmskclmndxdbgrd2Column38: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column39: TdxDBGridMaskColumn;
dxdbgrdchckclmndxdbgrd2Column42: TdxDBGridCheckColumn;
dxdbgrdmskclmndxdbgrd2Column43: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column44: TdxDBGridMaskColumn;
dxdbgrdchckclmndxdbgrd2Column45: TdxDBGridCheckColumn;
dxdbgrdmskclmndxdbgrd2Column46: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column47: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column48: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column49: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column53: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column54: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column55: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column56: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd2Column60: TdxDBGridMaskColumn;
dxdbgrdchckclmndxdbgrd2Column61: TdxDBGridCheckColumn;
dxdbgrdmskclmndxdbgrd2Column62: TdxDBGridMaskColumn;
dxdbgrdchckclmndxdbgrd2Column63: TdxDBGridCheckColumn;
ds_ch_invoice_head1: TDataSource;
qry_ch_invoice_head: TADOQuery;
sp_bankflow_batid: TADOStoredProc;
dxdbgrdmskclmndxdbgrd1Column1: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd1Column2: TdxDBGridMaskColumn;
dxdbgrdclmndxdbgrd1Column3: TdxDBGridColumn;
dxdbgrdclmndxdbgrd1Column4: TdxDBGridColumn;
dxdbgrdmskclmndxdbgrd1Column5: TdxDBGridMaskColumn;
dxdbgrdmskclmndxdbgrd1Column7: TdxDBGridMaskColumn;
dxdbgrdtclmndxdbgrd1Column8: TdxDBGridDateColumn;
dxdbgrdclmndxdbgrd1Column9: TdxDBGridColumn;
dxdbgrdmskclmndxdbgrd1Column11: TdxDBGridMaskColumn;
dxdbgrdtclmndxdbgrd1Column12: TdxDBGridDateColumn;
dxdbgrdmskclmndxdbgrd1Column13: TdxDBGridMaskColumn;
btn4_autoMatch: TbsSkinButton;
dxdbgrdclmndxdbgrd1Column6: TdxDBGridColumn;
dxdbgrdclmndxdbgrd1Column14: TdxDBGridColumn;
dxdbgrdclmndxdbgrd1Column15: TdxDBGridColumn;
dxdbgrdchckclmnColumn16_SHFPP: TdxDBGridCheckColumn;
dxdbgrdclmndxdbgrd1Column2b: TdxDBGridColumn;
ds4_pc: TDataSource;
qry4_pc: TADOQuery;
dxdbgrdclmndxdbgrdcrncyclmndxdbgrd1Column10: TdxDBGridColumn;
dxdbgrdclmndxdbgrdcrncyclmndxdbgrd2Column20: TdxDBGridColumn;
dxdbgrdclmndxdbgrdcrncyclmndxdbgrd2Column22: TdxDBGridColumn;
dxdbgrdclmndxdbgrdcrncyclmndxdbgrd2Column52: TdxDBGridColumn;
dxdbgrdclmndxdbgrdcrncyclmndxdbgrd3_notifyColumn16: TdxDBGridColumn;
btn11: TbsSkinButton;
btn3_custprint: TbsSkinButton;
dxdbgrdclmndxdbgrdcrncyclmndxdbgrd2Column51: TdxDBGridColumn;
qry5_report: TADOQuery;
ds5_report: TDataSource;
pnl1: TPanel;
lbl9: TLabel;
lbl15_searchqdthm: TLabel;
lbl10: TLabel;
lbl8: TLabel;
wdbdtmpckr1_kc: TwwDBDateTimePicker;
wdbdtmpckr2_kc: TwwDBDateTimePicker;
edt1_kh: TEdit;
cbb1_import_BatNo: TRxDBLookupCombo;
cxchckbx1: TcxCheckBox;
lbl11: TLabel;
btn4_cancalImport: TbsSkinButton;
btn2_refreshPC: TbsSkinButton;
dxdbgrdclmndxdbgrd1Column18_pprq: TdxDBGridColumn;
dxdbgrdclmndxdbgrd1Column19: TdxDBGridColumn;
dxdbgrdclmndxdbgrd1Column21: TdxDBGridColumn;
dxdbgrdchckclmndxdbgrdclmndxdbgrd1Column20: TdxDBGridCheckColumn;
dxdbgrdclmndxdbgrd1Column22: TdxDBGridColumn;
dxdbgrd1Column23: TdxDBGridColumn;
dxdbgrd1Column24: TdxDBGridColumn;
dxdbgrd1Column25: TdxDBGridColumn;
procedure btn4_exitClick(Sender: TObject);
procedure FormClose(Sender: TObject; var Action: TCloseAction);
procedure N1Click(Sender: TObject);
procedure btn1_notify_searchClick(Sender: TObject);
procedure MenuItem1Click(Sender: TObject);
procedure btn6_notify_exitClick(Sender: TObject);
procedure FormShow(Sender: TObject);
procedure edt1_khKeyPress(Sender: TObject; var Key: Char);
procedure dxdbgrd3_notifyDblClick(Sender: TObject);
procedure btn3_notifyClick(Sender: TObject);
procedure btn2_notify_clearClick(Sender: TObject);
procedure Edit6_searchkhmchKeyPress(Sender: TObject; var Key: Char);
procedure btn5_submitClick(Sender: TObject);
procedure btn6_cancelMatchClick(Sender: TObject);
procedure btn3ImportExcelClick(Sender: TObject);
procedure btn1_unmatchedSearchClick(Sender: TObject);
procedure btn4_autoMatchClick(Sender: TObject);
procedure dxdbgrd1CustomDraw(Sender: TObject; ACanvas: TCanvas;
ARect: TRect; ANode: TdxTreeListNode; AColumn: TdxDBTreeListColumn;
const AText: String; AFont: TFont; var AColor: TColor; ASelected,
AFocused: Boolean; var ADone: Boolean);
procedure btn2_clearSeachTJ2Click(Sender: TObject);
procedure btn11Click(Sender: TObject);
procedure btn3_custprintClick(Sender: TObject);
procedure cbb1_import_BatNoChange(Sender: TObject);
procedure cxchckbx1PropertiesChange(Sender: TObject);
procedure btn4_cancalImportClick(Sender: TObject);
procedure btn2_refreshPCClick(Sender: TObject);
procedure dxdbgrd1Click(Sender: TObject);
procedure dxdbgrd1ChangeNode(Sender: TObject; OldNode,
Node: TdxTreeListNode);
procedure dxdbgrd1ColumnSorting(Sender: TObject;
Column: TdxDBTreeListColumn; var Allow: Boolean);
private
{ Private declarations }
function checkImport(fkrzh:string;jylsh:string):Boolean;
function importdata(batid:string) :Boolean;
function importdata_zhsh(batid:string;skrzh_4m:string;skrzh_4:string;bb_7:string):Boolean;
procedure UpateSeaeState(bsno,billstate:string);
procedure customerUnmatchedBill();
public
{ Public declarations }
procedure seachPrintList();
end;
var
frm_ch_invoice_bankdata: Tfrm_ch_invoice_bankdata;
implementation
uses u_main,my_sys_function,u_sys_progress;
{$R *.dfm}
procedure Tfrm_ch_invoice_bankdata.btn4_exitClick(Sender: TObject);
begin
Close;
end;
procedure Tfrm_ch_invoice_bankdata.FormShow(Sender: TObject);
var str,mblstatus:string;
begin
qry_ch_invoice_head.active:=false;
qry_ch_invoice_head.active:=true;
wdbdtmpckr1_kc.Date:=Date-1 ;
wdbdtmpckr2_kc.Date:=Date ;
Width :=width-1;
qry4_pc.Active:=False;
qry4_pc.Active:=true;
dxdbgrd3_notify.Width :=dxdbgrd3_notify.width-1;
bsSkinPanel3.Height :=34;
loadreggrid(dxdbgrd3_notify,false,caption);
loadreggrid(dxdbgrd1,false,caption+'1');
loadreggrid(dxdbgrd2,false,caption+'2');
seachPrintList();
end;
procedure Tfrm_ch_invoice_bankdata.FormClose(Sender: TObject;
var Action: TCloseAction);
begin
savereggrid(dxdbgrd1,caption+'1');
savereggrid(dxdbgrd2,caption+'2');
savereggrid(dxdbgrd3_notify,caption);
frm_ch_invoice_bankdata.Hide;
frm_ch_invoice_bankdata.ManualFloat(frm_ch_invoice_bankdata.BoundsRect );
frm_main.freeTabs('frm_ch_invoice_bankdata');
action:=cafree;
frm_ch_invoice_bankdata:=nil;
end;
procedure Tfrm_ch_invoice_bankdata.N1Click(Sender: TObject);
begin
grid_save_xls(dxdbgrd1);
end;
procedure Tfrm_ch_invoice_bankdata.btn1_notify_searchClick(Sender: TObject);
begin
seachPrintList();
end;
///
///
///
procedure Tfrm_ch_invoice_bankdata.seachPrintList();
var
str:string;
begin
//
str:=' where 1=1 ';
if(cbb1_import_BatNo.DisplayValues[1]<>'') then
str:= str + ' AND 批次 =' + Trim(cbb1_import_BatNo.DisplayValues[1]) + ' '
else
begin
if(wdbdtmpckr1_kc.Text<>'') then
str:= str + ' AND 导入日期>=''' + Trim(wdbdtmpckr1_kc.Text) + ''' ';
//
if(wdbdtmpckr2_kc.Text<>'') then
str:= str + ' AND 导入日期<=''' + Trim(wdbdtmpckr2_kc.Text) + ' 23:59:59'' ';
end;
if(edt1_kh.Text<>'') then
str:= str + ' AND 付款人名称 like ''%' +stringreplace( Trim(edt1_kh.Text),'''','',[rfreplaceall]) + '%'' ';
if cxchckbx1.State= cbsChecked then
str:=str+ 'AND 是否匹配=1'
else if cxchckbx1.State=cbsunchecked then
str:=str+ 'AND isnull(是否匹配,0)=0';
str:=' select * from t_ch_invoice_bankdata ' + str+' order by 序号';
with qry1_bankdata do begin
//
Close;
SQL.Clear;
SQL.Add(str) ;
Open;
end;
qry2_bankdata_billno.active:=false;
qry2_bankdata_billno.Active:=true;
end;
procedure Tfrm_ch_invoice_bankdata.MenuItem1Click(Sender: TObject);
begin
grid_save_xls(dxdbgrd3_notify);
end;
procedure Tfrm_ch_invoice_bankdata.btn6_notify_exitClick(Sender: TObject);
begin
Close();
end;
procedure Tfrm_ch_invoice_bankdata.edt1_khKeyPress(Sender: TObject;
var Key: Char);
var
str:string;
begin
//
if key=#13 then
begin
seachPrintList();
end;
end;
procedure Tfrm_ch_invoice_bankdata.dxdbgrd3_notifyDblClick(Sender: TObject);
var
str:string;
begin
//
// str:=qry1_qdtm.FieldByName('签单条编号').AsString;
end;
procedure Tfrm_ch_invoice_bankdata.btn3_notifyClick(Sender: TObject);
var
qdtzht,str:string;
i,noticecount:integer;
aQuery:TADOQuery;
DatabaseDateTime:TDatetime;
begin
//
{ if (dxdbgrd3_notify.SelectedCount<1) and (qry1_qdtm.Recordset.RecordCount<1) then
Exit;
str:=qry1_qdtm.FieldByName('签单条编号').AsString;
show_qdtxx(str,2);
qry1_qdtm.Requery();
Exit;
}
end;
procedure Tfrm_ch_invoice_bankdata.btn2_notify_clearClick(Sender: TObject);
begin
wdbdtmpckr1_kc.Date:=Date-1 ;
wdbdtmpckr2_kc.Date:=Date ;
edt1_kh.Text:='';
end;
procedure Tfrm_ch_invoice_bankdata.Edit6_searchkhmchKeyPress(
Sender: TObject; var Key: Char);
begin
//回车查询执行
if key=#13 then
begin
btn1_notify_searchClick(Self.btn1_notify_search);
end;
end;
///
/// 更改海运出口状态
///
procedure Tfrm_ch_invoice_bankdata.UpateSeaeState(bsno,billstate:string);
begin
end;
procedure Tfrm_ch_invoice_bankdata.btn5_submitClick(
Sender: TObject);
var
str,pid,xh,strinfo:string;
bank1,bank2,kh1,kh2,khead1,khead2,checkinfo,kh_name,kh_bank:string;
fpbhs,fph:string;//所选发票编号s
i,icount:Integer;
aQuery:TADOQuery;
blcust,blbank,blmoney,blprop:Boolean;
totalmoney,bankmoney:double;
strsql:string;
begin
if(qry1_bankdata.Recordset.RecordCount<1) then
Exit;
if( qry1_bankdata.fieldbyname('是否匹配').AsBoolean) then
begin
Exit;
end;
pid := qry1_bankdata.fieldbyname('批次').AsString;
xh:= qry1_bankdata.fieldbyname('序号').AsString;
blcust:=True;
blbank:=True;
blprop:=false;
//验证客户名称是否唯一??,银行账号是否一致??金额差别??
checkinfo:='';
fpbhs:='';
totalmoney:=0;
aQuery:=CreateAdoQuery;
aQuery.Close;
aQuery.SQL.Clear;
strsql:='INSERT INTO [t_ch_invoice_bankdata_billno] ([序号] ,[发票号码],[发票金额比例],[发票数量]) '
+'values (%s,''%s'',1,%d);';
for i:=0 to dxdbgrd2.SelectedCount-1 do
begin
icount:= dxdbgrd2.SelectedCount;
fph:=qry3_unmatched.FieldByName('发票号码').AsString;
aQuery.SQL.add(Format(strsql,[xh,fph,icount]));
//
qry3_unmatched.GoTobookmark(pointer(dxdbgrd2.SelectedRows[i]));
if(blcust) then
begin
if i=0 then
begin
kh1 := qry3_unmatched.FieldByName('客户名称').AsString;
kh2:=kh1;
khead1:= qry3_unmatched.FieldByName('发票抬头').AsString;
khead2:= khead1;
end
else
begin
kh2 := qry3_unmatched.FieldByName('客户名称').AsString;
khead2:= qry3_unmatched.FieldByName('发票抬头').AsString;
end;
if kh1<>kh2 then
begin
checkinfo:=checkinfo+'所选发票客户名称不唯一!'+#13#10;
blcust:=False;
end;
end;
if(blcust) then
begin
if i=0 then
begin
bank1 := qry3_unmatched.FieldByName('开户银行').AsString;
bank2:=bank1;
end
else
begin
bank2 := qry3_unmatched.FieldByName('开户银行').AsString;
end;
if bank1<>bank2 then
begin
checkinfo:=checkinfo+'所选发票开户银行不唯一!'+#13#10;
blbank:=False;
end;
end;
totalmoney:= totalmoney+ qry3_unmatched.fieldbyname('合计金额').AsFloat;
if( fpbhs='') then
fpbhs:= ''''+ qry3_unmatched.FieldByName('发票号码').AsString +''''
else
fpbhs:= fpbhs + ','''+ qry3_unmatched.FieldByName('发票号码').AsString +'''' ;
end;
if(blbank=False) or ( blcust=False) then
blprop:=True;
//
kh_name:=qry1_bankdata.fieldbyname('付款人名称').AsString;
//客户名称
if( khead1<>kh_name) or (khead2<>kh_name) then
begin
checkinfo:=checkinfo +'银行流水与发票中的客户名称不一致!' +#13#10;
blprop:=True;
end;
//开户银行
kh_bank:=qry1_bankdata.fieldbyname('收款人开户行').AsString;
if(copy(bank1,1,4)<>copy(kh_bank,1,4)) or ( copy(bank2,1,4)<>copy(kh_bank,1,4) ) then
begin
checkinfo:=checkinfo +'银行流水开户行与发票中的开户行不一致!' +#13#10;
blprop:=True;
end;
//流水金额
bankmoney:= qry1_bankdata.fieldbyname('金额').AsFloat;
if( bankmoney-totalmoney)<>0 then
begin
checkinfo:=checkinfo +format('银行流水交易金额%f与发票匹配的合计金额%f不相等!'
,[bankmoney,totalmoney]) +#13#10;
blprop:=True;
end;
if(blprop) then
begin
strinfo:='您所选的手动匹配的支票存在一下问题:'+#13#10
+ checkinfo+#13#10
+ '是否要批次为'+ qry1_bankdata.fieldbyname('批次').AsString
+'顺序号为' + qry1_bankdata.fieldbyname('顺序号').AsString + '的银行流水手动匹配发票吗?';
if(application.MessageBox(pchar(strinfo),'信息提示', MB_ICONQUESTION+MB_YESNO) <> ID_YES) then
Exit;
end;
aQuery.SQL.add('update t_ch_invoice set 是否匹配银行流水=1 where 发票号码 in (' +fpbhs +'); ');
aQuery.SQL.add('update t_ch_invoice_bankdata set t_ch_invoice_bankdata.是否匹配=1,匹配日期=GETDATE(),匹配类型=''手动'',发票号码 ='''
+ stringreplace(fpbhs,'''','',[rfreplaceall]) +''',匹配数量= ' + inttostr(icount)
+ ' where 序号= ' +xh +';');
//客户简称 客户全称 流水序号 三个参数
aQuery.SQL.Add(
Format('update t_ch_invoice_bankdata set 揽货人=( '
+' select 揽货人 from t_crm_client c where c.客户简称=''%s'' '
+' or c.客户全称=''%s'') where 序号=%s',[kh1,khead1,xh])
);
try
frm_main.db.BeginTrans;
//提交编号='';
aQuery.ExecSQL;
frm_main.db.CommitTrans;
seachPrintList();
except
on ex: Exception do
begin
frm_main.db.RollbackTrans;
Application.messagebox(pchar('提交发生异常:'+ex.Message),'信息提醒',MB_ICONWARNING );
end;
end;
//
end;
procedure Tfrm_ch_invoice_bankdata.btn6_cancelMatchClick(
Sender: TObject);
var
str,xh,fph,strinfo:string;
i,icount:Integer;
aQuery:TADOQuery;
begin
// 获取提交的批ID
if(qry1_bankdata.Recordset.RecordCount<1) then
Exit;
if(not qry1_bankdata.fieldbyname('是否匹配').AsBoolean) then
Exit;
//
aQuery:=CreateAdoQuery;
icount:=0;
//
xh:= qry1_bankdata.fieldbyname('序号').AsString;
//fph:= qry2_bankdata_billno.fieldbyname('发票号').AsString;
strinfo:='是否要取消批次为"'+ qry1_bankdata.fieldbyname('批次').AsString
+'"顺序号为"' + qry1_bankdata.fieldbyname('顺序号').AsString + '"的银行流水吗?';
if(application.MessageBox(pchar(strinfo),'信息提示', MB_ICONQUESTION+MB_YESNO) <> ID_YES) then
Exit;
try
str:='update t_ch_invoice set 是否匹配银行流水=0 where 发票号码 in '
+' ( select 发票号码 from t_ch_invoice_bankdata_billno where 序号=%s); ' +#13#10
+'delete from t_ch_invoice_bankdata_billno where 序号=%s;' +#13#10
+' update t_ch_invoice_bankdata set 是否匹配=0,匹配日期=null,匹配类型=null,揽货人=null where 序号=%s; ';
str:=format(Str,[xh,xh,xh]);
frm_main.db.BeginTrans;
//
aQuery.close;
aQuery.sql.Clear;
aQuery.SQL.Add(str);
i:= aQuery.ExecSQL;
if(i<0 ) then
showmessage(inttostr(i));
frm_main.db.CommitTrans;
seachPrintList();
except
on ex: Exception do
begin
frm_main.db.RollbackTrans;
Application.messagebox(pchar('取消银行流水发生异常:'+ex.Message),'信息提醒',MB_ICONWARNING );
end;
end;
end;
function Tfrm_ch_invoice_bankdata.checkImport(fkrzh:string;jylsh:string):Boolean;
var
str,strinfo:string;
i,noticecount:integer;
aQuery:TADOQuery;
pc,shxh:string;
begin
Result:=true;
str:='select * from t_ch_invoice_bankdata where 付款人账号=''%s'' and 交易流水号=''%s''';
aQuery:=CreateAdoQuery();
with aQuery do
begin
Close;
SQL.Clear;
SQL.Add(Format(str,[fkrzh,jylsh]));
Open;
if(not IsEmpty) then
begin
First;
strinfo:='要导入的数据[付款人账号]=%s、交易流水号=%s 已经存在,'
+'与入库的批次为%s、顺序号为%s关键信息重复!'+#13
+'请确认您是否已经导入了该银行流水!'+ #13#10
+'仍继续导入请选"是",否则请关闭窗口或者选"否"取消导入!'+ #13#10
;
pc:=fieldbyname('批次').asstring;
shxh:=fieldbyname('顺序号').asstring;
if(Application.messagebox(pchar(Format(strinfo,[fkrzh,jylsh,pc,shxh]) ) ,'信息提醒',MB_ICONQUESTION+mb_yesno )<>id_yes) then
result:=false;
end;
Close;
end;
end;
///
/// 导入银行流水
///
procedure Tfrm_ch_invoice_bankdata.btn3ImportExcelClick(Sender: TObject);
var
Excel: OLEVariant;
ExcelVersion,filename,strSheet1name,strbatid,strtmp: string;
dlg:TOpenDialog;
i,ifieldcount,istartLine,ititle:integer;
blChineseBank,blmerchants:Boolean;
f_skrzh_4m :string;//收款人银行 7
f_skrzh_4 :string;//收款人账户 8
f_skrmc_5 :string;//收款人名称 9
//6
f_bb_7 :string;//币别 12
imOK:Boolean;
begin
blChineseBank:=False;
dlg:=TOpenDialog.Create(Self);
dlg.Filter:='Excel 文档(*.xls,*.xlsx)|*.xls;*.xlsx';
if( dlg.Execute()=false) then
Exit;
qry1Excel.Close;
connExcel.Close;
filename:=dlg.filename;
try
Excel := CreateOLEObject('EXCEL.Application');
ExcelVersion := Excel.version;
Excel.WorkBooks.Open( filename);
strSheet1name:= Excel.WorkSheets[1].Name;
//edt1.Text:=strSheet1name;
finally
Excel.Quit;
Excel := UnAssigned;
end;
if ExcelVersion = '11.0' then
//Excel2003及早期的版本
ConnExcel.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+
filename+';Extended Properties=excel 8.0;'+
'Persist Security Info=false;'
else //Excel2007及以后的版本
ConnExcel.ConnectionString:='Provider=Microsoft.ACE.OLEDB.12.0;Data Source='+
filename+';Extended Properties=excel 12.0;'+
'Persist Security Info=True';//注意不能为false
try
ConnExcel.LoginPrompt:=false;
ConnExcel.Connected:=true;
qry1Excel.Connection:=ConnExcel;
qry1Excel.close;
qry1Excel.SQL.Clear;
qry1Excel.SQL.add('select * from ['+strSheet1name+'$]');
qry1Excel.Active:=true;
ifieldcount:= qry1Excel.Recordset.Fields.Count;
//处理数据
if(ifieldcount>=30 )then
begin
//
qry1Excel.Last;
qry1Excel.First;
i:=0;
f_skrzh_4m:='';
if(qry1Excel.Recordset.RecordCount>=9) then
with qry1Excel do
begin
for i:=0 to 12 do
begin
// strtmp:=qry1Excel.Recordset.f fields[0].asstring;
strtmp:=qry1Excel.Fields[0].AsString;
if copy(Trim(strtmp),0,8)='交易类型' then
begin
ititle:=i;
blChineseBank:=True;
break;
end;
// 4行
if copy(Trim(strtmp),0,4)='标题' then
begin
//ititle:=i;
f_skrzh_4m:= qry1Excel.Fields[3].AsString;
ShowMessage(strtmp);
blmerchants:=True;
// break;
end;
if copy(Trim(strtmp),0,4)='接口' then
begin
//ititle:=i;
f_skrzh_4:= qry1Excel.Fields[5].AsString;
blmerchants:=True;
// break;
end;
if (copy(Trim(strtmp),0,4)='帐户') or (copy(Trim(strtmp),0,4)='账户') then
begin
//ititle:=i;
f_bb_7:= qry1Excel.Fields[1].AsString ;
blmerchants:=True;
end;
if blmerchants and (copy(Trim(strtmp),0,6)='交易日' ) then
begin
if(f_skrzh_4m='') then
f_skrzh_4m:='招商银行云霄路支行';
break;
end;
Next;
end;
end;
end;
sp_bankflow_batid.ExecProc;
strbatid:=sp_bankflow_batid.Parameters.ParamByName('@str').value;
if(blChineseBank or blmerchants ) then
begin
try
frm_main.db.BeginTrans;
if blChineseBank then
imOK:= importdata( strbatid)
else
imOK:= importdata_zhsh(strbatid,f_skrzh_4m,f_skrzh_4,f_bb_7);
if(imOK) then
begin
frm_main.db.CommitTrans;
Application.MessageBox('导入成功','信息提示',MB_ICONINFORMATION);
qry4_pc.Active:=False;
qry4_pc.Active:=true;
qry1_bankdata.Requery( );
end
else
begin
frm_main.db.RollbackTrans;
end;
except
on ex:Exception do
begin
frm_main.db.RollbackTrans;
errorlog(ex.Message);
ShowMessage('请确认导入格式是否正确,导入过程中发生错误:'+ex.Message);
end;
end;
end
else
begin
ShowMessage('您选择的文件无法识别,请确认您选择的文件是否正确!');
end;
//ShowMessage(IntToStr(qry1Excel.Recordset.RecordCount));
finally
qry1Excel.close();
connExcel.Close();
end;
end;
///
/// 导入中国银行 默认
///
function Tfrm_ch_invoice_bankdata.importdata(batid:string):Boolean;
var
empQuery:TADOQuery;
strtmp:string;
f_jylx_1 :string;//交易类型 0
f_fkrzh_2m :string;//付款人银行 3
f_fkrzh_2 :string;//付款人账号 4
f_fkrmc_3 :string;//付款人名称 5
f_skrzh_4m :string;//收款人银行 7
f_skrzh_4 :string;//收款人账户 8
f_skrmc_5 :string;//收款人名称 9
f_jyrq_6 :string;//交易日期 10
f_jyrq_6t :string;//交易时间 11
f_bb_7 :string;//币别 12
f_je_8 :string;//金额 13
f_jylsh_9 :string;//交易流水号 17
f_jyfy_10 :string;//交易附言 25
f_bz_11 :string;//备注 26
f_zy_12 :string;//备注 23
ino:Integer;//顺序号
begin
//中国银行,9行未标题行
result:=False;
if(qry1Excel.IsEmpty or (qry1Excel.Recordset.RecordCount<9)) then
Exit;
//验证第9行是否是标题行
empQuery:=CreateAdoQuery();
ino:=1;
with empQuery do
begin
Close;
SQL.Clear;
SQL.Add('select * from [t_ch_invoice_bankdata] where 1=2');
Open;
while not qry1Excel.Eof do
begin
strtmp:=qry1Excel.Fields[0].AsString;
//不过滤交易类型
if( strtmp='来账') then
begin
f_jylx_1 :=qry1Excel.Fields[0].AsString;
f_fkrzh_2m:=qry1Excel.Fields[3].AsString;
f_fkrzh_2:=qry1Excel.Fields[4].AsString;
f_fkrmc_3:=qry1Excel.Fields[5].AsString;
f_skrzh_4m:=qry1Excel.Fields[7].AsString;
f_skrzh_4:=qry1Excel.Fields[8].AsString;
f_skrmc_5:=qry1Excel.Fields[9].AsString;
f_jyrq_6 :=qry1Excel.Fields[10].AsString;
f_jyrq_6t:=qry1Excel.Fields[11].AsString;
f_bb_7 :=qry1Excel.Fields[12].AsString;
f_je_8 :=qry1Excel.Fields[13].AsString;
f_jylsh_9:=qry1Excel.Fields[17].AsString;
f_jyfy_10:=qry1Excel.Fields[25].AsString;
f_bz_11:=qry1Excel.Fields[26].AsString;
f_zy_12:=qry1Excel.Fields[23].AsString;
//检查是否已经导入过
if(checkImport(f_fkrzh_2,f_jylsh_9)=False) then
begin
Result:=false;
Cancel;
//Break;
Close;
qry1Excel.Close;
connExcel.Close;
Exit;
end;
Insert;
FieldByName('批次').AsString:=batid;
FieldByName('顺序号').AsInteger:=ino;
FieldByName('交易类型').AsString:=f_jylx_1;
FieldByName('付款人开户行').AsString:=f_fkrzh_2m;
FieldByName('付款人账号').AsString:=f_fkrzh_2;
FieldByName('付款人名称').AsString:=f_fkrmc_3;
FieldByName('收款人开户行').AsString:=f_skrzh_4m;
FieldByName('收款人账号').AsString:=f_skrzh_4;
FieldByName('收款人名称').AsString:=f_skrmc_5;
FieldByName('交易日期').AsDateTime:=
EncodeDate(StrToInt(Copy(f_jyrq_6,1,4)),StrToInt(Copy(f_jyrq_6,5,2)),StrToInt(Copy(f_jyrq_6,7,2)))
+ EncodeTime(StrToInt(Copy(f_jyrq_6t,1,2)),StrToInt(Copy(f_jyrq_6t,4,2)),StrToInt(Copy(f_jyrq_6t,7,2)),0);
FieldByName('币别').AsString:=f_bb_7;
FieldByName('金额').AsString:=StringReplace( f_je_8,',','',[rfreplaceall]);;
FieldByName('交易流水号').AsString:=f_jylsh_9;
FieldByName('交易附言').AsString:=f_jyfy_10;
FieldByName('备注').AsString:=f_bz_11;
FieldByName('摘要').AsString:=f_zy_12;
FieldByName('导入人').asstring :=employee;
FieldByName('导入日期').AsDateTime:=GetDbDatetime;
ino:=ino+1;
end;
qry1Excel.next;
end;
if(empQuery.State in [ dsEdit, dsInsert] ) then
empQuery.Post;
//employeePhone :=FieldByName('固定电话').AsString;
close();
end;
// connExcel.Close;
result:=True;
end;
///
/// 导入招商
///
function Tfrm_ch_invoice_bankdata.importdata_zhsh(batid:string;skrzh_4m:string;skrzh_4:string;bb_7:string):Boolean;
var
empQuery:TADOQuery;
strtmp,strtmp2:string;
f_jylx_1 :string;//交易类型 3
f_fkrzh_2m :string;//付款人银行 19
f_fkrzh_2 :string;//付款人账号 17
f_fkrmc_3 :string;//付款人名称 16
f_skrzh_4m :string;//收款人银行
f_skrzh_4 :string;//收款人账户 0,5
f_skrmc_5 :string;//收款人名称 -1,3
f_jyrq_6 :string;//交易日期 0
f_jyrq_6t :string;//交易时间 1
f_bb_7 :string;//币别 1,1
f_je_8 :string;//金额 13
f_jylsh_9 :string;//交易流水号 17
f_zy_10 :string;//交易流水号 17
ino:Integer;//顺序号
begin
Result:=false;
//中国银行,9行未标题行
if(qry1Excel.IsEmpty or (qry1Excel.Recordset.RecordCount<9)) then
Exit;
//验证第9行是否是标题行
ino:=1;
// ShowMessage(IntToStr(qry1Excel.Recordset.RecordCount));
empQuery:=CreateAdoQuery();
with empQuery do
begin
Close;
SQL.Clear;
SQL.Add('select * from [t_ch_invoice_bankdata] where 1=2');
Open;
qry1Excel.First;
while not qry1Excel.Eof do
begin
strtmp:=qry1Excel.Fields[6].AsString;
strtmp2:= qry1Excel.Fields[0].AsString;
//取消匹配条件(张晨源/齐天) 程建波 2017-07-13 ='提回对公户收款') then
if( Length(strtmp2)>2) and ((copy(Trim(strtmp2),1,2)='53')) then
begin
f_jylx_1 :=strtmp;//qry1Excel.Fields[3].AsString;
f_fkrzh_2m:=qry1Excel.Fields[22].AsString;
f_fkrzh_2:=qry1Excel.Fields[20].AsString;
f_fkrmc_3:=qry1Excel.Fields[19].AsString;
f_skrzh_4m:=skrzh_4m;//qry1Excel.Fields[7].AsString;
f_skrzh_4:=skrzh_4;//qry1Excel.Fields[8].AsString;
f_skrmc_5:='山东捷丰国际储运有限公司';//qry1Excel.Fields[9].AsString;
f_jyrq_6 :=qry1Excel.Fields[3].AsString;
f_jyrq_6:=StringReplace(f_jyrq_6,'-','',[rfReplaceAll]);
f_jyrq_6t:=qry1Excel.Fields[4].AsString;
f_bb_7 :=bb_7;//qry1Excel.Fields[12].AsString;
f_je_8 :=qry1Excel.Fields[8].AsString;
f_jylsh_9:=qry1Excel.Fields[11].AsString;
f_zy_10:=qry1Excel.Fields[10].AsString;
f_skrzh_4:=qry1Excel.Fields[0].AsString;
f_skrzh_4m:='招商银行云霄路支行';
//检查是否已经导入过
if(checkImport(f_fkrzh_2,f_jylsh_9)=False) then
begin
Result:=false;
//Break;
cancel;
Close;
qry1Excel.Close;
connExcel.Close;
Exit;
end;
Insert;
FieldByName('批次').AsString:=batid;
FieldByName('顺序号').AsInteger:=ino;
FieldByName('交易类型').AsString:=f_jylx_1;
FieldByName('付款人开户行').AsString:=f_fkrzh_2m;
FieldByName('付款人账号').AsString:=f_fkrzh_2;
FieldByName('付款人名称').AsString:=f_fkrmc_3;
FieldByName('收款人开户行').AsString:=f_skrzh_4m;
FieldByName('收款人账号').AsString:=f_skrzh_4;
FieldByName('收款人名称').AsString:=f_skrmc_5;
if (Length(f_jyrq_6t)=6) then begin
FieldByName('交易日期').AsDateTime:=
EncodeDate(StrToInt(Copy(f_jyrq_6,1,4)),StrToInt(Copy(f_jyrq_6,5,2)),StrToInt(Copy(f_jyrq_6,7,2)))
+ EncodeTime(StrToInt(Copy(f_jyrq_6t,1,2)),StrToInt(Copy(f_jyrq_6t,3,2)),StrToInt(Copy(f_jyrq_6t,5,2)),0);
end else begin
FieldByName('交易日期').AsDateTime:=
EncodeDate(StrToInt(Copy(f_jyrq_6,1,4)),StrToInt(Copy(f_jyrq_6,5,2)),StrToInt(Copy(f_jyrq_6,7,2)))
+ EncodeTime(StrToInt(Copy(f_jyrq_6t,1,2)),StrToInt(Copy(f_jyrq_6t,4,2)),StrToInt(Copy(f_jyrq_6t,7,2)),0);
end;
FieldByName('币别').AsString:=f_bb_7;
FieldByName('金额').AsString:=StringReplace( f_je_8,',','',[rfreplaceall]);
FieldByName('交易流水号').AsString:=f_jylsh_9;
FieldByName('摘要').AsString:=f_zy_10;
FieldByName('导入人').asstring :=employee;
FieldByName('导入日期').AsDateTime:=GetDbDatetime;
ino:=ino+1;
end;
qry1Excel.next;
end;
if(empQuery.State in [ dsEdit, dsInsert] ) then
empQuery.Post;
//employeePhone :=FieldByName('固定电话').AsString;
close();
end;
Result:=True;
end;
procedure Tfrm_ch_invoice_bankdata.btn1_unmatchedSearchClick(
Sender: TObject);
var
str:string;
begin
//
str:=' where isnull(是否匹配银行流水,0)=0 ' ;
if(cbb1_custmer3.DisplayValues[1]<>'' ) then
str:= str+' AND 客户名称=''' +TRIM( cbb1_custmer3.DisplayValues[1]) +'''';
if(wdblkpcmb1_billHead.text<>'' ) then
str:= str+' AND 发票抬头 like''%' +TRIM( wdblkpcmb1_billHead.text) +'%''';
if wdbdtmpckr1_billdate.text<>''then
str:=str+' and 开票日期>='+''''+wdbdtmpckr1_billdate.text+'''';
if wdbdtmpckr2_billdate.text<>''then
str:=str+' and 开票日期<='+''''+wdbdtmpckr2_billdate.text+'''';
if Trim( edt3_billNo.text)<>''then
str:=str+' AND 发票号码 like '+''''+'%'+ stringreplace(Trim(edt3_billNo.text),'''','''''',[rfReplaceAll]) +'%'+'''';
str:='select * from t_ch_invoice ' +str;
with qry3_unmatched do
begin
Close;sql.Clear;
sql.add(str);
Open;First;
end;
end;
procedure Tfrm_ch_invoice_bankdata.btn4_autoMatchClick(Sender: TObject);
var
str:string;
i:integer;
begin
str:='DECLARE @return_value int ' +#13#10
+'EXEC @return_value = [dbo].[p_invoice_bankflow_bill_deal] '+#13#10
+'SELECT ''Return Value'' = @return_value';
try
frm_main.db.BeginTrans;
frm_main.db.Execute(str);
frm_main.db.CommitTrans;
qry1_bankdata.Requery( );
Application.MessageBox(pchar('自动匹配完成'),'信息提示',MB_ICONINFORMATION);
except
on ex:Exception do
begin
frm_main.db.RollbackTrans;
Application.MessageBox(pchar('执行自动失败发生错误'+ ex.message),'信息提示',MB_ICONWARNING);
end;
end;
end;
procedure Tfrm_ch_invoice_bankdata.dxdbgrd1CustomDraw(Sender: TObject;
ACanvas: TCanvas; ARect: TRect; ANode: TdxTreeListNode;
AColumn: TdxDBTreeListColumn; const AText: String; AFont: TFont;
var AColor: TColor; ASelected, AFocused: Boolean; var ADone: Boolean);
var
value:string;
begin
value:=ANode.Strings[dxdbgrdchckclmnColumn16_SHFPP.Index];
if value ='True' then
begin
AFont.Color:=clgreen;
end
else
AFont.Color:=clblack;
end;
procedure Tfrm_ch_invoice_bankdata.btn2_clearSeachTJ2Click(
Sender: TObject);
begin
cbb1_custmer3.DisplayValue:='';
wdblkpcmb1_billHead.text:='';
edt3_billNo.text:='';
wdbdtmpckr1_billdate.text:='';
wdbdtmpckr2_billdate.text:='';
end;
procedure Tfrm_ch_invoice_bankdata.btn11Click(Sender: TObject);
var
strpc:string;
strsql,str:string;
aQuery:TADOQuery;
i:Integer;
begin
//
strpc:='-1';
if(cbb1_import_BatNo.DisplayValues[1]<>'') then
strpc:= Trim(cbb1_import_BatNo.DisplayValues[1])
else
begin
if(qry1_bankdata.Recordset.RecordCount>0) then
strpc :=qry1_bankdata.fieldbyname('批次').AsString;
end;
// 检查是否完全匹配
strsql:='select sum(case when t.[是否匹配]=1 then 1 else 0 end) as pcount,'
+ ' sum(case when t.[是否匹配]=0 then 1 else 0 end) as npcount '
+ ' from t_ch_invoice_bankdata t where t.批次= ' + strpc;
aQuery:=CreateAdoQuery;
with aQuery do
begin
Close;SQL.Clear;
SQL.Add(strsql);
Open;
if( FieldByName('npcount').AsInteger >0) then
if(Application.MessageBox('注意：打印后会自动将未匹配的银行流水标记匹配状态！'
+#13#10+'是否要继续？','打印并匹配',MB_YESNO+mb_iconwarning)<> id_yes) then
begin
close;
Exit;
end;
close;
SQL.Clear;
end;
try
frm_main.db.BeginTrans;
with aQuery do
begin
SQL.Add(format('update t_ch_invoice_bankdata set [是否匹配]=1,[匹配类型]=''强制匹配'' where 批次=%s and ISNULL([是否匹配],0)=0;',[strpc]) );
SQL.Add(Format('update t_ch_invoice_bankdata set 是否打印=1, 打印日期=GETDATE(),打印人=''%s'' '
+'where 批次 =%s ;',[employee,strpc]));
i:= ExecSQL;
if(i<0) then
ShowMessage('更新0条记录!');
end;
frm_main.db.CommitTrans;
except
on ex:Exception do
begin
str:= '更新流水打印状态时发生错误:'+ex.Message ;
errorlog(str);
Application.MessageBox(PChar(str),'发生异常',mb_iconwarning );
end;
end;
qry5_report.Active:=False;
qry5_report.Parameters[0].Value:=strpc;
qry5_report.Active:=true;
sys_print('银行流水对账明细',3,ds5_report,ds1_bankdata,ds2_bankdata_billno,nil,nil,nil,nil,nil,nil,nil,nil);
seachPrintList();
end;
///
/// 设定打印格式
///
procedure Tfrm_ch_invoice_bankdata.btn3_custprintClick(Sender: TObject);
var
strpc:string;
begin
//
strpc:='-1';
if(qry1_bankdata.Recordset.RecordCount>0) then
strpc :=qry1_bankdata.fieldbyname('批次').AsString;
qry5_report.Active:=False;
qry5_report.Parameters[0].Value:=strpc;
qry5_report.Active:=true;
sys_print('银行流水对账明细',2,ds5_report,ds1_bankdata,ds2_bankdata_billno,nil,nil,nil,nil,nil,nil,nil,nil);
end;
procedure Tfrm_ch_invoice_bankdata.cbb1_import_BatNoChange(
Sender: TObject);
begin
seachPrintList();
end;
procedure Tfrm_ch_invoice_bankdata.cxchckbx1PropertiesChange(
Sender: TObject);
begin
seachPrintList();
end;
procedure Tfrm_ch_invoice_bankdata.btn4_cancalImportClick(Sender: TObject);
var
i:Integer;
str,pc:string;
aQuery:TADOQuery;
begin
//
if(cbb1_import_BatNo.DisplayValues[1]='') then
begin
Application.MessageBox('请选择要取消导入的批次','信息提示',MB_ICONINFORMATION);
Exit;
end;
str:='select COUNT(1) as ncount from t_ch_invoice_bankdata_billno t where '
+' exists (select 序号 from t_ch_invoice_bankdata where 批次=%s and 序号= t.序号)' ;
pc:= Trim(cbb1_import_BatNo.DisplayValues[1]);
str :=format(str ,[pc]);
aQuery:=CreateAdoQuery();
with aQuery do begin
close;SQL.Clear;
SQL.Add(str) ;
Open;
if(FieldByName('ncount').AsInteger>0) then
begin
Application.MessageBox(pchar('您选择要取消导入的批次'+pc+'已经有匹配的发票进行匹配,不能进行取消!'
+#13#10 +'或取消匹配后再进行取消导入!'),'信息提示',MB_ICONINFORMATION);
Close;
Exit;
end;
Close;
//取消
str:=format('delete from t_ch_invoice_bankdata where 批次=%s;' ,[pc]);
SQL.Clear;
SQL.Add(str);
i:=ExecSQL;
qry4_pc.Active:=False;
qry4_pc.Active:=true;
qry1_bankdata.Requery( );
end;
end;
procedure Tfrm_ch_invoice_bankdata.btn2_refreshPCClick(Sender: TObject);
begin
qry4_pc.Active:=false;
qry4_pc.Active:=True;
seachPrintList();
end;
procedure Tfrm_ch_invoice_bankdata.dxdbgrd1Click(Sender: TObject);
begin
customerUnmatchedBill();
end;
procedure Tfrm_ch_invoice_bankdata.customerUnmatchedBill();
var
str,bibie,khmch:string;
begin
if(qry1_bankdata.Recordset.RecordCount<1) then
Exit;
if(qry1_bankdata.FieldByName('是否匹配').IsNull)
or (qry1_bankdata.FieldByName('是否匹配').AsBoolean=False) then
begin
//
bibie:=qry1_bankdata.FieldByName('币别').AsString;
khmch:=qry1_bankdata.FieldByName('付款人名称').Asstring;
str:='SELECT * FROM t_ch_invoice c '
+' where isnull(c.是否匹配银行流水,0)=0 AND '
+' replace(replace(''%s'',char(9),''''),''人民币'',''RMB'')=c.币别 '
+' and rtrim(REPLACE(''%s'',CHAR(9),'''')) in( rtrim(c.客户名称),'
+' replace(replace(replace(rtrim(c.发票抬头),CHAR(10),''''),CHAR(13),''''),CHAR(9),''''))';
str:=Format(str,[bibie,khmch]);
with qry3_unmatched do
begin
Close;
SQL.Clear;
SQL.Add(str);
Open;
end;
end;
end;
procedure Tfrm_ch_invoice_bankdata.dxdbgrd1ChangeNode(Sender: TObject;
OldNode, Node: TdxTreeListNode);
begin
customerUnmatchedBill();
end;
procedure Tfrm_ch_invoice_bankdata.dxdbgrd1ColumnSorting(Sender: TObject;
Column: TdxDBTreeListColumn; var Allow: Boolean);
begin
Column_sort(Column,qry1_bankdata,'序号');
end;
end.