foxpro utf8

http://www.west-wind.com/presentations/foxunicode/foxunicode.asp

http://www.west-wind.com/presentations/foxunicode/foxunicode.asp#WhatProblem

foxpro.gendao

para p_name
store 'VENDOR' TO P_NAME
STORE 'COMPANY_PROFILE'  TO P_NAME
store 'PAYMENT_TERMS' to P_NAME                                  
store 'PAYMENT_METHOD' to P_NAME                                  
STORE 'CUSTOMER'  TO P_NAME
store 'VENDOR' TO P_NAME
store 'RESOURCE' TO P_NAME
store 'ITEM' TO P_NAME
*STORE 'USER'  TO P_NAME
CLOS DATA
set dele on
use result in 0
use gendao in 0
sele gendao
dele for empty(field1)
SET FILTER TO tableNAME=P_NAME OR NAME=P_NAME
SCAN
if (not empty(name)) and empty(tablename)
replace tablename with name
replace name with ''
endif
if not empty(raw)

replace field1 with strt(strt(raw,'  `',''),'` ',' ')
replace field1 with strt(field1,'DEFAULT NULL','')
replace field1 with strt(field1,'NOT NULL','')
replace field1 with strt(field1,'varchar','C ')
replace field1 with strt(field1,'int(11)','I')
replace field1 with strt(field1,'char','c')
replace field1 with strt(field1,'bigint(20)','L')
endif
ENDSCAN
sele gendao
LOCATE FOR ALLT(upper(aarray(field1,1,' ')))=='RECORDID'
IF NOT FOUND()
INSERT INTO GENDAO (tableNAME,FIELD1,ZTYPE) VALUES (P_NAME,'RECORDID','FIELD')
*BROW FIELD ZTYPE
ENDIF

LOCATE FOR ALLT(upper(aarray(field1,1,' ')))=='DOC_NO'
IF NOT FOUND()
INSERT INTO GENDAO (tableNAME,FIELD1,ZTYPE) VALUES (P_NAME,'DOC_NO','FIELD')
ENDIF

LOCATE FOR ALLT(upper(aarray(field1,1,' ')))=='STATUS'
IF NOT FOUND()
INSERT INTO GENDAO (tableNAME,FIELD1,ZTYPE) VALUES (P_NAME,'STATUS','FIELD')
ENDIF

LOCATE FOR ALLT(upper(aarray(field1,1,' ')))=='TIMESTAMP'
IF NOT FOUND()
INSERT INTO GENDAO (tableNAME,FIELD1,ZTYPE) VALUES (P_NAME,'TIMESTAMP','FIELD')
ENDIF

*alter table PAYMENT_TERMS ADD COLUMN STATUS CHAR DEFAULT 'D'

*create table PAYMENT_TERMS ( TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,`DOC_NO` int(10) unsigned NOT NULL AUTO_INCREMENT ,CODE NVARCHAR(10) DEFAULT '',DUE_DATE_CALCULATION NVARCHAR(32) DEFAULT '',DISCOUNT_DATE_CALCULATION NVARCHAR(32) DEFAULT '',DISCOUNT_PERCENT FLOAT DEFAULT 0,DESCRIPTION NVARCHAR(50) DEFAULT '',CALC_PMT_DISC_ON_CR_MEMOS char DEFAULT '',BASE_DATE_CAPTION NVARCHAR(30) DEFAULT '',COD_PAYMENT char DEFAULT '',COD_CASHIERS_CHECK char DEFAULT '',RECORDID bigint(20) NOT NULL DEFAULT '0',
*PRIMARY KEY (`RECORDID`),
*KEY `DOC_NO` (`DOC_NO`) USING BTREE);

sele gendao
scan
replace field1 with strt(field1,' `',' ')
endscan
*strt(strt(raw,'  `',''),'` ',' ')

replace all ftype with 'BIG' FOR 'INT' $ FIELD1
REPLACE ALL FIELD1 WITH ALLT(FIELD1)
REPLACE ALL FIELD1 WITH STRT(FIELD1,'-','_')
REPLACE ALL FIELD1 WITH STRT(FIELD1,'_>_','_TO_')
REPLACE ALL FIELD1 WITH STRTRAN(FIELD1,space(4),'')
replace ALL fieldname with  STRT(SUBS(FIELD1,AT('TABLE',FIELD1)+5),'(','') , ZTYPE WITH 'TABLE' for 'TABLE' $ FIELD1
replace all fieldname with allt(fieldname)
replace all fieldname with strt(allt(fieldname),chr(9),'')
replace ALL classname  with  proper(fieldname) for 'TABLE' $ FIELD1
replace all fieldname with  LEFT(FIELD1,AT(' ',FIELD1)),ZTYPE WITH 'FIELD'  for (not 'TABLE' $ FIELD1) and (not 'ENGINE' $ FIELD1)
replace all ZTYPE WITH 'ENGINE'  for  'ENGINE' $ FIELD1
*replace all zvariable with  lower(fieldname) for ztype='FIELD'
replace all zvariable with  lower(fieldname)
replace all varName with "seqNum" for zvariable='msg_seq_no'
replace all varName with t2v(zvariable) for (not zvariable='msg_seq_no')
replace ALL classname with upper(left(varName,1))+subs(varName,2)  for ztype='FIELD' or ztype='TABLE'
sele gendao
scan
do case
case upper(aarray(field1,1,' '))='RECORDID'
replace fieldtype with "bigint(20) NOT NULL DEFAULT '0'"
replace javatype with "String"
case upper(aarray(field1,1,' '))='DOC_NO'
replace fieldtype with "int(10) NOT NULL DEFAULT '0'"
replace javatype with "int"

case upper(aarray(field1,1,' '))='STATUS'
replace fieldtype with "CHAR NOT NULL DEFAULT 'D'"
replace javatype with "char"


*alter table PAYMENT_TERMS ADD COLUMN STATUS CHAR DEFAULT 'D'

other
if aarray(field1,2,' ')='C' OR  aarray(field1,2,' ')='S'
replace fieldtype with 'NVARCHAR('+aarray(field1,3,' ')+") DEFAULT ''"
replace  javatype with 'String'
endif
if aarray(field1,2,' ')='D'
replace fieldtype with 'DOUBLE DEFAULT 0'
replace  javatype with 'double'
endif
if aarray(field1,2,' ')='c'
replace fieldtype with "char DEFAULT ''"
replace  javatype with 'char'
endif
if aarray(field1,2,' ')='I'
replace fieldtype with 'INTEGER DEFAULT 0'
replace  javatype with 'int'
endif

if aarray(field1,2,' ')='F'
replace fieldtype with 'FLOAT DEFAULT 0'
replace  javatype with 'float'
endif
if aarray(field1,2,' ')='L'
replace fieldtype with 'LONG'
replace  javatype with 'long'
endif
if upper(aarray(field1,2,' '))='DATE'
replace fieldtype with 'date'
replace javatype with 'Date'
endif
if upper(aarray(field1,2,' '))='C' and allt(upper(aarray(field1,3,' ')))=='1'
replace fieldtype with "char DEFAULT ''"
replace  javatype with 'char'
endif
endcase
Endscan

select result
locate for name=p_name
if not found()
insert into result (name) value (p_name)
endif
store  createtable() to _r
replace in result createtab with _r
store createbean() to _r
replace in result javabean with _r

sele result
brow
sele gendao
set filter to
*brow field field1:30,tablename:30,fieldname:30,classname:30, varname:30,fieldtype:30



function createbean()

select gendao
_delimiter=''
quota='"'
quota2="+"
r0=''
r1=''
r=''
store ''  to mclassname
scan

if ztype='TABLE'
r= r+'import java.util.Date;'+chr(13)

r=r+'public class '+allt(classname)+"  implements java.io.Serializable  {"+chr(13)
store allt(classname) to mclassname
else
r0=r0+" protected " + allt(javatype)+" "+allt(varname)+";"+chr(13)

r1=r1+"public void set"+allt(classname)+"("+allt(javatype)+" newValue){"+allt(varname)+" = newValue;}"+chr(13)
r1=r1+"public "+allt(javatype)+" get"+allt(classname)+"(){return "+allt(varname)+";}"+chr(13)+chr(13)

endif


endscan
r=r+r0+chr(13)
r=r+"public "+mclassname+"(){}"+chr(13)
r=r+r1+chr(13)+"}"
*public employee(){}
return r

*public class employee
*{*
* // select password, email,surname from employee;*
* protected  int theValue;
* protected  String password;
* protected  String email;
*







function createtable()
r= ''
select gendao
_delimiter=''
quota='"'
quota2="+"
scan
if ztype='TABLE'
r=r+quota+"create table "+allt(tablename) +' ( '+quota+quota2+chr(13)
else

do case
case LOWER(ALLT(fieldname))=='timestamp'
s='" '+_delimiter+'TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP "+'+CHR(13)

r=r+allt(s)




case LOWER(ALLT(fieldname))=='doc_no'
s=' `DOC_NO` int(10) unsigned NOT NULL AUTO_INCREMENT "+'+CHR(13)
r=r+'"'+_delimiter+allt(s)
other
s=fieldname
s=strt(s,'))',')')
s=strt(s,'((','(')

s0=fieldtype
s0=strt(s0,'))',')')
s0=strt(s0,'((','(')

r=r +quota+ _delimiter+allt(s)+' '+ALLT(s0)+quota+quota2+chr(13)
endcase


_delimiter=','
endif
endscan
r=r+quota+','+ "PRIMARY KEY (`RECORDID`)"+quota+quota2+chr(13)
r=r+'",KEY `DOC_NO` (`DOC_NO`) USING BTREE"+'+chr(13)
r=r+quota+');'+quota+";"+chr(13)
return r

function t2v()
para p_1
for i=0 to 25
p_1=strt(p_1,'_'+chr(97+i),chr(65+i))
endfor
r=p_1
return r

foxpro-javabean

function createbean()

select gendao
_delimiter=''
quota='"'
quota2="+"
r0=''
r1=''
r=''
store ''  to mclassname
scan
if ztype='TABLE'
r= r+'import java.util.Date;'+chr(13)
r=r+'public class '+allt(classname)+"{"+chr(13)
store allt(classname) to mclassname
else
r0=r0+" protected " + allt(javatype)+" "+allt(varname)+";"+chr(13)
r1=r1+"public void set"+allt(classname)+"("+allt(javatype)+" newValue){"+allt(varname)+" = newValue;}"+chr(13)
r1=r1+"public "+allt(javatype)+" get"+allt(classname)+"(){return "+allt(varname)+";}"+chr(13)+chr(13)
endif
endscan
r=r+r0+chr(13)
r=r+"public "+mclassname+"(){}"+chr(13)
r=r+r1+chr(13)+"}"
return r

foxpro getdao

para p_name
store 'VENDOR' TO P_NAME
CLOS DATA
set dele on
use result in 0
use gendao in 0
sele gendao
SET FILTER TO tableNAME=P_NAME
replace all ftype with 'BIG' FOR 'INT' $ FIELD1
REPLACE ALL FIELD1 WITH ALLT(FIELD1)
REPLACE ALL FIELD1 WITH STRTRAN(FIELD1,space(4),'')
replace ALL fieldname with  STRT(SUBS(FIELD1,AT('TABLE',FIELD1)+5),'(','') , ZTYPE WITH 'TABLE' for 'TABLE' $ FIELD1
replace all fieldname with allt(fieldname)
replace all fieldname with strt(allt(fieldname),chr(9),'')
replace ALL classname  with  proper(fieldname) for 'TABLE' $ FIELD1
replace all fieldname with  LEFT(FIELD1,AT(' ',FIELD1)),ZTYPE WITH 'FIELD'  for (not 'TABLE' $ FIELD1) and (not 'ENGINE' $ FIELD1)
replace all ZTYPE WITH 'ENGINE'  for  'ENGINE' $ FIELD1
*replace all zvariable with  lower(fieldname) for ztype='FIELD'
replace all zvariable with  lower(fieldname)
replace all varName with "seqNum" for zvariable='msg_seq_no'
replace all varName with t2v(zvariable) for (not zvariable='msg_seq_no')
replace ALL classname with upper(left(varName,1))+subs(varName,2)  for ztype='FIELD' or ztype='TABLE'
sele gendao
scan
do case
case aarray(field1,1,' ')='RECORDID'
replace fieldtype with "bigint(20) NOT NULL DEFAULT '0'"
other
if aarray(field1,2,' ')='C'
replace fieldtype with 'NVARCHAR('+aarray(field1,3,' ')+") DEFAULT ''"
endif
if aarray(field1,2,' ')='D'
replace fieldtype with 'DOUBLE DEFAULT 0'
endif
if aarray(field1,2,' ')='I'
replace fieldtype with 'INTEGER DEFAULT 0'
endif

if aarray(field1,2,' ')='F'
replace fieldtype with 'FLOAT DEFAULT 0'
endif
endcase
Endscan

select result
locate for name=p_name
if not found()
insert into result (name) value (p_name)
endif
store  createtable() to _r
replace in result createtab with _r
sele result
brow
sele gendao
set filter to
*brow field field1:30,tablename:30,fieldname:30,classname:30, varname:30,fieldtype:30
function createtable()
r= ''
select gendao
_delimiter=''
quota='"'
quota2="+"
scan
if ztype='TABLE'
r=r+quota+"create table "+allt(tablename) +' ( '+quota+quota2+chr(13)
else
r=r +quota+ _delimiter+allt(fieldname)+' '+ALLT(fieldtype)+quota+quota2+chr(13)
_delimiter=','
endif
endscan
r=r+quota+','+ "PRIMARY KEY (`RECORDID`)"+quota+quota2
r=r+quota+');'+quota+";"+chr(13)
return r

function t2v()
para p_1
for i=0 to 25
p_1=strt(p_1,'_'+chr(97+i),chr(65+i))
endfor
r=p_1
return r

foxpro-aarray string 2 array str2array java str.spirit

e.g.
 _HEADERS="HKID,TypeOfForm,Surname,GivenName,NameInChinese,Sex,MaritalStatus,PpNum,SpouseName,"
_HEADERS=_HEADERS+"SpouseHKID,SpousePpNum,ResAddr,AreaCodeResAddr,PosAddr,Capacity,PtPrinEmp,"
_HEADERS=_HEADERS+"StartDateOfEmp,EndDateOfEmp,PerOfSalary,AmtOfSalary,PerOfLeavePay,AmtOfLeavePay,"
_HEADERS=_HEADERS+"PerOfDirectorFee,AmtOfDirectorFee,PerOfCommFee,AmtOfCommFee,"
_HEADERS=_HEADERS+"PerOfBpEtc,AmtOfBpEtc,"
_HEADERS=_HEADERS+"PerOfPayRetire,AmtOfPayRetire,PerOfSalTaxPaid,AmtOfSalTaxPaid,"
_HEADERS=_HEADERS+"PerOfEduBen,AmtOfEduBen,"
_HEADERS=_HEADERS+"PerOfGainShareOption,AmtOfGainShareOption,"
_HEADERS=_HEADERS+"NatureOtherRAP1,PerOfOtherRAP1,AmtOfOtherRAP1,"
_HEADERS=_HEADERS+"NatureOtherRAP2,PerOfOtherRAP2,AmtOfOtherRAP2,"
_HEADERS=_HEADERS+"NatureOtherRAP3,PerOfOtherRAP3,AmtOfOtherRAP3,"
_HEADERS=_HEADERS+"PerOfPension,AmtOfPension,TotalIncome,"
_HEADERS=_HEADERS+"PlaceOfResInd,AddrOfPlace1,NatureOfPlace1,PerOfPlace1,"
_HEADERS=_HEADERS+"RentPaidEr1,RentPaidEe1,RentRefund1,RentPaidErByEe1,"
_HEADERS=_HEADERS+"AddrOfPlace2,NatureOfPlace2,PerOfPlace2,"
_HEADERS=_HEADERS+"RentPaidEr2,RentPaidEe2,RentRefund2,RentPaidErByEe2,"
_HEADERS=_HEADERS+"OverseaIncInd,AmtPaidOverseaCo,NameOfOverseaCo,AddrOfOverseaCo,Remarks"

for rr = 1 to occur(",",_headers)
? aarray(_headers,rr)
endfor








aarray.prg
lpara p_source, p_column,p_divider
store iif(empty(p_source),'',p_source) to p_source
store iif(empty(p_column),'',p_column) to p_column
*? aarray(sysmess('POToleranceQty'),-2,'.')
* e.g. ? aarray(sysmess('VendorInvoiceDate'),3,'.')
*store iif(empty(p_divider),'',p_divider) to p_divider   && cannot add it becuase for space p_divider
*set step on
if parameter()=0 and setass()
_screen.fontsize=9
_screen.fontname='·s²Ó©úÅé'
clear
? fullpath('aarray.prg')
? fullpath('aarray.fxp')
text

e.g. aarray('A,APPLE,BANANA,CAR',2)=APPLE
e.g. aarray(program(),1,'.') i.e. formName
e.g.
store 'turnover=turover1, one=once' to aaa
for _rr=1 to occur(',',aaa)+1
? aarray(aaa,_rr)
store aarray(aaa,_rr) to _bb
? aarray(_bb,1,'=')
? aarray(_bb,2,'=')
endfor

e.g.

sele pay_sxx0917
scan while wage_cfm==_d
_1=_1+datetimer+','
endscan
for rr=1 to occur(',',_1)
?  aarray(_1,rr)
endfor  && 20120917

ENDTEXT
return
endif
store iif(empty(p_column),1,p_column) to p_column
store .f. to v_right
if p_column<0
store abs(p_column) to p_column
store .t. to v_right
endif
local _r110813,__r110813,_b110813,rr_1022
store p_source to _r110813
store '' to __r110813

if  parameter()=2
store ',' to p_divider
endif
store _r110813 to _a
if not type('_a')=='C'
store '' to _a
endif
if not type('p_divider')=='C'
store ',' to p_divider
endif


if v_right
store p_divider+_a to _a
store subs(_a,at(p_divider,_a,p_column)+1) to __r110813

else
store p_divider+_a +p_divider to _a
store subs(_a,at(p_divider,_a,p_column)+1, at(p_divider,_a,p_column+1)-at(p_divider,_a,p_column)-1) to __r110813
endif
store allt(__r110813) to __r110813
return __r110813

foxpro-gen-asp-select

clos data
use chart1 in 0
use result in 0
r=""
sele chart1
scan
r=r+ '"'+allt(chart1.raw)+' "&chr(13)&_'+chr(13)
endscan
sele result
locate for name='chart1'
if not found()
append blank
replace name with 'chart1'
endif
replace aspselect with r

foxpro t2v table to variable nameConvention

para p_1
r=''
for i=0 to 25
sou='_'+chr(97+i)
tar=chr(65+i)
? sou
?? tar
r=strt(p_1,allt(sou),allt(tar))
endfor
return r

foxpro-file-create

if thisform.v_ok
store '' to _filename
do case
case inlist(thisform.htmlgenh.screenmode,1,3)
store allt(thisform.htmlgenh.php_view) to _filename
nhandle=fcreate('c:\appserv\www\test\'+_filename)
aaa=fputs(nhandle,thisform.php_retrieval_file)
fclose(nhandle)
other
store allt(thisform.htmlgenh.php_save) to _filename
nhandle=fcreate('c:\appserv\www\test\'+_filename)
aaa=fputs(nhandle,thisform.php)
fclose(nhandle)
store allt(thisform.htmlgenh.php_retriv) to _filename
nhandle=fcreate('c:\appserv\www\test\'+_filename)
aaa=fputs(nhandle,thisform.php_retrieval_file)
fclose(nhandle)
store allt(thisform.htmlgenh.html_file) to _filename
nhandle=fcreate('c:\appserv\www\test\'+_filename)
aaa=fputs(nhandle,thisform.whole)
fclose(nhandle)
endcase
*thisform.hyperlink1.navigateto ("http://localhost/test/"+allt(_filename))
do form htmlview with 'http://127.0.0.1/test/'+_filename
thisform.Pageframe1.activepage=1
endif

foxpro-gen-html-input

clos data
set dele on
store 'MENU' to mmm
use edward in 0
use result in 0
sele edward
r=''
r2=''
r3=''
r3a='stat="update '+mmm+' set '
r3b1='stat="insert into '+mmm+' ('
*stat="insert into menu (userid,password,divisionPermit,companyPermit) values ('" & q & "','"&mPassword&"',"&"'"&mdivisionPermit&"',"&"'"&mcompanyPermit&"');"
r3b2=''
i1=1
set memo to 200
dl=''
scan for name=mmm
? zdatabase
s1=allt(zdatabase)
s2=allt(label)
r=r+'<div>'+s2+'</div>'+chr(13)
r=r+'<div id="login-box-field">'+chr(13)
r=r+' <input '+chr(13)
r=r+' id="'+s1+'"'+chr(13)
r=r+' name="'+s1+'" '+chr(13)
r=r+' autocomplete=off '+chr(13)
r=r+' type="text" '+chr(13)
r=r+' class="form-login" '+chr(13)
r=r+' title="'+s1+'" value="" size="30" maxlength="30" />'+chr(13)
r=r+'</div>'+chr(13)
r2=r2+'_refreshv("'+s1+'",values['+allt(str(i1))+']);'+chr(13)
r3=r3+'m'+proper(s1)+'=Request.Form("'+s1+'")'+chr(13)
r3a=r3a+dl+s1+'='+"'"+'"&'+'m'+proper(s1)+'& "'+"'"
r3b1=r3b1+dl+s1
r3b2=r3b2+dl+chr(39)+'"&m'+proper(s1)+'&"'+chr(39)
dl=','
*divisionPermit='"&mdivisionPermit&"',companyPermit='"&mcompanyPermit&"',password='"&_mPassword&"' where userid='" & q &"';"'
i1=i1+1
endscan
r3a=r3a+' where Recordid='+chr(39)+'" & q &"'+chr(39)+';"'
? r3a
insertStatement= r3b1+') values (' +r3b2+')'+'"'
sele result
locate for allt(name)==allt(mmm)
if not found()
insert into result (name) values (mmm)
endif
replace html with r
replace ajax with r2
replace aspSave with r3+chr(13)+chr(13)+r3a+chr(13)+chr(13)+insertStatement
brow field aspSave,html,ajax, name

foxpro-gen_log for mysql which field change

clos data
set dele on
set memo to 200
use edward in 0
use result in 0
STORE 'EMPLOYEE' TO MMM
*EMPLOYEE
sele edward
r=''
scan for allt(name)=MMM and active
s0=allt(zdatabase)
s1=allt(label)
r=r+'IF (NEW.'+s0+' <> OLD.'+s0+') OR (NEW.'+s0+' IS NOT NULL AND OLD.'+s0+' IS NULL) OR (NEW.'+s0+' IS NULL AND OLD.'+s0+' IS NOT NULL) THEN'++chr(13)
r=r+"if (NEW."+S0+" <> OLD."+S0+") then"+chr(13)
r=r+" set logs=CONCAT(logs,'"+S0+" [ ' ,OLD."+S0+" ,'->', NEW."+S0+",']');"+chr(13)
r=r+"end if; "+chr(13)
r=r+"if (NEW."+S0+" IS NOT NULL AND OLD."+S0+" IS NULL) then"+chr(13)
r=r+" set logs=CONCAT(logs,'"+S0+" [ ' ,'NULL' ,'->', NEW."+S0+",']');"+chr(13)
r=r+"end if;"+chr(13)
r=r+"if (NEW."+S0+" IS NULL AND OLD."+S0+" IS NOT NULL) then "+chr(13)
r=r+" set logs=CONCAT(logs,'"+S0+" [ ' ,OLD."+S0+" ,'->', 'NULL',']');"+chr(13)
r=r+"end if; "+chr(13)
r=r+" set logs=CONCAT(logs,' ,');"+chr(13)
r=r+'END IF; '+chr(13)+chr(13)
endscan
sele result
locate for allt(name)==mmm
if not found()
insert into result (name) values (mmm)
endif
replace mysqllog with r
return r

foxpro-select

result in the column , e.g. code , o/s . onhand

select code, sum(qty) , sum(iif(code='a',qty,0 ))  as a , sum(iif(code='b',qty,0 )) as b ,  sum(iif(code='c',qty,0 ))  c from tempaa