File: /var/www/html/taxicamera/pmw_live_testing/old/application/models/admin/Mdailysettlement.php
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Mdailysettlement extends CI_Model {
public function __construct() {
parent::__construct();
}
public function totalDockets($table,$condition){
$result = array();
$total_docket = 0.00;
$this->db->select('sum(value) as total_docket');
$this->db->from($table);
$this->db->where($condition);
$query = $this->db->get();
$result = $query->row_array();
if(!empty($result) && !empty($result['total_docket'])){
$total_docket = $result['total_docket'];
}
return $total_docket;
}
public function totalExpense($table,$condition){
$result = array();
$total_expense = 0.00;
$this->db->select('sum(value) as total_expense');
$this->db->from($table);
$this->db->where($condition);
$query = $this->db->get();
$result = $query->row_array();
if(!empty($result) && !empty($result['total_expense'])){
$total_expense = $result['total_expense'];
}
return $total_expense;
}
public function AccidentInstallment($cond_accident_installment){
$result = array();
$total_accident_installment = 0.00;
$this->db->select('sum(shift_amt) as total_accident_installment');
$this->db->from('master_accident');
$this->db->join('accident_charge', 'master_accident.accident_id = accident_charge.accident_id', 'left');
$this->db->where($cond_accident_installment);
$query = $this->db->get();
//echo $this->db->last_query(); die();
$result = $query->row_array();
if(!empty($result) && !empty($result['total_accident_installment'])){
$total_accident_installment = $result['total_accident_installment'];
}
return $total_accident_installment;
}
public function GetAccidentData($cond_accident_installment){
$result = array();
$total_accident_installment = 0.00;
$this->db->select('master_accident.*,accident_charge.*');
$this->db->from('master_accident');
$this->db->join('accident_charge', 'master_accident.accident_id = accident_charge.accident_id', 'left');
$this->db->where($cond_accident_installment);
$query = $this->db->get();
//echo $this->db->last_query(); die();
$result = $query->result_array();
return $result ;
}
public function getCommission($commission_cond){
$result = array();
$driver_share = 0.00;
$this->db->select('master_commission.driver_share as driver_share');
$this->db->from('user_profile');
$this->db->join('master_commission', 'user_profile.dr_commission_id = master_commission.commission_id', 'left');
$this->db->where($commission_cond);
$query = $this->db->get();
//echo $this->db->last_query(); die();
$result = $query->row_array();
//pr($result);
if(!empty($result) && !empty($result['owner_share'])){
$driver_share = $result['owner_share'];
}
return $driver_share;
}
public function get_transaction_data_cash_in($transaction_date,$transaction_type){
$transaction_previous_date=date('Y-m-d', strtotime('-1 day', strtotime($transaction_date)));
$query="SELECT
SUM(1c_coins_qty) as 1c_coins_qty,
SUM(1c_coins_amt) as 1c_coins_amt,
SUM(5c_coins_qty) as 5c_coins_qty,
SUM(5c_coins_amt) as 5c_coins_amt,
SUM(10c_coins_qty) as 10c_coins_qty,
SUM(10c_coins_amt) as 10c_coins_amt,
SUM(20c_coins_qty) as 20c_coins_qty,
SUM(20c_coins_amt) as 20c_coins_amt,
SUM(50c_coins_qty) as 50c_coins_qty,
SUM(50c_coins_amt) as 50c_coins_amt,
SUM(1d_coins_qty) as 1d_coins_qty,
SUM(1d_coins_amt) as 1d_coins_amt,
SUM(2d_coins_qty) as 2d_coins_qty,
SUM(2d_coins_amt) as 2d_coins_amt,
SUM(5d_notes_qty) as 5d_notes_qty,
SUM(5d_notes_amt) as 5d_notes_amt,
SUM(10d_notes_qty) as 10d_notes_qty,
SUM(10d_notes_amt) as 10d_notes_amt,
SUM(20d_notes_qty) as 20d_notes_qty,
SUM(20d_notes_amt) as 20d_notes_amt,
SUM(50d_notes_qty) as 50d_notes_qty,
SUM(50d_notes_amt) as 50d_notes_amt,
SUM(100d_notes_qty)as 100d_notes_qty,
SUM(100d_notes_amt)as 100d_notes_amt,
SUM(total_amount) as total_amount FROM (
(SELECT
ifnull(SUM(dt.1c_coins_qty),0) as 1c_coins_qty,
ifnull(SUM(dt.1c_coins_amt),0) as 1c_coins_amt,
ifnull(SUM(dt.5c_coins_qty),0) as 5c_coins_qty,
ifnull(SUM(dt.5c_coins_amt),0) as 5c_coins_amt,
ifnull(SUM(dt.10c_coins_qty),0) as 10c_coins_qty,
ifnull(SUM(dt.10c_coins_amt),0) as 10c_coins_amt,
ifnull(SUM(dt.20c_coins_qty),0) as 20c_coins_qty,
ifnull(SUM(dt.20c_coins_amt),0) as 20c_coins_amt,
ifnull(SUM(dt.50c_coins_qty),0) as 50c_coins_qty,
ifnull(SUM(dt.50c_coins_amt),0) as 50c_coins_amt,
ifnull(SUM(dt.1d_coins_qty),0) as 1d_coins_qty,
ifnull(SUM(dt.1d_coins_amt),0) as 1d_coins_amt,
ifnull(SUM(dt.2d_coins_qty),0) as 2d_coins_qty,
ifnull(SUM(dt.2d_coins_amt),0) as 2d_coins_amt,
ifnull(SUM(dt.5d_notes_qty),0) as 5d_notes_qty,
ifnull(SUM(dt.5d_notes_amt),0) as 5d_notes_amt,
ifnull(SUM(dt.10d_notes_qty),0) as 10d_notes_qty,
ifnull(SUM(dt.10d_notes_amt),0) as 10d_notes_amt,
ifnull(SUM(dt.20d_notes_qty),0) as 20d_notes_qty,
ifnull(SUM(dt.20d_notes_amt),0) as 20d_notes_amt,
ifnull(SUM(dt.50d_notes_qty),0) as 50d_notes_qty,
ifnull(SUM(dt.50d_notes_amt),0) as 50d_notes_amt,
ifnull(SUM(dt.100d_notes_qty),0)as 100d_notes_qty,
ifnull(SUM(dt.100d_notes_amt),0)as 100d_notes_amt,
ifnull(SUM(dt.total_amount),0) as total_amount
FROM daily_transactions dt
WHERE dt.transaction_type ='".$transaction_type."' AND dt.transaction_date = '".$transaction_date."')
UNION ALL
(SELECT
ifnull(SUM(dts.1c_coins_qty),0) as 1c_coins_qty,
ifnull(SUM(dts.1c_coins_amt),0) as 1c_coins_amt,
ifnull(SUM(dts.5c_coins_qty),0) as 5c_coins_qty,
ifnull(SUM(dts.5c_coins_amt),0) as 5c_coins_amt,
ifnull(SUM(dts.10c_coins_qty),0) as 10c_coins_qty,
ifnull(SUM(dts.10c_coins_amt),0) as 10c_coins_amt,
ifnull(SUM(dts.20c_coins_qty),0) as 20c_coins_qty,
ifnull(SUM(dts.20c_coins_amt),0) as 20c_coins_amt,
ifnull(SUM(dts.50c_coins_qty),0) as 50c_coins_qty,
ifnull(SUM(dts.50c_coins_amt),0) as 50c_coins_amt,
ifnull(SUM(dts.1d_coins_qty),0) as 1d_coins_qty,
ifnull(SUM(dts.1d_coins_amt),0) as 1d_coins_amt,
ifnull(SUM(dts.2d_coins_qty),0) as 2d_coins_qty,
ifnull(SUM(dts.2d_coins_amt),0) as 2d_coins_amt,
ifnull(SUM(dts.5d_notes_qty),0) as 5d_notes_qty,
ifnull(SUM(dts.5d_notes_amt),0) as 5d_notes_amt,
ifnull(SUM(dts.10d_notes_qty),0) as 10d_notes_qty,
ifnull(SUM(dts.10d_notes_amt),0) as 10d_notes_amt,
ifnull(SUM(dts.20d_notes_qty),0) as 20d_notes_qty,
ifnull(SUM(dts.20d_notes_amt),0) as 20d_notes_amt,
ifnull(SUM(dts.50d_notes_qty),0) as 50d_notes_qty,
ifnull(SUM(dts.50d_notes_amt),0) as 50d_notes_amt,
ifnull(SUM(dts.100d_notes_qty),0)as 100d_notes_qty,
ifnull(SUM(dts.100d_notes_amt),0)as 100d_notes_amt,
0
FROM daily_transactions_settle dts
WHERE dts.transaction_date < '".$transaction_date."' ORDER BY dts.transaction_date DESC LIMIT 1)) t1";
$run_query = $this->db->query($query);
//echo $this->db->last_query(); die();
$result = $run_query->row_array();
return $result ;
}
public function get_transaction_data_cash_out($transaction_date,$transaction_type){
$query="SELECT GROUP_CONCAT(transaction_id) as transaction_id, GROUP_CONCAT(payin_id) as payin_id, GROUP_CONCAT(customer_id) as customer_id,
SUM(1c_coins_qty) as 1c_coins_qty, SUM(1c_coins_amt) as 1c_coins_amt,
SUM(5c_coins_qty) as 5c_coins_qty, SUM(5c_coins_amt) as 5c_coins_amt, SUM(10c_coins_qty) as 10c_coins_qty, SUM(10c_coins_amt) as 10c_coins_amt, SUM(20c_coins_qty) as 20c_coins_qty, SUM(20c_coins_amt) as 20c_coins_amt,
SUM(50c_coins_qty) as 50c_coins_qty,
SUM(50c_coins_amt) as 50c_coins_amt,
SUM(1d_coins_qty) as 1d_coins_qty,
SUM(1d_coins_amt) as 1d_coins_amt,
SUM(2d_coins_qty) as 2d_coins_qty,
SUM(2d_coins_amt) as 2d_coins_amt,
SUM(5d_notes_qty) as 5d_notes_qty,
SUM(5d_notes_amt) as 5d_notes_amt,
SUM(10d_notes_qty) as 10d_notes_qty,
SUM(10d_notes_amt) as 10d_notes_amt,
SUM(20d_notes_qty) as 20d_notes_qty,
SUM(20d_notes_amt) as 20d_notes_amt,
SUM(50d_notes_qty) as 50d_notes_qty,
SUM(50d_notes_amt) as 50d_notes_amt,
SUM(100d_notes_qty) as 100d_notes_qty,
SUM(100d_notes_amt) as 100d_notes_amt,
SUM(total_amount) as total_amount
FROM daily_transactions dt
WHERE transaction_type ='".$transaction_type."' AND transaction_date = '".$transaction_date."' ";
$run_query = $this->db->query($query);
//echo $this->db->last_query(); die();
$result = $run_query->row_array();
return $result ;
}
public function get_transaction_data_cash_in_out_details($transaction_date,$transaction_type){
$query="SELECT dt.*,
CASE WHEN '".$transaction_type."' = 1 THEN
CASE WHEN dt.payin_id <> 0 THEN CONCAT('Payment Received from driver(',mu.dc_no,')') ELSE CONCAT('Cash from ' ,mc.first_name) END
ELSE
CASE WHEN dt.payin_id <> 0 THEN CONCAT('Payment To Driver (',mu.dc_no,')') ELSE CONCAT ('Cash for ' ,mc.first_name) END
END
as transaction_desc
FROM daily_transactions dt
LEFT JOIN master_customer mc ON mc.customer_id = dt.customer_id
LEFT JOIN payin p ON p.payin_id=dt.payin_id
LEFT JOIN master_user mu ON mu.user_id=p.driver_id
WHERE dt.transaction_type ='".$transaction_type."' AND dt.transaction_date = '".$transaction_date."'";
$run_query = $this->db->query($query);
//echo $this->db->last_query(); die();
$result = $run_query->result_array();
return $result ;
}
public function get_docket_header($transaction_date){
$query="SELECT md.docket_name,pd.docket_id,SUM(sd.docket_settlement_amt) as total_amount
FROM driver_settlement ds
INNER JOIN dr_settlement_docket sd ON sd.dr_settlement_id = ds.dr_settlement_id
INNER JOIN payin_dockets pd ON pd.payin_dockets_id=sd.payin_docket_id
INNER JOIN master_docket md ON md.docket_id=pd.docket_id
INNER JOIN master_user mu ON mu.user_id=ds.driver_id
INNER JOIN user_profile up ON up.user_id=ds.driver_id
INNER JOIN driver_shift_details dsd ON dsd.shift_id=ds.shift_id
WHERE ds.created_on = '".$transaction_date."' GROUP BY pd.docket_id ORDER BY pd.docket_id";
$run_query = $this->db->query($query);
//echo $this->db->last_query(); die();
$result = $run_query->result_array();
return $result ;
}
public function get_docket_details($transaction_date){
$query="SELECT pd.docket_id,mu.dc_no,up.first_name,dsd.shift_no,md.docket_name,sd.docket_settlement_amt
FROM driver_settlement ds
INNER JOIN dr_settlement_docket sd ON sd.dr_settlement_id = ds.dr_settlement_id
INNER JOIN payin_dockets pd ON pd.payin_dockets_id=sd.payin_docket_id
INNER JOIN master_docket md ON md.docket_id=pd.docket_id
INNER JOIN master_user mu ON mu.user_id=ds.driver_id
INNER JOIN user_profile up ON up.user_id=ds.driver_id
INNER JOIN driver_shift_details dsd ON dsd.shift_id=ds.shift_id
WHERE ds.created_on = '".$transaction_date."' ORDER BY pd.docket_id";
$run_query = $this->db->query($query);
//echo $this->db->last_query(); die();
$result = $run_query->result_array();
return $result ;
}
public function daily_settle_submit($remarks,$settlement_date,$opening_balance=array()){
$opening_balance=$this->mcommon->getRow('daily_transactions_settle',array("transaction_date < STR_TO_DATE('".$settlement_date."','%d/%m/%Y') ORDER BY transaction_date DESC LIMIT 1"=>null));
//echo '<pre>';print_r($opening_balance);die;
$daily_settlement_data['1c_coins_qty'] =isset($opening_balance['1c_coins_qty'])?$opening_balance['1c_coins_qty']:0;
$daily_settlement_data['1c_coins_amt'] =isset($opening_balance['1c_coins_amt'])?$opening_balance['1c_coins_amt']:0;
$daily_settlement_data['5c_coins_qty'] =isset($opening_balance['5c_coins_qty'])?$opening_balance['5c_coins_qty']:0;
$daily_settlement_data['5c_coins_amt'] =isset($opening_balance['5c_coins_amt'])?$opening_balance['5c_coins_amt']:0;
$daily_settlement_data['10c_coins_qty'] =isset($opening_balance['10c_coins_qty'])?$opening_balance['10c_coins_qty']:0;
$daily_settlement_data['10c_coins_amt'] =isset($opening_balance['10c_coins_amt'])?$opening_balance['10c_coins_amt']:0;
$daily_settlement_data['20c_coins_qty'] =isset($opening_balance['20c_coins_qty'])?$opening_balance['20c_coins_qty']:0;
$daily_settlement_data['20c_coins_amt'] =isset($opening_balance['20c_coins_amt'])?$opening_balance['20c_coins_amt']:0;
$daily_settlement_data['50c_coins_qty'] =isset($opening_balance['50c_coins_qty'])?$opening_balance['50c_coins_qty']:0;
$daily_settlement_data['50c_coins_amt'] =isset($opening_balance['50c_coins_amt'])?$opening_balance['50c_coins_amt']:0;
$daily_settlement_data['1d_coins_qty'] =isset($opening_balance['1d_coins_qty'])?$opening_balance['1d_coins_qty']:0;
$daily_settlement_data['1d_coins_amt'] =isset($opening_balance['1d_coins_amt'])?$opening_balance['1d_coins_amt']:0;
$daily_settlement_data['2d_coins_qty'] =isset($opening_balance['2d_coins_qty'])?$opening_balance['2d_coins_qty']:0;
$daily_settlement_data['2d_coins_amt'] =isset($opening_balance['2d_coins_amt'])?$opening_balance['2d_coins_amt']:0;
$daily_settlement_data['5d_notes_qty'] =isset($opening_balance['5d_notes_qty'])?$opening_balance['5d_notes_qty']:0;
$daily_settlement_data['5d_notes_amt'] =isset($opening_balance['5d_notes_amt'])?$opening_balance['5d_notes_amt']:0;
$daily_settlement_data['10d_notes_qty'] =isset($opening_balance['10d_notes_qty'])?$opening_balance['10d_notes_qty']:0;
$daily_settlement_data['10d_notes_amt'] =isset($opening_balance['10d_notes_amt'])?$opening_balance['10d_notes_amt']:0;
$daily_settlement_data['20d_notes_qty'] =isset($opening_balance['20d_notes_qty'])?$opening_balance['20d_notes_qty']:0;
$daily_settlement_data['20d_notes_amt'] =isset($opening_balance['20d_notes_amt'])?$opening_balance['20d_notes_amt']:0;
$daily_settlement_data['50d_notes_qty'] =isset($opening_balance['50d_notes_qty'])?$opening_balance['50d_notes_qty']:0;
$daily_settlement_data['50d_notes_amt'] =isset($opening_balance['50d_notes_amt'])?$opening_balance['50d_notes_amt']:0;
$daily_settlement_data['100d_notes_qty'] =isset($opening_balance['100d_notes_qty'])?$opening_balance['100d_notes_qty']:0;
$daily_settlement_data['100d_notes_amt'] =isset($opening_balance['100d_notes_amt'])?$opening_balance['100d_notes_amt']:0;
$daily_settlement_data['total_amount'] =isset($opening_balance['total_amount'])?$opening_balance['total_amount']:0;
$query="INSERT INTO daily_transactions_settle(transaction_date, 1c_coins_qty, 1c_coins_amt,5c_coins_qty, 5c_coins_amt, 10c_coins_qty, 10c_coins_amt, 20c_coins_qty, 20c_coins_amt, 50c_coins_qty, 50c_coins_amt, 1d_coins_qty, 1d_coins_amt, 2d_coins_qty, 2d_coins_amt, 5d_notes_qty, 5d_notes_amt, 10d_notes_qty, 10d_notes_amt, 20d_notes_qty, 20d_notes_amt,50d_notes_qty,50d_notes_amt, 100d_notes_qty, 100d_notes_amt, total_amount, created_by, created_ts, remarks)
SELECT
STR_TO_DATE('".$settlement_date."','%d/%m/%Y'),
(SUM(IF(`transaction_type`=1, `1c_coins_qty`, 0))-SUM(IF(`transaction_type`=2, `1c_coins_qty`, 0)) + '".$daily_settlement_data['1c_coins_qty']."'),
(SUM(IF(`transaction_type`=1, `1c_coins_amt`, 0))-SUM(IF(`transaction_type`=2, `1c_coins_amt`, 0)) + '".$daily_settlement_data['1c_coins_amt']."'),
(SUM(IF(`transaction_type`=1, `5c_coins_qty`, 0))-SUM(IF(`transaction_type`=2, `5c_coins_qty`, 0)) + '".$daily_settlement_data['5c_coins_qty']."'),
(SUM(IF(`transaction_type`=1, `5c_coins_amt`, 0))-SUM(IF(`transaction_type`=2, `5c_coins_amt`, 0)) + '".$daily_settlement_data['5c_coins_amt']."'),
(SUM(IF(`transaction_type`=1, `10c_coins_qty`, 0))-SUM(IF(`transaction_type`=2, `10c_coins_qty`, 0))+ '".$daily_settlement_data['10c_coins_qty']."'),
(SUM(IF(`transaction_type`=1, `10c_coins_amt`, 0))-SUM(IF(`transaction_type`=2, `10c_coins_amt`, 0))+ '".$daily_settlement_data['10c_coins_amt']."'),
(SUM(IF(`transaction_type`=1, `20c_coins_qty`, 0))-SUM(IF(`transaction_type`=2, `20c_coins_qty`, 0))+ '".$daily_settlement_data['20c_coins_qty']."'),
(SUM(IF(`transaction_type`=1, `20c_coins_amt`, 0))-SUM(IF(`transaction_type`=2, `20c_coins_amt`, 0))+ '".$daily_settlement_data['20c_coins_amt']."'),
(SUM(IF(`transaction_type`=1, `50c_coins_qty`, 0))-SUM(IF(`transaction_type`=2, `50c_coins_qty`, 0))+ '".$daily_settlement_data['50c_coins_qty']."'),
(SUM(IF(`transaction_type`=1, `50c_coins_amt`, 0))-SUM(IF(`transaction_type`=2, `50c_coins_amt`, 0))+ '".$daily_settlement_data['50c_coins_amt']."'),
(SUM(IF(`transaction_type`=1, `1d_coins_qty`, 0))-SUM(IF(`transaction_type`=2, `1d_coins_qty`, 0))+ '".$daily_settlement_data['1d_coins_qty']."'),
(SUM(IF(`transaction_type`=1, `1d_coins_amt`, 0))-SUM(IF(`transaction_type`=2, `1d_coins_amt`, 0))+ '".$daily_settlement_data['1d_coins_amt']."'),
(SUM(IF(`transaction_type`=1, `2d_coins_qty`, 0))-SUM(IF(`transaction_type`=2, `2d_coins_qty`, 0))+ '".$daily_settlement_data['2d_coins_qty']."'),
(SUM(IF(`transaction_type`=1, `2d_coins_amt`, 0))-SUM(IF(`transaction_type`=2, `2d_coins_amt`, 0))+ '".$daily_settlement_data['2d_coins_amt']."'),
(SUM(IF(`transaction_type`=1, `5d_notes_qty`, 0))-SUM(IF(`transaction_type`=2, `5d_notes_qty`, 0))+ '".$daily_settlement_data['5d_notes_qty']."'),
(SUM(IF(`transaction_type`=1, `5d_notes_amt`, 0))-SUM(IF(`transaction_type`=2, `5d_notes_amt`, 0))+ '".$daily_settlement_data['5d_notes_amt']."'),
(SUM(IF(`transaction_type`=1, `10d_notes_qty`, 0))-SUM(IF(`transaction_type`=2, `10d_notes_qty`, 0))+ '".$daily_settlement_data['10d_notes_qty']."'),
(SUM(IF(`transaction_type`=1, `10d_notes_amt`, 0))-SUM(IF(`transaction_type`=2, `10d_notes_amt`, 0))+ '".$daily_settlement_data['10d_notes_amt']."'),
(SUM(IF(`transaction_type`=1, `20d_notes_qty`, 0))-SUM(IF(`transaction_type`=2, `20d_notes_qty`, 0))+ '".$daily_settlement_data['20d_notes_qty']."'),
(SUM(IF(`transaction_type`=1, `20d_notes_amt`, 0))-SUM(IF(`transaction_type`=2, `20d_notes_amt`, 0))+ '".$daily_settlement_data['20d_notes_amt']."'),
(SUM(IF(`transaction_type`=1, `50d_notes_qty`, 0))-SUM(IF(`transaction_type`=2, `50d_notes_qty`, 0))+ '".$daily_settlement_data['50d_notes_qty']."'),
(SUM(IF(`transaction_type`=1, `50d_notes_amt`, 0))-SUM(IF(`transaction_type`=2, `50d_notes_amt`, 0))+ '".$daily_settlement_data['50d_notes_amt']."'),
(SUM(IF(`transaction_type`=1, `100d_notes_qty`, 0))-SUM(IF(`transaction_type`=2, `100d_notes_qty`, 0))+ '".$daily_settlement_data['100d_notes_qty']."'),(SUM(IF(`transaction_type`=1, `100d_notes_amt`, 0))-SUM(IF(`transaction_type`=2, `100d_notes_amt`, 0))+ '".$daily_settlement_data['100d_notes_amt']."'),(SUM(IF(`transaction_type`=1, `total_amount`, 0))-SUM(IF(`transaction_type`=2, `total_amount`, 0))+ '".$daily_settlement_data['total_amount']."'),
'".$this->session->userdata('user_data')."',
'".date('Y-m-d H:i:s')."',
'".$remarks."'
FROM daily_transactions
WHERE transaction_date = STR_TO_DATE('".$settlement_date."','%d/%m/%Y')";
$run_query = $this->db->query($query);
$transaction_settle_id =$this->db->insert_id();
return $transaction_settle_id;
}
public function check_previous_day_status($settlement_date){
$query ="SELECT COUNT(*) as cnt FROM daily_transactions_settle WHERE transaction_date >= STR_TO_DATE('".$settlement_date."','%d/%m/%Y')";
$run_query = $this->db->query($query);
$result = $run_query -> row_array();
return $result;
}
}