Code: Select all
#SingleInstance, Force
#KeyHistory, 0
SetBatchLines, -1
ListLines, Off
SendMode Input ; Forces Send and SendRaw to use SendInput buffering for speed.
SetTitleMatchMode, 2
SetWorkingDir, %A_ScriptDir%
#MaxThreadsPerHotkey, 1 ; no re-entrant hotkey handling
#Include includes\ScrollGUI.ahk
;GLOBAL Desjardins := { csv: { debit: { date_col_num:4
;,description_col_num:6
;,retrait_col_num:8
;,depot_col_num:9 } } }
GLOBAL Desjardins := { csv: { date_col_num:4
,description_col_num:6
,retrait_col_num:8
,depot_col_num:9 } }
GLOBAL TD := { csv: { date_col_num:1
,description_col_num:2
,retrait_col_num:3
,depot_col_num:4 }}
GLOBAL RBC := { csv: { date_col_num:3
,description_col_num:[5,6]
,retrait_col_num:7
,depot_col_num:7 }}
GLOBAL BMO := { csv: { date_col_num:3
,description_col_num:5
,retrait_col_num:4
,depot_col_num:4 }}
/* todo
GLOBAL BanqueNat := { csv: { date_col_num:3
,description_col_num:5
,retrait_col_num:4
,depot_col_num:4 }}
*/
GLOBAL gui_column_names:={row:"RowCheckbox_"
, date:"Dt_"
, description:"Ds_"
, retrait:"Rt_"
, depot:"Dp_"
, nom:"Nm_"
, categorie:"Ct_"
, taxe:"Tx_"
, tps:"Ts_"
, tvq:"Tq_"
, note:"Nt_"}
GLOBAL TPS100_:=0.05
, TVQ100_:=0.09975
, TVH100_ONTARIO:=0.08
; Import UI
Gui, ImportUI:New
Gui, Font, s16 q5
Gui, Add, Button, % "x+m h35 center gSelectFileForImport +Default", Importer mon relevé
banqueList := "Desjardins|TD|BanqueNat|RBC|BMO||"
Gui, Add, ComboBox, % "x+0 yp+1 center w170 vbanque gCBMatchingGUICreationHandler"
, % banqueList
Gui, Add, ComboBox, % "x+0 yp center w140 vtype_releve gCBMatchingGUICreationHandler"
, Carte débit||Carte crédit
Gui, Show,, Importer un relevé bancaire
GuiControl, Focus, banque
OnMessage(0x111, "OnWM_COMMAND")
return
ImportUIGuiClose:
ImportUIGuiEscape:
ExitApp
;--------------------------------------------------------------------------------
SelectFileForImport() {
;--------------------------------------------------------------------------------
Global Transactions, banque, banqueList
GuiControlGet, banque, ImportUI: ; get the ComboBox choice
if (!banque) {
MsgBox % "Le choix d'institution semble être vide."
. "`n`nSvp, assurez-vous d'avoir sélectionné une institution financière de la liste."
return
}
For index, value in StrSplit(banqueList,"|") {
if (!value)
Continue
if (banque = value)
is_banque_in_list := True
}
if (!is_banque_in_list) {
MsgBox % "Le choix d'institution semble être invalide."
. "`n`nSvp, assurez-vous d'avoir sélectionné une institution financière de la liste."
return
}
FileSelectFile, selected_file,,,, *.csv
if (!selected_file or !FileExist(selected_file))
return
Progress, HWNDhImportProgress w500 h80, % "Conversion du relevé en objet", % "Finance D - import de relevé bancaire", % "Creation de la fenêtre"
Progress, Show
SplitPath, selected_file,,, file_extension
if (file_extension = "csv") {
CSV_object := CSV_to_object(selected_file)
}
else {
MsgBox % "L'extension du fichier n'est pas encore prit en charge.`n`nSvp, choisir un fichier au format .csv"
Progress, Hide
return
}
Transactions := Object_to_Transactions(CSV_object, banque)
if (Transactions) {
CreateDataGridUI(selected_file, Transactions)
}
Progress, Hide
}
;--------------------------------------------------------------------------------
CSV_to_object(ByRef file) {
;--------------------------------------------------------------------------------
Global progress_percent_1
if (!FileExist(file))
return
FileRead, csv_content, %file%
if (!csv_content) {
MsgBox % "Aucun contenu trouvé dans ce fichier.`n`n"file
return
}
; create the object that will be returned at the end of function
csv_as_2d_object := {}
csv_as_2d_object.SetCapacity(500)
csv_lines_array := StrSplit(csv_content, "`n", "`r")
csv_lines_count := csv_lines_array.Count()
For, index, content in csv_lines_array
{
progress_percent_1 := ((index * 100) / csv_lines_count) / 3
Progress, % progress_percent_1 ; Move the bar upward by percent.
if (!content)
Continue ; skip empty row
row_num++
Loop, Parse, content, CSV ; built-in AHK CSV parsing module
{
csv_as_2d_object[row_num, A_index] := A_LoopField
}
}
return csv_as_2d_object
}
;--------------------------------------------------------------------------------
Object_to_Transactions(ByRef CSV_object, ByRef banque) {
;--------------------------------------------------------------------------------
Global progress_percent_1, progress_percent_2
should_separate_retrait_from_depot_on_same_column := False
if (%banque%["csv","retrait_col_num"] = %banque%["csv","depot_col_num"])
should_separate_retrait_from_depot_on_same_column := True
; create the object that will be returned at the end of function
transactions := {}
transactions.SetCapacity(500)
transaction_row:=0
csv_lines_count := CSV_object.Count()
for index in CSV_object
{
progress_percent_2 := progress_percent_1 + (((index * 100) / csv_lines_count) / 2.6)
Progress, % progress_percent_2 ; Move the bar upward by percent.
;MsgBox % "make transactions " progress_percent_2
if (!RegExMatch(CSV_object[index, %banque%["csv","date_col_num"]], "^\d"))
continue ; skips csv index which does not start with a digit for the date column
transaction_row++
; Date
transactions[transaction_row,"date"] := CSV_object[index, %banque%["csv","date_col_num"]]
; Description (can handle object of multiple columns)
if (IsObject(%banque%["csv","description_col_num"])) {
description_str := ""
For i in %banque%["csv","description_col_num"] {
description_str .= CSV_object[index, %banque%["csv","description_col_num", i]] . " "
}
} else {
description_str := CSV_object[index, %banque%["csv","description_col_num"]]
}
formated_whitespaces_description_str := Trim(RegExReplace(description_str, "S)\s+", A_Space))
;capitalized_description_str := Format("{:T}", formated_whitespaces_description_str)
transactions[transaction_row,"description"] := formated_whitespaces_description_str
; Retrait et Depot
if (should_separate_retrait_from_depot_on_same_column) {
; Retrait
if (CSV_object[index, %banque%["csv","retrait_col_num"]] > 0)
transactions[transaction_row,"retrait"] := ""
else
transactions[transaction_row,"retrait"] := StrReplace(CSV_object[index, %banque%["csv","retrait_col_num"]], "-")
; Depot
if (CSV_object[index, %banque%["csv","depot_col_num"]] < 0)
transactions[transaction_row,"depot"] := ""
else
transactions[transaction_row,"depot"] := CSV_object[index, %banque%["csv","depot_col_num"]]
} else {
; Retrait
transactions[transaction_row,"retrait"] := CSV_object[index, %banque%["csv","retrait_col_num"]]
; Depot
transactions[transaction_row,"depot"] := CSV_object[index, %banque%["csv","depot_col_num"]]
}
}
return transactions
}
;--------------------------------------------------------------------------------
print_transaction_row(ByRef Transactions, row) {
;--------------------------------------------------------------------------------
; just for testing outputs
MsgBox % "Transaction #1`n`n" . "Le " Transactions[row, "date"]
. "`ndescription: " Transactions[row, "description"]
. "`nretrait: " Transactions[row, "retrait"]
. "`ndepot: " Transactions[row, "depot"]
}
;--------------------------------------------------------------------------------
CreateDataGridUI(ByRef filePath, ByRef Transactions) {
;--------------------------------------------------------------------------------
GLOBAL
; Main UI SETTINGS
SysGet, VirtualSize, MonitorWorkArea
Gui, hGuiMain:New,+HwndhGuiMain +OwnDialogs +MinSize925x550 +MaxSize%VirtualSizeRight%x%VirtualSizeBottom%
GW:=VirtualSizeRight, GH:=VirtualSizeBottom ; gui size
Gui, Font, s14 q5 norm ; sets default font
Gui, Add, Button, gSageExportButton x41 y5, % "Exporter les transactions " banque " pour Sage50"
CbNomList=
(Join|
ALEXANDRE DESROCHES
DANIEL DESROCHES
)
TPS100_ToString:=RegExReplace(TPS100_ * 100, ".000000")
TVQ100_ToString:=RegExReplace(TVQ100_ * 100, "000")
TVH100_ONTARIO_ToString:=RegExReplace(TVH100_ONTARIO * 100, ".000000")
TxListLong=
(Join|
QC (TPS %TPS100_ToString%`% + TVQ 9.975`%)
ON (TPS %TPS100_ToString%`% + TVH %TVH100_ONTARIO_ToString%`%)
CANADA (TPS %TPS100_ToString%`%)
)
TyList=
(Join|
1000 Salaire / dividendes
2000 Remboursement cpte. dépenses
3000 Transfert entre comptes
3500 Remboursement carte crédit
4200 Revenus / ventes
4300 Matériaux et produits
4500 Services rendus
4700 Loyer et locations
4990 Livraison et expédition
4995 Frais de déplacement
5190 Sous-contrat / sous-traitant
5200 Achat matériaux / produits
5300 Frais de transport / livraison
5611 Intérêts et frais bancaires
5625 Assurance
5630 Dons
5640 Réparation et entretien
5646 Formation professionnelle
5650 Frais de représentation
5685 Honoraires professionnels
5690 Licences, droits d'affaires
5700 Fournitures
5701 Location d'équipement
5703 Assurance bureau
5704 Chauffage bureau
5705 Électricité bureau
5706 Entretien bureau
5715 Loyer bureau
5716 Taxes mun. et scolaires
5721 Stationnement
5722 Entretien véhicule
5723 Essences
5724 Immatriculation, permis
5725 Assurance véhicule
5731 Location véhicule
5750 Poste et matériel bureau
5760 Télécommunication
5765 Publicité / promotion
5784 Frais de voyages
5800 Autres / personnel
)
/*;----------------------------------------------------------------------------------------------------------
; TRANSACTION CALCULATION TABLE - ADJUST OFFSET FOR EVERYTHING HERE
;----------------------------------------------------------------------------------------------------------
; "TABLE_X_MARGIN" DETERMINE LA MARGE GAUCHE-DROITE DES CONTROLES DANS LA TABLE. EX: EN DIMINUANT CE CHIFFRE, LA TABLE PRENDRA PLUS DE LARGEUR TOTALE
; "TABLE_COLUMN_COUNT" EST LE NOMBRE PAR LEQUEL LES PROPORTIONS SONT DIVISEES. EX: EN AJOUTANT +1 CHAQUE CONTROLE DIMINUERA PROPORTIONELLEMENT.
;
; ADJUST THE WIDTH RATIO HERE FOR EACH ROUND FUNCTION
; MOVE VARIABLES UNDER OR ABOVE ANOTHER TO CHANGE ORDER OF TAB. THE FIRST BEING LEFT MOST
*/
TABLE_COLUMN_COUNT:=12
, TABLE_X_MARGIN:=44
, TABLE_X_INCREMENT:=-1, TABLE_EDIT_H_INCREMENT:=-1, TABLE_TITLES_Y:=50, TABLE_TITLES_H:=40
, TABLE_ROW_H:=24
, TABLE_WIDTH:=(GW+TABLE_COLUMN_COUNT)-(TABLE_X_MARGIN*2)
, Prev_CONTROL_X:=0
;TABLE_WIDTH FORMULA: LARGEUR DU GUI +NOMBRE DE COLONNE (CAR À CHAQUE POSITIONNEMENT DE COLONNE LE X_INCREMENT EST -1,
;CE QUI DONNE 1 DE LARGEUR DISPONIBLE EN PLUS À CHAQUE COLONNE QUI EXISTE. SI ON AVAIT 7 COLONNE IL Y AURAIT 7PX DE PLUS LARGE DISPONIBLE À PARTAGER EN LES COLONNES)
; POURCENTAGE DE LA TABLE TOTALE :
, REMAINING_W_PERCENT:= 100
;DATE
, THIS_W_PERCENT:= 5.8 ;%
, REMAINING_W_PERCENT -= THIS_W_PERCENT
, DATE_W:=ROUND(((THIS_W_PERCENT*TABLE_WIDTH)/100))
, DATE_X:=TABLE_X_MARGIN + Prev_CONTROL_X, Prev_CONTROL_X:=Prev_CONTROL_X + DATE_W + TABLE_X_INCREMENT
;DESCRIPTION
, THIS_W_PERCENT:= 23 ;%
, REMAINING_W_PERCENT -= THIS_W_PERCENT
, DESCRIPTION_W:=ROUND(((THIS_W_PERCENT*TABLE_WIDTH)/100))
, DESCRIPTION_X:=TABLE_X_MARGIN + Prev_CONTROL_X, Prev_CONTROL_X:=Prev_CONTROL_X + DESCRIPTION_W + TABLE_X_INCREMENT
;RETRAIT
, THIS_W_PERCENT:= 6 ;%
, REMAINING_W_PERCENT -= THIS_W_PERCENT
, RETRAIT_W:=ROUND(((THIS_W_PERCENT*TABLE_WIDTH)/100))
, RETRAIT_X:=TABLE_X_MARGIN + Prev_CONTROL_X, Prev_CONTROL_X:=Prev_CONTROL_X + RETRAIT_W + TABLE_X_INCREMENT
;DEPOT
, THIS_W_PERCENT:= 6 ;%
, REMAINING_W_PERCENT -= THIS_W_PERCENT
, DEPOT_W:=ROUND(((THIS_W_PERCENT*TABLE_WIDTH)/100))
, DEPOT_X:=TABLE_X_MARGIN + Prev_CONTROL_X, Prev_CONTROL_X:=Prev_CONTROL_X + DEPOT_W + TABLE_X_INCREMENT
;/*
;NOM
, THIS_W_PERCENT:= 10 ;%
, REMAINING_W_PERCENT -= THIS_W_PERCENT
, NOM_W:=ROUND(((THIS_W_PERCENT*TABLE_WIDTH)/100)) ; EN POURCENTAGE DE LA TABLE TOTALE
, NOM_X:=TABLE_X_MARGIN + Prev_CONTROL_X, Prev_CONTROL_X:=Prev_CONTROL_X + NOM_W + TABLE_X_INCREMENT
;CATEGORIE
, THIS_W_PERCENT:= 14 ;%
, REMAINING_W_PERCENT -= THIS_W_PERCENT
, CATEGORIE_W:=ROUND(((THIS_W_PERCENT*TABLE_WIDTH)/100)) ; EN POURCENTAGE DE LA TABLE TOTALE
, CATEGORIE_X:=TABLE_X_MARGIN + Prev_CONTROL_X, Prev_CONTROL_X:=Prev_CONTROL_X + CATEGORIE_W + TABLE_X_INCREMENT
;CODETAXE
, THIS_W_PERCENT:= 10.5 ;%
, REMAINING_W_PERCENT -= THIS_W_PERCENT
, CODETAXE_W:=ROUND(((THIS_W_PERCENT*TABLE_WIDTH)/100)) ; EN POURCENTAGE DE LA TABLE TOTALE
, CODETAXE_X:= TABLE_X_MARGIN + Prev_CONTROL_X, Prev_CONTROL_X:=Prev_CONTROL_X + CODETAXE_W + TABLE_X_INCREMENT
;TPS/TVQ
, THIS_W_PERCENT:= 4 ;%
, REMAINING_W_PERCENT -= THIS_W_PERCENT
, TPS_W:=ROUND(((THIS_W_PERCENT*TABLE_WIDTH)/100)) ; EN POURCENTAGE DE LA TABLE TOTALE
, THIS_W_PERCENT:= 4 ;%
, REMAINING_W_PERCENT -= THIS_W_PERCENT
, TVQ_W:=ROUND(((THIS_W_PERCENT*TABLE_WIDTH)/100)) ; EN POURCENTAGE DE LA TABLE TOTALE
, TPS_X:=TABLE_X_MARGIN + Prev_CONTROL_X, Prev_CONTROL_X:=Prev_CONTROL_X + TPS_W + TABLE_X_INCREMENT, DEPENSE_TPS_TVQ_LINK_X:= Prev_CONTROL_X
, TVQ_X:=TABLE_X_MARGIN + Prev_CONTROL_X, Prev_CONTROL_X:=Prev_CONTROL_X + TVQ_W + TABLE_X_INCREMENT
;NOTE
, THIS_W_PERCENT:= REMAINING_W_PERCENT ;%
, REMAINING_W_PERCENT -= THIS_W_PERCENT
, NOTE_W:=ROUND(((THIS_W_PERCENT*TABLE_WIDTH)/100)) ; EN POURCENTAGE DE LA TABLE TOTALE
, NOTE_X:=TABLE_X_MARGIN + Prev_CONTROL_X, Prev_CONTROL_X:=Prev_CONTROL_X + NOTE_W + TABLE_X_INCREMENT
if (REMAINING_W_PERCENT > 0)
MsgBox % REMAINING_W_PERCENT " % is not assigned to table cols width"
*/
;----------------------------------------------------------------------------------------------------------
;TABLE HEADER TITLES:
;----------------------------------------------------------------------------------------------------------
style_header_normal := "s16 q5 norm"
style_header_highlight := "s16 q5 bold"
Gui, Font, % style_header_normal
Gui Add, Text,% "vDt_header x"DATE_X " Y" TABLE_TITLES_Y " w"DATE_W " H"TABLE_TITLES_H
. " +0x200 +Center +Border 0x4 SECTION "
, DATE
Gui Add, Text,% "vDs_header x"DESCRIPTION_X " Y" TABLE_TITLES_Y " w"DESCRIPTION_W " H"TABLE_TITLES_H
. " +0x200 +Center +Border 0x4 "
, DESCRIPTION BANCAIRE
Gui Add, Text,% "vRt_header x"RETRAIT_X " Y" TABLE_TITLES_Y " w"RETRAIT_W " H"TABLE_TITLES_H
. " +0x200 +Center +Border 0x4 "
, RETRAIT
Gui Add, Text,% "vDp_header x"DEPOT_X " Y" TABLE_TITLES_Y " w"DEPOT_W " H"TABLE_TITLES_H
. " +0x200 +Center +Border 0x4 "
, DÉPÔT
;/*
Gui Add, Text,% "vNm_header x"NOM_X " YS W"NOM_W " H"TABLE_TITLES_H
. " +0x200 +Center +Border 0x4 "
, NOM
Gui Add, Text,% "vCt_header x"CATEGORIE_X " YS W"CATEGORIE_W " H"TABLE_TITLES_H
. " +0x200 +Center +Border 0x4 "
, CATÉGORIE
Gui Add, Text,% "vTx_header X"CODETAXE_X " YS w"CODETAXE_W " H"TABLE_TITLES_H
. " +0x200 +Center +Border 0x4 "
, TAXE (`%)
Gui Add, Text,% "vTs_header x"TPS_X " YS w"TPS_W " H"TABLE_TITLES_H
. " +0x200 +Center +Border 0x4 "
, TPS
Gui Add, Text,% "vTq_header X"TVQ_X " YS w"TVQ_W " H"TABLE_TITLES_H
. " +0x200 +Center +Border 0x4 "
, TVQ
Gui Add, Text,% "vNt_header x"NOTE_X " YS W"NOTE_W " H"TABLE_TITLES_H
. " +0x200 +Center +Border 0x4 "
, NOTE
*/
;-----------------------------------------------------------------------------------------------------------------------------------------
; Generate rows and columns of ctrls
style_edit_normal := "s12 q5 norm"
style_edit_highlight := "s12 q5 bold"
tpsDefault := tvqDefault := "0.00"
MaxRows:=Transactions.Length()
Loop, %MaxRows% {
Gui, Font, % style_edit_normal
row:=A_Index
; # ROW NUM CHECKBOX
Gui, Add, Checkbox, % "vRowCheckbox_"row " hWndhRowCheckbox"row " x"2
. " Y+"TABLE_EDIT_H_INCREMENT " h"TABLE_ROW_H
. Visibility " Left SECTION Checked", %row%
; DATE
Gui Add, Edit, % Status " vDt_"row " x"TABLE_X_MARGIN " YP w"DATE_W " hp "
. Visibility " Center ReadOnly -TabStop", % Transactions[row, "date"]
; DESCRIPTION
Gui Add, Edit, % Status " vDs_"row " X+"-1 " YP w"DESCRIPTION_W " hp "
. Visibility " Center ReadOnly -TabStop", % Transactions[row, "description"]
; RETRAIT
Gui Add, Edit, % Status " vRt_"row " X+"-1 " YP w"RETRAIT_W " hp "
. Visibility " Center ReadOnly -TabStop", % Transactions[row, "retrait"]
; DEPOT
Gui Add, Edit, % Status " vDp_"row " X+"-1 " YP w"DEPOT_W " hp "
. Visibility " Center ReadOnly -TabStop", % Transactions[row, "depot"]
;/*
style_combobox_normal := "s10 q5 norm"
style_combobox_highlight := "s10 q5 bold"
Gui, Font, % style_combobox_normal
; NOM
Gui Add, ComboBox,% Status " hWndhNm_"row " vNm_"row
. " gCBMatchingGUICreationHandler x+-1 YP W"NOM_W " hp " Visibility
. " +Sort +Uppercase r15 Limit29", %CbNomList%
; CATÉGORIE
Gui Add, ComboBox,% Status " hWndhCt_"row " vCt_"row " x+-1 YP W"CATEGORIE_W " hp "
. " gCBMatchingGUICreationHandler " Visibility " R15 " , %TyList%
IF (Transactions[row, "depot"] > 0) {
GuiControl, ChooseString, % "Ct_"row, 4200
}
; CODETAXE
Gui Add, ComboBox,% Status " vTx_"row " hwndhTx_"row " X+"-1 " YP w"CODETAXE_W " hp"
. " gCBMatchingGUICreationHandler " Visibility " R3 limit5 +Uppercase", %TxListLong%
Gui, font, % style_edit_normal
; TPS
Gui Add, Edit,% Status " vTs_"row " " " x+-1 YP w"TPS_W " hp "
. Visibility " Center", %tpsDefault%
; TVQ
Gui Add, Edit,% Status " vTq_"row " " " x+-1 YP w"TVQ_W " hp "
. Visibility " Center", %tvqDefault%
; NOTE
Gui Add, Edit,% Status " vNt_"row " x+-1 YP W"NOTE_W " hp "
. Visibility " limit60"
*/
progress_percent_3 := progress_percent_2 + (((row * 100) / MaxRows) / 2.6)
Progress, % progress_percent_3 ; Move the bar upward by percent.
;MsgBox % "create window ctrls " progress_percent_3
}
/* Place the hGuiMain inside a scrollable Gui
Creates an instance of the class ScrollGUI - by just me
*/
ScrollableResultats:= New ScrollGUI(hGuiMain,A_ScreenWidth,(A_ScreenHeight-90), "+Resize", 3, 4)
MainGuiTitle := "Finance D - "
ScrollableResultats.Show(MainGuiTitle . filePath)
}
;--------------------------------------------------------------------------------
CreateCBMatchingGUI(hCB, Delimiter:="|") {
;--------------------------------------------------------------------------------
Global CBMatchingGUI := {}
Global CbNomList
SetBatchLines, -1
Gui CBMatchingGUI:New, -Caption -SysMenu -Resize +ToolWindow +AlwaysOnTop
Gui, +HWNDhCBMatchesGui +Delimiter%Delimiter%
Gui, Margin, 0, 0
Gui, Font, s14 q5
; get the Parent ComboBox info (the one that has focus right now)
CBMatchingGUI.hParentCB := hCB
ControlGet, CbList, List,,, % "ahk_id "CBMatchingGUI.hParentCB
CBMatchingGUI.parentCBList := CbList
WinGetPos, cX, cY, cW, cH, % "ahk_id "CBMatchingGUI.hParentCB
ControlGetText, parentText,, % "ahk_id "CBMatchingGUI.hParentCB
; set the overlay Gui controls with the Parent ComboBox info
CBMatchingGUI.hwnd := hCBMatchesGui
Gui, Add, Edit, % "+HWNDhEdit x0 y0 w"cW+200 " R1 +Uppercase", %parentText%
gFunction := Func("CBMatching").Bind(CBMatchingGUI, Delimiter)
GuiControl, +g, %hEdit%, %gFunction%
CBMatchingGUI.hEdit := hEdit
Gui, Add, ListBox, % "+HWNDhLB xp y+0 wp R15", % CBMatchingGUI.parentCBList
CBMatchingGUI.hLB := hLB
GuiControl, ChooseString, %hLB%, %parentText%
;CBMatching(CBMatchingGUI, "`n")
Gui, Show, % "x"cX-1 " y"cY-1 " ", % "CBMatchingGUI"
Send {End}
SetTimer, CBMatchingGUIDestroy, 70
}
;--------------------------------------------------------------------------------
CBMatching(ByRef CBMatchingGUI, Delimiter:="|") { ; ByRef object generated at GUI creation
;--------------------------------------------------------------------------------
GuiControlGet, userInput,, % CBMatchingGUI.hEdit
;--Find in list
choicesList := CBMatchingGUI.parentCBList
if (InStr(choicesList, userInput)) {
Loop, Parse, choicesList, %Delimiter%
{
if (FoundPos := InStr(A_LoopField, userInput)) {
if (FoundPos = 1)
MatchesAtStart .= Delimiter . A_LoopField
else
MatchesAnywhere .= Delimiter . A_LoopField
MatchCount++
}
}
Matches := MatchesAtStart . MatchesAnywhere ; Ordered Match list
GuiControl,, % CBMatchingGUI.hLB, %Matches%
if (MatchCount = 1) {
UniqueMatch := Matches
GuiControl, ChooseString, % CBMatchingGUI.hLB, %UniqueMatch%
}
else
GuiControl, Choose, % CBMatchingGUI.hLB, 1
}
else
GuiControl,, % CBMatchingGUI.hLB, % Delimiter . "<! Aucune correspondance !>"
}
;--------------------------------------------------------------------------------
CBMatchingGUIDestroy(isCalledDirectly:=false) {
;--------------------------------------------------------------------------------
Global CBMatchingGUI
; Global object created with the CBMatchingGUI
; keep globally because using a SetTimer loop
; and don't want to require a func().binding()
if ((!WinActive("Ahk_id "CBMatchingGUI.hwnd) or isCalledDirectly) and WinExist("ahk_id "CBMatchingGUI.hwnd)) {
Gui, % CBMatchingGUI.hwnd ":Destroy"
SetTimer, CBMatchingGUIDestroy, Delete
}
}
;--------------------------------------------------------------------------------
CBMatchingGUICreationHandler() {
;--------------------------------------------------------------------------------
Global CBMatchingGUI
GuiControlGet, %A_GuiControl% ; Get focused Gui control's value
cbText:=%A_GuiControl% ; text typed in focused ComboBox's edit control
if (!WinExist("ahk_id "CBMatchingGUI.hwnd) and ((StrLen(cbText) > 0) and (StrLen(cbText) < 2))) {
GuiControlGet, hCB, Hwnd, %A_GuiControl%
CreateCBMatchingGUI(hCB, Delimiter:="`n")
}
}
#IfWinActive, CBMatchingGUI
;================================================================================
F2::
Enter::
NumpadEnter::
Tab::setCBMatchingGUILBChoice(CBMatchingGUI) ; pass GUI object reference
~LButton::
;set ListBox choice by left click
Sleep, 10 ; allow control to update itself
setCBMatchingGUILBChoice(CBMatchingGUI)
return
Up::
Down::ControlSend,, % A_ThisHotkey = "Up" ? "{Up}" : "{Down}", % "ahk_id "CBMatchingGUI.hLB
;--------------------------------------------------------------------------------
setCBMatchingGUILBChoice(ByRef CBMatchingGUI) {
;--------------------------------------------------------------------------------
; get ListBox choice
GuiControlGet, LBMatchesSelectedChoice,, % CBMatchingGUI.hLB
; set choice in sage ComboBox
Control, ChooseString, %LBMatchesSelectedChoice%,,% "ahk_id "CBMatchingGUI.hParentCB
if (!ErrorLevel)
Sleep, 60
CBMatchingGUIDestroy(isCalledDirectly:=true)
}
;--------------------------------------------------------------------------------
SageExportButton() {
;--------------------------------------------------------------------------------
;"DEPOT REVENUS"
Global Transactions
Global banque
JG:={}
JG.SetCapacity(500)
for index in Transactions
{
GuiControlGet, RowCheckbox_%index%
Transactions[index,"is_checked"] := RowCheckbox_%index%
if (!Transactions[index,"is_checked"]
or !Transactions[index,"depot"]
or Transactions[index, "depot"] ~= "[a-zA-Z|\s]")
continue
; format this transaction's values for sage import
MM_DD_YY:=formatDate(Transactions,index,banque)
montantDebit:=Transactions[index,"depot"]
remarque:=trim(Transactions[index,"description"])
JG[index,"MM_DD_YY"]:=MM_DD_YY ; DATE
JG[index,"source"]:="Fonds N-D" ; NUMERO DE CHEQUE, VIREMENT, FACTURE ETC.
JG[index,"remarque"]:=remarque ; SANS ACCENTS
JG[index,"debit","cpte"]:="1060" ; COMPTE au debit
JG[index,"debit","montant"]:=montantDebit ; Montant debité
JG[index,"credit","cpte"]:="1050" ; COMPTE au credit
JG[index,"credit","montant"]:="-" . montantDebit ; Montant crédité
}
CreateSageImportCSVFile(JG)
}
;--------------------------------------------------------------------------------
formatDate(ByRef Transactions, ByRef index, banque:="") {
;--------------------------------------------------------------------------------
; formats the date to the sage import expected format
if (banque = "Desjardins")
{
csvDate:=StrSplit(Transactions[index,"date"], "/")
MM:=csvDate[2]
DD:=csvDate[3]
YY:=SubStr(csvDate[1],3,2)
MM_DD_YY:=MM . "-" . DD . "-" . YY
}
else if (banque = "TD") {
;12/02/2019
csvDate:=StrSplit(Transactions[index,"date"], "/")
MM:=csvDate[1]
DD:=csvDate[2]
YY:=SubStr(csvDate[3],3,2)
MM_DD_YY:=MM . "-" . DD . "-" . YY
}
else if (banque = "RBC") {
;12/2/2019
csvDate:=StrSplit(Transactions[index,"date"], "/")
MM:=csvDate[1]
DD:=(StrLen(csvDate[2]) = 1 ? 0 . csvDate[2] : csvDate[2])
YY:=SubStr(csvDate[3],3,2)
MM_DD_YY:=MM . "-" . DD . "-" . YY
}
else if (banque = "BMO") {
;20191101
csvDate:=Transactions[index,"date"]
MM:=SubStr(csvDate,5,2)
DD:=SubStr(csvDate,7,2)
YY:=SubStr(csvDate,3,2)
MM_DD_YY:=MM . "-" . DD . "-" . YY
}
else
{
MsgBox Cette banque n'est pas encore prise en charge.
return
}
return MM_DD_YY
}
;--------------------------------------------------------------------------------
CreateSageImportCSVFile(ByRef JG) {
;--------------------------------------------------------------------------------
Gui, SageImport:New
fileString:=""
For index in JG
{
transaction_string := ""
transaction_string .= JG[index,"MM_DD_YY"] . "," ; DATE
transaction_string .= """" . JG[index,"source"] . """" . "," ; SOURCE
transaction_string .= """" . JG[index,"remarque"] . """" ; REMARQUE
transaction_string .= "`n" ; Saut de ligne (enter)
transaction_string .= JG[index,"debit","cpte"] . "," ; DÉBIT #CPTE AFFECTÉ
transaction_string .= JG[index,"debit","montant"] ; DÉBIT MONTANT
transaction_string .= "`n" ; Saut de ligne (enter)
transaction_string .= JG[index,"credit","cpte"] . "," ; CRÉDIT #CPTE AFFECTÉ
transaction_string .= JG[index,"credit","montant"] ; CRÉDIT MONTANT
transaction_string .= "`n" ; Saut de ligne (enter)
fileString .= transaction_string
}
if (fileString) {
path := A_Desktop . "\SAGE_IMPORT.txt"
FileDelete, % path
FileAppend, %fileString%, % path
Run, % path
} else {
MsgBox,36,Exporter pour Sage, Il n'y a aucune transactio valide à exporter
}
}
;--------------------------------------------------------------------------------
OnWM_COMMAND(wParam, lParam, msg, hWnd) {
;--------------------------------------------------------------------------------
Static CBN_SELCHANGE := 1, CBN_EDITUPDATE := 6
GuiControlGet, GuiControlName, %A_Gui%:Name, %lParam%
GuiControlGet, hCB, Hwnd, %GuiControlName%
If (GuiControlName ~= "Tx_") && (wParam >> 16 = CBN_SELCHANGE) {
CalculTaxes(hCB)
}
}
;--------------------------------------------------------------------------------
CalculTaxes(hCB) {
;--------------------------------------------------------------------------------
GLOBAL
GuiControlGet, codeTaxe,, % hCB
GuiControlGet, fCtrlV, Name, % hCB
row:=RegExReplace(fCtrlV, "[^0-9]") ; keep only the numbers in the control's name
GuiControlGet, retrait, %hGuiMain%:, Rt_%row%
GuiControlGet, depot, %hGuiMain%:, Dp_%row%
montant:= retrait ? retrait : depot
; 1) Calculer le montant hors taxes de cette ligne
; selon les pourcentages du Code Taxe sélectionné :
if (InStr(codeTaxe, "QC")) ;____________________________________________________________________________________________________QUEBEC
{
TPS100_%row% := TPS100_ ; 5% TAXE GOUVERNEMENTALE
, TVQ100_%row% := TVQ100_ ; 9.975% TAXE QC
montantAvantTaxes := (montant / (TPS100_%row% + TVQ100_%row% + 1)) ; 14.975% TVH (TAXE VENTE HARMONISE)
}
else if (InStr(codeTaxe, "CAN")) { ;__________________________________________________________________________________________CANADA
TPS100_%row% := TPS100_ ; 5% TAXE GOUVERNEMENTALE
, TVQ100_%row% := 0 ; 0% TAXE QC
montantAvantTaxes := (montant / (TPS100_%row% + 1)) ; 5% TVH (TAXE VENTE HARMONISE)
}
else if (InStr(codeTaxe, "ON")) { ;__________________________________________________________________________________________ONTARIO
TPS100_%row% := TPS100_ + TVH100_ONTARIO ; 13% TAXE GOUVERNEMENTALE HARMONISEE
, TVQ100_%row% := 0 ; 0% TAXE QC
montantAvantTaxes := (montant / (TPS100_%row% + 1)) ; 13% TVH (TAXE VENTE HARMONISE)
}
else {
TPS100_%row% := 0
, TVQ100_%row% := 0
montantAvantTaxes := 0
}
; 2) Calculer les valeurs de TPS/TVQ pour cette ligne
vraiTs_%row% := (montantAvantTaxes * TPS100_%row%) ; TPS CALCUL sans arrondissement 6 floating points precision
, vraiTq_%row% := (montantAvantTaxes * TVQ100_%row%) ; TVQ CALCUL sans arrondissement 6 floating points precision
; 3) Afficher les résultats obtenus dans les contrôles du GUI
; en les arrondissant à 2 décimales :
GuiControl,%hGuiMain%:, Ts_%row%, % Round(vraiTs_%row%, 2) ; TPS update control Arrondi ET ACTUALISE Ts_%row%
GuiControl,%hGuiMain%:, Tq_%row%, % Round(vraiTq_%row%, 2) ; TVQ update control Arrondi ET ACTUALISE Tq_%row%
; -> Note: il faut cibler le hGuiMain car intégré au main GUI
}
#if WinActive(MainGuiTitle)
; double clicks will copy
; the text of the focused ctrl
; to clopboard to allow pasting
; in another software quickly
~LButton::
if (A_ThisHotkey = A_PriorHotkey && A_TimeSincePriorHotkey < 200) {
ControlGetFocus, fCtrl, A
if (!fCtrl)
return
ControlGetText, cText, %fCtrl%, A
if (!cText)
return
Clipboard := cText
}
return
; while holding ctrl + left Click
; the row under mouse cursor
; will be disabled and highlighted
^LButton::
if (GetKeyState("Ctrl", "P"))
prev_row := ""
; pour pouvoir désactiver une ligne pendant qu'on tient le raccourci
rows_enabled_states := {}
while GetKeyState("LButton", "P") {
row_num := get_row_under_mouse()
if (row_num != prev_row) {
; state toggle
if (!rows_enabled_states[row_num]) {
rows_enabled_states[row_num] := toggle_row_enabled(row_num)
}
; bold toggle
if (!rows_bold_states[row_num]) {
toggle_row_highlight(True, row_num)
rows_bold_states[row_num] := row_num
}
toggle_row_highlight(False, prev_row)
rows_bold_states[prev_row] := False
prev_row := row_num
}
sleep, 10
}
prev_row := ""
return
; while holding down ctrl,
; the row under mouse cursor
; will be highlighted to Help
; following data in table
~Ctrl::
rows_bold_states := {}
while GetKeyState("Ctrl", "P") {
row_num := get_row_under_mouse()
if (row_num != prev_row) {
if (!rows_bold_states[row_num]) {
toggle_row_highlight(True, row_num)
rows_bold_states[row_num] := row_num
}
toggle_row_highlight(False, prev_row)
rows_bold_states[prev_row] := False
prev_row := row_num
}
sleep, 10
}
toggle_row_highlight(False, row_num)
prev_row := ""
return
;--------------------------------------------------------------------------------
toggle_row_enabled(row_num) {
;--------------------------------------------------------------------------------
Global hGuiMain
GuiControlGet, is_checked, %hGuiMain%:, % "RowCheckbox_" . row_num
For column, variable_name in gui_column_names {
GuiControl, % hGuiMain ":"(is_checked ? "Disable":"Enable"), % variable_name . row_num
}
GuiControl, %hGuiMain%:, % "RowCheckbox_" . row_num, % (is_checked ? 0:1)
return is_checked
}
;--------------------------------------------------------------------------------
get_row_under_mouse() {
;--------------------------------------------------------------------------------
Global hGuiMain
MouseGetPos,,,,ctrl_under_mouse
if (!ctrl_under_mouse)
return
GuiControlGet, var_name, %hGuiMain%:Name, % ctrl_under_mouse
if (!var_name)
return
return row := RegExReplace(var_name, "\D")
}
;--------------------------------------------------------------------------------
toggle_row_highlight(boolean, ByRef row) {
;--------------------------------------------------------------------------------
Global hGuiMain
Global style_combobox_highlight, style_combobox_normal
, style_edit_highlight, style_edit_normal
static CB_SETEDITSEL := 0x0142
For column, variable_name in gui_column_names {
if variable_name contains Nm_,Ct_,Tx_
{
; these ctrls are comboboxes
bold_font := style_combobox_highlight
normal_font := style_combobox_normal
GuiControlGet, hCB, %hGuiMain%:Hwnd, % variable_name . row
} else {
bold_font := style_edit_highlight
normal_font := style_edit_normal
}
Gui, %hGuiMain%:Font, % (boolean ? bold_font : normal_font)
GuiControl, %hGuiMain%:Font, % variable_name . row
if (hCB) {
PostMessage, %CB_SETEDITSEL%, 0, 0,, % "ahk_id "hCB
; gets rid of unwanted combobox contents selection
; that occurs when toggleing the font to bold
hCB:=""
}
}
}
/*
todo
;--------------------------------------------------------------------------------
toggle_column_highlight(boolean, ByRef column) {
;--------------------------------------------------------------------------------
Global hGuiMain
For column, variable_name in gui_column_names {
normal_font := normal_edit_font
if (variable_name contains Nm_,Ct_,Tx_) {
; these ctrls are comboboxes
bold_font := bold_combobox_font
normal_font := normal_combobox_font
GuiControlGet, hCB, %hGuiMain%:Hwnd, % variable_name . row
}
Gui, %hGuiMain%:Font, % (boolean ? bold_font : normal_font)
GuiControl, %hGuiMain%:Font, % variable_name . row
Gui, %hGuiMain%:Font, % (boolean ? bold_header_font : normal_header_font)
GuiControl, %hGuiMain%:Font, % variable_name . "header"
if (hCB) {
PostMessage, %CB_SETEDITSEL%, 0, 0,, % "ahk_id "hCB
; gets rid of unwanted combobox contents selection
; that occurs when toggleing the font to bold
hCB:=""
}
}
}
*/