<% IF TYPE([orequest])=[O] functionname=orequest.form("functionname") thevtype=orequest.form("vtype") thetrnum=orequest.form("trnum") ipAddress = nvl(ORequest.ServerVariables("HTTP_X_FORWARDED_FOR"),'') IF empty(IPADDRESS) OR LOWER(IPADDRESS)="unknown" ipAddress = [remote ]+nvl(oRequest.ServerVariables("REMOTE_ADDR"),'') ENDIF *ipaddress of the calling pc or it can be the domain name too theCallingPC=nvl(orequest.servervariables('http_host'),'') sqlinprogressfile=addbs(oprop.appstartpath)+[temp\TimerInProgress.txt] ELSE theCallingPC=SUBSTR(SYS(0),1,AT([#],SYS(0))-1) functionname=[SQLTRANSACTIONS] sqlinprogressfile=addbs(mydir)+[temp\TimerInProgress.txt] SET PROCEDURE TO (mydir+[mh\prg\shahfuncs.fxp]) ADDITIVE SET PROCEDURE TO (mydir+[mh\prg\main.prg]) ADDITIVE ENDIF totalresponse=[] theresponse=[Transaction updates finished!] *On the same network, it gives the computer name however it will be the local ip address of the pc if called from remote processdtime=datetime() tmakestring=[dts:]+ttoc(processdtime)+[pc:]+theCallingPC+[end:] if nvl(functionname,'')=[updatetransactions] totalresponse=updatetransactions(thevtype,thetrnum) endif if nvl(functionname,'')='SQLTRANSACTIONS' if not file(sqlinprogressfile) strtofile(tmakestring,sqlinprogressfile) else inprocess=FILETOSTR(sqlinprogressfile) startingdt=strextract(inprocess,'dts:','pc:') calledfrompc=strextract(inprocess,'pc:','end:') if abs(datetime()-ctot(startingdt))<60 return [Update called from ]+calledfrompc+[ on ]+startingdt endif if abs(fdate(sqlinprogressfile,1)-datetime())>60 strtofile(tmakestring,sqlinprogressfile) endif ENDIF upmastertbl=[cashbill] updchildtbl=[cbilldata] updvtype=PADR([CashBill],10,[ ]) tSQL=[ SELECT TOP 20 x.transid,dtime, updvtype as vtype FROM (']+dbclocation+upmastertbl+[') x WHERE processdtime-x.modified>10 and x.sql=.t. and x.transid ] tSQL=tSQL+[ in (SELECT DISTINCT y.transid FROM (']+dbclocation+updchildtbl+[') y where y.sql=.t. ) ] tSQL=tSQL+[ ORDER BY x.dtime into cursor sqlupdater Readwrite ] &tsql tsql2=[SELECT distinct y.transid, datetime() as dtime, updvtype as vtype FROM (']+dbclocation+updchildtbl+[') y where y.sql=.t. into cursor sqlother2 ] &tsql2 if reccount([sqlother2])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd ENDIF tsql2=[SELECT distinct x.transid, dtime, updvtype as vtype FROM (']+dbclocation+upmastertbl+[') x where processdtime-x.modified>100 and x.sql=.t. into cursor sqlother2 ] &tsql2 if reccount([sqlother2])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif use in select(upmastertbl) use in select(updchildtbl) if reccount([sqlupdater])<20 upmastertbl=[bill] updchildtbl=[billdata] updvtype=[Bill] tSQL=[ SELECT TOP 20 x.transid,dtime, updvtype as vtype FROM (']+dbclocation+upmastertbl+[') x WHERE processdtime-x.modified>10 and x.sql=.t. and x.transid ] tSQL=tSQL+[ in (SELECT DISTINCT y.transid FROM (']+dbclocation+updchildtbl+[') y where y.sql=.t. ) ] tSQL=tSQL+[ ORDER BY x.dtime into cursor sqlother ] &tsql if reccount([sqlother])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif tsql2=[SELECT distinct y.transid, datetime() as dtime, updvtype as vtype FROM (']+dbclocation+updchildtbl+[') y where y.sql=.t. into cursor sqlother2 ] &tsql2 if reccount([sqlother2])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd ENDIF tsql2=[SELECT distinct x.transid, dtime, updvtype as vtype FROM (']+dbclocation+upmastertbl+[') x where processdtime-x.modified>100 and x.sql=.t. into cursor sqlother2 ] &tsql2 if reccount([sqlother2])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif use in select(upmastertbl) use in select(updchildtbl) &tsql if reccount([sqlupdater])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif endif if reccount([sqlupdater])<20 upmastertbl=[invoice] updchildtbl=[invdata] updvtype=[Invoice] tSQL=[ SELECT TOP 20 x.transid,dtime, updvtype as vtype FROM (']+dbclocation+upmastertbl+[') x WHERE processdtime-x.modified>10 and x.sql=.t. and x.transid ] tSQL=tSQL+[ in (SELECT DISTINCT y.transid FROM (']+dbclocation+updchildtbl+[') y where y.sql=.t. ) ] tSQL=tSQL+[ ORDER BY x.dtime into cursor sqlother ] &tsql if reccount([sqlother])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif tsql2=[SELECT distinct y.transid, datetime() as dtime, updvtype as vtype FROM (']+dbclocation+updchildtbl+[') y where y.sql=.t. into cursor sqlother2 ] &tsql2 if reccount([sqlother2])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd ENDIF tsql2=[SELECT distinct x.transid, dtime, updvtype as vtype FROM (']+dbclocation+upmastertbl+[') x where processdtime-x.modified>100 and x.sql=.t. into cursor sqlother2 ] &tsql2 if reccount([sqlother2])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif use in select(upmastertbl) use in select(updchildtbl) endif if reccount([sqlupdater])<20 upmastertbl=[CashSale] updchildtbl=[cashdata] updvtype=[CashSale] tSQL=[ SELECT TOP 20 x.transid,dtime, updvtype as vtype FROM (']+dbclocation+upmastertbl+[') x WHERE processdtime-x.modified>10 and x.sql=.t. and x.transid ] tSQL=tSQL+[ in (SELECT DISTINCT y.transid FROM (']+dbclocation+updchildtbl+[') y where y.sql=.t. ) ] tSQL=tSQL+[ ORDER BY x.dtime into cursor sqlother ] &tsql if reccount([sqlother])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif tsql2=[SELECT distinct y.transid, datetime() as dtime, updvtype as vtype FROM (']+dbclocation+updchildtbl+[') y where y.sql=.t. into cursor sqlother2 ] &tsql2 if reccount([sqlother2])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd ENDIF tsql2=[SELECT distinct x.transid, dtime, updvtype as vtype FROM (']+dbclocation+upmastertbl+[') x where processdtime-x.modified>100 and x.sql=.t. into cursor sqlother2 ] &tsql2 if reccount([sqlother2])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif use in select(upmastertbl) use in select(updchildtbl) ENDIF if reccount([sqlupdater])<20 upmastertbl=[rBill] updchildtbl=[rbilldata] updvtype=[CreditBill] tSQL=[ SELECT TOP 20 x.transid,dtime, updvtype as vtype FROM (']+dbclocation+upmastertbl+[') x WHERE processdtime-x.modified>10 and x.sql=.t. and x.transid ] tSQL=tSQL+[ in (SELECT DISTINCT y.transid FROM (']+dbclocation+updchildtbl+[') y where y.sql=.t. ) ] tSQL=tSQL+[ ORDER BY x.dtime into cursor sqlother ] &tsql if reccount([sqlother])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif tsql2=[SELECT distinct y.transid, datetime() as dtime, updvtype as vtype FROM (']+dbclocation+updchildtbl+[') y where y.sql=.t. into cursor sqlother2 ] &tsql2 if reccount([sqlother2])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd ENDIF tsql2=[SELECT distinct x.transid, dtime, updvtype as vtype FROM (']+dbclocation+upmastertbl+[') x where processdtime-x.modified>100 and x.sql=.t. into cursor sqlother2 ] &tsql2 if reccount([sqlother2])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif use in select(upmastertbl) use in select(updchildtbl) ENDIF if reccount([sqlupdater])<20 upmastertbl=[adjust] updchildtbl=[adjdata] updvtype=[Adjustment] tSQL=[ SELECT TOP 20 x.transid, dtime, updvtype as vtype FROM (']+dbclocation+upmastertbl+[') x WHERE processdtime-x.modified>10 and x.sql=.t. and x.transid ] tSQL=tSQL+[ in (SELECT DISTINCT y.transid FROM (']+dbclocation+updchildtbl+[') y where y.sql=.t. ) ] tSQL=tSQL+[ ORDER BY x.dtime into cursor sqlother ] &tsql if reccount([sqlother])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif tsql2=[SELECT distinct y.transid, datetime() as dtime, updvtype as vtype FROM (']+dbclocation+updchildtbl+[') y where y.sql=.t. into cursor sqlother2 ] &tsql2 if reccount([sqlother2])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd ENDIF tsql2=[SELECT distinct x.transid, dtime, updvtype as vtype FROM (']+dbclocation+upmastertbl+[') x where processdtime-x.modified>100 and x.sql=.t. into cursor sqlother2 ] &tsql2 if reccount([sqlother2])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif use in select(upmastertbl) use in select(updchildtbl) ENDIF if reccount([sqlupdater])<20 upmastertbl=[storetrf] updchildtbl=[strfdata] updvtype=[StoreTRF] tSQL=[ SELECT TOP 20 x.transid,dtime, updvtype as vtype FROM (']+dbclocation+upmastertbl+[') x WHERE processdtime-x.modified>10 and x.sql=.t. and x.transid ] tSQL=tSQL+[ in (SELECT DISTINCT y.transid FROM (']+dbclocation+updchildtbl+[') y where y.sql=.t. ) ] tSQL=tSQL+[ ORDER BY x.dtime into cursor sqlother ] &tsql if reccount([sqlother])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif tsql2=[SELECT distinct y.transid, datetime() as dtime, updvtype as vtype FROM (']+dbclocation+updchildtbl+[') y where y.sql=.t. into cursor sqlother2 ] &tsql2 if reccount([sqlother2])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd ENDIF tsql2=[SELECT distinct x.transid, dtime, updvtype as vtype FROM (']+dbclocation+upmastertbl+[') x where processdtime-x.modified>100 and x.sql=.t. into cursor sqlother2 ] &tsql2 if reccount([sqlother2])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif use in select(upmastertbl) use in select(updchildtbl) ENDIF if reccount([sqlupdater])<20 upmastertbl=[rcinvoice] updchildtbl=[rcinvdata] updvtype=[CREFUND] tSQL=[ SELECT TOP 20 x.transid,dtime, updvtype as vtype FROM (']+dbclocation+upmastertbl+[') x WHERE processdtime-x.modified>10 and x.sql=.t. and x.transid ] tSQL=tSQL+[ in (SELECT DISTINCT y.transid FROM (']+dbclocation+updchildtbl+[') y where y.sql=.t. ) ] tSQL=tSQL+[ ORDER BY x.dtime into cursor sqlother ] &tsql if reccount([sqlother])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif tsql2=[SELECT DISTINCT y.transid,datetime() as dtime, updvtype as vtype FROM (']+dbclocation+updchildtbl+[') y where y.sql=.t. into cursor sqlother2 ] &tsql2 if reccount([sqlother2])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd ENDIF tsql2=[SELECT distinct x.transid, dtime, updvtype as vtype FROM (']+dbclocation+upmastertbl+[') x where processdtime-x.modified>100 and x.sql=.t. into cursor sqlother2 ] &tsql2 if reccount([sqlother2])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif use in select(upmastertbl) use in select(updchildtbl) ENDIF if reccount([sqlupdater])<20 upmastertbl=[rinvoice] updchildtbl=[rinvdata] updvtype=[CREDITMEMO] tSQL=[ SELECT TOP 20 x.transid,dtime, updvtype as vtype FROM (']+dbclocation+upmastertbl+[') x WHERE processdtime-x.modified>10 and x.sql=.t. and x.transid ] tSQL=tSQL+[ in (SELECT DISTINCT y.transid FROM (']+dbclocation+updchildtbl+[') y where y.sql=.t. ) ] tSQL=tSQL+[ ORDER BY x.dtime into cursor sqlother ] &tsql if reccount([sqlother])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif tsql2=[SELECT distinct y.transid, datetime() as dtime, updvtype as vtype FROM (']+dbclocation+updchildtbl+[') y where y.sql=.t. into cursor sqlother2 ] &tsql2 if reccount([sqlother2])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd ENDIF tsql2=[SELECT distinct x.transid, dtime, updvtype as vtype FROM (']+dbclocation+upmastertbl+[') x where processdtime-x.modified>100 and x.sql=.t. into cursor sqlother2 ] &tsql2 if reccount([sqlother2])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif use in select(upmastertbl) use in select(updchildtbl) ENDIF if reccount([sqlupdater])<20 upmastertbl=[journal] updchildtbl=[jdata] updvtype=[Journal] tSQL=[ SELECT TOP 20 x.transid,dtime, updvtype as vtype FROM (']+dbclocation+upmastertbl+[') x WHERE processdtime-x.modified>10 and x.sql=.t. and x.transid ] tSQL=tSQL+[ in (SELECT DISTINCT y.transid FROM (']+dbclocation+updchildtbl+[') y where y.sql=.t. ) ] tSQL=tSQL+[ ORDER BY x.dtime into cursor sqlother ] &tsql if reccount([sqlother])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif if file (dbclocation+updchildtbl+'.dbf') tsql2=[SELECT distinct y.transid, datetime() as dtime, updvtype as vtype FROM (']+dbclocation+updchildtbl+[') y where y.sql=.t. into cursor sqlother2 ] &tsql2 if reccount([sqlother2])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif ENDIF tsql2=[SELECT distinct x.transid, dtime, updvtype as vtype FROM (']+dbclocation+upmastertbl+[') x where processdtime-x.modified>100 and x.sql=.t. into cursor sqlother2 ] &tsql2 if reccount([sqlother2])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif use in select(upmastertbl) use in select(updchildtbl) ENDIF if reccount([sqlupdater])<20 tSQL=[ SELECT TOP 20 x.transid,dtime, 'Transfer' as vtype FROM (']+dbclocation+[transfer') x WHERE processdtime-x.modified>10 and x.sql ] tSQL=tSQL+[ ORDER BY x.dtime into cursor sqlother ] &tsql if reccount([sqlother])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif use in select('transfer') ENDIF if reccount([sqlupdater])<20 tSQL=[ SELECT TOP 20 x.transid,dtime, 'Session' as vtype FROM (']+dbclocation+[salessession') x WHERE processdtime-x.modified>10 and x.sql ] tSQL=tSQL+[ ORDER BY x.dtime into cursor sqlother ] &tsql if reccount([sqlother])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif use in select('salessession') ENDIF if reccount([sqlupdater])<20 tSQL=[ SELECT TOP 20 x.transid,dtime, 'Receipt' as vtype FROM (']+dbclocation+[payment') x WHERE processdtime-x.modified>10 and x.sql ] tSQL=tSQL+[ ORDER BY x.dtime into cursor sqlother ] &tsql if reccount([sqlother])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif use in select('payment') ENDIF if reccount([sqlupdater])<20 tSQL=[ SELECT TOP 20 x.transid,dtime, 'Payment' as vtype FROM (']+dbclocation+[Billpay') x WHERE processdtime-x.modified>10 and x.sql ] tSQL=tSQL+[ ORDER BY x.dtime into cursor sqlother ] &tsql if reccount([sqlother])>0 SQLotherTable=DBF() select [sqlupdater] addsqlcmd=[append from (']+SQLotherTable+[')] &addsqlcmd endif use in select('billpay') ENDIF *copy to (addbs(oprop.appstartpath)+[temp\sqlrecs]) recordsfoundforupdate=reccount('sqlupdater') select [sqlupdater] delete all for empty(transid) go top if recordsfoundforupdate>0 and not eof() index on dtime tag dtime go top DO WHILE NOT EOF() AND abs(DATETIME()-processdtime)<4 thevtype=vtype thetrnum=transid theDtime=ttoc(dtime) theresponse=updatetransactions(thevtype,thetrnum) *theresponse=transtype+[ ]+mTransnum+[ ] theresponse=CHRTRAN(CHRTRAN(CHRTRAN(chrtran(allt(theresponse),chr(13)+chr(10),''),[ ],[ ]),[ ],[ ]),[ ],[ ]) Do Case Case Upper(theresponse)=Upper("Sales") Or Upper(theresponse)='CASHSALE' &&CashSale tsql=[update (']+dbclocation+[CashSale') set sql=.f. where allt(transid)==']+allt(thetrnum)+['] &tsql Case Upper(theresponse)=Upper("Invoice") tsql=[update (']+dbclocation+[Invoice') set sql=.f. where allt(transid)==']+allt(thetrnum)+['] &tsql Case Upper(theresponse)=Upper("Bill") &&Bill tsql=[update (']+dbclocation+[Bill') set sql=.f. where allt(transid)==']+allt(thetrnum)+['] &tsql Case Upper(theresponse)=Upper("CashBill") &&Cashbill tsql=[update (']+dbclocation+[CashBill') set sql=.f. where allt(transid)==']+allt(thetrnum)+['] &tsql Case Upper(theresponse)=Upper("CreditBill") tsql=[update (']+dbclocation+[rBill') set sql=.f. where allt(transid)==']+allt(thetrnum)+['] &tsql Case Upper(theresponse)=Upper("Adjustment") tsql=[update (']+dbclocation+[adjust') set sql=.f. where allt(transid)==']+allt(thetrnum)+['] &tsql Case Upper(theresponse)=Upper("STORETRF") tsql=[update (']+dbclocation+[storetrf') set sql=.f. where allt(transid)==']+allt(thetrnum)+['] &tsql Case Upper(theresponse)=Upper("RETURNS") Or theresponse='CREFUND' &&CREFUND tsql=[update (']+dbclocation+[rcinvoice') set sql=.f. where allt(transid)==']+allt(thetrnum)+['] &tsql Case Upper(theresponse)=Upper("CREDITMEMO") tsql=[update (']+dbclocation+[rinvoice') set sql=.f. where allt(transid)==']+allt(thetrnum)+['] &tsql Case Upper(theresponse)=Upper("Journal") tsql=[update (']+dbclocation+[journal') set sql=.f. where allt(transid)==']+allt(thetrnum)+['] &tsql Case Upper(theresponse)=Upper("Transfer") tsql=[update (']+dbclocation+[transfer') set sql=.f. where allt(transid)==']+allt(thetrnum)+['] &tsql Case Upper(theresponse)=Upper("Session") tsql=[update (']+dbclocation+[salessession') set sql=.f. where allt(transid)==']+allt(thetrnum)+['] &tsql Case Upper(theresponse)=Upper("Receipt") tsql=[update (']+dbclocation+[payment') set sql=.f. where allt(transid)==']+allt(thetrnum)+['] &tsql Case Upper(theresponse)=Upper("Payment") tsql=[update (']+dbclocation+[billpay') set sql=.f. where allt(transid)==']+allt(thetrnum)+['] &tsql Otherwise theresponse=[Error ]+[ ]+allt(TRANSFORM(thetrnum))+[ ]+allt(thevtype)+[ ]+theresponse ENDCASE totalresponse=totalresponse+theresponse+[ ] SELECT [sqlupdater] skip enddo * erase (sqlinprogressfile) ELSE totalresponse=[Transaction updates finished!] * erase (sqlinprogressfile) endif USE IN SELECT('sqlother') use in select('sqlupdater') endif close tables IF TYPE([orequest])<>[U] oresponse.write(totalresponse) oresponse.flush ELSE inspect(totalresponse) RETURN totalresponse endif %>