Sql.java

package com.tarento.retail.util;

/**
 * This interface will hold all the SQL Quries which are being used by the
 * application Internally, the inner interface will have the queries separated
 * based on the functionalities that they are associated with
 * 
 * @author Darshan Nagesh
 *
 */
public interface Sql {

	final String ID = "id";

	/**
	 * All the queries associated with the Common activities or transactions will be
	 * placed here
	 * 
	 * @author Darshan Nagesh
	 *
	 */
	public interface Common {
		final String VERIFY_PASSWORD = "SELECT id FROM user WHERE password = ? AND username = ? ";
		final String WHERE_CLAUSE = " WHERE ";
		final String AND_CONDITION = " AND ";
		final String OR_CONDITION = " OR ";
		final String OPEN_BRACE = "(";
		final String CLOSE_BRACE = ")";
		final String GET_COUNTRY_LIST = "SELECT id, name, code, currency, phone_code, FALSE as is_default FROM country ";
		final String GET_COUNTRY_LIST_FOR_USER = "SELECT c.id, c.name, c.code, c.currency, c.phone_code, cu.is_default FROM country c left join country_user cu ON c.id = cu.country_id where cu.user_id = ?";
		final String GET_COUNTRY_LIST_FOR_ORG = "SELECT id, name, code, currency, phone_code, url FROM country WHERE  org_id = ?";
		final String BY_ROLE_ID = " role_id in (<ROLE_ID>) ";
		final String DELETE_COUNTRY_FOR_USER = "DELETE from country_user where country_id=?";
		final String DELETE_COUNTRY_FOR_ORG = "DELETE from country where id=? AND org_id=? ";
	}

	public interface RoleAction {
		// final String GET_ALL_ROLES = "SELECT id, role_name, r.code , description,
		// org_id FROM role r INNER JOIN role_org ro ON r.id= ro.role_id WHERE ro.org_id
		// = ?";
		final String GET_ALL_ROLES = "SELECT id, role_name, code , description, org_id FROM role where org_id=?";
		// final String GET_ROLE_BY_ORG_DOMAIN = "select * from org_domain_role odr
		// INNER JOIN role r ON odr.role_id = r.id where org_domain=?";
		final String GET_ROLE_BY_ORG_DOMAIN = "select * from org_domain_role where org_domain=?";
		final String SELECT_ROLES_ON_ID = "SELECT * FROM role WHERE id=?";
		final String GET_DEFAULT_ROLES = "SELECT * FROM org_domain_role WHERE role_id = ?";
		final String GET_ROLE_BY_USER = "select r.role_id as id, r.description as description,  r.role_name as role_name, r.org_id as org_id from user usr inner join user_role ur  on usr.id = ur.user_id inner join role r on r.id = ur.role_id where usr.id = ? and r.org_id= ?";
		final String SELECT_ROLE_ACTIONS_ON_ROLEID = "SELECT * FROM role_actions WHERE role_id=?";
		final String SELECT_ACTIONS_ON_ID = "SELECT * FROM actions WHERE id=?";
		final String SAVE_NEW_ROLE = "INSERT INTO role (role_name,description,code, is_org_admin, org_id) VALUES (? , ?, ?,?,?)";

		final String UPDATE_ROLE = "UPDATE role SET role_name = ?, description = ? WHERE id= ? ";
		final String UPDATE_ROLE_ORG = "INSERT INTO role_org(role_id, org_id) value (?,?) ";

		final String INSERT_ACTION = "INSERT INTO retail_actions (description, module_code, module_name, url) VALUES (?, ?, ?, ?)";
		final String DELETE_ROLE = "DELETE from role where id=?";
		final String MAP_ACTION_TO_ROLE = "insert into role_actions(role_id,action_id) values(?,?)";
		// final String MAP_ACTION_TO_ROLE = "insert into
		// role_actions(role_id,action_id, org_id) values(?,?,?)";
		final String UNMAP_ORG_ROLE = "DELETE FROM role_org WHERE role_id = ? AND org_id = ?";
		final String UNMAP_ACTION_TO_ROLE = "delete from role_actions where action_id=? and role_id = ?";
		final String GET_MAPPED_ACTION_ROLE_LIST = "select * from retail_user.actions where id IN (select action_id as id from retail_user.role_actions where role_id=?)";
		final String GET_UNMAPPED_ACTION_ROLE_LIST = "select * from retail_user.actions where id NOT IN (select action_id as id from retail_user.role_actions where role_id=?)";
		// final String DELETE_USER_ROLE="DELETE from ";
		final String GET_USERS_BY_ROLE_ID = "select id, username, email_id, phone_no, org_id, timezone from retail_user.user where id IN ( select user_id from retail_user.user_role where role_id=? AND org_id=?)";
	}

	public interface UserQueries {
		final String SELECT_USER_BY_TOKEN = "SELECT COUNT(*) FROM user_authentication WHERE auth_token = ? ";
		final String SELECT_USER_ON_USERNAME = "SELECT * FROM user usr inner join country_user cu on usr.id =cu.user_id inner join country c on cu.country_id = c.id where username=? or phone_no = ?";