其中DateTimeToSql 需要引用gDateFunc单元 ShowWarning对话框需要引用,SynchReadData需要引用MTSCommon单元,SearchFieldNameValue需要引用
//查询:按支付时间查询,按运单号码查询
procedure TBalanceStatWSJ_payForm.RzBitBtnSearchSQLClick(Sender: TObject);
var
StartTime:String;//开始时间
EndTime:string; //结束时间
MSQLSTR:string;//SQL语句
MSQLTerm:string;//拼接语句
MErrorInfoStr:string;//错误语句
i :Integer;
begin
inherited;
MSQLSTR := '';
MSQLTerm:='';
MErrorInfoStr := '查询MPOS支付结果查询出错,请重新查询!!!';
//获取开始时间和结束时间,需要注意的是,转为浮点型是因为数据库有可能是繁体或者是简体的,
//是因为他们的显示格式不一样,但是都是浮点型转换过来的,所以需要先将日期格式转换为datatime类型
//再将datatime转换为浮点型进行运算,调用 gDateFunc单元的两个方法,MergeDataTime ,先合并时间,再用DATETIMETOSQL转成浮点型字符串
//语句一定是字符串才能拼起来的,都是它已经转为546546.121这个格式的了,不是‘2013-02-06 05:55:00’,所以加入where条件是不需要加单引号了
//命名的时候,注意命名规范如MSQLSTR是M开头 ,当我们通过对象点属性时,有时无法提示,如果编译通过说明这是正常的,直接用手敲就是了
//写注释的时候要注意注释中有特殊字符,注释下面第一行的代码被当做注释使用了,所以无法取到值
//获取开始时间
StartTime:= DateTimeToSql(MergeDateTime(RzDateTimePickerDateStart.Date,RzDateTimePickerTimeStart.Time));
//获取结束时间
EndTime:=DateTimeToSql(MergeDateTime(RzDateTimePickerDateEnd.Date,RzDateTimePickerTimeEnd.Time));
//拼接SQL语句
MSQLSTR := MSQLSTR + 'select ti.BusinessTime, ti.BarCode, m.SDShipp, ti.CardNo, ti.TerminalDealID,' + #13
+'ti.UnionpayDealID, ti.TotalPayment, bs.StateName, bu.UntreadReasonName,ti.Commoditys,' + #13
+'ti.Remark from tiInADCPOS_EX_Sign_Result ti ' + #13;
case RzPageControlSearchTerm.ActivePageIndex of
0:
begin
MSQLSTR := MSQLSTR
+'left outer join mManifest m on ti.BarCode = m.Jobno ' + #13
+'left outer join bState bs on ti.State = bs.statecode ' + #13
+'left outer join bUntreadReasonCode bu on ti.UntreadReasoncode = bu.UntreadReasonCode where ' + #13
//添加条件 开始时间和结束时间
+' BusinessTime between ' + startTime + ' and '+EndTime ;
//如果终端ID不为空
if RzEditterminal_id.text <> '' then
begin
MSQLSTR := MSQLSTR + ' and TerminalDealID ='+quotedstr(RzEditterminal_id.text);
end;
// 如果客户不为空
if RzButtonEditSearchSearchSDShipp.text <> '' then
begin
MSQLSTR := MSQLSTR + ' and SDShipp ='+quotedstr(RzButtonEditSearchSearchSDShipp.Text);
end;
// 如果银联ID不为空
if edtUnionpayDealID.Text <> '' then
begin
MSQLSTR := MSQLSTR + ' and UnionpayDealID ='+quotedstr(edtUnionpayDealID.Text);
end;
end;
1:
begin
MSQLSTR := MSQLSTR
+'left outer join mManifest m on ti.BarCode = m.Jobno ' + #13
+'left outer join bState bs on ti.State = bs.statecode ' + #13
+'left outer join bUntreadReasonCode bu on ti.UntreadReasoncode = ' + #13
+'b
其中DateTimeToSql 需要引用gDateFunc单元 ShowWarning对话框需要引用,SynchReadData需要引用MTSCommon单元,SearchFieldNameValue需要引用
//查询:按支付时间查询,按运单号码查询
procedure TBalanceStatWSJ_payForm.RzBitBtnSearchSQLClick(Sender: TObject);
var
StartTime:String;//开始时间
EndTime:string; //结束时间
MSQLSTR:string;//SQL语句
MSQLTerm:string;//拼接语句
MErrorInfoStr:string;//错误语句
i :Integer;
begin
inherited;
MSQLSTR := '';
MSQLTerm:='';
MErrorInfoStr := '查询MPOS支付结果查询出错,请重新查询!!!';
//获取开始时间和结束时间,需要注意的是,转为浮点型是因为数据库有可能是繁体或者是简体的,
//是因为他们的显示格式不一样,但是都是浮点型转换过来的,所以需要先将日期格式转换为datatime类型
//再将datatime转换为浮点型进行运算,调用 gDateFunc单元的两个方法,MergeDataTime ,先合并时间,再用DATETIMETOSQL转成浮点型字符串
//语句一定是字符串才能拼起来的,都是它已经转为546546.121这个格式的了,不是‘2013-02-06 05:55:00’,所以加入where条件是不需要加单引号了
//命名的时候,注意命名规范如MSQLSTR是M开头 ,当我们通过对象点属性时,有时无法提示,如果编译通过说明这是正常的,直接用手敲就是了
//写注释的时候要注意注释中有特殊字符,注释下面第一行的代码被当做注释使用了,所以无法取到值
//获取开始时间
StartTime:= DateTimeToSql(MergeDateTime(RzDateTimePickerDateStart.Date,RzDateTimePickerTimeStart.Time));
//获取结束时间
EndTime:=DateTimeToSql(MergeDateTime(RzDateTimePickerDateEnd.Date,RzDateTimePickerTimeEnd.Time));
//拼接SQL语句
MSQLSTR := MSQLSTR + 'select ti.BusinessTime, ti.BarCode, m.SDShipp, ti.CardNo, ti.TerminalDealID,' + #13
+'ti.UnionpayDealID, ti.TotalPayment, bs.StateName, bu.UntreadReasonName,ti.Commoditys,' + #13
+'ti.Remark from tiInADCPOS_EX_Sign_Result ti ' + #13;
case RzPageControlSearchTerm.ActivePageIndex of
0:
begin
MSQLSTR := MSQLSTR
+'left outer join mManifest m on ti.BarCode = m.Jobno ' + #13
+'left outer join bState bs on ti.State = bs.statecode ' + #13
+'left outer join bUntreadReasonCode bu on ti.UntreadReasoncode = bu.UntreadReasonCode where ' + #13
//添加条件 开始时间和结束时间
+' BusinessTime between ' + startTime + ' and '+EndTime ;
//如果终端ID不为空
if RzEditterminal_id.text <> '' then
begin
MSQLSTR := MSQLSTR + ' and TerminalDealID ='+quotedstr(RzEditterminal_id.text);
end;
// 如果客户不为空
if RzButtonEditSearchSearchSDShipp.text <> '' then
begin
MSQLSTR := MSQLSTR + ' and SDShipp ='+quotedstr(RzButtonEditSearchSearchSDShipp.Text);
end;
// 如果银联ID不为空
if edtUnionpayDealID.Text <> '' then
begin
MSQLSTR := MSQLSTR + ' and UnionpayDealID ='+quotedstr(edtUnionpayDealID.Text);
end;
end;
1:
begin
MSQLSTR := MSQLSTR
+'left outer join mManifest m on ti.BarCode = m.Jobno ' + #13
+'left outer join bState bs on ti.State = bs.statecode ' + #13
+'left outer join bUntreadReasonCode bu on ti.UntreadReasoncode = ' + #13
+'b