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