SQL Lite and QM

[stupomer]

Hi Gintaras,

I have added Find and Filter functions to dlg_QM_Grid2 --> now called dlg_QM_Grid2_Find_Filter.

Thanks so much for all your help in getting me here and the new version of QM looks great!
Stuart



Function dlg_QM_Grid2_Find_Filter

Code:

Copy      Help

\Dialog_Editor
function# hDlg message wParam lParam
if(hDlg) goto messages

#compile "qmgrid"
#compile CSqlite

;create database for testing. Normally you would create it in a database management program or in another macro.
if(dir("$desktop$\test80.db3")) goto g1
CSqlite db1.Open("$desktop$\test80.db3")
;db1.Exec("DROP TABLE table1"); err
;db1.Exec("CREATE TABLE IF NOT EXISTS table1 (A,B,C,D)")
db1.Exec("CREATE TABLE IF NOT EXISTS table1 (A PRIMARY KEY, B,C,D)")
db1.Exec("INSERT INTO table1 VALUES ('a','z[]x','Yes',3)")
db1.Exec("INSERT INTO table1 VALUES ('z','a',null,1)")
db1.Exec("INSERT INTO table1 VALUES ('k','k','Yes',8)")
db1.Close
;g1

if(!ShowDialog("dlg_QM_Grid2_Find_Filter" &dlg_QM_Grid2_Find_Filter 0)) ret

;BEGIN DIALOG
;0 "" 0x90C80A44 0x100 0 0 231 228 "QM_Grid"
;3 QM_Grid 0x54210009 0x200 0 0 232 140 ""
;12 Button 0x54032000 0x0 2 142 52 14 "Select 2 rows"
;5 Button 0x54032000 0x0 56 142 52 14 "Get Selected"
;13 Button 0x54032000 0x0 182 142 48 14 "Focus grid"
;6 Button 0x54032000 0x0 2 158 52 14 "Find rows"
;7 Static 0x54000000 0x0 56 160 54 12 "where in column"
;8 Edit 0x54030080 0x200 112 158 26 14 ""
;9 Static 0x54000000 0x0 140 160 20 12 "cell is"
;10 Edit 0x54030080 0x200 162 158 68 14 ""
;1 Button 0x54030001 0x0 82 184 48 14 "OK"
;4 Button 0x54032000 0x0 132 184 48 14 "Apply"
;2 Button 0x54030000 0x0 182 184 48 14 "Cancel"
;14 Button 0x54032000 0x0 0 202 40 20 "Find"
;15 Edit 0x54030080 0x200 44 202 54 20 ""
;16 Button 0x54032000 0x0 100 202 64 20 "Display Only Selected in Table"
;17 Button 0x54032000 0x0 166 202 64 20 "Return to Original"
;11 Static 0x54000010 0x20000 2 178 220 1 ""
;END DIALOG
;DIALOG EDITOR: "" 0x2030002 "" "" ""

ret
;messages
CSqlite- t_db
sel message
,case WM_INITDIALOG
,QgAddColumns id(3 hDlg) "Edit[]Multiline[]Check[]Combo" "80[]80[]80[]80" "0[]8[]2[]1"
,t_db.Open("$desktop$\test80.db3")
,t_db.ToQmGrid(id(3 hDlg) "SELECT * FROM table1")
,
,case WM_DESTROY
,case WM_COMMAND goto messages2
,case WM_NOTIFY goto messages3
ret
;messages2
str ss ExecLine SelectedResultsTable
ARRAY(str) ar
int c r hgrid=id(3 hDlg)
err-
sel wParam
,case [IDOK,4]
,t_db.FromQmGrid(id(3 hDlg) "table1")
,case IDCANCEL
,
,case 13 ;;Focus grid
,SetFocus hgrid
,
,case 12 ;;Select 2 rows
,SetFocus hgrid
,LvSelect hgrid 0 2|4 ;;sel first row
,LvSelect hgrid 1 1 ;;and next row
,
,case 5 ;;Get selected
,ARRAY(int) a; if(!LvGetSelectedItems(hgrid a)) ret ;;get selected indices
,ar.create(LvGetColumnCount(hgrid) a.len)
,for(r 0 ar.len(2)) for(c 0 ar.len(1)) LvGetItemText(hgrid a[r] c ar[c r])
,ss.From2dimArray(ar)
,mes ss
,
,case 6 ;;Find rows
,t_db.FromQmGrid(hgrid "temp" 0 "table1") ;;get all into temporary table
,str s1.getwintext(id(8 hDlg)) s2.getwintext(id(10 hDlg))
,_s.format("SELECT * FROM temp WHERE %s='%s' COLLATE NOCASE" s1 s2)
,t_db.Exec(_s ar)
,ss.From2dimArray(ar)
,mes ss
;
,case 14;;Find
,,ARRAY(str) WholeGrid
,,LvSelect hgrid -1;;deselect previously selected items
,,LvGetAll hgrid WholeGrid
,,str Query.getwintext((id(15 hDlg)));;a text input box in the dialog
,,str RxSearchTarget
,,int SearchQueryRowMatchesCounter = 0
,,ARRAY(int) SearchQueryRowMatches.create(100);;maximum 100 results
,,int i j Hit=0;;
,,for i 0 WholeGrid.len(2) ;;enumerate dimensions  FOR EACH ROW
,,,for j 0 WholeGrid.len(1) ;;enumerate dimension FOR EACH COLUMN
,,,,RxSearchTarget = WholeGrid[j i]
,,,,if(!empty(RxSearchTarget))                     
,,,,,Query.format("(?i)%s" Query);;makes search query case insensitive
,,,,,if(findrx(RxSearchTarget Query)>-1);; a search hit
,,,,,,Hit=1
,,,if Hit=1;;at least one hit in the row
,,,,SetFocus hgrid
,,,,LvSelect hgrid i 1;; don't deselect previous
,,,Hit=0;;reset before next row test
,,WholeGrid=0;;reset
,,
,case 16;;Display Selected    
,,t_db.FromQmGrid(id(3 hDlg) "temp2" 0 "table1") ;;get all into temporary table temp2    
,,t_db.TempTable("SelectedResultsTable" "table1")
,,hgrid=id(3 hDlg)
,,ARRAY(int) SelectedIndices; if(!LvGetSelectedItems(hgrid SelectedIndices)) ret ;;get selected indices
,,ar.create(4 SelectedIndices.len);;2 dimensions 2 elements in first dim, a.len = the number of selected items in the second
,,for(r 0 SelectedIndices.len)
,,,ExecLine = ""
,,,;out "Row: %i" r
,,,for(c 0 4)
,,,,LvGetItemText(hgrid SelectedIndices[r] c ar[c r])
,,,,;out ar[c r]
,,,,ExecLine.formata("'%s'," ar[c r])
,,,ExecLine.rtrim(",")
,,,;out ExecLine
,,,str ExecLineRowCol.format("INSERT INTO ''SelectedResultsTable'' VALUES (%s)" ExecLine)
,,,out ExecLineRowCol
,,,t_db.Exec(ExecLineRowCol)
,,t_db.ToQmGrid(hgrid "SELECT * FROM SelectedResultsTable")    
,
,case 17 ;;Return to Original
,,,t_db.ToQmGrid(hgrid "SELECT * FROM temp2" 0)
err+ mes _error.description; ret
ret 1
;messages3
NMHDR* nh=+lParam
if(nh.idFrom=3) ret DT_Ret(hDlg gridNotify2(nh))