<?php

/**
 *
 *Import accounts xls  - based on accnt.export.sql from the metasql directory.
 *
 */

require_once 'Pman/Roo.php';

class Pman_Xtuple_Import_Accounts extends Pman_Roo
{
    static $cli_opts = array();
    /**
     *  get .. same as roo... 
     * 
     */
    
    function getAuth()
    {
        if (HTML_FlexyFramework::get()->cli) {
            return true;
        }
        return parent::getAuth();
    }
    
    
    function get($tab, $opts = [])
    {
        PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, array($this, 'onPearError'));
        //$this->jerr("INVALID");
        //DB_DataObject::DebugLevel(1);
        require_once 'File/Convert.php';
        $cp = $this->tempName('xls');
        copy(dirname(__FILE__).'/../Setup/accounts_sample.xls', $cp);
        $fc = new File_Convert($cp, 'application/vnd.ms-excel');
        //var_Dump($img->getStoreName());
        $csv = $fc->convert('text/csv');
        unlink($cp);
        //var_dump($csv);
        $this->importCsv($csv);
    }
    
    
    function post($tab  )
    {
        
        $this->sessionState(0); // turn off the session..
        PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, array($this, 'onPearError'));

        if (empty($_FILES['imageUpload'])) {
            $this->jerr("no file?!");
         
        }
        $img = DB_DataObject::Factory('images');
        $img->setFrom(array(
            'onid' => 0,
            'ontable' => 'accnt'
        ));
        $img->onUpload(false);
        
        require_once 'File/Convert.php';
        $fc = new File_Convert($img->getStoreName(), $img->mimetype );
        //var_Dump($img->getStoreName());
        $csv = $fc->convert('text/csv');
        //print_R($fc);exit;
        //var_dump($csv);exit;
        $this->importCsv($csv);
        $this->jok("Done");
    }
    function importCsv($csv)
    {
        
        ini_set("auto_detect_line_endings", true);

        
        $req = array(
                     "type",
                    "subaccnttype_code",
                    "profit",
                    "number",
                    "sub",
                    "descrip",
                    "comments",
                    "currency",
                    "active",
                    "ar_account",
                    "ap_account",
                    "sales_account",
                    "cost_account",
                    "warehouse_account",
                    "yearend_account",
                    "tax_account",
                    "tax_account_name",
                    "bankaccnt_type",
                    "bankaccnt_accntnumber"
            );

        $fh = fopen($csv,'r');
        // we need to break this into cols..
        $cols = false;
        $rows = array();
        while(false !== ($n = fgetcsv($fh,10000, ',', '"'))) {
             
            if (!$cols) {
                
                $cols = array();
                foreach($n as $k) {
                    
                    $cols[] = 'accnt_' .strtolower(trim(str_replace(' ', '_', $k)));
                }
                if (empty($cols)) {
                    continue;
                }
               // print_R($cols);
                foreach($req as $r) {
                    
                    if (!in_array('accnt_' .$r,$cols)) {
                        $missing = $r;
                        $cols = false;
                        break;
                        
                    }
                }
                if (empty($cols)) {
                    break;
                }
                continue;
            }
            $row = array();
            foreach($cols as $i=>$k) {
                 
                $row[$k] = $n[$i];
            }
            $rows[] = $row;
        }
         
        if (empty($cols)) {
            $this->jerr("could not find a column named $missing");
        }
        fclose($fh);
        unlink($csv);
        $accnts = array(
            'ar' => array(),
            'ap'=> array(), 
            'sales'=> array(),
            'cost'=> array(),  
        );
        foreach($rows as $r) {
            // do we have an account..
            $acc = DB_DAtaObject::Factory('accnt');
            
            $r['accnt_curr_id'] = DB_DAtaObject::Factory('curr_symbol')->lookup('curr_name', $r['accnt_currency'])->curr_id;
            
            if (!$acc->get('accnt_number', $r['accnt_number'])) {
                
                $acc->setFrom($acc->defaults());
                $acc->setFrom($r);
                
                $sa = DB_DAtaObject::factory('subaccnt');
                if (!$sa->get('subaccnt_number', $r['accnt_sub'])) {
                    $sa->setFrom(array(
                        'subaccnt_number', $r['accnt_sub'],
                        'subaccnt_descrip', "Sub Account " . $r['accnt_sub']
                        
                    ));
                    $sa->insert();
                }
                
                
                $sa = DB_DAtaObject::factory('prftcntr');
                if (!$sa->get('prftcntr_number', $r['accnt_profit'])) {
                    $sa->setFrom(array(
                        'prftcntr_number', $r['accnt_profit'],
                        'prftcntr_descrip', "Profit Center " . $r['accnt_profit']
                        
                    ));
                    $sa->insert();
                }
                
                
               
                
                
                $r['accnt_name'] = implode('-', array(
                    $acc->accnt_company,
                    $acc->accnt_profit,
                    $acc->accnt_number,
                     $acc->accnt_sub
                ));
               
                
                
                $acc->insert();
            }
            if (!empty($r['accnt_bankaccnt_type'])) {
                // bank account.
                $ba = DB_DataObject::factory('bankaccnt');
                if (!$ba->get('bankaccnt_accnt_id', $acc->pid())) {
                    $ba->setFrom($ba->defaults());
                    $ba->setFrom(array(
                       'bankaccnt_name' =>  $acc->accnt_descrip,
                       'bankaccnt_descrip' => $acc->accnt_descrip,
                       'bankaccnt_bankname' => 'BANK NAME',
                       'bankaccnt_accntnumber' => $r['accnt_bankaccnt_accntnumber'],
                       'bankaccnt_ar' => true,
                       'bankaccnt_ap' => true,
                       'bankaccnt_nextchknum' => 5000,
                       'bankaccnt_type' =>  $r['accnt_bankaccnt_type'],
                       'bankaccnt_accnt_id' => $acc->pid(),
                       'bankaccnt_curr_id' => $r['accnt_curr_id'],
                       'bankaccnt_notes' => '',
                       'bankaccnt_routing' => '',
                       'bankaccnt_ach_enabled' => false,
                       'bankaccnt_ach_origintype' => 'I'
                   ));
                    $ba->insert();
                }
            }
            // tax account.
            
            // cost cat..
            foreach(array(
                'ar',
                'ap',
                'sales',
                'cost',
                ) as $col) {
                if (strlen($r['accnt_'. $col.'_account'])) {
                    $accnts[$col][$r['accnt_'. $col.'_account']] = $acc->pid();
                }
            }
            if (strlen($r['accnt_tax_account'])) {
                $t = DB_DataObject::Factory('tax');
                if (!$t->get('tax_code',$r['accnt_tax_account'] )) {
                    $t->setFrom($t->default());
                    $t->tax_code = $r['accnt_tax_account'];
                    $t->tax_descrip = $r['accnt_tax_account_name'];
                    $t->tax_sales_accnt_id = $acc->pid();
                    $t->insert();
                }
            }
            if (!empty($r['yearend_account']) && strlen($r['yearend_account'])) {
                $t = DB_DataObject::Factory('metric');
                $t->query("select setmetric('YearEndEquityAccount', {$acc->pid()}::text)");

            }
            if (!empty($r['warehouse_account']) && strlen($r['warehouse_account'])) {
                $t = DB_DataObject::Factory('whsinfo');
                $t->query("UPDATE whsinfo set warehous_default_accnt_id=  {$acc->pid()}");
            }
             
            
        }
        
        
        
        foreach($accnts as $type => $vals) {
            $tbl = $type . ($type=='cost' ? 'cat' : 'accnt');
            $d = DB_DataObject::factory($tbl);
            
            switch($tbl) {
                case 'costcat':
                    $d->get('costcat_code','CATEGORY1');
                    break;
                
                case 'araccnt':
                    $d->araccnt_custtype_id = -1;
                    $d->araccnt_custtype = '.*';
                    $d->limit(1);
                    $d->find(true);
                    break;
                
                case 'apaccnt':
                    $d->apaccnt_vendtype_id = -1;
                    $d->apaccnt_vendtype = '.*';
                    $d->limit(1);
                    $d->find(true);
                    break;
                
                case 'salesaccnt':
                     $d->setFrom(array(
                        'salesaccnt_custtype_id'=> -1,
                        'salesaccnt_prodcat_id'=>-1,
                        'salesaccnt_warehous_id'=>-1,
                        'salesaccnt_custtype'=>'.*',
                        'salesaccnt_prodcat'=>'.*',
                    ));
                    $d->limit(1);
                    $d->find(true);
                    
            }
            foreach($vals as $v=>$id) {
                $v = strtolower($v);
                //echo $tbl.'_'.$v .'_accnt_id = ' . $id ."\n";
                $d->{$tbl.'_'.$v .'_accnt_id'} = $id;
            }
            if (!empty($d->{$tbl . '_id'})) {
                $d->update();
            } else {
                $d->insert();
            }
            
        }
        
         
    }
     
        
        
         
}