<?php
	include("includes/dbconfig.php");
	$pagetitle = "Store Quantities By Category";
    require('includes/applicationtop2.php');
	 require('includes/applicationmenu2.php');
	
	$err= "";
	
	$itemscount = 0;
	$seles_details = '';
	
	function Shop_name($shop){
		$name = 'Store';
		$categorydata = DB::query("SELECT full_name FROM shop where id =%i" , $shop);
			if(DB::count() != 0){										
		       foreach ($categorydata as $category)
	              {
			 $name =  $category['full_name'];
			  
		          }
			}
			return $name;
	}
	
	function Item_name($item){
		$name = 'Select one';
		$categorydata = DB::query("SELECT item_name FROM stock_item where id =%i" , $item);
			if(DB::count() != 0){										
		       foreach ($categorydata as $category)
	              {
			 $name =  $category['item_name'];
			  
		          }
			}
			return $name;
	}
	
	function Personel($person){
		$name = 'N/A';
		$categorydata = DB::query("SELECT first_name FROM personnel_details where id =%i" , $person);
			if(DB::count() != 0){										
		       foreach ($categorydata as $category)
	              {
			 $name =  $category['first_name'];
			  
		          }
			}
			return $name;
	}
	
	function PriceId($item , $shop){
		$id = 0;
		$count =0;
		$categorydata = DB::query("SELECT id FROM item_price where item=%i AND shop =%i"  , $item , $shop);
			if(DB::count() != 0){										
		       foreach ($categorydata as $category)
	              {
					  if($count >0){
						  exit('More data foiund');
					  }
			          $id =  $category['id'];
					 
					  $count +=1;
					
			  
		          }
			}
			return $id;
	}
	
	//Wild Bill Hickok
	//Billy the Kid (1859-1881)
	//John Wesley Hardin (1853-1895)
	//Wyatt Earp (1848-1929)
	
	function PriceId2($item , $shop){
		$id = 0;
		$count =0;
		$item_sold_per_id = 0;
		$categorydata = DB::query("SELECT id FROM item_price where item=%i  and shop = %i "  , $item , $shop);
			if(DB::count() != 0){										
		       foreach ($categorydata as $category)
	              {
					  if($count > 0) exit('Error here');
					   
			          $id =  $category['id'];
					 
					 $count +=1;
				 
					
			  
		          }
			}
			return $id;
	}
	
	function Item_sold_with_id($price_id , $shop){
		$qty = 0;
		$categorydata = DB::query("SELECT sum(quantity) as qty  FROM cash_sale_item where price_id=%i  " , $price_id , $shop);
			if(DB::count() != 0){										
		       foreach ($categorydata as $category)
	              {
			 $qty =  $category['qty'];
			  
		          }
			}
			return $qty;
	}
	
	function Format_dates($source ){
		 
    return  date("Y-m-d", strtotime($source)); 
       
    
	}
	
	function stock_category($cat, $start, $end ,   $item , $shop){//get me the stock 
	     $stockitemsdata = false;
		 $item_details =  $item_dam = $item_fal =''; 
		 $item_details .= '<tr class="success" ><td><h3>Sales details </h3> </td><td> </td> <td>  </td> <td>   </td><td>   </td> <td>  </td><td>   </td> <td>  </td></tr>';
		   $item_details .= '<tr class="success" ><td>Item </td><td> Shop </td><td> Time sold </td>  <td>Receipt Id  </td><td>  Serial No </td> <td> #Sold </td><td> Damaged  </td> <td> Faulty  </td></tr>';
		   $item_sql = $cat_sql = '';
		   if($item > 0 ){
			   $item_sql = '  stock_item .id ='.$item.' AND ';
		   }
		    if($cat > 0 ){
			   $cat_sql = '   stock_item.category ='.$cat.' AND ';
		   }
		   
		  
		        //$stockitemsdata = DB::query('SELECT * FROM stock_item  WHERE  created_date >= STR_TO_DATE("'.$start.'", "%Y-%m-%d") AND created_date < STR_TO_DATE("'.$end.'", "%Y-%m-%d") + INTERVAL 1 DAY  ORDER BY id'); 
		  
		   
		   
		       $stockitemsdata = DB::query('SELECT * FROM stock_item  WHERE  '.$item_sql.'  '.$cat_sql.'  created_date >= STR_TO_DATE("'.$start.'", "%Y-%m-%d") AND created_date < STR_TO_DATE("'.$end.'", "%Y-%m-%d") + INTERVAL 1 DAY  ORDER BY id'); 
		   
		 $sold =0;$bal = $fal = $dam =0;
		 if($stockitemsdata ){
			if(DB::count() != 0){
				foreach ($stockitemsdata  as $category)
	              {
					  echo '<tr> <td >'. $category['item_name']. '</td><td  >'. $category['created_date'] .'</td> <td  >'. $category['item_code'] .'</td> <td  >'. $category['bar_code'] .'</td> <td>  </td><td>  </td><td>  </td><td>  </td> </tr>';
			      $id =  $category['id'];
				  $result =  stock_level($id , $start , $end ,$category['item_name'] , $shop );
				 $sold += $result[0];
				 $bal += $result[1];
				 $dam += $result[3];
				 $fal += $result[4];
				 $item_details .= $result[2];
				  $item_dam .= $result[5];
				  $item_fal .= $result[6];
				
					
					 
			  
		          }
				
			}
		 }
			
			echo  '<tr    ><td> </td><td> </td> <td>  </td> <td> <b>Grand Total </b> </td><td> <b>'.$bal.'</b>  </td> <td> <b>'.$sold.'</b> </td><td> <b>'.$dam.'</b> </td><td> <b>'.$fal.'</b> </td></tr>';
			echo   $item_details;
			echo  '<tr  class="success"><td> </td><td> </td> <td>  </td> <td> <h4> <b>  Total </b> </h4>  </td><td> </td> <td> <h3><b>'.$sold.'</b> </h3></td><td><td> </tr>';
			if($item_dam !=""){
			    echo  '<tr class="warning"><td> <h4> <b> Damaged</b> </h4>   </td><td> </td> <td>  </td> <td></td><td> </td> <td> <h3> </h3></td><td><td> </tr>';
			
				echo   $item_dam;
			echo  '<tr  class="warning"><td> </td><td> </td> <td>  </td> <td> <h4> <b>  Total </b> </h4>  </td><td> </td> <td> </td><td></td><td><h3><b>'.$dam.'</b> </h3></td> </tr>';
			}
			if($item_fal !=''){
				 echo  '<tr  class="info"><td> <h4> <b> Faulty</b> </h4></td><td> </td> <td>  </td> <td>   </td><td> </td> <td> <h3> </h3></td><td></td><td></td> </tr>';
			
			echo   $item_fal;
			echo  '<tr  class="info"><td> </td><td> </td> <td>  </td> <td> <h4> <b>  Total </b> </h4>  </td><td> </td> <td> </td><td> </td><td><h3><b>'.$fal.'</b> </h3></td> </tr>';
			}
			
				
			 
	}
	
		function stock_level($item , $start , $end , $item_name , $shop){
			$item_sold_per_id = 0;
			$balance_left = 0;
			$perid_damage = $period_faulty = 0;
			$item_faulty = $item_damage = $item_details =   '';
			$stockitemsdata =  false;
			if($shop < 1){
				$stockitemsdata = DB::query("SELECT item, shop, quantity FROM item_stock_level WHERE item_stock_level.item   =%i     ", $item);
			}else {
				$stockitemsdata = DB::query("SELECT item, shop, quantity FROM item_stock_level WHERE item_stock_level.item   =%i     and shop =%i ", $item , $shop);
			}
			 
			
			 
		 	  if(DB::count() != 0){
				foreach ($stockitemsdata  as $category)
	              {
			        $stock_item=  $category['item'];//now we can get the price ID
					 
					$quantity = $category['quantity'];
					$balance_left += $quantity;
					$price_id = PriceId2($stock_item , $category['shop']);
					$faulty = $damaged = $no_sold =0;
					if($category['shop'] >0){
					$item_details .= Item_sale_details($price_id, $start, $end ,$item_name );
					$item_damage .=Special_sale_details($price_id, $start, $end ,$item_name,  2);
					$item_faulty .=Special_sale_details($price_id, $start, $end ,$item_name , 3);
					
					 
						$no_sold =  (int) No_sold($price_id, $start , $end, $category['shop']);//
						$damaged =  (int) No_disposed($price_id, $start , $end, $category['shop'], 2);//
						$faulty =  (int) No_disposed($price_id, $start , $end, $category['shop'], 3);//
						 
					}
					    $item_sold_per_id += $no_sold;
							$perid_damage +=  $damaged;
							$period_faulty += $faulty;
		 
					echo '<tr><td>  </td><td> </td> <td> '.Shop_name($category['shop']).' </td> <td>'.$price_id.' </td><td> '.$quantity.' </td> <td>  '.$no_sold.'</td><td>  '.$damaged.'</td><td>  '.$faulty.'</td></tr>';
				 
			  
		          } 
				
			 }
			 
			 echo '<tr><td> </td><td> </td><td> </td>  <td>Sub Totals</td><td>  '.$balance_left.' </td> <td> '.$item_sold_per_id.'</td> <td> '.$perid_damage.'</td> <td> '.$period_faulty.'</td></tr>';
				
			 return array($item_sold_per_id, $balance_left , $item_details , $perid_damage , $period_faulty , $item_damage , $item_faulty);
	}
	
	
		function No_sold($price_id, $start, $end , $shop){
			$epecific_price_id_sales = 0;
			 $stockitemsdata = DB::query('SELECT sum(quantity) as qty  FROM cash_sale_item WHERE is_cancelled = 0 AND  shop = '.$shop.'  AND price_id ='.$price_id.' and created_date >= STR_TO_DATE("'.$start.'", "%Y-%m-%d") AND created_date < STR_TO_DATE("'.$end.'", "%Y-%m-%d") + INTERVAL 1 DAY  ORDER BY id'); 
		
			 
		     if(DB::count() != 0){
				foreach ($stockitemsdata  as $category)
	              {
			       $epecific_price_id_sales =  $category['qty'];  
			  
		          } 
				
			 }
			 return $epecific_price_id_sales;
	} 
	
		function No_disposed($price_id, $start, $end , $shop , $type){
			$epecific_price_id_sales = 0;
			 $stockitemsdata = DB::query('SELECT sum(quantity) as qty  FROM special_sale_item WHERE bounce_type = '.$type.' AND  is_cancelled = 0 AND  shop = '.$shop.'  AND price_id ='.$price_id.' and created_date >= STR_TO_DATE("'.$start.'", "%Y-%m-%d") AND created_date < STR_TO_DATE("'.$end.'", "%Y-%m-%d") + INTERVAL 1 DAY  ORDER BY id'); 
		
			 
		     if(DB::count() != 0){
				foreach ($stockitemsdata  as $category)
	              {
			       $epecific_price_id_sales =  $category['qty'];  
			  
		          } 
				
			 }
			 return $epecific_price_id_sales;
	}
	
	
		function Item_sale_details($price_id, $start, $end  , $item){
			 $seles_details = '<tr class="success" ><td>'.$item.' </td><td> </td><td>  </td>  <td>  </td><td>   </td> <td>  </td><td>   </td> <td>  </td> </tr>';
			
			$epecific_price_id_sales = 0;
			 $stockitemsdata = DB::query('SELECT *FROM cash_sale_item WHERE is_cancelled = 0 AND price_id ='.$price_id.' and created_date >= STR_TO_DATE("'.$start.'", "%Y-%m-%d") AND created_date < STR_TO_DATE("'.$end.'", "%Y-%m-%d") + INTERVAL 1 DAY  ORDER BY id'); 
		
			 
		     if(DB::count() != 0){
				foreach ($stockitemsdata  as $category)
	              {
			       
				   $seles_details .= '<tr class="success" ><td> </td><td> '.Shop_name($category['shop']).'</td> <td> '. $category['created_date'].'</td>  <td>'.$category['receipt'].' </td><td>  '.$category['serial_number'].' </td> <td> '.$category['quantity'].'</td><td>   </td> <td>  </td></tr>';
			
			  
		          } 
				
			 }
			 else {$seles_details ='';}
			 return $seles_details;
			  
	} 
	
	function Special_sale_details($price_id, $start, $end  , $item , $bounce_type){
		$color ='class="info"';
		if($bounce_type ==2){
			$color ='class="warning"';
		}
		
			 $seles_details = '<tr  '.$color.'.><td>'.$item.' </td><td> </td><td>  </td>  <td>  </td><td>   </td> <td>  </td><td>   </td> <td>  </td></tr>';
			
			$epecific_price_id_sales = 0;
			 $stockitemsdata = DB::query('SELECT *FROM special_sale_item WHERE  bounce_type ='.$bounce_type.' AND is_cancelled = 0 AND price_id ='.$price_id.' and created_date >= STR_TO_DATE("'.$start.'", "%Y-%m-%d") AND created_date < STR_TO_DATE("'.$end.'", "%Y-%m-%d") + INTERVAL 1 DAY  ORDER BY id'); 
		
			 
		     if(DB::count() != 0){
				foreach ($stockitemsdata  as $category)
	              {
			       
				   $seles_details .= '<tr  '.$color.'.><td> </td><td> '.Shop_name($category['shop']).'</td> <td> '. $category['created_date'].'</td>  <td>'.$category['receipt'].' </td><td>  '.$category['serial_number'].' </td> <td></td><td></td><td> '.$category['quantity'].'</td></tr>';
			
			  
		          } 
				
			 }
			 else {$seles_details ='';}
			 return $seles_details;
			  
	}
	
	
	
	if(isset($_POST['category']))
	{
		 $start = strtotime($_POST['start']);
         $end = strtotime($_POST['end']);
		if($_SESSION["connectedusergroup"] == 1 || $_SESSION["connectedusergroup"] == 2)
		{
			if($end < $start){
				$err=$err."End date should atleast be greater than start date!<br>";
				
			}
			 
			//if(DB::count() == 0)
				//$err=$err."No Stock List For Selected Category Found!<br>";
			 
			
		}
		else
		{
			$err=$err."Limited Access!<br>";
		}
		
	}
?>
			<!-- Content Wrapper. Contains page content -->
			<div class="content-wrapper">
				<!-- Content Header (Page header) -->
				<section class="content-header">
					<h1>Store Quantities By Category</h1>
					 
					<ol class="breadcrumb">
					</ol>
				</section>

				<!-- Main content -->
				<section class="content">
					<!-- Default box -->
					<div class="box box-primary">
						<?php
							if($_SESSION["connectedusergroup"] == 1 || $_SESSION["connectedusergroup"] == 2)
							{
						?>
						<div class="box-body">
							<div id="servererrormessage">
							</div>
							
							<div class="box-body table-responsive no-padding">
								<div class="col-sm-12">
									<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
										<div class="form-group col-sm-2">
											<label for="category">Start DATE</label>
											<input class="form-group col-sm-12"  required value='<?php  if(isset($_POST['start'])){ echo $_POST['start']; }?>' type='date' name ='start' id ='start' />
										 
										</div>
										
										<div class="form-group col-sm-2"> 
											 <label for="category">End DATE</label>
											<input class="form-group col-sm-12" required value='<?php  if(isset($_POST['end'])){ echo $_POST['end']; }?>'   type='date' name ='end' id ='end' />
									 
										</div>
										
										<div class="form-group col-sm-2">
											<label for="category">Stock Item Category</label>
											<select   class="form-control category" name="category" required id="category"   data-placeholder="Choose One">
											
											 <option value="0">All</option>
												<?php
													$categorydata = DB::query("SELECT id, category_name FROM item_category ORDER BY category_name");
													
													foreach ($categorydata as $category)
													{
												?>
												<option <?php if(isset($_POST['category']) && $category['id']==$_POST['category']) echo "selected=\"selected\""; ?> value="<?php echo $category['id']; ?>"><?php echo $category['category_name']; ?></option>
												<?php
													}
												?>
											</select>
										</div><!-- form-group -->
										<div class="form-group col-sm-3">
											<label for="category">Item</label> <br />
											<select   class="form-control  " name="item"  id="item"   data-placeholder="Choose One">
											<option value="<?php  if(isset($_POST['item'])){ echo $_POST['item']; } else echo 0;?>"><?php  if(isset($_POST['item'])){ echo Item_name($_POST['item']); }else {?>No item selected <?php }  ?></option>
											 <?php if(isset($_POST['item'])){ ?>
											 <option value="0">Select All</option>
											 
											 <?php
											 $categorydata = DB::query("SELECT item_name, id FROM stock_item where category =%i" , $_POST['category']);
												if(DB::count() != 0){
													$name ='';
													
												   foreach ($categorydata as $category)
													  {
														 
														echo  '<option value="'.$category['id'].'">'.$category['item_name'].'</option>';
												  
													  }
												}
											 
											 }?>
											</select>
										 
										</div>
										
										<div class="form-group col-sm-2">
											<label for="category">Shop</label> <br />
											<select   class="form-control " name="shop"   id="shop"   data-placeholder="Choose One">
											 
											<option value="<?php  if(isset($_POST['shop'])){ echo $_POST['shop']; } else echo 0;?>"><?php  if(isset($_POST['shop'])){ echo Shop_name($_POST['shop']); }else {?>No item selected <?php }  ?></option>
											 <?php ?>
											  <option value="0">Select All</option>
											 <?php
											 
											 $categorydata = DB::query("SELECT full_name , id FROM shop  " );
													if(DB::count() != 0){										
													   foreach ($categorydata as $category)
														  {
															  echo ' <option value="'.$category['id'].'">'.$category['full_name'].'</option>';
													 
													  
														  }
													}
											 ?>
											</select>
										 
										</div>
										
										<div class="form-group col-sm-1">
											<label for="category"> </label> <br />
											<button type='submit' class='btn btn-info'> Submit </button>
										 
										</div>
										
										
									</form>									
								</div>
								
								<div class="col-md-12" style="margin-top: 25px"></div>
								<?php
									if($err=="" && isset($_POST['category']))
									{
								?>
								<div class="col-sm-12">
									<div class="input-group-btn">
										<button onclick="gendawano('stock_categpry_ledger', 'categoryfocus', <?php echo $_POST['category']; ?>)" class="btn btn-sm btn-primary pull-right"><i class="glyphicon glyphicon-print"></i> Print</button>
									</div>
								</div>
								
								<div class="col-md-12" style="margin-top: 25px"></div>
								
								
								 <button class="btn btn-default dropdown-toggle" type="button" id="dropdownMenu1" data-toggle="dropdown" aria-haspopup="true" aria-expanded="true">
									 <span class="glyphicon glyphicon-th-list"></span> <a href="#" onclick="$('#employees').tableExport({type:'excel',escape:'false'});"> <img src="images/xls.png" width="24px"> XLS</a>
								   
								  </button>
																
								<div class="box-body table-responsive"  id="employees">
								<h3 class="box-title text-center">  <b>Sales and stock ledger from  <?php  echo Format_dates($_POST['start']);?> to <?php  echo Format_dates($_POST['end']);?>  </b> 
												

								</h3>
									<table idx="stockitemstable" class="table table-striped"  >
										<thead>
											<tr>
												 
												<th>Name</th>
												 <th>Date</th>
												<th>Code</th>
												
												<th>Barcode</th>
												<th>Quantity At Hand</th>
												<th >No sold</th>
												<th> Damaged  </th> <th> Faulty  </th>
											</tr>
										</thead>
										<tr>
												 
												<td colspan='8'> 
												<h4 class="box-title text-center"  align='center'> <b>Sales and stock ledger from  <?php  echo Format_dates($_POST['start']);?> to <?php  echo Format_dates($_POST['end']);?>  </b></h4>
												</td>
                                                 
											</tr>
											
										<?php
									 
										 stock_category($_POST['category'] ,  Format_dates($_POST['start']) ,  Format_dates($_POST['end']) , $_POST['item'] , $_POST['shop']);
							 
										

										?>
										
										<!-- <tfoot>
											<tr>
												 
												<th>Name</th>
                                                 <th>Date</th>
												<th>Code</th>
												 
												<th>Barcode</th>
												<th>Quantity At Hand</th>
												<th >No sold</th>
											</tr>
										</tfoot> -->
										<tbody>
											 
										</tbody>
									</table>
								</div>
								<?php
									}
									
									if($err != "")
									{
								?>
								<div class="alert alert-danger">
									<i class="glyphicon glyphicon-ban-circle"></i><br>
									<?php 
										echo $err; 
									?>
								</div>
								<?php
									}
								?>
							</div>
							
						</div><!-- /.box-body -->
						<div class="box-footer">
							
						</div><!-- /.box-footer-->
						<?php
							}
							else
							{
						?>
						<div class="alert alert-danger alert-dismissable">
							<i class="glyphicon glyphicon-ban-circle"></i><br>
							Limited Access
						</div>
						<?php
							}
						?>
					</div><!-- /.box -->
				</section><!-- /.content -->
			</div><!-- /.content-wrapper -->

			<footer class="main-footer text-center">
				<strong>Copyright &copy; <?php echo date("Y"); ?> BSMPOS.</strong> All rights reserved.
			</footer>
		</div><!-- ./wrapper -->
		
		<div class="modal" id="waitdialogmodal"  data-backdrop="static">
            <div class="modal-dialog modal-sm">
              <div class="modal-content">
                  <div class="modal-body">
					<div class="text-center">
						<img src="<?php echo $base_url . $ajax_loading_image; ?>" />
					</div>
				  </div>
              </div>
            </div>
        </div>
		
		<div class="modal" id="mediummodal">
			<div class="modal-dialog">
				<div class="modal-content">
					<div class="modal-body">					
						<div class="row" id="mediummodaldetails">
					
						</div>
					</div>
				</div>
			</div>
		</div>
		
		<div class="modal" id="largemodal">
			<div class="modal-dialog modal-lg">
				<div class="modal-content">
					<div class="modal-body">					
						<div class="row" id="largemodaldetails">
							
						</div>
					</div>
				</div>
			</div>
		</div>
		
		<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css"/>
<script type="text/javascript" src="tableExport.js"></script>
<script type="text/javascript" src="jquery.base64.js"></script>
<script type="text/javascript" src="html2canvas.js"></script>
<script type="text/javascript" src="jspdf/libs/sprintf.js"></script>
<script type="text/javascript" src="jspdf/jspdf.js"></script>
<script type="text/javascript" src="jspdf/libs/base64.js"></script>
<script type="text/javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>

<script>
$(document).ready(function() {
    $('select.category').change(function(){
		 $('#item') .attr("readonly", false);  
		$("#item").empty();
         $.ajax({
                type: 'POST',
                url: 'select_items_from_category.php',
                data: {category: $('select.category').val() },
				success: function(html){ 
				 $("#item").append(html);
				},
                dataType: 'html'
         }); 
    });
});
</script>

		<!-- jQuery 2.1.3 -->
	 
		<!-- Bootstrap 3.3.2 JScc -->
		 
		<!-- SlimScroll -->
		<script src="plugins/slimScroll/jquery.slimScroll.min.js" type="text/javascript"></script>
		<!-- FastClick -->
		<script src='plugins/fastclick/fastclick.min.js'></script>
		<!-- DATA TABES SCRIPT -->
		<script src="plugins/datatables/jquery.dataTables.js" type="text/javascript"></script>
		<script src="plugins/datatables/dataTables.bootstrap.js" type="text/javascript"></script>
		<!-- AdminLTE App -->
		<script src="dist/js/app.min.js" type="text/javascript"></script>
		<script src="plugins/bert/storepos.js" type="text/javascript"></script>
		<script src="plugins/bert/store.js" type="text/javascript"></script>
	</body>
</html>