<?php 
include('includes/config/config.php');
include('log_entry.php');


$sql_menu= "select ohc_type_id from ohc_type ";
if($result_menu = mysqli_query($conn,$sql_menu)){
while($row_menu = mysqli_fetch_array($result_menu)){
	error_log("Batch Run### **Start******* Running Monthly Stock Update Dispensary for OHC:".$row_menu['ohc_type_id']);
	updateItemStockDispensaryMonthlyBalance($row_menu['ohc_type_id']);
	error_log("Batch Run### **End******* Running Monthly Stock Update Dispensary for OHC:".$row_menu['ohc_type_id']);
	
	
}
}else{
	error_log("Failed to get OHCs for Batch Run Running Monthly Stock Update Dispensary for OHC");
	
}
error_log("Batch Run### **Start******* Running Monthly Stock Update Store");
updateItemStockStoreMonthlyBalance();
error_log("Batch Run### **End******* Running Monthly Stock Update Store");
take_Monthly_db_backup();

function updateItemStockDispensaryMonthlyBalance($ohc_id){
mysqli_query($conn,"BEGIN" );
error_log("param ohc_location:".$ohc_id);
$sql_existing_today_balance = "select * from item_stock_dispensary_monthly_balance where record_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) and ohc_location_id = '$ohc_id'  ";
	$num_rows_existing = mysqli_num_rows ( mysqli_query ($sql_existing_today_balance) );
	if ($num_rows_existing != 0) {
		$delete_existing_today_balance = "delete from item_stock_dispensary_monthly_balance where record_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) and ohc_location_id = '$ohc_id'  ";
		if (! mysqli_query($conn,$delete_existing_today_balance )) {
			error_log("Failed to clear existing:".mysqli_error($conn)." failing query:".$delete_existing_today_balance);
			mysqli_query($conn,"ROLLBACK" );
			exit ( mysqli_error($conn) );
		}
	}

	$sql_bulk_insert="";
	$sql_existing_today_balance = "select * from item_stock_dispensary where ohc_location_id = '$ohc_id'  ";
	error_log("$sql_existing_today_balance");
	if(!$result_today_bal= mysqli_query ($sql_existing_today_balance)){
		error_log("sql_existing_today_balance:".$sql_existing_today_balance);
	}
	while ($row_bal = @mysqli_fetch_array ( $result_today_bal )) {
		@extract ( $row_bal );
		$sql_bulk_insert.="insert into item_stock_dispensary_monthly_balance(item_id,item_batch_no,ohc_location_id,expiry_date,stock_qty,record_date) values(";
		$sql_bulk_insert.="'".$row_bal['item_id']."','".$row_bal['item_batch_no']."','".$row_bal['ohc_location_id']."','".$row_bal['expiry_date']."','".$row_bal['stock_qty']."',DATE_SUB(CURDATE(), INTERVAL 1 DAY));";
	}
	error_log("bulk Insert Query:".$sql_bulk_insert);

	
	if (!$mysqli->multi_query($sql_bulk_insert)) {
		echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
	}
	$mysqli -> commit();
	mysqli_query($conn,"COMMIT" );
	

}

function updateItemStockStoreMonthlyBalance(){
	mysqli_query($conn,"BEGIN" );
	error_log("Store Stock Monthly Balance Record");
	$sql_existing_today_balance = "select * from item_stock_monthly_balance where record_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY) ";
	$num_rows_existing = mysqli_num_rows ( mysqli_query ($sql_existing_today_balance) );
	if ($num_rows_existing != 0) {
		$delete_existing_today_balance = "delete from item_stock_monthly_balance where record_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY)  ";
		if (! mysqli_query($conn,$delete_existing_today_balance )) {
			error_log("Failed to clear existing:".mysqli_error($conn)." failing query:".$delete_existing_today_balance);
			mysqli_query($conn,"ROLLBACK" );
			exit ( mysqli_error($conn) );
		}
	}

	$sql_bulk_insert="";
	$sql_existing_today_balance = "select * from item_stock ";
	error_log("$sql_existing_today_balance");
	if(!$result_today_bal= mysqli_query ($sql_existing_today_balance)){
		error_log("sql_existing_today_balance:".$sql_existing_today_balance);
	}
	while ($row_bal = @mysqli_fetch_array ( $result_today_bal )) {
		@extract ( $row_bal );
		$sql_bulk_insert.="insert into item_stock_Monthly_balance(item_id,item_batch_no,expiry_date,stock_qty,record_date) values(";
		$sql_bulk_insert.="'".$row_bal['item_id']."','".$row_bal['item_batch_no']."','".$row_bal['expiry_date']."','".$row_bal['stock_qty']."',DATE_SUB(CURDATE(), INTERVAL 30 DAYS));";
	}
	error_log("bulk Insert Query Store:".$sql_bulk_insert);


	if (!$mysqli->multi_query($sql_bulk_insert)) {
		echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
	}
	$mysqli -> commit();
	mysqli_query($conn,"COMMIT" );


}

function take_Monthly_db_backup(){
	 
$backup_file = "G:/mpbirla_backups/mpbirla_live". date("Y-m-d-H-i-s") . '.sql';
$command = "C:/xampp/mysqli/bin/mysqlidump -u ".$database_user. "-p ".$database_password. "mpbirla_live > ". $backup_file;

system($command);

	
}


?>