FiveTech Support Forums

FUNCTION mySQLLov( oGet , aGF , aGV )

   LOCAL oLovBrw
   LOCAL nWnd_H  := ( LST_CHARPIX_H * 15 )
   LOCAL nTop    := oGet:xnTop
   LOCAL nLeft   := oGet:xnLeft
   LOCAL nRight  := oGet:xnRight
   LOCAL nBottom := oGet:xnBottom
   LOCAL cKeyID
   LOCAL nLovHight
   LOCAL nLovWidth
   LOCAL oLovWnd
   LOCAL oQry
   LOCAL j

   aGCoors := GetWndRect( oGet:hWnd )

   nTop    := aGCoors[ 1 ] + ( ( aGCoors[ 3 ] - aGCoors[ 1 ] )  + 1 )
   nLeft   := aGCoors[ 2 ]
   nBottom := aGCoors[ 3 ] + ( ( aGCoors[ 3 ] - aGCoors[ 1 ] ) * oGet:xnRows2Display )
   nRight  := aGCoors[ 4 ]
   nLovHight :=  ( nBottom - nTop ) / 2
   nLovWidth :=  ( nRight - nLeft ) / 2

   //msgalert( oGet:xcQry )

   oQry := oApp:gDBServer:Query( oGet:xcQry )

   DEFINE DIALOG oLovWnd FROM nTop , nLeft TO  nBottom, nRight  STYLE nOr( DS_MODALFRAME, WS_POPUP ) PIXEL

   @ 0 , 0 XBROWSE oLovBrw SIZE nLovWidth, nLovHight PIXEL ;
      OF oLovWnd  FONT oApp:oFontBrw1 ;
      COLUMNS oGet:xaCols;
      OBJECT oQry AUTOSORT

     //HEADERS oGet:xaHeaders ;

   oLovBrw:nMarqueeStyle       := MARQSTYLE_HIGHLROW

   //oLovBrw:nColDividerStyle    := LINESTYLE_BLACK
   //oLovBrw:nRowDividerStyle    := LINESTYLE_BLACK
   //oLovBrw:bClrSelFocus := { || { nRGB( 250, 145, 30 ), nRGB( 100, 0, 0 ) } }
    oLovBrw:bClrSelFocus :=   { || {  nRGB( 255, 255, 255 ) , nRGB( 0, 0, 160 ) } }
   //oLovBrw:bClrStd  := { || { CLR_BLACK, IIf( oQry:RecNo() % 2 == 0, nRGB( 193, 221, 255 ), nRGB( 221, 245, 255 ) ) } }
   oLovBrw:bChange  := { || ( oGet:cText( oLovBrw:cSeek ) )   }
   oLovBrw:bKeyChar := { | n, f | IIf( n == VK_RETURN , ( oGet:cText( AllTrim( ( oQry:FieldGet( oGet:xuGetField ) ) ) ), oLovWnd:END() )  , NIL ) }
   oLovBrw:lHeader  := .F.
   oLovBrw:lFooter  := .F.
   oLovBrw:lHScroll := .F.
   oLovBrw:lRecordSelector := .F.

   oLovBrw:cSeek := AllTrim( oGet:cText() )

   oQry:Seek( Upper( AllTrim( oGet:cText() ) ) ,  oGet:xuGetField , , ,.T. )

   oLovBrw:CreateFromCode()
   oLovWnd:oClient := oLovBrw


   ACTIVATE DIALOG oLovWnd   ON INIT ( oLovBrw:SetFocus() )

   cKeyID := oQry:FieldGet( oGet:xuKeyField )

   IF aGF != NIL .AND. aGV != NIL
      FOR j := 1 TO Len(aGF)
         AAdd(aGV, oQry:FieldGet( aGF[j] ) )
      NEXT
   ENDIF


   oQry:END()


   oGet:oWnd:GoNextCtrl( oGet:hWnd )  // Move NEXT Control


   RETURN cKeyID[code]


// CALLING SAMPLE IN THE GET



#xtranslate :xnTop          => :cargo\[1\]
#xtranslate :xnLeft         => :cargo\[2\]
#xtranslate :xnRight        => :cargo\[3\]
#xtranslate :xnBottom       => :cargo\[4\]
#xtranslate :xcQry          => :cargo\[5\]
#xtranslate :xuGetField     => :cargo\[6\]
#xtranslate :xuKeyField     => :cargo\[7\]
#xtranslate :xaCols         => :cargo\[8\]
#xtranslate :xaHeaders      => :cargo\[9\]
#xtranslate :xnRows2Display => :cargo\[10\]


#define LOV_NUM_IVARS 11

#define ALOV {0,0,20,40,"",1,2,{},{},15}


   @   140,   120 GET aoGET[ AI_IGRP_NAME ] VAR AD_IGRP_NAME SIZE 275, C_GETH PICTURE "@!" PIXEL OF oWnd ;
                  NOBORDER COLOR C_FCLR, C_BCLR FONT oApp:oFontGet UPDATE
                  

//------------------------------------------------------------------------------
// Lov Gets
//------------------------------------------------------------------------------

   aoGET[ AI_IGRP_NAME ]:cargo := ALOV

   aoGET[ AI_IGRP_NAME ]:bWhen   :=   { || IIf( aoGET[ AI_IGRP_NAME ]:SelectAll() = NIL, .T., .T. ) }
   
   // FUNCTION CALLED HERE **************************************************************************
   aoGET[ AI_IGRP_NAME ]:bChange :=   { | n, f | AD_IGRP_ID := mySQLLov( aoGET[ AI_IGRP_NAME ] ) }
   // FUNCTION CALLED HERE **************************************************************************
   
   aoGET[ AI_IGRP_NAME ]:bValid  :=   { ||  ! Empty( aoGET[ AI_IGRP_NAME ]:cText() ) } //.AND. GetCustAreaInfo( aoGET[ 4 ] ) ) }

   aoGET[ AI_IGRP_NAME ]:xnRight       :=  15
   aoGET[ AI_IGRP_NAME ]:xnBottom      :=  50
   aoGET[ AI_IGRP_NAME ]:xnRows2Display := 15
   aoGET[ AI_IGRP_NAME ]:xcQry         := "SELECT igrp_name, igrp_id FROM "+TBL_IGRP+" ORDER BY igrp_name ASC"
   aoGET[ AI_IGRP_NAME ]:xaCols        := { "igrp_name" }
   aoGET[ AI_IGRP_NAME ]:xaHeaders     := { "List Of Item Groups" }
   aoGET[ AI_IGRP_NAME ]:xuGetField    := "igrp_name"
   aoGET[ AI_IGRP_NAME ]:xuKeyField    := "igrp_id"

[/code]

#include "fivewin.ch"
#include "Constant.ch"
#include "xbrowse.ch"
#include "hbclass.ch"
#include "common.ch"
#include "inkey.ch"
#include "error.ch"

PROCEDURE MAIN
LOCAL nAccID 
LOCAL oDolPhinQry 
     // dolphin connection code should be here
     
    oDolPhinQry := MySQLDBServer:Query(  "SELECT acct_name, acct_id FROM "+TBL_ACCT+" ORDER BY acct_name ASC"  ) 

     // call XBROWSE INCREMENTAL FUNCTION   
     // param 1 : TITLE
     // PARAM 2 : DOLPHIN SQL QUERY
     // PARAM 3 : COLUMN NAME TO RETURN VALUE
     // PARAM 4 : COLUMN NAME TO DISPLAY IN THE XBROWSE LIST 
     
      nAcctID := mGenSearch( "Select Account To Modify",   , "acct_id" , "acct_name" )

      
      return
      
      
PROCEDURE mGenSearch(cTitle, oDolphinQry  , cKeyCol, cCol )
 
   

   LOCAL cSearch
   LOCAL oGet
   LOCAL cKeyID
   LOCAL oBrw
   LOCAL oQry
   LOCAL oDlg
   LOCAL aoBTN := Array(5)

   // pass  dolphin object query which having ORDER BY clause ASC.
   oQry := oDolphinQry 

   DEFINE DIALOG oDlg FROM 0,0 TO 450,375  STYLE nOr( DS_MODALFRAME, WS_POPUP ) PIXEL
   //TITLE cTitle

  // DEFINE DIALOG oLovWnd FROM nTop , nLeft TO  nBottom, nRight STYLE nOr( DS_MODALFRAME, WS_POPUP ) PIXEL

   @ 19 , 2 XBROWSE oBrw SIZE 185,190 PIXEL ;
      OF oDlg FONT oApp:oFontBrw1 ;
      COLUMNS cCol ;
      OBJECT  oQry AUTOSORT   ;

  @ 02, 09 SAY oBrw:oSeek PROMPT oBrw:cSeek SIZE 150, 10 PIXEL ;
      FONT oApp:fBold ;
      OF oDlg COLOR CLR_BLACK, CLR_YELLOW PICTURE '@!'

   oBrw:nMarqueeStyle       := MARQSTYLE_HIGHLROW

   //oBrw:nColDividerStyle    := LINESTYLE_BLACK
   //oBrw:nRowDividerStyle    := LINESTYLE_BLACK
   //oBrw:bClrSelFocus := { || { nRGB( 250, 145, 30 ), nRGB( 100, 0, 0 ) } }
   oBrw:bClrSelFocus :=   { || {  nRGB( 255, 255, 255 ) , nRGB( 0, 0, 160 ) } }
   //oBrw:bClrStd  := { || { CLR_BLACK, IIf( oQry:RecNo() % 2 == 0, nRGB( 193, 221, 255 ), nRGB( 221, 245, 255 ) ) } }
   //oBrw:bChange  := { || ( oGet:cText( oBrw:cSeek ) )   }
   oBrw:bKeyChar := { | n, f | IIf( n == VK_RETURN , ( cKeyID := oQry:FieldGet( cKeyCol ), oDlg:END() )  , NIL ) }
   oBrw:lHeader  := .F.
   oBrw:lFooter  := .F.
   oBrw:lHScroll := .F.
   oBrw:lRecordSelector := .F.


   //oQry:Seek( Upper( AllTrim( oGet:cText() ) ) ,  oGet:xuGetField , , ,.T. )

   /*
   @ 12.9, 05 BUTTONBMP aoBTN[ 1 ] OF oDlg   ;
      PROMPT "&OK" TEXTRIGHT SIZE 39, 14 ;
      BITMAP "BTN_OK" DEFAULT ;
      Left  FONT oApp:oFontBTN1    ;
      ACTION ( cKeyID := oQry:FieldGet( 2 ), oDlg:END() )


   @ 12.9, 21 BUTTONBMP aoBTN[ 2 ] OF oDlg ;
      PROMPT "&Cancel" TEXTRIGHT SIZE C_BTN_W, C_BTN_H ;
      Left  FONT oApp:oFontBTN1 ;
      ACTION ( cKeyId := NIL , oDlg:END() )

   */

   @ 210, 50 FLATBTN aoBTN[1] PROMPT "Select" SIZE (C_BTNW /2) , (C_BTNH/2)  PIXEL;
      COLOR C_FBTN_FCLR, C_FBTN_BCLR ;
      FONT oApp:oFontBTN1 OF oDlg   ;
      ACTION ( cKeyID := oQry:FieldGet( cKeyCol ) , oDlg:END() )


   @ 210, 110 FLATBTN aoBTN[2] PROMPT "Quit" SIZE (C_BTNW /2) , (C_BTNH/2)  PIXEL;
      COLOR C_FBTN_FCLR, C_FBTN_BCLR ;
      FONT oApp:oFontBTN1 OF oDlg   ;
      ACTION ( cKeyId := NIL , oDlg:END() )


   oBrw:CreateFromCode()
   oDlg:oClient := oBrw


   ACTIVATE DIALOG oDlg CENTERED ON INIT ( oBrw:SetFocus() )


   oQry:END()

   RETURN cKeyID

static FUNCTION DolphinSeek( c, oBrw, cQryWhere )

   local oQry        := oBrw:oMySql
   local nStart
   local uData, nNum, lRet
   local cSortOrder

   static aLastRec := {}

   if oBrw:lIncrFilter
      DEFAULT oBrw:cFilterFld := TOken( oQry:cOrder, , 1 )
      if Empty( oBrw:cFilterFld )
         return .f.
      endif

      if Empty( c )
         c     := cQryWhere
      else
         c     := If( Empty( cQryWhere ), "", "(" + cQryWhere + ") and " ) + ;
                  Lower( oBrw:cFilterFld ) + " like '" + ;
                  If( oBrw:lSeekWild, "%", "" ) + ;
                  c + "%'"
      endif
     // REBUILD WHERE CLAUSE AND REQUERY CURSOR DATA
      oQry:SetWhere( c, .t. )
      // GOING TOP
      oQry:GoTop()
      return ( oQry:LastRec() > 0 )

   endif

step # 1

//CREATE THIS TABLE IN THE MYSQL DATABASE

CREATE TABLE `dolphin_test` (
  `MEMBER_NAME` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

STEP # 2
//

INSERT INTO dolphin_test ( MEMBER_NAME ) values ("adds") ;
INSERT INTO dolphin_test ( MEMBER_NAME ) values ("asd") ;
INSERT INTO dolphin_test ( MEMBER_NAME ) values ("bbdd") ;

COMMIT ;

STEP # 3 
// OPEN THE testdol.prg from SAMPLE FOLDER
// GOTO LINE 54 APPROX
//comment the line

// oQry = oServer:Query( "SELECT * FROM table ORDER BY field1 ASC" )
//ADD BELOW STATMENT LINE AFTER THE ABOVE COMMENTED LINE.

oQry := oServer:Query( "SELECT MEMBER_NAME FROM DOLPHIN_TEST ORDER BY MEMBER_NAME ASC"  )
// PLEASE MAKE SURE THAT THE CONNECTION SHOULD POINT THE DATABASE WHICH HAS ABOVE TABLE.

STEP # 4
//compile the code and run it
//IT BROWSE THE DATA FROM TABLE DOLPHIN_TEST TABLE. NOW YOU CAN TYPE SOME CHAR TO SEARCH VALUES.
//BUT YOU HAVE TO TYPE  TWO TIMES A MEANS "AA" 
//WHEN YOU TYPE TWO TIMES "AA" IT HANGS THE APPLICATION AND WINDOWS OS ASK TO CLOSE THE APPLICATION.

lRet  := ( oQry:Seek( c, cSortOrder, nStart, oQry:LastRec(), .T., .T. ) != 0 )

step # 1

//CREATE THIS TABLE IN THE MYSQL DATABASE

CREATE TABLE `dolphin_test` (
  `MEMBER_NAME` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

STEP # 2
//

INSERT INTO dolphin_test ( MEMBER_NAME ) values ("adds") ;
INSERT INTO dolphin_test ( MEMBER_NAME ) values ("asd") ;
INSERT INTO dolphin_test ( MEMBER_NAME ) values ("bbdd") ;
INSERT INTO dolphin_test ( MEMBER_NAME ) values ("OC LTR ") ;
INSERT INTO dolphin_test ( MEMBER_NAME ) values ("ADbbdd") ;


COMMIT ;

STEP # 3 
// OPEN THE testdol.prg from SAMPLE FOLDER
// GOTO LINE 54 APPROX
//comment the line

// oQry = oServer:Query( "SELECT * FROM table ORDER BY field1 ASC" )
//ADD BELOW STATMENT LINE AFTER THE ABOVE COMMENTED LINE.

oQry := oServer:Query( "SELECT MEMBER_NAME FROM DOLPHIN_TEST ORDER BY MEMBER_NAME ASC"  )
// PLEASE MAKE SURE THAT THE CONNECTION SHOULD POINT THE DATABASE WHICH HAS ABOVE TABLE.

STEP # 4
//compile the code and run it
//IT BROWSE THE DATA FROM TABLE DOLPHIN_TEST TABLE. NOW YOU CAN TYPE SOME CHAR TO SEARCH VALUES.
//YOU HAVE TO TYPE  "O" ONLY  -- IT GOES TO THE DESIRED RECORD "OC LTR" UPTO THIS IT WORKS FINE WITHOUT ANY ISSUE.
//BUT WHEN TYPE NEXT CHAR "C" IT GIVES ERROR BEEP AND cSeek variable does not update with "OC" it still shows "O" only.
//WHEN "OC LTR" RECORD IS EXIST THEN IT SHOULD NOT GIVE ERROR BEEP.