public function getDetail($id)
{
$_id = new MongoId($id);
$history = History::where('historyObject._id', $_id)->where('historyObjectType', 'asset')->orderBy('historyTimestamp', 'desc')->orderBy('historySequence', 'desc')->get();
$diffs = array();
foreach ($history as $h) {
$h->date = date('Y-m-d H:i:s', $h->historyTimestamp->sec);
$diffs[$h->date][$h->historySequence] = $h->historyObject;
}
$history = History::where('historyObject._id', $_id)->where('historyObjectType', 'asset')->where('historySequence', 0)->orderBy('historyTimestamp', 'desc')->get();
$tab_data = array();
foreach ($history as $h) {
$apv_status = Assets::getApprovalStatus($h->approvalTicket);
if ($apv_status == 'pending') {
$bt_apv = '<span class="btn btn-info change-approval ' . $h->approvalTicket . '" data-id="' . $h->approvalTicket . '" >' . $apv_status . '</span>';
} else {
if ($apv_status == 'verified') {
$bt_apv = '<span class="btn btn-success" >' . $apv_status . '</span>';
} else {
$bt_apv = '';
}
}
$d = date('Y-m-d H:i:s', $h->historyTimestamp->sec);
$tab_data[] = array($d, $h->historyAction, $h->historyObject['itemDescription'], $h->historyAction == 'new' ? 'NA' : $this->objdiff($diffs[$d]), $bt_apv);
}
$header = array('Modified', 'Event', 'Name', 'Diff', 'Approval');
$attr = array('class' => 'table', 'id' => 'transTab', 'style' => 'width:100%;', 'border' => '0');
$t = new HtmlTable($tab_data, $attr, $header);
$itemtable = $t->build();
$asset = Asset::find($id);
Breadcrumbs::addCrumb('Ad Assets', URL::to(strtolower($this->controller_name)));
Breadcrumbs::addCrumb('Detail', URL::to(strtolower($this->controller_name) . '/detail/' . $asset->_id));
Breadcrumbs::addCrumb($asset->SKU, URL::to(strtolower($this->controller_name)));
return View::make('history.table')->with('a', $asset)->with('title', 'Asset Detail ' . $asset->itemDescription)->with('table', $itemtable);
}
public function getIndex()
{
$this->title = 'Manifest Pengiriman Harian - To Device / Logistic';
$this->place_action = 'none';
$this->show_select = false;
$this->can_add = false;
$this->is_report = true;
Breadcrumbs::addCrumb('Manifest', URL::to(strtolower($this->controller_name)));
$this->additional_filter = View::make('shared.addfilter')->with('submit_url', 'devmanifest')->render();
$db = Config::get('lundin.main_db');
$company = Input::get('acc-company');
//device=&courier=&logistic=&date-from=2015-10-24
$period_from = Input::get('date-from');
$period_to = Input::get('acc-period-to');
$device = Input::get('device');
$courier = Input::get('courier');
$logistic = Input::get('logistic');
$status = Input::get('status');
$courierstatus = Input::get('courier-status');
if ($period_to == '' || is_null($period_to)) {
$period_to = date('Y-m-d', time());
}
if ($period_from == '' || is_null($period_from)) {
$period_from = date('Y-m-d', time());
}
$this->def_order_by = 'TRANS_DATETIME';
$this->def_order_dir = 'DESC';
$this->place_action = 'none';
$this->show_select = false;
/* Start custom queries */
$model = $this->model;
if ($status == '' || is_null($status)) {
$status = Config::get('jayon.devmanifest_default_status');
} else {
$status = explode(',', $status);
}
if (empty($status)) {
$exstatus = Config::get('jayon.devmanifest_default_excl_status');
if (!empty($exstatus)) {
//$model = $model->whereNotIn('status', $exstatus);
}
} else {
//$model = $model->whereIn('status', $status);
}
/*
if($courierstatus == '' || is_null($courierstatus) ){
$courierstatus = Config::get('jayon.devmanifest_default_courier_status');
}else{
$courierstatus = explode(',', $courierstatus);
}
if(empty($courierstatus)){
$excrstatus = Config::get('jayon.devmanifest_default_excl_courier_status');
if(!empty($excrstatus)){
$model = $model->whereNotIn('courier_status', $excrstatus);
}
}else{
$model = $model->whereIn('courier_status', $courierstatus);
}
*/
if ($period_from == '' || is_null($period_from)) {
$datefrom = date('Y-m-d 00:00:00', strtotime($period_from));
$dateto = date('Y-m-d 23:59:59', strtotime($period_to));
} else {
$datefrom = date('Y-m-d 00:00:00', strtotime($period_from));
$dateto = date('Y-m-d 23:59:59', strtotime($period_to));
$model = $model->where(function ($q) use($datefrom, $dateto) {
$q->whereBetween('assignment_date', array($datefrom, $dateto));
});
}
if ($device == '' || is_null($device)) {
} else {
$model = $model->where('device_id', '=', $device);
}
if ($courier == '' || is_null($courier)) {
} else {
$model = $model->where('courier_id', '=', $courier);
}
if ($logistic == '' || is_null($logistic)) {
} else {
$model = $model->where('logistic', '=', $logistic);
}
/*
->where('status',$this->config->item('trans_status_admin_courierassigned'))
->or_where('status',$this->config->item('trans_status_mobile_pickedup'))
->or_where('status',$this->config->item('trans_status_mobile_enroute'))
->or_()
->group_start()
->where('status',$this->config->item('trans_status_new'))
->where('pending_count >', 0)
->group_end()
*/
$model = $model->where(function ($qr) {
$qr->where('status', Config::get('jayon.trans_status_admin_courierassigned'))->orWhere('status', Config::get('jayon.trans_status_mobile_pickedup'))->orWhere('status', Config::get('jayon.trans_status_mobile_enroute'))->orWhere(function ($q) {
$q->where('status', Config::get('jayon.trans_status_new'))->where('pending_count', '>', 0);
});
});
$actualresult = $model->get();
$tattrs = array('width' => '100%', 'class' => 'table table-bordered table-striped');
$thead = array();
$thead[] = array(array('value' => 'No.', 'attr' => ''), array('value' => 'Zone', 'attr' => ''), array('value' => 'TOKO ONLINE', 'attr' => ''), array('value' => 'Type', 'attr' => ''), array('value' => 'Status', 'attr' => ''), array('value' => 'KEPADA', 'attr' => ''), array('value' => 'Total Price', 'attr' => ''), array('value' => 'Delivery Charge', 'attr' => ''), array('value' => 'COD Surcharge', 'attr' => ''), array('value' => 'Total Charge', 'attr' => ''), array('value' => 'ALAMAT', 'attr' => ''), array('value' => 'Phone', 'attr' => ''), array('value' => 'No Kode Penjualan Toko', 'attr' => ''), array('value' => 'PENERIMA PAKET', 'attr' => 'colspan="2"'));
$thead[] = array(array('value' => 'Mohon tunjukkan kartu identitas untuk di foto sebagai bagian bukti penerimaan', 'attr' => 'style="text-align:center;" colspan="13"'), array('value' => 'TANDA TANGAN', 'attr' => ''), array('value' => 'NAMA', 'attr' => 'class="bold center" style="width:50px" '));
$tabdata = array();
$seq = 1;
$total_billing = 0;
$total_delivery = 0;
$total_cod = 0;
$d = 0;
$gt = 0;
$lastdate = '';
$courier_name = '';
$counts = array('Delivery Only' => 0, 'COD' => 0, 'CCOD' => 0, 'PS' => 0, 'pending' => 0);
foreach ($actualresult as $r) {
$counts[$r->delivery_type] += 1;
if ($r->pending_count > 0) {
$counts['pending'] += 1;
}
$courier_name = $r->courier_name;
//$total = str_replace(array(',','.'), '', $r->total_price);
//$dsc = str_replace(array(',','.'), '', $r->total_discount);
//$tax = str_replace(array(',','.'), '',$r->total_tax);
//$dc = str_replace(array(',','.'), '',$r->delivery_cost);
//$cod = str_replace(array(',','.'), '',$r->cod_cost);
$total = $r->total_price;
$dsc = $r->total_discount;
$tax = $r->total_tax;
$dc = $r->delivery_cost;
$cod = $r->cod_cost;
$total = is_nan((double) $total) ? 0 : (double) $total;
$dsc = is_nan((double) $dsc) ? 0 : (double) $dsc;
$tax = is_nan((double) $tax) ? 0 : (double) $tax;
$dc = is_nan((double) $dc) ? 0 : (double) $dc;
$cod = is_nan((double) $cod) ? 0 : (double) $cod;
$payable = 0;
//$details = $this->db->
//where('delivery_id',$r->delivery_id)->order_by('unit_sequence','asc')->get($this->config->item('delivery_details_table'));
//$details = $details->result_array();
$details = Deliverydetail::where('delivery_id', '=', $r->delivery_id)->orderBy('unit_sequence', 'asc')->get()->toArray();
$d = 0;
$gt = 0;
foreach ($details as $value => $key) {
$u_total = $key['unit_total'];
$u_discount = $key['unit_discount'];
$gt += is_nan((double) $u_total) ? 0 : (double) $u_total;
$d += is_nan((double) $u_discount) ? 0 : (double) $u_discount;
}
if ($gt == 0) {
if ($total > 0 && $payable) {
$gt = $total;
}
}
$payable = $gt;
$total_delivery += (double) $dc;
$total_cod += (double) $cod;
$total_billing += (double) $payable;
$db = '';
if ($r->delivery_bearer == 'merchant') {
$dc = 0;
$db = 'M';
} else {
$db = 'B';
}
//force all DO to zero
$cb = '';
if ($r->cod_bearer == 'merchant') {
$cod = 0;
$cb = 'M';
} else {
$cb = 'B';
}
$codclass = '';
if ($r->delivery_type == 'COD' || $r->delivery_type == 'CCOD') {
$chg = $gt - $dsc + $tax + $dc + $cod;
//$chg = $gt + $dc + $cod;
$codclass = 'cod';
} else {
$dc = 0;
$cod = 0;
$chg = $dc;
}
$fcode = $r->fulfillment_code == '' ? '' : '<hr />' . $r->fulfillment_code;
$phone_dupe = $r->same_phone == 1 ? 'class="dupe"' : '';
$email_dupe = $r->same_email == 1 ? 'class="dupe"' : '';
$tabdata[] = array(array('value' => $seq, 'attr' => ''), array('value' => $r->buyerdeliveryzone, 'attr' => ''), array('value' => $r->merchant_name, 'attr' => ''), array('value' => $r->delivery_type, 'attr' => 'class="currency ' . $codclass . '" '), array('value' => $r->status . '<br /><br />' . $r->pickup_status . '<br /><br />' . $r->warehouse_status, 'attr' => ''), array('value' => $r->buyer_name, 'attr' => ''), array('value' => $payable == 0 ? 0 : Ks::idr($payable), 'attr' => 'class="currency ' . $codclass . '" '), array('value' => $dc == 0 ? 0 : Ks::idr($dc), 'attr' => 'class="currency ' . $codclass . '" style="position:relative;"'), array('value' => $cod == 0 ? 0 : Ks::idr($cod), 'attr' => 'class="currency ' . $codclass . '" style="position:relative;"'), array('value' => $chg == 0 ? 0 : Ks::idr($chg), 'attr' => 'class="currency ' . $codclass . '" '), array('value' => $r->shipping_address, 'attr' => ''), array('value' => '<span ' . $phone_dupe . ' >' . $this->split_phone($r->phone) . '<br />' . $this->split_phone($r->mobile1) . '<br />' . $this->split_phone($r->mobile2) . '</span>', 'attr' => ''), array('value' => $this->hide_trx($r->merchant_trans_id) . $fcode . '<br/>' . $this->date_did($r->delivery_id), 'attr' => 'class="currency cod"'), array('value' => '', 'attr' => ''), array('value' => '', 'attr' => ''));
$seq++;
}
$mtable = new HtmlTable($tabdata, $tattrs, $thead);
$tables[] = $mtable->build();
$this->table_raw = $tables;
if ($this->print == true || $this->pdf == 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 = '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->title = 'Manifest Pengiriman Harian - Retur';
$this->place_action = 'none';
$this->show_select = false;
$this->can_add = false;
$this->is_report = true;
Breadcrumbs::addCrumb('Manifest', URL::to(strtolower($this->controller_name)));
$this->additional_filter = View::make(strtolower($this->controller_name) . '.addfilter')->with('submit_url', 'manifest')->render();
$db = Config::get('lundin.main_db');
$company = Input::get('acc-company');
//device=&courier=&logistic=&date-from=2015-10-24
$period_from = Input::get('date-from');
$period_to = Input::get('date-to');
$device = Input::get('device');
$courier = Input::get('courier');
$logistic = Input::get('logistic');
$position = Input::get('position');
$trip = Input::get('trip');
$status = Input::get('status');
$courierstatus = Input::get('courier-status');
if ($period_to == '' || is_null($period_to)) {
$period_to = date('Y0m', time());
}
$this->def_order_by = 'TRANS_DATETIME';
$this->def_order_dir = 'DESC';
$this->place_action = 'none';
$this->show_select = false;
/* Start custom queries */
$model = $this->model;
if ($status == '' || is_null($status)) {
$status = Config::get('jayon.manifest_default_status');
$status[] = Config::get('jayon.trans_status_mobile_return');
} else {
$status = explode(',', $status);
}
if (empty($status)) {
$exstatus = Config::get('jayon.manifest_default_excl_status');
if (!empty($exstatus)) {
$model = $model->whereNotIn('status', $exstatus);
}
} else {
$model = $model->whereIn('status', $status);
}
if ($courierstatus == '' || is_null($courierstatus)) {
$courierstatus = Config::get('jayon.manifest_default_courier_status');
} else {
$courierstatus = explode(',', $courierstatus);
}
if (empty($courierstatus)) {
$excrstatus = Config::get('jayon.manifest_default_excl_courier_status');
if (!empty($excrstatus)) {
$model = $model->whereNotIn('courier_status', $excrstatus);
}
} else {
$model = $model->whereIn('courier_status', $courierstatus);
}
if ($period_from == '' || is_null($period_from)) {
$def_from = date('Y-m-d', time());
$model = $model->where('pick_up_date', '=', new MongoDate(strtotime($def_from)));
} else {
$model = $model->where('pick_up_date', '=', new MongoDate(strtotime($period_from)));
}
if ($device == '' || is_null($device)) {
} else {
$model = $model->where('device_key', '=', $device);
}
if ($courier == '' || is_null($courier)) {
} else {
$model = $model->where('courier_id', '=', $courier);
}
if ($logistic == '' || is_null($logistic)) {
} else {
$model = $model->where('logistic', '=', $logistic);
}
if ($position == '' || is_null($position)) {
} else {
$model = $model->where('position', '=', $position);
}
if ($trip == '' || is_null($trip)) {
} else {
$model = $model->where(function ($t) use($trip) {
$t->where('trip', '=', intval($trip))->orWhere('trip', '=', strval($trip));
});
}
$actualresult = $model->get();
$tattrs = array('width' => '100%', 'class' => 'table table-bordered table-striped');
$thead = array();
//No. Kota Type Status KEPADA ALAMAT Phone Order ID Fulfillment ID Jumlah Box PENERIMA PAKET
//TANDA TANGAN NAMA
$thead[] = array(array('value' => '#', 'attr' => 'rowspan=2'), array('value' => 'Delivery Date', 'attr' => 'rowspan="2" '), array('value' => 'Kota', 'attr' => 'rowspan="2" class="center"'), array('value' => 'Type', 'attr' => 'rowspan="2" '), array('value' => 'Logistic', 'attr' => 'rowspan="2" '), array('value' => 'Device', 'attr' => 'rowspan="2" '), array('value' => 'Status', 'attr' => 'rowspan="2" '), array('value' => 'KEPADA / CUSTOMER', 'attr' => 'rowspan="2" '), array('value' => 'ALAMAT', 'attr' => 'rowspan="2" style="min-width:200px;"'), array('value' => 'Telepon', 'attr' => 'rowspan="2" '), array('value' => 'Order ID', 'attr' => 'rowspan="2" '), array('value' => 'Fulfillment ID', 'attr' => 'rowspan="2" '), array('value' => 'Nilai COD', 'attr' => 'rowspan="2" '), array('value' => 'Jumlah Paket', 'attr' => 'rowspan="2" '), array('value' => 'PENERIMA', 'attr' => 'colspan="4"'));
$thead[] = array(array('value' => 'JUMLAH DITERIMA DI HUB', 'attr' => ''), array('value' => 'TANGGAL / JAM TERIMA', 'attr' => ''), array('value' => 'TANDA TANGAN', 'attr' => ''), array('value' => 'NAMA', 'attr' => 'class="bold center" style="width:50px" '));
$seq = 1;
$tabdata = array();
foreach ($actualresult as $m) {
if ($m->pick_up_date instanceof MongoDate) {
$pick_up_date = date('Y-m-d H:i:s', $m->pick_up_date->sec);
$dattr = '';
} else {
$pick_up_date = $m->pick_up_date;
$dattr = 'class="red"';
}
$tabdata[] = array(array('value' => $seq, 'attr' => ''), array('value' => $pick_up_date, 'attr' => $dattr), array('value' => $m->consignee_olshop_city, 'attr' => ''), array('value' => $m->delivery_type, 'attr' => ''), array('value' => $m->logistic, 'attr' => ''), array('value' => $m->device_name, 'attr' => ''), array('value' => Prefs::translatestatus($m->status, 'delivery'), 'attr' => ''), array('value' => $m->consignee_olshop_name, 'attr' => ''), array('value' => $m->consignee_olshop_addr, 'attr' => ''), array('value' => $m->consignee_olshop_phone, 'attr' => ''), array('value' => $m->no_sales_order, 'attr' => ''), array('value' => $m->consignee_olshop_orderid, 'attr' => ''), array('value' => Ks::idr($m->cod), 'attr' => 'style="text-align:right;"'), array('value' => $m->number_of_package, 'attr' => ''), array('value' => '', 'attr' => ''), array('value' => '', 'attr' => ''), array('value' => '', 'attr' => ''), array('value' => '', 'attr' => ''));
$seq++;
}
$mtable = new HtmlTable($tabdata, $tattrs, $thead);
$tables[] = $mtable->build();
$this->table_raw = $tables;
if ($this->print == true || $this->pdf == true || $this->xls == true) {
return $tables;
} else {
return parent::reportPageGenerator();
}
}
public function getIndex()
{
$this->title = 'Delivery Report';
$this->place_action = 'none';
$this->show_select = false;
$this->can_add = false;
$this->is_report = true;
Breadcrumbs::addCrumb('Manifest', URL::to(strtolower($this->controller_name)));
$this->additional_filter = View::make('shared.addfilter')->with('submit_url', 'deliveryreport')->render();
//device=&courier=&logistic=&date-from=2015-10-24
$period_from = Input::get('date-from');
$period_to = Input::get('date-to');
$device = Input::get('device');
$courier = Input::get('courier');
$merchant = Input::get('merchant');
$logistic = Input::get('logistic');
$status = Input::get('status');
$courierstatus = Input::get('courier-status');
if ($period_to == '' || is_null($period_to)) {
$period_to = date('Y-m-d', time());
}
if ($period_from == '' || is_null($period_from)) {
$period_from = date('Y-m-d', time());
}
$this->def_order_by = 'TRANS_DATETIME';
$this->def_order_dir = 'DESC';
$this->place_action = 'none';
$this->show_select = false;
$mtab = Config::get('jayon.assigned_delivery_table');
$model = new Shipment();
$model = $model->select(DB::raw('count(*) as count, year(ordertime) as orderyear,weekofyear(ordertime) as orderweek, date(ordertime) as orderdate, m.merchantname as merchant_name, delivery_type'))->leftJoin('members as m', $mtab . '.merchant_id', '=', 'm.id')->orderBy('m.merchantname', 'asc');
if ($period_from == '' || is_null($period_from)) {
$datefrom = date('Y-m-d 00:00:00', strtotime($period_from));
$dateto = date('Y-m-d 23:59:59', strtotime($period_to));
} else {
$datefrom = date('Y-m-d 00:00:00', strtotime($period_from));
$dateto = date('Y-m-d 23:59:59', strtotime($period_to));
$model = $model->where(function ($q) use($datefrom, $dateto) {
$q->whereBetween('ordertime', array($datefrom, $dateto));
});
}
$model->orderBy('ordertime', 'asc')->groupBy('orderdate')->groupBy('orderyear')->groupBy('merchant_id')->groupBy('delivery_type');
$actualresult = $model->get();
/* Start custom queries */
$tattrs = array('width' => '100%', 'class' => 'table table-bordered table-striped');
$bymc = array();
$effdates = array();
foreach ($actualresult as $mc) {
if (isset($bymc[$mc->delivery_type][$mc->merchant_name][$mc->orderyear][$mc->orderweek][$mc->orderdate])) {
$bymc[$mc->delivery_type][$mc->merchant_name][$mc->orderyear][$mc->orderweek][$mc->orderdate] += $mc->count;
} else {
$bymc[$mc->delivery_type][$mc->merchant_name][$mc->orderyear][$mc->orderweek][$mc->orderdate] = $mc->count;
}
$effdates[$mc->orderyear][$mc->orderweek][] = $mc->orderdate;
}
$effdates2 = array();
foreach ($effdates as $yr => $wk) {
ksort($wk);
foreach ($wk as $k => $v) {
$dts = array_unique($v);
$effdates2[$yr][$k][] = array_shift($dts);
$effdates2[$yr][$k][] = array_pop($dts);
}
}
//print_r($effdates2);
//print_r($bymc);
$bydc = array();
$tpd = array();
$bpd = array();
$wpd = array();
$dtotal = array();
$btotal = array();
$wtotal = array();
$headvar1 = array(array('value' => '', 'attr' => ''), array('value' => '', 'attr' => ''), array('value' => '', 'attr' => ''));
$headvar2 = array(array('value' => '', 'attr' => ''), array('value' => '', 'attr' => ''), array('value' => '', 'attr' => ''));
$headvar3 = array(array('value' => '', 'attr' => ''), array('value' => '', 'attr' => ''), array('value' => '', 'attr' => ''));
$headvar4 = array(array('value' => 'No.', 'attr' => ''), array('value' => 'Merchant', 'attr' => ''), array('value' => 'Total', 'attr' => ''));
$weekspan = 0;
foreach ($effdates2 as $y => $w) {
$cy = '';
foreach ($w as $wk => $v) {
$headvar1[] = array('value' => '', 'attr' => '');
if ($cy != $y) {
$headvar2[] = array('value' => $y, 'attr' => '');
} else {
$headvar2[] = array('value' => '', 'attr' => '');
}
$headvar3[] = array('value' => $wk, 'attr' => '');
$headvar4[] = array('value' => $v[0] . ' - ' . $v[1], 'attr' => '');
$cy = $y;
$weekspan++;
}
}
//print $weekspan;
$tabdata = array();
$weekspan++;
$tarr = array();
$tharr = array();
$thval = array();
foreach ($bymc as $t => $m) {
//$weekspan += 1;
//$tabdata[] = $head;
$seq = 1;
$totaltype = 0;
foreach ($m as $mc => $yr) {
$row = array();
$row[] = array('value' => $seq, 'attr' => '');
$row[] = array('value' => $mc, 'attr' => '');
$valrows = array();
$totalrows = 0;
foreach ($effdates2 as $yr => $w) {
foreach ($w as $wk => $dt) {
$val = 0;
if (isset($bymc[$t][$mc][$yr][$wk])) {
//print_r($bymc[$t][$mc][$yr][$wk]);
$vr = $bymc[$t][$mc][$yr][$wk];
foreach ($vr as $k => $v) {
$val += $v;
}
} else {
$val = 0;
}
$valrows[] = array('value' => $val, 'attr' => '');
$totalrows += $val;
}
$totaltype += $totalrows;
}
$row[] = array('value' => $totalrows, 'attr' => '');
$mrow = array_merge($row, $valrows);
$tarr[$t][] = $mrow;
$seq++;
}
// subhead
$head = array();
$head[] = array('value' => '', 'attr' => '');
$head[] = array('value' => strtoupper($t), 'attr' => 'style="text-align:right;"');
$head[] = array('value' => $totaltype, 'attr' => '');
for ($i = 1; $i < $weekspan; $i++) {
$head[] = array('value' => '', 'attr' => '');
}
$tharr[$t] = $head;
$thval[$t] = $totaltype;
}
$totalorder = 0;
foreach ($tharr as $t => $v) {
$tabdata[] = $tharr[$t];
$totalorder += $thval[$t];
foreach ($tarr[$t] as $tv) {
$tabdata[] = $tv;
}
}
$sumup[] = array('value' => '', 'attr' => '');
$sumup[] = array('value' => '<b>TOTAL</b>', 'attr' => 'style="text-align:right;"');
$sumup[] = array('value' => $totalorder, 'attr' => 'style="text-align:right;"');
for ($i = 1; $i < $weekspan; $i++) {
$sumup[] = array('value' => '', 'attr' => '');
}
$tabdata[] = $sumup;
//die();
$thead = array();
//$thead[] = $headvar1;
$thead[] = $headvar2;
$thead[] = $headvar3;
$thead[] = $headvar4;
$seq = 1;
$total_billing = 0;
$total_delivery = 0;
$total_cod = 0;
$d = 0;
$gt = 0;
$lastdate = '';
$courier_name = '';
$csv_data = array();
$dids = array();
$cntcod = 0;
$cntccod = 0;
$cntdo = 0;
$cntps = 0;
$cntreturn = 0;
$box_count = 0;
$weight_sum = 0;
//total per columns
$tcod = 0;
$tccod = 0;
$tdo = 0;
$tps = 0;
$treturn = 0;
$tbox = 0;
$tweight = 0;
$totalrow = array();
$mname = '';
$cd = '';
$mtable = new HtmlTable($tabdata, $tattrs, $thead);
$tables[] = $mtable->build();
$this->table_raw = $tables;
$report_header_data = array('cod' => $cntcod, 'ccod' => $cntccod, 'do' => $cntdo, 'ps' => $cntps, 'return' => $cntreturn, 'avg' => 0);
if ($this->print == true || $this->pdf == true) {
return array('tables' => $tables, 'report_header_data' => $report_header_data);
} else {
return parent::reportPageGenerator();
}
}
public function getPrint($session_id)
{
$trx = Transaction::where('sessionId', $session_id)->get()->toArray();
$pay = Payment::where('sessionId', $session_id)->get()->toArray();
$tab = array();
foreach ($trx as $t) {
$tab[$t['SKU']]['description'] = $t['productDetail']['itemDescription'];
$tab[$t['SKU']]['qty'] = isset($tab[$t['SKU']]['qty']) ? $tab[$t['SKU']]['qty'] + 1 : 1;
$tab[$t['SKU']]['tagprice'] = $t['productDetail']['priceRegular'];
$tab[$t['SKU']]['total'] = isset($tab[$t['SKU']]['total']) ? $tab[$t['SKU']]['total'] + $t['productDetail']['priceRegular'] : $t['productDetail']['priceRegular'];
}
$tab_data = array();
$gt = 0;
foreach ($tab as $k => $v) {
$tab_data[] = array(array('value' => $v['description'], 'attr' => 'class="left"'), array('value' => $v['qty'], 'attr' => 'class="center"'), array('value' => Ks::idr($v['tagprice']), 'attr' => 'class="right"'), array('value' => Ks::idr($v['total']), 'attr' => 'class="right"'));
$gt += $v['tagprice'];
}
$tab_data[] = array('', '', '', Ks::idr($gt));
$header = array('things to buy', 'unit', 'tagprice', array('value' => 'price to pay', 'attr' => 'style="text-align:right"'));
$attr = array('class' => 'table', 'id' => 'transTab', 'style' => 'width:100%;', 'border' => '0');
$t = new HtmlTable($tab_data, $attr, $header);
$tr_tab = $t->build();
$viewmodel = Template::where('type', 'invoice')->where('status', 'active')->first();
return DbView::make($viewmodel)->field('body')->with('transtab', $tr_tab)->with('trx', $trx)->with('pay', $pay);
}
public function getIndex()
{
$this->title = 'Delivery Time';
$this->place_action = 'none';
$this->show_select = false;
$this->can_add = false;
$this->is_report = true;
Breadcrumbs::addCrumb('Manifest', URL::to(strtolower($this->controller_name)));
$this->additional_filter = View::make('shared.addfilter')->with('submit_url', 'deliverybydate')->render();
$db = Config::get('lundin.main_db');
$company = Input::get('acc-company');
//device=&courier=&logistic=&date-from=2015-10-24
$period_from = Input::get('date-from');
$period_to = Input::get('date-to');
$device = Input::get('device');
$courier = Input::get('courier');
$merchant = Input::get('merchant');
$logistic = Input::get('logistic');
$status = Input::get('status');
$courierstatus = Input::get('courier-status');
if ($period_to == '' || is_null($period_to)) {
$period_to = date('Y-m-d', time());
}
if ($period_from == '' || is_null($period_from)) {
$period_from = date('Y-m-d', time());
}
$this->def_order_by = 'TRANS_DATETIME';
$this->def_order_dir = 'DESC';
$this->place_action = 'none';
$this->show_select = false;
/* Start custom queries */
$mtab = Config::get('jayon.assigned_delivery_table');
$model = $this->model;
$model = $model->select('assignment_date', 'ordertime', 'deliverytime', 'delivery_note', 'pending_count', 'recipient_name', 'delivery_id', $mtab . '.merchant_id as merchant_id', 'cod_bearer', 'delivery_bearer', 'buyer_name', 'buyerdeliverycity', 'buyerdeliveryzone', 'c.fullname as courier_name', 'd.identifier as device_name', $mtab . '.phone', $mtab . '.mobile1', $mtab . '.mobile2', 'application_id', 'weight', 'merchant_trans_id', 'm.merchantname as merchant_name', 'm.fullname as fullname', 'a.application_name as app_name', 'a.domain as domain ', 'delivery_type', 'shipping_address', 'status', 'pickup_status', 'warehouse_status', 'cod_cost', 'delivery_cost', 'total_price', 'total_tax', 'total_discount', 'box_count')->leftJoin('members as m', Config::get('jayon.incoming_delivery_table') . '.merchant_id', '=', 'm.id')->leftJoin('applications as a', Config::get('jayon.assigned_delivery_table') . '.application_id', '=', 'a.id')->leftJoin('devices as d', Config::get('jayon.assigned_delivery_table') . '.device_id', '=', 'd.id')->leftJoin('couriers as c', Config::get('jayon.assigned_delivery_table') . '.courier_id', '=', 'c.id');
/*
$model = $model
->where(function($query){
$query->where('status','=', Config::get('jayon.trans_status_admin_courierassigned') )
->orWhere('status','=', Config::get('jayon.trans_status_mobile_pickedup') )
->orWhere('status','=', Config::get('jayon.trans_status_mobile_enroute') )
->orWhere(function($q){
$q->where('status', Config::get('jayon.trans_status_new'))
->where(Config::get('jayon.incoming_delivery_table').'.pending_count', '>', 0);
});
});
*/
if ($status == '' || is_null($status)) {
$status = Config::get('jayon.devmanifest_default_status');
} else {
$status = explode(',', $status);
}
if (empty($status)) {
$exstatus = Config::get('jayon.devmanifest_default_excl_status');
if (!empty($exstatus)) {
//$model = $model->whereNotIn('status', $exstatus);
}
} else {
//$model = $model->whereIn('status', $status);
}
/*
if($courierstatus == '' || is_null($courierstatus) ){
$courierstatus = Config::get('jayon.devmanifest_default_courier_status');
}else{
$courierstatus = explode(',', $courierstatus);
}
if(empty($courierstatus)){
$excrstatus = Config::get('jayon.devmanifest_default_excl_courier_status');
if(!empty($excrstatus)){
$model = $model->whereNotIn('courier_status', $excrstatus);
}
}else{
$model = $model->whereIn('courier_status', $courierstatus);
}
*/
if ($period_from == '' || is_null($period_from)) {
$datefrom = date('Y-m-d 00:00:00', strtotime($period_from));
$dateto = date('Y-m-d 23:59:59', strtotime($period_to));
} else {
$datefrom = date('Y-m-d 00:00:00', strtotime($period_from));
$dateto = date('Y-m-d 23:59:59', strtotime($period_to));
$model = $model->where(function ($q) use($datefrom, $dateto) {
$q->whereBetween('assignment_date', array($datefrom, $dateto));
});
}
if ($merchant == '' || is_null($merchant)) {
} else {
$model = $model->where(Config::get('jayon.incoming_delivery_table') . '.merchant_id', '=', $merchant);
}
if ($device == '' || is_null($device)) {
} else {
$model = $model->where('device_id', '=', $device);
}
if ($courier == '' || is_null($courier)) {
} else {
$model = $model->where('courier_id', '=', $courier);
}
if ($logistic == '' || is_null($logistic)) {
} else {
$model = $model->where('logistic', '=', $logistic);
}
$model = $model->where(function ($qr) {
$qr->where('status', Config::get('jayon.trans_status_admin_courierassigned'))->orWhere('status', Config::get('jayon.trans_status_mobile_pickedup'))->orWhere('status', Config::get('jayon.trans_status_mobile_enroute'))->orWhere(function ($q) {
$q->where('status', Config::get('jayon.trans_status_new'))->where('pending_count', '>', 0);
});
});
$model->orderBy('device_name', 'asc')->orderBy('buyerdeliverycity', 'asc')->orderBy('buyerdeliveryzone', 'asc')->orderBy('merchant_name', 'asc');
$actualresult = $model->get();
$tattrs = array('width' => '100%', 'class' => 'table table-bordered table-striped');
$bymc = array();
foreach ($actualresult as $mc) {
$bymc[$mc->merchant_name][] = $mc;
}
//print_r($bymc);
$bydc = array();
$tpd = array();
$bpd = array();
$wpd = array();
foreach ($actualresult as $dc) {
$bydc[$dc->device_name][$dc->buyerdeliverycity][$dc->buyerdeliveryzone][] = $dc;
if (is_null($dc->actual_weight) || $dc->actual_weight == '') {
$actual_weight = Prefs::getWeightNominal($dc->weight, $dc->application_id);
} else {
$actual_weight = $dc->actual_weight;
}
//print $dc->weight."\r\n";
//print $dc->application_id."\r\n";
//print $actual_weight."\r\n";
if (isset($tpd[$dc->device_name])) {
$tpd[$dc->device_name] += 1;
$bpd[$dc->device_name] += $dc->box_count;
$wpd[$dc->device_name] += $actual_weight;
} else {
$tpd[$dc->device_name] = 1;
$bpd[$dc->device_name] = $dc->box_count;
$wpd[$dc->device_name] = $actual_weight;
}
}
$dtotal = array();
$btotal = array();
$wtotal = array();
foreach ($tpd as $dk => $dv) {
$dtotal[] = $dv;
}
foreach ($bpd as $dk => $dv) {
$btotal[] = $dv;
}
foreach ($wpd as $dk => $dv) {
$wtotal[] = $dv;
}
if (count($dtotal) > 0) {
$dmax = max($dtotal);
} else {
$dmax = 1;
}
if (count($btotal) > 0) {
$bmax = max($btotal);
} else {
$bmax = 1;
}
if (count($wtotal) > 0) {
$wmax = max($wtotal);
} else {
$wmax = 1;
}
$headvar1 = array(array('value' => 'No.', 'attr' => ''), array('value' => 'Device', 'attr' => ''), array('value' => 'Total per Device', 'attr' => ''), array('value' => 'Jumlah Box', 'attr' => ''), array('value' => 'Berat', 'attr' => ''), array('value' => 'Kota', 'attr' => ''), array('value' => 'Kecamatan', 'attr' => ''), array('value' => 'Total', 'attr' => ''));
$headvar2 = array(array('value' => '', 'attr' => ''), array('value' => '', 'attr' => ''), array('value' => '', 'attr' => ''), array('value' => '', 'attr' => ''), array('value' => '', 'attr' => ''), array('value' => '', 'attr' => ''), array('value' => '', 'attr' => ''), array('value' => '', 'attr' => ''));
foreach (array_keys($bymc) as $mctitle) {
$headvar1[] = array('value' => $mctitle, 'attr' => 'colspan="3" class="vtext" style="min-height:100px;"');
$headvar2[] = array('value' => 'COD', 'attr' => '');
$headvar2[] = array('value' => 'DO', 'attr' => '');
$headvar2[] = array('value' => 'P', 'attr' => '');
}
$thead = array();
$thead[] = $headvar1;
$thead[] = $headvar2;
$seq = 1;
$total_billing = 0;
$total_delivery = 0;
$total_cod = 0;
$d = 0;
$gt = 0;
$lastdate = '';
$courier_name = '';
$order2assigndays = 0;
$assign2deliverydays = 0;
$order2deliverydays = 0;
$csv_data = array();
$dids = array();
foreach ($actualresult as $ar) {
$dids[] = $ar->delivery_id;
}
/*
$details = Deliverylog::whereIn('delivery_id',$dids)
->where(function($q){
$q->where('status',Config::get('jayon.trans_status_mobile_delivered'))
->orWhere('status',Config::get('jayon.trans_status_admin_courierassigned'))
->orWhere('status',Config::get('jayon.trans_status_new'))
->orWhere('status',Config::get('jayon.trans_status_rescheduled'))
->orWhere('status',Config::get('jayon.trans_status_mobile_return'));
})
->orderBy('timestamp','desc')
->get()->toArray();
$dlist = array();
foreach ($details as $dt) {
$dlist[$dt['delivery_id']][] = $dt;
}
*/
//print_r($dlist);
$tabdata = array();
$cntcod = 0;
$cntccod = 0;
$cntdo = 0;
$cntps = 0;
$cntreturn = 0;
$box_count = 0;
$weight_sum = 0;
//total per columns
$tcod = 0;
$tccod = 0;
$tdo = 0;
$tps = 0;
$treturn = 0;
$tbox = 0;
$tweight = 0;
$totalrow = array();
$mname = '';
$cd = '';
foreach ($bydc as $d => $c) {
foreach ($c as $ct => $zn) {
foreach ($zn as $z => $o) {
if ($d == $cd) {
$currddev = '';
$currdtotal = '';
$box_count = '';
$weight_sum = '';
} else {
$currddev = $d;
$currdtotal = $tpd[$d];
$box_count = $bpd[$d];
$weight_sum = $wpd[$d];
}
$tbox += $box_count;
$tweight += $weight_sum;
$cd = $d;
$row = array(array('value' => $seq, 'attr' => ''), array('value' => $currddev, 'attr' => ''));
$maxattr = $dmax == $currdtotal ? 'style="background-color:red;"' : '';
$row[] = array('value' => $currdtotal, 'attr' => $maxattr);
$maxattr = $bmax == $box_count ? 'style="background-color:red;"' : '';
$row[] = array('value' => $box_count, 'attr' => $maxattr);
$maxattr = $wmax == $weight_sum ? 'style="background-color:red;"' : '';
$row[] = array('value' => $weight_sum, 'attr' => $maxattr);
$row[] = array('value' => $ct, 'attr' => '');
$row[] = array('value' => $z, 'attr' => '');
$row[] = array('value' => count($o), 'attr' => '');
if (isset($bydc[$d][$ct][$z])) {
$mv = $bydc[$d][$ct][$z];
foreach ($bymc as $mcx => $mcv) {
$cod = 0;
$do = 0;
$p = 0;
foreach ($mv as $mo) {
//print $mo->merchant_name.' '.$mcx."/r/n";
if ($mo->merchant_name == $mcx) {
if ($mo->delivery_type == 'COD' || $mo->delivery_type == 'CCOD') {
$cod++;
}
if ($mo->delivery_type == 'Delivery Only' || $mo->delivery_type == 'DO') {
$do++;
}
if ($mo->status == 'pending') {
$p++;
}
}
}
$row[] = array('value' => $cod, 'attr' => '');
$row[] = array('value' => $do, 'attr' => '');
$row[] = array('value' => $p, 'attr' => '');
}
}
$tabdata[] = $row;
$seq++;
}
}
}
$totalrow = array(array('value' => '', 'attr' => ''), array('value' => '', 'attr' => ''), array('value' => '', 'attr' => ''), array('value' => $tbox, 'attr' => ''), array('value' => $tweight, 'attr' => ''), array('value' => '', 'attr' => ''));
$coloffset = 6;
$colc = 0;
foreach ($tabdata as $td) {
//print_r($td);
for ($ci = 0; $ci < count($td) - $coloffset; $ci++) {
if (isset($totalrow[$coloffset + $ci])) {
$totalrow[$coloffset + $ci] += $td[$coloffset + $ci]['value'];
} else {
$totalrow[$coloffset + $ci] = $td[$coloffset + $ci]['value'];
}
}
}
array_unshift($tabdata, $totalrow);
$avgdata = array(array('value' => 'Rata-rata<br />( dlm satuan hari )', 'attr' => 'colspan="6"'), array('value' => number_format($assign2deliverydays / $seq, 2, ',', '.'), 'attr' => 'style="font-size:18px;font-weight:bold;"'), array('value' => '', 'attr' => 'colspan="7"'));
//array_unshift($tabdata, $avgdata);
//array_push($tabdata, $avgdata);
$mtable = new HtmlTable($tabdata, $tattrs, $thead);
$tables[] = $mtable->build();
$this->table_raw = $tables;
$report_header_data = array('cod' => $cntcod, 'ccod' => $cntccod, 'do' => $cntdo, 'ps' => $cntps, 'return' => $cntreturn, 'avg' => number_format($assign2deliverydays / $seq, 2, ',', '.'));
if ($this->print == true || $this->pdf == true) {
return array('tables' => $tables, 'report_header_data' => $report_header_data);
} 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();
}
}
public function getIndex()
{
set_time_limit(0);
$this->title = 'Data Tool';
$this->place_action = 'none';
$this->show_select = false;
$this->can_add = false;
$this->is_report = true;
Breadcrumbs::addCrumb('Data Tool', URL::to(strtolower($this->controller_name)));
$this->additional_filter = View::make(strtolower($this->controller_name) . '.addfilter')->with('submit_url', 'datatool')->render();
$db = Config::get('lundin.main_db');
if (is_null($this->report_filter_input)) {
$in = Input::all();
} else {
$in = $this->report_filter_input;
}
//$company = $in['acc-company'];
//device=&courier=&logistic=&date-from=2015-10-24
//print_r($in);
$period_from = isset($in['date-from']) ? $in['date-from'] : '';
$period_to = isset($in['date-to']) ? $in['date-to'] : '';
$device = isset($in['device']) ? $in['device'] : '';
$courier = isset($in['courier']) ? $in['courier'] : '';
$merchant = isset($in['merchant']) ? $in['merchant'] : '';
$logistic = isset($in['logistic']) ? $in['logistic'] : '';
$city = isset($in['city']) ? $in['city'] : '';
$zone = isset($in['zone']) ? $in['zone'] : '';
$pendingcount = isset($in['pending-count']) ? $in['pending-count'] : '';
$timebase = isset($in['time-base']) ? $in['time-base'] : 'delivery_order_active.created';
$status = isset($in['status']) ? $in['status'] : '';
$courierstatus = isset($in['courier-status']) ? $in['courier-status'] : '';
$pickupstatus = isset($in['pickup-status']) ? $in['pickup-status'] : '';
if ($period_to == '' || is_null($period_to)) {
$period_to = date('Y-m-d', time());
}
if ($period_from == '' || is_null($period_from)) {
$period_from = date('Y-m-d', time());
}
$this->def_order_by = 'TRANS_DATETIME';
$this->def_order_dir = 'DESC';
$this->place_action = 'none';
$this->show_select = false;
/* Start custom queries */
$mtab = Config::get('jayon.assigned_delivery_table');
$model = $this->model;
$model = $model->select('assignment_date', 'ordertime', 'pickuptime', 'deliverytime', 'delivery_note', 'pending_count', 'recipient_name', 'delivery_id', $mtab . '.merchant_id as merchant_id', 'cod_bearer', 'delivery_bearer', 'buyer_name', 'buyerdeliveryzone', 'buyerdeliverycity', 'c.fullname as courier_name', $mtab . '.phone', $mtab . '.mobile1', $mtab . '.mobile2', 'merchant_trans_id', 'fulfillment_code', 'm.merchantname as merchant_name', 'm.fullname as fullname', 'a.application_name as app_name', 'a.domain as domain ', 'delivery_type', 'shipping_address', 'status', 'pickup_status', 'warehouse_status', 'cod_cost', 'delivery_cost', 'total_price', 'total_tax', 'total_discount')->leftJoin('members as m', Config::get('jayon.incoming_delivery_table') . '.merchant_id', '=', 'm.id')->leftJoin('applications as a', Config::get('jayon.assigned_delivery_table') . '.application_id', '=', 'a.id')->leftJoin('devices as d', Config::get('jayon.assigned_delivery_table') . '.device_id', '=', 'd.id')->leftJoin('couriers as c', Config::get('jayon.assigned_delivery_table') . '.courier_id', '=', 'c.id');
/*
$model = $model
->where(function($q){
$q->where('status',Config::get('jayon.trans_status_mobile_delivered'))
//->where('status',Config::get('jayon.trans_status_admin_courierassigned'))
->orWhere('status',Config::get('jayon.trans_status_new'))
->orWhere('status',Config::get('jayon.trans_status_rescheduled'))
->orWhere('status',Config::get('jayon.trans_status_mobile_return'));
});
*/
if ($pendingcount == '' || is_null($pendingcount)) {
} else {
if ($pendingcount == 4) {
$model = $model->where('pending_count', '>', $pendingcount);
} else {
$model = $model->where('pending_count', '=', $pendingcount);
}
}
if ($status == '' || is_null($status)) {
//$status = Config::get('jex.data_tool_default_status');
} else {
$status = explode(',', $status);
$model = $model->where(function ($qs) use($status) {
$qs->whereIn('status', $status);
});
}
if ($courierstatus == '' || is_null($courierstatus)) {
} else {
$courierstatus = explode(',', $courierstatus);
$model = $model->whereIn('courier_status', $courierstatus);
}
if ($pickupstatus == '' || is_null($pickupstatus)) {
} else {
$pickupstatus = explode(',', $pickupstatus);
$model = $model->whereIn('pickup_status', $pickupstatus);
}
/*
if(empty($status)){
$exstatus = Config::get('jayon.devmanifest_default_excl_status');
if(!empty($exstatus)){
$model = $model->whereNotIn('status', $exstatus);
}
}else{
$model = $model->whereIn('status', $status);
}
if(empty($courierstatus)){
$excrstatus = Config::get('jayon.devmanifest_default_excl_courier_status');
if(!empty($excrstatus)){
$model = $model->whereNotIn('courier_status', $excrstatus);
}
}else{
$model = $model->whereIn('courier_status', $courierstatus);
}
*/
if ($period_from == '' || is_null($period_from)) {
$datefrom = date('Y-m-d 00:00:00', time());
$dateto = date('Y-m-d 23:59:59', time());
} else {
$datefrom = date('Y-m-d 00:00:00', strtotime($period_from));
$dateto = date('Y-m-d 23:59:59', strtotime($period_to));
$model = $model->where(function ($q) use($datefrom, $dateto, $timebase) {
$q->whereBetween($timebase, array($datefrom, $dateto));
});
}
if ($merchant == '' || is_null($merchant)) {
} else {
$model = $model->where(Config::get('jayon.incoming_delivery_table') . '.merchant_id', '=', $merchant);
}
if ($device == '' || is_null($device)) {
} else {
$model = $model->where('device_id', '=', $device);
}
if ($courier == '' || is_null($courier)) {
} else {
$model = $model->where('courier_id', '=', $courier);
}
if ($logistic == '' || is_null($logistic)) {
} else {
$model = $model->where('logistic', '=', $logistic);
}
if ($city == '' || is_null($city)) {
} else {
$model = $model->where('buyerdeliverycity', 'like', '%' . $city . '%');
}
if ($zone == '' || is_null($zone)) {
} else {
$model = $model->where('buyerdeliveryzone', 'like', '%' . $zone . '%');
}
$actualresult = $model->orderBy($timebase, 'desc')->get();
$tattrs = array('width' => '100%', 'class' => 'table table-bordered table-striped');
$thead = array();
$thead[] = array(array('value' => 'No.', 'attr' => ''), array('value' => 'TOKO ONLINE', 'attr' => ''), array('value' => 'Type', 'attr' => ''), array('value' => 'Tgl Upload', 'attr' => ''), array('value' => 'Tgl Pick Up', 'attr' => ''), array('value' => 'Tgl Penugasan Terakhir <br />( Last Assignment Date )', 'attr' => ''), array('value' => 'Tgl Kirim', 'attr' => ''), array('value' => 'Pick Up -> Diterima', 'attr' => ''), array('value' => 'Kirim -> Diterima', 'attr' => ''), array('value' => 'Tgl Diterima', 'attr' => ''), array('value' => 'Status', 'attr' => ''), array('value' => 'Pending', 'attr' => ''), array('value' => 'Catatan', 'attr' => ''), array('value' => 'ALAMAT', 'attr' => ''), array('value' => 'Kecamatan', 'attr' => ''), array('value' => 'Kota', 'attr' => ''), array('value' => 'Delivery ID', 'attr' => ''), array('value' => 'No Kode Penjualan Toko', 'attr' => ''), array('value' => 'Fulfillment ID', 'attr' => ''));
/*
$thead[] = array(
array('value'=>'Mohon tunjukkan kartu identitas untuk di foto sebagai bagian bukti penerimaan','attr'=>'style="text-align:center;" colspan="13"'),
array('value'=>'TANDA TANGAN','attr'=>''),
array('value'=>'NAMA','attr'=>'class="bold center" style="width:50px" '),
);
*/
$seq = 1;
$total_billing = 0;
$total_delivery = 0;
$total_cod = 0;
$d = 0;
$gt = 0;
$lastdate = '';
$courier_name = '';
$order2assigndays = 0;
$assign2deliverydays = 0;
$order2deliverydays = 0;
$pickup2deliverydays = 0;
$csv_data = array();
$dids = array();
foreach ($actualresult as $ar) {
$dids[] = $ar->delivery_id;
}
$details = Deliverylog::whereIn('delivery_id', $dids)->where(function ($q) {
$q->where('notes', '!=', '')->orWhere('req_note', '!=', '');
})->orderBy('timestamp', 'desc')->get()->toArray();
$dlist = array();
foreach ($details as $dt) {
$dlist[$dt['delivery_id']][] = $dt;
}
$denotes = Deliverynote::whereIn('deliveryId', $dids)->orderBy('mtimestamp', 'desc')->get()->toArray();
foreach ($denotes as $dt) {
$dlist[$dt['deliveryId']][] = $dt;
}
$tabdata = array();
$cntcod = 0;
$cntccod = 0;
$cntdo = 0;
$cntps = 0;
$return = 0;
$valid_pickups = 0;
foreach ($actualresult as $r) {
$details = isset($dlist[$r->delivery_id]) ? $dlist[$r->delivery_id] : array();
$courier_name = $r->courier_name;
$total = str_replace(array(',', '.'), '', $r->total_price);
$dsc = str_replace(array(',', '.'), '', $r->total_discount);
$tax = str_replace(array(',', '.'), '', $r->total_tax);
$dc = str_replace(array(',', '.'), '', $r->delivery_cost);
$cod = str_replace(array(',', '.'), '', $r->cod_cost);
$total = (int) $total;
$dsc = (int) $dsc;
$tax = (int) $tax;
$dc = (int) $dc;
$cod = (int) $cod;
$payable = 0;
$otime = date('Y-m-d', strtotime($r->ordertime));
$ptime = date('Y-m-d', strtotime($r->pickuptime));
$dtime = date('Y-m-d', strtotime($r->deliverytime));
$ordertime = new DateTime($otime);
//print_r($details);
$notes = '';
$event_seq = 0;
$first_assignment = false;
foreach ($details as $d) {
$n = '';
if (isset($d['api_event'])) {
if ($d['api_event'] == 'admin_change_status') {
$n = $d['req_note'];
} else {
if ($d['notes'] != '') {
$n = $d['notes'];
}
}
if ($n != '' && $d['status'] != 'syncnote') {
$notes .= $d['timestamp'] . '<br />';
$notes .= '<b>' . $d['status'] . '</b><br />';
$notes .= $n . '<br /><br />';
}
if ($d['status'] == Config::get('jayon.trans_status_admin_courierassigned')) {
if ($event_seq > 0) {
$first_assignment = $d;
}
}
} else {
if ($d['note'] != '') {
$n = $d['note'];
}
if ($n != '') {
$notes .= $d['datetimestamp'] . '<br />';
$notes .= '<b>' . $d['status'] . '</b><br />';
$notes .= $n . '<br /><br />';
}
}
$event_seq++;
}
if ($first_assignment) {
$assignment_date = new DateTime($first_assignment['timestamp']);
$assignment_date->add(new DateInterval('P1D'));
} else {
$assignment_date = new DateTime($r->assignment_date);
}
$deliverytime = new DateTime($dtime);
$pickuptime = new DateTime($ptime);
$order2assign = $ordertime->diff($assignment_date);
$assign2delivery = $assignment_date->diff($deliverytime);
$pickup2delivery = $pickuptime->diff($deliverytime);
$order2delivery = $ordertime->diff($deliverytime);
if (is_null($r->deliverytime) || $r->deliverytime == '' || $r->deliverytime == '0000-00-00 00:00:00') {
$assign2delivery->d = 0;
$order2delivery->d = 0;
$pickup2delivery->d = 0;
$order2assigndays += (int) $order2assign->d;
} else {
$order2assigndays += (int) $order2assign->d;
$assign2deliverydays += (int) $assign2delivery->d;
$order2deliverydays += (int) $order2delivery->d;
}
if (is_null($pickuptime) || $pickuptime == '' || $r->pickuptime == '' || $r->pickuptime == '0000-00-00 00:00:00') {
//$r->pickuptime = $assignment_date->add(new DateInterval('P1D'))->format('Y-m-d');
//$pickuptime = new DateTime($r->pickuptime);
//$pickup2delivery = $pickuptime->diff($deliverytime);
$pickup2delivery->d = 0;
$pickup2deliverydays += (int) $pickup2delivery->d;
} else {
//$valid_pickups++;
$pickup2deliverydays += (int) $pickup2delivery->d;
$valid_pickups++;
}
$d = 0;
$gt = 0;
$cntcod = $r->delivery_type == 'COD' ? $cntcod + 1 : $cntcod;
$cntccod = $r->delivery_type == 'CCOD' ? $cntccod + 1 : $cntccod;
$cntdo = $r->delivery_type == 'Delivery Only' ? $cntdo + 1 : $cntdo;
$cntps = $r->delivery_type == 'PS' ? $cntps + 1 : $cntps;
$cntreturn = $r->status == 'returned' ? $return + 1 : $return;
//print_r($details);
/*
$notes = '';
foreach($details as $d )
{
$n = '';
$admin = true;
if($d['api_event'] == 'admin_change_status'){
$n = $d['req_note'];
}else{
if($d['notes'] != ''){
$n = $d['notes'];
}
$admin = false;
}
$isadmin = ($admin)?' [admin]':'';
if($n != '' && $d['status'] != 'syncnote'){
$notes .= $d['timestamp'].$isadmin.'<br />';
$notes .= '<b>'.$d['status'].'</b><br />';
$notes .= $n.'<br />';
}
}
*/
/*
if($gt == 0 ){
if($total > 0 && $payable)
$gt = $total;
}
$payable = $gt;
$total_delivery += (double)$dc;
$total_cod += (double)$cod;
$total_billing += (double)$payable;
$db = '';
if($r->delivery_bearer == 'merchant'){
$dc = 0;
$db = 'M';
}else{
$db = 'B';
}
//force all DO to zero
$cb = '';
if($r->cod_bearer == 'merchant'){
$cod = 0;
$cb = 'M';
}else{
$cb = 'B';
}
$codclass = '';
if($r->delivery_type == 'COD' || $r->delivery_type == 'CCOD'){
$chg = ($gt - $dsc) + $tax + $dc + $cod;
//$chg = $gt + $dc + $cod;
$codclass = 'cod';
}else{
$dc = 0;
$cod = 0;
$chg = $dc;
}
$fcode = ($r->fulfillment_code == '')?'':'<hr />'.$r->fulfillment_code;
$phone_dupe = ($r->same_phone == 1)?'class="dupe"':'';
$email_dupe = ($r->same_email == 1)?'class="dupe"':'';
*/
$tabdata[] = array(array('value' => $seq, 'attr' => ''), array('value' => $r->merchant_name, 'attr' => ''), array('value' => $r->delivery_type, 'attr' => ''), array('value' => $r->ordertime, 'attr' => ''), array('value' => $r->pickuptime, 'attr' => ''), array('value' => $r->assignment_date, 'attr' => ''), array('value' => $assignment_date->format('Y-m-d'), 'attr' => ''), array('value' => $pickup2delivery->d, 'attr' => ''), array('value' => $assign2delivery->d, 'attr' => ''), array('value' => $r->deliverytime, 'attr' => ''), array('value' => $r->status, 'attr' => ''), array('value' => $r->pending_count, 'attr' => ''), array('value' => $notes, 'attr' => ''), array('value' => $r->recipient_name . ' | ' . str_replace(array(",", '"', "\n", "\r"), '', $r->shipping_address) . ' ' . $this->split_phone($r->phone) . ' ' . $this->split_phone($r->mobile1) . ' ' . $this->split_phone($r->mobile2), 'attr' => ''), array('value' => $r->buyerdeliveryzone, 'attr' => ''), array('value' => $r->buyerdeliverycity, 'attr' => ''), array('value' => $r->delivery_id, 'attr' => ''), array('value' => $this->hide_trx($r->merchant_trans_id), 'attr' => ''), array('value' => $r->fulfillment_code, 'attr' => ''));
$seq++;
}
$valid_pickups = $valid_pickups > 0 ? $valid_pickups : 1;
$avgdata = array(array('value' => 'Rata-rata<br />( dlm satuan hari )', 'attr' => 'colspan="7"'), array('value' => number_format($pickup2deliverydays / $valid_pickups, 2, ',', '.'), 'attr' => 'style="font-size:18px;font-weight:bold;"'), array('value' => number_format($assign2deliverydays / $seq, 2, ',', '.'), 'attr' => 'style="font-size:18px;font-weight:bold;"'), array('value' => '', 'attr' => 'colspan="11"'));
array_unshift($tabdata, $avgdata);
array_push($tabdata, $avgdata);
$mtable = new HtmlTable($tabdata, $tattrs, $thead);
$tables[] = $mtable->build();
$this->table_raw = $tables;
$report_header_data = array('cod' => $cntcod, 'ccod' => $cntccod, 'do' => $cntdo, 'ps' => $cntps, 'return' => $return, 'avg' => number_format($assign2deliverydays / $seq, 2, ',', '.'));
if ($this->print == true || $this->pdf == true) {
return array('tables' => $tables, 'report_header_data' => $report_header_data);
} else {
return parent::reportPageGenerator();
}
}
$ctr = 0;
}
$tab_data[] = array(array('value' => $data['labels'][$i], 'attr' => 'class="left"'), array('value' => $data['series01']['data'][$i], 'attr' => 'class="right"'), array('value' => $data['series02']['data'][$i], 'attr' => 'class="right"'), array('value' => number_format($ctr, 2), 'attr' => 'class="left"'));
$tclicks += $data['series02']['data'][$i];
$tviews += $data['series01']['data'][$i];
}
if ($tviews > 0) {
$tctr = $tclicks / $tviews * 100;
} else {
$tctr = 0;
}
$tab_data[] = array(array('value' => 'Total', 'attr' => 'class="left"'), array('value' => $tviews, 'attr' => 'class="left"'), array('value' => $tclicks, 'attr' => 'class="left"'), array('value' => number_format($tctr, 2), 'attr' => 'class="left"'));
$header = array('Date', 'Views', 'Clicks', 'CTR (%)');
$attr = array('class' => 'table', 'id' => 'transTab', 'style' => 'width:100%;', 'border' => '0');
$t = new HtmlTable($tab_data, $attr, $header);
$summarytable = $t->build();
print $summarytable;
?>
</div>
</div>
</div>
<div id="blueimp-gallery" class="blueimp-gallery blueimp-gallery-controls">
<div class="slides"></div>
<h3 class="title"></h3>
<a class="prev">‹</a>
<a class="next">›</a>
<a class="close">×</a>
<a class="play-pause"></a>
public function getIndex()
{
$this->title = 'Delivery Time';
$this->place_action = 'none';
$this->show_select = false;
$this->can_add = false;
$this->is_report = true;
Breadcrumbs::addCrumb('Manifest', URL::to(strtolower($this->controller_name)));
$this->additional_filter = View::make('shared.addfilter')->with('submit_url', 'deliveryreport')->render();
$db = Config::get('lundin.main_db');
$company = Input::get('acc-company');
//device=&courier=&logistic=&date-from=2015-10-24
$period_from = Input::get('date-from');
$period_to = Input::get('date-to');
$device = Input::get('device');
$courier = Input::get('courier');
$merchant = Input::get('merchant');
$logistic = Input::get('logistic');
$status = Input::get('status');
$courierstatus = Input::get('courier-status');
if ($period_to == '' || is_null($period_to)) {
$period_to = date('Y-m-d', time());
}
if ($period_from == '' || is_null($period_from)) {
$period_from = date('Y-m-d', time());
}
$this->def_order_by = 'TRANS_DATETIME';
$this->def_order_dir = 'DESC';
$this->place_action = 'none';
$this->show_select = false;
/* Start custom queries */
$mtab = Config::get('jayon.assigned_delivery_table');
$model = $this->model;
/*
$model = $model->select(DB::raw('count(*) as count'),DB::raw('date(ordertime) as orderdate'),DB::raw('weekofyear(ordertime) as week'),DB::raw('month(ordertime) as month'),DB::raw('year(ordertime) as year'),'assignment_date','ordertime','delivery_type','deliverytime','delivery_note','pending_count','recipient_name','delivery_id',$mtab.'.merchant_id as merchant_id','cod_bearer','delivery_bearer','buyer_name','buyerdeliverycity','buyerdeliveryzone','c.fullname as courier_name','d.identifier as device_name', $mtab.'.phone', $mtab.'.mobile1',$mtab.'.mobile2','merchant_trans_id','m.merchantname as merchant_name','m.fullname as fullname','a.application_name as app_name','a.domain as domain ','delivery_type','shipping_address','status','pickup_status','warehouse_status','cod_cost','delivery_cost','total_price','total_tax','total_discount','box_count')
->leftJoin('members as m',Config::get('jayon.incoming_delivery_table').'.merchant_id','=','m.id')
->leftJoin('applications as a',Config::get('jayon.assigned_delivery_table').'.application_id','=','a.id')
->leftJoin('devices as d',Config::get('jayon.assigned_delivery_table').'.device_id','=','d.id')
->leftJoin('couriers as c',Config::get('jayon.assigned_delivery_table').'.courier_id','=','c.id');
$model = $model->select('assignment_date','ordertime','deliverytime','delivery_note','pending_count','recipient_name','delivery_id',$mtab.'.merchant_id as merchant_id','cod_bearer','delivery_bearer','buyer_name','buyerdeliverycity','buyerdeliveryzone','c.fullname as courier_name','d.identifier as device_name', $mtab.'.phone', $mtab.'.mobile1',$mtab.'.mobile2','application_id','weight','merchant_trans_id','m.merchantname as merchant_name','m.fullname as fullname','a.application_name as app_name','a.domain as domain ','delivery_type','shipping_address','status','pickup_status','warehouse_status','cod_cost','delivery_cost','total_price','total_tax','total_discount','box_count')
->leftJoin('members as m',Config::get('jayon.incoming_delivery_table').'.merchant_id','=','m.id')
->leftJoin('applications as a',Config::get('jayon.assigned_delivery_table').'.application_id','=','a.id')
->leftJoin('devices as d',Config::get('jayon.assigned_delivery_table').'.device_id','=','d.id')
->leftJoin('couriers as c',Config::get('jayon.assigned_delivery_table').'.courier_id','=','c.id');
*/
/*
$model = $model
->where(function($query){
$query->where('status','=', Config::get('jayon.trans_status_admin_courierassigned') )
->orWhere('status','=', Config::get('jayon.trans_status_mobile_pickedup') )
->orWhere('status','=', Config::get('jayon.trans_status_mobile_enroute') )
->orWhere(function($q){
$q->where('status', Config::get('jayon.trans_status_new'))
->where(Config::get('jayon.incoming_delivery_table').'.pending_count', '>', 0);
});
});
*/
if ($status == '' || is_null($status)) {
//$status = Config::get('jayon.devmanifest_default_status');
} else {
$status = explode(',', $status);
}
if (empty($status)) {
//$exstatus = Config::get('jayon.devmanifest_default_excl_status');
if (!empty($exstatus)) {
//$model = $model->whereNotIn('status', $exstatus);
}
} else {
//$model = $model->whereIn('status', $status);
}
/*
if($courierstatus == '' || is_null($courierstatus) ){
$courierstatus = Config::get('jayon.devmanifest_default_courier_status');
}else{
$courierstatus = explode(',', $courierstatus);
}
if(empty($courierstatus)){
$excrstatus = Config::get('jayon.devmanifest_default_excl_courier_status');
if(!empty($excrstatus)){
$model = $model->whereNotIn('courier_status', $excrstatus);
}
}else{
$model = $model->whereIn('courier_status', $courierstatus);
}
*/
if ($period_from == '' || is_null($period_from)) {
$datefrom = date('Y-m-d 00:00:00', strtotime($period_from));
$dateto = date('Y-m-d 23:59:59', strtotime($period_to));
} else {
$datefrom = date('Y-m-d 00:00:00', strtotime($period_from));
$dateto = date('Y-m-d 23:59:59', strtotime($period_to));
$model = $model->where(function ($q) use($datefrom, $dateto) {
$q->whereBetween('ordertime', array($datefrom, $dateto));
});
}
if ($merchant == '' || is_null($merchant)) {
} else {
$model = $model->where(Config::get('jayon.incoming_delivery_table') . '.merchant_id', '=', $merchant);
}
if ($device == '' || is_null($device)) {
} else {
$model = $model->where('device_id', '=', $device);
}
if ($courier == '' || is_null($courier)) {
} else {
$model = $model->where('courier_id', '=', $courier);
}
if ($logistic == '' || is_null($logistic)) {
} else {
$model = $model->where('logistic', '=', $logistic);
}
/*
$model = $model->where(function($qr){
$qr->where('status',Config::get('jayon.trans_status_admin_courierassigned'))
->orWhere('status',Config::get('jayon.trans_status_mobile_pickedup'))
->orWhere('status',Config::get('jayon.trans_status_mobile_enroute'))
->orWhere(function($q){
$q->where('status',Config::get('jayon.trans_status_new'))
->where('pending_count','>', 0);
});
});
*/
$model->orderBy('delivery_type', 'asc')->orderBy('merchant_id', 'asc');
//->orderBy('year', 'asc')
//->orderBy('week', 'asc');
//$model->groupBy('delivery_type')
// ->groupBy('merchant_id');
$actualresult = $model->get();
print_r($actualresult->toArray());
die;
$tattrs = array('width' => '100%', 'class' => 'table table-bordered table-striped');
$bymc = array();
$years = array();
$weeks = array();
$months = array();
$effdates = array();
$effweeks = array();
foreach ($actualresult as $mc) {
$weeks[] = $mc->week;
$months[] = $mc->month;
$years[] = $mc->year;
$effweeks[$mc->year][$mc->week] = $mc->week;
//if(isset($bymc[$mc->delivery_type][$mc->merchant_name][$mc->month][$mc->week])){
// $bymc[$mc->delivery_type][$mc->merchant_name][$mc->month][$mc->week] += $mc->count ;
//}else{
$bymc[$mc->delivery_type][$mc->merchant_name][$mc->year][$mc->week] = $mc->count;
if (isset($effdates[$mc->year][$mc->week][0])) {
if (isset($effdates[$mc->year][$mc->week][1])) {
$effdates[$mc->year][$mc->week][1] = $mc->orderdate;
} else {
$effdates[$mc->year][$mc->week][] = $mc->orderdate;
}
} else {
$effdates[$mc->year][$mc->week][] = $mc->orderdate;
}
//}
}
$weeks = array_unique($weeks);
$months = array_unique($months);
$years = array_unique($years);
/*
foreach($effweeks as $yr=>$wr){
foreach($wr as $ir){
$effdates[$yr][$ir] = $this->getEffDates($yr, $ir);
}
}
*/
print_r($effweeks);
//print_r($bymc);
print_r($effdates);
die;
$headvar0 = array(array('value' => 'No.', 'attr' => ''), array('value' => 'Merchant', 'attr' => ''), array('value' => 'Total', 'attr' => ''));
$headvar1 = array(array('value' => 'No.', 'attr' => ''), array('value' => 'Merchant', 'attr' => ''), array('value' => 'Total', 'attr' => ''));
$headvar2 = array(array('value' => '', 'attr' => ''), array('value' => '', 'attr' => ''), array('value' => '', 'attr' => ''));
foreach ($effdates as $yk => $wk) {
$span = 0;
foreach ($wk as $wt => $dt) {
$headvar1[] = array('value' => 'Week ' . $effweeks[$yk][$wt], 'attr' => '');
$headvar2[] = array('value' => $dt[0] . ' to ' . $dt[1], 'attr' => '');
$span++;
}
$headvar0[] = array('value' => $yk, 'attr' => 'colspan="' . $span . '"');
}
$thead = array();
$thead[] = $headvar0;
$thead[] = $headvar1;
$thead[] = $headvar2;
$seq = 1;
$total_billing = 0;
$total_delivery = 0;
$total_cod = 0;
$d = 0;
$gt = 0;
$lastdate = '';
$courier_name = '';
$order2assigndays = 0;
$assign2deliverydays = 0;
$order2deliverydays = 0;
$csv_data = array();
$dids = array();
foreach ($actualresult as $ar) {
$dids[] = $ar->delivery_id;
}
$tabdata = array();
$cntcod = 0;
$cntccod = 0;
$cntdo = 0;
$cntps = 0;
$cntreturn = 0;
$box_count = 0;
//total per columns
$tcod = 0;
$tccod = 0;
$tdo = 0;
$tps = 0;
$treturn = 0;
$totalrow = array();
$mname = '';
$cd = '';
$effweeks = array_keys($effdates);
foreach ($bymc as $t => $m) {
$pre = array();
$pre = array(array('value' => strtoupper($t), 'attr' => 'colspan="2"'), array('value' => '', 'attr' => ''));
$blanks = array();
foreach ($effweeks as $wk) {
$blanks[] = array('value' => '', 'attr' => '');
}
$tabdata[] = array_merge($pre, $blanks);
$seq = 1;
foreach ($m as $mn => $dt) {
$drow = array();
$tpm = 0;
foreach ($years as $y) {
}
foreach ($effweeks as $wk) {
foreach ($dt as $md) {
if (isset($md[$wk])) {
$drow[] = array('value' => $md[$wk], 'attr' => '');
$tpm += $md[$wk];
} else {
$drow[] = array('value' => 0, 'attr' => '');
}
}
}
$pre = array(array('value' => $seq, 'attr' => ''), array('value' => $mn, 'attr' => ''), array('value' => $tpm, 'attr' => ''));
$tabdata[] = array_merge($pre, $drow);
$seq++;
}
}
$mtable = new HtmlTable($tabdata, $tattrs, $thead);
$tables[] = $mtable->build();
$this->table_raw = $tables;
$report_header_data = array('cod' => $cntcod, 'ccod' => $cntccod, 'do' => $cntdo, 'ps' => $cntps, 'return' => $cntreturn, 'avg' => number_format($assign2deliverydays / $seq, 2, ',', '.'));
if ($this->print == true || $this->pdf == true) {
return array('tables' => $tables, 'report_header_data' => $report_header_data);
} else {
return parent::reportPageGenerator();
}
}
