<?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";
//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.`pnchina` 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);
 }
 ?>
 <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.php?sortby=<?= $sortwith; ?>&triger1=<?= $avg_sale_tr; ?>&triger2=<?= $total_sale_tr; ?>&triger3=<?= $qty_sold_tr; ?>&factor=<?= $factor; ?>'">Download Excel</button>
 </tr>
 </table>
</td></tr></table>
 <form name="create_order" action="order-creator.php" method="post" onLoad> 
 <?php
 
 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 
// 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;
$costOnHandFeet = 0;
$units_sold = 0;
$sales = 0;
$qty_on_hand = 0;
$qty_available = 0;
$recomended_qty = 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"];
	$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-';";
 //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"];
			}
		}
	}
$future_event = "";
$totalfeetBackorder = 0;
$totalfeetOnEvent = 0;
$totalfeetReservedOnEvent = 0;
$totalfeetAmazon = 0;
$totalMyhabitFeetOnEvent = 0;
$fullBin = 0;
$totalBin = 0;
 mysql_select_db($dbname2,$db);
	$queryCount = "SELECT SUM(qty), `SKU` FROM `fullfilment` WHERE `SKU` LIKE '%" . $frameid . "%' AND `status` = 'Backorder' GROUP BY `SKU`;"; // GROUP BY `SKU`
	//echo $queryCount . "<br>";
								$resultCount = mysql_query($queryCount);
								while ($rowCount = mysql_fetch_array($resultCount))
								{
								//echo $queryCount;
								$frame_units = intval($rowCount[0]);
								$framepn =  $rowCount[1];
								
								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;
								}
								
								$feetperframe = round((($width*2 + $height*2)*1.2)/12,2);
								
								$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`;";
		//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 = mysql_query($queryCount);
							while ($rowCount = mysql_fetch_array($resultCount))
							{
								//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)
								{
									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;
									}
								
									$feetperframe = round((($width*2 + $height*2)*1.2)/12,2);
									//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>";*/
		
		mysql_select_db($dbname3,$db);
		
/*$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.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 avo.`fulfillmentCenter` <> 'CVG1' AND avo.`fulfillmentCenter` <> 'CVG2' GROUP BY avo.`primary`;"; 
 
								$resultCount = mysql_query($queryCount);
						while ($rowCount = mysql_fetch_array($resultCount))
						{
						//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);
								//$frame_units = $countAmazonOrder - $countAmazonRec;
								if ($frame_units > 0  && $shipDate < $onemonth)
								{
									//echo $queryCount;
									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;
									}
								
								$feetperframe = round((($width*2 + $height*2)*1.2)/12,2);
								//echo $queryCount;
								$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`;";
//echo $queryCount;
								$resultCount = mysql_query($queryCount);
						while ($rowCount = mysql_fetch_array($resultCount))
						{
						//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)
								{
									//echo $queryCount;
									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;
									}
								
								$feetperframe = round((($width*2 + $height*2)*1.2)/12,2);
								//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>";*/
 mysql_select_db($dbname,$db);
	
$queryFeetSold = "SELECT * FROM `peachtree` where `itemID` LIKE '%" . $frameid . "%' AND (SUBSTR(`itemID`,1,3) = 'FR-' || SUBSTR(`itemID`,1,2) = 'M-');";
 //echo $queryFeetSold . "<br>";
 $totalfeetsold = 0;
	$frame_units = 0;
	$frame_sales = 0;
	$feetsoldpermonth = 0;
	
 $feetResult = mysql_query($queryFeetSold);
 if (mysql_num_rows($feetResult) > 0)
	 {
		while($rowFeet = mysql_fetch_array($feetResult))
		{
			$framepn = $rowFeet["itemID"];
			$frame_units = intval($rowFeet["units_sold"]);
			$frame_sales = floatval($rowFeet["sales"]);
			
			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;
			}
			
			$feetperframe = round((($width*2 + $height*2)*1.2)/12,2);
			
			$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 = mysql_query($queryTrans);
if (mysql_num_rows($transResult2) > 0)
 {
 	$rowTrans = mysql_fetch_array($transResult2);
	//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 = 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];
				}
		  }
		
		$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 = mysql_query($query1,$db);
		while($rowOnOrder = mysql_fetch_array($result3))
		  {
				$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 LIKE '%" . $partid1 . "%' 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 partid LIKE '%" . $partid1 . "%' 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);
 }
 	
 	if($countDays > 0)
 	$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 ($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>";
 		}
		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...
$totalFeetReserved = $totalfeetBackorder + $totalfeetReservedOnEvent + $totalfeetAmazon + $feetsoldpermonth;
//if feet sold per month greater than feet reserved on event...
if ($feetsoldpermonth > $totalfeetReservedOnEvent) {
$totalFeetReserved = $feetsoldpermonth + $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);
		
		$fullBin = $ARS * 10 * 2; //$feetsoldperday Full Bin system
		$totalBin = max($fullBin,$totalfeetAmazon,$totalfeetReservedOnEvent) + $totalMyhabitFeetOnEvent + $totalfeetBackorder;
		
		if ($qty_in_bin > 0.8*$totalBin) //green
		$binColor = "#2a9914";
		else if ($qty_in_bin > 0.5*$totalBin) //yellow
		$binColor = "#f5f812";
		else if ($qty_in_bin <= 0.5*$totalBin) //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 = 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 LIKE '%" . $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;";
		  $borderOnHand = "background: none repeat scroll 0 0 rgba(0, 0, 102, 0.3);";
		}
		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;";
	 //$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;
 	}
 		
	if ( 1==1) //
	{
	$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 = mysql_query($queryAvg);
    $rowAvg = mysql_fetch_array($resultAvg);
 	$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 class="dynamic_res2" > 
<div style="width:85px;text-align: center;">
 <?php echo $countItems; //"<input type='checkbox' name='check" . $partid . "' value='" . $partid . "'>"; ?>
 </div> 
   <div style="background-color:<?php echo $border; ?>;<?= $borderOnHand; ?>;width:160px;text-align: center;">
   
   <? echo "<img src=\"files/thumb/" . $artimage  . "\"><br><a target=_blank href=\"viewparts.php?partid=$partid1\">" . $frameid . "<br>" . $partid . "</a>" . $closeoutdisplay; ?>
   </div>
    <div style="width:180px;"><? echo $name; //"<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>";
   }
   ?>
 <!--  <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>';
  }
 ?>
   </div>
  <!-- <td width="60"><? echo $current_monthly_sales ; ?></td>
     <td width="60"><? echo $next_monthly_sales ; ?></td> -->
   <div style="width:90px;text-align: center;"><? echo "<a href=\"javascript:GlobalPopUp('monthly-current-pn.php?partno=$partid&total=$units_sold',800,200);\">" . $units_sold_period . "</a>"; ?></div>
  <div style="width:92px;text-align: center;"><? echo "$" . $totalsales ; ?> <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></div>
  <div style="width:100px;<?= $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></div>
   <div style="width:92px;font-size:10px;"><? echo "<b>" . $qty_remaining . "</b>"; //echo " " . $qty_remaining . " = " . $qty_on_order . " - " . $qty_received; 
   
     echo "<br><br>" . $feetonorder . " Ft. on order";
		
   ?></div>
   <div style="width:100px;"><ul style="font-size:11px;list-style-type:none;padding:0; margin:3;"><? 
   echo  "<li>". $totalfeetBackorder . " Ft. on Backorder </li>"; 
   echo "<li>" . $totalfeetOnEvent . " Ft. on Events </li>";
   echo "<li>" . $totalfeetAmazon . " Ft. on Amazon </li>"; 
   echo "<li>" . $totalMyhabitFeetOnEvent . " Ft. on MyHabit </li>"; 
   //$future_event; //$countDays;  ?>
   </ul></div>
  <div style="width:92px;"><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 Day: " . $ARS . "</li>";
   echo "<li>Feet needed: " . number_format($totalFeetReserved,2,'.',',') . "</li>";
   
  echo "<br>Qty in Bin: " . $qty_in_bin;
  echo "</ul>";
  
 
   ?>
    
   </div>  
   <div style="width:90px;"><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 . "</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  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>";
   }*/
   ?></ul></div>
<!--   <td width="60"><b><? echo  $days_until_out; ?></b></td> -->
   <div style="width:80px;text-align: center;"><b><? echo  $days_with_on_order; ?></b></div>
   <div style="width:52px;text-align: center;"><b><? echo  $recomended_qty; ?></b></div>
  <div style="width:55px;text-align: center;"><?php echo "<input type='text' size='3' name='qty" . $partid . "' value='0'>"; ?></div>
</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="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 = mysql_query ("select * from organization");
 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>
 <!--<input type="hidden" name="status" value="Initiated" />-->
 <input type="hidden" name="stage" value="Details Received" />
 <input type="hidden" name="orderType" value="Regular" />
 <input 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>
 <center><br><br>
 <?php if($moldings_to_make != "" ) echo "<b style=\"color:#ae0011;\">Moldings Missing:</b> <br> " . $moldings_to_make;?>
 <button onClick="window.location='index.php'">Back to Main Menu</button> 
 <? include ("footer.inc"); 
 ?>
 