<%
crlf=chr(13)+chr(10)
msg=[starting ]+crlf
msg=msg+TRANSFORM(LINENO()-4)+crlf
*STRTOFILE(msg,ADDBS(oprop.appstartpath)+[temp\actions.txt])
set date ymd
set century on
set mark to [/]
my_ratification_date=date()
my_ratification_number=[]
iserror=.F.
msg=[starting ]+crlf
msg=msg+TRANSFORM(LINENO()-4)+crlf
*strtOFILE(msg,ADDBS(oprop.appstartpath)+[temp\actions.txt])
=usesafe([category])
=Sys(3101,1256)
*=SYS(3101,65001)
file_reference=orequest.Form('file_reference')
*strtOFILE(msg,ADDBS(oprop.appstartpath)+[temp\actions.txt])
M_VISANUMBER=orequest.Form('visa_issue_number')
M_VISADATE=orequest.Form('visa_date')
M_SPONNAME=orequest.Form('sponsor_full_name')
M_SPONADD=orequest.Form('sponsor_address')
if [  ] $ M_SPONADD
	do while [  ] $ M_SPONADD
		M_SPONADD=strtran(M_SPONADD,[  ],[ ])
	enddo
endif
M_SPONID=orequest.Form('sponsor_id_number')
m_embassy=orequest.Form('embassy_location')
delegation_list=orequest.Form('delegation_list')
deletation_in_embassy=orequest.Form('delegation_of_embassies')
jobs_list=orequest.form('jobs_list')
errmsg=transform(LINENO())
msg=msg+TRANSFORM(LINENO()-4)+crlf
*strtoFILE(msg,ADDBS(oprop.appstartpath)+[temp\actions.txt])
iserror=.f.
If Isnull(M_VISANUMBER)
	Return '{"status":"error","message":" first '+file_reference+'"}'
Endif
errmsg=transform(LINENO())
msg=msg+TRANSFORM(LINENO()-4)+crlf
tfilename=dbclocation+[works\visainfo.dbf]
errmsg=transform(LINENO())
if File(tfilename)
	try 
		Use (tfilename) In Select('visainfo')
		if type([visainfo.coder])="U"
			use in select('visainfo')
			alter table (tfilename) add column coder c(15)
		endif
		if type([visainfo.ratdate])<>"D"
			use in select('visainfo')
			alter table (tfilename) alter column ratdate d(8)
		endif
		if type([visainfo.CHOOSETEXT])<>"C"
			use in select('visainfo')
			alter table (tfilename) alter column CHOOSETEXT c(10)
		endif
	CATCH
		try
			erase (tfilename)
		Catch
			errmsg=[File visainfo.dbf could not be deleted. Line ]+transform(lineno())
			iserror=.t.
		endtry
	ENDTRY
endif
If iserror
	Return '{"status":"error","message":"'+errmsg+'"}'
Endif
If Not File(tfilename)
	tcmd=[Create Table (']+tfilename+[') Free (entdate d(8), listType c(20),Code C(15),CodeR C(15),ratdate d(8), SR N(4,0), QTY N(4,0), PROF C(40),embassy C(4),]
	tcmd=tcmd+[ratified C(80), Comp C(80), Year N(2,0),Month N(2,0), SALARY N(8,0),CATEGORY C(40), SKILL N(1,0), REFFID C(9),]
	tcmd=tcmd+[SPONID C(15), SPONNAME C(80), SPONADR C(80), VISAID C(15), VISADT C(10),Curr C(5), CHOOSE L(1),CHOOSETEXT c(10),CID N(8),PID N(8),ownlic C(1))]
	try
		&tcmd
	Catch
		errmsg=[File visainfo.dbf could not be created. Line ]+transform(lineno())
		iserror=.t.
	endtry
ENDIF
If iserror
	Return '{"status":"error","message":"'+errmsg+'"}'
Endif
tcmd=[Use (']+tfilename+[') In Select('visainfo')]
&tcmd
select 'visainfo'
delete all for allt(reffid)==file_reference or entdate<date()-2
try
	tcmd=[Use (']+tfilename+[') In Select('visainfo') excl ]
	&tcmd
	select 'visainfo'
	pack
Catch
	*need no action.
ENDTRY

tcmd=[Use (']+tfilename+[') In Select('visainfo') ]
&tcmd
select 'visainfo'
rmsg=[File data updated ]+file_reference
Strtofile(delegation_list,Addbs(oprop.appstartpath)+'temp\delegation_list.json')
Strtofile(deletation_in_embassy,Addbs(oprop.appstartpath)+'temp\deletation_in_embassy.json')
Strtofile(jobs_list,Addbs(oprop.appstartpath)+'temp\jobs_list.json')
sometext=[]

errmsg=transform(LINENO())
msg=msg+TRANSFORM(LINENO()-4)+crlf
*strtoFILE(msg,ADDBS(oprop.appstartpath)+[temp\actions.txt])

select 'visainfo'
*Update deligation list
chekcntr=1
Do While .T.
	newdeligation=Strextract(delegation_list,[delegation_number":"],[",],chekcntr)
	If Empty(val(newdeligation))
		Exit
	Endif
	theagencyname=Strextract(delegation_list,[delegated_agency_name":"],[",],chekcntr)
	theambassy=Strextract(delegation_list,[visa_issuing_authority":"],[",],chekcntr)
	theoccupation=Strextract(delegation_list,[occupation":"],[",],chekcntr)
	thepersons_count=Strextract(delegation_list,[persons_count":"],[",],chekcntr)
	theratified_by=Strextract(delegation_list,[ratified_by":"],[",],chekcntr)
	if empty(theratified_by)
		chekcntr=chekcntr+1
		loop
	endif
	theratifieddate=Strextract(delegation_list,[ratification_date":"],["}],chekcntr)
	my_ratification_date=ctod(theratifieddate)
	my_ratification_number=newdeligation
	Locate For Alltrim(Code)==Alltrim(newdeligation) and listType='delegation_list' and allt(reffid)==file_reference
	If Eof()
		Append Blank
	Endif
	Replace REFFID With file_reference,;
		code with newdeligation,;
		coder with newdeligation,;
		SPONNAME With M_SPONNAME,;
		SPONID With M_SPONID,;
		SPONADR With M_SPONADD,;
		VISAID With M_VISANUMBER,;
		VISADT With M_VISADATE,;
		QTY With Val(Transform(thepersons_count)),;
		PROF With theoccupation,;
		comp With theagencyname,;
		ratified With theratified_by,;
		ratdate With my_ratification_date,;
		embassy With m_embassy,;
		listType with 'delegation_list',;
		entdate with date()
		chekcntr=chekcntr+1
Enddo
*Now we are going to Add records from delegations_in_emabassys
chekcntr=1
Do While .T.
	newdeligation=Strextract(deletation_in_embassy,[delegation_number":"],[",],chekcntr)
	If Empty(val(newdeligation))
		Exit
	Endif
	newdeligationhash=Strextract(deletation_in_embassy,[delegation_number_hash":"],[",],chekcntr)
	theambassy=Strextract(deletation_in_embassy,[visa_issuing_authority":"],[",],chekcntr)
	theoccupation=Strextract(deletation_in_embassy,[occupation":"],[",],chekcntr)
	thepersons_count=Strextract(deletation_in_embassy,[persons_count":"],[",],chekcntr)
	theagencyname=Strextract(deletation_in_embassy,[delegated_agency_name":"],["}],chekcntr)
	Locate For (Alltrim(CodeR)==Alltrim(newdeligation) or Alltrim(Code)==Alltrim(newdeligationhash)) and listType='delegation_list' and allt(reffid)==file_reference
	if not eof()
		theratdate=ratdate
		Else
		theratdate={//}
	endif
	Locate For Alltrim(CodeR)==Alltrim(newdeligation) and listType='delinembassy' and allt(reffid)==file_reference
	If Eof()
		Append Blank
	Endif
	Replace REFFID With  file_reference,;
		code with newdeligationhash,;
		coder with newdeligation,;
		ratdate with theratdate,;
		SPONNAME With M_SPONNAME,;
		SPONID With M_SPONID,;
		SPONADR With M_SPONADD,;
		VISAID With M_VISANUMBER,;
		VISADT With M_VISADATE,;
		QTY With Val(Transform(thepersons_count)),;
		PROF With theoccupation,;
		comp With theagencyname,;
		embassy With m_embassy,;
		listType with 'delinembassy',;
		entdate with date()
		chekcntr=chekcntr+1
Enddo
msg=msg+TRANSFORM(LINENO()-4)+crlf
*strtoFILE(msg,ADDBS(oprop.appstartpath)+[temp\actions.txt])
*Update jobs_list
chekcntr=1
Do While .T.
	theoccupation=Strextract(jobs_list,[occupation":"],[",],chekcntr)
	if empty(theoccupation)
		theoccupation=Strextract(jobs_list,[occupation": "],[",],chekcntr)
	endif
	If Empty(theoccupation)
		Exit
	Endif
	theagencyname=[JOBS LIST No AGENCY]
	theambassy=Strextract(jobs_list,[visa_issuing_authority":"],[",],chekcntr)
	if empty(theambassy)
		theambassy=Strextract(jobs_list,[visa_issuing_authority": "],[",],chekcntr)
	endif
	thepersons_count=Strextract(jobs_list,[persons_count":"],[",],chekcntr)
	if empty(thepersons_count)
		thepersons_count=Strextract(jobs_list,[persons_count": "],[",],chekcntr)
	endif
	theratified_by=''
	theratifieddate=''
	Locate For listType='jobs_list' and allt(reffid)==file_reference and allt(prof)==allt(theoccupation)
	If Eof()
		Append Blank
	Endif
	Replace REFFID With  file_reference,;
		SPONNAME With M_SPONNAME,;
		SPONID With M_SPONID,;
		SPONADR With M_SPONADD,;
		VISAID With M_VISANUMBER,;
		VISADT With M_VISADATE,;
		QTY With Val(Transform(thepersons_count)),;
		PROF With theoccupation,;
		comp With theagencyname,;
		embassy With m_embassy,;
		listType with 'jobs_list',;
		entdate with date()
		chekcntr=chekcntr+1
Enddo
errmsg=transform(LINENO())
msg=msg+TRANSFORM(LINENO()-4)+crlf
*strtoFILE(msg,ADDBS(oprop.appstartpath)+[temp\actions.txt])

select [category]
msg=msg+TRANSFORM(LINENO()-4)+crlf
*strtoFILE(msg,ADDBS(oprop.appstartpath)+[temp\actions.txt])
Set Order To PROF In Select('category') desc 
Select 'visainfo'
locate for allt(reffid)==file_reference
TCOUNTER=0
LICENSESEARCH=Alltrim(Transform(XCOMPANY.LICVAL))
CNTR=0
notupdated=.t.
Do While Not Eof()
	TCOUNTER=TCOUNTER+1
	THEPROF=PROF
	If Seek(THEPROF,'category','Prof')
		CNTR=CNTR+1
		Replace CATEGORY With CATEGORY.CATEGORY, SKILL With CATEGORY.SKILL,;
			year With CATEGORY.Year,;
			month With CATEGORY.Month,;
			SALARY With CATEGORY.SALARY,;
			curr With CATEGORY.Curr,;
			SR With CNTR
		If Empty(Curr)
			Replace Curr With [SAR]
		Endif
	Endif
	If LICENSESEARCH $ Comp
		Replace CHOOSE With .T., ownlic With 'A'
		if not empty(code) and not empty(ratdate) and notupdated
			try
				powerofattorney=allt(code)
				powerofattorneydt=ratdate
				tfile=dbclocation+'fileref'
				update (tfile) set poa_no=powerofattorney, poa_dt=powerofattorneydt where reffid==file_reference and empty(poa_no)
				rmsg=rmsg+' POA NUMBER updated'
			catch
				rmsg=rmsg+' *POA ERR*'
			endtry
			notupdated=.f.
		endif
	Else
		Replace ownlic With 'B'
	Endif
	Skip
Enddo
msg=msg+TRANSFORM(LINENO()-4)+crlf+[end]
*strtoFILE(msg,ADDBS(oprop.appstartpath)+[temp\actions.txt])
If iserror
	Return '{"status":"error","message":"'+transform(errmsg)+'"}'
Else
	Return '{"status":"success","message":"'+rmsg+'"}'
Endif
%>