Jump to content

bullmoose20

Members
 View Profile  See their activity

  • Posts

    15

  • Joined

  • Last visited

bullmoose20's Achievements

Seeker

Seeker (1/7)

1

Reputation

  1.    Daymond reacted to a post in a topic: MySQL UDFs (without ODBC)
  2. bullmoose20
    So the biggest issue I had was understanding the DLLStruct commands. Turned out that I needed to change it from char to byte and as well to upload the files with a proper header that included the filename. Retrieving and displaying the image can be done with the following (Kudos to GreenCan and others!). Essentially, _Execbin(PK) performs a query and returns the blob based on the clientID which is the Primary key on the table. Func _DisplayImage($sFile, $sDescription) Local $window_open, $aGIFDimension, $iOriginalW, $iOriginalH, $aClientSize, $nScale, $iPosX, $iPosY, $sTip, $iGUIWidth, $iGUIHeigth $window_open = WinList("ScanCartes") ; check If window already exists If $window_open[0][0] = 0 Then Else ; window already exist so only change the content _GIF_DeleteGIF($hGIF) ; first delete the previous image GUICtrlDelete($hGIFLabel) ; and delete the image label EndIf $aGIFDimension = _GIF_GetDimension($sFile) $iOriginalW = $aGIFDimension[0] $iOriginalH = $aGIFDimension[1] ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $aGIFDimension = ' & $aGIFDimension[0] & "W x " & $aGIFDimension[1] & "H" & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console $aClientSize = WinGetClientSize($hGui_ImageView) $aClientSize[0] = 220 $aClientSize[1] = 225 ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $aClientSize = ' & $aClientSize[0] & "W x " & $aClientSize[1] & "H" & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console ; Resize image to fit gui (trancexx) $nScale = 1 While 1 If $aClientSize[0] - 50 < $aGIFDimension[0] Or $aClientSize[1] - 70 < $aGIFDimension[1] Then $nScale /= 1.01 $aGIFDimension[1] = Round($aGIFDimension[1] * $nScale) $aGIFDimension[0] = Round($aGIFDimension[0] * $nScale) ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $nScale = ' & $nScale & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console Else ExitLoop EndIf WEnd While 1 If $aClientSize[0] - 50 > $aGIFDimension[0] Or $aClientSize[1] - 70 > $aGIFDimension[1] Then $nScale *= 1.01 $aGIFDimension[1] = Round($aGIFDimension[1] * $nScale) $aGIFDimension[0] = Round($aGIFDimension[0] * $nScale) ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $nScale = ' & $nScale & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console Else ExitLoop EndIf WEnd ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $aGIFDimension = ' & $aGIFDimension[0] & "W x " & $aGIFDimension[1] & "H" & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console $iPosX = (@DesktopWidth - $aGIFDimension[0]) / 2 $iPosY = 220 If $iPosY + $aGIFDimension[1] > $aClientSize[1] - 50 Then $iPosY -= $aGIFDimension[1] - $aClientSize[1] + 50 ; not to cover buttons $hGIF = _GUICtrlCreateGIF($sFile, "", $iPosX, $iPosY, $aGIFDimension[0], $aGIFDimension[1]) GUICtrlSetResizing(-1, 768 + 128 + 8 + 32) ; $GUI_DOCKSIZE + $GUI_DOCKVCENTER + $GUI_DOCKHCENTER + $GUI_DOCKTOP $sTip = "Size: " & $iOriginalW & " x " & $iOriginalH If $nScale < 1 Then $sTip &= @LF & "Resized to fit to: " & $aGIFDimension[0] & " x " & $aGIFDimension[1] GUICtrlSetTip($hGIF, $sTip, StringRegExpReplace($sFile, ".*\\", ""), 1) $hGIFLabel = GUICtrlCreateLabel($sDescription, 70, $iPosY + $aGIFDimension[1] + 5, @DesktopWidth - 140, 20, 1) ; $SS_CENTER GUISetState() Return EndFunc ;==>_DisplayImage Func _ExecBin($iItem) Local $aResult, $StringRemainder, $sDescription, $hFile, $iMaxRows, $Primary_Key, $sFileextension, $sEndOfFileNameFlag, $aTIFFDimension If $bGui_ImageView Then FileDelete($sFileName) ; delete previous file in temp folder $Primary_Key = $iItem $sPrimary = "NoClient" $sSQL = "SELECT Attachement FROM " & $dbname & "." & $sTableClients & " WHERE " & $sPrimary & "='" & $Primary_Key & "'" If $iDebug Then ConsoleWrite(@ScriptLineNumber & " " & $sSQL & @CR) $connected = _MySQL_Real_Connect($MysqlConn, $dbhost, $dbuser, $dbpassword, $dbname, $dbport) If $connected = 0 Then $errno = _MySQL_errno($MysqlConn) MsgBox(0, "Erreur:", $errno & @LF & _MySQL_error($MysqlConn)) If $errno = $CR_UNKNOWN_HOST Then MsgBox(0, "Erreur:", "$CR_UNKNOWN_HOST" & @LF & $CR_UNKNOWN_HOST) Exit (0) EndIf ;;~ attachement is a blob filed in the database ; $noClient = InputBox("Veuillez scanner la carte", "", "", " M10") ; If @error Then _Cleanup() Local $mysql_bool = _MySQL_Real_Query($MysqlConn, $sSQL) If $mysql_bool = $MYSQL_SUCCESS Then ;MsgBox($MB_SYSTEMMODAL, '', "Query OK") Else $errno = _MySQL_errno($MysqlConn) MsgBox($MB_SYSTEMMODAL, "Erreur:", $errno & @LF & _MySQL_error($MysqlConn)) Return EndIf Local $res = _MySQL_Store_Result($MysqlConn) Local $fields = _MySQL_Num_Fields($res) Local $rows = _MySQL_Num_Rows($res) Local $mysqlrow, $lenthsStruct, $length, $fieldPtr, $data If $rows > 0 Then For $k = 1 To $rows $mysqlrow = _MySQL_Fetch_Row($res, $fields) $lenthsStruct = _MySQL_Fetch_Lengths($res) For $i = 1 To $fields $length = DllStructGetData($lenthsStruct, 1, $i) $fieldPtr = DllStructGetData($mysqlrow, 1, $i) $data = DllStructGetData(DllStructCreate("byte[" & $length & "]", $fieldPtr), 1) Next Next EndIf If $data <> "" Then ; NOT an empty BLOB If $bBlobRaw Then ; $aRow[0] = $aRow[0] ; raw BLOB If StringMid($data, 3, 8) == "89504E47" Then ; png file signature $sFileextension = "png" $sFileName = _TempFile(@TempDir, "~", "." & $sFileextension) $sFileName = StringRight($sFileName, StringLen($sFileName) - StringInStr($sFileName, "\", 0, -1)) ; split filename from path ElseIf StringMid($data, 3, 4) == "424D" Then ; bmp file signature 424D $sFileextension = "bmp" $sFileName = _TempFile(@TempDir, "~", "." & $sFileextension) $sFileName = StringRight($sFileName, StringLen($sFileName) - StringInStr($sFileName, "\", 0, -1)) ; split filename from path ElseIf StringMid($data, 15, 8) == "4A464946" Then ; jpg file signature 4A464946 $sFileextension = "jpg" $sFileName = _TempFile(@TempDir, "~", "." & $sFileextension) $sFileName = StringRight($sFileName, StringLen($sFileName) - StringInStr($sFileName, "\", 0, -1)) ; split filename from path ElseIf StringMid($data, 3, 6) == "474946" Then ; gif file signature 474946 $sFileextension = "gif" $sFileName = _TempFile(@TempDir, "~", "." & $sFileextension) $sFileName = StringRight($sFileName, StringLen($sFileName) - StringInStr($sFileName, "\", 0, -1)) ; split filename from path ElseIf StringMid($data, 3, 8) == "00000100" Then ; ico file signature 00000100 $sFileextension = "ico" $sFileName = _TempFile(@TempDir, "~", "." & $sFileextension) $sFileName = StringRight($sFileName, StringLen($sFileName) - StringInStr($sFileName, "\", 0, -1)) ; split filename from path ElseIf StringMid($data, 3, 8) == "49492A00" Then ; tif file Intel byte order signature 49492A00 $sFileextension = "tif" $sFileName = _TempFile(@TempDir, "~", "." & $sFileextension) $sFileName = StringRight($sFileName, StringLen($sFileName) - StringInStr($sFileName, "\", 0, -1)) ; split filename from path ElseIf StringMid($data, 3, 8) == "4D4D002A" Then ; tif file Motorola byte order signature 4D4D002A $sFileextension = "tif" $sFileName = _TempFile(@TempDir, "~", "." & $sFileextension) $sFileName = StringRight($sFileName, StringLen($sFileName) - StringInStr($sFileName, "\", 0, -1)) ; split filename from path Else ; unknown format MsgBox(48, "Cannot View", "Sorry, the object cannot be recognized as an image", 3) ; not an error Return 1 EndIf Else ; split file name and BLOB object ;$sEndOfFileNameFlag = StringInStr($aRow[0], "00") $sEndOfFileNameFlag = StringInStr($data, "00") If Int($sEndOfFileNameFlag / 2) = $sEndOfFileNameFlag / 2 Then $sEndOfFileNameFlag += 1 ;$sFileName = BinaryToString(StringLeft($aRow[0], $sEndOfFileNameFlag - 1)) $sFileName = BinaryToString(StringLeft($data, $sEndOfFileNameFlag - 1)) If @error Then Return SetError(2, 0, @error) ;$aRow[0] = "0x" & StringTrimLeft($aRow[0], $sEndOfFileNameFlag + 1) $data = "0x" & StringTrimLeft($data, $sEndOfFileNameFlag + 1) $sFileextension = StringTrimLeft($sFileName, StringInStr($sFileName, ".")) EndIf $aResult = StringRegExp($data, "(.{4094}|.{1,4094)", 3) $StringRemainder = StringTrimLeft($data, Int(StringLen($data) / 4094) * 4094) ; Currently, I display the file name of raw objects starting with a tilde ~ ;~ If $bBlobRaw Then ;~ $sDescription = "" ;~ Else $sDescription = $sFileName ;~ EndIf If StringInStr(".gif;.png;.jpg;.tif;.bmp;.jpeg;.ico;", $sFileextension) > 0 Then $sFileName = @TempDir & "\" & $sFileName Else ; create executable objects in export folder $sFileName = $sExportFolder & "\" & $sFileName EndIf ; create object $hFile = FileOpen($sFileName, 16 + 2) If @error Then Return SetError(3, 0, 0) $iMaxRows = UBound($aResult) If $iMaxRows = 0 Then ; BLOB contains less than 4094 bytes FileWrite($hFile, $StringRemainder) Else FileWrite($hFile, $aResult[0]) ; "0x" already exists for the first row For $ii = 1 To $iMaxRows - 1 FileWrite($hFile, "0x" & $aResult[$ii]) Next FileWrite($hFile, "0x" & $StringRemainder) EndIf FileClose($hFile) If StringInStr(".gif;.png;.jpg;.bmp;.jpeg;.ico;", $sFileextension) > 0 Then _DisplayImage($sFileName, $sName) ElseIf StringInStr(".tiff;.tif;", $sFileextension) > 0 Then ; convert the tiff files to bmp, so that multipage tiff display the first page in the viewer ; it might be preferable to execute the multipage tiff so that it is displayed properly but ; I have no method to differentiate multipage from single page tiff... $aTIFFDimension = _GIF_GetDimension($sFileName) _ImageResize($sFileName, StringLeft($sFileName, StringInStr($sFileName, ".", 0, -1) - 0) & "bmp", $aTIFFDimension[0], $aTIFFDimension[1]) _DisplayImage(StringLeft($sFileName, StringInStr($sFileName, ".", 0, -1) - 0) & "bmp", $sDescription) FileDelete(StringLeft($sFileName, StringInStr($sFileName, ".", 0, -1) - 0) & "bmp") ; delete the temporary file Else If $bGui_ImageView Then FileDelete($sFileName) ; delete last image file in temp folder GUIDelete($hGui_ImageView) $bGui_ImageView = False EndIf ToolTip("Executing...", MouseGetPos(0) + 20, MouseGetPos(1) + 20) ShellExecute($sFileName, "", @ScriptDir) ; just don't know how to export (move to @scriptdir on request) these files yet, so I don't delete the objects ; and I cannot delete here unless I ShellExecuteWait of course ToolTip("") EndIf Else ;MsgBox(0, "NOBLOB", "No Image Found, show default image") ;If Not FileExists(@ScriptDir & "\Resources\_DefaultImage.jpg") Then _Create_resources() _DisplayImage(@ScriptDir & "\Resources\_DefaultImage.jpg", "Photo non trouvée") EndIf Return 1 EndFunc ;==>_ExecBin And to load the BLOB data into MySQL, it will store the BLOB data along with the proper filename in the header for retrieval into a LONGBLOB field called Attachement: #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_Res_Comment=imgUploader to store images to a MySQL DB as BLOB #AutoIt3Wrapper_Res_Description=imgUploader to store images to a MySQL DB as BLOB #AutoIt3Wrapper_Res_Fileversion=1.0.0.6 #AutoIt3Wrapper_Res_Fileversion_AutoIncrement=y ;#AutoIt3Wrapper_AU3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 -w 7 #AutoIt3Wrapper_AU3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 6 -w 7 #AutoIt3Wrapper_Run_Tidy=y #Tidy_Parameters=/sort_funcs /reel #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** #include <array.au3> #include <mysql.au3> #include <libMySQLdll.au3> #include <File.au3> #include <MsgBoxConstants.au3> Opt('MustDeclareVars', 1) Global $sExtFilter = "bmp|gif|jpeg|jpg|png|tif|tiff" Global $hTimer, $fDiff, $MysqlConn Global $connected, $errno, $dbhost, $dbuser, $dbpassword, $dbname, $dbport $dbhost = InputBox("DB Host", "Please enter the dbhost name (i.e. www.xyz.com, localhost)") If @error Then Exit (0) $dbuser = InputBox("DB User", "Please enter the dbuser name (i.e. sa)") If @error Then Exit (0) $dbpassword = InputBox("DB User Password", "Please enter the password for: <" & $dbuser & ">", "", "*") If @error Then Exit (0) $dbname = InputBox("DB Name", "Please enter the db name (i.e. xyz_DBName)") If @error Then Exit (0) $dbport = InputBox("DB Port", "Please enter the db port for MySQL (typically 3306)", "3306") If @error Then Exit (0) _MySQL_InitLibrary() $MysqlConn = _MySQL_Init() $connected = _MySQL_Real_Connect($MysqlConn, $dbhost, $dbuser, $dbpassword, $dbname, $dbport) If $connected = 0 Then $errno = _MySQL_errno($MysqlConn) MsgBox(0, "Error:", $errno & @LF & _MySQL_error($MysqlConn)) If $errno = $CR_UNKNOWN_HOST Then MsgBox(0, "Error:", "$CR_UNKNOWN_HOST" & @LF & $CR_UNKNOWN_HOST) Exit (0) EndIf _Browse() ; Cleanup _MySQL_Close($MysqlConn) _MySQL_EndLibrary() $fDiff = TimerDiff($hTimer) ; Find the difference in time from the previous call of TimerInit. The variable we stored the TimerInit handlem is passed as the "handle" to TimerDiff. MsgBox($MB_SYSTEMMODAL, "DONE", "DONE. Total upload time = " & Round($fDiff / 1000, 0) & " seconds") Exit (0) Func _Browse() Local $FSF, $FL2A, $iCnt = 0 $FSF = FileSelectFolder("Browse for folder containing pictures", "C:", "C:\LLN") If Not @error And FileExists($FSF) Then If StringRight($FSF, 1) <> "\" Then $FSF &= "\" $FL2A = _FileListToArray($FSF, "*", 1) ;ConsoleWrite($FSF) ;ConsoleWrite(_ArrayDisplay($FL2A, "List of Files")) If Not @error Then ;ConsoleWrite("NO ERROR after fl2a") $hTimer = TimerInit() ; Begin the timer and store the handle in a variable. ; Display a progress bar window. ProgressOn("Progress Meter", "Images uploading...", "0%") ; Update the progress value of the progress bar window every second. For $i = 1 To $FL2A[0] If StringRegExp($FL2A[$i], "(?i)\.(" & $sExtFilter & ")", 0) Then ConsoleWrite("Uploading:" & $FSF & $FL2A[$i] & @CRLF) _uploadImage($FSF & $FL2A[$i], $FL2A[$i]) ProgressSet((Round($i / $FL2A[0] * 100, 0)), (Round($i / $FL2A[0] * 100, 0)) & "%") $iCnt += 1 EndIf Next EndIf EndIf ; Set the "subtext" and "maintext" of the progress bar window. ProgressSet(100, "Done", "Complete") Sleep(5000) ; Close the progress window. ProgressOff() MsgBox($MB_SYSTEMMODAL, "", "Images Processed: " & $iCnt, 10) EndFunc ;==>_Browse Func _uploadImage($file, $fname) Local $f_handle, $contents, $bmp, $query, $mysql_bool Local $iPosition = StringInStr($fname, "-") ;ConsoleWrite("iPosition=" & $iPosition & @CRLF) If $iPosition > 0 Then Local $noClient = StringMid($fname, 1, $iPosition - 1) ConsoleWrite("NoClient=" & $noClient & @CRLF) $f_handle = FileOpen($file, 16) If @error <> 0 Then MsgBox($MB_SYSTEMMODAL, "Error:", "file open error " & @error & @CRLF) EndIf $contents = FileRead($f_handle, FileGetSize($file)) If @error <> 0 Then MsgBox($MB_SYSTEMMODAL, "Error:", "file read error " & @error & @CRLF) EndIf ;(Binary($sFileName & Chr(0) & BinaryToString($sBinaryString))) ;Filename plus null chr(0) so that the retrieval can be done $bmp = String(StringToBinary($fname & Chr(0)) & $contents) ;$query="INSERT INTO complexe_Test_SuiviClientLePheonix.tblCLIENTEXTENTION(attachement) values(" & $bmp & ")" $query = "UPDATE `" & $dbname & "`.`tblCLIENTEXTENTION` SET `attachement`=" & $bmp & " WHERE `NoClient`='" & $noClient & "'" ;ConsoleWrite("UPDATE `" & $dbname & "`.`tblCLIENTEXTENTION` SET `attachement`=" & "$bmp" & " WHERE `NoClient`='" & $noClient & "'" & @CRLF & @CRLF) ;MsgBox(0, "SQL", $query) $mysql_bool = _MySQL_Real_Query($MysqlConn, $query) If $mysql_bool = $MYSQL_SUCCESS Then ;MsgBox($MB_SYSTEMMODAL, '', "Query OK") Else $errno = _MySQL_errno($MysqlConn) MsgBox($MB_SYSTEMMODAL, "Error:", $errno & @LF & _MySQL_error($MysqlConn)) EndIf EndIf EndFunc ;==>_uploadImage Hope this helps others.
  3. bullmoose20
    I managed to figure out what was going on. Essentially the dllstruct was of type char instead of byte. Secondly, in the example, there was always a file name within the header with a null character. Since I was not loading the data with the embedded file name with the null character as the blob header, nothing was working. I am going to clean up the code a bit and post it later. I am super happy this worked...thanks for pointing me to greencans demo.
  4. bullmoose20
    OK. so I am focusing on the _Execbin function. My biggest problem is that when I debug the BLOB to the console in my script compared to the demo, the data returned in mine is only 4 characters in length. Func _ExecBin($iItem) Local $aRow, $iKey, $aResult, $StringRemainder, $sDescription, $hFile, $iMaxRows, $Primary_Key, $sFileextension, $sEndOfFileNameFlag, $aTIFFDimension If $bGui_ImageView Then FileDelete($sFileName) ; delete previous file in temp folder ; identify Column position of Primary Key For $i = 0 To UBound($aNames) - 1 ; every column If StringUpper($aNames[$i]) = $sPrimary Then $Primary_Key = _GUICtrlListView_GetItemText($hListView, $iItem, $i) ExitLoop EndIf Next $Primary_Key = $iItem $sPrimary = "NoClient" $sSQL = "SELECT Attachement FROM " & $dbname & "." & $sTable & " WHERE " & $sPrimary & "='" & $Primary_Key & "'" ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $sSQL = ' & $sSQL & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console If $iDebug Then ConsoleWrite(@ScriptLineNumber & " " & $sSQL & @CR) $connected = _MySQL_Real_Connect($MysqlConn, $dbhost, $dbuser, $dbpassword, $dbname, $dbport) If $connected = 0 Then $errno = _MySQL_errno($MysqlConn) MsgBox(0, "Erreur:", $errno & @LF & _MySQL_error($MysqlConn)) If $errno = $CR_UNKNOWN_HOST Then MsgBox(0, "Erreur:", "$CR_UNKNOWN_HOST" & @LF & $CR_UNKNOWN_HOST) Exit (0) EndIf ;;~ attachement is a blob filed in the database ; $noClient = InputBox("Veuillez scanner la carte", "", "", " M10") ; If @error Then _Cleanup() Local $mysql_bool = _MySQL_Real_Query($MysqlConn, $sSQL) If $mysql_bool = $MYSQL_SUCCESS Then ;MsgBox($MB_SYSTEMMODAL, '', "Query OK") Else $errno = _MySQL_errno($MysqlConn) MsgBox($MB_SYSTEMMODAL, "Erreur:", $errno & @LF & _MySQL_error($MysqlConn)) Return EndIf ;Local $res2 = _MySQL_Use_Result($MysqlConn) ;ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $res2 = ' & $res2 & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console Local $res = _MySQL_Store_Result($MysqlConn) ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $res = ' & $res & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console Local $fields = _MySQL_Num_Fields($res) ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $fields = ' & $fields & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console Local $rows = _MySQL_Num_Rows($res) ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $rows = ' & $rows & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console Local $mysqlrow, $lenthsStruct, $length, $fieldPtr, $data Local $array[$rows][$fields] For $k = 1 To $rows $mysqlrow = _MySQL_Fetch_Row($res, $fields) ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $mysqlrow = ' & $mysqlrow & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console $lenthsStruct = _MySQL_Fetch_Lengths($res) ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $lenthsStruct = ' & $lenthsStruct & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console For $i = 1 To $fields $length = DllStructGetData($lenthsStruct, 1, $i) ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $length = ' & $length & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console $fieldPtr = DllStructGetData($mysqlrow, 1, $i) ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $fieldPtr = ' & $fieldPtr & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console $data = DllStructGetData(DllStructCreate("char[" & $length & "]", $fieldPtr), 1) ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $data = ' & $data & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console $array[$k - 1][$i - 1] = $data ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $array = ' & $array[0][0] & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console Next Next _ArrayDisplay($array) ;_SQLite_QuerySingleRow(-1, $sSQL, $aRow) If @error Then Return SetError(1, 0, @error) If $data <> "" Then ; NOT an empty BLOB ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $data = ' & $data & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console @@ Debug(326) : $data = ÿØÿà in the demo its: @@ Debug(1495) : $aRow[0] = 0x5F5F477265656E43616E2E706E6700FFD8FFE000104A46494600010100000100010000FFDB00840009060614121115141014151 (I have truncated it for the post) I don't understand the dllstructgetdata command but I think this is where it is failing. I also think there has to be a simpler way to read the 1 record returned as BLOB to $data, but I cannot figure it out...
  5. bullmoose20
    Hi and thanks for pointing me to that demo. Got it working for that demo in no time. The issue is that it does way more than I actually need and I have spent most of the day trying to convert it from using SQLite to MySQL UDF and I am struggling big time....I can't seem to figure out the portions of this demo that I should be extracting for use and I can't migrate it to MySQL either... :-( One example is the use of the PRAGMA commands. I will keep trying here....
  6. bullmoose20
    how about a way to read back the blob data from the MySQL server?
  7. bullmoose20
    so I am still looking for the code that will read back the stored blob data to present to the user. I have the insert command working, but I dont know how to read the blob data, determine the filetype(.jpg, gif,png) and present the image back in an autoit gui. I am looking for pointers please. This post only seems to show the insert.
  8. bullmoose20
    I am trying to load a users picture that is stored in their customer record as a BLOB in MySQL. I am trying to find a way to show this in an autoit gui but I dont really know where to begin. I can get all the text fields without issue, but dont know about the image itself. Any pointers would be appreciated. I am using the MySQL udf by progandy, but no examples showing the retrieval of an image stired in a blob.
  9. bullmoose20
    Bump?
  10. bullmoose20
    hi, I was wondering how this script works if dataset contains "blob" data? I have blob data in the mysql db and i was how i could show that data back to the user? The data stored is jpg, png, gif. did you ever address the points above from the other folks? If so, how? thanks!
  11. bullmoose20
    Thanks. I posted a much more thorough posting in the ProgAndy MySQL UDF thread, but I thought I would also take a chance here with trying to get this to even work with the code and attachements and I have had no success because of the attached file not opening up properly.
  12. bullmoose20
    I am trying to read LONGBLOB data from a remote MySQL db, and then display the retrieved image/pic in an autoIT GUI. Would you have any examples of how to do this? Briefly, what the below does is insert an image on my c:\ drive to a remote MySQL server. Using MySQL Workbench, I can see the image is stored as a BLOB and that the image looks fine when I open up the BLOB with Workbench. The next part is where I am having difficulties. I am able to run the select, but clearly, since the data is a BLOB, it does not view properly in the different methods that ProgAndy uses in his included test.au3 file since he is displaying it as a string array. I was wondering what I could do to have the image show up in a very simple autoIT gui? #include <array.au3> #include <mysql.au3> dim $file="C:\Users\nickz\Downloads\complexe_Test_SuiviClientLePheonix\images\3281-3281.jpg" dim $f_handle _MySQL_InitLibrary() $MysqlConn = _MySQL_Init() $connected = _MySQL_Real_Connect($MysqlConn,"host","user","password","dbname") If $connected = 0 Then $errno = _MySQL_errno($MysqlConn) MsgBox(0,"Error:",$errno & @LF & _MySQL_error($MysqlConn)) If $errno = $CR_UNKNOWN_HOST Then MsgBox(0,"Error:","$CR_UNKNOWN_HOST" & @LF & $CR_UNKNOWN_HOST) Endif $f_handle=FileOpen($file,16) if @error <> 0 Then MsgBox(0,"Error:","file open error " & @error & @CRLF) EndIf $contents=FileRead($f_handle,FileGetSize($file)) if @error <> 0 Then MsgBox(0,"Error:","file read error " & @error & @CRLF) EndIf $bmp=String($contents) ;$query="INSERT INTO complexe_Test_SuiviClientLePheonix.tblCLIENTEXTENTION(attachement) values(" & $bmp & ")" $query="UPDATE `complexe_Test_SuiviClientLePheonix`.`tblCLIENTEXTENTION` SET `attachement`=" & $bmp & " WHERE `NoClient`='3281'" _MySQL_Real_Query($MysqlConn, $query) ;MsgBox(0,"SQL",$query) if @error <> 0 Then MsgBox(0,"Error:","SQL statement failed error =" & @error & @CRLF) EndIf ;~ address is a blob filed in the database $query="SELECT attachement from `complexe_Test_SuiviClientLePheonix`.`tblCLIENTEXTENTION` WHERE `NoClient`='3281'" _MySQL_Real_Query($MysqlConn, $query) $res = _MySQL_Store_Result($MysqlConn) msgbox(0,"",$query) $fields = _MySQL_Num_Fields($res) $rows = _MySQL_Num_Rows($res) MsgBox(0, "", $rows & "-" & $fields) ConsoleWrite("DataType=" & VarGetType($res) & @LF) ConsoleWrite("DataType=" & VarGetType($fields) & @LF) ConsoleWrite("DataType=" & VarGetType($rows) & @LF) ; Zugriff 1 MsgBox(0, '', "Zugriff Methode 1- Handarbeit") Dim $array[$rows][$fields] For $k = 1 To $rows $mysqlrow = _MySQL_Fetch_Row($res,$fields) $lenthsStruct = _MySQL_Fetch_Lengths($res) For $i = 1 To $fields $length = DllStructGetData($lenthsStruct, 1, $i) $fieldPtr = DllStructGetData($mysqlrow, 1, $i) $data = DllStructGetData(DllStructCreate("char[" & $length & "]", $fieldPtr), 1) $array[$k - 1][$i - 1] = $data Next Next _ArrayDisplay($array) ; Zugriff 2 MsgBox(0, '', "Zugriff Methode 2 - Reihe für Reihe") _MySQL_Data_Seek($res, 0) ; nur zum zum Zurücksetzen an den Anfang der Abfrage Do $row1 = _MySQL_Fetch_Row_StringArray($res) If @error Then ExitLoop _ArrayDisplay($row1) Until @error MsgBox(0, '', "Zugriff Methode 3 - alles in ein 2D Array") $array = _MySQL_Fetch_Result_StringArray($res) _ArrayDisplay($array) ; Feldinformationen MsgBox(0, '', "Zugriff Feldinformationen") Dim $arFields[$fields][3] For $i = 0 To $fields - 1 $field = _MySQL_Fetch_Field_Direct($res, $i) $arFields[$i][0] = _MySQL_Field_ReadValue($field, "name") $arFields[$i][1] = _MySQL_Field_ReadValue($field, "table") $arFields[$i][2] = _MySQL_Field_ReadValue($field, "db") Next _ArrayDisplay($arFields) ; Cleanup _MySQL_Free_Result($res) _MySQL_Close($MysqlConn) _MySQL_EndLibrary() msgbox(0,"","DONE") exit(0)
  13. bullmoose20
    I am trying to read LONGBLOB data from a MySQL db, and then display it in an autoIT GUI. So something similar to this project, however I cannot seem to get this one to work so that I can try to retrofit it with ProgAndy's MySQL UDF. Any ideas? BTW, the issue is that when I save the attachment in an earlier post from test.txt to VideosInfo.db I get the error cannot open this database.
  14. bullmoose20
    Both are running IE 11.0.9600.17107. Regards, Nick
  15. bullmoose20
    Someone tagged my code as "code", and the url did not come through properly. I have since modified the post to reflect the proper links that I am using which is just like yours above, but this does not seem to be the problem.
  16. bullmoose20
    Hello, Attached you will find the debug output log of a script that I am running on two different machines with the same .au3 file. On one machine it works fine, on the other it does not. I cannot figure out why and it is driving me nuts. 0139: 0-0: Local $oFrom = _IEGetObjById($o_IE1, $chkFrom) --> IE.au3 T3.0-1 Warning from function _IEGetObjById, $_IEStatus_NoMatch (ContentPlaceHolder1_MatchsDateTerr1_cmbDateFrom) On one pc, it finds "ContentPlaceHolder1_MatchsDateTerr1_cmbDateFrom" in the returned HTML, in the other, it does not find it and yet when I "view source" it is there. Ideas? Thanks in advance, Nick ====START WORKS ==== >"C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "C:\Users\nickza\Desktop\TSSS\TSSS-DataHelper.au3" /UserParams +>13:14:29 Starting AutoIt3Wrapper v.2.2.0.0 SciTE v.3.4.1.0 Keyboard:00000409 OS:WIN_81/ CPU:X64 OS:X64 Environment(Language:0409) +> SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE UserDir => C:\Users\nickza\AppData\Local\AutoIt v3\SciTE\AutoIt3Wrapper SCITE_USERHOME => C:\Users\nickza\AppData\Local\AutoIt v3\SciTE >Running AU3Check (3.3.10.2) from:C:\Program Files (x86)\AutoIt3 input:C:\Users\nickza\Desktop\TSSS\TSSS-DataHelper.au3 +>13:14:29 AU3Check ended.rc:0 >Running:(3.3.10.2):C:\Program Files (x86)\AutoIt3\autoit3.exe "C:\Users\nickza\Desktop\TSSS\TSSS-DataHelper.au3" --> Press Ctrl+Alt+F5 to Restart or Ctrl+Break to Stop !> Starting in DebugMode.. Line: @error-@extended: Line syntax 0001: 0-0: #Region ;**** Directives created by AutoIt3Wrapper_GUI **** 0002: 0-0: #AutoIt3Wrapper_Icon=C:\Program Files (x86)\AutoIt3\Icons\au3.ico 0003: 0-0: #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** 0008: 0-0: #AutoIt3Wrapper_run_debug_mode=Y 0009: 0-0: #include <IE.au3> 0010: 0-0: #include <Date.au3> 0011: 0-0: #include <Excel.au3> 0012: 0-0: #include <File.au3> 0013: 0-0: #include <Array.au3> 0014: 0-0: #include <Clipboard.au3> 0016: 0-0: Global $o_IE1 0017: 0-0: Global $ArrayCont = "" 0019: 0-0: Func _StringBetween2($s, $from, $to) 0025: 0-0: Const $navOpenInNewTab = 0x0800 0028: 0-0: Local $iDateCalc = _DateDiff("d", "2000/01/01 00:00:00", _NowCalc()) 0031: 0-0: Local $iDaysx01 = _DateDaysInMonth(@YEAR, @MON - 1) 0034: 0-0: Local $iDaysx02 = _DateDaysInMonth(@YEAR, @MON - 2) 0037: 0-0: Local $iDaysx03 = _DateDaysInMonth(@YEAR, @MON - 3) 0040: 0-0: Local $iDaysx04 = _DateDaysInMonth(@YEAR, @MON - 4) 0043: 0-0: Local $iDaysx05 = _DateDaysInMonth(@YEAR, @MON - 5) 0046: 1-0: Local $iDaysx06 = _DateDaysInMonth(@YEAR, @MON - 6) 0049: 1-0: Local $iDaysx07 = _DateDaysInMonth(@YEAR, @MON - 7) 0052: 1-0: Local $iDays01 = _DateDaysInMonth(@YEAR, @MON) 0055: 0-0: Local $iDays02 = _DateDaysInMonth(@YEAR, @MON + 1) 0058: 0-0: Local $iDays03 = _DateDaysInMonth(@YEAR, @MON + 2) 0061: 0-0: Local $iDays04 = _DateDaysInMonth(@YEAR, @MON + 3) 0064: 0-0: Local $iDays05 = _DateDaysInMonth(@YEAR, @MON + 4) 0067: 0-0: Local $iDays06 = _DateDaysInMonth(@YEAR, @MON + 5) 0070: 0-0: Local $iDays07 = _DateDaysInMonth(@YEAR, @MON + 6) 0074: 0-0: Local $iCurrMonth = _DateDiff("d", "2000/01/01 00:00:00", @YEAR & "/" & @MON & "/" & "01 00:00:00") 0077: 0-0: Local $iMonthx01 = $iCurrMonth - $iDaysx01 0078: 0-0: Local $iMonthx02 = $iMonthx01 - $iDaysx02 0079: 0-0: Local $iMonthx03 = $iMonthx02 - $iDaysx03 0080: 0-0: Local $iMonthx04 = $iMonthx03 - $iDaysx04 0081: 0-0: Local $iMonthx05 = $iMonthx04 - $iDaysx05 0082: 0-0: Local $iMonthx06 = $iMonthx05 - $iDaysx06 0083: 0-0: Local $iMonthx07 = $iMonthx06 - $iDaysx07 0085: 0-0: Local $iMonth01 = $iCurrMonth + $iDays01 0086: 0-0: Local $iMonth02 = $iMonth01 + $iDays02 0087: 0-0: Local $iMonth03 = $iMonth02 + $iDays03 0088: 0-0: Local $iMonth04 = $iMonth03 + $iDays04 0089: 0-0: Local $iMonth05 = $iMonth04 + $iDays05 0090: 0-0: Local $iMonth06 = $iMonth05 + $iDays06 0091: 0-0: Local $iMonth07 = $iMonth06 + $iDays07 0092: 0-0: Local $dateTo = $iMonth07 - 1 0093: 0-0: Local $dateFrom = $iMonthx03 0095: 0-0: local $iCmd = "" 0096: 0-0: If $CmdLine[0] <> "" Then 0105: 0-0: Local $sFilePath1 = "C:\Users\nickza\Desktop\TSSS\MASTER.xlsm" ;This file should already exist 0106: 0-0: Local $message = "Please select Input MASTER.xlsm" 0107: 0-0: Local $var = FileOpenDialog($message, @ScriptDir & "\", "XLSM (*.xlsm)", 1 + 2) 0108: 0-0: EndIf 0110: 0-0: If @error Then 0114: 0-0: $var = StringReplace($var, "|", @CRLF) 0115: 0-0: Local $szDrive, $szDir, $szFName, $szExt 0116: 0-0: Local $TestPath = _PathSplit($var, $szDrive, $szDir, $szFName, $szExt) 0117: 0-0: Local $sPath = $szDrive & $szDir 0118: 0-0: MsgBox(4096, "", "You chose " & $var & @CRLF & "It is RECOMMENDED you use English Canada and short date format of yyyy-MM-dd before running this script.", 30) 0120: 0-0: EndIf 0123: 0-0: Func nextMonthFROM() 0137: 0-0: Func prevMonthFROM() 0151: 0-0: Func prevMonthTO() 0165: 0-0: Func nextMonthTO() 0178: 0-0: Func getData($sLink) 0290: 0-0: ProgressOn("TSSS-DataHelper", "Working on getting data from site", "Working...", 100, 100, 16) 0291: 0-0: ProgressSet(10) 0292: 0-0: getData("C3L") 0179: 0-0: ClipPut("") 0180: 0-0: Local $ArrayCont 0182: 0-0: MsgBox(4096, "", "Getting data for: " & $sLink & " and saving to: " & $sPath & $sLink & ".xls", 5) 0183: 0-0: $o_IE1 = _IECreate("[url=http://www.tsisports.ca/soccer/ligue14/index.aspx?champ]http://www.tsisports.ca/soccer/ligue14/index.aspx?champ[/url]=" & $sLink) 0186: 0-0: Local $hIE1 = _IEPropertyGet($o_IE1, "hwnd") 0187: 0-0: $o_IE1.Navigate2("[url=http://www.tsisports.ca/soccer/ligue14/l_cal2.aspx]http://www.tsisports.ca/soccer/ligue14/l_cal2.aspx[/url]") 0188: 0-0: Sleep(2000) 0191: 0-0: _IENavigate($o_IE1, prevMonthFROM()) 0138: 0-0: Local $chkFrom = "ContentPlaceHolder1_MatchsDateTerr1_cmbDateFrom" 0139: 0-0: Local $oFrom = _IEGetObjById($o_IE1, $chkFrom) 0140: 0-0: ConsoleWrite(_IEPropertyGet($oFrom, "innerhtml") & @CRLF) <tbody><tr><td style="background-color: rgb(170, 0, 0);" colspan="7"><table style="width: 100%; color: white; font-size: xx-small; font-weight: bold; border-collapse: collapse;" cellspacing="0"> <tbody><tr><td style="width: 15%; color: white;"><a title="Go to the previous month" style="color: white;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','V5204')">&lt;</a></td><td align="center" style="width: 70%; white-space: nowrap;">May 2014</td><td align="right" style="width: 15%; color: white;"><a title="Go to the next month" style="color: white;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','V5265')">&gt;</a></td></tr> </tbody></table></td></tr><tr><th align="center" style="color: white; font-weight: normal; background-color: darkgray;" abbr="Monday" scope="col">Mon</th><th align="center" style="color: white; font-weight: normal; background-color: darkgray;" abbr="Tuesday" scope="col">Tue</th><th align="center" style="color: white; font-weight: normal; background-color: darkgray;" abbr="Wednesday" scope="col">Wed</th><th align="center" style="color: white; font-weight: normal; background-color: darkgray;" abbr="Thursday" scope="col">Thu</th><th align="center" style="color: white; font-weight: normal; background-color: darkgray;" abbr="Friday" scope="col">Fri</th><th align="center" style="color: white; font-weight: normal; background-color: darkgray;" abbr="Saturday" scope="col">Sat</th><th align="center" style="color: white; font-weight: normal; background-color: darkgray;" abbr="Sunday" scope="col">Sun</th></tr><tr><td align="center" style="width: 14%; color: white;"><a title="April 28" style="color: white;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5231')">28</a></td><td align="center" style="width: 14%; color: white;"><a title="April 29" style="color: white;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5232')">29</a></td><td align="center" style="width: 14%; color: white;"><a title="April 30" style="color: white;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5233')">30</a></td><td align="center" style="width: 14%;"><a title="May 1" style="color: black;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5234')">1</a></td><td align="center" style="width: 14%;"><a title="May 2" style="color: black;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5235')">2</a></td><td align="center" style="width: 14%; color: red;"><a title="May 3" style="color: red;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5236')">3</a></td><td align="center" style="width: 14%; color: red;"><a title="May 4" style="color: red;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5237')">4</a></td></tr><tr><td align="center" style="width: 14%;"><a title="May 5" style="color: black;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5238')">5</a></td><td align="center" style="width: 14%;"><a title="May 6" style="color: black;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5239')">6</a></td><td align="center" style="width: 14%;"><a title="May 7" style="color: black;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5240')">7</a></td><td align="center" style="width: 14%;"><a title="May 8" style="color: black;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5241')">8</a></td><td align="center" style="width: 14%;"><a title="May 9" style="color: black;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5242')">9</a></td><td align="center" style="width: 14%; color: red;"><a title="May 10" style="color: red;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5243')">10</a></td><td align="center" style="width: 14%; color: red;"><a title="May 11" style="color: red;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5244')">11</a></td></tr><tr><td align="center" style="width: 14%;"><a title="May 12" style="color: black;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5245')">12</a></td><td align="center" style="width: 14%;"><a title="May 13" style="color: black;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5246')">13</a></td><td align="center" style="width: 14%;"><a title="May 14" style="color: black;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5247')">14</a></td><td align="center" style="width: 14%;"><a title="May 15" style="color: black;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5248')">15</a></td><td align="center" style="width: 14%;"><a title="May 16" style="color: black;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5249')">16</a></td><td align="center" style="width: 14%; color: red;"><a title="May 17" style="color: red;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5250')">17</a></td><td align="center" style="width: 14%; color: red;"><a title="May 18" style="color: red;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5251')">18</a></td></tr><tr><td align="center" style="width: 14%;"><a title="May 19" style="color: black;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5252')">19</a></td><td align="center" style="width: 14%;"><a title="May 20" style="color: black;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5253')">20</a></td><td align="center" style="width: 14%;"><a title="May 21" style="color: black;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5254')">21</a></td><td align="center" style="width: 14%;"><a title="May 22" style="color: black;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5255')">22</a></td><td align="center" style="width: 14%;"><a title="May 23" style="color: black;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5256')">23</a></td><td align="center" style="width: 14%; color: red;"><a title="May 24" style="color: red;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5257')">24</a></td><td align="center" style="width: 14%; color: red;"><a title="May 25" style="color: red;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5258')">25</a></td></tr><tr><td align="center" style="width: 14%;"><a title="May 26" style="color: black;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5259')">26</a></td><td align="center" style="width: 14%;"><a title="May 27" style="color: black;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5260')">27</a></td><td align="center" style="width: 14%;"><a title="May 28" style="color: black;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5261')">28</a></td><td align="center" style="width: 14%;"><a title="May 29" style="color: black;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5262')">29</a></td><td align="center" style="width: 14%;"><a title="May 30" style="color: black;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5263')">30</a></td><td align="center" style="width: 14%; color: red;"><a title="May 31" style="color: red;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5264')">31</a></td><td align="center" style="width: 14%; color: white;"><a title="June 1" style="color: white;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5265')">1</a></td></tr><tr><td align="center" style="width: 14%; color: white;"><a title="June 2" style="color: white;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5266')">2</a></td><td align="center" style="width: 14%; color: white;"><a title="June 3" style="color: white;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5267')">3</a></td><td align="center" style="width: 14%; color: white;"><a title="June 4" style="color: white;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5268')">4</a></td><td align="center" style="width: 14%; color: white;"><a title="June 5" style="color: white;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5269')">5</a></td><td align="center" style="width: 14%; color: white;"><a title="June 6" style="color: white;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5270')">6</a></td><td align="center" style="width: 14%; color: white;"><a title="June 7" style="color: white;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5271')">7</a></td><td align="center" style="width: 14%; color: white;"><a title="June 8" style="color: white;" href="javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','5272')">8</a></td></tr> </tbody> 0141: 0-0: $oFrom = _IEPropertyGet($oFrom, "innerhtml") 0142: 0-0: Local $startFROM = StringInStr($oFrom, "Go to the previous month") + 54 0143: 0-0: Local $finishFROM = $startFROM + 88 0144: 0-0: $oFrom = StringMid($oFrom, $startFROM, 88) 0146: 0-0: ConsoleWrite("prevMonthFROM:" & $oFrom & @CRLF) prevMonthFROM:javascript:__doPostBack('ctl00$ContentPlaceHolder1$MatchsDateTerr1$cmbDateFrom','V5204') 0147: 0-0: Return $oFrom 0192: 0-0: exit +>13:14:40 AutoIt3.exe ended.rc:0 +>13:14:40 AutoIt3Wrapper Finished. >Exit code: 0 Time: 11.35 ====START FAILS ==== >"C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "C:\Users\nickza\Desktop\TSSS\TSSS-DataHelper.au3" /UserParams +>13:14:19 Starting AutoIt3Wrapper v.2.2.0.0 SciTE v.3.4.1.0 Keyboard:00000409 OS:WIN_81/ CPU:X64 OS:X64 Environment(Language:0409) +> SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE UserDir => C:\Users\nickza\AppData\Local\AutoIt v3\SciTE\AutoIt3Wrapper SCITE_USERHOME => C:\Users\nickza\AppData\Local\AutoIt v3\SciTE >Running AU3Check (3.3.10.2) from:C:\Program Files (x86)\AutoIt3 input:C:\Users\nickza\Desktop\TSSS\TSSS-DataHelper.au3 +>13:14:19 AU3Check ended.rc:0 >Running:(3.3.10.2):C:\Program Files (x86)\AutoIt3\autoit3.exe "C:\Users\nickza\Desktop\TSSS\TSSS-DataHelper.au3" --> Press Ctrl+Alt+F5 to Restart or Ctrl+Break to Stop !> Starting in DebugMode.. Line: @error-@extended: Line syntax 0001: 0-0: #Region ;**** Directives created by AutoIt3Wrapper_GUI **** 0002: 0-0: #AutoIt3Wrapper_Icon=C:\Program Files (x86)\AutoIt3\Icons\au3.ico 0003: 0-0: #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** 0008: 0-0: #AutoIt3Wrapper_run_debug_mode=Y 0009: 0-0: #include <IE.au3> 0010: 0-0: #include <Date.au3> 0011: 0-0: #include <Excel.au3> 0012: 0-0: #include <File.au3> 0013: 0-0: #include <Array.au3> 0014: 0-0: #include <Clipboard.au3> 0016: 0-0: Global $o_IE1 0017: 0-0: Global $ArrayCont = "" 0019: 0-0: Func _StringBetween2($s, $from, $to) 0025: 0-0: Const $navOpenInNewTab = 0x0800 0028: 0-0: Local $iDateCalc = _DateDiff("d", "2000/01/01 00:00:00", _NowCalc()) 0031: 0-0: Local $iDaysx01 = _DateDaysInMonth(@YEAR, @MON - 1) 0034: 0-0: Local $iDaysx02 = _DateDaysInMonth(@YEAR, @MON - 2) 0037: 0-0: Local $iDaysx03 = _DateDaysInMonth(@YEAR, @MON - 3) 0040: 0-0: Local $iDaysx04 = _DateDaysInMonth(@YEAR, @MON - 4) 0043: 0-0: Local $iDaysx05 = _DateDaysInMonth(@YEAR, @MON - 5) 0046: 1-0: Local $iDaysx06 = _DateDaysInMonth(@YEAR, @MON - 6) 0049: 1-0: Local $iDaysx07 = _DateDaysInMonth(@YEAR, @MON - 7) 0052: 1-0: Local $iDays01 = _DateDaysInMonth(@YEAR, @MON) 0055: 0-0: Local $iDays02 = _DateDaysInMonth(@YEAR, @MON + 1) 0058: 0-0: Local $iDays03 = _DateDaysInMonth(@YEAR, @MON + 2) 0061: 0-0: Local $iDays04 = _DateDaysInMonth(@YEAR, @MON + 3) 0064: 0-0: Local $iDays05 = _DateDaysInMonth(@YEAR, @MON + 4) 0067: 0-0: Local $iDays06 = _DateDaysInMonth(@YEAR, @MON + 5) 0070: 0-0: Local $iDays07 = _DateDaysInMonth(@YEAR, @MON + 6) 0074: 0-0: Local $iCurrMonth = _DateDiff("d", "2000/01/01 00:00:00", @YEAR & "/" & @MON & "/" & "01 00:00:00") 0077: 0-0: Local $iMonthx01 = $iCurrMonth - $iDaysx01 0078: 0-0: Local $iMonthx02 = $iMonthx01 - $iDaysx02 0079: 0-0: Local $iMonthx03 = $iMonthx02 - $iDaysx03 0080: 0-0: Local $iMonthx04 = $iMonthx03 - $iDaysx04 0081: 0-0: Local $iMonthx05 = $iMonthx04 - $iDaysx05 0082: 0-0: Local $iMonthx06 = $iMonthx05 - $iDaysx06 0083: 0-0: Local $iMonthx07 = $iMonthx06 - $iDaysx07 0085: 0-0: Local $iMonth01 = $iCurrMonth + $iDays01 0086: 0-0: Local $iMonth02 = $iMonth01 + $iDays02 0087: 0-0: Local $iMonth03 = $iMonth02 + $iDays03 0088: 0-0: Local $iMonth04 = $iMonth03 + $iDays04 0089: 0-0: Local $iMonth05 = $iMonth04 + $iDays05 0090: 0-0: Local $iMonth06 = $iMonth05 + $iDays06 0091: 0-0: Local $iMonth07 = $iMonth06 + $iDays07 0092: 0-0: Local $dateTo = $iMonth07 - 1 0093: 0-0: Local $dateFrom = $iMonthx03 0095: 0-0: local $iCmd = "" 0096: 0-0: If $CmdLine[0] <> "" Then 0105: 0-0: Local $sFilePath1 = "C:\Users\nickza\Desktop\TSSS\MASTER.xlsm" ;This file should already exist 0106: 0-0: Local $message = "Please select Input MASTER.xlsm" 0107: 0-0: Local $var = FileOpenDialog($message, @ScriptDir & "\", "XLSM (*.xlsm)", 1 + 2) 0108: 0-0: EndIf 0110: 0-0: If @error Then 0114: 0-0: $var = StringReplace($var, "|", @CRLF) 0115: 0-0: Local $szDrive, $szDir, $szFName, $szExt 0116: 0-0: Local $TestPath = _PathSplit($var, $szDrive, $szDir, $szFName, $szExt) 0117: 0-0: Local $sPath = $szDrive & $szDir 0118: 0-0: MsgBox(4096, "", "You chose " & $var & @CRLF & "It is RECOMMENDED you use English Canada and short date format of yyyy-MM-dd before running this script.", 30) 0120: 0-0: EndIf 0123: 0-0: Func nextMonthFROM() 0137: 0-0: Func prevMonthFROM() 0151: 0-0: Func prevMonthTO() 0165: 0-0: Func nextMonthTO() 0178: 0-0: Func getData($sLink) 0290: 0-0: ProgressOn("TSSS-DataHelper", "Working on getting data from site", "Working...", 100, 100, 16) 0291: 0-0: ProgressSet(10) 0292: 0-0: getData("C3L") 0179: 0-0: ClipPut("") 0180: 0-0: Local $ArrayCont 0182: 0-0: MsgBox(4096, "", "Getting data for: " & $sLink & " and saving to: " & $sPath & $sLink & ".xls", 5) 0183: 0-0: $o_IE1 = _IECreate("[url=http://www.tsisports.ca/soccer/ligue14/index.aspx?champ]http://www.tsisports.ca/soccer/ligue14/index.aspx?champ[/url]=" & $sLink) 0186: 0-0: Local $hIE1 = _IEPropertyGet($o_IE1, "hwnd") 0187: 0-0: $o_IE1.Navigate2("[url=http://www.tsisports.ca/soccer/ligue14/l_cal2.aspx]http://www.tsisports.ca/soccer/ligue14/l_cal2.aspx[/url]") 0188: 0-0: Sleep(2000) 0191: 0-0: _IENavigate($o_IE1, prevMonthFROM()) 0138: 0-0: Local $chkFrom = "ContentPlaceHolder1_MatchsDateTerr1_cmbDateFrom" 0139: 0-0: Local $oFrom = _IEGetObjById($o_IE1, $chkFrom) --> IE.au3 T3.0-1 Warning from function _IEGetObjById, $_IEStatus_NoMatch (ContentPlaceHolder1_MatchsDateTerr1_cmbDateFrom) 0140: 7-2: ConsoleWrite(_IEPropertyGet($oFrom, "innerhtml") & @CRLF) --> IE.au3 T3.0-1 Error from function _IEPropertyGet, $_IEStatus_InvalidDataType 0 0141: 0-0: $oFrom = _IEPropertyGet($oFrom, "innerhtml") --> IE.au3 T3.0-1 Error from function _IEPropertyGet, $_IEStatus_InvalidDataType 0142: 3-1: Local $startFROM = StringInStr($oFrom, "Go to the previous month") + 54 0143: 0-0: Local $finishFROM = $startFROM + 88 0144: 0-0: $oFrom = StringMid($oFrom, $startFROM, 88) 0146: 0-0: ConsoleWrite("prevMonthFROM:" & $oFrom & @CRLF) prevMonthFROM: 0147: 0-0: Return $oFrom --> IE.au3 T3.0-1 Error from function _IENavigate, $_IEStatus_COMError (-2147352567) 0192: 2--2147352567: exit +>13:14:39 AutoIt3.exe ended.rc:0 +>13:14:39 AutoIt3Wrapper Finished. >Exit code: 0 Time: 20.33 ====END FAILS==== ===SOURCE=== #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_Icon=C:\Program Files (x86)\AutoIt3\Icons\au3.ico #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** ;------------------------------------------------------------------------------------------ ; Script to help set logos of teams into TSSS ; v1.0 ; May 29, 2013 ;#AutoIt3Wrapper_run_debug_mode=Y #include <IE.au3> #include <Date.au3> #include <Excel.au3> #include <File.au3> #include <Array.au3> #include <Clipboard.au3> #include <MsgBoxConstants.au3> Global $o_IE1 Global $ArrayCont = "" Func _StringBetween2($s, $from, $to) $x = StringInStr($s, $from) + StringLen($from) $y = StringInStr(StringTrimLeft($s, $x), $to) Return StringMid($s, $x, $y) EndFunc ;==>_StringBetween2 Const $navOpenInNewTab = 0x0800 ; Calculated the number of DAYS since TSIStart (2000/01/01 00:00:00) Local $iDateCalc = _DateDiff('d', "2000/01/01 00:00:00", _NowCalc()) ;MsgBox(4096, _NowCalc(), "Number of days since 2000/01/01: " & $iDateCalc) Local $iDaysx01 = _DateDaysInMonth(@YEAR, @MON - 1) ;MsgBox(4096, "Days in Month", @YEAR & " " & @MON - 1 & " " & " has " & String($iDaysx01) & " days in it.") Local $iDaysx02 = _DateDaysInMonth(@YEAR, @MON - 2) ;MsgBox(4096, "Days in Month", @YEAR & " " & @MON - 2 & " " & " has " & String($iDaysx02) & " days in it.") Local $iDaysx03 = _DateDaysInMonth(@YEAR, @MON - 3) ;MsgBox(4096, "Days in Month", @YEAR & " " & @MON - 3 & " " & " has " & String($iDaysx03) & " days in it.") Local $iDaysx04 = _DateDaysInMonth(@YEAR, @MON - 4) ;MsgBox(4096, "Days in Month", @YEAR & " " & @MON - 4 & " " & " has " & String($iDaysx03) & " days in it.") Local $iDaysx05 = _DateDaysInMonth(@YEAR, @MON - 5) ;MsgBox(4096, "Days in Month", @YEAR & " " & @MON - 5 & " " & " has " & String($iDaysx03) & " days in it.") Local $iDaysx06 = _DateDaysInMonth(@YEAR, @MON - 6) ;MsgBox(4096, "Days in Month", @YEAR & " " & @MON - 6 & " " & " has " & String($iDaysx03) & " days in it.") Local $iDaysx07 = _DateDaysInMonth(@YEAR, @MON - 7) ;MsgBox(4096, "Days in Month", @YEAR & " " & @MON - 7 & " " & " has " & String($iDaysx03) & " days in it.") Local $iDays01 = _DateDaysInMonth(@YEAR, @MON) ;MsgBox(4096, "Days in Month", @YEAR & " " & @MON & " " & " has " & String($iDays01) & " days in it.") Local $iDays02 = _DateDaysInMonth(@YEAR, @MON + 1) ;MsgBox(4096, "Days in Month", @YEAR & " " & @MON + 1 & " " & " has " & String($iDays02) & " days in it.") Local $iDays03 = _DateDaysInMonth(@YEAR, @MON + 2) ;MsgBox(4096, "Days in Month", @YEAR & " " & @MON + 2 & " " & " has " & String($iDays03) & " days in it.") Local $iDays04 = _DateDaysInMonth(@YEAR, @MON + 3) ;MsgBox(4096, "Days in Month", @YEAR & " " & @MON + 3 & " " & " has " & String($iDays04) & " days in it.") Local $iDays05 = _DateDaysInMonth(@YEAR, @MON + 4) ;MsgBox(4096, "Days in Month", @YEAR & " " & @MON + 4 & " " & " has " & String($iDays05) & " days in it.") Local $iDays06 = _DateDaysInMonth(@YEAR, @MON + 5) ;MsgBox(4096, "Days in Month", @YEAR & " " & @MON + 5 & " " & " has " & String($iDays06) & " days in it.") Local $iDays07 = _DateDaysInMonth(@YEAR, @MON + 6) ;MsgBox(4096, "Days in Month", @YEAR & " " & @MON + 6 & " " & " has " & String($iDays07) & " days in it.") ; Calculated the number of DAYS since TSIStart (2000/01/01 00:00:00) for current month Local $iCurrMonth = _DateDiff('d', "2000/01/01 00:00:00", @YEAR & "/" & @MON & "/" & "01 00:00:00") ;MsgBox(4096, _NowCalc(), "Number of days since 2000/01/01 and " & @YEAR & "/" & @MON & "/" & "01 00:00:00" &": " & $iCurrMonth,5) Local $iMonthx01 = $iCurrMonth - $iDaysx01 Local $iMonthx02 = $iMonthx01 - $iDaysx02 Local $iMonthx03 = $iMonthx02 - $iDaysx03 Local $iMonthx04 = $iMonthx03 - $iDaysx04 Local $iMonthx05 = $iMonthx04 - $iDaysx05 Local $iMonthx06 = $iMonthx05 - $iDaysx06 Local $iMonthx07 = $iMonthx06 - $iDaysx07 Local $iMonth01 = $iCurrMonth + $iDays01 Local $iMonth02 = $iMonth01 + $iDays02 Local $iMonth03 = $iMonth02 + $iDays03 Local $iMonth04 = $iMonth03 + $iDays04 Local $iMonth05 = $iMonth04 + $iDays05 Local $iMonth06 = $iMonth05 + $iDays06 Local $iMonth07 = $iMonth06 + $iDays07 Local $dateTo = $iMonth07 - 1 Local $dateFrom = $iMonthx03 local $iCmd = "" If $CmdLine[0] <> "" Then $iCmd = 1 $var = $CmdLine[1] if FileExists($var) Then Else MsgBox(4096, @AutoItExe & "Error!", "Input File not found chosen") Exit EndIf Else Local $sFilePath1 = "C:\Users\nickza\Desktop\TSSS\MASTER.xlsm" ;This file should already exist Local $message = "Please select Input MASTER.xlsm" Local $var = FileOpenDialog($message, @ScriptDir & "\", "XLSM (*.xlsm)", 1 + 2) EndIf If @error Then MsgBox(4096, "Error!", "No File(s) chosen") Exit Else $var = StringReplace($var, "|", @CRLF) Local $szDrive, $szDir, $szFName, $szExt Local $TestPath = _PathSplit($var, $szDrive, $szDir, $szFName, $szExt) Local $sPath = $szDrive & $szDir ; Show current date/time in the pc's format $myDate = @YEAR & "-" & @MON & "-" & @MDAY if _NowDate() <> $myDate Then MsgBox(4096, "", "You chose " & $var & @CRLF & "It is RECOMMENDED you use English Canada and short date format of yyyy-MM-dd before running this script. You are currently using: " & _NowDate() & "." & @CRLF &"Please change your regional settings. Aborting script now...", 30) Exit Else MsgBox(4096, "", "You chose " & $var, 5) EndIf EndIf ;NOT USED Func nextMonthFROM() Local $chkFrom = "ContentPlaceHolder1_MatchsDateTerr1_cmbDateFrom" Local $oFrom = _IEGetObjById($o_IE1, $chkFrom) ;ConsoleWrite(_IEPropertyGet($oFrom, "innerhtml") & @CRLF) $oFrom = _IEPropertyGet($oFrom, "innerhtml") Local $startFROM = StringInStr($oFrom, "Go to the next month") + 49 Local $finishFROM = $startFROM + 89 $oFrom = StringMid($oFrom, $startFROM, 89) ;MsgBox(0,"FROM", $oFrom) ConsoleWrite("nextMonthFROM:" & $oFrom & @CRLF) Return $oFrom EndFunc ;==>nextMonthFROM ;USED Func prevMonthFROM() Local $chkFrom = "ContentPlaceHolder1_MatchsDateTerr1_cmbDateFrom" Local $oFrom = _IEGetObjById($o_IE1, $chkFrom) ;ConsoleWrite(_IEPropertyGet($oFrom, "innerhtml") & @CRLF) $oFrom = _IEPropertyGet($oFrom, "innerhtml") Local $startFROM = StringInStr($oFrom, "Go to the previous month") + 54 Local $finishFROM = $startFROM + 88 $oFrom = StringMid($oFrom, $startFROM, 88) ;MsgBox(0,"FROM", $oFrom) ConsoleWrite("prevMonthFROM:" & $oFrom & @CRLF) Return $oFrom EndFunc ;==>prevMonthFROM ;NOT USED Func prevMonthTO() Local $chkTo = "ContentPlaceHolder1_MatchsDateTerr1_cmbDateTo" Local $oTo = _IEGetObjById($o_IE1, $chkTo) ;ConsoleWrite(_IEPropertyGet($oTo, "innerhtml") & @CRLF) $oTo = _IEPropertyGet($oTo, "innerhtml") Local $startTO = StringInStr($oTo, "Go to the previous month") + 53 Local $finishTO = $startTO + 87 $oTo = StringMid($oTo, $startTO, 87) ;MsgBox(0,"TO", $oTo) ConsoleWrite("prevMonthTO:" & $oTo & @CRLF) Return $oTo EndFunc ;==>prevMonthTO ;USED Func nextMonthTO() Local $chkTo = "ContentPlaceHolder1_MatchsDateTerr1_cmbDateTo" Local $oTo = _IEGetObjById($o_IE1, $chkTo) ;ConsoleWrite(_IEPropertyGet($oTo, "innerhtml") & @CRLF) $oTo = _IEPropertyGet($oTo, "innerhtml") Local $startTO = StringInStr($oTo, "Go to the next month") + 50 Local $finishTO = $startTO + 86 $oTo = StringMid($oTo, $startTO, 86) ;MsgBox(0,"TO", $oTo) ConsoleWrite("nextMonthTO:" & $oTo & @CRLF) Return $oTo EndFunc ;==>nextMonthTO Func getData($sLink) ClipPut("") Local $ArrayCont ;MsgBox(4096, "", "Getting data for: " & $sLink & " and saving to: " & $sPath & $sLink & ".xls" & @CRLF & "Previous Monthx01:" & $iMonthx01 & @CRLF & "Previous Monthx02:" & $iMonthx02 & @CRLF & "Previous Monthx03:" & $iMonthx03 & @CRLF & "Month01:" & $iMonth01 & @CRLF & "Month02:" & $iMonth02 & @CRLF & "Month03:" & $iMonth03 & @CRLF & "Month04:" & $iMonth04 & @CRLF & "Month05:" & $iMonth05 & @CRLF & "Month06:" & $iMonth06 & @CRLF & "Month07:" & $iMonth07 & @CRLF & "Date To:" & $dateTo & @CRLF & "Date From:" & $dateFrom, 10) MsgBox(4096, "", "Getting data for: " & $sLink & " and saving to: " & $sPath & $sLink & ".xls", 5) $o_IE1 = _IECreate('http://www.tsisports.ca/soccer/ligue14/index.aspx?champ=' & $sLink) ; Get a handle to the IE window. Local $hIE1 = _IEPropertyGet($o_IE1, "hwnd") $o_IE1.Navigate2('http://www.tsisports.ca/soccer/ligue14/l_cal2.aspx') ;If IsObj($o_IE1) Then ; MsgBox($MB_SYSTEMMODAL, "", "The variable is an object") ;Else ; MsgBox($MB_SYSTEMMODAL, "", "The variable is not an object") ;EndIf Sleep(2000) ;SET THE FROM DATE ;_IENavigate($o_IE1,prevMonthFROM()) _IENavigate($o_IE1, prevMonthFROM()) ; exit _IENavigate($o_IE1, prevMonthFROM()) _IENavigate($o_IE1, prevMonthFROM()) _IENavigate($o_IE1, prevMonthFROM()) Local $dateFrom = prevMonthFROM() $dateFrom = StringReplace($dateFrom, "','V", "','") ConsoleWrite("DATE FROM:" & $dateFrom & @CRLF) ;MsgBox (0,"DATE FROM", $dateFrom) _IENavigate($o_IE1, prevMonthFROM()) _IENavigate($o_IE1, $dateFrom) ;SET THE TO DATE ;_IENavigate($o_IE1,nextMonthTO()) _IENavigate($o_IE1, nextMonthTO()) _IENavigate($o_IE1, nextMonthTO()) _IENavigate($o_IE1, nextMonthTO()) _IENavigate($o_IE1, nextMonthTO()) _IENavigate($o_IE1, nextMonthTO()) _IENavigate($o_IE1, prevMonthFROM()) Local $dateTo = nextMonthTO() $dateTo = StringReplace($dateTo, "','V", "','") ConsoleWrite("DATE TO:" & $dateTo & @CRLF) ;MsgBox (0,"DATE TO", $dateTo) _IENavigate($o_IE1, nextMonthTO()) _IENavigate($o_IE1, $dateTo) ;Submit the data Local $oSubmit = _IEGetObjByName($o_IE1, "ctl00$ContentPlaceHolder1$MatchsDateTerr1$Button1") ;MsgBox("","",$oSubmit) _IEAction($oSubmit, "click") _IELoadWait($o_IE1) $oTable = _IETableGetCollection($o_IE1) Local $iNumTables = @extended ; MsgBox(0, "Table Info", "There are " & $iNumTables & " tables on the page") ;$htmltxt = _IEBodyReadHTML ( $o_IE1) ;MsgBox(0,'t0',$htmltxt) ;$aTableData = StringRegExp($htmltxt,'(?<=>)[^<\v"]+(?=<)',3) ;_ArrayDisplay($aTableData) Local $begin = TimerInit() ;init timer ;$oTable = _IETableGetCollection($o_IE1, 35) $oTable = _IETableGetCollection($o_IE1, $iNumTables - 2) Local $dif = TimerDiff($begin) ; stop timer MsgBox(0, "DONE", _NowCalc() & @CRLF & "It took: " & Round($dif / 1000, 1) & " seconds to process." & @CRLF & "PRESS OK TO CONTINUE...", 5) Local $begin = TimerInit() ;init timer $aTableData = _IETableWriteToArray($oTable, True) Local $dif = TimerDiff($begin) ; stop timer MsgBox(0, "DONE", _NowCalc() & @CRLF & "It took: " & Round($dif / 1000, 1) & " seconds to process." & @CRLF & "PRESS OK TO CONTINUE...", 5) $rows = UBound($aTableData) $cols = UBound($aTableData, 2) ;ProgressOn("Progress Bar", "Sample progress bar", "Working...") For $m = 0 To $rows - 1 ; ProgressSet($m) For $n = 0 To $cols - 1 $ArrayCont = $ArrayCont & Chr(9) & $aTableData[$m][$n] Next $ArrayCont = $ArrayCont & @CRLF Next ; ProgressSet(100, "Done!") ; Sleep(2000) ; ProgressOff() ClipPut($ArrayCont) If (ClipGet() <> "") Then Local $oExcel = _ExcelBookNew() ; _ExcelWriteSheetFromArray($oExcel,$aTableData) ; Exit ;Sleep(5000) WinActivate("Book1 -", "") WinWaitActive("Book1 -", "") Local $sFormat = "@" ;Format String tells _ExcelNumberFormat to make it a TEXT ;_ExcelNumberFormat($oExcel, $sFormat, 1, 36, 65000, 36) ;Start on Row 1, Start on Column 1, End on Row 5, End on Column 5 _ExcelNumberFormat($oExcel, $sFormat, 1, 10, 65000, 10) ;Start on Row 1, Start on Column 1, End on Row 5, End on Column 5 Send("^v") ; Exit Sleep(2000) $oExcel.Cells.Select $oExcel.Cells.EntireColumn.AutoFit ; $oExcel.Columns("A:AB").Select $oExcel.Columns("A:B").Select $oExcel.Selection.Delete; Shift:=xlToLeft $oExcel.Columns("L:IV").Select $oExcel.Selection.Delete; Shift:=xlToLeft $lastCell = $oExcel.Intersect($oExcel.ActiveSheet.Cells(1).Find("*", Default, $xlValues, $xlWhole, $xlByRows, $xlPrevious).EntireRow, $oExcel.ActiveSheet.Cells(1).Find("*", Default, $xlValues, $xlWhole, $xlByColumns, $xlPrevious).EntireColumn) Local $rowstoprocess = $lastCell.Row $oExcel.Rows($lastCell.Row + 1 & ":65000").Select $oExcel.Selection.Delete; Shift:=xlToLeft $oExcel.Range("A1").Select $oExcel.Cells.Select $oExcel.Cells.EntireColumn.AutoFit $oExcel.Range("A1").Select ;MsgBox(0, "Exiting", "Press OK to Save File and Exit") _ExcelBookSaveAs($oExcel, $sPath & $sLink & ".xls", "xls", 0, 1) ; Now we save it into the temp directory; overwrite existing file if necessary _ExcelBookClose($oExcel) ; And finally we close out _IEQuit($o_IE1) Else MsgBox(0, "CLIPBOARD is BLANK", "Unable to copy data to clipboard from PTS for:" & $sLink & ", aborting script now") Exit EndIf EndFunc ;==>getData ProgressOn("TSSS-DataHelper", "Working on getting data from site", "Working...", 100, 100, 16) ProgressSet(10) getData("C3L") ProgressSet(33) getData("CLE") ProgressSet(66) getData("CLV") ProgressSet(99) ProgressSet(100, "Done!") Sleep(2000) ProgressOff() ProgressOn("TSSS-DataHelper", "Now running MASTER.XLSM", "Working...", 100, 100, 16) ProgressSet(50) ;MsgBox(0, "Exiting", "DONE",5) Local $oExcel = _ExcelBookOpen($var) ;Now run the import script which will also export all the data as needed Send("^i") ProgressSet(100, "Done!") Sleep(2000) ProgressOff() ;------------------------------------------------------------------------------------------ ===END SOURCE===
×
×
  • Create New...