<?php session_start();
		if (isset($_SESSION['logged']) && $_SESSION['logged'] == 'yes') {$user=$_SESSION['user'];
			include ('../scripts/classes.php');
			if ($_SESSION['lang']=="EN"){include ('../lang/en.php');}elseif ($_SESSION['lang']=="GR"){include ('../lang/gr.php');}else {include ('../lang/en.php');}
			if ($_SERVER['REQUEST_METHOD'] != 'GET') exit;
			$array = getGlobalSettings(); foreach ($array as $settings){extract($settings);}
			$rpt = $_GET['rpt'];	
			$year = isset($_GET['year_to_update']) ? $_GET['year_to_update'] : @date("Y");
			$month = isset($_GET['month_to_update']) ? $_GET['month_to_update'] : @date("m");
		  //$barcode_id= $_POST['barcode_id'];
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title><?php echo $page_title ?></title>
<link rel="shortcut icon" href="../img/favicon.png" />
<link href="../cms1.css" rel="stylesheet" type="text/css" />
<style media="screen" type="text/css">
div#content {
    display: none;
    }
div#loading {             
    height:50px;
    background: url(../img/loading.gif) no-repeat;
    cursor: wait;                
    }
</style>
<script type="text/javascript">
        function preloader(){
            document.getElementById("loading").style.display = "none";
            document.getElementById("content").style.display = "block";
        }//preloader
        window.onload = preloader;
</script>
</head>
<body>
			<div class="align-center" style="margin-top:25%">
			<h2><?php echo $lbl_statistics_update;?></h2><br />
			<div class="align-center" id="loading" style="width:40px;margin-left:auto;margin-right:auto;"></div>
			<?php echo $lbl_thank_u_for_waiting ?>
			<div class="align-center" id="content">OK!</div>
			</div>
<?php	
	//Check if records already exists
if ($rpt=='annual' || $rpt=='monthly'){	
    set_time_limit(1000); //for long queries
    ##GET THE BRANCHES FIRST
    $branches_sql = "SELECT id FROM cls_branches";
    $result_branches = mysqli_query($mysqli, $branches_sql) or die("Failed to get branches: " . mysqli_error($mysqli));
    if (mysqli_num_rows($result_branches) == 0){ die('Branches Setup is missing');
    }else {
	   while ($each_branch = mysqli_fetch_array($result_branches)) {
        $msc = microtime(true);
		$this_branch = $each_branch['id'];
        	//start populating
            $exists = "SELECT * FROM bi_sales WHERE bi_year ='$year' AND bi_month ='$month' AND branch_id = '$this_branch'";
        	$result_exists = mysqli_query($mysqli, $exists) or die("Failed to select if bi_sales record exists: " . mysqli_error($mysqli));
            	if (mysqli_num_rows($result_exists) == 0){
            		mysqli_query ($mysqli, "INSERT into bi_sales values(
            					NULL,
                                '$this_branch',
            					'$year',
            					'$month',
            					(SELECT SUM(amount) FROM cls_transactions WHERE transaction_type='Αγορές' AND YEAR(created_on)='$year' AND MONTH(created_on)='$month' AND ISNULL(is_reversed) AND ISNULL(reversal) AND branch_id='$this_branch'),
            					(SELECT SUM(amount) FROM cls_transactions WHERE transaction_type='Εξαργύρωση' AND YEAR(created_on)='$year' AND MONTH(created_on)='$month' AND ISNULL(is_reversed) AND ISNULL(reversal) AND branch_id='$this_branch'),
            					(SELECT SUM(credits) FROM cls_transactions WHERE transaction_type='Αγορές' AND YEAR(created_on)='$year' AND MONTH(created_on)='$month' AND ISNULL(is_reversed) AND ISNULL(reversal) AND branch_id='$this_branch'),
            					ABS((SELECT SUM(credits) FROM cls_transactions WHERE transaction_type='Εξαργύρωση' AND YEAR(created_on)='$year' AND MONTH(created_on)='$month' AND ISNULL(is_reversed) AND ISNULL(reversal) AND branch_id='$this_branch')),
            					NOW(),
            					'$user',
            					'',
            					(SELECT COUNT(id) FROM cls_transactions WHERE YEAR(created_on)='$year' AND MONTH(created_on)='$month' AND ISNULL(is_reversed) AND ISNULL(reversal) AND branch_id='$this_branch'))
                                ") or die("fuck ".mysqli_error($mysqli));
            	}else {	
            		$rec = mysqli_fetch_array($result_exists, MYSQLI_ASSOC);
            		$rec_to_update = $rec['id'];
            		mysqli_query ($mysqli, "UPDATE bi_sales SET 
            					sales_amount =(SELECT SUM(amount) FROM cls_transactions WHERE transaction_type='Αγορές' AND YEAR(created_on)='$year' AND MONTH(created_on)='$month' AND ISNULL(is_reversed) AND ISNULL(reversal) AND branch_id='$this_branch'),
            					discount_amount =(SELECT SUM(amount) FROM cls_transactions WHERE transaction_type='Εξαργύρωση' AND YEAR(created_on)='$year' AND MONTH(created_on)='$month' AND ISNULL(is_reversed) AND ISNULL(reversal) AND branch_id='$this_branch'),
            					credits =(SELECT SUM(credits) FROM cls_transactions WHERE transaction_type='Αγορές' AND YEAR(created_on)='$year' AND MONTH(created_on)='$month' AND ISNULL(is_reversed) AND ISNULL(reversal) AND branch_id='$this_branch'),
            					credits_redeem = ABS((SELECT SUM(credits) FROM cls_transactions WHERE transaction_type='Εξαργύρωση' AND YEAR(created_on)='$year' AND MONTH(created_on)='$month' AND ISNULL(is_reversed) AND ISNULL(reversal) AND branch_id='$this_branch')),
            					updated_on = NOW(),
            					created_by='$user',
            					records=(SELECT COUNT(id) FROM cls_transactions WHERE YEAR(created_on)='$year' AND MONTH(created_on)='$month' AND ISNULL(is_reversed) AND ISNULL(reversal) AND branch_id='$this_branch')
            					WHERE id='$rec_to_update'
            					") or die("Failed to update existing record ". mysqli_error($mysqli));
            	}
        	## update execution time START
        	$exists = "SELECT id FROM bi_sales WHERE bi_year ='$year' AND bi_month ='$month' AND branch_id = '$this_branch'";
        	$result_exists = mysqli_query($mysqli, $exists) or die("Failed to select if bi_sales record exists for time: " . mysqli_error($mysqli));	
        	$rec = mysqli_fetch_array($result_exists, MYSQLI_ASSOC);
        	$rec_to_update = $rec['id'];
        	$msc = number_format(microtime(true)-$msc, 4);
        	mysqli_query ($mysqli, "UPDATE bi_sales SET exec_time='$msc' WHERE id='$rec_to_update' AND branch_id = '$this_branch'") or die("Failed to update record execution time ". mysqli_error($mysqli));
        	$msc_rollup = $msc_rollup + $msc;
        	## update execution time END
            } //end while branches
    }//end if branchs exist
	?>	    
            <div class="align-center"><?php echo _TOTAL.':<strong>'.$msc_rollup.'</strong> '.$lbl_seconds ?></div>
			<script type="text/javascript">
				<?php if ($rpt=='annual') { ?> window.location = "reports_annual.php" <?php } ?>
				<?php if ($rpt=='monthly') { ?> window.location = "reports_monthly.php" <?php } ?>
			</script>
<?php
###################################### SALES ITEMS PER MONTH & YEAR #######################################
}elseif ($rpt=='items_monthly' || $rpt=='items_annual'){	
	set_time_limit(1000); //for long queries
        ##GET THE BRANCHES FIRST
    $branches_sql = "SELECT id FROM cls_branches";
    $result_branches = mysqli_query($mysqli, $branches_sql) or die("Failed to get branches: " . mysqli_error($mysqli));
    if (mysqli_num_rows($result_branches) == 0){ die('Branches Setup is missing');
    }else {
	   while ($each_branch = mysqli_fetch_array($result_branches)) {
		$this_branch = $each_branch['id'];
            $exists_per_item = "SELECT id, description FROM cls_categories";
            $res_per_item  = mysqli_query($mysqli, $exists_per_item)or die("Failed to select if bi_sales_items record exists: " . mysqli_error($mysqli));
        		while ($row = mysqli_fetch_array($res_per_item)) { //per item
                $msc = microtime(true);
        		$item = $row['id'];
        		$item_desc = $row['description'];
        		$exists = "SELECT * FROM bi_sales_items WHERE bi_year ='$year' AND bi_month ='$month' AND bi_item='$item' AND branch_id = '$this_branch'";
        		$result_exists = mysqli_query($mysqli, $exists) or die("Failed to select if bi_sales record exists: " . mysqli_error($mysqli));
            		if (mysqli_num_rows($result_exists) == 0){
            			mysqli_query ($mysqli, "INSERT into bi_sales_items VALUES(
            			NULL,
                        '$this_branch',
            			'$year',
            			'$month',
            			'$item',
            			'$item_desc',
            			(SELECT SUM(amount) FROM cls_transactions WHERE transaction_type='Αγορές' AND YEAR(created_on)='$year' AND MONTH(created_on)='$month' AND category_id='$item' AND ISNULL(is_reversed) AND branch_id = '$this_branch'),
            			(SELECT SUM(credits) FROM cls_transactions WHERE transaction_type='Αγορές' AND YEAR(created_on)='$year' AND MONTH(created_on)='$month' AND category_id='$item' AND ISNULL(is_reversed) AND branch_id = '$this_branch'),
            			NOW(),
            			'$user',
            			'',
            			(SELECT COUNT(id) FROM cls_transactions WHERE YEAR(created_on)='$year' AND MONTH(created_on)='$month' AND category_id='$item' AND branch_id = '$this_branch'));") or die(mysqli_error($mysqli));
            			}else{
            		$rec = mysqli_fetch_array($result_exists, MYSQLI_ASSOC);
            		$rec_to_update = $rec['id'];
            		mysqli_query ($mysqli, "UPDATE bi_sales_items SET 
            			bi_item_desc='$item_desc',
            			sales_amount = (SELECT SUM(amount) FROM cls_transactions WHERE transaction_type='Αγορές' AND year(created_on)='$year' AND month(created_on)='$month' AND category_id='$item' AND ISNULL(is_reversed) AND branch_id = '$this_branch'),
            			credits = (SELECT SUM(credits) FROM cls_transactions WHERE transaction_type='Αγορές' AND year(created_on)='$year' AND month(created_on)='$month' AND  category_id='$item' AND ISNULL(is_reversed) AND branch_id = '$this_branch'),
            			updated_on = NOW(),
            			created_by = '$user',
            			records = (SELECT COUNT(id) FROM cls_transactions WHERE year(created_on)='$year' AND MONTH(created_on)='$month' AND category_id='$item' AND branch_id = '$this_branch')
            			WHERE id='$rec_to_update'") or die("Failed to update existing record ". mysqli_error($mysqli));
                    } //end if exists             
                	## update execution time START
                	$exists = "SELECT id FROM bi_sales_items WHERE bi_year ='$year' AND bi_month ='$month' AND branch_id = '$this_branch'";
                	$result_exists = mysqli_query($mysqli, $exists) or die("Failed to select if bi_sales record exists: " . mysqli_error($mysqli));	
                	$msc = number_format(microtime(true)-$msc, 4);
             	
            		while ($row = mysqli_fetch_array($result_exists, MYSQLI_ASSOC)) 
            		{
            		  $rec_to_update = $row['id'];
            		  mysqli_query ($mysqli, "UPDATE bi_sales_items SET exec_time='$msc' WHERE id='$rec_to_update '") or die("Failed to update record execution time ". mysqli_error($mysqli));
            		}
                    $msc_rollup = $msc_rollup + $msc;
                    ## update execution time END
                }//end while loop //per item   
            } //end while branches
    }//end if branchs exist     
?>
            <div class="align-center"><?php echo _TOTAL.':<strong>'.$msc_rollup.'</strong> '.$lbl_seconds ?></div>	
			<script><?php if ($rpt=='items_monthly') { ?> window.location = "reports_items_monthly.php" <?php } ?></script>
            <script><?php if ($rpt=='items_annual') { ?> window.location = "reports_items_annual.php" <?php } ?></script>
<?php
}
?>

</body>
</html>
<?php
} else {
    header('Location: login.php');
}
?>