<html>
<link href="style200.css" type="text/css" rel="stylesheet">

<body>
<textarea wrap="virtual" name="page" cols="80" rows="90">
/********************************************************************************************/
/****************** SAMPLE SQL Connection ***************************************************/
/********************************************************************************************/
#include "Tdsn.ch"
FUNCTION OpenConn( cDSN )
	LOCAL oDsn
	Local cpath := CURDRIVE()+":\"+CURDIR()+"\SQLITE.DAT"  // For SQLite Database
	Local nTypeDsn := 2  // System DSN
	oDsn := TMyDsn():New( cDSN )
	IF oDsn:nStatus == TDSN_NO_ERROR
		oDsn:Del()
	ENDIF
	oDsn:Close()
	// Create( cDSN, cDescription, cODBCDriver, cServer, cDataBase, cUser, nDsnType, cDriver, nPort )	
	#ifdef	SQL_LITE
		oDsn:Create( ;
			cDSN, ;
			"XXX Connection", ;
			"SQLite ODBC Driver", "", ;
			cpath)
	#Elseif MYSQL
		oDsn:Create( ;
			cDSN, ;
			"XXX Connection", ;
			"MySQL ODBC 3.51 Driver",;
         "xxx.x.x.xxx","parcel" ) // IP - xxx.x.x.xxx or  'localhost'
	#Elseif SQL_SERVER
		oDsn:Create( ;
				cDSN, ;
				"xxx Connection", ;
				"SQL Server", ;
				"Server", ;
				"Database" )
	#EndIf
	oDsn:Close()
RETURN NIL

FUNCTION CloseConn( cDSN )
	LOCAL oDsn
	oDsn := TMyDsn():New( cDSN )
	IF oDsn:nStatus == TDSN_NO_ERROR
		oDsn:Del()
	ENDIF
RETURN NIL




/********************************************************************************************/
/****************** SAMPLE List data and Search Data ****************************************/
/********************************************************************************************/
#include "MYHEADER.CH"

FUNCTION CUSTOMER()
	LOCAL cQRY
	Local oBtnAdd, oBtnEdt, oBtnDel, oBtnSch, oBtnMbl, oBtnCancel, oBtnPrint
	PRIV oDlg, oDAT, oBRW
  	CURSORWAIT()

	oODBC:EXECUTE( datatemp('zTmp')  )		
	cQryX0 := "delete from ztmp;"
	oODBC:EXECUTE( cQryX0  )		
	cQryX0 := "insert into ztmp (custid, tipex) select distinct custid, 'AKTIF' from workorder"
	oODBC:EXECUTE( cQryX0  )		

	cQRY := 	"select a.*, "+;
		"b.tipex "+;
		"from customer a "+;
		"left join zTmp b on a.custid = b.custid"

   oDAT := TDBODBC():NEW( cQry, oODBC )
   oDAT :AddPrimaryKey("CUSTID")
	oDAT :SETORDER("custnm, alamat")

   DEFINE DIALOG oDlg RESOURCE "LISTCUST" TITLE "Customer List"
  	REDEFINE BROWSE oBrw OF oDlg ID 201 UPDATE ON DBLCLICK oBtnEdt:click()

	oBrw:SEToOBJ( oDat )
	
	ADD COLUMN TO oBRW DATA oBRW:nAT   	  PICTURE "999,999" 	HEADER "No." ALIGN DT_RIGHT
	ADD COLUMN TO oBRW DATA oDAT:CUSTNM   PICTURE "@S30" 		HEADER "Customer"
	ADD COLUMN TO oBRW DATA oDAT:ALAMAT   PICTURE "@S40" 		HEADER "Alamat"
	ADD COLUMN TO oBRW DATA oDAT:TEL_MBL  PICTURE "@S20" 		HEADER "HP"
	ADD COLUMN TO oBRW DATA oDAT:TEL_RMH  PICTURE "@S20" 		HEADER "Rumah-Phn"
	ADD COLUMN TO oBRW DATA oDAT:TEL_KTR  PICTURE "@S20" 		HEADER "Kantor-Phn"
	ADD COLUMN TO oBrw DATA oDat:tipex 								HEADER "Status"	SIZE 050

	oBrw:Bkeydown := { |nKey| ;
		if( nkey==ASC("M"), oBtnMbl:click(), NIL ), ;
		if( nkey==VK_INSERT, oBtnAdd:click(), NIL ), ;
		if( nkey==VK_RETURN, oBtnEdt:click(), NIL ), ;
		if( nkey==VK_DELETE, oBtnDel:click(), NIL ), ;
		if( nkey==VK_ESCAPE, oDLG:END(), NIL ) }		

	oBrw:SetColor({ 1,2,3,4 },{ CLR_BLACK ,{ || if( oBrw:nAT  % 2 = 0,CLR_WHITE, CLR_LZSOFT )},  CLR_BLACK, CLR_LZHEADER })	

   cItem := "Customer"
	cCariMsg	:= "Customer"
	cCariDat	:= oDAT:CUSTNM
   REDEFINE COMBOBOX oCbx VAR cItem ITEMS { "Customer", "Alamat", "HP", "Rumah-Phn", "Kantor-Phn", "Status" } ;
      ID 110 OF oDlg ;
      ON CHANGE ( URUTDATA( oCbx:nAt ) )   

   REDEFINE BUTTON oBtnCancel ID 999 OF oDlg ACTION oDlg:END()

   REDEFINE BUTTON oBtnAdd 	ID 901 OF oDlg ACTION SHOWCUSTOMER( oDAT, .T. )
   REDEFINE BUTTON oBtnEdt 	ID 902 OF oDlg ACTION SHOWCUSTOMER( oDAT )
   REDEFINE BUTTON oBtnDel 	ID 903 OF oDlg ACTION HAPUS( oDAT )
   REDEFINE BUTTON oBtnSch 	ID 905 OF oDlg ACTION ( CARIDATA(), oBRW:SETFOCUS(.T.) )

	oDlg:SETCONTROL( oBRW )
	oDLG:lHELPICON := .F.
   ACTIVATE DIALOG oDlg CENTER VALID .T.
   oDAT:END()
   RELE ALL LIKE *
RETURN NIL

STATIC FUNCTION URUTDATA( nPlh )
	// { "Customer", "Alamat", "HP", "Rumah-Phn", "Kantor-Phn", "Status" }
	LOCAL nAT := oBRW:nAT	
	DO CASE
		CASE nPLH = 1
			cCariMsg	:= "Customer"
			cCariDat	:= oDat:Custnm
			cVar := ALLTRIM(oDat:Custnm)
			oDat:SETORDER("Custnm, alamat")
		CASE nPLH = 2
			cCariMsg	:= "Alamat"
			cCariDat	:= oDat:Alamat
			cVar := ALLTRIM(oDat:Alamat)
			oDat:SETORDER("Alamat, custnm")
		CASE nPLH = 3
			cCariMsg	:= "HP"
			cCariDat	:= oDat:Tel_mbl
			cVar := ALLTRIM(oDat:Tel_mbl)
			oDat:SETORDER("Tel_mbl, custnm")
		CASE nPLH = 4
			cCariMsg	:= "Rumah-Phn"
			cCariDat	:= oDat:Tel_rmh
			cVar := ALLTRIM(oDat:Tel_rmh)
			oDat:SETORDER("Tel_rmh, custnm")
		CASE nPLH = 5
			cCariMsg	:= "Kantor-Phn"
			cCariDat	:= oDat:Tel_Ktr
			cVar := ALLTRIM(oDat:Tel_Ktr)
			oDat:SETORDER("Tel_Ktr, custnm")
		CASE nPLH = 6
			cCariMsg	:= "Status"
			cCariDat	:= oDat:tipex
			cVar := ALLTRIM(oDat:tipex)
			oDat:SETORDER("tipex, custnm")
	ENDCASE
	IF oDat:SEEK( cVar )
		nAT := oDat:Recno()
		oBRW:nAt := nAT
	ELSE
	   oDat:GotoID( nAT )
   ENDIF
	oBrw:oBugUp()
   oDlg:UPDATE()
   oBrw:Setfocus()
RETURN NIL

STATIC FUNCTION CARIDATA()
   LOCAL nAT := oBRW:nAT
   STATIC xxVar
   cCariDat := uValBlank( cCariDat )
   IF MSGGET( "PENCARIAN DATA", cCariMsg,  @cCariDat )
      IF ! EMPTY( cCariDat )
      	xxVAR := cCariDat
      	IF VALTYPE( cCariDat ) = "C"
	      	xxVAR := ALLTRIM(cCariDat)
      	ENDIF
		  	CURSORWAIT()
			IF oDat:SEEK( xxVAR )
				nAT := oDat:Recno()
				oBRW:nAt := nAT
				oBrw:oBugUp()
			   oDlg:UPDATE()
         ELSE
         	ALERT("Data "+cCariMsg+" = "+cValToChar( xxVAR )+" Tidak Ada !!!")
            oDat:GotoID( nAT )
         ENDIF
      ENDIF
   ENDIF
RETURN NIL

STATIC FUNCTION HAPUS( oDAT )
	Local nAt := oBrw:nAt
	if UPPER(oDat:Tipex) = "AKTIF"
		ALERT("Data ini AKTIF, tidak dapat dihapus !!!")
		RETURN NIL
	ENDIF
	IF MSGYESNO("Record ini akan dihapus ?")
	  	CURSORWAIT()
	  	oODBC:EXECUTE("DELETE FROM MOBIL WHERE CUSTID = "+ALLTRIM(STR(oDAT:CUSTID)) )
		oDAT:DELETE()
		oDAT:Refresh()
		if nAt <> oDAT:Recno() .and. oDat:Reccount() > 0
			if nAt > oDat:Recno()
				nAt := oDat:Reccount()
			endif
			oDat:GotoID( nAt )
			oBRW:nAt := nAT
		endif
		oBrw:oBugUp()
		oDLG:UPDATE()
	ENDIF
RETURN NIL

FUNCTION SHOWCUSTOMER( oDAT, lADD )
	LOCAL oFONT
	Local oBtnSave, oBtnCancel
	LOCAL oGET[6]
	local nAt := oBrw:nAT
	LOCAL cTMP
	LOCAL cSEL := SELECT()
	DEFAULT lADD := .F.
	IF lADD
		cTitle:= "NEW CUSTOMER"
		SCATNEWSQL( oDAT, 'o' )
	ELSE
		cTitle:= "EDIT CUSTOMER"
		SCATTERSQL( oDAT, 'o' )			
	ENDIF
	priv oBrwMbl, oDlg1
	SELECT 11

	/******This Sample Using TdbOdbcDirect****************/
	
   cQRY2 := "SELECT MOBILID, NOPOL, MODEL, TYPE, TAHUN, WARNA, NOMESIN, NORANGKA, CUSTID FROM MOBIL WHERE CUSTID = "+GENSQL(oCUSTID)+" ORDER BY NOPOL"
   oRsX := oODBC:QUERY( cQRY2 )
   oRsX :OPEN()
   oRsX :PRIMARYKEYS("MOBILID")
   INDE ON FIELD_0002 TAG "NOPOL"
   oRsX :COMPLETE()

	/*****************************************************/

	DEFINE FONT oFONT NAME GetSysFont() SIZE 0, -12 BOLD
	DEFINE DIALOG oDlg1 RESOURCE "CUSTOMER" TITLE cTITLE
	
	REDEFINE GET oGET[1] VAR oCUSTID PICTURE "999999999" ID 100 OF oDlg1 UPDATE
	oGET[1]:DISABLE()
	oGET[1]:SETFONT( oFONT )

	REDEFINE GET oGET[2] VAR oCUSTNM 	PICTURE "@!" ID 101 OF oDlg1 UPDATE
	REDEFINE GET oGET[3] VAR oALAMAT 	PICTURE "@!" ID 102 OF oDlg1 UPDATE
	REDEFINE GET oGET[4] VAR oTEL_MBL	ID 103 OF oDlg1 UPDATE
	REDEFINE GET oGET[5] VAR oTEL_RMH	ID 104 OF oDlg1 UPDATE
	REDEFINE GET oGET[6] VAR oTEL_KTR	ID 105 OF oDlg1 UPDATE

  	REDEFINE BROWSE oBrwMbl OF oDlg1 ID 200 GRID ALIAS (oRsX:cAlias) UPDATE
	ADD COLUMN TO oBrwMbl DATA oRsX:FIELDGET(2) ;
		PICTURE "@!" ;
		EDITABLE	MOVE DT_MOVE_NEXT ; 
		VALID { |cVAR| IIF( EditBrow( 2, cVAR ), .T., .F. ) };
		HEADER "No.Pol" 		SIZE 70
	ADD COLUMN TO oBrwMbl DATA oRsX:FIELDGET(3) ;
		PICTURE "@!" ;
		VALID { |cVAR| IIF( EditBrow( 3, cVAR ), .T., .F. ) };
		EDITABLE	MOVE DT_MOVE_NEXT ; 
		HEADER "Model"			SIZE 111
	ADD COLUMN TO oBrwMbl DATA  oRsX:FIELDGET(4) ;
		EDITABLE	MOVE DT_MOVE_NEXT ; 
		VALID { |cVAR| IIF( EditBrow( 4, cVAR ), .T., .F. ) };
		HEADER "Type"			SIZE 111
	ADD COLUMN TO oBrwMbl DATA  oRsX:FIELDGET(5) ;
		EDITABLE	MOVE DT_MOVE_NEXT ; 
		VALID { |cVAR| IIF( EditBrow( 5, cVAR ), .T., .F. ) };
		HEADER "Tahun"
	ADD COLUMN TO oBrwMbl DATA  oRsX:FIELDGET(6) ;
		EDITABLE	MOVE DT_MOVE_NEXT ; 
		VALID { |cVAR| IIF( EditBrow( 6, cVAR ), .T., .F. ) };
		HEADER "Warna"			SIZE 100
	ADD COLUMN TO oBrwMbl DATA  oRsX:FIELDGET(7) ;
		EDITABLE	MOVE DT_MOVE_NEXT ; 
		VALID { |cVAR| IIF( EditBrow( 7, cVAR ), .T., .F. ) };
		HEADER "No.Mesin"		SIZE 100
	ADD COLUMN TO oBrwMbl DATA  oRsX:FIELDGET(8) ;
		EDITABLE	MOVE DT_MOVE_NEXT ; 
		VALID { |cVAR| IIF( EditBrow( 8, cVAR ), .T., .F. ) };
		HEADER "No.Rangka"		SIZE 100

   oBrwMbl:SetAppendMode( .T. )

   // oBrwMbl:SetDeleteMode( .T., .T., {|| ( oRsx:Delete(), oRsx:Refresh(.t.), oBrwMbl:BugUp() ) } ) // ( lOnOff, lConfirm, bDelete)
	// oBrwMbl:Bkeydown := { |nKey| if( nkey==VK_ESCAPE, oDlg1:END(), NIL ) }		

	oBrwMbl:Bkeydown := { |nKey| ;
		if( nkey==VK_DELETE, HAPUSDTL(oRsX), NIL ), ;
		if( nkey==VK_ESCAPE, oDLG1:END(), NIL ) }		
   
	oBrwMbl:SetColor({ 1,2,3,4 },{ CLR_BLACK ,{ || if( oBrw:nAT  % 2 = 0,CLR_WHITE, CLR_LZSOFT )},  CLR_BLACK, CLR_LZHEADER })	

	IF lADD
		oBrwMbl:DISABLE()
	ENDIF
	
   REDEFINE BUTTON oBtnSave 	ID 901 OF oDlg1 ACTION IIF( DATAOK( oGET ), ( EditMode( oDat, lADD ), oDlg1:END() ), )
   REDEFINE BUTTON oBtnCancel ID 902 OF oDlg1 ACTION oDlg1:END()

	oDlg1:lHELPICON := .F.
	ACTIVATE DIALOG oDlg1 CENTER
   oRsX:END()
   UNGATHERSQL( oDAT, "o" )
   RELE ALL LIKE *
   oDlg:Update()
   oBrw:SetFocus()
RETURN NIL	

STATIC FUNCTION HAPUSDTL( oRsx )
	LOCAL cXXX
	IF oRSX:MOBILID <> 0
		cQry := "select count(*) from workorder where mobilid = "+GENSQL(oRsx:mobilID)
		IF oODBC:QueryData( cQry ) > 0
			ALERT("Data ini AKTIF, tidak dapat dihapus !!!")
			RETURN NIL
		ENDIF
	ENDIF
	IF MSGYESNO("Record ini akan dihapus ?")
	  	CURSORWAIT()
		oRsx:DELETE()
		oRsx:REFRESH()
		oDLG1:UPDATE()
	ENDIF
RETURN NIL

STATIC FUNCTION EditBrow( nField, Cvar )
	LOCAL lADD := .F.
	LOCAL nAT  := ( oRsX:cALIAS )->( RECNO() )
	local mVar := iif( nField=2, upper(alltrim(strtran(ALLTRIM(cVar)," ",""))), upper(alltrim(cVar)) )
	local mStb := iif( nField=2, upper(alltrim(strtran(ALLTRIM(cVar)," ",""))), alltrim(oRsX:FIELDGET(2)) )
	IF nFIELD = 2
		IF ! CHKMOBIL( mVar )
			RETURN(.F.)
		ENDIF
	ENDIF
	if EMPTY( oRsX:FIELDGET(1) )
		oRsX:ADDNEW()
		oRsX:FIELDPUT(3, SPACE(40))
		oRsX:FIELDPUT(4, SPACE(40))
		oRsX:FIELDPUT(5, SPACE(4))
		oRsX:FIELDPUT(6, SPACE(20))
		oRsX:FIELDPUT(7, SPACE(20))
		oRsX:FIELDPUT(8, SPACE(20))
		oRsX:FIELDPUT(9, oDAT:CUSTID)
		lADD := .T.
	ELSE
		oRsX:EDIT()
	ENDIF
	oRsX:FIELDPUT(nField, mVar)
	oRsX:UPDATE()
	oRsX:REFRESH()
	IF lADD
      IF ( oRsX:CALIAS )->( DBSEEK( mStb ) )
      	nAt := oRsx:Recno()
      	oBrwMbl:nAt := nAT
         oBrwMbl:UPSTABLE()
      ELSE
         ( oRsX:CALIAS )->( DBGOTO( nAT ) )
      ENDIF
      oBrwMbl:BugUp()
   ENDIF
	oDLG1:UPDATE()
RETURN(.T.)

STATIC FUNCTION CHKMOBIL(uVAR)
	LOCAL lRET := .T., cQRY
	cQry := "select count(*) from mobil where nopol = "+GENSQL( uVar)+" and mobilid <> "+GENSQL(oRsX:MOBILID)
	IF oODBC:QueryData( cQry ) > 0
		ALERT("NOMOR POLISI = "+uVar+" sudah ada!!!")
		lRet := .F.
	ENDIF
RETURN lRET

STATIC FUNCTION DATAOK( oGET )
	LOCAL cQry
	Local lRET := .T., I := nCtr := 0
	AEVAL( oGet, {|x| ++nCtr, if(((nCtr = 2 .OR. nCtr = 3) .AND. Empty(x:Varget()) .AND. lRET), ( lRET := .F., I := nCtr ), nil ) } )
	IF !lRET
		ALERT("Field tidak boleh kosong !!! " )	
		oGET[I]:SETFOCUS()
		RETURN lRET
	ENDIF
	IF lRET
		cQry := "select count(*) from customer where "+;
			"custnm = "+GENSQL( alltrim( strtran( oGET[2]:VARGET(),"  "," " ) ) )+" "+;
			"and alamat = "+GENSQL( alltrim( strtran( oGET[4]:VARGET(),"  "," " ) ) )+" "+;
			"and custid <> "+GENSQL(oCUSTID)
		IF oODBC:QueryData( cQry ) > 0
			ALERT("CUSTOMER sudah ada!!!")
			oGET[2]:SETFOCUS()
			lRet := .F.
		ENDIF
	ENDIF
RETURN lRET

STATIC FUNCTION EditMode( oDBF, lADD )
	LOCAL lSTABILIZE := .F.
	LOCAL nAT := oBRW:nAT
  	CURSORWAIT()
  	oCUSTNM 	:= alltrim( strtran( oCUSTNM,"  "," " ) )
  	oALAMAT 	:= alltrim( strtran( oALAMAT,"  "," " ) )
	oTEL_MBL	:= alltrim( strtran( ALLTRIM(oTEL_MBL)," ","" ) )
	oTEL_RMH	:= alltrim( strtran( ALLTRIM(oTEL_RMH)," ","" ) )
	oTEL_KTR	:= alltrim( strtran( ALLTRIM(oTEL_KTR)," ","" ) )
	aflds 	:= { "CUSTNM","ALAMAT","TEL_MBL","TEL_RMH","TEL_KTR" }
	avars 	:= { oCUSTNM, oALAMAT, oTEL_MBL, oTEL_RMH, oTEL_KTR }
	if lADD
		sQueryX	:= genqry( 1, 'CUSTOMER', aflds, avars )
		lADD := .T.
	ELSE
		sQueryX	:= genqry( 2, 'CUSTOMER', aflds, avars, { { "CUSTID", oCUSTID } } )
	ENDIF
	oDBF:EXECUTE(sQueryX)
	oDBF:REFRESH()
	IF nAT > 0
		if nAt <> oDbf:Recno() .and. oDbf:Reccount() > 0
			if nAt > oDbf:Recno()
				nAt := oDbf:Reccount()
			endif
			oDbf:GotoID( nAt )
			oBRW:nAt := nAT
		endif
	ENDIF
   oBRW:oBUGUP()
   oDLG:UPDATE()
	oBRW:SETFOCUS()
RETURN
</textarea>
</body>
<html>