멀티프로젝트 01

2. 새로운 폴더( drawgraph)를 만들고 하단에 js css images 폴더를 만든다.

test.html을 다음과 같이 만들어 보자. media 서버에 올리고 테스트하여본다.

<!DOCTYPE html>

<html>

<head>

2 <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.0/jquery.min.js"></script>

3 <script src="//cdnjs.cloudflare.com/ajax/libs/raphael/2.1.0/raphael-min.js"></script>

4 <script src="//cdnjs.cloudflare.com/ajax/libs/morris.js/0.5.1/morris.min.js"></script>

</head>

<body>

new Morris.Line({

// ID of the element in which to draw the chart.

element: 'myfirstchart',

// Chart data records -- each entry in this array corresponds to a point on

// the chart.

data: [

{ year: '2008', value: 20 },

{ year: '2009', value: 10 },

{ year: '2010', value: 5 },

{ year: '2011', value: 5 },

{ year: '2012', value: 20 }

// The name of the data record attribute that contains x-values.

xkey: 'year',

// A list of names of data record attributes that contain y-values.

ykeys: ['value'],

// Labels for the ykeys -- will be displayed when you hover over the

// chart.

labels: ['Value']

});

</script>

</body>

</html>

3. php 에서 postproj.sqlite DB 파일을 읽고 json으로 보내주는 코드를 작성해 본다.

read_all.php를 다음과 같이 작성한다. media 서버에 올리고 테스트하여본다.

<?php

// PHP에 SQLite3 확장 모듈이 설치되어 있어야 합니다. 서버에서 SQLite3 모듈이 활성화되어 있는지 확인하세요.

// sudo apt update

// sudo apt install php-sqlite3

// sudo systemctl restart apache2

echo "{";

echo " 'pilot_operation':";

echo " [ ";

printPCSinfo();

echo " ]";

echo "}";

return;

////////////////////////////////////////////////////////////////////////////////////////////////////////

function printPCSinfo()

{

$db = new SQLite3('postproj.sqlite');

$sql="select * from pilot_operation";

$results = $db->query($sql);

while($row = $results->fetchArray(SQLITE3_ASSOC) ){

$a1 = $row['a1'];

$a2 = $row['a2'];

$a3 = $row['a3'];

$a4 = $row['a4'];

$a5 = $row['a5'];

$b1 = $row['b1'];

$b2 = $row['b2'];

$b3 = $row['b3'];

$c1 = $row['c1'];

$c2 = $row['c2'];

$c3 = $row['c3'];

$c4 = $row['c4'];

$c5 = $row['c5'];

$c6 = $row['c6'];

$c7 = $row['c7'];

$c8 = $row['c8'];

$c9 = $row['c9'];

$d1 = $row['d1'];

$d2 = $row['d2'];

$d3 = $row['d3'];

$d4 = $row['d4'];

$d5 = $row['d5'];

$d6 = $row['d6'];

$d7 = $row['d7'];

echo "{ 'a1':'$a1','a2':'$a2','a3':'$a3','a4':'$a4','a5':'$a5','b1':'$b1','b2':'$b2','b3':'$b3','c1':'$c1','c2':'$c2','c3':'$c3','c4':'$c4','c5':'$c5','c6':'$c6','c7':'$c7','c8':'$c8','c9':'$c9' ,'d1':'$d1','d2':'$d2','d3':'$d3','d4':'$d4','d5':'$d5','d6':'$d6','d7':'$d7'},";

}

CREATE TABLE "pilot_operation" ("a1" INTEGER DEFAULT (0) ,"a2" TEXT DEFAULT (null) ,"a3" TEXT DEFAULT (null) ,"a4" TEXT DEFAULT (null) ,"a5" TEXT DEFAULT (null) ,"b1" TEXT DEFAULT (null) ,"b2" TEXT DEFAULT (null) ,"b3" FLOAT DEFAULT (0) ,"c1" TEXT DEFAULT (null) ,"c2" TEXT DEFAULT (null) ,"c3" TEXT DEFAULT (null) ,"c4" TEXT DEFAULT (null) ,"c5" TEXT DEFAULT (null) ,"c6" TEXT DEFAULT (null) ,"c7" TEXT DEFAULT (null) ,"c8" INTEGER DEFAULT (0) ,"c9" INTEGER DEFAULT (0) ,"d1" INTEGER DEFAULT (0) ,"d2" INTEGER DEFAULT (0) ,"d3" INTEGER DEFAULT (0) ,"d4" FLOAT DEFAULT (0) ,"d5" TEXT DEFAULT (null) ,"d6" TEXT DEFAULT (null) ,"d7" TEXT DEFAULT (null) )

,' ',$row['a2'],' ',$row['a3'],' ',$row['a4'],' ',$row['a5'],

4. html에서 서버로 부터 데이터를 받아서 그래프로 그려 볼 것이다. JQuery로 작성하였지만 react에서도 쉽게 적용할 수 있을 것이다.

index.html을 다음과 같이 만들어 보자. images 폴더에 다음 이미지 mypage_nav_bg.png 를 복사한다.

<!DOCTYPE html>

<html>

<head>

</head>

<body>

</div>

<ul>

<li><a href="#" onclick="tab1()">기본데이터</a></li>

<li><a href="#" onclick="tab4()">일주행거리</a></li>

<li><a href="#" onclick="tab5()">년주행거리</a></li>

</ul>

</div>

</div>

</div>

</body>

</html>

html {

font-family: "Helvetica Neue", "Arial", sans-serif;

font-size: 16px;

line-height: 1.5;

}

body {

background-color: #ffffff;

border: 0;

margin: 0;

padding: 0;

}

.mypage-header {

width: 100%;

height:100%;

background-color:black;

margin-left: auto;

margin-right: auto;

color:white;

text-align:left;

background-image: url('../images/mypage_nav_bg.png');

}

.mypage-header h1 {

height: 80px;

margin: auto;

font-size: 20px;

font-weight: normal;

line-height: 80px;

letter-spacing: 0.1em;

//padding-left: 10px;

}

.mypage-header h1:hover{

color: #ff0000;

}

.mypage-navtop

{

width:100%;

height:40px;

background-color:#FFFFFF;

}

.mypage-nav

{

width:100%;

height:40px;

background-color:purple;

margin-left: auto;

margin-right: auto;

}

.mypage-nav ul {

float: left;

width: 100%;

padding: 0;

margin :0;

list-style-type: none;

}

.mypage-nav ul li {display: inline;}

.mypage-nav ul li a {

float: left;

width: 6em;

text-decoration: none;

color: white;

background-color: purple;

padding: 0.5em 0.8em;

border-right: 1px solid white;

border-left: 1px solid white;

}

.mypage-nav ul li a:hover {background-color: #ff3300;}

.mypage-section {

width: 100%;

}

var title=["일련번호","우정청명","총괄국명","차량(등록)관리관서명","차량운용관서구분","차종","차형","내용연수(년)","구매일자","차량용도","차명(장비명)",

"차량번호","차대번호","사용연료","제조회사","배기량(CC)","적재량(Kg)","총주행거리","연간주행거리","월간주행거리","일평균주행거리(연간270일기준)",

"차고지(우편번호)","차고지(기본주소)","차고지(상세주소)"];

var arrayTupleData=new Array();

function TupleData(a1,a2,a3,a4,a5,b1,b2,b3,c1,c2,c3,c4,c5,c6,c7,c8,c9,d1,d2,d3,d4,d5,d6,d7)

{

this.a1=a1; this.a2=a2; this.a3=a3; this.a4=a4; this.a5=a5;

this.b1=b1; this.b2=b2; this.b3=b3;

this.c1=c1; this.c2=c2; this.c3=c3; this.c4=c4; this.c5=c5; this.c6=c6; this.c7=c7; this.c8=c8; this.c9=c9;

this.d1=d1; this.d2=d2; this.d3=d3; this.d4=d4; this.d5=d5; this.d6=d6; this.d7=d7;

this.arrayData=[a1,a2,a3,a4,a5,b1,b2,b3,c1,c2,c3,c4,c5,c6,c7,c8,c9,d1,d2,d3,d4,d5,d6,d7];

}

$(document).ready(function(){

$.get("read_all.php",

function(responseTxt, statusTxt, xhr)

{

console.log(responseTxt);

if(statusTxt == "success")

{

console.log(responseTxt);

getDataFromJason(responseTxt);

drawtable1();//initial drawing table

}

if(statusTxt == "error")

{

alert("Error: " + xhr.status + ": " + xhr.statusText);

}

});//GET

$("button").click(function()

{ console.log("button click");

});//BUTTON

});//READY

function getDataFromJason(jsonStr)

{

var jsonObj = eval ("(" + jsonStr + ")");

var a1,a2,a3,a4,a5,b1,b2,b3,c1,c2,c3,c4,c5,c6,c7,c8,c9,d1,d2,d3,d4,d5,d6,d7;

if( typeof jsonObj == "object" )

{

for (var key in jsonObj)

{

if(key=="pilot_operation")

{

var arrayObj=jsonObj[key];

for (var i=0;i<arrayObj.length;i++)

{

var obj=arrayObj[i];

for (var subkey in obj)

{

if(subkey=="a1") a1=obj[subkey];

if(subkey=="a2") a2=obj[subkey];

if(subkey=="a3") a3=obj[subkey];

if(subkey=="a4") a4=obj[subkey];

if(subkey=="a5") a5=obj[subkey];

if(subkey=="b1") b1=obj[subkey];

if(subkey=="b2") b2=obj[subkey];

if(subkey=="b3") b3=obj[subkey];

if(subkey=="c1") c1=obj[subkey];

if(subkey=="c2") c2=obj[subkey];

if(subkey=="c3") c3=obj[subkey];

if(subkey=="c4") c4=obj[subkey];

if(subkey=="c5") c5=obj[subkey];

if(subkey=="c6") c6=obj[subkey];

if(subkey=="c7") c7=obj[subkey];

if(subkey=="c8") c8=obj[subkey];

if(subkey=="c9") c9=obj[subkey];

if(subkey=="d1") d1=obj[subkey];

if(subkey=="d2") d2=obj[subkey];

if(subkey=="d3") d3=obj[subkey];

if(subkey=="d4") d4=obj[subkey];

if(subkey=="d5") d5=obj[subkey];

if(subkey=="d6") d6=obj[subkey];

if(subkey=="d7") d7=obj[subkey];

}// for (var subkey

arrayTupleData.push(new TupleData(a1,a2,a3,a4,a5,b1,b2,b3,c1,c2,c3,c4,c5,c6,c7,c8,c9,d1,d2,d3,d4,d5,d6,d7));

//document.write(a1+" "+a2+" "+a3+" "+a4+" "+a5+" ");

//document.write(b1+" "+b2+" "+b3);

//document.write(c1+" "+c2+" "+c3+" "+c4+" "+c5+" "+c6+" "+c7+" "+c8+" "+c9+" ");

//document.write(d1+" "+d2+" "+d3+" "+d4+" "+d5+" "+d6+" "+d7+"<br>");

}//for (var i=0

}//if(key=="pilot_operation")

}//for (var key in jsonObj)

}// if( typeof jsonObj

}

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////

function drawtable1()

{//draw raw data table

console.log("drawtable1");

var elem0 = document.getElementById("div1");

if(elem0.hasChildNodes())

{

console.log("div1 hasChildNodes elem0.childNodes.length="+elem0.childNodes.length);

for(i=elem0.childNodes.length; i>=0; i--)

{

//remove

var childObj= elem0.childNodes[i];

if(childObj) elem0.removeChild(childObj);//기존 data 무조건 없앤다.

}

elem0.style.height="auto";

var mytable=document.createElement('table');

mytable.width = "100%";

mytable.setAttribute("align","center");

//mytable.style.position='absolute';

//mytable.style.left= "0px";//"150px"

//mytable.style.top= "0px";//"120px"

mytable.style.border ="0px";// "1px solid #000"

var mystr="_";

var nameheader="table01_";

var nrCols=24

var nrRows=arrayTupleData.length;

console.log(" nrRows="+ nrRows);

var mybody=document.createElement('tbody');

// mybody.style.border = "1px solid #000"

var row, cell;

for(var i=0;i<nrRows;i++)

{

row=document.createElement('tr');

row.style.border ="0px";// "1px solid #000";

row.style.height="25px";

for(var j=0;j<nrCols;j++)

{

var idStr=formatTwoNumberLength(i,j,3);

cell=document.createElement('td');

cell.style.border = "0px";//"1px solid #000";

cell.style.textAlign="center";

if(i==0)

{

cell.innerHTML= '<font size="2"><b>'+title[j]+'</b></font>';

}

else

{//mySelectedHangerInfoIndex hangerInfo

var p= arrayTupleData[i];

cell.innerHTML= '<font size="2">'+ p.arrayData[j]+ '</font>';

}

row.appendChild(cell);

}

mybody.appendChild(row);

}

mytable.appendChild(mybody);

////////myTablelayer.appendChild(mytable);

elem0.appendChild(mytable);

}

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////

function drawtable2()

{//draw raw data table

console.log("drawtable2");

var elem0 = document.getElementById("div1");

if(elem0.hasChildNodes())

{

console.log("div1 hasChildNodes elem0.childNodes.length="+elem0.childNodes.length);

for(i=elem0.childNodes.length; i>=0; i--)

{

//remove

var childObj= elem0.childNodes[i];

if(childObj) elem0.removeChild(childObj);//기존 data 무조건 없앤다.

}

//$('div1h2').html('차량연식통계');

elem0.style.height="auto";

drawCarProductYear("all",elem0);

drawCarProductYear("서울청",elem0);

drawCarProductYear("경인청",elem0);

drawCarProductYear("부산청",elem0);

drawCarProductYear("충청청",elem0);

drawCarProductYear("전남청",elem0);

drawCarProductYear("경북청",elem0);

drawCarProductYear("전북청",elem0);

drawCarProductYear("강원청",elem0);

drawCarProductYear("제주청",elem0);

return;

}

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////

function drawCarProductYear(filterName,parentLayer)

{

//전국의 차량의 연수 통계

var nrRows=arrayTupleData.length;

var carYearOld=[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0];//초기화

for(var i=0;i<nrRows;i++)

{

var p= arrayTupleData[i];

var str= p.arrayData[7];//차량연수 1.7년 ==> 1년으로 계산

var num= parseInt(str);

if(filterName=="all" || filterName== p.arrayData[1])

{

console.log("filterName="+filterName+" no="+ p.arrayData[0]+" year="+num+" origin_value="+str);

if(num>=0 && num<=16 ) carYearOld[num]= carYearOld[num]+1;

}

var graphLayer= getNewLayer(1300, 500, filterName,filterName);

parentLayer.appendChild(graphLayer);

new Morris.Line({

// ID of the element in which to draw the chart.

element: filterName,//<<-- layer id name

// Chart data records -- each entry in this array corresponds to a point on

// the chart.

data: [

{ year: '2015', value: carYearOld[0] },

{ year: '2014', value: carYearOld[1] },

{ year: '2013', value: carYearOld[2] },

{ year: '2012', value: carYearOld[3] },

{ year: '2011', value: carYearOld[4] },

{ year: '2010', value: carYearOld[5] },

{ year: '2009', value: carYearOld[6] },

{ year: '2008', value: carYearOld[7] },

{ year: '2007', value: carYearOld[8] },

{ year: '2006', value: carYearOld[9] },

{ year: '2005', value: carYearOld[10] },

{ year: '2004', value: carYearOld[11] },

{ year: '2003', value: carYearOld[12] },

{ year: '2002', value: carYearOld[13] },

{ year: '2001', value: carYearOld[14] },

{ year: '2000', value: carYearOld[15] },

// The name of the data record attribute that contains x-values.

xkey: 'year',

// A list of names of data record attributes that contain y-values.

ykeys: ['value'],

// Labels for the ykeys -- will be displayed when you hover over the

// chart.

labels: ['Value']

});

}

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////

function drawtable3()

{

console.log("drawtable3");

var elem0 = document.getElementById("div1");

if(elem0.hasChildNodes())

{

console.log("div1 hasChildNodes elem0.childNodes.length="+elem0.childNodes.length);

for(i=elem0.childNodes.length; i>=0; i--)

{

//remove

var childObj= elem0.childNodes[i];

if(childObj) elem0.removeChild(childObj);//기존 data 무조건 없앤다.

}

elem0.style.height="auto";

var graphLayer= getNewLayer(1300, 500, "fuel","차량연료");

elem0.appendChild(graphLayer);

//////////////////////////////////////////////

var nrRows=arrayTupleData.length;

var fuelData1=[0,0,0,0,0,0,0,0,0,0];//경유

var fuelData2=[0,0,0,0,0,0,0,0,0,0];//휘발유

var fuelData3=[0,0,0,0,0,0,0,0,0,0];//LPG

var fuelData4=[0,0,0,0,0,0,0,0,0,0];//하이브리

for(var i=0;i<nrRows;i++)

{

var p= arrayTupleData[i];

var str1= p.arrayData[1];//차량연료

var str2= p.arrayData[13];//차량연료

var index=0;

if(str1=="서울청") index=1;

else if(str1=="경인청") index=2;

else if(str1=="부산청") index=3;

else if(str1=="충청청") index=4;

else if(str1=="전남청") index=5;

else if(str1=="경북청") index=6;

else if(str1=="전북청") index=7;

else if(str1=="강원청") index=8;

else if(str1=="제주청") index=9;

else continue;

if(str2=="경유") { fuelData1[index]=fuelData1[index]+1; fuelData1[0]=fuelData1[0]+1; }

else if(str2=="휘발유") { fuelData2[index]=fuelData2[index]+1; fuelData2[0]=fuelData2[0]+1; }

else if(str2=="LPG") { fuelData3[index]=fuelData3[index]+1; fuelData3[0]=fuelData3[0]+1; }

else if(str2=="하이브리드"){ fuelData4[index]=fuelData4[index]+1; fuelData4[0]=fuelData4[0]+1; }

}

/////////////////////////////////////////////

Morris.Bar({

element: 'fuel',

data: [

{ y: '전국', a: fuelData1[0], b: fuelData2[0], c: fuelData3[0], d: fuelData4[0]},

xkey: 'y',

ykeys: ['a', 'b', 'c', 'd'],

labels: ['경유', '휘발유','LPG','Hyprid']

});

Morris.Bar({

element: 'fuel',

data: [

{ y: '서울청', a: fuelData1[1], b: fuelData2[1], c: fuelData3[1], d: fuelData4[1]},

{ y: '경인청', a: fuelData1[2], b: fuelData2[2], c: fuelData3[2], d: fuelData4[2]},

{ y: '부산청', a: fuelData1[3], b: fuelData2[3], c: fuelData3[3], d: fuelData4[3]},

{ y: '충청청', a: fuelData1[4], b: fuelData2[4], c: fuelData3[4], d: fuelData4[4]},

{ y: '전남청', a: fuelData1[5], b: fuelData2[5], c: fuelData3[5], d: fuelData4[5]},

{ y: '경북청', a: fuelData1[6], b: fuelData2[6], c: fuelData3[6], d: fuelData4[6]},

{ y: '전북청', a: fuelData1[7], b: fuelData2[7], c: fuelData3[7], d: fuelData4[7]},

{ y: '강원청', a: fuelData1[8], b: fuelData2[8], c: fuelData3[8], d: fuelData4[8]},

{ y: '제주청', a: fuelData1[9], b: fuelData2[9], c: fuelData3[9], d: fuelData4[9]}

xkey: 'y',

ykeys: ['a', 'b', 'c', 'd'],

labels: ['경유', '휘발유','LPG','Hyprid']

});

return;

}

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////

function drawtable4()

{

console.log("drawtable4 xxx");

var elem0 = document.getElementById("div1");

if(elem0.hasChildNodes())

{

console.log("div1 hasChildNodes elem0.childNodes.length="+elem0.childNodes.length);

for(i=elem0.childNodes.length; i>=0; i--)

{

//remove

var childObj= elem0.childNodes[i];

if(childObj) elem0.removeChild(childObj);//기존 data 무조건 없앤다.

}

//////////////////////////////////////////////

var nrRows=arrayTupleData.length;

var driveDistance=[0,0,0,0,0,0,0,0,0,0];//

var dataCount= [0,0,0,0,0,0,0,0,0,0];//

for(var i=0;i<nrRows;i++)

{

var p= arrayTupleData[i];

var str1= p.arrayData[1];

var str2= p.arrayData[20];//하루평균주행거리

if(str1=="서울청") { driveDistance[1]=driveDistance[1]+parseFloat(str2); dataCount[1]=dataCount[1]+1;}

else if(str1=="경인청") { driveDistance[2]=driveDistance[2]+parseFloat(str2); dataCount[2]=dataCount[2]+1;}

else if(str1=="부산청") { driveDistance[3]=driveDistance[3]+parseFloat(str2); dataCount[3]=dataCount[3]+1;}

else if(str1=="충청청") { driveDistance[4]=driveDistance[4]+parseFloat(str2); dataCount[4]=dataCount[4]+1;}

else if(str1=="전남청") { driveDistance[5]=driveDistance[5]+parseFloat(str2); dataCount[5]=dataCount[5]+1;}

else if(str1=="경북청") { driveDistance[6]=driveDistance[6]+parseFloat(str2); dataCount[6]=dataCount[6]+1;}

else if(str1=="전북청") { driveDistance[7]=driveDistance[7]+parseFloat(str2); dataCount[7]=dataCount[7]+1;}

else if(str1=="강원청") { driveDistance[8]=driveDistance[8]+parseFloat(str2); dataCount[8]=dataCount[8]+1;}

else if(str1=="제주청") { driveDistance[9]=driveDistance[9]+parseFloat(str2); dataCount[9]=dataCount[9]+1;}

driveDistance[0]=driveDistance[0]+parseFloat(str2);

}

if(nrRows>0) driveDistance[0]=driveDistance[0]/nrRows;

else driveDistance[0]=0;

for(var i=1;i<=9;i++)

{

if(dataCount[i]>0) driveDistance[i]=driveDistance[i]/dataCount[i];//mean value

else driveDistance[i]=0;

}

/////////////////////////////////////////////

elem0.style.height="auto";

var graphLayer= getNewLayer(1300, 500, "driveDistance0","하루평균주행거리(km)");

elem0.appendChild(graphLayer);

Morris.Bar({

element: 'driveDistance0',

data: [

{ y: '전국평균', a: driveDistance[0]},

xkey: 'y',

ykeys: ['a'],

labels: ['일평균주행거리(km)']

});

elem0.style.height="auto";

var graphLayer= getNewLayer(1300, 500, "driveDistance","하루평균주행거리(km)");

elem0.appendChild(graphLayer);

Morris.Bar({

element: 'driveDistance',

data: [

{ y: '서울청', a: driveDistance[1]},

{ y: '경인청', a: driveDistance[2]},

{ y: '부산청', a: driveDistance[3]},

{ y: '충청청', a: driveDistance[4]},

{ y: '전남청', a: driveDistance[5]},

{ y: '경북청', a: driveDistance[6]},

{ y: '전북청', a: driveDistance[7]},

{ y: '강원청', a: driveDistance[8]},

{ y: '제주청', a: driveDistance[9]},

xkey: 'y',

ykeys: ['a'],

labels: ['일평균주행거리(km)']

});

return;

}

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////

function drawtable5()

{

console.log("drawtable5");

var elem0 = document.getElementById("div1");

if(elem0.hasChildNodes())

{

console.log("div1 hasChildNodes elem0.childNodes.length="+elem0.childNodes.length);

for(i=elem0.childNodes.length; i>=0; i--)

{

//remove

var childObj= elem0.childNodes[i];

if(childObj) elem0.removeChild(childObj);//기존 data 무조건 없앤다.

}

//////////////////////////////////////////////

var nrRows=arrayTupleData.length;

var driveDistance=[0,0,0,0,0,0,0,0,0,0];//

var dataCount= [0,0,0,0,0,0,0,0,0,0];//

for(var i=0;i<nrRows;i++)

{

var p= arrayTupleData[i];

var str1= p.arrayData[1];

var str2= p.arrayData[18];//년평균주행거리