<?php 
/*This is a data import tool that I built that imports a csv file using 'LOAD DATA INFILE' into mysql using a temporary database in order to do an 'UPDATE' to the data each time it's run. It was set to run on a cron job nightly */
<?php 
//This will hold all of the messages for better for for worse...
$results = array();
// Set the database access information as constants:
DEFINE ('DB_USER', '******');
DEFINE ('DB_PASSWORD', '******');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'Reports'); 
// Make the connection:
$dbc = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die ('Could not connect to MySQL: ' . mysqli_connect_error( ) );
mysqli_set_charset($dbc, 'utf8'); 
//Store connection in results
$results[] = "Connected to: " . DB_NAME . " DataBase";


/******BEGIN IMPORTING FULFILLMENT DATA******/
//empty the table first
$q1_empty_table = "
TRUNCATE TABLE amz_fulfillment_inv
";
//Import data from amz_fulfillment_inv file into the temp table  
$q1_load_data = "
LOAD DATA INFILE '/websites/www/customer-site/mws/src/MarketplaceWebService/Samples/amz_fulfillment_inv.txt' 
INTO TABLE amz_fulfillment_inv
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
IGNORE 1 LINES;
";

// Run the queries.
$r1_empty_table = @mysqli_query ($dbc, $q1_empty_table); 
$r1_load_data = @mysqli_query ($dbc, $q1_load_data); 


//Collect the results of all of the queries.
if ($r1_empty_table) { 
	$results[] = "****amz_fulfillment_inv table was emptied****"; 
} else { 
	$results[] = "*******ERROR********COULD NOT EMPTY AMZ_FULFILLMENT_INV" . mysqli_error($dbc) . ' ' . $q1_empty_table; // Debugging message:
} 
if ($r1_load_data) { 
	$results[] = "****amz_fulfillment_inv was filled with most recent data****"; 
} else { 
	$results[] = "*******ERROR********COULD NOT IMPORT DATA INTO AMZ_FULFILLMENT_INV" . mysqli_error($dbc) . ' ' . $q1_load_data; // Debugging message:
} 

/******BEGIN IMPORTING ORDERS DATA******/

//empty table first 
$q2_empty_table = "
TRUNCATE TABLE amz_orders_4weeks
";
//Import data from amz_fulfillment_inv file into the temp table  
$q2_load_data = "
LOAD DATA INFILE '/websites/www/customer-site/mws/src/MarketplaceWebService/Samples/amz_orders_4weeks.txt' 
INTO TABLE amz_orders_4weeks
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
IGNORE 1 LINES;
";

$q2_fix_dates = "
UPDATE amz_orders_4weeks
SET `date-formatted` = DATE(`purchase-date`);
";

// Run the queries.
$r2_empty_table = @mysqli_query ($dbc, $q2_empty_table); 
$r2_load_data = @mysqli_query ($dbc, $q2_load_data); 
$r2_fix_dates = @mysqli_query ($dbc, $q2_fix_dates); 


//Collect the results of all of the queries.
if ($r2_empty_table) { 
	$results[] = "****amz_orders_4weeks table was emptied****"; 
} else { 
	$results[] = "*******ERROR********COULD NOT EMPTY AMZ_ORDERS_4WEEKS TABLE" . mysqli_error($dbc) . ' ' . $q2_empty_table; // Debugging message:
} 
if ($r2_load_data) { 
	$results[] = "****amz_orders_4weeks was filled with most recent data****"; 
} else { 
	$results[] = "*******ERROR********COULD NOT IMPORT DATA INTO AMZ_ORDERS_4WEEKS" . mysqli_error($dbc) . ' ' . $q2_load_data; // Debugging message:
} 
 if ($r2_fix_dates) { 
	$results[] = "****amz_orders_4weeks was date fixed****"; 
} else { 
	$results[] = "*******ERROR********COULD NOT FIX DATES IN AMZ_ORDERS_4WEEKS" . mysqli_error($dbc) . ' ' . $q2_fix_dates; // Debugging message:
} 

/******BEGIN FILLING REPORTS TABLE******/ //week filters was select count(sku)

$q_empty_Daily_Report = "TRUNCATE TABLE Daily_Report";
$q_calc = "
	select `sku`, `afn-fulfillable-quantity`, `afn-reserved-quantity`, `afn-inbound-working-quantity`, `afn-inbound-shipped-quantity`, `afn-inbound-receiving-quantity` from amz_fulfillment_inv; ";
$r_empty_Daily_Report = @mysqli_query ($dbc, $q_empty_Daily_Report); // Run the query.	
$r_calc = @mysqli_query ($dbc, $q_calc); // Run the query.	
	while ($row = mysqli_fetch_array ($r_calc, MYSQLI_ASSOC)) {
			$SKU = $row['sku'];
			$q1week = "select SUM(quantity) from amz_orders_4weeks where `sku` = '$SKU' and `order-status`='Shipped' and `item-status` = 'Shipped' and `sales-channel`='Amazon.com' and `fulfillment-channel`='Amazon' and `date-formatted` BETWEEN DATE_SUB(NOW(), INTERVAL 8 DAY) AND NOW() group by sku";
			$q2week = "select SUM(quantity) from amz_orders_4weeks where `sku` = '$SKU' and `order-status`='Shipped' and `item-status` = 'Shipped' and `sales-channel`='Amazon.com' and `fulfillment-channel`='Amazon'and `date-formatted` BETWEEN DATE_SUB(NOW(), INTERVAL 15 DAY) AND NOW() group by sku";
			$q4week = "select SUM(quantity) from amz_orders_4weeks where `sku` = '$SKU' and `order-status`='Shipped' and `item-status` = 'Shipped' and `sales-channel`='Amazon.com' and `fulfillment-channel`='Amazon' group by sku";
			
			$r1week = @mysqli_query ($dbc, $q1week); 
			$r2week = @mysqli_query ($dbc, $q2week); 
			$r4week = @mysqli_query ($dbc, $q4week); 
			$row1week = mysqli_fetch_array ($r1week, MYSQLI_ASSOC);
			$row2week = mysqli_fetch_array ($r2week, MYSQLI_ASSOC);
			$row4week = mysqli_fetch_array ($r4week, MYSQLI_ASSOC);
			$highest =  max(($row4week['SUM(quantity)'] / 4),  ($row2week['SUM(quantity)'] / 2),  $row1week['SUM(quantity)']);
			$outbound = round(($highest * 4) - ($row['afn-fulfillable-quantity'] + $row['afn-reserved-quantity'] + $row['afn-inbound-working-quantity'] + $row['afn-inbound-shipped-quantity'] + $row['afn-inbound-receiving-quantity'] ));
			
			if ($outbound > 0){ 
			
				$q_insert = "INSERT INTO Daily_Report (sku, last4weeks, last2weeks, lastweek, highest, `afn-fulfillable-quantity`, `afn-reserved-quantity`, `afn-inbound-working-quantity`, `afn-inbound-shipped-quantity`, `afn-inbound-receiving-quantity`, `outbound` ) 
				VALUES ('{$row['sku']}', '{$row4week['SUM(quantity)']}', '{$row2week['SUM(quantity)']}', '{$row1week['SUM(quantity)']}', '$highest', '{$row['afn-fulfillable-quantity']}', '{$row['afn-reserved-quantity']}', '{$row['afn-inbound-working-quantity']}', '{$row['afn-inbound-shipped-quantity']}', '{$row['afn-inbound-receiving-quantity']}', '$outbound' )";
				
				$r_insert = @mysqli_query ($dbc, $q_insert); // Run the query.
					
				if (!$r_insert) { 
				$results[] = "*******ERROR********COULD NOT INSERT INTO DAILY REPORTS" . mysqli_error($dbc) . ' ' . $q2_fix_dates; // Debugging message:
				}
			}		
	}
//Close the database connection.
mysqli_close($dbc); 
//Outputs messages to a browser if run manually
echo "<pre>"; print_r($results); echo "<pre>";
//Send an email confirmation with all results each time it runs:
$to = "grace@gmrbowden.com";
$subject = "Nightly Amazon Import";
$headers = "From: imports@customer-site";
$msg = "Results: " . print_r($results, true); 
mail($to,$subject,$msg,$headers);
?>