unit u_ch_fee_edi; interface uses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, ExtCtrls, bsSkinCtrls, Grids, Wwdbigrd, Wwdbgrid, dxExEdtr, DB, ADODB, dxCntner, dxTL, dxDBCtrl, dxDBGrid, dxDBTLCl, dxGrClms, StdCtrls, dxGrClEx; type TfrmCh_fee_edi = class(TForm) bsknpnl2: TbsSkinPanel; btn2Cancel: TbsSkinButton; btn1Import: TbsSkinButton; btn12: TbsSkinButton; pnl1: TPanel; dxdbgrd1_ch_fee_edi: TdxDBGrid; qry1_ch_fee_edi: TADOQuery; ds1_ch_fee_edi: TDataSource; dxdbgrdmskclmndxdbgrd1_ch_fee_ediCH_ID: TdxDBGridMaskColumn; dxdbgrdmskclmndxdbgrd1_ch_fee_edireportid: TdxDBGridMaskColumn; dxdbgrdmskclmndxdbgrd1_ch_fee_ediColumn3: TdxDBGridMaskColumn; dxdbgrdmskclmndxdbgrd1_ch_fee_ediColumn4: TdxDBGridMaskColumn; dxdbgrdmskclmndxdbgrd1_ch_fee_ediColumn5: TdxDBGridMaskColumn; dxdbgrdmskclmndxdbgrd1_ch_fee_ediColumn7: TdxDBGridMaskColumn; dxdbgrdmskclmndxdbgrd1_ch_fee_ediColumn8: TdxDBGridMaskColumn; dxdbgrdmskclmndxdbgrd1_ch_fee_ediColumn9: TdxDBGridMaskColumn; dxdbgrdmskclmndxdbgrd1_ch_fee_ediColumn13: TdxDBGridMaskColumn; dxdbgrdmskclmndxdbgrd1_ch_fee_ediColumn15: TdxDBGridMaskColumn; dxdbgrdtclmndxdbgrd1_ch_fee_ediColumn16: TdxDBGridDateColumn; dxdbgrdtclmndxdbgrd1_ch_fee_ediColumn17: TdxDBGridDateColumn; dxdbgrdmskclmndxdbgrd1_ch_fee_ediColumn18: TdxDBGridMaskColumn; dxdbgrdmskclmndxdbgrd1_ch_fee_ediColumn19: TdxDBGridMaskColumn; dxdbgrdmskclmndxdbgrd1_ch_fee_ediColumn20: TdxDBGridMaskColumn; dxdbgrdmskclmndxdbgrd1_ch_fee_ediColumn21: TdxDBGridMaskColumn; dxdbgrdmskclmndxdbgrd1_ch_fee_ediColumn22: TdxDBGridMaskColumn; lbl1: TLabel; qry1: TADOQuery; ds1: TDataSource; atncfldqry1_ch_fee_ediCH_ID: TAutoIncField; strngfldqry1_ch_fee_edireportid: TStringField; strngfldqry1_ch_fee_ediDSDesigner: TStringField; strngfldqry1_ch_fee_ediDSDesigner2: TStringField; strngfldqry1_ch_fee_ediDSDesigner3: TStringField; strngfldqry1_ch_fee_ediDSDesigner4: TStringField; strngfldqry1_ch_fee_ediDSDesigner5: TStringField; strngfldqry1_ch_fee_ediDSDesigner6: TStringField; strngfldqry1_ch_fee_ediDSDesigner7: TStringField; bcdfldqry1_ch_fee_ediDSDesigner8: TBCDField; bcdfldqry1_ch_fee_ediDSDesigner9: TBCDField; bcdfldqry1_ch_fee_ediDSDesigner10: TBCDField; strngfldqry1_ch_fee_ediDSDesigner11: TStringField; bcdfldqry1_ch_fee_ediDSDesigner12: TBCDField; strngfldqry1_ch_fee_ediDSDesigner13: TStringField; dtmfldqry1_ch_fee_ediDSDesigner14: TDateTimeField; dtmfldqry1_ch_fee_ediDSDesigner15: TDateTimeField; strngfldqry1_ch_fee_ediDSDesigner16: TStringField; strngfldqry1_ch_fee_ediDSDesigner17: TStringField; strngfldqry1_ch_fee_ediDSDesigner18: TStringField; strngfldqry1_ch_fee_ediDSDesigner19: TStringField; intgrfldqry1_ch_fee_ediDSDesigner20: TIntegerField; strngfldqry1DSDesigner: TStringField; strngfldqry1DSDesigner2: TStringField; strngfldqry1DSDesigner3: TStringField; dxdbgrdclmn_ch_fee_ediColumn6: TdxDBGridColumn; dxdbgrdclmndxdbgrdcrncyclmndxdbgrd1_ch_fee_ediColumn12: TdxDBGridColumn; dxdbgrdclmndxdbgrdcrncyclmndxdbgrd1_ch_fee_ediColumn10: TdxDBGridColumn; dxdbgrdclmndxdbgrdcrncyclmndxdbgrd1_ch_fee_ediColumn11: TdxDBGridColumn; dxdbgrdclmndxdbgrdcrncyclmndxdbgrd1_ch_fee_ediColumn14: TdxDBGridColumn; btn3ViewFee: TbsSkinButton; procedure btn2CancelClick(Sender: TObject); procedure btn1ImportClick(Sender: TObject); procedure btn3ViewFeeClick(Sender: TObject); private { Private declarations } m_qryTask:TADOQuery; m_blshowform:Boolean; public { Public declarations } procedure BindData(qrytask:TADOQuery;blshowform:Boolean); function DoImportData():Boolean; function CancelImportData():Boolean; end; var frmCh_fee_edi: TfrmCh_fee_edi; implementation uses u_main,u_op_seae_TaskContrast,my_sys_function; {$R *.dfm} procedure TfrmCh_fee_edi.BindData(qrytask:TADOQuery;blshowform:Boolean); var taskid:string; bsno,strtmp,strsql:string; ifinish:Integer; begin m_blshowform:=blshowform; m_qryTask:=qrytask; taskid:=qrytask.fieldbyname('任务编号').AsString; bsno:= qrytask.fieldbyname('SEA编号').AsString; ifinish:= m_qryTask.FieldByName('是否完成').AsInteger; btn1Import.Enabled:= True; btn2Cancel.Enabled:=true; if(bsno='') then btn1Import.Enabled:=False else begin if(ifinish=1) and (blshowform=True) then begin btn1Import.Enabled:=False; btn2Cancel.Enabled:=false; end else begin strtmp:=m_qryTask.fieldbyname('备注').AsString ; if( ifinish = 0) and ( strtmp<>'' ) and (Pos('重新匹配',strtmp)>0) then begin strsql:= Format('update t_ch_fee_edi set 编号=''%s'' where reportid=''%s'';', [bsno,taskid]) ; m_qryTask.Connection.Execute(strsql); end; end; end; //调试 // btn1Import.Enabled:=false; // //qry1.Active:=False; //qry1.Active:=True; lbl1.Caption:=qrytask.fieldbyname('任务说明').AsString; qry1_ch_fee_edi.Active:=False; qry1_ch_fee_edi.Parameters[0].Value:=taskid; qry1_ch_fee_edi.Active:=True; //导入前先补充 [编号] --处理自动匹配的情况 // ContrastTDNR(); end; procedure TfrmCh_fee_edi.btn2CancelClick(Sender: TObject); begin CancelImportData(); end; function TfrmCh_fee_edi.DoImportData():Boolean ; var strtmp,fphm,taskid:string; strsql,sqlpre:string; strfield1,strfield2:string; qrytmp:TAdoquery; begin //检查状态:导入检查发票号是否存在,已存在不允许导入 //检查删除: 1>op_seae 费用状态 为 费用封帐 和 提交财务的 也不允许导入费用 // 2> 结算金额/票金额/冲抵金额/申请金额 不为零 fphm:=''; if(not qry1_ch_fee_edi.IsEmpty) then begin qry1_ch_fee_edi.First; fphm:=qry1_ch_fee_edi.fieldbyname('发票号码').asstring; end; if( fphm='')then begin result:=False; Exit; end; if(lbl1.caption='更正发票') then begin qrytmp:=CreateAdoQuery; strsql:='select 费用状态, isnull(报检单号,'''') as 报检单号 from t_op_seae where 编号='''+ m_qryTask.fieldbyname('SEA编号').AsString +''' '; with qrytmp do begin close;sql.Clear; sql.Add(strsql); Open; if not IsEmpty then begin if(fieldbyname('报检单号').asstring<>'') then begin showmessage(format('该业务已经有报检单号,不能删除。',[strtmp])); result:=False; Exit; end; strtmp:= fieldbyname('费用状态').asstring; end; close; end; if( strtmp='费用封帐') or (strtmp ='提交财务') then begin showmessage(format('该业务已经%s,不能通过更正发票进行删除,请结束任务,然后手动处理。',[strtmp])); result:=False; Exit; end else begin //检查费用额度 strsql:='select 开票金额,结算金额,冲抵金额,申请金额 from t_ch_fee ' +format(' where msk发票号码=''%s''',[fphm]) +' and ( isnull(开票金额,0)<>0 or isnull(结算金额,0)<>0 or isnull(冲抵金额,0)<>0 or isnull(申请金额,0)<>0)'; with qrytmp do begin close;sql.Clear; sql.Add(strsql); Open; if not IsEmpty then begin ShowMessage('开票金额,结算金额,冲抵金额,申请金额 中存在不为0的项目,不能删除该票费用,请结束任务,然后手动处理。'); result:=False; Close; Exit; end; Close; end; // try if(m_blshowform=True) then m_qryTask.Connection.BeginTrans; strsql:=''; With qrytmp do begin close;SQL.Clear; sql.add(Format('select CH_ID from t_ch_fee where msk发票号码= ''%s'' ',[fphm])); open; if(not qrytmp.IsEmpty ) then begin while not eof do begin strsql :=strsql+'delete from t_ch_fee where CH_ID =' + qrytmp.fieldbyname('CH_ID').AsString + ';'; qrytmp.next; end; m_qryTask.Connection.Execute(strsql); end; close; end; sqlpre:='INSERT INTO t_op_task_state ( [任务编号],[任务状态],[操作人])VALUES (''%s'' ,''%s'' ,''%s'')'; strsql:=Format(sqlpre,[m_qryTask.fieldbyname('任务编号').asstring,'完成',employee] ); m_qryTask.Connection.Execute(strsql); if(m_blshowform) then begin //写任务 m_qryTask.Edit; m_qryTask.FieldByName('完成时间').AsDateTime:=Now; m_qryTask.FieldByName('是否完成').AsInteger:=1; m_qryTask.FieldByName('任务状态').AsString:='完成'; m_qryTask.post; m_qryTask.Connection.CommitTrans; m_qryTask.Requery(); Application.MessageBox(PChar('操作完成'),'信息提示'); end; except on e:Exception do begin // on except if(m_blshowform=True) and (m_qryTask.Connection.InTransaction=True) then m_qryTask.Connection.RollbackTrans; m_qryTask.Cancel(); Result:=False; Application.MessageBox(PChar('处理发生错误: '+e.Message),'信息提示'); end; end; // end; end else begin qrytmp:=CreateAdoQuery; strsql:='select 费用状态, isnull(报检单号,'''') as 报检单号 from t_op_seae where 编号='''+ m_qryTask.fieldbyname('SEA编号').AsString +''' '; with qrytmp do begin close;sql.Clear; sql.Add(strsql); Open; if not IsEmpty then begin strtmp:= fieldbyname('费用状态').asstring; // if( strtmp='费用封帐') or (strtmp ='提交财务') then begin showmessage(format('该业务已经%s,不能导入。',[strtmp])); result:=False; Exit; end else if(fieldbyname('报检单号').asstring<>'') then begin showmessage(format('该业务已经有报检单号,不能导入。',[strtmp])); result:=False; Exit; end; end; close; end; //判断是否已经导入过 //检查费用额度 strsql:='select CH_ID from t_ch_fee ' +format('where MSK发票号码=''%s'';',[fphm]) ; //qrytmp:=CreateAdoQuery; with qrytmp do begin close;sql.Clear; sql.Add(strsql); Open; if not IsEmpty then begin ShowMessage(Format('发票号码 %s 已存在,不能重复导入!',[fphm])); result:=False; Close; Exit; end; Close; end; try if(m_blshowform) then m_qryTask.Connection.BeginTrans; //币别是美元的全都算成海运费 strfield1:='类型,费用状态,编号,费用名称,结算单位,客户名称,标准,单价,数量,' +'金额,币别,汇率,备注,提交日期,录入人, 录入日期,费用英文名称,MSK发票号码 ,顺序' +',结算金额,开票金额,是否对帐,冲抵金额,机密,是否垫付,申请金额,不开发票,申请开票,提交,财务对帐'; //插入 strsql:=' insert into [t_ch_fee] ( '+ strfield1+ ' )' ; strfield2:='类型,''录入状态'',编号,case when 币别=''USD'' and 费用名称='''' THEN ''海运费'' else 费用名称 end as 费用名称,结算单位,客户名称,标准,' +'case when 币别=''USD'' then 金额/数量 else 单价 end as 单价,数量,' +'金额,币别,' +' case when 币别=''USD'' THEN isnull( (select 汇率 from t_code_usdexrate where 开始日期<= t.开船日期 and 结束日期>=t.开船日期 ),(select 汇率 from t_code_currency where 币别=''USD'') ) ELSE 汇率 END AS 汇率, ' +' 备注,提交日期,'''+ employee + ''' , getdate(),费用英文名称,发票号码 ,顺序' +',0,0,0,0,0,0,0,0,0,0,0'; strsql:= strsql + ' SELECT '+strfield2 + ' FROM ' +' [t_ch_fee_edi] f,(select 开船日期,编号 as 编号1 from t_op_seae ) t where f.编号=t.编号1 and reportid='''+ m_qrytask.fieldbyname('任务编号').asstring + ''';'; m_qryTask.Connection.Execute(strsql); if(m_blshowform) then begin sqlpre:='INSERT INTO t_op_task_state ( [任务编号],[任务状态],[操作人])VALUES (''%s'' ,''%s'' ,''%s'')'; strsql:=Format(sqlpre,[m_qryTask.fieldbyname('任务编号').asstring,'完成',employee] ); m_qryTask.Connection.Execute(strsql); //写任务 m_qryTask.Edit; m_qryTask.FieldByName('完成时间').AsDateTime:=Now; m_qryTask.FieldByName('是否完成').AsInteger:=1; m_qryTask.FieldByName('任务状态').AsString:='完成'; m_qryTask.post; m_qryTask.Connection.CommitTrans; m_qryTask.Requery(); Application.MessageBox(PChar('操作完成'),'信息提示'); frmOpSeae_TaskContrast.close; end; except on e:Exception do begin if(m_blshowform) then m_qryTask.Connection.RollbackTrans; m_qryTask.Cancel(); Result:=False; Application.MessageBox(PChar('处理发生错误: '+e.Message),'信息提示'); end; end; end; end; function TfrmCh_fee_edi.CancelImportData():Boolean; var sql,bsno,reportid,cnfields:string; begin if( Application.MessageBox(PChar('您确实要直接结束该任务吗?'+#13#10+'结束任务将直接关闭任务!,是否结束任务?'),'信息提示',MB_YESNO+ MB_ICONQUESTION)<>IDYES) then Exit; // try //** m_qryTask.Connection.BeginTrans; // m_qryTask.Edit; m_qryTask.FieldByName('完成时间').AsDateTime:=Now; m_qryTask.FieldByName('是否完成').AsInteger:=1; m_qryTask.FieldByName('任务状态').AsString:='结束'; m_qryTask.Post; // sql:='INSERT INTO t_op_task_state ( [任务编号],[任务状态],[操作人])VALUES (''%s'' ,''%s'' ,''%s'')'; sql:=Format(sql,[m_qryTask.fieldbyname('任务编号').asstring,'结束',employee] ); m_qryTask.Connection.Execute(sql); //** m_qryTask.Connection.CommitTrans; m_qryTask.Requery(); Application.MessageBox(PChar('操作完成'),'信息提示'); frmOpSeae_TaskContrast.close; except on e:Exception do begin //** m_qryTask.Connection.RollbackTrans; Result:=False; Application.MessageBox(PChar('处理发生错误: '+E.Message),'信息提示'); end; end; end; procedure TfrmCh_fee_edi.btn1ImportClick(Sender: TObject); begin // DoImportData(); end; procedure TfrmCh_fee_edi.btn3ViewFeeClick(Sender: TObject); var bsno:string; begin // bsno:=m_qryTask.fieldbyname('SEA编号').AsString; if(bsno<>'') then begin fee_view(bsno); end; end; end.