<?php   
class DB_Functions{       
private $db;
function __construct() {         
include_once './db_connect.php'; 
date_default_timezone_set("Africa/Nairobi");
$this->db = new DB_Connect();         
  
   $this->link = $this->db->connect();
		$this->dt_format = new DateTime("now", new DateTimeZone('Africa/Nairobi')); //first argument "must" be a string
		$this->dt_format->setTimestamp(time()); //adjust the object to correct timestamp  
}       
function __destruct() {
}  
///////////////////////////////
function storestockLedger($inv_no,$inv_date,$itemcode,$loc,$store,$stock_transfer,$stock_type,$type_name,$action_taken,$qty,$price,$create_by,$createdate,$createtime,$ip,$link1,$errorflag){
	$flag=$errorflag;
	$result=mysqli_query($link1,"insert into store_stock_ledger set reference_no='".$inv_no."',reference_date='".$inv_date."',partcode='".$itemcode."',location_code='".$loc."',owner_code='".$store."',stock_transfer='".$stock_transfer."',stock_type='".$stock_type."',type_of_transfer='".$type_name."',action_taken='".$action_taken."',qty='".$qty."',rate='".$price."',create_by='".$create_by."',create_date='".$createdate."',create_time='".$createtime."',ip='".$ip."'");
//// check if query is not executed
	if (!$result) {
		$flag = false;
		echo "Error detailsSL: " . mysqli_error($link1) . "";
	}
	return $flag;
}
//////////////

function stockLedger($inv_no,$inv_date,$itemcode,$from_party,$to_party,$stock_transfer,$stock_type,$type_name,$action_taken,$qty,$price,$create_by,$createdate,$createtime,$ip,$link1,$errorflag){
	$flag=$errorflag;
	$result=mysqli_query($link1,"insert into stock_ledger set reference_no='".$inv_no."',reference_date='".$inv_date."',partcode='".$itemcode."',from_party='".$from_party."', to_party='".$to_party."',stock_transfer='".$stock_transfer."',stock_type='".$stock_type."',type_of_transfer='".$type_name."',action_taken='".$action_taken."',qty='".$qty."',rate='".$price."',create_by='".$create_by."',create_date='".$createdate."',create_time='".$createtime."',ip='".$ip."'");
//// check if query is not executed
	if (!$result) {
		$flag = false;
		echo "Error detailsSL: " . mysqli_error($link1) . "";
	}
	return $flag;
}

///////////////
function getoustanding($loc,$party,$link1){
	$sql="select SUM(CASE WHEN crdr='CR' THEN amount ELSE 0 END) AS cr_amt, SUM(CASE WHEN crdr='DR' THEN amount ELSE 0 END) as dr_amt from party_account_ledger where location_code='".$loc."' and party='".$party."'";
	$rs=mysqli_query($link1,$sql) or die(mysqli_error($link1));
	$row=mysqli_fetch_assoc($rs);
	$outstanding=$row['dr_amt']-$row['cr_amt'];
	return $outstanding;
	}
///////status/////
function getdispatchstatus($var){
	if($var==1){
		$status="Pending";
	}else if($var==2){
		$status="Processed";
	}else if($var==3){
		$status="Dispatched";
	}else if($var==4){
		$status="Received";
	}else if($var==5){
		$status="Cancelled";
	}else if($var==6){
		$status="Partial Processed";
	}else if($var==7){
		$status="Pending For Admin Approval";
	}else if($var==8){
		$status="Pending For Finance Approval";
	}else if($var==9){
		$status="Pending For Gate Entry";
	}else if($var==10){
		$status="Partial Received";
	}else if($var==11){
		$status="Missing";
	}else if($var==12){
		$status="CN Generated";
	}else{
		$status="-";
	}
	return $status;
}


////////////////////

function escape( $x ) {
return "'" . mysql_real_escape_string($x) . "'";
}
/////////////////////////////////////////
function getAnyDetails($keyid,$fld,$lookupname,$tbname){
	///// check no. of column
	$link1=$this->link;
	$chk_keyword = substr_count($fld, ',');
	if($keyid!=''){
		$part_desc="part_desc";
		$part_name="part_name";
		$v_part=",vendor_partcode";
		if($chk_keyword > 0){
			//$arr1=array($fld);
			if((strpos($fld, 'part_desc') !== false) || (strpos($fld, 'part_name') !== false)){
				$fields=$fld.$v_part;
			}else{
				$fields=$fld;
			}
			$explodee = explode(",",$fld);
			//print_r($explodee);
			$tb_details = mysqli_fetch_array(mysqli_query($link1,"select ".$fields." from ".$tbname." where ".$lookupname." = '".$keyid."'"));
			$rtn_str = "";
			for($k=0;$k < count($explodee);$k++){
				if($explodee[$k]==$part_desc){
					$rslt=$tb_details[$k]."(".$tb_details['vendor_partcode'].")";
				}else if($explodee[$k]==$part_name){
					$rslt=$tb_details[$k]."(".$tb_details['vendor_partcode'].")";
				}else{
					$rslt=$tb_details[$k];
				}
				if($rtn_str==""){
					$rtn_str= $rslt;
				}else{
					$rtn_str.= "~".$rslt;
				}
			}
		}else{
			if(($fld==$part_desc) || ($fld==$part_name)){
				$fields=$fld.",vendor_partcode";
			}else{
				$fields=$fld;
			}
			$tb_details = mysqli_fetch_array(mysqli_query($link1,"select ".$fields." from ".$tbname." where ".$lookupname." = '".$keyid."'"));
			if($fld==$part_desc){
				$rtn_str = $tb_details['part_desc']."(".$tb_details['vendor_partcode'].")";
			}else if($fld==$part_name){
				$rtn_str = $tb_details['part_name']."(".$tb_details['vendor_partcode'].")";
			}else{
				$rtn_str = $tb_details[$fields];
			}
		}
		return $rtn_str;
	}
}
//////////get main url to reach the server folder////////////////
public function getAttachmentUrl() {
	$url = "http://geeranishop.com/SALEAPI/";
	return $url;
}
 //////////Attendance view////////////////

public function getEng_attendance ($user_id, $from_date, $to_date) {
     $link1=$this->link;
$result = mysqli_query ($link1,"select * from user_attendence where user_id='$user_id' and date (in_datetime) >='$from_date' and date (out_datetime)<='$to_date'") or die(mysqli_error($link1));
    return $result;
}

 /////////////// City Master /////////////////////
 public function getStateMaster() {    
     $link1=$this->link;
 $result = mysqli_query($link1,"SELECT * FROM state_master order by state")or die(mysqli_error($link1));         
 return $result;     
 } 
 
 public function getCityMaster($state){
	 $link1=$this->link;
 $result = mysqli_query($link1,"SELECT * FROM district_master  where status='A' and state='".$state."' order by city")or die(mysql_error());         
 return $result;
 }
 
 //////////
  public function getareamaster($city){
	 $link1=$this->link;
 $result = mysqli_query($link1,"SELECT * FROM area_master  where status='A' and city='".$city."' order by area")or die(mysql_error());         
 return $result;
 }
 //////////////
/////////////// Brand master /////////////////////
 public function getbrand() {   
     $link1=$this->link;
 $result = mysqli_query($link1,"SELECT * FROM brand_master where status='1' order by brand")or die(mysql_error());         
 return $result;     
 } 
 ////////////////////
 
 /////////////// Product master /////////////////////
 public function getproductmaster() {   
     $link1=$this->link;
 $result = mysqli_query($link1,"SELECT * FROM product_master where status='1' order by product_name")or die(mysql_error());         
 return $result;     
 } 
 ////////////////////
  
 /////////////// Model master /////////////////////
 public function getmodelmaster($brandid,$productid) {   
     $link1=$this->link;
 $result = mysqli_query($link1,"SELECT * FROM model_master where model_id in (select model_id from model_product_map where product_id='".$productid."' and status='A')   and brand_id = '".$brandid."' and status='1'")or die(mysqli_error($link1));         
 return $result;     
 } 
 ////////////////////
 
 /////////////// Model master /////////////////////
 public function getpartmaster($modelid) {   
     $link1=$this->link;
    // echo "SELECT id,partcode, model_id, part_name,part_desc, part_category,vendor_partcode,location_price,status FROM partcode_master where status='1' and model_id!='' and model_id like '%".$modelid."%' order by part_name ";
 $result = mysqli_query($link1,"SELECT id,partcode, model_id, part_name,part_desc, part_category,vendor_partcode,location_price,status FROM partcode_master where status='1' and (brand_id='3' or brand_id='4')  order by part_name ")or die(mysqli_error($link1));         
 return $result;     
 } 
 
 
////////////////////////////////////
 
 public function getUserMaster(){
 $result=mysql_query("select * from admin_users where status='active'");
 return $result;
 }
 public function getLocationDetail($loc_code){
 	$link1=$this->link;
 $result=mysqli_query($link1,"select * from asc_master where asc_code='".$loc_code."'");
 return $result;
 }
 
 public function getIMEIDetails($ei) {
	$b=array();      
	//echo "SELECT * FROM billing_imei_data where imei='$ei' or imei2='$ei' order by id desc";
 $result = mysql_query("SELECT * FROM billing_imei_data where imei1='$ei' or imei2='$ei' order by id desc") or die(mysql_error());         
return $result;

 } 
 
 public function getUserDetails($ei,$password){
	 $link1=$this->link;
	//echo "select * from admin_users where (username='$ei' or owner_code='$ei') and password='$password' and status='active'";
	 $result=mysqli_query($link1,"select * from hrms_employe_master where loginid='$ei' and password='$password' and status='active'")  or die(mysqli_error($link1)); 
	 return $result;
 }
 
 public  function getSaleData(){
     $link1=$this->link;
	 $result=mysqli_query($link1,"select * from sale_data order by id");
	 return $result;
	 
 }
 public  function getUserType($typeid){
	 $result=mysql_query("select * from usertype_master where refid='$typeid'");
	 return $result;
	 
 }
 public function getOffers(){
	 $result=mysql_query("select * from scheme_master where status='Active' AND from_date <= '".date("Y-m-d")."' AND to_date >= '".date("Y-m-d")."'");
	 return $result; 
 }
 
 public function getSaleData_User($ei){
       $link1=$this->link;
	 $result=mysqli_query($link1,"select * from sale_data where user_name='$ei'");
	 return $result;
	 
 }
 
 public function Retailer_list($ei){
 	
     $link1=$this->link;
	$result=mysqli_query($link1,"SELECT * FROM my_retailer where create_by='".$ei."' and approval_status='Approved'"); 
	return $result;	 
 }

 public function MyRetailer_list($ei){
 //	echo "SELECT * FROM locatin_master where locationtype='RET' and status='1'";
	$link1=$this->link;
   $result=mysqli_query($link1,"SELECT * FROM location_master where locationtype='RET' and statusid='1' and create_by='".$ei."'") or die(mysqli_error($link1)); 
   return $result;	 
}

public function getContactBook($ei){
	//	echo "SELECT * FROM locatin_master where locationtype='RET' and status='1'";
	   $link1=$this->link;
	  $result=mysqli_query($link1,"SELECT * FROM contact_book where create_by='".$ei."'") or die(mysqli_error($link1)); 
	  return $result;	 
   }

public function contact_book($retailer_code,$retailer_name,$create_by,$contact_person_name,$location,$contact_no,$entry_date){

	//echo "insert into contact_book set retailer_code='".$retailer_code."',retailer_name='".$retailer_name."',create_by='".$create_by."',contact_person_name='".$contact_person_name."',location='".$location."',contact_no='".$contact_no."',entry_date='".$entry_date."'";
$a=array();
	$link1=$this->link;
	$result=mysqli_query($link1,"insert into contact_book set retailer_code='".$retailer_code."',retailer_name='".$retailer_name."',create_by='".$create_by."',contact_person_name='".$contact_person_name."',location='".$location."',contact_no='".$contact_no."',entry_date='".$entry_date."'") or die(mysqli_error($link1));
  if($result){
	  $a["status"]="1";
	  $a["msg"]="success";
  }else{
	  $a["status"]="0";
	  $a["mag"]="Unsuccess";
  }

return $a;
}


public function check_in($task_id,$create_by,$chech_in_date,$check_in_time,$checkInLati,$checkInLongi,$checkinaddress){

	$a=array();
	$link1=$this->link;

	if($task_id!=''){


$result=mysqli_query($link1,"insert into task_checkin set task_id='".$task_id."',create_by='".$create_by."',check_in_date='".$chech_in_date."',check_in_time='".$check_in_time."',checkinlati='".$checkInLati."',checkinlongi='".$checkInLongi."',checkinaddress='".$checkinaddress."'");

if($result){
	$a["status"]="1";
	$a["msg"]="success";
}else{
	$a["status"]="0";
	$a["mag"]="Unsuccess".mysqli_error($link1);
}
	}else{
		$a["status"]="0";
	$a["mag"]="Unsuccesstaskid is blank";
	}

return $a;



}


 ///// written on 17 april 2019 by shekhar
 /*public function Retailer_list($ei){
     
//	echo "SELECT a.name,a.city,a.state,a.asc_code FROM asc_master a ,access_location b where b.uid='".$ei."' and b.status='Y' and a.asc_code = b.location_id and a.status='active' order by a.name";
	$result=mysqli_query($link1,"SELECT a.name,a.city,a.state,a.asc_code FROM asc_master a ,access_location b where b.uid='".$ei."' and b.status='Y' and a.asc_code = b.location_id and a.status='active' order by a.name") or die(mysqli_error($link1)); 
	return $result;	 
 }*/
 public function mappedLocation($ei){
	$result=mysql_query("SELECT * FROM access_location where uid='".$ei."' and status='Y'"); 
	return $result;	 
 }


 public function checkin_details($task_id){
	$a=array();
	$b=array();
	$link1=$this->link;
	//echo "select * from task_checkin where task_id='".$task_id."'";
	$result=mysqli_query($link1,"select * from task_checkin where task_id='".$task_id."' order by id desc") or die(mysqli_error($link1));
$row=mysqli_fetch_assoc($result);

$b["task_id"]=$row['task_id'];
$b["create_by"]=$row["create_by"];
$b['check_in_date']=$row['check_in_date'];
$b['check_in_time']=$row['check_in_time'];
$b['checknLati']=$row['checkinlati'];
$b['checkInLongi']=$row['checkinlongi'];
$b['checkinaddress']=$row['checkinaddress'];



return $b;
 }


 public function salesOrderList($ei){
       $link1=$this->link;
      // echo "SELECT * FROM purchase_order_master where create_by='".$ei."'";
	$result=mysqli_query($link1,"SELECT * FROM po_master where entry_by='".$ei."'");

	return $result;	 
 }
 public function TaDaList($ei){
	 $link1=$this->link;
	$result = mysqli_query($link1,"SELECT * FROM ta_da where userid='".$ei."'"); 
	return $result;	 
 }
 ///// get product category
	public function stockList($retailerCode,$productCode){
		if($productCode){ $condition = " and partcode='".$productCode."'";}else{ $condition = "";}
		$result = mysql_query("SELECT * FROM stock_status where asc_code = '".$retailerCode."' ".$condition."")or die(mysql_error());         
		return $result;
	}
 ////// get purchase order item details
	public function salesOrderItems($order_no){
		$link1=$this->link;
		return $result = mysqli_query($link1,"SELECT * FROM po_items where po_no='".$order_no."'");
	}
	public function TaDaItems($ref_no){
		$link1=$this->link;
	 $result = mysqli_query($link1,"SELECT * FROM ta_da where system_ref_no='".$ref_no."'") or die(mysqli_error($link1));
	 	return $result;
	}
 public function productList(){
 	$link1=$this->link;
	$result=mysqli_query($link1,"SELECT * FROM product_master where status='active'"); 
	return $result;	 
 }
 public function productDetail($prodcode){
 	$link1=$this->link;
	$result=mysqli_query($link1,"SELECT * FROM product_master where productcode='".$prodcode."'"); 
	return $result;	 
 }
 public function catName($pscatid){
	$link1=$this->link;
	$result=mysqli_query($link1,"SELECT prod_sub_cat,product_category FROM product_sub_category where psubcatid='".$pscatid."'"); 
	return $result;	 
 }
 public function brandName($brandcode){
 	$link1=$this->link;
	$result=mysqli_query($link1,"SELECT make FROM make_master where id='".$brandcode."'"); 
	return $result;	 
 }
 public function productPrice($loc_state,$loc_type,$prodcode){
 	//echo "SELECT mrp,price FROM price_master where state='".$loc_state."' and location_type='".$loc_type."' and product_code='".$prodcode."' and status='active'";
 	$link1=$this->link;
	$result=mysqli_query($link1,"SELECT mrp,price FROM price_master where state='".$loc_state."' and location_type='".$loc_type."' and product_code='".$prodcode."' and status='active'"); 
	return $result;	 
 }
 
 public function task_master($ei){
	 $link1=$this->link;
	 //echo "SELECT * FROM task_master where 1 and (status='pending') or (status='open') or (status='in-progress')";
	 $result=mysqli_query($link1,"SELECT * FROM saleperson_task where create_by='$ei'");
	 return $result;
 }
 
 public function collection_list($ei){
	$link1=$this->link;
	//echo "SELECT * FROM task_master where 1 and (status='pending') or (status='open') or (status='in-progress')";
	$result=mysqli_query($link1,"SELECT * FROM collect_data where collect_by='$ei'");
	return $result;
}

///////////////////////////////////////////billing list///////////
public function bill_list($ei){
	$link1=$this->link;
	//echo "SELECT * FROM task_master where 1 and (status='pending') or (status='open') or (status='in-progress')";
	$result=mysqli_query($link1,"SELECT * FROM billing_data where billing_by='$ei'");
	return $result;
}

  ////////////////////Lead User Track///////////////
 public function LeadUserTrack($prime_id,$address,$date_value,$time_value,$eng_id,$latitude,$longitude,$imei,$model_id,$distance) {
	$link1=$this->link;
	$sql =mysqli_query($link1,"insert ignore into lead_user_track set prime_id ='$prime_id',eng_id='$eng_id',travel_date='$date_value',travel_time='$time_value',address='$address' ,latitude  = '$latitude' , longitude ='$longitude' ,  	imei ='$imei' , model_id = '$model_id',travel_km='$distance'");
    if(mysql_insert_id()){
	$a[prime_id] = $prime_id;
  $a["response"]='1';    
	return $a;  
 }  
else{
	$a[prime_id] = $prime_id;
	$a["response"]='0';    
	return $a;  	
	}
 	}
 ////////////////////event Track///////////////
 public function UserEventTrack($userid,$imei,$model_id,$date_value,$time_value,$distance,$latitude,$longitude,$address,$event,$event_refno) {
	$link1=$this->link;
	$sql = mysqli_query($link1,"INSERT INTO user_event_track set userid = '".$userid."', imei = '".$imei."', model_id = '".$model_id."', travel_date = '".$date_value."', travel_time = '".$time_value."', travel_km = '".$distance."', latitude = '".$latitude."', longitude = '".$longitude."', address = '".$address."', event_done = '".$event."', event_ref_no = '".$event_refno."'");
    if(mysqli_insert_id($link1)){
  		$a = '1';    
		return $a;  
	}
	else{
		$a = '0';    
		return $a;  	
	}
 }
 
 public function task_master_all(){
	$link1=$this->link;
	 $result=mysqli_query($link1,"SELECT * FROM task_master");
	 return $result; 
 }

 public function getfeedback_data($ei){
	 $link1=$this->link;
	 $result=mysqli_query($link1,"SELECT * FROM product_feedback_data WHERE update_by = '".$ei."'");
	 return $result;
 }	
	
	
	public function RetailerPost($name,$city,$state,$addrs,$pin,$email,$phn,$latitude,$longitude,$create_by){
		$link1=$this->link;
	$b=array();	
	$result=mysqli_query($link1,"insert into my_retailer set  name='".$name."',city ='".$city."',state='".$state."',address='".$addrs."',pincode='".$pin."',email='".$email."',phone='".$phn."',latitude='".$latitude."',longitude='".$longitude."',create_by='".$create_by."'");
	if($result){
		$b['name']=$name;
		$b['city']=$city;
		$b['state']=$state;
		$b['address']=$addrs;
		$b['pincode']=$pin;
		$b['email']=$email;
		$b['phone']=$phn;
		$b['latitude']=$latitude;
		$b['longitude']=$longitude;
		$b['response']=1;	
	}else{
		$b['name']=$name;
		$b['city']=$city;
		$b['state']=$state;
		$b['address']=$addrs;
		$b['pincode']=$pin;
		$b['email']=$email;
		$b['phone']=$phn;
		$b['latitude']=$latitude;
		$b['longitude']=$longitude;
		$b['response']=0;
	}
	return $b;
}
	////// make sales order
	Public function createRetailer($district,$Location,$kiosk_owner_name,$parmanent_add,$owner_phn_no,$other_no,$owner_email_id,$daily_trunover,$iden_no,$kiosk_center_loc,$kiosk_center_name,$kiosk_contact,$kiosk_phn_no,$kiosk_email_id,$kiosk_urban_add,$municipal_town,$ward_name,$bank_acc_no,$bank_name,$branch_name,$both_names1,$permanent_add1,$owner_phn_no1,$other_phn_no1,$owner_email_id1,$sign_id_no1,$both_names2,$permanent_add2,$owner_phn_no2,$other_phn_no2,$owner_email_id2,$sign_id_no2,$phy_add_proof,$kiosk_photo_proof,$owner_referee_id,$bank_acc_details,$trade_lic,$date_applied,$deal_docs,$supplied_goods_amt,$charges_paid,$geerani_shop_approval,$date_approved,$create_by,$salesperson_name){
		$b=array();
		$link1=$this->link;
		$today = date("Y-m-d");
		$currtime = date("H:i:s");
//echo "insert into my_retailer set district='$district',location='$location',kiosk_owner_name='$kiosk_owner_name',parmanent_add='$parmanent_add',owner_phn_no='$owner_phn_no',other_no='$other_no',owner_email_id='$owner_email_id',daily_trunover='$daily_trunover',identification_no='$identification_no',kiosk_center_loc='$kiosk_center_loc',kiosk_center_name='$kiosk_center_name',kiosk_contact='$kiosk_contact',kiosk_phn_no='$kiosk_phn_no',kiosk_email_id='$kiosk_email_id',kiosk_urban_add='$kiosk_urban_add',municipal_town='$municipal_town',ward_name='$ward_name',bank_acc_no='$bank_acc_no',bank_name='$bank_name',branch_name='$branch_name',acc_holder_name='$acc_holder_name',both_names1='$both_names1',permanent_add1='$permanent_add1',owner_phn_no1='$owner_phn_no1',other_phn_no1='$other_phn_no1',owner_email_id1='$owner_email_id1',sign_id_no1='$sign_id_no1',both_names2='$both_names2',permanent_add2='$permanent_add2',owner_phn_no2='$owner_phn_no2',other_phn_no2='$other_phn_no2',owner_email_id2='$owner_email_id2',sign_id_no2='$sign_id_no2',phy_add_proof='$phy_add_proof',kiosk_photo_proof='$kiosk_photo_proof',owner_referee_id='$owner_referee_id',bank_acc_details='$bank_acc_details',trade_lic='$trade_lic',date_applied='$date_applied',salesperson_name='$salesperson_name',deal_docs='$deal_docs',supplied_goods_amt='$supplied_goods_amt',charges_paid='$charges_paid',geerani_shop_approval='$geerani_shop_approval',date_approved='$date_approved',create_by='$create_by'"; 
		$rs=mysqli_query($link1,"insert into my_retailer set state='Kenya',city='$district',name='$kiosk_center_name',district='$district',location='$Location',kiosk_owner_name='$kiosk_owner_name',parmanent_add='$parmanent_add',owner_phn_no='$owner_phn_no',other_no='$other_no',owner_email_id='$owner_email_id',daily_trunover='$daily_trunover',identification_no='$$iden_no',kiosk_center_loc='$kiosk_center_loc',kiosk_center_name='$kiosk_center_name',kiosk_contact='$kiosk_contact',kiosk_phn_no='$kiosk_phn_no',kiosk_email_id='$kiosk_email_id',kiosk_urban_add='$kiosk_urban_add',municipal_town='$municipal_town',ward_name='$ward_name',bank_acc_no='$bank_acc_no',bank_name='$bank_name',branch_name='$branch_name',acc_holder_name='$acc_holder_name',both_names1='$both_names1',permanent_add1='$permanent_add1',owner_phn_no1='$owner_phn_no1',other_phn_no1='$other_phn_no1',owner_email_id1='$owner_email_id1',sign_id_no1='$sign_id_no1',both_names2='$both_names2',permanent_add2='$permanent_add2',owner_phn_no2='$owner_phn_no2',other_phn_no2='$other_phn_no2',owner_email_id2='$owner_email_id2',sign_id_no2='$sign_id_no2',phy_add_proof='$phy_add_proof',kiosk_photo_proof='$kiosk_photo_proof',owner_referee_id='$owner_referee_id',bank_acc_details='$bank_acc_details',trade_lic='$trade_lic',date_applied='$date_applied',salesperson_name='$salesperson_name',deal_docs='$deal_docs',supplied_goods_amt='$supplied_goods_amt',charges_paid='$charges_paid',geerani_shop_approval='Pending',date_approved='$date_approved',create_by='$create_by',approval_status='Pending'");

		if($rs){
	 $b["kiosk_owner_name"]=$kiosk_owner_name;
	 $b["owner_phn_no"]=$owner_phn_no;
     $b["status"]="1";
		}else{
			$b["kiosk_owner_name"]=$kiosk_owner_name;
			$b["owner_phn_no"]=$owner_phn_no;
			$b["status"]="0";

		}
		return $b;
	}
	////////////////////////////////////////////////////////////////////////////////
	function createSO($location_code,$uid,$uname,$prodList){
		$link1=$this->link;
		$flag = 1;
		$today = date("Y-m-d");
		$currtime = date("H:i:s");
		if($location_code){
		//// Make System generated PO no.//////
		$fromaddress = explode ("~" ,$this->getAnyDetails($location_code,"locationaddress,stateid","location_code","location_master",$link1));
		$wh=mysqli_fetch_assoc(mysqli_query($link1,"select wh_location from map_wh_location where location_code='".$location_code."' and status='Y'"));
        $parentcode=$wh['wh_location'];
		$locinfo= mysqli_fetch_array(mysqli_query($link1,"select * from location_master where location_code='".$parentcode."' "));
//// Make System generated PO no.//////
$res_po=mysqli_query($link1,"select max(po_id) as no from po_master where from_code='".$location_code."'");
$row_po=mysqli_fetch_array($res_po);
$c_nos=$row_po['no']+1;
$po_no=$location_code."PO".$c_nos; 
		//// get parent id

	///// Insert in item data by picking each data row one by one
	for($i=0; $i<count($prodList) ; $i++) { 
		$product_id = $prodList[$i]["product_id"];
		$model_id = $prodList[$i]["model_id"];
		$brand_id =  $prodList[$i]["brand_id"];
		$part_code =  $prodList[$i]["part_code"];
echo		$qty=$prodList[$i]["qty"];
		$price=$prodList[$i]["price"];
		$amount = round($qty * $price);
		$total_amount += $amount;
			/////////// insert data
			$query2="insert into po_items set  po_price='".$amount."',po_value='".$amount."',po_id ='".$poid['po_id']."' , po_no='".$po_no."',product_id ='".$product_id."', brand_id ='".$brand_id."', model_id ='".$model_id."', partcode ='".$part_code."',type = 'PO', qty='".$qty."' , update_date= '".$today."' ,status='1'";
			$result = mysqli_query($link1, $query2);
		   if($result){
			   $flag *=1;
		   }else{
			   $flag *=0;
		   }
		   ////// hold the PO qty in stock ////
	       //$flag=holdStockQty($parentcode,$val,$req_qty[$k],$link1,$flag);
	}/// close for loop
	///// Insert Master Data
	$usr_add="INSERT INTO po_master set po_value='".$total_amount."',po_no='".$po_no."', po_date='".$today."' , to_code ='".$parentcode."' , to_address='".$locinfo['locationaddress']."' ,to_state='".$locinfo['stateid']."',potype='PO', update_date='".$today."',entry_by='".$uid."' ,entry_ip ='".$_SERVER['REMOTE_ADDR']."' ,status='1' ,from_code= '".$location_code."',  	from_address = '".$fromaddress[0]."' ,po_id='".$c_nos."' , 	from_state = '".$fromaddress[1]."'  ";
$result1=mysqli_query($link1,$usr_add);
	if($result1){
		   $flag *=1;
	   }else{
		   $flag *=0;
	   }
	////// insert in activity table////
	$this->dailyActivity($uid,$po_no,"PO","ADD","app_user");
	if($flag==1){
		///// insert in event track
		$resp = $this->UserEventTrack($uid,$imei,$modelid,$today,$currtime,$distance,$latitude,$longitude,$address,"Sales Order Create",$po_no);
		return $po_no;
	}else{
		mysqli_query($link1,"delete from purchase_order_data where po_no='".$po_no."'");
		mysqli_query($link1,"delete from purchase_order_master where po_no='".$po_no."'");
		return 0;
	}
		}else{
			return "userid is blank";
		}
	///// check both master and data query are successfully executed
	}
	////// function to capture daily activities /////
	public function dailyActivity($uid,$refno,$activityType,$actionTaken,$ip){
		$link1=$this->link;
		$todayDate = date('Y-m-d');
		$todayTime = date('H:i:s');
		$query = "INSERT INTO daily_activities set userid='".$uid."',ref_no='".$refno."',activity_type='".$activityType."',action_taken='".$actionTaken."',update_date='".$todayDate."',update_time='".$todayTime."',system_ip='".$ip."'";
		$result = mysqli_query($link1,$query) or die(mysqli_error($link1));
	}
 function product_feedback($fb_type,$fb_subj,$feedback,$image,$userid,$latitude,$longitude,$address,$distance,$imei,$model_id){
	 $link1=$this->link;
	$b=array();	
	$today = date("Y-m-d");
	$todaytime = date("H:i:s");
	///// make FB ref no
	$res_ref = mysqli_query($link1,"SELECT MAX(ref_id) as mno FROM product_feedback_data WHERE update_by = '".$userid."'") or die(mysqli_error($link1));
	$row_ref = mysqli_fetch_assoc($res_ref);
	$next_mno = $row_ref["mno"] + 1;
	$ref_no = "FB/".$userid."/".$next_mno;
	////////
	$result = mysqli_query($link1,"INSERT INTO product_feedback_data SET ref_no = '".$ref_no."', ref_id = '".$next_mno."', feedback_type = '".$fb_type."', feedback_subject = '".$fb_subj."', feedback = '".$feedback."', update_by = '".$userid."', update_date = '".date("Y-m-d H:i:s")."', latitude = '".$latitude."', longitude = '".$longitude."', address = '".$address."', image = '".$image."'");
	if($result){
		///// insert in event track
		$resp = $this->UserEventTrack($userid,$imei,$model_id,$today,$todaytime,$distance,$latitude,$longitude,$address,"Feedback Submit",$ref_no);
		$b['success'] = "Feedback is successfully submitted";
		$b['id'] = $userid;
	}else{
		$b['error'] = "Request could not processed. Please try again";
		$b['id'] = $userid;
	}
	return $b;
 }
 /////// add TA DA of sales executive
 public function addTaDa($uid , $food_exp , $food_filename , $courier_exp , $courier_filename , $localconv_exp , $localconv_filename , $mobile_exp , $mobile_filename , $other_exp , $other_filename , $remk,$latitude,$longitude,$address,$distance,$imei,$modelid){
	$link1=$this->link;
	$todayDate = date('Y-m-d');
	$todayTime = date('H:i:s');
	// make expense system generated ref no.
	$sql = "SELECT MAX(seq_no) as qa FROM ta_da where userid='".$uid."'";
	$res = mysqli_query($link1,$sql) or die(mysqli_error($link1));
	$row = mysqli_fetch_assoc($res);
	$code_id = $row["qa"];
	$next_seq = $row["qa"] + 1;
	/// make 3 digit padding
	$pad = str_pad(++$code_id,4,"0",STR_PAD_LEFT);
	//// make logic of ref no.
	$refno = "EXP/".strtoupper($uid)."/".$pad;
    /// calculate total exp amount
	$total_exp = $food_exp + $courier_exp + $localconv_exp + $mobile_exp + $other_exp;
	$query = "INSERT INTO ta_da set userid='".$uid."',system_ref_no='".$refno."',seq_no='".$next_seq."',food_exp='".$food_exp."',food_exp_img='".$food_filename."',courier_exp='".$courier_exp."',courier_exp_img='".$courier_filename."',localconv_exp='".$localconv_exp."',localconv_exp_img='".$localconv_filename."',mobile_exp='".$mobile_exp."',mobile_exp_img='".$mobile_filename."',other_exp='".$other_exp."',other_exp_img='".$other_filename."',entry_date='".$todayDate."',entry_time='".$todayTime."',entry_by='app_user',remark='".$remk."',total_amt='".$total_exp."',status='Pending', latitude = '".$latitude."', longitude = '".$longitude."', address = '".$address."'";
	$result = mysqli_query($link1,$query) or die(mysqli_error($link1));
	if($result){
		///// insert in event track
		$resp = $this->UserEventTrack($uid,$imei,$modelid,$todayDate,$todayTime,$distance,$latitude,$longitude,$address,"TA/DA Submit",$refno);
		return $refno;
	}else{
		return 0;
	}
 }
 public function save_stock($retailer_name,$feature_phone,$smart_phone,$collection_amt,$collection_mode,$image_name,$image){
	 $a=array();
	 $result=mysql_query("insert into save_stock set retailer_name='$retailer_name',feature_phone='$feature_phone',smart_phone='$smart_phone',collection_amt='$collection_amt',collection_mode='$collection_mode',image_name='$image_name'");
	 if($result) {  	 
     $a["response"]=1;
     
   return $a;         
   
   } else{ if( mysql_errno() == 1062) {                
 
   $a["response"]=0;
   
   return $a;             
   } else {                 
 
   $a["status"]='no';
                  
  return $a;             
                     
 }} 
 }
 
 public function update_task($taskid,$check_in_date,$check_in_time,$check_out_date,$check_out_time,$status,$checkInLati,$checkInLong,$checkOutLati,$checkOutLongi,$image_name,$checkinaddress,$checkoutaddress,$create_by,$remark){

	$link1=$this->link;
	$b=array();
	$todayDate = date('Y-m-d');
	$todayTime = date('H:i:s');
	$in_datetime=$todayDate." "."09:00:00";
	$out_datetime=$todayDate." "."18:00:00";

  $res=mysqli_query($link1,"update saleperson_task set check_in_date='".$check_in_date."',check_in_time='".$check_in_time."',check_out_date='".$check_out_date."',check_out_time='".$check_out_time."',image_name='".$image_name."',status='".$status."',checkInLati='".$checkInLati."',checkInLong='".$checkInLong."',checkOutLati='".$checkOutLati."',checkOutLongi='".$checkOutLongi."',update_date='".$todayDate."',checkinaddress='".$checkinaddress."',checkoutaddress='".$checkoutaddress."',remark='".$remark."' where taskid='".$taskid."'") or die(mysqli_error($link1));

 $sql_at="select id from user_attendence where insert_date='".$todayDate."' and user_id='".$create_by."'";
 $rs_at=mysqli_query($link1,$sql_at) or die(mysqli_error($link1));
 if(mysqli_num_rows($rs_at)>0){

	mysqli_query($link1,"update user_attendence set status_in='IN',status_out='OUT',pmodel='task',status='P',working_hour='9:00',in_datetime='".$in_datetime."',out_datetime='".$out_datetime."',task_id='".$taskid."' where insert_date='".$todayDate."' and user_id='".$create_by."'" ) or die(mysqli_error($link1));
 }else{
	mysqli_query($link1,"insert into user_attendence set status_in='IN',status_out='OUT',pmodel='task',status='P',working_hour='9:00',in_datetime='".$in_datetime."',out_datetime='".$out_datetime."',task_id='".$taskid."',insert_date='".$todayDate."',user_id='".$create_by."'" )	 or die(mysqli_error($link1));
 }
 
  if(!$res){
   $b["error"]="something went wrong";
   $b["status"]="0";

  }else{
$b["status"]="1";
$b["error"]="";

  }
return $b;

 }
 public function save_task($taskname,$visit_date,$task_sub,$visit_type,$transactional_type,$retailer_name,$owner_name,$contact_number,$amount,$create_by,$collection_amount,$billing_amount){
	$link1=$this->link;
	$a=array();
	$todayDate = date('Y-m-d');
	$todayTime = date('H:i:s');
	if($create_by!=''){
	////// update status in task master
	$sql = "SELECT MAX(id) as qa FROM saleperson_task";
	$res = mysqli_query($link1,$sql) or die(mysqli_error($link1));
	$row = mysqli_fetch_assoc($res);
	$code_id = $row["qa"];
	$next_seq = $row["qa"] + 1;
	/// make 3 digit padding
	$pad = str_pad(++$code_id,4,"0",STR_PAD_LEFT);
	$taskid="TK".$pad;
	
	$res1 = mysqli_query($link1,"insert into saleperson_task SET taskid='".$taskid."',taskname='".$taskname."',visit_date='".$visit_date."',task_sub='".$task_sub."',visit_type='".$visit_type."',transactional_type='".$transactional_type."',retailer_name='".$retailer_name."',collection_amount='".$collection_amount."',billing_amount='".$billing_amount."',owner_name='".$owner_name."',contact_number='".$contact_number."',amount='".$amount."',create_by='".$create_by."',create_date='".$todayDate."'") or die(mysqli_error($link1));
	$id=mysqli_insert_id($link1);
		
		if($id!='0' && $id!=''){

        $b["status"]="1";
		$b["error"]="";
		$b["taskid"]=$taskid;

		
		
		
		}else{
			$b['error'] = "Request could not processed. Please try again";
		}
	}else{
		$b['error']="Create by is blank";	
	}

	return $b;
 }
 
////////////////////////////////////////Collect Data/////////////////////
public function save_collect($retailer_code,$collection_mode,$collection_amt,$collection_date,$remark,$collect_by,$collect_sub_mode,$trans_date,$trans_no,$cheque_date,$cheque_no){
	$link1=$this->link;
	$a=array();
	$todayDate = date('Y-m-d');
	$todayTime = date('H:i:s');
	if($collect_by!=''){
	////// update status in task master
	$sql = "SELECT MAX(id) as qa FROM collect_data";
	$res = mysqli_query($link1,$sql) or die(mysqli_error($link1));
	$row = mysqli_fetch_assoc($res);
	$code_id = $row["qa"];
	$next_seq = $row["qa"] + 1;
	/// make 3 digit padding
	$pad = str_pad(++$code_id,4,"0",STR_PAD_LEFT);
	$collect_id="CD".$pad;
	
	///////////////////////
	
$sql_store="select location_code from location_master where sale_person='".$collect_by."' and locationtype='STR'";
		$rs_store=mysqli_query($link1,$sql_store) or die(mysqli_error($link1));
		$row_store=mysqli_fetch_assoc($rs_store);
		$storecode=$row_store['location_code'];
		$wh_row=mysqli_fetch_assoc(mysqli_query($link1,"select wh_location from map_wh_location where status='Y' and location_code='".$storecode."'"));
if($wh_row['wh_location']!=''){
$wh_loc=$wh_row['wh_location'];
}else{
$wh_loc="FIX00017";
}	
	
	
	
	
	
	
	
	///////////////////
	
	$res1 = mysqli_query($link1,"insert into collect_data SET location_code='".$wh_loc."',trans_no='".$trans_no."',trans_date='".$trans_date."',cheque_no='".$cheque_no."',cheque_date='".$cheque_date."',collection_sub_mode='".$collect_sub_mode."',collect_id='".$collect_id."',collection_mode='".$collection_mode."',collect_amt='".$collection_amt."',collect_date='".$collection_date."',remark='".$remark."',collect_by='".$collect_by."',retailer_code='".$retailer_code."'") or die(mysqli_error($link1));
	$id=mysqli_insert_id($link1);
		
		if($id!='0' && $id!=''){

        $b["status"]="1";
		$b["error"]="";
		$b["collect_id"]=$collect_id;

		

		if($collection_mode=="CASH"){

	

			$sql_acc="select account_no from cash_account_master where account_holder_name='".$storecode."'";
			$rs_acc=mysqli_query($link1,$sql_acc);
			$row_acc=mysqli_fetch_assoc($rs_acc);
			$account_no = $row_acc['account_no'];

			mysqli_query($link1,"insert into cash_account_ledger set location_code='".$wh_loc."',account_no='".$account_no."',amount='".$collection_amt."',ref_no='".$collect_id."',remark='APP Collection',month_year='".date('m-Y')."',crdr='CR',entry_date='".$collection_date."',challan_no='".$collect_id."'") or die(mysqli_error($link1));
			
				$res_ac_ledger = mysqli_query($link1,"INSERT INTO party_account_ledger set party='".$retailer_code."',location_code='".$wh_loc."',entry_date='".$collection_date."',remark='APP Collection',transaction_no='".$collect_id."',ref_no='".$collect_id."', transaction_type = 'APP Collection',month_year='".date("m-Y")."',crdr='CR',amount='".$collection_amt."'");
		
		
		}else if($collection_mode=="BANK" && $collect_sub_mode!='Cheque'){

$account_no="510030110000111";
			mysqli_query($link1,"insert into bank_account_ledger set location_code='".$wh_loc."',account_no='".$account_no."',amount='".$collection_amt."',ref_no='".$collect_id."',remark='APP Collection',month_year='".date('m-Y')."',crdr='CR',entry_date='".$collection_date."',challan_no='".$collect_id."'") or die(mysqli_error($link1));

	$res_ac_ledger = mysqli_query($link1,"INSERT INTO party_account_ledger set party='".$retailer_code."',location_code='".$wh_loc."',entry_date='".$collection_date."',remark='APP Collection',transaction_no='".$collect_id."',ref_no='".$collect_id."', transaction_type = 'APP Collection',month_year='".date("m-Y")."',crdr='CR',amount='".$collection_amt."'");
		

		}

	




		
		
		
		}else{
			$b['error'] = "Request could not processed. Please try again";
		}
	}else{
		$b['error']="Create by is blank";	
	}

	return $b;
 }
////////////////////////////////////////////////////
////////////////////////////////////////Collect Data/////////////////////
public function save_bill($retailer_code,$retailer_name,$billing_amt,$billing_date,$remark,$billing_by,$billing_type,$bill_no){
	$link1=$this->link;
	$a=array();
	$todayDate = date('Y-m-d');
	$todayTime = date('H:i:s');
	if($billing_by!=''){
	////// update status in task master
	$sql = "SELECT MAX(id) as qa FROM billing_data";
	$res = mysqli_query($link1,$sql) or die(mysqli_error($link1));
	$row = mysqli_fetch_assoc($res);
	$code_id = $row["qa"];
	$next_seq = $row["qa"] + 1;
	/// make 3 digit padding
	$pad = str_pad(++$code_id,4,"0",STR_PAD_LEFT);
	$billing_id="BD".$pad;
	
	$res11 = mysqli_query($link1,"insert into billing_data SET billing_id='".$billing_id."',billing_amt='".$billing_amt."',billing_date='".$billing_date."',remark='".$remark."',retailer_name='".$retailer_name."',billing_by='".$billing_by."',retailer_code='".$retailer_code."',billing_type='".$billing_type."',bill_no='".$bill_no."'") or die(mysqli_error($link1));
	$id=mysqli_insert_id($link1);
		
		if($id!='0' && $id!=''){

        $b["status"]="1";
		$b["error"]="";
		$b["billing_id"]=$billing_id;

		
		
		
		}else{
			$b['error'] = "Request could not processed. Please try again";
		}
	}else{
		$b['error']="Create by is blank";	
	}

	return $b;
 }

///////////////////////////////////////////////////////////

 public function postsale($imei,$cust_name,$contact_no,$state,$city,$sync_date,$ei,$productname,$serial_no){
	 $a=array();
	 
	$result=mysql_query("insert into sale_data set imei='$imei',cust_name='$cust_name',contact_no='$contact_no',state='$state',city='$city',sync_date='$sync_date',user_name='$ei',productname='$productname',serial_no='$serial_no' ");
	
	 if($result) {  
	 
     $a["response"]=1;
     
   return $a;         
   
   } else {             
  if( mysql_errno() == 1062) {                
 
   $a["response"]=0;
   
   return $a;             
   } else {                 
 
   $a["status"]='no';
                  
 return $a;             
                     
 }
   }// end of else
 }// end of function
 
  /////////////user Attendence Data/////////////
  public function user_attendance($longitu_in,$latitu_in,$in_datetime,$out_datetime,$status_in,$status_out,$ei,$address_in,$address_out,$insert_date,$longitu_out,$latitu_out,$Image_in,$Image_out,$pmodel,$imei,$pmac) {
	$link1=$this->link; 
	$a=array();
	 $todayDate = date('Y-m-d');
	 $todayTime = date('H:i:s');
	 $msg='';
	 $chk_user=mysqli_fetch_array(mysqli_query($link1,"select utype from admin_users where username='$ei'"));
	 
$chk=mysqli_query($link1,"select * from user_attendence where user_id='$ei' and insert_date='$insert_date'");
$chk2=mysqli_fetch_array($chk);
if(mysqli_num_rows($chk)<=0 && $status_in!='' && $chk_user['utype']!='A'){
	//echo "insert into user_attendence set longitude_in='$longitu_in',latitude_in='$latitu_in',longitude_out='$longitu_out',latitude_out='$latitu_out',user_id='$ei',status_in='$status_in',in_datetime='$in_datetime',status_out='$status_out',out_datetime='$out_datetime',address_in='$address_in',address_out='$address_out',insert_date='$insert_date'";
	$result=mysqli_query($link1,"insert into user_attendence set longitude_in='$longitu_in',latitude_in='$latitu_in',longitude_out='$longitu_out',latitude_out='$latitu_out',user_id='$ei',status_in='$status_in',in_datetime='$in_datetime',status_out='$status_out',out_datetime='$out_datetime',address_in='$address_in',address_out='$address_out',insert_date='$insert_date',Image_in='$Image_in',Image_out='$Image_out', pmodel = '$pmodel' , imei = '$imei' , pmac = '$pmac'  ")or die(mysqli_error($link1));
	$ref_no = mysqli_insert_id($link1);
	$latitude = $latitu_in;
	$longitude = $longitu_in;
	$address = $address_in;
}else if(mysqli_num_rows($chk)>0 && $chk2['status_out']==''){
	$result=mysqli_query($link1,"update user_attendence set status_out='$status_out',out_datetime='$out_datetime',address_out='$address_out',longitude_out='$longitu_out',latitude_out='$latitu_out',Image_out='$Image_out' ,pmodel = '$pmodel' , imei = '$imei' , pmac = '$pmac' where id='$chk2[id]'");
	$ref_no = $chk2['id'];
	$latitude = $latitu_out;
	$longitude = $longitu_out;
	$address = $address_out;
}
elseif(mysqli_num_rows($chk)>0 && $chk2[status_in]!='' && $chk2[status_out]!='' ){
	$msg="Data Already Exist!";
}
else if($chk_user['utype']=='A'){
	$msg="You are not authorized for attendence!";
}
else{
	$msg="Plese Log In First!";
}
if($result) { 
   ///// insert in event track
   $resp = $this->UserEventTrack($user_id,$imei,$pmodel,$todayDate,$todayTime,$distance,$latitude,$longitude,$address,"Attendance IN",$ref_no); 
   $a["status"]='yes';
   $a["userid"]=$ei;  
   $a["msg"]=$msg;      
return $a;         
} else {             
if( mysqli_errno($link1) == 1062) {                
 // Duplicate key - Primary Key Violation   
   $a["status"]='yes';
   $a["userid"]=$ei;
   $a["msg"]=$msg;               
 return $a;             
 } else {                 
 // For other errors 
   $a["status"]='no';
   $a["userid"]=$ei;
   $a["msg"]=$msg;                 
 return $a;             
 }                     
 }

}
 ////// function for upload user document
	public function uploadDoc($seid,$docname,$fname,$docfile){
	    	$link1=$this->link; 
		$msgg="";
		$stss="";
		////////// if GST file is uploaded 
		if($docfile!=NULL && $docfile!=""){
			$binary = base64_decode($docfile);
			$result = file_put_contents("user_doc/".$fname,$binary);
			///update status verified in sign up table
			$query1 = mysqli_query($link1,"INSERT INTO document_upload set doc_name= '".$docname."',file_name='".$fname."',upload_by='".$seid."',upload_from='APP'");
			if(!$query1){
				$msgg="Request could not be procesed";
				$stss = "fail";
			}else{
				$msgg = "Document is successfully uploaded.";
				$stss = "success";
			}
		}else{
			$msgg="Request could not be procesed";
			$stss = "fail";
		}
		return $stss."~".$msgg; 
	}
 public function changePassword($user_id,$old_password,$new_password){
     	$link1=$this->link; 
		$b=array();
		//// check old password should be matched
		//echo "SELECT uid,password,status from admin_users where username='".$user_id."'";
		$userdet = mysqli_fetch_assoc(mysqli_query($link1,"SELECT uid,password,status from admin_users where username='".$user_id."' or phone = '".$user_id."' or emailid = '".$user_id."'"));
		//// check userid is active or deactive
		if($userdet["status"] == "active"){
			///// check old password is correct or not
			if($userdet["password"] == $old_password){
				$result = mysqli_query($link1,"UPDATE admin_users set password = '".$new_password."' where uid='".$userdet["uid"]."'");
				if($result){
					$b['response'] = "Password is changed successfully.";	
				}else{
					$b['response'] = "Something went wrong. Please try again.";
				}
			}else{
				$b['response'] = "Old password is not matched.";
			}
		}else{
			$b['response'] = "Userid is deactivated.";
		}
		return $b;
	}
	///// function to get sale order qty as well sale qty associate with requested sales executive
	public function getSaleReport($user_id,$from_date,$to_date){
	    $link1=$this->link;
		if($from_date){ 
			$f_date = $from_date;
			$t_date = $to_date;
		}else{ 
			$f_date = date("Y-m-d");
			$t_date = date("Y-m-d");
		}
		////// get sale order qty
		$res_so = mysqli_query($link1,"SELECT b.prod_code, b.req_qty, b.qty FROM purchase_order_master a, purchase_order_data b WHERE a.po_no=b.po_no and a.sales_executive='".$user_id."' and a.entry_date >= '".$f_date."' and a.entry_date <= '".$t_date."' GROUP BY b.prod_code");
		return $res_so;
	}
	///// function to get selected task history
	public function taskHistory($refid){
	    	$link1=$this->link; 
		////// get sale order qty
		$res_th = mysqli_query($link1,"SELECT * FROM save_task_report WHERE id='".$refid."' ORDER BY updatedate");
		return $res_th;
	}
	///// function to get leave list of employee ////
	public function LeaveList($ei){
	    	$link1=$this->link; 
		$result = mysqli_query($link1,"SELECT * FROM hrms_leave_request where empid='".$ei."'") or die(mysqli_error($link1)); 
		return $result;	 
 	}
	/////// add Add Leave for employee /////
 	public function addLeave($empid , $leave_type , $from_date , $to_date , $purpose , $leave_description, $leave_duration,$sub_leave_type){
 	    	$link1=$this->link; 
		$todayDate = date('Y-m-d');
		$todayTime = date('H:i:s');
		
		$query = "INSERT INTO hrms_leave_request SET empid='".$empid."',leave_type='".$leave_type."',from_date='".$from_date."',to_date='".$to_date."',leave_duration='".$leave_duration."',purpose='".$purpose."',description='".$leave_description."',status='Pending',entry_date='".$todayDate."',entry_time='".$todayTime."',sub_leave_type='".$sub_leave_type."'";
		$result = mysqli_query($link1,$query) or die(mysqli_error($link1)); 
		if($result){
			return 1;
		}else{
			return 0;
		}
 	}
	/////// Leave view of employee /////
 	public function LeaveItems($ref_no){
		 $link1=$this->link;
		return $result = mysqli_query($link1,"SELECT * FROM hrms_leave_request WHERE id='".$ref_no."'");
	}
	/////// get employee salary slip details //////
	public function SalarySlipItems($emp_id, $salary_month, $salary_year){
		return $result = mysql_query("SELECT * FROM hrms_salary_upload WHERE emp_id='".$emp_id."' and salary_month='".$salary_month."' and salary_year='".$salary_year."'");
	}
	/////// Leave cancel of employee /////
 	public function LeaveCancel($ref_no, $cancel_by, $cancel_remark){
 		$todayDate = date('Y-m-d');
		$todayTime = date('H:i:s');
		
		$result = mysql_query("UPDATE hrms_leave_request SET cancel_by = '".$cancel_by."', cancel_date = '".$todayDate."', cancel_time = '".$todayTime."', cancel_remark = '".$cancel_remark."' WHERE id='".$ref_no."'");
		if($result){
			return 1;
		}else{
			return 0;
		}	
	}
	////// function for activity add
	public function ActivityAdd($activity_name,$activity_desc,$file_name,$attach_file,$create_by){
		$todayDate = date('Y-m-d');
		$todayTime = date('H:i:s');
		
		////////// if activity file is uploaded 
		if($attach_file!=NULL && $attach_file!=""){
			$binary = base64_decode($attach_file);
			$result = file_put_contents("activity_file/".$file_name,$binary);
		}
	
		////// count max no. of activity in selected state
		$query_code="select MAX(temp_no) as tn from hrms_activities_master where 1 ";
		$result_code=mysql_query($query_code);
		if(!$result_code){
			$err_msg = "ER1".mysql_error();
		}
		$arr_result2=mysql_fetch_array($result_code);
		$code_id=$arr_result2[0];
		/// make 6 digit padding
		$tmp_no = $code_id+1;
		$pad=str_pad($tmp_no,6,"0",STR_PAD_LEFT);		
		$activate_no = "A".$pad; 
		
		///insert data into db table
		$query1 = mysql_query("INSERT INTO hrms_activities_master set temp_no = '".$tmp_no."', activity_no ='".$activate_no."', activity_desc = '".$activity_name."', activity_detail = '".$activity_desc."', file_name = '".$file_name."', file_path = '".$attach_file."', status = 'Active', create_by = '".$create_by."', create_date = '".$todayDate."'");
		
		if($query1){
			///insert data into db table
			$query2 = mysql_query("INSERT INTO daily_activities set userid = '".$create_by."', ref_no ='".$activate_no."', activity_type = 'ACTIVITY', action_taken = 'ADD', update_date = '".$todayDate."', update_time = '".$todayTime."', system_ip = 'app_user'");
		}
		
		if($query2){
			return 1;
		}else{
			return 0;
		}		
	}
	///// function to get activity list ////
	public function ActivityList($activity_no){
		$result = mysql_query("SELECT * FROM hrms_activities_master where activity_no = '".$activity_no."'"); 
		return $result;	 
 	}
 	/////// Activity view of employee /////
 	public function ActivityItems($ref_no){
		return $result = mysql_query("SELECT * FROM hrms_activities_master WHERE activity_no = '".$ref_no."'");
	}
	////// function for activity add
	public function ActivityFeedbackAdd($activity_no,$activity_remark,$file_name,$attach_file,$create_by){
		$todayDate = date('Y-m-d');
		$todayTime = date('H:i:s');
		
		////////// if activity file is uploaded 
		if($attach_file!=NULL && $attach_file!=""){
			$binary = base64_decode($attach_file);
			$result = file_put_contents("activity_feedback_file/".$file_name,$binary);
		}
			
		///insert data into db table
		$query1 = mysql_query("INSERT INTO hrms_activities_feedback set activity_no ='".$activity_no."', feedback = '".$activity_remark."', file_name = '".$file_name."', file_path = '".$attach_file."', update_by = '".$create_by."', update_date = '".$todayDate."'");
		
		if($query1){
			///insert data into db table
			$query2 = mysql_query("INSERT INTO daily_activities set userid = '".$create_by."', ref_no ='".$activity_no."', activity_type = 'FEEDBACK', action_taken = 'ADD', update_date = '".$todayDate."', update_time = '".$todayTime."', system_ip = 'app_user'");
		}
		
		if($query2){
			return 1;
		}else{
			return 0;
		}		
	}
	///// function to get activity feedback list ////
	public function ActivityFeedBackList($activity_no){
		$result = mysql_query("SELECT * FROM hrms_activities_feedback where activity_no = '".$activity_no."'"); 
		return $result;	 
 	}
	//// get lat long
	public function liveTrack($uid,$latitude,$longitude,$deviceid){
		$res_lt = mysql_query("INSERT INTO live_track SET userid='".$uid."' , latitude='".$latitude."' , longitude='".$longitude."' , device_id='".$deviceid."'");
		if($res_lt){
			return "1";	
		}else{
			return "0";	
		}
	}


	//// get lat long
	public function noticelist($userid){
		$link1=$this->link;
	//	echo "select * from hrms_notice where emp_id='".$userid."'";
		$result = mysqli_query($link1,"select * from hrms_notice where emp_id='".$userid."'");
		return $result;
	}

public function retail_outstanding($user_id){
    	$link1=$this->link;
$a=array();
$b=array();
$c=array();
$total=0;
$sql="select location_code,locationname from location_master where create_by='".$user_id."' and statusid='1' and locationtype='RET'";
$rs=mysqli_query($link1,$sql) or die(mysqli_error($link1));
while($row=mysqli_fetch_assoc($rs)){

$sql_out="select * from retailer_outstanding where retailer='".$row['location_code']."'";
$rs_out=mysqli_query($link1,$sql_out) or die(mysqli_error($link1));
$row_out=mysqli_fetch_assoc($rs_out);
if($row_out['outstanding']!=''){
    $outstanding=$row_out['outstanding'];
}else{
    
 $outstanding=0;   
}
$retailer_code=$row['location_code'];
$wh_loc="FIX00017";
$a['retailer']=$row['location_code'];
$a['retailer_name']=$this->getAnyDetails($row['location_code'],"locationname","location_code","location_master",$link1);
$a['outstanding']=round($this->getoustanding($wh_loc,$retailer_code,$link1));
$out=round($this->getoustanding($wh_loc,$retailer_code,$link1)); 
$total=$total+round($out);

array_push($b,$a);
}

$c['userid']=$user_id;
$c['TotalOutstanding']=$total;
$c['retailer_outstanding']=$b;

return $c;
}


function createST($uid,$prodList){
$a=array();
$link1=$this->link;
if($uid!='' || count($prodList)!=0){

$sql="select location_code from location_master where sale_person='".$uid."' and locationtype='STR'";
$rs=mysqli_query($link1,$sql) or die(mysqli_error($link1));
$row=mysqli_fetch_assoc($rs);
$partycode=$row['location_code'];
$entry_date=$today=date("Y-m-d");

$res_po=mysqli_query($link1,"select max(po_id) as no from po_master where from_code='".$partycode."'");
$row_po=mysqli_fetch_array($res_po);
$c_nos=$row_po['no']+1;
$po_no=$partycode."PO".$c_nos; 

$wh_row=mysqli_fetch_assoc(mysqli_query($link1,"select wh_location from map_wh_location where status='Y' and location_code='".$partycode."'"));
if($wh_row['wh_location']!=''){
$wh_loc=$wh_row['wh_location'];
}else{
	$wh_loc="FIX00017";
}

$fromaddress = explode ("~" ,$this->getAnyDetails($partycode,"locationaddress,stateid","location_code","location_master",$link1));
$locinfo= mysqli_fetch_array(mysqli_query($link1,"select * from location_master where location_code='".$wh_loc."' "));

$usr_add="INSERT INTO po_master set from_store='".$partycode."',po_no='".$po_no."', po_date='".$today."' , to_code ='".$wh_loc."' , to_address='".$locinfo['locationaddress']."' ,to_state='".$locinfo['stateid']."',potype='APP', update_date='".$today."',entry_by='".$uid."' ,entry_ip ='".$_SERVER['REMOTE_ADDR']."' ,status='1' ,from_code= '".$partycode."',  	from_address = '".$fromaddress[0]."' ,po_id='".$c_nos."' , 	from_state = '".$fromaddress[1]."'  ";
$result=mysqli_query($link1,$usr_add);
$poid = mysqli_insert_id($link1);

for($i=0; $i<count($prodList) ; $i++) { 
	
	$part_code =  $prodList[$i]["partcode"];
	$qty =  $prodList[$i]["qty"];

	$query2="insert into po_items set  po_value='0',po_price='0',po_id ='".$poid."' , po_no='".$po_no."',partcode ='".$part_code."',type = 'APP', qty='".$qty."' , update_date= '".$today."' ,status='1'";
	$result = mysqli_query($link1, $query2);
}
$a['ST NO']=$po_no;  
$a["msg"]="Successfully";
} else{
    $a['msg']="something went wrong";
}   
return $a;    
}

function storeInventory($uid){
    $a=array();
    $b=array();
	$link1=$this->link;
	$store="FIX00100";
$sql="select okqty,partcode from store_inventory where location_code='".$store."'";
$rs=mysqli_query($link1,$sql) or die(mysqli_error($link1));
if(mysqli_num_rows($rs)>0){
 while($row=mysqli_fetch_assoc($rs)){  
$a['uid']=$uid;
$a['partcode']=$row['partcode'];
$a['qty']=$row['okqty'];
 array_push($b,$a);   
}}
return $b;
}

 public function stOrderList($ei){
       $link1=$this->link;
      // echo "SELECT * FROM purchase_order_master where create_by='".$ei."'";
	$result=mysqli_query($link1,"SELECT * FROM po_master where entry_by='".$ei."' and potype='APP'");

	return $result;	 
 }


 public function stOrderview($ei,$st_no){
     $a=array();
     $b=array();
     $c=array();
	$link1=$this->link;
   // echo "SELECT * FROM purchase_order_master where create_by='".$ei."'";
 $result=mysqli_query($link1,"SELECT * FROM po_master where entry_by='".$ei."' and po_no='".$st_no."'");
 $row=mysqli_fetch_assoc($result);
 $a["st_no"]=$st_no;
 $a['po_from']=$row['from_code'];
 $a['po_to']=$row['to_code'];
 $a['entry_date']=$row['update_date'];
 $a['status']=$this->getdispatchstatus($row['status']);

 $result1=mysqli_query($link1,"SELECT * FROM po_items where po_no='".$st_no."'");
 while($row1=mysqli_fetch_assoc($result1)){
$partname=$this->getAnyDetails($row1['partcode'],"part_name","partcode","partcode_master",$link1);
$c['partcode']=$row1['partcode'];
$c['partname']=$partname;
$c['req_qty']=$row1['qty'];
$c['disp_qty']=$row1['disp_qty'];
$c['rec_qty']=$row1['rec_qty'];
 $c['sub_status']=$this->getdispatchstatus($row1['status']);

array_push($b,$c);  
}
$a["request_detail"]=$b;
 return $a;	 
}

function receiveST($uid,$st_no,$prodList){
	$a=array();
	$link1=$this->link;
	
	
	if($uid!='' || count($prodList)!=0){
	
		$entry_date=date("Y-m-d");
		$entry_time=date("H:i:s");
		$datetime=date("Y-m-d H:i:s");
	///////////storecode////////////////	
		$sql="select location_code from location_master where sale_person='".$uid."' and locationtype='STR'";
$rs=mysqli_query($link1,$sql) or die(mysqli_error($link1));
$row=mysqli_fetch_assoc($rs);
$partycode=$row['location_code'];
	
	$wh_row=mysqli_fetch_assoc(mysqli_query($link1,"select wh_location from map_wh_location where status='Y' and location_code='".$partycode."'"));
if($wh_row['wh_location']!=''){
$wh_loc=$wh_row['wh_location'];
}else{
	$wh_loc="FIX00017";
}
///////////////////////////////////////////////////////
	

	
	


	for($i=0; $i<count($prodList) ; $i++) { 
		
		$part_code =  $prodList[$i]["partcode"];
		$qty =  $prodList[$i]["req_qty"];
		$disp_qty =  $prodList[$i]["disp_qty"];
		$rec_qty=$prodList[$i]["rec_qty"];
	

	
$sql_part="select * from po_items where po_no='".$st_no."' and po_no!='' and status in ('2','6')";
$rs_part=mysqli_query($link1,$sql_part) or die(mysqli_error($link1));
if(mysqli_num_rows($rs_part)>0){
$row_part=mysqli_fetch_assoc($rs_part);
	
	/////////update store inventory/////////
		if(mysqli_num_rows(mysqli_query($link1,"select partcode from store_inventory where partcode='".$part_code."' and location_code='".$partycode."'"))>0){
  
  
			   ///if product is exist in inventory then update its qty 
  
  
			  $result=mysqli_query($link1,"update store_inventory set okqty =okqty+'".$rec_qty."',updatedate='".$datetime."' where partcode='".$part_code."' and location_code='".$partycode."'");
  
  
			}		
  
  
			else{			
  
  
			   //// if product is not exist then add in inventory
  
  
  
			   $result=mysqli_query($link1,"insert into store_inventory set location_code='".$partycode."',partcode='".$part_code."',okqty='".$rec_qty."',updatedate='".$datetime."'");
  
  
  
			}	


  
   $result=mysqli_query($link1,"update client_inventory set  	okqty  =okqty+'".$rec_qty."',updatedate='".$datetime."' where partcode='".$part_code."' and location_code='".$wh_loc."'");
  
  	mysqli_query($link1,"update po_items set status='4',rec_qty='".$rec_qty."' where po_no='".$st_no."' and partcode='".$part_code."' and po_no!='' and status in ('2','6')") or die(mysqli_error($link1));
		}
  
/////////////////////////////////////////////////	
	
	
	}
	

	
	mysqli_query($link1,"update po_master set receive_date='".$entry_date."',status='4' where po_no='".$st_no."' and po_no!=''") or die(mysqli_error($link1));

	$a['ST NO']=$st_no;  
	$a["msg"]="Update Successfully";
	} else{
		$a['msg']="something went wrong";
	}   
	return $a;    
	}
	
///stock receive funcation end here////////
public function DirectInvList($ei){
       $link1=$this->link;
      // echo "SELECT * FROM purchase_order_master where create_by='".$ei."'";
	$result=mysqli_query($link1,"SELECT * FROM billing_master where sale_person='".$ei."' and approved_by='APP'");

	return $result;	 
 }
 ///////////////invoice list function end here//////////////

public function DirectInvview($ei,$invoice_no){
     $a=array();
     $b=array();
     $c=array();
	$link1=$this->link;
//	echo "SELECT * FROM billing_master where sale_person='".$ei."' and challan_no='".$invoice_no."'";
  
 $result=mysqli_query($link1,"SELECT * FROM billing_master where sale_person='".$ei."' and challan_no='".$invoice_no."'");
 $row=mysqli_fetch_assoc($result);

 $a['from_location']=$row['from_location'];
 $a['from']=$this->getAnyDetails($row['from_location'],"locationname","location_code","location_master",$link1);
 $a['to_location']=$row['to_location'];
 $a["to"]=$this->getAnyDetails($row['to_location'],"locationname","location_code","location_master",$link1);
 $a['invoice_no']=$row['challan_no'];
 $a['invoice_date']=$row['sale_date'];
 
 $a['status']=$this->getdispatchstatus($row['status']);

 $result1=mysqli_query($link1,"SELECT * FROM billing_product_items where challan_no='".$invoice_no."'");
 while($row1=mysqli_fetch_assoc($result1)){
$partname=$this->getAnyDetails($row1['partcode'],"part_name","partcode","partcode_master",$link1);
$c['partcode']=$row1['partcode'];
$c['partname']=$partname;
$c['qty']=$row1['qty'];

array_push($b,$c);  
}
$a["part_detail"]=$b;
 return $a;	 
}
////////////////////////////////////

public function collection_list_test($ei,$retailer_code,$from_date,$to_date){
	$link1=$this->link;
	if($retailer_code!=''){
	    $ret="and retailer_code='".$retailer_code."'";
	}else{
	    $ret="";
	}
	
	if($from_date!='' && $to_date!=''){
	    $datefilter="and collect_date between '".$from_date."' and '".$to_date."'";
	}
	//echo "SELECT * FROM task_master where 1 and (status='pending') or (status='open') or (status='in-progress')";
	$result=mysqli_query($link1,"SELECT * FROM collect_data where collect_by='$ei' $ret $datefilter");
	return $result;
}

public function get_avl($uid,$partcode){
    $link1=$this->link;
    $sql_store="select location_code from location_master where locationtype='STR' and sale_person='".$uid."' and statusid='1' and sale_person!=''";
    $rs_store=mysqli_query($link1,$sql_store) or die(mysqli_error($link1));
    if(mysqli_num_rows($rs_store)>0){
      $row_store=mysqli_fetch_assoc($rs_store);  
     $store=$row_store['location_code'];
$sql="select okqty,partcode from store_inventory where location_code='".$store."' and partcode='".$partcode."'";
$rs=mysqli_query($link1,$sql) or die(mysqli_error($link1));
if(mysqli_num_rows($rs)>0){  
     $row=mysqli_fetch_assoc($rs);   
     
     $a=$row['okqty'];   
        
        
    }else{
        $a=0;
    }}else{
     $a=0;   
    }
    return $a;
}

function createINV($sale_person,$retailer_code,$part_List){
$a=array();
$link1=$this->link;
$today=date("Y-m-d");
$datetime=date("Y-m-d H:i:s");
$today=date("Y-m-d");
	  mysqli_autocommit($link1, false);
	  $flag = true;
	  $error_msg = "";
if($sale_person!='' && $retailer_code!='' & count($part_List)!=0){
$sql_store="select location_code from location_master where sale_person='".$sale_person."' and locationtype='STR'";
$rs_store=mysqli_query($link1,$sql_store) or die(mysqli_error($link1));
$row_store=mysqli_fetch_assoc($rs_store);
$storecode=$row_store['location_code'];

$wh_row=mysqli_fetch_assoc(mysqli_query($link1,"select wh_location from map_wh_location where status='Y' and location_code='".$storecode."'"));
if($wh_row['wh_location']!=''){
$wh_loc=$wh_row['wh_location'];
}else{
$wh_loc="FIX00017";    
}

		$sql_invcount = "SELECT * FROM invoice_counter where location_code='".$wh_loc."'";

		$res_invcount = mysqli_query($link1,$sql_invcount)or die("error1".mysqli_error($link1));

		$row_invcount = mysqli_fetch_array($res_invcount);

		$next_invno = $row_invcount['dinv_counter']+1;

		/////update next counter against invoice

		$res_upd = mysqli_query($link1,"UPDATE invoice_counter set dinv_counter = '".$next_invno."' where location_code='".$wh_loc."'");


        if(!$res_upd){

			$flag = false;

			$error_msg = "Error1". mysqli_error($link1) . ".";

		}

        $invoice_no =$row_invcount['dinv_series']."".$row_invcount['fy']."".str_pad($next_invno,4,"0",STR_PAD_LEFT);	

	
		$doctype_flag= 1;

$locationcode=$retailer_code;
		////// PO dispatcher
	 $fromlocdet = explode("~",$this->getAnyDetails($wh_loc,"locationname,locationaddress,dispatchaddress,deliveryaddress,cityid,stateid,zipcode,emailid,gstno","location_code","location_master",$link1));
	 ////// PO receiver
	 $tolocdet = explode("~",$this->getAnyDetails($locationcode,"locationname,locationaddress,dispatchaddress,deliveryaddress,cityid,stateid,zipcode,emailid,gstno","location_code","location_master",$link1));
 

////////////////GST Calculation////////////////
  //////intialize tax variables

  $sgst_final_val=0;

  $cgst_final_val=0;

  $igst_final_val=0;

  $basic_cost=0;

  $total_qty = 0;

  $total_reqqty = 0;

  $total_procqty = 0;


  for($i=0; $i<count($part_List) ; $i++) { 
		
	$partcode =  $part_List[$i]["partcode"];
	
	$req_qty=$part_List[$i]["req_qty"];  
	$price=$part_List[$i]["price"];

	  // checking row value of product and qty should not be blank
	  if($partcode!='' && $req_qty!='' && $req_qty!=0) {
	  $partdet = explode("~",$this->getAnyDetails($partcode , "hsn_code,part_name" ,"partcode", "partcode_master" ,$link1));
	  
	  
	  $res_tax = mysqli_query($link1,"SELECT id,sgst,igst,cgst FROM tax_hsn_master where hsn_code='".$partdet[0]."'");

		$row_tax = mysqli_fetch_assoc($res_tax) ;

		if($row_tax['id']==""){

			$flag=false;

			$error_msg="Tax not found in HSN TAX MASTER";

		}
	  
	  $linetotal=$req_qty*$price;
$parttaxable=$linetotal/1.18;
$price=$price/1.18;

	  ////// initialize line tax variables

		$cgst_per=0;

		$cgst_val=0;

		

		$sgst_per=0;

		$sgst_val=0;

		

		$igst_per=0;

		$igst_val=0;

		

		$tot_val=0;
		 
//// check if dispatcher and receiver belongs to same state then tax should be apply as SGST&CGST (In india) 

if($fromlocdet['5'] == $tolocdet['5']){

		//----------------------------- CGST & SGST Applicable----------------------//

			

				$cgst_per = 9;

				$sgst_per = 9;

			

			/////// calculate cgst and sgst	

			$cgst_val = $parttaxable*0.09;

			$cgst_final_val = $cgst_final_val + $cgst_val;

			

			$sgst_val = $parttaxable*0.09; 

			$sgst_final_val = $sgst_final_val + $sgst_val;



			$basic_cost = $basic_cost + $parttaxable;	

			$tot_val = $parttaxable + $cgst_val + $sgst_val;	

		}else{//// check if dispatcher and receiver belongs to different state then tax should be apply as IGST (In india) 

			//----------------------------- IGST Applicable----------------------//

			

				$igst_per = 18;

		

			/////// calculate igst

			$igst_val = $parttaxable*0.18;

			$igst_final_val = $igst_final_val + $igst_val;

		

			$basic_cost = $basic_cost + $parttaxable;

			$tot_val = $parttaxable + $igst_val;

		}
$partdiscountper="";
$prod_code="";
$model="";
$partdiscount=0;

		$sql_billdata = "INSERT INTO billing_product_items set discount_per='".$partdiscountper."',from_location='".$wh_loc."', to_location='".$locationcode."',challan_no='".$invoice_no."',type='PO', hsn_code='".$partdet[0]."',product_id ='".$prod_code."', brand_id ='".$brand."', model_id ='".$model."', partcode ='".$partcode."', part_name='".$partdet[1]."',qty ='".$req_qty."' ,okqty ='".$req_qty."' , price='".$price."',uom='PCS',value='".$parttaxable."',basic_amt='".$parttaxable."',cgst_per='".$cgst_per."',cgst_amt='".$cgst_val."',sgst_per='".$sgst_per."',sgst_amt='".$sgst_val."',igst_per='".$igst_per."',igst_amt='".$igst_val."',item_total='".$tot_val."',taxable='".$parttaxable."',discount_amt='".$partdiscount."',stock_type='okqty'";
	   
		// echo "<br><br>";
		 
			   $res_billdata = mysqli_query($link1,$sql_billdata)or die("error 3".mysqli_error($link1));
					//// check if query is not executed
					if (!$res_billdata) {
						$flag = false;
						$error_msg = "Error details3: " . mysqli_error($link1) . ".";
					}
	
					////////////// update  okqty in client inventory table //////////////////////////////////////////////////////////	 
			//	  echo "UPDATE client_inventory set okqty = okqty - '".$req_qty."',updatedate='" . $datetime . "' where partcode='".$partcode."' and location_code='testwh' and  okqty >= '".$req_qty."' ";
												   
				   $client   = mysqli_query($link1 ,"UPDATE client_inventory set okqty = okqty - '".$req_qty."',updatedate='" . $datetime . "' where partcode='".$partcode."' and location_code='".$wh_loc."' and  okqty >= '".$req_qty."' ");
		$client1=mysqli_affected_rows($link1);
				//// check if query is not executed
				if (!$client1) {
					$flag = false;
					$error_msg = "Error details7: " . mysqli_error($link1) . ".";
				}	
				
				
			  // echo "UPDATE store_inventory set okqty = okqty - '".$req_qty."',updatedate='" . $datetime . "' where partcode='".$partcode."' and location_code='".$storecode."' and  okqty >= '".$req_qty."' ";
												 
				$store_inv   = mysqli_query($link1 ,"UPDATE store_inventory set okqty = okqty - '".$req_qty."',updatedate='" . $datetime . "' where partcode='".$partcode."' and location_code='".$storecode."' and  okqty >= '".$req_qty."' ");
			
				$store_inv1=mysqli_affected_rows($link1);
				//// check if query is not executed
				if (!$store_inv1) {
					$flag = false;
					$error_msg = "Error details7: " . mysqli_error($link1) . ".";
				}	
  
				//////////////////////// for IN transit QTY update///////////////////////////////// 
  
  
				if(mysqli_num_rows(mysqli_query($link1,"select partcode from client_inventory where partcode='".$partcode."' and location_code='".$wh_loc."'"))>0){
  
  
			   ///if product is exist in inventory then update its qty 
  
  
			  $result=mysqli_query($link1,"update client_inventory set  	in_transit  =  	in_transit+'".$req_qty."',updatedate='".$datetime."' where partcode='".$partcode."' and location_code='".$wh_loc."'");
  
  
			}		
  
  
			else{			
  
  
			   //// if product is not exist then add in inventory
  
  
  
			   $result=mysqli_query($link1,"insert into client_inventory set location_code='".$wh_loc."',partcode='".$partcode."',in_transit='".$req_qty."',updatedate='".$datetime."'");
  
  
  
			}	
  
	  
			$this->stockLedger($invoice_no,$today,$partcode,$wh_loc,$locationcode,"OUT","OK","Direct Dispatch","Process",$req_qty,$price,$sale_peron,$today,$createtime,$ip,$link1,$errorflag);

            $this->storestockLedger($invoice_no,$today,$partcode,$wh_loc,$storecode,"OUT","OK","Direct Dispatch","Process",$req_qty,$price,$create_by,$createdate,$createtime,$ip,$link1,$errorflag);


	}
	//////////////////if under for lood end here////////////////////////////////
	$inv_tot_cost = $basic_cost + $cgst_final_val + $sgst_final_val + $igst_final_val;

}
///////////////////////for lood end here////////////////////////////////

  

 

$sql_billmaster = "INSERT INTO billing_master set discount_type='".$discounttype."',sub_discount_type='".$subdiscounttype."',discount_per='".$total_discount_per."',store_location='".$storecode."',from_location='".$wh_loc."', to_location='".$locationcode."',from_gst_no='".$fromlocdet[8]."',to_gst_no='".$tolocdet[8]."',party_name='".$tolocdet[0]."',challan_no='".$invoice_no."',sale_date='".$today."',entry_date='".$today."',entry_time='".$currtime."',logged_by='".$wh_loc."',billing_rmk='Against ".$remark."',bill_from='".$fromlocdet[0]."',from_stateid='".$fromlocdet['5']."',to_stateid='".$tolocdet[5]."' ,bill_to='".$tolocdet[0]."',from_addrs='".$fromlocdet[1]."',disp_addrs='".$fromlocdet[2]."',to_addrs='".$tolocdet[1]."',deliv_addrs='".$tolocdet[3]."',status='2',document_type='INV',po_type='PO',basic_cost='".$basic_cost."',  total_cost='".$inv_tot_cost."',cgst_amt='".$cgst_final_val."',sgst_amt='".$sgst_final_val."',igst_amt='".$igst_final_val."',discount_amt='".$tot_dis_amt."',purpose='Direct PO Dispatch', sale_person='".$sale_person."',approved_by='APP'"; 
 
	
	  $res_billmaster = mysqli_query($link1,$sql_billmaster)or die("error 5".mysqli_error($link1));
		  //// check if query is not executed
	if (!$res_billmaster) {
		$flag = false;
		$error_msg = "Error details6: " . mysqli_error($link1) . ".";
	}	 
	
	$res_ac_ledger = mysqli_query($link1,"INSERT INTO party_account_ledger set party='".$locationcode."',location_code='".$wh_loc."',entry_date='".$today."',remark='Direct PO Dispatch($invoice_no)',transaction_no='".$invoice_no."',ref_no='".$invoice_no."', transaction_type = 'Direct PO Process',month_year='".date("m-Y")."',crdr='DR',amount='".$inv_tot_cost."'");

$res_ac_ledger = mysqli_query($link1,"INSERT INTO party_account_ledger set location_code='".$locationcode."',party='".$wh_loc."',entry_date='".$today."',remark='Direct PO Dispatch($invoice_no)',transaction_no='".$invoice_no."',ref_no='".$invoice_no."', transaction_type = 'Direct PO Process',month_year='".date("m-Y")."',crdr='CR',amount='".$inv_tot_cost."'");

if(!$res_ac_ledger){

	$flag = false;

	$error_msg = "Error details8: " . mysqli_error($link1) . ".";

}
if ($flag) {
        mysqli_commit($link1);

		$loc_name=$this->getAnyDetails($locationcode,"locationname","location_code","location_master",$link1);	
	$mob=$this->getAnyDetails($locationcode,"contactno1","location_code","location_master",$link1);
	$outstanding=$this->getoustanding($wh_loc,$locationcode,$link1);  
	 

        if($mob!=''){
     $url_inv="http://crm.fixxaphone.co.in/print/print_invoice_link.php?id=".base64_encode($invoice_no);    
	$sms=urlencode("Dear ".$loc_name.", Fixx A Phone has generated an Invoice ".$invoice_no." dated ".date("d-m-Y")." of Value ".$inv_tot_cost." against you and your current outstanding is ".$outstanding." . Thank you for being with us.Download Your Invoice From:".$url_inv);
	
$loc_mobile="91".$mob;
$whatsapp_url="https://dash.whatybot.com/api/send.php?client_id=1dab36d0d031244f64c38887f4cf99e9&instance=a3fd513d06500166cb9985c224492799&number=".$loc_mobile."&message=".$sms."&type=text";

$contents1 =file_get_contents($whatsapp_url);

$mobile2="918984500147";
            
                     $whatsapp_url2="https://dash.whatybot.com/api/send.php?client_id=1dab36d0d031244f64c38887f4cf99e9&instance=a3fd513d06500166cb9985c224492799&number=".$mobile2."&message=".$sms."&type=text";
            $contents2 =file_get_contents($whatsapp_url2);
            
    if($sale_person=='FIUSR015'){
        $mobile3="919938662066";
    }else if($sale_person=='FIEMP0045'){
        $mobile3="917008833232";
    }        
  if($mobile3!=''){          
   $whatsapp_url3="https://dash.whatybot.com/api/send.php?client_id=1dab36d0d031244f64c38887f4cf99e9&instance=a3fd513d06500166cb9985c224492799&number=".$mobile3."&message=".$sms."&type=text";
            $contents3 =file_get_contents($whatsapp_url3);
  }
                     
        }

		$cflag = "success";
		$cmsg = "Success";
        $msg = "STN is successfully placed with ref. no.".$invoice_no;
        $a["invoice_no"]=$invoice_no;
        $a["msg"]="Update Successfully";
    } else {
		mysqli_rollback($link1);
		$cflag = "danger";
		$cmsg = "Failed";
		$msg = "Request could not be processed. Please try again.".$error_msg;
		 $a["invoice_no"]="";
        $a["msg"]="something went wrong";
	} 
	
}else{
  $a["invoice_no"]="";
 $a["msg"]="something went wrong";   
}	//echo $msg."      ";
	
    mysqli_close($link1);
    return $a;
 
}

 public function streceiveList($ei){
       $link1=$this->link;
      // echo "SELECT * FROM purchase_order_master where create_by='".$ei."'";
	$result=mysqli_query($link1,"SELECT * FROM billing_master where po_by='".$ei."' and po_type='APP'");

	return $result;	 
 }
 
 public function streceiveview($ei,$st_no){
     $a=array();
     $b=array();
     $c=array();
	$link1=$this->link;
   // echo "SELECT * FROM purchase_order_master where create_by='".$ei."'";
 $result=mysqli_query($link1,"SELECT * FROM billing_master  where challan_no='".$st_no."'");
 $row=mysqli_fetch_assoc($result);

 $a['po_from']=$row['from_location'];
 $a['po_to']=$row['to_location'];
 $a['entry_date']=$row['sale_date'];
 $a['status']=$this->getdispatchstatus($row['status']);

 $result1=mysqli_query($link1,"SELECT * FROM billing_product_items where challan_no='".$st_no."'");
 while($row1=mysqli_fetch_assoc($result1)){
$partname=$this->getAnyDetails($row1['partcode'],"part_name","partcode","partcode_master",$link1);
$c['partcode']=$row1['partcode'];
$c['partname']=$partname;
$c['qty']=$row1['qty'];
$c['disp_qty']=$row1['disp_qty'];
$c['rec_qty']=$row1['rec_qty'];
$c['part_status']=$this->getdispatchstatus($row1['status']);
array_push($b,$c);  
}
$a["request_detail"]=$b;
 return $a;	 
}
 /////////////////////////////////////////
 function stockReturn($sale_person,$retailer_code,$part_List){

	if($retailer_code!='' && $part_List!=''){


		$a['return_no']="RT0001";
		$a["msg"]="Return Successful";

	}else{
$a["msg"]="Something Went wrong";
	}

 return $a;
}

 function stockReturnList($sale_person){
$a=array();
$b=array();
$link1=$this->link;
$sql="select * from billing_master where sale_person='".$sale_person."' and po_type='Sale Return'";
$rs=mysqli_query($link1,$sql) or die(mysqli_error($link1));
while($row=mysqli_fetch_assoc($rs)){
    
$b["invoice_no"] = $row["challan_no"];
  	$b["from_location"] = $row["from_location"];
	  $b["from"]=$this->getAnyDetails( $row["from_location"],"locationname","location_code","location_master");


	$b["to_location"] = $row["to_location"];
$b["to"] = $this->getAnyDetails( $row["to_location"],"locationname","location_code","location_master");
	
  	$b["entry_date"] = $row["sale_date"];
	 $b["invoice_value"]=$row["total_cost"]; 
  
	$b["status"] =$this->getdispatchstatus($row['status']) ;
    
    
 array_push($a,$b);   
    
    
}


 return $a;
}

public function stockreturnview($ei,$invoice_no){
     $a=array();
     $b=array();
     $c=array();
	$link1=$this->link;
//	echo "SELECT * FROM billing_master where sale_person='".$ei."' and challan_no='".$invoice_no."'";
  
 $result=mysqli_query($link1,"SELECT * FROM billing_master where sale_person='".$ei."' and challan_no='".$invoice_no."'");
 $row=mysqli_fetch_assoc($result);

 $a['from_location']=$row['from_location'];
 $a['from']=$this->getAnyDetails($row['from_location'],"locationname","location_code","location_master",$link1);
 $a['to_location']=$row['to_location'];
 $a["to"]=$this->getAnyDetails($row['to_location'],"locationname","location_code","location_master",$link1);
 $a['invoice_no']=$row['challan_no'];
 $a['invoice_date']=$row['sale_date'];
 
 $a['status']=$this->getdispatchstatus($row['status']);

 $result1=mysqli_query($link1,"SELECT * FROM billing_product_items where challan_no='".$invoice_no."'");
 while($row1=mysqli_fetch_assoc($result1)){
$partname=$this->getAnyDetails($row1['partcode'],"part_name","partcode","partcode_master",$link1);
$c['partcode']=$row1['partcode'];
$c['partname']=$partname;
$c['qty']=$row1['qty'];

array_push($b,$c);  
}
$a["part_detail"]=$b;
 return $a;	 
}

function validatecheque($chequeno){
    $link1=$this->link;
    $a=array();
    
    $sql="select cheque_no from collect_data where cheque_no='".$chequeno."' and cheque_no!=''";
    $rs=mysqli_query($link1,$sql) or die(mysqli_error($link1));
    if(mysqli_num_rows($rs)>0){
     $a["status"]="2";
     $a["error"]="duplicate";
        
    }else{
     $a["status"]="1";
     $a["error"]="successful";    
        
    }
  return $a;  
}

///////////////////////////////////////////////////////
}



