<?php
defined('BASEPATH') OR exit('No direct script access allowed');
	class Vouchermdl extends CI_model{

		public function get_cnt($table, $arr){

			$this->db->where($arr);
			return $this->db->count_all_results($table);
		}

		public function get_fin_year_wise_max_id($table,$col_name,$col_fin_year,$sess_fin_year)
	    {   
	        $record = $this->db->query("SELECT MAX($col_name) as max_no FROM $table WHERE $col_fin_year = '$sess_fin_year'")->result_array();

	        if(empty($record[0]['max_no']))
	        {
	            return 1;
	        }
	        else
	        {
	            return $record[0]['max_no']+1;
	        }
	    }
        
 		public function party_data(){
					$subsql = "";
					$limit  = 20;
			    $offset = 0;
			    $page   = 1;
			    if(isset($_GET['limit']) && !empty($_GET['limit'])){
			        $limit = $_GET['limit'];
			    }
			    if(isset($_GET['page']) && !empty($_GET['page'])){
			        $page   = $_GET['page'];
			        $offset = $limit * ($page - 1);
			    }
				if(isset($_GET['name']) && !empty($_GET['name'])){
					$name 	= $_GET['name'];
					$subsql .= " AND (acc_name LIKE '%".$name."%' OR acc_contact LIKE '%".$name."%') ";
				}
				if(isset($_GET['param']) && !empty($_GET['param'])){
					$param 	= $_GET['param'];
					$subsql .= " AND (acc_status1 = 1) ";
				}
				if(isset($_GET['param1']) && !empty($_GET['param1']))
				{
					if($_GET['param1'] == "PAYMENT")
					{
						return $this->db->query("SELECT acc_id as id, CONCAT(acc_contact,' - ',acc_aumobile) as name
						FROM account_master WHERE acc_grp_id = 6 $subsql GROUP BY acc_aumobile,acc_contact ASC LIMIT $limit OFFSET $offset")->result_array();
					}
					else if($_GET['param1'] == "RECEIPT")
					{
						return $this->db->query("SELECT acc_id as id, CONCAT(acc_name,' - ',acc_contact) as name
						FROM account_master WHERE acc_grp_id = 5 $subsql GROUP BY acc_name,acc_contact ASC LIMIT $limit OFFSET $offset")->result_array();
					}
				}
				
}
        
        public function order_data()
		{
			$subsql = "";
			$limit  = 20;
		    $offset = 0;
		    $page   = 1;
		    if(isset($_GET['limit']) && !empty($_GET['limit'])){
		        $limit = $_GET['limit'];
		    }
		    if(isset($_GET['page']) && !empty($_GET['page'])){
		        $page   = $_GET['page'];
		        $offset = $limit * ($page - 1);
		    }
			if(isset($_GET['name']) && !empty($_GET['name'])){
				$name 	= $_GET['name'];
				$subsql .= " AND (sale_entry_no LIKE '%".$name."%') ";
			}
			// if(isset($_GET['param2']) && !empty($_GET['param2'])){
			// 	$param2 	= $_GET['param2'];
			// 	$subsql .= " AND (sale_approve = '$param2') ";
			// }
			if(isset($_GET['param1']) && !empty($_GET['param1']))
			{
				if($_GET['param1'] == "RECEIPT")
				{
					return $this->db->query("SELECT sale_id as id, sale_entry_no as name
					FROM sales_master WHERE 1	$subsql	GROUP BY sale_entry_no ASC LIMIT $limit OFFSET $offset")->result_array();
				}
			}	
		}

	    /*voucher master*/

    /************************** voucher master ******************************************/
	    public function get_voucher_data_general_count($fin_year)
	    {
	    	// $branch_id = $_SESSION['user_branch_id'];
	    	$subsql = '';
	    	$role = $_SESSION['user_role_id'];
			
			if(isset($_GET['search_field']) && isset($_GET['search_value']))
			{
				$field    = $_GET['search_field'];
	            $keyword  = $_GET['search_value'];

				// $start_date = date('Y-m-d',strtotime($_GET['start_date']));
	   			//          $end_date   = date('Y-m-d',strtotime($_GET['end_date']));

				// this code for check the value of payment type
				if($_GET['search_field'] == "vc_voucher_type" && $_GET['search_value'] == "PAYMENT") 
				{
					$_GET['search_value'] = 1;
				}

				if($_GET['search_field'] == "vc_voucher_type" && $_GET['search_value'] == "RECEIPT")
				{
					$_GET['search_value'] = 2;
				}

				// this code for check the value of entry date

				if($_GET['search_field'] == "vc_entry_date") 
				{
					$_GET['search_value'] = date('Y-m-d',strtotime($_GET['search_value']));
				}

				if (!empty($field) && !empty($keyword)) 
				{
					$subsql .= " AND ".$_GET['search_field']." like '%".$_GET['search_value']."%'"; 
				}
				

				// this code for unset the value of payment type
				if($_GET['search_field'] == "vc_voucher_type" && $_GET['search_value'] == "1")  
				{
					$_GET['search_value'] = "PAYMENT";
				}

				if($_GET['search_field'] == "vc_voucher_type" && $_GET['search_value'] == "2")
				{
					$_GET['search_value'] = "RECEIPT";
				}

				// this code for unset the value of entry date

				if($_GET['search_field'] == "vc_entry_date") 
				{
					$_GET['search_value'] = date('d-m-Y',strtotime($_GET['search_value']));
				}
			}
			

			$query = "SELECT vm.*,acc.acc_name FROM voucher_master vm LEFT JOIN account_master acc ON(vm.vc_party_id = acc.acc_id)
				WHERE vm.vc_grp_id NOT IN(6,5,23,7) AND vm.vc_fin_year = '$fin_year' $subsql";

			return $this->db->query($query)->num_rows();
	    }

	   	public function get_all_voucher_data_general($fin_year,$per_page, $offset)
		{
			$record = array();
			// $branch_id = $_SESSION['user_branch_id'];
			$role = $_SESSION['user_role_id'];

			$subsql = '';
			
			if(isset($_GET['search_field']) && isset($_GET['search_value']))
			{	
				$field    = $_GET['search_field'];
	            $keyword  = $_GET['search_value'];
	
				// this code for check the value of payment type
				if($_GET['search_field'] == "vc_voucher_type" && $_GET['search_value'] == "PAYMENT") 
				{
					$_GET['search_value'] = 1;
				}

				if($_GET['search_field'] == "vc_voucher_type" && $_GET['search_value'] == "RECEIPT")
				{
					$_GET['search_value'] = 2;
				}

				// this code for check the value of entry date

				if($_GET['search_field'] == "vc_entry_date") 
				{
					$_GET['search_value'] = date('Y-m-d',strtotime($_GET['search_value']));
				}

				if(!empty($field) && !empty($keyword))
	            {
					$subsql .= " AND ".$_GET['search_field']." like '%".$_GET['search_value']."%'"; 
				}
				// this code for unset the value of payment type
				if($_GET['search_field'] == "vc_voucher_type" && $_GET['search_value'] == "1")  
				{
					$_GET['search_value'] = "PAYMENT";
				}

				if($_GET['search_field'] == "vc_voucher_type" && $_GET['search_value'] == "2")
				{
					$_GET['search_value'] = "RECEIPT";
				}

				if($_GET['search_field'] == "vc_entry_date") 
				{
					$_GET['search_value'] = date('d-m-Y',strtotime($_GET['search_value']));
				}
			}
			

			$vm_query = "SELECT vm.*,acc.acc_name FROM voucher_master vm LEFT JOIN account_master acc ON(vm.vc_party_id = acc.acc_id)
				WHERE vm.vc_grp_id NOT IN(5,6,23,7) AND vm.vc_fin_year = '$fin_year'  $subsql ORDER BY vm.vc_entry_no DESC LIMIT $per_page OFFSET $offset";

			// echo $vm_query;exit();

			$record['voucher_data'] = $this->db->query($vm_query)->result_array();

			foreach ($record['voucher_data'] as $key => $value) 
            {
                $record['voucher_data'][$key]['vt_trans_cnt'] = $this->db->query("SELECT * FROM voucher_trans WHERE vt_vc_id = ".$value['vc_id']."")->num_rows();
            }

            $total = 0;
	        $round_off = 0;
	        foreach ($record['voucher_data'] as $key => $value) {
	        	
	        	$total += $value['vc_amount'];
		        $round_off += $value['vc_round_off'];
	        }

	        $record['total'] = $total;
	        $record['round_off'] = $round_off;
			
			return $record;
		}
    /************************** voucher master ******************************************/

	    public function get_voucher_data_count($fin_year)
	    {
	    	$subsql = '';
			
			if(isset($_GET['search_field']) && isset($_GET['search_value']))
			{
				$field    = $_GET['search_field'];
	            $keyword  = $_GET['search_value'];

				// $start_date = date('Y-m-d',strtotime($_GET['start_date']));
	   			//          $end_date   = date('Y-m-d',strtotime($_GET['end_date']));

				// this code for check the value of payment type
				if($_GET['search_value'] == "PAYMENT") 
				{
					$_GET['search_value'] = 1;
				}

				if($_GET['search_value'] == "RECEIPT")
				{
					$_GET['search_value'] = 2;
				}

				// this code for check the value of entry date

				if($_GET['search_field'] == "vc_entry_date") 
				{
					$_GET['search_value'] = date('Y-m-d',strtotime($_GET['search_value']));
				}

				if (!empty($field) && !empty($keyword)) 
				{
					$subsql .= " AND ".$_GET['search_field']." like '%".$_GET['search_value']."%'"; 
				}
				

				// this code for unset the value of payment type
				if($_GET['search_value'] == "1")  
				{
					$_GET['search_value'] = "PAYMENT";
				}

				if($_GET['search_value'] == "2")
				{
					$_GET['search_value'] = "RECEIPT";
				}

				// this code for unset the value of entry date

				if($_GET['search_field'] == "vc_entry_date") 
				{
					$_GET['search_value'] = date('d-m-Y',strtotime($_GET['search_value']));
				}
			}

			$query = "SELECT vm.*,cl.acc_name FROM voucher_master vm LEFT JOIN account_master cl ON(vm.vc_party_id = cl.acc_id)
				WHERE vm.vc_grp_id = 5 AND vm.vc_fin_year = '$fin_year' $subsql";

			return $this->db->query($query)->num_rows();
	    }

	   	public function get_all_voucher_data($fin_year,$per_page, $offset)
		{
			$record = array();

			$subsql = '';
			
			if(isset($_GET['search_field']) && isset($_GET['search_value']))
			{	
				$field    = $_GET['search_field'];
	            $keyword  = $_GET['search_value'];
	
				// this code for check the value of payment type
				if($_GET['search_value'] == "PAYMENT") 
				{
					$_GET['search_value'] = 1;
				}

				if($_GET['search_value'] == "RECEIPT")
				{
					$_GET['search_value'] = 2;
				}

				// this code for check the value of entry date

				if($_GET['search_field'] == "vc_entry_date") 
				{
					$_GET['search_value'] = date('Y-m-d',strtotime($_GET['search_value']));
				}

				if(!empty($field) && !empty($keyword))
	            {
					$subsql .= " AND ".$_GET['search_field']." like '%".$_GET['search_value']."%'"; 
				}
				// this code for unset the value of payment type
				if($_GET['search_value'] == "1")  
				{
					$_GET['search_value'] = "PAYMENT";
				}

				if($_GET['search_value'] == "2")
				{
					$_GET['search_value'] = "RECEIPT";
				}

				// this code for unset the value of entry date

				if($_GET['search_field'] == "vc_entry_date") 
				{
					$_GET['search_value'] = date('d-m-Y',strtotime($_GET['search_value']));
				}
			}
			

			$vm_query = "SELECT vm.*,cl.acc_name FROM voucher_master vm LEFT JOIN account_master cl ON(vm.vc_party_id = cl.acc_id)
				WHERE vm.vc_grp_id = 5 AND vm.vc_fin_year = '$fin_year' $subsql ORDER BY vm.vc_entry_no DESC LIMIT $per_page OFFSET $offset";

			// echo $vm_query;exit();

			$record['voucher_data'] = $this->db->query($vm_query)->result_array();
			// echo"<pre>";print_r($record);exit();
			foreach ($record['voucher_data'] as $key => $value) 
            {
              // echo "<pre>";	print_r($record['voucher_data'][$key]);exit();
                $record['voucher_data'][$key]['vt_trans_cnt'] = $this->db->query("SELECT * FROM voucher_trans WHERE vt_vc_id = ".$value['vc_id']."")->num_rows();
            }
			// echo"<pre>";print_r($record['voucher_data'] );exit();

            $total = 0;
	        $round_off = 0;
	        foreach ($record['voucher_data'] as $key => $value) {
	        // echo"<pre>";	print_r($value);exit;
	        	$total += $value['vc_amount'];
		        $round_off += $value['vc_round_off'];
	        }

	        $record['total'] = $total;
	        $record['round_off'] = $round_off;
			// echo"<pre>";print_r($record);exit();
			
			return $record;

		}
	    /*supplier voucher master*/

	    public function get_voucher_data_supplier_count($fin_year)
	    {
	    	$subsql = '';
			
			if(isset($_GET['search_field']) && isset($_GET['search_value']))
			{
				$field    = $_GET['search_field'];
	            $keyword  = $_GET['search_value'];

				// $start_date = date('Y-m-d',strtotime($_GET['start_date']));
	   			//          $end_date   = date('Y-m-d',strtotime($_GET['end_date']));

				// this code for check the value of payment type
				if($_GET['search_value'] == "PAYMENT") 
				{
					$_GET['search_value'] = 1;
				}

				if($_GET['search_value'] == "RECEIPT")
				{
					$_GET['search_value'] = 2;
				}

				// this code for check the value of entry date

				if($_GET['search_field'] == "vc_entry_date") 
				{
					$_GET['search_value'] = date('Y-m-d',strtotime($_GET['search_value']));
				}

				if (!empty($field) && !empty($keyword)) 
				{
					$subsql .= " AND ".$_GET['search_field']." like '%".$_GET['search_value']."%'"; 
				}
				

				// this code for unset the value of payment type
				if($_GET['search_value'] == "1")  
				{
					$_GET['search_value'] = "PAYMENT";
				}

				if($_GET['search_value'] == "2")
				{
					$_GET['search_value'] = "RECEIPT";
				}

				// this code for unset the value of entry date

				if($_GET['search_field'] == "vc_entry_date") 
				{
					$_GET['search_value'] = date('d-m-Y',strtotime($_GET['search_value']));
				}
			}

			$query = "SELECT vm.*,acc.acc_name FROM voucher_master vm LEFT JOIN account_master acc ON(vm.vc_party_id = acc.acc_id)
				WHERE vm.vc_grp_id = 6 AND vm.vc_fin_year = '$fin_year' $subsql";

			return $this->db->query($query)->num_rows();
	    }

	   	public function get_all_voucher_data_supplier($fin_year,$per_page, $offset)
		{
			$record = array();

			$subsql = '';
			
			if(isset($_GET['search_field']) && isset($_GET['search_value']))
			{	
				$field    = $_GET['search_field'];
	            $keyword  = $_GET['search_value'];
	
				// this code for check the value of payment type
				if($_GET['search_value'] == "PAYMENT") 
				{
					$_GET['search_value'] = 1;
				}

				if($_GET['search_value'] == "RECEIPT")
				{
					$_GET['search_value'] = 2;
				}

				// this code for check the value of entry date

				if($_GET['search_field'] == "vm.vc_entry_date") 
				{
					$_GET['search_value'] = date('Y-m-d',strtotime($_GET['search_value']));
				}

				if(!empty($field) && !empty($keyword))
	            {
					$subsql .= " AND ".$_GET['search_field']." like '%".$_GET['search_value']."%'"; 
				}
				// this code for unset the value of payment type
				if($_GET['search_value'] == "1")  
				{
					$_GET['search_value'] = "PAYMENT";
				}

				if($_GET['search_value'] == "2")
				{
					$_GET['search_value'] = "RECEIPT";
				}

				// this code for unset the value of entry date

				if($_GET['search_field'] == "vc_entry_date") 
				{
					$_GET['search_value'] = date('d-m-Y',strtotime($_GET['search_value']));
				}
			}
			

			$vm_query = "SELECT vm.*,acc.acc_name FROM voucher_master vm LEFT JOIN account_master acc ON(vm.vc_party_id = acc.acc_id)
				WHERE vm.vc_grp_id = 6 AND vm.vc_fin_year = '$fin_year' $subsql ORDER BY vm.vc_entry_no DESC LIMIT $per_page OFFSET $offset";

			// echo $vm_query;exit();

			$record['voucher_data'] = $this->db->query($vm_query)->result_array();

			foreach ($record['voucher_data'] as $key => $value) 
            {
                $record['voucher_data'][$key]['vt_trans_cnt'] = $this->db->query("SELECT * FROM voucher_trans WHERE vt_vc_id = ".$value['vc_id']."")->num_rows();
            }

            $total = 0;
	        $round_off = 0;
	        foreach ($record['voucher_data'] as $key => $value) {
	        	
	        	$total += $value['vc_amount'];
		        $round_off += $value['vc_round_off'];
	        }

	        $record['total'] = $total;
	        $record['round_off'] = $round_off;
			
			return $record;
		}

/*********************************** karigar voucher ************************************/
		 public function get_voucher_data_karigar_count($fin_year)
	    {
	    	$subsql = '';
			
			if(isset($_GET['search_field']) && isset($_GET['search_value']))
			{
				$field    = $_GET['search_field'];
	            $keyword  = $_GET['search_value'];

				// $start_date = date('Y-m-d',strtotime($_GET['start_date']));
	   			//          $end_date   = date('Y-m-d',strtotime($_GET['end_date']));

				// this code for check the value of payment type
				if($_GET['search_value'] == "PAYMENT") 
				{
					$_GET['search_value'] = 1;
				}

				if($_GET['search_value'] == "RECEIPT")
				{
					$_GET['search_value'] = 2;
				}

				// this code for check the value of entry date

				if($_GET['search_field'] == "vc_entry_date") 
				{
					$_GET['search_value'] = date('Y-m-d',strtotime($_GET['search_value']));
				}

				if (!empty($field) && !empty($keyword)) 
				{
					$subsql .= " AND ".$_GET['search_field']." like '%".$_GET['search_value']."%'"; 
				}
				

				// this code for unset the value of payment type
				if($_GET['search_value'] == "1")  
				{
					$_GET['search_value'] = "PAYMENT";
				}

				if($_GET['search_value'] == "2")
				{
					$_GET['search_value'] = "RECEIPT";
				}

				// this code for unset the value of entry date

				if($_GET['search_field'] == "vc_entry_date") 
				{
					$_GET['search_value'] = date('d-m-Y',strtotime($_GET['search_value']));
				}
			}

			$query = "SELECT vm.*,k.km_name FROM voucher_master vm LEFT JOIN karigar_master k ON(vm.vc_party_id = k.km_id)
				WHERE vm.vc_grp_id = 7 AND vm.vc_fin_year = '$fin_year' $subsql";

			return $this->db->query($query)->num_rows();
	    }

	   	public function get_all_voucher_data_karigar($fin_year,$per_page, $offset)
		{
			$record = array();

			$subsql = '';
			
			if(isset($_GET['search_field']) && isset($_GET['search_value']))
			{	
				$field    = $_GET['search_field'];
	            $keyword  = $_GET['search_value'];
	
				// this code for check the value of payment type
				if($_GET['search_value'] == "PAYMENT") 
				{
					$_GET['search_value'] = 1;
				}

				if($_GET['search_value'] == "RECEIPT")
				{
					$_GET['search_value'] = 2;
				}

				// this code for check the value of entry date

				if($_GET['search_field'] == "vc_entry_date") 
				{
					$_GET['search_value'] = date('Y-m-d',strtotime($_GET['search_value']));
				}

				if(!empty($field) && !empty($keyword))
	            {
					$subsql .= " AND ".$_GET['search_field']." like '%".$_GET['search_value']."%'"; 
				}
				// this code for unset the value of payment type
				if($_GET['search_value'] == "1")  
				{
					$_GET['search_value'] = "PAYMENT";
				}

				if($_GET['search_value'] == "2")
				{
					$_GET['search_value'] = "RECEIPT";
				}

				// this code for unset the value of entry date

				if($_GET['search_field'] == "vc_entry_date") 
				{
					$_GET['search_value'] = date('d-m-Y',strtotime($_GET['search_value']));
				}
			}
			

			$vm_query = "SELECT vm.*,k.km_name FROM voucher_master vm LEFT JOIN karigar_master k ON(vm.vc_party_id = k.km_id)
				WHERE vm.vc_grp_id = 7  AND vm.vc_fin_year = '$fin_year' $subsql ORDER BY vm.vc_entry_no DESC LIMIT $per_page OFFSET $offset";
			$record['voucher_data'] = $this->db->query($vm_query)->result_array();
			
			foreach ($record['voucher_data'] as $key => $value) 
            {
            	$total_adj_amt = 0;
            	$trans_query = "SELECT *,DATE_FORMAT(v.vt_bill_date, '%d-%m-%Y') AS vt_bill_date,o.* FROM voucher_trans v LEFT JOIN sales_master o ON(v.vt_sale_id = o.sale_id) WHERE vt_vc_id = ".$value['vc_id']."";
				$trans_data = $this->db->query($trans_query)->result_array();
				$record['voucher_data'][$key]['vt_trans_cnt'] =0;  
				if(!empty($trans_data)){
					foreach ($trans_data as $k => $value) {
						$total_adj_amt += ($value['vt_adjust_amt'] + $value['vt_adjust_round_off']);
					}
					$total_master_adj = $record['voucher_data'][0]['vc_amount'] + $record['voucher_data'][0]['vc_round_off'];
					
					if($total_master_adj <= $total_adj_amt){
						$record['voucher_data'][$key]['vt_trans_cnt'] =1;
					}

				}else{
					$record['voucher_data'][$key]['vt_trans_cnt'] = $this->db->query("SELECT * FROM voucher_trans WHERE vt_vc_id = ".$value['vc_id']."")->num_rows();
				}

                
            }
			
            $total = 0;
	        $round_off = 0;
	        foreach ($record['voucher_data'] as $key => $value) {
	        	
	        	$total += $value['vc_amount'];
		        $round_off += $value['vc_round_off'];
	        }

	        $record['total'] = $total;
	        $record['round_off'] = $round_off;

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

			return $record;
		}

/*********************************** /end karigar voucher *****************************/

		public function get_single_voucher($fin_year)
		{
			$record = array();
			$record['entry_no'] = $this->get_fin_year_wise_max_id('voucher_master','vc_entry_no','vc_fin_year',$fin_year);

			$client_data = $this->db->query("SELECT acc_id,acc_name,acc_contact FROM account_master where acc_grp_id = 5")->result_array();
			$record['client_data'][0] = "PLEASE SELECT";
			foreach ($client_data as $key => $value) {

				$record['client_data'][$value['acc_id']] = $value['acc_name'] .'-'. $value['acc_contact'];
				// print_r($record['client_data'][$value['acc_id']]);exit;
			}
// echo"<pre>";print_r($record);exit();
			$karigar_data = $this->db->query("SELECT km_id,km_name FROM karigar_master ORDER BY km_name ASC")->result_array();
			$record['karigar_data'][0] = "PLEASE SELECT";
			// foreach ($karigar_data as $key => $value) {
			// 	$record['karigar_data'][$value['km_id']] = $value['km_name'];
			// }

			if($_GET['type'] == "PAYMENT")
				$record['voucher_type_data'][1] = "PAYMENT";
			else if($_GET['type'] == "RECEIPT")
				$record['voucher_type_data'][2] = "RECEIPT";
			else
				$record['voucher_type_data'][-1] = "OPENING";


            // $record['party_data'][0] = "PLEASE SELECT";

			$record['voucher_type'] = $_GET['type'];

			return $record;
		}


		public function get_party_list_data_groupwise($grp_id)
		{
			if($grp_id == 5)
			{
				return $this->db->query("SELECT acc_id,acc_name,acc_contact FROM account_master ORDER BY acc_name ASC")->result_array();
			}
			else if($grp_id == 6)
			{
				return $this->db->query("SELECT acc_id,acc_name,acc_mobile FROM account_master WHERE acc_grp_id = '6' ORDER BY acc_name ASC")->result_array();
			
			}else if($grp_id == 7){
				return $this->db->query("SELECT km_id,km_name,km_mob FROM karigar_master ORDER BY km_name ASC")->result_array();
			} else{
				return $this->db->query("SELECT acc_id,acc_name,acc_mobile FROM account_master WHERE acc_grp_id NOT IN (5,6,23,7) ORDER BY acc_name ASC")->result_array();
			}
			
		}
        
        public function get_party_status_with_billno($bill_no)
        {
            $result = array();
            $order_amt = 0;

            $order_data = $this->db->query("SELECT sale_id, sale_customer_id, 0 as sale_balance FROM sales_master WHERE sale_id = $bill_no")->result_array();

           
            if(!empty($order_data))
            {
            	$acc_id = $order_data[0]['sale_customer_id'];
            	$grp_id = 5;
            }

            if ($grp_id == 5) 
            {
	            $acc_data = $this->db->query("SELECT 0 as acc_bal FROM account_master WHERE acc_id = $acc_id")->result_array();
	            $acc_bal = $acc_data[0]['acc_bal'];
            }
            else if ($grp_id == 6) 
            {
	            $acc_data = $this->db->query("SELECT 0 as acc_op_balance FROM account_master WHERE acc_id = $acc_id AND acc_grp_id=6")->result_array();
	            $acc_bal = $acc_data[0]['acc_op_balance'];
            }
            else
            {
            	$acc_bal = 0;
            }

        	$vc_query = "SELECT IFNULL(SUM(IF(v.vc_trans_type='DR',(v.vc_amount + v.vc_round_off),0)),0) AS TVDRTOT, IFNULL(SUM(IF(v.vc_trans_type='CR',(v.vc_amount + v.vc_round_off),0)),0) AS TVCRTOT FROM voucher_master v WHERE v.vc_grp_id = $grp_id AND v.vc_party_id = $acc_id";
        	$voucher_op_data = $this->db->query($vc_query)->result_array();

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

        	if($grp_id == 5) 
        	{
        		$order_query = "SELECT IFNULL(SUM(sale_total_amt),0) as final_total FROM sales_master sale WHERE sale.sale_customer_id='".$acc_id."'";

        		// echo $order_query;exit();

           		$order_amt = $this->db->query($order_query)->result_array()[0]['final_total'];
        		
        		// echo $order_amt;exit();

           		$total_cr_amt = $acc_bal + $voucher_op_data[0]['TVCRTOT'];
    			$total_dr_amt = $acc_bal + $order_amt + $voucher_op_data[0]['TVDRTOT'];

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

	    		if($total_cr_amt >= $total_dr_amt)
		        {
		            $openiong_amt = $total_cr_amt - $total_dr_amt;

		            if($openiong_amt >= 0)
		            {
		                $openiong_amt = round($openiong_amt)." TO PAY";
		            }
		            else
		            {
		                $openiong_amt = round($openiong_amt)." TO RECEIVE";
		            }
		        }
		        else
		        {
		            $openiong_amt = $total_dr_amt - $total_cr_amt;

		            // echo $openiong_amt;exit();

		            if($openiong_amt >= 0)
		            {
		                $openiong_amt = round($openiong_amt)." TO RECEIVE";
		            }
		            else
		            {
		                $openiong_amt = round($openiong_amt)." TO PAY";
		            }
		        }

		        $client_query = "SELECT acc_name, acc_contact FROM account_master WHERE acc_id = $acc_id";

		        $client_mob_no = $this->db->query($client_query)->result_array()[0]['acc_contact'];
		        $acc_name = $this->db->query($client_query)->result_array()[0]['acc_name'];

		        $result['acc_mob_no'] = $client_mob_no;
		        $result['acc_name'] = $acc_name;
		        $result['acc_id'] = $acc_id;
        	}
        	else if($grp_id == 6) 
        	{
        		$inward_query = "SELECT IFNULL(SUM(per.per_total_amt),0) as final_total FROM purchase_master per WHERE per.per_supplier_id='".$acc_id."'";

        		// echo $inward_query;exit();

           		$inward_amt = $this->db->query($inward_query)->result_array()[0]['final_total'];
        		
        		// echo $order_amt;exit();

           		$total_cr_amt = $acc_bal + $inward_amt + $voucher_op_data[0]['TVCRTOT'];
    			$total_dr_amt = $acc_bal + $voucher_op_data[0]['TVDRTOT'];

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

	    		if($total_cr_amt >= $total_dr_amt)
		        {
		            $openiong_amt = $total_cr_amt - $total_dr_amt;

		            if($openiong_amt >= 0)
		            {
		                $openiong_amt = round($openiong_amt)." TO RECEIVE ";
		            }
		            else
		            {
		                $openiong_amt = round($openiong_amt)." TO PAY";
		            }
		        }
		        else
		        {
		            $openiong_amt = $total_dr_amt - $total_cr_amt;

		            // echo $openiong_amt;exit();

		            if($openiong_amt >= 0)
		            {
		                $openiong_amt = round($openiong_amt)." TO PAY";
		            }
		            else
		            {
		                $openiong_amt = round($openiong_amt)." TO RECEIVE";
		            }
		        }

		        $acc_query = "SELECT acc_mobile FROM account_master WHERE acc_id = $acc_id";

		        $client_mob_no = $this->db->query($acc_query)->result_array()[0]['acc_mobile'];

		        $result['acc_mob_no'] = $client_mob_no;
        	}
        	else
        	{
        		$hisab_query = "SELECT IFNULL(SUM(hm_amount - hm_round_off),0) as final_total FROM hisab_master WHERE hm_km_id = $acc_id";
           		$hisab_amt = $this->db->query($hisab_query)->result_array()[0]['final_total'];

           		// echo "<pre>";print_r($voucher_op_data[0]['TVCRTOT']);exit();
           		// echo "<pre>";print_r($hisab_amt);exit();
           		// echo "<pre>";print_r($hisab_amt);exit();

           		$total_cr_amt = $acc_bal + $hisab_amt + $voucher_op_data[0]['TVCRTOT'];
        		$total_dr_amt = $acc_bal + $voucher_op_data[0]['TVDRTOT'];
        		
        		// echo "<pre>";print_r($total_cr_amt);
        		// echo "<pre>";print_r($total_dr_amt);
        		// exit();

        		if($total_cr_amt >= $total_dr_amt)
		        {
		            $openiong_amt = $total_cr_amt - $total_dr_amt;

		            if($openiong_amt >= 0)
		            {
		                $openiong_amt = round($openiong_amt)." TO PAY";
		            }
		            else
		            {
		                $openiong_amt = round($openiong_amt)." TO RECEIVE";
		            }
		        }
		        else
		        {
		            $openiong_amt = $total_dr_amt - $total_cr_amt;

		            if($openiong_amt >= 0)
		            {
		                $openiong_amt = round($openiong_amt)." TO RECEIVE";
		            }
		            else
		            {
		                $openiong_amt = round($openiong_amt)." TO PAY";
		            }
		        }

		        $karigar_query = "SELECT km_mob FROM karigar_master WHERE km_id = $acc_id";

		        $karigar_mob_no = $this->db->query($karigar_query)->result_array()[0]['km_mob'];

		        $result['acc_mob_no'] = $karigar_mob_no;

        	}

            $result['status'] = $openiong_amt;
      
            return $result;
        }

        public function get_party_acc_status($acc_id,$grp_id)
        {
			
            $result = array();
            $order_amt = 0;

            if ($grp_id == 5) 
            {
	            $acc_data = $this->db->query("SELECT 0 as acc_bal FROM account_master WHERE acc_id = $acc_id")->result_array();
	            $acc_bal = $acc_data[0]['acc_bal'];
            }
            else if ($grp_id == 6) 
            {
	            $acc_data = $this->db->query("SELECT 0 as acc_op_balance FROM account_master WHERE acc_id = $acc_id AND acc_grp_id=6")->result_array();
	            $acc_bal = $acc_data[0]['acc_op_balance'];
            }
            else if($grp_id == 7)
            {
            	$acc_bal = 0;
            }else{
            	$acc_bal = $this->db->query("SELECT 
            		IFNULL(SUM(IF(acc_drcr='CR',acc_op_balance,0)),0) AS TVCRTOT,
                    IFNULL(SUM(IF(acc_drcr='DR',acc_op_balance,0)),0) AS TVDRTOT
                    FROM account_master WHERE acc_id = '".$acc_id."' AND acc_grp_id NOT IN(5,6,23,7)")->result_array();
	        }

        	$vc_query = "SELECT IFNULL(SUM(IF(v.vc_trans_type='DR',(v.vc_amount + v.vc_round_off),0)),0) AS TVDRTOT, IFNULL(SUM(IF(v.vc_trans_type='CR',(v.vc_amount + v.vc_round_off),0)),0) AS TVCRTOT FROM voucher_master v WHERE v.vc_grp_id = $grp_id AND v.vc_party_id = $acc_id";
        	$voucher_op_data = $this->db->query($vc_query)->result_array();
        	// print_r($voucher_op_data);exit();

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

        	if($grp_id == 5) 
        	{
        		$order_query = "SELECT IFNULL(SUM(sale_total_amt),0) as final_total FROM sales_master sale WHERE sale.sale_customer_id='".$acc_id."'";

        		// echo $order_query;exit();

           		$order_amt = $this->db->query($order_query)->result_array()[0]['final_total'];
        		
        		// echo $order_amt;exit();

           		$total_cr_amt = $acc_bal + $voucher_op_data[0]['TVCRTOT'];
    			$total_dr_amt = $acc_bal + $order_amt + $voucher_op_data[0]['TVDRTOT'];

	        	// echo "<pre>";print_r("cr:".$total_cr_amt);
	        	// echo "<pre>";print_r("dr:".$total_dr_amt);exit();

	    		if($total_cr_amt >= $total_dr_amt)
		        {
		            $openiong_amt = $total_cr_amt - $total_dr_amt;

		            if($openiong_amt >= 0)
		            {
		                $openiong_amt = round($openiong_amt)." TO PAY";
		            }
		            else
		            {
		                $openiong_amt = round($openiong_amt)." TO RECEIVE";
		            }
		        }
		        else
		        {
		            $openiong_amt = $total_dr_amt - $total_cr_amt;

		            // echo $openiong_amt;exit();

		            if($openiong_amt >= 0)
		            {
		                $openiong_amt = round($openiong_amt)." TO RECEIVE";
		            }
		            else
		            {
		                $openiong_amt = round($openiong_amt)." TO PAY";
		            }
		        }

		        $client_query = "SELECT acc_name, acc_contact FROM account_master WHERE acc_id = $acc_id";

		        $client_mob_no = $this->db->query($client_query)->result_array()[0]['acc_contact'];
		        $acc_name = $this->db->query($client_query)->result_array()[0]['acc_name'];

		        $result['acc_mob_no'] = $client_mob_no;
		        $result['acc_name'] = $acc_name;
        	}
        	else if($grp_id == 6) 
        	{
        		$inward_query = "SELECT IFNULL(SUM(per.per_total_amt),0) as final_total FROM purchase_master per WHERE per.per_supplier_id='".$acc_id."'";

        		// echo $inward_query;exit();

           		$inward_amt = $this->db->query($inward_query)->result_array()[0]['final_total'];
        		
        		// echo $order_amt;exit();

           		$total_cr_amt = $acc_bal + $inward_amt + $voucher_op_data[0]['TVCRTOT'];
    			$total_dr_amt = $acc_bal + $voucher_op_data[0]['TVDRTOT'];

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

	    		if($total_cr_amt >= $total_dr_amt)
		        {
		            $openiong_amt = $total_cr_amt - $total_dr_amt;

		            if($openiong_amt >= 0)
		            {
		                $openiong_amt = round($openiong_amt)." TO RECEIVE ";
		            }
		            else
		            {
		                $openiong_amt = round($openiong_amt)." TO PAY";
		            }
		        }
		        else
		        {
		            $openiong_amt = $total_dr_amt - $total_cr_amt;

		            // echo $openiong_amt;exit();

		            if($openiong_amt >= 0)
		            {
		                $openiong_amt = round($openiong_amt)." TO PAY";
		            }
		            else
		            {
		                $openiong_amt = round($openiong_amt)." TO RECEIVE";
		            }
		        }

		        $acc_query = "SELECT acc_mobile FROM account_master WHERE acc_id = $acc_id";

		        $client_mob_no = $this->db->query($acc_query)->result_array()[0]['acc_mobile'];

		        $result['acc_mob_no'] = $client_mob_no;
        	}
        	else if($grp_id == 7) 
        	{
        		$hisab_query = "SELECT IFNULL(SUM(hm.hm_amount),0) as final_total FROM hisab_master hm WHERE hm.hm_karigar_id = '".$acc_id."'";
           		$hisab_amt = $this->db->query($hisab_query)->result_array()[0]['final_total'];

           		$total_cr_amt = $acc_bal + $hisab_amt + $voucher_op_data[0]['TVCRTOT'];
        		$total_dr_amt = $acc_bal + $voucher_op_data[0]['TVDRTOT'];
        		if($total_cr_amt >= $total_dr_amt)
		        {
		            $openiong_amt = $total_cr_amt - $total_dr_amt;

		            if($openiong_amt >= 0)
		            {
		                $openiong_amt = round($openiong_amt)." TO PAY";
		            }
		            else
		            {
		                $openiong_amt = round($openiong_amt)." TO RECEIVE";
		            }
		        }
		        else
		        {
		            $openiong_amt = $total_dr_amt - $total_cr_amt;

		            if($openiong_amt >= 0)
		            {
		                $openiong_amt = round($openiong_amt)." TO RECEIVE";
		            }
		            else
		            {
		                $openiong_amt = round($openiong_amt)." TO PAY";
		            }
		        }

		        $karigar_query = "SELECT km_mob FROM karigar_master WHERE km_id = $acc_id";

		        $karigar_mob_no = $this->db->query($karigar_query)->result_array()[0]['km_mob'];

		        $result['acc_mob_no'] = $karigar_mob_no;

        	}else{

        	$vc_query = "SELECT IFNULL(SUM(IF(v.vc_trans_type='DR',(v.vc_amount + v.vc_round_off),0)),0) AS TVDRTOT, IFNULL(SUM(IF(v.vc_trans_type='CR',(v.vc_amount + v.vc_round_off),0)),0) AS TVCRTOT FROM voucher_master v WHERE v.vc_grp_id NOT IN(5,6,23,7) AND v.vc_party_id = '".$acc_id."'";
        		$voucher_op_data = $this->db->query($vc_query)->result_array();

        		$total_cr_amt = $acc_bal[0]['TVCRTOT'] + $voucher_op_data[0]['TVCRTOT'];
    			$total_dr_amt = $acc_bal[0]['TVDRTOT'] + $voucher_op_data[0]['TVDRTOT'];

    			//this is cash and bank balance
    			$acc_data = $this->db->query("SELECT acc_grp_id FROM account_master WHERE acc_id = '".$acc_id."' ")->result_array();
        		if(!empty($acc_data) && ($acc_data[0]['acc_grp_id'] == 9)){
        			$vc_query = "SELECT IFNULL(SUM(IF(v.vc_trans_type='DR',(v.vc_amount + v.vc_round_off),0)),0) AS TVDRTOT, IFNULL(SUM(IF(v.vc_trans_type='CR',(v.vc_amount + v.vc_round_off),0)),0) AS TVCRTOT FROM voucher_master v WHERE /*v.vc_grp_id = $grp_id AND*/ v.vc_cash_acc_id = '".$acc_id."'";
        			$voucher_op_data = $this->db->query($vc_query)->result_array();

        			$total_cr_amt = $total_cr_amt + $voucher_op_data[0]['TVDRTOT'];
    				$total_dr_amt = $total_dr_amt + $voucher_op_data[0]['TVCRTOT'];
        		}
	        	// echo "<pre>";print_r($total_cr_amt);
	        	// echo "<pre>";print_r($total_dr_amt);exit();
	    		if($total_cr_amt >= $total_dr_amt)
		        {
		            $openiong_amt = $total_cr_amt - $total_dr_amt;
		            if($openiong_amt >= 0)
		            {
		                $openiong_amt = round($openiong_amt)." TO PAY ";
		            }
		            else
		            {
		                $openiong_amt = round($openiong_amt)." TO RECEIVE";
		            }
		        }
		        else
		        {
		            $openiong_amt = $total_dr_amt - $total_cr_amt;
		            // echo $openiong_amt;exit();
		            if($openiong_amt >= 0)
		            {
		                $openiong_amt = round($openiong_amt)." TO RECEIVE";
		            }
		            else
		            {
		                $openiong_amt = round($openiong_amt)." TO PAY";
		            }
		        }
		        $acc_query = "SELECT acc_mobile FROM account_master WHERE acc_id = '".$acc_id."'";
		        $client_mob_no = $this->db->query($acc_query)->result_array()[0]['acc_mobile'];
		        $result['acc_mob_no'] = $client_mob_no;
        	}

            $result['status'] = $openiong_amt;
      
            return $result;
        }

		public function get_party_order_details($acc_id,$grp_id)
		{
			
			if ($grp_id == 5) 
			{
				$query = "SELECT sale.*,  CASE 
        WHEN sale.sale_entry_no = '' OR sale.sale_entry_no IS NULL 
            THEN sale.sale_unregi_entry_no
        ELSE sale.sale_entry_no
    END AS sale_entry_no, (sale.sale_total_amt - (sale.sale_allocated_amt + sale.sale_round_off)) as bal_amt,DATE_FORMAT(sale.sale_entry_date,'%d-%m-%Y') AS sale_bill_date FROM sales_master sale WHERE sale.sale_customer_id = $acc_id HAVING bal_amt > 0";
				
				return  $this->db->query($query)->result_array();
			}
			if ($grp_id == 6) 
			{
				$query = "SELECT per.*, CASE 
        WHEN per.per_entry_no = '' OR per.per_entry_no IS NULL 
            THEN per.per_unregi_entry_no
        ELSE per.per_entry_no
    END AS per_entry_no, (per.per_total_amt - (per.per_allocated_amt + per.per_round_off)) as bal_amt,DATE_FORMAT(per.per_entry_date,'%d-%m-%Y') AS per_entry_date ,DATE_FORMAT(per.per_bill_date,'%d-%m-%Y') AS per_bill_date FROM purchase_master per WHERE per.per_supplier_id = $acc_id HAVING bal_amt > 0";
				
				return  $this->db->query($query)->result_array();
			}
			else
			{
				$query2 = "SELECT hm.*,(hm.hm_amount - (hm.hm_allocated_amt + hm.hm_round_off)) as bal_amt,DATE_FORMAT(hm.hm_entry_date,'%d-%m-%Y') AS hm_entry_date FROM hisab_master hm WHERE hm.hm_karigar_id = $acc_id HAVING bal_amt > 0";
				
				return  $this->db->query($query2)->result_array();
			}
		}

		public function get_party_order_details_with_billno($bill_no)
		{
			$order_data = $this->db->query("SELECT sale_id, sale_customer_id, 0 as sale_balance FROM sales_master WHERE sale_entry_no = $bill_no")->result_array();

           
            if(!empty($order_data))
            {
            	$acc_id = $order_data[0]['sale_customer_id'];
            	
            	$query = "SELECT sale.*,(sale.sale_total_amt - (sale.sale_allocated_amt + sale.sale_round_off)) as bal_amt,DATE_FORMAT(sale.sale_entry_date,'%d-%m-%Y') AS sale_bill_date FROM sales_master sale WHERE sale.sale_customer_id = $acc_id HAVING bal_amt > 0";
			
				return  $this->db->query($query)->result_array();
            }
			
			
		}
		
     

		public function check_duplicate_voucher_entry($entry_no,$fin_year)
		{
			$query = "SELECT vc_id FROM voucher_master WHERE vc_entry_no = '$entry_no' AND vc_fin_year = '$fin_year'";
			return $this->db->query($query)->num_rows();
		}

		public function check_edit_duplicate_voucher_entry($entry_no,$fin_year,$vc_id)
		{
			$query = "SELECT vc_id FROM voucher_master WHERE vc_entry_no = '$entry_no' AND vc_fin_year = '$fin_year' AND vc_id != $vc_id";
			return $this->db->query($query)->num_rows();
		}


		public function get_single_update_voucher($vc_id)
		{
			$record = array();
			if($_GET['type'] == "PAYMENT")
				$record['voucher_type_data'][1] = "PAYMENT";
			else if($_GET['type'] == "RECEIPT")
				$record['voucher_type_data'][2] = "RECEIPT";
			else
				$record['voucher_type_data'][-1] = "OPENING";


            // $record['party_data'][0] = "PLEASE SELECT";

			$record['voucher_type'] = $_GET['type'];

			$record['voucher_data'] = $this->db->query("SELECT v.*,DATE_FORMAT(v.vc_entry_date, '%d-%m-%Y') AS vc_entry_date,c.acc_name FROM voucher_master v LEFT JOIN account_master c ON(v.vc_party_id = c.acc_id) WHERE v.vc_id = $vc_id")->result_array();
			if(!empty($record['voucher_data'])){
				$record['voucher_data'][0]['outstanding_show'] =0;  
			}

			$voucher_trans_query = "SELECT *,DATE_FORMAT(v.vt_bill_date, '%d-%m-%Y') AS vt_bill_date,o.* FROM voucher_trans v LEFT JOIN sales_master o ON(v.vt_sale_id = o.sale_id) WHERE vt_vc_id = $vc_id";
			// echo $voucher_trans_query;exit();
			$record['voucher_trans'] = $this->db->query($voucher_trans_query)->result_array();
			
			$total_adj_amt = 0;
			if(!empty($record['voucher_trans']) && $record['voucher_data'][0]['vc_grp_id']==7){
				foreach ($record['voucher_trans'] as $key => $value) {
					$total_adj_amt = $total_adj_amt + ($value['vt_adjust_amt']+$value['vt_adjust_round_off']);
				}
				$total_master_adj = ($record['voucher_data'][0]['vc_amount']+$record['voucher_data'][0]['vc_round_off']);
				if($total_master_adj > $total_adj_amt){
					$record['voucher_data'][0]['outstanding_show'] =1;
				}
			}




			// echo "<pre>";print_r($record['voucher_trans']);exit();

			$acc_id 	= $record['voucher_data'][0]['vc_cash_acc_id'];
			$party_id 	= $record['voucher_data'][0]['vc_party_id'];

			if ($record['voucher_data'][0]['vc_grp_id'] == 5) 
			{
				$client_data = $this->db->query("SELECT acc_id,acc_name,acc_contact FROM account_master WHERE acc_id = $party_id")->result_array();
				$record['party_id'] = $client_data[0]['acc_id'];
				$record['party_name'] = $client_data[0]['acc_name'];
				$record['mob'] = $client_data[0]['acc_contact'];

			}
			else if ($record['voucher_data'][0]['vc_grp_id'] == 6) 
			{
				$acc_data = $this->db->query("SELECT acc_id,acc_name,acc_mobile FROM account_master WHERE acc_id = $party_id")->result_array();
				$record['party_id'] = $acc_data[0]['acc_id'];
				$record['party_name'] = $acc_data[0]['acc_name'];
				$record['mob'] = $acc_data[0]['acc_mobile'];

			}
			else if($record['voucher_data'][0]['vc_grp_id'] == 7)
			{
				$karigar_data = $this->db->query("SELECT km_id,km_name,km_mob FROM karigar_master WHERE km_id = $party_id ORDER BY km_name ASC")->result_array();
				$record['party_id'] = $karigar_data[0]['km_id'];
				$record['party_name'] = $karigar_data[0]['km_name'];
				$record['mob'] = $karigar_data[0]['km_mob'];

			}else{
				$acc_data = $this->db->query("SELECT acc_id,acc_name,acc_mobile FROM account_master WHERE acc_id = $party_id")->result_array();
				$record['party_id'] = $acc_data[0]['acc_id'];
				$record['party_name'] = $acc_data[0]['acc_name'];
				$record['mob'] = $acc_data[0]['acc_mobile'];				
			}
			
			return $record;
		}



/*sale model end*/
	}
?>