"io/ioutil"
"os"
"strings"
+ "strconv"
)
+type inv_accnts struct {
+ id string
+ taxval int
+ tax bool
+ buy bool
+}
+
//
-// hardcoded account ids we have to look at
+// accounts considered in tax included balance check
//
-// --- buy
-// wareneingang 19% and 7%
-const pid_buy_n = string("8e3b7c42e3173ed85f3d4736e82afb4d")
-const pid_buy_s = string("0cfd2ceb45fff89b9d1b7ce3af66cdf3")
-const pid_misc = string("e3acc2865dbf931e41cf2b90240de5c2")
-const pid_rep = string("b1d04ad157cac569f4299d4ddf94ed6f")
-const pid_room = string("4394ed4ffa7266f8f8731080926a7a61")
-const pid_cap = string("4196ee026d1bdb785df2c975fca91ae0")
+var iaa []inv_accnts = []inv_accnts{
+// wareneingang 19% and 7% (note: pids!)
+ { "8e3b7c42e3173ed85f3d4736e82afb4d",19,false,true },
+ { "0cfd2ceb45fff89b9d1b7ce3af66cdf3", 7,false,true },
+ { "e3acc2865dbf931e41cf2b90240de5c2",19,false,true },
+ { "b1d04ad157cac569f4299d4ddf94ed6f",19,false,true },
+ { "4394ed4ffa7266f8f8731080926a7a61",19,false,true },
+ { "4196ee026d1bdb785df2c975fca91ae0",19,false,true },
+// aids ...
+ { "cb67d346eac01c2b66e2394df4e8d6e8",19,false,true },
// abziehbare vst 19% and 7%
-const aid_vst_n = string("7c449e13125d6b93043f963628106db2")
-const aid_vst_s = string("006643c1c0a91f2b40614c75a49c6295")
+ { "7c449e13125d6b93043f963628106db2",19,true,true },
+ { "006643c1c0a91f2b40614c75a49c6295", 7,true,true },
// --- sales
// receipts
-const aid_rec_n = string("f3e905732b729ba096a50dab60559ce7")
-const aid_rec_s = string("66c1b04bd897766cb2be538094e1db6a")
-const aid_tip = string("1d20024badc11a99a8e1cf3a9a64a501")
-const aid_dep = string("9772f4e231f6f5e3100132cc53eb3447")
+ { "f3e905732b729ba096a50dab60559ce7",19,false,false },
+ { "66c1b04bd897766cb2be538094e1db6a", 7,false,false },
+ { "1d20024badc11a99a8e1cf3a9a64a501",19,false,false },
+ { "9772f4e231f6f5e3100132cc53eb3447",19,false,false },
// ust
-const aid_ust_n = string("e4bd6ff52408be8076f24aeb105893d9")
-const aid_ust_s = string("38bf40d16529f2a1e611c073c6c1dc9c")
+ { "e4bd6ff52408be8076f24aeb105893d9",19,true,false },
+ { "38bf40d16529f2a1e611c073c6c1dc9c", 7,true,false },
+}
+
+//
+// account exceptions
+//
+// account exceptions: nineteen to seven
+var n2s_exc = []string{
+}
+// account exceptions: nineteen to zero
+var n2z_exc = []string{
+ "4970 Nebenkosten des",
+ "4910 Porto",
+}
+// transaction exceptions: nineteen to seven
+var n2s_exc_ta = []string{
+ "GEMA",
+}
+// transaction exceptions: nineteen to zero
+var n2z_exc_ta = []string{
+ "Deutsche Post",
+ "gesetz IHK",
+ "Gesundheitsbelehrung",
+ "Gewerbezentralregister",
+ "Entgeltabrechnung siehe Anlage",
+ "ENTGELT SPK",
+ "ttenrecht und F",
+ "Unterrichtung Gastst",
+ "MPLC",
+}
+
+// transacion exception list --- the rest, required?
+var trn_exc = []string{
+}
+
+//
+// accounts which will be summed up
+//
+
+type sum_accnt struct {
+ name string
+ aid string
+ valplus int
+ valminus int
+}
+
+var summed_accounts = []sum_accnt{
+ {"Bankkonto","02ea930fdcc500cf7d3a21b80a126eb0",0,0},
+ {"Kasse","04e71353130ccb554ebaf4c2438d6b2f",0,0},
+}
// account maps
type amap struct {
taxval int // 7 or 19
buy bool // buy or sales
tax bool // tax or non-tax(=goods) account
- rid []string // required transaction account(s)
}
// xml
Trn []Transaction `xml:"book>transaction"`
}
+// tax
+type TaxReport struct {
+ Expenses [2]int
+ InputTax [2]int
+ ExpExc [2]int
+ ITExc [2]int
+ Receipts [2]int
+ SalesTax [2]int
+}
+
// 'global' data
var data ParsedData
+var tax_report TaxReport
func main() {
+ // argv
+ sel_date := ""
+ if len(os.Args) > 1 {
+ sel_date = os.Args[1]
+ }
+
// open xml file
file, err := os.Open("c13_skr03.gnucash")
if err != nil {
// whooha, this is our data!
fmt.Println("Parsed accounts:",len(data.Accnt))
fmt.Println("Parsed transactions:",len(data.Trn))
+ fmt.Println("")
accnt := make(map[string]amap)
for ac := range data.Accnt {
aid := data.Accnt[ac].AccountId
pid := data.Accnt[ac].ParentId
- // general map
- rid := make ([]string,10,10)
- accnt[aid]=amap{
- pid,ac,0,false,false,rid,
- }
- rid[0]="NONE"
- tmp := accnt[aid]
- switch {
- // ---- buy
- // -- goods
- case pid == pid_buy_n || pid == pid_misc || pid == pid_rep || pid == pid_room || pid == pid_cap:
- tmp.taxval=19
- tmp.buy=true
- rid[0]=aid_vst_n
- accnt[aid]=tmp
- case pid == pid_buy_s:
- tmp.taxval=7
- tmp.buy=true
- rid[0]=aid_vst_s
- accnt[aid]=tmp
- // -- tax
- case aid == aid_vst_n:
- tmp.taxval=19
- tmp.buy=true
- tmp.tax=true
- rid=[]string{pid_buy_n,pid_misc,pid_rep,pid_room,pid_cap,}
- accnt[aid]=tmp
- case aid == aid_vst_s:
- tmp.taxval=7
- tmp.buy=true
- tmp.tax=true
- rid[0]=pid_buy_s
- accnt[aid]=tmp
- // ---- sales ----
- // -- receipts
- case aid == aid_rec_n || aid == aid_tip || aid == aid_dep:
- tmp.taxval=19
- rid[0]=aid_ust_n
- accnt[aid]=tmp
- case aid == aid_rec_s:
- tmp.taxval=7
- rid[0]=aid_ust_s
- accnt[aid]=tmp
- // -- tax
- case aid == aid_ust_n:
- tmp.taxval=19
- tmp.tax=true
- rid=[]string{aid_rec_n,aid_tip,aid_dep,}
- accnt[aid]=tmp
- case aid == aid_ust_s:
- tmp.taxval=7
- tmp.tax=true
- rid[0]=aid_rec_s
- accnt[aid]=tmp
+ for iac := range iaa {
+ // consider account if pid or aid matches
+ if pid == iaa[iac].id || aid == iaa[iac].id {
+ taxval := iaa[iac].taxval
+ for ec := range n2s_exc {
+ if strings.Contains(data.Accnt[ac].Name,
+ n2s_exc[ec]) {
+ taxval=7
+ break
+ }
+ }
+ for ec := range n2z_exc {
+ if strings.Contains(data.Accnt[ac].Name,
+ n2z_exc[ec]) {
+ taxval=0
+ break
+ }
+ }
+ accnt[aid]=amap{
+ pid,
+ ac,
+ taxval,
+ iaa[iac].buy,
+ iaa[iac].tax,
+ }
+ break
+ }
}
}
// check transactions ...
for tc := range data.Trn {
- // ... and all the accounts involved
- for tsc := range data.Trn[tc].Spl {
- aid := data.Trn[tc].Spl[tsc].AccountId
- if check_trn(&data.Trn[tc],accnt,aid) == false {
- ac := accnt[aid].num
- fmt.Println("")
- fmt.Println(data.Trn[tc].Date)
- fmt.Println(" ",data.Trn[tc].Description)
- fmt.Println(" ",data.Accnt[ac].Name)
- }
- }
+ // check balance ...
+ check_balance(&data.Trn[tc],accnt,sel_date)
}
+ // tax report
+ fmt.Println("Umsatzsteuervoranmeldung (19% | 7%):")
+ fmt.Println("------------------------------------")
+ fmt.Println("Aufwendungen:",tax_report.Expenses[0],
+ tax_report.Expenses[1]);
+ fmt.Println("ohne Ausn. :",tax_report.Expenses[0]-
+ tax_report.ExpExc[0],
+ tax_report.Expenses[1]-
+ tax_report.ExpExc[1]);
+ fmt.Println("gesch. Vst. :",int((tax_report.Expenses[0]*19)/100.0),
+ int((tax_report.Expenses[1]*7)/100.0))
+ fmt.Println("ohne Ausn. :",int(((tax_report.Expenses[0]-
+ tax_report.ExpExc[0])*19)/100.0),
+ int(((tax_report.Expenses[1]-
+ tax_report.ExpExc[1])*7)/100.0))
+ fmt.Println("Vorsteuer :",tax_report.InputTax[0],
+ tax_report.InputTax[1],
+ "->",tax_report.InputTax[0]+tax_report.InputTax[1]);
+ fmt.Println("------------------------------------")
+ fmt.Println("Einnahmen :",-tax_report.Receipts[0],
+ -tax_report.Receipts[1]);
+ fmt.Println("gesch. Ust. :",int((-tax_report.Receipts[0]*19)/100.0),
+ int((-tax_report.Receipts[1]*7)/100.0));
+ fmt.Println("Umsatzsteuer:",-tax_report.SalesTax[0],
+ -tax_report.SalesTax[1]);
+ fmt.Println("------------------------------------")
+
+ // summed accounts
+ fmt.Println("")
+ fmt.Println("Summen einiger Konten:")
+ fmt.Println("----------------------")
+ for sac := range summed_accounts {
+ saccnt := summed_accounts[sac]
+ fmt.Println(" Konto: ",saccnt.name)
+ fmt.Println(" +: ",saccnt.valplus)
+ fmt.Println(" -: ",saccnt.valminus)
+ }
}
-func check_trn(ta *Transaction,accnt map[string]amap,aid string) bool {
- //ac := accnt[aid].num
- if accnt[aid].rid[0]=="NONE" {
+func check_balance(ta *Transaction,accnt map[string]amap,sel_date string) bool {
+
+ // check date
+ tdate := strings.Fields(ta.Date)[0]
+ if !strings.Contains(tdate,sel_date) {
return true
}
- for ra := range accnt[aid].rid {
- for ea := range ta.Spl {
- oaid := ta.Spl[ea].AccountId
- switch {
- case accnt[aid].tax && accnt[aid].buy:
- // check pids
- if accnt[oaid].pid == accnt[aid].rid[ra] {
- return true
+
+ // exceptions
+ tv_ow := -1
+ for ec := range n2s_exc_ta {
+ if strings.Contains(ta.Description,n2s_exc_ta[ec]) {
+ tv_ow=7
+ break
+ }
+ }
+ for ec := range n2z_exc_ta {
+ if strings.Contains(ta.Description,n2z_exc_ta[ec]) {
+ tv_ow=0
+ break
+ }
+ }
+
+ // [taxval: 19=0 7=1][tax: no=0 yes=1][buy: no=0 yes=1]
+ var sum [2][2][2]int
+
+ // loop over splits within the transaction
+ for sc := range ta.Spl {
+ aid := ta.Spl[sc].AccountId
+ //accnt[aid].tax
+ // loop over all considered accounts (defined earlier as global)
+ for iac := range iaa {
+ tv := int(0)
+ // taxval changed by exception
+ if tv_ow != -1 {
+ if tv_ow == 7 {
+ tv=1
+ }
+ if tv_ow == 0 {
+ // reset taxvalues of involved accounts
+ // (to drop an error)
+ if accnt[aid].tax {
+ if accnt[aid].taxval==7 {
+ tv=1
+ }
+ } else {
+ continue
+ }
+ }
+ // taxval as defined by account
+ } else {
+ if accnt[aid].taxval == 0 {
+ if accnt[aid].tax {
+ fmt.Println("FATAL!");
+ }
+ continue
+ }
+ if accnt[aid].taxval == 7 {
+ tv = 1
}
- default:
- // check aids
- if ta.Spl[ea].AccountId == accnt[aid].rid[ra] {
- return true
+ }
+ // tax
+ tax := int(0)
+ if iaa[iac].tax {
+ tax = 1
+ }
+ // buy
+ buy := int(0)
+ if iaa[iac].buy {
+ buy = 1
+ }
+ // match! add to sum and break.
+ match := bool(false)
+ // check pids if ...
+ if tax == 0 && buy == 1 {
+ _, exists := accnt[aid]
+ if exists {
+ // pids
+ if accnt[aid].pid == iaa[iac].id {
+ match = true
+ }
+ }
+ }
+ // ... however, always check aids
+ if aid == iaa[iac].id {
+ match = true
+ }
+ if match {
+ inc, _ := strconv.Atoi(strings.TrimSuffix(ta.Spl[sc].Value,"/100"))
+ sum[tv][tax][buy] += inc
+ break
+ }
+ }
+ // now get the sums
+ for sac := range summed_accounts {
+ if summed_accounts[sac].aid == aid {
+ inc, _ := strconv.Atoi(strings.TrimSuffix(ta.Spl[sc].Value,"/100"))
+ if inc >= 0 {
+ summed_accounts[sac].valplus += inc
+ } else {
+ summed_accounts[sac].valminus += inc
}
}
- //fmt.Println(data.Accnt[accnt[oaid].num].Name)
}
}
- // some exceptions
- wordlist := []string{
- "GEMA",
- "Deutsche Post",
- "gesetz IHK",
- "Gesundheitsbelehrung",
- "Gewerbezentralregister",
- "Entgeltabrechnung siehe Anlage",
- "ENTGELT SPK",
- "ttenrecht und F",
- }
- for wc := range wordlist {
- if strings.Contains(ta.Description,wordlist[wc]){
- return true
+ // check for exceptions
+ exc := false
+ for ec := range trn_exc {
+ if strings.Contains(ta.Description,trn_exc[ec]) {
+ exc = true
+ break
}
}
+ //for ac := range accountlist {
+ // if strings.Contains(data.Accnt[anum].Name,accountlist[ac]){
+ // return true
+ // }
+ //}
- return false
+ // tax report
+ for tv := 0; tv<2; tv++ {
+ tax_report.Expenses[tv] += sum[tv][0][1]
+ tax_report.InputTax[tv] += sum[tv][1][1]
+ tax_report.Receipts[tv] += sum[tv][0][0]
+ tax_report.SalesTax[tv] += sum[tv][1][0]
+ if exc {
+ tax_report.ExpExc[tv] += sum[tv][0][1]
+ tax_report.ITExc[tv] += sum[tv][1][1]
+ }
+ }
+
+ // check
+ var expected [2]int
+ check := true
+ for buy := 0; buy < 2; buy++ {
+ expected[0]=int((sum[0][0][buy]*19)/100.0)
+ expected[1]=int((sum[1][0][buy]*7)/100.0)
+ for tv :=0; tv < 2; tv++ {
+ if expected[tv] < sum[tv][1][buy]-1 ||
+ expected[tv] > sum[tv][1][buy]+1 {
+ var sb, st string
+ if buy == 0 {
+ sb = "Umsatzsteuer"
+ } else {
+ sb = "Vorsteuer"
+ }
+ if tv == 0 {
+ st = "19%"
+ } else {
+ st = " 7%"
+ }
+ check = false
+ fmt.Printf("%s %s: ",sb,st);
+ fmt.Printf("Erwarte %d statt %d aus %d! ",
+ expected[tv],
+ sum[tv][1][buy],sum[tv][0][buy]);
+ if(!exc) {
+ fmt.Printf("\n");
+ } else {
+ fmt.Printf("Ausnahme greift!\n");
+ fmt.Printf("(%s)\n\n",ta.Description)
+ }
+ }
+ }
+ }
+ if !check && !exc {
+ fmt.Println("am",ta.Date)
+ fmt.Printf("(%s)\n",ta.Description)
+ fmt.Println("Beteiligte Konten:")
+ for ic := range ta.Spl {
+ found := strings.TrimSuffix(ta.Spl[ic].Value,"/100")
+ aid := ta.Spl[ic].AccountId
+ _, exists := accnt[aid]
+ if exists {
+ num := accnt[aid].num
+ fmt.Printf(" %s => %s\n",data.Accnt[num].Name,
+ found)
+ } else {
+ fmt.Printf(" %s => %s\n",aid,found)
+ }
+ }
+ fmt.Println("")
+ }
+
+ return check
+}
+
+func round(v float64) int {
+ if v < 0.0 {
+ v -= 0.5
+ } else {
+ v += 0.5
+ }
+ return int(v)
}