package com.example.springboot.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Component;

import com.example.springboot.entity.Queue;
import com.example.springboot.entity.north_frame_buffer1_frames;
import com.example.springboot.entity.north_glass_buffer1;
import com.example.springboot.service.North_Glass_Buffer1Service.north_glass_buffer1Comparator;

@Component
public class JdbcConnections {
	/**
	 * 数据库连接参数
	 * driver,url,username,password
	 */
	private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
	private static final String URL = "jdbc:mysql://localhost:3306/canadames";
	private static final String USERNAME = "root";
	private static final String PASSWORD = "beibo.123/";

	private static Connection conns = null;
	private static PreparedStatement ps = null;
	private static ResultSet rss = null;

	public north_glass_buffer1 selectGlass(int glassid) throws SQLException {
		try {
			Connection conn = getConn();
			north_glass_buffer1 glass = new north_glass_buffer1();
			String sql = "select ordernumber from north_glass_buffer1 where glassid=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, glassid);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				glass.setordernumber(rs.getString("ordernumber"));
			}
			conn.close();
			return glass;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}

	}

	public north_glass_buffer1 SelectGlassByGlassIdOrderIdFrameId(String glassid, String orderid, String frameid)
			throws SQLException {

		try {
			Connection conn = getConn();
			north_glass_buffer1 north_glass_buffer1s = new north_glass_buffer1();
			String sql = "select * from north_glass_buffer1 where barcode=? or ordernumber=? or FrameBarcode=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, glassid);
			ps.setString(2, orderid);
			ps.setString(3, frameid);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				north_glass_buffer1s.setId(rs.getInt("Id"));
				north_glass_buffer1s.setordernumber(rs.getString("ordernumber"));
				north_glass_buffer1s.setlistnumber(rs.getString("listnumber"));
				north_glass_buffer1s.setboxnumber(rs.getString("boxnumber"));
				north_glass_buffer1s.setbarcode(rs.getString("barcode"));
				north_glass_buffer1s.setglasslength(rs.getDouble("glasslength"));
				north_glass_buffer1s.setglassheight(rs.getDouble("glassheight"));
				north_glass_buffer1s.setglasslengthmm(rs.getDouble("glasslength_mm"));
				north_glass_buffer1s.setglassheightmm(rs.getDouble("glassheight_mm"));
				north_glass_buffer1s.setitemtype(rs.getString("itemtype"));
				north_glass_buffer1s.setslotnumber(rs.getString("slotnumber"));
				north_glass_buffer1s.setdatemodified(rs.getString("datemodified"));
				north_glass_buffer1s.setdatecreated(rs.getString("datecreated"));
				north_glass_buffer1s.setFrameBarcode(rs.getString("FrameBarcode"));
			}
			conn.close();
			return north_glass_buffer1s;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}

	}

	public north_glass_buffer1 SelectGlassByGlassIdOrderIdFrameIdout(String glassid, String orderid, String frameid)
			throws SQLException {
		try {
			Connection conn = getConn();
			north_glass_buffer1 north_glass_buffer1s = new north_glass_buffer1();
			String sql = "select * from north_glass_buffer1 where barcode=? or ordernumber=? or FrameBarcode=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, glassid);
			ps.setString(2, orderid);
			ps.setString(3, frameid);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				north_glass_buffer1s.setId(rs.getInt("Id"));
				north_glass_buffer1s.setordernumber(rs.getString("ordernumber"));
				north_glass_buffer1s.setlistnumber(rs.getString("listnumber"));
				north_glass_buffer1s.setboxnumber(rs.getString("boxnumber"));
				north_glass_buffer1s.setbarcode(rs.getString("barcode"));
				north_glass_buffer1s.setglasslength(rs.getDouble("glasslength"));
				north_glass_buffer1s.setglassheight(rs.getDouble("glassheight"));
				north_glass_buffer1s.setglasslengthmm(rs.getDouble("glasslength_mm"));
				north_glass_buffer1s.setglassheightmm(rs.getDouble("glassheight_mm"));
				north_glass_buffer1s.setitemtype(rs.getString("itemtype"));
				north_glass_buffer1s.setslotnumber(rs.getString("slotnumber"));
				north_glass_buffer1s.setdatemodified(rs.getString("datemodified"));
				north_glass_buffer1s.setdatecreated(rs.getString("datecreated"));
				north_glass_buffer1s.setFrameBarcode(rs.getString("FrameBarcode"));
			}
			conn.close();
			return north_glass_buffer1s;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}

	}

	public north_glass_buffer1 SelectGlassByGlassIdOrderIdFrameIdouts(String glassid, String orderid, String frameid)
			throws SQLException {
		try {
			Connection conn = getConn();
			north_glass_buffer1 north_glass_buffer1s = new north_glass_buffer1();
			String sql = "select * from north_glass_buffer1 where barcode=? or ordernumber=? or FrameBarcode=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, glassid);
			ps.setString(2, orderid);
			ps.setString(3, frameid);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				north_glass_buffer1s.setId(rs.getInt("Id"));
				north_glass_buffer1s.setordernumber(rs.getString("ordernumber"));
				north_glass_buffer1s.setlistnumber(rs.getString("listnumber"));
				north_glass_buffer1s.setboxnumber(rs.getString("boxnumber"));
				north_glass_buffer1s.setbarcode(rs.getString("barcode"));
				north_glass_buffer1s.setglasslength(rs.getDouble("glasslength"));
				north_glass_buffer1s.setglassheight(rs.getDouble("glassheight"));
				north_glass_buffer1s.setglasslengthmm(rs.getDouble("glasslength_mm"));
				north_glass_buffer1s.setglassheightmm(rs.getDouble("glassheight_mm"));
				north_glass_buffer1s.setitemtype(rs.getString("itemtype"));
				north_glass_buffer1s.setslotnumber(rs.getString("slotnumber"));
				north_glass_buffer1s.setdatemodified(rs.getString("datemodified"));
				north_glass_buffer1s.setdatecreated(rs.getString("datecreated"));
				north_glass_buffer1s.setFrameBarcode(rs.getString("FrameBarcode"));
			}
			conn.close();
			return north_glass_buffer1s;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}

	}

	public north_glass_buffer1 SelectGlassByGlassIdOrderIdFrameIdplchold(String glassid, String orderid, String frameid)
			throws SQLException {
		try {
			Connection conn = getConn();
			north_glass_buffer1 north_glass_buffer1s = new north_glass_buffer1();
			String sql = "select * from north_glass_buffer1 where barcode=? or ordernumber=? or FrameBarcode=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, glassid);
			ps.setString(2, orderid);
			ps.setString(3, frameid);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				north_glass_buffer1s.setId(rs.getInt("Id"));
				north_glass_buffer1s.setordernumber(rs.getString("ordernumber"));
				north_glass_buffer1s.setlistnumber(rs.getString("listnumber"));
				north_glass_buffer1s.setboxnumber(rs.getString("boxnumber"));
				north_glass_buffer1s.setbarcode(rs.getString("barcode"));
				north_glass_buffer1s.setglasslength(rs.getDouble("glasslength"));
				north_glass_buffer1s.setglassheight(rs.getDouble("glassheight"));
				north_glass_buffer1s.setglasslengthmm(rs.getDouble("glasslength_mm"));
				north_glass_buffer1s.setglassheightmm(rs.getDouble("glassheight_mm"));
				north_glass_buffer1s.setitemtype(rs.getString("itemtype"));
				north_glass_buffer1s.setslotnumber(rs.getString("slotnumber"));
				north_glass_buffer1s.setdatemodified(rs.getString("datemodified"));
				north_glass_buffer1s.setdatecreated(rs.getString("datecreated"));
				north_glass_buffer1s.setFrameBarcode(rs.getString("FrameBarcode"));
			}
			conn.close();
			return north_glass_buffer1s;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}

	}

	// 根据玻璃id，订单id，铝框id查询客户玻璃信息
	public Queue SelectGlassByGlassIdOrderIdFrameIdQueue(String glassid, String orderid, String frameid)
			throws SQLException {
		try {
			Connection conn = getConn();
			Queue queue = new Queue();
			String sql = "select * from north_glass_buffer1 where barcode=? or ordernumber=? or FrameBarcode=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, glassid);
			ps.setString(2, orderid);
			ps.setString(3, frameid);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				queue.setid(rs.getInt("Id"));
				queue.setorderId(rs.getString("ordernumber"));
				queue.setlistId(rs.getString("listnumber"));
				queue.setboxId(rs.getString("boxnumber"));
				queue.setglassId(rs.getString("barcode"));
				queue.setglasswidthmm(rs.getDouble("glasslength"));
				queue.setglassheightmm(rs.getDouble("glassheight"));
				queue.setglasswidth(rs.getDouble("glasslength_mm"));
				queue.setglassheight(rs.getDouble("glassheight_mm"));
				// queue.setitemtype(rs.getString("itemtype"));
				// queue.setslotnumber(rs.getString("slotnumber"));
				// queue.setdatemodified(rs.getString("datemodified"));
				// queue.setdatecreated(rs.getString("datecreated"));
				// queue.setFrameBarcode(rs.getString("FrameBarcode"));
			}
			conn.close();
			return queue;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}

	}

	// 根据玻璃id，订单id，铝框id查询客户玻璃信息
	public List<north_glass_buffer1> SelectGlassByGlassIdOrderIdFrameIds(String glassid, String orderid, String frameid)
			throws SQLException {
		try {
			Connection conn = getConn();
			// north_glass_buffer1 glass=new north_glass_buffer1();
			List<north_glass_buffer1> glass = new ArrayList<north_glass_buffer1>();
			String sql = "select * from north_glass_buffer1 where position(? in barcode) and position(? in ordernumber) and position(? in FrameBarcode)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, glassid);
			ps.setString(2, orderid);
			ps.setString(3, frameid);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				north_glass_buffer1 north_glass_buffer1s = new north_glass_buffer1();

				north_glass_buffer1s.setId(rs.getInt("Id"));
				north_glass_buffer1s.setordernumber(rs.getString("ordernumber"));
				north_glass_buffer1s.setlistnumber(rs.getString("listnumber"));
				north_glass_buffer1s.setboxnumber(rs.getString("boxnumber"));
				north_glass_buffer1s.setbarcode(rs.getString("barcode"));
				north_glass_buffer1s.setglasslength(rs.getDouble("glasslength"));
				north_glass_buffer1s.setglassheight(rs.getDouble("glassheight"));
				north_glass_buffer1s.setglasslengthmm(rs.getDouble("glasslength_mm"));
				north_glass_buffer1s.setglassheightmm(rs.getDouble("glassheight_mm"));
				north_glass_buffer1s.setitemtype(rs.getString("itemtype"));
				north_glass_buffer1s.setslotnumber(rs.getString("slotnumber"));
				north_glass_buffer1s.setdatemodified(rs.getString("datemodified"));
				north_glass_buffer1s.setdatecreated(rs.getString("datecreated"));
				north_glass_buffer1s.setFrameBarcode(rs.getString("FrameBarcode"));

				glass.add(north_glass_buffer1s);

			}
			conn.close();
			return glass;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}

	}

	// 根据铝框id查询客户玻璃信息
	public List<north_glass_buffer1> SelectGlassByGlassIdOrderIdFrameIdss(String frameid) throws SQLException {
		try {
			Connection conn = getConn();
			// north_glass_buffer1 glass=new north_glass_buffer1();
			List<north_glass_buffer1> glass = new ArrayList<north_glass_buffer1>();
			String sql = "select * from north_glass_buffer1 where FrameBarcode=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, frameid);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				north_glass_buffer1 north_glass_buffer1s = new north_glass_buffer1();

				north_glass_buffer1s.setId(rs.getInt("Id"));
				north_glass_buffer1s.setordernumber(rs.getString("ordernumber"));
				north_glass_buffer1s.setlistnumber(rs.getString("listnumber"));
				north_glass_buffer1s.setboxnumber(rs.getString("boxnumber"));
				north_glass_buffer1s.setbarcode(rs.getString("barcode"));
				north_glass_buffer1s.setglasslength(rs.getDouble("glasslength"));
				north_glass_buffer1s.setglassheight(rs.getDouble("glassheight"));
				north_glass_buffer1s.setglasslengthmm(rs.getDouble("glasslength_mm"));
				north_glass_buffer1s.setglassheightmm(rs.getDouble("glassheight_mm"));
				north_glass_buffer1s.setitemtype(rs.getString("itemtype"));
				north_glass_buffer1s.setslotnumber(rs.getString("slotnumber"));
				north_glass_buffer1s.setdatemodified(rs.getString("datemodified"));
				north_glass_buffer1s.setdatecreated(rs.getString("datecreated"));
				north_glass_buffer1s.setFrameBarcode(rs.getString("FrameBarcode"));

				glass.add(north_glass_buffer1s);

			}
			conn.close();
			return glass;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}

	}

	// 根据铝框id查询l铝框是否翻转
	public String SelectFlipByFrameBarcode(String frameBarcode) throws SQLException {
		try {
			Connection conn = getConn();
			// north_glass_buffer1 glass=new north_glass_buffer1();
			String flip = "";
			String sql1 = "select * from north_glass_buffer1_frames where Barcode=? limit 1";
			ps = conn.prepareStatement(sql1);
			ps.setString(1, frameBarcode);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				flip = rs.getString("Flip");

			}
			conn.close();
			return flip;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}

	}

	// 根据铝框id查询是否铝框摆放
	public String SelectPositionByFrameBarcode(String frameBarcode) throws SQLException {
		try {
			Connection conn = getConn();
			// north_glass_buffer1 glass=new north_glass_buffer1();
			String position = "";
			String sql1 = "select * from north_glass_buffer1_frames where Barcode=? limit 1";
			ps = conn.prepareStatement(sql1);
			ps.setString(1, frameBarcode);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				position = rs.getString("flip");
			}
			conn.close();
			return position;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}

	}

	// 根据铝框id查询铝框宽
	public String SelectFrameLengthByFrameBarcode(String frameBarcode) throws SQLException {
		try {
			Connection conn = getConn();
			// north_glass_buffer1 glass=new north_glass_buffer1();
			String position = "";
			String sql1 = "select * from north_glass_buffer1_frames where Barcode=? limit 1";
			ps = conn.prepareStatement(sql1);
			ps.setString(1, frameBarcode);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				position = rs.getString("framelength");
			}
			conn.close();
			return position;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}

	}

	// 根据铝框id查询铝框高
	public String SelectFrameHeightByFrameBarcode(String frameBarcode) throws SQLException {
		try {
			Connection conn = getConn();
			// north_glass_buffer1 glass=new north_glass_buffer1();
			String position = "";
			String sql1 = "select * from north_glass_buffer1_frames where Barcode=? limit 1";
			ps = conn.prepareStatement(sql1);
			ps.setString(1, frameBarcode);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				position = rs.getString("frameheight");
			}
			conn.close();
			return position;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}

	}

	// 根据铝框id查询铝框信息
	public north_frame_buffer1_frames SelectFrameBarcode(String frameBarcode) throws SQLException {
		try {
			Connection conn = getConn();
			north_frame_buffer1_frames frame=new north_frame_buffer1_frames();
			String sql1 = "select * from north_glass_buffer1_frames where Barcode=? limit 1";
			ps = conn.prepareStatement(sql1);
			ps.setString(1, frameBarcode);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				frame.setId(rs.getInt("id"));
				frame.setlistnumber(rs.getString("listnumber"));
				frame.setordernumber(rs.getString("ordernumber"));
				frame.setboxnumber(rs.getString("boxnumber"));
				frame.setbarcode(rs.getString("barcode"));
				frame.setframelength(rs.getDouble("framelength"));
				frame.setframeheight(rs.getDouble("frameheight"));
				frame.setframelengthmm(rs.getDouble("framelength_mm"));
				frame.setframeheightmm(rs.getDouble("frameheight_mm"));
				frame.setflip(rs.getInt("flip"));
			}
			conn.close();
			return frame;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * 1. 加载驱动
	 * 2. 获取连接 conn
	 * 3. 创建语句 ps
	 * 4. 执行语句 rs
	 * 5. 处理结果
	 * 6. 回收资源
	 * 
	 * 实现CRUD
	 * 更新：
	 * 1增加
	 * 2删除
	 * 3修改
	 * 查询：
	 * 1. 查一个，一个对象
	 * 2. 查一组，做成一个对象列表，查全部
	 */
	public static Connection getConn() throws SQLException {
		try {
			Connection conn = null;
			// conn =
			// DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8&characterEncoding=utf-8",
			// "root", "beibo.123/");
			conn = DriverManager.getConnection(
					"jdbc:mysql://192.168.102.9:3306/production?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false",
					"northglass", "n0rthgla55");
			return conn;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}

	}

	static {
		try {
			Class.forName(DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

}