又來發表 autoit 了，此次因常用豬頭記帳來管理收支， 有時看記帳偶而會發現當初紀錄時錯誤的地方， 例如用現金支付，但是選擇使用信用卡支付，因而造成統計資料錯誤。 因此自己撰寫搜尋，找到那筆資料後再回頭使用豬頭記帳修改。 至於為什麼不直接編輯就好，就說了豬頭也能使用，不改太多功能， 避免越改越大，失去當初寫的美意。 好了以下是原始程式碼 下載網址 http://doora.qiniudn.com/R2uHF.au3 ------------------------------------------------------------- #NoTrayIcon #include <array.au3> #include <ButtonConstants.au3> #include <Color.au3> #include <ColorConstantS.au3> #include <ComboConstants.au3> #include <Date.au3> #include <DateTimeConstants.au3> #include <EditConstants.au3> #include <FileConstants.au3> #include <FontConstants.au3> #include <GUIConstantsEx.au3> #include <GUIEdit.au3> #include <GUIListBox.au3> #Include <GuiListView.au3> #include <GuiStatusBar.au3> #include <ListViewConstants.au3> #include <MsgBoxConstants.au3> #include <SQLite.au3> #include <SQLite.dll.au3> #include <StaticConstants.au3> #include <WinAPI.au3> #include <WinAPIEx.au3> #include <WindowsConstants.au3> Global $hQuery, $aRow, $aRow2, $a Global $GUI_ListBox Global $GUI_Combo1, $GUI_Input1, $GUI_Input2, $GUI_Combo2, $GUI_Combo3, $GUI_Combo4, $GUI_Button4 Global $USER_ID, $subjetc_ID Global $iExListViewStyle = BitOR($LVS_EX_FULLROWSELECT, $LVS_EX_GRIDLINES, $LVS_EX_DOUBLEBUFFER);項目選中顯示高亮度題示，顯示項目橫列線條，緩衝控鍵顯示 _SQLite_Startup ("sqlite3.dll", TRUE, 0) ;加載SQLite3.dll,工作目錄下需有 SQLite3.dll 如果沒有就從autoit 網站下載並放入同一目錄下 If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite錯誤", "SQLite3.dll不能載入!") Exit -1 EndIf $GUI_Form = GUICreate("豬頭記帳搜尋", 400, 480, -1, -1) ;建立主視窗GUI樣式 GUISetBkColor(0xECE9C8) ; 設定視窗背景顏色 $GUI_ListBox = GUICtrlCreateListView(" 日 期 | 支出/收入 | 花 費 | 備註 ", 2, 167, 397, 310, BitOR($LVS_REPORT, $WS_BORDER), BitOR($LVS_EX_CHECKBOXES, $LVS_EX_FULLROWSELECT)) ;上面設定ListView內容 _GUICtrlListView_SetExtendedListViewStyle($GUI_ListBox,$iExListViewStyle) ;將 視窗設定風格顯示 $GUI_ListBox = GUICtrlGetHandle($GUI_ListBox) ;回傳控件項目句柄,針對項目雙擊後 選擇其它功能 ; 以下視窗顯示 $GUI_Button1 = GUICtrlCreateButton("讀取", 2, 2, 48, 22, 0) GUICtrlCreateLabel("使用者帳號：",60, 8, 72, 15) $GUI_Combo1 = GUICtrlCreateCombo("", 132, 2, 90, 50,$CBS_DROPDOWNLIST);顯示使 用者名稱用 GUICtrlCreateLabel("搜尋日期：", 8, 35, 72, 15) $GUI_Input1 = GUICtrlCreateInput("",68,32,70,20,$ES_READONLY) ;搜尋起始日期 $GUI_Button2 = GUICtrlCreateButton("日期起點", 140, 30, 60, 22, 0) $GUI_Input2 = GUICtrlCreateInput("",210,32,70,20,$ES_READONLY) ;搜尋最後日期 $GUI_Button3 = GUICtrlCreateButton("日期終點", 283, 30, 60, 22, 0) GUICtrlCreateLabel("支出/收入：", 8, 65, 75, 15) $GUI_Combo2 = GUICtrlCreateCombo("", 73, 60, 90, 50,$CBS_DROPDOWNLIST);顯示收 支來源 GUICtrlSetData($GUI_Combo2, "現金支出|信用卡支出|收入", "") GUICtrlCreateLabel("類別：", 8, 94, 38, 15) $GUI_Combo3 = GUICtrlCreateCombo("", 47, 90, 90, 50,$CBS_DROPDOWNLIST);顯示類 別 GUICtrlCreateLabel("子項目：", 145, 94, 50, 15) $GUI_Combo4 = GUICtrlCreateCombo("", 195, 90, 90, 50,$CBS_DROPDOWNLIST);顯示子 項目 $GUI_Button4 = GUICtrlCreateButton("搜尋", 50,130, 60, 22, 0) GUISetState(@SW_SHOW,$GUI_Form) ;GUI 顯示 While 1 Switch GUIGetMsg() Case $GUI_EVENT_CLOSE ;視窗關閉 _SQLite_Close(-1) _SQLite_Shutdown () ;本程式關閉時shutdown sqlite.dll ExitLoop Case $GUI_Button1 Local $SQLite_Data_Path = FileOpenDialog("選擇檔案", @ScriptDir & "\", "(*.sqlite)", $FD_FILEMUSTEXIST + $FD_MULTISELECT) If Not FileExists($SQLite_Data_Path) Then ;如果無資料庫檔案則執行以下命令 MsgBox(0,"錯誤","資料庫無法打開，請檢查檔案是否存在或是檔案已損毀。") Exit EndIf _SQLite_Open ($SQLite_Data_Path) ;讀取資料庫，名稱為$SQLite_Data_Path 變數 名稱 If @error Then MsgBox($MB_SYSTEMMODAL, "資料庫無法載入", "載入失敗!") Exit EndIf SQLiteRead() Case $GUI_Button2 ;搜尋起始日期 Local $BMDATETEMP = GUICreate("日期起點", 200, 50, -1, -1) Local $idDate = GUICtrlCreateDate("", 10, 10, 185, 20, $DTS_SHORTDATEFORMAT) ; 選擇特定的默認格式 Local $sStyle = "yyyy-MM-dd" GUICtrlSendMsg($idDate, $DTM_SETFORMATW, 0, $sStyle) GUISetState(@SW_SHOW) ; 循環到用戶退出. While 1 SWITCH GUIGetMsg() Case $GUI_EVENT_CLOSE GUICtrlSetData($GUI_Input1, GUICtrlRead($idDate)) ExitLoop EndSwitch WEnd GUIDelete($BMDATETEMP) Case $GUI_Button3 ;搜尋最後日期 Local $BMDATETEMP = GUICreate("日期終點", 200, 50, -1, -1) Local $idDate = GUICtrlCreateDate("", 10, 10, 185, 20, $DTS_SHORTDATEFORMAT) ; 選擇特定的默認格式 Local $sStyle = "yyyy-MM-dd" GUICtrlSendMsg($idDate, $DTM_SETFORMATW, 0, $sStyle) GUISetState(@SW_SHOW) ; 循環到用戶退出. While 1 SWITCH GUIGetMsg() Case $GUI_EVENT_CLOSE GUICtrlSetData($GUI_Input2, GUICtrlRead($idDate)) ExitLoop EndSwitch WEnd GUIDelete($BMDATETEMP) Case $GUI_Combo1 ;顯示使用者名稱 user_data() Case $GUI_Combo3 ;顯示類別 class_data() Case $GUI_Button4 ;搜尋 forage() EndSwitch WEnd Func forage() ; 開始搜尋 GUICtrlSetState($GUI_Button4, $GUI_DISABLE) ;將搜尋鈕禁制避免重複搜尋 If GUICtrlRead ($GUI_Combo1) = "" Then ;如果未選擇帳號 MsgBox(0,"操作錯誤","尚未選擇帳號，請重新點選讀取檔案") ElseIf GUICtrlRead ($GUI_Combo2) = "" Then ;如果未選擇支出及收入類別 MsgBox(0,"操作錯誤","尚未選擇支出及收入選項，請重新點選支出及收入選項") ElseIf GUICtrlRead ($GUI_Combo3) = "" Or GUICtrlRead ($GUI_Combo4) = "" Then ;如果未選擇類別及子項目 MsgBox(0,"操作錯誤","尚未選擇類別及子項目，請重新點選類別及子項目") ElseIf GUICtrlRead ($GUI_Input1) <> "" AND GUICtrlRead($GUI_Input2) <> "" Then ;輸入起訖日期蒐尋 _GUICtrlListView_DeleteAllItems ($GUI_ListBox) ;清除舊資料顯示 _SQLite_QuerySingleRow(-1, "SELECT uno FROM user WHERE uid = '" & GUICtrlRead ($GUI_Combo1) & "' ;", $aRow ) ;查詢user_uno值,使用者uno值 Local $uno = $aRow [0] ;MsgBox(0,"$uno","使用者$uno值" & $uno ) _SQLite_QuerySingleRow(-1, "SELECT cno FROM class WHERE name = '" & GUICtrlRead ($GUI_Combo3) & "' AND uno = '" & $uno & "' ;", $aRow ) ;查詢 class_cno值,使用者類別值 Local $c_cno = $aRow [0] ;MsgBox(0,"$c_cno","類別$c_cno值" & $c_cno ) _SQLite_QuerySingleRow(-1, "SELECT sno FROM subject WHERE name = '" & GUICtrlRead ($GUI_Combo4) & "' AND uno = '" & $uno & "' AND cno = '" & $c_cno & "' ;", $aRow ) ;查詢subject_sno值,使用者子項目值 Local $s_sno = $aRow [0] ;MsgBox(0,"$s_sno","子項目$s_sno值" & $s_sno ) _SQLite_QuerySingleRow(-1, "SELECT count(),sum(spend) FROM money WHERE datetime(date) >= '" & GUICtrlRead ($GUI_Input1) & "' AND datetime(date) <= '" & GUICtrlRead ($GUI_Input2) & "' AND uno ='" & $uno & "' AND cno = '" & $c_cno & "' AND sno = '" & $s_sno & "' AND mode = '" & GUICtrlRead($GUI_Combo2) & "' ;", $aRow) ;查詢資料,使用者及類別和子項目符合幾 筆 $aRow2 = $aRow[0] IF $aRow2 = 0 Then Local $totalmoney = 0 Else Local $totalmoney = $aRow[1] EndIf _SQLite_Query(-1, "SELECT * FROM money WHERE datetime(date) >= '" & GUICtrlRead ($GUI_Input1) & "' AND datetime(date) <= '" & GUICtrlRead ($GUI_Input2) & "' AND uno ='" & $uno & "' AND cno = '" & $c_cno & "' AND sno = '" & $s_sno & "' AND mode = '" & GUICtrlRead($GUI_Combo2) & "' ORDER BY mno DESC ;", $hQuery) ;查詢資料，並存入$hQuery 變數 sql_data_array() MsgBox(0,"筆數","總共" & $aRow2 & "筆" & @CRLF & "合計：" & $totalmoney & " 元") ElseIf GUICtrlRead ($GUI_Input1) <> "" Then _GUICtrlListView_DeleteAllItems ($GUI_ListBox) ;清除舊資料顯示 _SQLite_QuerySingleRow(-1, "SELECT uno FROM user WHERE uid = '" & GUICtrlRead ($GUI_Combo1) & "' ;", $aRow ) ;查詢user_uno值,使用者uno值 Local $uno = $aRow [0] ;MsgBox(0,"$uno","使用者$uno值" & $uno ) _SQLite_QuerySingleRow(-1, "SELECT cno FROM class WHERE name = '" & GUICtrlRead ($GUI_Combo3) & "' AND uno = '" & $uno & "' ;", $aRow ) ;查詢 class_cno值,使用者類別值 Local $c_cno = $aRow [0] ;MsgBox(0,"$c_cno","類別$c_cno值" & $c_cno ) _SQLite_QuerySingleRow(-1, "SELECT sno FROM subject WHERE name = '" & GUICtrlRead ($GUI_Combo4) & "' AND uno = '" & $uno & "' AND cno = '" & $c_cno & "' ;", $aRow ) ;查詢subject_sno值,使用者子項目值 Local $s_sno = $aRow [0] ;MsgBox(0,"$s_sno","子項目$s_sno值" & $s_sno ) _SQLite_QuerySingleRow(-1, "SELECT count(),sum(spend) FROM money WHERE datetime(date) >= '" & GUICtrlRead ($GUI_Input1) & "' AND uno ='" & $uno & "' AND cno = '" & $c_cno & "' AND sno = '" & $s_sno & "' AND mode = '" & GUICtrlRead($GUI_Combo2) & "' ;", $aRow) ;查詢資料,使用者及類別和子項目符合幾 筆 $aRow2 = $aRow[0] IF $aRow2 = 0 Then Local $totalmoney = 0 Else Local $totalmoney = $aRow[1] EndIf _SQLite_Query(-1, "SELECT * FROM money WHERE datetime(date) >= '" & GUICtrlRead ($GUI_Input1) & "' AND uno ='" & $uno & "' AND cno = '" & $c_cno & "' AND sno = '" & $s_sno & "' AND mode = '" & GUICtrlRead($GUI_Combo2) & "' ORDER BY mno DESC ;", $hQuery) ;查詢資料，並存入$hQuery 變數 sql_data_array() MsgBox(0,"筆數","總共" & $aRow2 & "筆" & @CRLF & "合計：" & $totalmoney & " 元") Else _GUICtrlListView_DeleteAllItems ($GUI_ListBox) ;清除舊資料顯示 _SQLite_QuerySingleRow(-1, "SELECT uno FROM user WHERE uid = '" & GUICtrlRead ($GUI_Combo1) & "' ;", $aRow ) ;查詢user_uno值,使用者uno值 Local $uno = $aRow [0] ;MsgBox(0,"$uno","使用者$uno值" & $uno ) _SQLite_QuerySingleRow(-1, "SELECT cno FROM class WHERE name = '" & GUICtrlRead ($GUI_Combo3) & "' AND uno = '" & $uno & "' ;", $aRow ) ;查詢 class_cno值,使用者類別值 Local $c_cno = $aRow [0] ;MsgBox(0,"$c_cno","類別$c_cno值" & $c_cno ) _SQLite_QuerySingleRow(-1, "SELECT sno FROM subject WHERE name = '" & GUICtrlRead ($GUI_Combo4) & "' AND uno = '" & $uno & "' AND cno = '" & $c_cno & "' ;", $aRow ) ;查詢subject_sno值,使用者子項目值 Local $s_sno = $aRow [0] ;MsgBox(0,"$s_sno","子項目$s_sno值" & $s_sno ) _SQLite_QuerySingleRow(-1, "SELECT count(),sum(spend) FROM money WHERE uno ='" & $uno & "' AND cno = '" & $c_cno & "' AND sno = '" & $s_sno & "' AND mode = '" & GUICtrlRead($GUI_Combo2) & "' ;", $aRow) ;查詢資料,使用者及類別和 子項目符合幾筆 $aRow2 = $aRow[0] IF $aRow2 = 0 Then Local $totalmoney = 0 Else Local $totalmoney = $aRow[1] EndIf _SQLite_Query(-1, "SELECT * FROM money WHERE uno ='" & $uno & "' AND cno = '" & $c_cno & "' AND sno = '" & $s_sno & "' AND mode = '" & GUICtrlRead($GUI_Combo2) & "' ORDER BY mno DESC ;", $hQuery) ;查詢資料，並存入 $hQuery 變數 sql_data_array() MsgBox(0,"筆數","總共" & $aRow2 & "筆" & @CRLF & "合計：" & $totalmoney & " 元") EndIf GUICtrlSetState($GUI_Button4, $GUI_ENABLE) EndFunc Func sql_data_array() ;取出之資料存入陣列 Local $ArrayData[$aRow2][4] ;設定二維陣列 Local $c = 0 ;初始二維陣列值 While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK $ArrayData[$c][0]=$aRow[5] $ArrayData[$c][1]=$aRow[7] $ArrayData[$c][2]=$aRow[4] $ArrayData[$c][3]=$aRow[6] $c = $c +1 WEnd _GUICtrlListView_AddArray($GUI_ListBox, $ArrayData) _SQLite_QueryFinalize($hQuery) EndFunc Func class_data() ;讀取完類別後顯示子項目 _SQLite_Query(-1, "SELECT * FROM user WHERE uid = '" & GUICtrlRead ($GUI_Combo1) & "' COLLATE NOCASE ;", $hQuery) ;查詢user_uno While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK Local $uno = $aRow[0] WEnd _SQLite_Query(-1, "SELECT * FROM class WHERE name = '" & GUICtrlRead ($GUI_Combo3) & "' AND uno = '" & $uno & "' COLLATE NOCASE ;", $hQuery) ;查詢 class_cno While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK Local $c_cno = $aRow[0] WEnd _SQLite_Query(-1, "SELECT COUNT(cno) FROM subject WHERE cno = '" & $c_cno & "' AND uno = '" & $uno & "' ;",$hQuery) ;讀取設定類別 While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK ;將_SQLite_Query 查回的 $hQuery 變數存入$aRow陣列資料內 $a = Int($aRow[0]) ;取出資料，目前為資料筆數 WEnd Local $ArrayData[$a][5] ;設定二維陣列 Local $c = 0 ;初始二維陣列值 _SQLite_Query(-1, "SELECT * FROM subject WHERE cno = '" & $c_cno & "' AND uno = '" & $uno & "' COLLATE NOCASE ;",$hQuery) ;讀取全部資料筆數，並存入 $hQuery 變數 While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK ;將_SQLite_Query 查回的 $hQuery 變數存入$aRow陣列資料內 $ArrayData[$c][0]=$aRow[0] $ArrayData[$c][1]=$aRow[1] $ArrayData[$c][2]=$aRow[2] $ArrayData[$c][3]=$aRow[3] $ArrayData[$c][4]=$aRow[4] $c = $c +1 WEnd _SQLite_QueryFinalize($hQuery) ;結束SQLITE 查詢 Local $subject_1 = $ArrayData[0][3] Local $d =1 While $d < $a $subject_1 = $subject_1 &"|" & $ArrayData[$d][3] $d = $d + 1 WEnd GUICtrlDelete($GUI_Combo4) Sleep(100) $GUI_Combo4 = GUICtrlCreateCombo("", 195, 90, 90, 50,$CBS_DROPDOWNLIST);顯示 子項目 GUICtrlSetData($GUI_Combo4, $subject_1, "") EndFunc Func user_data() ;讀取使用者資料,讀取完後顯示類別 _SQLite_Query(-1, "SELECT * FROM user WHERE uid = '" & GUICtrlRead ($GUI_Combo1) & "' COLLATE NOCASE ;", $hQuery) ;查詢資料筆數，並存入$hQuery 變 數- 先取得使用者資料 While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK ;取得筆數資料 Local $uno = $aRow[0] Local $uid = $aRow[1] Local $passwd = $aRow[2] Local $needPwd = $aRow[3] WEnd _SQLite_Query(-1, "SELECT COUNT(uno) FROM class WHERE uno = '" & $uno & "' ;",$hQuery) ;讀取全部資料筆數，並存入$hQuery 變數- 設定類別 While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK ;將_SQLite_Query 查回的 $hQuery 變數存入$aRow陣列資料內 $a = Int($aRow[0]) ;取出資料，目前為資料筆數 WEnd Local $ArrayData[$a][4] ;設定二維陣列 Local $c = 0 ;初始二維陣列值 _SQLite_Query(-1, "SELECT * FROM class WHERE uno = '" & $uno & "' COLLATE NOCASE ;",$hQuery) ;讀取全部資料筆數，並存入$hQuery 變數 While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK ;將_SQLite_Query 查回的 $hQuery 變數存入$aRow陣列資料內 $ArrayData[$c][0]=$aRow[0] $ArrayData[$c][1]=$aRow[1] $ArrayData[$c][2]=$aRow[2] $ArrayData[$c][3]=$aRow[3] $c = $c +1 WEnd _SQLite_QueryFinalize($hQuery) ;結束SQLITE 查詢 Local $class_1 = $ArrayData[0][2] Local $d =1 While $d < $a $class_1 = $class_1 &"|" & $ArrayData[$d][2] $d = $d + 1 WEnd GUICtrlDelete($GUI_Combo3) Sleep(100) $GUI_Combo3 = GUICtrlCreateCombo("", 47, 90, 90, 50,$CBS_DROPDOWNLIST);顯示類 別 GUICtrlSetData($GUI_Combo3, $class_1, "") EndFunc Func SQLiteRead() ;讀取使用者 _SQLite_Query(-1, "SELECT COUNT(uno) FROM user;",$hQuery) ;讀取全部資料筆數， 並存入$hQuery 變數 While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK ;將_SQLite_Query 查回的 $hQuery 變數存入$aRow陣列資料內 $a = Int($aRow[0]) ;取出資料，目前為資料筆數 WEnd Local $ArrayData[$a][4] ;設定二維陣列 Local $c = 0 ;初始二維陣列值 _SQLite_Query(-1, "SELECT * FROM user;",$hQuery) ;讀取全部資料筆數，並存入 $hQuery 變數 While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK ;將_SQLite_Query 查回的 $hQuery 變數存入$aRow陣列資料內 $ArrayData[$c][0]=$aRow[0] $ArrayData[$c][1]=$aRow[1] $ArrayData[$c][2]=$aRow[2] $ArrayData[$c][3]=$aRow[3] $c = $c +1 WEnd _SQLite_QueryFinalize($hQuery) ;結束SQLITE 查詢 Local $userbunch = $ArrayData[0][1] Local $d =1 While $d < $a $userbunch = $userbunch &"|" & $ArrayData[$d][1] $d = $d + 1 WEnd GUICtrlDelete($GUI_Combo1) Sleep(100) $GUI_Combo1 = GUICtrlCreateCombo("", 132, 2, 90, 50,$CBS_DROPDOWNLIST ) GUICtrlSetData($GUI_Combo1, $userbunch, "") EndFunc --

※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 36.236.21.243 ※ 文章網址: https://webptt.com/m.aspx?n=bbs/EzHotKey/M.1500906711.A.AF9.html