git.roojs.org Git - Pman.Xtuple/blob - DataObjects/Expense.php

   1 <?php
   2 /**
   3  * Table Definition for expense
   4  */
   5 require_once 'DB/DataObject.php';
   6
   7 class Pman_Xtuple_DataObjects_Expense extends DB_DataObject
   8 {
   9     ###START_AUTOCODE
  10     /* the code below is auto generated do not remove the above tag */
  11
  12     public $__table = 'expense';             // table name
  13     public $expense_id;                      // int4(4)  not_null default_nextval%28%28expense_id_seq%29%3A%3Aregclass%29 primary_key
  14     public $expense_accnt_id;                // int4(4)
  15     public $expense_emp_id;                  // int4(4)  not_null
  16     public $expense_number;                  // text(-1)  not_null
  17     public $expense_trandate;                // timestamptz(8)
  18     public $expense_created;                 // timestamptz(8)
  19     public $expense_modified;                // timestamptz(8)
  20     public $expense_duedate;                 // timestamptz(8)
  21     public $expense_memo;                    // text(-1)
  22     public $expense_status;                  // text(-1)
  23     public $expense_advance;                 // numeric(-1)  default_0.000
  24     public $expense_amount;                  // numeric(-1)  default_0.000
  25     public $expense_tax;                     // numeric(-1)  default_0.000
  26     public $expense_total;                   // numeric(-1)  default_0.000
  27     public $expense_posted;                   // numeric(-1)  default_0.000
  28     public $expense_comments;
  29
  30
  31    /**
  32     * Getter / Setter for $expense_accnt_id
  33     *
  34     * @param    mixed   (optional) value to assign
  35     * @access   public
  36     */
  37     public function accnt() {
  38         return $this->link('expense_accnt_id', func_get_args());
  39     }
  40
  41    /**
  42     * Getter / Setter for $expense_emp_id
  43     *
  44     * @param    mixed   (optional) value to assign
  45     * @access   public
  46     */
  47     public function emp() {
  48         return $this->link('expense_emp_id', func_get_args());
  49     }
  50
  51
  52     /* the code above is auto generated do not remove the tag below */
  53     ###END_AUTOCODE
  54     function applyFilters($q, $au, $roo)
  55     {
  56         // alway sapply perms.
  57
  58         if (!$roo->hasPerm('Xtuple.ExpensesAll','S')) {
  59             $au->cntct(); /// make sure an employee exists..
  60
  61             $this->expense_emp_id = $au->emp()->pid();
  62
  63
  64         }
  65
  66         $this->selectAdd("
  67             (SELECT curr_name from curr_symbol where curr_id = basecurrid() LIMIT 1 ) as base_curr_name,
  68             basecurrid()  as base_curr_id,
  69
  70             (select expcat_id from expcat where expcat_descrip = 'Expenses' LIMIT 1) as  def_expcat_id,
  71             (select expcat_descrip from expcat where expcat_descrip = 'Expenses' LIMIT 1) as  def_expcat_descrip,
  72
  73             expense_amount as expense_amount_ro,
  74             expense_tax as expense_tax_ro,
  75             expense_total as expense_total_ro
  76
  77         ");
  78
  79         if (!empty($q['_asExcel'])) {
  80           //  DB_DataObject::debugLevel(1);
  81             $this->expense_id = $q['expense_id'];
  82             $this->find(true);
  83             $this->toExcel($roo);
  84         }
  85
  86
  87
  88     }
  89
  90     function defaults() {
  91         return array(
  92             'expense_status' => 'Draft'
  93         );
  94
  95     }
  96
  97
  98     function beforeInsert($request,$roo)
  99     {
 100         if (empty($this->expense_accnt_id)) {
 101             $this->expense_accnt_id = $this->sqlValue('NULL');
 102         }
 103         // only needed on insert..
 104         if (empty($this->expense_emp_id)) {
 105             $this->expense_emp_id = $roo->authUser->emp()->pid();
 106         }
 107         foreach($this->defaults() as $k=>$v) {
 108             if (empty($this->$k)) {
 109                 $this->$k = $v;
 110             }
 111         }
 112     }
 113
 114
 115     function onInsert()
 116     {
 117         $this->expense_number = "EXP-".strtoupper(substr($this->database(), -2)) . '-'. $this->pid() ;
 118         $t = DB_DataObject::Factory('expense');
 119         $t->query("UPDATE expense SET  expense_number = '{$this->expense_number}'
 120                     WHERE expense_id = {$this->pid()}"
 121         );
 122
 123
 124     }
 125
 126     function beforeUpdate($old, $request,$roo)
 127     {
 128
 129
 130
 131         if (empty($this->expense_accnt_id)) {
 132             $this->expense_accnt_id = $this->sqlValue('NULL');
 133         }
 134
 135
 136
 137         if ($this->expense_posted) {
 138             $roo->jerr("you can not modify a posted expense");
 139         }
 140         if ($this->expense_status =='Paid In Full') {
 141             $roo->jerr("you can not modify a posted expense");
 142         }
 143
 144
 145     }
 146     function onUpdate($old,$q, $roo)
 147     {
 148
 149         if (!empty($q['_post'])) {
 150             $this->post($roo);
 151         }
 152
 153     }
 154
 155
 156     function items()
 157     {
 158         $ci = DB_DataObject::factory('expitem');
 159         $ci->autoJoin();
 160         $ci->expitem_expense_id  = $this->pid();
 161         $ci->orderBy('expitem_row ASC');
 162         //$ci->applyFilters(array(  ), $roo->authUser, $roo);
 163         return $ci->fetchAll();
 164     }
 165
 166
 167     function beforeDelete($dependants_array, $roo)
 168     {
 169         if ($this->expense_posted) {
 170             $roo->jerr("you can not delete a posted expense");
 171         }
 172
 173         if ($this->expense_status =='Paid In Full') {
 174             $roo->jerr("you can not modify a posted expense");
 175         }
 176
 177         /// delete depenedants...
 178         if (strlen($this->expense_status ) && $this->expense_status != 'Draft') {
 179             $roo->jerr("you can only delete draft expense reports");
 180         }
 181         // in theory check perms... - but not implemented...
 182
 183         $ei = $this->items();
 184         foreach($ei as $i) {
 185             $i->delete();
 186         }
 187         return true;
 188
 189
 190     }
 191
 192
 193     function post($roo)
 194     {
 195
 196         if ($this->expense_status != 'Pending Accounting Approval') {
 197             $roo->jerr("expenses are not pending approval by accounts");
 198         }
 199
 200         $xx = DB_DataObject::Factory('expense');
 201         $xx->get($this->pid());
 202
 203         // validate data...
 204         if (empty($xx->expense_accnt_id)) {
 205             $roo->jerr("no bank specified");
 206         }
 207         $items= $this->items() ;
 208         // next... verify that the rows have accounts.
 209
 210         $tot = 0.0;
 211         foreach($items as $i) {
 212             if (empty($i->expitem_expcat_id)) {
 213                 $roo->jerr("one of the lines is missing an expense category.");
 214             }
 215
 216             if (empty($i->expitem_memo)) {
 217                 $roo->jerr("one of the lines is missing a description.");
 218             }
 219             $tot = bcadd($tot, round($i->expitem_total,2), 2);
 220
 221         }
 222
 223
 224         $cur  = DB_DAtaObject::Factory('curr_symbol');
 225         $base_cur = $cur->base();
 226
 227         // finally try and post the thing..
 228
 229         $cn = $base_cur->curr_name;
 230         $vi = DB_DataObject::Factory('vendinfo');
 231         $vi->vend_number = "STAFF-{$cn}";
 232         if (!$vi->find(true)) {
 233             $roo->jerr("An vendor account 'STAFF-{$cn}' does not exist");
 234         }
 235
 236
 237         $taxtype  = DB_DataObject::Factory('taxtype');
 238         $taxtype->get('taxtype_name', 'Taxable');
 239
 240
 241         // verify that tax zone is correct.
 242
 243         $taxzone = DB_DataObject::Factory('taxzone'); // we use vend info..
 244         $taxzone->get('taxzone_code', 'NO TAX');
 245         if ($taxzone->pid() == $vi->vend_taxzone_id && $this->expense_tax > 0.0) {
 246             $roo->jerr("change the taxzone of the vendor 'STAFF-{$cn}' to not tax free ");
 247         }
 248
 249         $vohead = DB_DataObject::Factory('vohead');
 250         $vohead ->query('SELECT fetchVoNumber() AS vouchernumber');
 251         $vohead->fetch();
 252         $vn = $vohead->vouchernumber;
 253
 254         $vohead = DB_DataObject::Factory('vohead');
 255
 256         $vohead->setFrom($vohead->defaults());
 257
 258         $vohead->setFrom(array(
 259
 260
 261             'vohead_number'=>  $vn ,
 262             'vohead_invcnumber' => $this->expense_number,
 263             'vohead_docdate'=>  $this->expense_trandate,
 264             'vohead_distdate'=> $this->expense_trandate,
 265             'vohead_duedate'=>  $this->expense_trandate,
 266
 267             'vohead_amount'=>   $tot,
 268             'vohead_terms_id'=> $vi->vend_terms_id,
 269             'vohead_notes'=>    'Created from expense report',
 270             'vohead_curr_id'=>  $base_cur->pid(),
 271             //'vohead_pohead_id' => $po->pohead_id,
 272             'vohead_vend_id' => $vi->pid(),
 273             'vohead_taxtype_id'=> $taxtype->pid(),
 274             'vohead_taxzone_id'=>$vi->vend_taxzone_id,
 275             //'vohead_misc'=> 0
 276
 277
 278
 279         ));
 280         //$roo->jerr("total : $tot");
 281         //$roo->jerr(print_R($this,true));
 282
 283         $vohead->insert();
 284
 285
 286         foreach($items as $i) {
 287             $vodist = DB_DataObject::Factory('vodist');
 288
 289             $vodist->createFromExpItem($roo, $vohead, $i);
 290         }
 291         //$roo->jerr(print_r($vohead->distitems(),true));
 292
 293
 294         if ($this->expense_tax > 0.0) {
 295             $vodist = DB_DataObject::Factory('vodist');
 296             $vodist->createFromExpTax($roo, $vohead, $this->expense_tax);
 297         }
 298         $vd = DB_DataObject::Factory('vodist');
 299         $vd->query("SELECT postVoucher({$vohead->pid()},
 300                     fetchJournalNumber('AP-VO'), TRUE) AS result");
 301         $vd->fetch();
 302         if (!$vd->result || $vd->result < 0) {
 303             $roo->jerr("postVoucher() return $vd->result");
 304         }
 305         $old = clone($xx);
 306         $xx->expense_posted = 1;
 307         $xx->expense_status = 'Paid In Full';
 308         $xx->update($old);
 309
 310         $roo->jok("POSTED");
 311
 312     }
 313
 314
 315     function toExcel($roo)
 316     {
 317
 318
 319         //$ci->applyFilters(array(  ), $roo->authUser, $roo);
 320         $data  = $this->items();
 321
 322         $endrow = 11 + count($data);
 323
 324         //echo '<PRE>';print_r($data);
 325         require_once 'Pman/Core/SimpleExcel.php';
 326
 327         //print_R($this);exit;
 328
 329        $x = new Pman_Core_SimpleExcel(
 330             $data,
 331
 332             array(
 333            'formats' => array(
 334                 'left' => array('Align'=>'left'),
 335                 'money' => array('NumFormat' => '$#,##0.00;[RED]-$#,##0.00'),
 336                 'date' => array('NumFormat' => 'D/M/YYYY', 'Align'=>'left'),
 337                 'header' => array('Size' => 16,  'Bold' => 1)
 338             ),
 339
 340             'workbook' => 'Expense Report' . $this->expense_number,
 341             'head'  => array(
 342                 array("","","","","","", array("Expense Report", 'header')),
 343                 array("","","","", "","","Ref#", $this->expense_number),
 344                 array("","","","", "","","Employee:", $this->expense_emp_id_emp_name),
 345                 array("","","","", "","","Due:", array( Pman_Core_SimpleExcel::date($this->expense_trandate),'date')),
 346                 array("","","","", "","","Status:", $this->expense_status),
 347                 array("Summary:", $this->expense_memo),
 348                 array(),
 349                 array(),
 350             ),
 351
 352
 353             'cols' =>  array(
 354                 array(
 355                     'dataIndex' => 'expitem_row',
 356                     'header'  => 'Line#',
 357                     'width'  => 75,
 358                     'format' => 'left',
 359                 ),
 360                 array(
 361                     'header' => 'Date',
 362                     'width' => 75,
 363                     'dataIndex' => 'expitem_date',
 364                     'format' => 'date'
 365                 ),
 366                 array(
 367                  'dataIndex' => 'expitem_expcat_id_expcat_descrip',
 368                                 'header' => 'Category',
 369                                 'width' => 100,
 370                 ),
 371                 array(
 372                  'dataIndex' => 'expitem_memo',
 373                                 'header' => 'Description',
 374                                 'width' =>150,
 375
 376                 ),
 377                 array(
 378                  'dataIndex' => 'expitem_curr_id_curr_name',
 379                                 'header' => 'Currency',
 380                                 'width' =>75,
 381
 382                 ),
 383
 384                 array(
 385                             'header' => 'Amount',
 386                             'width' => 75,
 387                             'dataIndex' => 'expitem_amount_fc',
 388                             'format' => 'money'
 389                 ),
 390                 array(
 391                             'header' => 'Tax',
 392                             'width' => 75,
 393                             'dataIndex' => 'expitem_tax',
 394                             'format' => 'money'
 395                 ),array(
 396                             'header' => 'Total (in base currency)',
 397                             'width' => 75,
 398                             'dataIndex' => 'expitem_total',
 399                             'format' => 'money'
 400                 ),
 401
 402
 403
 404             ),
 405             'foot' => array(
 406                 array(),
 407                  array("","","","","","", "Total", array(
 408                         "=SUM(H11:H{$endrow})",
 409                         "money"
 410                 )),
 411
 412             )
 413         ));
 414
 415         $x->send($this->expense_number.'-'.date('Y-m-d').'.xls');
 416
 417
 418
 419
 420     }
 421
 422
 423 }