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

   1 <?php
   2 /**
   3  * Table Definition for accnt
   4  */
   5 require_once 'DB/DataObject.php';
   6
   7 class Pman_Xtuple_DataObjects_Accnt extends DB_DataObject
   8 {
   9     ###START_AUTOCODE
  10     /* the code below is auto generated do not remove the above tag */
  11
  12     public $__table = 'accnt';               // table name
  13     public $accnt_id;                        // int4(4)  not_null default_nextval%28%28accnt_accnt_id_seq%29%3A%3Aregclass%29 primary_key
  14     public $accnt_number;                    // text(-1)
  15     public $accnt_descrip;                   // text(-1)
  16     public $accnt_comments;                  // text(-1)
  17     public $accnt_profit;                    // text(-1)
  18     public $accnt_sub;                       // text(-1)
  19     public $accnt_type;                      // bpchar(-1)  not_null
  20     public $accnt_extref;                    // text(-1)
  21     public $accnt_company;                   // text(-1)
  22     public $accnt_closedpost;                // bool(1)
  23     public $accnt_forwardupdate;             // bool(1)
  24     public $accnt_subaccnttype_code;         // text(-1)
  25     public $accnt_curr_id;                   // int4(4)  default_basecurrid%28%29
  26     public $accnt_active;                    // bool(1)  not_null default_true
  27     public $accnt_name;                      // text(-1)
  28     public $accnt_code_alt;                  // text default ''
  29     public $accnt_descrip_alt;               // text default ''
  30
  31
  32     /**
  33     * Getter / Setter for $accnt_company
  34     *
  35     * @param    mixed   (optional) value to assign
  36     * @access   public
  37     */
  38     public function company() {
  39         return func_num_args() ? $this->link('accnt_company', func_get_arg(0)) : $this->link('accnt_company');
  40     }
  41
  42    /**
  43     * Getter / Setter for $accnt_curr_id
  44     *
  45     * @param    mixed   (optional) value to assign
  46     * @access   public
  47     */
  48     public function curr() {
  49         return func_num_args() ? $this->link('accnt_curr_id', func_get_arg(0)) : $this->link('accnt_curr_id');
  50     }
  51
  52
  53     /* the code above is auto generated do not remove the tag below */
  54     ###END_AUTOCODE
  55     function   applyFilters($q, $authUser, $roo)
  56     {
  57         $dt = date('Y-m-01', isset($q['_as_of']) ? strtotime($q['_as_of']) : time());
  58
  59
  60         // check all the currencies..
  61         $ac = $this->factory($this->tableName());
  62         $ac->selectAdd();
  63         $ac->accnt_active = 1;
  64         $ac->selectAdd('distinct(accnt_curr_id) as accnt_curr_id');
  65         $curs = $ac->fetchAll('accnt_curr_id');
  66
  67
  68         DB_DataObject::Factory('curr_rate')->hasRates(
  69                     $roo,
  70                     isset($q['_as_of']) ? $q['_as_of'] : false,
  71                     $curs
  72         );
  73
  74
  75
  76         if(isset($q['_with_xt_balances'])){
  77
  78             $this->selectAdd("
  79                 (SELECT
  80                     SUM(trialbal_ending -  trialbal_beginning)
  81
  82                     AS
  83                         trialbal_ending
  84                     FROM
  85                          trialbal
  86                     LEFT JOIN
  87                         period
  88                     ON
  89                         period_id = trialbal_period_id
  90                      WHERE
  91                             trialbal_accnt_id=accnt_id
  92                         AND
  93                              period_start <= '$dt'
  94                 )  as balance_base
  95             ");
  96
  97             $this->orderBy('accnt_subaccnttype_code ASC, accnt_descrip ASC');
  98         }
  99
 100         if(isset($q['_general_ledger'])){
 101             $this->orderBy("accnt_type asc,accnt_subaccnttype_code asc, accnt_descrip asc");
 102         }
 103
 104
 105         if (isset($q['_with_balances']))
 106         {
 107             // all gltrans are in HKD.
 108
 109             // for hk office - the hkd value is line/by/line correct.
 110
 111             // if baseCurrId() == getcurrid('HKD')
 112             // for the sg office hkd value ==
 113             // check we have rates..
 114
 115             //DB_DataObject::debugLevel(1);
 116             $p = DB_DataObject::factory('period');
 117             if (!$p->get('period_start', $dt)) {
 118                 // get the last period
 119                 $p = DB_DataObject::factory('period');
 120                 $p->orderBy('period_start DESC');
 121                 $p->limit(1);
 122                 $p->find(True);
 123
 124                 //$roo->jerr("period does not exist");
 125             }
 126
 127
 128             // for trial balances - if it's not an asset or Liability..
 129             // then ending is only the end of that period...
 130
 131             $this->selectAdd("
 132
 133
 134
 135
 136                 (SELECT
 137                         currtocurr(
 138                             baseCurrId(),
 139                             getcurrid('HKD' ),
 140                             SUM( CASE WHEN accnt_type IN ( 'A','E') THEN
 141                                        (trialbal_ending -  trialbal_beginning)   * -1
 142                                 ELSE
 143                                        trialbal_ending -  trialbal_beginning
 144                                 END ),
 145                             '{$p->period_end}'
 146                         )
 147
 148                      FROM
 149                              trialbal
 150                     LEFT JOIN
 151                         period
 152                     ON
 153                         period_id = trialbal_period_id
 154                     WHERE
 155                             trialbal_accnt_id=accnt_id
 156                         AND
 157                             period_start <= '$dt'
 158                 )  as balance_hkd,
 159
 160
 161
 162
 163
 164                  (SELECT
 165                         CASE WHEN accnt_type IN ( 'A','E') THEN
 166                                SUM(trialbal_ending -  trialbal_beginning)  * -1
 167
 168                         ELSE
 169                                SUM(trialbal_ending -  trialbal_beginning)
 170                         END as trialbal_ending
 171
 172                      FROM
 173                          trialbal
 174                     LEFT JOIN
 175                         period
 176                     ON
 177                         period_id = trialbal_period_id
 178                      WHERE
 179                             trialbal_accnt_id=accnt_id
 180                         AND
 181                              period_start <= '$dt'
 182                 )  as balance_base,
 183
 184
 185                  (SELECT
 186                         CASE WHEN accnt_type IN (  'A', 'E') THEN
 187                                trialbal_ending   * -1
 188                         ELSE
 189                                trialbal_ending
 190                         END as trialbal_ending
 191
 192                      FROM
 193                          trialbal
 194                     LEFT JOIN
 195                         period
 196                     ON
 197                         period_id = trialbal_period_id
 198                      WHERE
 199                             trialbal_accnt_id=accnt_id
 200                         AND
 201                             period_start =  '$dt'
 202
 203                 )  as balance_base_soy,
 204
 205                      (SELECT
 206                         currtocurr(
 207                             baseCurrId(),
 208                             accnt_curr_id,
 209                             SUM( CASE WHEN accnt_type IN ( 'A','E') THEN
 210                                        (trialbal_ending -  trialbal_beginning)   * -1
 211                                 ELSE
 212                                        trialbal_ending -  trialbal_beginning
 213                                 END
 214                             ),
 215                             '{$p->period_end}'
 216                         )
 217
 218                      FROM
 219                              trialbal
 220                     LEFT JOIN
 221                         period
 222                     ON
 223                         period_id = trialbal_period_id
 224                     WHERE
 225                             trialbal_accnt_id=accnt_id
 226                         AND
 227                             period_start <= '$dt'
 228                 )  as balance ,
 229                  (SELECT curr_name from curr_symbol where curr_id =  baseCurrId()) as base_curr,
 230
 231                 {$p->pid()} as period_id
 232
 233          ");
 234
 235            $this->selectAdd("
 236
 237                 (SELECT
 238                     ending
 239                     FROM
 240                     netsuite_balance
 241                     WHERE
 242                     netsuite_balance.accnt_id = accnt.accnt_id
 243                     AND
 244                     period_id = {$p->pid()}
 245                     AND
 246                     curr_id = accnt_curr_id
 247                     LIMIT 1
 248                 ) as nsbalance,
 249
 250                 (SELECT
 251                     COALESCE(MAX(curr_id),0) as curr_id
 252                     FROM
 253                     netsuite_balance
 254                     WHERE
 255                     netsuite_balance.accnt_id = accnt.accnt_id
 256                     AND
 257                     period_id = {$p->pid()}
 258                     LIMIT 1
 259                 ) as nscurr_id,
 260
 261
 262                 (SELECT
 263                     base_ending
 264                     FROM
 265                     netsuite_balance
 266                     WHERE
 267                     netsuite_balance.accnt_id = accnt.accnt_id
 268                     AND
 269                     period_id = {$p->pid()}
 270                     LIMIT 1
 271                 ) as nsbalance_base,
 272
 273                 (SELECT
 274                     base_close
 275                     FROM
 276                     netsuite_balance
 277                     WHERE
 278                     netsuite_balance.accnt_id = accnt.accnt_id
 279                     AND
 280                     period_id = {$p->pid()}
 281                     LIMIT 1
 282                 ) as base_close,
 283
 284                 (SELECT
 285                     id
 286                     FROM
 287                     netsuite_balance
 288                     WHERE
 289                     netsuite_balance.accnt_id = accnt.accnt_id
 290                     AND
 291                     period_id = {$p->pid()}
 292                     LIMIT 1
 293                 ) as nsbalance_id
 294
 295
 296
 297
 298
 299            ");
 300
 301            // $this->whereAdd('accnt_curr_id NOT IN ( 9,6) ');
 302             $this->whereAdd("accnt_id IN (
 303                     select distinct(trialbal_accnt_id)
 304                         FROM
 305                         trialbal
 306                         LEFT JOIN
 307                         period
 308                         ON
 309                         period_id = trialbal_period_id
 310
 311                         WHERE
 312                         period_start <= '$dt'::date
 313                         AND
 314                         (
 315                             trialbal_credits != 0.0
 316                             OR
 317                             trialbal_debits != 0.0
 318                             OR
 319                             trialbal_ending != 0.0
 320                             OR
 321                             trialbal_beginning != 0.0
 322                         )
 323                     )
 324             ");
 325             $this->orderBy();
 326             $this->orderBy('accnt_subaccnttype_code ASC, accnt_descrip ASC');
 327         }
 328
 329         if(!empty($q['search']['name'])){
 330             $this->whereAdd("
 331                 accnt_name ILIKE '{$this->escape($q['search']['name'])}%'
 332                 OR
 333                 accnt_descrip ILIKE '{$this->escape($q['search']['name'])}%'
 334                 OR
 335                 accnt_descrip_alt ILIKE '{$this->escape($q['search']['name'])}%'
 336             ");
 337         }
 338
 339     }
 340
 341     function toRooArray($q)
 342     {
 343         $ret = $this->toArray();
 344         return $ret;
 345     /*
 346         if (empty($q['_with_balances'])) {
 347             return $ret;
 348         }
 349         static $netsuite = false;
 350         if (!$netsuite) {
 351             $netsuite= $this->oldParse();
 352         }
 353
 354         //$ret['balance_ns'] = isset($netsuite[$this->accnt_number]) ?
 355         //        $netsuite[$this->accnt_number]->netsuite_balance : 0.0;
 356         return $ret;
 357         */
 358
 359     }
 360
 361
 362     function oldParse()
 363     {
 364         $ff = HTML_FlexyFramework::get();
 365         $ff->page->baseURL;
 366         $which = array_pop(explode('/', $ff->page->baseURL));
 367         $cn = $which == 'hk.php' ? 'HK' : 'SG';
 368
 369         $har = explode('/', realpath(__FILE__));
 370         $home = '/home/alan';
 371         $fn = "$home/Dropbox/xtuple_working/old_database_snapshot/$cn/Netsuite_Account.sql.json.all";
 372         $ar = file($fn);
 373        // echo '<PRE>';print_R($ar);exit;
 374         $ret = array();
 375         foreach($ar as $i=>$l) {
 376             if (!$i) continue;
 377             $line = json_decode($l);
 378
 379             $ret[$line->id] =   (object)array(
 380                     'id' => $line->id ,
 381                     'description' => $line->description,
 382                     'netsuite_balance' => $line->balance,
 383                     'acctType' => $line->acctType,
 384                     'is_old' => 1
 385                 );
 386         }
 387         return $ret;
 388
 389
 390
 391     }
 392     // used by gltrans reports?
 393     function multiplier() {
 394         $map = array(
 395                 'Q' => 1,
 396                 'L' =>  1,
 397                 'A' => -1,
 398                 'E' => -1,
 399                 'R' => 1,
 400         );
 401         return $map[$this->accnt_type];
 402     }
 403
 404     function defaults()
 405     {
 406         return array(
 407             'accnt_descrip' => '',
 408             'accnt_comments' => '',
 409             'accnt_extref' => '',
 410             'accnt_closedpost'=> false,
 411             'accnt_forwardupdate' => false,
 412             'accnt_active' => true,
 413         );
 414     }
 415
 416     /*
 417      * public $accnt_id;                        // int4(4)  not_null default_nextval%28%28accnt_accnt_id_seq%29%3A%3Aregclass%29 primary_key
 418     public $accnt_number;                    // text(-1)
 419     public $accnt_descrip;                   // text(-1)
 420     public $accnt_comments;                  // text(-1)
 421     public $accnt_profit;                    // text(-1)
 422     public $accnt_sub;                       // text(-1)
 423     public $accnt_type;                      // bpchar(-1)  not_null
 424     public $accnt_extref;                    // text(-1)
 425     public $accnt_company;                   // text(-1)
 426     public $accnt_closedpost;                // bool(1)
 427     public $accnt_forwardupdate;             // bool(1)
 428     public $accnt_subaccnttype_code;         // text(-1)
 429     public $accnt_curr_id;                   // int4(4)  default_basecurrid%28%29
 430     public $accnt_active;                    // bool(1)  not_null default_true
 431     public $accnt_name;
 432      *
 433      */
 434
 435     function joinAddBankaccnt()
 436     {
 437         $this->_join .= '
 438             LEFT JOIN
 439                 bankaccnt
 440             ON
 441                 bankaccnt_accnt_id = accnt_id
 442         ';
 443         $bankaccnt = DB_DataObject::Factory('bankaccnt');
 444         $this->selectAs($bankaccnt, '%s');
 445
 446     }
 447
 448     function importFromArray($roo, $accounts)
 449     {
 450         foreach ($accounts as $account){
 451             $accnt = DB_DataObject::factory('accnt');
 452             if($accnt->get('accnt_name', $account['accnt_name'])){
 453                 continue;
 454             }
 455             $accnt->setFrom($accnt->defaults());
 456
 457             $curr = DB_DataObject::factory('curr_symbol');
 458             if(!$curr->get('curr_abbr', $account['accnt_curr_abbr'])){
 459                 $roo->jerr("Missing currency : {$account['accnt_curr_abbr']}");
 460             }
 461             $accnt->accnt_number = $account['accnt_number'];
 462             $accnt->accnt_descrip = $account['accnt_descrip'];
 463             $accnt->accnt_profit = $account['accnt_profit'];
 464             $accnt->accnt_sub = $account['accnt_sub'];
 465             $accnt->accnt_type = $account['accnt_type'];
 466             $accnt->accnt_company = $account['accnt_company'];
 467             $accnt->accnt_subaccnttype_code = $account['accnt_subaccnttype_code'];
 468             $accnt->accnt_name = $account['accnt_name'];
 469
 470             $accnt->insert();
 471             //$roo->jerr($accnt->accnt_name);exit;
 472             if(empty($account['bankaccnt_id'])){
 473                 continue;
 474             }
 475
 476             $bankaccnt = DB_DataObject::factory('bankaccnt');
 477             $bankaccnt->setFrom($account);
 478
 479             $curr = DB_DataObject::factory('curr_symbol');
 480             if(!$curr->get('curr_abbr', $account['bankaccnt_curr_abbr'])){
 481                 $roo->jerr("Missing currency : {$account['bankaccnt_curr_abbr']}");
 482             }
 483             $bankaccnt->bankaccnt_curr_id = $curr->pid();
 484             $bankaccnt->bankaccnt_accnt_id = $accnt->pid();
 485             $bankaccnt->insert();
 486         }
 487     }
 488
 489     function checkAlternativeCode($roo, $download = false)
 490     {
 491         /*
 492          *  check the alternative code
 493          *  if an account has transactions and there is not a alt code for it - then display an error
 494          */
 495         $errors = array();
 496         $accnt = DB_DataObject::factory('accnt');
 497         $accnt->selectAdd();
 498         $accnt->selectAdd("
 499             accnt_id,
 500             accnt_code_alt,
 501             CASE WHEN (accnt_descrip_alt IS NOT NULL AND accnt_descrip_alt != '') THEN
 502                 accnt_descrip_alt
 503             ELSE
 504                 accnt_descrip
 505             END AS accnt_descrip
 506         ");
 507         $accnt->orderBy('accnt_code_alt DESC');
 508         $accnt->find();
 509         while ($accnt->fetch()){
 510             if(!empty($accnt->accnt_code_alt)){
 511                 $a = DB_DataObject::factory('accnt');
 512                 $a->accnt_code_alt = $accnt->accnt_code_alt;
 513                 if($a->count() > 1){
 514                     $errors[] = "Duplicate alternative code : " . $accnt->accnt_code_alt . " - " . $accnt->accnt_descrip;
 515                 }
 516                 continue;
 517             }
 518
 519             $gltrans = DB_DataObject::factory('gltrans');
 520             $gltrans->selectAdd();
 521             $gltrans->selectAdd('gltrans_id');
 522             $gltrans->whereAdd("
 523                 gltrans_accnt_id = {$accnt->accnt_id}
 524                 AND
 525                 NOT gltrans_deleted
 526             ");
 527             if(!$gltrans->count()){
 528                 continue;
 529             }
 530
 531             $errors[] = "Missing alternative code : " . $accnt->accnt_descrip;
 532         }
 533
 534         $errors = array_filter($errors);
 535
 536         if(!empty($errors)){
 537             if($download){
 538                 $roo->jerr(implode("\n", $errors), array(), 'text/plain');
 539             }
 540
 541             $roo->jerr(implode("\n", $errors));
 542         }
 543
 544         return true;
 545     }
 546
 547 }