chart prod1

chart prod1

<?php
 error_reporting(E_ALL);
 ini_set( 'display_errors','1');
 try{ $dsn = 'dblib:dbname=silrd;host=192.168.7.199\PRI';
 $user = 'tabula';
 $password = 'Manager1';
 $dbh = new PDO($dsn, $user, $password);
 } catch (PDOException $e) {
    echo "Failed to get DB handle: " . $e->getMessage() . "\n";
    exit;
 }
// ----------------------FETCH THE SERIAL POPULATION-----------------------------------------
 $select = "SELECT DISTINCT SERIAL.SERIAL,REVERSE(SERIALDES) +' : '+ CONVERT(VARCHAR,QUANT/100)";
 $from = " FROM SERIAL,SIL_SERNTESTS,SERNUMBERS";
 $where = " WHERE SIL_SERNTESTS.SERN = SERNUMBERS.SERN AND SERIAL.SERIAL = SERNUMBERS.SERIAL AND SIL_SERNTESTS.TEST <> 10 AND SERIAL.SERIAL > 0";
 $and = " AND SIL_SERNTESTS.UDATE >DATEDIFF(minute,'01-01-1988 00:00',getdate())-120 AND SIL_SERNTESTS.UDATE < DATEDIFF(minute,'01-01-1988 00:00',getdate());";
 $sql=$select.$from.$where.$and;
 $stmt = $dbh->prepare($sql);
 $stmt->execute();
 $res = $stmt->fetchAll();
 $serialsA = $categoriesA = array();
 $serials =  "(";
 $serials2 = "[";

 foreach($res as $line) {

	array_push($serialsA,$line[0]);
	$serials = $serials.$line[0].",";
	$serials2 = $serials2."'".$line[1]."',";
	$categoriesA[$line[0]]=$line[1];
	}
 $serials = rtrim($serials,",").")";
 $serials2 = rtrim($serials2,",")."]";
// echo "<p>".$serials2."</p>";
// echo "<p>".$serials."</p>";
// print_r($serialsA);
// echo "<p>".print_r($categoriesA)."</p>";
//--------------------------------------FETCH THE TESTS POPULATION----------------------------
 $select = "SELECT DISTINCT SIL_TESTS.TEST,TESTDES,SIL_TESTS.ORD";
 $from = " FROM SIL_SERNTESTS,SERNUMBERS,SIL_TESTS";
 $where = " WHERE SIL_SERNTESTS.SERN = SERNUMBERS.SERN AND SIL_TESTS.TEST = SIL_SERNTESTS.TEST AND SERNUMBERS.SERIAL IN".$serials." AND SIL_SERNTESTS.TEST <> 10";
 $order = " ORDER BY SIL_TESTS.ORD DESC";
 $sql= "{$select}{$from}{$where}{$order}";
// echo "<p>".$sql."</p>";

 $tests = "(";
 $testsA = $actionsA = array();


 $stmt = $dbh->prepare($sql);
 $stmt->execute();
 $res = $stmt->fetchAll();

 foreach($res as $line) {

	// print_r($line);
        array_push($testsA,$line[0]);
        $tests = $tests.$line[0].",";
        $actionsA[$line[0]]=$line[1];
	}
 $tests = rtrim($tests,",").")";
// echo "<p>".$tests."</p>";
// print_r($testsA);
//echo "<p>".print_r($actionsA)."</p>";


// -------------Construct the DataGrid-------------
 $dg = array();
 foreach ($testsA as $t) {
	foreach($serialsA as $s) {
		$dg[$t][$s] = array(0,0,0,0);
	}
 }
//print_r($dg);
//------------------POPULATE THE DATA GRID WITH ACTIONS COUNT----------------------------
 $select  = "SELECT SERNUMBERS.SERIAL,SIL_SERNTESTS.TEST,SIL_PARTTESTS.STD,SERIAL.QUANT,SUM(1) as COUNT";
 $from  = " FROM SIL_SERNTESTS,SERNUMBERS,SIL_PARTTESTS,SERIAL";
 $where = " WHERE SERNUMBERS.SERN = SIL_SERNTESTS.SERN AND SERNUMBERS.SERIAL IN".$serials." AND SIL_SERNTESTS.TEST IN".$tests." AND SIL_SERNTESTS.STATUS =1";
 $and = " AND SIL_PARTTESTS.PART = SERNUMBERS.PART AND SIL_PARTTESTS.TEST = SIL_SERNTESTS.TEST AND SERIAL.SERIAL = SERNUMBERS.SERIAL";
 $group = " GROUP BY SERNUMBERS.SERIAL,SIL_SERNTESTS.TEST,SIL_PARTTESTS.STD,SERIAL.QUANT";
 $sql= "{$select}{$from}{$where}{$and}{$group}";
// echo "<p>".$sql."</p>";

 $stmt = $dbh->prepare($sql);
 $stmt->execute();
 $res = $stmt->fetchAll();


 foreach($res as $line) {
//	if($line[4]< '1' ) $line[4]='null';
	$dg[$line[1]][$line[0]] = array($line[4],$line[2],0,$line[3]);

 }
 
// print_r($dg);
//----------------CALCULATING AVERAGE WORK TIME-------------------------------------------
 $select  = "SELECT SERNUMBERS.SERIAL,SIL_SERNTESTS.TEST,COUNT(DISTINCT SERNUMBERS.PART)";
 $from  = " FROM SIL_SERNTESTS,SERNUMBERS";
 $where = " WHERE SERNUMBERS.SERN = SIL_SERNTESTS.SERN AND SERNUMBERS.SERIAL IN".$serials." AND SIL_SERNTESTS.TEST IN".$tests." AND SIL_SERNTESTS.STATUS > 0";
// $and = " AND SIL_SERNTESTS.INTERVAL > 0";
 $group = " GROUP BY SERNUMBERS.SERIAL,SIL_SERNTESTS.TEST";
 $sql= "{$select}{$from}{$where}{$group}";
// echo "<p>".$sql."</p>";

 $stmt = $dbh->prepare($sql);
 $stmt->execute();
 $res = $stmt->fetchAll();


 foreach($res as $line) {
        $dg[$line[1]][$line[0]][2] = $line[2];

 }


//------------------CONSTRUCT THE SERIES FOR THE PRODUCTION STATE GRAPH------------------
 $series =  "series: [ ";
 $series2 =  "series: [ ";
 foreach($testsA as $test) {
	 $s= "{ name: '".$actionsA[$test]."',data: ";
	 $s1 = $t1 = "[";
//         echo "<p>".print_r($dg[$test],1)."</p>";

	 foreach($dg[$test] as $x) {
		if($x[0] < 1) {
			$s1=$s1."null,";
			} else {
				$s1 = $s1.number_format(round(($x[0]/$x[2]/(($x[3]/100)+0.01))*100)).",";
				}
//		$t1 = $t1.number_format(floor($x[1]($x[2]+0.01)/10),0,'.','').",";
//	echo $x;
	 }
	 $s1 = rtrim($s1,",")."]";
	 $t1 = rtrim($t1,",")."]";
	 $series=$series.$s.$s1."},";
         $series2=$series2.$s.$t1."},";
 }
$series = rtrim($series,",")."]";
$series2 = rtrim($series2,",")."]";
//echo "<p>".$series."</p>";
//echo "<p>".$series2."</p>";

 unset($dbh);
 unset($stmt);
?>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Silrd Production</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
<script type="text/javascript">

	function timedRefresh(timeoutPeriod) {
	 setTimeout("location.reload(true);",timeoutPeriod);
	}

 $(function () {
    var chart;
    $(document).ready(function() {
        chart = new Highcharts.Chart({
            chart: {
                renderTo: 'container',
                type: 'column',
	        events: {
           	  load: function() {
                  this.renderer.image('logo.jpg', 250, 50, 350,250)
                  .add();
	  	      }
	        }
            },
          credits: {
                enabled: false
            },
            title: {
                text: 'Current Production - Percentage Of Completion '
            },
            xAxis: {
                categories: <?php echo $serials2; ?>,
            labels: {
                style: {
                    fontSize: '14px',
	            fontWeight: 'bold'
                }
            }
           },
            yAxis: {
                min: 0,
                title: {
                    text: 'Percentage Of Completion'
                },

                stackLabels: {
                    enabled: false,
                    style: {
                        fontWeight: 'bold',
                        color: (Highcharts.theme && Highcharts.theme.textColor) || 'gray'
                    }
                }
            },
            legend: {
                align: 'right',
                x: -100,
                verticalAlign: 'top',
                y: 20,
                floating: true,
                backgroundColor: (Highcharts.theme && Highcharts.theme.legendBackgroundColorSolid) || 'white',
                borderColor: '#CCC',
                borderWidth: 1,
                shadow: false
            },
            tooltip: {
                formatter: function() {
                    return '<b>'+ this.series.name +': '+ this.y  +'%</b>';

                }
            },
            plotOptions: {
                column: {
                    stacking: 'normal',
                    dataLabels: {
			enabled: true,
                        color: (Highcharts.theme && Highcharts.theme.dataLabelsColor) || 'white',
                        style: {  fontWeight: 'bold' },
formatter: function() {
if(this.y==null) return '';
 return this.y + '%';
}

                    }
                }
            },
<?php echo $series; ?>
        });
    });
});

</script>
</head>
	<body onload="JavaScript:timedRefresh(60000);">
		 <script src="http://code.highcharts.com/highcharts.js"></script>
		 <script src="http://code.highcharts.com/modules/exporting.js"></script>
		 <div id="container" style="min-width: 400px; height: 400px; margin: 0 auto"></div>
		 <div style="min-width: 400px; height: 100px; margin: 0 auto"></div>
		 <div id="container3" style="min-width: 400px; height: 400px; margin: 0 auto"></div>
	</body>
</html>