public function calculation_sheet_custom($keys)
{
include_once APPPATH . "models/helperClasses/Calculation_Sheet.php";
$this->db->select("*");
$this->db->where(array('entryDate >=' => $keys['from_date'], 'entryDate <=' => $keys['to_date']));
if ($keys['company_id'] != 'all') {
$this->db->where('company_id', $keys['company_id']);
}
if ($keys['contractor_id'] != 'all') {
$this->db->where('contractor_id', $keys['contractor_id']);
}
if ($keys['product'] != 'all' && $keys['product'] != '') {
$this->db->where('product', $keys['product']);
}
if ($keys['product_type'] != 'all' && $keys['product_type'] != '') {
if ($_GET['product_type'] == 'black oil') {
$this->db->where('product_type', 'black oil');
}
if ($_GET['product_type'] == 'white oil') {
$this->db->where('product_type', 'white oil');
}
}
$result = $this->db->get('calculation_sheet_view')->result();
$calculation_sheet_white_oil = array();
foreach ($result as $record) {
array_push($calculation_sheet_white_oil, new Calculation_Sheet($record));
}
$grouped = Arrays::groupBy($calculation_sheet_white_oil, Functions::extractField('destination'), 'invoice_number');
ksort($grouped);
return $grouped;
}
public function pre_saved_sorting_columns($module)
{
$result = $this->db->get_where('sort', array('view' => $module))->result();
if (sizeof($result) > 0) {
$grouped = Arrays::groupBy($result, Functions::extractField('sort_by'), 'priority');
return $grouped;
}
return null;
}
public function pre_saved_sorting_columns($module)
{
$multiple_sorting_info = $this->multiple_sorting_info($module);
$objects = array();
foreach ($multiple_sorting_info as $record) {
array_push($objects, json_decode(json_encode($record), FALSE));
}
$grouped = Arrays::groupBy($objects, Functions::extractField('column_name'), 'column_priority');
return $grouped;
}
function tanker_trips_expenses()
{
$this->db->select('voucher_journal.trip_id as trip_id, sum(voucher_entry.debit_amount) as amount, account_titles.title as title');
$this->db->join('voucher_journal', 'voucher_journal.trip_id = trips.id', 'left');
$this->db->join('voucher_entry', 'voucher_entry.journal_voucher_id = voucher_journal.id', 'left');
$this->db->join('account_titles', 'account_titles.id = voucher_entry.account_title_id', 'left');
$this->db->where('voucher_journal.tanker_id', $this->tankerId);
$this->db->where('trips.entryDate >=', $this->from);
$this->db->where('trips.entryDate <=', $this->to);
$this->db->where('account_titles.secondary_type', 'other_expense');
$this->db->where('voucher_journal.trip_id !=', 0);
$this->db->where('voucher_journal.active', 1);
$this->db->where('trips.active', 1);
$this->db->order_by('voucher_journal.trip_id');
$this->db->group_by('voucher_journal.trip_id, voucher_entry.account_title_id');
$result = $this->db->get('trips')->result();
return Arrays::groupBy($result, Functions::extractField('trip_id'), 'trip_id');
}
public function automatic_transactions_on_trip_edit($trip_id)
{
$trip_ids = array($trip_id);
$trips = $this->trips_model->parametrized_trips_engine($trip_ids, '');
$trip = $trips[0];
/*
* fetching helping material
*/
$this->db->select("id");
$result = $this->db->get_where('account_titles', array('title' => 'Contractor Freight A/C From Company'))->result();
$contractor_freight_ac_from_company_id = $result[0]->id;
$this->db->select("id");
$result = $this->db->get_where('account_titles', array('title' => 'Company Freight A/c'))->result();
$company_freight_ac_id = $result[0]->id;
$this->db->select("id");
$result = $this->db->get_where('account_titles', array('title' => 'Income A/c'))->result();
$income_ac_id = $result[0]->id;
$this->db->select("id");
$result = $this->db->get_where('account_titles', array('title' => 'Contractor Freight A/C To Customer'))->result();
$contractor_freight_ac_to_customer_id = $result[0]->id;
$this->db->select("id");
$result = $this->db->get_where('account_titles', array('title' => 'Contractor Commission A/C'))->result();
$contractor_commission_ac_id = $result[0]->id;
$this->db->select("id");
$result = $this->db->get_where('account_titles', array('title' => 'Contractor Service Charges'))->result();
$contractor_service_charges_id = $result[0]->id;
$this->db->select("id");
$result = $this->db->get_where('account_titles', array('title' => 'Customer Freight A/c'))->result();
$customer_freight_ac_id = $result[0]->id;
$this->db->select("id");
$result = $this->db->get_where('account_titles', array('title' => 'Company Commission A/c'))->result();
$company_commission_ac_id = $result[0]->id;
$this->db->select("id");
$result = $this->db->get_where('account_titles', array('title' => 'Company W.h.t A/c'))->result();
$company_wht_ac_id = $result[0]->id;
//fetching agents ids
$company_id = $trip->company->id;
$customer_id = $trip->customer->id;
$contractor_id = $trip->contractor->id;
$tanker_id = $trip->tanker->id;
/***********************************************************************************************
***************************************Auto Voucher Editing Portion****************************/
/***Hleper Arrays***/
$voucher_entries_update_array = array();
$vouchers_to_update_array = array();
/*******************/
foreach ($trip->trip_related_details as $detail) {
//here we will claculate the amounts needed to update
$customer_freight_amount = round($detail->get_customer_freight_amount($trip->customer->freight), 3);
//$total_customer_freight += $customer_freight_amount;
$contractor_freight_amount = round($detail->get_contractor_freight_amount_according_to_company($trip->get_contractor_freight_according_to_company()), 3);
/*************Getting voucher ids associated with this detail id***************/
$this->db->select('trip_detail_voucher_relation.voucher_id');
$this->db->from('trip_detail_voucher_relation');
$this->db->join('voucher_journal', 'voucher_journal.id = trip_detail_voucher_relation.voucher_id', 'left');
$this->db->where('voucher_journal.active', 1);
$this->db->where('trip_detail_voucher_relation.trip_detail_id', $detail->product_detail_id);
/*
* test section
*/
$this->db->where('voucher_journal.auto_generated', 1);
/*******************************************************/
$result = $this->db->get()->result();
/***Hleper Arrays***/
$voucher_ids_related_detail_id = array();
/*******************/
foreach ($result as $record) {
array_push($voucher_ids_related_detail_id, $record->voucher_id);
}
/******************************************************************************/
/**************loop On Voucher Ids for updating records****************/
foreach ($voucher_ids_related_detail_id as $voucher_id) {
/**********Lets fetch the voucher to update************/
$temp_voucher_ids = array($voucher_id);
$temp_vouchers = $this->accounts_model->journal('users', 1, $temp_voucher_ids, "");
$temp_voucher = $temp_vouchers[0];
/******************************************************/
//deciding the voucher date to save
if ($temp_voucher->auto_generated == 1) {
$voucher_date = $trip->dates->filling_date;
} else {
$voucher_date = $temp_voucher->voucher_date;
}
/////////////////////
$voucher_data_to_update = array('id' => $temp_voucher->voucher_id, 'voucher_date' => $voucher_date, 'tanker_id' => $tanker_id);
array_push($vouchers_to_update_array, $voucher_data_to_update);
/*************************************************************/
switch ($temp_voucher->transaction_column) {
case 'customer_freight':
$dr_cr_amount = $customer_freight_amount;
break;
case 'contractor_freight':
$dr_cr_amount = $contractor_freight_amount;
break;
default:
$dr_cr_amount = "unknown";
}
/*************making the voucher_entries to update***********/
foreach ($temp_voucher->entries as $entry) {
//setting description
if ($temp_voucher->auto_generated == 1) {
$entry_description = "Capacity=> " . $trip->tanker->capacity . " | product=> " . $detail->product->name . " <br> Route=> " . $detail->source->name . " to " . $detail->destination->name;
} else {
$entry_description = $entry->description;
}
if ($dr_cr_amount == "unknown") {
$dr_cr_amount = $entry->dr_cr == 0 ? $entry->credit : $entry->debit;
}
if ($temp_voucher->auto_generated == 1) {
$related_customer = $customer_id;
$related_contractor = $contractor_id;
$related_company = $company_id;
} else {
$related_customer = $entry->related_agent_id;
$related_contractor = $entry->related_agent_id;
$related_company = $entry->related_agent_id;
}
$temp_entry = array('id' => $entry->id, 'description' => $entry_description, 'related_customer' => $entry->related_agent == 'customers' ? $related_customer : 0, 'related_company' => $entry->related_agent == 'companies' ? $related_company : 0, 'related_contractor' => $entry->related_agent == 'carriage_contractors' ? $related_contractor : 0, 'credit_amount' => $entry->dr_cr == 'credit' ? $dr_cr_amount : 0, 'debit_amount' => $entry->dr_cr == 'debit' ? $dr_cr_amount : 0);
array_push($voucher_entries_update_array, $temp_entry);
}
/********************************/
/********************************************/
}
/**********************************************************************/
}
if (sizeof($voucher_entries_update_array) > 0) {
$this->db->update_batch('voucher_journal', $vouchers_to_update_array, 'id');
$this->db->update_batch('voucher_entry', $voucher_entries_update_array, 'id');
}
/***********************************************************************************************
***************************************Mass Editing Portion*************************************/
/***Hleper Arrays***/
$voucher_entries_update_array = array();
$vouchers_to_update_array = array();
/*******************/
foreach ($trip->trip_related_details as $detail) {
/*************Getting voucher ids associated with this detail id***************/
$this->db->select('trip_detail_voucher_relation.voucher_id');
$this->db->from('trip_detail_voucher_relation');
$this->db->join('voucher_journal', 'voucher_journal.id = trip_detail_voucher_relation.voucher_id', 'left');
$this->db->where('voucher_journal.active', 1);
$this->db->where('trip_detail_voucher_relation.trip_detail_id', $detail->product_detail_id);
/*
* test section
*/
$this->db->where('voucher_journal.auto_generated', 0);
/*******************************************************/
$result = $this->db->get()->result();
/***Hleper Arrays***/
$voucher_ids_related_detail_id = array();
/*******************/
foreach ($result as $record) {
array_push($voucher_ids_related_detail_id, $record->voucher_id);
}
/******************************************************************************/
/**************loop On Voucher Ids for updating records****************/
foreach ($voucher_ids_related_detail_id as $voucher_id) {
//echo $voucher_id." / ";
/**********selecting trip ids against the voucher id**********/
$this->db->select('trip_detail_voucher_relation.trip_detail_id');
$this->db->from('trip_detail_voucher_relation');
$this->db->join('voucher_journal', 'voucher_journal.id = trip_detail_voucher_relation.voucher_id', 'left');
$this->db->where('voucher_journal.active', 1);
$this->db->where('trip_detail_voucher_relation.voucher_id', $voucher_id);
$result = $this->db->get()->result();
/***Hleper Arrays***/
$trip_detail_ids_related_to_voucher_id = array();
/*******************/
foreach ($result as $record) {
array_push($trip_detail_ids_related_to_voucher_id, $record->trip_detail_id);
}
/*************************************************************/
/********fetching trip_ids by trip_detail_ids***********/
$this->db->select('trip_id');
$this->db->distinct();
$this->db->from('trips_details');
$this->db->where_in('trips_details.id', $trip_detail_ids_related_to_voucher_id);
$result_3 = $this->db->get()->result();
$trip_ids_related_to_voucher_id = array();
foreach ($result_3 as $record_3) {
array_push($trip_ids_related_to_voucher_id, $record_3->trip_id);
}
/********************************************/
/*declaring variables of amounts to update*/
$total_customer_freight = 0;
$total_customer_freight_without_shortage = 0;
$total_shortage_amount = 0;
$total_contractor_freight = 0;
$total_contractor_freight_without_shortage = 0;
$total_company_commission = 0;
$total_company_wht = 0;
$total_service_charges = 0;
$total_contractor_commission = 0;
$grand_total_freight = 0;
/******************************************/
/**
* fetching white_oil and black_oil seprate trip ids
**/
$this->db->select('trip_id, trip_detail_id, product_type');
$this->db->where_in('trip_detail_id', $trip_detail_ids_related_to_voucher_id);
$raw_trip_ids_with_product_type = $this->db->get('trips_details_upper_layer_view')->result();
$grouped_by_product_type = Arrays::groupBy($raw_trip_ids_with_product_type, Functions::extractField('product_type'));
$black_oil_trip_ids = isset($grouped_by_product_type['black oil']) ? property_to_array('trip_detail_id', $grouped_by_product_type['black oil']) : [];
$white_oil_trip_ids = isset($grouped_by_product_type['white oil']) ? property_to_array('trip_detail_id', $grouped_by_product_type['white oil']) : [];
$black_oil_trip_ids = sizeof($black_oil_trip_ids) == 0 ? [0] : $black_oil_trip_ids;
$white_oil_trip_ids = sizeof($white_oil_trip_ids) == 0 ? [0] : $white_oil_trip_ids;
$this->db->select('*');
$this->db->where_in('trip_detail_id', $black_oil_trip_ids);
$black_oil_trips = $this->db->get('manage_accounts_black_oil_view')->result();
$this->db->select('*');
$this->db->where_in('trip_detail_id', $white_oil_trip_ids);
$white_oil_trips = $this->db->get('manage_accounts_white_oil_view')->result();
/**********************************************************/
foreach ($black_oil_trips as $detail) {
//here we will claculate the amounts needed to update
$customer_freight_amount = round($detail->customer_freight, 3);
$total_customer_freight += $customer_freight_amount;
$contractor_freight_amount = round($detail->net_payables, 3);
$total_contractor_freight += $contractor_freight_amount;
$contractor_commission_amount = $detail->contractor_commission_amount;
$total_contractor_commission += $contractor_commission_amount;
$company_commission_amount = round($detail->company_commission_amount, 3);
$total_company_commission += $company_commission_amount;
$wht_amount = $detail->wht_amount;
$total_company_wht += $wht_amount;
$service_charges = 0;
$service_charges = $detail->freight_amount_cmp - $detail->company_commission_amount - $detail->customer_freight - $detail->contractor_commission_amount - $detail->wht_amount - $detail->shortage_amount;
if ($service_charges > -0.1 && $service_charges < 0.1) {
$service_charges = 0;
}
$total_service_charges += $service_charges;
}
foreach ($white_oil_trips as $detail) {
//here we will claculate the amounts needed to update
$customer_freight_amount = round($detail->customer_freight_amount, 3);
$total_customer_freight += $customer_freight_amount;
$customer_freight_amount_without_shortage = round($detail->customer_freight_without_shortage, 3);
$total_customer_freight_without_shortage += $customer_freight_amount_without_shortage;
$shortage_amount = $detail->shortage_amount;
$total_shortage_amount += $shortage_amount;
$contractor_freight_amount = round($detail->contractor_freight_amount, 3);
$total_contractor_freight += $contractor_freight_amount;
$contractor_freight_amount_without_shortage = round($detail->contractor_freight_without_shortage, 3);
$total_contractor_freight_without_shortage += $contractor_freight_amount_without_shortage;
$contractor_commission_amount = $detail->contractor_commission_amount;
$total_contractor_commission += $contractor_commission_amount;
$company_commission_amount = round($detail->company_commission_amount, 3);
$total_company_commission += $company_commission_amount;
$wht_amount = $detail->wht_amount;
$total_company_wht += $wht_amount;
$service_charges = 0;
$service_charges = $detail->total_freight_cmp - $detail->company_commission_amount - $detail->customer_freight_amount - $detail->contractor_commission_amount - $detail->wht_amount;
if ($service_charges > -0.1 && $service_charges < 0.1) {
$service_charges = 0;
}
$total_service_charges += $service_charges;
$total_freight_for_company = $detail->total_freight_cmp;
$grand_total_freight += $total_freight_for_company;
}
/**********Lets fetch the voucher to update************/
$temp_voucher_ids = array($voucher_id);
$temp_vouchers = $this->accounts_model->journal('users', 1, $temp_voucher_ids, "");
$temp_voucher = $temp_vouchers[0];
/******************************************************/
/*
* here we will update what ever we want to do
*/
//deciding the voucher date to save
if ($temp_voucher->auto_generated == 1) {
$voucher_date = $trip->dates->filling_date;
} else {
$voucher_date = $temp_voucher->voucher_date;
}
/////////////////////
$voucher_data_to_update = array('id' => $temp_voucher->voucher_id, 'voucher_date' => $voucher_date);
array_push($vouchers_to_update_array, $voucher_data_to_update);
/*************************************************************/
switch ($temp_voucher->transaction_column) {
case 'customer_freight':
$dr_cr_amount = $total_customer_freight;
break;
case 'customer_freight_without_shortage':
$dr_cr_amount = $total_customer_freight_without_shortage;
break;
case 'shortage_amount':
$dr_cr_amount = $total_shortage_amount;
break;
case 'contractor_freight':
$dr_cr_amount = $total_contractor_freight;
break;
case 'contractor_freight_without_shortage':
$dr_cr_amount = $total_contractor_freight_without_shortage;
break;
case 'company_total_freight':
$dr_cr_amount = $grand_total_freight;
break;
case 'company_wht':
$dr_cr_amount = $total_company_wht;
break;
case 'company_commission':
$dr_cr_amount = $total_company_commission;
break;
case 'contractor_commission':
$dr_cr_amount = $total_contractor_commission;
break;
case 'service_charges':
$dr_cr_amount = $total_service_charges;
break;
default:
$dr_cr_amount = "unknown";
}
/*************making the voucher_entries to update***********/
foreach ($temp_voucher->entries as $entry) {
if ($dr_cr_amount == "unknown") {
$dr_cr_amount = $entry->dr_cr == 0 ? $entry->credit : $entry->debit;
}
if ($temp_voucher->auto_generated == 1) {
$related_customer = $customer_id;
$related_contractor = $contractor_id;
$related_company = $company_id;
} else {
$related_customer = $entry->related_agent_id;
$related_contractor = $entry->related_agent_id;
$related_company = $entry->related_agent_id;
}
$temp_entry = array('id' => $entry->id, 'related_customer' => $entry->related_agent == 'customers' ? $related_customer : 0, 'related_company' => $entry->related_agent == 'companies' ? $related_company : 0, 'related_contractor' => $entry->related_agent == 'carriage_contractors' ? $related_contractor : 0, 'credit_amount' => $entry->dr_cr == 'credit' ? $dr_cr_amount : 0, 'debit_amount' => $entry->dr_cr == 'debit' ? $dr_cr_amount : 0);
array_push($voucher_entries_update_array, $temp_entry);
}
/********************************/
/********************************************/
}
/**********************************************************************/
}
if (sizeof($voucher_entries_update_array) > 0) {
$this->db->update_batch('voucher_journal', $vouchers_to_update_array, 'id');
$this->db->update_batch('voucher_entry', $voucher_entries_update_array, 'id');
}
}
public function calculation_sheet_custom($keys)
{
include_once APPPATH . "models/helperClasses/Calculation_Sheet.php";
//selecting trips_details_ids which have decanding shortage voucher
$this->db->select("trips_details.id as trip_detail_id");
$this->join_trips_and_details();
$this->db->where('trips_details.shortage_voucher_decnd !=', 0);
$this->db->where(array('trips.entryDate >=' => $keys['from_date'], 'trips.entryDate <=' => $keys['to_date']));
if ($keys['company_id'] != 'all') {
$this->db->where('trips.company_id', $keys['company_id']);
}
if ($keys['contractor_id'] != 'all') {
$this->db->where('trips.contractor_id', $keys['contractor_id']);
}
if ($keys['product'] != 'all' && $keys['product'] != '') {
$this->db->where('trips_details.product', $keys['product']);
}
$result = $this->db->get('trips')->result();
//var_dump($result); die();
$details_ids_with_decanding_shortage = array(0);
foreach ($result as $record) {
array_push($details_ids_with_decanding_shortage, $record->trip_detail_id);
}
//selecting trips_details_ids which have destination shortage voucher
$this->db->select("trips_details.id as trip_detail_id");
$this->join_trips_and_details();
$this->db->where('trips_details.shortage_voucher_dest !=', 0);
$this->db->where('trips_details.shortage_voucher_decnd', 0);
$this->db->where(array('trips.entryDate >=' => $keys['from_date'], 'trips.entryDate <=' => $keys['to_date']));
if ($keys['company_id'] != 'all') {
$this->db->where('trips.company_id', $keys['company_id']);
}
if ($keys['contractor_id'] != 'all') {
$this->db->where('trips.contractor_id', $keys['contractor_id']);
}
if ($keys['product'] != 'all' && $keys['product'] != '') {
$this->db->where('trips_details.product', $keys['product']);
}
$result = $this->db->get('trips')->result();
$details_ids_with_destination_shortage = array(0);
foreach ($result as $record) {
array_push($details_ids_with_destination_shortage, $record->trip_detail_id);
}
//var_dump($details_ids_with_destination_shortage);
/*--- Selecting every thing with decanding shortage vouchers -----*/
$this->select_calculation_sheet_stuff();
$this->db->from("trips");
$this->join_trips_with_every_thing();
$this->join_trips_details_and_decanding_shortage();
$this->db->where('trips.active', 1);
$this->db->where(array('trips.entryDate >=' => $keys['from_date'], 'trips.entryDate <=' => $keys['to_date']));
if ($keys['company_id'] != 'all') {
$this->db->where('trips.company_id', $keys['company_id']);
}
if ($keys['contractor_id'] != 'all') {
$this->db->where('trips.contractor_id', $keys['contractor_id']);
}
if ($keys['product'] != 'all' && $keys['product'] != '') {
$this->db->where('trips_details.product', $keys['product']);
}
if ($keys['product_type'] != 'all' && $keys['product_type'] != '') {
if ($_GET['product_type'] == 'black oil') {
$this->black_oil();
}
if ($_GET['product_type'] == 'white oil') {
$this->white_oil();
}
}
if (sizeof($details_ids_with_decanding_shortage) > 0) {
$this->db->where_in('trips_details.id', $details_ids_with_decanding_shortage);
}
$this->db->group_by('voucher_journal.id');
$this->db->order_by('trips_details.id');
$result = $this->db->get()->result();
$calculation_sheet_black_oil = array();
foreach ($result as $record) {
array_push($calculation_sheet_black_oil, new Calculation_Sheet($record));
}
/*--- Selecting every thing with destination shortage vouchers -----*/
$this->select_calculation_sheet_stuff();
$this->db->from("trips");
$this->join_trips_with_every_thing();
$this->join_trips_details_and_destination_shortage();
$this->db->where('trips.active', 1);
$this->db->where(array('trips.entryDate >=' => $keys['from_date'], 'trips.entryDate <=' => $keys['to_date']));
if ($keys['company_id'] != 'all') {
$this->db->where('trips.company_id', $keys['company_id']);
}
if ($keys['contractor_id'] != 'all') {
$this->db->where('trips.contractor_id', $keys['contractor_id']);
}
if ($keys['product'] != 'all' && $keys['product'] != '') {
$this->db->where('trips_details.product', $keys['product']);
}
if ($keys['product_type'] != 'all' && $keys['product_type'] != '') {
if ($_GET['product_type'] == 'black oil') {
$this->black_oil();
}
if ($_GET['product_type'] == 'white oil') {
$this->white_oil();
}
}
if (isset($keys['agent_type']) && isset($keys['agent_id'])) {
switch ($keys['agent_type']) {
case 'other_agents':
$this->db->where('voucher_entry.related_other_agent !=', 0);
if ($keys['agent_id'] != '') {
$this->db->where('voucher_entry.related_other_agent', $keys['agent_id']);
}
break;
case 'customers':
$this->db->where('voucher_entry.related_customer !=', 0);
if ($keys['agent_id'] != '') {
$this->db->where('voucher_entry.related_customer', $keys['agent_id']);
}
break;
case 'companies':
$this->db->where('voucher_entry.related_company !=', 0);
if ($keys['agent_id'] != '') {
$this->db->where('voucher_entry.related_company', $keys['agent_id']);
}
break;
case 'carriage_contractors':
$this->db->where('voucher_entry.related_contractor !=', 0);
if ($keys['agent_id'] != '') {
$this->db->where('voucher_entry.related_contractor', $keys['agent_id']);
}
break;
}
}
if (sizeof($details_ids_with_decanding_shortage) > 0) {
$this->db->where_in('trips_details.id', $details_ids_with_destination_shortage);
}
$this->db->group_by('voucher_journal.id');
$this->db->order_by('trips_details.id');
$result = $this->db->get()->result();
foreach ($result as $record) {
array_push($calculation_sheet_black_oil, new Calculation_Sheet($record));
}
$grouped = Arrays::groupBy($calculation_sheet_black_oil, Functions::extractField('destination'), 'invoice_number');
ksort($grouped);
return $grouped;
}
public function all_routes_freights($route_type = 'primary')
{
include_once APPPATH . "models/helperClasses/Route_Details.php";
switch ($route_type) {
case 'primary':
$this->db->where('routes.type', 1);
break;
case 'secondary':
$this->db->where('routes.type', 3);
break;
case 'secondary_local':
$this->db->where('routes.type', 4);
break;
default:
$this->db->where('routes.type', 1);
break;
}
$this->db->select('routes.id as route_id, freights.id as freight_id,
freights.startDate, freights.endDate, freights.freight,
routes.source, routes.destination, routes.product,
');
$this->db->from('routes');
$this->db->join('freights', 'freights.route_id = routes.id', 'left');
$this->db->where('routes.active', 1);
/*$this->db->join('cities as sourceCity','sourceCity.id = routes.source','left');
$this->db->join('cities as destinationCity','destinationCity.id = routes.destination','left');
$this->db->join('products','products.id = routes.product','left');*/
$this->db->order_by('freights.id', 'desc');
$results = $this->db->get()->result();
foreach ($results as &$record) {
$record->route = $record->source . "_" . $record->destination . "_" . $record->product;
}
$grouped = Arrays::groupBy($results, Functions::extractField('route'));
return $grouped;
}
private function groupRawReportByRoute($report)
{
//adding extra_column
foreach ($report as &$record) {
$record->route_product_key = $record->source_id . "_" . $record->destination_id . "_" . $record->product_id;
}
// grouping by route_product_key and triming
return $this->trimm_expense_report(Arrays::groupBy($report, Functions::extractField('route_product_key')));
}
/**
* Generic select function
*
* @param {mixed} $paramValues
* @param {string} $key (optional) Key field which can be used to group the output
* @param {boolean} $cleanUp Do clean up of columns in query output
*/
public function select($paramValues = null, $key = null, $cleanUp = true)
{
// If no key is supplied take default from JSON spec
if (is_null($key)) {
$key = $this->output->key;
}
$data = $this->execute($paramValues);
// Keys should not always be cleaned up
if ($cleanUp) {
$this->cleanUp($data, 'keys', $key);
}
// Child defs should always be cleaned up
$this->cleanUp($data, 'childDefs', $key);
// We are ready if output is not an array of assocs
if ($this->output->columns != 'all' || $this->output->rows != 'all') {
return $data;
}
// Apply rows2columns transformation
if (property_exists($this->output, 'rows2columns')) {
$data = Arrays::rows2columns($data, $this->output->rows2columns->key, $this->output->rows2columns->name, $this->output->rows2columns->value);
}
// Apply grouping transformation
if ($key && $this->output->group) {
return Arrays::groupBy($data, $key, true);
}
// Apply key transformation
if ($key) {
return Arrays::toAssoc($data, $key);
}
return $data;
}
public function tankers_income_statement($keys)
{
include_once APPPATH . "models/helperClasses/tankers_routes.php";
include_once APPPATH . "models/helperClasses/income_statement_row.php";
$this->db->select("voucher_journal.tanker_id, tankers.truck_number as tanker_number,\n SUM(voucher_entry.debit_amount) as total_expense,\n SUM(voucher_entry.credit_amount) as total_income,\n (SUM(voucher_entry.credit_amount) - SUM(voucher_entry.debit_amount)) as profit,\n account_titles.secondary_type,\n ");
$this->db->from('voucher_journal');
$this->db->join('voucher_entry', 'voucher_entry.journal_voucher_id = voucher_journal.id', 'left');
$this->db->join('account_titles', 'account_titles.id = voucher_entry.account_title_id', 'left');
$this->db->join('tankers', 'tankers.id = voucher_journal.tanker_id', 'left');
$this->db->where(array('voucher_journal.active' => 1, 'voucher_journal.ignored !=' => 1, 'voucher_journal.person_tid' => 'users.1', 'voucher_journal.tanker_id !=' => 0));
$where = "(account_titles.type = 'expense' OR account_titles.type = 'income')";
$this->db->where($where);
$this->db->group_by('voucher_journal.tanker_id, account_titles.secondary_type');
if ($keys['from'] != '') {
$this->db->where('voucher_journal.voucher_date >=', $keys['from']);
}
if ($keys['to'] != '') {
$this->db->where('voucher_journal.voucher_date <=', $keys['to']);
}
if ($keys['tanker_id'] != '') {
$this->db->where_in('voucher_journal.tanker_id', $keys['tanker_id']);
}
if ($keys['customer_id'] != '') {
$this->db->where('voucher_entry.related_customer', $keys['customer_id']);
}
//checking weather user demands Sorting or not
/*if(isset($keys['sort']) && $keys['sort'] != ''){
//$this->db->order_by($keys['sort']['sort_by'],$keys['sort']['order']);
//$this->db->order_by('voucher_journal.id',$keys['sort']['order']);
}else{
$this->db->order_by('voucher_journal.tanker_id','asc');
}*/
/////////////////////////////////////////////////////
$result = $this->db->get()->result();
$grouped = Arrays::groupBy($result, Functions::extractField('tanker_id'));
$income_statement = array();
foreach ($grouped as $group) {
$income_statement_row = new Income_Statement_Row();
foreach ($group as $record) {
$income_statement_row->tanker_id = $record->tanker_id;
$income_statement_row->tanker_number = $record->tanker_number;
if ($record->secondary_type == 'other_expense') {
$income_statement_row->other_expense = $record->total_expense;
}
if ($record->secondary_type == 'shortage_expense') {
$income_statement_row->shortage_expense = $record->total_expense;
}
$income_statement_row->total_income += $record->total_income;
$income_statement_row->secondary_type = $record->secondary_type;
}
$income_statement_row->profit = $income_statement_row->total_income - ($income_statement_row->shortage_expense + $income_statement_row->other_expense);
array_push($income_statement, $income_statement_row);
}
usort($income_statement, array("Sorting_Model", "sort_tanker_income_statement"));
/*Getting the routes information*/
$this->db->select("trips.tanker_id, source_cities.cityName as source, destination_cities.cityName as destination,\n COUNT(trips_details.source) as route_counter,\n ");
$this->db->from('trips');
$this->db->join('trips_details', 'trips_details.trip_id = trips.id', 'left');
$this->db->join('cities as source_cities', 'source_cities.id = trips_details.source', 'left');
$this->db->join('cities as destination_cities', 'destination_cities.id = trips_details.destination', 'left');
$this->db->group_by(array('trips.tanker_id', 'trips_details.source', 'trips_details.destination'));
$this->db->where(array('trips.active' => 1));
if ($keys['from'] != '') {
$this->db->where('trips.entryDate >=', $keys['from']);
}
if ($keys['to'] != '') {
$this->db->where('trips.entryDate <=', $keys['to']);
}
if ($keys['tanker_id'] != '') {
$this->db->where_in('trips.tanker_id', $keys['tanker_id']);
}
if ($keys['customer_id'] != '') {
$this->db->where('trips.customer_id', $keys['customer_id']);
}
$result = $this->db->get()->result();
$tankers_routes = new Tankers_Routes($result);
/**********************************/
return array('tankers_routes' => $tankers_routes, 'income_statement' => $income_statement);
}
public function __construct($statuses)
{
$this->statuses = Arrays::groupBy($statuses, Functions::extractField('trip_detail_id'));
}
public function groupShortageDataByTripDetailId()
{
$shortagesData = Arrays::groupBy($this->getShortagesData(), Functions::extractField('trip_detail_id'));
return $shortagesData;
}