作者yohoboy (小宝包是天使与恶魔)
看板EzHotKey
标题Re: [-AI-] 猪头记帐搜寻(再次改版)
时间Wed Aug 22 11:18:54 2018
猪头记帐 用来个人记帐使用,本程式用以辅助快速搜寻
类别子项目等并显示出来。
建议事项,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/cn.aspx?n=bbs/EzHotKey/M.1534907936.A.805.html