package admin.model;

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

import model.BillInfo;
import model.ConnectionPool;

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

    public List<BillInfo> getBill(int limit, int offset) throws SQLException {
    	PreparedStatement getBill = null;
       List<BillInfo> result = new ArrayList<BillInfo>();
       Connection con = new ConnectionPool().getConnection();
        getBill = con.prepareStatement("select * from bill limit ? offset ?");
        getBill.setInt(1, limit);
        getBill.setInt(2, offset);
        ResultSet rs = getBill.executeQuery();
        
        while (rs.next()) {
        	String id = rs.getString(1);
        	String date = rs.getString(2);
            String usern= rs.getString(3);
            String idproduct = rs.getString(4);
            String payinfo = rs.getString(5);
            double total = rs.getDouble(6);
            String status = rs.getString(7);

            BillInfo bill = new BillInfo(id,date,usern, idproduct, payinfo, total,status);
            result.add(bill);
        }

        con.close();
        return result;
    }

    public int BillRecords() throws SQLException {
    	PreparedStatement billrc = null;
    	int record = 0;
		Connection con = new ConnectionPool().getConnection();
		

			billrc = con.prepareStatement("select count(id) from bill");
	        ResultSet rs2 = billrc.executeQuery();
	        while (rs2.next()) { 	        	
	        	record = rs2.getInt(1);
	        }
			con.close();
		return record;
	}
    
	public boolean ChangeStatus(String id,String status) {
		PreparedStatement chgstt = null;
		boolean result = false;

		Connection con = new ConnectionPool().getConnection();
		try {
		
			chgstt = con.prepareStatement("update bill set status = ? where id = ?");
			chgstt.setString(1, status);
			chgstt.setString(2, id);
			int number = chgstt.executeUpdate();
			if (number > 0) {
				result = true;
			}

			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;
	}
	
	
 	public boolean DeleteBill(String id) {
 		PreparedStatement delbill = null;
		boolean result = false;
		Connection con = new ConnectionPool().getConnection();
		try {
			
			delbill = con.prepareStatement("DELETE FROM bill WHERE id = ?");
			delbill.setString(1, id);
			int number = delbill.executeUpdate();
			if (number > 0) {
				result = true;
			}
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;
	}
 	
 	
 	public boolean DeleteAllBill() {
 		PreparedStatement delallbill = null;
		boolean result = false;
		Connection con = new ConnectionPool().getConnection();
		try {

			delallbill = con.prepareStatement("DELETE FROM bill");

			int number = delallbill.executeUpdate();
			if (number > 0) {
				result = true;
			}
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;
	}
    
    public static void main(String[] args) throws SQLException {
    	 System.out.println(new BillMods().BillRecords());
    	// new BillMod().createbill(new BillInfo("01/02/2000 20:19:02","LuanXT","LuanXt","luanxt",12,"luanxt"));
//		String a = "luanxt";
//    System.out.println(new BillMod().getBill(a));
//    	int size = new BillMod().getBill("luanxt").size();
//    	System.out.println(size);

    	}
	}


    


