首先你得有个获取队列情况的接口。如果使用ami的话太麻烦,接收的数据控制不好断节,所以我用了php+shell的形式获取数据。由于是局域网内使用,所以没加验证,有需求的自己加一下。
vi /var/www/html/cli.php
按i进入插入模式,粘贴上如下代码:
<?php if(isset($_GET["cmd"])){ //先判断是否是预期参数 $fp=popen("asterisk -x \"".$_GET["cmd"]."\"","r"); //asterisk -x单句命令执行 while(!feof($fp)) echo fgets($fp,4096); pclose($fp); } ?>
按Esc输入":wq"保存退出。好了这个接口就可以使用来执行Asterisk CLI命令了。
然后通过你自己的坐席数据库接口,结合ahk写一个客户端。
;数据库配置信息 host = 135.230.71.1 api = http://%host%/sqlapi.php #MaxMem 1024 ;检查连通性 if Not InStr(ping_info := ping(host),"正常") { MsgBox, 4112, 网络错误, % ping_info ExitApp } ;创建GUI gui, add, edit, x0 y0 w800 h400 vshow, gui, show, , 本地呼叫系统坐席队列监控 gui, +AlwaysOnTop +Resize ;循环监控坐席状态 3秒间隔 Loop { out = Queue := QueueStatus() ;获取队列状态数组 tp := get_result_obj("select 队列,业务 from [我是口令呼叫系统数据库].[dbo].[呼叫系统业务分派] where 状态=1 and ((cast(getdate() as time) between 开始时间1 and 结束时间1) or (cast(getdate() as time) between 开始时间2 and 结束时间2));") ;这是一个获取当前正在执行的任务的语句 task := [] for k,v in tp task[v[1]] := v[2] tp := get_result_obj("select 分机,工号,姓名 from [我是口令呼叫系统数据库].[dbo].[呼叫系统分机信息] where [工号] is not null") ;获取已经登记的坐席信息 user := [] for k,v in tp user[v[1]] := {"id":v[2],"name":v[3]} ;格式化输出 for group,gv in Queue { out .= task[group] "(" group ") 策略:" gv["strategy"] " " gv["incall"] "个等待来电 平均摘机时间:" gv["holdtime"] "(秒)平均通话时长:" gv["talktime"] "(秒)`n" for exten,mv in gv["member"] { sp = loop % 20-strlen(user[exten]["name"]) sp .= " " ;很笨的一种汉化替换方法 status := RegExReplace(mv["status"],"In use","通话中") status := RegExReplace(status,"Not in use","空闲") status := RegExReplace(status,"Ringing","振铃") status := RegExReplace(status,"Unavailable","不可用") status := RegExReplace(status,"On hold","保持") out .= " 坐席:" user[exten]["name"] "(" user[exten]["id"] " " exten ")" sp " 类型:" mv["kind"] " 状态:" status " 本次签入后呼叫量:" mv["callcount"] " 最后一次摘机:" mv["lastcalltime"] "秒前`n" } for k,cv in gv["caller"] out .= " 来电:" k "." cv["phone"] " 等待时间:" cv["wait"] "秒`n" out .= "`n" } GuiControl, , show, % out Sleep, 3000 } Return ;界面调整尺寸 GuiSize: GuiControl, move, show, % "w" A_GuiWidth "h" A_GuiHeight Return ;获取状态函数 QueueStatus(){ src := URLDownloadToVar("http://135.230.71.2/cli.php?cmd=queue show") ;你的Asterisk的cli接口 queues := [] Loop, Parse, src, `n, `r { line := A_LoopField if RegExMatch(line,"(\d+) has (\d+) calls \(max unlimited\) in '(\w+)' strategy \((\d+)s holdtime, (\d+)s talktime\), W:\d+, C:\d+, A:\d+, SL:[0-9\.]+% within \d+s",qtm) ;队列标题匹配 { cqueue := qtm1 queues[cqueue] := {"incall":qtm2,"strategy":qtm3,"holdtime":qtm4,"talktime":qtm5,member:{},caller:{}} } else if RegExMatch(line,"\s+sip/(\d+) with penalty 1 \(ringinuse disabled\) \((\w+)\) \((.+)\) has taken (\d+) calls \(last was (\d+) secs ago\)",mcm) ;坐席匹配1 queues[cqueue]["member"][mcm1] := {"kind":mcm2,"status":mcm3,"callcount":mcm4,"lastcalltime":mcm5} else if RegExMatch(line,"\s+sip/(\d+) with penalty 1 \(ringinuse disabled\) \((\w+)\) \((.+)\) has taken no calls yet",mcm) ;坐席匹配2 queues[cqueue]["member"][mcm1] := {"kind":mcm2,"status":mcm3,"callcount":0,"lastcalltime":"NULL"} else if RegExMatch(line,"\s+(\d+). DAHDI/i1/(\d+)-\w+ \(wait: 0:(\d+), prio: 0\)",ccm) ;来电匹配 queues[cqueue]["caller"][ccm1] := {"phone":ccm2,"wait":ccm3} } Return queues } ;返回csv带标题格式查询结果 get_result_with_colname(sql){ global api result := URLDownloadToVar(api "?str=" urlencode(sql "我是口令") "&o=1","UTF-8") return RegExReplace(result,"`n$","") } ;返回csv查询结果 get_result(sql){ global api result := URLDownloadToVar(api "?str=" urlencode(sql "我是口令"),"UTF-8") return RegExReplace(result,"`n$","") } ;返回查询数组 get_result_obj(sql){ global api result := URLDownloadToVar(api "?str=" urlencode(sql "我是口令"),"UTF-8") ;return RegExReplace(result,"`n$","") line := strsplit(result,"`n","`r") for k,v in line { if v line[k] := strsplit(v,",") Else line.remove(k) } return line } ;返回1行数据 get_1_result(sql){ global api result := URLDownloadToVar(api "?str=" urlencode(sql "我是口令"),"UTF-8") return RegExReplace(result,"^([^\n]*)\n.*$","$1") } ;返回执行影响结果 get_rowcount(sql){ global api result := URLDownloadToVar(api "?str=" urlencode(sql "我是口令") "&o=2","UTF-8") return RegExReplace(result,"^([^\n]*)\n.*$","$1") } URLDownloadToVar(url, Encoding = "",Method="GET",postData=""){ ;网址,编码,请求方式,post数据 hObject:=ComObjCreate("WinHttp.WinHttpRequest.5.1") if Method = GET { Try { hObject.Open("GET",url) hObject.Send() } catch e return -1 } else if Method = POST { Try { hObject.Open("POST",url,False) hObject.SetRequestHeader("Content-Type", "application/x-www-form-urlencoded") hObject.Send(postData) } catch e return -1 } if (Encoding && hObject.ResponseBody) { oADO := ComObjCreate("adodb.stream") oADO.Type := 1 oADO.Mode := 3 oADO.Open() oADO.Write(hObject.ResponseBody) oADO.Position := 0 oADO.Type := 2 oADO.Charset := Encoding return oADO.ReadText(), oADO.Close() } return hObject.ResponseText } Ansi2UTF8(sString) { Ansi2Unicode(sString, wString, 0) Unicode2Ansi(wString, zString, 65001) Return zString } UTF82Ansi(zString) { Ansi2Unicode(zString, wString, 65001) Unicode2Ansi(wString, sString, 0) Return sString } Ansi2Unicode(ByRef sString, ByRef wString, CP = 0) { nSize := DllCall("MultiByteToWideChar" , "Uint", CP , "Uint", 0 , "Uint", &sString , "int", -1 , "Uint", 0 , "int", 0) VarSetCapacity(wString, nSize * 2) DllCall("MultiByteToWideChar" , "Uint", CP , "Uint", 0 , "Uint", &sString , "int", -1 , "Uint", &wString , "int", nSize) } Unicode2Ansi(ByRef wString, ByRef sString, CP = 0) { nSize := DllCall("WideCharToMultiByte" , "Uint", CP , "Uint", 0 , "Uint", &wString , "int", -1 , "Uint", 0 , "int", 0 , "Uint", 0 , "Uint", 0) VarSetCapacity(sString, nSize) DllCall("WideCharToMultiByte" , "Uint", CP , "Uint", 0 , "Uint", &wString , "int", -1 , "str", sString , "int", nSize , "Uint", 0 , "Uint", 0) } urlencode(string){ string := Ansi2UTF8(string) StringLen, len, string Loop % len { SetFormat, IntegerFast, hex StringMid, out, string, %A_Index%, 1 hex := Asc(out) hex2 := hex StringReplace, hex, hex, 0x, , All SetFormat, IntegerFast, d hex2 := hex2 If (hex2==33 || (hex2>=39 && hex2 <=42) || hex2==45 || hex2 ==46 || (hex2>=48 && hex2<=57) || (hex2>=65 && hex2<=90) || hex2==95 || (hex2>=97 && hex2<=122) || hex2==126) content .= out Else content .= "`%" hex } Return content } ping(ip){ FileEncoding, RunWait, %ComSpec% /c ping -n 1 %ip% >%A_Temp%\ahk_ping.tmp, , Hide FileRead, content, %A_Temp%\ahk_ping.tmp StringReplace, content, content, `r, , All StringSplit, var, content, `n If content Contains 请求超时,Request timed out Return "请求超时" If content Contains 找不到主机,could not find host Return "找不到主机 " If content Contains 无法访问目标主机,Destination host unreachable Return "无法访问目标主机 " Else { time := RegExReplace(var3, "(来自|Reply from) \d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}[\s的回复|]*: (字节|bytes)=\d{1,3}\ (时间|time)[=<](\d{1,3})ms TTL=\d{1,3}","$4") Return "正常 time:" time "ms" } FileEncoding, UTF-8 } ;调试用 输出数组 show_obj(obj,menu_name:=""){ static id if menu_name = { main = 1 id++ menu_name := id } Menu, % menu_name, add, Menu, % menu_name, DeleteAll for k,v in obj { if (IsObject(v)) { id++ submenu_name := id Menu, % submenu_name, add, Menu, % submenu_name, DeleteAll Menu, % menu_name, add, % k ? "【" k "】[obj]" : "", :%submenu_name% show_obj(v,submenu_name) } Else { Menu, % menu_name, add, % k ? "【" k "】" v: "", MenuHandler } } if main = 1 menu,% menu_name, show MenuHandler: return } GuiClose: ExitApp
好了,看下效果:
RunAs, Administrator, ;用户名 密码 try Run, %ComSpec% /c, , hide catch error ;错误对象 MsgBox, 4112, % error["Message"], % error["Extra"] RunAs
;加载你的dll 注意像下面这种文件名里面有多个点的写全文件名 hModule := DllCall("LoadLibrary", "Str", "time.cqp.dll", "Ptr") ;无参数类型返回字符串 ;Ptr1 := DllCall("time.cqp.dll\info") ;返回指针 ;带参数类型返回字符串 ;注意一定要用numput,不然指针飞了报0x0000005的错 ;长度自己定好 VarSetCapacity(p1,3),VarSetCapacity(p2,3),VarSetCapacity(p3,3),VarSetCapacity(p4,3) numput(0x313233,p1),numput(0x343536,p2),numput(0x373839,p3),numput(0x404142,p4) ;注意这里指针一定要用&变量的格式传入指针 参数记得写全 不然报错-4 Ptr2 := DllCall("time.cqp.dll\plugmain","Ptr",&p1,"Ptr",&p2,"Ptr",&p3,"Ptr",&p4,"Ptr",&p4) Error := ErrorLevel ;保存错误值 Str = ;初始输出字符串变量 Loop { UChar := NumGet(0+Ptr2,A_index-1,"UChar") if (UChar=0x0) ;以0结束 break Str .= Chr(UChar) } MsgBox % Str "`n" Error
从AHK_L 46+开始增加了StrPut()和StrGet()来读取写入字符串到内存。
hModule := DllCall("LoadLibrary", "Str", "time.cqp.dll", "Ptr") StrPutVar("你",p1),StrPutVar("是",p2),StrPutVar("谁",p3),StrPutVar("啊",p4),StrPutVar("?",p5) Ptr2 := DllCall("time.cqp.dll\plugmain","Ptr",&p1,"Ptr",&p2,"Ptr",&p3,"Ptr",&p4,"Ptr",&p5) Error := ErrorLevel ;保存错误值 MsgBox % StrGet(Ptr2) "`n" Error StrPutVar(string, ByRef var, encoding:="") { ; 确定容量. VarSetCapacity( var, StrPut(string, encoding) ; StrPut 返回字符数, 但 VarSetCapacity 需要字节数. * ((encoding="utf-16"||encoding="cp1200") ? 2 : 1) ) ; 复制或转换字符串. return StrPut(string, &var, encoding) }
;Autohotkey+Web Short Message(com.wangtai.smstwoman)短信接口 ;Thinkai@2015-01-05 ;注意每次运行APP的管理网址都不一样 ManageUrl = http://192.168.1.106:1984/8119 ;获取参数 RegExMatch(ManageUrl,"^(http://.*:\d*/)(\d{4})",parameter) global parameter1 global parameter2 ;运用实例 ;发短信 if (SendMsg(10001,7)=1) MsgBox, 64, 提示, 短信发送成功 ;接收回复 NewMessage := GetNewMsg() From := NewMessage[1]["person"] ? NewMessage[1]["person"] "(" NewMessage[1]["address"] ")" : "未知(" NewMessage[1]["address"] ")" MsgBox % "From:" From "`n" NewMessage[1]["body"] ;获取联系人 Contact := GetContacts() for k,v in Contact { MsgBox, 64, 提示, 第一个联系人是%v%`,号码是%k% Return } ;获取新消息函数(调用后收到的) GetNewMsg(){ ;返回数组说明 ;数组[1]: ; _id:消息ID ; address:号码 ; body:短信正文 ; date:收信Uinx时间戳 ; person:联系人 Loop { res := URLDownloadToVar(parameter1 "get_new_msg/" parameter2 "/", "utf-8","POST") ;获取最新短信的JSON数据 if (strlen(res)>2) Return json_toobj(res) Sleep, 100 } } ;获取联系人函数 GetContacts(){ obj := {} ;初始数组 res := URLDownloadToVar(parameter1 "get_contacts/" parameter2 "/", "utf-8","POST") ;获取联系人的JSON数据 ;处理格式问题 StringTrimLeft, res, res, 1 StringTrimRight, res, res, 1 StringSplit, var, res, `, loop % var0 { tmp_var := var%A_index% RegExMatch(tmp_var,"""(.*)"":""(.*)""",m) m1 := RegExReplace(m1,"\s","") m1 := RegExReplace(m1,"\+86","") obj["" m1] := m2 ;注意此处数组key的类型 } Return obj ;返回数组 obj[电话号码] := 联系人姓名 } ;发短信函数 SendMsg(to,msg){ ;号码,消息 msg := urlencode(msg) Return URLDownloadToVar(parameter1 "send/" parameter2 "/" to "/", "utf-8","POST","msg=" msg) } URLDownloadToVar(url, Encoding = "",Method="GET",postData=""){ ;网址,编码,请求方式,post数据 hObject:=ComObjCreate("WinHttp.WinHttpRequest.5.1") if Method = GET { hObject.Open("GET",url) Try hObject.Send() catch e return -1 } else if Method = POST { hObject.Open("POST",url,False) hObject.SetRequestHeader("Content-Type", "application/x-www-form-urlencoded") Try hObject.Send(postData) catch e return -1 } if Encoding { oADO := ComObjCreate("adodb.stream") oADO.Type := 1 oADO.Mode := 3 oADO.Open() oADO.Write(hObject.ResponseBody) oADO.Position := 0 oADO.Type := 2 oADO.Charset := Encoding return oADO.ReadText(), oADO.Close() } return hObject.ResponseText } urlencode(string){ string := Ansi2UTF8(string) StringLen, len, string Loop % len { SetFormat, IntegerFast, hex ;运算结果为HEX StringMid, out, string, %A_Index%, 1 hex := Asc(out) ;获取单字节ascii值 hex2 := hex ;另存变量 StringReplace, hex, hex, 0x, , All SetFormat, IntegerFast, d hex2 := hex2 ;十进制化 ;判断是否可见单字节字符 是则直接连接 否则编码 If (hex2==33 || (hex2>=39 && hex2 <=42) || hex2==45 || hex2 ==46 || (hex2>=48 && hex2<=57) || (hex2>=65 && hex2<=90) || hex2==95 || (hex2>=97 && hex2<=122) || hex2==126) content .= out Else content .= "`%" hex } Return content } Ansi2Oem(sString) { Ansi2Unicode(sString, wString, 0) Unicode2Ansi(wString, zString, 1) Return zString } Oem2Ansi(zString) { Ansi2Unicode(zString, wString, 1) Unicode2Ansi(wString, sString, 0) Return sString } Ansi2UTF8(sString) { Ansi2Unicode(sString, wString, 0) Unicode2Ansi(wString, zString, 65001) Return zString } UTF82Ansi(zString) { Ansi2Unicode(zString, wString, 65001) Unicode2Ansi(wString, sString, 0) Return sString } Ansi2Unicode(ByRef sString, ByRef wString, CP = 0) { nSize := DllCall("MultiByteToWideChar" , "Uint", CP , "Uint", 0 , "Uint", &sString , "int", -1 , "Uint", 0 , "int", 0) VarSetCapacity(wString, nSize * 2) DllCall("MultiByteToWideChar" , "Uint", CP , "Uint", 0 , "Uint", &sString , "int", -1 , "Uint", &wString , "int", nSize) } Unicode2Ansi(ByRef wString, ByRef sString, CP = 0) { nSize := DllCall("WideCharToMultiByte" , "Uint", CP , "Uint", 0 , "Uint", &wString , "int", -1 , "Uint", 0 , "int", 0 , "Uint", 0 , "Uint", 0) VarSetCapacity(sString, nSize) DllCall("WideCharToMultiByte" , "Uint", CP , "Uint", 0 , "Uint", &wString , "int", -1 , "str", sString , "int", nSize , "Uint", 0 , "Uint", 0) } json_toobj(str){ quot := """" ; firmcoded specifically for readability. Hardcode for (minor) performance gain ws := "`t`n`r " Chr(160) ; whitespace plus NBSP. This gets trimmed from the markup obj := {} ; dummy object objs := [] ; stack keys := [] ; stack isarrays := [] ; stack literals := [] ; queue y := nest := 0 ; First pass swaps out literal strings so we can parse the markup easily StringGetPos, z, str, %quot% ; initial seek while !ErrorLevel { ; Look for the non-literal quote that ends this string. Encode literal backslashes as '\u005C' because the ; '\u..' entities are decoded last and that prevents literal backslashes from borking normal characters StringGetPos, x, str, %quot%,, % z + 1 while !ErrorLevel { StringMid, key, str, z + 2, x - z - 1 StringReplace, key, key, \\, \u005C, A If SubStr( key, 0 ) != "\" Break StringGetPos, x, str, %quot%,, % x + 1 } ; StringReplace, str, str, %quot%%t%%quot%, %quot% ; this might corrupt the string str := ( z ? SubStr( str, 1, z ) : "" ) quot SubStr( str, x + 2 ) ; this won't ; Decode entities StringReplace, key, key, \%quot%, %quot%, A StringReplace, key, key, \b, % Chr(08), A StringReplace, key, key, \t, % A_Tab, A StringReplace, key, key, \n, `n, A StringReplace, key, key, \f, % Chr(12), A StringReplace, key, key, \r, `r, A StringReplace, key, key, \/, /, A while y := InStr( key, "\u", 0, y + 1 ) if ( A_IsUnicode || Abs( "0x" SubStr( key, y + 2, 4 ) ) < 0x100 ) key := ( y = 1 ? "" : SubStr( key, 1, y - 1 ) ) Chr( "0x" SubStr( key, y + 2, 4 ) ) SubStr( key, y + 6 ) literals.insert(key) StringGetPos, z, str, %quot%,, % z + 1 ; seek } ; Second pass parses the markup and builds the object iteratively, swapping placeholders as they are encountered key := isarray := 1 ; The outer loop splits the blob into paths at markers where nest level decreases Loop Parse, str, % "]}" { StringReplace, str, A_LoopField, [, [], A ; mark any array open-brackets ; This inner loop splits the path into segments at markers that signal nest level increases Loop Parse, str, % "[{" { ; The first segment might contain members that belong to the previous object ; Otherwise, push the previous object and key to their stacks and start a new object if ( A_Index != 1 ) { objs.insert( obj ) isarrays.insert( isarray ) keys.insert( key ) obj := {} isarray := key := Asc( A_LoopField ) = 93 } ; arrrrays are made by pirates and they have index keys if ( isarray ) { Loop Parse, A_LoopField, `,, % ws "]" if ( A_LoopField != "" ) obj[key++] := A_LoopField = quot ? literals.remove(1) : A_LoopField } ; otherwise, parse the segment as key/value pairs else { Loop Parse, A_LoopField, `, Loop Parse, A_LoopField, :, % ws if ( A_Index = 1 ) key := A_LoopField = quot ? literals.remove(1) : A_LoopField else if ( A_Index = 2 && A_LoopField != "" ) obj[key] := A_LoopField = quot ? literals.remove(1) : A_LoopField } nest += A_Index > 1 } ; Loop Parse, str, % "[{" If !--nest Break ; Insert the newly closed object into the one on top of the stack, then pop the stack pbj := obj obj := objs.remove() obj[key := keys.remove()] := pbj If ( isarray := isarrays.remove() ) key++ } ; Loop Parse, str, % "]}" Return obj }
Mail("sender@mail.com","receiver@mail.com","测试" A_Now,"正文空空如也") ;qq邮箱需要到网页邮箱设置-账户里面开启smtp服务,密码是另外生成的密码 Mail(from,to,subject,content,attach*){ ;发件人,收件人,标题,正文,附件文件路径数组 eg:["d:\a.xls","d:\b.doc"] NameSpace := "http://schemas.microsoft.com/cdo/configuration/" Email := ComObjCreate("CDO.Message") Email.From := from Email.To := to ;Email.Cc := "cc@mail.com" ;抄送 ;Email.Bcc := "bcc@mail.com" ;暗送 Email.Subject := subject ;Email.Htmlbody := content ;html格式的正文 Email.Textbody := content ;纯文本格式的正文 for k,v in attach { IfExist, % v Email.AddAttachment(v) } Email.Configuration.Fields.Item(NameSpace "sendusing") := 2 Email.Configuration.Fields.Item(NameSpace "smtpserver") := "smtp.mail.com" ;SMTP服务器地址 Email.Configuration.Fields.Item(NameSpace "smtpserverport") := 25 ;smtp发送端口 qq:465 Email.Configuration.Fields.Item(NameSpace "smtpauthenticate") := 1 ;需要验证 ;Email.Configuration.Fields.Item(NameSpace "smtpusessl") := true ;使用ssl qq等需要 Email.Configuration.Fields.Item(NameSpace "sendusername") := "sender@mail.com" ;邮箱账号 Email.Configuration.Fields.Item(NameSpace "sendpassword") := "password" ;邮箱密码 Email.Configuration.Fields.update() Email.Fields.Item("urn:schemas:mailheader:disposition-notification-to") := from ;设置“已读”回执 Email.Fields.Item("urn:schemas:mailheader:return-receipt-to") := from ;设置“已送达”回执 Email.Fields.Update() Email.Send }
这是使用File对象把可见十六进制与文件互转的一个函数。
;文件转十六进制 file2hex(file){ tmp_file := FileOpen(file, "r") while not tmp_file.AtEOF { ;没有到结尾 tmp_file.RawRead(Data, 1) tmp_hex := SubStr("00" . ToBase(Asc(Data),16),-1) hex = % hex tmp_hex } tmp_file.Close() return hex } ;十六进制转文件 比如hex=313233 ,文件内容则为123 hex2file(hex,file){ tmp_file := FileOpen(file, "w") if (Mod(StrLen(hex),2)=1) ;不是双数长度 return -1 while hex { StringLeft, tmp_hex, hex, 2 StringTrimLeft, hex,hex, 2 tmp_hex := "0x" tmp_hex tmp_file.WriteUChar(tmp_hex) } tmp_file.Close() tmp_file = } ;进制转换函数 ToBase(n,b){ return (n < b ? "" : ToBase(n//b,b)) . ((d:=Mod(n,b)) < 10 ? d : Chr(d+55)) }
此脚本为单机绿色小软件,着重突出自定义日期提醒规则,方便与客户高效的进行联系和记录。
注意事项:
1、此脚本所内置的sqlite3.dll为Navicat11自带版本,部分函数其他版本dll没有,请注意不要搞混(编译exe不用考虑此项)。
2、姓名和产品型号可以快速输入,只要你输入部分字词,会自动检索相关项目并添加到下拉框中。
3、16项字段里面有3个日期规则,销售日期匹配提醒规则模板的内容,专属提醒则为指定一次性日期,生日每年当天提醒。
4、标记有6种状态,在刷选的时候注意按提示输入数字而不是汉字。
5、导出的列表为csv格式,如果日期格式有问题可以改后缀为.txt再用Excel手动打开,类型选择”文本“。
#NoEnv #SingleInstance froce #Include SQLiteDB.ahk OnExit, ExitScript ;DIY项目 ;主标题 maintitle = 售后定时客户关怀提醒 ;预置文件 FileCreateDir, % A_ScriptDir "\ico\" FileInstall, 1.ico, % A_ScriptDir "\ico\1.ico" FileInstall, 2.ico, % A_ScriptDir "\ico\2.ico" FileInstall, 3.ico, % A_ScriptDir "\ico\3.ico" FileInstall, 4.ico, % A_ScriptDir "\ico\4.ico" FileInstall, 5.ico, % A_ScriptDir "\ico\5.ico" FileInstall, 6.ico, % A_ScriptDir "\ico\6.ico" FileInstall, sqlite3.dll, sqlite3.dll ;初始化连接数据库 以便反复查询 DBFileName := A_ScriptDir . "\after_sales_noti.db" DB := new SQLiteDB if !DB.OpenDB(DBFileName) { MsgBox, 16, SQLite错误, % "消息:`t" . DB.ErrorMsg . "`n代码:`t" . DB.ErrorCode ExitApp } ;首先检查初始化 if !IsObject(Query("select 1 from sqlite_master where name='noti'")) ;检查提醒模板表 Exec("CREATE TABLE ""noti"" ( ""noti_level"" TEXT(50), ""noti_rules"" TEXT(255), ""id"" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, CONSTRAINT ""noti_level"" UNIQUE (""noti_level"") ON CONFLICT REPLACE )") if !IsObject(Query("select 1 from sqlite_master where name='detail'")) ;检查提醒清单表 Exec("CREATE TABLE ""detail"" (""name"" TEXT(50),""phone"" TEXT(15),""cell"" TEXT(15),""birthday"" TEXT(50),""product_model"" TEXT(255),""product_info"" TEXT(255),""sale_date"" TEXT(20),""qq"" TEXT(50),""mail"" TEXT(100),""address"" TEXT(255),""note"" TEXT(255),""noti_level"" TEXT(50),""timer"" TEXT(50),""state"" INTEGER,""create_date"" TEXT(50),""id"" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL)") if !IsObject(Query("select 1 from sqlite_master where name='setting'")) ;检查设置表 Exec("CREATE TABLE ""setting"" (""name"" TEXT(50),""value"" TEXT(255),""id"" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL)") if !IsObject(Query("select 1 from setting")) ;如果没有设置项 Exec("Insert INTO setting (""name"", ""value"") select 'last_login',date('" thisday "');Insert INTO setting (""name"", ""value"") select 'viewed',0;") if !IsObject(Query("select 1 from noti")) ;如果没有提醒模板 Exec("Insert INTO noti (""noti_level"", ""noti_rules"") VALUES ('普通用户', '+7|+15');Insert INTO noti (""noti_level"", ""noti_rules"") VALUES ('VIP', '+3|+7|+15');") ;刷选预设 search_options := {"姓名":{"real":"name","method":"等于|包含"} ,"电话":{"real":"phone","method":"等于|包含"} ,"手机":{"real":"cell","method":"等于|包含"} ,"生日":{"real":"date(birthday)","method":"等于|介于|不等于|包含|大于|小于","example1":"1949-10-01","example2":"1959-10-01"} ,"产品型号":{"real":"product_model","method":"等于|包含"} ,"产品信息":{"real":"product_info","method":"等于|包含"} ,"QQ":{"real":"qq","method":"等于|介于|包含|大于|小于"} ,"邮箱":{"real":"mail","method":"等于|包含"} ,"地址":{"real":"address","method":"等于|包含"} ,"备注":{"real":"note","method":"等于|包含"} ,"提醒规则模板":{"real":"noti_level","method":"等于|包含"} ,"专属提醒时间":{"real":"date(timer)","method":"等于|介于|大于|小于","example1":"1949-10-01","example2":"1959-10-01"} ,"标记状态":{"real":"state","method":"等于|不等于","example1":"1"} ,"创建时间":{"real":"date(create_date)","method":"等于|介于|不等于|包含|大于|小于","example1":"1949-10-01","example2":"1959-10-01"}} ;创建托盘菜单 menu, Tray, NoStandard Menu, tray, Add, 主界面 Menu, tray, Add, 提醒规则管理 Menu, tray, Add, 开机启动 Menu, tray, Add, 退出 Menu, tray, Default, 主界面 ;图标列表 ImageListID := IL_Create(6) Loop 6 IL_Add(ImageListID, A_ScriptDir "\ico\" A_Index ".ico") gosub, 主界面 return 主界面: ;提醒模板 noti := Query("select * from noti") noti_para = for k,v in noti noti_para .= noti_para ? "|" v[1] : v[1] If main_gui_showed { Gui, main:show ControlGet, l, List, Count, ComboBox3, % maintitle Loop % (StrSplit(l,"`n")).MaxIndex() Control, Delete, 1, ComboBox3, % maintitle GuiControl, main:, noti_level, % noti_para } else { Gui, main:Add, Text, x0 y0 w60 h20, 姓 名 Gui, main:Add, ComboBox, x40 y0 w170 vname gname Gui, main:Add, Text, x0 y20 w60 h20, 电 话 Gui, main:Add, Edit, x40 y20 w170 h20 -Multi vphone Gui, main:Add, Text, x0 y40 w60 h20, 手 机 Gui, main:Add, Edit, x40 y40 w170 h20 -Multi vcell Gui, main:Add, Text, x0 y60 w60 h20, Q Q Gui, main:Add, Edit, x40 y60 w170 h20 -Multi vqq Gui, main:Add, Text, x0 y80 w60 h20, 邮 箱 Gui, main:Add, Edit, x40 y80 w170 h20 -Multi vmail Gui, main:Add, Text, x0 y120 w60 h20, 地 址 Gui, main:Add, Edit, x40 y100 w170 h60 vaddress Gui, main:Add, Text, x210 y0 w60, 产品型号 Gui, main:Add, ComboBox, x260 y0 w170 h20 vproduct_model gproduct_model Gui, main:Add, Text, x210 y20 w60 h20, 产品信息 Gui, main:Add, Edit, x260 y20 w170 h40 vproduct_info Gui, main:Add, Text, x210 y60 w60 h20, 销售日期 Gui, main:Add, DateTime, x260 y60 w170 h20 vsale_date Gui, main:Add, Text, x210 y80 w60 h20, 专属提醒 Gui, main:Add, DateTime, x260 y80 w170 h20 vtimer Gui, main:Add, Text, x210 y100 w60 h20, 用户生日 Gui, main:Add, DateTime, x260 y100 w170 h20 vbirthday Gui, main:Add, Text, x210 y120 w60 h20, 备 注 Gui, main:Add, Edit, x260 y120 w170 h40 vnote Gui, main:Add, GroupBox, x440 y0 w200 h120, 标记状态 Gui, main:Add, Radio, x450 y26 w60 h20 gr1 vr1, 1初始化 Gui, main:Add, Picture, x510 y20 w32 h32 gp1, %A_ScriptDir%\ico\1.ico Gui, main:Add, Radio, x542 y26 w60 h20 gr2 vr2, 2再联系 Gui, main:Add, Picture, x602 y20 w32 h32 gp2, %A_ScriptDir%\ico\2.ico Gui, main:Add, Radio, x450 y58 w60 h20 gr3 vr3, 3已完成 Gui, main:Add, Picture, x510 y52 w32 h32 gp3, %A_ScriptDir%\ico\3.ico Gui, main:Add, Radio, x542 y58 w60 h20 gr4 vr4, 4失败 Gui, main:Add, Picture, x602 y52 w32 h32 gp4, %A_ScriptDir%\ico\4.ico Gui, main:Add, Radio, x450 y90 w60 h20 gr5 vr5, 5黑名单 Gui, main:Add, Picture, x510 y84 w32 h32 gp5, %A_ScriptDir%\ico\5.ico Gui, main:Add, Radio, x542 y90 w60 h20 gr6 vr6, 6要上心 Gui, main:Add, Picture, x602 y84 w32 h32 gp6, %A_ScriptDir%\ico\6.ico Gui, main:Add, Text, x440 y120 w200 h20, 客户提醒规则模板 Gui, main:Add, DDL, x440 y140 w200 R20 vnoti_level, % noti_para Gui, main:Add, Button, x0 y170 w100 h20 gnew, 新建资料 Gui, main:Add, Button, x108 y170 w100 h20 gsave vsave, 保存资料 Gui, main:Add, Button, x216 y170 w100 h20 gdelete, 删除资料 Gui, main:Add, Button, x324 y170 w100 h20 gexport, 导出下方列表 Gui, main:Add, DateTime, x432 y170 w110 h20 vsearch_date, Gui, main:Add, Button, x542 y170 w100 h20 gview_thisday, 查看此日提醒 Gui, main:Add, Text, x20 y203 w100 h20, 查询销售日期从 Gui, main:Add, DateTime, x120 y200 w200 h20 vsearch_startdate, Gui, main:Add, Text, x320 y203 w20 h20, 到 Gui, main:Add, DateTime, x340 y200 w200 h20 vsearch_enddate, Gui, main:Add, Text, x540 y203 w90 h20, 的清单数据 Gui, main:Add, Text, x20 y223 w30 h20, 筛选 Gui, main:Add, DropDownList, x50 y220 w150 h20 R20 vsearch_key gsearch_key, 无|姓名|电话|手机|生日|产品型号|产品信息|QQ|邮箱|地址|备注|提醒规则模板|专属提醒时间|标记状态|创建时间 Gui, main:Add, DropDownList, x200 y220 w50 h20 R10 vsearch_method gsearch_method, 等于|介于|不等于|包含|大于|小于 Gui, main:Add, Edit, x250 y220 w150 h20 -Multi vsearch_value1, Gui, main:Add, Edit, x400 y220 w150 h20 -Multi vsearch_value2, Gui, main:Add, Button, x550 y220 w80 h20 gsearch, 查询 Gui, main:Add, ListView, x0 y240 w640 h200 vmylv gmylv, 姓名|电话|手机|生日|产品型号|产品信息|销售日期|QQ|邮箱|地址|备注|提醒规则模板|专属提醒时间|标记状态|创建时间|ID Gui, main:Add, StatusBar, x0 y440 w640 h20, Gui, main:Show, , % maintitle Gui, main:Default LV_SetImageList(ImageListID) ;OnMessage(0x201, "WM_LButtonDOWN") GuiControl, main:, r1, 1 GuiControl, main:, timer, 19000101 GuiControl, main:, birthday, 19000101 main_gui_showed = 1 } gosub, view_thisday return ;姓名快速提示 name: GuiControlGet, name result := append2obj(Query("select name from detail where name like '" name "%' group by name"),Query("select name from detail where name like '%" name "%' and name not like '" name "%' group by name")) if IsObject(result) { name_para = ControlGet, l, List, Count, ComboBox1, % maintitle Loop % (StrSplit(l,"`n")).MaxIndex() Control, Delete, 1, ComboBox1, % maintitle for k,v in result name_para .= name_para ? "|" v[1] : v[1] GuiControl, main:, name, % name_para SB_SetText("下拉【姓名】可点击待选快速输入!") } return ;产品型号快速提示 product_model: GuiControlGet, product_model result := append2obj(Query("select product_model from detail where product_model like '" product_model "%' group by product_model"),Query("select product_model from detail where product_model like '%" product_model "%' and product_model not like '" product_model "%' group by product_model")) if IsObject(result) { product_model_para = ControlGet, l, List, Count, ComboBox2, % maintitle Loop % (StrSplit(l,"`n")).MaxIndex() Control, Delete, 1, ComboBox2, % maintitle for k,v in result product_model_para .= product_model_para ? "|" v[1] : v[1] GuiControl, main:, product_model, % product_model_para SB_SetText("下拉【产品型号】点击待选可快速输入!") } return ;新建 new: SB_SetText("新建") change= GuiControl, main:, save, 保存资料 for k,v in ["phone","cell","qq","mail","address","product_info","note"] GuiControl, main:, % v, for k,v in ["name","product_model","noti_level"] GuiControl, main:Choose, % v, 0 GuiControl, main:, sale_date, % A_Now GuiControl, main:, timer, 19000101 GuiControl, main:, birthday, 19000101 GuiControl, main:, r1, 1 gosub, r1 return ;保存 save: Gui, main:Submit, NoHide if (!noti_level) { MsgBox, 4112, 错误, 您未选择客户提醒规则模板,请核对! return } if (!name && !phone && !cell) { MsgBox, 4112, 错误, 您未填写用户姓名或电话,请至少填一项有效信息! return } Loop, 6 { if (r%A_Index%=1) { state := A_index break } } FormatTime, sale_date, % sale_date, yyyy-MM-dd HH:mm:ss FormatTime, timer, % timer, yyyy-MM-dd HH:mm:ss FormatTime, birthday, % birthday, yyyy-MM-dd HH:mm:ss address := RegExReplace(RegExReplace(address,"`r","\r"),"`n","\n") note := RegExReplace(RegExReplace(note,"`r","\r"),"`n","\n") if !change result := Exec("Insert into detail ('name','phone','cell','qq','mail','address','product_model','product_info','sale_date','timer','birthday','note','noti_level','state','create_date') select '" name "','" phone "','" cell "','" qq "','" mail "','" address "','" product_model "','" product_info "','" sale_date "','" timer "','" birthday "','" note "','" noti_level "'," state ",date('" thisday "')") else result := Exec("update detail set name='" name "',phone='" phone "',cell='" cell "',qq='" qq "',mail='" mail "',address='" address "',product_model='" product_model "',product_info='" product_info "',sale_date='" sale_date "',timer='" timer "',birthday='" birthday "',note='" note "',noti_level='" noti_level "',state=" state " where id=" change_id) if result { SB_SetText("已保存!") if issearch gosub, refresh else gosub, view_thisday gosub, new } return ;查询 search: issearch = 1 Gui, main:Submit, NoHide FormatTime, search_startdate, % search_startdate, yyyy-MM-dd FormatTime, search_enddate, % search_enddate, yyyy-MM-dd SQL = select * from detail condition := {} conditions = condition.Insert("date(sale_date) between '" search_startdate "' and '" search_enddate "'") if (search_key && search_method) { if search_method = 等于 condition.Insert(search_options[search_key]["real"] "='" search_value1 "'") else if search_method = 介于 condition.Insert(search_options[search_key]["real"] " between '" search_value1 "' and '" search_value2 "'") else if search_method = 不等于 condition.Insert(search_options[search_key]["real"] "<>'" search_value1 "'") else if search_method = 包含 condition.Insert(search_options[search_key]["real"] " like '%" search_value1 "%'") else if search_method = 大于 condition.Insert(search_options[search_key]["real"] ">'" search_value1 "'") else if search_method = 小于 condition.Insert(search_options[search_key]["real"] "<'" search_value1 "'") } for k,v in condition conditions .= conditions ? " and " v : " where " v gosub, refresh return ;查看指定日期按规则生成的提醒 view_thisday: GuiControlGet, search_date if search_date FormatTime, thisday, % search_date, yyyy-MM-dd if !thisday FormatTime, thisday, % A_Now, yyyy-MM-dd lv_result= issearch= ;获取提醒规则模板 levels := Query("select noti_level,noti_rules from noti") ;枚举循环规则 for i,r in levels { level := r[1],rule_c:=r[2] rules := StrSplit(rule_c,"|") for i,rule in rules { if RegExMatch(rule,"\+(\d{1,5})$",m) ;+xx天 lv_result := append2obj(lv_result,Query("SELECT * FROM detail WHERE julianday(date('" thisday "'))-julianday(date(sale_date))=" m1 " and noti_level='" level "'")) if RegExMatch(rule,"E(\d{1,5})$",m) ;每隔xx天 lv_result := append2obj(lv_result,Query("SELECT * FROM detail WHERE (Floor(julianday(date('" thisday "'))-julianday(sale_date))-Floor(Floor(julianday(date('" thisday "'))-julianday(sale_date))/" m1 ")*" m1 ")<1 AND julianday(date('" thisday "'))-julianday(date(sale_date))>=1 and noti_level='" level "'")) if RegExMatch(rule,"EW$",m) ;按周重复 lv_result := append2obj(lv_result,Query("SELECT * FROM detail WHERE strftime('%w',sale_date)=strftime('%w',date('" thisday "')) AND julianday(date('" thisday "'))-julianday(date(sale_date))>=1 and noti_level='" level "'")) if RegExMatch(rule,"EM$",m) ;按月重复 lv_result := append2obj(lv_result,Query("SELECT * FROM detail WHERE strftime('%d',sale_date)=strftime('%d',date('" thisday "')) AND julianday(date('" thisday "'))-julianday(date(sale_date))>=1 and noti_level='" level "'")) if RegExMatch(rule,"EY$",m) ;按年重复 lv_result := append2obj(lv_result,Query("SELECT * FROM detail WHERE strftime('%m-%d',sale_date)=strftime('%m-%d',date('" thisday "')) AND julianday(date('" thisday "'))-julianday(date(sale_date))>=1 and noti_level='" level "'")) if RegExMatch(rule,"EW(\d{1})$",m) ;每周X { if (A_WDay=(m1+1>7 ? 1 : a+m1)) lv_result := append2obj(lv_result,Query("SELECT * FROM detail WHERE noti_level='" level "'")) } if RegExMatch(rule,"EM(\d{1,2})$",m) ;每月XX { if (A_DD=m1) lv_result := append2obj(lv_result,Query("SELECT * FROM detail WHERE noti_level='" level "'")) } if RegExMatch(rule,"EM(\d{1,2})$",m) ;每月XX { if (A_DD=m1) lv_result := append2obj(lv_result,Query("SELECT * FROM detail WHERE noti_level='" level "'")) } } } ;定时提醒 lv_result := append2obj(lv_result,Query("SELECT * FROM detail WHERE date(timer)='" A_YYYY "-" A_MM "-" A_DD "'")) ;生日提醒 lv_result := append2obj(lv_result,Query("SELECT * FROM detail WHERE strftime('%m-%d',birthday)='" A_MM "-" A_DD "' AND date(birthday)<>'1900-01-01'")) gosub, refresh return ;筛选方式 search_method: GuiControlGet, search_method if search_method { if search_method in 等于,不等于,包含,大于,小于 { GuiControl, main:Enable, search_value1 GuiControl, main:Disable, search_value2 } else if search_method = 介于 { GuiControl, main:Enable, search_value1 GuiControl, main:Enable, search_value2 } } else { GuiControl, main:Disable, search_value1 GuiControl, main:Disable, search_value2 } return ;筛选项 search_key: GuiControlGet, search_key if (search_key<>"无" && search_key) GuiControl, main:Enable, search_method else GuiControl, main:Disable, search_method GuiControl, main:Disable, search_value1 GuiControl, main:Disable, search_value2 ;search_options ControlGet, l, List, Count, ComboBox5, % maintitle Loop % (StrSplit(l,"`n")).MaxIndex() Control, Delete, 1, ComboBox5, % maintitle GuiControl, main:, search_method, % search_options[search_key]["method"] GuiControl, main:, search_value1, % search_options[search_key]["example1"] GuiControl, main:, search_value2, % search_options[search_key]["example2"] return ;列表 mylv: show_detail: change := 1 GuiControl, main:, save, 保存修改 FocusedRowNumber := LV_GetNext(0, "F") if not FocusedRowNumber return SB_SetText("显示列表中第" FocusedRowNumber "行详细信息,可修改保存") Loop 16 { LV_GetText(var%A_Index%, FocusedRowNumber,A_Index) } change_id := var16 for k,v in {"phone":var2,"cell":var3,"product_info":var6,"qq":var8,"mail":var9} GuiControl, main:, % k, % v for k,v in {"address":var10,"note":var11} GuiControl, main:, % k, % RegExReplace(RegExReplace(v,"\\n","`n"),"\\r","`r") for k,v in {"Edit1":var1,"Edit7":var5} ControlSetText, % k, % v, % maintitle for k,v in noti { if (var12=v[1]) { GuiControl, main:Choose, noti_level, % k break } } for k,v in {"birthday":var4,"sale_date":var7,"timer":var13} GuiControl, main:, % k, % RegExReplace(RegExReplace(RegExReplace(v,"\s",""),"-",""),":","") gosub, % "r" var14 return delete: FocusedRowNumber := LV_GetNext(0, "F") if not FocusedRowNumber return LV_GetText(Delete_id, FocusedRowNumber,16) result := Exec("Delete from detail where id=" Delete_id) if result { MsgBox, 64, 提示, 已删除选中项! gosub, refresh } return ;刷新 refresh: LV_Delete() if issearch lv_result := Query(SQL conditions) if (IsObject(lv_result) && ObjKeyCount(lv_result)) { for k,v in lv_result { LV_Add("Icon" . v[14],v[1],v[2],v[3],v[4],v[5],v[6],v[7],v[8],v[9],v[10],v[11],v[12],v[13],v[14],v[15],v[16]) } LV_ModifyCol() SB_SetText(k "项结果,双击指定行查看、修改") } else SB_SetText("无结果") return ;导出 export: FileSelectFile, file, S, , 请选择保存位置, CSV文件(*.csv) if file { content = `"姓名`",`"电话`",`"手机`",`"生日`",`"产品型号`",`"产品信息`",`"销售日期`",`"QQ`",`"邮箱`",`"地址`",`"备注`",`"提醒规则模板`",`"专属提醒时间`",`"标记状态`",`"创建时间`",`"ID`" for k,v in lv_result { line = for x,y in v { line .= line ? ",""" y """" : """" y """" } content .= "`n" line } file := InStr(file,".csv") ? file : file ".csv" FileDelete, % file FileAppend, % content, % file Run, % file } return 提醒规则管理: for k,v in Query("select * from noti") notis .= notis ? "|" v[1] : v[1] Gui, noti:Destroy Gui, noti:Add, Text, x0 y0 w400 h20, 规则模板名称: Gui, noti:Add, ComboBox, x0 y20 w400 R20 vnotilevelname gnotilevelname, % notis Gui, noti:Add, Text, x0 y40 w400 h20, 规则 Gui, noti:Add, Edit, x0 y60 w400 h20 -Multi vnotilevelvalue, Gui, noti:Add, Button, x0 y80 w130 h20 gnotisubmit, 提交 Gui, noti:Add, Button, x135 y80 w130 h20 gnotidelete, 删除 Gui, noti:Add, Button, x270 y80 w130 h20 gnotihelp, 帮助 Gui, noti:Show, , 提醒规则管理 Gui, noti:+AlwaysOnTop return notilevelname: GuiControlGet, notilevelname if IsObject(t := Query("select noti_rules from noti where noti_level='" notilevelname "'")) GuiControl, noti:, notilevelvalue, % t[1][1] return notisubmit: Gui, noti:Submit, NoHide if (Exec("Replace INTO noti (""noti_level"", ""noti_rules"") VALUES ('" notilevelname "', '" notilevelvalue "')")) MsgBox, 4160, 提示, 添加/修改成功! return notidelete: Gui, noti:Submit, NoHide if (Exec("Delete from noti where noti_level='" notilevelname "'")) MsgBox, 4160, 提示, 删除成功! return notihelp: MsgBox, 64, 规则帮助, 规则(n代表数值):`n+nn 延后nn天`nEnn 每隔nn天`nEW 每周`nEWn 每周星期n`nEM 每月`nEMnn 每月nn日`nEY 每年`n多项以“|”分隔`n参照日期以销售日期为准。 return 开机启动: RegRead, var, HKCU, Software\Microsoft\Windows\CurrentVersion\Run, after_sales_noti last := A_LastError if last=2 { RegWrite, REG_SZ, HKCU, Software\Microsoft\Windows\CurrentVersion\Run, after_sales_noti, %A_ScriptFullPath% if Errorlevel MsgBox, 16, 错误, 没有权限或者被安全软件拦截了,请下次再试! else Menu, Tray, Check, 开机启动 } if last=0 { RegDelete, HKCU, Software\Microsoft\Windows\CurrentVersion\Run, after_sales_noti if Errorlevel MsgBox, 16, 错误, 没有权限或者被安全软件拦截了,请下次再试! else Menu, Tray, UnCheck, 开机启动 } return GuiClose: Gui, main:Hide return notiGuiClose: Gui, noti:Destroy return 退出: viewGuiClose: ExitScript: DB.Close ExitApp r1: p1: r2: p2: r3: p3: r4: p4: r5: p5: r6: p6: lst := [1,2,3,4,5,6] Loop % lst.MaxIndex() { if (RegExReplace(A_ThisLabel,"^\w(\d)$","$1")=A_Index) GuiControl, main:, % "r" A_Index, 1 else GuiControl, main:, % "r" lst[A_Index], 0 } return ;WM_LButtonDOWN(wParam, lParam) ;{ ; ControlGetFocus, Focus, % maintitle ; if Focus = SysListView321 ; { ; change := 1 ; gosub, show_detail ; } ;} ;合并结果 append2obj(mainobj,obj){ if !IsObject(mainobj) mainobj := Object() id := [] for a,b in mainobj id[b[16]]:=1 for k,v in obj { if !id[v[16]] mainobj.Insert(v) } return mainobj } ;获取数组项目数 ObjKeyCount(obj){ for k in obj i++ return i } ;构造查询SQL函数 Query(SQL){ ;返回数组 global DB ;全局 if !DB.GetTable(SQL, Result) MsgBox, 16, SQLite错误: 获取结果, % "消息:`t" . DB.ErrorMsg . "`n代码:`t" . DB.ErrorCode if (Result.HasRows) { return Result.Rows } } ;构造执行SQL函数 Exec(SQL){ ;返回执行影响行数 global DB ;全局 if !DB.Exec(SQL) MsgBox, 16, SQLite错误: 获取结果, % "消息:`t" . DB.ErrorMsg . "`n代码:`t" . DB.ErrorCode return DB._Changes() }
取自:http://www.autohotkey.com/board/topic/30624-function-httpquery-get-and-post-requests-update-036
httpQuery(byref p1 = "", p2 = "", p3="", p4="") { ; v0.3.6 (w) Oct, 26 2010 by derRaphael / zLib-Style release ; currently the verbs showHeader, storeHeader, and updateSize are supported in httpQueryOps ; in case u need a different UserAgent, Proxy, ProxyByPass, Referrer, and AcceptType just ; specify them as global variables - mind the varname for referrer is httpQueryReferer [sic]. ; Also if any special dwFlags are needed such as INTERNET_FLAG_NO_AUTO_REDIRECT or cache ; handling this might be set using the httpQueryDwFlags variable as global global httpQueryOps, httpAgent, httpProxy, httpProxyByPass, httpQueryReferer, httpQueryAcceptType , httpQueryDwFlags ; Get any missing default Values ;v0.3.6 ; check for syntax if ( VarSetCapacity(p1) != 0 ) dreturn:=true, result := "", lpszUrl := p1, POSTDATA := p2, HEADERS := p3 else result := p1, lpszUrl := p2, POSTDATA := p3, HEADERS := p4 DefaultOps = (LTrim Join| httpAgent=AutoHotkeyScript|httpProxy=0|httpProxyByPass=0|INTERNET_FLAG_SECURE=0x00800000 SECURITY_FLAG_IGNORE_UNKNOWN_CA=0x00000100|SECURITY_FLAG_IGNORE_CERT_CN_INVALID=0x00001000 SECURITY_FLAG_IGNORE_CERT_DATE_INVALID=0x00002000|SECURITY_FLAG_IGNORE_CERT_WRONG_USAGE=0x00000200 INTERNET_OPEN_TYPE_PROXY=3|INTERNET_OPEN_TYPE_DIRECT=1|INTERNET_SERVICE_HTTP=3 ) Loop,Parse,DefaultOps,| { RegExMatch(A_LoopField,"(?P<Option>[^=]+)=(?P<Default>.*)",http) if StrLen(%httpOption%)=0 %httpOption% := httpDefault } ; Load Library hModule := DllCall("LoadLibrary", "Str", "WinINet.Dll") ; SetUpStructures for URL_COMPONENTS / needed for InternetCrackURL ; http://msdn.microsoft.com/en-us/library/aa385420(VS.85).aspx offset_name_length:= "4-lpszScheme-255|16-lpszHostName-1024|28-lpszUserName-1024|" . "36-lpszPassword-1024|44-lpszUrlPath-1024|52-lpszExtrainfo-1024" VarSetCapacity(URL_COMPONENTS,60,0) ; Struc Size ; Scheme Size ; Max Port Number NumPut(60,URL_COMPONENTS,0), NumPut(255,URL_COMPONENTS,12), NumPut(0xffff,URL_COMPONENTS,24) Loop,Parse,offset_name_length,| { RegExMatch(A_LoopField,"(?P<Offset>\d+)-(?P<Name>[a-zA-Z]+)-(?P<Size>\d+)",iCU_) VarSetCapacity(%iCU_Name%,iCU_Size,0) NumPut(&%iCU_Name%,URL_COMPONENTS,iCU_Offset) NumPut(iCU_Size,URL_COMPONENTS,iCU_Offset+4) } ; Split the given URL; extract scheme, user, pass, authotity (host), port, path, and query (extrainfo) ; http://msdn.microsoft.com/en-us/library/aa384376(VS.85).aspx DllCall("WinINet\InternetCrackUrlA","Str",lpszUrl,"uInt",StrLen(lpszUrl),"uInt",0,"uInt",&URL_COMPONENTS) ; Update variables to retrieve results Loop,Parse,offset_name_length,| { RegExMatch(A_LoopField,"-(?P<Name>[a-zA-Z]+)-",iCU_) VarSetCapacity(%iCU_Name%,-1) } nPort:=NumGet(URL_COMPONENTS,24,"uInt") ; Import any set dwFlags dwFlags := httpQueryDwFlags ; For some reasons using a selfsigned https certificates doesnt work ; such as an own webmin service - even though every security is turned off ; https with valid certificates works when if (lpszScheme = "https") dwFlags |= (INTERNET_FLAG_SECURE|SECURITY_FLAG_IGNORE_CERT_CN_INVALID |SECURITY_FLAG_IGNORE_CERT_WRONG_USAGE) ; Check for Header and drop exception if unknown or invalid URL if (lpszScheme="unknown") { Result := "ERR: No Valid URL supplied." return StrLen(Result) } ; Initialise httpQuery's use of the WinINet functions. ; http://msdn.microsoft.com/en-us/library/aa385096(VS.85).aspx hInternet := DllCall("WinINet\InternetOpenA" ,"Str",httpAgent,"UInt" ,(httpProxy != 0 ? INTERNET_OPEN_TYPE_PROXY : INTERNET_OPEN_TYPE_DIRECT ) ,"Str",httpProxy,"Str",httpProxyBypass,"Uint",0) ; Open HTTP session for the given URL ; http://msdn.microsoft.com/en-us/library/aa384363(VS.85).aspx hConnect := DllCall("WinINet\InternetConnectA" ,"uInt",hInternet,"Str",lpszHostname, "Int",nPort ,"Str",lpszUserName, "Str",lpszPassword,"uInt",INTERNET_SERVICE_HTTP ,"uInt",0,"uInt*",0) ; Do we POST? If so, check for header handling and set default if (StrLen(POSTDATA)>0) { HTTPVerb:="POST" if StrLen(Headers)=0 Headers:="Content-Type: application/x-www-form-urlencoded" } else ; otherwise mode must be GET - no header Defaults needed HTTPVerb:="GET" ; Form the request with proper HTTP protocol version and create the request handle ; http://msdn.microsoft.com/en-us/library/aa384233(VS.85).aspx hRequest := DllCall("WinINet\HttpOpenRequestA" ,"uInt",hConnect,"Str",HTTPVerb,"Str",lpszUrlPath . lpszExtrainfo ,"Str",ProVer := "HTTP/1.1", "Str",httpQueryReferer,"Str",httpQueryAcceptTypes ,"uInt",dwFlags,"uInt",Context:=0 ) ; Send the specified request to the server ; http://msdn.microsoft.com/en-us/library/aa384247(VS.85).aspx sRequest := DllCall("WinINet\HttpSendRequestA" , "uInt",hRequest,"Str",Headers, "uInt",StrLen(Headers) , "Str",POSTData,"uInt",StrLen(POSTData)) VarSetCapacity(header, 2048, 0) ; max 2K header data for httpResponseHeader VarSetCapacity(header_len, 4, 0) ; Check for returned server response-header (works only _after_ request been sent) ; http://msdn.microsoft.com/en-us/library/aa384238.aspx Loop, 5 if ((headerRequest:=DllCall("WinINet\HttpQueryInfoA","uint",hRequest ,"uint",21,"uint",&header,"uint",&header_len,"uint",0))=1) break if (headerRequest=1) { VarSetCapacity(res,headerLength:=NumGet(header_len),32) DllCall("RtlMoveMemory","uInt",&res,"uInt",&header,"uInt",headerLength) Loop,% headerLength if (*(&res-1+a_index)=0) ; Change binary zero to linefeed NumPut(Asc("`n"),res,a_index-1,"uChar") VarSetCapacity(res,-1) } else res := "timeout" ; Get 1st Line of Full Response Loop,Parse,res,`n,`r { RetValue := A_LoopField break } ; No Connection established - drop exception if (RetValue="timeout") { html := "Error: timeout" return -1 } ; Strip protocol version from return value RetValue := RegExReplace(RetValue,"HTTP/1\.[01]\s+") ; List taken from http://en.wikipedia.org/wiki/List_of_HTTP_status_codes HttpRetCodes := "100=continue|101=Switching Protocols|102=Processing (WebDAV) (RFC 2518)|" . "200=OK|201=Created|202=Accepted|203=Non-Authoritative Information|204=No" . " Content|205=Reset Content|206=Partial Content|207=Multi-Status (WebDAV)" . "|300=Multiple Choices|301=Moved Permanently|302=Found|303=See Other|304=" . "Not Modified|305=Use Proxy|306=Switch Proxy|307=Temporary Redirect|400=B" . "ad Request|401=Unauthorized|402=Payment Required|403=Forbidden|404=Not F" . "ound|405=Method Not Allowed|406=Not Acceptable|407=Proxy Authentication " . "Required|408=Request Timeout|409=Conflict|410=Gone|411=Length Required|4" . "12=Precondition Failed|413=Request Entity Too Large|414=Request-URI Too " . "Long|415=Unsupported Media Type|416=Requested Range Not Satisfiable|417=" . "Expectation Failed|418=I'm a teapot (RFC 2324)|422=UnProcessable Entity " . "(WebDAV) (RFC 4918)|423=Locked (WebDAV) (RFC 4918)|424=Failed Dependency" . " (WebDAV) (RFC 4918)|425=Unordered Collection (RFC 3648)|426=Upgrade Req" . "uired (RFC 2817)|449=Retry With|500=Internal Server Error|501=Not Implem" . "ented|502=Bad Gateway|503=Service Unavailable|504=Gateway Timeout|505=HT" . "TP Version Not Supported|506=Variant Also Negotiates (RFC 2295)|507=Insu" . "fficient Storage (WebDAV) (RFC 4918)|509=Bandwidth Limit Exceeded|510=No" . "t Extended (RFC 2774)" ; Gather numeric response value RetValue := SubStr(RetValue,1,3) ; Parse through return codes and set according informations Loop,Parse,HttpRetCodes,| { HttpreturnCode := SubStr(A_LoopField,1,3) ; Numeric return value see above HttpreturnMsg := SubStr(A_LoopField,5) ; link for additional information if (RetValue=HttpreturnCode) { RetMsg := HttpreturnMsg break } } ; Global HttpQueryOps handling if StrLen(HTTPQueryOps)>0 { ; Show full Header response (usefull for debugging) if (InStr(HTTPQueryOps,"showHeader")) MsgBox % res ; Save the full Header response in a global Variable if (InStr(HTTPQueryOps,"storeHeader")) global HttpQueryHeader := res ; Check for size updates to export to a global Var if (InStr(HTTPQueryOps,"updateSize")) { Loop,Parse,res,`n if RegExMatch(A_LoopField,"Content-Length:\s+?(?P<Size>\d+)",full) { global HttpQueryFullSize := fullSize break } if (fullSize+0=0) HttpQueryFullSize := "size unavailable" } } ; Check for valid codes and drop exception if suspicious if !(InStr("100 200 201 202 302",RetValue)) { Result := RetValue " " RetMsg return StrLen(Result) } VarSetCapacity(BytesRead,4,0) fsize := 0 Loop ; the receiver loop - rewritten in the need to enable { ; support for larger file downloads bc := A_Index VarSetCapacity(buffer%bc%,1024,0) ; setup new chunk for this receive round ReadFile := DllCall("wininet\InternetReadFile" ,"uInt",hRequest,"uInt",&buffer%bc%,"uInt",1024,"uInt",&BytesRead) ReadBytes := NumGet(BytesRead) ; how many bytes were received? if ((ReadFile!=0)&&(!ReadBytes)) ; we have had no error yet and received no more bytes break ; we must be done! so lets break the receiver loop else { fsize += ReadBytes ; sum up all chunk sizes for correct return size sizeArray .= ReadBytes "|" } if (InStr(HTTPQueryOps,"updateSize")) Global HttpQueryCurrentSize := fsize } sizeArray := SubStr(sizeArray,1,-1) ; trim last PipeChar VarSetCapacity( ( dReturn == true ) ? result : p1 ,fSize+1,0) ; reconstruct the result from above generated chunkblocks Dest := ( dreturn == true ) ? &result : &p1 ; to a our ByRef result variable Loop,Parse,SizeArray,| DllCall("RtlMoveMemory","uInt",Dest,"uInt",&buffer%A_Index%,"uInt",A_LoopField) , Dest += A_LoopField DllCall("WinINet\InternetCloseHandle", "uInt", hRequest) ; close all opened DllCall("WinINet\InternetCloseHandle", "uInt", hInternet) DllCall("WinINet\InternetCloseHandle", "uInt", hConnect) DllCall("FreeLibrary", "UInt", hModule) ; unload the library if ( dreturn == true ) { VarSetCapacity( result, -1 ) ErrorLevel := fSize return Result } else return fSize ; return the size - strings need update via VarSetCapacity(res,-1) }
说明:
这个函数有以下功能:
● 支持URL含有端口
● “用户名:密码@域名”格式的URL
● SSL(https)
● HTTP 报头信息 / Dumping(转储) / Storing(存储)
● 下载进度条界面
● 网络连接处理的标识 (自动跟踪特性等)
● 来源页支持
● 客户端接受到的MIME类型的支持
● 代理支持
● 超时支持
● 自定义浏览器UserAgent
使用方法很简单:
0.3.6版本引入另外一种语法与功能支持。从而简化了功能的使用。长话短说:
如果第一个参数不是空变量且包含有URL,httpquery会直接返回数据,消除了额外varsetcapacity的调用需要。然而旧的语法仍然是可用的和工作,所以使用此功能的脚本都需要进行改变。
记住,当处理二进制数据为压缩文件、下载可执行文件、或图片,我们将第一个参数为空值和第二包含URL。
使用新的语法:
html := httpQUERY(URL:="http://url") 将会返回获取到的html全文,长度为Errorlevel,使用的是GET方式,postparams(POST参数)长度为零。
html := httpQUERY(URL:="http://url",POSTDATA) 将会POST数据如果POSTDATA长度不为0
使用老的语法:
你需要定义一个变量将接收返回的数据缓存。所以VarSetCapacity(buffer,-1)释放内存是有必要的。
httpQUERY(buffer:="",URL) 将会返回长度,第一个参数将会缓存获取到的html全文,使用的是GET方式,postparams(POST参数)长度为零。
httpQUERY(buffer:="",URL,POSTDATA) 将会POST数据如果POSTDATA长度不为0
现在支持以下格式的URL:
<!– m –>http://username:pass… … s#fragment<!– m –>
Since httpQuery has been updated to use InternetCrackURL from winINet, all essential parts will be recognized. so there is no need to set up any additional parameters. Attention: When u need to authetificate in the Website the username / password attempt will not work. u have to submit those parameters via POST or GET method.
Additional Parameters:
To see a dump of the received httpHeaders, there is buildIn support for a global Variable named httpQueryOps. It may consist of one or more Verbs. For now "showHeader", "storeHeader", and "updateSize" verbs are supported. If You use storeHeader the complete Header will be saved in a variable named HttpQueryHeader which will be made global at runtime. The verb updateSize will make two variables globally Available: httpQueryFullSize and httpQueryCurrentSize. An usage example to show a download status indicator is included
以下变量进行全局:
httpAgent:UserAgent,默认是AutoHotkeyScript
httpProxy: 代理,default = 0
httpProxyByPass: 不使用代理的网址列表. default = 0
httpQueryReferer: 来源页
httpQueryAcceptType: 客户端接受到的MIME类型
httpQueryDwFlags: if in need for any special flags for the current connection this is the variable to set (example V shows an useCase for this feat)
示例1 POST数据
url := "http://thinkai.net/test.php" MsgBox, % httpQUERY(url,"a=1&b=我")
示例2 GET数据
url := "http://thinkai.net/test.php" MsgBox, % httpQUERY(url "?a=1&b=我")
示例3 下载文件并存储
#noenv data := "" URL := "http://www.autohotkey.net/programs/AutoHotkey104706.zip" httpQueryOps := "updateSize" SetTimer,showSize,10 length := httpQuery(data,URL) Tooltip if (write_bin(data,"ahk.exe",length)!=1) MsgBox "出错!" else MsgBox "ahk.zip"已保存! Return showSize: Tooltip,% HttpQueryCurrentSize "/" HttpQueryFullSize return GuiClose: GuiEscape: ExitApp write_bin(byref bin,filename,size){ h := DllCall("CreateFile","str",filename,"Uint",0x40000000 ,"Uint",0,"UInt",0,"UInt",4,"Uint",0,"UInt",0) IfEqual h,-1, SetEnv, ErrorLevel, -1 IfNotEqual ErrorLevel,0,ExitApp ; couldn't create the file r := DllCall("SetFilePointerEx","Uint",h,"Int64",0,"UInt *",p,"Int",0) IfEqual r,0, SetEnv, ErrorLevel, -3 IfNotEqual ErrorLevel,0, { t = %ErrorLevel% ; save ErrorLevel to be returned DllCall("CloseHandle", "Uint", h) ErrorLevel = %t% ; return seek error } result := DllCall("WriteFile","UInt",h,"Str",bin,"UInt" ,size,"UInt *",Written,"UInt",0) h := DllCall("CloseHandle", "Uint", h) return, 1 } #include httpQuery.ahk
示例4 上传图片到Imageshack使用官方(免费)API
; exmpl.imageshack.httpQuery.ahk ; This example uploads an image and constructs a multipart/form-data Type ; for fileuploading and returns the XML which is returned to show the stored Imagepath FileSelectFile,image FileGetSize,size,%image% SplitPath,image,OFN FileRead,img,%image% VarSetCapacity(placeholder,size,32) boundary := makeProperBoundary() post:="--" boundary "`ncontent-disposition: form-data; name=""MAX_FILE_SIZE""`n`n" . "1048576`n--" boundary "`ncontent-disposition: form-data; name=""xml""`n`nyes`n--" . boundary "`ncontent-disposition: form-data; name=""fileupload""; filename=""" . ofn """`nContent-type: " MimeType(img) "`nContent-Transfer-Encoding: binary`n`n" . placeholder "`n--" boundary "--" headers:="Content-type: multipart/form-data, boundary=" boundary "`nContent-Length: " strlen(post) DllCall("RtlMoveMemory","uInt",(offset:=&post+strlen(post)-strlen(Boundary)-size-5) ,"uInt",&img,"uInt",size) size := httpQuery(result:="","http://www.imageshack.us/index.php",post,headers) VarSetCapacity(result,-1) Gui,Add,Edit,w800 h600, % result Gui,Show return GuiClose: GuiEscape: ExitApp makeProperBoundary(){ Loop,26 n .= chr(64+a_index) n .= "0123456789" Loop,% StrLen(A_Now) { Random,rnd,1,% StrLen(n) Random,UL,0,1 b .= RegExReplace(SubStr(n,rnd,1),".$","$" (round(UL)? "U":"L") "0") } Return b } MimeType(ByRef Binary) { MimeTypes:="424d image/bmp|4749463 image/gif|ffd8ffe image/jpeg|89504e4 image/png|4657530" . " application/x-shockwave-flash|49492a0 image/tiff" @:="0123456789abcdef" Loop,8 hex .= substr(@,(*(a:=&Binary-1+a_index)>>4)+1,1) substr(@,((*a)&15)+1,1) Loop,Parse,MimeTypes,| if ((substr(hex,1,strlen(n:=RegExReplace(A_Loopfield,"\s.*"))))=n) Mime := RegExReplace(A_LoopField,".*?\s") Return (Mime!="") ? Mime : "application/octet-stream" } #include httpQuery.ahk
更多示例详见顶部来源
Clipboard := MD5("mypassword") MD5(string, encoding = "UTF-8") { return CalcStringHash(string, 0x8003, encoding) } ; CalcAddrHash ====================================================================== CalcAddrHash(addr, length, algid, byref hash = 0, byref hashlength = 0) { static h := [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, "a", "b", "c", "d", "e", "f"] static b := h.minIndex() hProv := hHash := o := "" if (DllCall("advapi32\CryptAcquireContext", "Ptr*", hProv, "Ptr", 0, "Ptr", 0, "UInt", 24, "UInt", 0xf0000000)) { if (DllCall("advapi32\CryptCreateHash", "Ptr", hProv, "UInt", algid, "UInt", 0, "UInt", 0, "Ptr*", hHash)) { if (DllCall("advapi32\CryptHashData", "Ptr", hHash, "Ptr", addr, "UInt", length, "UInt", 0)) { if (DllCall("advapi32\CryptGetHashParam", "Ptr", hHash, "UInt", 2, "Ptr", 0, "UInt*", hashlength, "UInt", 0)) { VarSetCapacity(hash, hashlength, 0) if (DllCall("advapi32\CryptGetHashParam", "Ptr", hHash, "UInt", 2, "Ptr", &hash, "UInt*", hashlength, "UInt", 0)) { loop % hashlength { v := NumGet(hash, A_Index - 1, "UChar") o .= h[(v >> 4) + b] h[(v & 0xf) + b] } } } } DllCall("advapi32\CryptDestroyHash", "Ptr", hHash) } DllCall("advapi32\CryptReleaseContext", "Ptr", hProv, "UInt", 0) } return o } ; CalcStringHash ==================================================================== CalcStringHash(string, algid, encoding = "UTF-8", byref hash = 0, byref hashlength = 0) { chrlength := (encoding = "CP1200" || encoding = "UTF-16") ? 2 : 1 length := (StrPut(string, encoding) - 1) * chrlength VarSetCapacity(data, length, 0) StrPut(string, &data, floor(length / chrlength), encoding) return CalcAddrHash(&data, length, algid, hash, hashlength) }
摘自HashCalc http://www.autohotkey.com/board/topic/86157-hashgenerator-with-verify-md2-md5-sha1-sha2-hmac
作者为
于 发表这个函数改自http://www.autohotkey.com/board/topic/101686-objectresponsetext-error/#entry667257
实现的是免文件读写下载网页代码
;示例1 POST数据 url := "http://thinkai.net/test.php" MsgBox, % URLDownloadToVar(url,"utf-8","POST","a=1&b=2") ;示例2 GET数据 url := "http://thinkai.net/test.php" MsgBox, % URLDownloadToVar(url "?config=设置","utf-8","GET") URLDownloadToVar(url, Encoding = "",Method="GET",postData=""){ ;网址,编码,请求方式,post数据 hObject:=ComObjCreate("WinHttp.WinHttpRequest.5.1") try { hObject.SetTimeouts(30000,30000,1200000,1200000) hObject.Open(Method,url,(Method="POST" ? True : False)) hObject.SetRequestHeader("Content-Type", "application/x-www-form-urlencoded") if IsObject(headers) { for k,v in headers hObject.SetRequestHeader(k, v) } hObject.Send(postData) if (Method="POST") hObject.WaitForResponse(-1) } catch e return -1 if (Encoding && hObject.ResponseBody) { oADO := ComObjCreate("adodb.stream") oADO.Type := 1 oADO.Mode := 3 oADO.Open() oADO.Write(hObject.ResponseBody) oADO.Position := 0 oADO.Type := 2 oADO.Charset := Encoding return oADO.ReadText(), oADO.Close() } return hObject.ResponseText }
50 queries in 1.538 seconds |