Ejemplo n.º 1
0
 public function getIndex()
 {
     $this->heads = array(array('Period', array('search' => true, 'sort' => true, 'style' => 'min-width:90px;', 'daterange' => true)), array('Date', array('search' => true, 'sort' => true, 'style' => 'min-width:100px;', 'daterange' => true)), array('JV Ref', array('search' => true, 'sort' => true, 'style' => 'min-width:120px;')), array('Account', array('search' => true, 'style' => 'min-width:100px;', 'sort' => true)), array('Account Description', array('search' => true, 'style' => 'min-width:125px;', 'sort' => true)), array('Reference Doc.', array('search' => true, 'sort' => true)), array('Orig. Currency', array('search' => true, 'sort' => true)), array('Orig. Amount', array('search' => true, 'sort' => true)), array('Conversion Rate', array('search' => true, 'sort' => true)), array('Base Amount', array('search' => true, 'sort' => true)), array('Transaction Description', array('search' => true, 'sort' => true)));
     //print $this->model->where('docFormat','picture')->get()->toJSON();
     $this->title = 'Statement of Operation Account Balance';
     $this->place_action = 'none';
     $this->can_add = false;
     $this->show_select = false;
     Breadcrumbs::addCrumb('Cost Report', URL::to(strtolower($this->controller_name)));
     $db = Config::get('lundin.main_db');
     $company = Input::get('acc-company');
     //print $prior_year;
     $company = strtolower($company);
     if ($company == '') {
         $company = Config::get('lundin.default_company');
     }
     $companylist = Prefs::getCompany(array('key' => 'DB_CODE', 'sign' => '=', 'value' => $company))->CompanyToArray()->toArray();
     if (count($companylist) > 0) {
         $companyname = $companylist[0]['DESCR'];
     } else {
         $companyname = '';
     }
     $afe = Input::get('acc-afe');
     if ($afe == '' || is_null($afe)) {
         $afes = Prefs::getAfe($company)->AfeToArray();
         $afe = $afes[0]->ANL_CODE;
     }
     $company = strtolower($company);
     $maxperiod = Prefs::latestPeriod($company . '_a_salfldg');
     $period_from = Input::get('acc-period-from');
     $period_to = Input::get('acc-period-to');
     if ($period_from == '' || is_null($period_from)) {
         $period_from = $maxperiod;
     }
     // test value
     //$period_from = '2015001';
     if ($period_to == '' || is_null($period_to)) {
         $period_to = date('Y0m', time());
     }
     $prior_year = substr($period_from, 0, 4);
     $prior_year = $prior_year - 1;
     $prior_year .= '012';
     $this->additional_filter = View::make(strtolower($this->controller_name) . '.addfilter')->with('maxperiod', $period_from)->with('submit_url', 'jvsoab')->render();
     $this->def_order_by = 'TRANS_DATETIME';
     $this->def_order_dir = 'DESC';
     $this->place_action = 'none';
     $this->show_select = false;
     $this->sql_key = 'TRANS_DATETIME';
     $this->sql_table_name = $company . '_a_salfldg';
     $this->sql_connection = 'mysql2';
     /* Start custom queries */
     $model = DB::connection($this->sql_connection)->table($this->sql_table_name);
     $tables = array();
     //current month
     $currentmonthset = array();
     $titlekeys = array();
     $sectiontitle = array();
     foreach (Config::get('accgroup.jvsoab') as $sec => $data) {
         $sectiontitle[$data['key']] = $sec;
         $section = $data['key'];
         foreach ($data['data'] as $h => $v) {
             if ($v['is_head']) {
             } else {
                 $titlekeys[$section][$v['key']] = $h;
             }
         }
         foreach ($data['data'] as $h => $v) {
             if ($v['is_head']) {
             } else {
                 $model = DB::connection($this->sql_connection)->table($this->sql_table_name);
                 $model = $model->select(DB::raw($company . '_a_salfldg.ACCNT_CODE,' . $company . '_acnt.DESCR AS ADESCR,' . 'SUM(' . $company . '_a_salfldg.AMOUNT) as AMT'));
                 if ($v['sql'] == 'in') {
                     $model = $model->whereIn($company . '_a_salfldg.ACCNT_CODE', explode(',', $v['val']));
                 } elseif ($v['sql'] == 'like') {
                     if (is_array($v['val'])) {
                         $vals = $v['val'];
                         $model = $model->where(function ($q) use($vals, $company) {
                             foreach ($vals as $nval) {
                                 $q = $q->whereOr($company . '_a_salfldg.ACCNT_CODE', 'like', $nval);
                             }
                         });
                     } else {
                         $model = $model->where($company . '_a_salfldg.ACCNT_CODE', 'like', $v['val']);
                     }
                 }
                 $res = $model->where($company . '_a_salfldg.PERIOD', '=', $period_from)->leftJoin($company . '_acnt', $company . '_a_salfldg.ACCNT_CODE', '=', $company . '_acnt.ACNT_CODE')->groupBy($company . '_a_salfldg.ACCNT_CODE')->get();
                 $currentmonthset[$section][$v['key']] = $res;
             }
         }
         //priormonth ITD
         $priormonthset = array();
         foreach ($data['data'] as $h => $v) {
             if ($v['is_head']) {
             } else {
                 $model = DB::connection($this->sql_connection)->table($this->sql_table_name);
                 $model = $model->select(DB::raw($company . '_a_salfldg.ACCNT_CODE,' . $company . '_acnt.DESCR AS ADESCR,' . 'SUM(' . $company . '_a_salfldg.AMOUNT) as AMT'));
                 if ($v['sql'] == 'in') {
                     $model = $model->whereIn($company . '_a_salfldg.ACCNT_CODE', explode(',', $v['val']));
                 } elseif ($v['sql'] == 'like') {
                     if (is_array($v['val'])) {
                         $vals = $v['val'];
                         $model = $model->where(function ($q) use($vals, $company) {
                             foreach ($vals as $nval) {
                                 $q = $q->whereOr($company . '_a_salfldg.ACCNT_CODE', 'like', $nval);
                             }
                         });
                     } else {
                         $model = $model->where($company . '_a_salfldg.ACCNT_CODE', 'like', $v['val']);
                     }
                 }
                 $res = $model->where($company . '_a_salfldg.PERIOD', '<', $period_from)->leftJoin($company . '_acnt', $company . '_a_salfldg.ACCNT_CODE', '=', $company . '_acnt.ACNT_CODE')->groupBy($company . '_a_salfldg.ACCNT_CODE')->get();
                 $priormonthset[$section][$v['key']] = $res;
             }
         }
         //print_r($priormonthset);
         //currentmonth ITD
         $currentmonthitdset = array();
         foreach ($data['data'] as $h => $v) {
             if ($v['is_head']) {
             } else {
                 $model = DB::connection($this->sql_connection)->table($this->sql_table_name);
                 $model = $model->select(DB::raw($company . '_a_salfldg.ACCNT_CODE,' . $company . '_acnt.DESCR AS ADESCR,' . 'SUM(' . $company . '_a_salfldg.AMOUNT) as AMT'));
                 if ($v['sql'] == 'in') {
                     $model = $model->whereIn($company . '_a_salfldg.ACCNT_CODE', explode(',', $v['val']));
                 } elseif ($v['sql'] == 'like') {
                     if (is_array($v['val'])) {
                         $vals = $v['val'];
                         $model = $model->where(function ($q) use($vals, $company) {
                             foreach ($vals as $nval) {
                                 $q = $q->whereOr($company . '_a_salfldg.ACCNT_CODE', 'like', $nval);
                             }
                         });
                     } else {
                         $model = $model->where($company . '_a_salfldg.ACCNT_CODE', 'like', $v['val']);
                     }
                 }
                 $res = $model->where($company . '_a_salfldg.PERIOD', '<=', $period_from)->leftJoin($company . '_acnt', $company . '_a_salfldg.ACCNT_CODE', '=', $company . '_acnt.ACNT_CODE')->groupBy($company . '_a_salfldg.ACCNT_CODE')->get();
                 $currentmonthitdset[$section][$v['key']] = $res;
             }
         }
         //print_r($currentmonthitdset);
         //prioryear ITD
         $prioryearset = array();
         foreach ($data['data'] as $h => $v) {
             if ($v['is_head']) {
             } else {
                 $model = DB::connection($this->sql_connection)->table($this->sql_table_name);
                 $model = $model->select(DB::raw($company . '_a_salfldg.ACCNT_CODE,' . $company . '_acnt.DESCR AS ADESCR,' . 'SUM(' . $company . '_a_salfldg.AMOUNT) as AMT'));
                 if ($v['sql'] == 'in') {
                     $model = $model->whereIn($company . '_a_salfldg.ACCNT_CODE', explode(',', $v['val']));
                 } elseif ($v['sql'] == 'like') {
                     if (is_array($v['val'])) {
                         $vals = $v['val'];
                         $model = $model->where(function ($q) use($vals, $company) {
                             foreach ($vals as $nval) {
                                 $q = $q->whereOr($company . '_a_salfldg.ACCNT_CODE', 'like', $nval);
                             }
                         });
                     } else {
                         $model = $model->where($company . '_a_salfldg.ACCNT_CODE', 'like', $v['val']);
                     }
                 }
                 $res = $model->where($company . '_a_salfldg.PERIOD', '<', $period_from)->leftJoin($company . '_acnt', $company . '_a_salfldg.ACCNT_CODE', '=', $company . '_acnt.ACNT_CODE')->groupBy($company . '_a_salfldg.ACCNT_CODE')->get();
                 $prioryearset[$section][$v['key']] = $res;
             }
         }
         //print_r($prioryearset);
     }
     //die();
     $tabdata = array();
     foreach (Config::get('accgroup.jvsoab') as $sec => $data) {
         $section = $data['key'];
         foreach ($data['data'] as $h => $v) {
             $key = $v['key'];
             $dt = array();
             foreach ($currentmonthset[$section][$key] as $data) {
                 $data->PMAMT = 0;
                 $data->CMITDAMT = 0;
                 $data->PYAMT = 0;
                 $tabdata[$section][$key][$data->ACCNT_CODE] = $data;
             }
             //$tabdata[$section] = $dt;
             if (isset($priormonthset[$section][$key])) {
                 foreach ($priormonthset[$section][$key] as $data) {
                     if (isset($tabdata[$section][$key][$data->ACCNT_CODE])) {
                         $tabdata[$section][$key][$data->ACCNT_CODE]->PMAMT = $data->AMT;
                     } else {
                         $ndt = new stdClass();
                         $ndt->ADESCR = $data->ADESCR;
                         $ndt->AMT = 0;
                         $ndt->PMAMT = $data->AMT;
                         $ndt->CMITDAMT = 0;
                         $ndt->PYAMT = 0;
                         $tabdata[$section][$key][$data->ACCNT_CODE] = $ndt;
                     }
                 }
             }
             if (isset($currentmonthitdset[$section][$key])) {
                 foreach ($currentmonthitdset[$section][$key] as $data) {
                     if (isset($tabdata[$section][$key][$data->ACCNT_CODE])) {
                         $tabdata[$section][$key][$data->ACCNT_CODE]->CMITDAMT = $data->AMT;
                     } else {
                         $ndt = new stdClass();
                         $ndt->ADESCR = $data->ADESCR;
                         $ndt->AMT = 0;
                         $ndt->PMAMT = 0;
                         $ndt->CMITDAMT = $data->AMT;
                         $ndt->PYAMT = 0;
                         $tabdata[$section][$key][$data->ACCNT_CODE] = $ndt;
                     }
                 }
             }
             if (isset($prioryearset[$section][$key])) {
                 foreach ($prioryearset[$section][$key] as $data) {
                     if (isset($tabdata[$section][$key][$data->ACCNT_CODE])) {
                         $tabdata[$section][$key][$data->ACCNT_CODE]->PYAMT = $data->AMT;
                     } else {
                         $ndt = new stdClass();
                         $ndt->ADESCR = $data->ADESCR;
                         $ndt->AMT = 0;
                         $ndt->PMAMT = 0;
                         $ndt->CMITDAMT = $data->AMT;
                         $ndt->PYAMT = 0;
                         $tabdata[$section][$key][$data->ACCNT_CODE] = $ndt;
                     }
                 }
             }
         }
     }
     //print_r($tabdata);
     //die();
     //ksort($tabdata);
     $period_year = substr($period_from, 0, 4);
     $period_month = substr($period_from, 5, 2);
     $period_month = date('F', strtotime($period_year . '-' . $period_month));
     $tattrs = array('width' => '100%', 'class' => 'table table-bordered');
     $thead = array();
     $thead[] = array(array('value' => '<h2>' . $companyname . '<h2>', 'attr' => 'colspan="9"'));
     $thead[] = array(array('value' => '<h2>' . $this->title . '<h2>', 'attr' => 'colspan="9"'));
     $thead[] = array(array('value' => '<h2>' . $period_month . ' ' . $period_year . '<h2>', 'attr' => 'colspan="9"'));
     $thead[] = array(array('value' => '', 'attr' => 'colspan=4'), array('value' => 'Prior Month ITD'), array('value' => 'Current Month'), array('value' => 'Current Month ITD'), array('value' => 'Prior Year ITD'), array('value' => 'Current YTD'));
     /*
     $thead[] = array(
             array('value'=>'','attr'=>'colspan=4'),
             array('value'=>'ITD'),
             array('value'=>''),
             array('value'=>'ITD'),
             array('value'=>'ITD'),
             array('value'=>'Movement')
         );
     */
     $seq = 1;
     $tdata = array();
     $ck = '';
     //print_r($titlekeys);
     //print_r($sectiontitle);
     //print_r($tabdata);
     //die();
     foreach ($tabdata as $k => $dm) {
         //print($k);
         //print $k;
         //if($ck != $k){
         $tdata[] = array(array('value' => '<h2>' . $sectiontitle[$k] . '</h2>', 'attr' => 'colspan="4"'), array('value' => '', 'attr' => 'class="column-amt"'), array('value' => '', 'attr' => 'class="column-amt"'), array('value' => '', 'attr' => 'class="column-amt"'), array('value' => '', 'attr' => 'class="column-amt"'), array('value' => '', 'attr' => 'class="column-amt"'));
         //}
         foreach ($dm as $ac => $md) {
             $tdata[] = array(array('value' => '&nbsp;'), array('value' => '<h3>' . $titlekeys[$k][$ac] . '</h3>', 'attr' => 'colspan="3"'), array('value' => '', 'attr' => 'class="column-amt"'), array('value' => '', 'attr' => 'class="column-amt"'), array('value' => '', 'attr' => 'class="column-amt"'), array('value' => '', 'attr' => 'class="column-amt"'), array('value' => '', 'attr' => 'class="column-amt"'));
             /*
             $tdata[] = array(
                     array('value'=> $titlekeys[$k][$dm['key']], 'attr'=>'colspan="3"'),
                     array('value'=>'', 'attr'=>'class="column-amt"' ),
                     array('value'=>'', 'attr'=>'class="column-amt"' ),
                     array('value'=>'', 'attr'=>'class="column-amt"' ),
                     array('value'=>'', 'attr'=>'class="column-amt"' ),
                     array('value'=>'', 'attr'=>'class="column-amt"' )
                 );
             */
             //print_r($md);
             $sum = new stdClass();
             $sum->PMAMT = 0;
             $sum->AMT = 0;
             $sum->CMITDAMT = 0;
             $sum->PYAMT = 0;
             $sum->movement = 0;
             foreach ($md as $mk => $m) {
                 $movement = $m->CMITDAMT - $m->PYAMT;
                 $sum->PMAMT += (double) $m->PMAMT;
                 $sum->AMT += (double) $m->AMT;
                 $sum->CMITDAMT += (double) $m->CMITDAMT;
                 $sum->PYAMT += (double) $m->PYAMT;
                 $sum->movement += (double) $movement;
                 $movement = 0;
                 $tdata[] = array(array('value' => '&nbsp;'), array('value' => '&nbsp;'), array('value' => $mk), array('value' => $m->ADESCR), array('value' => $m->PMAMT, 'attr' => 'class="column-amt"'), array('value' => $m->AMT, 'attr' => 'class="column-amt"'), array('value' => $m->CMITDAMT, 'attr' => 'class="column-amt"'), array('value' => $m->PYAMT, 'attr' => 'class="column-amt"'), array('value' => $movement, 'attr' => 'class="column-amt"'));
                 $seq++;
             }
         }
         $tdata[] = array(array('value' => '<h3> TOTAL ' . $sectiontitle[$k] . '</h3>', 'attr' => 'colspan="4"'), array('value' => $sum->PMAMT, 'attr' => 'class="column-amt total"'), array('value' => floatval($sum->AMT), 'attr' => 'class="column-amt total"'), array('value' => $sum->CMITDAMT, 'attr' => 'class="column-amt total"'), array('value' => $sum->PYAMT, 'attr' => 'class="column-amt total"'), array('value' => $sum->movement, 'attr' => 'class="column-amt total"'));
         $tdata[] = array(array('value' => '', 'attr' => 'colspan="4"'), array('value' => '', 'attr' => 'class="column-amt total"'), array('value' => '', 'attr' => 'class="column-amt total"'), array('value' => '', 'attr' => 'class="column-amt total"'), array('value' => '', 'attr' => 'class="column-amt total"'), array('value' => '', 'attr' => 'class="column-amt total"'));
     }
     //print_r($tdata);
     //die();
     $mtable = new HtmlTable($tdata, $tattrs, $thead);
     $tables[] = $mtable->build();
     $this->table_raw = $tables;
     if ($this->print == true) {
         return $tables;
     } else {
         return parent::reportPageGenerator();
     }
 }