---
# System prepended metadata

title: 智慧影像科專案-PeopleDirection
tags: [智慧影像科專案]

---

# 智慧影像科專案-PeopleDirection
###### tags: `智慧影像科專案`
## 操作過程
### 資料庫(**DbTest.java**)

#### 啟動DbMaker資料庫(Jserver Manager)
![](https://i.imgur.com/1LBKcx1.png)

#### 登入DbMaker資料庫(JDBATool)
![](https://i.imgur.com/Zv271lH.png)

#### 清除舊有表格People,以及創建新的表格People
:::success
執行```DbTest.java```
:::
 
![](https://i.imgur.com/YPwmmyU.png)


### Samba資料庫取資料(**App.java**)
:::success
執行```App.java```
:::
#### 若成功輸出則印出檔案名稱
![](https://i.imgur.com/ZOQp1K4.png)

#### 成功在兩個資料夾對傳資料
![](https://i.imgur.com/yjQ8j8Q.png)

### 分析本地端的Log檔案(**countperson.java**)


#### 原先資料庫表格是空的

![](https://i.imgur.com/hA0qNd0.png)

:::success
執行```countperson.java```
:::
#### 執行`countperson.java`後
程式每15分鐘會執行一遍
![](https://i.imgur.com/LrJv0In.png)

#### 資料庫重新整理後的畫面
![](https://i.imgur.com/aq0Nh8B.png)













## 程式碼

### `DbTest.java`
#### *根據要使用的功能去刪除註解*
```java=
package com.samba.PeopleDirection;

import java.lang.Class;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
//import java.sql.ResultSet;
import java.sql.SQLException;
//import java.sql.Statement;

public class DbTest {
	
    public static void main (String[] args) throws SQLException, InstantiationException, IllegalAccessException{
   	
    	String driver = "dbmaker.sql.JdbcOdbcDriver";
    	
    	//連結driver載入驅動程式
    	try {
    		Class.forName(driver).newInstance();
    	}
    	catch(ClassNotFoundException e) {
        	//System.out.println(e.getMessage());
    	}

        // 使用 try {} 可以讓 Connection 結束後自動執行 close() 以關閉資料庫。
        try (Connection conn = DriverManager.getConnection("jdbc:DBMaker://127.0.0.1:7777/DATA_DB","SYSADM","");) {
                
	        	//刪除表
				PreparedStatement Dt = conn.prepareStatement("DROP TABLE People;");
				Dt.execute();
        	
        	    //創建表
	        	PreparedStatement Ct = conn.prepareStatement
				("CREATE TABLE People( Datetime varchar(80) NOT NULL "
									+ ",Total integer NOT NULL );");
				Ct.execute();
				
				// 插入資料
        		/*PreparedStatement ps = conn.prepareStatement("INSERT INTO People (Datetime ,Type ,Direction ,Total) VALUES(? ,? ,? ,?);");
        		ps.setInt(1,20190731);
        		ps.setString(2,"person");
        		ps.setInt(3,1);
        		ps.setInt(4,10);*/
                //ps.execute();
        	
        	    // 插入資料
                //PreparedStatement ps = conn.prepareStatement("INSERT INTO users (name, mail) VALUES(?, ?);");
                //ps.execute();
                //刪除資料
	        	//PreparedStatement pd = conn.prepareStatement("DELETE FROM People  WHERE Total='person' ;");
	        	//pd.execute();
                
	        	//ps.setString(1, "Shin");
                //ps.setString(2, "my@shin.com");
                

                //ps = conn.prepareStatement("SELECT * FROM People;");
                // 查詢資料
                //ResultSet rs = ps.executeQuery();
                //while (rs.next()) {
                    //System.out.println("Query : " + rs.getString("Datetime") +  " , "+ rs.getString("Total"));
                //}         
        }
        
    }
}
```


### `App.java`
    
```java =
package com.samba.PeopleDirection;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.SQLException;
import java.io.File;
import java.util.*;
import org.json.JSONException;
import org.json.JSONObject;
import org.json.JSONArray;
import java.lang.Class;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
//import java.sql.ResultSet;

public class countperson {
	
	//設定排程時間
	public void ScheduleDelay() throws InstantiationException, IllegalAccessException, SQLException {
		Timer timer = new Timer();
		timer.schedule(new DateTask123() ,1000 ,900000);//第一次執行延遲1秒,每隔15分鐘就執行一次這個Function
		try {
			Thread.sleep(1000);	
				}
		catch(InterruptedException e) {	
		}
		//timer.cancel();
		System.out.println("End testScheduleDelay：" + new Date() + "\n");
	}
	
	//主程式
	public static void main(String[] args) throws InstantiationException, IllegalAccessException, SQLException  {
		countperson GL = new countperson();
		GL.ScheduleDelay();		
	}
}

//因為TimerTask需要透過繼承,因此另起一個Class給主程式繼承
class DateTask123 extends TimerTask  {	
	static DateTask123 DT = new DateTask123();
	
	//計算Log檔裡特定欄位的資料
	public static String PersonCounting(String documentTxt ,String documentJson ,String ac) {		
		// 讀取nameID.txt檔案中的NAMEID欄位（key）對應值（value）並存儲
			ArrayList<String> list = new ArrayList<String>();
			BufferedReader brname;
			try {
				brname = new BufferedReader(new FileReader("D:/SHIN/SYS/智慧影像科專案/BS_PeopleCounting/src/json/test/"+documentTxt));// 讀取NAMEID對應值
				String sname = null;
				while ((sname = brname.readLine()) != null) {
					list.add(sname);// 將對應value新增到連結串列儲存
				}
				brname.close();
			} catch (IOException e1) {
				e1.printStackTrace();
			}
			// 讀取原始json檔案並進行操作和輸出	
			try {
				BufferedReader br = new BufferedReader(new FileReader(
						"D:/SHIN/SYS/智慧影像科專案/BS_PeopleCounting/src/json/test/"+documentJson));// 讀取原始json檔案
				//BufferedWriter bw = new BufferedWriter(new FileWriter(
				//		"src/json/test/"+documentJson));// 輸出新的json檔案
				String s = null;
				int round=0;
				int[] sum = new int[15];
				while ((s = br.readLine()) != null) {
					try {
						
						JSONObject dataJson = new JSONObject(s);// 建立一個包含原始json串的json物件
						{
							JSONArray tag = dataJson.getJSONArray("tag");					
							for (int i = 0; i < tag.length(); i++) {
								JSONObject info = tag.getJSONObject(i);// 獲取tag陣列的第i個json物件
								double value = info.getDouble("direction");// 讀取info物件裡的direction欄位值					
								JSONArray type = info.getJSONArray("objectTypes");//讀取info物件裡的objectTypes陣列值
								String person = type.toString();// JSONArray 轉 String
								boolean status = person.contains("person");//判斷Array值
								if(status) {
									double pn = Math.ceil(value);//無條件進位value值 
									if(pn > 0) {
										for(int j=1 ; j<12 ; j++ ) {
											if(pn == j) {
												round++;
												sum[j]++;
						} } } } } }
							
					} catch (JSONException e) {
						e.printStackTrace();
					}
				}
				br.close();
				for(int k=1 ; k<12 ; k++ ) {
					//System.out.println("路徑方向為" +k +" : " +sum[k] +"人");
				}
				//轉換字型
				ac = Integer.toString(round);
				
			} catch (IOException e) {
				e.printStackTrace();
			}	
		return ac;	 	 
	}
	
	//連結資料庫(創建、刪除、插入資料、查詢)
	public static void DbConnect( String documentTxt ,int round ,int finish) throws SQLException, InstantiationException, IllegalAccessException {
		String driver = "dbmaker.sql.JdbcOdbcDriver";    	
    	//連結driver載入驅動程式
    	try {
    		Class.forName(driver).newInstance();
    	}
    	catch(ClassNotFoundException e) {
        	//System.out.println(e.getMessage());
    	}

        // 使用 try {} 可以讓 Connection 結束後自動執行 close() 以關閉資料庫。
        try (Connection conn = DriverManager.getConnection("jdbc:DBMaker://127.0.0.1:7777/DATA_DB","SYSADM","");) {
                
        
        	    //創建表
        		//PreparedStatement Ct = conn.prepareStatement
        				//("CREATE TABLE People( Datetime varchar(80) NOT NULL "
        				//					+ ",Total integer NOT NULL );");
        		//Ct.execute();
        		//刪除表
        		//PreparedStatement Dt = conn.prepareStatement("DROP TABLE People;");
        		//Dt.execute();
        		
        		
        		// 插入資料
        		PreparedStatement ps = conn.prepareStatement("INSERT INTO People (Datetime ,Total) VALUES(? ,?);");
        		ps.setString(1,documentTxt);
        		ps.setInt(2,round);
                ps.execute();
        	
        	    
                //PreparedStatement ps = conn.prepareStatement("INSERT INTO users (name, mail) VALUES(?, ?);");
                //ps.execute();
        		
                //刪除資料
	        	//PreparedStatement pd = conn.prepareStatement("DELETE FROM People  WHERE Type='person' ;");
	        	//pd.execute();
	        	
                ps = conn.prepareStatement("SELECT * FROM People;");
                // 查詢資料
                /*ResultSet rs = ps.executeQuery();
                if(finish != 0) {
	                while (rs.next()) {
	                    System.out.println("Query : " + rs.getString("Datetime") + " : " + rs.getString("Total"));
	                }   
	            }*/
        }	
	}
	
	//取得本地端資料夾內的資料、檔案數量
	public int getlist(File f) {
		int size = 0;
		File []flist = new File[] {};
		flist = f.listFiles();
		System.out.println(flist);
		size=flist.length;

		for (int i = 0; i < flist.length; i++) {
			if (flist[i].isDirectory()) {
			size = size + getlist(flist[i]);
			size--;
			}
		}
		//System.out.println(size);
		return size;
		
	}
	
	//此Class_DateTask1232的主程式
	public void testrun() throws InstantiationException, IllegalAccessException, SQLException {
		//////////////////////////////////////////////////
		//countperson GL = new countperson();
		int count = 0;
		int finish =0 ;
		int total=0,TotalTxt=0,round=0;		
		String ac = null ,documentTxt = null ,documentJson = null ;
		File f = new File("D:/SHIN/SYS/智慧影像科專案/BS_PeopleCounting/src/json/test/");
		ArrayList<String>fileList = new ArrayList<String>();
		String[] self = new String[] {};
		self=f.list();
		
		for (int r=0 ; r<DT.getlist(f) ; r++) {
			fileList.add(self[TotalTxt]);
			TotalTxt++;
		}

		for(int g=0;g<fileList.size();g++){	
		//將每份Txt結果寫進DBMaker
		documentTxt = (fileList.get(g));
		documentJson = (fileList.get(g)); 		 
		//System.out.println(fileList.get(g)); //印出資料夾內的檔名
		round = Integer.valueOf(PersonCounting(documentTxt ,documentJson ,ac));
		//System.out.println("此份Log : " +round +"人");
		total = total +round;
		//System.out.println("全部Log總人數 : "+total +"人");

		DbConnect(documentTxt,round,finish);
		count = count + round;
		}
		
		//Final
		documentTxt = "總人數 ";
		round = count;
		finish++;
		//最終結果統計寫進DBMaker
		DbConnect(documentTxt,round,finish);
	}
	
	//執行Timetask的Function
	@Override
	public void run() {
		// TODO Auto-generated method stub
			 System.out.println("Task 執行時間：" + new Date());
			 
				try {
					DT.testrun();
				} catch (InstantiationException | IllegalAccessException | SQLException e) {
					e.printStackTrace();
				}
	}
}



```


### `countperson.java`
``` java=
package com.samba.PeopleDirection;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.SQLException;
import java.io.File;
import java.util.*;
import org.json.JSONException;
import org.json.JSONObject;
import org.json.JSONArray;
import java.lang.Class;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
//import java.sql.ResultSet;

public class countperson {
	
	//設定排程時間
	public void ScheduleDelay() throws InstantiationException, IllegalAccessException, SQLException {
		Timer timer = new Timer();
		timer.schedule(new DateTask123() ,1000 ,900000);//第一次執行延遲1秒,每隔15分鐘就執行一次這個Function
		try {
			Thread.sleep(1000);	
				}
		catch(InterruptedException e) {	
		}
		//timer.cancel();
		System.out.println("End testScheduleDelay：" + new Date() + "\n");
	}
	
	//主程式
	public static void main(String[] args) throws InstantiationException, IllegalAccessException, SQLException  {
		countperson GL = new countperson();
		GL.ScheduleDelay();		
	}
}

//因為TimerTask需要透過繼承,因此另起一個Class給主程式繼承
class DateTask123 extends TimerTask  {	
	static DateTask123 DT = new DateTask123();
	
	//計算Log檔裡特定欄位的資料
	public static String PersonCounting(String documentTxt ,String documentJson ,String ac) {		
		// 讀取nameID.txt檔案中的NAMEID欄位（key）對應值（value）並存儲
			ArrayList<String> list = new ArrayList<String>();
			BufferedReader brname;
			try {
				brname = new BufferedReader(new FileReader("D:/SHIN/SYS/智慧影像科專案/BS_PeopleCounting/src/json/test/"+documentTxt));// 讀取NAMEID對應值
				String sname = null;
				while ((sname = brname.readLine()) != null) {
					list.add(sname);// 將對應value新增到連結串列儲存
				}
				brname.close();
			} catch (IOException e1) {
				e1.printStackTrace();
			}
			// 讀取原始json檔案並進行操作和輸出	
			try {
				BufferedReader br = new BufferedReader(new FileReader(
						"D:/SHIN/SYS/智慧影像科專案/BS_PeopleCounting/src/json/test/"+documentJson));// 讀取原始json檔案
				//BufferedWriter bw = new BufferedWriter(new FileWriter(
				//		"src/json/test/"+documentJson));// 輸出新的json檔案
				String s = null;
				int round=0;
				int[] sum = new int[15];
				while ((s = br.readLine()) != null) {
					try {
						
						JSONObject dataJson = new JSONObject(s);// 建立一個包含原始json串的json物件
						{
							JSONArray tag = dataJson.getJSONArray("tag");					
							for (int i = 0; i < tag.length(); i++) {
								JSONObject info = tag.getJSONObject(i);// 獲取tag陣列的第i個json物件
								double value = info.getDouble("direction");// 讀取info物件裡的direction欄位值					
								JSONArray type = info.getJSONArray("objectTypes");//讀取info物件裡的objectTypes陣列值
								String person = type.toString();// JSONArray 轉 String
								boolean status = person.contains("person");//判斷Array值
								if(status) {
									double pn = Math.ceil(value);//無條件進位value值 
									if(pn > 0) {
										for(int j=1 ; j<12 ; j++ ) {
											if(pn == j) {
												round++;
												sum[j]++;
						} } } } } }
							
					} catch (JSONException e) {
						e.printStackTrace();
					}
				}
				br.close();
				for(int k=1 ; k<12 ; k++ ) {
					//System.out.println("路徑方向為" +k +" : " +sum[k] +"人");
				}
				//轉換字型
				ac = Integer.toString(round);
				
			} catch (IOException e) {
				e.printStackTrace();
			}	
		return ac;	 	 
	}
	
	//連結資料庫(創建、刪除、插入資料、查詢)
	public static void DbConnect( String documentTxt ,int round ,int finish) throws SQLException, InstantiationException, IllegalAccessException {
		String driver = "dbmaker.sql.JdbcOdbcDriver";    	
    	//連結driver載入驅動程式
    	try {
    		Class.forName(driver).newInstance();
    	}
    	catch(ClassNotFoundException e) {
        	//System.out.println(e.getMessage());
    	}

        // 使用 try {} 可以讓 Connection 結束後自動執行 close() 以關閉資料庫。
        try (Connection conn = DriverManager.getConnection("jdbc:DBMaker://127.0.0.1:7777/DATA_DB","SYSADM","");) {
                
        
        	    //創建表
        		//PreparedStatement Ct = conn.prepareStatement
        				//("CREATE TABLE People( Datetime varchar(80) NOT NULL "
        				//					+ ",Total integer NOT NULL );");
        		//Ct.execute();
        		//刪除表
        		//PreparedStatement Dt = conn.prepareStatement("DROP TABLE People;");
        		//Dt.execute();
        		
        		
        		// 插入資料
        		PreparedStatement ps = conn.prepareStatement("INSERT INTO People (Datetime ,Total) VALUES(? ,?);");
        		ps.setString(1,documentTxt);
        		ps.setInt(2,round);
                ps.execute();
        	
        	    
                //PreparedStatement ps = conn.prepareStatement("INSERT INTO users (name, mail) VALUES(?, ?);");
                //ps.execute();
        		
                //刪除資料
	        	//PreparedStatement pd = conn.prepareStatement("DELETE FROM People  WHERE Type='person' ;");
	        	//pd.execute();
	        	
                ps = conn.prepareStatement("SELECT * FROM People;");
                // 查詢資料
                /*ResultSet rs = ps.executeQuery();
                if(finish != 0) {
	                while (rs.next()) {
	                    System.out.println("Query : " + rs.getString("Datetime") + " : " + rs.getString("Total"));
	                }   
	            }*/
        }	
	}
	
	//取得本地端資料夾內的資料、檔案數量
	public int getlist(File f) {
		int size = 0;
		File []flist = new File[] {};
		flist = f.listFiles();
		size=flist.length;

		for (int i = 0; i < flist.length; i++) {
			if (flist[i].isDirectory()) {
			size = size + getlist(flist[i]);
			size--;
			}
		}
		//System.out.println(size);
		return size;
		
	}
	
	//此Class_DateTask1232的主程式
	public void testrun() throws InstantiationException, IllegalAccessException, SQLException {
		//////////////////////////////////////////////////
		//countperson GL = new countperson();
		int count = 0;
		int finish =0 ;
		int total=0,TotalTxt=0,round=0;		
		String ac = null ,documentTxt = null ,documentJson = null ;
		File f = new File("D:/SHIN/SYS/智慧影像科專案/BS_PeopleCounting/src/json/test/");
		ArrayList<String>fileList = new ArrayList<String>();
		String[] self = new String[] {};
		self=f.list();
		
		for (int r=0 ; r<DT.getlist(f) ; r++) {
			fileList.add(self[TotalTxt]);
			TotalTxt++;
		}

		for(int g=0;g<fileList.size();g++){	
		//將每份Txt結果寫進DBMaker
		documentTxt = (fileList.get(g));
		documentJson = (fileList.get(g)); 		 
		//System.out.println(fileList.get(g)); //印出資料夾內的檔名
		round = Integer.valueOf(PersonCounting(documentTxt ,documentJson ,ac));
		//System.out.println("此份Log : " +round +"人");
		total = total +round;
		//System.out.println("全部Log總人數 : "+total +"人");

		DbConnect(documentTxt,round,finish);
		count = count + round;
		}
		
		//Final
		documentTxt = "總人數 ";
		round = count;
		finish++;
		//最終結果統計寫進DBMaker
		DbConnect(documentTxt,round,finish);
	}
	
	//執行Timetask的Function
	@Override
	public void run() {
		// TODO Auto-generated method stub
			 System.out.println("Task 執行時間：" + new Date());
			 
				try {
					DT.testrun();
				} catch (InstantiationException | IllegalAccessException | SQLException e) {
					e.printStackTrace();
				}
	}
}

```




