rony2006 Posted May 3, 2016 Share Posted May 3, 2016 Hello guys, I know that maybe was better to ask on a SQL forum but I dont know any and I think you understand my code better. What I have now: I have a script that connects to a SQL db and I make a query $interogare = "SELECT `id`, `denumire`, `tip`, `producator`, `codproducator`, `distribuitor`, `coddistribuitor`, `codsap`, `codbm`, `locatie`, `cantitate`, `stocminim`, `um`, `datasiora`, `autor` FROM `materiale` WHERE denumire LIKE "& $filtrare1 & " OR tip LIKE "& $filtrare1 & " OR producator LIKE "& $filtrare1 & "OR id LIKE "& $filtrare1 & " OR autor LIKE "& $filtrare1 & "OR codsap LIKE "& $filtrare1 & "OR codproducator LIKE "& $filtrare1 & " OR distribuitor LIKE "& $filtrare1 & "OR coddistribuitor LIKE "& $filtrare1 & " OR datasiora LIKE "& $filtrare1 & " OR locatie LIKE "& $filtrare1 & "" $aOk = _EzMySql_GetTable2d($interogare) the value of $filtrare1 is searched by the query in all columns and rows from my table. I also use '% and %' at the value of $filtrare1 What I need: I need to add 2 more keyword. So then I will have 3 input box: $filtrare1 $filtrare2 $filtrare3 Lets say that I have the following table: col1 col2 A1A2 C1 C2 B1B2 A1 D1D2 D3 If $filtrare1 is "B1" now i have returned: B1B2 D1D2 D3 and column name. But now I want to also have a value in $filtrare2. So if $filtrare1 is A1 and $filtrare2 is D3 then I should have returned the column that contains keyword $filtrare1 AND keyword $filtrare2. How I can do this please? Link to comment Share on other sites More sharing options...
jchd Posted May 3, 2016 Share Posted May 3, 2016 You can't. All you can get back is the content of all columns specified in the SELECT query for all rows that match your criterion. If you expect to get something like "the column name and content which match this or that", then seriously consider changing your schema. It isn't a problem with SQL per se, but a limitation you (unwillingly) introduced yourself by organizing your data this way. If you're willing to explain in some detail what kind of data you manipulate and the relationships between items, I (and others) can give you more useful advice. SQL can pretty easily model semantics but you have to be very clear about the necessary semantics. So, what are col1, col2, A1, A2, B1, B2, ... ? This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
rony2006 Posted May 3, 2016 Author Share Posted May 3, 2016 @jchd and others: I built a software that manage maintenance at equipment in a production factory. This is the interface where the shift leader or the operator write his problems at machines: When he clicks the button "trimite" all the data is put in my database server: After that, he calls me and I check from my computer (another computer from network) what kind of ticket he created. What is the problem, were is the problem, the time, is a default, a improvment .etc Lets say that I want to see all the tickets that was made with the equipment "ABG8", Then I write ABG8 in the key 1 and when I click go, I have in the list only items that contains the word ABG8. Until now is good. But I also want to see in my list only the items that has equipment ABG8, but also to see ONLY the tickets that are "Closed". So for this I write "ABG8" at key 1 and "closed" at key 2 and then click go. The problem is that I don't know how to query the db using 2 or 3 keyword. Here is the autoit code: expandcollapse popup; -- Created with ISN Form Studio 2 for ISN AutoIt Studio -- ; #include <StaticConstants.au3> #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> #Include <GuiButton.au3> #include <GuiListView.au3> #include <GuiTab.au3> #include <EditConstants.au3> #include <ComboConstants.au3> #include "EzMySql.au3" #include <Array.au3> #include <Date.au3> #include "DTC.au3" $main = GUICreate("main",843,610,-1,-1,$WS_MAXIMIZEBOX,-1) GUISetBkColor(0xD9E5F3,$main) $tab = GUICtrlCreatetab(20,90,798,485,-1,-1) GUICtrlSetResizing(-1, $GUI_DOCKAUTO) GuiCtrlSetState(-1,2048) GUICtrlSetFont(-1,12,700,0,"Calibri") GUICtrlSetBkColor(-1,"0x35C8B9") GUICtrlCreateTabItem("Cautare Material") GUICtrlCreateTabItem("Adaugare Material") GUICtrlCreateTabItem("Ticket Nou") GUICtrlCreateTabItem("Lista Tickete") GUICtrlCreateTabItem("Statistici") GUICtrlCreateTabItem("Istoric Echipamente") GUICtrlCreateTabItem("Arhive") GUICtrlCreateTabItem("") _GUICtrlTab_SetCurFocus($tab,-1) GUICtrlCreatePic(@ScriptDir & "\res\logo.jpg",0,0,843,63,-1,-1) GUICtrlSetResizing(-1, $GUI_DOCKHCENTER) GUISwitch($main,_GUICtrlTab_SetCurFocus($tab,4)&GUICtrlRead ($tab, 1)) GUICtrlCreateButton("My Text",92,153,100,30,-1,-1) GUICtrlCreateButton("My Text",112,193,100,30,-1,-1) GUISwitch($main,_GUICtrlTab_SetCurFocus($tab,5)&GUICtrlRead ($tab, 1)) GUICtrlCreateLabel("iesiri piese de schimb",88,153,154,15,-1,-1) GUICtrlSetBkColor(-1,"-2") GUISwitch($main,_GUICtrlTab_SetCurFocus($tab,0)&GUICtrlRead ($tab, 1)) $lista = GUICtrlCreatelistview("CUI |Denumire |Tip |Producator |Cod Prod. |Distribuitor |Cod Dis. |Cod SAP |Cod BM |Locatie |Cantitate|Stoc Min. |U.M. |Data |Adaugat de",47,168,738,276,$LVS_NOLABELWRAP,BitOr($LVS_EX_FULLROWSELECT,$LVS_EX_GRIDLINES)) GUICtrlSetBkColor(-1,"0xEAFBFF") GUISwitch($main,_GUICtrlTab_SetCurFocus($tab,5)&GUICtrlRead ($tab, 1)) GUICtrlCreateLabel("intrari piese de schimb",72,208,154,15,-1,-1) GUICtrlSetBkColor(-1,"-2") GUISwitch($main,_GUICtrlTab_SetCurFocus($tab,0)&GUICtrlRead ($tab, 1)) $filtrare = GUICtrlCreateInput("",112,128,150,25,-1,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUISwitch($main,_GUICtrlTab_SetCurFocus($tab,5)&GUICtrlRead ($tab, 1)) GUICtrlCreateLabel("interventii piese de schimb",72,268,154,15,-1,-1) GUICtrlSetBkColor(-1,"-2") GUISwitch($main,_GUICtrlTab_SetCurFocus($tab,0)&GUICtrlRead ($tab, 1)) GUICtrlCreateLabel("Keyword:",47,131,144,25,-1,-1) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $go = GUICtrlCreateButton("GO!",267,128,32,25,-1,-1) GUICtrlSetBkColor(-1,"0xFDBA55") $iesire1 = GUICtrlCreateButton("Iesire 1 u.m.",63,483,137,25,-1,-1) GUICtrlSetResizing(-1, $GUI_DOCKHCENTER) ;;;;;;;;;;;;;;;;;;;;; GUICtrlSetBkColor(-1,"0x80FF00") $iesirenr = GUICtrlCreateInput("",63,522,48,25,-1,$WS_EX_CLIENTEDGE) ;;;;; GUICtrlSetResizing(-1, $GUI_DOCKHCENTER) GUICtrlSetFont(-1,12,400,0,"Calibri") $iesirenrb = GUICtrlCreateButton("Iesire",152,522,48,25,-1,-1) GUICtrlSetResizing(-1, $GUI_DOCKHCENTER) GUICtrlSetBkColor(-1,"0x00FFFF") GUICtrlCreateLabel("u.m.",119,528,30,15,-1,-1) GUICtrlSetResizing(-1, $GUI_DOCKHCENTER) GUICtrlSetFont(-1,10,400,0,"MS Sans Serif") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateButton("Iesire 1 u.m.",370,598,137,25,-1,-1) $intrare1 = GUICtrlCreateButton("Intrare 1 u.m.",633,483,137,25,-1,-1) GUICtrlSetBkColor(-1,"0x80FF00") $intrarenr = GUICtrlCreateInput("",633,522,48,25,-1,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlCreateLabel("u.m.",688,528,30,15,-1,-1) GUICtrlSetFont(-1,10,400,0,"MS Sans Serif") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateButton("Iesire",339,611,48,25,-1,-1) $intrarenrb = GUICtrlCreateButton("Intrare",722,522,48,25,-1,-1) GUICtrlSetBkColor(-1,"0x00FFFF") GUICtrlCreateGroup("Operatii",47,456,741,100,-1,-1) GUICtrlSetFont(-1,10,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xFFFFFE") $sterge = GUICtrlCreateButton("Stergere pozitie",268,483,99,25,-1,-1) GUICtrlSetResizing(-1, $GUI_DOCKHCENTER) GUICtrlSetBkColor(-1,"0xFF0000") GUISwitch($main,_GUICtrlTab_SetCurFocus($tab,1)&GUICtrlRead ($tab, 1)) GUICtrlCreateLabel("Denumire:",47,168,72,25,-1,-1) GUICtrlSetResizing(-1, $GUI_DOCKHCENTER) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Tip:",47,206,72,25,-1,-1) GUICtrlSetResizing(-1, $GUI_DOCKHCENTER) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $denumire = GUICtrlCreateInput("",142,168,150,23,-1,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3F4FE") GUICtrlCreateLabel("Producator:",47,244,95,25,-1,-1) GUICtrlSetResizing(-1, $GUI_DOCKHCENTER) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $tip = GUICtrlCreateInput("",142,206,150,23,-1,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3F4FE") $producator = GUICtrlCreateInput("",142,244,150,23,-1,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3F4FE") GUICtrlCreateLabel("Distribuitor:",47,282,95,25,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $distribuitor = GUICtrlCreateInput("",142,282,150,23,-1,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3F4FE") GUICtrlCreateLabel("Cod Producator:",490,168,119,25,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $codproducator = GUICtrlCreateInput("",620,168,150,23,-1,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3F4FE") GUICtrlCreateLabel("Cod Distribuitor",490,206,119,25,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $coddistribuitor = GUICtrlCreateInput("",620,206,150,23,-1,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3F4FE") GUICtrlCreateLabel("Cod SAP:",490,244,119,25,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $codsap = GUICtrlCreateInput("",620,244,150,23,-1,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3F4FE") GUICtrlCreateLabel("Cod BM:",490,282,119,25,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $codbm = GUICtrlCreateInput("",620,282,150,23,-1,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3F4FE") GUICtrlCreateLabel("Cantitate:",47,352,95,25,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $cantitate = GUICtrlCreateInput("",142,352,63,23,-1,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3F4FE") GUICtrlCreateLabel("U.M.:",47,390,95,25,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $um = GUICtrlCreateInput("",142,390,63,23,$ES_LOWERCASE,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3F4FE") $stocminim = GUICtrlCreateInput("",142,428,63,23,-1,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3F4FE") GUICtrlCreateLabel("Stoc Minim:",47,428,95,25,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Locatie:",490,352,119,25,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") ;$detalii = GUICtrlCreateInput("",142,466,150,70,-1,$WS_EX_CLIENTEDGE) ;GUICtrlSetFont(-1,12,400,0,"Calibri") ;GUICtrlSetBkColor(-1,"0xD3F4FE") GUICtrlCreateLabel("Detalii:",47,466,95,25,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $locatie = GUICtrlCreateCombo("",620,352,150,23,-1,-1) GUICtrlSetData(-1,"DL-1-R1-A|DL-1-R1-B|DL-1-R2-A|DL-1-R2-B|DL-1-R3-A|DL-1-R3-B|DL-1-R4-A|DL-1-R4-B|DL-1-R5-A|DL-1-R5-B" & _ "|DL-2-R1-A|DL-2-R1-B|DL-2-R2-A|DL-2-R2-B|DL-2-R3-A|DL-2-R3-B|DL-2-R4-A|DL-2-R4-B|DL-2-R5-A|DL-2-R5-B" & _ "|DL-3-R1-A|DL-3-R1-B|DL-3-R2-A|DL-3-R2-B|DL-3-R3-A|DL-3-R3-B|DL-3-R4-A|DL-3-R4-B|DL-3-R5-A|DL-3-R5-B|") GUICtrlSetFont(-1,10,400,0,"Verdana") GUICtrlSetBkColor(-1,"0xFFA851") GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3F4FE") GUICtrlCreateLabel("Data si ora:",490,390,119,25,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $datasiora = GUICtrlCreateInput(_Now() ,620,390,150,23,$ES_READONLY,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3F4FE") GUICtrlCreateLabel("Autor:",490,428,119,25,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $autor = GUICtrlCreateInput(@Username,620,428,150,23,$ES_READONLY,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3F4FE") $adaugare = GUICtrlCreateButton("Adaugare",584,508,137,25,-1,-1) GUICtrlSetResizing(-1, $GUI_DOCKHCENTER) GUICtrlSetBkColor(-1,"0x80FF00") $reset =GUICtrlCreateButton("Resetare Campuri",421,508,137,25,-1,-1) GUICtrlSetResizing(-1, $GUI_DOCKHCENTER) GUICtrlSetBkColor(-1,"0xFFFF00") GUISwitch($main,_GUICtrlTab_SetCurFocus($tab,2)&GUICtrlRead ($tab, 1)) GUICtrlCreateLabel("Titlu:",43,188,72,25,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $t_titlu = GUICtrlCreateInput("",192,188,150,23,-1,$WS_EX_CLIENTEDGE) GUICtrlSetLimit(-1, 20) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3FFD2") GUICtrlCreateLabel("Prioritate:",43,226,167,88,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $t_descriere = GUICtrlCreateInput("",191,264,235,106,BitOr($ES_LOWERCASE,$ES_MULTILINE),$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3FFD2") $t_prioritate = GUICtrlCreateCombo("",191,226,151,27,-1,-1) GUICtrlSetData(-1,"Mare|Medie|Mica") GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3FFD2") GUICtrlCreateLabel("Recurent:",159,635,183,25,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Descriere:",43,264,72,25,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Echipament:",43,383,99,25,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $t_echipament = GUICtrlCreateCombo("Alege sau scrie",192,383,150,23,-1,$WS_EX_CLIENTEDGE) GUICtrlSetData(-1,"ABG8|PUR23|Presa Montare|Sauna|HV|IPS6|Packing|Testare Manuala|Topex|Masina de Gaurit|UV|Statii Costitorire|Altele") GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3FFD2") GUICtrlCreateLabel("Comanda:",41,445,99,25,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $t_comanda = GUICtrlCreateInput("",190,445,150,23,-1,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3FFD2") GUICtrlCreateLabel("Material:",41,483,99,25,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $t_material = GUICtrlCreateInput("",190,483,150,23,-1,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3FFD2") GUICtrlCreateLabel("Autor:",493,150,63,25,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $t_autor = GUICtrlCreateInput("",609,150,150,23,-1,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3FFD2") $t_termen = GUICtrlCreateInput("",609,226,150,23,-1,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3FFD2") GUICtrlCreateLabel("Data si ora:",493,264,91,24,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $t_datasiora = GUICtrlCreateInput("",609,264,150,23,$ES_READONLY,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3FFD2") GUICtrlCreateLabel("Status TS:",493,302,91,24,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Categorie ticket:",43,150,167,26,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Disponibil",609,302,91,24,-1,-1) GUICtrlSetFont(-1,13,700,0,"Calibri") GUICtrlSetColor(-1,"0x00FF13") GUICtrlSetBkColor(-1,"-2") $t_categorie = GUICtrlCreateCombo("",191,150,151,21,-1,-1) GUICtrlSetData(-1,"Defect|Imbunatatire|Preventiv|Comanda|Altele") GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3FFD2") $t_reset = GUICtrlCreateButton("Resetare Campuri",421,510,137,25,-1,-1) GUICtrlSetResizing(-1, $GUI_DOCKHCENTER) GUICtrlSetBkColor(-1,"0xFFFF00") GUICtrlCreateLabel("Responsabil:",493,188,91,24,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $t_trimite = GUICtrlCreateButton("Trimite",584,510,137,25,-1,-1) GUICtrlSetResizing(-1, $GUI_DOCKHCENTER) GUICtrlSetBkColor(-1,"0x80FF00") $t_responsabil = GUICtrlCreateInput("oricine",609,188,150,23,-1,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3FFD2") GUICtrlCreateLabel("",312,389,75,25,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Termen:",493,226,91,24,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUISwitch($main,_GUICtrlTab_SetCurFocus($tab,1)&GUICtrlRead ($tab, 1)) $detalii = GUICtrlCreateInput("",142,466,150,70,BitOr($ES_MULTILINE,$WS_VSCROLL),$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xD3F4FE") $tezt = GUISwitch($main,_GUICtrlTab_SetCurFocus($tab,0)&GUICtrlRead ($tab, 1)) $detaliib = GUICtrlCreateButton("Detalii....",268,522,99,25,-1,-1) GUICtrlSetResizing(-1, $GUI_DOCKHCENTER) GUICtrlSetBkColor(-1,"0xFFFF00") GUISwitch($main,_GUICtrlTab_SetCurFocus($tab,3)&GUICtrlRead ($tab, 1)) $key1 = GUICtrlCreateInput("",89,126,108,25,-1,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlCreateLabel("Key 1:",47,131,144,25,-1,-1) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xFFFFFF") GUICtrlCreateLabel("Key 2:",226,129,39,20,-1,-1) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xFFFFFF") $key2 = GUICtrlCreateInput("",276,126,108,25,-1,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlCreateLabel("Key 3:",413,129,39,20,-1,-1) GUICtrlSetFont(-1,12,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $key3 = GUICtrlCreateInput("",463,126,108,25,-1,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,12,400,0,"Calibri") $keygo = GUICtrlCreateButton("GO!",575,126,32,25,-1,-1) GUICtrlSetBkColor(-1,"0xFDBA55") $listatickete = GUICtrlCreatelistview("CUI|Categorie |Prioritate|Status |Titlu |Echipament|Responsabil|Data |Creat de ",47,168,738,320,$LVS_NOLABELWRAP,BitOr($LVS_EX_FULLROWSELECT,$LVS_EX_GRIDLINES));369 GUICtrlSetBkColor(-1,"0xD3FFD2") GUICtrlSetState(-1,BitOr($GUI_SHOW,$GUI_ENABLE,$GUI_DEFBUTTON)) GUICtrlSetBkColor(-1,"0xD3FFD2") $detaliiticket = GUICtrlCreateButton("Detalii Ticket",61,532,137,25,-1,-1) GUICtrlSetBkColor(-1,"0x80FF00") GUICtrlCreateGroup("Operatii",48,510,741,61,-1,-1) GUICtrlSetFont(-1,10,400,0,"Calibri") GUICtrlSetBkColor(-1,"0xFFFFFE") $refresht = GUICtrlCreateButton("Refresh",215,532,137,25,-1,-1) GUICtrlSetBkColor(-1,"0xFFFF00") _GUICtrlTab_SetCurFocus($tab,0) GUISetState(@SW_SHOW,$main) ;ShellExecute("Notificari.exe") Func guiticket() $detalii_ticket = GUICreate("Detalii Ticket",851,312,-1,-1,-1,-1) GUISetBkColor(0xD9E5F3,$detalii_ticket) $ttitlu1 = GUICtrlCreateLabel("",310,10,317,24,-1,-1) GUICtrlSetFont(-1,12,700,0,"Verdana") GUICtrlSetColor(-1,"0x353535") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Categorie:",20,50,78,24,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Titlu:",20,80,78,24,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Prioritate:",20,110,78,24,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Termen:",20,140,78,24,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Descriere:",20,200,78,24,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Echipament:",20,170,95,24,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Comanda:",320,50,78,24,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Material:",320,80,78,24,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Autor Ticket:",320,110,92,24,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Autor Inchidere:",320,170,116,24,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Responsabil:",320,140,92,24,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Data Creare:",593,50,100,24,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Data Preluare:",593,80,117,24,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Timp Reactie:",593,110,117,24,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Data Inchidere:",593,140,117,24,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Timp Rezolvare:",593,170,117,24,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Downtime:",593,200,117,24,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") GUICtrlCreateLabel("Masura:",320,200,78,24,-1,-1) GUICtrlSetFont(-1,13,400,0,"Calibri") GUICtrlSetBkColor(-1,"-2") $tcategorie = GUICtrlCreateInput("",115,50,150,22,$ES_READONLY,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,8,400,0,"Verdana") $ttitlu = GUICtrlCreateInput("",115,80,150,22,$ES_READONLY,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,8,400,0,"Verdana") $tprioritate = GUICtrlCreateInput("",115,110,150,22,$ES_READONLY,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,8,400,0,"Verdana") $ttermen = GUICtrlCreateInput("",115,140,150,22,$ES_READONLY,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,8,400,0,"Verdana") $techipament = GUICtrlCreateInput("",115,170,150,22,$ES_READONLY,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,8,400,0,"Verdana") $tdescriere = GUICtrlCreateInput("",20,224,245,78,$ES_MULTILINE,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,8,400,0,"Verdana") $tcomanda = GUICtrlCreateInput("",444,50,115,22,$ES_READONLY,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,8,400,0,"Verdana") $tmaterial = GUICtrlCreateInput("",444,80,115,22,$ES_READONLY,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,8,400,0,"Verdana") $tautor = GUICtrlCreateInput("",444,110,115,22,$ES_READONLY,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,8,400,0,"Verdana") $tresponsabil = GUICtrlCreateInput("",444,140,115,22,$ES_READONLY,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,8,400,0,"Verdana") $tautorinchidere = GUICtrlCreateInput("",444,170,115,22,$ES_READONLY,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,8,400,0,"Verdana") GUICtrlCreateInput("",320,224,239,78,-1,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,8,400,0,"Verdana") $tdatacreare = GUICtrlCreateInput("",710,50,130,22,$ES_READONLY,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,8,400,0,"Verdana") $tdatapreluare = GUICtrlCreateInput("",710,80,130,22,$ES_READONLY,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,8,400,0,"Verdana") GUICtrlCreateInput("",20860,20060,115,22,$ES_READONLY,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,8,400,0,"Verdana") $treactie = GUICtrlCreateInput("",710,110,130,22,$ES_READONLY,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,8,400,0,"Verdana") $tdatainchidere = GUICtrlCreateInput("",710,140,130,22,$ES_READONLY,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,8,400,0,"Verdana") $trezolvare = GUICtrlCreateInput("",710,170,130,22,$ES_READONLY,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,8,400,0,"Verdana") $tdowntime = GUICtrlCreateInput("",710,200,130,22,$ES_READONLY,$WS_EX_CLIENTEDGE) GUICtrlSetFont(-1,8,400,0,"Verdana") $preluaret = GUICtrlCreateButton("Preluare Ticket",593,231,116,30,-1,-1) GUICtrlSetFont(-1,9,400,0,"Verdana") $inchideret = GUICtrlCreateButton("Inchidere Ticket",593,272,116,30,-1,-1) GUICtrlSetFont(-1,9,400,0,"Verdana") GUISetState(@SW_SHOW,$detalii_ticket) ;Afisare Date Standard ;@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ ;@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ ;@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ ;@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; Global $ab = _GUICtrlListView_GetItemTextArray($listatickete,-1) Database_start() $interogare = "SELECT `status`, `categorie`, `titlu`, `prioritate`, `descriere`, `comanda`, `material`, `echipament`, `autor_ticket`, `autor_terminare`, `responsabil`, `termen`, `data_creare`, `data_deschidere`, `data_inchidere`, `masura`, `detalii`, `t_reactie`, `t_interventie`, `t_downtime` FROM `tichete` WHERE id= "& $ab[1] &"" $ainterogare = _EzMySql_GetTable2d($interogare) $error = @error If Not IsArray($ainterogare) Then MsgBox(0, $sMySqlStatement & " error", $error) Database_stop() $tit = "Detalii Ticket nr." & $ab[1] GUICtrlSetData($ttitlu1, $tit) GUICtrlSetData($tcategorie, $ainterogare[1][1]) GUICtrlSetData($ttitlu, $ainterogare[1][2]) GUICtrlSetData($tprioritate, $ainterogare[1][3]) GUICtrlSetData($tdescriere, $ainterogare[1][4]) GUICtrlSetData($tcomanda, $ainterogare[1][5]) GUICtrlSetData($tmaterial, $ainterogare[1][6]) GUICtrlSetData($techipament, $ainterogare[1][7]) GUICtrlSetData($tautor, $ainterogare[1][8]) GUICtrlSetData($tautorinchidere, $ainterogare[1][9]) GUICtrlSetData($tresponsabil, $ainterogare[1][10]) GUICtrlSetData($ttermen, $ainterogare[1][11]) GUICtrlSetData($tdatacreare, $ainterogare[1][12]) GUICtrlSetData($tdatapreluare, $ainterogare[1][13]) GUICtrlSetData($tdatainchidere, $ainterogare[1][14]) GUICtrlSetData($treactie, $ainterogare[1][17]) GUICtrlSetData($trezolvare, $ainterogare[1][18]) While 1 $nMsg = GUIGetMsg() Switch $nMsg ;daca modific variabila asta, mai imi merg butoanele? sau trebuie sa fac alt swicht Case $GUI_EVENT_CLOSE Exit ;;;Adaugare Material Case $preluaret Database_start() $t_data1 = "'" & $t_datasiora & "'" $iesire1st = "UPDATE tichete SET data_deschidere="&$t_data1&" WHERE id= "& $ab[1] &"" If Not _EzMySql_Exec($iesire1st) Then MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf $iesire1st = "UPDATE tichete SET status='Preluat' WHERE id= "& $ab[1] &"" If Not _EzMySql_Exec($iesire1st) Then MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf ;Refresh la GUI Detalii ticket $interogare = "SELECT `status`, `categorie`, `titlu`, `prioritate`, `descriere`, `comanda`, `material`, `echipament`, `autor_ticket`, `autor_terminare`, `responsabil`, `termen`, `data_creare`, `data_deschidere`, `data_inchidere`, `masura`, `detalii`, `t_reactie`, `t_interventie`, `t_downtime` FROM `tichete` WHERE id= "& $ab[1] &"" $ainterogare = _EzMySql_GetTable2d($interogare) $error = @error If Not IsArray($ainterogare) Then MsgBox(0, $sMySqlStatement & " error", $error) Database_stop() GUICtrlSetData($tcategorie, $ainterogare[1][1]) GUICtrlSetData($ttitlu, $ainterogare[1][2]) GUICtrlSetData($tprioritate, $ainterogare[1][3]) GUICtrlSetData($tdescriere, $ainterogare[1][4]) GUICtrlSetData($tcomanda, $ainterogare[1][5]) GUICtrlSetData($tmaterial, $ainterogare[1][6]) GUICtrlSetData($techipament, $ainterogare[1][7]) GUICtrlSetData($tautor, $ainterogare[1][8]) GUICtrlSetData($tautorinchidere, $ainterogare[1][9]) GUICtrlSetData($tresponsabil, $ainterogare[1][10]) GUICtrlSetData($ttermen, $ainterogare[1][11]) GUICtrlSetData($tdatacreare, $ainterogare[1][12]) GUICtrlSetData($tdatapreluare, $ainterogare[1][13]) GUICtrlSetData($tdatainchidere, $ainterogare[1][14]) ;Calculare timp reactie $ttcreare = GUICtrlRead($tdatacreare) $ttpreluare = GUICtrlRead($tdatapreluare) Global $sOut_Date = _Date_Time_Convert($ttcreare, "dd.MM.yyyy HH:mm:ss", "yyyy/MM/dd HH:mm:ss") MsgBox (0, "",$sOut_Date) Global $sOut_Date1 = _Date_Time_Convert($ttpreluare, "dd.MM.yyyy HH:mm:ss", "yyyy/MM/dd HH:mm:ss") MsgBox (0, "",$sOut_Date1) Global $trea = _DateDiff ('n', $sOut_Date, $sOut_Date1 ) GUICtrlSetData($treactie, $trea) ;Update pozitie cu t_reactie Database_start() $t_rea = "'" & $trea & "'" $iesire1st = "UPDATE tichete SET t_reactie="&$t_rea&" WHERE id= "& $ab[1] &"" If Not _EzMySql_Exec($iesire1st) Then MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf Database_stop() Case $inchideret GUICtrlSetData($tdatainchidere, $t_datasiora) Database_start() $t_data1 = "'" & $t_datasiora & "'" $iesire1st = "UPDATE tichete SET data_inchidere="&$t_data1&" WHERE id= "& $ab[1] &"" If Not _EzMySql_Exec($iesire1st) Then MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf $iesire1st = "UPDATE tichete SET status='Rezolvat' WHERE id= "& $ab[1] &"" If Not _EzMySql_Exec($iesire1st) Then MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf Database_stop() $ttpreluare = GUICtrlRead($tdatapreluare) $ttrezolvare = GUICtrlRead($tdatainchidere) Global $sOut_Date = _Date_Time_Convert($ttpreluare, "dd.MM.yyyy HH:mm:ss", "yyyy/MM/dd HH:mm:ss") Global $sOut_Date1 = _Date_Time_Convert($ttrezolvare, "dd.MM.yyyy HH:mm:ss", "yyyy/MM/dd HH:mm:ss") Global $trea1 = _DateDiff ('n', $sOut_Date, $sOut_Date1 ) GUICtrlSetData($trezolvare, $trea1) $t_rea2 = "'" & $trea1 & "'" Database_start() $iesire1st = "UPDATE tichete SET t_interventie="&$t_rea2&" WHERE id= "& $ab[1] &"" If Not _EzMySql_Exec($iesire1st) Then MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf Sleep (1000) $ttpre = GUICtrlRead($treactie) $downtime = $trea1 + $ttpre GUICtrlSetData($tdowntime, $downtime) ; de updatat pozitia tiketului cu downtimeu, ; sa pun downtimeul si ca info general la deschiderea gui-ului ; ; ; ; ; ; ; ;IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII ;IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII ;IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII ;IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII ;IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII ;IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII ;IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII ;IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII ;IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII ;IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII ;IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII Case Else Global $t_datasiora = _Now() EndSwitch Wend EndFunc Func Database_start() If Not _EzMySql_Startup() Then MsgBox(0, "Error Starting MySql", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf $Pass = "" If Not _EzMySql_Open("127.0.0.1", "root", $Pass, "test", "3306") Then MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf EndFunc Func Database_stop() _EzMySql_Close() _EzMySql_ShutDown() EndFunc While 1 $nMsg = GUIGetMsg() Switch $nMsg ;daca modific variabila asta, mai imi merg butoanele? sau trebuie sa fac alt swicht Case $GUI_EVENT_CLOSE Exit ;;;Adaugare Material Case $reset GUICtrlSetData($denumire,"") GUICtrlSetData($tip,"") GUICtrlSetData($producator,"") GUICtrlSetData($codproducator,"") GUICtrlSetData($distribuitor,"") GUICtrlSetData($coddistribuitor,"") GUICtrlSetData($cantitate,"") GUICtrlSetData($locatie,"") GUICtrlSetData($um,"") GUICtrlSetData($codbm,"") GUICtrlSetData($stocminim,"") GUICtrlSetData($codsap,"") GUICtrlSetData($detalii,"") beep (2500, 100) Case $Adaugare ;mai jos scriu ce sa se intample in CAZ ca se apasa butonul trimite Database_start() $denumirex = GUICtrlRead($denumire) $denumire1 = "'" & $denumirex & "'" ; adauga ' la denumire ca sa fie bine interpretat de mysql query $tipx = GUICtrlRead($tip) $tip1 = "'" & $tipx & "'" $producatorx = GUICtrlRead($producator) $producator1 = "'" & $producatorx & "'" $codproducatorx = GUICtrlRead($codproducator) $codproducator1 = "'" & $codproducatorx & "'" $distribuitorx = GUICtrlRead($distribuitor) $distribuitor1 = "'" & $distribuitorx & "'" $coddistribuitorx = GUICtrlRead($coddistribuitor) $coddistribuitor1 = "'" & $coddistribuitorx & "'" $cantitatex = GUICtrlRead($cantitate) $cantitate1 = "'" & $cantitatex & "'" $locatiex = GUICtrlRead($locatie) $locatie1 = "'" & $locatiex & "'" $umx = GUICtrlRead($um) $um1 = "'" & $umx & "'" $codbmx = GUICtrlRead($codbm) $codbm1 = "'" & $codbmx & "'" $datasiorax = GUICtrlRead($datasiora) $datasiora1 = "'" & $datasiorax & "'" $autorx = GUICtrlRead($autor) $autor1 = "'" & $autorx & "'" $stocminimx = GUICtrlRead($stocminim) $stocminim1 = "'" & $stocminimx & "'" $codsapx = GUICtrlRead($codsap) $codsap1 = "'" & $codsapx & "'" $detaliix = GUICtrlRead($detalii) $detalii1 = "'" & $detaliix & "'" $validare = MsgBox (1, "Adaugare material nou", "Previzualizare" & @CRLF & @CRLF & "DENUMIRE: " & $denumirex & @CRLF & "CANTITATE: " & $cantitatex & @CRLF & "LOCATIE: " & $locatiex ) if $validare = 2 then beep (1500, 150) Else $sMySqlStatement = "INSERT INTO `materiale` (`id`, `denumire`, `tip`, `producator`, `codproducator`, `distribuitor`, `coddistribuitor`, `codsap`, `codbm`, `cantitate`, `um`, `stocminim`, `locatie`, `datasiora`, `autor`, `detalii`) VALUES (NULL, " & $denumire1 & ", " & $tip1 & ", " & $producator1 & ", " & $codproducator1 & ", " & $distribuitor1 & ", " & $coddistribuitor1 & ", " & $codsap1 & ", " & $codbm1 & ", " & $cantitate1 & ", " & $um1 & ", " & $stocminim1 & ", " & $locatie1 & ", " & $datasiora1 & ", " & $autor1 & ", " & $detalii1 & ");" If Not _EzMySql_Exec($sMySqlStatement) Then MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf beep (2500, 100) Database_start() EndIf ;;;Cautare material Case $go beep (2500, 100) Database_start() $filtrarex = GUICtrlRead($filtrare) $filtrare1 = "'%" & $filtrarex & "%'" _GUICtrlListView_DeleteAllItems($lista) ;sterge toate itemele din lista $sMySqlStatement ="yg" $interogare = "SELECT `id`, `denumire`, `tip`, `producator`, `codproducator`, `distribuitor`, `coddistribuitor`, `codsap`, `codbm`, `locatie`, `cantitate`, `stocminim`, `um`, `datasiora`, `autor` FROM `materiale` WHERE denumire LIKE "& $filtrare1 & " OR tip LIKE "& $filtrare1 & " OR producator LIKE "& $filtrare1 & "OR id LIKE "& $filtrare1 & " OR autor LIKE "& $filtrare1 & "OR codsap LIKE "& $filtrare1 & "OR codproducator LIKE "& $filtrare1 & " OR distribuitor LIKE "& $filtrare1 & "OR coddistribuitor LIKE "& $filtrare1 & " OR datasiora LIKE "& $filtrare1 & " OR locatie LIKE "& $filtrare1 & "" $aOk = _EzMySql_GetTable2d($interogare) $error = @error If Not IsArray($aOk) Then MsgBox(0, $sMySqlStatement & " error", $error) _GUICtrlListView_SetItemCount($lista, 5000) ; One column load $iTimer = TimerInit() _GUICtrlListView_AddArray($lista, $aOk) ;MsgBox($MB_SYSTEMMODAL, "Information", "Load time: " & TimerDiff($iTimer) / 1000 & " seconds") Sleep (50) _GUICtrlListView_DeleteItem($lista, 0) ; sterge primu element (colum header) _EzMySql_Close() _EzMySql_ShutDown() ; Case $iesire1 $ab = _GUICtrlListView_GetItemTextArray($lista,-1) Database_start() ;$ab[1] este id-ul(cui-ul) la randul selectat $iesire1st = "UPDATE materiale SET cantitate=cantitate-1 WHERE id= "& $ab[1] &"" If Not _EzMySql_Exec($iesire1st) Then MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf ;Refresh la lista $filtrarex = GUICtrlRead($filtrare) $filtrare1 = "'%" & $filtrarex & "%'" _GUICtrlListView_DeleteAllItems($lista) ;sterge toate itemele din lista Database_start() $sMySqlStatement ="yg" $interogare = "SELECT `id`, `denumire`, `tip`, `producator`, `codproducator`, `distribuitor`, `coddistribuitor`, `codsap`, `codbm`, `locatie`, `cantitate`, `stocminim`, `um`, `datasiora`, `autor` FROM `materiale` WHERE denumire LIKE "& $filtrare1 & " OR tip LIKE "& $filtrare1 & " OR producator LIKE "& $filtrare1 & "OR id LIKE "& $filtrare1 & " OR autor LIKE "& $filtrare1 & "OR codproducator LIKE "& $filtrare1 & " OR distribuitor LIKE "& $filtrare1 & "OR coddistribuitor LIKE "& $filtrare1 & " OR datasiora LIKE "& $filtrare1 & " OR locatie LIKE "& $filtrare1 & "" $aOk = _EzMySql_GetTable2d($interogare) $error = @error If Not IsArray($aOk) Then MsgBox(0, $sMySqlStatement & " error", $error) _GUICtrlListView_SetItemCount($lista, 5000) ; One column load ;$iTimer = TimerInit() _GUICtrlListView_AddArray($lista, $aOk) ;MsgBox($MB_SYSTEMMODAL, "Information", "Load time: " & TimerDiff($iTimer) / 1000 & " seconds") Sleep (50) _GUICtrlListView_DeleteItem($lista, 0) ; sterge primu element (colum header) _EzMySql_Close() _EzMySql_ShutDown() Case $iesirenrb $ab = _GUICtrlListView_GetItemTextArray($lista,-1) Database_start() ;$ab[1] este id-ul(cui-ul) la randul selectat ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; $iesirenrx = GUICtrlRead($iesirenr) $iesirenr1 = "'" & $iesirenrx & "'" $iesire1st = "UPDATE materiale SET cantitate=cantitate-"&$iesirenr1&" WHERE id= "& $ab[1] &"" If Not _EzMySql_Exec($iesire1st) Then MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf GUICtrlSetData($iesirenr,"") ;Refresh la lista $filtrarex = GUICtrlRead($filtrare) $filtrare1 = "'%" & $filtrarex & "%'" _GUICtrlListView_DeleteAllItems($lista) ;sterge toate itemele din lista Database_start() $sMySqlStatement ="yg" $interogare = "SELECT `id`, `denumire`, `tip`, `producator`, `codproducator`, `distribuitor`, `coddistribuitor`, `codsap`, `codbm`, `locatie`, `cantitate`, `stocminim`, `um`, `datasiora`, `autor` FROM `materiale` WHERE denumire LIKE "& $filtrare1 & " OR tip LIKE "& $filtrare1 & " OR producator LIKE "& $filtrare1 & "OR id LIKE "& $filtrare1 & " OR autor LIKE "& $filtrare1 & "OR codproducator LIKE "& $filtrare1 & " OR distribuitor LIKE "& $filtrare1 & "OR coddistribuitor LIKE "& $filtrare1 & " OR datasiora LIKE "& $filtrare1 & " OR locatie LIKE "& $filtrare1 & "" $aOk = _EzMySql_GetTable2d($interogare) $error = @error If Not IsArray($aOk) Then MsgBox(0, $sMySqlStatement & " error", $error) _GUICtrlListView_SetItemCount($lista, 5000) ; One column load ;$iTimer = TimerInit() _GUICtrlListView_AddArray($lista, $aOk) ;MsgBox($MB_SYSTEMMODAL, "Information", "Load time: " & TimerDiff($iTimer) / 1000 & " seconds") Sleep (50) _GUICtrlListView_DeleteItem($lista, 0) ; sterge primu element (colum header) _EzMySql_Close() _EzMySql_ShutDown() Case $detaliib $ab = _GUICtrlListView_GetItemTextArray($lista,-1) Database_start() $interogare = "SELECT `detalii` FROM `materiale` WHERE id= "& $ab[1] &"" $ainterogare = _EzMySql_GetTable2d($interogare) $error = @error If Not IsArray($ainterogare) Then MsgBox(0, $sMySqlStatement & " error", $error) MsgBox (0, "Detalii Material:", $ainterogare[1][0]) _EzMySql_Close() _EzMySql_ShutDown() Case $sterge $ab = _GUICtrlListView_GetItemTextArray($lista,-1) Database_start() $stergeinfo = MsgBox (1, 'Confirmare stergere', 'Sunteti sigur ca doriti sa stergeti pozitia cu denumirea: ' & $ab[2] & '?' ) if $stergeinfo = 2 then beep (1500, 150) Else $interogare = "DELETE FROM `materiale` WHERE id= "& $ab[1]&"" If Not _EzMySql_Exec($interogare) Then MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf beep (2500, 100) EndIf ;Refresh la lista $filtrarex = GUICtrlRead($filtrare) $filtrare1 = "'%" & $filtrarex & "%'" _GUICtrlListView_DeleteAllItems($lista) ;sterge toate itemele din lista Database_start() $sMySqlStatement ="yg" $interogare = "SELECT `id`, `denumire`, `tip`, `producator`, `codproducator`, `distribuitor`, `coddistribuitor`, `codsap`, `codbm`, `locatie`, `cantitate`, `stocminim`, `um`, `datasiora`, `autor` FROM `materiale` WHERE denumire LIKE "& $filtrare1 & " OR tip LIKE "& $filtrare1 & " OR producator LIKE "& $filtrare1 & "OR id LIKE "& $filtrare1 & " OR autor LIKE "& $filtrare1 & "OR codproducator LIKE "& $filtrare1 & " OR distribuitor LIKE "& $filtrare1 & "OR coddistribuitor LIKE "& $filtrare1 & " OR datasiora LIKE "& $filtrare1 & " OR locatie LIKE "& $filtrare1 & "" $aOk = _EzMySql_GetTable2d($interogare) $error = @error If Not IsArray($aOk) Then MsgBox(0, $sMySqlStatement & " error", $error) _GUICtrlListView_SetItemCount($lista, 5000) ; One column load ;$iTimer = TimerInit() _GUICtrlListView_AddArray($lista, $aOk) ;MsgBox($MB_SYSTEMMODAL, "Information", "Load time: " & TimerDiff($iTimer) / 1000 & " seconds") Sleep (50) _GUICtrlListView_DeleteItem($lista, 0) ; sterge primu element (colum header) _EzMySql_Close() _EzMySql_ShutDown() Case $intrare1 $ab = _GUICtrlListView_GetItemTextArray($lista,-1) Database_start() ;$ab[1] este id-ul(cui-ul) la randul selectat $iesire1st = "UPDATE materiale SET cantitate=cantitate+1 WHERE id= "& $ab[1] &"" If Not _EzMySql_Exec($iesire1st) Then MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf ;Refresh la lista $filtrarex = GUICtrlRead($filtrare) $filtrare1 = "'%" & $filtrarex & "%'" _GUICtrlListView_DeleteAllItems($lista) ;sterge toate itemele din lista Database_start() $sMySqlStatement ="yg" $interogare = "SELECT `id`, `denumire`, `tip`, `producator`, `codproducator`, `distribuitor`, `coddistribuitor`, `codsap`, `codbm`, `locatie`, `cantitate`, `stocminim`, `um`, `datasiora`, `autor` FROM `materiale` WHERE denumire LIKE "& $filtrare1 & " OR tip LIKE "& $filtrare1 & " OR producator LIKE "& $filtrare1 & "OR id LIKE "& $filtrare1 & " OR autor LIKE "& $filtrare1 & "OR codproducator LIKE "& $filtrare1 & " OR distribuitor LIKE "& $filtrare1 & "OR coddistribuitor LIKE "& $filtrare1 & " OR datasiora LIKE "& $filtrare1 & " OR locatie LIKE "& $filtrare1 & "" $aOk = _EzMySql_GetTable2d($interogare) $error = @error If Not IsArray($aOk) Then MsgBox(0, $sMySqlStatement & " error", $error) _GUICtrlListView_SetItemCount($lista, 5000) ; One column load ;$iTimer = TimerInit() _GUICtrlListView_AddArray($lista, $aOk) ;MsgBox($MB_SYSTEMMODAL, "Information", "Load time: " & TimerDiff($iTimer) / 1000 & " seconds") Sleep (50) _GUICtrlListView_DeleteItem($lista, 0) ; sterge primu element (colum header) _EzMySql_Close() _EzMySql_ShutDown() Case $intrarenrb $ab = _GUICtrlListView_GetItemTextArray($lista,-1) Database_start() ;$ab[1] este id-ul(cui-ul) la randul selectat ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; $intrarenrx = GUICtrlRead($intrarenr) $intrarenr1 = "'" & $intrarenrx & "'" $iesire1st = "UPDATE materiale SET cantitate=cantitate+"&$intrarenr1&" WHERE id= "& $ab[1] &"" If Not _EzMySql_Exec($iesire1st) Then MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf GUICtrlSetData($intrarenr,"") ;Refresh la lista $filtrarex = GUICtrlRead($filtrare) $filtrare1 = "'%" & $filtrarex & "%'" _GUICtrlListView_DeleteAllItems($lista) ;sterge toate itemele din lista Database_start() $sMySqlStatement ="yg" $interogare = "SELECT `id`, `denumire`, `tip`, `producator`, `codproducator`, `distribuitor`, `coddistribuitor`, `codsap`, `codbm`, `locatie`, `cantitate`, `stocminim`, `um`, `datasiora`, `autor` FROM `materiale` WHERE denumire LIKE "& $filtrare1 & " OR tip LIKE "& $filtrare1 & " OR producator LIKE "& $filtrare1 & "OR id LIKE "& $filtrare1 & " OR autor LIKE "& $filtrare1 & "OR codproducator LIKE "& $filtrare1 & " OR distribuitor LIKE "& $filtrare1 & "OR coddistribuitor LIKE "& $filtrare1 & " OR datasiora LIKE "& $filtrare1 & " OR locatie LIKE "& $filtrare1 & "" $aOk = _EzMySql_GetTable2d($interogare) $error = @error If Not IsArray($aOk) Then MsgBox(0, $sMySqlStatement & " error", $error) _GUICtrlListView_SetItemCount($lista, 5000) ; One column load ;$iTimer = TimerInit() _GUICtrlListView_AddArray($lista, $aOk) ;MsgBox($MB_SYSTEMMODAL, "Information", "Load time: " & TimerDiff($iTimer) / 1000 & " seconds") Sleep (50) _GUICtrlListView_DeleteItem($lista, 0) ; sterge primu element (colum header) _EzMySql_Close() _EzMySql_ShutDown() Case $t_trimite Database_start() $t_categoriex = GUICtrlRead($t_categorie) $t_categorie1 = "'" & $t_categoriex & "'" ; adauga ' la denumire ca sa fie bine interpretat de mysql query $t_titlu1 = "'" & GUICtrlRead($t_titlu) & "'" ; adauga ' la denumire ca sa fie bine interpretat de mysql query $t_prioritate1 = "'" & GUICtrlRead($t_prioritate) & "'" $t_descriere1 = "'" & GUICtrlRead($t_descriere) & "'" $t_comanda1 = "'" & GUICtrlRead($t_comanda) & "'" $t_material1 = "'" & GUICtrlRead($t_material) & "'" $t_echipament1 = "'" & GUICtrlRead($t_echipament) & "'" $t_autor1 = "'" & GUICtrlRead($t_autor) & "'" $t_responsabil1 = "'" & GUICtrlRead($t_responsabil) & "'" $t_termen1 = "'" & GUICtrlRead($t_termen) & "'" $t_data1 = "'" & GUICtrlRead($t_datasiora) & "'" $t_controlid ="0" $t_controlid1= "'" & GUICtrlRead($t_controlid) & "'" ;$t_validare = MsgBox (1, "Trimite ticket nou", "Previzualizare" & @CRLF & @CRLF & "TITLU: " & $t_titlu & @CRLF & "PRIORITATE: " & $t_prioritate & @CRLF & "ECHIPAMENT: " & $t_echipament ) ;if $t_validare = 2 then ;beep (1500, 150) ;Else $sMySqlStatement = "INSERT INTO `tichete` (`id`, `categorie`, `titlu`, `prioritate`, `descriere`, `comanda`, `material`, `echipament`, `autor_ticket`, `responsabil`, `termen`, `data_creare`, `control_id`) VALUES (NULL, " & $t_categorie1 & ", " & $t_titlu1 & ", " & $t_prioritate1 & ", " & $t_descriere1 & ", " & $t_comanda1 & ", " & $t_material1 & ", " & $t_echipament1 & ", " & $t_autor1 & ", " & $t_responsabil1 & ", " & $t_termen1 & ", " & $t_data1 & ", " & $t_controlid1 & ");" ; , `prioritate`, `descriere`, `comanda`, `material`, `echipament`, `autor_ticket`, `responsabil`, `termen`, `data_creare` If Not _EzMySql_Exec($sMySqlStatement) Then MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf beep (2500, 100) _EzMySql_Close() _EzMySql_ShutDown() ;EndIf Case $detaliiticket guiticket() Case $refresht Database_start() _GUICtrlListView_DeleteAllItems($listatickete) ;sterge toate itemele din lista $sMySqlStatement ="yg" $interogare = "SELECT id, categorie, prioritate,status,titlu,echipament,responsabil,data_creare,autor_ticket FROM `tichete` WHERE control_id<2 ORDER BY `prioritate` ASC " $aOk = _EzMySql_GetTable2d($interogare) $error = @error If Not IsArray($aOk) Then MsgBox(0, $sMySqlStatement & " error", $error) _GUICtrlListView_SetItemCount($listatickete, 5000) ; One column load $iTimer = TimerInit() _GUICtrlListView_AddArray($listatickete, $aOk) ;MsgBox($MB_SYSTEMMODAL, "Information", "Load time: " & TimerDiff($iTimer) / 1000 & " seconds") Sleep (50) _GUICtrlListView_DeleteItem($listatickete, 0) ; sterge primu element (colum header) _EzMySql_Close() _EzMySql_ShutDown() Case $keygo beep (2500, 100) Database_start() $key1x = "'%" & GUICtrlRead($key1) & "%'" $key2x = "'%" & GUICtrlRead($key2) & "%'" $key3x = "'%" & GUICtrlRead($key3) & "%'" _GUICtrlListView_DeleteAllItems($listatickete) ;sterge toate itemele din lista $sMySqlStatement ="yg" $interogare = "SELECT * FROM `tichete` WHERE id LIKE "& $key1x & " OR status LIKE "& $key1x & " OR categorie LIKE "& $key1x & "OR titlu LIKE "& $key1x & "" $aOk = _EzMySql_GetTable2d($interogare) $error = @error If Not IsArray($aOk) Then MsgBox(0, $sMySqlStatement & " error", $error) _ArrayDisplay($aOk, "1D display") _EzMySql_Close() _EzMySql_ShutDown() Case Else GUICtrlSetData($datasiora, _Now()) GUICtrlSetData($t_datasiora, _Now()) EndSwitch WEnd Any help please? Link to comment Share on other sites More sharing options...
rony2006 Posted May 3, 2016 Author Share Posted May 3, 2016 Until now I have in mind 2 solution at my problem but they are to complicated: (this is the less complicated) Solution 1: Select a table from my db based on Key 1 Copy the resulted table in another db Apply filter by Key 2 on the new table Copy the resulted table in another db Apply filter by Key 3 on the last table Get the last table in 2d array and then in listview Delete last 2 tables Link to comment Share on other sites More sharing options...
jchd Posted May 3, 2016 Share Posted May 3, 2016 Don't make your life more complicated than it really needs to be. Select this, that, something_else from mytable where echipament like 'AGB8' and status = 'Rezolvat' and Responsabil like 'oricine' This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
rony2006 Posted May 4, 2016 Author Share Posted May 4, 2016 Can you be more specific please? Most of the times $filtrare2 and $filtrare 3 will be empty (no value writed). Link to comment Share on other sites More sharing options...
jchd Posted May 4, 2016 Share Posted May 4, 2016 Say your filters apply to col1, col2 and col3, respectively. Then a suitable SQL could be built like this: Local $filtrare1 = 'abc', $filtrare2 = '123ZZZ', $filtrare3 = 'gshbo' Local $EscFiltrare1 = StringReplace($filtrare1, "'", "''") Local $EscFiltrare2 = StringReplace($filtrare2, "'", "''") Local $EscFiltrare3 = StringReplace($filtrare3, "'", "''") Local $SQL = "select the_list_of columns_you_want_retrieved from yourtable where " & _ "('" & $EscFiltrare1 & "' = '' or col1 = '" & $EscFiltrare1 & "') and " & _ "('" & $EscFiltrare2 & "' = '' or col2 = '" & $EscFiltrare2 & "') and " & _ "('" & $EscFiltrare3 & "' = '' or col3 = '" & $EscFiltrare3 & "') " & _ "order by list_of_columns_for_sorting_the_resultset_smartly;" ConsoleWrite($SQL & @LF) Look at the display produced: this way an empty filter is not a problem. No penalty is incurred either since all decent SQL engines use boolean evaluation short-circuit. This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta. SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt) Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now