<?php

define("servername" ,"localhost");
define("username" , "upstreamlogistic_pos_spotwash");
define("password" , ',y@9-d$twcIi');
define("dbname" , "upstreamlogistic_pos_spotwash");


// Create connection
$conn = new mysqli(servername, username, password, dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " );
} 

//include_once("../mySQL.php");

echo "working....<br />";


    $day = date('Y-m-d');
    
    
    //sales
    //     $sqlSales = "SELECT laundary_job.mart_code, SUM((`laundary_busket`.`laundary_price` * `laundary_busket`.`laundary_qty`)) AS cost FROM `laundary_busket`, laundary_job WHERE laundary_job.job_code = laundary_busket.job_code AND laundary_job.job_date = CURRENT_DATE  AND `laundary_busket`.`laundary_status` = 0 GROUP BY laundary_job.mart_code ORDER BY `busket_sn` DESC LIMIT 15 ";

     $sqlSales = "SELECT laundary_job.mart_code, SUM((`laundary_busket`.`laundary_price` * `laundary_busket`.`laundary_qty`)) AS cost FROM `laundary_busket`, laundary_job WHERE laundary_job.job_code = laundary_busket.job_code AND laundary_job.job_date = CURRENT_DATE  AND `laundary_busket`.`laundary_status` = 0 GROUP BY laundary_job.mart_code ORDER BY `busket_sn` DESC LIMIT 15 ";
	 
		$resultSales = $conn->query($sqlSales);
		$json = array();
		
		
		$sales = array();
		
        $reportSales = "";
        $totalSales = 0;
		if ($resultSales->num_rows > 0) {
			// output data of each row
			while($rowSales = $resultSales->fetch_assoc()) {
			    
			    $mart_code = $rowSales['mart_code'];
			    $mart_cost = $rowSales['cost'];
			   
			   $sales[$mart_code] = $mart_cost; 
			    
			    $totalSales += (int) $rowSales['cost'];
			    
			}
			
			
		}
    
    
    //income

	 $sqlData = "SELECT `laundary_mart`.mart_name, `laundary_mart`.mart_short_name, `laundary_job`.mart_code, SUM(payment.amount_paid)  AS receiptAmt, COUNT(`pay_number`) AS receiptCount FROM `laundary_mart`, laundary_job, payment WHERE `laundary_mart`.mart_code = laundary_job.mart_code AND payment.job_code = `laundary_job`.job_code AND payment.pay_date LIKE '$day%' AND laundary_mart.mart_code != 'M-5eb09925980e8' GROUP BY laundary_mart.mart_code";
	 
	 
		$resultData = $conn->query($sqlData);
	//	$json = array();
		
        $reportData = "";
		if ($resultData->num_rows > 0) {
			// output data of each row
			while($row = $resultData->fetch_assoc()) {
			
		         $mart_name = $row['mart_name'];
		         $receiptCount =  $row['receiptCount'];
		         $receiptAmt = number_format($row['receiptAmt'],0);
		         $receiptCost += $row['receiptAmt'];
		         
		         $mart_code = $row['mart_code'];
		         $mySales = $sales[$mart_code];
		    
		    $reportData .= $mart_name."(".$receiptCount.") SHS:".$receiptAmt." / ". number_format($mySales, 0) .", ";
		    
		//	sendNotification($conn, $id, $phone, $msg);
			}

            $totalAmt = number_format($receiptCost, 0);
            
           echo $reportData = $reportData." | Grand Total: Shs $totalAmt / ". number_format( $totalSales, 0);
            
            
		} else {
			echo "No Report Data";
		}
		
		$uniq = uniqid();
		$myCJR = date('ymdhis');
		
		//echo $reportData;
	  // $sqlSave = "INSERT INTO `sms_notification` (`sms_id`, `sms_type`, `sms_laundry_code`, `laundry_invoice_number`, `sms_client_code`, `sms_to`, `sms_job_price`, `sms_receipt_total`, `sms_other_msg`, `sms_basket_items`, `sms_status`, `ext_status`, `sms_mart_code`, `sms_created_by`, `sms_created_on`, `ext_ref`, `sms_date`, `sms_time`, `sms_timestamp`) VALUES
		//                (NULL, '2', 'CJR-$myCJR-A', 'N?A', 'C-26642446104396727', '256750846860', '0', '0', '(Income/Sales) $reportData', 'N/A', '0', 'pending', 'M-5eb09925980e8', 'Admin', CURRENT_TIMESTAMP, 'N/A', CURRENT_DATE,  CURRENT_TIME, CURRENT_TIMESTAMP); ";
	
	
	     $sqlSave = "INSERT INTO `sms_notification` (`sms_id`, `sms_type`, `sms_laundry_code`, `laundry_invoice_number`, `sms_client_code`, `sms_to`, `sms_job_price`, `sms_receipt_total`, `sms_other_msg`, `sms_basket_items`, `sms_status`, `ext_status`, `sms_mart_code`, `sms_created_by`, `sms_created_on`, `ext_ref`, `sms_date`, `sms_time`, `sms_timestamp`) VALUES
		               (NULL, '2', 'CJR-$myCJR-A', 'N?A', 'C-26642446104396727', '256701809030', '0', '0', '(Income/Sales) $reportData', 'N/A', '0', 'pending', 'M-5eb09925980e8', 'Admin', CURRENT_TIMESTAMP, 'N/A', CURRENT_DATE,  CURRENT_TIME, CURRENT_TIMESTAMP); ";
	
	
		$resultSave = $conn->query($sqlSave);

		/*
			$id = $row['sms_id'];
			$toPhone = "256788084769";
			echo $phone = preg_replace('/\D+/', '', $toPhone);
			$msg = "TEST MSG";
			//sendNotification($toPhone, $msg);
			*/
		echo "message saved....<br />";
		
		$conn->close();
		
?>