#Region ;**** 参数创建于 ACNWrapper_GUI ****
#AutoIt3Wrapper_icon=C:\Windows\system32\SHELL32.dll|-23
#AutoIt3Wrapper_UseUpx=n
#EndRegion ;**** 参数创建于 ACNWrapper_GUI ****
#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <array.au3>
#include "mysql.au3"
#include <Date.au3>
#include <Timers.au3>
#include <FTPEx.au3>
#include <Excel.au3>
;FTP服务器参数
Global $FTP244_IP = "192.168.0.1"
Global $FTP244_Port = 21
Global $FTP244_User = "abc1"
Global $FTP244_PW = "abc2"
Local $FTP244_Open = 0
Local $FTP244_Connect = 0
Global $FTP244_RemoteFile = "\x86\libmysql.dll";判断本机OS位数，分别定向于X86或X64文件夹
Global $FTP244_LocalFile = @ScriptDir & "\libmysql.dll"
Global $File244_found = 0
;管理服务器参数
Global $serverIP = "192.168.0.2"
Global $conname = "2021"
Global $userID = "abc3"
Global $userPD = "abc4"
Global $DBname = "ordersys"
Local $Ver = "V1.0.0.200"
Local $pc_ip = ""
Local $pc_name = ""
Local $Input1 = ""
Local $Input2 = ""
Local $login = 0
Global $stu_num = ""
Global $stu_name=""
Global $stu_college = ""
Global $stu_class = ""
Global $order_code = ""
Global $date=""
;~ Global $search_Labe2l=""
;================环境检测，核实是否存在必要的libmysql.dll，没有就去244下载一个，直至成功==================OK！
If Not FileExists($FTP244_LocalFile) Then
getdll()
EndIf
#Region ### START Koda GUI section ### Form=d:\au3\教学运行管理系统\查询工具-主.kxf
$WinMain = GUICreate("共享计算机实验室签到记录查询工具", 615, 275, 200, 124)
GUICtrlSetFont(-1, 20, 400, 0, "微软雅黑")
GUISetBkColor(0x008099)
$Label1 = GUICtrlCreateLabel("共享计算机实验室签到记录查询工具", 96, 48, 420, 36)
GUICtrlSetColor(-1, 0xFFFFFF)
GUICtrlSetFont(-1, 20, 400, 0, "微软雅黑")
$Button1 = GUICtrlCreateButton("按日期查询", 64, 148, 75, 25)
$Button2 = GUICtrlCreateButton("按姓名查询", 195, 148, 75, 25)
GUICtrlSetState(-1, $GUI_DISABLE)
$Button3 = GUICtrlCreateButton("按院系查询", 325, 148, 75, 25)
GUICtrlSetState(-1, $GUI_DISABLE)
$Button4 = GUICtrlCreateButton("按班级查询", 456, 148, 75, 25)
GUICtrlSetState(-1, $GUI_DISABLE)
$Label2 = GUICtrlCreateLabel("计算机实验室", 424, 240, 76, 17)
$Label3 = GUICtrlCreateLabel($Ver, 512, 240, 36, 17)
$WinSub = GUICreate("请输入查询条件", 615, 142, 200, 224)
$Sub_Label = GUICtrlCreateLabel("", 56, 32, 360, 17)
GUICtrlSetColor(-1, 0xFF0000)
$Sub_Input = GUICtrlCreateInput("", 120, 72, 121, 21)
$Sub_Button1 = GUICtrlCreateButton("搜索并导出至EXCEL文件", 250, 72, 175, 25)
$Sub_Button2 = GUICtrlCreateButton("返回", 456, 72, 75, 25)
;~ $Sub_Button3 = GUICtrlCreateButton("导出", 304, 100, 75, 25)
GUISwitch($WinMain)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###
While 1
$nMsg = GUIGetMsg()
Switch $nMsg
Case $GUI_EVENT_CLOSE
Exit
Case $Button1
;~ GUISetState(@SW_HIDE,$WinMain)
GUISetState(@SW_SHOW,$WinSub)
GUICtrlSetData($Sub_Label, "请输入要查询的日期（格式：YYYY-MM-DD，允许关键字模糊）：")
$date=@MON & "-" & @MDAY
;~ MsgBox(0,"testdate1",$date)
GUICtrlSetData($Sub_Input, $date)
;~ box()
Case $Button2
;~ GUISetState(@SW_HIDE,$WinMain)
GUISetState(@SW_SHOW,$WinSub)
GUICtrlSetData($Sub_Label, "请输入要查询的姓名（允许关键字模糊）：")
GUICtrlSetData($Sub_Input, "")
Case $Button3
;~ GUISetState(@SW_HIDE,$WinMain)
GUISetState(@SW_SHOW,$WinSub)
GUICtrlSetData($Sub_Label, "请输入要查询的院系（允许关键字模糊）：")
GUICtrlSetData($Sub_Input, "")
Case $Button4
;~ GUISetState(@SW_HIDE,$WinMain)
GUISetState(@SW_SHOW,$WinSub)
GUICtrlSetData($Sub_Label, "请输入要查询的班级（允许关键字模糊）：")
GUICtrlSetData($Sub_Input, "")
Case $Sub_Button1
$date = GUICtrlRead($Sub_Input);刷新刚才输入的输入框数据
;~ MsgBox(0,"testdate2",$date)
$query="SELECT " _;换行格式（行尾：" _，注意带个空格。换行后：&"）
&"stu_info.stu_num,stu_info.stu_name,stu_info.stu_college,stu_info.stu_class,stu_login.pc_ip,stu_login.pc_name,stu_login.login_time " _
&"FROM stu_info " _
&"INNER JOIN stu_login ON stu_info.stu_num = stu_login.stu_num " _
&"WHERE stu_login.login_time LIKE '%" & $date & "%'"
;~ MsgBox(0,"测试——待写入MySQL的语句",$query)
readdb($query)
Case $Sub_Button2
GUISetState(@SW_HIDE,$WinSub)
;~ GUISetState(@SW_SHOW,$WinMain)
;~ Case $Sub_Button3
EndSwitch
WEnd
Func getdll();OK---检验本地system32有无“libmysql.dll”文件，若无，则根据X86或X64，从FTP服务器分别下载相应版本的libmysql.dll文件
Do
$FTP244_Open = _FTP_Open("WHTCC_FTP");创建一个FTP会话
If $FTP244_Open = 0 Then;判断创建会话是否成功
MsgBox(0, "错误", "本机创建会话失败！")
ContinueLoop
EndIf;结束判断
$FTP244_Connect = _FTP_Connect($FTP244_Open, $FTP244_IP, $FTP244_User, $FTP244_PW, $FTP244_Port);_FTP_Connect($l_InternetSession, $s_ServerName, $s_Username, $s_Password
If $FTP244_Connect = 0 Then;连接失败
Beep(80, 100)
MsgBox(64, "连接服务器失败", "发生此错误的可能性：" & @CRLF & @CRLF & " 1、服务器IP是否正确？" & @CRLF & " 2、用户名/密码是否不匹配？" & @CRLF & " 3、线路故障？" & @CRLF & @CRLF & "请核对后重试...")
EndIf;判断结束
_FTP_FileGet($FTP244_Connect, $FTP244_RemoteFile, $FTP244_LocalFile)
$File244_found = _FTP_FileGet($FTP244_Connect, $FTP244_RemoteFile, $FTP244_LocalFile);成功则为1，否则0
_FTP_Close($FTP244_Open)
If FileExists($FTP244_LocalFile) Then;检测文件是否已下载成功
;MsgBox(4096, "环境检测", $FTP244_LocalFile &"下载已成功")
Else
If MsgBox(4, "环境检测", $FTP244_LocalFile & "没有初始化成功，是否继续尝试？") = 7 Then Exit
EndIf
Sleep(3000);延迟3秒再次尝试打开文件
Until $File244_found = 1;直到文件被读取到，否则在此循环
EndFunc ;==>getdll
Func Box()
EndFunc
Func readdb($query);完成---------
;连接远程电脑Mysql数据库
_MySQL_InitLibrary()
$MysqlConn = _MySQL_Init()
$connected = _MySQL_Real_Connect($MysqlConn, $serverIP, $userID, $userPD, $DBname,3306);(,服务器名,ID,密码,数据库名)
If $connected = 0 Then Exit MsgBox(16, '数据库连接失败！Connection Error', _MySQL_Error($MysqlConn))
;执行Mysql语句————读取表中数据
_mysql_set_character_set($MysqlConn, "GBK");_mysql_real_query($MysqlConn,"set names `gbk`") ;设置mysql的输出编辑为 'gbk'
;$query = "SELECT * FROM stu_info where stu_num = " &'"';需要查询的语句;Mysql命令行
_MySQL_Real_Query($MysqlConn, $query);具体执行上述命令行
$res = _MySQL_Store_Result($MysqlConn)
;获取表的列、行数
$fields = _MySQL_Num_Fields($res)
$rows = _MySQL_Num_Rows($res)
;读取的行与列数显示一下
;MsgBox(0,"读取的列行数",$fields & "-" & $rows,20)
If $rows = 0 Then
MsgBox(0, "来自服务器消息", "输入的搜索条件在数据库中没有找到数据...", 6)
GUISetState(@SW_SHOW)
Else
$login = 1
;读取数据，写入数组，并将列表显示出来
Dim $array[$rows][$fields]
For $k = 1 To $rows
$mysqlrow = _MySQL_Fetch_Row($res, $fields)
$lenthsStruct = _MySQL_Fetch_Lengths($res)
For $i = 1 To $fields
$length = DllStructGetData($lenthsStruct, 1, $i)
$fieldPtr = DllStructGetData($mysqlrow, 1, $i)
$data = DllStructGetData(DllStructCreate("char[" & $length & "]", $fieldPtr), 1)
$array[$k - 1][$i - 1] = $data
;MsgBox(0,"Test-服务器读取数据过程",($k - 1)&"-"&($i - 1)&"-"&$array[$k - 1][$i - 1])
Next
Next
;显示搜索结果
;~ _ArrayDisplay($array)
;To Excel
Local $oExcel = _ExcelBookNew() ;创建新工作簿, 并使其可见
;声明数组
;~ Local $aArray[5][2] = [["LocoDarwin", 1],["Jon", 2],["big_daddy", 3],["DaleHolm", 4],["GaryFrost", 5]] ;0 基数组
;蠢法写入EXCEL表头
_ExcelWriteCell($oExcel, "学号", 1, 1)
_ExcelWriteCell($oExcel, "姓名", 1, 2)
_ExcelWriteCell($oExcel, "学院", 1, 3)
_ExcelWriteCell($oExcel, "班级", 1, 4)
_ExcelWriteCell($oExcel, "登陆IP", 1, 5)
_ExcelWriteCell($oExcel, "机器名", 1, 6)
_ExcelWriteCell($oExcel, "登陆时间", 1, 7)
;数组写入excel表格
_ExcelWriteSheetFromArray($oExcel, $array, 2, 1, 0, 0) ;0 基数组参数
MsgBox(0, "数据写入" ,"数据已写入Excel，并保存于桌面！")
_ExcelBookSaveAs($oExcel, @DesktopDir & "\"& @YEAR & @MON & @MDAY & "-" & @HOUR & @MIN& @SEC &".xls", "xls", 0, 1) ; 现在我们保存到 temp 目录中, 如果有必要覆盖现有的文件
;~ _ExcelBookClose($oExcel) ; 最后, 我们关闭文件
;~ $stu_name = $array[0][2]
;~ $stu_college = $array[0][3]
;~ $stu_class = $array[0][4]
;MsgBox(0,"Test-服务器读取到的学生姓名",$stu_name,2)
EndIf
;关闭数据库
_MySQL_Data_Seek($res, 0)
_MySQL_Free_Result($res)
_MySQL_Close($MysqlConn)
_MySQL_EndLibrary()
;$Local_pc_state=$array[0][2]
EndFunc ;==>readdb
|
|手机版|小黑屋|AUTOIT CN
( 鲁ICP备19019924号-1 )谷歌
百度
发表于 2021-4-4 22:27:15
.gif)
.gif)