SQLite Report Generator

SUBMITTED BY: Guest

DATE: May 29, 2013, 7:08 a.m.

FORMAT: Text only

SIZE: 91.3 kB

Raw Download

HITS: 2247

Report
  1. #Region ;**** Directives created by AutoIt3Wrapper_GUI ****
  2. #AutoIt3Wrapper_Icon=SQLite2.ico
  3. #AutoIt3Wrapper_UseUpx=n
  4. #AutoIt3Wrapper_Compile_Both=y
  5. #AutoIt3Wrapper_UseX64=y
  6. #AutoIt3Wrapper_Res_Comment=SQL based Report Generator for SQLite databases
  7. #AutoIt3Wrapper_Res_Description=SQLite Report Generator
  8. #AutoIt3Wrapper_Res_Fileversion=1.0.0.6
  9. #AutoIt3Wrapper_Res_LegalCopyright=GreenCan
  10. #AutoIt3Wrapper_Res_requestedExecutionLevel=asInvoker
  11. #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
  12. #cs ----------------------------------------------------------------------------
  14. AutoIt Version: 3.3.8.1
  15. Author: GreenCan
  17. Other contributors:
  18. _ParseCSV: ProgAndy
  19. _DateCalc: Sean Hart <autoit@hartmail.ca>
  20. WM_NOTIFY() issue and some code optimization: jpm
  22. Script Function:
  23. SQL based Report Generator for SQLite databasess
  25. Known problem:
  26. None
  28. Updates:
  29. 1.0.0.6:
  30. Bug fixes (thanks jpm)
  31. Better GUI windows management
  32. Excel export optimized
  33. _COMError implemented
  35. #ce ----------------------------------------------------------------------------
  36. #include <Misc.au3>
  37. #include <SQLite.au3>
  38. #include <SQLite.dll.au3>
  39. #include <GUIConstantsEx.au3>
  40. #include <GuiListView.au3>
  41. #include <WindowsConstants.au3>
  42. #include <ButtonConstants.au3>
  43. #include <EditConstants.au3>
  44. #include <Date.au3>
  46. If _Singleton(@ScriptName, 1) = 0 Then Exit
  47. Opt("MustDeclareVars", 1)
  48. Opt("TrayIconHide", 1) ; hide the tray Icon
  50. ; Initializes COM handler
  51. ;~ Global $oAppError ; COM Error handler
  52. Global $oAppError = ObjEvent("AutoIt.Error", "_COMError")
  54. Global $iDebug = 1
  55. If Not @Compiled Then ConsoleWrite(@ScriptLineNumber & " Debug enabled" & @CR)
  57. #region Declare
  58. Const $iDark_Blue = 0x2601D3
  59. Const $iLight_Green = 0xEAFFE8
  61. ; postion of main window at startup
  62. Local $SM_VIRTUALWIDTH = 78
  63. Global $VirtualDesktopWidth = DllCall("user32.dll", "int", "GetSystemMetrics", "int", $SM_VIRTUALWIDTH)
  64. $VirtualDesktopWidth = $VirtualDesktopWidth[0]
  67. Global $Menu_ListView, $DoubleClicked, $iIndex = 1 ; _ArrayMenu() requires $Menu_ListView and $DoubleClicked to be declared as Global variables.
  68. Global $ListDelimiter = RegRead("HKEY_CURRENT_USER\Control Panel\International", "sList") ; Retrieve standard delimiter symbol
  69. Global $sDecimal = RegRead("HKEY_CURRENT_USER\Control Panel\International", "sDecimal") ; decimal symbol
  70. ; GUI settings:
  71. ; _ArrayMenu
  72. Global $iAM_Width = 400, $iAM_Height = 400, $iAM_Left = -1, $iAM_Top = -1, $iAM_OffsetWidth = Default, $iAM_OffsetHeight = Default
  73. ; Multiline GUI
  74. Global $hGUI_Multiline, $View_Multiline, $ColName
  75. Global $MultiLine_Width = 300, $MultiLine_Height = 300, $MultiLine_OffsetWidth = Default, $MultiLine_OffsetHeight = Default, $MultiLine_Left = 0, $MultiLine_Top = 0
  76. ; $SQLResultWindow GUI
  77. Local $SQLResultWindow, $iLV_Width = 800, $iLV_Height = 400, $iLV_OffsetWidth = Default, $iLV_OffsetHeight = Default, $iLV_Left = -1, $iLV_Top = -1
  78. ; Vertical View
  79. Global $GUI_VerticalView, $aVertView, $VertView_hListView, $bVerticalView_Resize, $VertView_Width = 250, $VertView_Height = 480, $VertView_OffsetWidth = 0, $VertView_OffsetHeight = 0, $VertView_Left = $VirtualDesktopWidth - $VertView_Width - 15, $VertView_Top = 0, $VerticalView_Copy, $bVerticalView_GUI = False
  80. Global $iLastItem = -1, $iLastsubitemNR = -1 ; used in WM_NOTIfY2
  81. Global $aNames ; Column titles
  82. Global $Choice ; menu selected item
  83. Global $FileHandle, $hListView
  84. ; Menu Loop vars
  85. Local $aParameter, $Parameter1, $Parameter2, $lblParameter1, $lblParameter2, $TitleParams, $sSQL, $iIndex, $iKeeppreviousIndex = 0, $sRow, $size
  86. Local $BtSQL, $BtClose, $BtExport, $DataBase, $hQuery, $aRow, $aResult, $Result, $Active_win, $To_Clip
  87. #endregion Declare
  89. #region ini
  90. Local $iniFileName = @ScriptDir & "\SQLite Reports.ini"
  91. If Not FileExists($iniFileName) Then Exit MsgBox(48, "Error", $iniFileName & " not found") ; startup error, keep the message
  92. Global $Db = IniRead($iniFileName, "General", "Database", "-")
  93. If Not FileExists(@ScriptDir & "\" & $Db) Then Exit MsgBox(48, "Error", "Database not found:" & @CR & @ScriptDir & "\" & $Db)
  94. Global $sDbDateFormat = IniRead($iniFileName, "General", "DateFormat", "YYYY/MM/DD")
  96. Local $_DesktopWidth = IniRead($iniFileName, "General", "DesktopWidth", 0) ; Desktop width of the previous session
  97. If $_DesktopWidth = $VirtualDesktopWidth Then
  98. ; same desktop width so we can use the stored window coordinates
  99. ; _ArrayMenu
  100. $Result = IniRead($iniFileName, "General", "Menu", $iAM_Width & "," & $iAM_Height & "," & $iAM_Left & "," & $iAM_Top )
  101. $aResult = StringSplit($Result, ",")
  102. $iAM_Width = $aResult[1]
  103. $iAM_Height = $aResult[2]
  104. $iAM_Left = $aResult[3]
  105. $iAM_Top = $aResult[4]
  106. ; Vertical View
  107. $Result = IniRead($iniFileName, "General", "VerticalView", $VertView_Width & "," & $VertView_Height & "," & $VertView_Left & "," & $VertView_Top )
  108. $aResult = StringSplit($Result, ",")
  109. $VertView_Width = $aResult[1]
  110. $VertView_Height = $aResult[2]
  111. $VertView_Left = $aResult[3]
  112. $VertView_Top = $aResult[4]
  113. ; $SQLResultWindow (Listview)
  114. $Result = IniRead($iniFileName, "General", "SQLResultView", $iLV_Width & "," & $iLV_Height & "," & $iLV_Left & "," & $iLV_Top )
  115. $aResult = StringSplit($Result, ",")
  116. $iLV_Width = $aResult[1]
  117. $iLV_Height = $aResult[2]
  118. $iLV_Left = $aResult[3]
  119. $iLV_Top = $aResult[4]
  120. ; Multiline GUI
  121. $Result = IniRead($iniFileName, "General", "MultilineView", $MultiLine_Width & "," & $MultiLine_Height & "," & $MultiLine_Left & "," & $MultiLine_Top )
  122. $aResult = StringSplit($Result, ",")
  123. $MultiLine_Width = $aResult[1]
  124. $MultiLine_Height = $aResult[2]
  125. $MultiLine_Left = $aResult[3]
  126. $MultiLine_Top = $aResult[4]
  127. EndIf
  129. Global $aSQL = IniReadSection($iniFileName, "SQL")
  130. Global $aReportTitles = IniReadSection($iniFileName, "Titles")
  131. Global $aParameters = IniReadSection($iniFileName, "Parameters")
  132. ; make a single array out of the multi-dimentional array
  133. Dim $aMenu[$aReportTitles[0][0]]
  134. For $i = 0 To $aReportTitles[0][0] - 1
  135. $aMenu[$i] = $aReportTitles[$i + 1][1]
  136. Next
  137. #endregion ini
  139. #region SQL Startup
  140. ;~ If @AutoItX64 Then
  141. ;~ Local $sSQliteDll = _SQLite_Startup(@ScriptDir & "SQLite3_x64.dll")
  142. ;~ If @error > 0 Then Exit MsgBox(16, "SQLite Error", "SQLite3_x64.dll Can't be Loaded!")
  143. ;~ Else
  144. ;~ Local $sSQliteDll = _SQLite_Startup(@ScriptDir & "\SQLite3.dll")
  145. ;~ If @error > 0 Then Exit MsgBox(16, "SQLite Error", "SQLite3.dll Can't be Loaded!")
  146. ;~ EndIf
  147. Local $sSQliteDll = _SQLite_Startup()
  148. If Not @Compiled Then ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ") : SQLite3.dll Loaded " & $sSQliteDll & " (" & _SQLite_LibVersion() & ")" & @CRLF)
  149. #endregion SQL Startup
  151. While 1 ; Menu loop
  152. $Choice = _ArrayMenu($aMenu, "Reports", "Select", $iIndex - 1, $iAM_Width, $iAM_Height, $iAM_Left, $iAM_Top)
  153. If $Choice == "" Then _Exit()
  154. $iIndex = _ArraySearch($aReportTitles, $Choice, 1, 0, 0, 0, 1, 1) ; find selected element number
  155. ; If Not @Compiled Then ConsoleWrite(@ScriptLineNumber & " You selected: " & $Choice & " ==> " & $iIndex & " ==> " & $aSQL[$iIndex][1] & @CR)
  157. #region ini debug
  158. ; if debug is enabled, reread the essential SQL sections
  159. ; this will allow to modify the SQL sections, titles and parameters and run the report without having to restart the script
  160. If $iDebug Then
  161. $aSQL = IniReadSection($iniFileName, "SQL")
  162. $aReportTitles = IniReadSection($iniFileName, "Titles")
  163. $aParameters = IniReadSection($iniFileName, "Parameters")
  165. ; make a single array out of the multi-dimentional array
  166. Dim $aMenu[$aReportTitles[0][0] + 1]
  167. For $i = 0 To $aReportTitles[0][0] - 1
  168. $aMenu[$i] = $aReportTitles[$i + 1][1]
  169. Next
  170. EndIf
  171. #endregion ini debug
  173. #region Parameters
  174. ; string parameters, maximum 2: %1% and %2%
  175. ; date parameters: %d1% and %d2%
  176. If StringInStr($aSQL[$iIndex][1], "1%") > 0 Then ; capture if SQL contains dynamic parameters
  177. If $iIndex <> $iKeeppreviousIndex Then ; remember the parameter only if the selection does not change
  178. $Parameter1 = ""
  179. $Parameter2 = ""
  180. EndIf
  182. If StringInStr($aSQL[$iIndex][1], "%2%") > 0 Then
  183. ; 2 string parameters
  184. $lblParameter1 = StringTrimRight($aParameters[$iIndex][1], StringLen($aParameters[$iIndex][1]) - StringInStr($aParameters[$iIndex][1], ",") + 1)
  185. $lblParameter2 = StringTrimLeft($aParameters[$iIndex][1], StringLen($lblParameter1) + 1)
  187. $aParameter = _InputBox("Parameters", _
  188. StringTrimRight($lblParameter1, StringLen($lblParameter1) - StringInStr($lblParameter1, "|") + 1) & ": ", $Parameter1, "", StringTrimLeft($lblParameter1, StringInStr($lblParameter1, "|")), _
  189. StringTrimRight($lblParameter2, StringLen($lblParameter2) - StringInStr($lblParameter2, "|") + 1) & ": ", $Parameter2, "", StringTrimLeft($lblParameter2, StringInStr($lblParameter2, "|")), _
  190. Default , Default , $iAM_Left + ($iAM_Width - 250)/2, $iAM_Top + ($iAM_Height - 170)/2 )
  191. If @error = 1 Then ContinueLoop ; Escape
  192. $Parameter1 = $aParameter[0]
  193. $Parameter2 = $aParameter[1]
  194. $sSQL = StringReplace($aSQL[$iIndex][1], "%1%", $aParameter[0])
  195. $sSQL = StringReplace($sSQL, "%2%", $aParameter[1])
  196. $TitleParams = " - " & StringTrimRight($lblParameter1, StringLen($lblParameter1) - StringInStr($lblParameter1, "|") + 1) & " : " & $Parameter1 & " - " & _
  197. StringTrimRight($lblParameter2, StringLen($lblParameter2) - StringInStr($lblParameter2, "|") + 1) & " : " & $Parameter2 & " "
  198. ElseIf StringInStr($aSQL[$iIndex][1], "%1%") > 0 Then
  199. ; 1 string parameter
  200. $lblParameter1 = $aParameters[$iIndex][1]
  202. $aParameter = _InputBox("Parameter", StringTrimRight($lblParameter1, StringLen($lblParameter1) - StringInStr($lblParameter1, "|") + 1) & ": ", $Parameter1, "", StringTrimLeft($lblParameter1, StringInStr($lblParameter1, "|")), _
  203. "", "", "", "", _
  204. Default , Default , $iAM_Left + ($iAM_Width - 250)/2, $iAM_Top + ($iAM_Height - 170)/2 )
  205. If @error = 1 Then ContinueLoop ; Escape
  206. $Parameter1 = $aParameter[0]
  207. $sSQL = StringReplace($aSQL[$iIndex][1], "%1%", $aParameter[0])
  208. $TitleParams = " - " & StringTrimRight($lblParameter1, StringLen($lblParameter1) - StringInStr($lblParameter1, "|") + 1) & " : " & $Parameter1
  209. ElseIf StringInStr($aSQL[$iIndex][1], "%d2%") > 0 Then
  210. ; 2 date parameters
  211. $lblParameter1 = StringTrimRight($aParameters[$iIndex][1], StringLen($aParameters[$iIndex][1]) - StringInStr($aParameters[$iIndex][1], ",") + 1)
  212. $lblParameter2 = StringTrimLeft($aParameters[$iIndex][1], StringLen($lblParameter1) + 1)
  213. $aParameter = _InputDate($Parameter1, $Parameter2)
  214. If @error Then ContinueLoop ; Escape
  215. $Parameter1 = DateFormat($aParameter[1], $sDbDateFormat)
  216. $sSQL = StringReplace($aSQL[$iIndex][1], "%d1%", $Parameter1)
  217. $Parameter2 = DateFormat($aParameter[2], $sDbDateFormat)
  218. $sSQL = StringReplace($sSQL, "%d2%", $Parameter2)
  219. $TitleParams = " - From " & $Parameter1 & " to " & $Parameter2 & " [" & $aParameter[0] + 1 & " days] "
  220. Else
  221. ; 1 date parameters
  222. $lblParameter1 = $aParameters[$iIndex][1]
  223. $aParameter = _InputDate($Parameter1, Default, False)
  224. If @error Then ContinueLoop ; Escape
  225. $Parameter1 = DateFormat($aParameter[1], $sDbDateFormat)
  226. $sSQL = StringReplace($aSQL[$iIndex][1], "%d1%", $Parameter1)
  227. $TitleParams = " - On " & $Parameter1
  229. EndIf
  230. $iKeeppreviousIndex = $iIndex
  231. Else
  232. $TitleParams = ""
  233. $sSQL = $aSQL[$iIndex][1]
  234. EndIf
  235. ;If Not @Compiled Then ConsoleWrite ("SQL: " & $sSQL & @CRLF)
  236. #endregion Parameters
  238. ; first open a new file
  239. $FileHandle = FileOpen(@TempDir & "\" & $Choice & ".csv", 2)
  240. If $FileHandle = -1 Then
  241. MsgBox(16, "Error", "Can't open file:" & @LF & @TempDir & "\" & $Choice & ".csv" & @LF & "Please check if the file is not in use!")
  242. ContinueLoop
  243. EndIf
  245. ToolTip("Creating Report, please wait", $iLV_Left + ($iLV_Width)/2, $iLV_Top + ($iLV_Height)/2, "Reports", 0, 2)
  247. #region SQL Open
  248. $DataBase = _SQLite_Open(@ScriptDir & "\" & $Db, $SQLITE_OPEN_READWRITE)
  249. If @error Then Exit MsgBox(16, "_SQLite_Open Error " & @error, "Can't Load Database " & @ScriptDir & "\" & $Db & @LF & "@extended " & @extended)
  250. #endregion SQL Open
  252. ; Create Listview GUI
  253. $SQLResultWindow = GUICreate($Choice, $iLV_Width, $iLV_Height, $iLV_Left, $iLV_Top, BitOR($GUI_SS_DEFAULT_GUI, $WS_MAXIMIZEBOX, $WS_SIZEBOX))
  254. If $iLV_OffsetWidth = Default Then
  255. ; remember the difference between the result of WinGetClientSize and the initial Width and Height of the GUI
  256. $size = WinGetClientSize($SQLResultWindow)
  257. $iLV_OffsetWidth = $iLV_Width - $size[0]
  258. $iLV_OffsetHeight = $iLV_Height - $size[1]
  259. EndIf
  260. $BtSQL = GUICtrlCreateButton('SQL', 5, $iLV_Height - 28, 85, 25)
  261. GUICtrlSetResizing(-1, $GUI_DOCKLEFT + $GUI_DOCKSIZE + $GUI_DOCKBOTTOM)
  263. $BtClose = GUICtrlCreateButton('Close', $iLV_Width - 90, $iLV_Height - 28, 85, 25)
  264. GUICtrlSetResizing(-1, $GUI_DOCKRIGHT + $GUI_DOCKSIZE + $GUI_DOCKBOTTOM)
  266. $BtExport = GUICtrlCreateButton('Export Excel', $iLV_Width - 180, $iLV_Height - 28, 85, 25)
  267. GUICtrlSetResizing(-1, $GUI_DOCKRIGHT + $GUI_DOCKSIZE + $GUI_DOCKBOTTOM)
  269. $hListView = GUICtrlCreateListView('', 0, 0, $iLV_Width, $iLV_Height - 30, Default, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT, $LVS_EX_HEADERDRAGDROP))
  270. GUICtrlSetResizing($hListView, $GUI_DOCKBORDERS)
  271. GUISetIcon("SQLite2.ico", 0)
  272. ; will display all records of an SQL
  274. $Result = _SQLite_Query(-1, $sSQL & ";", $hQuery)
  275. If $Result = $SQLITE_OK Then
  276. ;If Not @Compiled Then ConsoleWrite("SELECT success" & @LF & @LF)
  277. ; Set the Titles in the listview
  278. _SQLite_FetchNames($hQuery, $aNames) ; Read out Column Names
  279. If UBound($aNames) = 1 And $aNames[0] = "" Then
  280. ToolTip("")
  281. FileClose($FileHandle)
  282. GUIDelete($SQLResultWindow)
  283. MsgBox(64, "SQLite Info", "Command " & $sSQL & " performed")
  284. FileDelete(@TempDir & "\" & $Choice & ".csv")
  285. ContinueLoop
  286. Else
  287. ExportData($aNames)
  288. For $i = 0 To UBound($aNames) - 1
  289. _GUICtrlListView_InsertColumn($hListView, $i, $aNames[$i], 50)
  290. Next
  292. ; Rows
  293. While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
  294. ExportData($aRow)
  295. WEnd
  296. EndIf
  297. Else
  298. ToolTip("")
  299. FileClose($FileHandle)
  300. If _SQLite_ErrMsg() = "not an error" Then ; command that does not return results but has not to be considered as an error
  301. GUIDelete($SQLResultWindow)
  302. MsgBox(64, "SQLite Info", "Command " & $sSQL & " performed")
  303. FileDelete(@TempDir & "\" & $Choice & ".csv")
  304. ContinueLoop
  305. Else
  306. GUIDelete($SQLResultWindow)
  307. MsgBox(48, "SQLite Error", "Error: " & _SQLite_ErrMsg() & @CR & "Query: " & $sSQL)
  308. FileDelete(@TempDir & "\" & $Choice & ".csv")
  309. ContinueLoop
  310. EndIf
  311. EndIf
  313. #region SQL Close
  314. _SQLite_Close()
  315. #endregion SQL Close
  317. FileClose($FileHandle)
  319. $aResult = _ParseCSV(@TempDir & "\" & $Choice & ".csv", $ListDelimiter) ; convert csv into an array to populate the Listview
  321. If UBound($aResult) = 0 Then
  322. ToolTip("")
  323. GUIDelete($SQLResultWindow)
  324. MsgBox(64, "SQLite Info", "Command " & $sSQL & " performed")
  325. FileDelete(@TempDir & "\" & $Choice & ".csv")
  326. ContinueLoop
  327. EndIf
  329. WinSetTitle($Choice, '', $Choice & $TitleParams & " (" & UBound($aResult) - 1 & " rows)")
  330. GUISetState(@SW_SHOW, $SQLResultWindow)
  332. _GUICtrlListView_RegisterSortCallBack($hListView, True, True)
  333. _GUICtrlListView_BeginUpdate($hListView)
  335. ;_ArrayDisplay($aResult, @ScriptLineNumber)
  336. For $i = 1 To UBound($aResult) - 1 ; start at row 2 to skip titles
  337. $sRow = ""
  338. For $ii = 0 To UBound($aResult, 2) - 1
  339. $sRow &= $aResult[$i][$ii] & "|"
  340. Next
  341. $sRow = StringTrimRight($sRow, 1)
  342. GUICtrlCreateListViewItem($sRow, $hListView) ; create the listview elements
  343. Next
  344. For $i = 0 To UBound($aNames) - 1
  345. GUICtrlSendMsg($hListView, $LVM_SETCOLUMNWIDTH, $i, -1)
  346. GUICtrlSendMsg($hListView, $LVM_SETCOLUMNWIDTH, $i, -2) ; title
  347. Next
  348. _GUICtrlListView_EndUpdate($hListView)
  349. GUIRegisterMsg($WM_NOTIfY, "WM_NOTIfY2") ; new WM_NOTIfY2 to capture click and double click in this listview
  350. ToolTip("")
  352. While 1
  353. Switch GUIGetMsg()
  355. Case $GUI_EVENT_CLOSE
  356. ; Check If Active window closed is "Vertical View" , If correct, Then only close this GUI
  357. $Active_win = WinGetActive()
  358. If $Active_win[0] = "Vertical View" Then
  359. $size = WinGetPos($GUI_VerticalView)
  360. $VertView_Left = $size[0]
  361. $VertView_Top = $size[1]
  362. $size = WinGetClientSize($GUI_VerticalView)
  363. $VertView_Width = $size[0] + $VertView_OffsetWidth
  364. $VertView_Height = $size[1] + $VertView_OffsetHeight
  365. GUIDelete($GUI_VerticalView)
  366. $bVerticalView_GUI = False
  367. WinActivate($SQLResultWindow)
  368. ElseIf $Active_win[0] = "Cell Content" Then
  369. $size = WinGetPos($hGUI_Multiline)
  370. $MultiLine_Left = $size[0]
  371. $MultiLine_Top = $size[1]
  372. $size = WinGetClientSize($hGUI_Multiline)
  373. $MultiLine_Width = $size[0] + $MultiLine_OffsetWidth
  374. $MultiLine_Height = $size[1] + $MultiLine_OffsetHeight
  375. GUIDelete($hGUI_Multiline)
  376. WinActivate($SQLResultWindow)
  377. Else
  378. ExitLoop
  379. EndIf
  381. Case $BtClose
  382. ; Check If Active window closed is "Vertical View" , If correct, Then only close this GUI
  383. $Active_win = WinGetActive()
  384. If $Active_win[0] = "Vertical View" Then
  385. $size = WinGetPos($GUI_VerticalView)
  386. $VertView_Left = $size[0]
  387. $VertView_Top = $size[1]
  388. $size = WinGetClientSize($GUI_VerticalView)
  389. $VertView_Width = $size[0] + $VertView_OffsetWidth
  390. $VertView_Height = $size[1] + $VertView_OffsetHeight
  391. GUIDelete($GUI_VerticalView)
  392. $bVerticalView_GUI = False
  393. WinActivate($SQLResultWindow)
  394. ElseIf $Active_win[0] = "Cell Content" Then
  395. $size = WinGetPos($hGUI_Multiline)
  396. $MultiLine_Left = $size[0]
  397. $MultiLine_Top = $size[1]
  398. $size = WinGetClientSize($hGUI_Multiline)
  399. $MultiLine_Width = $size[0] + $MultiLine_OffsetWidth
  400. $MultiLine_Height = $size[1] + $MultiLine_OffsetHeight
  401. GUIDelete($hGUI_Multiline)
  402. WinActivate($SQLResultWindow)
  403. Else
  404. ExitLoop
  405. EndIf
  407. Case $bVerticalView_GUI And $VerticalView_Copy ; only allow copy to clipboard If Vertical Vew is open
  408. $To_Clip = ""
  409. For $i_I = 0 To UBound($aVertView) - 1
  410. ; exact column Order
  411. For $i_C = 0 To UBound($aVertView, 2) - 1
  412. $To_Clip = $To_Clip & $aVertView[$i_I][$i_C] & $ListDelimiter
  413. Next
  414. $To_Clip = StringTrimRight($To_Clip, 1) & @CRLF
  415. Next
  416. ; put to clipboard
  417. ClipPut($To_Clip)
  418. ;MsgBox(0, "_ArrayToClip() Test", ClipGet())
  420. Case $BtSQL
  421. View_SQL($sSQL, $Choice)
  423. Case $hListView ; clicked column header, thus sort
  424. If UBound($aResult) > 200 Then
  425. ToolTip("Sorting", Default, Default, "Please wait", 1, 2)
  426. EndIf
  427. _GUICtrlListView_SortItems($hListView, GUICtrlGetState($hListView))
  428. ToolTip("")
  430. Case $BtExport
  431. $Result = FileMove(@TempDir & "\" & $Choice & ".csv", @MyDocumentsDir, 1)
  432. If $Result = 0 Then
  433. MsgBox(16, "Error", "Can't copy file:" & @LF & @TempDir & "\" & $Choice & ".csv to " & @MyDocumentsDir & @LF & "Please check if the file is not in use!")
  434. Else
  435. Local $oExcel = ObjCreate("Excel.Application")
  436. If Not IsObj($oExcel) Then ; Excel not found, another program maybe?
  437. ShellExecute(Chr(34) & @MyDocumentsDir & "\" & $Choice & ".csv" & Chr(34))
  438. Else
  439. ; any error will be intercepted by the Comm error handler
  440. With $oExcel
  441. .Visible = 1
  442. .WorkBooks.OpenText(Chr(34) & @MyDocumentsDir & "\" & $Choice & ".csv" & Chr(34), _
  443. Default, 1, 1, -4142, False, False, False, False, False, True, $ListDelimiter)
  444. .ActiveWorkbook.Sheets(1).Select()
  445. .ActiveSheet.Range(.Columns(1), .Columns(_GUICtrlListView_GetColumnCount($hListView))).AutoFit
  446. .ActiveSheet.Range(.Rows(1), .Rows(_GUICtrlListView_GetItemCount($hListView)+1)).AutoFit
  447. .Activesheet.Range("A2").Select
  448. .ActiveWindow.FreezePanes = True
  449. EndWith
  451. EndIf
  452. EndIf
  454. EndSwitch
  455. WEnd
  456. $size = WinGetPos($SQLResultWindow)
  457. $iLV_Left = $size[0]
  458. $iLV_Top = $size[1]
  459. $size = WinGetClientSize($SQLResultWindow)
  460. $iLV_Width = $size[0] + $iLV_OffsetWidth
  461. $iLV_Height = $size[1] + $iLV_OffsetHeight
  462. GUIDelete($SQLResultWindow)
  463. FileDelete(@TempDir & "\" & $Choice & ".csv")
  464. WEnd
  465. _Exit()
  466. #FUNCTION# ==============================================================
  467. Func _Exit()
  468. IniWrite($iniFileName, "General", "DesktopWidth", $VirtualDesktopWidth) ; remember desktop width
  469. ; write GUI coordinates
  470. IniWrite($iniFileName, "General", "Menu", $iAM_Width & "," & $iAM_Height & "," & $iAM_Left & "," & $iAM_Top )
  471. IniWrite($iniFileName, "General", "VerticalView", $VertView_Width & "," & $VertView_Height & "," & $VertView_Left & "," & $VertView_Top )
  472. IniWrite($iniFileName, "General", "SQLResultView", $iLV_Width & "," & $iLV_Height & "," & $iLV_Left & "," & $iLV_Top )
  473. IniWrite($iniFileName, "General", "MultilineView", $MultiLine_Width & "," & $MultiLine_Height & "," & $MultiLine_Left & "," & $MultiLine_Top )
  474. Exit
  475. EndFunc ;==>_Exit
  476. #FUNCTION# ==============================================================
  477. Func View_MultilineCell($Cell_content, $Column_name = "")
  478. Local $Msg2, $window_open, $size
  480. If $Cell_content = "" Then
  481. ; clicked on no-nmultiline cell, so close the window
  482. $window_open = WinList("Cell Content")
  483. If $window_open[0][0] > 0 Then
  484. $size = WinGetPos($hGUI_Multiline)
  485. $MultiLine_Left = $size[0]
  486. $MultiLine_Top = $size[1]
  487. $size = WinGetClientSize($hGUI_Multiline)
  488. $MultiLine_Width = $size[0] + $MultiLine_OffsetWidth
  489. $MultiLine_Height = $size[1] + $MultiLine_OffsetHeight
  490. GUIDelete($hGUI_Multiline)
  491. EndIf
  492. Return
  493. Else
  494. $window_open = WinList("Cell Content") ; check If window already exists
  495. If $window_open[0][0] = 0 Then
  496. ; If the window does not exist yet, create it
  497. Local Const $_ARRAYCONSTANT_WS_MAXIMIZEBOX = 0x00010000
  498. Local Const $_ARRAYCONSTANT_WS_MINIMIZEBOX = 0x00020000
  499. Local Const $_ARRAYCONSTANT_WS_SIZEBOX = 0x00040000
  500. $hGUI_Multiline = GUICreate("Cell Content", $MultiLine_Width, $MultiLine_Height, $MultiLine_Left, $MultiLine_Top, BitOR($_ARRAYCONSTANT_WS_SIZEBOX, $_ARRAYCONSTANT_WS_MINIMIZEBOX, $_ARRAYCONSTANT_WS_MAXIMIZEBOX), $WS_EX_TOPMOST, $SQLResultWindow);($WS_POPUPWINDOW), $WS_EX_TOPMOST,$SQLResultWindow) ;( $WS_POPUPWINDOW $WS_BORDER) $WS_EX_TOPMOST,$SQLResultWindow)
  501. $ColName = GUICtrlCreateLabel("Content of " & $Column_name, 5, 2, $MultiLine_Width, 20)
  502. $View_Multiline = GUICtrlCreateEdit($Cell_content, 5, 20, $MultiLine_Width - 10, $MultiLine_Height - 50, BitOR($ES_AUTOVSCROLL, $ES_AUTOHSCROLL, $ES_READONLY, $ES_WANTRETURN, $WS_HSCROLL, $WS_VSCROLL))
  503. GUICtrlSetResizing($View_Multiline, $GUI_DOCKTOP + $GUI_DOCKBOTTOM + $GUI_DOCKRIGHT + $GUI_DOCKLEFT)
  504. GUICtrlSetResizing($ColName, $GUI_DOCKTOP + $GUI_DOCKBOTTOM + $GUI_DOCKLEFT)
  505. GUICtrlSetColor(-1, $iDark_Blue)
  506. GUICtrlSetBkColor(-1, $iLight_Green)
  507. GUICtrlCreateLabel("Click on any non-multiline cell to hide this pop-up", 35, $MultiLine_Height - 15, $MultiLine_Width)
  508. GUICtrlSetFont(-1, 7, 400)
  509. GUISetIcon("SQLite2.ico", 0)
  510. If $MultiLine_OffsetWidth = Default Then
  511. ; remember the difference between the result of WinGetClientSize and the initial Width and Height of the GUI
  512. $size = WinGetClientSize($hGUI_Multiline)
  513. $MultiLine_OffsetWidth = $MultiLine_Width - $size[0]
  514. $MultiLine_OffsetHeight = $MultiLine_Height - $size[1]
  515. EndIf
  516. Else
  517. ; window already exist so only change the content
  518. GUICtrlSetData($ColName, "Content of " & $Column_name) ; column title
  519. GUICtrlSetData($View_Multiline, $Cell_content) ; cell content
  520. EndIf
  521. GUISetState()
  522. Return
  524. EndIf
  525. EndFunc ;==>View_MultilineCell
  526. #FUNCTION# ==============================================================
  527. Func WinGetActive()
  528. ; Default Retrieving active window in AutoIt
  529. ; So I've had this problem for a long time and I've never figured out how to do it until now.
  530. ; This function returns an array with two values:
  531. ; $aWindow[0] returns the title of the window
  532. ; $aWindow[1] returns the window handle
  533. Dim $aWindow[2]
  534. Dim $aWinlist
  535. $aWinlist = WinList()
  536. For $i_I = 1 To $aWinlist[0][0]
  537. If $aWinlist[$i_I][0] <> "" And IsVisible($aWinlist[$i_I][1]) Then
  538. $aWindow[0] = $aWinlist[$i_I][0]
  539. $aWindow[1] = $aWinlist[$i_I][1]
  540. ExitLoop
  541. EndIf
  542. Next
  543. Return $aWindow
  544. EndFunc ;==>WinGetActive
  545. #FUNCTION# ==============================================================
  546. Func IsVisible($handle)
  547. If BitAND(WinGetState($handle), 2) Then
  548. Return 1
  549. Else
  550. Return 0
  551. EndIf
  552. EndFunc ;==>IsVisible
  553. #FUNCTION# ==============================================================
  554. ; #FUNCTION# ====================================================================================================================
  555. ; Name...........: _ParseCSV
  556. ; Description ...: Reads a CSV-file
  557. ; Syntax.........: _ParseCSV($sFile, $sDelimiters=',', $sQuote='"', $iFormat=0)
  558. ; Parameters ....: $sFile - File to read or string to parse
  559. ; $sDelimiters - [optional] Fieldseparators of CSV, mulitple are allowed (default: ,;)
  560. ; $sQuote - [optional] Character to quote strings (default: ")
  561. ; $iFormat - [optional] Encoding of the file (default: 0):
  562. ; |-1 - No file, plain data given
  563. ; |0 or 1 - automatic (ASCII)
  564. ; |2 - Unicode UTF16 Little Endian reading
  565. ; |3 - Unicode UTF16 Big Endian reading
  566. ; |4 or 5 - Unicode UTF8 reading
  567. ; Return values .: Success - 2D-Array with CSV data (0-based)
  568. ; Failure - 0, sets @error to:
  569. ; |1 - could not open file
  570. ; |2 - error on parsing data
  571. ; |3 - wrong format chosen
  572. ; Author ........: ProgAndy
  573. ; Modified.......:
  574. ; Remarks .......:
  575. ; Related .......: _WriteCSV
  576. ; Link ..........:
  577. ; Example .......:
  578. ; ===============================================================================================================================
  579. Func _ParseCSV($sFile, $sDelimiters = ',;', $sQuote = '"', $iFormat = 0)
  580. Local Static $aEncoding[6] = [0, 0, 32, 64, 128, 256]
  581. If $iFormat < -1 Or $iFormat > 6 Then
  582. Return SetError(3, 0, 0)
  583. ElseIf $iFormat > -1 Then
  584. Local $hFile = FileOpen($sFile, $aEncoding[$iFormat]), $sLine, $aTemp, $aCSV[1], $iReserved, $iCount
  585. If @error Then Return SetError(1, @error, 0)
  586. $sFile = FileRead($hFile)
  587. FileClose($hFile)
  588. EndIf
  589. If $sDelimiters = "" Or IsKeyword($sDelimiters) Then $sDelimiters = ',;'
  590. If $sQuote = "" Or IsKeyword($sQuote) Then $sQuote = '"'
  591. $sQuote = StringLeft($sQuote, 1)
  592. Local $srDelimiters = StringRegExpReplace($sDelimiters, '[\\\^\-\[\]]', '\\\0')
  593. Local $srQuote = StringRegExpReplace($sQuote, '[\\\^\-\[\]]', '\\\0')
  594. Local $sPattern = StringReplace(StringReplace('(?m)(?:^|[,])\h*(["](?:[^"]|["]{2})*["]|[^,\r\n]*)(\v+)?', ',', $srDelimiters, 0, 1), '"', $srQuote, 0, 1)
  595. Local $aREgex = StringRegExp($sFile, $sPattern, 3)
  596. If @error Then Return SetError(2, @error, 0)
  597. $sFile = '' ; save memory
  598. Local $iBound = UBound($aREgex), $iIndex = 0, $iSubBound = 1, $iSub = 0
  599. Local $aResult[$iBound][$iSubBound]
  600. For $i = 0 To $iBound - 1
  601. Select
  602. Case StringLen($aREgex[$i]) < 3 And StringInStr(@CRLF, $aREgex[$i])
  603. $iIndex += 1
  604. $iSub = 0
  605. ContinueLoop
  606. Case StringLeft(StringStripWS($aREgex[$i], 1), 1) = $sQuote
  607. $aREgex[$i] = StringStripWS($aREgex[$i], 3)
  608. $aResult[$iIndex][$iSub] = StringReplace(StringMid($aREgex[$i], 2, StringLen($aREgex[$i]) - 2), $sQuote & $sQuote, $sQuote, 0, 1)
  609. Case Else
  610. $aResult[$iIndex][$iSub] = $aREgex[$i]
  611. EndSelect
  612. $aREgex[$i] = 0 ; save memory
  613. $iSub += 1
  614. If $iSub = $iSubBound Then
  615. $iSubBound += 1
  616. ReDim $aResult[$iBound][$iSubBound]
  617. EndIf
  618. Next
  619. If $iIndex = 0 Then $iIndex = 1
  620. ReDim $aResult[$iIndex][$iSubBound]
  621. Return $aResult
  622. EndFunc ;==>_ParseCSV
  623. #FUNCTION# ==============================================================
  624. Func ExportData($aRow)
  625. ; will export all records fetched by SQL
  626. Local $Row = ""
  627. For $ColItem In $aRow
  628. If StringIsFloat($ColItem) Then
  629. $ColItem = StringReplace($ColItem, ".", $sDecimal) ; convert Floating Point with decimal separator compliant with Excel
  630. Else ; If StringInStr($ColItem,@LF)>0 Then
  631. $ColItem = Chr(34) & StringReplace($ColItem, Chr(34), "'") & Chr(34) ;Replace all " by ' before putting the string between double quotes to avoid Excel conflicts
  632. EndIf
  634. $Row &= $ColItem & $ListDelimiter
  635. Next
  637. $Row = StringTrimRight($Row, 1) & @CRLF
  638. FileWriteLine($FileHandle, $Row)
  640. Return
  641. EndFunc ;==>ExportData
  642. #FUNCTION# ==============================================================
  643. ; #FUNCTION# ====================================================================================================================
  644. ; Name ..........: _ArrayMenu
  645. ; Description ...: Creates a menu from Array items
  646. ; Syntax ........: _ArrayMenu($aMenu[, $sGUITitle= ""[, $sTitle = "Select"[, [$RowSelected = 1[, $iWidth = Default[, $iHeight = Default[, $iLeft = Default[, $iTop = Default]]]]]]])
  647. ; Parameters ....: $aMenu - A single dimension array containing the items to display as a menu
  648. ; $sGUITitle - [optional] Title for the selection
  649. ; $sTitle - [optional] The title of the selection box (default = "Select")
  650. ; $RowSelected - [optional] The Row that has focus in the selection box (default = 1)
  651. ; $iWidth - [optional] The width of the window.
  652. ; $iHeight - [optional] The height of the window.
  653. ; $iLeft - [optional] The left side of the dialog box. By default (-1), the window is centered. If defined, top must also be defined.
  654. ; $iTop - [optional] The top of the dialog box. Default (-1) is centered
  655. ;
  656. ; Return values .: Success (if a selection was made) - Returns Name of element
  657. ; Failure (if escape was pressed) - Returns an empty string
  658. ;
  659. ; Requirements ..: _ArrayMenu requires $Menu_ListView and $DoubleClicked to be declared as Global variables.
  660. ; Global $Menu_ListView, $DoubleClicked
  661. ; Author ........: Greencan
  662. ; Related .......:
  663. ; Remarks .......: _ArrayMenu always shows TOPMOST
  664. ; Link ..........;
  665. ; Example .......; Yes
  666. ; ===============================================================================================================================
  667. Func _ArrayMenu($aMenu, $sGUITitle = "", $sTitle = "Select", $RowSelected = 1, $iWidth = Default, $iHeight = Default, $iLeft = Default, $iTop = Default)
  668. $DoubleClicked = False ; Declared as Global var
  669. Local $mainmsg, $size
  670. ;~ Local $Menu_Window = GuiCreate(" " & $sGUITitle, $iWidth, $iHeight, $iLeft, $iTop, $WS_DLGFRAME, BitOR($WS_EX_TOOLWINDOW, $WS_EX_TOPMOST))
  671. ;~ Local $Menu_Window = GUICreate(" " & $sGUITitle, $iWidth, $iHeight, $iLeft, $iTop, Default, $WS_EX_TOPMOST)
  672. Local $Menu_Window = GUICreate(" " & $sGUITitle, $iWidth, $iHeight, $iLeft, $iTop, BitOR($GUI_SS_DEFAULT_GUI, $WS_SIZEBOX), $WS_EX_TOPMOST)
  673. Local $aSize = WinGetClientSize($Menu_Window)
  674. If $iLV_OffsetWidth = Default Then
  675. ; remember the difference between the result of WinGetClientSize and the initial Width and Height of the GUI
  676. $iAM_Width = $iWidth
  677. $iAM_Height = $iHeight
  678. $iAM_OffsetWidth = $iAM_Width - $aSize[0]
  679. $iAM_OffsetHeight = $iAM_Height - $aSize[1]
  680. EndIf
  681. Local $Menu_ListView = GUICtrlCreateListView($sTitle, 5, 30, $aSize[0] - 10, $aSize[1] - 65, BitOR($LVS_NOCOLUMNHEADER, $LVS_SHOWSELALWAYS, $LVS_SINGLESEL))
  682. GUICtrlCreateLabel($sTitle, 5, 10, $iWidth - 10, 15)
  684. For $i = 0 To UBound($aMenu) - 1
  685. GUICtrlCreateListViewItem($aMenu[$i], $Menu_ListView) ; Menu item
  686. Next
  688. Local $MainbuttonGo = GUICtrlCreateButton("Go", 5, $aSize[1] - 32, 100, 30, $BS_DEFPUSHBUTTON)
  689. Local $MainbuttonExit = GUICtrlCreateButton("Exit", $aSize[0] - 105, $aSize[1] - 32, 100, 30)
  690. GUISetIcon("SQLite2.ico", 0)
  692. ; Finished - Change the column width to fit the item text
  693. GUICtrlSendMsg($Menu_ListView, $LVM_SETCOLUMNWIDTH, 0, -1)
  694. GUICtrlSendMsg($Menu_ListView, $LVM_SETCOLUMNWIDTH, 0, -2) ; title
  696. GUIRegisterMsg($WM_NOTIfY, "WM_NOTIFY") ; double click
  698. GUISetState(@SW_SHOW, $Menu_Window)
  700. _GUICtrlListView_ClickItem($Menu_ListView, $RowSelected)
  702. ; Run the GUI until the dialog is closed
  703. While 1
  704. $mainmsg = GUIGetMsg()
  705. Select
  707. Case $DoubleClicked Or $mainmsg = $MainbuttonGo
  708. $DoubleClicked = False
  709. $Choice = StringTrimRight(GUICtrlRead(GUICtrlRead($Menu_ListView)), 1)
  710. If $Choice <> "" Then
  711. $size = WinGetPos($Menu_Window)
  712. $iAM_Left = $size[0]
  713. $iAM_Top = $size[1]
  714. $size = WinGetClientSize($Menu_Window)
  715. $iAM_Width = $size[0] + $iAM_OffsetWidth
  716. $iAM_Height = $size[1] + $iAM_OffsetHeight
  717. GUIDelete($Menu_Window)
  718. Return $Choice
  719. EndIf
  721. Case $mainmsg = $GUI_EVENT_CLOSE Or $mainmsg = $MainbuttonExit; The End
  722. $size = WinGetPos($Menu_Window)
  723. $iAM_Left = $size[0]
  724. $iAM_Top = $size[1]
  725. $size = WinGetClientSize($Menu_Window)
  726. $iAM_Width = $size[0] + $iAM_OffsetWidth
  727. $iAM_Height = $size[1] + $iAM_OffsetHeight
  728. GUIDelete($Menu_Window)
  729. Return ""
  731. EndSelect
  733. WEnd ; Loop 1
  735. Return
  736. EndFunc ;==>_ArrayMenu
  737. #FUNCTION# ==============================================================
  738. ; #FUNCTION# ====================================================================================================================
  739. ; Name ..........: _InputBox
  740. ; Description ...: Alternative InputBox for one or two inputs
  741. ; Syntax ........: _InputBox($sTitle, $sPrompt1[, $sDefault1 = ""[, $sPwdChar1 = ""[, $sInputSetTip1 = ""[, $sPrompt2 = ""[, $sDefault2 = ""[, $sPwdChar2 = ""[, $sInputSetTip2 = ""[, $iWidth = Default[, $iHeight = Default[, $iLeft = Default[, $iTop = Default, $hwnd = Default]]]]]]]]]]])
  742. ; Parameters ....: $sTitle - The title of the input box.
  743. ; $sPrompt1 - Message to the user indicating what kind of input is expected.
  744. ; $sDefault1 - [optional] The value that the input box starts with (default = "")
  745. ; $sPwdChar1 - [optional] Password character if required, usualy '*' (default = "")
  746. ; $sInputSetTip1 - [optional] Tip text associated with the input (default = "")
  747. ; $sPrompt2 - [optional] Second Message to the user indicating what kind of input is expected.
  748. ; $sDefault2 - [optional] The value that the Second input box starts with (default = "")
  749. ; $sPwdChar2 - [optional] Password character if required, usualy '*' (default = "")
  750. ; $sInputSetTip2 - [optional] Tip text associated with the Second input (default = "")
  751. ; $iWidth - [optional] The width of the window.
  752. ; $iHeight - [optional] The height of the window.
  753. ; $iLeft - [optional] The left side of the dialog box. By default (-1), the window is centered. If defined, top must also be defined.
  754. ; $iTop - [optional] The top of the dialog box. Default (-1) is centered
  755. ; $hwnd - [optional] The window handle to use as the parent for this dialog
  756. ;
  757. ; Return values .: Success - Returns a single dimension array containing two elements:
  758. ; element 0 contains the value of input 1 and element 1 contains the value of input2
  759. ; If input 2 is not used, value of element 1 will be empty
  760. ; Failure - Returns @error 1
  761. ;
  762. ; Requirements ..:
  763. ; Author ........: Greencan
  764. ; Related .......:
  765. ; Remarks .......: The size of the dialog window will adapt dynamically to the number of required inputs
  766. ; Link ..........;
  767. ; Example .......; Yes
  768. ; ===============================================================================================================================
  769. Func _InputBox($sTitle, $sPrompt1, $sDefault1 = "", $sPwdChar1 = "", $sInputSetTip1 = "", $sPrompt2 = "", $sDefault2 = "", $sPwdChar2 = "", $sInputSetTip2 = "", $iWidth = Default, $iHeight = Default, $iLeft = Default, $iTop = Default, $hwnd = Default)
  770. Local $sInput1, $sInput2, $bOK, $bCancel, $msg, $avArray[2]
  771. ; ConsoleWrite(@ScriptLineNumber & " >" & $sInputSetTip1 & "< - >" & $sInputSetTip2 & "<" & @CR)
  772. If $iWidth = Default Then $iWidth = 250
  773. If $iHeight = Default Then
  774. If $sPrompt2 <> "" Then
  775. $iHeight = 170
  776. Else
  777. $iHeight = 130
  778. EndIf
  779. EndIf
  781. GUICreate($sTitle, $iWidth, $iHeight, $iLeft, $iTop, $WS_SYSMENU, -1, $hwnd)
  782. GUICtrlCreateLabel($sPrompt1, 10, 10, $iWidth - 20, 20)
  783. If $sPwdChar1 <> "" Then
  784. $sInput1 = GUICtrlCreateInput($sDefault1, 15, 30, $iWidth - 30, 20, $ES_PASSWORD)
  785. Else
  786. $sInput1 = GUICtrlCreateInput($sDefault1, 15, 30, $iWidth - 30, 20)
  787. EndIf
  788. If $sInputSetTip1 <> "" Then GUICtrlSetTip(-1, $sInputSetTip1)
  790. If $sPrompt2 <> "" Then
  791. GUICtrlCreateLabel($sPrompt2, 10, 60, $iWidth - 20, 20)
  792. If $sPwdChar2 <> "" Then
  793. $sInput2 = GUICtrlCreateInput($sDefault2, 15, $iHeight - 90, $iWidth - 30, 20, $ES_PASSWORD)
  794. Else
  795. $sInput2 = GUICtrlCreateInput($sDefault2, 15, $iHeight - 90, $iWidth - 30, 20)
  796. EndIf
  797. If $sInputSetTip2 <> "" Then GUICtrlSetTip(-1, $sInputSetTip2)
  798. EndIf
  800. $bOK = GUICtrlCreateButton("OK", 30, $iHeight - 55, 77, 22, $BS_DEFPUSHBUTTON)
  801. $bCancel = GUICtrlCreateButton("Cancel", $iWidth - 107, $iHeight - 55, 77, 22)
  802. GUISetState()
  804. $msg = 0
  805. While 1
  806. $msg = GUIGetMsg()
  807. Select
  808. Case $msg = $bOK
  809. $avArray[0] = GUICtrlRead($sInput1)
  810. If $sPrompt2 <> "" Then $avArray[1] = GUICtrlRead($sInput2)
  811. GUIDelete()
  812. Return $avArray
  813. Case $msg = $bCancel Or $msg = $GUI_EVENT_CLOSE
  814. GUIDelete()
  815. Return SetError(1, 0, 0)
  816. EndSelect
  817. WEnd
  818. EndFunc ;==>_InputBox
  819. #FUNCTION# ==============================================================
  820. ; #FUNCTION# ====================================================================================================================
  821. ; Name ..........: _InputDate
  822. ; Description ...: Creates a Dialog for a single day or a period entry
  823. ; Syntax ........: _InputDate([$dFrom = Default[, $dTill = Default[, $bPeriod = Default[, $bLegality = Default]]]])
  824. ; Parameters ....: $dFrom - [optional] Date (if $bPeriod is True, starting the period ) (default = Default)
  825. ; $dTill - [optional] Date ending the Period, unused if $bPeriod is False (default = Default)
  826. ; $bPeriod - [optional] Bolean, if false, dialog will input one date (default = Default)
  827. ; $bLegality - [optional] Period Legality Check (default = Default)
  828. ;
  829. ; Return values .: Success - A single dimension array containing following elemnts:
  830. ; [0] - Number of days between From and Till ( is 0 if single date input)
  831. ; [1] - From date
  832. ; [2] - Till date ( is empty if single date input)
  833. ; Failure - Returns @error
  834. ; |1 - Escape pressed
  835. ; |2 - $bPeriod is not boolean
  836. ; |3 - $bLegality is not boolean
  837. ;
  838. ; Requirements ..:
  839. ; Author ........: Greencan
  840. ; Related .......:
  841. ; Remarks .......: Input dates Default to Date_Time_GetLocalTime() unless provided
  842. ; in 'yyyy/mm/dd' format
  843. ; Illegal formats will be replaced by Date_Time_GetLocalTime()
  844. ; function uses DateCalc.au3 (_DateCalc udf) from Sean Hart
  845. ; Link ..........;
  846. ; Example .......; Yes
  847. ; ===============================================================================================================================
  848. Func _InputDate($dFrom = Default, $dTill = Default, $bPeriod = Default, $bLegality = Default)
  850. If $dFrom = Default Then $dFrom = ""
  851. If $dTill = Default Then $dTill = ""
  852. If $bPeriod = Default Then $bPeriod = True
  853. If Not IsBool($bPeriod) Then Return SetError(2, 0, 0)
  854. If $bLegality = Default Then $bLegality = True
  855. If Not IsBool($bLegality) Then Return SetError(3, 0, 0)
  857. Local $_msg, $_ok, $Days_between, $sTitle, $iHeight
  858. Local $sDateFormat = RegRead("HKCU\Control Panel\International", "sShortDate") ; get system locale short Dateformat
  860. If $bPeriod Then
  861. $sTitle = "Set Period"
  862. $iHeight = 170
  863. Else
  864. $sTitle = "Set Date"
  865. $iHeight = 130
  866. EndIf
  868. ;~ Default , Default , $iAM_Left + (170 - 250)/2, $iAM_Top + ($iHeight - 170)/2 )
  869. ;~ $iAM_Width & "," & $iAM_Height & "," & $iAM_Left & "," & $iAM_Top
  870. ConsoleWrite(@ScriptLineNumber & " $iAM_Left:" & $iAM_Left & " $iAM_Top:" & $iAM_Top & " $iAM_Width:" & $iAM_Width & " $iAM_Height:" & $iAM_Height & " " & $iAM_Left + (172)/2 & " " & $iAM_Top + ( $iHeight)/2 & @CR)
  871. ;~ Local $input_Window = GUICreate($sTitle, 170, $iHeight, -1, -1, 0x00800000) ; $WS_BORDER = 0x00800000 requires #include <WindowsConstants.au3>
  872. Local $input_Window = GUICreate($sTitle, 170, $iHeight, $iAM_Left + ($iAM_Width - 172)/2, $iAM_Top + ($iAM_Height - $iHeight)/2, 0x00800000) ; $WS_BORDER = 0x00800000 requires #include <WindowsConstants.au3>
  874. If $bPeriod Then
  875. ; period
  876. GUICtrlCreateLabel("From:", 10, 22)
  877. $dFrom = GUICtrlCreateDate($dFrom, 50, 20, 100, 20, 0) ; $DTS_SHORTDATEFORMAT = 0 #include <DateTimeConstants.au3>
  878. GUICtrlCreateLabel("To:", 10, 52)
  879. $dTill = GUICtrlCreateDate($dTill, 50, 50, 100, 20, 0) ; $DTS_SHORTDATEFORMAT = 0 #include <DateTimeConstants.au3>
  880. Else
  881. ; Single date
  882. GUICtrlCreateLabel("Date:", 10, 22)
  883. $dFrom = GUICtrlCreateDate($dFrom, 50, 20, 100, 20, 0) ; $DTS_SHORTDATEFORMAT = 0 #include <DateTimeConstants.au3>
  884. EndIf
  886. $_ok = GUICtrlCreateButton("OK", 50, $iHeight - 60, 70, 20, 0x0001) ; $BS_DEFPUSHBUTTON = 0x0001 requires #include <ButtonConstants.au3>
  888. GUISetState()
  890. ; Run the GUI until the dialog is closed
  891. Do
  892. $_msg = GUIGetMsg()
  893. If $_msg = $_ok Then
  894. If $bPeriod Then
  895. $Days_between = _DateDiff('D', _DateCalc(GUICtrlRead($dFrom), $sDateFormat), _DateCalc(GUICtrlRead($dTill), $sDateFormat))
  896. ; period legality check
  897. If $bLegality = 1 And $Days_between < 0 Then
  898. GUICtrlCreateLabel("Incorrect Period", 48, 82, 120)
  899. GUICtrlSetColor(-1, 0xff0000)
  900. Beep(2000, 10)
  901. ContinueLoop
  902. Else
  903. Dim $Period[3]
  904. $Period[0] = $Days_between
  905. $Period[1] = GUICtrlRead($dFrom)
  906. $Period[2] = GUICtrlRead($dTill)
  907. GUIDelete($input_Window)
  908. Return $Period
  909. EndIf
  910. Else
  911. Dim $Period[3]
  912. $Period[0] = 0
  913. $Period[1] = GUICtrlRead($dFrom)
  914. $Period[2] = ""
  915. GUIDelete($input_Window)
  916. Return $Period
  917. EndIf
  918. GUISetState()
  919. EndIf
  920. Until $_msg = -3 ; $GUI_EVENT_CLOSE = -3 requires #include <GUIConstantsEx.au3>
  921. GUIDelete($input_Window)
  922. Return SetError(1, 0, 0)
  924. EndFunc ;==>_InputDate
  925. #FUNCTION# ==============================================================
  926. ; #FUNCTION# ====================================================================================================================
  927. ; Name ..........: _DateCalc
  928. ; Description ...: Returns the Date [and time] in format YYYY/MM/DD [HH:MM:SS],
  929. ; give the date / time in the system or specified format.
  930. ; Syntax ........: _DateCalc($sSysDateTime[, $dFormat = ""[, $tFormat = ""]])
  931. ; Parameters ....: $sSysDateTime - Input date [and time]
  932. ; $dFormat - [optional] Format of input date (default = "")
  933. ; $tFormat - [optional] Format of input time (default = "")
  934. ;
  935. ; Return values .: Success - Returns Date in in format YYYY/MM/DD [HH:MM:SS]
  936. ; Failure - Returns @error
  937. ; |1 - input date badly formatted
  938. ; |2 - input time badly formatted
  939. ;
  940. ; Requirements ..: None
  941. ; Author ........: Sean Hart <autoit@hartmail.ca>
  942. ; Related .......:
  943. ; Remarks .......: Date format can be provided without any separators only in
  944. ; the format YYYYMMDD.
  945. ; If system format is used it is current user format, NOT
  946. ; default user format (which may be different).
  947. ; 2 digit years converted: 81 - 99 -> 1981-1999
  948. ; 00 - 80 -> 2000-2080²
  949. ; Link ..........;
  950. ; Example .......; Yes
  951. ; ===============================================================================================================================
  952. Func _DateCalc($sSysDateTime, $dFormat = "", $tFormat = "")
  953. Local $sDay, $sMonth, $sYear, $sHour, $sMin, $sSec, $dSep, $tSep, $am, $pm, $split1[9], $split2[9], $sSysDate, $sSysTime, $isAM, $isPM, $sTestDate
  954. ;Local $dFormat, $tFormat
  956. ; Read default system time formats and separators from registry unless provided
  957. If $dFormat = "" Then
  958. $dFormat = RegRead("HKEY_CURRENT_USER\Control Panel\International", "sShortDate")
  959. $dSep = RegRead("HKEY_CURRENT_USER\Control Panel\International", "sDate")
  960. Else
  961. ; Extract separator from date format by finding first non recognised character
  962. For $x = 1 To StringLen($dFormat)
  963. If (Not (StringMid($dFormat, $x, 1) = "y")) And (Not (StringMid($dFormat, $x, 1) = "m")) And (Not (StringMid($dFormat, $x, 1) = "d")) Then
  964. $dSep = StringMid($dFormat, $x, 1)
  965. ExitLoop
  966. EndIf
  967. Next
  968. EndIf
  969. If $tFormat = "" Then
  970. $tFormat = RegRead("HKEY_CURRENT_USER\Control Panel\International", "sShortDate")
  971. $tSep = RegRead("HKEY_CURRENT_USER\Control Panel\International", "sDate")
  972. $am = RegRead("HKEY_CURRENT_USER\Control Panel\International", "s1159")
  973. $pm = RegRead("HKEY_CURRENT_USER\Control Panel\International", "s2359")
  974. Else
  975. ; Extract separator from time format by finding first non hour character
  976. For $x = 1 To StringLen($tFormat)
  977. If (Not (StringMid($tFormat, $x, 1) = "h")) Then
  978. $tSep = StringMid($tFormat, $x, 1)
  979. ExitLoop
  980. EndIf
  981. Next
  982. $am = "AM"
  983. $pm = "PM"
  984. EndIf
  986. ; Separate date and time if included (make break at first space)
  987. If StringInStr($sSysDateTime, " ") Then
  988. $sSysDate = StringLeft($sSysDateTime, StringInStr($sSysDateTime, " ") - 1)
  989. $sSysTime = StringStripWS(StringReplace($sSysDateTime, $sSysDate, ""), 1)
  990. Else
  991. $sSysDate = $sSysDateTime
  992. $sSysTime = ""
  993. EndIf
  995. ; Simple check of input date format (look for separators and unexpected non numeric characters)
  996. $sTestDate = StringReplace($sSysDate, $dSep, "")
  997. $sTestDate = "1" & $sTestDate
  998. If (String(Number($sTestDate)) <> $sTestDate) Then
  999. SetError(1)
  1000. Return
  1001. EndIf
  1002. If (StringInStr($sSysDate, $dSep) = 0) And ($dSep <> "") Then
  1003. SetError(1)
  1004. Return
  1005. EndIf
  1006. If $sSysTime <> "" Then
  1007. $sTestDate = StringReplace($sSysTime, $tSep, "")
  1008. $sTestDate = StringReplace($sTestDate, $am, "")
  1009. $sTestDate = StringReplace($sTestDate, $pm, "")
  1010. $sTestDate = StringReplace($sTestDate, " ", "")
  1011. $sTestDate = "1" & $sTestDate
  1012. If (StringInStr($sSysTime, $tSep) = 0) Or (String(Number($sTestDate)) <> $sTestDate) Then
  1013. SetError(2)
  1014. Return
  1015. EndIf
  1016. EndIf
  1018. ; Break up date components (using format as a template), unless format is YYYYMMDD
  1019. If $dFormat = "YYYYMMDD" Then
  1020. $sYear = StringMid($sSysDate, 1, 4)
  1021. $sMonth = StringMid($sSysDate, 5, 2)
  1022. $sDay = StringMid($sSysDate, 7, 2)
  1023. Else
  1024. $split1 = StringSplit($dFormat, $dSep)
  1025. $split2 = StringSplit($sSysDate, $dSep)
  1026. For $x = 1 To $split1[0]
  1027. If StringInStr($split1[$x], "M") Then $sMonth = $split2[$x]
  1028. If StringInStr($split1[$x], "d") Then $sDay = $split2[$x]
  1029. If StringInStr($split1[$x], "y") Then $sYear = $split2[$x]
  1030. Next
  1031. EndIf
  1033. ; Pad values with 0 if required and fix 2 digit year
  1034. If StringLen($sMonth) = 1 Then $sMonth = "0" & $sMonth
  1035. If StringLen($sDay) = 1 Then $sDay = "0" & $sDay
  1036. If StringLen($sYear) = 2 Then
  1037. If $sYear > 80 Then
  1038. $sYear = "19" & $sYear
  1039. Else
  1040. $sYear = "20" & $sYear
  1041. EndIf
  1042. EndIf
  1044. ; Break up time components (if given)
  1045. If $sSysTime <> "" Then
  1046. ; Look for AM/PM and note it, Then remove from the string
  1047. $isPM = 0
  1048. If StringInStr($sSysTime, $am) Then
  1049. $sSysTime = StringReplace($sSysTime, " " & $am, "")
  1050. $isPM = 1
  1051. ElseIf StringInStr($sSysTime, $pm) Then
  1052. $sSysTime = StringReplace($sSysTime, " " & $pm, "")
  1053. $isPM = 2
  1054. EndIf
  1055. $split1 = StringSplit($tFormat, $tSep)
  1056. $split2 = StringSplit($sSysTime, $tSep)
  1057. $sSec = "00"
  1058. For $x = 1 To $split2[0]
  1059. If StringInStr($split1[$x], "h") Then $sHour = $split2[$x]
  1060. If StringInStr($split1[$x], "m") Then $sMin = $split2[$x]
  1061. If StringInStr($split1[$x], "s") Then $sSec = $split2[$x]
  1062. Next
  1064. ; Clean up time values (change hour to 24h and 0 pad values)
  1065. If ($isPM = 1) And ($sHour = 12) Then $sHour = "00"
  1066. If ($isPM = 2) And ($sHour < 12) Then $sHour = $sHour + 12
  1067. If StringLen($sHour) = 1 Then $sHour = "0" & $sHour
  1068. If StringLen($sMin) = 1 Then $sMin = "0" & $sMin
  1069. If StringLen($sSec) = 1 Then $sSec = "0" & $sSec
  1071. ; Return date with time
  1072. Return $sYear & "/" & $sMonth & "/" & $sDay & " " & $sHour & ":" & $sMin & ":" & $sSec
  1073. Else
  1074. ; Return date only
  1075. Return $sYear & "/" & $sMonth & "/" & $sDay
  1076. EndIf
  1077. EndFunc ;==>_DateCalc
  1078. #FUNCTION# ==============================================================
  1079. ; #FUNCTION# ====================================================================================================================
  1080. ; Name ..........: DateFormat
  1081. ; Description ...: Universal date format converter
  1082. ; Converts the (default regional setting) PC Date format into any other specIfied date format:
  1083. ; - convert date into another date format (for example ddd dd MMMM, yyyy)
  1084. ; - convert date into day (d or dd)
  1085. ; - convert date into month ( m, mm or mmm)
  1086. ; - convert date into year (yy or yyyy)
  1087. ; Syntax ........: DateFormat($InputDate[, $DateFmt = "mm/dd/yyyy"])
  1088. ; Parameters ....: $InputDate - Date to be converted
  1089. ; $DateFmt - [optional] Date format to convert to (default = "mm/dd/yyyy")
  1090. ;
  1091. ; Return values .: Success - Returns Date in in requested format
  1092. ; Failure (if escape was pressed) - Returns an empty string
  1093. ; Return values .: Success - A single dimension array containing following elemnts:
  1094. ; [0] - Number of days between From and Till ( is 0 if single date input)
  1095. ; [1] - From date
  1096. ; [2] - Till date ( is empty if single date input)
  1097. ; Failure - Returns @error
  1098. ; |1 - Input date badly formatted
  1099. ; |2 - Invalid Year in Date
  1100. ; |3 - Invalid Month in Date
  1101. ; |4 - Invalid Day in Date
  1102. ;
  1103. ; Requirements ..: _DateCalc() - The udf is included in this script
  1104. ; Author ........: Greencan
  1105. ; Related .......:
  1106. ; Remarks .......: Part of source code was borrowed from the Date conversion example provided by DaRam
  1107. ; http://www.autoitscript.com/forum/index.php?showtopic=76984&view=findpost&p=557834
  1108. ; DateCalc.au3 (_DateCalc udf) by Sean Hart
  1109. ; http://www.autoitscript.com/forum/index.php?showtopic=14084&view=findpost&p=96173
  1110. ; Link ..........;
  1111. ; Example .......; Yes
  1112. ; DateFormat( _NowCalcDate() , "dd-MM-yyyy" )
  1113. ; ===============================================================================================================================
  1114. Func DateFormat($InputDate, $DateFmt = "mm/dd/yyyy")
  1115. If StringLen($InputDate) < 6 Then Return SetError(1, 0, 0) ; Invalid Date ==> ddmmyy = 6
  1117. Local $sDateFormat = RegRead("HKCU\Control Panel\International", "sShortDate") ; get system locale short Dateformat
  1118. If $DateFmt = "" Then $DateFmt = "dd/mm/yyyy"
  1119. Local $DateValue = _DateCalc($InputDate, $sDateFormat) ; convert the date to yyyy/mm/dd
  1120. $DateValue = StringSplit($DateValue, "/")
  1121. ; error checking
  1122. If @error Then Return SetError(1, 0, 0) ; Invalid Input Date
  1123. If $DateValue[0] < 3 Then Return SetError(1, 0, 0) ; less than 3 parts in date not possible
  1124. If Int(Number($DateValue[1])) < 0 Then Return SetError(2, 0, 0) ; Invalid Year in Date
  1125. If Int(Number($DateValue[2])) < 1 Or Int(Number($DateValue[2])) > 12 Then Return SetError(3, 0, 0) ; Invalid Month in Date
  1126. If Int(Number($DateValue[3])) < 1 Or Int(Number($DateValue[3])) > 31 Then Return SetError(4, 0, 0) ; Invalid Day in Date
  1128. If Int(Number($DateValue[1])) < 100 Then $DateValue[1] = StringLeft(@YEAR, 2) & $DateValue[1]
  1129. $InputDate = $DateFmt
  1130. $InputDate = StringReplace($InputDate, "d", "@") ; Convert All Day References to @
  1131. $InputDate = StringReplace($InputDate, "m", "#") ; Convert All Month References to #
  1132. $InputDate = StringReplace($InputDate, "y", "&") ; Convert All Year References to &
  1133. $InputDate = StringReplace($InputDate, "&&&&", $DateValue[1]) ; Century and Year
  1134. $InputDate = StringReplace($InputDate, "&&", StringRight($DateValue[1], 2)) ; Year Only
  1135. $InputDate = StringReplace($InputDate, "&", "") ; Discard leftover Year Indicator
  1136. $InputDate = StringReplace($InputDate, "####", _DateToMonth($DateValue[2], 0)) ; Long Month Name
  1137. $InputDate = StringReplace($InputDate, "###", _DateToMonth($DateValue[2], 1)) ; Short Month Name
  1138. If StringLen($DateValue[1]) < 2 Then
  1139. $InputDate = StringReplace($InputDate, "##", "0" & $DateValue[2]) ; Month Number 2 Digit
  1140. Else
  1141. $InputDate = StringReplace($InputDate, "##", $DateValue[2]) ; Month Number 2 Digit
  1142. EndIf
  1143. $InputDate = StringReplace($InputDate, "#", Int($DateValue[2])) ; Month Number
  1144. Local $iPos = _DateToDayOfWeek($DateValue[1], $DateValue[2], $DateValue[3]) ; Day of Week Number
  1145. $InputDate = StringReplace($InputDate, "@@@@", _DateDayOfWeek($iPos, 0)) ; Long Weekday Name
  1146. $InputDate = StringReplace($InputDate, "@@@", _DateDayOfWeek($iPos, 1)) ; Short Weekday Name
  1147. $InputDate = StringReplace($InputDate, "@@", $DateValue[3]) ; Day Number 2 Digit
  1148. $InputDate = StringReplace($InputDate, "@", Int($DateValue[3])) ; Day Number
  1150. Return $InputDate
  1151. EndFunc ;==>DateFormat
  1152. #FUNCTION# ==============================================================
  1153. Func WM_NOTIFY($hwnd, $MsgID, $wParam, $lParam)
  1154. ; notify used for Double click in Main menu
  1155. Local $tagNMHDR, $code
  1156. $tagNMHDR = DllStructCreate("struct; hwnd hWndFrom;uint_ptr IDFrom;INT Code; endstruct", $lParam)
  1157. If @error Then Return 0
  1158. $code = DllStructGetData($tagNMHDR, 3)
  1159. ;If $wParam = $Menu_ListView And $code = -3 Then
  1160. If $code = -3 Then
  1161. $DoubleClicked = True
  1162. EndIf
  1163. ;If $wParam = $Menu_ListView Then $DoubleClicked = True
  1164. Return $GUI_RUNDEFMSG
  1165. EndFunc ;==>WM_NOTIFY
  1166. #FUNCTION# ==============================================================
  1167. Func WM_NOTIfY2($hwnd, $iMsg, $wParam, $lParam)
  1168. ; this udf will show a window with the content of a cell. Displays only If the data contains @CRLF (multiline)
  1169. ; the udf replaces WM_NOTIfY in Listviewer ($hListView)
  1170. Local $hWndFrom, $iCode, $tNMHDR, $hWndListView, $tInfo, $iItem, $subitemNR, $Column_attribute, $sToolTipData, $aTitle, $ColumnOrder, $aItem, $Position, $Rows_to_Clip
  1171. $hWndListView = $hListView
  1172. If Not IsHWnd($hListView) Then $hWndListView = GUICtrlGetHandle($hListView)
  1173. $tNMHDR = DllStructCreate($tagNMHDR, $lParam)
  1174. $hWndFrom = HWnd(DllStructGetData($tNMHDR, "hWndFrom"))
  1175. $iCode = DllStructGetData($tNMHDR, "Code")
  1176. Switch $hWndFrom
  1177. Case $hWndListView
  1178. Switch $iCode
  1179. Case $NM_CLICK;
  1180. $tInfo = DllStructCreate($tagNMLISTVIEW, $lParam)
  1181. $iItem = DllStructGetData($tInfo, "Item")
  1182. $subitemNR = DllStructGetData($tInfo, "SubItem")
  1183. $Column_attribute = _GUICtrlListView_GetColumn($hListView, $subitemNR)
  1184. If $iLastItem = $iItem And $iLastsubitemNR = $subitemNR Then Return 0
  1185. $iLastItem = $iItem
  1186. $iLastsubitemNR = $subitemNR
  1187. $sToolTipData = _GUICtrlListView_GetItemText($hListView, $iItem, $subitemNR)
  1188. ; Check LF, CR only
  1189. ; first check If LF in the memo
  1190. $Position = StringInStr($sToolTipData, @LF)
  1191. If $Position > 0 Then
  1192. ; Then check If @CRLF in the memo
  1193. $Position = StringInStr($sToolTipData, @CRLF)
  1194. If $Position > 0 Then ;can be displayed as is
  1195. View_MultilineCell($sToolTipData, $Column_attribute[5] & " Row " & $iItem + 1)
  1196. Else ; must convert @LF to @CRLF before displaying
  1197. View_MultilineCell(StringReplace($sToolTipData, @LF, @CRLF), $Column_attribute[5] & " Row " & $iItem + 1)
  1198. EndIf
  1199. Else
  1200. $Position = StringInStr($sToolTipData, @CR)
  1201. If $Position > 0 Then
  1202. ; Then check If @CRLF in the memo
  1203. $Position = StringInStr($sToolTipData, @CRLF)
  1204. If $Position > 0 Then ;can be displayed as is
  1205. View_MultilineCell($sToolTipData, $Column_attribute[5] & " Row " & $iItem + 1)
  1206. Else ; must convert @CR to @CRLF before displaying
  1207. View_MultilineCell(StringReplace($sToolTipData, @CR, @CRLF), $Column_attribute[5] & " Row " & $iItem + 1)
  1208. EndIf
  1209. Else
  1210. View_MultilineCell("")
  1211. EndIf
  1212. EndIf
  1213. Case $NM_DBLCLK
  1214. $ColumnOrder = _GUICtrlListView_GetColumnOrderArray($hListView) ; exact column order, required If the user changed the layout in the listview
  1215. ; exact column Order
  1216. ; titles first
  1217. $aTitle = $aNames
  1218. Dim $aVertView[UBound($aTitle) + 1][2]
  1219. $aVertView[0][0] = "Column title"
  1220. $aVertView[0][1] = "Content"
  1221. ; Set the titles according to column setting
  1222. For $i_C = 1 To UBound($ColumnOrder) - 1
  1223. ; only export the column If width > 0
  1224. If _GUICtrlListView_GetColumnWidth($hListView, $i_C - 1) > 0 Then $aVertView[$i_C][0] = $aTitle[$ColumnOrder[$i_C]]
  1225. Next
  1226. $Rows_to_Clip = StringSplit(_GUICtrlListView_GetSelectedIndices($hListView), "|")
  1227. For $i_I = 1 To UBound($Rows_to_Clip) - 1
  1228. ; exact column Order
  1229. For $i_C = 1 To UBound($ColumnOrder) - 1
  1230. $aItem = _GUICtrlListView_GetItem($hListView, $Rows_to_Clip[$i_I] + 0, $ColumnOrder[$i_C])
  1231. ; only export the column If width > 0
  1232. If _GUICtrlListView_GetColumnWidth($hListView, $i_C - 1) > 0 Then $aVertView[$i_C][1] = $aItem[3]
  1233. Next
  1234. Next
  1235. ; cleanup empty elements in array
  1236. For $i_C = UBound($aVertView) - 1 To 1 Step -1
  1237. If $aVertView[$i_C][0] = "" Then _ArrayDelete($aVertView, $i_C)
  1238. Next
  1239. _ArrayDisplay2($aVertView, "Vertical View", 2, -1, 0, ";")
  1241. EndSwitch
  1242. EndSwitch
  1243. Return $GUI_RUNDEFMSG
  1244. EndFunc ;==>WM_NOTIfY2
  1245. #FUNCTION# ==============================================================
  1246. Func View_SQL($sSQL, $sTitle)
  1247. Local $msg
  1248. ; get the coordiantes of $SQLResultWindow
  1249. $size = WinGetPos($SQLResultWindow)
  1250. $iLV_Left = $size[0]
  1251. $iLV_Top = $size[1]
  1252. $size = WinGetClientSize($SQLResultWindow)
  1253. $iLV_Width = $size[0] + $iLV_OffsetWidth
  1254. $iLV_Height = $size[1] + $iLV_OffsetHeight
  1256. Local $window_width = $iLV_Width - 100
  1257. Local $window_heigth = $iLV_Height - 100
  1259. ; format SQL script a bit
  1260. ; the formating does not consider UNION,
  1261. Local $stempSQL, $sSQLPart1, $sSQLPart2, $sSQLPart3, $sSQLPart4, $sSQLPart5, $sSQLPart6
  1262. $stempSQL = $sSQL
  1264. ; LIMIT - should be after FROM !
  1265. If StringInStr($stempSQL, "LIMIT", 0, -1) > 0 Then
  1266. If StringInStr($stempSQL, "LIMIT", 0, -1) > StringInStr($stempSQL, "FROM", 0, -1) Then
  1267. $sSQLPart6 = @CRLF & "LIMIT" & @CRLF & @TAB & StringTrimLeft($stempSQL, StringInStr($stempSQL, "LIMIT", 0, -1) + 5)
  1268. $stempSQL = StringStripWS(StringTrimRight($stempSQL, StringLen($stempSQL) - StringInStr($stempSQL, "LIMIT", 0, -1) + 1), 2)
  1269. EndIf
  1270. EndIf
  1272. ; ORDER BY - should be after FROM !
  1273. If StringInStr($stempSQL, "ORDER BY", 0, -1) > 0 Then
  1274. If StringInStr($stempSQL, "ORDER BY", 0, -1) > StringInStr($stempSQL, "FROM", 0, -1) Then
  1275. $sSQLPart5 = @CRLF & "ORDER BY" & @CRLF & @TAB & StringTrimLeft($stempSQL, StringInStr($stempSQL, "ORDER BY", 0, -1) + 8)
  1276. $stempSQL = StringStripWS(StringTrimRight($stempSQL, StringLen($stempSQL) - StringInStr($stempSQL, "ORDER BY", 0, -1) + 1), 2)
  1277. EndIf
  1278. EndIf
  1280. ; GROUP BY - should be after FROM !
  1281. If StringInStr($stempSQL, "GROUP BY", 0, -1) > 0 Then
  1282. If StringInStr($stempSQL, "GROUP BY", 0, -1) > StringInStr($stempSQL, "FROM", 0, -1) Then
  1283. $sSQLPart4 = @CRLF & "GROUP BY" & @CRLF & @TAB & StringTrimLeft($stempSQL, StringInStr($stempSQL, "GROUP BY", 0, -1) + 8)
  1284. $stempSQL = StringStripWS(StringTrimRight($stempSQL, StringLen($stempSQL) - StringInStr($stempSQL, "GROUP BY", 0, -1) + 1), 2)
  1285. EndIf
  1286. EndIf
  1288. ; WHERE - should be after FROM !
  1289. If StringInStr($stempSQL, "WHERE", 0, -1) > 0 Then
  1290. If StringInStr($stempSQL, "WHERE", 0, -1) > StringInStr($stempSQL, "FROM", 0, -1) Then
  1291. $sSQLPart3 = @CRLF & "WHERE" & @CRLF & @TAB & StringTrimLeft($stempSQL, StringInStr($stempSQL, "WHERE", 0, -1) + 5)
  1292. $stempSQL = StringStripWS(StringTrimRight($stempSQL, StringLen($stempSQL) - StringInStr($stempSQL, "WHERE", 0, -1) + 1), 2)
  1293. EndIf
  1294. EndIf
  1296. ; INNER JOIN
  1297. If StringInStr($stempSQL, "INNER JOIN", 0, -1) > 0 Then
  1298. $stempSQL = StringReplace($stempSQL, "INNER JOIN", @CRLF & @TAB & @TAB & "INNER JOIN")
  1299. EndIf
  1301. ; FROM
  1302. If StringInStr($stempSQL, "FROM", 0, -1) > 0 Then
  1303. $sSQLPart2 = @CRLF & "FROM" & @CRLF & @TAB & StringTrimLeft($stempSQL, StringInStr($stempSQL, "FROM", 0, -1) + 4)
  1304. $stempSQL = StringStripWS(StringTrimRight($stempSQL, StringLen($stempSQL) - StringInStr($stempSQL, "FROM", 0, -1) + 1), 2)
  1305. EndIf
  1307. ; SELECT
  1308. If StringInStr($stempSQL, "SELECT", 0, 1) > 0 Then
  1309. $sSQLPart1 = "SELECT" & @CRLF & @TAB & StringTrimLeft($stempSQL, StringInStr($stempSQL, "SELECT", 0, 1) + 6)
  1310. $stempSQL = StringStripWS(StringTrimRight($stempSQL, StringLen($stempSQL) - StringInStr($stempSQL, "SELECT", 0, 1) + 1), 2)
  1311. EndIf
  1313. Local $hGUI_View_SQL = GUICreate("SQL", $window_width, $window_heigth, $iLV_Left + 50, $iLV_Top + 50, $WS_CAPTION, $WS_EX_TOPMOST, $SQLResultWindow)
  1314. GUICtrlCreateLabel($sTitle, 10, 2, 600)
  1315. Local $ViewSQL = GUICtrlCreateEdit($sSQLPart1 & $sSQLPart2 & $sSQLPart3 & $sSQLPart4 & $sSQLPart5 & $sSQLPart6 & @CRLF, 5, 25, $window_width - 10, $window_heigth - 80, BitOR($ES_AUTOVSCROLL, $ES_AUTOHSCROLL, $ES_READONLY, $ES_WANTRETURN, $WS_HSCROLL, $WS_VSCROLL))
  1316. GUICtrlSetResizing(-1, $GUI_DOCKTOP + $GUI_DOCKBOTTOM)
  1317. GUICtrlSetColor(-1, $iDark_Blue)
  1318. GUICtrlSetBkColor(-1, $iLight_Green)
  1319. Local $buttonOK = GUICtrlCreateButton("OK", 10, $window_heigth - 35, 60, 20, $BS_DEFPUSHBUTTON)
  1321. #region Perforated Image
  1322. ; Perforated Image
  1323. # ==> Start
  1324. Local $_Left_pos, $_Top_pos, $_GUI_NAME
  1325. $_Left_pos = $window_width - 145; 338 ; Replace with correct position
  1326. $_Top_pos = $window_heigth - 20; 521 ; Replace with correct position
  1327. _GuiImageHole($hGUI_View_SQL, $_Left_pos, $_Top_pos, 136, 41)
  1328. # <== End
  1329. #endregion Perforated Image
  1331. GUISetState()
  1332. Do
  1333. $msg = GUIGetMsg()
  1334. Select
  1335. Case $msg = $buttonOK Or $msg = -3
  1336. GUIDelete($hGUI_View_SQL)
  1337. Return
  1339. EndSelect
  1340. Until $msg = $GUI_EVENT_CLOSE
  1341. EndFunc ;==>View_SQL
  1342. #FUNCTION# ==============================================================
  1343. #region Perforated Image
  1344. #comments-start
  1345. The lines below will generate the perforated image (bewteen start and end)
  1346. Move these lines into your GUI code, usually just before GUISetState()
  1347. Don't forget to fill in the correct coordinates for $Left_pos, $Top_pos
  1348. and enter the GUI Window Handle in the last line
  1350. # ==> Start
  1351. Local $_Left_pos, $_Top_pos, $_GUI_NAME
  1352. $_Left_pos = 10 ; Replace with correct position
  1353. $_Top_pos = 10 ; Replace with correct position
  1354. $_GUI_NAME = 'The name of your GUI window'
  1355. _GuiImageHole($_GUI_NAME, $_Left_pos, $_Top_pos, 136, 45)
  1356. # <== End
  1358. #comments-end
  1360. #FUNCTION# ==============================================================
  1361. ; #FUNCTION# ====================================================================================================
  1362. ; Name...........: _GuiImageHole
  1363. ; Description....: Create a perforated image in a GUI Window
  1364. ; Syntax.........: _GuiImageHole($window_handle, $pos_x, $pos_y,$Image_Width ,$Image_Height)
  1365. ; Parameters ....: $window_handle - Window Handle of the GU to be perforated
  1366. ; $pos_x - Upper left position of the perforation
  1367. ; $pos_y - Upper Top position of the perforation
  1368. ; $Image_Width - Image width
  1369. ; $Image_Height - Image heigth
  1370. ; Return values .: Success - Nothing returned
  1371. ; Failure - Function does not fail
  1372. ; Authors........: GreenCan
  1373. ; Modified ......:
  1374. ; Remarks .......:
  1375. ; Related .......:
  1376. ; Link ..........:
  1377. ; Example .......: Yes
  1378. ; ===============================================================================================================
  1379. Func _GuiImageHole($window_handle, $pos_x, $pos_y, $Image_Width, $Image_Height)
  1380. Local $aClassList, $aM_Mask, $aMask
  1381. #region picture array
  1382. Local $aPicture[262] = [ _
  1383. '3,1,48,1', '50,1,137,1', '3,2,48,2', '50,2,93,2', '94,2,137,2', '1,3,2,3', '4,3,47,3', '49,3,92,3', '95,3,137,3', '1,4,3,4', '5,4,25,4', '28,4,46,4', '48,4,92,4', '95,4,137,4', '1,5,3,5', '5,5,25,5', '29,5,45,5', '48,5,92,5', '95,5,137,5', '1,6,4,6', _
  1384. '6,6,24,6', '30,6,45,6', '47,6,92,6', '96,6,137,6', '1,7,5,7', '7,7,9,7', '10,7,23,7', '30,7,38,7', '42,7,44,7', '46,7,93,7', '96,7,137,7', '1,8,6,8', '11,8,24,8', '29,8,37,8', '45,8,66,8', '68,8,93,8', '96,8,137,8', '1,9,6,9', '13,9,24,9', '30,9,36,9', _
  1385. '44,9,66,9', '69,9,87,9', '91,9,93,9', '97,9,137,9', '1,10,6,10', '13,10,23,10', '31,10,37,10', '43,10,65,10', '71,10,86,10', '92,10,93,10', '97,10,137,10', '1,11,6,11', '12,11,22,11', '32,11,35,11', '42,11,65,11', '71,11,86,11', '92,11,93,11', '97,11,137,11', '1,12,6,12', '15,12,22,12', _
  1386. '32,12,34,12', '43,12,50,12', '54,12,65,12', '70,12,86,12', '97,12,137,12', '1,13,5,13', '18,13,21,13', '32,13,33,13', '44,13,49,13', '55,13,65,13', '72,13,86,13', '96,13,137,13', '1,14,5,14', '15,14,16,14', '44,14,48,14', '55,14,64,14', '73,14,86,14', '96,14,137,14', '1,15,4,15', '15,15,20,15', _
  1387. '45,15,48,15', '54,15,64,15', '74,15,85,15', '96,15,137,15', '1,16,4,16', '17,16,19,16', '46,16,48,16', '55,16,63,16', '75,16,84,16', '96,16,137,16', '1,17,5,17', '17,17,18,17', '46,17,47,17', '56,17,63,17', '76,17,84,17', '97,17,137,17', '1,18,5,18', '17,18,18,18', '46,18,47,18', '56,18,63,18', _
  1388. '77,18,84,18', '97,18,137,18', '1,19,6,19', '16,19,18,19', '33,19,34,19', '57,19,63,19', '77,19,84,19', '98,19,137,19', '1,20,6,20', '16,20,17,20', '32,20,34,20', '58,20,63,20', '77,20,84,20', '98,20,137,20', '1,21,6,21', '16,21,17,21', '32,21,34,21', '58,21,63,21', '78,21,83,21', '98,21,137,21', _
  1389. '1,22,6,22', '32,22,34,22', '58,22,63,22', '78,22,83,22', '98,22,137,22', '1,23,6,23', '57,23,64,23', '78,23,82,23', '98,23,137,23', '1,24,6,24', '57,24,65,24', '78,24,82,24', '98,24,137,24', '1,25,6,25', '58,25,65,25', '77,25,82,25', '99,25,137,25', '1,26,6,26', '58,26,64,26', '76,26,83,26', _
  1390. '99,26,137,26', '1,27,7,27', '32,27,33,27', '46,27,47,27', '58,27,64,27', '77,27,83,27', '100,27,137,27', '1,28,7,28', '31,28,33,28', '46,28,47,28', '58,28,65,28', '77,28,83,28', '99,28,137,28', '2,29,7,29', '31,29,33,29', '59,29,65,29', '78,29,84,29', '98,29,137,29', '7,30,8,30', '16,30,18,30', _
  1391. '31,30,33,30', '46,30,47,30', '59,30,65,30', '79,30,84,30', '96,30,137,30', '8,31,10,31', '15,31,18,31', '25,31,26,31', '31,31,34,31', '45,31,47,31', '60,31,62,31', '79,31,85,31', '95,31,137,31', '3,32,6,32', '8,32,10,32', '15,32,21,32', '25,32,26,32', '33,32,34,32', '42,32,48,32', '60,32,62,32', _
  1392. '78,32,85,32', '95,32,137,32', '34,33,35,33', '39,33,40,33', '42,33,49,33', '60,33,62,33', '76,33,86,33', '95,33,120,33', '125,33,130,33', '135,33,137,33', '38,34,39,34', '44,34,51,34', '76,34,88,34', '95,34,122,34', '125,34,129,34', '131,34,134,34', '135,34,137,34', '47,35,51,35', '55,35,60,35', '64,35,65,35', _
  1393. '76,35,88,35', '95,35,121,35', '123,35,124,35', '126,35,128,35', '130,35,134,35', '135,35,137,35', '49,36,51,36', '56,36,62,36', '65,36,66,36', '69,36,70,36', '76,36,81,36', '83,36,88,36', '95,36,120,36', '122,36,124,36', '126,36,127,36', '129,36,137,36', '56,37,58,37', '66,37,70,37', '76,37,78,37', '84,37,88,37', _
  1394. '95,37,120,37', '122,37,124,37', '126,37,127,37', '129,37,131,37', '135,37,137,37', '67,38,71,38', '77,38,79,38', '84,38,89,38', '95,38,119,38', '126,38,127,38', '129,38,132,38', '134,38,137,38', '32,39,33,39', '85,39,89,39', '95,39,118,39', '120,39,124,39', '126,39,127,39', '129,39,132,39', '134,39,137,39', '18,40,24,40', _
  1395. '26,40,41,40', '86,40,89,40', '95,40,117,40', '121,40,123,40', '127,40,128,40', '134,40,137,40', '14,41,46,41', '85,41,89,41', '95,41,137,41', '9,42,52,42', '88,42,89,42', '97,42,137,42', '7,43,55,43', '57,43,59,43', '62,43,65,43', '67,43,69,43', '70,43,73,43', '100,43,137,43', '6,44,79,44', '100,44,137,44', _
  1396. '4,45,81,45', '100,45,137,45']
  1397. #endregion picture array
  1398. ; get the size of the active window
  1399. Local $size = WinGetClientSize($window_handle)
  1400. Local $window_width = $size[0]
  1401. Local $Window_height = $size[1] + 33 ; including the title bar
  1402. ; First hide the window
  1403. $aClassList = StringSplit(_WinGetClassListEx($window_handle), @LF)
  1404. $aM_Mask = DllCall('gdi32.dll', 'long', 'CreateRectRgn', 'long', 0, 'long', 0, 'long', 0, 'long', 0)
  1405. ; rectangle A - left side
  1406. $aMask = DllCall('gdi32.dll', 'long', 'CreateRectRgn', 'long', 0, 'long', 0, 'long', $pos_x, 'long', $Window_height)
  1407. DllCall('gdi32.dll', 'long', 'CombineRgn', 'long', $aM_Mask[0], 'long', $aMask[0], 'long', $aM_Mask[0], 'int', 2)
  1408. ; rectangle B - Top
  1409. $aMask = DllCall('gdi32.dll', 'long', 'CreateRectRgn', 'long', 0, 'long', 0, 'long', $window_width, 'long', $pos_y)
  1410. DllCall('gdi32.dll', 'long', 'CombineRgn', 'long', $aM_Mask[0], 'long', $aMask[0], 'long', $aM_Mask[0], 'int', 2)
  1411. ; rectangle C - Right side
  1412. $aMask = DllCall('gdi32.dll', 'long', 'CreateRectRgn', 'long', $pos_x + $Image_Width, 'long', 0, 'long', $window_width + 30, 'long', $Window_height)
  1413. DllCall('gdi32.dll', 'long', 'CombineRgn', 'long', $aM_Mask[0], 'long', $aMask[0], 'long', $aM_Mask[0], 'int', 2)
  1414. ; rectangle D - Bottom
  1415. $aMask = DllCall('gdi32.dll', 'long', 'CreateRectRgn', 'long', 0, 'long', $pos_y + $Image_Height, 'long', $window_width, 'long', $Window_height)
  1416. DllCall('gdi32.dll', 'long', 'CombineRgn', 'long', $aM_Mask[0], 'long', $aMask[0], 'long', $aM_Mask[0], 'int', 2)
  1417. ; now unhide all regions as defined in array $aPicture
  1418. For $i_I = 0 To (UBound($aPicture) - 1)
  1419. Local $Block_value = StringSplit($aPicture[$i_I], ',')
  1420. $aMask = DllCall('gdi32.dll', 'long', 'CreateRectRgn', 'long', $pos_x + $Block_value[1] - 1, 'long', $pos_y + $Block_value[2], 'long', $pos_x + $Block_value[3], 'long', $pos_y + $Block_value[4] - 1)
  1421. DllCall('gdi32.dll', 'long', 'CombineRgn', 'long', $aM_Mask[0], 'long', $aMask[0], 'long', $aM_Mask[0], 'int', 2)
  1422. Next
  1423. DllCall('user32.dll', 'long', 'SetWindowRgn', 'hwnd', $window_handle, 'long', $aM_Mask[0], 'int', 1)
  1424. $aPicture = '' ; empty array
  1425. EndFunc ;==>_GuiImageHole
  1426. #FUNCTION# ==============================================================
  1427. Func _WinGetClassListEx($sTitle)
  1428. Local $sClassList = WinGetClassList($sTitle)
  1429. Local $aClassList = StringSplit($sClassList, @LF)
  1430. Local $sRetClassList = '', $sHold_List = '|'
  1431. Local $aiInHold, $iInHold
  1432. For $i_I = 1 To UBound($aClassList) - 1
  1433. If $aClassList[$i_I] = '' Then ContinueLoop
  1434. If StringRegExp($sHold_List, '\|' & $aClassList[$i_I] & '~(\d+)\|') Then
  1435. $aiInHold = StringRegExp($sHold_List, '.*\|' & $aClassList[$i_I] & '~(\d+)\|.*', 1)
  1436. $iInHold = Number($aiInHold[UBound($aiInHold) - 1])
  1437. If $iInHold = 0 Then $iInHold += 1
  1438. $aClassList[$i_I] &= '~' & $iInHold + 1
  1439. $sHold_List &= $aClassList[$i_I] & '|'
  1440. $sRetClassList &= $aClassList[$i_I] & @LF
  1441. Else
  1442. $aClassList[$i_I] &= '~1'
  1443. $sHold_List &= $aClassList[$i_I] & '|'
  1444. $sRetClassList &= $aClassList[$i_I] & @LF
  1445. EndIf
  1446. Next
  1447. Return StringReplace(StringStripWS($sRetClassList, 3), '~', '')
  1448. EndFunc ;==>_WinGetClassListEx
  1449. #endregion Perforated Image
  1450. #FUNCTION# ==============================================================
  1451. ; #FUNCTION# ====================================================================================================================
  1452. ; Name...........: _ArrayDisplay2
  1453. ; Description ...: Displays given 1D or 2D array in a listview.
  1454. ; Syntax.........: _ArrayDisplay(Const ByRef $avArray[, $sTitle = "Array: ListView Display", [$iColumnTitle = 0][, $iItemLimit = -1[, $iTranspose = 0[, $sDelimiter = ""[, $sReplace = "|"]]]]])
  1455. ; Parameters ....: $avArray - Array to display
  1456. ; $sTitle - [optional] Title to use for window
  1457. ; $iColumnTitles - [optional]
  1458. ; 0 = Default 'Col x' (same as _ArrayDisplay)
  1459. ; 1 = Display Column Titles defined as Row 0 in $avArray
  1460. ; 2 = Display Column Titles, don't show Row numbers (not valid for $iTranspose <> 0)
  1461. ; $iItemLimit - [optional] Maximum number of listview items (rows) to show
  1462. ; $iTranspose - [optional] If set dIfferently than default, will transpose the array If 2D
  1463. ; $sDelimiter - [optional] Change Opt("GUIDataSeparatorChar") on-the-fly
  1464. ; $sReplace - [optional] String to replace any occurrence of $sDelimiter with in each array element
  1465. ; Return values .: Success - 1
  1466. ; Failure - 0, sets @error:
  1467. ; |1 - $avArray is not an array
  1468. ; |2 - $avArray has too many Dimensions (only up to 2D supported)
  1469. ; Author ........: randallc, Ultima
  1470. ; ModIfied.......: Gary Frost (gafrost) / Ultima: modIfied to be self-contained (no longer depends on "GUIListView.au3")
  1471. ; Greencan added optional Column Title / Hide Column numbers, correct positioning of ListView GUI on screen
  1472. ; Remarks .......:
  1473. ; Related .......:
  1474. ; Link ..........;
  1475. ; Example .......; Yes
  1476. ; ===============================================================================================================================
  1477. Func _ArrayDisplay2(Const ByRef $avArray, $sTitle = "Array: ListView Display", $iColumnTitle = 0, $iItemLimit = -1, $iTranspose = 0, $sDelimiter = "", $sReplace = "|")
  1478. If Not IsArray($avArray) Then Return SetError(1, 0, 0)
  1480. ; Dimension checking
  1481. Local $iDimension = UBound($avArray, 0), $iUBound = UBound($avArray, 1) - 1, $iSubMax = UBound($avArray, 2) - 1
  1482. If $iDimension > 2 Then Return SetError(2, 0, 0)
  1483. Local $size
  1485. ; Delimiter handling
  1486. If $sDelimiter = "" Then $sDelimiter = Chr(124)
  1488. ; Declare variables
  1489. Local $i_I, $i_J, $vTmp, $aItem, $avArrayText, $sHeader = "Row", $iBuffer = 64
  1490. Local $iColLimit = 250, $iLVIAddUDFThreshold = 4000;, $VertView_Width = 640, $VertView_Height = 480
  1491. Local $iOnEventMode = Opt("GUIOnEventMode", 0), $sDataDelimiterChar = Opt("GUIDataSeparatorChar", $sDelimiter)
  1493. ; Swap Dimensions If transposing
  1494. If $iSubMax < 0 Then $iSubMax = 0
  1495. If $iTranspose Then
  1496. $vTmp = $iUBound
  1497. $iUBound = $iSubMax
  1498. $iSubMax = $vTmp
  1499. EndIf
  1501. ; Set limits for Dimensions
  1502. If $iSubMax > $iColLimit Then $iSubMax = $iColLimit
  1503. If $iItemLimit = 1 Then $iItemLimit = $iLVIAddUDFThreshold
  1504. If $iItemLimit < 1 Then $iItemLimit = $iUBound
  1505. If $iUBound > $iItemLimit Then $iUBound = $iItemLimit
  1506. If $iLVIAddUDFThreshold > $iUBound Then $iLVIAddUDFThreshold = $iUBound
  1508. ; Column Titles
  1509. If $iColumnTitle = 0 Then
  1510. ; Set header up
  1511. For $i_I = 0 To $iSubMax
  1512. $sHeader &= $sDelimiter & "Col " & $i_I
  1513. Next
  1514. Local $StartRow = 0
  1515. Local $StartCol = 0
  1516. Else
  1517. ; no first column with row number
  1518. If $iColumnTitle = 2 Then $sHeader = ""
  1519. ; Set header with array row 0
  1520. For $i_I = 0 To $iSubMax
  1521. Local $StartCol
  1522. If $iTranspose Then
  1523. $sHeader = "Title"
  1524. For $i_I = 1 To $iSubMax
  1525. $sHeader &= $sDelimiter & "Col " & $i_I
  1526. Next
  1527. $StartCol = 1
  1528. Else
  1529. If $iDimension = 1 Then
  1530. $sHeader &= $sDelimiter & $avArray[$i_I]
  1531. Else
  1532. $sHeader &= $sDelimiter & $avArray[0][$i_I]
  1533. EndIf
  1534. $StartCol = 0
  1535. EndIf
  1536. Next
  1537. Local $StartRow = 1
  1538. If StringLeft($sHeader, 1) = $sDelimiter Then $sHeader = StringTrimLeft($sHeader, 1)
  1539. EndIf
  1541. ; Convert array into text for listview
  1542. Local $avArrayText[$iUBound + 1]
  1543. For $i_I = $StartRow To $iUBound + $StartCol
  1544. If $iColumnTitle <> 0 And $iTranspose Then
  1545. If $iDimension = 1 Then
  1546. $avArrayText[$i_I - $StartRow] = "[" & $avArray[$i_I - $StartRow] & "]"
  1547. Else
  1548. $avArrayText[$i_I - $StartRow] = "[" & $avArray[0][$i_I - $StartRow] & "]"
  1549. EndIf
  1550. Else
  1551. If $iColumnTitle < 2 Then
  1552. $avArrayText[$i_I - $StartRow] = "[" & $i_I & "]"
  1553. EndIf
  1554. EndIf
  1555. For $i_J = $StartCol To $iSubMax
  1556. ; Get current item
  1557. If $iDimension = 1 Then
  1558. If $iTranspose Then
  1559. $vTmp = $avArray[$i_J]
  1560. Else
  1561. $vTmp = $avArray[$i_I]
  1562. EndIf
  1563. Else
  1564. If $iTranspose Then
  1565. If $iColumnTitle <> 0 Then
  1566. $vTmp = $avArray[$i_J][$i_I - $StartRow]
  1567. Else
  1568. $vTmp = $avArray[$i_J][$i_I]
  1569. EndIf
  1570. Else
  1571. $vTmp = $avArray[$i_I][$i_J]
  1572. EndIf
  1573. EndIf
  1575. ; Add to text array
  1576. $vTmp = StringReplace($vTmp, $sDelimiter, $sReplace, 0, 1)
  1577. $avArrayText[$i_I - $StartRow] &= $sDelimiter & $vTmp
  1579. ; Set max buffer size
  1580. $vTmp = StringLen($vTmp)
  1581. If $vTmp > $iBuffer Then $iBuffer = $vTmp
  1582. Next
  1583. If StringLeft($avArrayText[$i_I - $StartRow], 1) = $sDelimiter Then $avArrayText[$i_I - $StartRow] = StringTrimLeft($avArrayText[$i_I - $StartRow], 1)
  1584. Next
  1585. $iBuffer += 1
  1587. ; GUI Constants
  1588. Local Const $_ARRAYCONSTANT_GUI_DOCKBORDERS = 0x66
  1589. Local Const $_ARRAYCONSTANT_GUI_DOCKBOTTOM = 0x40
  1590. Local Const $_ARRAYCONSTANT_GUI_DOCKHEIGHT = 0x0200
  1591. Local Const $_ARRAYCONSTANT_GUI_DOCKLEFT = 0x2
  1592. Local Const $_ARRAYCONSTANT_GUI_DOCKRIGHT = 0x4
  1593. Local Const $_ARRAYCONSTANT_GUI_EVENT_CLOSE = -3
  1594. Local Const $_ARRAYCONSTANT_LVIf_PARAM = 0x4
  1595. Local Const $_ARRAYCONSTANT_LVIf_TEXT = 0x1
  1596. Local Const $_ARRAYCONSTANT_LVM_GETCOLUMNWIDTH = (0x1000 + 29)
  1597. Local Const $_ARRAYCONSTANT_LVM_GETITEMCOUNT = (0x1000 + 4)
  1598. Local Const $_ARRAYCONSTANT_LVM_GETITEMSTATE = (0x1000 + 44)
  1599. Local Const $_ARRAYCONSTANT_LVM_INSERTITEMA = (0x1000 + 7)
  1600. Local Const $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE = (0x1000 + 54)
  1601. Local Const $_ARRAYCONSTANT_LVM_SETITEMA = (0x1000 + 6)
  1602. Local Const $_ARRAYCONSTANT_LVS_EX_FULLROWSELECT = 0x20
  1603. Local Const $_ARRAYCONSTANT_LVS_EX_GRIDLINES = 0x1
  1604. Local Const $_ARRAYCONSTANT_LVS_SHOWSELALWAYS = 0x8
  1605. Local Const $_ARRAYCONSTANT_WS_EX_CLIENTEDGE = 0x0200
  1606. Local Const $_ARRAYCONSTANT_WS_MAXIMIZEBOX = 0x00010000
  1607. Local Const $_ARRAYCONSTANT_WS_MINIMIZEBOX = 0x00020000
  1608. Local Const $_ARRAYCONSTANT_WS_SIZEBOX = 0x00040000
  1609. Local Const $_ARRAYCONSTANT_tagLVITEM = "int Mask;int Item;int SubItem;int State;int StateMask;ptr Text;int TextMax;int Image;int Param;int Indent;int GroupID;int Columns;ptr pColumns"
  1611. Local $iAddMask = BitOR($_ARRAYCONSTANT_LVIf_TEXT, $_ARRAYCONSTANT_LVIf_PARAM)
  1612. Local $tBuffer = DllStructCreate("char Text[" & $iBuffer & "]"), $pBuffer = DllStructGetPtr($tBuffer)
  1613. Local $tItem = DllStructCreate($_ARRAYCONSTANT_tagLVITEM), $pItem = DllStructGetPtr($tItem)
  1614. DllStructSetData($tItem, "Param", 0)
  1615. DllStructSetData($tItem, "Text", $pBuffer)
  1616. DllStructSetData($tItem, "TextMax", $iBuffer)
  1617. ; Set interface up
  1618. Local $window_open = WinList("Vertical View") ; check If window already exists
  1619. ;Local $GUI_VerticalView
  1620. ;~ ConsoleWrite ( @ScriptLineNumber & " $VertView_Width:" & $VertView_Width & " $VertView_Height:" & $VertView_Height & " $VertView_Left:" & $VertView_Left & " $VertView_Top:" & $VertView_Top & @CR)
  1622. If $window_open[0][0] > 0 Then
  1623. ;~ $bVerticalView_Resize = False
  1624. $bVerticalView_GUI = False
  1625. $size = WinGetPos($GUI_VerticalView)
  1626. $VertView_Left = $size[0]
  1627. $VertView_Top = $size[1]
  1628. $size = WinGetClientSize($GUI_VerticalView)
  1629. $VertView_Width = $size[0] + $VertView_OffsetWidth
  1630. $VertView_Height = $size[1] + $VertView_OffsetHeight
  1631. GUIDelete($GUI_VerticalView)
  1632. $GUI_VerticalView = GUICreate($sTitle, $VertView_Width, $VertView_Height, $VertView_Left, $VertView_Top, BitOR($_ARRAYCONSTANT_WS_SIZEBOX, $_ARRAYCONSTANT_WS_MINIMIZEBOX, $_ARRAYCONSTANT_WS_MAXIMIZEBOX), $WS_EX_TOPMOST, $SQLResultWindow)
  1633. Else
  1634. $GUI_VerticalView = GUICreate($sTitle, $VertView_Width, $VertView_Height, $VertView_Left, $VertView_Top, BitOR($_ARRAYCONSTANT_WS_SIZEBOX, $_ARRAYCONSTANT_WS_MINIMIZEBOX, $_ARRAYCONSTANT_WS_MAXIMIZEBOX), $WS_EX_TOPMOST, $SQLResultWindow)
  1635. $bVerticalView_GUI = True
  1636. If $VertView_OffsetWidth = 0 Then
  1637. ; remember the difference between the result of WinGetClientSize and the initial Width and Height of the GUI
  1638. $size = WinGetClientSize($GUI_VerticalView)
  1639. $VertView_OffsetWidth = $VertView_Width - $size[0]
  1640. $VertView_OffsetHeight = $VertView_Height - $size[1]
  1641. EndIf
  1642. EndIf
  1643. ;~ ConsoleWrite ( @ScriptLineNumber & " $VertView_Width:" & $VertView_Width & " $VertView_Height:" & $VertView_Height & " $VertView_Left:" & $VertView_Left & " $VertView_Top:" & $VertView_Top & @CR)
  1645. Local $aiGUISize = WinGetClientSize("Vertical View")
  1646. Local $VertView_hListView = GUICtrlCreateListView($sHeader, 0, 0, $aiGUISize[0], $aiGUISize[1] - 26, $_ARRAYCONSTANT_LVS_SHOWSELALWAYS)
  1647. $VerticalView_Copy = GUICtrlCreateButton("Copy to Clipboard", 3, $aiGUISize[1] - 23, $aiGUISize[0] - 6, 20)
  1648. GUICtrlSetResizing($VertView_hListView, $_ARRAYCONSTANT_GUI_DOCKBORDERS)
  1649. GUICtrlSetResizing($VerticalView_Copy, $_ARRAYCONSTANT_GUI_DOCKLEFT + $_ARRAYCONSTANT_GUI_DOCKRIGHT + $_ARRAYCONSTANT_GUI_DOCKBOTTOM + $_ARRAYCONSTANT_GUI_DOCKHEIGHT)
  1650. GUICtrlSendMsg($VertView_hListView, $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE, $_ARRAYCONSTANT_LVS_EX_GRIDLINES, $_ARRAYCONSTANT_LVS_EX_GRIDLINES)
  1651. GUICtrlSendMsg($VertView_hListView, $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE, $_ARRAYCONSTANT_LVS_EX_FULLROWSELECT, $_ARRAYCONSTANT_LVS_EX_FULLROWSELECT)
  1652. GUICtrlSendMsg($VertView_hListView, $_ARRAYCONSTANT_LVM_SETEXTENDEDLISTVIEWSTYLE, $_ARRAYCONSTANT_WS_EX_CLIENTEDGE, $_ARRAYCONSTANT_WS_EX_CLIENTEDGE)
  1653. GUISetIcon("DATABASE.ICO", 0)
  1654. ; Fill listview
  1655. For $i_I = 0 To $iLVIAddUDFThreshold
  1656. GUICtrlCreateListViewItem($avArrayText[$i_I], $VertView_hListView)
  1657. Next
  1659. For $i_I = ($iLVIAddUDFThreshold + 1) To $iUBound
  1660. $aItem = StringSplit($avArrayText[$i_I], $sDelimiter)
  1661. DllStructSetData($tBuffer, "Text", $aItem[1])
  1663. ; Add listview item
  1664. DllStructSetData($tItem, "Item", $i_I)
  1665. DllStructSetData($tItem, "SubItem", 0)
  1666. DllStructSetData($tItem, "Mask", $iAddMask)
  1667. GUICtrlSendMsg($VertView_hListView, $_ARRAYCONSTANT_LVM_INSERTITEMA, 0, $pItem)
  1669. ; Set listview subitem text
  1670. DllStructSetData($tItem, "Mask", $_ARRAYCONSTANT_LVIf_TEXT)
  1671. For $i_J = 2 To $aItem[0]
  1672. DllStructSetData($tBuffer, "Text", $aItem[$i_J])
  1673. DllStructSetData($tItem, "SubItem", $i_J - 1)
  1674. GUICtrlSendMsg($VertView_hListView, $_ARRAYCONSTANT_LVM_SETITEMA, 0, $pItem)
  1675. Next
  1676. Next
  1677. ; calibrate columns
  1678. ;~ $VertView_Width = 0
  1679. For $i_I = 0 To $iSubMax + 1
  1680. GUICtrlSendMsg($VertView_hListView, 0x1000 + 30, $i_I, -2) ; added this to calibrate column width correctly
  1682. ;~ If $bVerticalView_Resize Then $VertView_Width += GUICtrlSendMsg($VertView_hListView, $_ARRAYCONSTANT_LVM_GETCOLUMNWIDTH, $i_I, 0)
  1683. Next
  1684. ;~ If $bVerticalView_Resize Then
  1685. ;~ ; postion of main window at startup
  1686. ;~ ;Local $VirtualDesktopWidth = DLLCall("user32.dll", "int", "GetSystemMetrics", "int", 78)
  1687. ;~ Local $iCentre = (@DesktopWidth - $VertView_Width - 20) / 2
  1689. ;~ If $VertView_Width + 20 > $VirtualDesktopWidth Then ; If Listview larger than $VirtualDesktopWidth limit to the visible size
  1690. ;~ $VertView_Width = $VirtualDesktopWidth - 20
  1691. ;~ $iCentre = ($VirtualDesktopWidth - $VertView_Width - 20) / 2
  1692. ;~ ElseIf $VertView_Width + 20 <= @DesktopWidth Then ; If Listview smaller than @DesktopWidth center on first screen
  1693. ;~ $iCentre = (@DesktopWidth - $VertView_Width - 20) / 2
  1694. ;~ ElseIf $VertView_Width < 250 Then ; Minimal size of Listview is 250
  1695. ;~ $VertView_Width = 250
  1696. ;~ $iCentre = (@DesktopWidth - $VertView_Width - 20) / 2
  1697. ;~ EndIf
  1698. ;~ $VertView_Left = $VirtualDesktopWidth - $VertView_Width - 20
  1699. ;~ WinMove($GUI_VerticalView, "", $VertView_Left, Default, $VertView_Width + 20)
  1700. ;~ EndIf
  1701. ;~ ConsoleWrite ( @ScriptLineNumber & " $VertView_Width:" & $VertView_Width & " $VertView_Height:" & $VertView_Height & " $VertView_Left:" & $VertView_Left & " $VertView_Top:" & $VertView_Top & @CR)
  1703. ; Show dialog
  1704. GUISetState(@SW_SHOW, $GUI_VerticalView)
  1706. Opt("GUIOnEventMode", $iOnEventMode)
  1707. Opt("GUIDataSeparatorChar", $sDataDelimiterChar)
  1709. Return 1
  1710. EndFunc ;==>_ArrayDisplay2
  1711. #FUNCTION# ==============================================================
  1712. Func _COMError()
  1714. Local $bHexNumber = Hex($oAppError.number, 8)
  1715. Local $sError = "COM Error Encountered in " & @ScriptName & @CRLF & _
  1716. "@AutoItVersion = " & @AutoItVersion & @CRLF & _
  1717. "@AutoItX64 = " & @AutoItX64 & @CRLF & _
  1718. "@Compiled = " & @Compiled & @CRLF & _
  1719. "@OSArch = " & @OSArch & @CRLF & _
  1720. "@OSVersion = " & @OSVersion & @CRLF & _
  1721. "Scriptline = " & $oAppError.scriptline & @CRLF & _
  1722. "NumberHex = " & $bHexNumber & @CRLF & _
  1723. "Number = " & $oAppError.number & @CRLF & _
  1724. "WinDescription = " & StringStripWS($oAppError.WinDescription, 2) & @CRLF & _
  1725. "Description = " & StringStripWS($oAppError.description, 2) & @CRLF & _
  1726. "Source = " & $oAppError.Source & @CRLF & _
  1727. "HelpFile = " & $oAppError.HelpFile & @CRLF & _
  1728. "HelpContext = " & $oAppError.HelpContext & @CRLF & _
  1729. "LastDllError = " & $oAppError.LastDllError
  1731. If $iDebug > 0 Then
  1732. If $iDebug = 1 Then ConsoleWrite($sError & @CRLF & "========================================================" & @CRLF)
  1733. If $iDebug = 2 Then MsgBox(64, "SQLite Reports - Debug Info", $sError)
  1734. EndIf
  1735. Return SetError(999, $oAppError.number, 0)
  1737. EndFunc ;==>_COMError
  1738. #FUNCTION# ==============================================================
comments powered by Disqus