<?php
$time_start = microtime(true);
 include ("header.inc");
 include ("config_code.php");
 include ("style.css");
 include ("functions.inc");
 echo "<link href='css/style.css' rel='stylesheet' type='text/css'>";
?>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<script>
	$(document).ready(function(){
		$('#scrollable_item').scroll(function(){
		//	console.info($(this).find('thead').offset().top+'thead top');
		//	console.info($(this).find('thead').offset().top+'thead top');
		$('.table_header_moved > div').each(function(){
		 	$(this).width($('#scrollable_item table th:eq( '+$(this).index() +' )').width());
		 	$(this).height($('#scrollable_item table th:eq( '+$(this).index() +' )').height());
		});
		$('.table_header_moved').show();
		//	$('')
		});
	});
</script>
<?php
 if (isset($_GET['sortby']))
 $sortwith = $_GET['sortby']; // this be the real magic
 else if (isset($sortby)) $sortwith = $sortby;
 else $sortwith = "unit_sold";
//Inactive
if(isset($_GET['inactive_filter']) && $_GET['inactive_filter'] == 'Yes')
{
$inactive_checked = "checked";
$inactive_filter_cnd = " AND p.`active` = 1 ";
}
else
{
$inactive_checked = "";
$inactive_filter_cnd = "";
}
 /*if (isset($_GET['partType']))
 $partType = $_GET['partType'];*/ // this be the real magic
 $partType = "Molding"; // 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>";
if ($showType != "" && $showType != "all")
{
$show_type = "AND `parts`.`ProductType` = '$showType' ";
$messageHeader = "Type  - $showType";	
}
else {
$show_type = "";
$messageHeader = "Type  - All";
}
if (isset($_GET['showsku']) && $showsku != "") //	$show_sku = "AND `itemID` LIKE '%$showsku%' ";
{
	$show_sku = "AND p.`partno` LIKE '%$showsku%' ";
$messageHeader .= " Search for - $showsku";
}
else 
{
$show_sku = "";
}
if ($showSupplier != "" && $showSupplier != "all")
{
	if ($showSupplier == "novendor")
	{
	$show_supplier = "AND (p.`supplier` = '') ";
	$messageHeader .= " Supplier  - No Set Supplier";
	}
	else
	{
	$show_supplier = "AND (p.`supplier` = '$showSupplier' || p.`supplier` = '') ";  // ||  `parts`.`supplier` = ''
	$messageHeader .= " Supplier  - $showSupplier";	
	}
}
else {
$show_supplier = "";
$messageHeader .= " Supplier - All";
}
 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 = 1;
 if (isset($_GET['triger3']))
 $qty_sold_tr = $_GET['triger3'];
 else $qty_sold_tr = 1;
 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);
 }
if(!isset($showsku)){
	$showsku = '';
}
 ?>
 <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>
<div class="open-order-sec full_width">
 <form style="text-align:left;" name="sorting" action="<?php echo $_SERVER['PHP_SELF'] ?>" method="get">
 <ul>
  <li>
	  <label>Filter Inactive?</label>
	  <input type="checkbox" name="inactive_filter" value="Yes" <?php echo $inactive_checked;?> />
  </li>
  <li>
 	  <label>Sort By:</label>
	  <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> 
  </li>
  <li>
     <label>Avg Sale over:</label>
	 <input name="triger1" style="margin: 0px; padding: 0px; width: 30px; font-size: 11px;" type="text" value="<?php echo $avg_sale_tr; ?>">
  </li>
  <li>
     <label>Total Sales over: </label>
     <input name="triger2" style="margin: 0px; padding: 0px; width: 30px; font-size: 11px;" type="text" value="<?php echo $total_sale_tr; ?>">
  </li>
  <li>
  	<label>Qty Sold over: </label>
  	<input name="triger3" style="margin: 0px; padding: 0px; width: 30px; font-size: 11px;" type="text" value="<?php echo $qty_sold_tr; ?>">
  </li>
  <li>
	  <label>Divide sales by:</label>
	  <input name="factor" style="margin: 0px; padding: 0px; width: 30px; font-size: 11px;" type="text" value="<?php echo $factor; ?>">
  </li>
</ul>
<ul>
	<li>
	  <label>Search Size &amp; Part Number:</label>
	  <input name="showsku" style="margin: 0px; padding: 0px; width: 75px; font-size: 11px;" type="text" value="<?php echo $showsku; ?>">
	</li>
 	<li>
 		<label>Product Type:</label>
 		<select name="partType" size="1" style="margin: 0px; padding: 0px; width: 75px; font-size: 11px;">
			<?php
			$resultType = mysqli_query ($db,"SELECT * FROM `product_type`"); ?>
			 <option value="all" <? if($showType=="all") echo "selected=\"selected\"";?>>All</option>
			 <?php
			 if ($rowType = mysqli_fetch_array($resultType,MYSQLI_ASSOC)) {
			   do {
			    $selected = "" ;
			     if ($rowType["type"] == $showType)
					$selected="selected";
			   		print "<option $selected value=\"" . $rowType["type"]."\">" . $rowType["type"]."\r";
				 } while($rowType = mysqli_fetch_array($resultType,MYSQLI_ASSOC));
			}
			?>
	    </select>
	</li>
 	<li>
 		<label>Preferred Supplier:</label>
 		<?php
			$supplierType = NULL;
		 $resultSupplier = mysqli_query ($db,"SELECT * FROM `organization` WHERE `active` = 1 ORDER BY productType ASC");
		 ?>
		 <select name="partSupplier">
		 <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 = mysqli_fetch_array($resultSupplier,MYSQLI_ASSOC)) {
		   do {
				   if ($rowSupplier["organization"] == $showSupplier)
				   $selected = "selected=\"selected\"";
				   else $selected = ""; 
				   
				   if($supplierType != $rowSupplier["productType"]) { //has changed
				   if($supplierType !== NULL)
				   echo '</optgroup>';  // close previous optgroup
				   $supplierType = $rowSupplier["productType"];
				   echo '<optgroup label="' . htmlspecialchars($supplierType) . '">';  // start optgroup
				   }
				   
		   		print "<option $selected value=\"" . $rowSupplier["organization"]."\">" . $rowSupplier["organization"]."\r";
			 } while($rowSupplier= mysqli_fetch_array($resultSupplier,MYSQLI_ASSOC));
			 }
		 ?>
		 </select>
 	</li>
 		<li>
		<input type="submit" name="submitreport" value="Run Report"/>
	</li>
</ul>
 </form>
</div>
<div class="open-order-sec full_width" style="margin-bottom:10px;margin-top:10px;">
<button onClick="window.location='demand_excel.php?sortby=<?=$sortwith;?>&triger1=<?=$avg_sale_tr;?>&triger2=<?= $total_sale_tr;?>&triger3=<?= $qty_sold_tr; ?>&factor=<?= $factor; ?>&showsku=<?=$showsku;?>&type=<?=$showType;?>&partSupplier=<?=$showSupplier;?>'" class="oo_submit">Download Excel</button>
</div> 
 </tr>
 
 </table> 
</td></tr></table>
<div class="open-order-sec full_width">
 <form name="create_order" action="order-creator.php" method="post" onLoad style="padding:0 !important;"> 
 <?php
if(!isset($showsku)){
	$showsku = '';
}
 
 table_header_moldings("Demand Planning Report Type  - $showType Search - $showsku", $factor);
 $countItems = 1;
 $moldings_to_make = "";
 //$query1 = "SELECT * FROM `peachtree` JOIN `parts` ON `itemID` = `partno` WHERE `itemID` = 'VG485' ORDER BY `peachtree`.`$sortwith` DESC";
  $query1 = "SELECT m.moldingid, m.qty_on_hand, m.frameid, m.title, m.feetinbox, m.vendor, p.supplier FROM `moldings` AS m  JOIN `parts` AS p ON m.`moldingid` = p.`pnchina` WHERE 1=1 $inactive_filter_cnd $show_supplier $show_sku ORDER BY p.`$sortwith` DESC"; //`itemID` NOT LIKE '%FR-%' AND
// $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 = mysqli_query($db,$query1);
 while($myrow = mysqli_fetch_array($result, MYSQLI_ASSOC))
      {
$gross_margin = 0;
$avg_price = 0;
$costOnHand = 0;
$costOnHandFeet = 0;
$units_sold = 0;
$sales = 0;
$qty_on_hand = 0; 
$qty_available = 0;
$recomended_qty = 0;
$recomended_qty_box_std = 0;
$need_bin_std_box = 0;
$ARS = 0;
$qty_remaining = 0;
$cogs_sold = 0;
$totalfeetsold = 0;
$totalsales = 0;
$partid1 = "";
$artimage = "";
 $partid = $myrow["moldingid"];
	$qty_on_hand = $myrow["qty_on_hand"];
	$frameid = $myrow["frameid"];
	$stackFrame = $frameid;	
	$stackFrameText = "";
	//grab the stacked frames if available...
	$queryStack = "SELECT FrameSKU, MoldingSKU FROM StackFrames WHERE MoldingSKU = '" . $partid . "' ;"; 
 $stackResult = mysqli_query($db,$queryStack);
 while ($rowStack = mysqli_fetch_array($stackResult, MYSQLI_ASSOC))
 { 
  $stackFrame .= "|" . $rowStack["FrameSKU"];
  $stackFrameText .= "<br>" . $rowStack["FrameSKU"];
 }
	
	$feetinbox = $myrow["feetinbox"];
	$name = $myrow["title"];
	$partSupplier = $myrow["supplier"]; //$myrow["vendor"];  
	$feetonhand = $qty_on_hand * $feetinbox;
   //$queryMon = "SELECT * FROM `pn-monthly` where `itemID` LIKE '%" . $frameid . "%' AND SUBSTR(`itemID`,1,3) = 'FR-';";
	$queryMon = "SELECT * FROM `pn-monthly` where `itemID` REGEXP '" . $stackFrame . "' AND SUBSTR(`itemID`,1,3) = 'FR-';";
 //echo $queryMon;
 $monResult = mysqli_query($db,$queryMon);
 if (mysqli_num_rows($monResult) > 0)
	 {
		while($rowMonthly = mysqli_fetch_array($monResult, MYSQLI_NUM))
		{
			if ( $rowMonthly[0] != NULL )
			{
			$current = "qty_" . $current_month;
			$next = "qty_" . $next_month;
			$current_monthly_sales = $rowMonthly["$current"];
			$next_monthly_sales = $rowMonthly["$next"];
			}
		}
	}
$future_event = "";
$totalfeetBackorder = 0;
$totalfeetOnEvent = 0;
$totalfeetReservedOnEvent = 0;
$totalfeetAmazon = 0;
$totalMyhabitFeetOnEvent = 0;
$confidence = 0;
$std_deviation = 0;
$z_code = 0;
$fullBin = 0;
$totalBin = 0;
 mysqli_select_db($db,$dbname2);
   /* $queryCount = "SELECT SUM(qty), `SKU` FROM `fullfilment` WHERE `SKU` LIKE '%" . $frameid . "%' AND `status` = 'Backorder' GROUP BY `SKU` ;"; // GROUP BY `SKU`*/
	 $queryCount = "SELECT SUM(qty), `SKU` FROM `fullfilment` WHERE `SKU` REGEXP '" . $stackFrame . "' AND `status` = 'Backorder' GROUP BY `SKU` ;"; // GROUP BY `SKU`
	//echo $queryCount . "<br>";
								$resultCount = mysqli_query($db,$queryCount);
								while ($rowCount = mysqli_fetch_array($resultCount, MYSQLI_NUM))
								{
								//echo $queryCount;
								$frame_units = intval($rowCount[0]);
								$framepn =  $rowCount[1];
								$feetperframe = getSize($framepn);
								
								$totalfeetBackorder += $frame_units * $feetperframe;	
													
								/*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);
  
		/*$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 `SKU` LIKE '%" . $frameid . "%' AND `eventItems`.`DateEnd` > $now GROUP BY `SKU`;";*/
		
		$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 `SKU` REGEXP '" . $stackFrame . "' AND `eventItems`.`DateEnd` > $now GROUP BY `SKU`;";
     	//echo $queryCount;
		/*$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;";*/
							$resultCount = mysqli_query($db,$queryCount);
							while ($rowCount = mysqli_fetch_array($resultCount, MYSQLI_NUM))
							{
								//echo $queryCount;
								$frame_units = intval($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($endEvent < $onemonth)
								{
									$feetperframe = getSize($framepn);
									//echo $queryCount;
									if($typeEvent == 6) continue;
									//$totalMyhabitFeetOnEvent += $frame_units * $feetperframe;
									else
									{		
									$totalfeetOnEvent += $frame_units * $feetperframe;
								
									if((substr($artpn,0,1) == "2" && substr($artpn,-1) != "H") || substr($artpn,0,3) == "WOA")
										$totalfeetReservedOnEvent += 2 * $feetperframe; //ONLY 2 ON THE EVENT
									else 
										$totalfeetReservedOnEvent += ($frame_units * $feetperframe)*($eventHoldPercentage/100);									
									}
								}
							}
								/*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>";*/
		
		mysqli_select_db($db,$dbname3);
		
/*$queryCount = "SELECT SUM(qty) AS sumQty,SUM(qtyReceived) AS sumRec, `SKU`, `shipDate` FROM `amazonvOrders` WHERE `SKU` LIKE '%" . $frameid . "%' AND (`qty` - `qtyCanceled`) > (`qtyReceived` + `qtyShipped`) AND `fulfillmentCenter` <> 'CVG1' AND `fulfillmentCenter` <> 'CVG2' GROUP BY `SKU`";*/
/*$queryCount = "SELECT avo.qtyConfirmed 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 '%" . $frameid . "%' AND avo.`fulfillmentCenter` <> 'CVG1' AND avo.`fulfillmentCenter` <> 'CVG2' GROUP BY avo.`primary`;";  */
$queryCount = "SELECT avo.qtyConfirmed 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` REGEXP '" . $stackFrame . "' AND avo.`fulfillmentCenter` <> 'CVG1' AND avo.`fulfillmentCenter` <> 'CVG2' GROUP BY avo.`primary`;"; 
//echo $queryCount;
								$resultCount = mysqli_query($db,$queryCount);
						while ($rowCount = mysqli_fetch_array($resultCount, MYSQLI_NUM))
						{
						//echo $queryCount;
								$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];
								
								if($cartonnumber < 1) $sumInCarton = 0;
								$frame_units =  ($countAmazonOrder) - ($countAmazonRec + $sumInCarton + $countAmazonCancel);
								if($frame_units > 0) //$frame_units = 0;
								//echo $framepn . " - frame units: " . $frame_units . " = " . $countAmazonOrder . " - " .  $countAmazonRec . "+" . $sumInCarton . "+" . $countAmazonCancel . "<br>";
								if ($frame_units > 0  && $shipDate < $onemonth)
								{
                                $feetperframe = getSize($framepn);
                                  
								$totalfeetAmazon += $frame_units * $feetperframe;
								}
							}
							
/*$queryCount = "SELECT SUM(qty) AS sumQty,SUM(qtyReceived) AS sumRec, `SKU`, `shipDate` FROM `amazonvOrders` WHERE `SKU` LIKE '%" . $frameid . "%' AND (`qty` - `qtyCanceled`) > (`qtyReceived` + `qtyShipped`) AND (`fulfillmentCenter` = 'CVG1' OR `fulfillmentCenter` = 'CVG2') GROUP BY `SKU`";*/
/*$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 '%" . $frameid . "%' AND (`fulfillmentCenter` = 'CVG1' OR `fulfillmentCenter` = 'CVG2') GROUP BY avo.`primary`;";*/
$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` REGEXP '" . $stackFrame . "' AND (`fulfillmentCenter` = 'CVG1' OR `fulfillmentCenter` = 'CVG2') GROUP BY avo.`primary`;";
//echo $queryCount;
								$resultCount = mysqli_query($db,$queryCount);
						while ($rowCount = mysqli_fetch_array($resultCount, MYSQLI_NUM))
						{
						//echo $queryCount;
								$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];
								
								//$frame_units = $countAmazonOrder - $countAmazonRec;
								
								if($cartonnumber < 1) $sumInCarton = 0;
								//echo $rowCount[3] . " = " . $shipDate . "<BR>";
								$frame_units =  ($countAmazonOrder) - ($countAmazonRec + $sumInCarton + $countAmazonCancel);
								
								if ($frame_units > 0  && $shipDate < $onemonth)
								{
                                    $feetperframe = getSize($framepn);
                                    //echo $queryCount;
								$totalMyhabitFeetOnEvent += $frame_units * $feetperframe;
								}
							}
								
								/*$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>";*/
 mysqli_select_db($db,$dbname);
	
/*$queryFeetSold = "SELECT * FROM `peachtree` where `itemID` LIKE '" . $frameid . "%' AND (SUBSTR(`itemID`,1,3) = 'FR-' || SUBSTR(`itemID`,1,2) = 'M-');";*/
$queryFeetSold = "SELECT * FROM `peachtree` where `itemID` REGEXP '" . $stackFrame . "' AND (SUBSTR(`itemID`,1,3) = 'FR-' || SUBSTR(`itemID`,1,2) = 'M-');";
/*$queryFeetSold = "SELECT * FROM `parts` where `partno` LIKE '" . $frameid . "%';"; // AND SUBSTR(`pnchina`,1,2) = 'M-';";*/
//echo $queryFeetSold . "<br>";
 $totalfeetsold = 0;
 $totalunits = 0;
	$frame_units = 0;
	$frame_sales = 0;
	$feetsoldpermonth = 0;
	$feetsoldpertwoweeks = 0;
	
 $feetResult = mysqli_query($db,$queryFeetSold);
 if (mysqli_num_rows($feetResult) > 0)
	 {
		while($rowFeet = mysqli_fetch_array($feetResult, MYSQLI_ASSOC))
		{
			$framepn = $rowFeet["itemID"];
			$frame_units = intval($rowFeet["units_sold"]);
			$frame_sales = floatval($rowFeet["sales"]);
			
            $feetperframe = getSize($framepn);
            //echo "frame units: " . $frame_units . "feet per frame: " . $feetperframe;
			$totalunits += $frame_units;
			$totalfeetsold += $frame_units * $feetperframe;	
			$totalsales += $frame_sales;
		}
		
		if ($totalfeetsold > 0)
 		$avg_price = number_format(($totalsales/$totalfeetsold),2,'.','');
 		else
 		$avg_price = 0;
		
		/*$feetsoldpermonth = round($totalfeetsold/12,2);*/
		$feetsoldperday = round($totalfeetsold/365,2);
	}
	
 //START - ADD QTY ON ORDER AND ACCEPTED
 $partid = trim($partid);
$queryTrans = "SELECT `partid`, `unit_sold`, `sales`, `qty_on_hand`, `qty_available`, `active`, `artimage`, `closeout`, `outofstock` FROM `parts` WHERE `pnchina`=TRIM('".$partid."') LIMIT 1;";
//echo $queryTrans;
$transResult2 = mysqli_query($db,$queryTrans);
if (mysqli_num_rows($transResult2 ) > 0)
 {
 	$rowTrans = mysqli_fetch_array($transResult2, MYSQLI_NUM);
	//echo "testing: " . $rowTrans[0];
 	if ( $rowTrans[0] != "" )
 	{
		$partid1 = $rowTrans[0];
		$qty_available = intval($rowTrans[3]);  //$rowTrans[4];
		$active =  $rowTrans[5];
		$artimage = $rowTrans[6];
		$closeout = $rowTrans[7];
		$outofstock = $rowTrans[8];
	//echo "in here!";
		/*$query1 = "SELECT quantity,qtyaccepted,qtyReceived,cost FROM `orderlines` WHERE STATUS <> 'Closed' AND STATUS <> 'Cancelled' AND (partid='" . $partid1 . "' or partid= '" . $partid . "');";*/
		$query1 = "SELECT quantity,qtyaccepted,qtyReceived FROM `orderlines` WHERE STATUS <> 'Closed' AND STATUS <> 'Cancelled' AND partid='" . trim($partid1) . "';";
		//echo $query1;
		$result3 = mysqli_query($db,$query1);
		//$rowOnOrder = mysql_fetch_array($result3, MYSQL_NUM);
		$qty_on_order = 0;
		$qty_accept = 0;
		$qty_received = 0;
		while($rowOnOrder = mysqli_fetch_array($result3, MYSQLI_NUM))
		  {
				if ( $rowOnOrder[0] != NULL )
				{
				$qty_on_order += $rowOnOrder[0];
				$qty_accept += $rowOnOrder[1];
				$qty_received += $rowOnOrder[2];
				}
		  }
		
		$orgSupplier = "";
		//$query1 = "SELECT `cost` FROM `orderlines` WHERE `partid`='" . $partid1 . "' ORDER BY `line_id` DESC LIMIT 1;";
		$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 . "<BR>";
		$result3 = mysqli_query($db,$query1);
		while($rowOnOrder = mysqli_fetch_array($result3, MYSQLI_NUM))
		  {
				$costOnHand = $rowOnOrder[0];	
				$costOnHandFeet = $costOnHand/$feetinbox;
				$orgSupplier = $rowOnOrder[1];
		  }
	  
	  //FIX FEBRUARY 15 - AMITAI
	   if($showSupplier != "all" && $showSupplier != "novendor" && $orgSupplier != "")
				{
				 if($showSupplier != $orgSupplier) {
					//echo $showSupplier . " != " . $orgSupplier;
					 continue;
				 }
				}
		
		if ($partSupplier != $orgSupplier && $orgSupplier != "")
		$partSupplier = $orgSupplier;
		
 	//******************* 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 partid = '" . $partid1 . "' AND dateChange > $timeYear ORDER BY dateChange ASC;"; //$timeSixMonths
	//echo $queryDays . "<br>";
	$resultDays = mysqli_query($db,$queryDays);
	$totalRowDays = mysqli_num_rows($resultDays);
	//echo "Total rows: " . $totalRows . "<br>";
	
	 //checking if new product...
	 $queryDaysBf = "SELECT * FROM `parts_timeline` WHERE partid = '" . $partid1 . "' AND dateChange < $timeYear AND qty_on_hand > 0 ORDER BY dateChange DESC LIMIT 1;";  //$timeSixMonths
			//	echo $queryDaysBf . "<BR>";
	$resultDaysBf = mysqli_query($db,$queryDaysBf);
	$totalRowsBf = mysqli_num_rows($resultDaysBf);
	
	$firstArrival = 0;
	if($totalRowsBf < 1)
	{
	$sqlArr = "SELECT * FROM `shipment_line` WHERE `partid` = " . $partid1 . " AND `qtyRec` != '' AND `dateRec` != '' ORDER BY lineid ASC LIMIT 1";
	$resultArrival = mysqli_query($db,$sqlArr);
	
	while($rowArr = mysqli_fetch_array($resultArrival, MYSQLI_ASSOC))
			{
				$firstArrival = strtotime(str_replace('-', '/', $rowArr["dateRec"]));
				//echo "FIRST TIME: " . $rowArr["dateRec"] . " IN UNIX: " . $firstArrival;
			}	
	} 				
	
	$countDays = inventory_days($resultDays,$totalRowsBf,$totalRowDays,$firstArrival);
 }
 	
 	if($countDays > 1)
 	$ARS = round($totalfeetsold/$countDays,2); // $units_sold_6m/$countDays
 	else $ARS = 0;
 	//echo $ARS . "<br>";
	
 	if ($ARS > 0 && $feetonhand > 0)
 	$days_until_out = round($feetonhand/$ARS);
 	else $days_until_out = 0;
  
	$date_when_0 = 0;
  	$date_when_max = 0;
	$real_count_days = 0;
	if(!isset($last0)){
		$last0 = ''; 
	}
	if(!isset($lastInc)){
		$lastInc = '';
	}
	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
		$feetonorder = $feetinbox * $qty_remaining;
		
 		/**days until the stock will run out including the on order qty**/
 		if ($ARS > 0 && $feetonorder> 0)
 		$days_with_on_order = round($feetonorder/$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;
		
	 	$moldings_to_make .= "Missing from QB or Communicator:" . $frameid . "," . $partid . "<br>";
 		}
		
		
		/***********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 = mysqli_query($db,$query4);
		while($row4 = mysqli_fetch_array($oResult4,MYSQLI_ASSOC))
		{
			$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 = mysqli_query($db,$query3);
			while($row2 = mysqli_fetch_array($oResult2,MYSQLI_ASSOC))
			{
				$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 = mysqli_query($db,$query5);
			while($row5 = mysqli_fetch_array($oResult5,MYSQLI_ASSOC))
			{
				$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++;		
        }
		
		
			
			$avgPT = 14; //8; //round($sumPT/$countOrderIDs);	
			$queryPT = "SELECT `deliveryTime` FROM `organization` WHERE `organization` = '" . $partSupplier . "' LIMIT 1;"; 
			//echo $query2;
			$oResultPT = mysqli_query($db,$queryPT);
			while($rowPT = mysqli_fetch_array($oResultPT,MYSQLI_ASSOC))
			{
				$avgPT = $rowPT["deliveryTime"];
			}
			
			if($countOrderIDs > 0)
			{		
			//$avgPT = 14; //8; //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))
			$earlyOrderMsgStd = "<br><b Style=\"font-size:10px;color:#AE0011;\">Early Qty Std = " . round($eoQtyFeet,0) . "</b>";
			}
		/********PT - End*************/
		}
		
		$resultDaysStd = mysqli_query($db,$queryDays);
			$countDaysStd = 0;
			$zeroZone = 1;
			$dateOnCheckZero = 0;
			$dateCheckPre = 0;
			$timeNow = time();
			$k = 0;
			$j = 0;
			$timeYear =  time() - (52 * 7 * 24 * 60 * 60);  //1 year back
			$bucketList = array();
			$timeLineStd = array();
			$startTime = 0;
			$endTime = 0;
			
		//SETS START TIME ONE YEAR BACK...
		$startTime = $timeYear;
		unset($daysInStock);
		$daysInStock = 	array();
		$resultDaysStd = mysqli_query($db,$queryDays);
		while($rowDays = mysqli_fetch_array($resultDaysStd, MYSQLI_ASSOC))
		{
					$qty_timeline = $rowDays["qty_on_hand"];
					$dateOnCheck = $rowDays["dateChange"]; //get it to 6:00AM from 15:59:04
					
					if($k > 0)
					if($dateOnCheck<=$daysInStock[$k-1]) continue;
					
					$dateCheck = date("Y-m-d",$dateOnCheck);
					$dateOnCheck = strtotime($dateCheck);
			//echo "time stamp: " . $dateOnCheck . " date real : " . $dateCheck . ": Qty = " . $qty_timeline . "<br>";
			if($qty_timeline > 0)
			{
				if($k < 1)
				{
				$startTime = $dateOnCheck;
				$daysInStock[$k] = $dateOnCheck;
				
				//echo "Start date: " . $dateCheck . ": Qty = " . $qty_timeline . "<br>";
				$k++;
				$dateOnCheckPre = $dateOnCheck;
				}
				else
				{
					if (in_array("$dateOnCheck",$daysInStock)) 
					{
					//echo "Date skipped from array: " . $dateCheck . ": Qty = " . $qty_timeline . "<br>";
					continue;
					}
					$daysToAdd = number_of_days($dateOnCheck,$dateOnCheckPre); 
					//echo "# Days to add: " . $daysToAdd . "<br>";
					if($daysToAdd < 1) $daysToAdd = 1;
					$i = 1;
					while($i <= $daysToAdd) //adds all of the days to the array of days
					{
						if($i == $daysToAdd)
						$daysInStock[$k] = $dateOnCheck;
						else
						$daysInStock[$k] = $dateOnCheckPre + (60*60*24) * $i;
					
					//echo "Date saved in array: " . date("Y-m-d",$daysInStock[$k]) . ": Qty = " . $qty_timeline . " day # " . $k . " timestamp: " . $daysInStock[$k] . "<br>";
					$i++;
					$k++;
					}
					
					$dateOnCheckPre = $dateOnCheck;
				}
			}
			else
			{
			$dateOnCheckPre = $dateOnCheck;
			//echo "Date skipped from array: " . $dateCheck . ": Qty = " . $qty_timeline . "<br>";
			}
		}
		
		/*foreach ($daysInStock as $value) {
				 echo "Timestamp in Stock: $value<br />\n";
			}*/
		
		$j = 0;
	if(isset($daysInStock[0])) // && isset($daysInStock[$avgPT - 1]))
	{
		//echo "days in stock value $avgPT - 1  : " . $daysInStock[$avgPT - 1] . "<br>";
		$startTime = $daysInStock[0];
		$endTime = $daysInStock[$avgPT - 1];
		while($endTime < $now)
		{
/*$queryBucket = "SELECT partno, qty_sold, dateChange FROM `parts_daily_timeline` WHERE partno LIKE '" . $frameid . "%' AND `dateChange` >= '$startTime' AND `dateChange` <= '$endTime' ORDER BY dateChange ASC;";
*/
$queryBucket = "SELECT partno, qty_sold, dateChange FROM `parts_daily_timeline` WHERE partno REGEXP '" . $stackFrame . "' AND `dateChange` >= '$startTime' AND `dateChange` <= '$endTime' ORDER BY dateChange ASC;";
						//echo $queryBucket . "<br>";
						$k = 0;
						$sum_sold = 0;
						$feetSoldStd = 0;
						unset($timeLineStd);
						$timeLineStd = array();
						//print_r(array_values($daysInStock));
						$resultBucket = mysqli_query($db, $queryBucket);
						while ($row = mysqli_fetch_array($resultBucket, MYSQLI_ASSOC))
						{
							$dateOnCheck = $row["dateChange"];
							$dateCheck = date("Y-m-d",$dateOnCheck);
							$dateOnCheck = strtotime($dateCheck);
							if (in_array("$dateOnCheck", $timeLineStd)) continue;
							if(!in_array("$dateOnCheck", $daysInStock)) continue;  //Amitai - remove days where not in stock
							
							$qty_sold = intval($row["qty_sold"]);
							$framepn = $row["partno"];
							//echo "Date with Qty: " . $dateCheck . " #" . $k . " Qty Sold = " . $qty_sold . "<br>";
							
                            $feetSoldStd = getSize($framepn);
                            $timeLineStd[$k] = $dateOnCheck;
							
							$sum_sold += $qty_sold * $feetSoldStd;
							$k++;				
						}
						//$totalDTs += $sum_sold; 						
						$bucketList[$j] = intval($sum_sold);
						
					//echo "<u>Bucket $j:</u> " . $bucketList[$j] . " Start: " . date("Y-m-d",$startTime) . " End: " . date("Y-m-d",$endTime) . "<br>";
						//echo "End qty to add : " . $endQty . " End time qty : " . date("Y-m-d",$rowEnd["dateChange"]) .  " End time loop: " . date("Y-m-d",$endTime) . "<br>";
						//echo "Start qty to remove: " . $startQty . " Start time loop: " . date("Y-m-d",$startTime) . "<br>";
						//$bucketList[$j] = $bucketList[$j-1] + $endQty - $startQty;
						
						//$startQty = $qtySoldOnCheck;
						//$startTime = $startTime + (60*60*24);
						//END TIME OF BUCKET sets end time
						//$endTime = $startTime + (60 * 60 * 24 * $avgPT );
						
						if($bucketList[$j] < 0) $bucketList[$j] = 0;
						$j++;
						
						$startTime = $daysInStock[$j];
						
						if(isset($daysInStock[$avgPT - 1 + $j]))
						$endTime = $daysInStock[$avgPT - 1 + $j];
						else break; //KILLS THE ARRAY!
		}
	}
	
		$std_deviation = sd($bucketList);
		$std_deviation = round($std_deviation,2);
		
		$eoQtyFeet = round($avgPT*$ARS,2); 
			/*if($units_sold >= 20)
			$z_code = 2.05;
			else if($units_sold >= 10)
			$z_code = 1.65;
			else if($units_sold >= 3)
			$z_code = 1;
			else*/
			$z_code = 1.282; //80% confidence
			$confidence = round($z_code*$std_deviation,2);
			
			$total_bin_std = round($eoQtyFeet + $confidence,2);
			
			$month_of_sales = round(($ARS) * 30);
		//echo "month_of_sales = " . $month_of_sales . "<br>";
		//$qty_reserved_on_events = $totalUnitsOnEvent/2;
		
		if ($month_of_sales > $totalfeetReservedOnEvent)
		$qty_needed = $month_of_sales;
		else
		$qty_needed = $totalfeetReservedOnEvent;
		
 		if(empty(trim($factor))) {
			$flag = "#000000";
	 	}
 		else{
 			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";*/
$feetsoldpermonth = round($ARS * 30,2);  //feet sold per month...
$feetsoldpertwoweeks = round($ARS * $avgPT,2);  //feet sold per month...
$totalFeetReserved = $totalfeetBackorder + $totalfeetReservedOnEvent + $totalfeetAmazon + $feetsoldpertwoweeks;
//if feet sold per month greater than feet reserved on event...
if ($feetsoldpertwoweeks > $totalfeetReservedOnEvent) {
$totalFeetReserved = $feetsoldpertwoweeks + $totalfeetAmazon + $totalfeetBackorder;
}
else {
$totalFeetReserved = $totalfeetReservedOnEvent + $totalfeetAmazon + $totalfeetBackorder;
}
		$qty_in_bin = $feetonorder + $feetonhand;
		if( $qty_in_bin < $totalFeetReserved) //assuming 120 days supply
 		  $flag = "#1c9327; font-weight:bold;";
 		else $flag = "#000000";
		
		$recomended_qty = round(($totalFeetReserved - $qty_in_bin)/$feetinbox,2);
		
		if($feetonhand > 0)
		{
		$need_bin_std = $total_bin_std + $totalfeetAmazon + $totalfeetBackorder + $totalMyhabitFeetOnEvent - $qty_in_bin;
		$recomended_qty =  $qty_needed + $totalfeetAmazon + $totalfeetBackorder + $totalMyhabitFeetOnEvent - $qty_in_bin;
		}
		else
		{
		$need_bin_std = $total_bin_std + $totalfeetAmazon + $totalfeetBackorder + $totalMyhabitOnEvent - $feetonorder;
		$recomended_qty =  $qty_needed + $totalfeetAmazon + $totalfeetBackorder + $totalMyhabitOnEvent - $feetonorder;
		}
		
		$recomended_qty_box_std = round($recomended_qty/$feetinbox,2);
		$need_bin_std_box = round($need_bin_std/$feetinbox,2);
		
		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 = $ARS * 10 * 2; //$feetsoldperday Full Bin system
		$totalBin = max($fullBin,$totalfeetAmazon,$totalfeetReservedOnEvent) + $totalMyhabitFeetOnEvent + $totalfeetBackorder;
		$totalBin_std = $total_bin_std + $totalfeetAmazon + $totalfeetBackorder + $totalMyhabitFeetOnEvent;
		if ($qty_in_bin > 0.8*$totalBin_std) //green
		$binColor = "#2a9914";
		else if ($qty_in_bin > 0.5*$totalBin_std) //yellow
		$binColor = "#e58407";
		else if ($qty_in_bin <= 0.5*$totalBin_std) //red
		$binColor = "#db1334";
		
		//round(($ARS) * 45) - (($qty_available + $qty_remaining) - round($ARS * 45));
 		
		if ($recomended_qty > 0)
		$flag = "#1c9327; font-weight:bold;";
 		else $recomended_qty = 0; 
 		/*else if ($recomended_qty <= 3 && $recomended_qty >= 1)
 		{ $recomended_qty = $recomended_qty; }*/
 		
 		/*$queryTrans = "SELECT partid,closeout FROM parts WHERE partno ='" . $partid . "' LIMIT 1;";
 		$transResult = mysqli_query($db,$queryTrans);
 		$row = mysql_fetch_array($transResult, MYSQL_NUM);*/
		if($feetonhand > 0)
		{
		if (($feetonhand + $feetonorder) > 0.8*$totalBin_std) //green
		$binColor = "#2a9914";
		else if (($feetonhand + $feetonorder) > 0.6*$totalBin_std) //yellow
		$binColor = "#e58407";
		else if (($feetonhand + $feetonorder) <= 0.6*$totalBin_std) //red
		$binColor = "#db1334";
		}
		else $binColor = "#db1334";
		
		
 		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 LIKE '%" . $partid1 . "%' ORDER BY `id` DESC LIMIT 1";
 $resultM = mysqli_query($db,$sqlM);
 $date2 = time(); //time now
 $days3 = 3*24*60*60; //3 days
 // Now we are getting our results and making them an array
 /*******************************/
 if (mysqli_num_rows($resultM) > 0)
 {
	 while($r = mysqli_fetch_array($resultM, MYSQLI_ASSOC)) {
	 if(isset($r["posted"])){
	 $posted = $r["posted"]; //the date posted
	 }
	 else{
	 	$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;";
		  $borderOnHand = "background: none repeat scroll 0 0 rgba(0, 0, 102, 0.3);";
		}
		else
		{
		 $borderOnHand = "";
		}
	 // End of Array
	 }
 }
 $i = 0;
 //changed $qty_on_hand
 	if ($qty_available > 0)
 	{
 	 $borderOutofStock = "";
 	 $boldOutofStock = "#000000;";
 	}
 	else
 	{
 	//echo "less then 1" . ;
 	 //$borderOutofStock = "border: medium #ae0011 dashed;";
	 //$borderOutofStock = "border: medium #ae0011 dashed;";
	 $borderOutofStock = "background: none repeat scroll 0 0 rgba(174, 0, 17, 0.3);";
 	 $boldOutofStock = "#ae0011;";
 	}
 		
 	//TOTAL	QTY & COSTS
 	if ($qty_on_hand > 0)
 	{
 			$totalCostOnHand = $costOnHand * $qty_on_hand; 	
 			$totalCost += $totalCostOnHand;
 			$totalInv += $qty_on_hand;
 	}
 		$units_sold_period = 90 * $ARS;
	/*if(!isset($units_sold_period)){
		$units_sold_period = 0;
	}
    if(empty(trim($units_sold_period))){
		$units_sold_period = 0;
	}
	else{
		$units_sold_period = number_format($totalfeetsold/$factor,0,'.',''); //units sold in the period...
	}*/
 		
 	//******************* EARN & TURN  ********************************/
	if ($costOnHand > 0 && $avg_price > 0)
	{
	//echo "Avg Price: " . $avg_price . "<BR>" . "Cost on Hand per Ft: " . $costOnHandFeet . "<BR>";
 	$gross_margin = (($avg_price - $costOnHandFeet)/$avg_price)*100; //GROSS MARGIN
	//echo "Gross Margin: " . $gross_margin . "<BR>";
 	$cogs_sold = $costOnHandFeet*$totalfeetsold; //COST OF GOOD SOLD
	//echo "COGS: " . $cogs_sold . "<BR>";
 	$queryAvg = "SELECT AVG(qty_on_hand) AS avg_on_hand FROM `parts_timeline` WHERE `partid` = '" . $partid1 . "' AND dateChange > " . $timeYear . ";";
 	//echo $queryAvg . "<br>";
	$resultAvg = mysqli_query($db,$queryAvg);
    $rowAvg = mysqli_fetch_array($resultAvg, MYSQLI_ASSOC);
 	$on_hand_avg = $rowAvg["avg_on_hand"];
	$on_hand_avg_feet = $on_hand_avg*$feetinbox;
 	
	//echo "Avg on Hand feet: " . $on_hand_avg_feet . "<BR>";
	
		if($on_hand_avg > 0)
		{	
		$avg_inv_cost = $on_hand_avg_feet * $costOnHandFeet; //Average Inv Costs
		
		$earnNturn = $gross_margin * ($cogs_sold / $avg_inv_cost);
		}
		else
		{
		$onHandMessage = "NO AVERAGE ON HAND";
		}
	
	}
	else
	{
	$costMessage = "NO COST INFORMATION";
	}
 	//******************* 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 style="text-align: center;">
 <?php echo $countItems; //"<input type='checkbox' name='check" . $partid . "' value='" . $partid . "'>"; ?>
 </td> 
   <td style="background-color:<?php echo $border; ?>;<?= $borderOnHand; ?>;text-align: center;">
   
   <? echo "<img src=\"files/thumb/" . $artimage  . "\"><br><a target=_blank href=\"viewparts.php?partid=$partid1\">" . $frameid . "<br>" . $partid . "</a>" . $closeoutdisplay . $stackFrameText; ?>
   </td>
    <td><? echo $name; //"<a href=\"#\" onMouseOver=\"ShowPicture('PicStyle" . $countItems . "',1)\" onMouseOut=\"ShowPicture('PicStyle" . $countItems . "',0)\">" . $name . "</a>"; 
	if(empty(trim($last0))) {
		$date_last0 = '';
	}
	else{
		$date_last0 = date("m.d.y",$last0);	
	}
	if(empty(trim($lastInc))) {
		$date_lastInc = '';
	}
	else{
		$date_lastInc = date("m.d.y",$lastInc);	
	}
	if(!isset($qtyInc)){
		$qtyInc = '';
	}
  /*  if ($qty_on_hand < 1)
 	{
   echo "<br><span style=\"font-size:10px;font-weight:bold;\">" . "Qty 0 on: " . $date_last0 . "<br>Qty " . $qtyInc . " on " . $date_lastInc . "<br>Days until out: " . $real_count_days . "</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>';
  }
  
  echo "<br><br><b style=\"color:#293b74\">Supplier: $partSupplier</b>";
    if($outofstock == '1')
  {
		echo "<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><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 style="text-align: center;"><? echo "<a href=\"javascript:GlobalPopUp('monthly-current-pn.php?partno=$partid&total=$units_sold',800,200);\">" . $units_sold_period . " Ft. per Qtr</a>"; ?></td>
  <td style="text-align: center;">
  <? echo "$" . $totalsales ; ?> 
  <br>
  <? echo "Total Units: " . $totalunits; ?>
  <br>
  <? echo "Total Feet Sold: " . $totalfeetsold; ?>
  <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;">Earn & 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 style="<?= $borderOutofStock; ?>;font-size:10px;"><b style="color: <?php echo $boldOutofStock; ?> ">
  <? echo $qty_on_hand ; 
  echo "<br><br>" . $feetinbox . " Ft. in box";
  echo "<br>" . $feetonhand . " Ft. on hand";
  ?>
  <?php 
  /*if($qty_on_hand > $qty_available)
  echo "<br>Avail. Qty: " . $qty_available;*/
  ?>
  </b></td>
   <td style="font-size:10px;"><? echo "<b>" . $qty_remaining . "</b>"; //echo " " . $qty_remaining . " = " . $qty_on_order . " - " . $qty_received; 
   
     echo "<br><br>" . $feetonorder . " Ft. on order";
		
   ?></td>
   <td><ul style="font-size:11px;list-style-type:none;padding:0; margin:3;"><? 
   echo  "<li>". $totalfeetBackorder . " Ft. on Backorder </li>"; 
   echo "<li><a href=\"eventItems.php?partnosearch=" . $frameid . "\" target=\"_blank\">" . $totalfeetOnEvent . " Ft. on Events </a></li>";
  /* echo "<li>" . $totalfeetAmazon . " Ft. on Amazon </li>"; 
   echo "<li>" . $totalMyhabitFeetOnEvent . " Ft. on MyHabit </li>"; */
   //$future_event; //$countDays;  ?>
   </ul></td>
  <td><ul style="font-size:11px;list-style-type:none;padding:0; margin:3;">
   <? 
  // echo  "<li>Feet per Month: " . $feetsoldpermonth . "</li>"; //feet sold in 1 month on average
    echo  "<li>Feet per " . $avgPT . " days: " . $feetsoldpertwoweeks . "</li>"; //feet sold in 1 month on average
   echo "<li>Days in Stock: " . $countDays . "</li>";
   echo "<li>Feet per Day: " . $ARS . "</li>";
   echo "<li>Feet needed: " . number_format($totalFeetReserved,2,'.',',');
  echo "<br>Qty in Bin: " . $qty_in_bin . "</li>";
  echo "</ul>";
  
 
   ?>
    
   </td>  
   <td><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.: " .  $totalfeetAmazon . "</li>";*/
    echo "<li>Reserved for Events: " .  $totalfeetReservedOnEvent . "</li>";
   /*echo "<li>MyHabit: " . $totalMyhabitFeetOnEvent . "</li>";*/
    echo "<li>Total Bin: " . $totalBin_std . "</li>";
 if($totalBin > $qty_in_bin)
  {
	  $needed_bin_qty = round(($totalBin - $qty_in_bin)/$feetinbox,2);
  echo "<li><u>Qty to complete Bin : " . $needed_bin_qty . "</u></li>";
  }
  
    echo "<li>Std. Dev.: " . $std_deviation . "</li>";
	 echo "<li>Confidence: " . $confidence . "</li>";
    //echo "<li>Total Bin: " . $totalBin . "</li>";
	echo "<li>Total Bin Std: " . $total_bin_std . "</li>";
   /*echo  number_format($ARS,2,'.',','); 
    if ($qty_on_hand < 1)
 	{
   //echo "<br><span style=\"font-size:10px;font-weight:bold;\">Real ARS: " . number_format($qtyInc/$real_count_days,2,'.',',') . "</span>";
   }*/
   echo "</ul>";
    echo "<br><b><u>Qty to complete Bin Std : " .  $need_bin_std . "</u></b>";
   ?></td>
<!--   <td width="60"><b><? echo  $days_until_out; ?></b></td> -->
   <td style="text-align: center;"><b><? echo  $days_with_on_order; ?></b></td>
   <td style="text-align: center;"><b><? echo  "Feet: " . $need_bin_std; echo "<br>Box: " . $need_bin_std_box; ?></b></td>
  <td style="text-align: center;"><?php echo "<input type='text' size='3' name='qty" . $partid . "' value='0'>"; ?></td>
 </tr>
 <?php 
 $countItems++;
 }
 ?>
 </table>  
 <script language="Javascript" src="cal/calendar.js"></script>
 </div>
 </div>
     </td>
   </tr>
   </table>
   
<table cellspacing="0" cellpadding="1" border="1" width="100%">
  <tr valign="top">
 	  <td valign="top">
 	<table cellspacing="0" cellpadding="1" border="1" width="100%">
 		<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>
 		</tr>
 	</table>
</td></tr></table>
<table cellspacing="0" cellpadding="1" border="1" width="100%">
 <tr valign="top">
     <td valign="top">
 	<table cellspacing="0" cellpadding="1" border="1" width="100%">
 <tr>
 <td class=menu>Order Number:<br> <input size="10" type="Text" name="ordernumber"></td>
 <td class=menu>Organization: <br>
 <?php 
 $result1 = mysqli_query ($db,"SELECT `organization` FROM `organization` WHERE 1=1");
 echo "<select name=\"organization\">\r";
 if ($row = mysqli_fetch_array($result1, MYSQLI_ASSOC)) {
   do {
	   if ($row["organization"] == $showSupplier)
		   $selected = "selected=\"selected\"";
		   else $selected = "";
   print "<option $selected value=\"" . $row["organization"]."\">" . $row["organization"]."\r";
	 }
	 while($row = mysqli_fetch_array($result1, MYSQLI_ASSOC));
}
?>
</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 = mysqli_query ($db,"SELECT `status` FROM `status` WHERE 1=1");
  echo "<select name=\"status\">\r";
  if ($row = mysqli_fetch_array($result1, MYSQLI_ASSOC)) {
    do {
     $selected = "" ;
      if ($row["status"] == "Review")
 		  $selected="selected";
    print "<option $selected value=\"" . $row["status"]."\">" . $row["status"]."\r";
 	 } while($row = mysqli_fetch_array($result1, MYSQLI_ASSOC));
 }
 ?>
 </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 class="oo_submit" onClick="return confirm('Are you sure you want to create this order?')" type='submit' NAME='createOrder' value='Create Order' ></td>
  </tr>
 </table>
</td></tr></table>
 </form>
</div>
 <center><br><br>
 <?php if($moldings_to_make != "" ) echo "<b style=\"color:#ae0011;\">Mouldings Missing:</b> <br> " . $moldings_to_make;?>
 <button class="oo_submit" style="margin-top:10px;" onClick="window.location='index.php'">Back to Main Menu</button> 
 <? include ("footer.inc"); 
 
  // Function to calculate square of value - mean
function sd_square($x, $mean) { return pow($x - $mean,2); }
 // Function to calculate standard deviation (uses sd_square)    
function sd($array) {
// square root of sum of squares devided by N-1
	if (count($array)>1)
		return sqrt(array_sum(array_map("sd_square", $array, array_fill(0,count($array), (array_sum($array) / count($array)) ) ) ) / (count($array)-1) );
	else return 0;
}
function getSize($framepn)
 {
     if(stripos($framepn,"20x24"))
     {
         $width = 20;
         $height = 24;
     }
     else if(stripos($framepn,"8x10"))
     {
         $width = 8;
         $height = 10;
     }
     else if(stripos($framepn,"16x20"))
     {
         $width = 16;
         $height = 20;
     }
     else if(stripos($framepn,"24x24"))
     {
         $width = 24;
         $height = 24;
     }
     else if(stripos($framepn,"24x36"))
     {
         $width = 24;
         $height = 36;
     }
     else if(stripos($framepn,"30x40"))
     {
         $width = 30;
         $height = 40;
     }
     else if(stripos($framepn,"36x48"))
     {
         $width = 36;
         $height = 48;
     }
     return round((($width*2 + $height*2)*1.2)/12,2);
 }
// echo '<br>Total execution time in seconds: ' . (microtime(true) - $time_start);
 ?>
 