mm-dao

package marketmanagement.dao;

import java.sql.CallableStatement;
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.mysql.jdbc.PreparedStatement;

import marketmanagement.model.ProductDetail;
import marketmanagement.model.PurchaseProduct;
import marketmanagement.model.RoleMaster;
import marketmanagement.model.UserManagement;

public class Dao {


	public static Connection getConnection()throws SQLException, ClassNotFoundException{

		Connection connection=null;

			Class.forName("com.mysql.jdbc.Driver");
			connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/marketmanagement","root","root");

		return connection;
	}

	public List<RoleMaster> getUserRole()throws Exception{
		String getName="select roleId,roleName from rolemaster";
		/*int ret=
		DatabaseUtil.select(getName, null, (ResultSet rs)->{

			return 0;
		});*/


		List<RoleMaster> roleNameList=new ArrayList<RoleMaster>();
		Connection connection=null;
		try{
			connection=getConnection();
			Statement statement=connection.createStatement();
			ResultSet rs=statement.executeQuery(getName);
			while(rs.next()){
				RoleMaster getRole=new RoleMaster();
				getRole.setRoleId(rs.getInt("roleId"));
				getRole.setRoleName(rs.getString("roleName"));

				roleNameList.add(getRole);
			}
		}finally {
			connection.close();
		}
	return roleNameList;

	}

	public int userRegistraton(UserManagement usermngmt) throws SQLException, ClassNotFoundException{
		Connection connection=null;
		int status=0;
		try{
			connection=getConnection();
			String insertUser="insert into usermanagement(userName,email,password,phoneNo,dateOfBirth,balance,roleId) values('"+usermngmt.getUserName()+"','"+usermngmt.getEmail()+"','"+usermngmt.getPassword()+"','"+usermngmt.getPhoneNo()+"','"+usermngmt.getDateOfBirth()+"','"+usermngmt.getBalance()+"','"+usermngmt.getRoleId()+"')";
			Statement statement=connection.createStatement();
			status=statement.executeUpdate(insertUser);

		}finally {
			connection.close();
		}


		return status;

	}
	public UserManagement loginuser(String email)throws SQLException, ClassNotFoundException{
		Connection connection=null;

		try{
			connection=getConnection();
			String getLoginDetails="select userName,email,password,roleId from usermanagement where email='"+email+"'";
			//System.out.println("getLoginDetails="+getLoginDetails);
			Statement statement=connection.createStatement();
			ResultSet rs=statement.executeQuery(getLoginDetails);
			if(rs.next()){
				UserManagement usermngmt=new UserManagement();
				usermngmt.setUserName(rs.getString("userName"));
				usermngmt.setEmail(rs.getString("email"));
				usermngmt.setPassword(rs.getString("password"));
				usermngmt.setRoleId(rs.getInt("roleId"));
				return usermngmt;
			}
		}finally {
			connection.close();
		}

		return null;
	}
	/*public List<UserManagement> loginUser1(String email)throws SQLException, ClassNotFoundException{
		List<UserManagement> loginUser=new ArrayList<UserManagement>();

		Connection connection=null;
		try{
			connection=getConnection();
			String getLoginDetails="select userName,email,password,roleId from usermanagement where email="+email;
			Statement statement=connection.createStatement();
			ResultSet rs=statement.executeQuery(getLoginDetails);
			if(rs.next()){
				UserManagement usermngmt=new UserManagement();
				usermngmt.setUserName(rs.getString("userName"));
				usermngmt.setEmail(rs.getString("email"));
				usermngmt.setPassword(rs.getString("password"));
				usermngmt.setRoleId(rs.getInt("roleId"));

				loginUser.add(usermngmt);
			}
		}finally {
			connection.close();
		}

		return loginUser;

	}*/
	public List<UserManagement> useralrdyLogin(int id)throws SQLException, ClassNotFoundException{
		List<UserManagement> loginUser=new ArrayList<UserManagement>();
		Connection connection=null;
		try{
			connection=getConnection();
			String getLoginDetails="select userId,userName from usermanagement where roleId="+id;
			Statement statement=connection.createStatement();
			ResultSet rs=statement.executeQuery(getLoginDetails);
			while(rs.next()){
				UserManagement usermngmt=new UserManagement();
				usermngmt.setUserId(rs.getInt("userId"));
				usermngmt.setUserName(rs.getString("userName"));

				loginUser.add(usermngmt);
			}
		}finally {
			connection.close();
		}

		return loginUser;

	}

	public List<UserManagement>  listUser(int roleId) throws SQLException, ClassNotFoundException {
		//List<Map<String, Object>> list=new ArrayList<Map<String,Object>>();
		List<UserManagement> list=new ArrayList<UserManagement>();
		Connection connection=null;
		try{

			connection=getConnection();
			/*StringBuilder builder = new StringBuilder();
			builder.append("select um.userId,um.userName,um.email,um.password,um.phoneNo,um.dateOfBirth,um.balance,um.roleId,rm.roleName");
			builder.append(" from usermanagement um ");
			builder.append(" inner join rolemaster rm ON um.roleId=rm.roleId ");
			if(roleId>0)
				builder.append(" where um.roleId=").append(roleId);

			String retriveStudentList=builder.toString();
			Statement statement=connection.createStatement();*/
			//System.out.println(retriveStudentList);
			CallableStatement cstmt=connection.prepareCall("{call studentList(?)}");
			cstmt.setInt(1, roleId);
			ResultSet rs=cstmt.executeQuery();
			while(rs.next()){
				//userId, userName, email, password, phoneNo, dateOfBirth, balance,roleId
					UserManagement userManagement=new UserManagement();
					userManagement.setUserId(rs.getInt("userId"));
					userManagement.setUserName(rs.getString("userName"));
					userManagement.setEmail(rs.getString("email"));
					userManagement.setPassword(rs.getString("password"));
					userManagement.setPhoneNo(rs.getString("phoneNo"));
					userManagement.setDateOfBirth(rs.getString("dateOfBirth"));
					userManagement.setBalance(rs.getString("balance"));
					userManagement.setRoleId(rs.getInt("roleId"));
					userManagement.setRoleName(rs.getString("roleName"));
            	list.add(userManagement);
            }


			return list;
		}
		finally {
			connection.close();
		}
	}

	public List<ProductDetail> listProduct(int userId)throws SQLException, ClassNotFoundException{

		List<ProductDetail> listProduct=new ArrayList<ProductDetail>();
		Connection connection=null;
		try{
			connection=getConnection();
			/*StringBuilder builder = new StringBuilder();
			builder.append("select p.productId,p.productCode,p.productName,p.productPrice,p.productDesription,p.userId,um.userName");
			builder.append(" from product p ");
			builder.append(" inner join usermanagement um ON p.userId=um.userId ");
			if(userId>0)
				builder.append(" where p.userId=").append(userId);

			String getProductList=builder.toString();*/
			//System.out.println(getProductList);
			CallableStatement cstmt=connection.prepareCall("{call listProduct(?)}");
			cstmt.setInt(1, userId);
			//Statement statement=connection.createStatement();
			ResultSet rs=cstmt.executeQuery();
			while(rs.next()){
				ProductDetail product=new ProductDetail();
				product.setProductId(rs.getInt("productId"));
				product.setProductCode(rs.getString("productCode"));
				product.setProductName(rs.getString("productName"));
				product.setProductPrice(rs.getString("productPrice"));
				product.setProductQuntity(rs.getInt("productQuntity"));
				product.setProductDescription(rs.getString("productDesription"));
				product.setUserId(rs.getInt("userId"));
				product.setUserName(rs.getString("userName"));

				listProduct.add(product);

			}
		}finally {
			connection.close();
		}

		return listProduct;

	}

	public UserManagement getUserById(int id) throws SQLException, ClassNotFoundException {
		UserManagement userManagement=null;
		//List<StudentInfo> list=new ArrayList<StudentInfo>();
		try(Connection connection=getConnection();
			){
            //String retriveUserByID="select * from usermanagement where userId="+id;
          CallableStatement cstmt=connection.prepareCall("{call getUserById(?)}");
          cstmt.setInt(1, id);
           // Statement statement=connection.createStatement();
            ResultSet rs=cstmt.executeQuery();
            if(rs.next()){

            	userManagement=new UserManagement();
				userManagement.setUserId(rs.getInt("userId"));
				userManagement.setUserName(rs.getString("userName"));
				userManagement.setEmail(rs.getString("email"));
				userManagement.setPassword(rs.getString("password"));
				userManagement.setPhoneNo(rs.getString("phoneNo"));
				userManagement.setDateOfBirth(rs.getString("dateOfBirth"));
				userManagement.setBalance(rs.getString("balance"));
				userManagement.setRoleId(rs.getInt("roleId"));
            	return userManagement;
            	//list.add(student);
            }else{
            	return null;
            }
		}

				//return student;
	}

	public int updateUserInfo(UserManagement userManagement) throws SQLException, ClassNotFoundException {
		int x=0;
		Connection connection=null;
		try{//userId, userName, email, password, phoneNo, dateOfBirth, roleId
			connection=getConnection();
			String updateUser="update usermanagement set userName='"+userManagement.getUserName()+"',email='"+userManagement.getEmail()+"',password='"+userManagement.getPassword()+"',phoneNo='"+userManagement.getPhoneNo()+"',dateOfBirth='"+userManagement.getDateOfBirth()+"',balance='"+userManagement.getBalance()+"',roleId='"+userManagement.getRoleId()+"' where userId='"+userManagement.getUserId()+"'";
			//System.out.println(updateUser);
			Statement statement=connection.createStatement();
			x=statement.executeUpdate(updateUser);
		}
		finally{
			connection.close();
		}
		return x;
	}

	public int deleteUser(int id) throws SQLException, ClassNotFoundException {
		int status=0;
        Connection connection=null;
       try{
           connection=getConnection();
           //StudentInfo student=new StudentInfo();

           Statement st=connection.createStatement();
           String deleteStudent="delete from usermanagement where userId="+id;

           status=st.executeUpdate(deleteStudent);
       }
       finally{
			connection.close();
		}
       return status;
	}

	//productId, productcode, productName, productPrice, productDesription, userId
	public int productDetail(ProductDetail product) throws SQLException, ClassNotFoundException{
		Connection connection=null;
		int status=0;
		try{
			connection=getConnection();
			/*String insertProduct="insert into product(productcode, productName, productPrice, productQuntity, productDesription, userId) values('"+product.getProductCode()+"','"+product.getProductName()+"','"+product.getProductPrice()+"','"+product.getProductDescription()+"','"+product.getUserId()+"')";
			Statement statement=connection.createStatement();*/
			CallableStatement cstmt=connection.prepareCall("{call productDetail(?,?,?,?,?,?)}");
			cstmt.setString(1, product.getProductCode());
			cstmt.setString(2, product.getProductName());
			cstmt.setString(3, product.getProductPrice());
			cstmt.setInt(4, product.getProductQuntity());
			cstmt.setString(5, product.getProductDescription());
			cstmt.setInt(6, product.getUserId());
			status=cstmt.executeUpdate();

		}finally {
			connection.close();
		}
		return status;
	}

	public ProductDetail getProductById(int id) throws SQLException, ClassNotFoundException {
		ProductDetail product=null;
		Connection connection=null;
		//List<StudentInfo> list=new ArrayList<StudentInfo>();
		try{
			connection=getConnection();
            /*String retriveProductByID="select * from product where productId="+id;

            Statement statement=connection.createStatement();*/
			CallableStatement cstmt=connection.prepareCall("{call getProductById(?)}");
			cstmt.setInt(1, id);
            ResultSet rs=cstmt.executeQuery();
            if(rs.next()){

            	product=new ProductDetail();
            	product.setProductId(rs.getInt("productId"));
				product.setProductCode(rs.getString("productCode"));
				product.setProductName(rs.getString("productName"));
				product.setProductPrice(rs.getString("productPrice"));
				product.setProductQuntity(rs.getInt("productQuntity"));
				product.setProductDescription(rs.getString("productDesription"));
				product.setUserId(rs.getInt("userId"));
				//product.setUserName(rs.getString("userName"));
				//System.out.println("productdetails="+product);
            	return product;
            	//list.add(student);
            }else{
            	return null;
            }
		}
		finally{
			connection.close();
		}

	}

	public int updateProductInfo(ProductDetail product) throws SQLException, ClassNotFoundException {
		int x=0;
		Connection connection=null;
		try{//productId, productcode, productName, productPrice, productDesription, userId
			connection=getConnection();
			String updateUser="update product set productcode='"+product.getProductCode()+"',productName='"+product.getProductName()+"',productPrice='"+product.getProductPrice()+"',productQuntity='"+product.getProductQuntity()+"',productDesription='"+product.getProductDescription()+"',userId='"+product.getUserId()+"' where productId='"+product.getProductId()+"'";
			//System.out.println(updateUser);
			Statement statement=connection.createStatement();
			x=statement.executeUpdate(updateUser);
		}
		finally{
			connection.close();
		}
		return x;
	}

	public int deleteProduct(int id) throws SQLException, ClassNotFoundException {
		int status=0;
        Connection connection=null;
       try{
           connection=getConnection();
           //StudentInfo student=new StudentInfo();

           String deleteStudent="delete from product where productId="+id;
           Statement st=connection.createStatement();
           status=st.executeUpdate(deleteStudent);
       }
       finally{
			connection.close();
		}
       return status;
	}

	public int purchaseProduct(PurchaseProduct product) throws SQLException, ClassNotFoundException{
		Connection connection=null;
		int status=0;
		try{
			connection=getConnection();
			/*String insertProduct="insert into product(productcode, productName, productPrice, productQuntity, productDesription, userId) values('"+product.getProductCode()+"','"+product.getProductName()+"','"+product.getProductPrice()+"','"+product.getProductDescription()+"','"+product.getUserId()+"')";
			Statement statement=connection.createStatement();*/
			CallableStatement cstmt=connection.prepareCall("{call purchaseproduct(?,?,?,?,?,?,?,?)}");
			cstmt.setString(1, product.getProductCode());
			cstmt.setString(2, product.getProductName());
			cstmt.setInt(3, product.getProductPrice());
			cstmt.setInt(4, product.getProductQuntity());
			cstmt.setInt(5, product.getTotal());
			cstmt.setString(6, product.getSeller());
			cstmt.setInt(7, product.getProductId());
			cstmt.setInt(8, product.getUserId());
			status=cstmt.executeUpdate();

		}finally {
			connection.close();
		}
		return status;
	}

	public List<PurchaseProduct> listPurchaseProduct(int userId)throws SQLException, ClassNotFoundException{

		List<PurchaseProduct> listPurchase=new ArrayList<PurchaseProduct>();
		Connection connection=null;
		try{
			connection=getConnection();
			CallableStatement cstmt=connection.prepareCall("{call listPurchaseProduct(?)}");
			cstmt.setInt(1, userId);
			ResultSet rs=cstmt.executeQuery();
			while(rs.next()){
				PurchaseProduct purchase=new PurchaseProduct();
				purchase.setPurchaseId(rs.getInt("purchaseId"));
				purchase.setProductCode(rs.getString("productCode"));
				purchase.setProductName(rs.getString("productName"));
				purchase.setProductPrice(rs.getInt("productPrice"));
				purchase.setProductQuntity(rs.getInt("productQuntity"));
				purchase.setTotal(rs.getInt("total"));
				purchase.setSeller(rs.getString("seller"));
				purchase.setProductId(rs.getInt("productId"));
				purchase.setUserId(rs.getInt("userId"));
				purchase.setUserName(rs.getString("userName"));

				listPurchase.add(purchase);

			}
		}finally {
			connection.close();
		}
		return listPurchase;
	}
}