yucatan Posted January 19, 2011 Share Posted January 19, 2011 Hello guys i have some mysql problems. this is my code expandcollapse popup#include <GUIConstantsEx.au3> #include <WindowsConstants.au3> #include <GuiListView.au3> #include <Date.au3> #include <Array.au3> #include <TabConstants.au3> #include <EditConstants.au3> #include <UpdownConstants.au3> #Include <GuiTab.au3> #Include <GuiComboBox.au3> #include <Misc.au3> #include<EzMySql.au3> Opt("GUICloseOnESC", 0) If Not _EzMySql_Startup() Then MsgBox(0, "Error Starting MySql", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf If Not _EzMySql_Open("localhost", "root", "", "", "3306") Then MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf If Not _EzMySql_Exec("CREATE DATABASE IF NOT EXISTS planning") Then MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf If Not _EzMySql_SelectDB("planning") Then MsgBox(0, "Error setting Database to use", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf Global $fClicked = 0, $fWeek_0 = False, $fWeek_53 = False Global $hListView = 0, $hEdit_Combo = 0, $aLV_Click_Info Global $iListView_ID, $sItemText Global $aLV_Handles[7], $aDayLabels[7] Global $aWeeks[54][8] Global $sYear = @YEAR Global $sdate = $sYear & "/01/01" Global $buswidth = 30, $monteurwidth = 60, $klantwidth = 155 global $listviews[7],$rows[1],$aok global $maandag[1],$dinsdag[1],$woensdag[1],$donderdag[1],$vrijdag[1],$zaterdag[1],$maandagdata[1],$dinsdagdata[1],$woensdagdata[1],$donderdagdata[1],$vrijdagdata[1] $clickedweek = _WeekNumberISO() ; Open DLL for _IsPressed Global $dll = DllOpen("user32.dll") Global $hGUI = GUICreate("Test", 900, 940) $hTab = GUICtrlCreateTab(10, 10, 890, 940) $hTab_0 = GUICtrlCreateTabItem("Week 0") For $i = 1 To 53 GUICtrlCreateTabItem("Week " & $i) Next GUICtrlCreateTabItem("") $combo = GUICtrlCreateCombo("", 500, 45,120,50, $CBS_DROPDOWNLIST) GUICtrlSetData(-1, "Kopieer vorige week|Laad Standaart", "") ; add other item snd set a new default GUICtrlSetState(_WeekNumberISO() + $hTab_0, $GUI_SHOW) $hInput = GUICtrlCreateInput($sYear, 420, 45, 50, 20, $ES_READONLY) $hUpDown = GUICtrlCreateUpdown($hInput, BitOR($GUI_SS_DEFAULT_UPDOWN, $UDS_WRAP, $UDS_NOTHOUSANDS)) GUICtrlSetLimit($hUpDown, $sYear + 5, $sYear - 5) For $i = 1 To 3 For $j = 0 To 1 $iX = 15 + (290 * ($i - 1)) $iY = 90 + (440 * $j) $aDayLabels[(3 * $j) + $i] = GUICtrlCreateLabel("", $iX + 40, $iY - 20, 150, 25) $hListView = _GUICtrlListView_Create($hGUI, "Bus", $iX, $iY, 285, 407, BitOR($LVS_REPORT, $LVS_SINGLESEL, $WS_BORDER), $LVS_EX_FULLROWSELECT) _ArrayAdd($listviews,$hListView) _GUICtrlListView_SetExtendedListViewStyle($hListView, $LVS_EX_GRIDLINES) ; Set grid lines $aLV_Handles[$i + ($j * 3)] = $hListView _GUICtrlListView_AddColumn($hListView, "Monteur") _GUICtrlListView_AddColumn($hListView, "Klant en Project") _GUICtrlListView_AddColumn($hListView, "Wvb") ControlDisable($hGUI, "", HWnd(_GUICtrlListView_GetHeader($hListView))) _GUICtrlListView_SetColumnWidth($hListView, 0, $buswidth) _GUICtrlListView_SetColumnWidth($hListView, 1, $monteurwidth) _GUICtrlListView_SetColumnWidth($hListView, 2, $klantwidth) _GUICtrlListView_SetColumnWidth($hListView, 3, $LVSCW_AUTOSIZE_USEHEADER) For $k = 1 To 27 _GUICtrlListView_AddItem($hListView, "") _GUICtrlListView_AddSubItem($hListView, $k - 1, "", 1) _GUICtrlListView_AddSubItem($hListView, $k - 1, "", 2) Next Next Next _Calculate() _refresh("week"&_WeekNumberISO()) GUISetState() GUIRegisterMsg($WM_NOTIFY, "WM_NOTIFY") $hAccel_Enter = GUICtrlCreateDummy() Dim $aAccelKeys[1][2]=[["{ENTER}", $hAccel_Enter]] While 1 Switch GUIGetMsg() Case $combo ConsoleWrite(GUICtrlRead($combo)) Case $GUI_EVENT_CLOSE DllClose($dll) Exit Case $hTab ; Do not display weeks 0 and 53 if not used If GUICtrlRead($hTab) = 0 And Not $fWeek_0 Then _GUICtrlTab_ClickTab($hTab, 1) If GUICtrlRead($hTab) = 53 And Not $fWeek_53 Then _GUICtrlTab_ClickTab($hTab, 52) $clickedweek = GUICtrlRead($hTab) For $i = 1 To 6 GUICtrlSetData($aDayLabels[$i], $aWeeks[$clickedweek][$i]) Next ; Clear existing items For $j = 1 To 6 For $i = 0 To 26 ; Because you have created 27 items initially _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "") _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "", 1) _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "", 2) _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "", 3) Next Next ; Fill with new data _refresh("week" & $clickedweek) Case $hUpDown _Calculate() EndSwitch ; If an item was double clicked If $fClicked Then ; Get ListView details $hListView = $aLV_Handles[$fClicked] $iListView_ID = $fClicked ; Reset flag $fClicked = 0 ; Get details item clicked $aLV_Click_Info = _GUICtrlListView_SubItemHitTest($hListView) If $aLV_Click_Info[0] <> -1 Then ; Delete any existing combo GUICtrlDelete($hEdit_Combo) ; Get current text $sItemText = _GUICtrlListView_GetItemText($hListView, $aLV_Click_Info[0], $aLV_Click_Info[1]) ; Get position Local $aRect = _GUICtrlListView_GetSubItemRect($hListView, $aLV_Click_Info[0], $aLV_Click_Info[1]) Local $aPos = WinGetPos($hListView) Local $tPoint = DllStructCreate("int X;int Y") DllStructSetData($tPoint, "X", $aPos[0]) DllStructSetData($tPoint, "Y", $aPos[1]) _WinAPI_ScreenToClient($hGUI, $tPoint) Local $iEdit_X = DllStructGetData($tPoint, "X") + $aRect[0] Local $iEdit_Y = DllStructGetData($tPoint, "Y") + $aRect[1] ; Get column width Local $iWidth = _GUICtrlListView_GetColumnWidth($hListView, $aLV_Click_Info[1]) ; Create edit combo $hEdit_Combo = GUICtrlCreateCombo("", $iEdit_X, $iEdit_Y, $iWidth, 20) ; Set combo values depending on column clicked Switch $aLV_Click_Info[1] ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2 Case 0 $sComboList = "1 Bus|2 Bus|3 Bus" If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText _GUICtrlComboBox_LimitText($hEdit_Combo, 2) Case 1 $sComboList = "Monteur 1|Monteur 2|Monteur 3" If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText Case 2 $sComboList = "Klant 1|Klant 2|Klant 3" If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText Case 3 $sComboList = "Wvb 1|Wvb 2|Wvb 3" If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText _GUICtrlComboBox_LimitText($hEdit_Combo, 3) EndSwitch ; Set values into combo GUICtrlSetData($hEdit_Combo, $sComboList, $sItemText) GUICtrlSetState($hEdit_Combo, BitOR($GUI_FOCUS, $GUI_ONTOP)) ; Get handle of combo edit control $hComboEdit = _WinAPI_GetFocus() ; Set flag for Enter pressed $iLoopCode = 0 ; Set flag for combo opened <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2 $fCombo_Opened = False ; Loop while the combo has focus While _WinAPI_GetFocus() = $hComboEdit ; If combo has been opened, check if closed again ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2 If $fCombo_Opened Then ; If combo is closed again If _GUICtrlComboBox_GetDroppedState($hEdit_Combo) = False Then $iLoopCode = 1 ExitLoop EndIf Else ; Check if opened and set flag If _GUICtrlComboBox_GetDroppedState($hEdit_Combo) Then $fCombo_Opened = True EndIf ; If Enter Pressed ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2 If _IsPressed("0D", $dll) Then $iLoopCode = 1 ExitLoop EndIf ; If ESC pressed If _IsPressed("1B", $dll) Then $iLoopCode = -1 ExitLoop EndIf ; Save CPU and prevent tab selection <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 1 Switch GUIGetMsg() Case $hTab _GUICtrlTab_ClickTab($hTab, $clickedweek) ;Case $GUI_EVENT_CLOSE ; DllClose($dll) ; Exit EndSwitch WEnd ; Read Combo $sText = GUICtrlRead($hEdit_Combo) ; Check if content changed manually If Not $iLoopCode And $sText <> $sItemText Then $iLoopCode = 1 ; <<<<<<<<<<<<<<<<<<<<< 2 ; Edit ListView if required If $iLoopCode = 1 Then _GUICtrlListView_SetItemText($hListView, $aLV_Click_Info[0], $sText, $aLV_Click_Info[1]) $week = "week"&GUICtrlRead($hTab) if $hListView = $listviews[7] then $dagread = "Maandag" if $hListView = $listviews[9] then $dagread = "Dinsdag" if $hListView = $listviews[11] then $dagread = "Woensdag" if $hListView = $listviews[8] then $dagread = "Donderdag" if $hListView = $listviews[10] then $dagread = "Vrijdag" if $hListView = $listviews[12] then $dagread = "Zaterdag" if $aLV_Click_Info[1] = 3 then $fields = "Wvb" if $aLV_Click_Info[1] = 2 then $fields = "Klantenproject" if $aLV_Click_Info[1] = 1 then $fields = "Naam" if $aLV_Click_Info[1] = 0 then $fields = "Bus" $value1 = "" $value2 = "" $value3 = "" $value4 = "" if $aLV_Click_Info[1] + 1 = 1 then $value1 = $sText if $aLV_Click_Info[1] + 1 = 2 then $value2 = $sText if $aLV_Click_Info[1] + 1 = 3 then $value3 = $sText if $aLV_Click_Info[1] + 1 = 4 then $value4 = $sText ConsoleWrite($aLV_Click_Info[0] + 1 & " "& $aLV_Click_Info[1] + 1 &@CRLF) Local $sMySqlStatement = "" $sMySqlStatement &= "INSERT INTO "&$week&" (Dag,Bus,Naam,Klantenproject,Wvb) VALUES (" & "'" & $dagread & "'," & "'" & $value1 & "'," & "'" & $value2 & "'," & "'" & $value3 & "'," & "'" & $value4 & "');" If Not _EzMySql_Exec($sMySqlStatement) Then MsgBox(0, "Error inserting data to Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf $sMySqlStatement = "" ConsoleWrite( _EzMySql_InsertID()&@CRLF) EndIf ; Delete combo GUICtrlDelete($hEdit_Combo) $hEdit_Combo = 0 EndIf EndIf WEnd Func WM_NOTIFY($hWnd, $iMsg, $wParam, $lParam) #forceref $hWnd, $iMsg, $wParam Local $hWndFrom, $iCode, $tNMHDR $tNMHDR = DllStructCreate($tagNMHDR, $lParam) $hWndFrom = HWnd(DllStructGetData($tNMHDR, "hWndFrom")) $iCode = DllStructGetData($tNMHDR, "Code") For $i = 1 To 6 If $aLV_Handles[$i] = $hWndFrom Then Switch $iCode Case $NM_CLICK $fClicked = $i EndSwitch ExitLoop EndIf Next Return $GUI_RUNDEFMSG EndFunc ;==>WM_NOTIFY Func _Calculate() ; Clear array Global $aWeeks[54][8] ; Set local variables Local $asDatePart, $iTimePart Local $aDays[8] = [7, "Maandag", "Dinsdag", "Woensdag", "Donderdag", "Vrijdag", "Zaterdag", "Zondag"] Local $aMonths[13] = [12, "Januari", "Februari", "Maart", "April", "Mei", "Juni", "Juli", "Augustus", "September", "October", "November", "December"] ; Read required year and week $sYear = GUICtrlRead($hInput) $clickedweek = GUICtrlRead($hTab) ; Set first day $sdate = $sYear & "/01/01" ; Set flag to indicate initial week $fStart = True ; Set flags to indicate 0/53 week empty $fWeek_0 = False $fWeek_53 = False While 1 ; Split date _DateTimeSplit($sdate, $asDatePart, $iTimePart) ; Exit if past end required year If $asDatePart[1] > $sYear Then ExitLoop ; Get week number $iWeek = _WeekNumberISO($sYear, $asDatePart[2], $asDatePart[3]) ; Adjust to cover final part of previous Week 52 if required If $fStart Then If $iWeek >= 52 Then $iWeek = 0 Else If $iWeek = 1 Then $iWeek = 53 EndIf ; Reset flag if past initial week If $iWeek = 2 Then $fStart = False ; Get day of week and adjust for Sunday $iIndex = _DateToDayOfWeek($sYear, $asDatePart[2], $asDatePart[3]) If $iIndex = 1 Then $iIndex = 8 ; Adjust for final week if needed If Not $fStart And $iWeek = 53 Then $iWeek = 52 EndIf ; Fill array ConsoleWrite($aDays[$iIndex - 1] & " " & $asDatePart[3] & " " & $aMonths[$asDatePart[2]]&@CRLF) $aWeeks[$iWeek][$iIndex - 1] = $aDays[$iIndex - 1] & " " & $asDatePart[3] & " " & $aMonths[$asDatePart[2]] ; Move to next day $sdate = _DateAdd("d", 1, $sdate) ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< WEnd ; Check if weeks 0 and 53 are used For $i = 1 To 6 If $aWeeks[0][$i] <> "" Then $fWeek_0 = True If $aWeeks[53][$i] <> "" Then $fWeek_53 = True Next ; If not then do not display them if selected If GUICtrlRead($hTab) = 0 And Not $fWeek_0 Then _GUICtrlTab_ClickTab($hTab, 1) If GUICtrlRead($hTab) = 53 And Not $fWeek_53 Then _GUICtrlTab_ClickTab($hTab, 52) $clickedweek = GUICtrlRead($hTab) For $i = 1 To 6 ;ConsoleWrite($aDayLabels[$i] & " - " & $aWeeks[$clickedweek][$i] & @CRLF) GUICtrlSetData($aDayLabels[$i], $aWeeks[$clickedweek][$i]) Next EndFunc ;==>_Calculate func _refresh($week) ;$arraysize = UBound($aok) ConsoleWrite($aok&@CRLF) $aOk = _EzMySql_GetTable2d("SELECT * FROM "&$week&";") $error = @error If Not IsArray($aOk) Then $create = "CREATE TABLE IF NOT EXISTS "&$week&" (" & _ "RowID INT NOT NULL AUTO_INCREMENT," & _ "Dag TEXT NOT NULL ," & _ "Bus TEXT NOT NULL ," & _ "Naam TEXT NOT NULL ," & _ "Klantenproject TEXT NOT NULL ," & _ "Wvb TEXT NOT NULL ," & _ "PRIMARY KEY (`RowID`) ," & _ "UNIQUE INDEX RowID_UNIQUE (`RowID` ASC) );" If Not _EzMySql_Exec($create) Then MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) _refresh($week) EndIf EndIf ;_ArrayDisplay($aok, "$avArray set manually 1D") $j = 0 for $i =0 to UBound($aok) -1 if $aok[$i][1] = "Maandag" then _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][2], 0) _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][3], 1) _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][4], 2) _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][5], 3) $j+=1 EndIf Next $j = 0 for $i =0 to UBound($aok) -1 if $aok[$i][1] = "Dinsdag" then _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][2], 0) _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][3], 1) _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][4], 2) _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][5], 3) $j+=1 EndIf Next $j = 0 for $i =0 to UBound($aok) -1 if $aok[$i][1] = "Woensdag" then _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][2], 0) _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][3], 1) _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][4], 2) _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][5], 3) $j+=1 EndIf Next $j = 0 for $i =0 to UBound($aok) -1 if $aok[$i][1] = "Donderdag" then _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][2], 0) _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][3], 1) _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][4], 2) _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][5], 3) $j+=1 EndIf Next $j = 0 for $i =0 to UBound($aok) -1 if $aok[$i][1] = "Vrijdag" then _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][2], 0) _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][3], 1) _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][4], 2) _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][5], 3) $j+=1 EndIf Next $j = 0 for $i =0 to UBound($aok) -1 if $aok[$i][1] = "Zaterdag" then _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][2], 0) _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][3], 1) _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][4], 2) _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][5], 3) $j+=1 EndIf Next ;_ArrayDisplay($maandagdata, "$avArray set manually 1D") EndFunc if i get a new week that is emty. if i fill something in in the field monteur. but the rest of the rule is emty. then i only add's the value i have added to mysql. as u proberly know mysql used rowid's if i fill in a bus number in the same rule. then it need to update the exsisting row instead of adding a totaly new row. there are two ways i can think of to fix this issue. one i log the rowid's if a new value is added. so that if in the same rule but in another field someting is added i can update the exsisting row. two i only send a mysql query if the rule is compleet. so then only then all 4 fields have been filled with something. that only then it send's a mysql query. if we chose for the seconds way(wicht i think is the most easy one) then we still have a problem with modifing. if i edit a field.. i need to know the rowid that should be updated... so thats the issue i have now. but i dont know if i should ask this to u. maby i need to make topic in general help and support. instead of in the gui help and support. What ur advice to me now? Happy to hear from u. Greetz Yucatan Link to comment Share on other sites More sharing options...
jchd Posted January 19, 2011 Share Posted January 19, 2011 Your question doesn't seem to be AutoIt related at all and should probably find an answer faster in some generic SQL forum. Now, it's unclear to me what schema you have and what your actual problem is. Do you create one table per week? If so that doesn't look a sound SQL schema, unless I misunderstand something big. Hint: try to separate SQL issues from AutoIt code as the whole stuff takes too much time to dissect. Try (here, once your started the topic or elsewhere) to expose a pure SQL pseudocode, eg: CREATE TABLE .... INSERT INTO ... SELECT .... Expected result: --- --- --- Actual result: /// /// /// 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...
yucatan Posted January 20, 2011 Author Share Posted January 20, 2011 my question is autoit related.i know how MySQl works.i just dont know what is the most efficent way to use it in autoit.i shall try to explane it.as u see i have a gui with 6 listviews with 4 columsas u understand i wanne use that listview to insert data into mysql.the problem is:as u look at the code at this time it inserts data into the mysql table at the moment that the editing is done.at that point is adds one value to the mysql.that works fine.but if i try to insert data into a exsisting row.it goes wrong then it just create a new row.if u know some mysql structure then u can see it in my code.expandcollapse popup#include <GUIConstantsEx.au3> #include <WindowsConstants.au3> #include <GuiListView.au3> #include <Date.au3> #include <Array.au3> #include <TabConstants.au3> #include <EditConstants.au3> #include <UpdownConstants.au3> #Include <GuiTab.au3> #Include <GuiComboBox.au3> #include <Misc.au3> #include<EzMySql.au3> Opt("GUICloseOnESC", 0) If Not _EzMySql_Startup() Then MsgBox(0, "Error Starting MySql", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf If Not _EzMySql_Open("localhost", "root", "", "", "3306") Then MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf If Not _EzMySql_Exec("CREATE DATABASE IF NOT EXISTS planning") Then MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf If Not _EzMySql_SelectDB("planning") Then MsgBox(0, "Error setting Database to use", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf Global $fClicked = 0, $fWeek_0 = False, $fWeek_53 = False Global $hListView = 0, $hEdit_Combo = 0, $aLV_Click_Info Global $iListView_ID, $sItemText Global $aLV_Handles[7], $aDayLabels[7] Global $aWeeks[54][8] Global $sYear = @YEAR Global $sdate = $sYear & "/01/01" Global $buswidth = 30, $monteurwidth = 60, $klantwidth = 155 global $listviews[7],$rows[1],$aok global $maandag[1],$dinsdag[1],$woensdag[1],$donderdag[1],$vrijdag[1],$zaterdag[1],$maandagdata[1],$dinsdagdata[1],$woensdagdata[1],$donderdagdata[1],$vrijdagdata[1] $clickedweek = _WeekNumberISO() ; Open DLL for _IsPressed Global $dll = DllOpen("user32.dll") Global $hGUI = GUICreate("Test", 900, 940) $hTab = GUICtrlCreateTab(10, 10, 890, 940) $hTab_0 = GUICtrlCreateTabItem("Week 0") For $i = 1 To 53 GUICtrlCreateTabItem("Week " & $i) Next GUICtrlCreateTabItem("") $combo = GUICtrlCreateCombo("", 500, 45,120,50, $CBS_DROPDOWNLIST) GUICtrlSetData(-1, "Kopieer vorige week|Laad Standaart", "") ; add other item snd set a new default GUICtrlSetState(_WeekNumberISO() + $hTab_0, $GUI_SHOW) $hInput = GUICtrlCreateInput($sYear, 420, 45, 50, 20, $ES_READONLY) $hUpDown = GUICtrlCreateUpdown($hInput, BitOR($GUI_SS_DEFAULT_UPDOWN, $UDS_WRAP, $UDS_NOTHOUSANDS)) GUICtrlSetLimit($hUpDown, $sYear + 5, $sYear - 5) For $i = 1 To 3 For $j = 0 To 1 $iX = 15 + (290 * ($i - 1)) $iY = 90 + (440 * $j) $aDayLabels[(3 * $j) + $i] = GUICtrlCreateLabel("", $iX + 40, $iY - 20, 150, 25) $hListView = _GUICtrlListView_Create($hGUI, "Bus", $iX, $iY, 285, 407, BitOR($LVS_REPORT, $LVS_SINGLESEL, $WS_BORDER), $LVS_EX_FULLROWSELECT) _ArrayAdd($listviews,$hListView) _GUICtrlListView_SetExtendedListViewStyle($hListView, $LVS_EX_GRIDLINES) ; Set grid lines $aLV_Handles[$i + ($j * 3)] = $hListView _GUICtrlListView_AddColumn($hListView, "Monteur") _GUICtrlListView_AddColumn($hListView, "Klant en Project") _GUICtrlListView_AddColumn($hListView, "Wvb") ControlDisable($hGUI, "", HWnd(_GUICtrlListView_GetHeader($hListView))) _GUICtrlListView_SetColumnWidth($hListView, 0, $buswidth) _GUICtrlListView_SetColumnWidth($hListView, 1, $monteurwidth) _GUICtrlListView_SetColumnWidth($hListView, 2, $klantwidth) _GUICtrlListView_SetColumnWidth($hListView, 3, $LVSCW_AUTOSIZE_USEHEADER) For $k = 1 To 27 _GUICtrlListView_AddItem($hListView, "") _GUICtrlListView_AddSubItem($hListView, $k - 1, "", 1) _GUICtrlListView_AddSubItem($hListView, $k - 1, "", 2) Next Next Next _Calculate() _refresh("week"&_WeekNumberISO()) GUISetState() GUIRegisterMsg($WM_NOTIFY, "WM_NOTIFY") $hAccel_Enter = GUICtrlCreateDummy() Dim $aAccelKeys[1][2]=[["{ENTER}", $hAccel_Enter]] While 1 Switch GUIGetMsg() Case $combo ConsoleWrite(GUICtrlRead($combo)) Case $GUI_EVENT_CLOSE DllClose($dll) Exit Case $hTab ; Do not display weeks 0 and 53 if not used If GUICtrlRead($hTab) = 0 And Not $fWeek_0 Then _GUICtrlTab_ClickTab($hTab, 1) If GUICtrlRead($hTab) = 53 And Not $fWeek_53 Then _GUICtrlTab_ClickTab($hTab, 52) $clickedweek = GUICtrlRead($hTab) For $i = 1 To 6 GUICtrlSetData($aDayLabels[$i], $aWeeks[$clickedweek][$i]) Next ; Clear existing items For $j = 1 To 6 For $i = 0 To 26 ; Because you have created 27 items initially _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "") _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "", 1) _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "", 2) _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "", 3) Next Next ; Fill with new data _refresh("week" & $clickedweek) Case $hUpDown _Calculate() EndSwitch ; If an item was double clicked If $fClicked Then ; Get ListView details $hListView = $aLV_Handles[$fClicked] $iListView_ID = $fClicked ; Reset flag $fClicked = 0 ; Get details item clicked $aLV_Click_Info = _GUICtrlListView_SubItemHitTest($hListView) If $aLV_Click_Info[0] <> -1 Then ; Delete any existing combo GUICtrlDelete($hEdit_Combo) ; Get current text $sItemText = _GUICtrlListView_GetItemText($hListView, $aLV_Click_Info[0], $aLV_Click_Info[1]) ; Get position Local $aRect = _GUICtrlListView_GetSubItemRect($hListView, $aLV_Click_Info[0], $aLV_Click_Info[1]) Local $aPos = WinGetPos($hListView) Local $tPoint = DllStructCreate("int X;int Y") DllStructSetData($tPoint, "X", $aPos[0]) DllStructSetData($tPoint, "Y", $aPos[1]) _WinAPI_ScreenToClient($hGUI, $tPoint) Local $iEdit_X = DllStructGetData($tPoint, "X") + $aRect[0] Local $iEdit_Y = DllStructGetData($tPoint, "Y") + $aRect[1] ; Get column width Local $iWidth = _GUICtrlListView_GetColumnWidth($hListView, $aLV_Click_Info[1]) ; Create edit combo $hEdit_Combo = GUICtrlCreateCombo("", $iEdit_X, $iEdit_Y, $iWidth, 20) ; Set combo values depending on column clicked Switch $aLV_Click_Info[1] ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2 Case 0 $sComboList = "1 Bus|2 Bus|3 Bus" If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText _GUICtrlComboBox_LimitText($hEdit_Combo, 2) Case 1 $sComboList = "Monteur 1|Monteur 2|Monteur 3" If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText Case 2 $sComboList = "Klant 1|Klant 2|Klant 3" If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText Case 3 $sComboList = "Wvb 1|Wvb 2|Wvb 3" If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText _GUICtrlComboBox_LimitText($hEdit_Combo, 3) EndSwitch ; Set values into combo GUICtrlSetData($hEdit_Combo, $sComboList, $sItemText) GUICtrlSetState($hEdit_Combo, BitOR($GUI_FOCUS, $GUI_ONTOP)) ; Get handle of combo edit control $hComboEdit = _WinAPI_GetFocus() ; Set flag for Enter pressed $iLoopCode = 0 ; Set flag for combo opened <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2 $fCombo_Opened = False ; Loop while the combo has focus While _WinAPI_GetFocus() = $hComboEdit ; If combo has been opened, check if closed again ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2 If $fCombo_Opened Then ; If combo is closed again If _GUICtrlComboBox_GetDroppedState($hEdit_Combo) = False Then $iLoopCode = 1 ExitLoop EndIf Else ; Check if opened and set flag If _GUICtrlComboBox_GetDroppedState($hEdit_Combo) Then $fCombo_Opened = True EndIf ; If Enter Pressed ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2 If _IsPressed("0D", $dll) Then $iLoopCode = 1 ExitLoop EndIf ; If ESC pressed If _IsPressed("1B", $dll) Then $iLoopCode = -1 ExitLoop EndIf ; Save CPU and prevent tab selection <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 1 Switch GUIGetMsg() Case $hTab _GUICtrlTab_ClickTab($hTab, $clickedweek) ;Case $GUI_EVENT_CLOSE ; DllClose($dll) ; Exit EndSwitch WEnd ; Read Combo $sText = GUICtrlRead($hEdit_Combo) ; Check if content changed manually If Not $iLoopCode And $sText <> $sItemText Then $iLoopCode = 1 ; <<<<<<<<<<<<<<<<<<<<< 2 ; Edit ListView if required If $iLoopCode = 1 Then _GUICtrlListView_SetItemText($hListView, $aLV_Click_Info[0], $sText, $aLV_Click_Info[1]) $week = "week"&GUICtrlRead($hTab) if $hListView = $listviews[7] then $dagread = "Maandag" if $hListView = $listviews[9] then $dagread = "Dinsdag" if $hListView = $listviews[11] then $dagread = "Woensdag" if $hListView = $listviews[8] then $dagread = "Donderdag" if $hListView = $listviews[10] then $dagread = "Vrijdag" if $hListView = $listviews[12] then $dagread = "Zaterdag" if $aLV_Click_Info[1] = 3 then $fields = "Wvb" if $aLV_Click_Info[1] = 2 then $fields = "Klantenproject" if $aLV_Click_Info[1] = 1 then $fields = "Naam" if $aLV_Click_Info[1] = 0 then $fields = "Bus" $value1 = "" $value2 = "" $value3 = "" $value4 = "" if $aLV_Click_Info[1] + 1 = 1 then $value1 = $sText if $aLV_Click_Info[1] + 1 = 2 then $value2 = $sText if $aLV_Click_Info[1] + 1 = 3 then $value3 = $sText if $aLV_Click_Info[1] + 1 = 4 then $value4 = $sText ConsoleWrite($aLV_Click_Info[0] + 1 & " "& $aLV_Click_Info[1] + 1 &@CRLF) Local $sMySqlStatement = "" $sMySqlStatement &= "INSERT INTO "&$week&" (Dag,Bus,Naam,Klantenproject,Wvb) VALUES (" & "'" & $dagread & "'," & "'" & $value1 & "'," & "'" & $value2 & "'," & "'" & $value3 & "'," & "'" & $value4 & "');" If Not _EzMySql_Exec($sMySqlStatement) Then MsgBox(0, "Error inserting data to Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf $sMySqlStatement = "" ConsoleWrite( _EzMySql_InsertID()&@CRLF) EndIf ; Delete combo GUICtrlDelete($hEdit_Combo) $hEdit_Combo = 0 EndIf EndIf WEnd Func WM_NOTIFY($hWnd, $iMsg, $wParam, $lParam) #forceref $hWnd, $iMsg, $wParam Local $hWndFrom, $iCode, $tNMHDR $tNMHDR = DllStructCreate($tagNMHDR, $lParam) $hWndFrom = HWnd(DllStructGetData($tNMHDR, "hWndFrom")) $iCode = DllStructGetData($tNMHDR, "Code") For $i = 1 To 6 If $aLV_Handles[$i] = $hWndFrom Then Switch $iCode Case $NM_CLICK $fClicked = $i EndSwitch ExitLoop EndIf Next Return $GUI_RUNDEFMSG EndFunc ;==>WM_NOTIFY Func _Calculate() ; Clear array Global $aWeeks[54][8] ; Set local variables Local $asDatePart, $iTimePart Local $aDays[8] = [7, "Maandag", "Dinsdag", "Woensdag", "Donderdag", "Vrijdag", "Zaterdag", "Zondag"] Local $aMonths[13] = [12, "Januari", "Februari", "Maart", "April", "Mei", "Juni", "Juli", "Augustus", "September", "October", "November", "December"] ; Read required year and week $sYear = GUICtrlRead($hInput) $clickedweek = GUICtrlRead($hTab) ; Set first day $sdate = $sYear & "/01/01" ; Set flag to indicate initial week $fStart = True ; Set flags to indicate 0/53 week empty $fWeek_0 = False $fWeek_53 = False While 1 ; Split date _DateTimeSplit($sdate, $asDatePart, $iTimePart) ; Exit if past end required year If $asDatePart[1] > $sYear Then ExitLoop ; Get week number $iWeek = _WeekNumberISO($sYear, $asDatePart[2], $asDatePart[3]) ; Adjust to cover final part of previous Week 52 if required If $fStart Then If $iWeek >= 52 Then $iWeek = 0 Else If $iWeek = 1 Then $iWeek = 53 EndIf ; Reset flag if past initial week If $iWeek = 2 Then $fStart = False ; Get day of week and adjust for Sunday $iIndex = _DateToDayOfWeek($sYear, $asDatePart[2], $asDatePart[3]) If $iIndex = 1 Then $iIndex = 8 ; Adjust for final week if needed If Not $fStart And $iWeek = 53 Then $iWeek = 52 EndIf ; Fill array ConsoleWrite($aDays[$iIndex - 1] & " " & $asDatePart[3] & " " & $aMonths[$asDatePart[2]]&@CRLF) $aWeeks[$iWeek][$iIndex - 1] = $aDays[$iIndex - 1] & " " & $asDatePart[3] & " " & $aMonths[$asDatePart[2]] ; Move to next day $sdate = _DateAdd("d", 1, $sdate) ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< WEnd ; Check if weeks 0 and 53 are used For $i = 1 To 6 If $aWeeks[0][$i] <> "" Then $fWeek_0 = True If $aWeeks[53][$i] <> "" Then $fWeek_53 = True Next ; If not then do not display them if selected If GUICtrlRead($hTab) = 0 And Not $fWeek_0 Then _GUICtrlTab_ClickTab($hTab, 1) If GUICtrlRead($hTab) = 53 And Not $fWeek_53 Then _GUICtrlTab_ClickTab($hTab, 52) $clickedweek = GUICtrlRead($hTab) For $i = 1 To 6 ;ConsoleWrite($aDayLabels[$i] & " - " & $aWeeks[$clickedweek][$i] & @CRLF) GUICtrlSetData($aDayLabels[$i], $aWeeks[$clickedweek][$i]) Next EndFunc ;==>_Calculate func _refresh($week) ;$arraysize = UBound($aok) ConsoleWrite($aok&@CRLF) $aOk = _EzMySql_GetTable2d("SELECT * FROM "&$week&";") $error = @error If Not IsArray($aOk) Then $create = "CREATE TABLE IF NOT EXISTS "&$week&" (" & _ "RowID INT NOT NULL AUTO_INCREMENT," & _ "Dag TEXT NOT NULL ," & _ "Bus TEXT NOT NULL ," & _ "Naam TEXT NOT NULL ," & _ "Klantenproject TEXT NOT NULL ," & _ "Wvb TEXT NOT NULL ," & _ "PRIMARY KEY (`RowID`) ," & _ "UNIQUE INDEX RowID_UNIQUE (`RowID` ASC) );" If Not _EzMySql_Exec($create) Then MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) _refresh($week) EndIf EndIf ;_ArrayDisplay($aok, "$avArray set manually 1D") $j = 0 for $i =0 to UBound($aok) -1 if $aok[$i][1] = "Maandag" then _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][2], 0) _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][3], 1) _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][4], 2) _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][5], 3) $j+=1 EndIf Next $j = 0 for $i =0 to UBound($aok) -1 if $aok[$i][1] = "Dinsdag" then _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][2], 0) _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][3], 1) _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][4], 2) _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][5], 3) $j+=1 EndIf Next $j = 0 for $i =0 to UBound($aok) -1 if $aok[$i][1] = "Woensdag" then _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][2], 0) _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][3], 1) _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][4], 2) _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][5], 3) $j+=1 EndIf Next $j = 0 for $i =0 to UBound($aok) -1 if $aok[$i][1] = "Donderdag" then _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][2], 0) _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][3], 1) _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][4], 2) _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][5], 3) $j+=1 EndIf Next $j = 0 for $i =0 to UBound($aok) -1 if $aok[$i][1] = "Vrijdag" then _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][2], 0) _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][3], 1) _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][4], 2) _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][5], 3) $j+=1 EndIf Next $j = 0 for $i =0 to UBound($aok) -1 if $aok[$i][1] = "Zaterdag" then _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][2], 0) _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][3], 1) _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][4], 2) _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][5], 3) $j+=1 EndIf Next ;_ArrayDisplay($maandagdata, "$avArray set manually 1D") EndFuncsomebody any advice? Link to comment Share on other sites More sharing options...
jchd Posted January 20, 2011 Share Posted January 20, 2011 but if i try to insert data into a exsisting row.it goes wrong then it just create a new row. Are you talking about some GUI element (listview or similar) or about rows of a table in your DB?If the later, you have no UPDATE SQL statement, only INSERTs. No surprise it always inserts and never updates.BTW, I don't have MySQL installed on this machine so I can't run your script in order to follow your explanations closely. 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...
yucatan Posted January 20, 2011 Author Share Posted January 20, 2011 i have now build in a update command. but it fails for some reason. this is the command: UPDATE week3 SET naam='mysql' WHERE rowid='5'); this is the insert command: INSERT INTO week3 (Dag,Bus,Naam,Klantenproject,Wvb) VALUES ('Maandag','','','','Wvb 2'); what am i doing wrong? if $sRowid = "" then Local $sMySqlStatement = "" $sMySqlStatement &= "INSERT INTO "&$week&" (Dag,Bus,Naam,Klantenproject,Wvb) VALUES (" & "'" & $dagread & "'," & "'" & $value1 & "'," & "'" & $value2 & "'," & "'" & $value3 & "'," & "'" & $value4 & "');" ;INSERT INTO week3 (Dag,Bus,Naam,Klantenproject,Wvb) VALUES ('data','data','data','data','data'); Else Local $sMySqlStatement = "" $sMySqlStatement &= "UPDATE "&$week&" SET naam='mysql' WHERE rowid='5');" ConsoleWrite($sMySqlStatement&@CRLF) EndIf ;UPDATE example SET age='22' WHERE age='21'") If Not _EzMySql_Exec($sMySqlStatement) Then MsgBox(0, "Error inserting data to Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf $sMySqlStatement = "" ConsoleWrite( _EzMySql_InsertID()&@CRLF) EndIf Link to comment Share on other sites More sharing options...
jchd Posted January 20, 2011 Share Posted January 20, 2011 I'm currently converting your initial code to use SQLite so testing can be made ubiquitous. Hopefully most DB calls are very similar. In your last code exerpt, where does $RowId come from? BTW the update is wrong: WHERE rowid = '5' should be WHERE rowid = 5 (without quotes). rowid is defined as an integer pripary key 'just like it should be) but you're testing if it equals the string '5'. This UPDATE will never work as is. 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...
yucatan Posted January 20, 2011 Author Share Posted January 20, 2011 the code as is. u can see where i get the rowid. i shall modify my update command and i test it. i let u know. expandcollapse popup#include <GUIConstantsEx.au3> #include <WindowsConstants.au3> #include <GuiListView.au3> #include <Date.au3> #include <Array.au3> #include <TabConstants.au3> #include <EditConstants.au3> #include <UpdownConstants.au3> #Include <GuiTab.au3> #Include <GuiComboBox.au3> #include <Misc.au3> #include<EzMySql.au3> Opt("GUICloseOnESC", 0) If Not _EzMySql_Startup() Then MsgBox(0, "Error Starting MySql", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf If Not _EzMySql_Open("localhost", "root", "", "", "3306") Then MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf If Not _EzMySql_Exec("CREATE DATABASE IF NOT EXISTS planning") Then MsgBox(0, "Error opening Database", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf If Not _EzMySql_SelectDB("planning") Then MsgBox(0, "Error setting Database to use", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf Global $fClicked = 0, $fWeek_0 = False, $fWeek_53 = False Global $hListView = 0, $hEdit_Combo = 0, $aLV_Click_Info Global $iListView_ID, $sItemText Global $aLV_Handles[7], $aDayLabels[7] Global $aWeeks[54][8] Global $sYear = @YEAR Global $sdate = $sYear & "/01/01" Global $buswidth = 30, $monteurwidth = 60, $klantwidth = 155 global $listviews[7],$rows[1],$aok global $maandag[1],$dinsdag[1],$woensdag[1],$donderdag[1],$vrijdag[1],$zaterdag[1],$maandagdata[1],$dinsdagdata[1],$woensdagdata[1],$donderdagdata[1],$vrijdagdata[1] $clickedweek = _WeekNumberISO() ; Open DLL for _IsPressed Global $dll = DllOpen("user32.dll") Global $hGUI = GUICreate("Test", 900, 940) $hTab = GUICtrlCreateTab(10, 10, 890, 940) $hTab_0 = GUICtrlCreateTabItem("Week 0") For $i = 1 To 53 GUICtrlCreateTabItem("Week " & $i) Next GUICtrlCreateTabItem("") $combo = GUICtrlCreateCombo("", 500, 45,120,50, $CBS_DROPDOWNLIST) GUICtrlSetData(-1, "Kopieer vorige week|Laad Standaart", "") ; add other item snd set a new default GUICtrlSetState(_WeekNumberISO() + $hTab_0, $GUI_SHOW) $hInput = GUICtrlCreateInput($sYear, 420, 45, 50, 20, $ES_READONLY) $hUpDown = GUICtrlCreateUpdown($hInput, BitOR($GUI_SS_DEFAULT_UPDOWN, $UDS_WRAP, $UDS_NOTHOUSANDS)) GUICtrlSetLimit($hUpDown, $sYear + 5, $sYear - 5) For $i = 1 To 3 For $j = 0 To 1 $iX = 15 + (290 * ($i - 1)) $iY = 90 + (440 * $j) $aDayLabels[(3 * $j) + $i] = GUICtrlCreateLabel("", $iX + 40, $iY - 20, 150, 25) $hListView = _GUICtrlListView_Create($hGUI, "RowID", $iX, $iY, 285, 407, BitOR($LVS_REPORT, $LVS_SINGLESEL, $WS_BORDER), $LVS_EX_FULLROWSELECT) _ArrayAdd($listviews,$hListView) _GUICtrlListView_SetExtendedListViewStyle($hListView, $LVS_EX_GRIDLINES) ; Set grid lines $aLV_Handles[$i + ($j * 3)] = $hListView _GUICtrlListView_AddColumn($hListView, "Bus") _GUICtrlListView_AddColumn($hListView, "Monteur") _GUICtrlListView_AddColumn($hListView, "Klant en Project") _GUICtrlListView_AddColumn($hListView, "Wvb") ControlDisable($hGUI, "", HWnd(_GUICtrlListView_GetHeader($hListView))) _GUICtrlListView_SetColumnWidth($hListView, 0, 20) _GUICtrlListView_SetColumnWidth($hListView, 1, $buswidth) _GUICtrlListView_SetColumnWidth($hListView, 2, $monteurwidth) _GUICtrlListView_SetColumnWidth($hListView, 3, $klantwidth) _GUICtrlListView_SetColumnWidth($hListView, 4, $LVSCW_AUTOSIZE_USEHEADER) For $k = 1 To 27 _GUICtrlListView_AddItem($hListView, "") _GUICtrlListView_AddSubItem($hListView, $k - 1, "", 1) _GUICtrlListView_AddSubItem($hListView, $k - 1, "", 2) Next Next Next _Calculate() _refresh("week"&_WeekNumberISO()) GUISetState() GUIRegisterMsg($WM_NOTIFY, "WM_NOTIFY") $hAccel_Enter = GUICtrlCreateDummy() Dim $aAccelKeys[1][2]=[["{ENTER}", $hAccel_Enter]] While 1 Switch GUIGetMsg() Case $combo ConsoleWrite(GUICtrlRead($combo)) Case $GUI_EVENT_CLOSE DllClose($dll) Exit Case $hTab ; Do not display weeks 0 and 53 if not used If GUICtrlRead($hTab) = 0 And Not $fWeek_0 Then _GUICtrlTab_ClickTab($hTab, 1) If GUICtrlRead($hTab) = 53 And Not $fWeek_53 Then _GUICtrlTab_ClickTab($hTab, 52) $clickedweek = GUICtrlRead($hTab) For $i = 1 To 6 GUICtrlSetData($aDayLabels[$i], $aWeeks[$clickedweek][$i]) Next ; Clear existing items For $j = 1 To 6 For $i = 0 To 26 ; Because you have created 27 items initially _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "") _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "", 1) _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "", 2) _GUICtrlListView_SetItemText ($aLV_Handles[$j], $i, "", 3) Next Next ; Fill with new data _refresh("week" & $clickedweek) Case $hUpDown _Calculate() EndSwitch ; If an item was double clicked If $fClicked Then ; Get ListView details $hListView = $aLV_Handles[$fClicked] $iListView_ID = $fClicked ; Reset flag $fClicked = 0 ; Get details item clicked $aLV_Click_Info = _GUICtrlListView_SubItemHitTest($hListView) If $aLV_Click_Info[0] <> -1 Then ; Delete any existing combo GUICtrlDelete($hEdit_Combo) ; Get current text $sItemText = _GUICtrlListView_GetItemText($hListView, $aLV_Click_Info[0], $aLV_Click_Info[1]) ; Get Rowid $sRowid = _GUICtrlListView_GetItemText($hListView, $aLV_Click_Info[0], 0) ; Get position Local $aRect = _GUICtrlListView_GetSubItemRect($hListView, $aLV_Click_Info[0], $aLV_Click_Info[1]) Local $aPos = WinGetPos($hListView) Local $tPoint = DllStructCreate("int X;int Y") DllStructSetData($tPoint, "X", $aPos[0]) DllStructSetData($tPoint, "Y", $aPos[1]) _WinAPI_ScreenToClient($hGUI, $tPoint) Local $iEdit_X = DllStructGetData($tPoint, "X") + $aRect[0] Local $iEdit_Y = DllStructGetData($tPoint, "Y") + $aRect[1] ; Get column width Local $iWidth = _GUICtrlListView_GetColumnWidth($hListView, $aLV_Click_Info[1]) ConsoleWrite($sRowid &@CRLF) ; Create edit combo $hEdit_Combo = GUICtrlCreateCombo("", $iEdit_X, $iEdit_Y, $iWidth, 20) ; Set combo values depending on column clicked Switch $aLV_Click_Info[1] ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2 Case 0 $sComboList = "1 Bus|2 Bus|3 Bus" If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText _GUICtrlComboBox_LimitText($hEdit_Combo, 2) Case 1 $sComboList = "Monteur 1|Monteur 2|Monteur 3" If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText Case 2 $sComboList = "Klant 1|Klant 2|Klant 3" If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText Case 3 $sComboList = "Wvb 1|Wvb 2|Wvb 3" If Not StringInStr($sComboList, $sItemText) Then $sComboList &= "|" & $sItemText _GUICtrlComboBox_LimitText($hEdit_Combo, 3) EndSwitch ; Set values into combo GUICtrlSetData($hEdit_Combo, $sComboList, $sItemText) GUICtrlSetState($hEdit_Combo, BitOR($GUI_FOCUS, $GUI_ONTOP)) ; Get handle of combo edit control $hComboEdit = _WinAPI_GetFocus() ; Set flag for Enter pressed $iLoopCode = 0 ; Set flag for combo opened <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2 $fCombo_Opened = False ; Loop while the combo has focus While _WinAPI_GetFocus() = $hComboEdit ; If combo has been opened, check if closed again ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2 If $fCombo_Opened Then ; If combo is closed again If _GUICtrlComboBox_GetDroppedState($hEdit_Combo) = False Then $iLoopCode = 1 ExitLoop EndIf Else ; Check if opened and set flag If _GUICtrlComboBox_GetDroppedState($hEdit_Combo) Then $fCombo_Opened = True EndIf ; If Enter Pressed ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 2 If _IsPressed("0D", $dll) Then $iLoopCode = 1 ExitLoop EndIf ; If ESC pressed If _IsPressed("1B", $dll) Then $iLoopCode = -1 ExitLoop EndIf ; Save CPU and prevent tab selection <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 1 Switch GUIGetMsg() Case $hTab _GUICtrlTab_ClickTab($hTab, $clickedweek) ;Case $GUI_EVENT_CLOSE ; DllClose($dll) ; Exit EndSwitch WEnd ; Read Combo $sText = GUICtrlRead($hEdit_Combo) ; Check if content changed manually If Not $iLoopCode And $sText <> $sItemText Then $iLoopCode = 1 ; <<<<<<<<<<<<<<<<<<<<< 2 ; Edit ListView if required If $iLoopCode = 1 Then _GUICtrlListView_SetItemText($hListView, $aLV_Click_Info[0], $sText, $aLV_Click_Info[1]) $week = "week"&GUICtrlRead($hTab) if $hListView = $listviews[7] then $dagread = "Maandag" if $hListView = $listviews[9] then $dagread = "Dinsdag" if $hListView = $listviews[11] then $dagread = "Woensdag" if $hListView = $listviews[8] then $dagread = "Donderdag" if $hListView = $listviews[10] then $dagread = "Vrijdag" if $hListView = $listviews[12] then $dagread = "Zaterdag" if $aLV_Click_Info[1] = 3 then $fields = "Wvb" if $aLV_Click_Info[1] = 2 then $fields = "Klantenproject" if $aLV_Click_Info[1] = 1 then $fields = "Naam" if $aLV_Click_Info[1] = 0 then $fields = "Bus" $value1 = "" $value2 = "" $value3 = "" $value4 = "" if $aLV_Click_Info[1] + 1 = 1 then $value1 = $sText if $aLV_Click_Info[1] + 1 = 2 then $value2 = $sText if $aLV_Click_Info[1] + 1 = 3 then $value3 = $sText if $aLV_Click_Info[1] + 1 = 4 then $value4 = $sText if $sRowid = "" then Local $sMySqlStatement = "" $sMySqlStatement &= "INSERT INTO "&$week&" (Dag,Bus,Naam,Klantenproject,Wvb) VALUES (" & "'" & $dagread & "'," & "'" & $value1 & "'," & "'" & $value2 & "'," & "'" & $value3 & "'," & "'" & $value4 & "');" ;INSERT INTO week3 (Dag,Bus,Naam,Klantenproject,Wvb) VALUES ('data','data','data','data','data'); ConsoleWrite($sMySqlStatement&@CRLF) Else Local $sMySqlStatement = "" $sMySqlStatement &= "UPDATE "&$week&" SET naam='mysql' WHERE rowid='5');" EndIf ;UPDATE example SET age='22' WHERE age='21'") If Not _EzMySql_Exec($sMySqlStatement) Then MsgBox(0, "Error inserting data to Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) Exit EndIf $sMySqlStatement = "" ConsoleWrite( _EzMySql_InsertID()&@CRLF) EndIf ; Delete combo GUICtrlDelete($hEdit_Combo) $hEdit_Combo = 0 EndIf EndIf WEnd Func WM_NOTIFY($hWnd, $iMsg, $wParam, $lParam) #forceref $hWnd, $iMsg, $wParam Local $hWndFrom, $iCode, $tNMHDR $tNMHDR = DllStructCreate($tagNMHDR, $lParam) $hWndFrom = HWnd(DllStructGetData($tNMHDR, "hWndFrom")) $iCode = DllStructGetData($tNMHDR, "Code") For $i = 1 To 6 If $aLV_Handles[$i] = $hWndFrom Then Switch $iCode Case $NM_CLICK $fClicked = $i EndSwitch ExitLoop EndIf Next Return $GUI_RUNDEFMSG EndFunc ;==>WM_NOTIFY Func _Calculate() ; Clear array Global $aWeeks[54][8] ; Set local variables Local $asDatePart, $iTimePart Local $aDays[8] = [7, "Maandag", "Dinsdag", "Woensdag", "Donderdag", "Vrijdag", "Zaterdag", "Zondag"] Local $aMonths[13] = [12, "Januari", "Februari", "Maart", "April", "Mei", "Juni", "Juli", "Augustus", "September", "October", "November", "December"] ; Read required year and week $sYear = GUICtrlRead($hInput) $clickedweek = GUICtrlRead($hTab) ; Set first day $sdate = $sYear & "/01/01" ; Set flag to indicate initial week $fStart = True ; Set flags to indicate 0/53 week empty $fWeek_0 = False $fWeek_53 = False While 1 ; Split date _DateTimeSplit($sdate, $asDatePart, $iTimePart) ; Exit if past end required year If $asDatePart[1] > $sYear Then ExitLoop ; Get week number $iWeek = _WeekNumberISO($sYear, $asDatePart[2], $asDatePart[3]) ; Adjust to cover final part of previous Week 52 if required If $fStart Then If $iWeek >= 52 Then $iWeek = 0 Else If $iWeek = 1 Then $iWeek = 53 EndIf ; Reset flag if past initial week If $iWeek = 2 Then $fStart = False ; Get day of week and adjust for Sunday $iIndex = _DateToDayOfWeek($sYear, $asDatePart[2], $asDatePart[3]) If $iIndex = 1 Then $iIndex = 8 ; Adjust for final week if needed If Not $fStart And $iWeek = 53 Then $iWeek = 52 EndIf ; Fill array ConsoleWrite($aDays[$iIndex - 1] & " " & $asDatePart[3] & " " & $aMonths[$asDatePart[2]]&@CRLF) $aWeeks[$iWeek][$iIndex - 1] = $aDays[$iIndex - 1] & " " & $asDatePart[3] & " " & $aMonths[$asDatePart[2]] ; Move to next day $sdate = _DateAdd("d", 1, $sdate) ; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< WEnd ; Check if weeks 0 and 53 are used For $i = 1 To 6 If $aWeeks[0][$i] <> "" Then $fWeek_0 = True If $aWeeks[53][$i] <> "" Then $fWeek_53 = True Next ; If not then do not display them if selected If GUICtrlRead($hTab) = 0 And Not $fWeek_0 Then _GUICtrlTab_ClickTab($hTab, 1) If GUICtrlRead($hTab) = 53 And Not $fWeek_53 Then _GUICtrlTab_ClickTab($hTab, 52) $clickedweek = GUICtrlRead($hTab) For $i = 1 To 6 ;ConsoleWrite($aDayLabels[$i] & " - " & $aWeeks[$clickedweek][$i] & @CRLF) GUICtrlSetData($aDayLabels[$i], $aWeeks[$clickedweek][$i]) Next EndFunc ;==>_Calculate func _refresh($week) ;$arraysize = UBound($aok) ConsoleWrite($aok&@CRLF) $aOk = _EzMySql_GetTable2d("SELECT * FROM "&$week&";") $error = @error If Not IsArray($aOk) Then $create = "CREATE TABLE IF NOT EXISTS "&$week&" (" & _ "RowID INT NOT NULL AUTO_INCREMENT," & _ "Dag TEXT NOT NULL ," & _ "Bus TEXT NOT NULL ," & _ "Naam TEXT NOT NULL ," & _ "Klantenproject TEXT NOT NULL ," & _ "Wvb TEXT NOT NULL ," & _ "PRIMARY KEY (`RowID`) ," & _ "UNIQUE INDEX RowID_UNIQUE (`RowID` ASC) );" If Not _EzMySql_Exec($create) Then MsgBox(0, "Error Creating Database Table", "Error: "& @error & @CR & "Error string: " & _EzMySql_ErrMsg()) _refresh($week) EndIf EndIf _ArrayDisplay($aok, "$avArray set manually 1D") $j = 0 for $i =0 to UBound($aok) -1 if $aok[$i][1] = "Maandag" then _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][0], 0) _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][2], 1) _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][3], 2) _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][4], 3) _GUICtrlListView_SetItemText($listviews[7], $j , $aok[$i][5], 4) $j+=1 EndIf Next $j = 0 for $i =0 to UBound($aok) -1 if $aok[$i][1] = "Dinsdag" then _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][0], 0) _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][2], 1) _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][3], 2) _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][4], 3) _GUICtrlListView_SetItemText($listviews[9], $j , $aok[$i][5], 4) $j+=1 EndIf Next $j = 0 for $i =0 to UBound($aok) -1 if $aok[$i][1] = "Woensdag" then _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][0], 0) _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][2], 1) _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][3], 2) _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][4], 3) _GUICtrlListView_SetItemText($listviews[11], $j , $aok[$i][5], 4) $j+=1 EndIf Next $j = 0 for $i =0 to UBound($aok) -1 if $aok[$i][1] = "Donderdag" then _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][0], 0) _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][2], 1) _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][3], 2) _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][4], 3) _GUICtrlListView_SetItemText($listviews[8], $j , $aok[$i][5], 4) $j+=1 EndIf Next $j = 0 for $i =0 to UBound($aok) -1 if $aok[$i][1] = "Vrijdag" then _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][0], 0) _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][2], 1) _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][3], 2) _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][4], 3) _GUICtrlListView_SetItemText($listviews[10], $j , $aok[$i][5], 4) $j+=1 EndIf Next $j = 0 for $i =0 to UBound($aok) -1 if $aok[$i][1] = "Zaterdag" then _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][0], 0) _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][2], 1) _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][3], 2) _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][4], 3) _GUICtrlListView_SetItemText($listviews[12], $j , $aok[$i][5], 4) $j+=1 EndIf Next EndFunc Greetz Yucatan Link to comment Share on other sites More sharing options...
jchd Posted January 20, 2011 Share Posted January 20, 2011 Sorry for interruption (I also have a business to run). I'd rather see you use the following schema: CREATE TABLE [planning] ( [id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [Year] INTEGER NOT NULL, [Week] INTEGER NOT NULL, [Dag] INTEGER NOT NULL, [bus] TEXT NOT NULL DEFAULT (''), [Naam] TEXT NOT NULL DEFAULT (''), [Klantenproject] TEXT NOT NULL DEFAULT (''), [Wvb] TEXT NOT NULL DEFAULT ('')); CREATE INDEX [idxDate] ON [planning] ([Year], [Week], [Dag]); My personnal thoughs: You need a year field or you're going to have hard time. Place your week in the global table instead of having one table per week. Make Dag (day ?) an index in a DayOf Week table. Other columns are applicative as I see them. You may have to add indexes if the baby get havily populated. MySQL seems a sledgehammer for this. Now your problem is that you were inserting a row after selection of any item in any combo box. Without using a MySQL-specific verb or feature and relying only on portable SQL statements, what you should do is, after validation of an item in a combo box, read the corresponding row and insert if there is none or update the particular column if that row already exists. Now the question is: how do you differentiate between rows in the same listview? I mean how does the position of the row in the listview translate into a condition in the SQL table? 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...
yucatan Posted January 20, 2011 Author Share Posted January 20, 2011 i know i'm going to add a year now. how can i copy an entire table to another table? for example if i wanne copy everything in week 2 into week 3? Link to comment Share on other sites More sharing options...
jchd Posted January 20, 2011 Share Posted January 20, 2011 Standard SQL does it nicely. insert into week3 select * from week2; 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...
yucatan Posted January 25, 2011 Author Share Posted January 25, 2011 (edited) <question has been removed dubbelpost> Edited January 25, 2011 by yucatan Link to comment Share on other sites More sharing options...
yucatan Posted January 25, 2011 Author Share Posted January 25, 2011 Standard SQL does it nicely.insert into week3 select * from week2;when i hit that i get this error:Duplicate entry '1' for key 'PRIMARY' 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