Avoids slow SQL queries by using aggregated data

Local Table Explorer.ahk

;*******************************************************
; Want a clear path for learning AutoHotkey; Take a look at our AutoHotkey Udemy courses.  They're structured in a way to make learning AHK EASY
; Right now you can  get a coupon code here: https://the-Automator.com/Learn
;*******************************************************
; http://www.autohotkey.com/board/topic/76147-search-specific-columns-of-list-view/
#SingleInstance,Force
#NoEnv
#MaxThreads, 1 ; when this is 1 it doesn't repeat list.  I think this is cause when I have more than one it continues even thoug the list isn't entirely read
SetBatchLines -1 
SplitPath,A_ahkPath,,Root
Menu, Tray, Add, Reset, Reset
Menu, Tray, Add
Menu, Tray, Default, Reset
Delimiter:={csv:",",tsv:"`t",txt:"`t"}
global settings:=new XML("settings")

;***********************Build Gui********************************.
Gui,+Resize +MinSize +hwndMain
Gui, Font, s12, Arial
Gui, Add, Text, x10 y8, Search
Gui, Add, Edit, x+10 yp-2 w205 vInputSearch gFind,
Gui, Add, Button, x+10 yp-1 w70 h25 Default gManualFind, Search
Gui, Add, Button, x+10 yp+0 w70 h25 gReset, Reset
Gui, Add, Text, x+20 y8 Section, Table
Gui,Add,StatusBar
; list out tables to query
MainObject:=[],NewListObject:=[]
Loop,Files,Tables\*.* ;******************Put your files in this folder****************
{
	SplitPath,A_LoopFileName,File
	list.=file "|"
	FileRead,info,%A_LoopFileFullPath%
	MainObject[file]:=info
}
Gui,Add,DropDownList, x+5 w550 hwndChoiceDDL gTableChoice vChoice, %list%
Gui,Add,Text,xm y+M,Search Column:
Gui,Add,DDL,x+M vSC hwndSearchColumn AltSubmit
Gui,Add,Text,x+m,Return Column:
Gui,Add,DDL,x+M vRC hwndReturnColumn AltSubmit gReset
Gui,Add,Text,x+M,Prefix:
Gui,Add,Edit,x+M vPre w50 gUpdateFix hwndPrefixHWND,
Gui,Add,Text,x+M,Suffix:
Gui,Add,Edit,x+M vSuffix w50 gUpdateFix hwndSuffixHWND,`,
Gui,Add,Text,x+M,Trim:
Gui,Add,Edit,x+M vTrim w50 gUpdateFix hwndTrimHWND,`,
Gui,Add,Button,xm gCopy,Copy To Clipboard
Gui,Add,Button,x+M gMultiple,Add Selected Values
Gui,Add,ListView,grid xm y+5 r5 w1050 h500 CountLine%A_Index% gMyListView hwndMyListView, Row|Product|ID ;Display list
GuiControl,1:ChooseString,%ChoiceDDL%,% (Choose:=Settings.SSN("//Last/@Table").text)
DetectHiddenWindows,On
Gui,Show
pos:=WinPos(main),Offset:=[]
for a,b in {ChoiceDDL:ChoiceDDL,MyListView:MyListView}{
	ControlGetPos,x,y,w,h,,ahk_id%b%
	Offset[a]:={hwnd:b,w:w-pos.w,h:(a="MyListView"?h-pos.h:"")}
}
Gui,Show,% settings.Get("//gui","Center"), Table Explorer 4.1
if(settings.SSN("//gui/@max").text){
	WinMaximize,ahk_id%main%
	Gosub,GuiSize
}

if(Choose){
	GoSub,TableChoice
	AutoWidth()
}
return
GuiSize:
position:=WinPos(main)
for a,b in Offset{
	pos:=""
	if(b.w)
		pos.=" w" position.w+b.w
	if(b.h)
		pos.=" h" position.h+b.h
	GuiControl,1:Move,% b.hwnd,%pos%
}
return
+Escape::
GuiClose:
pos:=WinPos(main)
Gui,Submit
WinGet,MinMax,MinMax,ahk_id%main%
if(MinMax){
	settings.Add("gui",{max:1})
}else{
	node:=settings.Add("gui",,pos.text),node.RemoveAttribute("max")
}
Settings.Add("Last",{Table:Choice})
Settings.Save(1)
ExitApp
return
;*******************************************************.
;*******When the dropdown is chosen for the table- this loops through them********************************.
TableChoice:
Gosub,Reset
Gui,Submit,Nohide
ea:=XML.EA(Settings.Find("//Setting/@File",Choice))
for a,b in {(PrefixHWND):(ea.Prefix?ea.Prefix:""),(SuffixHWND):(ea.Suffix?ea.Suffix:","),(TrimHWND):(ea.Trim?ea.Trim:",")}
	GuiControl,1:,%a%,%b%
Value:="", NewList2:="", NewList:="" Line:="" ;clearing variable values
File_1:=MainObject[Choice]

;*******Adjusting from file read as now it is a variable********************************.

CurrentObject:=[]
SplitPath,Choice,,,ext
for a,b in StrSplit(File_1,"`n","`r"){
	if(InStr(b,Chr(34))&&ext="CSV"){
		obj:=[]
		if(ext="CSV"){
			Loop,Parse,b,CSV
				obj.Push(A_LoopField)
			CurrentObject.Push(obj)
		}
	}else
		CurrentObject.Push(StrSplit(b,Delimiter[ext]))
}
GoSub ShowInList
return

;*********Running search button click******************************************.
ManualFind:
Find: ;Run the search here
Gui, Submit, Nohide
if(StrLen(InputSearch)<3&&A_ThisLabel="Find")
	return
LV_Delete()
GuiControl,1:-Redraw,SysListView321
for a,b in CurrentObject
	if(InStr(b[SC],InputSearch))
		LV_Add("",b*)
AutoWidth()
GuiControl,1:+Redraw,SysListView321
node:=settings.Find("//Setting/@File",Choice)
for a,b in {SearchColumn:SC,ReturnColumn:RC}
	node.SetAttribute(a,b)
return

;*********************Show first list of values**********************************
ShowInList:
LV_Delete(),columns:=""
GuiControl,1:-Redraw,SysListView321
if(!node:=settings.Find("//Setting/@File",Choice))
	node:=settings.Add("Setting",{File:Choice},,1)
Loop,% LV_GetCount("Column")
	LV_DeleteCol(1)
for a,b in CurrentObject{
	if(A_Index=1){
		for c,d in b
			LV_ModifyCol(A_Index,"",d),columns.="|" d,LV_InsertCol(A_Index,"",d)
		GuiControl,1:,%SearchColumn%,%columns%
		GuiControl,1:,%ReturnColumn%,%columns%
		value:=((value:=SSN(node,"@SearchColumn").text)?value:1)
		GuiControl,1:Choose,%SearchColumn%,%value%
		value:=((value:=SSN(node,"@ReturnColumn").text)?value:1)
		GuiControl,Choose,%ReturnColumn%,%value%
	}else
		LV_Add("",b*)
}
AutoWidth()
GuiControl,1:+Redraw,SysListView321
return

;***********************Double click- ********************************.
MyListView:
Gui,Submit,Nohide
if(A_GuiEvent="DoubleClick"){
	LV_GetText(Value,A_EventInfo,RC)
	if(!Value){
		m("This value is blank","time:1")
		return
	}if(!NewListObject[Value])
		NewList.=Pre Value Suffix,NewListObject[Value]:=1
	else
		m("Value already in list","time:1")
}
SB_SetText(NewList)
return

;***********************Reset********************************.
Reset:
NewList:="",NewListObject:=[]
SB_SetText("")
return

m(x*){
	active:=WinActive("A")
	ControlGetFocus,Focus,A
	ControlGet,hwnd,hwnd,,%Focus%,ahk_id%active%
	static list:={btn:{oc:1,ari:2,ync:3,yn:4,rc:5,ctc:6},ico:{"x":16,"?":32,"!":48,"i":64}},msg:=[],msgbox
	list.title:="Table Explorer 4.1",list.def:=0,list.time:=0,value:=0,msgbox:=1,txt:=""
	for a,b in x
		obj:=StrSplit(b,":"),(vv:=List[obj.1,obj.2])?(value+=vv):(list[obj.1]!="")?(List[obj.1]:=obj.2):txt.=b "`n"
	msg:={option:value+262144+(list.def?(list.def-1)*256:0),title:list.title,time:list.time,txt:txt}
	Sleep,120
	MsgBox,% msg.option,% msg.title,% msg.txt,% msg.time
	msgbox:=0
	for a,b in {OK:value?"OK":"",Yes:"YES",No:"NO",Cancel:"CANCEL",Retry:"RETRY"}
		IfMsgBox,%a%
		{
			WinActivate,ahk_id%active%
			ControlFocus,%Focus%,ahk_id%active%
			return b
		}
}
t(x*){
	for a,b in x{
		if((obj:=StrSplit(b,":")).1="time"){
			SetTimer,killtip,% "-" obj.2*1000
			Continue
		}
		list.=b "`n"
	}
	Tooltip,% list
	return
	killtip:
	ToolTip
	return
}
Copy:
Gui,Submit,Nohide
if(Trim)
	Clipboard:=Trim(NewList,Trim)
else
	Clipboard:=NewList
m(Clipboard)
return
AutoWidth(){
	Loop,% LV_GetCount("Column")
		LV_ModifyCol(A_Index,"AutoHDR")
}
Class XML{
	keep:=[]
	__New(param*){
		if(!FileExist(A_ScriptDir "\lib"))
			FileCreateDir,%A_ScriptDir%\lib
		root:=param.1,file:=param.2,file:=file?file:root ".xml",temp:=ComObjCreate("MSXML2.DOMDocument"),temp.setProperty("SelectionLanguage","XPath"),this.xml:=temp,this.file:=file,xml.keep[root]:=this
		;temp.preserveWhiteSpace:=1
		if(FileExist(file)){
			FileRead,info,%file%
			if(info=""){
				this.xml:=this.CreateElement(temp,root)
				FileDelete,%file%
			}else
				temp.LoadXML(info),this.xml:=temp
		}else
			this.xml:=this.CreateElement(temp,root)
	}CreateElement(doc,root){
		return doc.AppendChild(this.xml.CreateElement(root)).ParentNode
	}Add(path,att:="",text:="",dup:=0){
		p:="/",add:=(next:=this.SSN("//" path))?1:0,last:=SubStr(path,InStr(path,"/",0,0)+1)
		if(!next.xml){
			next:=this.SSN("//*")
			for a,b in StrSplit(path,"/")
				p.="/" b,next:=(x:=this.SSN(p))?x:next.AppendChild(this.xml.CreateElement(b))
		}if(dup&&add)
			next:=next.ParentNode.AppendChild(this.xml.CreateElement(last))
		for a,b in att
			next.SetAttribute(a,b)
		next.text:=text
		return next
	}
	Find(info*){
		static last:=[]
		doc:=info.1.NodeName?info.1:this.xml
		if(info.1.NodeName)
			node:=info.2,find:=info.3,return:=info.4!=""?"SelectNodes":"SelectSingleNode",search:=info.4
		else
			node:=info.1,find:=info.2,return:=info.3!=""?"SelectNodes":"SelectSingleNode",search:=info.3
		if(InStr(info.2,"descendant"))
			last.1:=info.1,last.2:=info.2,last.3:=info.3,last.4:=info.4
		if(InStr(find,"'"))
			return doc[return](node "[.=concat('" RegExReplace(find,"'","'," Chr(34) "'" Chr(34) ",'") "')]/.." (search?"/" search:""))
		else
			return doc[return](node "[.='" find "']/.." (search?"/" search:""))
	}
	Under(under,node,att:="",text:="",list:=""){
		new:=under.AppendChild(this.xml.CreateElement(node)),new.text:=text
		for a,b in att
			new.SetAttribute(a,b)
		for a,b in StrSplit(list,",")
			new.SetAttribute(b,att[b])
		return new
	}ReCreate(path,new:=""){
		new:=new?new:path,rem:=this.SSN(path),rem.ParentNode.RemoveChild(rem),new:=this.Add(new)
		return new
	}SSN(path){
		return this.xml.SelectSingleNode(path)
	}SN(path){
		return this.xml.SelectNodes(path)
	}__Get(x=""){
		return this.xml.xml
	}Get(Path,Default){
		text:=this.SSN(path).text
		return text?text:Default
	}Transform(){
		static
		if(!IsObject(xsl))
			xsl:=ComObjCreate("MSXML2.DOMDocument"),xsl.loadXML("<xsl:stylesheet version=""1.0"" xmlns:xsl=""http://www.w3.org/1999/XSL/Transform""><xsl:output method=""xml"" indent=""yes"" encoding=""UTF-8""/><xsl:template match=""@*|node()""><xsl:copy>`n<xsl:apply-templates select=""@*|node()""/><xsl:for-each select=""@*""><xsl:text></xsl:text></xsl:for-each></xsl:copy>`n</xsl:template>`n</xsl:stylesheet>"),style:=null
		this.xml.transformNodeToObject(xsl,this.xml)
	}Save(x*){
		if(x.1=1)
			this.Transform()
		if(this.xml.SelectSingleNode("*").xml="")
			return m("Errors happened while trying to save " this.file ". Reverting to old version of the XML")
		filename:=this.file?this.file:x.1.1,ff:=FileOpen(filename,0),text:=ff.Read(ff.length),ff.Close()
		if(!this[])
			return m("Error saving the " this.file " xml.  Please get in touch with maestrith if this happens often")
		if(text!=this[])
			file:=FileOpen(filename,"rw"),file.seek(0),file.write(this[]),file.length(file.position)
	}EA(path,att:=""){
		list:=[]
		if(att)
			return path.NodeName?SSN(path,"@" att).text:this.SSN(path "/@" att).text
		nodes:=path.NodeName?path.SelectNodes("@*"):nodes:=this.SN(path "/@*")
		while,n:=nodes.item(A_Index-1)
			list[n.NodeName]:=n.text
		return list
}}
SSN(node,path){
	return node.SelectSingleNode(path)
}
SN(node,path){
	return node.SelectNodes(path)
}
UpdateFix:
Gui,Submit,Nohide
node:=Settings.Find("//Setting/@File",Choice)
for a,b in {Prefix:Pre,Suffix:Suffix,Trim:Trim}
	node.SetAttribute(a,b)
return
Multiple:
Gui,Submit,Nohide
next:=0
while(next:=LV_GetNext(next)){
	LV_GetText(Value,next,RC)
	if(!NewListObject[Value]&&Value)
		NewList.=Pre Value Suffix,NewListObject[Value]:=1
}SB_SetText(NewList)
return
class GUIKeep{
	static table:=[],showlist:=[]
	__New(win,parent:=""){
		DetectHiddenWindows,On
		Gui,%win%:Destroy
		Gui,%win%:+hwndhwnd
		this.xml:=new XML("gui")
		for a,b in {border:A_OSVersion~="^10"?3:0,caption:DllCall("GetSystemMetrics",int,4)}
			this[a]:=b
		this.gui:=[],this.sc:=[],this.hwnd:=hwnd,this.con:=[],this.ahkid:=this.id:="ahk_id" hwnd,this.win:=win,this.Table[win]:=this,this.var:=[]
		Gui,%win%:+LabelGUIKeep.
		this.win:=win
		Gui,%win%:Default
	}
	DropFiles(filelist,ctrl,x,y){
		df:="DropFiles"
		if(IsFunc(df))
			%df%(filelist,ctrl,x,y)
	}
	Add(info*){
		static
		if(!info.1){
			var:=[]
			Gui,% this.win ":Submit",Nohide
			for a in this.var
				var[a]:=%a%
			return var
		}
		for a,b in info{
			i:=StrSplit(b,","),newpos:=""
			Gui,% this.win ":Add",% i.1,% i.2 " hwndhwnd",% i.3
			if(RegExMatch(i.2,"U)\bv(.*)\b",var))
				this.var[var1]:=1
			this.con[hwnd]:=[]
			if(i.5)
				this.xml.Add("control",{name:i.5,hwnd:hwnd})
			if(i.4!="")
				this.con[hwnd,"pos"]:=i.4,this.resize:=1
		}
	}
	Escape(){
		this:=GUIKeep.table[A_Gui]
		KeyWait,Escape,U
		if(IsFunc(func:=A_Gui "Escape"))
			return %func%()
		else if(IsLabel(label:=A_Gui "Escape"))
			SetTimer,%label%,-1
		else
			this.savepos(),this.exit()
	}
	savepos(){
		if(!top:=settings.ssn("//gui/position[@window='" this.win "']"))
			top:=settings.add("gui/position",,,1),top.SetAttribute("window",this.win)
		top.text:=this.winpos().text
	}
	Exit(){
		global x
		this.savepos(),x.activate()
		WinGet,pid,pid,ahk_id%A_ScriptHwnd%
		Process,Close,%pid%
		ExitApp
	}
	Close(a:=""){
		this:=GUIKeep.table[A_Gui]
		if(IsFunc(func:=A_Gui "Close"))
			return %func%()
		else if(IsLabel(label:=A_Gui "Close"))
			SetTimer,%label%,-1
		else
			this.savepos(),this.exit()
	}
	Size(){
		this:=GUIKeep.table[A_Gui],pos:=this.winpos()
		for a,b in this.gui
			for c,d in b
				GuiControl,% this.win ":MoveDraw",%a%,% c (c~="y|h"?pos.h:pos.w)+d
	}
	Show(name){
		this.getpos(),pos:=this.resize=1?"":"AutoSize",this.name:=name
		if(this.resize=1)
			Gui,% this.win ":+Resize"
		GUIKeep.showlist.push(this)
		SetTimer,guikeepshow,-100
		return
		GUIKeepShow:
		while,this:=GUIKeep.Showlist.pop(){
			Gui,% this.win ":Show",% settings.ssn("//gui/position[@window='" this.win "']").text " " pos,% this.name
			this.size()
			if(this.resize!=1)
				Gui,% this.win ":Show",AutoSize
			WinActivate,% this.id
		}
		return
	}
	__Get(){
		return this.add()
	}
	GetPos(){
		Gui,% this.win ":Show",AutoSize Hide
		WinGet,cl,ControlListHWND,% this.ahkid
		pos:=this.winpos(),ww:=pos.w,wh:=pos.h,flip:={x:"ww",y:"wh"}
		for index,hwnd in StrSplit(cl,"`n"){
			obj:=this.gui[hwnd]:=[]
			ControlGetPos,x,y,w,h,,ahk_id%hwnd%
			for c,d in StrSplit(this.con[hwnd].pos)
				d~="w|h"?(obj[d]:=%d%-w%d%):d~="x|y"?(obj[d]:=%d%-(d="y"?wh+this.Caption+this.Border:ww+this.Border))
		}
		Gui,% this.win ":+MinSize"
	}
	WinPos(){
		VarSetCapacity(rect,16),DllCall("GetClientRect",ptr,this.hwnd,ptr,&rect)
		WinGetPos,x,y,,,% this.ahkid
		w:=NumGet(rect,8),h:=NumGet(rect,12),text:=(x!=""&&y!=""&&w!=""&&h!="")?"x" x " y" y " w" w " h" h:""
		return {x:x,y:y,w:w,h:h,text:text}
	}
}
WinPos(hwnd){
	VarSetCapacity(rect,16),DllCall("GetClientRect",ptr,hwnd,ptr,&rect)
	WinGetPos,x,y,,,% "ahk_id" hwnd
	w:=NumGet(rect,8),h:=NumGet(rect,12),text:=(x!=""&&y!=""&&w!=""&&h!="")?"x" x " y" y " w" w " h" h:""
	return {x:x,y:y,w:w,h:h,text:text}
}