
package model;

import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.Normalizer;
import java.text.Normalizer.Form;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author user
 */
public class SanPham {


    

    public List<ThongTinSanPham> getAll(int limit, int offset) throws SQLException {
    	PreparedStatement getal = null;
//        int offset = (pageNumber - 1) * 9;

       List<ThongTinSanPham> result = new ArrayList<ThongTinSanPham>();
       Connection con = new ConnectionPool().getConnection();
       getal = con.prepareStatement("select * from product limit ? offset ?");
       getal.setInt(1, limit);
       getal.setInt(2, offset);
        ResultSet rs = getal.executeQuery();
        
        while (rs.next()) {
        	
          int id = rs.getInt(1);
        	String name = rs.getString(2);
            String image = rs.getString(3);
            double price = rs.getDouble(4);
            String description = rs.getString(5);
            String category = rs.getString(6);
            String status = rs.getString(7);

            ThongTinSanPham sanpham = new ThongTinSanPham(id,name, image, price, description,category, status);
            result.add(sanpham);
        }

        con.close();
        return result;
    }
    
    
    public List<ThongTinSanPham> getAllPDSO(int limit, int offset) throws SQLException {
    	PreparedStatement getal = null;
    	String dk = "HetHang";


       List<ThongTinSanPham> result = new ArrayList<ThongTinSanPham>();
       Connection con = new ConnectionPool().getConnection();
       getal = con.prepareStatement("select * from product where status = ? limit ? offset ?");

       getal.setString(1, dk);
       getal.setInt(2, limit);
       getal.setInt(3, offset);
        ResultSet rs = getal.executeQuery();
        
        while (rs.next()) {
        	
          int id = rs.getInt(1);
        	String name = rs.getString(2);
            String image = rs.getString(3);
            double price = rs.getDouble(4);
            String description = rs.getString(5);
            String category = rs.getString(6);
            String status = rs.getString(7);

            ThongTinSanPham sanpham = new ThongTinSanPham(id,name, image, price, description,category, status);
            result.add(sanpham);
        }

        con.close();
        return result;
    }
    
    public int recordsnumberPDSO() throws SQLException {
    	PreparedStatement recornumb = null;
    	String dk = "HetHang";
    	int record = 0;
		Connection con = new ConnectionPool().getConnection();
		
			recornumb = con.prepareStatement("select count(id) from product where status = ?");
			recornumb.setString(1, dk);
	        ResultSet rs2 = recornumb.executeQuery();
	        while (rs2.next()) { 	        	
	        	record = rs2.getInt(1);
	        }
			con.close();
		return record;
	}
    
    
    public List<ThongTinSanPham> getAllbyPrice(int limit, int offset) throws SQLException {
    	PreparedStatement getal = null;
//        int offset = (pageNumber - 1) * 9;

       List<ThongTinSanPham> result = new ArrayList<ThongTinSanPham>();
       Connection con = new ConnectionPool().getConnection();
       getal = con.prepareStatement("select * from product ORDER BY ABS(price) DESC limit ? offset ?");
       getal.setInt(1, limit);
       getal.setInt(2, offset);
        ResultSet rs = getal.executeQuery();
        
        while (rs.next()) {
        	
          int id = rs.getInt(1);
        	String name = rs.getString(2);
            String image = rs.getString(3);
            double price = rs.getDouble(4);
            String description = rs.getString(5);
            String category = rs.getString(6);
            String status = rs.getString(7);
            
            ThongTinSanPham sanpham = new ThongTinSanPham(id,name, image, price, description,category, status);
            result.add(sanpham);
        }

        con.close();
        return result;
    }

    public int recordsnumber() throws SQLException {
    	PreparedStatement recornumb = null;
    	int record = 0;
		Connection con = new ConnectionPool().getConnection();
		
			recornumb = con.prepareStatement("select count(id) from product");
	        ResultSet rs2 = recornumb.executeQuery();
	        while (rs2.next()) { 	        	
	        	record = rs2.getInt(1);
	        }
			con.close();
		return record;
	}
    
    public List<ThongTinSanPham> byCategory(int limit, int offset, String category) throws SQLException {
    	PreparedStatement bycate = null;
       List<ThongTinSanPham> result = new ArrayList<ThongTinSanPham>();
       Connection con = new ConnectionPool().getConnection();
        bycate = con.prepareStatement("select * from product where category like ? limit ? offset ?");
        bycate.setString(1, "%"+category+"%");
        bycate.setInt(2, limit);
        bycate.setInt(3, offset);
        ResultSet rs = bycate.executeQuery();
        
        while (rs.next()) {
        	
          int id = rs.getInt(1);
        	String name = rs.getString(2);
            String image = rs.getString(3);
            double price = rs.getDouble(4);
            String description = rs.getString(5);
            String category2 = rs.getString(6);
            String status = rs.getString(7);
            
            ThongTinSanPham sanpham = new ThongTinSanPham(id,name, image, price, description,category2, status);
            result.add(sanpham);
        }

        con.close();
        return result;
    }
    
    public int recordsbycategory(String category) throws SQLException {
    	PreparedStatement rcbycate = null;
    	int record = 0;
    	
		Connection con = new ConnectionPool().getConnection();

			rcbycate = con.prepareStatement("select count(id) from product where category like ?");
			rcbycate.setString(1, "%"+category+"%");
	        ResultSet rs2 = rcbycate.executeQuery();
	        while (rs2.next()) { 	        	
	        	record = rs2.getInt(1);
	        }
			con.close();
		return record;
	}
    
    public List<ThongTinSanPham> search(int limit, int offset, String name, String cate) throws SQLException {
    	PreparedStatement search = null;
       List<ThongTinSanPham> result = new ArrayList<ThongTinSanPham>();
       Connection con = new ConnectionPool().getConnection();
        search = con.prepareStatement("select * from product where name like ? and category like ? limit ? offset ?");
        search.setString(1, "%"+name+"%");
        search.setString(2, "%"+cate+"%");
        search.setInt(3, limit);
        search.setInt(4, offset);
        ResultSet rs = search.executeQuery();
        
        while (rs.next()) {
        	
          int id = rs.getInt(1);
        	String name2 = rs.getString(2);
            String image = rs.getString(3);
            double price = rs.getDouble(4);
            String description = rs.getString(5);
            String category = rs.getString(6);
            String status = rs.getString(7);
            
            ThongTinSanPham sanpham = new ThongTinSanPham(id,name2, image, price, description,category, status);
            result.add(sanpham);
        }

        con.close();
        return result;
    }
    
    public int recordsearch(String name, String cate) throws SQLException {
    	PreparedStatement recordsearch = null;
    	int record = 0;
		Connection con = new ConnectionPool().getConnection();

			recordsearch = con.prepareStatement("select count(id) from product where name like ? and category like ?");
			recordsearch.setString(1, "%"+name+"%");
			recordsearch.setString(2, "%"+cate+"%");
			
			ResultSet rs2 = recordsearch.executeQuery();
	        while (rs2.next()) { 	        	
	        	record = rs2.getInt(1);
	        }
			con.close();
		return record;
	}
    
    
    public ThongTinSanPham productId(int pid) throws SQLException {
    	PreparedStatement productid = null;
       ThongTinSanPham result = null;
       Connection con = new ConnectionPool().getConnection();
        productid = con.prepareStatement("select * from product where id = ?");
        productid.setInt(1, pid);
        ResultSet rs = productid.executeQuery();
        
        if (rs.next()) {
        	
          int id = rs.getInt(1);
        	String name = rs.getString(2);
            String image = rs.getString(3);
            double price = rs.getDouble(4);
            String description = rs.getString(5);
            String category = rs.getString(6);
            String status = rs.getString(7);
            
            result = new ThongTinSanPham(id,name, image, price, description,category, status);
            
        }

        con.close();
        return result;
    }
    
    public ProductForCart ProductForCart(int id) throws Exception {
    	PreparedStatement productforcart = null;
    	ProductForCart sp = null;
        Connection con = new ConnectionPool().getConnection();
        productforcart = con.prepareStatement("select * from product where id = ?");
        productforcart.setInt(1, id);
        ResultSet rs = productforcart.executeQuery();
        if (rs.next()) {
            String msp = rs.getString("id");
            String name = rs.getString("name");
            String image = rs.getString("image");
            double price = rs.getDouble("price");
            String status = rs.getString("status");
            sp = new ProductForCart(msp, name, image, price, status);
        }
        con.close();
        return sp;
    }
    
    
    public List<ThongTinSanPham> category() throws SQLException {
    	PreparedStatement category = null;
    	List<ThongTinSanPham> result = new ArrayList<ThongTinSanPham>();
		Connection con = new ConnectionPool().getConnection();		
			category = con.prepareStatement("SELECT Distinct category FROM product");
	        ResultSet rs2 = category.executeQuery();
	        while (rs2.next()) { 	        	
	        	String cate = rs2.getString("category");
	        	ThongTinSanPham sanpham = new ThongTinSanPham(1,"","",0,"",cate,"");
	        	result.add(sanpham);
	        }    
			con.close();
		return result;
	}
    

    public String UTFToASCI(String text) {
	    return Normalizer.normalize(text, Form.NFD).replaceAll("[^\\p{ASCII}]", "");
	}
    
    public static void main(String[] args) throws SQLException {
		System.out.println(new SanPham().recordsearch("nhẫn","trang suc"));
	}
}

    

