猪头记帐搜寻(豬頭記帳)-『作品展览』-AUTOIT CN

yohoboy 发表于 2018-8-22 11:06:00

猪头记帐搜寻(豬頭記帳)

豬頭記帳用來個人記帳使用，本程式用以輔助快速搜尋類別子項目等並顯示出來。
建議事項，1. 有使用(豬頭記帳)軟體的人
            2. 想快速搜尋類別及子項目的需求。

軟體版本:autoit 3.3.14.5sciTE 編碼: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
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] ;設定二維陣列
_SQLite_Query(-1, $Sqlstr & " ORDER BY date DESC ;", $hQuery) ;查詢資料，並存入$hQuery 變數
Local $c = 0 , $ArrayDataTemp;初始二維陣列值
While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK
$ArrayDataTemp = $aRow
$ArrayData[$c] = $aRow
$ArrayData[$c] = $aRow
$ArrayDataTemp = $aRow
$ArrayDataTemp = $aRow
$ArrayData[$c] = $aRow
$ArrayData[$c] = $aRow
_SQLite_QuerySingleRow(-1, "SELECT name FROM class WHERE uno = '" & $ArrayDataTemp & "' AND cno = '" & $ArrayDataTemp & "' ;", $aRow) ;
$ArrayData[$c] = $aRow
_SQLite_QuerySingleRow(-1, "SELECT name FROM subject WHERE uno = '" & $ArrayDataTemp & "' AND cno = '" & $ArrayDataTemp & "' AND sno = '" & $ArrayDataTemp & "';", $aRow) ;
$ArrayData[$c] = $aRow
$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] ,$c = 0 , $ArrayDataTemp;初始二維陣列值
_SQLite_Query(-1, $Sqlstr & " ORDER BY date DESC limit " & Int(GUICtrlRead($GUI_Input1)) & ";", $hQuery) ;查詢資料，並存入$hQuery 變數
While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK
$ArrayDataTemp = $aRow
$ArrayData[$c] = $aRow
$ArrayData[$c] = $aRow
$ArrayDataTemp = $aRow
$ArrayDataTemp = $aRow
$ArrayData[$c] = $aRow
$ArrayData[$c] = $aRow
_SQLite_QuerySingleRow(-1, "SELECT name FROM class WHERE uno = '" & $ArrayDataTemp & "' AND cno = '" & $ArrayDataTemp & "' ;", $aRow) ;
$ArrayData[$c] = $aRow
_SQLite_QuerySingleRow(-1, "SELECT name FROM subject WHERE uno = '" & $ArrayDataTemp & "' AND cno = '" & $ArrayDataTemp & "' AND sno = '" & $ArrayDataTemp & "';", $aRow) ;
$ArrayData[$c] = $aRow
$c = $c + 1
WEnd
$ArrayData[$c] = "資料未完......"
_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
_SQLite_QuerySingleRow(-1, "SELECT COUNT(cno) FROM class WHERE uno = '" & $uno & "' ;",$aRow) ;讀取全部類別資料筆數
Local $ArrayData)]
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
$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)];設定一維陣列
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
$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
_SQLite_QuerySingleRow(-1, "SELECT COUNT(sno) FROM subject WHERE uno = '" & $uno & "' AND cno = '" & $cno & "' COLLATE NOCASE ;",$aRow) ;
Local $ArrayData)];
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
$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
EndFunc

xzf680 发表于 2018-8-25 11:43:42

顶一下，沙发坐一下

cfanpc 发表于 2018-8-26 13:37:12

谢谢分享，收藏学习下

页: [1]

AUTOIT CN's Archiver

猪头记帐搜寻(豬頭記帳)