<?php

/*define('DB_SERVER','localhost');
define('DB_USER','glserver_chand_gold_usr');
define('DB_PASS' ,'n92thMTPOR!B');
define('DB_NAME', 'glserver_chandrani_gold_db');*/
define('DB_SERVER','localhost');
define('DB_USER','chandranipearls_chandran_billing');
define('DB_PASS' ,'{Z7]HQ!e&[kk');
define('DB_NAME', 'chandranipearls_chandran_billing_soft');

date_default_timezone_set("Asia/Calcutta");

/*
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);*/

//error_reporting(0);

class DB_Controller
{
	function __construct()
	{
		$con = mysqli_connect(DB_SERVER,DB_USER,DB_PASS,DB_NAME);
		$this->dbh=$con;		
		if(mysqli_connect_errno())
		{
			echo "Failed to connect to MySQL: " . mysqli_connect_error();
		}
				
	}
	public function real_escapes($obj)
	{
		$ret=mysqli_real_escape_string($this->dbh,$obj);
		return $ret;
	}
	public function Insert_Data($table,$Fields,$Values)
	{
	    $SQL="insert into ".$table."(".$Fields.") values(".$Values.")";
		$QRY=mysqli_query($this->dbh,$SQL);
		return $QRY;
	}

		public function Insert_Data1($table,$Fields,$Values)
	{
	    echo $SQL="insert into ".$table."(".$Fields.") values(".$Values.")";
		//$QRY=mysqli_query($this->dbh,$SQL);
		//return $QRY;
	}


	public function Update_Data($table,$FieldValue,$Condition)
	{
	    $SQL="Update ".$table." set ".$FieldValue." where ".$Condition;
		$QRY=mysqli_query($this->dbh,$SQL);
		return $QRY;
	}


		public function Update_Data1($table,$FieldValue,$Condition)
	{
	   echo $SQL="Update ".$table." set ".$FieldValue." where ".$Condition;
		//$QRY=mysqli_query($this->dbh,$SQL);
		return $QRY;
	}


	public function num_check($table,$FieldValue,$Condition)
	{
	    $SQL="select ".$FieldValue." from ".$table." where ".$Condition;
		$QRY=mysqli_query($this->dbh,$SQL);
		$Num=mysqli_num_rows($QRY);
		return $Num;
	}
	public function Check_Login()
	{
		if($_COOKIE['User_Id']=='')
		{
		?>
        <script>window.location.href='logout.php';</script>
		 
         <?php
		}
	}
	public function Fetch_multiple_data($table,$Field_Needed,$condition)
	{
		 $SQL="select ".$Field_Needed." from ".$table." where ".$condition."";
		//echo $SQL;
		$QRY=mysqli_query($this->dbh,$SQL);
		while($Fetch=mysqli_fetch_assoc($QRY))
		{
		$rows[]=$Fetch;
		}
		return $rows;
	}
	public function cookies($variable,$value)
	{
		setcookie($variable,$value, time() + (3600 * 10), "/");//====set cookie for 10 hrs====//
	}
	public function Fetch_individual_data($table,$needed_values,$condition)
	{
		$SQL="select ".$needed_values." from ".$table." where ".$condition."";
		
	   //echo $SQL;
		$QRY=mysqli_query($this->dbh,$SQL);
		$Fetch=mysqli_fetch_assoc($QRY);
		return $Fetch;
	}

		public function Fetch_individual_data1($table,$needed_values,$condition)
	{
		$SQL="select ".$needed_values." from ".$table." where ".$condition."";
		
	   echo $SQL;
		$QRY=mysqli_query($this->dbh,$SQL);
		$Fetch=mysqli_fetch_assoc($QRY);
		return $Fetch;
	}
	public function get_sum_field($table,$field,$condition)
	{
	   $SQL_SUM="select sum(".$field.") as total from ".$table." where ".$condition;	
		//echo $SQL_SUM;
		$QRY_SUM=mysqli_query($this->dbh,$SQL_SUM);
		$Ft_SUM=mysqli_fetch_array($QRY_SUM);
		$Ft_Val=$Ft_SUM['total'];		
		return $Ft_Val;
	}	
	


		public function create_design_code($metal_id,$metal_type,$cat)
	{
	    $SQL_SUM="select Code,Last_P_Code,Category from gold_product_category_master where SLN='".$cat."'";	
		//echo $SQL_SUM;
		$QRY_SUM=mysqli_query($this->dbh,$SQL_SUM);
		$Ft_SUM=mysqli_fetch_array($QRY_SUM);
		$Code=$Ft_SUM['Code'];
			
		if($Ft_SUM['Last_P_Code']==""){
			
		$Last_P_Code="10001";
			
		}
			
		else{
			
		$Last_P_Code=$Ft_SUM['Last_P_Code']+1;	
			
		}	
			
		$design_code = 	$metal_id."".$Code."".$Last_P_Code;

		//$Product_Name = $metal_type." ".strtoupper($Ft_SUM['Category'])."-".$design_code;
		
		$Product_Name = $metal_type." ".strtoupper($Ft_SUM['Category']);	
		
		return $Last_P_Code.",".$design_code.",".$Product_Name;
	}
	
	public function Generate_Gold_Id()
	{
		$Get_Sql=mysqli_query($this->dbh,"select max(Order_No) as maxid from gold_billing_order_master");
		$num=mysqli_num_rows($Get_Sql);
		$ft=mysqli_fetch_array($Get_Sql);
		if($num>0)
		{
			$Maxid=explode("CPG",$ft['maxid']);
			$New_Id=$Maxid[1]+1;
		}
		else
		{
			$New_Id=1;
		}
		$Order_Id="CPG".str_pad($New_Id, 5, '0', STR_PAD_LEFT);
		
		return $Order_Id;
	}
	
	public function today_gold_rate($Gold_Purity,$Dealer_Code)
	{
		$Current_Date=date('Y-m-d');
		$Get_Dealer_Zone=$this->Fetch_individual_data('dealer_master',"Dealer_Zone","Order_Code='".$Dealer_Code."'");
		
		$Get_Price=$this->Fetch_individual_data("gold_rate_master","Buying_Rate,Selling_Rate",$condition);
		//return $Get_Price['Buying_Rate'];
		return $Get_Price['Selling_Rate'];
	}

		public function today_gold_customer_rate($Gold_Purity)
	{
		$Current_Date=date('Y-m-d');
		$condition="Karat='".$Gold_Purity."' and DATE(Entry_Date)='".$Current_Date."' order by SLN desc limit 1";
		$Get_Price=$this->Fetch_individual_data("gold_rate_master","Selling_Rate",$condition);
		return $Get_Price['Selling_Rate'];
	}
	
	public function today_gold_dealer_rate($Gold_Purity)
	{
		$Current_Date=date('Y-m-d');
		$condition="Karat='".$Gold_Purity."' and DATE(Entry_Date)='".$Current_Date."' order by SLN desc limit 1";
		$Get_Price=$this->Fetch_individual_data("gold_rate_master","Dealer_Rate",$condition);
		return $Get_Price['Dealer_Rate'];
	}

	public function product_check($table,$Product_Id)
	{
		 $SQL_Pro="select SLN from ".$table." where Productid='".$Product_Id."'";
		$QRY_Pro=mysqli_query($this->dbh,$SQL_Pro);
		$Num_Pro=mysqli_num_rows($QRY_Pro);		
		return $Num_Pro;
	}
	public function barcode_check($table,$Barcode)
	{
		 $SQL_Pro="select SLN from ".$table." where Barcode='".$Barcode."'";
		$QRY_Pro=mysqli_query($this->dbh,$SQL_Pro);
		$Num_Pro=mysqli_num_rows($QRY_Pro);		
		return $Num_Pro;
	}
	public function product_det($table,$Product_Id)
	{
		$SQL_Det="select * from ".$table." where Productid='".$Product_Id."'";
		$QRY_Det=mysqli_query($this->dbh,$SQL_Det);
		$Ft_Det=mysqli_fetch_array($QRY_Det);		
		return $Ft_Det;
	}
	public function product_barcode_det($table,$Barcode)
	{
		$SQL_Det="select * from ".$table." where Barcode='".$Barcode."'";
		$QRY_Det=mysqli_query($this->dbh,$SQL_Det);
		$Ft_Det=mysqli_fetch_array($QRY_Det);		
		return $Ft_Det;
	}
	public function GetStnDis($Product_Code)
	{
	
		$SQL_DM="select Discount from stn_discount_master where Product_Code='".$Product_Code."'";
		$QRY_DM=mysqli_query($this->dbh,$SQL_DM);
		$Num_DM=mysqli_num_rows($QRY_DM);		
		if($Num_DM>0)
		{
			   $Ft_DM=mysqli_fetch_assoc($QRY_DM);	
			   $FDM=$Ft_DM['Discount'];
		}
		
		return 	$FDM;
		
		
	}
	/*public function GetDealerMarginAmt($Category_Code,$Dealer_Code)
	{
		
		$SQL_DM="select Margin from dealer_margin_master where Category_Code='".$Category_Code."' and Dealer_Code='".$Dealer_Code."'";
		$QRY_DM=mysqli_query($this->dbh,$SQL_DM);
		$Num_DM=mysqli_num_rows($QRY_DM);		
		if($Num_DM>0)
		{
			   $Ft_DM=mysqli_fetch_assoc($QRY_DM);	
			   $FDM=$Ft_DM['Margin'];
		}
		else{
		     $SQL_DM="select Margin from dealer_margin_master where Category_Code='0' and Dealer_Code='".$Dealer_Code."'";
		     $QRY_DM=mysqli_query($this->dbh,$SQL_DM);
			 $Ft_DM=mysqli_fetch_assoc($QRY_DM);	
		     $FDM=$Ft_DM['Margin'];
		
		}
		return 	$FDM;
		
		
	}*/
	public function GetDealerMarginAmt($Component,$Dealer_Code)
	{
		
		 //$SQL_DM="select Margin from gold_dealer_margin_master where `Component`='".$Component."' and Dealer_Code='".$Dealer_Code."'";
		$SQL_DM="select Margin from gold_dealer_margin_master where `Component`='".$Component."'";
		$QRY_DM=mysqli_query($this->dbh,$SQL_DM);
		$Num_DM=mysqli_num_rows($QRY_DM);		
		if($Num_DM>0)
		{
			   $Ft_DM=mysqli_fetch_assoc($QRY_DM);	
			   $FDM=$Ft_DM['Margin'];
		}
		/*else{
		     $SQL_DM="select Margin from dealer_margin_master where Category_Code='0' and Dealer_Code='".$Dealer_Code."'";
		     $QRY_DM=mysqli_query($this->dbh,$SQL_DM);
			 $Ft_DM=mysqli_fetch_assoc($QRY_DM);	
		     $FDM=$Ft_DM['Margin'];
		
		}*/
		return 	$FDM;
		
		
	}
	public function Get_GST_Product($table,$hsn)
	{
		$SQL_f2="select GST_Rate from ".$table." where HSN_Code='".$hsn."'";
		$QRY_f2=mysqli_query($this->dbh,$SQL_f2);
		$Ft_f2=mysqli_fetch_assoc($QRY_f2);
		if(mysqli_num_rows($QRY_f2)>0)
		{
			$GST=$Ft_f2['GST_Rate'];
		}
		else
		{
			$GST=3;
		}
		return $GST;
	}
	public function Fetch_distinct_datas($table,$val,$condition)
	{
		$SQL="select distinct(".$val.") from ".$table." where ".$condition."";
		//echo $SQL;
		$QRY=mysqli_query($this->dbh,$SQL);
		while($Fetch=mysqli_fetch_assoc($QRY))
		{
		$rows[]=$Fetch;
		}
		return $rows;
	}
	
	public function Generate_Gold_Invoice($Shop_ID)
	{
		$currentYr=date("y");
	    $currentMonth=date("m");
		if($currentMonth<04)
		{
			$currentYrFinacila=($currentYr - 1);
		}
		else
		{
			$currentYrFinacila=$currentYr;
		}
		
		$FinalcialRange=$currentYrFinacila."-".($currentYrFinacila+1);
		$Get="select Invoice_No from gold_invoice_master where Dealer_Code='".$Shop_ID."' and Financial_Year='".$FinalcialRange."' and Invoice_No!='' order by SLN desc limit 1";
		$Qry_No=mysqli_query($this->dbh,$Get);
		$Fetch_No=mysqli_fetch_array($Qry_No);
		$Explode=explode('/',$Fetch_No['Invoice_No']);
		$Invoice_Count= $Explode[2]+ 1;
		$Invoice = str_pad($Invoice_Count, 5, '0', STR_PAD_LEFT);
		
		
		
		$Invoice_Number=$Shop_ID."/G/".$Invoice."/".$FinalcialRange;
		return 	$Invoice_Number;
	}


		public function member_check($table,$memid)
	{
		$SQL_Mem="select SLN from ".$table." where Member_Id='".$memid."' or Phone='".$memid."'";
		$QRY_Mem=mysqli_query($this->dbh,$SQL_Mem);
		$Num_Mem=mysqli_num_rows($QRY_Mem);		
		return $Num_Mem;
	}



		public function UpdatePaymentMode($orderid)
	{

		$sql=mysqli_query($this->dbh,"UPDATE gold_billing_order_master bm
JOIN gold_payment_master pm ON bm.`Order_No` = pm.`Order_No`
JOIN gold_billing_order_details bod ON bm.`Order_No` = bod.`Order_No` 
SET bm.Mode =
    CASE
        WHEN pm.Cash_Amount > 0 THEN 'Cash'
        WHEN pm.Cash_Amount = 0 AND pm.Bank_Amount = 0 AND pm.Other_Payment_Amount = 0 AND pm.Other_Payment_Amount2 = 0 THEN 'Cash'  -- Handle all zeros as Cash
        WHEN pm.Bank_Amount > 0 THEN 'Card'
        WHEN pm.Other_Payment_Amount > 0 OR pm.Other_Payment_Amount2 > 0 THEN 'UPI'
        ELSE 'Unknown'
    END,
    bod.Mode =  
    CASE
        WHEN pm.Cash_Amount > 0 THEN 'Cash'
        WHEN pm.Cash_Amount = 0 AND pm.Bank_Amount = 0 AND pm.Other_Payment_Amount = 0 AND pm.Other_Payment_Amount2 = 0 THEN 'Cash'  -- Handle all zeros as Cash
        WHEN pm.Bank_Amount > 0 THEN 'Card'
        WHEN pm.Other_Payment_Amount > 0 OR pm.Other_Payment_Amount2 > 0 THEN 'UPI'
        ELSE 'Unknown'
    END
WHERE pm.`Order_No` = '".$orderid."'") ;
	}
	public function GetProductFromBarcode($Barcode)
	{
		$Product=substr($Barcode,0,-4);
		return $Product;
	}
	public function Challan_Details($table,$Condition)
	{
		$Cha_Det="select * from ".$table." where ".$Condition."";
		$QRY_Det=mysqli_query($this->dbh,$Cha_Det);
		$Ft_Cha_Det=mysqli_fetch_assoc($QRY_Det);
		return $Ft_Cha_Det;
	}
	public function Challan_det_return($table,$condition)
	{
		$SQL="select * from ".$table." where SLN>0 and ".$condition."";
		$QRY=mysqli_query($this->dbh,$SQL);
		while($Fetch=mysqli_fetch_assoc($QRY))
		{
		$rows[]=$Fetch;
		}
		return $rows;
	}
	
	public function Generate_GRN($Shop_ID,$FinalcialRange)
	{
		$Get="select GRN_NO from gold_grn_master where Dealer_Code='".$Shop_ID."' and Financial_Year='".$FinalcialRange."' order by SLN desc limit 1";
		$Qry_No=mysqli_query($this->dbh,$Get);
		$Fetch_No=mysqli_fetch_array($Qry_No);
		$Explode=explode('/',$Fetch_No['GRN_NO']);
		$Invoice_Count= $Explode[2]+ 1;
		$Invoice = str_pad($Invoice_Count, 5, '0', STR_PAD_LEFT);
		
		
		
		$GRN_Number=$Shop_ID."/GRN/G/".$Invoice."/".$FinalcialRange;
		return 	$GRN_Number;	
	}
	
	public function UpdateProductStock($Order_Id,$Dealer_Code,$Bill_Date)
	{
	   $Condition="Order_No='".$Order_Id."'";
	   $Get_Order_Details=$this->Challan_det_return('gold_billing_order_details',$Condition);
	   
	  
	
	   foreach($Get_Order_Details as $Details)
		{
			
			
			$Condition1=" Barcode='".$Details['Barcode']."' and Dealer_Code='".$Dealer_Code."'";

			$Get_Quantity=$this->Challan_Details('dealer_gold_stock_master',$Condition1);
			
			$Qty=$Get_Quantity['Quantity'] - $Details['Quantity'];
			
			
			$FieldValue="Quantity='".$Qty."'";

			$Condition2=" Barcode='".$Details['Barcode']."' and Dealer_Code='".$Dealer_Code."'";

			$Update_Stock=$this->Update_Data('dealer_gold_stock_master',$FieldValue,$Condition2);
			
			$Get_Product=$this->Fetch_individual_data('gold_products_master',"GoldPurity,GoldWeight,Diamond_Carat,sollitaire_Carat","Barcode='".$Details['Barcode']."'");
			
			$Total_Gold_wt=$Get_Product['GoldWeight']-$Details['gold_wt'];
			$Total_Diamond_wt=$Get_Product['Diamond_Carat']-$Details['Diamond_Carat'];
			$Total_Solitair_wt=$Get_Product['sollitaire_Carat']-$Details['sollitaire_Carat'];

				 $Fields='Challan_No,Date,Dealer_Code,Product_Code,Category_Code,Stock_Out,Balance,Booked_Balance,gold_purity,gold_wt,diamond_ct,solitaire_ct,Movement_Type,DRP_Balance';

				$Values="'".$Order_Id."','".$Bill_Date."','".$Dealer_Code."','".$Details['Product_Code']."','".$Get_Quantity['Category_Code']."','".$Details['Quantity']."','".$Qty."','".$Get_Quantity['Booked']."','".$Get_Product['GoldPurity']."','".$Total_Gold_wt."','".$Total_Diamond_wt."','".$Total_Solitair_wt."','Order Placed','".$Get_Quantity['DRP']."'";

				$Insert_Transaction=$this->Insert_Data('gold_stock_transaction_master',$Fields,$Values);
			
		
			
		}

	}
	
	public function Get_Cash_Movement_Balance_Pearl($Dealer_Code)
	{
		$SQL_2="select Balance from cash_movement_master where Dealer_Code='".$Dealer_Code."' order by SLN desc limit 1";
		$QRY_2=mysqli_query($this->dbh,$SQL_2);
		$Ft_Det=mysqli_fetch_assoc($QRY_2);		
		return $Ft_Det['Balance'];
	}
	
	public function Get_Cash_Movement_Balance_Gold($Dealer_Code)
	{
		$SQL_2="select Balance from gold_cash_movement_master where Dealer_Code='".$Dealer_Code."' order by SLN desc limit 1";
		$QRY_2=mysqli_query($this->dbh,$SQL_2);
		$Ft_Det=mysqli_fetch_assoc($QRY_2);		
		return $Ft_Det['Balance'];
	}
	
}
?>