豬頭記帳 用來個人記帳使用，本程式用以輔助快速搜尋 類別子項目等並顯示出來。 建議事項，1. 有使用(豬頭記帳)軟體的人 2. 想快速搜尋類別及子項目的需求。 軟體版本:autoit 3.3.14.5 sciTE 編碼:65001 ,字型:繁體字型。 ---- 以下為完整程式碼------- #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 <GuiComboBox.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 <StaticConstants.au3> #include <WinAPI.au3> #include <WinAPIEx.au3> #include <WindowsConstants.au3> Global $hQuery, $aRow, $aRow2, $a Global $GUI_ListView Global $GUI_Combo1, $GUI_Input1, $GUI_Combo2, $GUI_Combo3, & _ $GUI_Combo4, $GUI_Button2 Global $USER_ID, $subjetc_ID Global $iExListViewStyle = BitOR($LVS_EX_FULLROWSELECT, & _ $LVS_EX_GRIDLINES, $LVS_EX_DOUBLEBUFFER) ;項目選中顯示高亮度題示，顯示項目橫列線條，緩衝控鍵顯示 Global $uno, $uid, $cno, $sno _SQLite_Startup ("sqlite3.dll", TRUE, 0) ;加載SQLite3.dll,工作目錄下需有SQLite3.dll If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite錯誤", "SQLite3.dll不能載入!") Exit -1 EndIf $GUI_Form = GUICreate("豬頭記帳搜尋", 480, 480, -1, -1) ;建立主視窗GUI樣式 GUISetBkColor(0xECE9C8) ; 設定視窗背景顏色 ; 以下視窗顯示 $GUI_Button1 = GUICtrlCreateButton("讀取", 2, 4, 48, 22, 0) GUICtrlCreateLabel("使用者帳號：",60, 10, 72, 17) $GUI_Combo1 = GUICtrlCreateCombo("", 132, 4, 110, 50, & _ $CBS_DROPDOWNLIST);顯示使用者名稱用 GUICtrlCreateLabel("類別：", 8, 40, 38, 15) $GUI_Combo2 = GUICtrlCreateCombo("", 46, 35, 110, 50, & _ $CBS_DROPDOWNLIST);顯示類別 GUICtrlCreateLabel("子項目：", 175, 40, 50, 15) $GUI_Combo3 = GUICtrlCreateCombo("", 225, 35, 110, 50, & _ $CBS_DROPDOWNLIST);顯示子項目 $Checkbox1 = GUICtrlCreateCheckbox("按搜尋日期：", 30, 65, 90, 17) $Date1 = GUICtrlCreateDate("", 120, 63, 80, 20,$DTS_SHORTDATEFORMAT) Local $sStyle = "yyyy-MM-dd" GUICtrlSendMsg($Date1, $DTM_SETFORMATW, 0, $sStyle) GUICtrlCreateLabel("到", 208, 68, 16, 17) $Date2 = GUICtrlCreateDate("", 230, 63, 80, 20,$DTS_SHORTDATEFORMAT) GUICtrlSendMsg($Date2, $DTM_SETFORMATW, 0, $sStyle) GUICtrlCreateLabel("支出/收入：", 8, 95, 75, 15) $GUI_Combo4 = GUICtrlCreateCombo("", 75, 90, 125, 50,$CBS_DROPDOWNLIST) ;顯示收支來源 GUICtrlSetData($GUI_Combo4, "||現金及信用卡支出|現金支出|信用卡支出|收入", "") $GUI_Button2 = GUICtrlCreateButton("搜尋", 370,25, 60, 60, 0) GUICtrlSetFont(-1, 16, 400, 0) GUICtrlCreateLabel("最大顯示數量(0為全部)：", 8, 135, 135, 15) $GUI_Input1 = GUICtrlCreateInput("100",145,131,40,17,$ES_NUMBER) $GUI_ListView = GUICtrlCreateListView("", 0, 160, 480, 320, BitOR($LVS_REPORT, $WS_BORDER), BitOR($LVS_EX_CHECKBOXES, & _ $LVS_EX_FULLROWSELECT)) _GUICtrlListView_SetExtendedListViewStyle($GUI_ListView,$iExListViewStyle) ;將視窗設定風格顯示 ;_GUICtrlListView_SetExtendedListViewStyle($GUI_ListView, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT, $LVS_EX_CHECKBOXES)) _GUICtrlListView_AddColumn ($GUI_ListView,"日期",80) _GUICtrlListView_AddColumn ($GUI_ListView,"支出/收入",75) _GUICtrlListView_AddColumn ($GUI_ListView,"類別",75) _GUICtrlListView_AddColumn ($GUI_ListView,"子項目",75) _GUICtrlListView_AddColumn ($GUI_ListView,"花費",50) _GUICtrlListView_AddColumn ($GUI_ListView,"備註",120) $GUI_ListView = GUICtrlGetHandle($GUI_ListView) ;回傳控件項目句柄,針對項目雙擊後選擇其它功能 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 ;開啟資料庫，搜尋使用者帳號及顯示在 $GUI_Combo1 User_Read() Case $GUI_Combo1 ;選擇使用者編號 user_uno() Case $GUI_Combo2 ;選擇類別編號 user_cno() Case $GUI_Combo3 ;顯示次項目 user_sno() Case $GUI_Button2 ;搜尋 forage() EndSwitch WEnd Func forage() ; 開始搜尋 GUICtrlSetState($GUI_Button2, $GUI_DISABLE) ;將搜尋鈕禁制避免重複搜尋 If GUICtrlRead ($GUI_Combo1) = "" Then ;如果未選擇帳號 MsgBox(0,"操作錯誤","尚未選擇帳號，請重新點選讀取檔案") Else _GUICtrlListView_DeleteAllItems ($GUI_ListView) ;清除舊資料顯示 Local $Sqlstr="Select Count(mno) From money where" & _ " mno Like '%' AND uno = '" & $uno & "'" Local $index=_GUICtrlComboBox_GetCurSel($GUI_Combo2) - 1 If $index >= 0 Then $Sqlstr &= "AND uno ='" & $uno & "' AND cno ='" & $cno & "'" ;加入使用者編號及類別編號 Local $index=_GUICtrlComboBox_GetCurSel($GUI_Combo3) - 1 If $index >= 0 Then $Sqlstr &= "AND sno ='" & $sno & "'" ;加入次項目編號 If BitAND(GUICtrlRead($Checkbox1),$GUI_CHECKED) Then ;加入日期 $Sqlstr &= " AND datetime(date) >= datetime('" & GUICtrlRead($Date1) & _ "') AND datetime(date) <= datetime('" & GUICtrlRead($Date2) & "')" EndIf Local $index=_GUICtrlComboBox_GetCurSel($GUI_Combo4) - 1 If $index >= 0 Then If GUICtrlRead ($GUI_Combo4) = "現金及信用卡支出" Then $Sqlstr &= " AND mode ='現金支出' Or mode ='信用卡支出'" ;加入支付方式 ElseIf GUICtrlRead ($GUI_Combo4) = "現金支出" Then $Sqlstr &= " AND mode ='現金支出'" ; ElseIf GUICtrlRead ($GUI_Combo4) = "信用卡支出" Then $Sqlstr &= " AND mode ='信用卡支出'" ; ElseIf GUICtrlRead ($GUI_Combo4) = "收入" Then $Sqlstr &= " AND mode ='收入'" ; EndIf EndIf _SQLite_QuerySingleRow(-1, $Sqlstr & ";" , $aRow) ;取出筆數 $aRow2 = $aRow[0] If $aRow2 <> 0 Then If Int(GUICtrlRead($GUI_Input1)) = 0 or $aRow2 <= Int(GUICtrlRead($GUI_Input1)) Then Local $Sqlstr="Select * From money where mno Like '%' " Local $index=_GUICtrlComboBox_GetCurSel($GUI_Combo2) - 1 If $index >= 0 Then $Sqlstr &= "AND uno ='" & $uno & "' AND cno ='" & $cno & "'" ;加入使用者編號及類別編號 Local $index=_GUICtrlComboBox_GetCurSel($GUI_Combo3) - 1 If $index >= 0 Then $Sqlstr &= "AND sno ='" & $sno & "'" ;加入次項目編號 If BitAND(GUICtrlRead($Checkbox1),$GUI_CHECKED) Then ;加入日期 $Sqlstr &= " AND datetime(date) >= datetime('" & GUICtrlRead ($Date1) & _ "') AND datetime(date) <= datetime('" & GUICtrlRead ($Date2) & "')" EndIf Local $index=_GUICtrlComboBox_GetCurSel($GUI_Combo4) - 1 If $index >= 0 Then If GUICtrlRead ($GUI_Combo4) = "現金及信用卡支出" Then $Sqlstr &= " AND mode ='現金支出' Or mode ='信用卡支出'" ;加入支付方式 ElseIf GUICtrlRead ($GUI_Combo4) = "現金支出" Then $Sqlstr &= " AND mode ='現金支出'" ; ElseIf GUICtrlRead ($GUI_Combo4) = "信用卡支出" Then $Sqlstr &= " AND mode ='信用卡支出'" ; ElseIf GUICtrlRead ($GUI_Combo4) = "收入" Then $Sqlstr &= " AND mode ='收入'" ; EndIf EndIf Local $ArrayData[$aRow2][8] ;設定二維陣列 _SQLite_Query(-1, $Sqlstr & " ORDER BY date DESC ;", $hQuery) ;查詢資料， 並存入$hQuery 變數 Local $c = 0 , $ArrayDataTemp[3];初始二維陣列值 While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK $ArrayDataTemp[0] = $aRow[1] $ArrayData[$c][0] = $aRow[5] $ArrayData[$c][1] = $aRow[7] $ArrayDataTemp[1] = $aRow[2] $ArrayDataTemp[2] = $aRow[3] $ArrayData[$c][4] = $aRow[4] $ArrayData[$c][5] = $aRow[6] _SQLite_QuerySingleRow(-1, "SELECT name FROM class WHERE uno = '" & $ArrayDataTemp[0] & "' AND cno = '" & $ArrayDataTemp[1] & "' ;", $aRow) ; $ArrayData[$c][2] = $aRow[0] _SQLite_QuerySingleRow(-1, "SELECT name FROM subject WHERE uno = '" & $ArrayDataTemp[0] & "' AND cno = '" & $ArrayDataTemp[1] & "' AND sno = '" & $ArrayDataTemp[2] & "';", $aRow) ; $ArrayData[$c][3] = $aRow[0] $c = $c + 1 WEnd _GUICtrlListView_AddArray($GUI_ListView, $ArrayData) _SQLite_QueryFinalize($hQuery) Else Local $Sqlstr="Select * From money where mno Like '%' " Local $index=_GUICtrlComboBox_GetCurSel($GUI_Combo2) - 1 If $index >= 0 Then $Sqlstr &= "AND uno ='" & $uno & "' AND cno ='" & $cno & "'" ;加入使用者編號及類別編號 Local $index=_GUICtrlComboBox_GetCurSel($GUI_Combo3) - 1 If $index >= 0 Then $Sqlstr &= "AND sno ='" & $sno & "'" ;加入次項目編號 If BitAND(GUICtrlRead($Checkbox1),$GUI_CHECKED) Then ;加入日期 $Sqlstr &= " AND datetime(date) >= datetime('" & GUICtrlRead ($Date1) & _ "') AND datetime(date) <= datetime('" & GUICtrlRead ($Date2) & "')" EndIf Local $index=_GUICtrlComboBox_GetCurSel($GUI_Combo4) - 1 If $index >= 0 Then If GUICtrlRead ($GUI_Combo4) = "現金及信用卡支出" Then $Sqlstr &= " AND mode ='現金支出' Or mode ='信用卡支出'" ;加入支付方式 ElseIf GUICtrlRead ($GUI_Combo4) = "現金支出" Then $Sqlstr &= " AND mode ='現金支出'" ; ElseIf GUICtrlRead ($GUI_Combo4) = "信用卡支出" Then $Sqlstr &= " AND mode ='信用卡支出'" ; ElseIf GUICtrlRead ($GUI_Combo4) = "收入" Then $Sqlstr &= " AND mode ='收入'" ; EndIf EndIf $aRow2 = Int(GUICtrlRead($GUI_Input1)) + 1 Local $ArrayData[$aRow2][8] ,$c = 0 , $ArrayDataTemp[3];初始二維陣列值 _SQLite_Query(-1, $Sqlstr & " ORDER BY date DESC limit " & Int(GUICtrlRead($GUI_Input1)) & ";", $hQuery) ;查詢資料，並存入$hQuery 變數 While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK $ArrayDataTemp[0] = $aRow[1] $ArrayData[$c][0] = $aRow[5] $ArrayData[$c][1] = $aRow[7] $ArrayDataTemp[1] = $aRow[2] $ArrayDataTemp[2] = $aRow[3] $ArrayData[$c][4] = $aRow[4] $ArrayData[$c][5] = $aRow[6] _SQLite_QuerySingleRow(-1, "SELECT name FROM class WHERE uno = '" & $ArrayDataTemp[0] & "' AND cno = '" & $ArrayDataTemp[1] & "' ;", $aRow) ; $ArrayData[$c][2] = $aRow[0] _SQLite_QuerySingleRow(-1, "SELECT name FROM subject WHERE uno = '" & $ArrayDataTemp[0] & "' AND cno = '" & $ArrayDataTemp[1] & "' AND sno = '" & $ArrayDataTemp[2] & "';", $aRow) ; $ArrayData[$c][3] = $aRow[0] $c = $c + 1 WEnd $ArrayData[$c][0] = "資料未完......" _GUICtrlListView_AddArray($GUI_ListView, $ArrayData) _SQLite_QueryFinalize($hQuery) EndIf Else MsgBox(0,"提示","目前無資料，請重新搜尋。") EndIf EndIf GUICtrlSetState($GUI_Button2, $GUI_ENABLE) EndFunc Func user_uno() ;選擇使用者編號 _SQLite_QuerySingleRow(-1, "SELECT uno FROM user WHERE uid = '" & GUICtrlRead ($GUI_Combo1) & "' COLLATE NOCASE ;", $aRow) ;查詢使用者編號 $uno = $aRow[0] _SQLite_QuerySingleRow(-1, "SELECT COUNT(cno) FROM class WHERE uno = '" & $uno & "' ;",$aRow) ;讀取全部類別資料筆數 Local $ArrayData[int($aRow[0])] Local $c = 0 ;初始一維陣列值 _SQLite_Query(-1, "SELECT name FROM class WHERE uno = '" & $uno & "' ;",$hQuery) ;讀取全部資料筆數，並存入$hQuery 變數 While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK ;將_SQLite_Query 查回的 $hQuery 變數存入$aRow陣列資料內 $ArrayData[$c]=$aRow[0] $c = $c +1 WEnd _SQLite_QueryFinalize($hQuery) ;結束SQLITE 查詢 Local $Str = "" For $i = 0 To UBound($ArrayData)-1 $Str &= "|" & $ArrayData[$i] Next _SQLite_QueryFinalize($hQuery) ;結束SQLITE 查詢 $Str = "|" & $Str GUICtrlSetData($GUI_Combo2, "") GUICtrlSetData($GUI_Combo2, $Str, "") GUICtrlSetData($GUI_Combo3, "") EndFunc Func User_Read() ;開啟資料庫，搜尋使用者帳號 Local $SQLite_Data_Path = FileOpenDialog("選擇檔案", @ScriptDir & "\", "(*.sqlite)", $FD_FILEMUSTEXIST + $FD_MULTISELECT) If Not FileExists($SQLite_Data_Path) Then ;如果無資料庫檔案則執行以下命令 MsgBox(0,"錯誤","資料庫無法打開，請檢查檔案是否存在或是檔案已損毀。") _SQLite_Close(-1) _SQLite_Shutdown () Exit EndIf _SQLite_Open ($SQLite_Data_Path) ;讀取資料庫，名稱為$SQLite_Data_Path 變數名 稱 If @error Then MsgBox($MB_SYSTEMMODAL, "資料庫無法載入", "載入失敗!" & @CRLF & "請重新載入 資料庫。") _SQLite_Close(-1) _SQLite_Shutdown () Exit EndIf _SQLite_QuerySingleRow(-1, "SELECT COUNT(uno) FROM user;",$aRow) ;統計使用者 數量 Local $ArrayData[Int($aRow[0])];設定一維陣列 Local $c = 0 ;初始二維陣列值 _SQLite_Query(-1, "SELECT uid FROM user;",$hQuery) ;讀取全部資料筆數，並存入 $hQuery 變數 While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK ;將_SQLite_Query 查回的 $hQuery 變數存入$aRow陣列資料內 $ArrayData[$c] = $aRow[0] $c = $c + 1 WEnd Local $Str = "" For $i = 0 To UBound($ArrayData)-1 $Str &= "|" & $ArrayData[$i] Next _SQLite_QueryFinalize($hQuery) ;結束SQLITE 查詢 GUICtrlSetData($GUI_Combo1,"") GUICtrlSetData($GUI_Combo1, $Str, "") EndFunc Func user_cno() ;讀取完類別後顯示類別 _SQLite_QuerySingleRow(-1, "SELECT cno FROM class WHERE uno = '" & $uno & "' AND name = '" & GUICtrlRead($GUI_Combo2) & "' COLLATE NOCASE ;", $aRow) ;取得 類別編號 $cno = $aRow[0] _SQLite_QuerySingleRow(-1, "SELECT COUNT(sno) FROM subject WHERE uno = '" & $uno & "' AND cno = '" & $cno & "' COLLATE NOCASE ;",$aRow) ; Local $ArrayData[Int($aRow[0])]; Local $c = 0 ;初始二維陣列值 _SQLite_Query(-1, "SELECT name FROM subject WHERE uno = '" & $uno & "' AND cno = '" & $cno & "' COLLATE NOCASE ;",$hQuery) ;讀取全部資料筆數，並存入 $hQuery 變數 While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK ;將_SQLite_Query 查回的 $hQuery 變數存入$aRow陣列資料內 $ArrayData[$c]=$aRow[0] $c = $c +1 WEnd _SQLite_QueryFinalize($hQuery) ;結束SQLITE 查詢 Local $Str = "" For $i = 0 To UBound($ArrayData)-1 $Str &= "|" & $ArrayData[$i] Next _SQLite_QueryFinalize($hQuery) ;結束SQLITE 查詢 $Str = "|" & $Str GUICtrlSetData($GUI_Combo3, "") GUICtrlSetData($GUI_Combo3, $Str, "") EndFunc Func user_sno() _SQLite_QuerySingleRow(-1, "SELECT sno FROM subject WHERE uno = '" & $uno & "' AND cno = '" & $cno & "' AND name = '" & GUICtrlRead($GUI_Combo3) & "' COLLATE NOCASE ;", $aRow) ;取得類別編號 $sno = $aRow[0] EndFunc --

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