Code: Select all | Expand
#include "fivewin.ch"#include "xbrowse.ch"#require "hbpgsql"#include "DbStruct.ch"REQUEST DBFNTXREQUEST DBFCDXREQUEST DBFFPTREQUEST DBFDBTstatic aData,oBrwDataPROCEDURE Main( cHost, cDatabase, cUser, cPass ) LOCAL oServer, oQuery, oRow, i, x, aTables, aStruct LOCAL cQuery, lAppend, cFileOrig, cFileDEST cHost := "localhost" cDatabase := "mydata" cUser := "postgres" cPass := "mypass" oServer := TPQServer():New( cHost, cDatabase, cUser, cPass ) IF oServer:NetErr() ?"Query 1 : "+oServer:ErrorMsg() QUIT ENDIF oServer:SetVerbosity( 2 ) oServer:traceon( "simple.log" ) cFileORIG := "customer.dbf" cFileDEST := "mst_customer" lAppend := .t. if !oServer:TableExists( cFileDEST ) ConvertToSQL(cFileORIG,cFileDEST,lAppend,oServer) CreateIndexSQL('mst_customer', 'recno_key', 'recno_key',oServer) CreateIndexSQL('mst_customer', 'first', 'first',oServer) CreateIndexSQL('mst_customer', 'city', 'city',oServer) CreateIndexSQL('mst_customer', 'first_city', 'first, city',oServer) endif BrowseData(oServer) oServer:Destroy() return nil static function ConvertToSQL(cFileORIG,cFileDEST,lAppend,oServer) local cComm, apCode, cOut local nErr, nPos LOCAL vEmp := {} Local nCnn, s,oSql local aReturn := {} local aReturnX := {} cFileORIG := lower(alltrim(cFileORIG)) cFileDEST := lower(alltrim(cFileDEST)) define dialog oDlgStock from 1,1 to 40,400 pixel style nOR( WS_CAPTION ) title "Tunggu Sebentar" activate dialog oDlgStock centered nowait oDlgStock:cTitle := cFileDEST SysRefresh() dbCloseAll() cSql := "DROP TABLE IF EXISTS "+cFileDEST oQuery := oServer:Execute( cSql ) IF oQuery:NetErr() ? "Query 2 : "+oQuery:ErrorMsg() QUIT ENDIF oQuery:Destroy() dbCloseAll() use (cFileORIG) new shared alias orig aStruct := orig->(DbStruct()) cField := "" for i := 1 to len(aStruct) cFieldName := alltrim(aStruct[i][DBS_NAME]) if lower(cFieldName) == "index" cFieldName := "XINDEX" endif cType := aStruct[i][DBS_TYPE] cLen := alltrim(Str( aStruct[i][DBS_LEN ], 3 )) cDec := alltrim(Str( aStruct[i][DBS_DEC ], 3 )) cOke := "" if cType == "C" cOke := " "+cFieldName+" CHAR ("+cLen+") , " endif if cType == "M" cOke := " "+cFieldName+" CHAR (150) , " endif if cType == "N" if val(cDec) == 0 cOke := " "+cFieldName+" NUMERIC ( "+cLen+", 0) , " else cOke := " "+cFieldName+" NUMERIC ( "+cLen+", 2) , " endif endif if cType == "D" cOke := " "+cFieldName+" DATE, " endif if cType == "L" cOke := " "+cFieldName+" BOOLEAN, " endif cField += cOke next cField := upper(cField) cSql := "CREATE TABLE "+cFileDEST+" ( recno_key serial primary key, " cSQL += cField cSQL += " edited_date DATE, edited_time CHAR(8) " cSQL += " );" ?cSql oQuery := oServer:Execute( cSql ) IF oQuery:NetErr() ? "Query 3 : "+oQuery:ErrorMsg() QUIT ENDIF oQuery:Destroy() if lAppend dbCloseAll() use (cFileORIG) new shared alias orig nMulai := 0 nPersen := 0 nRecord := orig->(lastrec()) orig->(dbGotop()) do while !orig->(eof()) ++nMulai nPersen := ( nMulai / nRecord ) * 100 oDlgStock:cTitle := cFileDEST +" "+str(nMulai,12)+"/"+str(nRecord,12)+"="+str(nPersen,12)+"%" SysRefresh() cFieldJalan := alltrim(orig->(FieldName(1))) cDatanya := orig->&cFieldJalan if valtype(cDatanya) == "N" cDatanya := alltrim(str(cDatanya,14,2)) else cDatanya := "'"+upper(alltrim(cDatanya))+"'" endif cSQL := "INSERT INTO "+alltrim(cFileDEST)+" ( "+cFieldJalan+" ) VALUES ( "+cDatanya+" )" oQuery := oServer:Execute( cSql ) IF oQuery:NetErr() ? "Query 4 : "+oQuery:ErrorMsg() QUIT ENDIF oQuery:Destroy() for x := 2 to orig->(fcount()) cFieldJalan := alltrim(orig->(FieldName(x))) cDatanya := orig->&cFieldJalan if valtype(cDatanya) == "N" cDatanya := alltrim(str(cDatanya,14,2)) endif if valtype(cDatanya) == "D" cTahun := strzero(year(cDatanya),4) cBulan := strzero(month(cDatanya),2) cTgl := strzero(day(cDatanya),2) cDatanya := cTahun+"-"+cBulan+"-"+cTgl endif if valtype(cDatanya) == "L" if cDatanya cDatanya := "1" else cDatanya := "0" endif endif if valtype(cDatanya) == "C" cChar := "" for xx := 1 to len(cDatanya) cOke := subs(cDatanya,xx,1) if cOke == "'" cOke := "" endif cChar += cOke next cDatanya := "'"+upper(alltrim(cChar))+"'" endif if valtype(cDatanya) == "M" cDatanya := "'"+upper(alltrim(cDatanya))+"'" endif cSql := "UPDATE "+alltrim(cFileDEST)+" SET "+cFieldJalan+" = "+cDatanya+" WHERE recno_key = "+alltrim(str(nMulai,12)) oQuery := oServer:Execute( cSql ) IF oQuery:NetErr() ? "Query 4a : "+oQuery:ErrorMsg() QUIT ENDIF oQuery:Destroy() next orig->(dbSkip()) enddo dbCloseAll() endif********************** oDlgStock:End()return nil static function BrowseData(oServer)local oDlgRekening,lAmbil := .f.oQuery := oServer:Query( "SELECT * from mst_customer order by recno_key" )aData := {}DO WHILE ! oQuery:Eof() aField := {} for nField := 1 to oQuery:FCount() cData := oQuery:FieldGet( nField ) if valtype(cData) == "C" if len(cData) == 0 cData := spac(oQuery:FieldLen( nField )) endif endif aadd(aField,cData ) next aadd(aData,aField ) oQuery:Skip() enddooQuery:Destroy() define dialog oDlgRekening from 1,1 to 500,900 TITLE "My Data" pixel @ 4,1 XBROWSE oBrwData size 440,200 pixel of oDlgRekening ARRAY aData ON DBLCLICK ( lAmbil := .t. ,oDlgRekening:End() ) oBrwData:nMarqueeStyle := MARQSTYLE_HIGHLCELL oBrwData:nColDividerStyle := LINESTYLE_BLACK oBrwData:nRowDividerStyle := LINESTYLE_BLACK oBrwData:lColDividerComplete := .t. oCol := oBrwData:AddCol() oCol:cHeader := "Record" oCol:bEditValue := { || aData[oBrwData:nArrayAt][1] } oCol:cEditPicture := "@!" oCol:nWidth := 100 oCol:nHeadStrAlign := 2 oCol:bClrStd := {|| { CLR_BLACK,nRGB(194,233,235) } } oCol := oBrwData:AddCol() oCol:cHeader := "First Name" oCol:bEditValue := { || aData[oBrwData:nArrayAt][2] } oCol:cEditPicture := "@!" oCol:nWidth := 150 oCol:nHeadStrAlign := 2 oCol:bClrStd := {|| { CLR_BLACK,nRGB(194,233,235) } } oCol:nEditType := 1 oCol:bOnPostEdit := { |o, varInput, nLastKey | iif( nLastKey == 13, ; ( aData[oBrwData:nArrayAt][2] := varInput , ; SimpanData( "first","'"+alltrim(varInput)+"'",oServer),oBrwData:goRight() ) ; , .t. ) } oCol := oBrwData:AddCol() oCol:cHeader := "Last Name" oCol:bEditValue := { || aData[oBrwData:nArrayAt][3] } oCol:cEditPicture := "@!" oCol:nWidth := 150 oCol:nHeadStrAlign := 2 oCol:bClrStd := {|| { CLR_BLACK,nRGB(194,233,235) } } oCol:bOnPostEdit := {|o, v, n| iif( n != VK_ESCAPE, ( aData[oBrwData:nArrayAt][3] := v , SimpanData( aData[oBrwData:nArrayAt][1] ,"last","'"+alltrim(v)+"'",oServer) ), ),oBrwData:Refresh() } oCol:nEditType := 1 oCol := oBrwData:AddCol() oCol:cHeader := "Street" oCol:bEditValue := { || aData[oBrwData:nArrayAt][4] } oCol:cEditPicture := "@!" oCol:nWidth := 300 oCol:nHeadStrAlign := 2 oCol:bClrStd := {|| { CLR_BLACK,nRGB(194,233,235) } } oCol:bOnPostEdit := {|o, v, n| iif( n != VK_ESCAPE, ( aData[oBrwData:nArrayAt][4] := v , SimpanData( aData[oBrwData:nArrayAt][1] ,"street","'"+alltrim(v)+"'",oServer) ), ),oBrwData:Refresh() } oCol:nEditType := 1 oBrwData:nHeaderHeight := 45 oBrwData:nRowHeight := 30 oBrwData:l2007 := .t. oBrwData:nFreeze := 2 oBrwData:nHeaderLines := 2 oBrwData:CreateFromCode() nRow := 220 nCol := 10 @nRow,nCol button "Top" size 30,14 of oDlgRekening pixel action ( oBrwData:Gotop(),oBrwData:SetFocus(),oBrwData:Refresh() ) nCol += 35 @nRow,nCol button "Bottom" size 30,14 of oDlgRekening pixel action ( oBrwData:GoBottom(),oBrwData:SetFocus(),oBrwData:Refresh() ) nCol += 35 @nRow,nCol button "Find First Name" size 60,14 of oDlgRekening pixel action ( CariNamaDepan(oServer) ) nCol += 65 @nRow,nCol button "Sort by First Name" size 60,14 of oDlgRekening pixel action ( NamaDepan(oServer) ) nCol += 65 @nRow,nCol button "Add 1 Record" size 60,14 of oDlgRekening pixel action ( LastRecord(oServer) ) nCol += 65 @nRow,nCol button "Add 500 Record" size 60,14 of oDlgRekening pixel action ( AddData("customer.dbf","mst_customer" ,oServer) ) nCol += 65 @nRow,nCol button "Delete Record" size 60,14 of oDlgRekening pixel action ( HapusData(aData[oBrwData:nArrayAt][1],oServer) ) nCol += 65 @nRow,nCol button "Exit" size 30,14 of oDlgRekening pixel action ( oDlgRekening:End() ) ACTIVATE DIALOG oDlgRekening ON INIT ( oBrwData:gotop(),oBrwData:SetFocus(), oBrwData:Refresh() ) return lAmbilstatic function CariNamaDepan(oServer)local cCari := spac(20)if MsgGet("Find First name","First Name",@cCari) cCari := upper(alltrim(cCari)) oQuery := oServer:Query( "SELECT * from mst_customer where first like '%"+cCari+"%' order by recno_key" ) aData := {} DO WHILE ! oQuery:Eof() aField := {} for nField := 1 to oQuery:FCount() cData := oQuery:FieldGet( nField ) if valtype(cData) == "C" if len(cData) == 0 cData := spac(oQuery:FieldLen( nField )) endif endif aadd(aField,cData ) next aadd(aData,aField ) oQuery:Skip() enddo oBrwData:SetArray(aData) oQuery:Refresh() oBrwData:Gotop() oBrwData:Refresh()endifreturn nilstatic function SimpanData(cField,cDatanya,oServer)nRecordSimpan := aData[oBrwData:nArrayAt][1]cFileDEST := "mst_customer"cSql := "UPDATE "+alltrim(cFileDEST)+" SET "+cField+" = "+cDatanya+" WHERE recno_key = "+alltrim(str(nRecordSimpan,12)) oQuery := oServer:Execute( cSql ) IF oQuery:NetErr() ? "Query 22a : "+oQuery:ErrorMsg() QUIT ENDIF oQuery:Destroy() return nilstatic function HapusData(nRecordSimpan,oServer)if !MsgYesNo("Are you sure ?") return nilendifcFileDEST := "mst_customer"cSql := "DELETE from "+alltrim(cFileDEST)+" WHERE recno_key = "+alltrim(str(nRecordSimpan,12)) oQuery := oServer:Execute( cSql ) IF oQuery:NetErr() ? "Query 22a : "+oQuery:ErrorMsg() QUIT ENDIF oQuery:Destroy() oQuery := oServer:Query( "SELECT * from mst_customer order by recno_key" ) IF oQuery:NetErr() ? "Query 22a : "+oQuery:ErrorMsg() QUIT ENDIFaData := {}DO WHILE ! oQuery:Eof() aField := {} for nField := 1 to oQuery:FCount() cData := oQuery:FieldGet( nField ) if valtype(cData) == "C" if len(cData) == 0 cData := spac(oQuery:FieldLen( nField )) endif endif aadd(aField,cData ) next aadd(aData,aField ) oQuery:Skip() enddooQuery:Destroy()oBrwData:SetArray(aData)oBrwData:Gotop()oBrwData:Refresh()return nilstatic function NamaDepan(oServer)oQuery := oServer:Query( "SELECT * from mst_customer order by first" )aData := {}DO WHILE ! oQuery:Eof() aField := {} for nField := 1 to oQuery:FCount() cData := oQuery:FieldGet( nField ) if valtype(cData) == "C" if len(cData) == 0 cData := spac(oQuery:FieldLen( nField )) endif endif aadd(aField,cData ) next aadd(aData,aField ) oQuery:Skip() enddooBrwData:SetArray(aData)oBrwData:Gotop()oBrwData:Refresh()return nilstatic function LastRecord(oServer)local nRecord := 0cSql := "SELECT recno_key "cSql += " FROM mst_customer "cSql += " ORDER BY recno_key DESC "cSql += " LIMIT 1 " oQuery := oServer:Execute( cSql ) IF oQuery:NetErr() ? "Query 22a : "+oQuery:ErrorMsg() QUIT ENDIF nRecord := oQuery:FieldGet( 1 )oQuery:Destroy()oQuery:Refresh() ++nRecordcFileDEST := "mst_customer"cFieldJalan := "first"cDatanya := "'ZZZZ'" cSQL := "INSERT INTO "+alltrim(cFileDEST)+" ( "+cFieldJalan+" ) VALUES ( "+cDatanya+" )" oQuery := oServer:Execute( cSql ) IF oQuery:NetErr() ? "Query 4 : "+oQuery:ErrorMsg() QUIT ENDIF oQuery:Destroy() oServer:Commit()oQuery := oServer:Query( "SELECT * from mst_customer order by recno_key DESC LIMIT 1" )aData := {}DO WHILE ! oQuery:Eof() aField := {} for nField := 1 to oQuery:FCount() cData := oQuery:FieldGet( nField ) if valtype(cData) == "C" if len(cData) == 0 cData := spac(oQuery:FieldLen( nField )) endif endif aadd(aField,cData ) next aadd(aData,aField ) oQuery:Skip() enddooQuery:Destroy()oBrwData:SetArray(aData)oBrwData:GoBottom()oBrwData:Refresh() return nil/*CREATE OR REPLACE FUNCTION create_index(table_name text, index_name text, column_name text) RETURNS void AS $$ declare l_count integer;begin select count(*) into l_count from pg_indexes where schemaname = 'public' and tablename = lower(table_name) and indexname = lower(index_name); if l_count = 0 then execute 'create index ' || index_name || ' on ' || table_name || '(' || column_name || ')'; end if;end;$$ LANGUAGE plpgsql;usage: select create_index('my_table', 'my_index_name', 'id');*/static function CreateIndexSQL(cTabelName,cIndexName,cFieldName,oServer) cQuery := "SELECT create_index('"+lower(cTabelName)+"', '"+lower(cTabelName)+"_"+lower(cIndexName)+"', '"+lower(cFieldName)+"');" oQuery := oServer:Query( cQuery ) IF oQuery:NetErr() ? "4"+oQuery:ErrorMsg() ENDIF oQuery:Destroy()return nil ****static function AddData(cFileORIG,cFileDEST,oServer)if MsgYesNo("Add 500 Record ?") cSql := "SELECT recno_key "+; "FROM mst_customer "+; "ORDER BY recno_key DESC "+; "LIMIT 1" oQuery := oServer:Execute( cSql ) IF oQuery:NetErr() ? "Query 4 : "+oQuery:ErrorMsg() QUIT ENDIF nLastRecord := oQuery:FieldGet( 1 ) //?str(cData,12) oQuery:Destroy() dbCloseAll() define dialog oDlgStock from 1,1 to 40,400 pixel style nOR( WS_CAPTION ) title "Tunggu Sebentar" activate dialog oDlgStock centered nowait use (cFileORIG) new shared alias orig nMulai := 0 nPersen := 0 nRecord := orig->(lastrec()) orig->(dbGotop()) do while !orig->(eof()) ++nMulai nPersen := ( nMulai / nRecord ) * 100 oDlgStock:cTitle := cFileDEST +" "+str(nMulai+nLastRecord,12)+"/"+str(nRecord+nLastRecord,12)+"="+str(nPersen,12)+"%" SysRefresh() cFieldJalan := alltrim(orig->(FieldName(1))) cDatanya := orig->&cFieldJalan if valtype(cDatanya) == "N" cDatanya := alltrim(str(cDatanya,14,2)) else cDatanya := "'"+upper(alltrim(cDatanya))+"'" endif cSQL := "INSERT INTO "+alltrim(cFileDEST)+" ( "+cFieldJalan+" ) VALUES ( "+cDatanya+" )" oQuery := oServer:Execute( cSql ) IF oQuery:NetErr() ? "Query 4 : "+oQuery:ErrorMsg() QUIT ENDIF oQuery:Destroy() for x := 2 to orig->(fcount()) cFieldJalan := alltrim(orig->(FieldName(x))) cDatanya := orig->&cFieldJalan if valtype(cDatanya) == "N" cDatanya := alltrim(str(cDatanya,14,2)) endif if valtype(cDatanya) == "D" cTahun := strzero(year(cDatanya),4) cBulan := strzero(month(cDatanya),2) cTgl := strzero(day(cDatanya),2) cDatanya := cTahun+"-"+cBulan+"-"+cTgl endif if valtype(cDatanya) == "L" if cDatanya cDatanya := "1" else cDatanya := "0" endif endif if valtype(cDatanya) == "C" cChar := "" for xx := 1 to len(cDatanya) cOke := subs(cDatanya,xx,1) if cOke == "'" cOke := "" endif cChar += cOke next cDatanya := "'"+upper(alltrim(cChar))+"'" endif if valtype(cDatanya) == "M" cDatanya := "'"+upper(alltrim(cDatanya))+"'" endif cSql := "UPDATE "+alltrim(cFileDEST)+" SET "+cFieldJalan+" = "+cDatanya+" WHERE recno_key = "+alltrim(str(nMulai+nLastRecord,12)) oQuery := oServer:Execute( cSql ) IF oQuery:NetErr() ? "Query 4a : "+oQuery:ErrorMsg() QUIT ENDIF oQuery:Destroy() next orig->(dbSkip()) enddo dbCloseAll() oDlgStock:End() oQuery := oServer:Query( "SELECT * from mst_customer order by recno_key" ) aData := {} DO WHILE ! oQuery:Eof() aField := {} for nField := 1 to oQuery:FCount() cData := oQuery:FieldGet( nField ) if valtype(cData) == "C" if len(cData) == 0 cData := spac(oQuery:FieldLen( nField )) endif endif aadd(aField,cData ) next aadd(aData,aField ) oQuery:Skip() enddo oBrwData:SetArray(aData) oQuery:Refresh() oBrwData:Gotop() oBrwData:Refresh() endif return nil
Best Regards
Fafi