<?php
$tableName="Wines2";
    $con = mysqli_connect('localhost', 'mayabeac_wine', '3]eGo-r(Scru',"mayabeac_WineList");
        if (mysqli_connect_errno()) {
            echo"
                Failed to connect to the MySQL DB:".mysqli_connect_error();
            }
           else{


}

$countryCount=0;
$sql="SELECT DISTINCT WineCountry FROM ".$tableName." WHERE WineType != 'Beer' AND OutOfStock = 0 AND Archived = 0 AND LeaveOffMenu = 0 AND  ((QtyInStock > 0 AND WineType!='Glass' AND WineType!='Beer') OR (QtyInStock >= 0 AND SoldAs = 1 AND WineType2 = 6) OR (QtyInStock = -9999 And WineType2 = 6))";
$records = mysqli_query($con, $sql);
while($wine=mysqli_fetch_assoc($records)){
if($wine['WineCountry']!=""){
$countryCount=$countryCount+1;

}
//echo $wine['WineCountry']."<br>";
}

$sql="SELECT * FROM ".$tableName." WHERE WineType != 'Beer' AND OutOfStock = 0 AND Archived = 0 AND LeaveOffMenu = 0 AND ((QtyInStock > 0 AND SoldAs = 0) OR (QtyInStock >= 0 AND SoldAs = 1 AND WineType2 = 6) OR (QtyInStock = -9999 And WineType2 = 6) OR (QtyInStock > 0 AND WineProfile1 = 'Dessert Wines and Port'))";
$records = mysqli_query($con, $sql); 

$redCount = 0;
$whiteCount = 0;
$roseCount=0;
$sparklingCount=0;
$dessertCount=0;

while($wine=mysqli_fetch_assoc($records)){

if($wine['WineType']=='Red'){
$redCount++;
//echo $redCount." ".$wine['WineName']."<br>";
}
elseif($wine['WineType']=='White'){
$whiteCount++;
}
elseif($wine['WineType']=='Rose'){
$roseCount++;
}

elseif($wine['WineType']=='Sparkling'){
$sparklingCount++;
}

elseif($wine['WineType']=='Dessert'){
$dessertCount=$dessertCount+1;
}
elseif($wine['WineType']=='Port'){
$dessertCount=$dessertCount+1;
}

}

if($wine['WineProfile1']=='Dessert Wines and Port'){
$dessertCount=$dessertCount+1;
}

if($wine['WineProfile2']=='Dessert Wines and Port'){
$dessertCount=$dessertCount+1;
}



$wineTotal = $redCount+$whiteCount+$roseCount+$sparklingCount+$dessertCount;

$sql="SELECT DISTINCT WineCountry FROM ".$tableName." WHERE WineType <> 'Beer' AND OutOfStock = 0 AND Archived = 0 AND LeaveOffMenu = 0 AND ((QtyInStock > 0 AND SoldAs = 0) OR (QtyInStock >= 0 AND SoldAs = 1 AND WineType2 = 6) OR (QtyInStock = -9999 And WineType2 = 6)) ORDER BY WineCountry"; 


$records = mysqli_query($con, $sql); 

$linecount=1;

echo "<table style='width: 70%; margin-left: 15%; margin-right: 15%; font-size: 1.5em;'>";

   
echo "<colgroup>";
       echo "<col span='1' style='width: 20em;'>";
        echo "<col span='1' style='width: 2em;'>";
       echo "<col span='1' style='width: 20em;'>";
      echo "</colgroup>";

echo "<div style='text-align: center; font-size: 1.5em;'>Choose from our current selection of ".$wineTotal." wines from ".$countryCount." countries</div><br>";
echo "<div style='text-align: center; font-size: 1.25em;'><a href='/byprofile/?Listing=RedPrice'>".$redCount." Reds</a>, <a href='/byprofile/?Listing=WhitePrice'>".$whiteCount." Whites</a>, <a href='/byprofile/?Listing=Sparkling'>".$sparklingCount." Sparkling</a>, <a href='/byprofile/?Listing=Rose'>".$roseCount." Rosés</a> and <a href='m/byprofile/?Listing=Dessert Wines and Port'>".$dessertCount." Dessert Wines</a></div><br>";

while($wine=mysqli_fetch_assoc($records)){
if(!empty($wine['WineCountry'])){

    $countryCount = 0;
    $sql = "SELECT * FROM ".$tableName." WHERE OutOfStock = 0 AND Archived = 0 AND LeaveOffMenu = 0 AND WineType!='Beer' AND WineType!='Glass' AND ((QtyInStock > 0 AND SoldAs = 0) OR (QtyInStock >= 0 AND SoldAs = 1 AND WineType2 = 6)  OR (QtyInStock = -9999 And WineType2 = 6)) AND WineCountry = '".$wine['WineCountry']."'";

    $records1 =  mysqli_query($con, $sql);

    //echo $sql;

    while($wine2=mysqli_fetch_assoc($records1)){
        $countryCount = $countryCount + 1;
    }



if(1==1){
if(wonderplugin_is_device('iPhone,Android,iPad,Windows,Mac')){

  	if($linecount==1){
    	echo "<tr><td style='font-size=1.5em;  text-align: right;'><a href='/byCountry/?Listing=".$wine['WineCountry']."'>".$wine['WineCountry']." (".$countryCount.")</a></td><td></td>";  	
    }
	else{
	echo "<td style='font-size=1.5em; text-align: left;'><a href='/byCountry/?Listing=".$wine['WineCountry']."'>".$wine['WineCountry']." (".$countryCount.")</a></td></tr>";
  	}
  	if($linecount==1){
    	$linecount=2;
        }
  	else{
    	$linecount=1;
              
    }

}
else{

     echo "<tr><td style='font-size=1.5em; text-align: left;'><a href='/byCountry/?Listing=".$wine['WineCountry']."'>".$wine['WineCountry']." (".$countryCount.")</a></td></tr>";


      }

}
}
}
if($linecount==2){
  echo "<td></td></tr>"; 
}

echo "</table>";