<?php

require_once 'Pman/Roo.php';

class Pman_Xtuple_Import_Customers extends Pman_Roo
{
    function getAuth()
    {
        if (HTML_FlexyFramework::get()->cli) {
            return true;
        }
        return parent::getAuth();
    }
    
    function post()
    {
        $this->transObj = DB_DataObject::Factory('vendinfo');
        
        $this->transObj->query('BEGIN');
        
        PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, array($this, 'onPearError'));
        
        $img = DB_DataObject::Factory('images');
        $img->setFrom(array(
            'onid' => 0,
            'ontable' => 'ipshead'
        ));
        $img->onUpload(false);
        
        require_once 'File/Convert.php';
        $fc = new File_Convert($img->getStoreName(), $img->mimetype );
        $csv = $fc->convert('text/csv');
        $this->importCsv($csv);
    }
    
    function importCsv($csv)
    {
        ini_set("auto_detect_line_endings", true);
        
        $fh = fopen($csv, 'r');
        if (!$fh) {
            $this->jerr("invalid file");
        }
        
        $req = array(
            'ACTIVE STATUS', 'CUSTOMER', 'BALANCE', 'BALANCE TOTAL', 'COMPANY', 'MR, MRS',
            'FIRST NAME', 'M.I.', 'LAST NAME', 'CONTACT', 'PHONE', 'FAX', 'ALT. PHONE', 'ALT. CONTACT', 'EMAIL', 'BILL TO 1',
            'BILL TO 2', 'BILL TO 3', 'BILL TO 4', 'BILL TO 5', 'SHIP TO 1', 'SHIP TO 2', 'SHIP TO 3', 'SHIP TO 4', 'SHIP TO 5',
            'CUSTOMER TYPE', 'TERMS', 'REP', 'TAX CODE', 'RESALE NUM', 'ACCOUNT NO.', 'CREDIT LIMIT', 'JOB STATUS', 'JOB TYPE',
            'JOB DESCRIPTION', 'START DATE', 'PROJECTED END', 'END DATE', 'NOTE'
        );
        
        $cols = false;
        $rows = array();
        
        while(false !== ($n = fgetcsv($fh,10000, ',', '"'))) {
            if (!$cols) {
                
                $cols = array();
                foreach($n as $k) {
                    $cols[] = strtoupper(trim($k));
                }
                
                if (empty($cols)) {
                    continue;
                }
                foreach($req as $r) {
                    if (!in_array($r,$cols)) {
                        $cols = false;
                        break;
                    }
                }
                continue;
            }
            foreach($cols as $i=>$k) {
                $row[$k] = $n[$i];
            }
            $rows[] = $row;
        }
        
        if (empty($cols)) {
            $this->jerr("could not find a row with " . implode(' / ', $req));
        }
        
        fclose($fh);
        
        $tz = DB_DataObject::Factory('taxzone');
        $tz->whereAdd("taxzone_code != 'NO TAX'");
        if (!$tz->find(true)) {
            $this->jerr("could not find tax zone for taxable");
        }
        
        
        foreach ($rows as $row){
            $custinfo = DB_DataObject::factory('custinfo');
            if(empty($row['COMPANY'])){
                continue;
            }
            $checkTerms = true;
            if(!$custinfo->get('cust_name', $row['COMPANY'])){ // customer not exist
                $custinfo->setFrom(array(
                    'cust_active'       => $row['ACTIVE STATUS'] == 'Active' ? TRUE : FALSE,
                    'cust_salesrep_id'  => $this->salesrep($row['REP']),
                    'cust_name'         => $row['COMPANY'],
                    'cust_number'       => str_replace(' ', '', strtoupper($row['COMPANY'])),
                    'cust_creditlmt'    => empty($row['CREDIT LIMIT']) ? 0 : $row['CREDIT LIMIT'],
                    'cust_terms_id'     => $this->terms($row['TERMS']),
                    'cust_taxzone_id'   => $tz->pid()
                ));
                
                
                foreach($custinfo->defaults() as $k=>$v) {
                    if (!isset($custinfo->$k)) {
                        $custinfo->$k = $v;
                    }
                }

                $custinfo->insert();
                
                $checkTerms = false;
            }
            
            if(!$custinfo->pid()){
                $this->jerr("error occur on insert customer " . $row['COMPANY']);
            }
            
            // check the terms
            if($checkTerms){
                $terms_id = $this->terms($row['TERMS']);
                if($custinfo->cust_terms_id  != $terms_id){
                    $oldcust = clone($custinfo);
                    $custinfo->cust_terms_id = $terms_id;
                    $custinfo->update($oldcust);
                }
            }
            
            // check cntct and update cust_cntct_id
            $cntct = DB_DataObject::factory('cntct');
            if(empty($row['FIRST NAME']) && empty($row['LAST NAME'])){
                $row['FIRST NAME'] = $row['CONTACT'];
                
                if(empty($row['CONTACT'])){
                    $row['FIRST NAME'] = $row['CUSTOMER'];
                }
                
            }
           
            $c = array(
                'cntct_first_name'  => empty($row['MR, MRS']) ? $row['FIRST NAME'] : $row['MR, MRS'] . ' ' . $row['FIRST NAME'],
                'cntct_last_name'   => $row['LAST NAME'],
                'cntct_active'      => 1,
                'cntct_phone'       => $row['PHONE'],
                'cntct_fax'         => $row['FAX'],
                'cntct_email'       => $row['EMAIL'],
                'cntct_notes'       => $row['NOTE'],
                'cntct_crmacct_id'  => $cntct->sqlValue("(SELECT crmacct_id FROM crmacct WHERE crmacct_cust_id = {$custinfo->pid()})")
            );
            $c['cntct_name'] = empty($c['cntct_last_name']) ? $c['cntct_first_name'] : $c['cntct_first_name'] . ' ' . $c['cntct_last_name'];
            if(!$cntct->get('cntct_name', $c['cntct_name'])){
                $cntct = $cntct->createFromArray($c);
            }
            if(!$cntct->pid()){
                $this->jerr("error occur on insert cntct " . $row['CUSTOMER']);
            }
            
            
            if(!$cntct->cntct_addr_id){
                $addrs = $this->addrValue($row);
                $addr = DB_DataObject::Factory('addr');
                $addr->createFromArray($addrs);
                
                if(!$addr->pid()){
                    $this->jerr("error occur on insert address " . $row['CUSTOMER']);
                }

                $oldcn = clone($cntct);
                $cntct->cntct_addr_id = $addr->pid();
                $cntct->update($oldcn);
            }
            
            if(!$custinfo->cust_cntct_id){
                $oldcust = clone($custinfo);
                $custinfo->cust_cntct_id = $cntct->pid();
                $custinfo->update($oldcust);
            }
            
            // fetch all cntct id to update the shipment list
            $cn = DB_DataObject::factory('cntct');
            $cn->whereAdd("
                cntct_crmacct_id = (SELECT crmacct_id FROM crmacct WHERE crmacct_cust_id = {$custinfo->pid()})
            ");
            $list = $cn->fetchAll('cntct_id');
            
            $custinfo->updateShipList($list);
            
        }
        
        $this->jok("DONE");
        
        exit;
    }
    
    /*
     * use default salesrep accounts if the code does not exist
     */
    function salesrep($salesrep_name)
    {
        $salesrep = DB_DataObject::factory('salesrep');
        if(!$salesrep->get('salesrep_name', $salesrep_name)){
            $salesrep->get('salesrep_name', 'accounts');
        }
        return $salesrep->pid();
        
    }
    
    /*
     * use default terms C.O.D. if the code does not exist
     */
    function terms($t)
    {
        $terms = DB_DataObject::factory('terms');
        $terms->whereAdd("
            terms_code ILIKE '%{$terms->escape($t)}%'
            OR
            terms_descrip ILIKE '%{$terms->escape($t)}%'
        ");
        if($terms->find(true)){
            return $terms->pid();
        }
        
        $terms->setFrom(array(
            'terms_code' => strtoupper($t),
            'terms_descrip' => $t,
            'terms_type' => 'D',
            'terms_duedays' => 0,
            'terms_discprcnt' => 0,
            'terms_cutoffday' => 0,
            'terms_ap' => TRUE,
            'terms_ar' => TRUE 
        ));
        $terms->insert();
        
        if(!$terms->pid()){
            $this->jerr("error occur on insert a terms " . $t);
        }
        
        return $terms->pid();
    }
    
    function addrValue($row)
    {
        $australiaStates = array(
            'ACT'   => 'Australian Capital Territory',
            'JBT'   => 'Jervis Bay Territory',
            'NSW'   => 'New South Wales',
            'NT'    => 'Northern Territory',
            'QLD'   => 'Queensland',
            'SA'    => 'South Australia',
            'TAS'   => 'Tasmania',
            'VIC'   => 'Victoria',
            'WA'    => 'Western Australia'
        );
        $address = array(
            'SHIP TO 5' => '',
            'SHIP TO 4' => '',
            'SHIP TO 3' => 'addr_line3', 
            'SHIP TO 2' => 'addr_line2',
            'SHIP TO 1' => 'addr_line1'
        );
        
        $city = '';
        $state = '';
        $postalcode = '';

        foreach (array_keys($address) as $a){
            if(empty($row[$a])){
                unset($address[$a]);
                continue;
            }
            foreach ($australiaStates as $k => $v){
                if(!preg_match("/{$k}\s{0,}[0-9]{0,}$/", $row[$a])){
                    continue;
                }
                $city = trim(preg_replace("/{$k}\s{0,}[0-9]{0,}$/", '', $row[$a]));
                
                $postalcode = trim(preg_replace("/{$city}/", '', $row[$a]));
                
                $state = $v;
            }
            unset($address[$a]);
            break;
        }

        foreach ($address as $k => $v){
            if(empty($v)){
                $row['SHIP TO 3'] = implode(' ', array($row['SHIP TO 3'], $row['SHIP TO 4']));
            }
        }
        $addrs = array(
            'addr_active'       => 1,
            'addr_line1'        => isset($address['SHIP TO 1']) ? $row['SHIP TO 1'] : '',
            'addr_line2'        => isset($address['SHIP TO 2']) ? $row['SHIP TO 2'] : '',
            'addr_line3'        => isset($address['SHIP TO 3']) ? $row['SHIP TO 3'] : '',
            'addr_city'         => $city,
            'addr_state'        => $state,
            'addr_postalcode'   => $postalcode
        );
        
        return $addrs;
    }
}