<?php
require("php_con.php");
$GLOBALS['connation']=$connation;

function active_log($txt){
$file=fopen("active-log.txt","a");
$write_time=date("Y-m-d h:i:s A");
fwrite($file,"[".$write_time."] ".$txt."\n");
fclose($file);
}

function total_product(){
	$total_product=mysqli_query($GLOBALS['connation'],"SELECT COUNT(*) AS qty FROM product_details");
	$total_product_resalt=mysqli_fetch_assoc($total_product);
	return($total_product_resalt['qty']);
}

function total_supplier(){
	$total_supplier=mysqli_query($GLOBALS['connation'],"SELECT COUNT(*) AS qty FROM supplier_details");
	$total_supplier_resalt=mysqli_fetch_assoc($total_supplier);
	return($total_supplier_resalt['qty']);
}

function last_bill(){
	$last_bill=mysqli_query($GLOBALS['connation'],"SELECT sale_details_billid FROM sale_details ORDER BY sale_details_id DESC LIMIT 1");
	$last_bill_resalt=mysqli_fetch_assoc($last_bill);
	if($last_bill_resalt){
	return($last_bill_resalt['sale_details_billid']);
	}
	else{
	return("N/A");
	}
}

function total_customer(){
	$total_customer=mysqli_query($GLOBALS['connation'],"SELECT COUNT(*) AS count FROM customer_details");
	$total_customer_resalt=mysqli_fetch_assoc($total_customer);
	return($total_customer_resalt['count']);
}

function today_sale(){
	$today=date("Y-m-d");
	$today_sale=mysqli_query($GLOBALS['connation'],"SELECT COUNT(*) AS qty FROM sale_details WHERE sale_details_date='$today'");
	$today_sale_resalt=mysqli_fetch_assoc($today_sale);
	return($today_sale_resalt['qty']);
}

function bill_id(){
	$bill_id=mysqli_query($GLOBALS['connation'],"SELECT setting_value FROM setting WHERE setting_text='bill_start_count'");
	$bill_id_resalt=mysqli_fetch_array($bill_id);
	
	$update=$bill_id_resalt['setting_value']+1;
	mysqli_query($GLOBALS['connation'],"UPDATE setting SET setting_value='$update' WHERE setting_text='bill_start_count'");
	
	return($bill_id_resalt['setting_value']."-".date("ymd"));
}

function customer_pin(){
	$query=mysqli_query($GLOBALS['connation'],"SELECT setting_value FROM setting WHERE setting_text='customer_pin'");
	$resalt=mysqli_fetch_array($query);
	
	return($resalt['setting_value']);
}

function setting_view($setting_text){
	$setting_view=mysqli_query($GLOBALS['connation'],"SELECT setting_value FROM setting WHERE setting_text='$setting_text'");
	$setting_view_resalt=mysqli_fetch_array($setting_view);

	if(isset($setting_view_resalt)){	
	return($setting_view_resalt['setting_value']);
	}
	else{
	mysqli_query($GLOBALS['connation'],"INSERT INTO setting (setting_id, setting_text, setting_value) VALUES (NULL, '$setting_text', '')");
	}
}

function setting_update($setting_text,$setting_value){
	$setting_view=mysqli_query($GLOBALS['connation'],"SELECT setting_value FROM setting WHERE setting_text='$setting_text'");
	if(!mysqli_num_rows($setting_view)>0){
		mysqli_query($GLOBALS['connation'],"INSERT INTO setting (setting_id, setting_text, setting_value) VALUES (NULL, '$setting_text', '')");
	}	
	mysqli_query($GLOBALS['connation'],"UPDATE setting SET setting_value='$setting_value' WHERE setting_text='$setting_text'");
}

function bill_amount($sale_billid){
$query=mysqli_query($GLOBALS['connation'],"SELECT SUM(sl.sale_saleprice*sl.sale_qty) AS amount
FROM sale_list sl
WHERE sl.sale_billid='$sale_billid'");
$resalt=mysqli_fetch_assoc($query);
	
$discount=mysqli_query($GLOBALS['connation'],"SELECT sale_details_discount FROM sale_details WHERE sale_details_billid='$sale_billid'");
$discount_resalt=mysqli_fetch_assoc($discount);
	
$b=(float)$resalt['amount']-$discount_resalt['sale_details_discount'];
	
return($b);
}

function qutotaion_bill_amount($sale_billid){
$query=mysqli_query($GLOBALS['connation'],"SELECT SUM(sl.sale_saleprice*sl.sale_qty) AS amount
FROM sale_list sl
WHERE sl.sale_billid='$sale_billid'");
$resalt=mysqli_fetch_assoc($query);
	
return($resalt['amount']);
}

function cus_bill($sale_details_customer){
$sale_details_customer=(int)$sale_details_customer;
$query=mysqli_query($GLOBALS['connation'],"SELECT COUNT(*) AS count_bill FROM sale_details WHERE sale_details_customer='$sale_details_customer'");
$resalt=mysqli_fetch_assoc($query);

return($resalt['count_bill']);
}

function cus_balance($sale_details_customer){
$sale_details_customer=(int)$sale_details_customer;
	
$payment=0;
$sale_amount=0;
$query=mysqli_query($GLOBALS['connation'],"SELECT sale_details_billid,SUM(sale_details_cash_payment+sale_details_card_payment) AS payment,sale_details_discount
FROM sale_details
WHERE sale_details_customer='$sale_details_customer'
GROUP BY sale_details_billid");
while($resalt=mysqli_fetch_assoc($query)){
$payment+=$resalt['payment'];
	
$sale_qury=mysqli_query($GLOBALS['connation'],"SELECT SUM(sale_saleprice*sale_qty) AS sale_amount FROM sale_list WHERE sale_billid='$resalt[sale_details_billid]'");
$sale_resalt=mysqli_fetch_assoc($sale_qury);
	
$sale_amount+=$sale_resalt['sale_amount']-$resalt['sale_details_discount'];
	
}
	
$balance=$payment-$sale_amount;

return($balance);
}

function price_buy($price_systemid){
$query=mysqli_query($GLOBALS['connation'],"SELECT * FROM price_details WHERE price_systemid='$price_systemid' ORDER BY price_id DESC LIMIT 1");
$resalt=mysqli_fetch_assoc($query);
return($resalt['price_buy']);
}

function price_sale($price_systemid){
$query=mysqli_query($GLOBALS['connation'],"SELECT * FROM price_details WHERE price_systemid='$price_systemid' ORDER BY price_id DESC LIMIT 1");
$resalt=mysqli_fetch_assoc($query);
return($resalt['price_sale']);
}

function availbel_qty($stock_systemid){
	$query=mysqli_query($GLOBALS['connation'],"SELECT SUM(if(sd.stock_type='1',sd.stock_qty,0)-if(sd.stock_type='0',sd.stock_qty,0)) AS avail_qty
	FROM stock_details sd
	WHERE sd.stock_systemid='$stock_systemid'");
	$resalt=mysqli_fetch_assoc($query);
	return($resalt['avail_qty']);
}

function total_in($stock_systemid){
	$query=mysqli_query($GLOBALS['connation'],"SELECT SUM(if(sd.stock_type='1',sd.stock_qty,0)) AS qty
	FROM stock_details sd
	WHERE sd.stock_systemid='$stock_systemid'");
	$resalt=mysqli_fetch_assoc($query);
	return($resalt['qty']);
}

function total_out($stock_systemid){
	$query=mysqli_query($GLOBALS['connation'],"SELECT SUM(if(sd.stock_type='0',sd.stock_qty,0)) AS qty
	FROM stock_details sd
	WHERE sd.stock_systemid='$stock_systemid'");
	$resalt=mysqli_fetch_assoc($query);
	return($resalt['qty']);
}

function availbel_qty_date_wise($stock_systemid,$st_time,$end_time){
	$query=mysqli_query($GLOBALS['connation'],"SELECT SUM(if(sd.stock_type='1',sd.stock_qty,0)-if(sd.stock_type='0',sd.stock_qty,0)) AS avail_qty
	FROM stock_details sd
	WHERE sd.stock_systemid='$stock_systemid' AND sd.stock_addtime BETWEEN '$st_time' AND '$end_time'");
	$resalt=mysqli_fetch_assoc($query);
	return($resalt['avail_qty']);
}

function location_name($ld_id){
$query=mysqli_query($GLOBALS['connation'],"SELECT ld_name FROM location_details WHERE ld_id='$ld_id'");
$resalt=mysqli_fetch_assoc($query);
return($resalt['ld_name']);
}

function start_amount($Start_balance_date,$Start_balance_start_user){
$query=mysqli_query($GLOBALS['connation'],"SELECT Start_balance_start_amount FROM start_balance_details WHERE Start_balance_date='$Start_balance_date' AND Start_balance_start_user='$Start_balance_start_user'");
$resalt=mysqli_fetch_assoc($query);
return(@$resalt['Start_balance_start_amount']);
}

function end_amount($Start_balance_date,$Start_balance_start_user){
$query=mysqli_query($GLOBALS['connation'],"SELECT Start_balance_end_amount FROM start_balance_details WHERE Start_balance_date='$Start_balance_date' AND Start_balance_start_user='$Start_balance_start_user'");
$resalt=mysqli_fetch_assoc($query);
return(@$resalt['Start_balance_end_amount']);
}

function user_cash_amount($Start_balance_date,$Start_balance_start_user){
$query=mysqli_query($GLOBALS['connation'],"SELECT SUM(sale_details_cash_payment) AS amount FROM sale_details WHERE sale_details_date='$Start_balance_date' AND sale_details_user='$Start_balance_start_user'");
$resalt=mysqli_fetch_assoc($query);
return($resalt['amount']);
}

function user_discount_amount($Start_balance_date,$Start_balance_start_user){
$query=mysqli_query($GLOBALS['connation'],"SELECT SUM(sale_details_discount) AS amount FROM sale_details WHERE sale_details_date='$Start_balance_date' AND sale_details_user='$Start_balance_start_user'");
$resalt=mysqli_fetch_assoc($query);	
return($resalt['amount']);
}

function user_discount_amount_sale_list($Start_balance_date,$Start_balance_start_user){
$query1=mysqli_query($GLOBALS['connation'],"SELECT SUM((sl.sale_real_sale_price-sl.sale_saleprice)*sl.sale_qty) AS dis_val
FROM sale_list sl INNER JOIN sale_details sd ON sl.sale_billid=sd.sale_details_billid
WHERE sd.sale_details_date='$Start_balance_date' AND sd.sale_details_user='$Start_balance_start_user'");
$resalt1=mysqli_fetch_assoc($query1);
return($resalt1['dis_val']);
}

function user_card_amount($Start_balance_date,$Start_balance_start_user){
$query=mysqli_query($GLOBALS['connation'],"SELECT SUM(sale_details_card_payment) AS amount FROM sale_details WHERE sale_details_date='$Start_balance_date' AND sale_details_user='$Start_balance_start_user'");
$resalt=mysqli_fetch_assoc($query);
return($resalt['amount']);
}

function user_expenses($Start_balance_date,$Start_balance_start_user){
$query=mysqli_query($GLOBALS['connation'],"SELECT SUM(expenses_details_amount) AS amount FROM expenses_details WHERE expenses_details_date='$Start_balance_date' AND expenses_details_user='$Start_balance_start_user'");
$resalt=mysqli_fetch_assoc($query);
return($resalt['amount']);
}

function discount_cal($sale_price,$display_price){
	$a=$sale_price-$display_price;
	$b=($a/$sale_price)*100;
	return($b);
}

function discount_price($pice,$discount){
	$a=$pice/100;
	$b=$a*$discount;
	$c=$pice-$b;
	return($c);
}

function supplier_total_invoice_amount($supplier_bill_details_supplier){
$qury=mysqli_query($GLOBALS['connation'],"SELECT SUM(sbl.supp_bill_buy_price*sbl.supp_bill_qty) AS amount
FROM supplier_bill_details sbd RIGHT JOIN supp_bill_list sbl ON sbd.supplier_bill_details_system_id=sbl.supp_bill_system_id
WHERE sbd.supplier_bill_details_supplier='$supplier_bill_details_supplier'");
$resalt=mysqli_fetch_assoc($qury);
return($resalt['amount']);
}

function supplier_total_payment($sp_supplier_id){
$qury=mysqli_query($GLOBALS['connation'],"SELECT SUM(sp.sp_amount) AS amount
FROM supplier_payment sp
WHERE sp.sp_supplier_id='$sp_supplier_id'");
$resalt=mysqli_fetch_assoc($qury);
return($resalt['amount']);
}

function month_profit($st_date,$en_date){
$qury=mysqli_query($GLOBALS['connation'],"SELECT SUM((sl.sale_saleprice-sl.sale_buy_price)*sl.sale_qty) AS amount
FROM sale_details sd INNER JOIN sale_list sl ON sd.sale_details_billid=sl.sale_billid
WHERE sd.sale_details_date BETWEEN '$st_date' AND '$en_date'");
$resalt=mysqli_fetch_assoc($qury);
return($resalt['amount']);
}
?>