gocash.go

   1 package main
   2
   3 import (
   4         "encoding/xml"
   5         "fmt"
   6         "io/ioutil"
   7         "os"
   8         "strings"
   9         "strconv"
  10 )
  11
  12 type inv_accnts struct {
  13         id string
  14         taxval int
  15         tax bool
  16         buy bool
  17 }
  18
  19 //
  20 // accounts considered in tax included balance check
  21 //
  22 var iaa []inv_accnts = []inv_accnts{
  23 // wareneingang 19% and 7% (note: pids!)
  24  { "8e3b7c42e3173ed85f3d4736e82afb4d",19,false,true },
  25  { "0cfd2ceb45fff89b9d1b7ce3af66cdf3", 7,false,true },
  26  { "e3acc2865dbf931e41cf2b90240de5c2",19,false,true },
  27  { "b1d04ad157cac569f4299d4ddf94ed6f",19,false,true },
  28  { "4394ed4ffa7266f8f8731080926a7a61",19,false,true },
  29  { "4196ee026d1bdb785df2c975fca91ae0",19,false,true },
  30 // aids ...
  31  { "cb67d346eac01c2b66e2394df4e8d6e8",19,false,true },
  32 // abziehbare vst 19% and 7%
  33  { "7c449e13125d6b93043f963628106db2",19,true,true },
  34  { "006643c1c0a91f2b40614c75a49c6295", 7,true,true },
  35 // --- sales
  36 // receipts
  37  { "f3e905732b729ba096a50dab60559ce7",19,false,false },
  38  { "66c1b04bd897766cb2be538094e1db6a", 7,false,false },
  39  { "1d20024badc11a99a8e1cf3a9a64a501",19,false,false },
  40  { "9772f4e231f6f5e3100132cc53eb3447",19,false,false },
  41 // ust
  42  { "e4bd6ff52408be8076f24aeb105893d9",19,true,false },
  43  { "38bf40d16529f2a1e611c073c6c1dc9c", 7,true,false },
  44 }
  45
  46 //
  47 // account exceptions
  48 //
  49 // account exceptions: nineteen to seven
  50 var n2s_exc = []string{
  51 }
  52 // account exceptions: nineteen to zero
  53 var n2z_exc = []string{
  54         "4970 Nebenkosten des",
  55         "4910 Porto",
  56 }
  57 // transaction exceptions: nineteen to seven
  58 var n2s_exc_ta = []string{
  59         "GEMA",
  60 }
  61 // transaction exceptions: nineteen to zero
  62 var n2z_exc_ta = []string{
  63         "Deutsche Post",
  64         "gesetz IHK",
  65         "Gesundheitsbelehrung",
  66         "Gewerbezentralregister",
  67         "Entgeltabrechnung siehe Anlage",
  68         "ENTGELT SPK",
  69         "ttenrecht und F",
  70         "Unterrichtung Gastst",
  71         "MPLC",
  72 }
  73
  74 // transacion exception list --- the rest, required?
  75 var trn_exc = []string{
  76 }
  77
  78 //
  79 // accounts which will be summed up
  80 //
  81
  82 type sum_accnt struct {
  83         name string
  84         aid string
  85         valplus int
  86         valminus int
  87 }
  88
  89 var summed_accounts = []sum_accnt{
  90         {"Bankkonto","02ea930fdcc500cf7d3a21b80a126eb0",0,0},
  91         {"Kasse","04e71353130ccb554ebaf4c2438d6b2f",0,0},
  92 }
  93
  94 // account maps
  95 type amap struct {
  96         pid string // parent id
  97         num int // account number
  98         taxval int // 7 or 19
  99         buy bool // buy or sales
 100         tax bool // tax or non-tax(=goods) account
 101 }
 102
 103 // xml
 104 type Account struct {
 105         XMLName xml.Name `xml:"account"`
 106         Name string `xml:"name"`
 107         AccountId string `xml:"id"`
 108         ParentId string `xml:"parent"`
 109 }
 110 type Split struct {
 111         XMLName xml.Name `xml:"split"`
 112         Id string `xml:"id"`
 113         Value string `xml:"value"`
 114         Quantity string `xml:"quantity"`
 115         AccountId string `xml:"account"`
 116 }
 117 type Transaction struct {
 118         XMLName xml.Name `xml:"transaction"`
 119         Id string `xml:"id"`
 120         Date string `xml:"date-posted>date"`
 121         Description string `xml:"description"`
 122         Spl []Split `xml:"splits>split"`
 123 }
 124 type ParsedData struct {
 125         XMLName xml.Name `xml:"gnc-v2"`
 126         DataCnt []string `xml:"count-data"`
 127         Accnt []Account `xml:"book>account"`
 128         Trn []Transaction `xml:"book>transaction"`
 129 }
 130
 131 // tax
 132 type TaxReport struct {
 133         Expenses [2]int
 134         InputTax [2]int
 135         ExpExc [2]int
 136         ITExc [2]int
 137         Receipts [2]int
 138         SalesTax [2]int
 139 }
 140
 141 // 'global' data
 142 var data ParsedData
 143 var tax_report TaxReport
 144
 145 func main() {
 146
 147         // argv
 148         sel_date := ""
 149         if len(os.Args) > 1 {
 150                 sel_date = os.Args[1]
 151         }
 152
 153         // open xml file
 154         file, err := os.Open("c13_skr03.gnucash")
 155         if err != nil {
 156                 fmt.Println("Error opening file:", err)
 157                 return
 158         }
 159         defer file.Close()
 160
 161         // read xml file
 162         xmldata, err := ioutil.ReadAll(file)
 163         if err != nil {
 164                 fmt.Println("Error reading file:", err)
 165                 return
 166         }
 167
 168         // unmarshal xml data
 169         err = xml.Unmarshal(xmldata,&data)
 170         if err != nil {
 171                 fmt.Println("Error unmarshaling xml data:", err)
 172                 return
 173         }
 174
 175         // whooha, this is our data!
 176         fmt.Println("Parsed accounts:",len(data.Accnt))
 177         fmt.Println("Parsed transactions:",len(data.Trn))
 178         fmt.Println("")
 179
 180         accnt := make(map[string]amap)
 181
 182         for ac := range data.Accnt {
 183                 aid := data.Accnt[ac].AccountId
 184                 pid := data.Accnt[ac].ParentId
 185                 for iac := range iaa {
 186                         // consider account if pid or aid matches
 187                         if pid == iaa[iac].id || aid == iaa[iac].id {
 188                                 taxval := iaa[iac].taxval
 189                                 for ec := range n2s_exc {
 190                                         if strings.Contains(data.Accnt[ac].Name,
 191                                                             n2s_exc[ec]) {
 192                                                 taxval=7
 193                                                 break
 194                                         }
 195                                 }
 196                                 for ec := range n2z_exc {
 197                                         if strings.Contains(data.Accnt[ac].Name,
 198                                                             n2z_exc[ec]) {
 199                                                 taxval=0
 200                                                 break
 201                                         }
 202                                 }
 203                                 accnt[aid]=amap{
 204                                         pid,
 205                                         ac,
 206                                         taxval,
 207                                         iaa[iac].buy,
 208                                         iaa[iac].tax,
 209                                 }
 210                                 break
 211                         }
 212                 }
 213         }
 214
 215         // check transactions ...
 216         for tc := range data.Trn {
 217                 // check balance ...
 218                 check_balance(&data.Trn[tc],accnt,sel_date)
 219         }
 220
 221         // tax report
 222         fmt.Println("Umsatzsteuervoranmeldung (19% | 7%):")
 223         fmt.Println("------------------------------------")
 224         fmt.Println("Aufwendungen:",tax_report.Expenses[0],
 225                                     tax_report.Expenses[1]);
 226         fmt.Println("ohne Ausn.  :",tax_report.Expenses[0]-
 227                                     tax_report.ExpExc[0],
 228                                     tax_report.Expenses[1]-
 229                                     tax_report.ExpExc[1]);
 230         fmt.Println("gesch. Vst. :",int((tax_report.Expenses[0]*19)/100.0),
 231                                     int((tax_report.Expenses[1]*7)/100.0))
 232         fmt.Println("ohne Ausn.  :",int(((tax_report.Expenses[0]-
 233                                          tax_report.ExpExc[0])*19)/100.0),
 234                                     int(((tax_report.Expenses[1]-
 235                                          tax_report.ExpExc[1])*7)/100.0))
 236         fmt.Println("Vorsteuer   :",tax_report.InputTax[0],
 237                                     tax_report.InputTax[1],
 238                     "->",tax_report.InputTax[0]+tax_report.InputTax[1]);
 239         fmt.Println("------------------------------------")
 240         fmt.Println("Einnahmen   :",-tax_report.Receipts[0],
 241                                     -tax_report.Receipts[1]);
 242         fmt.Println("gesch. Ust. :",int((-tax_report.Receipts[0]*19)/100.0),
 243                                     int((-tax_report.Receipts[1]*7)/100.0));
 244         fmt.Println("Umsatzsteuer:",-tax_report.SalesTax[0],
 245                                     -tax_report.SalesTax[1]);
 246         fmt.Println("------------------------------------")
 247
 248         // summed accounts
 249         fmt.Println("")
 250         fmt.Println("Summen einiger Konten:")
 251         fmt.Println("----------------------")
 252         for sac := range summed_accounts {
 253                 saccnt := summed_accounts[sac]
 254                 fmt.Println(" Konto: ",saccnt.name)
 255                 fmt.Println("     +: ",saccnt.valplus)
 256                 fmt.Println("     -: ",saccnt.valminus)
 257         }
 258 }
 259
 260 func check_balance(ta *Transaction,accnt map[string]amap,sel_date string) bool {
 261
 262         // check date
 263         tdate := strings.Fields(ta.Date)[0]
 264         if !strings.Contains(tdate,sel_date) {
 265                 return true
 266         }
 267
 268         // exceptions
 269         tv_ow := -1
 270         for ec := range n2s_exc_ta {
 271                 if strings.Contains(ta.Description,n2s_exc_ta[ec]) {
 272                         tv_ow=7
 273                         break
 274                 }
 275         }
 276         for ec := range n2z_exc_ta {
 277                 if strings.Contains(ta.Description,n2z_exc_ta[ec]) {
 278                         tv_ow=0
 279                         break
 280                 }
 281         }
 282
 283         // [taxval: 19=0 7=1][tax: no=0 yes=1][buy: no=0 yes=1]
 284         var sum [2][2][2]int
 285
 286         // loop over splits within the transaction
 287         for sc := range ta.Spl {
 288                 aid := ta.Spl[sc].AccountId
 289                 //accnt[aid].tax
 290                 // loop over all considered accounts (defined earlier as global)
 291                 for iac := range iaa {
 292                         tv := int(0)
 293                         // taxval changed by exception
 294                         if tv_ow != -1 {
 295                                 if tv_ow == 7 {
 296                                         tv=1
 297                                 }
 298                                 if tv_ow == 0 {
 299                                         // reset taxvalues of involved accounts
 300                                         // (to drop an error)
 301                                         if accnt[aid].tax {
 302                                                 if accnt[aid].taxval==7 {
 303                                                         tv=1
 304                                                 }
 305                                         } else {
 306                                                 continue
 307                                         }
 308                                 }
 309                         // taxval as defined by account
 310                         } else {
 311                                 if accnt[aid].taxval == 0 {
 312                                         if accnt[aid].tax {
 313                                                 fmt.Println("FATAL!");
 314                                         }
 315                                         continue
 316                                 }
 317                                 if accnt[aid].taxval == 7 {
 318                                         tv = 1
 319                                 }
 320                         }
 321                         // tax
 322                         tax := int(0)
 323                         if iaa[iac].tax {
 324                                 tax = 1
 325                         }
 326                         // buy
 327                         buy := int(0)
 328                         if iaa[iac].buy {
 329                                 buy = 1
 330                         }
 331                         // match! add to sum and break.
 332                         match := bool(false)
 333                         // check pids if ...
 334                         if tax == 0 && buy == 1 {
 335                                 _, exists := accnt[aid]
 336                                 if exists {
 337                                         // pids
 338                                         if accnt[aid].pid == iaa[iac].id {
 339                                                 match = true
 340                                         }
 341                                 }
 342                         }
 343                         // ... however, always check aids
 344                         if aid == iaa[iac].id {
 345                                 match = true
 346                         }
 347                         if match {
 348                                 inc, _ := strconv.Atoi(strings.TrimSuffix(ta.Spl[sc].Value,"/100"))
 349                                 sum[tv][tax][buy] += inc
 350                                 break
 351                         }
 352                 }
 353                 // now get the sums
 354                 for sac := range summed_accounts {
 355                         if summed_accounts[sac].aid == aid {
 356                                 inc, _ := strconv.Atoi(strings.TrimSuffix(ta.Spl[sc].Value,"/100"))
 357                                 if inc >= 0 {
 358                                         summed_accounts[sac].valplus += inc
 359                                 } else {
 360                                         summed_accounts[sac].valminus += inc
 361                                 }
 362                         }
 363                 }
 364         }
 365
 366         // check for exceptions
 367         exc := false
 368         for ec := range trn_exc {
 369                 if strings.Contains(ta.Description,trn_exc[ec]) {
 370                         exc = true
 371                         break
 372                 }
 373         }
 374         //for ac := range accountlist {
 375         //      if strings.Contains(data.Accnt[anum].Name,accountlist[ac]){
 376         //              return true
 377         //      }
 378         //}
 379
 380         // tax report
 381         for tv := 0; tv<2; tv++ {
 382                 tax_report.Expenses[tv] += sum[tv][0][1]
 383                 tax_report.InputTax[tv] += sum[tv][1][1]
 384                 tax_report.Receipts[tv] += sum[tv][0][0]
 385                 tax_report.SalesTax[tv] += sum[tv][1][0]
 386                 if exc {
 387                         tax_report.ExpExc[tv] += sum[tv][0][1]
 388                         tax_report.ITExc[tv] += sum[tv][1][1]
 389                 }
 390         }
 391
 392         // check
 393         var expected [2]int
 394         check := true
 395         for buy := 0; buy < 2; buy++ {
 396                 expected[0]=int((sum[0][0][buy]*19)/100.0)
 397                 expected[1]=int((sum[1][0][buy]*7)/100.0)
 398                 for tv :=0; tv < 2; tv++ {
 399                         if expected[tv] < sum[tv][1][buy]-1 ||
 400                            expected[tv] > sum[tv][1][buy]+1 {
 401                                 var sb, st string
 402                                 if buy == 0 {
 403                                         sb = "Umsatzsteuer"
 404                                 } else {
 405                                         sb = "Vorsteuer"
 406                                 }
 407                                 if tv == 0 {
 408                                         st = "19%"
 409                                 } else {
 410                                         st = " 7%"
 411                                 }
 412                                 check = false
 413                                 fmt.Printf("%s %s: ",sb,st);
 414                                 fmt.Printf("Erwarte %d statt %d aus %d! ",
 415                                            expected[tv],
 416                                            sum[tv][1][buy],sum[tv][0][buy]);
 417                                 if(!exc) {
 418                                         fmt.Printf("\n");
 419                                 } else {
 420                                         fmt.Printf("Ausnahme greift!\n");
 421                                         fmt.Printf("(%s)\n\n",ta.Description)
 422                                 }
 423                         }
 424                 }
 425         }
 426         if !check && !exc {
 427                 fmt.Println("am",ta.Date)
 428                 fmt.Printf("(%s)\n",ta.Description)
 429                 fmt.Println("Beteiligte Konten:")
 430                 for ic := range ta.Spl {
 431                         found := strings.TrimSuffix(ta.Spl[ic].Value,"/100")
 432                         aid := ta.Spl[ic].AccountId
 433                         _, exists := accnt[aid]
 434                         if exists {
 435                                 num := accnt[aid].num
 436                                 fmt.Printf("  %s => %s\n",data.Accnt[num].Name,
 437                                                           found)
 438                         } else {
 439                                 fmt.Printf("  %s => %s\n",aid,found)
 440                         }
 441                 }
 442                 fmt.Println("")
 443         }
 444
 445         return check
 446 }
 447
 448 func round(v float64) int {
 449         if v < 0.0 {
 450                 v -= 0.5
 451         } else {
 452                 v += 0.5
 453         }
 454         return int(v)
 455 }
 456