package logApi;

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.HashMap;
import java.util.Map;
import java.util.concurrent.ExecutionException;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import java.util.List;

public class DBConnection {
	private Connection con;
	private Statement stmt;
	private ResultSet rs;
	public 	static JSONArray jarr;
	public DBConnection() {
		try {
			String url = "jdbc:sqlserver://signus-sf1.koreacentral.cloudapp.azure.com:14443;databaseName=H3_DB";
			String user = "hanmi";
			String passwd = "u3hanmi";
			con = DriverManager.getConnection(url, user, passwd);
			System.out.println("DB���� ����");
			stmt = con.createStatement();
			System.out.println("Statement��ü ���� ����");
			List<Map> list = new ArrayList<Map>() ;
			List<String> keyList = new ArrayList<String>();
			Map map = new HashMap();
			
			
			rs = stmt.executeQuery("select * from T_SYS_LOG_SF WHERE SEND_YN= 'N' ORDER BY 1 "); //��ȸ�� ������� ResultSet�� rs�� �����Ѵ�. 
			
			while(rs.next()) {
				
				map.put("crtfcKey", rs.getString(2));
				map.put("logDt", rs.getString(3));
				map.put("useSe", rs.getString(4));
				map.put("sysUser", rs.getString(5));
				map.put("conectIp", rs.getString(6));
				map.put("dataUsgqty", rs.getString(7));
				//map.put("SEND_YN", rs.getString(8));
				//map.put("PROC_NM", rs.getString(9));
				list.add( map);
				keyList.add(rs.getString(1));
				map = new HashMap();
			}
			
			rs.close();
			stmt.close();	
			
			jarr = convertListToJson(list);

			
			for(int i=0; i<keyList.size(); i++) {
				try {
					LogAPI.send("logData="+jarr.get(i).toString());
					
					stmt = con.createStatement();
					stmt.executeUpdate("UPDATE T_SYS_LOG_SF SET SEND_YN='N' WHERE IDX = "+keyList.get(i));
					stmt.close();
				} catch (JSONException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (ExecutionException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (InterruptedException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			
			}
			
			con.close();
			

			
		} catch (SQLException e) {
			System.out.println("DB Connect Failed");
			System.out.print("Error: " + e.getMessage());
		}
	}
	
	
	public static JSONArray convertListToJson(List<Map> list) { 
		JSONArray jsonArray = new JSONArray(); 
		for (Map<String, Object> map : list) { 
			jsonArray.put(convertMapToJson(map)); 
		} 
		return jsonArray; 
	}

	
	public static JSONObject convertMapToJson(Map<String, Object> map) { 
		JSONObject json = new JSONObject(); 
		for (Map.Entry<String, Object> entry : map.entrySet()) { 
			String key = entry.getKey(); Object value = entry.getValue(); json.put(key, value); 
		} 
		return json; 
	}

}