<?php
 include ("header.inc");
 include ("config.php");
 include ("style.css");
 include ("functions.inc");
 if (isset($_GET['sortby']))
 $sortwith = $_GET['sortby']; // this be the real magic
 else if (isset($sortby)) $sortwith = $sortby;
 else $sortwith = "unit_sold";

/* if (isset($_GET['partType']))
 $partType = $_GET['partType']; // this be the real magic
*/
 $partType = "Frame"; // this be the real magic
 
 if (isset($_GET['showsku']))
 $showsku = $_GET['showsku']; // this be the real magic
 
 if (isset($partType)) $showType = $partType;
 else $showType = "all";
 
 if (isset($_GET['partSupplier']))
 $partSupplier = $_GET['partSupplier']; // this be the real magic
 
 if (isset($partSupplier)) $showSupplier = $partSupplier;
 else $showSupplier = "all";
 
//echo "show: " . $showType . "partType: " . $partType . "<br>";

//Inactive
if(isset($_GET['inactive_filter']) && $_GET['inactive_filter'] == 'Yes')
{
$inactive_checked = "checked";
$inactive_filter_cnd = " AND `parts`.`active` = 1 ";
}
else
{
$inactive_checked = "";
$inactive_filter_cnd = "";
}

if ($showType != "" && $showType != "all")
{
$show_type = "AND `parts`.`ProductType` = '$showType' ";
$messageHeader = "Type  - $showType";	
}
else {
$show_type = "";
$messageHeader = "Type  - All";
}

if ($showSupplier != "" && $showSupplier != "all")
{
	if ($showSupplier == "novendor")
	{
	$show_supplier = "AND (`parts`.`supplier` = '') "; 
	$messageHeader .= " Supplier  - No Set Supplier";
	}
	else
	{
	$show_supplier = "AND (`parts`.`supplier` = '$showSupplier' || `parts`.`supplier` = '') ";  // ||  `parts`.`supplier` = ''
	$messageHeader .= " Supplier  - $showSupplier";	
	}
}
else {
$show_supplier = "";
$messageHeader .= " Supplier - All";
}

 if (isset($_GET['showsku']) && $showsku != "") //	$show_sku = "AND `itemID` LIKE '%$showsku%' ";
{
	$show_sku = "AND `parts`.`partno` LIKE '%$showsku%' ";
$messageHeader .= " Search for - $showsku";
}
else 
{
$show_sku = "";
}

 if (isset($_GET['triger1']))
 $avg_sale_tr = $_GET['triger1'];
 else $avg_sale_tr = 0;
 if (isset($_GET['triger2']))
 $total_sale_tr = $_GET['triger2'];
 else $total_sale_tr = 0;
 if (isset($_GET['triger3']))
 $qty_sold_tr = $_GET['triger3'];
 else $qty_sold_tr = 0;
 if (isset($_GET['factor']))
 $factor = $_GET['factor'];
 else $factor = 1;
 $current_month = date(m);
 $current_month = str_pad($current_month, 2, '0', STR_PAD_LEFT);
 if ($current_month != 12) 
 {
 $next_month = $current_month + 1;
 $next_month = str_pad($next_month, 2, '0', STR_PAD_LEFT);
 }
 else
 {
 $next_month = 01;
 $next_month = str_pad($next_month, 2, '0', STR_PAD_LEFT);
 }
 ?>
  <body> <!-- onLoad="initdt(document.create_order);" -->

    <style>
#PicStyle {
position:absolute;
/*visibility:hidden;
*/border:solid 1px #CCC;
padding:5px;
}
</style>
<script language="Javascript">
<!--
function ShowPicture(id,Source) {
if (Source=="1"){
if (document.layers) document.layers[''+id+''].visibility = "show"
else if (document.all) document.all[''+id+''].style.visibility = "visible"
else if (document.getElementById) document.getElementById(''+id+'').style.visibility = "visible"
}
else
if (Source=="0"){
if (document.layers) document.layers[''+id+''].visibility = "hide"
else if (document.all) document.all[''+id+''].style.visibility = "hidden"
else if (document.getElementById) document.getElementById(''+id+'').style.visibility = "hidden"
}
}
//-->
</script>
<br><br>
 <form style="text-align:left;" name="sorting" action="<?php echo $_SERVER['PHP_SELF'] ?>" method="get">
 Filter Inactive?
    <input type="checkbox" name="inactive_filter" value="Yes" <?php echo $inactive_checked;?> />
 Sort By:
 <select name="sortby" size="1" style="margin: 0px; padding: 0px; width: 75px; font-size: 11px;">
 <option value="unit_sold" <? if($sortwith=="unit_sold") echo "selected=\"selected\"";?> >Units Sold</option>
 <option value="sales"  <? if($sortwith=="sales") echo "selected=\"selected\"";?>>Sales</option>
  <option value="ARS"  <? if($sortwith=="ARS") echo "selected=\"selected\"";?>>ARS</option>
 </select> 
 &nbsp;&nbsp;
 Avg Sale over: <input name="triger1" style="margin: 0px; padding: 0px; width: 30px; font-size: 11px;" type="text" value="<?php echo $avg_sale_tr; ?>">
 &nbsp;&nbsp;
 Total Sales over: <input name="triger2" style="margin: 0px; padding: 0px; width: 30px; font-size: 11px;" type="text" value="<?php echo $total_sale_tr; ?>">
 &nbsp;&nbsp;
 Qty Sold over: <input name="triger3" style="margin: 0px; padding: 0px; width: 30px; font-size: 11px;" type="text" value="<?php echo $qty_sold_tr; ?>">
 &nbsp;&nbsp;
 Divide sales by: <input name="factor" style="margin: 0px; padding: 0px; width: 30px; font-size: 11px;" type="text" value="<?php echo $factor; ?>">
<br><br>
 Search Size &amp; Part Number: <input name="showsku" style="margin: 0px; padding: 0px; width: 75px; font-size: 11px;" type="text" value="<?php echo $showsku; ?>">
 &nbsp;&nbsp;
 Product Type:
 <select name="partType" size="1" style="margin: 0px; padding: 0px; width: 75px; font-size: 11px;">
<?php
$resultType = mysql_query ("SELECT * FROM `product_type`"); ?>
 <option value="all" <? if($showType=="all") echo "selected=\"selected\"";?>>All</option>
 <?php
 if ($rowType = mysql_fetch_array($resultType)) {
   do {
    $selected = "" ;
     if ($rowType["type"] == $showType)
		$selected="selected";
   		print "<option $selected value=\"" . $rowType["type"]."\">" . $rowType["type"]."\r";
	 } while($rowType = mysql_fetch_array($resultType));
}
?>
 </select>
  &nbsp;&nbsp;
 Preffered Supplier:
 <?php
 $resultSupplier = mysql_query ("SELECT * FROM `organization` WHERE `active` = 1");
 echo "<select name=\"partSupplier\">\r"; ?>
 <option value="all" <? if($showSupplier=="all") echo "selected=\"selected\"";?>>All</option>
 <option value="novendor" <? if($showSupplier=="novendor") echo "selected=\"selected\"";?>>No Vendor Set</option> 
 <?php if ($rowSupplier = mysql_fetch_array($resultSupplier)) {
   do {
		   if ($rowSupplier["organization"] == $showSupplier)
		   $selected = "selected=\"selected\"";
		   else $selected = "";
   		print "<option $selected value=\"" . $rowSupplier["organization"]."\">" . $rowSupplier["organization"]."\r";
	 } while($rowSupplier= mysql_fetch_array($resultSupplier));
	 }
	 ?>
	 </select>
 &nbsp;&nbsp;
 <input type="submit" name="submitreport" value="Run Report"/>
 </form>
 &nbsp;&nbsp; <button onClick="window.location='demand_excel_frames.php?sortby=<?= $sortwith; ?>&triger1=<?= $avg_sale_tr; ?>&triger2=<?= $total_sale_tr; ?>&triger3=<?= $qty_sold_tr; ?>&factor=<?= $factor; ?>&showsku=<?= $showsku; ?>&partType=<?= $showType; ?>&partSupplier=<?= $showSupplier; ?>'">Download Excel</button>
 </tr>
 </table>
</td></tr></table>
 <form name="create_order" action="order-creator-frames.php" method="post" onLoad> 
 <?php
 
table_header_peachtree_new("Demand Planning Report - $messageHeader", $factor);
 $countItems = 1;
 
 //$query1 = "SELECT * FROM `peachtree` JOIN `parts` ON `itemID` = `partno` WHERE `itemID` = 'VG485' ORDER BY `peachtree`.`$sortwith` DESC";
/* $query1 = "SELECT * FROM `peachtree` JOIN `parts` ON `itemID` = `partno` WHERE `itemID` NOT LIKE '%FR-%' AND `itemID` NOT LIKE 'X%' AND `parts`.`active` = 1 $show_type  $show_supplier $inactive_filter_cnd $show_sku ORDER BY `peachtree`.`$sortwith` DESC";*/
 $query1 = "SELECT * FROM `parts` WHERE `partno` NOT LIKE 'X%' $show_type $show_supplier $inactive_filter_cnd $show_sku ORDER BY `parts`.`$sortwith` DESC";
// echo $query1;
// $query1 = "SELECT * FROM `peachtree` JOIN `parts` ON `itemID` = `partno` WHERE `itemID` LIKE '%MON%' AND `parts`.`active` = 1 ORDER BY `peachtree`.$sortwith DESC";
 	//echo $query1;
	//TOTAL COST & QTY
 	$totalCost = 0;
 	$totalInv = 0; 
 $result = mysql_query($query1);
 while($myrow = mysql_fetch_array($result))
      {
$gross_margin = 0;
$avg_price = 0;
$costOnHand = 0;
$units_sold = 0;
$sales = 0;
$qty_on_hand = 0;
$qty_available = 0;
$recomended_qty = 0;
$ARS = 0;
$qty_remaining = 0;
$cogs_sold = 0;
 $partid = $myrow["partno"];
 $partSupplier = $myrow["supplier"];
 $name = $myrow["paintingname"];
 
 $future_event = "";
$totalUnitsBackorder = 0;
$totalUnitsOnEvent = 0;
$qty_reserved_on_events = 0;
$totalUnitsAmazon = 0;
$totalMyhabitOnEvent = 0;
$fullBin = 0;
$totalBin = 0;

 mysql_select_db($dbname2,$db);
	$queryCount = "SELECT qty, `SKU` FROM `fullfilment` WHERE `SKU` LIKE '%" . $partid . "%' AND `status` = 'Backorder';"; // GROUP BY `SKU`;"; // GROUP BY `SKU`
	//echo $queryCount . "<br>";
								$resultCount = mysql_query($queryCount);
								while ($rowCount = mysql_fetch_array($resultCount))
								{
								//echo $queryCount;
								$framepn =  $rowCount[1];
								$frame_units = $rowCount[0];
								
								$totalUnitsBackorder += $frame_units;	
													
								/*if ($countBackorder > 0)
								$future_event .= "<div style=\"float:right;padding:10px;\"><img src=\"images/alert.png\" align=\"right\" style=\"margin-top:10px; margin-right:10px; margin-bottom:10px;\"><a href=\"https://www.overstockart.net/ccm/fullfilment.php?q=" . $frameid . "&status=Backorder\" target=\"_blank\" style=\"color:#ae0011;font-weight:bold;\">" . $countBackorder . " On Backorder!</a></div>";*/
								}
		$now = time();
		$onemonth = strtotime("+1 month", $now); //was one month - $onemonth
		$twentydays = strtotime("+20 days", $now); //was one month - $onemonth
		$twomonth = strtotime("+2 month", $now); //was one month - $twomonth
  
		$queryCount = "SELECT `eventItems`.`qty`, `eventItems`.`SKU`, `eventItems`.`DateEnd`, `saleEvents`.`Type`, `saleEvents`.`Company`, `saleEvents`.`DateStart`, `eventItems`.`eventID` FROM `eventItems` LEFT JOIN `saleEvents` ON `eventItems`.`eventID` = `saleEvents`.`eventID` WHERE `eventItems`.`SKU` LIKE '%" . $partid . "%' AND `eventItems`.`DateEnd` > $now;";
		//echo $queryCount;
								$resultCount = mysql_query($queryCount);
								while ($rowCount = mysql_fetch_array($resultCount))
								{
								//AND `DateEnd` < $onemonth
								//AND `DateEnd` > $now
								//echo $queryCount;
								$frame_units = $rowCount[0];
								
								$SKU =  $rowCount[1];
								
								if(strpos($SKU,"-") > 0)
								{
								$artpn = substr($SKU,0,strpos($SKU,"-"));
								$framepn = substr($SKU,strpos($SKU,"-")+1);
								}
								else
								{
								$artpn = $SKU;
								$framepn = "";									
								}
								
								if((substr($artpn,0,1) == "2" && substr($artpn,-1) != "H") || substr($artpn,0,3) == "WOA")
									$eventHoldPercentage = 5;
								else $eventHoldPercentage = 50;
								
								$endEvent = $rowCount[2];
								$typeEvent = $rowCount[3];
								$companyEvent = $rowCount[4];
								$startEvent = $rowCount[5];
								
								if($typeEvent == 6) continue;
								//$totalMyhabitOnEvent += $frame_units;
								else if($startEvent < $twentydays) //$onemonth $endEvent
									{
			//echo $companyEvent . " DATE:" . date("m-d-Y",$startEvent) . " PN: " . $framepn  . " COUNT: " . $frame_units . "<BR>";
									$totalUnitsOnEvent += $frame_units;
									if((substr($artpn,0,1) == "2" && substr($artpn,-1) != "H") || substr($artpn,0,3) == "WOA")
										$qty_reserved_on_events += 2; //ONLY 2 ON THE EVENT
									else $qty_reserved_on_events += $frame_units*($eventHoldPercentage/100);
									}
								//echo $queryCount;
								}
								/*if ($countEventItems > 0)
								$future_event .= "<div style=\"clear: both\";></div><div style=\"float:right;padding:10px;\"><img src=\"images/clock.svg\" align=\"right\" style=\"margin-top:10px; margin-right:10px; margin-bottom:10px;\"><a href=\"eventItems.php?partnosearch=" . $frameid . "\" target=\"_blank\" style=\"color:#293b74;font-weight:bold;\">" . $countEventItems . " On Hold for Events</a></div>";*/
		
		mysql_select_db($dbname3,$db);
		
/*$queryCount = "SELECT qty AS sumQty,qtyReceived,qtyShipped AS sumShip, `SKU`, `shipDate` FROM `amazonvOrders` WHERE `SKU` LIKE '%" . $partid . "%' AND (`qty` - `qtyCanceled`) > (`qtyReceived` + `qtyShipped`) AND `fulfillmentCenter` <> 'CVG1' AND `fulfillmentCenter` <> 'CVG2';"; // GROUP BY `SKU`";
echo $queryCount . "<br>";*/

/*$queryCount = "SELECT avo.qty AS sumQty, avo.qtyReceived AS sumRec, avo.qtyShipped AS sumShip, avo.qtyCanceled AS sumCanceled, avo.`SKU`, avo.`shipDate`, avsi.qty AS sumInCarton, avsi.`carton_id` FROM `amazonvOrders` AS avo LEFT JOIN amazonvShipItem AS avsi ON avo.`primary` = avsi.amazonvOrderID WHERE avo.`SKU` LIKE '%" . $partid . "%' AND (avo.`qty` - avo.`qtyCanceled`) > (avo.`qtyReceived` + avo.`qtyShipped`) AND avo.`fulfillmentCenter` <> 'CVG1' AND avo.`fulfillmentCenter` <> 'CVG2';"; */

$queryCount = "SELECT avo.qty AS sumQty, avo.qtyReceived AS sumRec, avo.qtyShipped AS sumShip, avo.qtyCanceled AS sumCanceled, avo.`SKU`, avo.`shipDate`, SUM(avsi.qty) AS sumInCarton, avsi.`carton_id`, avo.`primary` FROM `amazonvOrders` AS avo LEFT JOIN amazonvShipItem AS avsi ON avo.`primary` = avsi.amazonvOrderID WHERE avo.`SKU` LIKE '%" . $partid . "%' AND avo.`fulfillmentCenter` <> 'CVG1' AND avo.`fulfillmentCenter` <> 'CVG2' GROUP BY avo.`primary`;"; 

//echo $queryCount;

								$resultCount = mysql_query($queryCount);
							while ($rowCount = mysql_fetch_array($resultCount))
							{
								$countAmazonOrder = $rowCount[0];
								$countAmazonRec = $rowCount[1];
								$countAmazonShip = $rowCount[2];
								$countAmazonCancel = $rowCount[3];

								$framepn =  $rowCount[4];								
								$shipDate = strtotime($rowCount[5]);
								$sumInCarton =  $rowCount[6];
								$cartonnumber =  $rowCount[7];
								
								/*
								//echo $rowCount[3] . " = " . $shipDate . "<BR>";
								$frame_units = $countAmazonOrder - ($countAmazonRec + $countAmazonShip);
								if ($frame_units > 0 && $shipDate < $twomonth)
								{
								//echo "frame units: " . $frame_units . "<br>";
								//echo $queryCount;
								$totalUnitsAmazon += $frame_units;
								}
								*/
								if($cartonnumber < 1) $sumInCarton = 0;
								//echo $rowCount[3] . " = " . $shipDate . "<BR>";
								$frame_units =  ($countAmazonOrder) - ($countAmazonRec + $sumInCarton + $countAmazonCancel);
								
								if ($frame_units > 0 && $shipDate < $twomonth)
								{
								//echo $queryCount;
								$totalUnitsAmazon += $frame_units;
								}								
								
							}

/*$queryCount = "SELECT qty AS sumQty,qtyReceived,qtyShipped AS sumShip, `SKU`, `shipDate` FROM `amazonvOrders` WHERE `SKU` LIKE '%" . $partid . "%' AND (`qty` - `qtyCanceled`) > (`qtyReceived` + `qtyShipped`) AND (`fulfillmentCenter` = 'CVG1' OR `fulfillmentCenter` = 'CVG2');"; // GROUP BY `SKU`";
//echo $queryCount . "<br>";*/

/*$queryCount = "SELECT avo.qty AS sumQty, avo.qtyReceived AS sumRec, avo.qtyShipped AS sumShip, avo.qtyCanceled AS sumCanceled, avo.`SKU`, avo.`shipDate`, avsi.qty AS sumInCarton, avsi.`carton_id` FROM `amazonvOrders` AS avo LEFT JOIN amazonvShipItem AS avsi ON avo.`primary` = avsi.amazonvOrderID WHERE avo.`SKU` LIKE '%" . $partid . "%' AND (`fulfillmentCenter` = 'CVG1' OR `fulfillmentCenter` = 'CVG2');"; */

$queryCount = "SELECT avo.qty AS sumQty, avo.qtyReceived AS sumRec, avo.qtyShipped AS sumShip, avo.qtyCanceled AS sumCanceled, avo.`SKU`, avo.`shipDate`, SUM(avsi.qty) AS sumInCarton, avsi.`carton_id`, avo.`primary` FROM `amazonvOrders` AS avo LEFT JOIN amazonvShipItem AS avsi ON avo.`primary` = avsi.amazonvOrderID WHERE avo.`SKU` LIKE '%" . $partid . "%' AND (`fulfillmentCenter` = 'CVG1' OR `fulfillmentCenter` = 'CVG2') GROUP BY avo.`primary`;";
//echo $queryCount;
								$resultCount = mysql_query($queryCount);
							while ($rowCount = mysql_fetch_array($resultCount))
							{
								$countAmazonOrder = $rowCount[0];
								$countAmazonRec = $rowCount[1];
								$countAmazonShip = $rowCount[2];
								$countAmazonCancel = $rowCount[3];

								$framepn =  $rowCount[4];								
								$shipDate = strtotime($rowCount[5]);
								$sumInCarton =  $rowCount[6];
								$cartonnumber =  $rowCount[7];
								
								/*//echo $rowCount[3] . " = " . $shipDate . "<BR>";
								$frame_units = $countAmazonOrder - ($countAmazonRec + $countAmazonShip);*/
								if($cartonnumber < 1) $sumInCarton = 0;
								//echo $rowCount[3] . " = " . $shipDate . "<BR>";
								$frame_units =  ($countAmazonOrder) - ($countAmazonRec + $sumInCarton + $countAmazonCancel);
								//$frame_units = $countAmazonOrder - ($countAmazonRec + $countAmazonShip + $countAmazonCancel);
								if ($frame_units > 0 && $shipDate < $twomonth)
								{
								//echo "frame units: " . $frame_units . "<br>";
								//echo $queryCount;
								$totalMyhabitOnEvent += $frame_units;
								}
							}
								/*$future_event .= "<div style=\"clear: both\";></div><div style=\"float:right;padding:10px;\"><img src=\"images/amazon.png\" align=\"right\" style=\"margin-top:10px; margin-right:10px; margin-bottom:10px;\"><a href=\"http://www.overstockart.net/sorter/amazon-sku-list.php?qty_filter=Yes&sortby=date_for_sort&filterPO=all&filterFC=all&filter1=" . $partno . "&submitreport=Run+Items\" target=\"_blank\" style=\"color:#333333;font-weight:bold;\">" . $countAmazonItems . " On Order by Amazon</a></div>";*/

 mysql_select_db($dbname,$db);
 
 $queryMon = "SELECT * FROM `pn-monthly` where itemID = '" . $partid . "';";
 //echo $queryMon;
 $monResult = mysql_query($queryMon);
 if (mysql_num_rows($monResult) > 0)
	 {
		while($rowMonthly = mysql_fetch_array($monResult))
		{
			if ( $rowMonthly[0] != NULL )
			{
			$current = "qty_" . $current_month;
			$next = "qty_" . $next_month;
			$current_monthly_sales = $rowMonthly["$current"];
			$next_monthly_sales = $rowMonthly["$next"];
			}
		}
	}
	
 //START - ADD QTY ON ORDER AND ACCEPTED
 	$queryTrans = "SELECT partid, unit_sold, sales, qty_on_hand, qty_available, active, artimage, closeout, cost, outofstock FROM parts WHERE partno ='" . $partid . "' LIMIT 1;";
 	$transResult = mysql_query($queryTrans);
	if (mysql_num_rows($transResult) > 0)
	 {
 	$rowTrans = mysql_fetch_array($transResult);
 	if ( $rowTrans[0] != "" )
 	{
		$partid1 = $rowTrans[0];
		$active =  $rowTrans[5];
		$units_sold = intval($rowTrans[1]);
		$sales = floatval($rowTrans[2]);
		$qty_on_hand = intval($rowTrans[3]);
		$qty_available = intval($rowTrans[4]);
		$closeout = $rowTrans[7];
		$costPart = $rowTrans[8];
		$outofstock = $rowTrans[9];
		
		$orgSupplier = "";
		$costOrder = 0;
		
		$query1 = "SELECT  ol.`cost`, o.`organization` FROM `orderlines` AS ol LEFT JOIN `orders` AS o ON `ol`.`orderid` =  `o`.`orderid` WHERE ol.`partid` =  '" . $partid1 . "' ORDER BY  ol.`line_id` DESC LIMIT 1"; 
		//echo $query1;
		 $result3 = mysql_query($query1);
		while($rowOnOrder = mysql_fetch_array($result3))
		 {
				$costOrder = $rowOnOrder[0];
				$orgSupplier = $rowOnOrder[1];
		 }
		 
		 if($showSupplier != "all" && $showSupplier != "novendor" && $orgSupplier != "")
				{
				 if($showSupplier != $orgSupplier) continue; 
				}
		
		if ($partSupplier != $orgSupplier && $orgSupplier != "")
		$partSupplier = $orgSupplier;
		 
		if ($costOrder > 1.0)
		$costOnHand = $costOrder;
		else
		$costOnHand = $costPart;		
		 
		if ($units_sold > 0)
 		$avg_price = number_format(($sales/$units_sold),2,'.','');
 		else
 		$avg_price = 0;
		

	if ($units_sold < $qty_sold_tr || $avg_price < $avg_sale_tr || $sales < $total_sale_tr) continue;
 	//echo "<br>" . $units_sold . " < " . $qty_sold_tr . " || " . $avg_price . " < " . $avg_sale_tr . " || " . $sales . " < " . $total_sale_tr;
	
 	$query1 = "SELECT quantity,qtyaccepted,qtyReceived,cost FROM `orderlines` WHERE `status` <> 'Closed' AND `status` <> 'Cancelled' AND (partid='" . $partid1 . "' or partid= '" . $partid . "');";
 	//echo $query1;
    $result3 = mysql_query($query1,$db);
 	//$rowOnOrder = mysql_fetch_array($result3, MYSQL_NUM);
 	$qty_on_order = 0;
 	$qty_accept = 0;
 	$qty_received = 0;
 	while($rowOnOrder = mysql_fetch_array($result3))
      {
 		    if ( $rowOnOrder[0] != NULL )
 			{
 			$qty_on_order += $rowOnOrder[0];
 			$qty_accept += $rowOnOrder[1];
 			$qty_received += $rowOnOrder[2];
/* 			$costOnHand = $rowOnOrder[3];*/
 			}
 	  }
	  
 	//******************* 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 ='" . $partid . "' AND dateChange > $timeYear ORDER BY dateChange ASC;"; //$timeSixMonths
	//echo $queryDays . "<br>";
	$resultDays = mysql_query($queryDays);
	$totalRowDays = mysql_num_rows($resultDays);
	//echo "Total rows: " . $totalRows . "<br>";
	
	 //checking if new product...
	 $queryDaysBf = "SELECT * FROM `parts_timeline` WHERE partno ='" . $partid . "' AND dateChange < $timeYear ORDER BY dateChange DESC LIMIT 1;";  //$timeSixMonths
			//	echo $queryDaysBf . "<BR>";
	$resultDaysBf = mysql_query($queryDaysBf);
	$totalRowsBf = mysql_num_rows($resultDaysBf);
	
	$countDays = inventory_days($resultDays,$totalRowsBf,$totalRowDays);
 }
 	
 	//echo "total days: " . $countDays . "<br>";
	//$units_sold_6m = round($units_sold/2);  //find the average sales in the past 6 months
 	//echo $units_sold_6m . "<br>";
 	if($countDays > 0)
 	$ARS = $units_sold/$countDays; // $units_sold_6m/$countDays
 	else $ARS = 0;
 	//echo $ARS . "<br>";
	
 	if ($ARS > 0 && $qty_on_hand > 0)
 	$days_until_out = round($qty_on_hand/$ARS);
 	else $days_until_out = 0;
 
/* 	if ($ARS > 0 && $qty_available > 0)
 	$days_until_out = round($qty_available/$ARS);
 	else $days_until_out = 0;
*/
  //echo $days_until_out . "<br>";
	$date_when_0 = 0;
  	$date_when_max = 0;
	$real_count_days = 0;
	if ($qty_available < 1)
		{	
			$real_count_days = 	number_of_days($last0,$lastInc);		
		}
		  
  		$qty_remaining = $qty_on_order - $qty_received; //remove the qty received from order
		
 		/**days until the stock will run out including the on order qty**/
 		if ($ARS > 0 && $qty_remaining > 0)
 		$days_with_on_order = round($qty_remaining/$ARS) + $days_until_out;
 		else $days_with_on_order = $days_until_out;
 		/**days until the stock will run out including the on order qty**/
   //*********************  INVENTORY DAYS  *************************
   
 	}
 	
 	else { 
 		$qty_on_order = 0;
 		$qty_accept = 0;
 		$qty_received = 0;
 		}
		
		$earlyOrderMsg = "";
		/***********Early Order PT - Start***********/
		if ($ARS > 0.1)
		{
		$qtyShipped = 0;
		$olOrg = "";
		$countOrderIDs = 0;
		$sumPT = 0;
		$productionTime = 0;
		//grab the start date of the order:
		$query4 = "SELECT  ol.`line_id`, ol.`orderid`, ol.`cost`, ol.`status`, ol.`quantity`,  o.`organization`, o.`startdate`
FROM `orderlines` AS ol
LEFT JOIN  `orders` AS o ON `ol`.`orderid` =  `o`.`orderid` 
WHERE ol.`partid` =  '" . $partid1 . "'
AND  (ol.`status` = 'Shipped' || ol.`status` = 'Closed')
ORDER BY  ol.`line_id` DESC"; 
		//echo $query4;
		$oResult4 = mysql_query($query4,$db);
		while($row4 = mysql_fetch_array($oResult4))
		{
			$orderlineClosed = $row4["line_id"];
			$productionStart = $row4["startdate"];
			$orderidIn = $row4["orderid"];
			$partOrg = $row4["organization"];
			if($olOrg == "")
			{
			$olOrg = $partOrg;
			}
			else if ($olOrg != $partOrg) continue;
			
			$query3 = "SELECT * FROM shipment_line WHERE orderline_id = '" . $orderlineClosed. "';"; 
			//echo $query2;
			$oResult2 = mysql_query($query3,$db);
			while($row2 = mysql_fetch_array($oResult2))
			{
				$qtyShipped += $row2["qty"];
			}
		
			$query5 = "SELECT  sl.`orderline_id`, sl.`shipid`, sl.`qty`, s.`organization`, s.`startdate` 
FROM  `shipment_line` AS sl
LEFT JOIN `shipment` AS s ON sl.`shipid` =  s.`shipid` 
WHERE sl.`orderline_id` = '" . $orderlineClosed . "'
ORDER BY `lineid` ASC LIMIT 1"; 
			//echo $query2;
			$oResult5 = mysql_query($query5,$db);
			while($row5 = mysql_fetch_array($oResult5))
			{
				$productionEnd = $row5["startdate"];
				$shipEnd = $row5["shipid"];
			}
			
			//production time...
			$productionTime = dateDiff($productionStart,$productionEnd);
			
			//remove anomolies
			if($productionTime > 100 || $productionTime < 10) continue;
			 
			//echo $partno . " PT = " . $productionTime . " ORDER ID=" . $orderidIn . " SHIP ID=" . $shipEnd . "<br>";
			$sumPT += $productionTime;
			$countOrderIDs++;		
        }
		
			if($countOrderIDs > 0)
			{		
			$avgPT = round($sumPT/$countOrderIDs);		
			
			//if 6 month sales qty is smaller then order just 6 MOnths sales qty...
			if($avgPT*$ARS*2 > 180*$ARS)
			$eoQty = 180*$ARS;
			else
			$eoQty = $avgPT*$ARS*2;
			
			 $qtyInRoute = 0;
			if ($qtyShipped > $qty_received)
			 $qtyInRoute = ($qtyShipped - $qty_received); 
			
			if((($qty_on_order - $qtyShipped) + $qty_on_hand + $qtyInRoute) < round($avgPT*$ARS,0))
			$earlyOrderMsg = "<br><b Style=\"font-size:10px;color:#AE0011;\">Early Qty = " . round($eoQty,0) . "</b>";
			}
		/********PT - End*************/
		}
		
		/*if( ($qty_available + $qty_remaining ) < ($units_sold/$factor))
 		  $flag = "#ae0011; font-weight:bold;";
 		else $flag = "#000000";
 		
 		if( $days_with_on_order < 120) //assuming 120 days supply
 		  $flag = "#1c9327; font-weight:bold;";
 		else $flag = "#000000";*/
 		
 		//recomended qty to order based on 2 motnhs + 35% growth supply time (90 days)
		$month_of_sales = round(($ARS) * 30);
		//echo "month_of_sales = " . $month_of_sales . "<br>";
		//$qty_reserved_on_events = $totalUnitsOnEvent/2;
		
		if ($month_of_sales > $qty_reserved_on_events)
		$qty_needed = $month_of_sales;
		else
		$qty_needed = $qty_reserved_on_events;
		//+ $totalMyhabitOnEvent
		$qty_in_bin = $qty_on_hand + $qty_remaining;
		$recomended_qty =  $qty_needed + $totalUnitsAmazon + $totalUnitsBackorder + $totalMyhabitOnEvent - ($qty_on_hand + $qty_remaining);
 		
		if ($recomended_qty >= 1)
 		{  $flag = "#1c9327; font-weight:bold;"; }
		/*else if ($recomended_qty <= 3 && $recomended_qty >= 1)
 		{ $recomended_qty = $recomended_qty; }*/
 		else 
		{
		$recomended_qty = 0;
		$flag = "#000000";
		}
 		
		$fullBin = round($ARS * 10 * 2,2); //Full Bin system
		//if ($fullBin > $totalUnitsAmazon)
		$totalBin = max($fullBin,$totalUnitsAmazon,$qty_reserved_on_events) + $totalMyhabitOnEvent + $totalUnitsBackorder;
		/*else
		$totalBin = $totalUnitsAmazon + $totalMyhabitOnEvent + $totalUnitsBackorder;*/
		
		if (($qty_on_hand + $qty_remaining) > 0.8*$totalBin) //green
		$binColor = "#2a9914";
		else if (($qty_on_hand + $qty_remaining) > 0.5*$totalBin) //yellow
		$binColor = "#f5f812";
		else if (($qty_on_hand + $qty_remaining) <= 0.5*$totalBin) //red
		$binColor = "#db1334";

 		/*$queryTrans = "SELECT partid,closeout FROM parts WHERE partno ='" . $partid . "' LIMIT 1;";
 		$transResult = mysql_query($queryTrans,$db);
 		$row = mysql_fetch_array($transResult, MYSQL_NUM);*/
 		if($partid1 == "")
 		  $border = "#293b74";
 		else $border = "#FFFFFF";
 		
 		if($closeout == '1')
 		$closeoutdisplay = "<br><b style=\"color:#293b74\">closeout</b>";
 		else
 		$closeoutdisplay = "";
 		
 $borderOnHand = ""; //BORDER FOR MESSAGED PARTS
 // Here we will get it to show the replies
 // This query selects the replies from the database where the thread ID matches the thread $_GET value.
 $sqlM = "SELECT * FROM `replies-parts` WHERE thread = '" . $partid1 . "' ORDER BY `id` DESC LIMIT 1";
 $resultM = mysql_query($sqlM);
 $date2 = time(); //time now
 $days3 = 3*24*60*60; //3 days
 // Now we are getting our results and making them an array
 /*******************************/
 if (mysql_num_rows($resultM) > 0)
 {
	 while($r = mysql_fetch_array($resultM)) {
	 $posted = $r[posted]; //the date posted
	 //if time now - change in inventory is smaller then 3 days then note a change in inventory
		if ($date2 - $posted < $days3)
		{
		  $borderOnHand = "border: medium #000066 dashed;";
		}
		else
		{
		 $borderOnHand = "";
		}
	 // End of Array
	 }
 }
 
 //changed $qty_on_hand
 	if ($qty_available > 0)
 	{
 	 $borderOutofStock = "";
 	 $boldOutofStock = "#000000;";
 	}
 	else
 	{
 	//echo "less then 1" . ;
 	 $borderOutofStock = "border: medium #ae0011 dashed;";
 	 $boldOutofStock = "#ae0011;";
 	}
 		
 	//TOTAL	QTY & COSTS
 	if ($qty_on_hand > 0)
 	{
 			$totalCostOnHand = $costOnHand * $qty_on_hand; 	
 			$totalCost += $totalCostOnHand;
 			$totalInv += $qty_on_hand;
 	}
	/*if ($qty_available > 0)
 	{
 			$totalCostOnHand = $costOnHand * $qty_available; 	
 			$totalCost += $totalCostOnHand;
 			$totalInv += $qty_available;
 	}*/
 		
	//if ($units_sold > $qty_sold_tr && $avg_price > $avg_sale_tr && $sales > $total_sale_tr) //
	//{
	$units_sold_period = number_format($units_sold/$factor,0,'.',''); //units sold in the period...
 		
 	//******************* EARN & TURN  ********************************/
	if ($costOnHand > 0)
	{
	
	if ($avg_price == 0) $gross_margin = 0;
	else
 	$gross_margin = (($avg_price - $costOnHand)/$avg_price)*100; //GROSS MARGIN
 	
	$cogs_sold = $costOnHand*$units_sold; //COST OF GOOD SOLD
 	/*$queryAvg = "SELECT AVG(qty_on_hand) AS avg_on_hand FROM `parts_timeline` WHERE `partno` = '" . $partid . "' AND dateChange > " . $timeYear . ";";
 	$resultAvg = mysql_query($queryAvg);
    $rowAvg = mysql_fetch_array($resultAvg);
 	$on_hand_avg = $rowAvg["avg_on_hand"];
 	
		if($on_hand_avg > 0)
		{	
		echo "on_hand_avg = " . $on_hand_avg . " * costOnHand = " . $costOnHand;
		$avg_inv_cost = $on_hand_avg * $costOnHand; //Average Inv Costs
		
		$earnNturn = $gross_margin * ($cogs_sold / $avg_inv_cost);
		}
		else
		{
		$onHandMessage = "NO AVERAGE ON HAND";
		}*/
	
	}
	else
	{
	$costMessage = "NO COST INFORMATION";
	}
 	//echo "<br>" . $partid . " EN= " . $earnNturn . " = " . "GROSSMARGIN(" . $gross_margin . ") * (COGS" . $cogs_sold  . " / " . $avg_inv_cost . " AVG INV)";
 	//******************* END EARN & TURN ******************************/
 	
 		$i == 0 ? $color = "#FFFFFF" : $color = "#DDDDDD";
 		$i == 0 ? $class = "normal" : $class = "normal2";
         $i == 0 ? $i = 1 : $i = 0;
 		?>		
 <tr onMouseOver=this.className='highlight' onMouseOut=this.className="<? echo $class; ?>" bgcolor="<? echo $color; ?>" style="color:<?php echo $flag; ?>; ">
 <td width="40"> 
 <?php echo $countItems; //"<input type='checkbox' name='check" . $partid . "' value='" . $partid . "'>"; ?>
 </td> 
   <td bgcolor="<?php echo $border; ?>" style=" <?= $borderOnHand; ?> " width="180">
   <? echo "<a target=_blank href=\"viewparts.php?partid=$partid1\">" . $partid . "</a>" . $closeoutdisplay; 
   echo "<br>Supplier: " . $partSupplier; 
   ?>
   </td>
   <td width="250"><? echo "<b style=\"color:#AE0011;\">" . $name . "</b>"; //"<a href=\"#\" onMouseOver=\"ShowPicture('PicStyle" . $countItems . "',1)\" onMouseOut=\"ShowPicture('PicStyle" . $countItems . "',0)\">" . $name . "</a>"; 
    if ($qty_on_hand < 1)
 	{
   echo "<br><span style=\"font-size:10px;font-weight:bold;\">" . "Qty 0 on: " . date("m.d.y",$last0) . "<br>Qty " . $qtyInc . " on " . date("m.d.y",$lastInc) . "<br>Days until out: " . $real_count_days . "</span>";
   }
echo "<br><span style=\"font-size:10px;font-weight:bold;\">A Month of Sales = " . $month_of_sales;
/*echo "<br>Qty Reserved for Events = " . $qty_reserved_on_events; */ 
echo "<br>Backorder qty = " . $totalUnitsBackorder;
echo "<br>Total Units on Events = " . $totalUnitsOnEvent;
echo "<br>Units to ship to Amazon = " . $totalUnitsAmazon;
echo "<br>Units to ship to MyHabit = " . $totalMyhabitOnEvent. "</span>";
   ?>
 <!--  <div class="PicStyle" id="PicStyle<?= $countItems; ?>"><img src="<?php echo "files/thumb/" . $artimage ?> "></div>-->
  <?php 
  if($active == '0')
		echo "<BR><b style=\"color:#ae0011\">DISCONTINUED!</b>";
  else
  {
  echo '<br /><a href="#" onClick="removeComm(' . $partid1 . ')" style="color:#AE0011; font-size:11px; font-weight:bold;">
  [discontinue]</a>';
  }
  
   if($outofstock == '1')
  {
		echo "<br><br><b style=\"color:#ae0011; font-size:11px; \">OUT OF STOCK AT VENDOR!</b>";
		  echo '<br><a href="#" onClick="markOut(' . $partid1 . ',0)" style="color:#293b74; font-size:11px; font-weight:bold;">[mark back in stock]</a>'; 
	} 
  else
  {
  echo '<br><br><b style=\"color:#12a23b; font-size:11px; \">IN STOCK AT VENDOR!</b><br><a href="#" onClick="markOut(' . $partid1 . ',1)" style="color:#AE0011; font-size:11px; font-weight:bold;">[set vendor out of stock]</a>';
  }
 ?>
   </td>
  <!-- <td width="60"><? echo $current_monthly_sales ; ?></td> -->
     <td width="60"><? echo "Units Sold: <a href=\"javascript:GlobalPopUp('monthly-current-pn.php?partno=$partid&total=$units_sold',800,200);\">" . $units_sold_period . "</a>";
 echo "<br>" . "$" . $sales; 
 ?></td>
   <td width="80">   <ul style="font-size:11px;list-style-type:none;padding:0; margin:3;color:<?=$binColor?>;">
<? echo "<li>Full Bin: " . $fullBin . "</li>";
   echo "<li>Amazon V.: " .  $totalUnitsAmazon . "</li>";
   echo "<li>Qty Reserved for Events: " .  $qty_reserved_on_events . "</li>";
   echo "<li>MyHabit: " . $totalMyhabitOnEvent . "</li>";
    echo "<li>Total Bin: " . $totalBin . "</li>";
   ?></ul></td>
   <td width="100"> 
   <br> <span style=" 
		   <?php 
		   /*if ($costOnHand > 0)
			{
			  if($on_hand_avg > 0)
			  {
			   if ($earnNturn >100) echo "color:#293b74;"; 
			   else echo "color:#AE0011;";?>  font-size:10px; font-weight:bold;">Eearn & Turn: <?= number_format($earnNturn,1,'.',','); 
			  }
			  else
			  {
			  echo "font-size:10px; font-weight:bold;\">" . $onHandMessage;
			  }
			   
		   }
		   else
		   {
		   echo "font-size:10px; font-weight:bold;\">" . $costMessage;
		   }*/
		   ?>
   
   </span></td>
  <td width="140" style=" <?= $borderOutofStock; ?>; font-size:10px; ">
  <b style="color: <?php echo $boldOutofStock; ?> ">
  <? //echo $qty_on_hand ; 
  echo "<input type='text' size='3' name='onhand" . $partid1 . "' value='" . $qty_on_hand . "'>";
  ?>
  <?php 
  if($qty_on_hand > $qty_available)
  echo "<br>Avail. Qty: " . $qty_available;
  
  echo "<br>Qty in Bin: " . $qty_in_bin;
  
  if($totalBin > $qty_in_bin)
  {
	  $needed_bin_qty = $totalBin - $qty_in_bin;
  echo "<br><u>Qty to complete Bin : " . $needed_bin_qty . "</u>";
  }
  ?>
  </b></td>
   <td width="60"><? echo $qty_remaining; //echo " " . $qty_remaining . " = " . $qty_on_order . " - " . $qty_received; 
    // echo "<input type='text' size='3' name='onorder" . $partid . "' value='" . $qty_remaining . "'>";
   ?>

   </td>
   <td width="60"><? echo  $countDays;  ?></td>
   <td width="60"><? echo  $units_sold; ?></td>
   <td width="60"><? echo  number_format($ARS,2,'.',','); 
    if ($qty_on_hand < 1 && $real_count_days > 0)
 	{
   echo "<br><span style=\"font-size:10px;font-weight:bold;\">Real ARS: " . number_format($qtyInc/$real_count_days,2,'.',',') . "</span>";
   }
   ?></td>
   <td width="60"><b><? echo  $days_until_out; ?></b></td>
   <td width="60"><b><? echo  $days_with_on_order; ?></b></td>
     <td width="60"><b><? echo  $recomended_qty;
	 echo $earlyOrderMsg; ?></b></td>
   <td width="60"> <?php echo "<input type='text' size='3' name='qty" . $partid1 . "' value='0'>"; ?></td>
 </tr>
 <?php 
 $countItems++;
 //	}
 }
 ?>
 <script language="Javascript" src="cal/calendar.js">
 </script>
 </table>  
 </div>
     </td>
   </tr>
   </table>
   
<table cellspacing="0" cellpadding="1" border="1" width="1080">
  <tr valign="top">
 	  <td valign="top">
 	<table cellspacing="0" cellpadding="1" border="1" width="1080">
 		<tr>
 	  <td width="100"><b>total qty:</b></td>
 		<td width="100"><b><? echo number_format($totalInv,2,'.',','); ?></b></td>
 		  <td width="100"><b>total cost:</b></td>
 		<td width="100"><b><? echo "$" . number_format($totalCost,2,'.',','); ?></b></td>
         <td width="100">  <input style="font-size: larger; color:#ffffff; background-color: #293b74; border: 3pt ridge lightgrey" onClick="return confirm('Are you sure you want to Update On Hand Values?')" type='submit' NAME='submitUpdate' value='Update On Hand Count' >
         </td>
 		</tr>
 	</table>
</td></tr></table>
<table cellspacing="0" cellpadding="1" border="1" width="1080">
 <tr valign="top">
     <td valign="top">
 	<table cellspacing="0" cellpadding="1" border="1" width="1080">
 <tr>
 <td class=menu>Order Number:<br> <input size="10" type="Text" name="ordernumber"></td>
 <td class=menu>Organization: <br>
 <?php 
 $result1 = mysql_query ("SELECT * FROM `organization` WHERE `active` = 1");
 echo "<select name=\"organization\">\r";
 if ($row = mysql_fetch_array($result1)) {
   do {
   if ($row["organization"] == $showSupplier)
		   $selected = "selected=\"selected\"";
		   else $selected = "";
   print "<option $selected value=\"" . $row["organization"]."\">" . $row["organization"]."\r";
	 } while($row = mysql_fetch_array($result1));
}

?>
</td>
 <td class=menu >Order Date: <br><input size="10" style="margin-right:10px;" type="Text" name="startdate" value="<?php echo date("m/d/Y");?>"><a href="javascript: void(0);" onClick="return getCalendar(document.create_order.startdate);" ><img src="cal/calendar.png" border="0" /></a>
 </td>
 <td class=menu>Delivery Date: <br><input size="10" style="margin-right:10px;" type="Text" name="enddate" value="<?php echo date("m/d/Y",strtotime("+1 week", time()));?>"><a href="javascript: void(0);" onClick="return getCalendar(document.create_order.enddate);"><img src="cal/calendar.png" border="0" /></a></td>
 <td class=menu>Status: <br>
 <?php
 $result1 = mysql_query ("select * from `status`");
  echo "<select name=\"status\">\r";
  if ($row = mysql_fetch_array($result1)) {
    do {
     $selected = "" ;
      if ($row["status"] == "Review")
 		  $selected="selected";
    print "<option $selected value=\"" . $row["status"]."\">" . $row["status"]."\r";
 	 } while($row = mysql_fetch_array($result1));
 }
 ?>
 </td>
  <td class=menu>Order Type:<br>
<?
$resultType = mysql_query ("SELECT * FROM ordertype");
 echo "<select name=\"orderType\">\r";
 if ($rowType = mysql_fetch_array($resultType)) {
   do {
/*	  $selected = "" ;
     if ($row["type"] == $selecttype)
	   $selected="selected";
*/   print "<option value=\"". $rowType["type"]."\" >". $rowType["type"]."\r";
	 } while($rowType = mysql_fetch_array($resultType));
}
?>
  </td>
 <td class=menu>
 <!--<input type="hidden" name="status" value="Initiated" />-->
 <input type="hidden" name="stage" value="Details Received" />
  <!--<input type="hidden" name="orderType" value="Regular" />-->
 <input style="font-size: larger; color:#ffffff; background-color: #ae0011; border: 3pt ridge lightgrey"onClick="return confirm('Are you sure you want to Create this Order?')" type='submit' NAME='submitOrder' value='Create Frames Order' ></td>
  </tr>
 </table>
</td></tr></table>
 </form>
 <center><br><br>
 <button onClick="window.location='index.php'">Back to Main Menu</button> 
 <? include ("footer.inc"); 
 ?>
 