<% *dbclocation=[d:\shahprjs\recruiting\dataorigin\] *oJSON=NEWOBJECT('json','json.prg') *AjaxResponse=createobject('empty') =SYS(3101,1256) searchTerm=UPPER(ALLTRIM(TRANSFORM(NVL(orequest.form('searchTerm'),'')))) searchField=lower(ALLTRIM(TRANSFORM(NVL(orequest.form('id'),'')))) searchTerm=CHRTRAN(searchTerm,[abcdefghijklmnopqrstuvwxyz],[ABCDEFGHIJKLMNOPQRSTUVWXYZ]) STRTOFILE([searchField ]+searchField+[ searchTerm ]+searchTerm,addbs(oprop.appstartpath)+[temp\txt.txt]) DO case CASE searchfield='txtpatientid' IF EMPTY(searchTerm) tsql=[SELECT distinct TOP 300 patientid as id, name as text FROM ("]+dbclocation+[patients.dbf]+[") WHERE NOT EMPTY(name) ] tsql=tsql+[ ORDER BY id desc INTO CURSOR results ] ELSE tsql=[SELECT distinct TOP 300 patientid as id, name as text FROM ("]+dbclocation+[patients.dbf]+[") WHERE ("] tsql=tsql+searchTerm+[") $ UPPER(patientid+name) ORDER BY text INTO CURSOR results ] ENDIF *STRTOFILE('searchTerm: '+searchTerm+':'+chr(13)+tsql,(addbs(oprop.appstartpath)+[temp\error_sql.txt])) &tsql CASE searchfield='itemsearch' if empty(searchTerm) tsql=[SELECT distinct TOP 200 itemid as id, name as text FROM ("]+dbclocation+[itemmast.dbf]+[") WHERE NOT EMPTY(name) ] tsql=tsql+[ ORDER BY text INTO CURSOR results ] else USESAFE([itemmast]) USESAFE([asctable]) usesafe([itemsold]) usesafe([asctblold]) newsearchterm=padr(searchTerm,len(itemmast.itemid),[ ]) founditem=[] if empty(founditem) tsql=[SELECT itemid as id, allt(name) as text FROM ("]+dbclocation+[itemmast.dbf]+[") WHERE (] tsql=tsql+[allt(itemid)=="]+searchTerm+[" INTO CURSOR results ] &tsql if _tally>0 founditem=.t. endif endif if empty(founditem) if seek(newsearchterm,[asctable],[bar_code]) and indexSeek(asctable.itemid,.t.,"Itemmast","Itemid") founditem=itemmast.itemid tsql=[SELECT itemid as id, allt(name) as text FROM ("]+dbclocation+[itemmast.dbf]+[") WHERE ("] tsql=tsql+[itemid="]+founditem+[" INTO CURSOR results ] endif endif if empty(founditem) if seek(newsearchterm,[ITEMSOLD],[itemid]) or seek(newsearchterm,[ITEMSOLD],[bar_code]) select [ITEMSOLD] scatter name olditback memo select [itemmast] append blank gather olditback founditem=itemmast.itemid tsql=[SELECT itemid as id, allt(name) as text FROM ("]+dbclocation+[itemmast.dbf]+[") WHERE ("] tsql=tsql+[itemid="]+founditem+[" INTO CURSOR results ] endif endif if empty(founditem) if seek(newsearchterm,[asctblold],[bar_code]) and indexSeek(asctblold.itemid,.t.,"Itemsold","Itemid") select [ITEMSOLD] scatter name olditback memo select [itemmast] append blank gather olditback founditem=itemmast.itemid tsql=[SELECT itemid as id, allt(name) as text FROM ("]+dbclocation+[itemmast.dbf]+[") WHERE ("] tsql=tsql+[itemid="]+founditem+[" INTO CURSOR results ] endif endif if empty(founditem) tsql=[SELECT distinct top 200 itemid as id, allt(name) as text FROM ("]+dbclocation+[itemmast.dbf]+[") WHERE ("] tsql=tsql+searchTerm+[" $ UPPER(itemid+name+bar_code) or itemid in ( select distinct itemid from ("]+dbclocation+[asctable.dbf]+[") ] tsql=tsql+[ where allt(bar_code)=="]+searchterm+[")) ORDER BY text INTO CURSOR results ] &tsql endif ENDIF *STRTOFILE('searchTerm: '+searchTerm+':'+chr(13)+tsql,(addbs(oprop.appstartpath)+[temp\error_sql.txt])) CASE searchField='txtaccountid' IF EMPTY(searchTerm) SELECT distinct TOP 400 accountid as id, name as text FROM (dbclocation+[accounts.dbf]) WHERE NOT EMPTY(name) ORDER BY name INTO CURSOR results ELSE SELECT itemid as id, name as text FROM (dbclocation+[itemmast.dbf]) WHERE searchTerm $ UPPER(itemid+name) ORDER BY name INTO CURSOR results ENDIF CASE searchField='txtdebitor' IF EMPTY(searchTerm) SELECT distinct TOP 400 accountid as id, name as text FROM (dbclocation+[accounts.dbf]) WHERE NOT EMPTY(name) ORDER BY name INTO CURSOR results ELSE SELECT accountid as id, name as text FROM (dbclocation+[accounts.dbf]) WHERE searchTerm $ UPPER(accountid+name) ORDER BY name INTO CURSOR results ENDIF CASE searchField='txtcustomer' IF EMPTY(searchTerm) SELECT distinct TOP 400 accountid as id, name as text FROM (dbclocation+[accounts.dbf]) WHERE NOT EMPTY(name) ORDER BY name INTO CURSOR results ELSE SELECT accountid as id, name as text FROM (dbclocation+[accounts.dbf]) WHERE searchTerm $ UPPER(accountid+name) ORDER BY name INTO CURSOR results ENDIF CASE searchField='txtvendor' IF EMPTY(searchTerm) SELECT distinct TOP 400 accountid as id, name as text FROM (dbclocation+[accounts.dbf]) WHERE NOT EMPTY(name) ORDER BY name INTO CURSOR results ELSE SELECT accountid as id, name as text FROM (dbclocation+[accounts.dbf]) WHERE searchTerm $ UPPER(accountid+name) ORDER BY name INTO CURSOR results ENDIF CASE searchField='txtdepositid' IF EMPTY(searchTerm) SELECT distinct TOP 400 accountid as id, name as text FROM (dbclocation+[accounts.dbf]) WHERE NOT EMPTY(name) ORDER BY name INTO CURSOR results ELSE SELECT accountid as id, name as text FROM (dbclocation+[accounts.dbf]) WHERE searchTerm $ UPPER(accountid+name) ORDER BY name INTO CURSOR results ENDIF CASE searchField='txtcreditor' IF EMPTY(searchTerm) SELECT distinct TOP 400 accountid as id, name as text FROM (dbclocation+[accounts.dbf]) WHERE NOT EMPTY(name) ORDER BY name INTO CURSOR results ELSE SELECT accountid as id, name as text FROM (dbclocation+[accounts.dbf]) WHERE searchTerm $ UPPER(accountid+name) ORDER BY name INTO CURSOR results ENDIF CASE searchField='txtdepartment' IF EMPTY(searchTerm) SELECT distinct TOP 400 packing as id, packing as text FROM (dbclocation+[itemmast.dbf]) ORDER BY name INTO CURSOR results ELSE SELECT distinct packing as id, packing as text FROM (dbclocation+[itemmast.dbf]) WHERE searchTerm $ UPPER(packing) ORDER BY name INTO CURSOR results ENDIF case searchfield='med' &&and val(subst(searchField,4))>0 IF EMPTY(searchTerm) tsql=[SELECT distinct TOP 200 itemid as id, name as text FROM ("]+dbclocation+[itemmast.dbf]+[") WHERE NOT EMPTY(name) ] tsql=tsql+[ ORDER BY text INTO CURSOR results ] ELSE tsql=[SELECT distinct top 200 itemid as id, allt(name) as text FROM ("]+dbclocation+[itemmast.dbf]+[") WHERE ("] tsql=tsql+searchTerm+[" $ UPPER(itemid+name+bar_code) or itemid in ( select distinct itemid from ("]+dbclocation+[asctable.dbf]+[") ] tsql=tsql+[ where allt(bar_code)=="]+searchterm+[")) ORDER BY text INTO CURSOR results ] ENDIF STRTOFILE('searchTerm: '+searchTerm+':'+chr(13)+tsql,(addbs(oprop.appstartpath)+[temp\error_sql.txt])) &tsql OTHERWISE oresponse.write('[{"id":"err","text":"'+searchField+'"}]') return '' endcase tresult='{"results": [' *IF SELECT('results')>0 SCAN tresult=tresult+'{"id":"'+ALLTRIM(TRANSFORM(id))+'","text":"'+ALLTRIM(TRANSFORM(text))+'"},' ENDSCAN *endif IF EMPTY(searchTerm ) tresult=SUBSTR(tresult,1,LEN(tresult)-1) else LOCATE FOR ALLTRIM(id)==searchTerm IF EOF() tresult=tresult+'{"id":"'+ALLTRIM(TRANSFORM(searchTerm))+'","text":"'+ALLTRIM(TRANSFORM(searchTerm))+'"}' ELSE tresult=SUBSTR(tresult,1,LEN(tresult)-1) endif endif tresult=tresult+']}' *oresponse.write(tresult) CLOSE DATABASES CLOSE TABLES oResponse.ContentType = "text/json" *oJSON.keyforcursors="results" oResponse.Write(tresult) oresponse.flush %>