unit ACRDABA;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, rootg, DB, ADODB, frxClass, frxDBSet, ImgList, ComCtrls,
Buttons, Wwdbigrd, Grids, Wwdbgrid, StdCtrls, Mask, wwdbedit, ExtCtrls,
ToolWin, wwdbdatetimepicker, Wwdotdot, Wwdbcomb, DBCtrls, frxExportXLS,
Menus, cxControls, cxContainer, cxEdit, cxProgressBar;
type
TfrmACRDABA = class(Tfrmrootg)
QryMasterRDA01: TWideStringField;
QryMasterRDA02: TWideStringField;
QryMasterRDA03: TDateTimeField;
QryMasterRDA04: TWideStringField;
QryMasterRDA05: TWideStringField;
QryMasterRDA06: TDateTimeField;
QryMasterRDA07: TWideStringField;
QryMasterRDA08: TFloatField;
QryMasterRDA09: TWideStringField;
QryMasterRDA10: TWideStringField;
QryMasterRDA11: TWideStringField;
Label1: TLabel;
Label2: TLabel;
wwDBEdit2: TwwDBEdit;
Label7: TLabel;
wwDBComboBox1: TwwDBComboBox;
Label4: TLabel;
wwDBEdit3: TwwDBEdit;
SpeedButton1: TSpeedButton;
Label8: TLabel;
wwDBEdit5: TwwDBEdit;
wwDBEdit6: TwwDBEdit;
Label9: TLabel;
SpeedButton2: TSpeedButton;
wwDBComboBox2: TwwDBComboBox;
Label11: TLabel;
Label3: TLabel;
wwDBDateTimePicker3: TwwDBDateTimePicker;
wwDBDateTimePicker4: TwwDBDateTimePicker;
Label6: TLabel;
QryDetailRDB01: TWideStringField;
QryDetailRDB02: TWideStringField;
QryDetailRDB03: TWideStringField;
QryDetailRDB04: TWideStringField;
QryDetailRDB05: TWideStringField;
QryDetailRDB06: TWideStringField;
QryDetailRDB07: TWideStringField;
QryDetailRDB08: TWideStringField;
QryDetailRDB09: TWideStringField;
QryDetailRDB10: TWideStringField;
QryDetailRDB11: TFloatField;
QryDetailRDB12: TFloatField;
QryDetailRDB13: TWideStringField;
DBText1: TDBText;
Label5: TLabel;
wwDBEdit4: TwwDBEdit;
wwDBCBRDB03: TwwDBComboBox;
wwDBCDRDB04: TwwDBComboDlg;
QryDetailJEHJ: TFloatField;
QryDetailRDB14: TFloatField;
QryDetailWCXJE: TFloatField;
QryDetailRDB15: TFloatField;
QryDetailRDB17: TWideStringField;
QryDetailRDB18: TWideStringField;
Label10: TLabel;
wwDBEdit7: TwwDBEdit;
Label12: TLabel;
wwDBEdit8: TwwDBEdit;
QryMasterWCXJE: TFloatField;
QryMasterRDA12: TFloatField;
Label13: TLabel;
wwDBEdit9: TwwDBEdit;
QryMasterRDA13: TWideStringField;
QryMasterRDA14: TWideStringField;
QryMasterRDA15: TWideStringField;
QryMasterRDA16: TDateTimeField;
QryMasterRDA17: TWideStringField;
QryMasterRDA18: TDateTimeField;
QryMasterRDA19: TFloatField;
TabSheet3: TTabSheet;
Label16: TLabel;
Label17: TLabel;
Label18: TLabel;
Label19: TLabel;
Label15: TLabel;
Label20: TLabel;
Label21: TLabel;
wwDBComboBox3: TwwDBComboBox;
wwDBComboBox4: TwwDBComboBox;
wwDBDateTimePicker5: TwwDBDateTimePicker;
wwDBComboBox5: TwwDBComboBox;
wwDBDateTimePicker6: TwwDBDateTimePicker;
wwDBEdit10: TwwDBEdit;
wwDBEdit11: TwwDBEdit;
cmdZD: TToolButton;
QryMasterRDA20: TFloatField;
QryMasterRDA21: TWideStringField;
Label14: TLabel;
Label22: TLabel;
wwDBEdit12: TwwDBEdit;
wwDBEdit13: TwwDBEdit;
cmdGet: TSpeedButton;
PopupMenu1: TPopupMenu;
A41: TMenuItem;
N1: TMenuItem;
QryDetailRDB19: TFloatField;
QryDetailRDB20: TWideStringField;
QryDetailRDB21: TWideStringField;
QryDetailRDB22: TWideStringField;
QryDetailRDB23: TWideStringField;
QryMasterRDA22: TWideStringField;
cxProgressBar1: TcxProgressBar;
procedure FormDestroy(Sender: TObject);
procedure FormCreate(Sender: TObject);
procedure QryDetailAfterInsert(DataSet: TDataSet);
procedure QryDetailAfterDelete(DataSet: TDataSet);
procedure addClick(Sender: TObject);
procedure SpeedButton1Click(Sender: TObject);
procedure QryMasterRDA04Validate(Sender: TField);
procedure QryDetailCalcFields(DataSet: TDataSet);
procedure saveClick(Sender: TObject);
procedure SpeedButton2Click(Sender: TObject);
procedure conformClick(Sender: TObject);
procedure btnRefresh; override;
procedure findClick(Sender: TObject);
procedure QryMasterRDA11GetText(Sender: TField; var Text: String;
DisplayText: Boolean);
procedure QryMasterRDA07GetText(Sender: TField; var Text: String;
DisplayText: Boolean);
procedure wwDBGrid2CalcCellColors(Sender: TObject; Field: TField;
State: TGridDrawState; Highlight: Boolean; AFont: TFont;
ABrush: TBrush);
procedure QryMasterCalcFields(DataSet: TDataSet);
procedure wwDBComboBox3Change(Sender: TObject);
procedure PageControl1Change(Sender: TObject);
procedure cmdZDClick(Sender: TObject);
procedure editClick(Sender: TObject);
procedure deleClick(Sender: TObject);
procedure FormShow(Sender: TObject);
procedure sqlRefresh; override;
procedure refreshClick(Sender: TObject);
procedure cmdGetClick(Sender: TObject);
procedure printClick(Sender: TObject);
procedure A41Click(Sender: TObject);
procedure N1Click(Sender: TObject);
procedure wwDBCDRDB04CustomDlg(Sender: TObject);
procedure frxReport1BeforePrint(Sender: TfrxReportComponent);
procedure wwDBEdit3Exit(Sender: TObject);
private
{ Private declarations }
pgh: integer;
public
{ Public declarations }
end;
var
frmACRDABA: TfrmACRDABA;
implementation
uses cGlobal, filterf, DBHELP, ShowMsg, ACRECDHELP;
{$R *.dfm}
procedure TfrmACRDABA.FormDestroy(Sender: TObject);
begin
inherited;
frmACRDABA:=nil;
end;
procedure TfrmACRDABA.FormCreate(Sender: TObject);
begin
FrmName := 'ACRDABA';
FrmHDB := 'ACRDA';
FrmGDB := 'ACRDB';
FrmHID := 'RDA01';
FrmGID1 := 'RDB01';
FrmGID2 := 'RDB02';
pgh:=PageControl1.Height;
inherited;
end;
procedure TfrmACRDABA.QryDetailAfterInsert(DataSet: TDataSet);
begin
if Trim(QryMaster.FieldByName(FrmHID).AsString)<>'' then
begin
QryDetail.Edit;
QryDetail.FieldByName(FrmGID1).AsString:=QryMaster.FieldByName(FrmHID).AsString;
QryDetail.FieldByName(FrmGID2).AsString:=FormatFloat('0000',QryDetail.RecordCount+1);
end;
end;
procedure TfrmACRDABA.QryDetailAfterDelete(DataSet: TDataSet);
Var
i: string;
begin
i:=QryDetail.Bookmark;
try
QryDetail.DisableControls;
while not QryDetail.Eof do begin
QryDetail.Edit;
QryDetail.FieldByName(FrmGID2).AsString:= FormatFloat('0000',QryDetail.RecNo);
QryDetail.Next;
end;
finally
QryDetail.EnableControls;
end;
QryDetail.Bookmark:=i;
end;
procedure TfrmACRDABA.addClick(Sender: TObject);
begin
inherited;
QryMaster.FieldByName('RDA03').AsDateTime :=Date();
QryMaster.FieldByName('RDA06').AsDateTime :=Date();
QryMaster.FieldByName('RDA02').AsString :=dllUserName;
QryMaster.FieldByName('RDA07').AsString :='1';
QryMaster.FieldByName('RDA11').AsString :='1';
QryMaster.FieldByName('RDA14').AsString :='电汇';
QryMaster.FieldByName('RDA22').AsString :='N'; //销货收款标志
end;
procedure TfrmACRDABA.SpeedButton1Click(Sender: TObject);
Var
QryTmp:TADOQuery;
begin
inherited;
if not Assigned(frmDBHELP) then
frmDBHELP := TfrmDBHELP.Create(Application);
with frmDBHELP do
begin
DBHELP:=QryMaster;
strDBName:='ACRGA';
strCol:='RGA01 AS 客户编号,RGA03 AS 客户全称';
strWhere:=' 1=1 ';
strOrder:='客户编号';
strParaFieldCode:='客户编号';
strHelpFieldCode:='RDA04';
strParaFieldName:='客户全称';
strHelpFieldName:='RDA05';
ShowModal;
Free;
end;
try
QryTmp:=TADOQuery.Create(nil);
QryTmp.Connection:=DllAdoCn;
QryTmp.EnableBCD :=False;
QryTmp.Close;
QryTmp.SQL.Clear;
QryTmp.SQL.Add('SELECT RGA11 FROM ACRGA WHERE RGA01='+VarToSql(Trim(QryMaster.FieldByName('RDA04').AsString)));
QryTmp.Open;
QryMaster.FieldByName('RDA09').AsString := QryTmp.FieldByName('RGA11').AsString; //币种
finally
QryTmp.Close;
QryTmp.Free;
end;
if not GetSQLEmpty(' SELECT RDA04,SUM(ISNULL(RDA08,0.0)-ISNULL(RDA12,0.0)) AS YE '+
' FROM ACRDA '+
' WHERE RDA11=''2'' AND RDA04='+VarToSql(Trim(QryMaster.FieldByName('RDA04').AsString))+
' GROUP BY RDA04 '+
' HAVING SUM(ISNULL(RDA08,0.0)-ISNULL(RDA12,0.0))>0 ') then
begin
Application.MessageBox('此客户有预收款未冲销,请冲销后再进行收款!','提示信息',MB_IconInformation+MB_OK);
abort;
end;
end;
procedure TfrmACRDABA.QryMasterRDA04Validate(Sender: TField);
Var
QryTmpA:TADOQuery;
begin
inherited;
try
QryTmpA:=TADOQuery.Create(nil);
QryTmpA.Connection:=DllAdoCn;
QryTmpA.EnableBCD :=False;
QryTmpA.Close;
QryTmpA.SQL.Clear;
QryTmpA.SQL.Add('SELECT RGA03,RGA11 FROM ACRGA WHERE RGA01='+VarToSql(Trim(QryMaster.FieldByName('RDA04').AsString)));
QryTmpA.Open;
QryMaster.FieldByName('RDA05').AsString := Trim(QryTmpA.FieldByName('RGA03').AsString); //全称
QryMaster.FieldByName('RDA09').AsString := Trim(QryTmpA.FieldByName('RGA11').AsString); //币种
finally
QryTmpA.Close;
QryTmpA.Free;
end;
end;
procedure TfrmACRDABA.QryDetailCalcFields(DataSet: TDataSet);
begin
inherited;
if QryDetail.FieldByName('RDB03').AsString <>'4' Then
begin
QryDetail.FieldByName('JEHJ').AsFloat := StrToFloat(formatfloat('0.00',QryDetail.FieldByName('RDB11').AsFloat*QryDetail.FieldByName('RDB12').AsFloat+QryDetail.FieldByName('RDB19').AsFloat));
QryDetail.FieldByName('WCXJE').AsFloat := StrToFloat(formatfloat('0.00',QryDetail.FieldByName('JEHJ').AsFloat - QryDetail.FieldByName('RDB15').AsFloat- QryDetail.FieldByName('RDB14').AsFloat));
end;
end;
procedure TfrmACRDABA.saveClick(Sender: TObject);
Var
vCXJE:Double;
vbk:String;
begin
wwDBEdit1.SetFocus;
if Trim(wwDBDateTimePicker4.Text)='' Then
begin
Application.MessageBox('收款日期不能为空!','提示信息',MB_IconInformation+MB_OK);
abort;
end;
if Trim(wwDBEdit3.Text)='' Then
begin
Application.MessageBox('客户编号不能为空!','提示信息',MB_IconInformation+MB_OK);
abort;
end;
if Trim(wwDBEdit6.Text)='' Then
begin
Application.MessageBox('币种不能为空!','提示信息',MB_IconInformation+MB_OK);
abort;
end;
//对账单号、序号不能为空
vbk:='N';
if not QryDetail.IsEmpty Then
begin
if QryDetail.State in [dsEdit,dsInsert] then
begin
QryDetail.Post;
end;
QryDetail.Edit;
QryDetail.DisableControls;
QryDetail.First;
While not QryDetail.Eof do
begin
if ((Trim(QryDetail.FieldByName('RDB20').AsString)='') or (Trim(QryDetail.FieldByName('RDB21').AsString)='')) AND (Trim(QryDetail.FieldByName('RDB03').AsString)<>'4') then
begin
vbk:='Y';
Break;
end;
QryDetail.Next;
end;
QryDetail.EnableControls;
end;
if vbk='Y' then
begin
application.MessageBox('表身对账单单号、序号为空，不能保存！','信息提示',mb_ok+mb_iconinformation);
abort;
end;
//更新金额
if not QryDetail.IsEmpty Then
begin
if QryDetail.State in [dsEdit,dsInsert] then
begin
QryDetail.Post;
end;
if QryDetail.RecordCount>0 then
begin
vCXJE :=0.0;
QryDetail.DisableControls;
QryDetail.First;
While not QryDetail.Eof do
begin
vCXJE:=vCXJE+QryDetail.fieldbyname('RDB14').AsFloat;
QryDetail.Next;
end;
QryDetail.EnableControls;
end
else
begin
vCXJE:=0;
end;
QryMaster.FieldByName('RDA12').AsFloat :=StrToFloat(FormatFloat('0.00',vCXJE)); //本次冲销总金额
QryDetail.Edit;
end
else
begin
QryMaster.FieldByName('RDA12').AsFloat := 0.0;
end;
if QryMaster.FieldByName('WCXJE').AsFloat <0 Then
begin
Application.MessageBox('本次收款金额不能小于本次冲销金额!','提示信息',MB_IconInformation+MB_OK);
abort;
end;
PageControl1.ActivePage :=TabSheet1;
inherited;
end;
procedure TfrmACRDABA.SpeedButton2Click(Sender: TObject);
begin
inherited;
if not Assigned(frmDBHELP) then
frmDBHELP := TfrmDBHELP.Create(Application);
with frmDBHELP do
begin
DBHELP:=QryMaster;
strDBName:='CMSMF';
strCol:='MF001 AS 币种编号,MF002 AS 币种名称';
strWhere:=' 1=1 ';
strOrder:='币种编号';
strParaFieldName:='币种编号';
strHelpFieldName:='RDA09';
ShowModal;
Free;
end;
end;
procedure TfrmACRDABA.conformClick(Sender: TObject);
Var
vRDA06:Variant;
vRDA14:String;
begin
//判断是否平衡(不平衡时转预收款)
if QryMaster.FieldByName('WCXJE').AsFloat <>0 Then
begin
Application.MessageBox('本次收款金额与本次冲销金额不一样,请将多余的金额转成预收款!','提示信息',MB_IconInformation+MB_OK);
abort;
end;
if QryMaster.FieldByName('RDA06').IsNull then
begin
vRDA06 := null;
end
else
begin
vRDA06 := QryMaster.FieldByName('RDA06').AsVariant;
end;
vRDA14 := QryMaster.FieldByName('RDA14').AsString;
inherited;
if Trim(QryMaster.FieldByName('CONFORM_CODE').AsString)='Y' Then
begin
try
if not dlladocn.InTransaction then
dlladocn.BeginTrans;
QryDetail.First;
cxProgressBar1.Visible :=True;
cxProgressBar1.Position :=0;
cxProgressBar1.Properties.Min :=0;
cxProgressBar1.Properties.Max :=QryDetail.RecordCount;
cxProgressBar1.Properties.Text :='正在处理数据中。。。';
while not QryDetail.Eof do
begin
if Trim(QryDetail.FieldByName('RDB20').AsString) <>'' Then
begin
//1、更新对账单单身 已收款、实际收款日期、收款方式
ExecuteSQL('UPDATE ACRED SET RED19=ISNULL(RED19,0.0)+'+VarToSql(QryDetail.FieldByName('RDB14').AsFloat)+',RED24='+VarToSql(vRDA06)+',RED22='+VarToSql(vRDA14)+' WHERE RED01='+VarToSql(Trim(QryDetail.FieldByName('RDB20').AsString))+' AND RED02='+VarToSql(Trim(QryDetail.FieldByName('RDB21').AsString)));
//2、更新对帐单表头已收款金额、已开票金额
ExecuteSQL(' UPDATE ACREC SET ACREC.REC13=AA.YSK '+
' FROM ACREC,(SELECT REC01,SUM(ISNULL(RED19,0.0)) AS YSK '+
' FROM ACREC '+
' INNER JOIN ACRED ON ACREC.REC01=ACRED.RED01 '+
' WHERE ACRED.RED01='+VarToSql(Trim(QryDetail.FieldByName('RDB20').AsString))+
' GROUP BY REC01) AS AA WHERE ACREC.REC01=AA.REC01');
//3、更新对帐单表头锁定码
ExecuteSQL(' UPDATE ACREC SET REC15=(CASE WHEN (ISNULL(REC10,0.0)-ISNULL(REC13,0.0)=0) AND (ISNULL(REC10,0.0)-ISNULL(REC14,0.0)=0.0) THEN ''Y'' ELSE ''N'' END ) WHERE REC01='+VarToSql(Trim(QryDetail.FieldByName('RDB20').AsString)));
end;
if Trim(QryDetail.FieldByName('RDB03').AsString)='4' Then
begin
//4、更新开账单
ExecuteSQL(' UPDATE ACRFB SET RFB11=ISNULL(RFB11,0.0)+'+VarToSql(QryDetail.FieldByName('RDB14').AsFloat)+' WHERE RFB03='+VarToSql(Trim(QryMaster.FieldByName('RDA04').AsString)));
end;
if Trim(QryDetail.FieldByName('RDB03').AsString)='1' Then
begin
//5、更新销货单已收款金额、转应收金额
ExecuteSQL(' UPDATE ACRAB SET RAB33=ISNULL(RAB33,0.0)+'+VarToSql(QryDetail.FieldByName('RDB14').AsFloat)+',RAB37=ISNULL(RAB37,0.0)-'+VarToSql(QryDetail.FieldByName('RDB14').AsFloat)+' WHERE RAB03='+VarToSql(Trim(QryDetail.FieldByName('RDB04').AsString)+'-'+Trim(QryDetail.FieldByName('RDB05').AsString)+'-'+Trim(QryDetail.FieldByName('RDB06').AsString)));
end;
if Trim(QryDetail.FieldByName('RDB03').AsString)='2' Then
begin
//6、更新销退单已收款金额、转应收金额
ExecuteSQL(' UPDATE ACRBB SET RBB34=ISNULL(RBB34,0.0)+'+VarToSql((-1)*QryDetail.FieldByName('RDB14').AsFloat)+',RBB38=ISNULL(RBB38,0.0)-'+VarToSql((-1)*QryDetail.FieldByName('RDB14').AsFloat)+' WHERE RBB03='+VarToSql(Trim(QryDetail.FieldByName('RDB04').AsString)+'-'+Trim(QryDetail.FieldByName('RDB05').AsString)+'-'+Trim(QryDetail.FieldByName('RDB06').AsString)));
end;
if Trim(QryDetail.FieldByName('RDB03').AsString)='3' Then
begin
//7、更新其它费用已收款金额、转应收金额
ExecuteSQL(' UPDATE ACRJB SET RJB07=ISNULL(RJB07,0.0)+'+VarToSql(QryDetail.FieldByName('RDB14').AsFloat)+',RJB09=ISNULL(RJB09,0.0)-'+VarToSql(QryDetail.FieldByName('RDB14').AsFloat)+' WHERE RJB01='+VarToSql(Trim(QryDetail.FieldByName('RDB05').AsString))+' AND RJB02='+VarToSql(Trim(QryDetail.FieldByName('RDB06').AsString)));
end;
if Trim(QryDetail.FieldByName('RDB03').AsString)='5' Then
begin
//8、更新其它销货已收款金额、转应收金额
ExecuteSQL(' UPDATE ACRAD SET RAD33=ISNULL(RAD33,0.0)+'+VarToSql(QryDetail.FieldByName('RDB14').AsFloat)+',RAD37=ISNULL(RAD37,0.0)-'+VarToSql(QryDetail.FieldByName('RDB14').AsFloat)+' WHERE RAD01='+VarToSql(Trim(QryDetail.FieldByName('RDB05').AsString))+' AND RAD02='+VarToSql(Trim(QryDetail.FieldByName('RDB06').AsString)));
end;
if Trim(QryDetail.FieldByName('RDB03').AsString)='6' Then
begin
//9、更新销货折让已收款金额、转应收金额
Executesql(' UPDATE ACRAF SET RAF33=ISNULL(RAF33,0.0)+'+VarToSql((-1)*QryDetail.FieldByName('RDB14').AsFloat)+',RAF37=ISNULL(RAF37,0.0)-'+VarToSql((-1)*QryDetail.FieldByName('RDB14').AsFloat)+' WHERE RAF01='+VarToSql(Trim(QryDetail.FieldByName('RDB05').AsString))+' AND RAF02='+VarToSql(Trim(QryDetail.FieldByName('RDB06').AsString)));
end;
cxProgressBar1.Position :=cxProgressBar1.Position+1;
cxProgressBar1.Repaint;
QryDetail.Next;
end;
cxProgressBar1.Visible :=False;
dlladocn.CommitTrans;
except
on E:Exception do
begin
dlladocn.RollbackTrans;
Application.Messagebox(pchar('操作失败．'+#13#10+E.Message),'提示信息',MB_OK+MB_ICONERROR);
end;
end;
end
else
begin
try
if not dlladocn.InTransaction then
dlladocn.BeginTrans;
QryDetail.First;
cxProgressBar1.Visible :=True;
cxProgressBar1.Position :=0;
cxProgressBar1.Properties.Min :=0;
cxProgressBar1.Properties.Max :=QryDetail.RecordCount;
cxProgressBar1.Properties.Text :='正在处理数据中。。。';
while not QryDetail.Eof do
begin
if Trim(QryDetail.FieldByName('RDB20').AsString) <>'' Then
begin
//1、更新对账单单身 已收款、实际收款日期、收款方式
ExecuteSQL('UPDATE ACRED SET RED19=ISNULL(RED19,0.0)-'+VarToSql(QryDetail.FieldByName('RDB14').AsFloat)+',RED24=(CASE WHEN RED24='+VarToSql(vRDA06)+' THEN NULL ELSE RED24 END ),RED22=(CASE WHEN RED22='+VarToSql(vRDA14)+' THEN '''' ELSE RED22 END ) WHERE RED01='+VarToSql(Trim(QryDetail.FieldByName('RDB20').AsString))+' AND RED02='+VarToSql(Trim(QryDetail.FieldByName('RDB21').AsString)));
//2、更新对帐单表头已收款金额、已开票金额
ExecuteSQL(' UPDATE ACREC SET ACREC.REC13=AA.YSK '+
' FROM ACREC,(SELECT REC01,SUM(ISNULL(RED19,0.0)) AS YSK '+
' FROM ACREC '+
' INNER JOIN ACRED ON ACREC.REC01=ACRED.RED01 '+
' WHERE ACRED.RED01='+VarToSql(Trim(QryDetail.FieldByName('RDB20').AsString))+
' GROUP BY REC01) AS AA WHERE ACREC.REC01=AA.REC01');
//3、更新对帐单表头锁定码
ExecuteSQL(' UPDATE ACREC SET REC15=(CASE WHEN (ISNULL(REC10,0.0)-ISNULL(REC13,0.0)=0) AND (ISNULL(REC10,0.0)-ISNULL(REC14,0.0)=0.0) THEN ''Y'' ELSE ''N'' END ) WHERE REC01='+VarToSql(Trim(QryDetail.FieldByName('RDB20').AsString)));
end;
if Trim(QryDetail.FieldByName('RDB03').AsString)='4' Then
begin
//4、更新开账单
ExecuteSQL(' UPDATE ACRFB SET RFB11=ISNULL(RFB11,0.0)-'+VarToSql(QryDetail.FieldByName('RDB14').AsFloat)+' WHERE RFB03='+VarToSql(Trim(QryMaster.FieldByName('RDA04').AsString)));
end;
if Trim(QryDetail.FieldByName('RDB03').AsString)='1' Then
begin
//5、更新销货单已收款金额、转应收金额
ExecuteSQL(' UPDATE ACRAB SET RAB33=ISNULL(RAB33,0.0)-'+VarToSql(QryDetail.FieldByName('RDB14').AsFloat)+',RAB37=ISNULL(RAB37,0.0)+'+VarToSql(QryDetail.FieldByName('RDB14').AsFloat)+' WHERE RAB03='+VarToSql(Trim(QryDetail.FieldByName('RDB04').AsString)+'-'+Trim(QryDetail.FieldByName('RDB05').AsString)+'-'+Trim(QryDetail.FieldByName('RDB06').AsString)));
end;
if Trim(QryDetail.FieldByName('RDB03').AsString)='2' Then
begin
//6、更新销退单已收款金额、转应收金额
ExecuteSQL(' UPDATE ACRBB SET RBB34=ISNULL(RBB34,0.0)-'+VarToSql((-1)*QryDetail.FieldByName('RDB14').AsFloat)+',RBB38=ISNULL(RBB38,0.0)+'+VarToSql((-1)*QryDetail.FieldByName('RDB14').AsFloat)+' WHERE RBB03='+VarToSql(Trim(QryDetail.FieldByName('RDB04').AsString)+'-'+Trim(QryDetail.FieldByName('RDB05').AsString)+'-'+Trim(QryDetail.FieldByName('RDB06').AsString)));
end;
if Trim(QryDetail.FieldByName('RDB03').AsString)='3' Then
begin
//7、更新其它费用已收款金额、转应收金额
ExecuteSQL(' UPDATE ACRJB SET RJB07=ISNULL(RJB07,0.0)-'+VarToSql(QryDetail.FieldByName('RDB14').AsFloat)+',RJB09=ISNULL(RJB09,0.0)+'+VarToSql(QryDetail.FieldByName('RDB14').AsFloat)+' WHERE RJB01='+VarToSql(Trim(QryDetail.FieldByName('RDB05').AsString))+' AND RJB02='+VarToSql(Trim(QryDetail.FieldByName('RDB06').AsString)));
end;
if Trim(QryDetail.FieldByName('RDB03').AsString)='5' Then
begin
//8、更新其它销货已收款金额、转应收金额
ExecuteSQL(' UPDATE ACRAD SET RAD33=ISNULL(RAD33,0.0)-'+VarToSql(QryDetail.FieldByName('RDB14').AsFloat)+',RAD37=ISNULL(RAD37,0.0)+'+VarToSql(QryDetail.FieldByName('RDB14').AsFloat)+' WHERE RAD01='+VarToSql(Trim(QryDetail.FieldByName('RDB05').AsString))+' AND RAD02='+VarToSql(Trim(QryDetail.FieldByName('RDB06').AsString)));
end;
if Trim(QryDetail.FieldByName('RDB03').AsString)='6' Then
begin
//9、更新销货折让已收款金额、转应收金额
Executesql(' UPDATE ACRAF SET RAF33=ISNULL(RAF33,0.0)-'+VarToSql((-1)*QryDetail.FieldByName('RDB14').AsFloat)+',RAF37=ISNULL(RAF37,0.0)+'+VarToSql((-1)*QryDetail.FieldByName('RDB14').AsFloat)+' WHERE RAF01='+VarToSql(Trim(QryDetail.FieldByName('RDB05').AsString))+' AND RAF02='+VarToSql(Trim(QryDetail.FieldByName('RDB06').AsString)));
end;
cxProgressBar1.Position :=cxProgressBar1.Position+1;
cxProgressBar1.Repaint;
QryDetail.Next;
end;
cxProgressBar1.Visible :=False;
dlladocn.CommitTrans;
except
on E:Exception do
begin
dlladocn.RollbackTrans;
Application.Messagebox(pchar('操作失败．'+#13#10+E.Message),'提示信息',MB_OK+MB_ICONERROR);
end;
end;
end;
end;
procedure TfrmACRDABA.btnRefresh;
Var
statemode: boolean;
begin
inherited;
statemode := QryMaster.State in [dsInsert,dsEdit];
wwDBComboBox2.ReadOnly :=True;
wwDBEdit5.ReadOnly :=True;
wwDBEdit8.ReadOnly :=True;
if Trim(wwDBComboBox3.Text)='承兑' Then
begin
TabSheet3.TabVisible :=True;
label21.Visible :=True;
wwDBComboBox4.Visible :=True;
end
else
begin
TabSheet3.TabVisible :=False;
label21.Visible :=False;
wwDBComboBox4.Visible :=False;
end;
if Trim(wwDBComboBox3.Text)='电汇' Then
begin
label7.Visible :=True;
wwDBComboBox1.Visible :=True;
end
else
begin
label7.Visible :=False;
wwDBComboBox1.Visible :=False;
end;
wwDBEdit12.ReadOnly :=True;
wwDBEdit13.ReadOnly :=True;
cmdZD.Enabled := not statemode and GetUserRight(FrmName,2);
if Trim(QryMaster.FieldByName('RDA21').AsString)<>'' Then
begin
cmdZD.Caption :='撤消';
end
else
begin
cmdZD.Caption :='转单';
end;
wwDBCBRDB03.ReadOnly :=True;
wwDBEdit6.ReadOnly :=True;
end;
procedure TfrmACRDABA.findClick(Sender: TObject);
var
fSQL: string;
begin
if not Assigned(frmfilterf) then
frmfilterf:=Tfrmfilterf.create(application);
with frmfilterf do
begin
FilterfCDS:=FrmGrid;
FilterfTable:=FrmHDB;
ShowModal;
fSQL:=FilterfSQL;
Free;
end;
if trim(fSQL)<>'' then
begin
fSQL:=COPY(fSQL,1,Length(fSQL)-length(' ORDER BY fid DESC'))+' AND ACRDA.RDA11=''1'' AND ISNULL(ACRDA.RDA22,'''')<>''Y'' ORDER BY RDA01 DESC ';
QryMaster.DisableControls;
with QryMaster do
begin
Connection:=DlladoCn;
Close;
SQL.Clear;
SQL.Add(fSQL);
Open;
end;
QryMaster.EnableControls;
end
else
begin
QryMaster.DisableControls;
fSQL:=' SELECT TOP 500 * FROM ACRDA WHERE ACRDA.RDA11=''1'' AND ISNULL(ACRDA.RDA22,'''')<>''Y'' ORDER BY RDA01 DESC ';
with QryMaster do
begin
Connection:=DlladoCn;
Close;
SQL.Clear;
SQL.Add(fSQL);
Open;
end;
QryMaster.EnableControls;
end;
if QryMaster.RecordCount<>0 then
refreshmode:=True;
recRefresh;
btnRefresh;
PageControl1.ActivePageIndex:=2;
PageControl1.OnChange(Sender);
QryMaster.First;
end;
procedure TfrmACRDABA.QryMasterRDA11GetText(Sender: TField;
var Text: String; DisplayText: Boolean);
begin
inherited;
if Trim(QryMaster.FieldByName('RDA11').AsString)='1' Then
begin
text :='1:应收款';
end
else
if Trim(QryMaster.FieldByName('RDA11').AsString)='2' Then
begin
text :='2:预收款';
end;
end;
procedure TfrmACRDABA.QryMasterRDA07GetText(Sender: TField;
var Text: String; DisplayText: Boolean);
begin
inherited;
if Trim(QryMaster.FieldByName('RDA07').AsString)='1' Then
begin
text :='1:公对公';
end
else
if Trim(QryMaster.FieldByName('RDA07').AsString)='2' Then
begin
text :='2:私对私';
end
else
if Trim(QryMaster.FieldByName('RDA07').AsString)='3' Then
begin
text :='3:私对公';
end;
end;
procedure TfrmACRDABA.wwDBGrid2CalcCellColors(Sender: TObject;
Field: TField; State: TGridDrawState; Highlight: Boolean; AFont: TFont;
ABrush: TBrush);
begin
inherited;
if ((Field.FieldName = 'RDB03') or (Field.FieldName = 'RDB04') or (Field.FieldName = 'RDB12') or (Field.FieldName = 'RDB13') or (Field.FieldName = 'RDB14')) then
begin
ABrush.Color:= $00B9FFFF;
AFont.Color :=clblack;
end;
end;
procedure TfrmACRDABA.QryMasterCalcFields(DataSet: TDataSet);
begin
inherited;
QryMaster.FieldByName('WCXJE').AsFloat := StrToFloat(FormatFloat('0.00',QryMaster.FieldByName('RDA08').AsFloat - QryMaster.FieldByName('RDA12').AsFloat-QryMaster.FieldByName('RDA20').AsFloat));
end;
procedure TfrmACRDABA.wwDBComboBox3Change(Sender: TObject);
begin
inherited;
btnrefresh;
end;
procedure TfrmACRDABA.PageControl1Change(Sender: TObject);
begin
//inherited;
if PageControl1.ActivePageIndex=2 then
begin
Splitter1.Visible:=False;
TabControl1.Visible:=False;
PageControl1.Align:=alClient;
end
else
begin
PageControl1.Align:=alTop;
PageControl1.Height:=pgh;
Splitter1.Visible:=True;
TabControl1.Visible:=True;
end;
btnrefresh;
end;
procedure TfrmACRDABA.cmdZDClick(Sender: TObject);
Var
vYSDH:String; //预收单号
vSKDH:String; //收款单号
vRDA16,vRDA18:Variant;
begin
inherited;
if Trim(QryMaster.FieldByName('CONFORM_CODE').AsString) ='Y' Then
begin
Application.MessageBox('单据已审核,不能进行操作。','提示信息',MB_IconInformation+MB_OK);
abort;
end;
if QryMaster.IsEmpty Then
begin
Application.MessageBox('资料为空，不能进行转预收款作业。','提示信息',MB_IconInformation+MB_OK);
abort;
end;
vSKDH:=Trim(Trim(wwDBEdit1.Text)); //收款单号
if cmdZD.Caption ='转单' Then //转单
begin
if QryMaster.FieldByName('WCXJE').AsFloat >0 Then
begin
try
if not dlladocn.InTransaction then
dlladocn.BeginTrans;
//取单号
vYSDH:=GetFormNo('ACRDA','RDA01'); //预收款单号
vSKDH:=Trim(Trim(wwDBEdit1.Text)); //收款单号
//更新收款单信息
ExecuteSQL('UPDATE ACRDA SET RDA20='+VarToSql(QryMaster.FieldByName('WCXJE').AsFloat)+',RDA21='+VarToSql(vYSDH)+' WHERE RDA01='+VarToSql(Trim(vSKDH)));
//更新预收款的收款单号
if trim(wwDBDateTimePicker5.Text)<>'' then
begin
vRDA16:=wwDBDateTimePicker5.Text;
end
else
begin
vRDA16:=null;
end;
if trim(wwDBDateTimePicker6.Text)<>'' then
begin
vRDA18:=wwDBDateTimePicker6.Text;
end
else
begin
vRDA18:=null;
end;
ExecuteSQL('INSERT INTO ACRDA(RDA01,RDA02,RDA03,RDA04,RDA05,RDA06,RDA07,RDA08,RDA09,RDA10,'+
' RDA11,RDA12,RDA13,RDA14,RDA15,RDA16,RDA17,RDA18,RDA19,RDA20,'+
' RDA21,CONFORM_DATE,CONFORM_NAME,CONFORM_CODE) VALUES('+VarToSql(vYSDH)+
','+VarToSql(DllUserName)+
','+VarToSql(Date())+
','+VarToSql(Trim(QryMaster.FieldByName('RDA04').AsString))+
','+VarToSql(Trim(QryMaster.FieldByName('RDA05').AsString))+
','+VarToSql(QryMaster.FieldByName('RDA06').AsDateTime)+
','+VarToSql(Trim(QryMaster.FieldByName('RDA07').AsString))+
','+VarToSql(QryMaster.FieldByName('WCXJE').AsFloat)+
','+VarToSql(Trim(QryMaster.FieldByName('RDA09').AsString))+
','+VarToSql(Trim(QryMaster.FieldByName('RDA10').AsString))+
','+VarToSql('2')+
',0.0 '+
','+VarToSql(Trim(QryMaster.FieldByName('RDA13').AsString))+
','+VarToSql(Trim(QryMaster.FieldByName('RDA14').AsString))+
','+VarToSql(Trim(QryMaster.FieldByName('RDA15').AsString))+
','+VarToSql(vRDA16)+
','+VarToSql(Trim(QryMaster.FieldByName('RDA17').AsString))+
','+VarToSql(vRDA18)+
','+VarToSql(QryMaster.FieldByName('RDA19').AsFloat)+
',0.0'+
','+VarToSql(vSKDH)+
','+VarToSql(Date())+
','+VarToSql(DllUserName)+
','+VarToSql('Y')+')');
dlladocn.CommitTrans;
except
on E:Exception do
begin
dlladocn.RollbackTrans;
Application.Messagebox(pchar('操作失败．'+#13#10+E.Message),'提示信息',MB_OK+MB_ICONERROR);
end;
end;
end;
end
else //撤消
begin
//判断预收款是否有冲销，如果有冲销，将不能撤消
if not GetSQLEmpty('SELECT RDC01 FROM ACRDC WHERE RDC09='+VarToSql(Trim(wwDBEdit12.Text))+' AND RDC11>0.0 ') Then
begin
Application.MessageBox('生成的预收款单已冲销,无法撤消.','提示信息',MB_IconInformation+MB_OK);
abort;
end;
try
if not dlladocn.InTransaction then
dlladocn.BeginTrans;
//更新收款单信息
ExecuteSQL('UPDATE ACRDA SET RDA20=0.0,RDA21='''' WHERE RDA01='+VarToSql(Trim(wwDBEdit1.Text)));
//删除预收款信息
ExecuteSQL('DELETE ACRDA WHERE RDA01='+VarToSql(Trim(wwDBEdit12.Text)));
dlladocn.CommitTrans;
except
on E:Exception do
begin
dlladocn.RollbackTrans;
Application.Messagebox(pchar('操作失败．'+#13#10+E.Message),'提示信息',MB_OK+MB_ICONERROR);
end;
end;
end;
QryMaster.Connection:=DlladoCn;
QryMaster.Close;
QryMaster.SQL.Clear;
QryMaster.SQL.Add(' SELECT * FROM ACRDA WHERE RDA11=''1'' AND ISNULL(ACRDA.RDA22,'''')<>''Y'' AND RDA01='+VarToSql(Trim(vSKDH)));
QryMaster.Open;
QryMaster.first;
btnrefresh;
end;
procedure TfrmACRDABA.editClick(Sender: TObject);
begin
if Trim(wwDBEdit12.Text)<>'' Then
begin
Application.MessageBox('此收款单有转预收款,如果要修改,请先撤销。!','提示信息',MB_IconInformation+MB_OK);
abort;
end;
inherited;
end;
procedure TfrmACRDABA.deleClick(Sender: TObject);
begin
if Trim(wwDBEdit12.Text)<>'' Then
begin
Application.MessageBox('此收款单有转预收款,如果要删除,请先撤销。!','提示信息',MB_IconInformation+MB_OK);
abort;
end;
inherited;
end;
procedure TfrmACRDABA.FormShow(Sender: TObject);
begin
inherited;
QryMaster.Connection:=DlladoCn;
QryMaster.Close;
QryMaster.SQL.Clear;
QryMaster.SQL.Add(' SELECT * FROM ACRDA WHERE RDA11=''1'' AND ISNULL(ACRDA.RDA22,'''')<>''Y'' AND 1<>1 ');
QryMaster.Open;
QryMaster.first;
end;
procedure TfrmACRDABA.sqlRefresh;
Var
sSQL:String;
begin
with QryMaster do begin
Connection := DlladoCn;
Close;
SQL.Clear;
SQL.Add('SELECT TOP 0 * FROM ACRDA WHERE RDA11=''1'' AND ISNULL(ACRDA.RDA22,'''')<>''Y'' ');
Open;
end;
sSQL:='select top 0 * from '+FrmGDB+' where fid>0';
with QryDetail do begin
Connection := DlladoCn;
Close;
SQL.Clear;
SQL.Add(sSQL);
Open;
end;
end;
procedure TfrmACRDABA.refreshClick(Sender: TObject);
var
rcode: string;
sSQL: string;
begin
rcode := wwDBEdit1.Text;
if not Assigned(frmShowMsg) then
frmShowMsg := TfrmShowMsg.Create(Application);
frmShowMsg.Show;
frmShowMsg.Update;
QryMaster.DisableControls;
with QryMaster do begin
sSQL:='SELECT * FROM ACRDA WHERE RDA11=''1'' AND ISNULL(ACRDA.RDA22,'''')<>''Y'' ';
Connection := DlladoCn;
Close;
SQL.Clear;
SQL.Add(sSQL);
Open;
end;
QryMaster.Locate(FrmHID,Trim(rcode),[loPartialKey]);
refreshmode:=True;
recRefresh;
btnRefresh;
QryMaster.EnableControls;
frmShowMsg.Hide;
frmShowMsg.Free;
end;
procedure TfrmACRDABA.cmdGetClick(Sender: TObject);
Var
QryTmpB,QryTmpC:TADOQuery;
vJE,vQCJE,vRFB11:Double;
begin
inherited;
wwDBEdit1.SetFocus;
if Trim(wwDBEdit3.Text)='' Then
begin
Application.MessageBox('客户编号不能为空!','提示信息',MB_IconInformation+MB_OK);
abort;
end;
if QryMaster.FieldByName('RDA08').AsFloat <=0 Then
begin
Application.MessageBox('本次收款金额必须大于零!','提示信息',MB_IconInformation+MB_OK);
abort;
end;
if QryDetail.RecordCount>0 then
begin
if application.Messagebox('表身有资料,提取数据将删除表身资料,继续吗？','提示信息',Mb_YesNo+Mb_IconQuestion)=IDNO then
Abort
else
begin
QryDetail.First;
while not QryDetail.Eof do
QryDetail.Delete;
end;
end;
vJE:=QryMaster.FieldByName('RDA08').AsFloat;
//有期初值
if not GetSQLEmpty('SELECT ISNULL(RFB07,0.0)-ISNULL(RFB11,0.0) AS YE FROM ACRFB WHERE RFB03='+VarToSql(Trim(QryMaster.FieldByName('RDA04').AsString))+' AND ISNULL(RFB07,0.0)-ISNULL(RFB11,0.0)>0 ') Then
begin
QryMaster.FieldByName('RDA12').AsFloat :=0.0;
try
QryTmpC:=TADOQuery.Create(nil);
QryTmpC.Connection:=DllAdoCn;
QryTmpC.EnableBCD :=False;
QryTmpC.Close;
QryTmpC.SQL.Clear;
QryTmpC.SQL.Add('SELECT ISNULL(RFB07,0.0)-ISNULL(RFB11,0.0) AS QCYE,RFB11 FROM ACRFB WHERE RFB03='+VarToSql(Trim(QryMaster.FieldByName('RDA04').AsString))+' AND ISNULL(RFB07,0.0)-ISNULL(RFB11,0.0)>0 ');
QryTmpC.Open;
vQCJE := QryTmpC.FieldByName('QCYE').AsFloat; //未冲销期初金额
vRFB11 := QryTmpC.FieldByName('RFB11').AsFloat; //已冲销期初金额
finally
QryTmpC.Close;
QryTmpC.Free;
end;
if vJE<=vQCJE Then //收款金额小于等于未冲销期初金额,则全部冲销期初金额
begin
QryDetail.Append;
QryDetail.FieldByName('RDB03').AsString :='4'; //来源 1：销货。2。销退。3:订单
QryDetail.FieldByName('RDB04').AsString :=''; //来源单别
QryDetail.FieldByName('RDB05').AsString :=''; //来源单号
QryDetail.FieldByName('RDB06').AsString :=''; //来源序号
QryDetail.FieldByName('RDB07').AsString :=''; //品号
QryDetail.FieldByName('RDB08').AsString :=''; //品名
QryDetail.FieldByName('RDB09').AsString :=''; //规格
QryDetail.FieldByName('RDB10').AsString :=''; //单位
QryDetail.FieldByName('RDB11').AsFloat :=0.0; //数量
QryDetail.FieldByName('RDB12').AsFloat :=0.0; //单价
QryDetail.FieldByName('RDB13').AsString :=''; //备注
QryDetail.FieldByName('RDB14').AsFloat :=vJE; //本次收款金额
QryDetail.FieldByName('RDB15').AsFloat :=vRFB11;//已收款金额
QryDetail.FieldByName('RDB17').AsString :=''; //快捷码
QryDetail.FieldByName('RDB18').AsString :=''; //客户单号
QryDetail.FieldByName('RDB19').AsFloat :=0.0; //其它费用
QryDetail.FieldByName('RDB20').AsString :=''; //对账单号
QryDetail.FieldByName('RDB21').AsString :=''; //对账序号
QryDetail.FieldByName('RDB22').AsString :=''; //送货单号
QryDetail.FieldByName('RDB23').AsString :=''; //客户品号
end
else
begin //先冲销期初值,再冲销对账单
QryDetail.Append;
QryDetail.FieldByName('RDB03').AsString :='4'; //来源 1：销货。2。销退。3:订单
QryDetail.FieldByName('RDB04').AsString :=''; //来源单别
QryDetail.FieldByName('RDB05').AsString :=''; //来源单号
QryDetail.FieldByName('RDB06').AsString :=''; //来源序号
QryDetail.FieldByName('RDB07').AsString :=''; //品号
QryDetail.FieldByName('RDB08').AsString :=''; //品名
QryDetail.FieldByName('RDB09').AsString :=''; //规格
QryDetail.FieldByName('RDB10').AsString :=''; //单位
QryDetail.FieldByName('RDB11').AsFloat :=0.0; //数量
QryDetail.FieldByName('RDB12').AsFloat :=0.0; //单价
QryDetail.FieldByName('RDB13').AsString :=''; //备注
QryDetail.FieldByName('RDB14').AsFloat :=vQCJE; //本次收款金额
QryDetail.FieldByName('RDB15').AsFloat :=vRFB11;//已收款金额
QryDetail.FieldByName('RDB17').AsString :=''; //快捷码
QryDetail.FieldByName('RDB18').AsString :=''; //客户单号
QryDetail.FieldByName('RDB19').AsFloat :=0.0; //其它费用
QryDetail.FieldByName('RDB20').AsString :=''; //对账单号
QryDetail.FieldByName('RDB21').AsString :=''; //对账序号
QryDetail.FieldByName('RDB22').AsString :=''; //送货单号
QryDetail.FieldByName('RDB23').AsString :=''; //客户品号
vJE :=vJE-vQCJE;
//再冲销对账单
try
QryTmpB:=TADOQuery.Create(nil);
QryTmpB.Connection:=DllAdoCn;
QryTmpB.EnableBCD :=False;
QryTmpB.Close;
QryTmpB.SQL.Clear;
QryTmpB.SQL.Add(' SELECT RED03 AS RDB03, '+
' CASE WHEN (RED03=''1'' OR RED03=''2'') THEN SUBSTRING(RED07,1,4) WHEN (RED03=''3'' OR RED03=''5'' OR RED03=''6'' ) THEN '''' END RDB04, '+
' CASE WHEN (RED03=''1'' OR RED03=''2'') THEN SUBSTRING(RED07,6,11) WHEN (RED03=''3'' OR RED03=''5'' OR RED03=''6'' ) THEN SUBSTRING(RED07,1,11) END RDB05, '+
' CASE WHEN (RED03=''1'' OR RED03=''2'') THEN SUBSTRING(RED07,18,4) WHEN (RED03=''3'' OR RED03=''5'' OR RED03=''6'' ) THEN SUBSTRING(RED07,13,4) END RDB06, '+
' RED08 AS RDB07,RED09 AS RDB08,RED10 AS RDB09,RED11 AS RDB10,RED13 AS RDB11,RED12 AS RDB12,'''' AS RDB13, '+
' ISNULL(RED13,0.0)*ISNULL(RED12,0.0)+ISNULL(RED26,0.0)-ISNULL(RED19,0.0) AS RDB14,RED19 AS RDB15,RED06 AS RDB17,'+
' RED05 AS RDB18,RED26 AS RDB19,RED01 AS RDB20,RED02 AS RDB21,RED29 AS RDB22,RED30 AS RDB23 '+
' FROM ACRED '+
' INNER JOIN ACREC ON ACREC.REC01=ACRED.RED01 '+
' WHERE ACREC.CONFORM_CODE=''Y'' AND ISNULL(RED28,0.0)-ISNULL(RED19,0.0)<>0 AND REC04='+VarToSql(Trim(wwDBEdit3.Text))+
' AND EXISTS(SELECT 1 FROM (SELECT RAB03 AS ERPDH,RAB30 AS JE,RAB34 AS YKPJE,''销货'' AS BZ '+
' FROM ACRAA '+
' INNER JOIN ACRAB ON ACRAA.RAA01=ACRAB.RAB01 '+
' WHERE ACRAA.CONFORM_CODE=''Y'' AND ISNULL(RAB30,0.0)-ISNULL(RAB33,0.0)>0 '+
' UNION '+
' SELECT RBB03 AS ERPDH,RBB31 AS JE,RBB35 AS YKPJE,''销退'' AS BZ '+
' FROM ACRBA '+
' INNER JOIN ACRBB ON ACRBA.RBA01=ACRBB.RBB01 '+
' WHERE ACRBA.CONFORM_CODE=''Y'' AND ISNULL(RBB31,0.0)-ISNULL(RBB34,0.0)>0 '+
' UNION ALL '+
' SELECT RJB01+''-''+RJB02 AS ERPDH,RJB04 AS JE,RJB08 AS YKPJE,''其它'' AS BZ '+
' FROM ACRJB '+
' INNER JOIN ACRJA ON ACRJA.RJA01=ACRJB.RJB01 '+
' WHERE ACRJA.CONFORM_CODE=''Y'' AND ISNULL(ACRJB.RJB04,0.0)-ISNULL(ACRJB.RJB07,0.0)>0.0 '+
' UNION ALL '+
' SELECT RAD01+''-''+RAD02 AS ERPDH,RAD30 AS JE,RAD34 AS YKPJE,''其它销货'' AS BZ '+
' FROM ACRAC '+
' INNER JOIN ACRAD ON ACRAC.RAC01=ACRAD.RAD01 '+
' WHERE ACRAC.CONFORM_CODE=''Y'' AND ISNULL(ACRAD.RAD30,0.0)-ISNULL(ACRAD.RAD33,0.0)>0 '+
' UNION ALL '+
' SELECT RAF01+''-''+RAF02 AS ERPDH,RAF30 AS JE,RAF34 AS YKPJE,''销货折让'' AS BZ '+
' FROM ACRAE '+
' INNER JOIN ACRAF ON ACRAE.RAE01=ACRAF.RAF01 '+
' WHERE ACRAE.CONFORM_CODE=''Y'' AND ISNULL(RAF30,0.0)-ISNULL(RAF33,0.0)>0) AS AA WHERE AA.ERPDH=ACRED.RED07) '+
' ORDER BY RED04 ');
QryTmpB.Open;
if QryTmpB.RecordCount>0 then
begin
QryTmpB.First;
While not QryTmpB.Eof do
begin
if vJE-QryTmpB.FieldByName('RDB14').AsFloat>0 Then
begin
QryDetail.Append;
QryDetail.FieldByName('RDB03').AsString :=QryTmpB.FieldByName('RDB03').AsString; //来源 1：销货。2。销退。3:订单
QryDetail.FieldByName('RDB04').AsString :=QryTmpB.FieldByName('RDB04').AsString; //来源单别
QryDetail.FieldByName('RDB05').AsString :=QryTmpB.FieldByName('RDB05').AsString; //来源单号
QryDetail.FieldByName('RDB06').AsString :=QryTmpB.FieldByName('RDB06').AsString; //来源序号
QryDetail.FieldByName('RDB07').AsString :=QryTmpB.FieldByName('RDB07').AsString; //品号
QryDetail.FieldByName('RDB08').AsString :=QryTmpB.FieldByName('RDB08').AsString; //品名
QryDetail.FieldByName('RDB09').AsString :=QryTmpB.FieldByName('RDB09').AsString; //规格
QryDetail.FieldByName('RDB10').AsString :=QryTmpB.FieldByName('RDB10').AsString; //单位
QryDetail.FieldByName('RDB11').AsFloat :=QryTmpB.FieldByName('RDB11').AsFloat; //数量
QryDetail.FieldByName('RDB12').AsFloat :=QryTmpB.FieldByName('RDB12').AsFloat; //单价
QryDetail.FieldByName('RDB13').AsString :=QryTmpB.FieldByName('RDB13').AsString; //备注
QryDetail.FieldByName('RDB14').AsFloat :=QryTmpB.FieldByName('RDB14').AsFloat; //本次收款金额
QryDetail.FieldByName('RDB15').AsFloat :=QryTmpB.FieldByName('RDB15').AsFloat; //已收款金额
QryDetail.FieldByName('RDB17').AsString :=QryTmpB.FieldByName('RDB17').AsString; //快捷码
QryDetail.FieldByName('RDB18').AsString :=QryTmpB.FieldByName('RDB18').AsString; //客户单号
QryDetail.FieldByName('RDB19').AsFloat :=QryTmpB.FieldByName('RDB19').AsFloat; //其它费用
QryDetail.FieldByName('RDB20').AsString :=QryTmpB.FieldByName('RDB20').AsString; //对账单号
QryDetail.FieldByName('RDB21').AsString :=QryTmpB.FieldByName('RDB21').AsString; //对账序号
QryDetail.FieldByName('RDB22').AsString :=QryTmpB.FieldByName('RDB22').AsString; //送货单号
QryDetail.FieldByName('RDB23').AsString :=QryTmpB.FieldByName('RDB23').AsString; //客户品号
vJE :=vJE-QryTmpB.FieldByName('RDB14').AsFloat;
QryTmpB.Next;
end
else
begin
QryDetail.Append;
QryDetail.FieldByName('RDB03').AsString :=QryTmpB.FieldByName('RDB03').AsString; //来源 1：销货。2。销退。3:订单
QryDetail.FieldByName('RDB04').AsString :=QryTmpB.FieldByName('RDB04').AsString; //来源单别
QryDetail.FieldByName('RDB05').AsString :=QryTmpB.FieldByName('RDB05').AsString; //来源单号
QryDetail.FieldByName('RDB06').AsString :=QryTmpB.FieldByName('RDB06').AsString; //来源序号
QryDetail.FieldByName('RDB07').AsString :=QryTmpB.FieldByName('RDB07').AsString; //品号
QryDetail.FieldByName('RDB08').AsString :=QryTmpB.FieldByName('RDB08').AsString; //品名
QryDetail.FieldByName('RDB09').AsString :=QryTmpB.FieldByName('RDB09').AsString; //规格
QryDetail.FieldByName('RDB10').AsString :=QryTmpB.FieldByName('RDB10').AsString; //数量
QryDetail.FieldByName('RDB11').AsFloat :=QryTmpB.FieldByName('RDB11').AsFloat; //数量
QryDetail.FieldByName('RDB12').AsFloat :=QryTmpB.FieldByName('RDB12').AsFloat; //单价
QryDetail.FieldByName('RDB13').AsString :=QryTmpB.FieldByName('RDB13').AsString; //备注
QryDetail.FieldByName('RDB14').AsFloat :=vJE; //本次收款金额
QryDetail.FieldByName('RDB15').AsFloat :=QryTmpB.FieldByName('RDB15').AsFloat; //已收款金额
QryDetail.FieldByName('RDB17').AsString :=QryTmpB.FieldByName('RDB17').AsString; //快捷码
QryDetail.FieldByName('RDB18').AsString :=QryTmpB.FieldByName('RDB18').AsString; //客户单号
QryDetail.FieldByName('RDB19').AsFloat :=0.0; //其它费用
QryDetail.FieldByName('RDB20').AsString :=QryTmpB.FieldByName('RDB20').AsString; //对账单号
QryDetail.FieldByName('RDB21').AsString :=QryTmpB.FieldByName('RDB21').AsString; //对账序号
QryDetail.FieldByName('RDB22').AsString :=QryTmpB.FieldByName('RDB22').AsString; //送货单号
QryDetail.FieldByName('RDB23').AsString :=QryTmpB.FieldByName('RDB23').AsString; //客户品号
Break;
end;
end;
end;
finally
QryTmpB.Close;
QryTmpB.Free;
end;
end;
end
else
begin
QryMaster.FieldByName('RDA12').AsFloat :=0.0;
//取未收款的信息 1)够冲销 2)不够冲销
try
QryTmpB:=TADOQuery.Create(nil);
QryTmpB.Connection:=DllAdoCn;
QryTmpB.EnableBCD :=False;
QryTmpB.Close;
QryTmpB.SQL.Clear;
QryTmpB.SQL.Add(' SELECT RED03 AS RDB03, '+
' CASE WHEN (RED03=''1'' OR RED03=''2'') THEN SUBSTRING(RED07,1,4) WHEN (RED03=''3'' OR RED03=''5'' OR RED03=''6'' ) THEN '''' END RDB04, '+
' CASE WHEN (RED03=''1'' OR RED03=''2'') THEN SUBSTRING(RED07,6,11) WHEN (RED03=''3'' OR RED03=''5'' OR RED03=''6'' ) THEN SUBSTRING(RED07,1,11) END RDB05, '+
' CASE WHEN (RED03=''1'' OR RED03=''2'') THEN SUBSTRING(RED07,18,4) WHEN (RED03=''3'' OR RED03=''5'' OR RED03=''6'' ) THEN SUBSTRING(RED07,13,4) END RDB06, '+
' RED08 AS RDB07,RED09 AS RDB08,RED10 AS RDB09,RED11 AS RDB10,RED13 AS RDB11,RED12 AS RDB12,'''' AS RDB13, '+
' ISNULL(RED13,0.0)*ISNULL(RED12,0.0)+ISNULL(RED26,0.0)-ISNULL(RED19,0.0) AS RDB14,RED19 AS RDB15,RED06 AS RDB17,'+
' RED05 AS RDB18,RED26 AS RDB19,RED01 AS RDB20,RED02 AS RDB21,RED29 AS RDB22,RED30 AS RDB23 '+
' FROM ACRED '+
' INNER JOIN ACREC ON ACREC.REC01=ACRED.RED01 '+
' WHERE ACREC.CONFORM_CODE=''Y'' AND ISNULL(RED28,0.0)-ISNULL(RED19,0.0)<>0 AND REC04='+VarToSql(Trim(wwDBEdit3.Text))+
' AND EXISTS (SELECT 1 FROM (SELECT RAB03 AS ERPDH,RAB30 AS JE,RAB34 AS YKPJE,''销货'' AS BZ '+
' FROM ACRAA '+
' INNER JOIN ACRAB ON ACRAA.RAA01=ACRAB.RAB01 '+
' WHERE ACRAA.CONFORM_CODE=''Y'' AND ISNULL(RAB30,0.0)-ISNULL(RAB33,0.0)>0 '+
' UNION '+
' SELECT RBB03 AS ERPDH,RBB31 AS JE,RBB35 AS YKPJE,''销退'' AS BZ '+
' FROM ACRBA '+
' INNER JOIN ACRBB ON ACRBA.RBA01=ACRBB.RBB01 '+
' WHERE ACRBA.CONFORM_CODE=''Y'' AND ISNULL(RBB31,0.0)-ISNULL(RBB34,0.0)>0 '+
' UNION ALL '+
' SELECT RJB01+''-''+RJB02 AS ERPDH,RJB04 AS JE,RJB08 AS YKPJE,''其它'' AS BZ '+
' FROM ACRJB '+
' INNER JOIN ACRJA ON ACRJA.RJA01=ACRJB.RJB01 '+
' WHERE ACRJA.CONFORM_CODE=''Y'' AND ISNULL(ACRJB.RJB04,0.0)-ISNULL(ACRJB.RJB07,0.0)>0.0 '+
' UNION ALL '+
' SELECT RAD01+''-''+RAD02 AS ERPDH,RAD30 AS JE,RAD34 AS YKPJE,''其它销货'' AS BZ '+
' FROM ACRAC '+
' INNER JOIN ACRAD ON ACRAC.RAC01=ACRAD.RAD01 '+
' WHERE ACRAC.CONFORM_CODE=''Y'' AND ISNULL(ACRAD.RAD30,0.0)-ISNULL(ACRAD.RAD33,0.0)>0 '+
' UNION ALL '+
' SELECT RAF01+''-''+RAF02 AS ERPDH,RAF30 AS JE,RAF34 AS YKPJE,''销货折让'' AS BZ '+
' FROM ACRAE '+
' INNER JOIN ACRAF ON ACRAE.RAE01=ACRAF.RAF01 '+
' WHERE ACRAE.CONFORM_CODE=''Y'' AND ISNULL(RAF30,0.0)-ISNULL(RAF33,0.0)>0) AS AA WHERE AA.ERPDH=ACRED.RED07) '+
' ORDER BY RED04 ');
QryTmpB.Open;
if QryTmpB.RecordCount>0 then
begin
QryTmpB.First;
While not QryTmpB.Eof do
begin
if vJE-QryTmpB.FieldByName('RDB14').AsFloat>0 Then
begin
QryDetail.Append;
QryDetail.FieldByName('RDB03').AsString :=QryTmpB.FieldByName('RDB03').AsString; //来源 1：销货。2。销退。3:订单
QryDetail.FieldByName('RDB04').AsString :=QryTmpB.FieldByName('RDB04').AsString; //来源单别
QryDetail.FieldByName('RDB05').AsString :=QryTmpB.FieldByName('RDB05').AsString; //来源单号
QryDetail.FieldByName('RDB06').AsString :=QryTmpB.FieldByName('RDB06').AsString; //来源序号
QryDetail.FieldByName('RDB07').AsString :=QryTmpB.FieldByName('RDB07').AsString; //品号
QryDetail.FieldByName('RDB08').AsString :=QryTmpB.FieldByName('RDB08').AsString; //品名
QryDetail.FieldByName('RDB09').AsString :=QryTmpB.FieldByName('RDB09').AsString; //规格
QryDetail.FieldByName('RDB10').AsString :=QryTmpB.FieldByName('RDB10').AsString; //单位
QryDetail.FieldByName('RDB11').AsFloat :=QryTmpB.FieldByName('RDB11').AsFloat; //数量
QryDetail.FieldByName('RDB12').AsFloat :=QryTmpB.FieldByName('RDB12').AsFloat; //单价
QryDetail.FieldByName('RDB13').AsString :=QryTmpB.FieldByName('RDB13').AsString; //备注
QryDetail.FieldByName('RDB14').AsFloat :=QryTmpB.FieldByName('RDB14').AsFloat; //本次收款金额
QryDetail.FieldByName('RDB15').AsFloat :=QryTmpB.FieldByName('RDB15').AsFloat; //已收款金额
QryDetail.FieldByName('RDB17').AsString :=QryTmpB.FieldByName('RDB17').AsString; //快捷码
QryDetail.FieldByName('RDB18').AsString :=QryTmpB.FieldByName('RDB18').AsString; //客户单号
QryDetail.FieldByName('RDB19').AsFloat :=QryTmpB.FieldByName('RDB19').AsFloat; //其它费用
QryDetail.FieldByName('RDB20').AsString :=QryTmpB.FieldByName('RDB20').AsString; //对账单号
QryDetail.FieldByName('RDB21').AsString :=QryTmpB.FieldByName('RDB21').AsString; //对账序号
QryDetail.FieldByName('RDB22').AsString :=QryTmpB.FieldByName('RDB22').AsString; //送货单号
QryDetail.FieldByName('RDB23').AsString :=QryTmpB.FieldByName('RDB23').AsString; //客户品号
vJE :=vJE-QryTmpB.FieldByName('RDB14').AsFloat;
QryTmpB.Next;
end
else
begin
QryDetail.Append;
QryDetail.FieldByName('RDB03').AsString :=QryTmpB.FieldByName('RDB03').AsString; //来源 1：销货。2。销退。3:订单
QryDetail.FieldByName('RDB04').AsString :=QryTmpB.FieldByName('RDB04').AsString; //来源单别
QryDetail.FieldByName('RDB05').AsString :=QryTmpB.FieldByName('RDB05').AsString; //来源单号
QryDetail.FieldByName('RDB06').AsString :=QryTmpB.FieldByName('RDB06').AsString; //来源序号
QryDetail.FieldByName('RDB07').AsString :=QryTmpB.FieldByName('RDB07').AsString; //品号
QryDetail.FieldByName('RDB08').AsString :=QryTmpB.FieldByName('RDB08').AsString; //品名
QryDetail.FieldByName('RDB09').AsString :=QryTmpB.FieldByName('RDB09').AsString; //规格
QryDetail.FieldByName('RDB10').AsString :=QryTmpB.FieldByName('RDB10').AsString; //单位
QryDetail.FieldByName('RDB11').AsFloat :=QryTmpB.FieldByName('RDB11').AsFloat; //数量
QryDetail.FieldByName('RDB12').AsFloat :=QryTmpB.FieldByName('RDB12').AsFloat; //单价
QryDetail.FieldByName('RDB13').AsString :=QryTmpB.FieldByName('RDB13').AsString; //备注
QryDetail.FieldByName('RDB14').AsFloat :=vJE; //本次收款金额
QryDetail.FieldByName('RDB15').AsFloat :=QryTmpB.FieldByName('RDB15').AsFloat; //已收款金额
QryDetail.FieldByName('RDB17').AsString :=QryTmpB.FieldByName('RDB17').AsString; //快捷码
QryDetail.FieldByName('RDB18').AsString :=QryTmpB.FieldByName('RDB18').AsString; //客户单号
QryDetail.FieldByName('RDB19').AsFloat :=0.0; //其它费用
QryDetail.FieldByName('RDB20').AsString :=QryTmpB.FieldByName('RDB20').AsString; //对账单号
QryDetail.FieldByName('RDB21').AsString :=QryTmpB.FieldByName('RDB21').AsString; //对账序号
QryDetail.FieldByName('RDB22').AsString :=QryTmpB.FieldByName('RDB22').AsString; //送货单号
QryDetail.FieldByName('RDB23').AsString :=QryTmpB.FieldByName('RDB23').AsString; //客户品号
Break;
end;
end;
end;
finally
QryTmpB.Close;
QryTmpB.Free;
end;
end;
end;
procedure TfrmACRDABA.printClick(Sender: TObject);
begin
//inherited;
end;
procedure TfrmACRDABA.A41Click(Sender: TObject);
begin
inherited;
if (DSMaster.DataSet.IsEmpty) or (DSDetail.DataSet.IsEmpty) then
begin
Application.MessageBox('记录为空，打印无效．','提示信息',MB_IconInformation+MB_OK);
Abort;
end;
try
QryMaster.DisableControls;
frxReport1.Report.LoadFromFile(GetLogDir('Rpt')+'ACRDABA.FR3');
frxReport1.ShowReport(True);
QryMaster.EnableControls;
except
on E:Exception do
begin
messagedlg('报表文件不存在，单据打印失败 ！ '+#10#13+E.Message,mtError,[MBOK],0);
Abort;
end;
end;
end;
procedure TfrmACRDABA.N1Click(Sender: TObject);
begin
inherited;
if (DSMaster.DataSet.IsEmpty) or (DSDetail.DataSet.IsEmpty) then
begin
Application.MessageBox('记录为空，打印无效．','提示信息',MB_IconInformation+MB_OK);
Abort;
end;
try
QryMaster.DisableControls;
frxReport1.Report.LoadFromFile(GetLogDir('Rpt')+'ACRDABB.FR3');
frxReport1.ShowReport(True);
QryMaster.EnableControls;
except
on E:Exception do
begin
messagedlg('报表文件不存在，单据打印失败 ！ '+#10#13+E.Message,mtError,[MBOK],0);
Abort;
end;
end;
end;
procedure TfrmACRDABA.wwDBCDRDB04CustomDlg(Sender: TObject);
Var
sResult:String;
sl:TStringList;
i:integer;
QryTmp:TADOQuery;
begin
inherited;
if Trim(wwDBEdit3.Text)='' Then
begin
Application.MessageBox('客户编号不能为空!','提示信息',MB_IconInformation+MB_OK);
abort;
end;
if not Assigned(frmACRECDHELP) then
begin
frmACRECDHELP := TfrmACRECDHELP.Create(Application);
frmACRECDHELP.sWhere :=' REC04='+VarToSql(Trim(wwDBEdit3.Text))+' AND ACREC.CONFORM_CODE=''Y'' AND ISNULL(RED28,0.0)-ISNULL(RED19,0.0)<>0 AND EXISTS(SELECT 1 FROM (SELECT RAB03 AS ERPDH,RAB30 AS JE,RAB34 AS YKPJE,''销货'' AS BZ '+
' FROM ACRAA '+
' INNER JOIN ACRAB ON ACRAA.RAA01=ACRAB.RAB01 '+
' WHERE ACRAA.CONFORM_CODE=''Y'' AND ISNULL(RAB30,0.0)-ISNULL(RAB33,0.0)>0 '+
' UNION '+
' SELECT RBB03 AS ERPDH,RBB31 AS JE,RBB35 AS YKPJE,''销退'' AS BZ '+
' FROM ACRBA '+
' INNER JOIN ACRBB ON ACRBA.RBA01=ACRBB.RBB01 '+
' WHERE ACRBA.CONFORM_CODE=''Y'' AND ISNULL(RBB31,0.0)-ISNULL(RBB34,0.0)>0 '+
' UNION ALL '+
' SELECT RJB01+''-''+RJB02 AS ERPDH,RJB04 AS JE,RJB08 AS YKPJE,''其它'' AS BZ '+
' FROM ACRJB '+
' INNER JOIN ACRJA ON ACRJA.RJA01=ACRJB.RJB01 '+
' WHERE ACRJA.CONFORM_CODE=''Y'' AND ISNULL(ACRJB.RJB04,0.0)-ISNULL(ACRJB.RJB07,0.0)>0.0 '+
' UNION ALL '+
' SELECT RAD01+''-''+RAD02 AS ERPDH,RAD30 AS JE,RAD34 AS YKPJE,''其它销货'' AS BZ '+
' FROM ACRAC '+
' INNER JOIN ACRAD ON ACRAC.RAC01=ACRAD.RAD01 '+
' WHERE ACRAC.CONFORM_CODE=''Y'' AND ISNULL(ACRAD.RAD30,0.0)-ISNULL(ACRAD.RAD33,0.0)>0 '+
' UNION ALL '+
' SELECT RAF01+''-''+RAF02 AS ERPDH,RAF30 AS JE,RAF34 AS YKPJE,''销货折让'' AS BZ '+
' FROM ACRAE '+
' INNER JOIN ACRAF ON ACRAE.RAE01=ACRAF.RAF01 '+
' WHERE ACRAE.CONFORM_CODE=''Y'' AND ISNULL(RAF30,0.0)-ISNULL(RAF33,0.0)>0) AS AA WHERE AA.ERPDH=ACRED.RED07) ';
frmACRECDHELP.ShowModal;
sResult:=frmACRECDHELP.rtnHelpFieldName;
frmACRECDHELP.Free;
end;
if sResult<>'' Then
begin
sl:=TStringList.Create;
sl.Delimiter :=';';
sl.DelimitedText := Trim(sResult);
if sl.Count=1 then
begin
try
QryTmp:=TADOQuery.Create(nil);
QryTmp.Connection:=dlladocn;
QryTmp.EnableBCD :=False;
QryTmp.Close;
QryTmp.SQL.Clear;
QryTmp.SQL.Add(' SELECT RED03 AS RDB03, '+
' CASE WHEN (RED03=''1'' OR RED03=''2'') THEN SUBSTRING(RED07,1,4) WHEN (RED03=''3'' OR RED03=''5'' OR RED03=''6'' ) THEN '''' END RDB04, '+
' CASE WHEN (RED03=''1'' OR RED03=''2'') THEN SUBSTRING(RED07,6,11) WHEN (RED03=''3'' OR RED03=''5'' OR RED03=''6'' ) THEN SUBSTRING(RED07,1,11) END RDB05, '+
' CASE WHEN (RED03=''1'' OR RED03=''2'') THEN SUBSTRING(RED07,18,4) WHEN (RED03=''3'' OR RED03=''5'' OR RED03=''6'' ) THEN SUBSTRING(RED07,13,4) END RDB06, '+
' RED08 AS RDB07,RED09 AS RDB08,RED10 AS RDB09,RED11 AS RDB10,RED13 AS RDB11,RED12 AS RDB12,'''' AS RDB13, '+
' ISNULL(RED13,0.0)*ISNULL(RED12,0.0)+ISNULL(RED26,0.0)-ISNULL(RED19,0.0) AS RDB14,RED19 AS RDB15,RED06 AS RDB17,'+
' RED05 AS RDB18,RED26 AS RDB19,RED01 AS RDB20,RED02 AS RDB21,RED29 AS RDB22,RED30 AS RDB23 '+
' FROM ACRED '+
' INNER JOIN ACREC ON ACREC.REC01=ACRED.RED01 '+
' WHERE ACREC.CONFORM_CODE=''Y'' AND ISNULL(RED28,0.0)-ISNULL(RED19,0.0)<>0 AND RED01+''-''+RED02='+VarToSql(Trim(sl.Strings[0])));
QryTmp.Open;
if QryTmp.RecordCount >0 Then
begin
QryDetail.Edit;
QryDetail.FieldByName('RDB03').AsString :=QryTmp.FieldByName('RDB03').AsString;
QryDetail.FieldByName('RDB04').AsString :=QryTmp.FieldByName('RDB04').AsString;
QryDetail.FieldByName('RDB05').AsString :=QryTmp.FieldByName('RDB05').AsString;
QryDetail.FieldByName('RDB06').AsString :=QryTmp.FieldByName('RDB06').AsString;
QryDetail.FieldByName('RDB07').AsString :=QryTmp.FieldByName('RDB07').AsString;
QryDetail.FieldByName('RDB08').AsString :=QryTmp.FieldByName('RDB08').AsString;
QryDetail.FieldByName('RDB09').AsString :=QryTmp.FieldByName('RDB09').AsString;
QryDetail.FieldByName('RDB10').AsString :=QryTmp.FieldByName('RDB10').AsString;
QryDetail.FieldByName('RDB11').AsFloat :=QryTmp.FieldByName('RDB11').AsFloat;
QryDetail.FieldByName('RDB12').AsFloat :=QryTmp.FieldByName('RDB12').AsFloat;
QryDetail.FieldByName('RDB13').AsString :=QryTmp.FieldByName('RDB13').AsString;
QryDetail.FieldByName('RDB14').AsFloat :=QryTmp.FieldByName('RDB14').AsFloat;
QryDetail.FieldByName('RDB15').AsFloat :=QryTmp.FieldByName('RDB15').AsFloat;
QryDetail.FieldByName('RDB17').AsString :=QryTmp.FieldByName('RDB17').AsString;
QryDetail.FieldByName('RDB18').AsString :=QryTmp.FieldByName('RDB18').AsString;
QryDetail.FieldByName('RDB19').AsFloat :=QryTmp.FieldByName('RDB19').AsFloat;
QryDetail.FieldByName('RDB20').AsString :=QryTmp.FieldByName('RDB20').AsString;
QryDetail.FieldByName('RDB21').AsString :=QryTmp.FieldByName('RDB21').AsString;
QryDetail.FieldByName('RDB22').AsString :=QryTmp.FieldByName('RDB22').AsString;
QryDetail.FieldByName('RDB23').AsString :=QryTmp.FieldByName('RDB23').AsString;
end;
finally
QryTmp.Close;
QryTmp.Free;
end;
end
else
begin
for i:=1 to sl.Count-1 do
begin
if i=1 then
begin
try
QryTmp:=TADOQuery.Create(nil);
QryTmp.Connection:=dlladocn;
QryTmp.EnableBCD :=False;
QryTmp.Close;
QryTmp.SQL.Clear;
QryTmp.SQL.Add(' SELECT RED03 AS RDB03, '+
' CASE WHEN (RED03=''1'' OR RED03=''2'') THEN SUBSTRING(RED07,1,4) WHEN (RED03=''3'' OR RED03=''5'' OR RED03=''6'' ) THEN '''' END RDB04, '+
' CASE WHEN (RED03=''1'' OR RED03=''2'') THEN SUBSTRING(RED07,6,11) WHEN (RED03=''3'' OR RED03=''5'' OR RED03=''6'' ) THEN SUBSTRING(RED07,1,11) END RDB05, '+
' CASE WHEN (RED03=''1'' OR RED03=''2'') THEN SUBSTRING(RED07,18,4) WHEN (RED03=''3'' OR RED03=''5'' OR RED03=''6'' ) THEN SUBSTRING(RED07,13,4) END RDB06, '+
' RED08 AS RDB07,RED09 AS RDB08,RED10 AS RDB09,RED11 AS RDB10,RED13 AS RDB11,RED12 AS RDB12,'''' AS RDB13, '+
' ISNULL(RED13,0.0)*ISNULL(RED12,0.0)+ISNULL(RED26,0.0)-ISNULL(RED19,0.0) AS RDB14,RED19 AS RDB15,RED06 AS RDB17,'+
' RED05 AS RDB18,RED26 AS RDB19,RED01 AS RDB20,RED02 AS RDB21,RED29 AS RDB22,RED30 AS RDB23 '+
' FROM ACRED '+
' INNER JOIN ACREC ON ACREC.REC01=ACRED.RED01 '+
' WHERE ACREC.CONFORM_CODE=''Y'' AND ISNULL(RED28,0.0)-ISNULL(RED19,0.0)<>0 AND RED01+''-''+RED02='+VarToSql(Trim(sl.Strings[0])));
QryTmp.Open;
if QryTmp.RecordCount >0 Then
begin
QryDetail.Edit;
QryDetail.FieldByName('RDB03').AsString :=QryTmp.FieldByName('RDB03').AsString;
QryDetail.FieldByName('RDB04').AsString :=QryTmp.FieldByName('RDB04').AsString;
QryDetail.FieldByName('RDB05').AsString :=QryTmp.FieldByName('RDB05').AsString;
QryDetail.FieldByName('RDB06').AsString :=QryTmp.FieldByName('RDB06').AsString;
QryDetail.FieldByName('RDB07').AsString :=QryTmp.FieldByName('RDB07').AsString;
QryDetail.FieldByName('RDB08').AsString :=QryTmp.FieldByName('RDB08').AsString;
QryDetail.FieldByName('RDB09').AsString :=QryTmp.FieldByName('RDB09').AsString;
QryDetail.FieldByName('RDB10').AsString :=QryTmp.FieldByName('RDB10').AsString;
QryDetail.FieldByName('RDB11').AsFloat :=QryTmp.FieldByName('RDB11').AsFloat;
QryDetail.FieldByName('RDB12').AsFloat :=QryTmp.FieldByName('RDB12').AsFloat;
QryDetail.FieldByName('RDB13').AsString :=QryTmp.FieldByName('RDB13').AsString;
QryDetail.FieldByName('RDB14').AsFloat :=QryTmp.FieldByName('RDB14').AsFloat;
QryDetail.FieldByName('RDB15').AsFloat :=QryTmp.FieldByName('RDB15').AsFloat;
QryDetail.FieldByName('RDB17').AsString :=QryTmp.FieldByName('RDB17').AsString;
QryDetail.FieldByName('RDB18').AsString :=QryTmp.FieldByName('RDB18').AsString;
QryDetail.FieldByName('RDB19').AsFloat :=QryTmp.FieldByName('RDB19').AsFloat;
QryDetail.FieldByName('RDB20').AsString :=QryTmp.FieldByName('RDB20').AsString;
QryDetail.FieldByName('RDB21').AsString :=QryTmp.FieldByName('RDB21').AsString;
QryDetail.FieldByName('RDB22').AsString :=QryTmp.FieldByName('RDB22').AsString;
QryDetail.FieldByName('RDB23').AsString :=QryTmp.FieldByName('RDB23').AsString;
end;
finally
QryTmp.Close;
QryTmp.Free;
end;
end
else
begin
QryDetail.Append;
try
QryTmp:=TADOQuery.Create(nil);
QryTmp.Connection:=dlladocn;
QryTmp.EnableBCD :=False;
QryTmp.Close;
QryTmp.SQL.Clear;
QryTmp.SQL.Add(' SELECT RED03 AS RDB03, '+
' CASE WHEN (RED03=''1'' OR RED03=''2'') THEN SUBSTRING(RED07,1,4) WHEN (RED03=''3'' OR RED03=''5'' OR RED03=''6'' ) THEN '''' END RDB04, '+
' CASE WHEN (RED03=''1'' OR RED03=''2'') THEN SUBSTRING(RED07,6,11) WHEN (RED03=''3'' OR RED03=''5'' OR RED03=''6'' ) THEN SUBSTRING(RED07,1,11) END RDB05, '+
' CASE WHEN (RED03=''1'' OR RED03=''2'') THEN SUBSTRING(RED07,18,4) WHEN (RED03=''3'' OR RED03=''5'' OR RED03=''6'' ) THEN SUBSTRING(RED07,13,4) END RDB06, '+
' RED08 AS RDB07,RED09 AS RDB08,RED10 AS RDB09,RED11 AS RDB10,RED13 AS RDB11,RED12 AS RDB12,'''' AS RDB13, '+
' ISNULL(RED13,0.0)*ISNULL(RED12,0.0)+ISNULL(RED26,0.0)-ISNULL(RED19,0.0) AS RDB14,RED19 AS RDB15,RED06 AS RDB17,'+
' RED05 AS RDB18,RED26 AS RDB19,RED01 AS RDB20,RED02 AS RDB21,RED29 AS RDB22,RED30 AS RDB23 '+
' FROM ACRED '+
' INNER JOIN ACREC ON ACREC.REC01=ACRED.RED01 '+
' WHERE ACREC.CONFORM_CODE=''Y'' AND ISNULL(RED28,0.0)-ISNULL(RED19,0.0)<>0 AND RED01+''-''+RED02='+VarToSql(Trim(sl.Strings[i-1])));
QryTmp.Open;
if QryTmp.RecordCount >0 Then
begin
QryDetail.Edit;
QryDetail.FieldByName('RDB03').AsString :=QryTmp.FieldByName('RDB03').AsString;
QryDetail.FieldByName('RDB04').AsString :=QryTmp.FieldByName('RDB04').AsString;
QryDetail.FieldByName('RDB05').AsString :=QryTmp.FieldByName('RDB05').AsString;
QryDetail.FieldByName('RDB06').AsString :=QryTmp.FieldByName('RDB06').AsString;
QryDetail.FieldByName('RDB07').AsString :=QryTmp.FieldByName('RDB07').AsString;
QryDetail.FieldByName('RDB08').AsString :=QryTmp.FieldByName('RDB08').AsString;
QryDetail.FieldByName('RDB09').AsString :=QryTmp.FieldByName('RDB09').AsString;
QryDetail.FieldByName('RDB10').AsString :=QryTmp.FieldByName('RDB10').AsString;
QryDetail.FieldByName('RDB11').AsFloat :=QryTmp.FieldByName('RDB11').AsFloat;
QryDetail.FieldByName('RDB12').AsFloat :=QryTmp.FieldByName('RDB12').AsFloat;
QryDetail.FieldByName('RDB13').AsString :=QryTmp.FieldByName('RDB13').AsString;
QryDetail.FieldByName('RDB14').AsFloat :=QryTmp.FieldByName('RDB14').AsFloat;
QryDetail.FieldByName('RDB15').AsFloat :=QryTmp.FieldByName('RDB15').AsFloat;
QryDetail.FieldByName('RDB17').AsString :=QryTmp.FieldByName('RDB17').AsString;
QryDetail.FieldByName('RDB18').AsString :=QryTmp.FieldByName('RDB18').AsString;
QryDetail.FieldByName('RDB19').AsFloat :=QryTmp.FieldByName('RDB19').AsFloat;
QryDetail.FieldByName('RDB20').AsString :=QryTmp.FieldByName('RDB20').AsString;
QryDetail.FieldByName('RDB21').AsString :=QryTmp.FieldByName('RDB21').AsString;
QryDetail.FieldByName('RDB22').AsString :=QryTmp.FieldByName('RDB22').AsString;
QryDetail.FieldByName('RDB23').AsString :=QryTmp.FieldByName('RDB23').AsString;
end;
finally
QryTmp.Close;
QryTmp.Free;
end;
end;
end;
end;
end;
end;
procedure TfrmACRDABA.frxReport1BeforePrint(Sender: TfrxReportComponent);
begin
inherited;
TfrxMemoView(frxReport1.FindObject('memo58')).Text :=wwDBComboBox2.Text;
TfrxMemoView(frxReport1.FindObject('memo44')).Text :=wwDBComboBox3.Text;
TfrxMemoView(frxReport1.FindObject('memo46')).Text :=wwDBComboBox4.Text;
TfrxMemoView(frxReport1.FindObject('memo53')).Text :=wwDBComboBox1.Text;
end;
procedure TfrmACRDABA.wwDBEdit3Exit(Sender: TObject);
Var
QryTmp:TADOQuery;
begin
inherited;
if QryMaster.State in [dsEdit,dsInsert] Then
begin
try
QryTmp:=TADOQuery.Create(nil);
QryTmp.Connection:=DllAdoCn;
QryTmp.EnableBCD :=False;
QryTmp.Close;
QryTmp.SQL.Clear;
QryTmp.SQL.Add('SELECT RGA11 FROM ACRGA WHERE RGA01='+VarToSql(Trim(wwDBEdit3.Text)));
QryTmp.Open;
QryMaster.FieldByName('RDA09').AsString := QryTmp.FieldByName('RGA11').AsString; //币种
finally
QryTmp.Close;
QryTmp.Free;
end;
if not GetSQLEmpty(' SELECT RDA04,SUM(ISNULL(RDA08,0.0)-ISNULL(RDA12,0.0)) AS YE '+
' FROM ACRDA '+
' WHERE RDA11=''2'' AND RDA04='+VarToSql(Trim(wwDBEdit3.Text))+
' GROUP BY RDA04 '+
' HAVING SUM(ISNULL(RDA08,0.0)-ISNULL(RDA12,0.0))>0 ') then
begin
Application.MessageBox('此客户有预收款未冲销,请冲销后再进行收款!','提示信息',MB_IconInformation+MB_OK);
abort;
end;
end;
end;
initialization
RegisterClass(TfrmACRDABA);
finalization
UnRegisterClass(TfrmACRDABA);
end.
646
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
