";
$resultBack = mysqli_query($db,$queryBack) or die("error in DB call $queryBack");
//$backorderids_count = mysql_num_rows($resultBack);
while($myrow = mysqli_fetch_array($resultBack,MYSQLI_ASSOC))
{
$partno = $myrow["itemCode"];
if(substr($partno,-1) == 'P')
$partno = substr($partno,0,-1);
if(!in_array($partno,$product_array))
{
$product_array[] = $partno;
}
else continue;
//echo $partno . "
";
mysqli_select_db($db,$dbname);
$query1 = "SELECT p.partid, p.partno, p.unit_sold, p.sales, p.ARS FROM parts p WHERE p.partno = '$partno' LIMIT 1"; // $inactive_filter_cnd LIMIT 0 , 100
//echo $query1 . "
";
$result = mysqli_query($db,$query1) or die("error in DB call $query1");
while($row = mysqli_fetch_array($result,MYSQLI_ASSOC))
{
$partid = $row["partid"];
$unit_sold = $row["unit_sold"];
$sales = $row["sales"];
//$ARS = $row["ARS"];
//******************* INVENTORY DAYS ********************************
$priorDay = "";
$noCount = 0;
$markFirst = "";
$timeNow = time();
//$timeSixMonths = time() - (6 * 4.35 * 7 * 24 * 60 * 60); //6 months back
$timeYear = time() - (52 * 7 * 24 * 60 * 60); //1 year back
$queryDays = "SELECT * FROM `parts_timeline` WHERE partno ='" . $partno . "' AND dateChange > $timeYear ORDER BY dateChange ASC;"; //$timeSixMonths
//echo $queryDays . "
";
$resultDays = mysqli_query($db,$queryDays) or die("error in DB call $queryDays");
$totalRowDays = mysqli_num_rows($resultDays);
//echo "Total rows: " . $totalRows . "
";
//checking if new product...
$queryDaysBf = "SELECT * FROM `parts_timeline` WHERE partno ='" . $partno . "' AND dateChange < $timeYear ORDER BY dateChange DESC LIMIT 1;"; //$timeSixMonths
// echo $queryDaysBf . "
";
$resultDaysBf = mysqli_query($db,$queryDaysBf) or die("error in DB call $queryDaysBf");
$totalRowsBf = mysqli_num_rows($resultDaysBf);
$countDays = inventory_days($resultDays,$totalRowsBf,$totalRowDays);
//echo "total days: " . $countDays . "
";
//$units_sold_6m = round($units_sold/2); //find the average sales in the past 6 months
//echo $units_sold_6m . "
";
if($countDays > 30)
$ARS = $unit_sold/$countDays; // $units_sold_6m/$countDays
else $ARS = 0;
/************* ARS ***************/
if($sales > 0 && $unit_sold > 0)
$avg_price = round($sales / $unit_sold,2);
else $avg_price = 0;
$daily_loss = round($ARS * $avg_price,2);
//echo "P/N: " . $partno . " ARS= " . $ARS . " Units= " . $unit_sold . " Sales= " . $sales . " AVG PRICE= " . $avg_price . "
";
$report .= "P/N: " . $partno . " ARS= " . $ARS . " AVG PRICE= " . $avg_price . " Daily Loss = " . $daily_loss . "
";
$sum_lost_sales += $daily_loss; //$ARS * $avg_price;
}
mysqli_select_db($db,$dbname3);
}
$lostsales_all = round($sum_lost_sales,2);
$report .= "
TOTAL LOST SALES TODAY: " . $lostsales_all . "
";
mysqli_select_db($db,$dbname3);
$queryBack2 = "SELECT itemCode FROM `backorder-on-event` WHERE locate('-',itemCode)<1; ";
//echo $queryBack . "
";
$resultBack2 = mysqli_query($db,$queryBack2) or die("error in DB call $queryBack2");
//$backorderids_count = mysql_num_rows($resultBack);
while($myrow2 = mysqli_fetch_array($resultBack2,MYSQLI_ASSOC))
{
$partno = $myrow2["itemCode"];
if(substr($partno,-1) == 'P')
$partno = substr($partno,0,-1);
if(!in_array($partno,$product_array_on_event))
{
$product_array_on_event[] = $partno;
}
else continue;
//echo $partno . "
";
mysqli_select_db($db,$dbname);
$query1 = "SELECT p.partid, p.partno, p.unit_sold, p.sales, p.ARS FROM parts p WHERE p.partno = '$partno' LIMIT 1"; // $inactive_filter_cnd LIMIT 0 , 100
//echo $query1 . "
";
$result = mysqli_query($db,$query1) or die("error in DB call $query1");
while($row = mysqli_fetch_array($result,MYSQLI_ASSOC))
{
$partid = $row["partid"];
$unit_sold = $row["unit_sold"];
$sales = $row["sales"];
//$ARS = $row["ARS"];
//******************* INVENTORY DAYS ********************************
$priorDay = "";
$noCount = 0;
$markFirst = "";
$timeNow = time();
//$timeSixMonths = time() - (6 * 4.35 * 7 * 24 * 60 * 60); //6 months back
$timeYear = time() - (52 * 7 * 24 * 60 * 60); //1 year back
$queryDays = "SELECT * FROM `parts_timeline` WHERE partno ='" . $partno . "' AND dateChange > $timeYear ORDER BY dateChange ASC;"; //$timeSixMonths
//echo $queryDays . "
";
$resultDays = mysqli_query($db,$queryDays);
$totalRowDays = mysqli_num_rows($resultDays);
//echo "Total rows: " . $totalRows . "
";
//checking if new product...
$queryDaysBf = "SELECT * FROM `parts_timeline` WHERE partno ='" . $partno . "' AND dateChange < $timeYear ORDER BY dateChange DESC LIMIT 1;"; //$timeSixMonths
// echo $queryDaysBf . "
";
$resultDaysBf = mysqli_query($db,$queryDaysBf);
$totalRowsBf = mysqli_num_rows($resultDaysBf);
$countDays = inventory_days($resultDays,$totalRowsBf,$totalRowDays);
//echo "total days: " . $countDays . "
";
//$units_sold_6m = round($units_sold/2); //find the average sales in the past 6 months
//echo $units_sold_6m . "
";
if($countDays > 30)
$ARS = $unit_sold/$countDays; // $units_sold_6m/$countDays
else $ARS = 0;
/************* ARS ***************/
if($sales > 0 && $unit_sold > 0)
$avg_price = round($sales / $unit_sold,2);
else $avg_price = 0;
$daily_loss = round($ARS * $avg_price,2);
//echo "P/N: " . $partno . " ARS= " . $ARS . " Units= " . $unit_sold . " Sales= " . $sales . " AVG PRICE= " . $avg_price . "
";
$report .= "P/N: " . $partno . " ARS= " . $ARS . " AVG PRICE= " . $avg_price . " Daily Loss = " . $daily_loss . "
";
//echo "P/N: " . $partno . " ARS= " . $ARS . " Units= " . $unit_sold . " Sales= " . $sales . " AVG PRICE= " . $avg_price . "
";
$sum_lost_sales_on_event += $daily_loss;
}
mysqli_select_db($db,$dbname3);
}
$lostsales_on_event = round($sum_lost_sales_on_event,2);
$report .= "
TOTAL LOST SALES ON EVENT TODAY: " . $lostsales_on_event . "";
mysqli_select_db($db,$dbname);
//ADD TO THE DATA TO THE TIMELINE
$insert_lostsales_timeline = "INSERT INTO `lostsales_timeline` (`date`,`lostsales_all`,`lostsales_on_event`) VALUES ('$time','$lostsales_all','$lostsales_on_event');";
mysqli_query($db,$insert_lostsales_timeline);
mailmessageLostSales("LOST SALES REPORT - OVERSTOCKART.COM",$report);
?>