#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.

/*
###
###	IndexVFSC written by
###	Update notes:
### 17/02/16: Replaced FileAppend with FileOpen https://www.reddit.com/r/AutoHotkey/comments/462rpb/best_practice_for_handling_fileappend_errors_when/
### 16/02/16: Added A_USERNAME as a field. However, it appears that the FSC database overrides this field. Must write a script in EDMS to handle the RKYV_USER Field to go into Created By field before anything else.
### 19/02/16: Change WriteLine to Write to avoid extra lines being generated in .csv format.
### 08/11/16: update to EDMS
### 01/08/17: change directory from CSV1 to CSV11_1 (db 11 CSV 1)
### 30/10/17: changed funding type for STANDING ORDER to "STANDING ORDER" from "LOCAL AUTHORITY"
###	08/01/18: changed all three selections for STANDING ORDER to STANDING ORDER.
###
*/

;below is copyright notice for the RANDOM function used in this program.

/*
MERSENNE TWISTER ALGORITHIM
Copyright (C) 1997 - 2002, Makoto Matsumoto and Takuji Nishimura, All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
The names of its contributors may not be used to endorse or promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO,
THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS
BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE
GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Do NOT use for CRYPTOGRAPHY without securely hashing several returned values together, otherwise the generator state can be learned after reading 624 consecutive values.
*/

connection_string := ""

PayTypesFull := ADOSQL( connection_string ";coldelim=   `t", "
(
	SELECT PICKLISTVALUE
	FROM PICKLIST_CONDITIONAL
	WHERE PARENTID = 0
)")

Loop, parse, PayTypesFull, `n
	{
	if (A_LoopField = "Divisional" or A_LoopField = "LA" or A_LoopField = "Borough" or A_LoopField = "Schemes")
		{
		continue
		}
	PayTypes = %PayTypes%|%A_LoopField%
	}
StringReplace, PayTypes, PayTypes, |PICKLISTVALUE|

SchemesFull := ADOSQL( connection_string ";coldelim=   `t", "
(
	SELECT PICKLISTVALUE
	FROM PICKLIST_CONDITIONAL
	WHERE PARENTID = 11
)")

Loop, parse, SchemesFull, `n
	{
	Schemes = %Schemes%|%A_LoopField%
	}
StringReplace, Schemes, Schemes, |PICKLISTVALUE|

BorsFull := ADOSQL( connection_string ";coldelim=   `t", "
(
	SELECT PICKLISTVALUE
	FROM PICKLIST_CONDITIONAL
	WHERE PARENTID = 9
)")

Loop, parse, BorsFull, `n
	{
	Bors = %Bors%|%A_LoopField%
	}
StringReplace, Bors, Bors, |PICKLISTVALUE|

DivsFull := ADOSQL( connection_string ";coldelim=   `t", "
(
	SELECT PICKLISTVALUE
	FROM PICKLIST_CONDITIONAL
	WHERE PARENTID = 1640
)")

Loop, parse,DivsFull, `n
	{
	Divs = %Divs%|%A_LoopField%
	}
StringReplace, Divs, Divs, |PICKLISTVALUE|


FundLocsFull := ADOSQL( connection_string ";coldelim=   `t", "
(
	SELECT PICKLISTVALUE
	FROM PICKLIST_CONDITIONAL
	WHERE PARENTID = 1682
)")

Loop, parse,FundLocsFull, `n
	{
	FundLocs = %FundLocs%|%A_LoopField%
	}

connection_string := ""

DistribFull := ADOSQL( connection_string ";coldelim=   `t", "
(
	SELECT FULLNAME
	FROM USERS
)")

DistribFull := RegExreplace(DistribFull, " Date: [0-9]{14}")
DistribList = %A_SPACE%|

Loop, parse, DistribFull, `n
	{
	DistribList = %DistribList%|%A_LoopField%
	}

selectedfile = %1%
StringLen, LenSF, selectedfile
d = \
StringGetPos, LastSlash, selectedfile, %d% , R
LastSlash += 2
StringMid, selectedfilename, selectedfile, %LastSlash%
selectedtruename = %selectedfilename%

PaymentInv:
InvoiceTypeSelect = 1
gui, destroy
gui, add, text, Right w125, Reference No:
gui, add, text, Right w125, Payment Type:
gui, add, text, Right w125, Funding Type:
gui, add, text, Right w125, Funding Location:
gui, add, text, Right w125, Doc Date:
gui, add, text, Right w125, Academy Batch Number:
gui, add, text, Right w125, Total Value of Document:
gui, add, text, Right w125, Notes:
gui, add, Edit, ym vRefNo
gui, add, DropDownList, vPayType gPayTyping Sort w150, %PayTypes%
gui, add, Edit, vFundType
gui, add, DropDownList, vFundLoc Sort w200
gui, add, DateTime, vDocDate
gui, add, Edit, vAcadBatch
gui, add, Edit, vValofDoc
gui, add, Edit, vPayNotes w200 h50
Gui, add, Button, default gNextBit, Index
gui, add, button, gAcadInv, Change to Academy Invoice
gui, show
return

AcadInv:
InvoiceTypeSelect = 2
gui, Destroy
gui, add, text, Right w125, Repair No
gui, add, text, Right w125, Invoice Date
gui, add, text, Right w125, Division
gui, add, text, Right w125, Invoice Value £
gui, add, text, Right w125, Distribution
gui, add, text, Right w125, Notes:
gui, add, Edit, ym vRepNo
gui, add, DateTime, vADocDate
gui, add, DropDownList, vDivis Sort w200,
gui, add, Edit, vInvValue
gui, add, DropDownList, vDistrib Sort w200, %DistribList%
gui, add, Edit, vPayNotes w200 h50
Gui, add, Button, gNextBit, Index
gui, add, button, gPaymentInv, Change to Income Document
gui, show
return

GuiEscape:
GuiClose:
ExitApp

ReturnLoop:
gui, show
return

PayTyping:
GuiControlGet, PayType
GuiControl,, FundLoc, |
If PayType = Universal Credit
	{
	GuiControl,, FundType, Universal Credit
	GuiControl,, FundLoc, Universal Credit
	GuiControl, ChooseString , FundLoc, Universal Credit
	}
If (PayType = "Care Line Payments" or PayType = "Direct Debits" or PayType = "Giro" or PayType = "Housing Benefit" or PayType = "Rechargeable Repairs" or PayType = "DHP")
	{
	GuiControl,, FundType, Local Authority
	GuiControl,, FundLoc, %FundLocs%
	}
If PayType = Standing Orders
	{
	GuiControl,, FundType, Standing Order
	GuiControl,, FundLoc, Standing Order
	GuiControl, ChooseString, FundLoc, Standing Order
	}
If (PayType = "Cash Sheet - DIVISIONAL")
	{
	GuiControl,, FundType, Divisional
	GuiControl,, FundLoc, %Divs%
	}
If (PayType = "DSS")
	{
	GuiControl,, FundType, Department Working Pensions
	GuiControl,, FundLoc, DWP
	GuiControl, ChooseString, FundLoc, DWP
	}
If (PayType = "Supporting People")
	{
	GuiControl,, FundType, Borough
	GuiControl,, FundLoc, %Bors%
	}
If (PayType = "Cash Sheet - SCHEME")
	{
	GuiControl,, FundType, Schemes
	GuiControl,, FundLoc, %Schemes%
	}
If (PayType = "OTC")
	{
	GuiControl,, FundType, OTC
	GuiControl,, FundLoc, OTC
	GuiControl, ChooseString, FundLoc, OTC
	}
If (PayType = "Social Services")
	{
	GuiControl,, FundType, Social Services
	GuiControl,, FundLoc, Social Services
	GuiControl, ChooseString, FundLoc, Social Services
	}
If (PayType = "Court Costs")
	{
	GuiControl,, FundType, Court Costs
	GuiControl,, FundLoc, Court Costs
	GuiControl, ChooseString, FundLoc, Court Costs
	}
return

NextBit:
If InvoiceTypeSelect = 1
{
Gui, Submit, NoHide
If RefNo =
	{
	MsgBox, Enter a Reference Number
	goto ReturnLoop
	}
If PayType =
	{
	MsgBox, Enter a Payment Type
	goto ReturnLoop
	}
If FundType =
	{
	MsgBox, Enter a Funding Type
	goto ReturnLoop
	}
If FundLoc =
	{
	MsgBox, Enter a Funding Location
	goto ReturnLoop
	}
If AcadBatch =
	{
	MsgBox, Enter a Academy Batch Number
	goto ReturnLoop
	}
If ValofDoc =
	{
	MsgBox, Enter a Value of Documents
	goto ReturnLoop
	}
Gui, Destroy

StringMid, DocDate, DocDate, 1, 8
DocDate = %DocDate%000000

CopyRetries = 0

CopyLoop:
EndFileName = %A_NowUTC%_%selectedfilename%
FileCopy, %selectedfile%, ...\%EndFileName%, 0

If ErrorLevel > 0
	{
	Goto ErrorHandleFileCopy
	}

ReCopy:
fileObj := FileOpen("...DUMP.csv", "a-d")
load\CSV11_1\%selectedfilename%"`,%RefNo%`,%PayType%`,%FundType%`,%FundLoc%`,%DocDate%`,%AcadBatch%`,%ValofDoc%`,%PayNotes%`,%A_USERNAME%, ...DUMP.csv
fileObj.Write("`r`n""E:\Cherry\database\11\dataload\CSV11_1\" . EndFileName . """`," . RefNo "`," . PayType . "`," . FundType . "`," . FundLoc . "`," . DocDate . "`," AcadBatch . "`," . ValofDoc . "`," . PayNotes . "`," . A_USERNAME)
If ErrorLevel > 0
	{
	If CopyRetries > 9
		{
		Goto ErrorHandleCopy
		}
	Random, SleepTime, 100, 300
	Sleep %SleepTime%
	CopyRetries += 1
	Goto ReCopy
	}
MsgBox, %selectedtruename% indexed to FSC database

ExitApp
}


Gui, Submit, NoHide
If RepNo =
	{
	MsgBox, Enter a Repair Number
	goto ReturnLoop
	}
If ADocDate =
	{
	MsgBox, Select Document Date
	goto ReturnLoop
	}
If Divis =
	{
	MsgBox, Enter Division
	goto ReturnLoop
	}
If InvValue =
	{
	MsgBox, Enter Invoice Value
	goto ReturnLoop
	}

Gui, Destroy
StringMid, ADocDate, ADocDate, 1, 8
DocDate = %ADocDate%000000

CopyRetries = 0

ACopyLoop:
EndFileName = %A_NowUTC%_%selectedfilename%
FileCopy, %selectedfile%, ...%EndFileName%, 0
If ErrorLevel > 0
	{
	Goto AErrorHandleFileCopy
	}

CopyRetries = 0

AReCopy:
fileObj := FileOpen("...", "a-d")
fileObj.Write("`r`n""..." . EndFileName . """`," . RepNo "`," . ADocDate . "`," . Divis . "`," . InvValue . "`," . Distrib . "`," PayNotes . "`," . A_USERNAME)
If ErrorLevel > 0
	{
	If CopyRetries > 9
		{
		Goto AErrorHandleCopy
		}
	Random, SleepTime, 100, 300
	Sleep %SleepTime%
	CopyRetries += 1
	Goto AReCopy
	}
MsgBox, %selectedtruename% indexed to FSC database

ExitApp


ErrorHandleCopy:
MsgBox, Error, could not write to ..., please contact  for assistance.`nError Code: %A_LastError%
ExitApp

ErrorHandleFileCopy:
MsgBox, Error, could not write file to ... - please contact for assistance.`nError Code: %A_LastError%
ExitApp

AErrorHandleCopy:
MsgBox, Error, could not write to ..., please contact  for assistance.`nError Code: %A_LastError%
ExitApp

AErrorHandleFileCopy:
MsgBox, Error, could not write file to ... - please contact  for assistance.`nError Code: %A_LastError%
ExitApp

/*
###############################################################################################################
######                                      ADOSQL v5.04L                                                ######
###############################################################################################################

	Wraps the utility of ADODB to connect to a database, submit a query, and read the resulting recordset.
	Returns the result as a new object (or array of objects, if the query has multiple statements).
	To instead have this function return a string, include a delimiter option in the connection string.
*/

Global ADOSQL_LastError, ADOSQL_LastQuery ; These super-globals are for debugging your SQL queries.

ADOSQL( Connection_String, Query_Statement ) {
; Uses an ADODB object to connect to a database, submit a query and read the resulting recordset.
; By default, this function returns an object. If the query generates exactly one result set, the object is
; a 2-dimensional array containing that result (the first row contains the column names). Otherwise, the
; returned object is an array of all the results. To instead have this function return a string, append either
; ";RowDelim=`n" or ";ColDelim=`t" to the connection string (substitute your preferences for "`n" and "`t").
; If there is more than one table in the output string, they are separated by 3 consecutive row-delimiters.
; ErrorLevel is set to "Error" if ADODB is not available, or the COM error code if a COM error is encountered.
; Otherwise ErrorLevel is set to zero.

	coer := "", txtout := 0, rd := "`n", cd := "CSV", str := Connection_String ; 'str' is shorter.

; Examine the connection string for output formatting options.
	If ( 9 < oTbl := 9 + InStr( ";" str, ";RowDelim=" ) )
	{
		rd := SubStr( str, oTbl, 0 - oTbl + oRow := InStr( str ";", ";", 0, oTbl ) )
		str := SubStr( str, 1, oTbl - 11 ) SubStr( str, oRow )
		txtout := 1
	}
	If ( 9 < oTbl := 9 + InStr( ";" str, ";ColDelim=" ) )
	{
		cd := SubStr( str, oTbl, 0 - oTbl + oRow := InStr( str ";", ";", 0, oTbl ) )
		str := SubStr( str, 1, oTbl - 11 ) SubStr( str, oRow )
		txtout := 1
	}

	ComObjError( 0 ) ; We'll manage COM errors manually.

; Create a connection object. > http://www.w3schools.com/ado/ado_ref_connection.asp
; If something goes wrong here, return blank and set the error message.
	If !( oCon := ComObjCreate( "ADODB.Connection" ) )
		Return "", ComObjError( 1 ), ErrorLevel := "Error"
		, ADOSQL_LastError := "Fatal Error: ADODB is not available."


	oCon.ConnectionTimeout := 3 ; Allow 3 seconds to connect to the server.
	oCon.CursorLocation := 3 ; Use a client-side cursor server.
	oCon.CommandTimeout := 900 ; A generous 15 minute timeout on the actual SQL statement.
	oCon.Open( str ) ; open the connection.

; Execute the query statement and get the recordset. > http://www.w3schools.com/ado/ado_ref_recordset.asp
	If !( coer := A_LastError )
		oRec := oCon.execute( ADOSQL_LastQuery := Query_Statement )

	If !( coer := A_LastError ) ; The query executed OK, so examine the recordsets.
	{
		o3DA := [] ; This is a 3-dimensional array.
		While IsObject( oRec )
			If !oRec.State ; Recordset.State is zero if the recordset is closed, so we skip it.
				oRec := oRec.NextRecordset()
			Else ; A row-returning operation returns an open recordset
			{
				oFld := oRec.Fields
				o3DA.Insert( oTbl := [] )
				oTbl.Insert( oRow := [] )

				Loop % cols := oFld.Count ; Put the column names in the first row.
					oRow[ A_Index ] := oFld.Item( A_Index - 1 ).Name

				While !oRec.EOF ; While the record pointer is not at the end of the recordset...
				{
					oTbl.Insert( oRow := [] )
					oRow.SetCapacity( cols ) ; Might improve performance on huge tables??
					Loop % cols
						oRow[ A_Index ] := oFld.Item( A_Index - 1 ).Value
					oRec.MoveNext() ; move the record pointer to the next row of values
				}

				oRec := oRec.NextRecordset() ; Get the next recordset.
			}

		If (txtout) ; If the user wants plaintext output, copy the results into a string
		{
			Query_Statement := "x"
			Loop % o3DA.MaxIndex()
			{
				Query_Statement .= rd rd
				oTbl := o3DA[ A_Index ]
				Loop % oTbl.MaxIndex()
				{
					oRow := oTbl[ A_Index ]
					Loop % oRow.MaxIndex()
						If ( cd = "CSV" )
						{
							str := oRow[ A_Index ]
							StringReplace, str, str, ", "", A
							If !ErrorLevel || InStr( str, "," ) || InStr( str, rd )
								str := """" str """"
							Query_Statement .= ( A_Index = 1 ? rd : "," ) str
						}
						Else
							Query_Statement .= ( A_Index = 1 ? rd : cd ) oRow[ A_Index ]
				}
			}
			Query_Statement := SubStr( Query_Statement, 2 + 3 * StrLen( rd ) )
		}
	}
	Else ; Oh NOES!! Put a description of each error in 'ADOSQL_LastError'.
	{
		oErr := oCon.Errors ; > http://www.w3schools.com/ado/ado_ref_error.asp
		Query_Statement := "x"
		Loop % oErr.Count
		{
			oFld := oErr.Item( A_Index - 1 )
			str := oFld.Description
			Query_Statement .= "`n`n" SubStr( str, 1 + InStr( str, "]", 0, 2 + InStr( str, "][", 0, 0 ) ) )
				. "`n   Number: " oFld.Number
				. ", NativeError: " oFld.NativeError
				. ", Source: " oFld.Source
				. ", SQLState: " oFld.SQLState
		}
		ADOSQL_LastError := SubStr( Query_Statement, 4 )
		Query_Statement := ""
		txtout := 1
	}

; Close the connection and return the result. Local objects are cleaned up as the function returns.
	oCon.Close()
	ComObjError( 1 )
	ErrorLevel := coer
	Return txtout ? Query_Statement : o3DA.MaxIndex() = 1 ? o3DA[1] : o3DA
} ; END - ADOSQL( Connection_String, Query_Statement )