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

class BiltyModel extends CI_Model{

	function __construct(){
		parent::__construct();
	}


		function checkLogin($user_name, $pass){
			$this->db->where('mobile', $user_name);
			$this->db->where('PASSWORD', $pass);
			$query = $this->db->get('loktantra_senani_tb');
			if($query){
				return $query->row_array();
			}else{
				return false;
			}
		}

		function logged_status($table, $data, $where){
			$this->db->set($data);
	        $this->db->where($where);
			if($this->db->update($table, $data)){
				return true;
			}else{
				return false;
			}
		}


		function selectPassword($mobile){


				$sql ="SELECT PASSWORD FROM loktantra_senani_tb  WHERE mobile='$mobile' ;";

			$query = $this->db->query($sql);

			$data = $query->row_array();

     $value = $data['PASSWORD'];

//	print_r($value); die;

		if($query){
			//	print_r($id); die;

		//	print_r($query->result()); die;

		return $value;


		}else{
			return false;
		}

		}



		function selectCatInfo($type, $where){

    	if($where){
			$this->db->where($where);
		}

 	$query = $this->db->get($type);

		if($query){
			return $query->result();
		}else{
			return false;
		}
	}



	function selectInfoFront($id){

			$sql ="SELECT 	lst.id AS l_id,mr_miss,pita_pati,lst.NAME AS l_name,hindi_name, gender, father_name, dob, application_date, address, lst.state_id_fk,
district_id_fk, email, mobile, phone_no, whatsapp, applicant_name, relationship, jail_name, under_arrest, jail_avdhi,
past_responsibility, present_responsibility, living_heavenly, PASSWORD, memoris, lst.STATUS AS l_status,
image_formate1,image_formate2, image_formate3, image_formate4, image_formate5, image_formate6,st.name AS s_name,dt.name AS d_name
 FROM loktantra_senani_tb lst INNER JOIN state_tb st ON lst.state_id_fk= st.id
 INNER JOIN district_tb dt ON district_id_fk=dt.id WHERE lst.STATUS ='active' AND lst.id='$id' ;";

		$query = $this->db->query($sql);
		if($query){

//	print_r($query->result()); die;

	return $query->result();

	}else{
		return false;
	}

}

function selectInfoByStateandcityActive($stateid,$cityid){

	if($stateid>0 && $cityid>0){

		$sql ="SELECT 	lst.id AS l_id,mr_miss,pita_pati,lst.NAME AS l_name,hindi_name, gender, father_name, dob, application_date, address, lst.state_id_fk,
district_id_fk, email, mobile, phone_no, whatsapp, applicant_name, relationship, jail_name, under_arrest, jail_avdhi,
past_responsibility, present_responsibility, living_heavenly, PASSWORD, memoris, lst.STATUS AS l_status,
image_formate1,image_formate2, image_formate3, image_formate4, image_formate5, image_formate6,st.name AS s_name,dt.name AS d_name
FROM loktantra_senani_tb lst INNER JOIN state_tb st ON lst.state_id_fk= st.id
INNER JOIN district_tb dt ON district_id_fk=dt.id
WHERE lst.STATUS ='active' AND lst.state_id_fk ='$stateid' AND district_id_fk ='".$cityid."' ";


} elseif ($stateid>0 && $cityid==0)
	// code...
 {
	$sql ="SELECT 	lst.id AS l_id,mr_miss,pita_pati,lst.NAME AS l_name,hindi_name, gender, father_name, dob, application_date, address, lst.state_id_fk,
district_id_fk, email, mobile, phone_no, whatsapp, applicant_name, relationship, jail_name, under_arrest, jail_avdhi,
past_responsibility, present_responsibility, living_heavenly, PASSWORD, memoris, lst.STATUS AS l_status,
image_formate1,image_formate2, image_formate3, image_formate4, image_formate5, image_formate6,st.name AS s_name,dt.name AS d_name
FROM loktantra_senani_tb lst INNER JOIN state_tb st ON lst.state_id_fk= st.id
INNER JOIN district_tb dt ON district_id_fk=dt.id
WHERE lst.STATUS ='active' AND lst.state_id_fk ='$stateid' ";


}

else {
	$sql ="SELECT 	lst.id AS l_id,mr_miss,pita_pati,lst.NAME AS l_name,hindi_name, gender, father_name, dob, application_date, address, lst.state_id_fk,
district_id_fk, email, mobile, phone_no, whatsapp, applicant_name, relationship, jail_name, under_arrest, jail_avdhi,
past_responsibility, present_responsibility, living_heavenly, PASSWORD, memoris, lst.STATUS AS l_status,
image_formate1,image_formate2, image_formate3, image_formate4, image_formate5, image_formate6,st.name AS s_name,dt.name AS d_name
FROM loktantra_senani_tb lst INNER JOIN state_tb st ON lst.state_id_fk= st.id
INNER JOIN district_tb dt ON district_id_fk=dt.id WHERE lst.STATUS ='active';";
}

	$query = $this->db->query($sql);
	if($query){

//	print_r($query->result()); die;

return $query->result();

}else{
	return false;
}

}



function BiltyManageInfoPendding($d1,$d2){


    //print_r($d1); die;

   if($d1!='' && $d2!=''){

	$sql ="SELECT 	id,invoice_no,consignee_id_fk,consignee_name,consignee_address,consignee_gstin,consignor_id_fk,consignor_name, 
	consignor_address,consignor_gstin,source_id_fk,source,destination_id_fk,destination,bill_date,truck_no, 
	lot_no, pr_no,pm,quality,item,weight,rate,total_amount,remark,CURRENT_DATE,STATUS, 
	c_y_session, packages,paid_amount,sms_no,TYPE
	 FROM transport_bilty_tb WHERE bill_date BETWEEN CAST('$d1' AS DATE) AND CAST('$d2' AS DATE)  AND paid_amount = 0 ORDER BY id;";
//print_r($sql); die;

}

else {

	$sql ="SELECT 	id,invoice_no,consignee_id_fk,consignee_name,consignee_address,consignee_gstin,consignor_id_fk,consignor_name, 
	consignor_address,consignor_gstin,source_id_fk,source,destination_id_fk,destination,bill_date,truck_no, 
	lot_no, pr_no,pm,quality,item,weight,rate,total_amount,remark,CURRENT_DATE,STATUS, 
	c_y_session, packages,paid_amount,sms_no,TYPE
	 FROM transport_bilty_tb WHERE paid_amount = 0 ORDER BY id;";

//print_r($sql); die;


}

      $query = $this->db->query($sql);


		if($query){

	//	print_r($query->result()); die;

		return $query->result();

		}else{
			return false;
		}


	}


	function BiltyManageInfoComplete($d1,$d2){


		//print_r($d1); die;
	
	   if($d1!='' && $d2!=''){
	
		$sql ="SELECT 	id,invoice_no,consignee_id_fk,consignee_name,consignee_address,consignee_gstin,consignor_id_fk,consignor_name, 
		consignor_address,consignor_gstin,source_id_fk,source,destination_id_fk,destination,bill_date,truck_no, 
		lot_no, pr_no,pm,quality,item,weight,rate,total_amount,remark,CURRENT_DATE,STATUS, 
		c_y_session, packages,paid_amount,sms_no,TYPE
		 FROM transport_bilty_tb WHERE bill_date BETWEEN CAST('$d1' AS DATE) AND CAST('$d2' AS DATE)  AND paid_amount > 0 ORDER BY id;";
	//print_r($sql); die;
	
	}
	
	else {
	
		$sql ="SELECT 	id,invoice_no,consignee_id_fk,consignee_name,consignee_address,consignee_gstin,consignor_id_fk,consignor_name, 
		consignor_address,consignor_gstin,source_id_fk,source,destination_id_fk,destination,bill_date,truck_no, 
		lot_no, pr_no,pm,quality,item,weight,rate,total_amount,remark,CURRENT_DATE,STATUS, 
		c_y_session, packages,paid_amount,sms_no,TYPE
		 FROM transport_bilty_tb WHERE bill_date BETWEEN '2024-04-01' AND '2025-03-31'  AND paid_amount > 0 ORDER BY id;";
	
	//print_r($sql); die;
	
	
	}
	
		  $query = $this->db->query($sql);
	
	
			if($query){
	
		//	print_r($query->result()); die;
	
			return $query->result();
	
			}else{
				return false;
			}
	
	
		}



	function getInfoById($id){


		//print_r($d1); die;
		
	
		$sql ='SELECT 	id,invoice_no,consignee_id_fk,consignee_name,consignee_address,consignee_gstin,consignor_id_fk,consignor_name, 
		consignor_address,consignor_gstin,source_id_fk,source,destination_id_fk,destination,bill_date,truck_no, 
		lot_no, pr_no,pm,quality,item,weight,rate,total_amount,remark,CURRENT_DATE,STATUS, 
		c_y_session, packages,paid_amount,sms_no,TYPE
		 FROM transport_bilty_tb WHERE id ="'.$id.'" ;';
	
	//print_r($sql); die;
	
	
		  $query = $this->db->query($sql);
	
	
			if($query){
	
		//	print_r($query->result()); die;
	
			return $query->result();
	
			}else{
				return false;
			}
	
	
		}

		function getMaxInvoiceNo(){


			//print_r($d1); die;
			
		
			$sql ="SELECT MAX(invoice_no) AS ram FROM transport_bilty_tb WHERE STATUS='active';";
		
		//print_r($sql); die;
		
		
			  $query = $this->db->query($sql);
		
		
				if($query){
		
			//	print_r($query->result()); die;
		
				return $query->result();
		
				}else{
					return false;
				}
		
		
			}






function selectInfoByStateandcityFront($stateid,$cityid,$name){
	if($stateid>0 && $cityid>0 && $name!='' )


	{
		$sql ='SELECT 	lst.id AS l_id,mr_miss,pita_pati,lst.NAME AS l_name,hindi_name, gender, father_name, dob, application_date, address, lst.state_id_fk,
district_id_fk, email, mobile, phone_no, whatsapp, applicant_name, relationship, jail_name, under_arrest, jail_avdhi,
past_responsibility, present_responsibility, living_heavenly, PASSWORD, memoris, lst.STATUS AS l_status,
image_formate1,image_formate2, image_formate3, image_formate4, image_formate5, image_formate6,st.name AS s_name,dt.name AS d_name
FROM loktantra_senani_tb lst INNER JOIN state_tb st ON lst.state_id_fk= st.id
INNER JOIN district_tb dt ON district_id_fk=dt.id
WHERE   lst.STATUS="active" AND lst.state_id_fk ="'.$stateid.'" AND district_id_fk ="'.$cityid.'" AND lst.NAME ="'.$name.'";';

}

elseif ($stateid>0 && $cityid>0 && empty($name) ) {

$sql ='SELECT 	lst.id AS l_id,mr_miss,pita_pati,lst.NAME AS l_name,hindi_name, gender, father_name, dob, application_date, address, lst.state_id_fk,
district_id_fk, email, mobile, phone_no, whatsapp, applicant_name, relationship, jail_name, under_arrest, jail_avdhi,
past_responsibility, present_responsibility, living_heavenly, PASSWORD, memoris, lst.STATUS AS l_status,
image_formate1,image_formate2, image_formate3, image_formate4, image_formate5, image_formate6,st.name AS s_name,dt.name AS d_name
FROM loktantra_senani_tb lst INNER JOIN state_tb st ON lst.state_id_fk= st.id
INNER JOIN district_tb dt ON district_id_fk=dt.id WHERE lst.STATUS="active" AND lst.state_id_fk ="'.$stateid.'" AND district_id_fk ="'.$cityid.'";';
}

elseif ($stateid>0 && $cityid==0 && empty($name) ) {

$sql ='SELECT 	lst.id AS l_id,mr_miss,pita_pati,lst.NAME AS l_name,hindi_name, gender, father_name, dob, application_date, address, lst.state_id_fk,
district_id_fk, email, mobile, phone_no, whatsapp, applicant_name, relationship, jail_name, under_arrest, jail_avdhi,
past_responsibility, present_responsibility, living_heavenly, PASSWORD, memoris, lst.STATUS AS l_status,
image_formate1,image_formate2, image_formate3, image_formate4, image_formate5, image_formate6,st.name AS s_name,dt.name AS d_name
FROM loktantra_senani_tb lst INNER JOIN state_tb st ON lst.state_id_fk= st.id
INNER JOIN district_tb dt ON district_id_fk=dt.id WHERE lst.STATUS="active" AND lst.state_id_fk ="'.$stateid.'";';
}

else {

$sql ="SELECT lst.id AS l_id,mr_miss,pita_pati,lst.NAME AS l_name,hindi_name, gender, father_name, dob, application_date, address, lst.state_id_fk,
district_id_fk, email, mobile, phone_no, whatsapp, applicant_name, relationship, jail_name, under_arrest, jail_avdhi,
past_responsibility, present_responsibility, living_heavenly, PASSWORD, memoris, lst.STATUS AS l_status,
image_formate1,image_formate2, image_formate3, image_formate4, image_formate5, image_formate6,st.name AS s_name,dt.name AS d_name
FROM loktantra_senani_tb lst INNER JOIN state_tb st ON lst.state_id_fk= st.id
INNER JOIN district_tb dt ON district_id_fk=dt.id WHERE   lst.STATUS='active'";

}



	$query = $this->db->query($sql);

	if($query){
	//print_r($query->result()); die;

return $query->result();

}
else{
	return false;
}

}


	function selectInfoLimit(){

			$sql ="SELECT 	pt.id AS p_id,area_id_fk,owner_name,owner_mobile_no,price,address,pt.name AS p_name,size,
			plot_range,pt.status AS p_status,atb.name AS a_name,image_formate,description,TYPE,way
			FROM house_tb pt INNER JOIN area_tb  atb ON atb.id=area_id_fk LIMIT 8;";

		$query = $this->db->query($sql);
		if($query){

//	print_r($query->result()); die;

	return $query->result();

	}else{
		return false;
	}

}


function selectFrontAllInfo(){

		$sql ="SELECT 	pt.id AS p_id,area_id_fk,owner_name,owner_mobile_no,price,address,pt.name AS p_name,size,
		plot_range,pt.status AS p_status,atb.name AS a_name,image_formate,description,TYPE,way
		FROM house_tb pt INNER JOIN area_tb  atb ON atb.id=area_id_fk WHERE pt.STATUS='active';";

	$query = $this->db->query($sql);
	if($query){

//	print_r($query->result()); die;

return $query->result();

}else{
	return false;
}

}


function selectSearchFrontAllInfo($id){

		$sql ='SELECT 	pt.id AS p_id,area_id_fk,owner_name,owner_mobile_no,price,address,pt.name AS p_name,size,
		plot_range,pt.status AS p_status,atb.name AS a_name,image_formate,description,TYPE,way
		FROM house_tb pt INNER JOIN area_tb  atb ON atb.id=area_id_fk WHERE area_id_fk ="'.$id.'" '  ;

	$query = $this->db->query($sql);
	if($query){

//	print_r($query->result()); die;

return $query->result();

}else{
	return false;
}

}



          // insert Query For Insert Data

	public function insertInfo($table, $data) {

		if ($this->db->insert($table, $data)) {
            $insert_id = $this->db->insert_id();
			return $insert_id;
		}else{
			return false;
		}
	}

	       // Update Query For updateSubgallertImage
    function updateImage1($id,$data){
     $this->db->where('id', $id);
     $this->db->update('loktantra_senani_tb', $data);
       }


			 function updateImage2($id,$data){
	      $this->db->where('id', $id);
	      $this->db->update('loktantra_senani_tb', $data);
	        }

					function updateImage3($id,$data){
					 $this->db->where('id', $id);
					 $this->db->update('loktantra_senani_tb', $data);
						 }
						 function updateImage4($id,$data){
							$this->db->where('id', $id);
							$this->db->update('loktantra_senani_tb', $data);
								}
								function updateImage5($id,$data){
								 $this->db->where('id', $id);
								 $this->db->update('loktantra_senani_tb', $data);
									 }
									 function updateImage6($id,$data){
	 								 $this->db->where('id', $id);
	 								 $this->db->update('loktantra_senani_tb', $data);
	 									 }



// Update Query For Selected Student
function updateInfoById($id,$data){
$this->db->where('id', $id);
$this->db->update('transport_bilty_tb', $data);
}

function updatePasswordById($id,$data){
$this->db->where('id', $id);
$this->db->update('loktantra_senani_tb', $data);
}







function mySms($mobileno, $message){

	$mymessage="Your Password is = ".$message;

    $message = urlencode($mymessage);
    $sender = 'APTKAL';
		  $user = 'aapatkal';
		 $pass = 'aapatkal321';
    $apikey = 'fl=0&gwid=2&dc=8';
    $baseurl = 'http://103.26.99.76/vendorsms/pushsms.aspx?user='.$user.'&password='.$pass;

    $url = $baseurl.'&msisdn='.$mobileno.'&sid='.$sender.'&msg='.$message.'&'.$apikey;



    $ch = curl_init();
    curl_setopt($ch, CURLOPT_POST, false);
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    $response = curl_exec($ch);
    curl_close($ch);

    // Use file get contents when CURL is not installed on server.
    if(!$response){
        $response = file_get_contents($url);
    }
}




function deleteInfo($id)
{

 $sql ='DELETE FROM transport_bilty_tb WHERE id= "'.$id.'"; ';

	 
$this->db->query($sql);
}





}






?>
