|
放這邊存檔,有需求的自行修改功能............
#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 <GuiButton.au3>
#include <GUIConstantsEx.au3>
#include <GUIEdit.au3>
#include <GUIListBox.au3>
#Include <GuiListView.au3>
#include <GuiScrollBars.au3>
#include <GuiStatusBar.au3>
#include <ListViewConstants.au3>
#include <MsgBoxConstants.au3>
#include <Process.au3>
#include <SliderConstants.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <StaticConstants.au3>
#include <StructureConstants.au3>
#include <UpDownConstants.au3>
#include <WinAPI.au3>
#include <WinAPIEx.au3>
#include <WindowsConstants.au3>
Opt("TrayIconHide", 1) ;0 = 顯示托盤圖標, 1 = 隱藏托盤圖標
Opt('MustDeclareVars', 1);1 = 變量不需預先聲明, 1 = 變量必需預先聲明
Opt("GUICloseOnESC", 0) ;1 = 點擊 Esc 鍵關閉, 0 = 點擊 Esc 鍵不關閉
Global $GUI_Button1, $GUI_Button2, $GUI_Button3, $GUI_Button4, $GUI_Combo1
Global $GUI_Input1, $GUI_Input2, $GUI_Input3, $GUI_DATE, $Checkbox1, $Checkbox2, $updata
Global $SQLite_Data_Path, $hQuery, $aRow, $aRow2
Global $idPic1, $Temp, $a, $Data1, $GUI_ListView
Global $g_hStatus
Global $iExListViewStyle = BitOR($LVS_EX_FULLROWSELECT, $LVS_EX_GRIDLINES, $LVS_EX_DOUBLEBUFFER);項目選中顯示高亮度題示,顯示項目橫列線條,緩衝控鍵顯示
Global $SYSTEMNAME = "ART(公共藝術)|CCTV(閉路電視)|CLOCK(子母鐘)|CV(結構腐蝕)|CWDM(粗波分割多工轉換)|DLTS(直線電話)|DPA(機廠廣播)|" & _
"DVRS(數位錄音)|EPABX(自動電話)|GE(寬頻網路)|IAS(門禁)|KVM(KVM)|LSPS(投射螢幕)|LU(邊坡滑動)|OSSS(地震警報)|PA(廣播)|" & _
"PIDS(旅客資訊)|PSS(通訊電源)|R22A-IRSY(防侵入)|RADIO(無線電)|SCADA(中央監控)|SDH(數位同步)|WN(風速儀)|共用設備|其他"
Global $SelectUnitName = "||T244|T2441|T2442|T2443|T2444|T2445"
$SQLite_Data_Path = "purchase.db" ;sql資料庫路徑及名稱
_SQLite_Startup ("sqlite3.dll", TRUE, 0) ;加載SQLite3.dll,工作目錄下需有SQLite3.dll 如果沒有就從autoit 網站下載並放入同一目錄下
If @error Then ;如果沒有SQLite3.dll檔案則顯示錯誤訊息
MsgBox($MB_SYSTEMMODAL, "SQLite 錯誤", "SQLite3.dll 不能載入,請檢查SQLite3.dll,SQLite3.def檔案是否存在同一目錄下!")
Exit ;離開程式不執行
EndIf
If Not FileExists($SQLite_Data_Path) Then ;如果無資料庫檔案則執行以下命令
SQLCreate() ;建立新資料庫,參考SQLCreate() 功能,在下面,第一次執行時須把註解取消,並註解下面msgbox
;MsgBox(0,"建立資料庫","資料庫已建立,請重新執行程式。")
Exit
EndIf
_SQLite_Open ($SQLite_Data_Path) ;讀取資料庫,名稱為$SQLite_Data_Path 變數名稱
Local $Title = "緊急小額請購物料管理查詢系統..." ;標題名稱
Local $GUI_Form = GUICreate($Title, 765, 650, -1, -1) ;建立主視窗GUI樣式
GUISetBkColor(0xECE9C8) ; 設定視窗背景顏色
$GUI_Button1 = GUICtrlCreateButton("搜尋", 100, 20, 48, 22, 0)
$GUI_Button2 = GUICtrlCreateButton("新增", 160, 20, 48, 22, 0)
$GUI_Button3 = GUICtrlCreateButton("刪除", 218, 20, 48, 22, 0)
$GUI_Button4 = GUICtrlCreateButton("更新", 430, 450, 48, 22, 0)
GUICtrlSetState($GUI_Button4,$GUI_DISABLE)
GUICtrlCreateLabel("請購單位:", 20, 55, 60, 20)
$GUI_Combo1 = GUICtrlCreateCombo("", 83, 50, 55, 20,$CBS_DROPDOWNLIST)
GUICtrlSetData($GUI_Combo1, $SelectUnitName, "" ) ;選擇單位
GUICtrlCreateLabel("請購單號:", 150, 55, 60, 20)
$GUI_Input1 = GUICtrlCreateInput("",213,50,90,20) ;輸入物料編號用
$Checkbox1 =GUICtrlCreateCheckbox("請購日期:", 315, 52, 70, 20)
$GUI_DATE = GUICtrlCreateDate("",392,50,95,20, $DTS_SHORTDATEFORMAT) ;顯示系統名稱
Local $sStyle = "yyyy-MM-dd"
GUICtrlSendMsg($GUI_DATE, $DTM_SETFORMATW, 0, $sStyle)
GUICtrlCreateLabel("請購品名:", 500, 55, 60, 15)
$GUI_Input2 = GUICtrlCreateInput("",560,50,140,20) ;顯示設備名稱
$Checkbox2 =GUICtrlCreateCheckbox("有現貨", 705, 50, 50, 20)
GUICtrlCreateLabel("照片:", 20, 415, 40, 15)
$idPic1 = GUICtrlCreatePic( @ScriptDir & "\PHOTO\fail.jpg", 60, 415, 280, 210) ;照片參考
;----修正內容用----
GUICtrlCreateLabel("剩餘量:", 380, 420, 50, 20)
$GUI_Input3 = GUICtrlCreateInput("",430,417,40,20,$ES_NUMBER) ;輸入剩餘數量用
;----End----
$GUI_ListView = GUICtrlCreateListView("項次|單位| 請 購 編 號 |請 購 日 期|請購量|" & _
" 使 用 系 統 | 品 名 | 用 途 |剩餘量|照片名稱", _
2, 80, 760, 310, BitOR($LVS_REPORT, $WS_BORDER))
$GUI_ListView = GUICtrlGetHandle($GUI_ListView)
_GUICtrlListView_SetColumnWidth($GUI_ListView, 0, 40)
_GUICtrlListView_SetColumnWidth($GUI_ListView, 4, 55)
_GUICtrlListView_SetColumnWidth($GUI_ListView, 6, 110)
_GUICtrlListView_SetColumnWidth($GUI_ListView, 8, 55)
GUIRegisterMsg($WM_NOTIFY, "WM_NOTIFY")
GUISetState(@SW_SHOW,$GUI_Form) ;GUI 顯示
While 1
Switch GUIGetMsg()
Case $GUI_Button1 ;搜尋資料庫功能鈕
SQLiteSelect()
Case $GUI_Button2 ;新增資料庫功能鈕
New_Data()
Case $GUI_Button3 ;刪除資料庫功能鈕
Local $sAnswer = InputBox("刪除資料","請輸入項次號碼","","",-1,-1)
If $sAnswer <> "" Then
_SQLite_Exec(-1, "DELETE FROM purchase WHERE Serial_Number = '" & $sAnswer & "';") ;刪除該筆工單資料
_GUICtrlListView_DeleteAllItems ($GUI_ListView) ;清除ListView資料
EndIf
Case $GUI_Button4 ;更新資料庫功能鈕
_SQLite_Exec(-1, "UPDATE purchase SET Remaining = '" & GUICtrlRead($GUI_Input3) & "' WHERE Serial_Number = '" & $updata & "';") ;修改各欄位資料
_GUICtrlListView_SetItemText($GUI_ListView, $a, GUICtrlRead($GUI_Input3), 8)
GUICtrlSetState($GUI_Button4,$GUI_DISABLE)
GUICtrlSetData( $GUI_Input3, "", "")
Case $GUI_EVENT_CLOSE ;視窗關閉
ExitLoop
EndSwitch
WEnd
Func New_Data() ;新增按鈕跳出之視窗-OK
LocaL $Form2 = GUICreate("物料號碼新增", 370, 300, -1, -1)
GUICtrlCreateLabel("請購單位:" , 10, 17, 60, 17)
Local $Combo1 = GUICtrlCreateCombo("", 70, 15, 60, 20,$CBS_DROPDOWNLIST)
GUICtrlSetData($Combo1, $SelectUnitName, "")
GUICtrlCreateLabel("請購編號:" , 10, 47, 60, 17)
Local $Input1 = GUICtrlCreateInput("", 70, 45, 110, 20)
GUICtrlCreateLabel("-", 190, 50, 17, 17)
Local $Input2 = GUICtrlCreateInput("", 208, 45, 40, 20,$ES_READONLY) ;序號,主KEY
GUICtrlCreateLabel("系統:", 35, 75, 40, 17)
Local $Combo2 = GUICtrlCreateCombo("", 70, 70, 180, 25,$CBS_DROPDOWNLIST)
GUICtrlSetData($Combo2, $SYSTEMNAME, "")
GUICtrlCreateLabel("請購品名:", 10, 100, 60, 17)
Local $Input3 = GUICtrlCreateInput("", 70, 95, 270, 20)
GUICtrlCreateLabel("用途:", 35, 123, 40, 17)
Local $Input4 = GUICtrlCreateInput("", 70, 120, 270, 20)
GUICtrlCreateLabel("請購日期:", 10, 145, 75, 15)
Local $GUI_DATE = GUICtrlCreateDate("", 70, 145, 95, 21,$DTS_SHORTDATEFORMAT)
Local $sStyle = "yyyy-MM-dd"
GUICtrlSendMsg($GUI_DATE, $DTM_SETFORMATW, 0, $sStyle)
GUICtrlCreateLabel("請購數量:", 10, 170, 60, 17)
Local $Input5 = GUICtrlCreateInput("", 70, 170, 40, 20,$ES_NUMBER)
GUICtrlCreateLabel("剩餘數量:", 10, 197, 60, 17)
Local $Input6 = GUICtrlCreateInput("", 70, 195, 40, 20,$ES_NUMBER)
GUICtrlCreateLabel("選擇圖片:", 10, 224, 60, 17)
Local $Input7 = GUICtrlCreateInput("", 70, 220, 120, 20)
Local $Button1 = GUICtrlCreateButton("確認", 180, 270, 65, 25)
Local $Button2 = GUICtrlCreateButton("取消", 260, 270, 65, 25)
Local $Button3 = GUICtrlCreateButton("取圖", 70, 270, 65, 25)
Local $idPic1 = GUICtrlCreatePic( @ScriptDir & "\PHOTO\fail.jpg", 220, 150, 110, 110) ;照片
_SQLite_QuerySingleRow(-1, "SELECT COUNT(Serial_Number) FROM purchase;", $aRow) ;執行SQL語法查詢
Local $Temp = INT($aRow[0])
;MsgBox(0,"",$Temp)
If $Temp = 0 Then
GUICtrlSetData($Input2, "1")
Else
_SQLite_QuerySingleRow(-1, "SELECT MAX(Serial_Number) FROM purchase;", $aRow) ;執行SQL語法查詢
$Temp = INT($aRow[0]) + 1
;MsgBox(0,"",$Temp)
GUICtrlSetData($Input2, $Temp)
EndIf
GUISetState(@SW_SHOW,$Form2)
While 1
Local $nMsg = GUIGetMsg()
Switch $nMsg
Case $GUI_EVENT_CLOSE ,$Button2 ; 關閉視窗或是取消鈕則退出
ExitLoop
Case $Button1 ;輸入確認
IF GUICtrlRead($Input1) = "" AND GUICtrlRead($Input2) = "" AND GUICtrlRead($Input3) = "" AND GUICtrlRead($Input5) = "" AND GUICtrlRead($Input6) = "" Then ;防呆處理,須輸入請購單號
MsgBox(0,"輸入錯誤","未輸入完整資料,請確認輸入欄框是否有未輸入資料。")
Else
_SQLite_QuerySingleRow(-1, "SELECT Serial_Number FROM purchase WHERE Serial_Number = '" & GUICtrlRead($Input2) & "';", $aRow) ;執行SQL語法查詢確認是否有該筆資料
$Temp = $aRow[0]
If $Temp = "" Then ;如果沒有該筆資料則新增
_SQLite_Exec(-1, "Insert into purchase (Serial_Number,Unit,Number,Date,Quantity,System,Product_Name,Use,Remaining,Photo)" & _
" values ('" & GUICtrlRead($Input2) & "','" & GUICtrlRead($Combo1) & "','" & GUICtrlRead($Input1) & _
"','" & GUICtrlRead($GUI_DATE) & "','" & GUICtrlRead($Input5) & "','" & GUICtrlRead($Combo2) & _
"','" & GUICtrlRead($Input3) & "','" & GUICtrlRead($Input4) & "','" & GUICtrlRead($Input6) & _
"','" & GUICtrlRead($Input7) & "');")
MsgBox(0,"新增成功","採購單號:" & GUICtrlRead($Input1) & "-" & GUICtrlRead($Input2) & "已新增" )
Else ;有的話顯示該筆記錄
MsgBox(0,"資料重覆","已有該筆資料")
EndIf
EndIf
Case $Button3
FileChangeDir(@ScriptDir & "\PHOTO\")
; 显示选择文件的"打开文件对话框".
Local $sFileOpenDialog = FileOpenDialog("選擇圖片.", @ScriptDir & "\PHOTO\", "*.jpg (*.jpg)|*.bmp (*.bmp)|選擇圖片 (*.*)", $FD_FILEMUSTEXIST)
If @error Then
; 显示发生错误消息.
MsgBox($MB_SYSTEMMODAL, "未選擇圖片", "沒有選擇圖片.")
; 更改工作目录 (@WorkingDir) 到脚本目录的位置, 并设置为 FileOpenDialog() 最后访问的文件夹.
FileChangeDir(@ScriptDir & "\PHOTO\")
Else
; 更改工作目录 (@WorkingDir) 到脚本目录的位置, 并设置为 FileOpenDialog() 最后访问的文件夹.
FileChangeDir(@ScriptDir & "\PHOTO\")
; 替换 FileOpenDialog() 返回字符串的分隔符 "|" 为 @CRLF.
$sFileOpenDialog = StringReplace($sFileOpenDialog, "|", @CRLF)
; 显示选择的文件.
Local $aDays = StringSplit($sFileOpenDialog,"\") ;取出檔案路徑分離
;_ArrayDisplay($aDays)
Local $aExtract = _ArrayExtract($aDays, $aDays[0], $aDays[0]) ;取得最後的檔案名稱
;_ArrayDisplay($aExtract)
GUICtrlSetData($Input7, $aExtract[0], "") ;填入檔案
Sleep(300)
Local $Data11 = "\photo\" & GUICtrlRead($Input7) ; 以下顯示設備前照
Local $iFileExists1 = FileExists(@ScriptDir & $Data11)
If $iFileExists1 Then
GUICtrlSetImage($idPic1,@ScriptDir & $Data11 , -1)
Else
$Data11 = ""
GUICtrlSetImage($idPic1,@ScriptDir & "\photo\fail.jpg" , -1)
EndIf
EndIf
EndSwitch
WEnd
GUIDelete($Form2)
EndFunc
Func SQLiteSelect() ;搜尋資料庫 BY 多筆
_GUICtrlButton_Enable($GUI_Button1,False)
_GUICtrlButton_Enable($GUI_Button4,False)
GUICtrlSetData( $GUI_Input3, "")
If GUICtrlRead($GUI_Combo1) <> "" Or GUICtrlRead($GUI_Input1) <> "" Or GUICtrlRead($GUI_Input2) <> "" Or BitAND(GUICtrlRead($Checkbox1),$GUI_CHECKED) _
Or BitAND(GUICtrlRead($Checkbox2),$GUI_CHECKED) Then
Local $Sqlstr
_GUICtrlListView_DeleteAllItems($GUI_ListView)
$Sqlstr="Select Count(Serial_Number) From purchase Where Serial_Number Like '%' " ;統計顯示數量
If GUICtrlRead($GUI_Combo1) <> "" Then $Sqlstr &= " And Unit ='" & GUICtrlRead($GUI_Combo1) & "'" ;加入請購單位
If GUICtrlRead($GUI_Input1) <> "" Then $Sqlstr &= " And Number LIKE '" & GUICtrlRead($GUI_Input1) & "%'" ;加入請購編號
If GUICtrlRead($GUI_Input2) <> "" Then $Sqlstr &= " And Product_Name Like '%" & GUICtrlRead($GUI_Input2) & "%'" ;加入請購品名
If BitAND(GUICtrlRead($Checkbox1),$GUI_CHECKED) Then ;加入請購日期
$Sqlstr &= " And datetime(Date) = datetime('" & GUICtrlRead ($GUI_DATE) & "')"
EndIf
If BitAND(GUICtrlRead($Checkbox2),$GUI_CHECKED) Then ;加入是否有現貨
$Sqlstr &= " And Remaining >= '1'"
EndIf
;MsgBox(0,"",$Sqlstr)
_SQLite_QuerySingleRow(-1, $Sqlstr & ";" , $aRow) ;取出筆數
$aRow2 = Int($aRow[0])
;MsgBox(0,"",$aRow2)
If $aRow2 <> 0 Then
$Sqlstr="Select * From purchase Where Serial_Number Like '%' " ;統計顯示數量
If GUICtrlRead($GUI_Combo1) <> "" Then $Sqlstr &= " And Unit ='" & GUICtrlRead($GUI_Combo1) & "'" ;加入請購單位
If GUICtrlRead($GUI_Input1) <> "" Then $Sqlstr &= " And Number LIKE'" & GUICtrlRead($GUI_Input1) & "%'" ;加入請購編號
If GUICtrlRead($GUI_Input2) <> "" Then $Sqlstr &= " And Product_Name Like'%" & GUICtrlRead($GUI_Input2) & "%'" ;加入請購品名
If BitAND(GUICtrlRead($Checkbox1),$GUI_CHECKED) Then ;加入請購日期
$Sqlstr &= " And datetime(Date) = datetime('" & GUICtrlRead ($GUI_DATE) & "')"
EndIf
If BitAND(GUICtrlRead($Checkbox2),$GUI_CHECKED) Then ;加入請購日期
$Sqlstr &= " And Remaining >= '1'"
EndIf
_SQLite_Query(-1, $Sqlstr & "ORDER BY Serial_Number DESC ;", $hQuery) ;查詢資料,並存入$hQuery 變數
sql_data_array()
EndIf
;sql_data_array()
EndIf
_GUICtrlButton_Enable ($GUI_Button1,True)
EndFunc
Func sql_data_array() ;取出之資料存入陣列
Local $ArrayData[$aRow2][10] ;設定二維陣列
Local $c = 0 ;初始二維陣列值
While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK
$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]
$ArrayData[$c][5]=$aRow[5]
$ArrayData[$c][6]=$aRow[6]
$ArrayData[$c][7]=$aRow[7]
$ArrayData[$c][8]=$aRow[8]
$ArrayData[$c][9]=$aRow[9]
$c = $c +1
WEnd
_GUICtrlListView_AddArray($GUI_ListView, $ArrayData)
_SQLite_QueryFinalize($hQuery)
MsgBox(0,"筆數","共" & $aRow2 & "筆")
EndFunc
Func SQLCreate() ;建立資料庫
_SQLite_Open ($SQLite_Data_Path) ;建立資料庫,名稱為$SQLite_Data_Path 變數名稱
_SQLite_Exec(-1, "Create Table IF NOT Exists purchase (Serial_Number INT PRIMARY KEY , Unit Text, Number Text, Date DATE," & _
" Quantity INT, System TEXT, Product_Name TEXT, Use TEXT, Remaining INT, Photo TEXT);") ;建立資料庫內容
;^^以上執行SQL語法建立資料表,並建立相關欄位等資料內容
_SQLite_Close (-1) ;關閉資料庫
MsgBox(0,"資料庫","資料庫已建立,請重新執行程式")
Exit
EndFunc
Func WM_NOTIFY($hWnd, $iMsg, $wParam, $lParam)
#forceref $hWnd, $iMsg, $wParam
Local $hWndFrom, $iIDFrom, $iCode, $tNMHDR, $hWndListView, $tInfo
$hWndListView = $GUI_ListView
If Not IsHWnd($GUI_ListView) Then $hWndListView = GUICtrlGetHandle($GUI_ListView)
$tNMHDR = DllStructCreate($tagNMHDR, $lParam)
$hWndFrom = HWnd(DllStructGetData($tNMHDR, "hWndFrom"))
$iIDFrom = DllStructGetData($tNMHDR, "IDFrom")
$iCode = DllStructGetData($tNMHDR, "Code")
Switch $hWndFrom
Case $hWndListView
Switch $iCode
Case $NM_CLICK ; 发送用户鼠标左键点击项目消息
$tInfo = DllStructCreate($tagNMITEMACTIVATE, $lParam)
$a = DllStructGetData($tInfo, "Index") ;取得listview 游標id-index
Example($a) ;轉EXAMPLE FUNC
; 没有返回值
EndSwitch
EndSwitch
Return $GUI_RUNDEFMSG
EndFunc ;==>WM_NOTIFY
Func Example($a) ;滑鼠左鍵跳出詳細內容
Local $aItem, $sText, $idListview ,$i
GUICtrlSetData( $GUI_Input3, "")
GUICtrlSetImage($idPic1,@ScriptDir & "\photo\fail.jpg" , -1)
$aItem = _GUICtrlListView_GetItemTextArray($GUI_ListView, $a) ;讀取指定LIST 內容
_SQLite_QuerySingleRow(-1, "SELECT * FROM purchase WHERE Serial_Number = '" & $aItem[1] & "' COLLATE NOCASE ;", $aRow)
IF $aRow[0] <> "" Then ;如果項目有資料則顯示
GUICtrlSetState($GUI_Button4,$GUI_ENABLE)
$updata = $aRow[0]
GUICtrlSetData( $GUI_Input3, $aRow[8], "0")
Local $Data11 = "\PHOTO\" & $aRow[9] ; 以下顯示設備前照
Local $iFileExists1 = FileExists(@ScriptDir & $Data11)
If $iFileExists1 Then
GUICtrlSetImage($idPic1,@ScriptDir & $Data11 , -1)
$Data11 = ""
Else
$Data11 = ""
GUICtrlSetImage($idPic1,@ScriptDir & "\photo\fail.jpg" , -1)
EndIf
Else
GUICtrlSetState($GUI_Button4,$GUI_DISABLE)
GUICtrlSetImage($idPic1,@ScriptDir & "\photo\fail.jpg" , -1)
EndIf
EndFunc ;==>Example
|
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有账号?加入
×
评分
-
查看全部评分
|