package com.DocSystem.test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.DocSystem.common.Log;
import com.DocSystem.controller.BaseController;
import com.DocSystem.entity.Doc;
import com.DocSystem.entity.DocAuth;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

class DBTest extends BaseController{
	
 // MySQL 8.0 以下版本 - JDBC 驱动名及数据库 URL
 static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; 
 //static final String DB_URL = "jdbc:mysql://localhost:3306/RUNOOB";
 static final String DB_URL = "jdbc:mysql://localhost:3306/docsystem?zeroDateTimeBehavior=convertToNull&characterEncoding=utf8";
 
 // MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL
 //static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver"; 
 //static final String DB_URL = "jdbc:mysql://localhost:3306/RUNOOB?useSSL=false&serverTimezone=UTC";
 
 
 // 数据库的用户名与密码,需要根据自己的设置
 static final String USER = "root";
 static final String PASS = "";
 
 public static void main(String[] args) {
 
 	List<DocAuth> docAuthList = queryDocAuth();
 	
 	for(int i=0; i<docAuthList.size(); i++)
 	{
 		DocAuth docAuth = docAuthList.get(i);
 		Doc doc = getDocInfo(docAuth.getReposId(), docAuth.getDocId());
 		if(doc != null)
 		{
 			docAuth.setDocPath(doc.getPath());
 			docAuth.setDocName(doc.getName());
 		}
 	}
		Log.printObject("main() docAuthList:", docAuthList);
		writeToJsonFile(docAuthList,"docAuthList.json");
		
		
		String s = readJsonFile("docAuthList.json");
		JSONObject jobj = JSON.parseObject(s);
 JSONArray list = jobj.getJSONArray("docAuthList");

 for (int i = 0 ; i < list.size();i++){
 JSONObject obj = (JSONObject)list.get(i);
 DocAuth docAuth = new DocAuth();
 docAuth.setDocId( Long.parseLong(obj.get("docId").toString()));
 docAuth.setDocName( (String)obj.get("docName"));
 System.out.println(" " + docAuth.getDocId() + " " + docAuth.getDocName());
 }
 }

	private static boolean writeToJsonFile(List<DocAuth> docAuthList, String filePath) {
		
		String content = JSON.toJSONStringWithDateFormat(docAuthList, "yyy-MM-dd HH:mm:ss");
		if(content == null)
		{
			System.out.println("writeToJsonFile() content is null");
			return false;
		}
		
		content = "{docAuthList:" + content + "}";
			
		FileOutputStream out = null;
		try {
			out = new FileOutputStream(filePath);
		} catch (FileNotFoundException e) {
			System.out.println("writeToJsonFile() new FileOutputStream failed");
			e.printStackTrace();
			return false;
		}
		try {
			byte[] buff = content.getBytes();
			out.write(buff, 0, buff.length);
			//关闭输出流
			out.close();
		} catch (IOException e) {
			System.out.println("writeToJsonFile() out.write exception");
			e.printStackTrace();
			return false;
		}		
		return true;
	}

	private static List<DocAuth> queryDocAuth() {
		
		List<DocAuth> docAuthList = new ArrayList<DocAuth>();
		
 Connection conn = null;
 Statement stmt = null;
 try{
 // 注册 JDBC 驱动
 Class.forName(JDBC_DRIVER);
 
 // 打开链接
 //System.out.println("连接数据库...");
 conn = (Connection) DriverManager.getConnection(DB_URL,USER,PASS);
 
 // 执行查询
 //System.out.println(" 实例化Statement对象...");
 stmt = (Statement) conn.createStatement();
 String sql;
 
 sql = "select * from doc_auth";
 ResultSet rs = stmt.executeQuery(sql);
 
 // 展开结果集数据库
 while(rs.next()){ 
 DocAuth docAuth = new DocAuth();
 docAuth.setId(rs.getInt("ID"));
 docAuth.setReposId(rs.getInt("REPOS_ID"));
 docAuth.setDocId(rs.getLong("DOC_ID"));
 docAuth.setType(rs.getInt("TYPE"));
 docAuth.setPriority(rs.getInt("PRIORITY"));
 docAuth.setUserId(rs.getInt("USER_ID"));
 docAuth.setGroupId(rs.getInt("GROUP_ID"));
 docAuth.setIsAdmin(rs.getInt("IS_ADMIN"));
 docAuth.setAccess(rs.getInt("ACCESS"));
 docAuth.setEditEn(rs.getInt("EDIT_EN"));
 docAuth.setAddEn(rs.getInt("ADD_EN"));
 docAuth.setDeleteEn(rs.getInt("DELETE_EN"));
 docAuth.setHeritable(rs.getInt("HERITABLE"));
 docAuth.setDocPath(rs.getString("DOC_PATH"));
 docAuth.setDocName(rs.getString("DOC_NAME"));
 
 Log.printObject("queryDocAuth() docAuth: ", docAuth);
 docAuthList.add(docAuth);
 }
 	
 // 完成后关闭
 rs.close();
 stmt.close();
 conn.close();
 return docAuthList;
 }catch(SQLException se){
 // 处理 JDBC 错误
 se.printStackTrace();
 }catch(Exception e){
 // 处理 Class.forName 错误
 e.printStackTrace();
 }finally{
 // 关闭资源
 try{
 if(stmt!=null) stmt.close();
 }catch(SQLException se2){
 }// 什么都不做
 try{
 if(conn!=null) conn.close();
 }catch(SQLException se){
 se.printStackTrace();
 }
 }
		return null;
	}

	private static Doc getDocInfo(Integer reposId, Long docId) 
	{
		Doc doc = null;
		
 Connection conn = null;
 Statement stmt = null;
 try{
 // 注册 JDBC 驱动
 Class.forName(JDBC_DRIVER);
 
 // 打开链接
 //System.out.println("连接数据库...");
 conn = (Connection) DriverManager.getConnection(DB_URL,USER,PASS);
 
 // 执行查询
 //System.out.println(" 实例化Statement对象...");
 stmt = (Statement) conn.createStatement();
 String sql;
 
 sql = "select * from doc where VID=" + reposId + " and DOC_ID=" + docId;
 ResultSet rs = stmt.executeQuery(sql);

 while(rs.next()){
 doc = new Doc();
 doc.setId(rs.getInt("ID"));
 doc.setDocId(rs.getLong("DOC_ID"));
 doc.setVid(rs.getInt("VID"));
 doc.setPath(rs.getString("PATH"));
 doc.setName(rs.getString("NAME"));
 Log.printObject("getDocInfo() Doc: ", doc);
 break;
 }
 	
 // 完成后关闭
 rs.close();
 stmt.close();
 conn.close();
 return doc;
 }catch(SQLException se){
 // 处理 JDBC 错误
 se.printStackTrace();
 }catch(Exception e){
 // 处理 Class.forName 错误
 e.printStackTrace();
 }finally{
 // 关闭资源
 try{
 if(stmt!=null) stmt.close();
 }catch(SQLException se2){
 }// 什么都不做
 try{
 if(conn!=null) conn.close();
 }catch(SQLException se){
 se.printStackTrace();
 }
 }
 System.out.println("Goodbye!");
		return null;
	}
	
	/**
 * 读取json文件,返回json串
 * @param fileName
 * @return
 */
 public static String readJsonFile(String fileName) {
 String jsonStr = "";
 try {
 File jsonFile = new File(fileName);
 FileReader fileReader = new FileReader(jsonFile);

 Reader reader = new InputStreamReader(new FileInputStream(jsonFile),"utf-8");
 int ch = 0;
 StringBuffer sb = new StringBuffer();
 while ((ch = reader.read()) != -1) {
 sb.append((char) ch);
 }
 fileReader.close();
 reader.close();
 jsonStr = sb.toString();
 return jsonStr;
 } catch (IOException e) {
 e.printStackTrace();
 return null;
 }
 }
}