';print_R($l);exit;
            if ($l['accnt_type'] != $v['accnt_type']) {
                return $l['accnt_type']  > $v['accnt_type'] ? 1 : -1;
            }
            if ($l['accnt_subaccnttype_code'] != $v['accnt_subaccnttype_code']) {
                return $l['accnt_subaccnttype_code']  > $v['accnt_subaccnttype_code'] ? 1 : -1;
            }
            
            return $l['accnt_descrip']  > $v['accnt_descrip'] ? 1 : -1;
            
        });
        
        
        // need to sort accounts...
        // need to feetch all totals here, rather than later...
        // but need to know which currencies to fetch...
        
        //echo '
';
        //print_r($head);exit;
        $rows = array();
        foreach($acc as $id => $years ) {
            $first = array_pop(array_values($years));
            //print_r($first);
            $row = array_fill(0, 30, '');
            $row[0] = $first['accnt_number'];
            $row[1] = $first['accnt_descrip'];
            $row[2] = $first['curr'];
            $row[3] = $first['accnt_type'] . '/' . $first['accnt_subaccnttype_code'];
            
            
            //print_r($years);
            
            for ($y = 2009; $y < 2013; $y++) {
                $o = $yo[$y];
                if (!isset($years[$y])) {
                    continue;
                }
                $rec = $years[$y];
                
                if (empty($rec['accnt_number'])) {
                    
                    $curs = array_keys($rec['ns']) ;
                    if (count($curs) != 1) {
                        print_r($rec);
                        die("got a ns only acc. with multiple tx's in");
                    }
                    $cur = $curs[0];
                    $row[$o] = $cur ;
                    $row[$o+1] =  $rec['ns'][$cur]['fc'];
                    $row[$o+2] = 0;
                    $row[$o+3] = '?? ' . $rec['ns'][$cur]['fc']; //<<< probaly not!
                    continue;
                    
                }
                
                
                if (empty($rec['ns'])) {
                    $row[$o] = $rec['base'];
                    $row[$o+1] = 0;
                    $row[$o+2] =  sprintf('%0.2f',$rec['XT.' . $rec['base']  ]);
                    $row[$o+3] = -1.0 * $rec['XT.' .$rec['base']  ];
                    continue;
                    
                }
                
                if (!isset($rec['ns.' .$rec['diffcur']  ])) {
                    echo "diffcurr NS not available...";
                    print_R($rec);
                    exit;
                }
                
                
                
                $row[$o] = $rec['diffcur'];
                $row[$o+1] =  sprintf('%0.2f',$rec['ns.' . $rec['diffcur']  ]);
                $row[$o+2] =  sprintf('%0.2f',$rec['XT.' .$rec['diffcur']  ]);
                $row[$o+3] =  sprintf('%0.2f', -1.0 * $rec['diff'] );
            }
            $rows[] = $row;
            
        }
        
        
        // output csv..
        
        header('Content-type: text/csv');
        header( 'Content-Disposition: attachment;filename=Accounts.csv');
        $fh = fopen('php://output','w');
        fputcsv($fh, $head);
        $lac = '';
        foreach($rows as $o) {
            $ac = $o[3];
            if (!empty($lac) && $lac !=$ac) {
                fputcsv($fh, array());
            }
            $lac = $ac;
            fputcsv($fh, $o);
        }
        fclose($fh);
        exit;
        
        
        
        
        
        
        
        
        
        
        
        exit;
        
    }
    
    
    function process($y)
    {
        //print_r($this);exit;
        $this->year = $y;
        
        
        $base = $this->yfile();
        
        
         
        
        $fh = fopen($base,'r');
        if (!$fh) {
            die("no file");
        }
        
        
        
        $lines = array();
        $accnts = array();
        $start = false;
        while (false !== ($row = fgetcsv($fh))) {
            
            if (!$start) {
                //var_dump($row);
                if (trim($row[0]) == 'Name') {
                    $start = true;
                    continue;
                }
                continue;
            }
            
             if (str_replace(',','', $row[3]) * 1.0 == 0.0) {
                continue;
             }
            
            $acc = trim($row[0]);
            if (!isset($lines[$acc])) {
                $lines[$acc] = $this->acctDef($acc);
                if ($lines[$acc]['id']) {
                    $accnts[] = $lines[$acc]['id'];
                }
                $lines[$acc]['ns.name'] = $acc;
                
            }
            
            $curr = trim($row[2]);
            if ($curr == 'CNY') {
                $curr = 'RMB';
            }
            
            
            $hkd = str_replace(',', '', preg_replace('/HK\$/', '', $row[1]));
            $fc = str_replace(',','', $row[3]);
            
            if (
                    ($hkd > 0 && $fc < 0)
                ||
                    ($hkd < 0 && $fc > 0)
               )
            {
                $fc *= -1;
            }
             
            
            $lines[$acc]['ns'][$curr] = array(
                    'fc' => $fc,
                    'HKD' => $hkd
            );
        }
        fclose($fh);
        
        //DB_DataObject::DebugLevel(1);
        // now fill in the xtuple data..
        $gl = DB_DataObject::Factory('gltrans');
        
        $dt = $this->db == 'hk' ? ($this->year .'-04-30') : ($this->year .'-09-30');
        $dt = $dt== '2012-09-30' ? '2012-08-31' : $dt;
        
        
        $gl->selectAdd();
        $gl->selectAdd('distinct(gltrans_accnt_id ) as gltrans_accnt_id ');
        $gl->whereAdd('gltrans_accnt_id NOT IN (' .implode(',', $accnts) .')');
        $gl->whereAdd("gltrans_date < '{$dt}'::date + INTERVAL '1 DAY'");
        $other = $gl->fetchAll('gltrans_accnt_id');
         
        foreach($other as $ac) {
            $lines['XT.'.$ac] = $this->acctDef($ac, true);
        }
        $res = array();
        
        // at this point we should decide which currency to use for the whole account.
        
        
        
        
        //print_r($lines);
        foreach($lines as $k=> $line) {
            $add = $this->study($line);
            
             
            
            if ($add) {
                $res[$k] = $add;
            }
        }
        return $res;
    }
    
    function acctDef($name, $isnum=false) {
        
        $d = DB_DataObject::Factory('accnt');
        $d->autoJoin();
        //why?
        if (!$isnum) {
            if ($name == 'Bank - HKD Transfer Account (HK HQ)') {
                $name = 'Bank - HKD transfer Account (HK HQ)';
            }
            if ($name == 'CCB Motor Hire Purchase (HK HQ)') {
                $name = 'Hire Purchase of Motor vehicle (HK HQ)';
            }
            if ($name == 'Loss on disposal') {
                $name = 'Loss on disposal of assets';
            }
            $d->accnt_descrip = $name;
            
            //DB_DataObject::debugLevel(1);
            
            
            $d->whereAdd('accnt_number::integer < 1000');
            if ($d->count() != 1) {
                //echo "Can not find accnt $name\n";
                
                if (in_array($name , array(
                    'Freight Income (HK HQ)' // known badd..
    
                    
                ))) {
                    return array(
                       'id' => 0,
                       'accnt_descrip' => $name,
                       'accnt_number' => '',
                       'curr_id' => 1, // usd...
                       'curr' => 'USD',
                       'accnt_type' => '',
                       'accnt_subaccnttype_code' => '',
                       
                   );   
                }
                
                exit;
                
            }
            $d->find(true);
        } else {
            $d->get($name);
        }
        $cur = 'HKD'; //$this->db == 'hk' ? 'HKD' : 'SGD';
        $ret = array(
                'id' => $d->accnt_id,
                'curr' => $d->accnt_curr_id_curr_name,
                'curr_id' => $d->accnt_curr_id,
                'accnt_type' => $d->accnt_type,
                'accnt_number' => $d->accnt_number,
                'accnt_descrip' => $d->accnt_descrip,
                'accnt_subaccnttype_code' => $d->accnt_subaccnttype_code,
                'base' => $cur,
                'ns' => array()
        );
        
        // find the total in local...
        
        
        $ret['XT.'. $cur] = $this->getAccntAt($d->accnt_id, $cur);
        
        if ($cur != $ret['curr']) {
            $ret['XT.'. $ret['curr']] = $this->getAccntAt($d->accnt_id, $ret['curr_id']);
        }
        
        
        
        return $ret;
        
    }
     
    function getAccntAt($id, $curr=false) {
        //DB_DataObject::debugLevel(1);
        // STATEMENT : Q, L, A
        $dt = $this->db == 'hk' ? ($this->year .'-04-30') : ($this->year .'-09-30');
        $dt = $dt== '2012-09-30' ? '2012-08-31' : $dt;
        // the last column is only advisory, we can not base any data on it..
        //if ($dt == '2012-09-30') {
        //    $dt  = '2012-08-30';
        //}
        
        
        static $pds = array();
        
        if (!isset($pds[$dt])) {
            $p = DB_DAtaObject::Factory('period');
            $p->get('period_end', $dt);

            $pds[$dt] = $p->pid();
        }
        
        $pid = $pds[$dt];
        
          
        
        
        $gl = DB_DataObject::Factory('trialbal');
        
        
        $gl->selectAdd();
        
        if (!$curr) {
            $gl->selectAdd('trialbal_ending');
        } else {
            if (!is_numeric($curr)) {
                $curr = "getcurrid('$curr')";
                
                
            }
            $gl->selectAdd("
                    currtocurr(
                        baseCurrId(),
                        $curr ,
                        trialbal_ending,
                        '$dt'
                        
                    ) as trialbal_ending ");
            
            
        }
        
        $gl->trialbal_accnt_id = $id;
        $gl->trialbal_period_id = $pid;
        $gg = clone ($gl);
        if (!$gl->find(true)) {
            DB_DataObject::debugLevel(1);
            $gg->find(true);
            exit;
        }
        return   isset($gl->trialbal_ending)  ? $gl->trialbal_ending : 0.0;
            
        
        
    }
    
    function study($line) {
        
        // comparison = should be done on base currency for everything except the banks..
        
        //print_r($line);exit;
        
        $compare = 'HKD'; //$this->db == 'HK' ? 'HKD' : 'SGD';
        if ($line['accnt_subaccnttype_code'] == 'CA') {
            // it's a bank
            $compare = $line['curr'];
        }
        
        //print_r($line);exit;
        if (empty($line['ns']) && $line['XT.'.$line['base']] == 0.0) {
            return false;
        }
        
        if (empty($line['ns']) || empty($line['id'])) {
            //echo "SKIP {$line['accnt_descrip']}\n";
            return $line;
        }
        if (!isset($line['accnt_type'])) {
            print_r($line);exit;
        }
        
        //if ($this->db == 'hk') {
        $hkd = 0.0;
        foreach($line['ns'] as $cur=>$data) {
            $hkd += $data['HKD'];
        }
        $line['ns.HKD'] = $hkd;
        $base = 'HKD';
            
        //if ($this->db == 'sg') {
        //    $sgd = 0.0;
        //    foreach($line['ns'] as $cur=>$data) {
        //        $sgd += $data['HKD'] / 6.2;
        //    }
        //    $line['ns.SGD'] = $sgd;
        //    $base = 'SGD';
        //}
        
        $fac = 1;
        switch($line['accnt_type'] ) {
            case 'A':
            case 'E':
            case 'R':
            case 'L':
                $fac = -1.0;
                break;
            default:
                $fac = 1.0;
                break;
                
        }
         
        if (count(array_keys($line['ns'])) == 1) {
            // then it should be a simple match
            
            
            if (isset($line['ns'][ $compare   ])) {
                // use the base currency to compare..
                $line['ns.' . $compare] = $line['ns'][  $compare  ]['fc'];
                $line['diffcur'] = $compare;
                if ($line['XT.' .$compare ] == ($fac * $line['ns'][  $compare  ]['fc'])) {
                        // they match!!
                        $line['diff'] = '';
                        return $line;
                }
                $line['diff'] = sprintf('%0.2f',
                        $line['XT.' .$compare ] - ($fac * $line['ns'][  $compare ]['fc']));
                
                return $line; 
                
            }
            
            // otherise try and use base currency.
            
            if ($this->db == 'sg') {
                if (!isset($line['ns.' . $line['base'] ] )) {
                    //echo '
';print_R($line);exit;
                    $line['ns.' . $line['base'] ] = sprintf('%0.2f', $line['ns'][  'HKD' ]['fc'] * (1/6.2));
                }
            } else {
            // use the base currency to compare..
                $line['ns.' . $line['base'] ] = sprintf('%0.2f', $line['ns'][  $line['base']   ]['fc']);
            }
            $line['diffcur'] = $line['base'];
            if ($line['XT.' .$line['base'] ] == ($fac * $line['ns.' . $line['base'] ])) {
                    // they match!!
                    $line['diff'] = '';
                    return $line;
            }
            $line['diff'] = sprintf('%0.2f', $line['XT.' .$line['base'] ] - ($fac * $line['ns.' . $line['base'] ]));
            
            return $line; 
              
            //print_R($line);exit;
            
            
        }
        // otherwise we have to compare on base currency...
        
        if (!isset($line['XT.' . $base])) {
            echo "MISSING: XT." . $base;
            print_r($line);exit;
        }
        // got a multicurrency netsuite..
        //if ($line['XT.HKD'] == ($fac * $hkd)) {
            // they match!!
        //    return false;
        //}
        $line['diff'] = sprintf('%0.2f', $line['XT.' . $base] - ($fac * $line['ns.'.$base]));
        $line['diffcur'] = $base;
        return $line; 
        
        
        
        
    }
    
}