#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <GuiStatusBar.au3>
#include <GuiListView.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
#Include <GuiComboBox.au3>
#include <MsgBoxConstants.au3>
#include <ListViewConstants.au3>
#include <Array.au3>
#include <EditConstants.au3>
#include <GuiButton.au3>
#include <GuiEdit.au3>
#include <WinAPISys.au3>
#include <StringConstants.au3>
#include "_SciLexer.au3"
#include "SkinH.au3"
#include "BlockInputEx.au3"
Opt("TrayIconHide", 1) ;0 = 顯示托盤圖標, 1 = 隱藏托盤圖標
Opt('MustDeclareVars', 0) ;0 = 變量不需預先聲明, 1 = 變量必需預先聲明
Opt("GUICloseOnESC", 0) ;0 = 點擊 Esc 鍵不關閉, 1 = 點擊 Esc 鍵關閉
Opt('GUIOnEventMode', 0) ;啟用=1/禁用=0 OnEvent 事件函數通知
_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
Global $SQLite_Data_Path = "AU3_CODE.SQLITE" ;sql資料庫路徑及名稱
If Not FileExists($SQLite_Data_Path) Then ;如果無資料庫或資料庫損毀重建時則執行以下命令
Local $QUERY = MsgBox(1, "錯誤", "資料庫無法打開，請檢查""AU3_CODE.SQLITE""檔是否存在或是檔案已損毀，如要建立新檔請按確定，否則取消。")
If $QUERY = 1 Then
SQLCreate() ;建立新資料庫，參考SQLCreate() 功能,在下面
Exit
Else
Exit ;按下取消紐時，程式不執行
EndIf
EndIf
Func SQLCreate() ;建立資料庫
_SQLite_Open($SQLite_Data_Path) ;建立資料庫，名稱為$SQLite_Data_Path 變數名稱
_SQLite_Exec(-1, "Create Table IF NOT Exists Source_Code (Serial_Number Int PRIMARY KEY , Name_ID Int, Range_ID Int, Source_Code Blob, Remarks TEXT);") ;建立資料庫內容
_SQLite_Exec(-1, "Create Table IF NOT Exists Name (Name_ID INT PRIMARY KEY , Name TEXT);") ;
_SQLite_Exec(-1, "Create Table IF NOT Exists Range (Range_ID INT PRIMARY KEY , Range_Name TEXT);") ;
_SQLite_Close(-1) ;關閉資料庫
MsgBox(0, "資料庫", "資料庫已建立，請重新執行程式")
Exit
EndFunc ;==>SQLCreate
;----環境變數
Global $aRow, $hQuery,$aRow2,$a,$ilParam,$tInfo,$ID = 0
Global $iExListViewStyle = BitOR($LVS_EX_FULLROWSELECT, $LVS_EX_GRIDLINES, $LVS_EX_DOUBLEBUFFER);項目選中顯示高亮度題示，顯示項目橫列線條，緩衝控鍵顯示
Global $aItem,$g_idListView,$iHeight,$Pos, $NEW_ListView, $hEdit,$hFont
_SQLite_Open($SQLite_Data_Path) ;建立資料庫，名稱為$SQLite_Data_Path 變數名稱
Global $New_FONT = GUICREATE("新增源碼管理", 1000, 635, -1, -1) ;主視窗畫面
GUICtrlCreateLabel("CODE 主分類：", 10, 10, 100, 20)
Global $New_Combo1 = GUICtrlCreateCombo("", 10, 30, 150, 20) ;主分類選擇
Local $New_SelectName = "|"
_SQLite_Query(-1, "SELECT Name FROM Name ORDER BY Name_ID Asc;",$hQuery) ;讀取全部資料，並存入$hQuery 變數
While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
$New_SelectName &= "|" & $aRow[0]
WEnd
GUICtrlSetData($New_Combo1, $New_SelectName, "" ) ;顯示CODE主分類項目
GUICtrlCreateLabel("CODE 次分類：", 10, 55, 100, 20)
Global $New_Combo2 = GUICtrlCreateCombo("", 10, 70, 150, 20) ;主分類選擇
Local $New_SelectRange = "|"
_SQLite_Query(-1, "SELECT Range_Name FROM Range ORDER BY Range_ID Asc;",$hQuery) ;讀取全部資料，並存入$hQuery 變數
While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
$New_SelectRange &= "|" & $aRow[0]
WEnd
GUICtrlSetData($New_Combo2, $New_SelectRange, "" ) ;顯示CODE次分類項目
GUICtrlCreateLabel("參考項目：", 10, 97, 90, 20)
Global $New_Input1 = GUICtrlCreateInput("",10,115,190,20) ;輸入欲搜尋的CODE參考項目
GUICtrlCreateLabel("現有參考項目：", 10, 140, 100, 20)
$NEW_ListView = GUICtrlCreateListView("序 號|參 考 項 目", 10, 160,250 , 400, BitOR($LVS_REPORT, $WS_BORDER), BitOR($LVS_EX_CHECKBOXES, $LVS_EX_FULLROWSELECT))
_GUICtrlListView_SetExtendedListViewStyle($NEW_ListView, $iExListViewStyle)
_GUICtrlListView_SetColumnWidth ( $NEW_ListView, 0, 40 )
_GUICtrlListView_SetColumnWidth ( $NEW_ListView, 1, 170 )
Local $New_Button1 = GUICtrlCreateButton("新增", 10, 600, 48, 22, 0)
Local $New_Button2 = GUICtrlCreateButton("關閉", 70, 600, 48, 22, 0)
Local $New_Button3 = GUICtrlCreateButton("修改", 130, 600, 48, 22, 0)
Local $New_Button4 = GUICtrlCreateButton("刪除", 190, 600, 48, 22, 0)
;設置皮膚
_SkinH_Init(@ScriptDir, 0)
_SkinH_AttachEx('QQ2009.she') ;設定皮膚檔案
Global $New_Sci = Sci_CreateEditor($New_FONT, 270, 10, 710, 590 ) ;設定顯示框大小
InitEditor($New_Sci,"au3.keywords.properties") ;設定定義函數名稱
Local $New_hProgress = GUICtrlCreateProgress(270, 615, 710, 15) ;設定讀取行數進度條
GUIRegisterMsg($WM_NOTIFY, '_WM_NOTIFY') ;註冊win消息ID-訊息通知,轉WM_NOTIFY函數處理,用於項目雙擊控制
GUISETSTATE( @SW_SHOW,$New_FONT)
While 1
SWITCH GUIGETMSG()
CASE $GUI_EVENT_CLOSE,$New_Button2
GUIDelete($New_FONT)
ExitLoop
Case $New_Button1
New_Code_Wirte()
Case $New_Button3
updata()
Case $New_Button4
deldata()
Case $New_Combo2
NEW_Select_Source_Code()
EndSwitch
GUICtrlSetData($New_hProgress,Round(Sci_GetCurrentLine($New_Sci)/Sci_GetLineCount($New_Sci)*100))
WEND
Func NEW_Select_Source_Code()
_GUICtrlListView_DeleteAllItems($NEW_ListView)
Local $Sqlstr="SELECT count(Source_Code.Serial_Number) FROM Source_Code INNER JOIN Name,Range ON Source_Code.Name_ID = Name.Name_ID and Source_Code.Range_ID = Range.Range_ID " & _
"Where Name.Name = '" & GUICtrlRead($New_Combo1) & "' And Range.Range_Name = '" & GUICtrlRead($New_Combo2) & "'"
_SQLite_QuerySingleRow(-1, $Sqlstr & ";" , $aRow) ;取出筆數
Local $ArrayData[$aRow[0]][2], $c = 0
$Sqlstr = "" ;清空內容
$Sqlstr="SELECT Serial_Number,Remarks FROM Source_Code INNER JOIN Name,Range ON Source_Code.Name_ID = Name.Name_ID and Source_Code.Range_ID = Range.Range_ID " & _
"Where Name.Name = '" & GUICtrlRead($New_Combo1) & "' And Range.Range_Name = '" & GUICtrlRead($New_Combo2) & "'"
_SQLite_Query(-1, $Sqlstr & " ORDER BY Serial_Number ASC ;", $hQuery)
While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
$ArrayData[$c][0]= $aRow[0]
$ArrayData[$c][1]= $aRow[1]
$c = $c+1
WEnd
;_ArrayDisplay($ArrayData)
_GUICtrlListView_AddArray($NEW_ListView,$ArrayData)
EndFunc
Func New_Code_Wirte()
Local $New_Combo1_temp = 0, $New_Combo2_temp = 0, $New_Input1_temp = 0, $New_Combo1_Text, $New_Combo2_Text
Local $dBinary
$New_Combo1_Text = GUICtrlRead($New_Combo1)
$New_Combo2_Text = GUICtrlRead($New_Combo2)
$dBinary = StringToBinary(Sci_GetText($New_Sci), $SB_UTF8)
If GUICtrlRead($New_Combo1) <> "" And GUICtrlRead($New_Combo2) <> "" And GUICtrlRead($New_Input1) <> "" Then
_SQLite_QuerySingleRow(-1,"SELECT Name_ID From Name Where Name ='" & GUICtrlRead($New_Combo1) & "';",$aRow) ;搜尋是否有主分類
If $aRow[0] <> "" Then
$New_Combo1_temp = Int($aRow[0])
Else ;沒就新增
_SQLite_QuerySingleRow(-1, "SELECT MAX(Name_ID) FROM Name ;",$aRow) ;找最大數值
$New_Combo1_temp = Int($aRow[0] + 1)
_SQLite_Exec(-1, "Insert into Name values ('" & $New_Combo1_temp & "','" & GUICtrlRead($New_Combo1) & "');")
EndIf
_SQLite_QuerySingleRow(-1,"SELECT Range_ID From Range Where Range_Name ='" & GUICtrlRead($New_Combo2) & "';",$aRow) ;搜尋是否有次分類
If $aRow[0] <> "" Then
$New_Combo2_temp = Int($aRow[0])
Else
_SQLite_QuerySingleRow(-1, "SELECT MAX(Range_ID) FROM Range ;",$aRow) ;找最大數值
$New_Combo2_temp = Int($aRow[0] + 1)
_SQLite_Exec(-1, "Insert into Range values ('" & $New_Combo2_temp & "','" & GUICtrlRead($New_Combo2) & "');")
EndIf
_SQLite_QuerySingleRow(-1, "SELECT MAX(Serial_Number) FROM Source_Code ;",$aRow) ;找最大數值
$New_Input1_temp = Int($aRow[0] + 1)
_SQLite_Exec(-1, "Insert into Source_Code values ('" & $New_Input1_temp & "','" & $New_Combo1_temp & "','" & $New_Combo2_temp & "','" & $dBinary & "','" & GUICtrlRead($New_Input1) & "');")
EndIf
GUICtrlSetData($NEW_Combo1, "", "" ) ;顯示CODE主分類項目
Local $SelectName = "|"
_SQLite_Query(-1, "SELECT Name FROM Name ORDER BY Name_ID Asc;",$hQuery) ;讀取全部資料，並存入$hQuery 變數
While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
$SelectName &= "|" & $aRow[0]
WEnd
GUICtrlSetData($NEW_Combo1, $SelectName, $New_Combo1_Text ) ;顯示CODE主分類項目
GUICtrlSetData($NEW_Combo2, "", "" ) ;顯示CODE次分類項目
Local $SelectRange = "|"
_SQLite_Query(-1, "SELECT Range_Name FROM Range ORDER BY Range_ID Asc;",$hQuery) ;讀取全部資料，並存入$hQuery 變數
While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
$SelectRange &= "|" & $aRow[0]
WEnd
GUICtrlSetData($New_Input1,"","")
GUICtrlSetData($NEW_Combo2, $SelectRange, $New_Combo2_Text ) ;顯示CODE次分類項目
NEW_Select_Source_Code()
EndFunc
Func updata()
If GUICtrlRead($New_Combo1) <> "" And GUICtrlRead($New_Combo2) <> "" And GUICtrlRead($New_Input1) <> "" Then
$New_Combo1_Text = GUICtrlRead($New_Combo1)
$New_Combo2_Text = GUICtrlRead($New_Combo2)
Local $sOutput = StringReplace( GUICtrlRead($New_Input1), "'", "") ;此行為了應付如果誤輸入 { ' } 時，造成下面查詢語法的錯誤
Local $New_Combo1_temp = 0 ,$New_Combo2_temp = 0,$dBinary = ""
_SQLite_QuerySingleRow(-1,"SELECT Name_ID From Name Where Name ='" & GUICtrlRead($New_Combo1) & "';",$aRow) ;搜尋是否有主分類
If $aRow[0] <> "" Then
$New_Combo1_temp = Int($aRow[0])
Else ;沒就新增
_SQLite_QuerySingleRow(-1, "SELECT MAX(Name_ID) FROM Name ;",$aRow) ;找最大數值
$New_Combo1_temp = Int($aRow[0] + 1)
_SQLite_Exec(-1, "Insert into Name values ('" & $New_Combo1_temp & "','" & GUICtrlRead($New_Combo1) & "');")
EndIf
_SQLite_QuerySingleRow(-1,"SELECT Range_ID From Range Where Range_Name ='" & GUICtrlRead($New_Combo2) & "';",$aRow) ;搜尋是否有次分類
If $aRow[0] <> "" Then
$New_Combo2_temp = Int($aRow[0])
Else
_SQLite_QuerySingleRow(-1, "SELECT MAX(Range_ID) FROM Range ;",$aRow) ;找最大數值
$New_Combo2_temp = Int($aRow[0] + 1)
_SQLite_Exec(-1, "Insert into Range values ('" & $New_Combo2_temp & "','" & GUICtrlRead($New_Combo2) & "');")
EndIf
$dBinary = StringToBinary(Sci_GetText($New_Sci), $SB_UTF8)
_SQLite_Exec(-1, "UPDATE Source_Code SET Name_ID = '" & $New_Combo1_temp & "' , Range_ID = '" & $New_Combo2_temp & "', Remarks = '" & $sOutput & _
"', Source_Code = '"& $dBinary &"' WHERE Serial_Number = '" & $ID & "';") ;
GUICtrlSetData($NEW_Combo1, "", "" ) ;顯示CODE主分類項目
Local $SelectName = "|"
_SQLite_Query(-1, "SELECT Name FROM Name ORDER BY Name_ID Asc;",$hQuery) ;讀取全部資料，並存入$hQuery 變數
While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
$SelectName &= "|" & $aRow[0]
WEnd
GUICtrlSetData($NEW_Combo1, $SelectName, $New_Combo1_Text ) ;顯示CODE主分類項目
GUICtrlSetData($NEW_Combo2, "", "" ) ;顯示CODE次分類項目
Local $SelectRange = "|"
_SQLite_Query(-1, "SELECT Range_Name FROM Range ORDER BY Range_ID Asc;",$hQuery) ;讀取全部資料，並存入$hQuery 變數
While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
$SelectRange &= "|" & $aRow[0]
WEnd
GUICtrlSetData($New_Input1,"","")
GUICtrlSetData($NEW_Combo2, $SelectRange, $New_Combo2_Text ) ;顯示CODE次分類項目
NEW_Select_Source_Code()
MsgBox(0,"已修改完成","源碼已修改完成。")
EndIf
EndFunc
func deldata()
Local $b
If $ID <> 0 Then
$b = MsgBox(1,"源碼刪除","是否刪除序號:" & $ID &" 的源碼")
If $b = 1 Then
_SQLite_Exec(-1, "DELETE FROM Source_Code WHERE Serial_Number = '" & $ID & "';") ;刪除該筆工單資料
Sci_DelLines($Sci)
NEW_Select_Source_Code()
GUICtrlSetData($New_Input1, "", "" ) ;
MsgBox(0,"源碼刪除","已刪除序號：" & $ID & "的源碼")
$ID = ""
EndIf
EndIf
EndFunc
Func _WM_NOTIFY($hWnd, $iMsg, $iwParam, $ilParam) ;滑鼠鍵盤點擊事件
Local $tNMHDR, $hWndFrom, $iCode, $hWndListView
$hWndListView = $NEW_ListView
If Not IsHWnd($NEW_ListView) Then $hWndListView = GUICtrlGetHandle($g_idListView)
$tNMHDR = DllStructCreate($tagNMHDR, $ilParam)
$hWndFrom = DllStructGetData($tNMHDR, 'hWndFrom')
$iCode = DllStructGetData($tNMHDR, 'Code')
Switch $iCode
Case $NM_DBLCLK
Global $aHit = _GUICtrlListView_SubItemHitTest($NEW_ListView)
If $aHit[0] <> -1 Then
Local $aRect = _GUICtrlListView_GetSubItemRect($NEW_ListView, $aHit[0], $aHit[1])
Local $sItemText = _GUICtrlListView_GetItemText($NEW_ListView, $aHit[0], $aHit[1])
Local $aPos = ControlGetPos($hWnd, '', $NEW_ListView)
Local $iStyle = BitOR($WS_CHILD, $WS_VISIBLE, $ES_AUTOHSCROLL, $ES_LEFT)
$hEdit = _GUICtrlEdit_Create($hWnd, $sItemText, $aPos[0] + $aRect[0], $aPos[1] + $aRect[1], _GUICtrlListView_GetColumnWidth($NEW_ListView, $aHit[1]), $iHeight, $iStyle)
_WinAPI_BringWindowToTop($hEdit)
_WinAPI_SetFont($hEdit, $hFont, True)
_GUICtrlEdit_SetMargins($hEdit, $EC_LEFTMARGIN, 4)
_WinAPI_SetFocus($hEdit)
_GUICtrlEdit_SetSel($hEdit, 0, -1)
EndIf
Case $NM_CLICK ;滑鼠左鍵點擊
Local $tInfo = DllStructCreate($tagNMITEMACTIVATE, $ilParam)
Local $a = DllStructGetData($tInfo, "Index") ;取得listview 游標id-index
Mouse_L_Click($a)
EndSwitch
EndFunc ;==>WM_NOTIFY
Func Mouse_L_Click($a) ;滑鼠左鍵選單
Local $aItem, $sText, $i, $iI
Sci_DelLines($Sci)
$aItem = _GUICtrlListView_GetItemTextArray($NEW_ListView, $a) ;讀取指定LIST 內容
If $aItem[1] <> "" Then
$aRow2 = ""
_SQLite_QuerySingleRow(-1, "SELECT Serial_Number,Remarks,Source_Code FROM Source_Code WHERE Serial_Number = '" & $aItem[1] & "';", $aRow) ;查詢資料筆數，並存入變數
$aRow2 =BinaryToString( $aRow[2], $SB_UTF8)
If $aRow2 <> "" Then ;
$ID = $aRow[0]
GUICtrlSetData($New_Input1,$aRow[1],"")
ClipPut($aRow2)
Sci_Paste($Sci)
Else ;將資料撈出並顯示
_SQLite_QueryFinalize($hQuery)
MsgBox(0, "查詢結果", "查無代碼")
$ID = ""
EndIf
Else
$ID = ""
EndIf
EndFunc ;==>Mouse_L_Click
|手机版|小黑屋|AUTOIT CN
( 鲁ICP备19019924号-1 )谷歌
百度
发表于 2021-5-6 02:11:19