<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Sales_model extends CI_Model {

	//Datatable start
	var $table = 'db_sales as a';
	var $column_order = array( 
							'a.id',
							'a.sales_date',
							'a.sales_code',
							'a.reference_no',
							'b.customer_name',
							'a.grand_total',
							'a.paid_amount',
							'a.payment_status',
							'a.created_by',
							'a.return_bit',
							'a.pos',
							'a.store_id',
							'a.quotation_id',
							'a.due_date',
							'c.code',
							'a.transaction_currency_id',
							'a.exchange_rate',
							'a.base_currency_total',
							); //set column field database for datatable orderable
	var $column_search = array( 
							'a.id',
							'a.sales_date',
							'a.sales_code',
							'a.reference_no',
							'b.customer_name',
							'a.grand_total',
							'a.paid_amount',
							'a.payment_status',
							'a.created_by',
							'a.return_bit',
							'a.pos',
							'a.store_id',
							'a.due_date',
							'c.code',
							);//set column field database for datatable searchable 
	var $order = array('a.id' => 'desc'); // default order  

	public function __construct()
	{
		parent::__construct();
		$CI =& get_instance();
	}

	private function _get_datatables_query()
	{
		$privileged_warehouses = get_privileged_warehouses_ids();

		$this->db->select($this->column_order);
		$this->db->from($this->table);
		$this->db->join('db_customers as b','b.id=a.customer_id','left');
		$this->db->join('db_customer_coupons as c','c.id=a.coupon_id','left');
		
		/*If warehouse selected*/
		$warehouse_id = $this->input->post('warehouse_id');
		$customer_id = $this->input->post('customer_id');

		if(!empty($warehouse_id)){
			//$this->db->join('db_warehouse as w','w.id='.$warehouse_id,'left');
			$this->db->where('a.warehouse_id',$warehouse_id);
		}
		else if(!is_admin() && !is_store_admin()){
     		//Find the previllaged wareshouses to the user
     		 //$this->db->join('db_warehouse as w','w.id in ('.$privileged_warehouses.')','left');
     		 $this->db->where("a.warehouse_id in ($privileged_warehouses)");
     	}

		if(!empty($customer_id)){
			$this->db->where('a.customer_id',$customer_id);
		}
		//if(!is_admin()){
	      $this->db->where("a.store_id",get_current_store_id());
	    //}
	      if(!is_admin()){
	      	if($this->session->userdata('role_id')!='2'){
	      		if(!permissions('show_all_users_sales_invoices')){
	      			$this->db->where("upper(a.created_by)",strtoupper($this->session->userdata('inv_username')));
	      		}
	      	}
	      }
	     $sales_from_date = $this->input->post('sales_from_date');
	     $sales_from_date = system_fromatted_date($sales_from_date);
	     $sales_to_date = $this->input->post('sales_to_date');
	     $sales_to_date = system_fromatted_date($sales_to_date);
	     $users = $this->input->post('users');
	     if($users && !empty($users)){
	     	$this->db->where("upper(a.created_by)",strtoupper($users));
	     }
	     if($sales_from_date!='1970-01-01'){
	     	$this->db->where("a.sales_date>=",$sales_from_date);
	     }
	     if($sales_to_date!='1970-01-01'){
	     	$this->db->where("a.sales_date<=",$sales_to_date);
	     }
	   // echo $this->db->get_compiled_select();exit();
		$i = 0;
	
		foreach ($this->column_search as $item) // loop column 
		{
			if($_POST['search']['value']) // if datatable send POST for search
			{
				
				

				if($i===0) // first loop
				{
					$this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.

					$this->db->like($item, $_POST['search']['value']);

				}
				else
				{
					$this->db->or_like($item, $_POST['search']['value']);
				}

				


				if(count($this->column_search) - 1 == $i) //last loop
					$this->db->group_end(); //close bracket
			}
			$i++;
		}
		
		if(isset($_POST['order'])) // here order processing
		{
			$this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
		} 
		else if(isset($this->order))
		{
			$order = $this->order;
			$this->db->order_by(key($order), $order[key($order)]);
		}
	}

	function get_datatables()
	{
		$this->_get_datatables_query();
		if($_POST['length'] != -1)
		$this->db->limit($_POST['length'], $_POST['start']);
		$query = $this->db->get();
		return $query->result();
	}

	function count_filtered()
	{
		$this->_get_datatables_query();
		$query = $this->db->get();
		return $query->num_rows();
	}

	public function count_all()
	{
		$this->db->where("store_id",get_current_store_id());
		$this->db->from($this->table);
		return $this->db->count_all_results();
	}
	//Datatable end

	public function xss_html_filter($input){
		return $this->security->xss_clean(html_escape($input));
	}

	//Save Sales
	public function verify_save_and_update(){
		//Filtering XSS and html escape from user inputs 
		extract($this->xss_html_filter(array_merge($this->data,$_POST,$_GET)));
		//echo "<pre>";print_r($this->xss_html_filter(array_merge($this->data,$_POST,$_GET)));exit();
		
		//varify max sales usage of the package subscription
		try {
		validate_package_offers('max_invoices','db_sales');
		} catch (Exception $e) {
			log_message('error', 'Package validation error: ' . $e->getMessage());
			return "Package validation failed: " . $e->getMessage();
		}
		//END

		// Define constants if not already defined
		if (!defined('CUR_DATE')) {
			define('CUR_DATE', date('Y-m-d'));
		}
		if (!defined('CUR_TIME')) {
			define('CUR_TIME', date('H:i:s'));
		}
		if (!defined('CUR_USERNAME')) {
			$username = $this->session->userdata('inv_username');
			if (empty($username)) {
				log_message('error', 'Sales save error - No username in session');
				return "Session error: No username found";
			}
			define('CUR_USERNAME', $username);
		}
		if (!defined('SYSTEM_IP')) {
			define('SYSTEM_IP', $_SERVER['REMOTE_ADDR']);
		}
		if (!defined('SYSTEM_NAME')) {
			define('SYSTEM_NAME', gethostname());
		}

		// Debug logging
		log_message('debug', 'Sales save debug - Starting transaction');
		log_message('debug', 'Sales save debug - Customer ID: ' . (isset($customer_id) ? $customer_id : 'NOT SET'));
		log_message('debug', 'Sales save debug - Sales Date: ' . (isset($sales_date) ? $sales_date : 'NOT SET'));
		log_message('debug', 'Sales save debug - Store ID: ' . get_current_store_id());
		log_message('debug', 'Sales save debug - Username: ' . CUR_USERNAME);
		
		// Also output to error log for immediate debugging
		error_log('Sales save debug - Starting transaction');
		error_log('Sales save debug - Customer ID: ' . (isset($customer_id) ? $customer_id : 'NOT SET'));
		error_log('Sales save debug - Sales Date: ' . (isset($sales_date) ? $sales_date : 'NOT SET'));
		error_log('Sales save debug - Store ID: ' . get_current_store_id());
		error_log('Sales save debug - Username: ' . CUR_USERNAME);

		$this->db->trans_begin();
		$sales_date=system_fromatted_date($sales_date);

		$due_date=(!empty($due_date)) ? system_fromatted_date($due_date) : NULL;
		if($due_date=='1970-01-01'){
			$due_date = NULL;
		}
		//echo $due_date;exit;
		if($other_charges_input=='' || $other_charges_input==0){$other_charges_input=null;}
	    if($other_charges_tax_id=='' || $other_charges_tax_id==0){$other_charges_tax_id=null;}
	    if($other_charges_amt=='' || $other_charges_amt==0){$other_charges_amt=null;}
	    if($discount_to_all_input=='' || $discount_to_all_input==0){$discount_to_all_input=null;}
	    if($tot_discount_to_all_amt=='' || $tot_discount_to_all_amt==0){$tot_discount_to_all_amt=null;}
	    if($tot_round_off_amt=='' || $tot_round_off_amt==0){$tot_round_off_amt=null;}

	    $prev_item_ids = array();
	    
	    if(empty(trim($count_id))) {
	    	echo "Invoice Number Should be not be Empty!";exit;
	    }
	    else{
	    	if(!is_numeric($count_id)){
	    		echo "Invoice Number Should be Numerical!";exit;		
	    	}
	    }


	    //Get coupon details
	    $customer_coupon_id = null;
	    if(!empty($coupon_code)){
	    	$coupon_details = get_customer_coupon_details_by_coupon_code($coupon_code);
	    	
		    if($coupon_details->num_rows()>0){
		    	if($coupon_details->row()->customer_id==$customer_id){
		    		$customer_coupon_id = $coupon_details->row()->id;		
		    	}
		    }
	    }

	    //Verify Sales Code
		$this->db->where("sales_code",$init_code.$count_id);
		if($command=='update'){
			$this->db->where("id<>",$sales_id);
		}
		$this->db->from('db_sales');
		//echo $this->db->get_compiled_select();exit;
		$count = $this->db->count_all_results();
		

		if($count>0){

			$autosynch_sales_code = true;

			if($autosynch_sales_code){
				$count_id = autosynch_sales_code();
			}
			else{
				echo "Sales Code already exist";
				exit;	
			}
		}
		
	    // Get currency information for the customer
	    $transaction_currency_id = null;
	    $exchange_rate = 1.0;
	    $base_currency_total = $tot_total_amt;
	    
	    if (!empty($customer_id)) {
	        $customer_currency = get_customer_trading_currency($customer_id);
	        $base_currency = get_base_currency();
	        
	        if ($customer_currency && $customer_currency->id != $base_currency->id) {
	            $transaction_currency_id = $customer_currency->id;
	            $exchange_rate = get_exchange_rate($base_currency->id, $customer_currency->id);
	            $base_currency_total = $tot_total_amt / $exchange_rate;
	        }
	    }
	    
	    $sales_entry_init = array(
							'init_code' 				=> $init_code,
		    				'count_id' 					=> $count_id,
		    				'sales_code' 				=> $init_code.$count_id,//get_init_code('sales'),
		    				/*Coupon disocunt amt*/
		    				'coupon_id' 				=> $customer_coupon_id,
		    				'coupon_amt' 				=> $coupon_discount_amt,
		    				'invoice_terms' 				=> trim($invoice_terms),
		    				'transaction_currency_id' 	=> $transaction_currency_id,
		    				'exchange_rate' 			=> $exchange_rate,
		    				'base_currency_total' 		=> $base_currency_total,
	    					);

	    if($command=='save'){//Create sales code unique if first time entry

			$this->db->query("ALTER TABLE db_sales AUTO_INCREMENT = 1");
			
			// Debug logging
			log_message('debug', 'Sales save debug - About to insert sales record');
			log_message('debug', 'Sales save debug - Grand total: ' . (isset($tot_total_amt) ? $tot_total_amt : 'NOT SET'));
			
		    $sales_entry = array(
		    				
		    				//'count_id' 					=> get_count_id('db_sales'),  
		    				'reference_no' 				=> $reference_no, 
		    				'sales_date' 				=> $sales_date,
		    				'due_date' 					=> $due_date,
		    				'sales_status' 				=> $sales_status,
		    				'customer_id' 				=> $customer_id,
		    				/*'warehouse_id' 				=> $warehouse_id,*/
		    				/*Other Charges*/
		    				'other_charges_input' 		=> $other_charges_input,
		    				'other_charges_tax_id' 		=> $other_charges_tax_id,
		    				'other_charges_amt' 		=> $other_charges_amt,
		    				/*Discount*/
		    				'discount_to_all_input' 	=> $discount_to_all_input,
		    				'discount_to_all_type' 		=> $discount_to_all_type,
		    				'tot_discount_to_all_amt' 	=> $tot_discount_to_all_amt,
		    				
		    				/*Subtotal & Total */
		    				'subtotal' 					=> $tot_subtotal_amt,
		    				'round_off' 				=> $tot_round_off_amt,
		    				'grand_total' 				=> $tot_total_amt,
		    				'sales_note' 				=> $sales_note,
		    				/*System Info*/
		    				'created_date' 				=> $CUR_DATE,
		    				'created_time' 				=> $CUR_TIME,
		    				'created_by' 				=> $CUR_USERNAME,
		    				'system_ip' 				=> $SYSTEM_IP,
		    				'system_name' 				=> $SYSTEM_NAME,
		    				'status' 					=> 1,
		    			);
		    if(isset($quotation_id)){
				$sales_entry['quotation_id'] = $quotation_id;
			}
		    $sales_entry['store_id']=(store_module() && is_admin()) ? $store_id : get_current_store_id();  	
		    $sales_entry['warehouse_id']=(warehouse_module() && warehouse_count()>1) ? $warehouse_id : get_store_warehouse_id();

		   // print_r($sales_entry);exit;
		   
		   // Debug logging before insert
		   log_message('debug', 'Sales save debug - About to insert into db_sales');
		   log_message('debug', 'Sales save debug - Sales entry data: ' . json_encode(array_merge($sales_entry,$sales_entry_init)));
		   
			$q1 = $this->db->insert('db_sales', array_merge($sales_entry,$sales_entry_init));
			
			// Debug logging after insert
			if (!$q1) {
				log_message('error', 'Sales save error - Failed to insert into db_sales: ' . $this->db->last_query());
				log_message('error', 'Sales save error - Database error: ' . $this->db->error()['message']);
			} else {
				log_message('debug', 'Sales save debug - Successfully inserted into db_sales');
			}
			
			$sales_id = $this->db->insert_id();
			//SET QUOTATION STATUS
			if(isset($quotation_id)){
				$q11 = $this->db->set("sales_status",'Converted')->where("id",$quotation_id)->update("db_quotation");
			    	if(!$q11){
			    		return false;
			    	}
			}

		}
		else if($command=='update'){	
			$sales_entry = array(
		    				//'sales_code' 				=> $sales_code, 
		    				'reference_no' 				=> $reference_no, 
		    				'sales_date' 			=> $sales_date,
		    				'due_date' 				=> $due_date,
		    				'sales_status' 			=> $sales_status,
		    				'customer_id' 				=> $customer_id,
		    				/*'warehouse_id' 				=> $warehouse_id,*/
		    				/*Other Charges*/
		    				'other_charges_input' 		=> $other_charges_input,
		    				'other_charges_tax_id' 		=> $other_charges_tax_id,
		    				'other_charges_amt' 		=> $other_charges_amt,
		    				/*Discount*/
		    				'discount_to_all_input' 	=> $discount_to_all_input,
		    				'discount_to_all_type' 		=> $discount_to_all_type,
		    				'tot_discount_to_all_amt' 	=> $tot_discount_to_all_amt,
		    				/*Subtotal & Total */
		    				'subtotal' 					=> $tot_subtotal_amt,
		    				'round_off' 				=> $tot_round_off_amt,
		    				'grand_total' 				=> $tot_total_amt,
		    				'sales_note' 			=> $sales_note,
		    			);
			//print_r($sales_entry);exit;
			$sales_entry['store_id']=(store_module() && is_admin()) ? $store_id : get_current_store_id();  	
			$sales_entry['warehouse_id']=(warehouse_module() && warehouse_count()>1) ? $warehouse_id : get_store_warehouse_id();
			$q1 = $this->db->where('id',$sales_id)->update('db_sales', array_merge($sales_entry,$sales_entry_init));

			##############################################START
			//FIND THE PREVIOUSE ITEM LIST ID'S
			$prev_item_ids = $this->db->select("item_id")->from("db_salesitems")->where("sales_id",$sales_id)->get()->result_array();
			##############################################END

			$q11=$this->db->query("delete from db_salesitems where sales_id='$sales_id'");
			if(!$q11){
				return "failed";
			}
		}
		//end

		
		//Import post data from form
		for($i=1;$i<=$rowcount;$i++){
		
			if(isset($_REQUEST['tr_item_id_'.$i]) && !empty($_REQUEST['tr_item_id_'.$i])){

				$item_id 			=$this->xss_html_filter(trim($_REQUEST['tr_item_id_'.$i]));
				$sales_qty			=$this->xss_html_filter(trim($_REQUEST['td_data_'.$i.'_3']));
				$price_per_unit 	=$this->xss_html_filter(trim($_REQUEST['td_data_'.$i.'_4']));
				$tax_id 			=$this->xss_html_filter(trim($_REQUEST['tr_tax_id_'.$i]));
				$tax_amt 			=$this->xss_html_filter(trim($_REQUEST['td_data_'.$i.'_11']));
				$unit_total_cost	=$this->xss_html_filter(trim($_REQUEST['td_data_'.$i.'_10']));
				//$discount_input	=$this->xss_html_filter(trim($_REQUEST['td_data_'.$i.'_8']));
				$total_cost			=$this->xss_html_filter(trim($_REQUEST['td_data_'.$i.'_9']));
				$tax_type			=$this->xss_html_filter(trim($_REQUEST['tr_tax_type_'.$i]));
				$unit_tax			=$this->xss_html_filter(trim($_REQUEST['tr_tax_value_'.$i]));
				$description		=$this->xss_html_filter(trim($_REQUEST['description_'.$i]));

                //$discount_input  =(empty($discount_input)) ? 0 : $discount_input;
				//$discount_amt 		=($sales_qty * $unit_total_cost)*$discount_input/100;


				$discount_type 		=$this->xss_html_filter(trim($_REQUEST['item_discount_type_'.$i]));
				$discount_input 	=$this->xss_html_filter(trim($_REQUEST['item_discount_input_'.$i]));
				$discount_amt	    =$this->xss_html_filter(trim($_REQUEST['td_data_'.$i.'_8']));//Amount

				$discount_amt_per_unit = $discount_amt/$sales_qty;
				if($tax_type=='Exclusive'){
					$single_unit_total_cost = $price_per_unit + ($unit_tax * $price_per_unit / 100);
				}
				else{//Inclusive
					$single_unit_total_cost =$price_per_unit;
				}
				$single_unit_total_cost -=$discount_amt_per_unit;


				if($tax_id=='' || $tax_id==0){$tax_id=null;}
				if($tax_amt=='' || $tax_amt==0){$tax_amt=null;}
				if($discount_input=='' || $discount_input==0){$discount_input=null;}
				//if($unit_total_cost=='' || $unit_total_cost==0){$unit_total_cost=null;}
				if($total_cost=='' || $total_cost==0){$total_cost=null;}
				
				

				//For Update operation only
				if($command=='update' && !update_warehouse_items($item_id)){
					return "failed";
				}
				//end
				
				$item_details = get_item_details($item_id);
				$item_name = $item_details->item_name;
				$service_bit = $item_details->service_bit;
				$purchase_price = $item_details->price;
				$current_stock_of_item = total_available_qty_items_of_warehouse($warehouse_id,null,$item_id);
				if($current_stock_of_item<$sales_qty && $service_bit==0){
					return "insufficient_stock:" . $item_name . " has only " . $current_stock_of_item . " in Stock!!";
				}
				
				// Batch tracking details
				$batch_number = isset($_REQUEST['batch_number_'.$i]) ? $this->xss_html_filter(trim($_REQUEST['batch_number_'.$i])) : '';
				$manufacturing_date = isset($_REQUEST['manufacturing_date_'.$i]) ? $this->xss_html_filter(trim($_REQUEST['manufacturing_date_'.$i])) : '';
				$expiry_date = isset($_REQUEST['expiry_date_'.$i]) ? $this->xss_html_filter(trim($_REQUEST['expiry_date_'.$i])) : '';

				// Check if item has batch tracking enabled
				$item_info = $this->db->select('batch_tracking_enabled')->from('db_items')->where('id', $item_id)->get()->row();
				
				if ($item_info && $item_info->batch_tracking_enabled == 1) {
					// Validate batch selection for batch-tracked items
					if (empty($batch_number)) {
						return "batch_required:" . $item_name . " has batch tracking enabled. You must select a batch before saving.";
					}
					
					// Handle FIFO batch allocation for batch-tracked items
					$result = $this->handle_fifo_batch_allocation($sales_id, $sales_status, $item_id, $description, $sales_qty, 
						$price_per_unit, $tax_type, $tax_id, $tax_amt, $discount_input, $discount_amt, 
						$discount_type, $single_unit_total_cost, $total_cost, $purchase_price, $store_id);
					
					if (!$result) {
						log_message('error', 'FIFO batch allocation failed for item ' . $item_id);
						return false;
					}
					
					// Skip the regular sales entry since FIFO handled it
					continue;
				} else {
					// Regular item without batch tracking
					$salesitems_entry = array(
			    				'sales_id' 			=> $sales_id, 
			    				'sales_status'		=> $sales_status, 
			    				'item_id' 			=> $item_id, 
			    				'description' 		=> $description, 
			    				'sales_qty' 		=> $sales_qty,
			    				'price_per_unit' 	=> $price_per_unit,
			    				'tax_type' 			=> $tax_type,
			    				'tax_id' 			=> $tax_id,
			    				'tax_amt' 			=> $tax_amt,
			    				'discount_input' 	=> $discount_input,
			    				'discount_amt' 		=> $discount_amt,
			    				'discount_type' 	=> $discount_type,
			    				'unit_total_cost' 	=> $single_unit_total_cost,
			    				'total_cost' 		=> $total_cost,
			    				'purchase_price' 	=> $purchase_price,
			    				'status'	 		=> 1,
			    				'seller_points'		=> get_seller_points($item_id) * $sales_qty,
			    				// Batch tracking fields (empty for non-batch items)
			    				'batch_number' 		=> '',
			    				'manufacturing_date' => '',
			    				'expiry_date' 		=> '',
			    			);
					
					$salesitems_entry['store_id']=(store_module() && is_admin()) ? $store_id : get_current_store_id();  	
					$q2 = $this->db->insert('db_salesitems', $salesitems_entry);
				}
				
				//UPDATE itemS QUANTITY IN itemS TABLE
				$this->load->model('pos_model');				
				$q6=$this->pos_model->update_items_quantity($item_id);
				if(!$q6){
					return "failed";
				}
				
			}
		
		}//for end

		if($amount=='' || $amount==0){$amount=null;}
		if($amount>0 && !empty($payment_type)){

			if($amount>$tot_total_amt){
				echo "Payble amount should not be exceeds Invoice Amount!!";exit;
			}

			/**
			 * @update
			 * Verifieng previous and current payment total with invoice amount
			*/
			if($command=='update'){
				$tot_payment = $this->db->select('coalesce(sum(payment),0) as payment')->where('sales_id',$sales_id)->get('db_salespayments')->row()->payment;
				if(($tot_payment+$amount)>$tot_total_amt){
					echo "Payble amount should not be exceeds Invoice Amount!!\nPlease check previous payments as well.";exit;
				}
			}


			//is total advance payment enabled ?
			$advance_adjusted=0;
			if(isset($allow_tot_advance)){
				$tot_advance = get_customer_details($customer_id)->tot_advance;
				if($tot_advance>0){
					if($amount==$tot_advance){
						$advance_adjusted = $amount;
					}
					else if($amount>$tot_advance){
						$advance_adjusted = $tot_advance;	
					}
					else{
						$advance_adjusted =  $amount;
					}
				}
			}
			//end 

			// Get payment currency information from form data
			$payment_currency_id = isset($_POST['payment_currency']) ? $_POST['payment_currency'] : null;
			$exchange_rate = 1.0;
			$base_currency_amount = $amount;
			
			// Check if payment currency is specified
			if (!empty($payment_currency_id)) {
				$base_currency = get_base_currency();
				if ($payment_currency_id != $base_currency->id) {
					$exchange_rate = get_exchange_rate($base_currency->id, $payment_currency_id);
					$base_currency_amount = $amount / $exchange_rate;
				}
			}

			$payment_code=get_init_code('sales_payment');
			$salespayments_entry = array(
					'payment_code' 		=> $payment_code,
		    		'count_id'	  		=> get_count_id('db_salespayments'),
					'sales_id' 			=> $sales_id, 
					'payment_date'		=> $sales_date,//Current Payment with sales entry
					'payment_type' 		=> $payment_type,
					'payment' 			=> $amount,
					'payment_currency_id' => $payment_currency_id,
					'exchange_rate' 	=> $exchange_rate,
					'base_currency_amount' => $base_currency_amount,
					'payment_note' 		=> $payment_note,
					'created_date' 		=> $CUR_DATE,
    				'created_time' 		=> $CUR_TIME,
    				'created_by' 		=> $CUR_USERNAME,
    				'system_ip' 		=> $SYSTEM_IP,
    				'system_name' 		=> $SYSTEM_NAME,
    				'status' 			=> 1,
    				'account_id' 		=> (empty($account_id)) ? null : $account_id,
    				'customer_id' 		=> $customer_id,
    				'advance_adjusted' 	=> $advance_adjusted,
    				'cheque_number' 	=> $cheque_number,
    				'cheque_period' 	=> $cheque_period,
    				'cheque_status' 	=> "Pending",
				);
			$salespayments_entry['store_id']=(store_module() && is_admin()) ? $store_id : get_current_store_id();  	
			$q3 = $this->db->insert('db_salespayments', $salespayments_entry);

			// Get the salespayments ID after successful insert
			$salespayments_id = $this->db->insert_id();

			//Set the payment to specified account
			$target_account_id = null;
			
			if(!empty($account_id)){
				// Use the selected account directly
				$target_account_id = $account_id;
			} else if (!empty($payment_currency_id)) {
				// Create currency-specific account if no specific account was selected
				$currency_account = get_or_create_currency_account('Cash', $payment_currency_id);
				if ($currency_account) {
					$target_account_id = $currency_account->id;
				}
			}
			
			if ($target_account_id) {
				//ACCOUNT INSERT
				$insert_bit = insert_account_transaction(array(
															'transaction_type'  	=> 'SALES PAYMENT',
															'reference_table_id'  	=> $salespayments_id,
															'ref_salespayments_id'  => $salespayments_id,
															'debit_account_id'  	=> null,
															'credit_account_id'  	=> $target_account_id,
															'debit_amt'  			=> 0,
															'credit_amt'  			=> $amount,
															'process'  				=> 'SAVE',
															'note'  				=> $payment_note,
															'transaction_date'  	=> $CUR_DATE,
															'payment_code'  		=> $payment_code,
															'customer_id'  			=> $customer_id,
															'supplier_id'  			=> null,
															'currency_id'  			=> $payment_currency_id,
															'exchange_rate'  		=> $exchange_rate,
													));
				if(!$insert_bit){
					return "failed";
				}
			}
			//end
			
		}
		
		
		

		$q10=$this->update_sales_payment_status($sales_id,$customer_id);
		if($q10!=1){
			return "failed";
		}
		
		
		if(!set_customer_tot_advance($customer_id)){
			return "failed";
		}
		/*$q10=$this->set_quotation_sales_status($sales_id);
		if(!$q10){
			return "failed";
		}*/
		
		//Dont save if invoice credit limit exceeds
		if(!check_credit_limit_with_invoice($customer_id,$sales_id)){
			return 'failed';
		}


		$sms_info='';
		if(isset($send_sms) && $customer_id!=1){
			if(send_sms_using_template($sales_id,1)==true){
				$sms_info = 'SMS Has been Sent!';
			}else{
				$sms_info = 'Failed to Send SMS';
			}
		}
		

		##############################################START
		//FIND THE PREVIOUSE ITEM LIST ID'S
		$curr_item_ids = $this->db->select("item_id")->from("db_salesitems")->where("sales_id",$sales_id)->get()->result_array();
		$two_array = array_merge($prev_item_ids,$curr_item_ids);

		/*Update items in all warehouses of the item*/
		$q7=update_warehouse_items($two_array);
		if(!$q7){
			return "failed";
		}
		##############################################END
		
		//Calculate Opening balance before and after invoice
		/*$q7=calculate_ob_of_customer($sales_id,$customer_id);
		if(!$q7){
			return "failed";
		}*/

		/*$this->db->set("due_date",null)->where("due_date",'1970-01-01')->or_where("due_date","0000-00-00")->update("db_sales");*/

		if ($this->db->trans_status() === FALSE) {
			log_message('error', 'Sales save error - Transaction failed, rolling back');
			$this->db->trans_rollback();
			return "failed";
		} else {
			log_message('debug', 'Sales save debug - Transaction successful, committing');
		$this->db->trans_commit();
		$this->session->set_flashdata('success', 'Success!! Record Saved Successfully! '.$sms_info);
		return "success<<<###>>>$sales_id";
		}
		
	}//verify_save_and_update() function end

	function update_sales_payment_status_by_sales_id($sales_id,$customer_id){
		$q8=$this->db->query("select COALESCE(SUM(payment),0) as payment from db_salespayments where sales_id='$sales_id'");
		$sum_of_payments=$q8->row()->payment;
		

		$payble_total=$this->db->query("select coalesce(sum(grand_total),0) as total from db_sales where id='$sales_id'")->row()->total;
		//$payble_total=$q9->row()->total;
		
		//$pending_amt=$payble_total-$sum_of_payments;

		$payment_status='';
		if($payble_total==$sum_of_payments){
			$payment_status="Paid";
		}
		else if($sum_of_payments!=0 && ($sum_of_payments<$payble_total)){
			$payment_status="Partial";
		}
		else if($sum_of_payments==0){
			$payment_status="Unpaid";
		}


		$q7=$this->db->query("update db_sales set 
							payment_status='$payment_status',
							paid_amount=$sum_of_payments 
							where id='$sales_id'");
		//$customer_id =$this->db->query("select customer_id from db_sales where id=$sales_id")->row()->customer_id;
		$q12 = $this->db->query("update db_customers set sales_due=(select COALESCE(SUM(grand_total),0)-COALESCE(SUM(paid_amount),0) from db_sales where customer_id='$customer_id' and sales_status='Final') where id=$customer_id");
		if(!$q7)
		{
			return false;
		}
		else{
			return true;
		}
	}


	function update_sales_payment_status($sales_id=null,$customer_id=null){
	//UPDATE PRODUCTS QUANTITY IN PRODUCTS TABLE
		if(empty($sales_id)){ //If sales ID not exist you need setup all the customers sales due
			$q11=$this->db->query("select id from db_customers");
			if($q11->num_rows()>0){
				foreach ($q11->result() as $res) {

					$q12=$this->db->query("select id from db_sales where customer_id=".$res->id);
					if($q12->num_rows()>0){
						foreach ($q12->result() as $res12) {
							if(!$this->update_sales_payment_status_by_sales_id($res12->id,$res->id)){
								return false;
							}
						}
					}
					else{
						$q13=$this->db->query("update db_customers set sales_due=0 where id=".$res->id);
						if(!$q13){
							return false;
						}
					}

				}
			}
			return true;
		}
		else{
					if(!$this->update_sales_payment_status_by_sales_id($sales_id,$customer_id)){
						return false;
					}
					return true;
		}
	}


	//Get sales_details
	public function get_details($id,$data){
		//Validate This sales already exist or not
		$query=$this->db->query("select * from db_sales where upper(id)=upper('$id')");
		if($query->num_rows()==0){
			show_404();exit;
		}
		else{
			$query=$query->row();
			$data['q_id']=$query->id;
			$data['item_code']=$query->item_code;
			$data['item_name']=$query->item_name;
			$data['category_name']=$query->category_name;
			$data['hsn']=$query->hsn;
			$data['unit_name']=$query->unit_name;
			$data['available_qty']=$query->available_qty;
			$data['alert_qty']=$query->alert_qty;
			$data['sales_price']=$query->sales_price;
			$data['sales_price']=$query->sales_price;
			$data['gst_percentage']=$query->gst_percentage;
			
			return $data;
		}
	}
	public function update_status($id,$status){
		
        $query1="update db_sales set status='$status' where id=$id";
        if ($this->db->simple_query($query1)){
            echo "success";
        }
        else{
            echo "failed";
        }
	}
	public function delete_sales($ids){
		// Add timeout to prevent hanging
		set_time_limit(30);
		
		// Debug logging
		log_message('debug', 'Sales delete - Starting deletion for IDs: ' . $ids);
		error_log('Sales delete - Starting deletion for IDs: ' . $ids);
		
      	$this->db->trans_begin();

      	$q12=$this->db->select("*")->where("sales_id in ($ids)")->get("db_salesreturn");
      	if($q12->num_rows()>0){
      		foreach ($q12->result() as $res12) {
      			$sales_code = $this->db->select("sales_code")->where("id",$res12->sales_id)->get("db_sales")->row()->sales_code;
      			echo "<br>Invoice Code: ".$sales_code;
      		}
      		echo "<br>Already Raised Returns, Please Delete Before Deleting Original Invoice";
      		exit;
      	}

		//ACCOUNT RESET - Get affected accounts and sales payment IDs before deleting
		// First, get the sales payment IDs for these sales
		$sales_payment_ids = $this->db->select("id")
									->where("sales_id in ($ids)")
									->get("db_salespayments")
									->result_array();
		$sales_payment_ids_list = array_column($sales_payment_ids, 'id');
		$sales_payment_ids_string = implode(',', $sales_payment_ids_list);
		
		log_message('debug', 'Sales delete - Found sales payment IDs: ' . $sales_payment_ids_string);
		error_log('Sales delete - Found sales payment IDs: ' . $sales_payment_ids_string);
		
		// Only proceed with account transaction queries if we have sales payment IDs
		$all_account_transactions = null;
		if (!empty($sales_payment_ids_string)) {
			// Now get all account transactions related to these sales payments
			$all_account_transactions = $this->db->select("id, debit_account_id, credit_account_id, ref_salespayments_id")
										->where("ref_salespayments_id in ($sales_payment_ids_string)")
										->get("ac_transactions");
			
			log_message('debug', 'Sales delete - Found ' . $all_account_transactions->num_rows() . ' account transactions with ref_salespayments_id in (' . $sales_payment_ids_string . ')');
			error_log('Sales delete - Found ' . $all_account_transactions->num_rows() . ' account transactions with ref_salespayments_id in (' . $sales_payment_ids_string . ')');
		} else {
			log_message('debug', 'Sales delete - No sales payment IDs found, skipping account transaction queries');
			error_log('Sales delete - No sales payment IDs found, skipping account transaction queries');
		}
		
		// Check if there are any account transactions that would be affected
		if ($all_account_transactions && $all_account_transactions->num_rows() > 0) {
			log_message('debug', 'Sales delete - Account transactions found, proceeding with deletion');
			error_log('Sales delete - Account transactions found, proceeding with deletion');
		} else {
			log_message('debug', 'Sales delete - No account transactions found for these sales');
			error_log('Sales delete - No account transactions found for these sales');
		}
		
		// Check what columns exist in ac_transactions table
		$table_columns = $this->db->list_fields('ac_transactions');
		log_message('debug', 'Sales delete - ac_transactions columns: ' . implode(', ', $table_columns));
		error_log('Sales delete - ac_transactions columns: ' . implode(', ', $table_columns));
		
		// Get unique accounts for balance update using sales payment IDs (only if we have sales payment IDs)
		$reset_accounts = null;
		if (!empty($sales_payment_ids_string)) {
			$reset_accounts = $this->db->select("debit_account_id,credit_account_id")
										->where("ref_salespayments_id in ($sales_payment_ids_string)")
										->group_by("debit_account_id,credit_account_id")
										->get("ac_transactions");
			
			// Debug logging
			log_message('debug', 'Sales delete - Found ' . $reset_accounts->num_rows() . ' account transactions to delete');
			error_log('Sales delete - Found ' . $reset_accounts->num_rows() . ' account transactions to delete');
		} else {
			log_message('debug', 'Sales delete - No sales payment IDs, skipping account reset query');
			error_log('Sales delete - No sales payment IDs, skipping account reset query');
		}
		
		// Sales payment IDs already retrieved above
		//ACCOUNT RESET END

		##############################################START
		//FIND THE PREVIOUSE ITEM LIST ID'S
		$prev_item_ids = $this->db->select("item_id")->from("db_salesitems")->where("sales_id in ($ids)")->get()->result_array();
		
		// Get batch details before deleting salesitems
		$batch_items = $this->db->select("item_id, batch_number, sales_qty")->from("db_salesitems")->where("sales_id in ($ids)")->where("batch_number IS NOT NULL")->where("batch_number != ''")->get()->result();
		##############################################END
		
		//RESET QUOTATION RESET
		if(!$this->reset_quotation_sales_status_to_null($ids)){
			return "failed";
		}

		//find customer list group by
		$this->db->select("customer_id,id as sales_id");
		$this->db->where("id in ($ids)");
		$this->db->where("store_id",get_current_store_id());
		$this->db->group_by("customer_id");
		$customer_records=$this->db->get("db_sales");
		//end

		#----------------------------------
		$this->db->where("id in ($ids)");
		//if not admin
		if(!is_admin()){
			$this->db->where("store_id",get_current_store_id());
		}

		$q3=$this->db->delete("db_sales");
		#----------------------------------
		
		//DELETE SALES PAYMENTS
		$this->db->where("sales_id in ($ids)");
		$q4 = $this->db->delete("db_salespayments");
		if(!$q4){
			$this->db->trans_rollback();
			return "failed";
		}
		//DELETE SALES PAYMENTS END
		
		//DELETE ACCOUNT TRANSACTIONS - Fixed approach
		log_message('debug', 'Sales delete - Deleting account transactions');
		error_log('Sales delete - Deleting account transactions');
		
		// Delete account transactions using the sales payment IDs we retrieved earlier
		if (!empty($sales_payment_ids_string)) {
			$this->db->where("ref_salespayments_id in ($sales_payment_ids_string)");
			$this->db->where("transaction_type", "SALES PAYMENT");
			$q5 = $this->db->delete("ac_transactions");
			
			$deleted_count = $this->db->affected_rows();
			log_message('debug', 'Sales delete - Deleted ' . $deleted_count . ' account transactions using sales payment IDs: ' . $sales_payment_ids_string);
			error_log('Sales delete - Deleted ' . $deleted_count . ' account transactions using sales payment IDs: ' . $sales_payment_ids_string);
		} else {
			log_message('debug', 'Sales delete - No sales payment IDs found for account transaction deletion');
			error_log('Sales delete - No sales payment IDs found for account transaction deletion');
		}
		//DELETE ACCOUNT TRANSACTIONS END
		
		$item_ids_first = get_in_comma_delimited($prev_item_ids);
		
		// Restore batch stock for opening stock batches
		if(!empty($batch_items)){
			foreach ($batch_items as $batch_item) {
				$this->db->where('item_id', $batch_item->item_id);
				$this->db->where('batch_number', $batch_item->batch_number);
				$this->db->set('remaining_quantity', 'remaining_quantity + ' . $batch_item->sales_qty, FALSE);
				$this->db->update('db_item_batches');
				
				log_message('debug', "Sales delete: Restored batch {$batch_item->batch_number} for item {$batch_item->item_id} by +{$batch_item->sales_qty}");
			}
		}



		$q6=$this->db->query("select id from db_items where id in ('".$item_ids_first."')");
		if($q6->num_rows()>0){			
			$this->load->model('pos_model');
			foreach ($q6->result() as $res6) {
				$q6=$this->pos_model->update_items_quantity($res6->id);
				if(!$q6){
					return "failed";
				}
			}
		}
		

    	if($customer_records->num_rows()>0){
        	foreach ($customer_records->result() as $res) {
        		if(!$this->update_sales_payment_status($res->sales_id,$res->customer_id)){
		        	return 'failed';
		        }

        	}        		
        }

		
		##############################################START
		/*Update items in all warehouses of the item*/
		$q7=update_warehouse_items($prev_item_ids);
		if(!$q7){
			return "failed";
		}
		##############################################END
		
		//ACCOUNT RESET - Simplified balance update
		log_message('debug', 'Sales delete - Updating account balances');
		error_log('Sales delete - Updating account balances');
		
		// Get all accounts that had transactions (from the original query)
        if($reset_accounts && $reset_accounts->num_rows()>0){
        	foreach ($reset_accounts->result() as $res1) {
				if($res1->debit_account_id && !update_account_balance($res1->debit_account_id)){
					log_message('error', 'Sales delete - Failed to update debit account balance: ' . $res1->debit_account_id);
					error_log('Sales delete - Failed to update debit account balance: ' . $res1->debit_account_id);
					$this->db->trans_rollback();
					return 'failed';
				}

				if($res1->credit_account_id && !update_account_balance($res1->credit_account_id)){
					log_message('error', 'Sales delete - Failed to update credit account balance: ' . $res1->credit_account_id);
					error_log('Sales delete - Failed to update credit account balance: ' . $res1->credit_account_id);
					$this->db->trans_rollback();
					return 'failed';
				}
        	}
        } else {
        	log_message('debug', 'Sales delete - No account transactions to reset balances for');
        	error_log('Sales delete - No account transactions to reset balances for');
        }
        //ACCOUNT RESET END

        if($customer_records->num_rows()>0){
        	foreach ($customer_records->result() as $customer_id) {
        		if(!set_customer_tot_advance($customer_id->customer_id)){
		        	return 'failed';
		        }
        	}
        		
        }

		// Check transaction status before committing
		if ($this->db->trans_status() === FALSE) {
			log_message('error', 'Sales delete - Transaction failed, rolling back');
			error_log('Sales delete - Transaction failed, rolling back');
			$this->db->trans_rollback();
			return "failed";
		} else {
			log_message('debug', 'Sales delete - Transaction successful, committing');
			error_log('Sales delete - Transaction successful, committing');
		$this->db->trans_commit();
		return "success";
		}
	}
	public function search_item($q){
		$json_array=array();
        $query1="select id,item_name from db_items where (upper(item_name) like upper('%$q%') or upper(item_code) like upper('%$q%'))";

        $q1=$this->db->query($query1);
        if($q1->num_rows()>0){
            foreach ($q1->result() as $value) {
            	$json_array[]=['id'=>(int)$value->id, 'text'=>$value->item_name];
            }
        }
        return json_encode($json_array);
	}
	
	public function find_item_details($id){
		$json_array=array();
		$customer_id = $this->input->post('customer_id');
		$price_type = 'retail'; // Default to retail
		
		// Debug logging
		log_message('debug', 'Sales find_item_details - Item ID: ' . $id . ', Customer ID: ' . $customer_id);
		
		// Get customer type if customer_id is provided
		if (!empty($customer_id)) {
			$customer_query = $this->db->query("SELECT customer_type FROM db_customers WHERE id = $customer_id");
			if ($customer_query->num_rows() > 0) {
				$customer = $customer_query->row();
				$price_type = $customer->customer_type ?: 'retail';
				log_message('debug', 'Customer type: ' . $price_type);
			}
		}
		
        $query1="select id,hsn,alert_qty,unit_name,sales_price,wholesale_price,retail_price,gst_percentage,available_qty from db_items where id=$id";

        $q1=$this->db->query($query1);
        if($q1->num_rows()>0){
            foreach ($q1->result() as $value) {
            	// Determine which price to use based on customer type
            	$final_price = $value->sales_price; // Default to sales_price
            	
            	log_message('debug', 'Item prices - Sales: ' . $value->sales_price . ', Wholesale: ' . $value->wholesale_price . ', Retail: ' . $value->retail_price);
            	
            	if ($price_type == 'wholesale' && !empty($value->wholesale_price) && $value->wholesale_price > 0) {
            		$final_price = $value->wholesale_price;
            		log_message('debug', 'Using wholesale price: ' . $final_price);
            	} elseif ($price_type == 'retail' && !empty($value->retail_price) && $value->retail_price > 0) {
            		$final_price = $value->retail_price;
            		log_message('debug', 'Using retail price: ' . $final_price);
            	} else {
            		log_message('debug', 'Using sales price: ' . $final_price);
            	}
            	
            	$json_array=['id'=>$value->id, 
        			 'hsn'=>$value->hsn,
        			 'alert_qty'=>$value->alert_qty,
        			 'unit_name'=>$value->unit_name,
        			 'sales_price'=>$final_price,
        			 'gst_percentage'=>$value->gst_percentage,
        			 'available_qty'=>$value->available_qty,
        			 'price_type'=>$price_type,
        			];
            }
        }
        return json_encode($json_array);
	}

	
	public function reset_quotation_sales_status_to_null($sales_ids){
			$this->db->where("id in($sales_ids)");
			$this->db->where("quotation_id!=''");
			$this->db->select("quotation_id");
			$quotation_ids = $this->db->get("db_sales");

			//if records exist
			if($quotation_ids->num_rows()>0){
				$tmpArr = array();
			    foreach ($quotation_ids->result() as $sub) {
			      $tmpArr[] = $sub->quotation_id;
			    }
			    $quotation_ids = implode(',', $tmpArr);
			    if(!empty($quotation_ids)){
			    	$q11 = $this->db->set("sales_status",null)->where("id in($quotation_ids)")->update("db_quotation");
			    	if(!$q11){
			    		return false;
			    	}
			    }
			}
			return true;
  	}


	
	/*v1.1*/
	public function inclusive($price='',$tax_per){
		return ($tax_per!=0) ? $price/(($tax_per/100)+1)/10 : $tax_per;
	}
	public function get_items_info($rowcount,$item_id){
		extract($_POST);
		
		// Debug logging
		log_message('debug', 'Sales get_items_info - Item ID: ' . $item_id . ', Customer ID from POST: ' . (isset($customer_id) ? $customer_id : 'NOT SET'));

		$res1=$this->db->select('*')->from('db_items')->where("id=$item_id")->get()->row();
		$q3=$this->db->query("select * from db_tax where id=".$res1->tax_id)->row();

		//Get Customer Price based on customer type
		$price_type = 'retail'; // Default to retail
		$customer_id = isset($customer_id) ? $customer_id : '';
		
		if (!empty($customer_id)) {
			$customer_query = $this->db->query("SELECT customer_type FROM db_customers WHERE id = $customer_id");
			if ($customer_query->num_rows() > 0) {
				$customer = $customer_query->row();
				$price_type = $customer->customer_type ?: 'retail';
				log_message('debug', 'Customer type: ' . $price_type);
			}
		}
		
		// Use appropriate price based on customer type
		log_message('debug', 'Item prices - Sales: ' . $res1->sales_price . ', Wholesale: ' . $res1->wholesale_price . ', Retail: ' . $res1->retail_price);
		
		if ($price_type == 'wholesale' && !empty($res1->wholesale_price) && $res1->wholesale_price > 0) {
			$sales_price = $res1->wholesale_price;
			log_message('debug', 'Using wholesale price: ' . $sales_price);
		} elseif ($price_type == 'retail' && !empty($res1->retail_price) && $res1->retail_price > 0) {
			$sales_price = $res1->retail_price;
			log_message('debug', 'Using retail price: ' . $sales_price);
		} else {
			$sales_price = $res1->sales_price;
			log_message('debug', 'Using sales price: ' . $sales_price);
		}
		
		$sales_price = number_format($sales_price,decimals(),'.','');

		// Currency conversion for customer's trading currency
		$converted_price = $sales_price;
		$exchange_rate = 1.0;
		$currency_symbol = '';
		$currency_code = '';
		
		if (!empty($customer_id)) {
			$customer_currency = get_customer_trading_currency($customer_id);
			$base_currency = get_base_currency();
			
			if ($customer_currency && $customer_currency->id != $base_currency->id) {
				$exchange_rate = get_exchange_rate($base_currency->id, $customer_currency->id);
				
				if ($exchange_rate != 1.0) {
					$converted_price = $sales_price * $exchange_rate;
					$converted_price = number_format($converted_price, decimals(), '.', '');
					$currency_symbol = $customer_currency->currency_symbol;
					$currency_code = $customer_currency->currency_code;
				}
			}
		}
		
		$item_available_qty = total_available_qty_items_of_warehouse($warehouse_id,null,$res1->id);// $res1->stock;
		
		$item_tax_amt = ($res1->tax_type=='Inclusive') ? calculate_inclusive($converted_price,$q3->tax) :calculate_exclusive($converted_price,$q3->tax);

		$info = array(
							'item_id' 					=> $res1->id, 
							'description' 				=> '', 
							'item_name' 				=> $res1->item_name,
							'item_available_qty' 		=> $item_available_qty,
							'item_price' 				=> $res1->price, 
							'item_sales_price' 			=> $converted_price, 
							'item_base_price' 			=> $sales_price, 
							'item_currency_symbol' 		=> $currency_symbol, 
							'item_currency_code' 		=> $currency_code, 
							'item_exchange_rate' 		=> $exchange_rate, 
							'item_tax_name' 			=> $q3->tax_name, 
							'item_sales_qty' 			=> ($item_available_qty<1 && $res1->service_bit!=1) ? $item_available_qty : number_format(1,2), 
							'item_tax_id' 				=> $q3->id, 
							'item_tax' 					=> $q3->tax, 
							'item_tax_type' 			=> $res1->tax_type, 
							'item_tax_amt' 				=> $item_tax_amt, 
							'item_discount' 			=> 0, 
							'item_discount_type' 		=> $res1->discount_type, 
							'item_discount_input' 		=> $res1->discount, 
							'service_bit' 				=> $res1->service_bit, 
							// Batch tracking information
							'batch_tracking_enabled' 	=> isset($res1->batch_tracking_enabled) ? $res1->batch_tracking_enabled : 0,
							'tracking_type' 				=> isset($res1->tracking_type) ? $res1->tracking_type : 'none',
							'expiry_enabled' 			=> isset($res1->expiry_enabled) ? $res1->expiry_enabled : 0,
							// No batch selected initially
							'batch_number' 				=> '',
							'manufacturing_date' 		=> '',
							'expiry_date' 				=> '',
						
						);

		$this->return_row_with_data($rowcount,$info);
	}
	/* For Quotation Items List Retrieve*/
	public function return_quotation_list($quotation_id){
		$q1=$this->db->select('*')->from('db_quotationitems')->where("quotation_id=$quotation_id")->get();
		$rowcount =1;
		foreach ($q1->result() as $res1) {
			$res2=$this->db->query("select * from db_items where id=".$res1->item_id)->row();
			$q3=$this->db->query("select * from db_tax where id=".$res1->tax_id)->row();
			
			$info = array(
							'item_id' 					=> $res1->item_id, 
							'description' 				=> $res1->description, 
							'item_name' 				=> $res2->item_name,
							'item_available_qty' 		=> $res2->stock,
							'item_price' 				=> $res2->price, 
							'item_sales_price' 			=> $res1->price_per_unit, 
							'item_tax_name' 			=> $q3->tax_name, 
							'item_sales_qty' 			=> $res1->quotation_qty, 
							'item_tax_id' 				=> $res1->tax_id, 
							'item_tax' 					=> $q3->tax, 
							'item_tax_type' 			=> $res1->tax_type, 
							'item_tax_amt' 				=> $res1->tax_amt, 
							'item_discount' 			=> $res1->discount_input, 
							'item_discount_type' 		=> $res1->discount_type, 
							'item_discount_input' 		=> $res1->discount_input, 
							'service_bit' 				=> 1, 
						);

			$result = $this->return_row_with_data($rowcount++,$info);
		}
		return $result;
	}
	/* For Purchase Items List Retrieve*/
	public function return_sales_list($sales_id){
		$q1=$this->db->select('*')->from('db_salesitems')->where("sales_id=$sales_id")->get();
		$rowcount =1;
		foreach ($q1->result() as $res1) {
			$res2=$this->db->query("select * from db_items where id=".$res1->item_id)->row();
			$q3=$this->db->query("select * from db_tax where id=".$res1->tax_id)->row();
			
			$info = array(
							'item_id' 					=> $res1->item_id, 
							'description' 				=> $res1->description, 
							'item_name' 				=> $res2->item_name,
							'item_available_qty' 		=> ($res2->stock + $res1->sales_qty),
							'item_price' 				=> $res2->price, 
							'item_sales_price' 			=> $res1->price_per_unit, 
							'item_tax_name' 			=> $q3->tax_name, 
							'item_sales_qty' 			=> $res1->sales_qty, 
							'item_tax_id' 				=> $res1->tax_id, 
							'item_tax' 					=> $q3->tax, 
							'item_tax_type' 			=> $res1->tax_type, 
							'item_tax_amt' 				=> $res1->tax_amt, 
							'item_discount' 			=> $res1->discount_input, 
							'item_discount_type' 		=> $res1->discount_type, 
							'item_discount_input' 		=> $res1->discount_input, 
							'service_bit' 				=> $res2->service_bit, 
							// Batch tracking information from items table
							'batch_tracking_enabled' 	=> isset($res2->batch_tracking_enabled) ? $res2->batch_tracking_enabled : 0,
							'tracking_type' 				=> isset($res2->tracking_type) ? $res2->tracking_type : 'none',
							'expiry_enabled' 			=> isset($res2->expiry_enabled) ? $res2->expiry_enabled : 0,
							// Batch details from sales items table
							'batch_number' 				=> isset($res1->batch_number) ? $res1->batch_number : '',
							'manufacturing_date' 		=> isset($res1->manufacturing_date) ? $res1->manufacturing_date : '',
							'expiry_date' 				=> isset($res1->expiry_date) ? $res1->expiry_date : '',
						);

			$result = $this->return_row_with_data($rowcount++,$info);
		}
		return $result;
	}

	public function return_row_with_data($rowcount,$info){
		extract($info);
		$item_amount = ($item_sales_price * $item_sales_qty) + $item_tax_amt;
		?>
            <tr id="row_<?=$rowcount;?>" data-row='<?=$rowcount;?>'>
               <!-- Hidden fields for JavaScript access -->
               <input type="hidden" id="tr_item_id_<?=$rowcount;?>" value="<?=$item_id;?>">
               <input type="hidden" id="service_bit_<?=$rowcount;?>" value="<?=$service_bit;?>">
               <input type="hidden" id="tr_available_qty_<?=$rowcount;?>_13" value="<?=$item_available_qty;?>">
               <!-- Batch tracking hidden fields -->
               <input type="hidden" id="batch_tracking_enabled_<?=$rowcount;?>" name="batch_tracking_enabled_<?=$rowcount;?>" value="<?=isset($batch_tracking_enabled) ? $batch_tracking_enabled : 0;?>">
               <input type="hidden" id="tracking_type_<?=$rowcount;?>" name="tracking_type_<?=$rowcount;?>" value="<?=isset($tracking_type) ? $tracking_type : 'none';?>">
               <input type="hidden" id="expiry_enabled_<?=$rowcount;?>" name="expiry_enabled_<?=$rowcount;?>" value="<?=isset($expiry_enabled) ? $expiry_enabled : 0;?>">
               <!-- Selected batch details -->
               <input type="hidden" id="batch_number_<?=$rowcount;?>" name="batch_number_<?=$rowcount;?>" value="<?=isset($batch_number) ? $batch_number : '';?>">
               <input type="hidden" id="manufacturing_date_<?=$rowcount;?>" name="manufacturing_date_<?=$rowcount;?>" value="<?=isset($manufacturing_date) ? $manufacturing_date : '';?>">
               <input type="hidden" id="expiry_date_<?=$rowcount;?>" name="expiry_date_<?=$rowcount;?>" value="<?=isset($expiry_date) ? $expiry_date : '';?>">
               
               <td id="td_<?=$rowcount;?>_1">
                  <label class='form-control' style='height:auto;' data-toggle="tooltip" title='Edit ?' >
                  <a id="td_data_<?=$rowcount;?>_1" href="javascript:void()" onclick="show_sales_item_modal(<?=$rowcount;?>)" title=""><?=$item_name;?></a> 
                  		<i onclick="show_sales_item_modal(<?=$rowcount;?>)" class="fa fa-edit pointer"></i>
                  		<?php if (isset($batch_tracking_enabled) && $batch_tracking_enabled == 1): ?>
                  		<i onclick="show_sales_batch_selection_modal(<?=$rowcount;?>, <?=$item_id;?>)" class="fa fa-cubes pointer text-warning" style="margin-left: 5px;" title="Select Batch"></i>
                  		<?php endif; ?>
                  		<?php 
                  		// Display selected batch details if any
                  		$batch_display = '';
                  		if (isset($batch_tracking_enabled) && $batch_tracking_enabled == 1) {
                  		    if (isset($batch_number) && !empty($batch_number)) {
                  		        $batch_display .= '<br><small class="text-info"><strong>Batch:</strong> ' . $batch_number . '</small>';
                  		    }
                  		    if (isset($manufacturing_date) && !empty($manufacturing_date) && $manufacturing_date != '0000-00-00') {
                  		        $formatted_mfg_date = date('d-m-Y', strtotime($manufacturing_date));
                  		        $batch_display .= '<br><small class="text-info"><strong>MFG:</strong> ' . $formatted_mfg_date . '</small>';
                  		    }
                  		    if (isset($expiry_date) && !empty($expiry_date) && $expiry_date != '0000-00-00') {
                  		        $formatted_exp_date = date('d-m-Y', strtotime($expiry_date));
                  		        $days_to_expiry = (strtotime($expiry_date) - time()) / (60 * 60 * 24);
                  		        $expiry_class = $days_to_expiry < 0 ? 'text-danger' : ($days_to_expiry < 30 ? 'text-warning' : 'text-success');
                  		        $batch_display .= '<br><small class="' . $expiry_class . '"><strong>EXP:</strong> ' . $formatted_exp_date . ' (' . round($days_to_expiry) . ' days)</small>';
                  		    }
                  		}
                  		if (!empty($batch_display)) {
                  		    echo $batch_display;
                  		}
                  		?>
                  	</label>
               </td>

               <!-- description  -->
               <!-- <td id="td_<?=$rowcount;?>_17">
                  
                  <textarea rows="1" type="text" style="font-weight: bold; height=34px;" id="td_data_<?=$rowcount;?>_17" name="td_data_<?=$rowcount;?>_17" class="form-control no-padding"><?=$description;?></textarea>
               </td> -->

               <!-- Qty -->
               <td id="td_<?=$rowcount;?>_3">
                  <div class="input-group ">
                     <span class="input-group-btn">
                     <button onclick="decrement_qty(<?=$rowcount;?>)" type="button" class="btn btn-default btn-flat"><i class="fa fa-minus text-danger"></i></button></span>
                     <input typ="text" value="<?=format_qty($item_sales_qty);?>" class="form-control no-padding text-center" onkeyup="calculate_tax(<?=$rowcount;?>); check_stock_availability(<?=$rowcount;?>);" id="td_data_<?=$rowcount;?>_3" name="td_data_<?=$rowcount;?>_3">
                     <span class="input-group-btn">
                     <button onclick="increment_qty(<?=$rowcount;?>)" type="button" class="btn btn-default btn-flat"><i class="fa fa-plus text-success"></i></button></span>
                  </div>
               </td>
               
               <!-- Unit Cost Without Tax-->
               <td id="td_<?=$rowcount;?>_10"><input type="text" name="td_data_<?=$rowcount;?>_10" id="td_data_<?=$rowcount;?>_10" class="form-control text-right no-padding only_currency text-center" onfocus="if(this.value=='0' || this.value=='0.00'){this.value='';this.select();}" onkeyup="calculate_tax(<?=$rowcount;?>);" onblur="if(this.value==''){this.value='0';} calculate_tax(<?=$rowcount;?>);" value="<?=store_number_format($item_sales_price,0);?>"></td>

               <!-- Discount -->
               <td id="td_<?=$rowcount;?>_8">
                  <input type="text" data-toggle="tooltip" title="Click to Change" name="td_data_<?=$rowcount;?>_8" id="td_data_<?=$rowcount;?>_8" class="pointer form-control text-right no-padding only_currency text-center item_discount" value="<?=store_number_format($item_discount,0);?>" onclick="show_sales_item_modal(<?=$rowcount;?>)" readonly>
               </td>

               <!-- Tax Amount -->
               <td id="td_<?=$rowcount;?>_11">
                  <input type="text" name="td_data_<?=$rowcount;?>_11" id="td_data_<?=$rowcount;?>_11" class="form-control text-right no-padding only_currency text-center" value="<?=store_number_format($item_tax_amt,0);?>" readonly>
               </td>

               <!-- Tax Details -->
               <td id="td_<?=$rowcount;?>_12">
                  <label class='form-control ' style='width:100%;padding-left:0px;padding-right:0px;'>
                  <a id="td_data_<?=$rowcount;?>_12" href="javascript:void()" data-toggle="tooltip" title='Click to Change' onclick="show_sales_item_modal(<?=$rowcount;?>)" title=""><?=$item_tax_name ;?></a>
                  	</label>
               </td>

               <!-- Amount -->
               <td id="td_<?=$rowcount;?>_9"><input type="text" name="td_data_<?=$rowcount;?>_9" id="td_data_<?=$rowcount;?>_9" class="form-control text-right no-padding only_currency text-center" style="border-color: #f39c12;" title="Total (Editable - will auto-calculate unit price)" onfocus="if(this.value=='0' || this.value=='0.00'){this.value='';this.select();}" onkeyup="calculate_from_total(<?=$rowcount;?>);" onblur="if(this.value==''){this.value='0';} calculate_from_total(<?=$rowcount;?>);" value="<?=store_number_format($item_amount,0);?>"></td>
               
               <!-- ADD button -->
               <td id="td_<?=$rowcount;?>_16" style="text-align: center;">
                  <a class=" fa fa-fw fa-minus-square text-red" style="cursor: pointer;font-size: 34px;" onclick="removerow(<?=$rowcount;?>)" title="Delete ?" name="td_data_<?=$rowcount;?>_16" id="td_data_<?=$rowcount;?>_16"></a>
               </td>
               <input type="hidden" id="td_data_<?=$rowcount;?>_4" name="td_data_<?=$rowcount;?>_4" value="<?=$item_sales_price;?>">
               <input type="hidden" id="td_data_<?=$rowcount;?>_15" name="td_data_<?=$rowcount;?>_15" value="<?=$item_tax_id;?>">
               <input type="hidden" id="td_data_<?=$rowcount;?>_5" name="td_data_<?=$rowcount;?>_5" value="<?=$item_tax_amt;?>">
               <input type="hidden" id="tr_available_qty_<?=$rowcount;?>_13" value="<?=$item_available_qty;?>">
               <input type="hidden" id="tr_item_id_<?=$rowcount;?>" name="tr_item_id_<?=$rowcount;?>" value="<?=$item_id;?>">
               
               <input type="hidden" id="tr_tax_type_<?=$rowcount;?>" name="tr_tax_type_<?=$rowcount;?>" value="<?=$item_tax_type;?>">
               <input type="hidden" id="tr_tax_id_<?=$rowcount;?>" name="tr_tax_id_<?=$rowcount;?>" value="<?=$item_tax_id;?>">
               <input type="hidden" id="tr_tax_value_<?=$rowcount;?>" name="tr_tax_value_<?=$rowcount;?>" value="<?=$item_tax;?>">
               <input type="hidden" id="description_<?=$rowcount;?>" name="description_<?=$rowcount;?>" value="<?=$description;?>">
               <input type="hidden" id="service_bit_<?=$rowcount;?>" name="service_bit_<?=$rowcount;?>" value="<?=$service_bit;?>">

               <input type="hidden" id="item_discount_type_<?=$rowcount;?>" name="item_discount_type_<?=$rowcount;?>" value="<?=$item_discount_type;?>">
               <input type="hidden" id="item_discount_input_<?=$rowcount;?>" name="item_discount_input_<?=$rowcount;?>" value="<?=store_number_format($item_discount_input,0);?>">
            </tr>
		<?php

	}
	public function delete_payment($payment_id){
        $this->db->trans_begin();

        // Get sales payment details first
		$salespayments = $this->db->query("select sales_id,customer_id from db_salespayments where id=$payment_id")->row();
		if (!$salespayments) {
			$this->db->trans_rollback();
			return "failed";
		}
		$sales_id = $salespayments->sales_id;
		$customer_id = $salespayments->customer_id;

        //ACCOUNT RESET - Get affected accounts before deleting transactions
		$reset_accounts = $this->db->select("debit_account_id,credit_account_id")
									->where("ref_salespayments_id", $payment_id)
									->group_by("debit_account_id,credit_account_id")
									->get("ac_transactions");

		// Delete account transactions first (to avoid foreign key constraint issues)
		$this->db->where("ref_salespayments_id", $payment_id);
		$delete_transactions = $this->db->delete("ac_transactions");
		if (!$delete_transactions) {
			$this->db->trans_rollback();
			return "failed";
		}

		// Now delete the sales payment record
		$q1=$this->db->query("delete from db_salespayments where id='$payment_id'");
		if(!$q1){
			$this->db->trans_rollback();
			return "failed";
		}
		
		$q2=$this->update_sales_payment_status($sales_id,$customer_id);
		if(!$q2){
			$this->db->trans_rollback();
			return "failed";
		}

		//ACCOUNT RESET - Update account balances
        if($reset_accounts->num_rows()>0){
        	foreach ($reset_accounts->result() as $res1) {
        		if($res1->debit_account_id && !update_account_balance($res1->debit_account_id)){
					$this->db->trans_rollback();
					return 'failed';
				}

				if($res1->credit_account_id && !update_account_balance($res1->credit_account_id)){
					$this->db->trans_rollback();
					return 'failed';
				}
        	}
        }
        //ACCOUNT RESET END

        if(!set_customer_tot_advance($customer_id)){
        	$this->db->trans_rollback();
        	return 'failed';
        }
		$this->db->trans_commit();
		return "success";
		
	}

	public function show_pay_now_modal($sales_id){
		$q1=$this->db->query("select * from db_sales where id=$sales_id");
		$res1=$q1->row();
		$customer_id = $res1->customer_id;
		$q2=$this->db->query("select * from db_customers where id=$customer_id");
		$res2=$q2->row();

		$customer_name=$res2->customer_name;
	    $customer_mobile=$res2->mobile;
	    $customer_phone=$res2->phone;
	    $customer_email=$res2->email;
	    $customer_country=$res2->country_id;
	    $customer_state=$res2->state_id;
	    $customer_address=$res2->address;
	    $customer_postcode=$res2->postcode;
	    $customer_gst_no=$res2->gstin;
	    $customer_tax_number=$res2->tax_number;
	    $customer_opening_balance=$res2->opening_balance;
	    $customer_tot_advance=$res2->tot_advance;
	    
	    // Get customer's currency for proper display
	    $customer_currency = get_customer_trading_currency($customer_id);
	    $customer_currency_symbol = $customer_currency ? $customer_currency->symbol : '$';

	    $sales_date=$res1->sales_date;
	    $reference_no=$res1->reference_no;
	    $sales_code=$res1->sales_code;
	    $sales_note=$res1->sales_note;
	    $grand_total=$res1->grand_total;
	    $paid_amount=$res1->paid_amount;
	    $due_amount =$grand_total - $paid_amount;

	    if(!empty($customer_country)){
	      $customer_country = $this->db->query("select country from db_country where id='$customer_country'")->row()->country;  
	    }
	    if(!empty($customer_state)){
	      $customer_state = $this->db->query("select state from db_states where id='$customer_state'")->row()->state;  
	    }

		?>
		<div class="modal fade" id="pay_now" tabindex='-1'>
		  <div class="modal-dialog ">
		    <div class="modal-content">
		      <div class="modal-header header-custom">
		        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
		          <span aria-hidden="true">&times;</span></button>
		        <h4 class="modal-title text-center"><?= $this->lang->line('payments'); ?></h4>
		      </div>
		      <div class="modal-body">
		        
		    <div class="row">
		      <div class="col-md-12">
		      	<div class="row invoice-info">
			        <div class="col-sm-4 invoice-col">
			          <?= $this->lang->line('customer_details'); ?>
			          <address>
			            <strong><?php echo  $customer_name; ?></strong><br>
			            <?php echo (!empty(trim($customer_mobile))) ? $this->lang->line('mobile').": ".$customer_mobile."<br>" : '';?>
			            <?php echo (!empty(trim($customer_phone))) ? $this->lang->line('phone').": ".$customer_phone."<br>" : '';?>
			            <?php echo (!empty(trim($customer_email))) ? $this->lang->line('email').": ".$customer_email."<br>" : '';?>
			            <?php echo (!empty(trim($customer_gst_no))) ? $this->lang->line('gst_number').": ".$customer_gst_no."<br>" : '';?>
			            <?php echo (!empty(trim($customer_tax_number))) ? $this->lang->line('tax_number').": ".$customer_tax_number."<br>" : '';?>
			            
			          </address>
			        </div>
			        <!-- /.col -->
			        <div class="col-sm-4 invoice-col">
			          <?= $this->lang->line('sales_details'); ?>
			          <address>
			            <b><?= $this->lang->line('invoice'); ?> #<?php echo  $sales_code; ?></b><br>
			            <b><?= $this->lang->line('date'); ?> :<?php echo show_date($sales_date); ?></b><br>
			            <b><?= $this->lang->line('grand_total'); ?> :<?php echo $grand_total; ?> <?php echo $customer_currency_symbol; ?></b><br>
			          </address>
			        </div>
			        <!-- /.col -->
			       
			        <div class="col-sm-4 invoice-col">
			          <b><?= $this->lang->line('paid_amount'); ?> :<span><?php echo number_format($paid_amount,2,'.',''); ?> <?php echo $customer_currency_symbol; ?></span></b><br>
			          <b><?= $this->lang->line('due_amount'); ?> :<span id='due_amount_temp'><?php echo number_format($due_amount,decimals(),'.',''); ?> <?php echo $customer_currency_symbol; ?></span></b><br>
			         
			        </div>
			        <!-- /.col -->
			      </div>
			      <!-- /.row -->
		      </div>
		      <div class="col-md-12">
		        <div>
		        <input type="hidden" name="payment_row_count" id='payment_row_count' value="1">
		        <div class="col-md-12  payments_div">
		          <div class="box box-solid bg-gray">
		            <div class="box-body">
			            <div class="row">
	                         <div class="col-md-12">
	                          <span for="">
	                            <label>
	                            <?= $this->lang->line('advance'); ?> : <label><?=store_number_format($customer_tot_advance)?> <?php echo $customer_currency_symbol; ?></label>
	                          </label>
	                          </span>
	                          <div class="checkbox">
	                            <label>
	                              <input type="checkbox" id="allow_tot_advance" name="allow_tot_advance"> <?= $this->lang->line('adjust_advance_payment'); ?>
	                            </label>
	                          </div>
	                         </div>
	                  	</div>

		              <div class="row">
		         		<div class="col-md-6">
		                  <div class="">
		                  <label for="payment_date"><?= $this->lang->line('date'); ?></label>
		                    <div class="input-group date">
			                      <div class="input-group-addon">
			                      <i class="fa fa-calendar"></i>
			                      </div>
			                      <input type="text" class="form-control pull-right datepicker" value="<?= show_date(date("d-m-Y")); ?>" id="payment_date" name="payment_date" readonly>
			                    </div>
		                      <span id="payment_date_msg" style="display:none" class="text-danger"></span>
		                </div>
		               </div>
		                <div class="col-md-6">
		                  <div class="">
		                  <label for="amount"><?= $this->lang->line('amount'); ?></label>
		                    <input type="text" class="form-control text-right paid_amt" id="amount" name="amount" placeholder="" value="<?=$due_amount;?>" onkeyup="updateCurrencyConversion()">
		                      <span id="amount_msg" style="display:none" class="text-danger"></span>
		                      <div id="currency_conversion_display" class="text-muted small" style="margin-top: 5px;">
		                        <span id="conversion_text">Enter amount to see conversion</span>
		                      </div>
		                </div>
		               </div>

		               

		                <div class="col-md-6">
		                  <div class="">
		                    <label for="payment_type"><?= $this->lang->line('payment_type'); ?></label>
		                    <select class="form-control" id='payment_type' name="payment_type" onchange="show_cheque_details()">
		                      <?php
		                        $q1=$this->db->query("select * from db_paymenttypes where status=1 and store_id=".get_current_store_id());
		                         if($q1->num_rows()>0){
		                             foreach($q1->result() as $res1){
		                             echo "<option value='".$res1->payment_type."'>".$res1->payment_type ."</option>";
		                           }
		                         }
		                         else{
		                            echo "No Records Found";
		                         }
		                        ?>
		                    </select>
		                    <span id="payment_type_msg" style="display:none" class="text-danger"></span>
		                  </div>
		                </div>
		                <div class="col-md-6">
		                  <div class="">
		                    <label for="payment_currency">Payment Currency</label>
		                    <select class="form-control" id='payment_currency' name="payment_currency" onchange="updatePaymentCurrency()">
		                    	<?php
		                    		$base_currency = get_base_currency();
		                    		$customer_currency = get_customer_trading_currency($customer_id);
		                    		
		                    		// Always show both currencies for flexibility
		                    		if ($customer_currency && $customer_currency->id != $base_currency->id) {
		                    			// Customer has different trading currency - default to customer currency
		                    			echo "<option value='".$customer_currency->id."' selected>".$customer_currency->currency_name." (".$customer_currency->currency_code.") - Customer Currency</option>";
		                    			echo "<option value='".$base_currency->id."'>".$base_currency->currency_name." (".$base_currency->currency_code.") - Base Currency</option>";
		                    		} else {
		                    			// Customer currency same as base or not set - default to base currency
		                    			echo "<option value='".$base_currency->id."' selected>".$base_currency->currency_name." (".$base_currency->currency_code.") - Base Currency</option>";
		                    			// Still show customer currency if it exists but is same as base
		                    			if ($customer_currency) {
		                    				echo "<option value='".$customer_currency->id."'>".$customer_currency->currency_name." (".$customer_currency->currency_code.") - Customer Currency</option>";
		                    			}
		                    		}
		                    	?>
		                    </select>
		                    <span id="payment_currency_msg" style="display:none" class="text-danger"></span>
		                  </div>
		                </div>

		                <div class="col-md-6">
		                  <div class="">
		                    <label for="account_id"><?= $this->lang->line('account'); ?></label>
		                    <select class="form-control" id='account_id' name="account_id" onchange="validateAccountCurrency()">
		                    	<option value="">-Select Currency First-</option>
		                    </select>
		                    <span id="account_id_msg" style="display:none" class="text-danger"></span>
		                    <div id="account_balance_display" class="text-info" style="margin-top: 5px; font-size: 12px; min-height: 20px;"></div>
		                    <div id="account_currency_alert" class="alert alert-warning" style="display:none; margin-top: 5px;">
		                       <i class="fa fa-exclamation-triangle"></i> <strong>Warning:</strong> Selected account currency doesn't match payment currency. Consider selecting a matching currency account.
		                    </div>
		                  </div>
		                </div>

		                <div class="cheque_div" style="display: none;">
		               	<div class="col-md-6">
                        <label for="cheque_number"><?= $this->lang->line('cheque_number'); ?></label>
                          <input type="text" class="form-control" id="cheque_number" name="cheque_number">
                            <span id="cheque_number_msg" style="display:none" class="text-danger"></span>
                     	</div>
                     	<div class="col-md-6">
                     	<label for="cheque_period"><?= $this->lang->line('cheque_period'); ?></label>
                          <input type="text" class="form-control" id="cheque_period" name="cheque_period">
                            <span id="cheque_period_msg" style="display:none" class="text-danger"></span>
                     	</div>
                     	</div><!-- cheque_div -->



		            <div class="clearfix"></div>
		        </div>  
		        <div class="row">
		               <div class="col-md-12">
		                  <div class="">
		                    <label for="payment_note"><?= $this->lang->line('payment_note'); ?></label>
		                    <textarea type="text" class="form-control" id="payment_note" name="payment_note" placeholder="" ></textarea>
		                    <span id="payment_note_msg" style="display:none" class="text-danger"></span>
		                  </div>
		               </div>
		                
		            <div class="clearfix"></div>
		        </div>   
		        </div>
		        </div>
		      </div><!-- col-md-12 -->
		    </div>
		      </div><!-- col-md-9 -->
		      <!-- RIGHT HAND -->
		    </div>
		      </div>
		      <div class="modal-footer">
		      	<input type="hidden" id="customer_id" value="<?=$customer_id?>">
		        <button type="button" class="btn btn-default btn-lg" data-dismiss="modal">Close</button>
		        <button type="button" onclick="save_payment(<?=$sales_id;?>)" class="btn bg-green btn-lg place_order btn-lg payment_save">Save<i class="fa  fa-check "></i></button>
		      </div>
		      
		      <script>
		      $(document).ready(function() {
		          // Initialize only once when modal is shown
		          $('#pay_now').off('shown.bs.modal.sales').on('shown.bs.modal.sales', function () {
		              console.log('Sales payment modal opened');
		              
		              // Small delay to ensure all elements are loaded
		              setTimeout(function() {
		                  // Initialize payment currency and accounts only once
		                  if (typeof updatePaymentCurrencySales === 'function') {
		                      updatePaymentCurrencySales();
		                  }
		              }, 300);
		          });
		          
		          // Clear cache when modal is hidden
		          $('#pay_now').off('hidden.bs.modal.sales').on('hidden.bs.modal.sales', function () {
		              console.log('Sales payment modal closed');
		              if (typeof clearSalesAccountCache === 'function') {
		                  clearSalesAccountCache();
		              }
		          });
		          
		          // Payment currency change handler
		          $('#payment_currency').off('change.sales').on('change.sales', function() {
		              console.log('Sales payment currency changed');
		              if (typeof updatePaymentCurrencySales === 'function') {
		                  updatePaymentCurrencySales();
		              }
		          });
		          
		          // Account change handler
		          $('#account_id').off('change.sales').on('change.sales', function() {
		              console.log('Sales account changed');
		              if (typeof checkAccountBalanceSales === 'function') {
		                  checkAccountBalanceSales();
		              }
		              if (typeof validateAccountCurrencySales === 'function') {
		                  validateAccountCurrencySales();
		              }
		          });
		          
		          // Amount change handler with debouncing
		          var salesAmountTimeout;
		          $('#amount').off('input.sales keyup.sales').on('input.sales keyup.sales', function() {
		              clearTimeout(salesAmountTimeout);
		              salesAmountTimeout = setTimeout(function() {
		                  if (typeof checkAccountBalanceSales === 'function') {
		                      checkAccountBalanceSales();
		                  }
		              }, 500); // Debounce for 500ms
		          });
		      });
		      </script>
		    </div>
		    <!-- /.modal-content -->
		  </div>
		  <!-- /.modal-dialog -->
		</div>
		<?php
	}

	public function save_payment(){
		extract($this->xss_html_filter(array_merge($this->data,$_POST,$_GET)));
		//print_r($this->xss_html_filter(array_merge($this->data,$_POST,$_GET)));exit();
    	if($amount=='' || $amount==0){$amount=null;}
		if($amount>0 && !empty($payment_type)){
			$this->db->query("ALTER TABLE db_salespayments AUTO_INCREMENT = 1");

			$this->db->trans_begin();

		$payment_code=get_init_code('sales_payment');
		
		// Handle currency conversion - Always convert to base currency
		$base_currency = get_base_currency();
		$payment_currency_id = !empty($payment_currency) ? $payment_currency : $base_currency->id;
		$exchange_rate = 1.0;
		$base_currency_amount = $amount;
		
		// If payment currency is different from base currency, convert
		if ($payment_currency_id != $base_currency->id) {
			$exchange_rate = get_exchange_rate($payment_currency_id, $base_currency->id);
			$base_currency_amount = $amount * $exchange_rate; // Convert to base currency
		}
		
		$salespayments_entry = array(
				'store_id' 			=> get_sales_details($sales_id)->store_id,
				'payment_code' 		=> $payment_code,
	    		'count_id'	  		=> get_count_id('db_salespayments'),
				'sales_id' 			=> $sales_id, 
				'payment_date'		=> system_fromatted_date($payment_date),//Current Payment with sales entry
				'payment_type' 		=> $payment_type,
				'payment' 			=> $amount, // Original payment amount in payment currency
				'payment_currency_id' => $payment_currency_id,
				'exchange_rate' 	=> $exchange_rate,
				'base_currency_amount' => $base_currency_amount, // Converted amount in base currency
				'payment_note' 		=> $payment_note,
				'created_date' 		=> $CUR_DATE,
				'created_time' 		=> $CUR_TIME,
				'created_by' 		=> $CUR_USERNAME,
				'system_ip' 		=> $SYSTEM_IP,
				'system_name' 		=> $SYSTEM_NAME,
				'status' 			=> 1,
				'account_id' 		=> (empty($account_id)) ? null : $account_id,
				'customer_id' 		=> $customer_id,
				'cheque_number' 	=> $cheque_number,
				'cheque_period' 	=> $cheque_period,
				'cheque_status' 	=> "Pending",
			);
		
			//is total advance payment enabled ?
			$advance_adjusted=0;
			if($allow_tot_advance=='checked'){
				$tot_advance = get_customer_details($customer_id)->tot_advance;
				if($tot_advance>0){
					if($amount==$tot_advance){
						$advance_adjusted = $amount;
					}
					else if($amount>$tot_advance){
						$advance_adjusted = $tot_advance;	
					}
					else{
						$advance_adjusted =  $amount;
					}
				}
			}
			//end 
			$salespayments_entry['advance_adjusted'] = $advance_adjusted;
			$q3 = $this->db->insert('db_salespayments', $salespayments_entry);
			
			// Get the salespayments ID after successful insert
			$salespayments_id = $this->db->insert_id();

			//Set the payment to specified account - Always use base currency
			if(!empty($account_id)){
				// Validate account currency matches payment currency
				$account_currency = $this->db->select('currency_id')->where('id', $account_id)->get('ac_accounts')->row();
				if($account_currency && $account_currency->currency_id != $payment_currency_id) {
					$this->db->trans_rollback();
					return "Currency mismatch: Selected account currency doesn't match payment currency. Please select an account with the same currency as the payment.";
				}
				
				//ACCOUNT INSERT - Use original amount in account's currency
				$insert_bit = insert_account_transaction(array(
														'transaction_type'  	=> 'SALES PAYMENT',
														'reference_table_id'  	=> $salespayments_id,
														'ref_salespayments_id'  => $salespayments_id,
														'debit_account_id'  	=> null,
														'credit_account_id'  	=> $account_id,
														'debit_amt'  			=> 0,
														'credit_amt'  			=> $amount, // Use original amount in account's currency
														'currency_id'  			=> $payment_currency_id, // Use payment currency
														'transaction_currency_id' => $payment_currency_id, // Store transaction currency
														'exchange_rate'  		=> $exchange_rate, // Store actual exchange rate used
														'base_currency_credit_amt' => $base_currency_amount, // Store converted amount
														'process'  				=> 'SAVE',
														'note'  				=> $payment_note . ($payment_currency_id != $base_currency->id ? " (Rate: " . number_format($exchange_rate, 4) . ")" : ""),
														'transaction_date'  	=> $CUR_DATE,
														'payment_code'  		=> $payment_code,
														'customer_id'  			=> $customer_id,
														'supplier_id'  			=> null,
												));
				if(!$insert_bit){
					return "failed";
				}
			}
			//end

			if(!set_customer_tot_advance($customer_id)){
	        	return 'failed';
	        }
			
		}
		else{
			return "Please Enter Valid Amount!";
		}
		
		$q10=$this->update_sales_payment_status($sales_id,$customer_id);
		if($q10!=1){
			return "failed";
		}

		$this->db->trans_commit();
		return "success";

	}
	
	public function view_payments_modal($sales_id){
		$q1=$this->db->query("select * from db_sales where id=$sales_id");
		$res1=$q1->row();
		$customer_id = $res1->customer_id;
		$q2=$this->db->query("select * from db_customers where id=$customer_id");
		$res2=$q2->row();

		$customer_name=$res2->customer_name;
	    $customer_mobile=$res2->mobile;
	    $customer_phone=$res2->phone;
	    $customer_email=$res2->email;
	    $customer_country=$res2->country_id;
	    $customer_state=$res2->state_id;
	    $customer_address=$res2->address;
	    $customer_postcode=$res2->postcode;
	    $customer_gst_no=$res2->gstin;
	    $customer_tax_number=$res2->tax_number;
	    $customer_opening_balance=$res2->opening_balance;
	    
	    // Get customer's currency for proper display
	    $customer_currency = get_customer_trading_currency($customer_id);
	    $customer_currency_symbol = $customer_currency ? $customer_currency->symbol : '$';

	    $sales_date=$res1->sales_date;
	    $reference_no=$res1->reference_no;
	    $sales_code=$res1->sales_code;
	    $sales_note=$res1->sales_note;
	    $grand_total=$res1->grand_total;
	    $paid_amount=$res1->paid_amount;
	    $due_amount =$grand_total - $paid_amount;

	    if(!empty($customer_country)){
	      $customer_country = $this->db->query("select country from db_country where id='$customer_country'")->row()->country;  
	    }
	    if(!empty($customer_state)){
	      $customer_state = $this->db->query("select state from db_states where id='$customer_state'")->row()->state;  
	    }

		?>
		<div class="modal fade" id="view_payments_modal" tabindex='-1'>
		  <div class="modal-dialog modal-lg">
		    <div class="modal-content">
		      <div class="modal-header header-custom">
		        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
		          <span aria-hidden="true">&times;</span></button>
		        <h4 class="modal-title text-center"><?= $this->lang->line('payments'); ?></h4>
		      </div>
		      <div class="modal-body">
		        
		    <div class="row">
		      <div class="col-md-12">
		      	<div class="row invoice-info">
			        <div class="col-sm-4 invoice-col">
			          <?= $this->lang->line('customer_details'); ?>
			          <address>
			            <strong><?php echo  $customer_name; ?></strong><br>
			            <?php echo (!empty(trim($customer_mobile))) ? $this->lang->line('mobile').": ".$customer_mobile."<br>" : '';?>
			            <?php echo (!empty(trim($customer_phone))) ? $this->lang->line('phone').": ".$customer_phone."<br>" : '';?>
			            <?php echo (!empty(trim($customer_email))) ? $this->lang->line('email').": ".$customer_email."<br>" : '';?>
			            <?php echo (!empty(trim($customer_gst_no))) ? $this->lang->line('gst_number').": ".$customer_gst_no."<br>" : '';?>
			            <?php echo (!empty(trim($customer_tax_number))) ? $this->lang->line('tax_number').": ".$customer_tax_number."<br>" : '';?>
			          </address>
			        </div>
			        <!-- /.col -->
			        <div class="col-sm-4 invoice-col">
			          <?= $this->lang->line('sales_details'); ?>
			          <address>
			            <b><?= $this->lang->line('invoice'); ?> #<?php echo  $sales_code; ?></b><br>
			            <b><?= $this->lang->line('date'); ?> :<?php echo show_date($sales_date); ?></b><br>
			            <b><?= $this->lang->line('grand_total'); ?> :<?php echo $grand_total; ?> <?php echo $customer_currency_symbol; ?></b><br>
			          </address>
			        </div>
			        <!-- /.col -->
			        <div class="col-sm-4 invoice-col">
			          <b><?= $this->lang->line('paid_amount'); ?> :<span><?php echo number_format($paid_amount,decimals(),'.',''); ?> <?php echo $customer_currency_symbol; ?></span></b><br>
			          <b><?= $this->lang->line('due_amount'); ?> :<span id='due_amount_temp'><?php echo number_format($due_amount,decimals(),'.',''); ?> <?php echo $customer_currency_symbol; ?></span></b><br>
			         
			        </div>
			        <!-- /.col -->
			      </div>
			      <!-- /.row -->
		      </div>
		      <div class="col-md-12">
		       
		     
		              <div class="row">
		         		<div class="col-md-12">
		                  
		                      <table class="table table-bordered">
                                  <thead>
                                  <tr class="bg-primary">
                                    <th>#</th>
                                    <th><?= $this->lang->line('payment_date'); ?></th>
                                    <th><?= $this->lang->line('payment'); ?></th>
                                    <th><?= $this->lang->line('payment_type'); ?></th>
                                    <th><?= $this->lang->line('account'); ?></th>
                                    <th><?= $this->lang->line('payment_note'); ?></th>
                                    <th><?= $this->lang->line('created_by'); ?></th>
                                    <th><?= $this->lang->line('action'); ?></th>
                                  </tr>
                                </thead>
                                <tbody>
                                	<?php
                                	$q1=$this->db->query("select * from db_salespayments where sales_id=$sales_id");
									$i=1;
									$str = '';
									if($q1->num_rows()>0){
										foreach ($q1->result() as $res1) {
											echo "<tr>";
											echo "<td>".$i++."</td>";
											echo "<td>".show_date($res1->payment_date)."</td>";
											echo "<td>".store_number_format($res1->payment)."</td>";
											echo "<td class='text-left'>";
			                                    echo $res1->payment_type;
			                                    if(!empty($res1->cheque_number)){
				                                    echo "<br>Cheque no.:".$res1->cheque_number;
				                                    echo "<br>Period:".$res1->cheque_period;
				                                }
			                                  echo "</td>";
											echo "<td>".get_account_name($res1->account_id)."</td>";
											echo "<td>".$res1->payment_note."</td>";
											echo "<td>".ucfirst($res1->created_by)."</td>";
										
											echo "<td>
											<a onclick='show_receipt(".$res1->id.")' title='Print Receipt' class='pointer btn  btn-default' ><i class='fa fa-print'></i>
											<a onclick='delete_sales_payment(".$res1->id.")' title='Delete Payment ?' class='pointer btn  btn-danger' ><i class='fa fa-trash'></i>
											</</td>";	
											echo "</tr>";
										}
									}
									else{
										echo "<tr><td colspan='7' class='text-danger text-center'>No Records Found</td></tr>";
									}
									?>
                                </tbody>
                            </table>
		               
		               </div>
		            <div class="clearfix"></div>
		        </div>    
		       
		     
		   
		      </div><!-- col-md-9 -->
		      <!-- RIGHT HAND -->
		    </div>
		      </div>
		      <div class="modal-footer">
		        <button type="button" class="btn btn-default btn-lg" data-dismiss="modal">Close</button>
		        
		      </div>
		    </div>
		    <!-- /.modal-content -->
		  </div>
		  <!-- /.modal-dialog -->
		</div>
		<?php
	}
	
	/**
	 * Handle FIFO batch allocation for batch-tracked items
	 * Automatically selects batches that expire first to fulfill the required quantity
	 */
	private function handle_fifo_batch_allocation($sales_id, $sales_status, $item_id, $description, $sales_qty, 
		$price_per_unit, $tax_type, $tax_id, $tax_amt, $discount_input, $discount_amt, 
		$discount_type, $single_unit_total_cost, $total_cost, $purchase_price, $store_id) {
		
		log_message('debug', "FIFO batch allocation started for item $item_id, qty: $sales_qty");
		
		// Get available batches from both purchases and opening stock
		// First get purchase batches
		$purchase_batches = $this->db->query("
			SELECT 
				pi.batch_number,
				pi.manufacturing_date,
				pi.expiry_date,
				pi.purchase_qty,
				COALESCE(SUM(si.sales_qty), 0) as sold_qty,
				(pi.purchase_qty - COALESCE(SUM(si.sales_qty), 0)) as available_qty
			FROM db_purchaseitems pi
			LEFT JOIN db_salesitems si ON pi.item_id = si.item_id 
				AND pi.batch_number = si.batch_number 
				AND si.status = 1
			WHERE pi.item_id = ? 
				AND pi.batch_number IS NOT NULL 
				AND pi.batch_number != ''
				AND pi.status = 1
			GROUP BY pi.batch_number, pi.manufacturing_date, pi.expiry_date, pi.purchase_qty
			HAVING available_qty > 0
			ORDER BY pi.expiry_date ASC, pi.manufacturing_date ASC
		", array($item_id));
		
		// Then get opening stock batches
		$opening_batches = $this->db->query("
			SELECT 
				batch_number,
				manufacturing_date,
				expiry_date,
				quantity as purchase_qty,
				0 as sold_qty,
				remaining_quantity as available_qty
			FROM db_item_batches
			WHERE item_id = " . (int)$item_id . "
				AND batch_number IS NOT NULL 
				AND batch_number != ''
				AND status = 1
				AND remaining_quantity > 0
			ORDER BY expiry_date ASC, manufacturing_date ASC
		");
		
		// Combine both results
		$all_batches = array();
		if($purchase_batches) {
			foreach($purchase_batches->result() as $row) {
				$all_batches[] = $row;
			}
		}
		if($opening_batches) {
			foreach($opening_batches->result() as $row) {
				$all_batches[] = $row;
			}
		}
		
		$remaining_qty = $sales_qty;
		$batch_entries = array();
		$total_available = 0;
		
		// First, calculate total available stock
		log_message('debug', "FIFO: Found batches for item $item_id:");
		foreach ($all_batches as $row) {
			$total_available += $row->available_qty;
			log_message('debug', "FIFO: Batch {$row->batch_number} - Available: {$row->available_qty}, Sold: {$row->sold_qty}");
		}
		
		// Check if we have enough stock
		if ($total_available < $sales_qty) {
			log_message('error', "FIFO batch allocation: Insufficient stock for item $item_id. Required: $sales_qty, Available: $total_available");
			return false;
		}
		
		foreach ($all_batches as $row) {
			if ($remaining_qty <= 0) break;
			
			$qty_to_use = min($remaining_qty, $row->available_qty);
			
			// Calculate proportional costs for this batch
			$proportional_unit_cost = $single_unit_total_cost;
			$proportional_total_cost = $total_cost * ($qty_to_use / $sales_qty);
			$proportional_tax_amt = $tax_amt * ($qty_to_use / $sales_qty);
			$proportional_discount_amt = $discount_amt * ($qty_to_use / $sales_qty);
			
			$batch_entry = array(
				'sales_id' 			=> $sales_id, 
				'sales_status'		=> $sales_status, 
				'item_id' 			=> $item_id, 
				'description' 		=> $description, 
				'sales_qty' 		=> $qty_to_use,
				'price_per_unit' 	=> $price_per_unit,
				'tax_type' 			=> $tax_type,
				'tax_id' 			=> $tax_id,
				'tax_amt' 			=> $proportional_tax_amt,
				'discount_input' 	=> $discount_input,
				'discount_amt' 		=> $proportional_discount_amt,
				'discount_type' 	=> $discount_type,
				'unit_total_cost' 	=> $proportional_unit_cost,
				'total_cost' 		=> $proportional_total_cost,
				'purchase_price' 	=> $purchase_price,
				'status'	 		=> 1,
				'seller_points'		=> get_seller_points($item_id) * $qty_to_use,
				'batch_number' 		=> $row->batch_number,
				'manufacturing_date' => $row->manufacturing_date,
				'expiry_date' 		=> $row->expiry_date,
				'store_id'			=> (store_module() && is_admin()) ? $store_id : get_current_store_id()
			);
			
			$batch_entries[] = $batch_entry;
			$remaining_qty -= $qty_to_use;
			
			log_message('debug', "FIFO batch allocation: Using batch {$row->batch_number} for $qty_to_use units. Remaining: $remaining_qty");
		}
		
		// Insert all batch entries
		log_message('debug', "FIFO: Inserting " . count($batch_entries) . " batch entries");
		foreach ($batch_entries as $entry) {
			log_message('debug', "FIFO: Inserting batch {$entry['batch_number']} with qty {$entry['sales_qty']}");
			$q2 = $this->db->insert('db_salesitems', $entry);
			if (!$q2) {
				log_message('error', 'Failed to insert FIFO batch entry: ' . $this->db->last_query());
				return false;
			}
			
			// Update remaining_quantity in db_item_batches for opening stock batches
			$this->db->where('item_id', $item_id);
			$this->db->where('batch_number', $entry['batch_number']);
			$this->db->set('remaining_quantity', 'remaining_quantity - ' . $entry['sales_qty'], FALSE);
			$this->db->update('db_item_batches');
			
			log_message('debug', "FIFO: Updated remaining_quantity for batch {$entry['batch_number']} by -{$entry['sales_qty']}");
		}
		
		// If we couldn't fulfill the entire quantity, log a warning
		if ($remaining_qty > 0) {
			log_message('warning', "FIFO batch allocation: Could not fulfill complete quantity for item $item_id. Remaining: $remaining_qty");
		}
		
		log_message('debug', "FIFO batch allocation completed for item $item_id. Created " . count($batch_entries) . " batch entries.");
		
		return true;
	}
}
