<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<?php 
include ("../navigation/header.php");

$page_name = "Stores-main";
include ("../navigation/access.php");

if ($permission_num==1) {

include ("../navigation/store_sidebar.php");

$date1 = $_GET['stock_date_select'];
$date2 = $_GET['stock_date_select2'];

if ($date1=='') {
$table_name = $_SESSION['db_prefix']."_stockloss";
$date1_query = "SELECT date FROM $table_name WHERE category = 'Production'  ORDER BY date DESC LIMIT 1";
$date1_result = mySQL_query($date1_query);
$date1 = mysql_result($date1_result, 0, "date");

}

if ($date2=='') {
$table_name = $_SESSION['db_prefix']."_stockloss";
$date2_query = "SELECT date FROM $table_name WHERE category = 'Production' AND date < '".$date1."' ORDER BY date DESC LIMIT 1";
$date2_result = mySQL_query($date2_query);
$date2 = mysql_result($date2_result, 0, "date");
}

$table_name = $_SESSION['db_prefix']."_stockloss";
$stock_dates_query = "SELECT DISTINCT date FROM $table_name WHERE category = 'Production'  ORDER BY date DESC";
$stock_dates_result = mySQL_query($stock_dates_query);
$stock_dates_num = mysql_num_rows($stock_dates_result);


// begin river sand
$table_name = $_SESSION['db_prefix']."_stockloss";
$aggregates1a_count_query = "SELECT total, unit_price FROM $table_name WHERE date = '".$date1."' and store_item_id = '10038'  ";
$aggregates1a_count_result = mySQL_query($aggregates1a_count_query);
$aggregates1a = mysql_result($aggregates1a_count_result, 0, "total");
$cost_aggregates1a = mysql_result($aggregates1a_count_result, 0, "unit_price");

$table_name = $_SESSION['db_prefix']."_stockloss";
$aggregates2a_count_query = "SELECT total, unit_price FROM $table_name WHERE date = '".$date2."' and store_item_id = '10038'  ";
$aggregates2a_count_result = mySQL_query($aggregates2a_count_query);
$aggregates2a = mysql_result($aggregates2a_count_result, 0, "total");


$aggregates_a_usage = $aggregates2a - $aggregates1a;

$table_name1 = $_SESSION['db_prefix']."_receive_store";
$table_name2 = $_SESSION['db_prefix']."_receive_store_lines";
$aggregates_a_received_query = "SELECT SUM(quantity) AS summed_quantity FROM $table_name1, $table_name2  WHERE store_item_id = '10038' AND receive_date >= '".$date2."' AND receive_date <= '".$date1."' AND $table_name1.receive_id = $table_name2.receive_id  ";
$aggregates_a_received_result = mySQL_query($aggregates_a_received_query);
$aggregates_a_received = mysql_result($aggregates_a_received_result, 0, "summed_quantity");

$table_name = $_SESSION['db_prefix']."_mix_design";
$type_tile_query = "SELECT mix_design FROM $table_name WHERE product_type = 'Tiles' ";
$type_tile_result = mySQL_query($type_tile_query);
$type_tile_num = mysql_num_rows($type_tile_result);

$total_skips=0;
$total_aggregates_a_usage_tiles=0;
$i=0;
while ($i<$type_tile_num) {
$mix_design = mysql_result($type_tile_result, $i, "mix_design");

$table_name = $_SESSION['db_prefix']."_production_plan_lines";
$skip_query = "SELECT SUM(quantity) AS summed_quantity FROM $table_name WHERE mix_design_name = '".$mix_design."' AND action = 'skip_count' AND action_date >= '".$date2."' AND action_date <= '".$date1."'   ";
$skip_result = mySQL_query($skip_query);
$skips = mysql_result($skip_result, 0, "summed_quantity");
$total_skips=$total_skips+$skips;

$table_name = $_SESSION['db_prefix']."_mix_design_lines";
$aggregates_a_usage_tiles_query = "SELECT quantity FROM $table_name WHERE mix_design = '".$mix_design."'  AND store_item_id = '10038'  ";
$aggregates_a_usage_tiles_result = mySQL_query($aggregates_a_usage_tiles_query);
$aggregates_a_usage_tiles_num = mysql_num_rows($aggregates_a_usage_tiles_result);
if ($aggregates_a_usage_tiles_num!=0) {
$aggregates_a_usage_tiles = mysql_result($aggregates_a_usage_tiles_result, 0, "quantity");
}
else {
$aggregates_a_usage_tiles;
}
$aggregates_a_usage_tiles = $aggregates_a_usage_tiles * $skips;

$total_aggregates_a_usage_tiles = $total_aggregates_a_usage_tiles + $aggregates_a_usage_tiles;

$i++;
}

$table_name = $_SESSION['db_prefix']."_mix_design";
$type_acc_query = "SELECT mix_design FROM $table_name WHERE product_type = 'Accessories' ";
$type_acc_result = mySQL_query($type_acc_query);
$type_acc_num = mysql_num_rows($type_acc_result);

$total_skips=0;
$total_aggregates_a_usage_acc=0;
$i=0;
while ($i<$type_acc_num) {
$mix_design = mysql_result($type_acc_result, $i, "mix_design");

$table_name = $_SESSION['db_prefix']."_production_plan_lines";
$skip_query = "SELECT SUM(quantity) AS summed_quantity FROM $table_name WHERE mix_design_name = '".$mix_design."' AND action = 'skip_count' AND action_date >= '".$date2."' AND action_date <= '".$date1."'   ";
$skip_result = mySQL_query($skip_query);
$skips = mysql_result($skip_result, 0, "summed_quantity");
$total_skips=$total_skips+$skips;

$table_name = $_SESSION['db_prefix']."_mix_design_lines";
$aggregates_a_usage_acc_query = "SELECT quantity FROM $table_name WHERE mix_design = '".$mix_design."'  AND store_item_id = '10038'  ";
$aggregates_a_usage_acc_result = mySQL_query($aggregates_a_usage_acc_query);
$aggregates_a_usage_acc_num = mysql_num_rows($aggregates_a_usage_acc_result);
if ($aggregates_a_usage_acc_num!=0) {
$aggregates_a_usage_acc = mysql_result($aggregates_a_usage_acc_result, 0, "quantity");
}
else {
$aggregates_a_usage_acc;
}
$aggregates_a_usage_acc = $aggregates_a_usage_acc * $skips;

$total_aggregates_a_usage_acc = $total_aggregates_a_usage_acc + $aggregates_a_usage_acc;

$i++;
}
$calc_aggregates1a = $aggregates2a + $aggregates_a_received - $total_aggregates_a_usage_tiles - $total_aggregates_a_usage_acc;

$dif_aggregates1a = $aggregates1a-$calc_aggregates1a;
$dif_aggregates1a_perc = (($aggregates1a/$calc_aggregates1a) * 100)-100;

$cost_dif_aggregates1a = $dif_aggregates1a * $cost_aggregates1a;



// end river sand

// begin silica sand
$table_name = $_SESSION['db_prefix']."_stockloss";
$aggregates1b_count_query = "SELECT total, unit_price FROM $table_name WHERE date = '".$date1."' and store_item_id = '10039'  ";
$aggregates1b_count_result = mySQL_query($aggregates1b_count_query);
$aggregates1b = mysql_result($aggregates1b_count_result, 0, "total");
$cost_aggregates1b = mysql_result($aggregates1a_count_result, 0, "unit_price");

$table_name = $_SESSION['db_prefix']."_stockloss";
$aggregates2b_count_query = "SELECT total FROM $table_name WHERE date = '".$date2."' and store_item_id = '10039'  ";
$aggregates2b_count_result = mySQL_query($aggregates2b_count_query);
$aggregates2b = mysql_result($aggregates2b_count_result, 0, "total");

$aggregates_b_usage = $aggregates2b - $aggregates1b;

$table_name1 = $_SESSION['db_prefix']."_receive_store";
$table_name2 = $_SESSION['db_prefix']."_receive_store_lines";
$aggregates_b_received_query = "SELECT SUM(quantity) AS summed_quantity FROM $table_name1, $table_name2  WHERE store_item_id = '10039' AND receive_date >= '".$date2."' AND receive_date <= '".$date1."' AND $table_name1.receive_id = $table_name2.receive_id  ";
$aggregates_b_received_result = mySQL_query($aggregates_b_received_query);
$aggregates_b_received = mysql_result($aggregates_b_received_result, 0, "summed_quantity");

$table_name = $_SESSION['db_prefix']."_mix_design";
$type_tile_query = "SELECT mix_design FROM $table_name WHERE product_type = 'Tiles' ";
$type_tile_result = mySQL_query($type_tile_query);
$type_tile_num = mysql_num_rows($type_tile_result);

$total_skips=0;
$total_aggregates_b_usage_tiles=0;
$i=0;
while ($i<$type_tile_num) {
$mix_design = mysql_result($type_tile_result, $i, "mix_design");

$table_name = $_SESSION['db_prefix']."_production_plan_lines";
$skip_query = "SELECT SUM(quantity) AS summed_quantity FROM $table_name WHERE mix_design_name = '".$mix_design."' AND action = 'skip_count' AND action_date >= '".$date2."' AND action_date <= '".$date1."'   ";
$skip_result = mySQL_query($skip_query);
$skips = mysql_result($skip_result, 0, "summed_quantity");
$total_skips=$total_skips+$skips;

$table_name = $_SESSION['db_prefix']."_mix_design_lines";
$aggregates_b_usage_tiles_query = "SELECT quantity FROM $table_name WHERE mix_design = '".$mix_design."' AND store_item_id = '10039'  ";
$aggregates_b_usage_tiles_result = mySQL_query($aggregates_b_usage_tiles_query);
$aggregates_b_usage_tiles_result_num = mysql_num_rows($aggregates_b_usage_tiles_result);
if ($aggregates_b_usage_tiles_result_num!=0) {
$aggregates_b_usage_tiles = mysql_result($aggregates_b_usage_tiles_result, 0, "quantity");
} 
else {
$aggregates_b_usage_tiles = 0;
}
$aggregates_b_usage_tiles = $aggregates_b_usage_tiles * $skips;

$total_aggregates_b_usage_tiles = $total_aggregates_b_usage_tiles + $aggregates_b_usage_tiles;

$i++;
}


$table_name = $_SESSION['db_prefix']."_mix_design";
$type_acc_query = "SELECT mix_design FROM $table_name WHERE product_type = 'Accessories' ";
$type_acc_result = mySQL_query($type_acc_query);
$type_acc_num = mysql_num_rows($type_acc_result);

$total_skips=0;
$total_aggregates_b_usage_acc=0;
$i=0;
while ($i<$type_acc_num) {
$mix_design = mysql_result($type_acc_result, $i, "mix_design");

$table_name = $_SESSION['db_prefix']."_production_plan_lines";
$skip_query = "SELECT SUM(quantity) AS summed_quantity FROM $table_name WHERE mix_design_name = '".$mix_design."' AND action = 'skip_count' AND action_date >= '".$date2."' AND action_date <= '".$date1."'   ";
$skip_result = mySQL_query($skip_query);
$skips = mysql_result($skip_result, 0, "summed_quantity");
$total_skips=$total_skips+$skips;

$table_name = $_SESSION['db_prefix']."_mix_design_lines";
$aggregates_b_usage_acc_query = "SELECT quantity FROM $table_name WHERE mix_design = '".$mix_design."'  AND store_item_id = '10039'  ";
$aggregates_b_usage_acc_result = mySQL_query($aggregates_b_usage_acc_query);
$aggregates_b_usage_acc_num = mysql_num_rows($aggregates_b_usage_acc_result);
if ($aggregates_b_usage_acc_num!=0) {
$aggregates_b_usage_acc = mysql_result($aggregates_b_usage_acc_result, 0, "quantity");
}
else {
$aggregates_b_usage_acc=0;
}
$aggregates_b_usage_acc = $aggregates_b_usage_acc * $skips;

$total_aggregates_b_usage_acc = $total_aggregates_b_usage_acc + $aggregates_b_usage_acc;

$i++;
}
$calc_aggregates1b = $aggregates2b + $aggregates_b_received - $total_aggregates_b_usage_tiles - $total_aggregates_b_usage_acc;

$dif_aggregates1b = $aggregates1b-$calc_aggregates1b;
$dif_aggregates1b_perc = (($aggregates1b/$calc_aggregates1b) * 100)-100;
$cost_dif_aggregates1b = $dif_aggregates1b * $cost_aggregates1b;

// end silica sand



// begin cement
$table_name = $_SESSION['db_prefix']."_stockloss";
$bulk_cement1_count_query = "SELECT total, unit_price FROM $table_name WHERE date = '".$date2."' and store_item_id = '10047'  ";
$bulk_cement1_count_result = mySQL_query($bulk_cement1_count_query);
$bulk_cement1 = mysql_result($bulk_cement1_count_result, 0, "total");
$cost_bulk_cement1 = mysql_result($bulk_cement1_count_result, 0, "unit_price");


$table_name = $_SESSION['db_prefix']."_stockloss";
$bulk_cement2_count_query = "SELECT total, unit_price FROM $table_name WHERE date = '".$date1."' and store_item_id = '10047'  ";
$bulk_cement2_count_result = mySQL_query($bulk_cement2_count_query);
$bulk_cement2 = mysql_result($bulk_cement2_count_result, 0, "total");
$cost_bulk_cement2 = mysql_result($bulk_cement2_count_result, 0, "unit_price");


$table_name = $_SESSION['db_prefix']."_stockloss";
$bag_cement1_count_query = "SELECT total FROM $table_name WHERE date = '".$date2."' and store_item_id = '10048'  ";
$bag_cement1_count_result = mySQL_query($bag_cement1_count_query);
$bag_cement1 = mysql_result($bag_cement1_count_result, 0, "total");

$table_name = $_SESSION['db_prefix']."_stockloss";
$bag_cement2_count_query = "SELECT total FROM $table_name WHERE date = '".$date1."' and store_item_id = '10048'  ";
$bag_cement2_count_result = mySQL_query($bag_cement2_count_query);
$bag_cement2 = mysql_result($bag_cement2_count_result, 0, "total");


$opening_cement = $bulk_cement1 + $bag_cement1;
$closing_cement = $bulk_cement2 + $bag_cement2;


$cement_usage = ($bulk_cement2 + $bag_cement2) - ($bulk_cement1 + $bag_cement1);

$table_name1 = $_SESSION['db_prefix']."_receive_store";
$table_name2 = $_SESSION['db_prefix']."_receive_store_lines";
$bulk_cement_received_query = "SELECT SUM(quantity) AS summed_quantity FROM $table_name1, $table_name2  WHERE store_item_id = '10047' AND receive_date >= '".$date2."' AND receive_date <= '".$date1."' AND $table_name1.receive_id = $table_name2.receive_id  ";
$bulk_cement_received_result = mySQL_query($bulk_cement_received_query);
$bulk_cement_received = mysql_result($bulk_cement_received_result, 0, "summed_quantity");

$table_name1 = $_SESSION['db_prefix']."_receive_store";
$table_name2 = $_SESSION['db_prefix']."_receive_store_lines";
$bag_cement_received_query = "SELECT SUM(quantity) AS summed_quantity FROM $table_name1, $table_name2  WHERE store_item_id = '10048' AND receive_date >= '".$date2."' AND receive_date <= '".$date1."' AND $table_name1.receive_id = $table_name2.receive_id  ";
$bag_cement_received_result = mySQL_query($bag_cement_received_query);
$bag_cement_received = mysql_result($bag_cement_received_result, 0, "summed_quantity");

$cement_received = $bulk_cement_received + $bag_cement_received; 

$table_name = $_SESSION['db_prefix']."_mix_design";
$type_tile_query = "SELECT mix_design FROM $table_name WHERE product_type = 'Tiles' ";
$type_tile_result = mySQL_query($type_tile_query);
$type_tile_num = mysql_num_rows($type_tile_result);

$total_skips=0;
$total_cement_usage_tiles=0;
$i=0;
while ($i<$type_tile_num) {
$mix_design = mysql_result($type_tile_result, $i, "mix_design");

$table_name = $_SESSION['db_prefix']."_production_plan_lines";
$skip_query = "SELECT SUM(quantity) AS summed_quantity FROM $table_name WHERE mix_design_name = '".$mix_design."' AND action = 'skip_count' AND action_date >= '".$date2."' AND action_date <= '".$date1."'   ";
$skip_result = mySQL_query($skip_query);
$skips = mysql_result($skip_result, 0, "summed_quantity");
$total_skips=$total_skips+$skips;

$table_name = $_SESSION['db_prefix']."_mix_design_lines";
$cement_usage_tiles_query = "SELECT quantity FROM $table_name WHERE mix_design = '".$mix_design."' AND store_item_id = '10047'  ";
$cement_usage_tiles_result = mySQL_query($cement_usage_tiles_query);
$cement_usage_tiles_num = mysql_num_rows($cement_usage_tiles_result);
if ($cement_usage_tiles_num!=0) {
$cement_usage_tiles = mysql_result($cement_usage_tiles_result, 0, "quantity");
}
else {
$cement_usage_tiles = 0;
}

$cement_tiles = $cement_usage_tiles * $skips;

$total_cement_usage_tiles = $total_cement_usage_tiles + $cement_tiles;

$i++;
}


$table_name = $_SESSION['db_prefix']."_mix_design";
$type_acc_query = "SELECT mix_design FROM $table_name WHERE product_type = 'Accessories' ";
$type_acc_result = mySQL_query($type_acc_query);
$type_acc_num = mysql_num_rows($type_acc_result);

$total_skips=0;
$total_cement_usage_acc=0;
$i=0;
while ($i<$type_acc_num) {
$mix_design = mysql_result($type_acc_result, $i, "mix_design");

$table_name = $_SESSION['db_prefix']."_production_plan_lines";
$skip_query = "SELECT SUM(quantity) AS summed_quantity FROM $table_name WHERE mix_design_name = '".$mix_design."' AND action = 'skip_count' AND action_date >= '".$date2."' AND action_date <= '".$date1."'   ";
$skip_result = mySQL_query($skip_query);
$skips = mysql_result($skip_result, 0, "summed_quantity");
$total_skips=$total_skips+$skips;

$table_name = $_SESSION['db_prefix']."_mix_design_lines";
$cement_usage_acc_query = "SELECT quantity FROM $table_name WHERE mix_design = '".$mix_design."'  AND store_item_id = '10048'  ";
$cement_usage_acc_result = mySQL_query($cement_usage_acc_query);
$cement_usage_acc_num = mysql_num_rows($cement_usage_acc_result);
if ($cement_usage_acc_num!=0) {
$cement_usage_acc = mysql_result($cement_usage_acc_result, 0, "quantity");
}
else {
$cement_usage_acc=0;
}
$cement_usage_acc = $cement_usage_acc * $skips;

$total_cement_usage_acc = $total_cement_usage_acc + $cement_usage_acc;

$i++;
}
$calc_cement = $opening_cement + $cement_received - $total_cement_usage_tiles - $total_cement_usage_acc;

$dif_cement = $closing_cement-$calc_cement;
$dif_cement_perc = (($closing_cement/$calc_cement) * 100)-100;

$cost_ddif_cement_perc = $dif_cement * $cost_bulk_cement1;
// end cement


// begin pigment
$table_name = $_SESSION['db_prefix']."_stockloss";
$pigment1_count_query = "SELECT SUM(total) AS summed_total FROM $table_name WHERE date = '".$date1."' and sub_category = 'pigment'  ";
$pigment1_count_result = mySQL_query($pigment1_count_query);
$pigment1 = mysql_result($pigment1_count_result, 0, "summed_total");

$table_name = $_SESSION['db_prefix']."_stockloss";
$pigment2_count_query = "SELECT SUM(total) AS summed_total FROM $table_name WHERE date = '".$date2."' and sub_category = 'pigment'  ";
$pigment2_count_result = mySQL_query($pigment2_count_query);
$pigment2 = mysql_result($pigment2_count_result, 0, "summed_total");

$pigment_usage = $pigment2 - $pigment1;

$table_name1 = $_SESSION['db_prefix']."_receive_store";
$table_name2 = $_SESSION['db_prefix']."_receive_store_lines";
$pigment_received_query = "SELECT SUM(quantity) AS summed_quantity FROM $table_name1, $table_name2  WHERE sub_category = 'Pigment'  AND receive_date >= '".$date2."' AND receive_date <= '".$date1."' AND $table_name1.receive_id = $table_name2.receive_id  ";
$pigment_received_result = mySQL_query($pigment_received_query);
$pigment_received = mysql_result($pigment_received_result, 0, "summed_quantity");


$table_name = $_SESSION['db_prefix']."_mix_design";
$type_tile_query = "SELECT mix_design FROM $table_name WHERE product_type = 'Tiles' ";
$type_tile_result = mySQL_query($type_tile_query);
$type_tile_num = mysql_num_rows($type_tile_result);

$total_skips=0;
$total_pigment_usage_tiles=0;
$i=0;
while ($i<$type_tile_num) {
$mix_design = mysql_result($type_tile_result, $i, "mix_design");

$table_name = $_SESSION['db_prefix']."_production_plan_lines";
$skip_query = "SELECT SUM(quantity) AS summed_quantity FROM $table_name WHERE mix_design_name = '".$mix_design."' AND action = 'skip_count' AND action_date >= '".$date2."' AND action_date <= '".$date1."'   ";
$skip_result = mySQL_query($skip_query);
$skips = mysql_result($skip_result, 0, "summed_quantity");
$total_skips=$total_skips+$skips;

$table_name = $_SESSION['db_prefix']."_mix_design_lines";
$pigment_usage_tiles_query = "SELECT SUM(quantity) AS summed_quantity FROM $table_name WHERE mix_design = '".$mix_design."' AND sub_category = 'Pigment' ";
$pigment_usage_tiles_result = mySQL_query($pigment_usage_tiles_query);
$pigment_usage_tiles_num = mysql_num_rows($pigment_usage_tiles_result);
if ($pigment_usage_tiles_num!=0){
$pigment_usage_tiles = mysql_result($pigment_usage_tiles_result, 0, "summed_quantity");
}
else {
$pigment_usage_tiles=0;
}
$pigment_tiles = $pigment_usage_tiles * $skips;

$total_pigment_usage_tiles = $total_pigment_usage_tiles + $pigment_tiles;

$i++;
}


$table_name = $_SESSION['db_prefix']."_mix_design";
$type_acc_query = "SELECT mix_design FROM $table_name WHERE product_type = 'Accessories' ";
$type_acc_result = mySQL_query($type_acc_query);
$type_acc_num = mysql_num_rows($type_acc_result);

$total_skips=0;
$total_pigment_usage_acc=0;
$i=0;
while ($i<$type_acc_num) {
$mix_design = mysql_result($type_acc_result, $i, "mix_design");

$table_name = $_SESSION['db_prefix']."_production_plan_lines";
$skip_query = "SELECT SUM(quantity) AS summed_quantity FROM $table_name WHERE mix_design_name = '".$mix_design."' AND action = 'skip_count' AND action_date >= '".$date2."' AND action_date <= '".$date1."'   ";
$skip_result = mySQL_query($skip_query);
$skips = mysql_result($skip_result, 0, "summed_quantity");
$total_skips=$total_skips+$skips;

$table_name = $_SESSION['db_prefix']."_mix_design_lines";
$pigment_usage_acc_query = "SELECT quantity FROM $table_name WHERE mix_design = '".$mix_design."'  AND sub_category = 'Pigment'  ";
$pigment_usage_acc_result = mySQL_query($pigment_usage_acc_query);
$pigment_usage_acc_num = mysql_num_rows($pigment_usage_acc_result);
if ($pigment_usage_acc_num!=0) {
$pigment_usage_acc = mysql_result($pigment_usage_acc_result, 0, "quantity");
}
else {
$pigment_usage_acc=0;
}
$pigment_usage_acc = $pigment_usage_acc * $skips;

$total_pigment_usage_acc = $total_pigment_usage_acc + $pigment_usage_acc;

$i++;
}
$calc_pigment = $pigment2 + $pigment_received - $total_pigment_usage_tiles - $total_pigment_usage_acc;

$dif_pigment = $pigment1-$calc_pigment;
$dif_pigment_perc = (($pigment1/$calc_pigment) * 100)-100;
// end pigment


// begin oils
$table_name = $_SESSION['db_prefix']."_stockloss";
$oils1_count_query = "SELECT SUM(total) AS summed_total FROM $table_name WHERE date = '".$date1."' and sub_category = 'Oils'  ";
$oils1_count_result = mySQL_query($oils1_count_query);
$oils1 = mysql_result($oils1_count_result, 0, "summed_total");

$table_name = $_SESSION['db_prefix']."_stockloss";
$oils2_count_query = "SELECT SUM(total) AS summed_total FROM $table_name WHERE date = '".$date2."' and sub_category = 'Oils'  ";
$oils2_count_result = mySQL_query($oils2_count_query);
$oils2 = mysql_result($oils2_count_result, 0, "summed_total");

$oils_usage = $oils2 - $oils1;

$table_name1 = $_SESSION['db_prefix']."_receive_store";
$table_name2 = $_SESSION['db_prefix']."_receive_store_lines";
$oils_received_query = "SELECT SUM(quantity) AS summed_quantity FROM $table_name1, $table_name2  WHERE sub_category = 'Oils'  AND receive_date >= '".$date2."' AND receive_date <= '".$date1."' AND $table_name1.receive_id = $table_name2.receive_id  ";
$oils_received_result = mySQL_query($oils_received_query);
$oils_received = mysql_result($oils_received_result, 0, "summed_quantity");


$table_name = $_SESSION['db_prefix']."_mix_design";
$type_tile_query = "SELECT mix_design FROM $table_name WHERE product_type = 'Tiles' ";
$type_tile_result = mySQL_query($type_tile_query);
$type_tile_num = mysql_num_rows($type_tile_result);

$total_skips=0;
$total_oils_usage_tiles=0;
$i=0;
while ($i<$type_tile_num) {
$mix_design = mysql_result($type_tile_result, $i, "mix_design");

$table_name = $_SESSION['db_prefix']."_production_plan_lines";
$skip_query = "SELECT SUM(quantity) AS summed_quantity FROM $table_name WHERE mix_design_name = '".$mix_design."' AND action = 'skip_count' AND action_date >= '".$date2."' AND action_date <= '".$date1."'   ";
$skip_result = mySQL_query($skip_query);
$skips = mysql_result($skip_result, 0, "summed_quantity");
$total_skips=$total_skips+$skips;

$table_name = $_SESSION['db_prefix']."_mix_design_lines";
$oils_usage_tiles_query = "SELECT SUM(quantity) AS summed_quantity FROM $table_name WHERE mix_design = '".$mix_design."' AND sub_category = 'Oils' ";
$oils_usage_tiles_result = mySQL_query($oils_usage_tiles_query);
$oils_usage_tiles_num = mysql_num_rows($oils_usage_tiles_result);
if ($oils_usage_tiles_num!=0) {
$oils_usage_tiles = mysql_result($oils_usage_tiles_result, 0, "summed_quantity");
}
else {
$oils_usage_tiles=0;
}
$oils_tiles = $oils_usage_tiles * $skips;

$total_oils_usage_tiles = $total_oils_usage_tiles + $oils_tiles;

$i++;
}


$table_name = $_SESSION['db_prefix']."_mix_design";
$type_acc_query = "SELECT mix_design FROM $table_name WHERE product_type = 'Accessories' ";
$type_acc_result = mySQL_query($type_acc_query);
$type_acc_num = mysql_num_rows($type_acc_result);

$total_skips=0;
$total_oils_usage_acc=0;
$i=0;
while ($i<$type_acc_num) {
$mix_design = mysql_result($type_acc_result, $i, "mix_design");
$table_name = $_SESSION['db_prefix']."_production_plan_lines";
$skip_query = "SELECT SUM(quantity) AS summed_quantity FROM $table_name WHERE mix_design_name = '".$mix_design."' AND action = 'skip_count' AND action_date >= '".$date2."' AND action_date <= '".$date1."'   ";
$skip_result = mySQL_query($skip_query);
$skips = mysql_result($skip_result, 0, "summed_quantity");
$total_skips=$total_skips+$skips;

$table_name = $_SESSION['db_prefix']."_mix_design_lines";
$oils_usage_acc_query = "SELECT quantity FROM $table_name WHERE mix_design = '".$mix_design."'  AND sub_category = 'Oils' ";
$oils_usage_acc_result = mySQL_query($oils_usage_acc_query);
$oils_usage_acc_num = mysql_num_rows($oils_usage_acc_result);
if ($oils_usage_acc_num!=0) {
$oils_usage_acc = mysql_result($oils_usage_acc_result, 0, "quantity");
}
else {
$oils_usage_acc=0;
}
$oils_usage_acc = $oils_usage_acc * $skips;

$total_oils_usage_acc = $total_oils_usage_acc + $oils_usage_acc;

$i++;
}
$calc_oils = $oils2 + $oils_received - $total_oils_usage_tiles - $total_oils_usage_acc;

$dif_oils = $oils1-$calc_oils;
$dif_oils_perc = (($oils1/$calc_oils) * 100)-100;
// end oils

?>
<!--style sheets-->
<link href="../css/main.css" rel="stylesheet" type="text/css">
<link rel="stylesheet" type="text/css" href="../css/tabs.css" />
<link rel="stylesheet" type="text/css" href="../css/scroll_table.css" />
<link href="../css/main.css" rel="stylesheet" type="text/css">
<link rel="stylesheet" type="text/css" href="../css/tabs.css" />

<!--jscript-->
<script language="javascript" src="../jscript/calendar.js"></script>
<script language="JavaScript" src="../jscript/tooltip.js"  type="text/javascript"></script> 

<script type="text/javascript" src="../jscript/jquery.jqplot.js"></script>
<script type="text/javascript" src="../jscript/jqplot.barRenderer.js"></script>
<script type="text/javascript" src="../jscript/jqplot.pieRenderer.js"></script>
<script type="text/javascript" src="../jscript/jqplot.categoryAxisRenderer.js"></script>
<script type="text/javascript" src="../jscript/jqplot.pointLabels.js"></script>
<link rel="stylesheet" type="text/css" href="../css/jquery.jqplot.css" />

  
</head>

<body>

<div class="main_frame">
 
  <div class="page_head_frame">
    <label class="label_page_head">Direct material stock report</label></div>
<div class="page_middle_frame">

<?php
//$page='sales_products.php';
//$reference_name='show';
//$reference=$show;
//$reference_name_2='cust_id';
//$reference_2=$cust_id;
//$reference_name_3='item_type';
//$reference_3=$item_type;
//include('../include/period_select_block_small.php');
?>
<form action="direct_material_stock_report.php" method="get">
<div class="page_inside_frame">&nbsp;Select  stock take period from:
  <select name="stock_date_select2" id="stock_date_select2" class="select100">
  <option value="<?php echo $date2 ?>" selected="selected"><?php echo date("d-m-Y", strtotime($date2)) ?></option>
  <?php $i=0; while ($i<$stock_dates_num) { ?>
  <option value="<?php echo mysql_result($stock_dates_result, $i, "date"); ?>"><?php echo date("d-m-Y", strtotime(mysql_result($stock_dates_result, $i, "date"))); ?></option>
  <?php $i++; } ?>
</select>
&nbsp;to:&nbsp;&nbsp;
<select name="stock_date_select" id="stock_date_select" class="select100">
<option value="<?php echo $date1 ?>" selected="selected"><?php echo date("d-m-Y", strtotime($date1)) ?></option>
<?php $i=0; while ($i<$stock_dates_num) { ?>
<option value="<?php echo mysql_result($stock_dates_result, $i, "date"); ?>"><?php echo date("d-m-Y", strtotime(mysql_result($stock_dates_result, $i, "date"))); ?></option>
<?php $i++; } ?>     
</select>
&nbsp;&nbsp;
<input type="submit" name="date_select_button" id="date_select_button" value="Select date" class="imagebutton">
</div>
</form>
<br />
<br />


<div class="page_inside_frame">
  <table width="686" id="table_display">
    <tr>
      <td width="125" align="center">Item</td>
      <td width="101" align="center">Riversand</td>
      <td width="106" align="center">Silica sand</td>
      <td width="106" align="center">Cement</td>
      <td width="101" align="center">Pigment</td>
      <td width="107" align="center">Oil</td>
    </tr>
    <tr>
      <td>Openings stock</td>
      <td align="right"><?php echo number_format($aggregates2a, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($aggregates2b, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($opening_cement, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($pigment2, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($oils2, 2, '.', ',') ?></td>
      </tr>
    <tr>
      <td>Received</td>
      <td align="right"><?php echo number_format($aggregates_a_received, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($aggregates_b_received, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($cement_received, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($pigment_received, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($oils_received, 2, '.', ',') ?></td>
      </tr>
    <tr>
      <td>Usage tiles</td>
      <td align="right"><?php echo number_format($total_aggregates_a_usage_tiles, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($total_aggregates_b_usage_tiles, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($total_cement_usage_tiles, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($total_pigment_usage_tiles, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($total_oils_usage_tiles, 2, '.', ',') ?></td>
      </tr>
    <tr>
      <td>Usage accessories</td>
      <td align="right"><?php echo number_format($total_aggregates_a_usage_acc, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($total_aggregates_b_usage_acc, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($total_cement_usage_acc, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($total_pigment_usage_acc, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($total_oils_usage_acc, 2, '.', ',') ?></td>
      </tr>
    <tr>
      <td>Calc. closing stock</td>
      <td align="right"><?php echo number_format($calc_aggregates1a, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($calc_aggregates1b, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($calc_cement, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($calc_pigment, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($calc_oils, 2, '.', ',') ?></td>
      </tr>
    <tr>
      <td>Closing Stock</td>
      <td align="right"><?php echo number_format($aggregates1a, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($aggregates1b, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($closing_cement, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($pigment1, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($oils1, 2, '.', ',') ?></td>
      </tr>
    <tr>
      <td>&nbsp;</td>
      <td align="right">&nbsp;</td>
      <td align="right">&nbsp;</td>
      <td align="right">&nbsp;</td>
      <td align="right">&nbsp;</td>
      <td align="right">&nbsp;</td>
    </tr>
    <tr>
      <td>Loss/gain</td>
      <td align="right"><?php echo number_format($dif_aggregates1a, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($dif_aggregates1b, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($dif_cement, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($dif_pigment, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($dif_oils, 2, '.', ',') ?></td>
    </tr>
    <tr>
      <td>Loss/gain %</td>
      <td align="right"><?php echo number_format($dif_aggregates1a_perc, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($dif_aggregates1b_perc, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($dif_cement_perc, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($dif_pigment_perc, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($dif_oils_perc, 2, '.', ',') ?></td>
    </tr>
    <tr>
      <td>Cost loss/gain P</td>
      <td align="right"><?php echo number_format($cost_dif_aggregates1a, 2, '.', ',') ?></td>
      <td align="right"><?php echo number_format($cost_dif_aggregates1b, 2, '.', ',') ?></td>
      <td align="right">&nbsp;</td>
      <td align="right">&nbsp;</td>
      <td align="right">&nbsp;</td>
    </tr>
  </table>
</div>

<div class="page_inside_frame" align="center">
<div id="chart7" style="height:200px;width:560px; float:right"></div>
<script>
$(document).ready(function(){
        var s1 = [<?php echo $dif_aggregates1a_perc ?>, <?php echo $dif_aggregates1b_perc ?>, <?php echo $dif_cement_perc ?>, <?php echo $dif_pigment_perc ?>, <?php echo $dif_oils_perc ?>];
		var ticks = ['River sand', 'Silica sand', 'Cement', 'Pigment', 'Oil'];
 
        plot7 = $.jqplot('chart7', [s1], {
            seriesDefaults:{
                renderer:$.jqplot.BarRenderer,
                rendererOptions: { fillToZero: true },
                    pointLabels: { show: true }
            },
            axes: {
                // yaxis: { autoscale: true },
                xaxis: {
                    renderer: $.jqplot.CategoryAxisRenderer,
                    ticks: ticks
                }
            }
        });
    });
</script>




</div>

 </div>
 </div>
 </div>
<?php include ('../navigation/side_bar.php'); ?>
</body>
<?php } else { ?>
<script> needToConfirm = false; </script>
<?php
    $_SESSION['denied_return'] = "store/store_main.php?date=";	?>
<body onLoad="accessdenied(); return false">
</body>
<?php }
?>

