<?php
/**
 * Table Definition for accnt
 */
require_once 'DB/DataObject.php';

class Pman_Xtuple_DataObjects_Accnt extends DB_DataObject 
{
    ###START_AUTOCODE
    /* the code below is auto generated do not remove the above tag */

    public $__table = 'accnt';               // table name
    public $accnt_id;                        // int4(4)  not_null default_nextval%28%28accnt_accnt_id_seq%29%3A%3Aregclass%29 primary_key
    public $accnt_number;                    // text(-1)  
    public $accnt_descrip;                   // text(-1)  
    public $accnt_comments;                  // text(-1)  
    public $accnt_profit;                    // text(-1)  
    public $accnt_sub;                       // text(-1)  
    public $accnt_type;                      // bpchar(-1)  not_null
    public $accnt_extref;                    // text(-1)  
    public $accnt_company;                   // text(-1)  
    public $accnt_closedpost;                // bool(1)  
    public $accnt_forwardupdate;             // bool(1)  
    public $accnt_subaccnttype_code;         // text(-1)  
    public $accnt_curr_id;                   // int4(4)  default_basecurrid%28%29
    public $accnt_active;                    // bool(1)  not_null default_true
    public $accnt_name;                      // text(-1)  
    public $accnt_code_alt;                  // text default ''
    public $accnt_descrip_alt;               // text default ''


    /**
    * Getter / Setter for $accnt_company
    *
    * @param    mixed   (optional) value to assign
    * @access   public
    */
    public function company() {
        return func_num_args() ? $this->link('accnt_company', func_get_arg(0)) : $this->link('accnt_company');
    }

   /**
    * Getter / Setter for $accnt_curr_id
    *
    * @param    mixed   (optional) value to assign
    * @access   public
    */
    public function curr() {
        return func_num_args() ? $this->link('accnt_curr_id', func_get_arg(0)) : $this->link('accnt_curr_id');
    }


    /* the code above is auto generated do not remove the tag below */
    ###END_AUTOCODE
    function   applyFilters($q, $authUser, $roo)
    {
        $dt = date('Y-m-01', isset($q['_as_of']) ? strtotime($q['_as_of']) : time());
        
        
        // check all the currencies..
        $ac = $this->factory($this->tableName());
        $ac->selectAdd();
        $ac->accnt_active = 1;
        $ac->selectAdd('distinct(accnt_curr_id) as accnt_curr_id');
        $curs = $ac->fetchAll('accnt_curr_id');
        
        
        DB_DataObject::Factory('curr_rate')->hasRates(
                    $roo,
                    isset($q['_as_of']) ? $q['_as_of'] : false,
                    $curs
        );

        
        if(isset($q['_with_xt_balances'])){
            
            $this->selectAdd("
                (SELECT 
                    SUM(trialbal_ending -  trialbal_beginning) 
                    
                    AS
                        trialbal_ending
                    FROM
                         trialbal
                    LEFT JOIN
                        period
                    ON
                        period_id = trialbal_period_id
                     WHERE
                            trialbal_accnt_id=accnt_id
                        AND
                             period_start <= '$dt'
                )  as balance_base
            ");
           
            $this->orderBy('accnt_subaccnttype_code ASC, accnt_descrip ASC');
        }
        
        if(isset($q['_general_ledger'])){
            $this->orderBy("accnt_type asc,accnt_subaccnttype_code asc, accnt_descrip asc");
        }
        
        
        if (isset($q['_with_balances']))
        {
            // all gltrans are in HKD.
            
            // for hk office - the hkd value is line/by/line correct.
            
            // if baseCurrId() == getcurrid('HKD')
            // for the sg office hkd value ==
            // check we have rates..
            
            //DB_DataObject::debugLevel(1);
            $p = DB_DataObject::factory('period');
            if (!$p->get('period_start', $dt)) {
                // get the last period
                $p = DB_DataObject::factory('period');
                $p->orderBy('period_start DESC');
                $p->limit(1);
                $p->find(True);
                
                //$roo->jerr("period does not exist");
            }
            
            
            // for trial balances - if it's not an asset or Liability..
            // then ending is only the end of that period...
            
            $this->selectAdd("
                             
                             
                (SELECT      
                        currtocurr(
                            baseCurrId(),
                            getcurrid('HKD' ),
                            SUM( CASE WHEN accnt_type IN ( 'A','E') THEN  
                                       (trialbal_ending -  trialbal_beginning)   * -1
                                ELSE 
                                       trialbal_ending -  trialbal_beginning 
                                END ),
                            '{$p->period_end}'
                        )
                     
                     FROM
                             trialbal
                    LEFT JOIN
                        period
                    ON
                        period_id = trialbal_period_id
                    WHERE
                            trialbal_accnt_id=accnt_id
                        AND
                            period_start <= '$dt'
                )  as balance_hkd,
                               
                
                 (SELECT      
                        CASE WHEN accnt_type IN ( 'A','E') THEN  
                               SUM(trialbal_ending -  trialbal_beginning)  * -1
                        
                        ELSE 
                               SUM(trialbal_ending -  trialbal_beginning)
                        END as trialbal_ending
                         
                     FROM
                         trialbal
                    LEFT JOIN
                        period
                    ON
                        period_id = trialbal_period_id
                     WHERE
                            trialbal_accnt_id=accnt_id
                        AND
                             period_start <= '$dt'
                )  as balance_base,
                 
                  
                 (SELECT      
                        CASE WHEN accnt_type IN (  'A', 'E') THEN  
                               trialbal_ending   * -1
                        ELSE 
                               trialbal_ending 
                        END as trialbal_ending
                         
                     FROM
                         trialbal
                    LEFT JOIN
                        period
                    ON
                        period_id = trialbal_period_id
                     WHERE
                            trialbal_accnt_id=accnt_id
                        AND
                            period_start =  '$dt'
                            
                )  as balance_base_soy,
                 
                     (SELECT      
                        currtocurr(
                            baseCurrId(),
                            accnt_curr_id,
                            SUM( CASE WHEN accnt_type IN ( 'A','E') THEN  
                                       (trialbal_ending -  trialbal_beginning)   * -1
                                ELSE 
                                       trialbal_ending -  trialbal_beginning 
                                END
                            ),
                            '{$p->period_end}'
                        )
                     
                     FROM
                             trialbal
                    LEFT JOIN
                        period
                    ON
                        period_id = trialbal_period_id
                    WHERE
                            trialbal_accnt_id=accnt_id
                        AND
                            period_start <= '$dt'
                )  as balance ,
                 (SELECT curr_name from curr_symbol where curr_id =  baseCurrId()) as base_curr,
                
                {$p->pid()} as period_id
              
         ");
             
           $this->selectAdd("    
                
                (SELECT
                    ending
                    FROM
                    netsuite_balance
                    WHERE
                    netsuite_balance.accnt_id = accnt.accnt_id
                    AND
                    period_id = {$p->pid()}
                    AND
                    curr_id = accnt_curr_id
                    LIMIT 1
                ) as nsbalance,
                
                (SELECT
                    COALESCE(MAX(curr_id),0) as curr_id
                    FROM
                    netsuite_balance
                    WHERE
                    netsuite_balance.accnt_id = accnt.accnt_id
                    AND
                    period_id = {$p->pid()}
                    LIMIT 1
                ) as nscurr_id,
            
                 
                (SELECT
                    base_ending
                    FROM
                    netsuite_balance
                    WHERE
                    netsuite_balance.accnt_id = accnt.accnt_id
                    AND
                    period_id = {$p->pid()}
                    LIMIT 1
                ) as nsbalance_base,
                   
                (SELECT
                    base_close
                    FROM
                    netsuite_balance
                    WHERE
                    netsuite_balance.accnt_id = accnt.accnt_id
                    AND
                    period_id = {$p->pid()}
                    LIMIT 1
                ) as base_close,
                
                (SELECT
                    id 
                    FROM
                    netsuite_balance
                    WHERE
                    netsuite_balance.accnt_id = accnt.accnt_id
                    AND
                    period_id = {$p->pid()}
                    LIMIT 1
                ) as nsbalance_id

                 
           ");
         
           // $this->whereAdd('accnt_curr_id NOT IN ( 9,6) '); 
            $this->whereAdd("accnt_id IN (
                    select distinct(trialbal_accnt_id)
                        FROM
                        trialbal
                        LEFT JOIN
                        period
                        ON
                        period_id = trialbal_period_id
                        
                        WHERE
                        period_start <= '$dt'::date
                        AND
                        (
                            trialbal_credits != 0.0 
                            OR
                            trialbal_debits != 0.0
                            OR
                            trialbal_ending != 0.0
                            OR
                            trialbal_beginning != 0.0
                        )
                    )
            "); 
            $this->orderBy();
            $this->orderBy('accnt_subaccnttype_code ASC, accnt_descrip ASC');
        }
        
        if(!empty($q['search']['name'])){
            $this->whereAdd("
                accnt_name ILIKE '{$this->escape($q['search']['name'])}%'
                OR
                accnt_descrip ILIKE '{$this->escape($q['search']['name'])}%'
                OR
                accnt_descrip_alt ILIKE '{$this->escape($q['search']['name'])}%'
            ");
        }
        
    }
    
    function toRooArray($q)  
    {
        $ret = $this->toArray();
        return $ret;
    /*
        if (empty($q['_with_balances'])) {
            return $ret;
        }
        static $netsuite = false;
        if (!$netsuite) {
            $netsuite= $this->oldParse();
        }
        
        //$ret['balance_ns'] = isset($netsuite[$this->accnt_number]) ?
        //        $netsuite[$this->accnt_number]->netsuite_balance : 0.0; 
        return $ret;
        */
        
    }
    
    
    function oldParse()
    {
        $ff = HTML_FlexyFramework::get();
        $ff->page->baseURL;
        $which = array_pop(explode('/', $ff->page->baseURL));
        $cn = $which == 'hk.php' ? 'HK' : 'SG';
        
        $har = explode('/', realpath(__FILE__));
        $home = '/home/alan';
        $fn = "$home/Dropbox/xtuple_working/old_database_snapshot/$cn/Netsuite_Account.sql.json.all";
        $ar = file($fn);
       // echo '<PRE>';print_R($ar);exit;
        $ret = array();
        foreach($ar as $i=>$l) {
            if (!$i) continue;
            $line = json_decode($l);
         
            $ret[$line->id] =   (object)array(
                    'id' => $line->id ,
                    'description' => $line->description,
                    'netsuite_balance' => $line->balance,
                    'acctType' => $line->acctType,
                    'is_old' => 1
                );
        }
        return $ret;
         

    }
    // used by gltrans reports?
    function multiplier() {
        $map = array(
                'Q' => 1,
                'L' =>  1,
                'A' => -1,
                'E' => -1,
                'R' => 1,
        );
        return $map[$this->accnt_type];
    }
    
    function defaults()
    {
        return array(
            'accnt_descrip' => '',
            'accnt_comments' => '',
            'accnt_extref' => '',
            'accnt_closedpost'=> false,
            'accnt_forwardupdate' => false,
            'accnt_active' => true,
        );
    }
    
    /*
     * public $accnt_id;                        // int4(4)  not_null default_nextval%28%28accnt_accnt_id_seq%29%3A%3Aregclass%29 primary_key
    public $accnt_number;                    // text(-1)  
    public $accnt_descrip;                   // text(-1)  
    public $accnt_comments;                  // text(-1)  
    public $accnt_profit;                    // text(-1)  
    public $accnt_sub;                       // text(-1)  
    public $accnt_type;                      // bpchar(-1)  not_null
    public $accnt_extref;                    // text(-1)  
    public $accnt_company;                   // text(-1)  
    public $accnt_closedpost;                // bool(1)  
    public $accnt_forwardupdate;             // bool(1)  
    public $accnt_subaccnttype_code;         // text(-1)  
    public $accnt_curr_id;                   // int4(4)  default_basecurrid%28%29
    public $accnt_active;                    // bool(1)  not_null default_true
    public $accnt_name;       
     * 
     */
    
    function joinAddBankaccnt()
    {
        $this->_join .= '
            LEFT JOIN
                bankaccnt
            ON
                bankaccnt_accnt_id = accnt_id
        ';
        $bankaccnt = DB_DataObject::Factory('bankaccnt');
        $this->selectAs($bankaccnt, '%s');
        
    }
    
    function importFromArray($roo, $accounts)
    {
        foreach ($accounts as $account){
            $accnt = DB_DataObject::factory('accnt');
            if($accnt->get('accnt_name', $account['accnt_name'])){
                continue;
            }
            $accnt->setFrom($accnt->defaults());
            
            $curr = DB_DataObject::factory('curr_symbol');
            if(!$curr->get('curr_abbr', $account['accnt_curr_abbr'])){
                $roo->jerr("Missing currency : {$account['accnt_curr_abbr']}");
            }
            $accnt->accnt_number = $account['accnt_number'];
            $accnt->accnt_descrip = $account['accnt_descrip'];
            $accnt->accnt_profit = $account['accnt_profit'];
            $accnt->accnt_sub = $account['accnt_sub'];
            $accnt->accnt_type = $account['accnt_type'];
            $accnt->accnt_company = $account['accnt_company'];
            $accnt->accnt_subaccnttype_code = $account['accnt_subaccnttype_code'];
            $accnt->accnt_name = $account['accnt_name'];
            
            $accnt->insert();
            //$roo->jerr($accnt->accnt_name);exit;
            if(empty($account['bankaccnt_id'])){
                continue; 
            }
            
            $bankaccnt = DB_DataObject::factory('bankaccnt');
            $bankaccnt->setFrom($account);
            
            $curr = DB_DataObject::factory('curr_symbol');
            if(!$curr->get('curr_abbr', $account['bankaccnt_curr_abbr'])){
                $roo->jerr("Missing currency : {$account['bankaccnt_curr_abbr']}");
            }
            $bankaccnt->bankaccnt_curr_id = $curr->pid();
            $bankaccnt->bankaccnt_accnt_id = $accnt->pid();
            $bankaccnt->insert();
        }
    }
    
    function checkAlternativeCode($roo, $download = false)
    {
        /*
         *  check the alternative code 
         *  if an account has transactions and there is not a alt code for it - then display an error
         */
        $errors = array();
        $accnt = DB_DataObject::factory('accnt');
        $accnt->selectAdd();
        $accnt->selectAdd("
            accnt_id,
            accnt_code_alt,
            CASE WHEN (accnt_descrip_alt IS NOT NULL AND accnt_descrip_alt != '') THEN
                accnt_descrip_alt
            ELSE
                accnt_descrip
            END AS accnt_descrip
        ");
        $accnt->orderBy('accnt_code_alt DESC');
        $accnt->find();
        while ($accnt->fetch()){
            if(!empty($accnt->accnt_code_alt)){
                $a = DB_DataObject::factory('accnt');
                $a->accnt_code_alt = $accnt->accnt_code_alt;
                if($a->count() > 1){
                    $errors[] = "Duplicate alternative code : " . $accnt->accnt_code_alt . " - " . $accnt->accnt_descrip;
                }
                continue;
            }
            
            $gltrans = DB_DataObject::factory('gltrans');
            $gltrans->selectAdd();
            $gltrans->selectAdd('gltrans_id');
            $gltrans->whereAdd("
                gltrans_accnt_id = {$accnt->accnt_id}
                AND
                NOT gltrans_deleted
            ");
            if(!$gltrans->count()){
                continue;
            }
            
            $errors[] = "Missing alternative code : " . $accnt->accnt_descrip;
        }
        
        $errors = array_filter($errors);
        
        if(!empty($errors)){
            if($download){
                $roo->jerr(implode("\n", $errors), array(), 'text/plain');
            }
            
            $roo->jerr(implode("\n", $errors));
        }
        
        return true;
    }
    
}