public function dr_cr_statuses(&$accounts)
{
$trip_detail_ids = property_to_array('trip_detail_id', $accounts);
$this->db->select('*');
$this->db->where_in('trip_detail_id', $trip_detail_ids);
$result = $this->db->get('dr_cr_status_for_manage_accounts_view')->result();
return new Dr_Cr_Status_Manager($result);
}
public function getFreightOnShortageVoucherId()
{
$shortage = $this->getShortage();
$voucher_type = $shortage->type == 1 ? 'dest_freight_on_shortage' : 'decnd_freight_on_shortage';
$trip_detail_id = $shortage->trip_detail_id;
// get vouchers
$this->db->select('id');
$this->db->where('voucher_type', $voucher_type);
$this->db->where('trip_product_detail_id', $trip_detail_id);
$this->db->where('active', 1);
$result = $this->db->get('voucher_journal')->result();
$voucher_id = sizeof($result) > 0 ? property_to_array('id', $result)[0] : 0;
return $voucher_id;
//$vouchers = $this->accounts_model->journal("users", "1", $voucher_ids, "", "");
}
public function vehicle_position_report($comand = null)
{
$headerData = array('title' => 'Virik Logistics | Reports', 'page' => 'reports');
$all_trip_types = $this->trips_model->get_trip_types();
if ($comand == 'generate') {
$keys = array();
$from = $this->helper_model->first_day_of_month();
$to = date('Y-m-d');
$trip_types = property_to_array('id', $all_trip_types);
if (isset($_GET['from']) && $_GET['from'] != '') {
$from = $_GET['from'];
}
if (isset($_GET['to']) && $_GET['to'] != '') {
$to = $_GET['to'];
}
if (isset($_GET['trip_types']) && sizeof($_GET['trip_types']) > 0) {
if (in_array('all', $_GET['trip_types'])) {
$trip_types = property_to_array('id', $all_trip_types);
} else {
$trip_types = $_GET['trip_types'];
}
} else {
$trip_types = [0];
}
/*----------
* getting trip types
* */
$this->db->select('*');
$this->db->where_in('id', $trip_types);
$selected_trip_types = $this->db->get('trip_types')->result();
/*---------------------------------*/
$bodyData = array('reports' => $this->reports_model->generate_vehicle_position_report($from, $to, $trip_types), 'trip_types' => $all_trip_types, 'selected_trip_types' => $selected_trip_types, 'from' => $from, 'to' => $to, 'someMessage' => '');
if (isset($_GET['print'])) {
if (isset($_POST['check'])) {
//$bodyData['report'] = $this->helper_model->filter_records($bodyData['report'], $_POST['check'],"trip_detail_id");
}
if (isset($_POST['column'])) {
//$bodyData['columns'] = $_POST['column'];
}
$this->load->view('reports/freight_report/print/freight_report', $bodyData);
} else {
if (isset($_GET['export'])) {
if (isset($_POST['check'])) {
//$bodyData['report'] = $this->helper_model->filter_records($bodyData['report'], $_POST['check'],"trip_detail_id");
}
if (isset($_POST['column'])) {
$bodyData['columns'] = $_POST['column'];
}
$this->load->view('reports/freight_report/export/freight_report', $bodyData);
} else {
$this->load->view('components/header', $headerData);
$this->load->view('reports/vehicle_position_report/show/vehicle_position_report', $bodyData);
$this->load->view('components/footer');
}
}
} else {
$bodyData = array('trip_types' => $all_trip_types, 'from' => $this->helper_model->first_day_of_month(), 'to' => date('Y-m-d'), 'someMessage' => '');
$this->load->view('components/header', $headerData);
$this->load->view('reports/vehicle_position_report/make/vehicle_position_report', $bodyData);
$this->load->view('components/footer');
}
}
function get_trip_idssss()
{
$this->db->select('trip_detail_id');
$this->db->where('voucher_id', '18802');
$result = $this->db->get('trip_detail_voucher_relation')->result();
$details_ids = property_to_array('trip_detail_id', $result);
$this->db->select('trip_id');
$this->db->where_in('id', $details_ids);
$result = $this->db->get('trips_details')->result();
print_r(property_to_array('trip_id', $result));
}
public function get_mass_trip_details_ids($mass_voucher_id)
{
$this->db->select('trip_detail_id');
$this->db->where('voucher_id', $mass_voucher_id);
$result = $this->db->get('trip_detail_voucher_relation')->result();
$trip_details_ids = property_to_array('trip_detail_id', $result);
$trip_details_ids[] = 0;
return $trip_details_ids;
}
public function _validate_shortage_ids_for_commiting($value)
{
$shortage_ids = explode('_', $value);
$shortage_ids = arr_val_del(0, $shortage_ids);
//checking if there is atleast one id selected
if (sizeof($shortage_ids) == 0) {
$this->form_validation->set_message('_validate_shortage_ids_for_commiting', 'Please select atleast one shortage.');
return false;
}
//checking if the ids are all integers.
foreach ($shortage_ids as $id) {
if (ctype_digit($id) == false) {
$this->form_validation->set_message('_validate_shortage_ids_for_commiting', 'Invalid shortage ids.');
return false;
}
}
//checking if there is already a voucher of given shortage ids
$this->db->select('shortage_id');
$this->db->where_in('voucher_journal.shortage_id', $shortage_ids);
$this->db->where('voucher_journal.active', 1);
$result = $this->db->get('voucher_journal')->result();
if (sizeof($result) > 0) {
$this->form_validation->set_message('_validate_shortage_ids_for_commiting', 'Some shortages (' . join(', ', property_to_array('shortage_id', $result)) . ') have already been commited. please try again.');
return false;
}
//checking if shortage ids are there in the shortage table..
$this->db->select('id');
$this->db->distinct();
$this->db->where_in('id', $shortage_ids);
$result = $this->db->get('shortages')->result();
if (sizeof($result) != sizeof($shortage_ids)) {
$this->form_validation->set_message('_validate_shortage_ids_for_commiting', 'Some shortages have been deleted from the system. please try again.');
return false;
}
return true;
}
public function search_black_oil($keys, $limit, $start)
{
//applying keys....
include_once APPPATH . "serviceProviders/Sort.php";
$sorting_info = Sort::columns('manage_accounts_black_oil');
/*
* -------------------------------
* Search By Account Titles
* -------------------------------
*/
if ($keys['account_title'] != '') {
$trip_detail_ids = [];
if ($keys['dr_cr'] != '') {
switch ($keys['dr_cr']) {
case 1:
$this->db->select('ma.trip_detail_id');
$this->db->join('dr_cr_status_for_manage_accounts_view as dr_cr_status', 'dr_cr_status.trip_detail_id = ma.trip_detail_id', 'left');
$this->db->where('account_title_id', $keys['account_title']);
$this->db->where('dr_cr_status.dr_cr', 1);
$result = $this->db->get('manage_accounts_white_oil_view as ma')->result();
$trip_detail_ids = property_to_array('trip_detail_id', $result);
break;
case 2:
$debit_ids[] = 0;
$this->db->select('dr_cr_status_for_manage_accounts_view.trip_detail_id');
$this->db->where('account_title_id', $keys['account_title']);
$this->db->where('dr_cr_status_for_manage_accounts_view.dr_cr', '1');
$result = $this->db->get('dr_cr_status_for_manage_accounts_view')->result();
$debit_ids = property_to_array('trip_detail_id', $result);
$this->db->select('dr_cr_status_for_manage_accounts_view.trip_detail_id');
$this->db->where_not_in('dr_cr_status_for_manage_accounts_view.trip_detail_id', $debit_ids);
$result = $this->db->get('dr_cr_status_for_manage_accounts_view')->result();
$trip_detail_ids = property_to_array('trip_detail_id', $result);
break;
case 0:
$this->db->select('ma.trip_detail_id');
$this->db->join('dr_cr_status_for_manage_accounts_view as dr_cr_status', 'dr_cr_status.trip_detail_id = ma.trip_detail_id', 'left');
$this->db->where('account_title_id', $keys['account_title']);
$this->db->where('dr_cr_status.dr_cr', 0);
$result = $this->db->get('manage_accounts_white_oil_view as ma')->result();
$trip_detail_ids = property_to_array('trip_detail_id', $result);
break;
case 3:
$credit_ids[] = 0;
$this->db->select('dr_cr_status_for_manage_accounts_view.trip_detail_id');
$this->db->where('account_title_id', $keys['account_title']);
$this->db->where('dr_cr_status_for_manage_accounts_view.dr_cr', '0');
$result = $this->db->get('dr_cr_status_for_manage_accounts_view')->result();
$credit_ids = property_to_array('trip_detail_id', $result);
$this->db->select('dr_cr_status_for_manage_accounts_view.trip_detail_id');
$this->db->where_not_in('dr_cr_status_for_manage_accounts_view.trip_detail_id', $credit_ids);
$result = $this->db->get('dr_cr_status_for_manage_accounts_view')->result();
$trip_detail_ids = property_to_array('trip_detail_id', $result);
break;
}
}
if (sizeof($trip_detail_ids) == 0) {
$trip_detail_ids[] = 0;
}
}
/*-------------------------------*/
$this->db->select('*');
/*
* ------------------------------
* SEARCH BY TRIPS BILLING
* ------------------------------
* */
if ($keys['bill_status'] != '') {
if ($keys['bill_status'] == 1) {
$this->db->where('bill_id !=', 0);
if ($keys['billed_from'] != '') {
$this->db->where('billed_date_time >', $keys['billed_from']);
}
if ($keys['billed_to'] != '') {
$this->db->where('billed_date_time <', $keys['billed_to']);
}
}
if ($keys['bill_status'] == 0) {
$this->db->where('bill_id', 0);
}
}
/*--------------------------------------------*/
/*
* ------------------------------
* OPEN AND CLOSED TRIPS
* -----------------------------
* */
if ($keys['trip_status'] != '') {
if ($keys['trip_status'] == 1) {
$this->db->where('stn_number', '');
} else {
if ($keys['trip_status'] == 2) {
$this->db->where('stn_number !=', '');
}
}
}
/*----------------------------------------*/
if ($keys['account_title'] != '') {
if (sizeof($trip_detail_ids) > 0) {
$this->db->where_in('trip_detail_id', $trip_detail_ids);
}
}
if ($keys['from'] != '') {
$this->db->where('trip_date >=', $keys['from']);
}
if ($keys['to'] != '') {
$this->db->where('trip_date <=', $keys['to']);
}
if ($keys['trip_id'] != '') {
$this->db->where('trip_id', $keys['trip_id']);
}
if ($keys['trip_type'] != '') {
$this->db->where_in('trip_type_id', $keys['trip_type']);
}
if ($keys['trip_master_type'] != '') {
if ($keys['trip_master_type'] == 'primary') {
$where = "(trip_type_id = 2 OR trip_type_id = 4)";
$this->db->where($where);
} else {
if ($keys['trip_master_type'] == 'secondary') {
$where = "(trip_type_id = 1 OR trip_type_id = 3)";
$this->db->where($where);
} else {
if ($keys['trip_master_type'] == 'secondary_local') {
$where = "(trip_type_id = 6)";
$this->db->where($where);
}
}
}
}
if ($keys['trip_master_types'] != '') {
$trip_types = array();
foreach ($keys['trip_master_types'] as $type) {
switch ($type) {
case "primary":
$trip_types = [1, 2, 4, 5];
break;
case "secondary":
array_push($trip_types, 3);
break;
case "secondary_local":
array_push($trip_types, 6);
break;
}
}
$this->db->where_in('trip_type_id', $trip_types);
}
if ($keys['tanker'] != '') {
$this->db->where_in('tanker_id', $keys['tanker']);
}
if ($keys['entryDate'] != '') {
$this->db->where('trip_date', $keys['entryDate']);
}
if ($keys['product'] != '') {
$this->db->where_in('product_id', $keys['product']);
}
if ($keys['trips_routes'] != '') {
$where = "(";
foreach ($keys['trips_routes'] as $route) {
$route_parts = explode('_', $route);
$where .= "(source_id = " . $route_parts[0] . " AND destination_id = " . $route_parts[1] . ") OR ";
}
$where .= ")";
$where_parts = explode(') OR )', $where);
$where = $where_parts[0];
$where .= "))";
$this->db->where($where);
} else {
if ($keys['source'] != '') {
$this->db->where_in('source_id', $keys['source']);
}
if ($keys['destination'] != '') {
$this->db->where_in('destination_id', $keys['destination']);
}
}
if ($keys['company'] != '') {
$this->db->where_in('company_id', $keys['company']);
}
if ($keys['cmp_freight_unit'] != '') {
$this->db->where('company_freight_unit', $keys['cmp_freight_unit']);
}
if ($keys['cst_freight_unit'] != '') {
$this->db->where('customer_freight_unit', $keys['cst_freight_unit']);
}
if ($keys['wht'] != '') {
$this->db->where('wht', $keys['wht']);
}
if ($keys['company_commission'] != '') {
$this->db->where('company_commission', $keys['company_commission']);
}
if ($keys['contractor'] != '') {
$this->db->where_in('contractor_id', $keys['contractor']);
}
if ($keys['contractor_commission'] != '') {
$this->db->where('contractor_commission', $keys['contractor_commission']);
}
if ($keys['customer'] != '') {
$this->db->where_in('customer_id', $keys['customer']);
}
if ($keys['cst_freight_unit'] != '') {
$this->db->where('customer_freight_unit', $keys['cst_freight_unit']);
}
/*
* --------------------------------------
* filter by trip details ids
* --------------------------------------
* */
if (isset($keys['trip_detail_ids']) && $keys['trip_detail_ids'] != '') {
$this->db->where_in('trip_detail_id', $keys['trip_detail_ids']);
}
foreach ($sorting_info as $sort) {
$this->db->order_by($sort['sort_by'], $sort['order_by']);
}
$this->db->limit($limit, $start);
$accounts = $this->db->get('manage_accounts_black_oil_view')->result();
return $accounts;
}
public function fetch_other_expenses_including_trip_expenses($trip_type)
{
$this->db->select("\n tankers.truck_number as vehicle_number, trips.tanker_id as tanker_id\n ");
$this->db->from('trips');
/*------- applying joins ----------*/
$this->db->join('tankers', 'tankers.id = trips.tanker_id', 'left');
/*---- applying conditions ------------*/
$this->db->where('trips.entryDate >=', $this->from);
$this->db->where('trips.entryDate <=', $this->to);
$this->db->where('trips.type', $trip_type);
$this->db->where('trips.active', '1');
$this->db->group_by('trips.tanker_id');
$tankers_result = $this->db->get()->result();
$tanker_sockets = [];
foreach ($tankers_result as $record) {
$tanker_sockets[$record->vehicle_number] = 0;
}
$tanker_ids = property_to_array('tanker_id', $tankers_result);
$tanker_ids = sizeof($tanker_ids) > 0 ? $tanker_ids : [0];
$this->db->select("\n tankers.truck_number as vehicle_number,\n SUM(voucher_entry.debit_amount) as repair_expense\n ");
$this->db->from('voucher_journal');
/*------- applying joins ----------*/
$this->db->join('tankers', 'tankers.id = voucher_journal.tanker_id', 'left');
$this->db->join('voucher_entry', 'voucher_entry.journal_voucher_id = voucher_journal.id', 'left');
/*---- applying conditions ------------*/
$this->db->where_not_in('voucher_entry.account_title_id', [110, 41, 32]);
$this->db->where_in('voucher_journal.tanker_id', $tanker_ids);
$this->db->where('voucher_journal.voucher_date >=', $this->from);
$this->db->where('voucher_journal.voucher_date <=', $this->to);
$this->db->where('voucher_journal.ignored', '0');
$this->db->where('voucher_journal.active', '1');
$this->db->group_by('voucher_journal.tanker_id');
$result = $this->db->get()->result();
foreach ($result as $record) {
$tanker_sockets[$record->vehicle_number] = round($record->repair_expense, 2);
}
$all_other_expenses = [];
foreach ($tanker_sockets as $key => $value) {
if ($value > 0) {
$all_other_expenses[$key] = $value;
}
}
return $all_other_expenses;
}
/**
* 6/62015
**/
public function account_difference()
{
$this->db->select('trips_details.id');
$this->db->join('trips', 'trips.id = trips_details.trip_id', 'left');
$this->db->where('trips.active', 1);
$this->db->where(array('entryDate >=' => '2015-01-01', 'entryDate <' => '2015-02-01'));
$result = $this->db->get('trips_details')->result();
$detail_ids = property_to_array('id', $result);
$final_trips = $this->trips_model->parametrized_trips_engine_by_detail_ids($detail_ids, "trips_welcome");
foreach ($final_trips as $trip) {
$detail = $trip->trip_related_details[0];
$total_freight = $detail->get_total_freight_for_company();
$net_freight = $total_freight - $detail->get_wht_amount($trip->company->wht);
$this->db->select('voucher_journal.id, voucher_entry.debit_amount as amount');
$this->db->join('voucher_entry', 'voucher_entry.journal_voucher_id = voucher_journal.id', 'left');
$this->db->from('voucher_journal');
$this->db->where('voucher_journal.active', 1);
$this->db->where(array('voucher_journal.transaction_column' => 'contractor_freight', 'voucher_journal.auto_generated' => 1, 'voucher_journal.trip_product_detail_id' => $detail->product_detail_id));
$this->db->where('voucher_entry.dr_cr', 1);
$result = $this->db->get()->result();
if (round($net_freight, 3) != round($result[0]->amount, 3)) {
echo round($net_freight, 3) . "____" . round($result[0]->amount, 3) . " trip: " . $trip->trip_id . "<br>";
}
}
}
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_id', $trip_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_id', $grouped_by_product_type['black oil']) : [];
$white_oil_trip_ids = isset($grouped_by_product_type['white oil']) ? property_to_array('trip_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_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_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 = round($detail->service_charges);
$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];
/******************************************************/
echo "customer_freight_amount____" . $total_customer_freight . "______" . $temp_voucher->voucher_id . "<br>";
echo "customer_freight_amount_without_shortage____" . $total_customer_freight_without_shortage . "<br>";
echo "total_shortage_amount____" . $total_shortage_amount . "<br>";
echo "customer_freight_amount____" . $total_customer_freight . "<br>";
echo "total_contractor_freight____" . $total_contractor_freight . "<br>";
echo "total_contractor_freight_without_shortage____" . $total_contractor_freight_without_shortage . "<br>";
echo "total_contractor_comission____" . $total_contractor_commission . "<br>";
echo "total_company_commission____" . $total_company_commission . "<br>";
echo "wht_amount____" . $total_company_commission . "<br>";
echo "graind_total_freight____" . $grand_total_freight . "<br>";
echo "<br><br><br>";
/*
* 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);
}
/********************************/
/********************************************/
}
/**********************************************************************/
}
var_dump($voucher_entries_update_array);
die;
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 searched_voucher_ids($agent, $agent_id, $keys)
{
if ($keys['voucher_id'] != '') {
$this->db->where('voucher_journal.id', $keys['voucher_id']);
} else {
if ($keys['custom_from'] != '' && $keys['custom_to']) {
$this->db->where('voucher_journal.voucher_date >=', $keys['custom_from']);
$this->db->where('voucher_journal.voucher_date <=', $keys['custom_to']);
} else {
/*
* ----------------------------------------------
* Below area is commented because we are hiding
* the accounting year functionality just for
* now.
* it might be activated again on some day.
* ----------------------------------------------
*/
//if($keys['accounting_year_from'] != ''){
//$this->db->where('voucher_journal.voucher_date >=', $keys['accounting_year_from']);
//}
//if($keys['accounting_year_to'] != ''){
//$this->db->where('voucher_journal.voucher_date <=', $keys['accounting_year_to']);
//}
/*--------------------------------------------------*/
}
}
if ($keys['voucher_type'] != '') {
if ($keys['voucher_type'] == 1) {
$this->db->where('voucher_journal.ignored', 1);
}
} else {
$this->db->where('voucher_journal.ignored', 0);
}
if ($keys['title'] != '') {
$this->db->where('voucher_entry.account_title_id', $keys['title']);
}
if ($keys['expense_type'] != '') {
$this->db->where('account_titles.secondary_type', $keys['expense_type']);
}
if ($keys['ac_type'] != '') {
$this->db->where('account_titles.type', $keys['ac_type']);
}
if ($keys['agent_type'] != '') {
if ($keys['agent_type'] == 'customers') {
$agent_type = 'related_customer';
}
if ($keys['agent_type'] == 'other_agents') {
$agent_type = 'related_other_agent';
}
if ($keys['agent_type'] == 'carriage_contractors') {
$agent_type = 'related_contractor';
}
if ($keys['agent_type'] == 'companies') {
$agent_type = 'related_company';
}
if ($keys['agent_id'] != '') {
$this->db->where('voucher_entry.' . $agent_type . '', $keys['agent_id']);
} else {
$this->db->where('voucher_entry.' . $agent_type . ' !=', 0);
}
}
if ($keys['tanker'] != '') {
$this->db->where('tankers.id', $keys['tanker']);
}
if ($keys['voucher_detail'] != '') {
$this->db->like('voucher_journal.detail', $keys['voucher_detail']);
}
if ($keys['summery'] != '') {
$this->db->like('voucher_entry.description', $keys['summery']);
}
if ($keys['trip_id'] != '') {
$this->db->where('voucher_journal.trip_id', $keys['trip_id']);
}
if ($keys['trip_detail_id'] != '') {
$this->db->where('voucher_journal.trip_product_detail_id', $keys['trip_detail_id']);
}
//checking weather user demands pagination or not
if (isset($keys['limit']) && isset($keys['start'])) {
$this->db->limit($keys['limit'], $keys['start']);
}
/////////////////////////////////////////////////////
//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.id', 'asc');
}
/////////////////////////////////////////////////////
$this->db->select("voucher_journal.id as voucher_id");
$this->db->distinct();
$this->db->from('voucher_journal');
$this->db->join('voucher_entry', 'voucher_entry.journal_voucher_id = voucher_journal.id', 'inner');
$this->db->join('other_agents', 'other_agents.id = voucher_entry.related_other_agent', 'left');
$this->db->join('customers', 'customers.id = voucher_entry.related_customer', 'left');
$this->db->join('carriage_contractors', 'carriage_contractors.id = voucher_entry.related_contractor', 'left');
$this->db->join('companies', 'companies.id = voucher_entry.related_company', '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.person_tid' => $agent . "." . $agent_id, 'voucher_journal.active' => 1));
$result = $this->db->get()->result();
$voucher_ids[0] = 0;
$voucher_ids = property_to_array('voucher_id', $result);
return $voucher_ids;
}
public function ignoreShortageVoucherByDestinationData()
{
$destinationShortageData = $this->getDestinationShortageData();
if (sizeof($destinationShortageData) == 0) {
return;
}
$trip_detail_ids = property_to_array('trip_detail_id', $destinationShortageData);
$this->db->select('voucher_journal.trip_product_detail_id');
$this->db->where_in('voucher_journal.trip_product_detail_id', $trip_detail_ids);
$this->db->where('voucher_journal.voucher_type', 'shortage_voucher_decnd');
$result = $this->db->get('voucher_journal')->result();
if (sizeof($result) == 0) {
return [];
}
$ignorable_trip_detail_ids = property_to_array('trip_product_detail_id', $result);
$where_statement = "(voucher_journal.trip_product_detail_id IN (" . join(', ', $ignorable_trip_detail_ids) . ") AND voucher_journal.voucher_type = 'shortage_voucher_dest')";
$this->ci->helper_model->ignore_voucher_where($where_statement);
}