From ab977acbdce58cdf5d1eaafc616caa0c524e6733 Mon Sep 17 00:00:00 2001 From: Benjamin Linnik Date: Fri, 8 Nov 2013 13:20:45 +0100 Subject: [PATCH] bot: added new sql functions --- bot/Chips/Mimosa29.au3 | 73 +++ bot/Functions/CheckForConstantTemp.au3 | 16 + bot/Functions/Checkdata.au3 | 17 +- bot/Functions/CloseOpenExcel.au3 | 6 + bot/Functions/CommonFunctions.au3 | 2 +- bot/Functions/Header.au3 | 28 +- bot/Functions/LogWriteAndRefresh.au3 | 55 ++ bot/Functions/MoveFolder.au3 | 15 +- bot/Functions/Neu AutoIt v3 Script.au3 | 12 + bot/Functions/PrintSucessMessage.au3 | 2 +- bot/Functions/ReadAndSaveSQLParameters.au3 | 9 + bot/Functions/RefreshGUI.au3 | 2 +- bot/Functions/RefreshGUI2.au3 | 64 ++ bot/Functions/SaveToExcel.au3 | 4 +- bot/Functions/SaveToSQL.au3 | 14 + bot/Functions/SendNtfctn.au3 | 68 +-- bot/Functions/TemperatureControl.au3 | 1 + bot/Functions/WaitForTemperature.au3 | 22 +- bot/Functions/Watchrun.au3 | 19 +- bot/Functions/mysql_functions.au3 | 127 ++++ bot/Functions/mysql_header.au3 | 654 +++++++++++++++++++++ 21 files changed, 1127 insertions(+), 83 deletions(-) create mode 100755 bot/Chips/Mimosa29.au3 create mode 100755 bot/Functions/CheckForConstantTemp.au3 create mode 100755 bot/Functions/CloseOpenExcel.au3 create mode 100755 bot/Functions/LogWriteAndRefresh.au3 create mode 100755 bot/Functions/Neu AutoIt v3 Script.au3 create mode 100755 bot/Functions/ReadAndSaveSQLParameters.au3 create mode 100755 bot/Functions/RefreshGUI2.au3 create mode 100755 bot/Functions/SaveToSQL.au3 create mode 100755 bot/Functions/mysql_functions.au3 create mode 100755 bot/Functions/mysql_header.au3 diff --git a/bot/Chips/Mimosa29.au3 b/bot/Chips/Mimosa29.au3 new file mode 100755 index 0000000..069790a --- /dev/null +++ b/bot/Chips/Mimosa29.au3 @@ -0,0 +1,73 @@ +Global $system = "USB" +Global $hWnd +Global $hCounter +$savedir = "F:\Mi29" +Global $vref = -1 ; Initial vref, will be adjusted during runtime +$temperaturebefore = 600 + +Func Mi29_GotoMiTab() + +EndFunc ;==>Mi34_GotoMiTab + +Func Mi29_SelectBoard() + +EndFunc ;==>Mi34_SelectBoard + +Func Mi29_GetVref() + return -1 +EndFunc ;==>Mi34_GetVref + +Func Mi29_SetVref($vref) + +EndFunc ;==>Mi34_SetVref + +Func Mi29_SetMiParameter($matrix, $vref) + Switch($matrix) + Case "64" + ControlSend("CMOS USB DAQ V3.5.10", "", "TListBox1", "mi29_64") + Case "80" + ControlSend("CMOS USB DAQ V3.5.10", "", "TListBox1", "mi29_64") + ControlSend("CMOS USB DAQ V3.5.10", "", "TListBox1", "{DOWN}") + ;ControlSend("CMOS USB DAQ V3.5.10", "", "TListBox1", "mi29_64") doesnt work... + Case Else + MsgBox("1", "Schmodder", "Superschmodder: Falsche MAtrix ausgewählt!") + EndSwitch + Sleep(100) +EndFunc ;==>Mi34_SetMiParameter + +Func Mi29_TestSystem() + WinWait("Debug") + ControlClick("Debug", "", "[TEXT:Messages Clear]") + Sleep(100) + ControlClick("Debug", "", "[TEXT:Board Testing]") + Sleep(2000) + $i = 0 + Do + $text = WinGetText("[CLASS:TDebugFrm]", "") + $texthex = _StringToHex($text) + If ($texthex == "4D657373616765730A5072696E7420426F6172647320526567730A0D0A3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D0D0A426F6172642049643D302074657374207374617274696E67202E2E2E0D0A3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D3D0D0A0D0A53746F70206163717569736974696F6E202E2E2E0D0A41445220434F554E54455220412074657374696E67202E2E2E0D0A41445220434F554E54455220412030204572726F72730D0A52414D20412074657374696E67202E2E2E0D0A52414D20412030204572726F72730D0A52414D20422074657374696E67202E2E2E0D0A52414D20422030204572726F72730D0A52414D20432074657374696E67202E2E2E0D0A52414D20432030204572726F72730D0A52414D20442074657374696E67202E2E2E0D0A52414D20442030204572726F72730D0A0A555342322042616E647769647468204D65617375726D656E740A426F6172642054657374696E670A536F66747761726520496E697469616C697A6174696F6E0A4D6573736167657320436C6561720A") Then + ExitLoop + EndIf + Sleep(500) + $i = $i + 1 + If ($i > 20) Then + RefreshGUISendNtfct("USB Boards could be not OK! Please turn them on and off.") + ;Run(@AutoItExe & ' /AutoIt3ExecuteScript "' & @ScriptDir & '\Functions\RefreshGUISendNtfct.au3" "' & $system & '" "' & $completelog & '" "USB Boards could be not OK! Please turn them on and off."') + MsgBox(16, "Message", "USB Boards could be not OK! Please check the Debug output.") + Return 1 + EndIf + Until 0 + If StringRegExp($text, "[1-9]+ Errors") Then ; double check, will remove it later + RefreshGUISendNtfct("USB Boards could be not OK! Please turn them on and off.") + ;Run(@AutoItExe & ' /AutoIt3ExecuteScript "' & @ScriptDir & '\Functions\RefreshGUISendNtfct.au3" "' & $system & '" "' & $completelog & '" "USB Boards could be not OK! Please turn them on and off."') + MsgBox(16, "Message", "USB Boards could be not OK! Please check the Debug output.") + Return 1 + EndIf + WinClose("Debug") + WinWaitClose("Debug", 1000) + Return 0 +EndFunc + +Func Mi29_GetAverage($hWnd) + Return 2000 ; fake a good vref +EndFunc diff --git a/bot/Functions/CheckForConstantTemp.au3 b/bot/Functions/CheckForConstantTemp.au3 new file mode 100755 index 0000000..0db4e40 --- /dev/null +++ b/bot/Functions/CheckForConstantTemp.au3 @@ -0,0 +1,16 @@ +AutoItSetOption("TrayIconHide", 1) + +$returnval = 10000 +If ($CmdLine[0] > 1) Then + Sleep(1000 * 60 * 5) ; Wait 5 minutes + $returnval = CheckForConstantTemp($CmdLine[1], $CmdLine[2]) +EndIf +ConsoleWrite($returnval) ;### Debug Console +Exit $returnval + + +Func CheckForConstantTemp($savedir, $runnumber) + Local $strFileContents = FileRead($savedir & "\" & $runnumber & "\temperature.log") + Local $aryItems = StringSplit($strFileContents, '\t', 2) + Return 0 +EndFunc ;==>MoveFolder \ No newline at end of file diff --git a/bot/Functions/Checkdata.au3 b/bot/Functions/Checkdata.au3 index d7aed2d..2d2cd96 100755 --- a/bot/Functions/Checkdata.au3 +++ b/bot/Functions/Checkdata.au3 @@ -1,11 +1,11 @@ ; Prompt the user to run the script - use a Yes/No prompt (4 - see help file) -Local $answer = MsgBox(4, "Run script?", "Preparing to do all runs in range " & $CellRange & @CRLF & _ - "of file " & $FileName & " with " & @CRLF & _ +Local $answer = MsgBox(4, "Run script?", "Preparing to do all runs with " & @CRLF & _ "Radioactive source: " & $radSource & @CRLF & _ "Chip: " & $chipversion & " (#" & $chipinstalled & ")" & @CRLF & _ "Radiated: " & $radiated & @CRLF & _ "If you run this script, daq.exe will be closed and the current run aborted, proceed?") +;~ "of file " & $FileName & " with " & @CRLF & _ ; Check the user's answer to the prompt (see the help file for MsgBox return values) @@ -13,8 +13,11 @@ Local $answer = MsgBox(4, "Run script?", "Preparing to do all runs in range " & If $answer = 7 Then Exit EndIf -If Not FileExists($FileName) Then ; Just a check to be sure.. - MsgBox(16, "Excel Data Test", "Error: Can't find file " & $FileName) - Exit -EndIf -$oExcelDoc = ObjGet($FileName) ; Get an Excel Object from an existing filename \ No newline at end of file +;~ If Not FileExists($FileName) Then ; Just a check to be sure.. +;~ MsgBox(16, "Excel Data Test", "Error: Can't find file " & $FileName) +;~ Exit +;~ EndIf +;~ $oExcelDoc = ObjGet($FileName) ; Get an Excel Object from an existing filename + + +;~ Global $sql = GetSQLConnection() \ No newline at end of file diff --git a/bot/Functions/CloseOpenExcel.au3 b/bot/Functions/CloseOpenExcel.au3 new file mode 100755 index 0000000..5e9a1dc --- /dev/null +++ b/bot/Functions/CloseOpenExcel.au3 @@ -0,0 +1,6 @@ + + $oExcelDoc = ObjGet("H:\dennis doering\Arbeitsprogramme\Mimosa34\LaborbuchMi34 - Neu.xls") + $oExcelDoc.Save ; Save the workbook + $oExcelDoc.saved = 1 + $oExcelDoc.Close ; Close the Excel document + $oExcelDoc = 0 \ No newline at end of file diff --git a/bot/Functions/CommonFunctions.au3 b/bot/Functions/CommonFunctions.au3 index 6cf8ab3..8044c35 100755 --- a/bot/Functions/CommonFunctions.au3 +++ b/bot/Functions/CommonFunctions.au3 @@ -12,7 +12,7 @@ Func GetTemperatureForSure($whichone) EndIf $tempgeti = $tempgeti + 1 Until $currenttemp < 100000 - Return $currenttemp / 100 + Return $currenttemp / 100.0 EndFunc Func RefreshGUISendNtfct( $msg ) diff --git a/bot/Functions/Header.au3 b/bot/Functions/Header.au3 index 879ca4e..3d9361c 100755 --- a/bot/Functions/Header.au3 +++ b/bot/Functions/Header.au3 @@ -7,11 +7,16 @@ #include #include "BinaryIntegerConvert.au3" #include "EpochConvert.au3" -$programversion = "0.99.1" +#include "mysql_functions.au3" +$objErr = ObjEvent("AutoIt.Error","MyErrFunc") + +$programversion = "1.0" $excelLineBegin = "1100" ; Change this to the range of cells you want to be processed $excelLineEnd = "4000" Global $completelog = "" -$CellRange = "A" & $excelLineBegin & ":Q" & $excelLineEnd + +$lastexcelcell = "Q" +$CellRange = "A" & $excelLineBegin & ":" & $lastexcelcell & $excelLineEnd $cellSystem = 0 $cellDate = 1 $cellChipV = 3 @@ -28,6 +33,8 @@ $cellRad = 14 $cellTChip = 15 $cellNote = 16 +$estimate = 0 +$totalrealruns = 0 $temperaturebefore = -10000 ; set external parameters @@ -36,3 +43,20 @@ $chipversion = IniRead($configfile, "experimental setup", "ChipVersion", "Mi34") $chipinstalled = IniRead($configfile, "experimental setup", "ChipInstalled", "5") ; chip number currently mounted into the system $radSource = IniRead($configfile, "experimental setup", "RadSource", "none") ; current radioactive source of the system $radiated = IniRead($configfile, "experimental setup", "Radiated", "") ; radiated chip + +Func MyErrFunc() + +$hexnum=hex($objErr.number,8) + +Msgbox(0,"","We intercepted a COM Error!!" & @CRLF & @CRLF & _ + "err.description is: " & $objErr.description & @CRLF & _ + "err.windescription is: " & $objErr.windescription & @CRLF & _ + "err.lastdllerror is: " & $objErr.lastdllerror & @CRLF & _ + "err.scriptline is: " & $objErr.scriptline & @CRLF & _ + "err.number is: " & $hexnum & @CRLF & _ + "err.source is: " & $objErr.source & @CRLF & _ + "err.helpfile is: " & $objErr.helpfile & @CRLF & _ + "err.helpcontext is: " & $objErr.helpcontext _ + ) +exit +EndFunc diff --git a/bot/Functions/LogWriteAndRefresh.au3 b/bot/Functions/LogWriteAndRefresh.au3 new file mode 100755 index 0000000..12d4f98 --- /dev/null +++ b/bot/Functions/LogWriteAndRefresh.au3 @@ -0,0 +1,55 @@ +#include +AutoItSetOption("TrayIconHide", 1) +$oMyError = ObjEvent("AutoIt.Error","LogWriteErr") ; Install a custom error handler + +If ($CmdLine[0] == 2) Then + $returnval = SetTemperature($CmdLine[1], $CmdLine[2]) +EndIf +ConsoleWrite($returnval) ;### Debug Console +Exit $returnval + +Func LogWriteAndRefresh($text, $system) + ;~ ; Send text to Server + ;~ $textenc = urlencode($text) + ;~ $oHTTP = ObjCreate("winhttp.winhttprequest.5.1") + ;~ $oHTTP.Open("GET", "http://autoremotejoaomgcd.appspot.com/sendmessage?key=APA91bEejQPoLUIipcNpfeDvNoa5Bd48AASVBHRphY4ySu8U2FSfuDym2iHdkC_YJ2zyhhbVXVB0oRacd_QmDFsrNRATF0rT4b7pqIHj8EeUrOdq0s_gQq7BE0ERaUfHHikhet06D8ff8BUXe8uVZ5RqoI2ygWc48Q&message=RadHard:%20System-" & $system & ":%20" & $text & "&password=TannenbaumSaftSchorle") + ;~ $oHTTP.Send() + + Local $file = FileOpen("Progress_" & $system & ".log", 1) + ; Check if file opened for writing OK + If $file = -1 Then + Return 1 + EndIf + FileWriteLine($file, $text) + FileClose($file) + Return 0 +EndFunc + +Func urlencode($str, $plus = False) + Local $i, $return, $tmp, $exp + $return = "" + $exp = "[a-zA-Z0-9-._~]" + If $plus Then + $str = StringReplace($str, " ", "+") + $exp = "[a-zA-Z0-9-._~+]" + EndIf + For $i = 1 To StringLen($str) + $tmp = StringMid($str, $i, 1) + If StringRegExp($tmp, $exp, 0) = 1 Then + $return &= $tmp + Else + $return &= StringMid(StringRegExpReplace(StringToBinary($tmp, 4), "([0-9A-Fa-f]{2})", "%$1"), 3) + EndIf + Next + Return $return +EndFunc ;==>urlencode + + +; This is my custom error handler +Func LogWriteErr() + ConsoleWrite('@@ (27) :(' & @MIN & ':' & @SEC & ') MyErrFunc()' & @CR) ;### Function Trace + $HexNumber=hex($oMyError.number,8) + ConsoleWrite("Intercepted a COM Error !" & @CRLF & _ + "Number is: " & $HexNumber & @CRLF & _ + "Windescription is: " & $oMyError.windescription) +Endfunc \ No newline at end of file diff --git a/bot/Functions/MoveFolder.au3 b/bot/Functions/MoveFolder.au3 index 1067903..e3eb0e5 100755 --- a/bot/Functions/MoveFolder.au3 +++ b/bot/Functions/MoveFolder.au3 @@ -1,8 +1,9 @@ -AutoItSetOption("TrayIconHide", 1) $returnval = 10000 -If ($CmdLine[0] > 1) Then +If ($CmdLine[0] = 2) Then $returnval = MoveFolder($CmdLine[1], $CmdLine[2]) +ElseIf ($CmdLine[0] = 3) Then + $returnval = MoveFolders($CmdLine[1], $CmdLine[2], $CmdLine[3]) EndIf ConsoleWrite($returnval) ;### Debug Console Exit $returnval @@ -13,4 +14,14 @@ Func MoveFolder($src, $dst) FileDelete($src & "\*") DirRemove( $src ) Return 0 +EndFunc ;==>MoveFolder + + +Func MoveFolders($src, $dst, $runnumber) + For $i = 0 To 2 + DirMove ( $src & $runnumber-$i, $dst & $runnumber-$i ) + FileDelete($src & $runnumber-$i & "\*") + DirRemove( $src & $runnumber-$i ) + Next + Return 0 EndFunc ;==>MoveFolder \ No newline at end of file diff --git a/bot/Functions/Neu AutoIt v3 Script.au3 b/bot/Functions/Neu AutoIt v3 Script.au3 new file mode 100755 index 0000000..dc483df --- /dev/null +++ b/bot/Functions/Neu AutoIt v3 Script.au3 @@ -0,0 +1,12 @@ +#cs ---------------------------------------------------------------------------- + + AutoIt Version: 3.3.8.1 + Author: myName + + Script Function: + Template AutoIt script. + +#ce ---------------------------------------------------------------------------- + +; Script Start - Add your code below here + diff --git a/bot/Functions/PrintSucessMessage.au3 b/bot/Functions/PrintSucessMessage.au3 index 950e1e7..a009ae6 100755 --- a/bot/Functions/PrintSucessMessage.au3 +++ b/bot/Functions/PrintSucessMessage.au3 @@ -4,4 +4,4 @@ $calchours= Round($calcsec / 3600, 2) ; Hoours $calcdays= Round($calcsec / 86400, 2) ; Days RefreshGUISendNtfct("All runs (" & $totalrealruns & ") ended in " & $calcmin & " minutes (" & $calchours &" hours).") ;Run(@AutoItExe & ' /AutoIt3ExecuteScript "' & @ScriptDir & '\Functions\RefreshGUISendNtfct.au3" "' & $system & '" "All runs (' & $totalrealruns & ') ended in ' & $calcmin & ' minutes (' & $calchours &' hours)."') -Run(@AutoItExe & ' /AutoIt3ExecuteLine "MsgBox(0, ''Sucess!'', ''All runs (' & $totalrealruns & ') ended in ' & $calcmin & ' minutes (' & $calchours &' hours, ' & $calcdays & ' days).' & @CRLF & 'Please check the Excel sheet and the results for errors.'')"') +Run(@AutoItExe & ' /AutoIt3ExecuteLine "MsgBox(0, ''Sucess!'', ''All runs (' & $totalrealruns & ') ended in ' & $calcmin & ' minutes (' & $calchours &' hours, ' & $calcdays & ' days).' & @CRLF & 'Please check the SQL data and the results for errors.'')"') diff --git a/bot/Functions/ReadAndSaveSQLParameters.au3 b/bot/Functions/ReadAndSaveSQLParameters.au3 new file mode 100755 index 0000000..76f5d0a --- /dev/null +++ b/bot/Functions/ReadAndSaveSQLParameters.au3 @@ -0,0 +1,9 @@ +$runid = $suitrun[0] +$TempCooling = $suitrun[1] +$TempWaitFor = $suitrun[2] +If $TempWaitFor == "" Then $TempWaitFor = 0 +$clock = $suitrun[3] +If $clock == "" Then $clock = 100 +Global $runnumber = $suitrun[4] +$matrix = $suitrun[5] +$events = $suitrun[6] \ No newline at end of file diff --git a/bot/Functions/RefreshGUI.au3 b/bot/Functions/RefreshGUI.au3 index e389e0f..a8b41eb 100755 --- a/bot/Functions/RefreshGUI.au3 +++ b/bot/Functions/RefreshGUI.au3 @@ -4,7 +4,7 @@ $error = 1 Switch $CmdLine[0] Case 1 - RefreshLog($CmdLine[1]) + RefreshLog($CmdLine[1]) Case 2 If WinExists("MABS (Mimosa Automation Bot System)") Then If ($CmdLine[1] > 0 And $CmdLine[2] > 0 And $CmdLine[1] < 100 And $CmdLine[2] < 100) Then diff --git a/bot/Functions/RefreshGUI2.au3 b/bot/Functions/RefreshGUI2.au3 new file mode 100755 index 0000000..c97d246 --- /dev/null +++ b/bot/Functions/RefreshGUI2.au3 @@ -0,0 +1,64 @@ +#include +#include +#include +#include "GetSystem.au3" +AutoItSetOption("TrayIconHide", 1) +$oMyError = ObjEvent("AutoIt.Error","RefreshGUIErr") ; Install a custom error handler + +$returnval = 1 +Switch $CmdLine[0] + Case 2 + $returnval = RefreshLog($CmdLine[1], $CmdLine[2]) + Case 3 + If ($CmdLine[1] > 0 And $CmdLine[2] > 0 And $CmdLine[1] < 100 And $CmdLine[2] < 100) Then + $returnval = RefreshPercent($CmdLine[1], $CmdLine[2, $CmdLine[3]]) + EndIf +EndSwitch +ConsoleWrite($returnval) ;### Debug Console +Exit $returnval + +Func RefreshPercent($percentoverall, $percentcurrent, $system) + ControlSetText("MABS (Mimosa Automation Bot System)", "", "Static1", $percentoverall) + ControlSetText("MABS (Mimosa Automation Bot System)", "", "Static2", $percentcurrent) + Return 0 +EndFunc ;==>RefreshPercent + +Func RefreshLog($text, $system) + $filename = "Progress_" & $system & ".log" + _FileWriteLog($filename, $text) ; Write to the logfile + + Local $file = FileOpen($filename, 0) + ; Check if file opened for reading OK + If $file <> -1 Then + Local $log = FileRead($file, -1) + FileClose($file) + + If WinExists("MABS (Mimosa Automation Bot System)") Then + $hLog = ControlGetHandle("MABS (Mimosa Automation Bot System)", "", "Edit1") + $iEnd = StringLen($log) + ControlSetText("MABS (Mimosa Automation Bot System)", "", "Edit1", $log) + _GUICtrlEdit_SetSel($hLog, $iEnd, $iEnd) + _GUICtrlEdit_Scroll($hLog, $SB_SCROLLCARET ) + EndIf + Return 0 + EndIf + + ; Send text to Server + $textenc = urlencode($text) + $oHTTP = ObjCreate("winhttp.winhttprequest.5.1") + $oHTTP.Open("POST", "http://cerberus.x-matter.uni-frankfurt.de:8700/tools/logfile/") + $oHTTP.Send() +EndFunc ;==>RefreshLog + + + + +; This is my custom error handler +Func RefreshGUIErr() + ConsoleWrite('@@ (27) :(' & @MIN & ':' & @SEC & ') RefreshGUIErr()' & @CR) ;### Function Trace + $HexNumber=hex($oMyError.number,8) + ConsoleWrite("Intercepted a COM Error !" & @CRLF & _ + "Number is: " & $HexNumber & @CRLF & _ + "Windescription is: " & $oMyError.windescription) + Exit 1 +Endfunc \ No newline at end of file diff --git a/bot/Functions/SaveToExcel.au3 b/bot/Functions/SaveToExcel.au3 index e520798..228a3ee 100755 --- a/bot/Functions/SaveToExcel.au3 +++ b/bot/Functions/SaveToExcel.au3 @@ -11,4 +11,6 @@ $oDocument = $oExcelDoc.Worksheets(1) ; We use the 'Default' worksheet $oDocument.range($currentrange).value = $currentline $oExcelDoc.Windows(1).Visible = True ; Otherwise the worksheet window will be saved 'hidden' $oExcelDoc.Save ; Save the workbook -$oExcelDoc.saved = 1 \ No newline at end of file +$oExcelDoc.saved = 1 +$oExcelDoc.Close ; Close the Excel document +$oExcelDoc = 0 ; destroy objdect \ No newline at end of file diff --git a/bot/Functions/SaveToSQL.au3 b/bot/Functions/SaveToSQL.au3 new file mode 100755 index 0000000..10626fd --- /dev/null +++ b/bot/Functions/SaveToSQL.au3 @@ -0,0 +1,14 @@ +$calcsec = _DateDiff('s', $starttime, _NowCalc()) ; n for minites, s for seconds +$calcmin = Round($calcsec / 60, 2) +$calchours = Round($calcsec / 3600, 2) +If $temperaturechipstart = 100000 Then $temperaturechipstart = "NULL" +If $temperaturechipend = 100000 Then $temperaturechipstart = "NULL" +$datetime = @YEAR & "-" & @MON & "-" & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC +if (IsDeclared($storedir)) Then + $path = $storedir & "\\" & $runnumber +Else + $path = $savedir & "\\" & $runnumber +EndIf + +UpdateDBwithRun($runid, $datetime, $temperaturechipstart, $temperaturechipend, $vref, $calcsec, $programversion, $path) + diff --git a/bot/Functions/SendNtfctn.au3 b/bot/Functions/SendNtfctn.au3 index f019797..400c3cc 100755 --- a/bot/Functions/SendNtfctn.au3 +++ b/bot/Functions/SendNtfctn.au3 @@ -4,74 +4,42 @@ $notifycustom = IniRead("config.ini", "various", "NotifyCustom", "") ; chip numb $error = 1 If $CmdLine[0] > 1 Then - If $CmdLine[1] == "USB" Then - SendNtfctn_USB($CmdLine[2]) - $error = 0 - ElseIf $CmdLine[1] == "PXI" Then - SendNtfctn($CmdLine[2], "PXI") - $error = 0 - EndIf + If $CmdLine[1] == "USB" Then + SendNtfctn($CmdLine[2], "USB") + $error = 0 + ElseIf $CmdLine[1] == "PXI" Then + SendNtfctn($CmdLine[2], "PXI") + $error = 0 + EndIf EndIf ConsoleWrite($error) -Func SendNtfctn_USB($text) - $notify = IniRead("config.ini", "various", "Notify", "1") ; notifycode - $notifycustom = IniRead("config.ini", "various", "NotifyCustom", "") ; chip number currently mounted into the system - - $notifyBenny =BitAND($notify, 1) - $notifyDennis=BitAND($notify, 2) - $notifyStefan=BitAND($notify, 4) - - $text = urlencode($text) - $oHTTP = ObjCreate("winhttp.winhttprequest.5.1") - - If $notifyBenny Then - $oHTTP.Open("GET", "http://autoremotejoaomgcd.appspot.com/sendmessage?key=APA91bEejQPoLUIipcNpfeDvNoa5Bd48AASVBHRphY4ySu8U2FSfuDym2iHdkC_YJ2zyhhbVXVB0oRacd_QmDFsrNRATF0rT4b7pqIHj8EeUrOdq0s_gQq7BE0ERaUfHHikhet06D8ff8BUXe8uVZ5RqoI2ygWc48Q&message=RadHard:%20System-USB:%20" & $text & "&password=TannenbaumSaftSchorle") - $oHTTP.Send() - EndIf - - If $notifyDennis Then - $oHTTP.Open("GET", "http://autoremotejoaomgcd.appspot.com/sendmessage?key=APA91bHCZjLkmISyt3lHjn-ym1Md9SymV5hKq05gxuPKXSMVm6e1rphU55sjiQNfQOIUpzoNqCU8kGXLwDJTRksMBRgk62dW5N_Go69dchFvfYqNIKKrvG26FGBPjKea06PU6IPxLPm0WWSM53k7QIQj0EZtMSNxpg&message=RadHard:%20System-USB:%20" & $text & "&password=mimosa") - $oHTTP.Send() - EndIf - - If $notifyStefan Then - If Not StringInStr ( $text, "Percent%20done" ) Then - $oHTTP.Open("GET", "http://autoremotejoaomgcd.appspot.com/sendmessage?key=APA91bEejQPoLUIipcNpfeDvNoa5Bd48AASVBHRphY4ySu8U2FSfuDym2iHdkC_YJ2zyhhbVXVB0oRacd_QmDFsrNRATF0rT4b7pqIHj8EeUrOdq0s_gQq7BE0ERaUfHHikhet06D8ff8BUXe8uVZ5RqoI2ygWc48Q&message=RadHardSMS015787957348:%20System-USB:%20" & $text & "&password=TannenbaumSaftSchorle") - $oHTTP.Send() - EndIf - EndIf - - $oHTTP = 0 - -EndFunc ;==>SendNtfctn - Func SendNtfctn($text, $system) $notify = IniRead("config_" & $system & ".ini", "various", "Notify", "1") ; notifycode $notifycustom = IniRead("config_" & $system & ".ini", "various", "NotifyCustom", "") ; chip number currently mounted into the system - $notifyBenny =BitAND($notify, 1) - $notifyDennis=BitAND($notify, 2) - $notifyStefan=BitAND($notify, 4) + $notifyBenny = BitAND($notify, 1) + $notifyDennis = BitAND($notify, 2) + $notifyStefan = BitAND($notify, 4) $text = urlencode($text) $oHTTP = ObjCreate("winhttp.winhttprequest.5.1") If $notifyBenny Then - $oHTTP.Open("GET", "http://autoremotejoaomgcd.appspot.com/sendmessage?key=APA91bEejQPoLUIipcNpfeDvNoa5Bd48AASVBHRphY4ySu8U2FSfuDym2iHdkC_YJ2zyhhbVXVB0oRacd_QmDFsrNRATF0rT4b7pqIHj8EeUrOdq0s_gQq7BE0ERaUfHHikhet06D8ff8BUXe8uVZ5RqoI2ygWc48Q&message=RadHard:%20System-" & $system & ":%20" & $text & "&password=TannenbaumSaftSchorle") - $oHTTP.Send() + $oHTTP.Open("GET", "http://autoremotejoaomgcd.appspot.com/sendmessage?key=APA91bEejQPoLUIipcNpfeDvNoa5Bd48AASVBHRphY4ySu8U2FSfuDym2iHdkC_YJ2zyhhbVXVB0oRacd_QmDFsrNRATF0rT4b7pqIHj8EeUrOdq0s_gQq7BE0ERaUfHHikhet06D8ff8BUXe8uVZ5RqoI2ygWc48Q&message=RadHard:%20System-" & $system & ":%20" & $text & "&password=TannenbaumSaftSchorle") + $oHTTP.Send() EndIf If $notifyDennis Then - $oHTTP.Open("GET", "http://autoremotejoaomgcd.appspot.com/sendmessage?key=APA91bHCZjLkmISyt3lHjn-ym1Md9SymV5hKq05gxuPKXSMVm6e1rphU55sjiQNfQOIUpzoNqCU8kGXLwDJTRksMBRgk62dW5N_Go69dchFvfYqNIKKrvG26FGBPjKea06PU6IPxLPm0WWSM53k7QIQj0EZtMSNxpg&message=RadHard:%20System-" & $system & ":%20" & $text & "&password=mimosa") - $oHTTP.Send() + $oHTTP.Open("GET", "http://autoremotejoaomgcd.appspot.com/sendmessage?key=APA91bHCZjLkmISyt3lHjn-ym1Md9SymV5hKq05gxuPKXSMVm6e1rphU55sjiQNfQOIUpzoNqCU8kGXLwDJTRksMBRgk62dW5N_Go69dchFvfYqNIKKrvG26FGBPjKea06PU6IPxLPm0WWSM53k7QIQj0EZtMSNxpg&message=RadHard:%20System-" & $system & ":%20" & $text & "&password=mimosa") + $oHTTP.Send() EndIf If $notifyStefan Then - If Not StringInStr ( $text, "Percent%20done" ) Then - $oHTTP.Open("GET", "http://autoremotejoaomgcd.appspot.com/sendmessage?key=APA91bEejQPoLUIipcNpfeDvNoa5Bd48AASVBHRphY4ySu8U2FSfuDym2iHdkC_YJ2zyhhbVXVB0oRacd_QmDFsrNRATF0rT4b7pqIHj8EeUrOdq0s_gQq7BE0ERaUfHHikhet06D8ff8BUXe8uVZ5RqoI2ygWc48Q&message=RadHardSMS015787957348:%20System-" & $system & ":%20" & $text & "&password=TannenbaumSaftSchorle") - $oHTTP.Send() - EndIf + If Not StringInStr($text, "Percent%20done") Then + $oHTTP.Open("GET", "http://autoremotejoaomgcd.appspot.com/sendmessage?key=APA91bEejQPoLUIipcNpfeDvNoa5Bd48AASVBHRphY4ySu8U2FSfuDym2iHdkC_YJ2zyhhbVXVB0oRacd_QmDFsrNRATF0rT4b7pqIHj8EeUrOdq0s_gQq7BE0ERaUfHHikhet06D8ff8BUXe8uVZ5RqoI2ygWc48Q&message=RadHardSMS015787957348:%20System-" & $system & ":%20" & $text & "&password=TannenbaumSaftSchorle") + $oHTTP.Send() + EndIf EndIf $oHTTP = 0 diff --git a/bot/Functions/TemperatureControl.au3 b/bot/Functions/TemperatureControl.au3 index 97a5aaa..f882ae8 100755 --- a/bot/Functions/TemperatureControl.au3 +++ b/bot/Functions/TemperatureControl.au3 @@ -80,6 +80,7 @@ Func SetTemperature($system, $temperature) $signum = "" EndIf $temperature = $temperature * 100 + $temperature = Int ( $temperature ) $command = "SP@" & $signum & PadZeros($temperature,5); $oHTTP = ObjCreate("winhttp.winhttprequest.5.1") diff --git a/bot/Functions/WaitForTemperature.au3 b/bot/Functions/WaitForTemperature.au3 index be82489..f3f7b89 100755 --- a/bot/Functions/WaitForTemperature.au3 +++ b/bot/Functions/WaitForTemperature.au3 @@ -1,4 +1,4 @@ -If $currentline[$cellTemp][0] == $temperaturebefore Then +If $TempCooling == $temperaturebefore Then $tempcorrent = True Else $tempcorrent = False @@ -11,32 +11,36 @@ While Not $tempcorrent $tempcorrent = True ExitLoop 1 EndIf - If ($currentline[$cellTemp][0] < $currenttemp + 0.5 And $currentline[$cellTemp][0] > $currenttemp - 0.5) Then + If ($TempCooling < $currenttemp + 0.5 And $TempCooling > $currenttemp - 0.5) Then $tempcorrent = True Else If $tempwaiti > 5 Then ; after 30 minutes give up waiting $currenttempchip = GetTemperatureForSure("Chip" & $system) - RefreshGUISendNtfct("Cooling could not reach needed temperature of " & $currentline[$cellTemp][0] & " °C within 30 minutes. Current cooling temperature is " & $currenttemp & ", chip temperature is " & $currenttempchip & ". Will proceed anyway.") + RefreshGUISendNtfct("Cooling could not reach needed temperature of " & $TempCooling & " °C within 30 minutes. Current cooling temperature is " & $currenttemp & ", chip temperature is " & $currenttempchip & ". Will proceed anyway.") $tempcorrent = True ExitLoop 1 EndIf - $completelog &= _NowCalc() & " Current temperature is " & $currenttemp & "." & @CRLF & "Will set it to " & $currentline[$cellTemp][0] & " and wait for 5 minutes." & @CRLF + $completelog &= _NowCalc() & " Current temperature is " & $currenttemp & "." & @CRLF & "Will set it to " & $TempCooling & " and wait for 5 minutes." & @CRLF Run(@AutoItExe & ' /AutoIt3ExecuteScript "' & @ScriptDir & '\Functions\RefreshGUI.au3" "' & $completelog & '"') $tempseti = 0 Do If $tempseti > 10 Then ; after 10 failed attempts $currenttempchip = GetTemperatureForSure("Chip" & $system) - RefreshGUISendNtfct("Could not set temperature to " & $currentline[$cellTemp][0] & ". Current cooling temperature is " & $currenttemp & " °C, chip temperature is " & $currenttempchip & ". Will proceed anyway.") + RefreshGUISendNtfct("Could not set temperature to " & $TempCooling & ". Current cooling temperature is " & $currenttemp & " °C, chip temperature is " & $currenttempchip & ". Will proceed anyway.") $tempcorrent = True ExitLoop 2 EndIf - $settemp = RunWait(@AutoItExe & ' /AutoIt3ExecuteScript "' & @ScriptDir & '\Functions\TemperatureControl.au3" "' & $system & '" "' & $currentline[$cellTemp][0] & '"', @ScriptDir) + $settemp = RunWait(@AutoItExe & ' /AutoIt3ExecuteScript "' & @ScriptDir & '\Functions\TemperatureControl.au3" "' & $system & '" "' & $TempCooling & '"', @ScriptDir) $tempseti = $tempseti + 1 Until $settemp == 0 ; until temperature could be set succesfully $tempwaiti= $tempwaiti + 1 Sleep(1000 * 60 * 5) ; wait 5 minutes between checks and sets of temperature EndIf WEnd -Sleep($waitfortemp * 60 * 1000) ; wait for user specified minutes -$currenttempchip = GetTemperatureForSure("Chip" & $system) ; get chip temperature before measurement start -$temperaturebefore = $currentline[$cellTemp][0] ; if next run has the same needed temperature as this run, dont check for cooling water temperature, see the first lines of this file for details +If $TempWaitFor > 0 Then + $completelog &= _NowCalc() & " Found user defined waiting time, will wait for " & $TempWaitFor & " minutes before proceeding." & @CRLF + Run(@AutoItExe & ' /AutoIt3ExecuteScript "' & @ScriptDir & '\Functions\RefreshGUI.au3" "' & $completelog & '"') + Sleep($TempWaitFor * 60 * 1000) ; wait for user specified minutes +EndIf +$temperaturebefore = $TempCooling ; if next run has the same needed temperature as this run, dont check for cooling water temperature, see the first lines of this file for details +$temperaturechipstart = GetTemperatureForSure("Chip" & $system) \ No newline at end of file diff --git a/bot/Functions/Watchrun.au3 b/bot/Functions/Watchrun.au3 index 7607999..66c091f 100755 --- a/bot/Functions/Watchrun.au3 +++ b/bot/Functions/Watchrun.au3 @@ -1,8 +1,8 @@ $i = 0 Do $oldcounter = GetCounter() - $percentCurrent = Round($oldcounter / $eventnum * 100, 2) - $percentoverall = Round(($oldcounter / $eventnum / $totalruns + $totalrealruns / $totalruns) * 100, 2) + $percentCurrent = Round($oldcounter / $events * 100, 2) + $percentoverall = Round(($oldcounter / $events / $totalruns + $totalrealruns / $totalruns) * 100, 2) If Not $estimate And $percentoverall > 1 Then $calcsecestimate = _DateDiff('s', $starttime, _NowCalc()) ; n for minites, s for seconds $calcsecestimate = $calcsecestimate * 100 / $percentoverall @@ -19,19 +19,20 @@ Do Run(@AutoItExe & ' /AutoIt3ExecuteScript "' & @ScriptDir & '\Functions\RefreshGUI.au3" "' & $percentoverall & '" "' & $percentCurrent & '"') Run(@AutoItExe & ' /AutoIt3ExecuteScript "' & @ScriptDir & '\Functions\RefreshGUI.au3" "' & $completelog & '"') If Not Mod($i, 30) Then - Run(@AutoItExe & ' /AutoIt3ExecuteScript "' & @ScriptDir & '\Functions\WriteTemperatureLog.au3" "' & $savedir & '\' & $runnum & '\temperature.log" "' & $system & '"', @ScriptDir) + Run(@AutoItExe & ' /AutoIt3ExecuteScript "' & @ScriptDir & '\Functions\WriteTemperatureLog.au3" "' & $savedir & '\' & $runnumber & '\temperature.log" "' & $system & '"', @ScriptDir) Run(@AutoItExe & ' /AutoIt3ExecuteScript "' & @ScriptDir & '\Functions\SendNtfctn.au3" "' & $system & '" "Percent done: ' & $percentoverall & ' (' & $totalrealruns & '/' & $totalruns & ')"', @ScriptDir) EndIf Sleep(10000) If ($oldcounter == GetCounter()) Then StopRun() - Run(@AutoItExe & ' /AutoIt3ExecuteScript "' & @ScriptDir & '\Functions\SendNtfctn.au3" "' & $system & '" "Skipped run ' & $runnum & ', event counter froze."', @ScriptDir) - Run(@AutoItExe & ' /AutoIt3ExecuteLine "MsgBox(48, ''Warning'', ''Skipped run ' & $runnum & ', event counter froze.'')"') - $completelog &= _NowCalc() & " Skipped run " & $runnum & ", event counter froze." & @CRLF + Run(@AutoItExe & ' /AutoIt3ExecuteScript "' & @ScriptDir & '\Functions\SendNtfctn.au3" "' & $system & '" "Skipped run ' & $runnumber & ', event counter froze."', @ScriptDir) + Run(@AutoItExe & ' /AutoIt3ExecuteLine "MsgBox(48, ''Warning'', ''Skipped run ' & $runnumber & ', event counter froze.'')"') + $completelog &= _NowCalc() & " Skipped run " & $runnumber & ", event counter froze." & @CRLF Run(@AutoItExe & ' /AutoIt3ExecuteScript "' & @ScriptDir & '\Functions\RefreshGUI.au3" "' & $completelog & '"') - FileDelete($savedir & "\" & $runnum & "\*") - DirRemove($savedir & "\" & $runnum) + FileDelete($savedir & "\" & $runnumber & "\*") + DirRemove($savedir & "\" & $runnumber) ContinueLoop 2 EndIf $i = $i + 1 -Until (GetCounter() > $eventnum) \ No newline at end of file +Until (GetCounter() > $events) +$temperaturechipend = GetTemperatureForSure("Chip" & $system) \ No newline at end of file diff --git a/bot/Functions/mysql_functions.au3 b/bot/Functions/mysql_functions.au3 new file mode 100755 index 0000000..7605ba6 --- /dev/null +++ b/bot/Functions/mysql_functions.au3 @@ -0,0 +1,127 @@ +#cs ---------------------------------------------------------------------------- + + AutoIt Version: 3.3.8.1 + Author: myName + + Script Function: + Template AutoIt script. + +#ce ---------------------------------------------------------------------------- +#include "mysql_header.au3" +;~ $objErr = ObjEvent("AutoIt.Error","MyErrFunc2") + +Global $sql= GetSQLConnection() +;~ MsgBox(0,"",$sql) + +;~ $num = ReturnNumSuitRuns("34", 5, 0, "Fe55", "PXI") +;~ MsgBox(0,"",$num) +;~ $var= ReturnSuitRun("34", 3, 3, "Fe55", "USB") +;~ If $var[0] = "" Then MsgBox(0, "", "Hello") +;~ ConsoleWrite("ReturnSuitRunId2: " & $num & @CRLF) + +;~ ; $suitrun = ReturnSuitRun("Mi34", "3", "3", "Fe55", "USB") + + + +Func UpdateDBwithRun($id, $datetime, $temperaturechipstart, $temperaturechipend, $vref, $calcsec, $programversion, $path) + $sql = GetSQLConnection() + + $sqlstring = "UPDATE labbook SET " & _ + "datetime='" & $datetime & "', " & _ + "TempChipStart=" & $temperaturechipstart & ", " & _ + "TempChipEnd=" & $temperaturechipend & ", " & _ + "VRef=" & $vref & ", " & _ + "Runtime=" & $calcsec & ", " & _ + "MABSV='" & $programversion & "', " & _ + "StorePath='" & $path & "' " & _ + "WHERE id=" & $id + + ConsoleWrite($sqlstring) + _Query($sql, $sqlstring) + + _MySQLEnd($sql) +EndFunc + +Func ReturnNumSuitRuns($ChipGen, $ChipNum, $ChipRadiation, $RadiationSource, $System, $Clock=0) + $sql = GetSQLConnection() + + $ChipGen = StringReplace($ChipGen, "Mi", "", 1, 0) + $sqlstring = "ChipGen='" & $ChipGen & "' AND " & _ + "ChipNum='" & $ChipNum & "' AND " & _ + "RadiationSource='" & $RadiationSource & "' AND " & _ + "ChipRadiation=" & $ChipRadiation & " AND " & _ + "System='" & $System & "' AND " & _ + "datetime='1970-01-01 00:00:00' OR datetime IS NULL OR datetime='0000-00-00 00:00:00'" + If $clock <> 0 Then $sqlstring = $sqlstring & " AND Clock=" & $Clock + + $number = _CountDifferentRecords($sql, "labbook", "id", $sqlstring) + _MySQLEnd($sql) + ConsoleWrite("ReturnNumSuitRuns: " & $number & @CRLF) + Return $number +EndFunc + + +Func ReturnSuitRun($ChipGen, $ChipNum, $ChipRadiation, $RadiationSource, $System, $Clock=0) + $sql = GetSQLConnection() + + $ChipGen = StringReplace($ChipGen, "Mi", "", 1, 0) + $sqlstring = "SELECT id, CAST(TempCooling AS CHAR), TempWaitFor, Clock, runnumber, CAST(Matrix AS CHAR), Events FROM labbook WHERE " + $sqlstring = $sqlstring & "ChipGen='" & $ChipGen & "' AND " + $sqlstring = $sqlstring & "ChipNum='" & $ChipNum & "' AND " + $sqlstring = $sqlstring & "RadiationSource='" & $RadiationSource & "' AND " + $sqlstring = $sqlstring & "ChipRadiation=" & $ChipRadiation & " AND " + $sqlstring = $sqlstring & "System='" & $System & "' AND " + $sqlstring = $sqlstring & "datetime='1970-01-01 00:00:00' OR datetime IS NULL OR datetime='0000-00-00 00:00:00'" + If $clock <> 0 Then $sqlstring = $sqlstring & " AND Clock=" & $Clock + $sqlstring = $sqlstring & " LIMIT 1" +;~ ConsoleWrite($sqlstring & @CRLF) + + $var = _Query($sql, $sqlstring) + + Local $runparam[7] + If NOT $var.EOF Then + With $var + $runparam[0] = .Fields("id").value + $runparam[1] = .Fields("CAST(TempCooling AS CHAR)").value + $runparam[2] = .Fields("TempWaitFor").value + $runparam[3] = .Fields("Clock").value + $runparam[4] = .Fields("runnumber").value + $runparam[5] = .Fields("CAST(Matrix AS CHAR)").value + $runparam[6] = .Fields("Events").value + EndWith + EndIf + _MySQLEnd($sql) + Return $runparam +EndFunc + +Func GetSQLConnection() + Return _MySQLConnect("radhard", "mimosa88", "radhard", "jspc29.x-matter.uni-frankfurt.de") +EndFunc ;==>GetSQLConnection + +;~ Func MyErrFunc2() + +;~ $hexnum=hex($objErr.number,8) + +;~ Msgbox(0,"","We intercepted a COM Error!!" & @CRLF & @CRLF & _ +;~ "err.description is: " & $objErr.description & @CRLF & _ +;~ "err.windescription is: " & $objErr.windescription & @CRLF & _ +;~ "err.lastdllerror is: " & $objErr.lastdllerror & @CRLF & _ +;~ "err.scriptline is: " & $objErr.scriptline & @CRLF & _ +;~ "err.number is: " & $hexnum & @CRLF & _ +;~ "err.source is: " & $objErr.source & @CRLF & _ +;~ "err.helpfile is: " & $objErr.helpfile & @CRLF & _ +;~ "err.helpcontext is: " & $objErr.helpcontext _ +;~ ) +;~ exit +;~ EndFunc + + +;~ Local $var= mysql_fetch_array($sqlstring, $sql) +;~ If IsArray($var) Then + +;~ ConsoleWrite("inside!" & @CRLF) +;~ For $i = 0 to Ubound($var, 1) - 1 +;~ For $j = 0 To UBound($var, 2) - 1 +;~ ConsoleWrite($var[$i][$j][1] & @CRLF) +;~ Next +;~ Next \ No newline at end of file diff --git a/bot/Functions/mysql_header.au3 b/bot/Functions/mysql_header.au3 new file mode 100755 index 0000000..719c78c --- /dev/null +++ b/bot/Functions/mysql_header.au3 @@ -0,0 +1,654 @@ +#include-once + +#cs + Function Name: _MySQLConnect + Description: Initiate a connection to a MySQL database. + Parameter(s): $username - The username to connect to the database with. + $password - The password to connect to the database with. $Database - Database to connect to. + $server - The server your database is on. + $driver (optional) the ODBC driver to use (default is "{MySQL ODBC 3.51 Driver}" + Requirement(s): Autoit 3 with COM support + Return Value(s): On success returns the connection object for subsequent functions. On failure returns 0 and sets @error + @Error = 1 + Error opening connection + @Error = 2 + MySQL ODBC Driver not installed. + Author(s): cdkid +#ce + +Func _MySQLConnect($sUsername, $sPassword, $sDatabase, $sServer, $sDriver = "{MySQL ODBC 3.51 Driver}", $iPort=3306) + Local $v = StringMid($sDriver, 2, StringLen($sDriver) - 2) + Local $key = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $val = RegRead($key, $v) + If @error or $val = "" Then + SetError(2) + Return 0 + EndIf + $ObjConn = ObjCreate("ADODB.Connection") + $Objconn.open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";PORT="&$iPort) + If @error Then + SetError(1) + Return 0 + Else + Return $ObjConn + EndIf +EndFunc ;==>_MySQLConnect + + +Func mysql_fetch_array($arrObj, $objConn) + If IsObj($objConn) Then + Local $iCol = 0, $iFields = 0 + Local $__mysql_temp = $objConn.Execute($arrObj) + If @error Then Return False + + Local $__mysql_field_count = $__mysql_temp.Fields.Count + Local $__mysql_arr[1][$__mysql_field_count][2] + + Do + For $i In $__mysql_temp.Fields + ReDim $__mysql_arr[$iCol + 1][$iFields + 1][2] + + $__mysql_arr[$iCol][$iFields][0] = $i.Name + $__mysql_arr[$iCol][$iFields][1] = $i.Value + + $iFields += 1 + Next + + $iCol +=1 + $iFields = 0 + $__mysql_temp.MoveNext + Until $__mysql_temp.EOF + + Return $__mysql_arr + Else + Return False + EndIf +EndFunc ;==>mysql_fetch_array + +#cs + Function name: _Query + Description: Send a query to the database + Parameter(s): $oConnectionObj - As returned by _MySQLConnect. $query - The query to execute + Return Value(s):On success returns the query result. On failure returns 0 and sets @error to 1 + Requirement(s):Autoit3 with COM support + Author(s): cdid +#ce + + +Func _Query($oConnectionObj, $sQuery) + If IsObj($oConnectionObj) Then + Return $oConnectionobj.execute ($sQuery) + EndIf + If @error Then + SetError(1) + Return 0 + EndIf + +EndFunc ;==>_Query + +#cs + Function name: _MySQLEnd + Description: Closes the database connection (see notes!) + Parameter(s): $oConnectionObj - The connection object as returned by _MySQLConnect() + Requirement(s):Autoit 3 with COM support + Return Value(s):On success returns 1. On failure returns 0 and sets @error to 1 + Author(s): cdkid +#ce + +Func _MySQLEnd($oConnectionObj) + If IsObj($oConnectionObj) Then + $oConnectionObj.close + Return 1 + Else + SetError(1) + Return 0 + EndIf +EndFunc ;==>_MySQLEnd + +#cs + Function name: _AddRecord + Description: Adds a record to the specified table + Note(s): to add to multiple columns use an array with one blank element at the end as the $sColumn, and $value parameter + Parameter(s): $oConnectionObj - As returned by _MySQL Connect. $sTable - The table to put the record in + $row - The row to put the record in. $value - The value to put into the row + $vValue - OPTIONAL default will be default for the column (will not work with array, see notes) + Requirement(s): Autoit 3 with COM support + Return value(s): On success returns 1. If the connectionobj is not an object returns 0 and sets @error to 2. If there is any other error returns 0 and sets @error to 1. + Author(s): cdkid +#ce + +Func _AddRecord($oConnectionObj, $sTable, $vRow, $vValue = "") + If IsObj($oConnectionObj) Then + $query = "INSERT INTO " & $sTable & " (" + + If IsArray($vRow) Then + For $i = 0 To UBound($vRow, 1) - 1 + If $i > 0 And $i <> UBound($vRow, 1) - 1 Then + $query = $query & "," & $vRow[$i] & "" + ElseIf $i = UBound($vRow, 1) - 1 And $vRow[$i] <> "" Then + $query = $query & "," & $vRow[$i] & ") VALUES(" + ElseIf $i = 0 Then + $query = $query & "" & $vRow[$i] & "" + ElseIf $vRow[$i] = "" Then + $query = $query & ") VALUES(" + EndIf + Next + EndIf + If Not IsArray($vRow) And Not IsArray($vValue) And Not IsInt($vValue) Then + $oConnectionobj.execute ("INSERT INTO " & $sTable & " (" & $vRow & ") VALUES('" & $vValue & "')") + return 1 + ElseIf IsInt($vValue) And Not IsArray($vRow) And Not IsArray($vValue) Then + $oconnectionobj.execute ("INSERT INTO " & $sTable & " (" & $vRow & ") VALUES(" & $vValue & ")") + return 1 + EndIf + + If IsArray($vValue) Then + For $i = 0 To UBound($vValue, 1) - 1 + If $i > 0 And $i <> UBound($vValue, 1) - 1 And Not IsInt($vValue[$i]) Then + $query = $query & ",'" & $vValue[$i] & "'" + ElseIf $i = UBound($vValue, 1) - 1 And $vValue[$i] <> "" And Not IsInt($vValue[$i]) Then + $query = $query & ",'" & $vValue[$i] & "');" + ElseIf $i = 0 And Not IsInt($vValue[$i]) Then + $query = $query & "'" & $vValue[$i] & "'" + ElseIf $vValue[$i] = "" Then + $query = $query & ");" + ElseIf IsInt($vValue[$i]) And $vValue[$i] <> "" Then + $query = $query & "," & $vValue[$i] + EndIf + Next + EndIf + If StringRight($query, 2) <> ");" Then + $query = $query & ");" + + EndIf + $oconnectionobj.execute ($query) + EndIf + If Not IsObj($oConnectionObj) Then + SetError(2) + Return 0 + EndIf + If @error And IsObj($oConnectionObj) Then + Return 0 + SetError(1) + Else + Return 1 + EndIf + +EndFunc ;==>_AddRecord + + +#cs + Function name: _DeleteRecord + Description: Deletes a record from the specified table + Parameter(s): $oConnectionObj - As returned by _MySQLConnect. $sTable - The table to delete from. + $sColumn - The column to check value (see the example in the next post) $vRecordVal - + The value to check in $sColumn (see example). + $iLimit (optional) - the max number of record to delete if multiple match the criteria (default 1) + Return Value(s): On success returns 1. If there $oConnectionObj is not an object returns 0 and sets @error to 1. If there are any other errors returns 0 and sets @error to 2 + Requirement(s): Autoit 3 with COM support +#ce + +Func _DeleteRecord ($oConnectionObj, $sTable, $sColumn, $vRecordVal, $iLimit = 1) + If IsObj($oConnectionObj) And Not IsInt($vRecordVal) Then + $oconnectionobj.execute ("DELETE FROM " & $sTable & " WHERE " & $sColumn & " = '" & $vRecordVal & "' LIMIT " & $iLimit & ";") + ElseIf IsInt($vRecordVal) Then + $oconnectionobj.execute ("DELETE FROM " & $sTable & " WHERE " & $sColumn & " = " & $vRecordVal & " LIMIT " & $iLimit & ";") + If Not @error Then + Return 1 + ElseIf Not IsObj($oConnectionObj) Then + SetError(1) + Return 0 + ElseIf @error And IsObj($oConnectionObj) Then + SetError(2) + Return 0 + EndIf + EndIf +EndFunc ;==>_DeleteRecord + +#cs + Function name: _CreateTable() + Description: Creates a table + Parameters: $oConnectionObj - as returned by _MySQLConnect, $sTbl - The name of the table to create, $sPrimeKey - The name of the + primary key column. $keytype - The datatype of the primary key (default is integer), $sNotNull - "yes" = must be filled out whenever + a record is added "no" does not need to be filled out ("yes" default). $keyautoinc - "yes" = Auto incrememnts "no" = does not. + $sType - The table type (default is InnoDB) + Requirements: Autoit V3 with COM support + Return value(s): on success returns 1 on failure sets @error to 1 and returns 0 + Author: cdkid +#ce + +Func _CreateTable($oConnectionObj, $sTbl, $sPrimeKey, $keytype = "INTEGER", $sNotNull = "yes", $keyautoinc = "yes", $sType = "InnoDB") + If IsObj($oConnectionObj) And Not @error Then + $str = "CREATE TABLE " & $sTbl & " " & "(" & $sPrimeKey & " " & $keytype & " UNSIGNED" + If $sNotNull = "yes" Then + $str = $str & " NOT NULL" + EndIf + + If $keyautoinc = "yes" Then + $str = $str & " AUTO_INCREMENT," + EndIf + + $str = $str & " PRIMARY KEY (" & $sPrimeKey & " )" & " ) " & "TYPE = " & $sType & ";" + $oConnectionObj.execute ($str) + Return 1 + + + ElseIf @error Then + Return 0 + SetError(1) + EndIf + +EndFunc ;==>_CreateTable + +#cs + Function Name: _CreateColumn + Description: Creates a column in the given table + Requirements: AutoitV3 with COM support + Parameters: $oConnectionObj - as returned by _MySQLConnect. $sTable - the name of the table to add the column to. + $sAllowNull - if 'yes' then does not add 'NOT NULL' to the SQL statement (default 'yes') $sDataType - The data type of the column + default('VARCHAR(45)'). $sAutoInc - if 'yes' adds 'AUTO_INCREMENT' to the MySQL Statement (for use with Integer types) + default('no'). $sUnsigned - if 'yes' adds 'UNSIGNED' to the MySQL statement. default('no') $vDefault - the default value of the column + default('') + Author: cdkid +#ce + +Func _CreateColumn($oConnectionObj, $sTable, $sColumn, $sAllowNull = "no", $sDataType = "VARCHAR(45)", $sAutoInc = "no", $sUnsigned = "no", $vDefault = '') + If IsObj($oConnectionObj) And Not @error Then + $str = "ALTER TABLE `" & $sTable & "` ADD COLUMN `" & $sColumn & "` " & $sDataType & " " + If $sAllowNull = "yes" Then + $str = $str & "NOT NULL " + EndIf + If $sAutoInc = 'yes' Then + $str = $str & "AUTO_INCREMENT " + EndIf + If $sUnsigned = 'yes' Then + $str = $str & "UNSIGNED " + EndIf + $str = $str & "DEFAULT '" & $vDefault & "';" + $oConnectionObj.execute ($str) + Return 1 + Else + SetError(1) + Return 0 + EndIf + +EndFunc ;==>_CreateColumn + +#cs + Function Name: _DropCol() + Description: Delete a column from the given table + Requirements: AutoitV3 with COM support + Parameters: $oConnectionObj - As returned by _MySQLConnect(). $sTable - The name of the table to delete the column from + $sColumn - THe name of the column to delete + Author: cdkid +#ce + +Func _DropCol($oConnectionObj, $sTable, $sColumn) + If IsObj($oConnectionObj) & Not @error Then + $oConnectionObj.execute ("ALTER TABLE " & $sTable & " DROP COLUMN " & $sColumn & ";") + Return 1 + ElseIf @error Then + SetError(1) + Return 0 + EndIf +EndFunc ;==>_DropCol + +#cs + Function Name: _DropTbl() + Description: Deletes a table from the database + Requirements: AutoitV3 with COM support + Parameters: $oConnectionObj - As returned by _MySQLConnect. $sTable - The name of the table to delete + Author: cdkid +#ce + +Func _DropTbl($oConnectionObj, $sTable) + If IsObj($oConnectionObj) And Not @error Then + $oConnectionObj.execute ("DROP TABLE " & $sTable & ";") + Return 1 + Else + SetError(1) + Return 0 + EndIf +EndFunc ;==>_DropTbl + + +#cs + Function name: _CountRecords() + Description: Get the number of records in the specified column + Parameters: $oConnectionObj - As returned by _MySQLConnect. $sTable - The name of the table that the column is in + $value - If not = "" then it is put in the select statement in the WHERE clause (default "") + Return value(s): On success returns the number of records. On failure sets @error to 1 and returns 0 + Author: cdkid +#ce +Func _CountRecords($oConnectionObj, $sTable, $sColumn, $vValue = '') + If IsObj($oConnectionObj) And Not @error Then + + If $sColumn <> "" And $vValue <> "" And Not IsInt($vValue) Then + $constr = "SELECT " & $sColumn & " FROM " & $sTable & " WHERE " & $sColumn & " = '" & $vValue & "'" + ElseIf $sColumn <> "" And $vValue = '' And Not IsInt($vValue) Then + $constr = "SELECT " & $sColumn & " FROM " & $sTable + ElseIf IsInt($vValue) And $sColumn <> '' And $vValue <> '' Then + $constr = "SELECT " & $sColumn & " FROM " & $sTable & " WHERE " & $sColumn & " = " & $vValue + EndIf + $sql2 = ObjCreate("ADODB.Recordset") + $sql2.cursorlocation = 3 + $sql2.open ($constr, $oConnectionObj) + With $sql2 + $ret = .recordcount + EndWith + $sql2.close + Return $ret + Else + SetError(1) + Return 0 + EndIf +EndFunc ;==>_CountRecords + + +#cs + Function name: _CountRecords() + Description: Get the number of records in the specified column + Parameters: $oConnectionObj - As returned by _MySQLConnect. $sTable - The name of the table that the column is in + $value - If not = "" then it is put in the select statement in the WHERE clause (default "") + Return value(s): On success returns the number of records. On failure sets @error to 1 and returns 0 + Author: cdkid +#ce +Func _CountDifferentRecords($oConnectionObj, $sTable, $sColumn, $vValue = '') + If IsObj($oConnectionObj) And Not @error Then + + If $sColumn <> "" And $vValue <> "" Then + $constr = "SELECT " & $sColumn & " FROM " & $sTable & " WHERE " & $vValue + EndIf + $sql2 = ObjCreate("ADODB.Recordset") + $sql2.cursorlocation = 3 + $sql2.open ($constr, $oConnectionObj) + With $sql2 + $ret = .recordcount + EndWith + $sql2.close + Return $ret + Else + SetError(1) + Return 0 + EndIf +EndFunc ;==>_CountRecords + +#cs + Function name: _CountTables + Description: Counts the number of tables in the database + Parameter(s): $oConnectionObj - As returned by _MySQLConnect + Return value(s): if error - returns 0 and sets @error to 1. on success returns the number of tables in the database + Author: cdkid +#ce + +Func _CountTables($oConnectionObj) + If IsObj($oConnectionObj) Then + $quer = $oConnectionObj.execute ("SHOW TABLES;") + $i = 0 + With $quer + While Not .EOF + $i = $i + 1 + .MoveNext + WEnd + EndWith + Return $i + EndIf + If @error Then + SetError(1) + Return 0 + EndIf + +EndFunc ;==>_CountTables + +#cs + Function name: _GetColNames + Description: Get's the names of all columns in a specified table + Parameters: $oConnectionObj - As returned by _MySQLConnect. $sTable - The name of the table to get the column names from + Return values: On success returns an array where $array[0] is the number of elements in the array and all the rest are column names. + On failure returns 0 and sets @error to 1 + Author: cdkid +#ce +Func _GetColNames($oConnectionObj, $sTable) + If IsObj($oConnectionObj) And Not @error Then + Dim $ret[1], $rs + + $rs = $oConnectionObj.execute ("SHOW COLUMNS FROM " & $sTable & ";") + + With $rs + While Not .EOF + + ReDim $ret[UBound($ret, 1) + 1] + $ret[UBound($ret, 1) - 1] = $rs.Fields (0).Value + .MoveNext + WEnd + EndWith + $ret[0] = UBound($ret, 1) - 1 + Return $ret + EndIf + If @error Then + Return 0 + SetError(1) + EndIf +EndFunc ;==>_GetColNames + + +#cs + Function name: _GetTblNames + Description: Gets the names of all tables in the database + Parameters: $oConnectionObj - As returned by _MySQLConnect + Return value(s): On success returns an array where $array[0] is the number of tables and $array[n] is the nth table's name + on failure - returns 0 and sets @error to 1 + Author: cdkid +#ce + +Func _GetTblNames($oConnectionObj) + If IsObj($oConnectionObj) Then + Dim $ret[1] + $quer = $oConnectionObj.execute ("SHOW TABLES;") + With $quer + While Not .eof + ReDim $ret[UBound($ret, 1) + 1] + $ret[UBound($ret, 1) - 1] = .fields (0).value + .movenext + WEnd + EndWith + $ret[0] = UBound($ret, 1) - 1 + Return $ret + EndIf +EndFunc ;==>_GetTblNames + +#cs + Function name: _GetColVals + Description: Gets all of the values of a specified column in a specified table + Parameters: $oConnectionObj - As returned by _MySQLConnect(), $sTable - the table that the column is in + $sColumn - the column to get values from. + Return value(s): On success returns an array where $array[0] is the number of values and $array[n] is the Nth value + On failure sets @error to 1 and returns 0 + Author: cdkid +#ce + +Func _GetColVals($oConnectionObj, $sTable, $sColumn) + If IsObj($oConnectionObj) Then + Dim $ret[1] + $quer = $oConnectionObj.execute ("SELECT " & $sColumn & " FROM " & $sTable & ";") + With $quer + While Not .EOF + ReDim $ret[UBound($ret, 1) + 1] + $ret[UBound($ret, 1) - 1] = .Fields (0).value + .MoveNext + WEnd + EndWith + $ret[0] = UBound($ret, 1) - 1 + Return $ret + EndIf +EndFunc ;==>_GetColVals + +#cs + Function name: _GetColCount + Description: Gets the number of columns in the specified table + Parameters: $oConnectionObj - As returned by _MySQLConnect(). $sTable - the table to count the columns in + Return Value(s): On success returns the number of columns in the table. On failure returns -1 and sets @error to 1 + Author: cdkid +#ce +Func _GetColCount($oConnectionObj, $sTable) + If IsObj($oConnectionObj) Then + $quer = $oConnectionObj.execute ("SHOW COLUMNS IN " & $sTable) + With $quer + $i = 0 + While Not .eof + $i = $i + 1 + .movenext + WEnd + EndWith + Return $i + EndIf + If @error Then + Return -1 + SetError(1) + EndIf + +EndFunc ;==>_GetColCount + +#cs + Function name: _GetColType + Description: Gets the DATA TYPE of the specified column + Parameters: $oConnectionObj - As returned by _MySQLConnect(). $sTable - the table that the column is in. $sColumn - the column + to retrieve the data type from. + Return value(s): On success returns the data type of the column. On failure returns 0 and sets @error to 1 + Author: cdkid +#ce +Func _GetColType($oConnectionObj, $sTable, $sColumn) + If IsObj($oConnectionObj) Then + $quer = $oConnectionObj.execute ("SHOW COLUMNS IN " & $sTable) + With $quer + $i = 0 + While Not .eof + If .fields (0).value = $sColumn Then + $ret = .fields (1).value + EndIf + .MoveNext + WEnd + EndWith + Return $ret + EndIf + If @error Then + Return 0 + SetError(1) + EndIf +EndFunc ;==>_GetColType + +#cs + Function: _GetDBNames + Description: Get a count and list of all databases on current server. + Parameters: $oConObj - As returned by _MySQLConnect + Return Value(s): Success - An array where $array[0] is the number of databases and $array[n] is the nth database name. + Failure - -1 and sets @error to 1 + Author: cdkid +#ce +Func _GetDBNames($conobj) + If IsObj($conobj) Then + Local $arr[1], $m + $m = $conobj.Execute ("SHOW DATABASES;") + With $m + While Not .eof + ReDim $arr[UBound($arr, 1) + 1] + $arr[UBound($arr, 1) - 1] = .Fields (0).Value + .MoveNext + WEnd + EndWith + $arr[0] = UBound($arr, 1) - 1 + Return $arr + Else + SetError(1) + Return -1 + EndIf +EndFunc ;==>_GetDBNames + +#cs + Function: _ChangeCon + Description: Change your connection string + Parameters: + $oConnectionObj + As returned by _MySQLConnect + $username + OPTIONAL: the new username to use + If omitted, the same username will be used. + $password + OPTIONAL: the new password to use + If omitted, the same password will be used. + $database + OPTIONAL: the new database to connect to + If omitted, the same database will be used. + $driver + OPTIONAL: the new driver to use + If omitted, the MySQL ODBC 3.51 DRIVER will be used. + $server + OPTIONAL: the new server to connect to + If omitted, the same server will be used. + $iPort + OPTIONAL: the new port to be used to connect + if omitted, the default port (3306) will be used + Return Value: + On success, a new connection object for use with subsequent functions. + On failure, -1 and sets @error to 1 + Author: cdkid +#ce + +Func _ChangeCon($oConnectionObj, $username = "", $password = "", $database = "", $driver = "", $server = "", $iPort = 0) + Local $constr, $db, $usn, $pwd, $svr + If IsObj($oConnectionObj) Then + $constr = $oConnectionObj.connectionstring + $constr = StringReplace($constr, 'Provider=MSDASQL.1;Extended Properties="', '') + $constr = StringSplit($constr, ";") + For $i = 1 To $constr[0] + If StringLeft($constr[$i], 3) = "UID" Then + If $username <> "" Then + $usn = $username + Else + $usn = StringMid($constr[$i], 5) + EndIf + $usn = StringTrimRight($usn, 1) + EndIf + If StringLeft($constr[$i], 3) = "PWD" Then + If $password <> "" Then + $pwd = $password + Else + $pwd = StringMid($constr[$i], 5) + EndIf + EndIf + If StringLeft($constr[$i], 8) = "DATABASE" Then + If $database <> "" Then + $db = $database + Else + $db = StringMid($constr[$i], 10) + EndIf + EndIf + If StringLeft($constr[$i], 6) = "SERVER" Then + If $server <> "" Then + $svr = $server + Else + $svr = StringMid($constr[$i], 8) + EndIf + EndIf + If StringLeft($constr[$i], 6) = "DRIVER" Then + If $driver <> "" Then + $dvr = $driver + Else + $dvr = "{MySQL ODBC 3.51 DRIVER}" + EndIf + EndIf + If StringLeft($constr[$i], 4) = "PORT" Then + if $iport <> 0 Then + $port = $iport + Else + $port = 3306 + EndIf + EndIf + Next + $oConnectionObj.close + $oConnectionObj.Open ("DATABASE=" & $db & ";DRIVER=" & $dvr & ";UID=" & $usn & ";PWD=" & $pwd & ";SERVER=" & $svr & ";PORT=" & $port & ";") + Return $oConnectionObj + Else + SetError(1) + Return -1 + EndIf +EndFunc ;==>_ChangeCon -- 2.43.0