<?php defined('BASEPATH') OR exit('No direct script access allowed');
	class Commonmdl extends CI_model{
		protected $master;
		protected $trans;
		public function __construct(){
			parent::__construct();

			$this->master = 'voucher_master';
			$this->trans  = 'voucher_trans';

			$this->config->load('extra');
		}
		public function get_accounts($type = ''){
			$subsql = !empty($type) ? " AND acc_type ='".$type."'" : "";
			$data = $this->db->query("SELECT acc_id, acc_name FROM account_master WHERE acc_grp_id = 9 AND acc_status = 1 $subsql ORDER BY acc_name ASC")->result_array();
			if(!empty($data)){
				foreach ($data as $key => $value) {
					$record[$value['acc_id']] = strtoupper($value['acc_name']);
				}
			}else{
				$record[0] = 'NO PAYMENT MODE ADDED';
			}
			return $record;
		}
		public function get_users(){
			$data = $this->db_operations->get_record('user_master', ['user_status' => 1, 'user_type !=' => 3]);

            if(!empty($data)){
                $record[0] = 'SELECT'; 
                foreach ($data as $key => $value) {
                    $record[$value['user_id']] = strtoupper($value['user_full_name']);     
                }
            }else{
                $record[0] = 'NO USER ADDED';   
            }

            return $record;
		}
		public function get_select2_account(){
			$subsql = '';
			if(isset($_GET['param']) && !empty($_GET['param'])){
				$type 	= $_GET['param'];
				if($type == 'SUPPLIER'){
					if(isset($_GET['name']) && !empty($_GET['name'])){
						$name 	= $_GET['name'];
						$subsql .= " AND (supplier.acc_contact LIKE '%".$name."%' OR supplier.acc_aumobile LIKE '%".$name."%') ";
					}
					$query 	="
							SELECT supplier.acc_id as id , 
							CONCAT(supplier.acc_contact, ' - ', supplier.acc_aumobile) as name
							FROM account_master supplier
							WHERE supplier.acc_grp_id = 6 AND supplier.acc_status1 = 1
							$subsql
							ORDER BY supplier.acc_contact ASC
							LIMIT 10
						";
				}else if($type == 'CUSTOMER'){
					if(isset($_GET['name']) && !empty($_GET['name'])){
						$name 	= $_GET['name'];
						$subsql .= " AND (customer.acc_contact LIKE '%".$name."%' OR customer.acc_aumobile LIKE '%".$name."%') ";
					}
					$query ="
							SELECT customer.acc_id as id , CONCAT(UPPER(customer.acc_contact), ' - ', UPPER(customer.acc_aumobile)) as name
							FROM account_master customer
							WHERE customer.acc_grp_id = 5 AND customer.acc_status1 = 1
							$subsql
							ORDER BY customer.acc_contact ASC
							LIMIT 10
						";
				}
				// echo "<pre>";print_r($query);exit();
				return $this->db->query($query)->result_array();
			}
			return [];
		}
		public function get_supplier_balance($group, $acc_id, $party_id){
			$open_amt 		= 0;
			$credited_amt 	= 0;
			$debited_amt 	= 0;
			$amt 			= 0;
			$type 			= '';
			$data  			= $this->db_operations->get_record('account_master', ['acc_id' => $party_id]);
			if(!empty($data)){
				$open_amt 			= $data[0]['acc_open_bal'];
				$credited_amt 		= $this->get_credited_amt($group, $acc_id, $party_id);
				$debited_amt 		= $this->get_debited_amt($group, $acc_id, $party_id);
				// if($data[0]['acc_cr_dr'] == 'CR'){
				// 	$closing_bal= ($open_amt + $debited_amt) - $credited_amt;
				// 	$amt 	 	= $closing_bal;
				// 	$type 		= 'TO PAY';
				// 	if($amt < 0){
				// 		$amt = abs($amt);
				// 		$type= 'TO RECEIVE';
				// 	}
				// }else{
				// 	$closing_bal= ($open_amt + $credited_amt) - $debited_amt;
				// 	$amt 	 	= $closing_bal;
				// 	$type 		= 'TO RECEIVE';
				// 	if($amt < 0){
				// 		$amt = abs($amt);
				// 		$type= 'TO PAY';
				// 	}
				// }
			}

			return [
						'open_amt' 		=> round($open_amt,2), 
						'credited_amt' 	=> round($credited_amt,2), 
						'debited_amt' 	=> round($debited_amt,2), 
						'amt' 			=> round($amt,2), 
						'type' 			=> $type
					];
		}
		public function get_customer_balance($group, $acc_id, $party_id){
			$open_amt 		= 0;
			$credited_amt 	= 0;
			$debited_amt 	= 0;
			$amt 			= 0;
			$type 			= '';
			$data  			= $this->db_operations->get_record('account_master', ['acc_id' => $party_id]);
				// echo "<pre>";print_r($data);exit();

			if(!empty($data)){
				$open_amt 			= $data[0]['acc_open_bal'];
				$credited_amt 		= $this->get_credited_amt($group, $acc_id, $party_id);
				$debited_amt 		= $this->get_debited_amt($group, $acc_id, $party_id);
				$credited_amt 		= $credited_amt;
				$debited_amt 		= $debited_amt;
				// if($data[0]['acc_drcr'] == 'CR'){
				// 	$closing_bal= ($open_amt + $debited_amt) - $credited_amt;
				// 	$amt 	 	= $closing_bal;
				// 	$type 		= 'TO PAY';
				// 	if($amt < 0){
				// 		$amt = abs($amt);
				// 		$type= 'TO RECEIVE';
				// 	}
				// }else{
				// 	$closing_bal= ($open_amt + $credited_amt) - $debited_amt;
				// 	$amt 	 	= $closing_bal;
				// 	$type 		= 'TO RECEIVE';
				// 	if($amt < 0){
				// 		$amt = abs($amt);
				// 		$type= 'TO PAY';
				// 	}
				// }
			}
			return [
						'open_amt' 		=> round($open_amt,2), 
						'credited_amt' 	=> round($credited_amt,2), 
						'debited_amt' 	=> round($debited_amt,2), 
						'amt' 			=> round($amt,2), 
						'type' 			=> $type
					];
		}
		public function get_credited_amt($group, $acc_id, $party_id){
			$amt = 0;
            $query="
                    SELECT SUM(sale_total_amt) as amt
                    FROM sales_master
                    WHERE sale_customer_id = $party_id
                    GROUP BY sale_customer_id

                ";
            $data = $group == 'CUSTOMER' ? $this->db->query($query)->result_array() : [];
            if(!empty($data)) $amt = $amt + $data[0]['amt'];

            $query="
                    SELECT SUM(per_total_amt) as amt
                    FROM purchase_master
                    WHERE per_supplier_id = $party_id
                    GROUP BY per_supplier_id

                ";
            $data = $group == 'SUPPLIER' ? $this->db->query($query)->result_array() : [];

            if(!empty($data)) $amt = $amt + $data[0]['amt'];

			$query="
						SELECT SUM(vm_total_amt) as amt
						FROM voucher_master
						WHERE vm_type = 'PAYMENT'
						AND vm_group = '".$group."' 
						AND vm_party_id = $party_id
						AND vm_acc_id = $acc_id
						GROUP BY vm_party_id

					";
			$data = $this->db->query($query)->result_array();
			// if(!empty($data)) $amt = $amt + $data[0]['amt'];

				// echo "<pre>";print_r($amt);exit();

			return $amt;
		}
		public function get_debited_amt($group, $acc_id, $party_id){
			$amt = 0;
            $query="
                    SELECT SUM(sale_allocated_amt + sale_round_off) as amt
                    FROM sales_master
                    WHERE sale_customer_id = $party_id
                    GROUP BY sale_customer_id

                ";
            $data = $group == 'CUSTOMER' ? $this->db->query($query)->result_array() : [];
            if(!empty($data)) $amt = $amt + $data[0]['amt'];

            $query="
                    SELECT SUM(per_allocated_amt + per_round_off) as amt
                    FROM purchase_master
                    WHERE per_supplier_id = $party_id
                    GROUP BY per_supplier_id

                ";
            $data = $group == 'SUPPLIER' ? $this->db->query($query)->result_array() : [];
            if(!empty($data)) $amt = $amt + $data[0]['amt'];

			$query="
						SELECT SUM(vm_total_amt) as amt
						FROM voucher_master
						WHERE vm_type = 'RECEIPT'
						AND vm_group = '".$group."' 
						AND vm_party_id = $party_id
						AND vm_acc_id = $acc_id
						GROUP BY vm_party_id

					";
            $data = $group != 'CUSTOMER' ? $this->db->query($query)->result_array() : [];
			if(!empty($data)) $amt = $amt + $data[0]['amt'];
			return $amt;
		}
	}
?>