<?php
	class Salestest_model extends CI_model
	{
		public function __construct()
		{
			parent::__construct();
			$this->load->database();
			//$this->db2 = $this->load->database('otherdb', TRUE);
	
		}
		public function day_close_all_mode_pdf($branch) 
    	{
    	  
    		$cur_date = date("Y-m-d");
    		  //echo $branch;
    		$role_id = $this->session->userdata("role");
    		$query=$this->db2->query(" SELECT branch_slno as branch_slno,branch FROM outlet_deatils WHERE status='Active' and  branch='$branch'");
    		$total_branch = $query->result_array();
    		//$count =1;
    		foreach ($total_branch as $brans) {
    			$branch_code = $brans['branch_slno'];
    			$branches = $brans['branch'];
    			if ($branch == $branches){
    			    //echo "entered";
    				$table = 'product_billdetail'.$branch_code;
    				$table1 = 'payments_details'.$branch_code;
    				$query1=$this->db2->query("SELECT sum(D.multi_payment) as total_amount, D.payment_types FROM $table P inner join $table1 D on P.bill_id=D.bill_id WHERE P.status='closed' and D.payment_date = '$cur_date' and (D.payment_types !='NC' and D.payment_types !='CREDIT')  group by D.payment_types");
    				$result = $query1->result();
    			}
    		}	
    		return $result;
    	}
    	
    	public function get_edit_billdetails($branch) 
    	{
    		$cur_date = date("Y-m-d");
    		$role_id = $this->session->userdata("role");
    		$query=$this->db2->query(" SELECT branch_slno as branch_slno,branch FROM outlet_deatils WHERE status='Active' and  branch='$branch'");
    		$total_branch = $query->result_array();
    		//$count =1;
    		foreach ($total_branch as $brans) {
    			$branch_code = $brans['branch_slno'];
    			$branches = $brans['branch'];
    			if ($branch == $branches){
    				$table = 'product_billdetail'.$branch_code;
    			//	$table1 = 'payments_details'.$count;
    				$query=$this->db2->query("SELECT * FROM $table where edit_date = '$cur_date'");
    				$result = $query->result();
    			}
    		}	
    		return $result;
    	}
    	public function get_credits_billsdetails($branch) 
    	{
    		$cur_date = date("Y-m-d");
    		$role_id = $this->session->userdata("role");
    		$query=$this->db2->query(" SELECT branch_slno as branch_slno,branch FROM outlet_deatils WHERE status='Active' and  branch='$branch'");
    		$total_branch = $query->result_array();
    		foreach ($total_branch as $brans) {
    			$branch_code = $brans['branch_slno'];
    			$branches = $brans['branch'];
    			if ($branch == $branches){
    				$table = 'product_billdetail'.$branch_code;
    				$query=$this->db2->query("SELECT * FROM $table where bno != '0' and mop='CREDIT' and stlmnt_status !='Setteled'");
    				$result = $query->result();
    			}
    		}	
    		return $result;
    	}
    	public function get_nc_billsdetails($branch) 
    	{
    		$cur_date = date("Y-m-d");
    		$role_id = $this->session->userdata("role");
    		$query=$this->db2->query(" SELECT branch_slno as branch_slno,branch FROM outlet_deatils WHERE status='Active' and  branch='$branch'");
    		$total_branch = $query->result_array();
    		//$count =1;
    		foreach ($total_branch as $brans) {
    			$branch_code = $brans['branch_slno'];
    			$branches = $brans['branch'];
    			if ($branch == $branches){
    				$table = 'product_billdetail'.$branch_code;
    			//	$table1 = 'payments_details'.$count;
    				$query=$this->db2->query("SELECT * FROM $table where  mop='NC' and invoice_date='$cur_date'");
    				$result = $query->result();
    			}
    		}	
    		return $result;
    	}
    	public function get_cancelled_billsdetails($branch) 
    	{
    		$cur_date = date("Y-m-d");
    		$role_id = $this->session->userdata("role");
    		$query=$this->db2->query(" SELECT branch_slno as branch_slno,branch FROM outlet_deatils WHERE status='Active' and  branch='$branch'");
    		$total_branch = $query->result_array();
    		foreach ($total_branch as $brans) {
    			$branch_code = $brans['branch_slno'];
    			$branches = $brans['branch'];
    			if ($branch == $branches){
    				$table = 'product_billdetail'.$branch_code;
    				$query=$this->db2->query("SELECT * FROM $table where  status='cancelled' and invoice_date='$cur_date'");
    				$result = $query->result();
    			}
    		}	
    		return $result;
    	}
    	public function get_discount_billsdetails($branch) 
    	{
    		$cur_date = date("Y-m-d");
    		$role_id = $this->session->userdata("role");
    		$query=$this->db2->query(" SELECT branch_slno as branch_slno,branch FROM outlet_deatils WHERE status='Active' and  branch='$branch'");
    		$total_branch = $query->result_array();
    		//$count =1;
    		foreach ($total_branch as $brans) {
    			$branch_code = $brans['branch_slno'];
    			$branches = $brans['branch'];
    			if ($branch == $branches){
    				$table = 'product_billdetail'.$branch_code;
    				$query=$this->db2->query("SELECT * FROM $table where  disc_total_amt !='0' and invoice_date='$cur_date'");
    				$result = $query->result();
    			}
    		}	
    		return $result;
    	}
    	public function get_bill_ids($branch) 
    	{
    		$cur_date = date("Y-m-d");
    		$role_id = $this->session->userdata("role");
    		$query=$this->db2->query(" SELECT branch_slno as branch_slno,branch FROM outlet_deatils WHERE status='Active' and  branch='$branch'");
    		$total_branch = $query->result_array();
    		foreach ($total_branch as $brans) {
    			$branch_code = $brans['branch_slno'];
    			$branches = $brans['branch'];
    			if ($branch == $branches){
    				$table = 'product_billdetail'.$branch_code;
    				$query=$this->db2->query("SELECT min(in_no) as min_id, max(in_no) as max_id,branch FROM $table where invoice_date='$cur_date' and mop!='CREDIT'");
    				$result = $query->result();
    			}
    		}	
    		return $result;
    	}
    	public function nc_crdit_pdf($branch) 
    	{
    		$cur_date = date("Y-m-d");
    		$role_id = $this->session->userdata("role");
    		$query=$this->db2->query(" SELECT branch_slno as branch_slno,branch FROM outlet_deatils WHERE status='Active' and  branch='$branch'");
    		$total_branch = $query->result_array();
    		//$count =1;
    		foreach ($total_branch as $brans) {
    			$branch_code = $brans['branch_slno'];
    			$branches = $brans['branch'];
    			if ($branch == $branches){
    				$table = 'product_billdetail'.$branch_code;
    				$table1 = 'payments_details'.$branch_code;
    				$query=$this->db2->query("SELECT sum(D.multi_payment) as total_amount, D.payment_types FROM $table P inner join $table1 D on P.bill_id=D.bill_id WHERE ((P.status='closed' and D.payment_date = '$cur_date') and (D.payment_types ='NC'))");
    				$result = $query->result();
    			}
    		}	
    		return $result;
    	}
    	public function crdit_pdf_details($branch) 
    	{
    		$cur_date = date("Y-m-d");
    		$role_id = $this->session->userdata("role");
    		$query=$this->db2->query(" SELECT branch_slno as branch_slno,branch FROM outlet_deatils WHERE status='Active' and  branch='$branch'");
    		$total_branch = $query->result_array();
    		//$count =1;
    		foreach ($total_branch as $brans) {
    			$branch_code = $brans['branch_slno'];
    			$branches = $brans['branch'];
    			if ($branch == $branches){
    				$table = 'product_billdetail'.$branch_code;
    				$table1 = 'payments_details'.$branch_code;
    				$query=$this->db2->query("SELECT sum(D.multi_payment) as total_amount, D.payment_types FROM $table P inner join $table1 D on P.bill_id=D.bill_id WHERE ((P.status='closed' and D.payment_date = '$cur_date') and (D.payment_types ='CREDIT'))");
    				$result = $query->result();
    			}
    		}	
    		return $result;
    	}
    	public function vocher_amount($branch) 
    	{
    		$cur_date = date("Y-m-d");
    		//$cur_date = "2022-01-09";
    		$query=$this->db2->query("SELECT sum(vocher_amount) as vocher_amt FROM cash_vocher_entry WHERE vocher_date ='$cur_date' and branch ='$branch'");
    		$result = $query->result();
            return $result;
    	}
    	public function customer_data_collection($branch) 
    	{
    		$cur_date = date("Y-m-d");
    		$role_id = $this->session->userdata("role");
    		$query=$this->db2->query(" SELECT branch_slno as branch_slno,branch FROM outlet_deatils WHERE status='Active' and  branch='$branch'");
    		$total_branch = $query->result_array();
    		foreach ($total_branch as $brans) {
    			$branch_code = $brans['branch_slno'];
    			$branches = $brans['branch'];
    			if ($branch == $branches){
    				$table = 'product_billdetail'.$branch_code;
    				$query=$this->db2->query("SELECT A.collected,B.not_collected, C.total_collected from(select count(bill_id) as collected from $table where (((bill_date ='$cur_date' or invoice_date='$cur_date') and (cust_phone !='0')) and (branch='$branch'))) A join (select count(bill_id) as not_collected from $table where (((bill_date ='$cur_date' or invoice_date='$cur_date') and (cust_phone ='' or cust_phone ='0')) and (branch='$branch'))) B join (select count(bill_id) as total_collected from $table where ((bill_date ='$cur_date' or invoice_date='$cur_date') and (branch='$branch'))) C");
    				$result = $query->result();
    			}
    		}	
    		return $result;
    	}
    	public function entered_data_insert($paymentmode,$paymentamount)
    	{
            $branch = $this->session->userdata('branch');
    	    $close_date = date("Y-m-d");
    	    for($i = 0; $i < count($paymentmode); $i++) 
            {
            	$data = array(
        			'day_close_date'=>$close_date,
        			'payment_mode'=>$paymentmode[$i],
        			'payment_amount'=>$paymentamount[$i],
        			'branch'=>$branch
    			);
    			$this->db2->insert('day_close_details',$data);
            };
    		    
    	}
    	
    	// sms funstions 
    	
    	public function all_modes_day_new($branch) 
    	{
    	    //$cur_date = '2022-09-26';
    		$cur_date = date("Y-m-d");
    		$role_id = $this->session->userdata("role");
    		$query=$this->db2->query(" SELECT branch_slno as branch_slno,branch FROM outlet_deatils WHERE status='Active' and  branch='$branch'");
    		$total_branch = $query->result_array();
    		//$count =1;
    		foreach ($total_branch as $brans) {
    			$branch_code = $brans['branch_slno'];
    			$branches = $brans['branch'];
    			if ($branch == $branches){
    				$table = 'product_billdetail'.$branch_code;
    				$table1 = 'payments_details'.$branch_code;
    				$query=$this->db2->query("select A.inv_amt as cash_amt, B.adv_amt as cart_amt, C.paytm as ptm_amt ,D.swiggy as swiggy_amt,E.zomato as zomato_amt, F.nc_amt, G.credit_amt, sum(A.inv_amt + B.adv_amt + C.paytm + D.swiggy + E.zomato) as total_amount from (SELECT SUM(CASE WHEN ((D.payment_date ='$cur_date') and (D.payment_types='CASH' and P.status !='cancelled')) THEN D.multi_payment ELSE '0' END ) as inv_amt from $table P inner join $table1 D on P.bill_id=D.bill_id ) A join (SELECT SUM(CASE WHEN ((D.payment_date = '$cur_date') and (D.payment_types='CARD' and P.status !='cancelled')) THEN D.multi_payment ELSE '0' END ) as adv_amt from $table P inner join $table1 D on P.bill_id=D.bill_id ) B  join (SELECT SUM(CASE WHEN ((D.payment_date = '$cur_date') and (D.payment_types='PAYTM' and P.status !='cancelled')) THEN D.multi_payment ELSE '0' END ) as paytm from $table P inner join $table1 D on P.bill_id=D.bill_id ) C join (SELECT SUM(CASE WHEN ((orders_date = '$cur_date' and branch='$branch') and (channel='swiggy' and order_state !='Cancelled')) THEN order_total ELSE '0' END ) as swiggy from social_orders ) D join (SELECT SUM(CASE WHEN ((orders_date = '$cur_date' and branch='$branch') and (channel='zomato' and order_state !='Cancelled')) THEN order_total ELSE '0' END ) as zomato from social_orders ) E  join (SELECT SUM(CASE WHEN ((D.payment_date = '$cur_date') and (D.payment_types='NC' and P.status='closed')) THEN D.multi_payment ELSE '0' END ) as nc_amt from $table P inner join $table1 D on P.bill_id=D.bill_id ) F join (SELECT SUM(CASE WHEN ((D.payment_date = '$cur_date') and (D.payment_types='CREDIT' and P.status='closed')) THEN D.multi_payment ELSE '0' END ) as credit_amt from $table P inner join $table1 D on P.bill_id=D.bill_id ) G");
    				$result = $query->result();
    			}
    		}	
    		return $result;
    	}
    	
    	
    	public function all_modes_day($branch) 
    	{
    		$cur_date = date("Y-m-d");
    		$role_id = $this->session->userdata("role");
    		$query=$this->db2->query(" SELECT branch_slno as branch_slno,branch FROM outlet_deatils WHERE status='Active' and  branch='$branch'");
    		$total_branch = $query->result_array();
    		//$count =1;
    		foreach ($total_branch as $brans) {
    			$branch_code = $brans['branch_slno'];
    			$branches = $brans['branch'];
    			if ($branch == $branches){
    				$table = 'product_billdetail'.$branch_code;
    				$table1 = 'payments_details'.$branch_code;
    				$query=$this->db2->query("SELECT round(sum(D.multi_payment)) as total_amount, D.payment_types FROM $table P inner join $table1 D on P.bill_id=D.bill_id WHERE ((D.payment_date = '$cur_date' and P.status !='cancelled') and (D.payment_types !='NC' and D.payment_types !='CREDIT'))  group by D.payment_types");
    				$result = $query->result();
    			}
    		}	
    		return $result;
    	}
    	public function max_min_bills($branch) 
    	{
    		$cur_date = date("Y-m-d");
    		$role_id = $this->session->userdata("role");
    		$query=$this->db2->query(" SELECT branch_slno as branch_slno,branch FROM outlet_deatils WHERE status='Active' and  branch='$branch'");
    		$total_branch = $query->result_array();
    		//$count =1;
    		foreach ($total_branch as $brans) {
    			$branch_code = $brans['branch_slno'];
    			$branches = $brans['branch'];
    			if ($branch == $branches){
    				 $table = 'product_billdetail'.$branch_code;
    			//	$table1 = 'payments_details'.$count;
    				$query=$this->db2->query("SELECT min(in_no) as min, max(in_no) as max from $table where  invoice_date ='$cur_date'");
    				$result = $query->result();
    			}
    		}	
    		return $result;
    	}
    	public function othersmodedetails($branch) 
    	{
    	    $cur_date = date("Y-m-d");
    		$role_id = $this->session->userdata("role");
    		$query=$this->db2->query(" SELECT branch_slno as branch_slno,branch FROM outlet_deatils WHERE status='Active' and  branch='$branch'");
    		$total_branch = $query->result_array();
    		//$count =1;
    		foreach ($total_branch as $brans) {
    			$branch_code = $brans['branch_slno'];
    			$branches = $brans['branch'];
    			if ($branch == $branches){
    				$table = 'product_billdetail'.$branch_code;
    				$table1 = 'payments_details'.$branch_code;
    				$query=$this->db2->query("SELECT round(sum(D.multi_payment)) as total_amount, D.payment_types FROM $table P inner join $table1 D on P.bill_id=D.bill_id WHERE ((D.payment_date = '$cur_date' and P.status !='cancelled') and (D.payment_types ='NC' or D.payment_types ='CREDIT'))  group by D.payment_types");
    				$result = $query->result();
    			}
    		}	
    		return $result;
    	}
    	public function vocher_sales_sms($branch) 
    	{
    		$cur_date = date("Y-m-d");
    		//$cur_date = "2022-01-09";
    		$query=$this->db2->query("SELECT SUM(CASE WHEN vocher_date ='$cur_date' and branch='$branch' THEN vocher_amount ELSE '0' END ) as vocher_amt from cash_vocher_entry");
    		$result = $query->result();
            return $result;
    	}
    	// urbanpiper sales details 
    	public function get_urbanpipersales($branch) 
    	{
    		$cur_date = date("Y-m-d");
    		$query=$this->db2->query("SELECT UPPER(channel) as payment_types, round(sum(order_total)) as total_amount FROM social_orders WHERE ((orders_date ='$cur_date') and (branch='$branch' and order_state ='Success')) GROUP BY channel");
    		$result = $query->result();
    		return $result;
    	}
    	
//     	// urbanpiper sales details 
// 	public function get_urbanpipersales($branch) 
// 	{
// 		$cur_date = date("Y-m-d");
// 		$query=$this->db2->query("SELECT UPPER(channel) as payment_types, round(sum(order_total)) as total_amount FROM social_orders WHERE ((orders_date ='$cur_date') and (branch='$branch' and next_state !='Cancelled')) GROUP BY channel");
// 		$result = $query->result();
// 		return $result;
// 	}
	}
?>