<% *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\search_select2.txt]) tsql=[] DO case **** CASE searchField='txtsaleman' TSQL=[] IF EMPTY(searchTerm) TSQL=[SELECT distinct TOP 400 accountid as id, name as text FROM (']+dbclocation+[accounts.dbf') WHERE NOT EMPTY(name) and left(accountid,2)='14' ORDER BY text INTO CURSOR results] ELSE TSQL=[SELECT accountid as id, name as text FROM (']+dbclocation+[accounts.dbf') WHERE upper(']+searchTerm+[') $ UPPER(accountid+name) and left(accountid,2)='14' ORDER BY text INTO CURSOR results] ENDIF &TSQL TXTFORFILE=[LINE: ]+transform(lineno()-4)+[new: searchField ]+searchField+[ searchitem ]+searchTERM+CHR(13)+TSQL STRTOFILE(TXTFORFILE,addbs(oprop.appstartpath)+[temp\txtAFTERfirst.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) TSQL=[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 TSQL=[SELECT accountid as id, name as text FROM (']+dbclocation+[accounts.dbf') WHERE searchTerm $ UPPER(accountid+name) ORDER BY name INTO CURSOR results] ENDIF &TSQL 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='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 *TXTFORFILE=[LINE: ]+transform(lineno())+[ founditem ]+founditem+[new: searchField ]+searchField+[ searchTerm ]+searchTerm+[ NEWSEARCHTERM ]+NEWSEARCHTERM +CHR(13)+TSQL *STRTOFILE(TXTFORFILE,addbs(oprop.appstartpath)+[temp\txtAFTER.txt]) &tsql CASE (searchfield='itemsearch' or searchfield='med') and empty(searchterm) TSQL=[SELECT TOP 400 itemid as id, name as text FROM (']+dbclocation+[itemmast.dbf') WHERE NOT EMPTY(name) ORDER BY text INTO CURSOR results] &tsql CASE searchfield='itemsearch' or searchfield='med' **** tsql=[] wefoundrecord=.f. searchitem=ALLTRIM(shdecode(PADR(ALLTRIM(searchTerm),16))) isascociated=0 USESAFE([itemmast]) USESAFE([asctable]) usesafe([itemsold]) usesafe([asctblold]) *added for parseBars ParsenumWT=PADR(SUBSTR(searchitem,xcompany.pbstart,xcompany.pblength),LEN(itemmast.itemid),' ') ParsenumSL=PADR(SUBSTR(searchitem,xcompany.pbstartSL,xcompany.pblengthSL),LEN(itemmast.itemid),' ') ParsenumQT=PADR(SUBSTR(searchitem,xcompany.pbstartQT,xcompany.pblengthQT),LEN(itemmast.itemid),' ') * searchitem=STRTRAN(ALLTRIM(searchitem),[%26],[&]) thisformfinditem=padr(searchitem,16) tresult='{"results": [' if empty(thisformfinditem) tresult=tresult+'{"id":"0","text":"Please type ID, NAME OR ASCOSIATION"}' tresult=tresult+']}' oResponse.ContentType = "text/json" *STRTOFILE([LINE: ]+transform(lineno())+[ sql ]+tsql+[ Result ]+tresult,addbs(oprop.appstartpath)+[temp\search_result.txt]) *oJSON.keyforcursors="results" oResponse.Write(tresult) oresponse.flush return '' endif ITISNUM=ISNUMERIC(Thisformfinditem) vfinditem=VAL(transform(Thisformfinditem)) wefoundrecord=.t. myerrorstring=[searchitem :]+searchitem+chr(13) TXTFORFILE=[LINE: ]+transform(lineno()-4)+[ founditem ]+transform(wefoundrecord)+[new: searchField ]+searchField+[ searchitem ]+searchitem+CHR(13)+TSQL *STRTOFILE(TXTFORFILE,addbs(oprop.appstartpath)+[temp\txtAFTERfirst.txt]) IF ITISNUM itisanumber=.t. vfinditem=VAL(Thisformfinditem) ELSE itisanumber=.f. endif DO case CASE indexSeek(thisformfinditem,.t.,"Itemmast","Itemid") myerrorstring=myerrorstring+[case 1]+chr(13) TXTFORFILE=[LINE: ]+transform(lineno()-4)+[ founditem ]+transform(wefoundrecord)+[new: searchField ]+searchField+[ searchitem ]+searchitem+CHR(13)+TSQL *STRTOFILE(TXTFORFILE,addbs(oprop.appstartpath)+[temp\txtAFTER.txt]) CASE indexSeek(thisformfinditem,.t.,"asctable","bar_code") and indexSeek(asctable.itemid,.t.,"Itemmast","Itemid") myerrorstring=myerrorstring+[case 2]+chr(13) isascociated=1 TXTFORFILE=[LINE: ]+transform(lineno()-4)+[ founditem ]+transform(wefoundrecord)+[new: searchField ]+searchField+[ searchitem ]+searchitem+CHR(13)+TSQL *STRTOFILE(TXTFORFILE,addbs(oprop.appstartpath)+[temp\txtAFTERfirst.txt]) CASE indexSeek(thisformfinditem,.t.,"asctable","bar_code") myerrorstring=myerrorstring+[case 3]+chr(13) select [asctable] delete skip wefoundrecord=.f. CASE indexSeek(thisformfinditem,.t.,"Itemmast","bar_code") myerrorstring=myerrorstring+[case 4]+chr(13) isascociated=2 TXTFORFILE=[LINE: ]+transform(lineno()-4)+[ founditem ]+transform(wefoundrecord)+[new: searchField ]+searchField+[ searchitem ]+searchitem+CHR(13)+TSQL *STRTOFILE(TXTFORFILE,addbs(oprop.appstartpath)+[temp\txtAFTERfirst.txt]) *new added CASE not empty(ParsenumWT) and indexSeek(ParsenumWT,.t.,"Itemmast","itemid") and itemmast.parseBar and (empty(itemmast.parsetp) or itemmast.parsetp=[WT]) myerrorstring=myerrorstring+[case 4]+chr(13) Thisformfinditem=itemmast.itemid TXTFORFILE=[LINE: ]+transform(lineno()-4)+[ founditem ]+transform(wefoundrecord)+[new: searchField ]+searchField+[ searchitem ]+searchitem+CHR(13)+TSQL *STRTOFILE(TXTFORFILE,addbs(oprop.appstartpath)+[temp\txtAFTERfirst.txt]) CASE not empty(ParsenumSL) and indexSeek(ParsenumSL,.t.,"Itemmast","itemid") and itemmast.parseBar and itemmast.parsetp=[SL] myerrorstring=myerrorstring+[case 4]+chr(13) Thisformfinditem=itemmast.itemid TXTFORFILE=[LINE: ]+transform(lineno()-4)+[ founditem ]+transform(wefoundrecord)+[new: searchField ]+searchField+[ searchitem ]+searchitem+CHR(13)+TSQL *STRTOFILE(TXTFORFILE,addbs(oprop.appstartpath)+[temp\txtAFTERfirst.txt]) CASE not empty(ParsenumQT) and indexSeek(ParsenumQT,.t.,"Itemmast","itemid") and itemmast.parseBar and itemmast.parsetp=[QT] myerrorstring=myerrorstring+[case 4]+chr(13) Thisformfinditem=itemmast.itemid TXTFORFILE=[LINE: ]+transform(lineno()-4)+[ founditem ]+transform(wefoundrecord)+[new: searchField ]+searchField+[ searchitem ]+searchitem+CHR(13)+TSQL *STRTOFILE(TXTFORFILE,addbs(oprop.appstartpath)+[temp\txtAFTERfirst.txt]) *new added end Case itisanumber AND Seek(vfinditem,"Itemmast","valItemid") myerrorstring=myerrorstring+[case 5]+chr(13) TXTFORFILE=[LINE: ]+transform(lineno()-4)+[ founditem ]+transform(wefoundrecord)+[new: searchField ]+searchField+[ searchitem ]+searchitem+CHR(13)+TSQL *STRTOFILE(TXTFORFILE,addbs(oprop.appstartpath)+[temp\txtAFTERfirst.txt]) case itisanumber AND Seek(vfinditem,"Itemmast","valbarcode") myerrorstring=myerrorstring+[case 6]+chr(13) TXTFORFILE=[LINE: ]+transform(lineno()-4)+[ founditem ]+transform(wefoundrecord)+[new: searchField ]+searchField+[ searchitem ]+searchitem+CHR(13)+TSQL *STRTOFILE(TXTFORFILE,addbs(oprop.appstartpath)+[temp\txtAFTERfirst.txt]) case quadcode(ALLTRIM(Thisformfinditem)) myerrorstring=myerrorstring+[case 7]+chr(13) TXTFORFILE=[LINE: ]+transform(lineno()-4)+[ founditem ]+transform(wefoundrecord)+[new: searchField ]+searchField+[ searchitem ]+searchitem+CHR(13)+TSQL *STRTOFILE(TXTFORFILE,addbs(oprop.appstartpath)+[temp\txtAFTERfirst.txt]) OTHERWISE wefoundrecord=.f. myerrorstring=myerrorstring+[other]+chr(13) TXTFORFILE=[LINE: ]+transform(lineno()-4)+[ founditem ]+transform(wefoundrecord)+[new: searchField ]+searchField+[ searchitem ]+searchitem+CHR(13)+TSQL *STRTOFILE(TXTFORFILE,addbs(oprop.appstartpath)+[temp\txtAFTERfirst.txt]) endcase **** *This portion will draw the record from itemsold and asctblold, if not in itemmast *If got in itemsold, we will just add item to itemmast *however, this will work only with itemid or short code. Other search technics will fail. IF NOT wefoundrecord DO case CASE indexSeek(thisformfinditem,.t.,"Itemsold","Itemid") OR SEEK(thisformfinditem,"Itemsold","Bar_code") SELECT [itemsold] SCATTER NAME itemoldvar memo SELECT [itemmast] APPEND BLANK GATHER NAME itemoldvar memo wefoundrecord=.t. TXTFORFILE=[LINE: ]+transform(lineno()-4)+[ founditem ]+transform(wefoundrecord)+[new: searchField ]+searchField+[ searchitem ]+searchitem+CHR(13)+TSQL *STRTOFILE(TXTFORFILE,addbs(oprop.appstartpath)+[temp\txtAFTERfirst.txt]) CASE indexSeek(thisformfinditem,.t.,"asctblold","bar_code") and indexSeek(asctblold.itemid,.t.,"Itemsold","Itemid") SELECT [itemsold] SCATTER NAME itemoldvar memo SELECT [itemmast] APPEND BLANK GATHER NAME itemoldvar memo SELECT [asctblold] SCATTER NAME ascold SELECT [asctable] APPEND BLANK GATHER NAME ascold wefoundrecord=.t. isascociated=2 TXTFORFILE=[LINE: ]+transform(lineno()-4)+[ founditem ]+transform(wefoundrecord)+[new: searchField ]+searchField+[ searchitem ]+searchitem+CHR(13)+TSQL *STRTOFILE(TXTFORFILE,addbs(oprop.appstartpath)+[temp\txtAFTERfirst.txt]) endcase ENDIF *the above is enough for getting data from old items **** If wefoundrecord if empty(isascociated) asoctext=[] else asoctext=[ ]+allt(searchTerm) endif NAMETOPUTIN=ITEMMAST.NAME searchitem=itemmast.ItemID thetablename=dbclocation+[itemmast] tsql=[SELECT itemid as 'id', allt(itemid)+' '+allt(name)+asoctext as 'text' FROM (']+thetablename+[') where allt(itemid)==']+allt(searchitem)+[' INTO CURSOR results] &&readwrite *myerrorstring=myerrorstring+tsql+chr(13) &tsql myerrorstring=myerrorstring+[case 1]+chr(13) TXTFORFILE=[LINE: ]+transform(lineno()-4)+[ founditem ]+transform(wefoundrecord)+[new: searchField ]+searchField+[ searchitem ]+searchitem+CHR(13)+TSQL *STRTOFILE(TXTFORFILE,addbs(oprop.appstartpath)+[temp\txtAFTER.txt]) IF _tally>0 *CURSORTOXML('results','theresponse',1,0,0,'1') ELSE theresponse=[No Records] ENDIF *USE IN SELECT([results]) wefoundrecord=.t. endif tfindit=UPPER(ALLTRIM(searchTerm)) if not wefoundrecord MAX_RESULTS=300 tsql=[] If Not tfindit=="*" And Not tfindit=='+' tfindfirst='' tfindlast='' If '+' $ tfindit Or '@' $ tfindit If '@' $ tfindit tfindfirst=Alltrim(Substr(tfindit,1,At('@',tfindit)-1)) tfindlast=Val(Alltrim(Substr(tfindit,At('@',tfindit)+1))) Do Case Case Not Empty(tfindfirst) And Not Empty(tfindlast) wheretext=[where ]+Iif(xcompany.noshow0,'x.stock>0 and ','')+[ (']+tfindfirst+[' $ x.ItemID+x.Name+x.bar_code+x.Name And ]+TRANSFORM(tfindlast)+[ = x.Sale) ] tsql=[Select top MAX_RESULTS x.ItemID as 'id', x.bar_code, allt(itemid)+' '+ALLTRIM(x.Name) as 'text', x.sale, x.stock,alltrim(x.almara) as details, ctnqty, ctnsale, whqty, whsale ; From "]+(dbclocation+[itemmast])+[" x ]+wheretext+[ ; into cursor results Order By text] Case Empty(tfindfirst) And Not Empty(tfindlast) wheretext=[where ]+Iif(xcompany.noshow0,'x.stock>0 and ','')+TRANSFORM(tfindlast)+[ = x.Sale ] tsql=[Select top MAX_RESULTS x.ItemID as 'id', x.bar_code, allt(itemid)+' '+ALLTRIM(x.Name) as 'text', x.sale, x.stock, alltrim(x.almara) as details, ctnqty, ctnsale, whqty, whsale; From "]+(dbclocation+[itemmast])+[" x ]+wheretext+[ ; into cursor results Order By text] Case Not Empty(tfindfirst) And Empty(tfindlast) wheretext=[where ]+Iif(xcompany.noshow0,'x.stock>0 and ','')+[']+ tfindfirst+[' $ x.ItemID+x.Name+x.bar_code+x.Name ] tsql=[Select top MAX_RESULTS x.ItemID as 'id', x.bar_code, allt(itemid)+' '+ALLTRIM(x.Name) as 'text', x.sale, x.stock, alltrim(x.almara) as details, ctnqty, ctnsale, whqty, whsale; From "]+(dbclocation+[itemmast])+[" x ]+wheretext+[ ; into cursor results Order By text] Otherwise *Wait Window 'Un handled situation in cashdesk getting list, inform Sher Shah' Endcase Else tfindfirst=Alltrim(Substr(tfindit,1,At('+',tfindit)-1)) tfindlast=Alltrim(Substr(tfindit,At('+',tfindit)+1)) Do Case Case Not Empty(tfindfirst) And Not Empty(tfindlast) wheretext=[where ]+Iif(xcompany.noshow0,'x.stock>0 and ','')+[ (']+tfindfirst+[' $ x.ItemID+x.Name+x.bar_code+x.Name And ']+tfindlast+[' $ x.ItemID+x.Name+x.bar_code+x.Name) ] tsql=[Select top MAX_RESULTS x.ItemID as 'id', x.bar_code, allt(itemid)+' '+x.Name as 'text', x.sale, x.stock, alltrim(x.almara) as details, ctnqty, ctnsale, whqty, whsale; From "]+(dbclocation+[itemmast])+[" x ]+wheretext+[ ; into cursor results Order By text] Case Empty(tfindfirst) And Not Empty(tfindlast) wheretext=[where ]+Iif(xcompany.noshow0,'x.stock>0 and ','')+[']+tfindlast+[' ]+[ $ x.ItemID+x.Name+x.bar_code+x.Name ] wheretext2=[where ]+Iif(xcompany.noshow0,'a.stock>0 and ','')+[']+tfindlast+[' ]+[ $ a.ItemID+b.Name+a.bar_code+b.Name and a.itemid=b.itemid ] tsql=[Select top MAX_RESULTS x.ItemID as 'id', x.bar_code, allt(itemid)+' '+x.Name as 'text', x.sale, x.stock, alltrim(x.almara) as details, ctnqty, ctnsale, whqty, whsale; From "]+(dbclocation+[itemmast])+[" x ]+wheretext+[ ; into cursor results Order By text] Case Not Empty(tfindfirst) And Empty(tfindlast) wheretext=[where ]+Iif(xcompany.noshow0,'x.stock>0 and ','')+[']+ tfindfirst+[' $ x.ItemID+x.Name+x.bar_code+x.Name ] wheretext2=[where ]+Iif(xcompany.noshow0,'a.stock>0 and ','')+[']+ tfindfirst+[' $ a.ItemID+b.Name+a.bar_code+b.Name and a.itemid=b.itemid ] tsql=[Select top MAX_RESULTS x.ItemID as 'id', x.bar_code, allt(itemid)+' '+x.Name as 'text', x.sale, x.stock, alltrim(x.almara) as details, ctnqty, ctnsale, whqty, whsale; From "]+(dbclocation+[itemmast])+[" x ]+wheretext+[ ; into cursor results Order By text] Otherwise *Wait Window 'Un handled situation in cashdesk getting list, inform Sher Shah' Endcase Endif Else *wheretext='where '+Iif(xcompany.noshow0,'x.stock>0 and ','')+' tfindit $ x.ItemID+x.Name ' Item_to_find_in_cash=[']+tfindit+['] If xcompany.noshow0 tsql=[Select top MAX_RESULTS x.ItemID as 'id', x.bar_code, allt(itemid)+' '+x.Name as 'text', x.sale, ] tsql=tsql+[ x.stock, alltrim(x.almara) as , ctnqty, ctnsale, whqty, whsale ] tsql=tsql+[ FROM "]+(dbclocation+[itemmast])+[" x ] tsql=tsql+[ WHERE ]+Item_to_find_in_cash+[ $ x.ItemID+x.Name And x.stock>0 ] tsql=tsql+[ ORDER By details into cursor results ] Else tsql=[Select top MAX_RESULTS x.ItemID as 'id', x.bar_code, allt(itemid)+' '+x.Name as 'text', x.sale, x.stock, alltrim(x.almara) as details, ctnqty, ctnsale, whqty, whsale ] tsql=tsql+[ FROM "]+(dbclocation+[itemmast])+[" x ] tsql=tsql+[ WHERE ]+Item_to_find_in_cash+[ $ x.ItemID+x.Name ] tsql=tsql+[ ORDER By details into cursor results ] Endif Endif Else If xcompany.noshow0 tsql=[Select top MAX_RESULTS x.ItemID as 'id', x.bar_code, allt(itemid)+' '+x.Name as 'text', x.sale, x.stock, alltrim(x.almara) as details, ctnqty, ctnsale, whqty, whsale ] tsql=tsql+[ From "]+(dbclocation+[itemmast])+[" x Where x.stock>0 ] tsql=tsql+[ into cursor results Order By text] Else tsql=[Select top MAX_RESULTS x.ItemID as 'id', x.bar_code, allt(itemid)+' '+x.Name as 'text', x.sale, x.stock, alltrim(x.almara) as details, ctnqty, ctnsale, whqty, whsale ] tsql=tsql+[ From "]+(dbclocation+[itemmast])+[" x ] tsql=tsql+[ into cursor results Order By text ] Endif ENDIF IF NOT EMPTY(tsql) &tsql DO case CASE _tally=1 tmpitem=id if empty(isascociated) asoctext=[] else asoctext=[ ]+allt(searchTerm) endif thetablename=dbclocation+[itemmast] tsql=[SELECT itemid as 'id', allt(itemid)+' '+allt(name)+asoctext as 'text' FROM (']+thetablename+[') where allt(itemid)==']+allt(tmpitem)+[' INTO CURSOR results] &&readwrite &tsql CASE _tally>1 *CURSORTOXML('gotrecords','theresponse',1,0,0,'1') OTHERWISE theresponse=[No Records] ENDCASE ENDIF endif OTHERWISE oresponse.write('[{"id":"err","text":"'+searchField+'"}]') return '' endcase if reccount('results')>0 oResponse.ContentType = "text/json" oJSON.keyforcursors="results" tresult=ojson.stringify("results") tresult=strtran(tresult,[{"rows":],[]) tresult=strtran(tresult,']}}',']}') else Tresult='{"results":[{"id":"0","text":"NO VALIED ITEM FOUND"}]}' endif STRTOFILE([LINE: ]+transform(lineno())+[ sql ]+tsql+[ Result ]+tresult,addbs(oprop.appstartpath)+[temp\search_result_last.txt]) oResponse.Write(tresult) oresponse.flush CLOSE DATABASES CLOSE TABLES %>