Code:
'Date Created: 29-Jul-2016 04:57:10 PM
'Last Updated: 03-Jul-2018 02:20:19 PM
'Created By: Admin
'Updated By : Admin
DIM SHARED frm as D
DIM SHARED to as D
DIM SHARED varC_result as C
ok_button_label = "&OK"
cancel_button_label = "&Cancel"
varC_result = ui_dlg_box("Choose Range",<<%dlg%
{region}
From:| [%DATE;P=popup.calendar(dtoc(frm));I=popup.calendar%.15frm!frm_*];
To:| [%DATE;P=popup.calendar(dtoc(to));I=popup.calendar%.15to!to_*];
{endregion};
{line=1,0};
{region}
<*15=ok_button_label!OK> <15=cancel_button_label!CANCEL>
{endregion};
%dlg%,<<%code%
if left(a_dlg_button,4) = "frm_" then
if a_dlg_button = "frm_killfocus" then
frm = ctod(dtoc(frm))
end if
a_dlg_button = ""
end if
if left(a_dlg_button,3) = "to_" then
if a_dlg_button = "to_killfocus" then
to = ctod(dtoc(to))
end if
a_dlg_button = ""
end if
%code%)
count=1
while count <10
pday="pd"+count
'DIM a connection variable
DIM cn as SQL::Connection
dim flagResult as l
flagResult = cn.open("::Name::pat")
if flagResult = .f. then
ui_msg_box("Error","Could not connect to database. Error reported was: " + crlf() + cn.CallResult.text)
end
end if
'oring
'SELECT joinid, cfname, clname,straddy,city,state,zipcode,eagent,moneypd,epdocs,underwriter,homephone,cellphone,work_phone,ph_name1,ph_name2,ph_name3,status,enr_status,pay_status,follow_up,custname,saledate,cliemail,clifax,enroldate,c_type,card_name, AES_DECRYPT(c_num, sha2('passthis420',512)), c_xp, c_vc, c_am_vc, pd1,pa1,ba,bc,bs,bz,verif1,verif2,verif3,verif4,verif5,verif6,verif7,baddsame,verif1,verif2,verif3,verif4,verif5,verif6,verif7,trans_hist FROM pat.ccb_info INNER JOIN clientd ON pat.ccb_info.idclientd=clientd.idclientd WHERE (pd1 BETWEEN :frm and :to) and status <> 'dead'
'SELECT joinid, cfname,lname,straddy,city,state,zipcode,eagent,moneypd,epdocs,underwriter,homephone,cellphone,work_phone,ph_name1,ph_name2,ph_name3,status,enr_status,pay_status,follow_up,custname,saledate,cliemail,clifax,enroldate,c_type,card_name, AES_DECRYPT(c_num, sha2('passthis420',512)), c_xp, c_vc, c_am_vc, pd1,pa1,ba,bc,bs,bz,verif1,verif2,verif3,verif4,verif5,verif6,verif7,baddsame,verif1,verif2,verif3,verif4,verif5,verif6,verif7,trans_hist,auth_sent,lender,loan_num FROM pat.ccb_info INNER JOIN clientd ON pat.ccb_info.idclientd=clientd.idclientd WHERE (pd1 BETWEEN :frm and :to) and status <> 'dead' INNER JOIN intake ON pat.intake.idclientd=client.idclientd
cn.PortableSQLEnabled = .f.
dim sqlStatement as c
sqlStatement = "SELECT joinid, cfname, clname,price_quote,pay_status,enr_status,straddy,city,state,zipcode,eagent,assign_to,assign_date,magnet,moneypd,epdocs,underwriter,homephone,cellphone,ph_name1,ph_name2,ph_name3,work_phone,status,follow_up,custname,saledate,cliemail,clifax,enroldate,c_type,card_name, AES_DECRYPT(c_num, sha2('passthis420',512)), c_xp, c_vc, c_am_vc,pd1,pa1,pd2,pa2,pd3,pa3,pd4,pa4,pd5,pa5,pd6,pa6,pd7,pa7,pd8,pa8,pd9,pa9,pd10,pa10,pd11,pa11,pd12,pa12, ba,bc,bs,bz,verif1,verif2,verif3,verif4,verif5,verif6,verif7,verif8,verif9,verif10,verif11,verif12,baddsame,trans_hist FROM pat.ccb_info INNER JOIN clientd ON pat.ccb_info.idclientd=clientd.idclientd WHERE ("+pday+" BETWEEN :frm and :to and status not in ('dead' ,'cancelled','refunded'))"
DIM args as sql::arguments
if a5_eval_valid_expression("=Var->frm",local_variables()) then
args.add("frm",convert_type(a5_eval_expression("=Var->frm",local_variables()),"D"))
end If
if a5_eval_valid_expression("=Var->to",local_variables()) then
args.add("to",convert_type(a5_eval_expression("=Var->to",local_variables()),"D"))
end If
if a5_eval_valid_expression("=Var->pday",local_variables()) then
args.add("pday",convert_type(a5_eval_expression("=Var->pday",local_variables()),"c"))
end If
'Execute the Query
flagResult = cn.Execute(sqlStatement,args)
if flagResult = .f. then
ui_msg_box("Error",cn.CallResult.text)
cn.close()
end
end if
'Displays a message box (style:'OK Button', image: 'Information symbol', id: 'pd').
dim rs as sql::resultset
rs = cn.ResultSet
dim data as C
data = rs.ToString()
if data = "" then
goto end
end if
flagResult = cn.Execute(sqlStatement,args)
if flagResult = .f. then
ui_msg_box("Error",cn.CallResult.text)
cn.close()
end
end if
dim rs as sql::resultset
rs = cn.ResultSet
dim ttbl as p
dim flagLoop as l
flagLoop = .t.
while flagLoop
payment_write()
flagLoop = rs.NextRow()
end while
cn.close()
end:
count=count+1
end while
dim shared ttl as n
ttl=0.00
dim shared ts1 as n
dim shared ts2 as n
dim shared ts3 as n
dim shared ts4 as n
dim shared ts5 as n
dim shared ts6 as n
dim shared ts7 as n
dim shared ts8 as n
dim shared ts9 as n
dim shared ts10 as n
dim shared ts11 as n
dim shared ts12 as n
ts1= tablesum("paytoday","this_payment<>0 ","this_payment")
ttl=ts1+ts2+ts3+ts4+ts5+ts6+ts7+ts8+ts9+ts10+ts11+ts12
'Displays a message box (style:'OK Button', image: 'Information symbol', id: 'ttldia').
DIM SHARED ttldia_result as N
DIM SHARED ttldia_OK_Button as L
ttldia_OK_Button = .F.
title_var = "Money Coming "
message_text_var ="Money Expected "+ ttl
ttldia_result=ui_msg_box(title_var,message_text_var,UI_OK+ UI_FIRST_BUTTON_DEFAULT+ UI_INFORMATION_SYMBOL)
'Test to see which button on the message box was pressed....
SELECT
CASE ttldia_result = UI_OK_SELECTED
ttldia_OK_Button= .t.
END SELECT
'Open a Form or Browse layout, displaying all, or selected records in the layout.
query.filter = ".t."
query.order = "this_pdate"
'Open the layout_type showing just the records that satisfy the filter
DIM Shared varP_paytoday as P
DIM layout_name as c
layout_name = "paytoday"
dim tempP as p
'Get pointer to existing window. In case layout_name is qualified with a dictionary name, extract up to first @. In case formname has spaces, normalize it
tempP=obj(":"+object_name_normalize(word(layout_name,1,"@")))
'Test if pointer is valid
if is_object(tempP) then
'Test if pointer refers to a form or browse
if tempP.class() = "form" .or. tempP.class() = "browse" then
dim flagIsBaseFilter as l
flagIsBaseQuery = .t.
if flagIsBaseFilter then
'Apply the query to the form or browse as the Base query. (this means that
'the user cannot turn the query off, only add to the query)
tempP.BaseQueryRun(query.filter,query.order)
else
tempP.QueryRun(query.filter,query.order)
end if
'then activate the already open window
tempP.activate()
else
'Window is not already open, so open it
varP_paytoday = :Browse.viewqueried(layout_name,query.filter, query.order )
end if
else
varP_paytoday = :Browse.viewqueried(layout_name,query.filter, query.order )
end if
'script_play_local("gpr2")
How to overcome a sql rs.data() empty resultset
Reply With Quote
Bookmarks