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 = 'AFE Report - Lvl 3'; $this->place_action = 'none'; $this->show_select = false; Breadcrumbs::addCrumb('Cost Report', URL::to(strtolower($this->controller_name))); $this->additional_filter = View::make(strtolower($this->controller_name) . '.addfilter')->with('submit_url', 'afelvlthree')->render(); $db = Config::get('lundin.main_db'); $company = Input::get('acc-company'); $period_from = Input::get('acc-period-from'); $period_to = Input::get('acc-period-to'); if ($period_from == '' || is_null($period_from)) { $period_from = date('Y0m', time()); } if ($period_to == '' || is_null($period_to)) { $period_to = date('Y0m', time()); } $company = strtolower($company); if ($company == '') { $company = Config::get('lundin.default_company'); } $afe = Input::get('acc-afe'); if ($afe == '' || is_null($afe)) { $afes = Prefs::getAfe($company)->AfeToArray(); $afe = $afes[0]->ANL_CODE; } $company = strtolower($company); $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(); //print_r($mresult); $actualresult = $model->select(DB::raw($company . '_a_salfldg.ACCNT_CODE,' . $company . '_acnt.DESCR AS ADESCR,' . 'SUM(' . $company . '_a_salfldg.AMOUNT) as AMT'))->where($company . '_a_salfldg.ACCNT_CODE', 'like', '2%')->where($company . '_a_salfldg.ANAL_T1', '=', $afe)->where($company . '_a_salfldg.PERIOD', '>=', $period_from)->where($company . '_a_salfldg.PERIOD', '<=', $period_to)->leftJoin($company . '_acnt', $company . '_a_salfldg.ACCNT_CODE', '=', $company . '_acnt.ACNT_CODE')->groupBy($company . '_a_salfldg.ACCNT_CODE')->get(); $model = DB::connection($this->sql_connection)->table($company . '_a_salfldg'); $prioritdresult = $model->select(DB::raw($company . '_a_salfldg.ACCNT_CODE,' . $company . '_acnt.DESCR AS ADESCR,' . 'SUM(' . $company . '_a_salfldg.AMOUNT) as AMT'))->where($company . '_a_salfldg.ACCNT_CODE', 'like', '2%')->where($company . '_a_salfldg.ANAL_T1', '=', $afe)->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(); $model = DB::connection($this->sql_connection)->table($company . '_a_salfldg'); $currentitdresult = $model->select(DB::raw($company . '_a_salfldg.ACCNT_CODE,' . $company . '_acnt.DESCR AS ADESCR,' . 'SUM(' . $company . '_a_salfldg.AMOUNT) as AMT'))->where($company . '_a_salfldg.ACCNT_CODE', 'like', '2%')->where($company . '_a_salfldg.ANAL_T1', '=', $afe)->where($company . '_a_salfldg.PERIOD', '<=', $period_to)->leftJoin($company . '_acnt', $company . '_a_salfldg.ACCNT_CODE', '=', $company . '_acnt.ACNT_CODE')->groupBy($company . '_a_salfldg.ACCNT_CODE')->get(); $model = DB::connection($this->sql_connection)->table($company . '_b_salfldg'); $budgetresult = $model->select(DB::raw($company . '_b_salfldg.ACCNT_CODE,' . $company . '_acnt.DESCR AS ADESCR,' . 'SUM(' . $company . '_b_salfldg.AMOUNT) as AMT'))->where($company . '_b_salfldg.ACCNT_CODE', 'like', '2%')->where($company . '_b_salfldg.ANAL_T1', '=', $afe)->where($company . '_b_salfldg.PERIOD', '>=', $period_from)->where($company . '_b_salfldg.PERIOD', '<=', $period_to)->leftJoin($company . '_acnt', $company . '_b_salfldg.ACCNT_CODE', '=', $company . '_acnt.ACNT_CODE')->groupBy($company . '_b_salfldg.ACCNT_CODE')->get(); $model = DB::connection($this->sql_connection)->table($company . '_d_salfldg'); $revbudgetresult = $model->select(DB::raw($company . '_d_salfldg.ACCNT_CODE,' . $company . '_acnt.DESCR AS ADESCR,' . 'SUM(' . $company . '_d_salfldg.AMOUNT) as AMT'))->where($company . '_d_salfldg.ACCNT_CODE', 'like', '2%')->where($company . '_d_salfldg.ANAL_T1', '=', $afe)->where($company . '_d_salfldg.PERIOD', '>=', $period_from)->where($company . '_d_salfldg.PERIOD', '<=', $period_to)->leftJoin($company . '_acnt', $company . '_d_salfldg.ACCNT_CODE', '=', $company . '_acnt.ACNT_CODE')->groupBy($company . '_d_salfldg.ACCNT_CODE')->get(); $tabdata = array(); foreach ($actualresult as $a) { $tabdata[$a->ACCNT_CODE] = $a; $tabdata[$a->ACCNT_CODE]->BAMT = 0; $tabdata[$a->ACCNT_CODE]->DAMT = 0; $tabdata[$a->ACCNT_CODE]->PITDAMT = 0; $tabdata[$a->ACCNT_CODE]->CITDAMT = 0; } foreach ($prioritdresult as $p) { if (isset($tabdata[$p->ACCNT_CODE])) { $tabdata[$p->ACCNT_CODE]->PITDAMT = $p->AMT; } } foreach ($currentitdresult as $c) { if (isset($tabdata[$c->ACCNT_CODE])) { $tabdata[$c->ACCNT_CODE]->CITDAMT = $c->AMT; } } foreach ($budgetresult as $b) { if (isset($tabdata[$b->ACCNT_CODE])) { $tabdata[$b->ACCNT_CODE]->BAMT = $b->AMT; } else { $tabdata[$b->ACCNT_CODE] = $b; $tabdata[$b->ACCNT_CODE]->BAMT = $b->AMT; $tabdata[$b->ACCNT_CODE]->AMT = 0; $tabdata[$b->ACCNT_CODE]->DAMT = 0; $tabdata[$b->ACCNT_CODE]->PITDAMT = 0; $tabdata[$b->ACCNT_CODE]->CITDAMT = 0; } } foreach ($revbudgetresult as $d) { if (isset($tabdata[$d->ACCNT_CODE])) { $tabdata[$d->ACCNT_CODE]->DAMT = $d->AMT; } else { $tabdata[$d->ACCNT_CODE] = $d; $tabdata[$d->ACCNT_CODE]->DAMT = $d->AMT; $tabdata[$d->ACCNT_CODE]->AMT = 0; $tabdata[$d->ACCNT_CODE]->BAMT = 0; $tabdata[$d->ACCNT_CODE]->PITDAMT = 0; $tabdata[$d->ACCNT_CODE]->CITDAMT = 0; } } ksort($tabdata); $tattrs = array('width' => '100%', 'class' => 'table table-bordered'); $thead = array(); $tdataclr = array(); $tdataexp = array(); $thead[] = array(array('value' => '#', 'attr' => 'rowspan=2'), array('value' => 'Account', 'attr' => 'colspan=2 rowspan=2 class="center"'), array('value' => 'Budget', 'attr' => 'colspan=2'), array('value' => 'Actual', 'attr' => 'colspan=3'), array('value' => 'Variance', 'attr' => 'colspan=2')); $thead[] = array(array('value' => 'Original'), array('value' => 'Revised'), array('value' => 'Prior ITD'), array('value' => 'Current Month'), array('value' => 'Current Month ITD'), array('value' => '$'), array('value' => '%')); $seq = 1; $sumexp = new stdClass(); $sumclr = new stdClass(); $sumexp->BAMT = 0; $sumexp->DAMT = 0; $sumexp->BREV = 0; $sumexp->PITDAMT = 0; $sumexp->AMT = 0; $sumexp->CITDAMT = 0; $sumexp->VARIANCE = 0; $sumexp->PCT = 0; $sumclr->BAMT = 0; $sumclr->DAMT = 0; $sumclr->BREV = 0; $sumclr->PITDAMT = 0; $sumclr->AMT = 0; $sumclr->CITDAMT = 0; $sumclr->VARIANCE = 0; $sumclr->PCT = 0; foreach ($tabdata as $m) { if ($m->DAMT > 0) { $variance = $m->CITDAMT - $m->BAMT; $pct = $variance / $m->BAMT * 100; $brev = $m->DAMT - $m->BAMT; } else { $variance = 0; $pct = 0; $brev = 0; } if (preg_match('/^2009.*/', $m->ACCNT_CODE)) { $tdataclr[] = array(array('value' => $seq), array('value' => $m->ACCNT_CODE), array('value' => $m->ADESCR), array('value' => '', 'attr' => 'class="column-amt"'), array('value' => '', 'attr' => 'class="column-amt"'), array('value' => $m->PITDAMT, 'attr' => 'class="column-amt"'), array('value' => $m->AMT, 'attr' => 'class="column-amt"'), array('value' => $m->CITDAMT, 'attr' => 'class="column-amt"'), array('value' => '', 'attr' => 'class="column-amt"'), array('value' => '', 'attr' => 'class="column-amt"')); $sumclr->BAMT = ''; $sumclr->DAMT = ''; $sumclr->BREV = ''; $sumclr->PITDAMT += $m->PITDAMT; $sumclr->AMT += $m->AMT; $sumclr->CITDAMT += $m->CITDAMT; $sumclr->VARIANCE = ''; $sumclr->PCT = ''; } else { $tdataexp[] = array(array('value' => $seq), array('value' => $m->ACCNT_CODE), array('value' => $m->ADESCR), array('value' => $m->BAMT, 'attr' => 'class="column-amt"'), array('value' => $m->DAMT, 'attr' => 'class="column-amt"'), array('value' => $m->PITDAMT, 'attr' => 'class="column-amt"'), array('value' => $m->AMT, 'attr' => 'class="column-amt"'), array('value' => $m->CITDAMT, 'attr' => 'class="column-amt"'), array('value' => $variance, 'attr' => 'class="column-amt"'), array('value' => $pct, 'attr' => 'class="column-amt"')); $sumexp->BAMT += $m->BAMT; $sumexp->DAMT += $m->DAMT; $sumexp->BREV += $brev; $sumexp->PITDAMT += $m->PITDAMT; $sumexp->AMT += $m->AMT; $sumexp->CITDAMT += $m->CITDAMT; $sumexp->VARIANCE += $variance; $sumexp->PCT += $pct; } $seq++; } $tdataclr[] = array(array('value' => 'Total After Clearing', 'attr' => 'colspan=3 style="text-align:center;font-weight:bold;" '), array('value' => $sumclr->BAMT, 'attr' => 'class="column-amt"'), array('value' => $sumclr->DAMT, 'attr' => 'class="column-amt"'), array('value' => $sumclr->PITDAMT, 'attr' => 'class="column-amt"'), array('value' => $sumclr->AMT, 'attr' => 'class="column-amt"'), array('value' => $sumclr->CITDAMT, 'attr' => 'class="column-amt"'), array('value' => $sumclr->VARIANCE, 'attr' => 'class="column-amt"'), array('value' => $sumclr->PCT, 'attr' => 'class="column-amt"')); $tdataexp[] = array(array('value' => 'Total Expenditure', 'attr' => 'colspan=3 style="text-align:center;font-weight:bold;" '), array('value' => $sumexp->BAMT, 'attr' => 'class="column-amt"'), array('value' => $sumexp->DAMT, 'attr' => 'class="column-amt"'), array('value' => $sumexp->PITDAMT, 'attr' => 'class="column-amt"'), array('value' => $sumexp->AMT, 'attr' => 'class="column-amt"'), array('value' => $sumexp->CITDAMT, 'attr' => 'class="column-amt"'), array('value' => $sumexp->VARIANCE, 'attr' => 'class="column-amt"'), array('value' => $sumexp->PCT, 'attr' => 'class="column-amt"')); $tdata = array_merge($tdataexp, $tdataclr); $mtable = new HtmlTable($tdata, $tattrs, $thead); $tables[] = $mtable->build(); $this->sql_table_name = $company . '_b_salfldg'; $model = DB::connection($this->sql_connection)->table($this->sql_table_name); $bresult = $model->where('ACCNT_CODE', 'like', '200%')->leftJoin($company . '_acnt', $company . '_b_salfldg.ACCNT_CODE', '=', $company . '_acnt.ACNT_CODE')->groupBy('ACCNT_CODE')->get(array('ACCNT_CODE', DB::raw($company . '_acnt.DESCR AS ADESCR'), DB::raw('SUM(AMOUNT) as AMT'))); //print_r($bresult); $this->table_raw = $tables; if ($this->print == true) { return $tables; } else { return parent::reportPageGenerator(); } }
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' => ' '), 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' => ' '), array('value' => ' '), 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(); } }