<?php
include("../connect.php");
global $con;
include("../session.php");
//var_dump($_SESSION);
 ?>

 <!DOCTYPE html>
 <html lang="en">
   <head>
     <meta charset="utf-8">
     <meta http-equiv="X-UA-Compatible" content="IE=edge">
     <!--<meta name="viewport" content="width=device-width, initial-scale=1">-->
     <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no">
     <title><?= $config['CompanyName'] ?></title>

     <!-- Icons -->
     <link rel="shortcut icon" href="../img/favicon.ico" type="image/x-icon" />
     <link rel="apple-touch-icon" href="../img/apple-touch-icon.png" />
     <link rel="apple-touch-icon" sizes="57x57" href="../img/apple-touch-icon-57x57.png" />
     <link rel="apple-touch-icon" sizes="72x72" href="../img/apple-touch-icon-72x72.png" />
     <link rel="apple-touch-icon" sizes="76x76" href="../img/apple-touch-icon-76x76.png" />
     <link rel="apple-touch-icon" sizes="114x114" href="../img/apple-touch-icon-114x114.png" />
     <link rel="apple-touch-icon" sizes="120x120" href="../img/apple-touch-icon-120x120.png" />
     <link rel="apple-touch-icon" sizes="144x144" href="../img/apple-touch-icon-144x144.png" />
     <link rel="apple-touch-icon" sizes="152x152" href="../img/apple-touch-icon-152x152.png" />


     <!-- Bootstrap -->
     <link href="../css/bootstrap.min.css" rel="stylesheet">
     <link rel="stylesheet" href="../css/bootstrap-combobox.css" />

     <!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
     <!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
     <!--[if lt IE 9]>
       <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
       <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
       <script src="../js/functions.js"></script>
       <!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->

           <!--<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>-->
					 <script src="https://code.jquery.com/jquery-2.2.4.min.js"></script>

					 <script src="../js/functions.js"></script>
           <!-- Include all compiled plugins (below), or include individual files as needed -->
           <script src="../js/bootstrap.min.js"></script>
       	 <script src="../js/bootstrap-combobox.js"></script>
           <script type="text/javascript">
             $(document).ready(function(){

             });
           </script>

     <![endif]-->
	<link rel="stylesheet" href="../css/styles.css"/>
   </head>
   <body>
<?php
	include("navbar.php");
?>

   <!-- Stack the columns on mobile by making one full-width and the other half-width -->
     <div class="container " style="padding-top: 15px;">

     <div style="border: solid 3px; border-color:  #00408d; display: none;" id="salesDashboard" class="jumbotron">
			<div class="row">
       <h3>
       <?php
       		date_default_timezone_set("America/Puerto_Rico");
       		$hour = intval(date("G"));
       		$dias = array("domingo", "lunes", "martes", "miercoles", "jueves", "viernes", "sabado");
       		$meses = array("", "enero", "febrero", "marzo", "abril", "mayo", "junio", "julio", "agosto", "septiembre", "octubre", "noviembre", "diciembre");
       		if ($hour >= 4 && $hour < 12) {
       			$greeting = "Buenos dias ";
       		}elseif ($hour >= 12 && $hour < 19) {
       			$greeting = "Buenas tardes ";
       		}elseif ($hour >= 19 || $hour < 4) {
       			$greeting = "Buenas noches ";
       		}
       		$name = $admin->Name;
			$name = explode(" ", $name);
			$name = $name[0];
       		echo($greeting . $name . ".");

        ?>
       </h3>
			</div>
			<div class="row">

				<?php
				if($_SESSION['Username'] == "jcorrea" || $_SESSION['Username'] == "elisamuel40" || $_SESSION['Username'] == "ccordova"){
				?>

				<div class="row">
					<div class="col-sm-6 col-md-4">
						<div class="thumbnail">

							<div class="caption">
								<h3>This month sales:</h3>
								<h4>
								<?php
								$month = date("Y-m-01", time());
								$query = "SELECT SUM(Subtotal) TotalSales FROM INVOICE_HEAD WHERE DATE(InvoiceDate) BETWEEN '$month' AND NOW() AND `Status` = 1;";
								$queryLastYearProgressive = "SELECT SUM(Subtotal) TotalSales FROM INVOICE_HEAD WHERE DATE(InvoiceDate) BETWEEN DATE_ADD(DATE('$month'), INTERVAL -1 YEAR) AND DATE_ADD(NOW(), INTERVAL -1 YEAR) AND `Status` = 1;";
								$queryLastYearMonth = "SELECT SUM(Subtotal) TotalSales FROM INVOICE_HEAD WHERE MONTH(InvoiceDate) = MONTH('$month') AND YEAR(InvoiceDate) = YEAR('$month')-1 AND `Status` = 1;";
								$result = mysqli_query($con, $query);

								if(!$result){
									echo mysqli_error($con);
								}else{
									//var_dump($result);
								}
								$resultLastYearProgressive = mysqli_query($con, $queryLastYearProgressive);
								if(!$resultLastYearProgressive){
									echo mysqli_error($con);
								}else{
									//var_dump($resultLastYearProgressive);
								}
								$resultLastYearMonth = mysqli_query($con, $queryLastYearMonth);
								if(!$resultLastYearMonth){
									echo mysqli_error($con);
								}else{
									//var_dump($resultLastYearMonth);
								}
								$row = mysqli_fetch_array($result);
								$rowLastYearProgressive = mysqli_fetch_array($resultLastYearProgressive);
								$rowLastYearMonth = mysqli_fetch_array($resultLastYearMonth);
								echo moneyFormat($row['TotalSales']);

								if($rowLastYearProgressive['TotalSales'] === 0 || is_null($rowLastYearProgressive['TotalSales'])){
									$differencePercent = 100;
								}else{
									$differencePercent = ($row['TotalSales']/$rowLastYearProgressive['TotalSales']-1)*100;
								}
								echo " (" . ($differencePercent>=0?⬆️:⬇️) . ")";



								?>
								</h4>
								<h4>
									Last Year:
									<?php
									echo	moneyFormat($rowLastYearMonth['TotalSales']);
									?>
								</h4>
							</div>
						</div>
					</div>

					<div class="col-sm-6 col-md-4">
						<div class="thumbnail">

							<div class="caption">
								<h3>This month payments:</h3>
								<h4>
								<?php
								$month = date("Y-m-01", time());
								$query = "SELECT SUM(IF(PartialPayment,PaymentAmount, SubtotalAmount)) TotalPayment FROM PAYMENT_HEAD JOIN PAYMENT_DETAIL ON(PAYMENT_HEAD.ID = PAYMENT_DETAIL.PaymentID) WHERE DATE(PaymentDate) BETWEEN '$month' AND NOW() AND CPDDate <= NOW();";
								//$queryLastYear = "SELECT SUM(IF(PartialPayment,PaymentAmount, SubtotalAmount)) TotalPayment FROM PAYMENT_HEAD JOIN PAYMENT_DETAIL ON(PAYMENT_HEAD.ID = PAYMENT_DETAIL.PaymentID) WHERE DATE(PaymentDate) BETWEEN DATE_ADD('$month', INTERVAL -1 YEAR) AND DATE_ADD(NOW(), INTERVAL -1 YEAR) AND CPDDate <= DATE_ADD(NOW(), INTERVAL -1 YEAR);";
								//echo $queryLastYear;
								$queryLastYearProgressive = "SELECT SUM(IF(PartialPayment,PaymentAmount, SubtotalAmount)) TotalPayment FROM PAYMENT_HEAD JOIN PAYMENT_DETAIL ON(PAYMENT_HEAD.ID = PAYMENT_DETAIL.PaymentID) WHERE DATE(PaymentDate) BETWEEN DATE_ADD(DATE('$month'), INTERVAL -1 YEAR) AND DATE_ADD(NOW(), INTERVAL -1 YEAR) AND `Status` = 1;";
								$queryLastYearMonth = "SELECT  SUM(IF(PartialPayment,PaymentAmount, SubtotalAmount)) TotalPayment FROM PAYMENT_HEAD JOIN PAYMENT_DETAIL ON(PAYMENT_HEAD.ID = PAYMENT_DETAIL.PaymentID) WHERE MONTH(PaymentDate) = MONTH('$month') AND YEAR(PaymentDate) = YEAR('$month')-1;";
								$result = mysqli_query($con, $query);
								$resultLastYearProgressive = mysqli_query($con, $queryLastYearProgressive);
								$resultLastYearMonth = mysqli_query($con, $queryLastYearMonth);
								$row = mysqli_fetch_array($result);
								$rowLastYearProgressive = mysqli_fetch_array($resultLastYearProgressive);
								$rowLastYearMonth = mysqli_fetch_array($resultLastYearMonth);


								echo moneyFormat($row['TotalPayment']);
								if($rowLastYearProgressive['TotalPayment'] === 0 || is_null($rowLastYearProgressive['TotalPayment'])){
									$differencePercent = 100;
								}else{
									$differencePercent = ($row['TotalPayment']/$rowLastYearProgressive['TotalPayment']-1)*100;
								}
								echo " (" . $differencePercent . "% " . ($differencePercent>=0?"⬆️":"⬇️") . ")";


								?>
								</h4>
								<h4>
									Last Year:
									<?php
									echo	moneyFormat($rowLastYearMonth['TotalPayment']);
									?>
								</h4>
							</div>
						</div>
					</div>
					<div class="col-sm-6 col-md-4">
						<div class="thumbnail">
							<?php
								$month = date("Y-m-01", time());
								$query = "SELECT SUM(IF(AccountNumber LIKE '00000%', 0,1)) Converted, SUM(1) TotalEstimates FROM ESTIMATE_HEAD JOIN ESTIMATE_CUSTOMER USING(EstimateID) WHERE DATE(EstimateDate) BETWEEN '$month' AND NOW() AND DeleteEstimate = 0;";

								$result = mysqli_query($con, $query);
								$row = mysqli_fetch_array($result);


								?>
							<div class="caption">
								<h3>This month estimates: <?= ($row['TotalEstimates']) ?></h3>
								<h4>
								<b>Converted:</b> <?= $row['Converted'] ?><br>
								<b>Conversion Rate:</b> <?= $row['TotalEstimates']?round($row['Converted']/$row['TotalEstimates']*100,2):0 ?>%
								</h4>

							</div>
						</div>
					</div>
				</div>
				<div class="row">
					<div class="col-sm-6 col-md-4">
						<div class="thumbnail">

							<div class="caption">
								<h3>This year sales:</h3>
								<h4>
								<?php
								$month = date("Y-01-01", time());
								$query = "SELECT SUM(Subtotal) TotalSales FROM INVOICE_HEAD WHERE DATE(InvoiceDate) BETWEEN '$month' AND NOW() AND `Status` = 1 AND Void = 0;";

								$result = mysqli_query($con, $query);
								$row = mysqli_fetch_array($result);
								echo moneyFormat($row['TotalSales']);

								?>
								</h4>
								<h4>
									Last Year Sales:
									<?php
									$query = "SELECT SUM(Subtotal) TotalSales FROM INVOICE_HEAD WHERE YEAR(InvoiceDate) LIKE YEAR(DATE_ADD(NOW(), INTERVAL -1 YEAR)) AND `Status` = 1 AND Void = 0;";
									$result = mysqli_query($con, $query);
									$row = mysqli_fetch_array($result);
									echo moneyFormat($row['TotalSales']);
								//	echo	moneyFormat("");
									?>
								</h4>

							</div>
						</div>
					</div>

					<div class="col-sm-6 col-md-4">
						<div class="thumbnail">

							<div class="caption">
								<h3>This year payments:</h3>
								<h4>
								<?php
								$month = date("Y-01-01", time());
								$query = "SELECT SUM(IF(PartialPayment,PaymentAmount, SubtotalAmount)) TotalPayment FROM PAYMENT_HEAD JOIN PAYMENT_DETAIL ON(PAYMENT_HEAD.ID = PAYMENT_DETAIL.PaymentID) WHERE DATE(PaymentDate) BETWEEN '$month' AND NOW() AND CPDDate <= NOW();";
								//echo $query;
								$result = mysqli_query($con, $query);
								$row = mysqli_fetch_array($result);
								echo moneyFormat($row['TotalPayment']);

								?>
								</h4>
								<h4>
									Last Year Payments:
									<?php
									$query = "SELECT SUM(IF(PartialPayment,PaymentAmount, SubtotalAmount)) TotalPayment FROM PAYMENT_HEAD JOIN PAYMENT_DETAIL ON(PAYMENT_HEAD.ID = PAYMENT_DETAIL.PaymentID) WHERE YEAR(PaymentDate) LIKE YEAR(DATE_ADD(NOW(), INTERVAL -1 YEAR)) AND Void = 0 AND CPDDate <= NOW();";
									$result = mysqli_query($con, $query);
									$row = mysqli_fetch_array($result);
									echo moneyFormat($row['TotalPayment']);
								//	echo	moneyFormat("");
									?>
								</h4>
							</div>
						</div>
					</div>
					<div class="col-sm-6 col-md-4">
						<div class="thumbnail">
							<?php
								$month = date("Y-01-01", time());
								$query = "SELECT SUM(IF(AccountNumber LIKE '00000%', 0,1)) Converted, SUM(1) TotalEstimates FROM ESTIMATE_HEAD JOIN ESTIMATE_CUSTOMER USING(EstimateID) WHERE DATE(EstimateDate) BETWEEN '$month' AND NOW() AND DeleteEstimate = 0;";

								$result = mysqli_query($con, $query);
								$row = mysqli_fetch_array($result);


								?>
							<div class="caption">
								<h3>This year estimates: <?= ($row['TotalEstimates']) ?></h3>
								<h4>
								<b>Converted:</b> <?= $row['Converted'] ?><br>
								<b>Conversion Rate:</b> <?= $row['TotalEstimates']?round($row['Converted']/$row['TotalEstimates']*100,2):0 ?>%
								</h4>

							</div>
						</div>
					</div>
				</div>

				<!--
				<div class="col-md-4">
					<div class="panel-group" role="tablist">
						<div class="panel panel-default">
							<div class="panel-heading" role="tab" id="collapseListGroupHeading1">
								<h4 class="panel-title">
									<a href="#collapseListGroup1" class="collapsed" role="button" data-toggle="collapse" aria-expanded="false" aria-controls="collapseListGroup1"> Revenue & GP By Month </a>
								</h4>
							</div>
							<div class="panel-collapse collapse" role="tabpanel" id="collapseListGroup1" aria-labelledby="collapseListGroupHeading1" aria-expanded="false" style="">
								<ul class="list-group">
									<li class="list-group-item">
										<table class="table table-condensed">
											<thead>
												<tr>
													<th>Month</th>
													<th style="text-align: right;">Revenue</th>
													<th style="text-align: right;">Gross Profit</th>
												</tr>
											</thead>
											<tbody>


										<?php
											$months = ["","Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
											$query = "SELECT * FROM RevenueAndProfitByMonth ORDER BY `Year`, `Month` ASC LIMIT 14";
											$result = mysqli_query($con, $query);
											$totalGP = 0;
											$totalRevenue = 0;
											while($row = mysqli_fetch_array($result)){
												$totalRevenue += $row['TotalPrice'];
												$totalGP += $row['TotalProfit'];

										?>
											<tr>
												<td><?= $months[$row['Month']] . " " . $row['Year'] ?></td>
												<td style="text-align: right;"><?= moneyFormat($row['TotalPrice']) ?></td>
												<td style="text-align: right;"><?= moneyFormat($row['TotalProfit']) ?></td>
											</tr>
										<?php
											}
										?>
											<tr>
												<td>Totals</td>
												<td style="text-align: right;"><b><?= moneyFormat($totalRevenue) ?></b></td>
												<td style="text-align: right;"><b><?= moneyFormat($totalGP) ?></b></td>
											</tr>
											</tbody>
										</table>
									</li>

								</ul>
								<div class="panel-footer" >
									<span></span>
								</div>
							</div>
						</div>
					</div>
				</div>
				<?php
				}
				?>
				-->
				<div class="col-md-4">

				</div>
				<div class="col-md-4">

				</div>
			</div>

     </div>
     <div class="row">
			<?php
				$quickLinksBadges = [];

				//Get Orders In Queue
				$query = "SELECT ORDER_HEAD.OrderID FROM ORDER_HEAD, CUSTOMER WHERE ORDER_HEAD.AccountNumber = CUSTOMER.AccountNumber AND Processed = FALSE AND (ProcessUser LIKE '' OR ProcessUser LIKE '" . $_SESSION['Username'] . "') AND DeleteOrder != 1";
				$result = mysqli_query($con, $query);
				//echo $query;
				$quickLinksBadges['OrdersInQueue'] = mysqli_num_rows($result);

				//Get Today's Estimates
				$query = "SELECT EstimateID FROM ESTIMATE_HEAD WHERE Processed = 0 AND Archive = 0 AND DeleteEstimate = 0 ORDER BY EstimateDate";
				$result = mysqli_query($con, $query);
				$quickLinksBadges['EstimatesInQueue'] = mysqli_num_rows($result);

				//Get Invoices In Queue
				$query = "SELECT InvoiceID FROM INVOICE_HEAD WHERE STATUS = FALSE AND Void = 0";
				$result = mysqli_query($con, $query);
				$quickLinksBadges['InvoicesInQueue'] = mysqli_num_rows($result);

				//Get Payments Cashbox
				$query = "SELECT COUNT(PAYMENT_HEAD.ID) Payments FROM PAYMENT_HEAD LEFT JOIN SALESMAN ON(SalesmanID = SALESMAN.ID) WHERE PAYMENT_HEAD.ID NOT IN (SELECT PaymentID ID FROM PAYMENT_SALESMAN_RECONCILE_DETAIL) AND (PAYMENT_HEAD.ID NOT IN (SELECT PaymentID ID FROM PAYMENT_REMINDER) OR Processed = 0) AND PaymentDate > '2019-01-01' and Void = 0";
				$result = mysqli_query($con, $query);
				$row = mysqli_fetch_array($result);
				$quickLinksBadges['PaymentsInQueue'] = $row['Payments'];

				//Get Orders to Receive
				$query = "SELECT PurchaseOrderID FROM PURCHASE_ORDER_HEADER WHERE Completed = False AND Received = False AND Confirmed = True";
				$result = mysqli_query($con, $query);
				$quickLinksBadges['ReadyToReceive'] = mysqli_num_rows($result);

				//Get Pending Tickets
				$query = "SELECT TicketID FROM TICKET_HEAD WHERE (`Status` != 2 && `Status` != -1) ORDER BY `Priority` ASC, `Status` ASC";
				$result = mysqli_query($con, $query);
				$quickLinksBadges['PendingTickets'] = mysqli_num_rows($result);
        $index = 0;

				$query = "SELECT * FROM ADMIN_SHORTCUT WHERE `Username` LIKE '" .  $admin->Username . "' ORDER BY `Order`";
				$result = mysqli_query($con, $query);
        //var_dump($query);
				if(mysqli_num_rows($result)){


			?>
				<h4>Quick Links</h4>
			<?php
				}
        $index = 0;
				while($row = mysqli_fetch_array($result)){
          //var_dump(($index)%2 == 3);
          if( ($index)%4 == 0 && $index!=0 ){
            echo '</div><div class="row">';
          }
          $index++;
					@($badge = "  " . ($quickLinksBadges[$row['Badge']])?("<span style=\"background-color: red; color: white;\" class=\"badge\">" . $quickLinksBadges[$row['Badge']] . "</span>"):"");

			?>
			<div class="col-md-3" style="margin-bottom: 10px;"><a class="btn btn-block btn-default" href="<?= $row['Link'] ?>"><?= $row['Title'] ?> <?= $badge ?></a></div>
			<?php
				}
			?>
			<div class="col-md-3 visible-xs" style="margin-bottom: 10px;"><a class="btn  btn-block btn-default" href="logout.php">Logout</a></div>

		</div>

     </div>
			<br>
		 <br>
		 <br>
		 <br>
		 <br>
		 <br>
		 <br>
		 	<style>
				.footer {
					position: fixed;
					bottom: 0;
					width: 100%;
					/* Set the fixed height of the footer here */
					height: 60px;
					background-color: #f5f5f5;

				}
		 </style>
     <footer class="footer hidden-xs">
      <div class="container">
        <p class="text-muted"><img src="../img/elitelogo.png" style="height: 50px; margin-top: 5px;"></p>
      </div>
    </footer>
   </body>
 </html>
