2 require_once 'Pman.php';
6 // this will produce an excel spreadsheet with the list of adjustments to do..
8 class Pman_Xtuple_ApplyGLFinal extends Pman
11 static $cli_desc = "Migrate Netsuite -- apply final GL balances";
15 $au = $this->getAuthUser();
17 if (!empty($au->id)) {
20 if (!HTML_FlexyFramework::get()->cli) {
32 return $this->rootDir . '/../netsuite_balances/netsuite-HK-apr'. $this->year . '.csv';
34 return $this->rootDir . '/../netsuite_balances/netsuite-SG-sep'. $this->year . '.csv';
44 $this->jerr("disabled");
46 $this->db = preg_match('/hk$/', $this->bootLoader->database) ? 'hk' : 'sg';
49 $head = array('accnt_number', 'accnt_name', 'curr', 'accnt_type');
51 for ($y = 2009; $y < 2013; $y++) {
52 $yo[$y] = count($head);
53 array_push($head , 'CUR', 'NS' .$y, 'XT' .$y, 'DIFF'. $y, '' );
55 $res = $this->process($y);
56 foreach($res as $row) {
57 if (!isset($acc[$row['id']])) {
58 $acc[$row['id']] = array();
60 $acc[$row['id']][$y] = $row;
66 uasort($acc, function($ll,$vv) {
67 // // sort by accnt type first.
68 $l = array_pop(array_values($ll));
69 $v = array_pop(array_values($vv));
70 //echo '<PRE>';print_R($l);exit;
71 if ($l['accnt_type'] != $v['accnt_type']) {
72 return $l['accnt_type'] > $v['accnt_type'] ? 1 : -1;
74 if ($l['accnt_subaccnttype_code'] != $v['accnt_subaccnttype_code']) {
75 return $l['accnt_subaccnttype_code'] > $v['accnt_subaccnttype_code'] ? 1 : -1;
78 return $l['accnt_descrip'] > $v['accnt_descrip'] ? 1 : -1;
83 // need to sort accounts...
84 // need to feetch all totals here, rather than later...
85 // but need to know which currencies to fetch...
88 //print_r($head);exit;
90 foreach($acc as $id => $years ) {
91 $first = array_pop(array_values($years));
93 $row = array_fill(0, 30, '');
94 $row[0] = $first['accnt_number'];
95 $row[1] = $first['accnt_descrip'];
96 $row[2] = $first['curr'];
97 $row[3] = $first['accnt_type'] . '/' . $first['accnt_subaccnttype_code'];
102 for ($y = 2009; $y < 2013; $y++) {
104 if (!isset($years[$y])) {
109 if (empty($rec['accnt_number'])) {
111 $curs = array_keys($rec['ns']) ;
112 if (count($curs) != 1) {
114 die("got a ns only acc. with multiple tx's in");
118 $row[$o+1] = $rec['ns'][$cur]['fc'];
120 $row[$o+3] = '?? ' . $rec['ns'][$cur]['fc']; //<<< probaly not!
126 if (empty($rec['ns'])) {
127 $row[$o] = $rec['base'];
129 $row[$o+2] = sprintf('%0.2f',$rec['XT.' . $rec['base'] ]);
130 $row[$o+3] = -1.0 * $rec['XT.' .$rec['base'] ];
135 if (!isset($rec['ns.' .$rec['diffcur'] ])) {
136 echo "diffcurr NS not available...";
143 $row[$o] = $rec['diffcur'];
144 $row[$o+1] = sprintf('%0.2f',$rec['ns.' . $rec['diffcur'] ]);
145 $row[$o+2] = sprintf('%0.2f',$rec['XT.' .$rec['diffcur'] ]);
146 $row[$o+3] = sprintf('%0.2f', -1.0 * $rec['diff'] );
155 header('Content-type: text/csv');
156 header( 'Content-Disposition: attachment;filename=Accounts.csv');
157 $fh = fopen('php://output','w');
160 foreach($rows as $o) {
162 if (!empty($lac) && $lac !=$ac) {
163 fputcsv($fh, array());
188 //print_r($this);exit;
192 $base = $this->yfile();
197 $fh = fopen($base,'r');
207 while (false !== ($row = fgetcsv($fh))) {
211 if (trim($row[0]) == 'Name') {
218 if (str_replace(',','', $row[3]) * 1.0 == 0.0) {
222 $acc = trim($row[0]);
223 if (!isset($lines[$acc])) {
224 $lines[$acc] = $this->acctDef($acc);
225 if ($lines[$acc]['id']) {
226 $accnts[] = $lines[$acc]['id'];
228 $lines[$acc]['ns.name'] = $acc;
232 $curr = trim($row[2]);
233 if ($curr == 'CNY') {
238 $hkd = str_replace(',', '', preg_replace('/HK\$/', '', $row[1]));
239 $fc = str_replace(',','', $row[3]);
242 ($hkd > 0 && $fc < 0)
244 ($hkd < 0 && $fc > 0)
251 $lines[$acc]['ns'][$curr] = array(
258 //DB_DataObject::DebugLevel(1);
259 // now fill in the xtuple data..
260 $gl = DB_DataObject::Factory('gltrans');
262 $dt = $this->db == 'hk' ? ($this->year .'-04-30') : ($this->year .'-09-30');
263 $dt = $dt== '2012-09-30' ? '2012-08-31' : $dt;
267 $gl->selectAdd('distinct(gltrans_accnt_id ) as gltrans_accnt_id ');
268 $gl->whereAdd('gltrans_accnt_id NOT IN (' .implode(',', $accnts) .')');
269 $gl->whereAdd("gltrans_date < '{$dt}'::date + INTERVAL '1 DAY'");
270 $other = $gl->fetchAll('gltrans_accnt_id');
272 foreach($other as $ac) {
273 $lines['XT.'.$ac] = $this->acctDef($ac, true);
277 // at this point we should decide which currency to use for the whole account.
283 foreach($lines as $k=> $line) {
284 $add = $this->study($line);
295 function acctDef($name, $isnum=false) {
297 $d = DB_DataObject::Factory('accnt');
301 if ($name == 'Bank - HKD Transfer Account (HK HQ)') {
302 $name = 'Bank - HKD transfer Account (HK HQ)';
304 if ($name == 'CCB Motor Hire Purchase (HK HQ)') {
305 $name = 'Hire Purchase of Motor vehicle (HK HQ)';
307 if ($name == 'Loss on disposal') {
308 $name = 'Loss on disposal of assets';
310 $d->accnt_descrip = $name;
312 //DB_DataObject::debugLevel(1);
315 $d->whereAdd('accnt_number::integer < 1000');
316 if ($d->count() != 1) {
317 //echo "Can not find accnt $name\n";
319 if (in_array($name , array(
320 'Freight Income (HK HQ)' // known badd..
326 'accnt_descrip' => $name,
327 'accnt_number' => '',
328 'curr_id' => 1, // usd...
331 'accnt_subaccnttype_code' => '',
343 $cur = 'HKD'; //$this->db == 'hk' ? 'HKD' : 'SGD';
345 'id' => $d->accnt_id,
346 'curr' => $d->accnt_curr_id_curr_name,
347 'curr_id' => $d->accnt_curr_id,
348 'accnt_type' => $d->accnt_type,
349 'accnt_number' => $d->accnt_number,
350 'accnt_descrip' => $d->accnt_descrip,
351 'accnt_subaccnttype_code' => $d->accnt_subaccnttype_code,
356 // find the total in local...
359 $ret['XT.'. $cur] = $this->getAccntAt($d->accnt_id, $cur);
361 if ($cur != $ret['curr']) {
362 $ret['XT.'. $ret['curr']] = $this->getAccntAt($d->accnt_id, $ret['curr_id']);
371 function getAccntAt($id, $curr=false) {
372 //DB_DataObject::debugLevel(1);
373 // STATEMENT : Q, L, A
374 $dt = $this->db == 'hk' ? ($this->year .'-04-30') : ($this->year .'-09-30');
375 $dt = $dt== '2012-09-30' ? '2012-08-31' : $dt;
376 // the last column is only advisory, we can not base any data on it..
377 //if ($dt == '2012-09-30') {
378 // $dt = '2012-08-30';
382 static $pds = array();
384 if (!isset($pds[$dt])) {
385 $p = DB_DAtaObject::Factory('period');
386 $p->get('period_end', $dt);
388 $pds[$dt] = $p->pid();
396 $gl = DB_DataObject::Factory('trialbal');
402 $gl->selectAdd('trialbal_ending');
404 if (!is_numeric($curr)) {
405 $curr = "getcurrid('$curr')";
416 ) as trialbal_ending ");
421 $gl->trialbal_accnt_id = $id;
422 $gl->trialbal_period_id = $pid;
424 if (!$gl->find(true)) {
425 DB_DataObject::debugLevel(1);
429 return isset($gl->trialbal_ending) ? $gl->trialbal_ending : 0.0;
435 function study($line) {
437 // comparison = should be done on base currency for everything except the banks..
439 //print_r($line);exit;
441 $compare = 'HKD'; //$this->db == 'HK' ? 'HKD' : 'SGD';
442 if ($line['accnt_subaccnttype_code'] == 'CA') {
444 $compare = $line['curr'];
447 //print_r($line);exit;
448 if (empty($line['ns']) && $line['XT.'.$line['base']] == 0.0) {
452 if (empty($line['ns']) || empty($line['id'])) {
453 //echo "SKIP {$line['accnt_descrip']}\n";
456 if (!isset($line['accnt_type'])) {
460 //if ($this->db == 'hk') {
462 foreach($line['ns'] as $cur=>$data) {
463 $hkd += $data['HKD'];
465 $line['ns.HKD'] = $hkd;
468 //if ($this->db == 'sg') {
470 // foreach($line['ns'] as $cur=>$data) {
471 // $sgd += $data['HKD'] / 6.2;
473 // $line['ns.SGD'] = $sgd;
478 switch($line['accnt_type'] ) {
491 if (count(array_keys($line['ns'])) == 1) {
492 // then it should be a simple match
495 if (isset($line['ns'][ $compare ])) {
496 // use the base currency to compare..
497 $line['ns.' . $compare] = $line['ns'][ $compare ]['fc'];
498 $line['diffcur'] = $compare;
499 if ($line['XT.' .$compare ] == ($fac * $line['ns'][ $compare ]['fc'])) {
504 $line['diff'] = sprintf('%0.2f',
505 $line['XT.' .$compare ] - ($fac * $line['ns'][ $compare ]['fc']));
511 // otherise try and use base currency.
513 if ($this->db == 'sg') {
514 if (!isset($line['ns.' . $line['base'] ] )) {
515 //echo '<PRE>';print_R($line);exit;
516 $line['ns.' . $line['base'] ] = sprintf('%0.2f', $line['ns'][ 'HKD' ]['fc'] * (1/6.2));
519 // use the base currency to compare..
520 $line['ns.' . $line['base'] ] = sprintf('%0.2f', $line['ns'][ $line['base'] ]['fc']);
522 $line['diffcur'] = $line['base'];
523 if ($line['XT.' .$line['base'] ] == ($fac * $line['ns.' . $line['base'] ])) {
528 $line['diff'] = sprintf('%0.2f', $line['XT.' .$line['base'] ] - ($fac * $line['ns.' . $line['base'] ]));
532 //print_R($line);exit;
536 // otherwise we have to compare on base currency...
538 if (!isset($line['XT.' . $base])) {
539 echo "MISSING: XT." . $base;
542 // got a multicurrency netsuite..
543 //if ($line['XT.HKD'] == ($fac * $hkd)) {
547 $line['diff'] = sprintf('%0.2f', $line['XT.' . $base] - ($fac * $line['ns.'.$base]));
548 $line['diffcur'] = $base;