constants.java

oa rar it is the jsp

package com.accp.oa.common;

public class Constants {
	/** *************这是登录--角色---修改密码--模块的sql语句************************** */
	// 如下sql语句是用于实现登录功能
	public static final String VALEATE_USER = "SELECT * FROM tb_person WHERE Name = ? AND Password = ?";

	public static final String SEARCH_ALL_USER = "SELECT * FROM tb_person";

	// 如下sql语句用于查询出给定人员的权限
	public static final String SEARCH_RIGHT_BY_PERID = "SELECT * FROM tb_model WHERE Id IN (SELECT ModelId FROM tb_model_roes mr WHERE mr.RoesId = (SELECT RoesId FROM tb_user_roes ur WHERE ur.PerId = ?))";

	// 如下是一些map类型数据的键
	public static final String USERNAMES = "usernames";

	public static final String ROESNAMES = "roesnames";

	public static final String USERANDROES = "userandroes";

	// 如下是对角色表进行操作的sql语句
	public static final String SEARCH_ALL_ROES = "SELECT * FROM tb_roes";

	// 此sql语句用于查询出已有相应角色的人员角色信息
	public static final String SEARCH_USER_ROES = "SELECT  p.Id, p.Name, r.* FROM tb_person p ,tb_roes r WHERE p.Id in (SELECT PerId FROM tb_user_roes WHERE RoesId = r.Id)";

	// 此sql语句用于查询出没有相应角色的人员
	public static final String SEACH_USER_NOT_ROES = "SELECT  p.Id, p.Name  FROM tb_person p  WHERE p.Id not in (SELECT PerId FROM tb_user_roes )";

	// 此sql语句用度于查询出给定角色的所有权限
	public static final String SEARCH_RIGHT = "SELECT * FROM tb_model WHERE Id IN (SELECT ModelId FROM tb_model_roes WHERE RoesId = ?)";

	// 如下sql语句是用于对tb_model_roes表进行操作的
	public static final String DELETE_MODEL_ROES = "DELETE FROM tb_model_roes WHERE RoesId = ?";

	public static final String GRAND_RIGHT_ROES = "INSERT INTO tb_model_roes VALUES(?,?)";

	// 以下sql语句是用于添加新的用户和角色关系
	public static final String ADD_USER_ROES = "INSERT INTO tb_user_roes values(?,?)";

	// 此sql语句用于进行用户角色的修改
	public static final String UPDATE_USER_ROES = "UPDATE tb_user_roes SET RoesId = ? WHERE PerId = ?";

	// 此sql语句用于删除用户的角色
	public static final String DELETE_USER_ROES = "DELETE tb_user_roes WHERE PerId = ?";

	// 此sql语句用于删除角色名
	public static final String DELETE_ROESNAME = "DELETE tb_roes WHERE Id = ?";

	// 此sql语名用于添加角色名
	public static final String ADD_ROESNAME = "INSERT INTO tb_roes VALUES(?)";

	// 此sql语句用于查询出表tb_uer_roes中给定角色ID的所有数据
	public static final String SEARCH_ALL_BYROESID = "SELECT * FROM tb_user_roes WHERE RoesId = ?";

	/** **********************这是物资管理的sql语句***************************************************** */
	// 此sql语句用于查询出所有的物资类型
	public static final String SEARCH_ALL_TYPE = "SELECT * FROM tb_th_types";

	// 此sql语句用于添加物资类型
	public static final String ADD_TYPE = "INSERT INTO tb_th_types VALUES(?)";

	// 此sql语句用于删除物资类型
	public static final String DELETE_TYPE = "DELETE FROM tb_th_types WHERE Id = ?";

	// 此sql语句用于查询出所有的物资单位
	public static final String SEARCH_ALL_UNIT = "SELECT * FROM tb_th_unit";

	// 此sql语句用于添加物资单位
	public static final String ADD_UNIT = "INSERT INTO tb_th_unit VALUES(?)";

	// 此sql语句用于删除物资单位
	public static final String DELETE_UNIT = "DELETE FROM tb_th_unit WHERE Id = ?";

	// 此sql语句用于查询出所有的物资供应商
	public static final String SEARCH_ALL_BOSS = "SELECT * FROM tb_th_boss";

	// 此sql语句用于添加物资的供应商信息
	public static final String ADD_BOSS = "INSERT INTO tb_th_boss VALUES(?,?,?,?,?,?,?,?,?)";

	// 此sql语句用于删除供应商信息
	public static final String DELETE_BOSS = "DELETE FROM tb_th_boss WHERE Id = ?";

	// 此sql语句用于修改供应商信息
	public static final String UPDATE_BOSS = "UPDATE tb_th_boss SET Name = ? ,Address = ? ,Tel = ? ,"
			+ "Fax = ?,Email = ?,Lkname = ?,Lkduty = ?,Lktel = ?,Lkhandset = ? WHERE Id = ?";

	// 此sql语句用于添加库存
	public static final String ADD_PLAN = "INSERT INTO tb_th_plan(Name,Numbers,Type,Money,Boss,Dime,Planing,Untils) VALUES(?,?,?,?,?,?,?,?)";

	// 此sql语句用于查询出入或出或损耗的库存信息
	public static final String SEARCH_PLAN = "SELECT * FROM tb_th_plan WHERE Planing = ?";

	// 此sql语句用于删除相应库存信息
	public static final String DELETE_PLAN = "DELETE FROM tb_th_plan WHERE Id = ?";

	// 此sql语句用于修改相应库存信息
	public static final String UPDATE_PLAN = "UPDATE tb_th_plan SET Numbers = ?,Money = ?,Boss = ?,Dime = ? WHERE Id = ?";

	// 此sql语句用于统计相应条件的库存信息
	public static final String STATE_PLAN = "SELECT * FROM tb_th_plan WHERE 1=1";

	/** **************************客户管理sql语句************************** */
	// 添加客户信息
	public static final String ADD_CLIENT = "INSERT INTO tb_client VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

	// 以下14个是分页控制语句的拼凑
	// 查询前十条记录
	public static final String FENYE1 = "SELECT TOP 5 * FROM (SELECT TOP ";

	// 按照降序和升序查询
	public static final String SELECT_CLIENT = " * from tb_client a order by a.Id desc) b order by b.Id asc";

	// 按照客户的名称查询信息
	public static final String SELECT1_CLIENT = " * from tb_client a where a.ClientName='";

	// 按照客户的名称查询信息
	public static final String SELECT2_CLIENT = "' order by a.Id desc)b where b.ClientName='";

	// 按照客户的名称查询信息
	public static final String COMMON1 = "' order by b.Id asc";

	//
	public static final String SELECT_Stcokadd = " * from tb_Stcokadd a where a.ClientId = ";

	//
	public static final String SELECT_STCOKADD1 = " order by a.Id desc) b where b.ClientId= ";

	//
	public static final String SELECT_Follw = " * from tb_Follw a where a.StcokaddID= ";

	//
	public static final String SELECT_Follw1 = " order by a.Id desc) b where b.StcokaddID= ";

	//
	public static final String SELECT_Call = " * from tb_Call a where a.StcokaddID = ";

	//
	public static final String SELECT_Call1 = " order by a.Id desc) b where b.StcokaddID= ";

	//
	public static final String SELECT_Need = "  * from tb_Need a where a.ClientId= ";

	//
	public static final String SELECT_Need1 = " order by a.Id desc) b where b.ClientId= ";

	// 条件查询
	public static final String COMMON = " order by b.Id asc";

	// 删除客户信息
	public static final String DELETE_CLIENT = "delete from tb_client where Id = ?";

	// 按照id去查询客户信息
	public static final String SELECT_CLIENT1 = "select * from tb_client where Id = ?";

	// 更新客户信息
	public static final String UPDATE_CLIENT = "update tb_client set ClientName=?,Adress=?,"
			+ "Telephone=?,Fax=?,Eamil=?,Net=?,Calling=?,Demand=?,Money=?,Explain=?,Linkman=?,Sex=?,Duty=?,Tel=?,Remark=? where Id=?";

	// 添加采购信息
	public static final String ADD_Stcokadd = "insert into tb_Stcokadd values(?,?,?,?,?)";

	// 添加跟进信息
	public static final String ADD_Follow = "insert into tb_Follw values(?,?,?,?,?)";

	// 添加回访信息
	public static final String ADD_Call = "insert into tb_Call values(?,?,?,?,?)";

	// 添加客户需求信息
	public static final String ADD_Need = "insert into tb_Need values(?,?,?,?)";

	// 按照需求表id删除客户需求信息
	public static final String DELETE_Need = "delete from tb_Need where Id=?";

	// 按照客户id删除客户需求信息
	public static final String DELETE_Need1 = "delete from tb_Need where ClientId=?";

	// 按照id删除回访信息
	public static final String DELETE_Call = "delete from tb_Call where Id=?";

	// 按照采购项目id删除回访信息
	public static final String DELETE_Call1 = "delete from tb_Call where StcokaddID in (select Id from tb_Stcokadd where ClientId=?)";

	// 按照客户id删除采购信息
	public static final String DELETE_Stock = "delete from tb_Stcokadd where ClientId=?";

	// 按照id删除采购信息
	public static final String DELETE_Stock1 = "delete from tb_Stcokadd where Id=?";

	// 查询表中的总行数
	public static final String CLIENT_COUNT = "select count(*) from ";

	// 按照客户id查询表中的总行数
	public static final String STOCK_COUNT = " where ClientId=";

	// 按照采购的id查询总行数
	public static final String FOLLOW_COUNT = " where StcokaddID=";

	// 按照采购的id查询总行数
	public static final String CALL_COUNT = " where StcokaddID=";

	// 按照客户id查询表中的总行数
	public static final String NEED_COUNT = " where ClientId=";

	// 按照采购项目id删除记录
	public static final String DELETE_FOLLOW = "delete from tb_Follw where StcokaddID in (select Id from tb_Stcokadd where ClientId=?)";

	// 按照跟进记录id删除信息
	public static final String DELETE_FOLLOW1 = "delete from tb_Follw where Id=?";
	

	/** *********************************************人员管理模块的sql********************************************* */
	
	public static final String ADDPERSONSQL = "INSERT INTO tb_person VALUES"
			+ "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,default)";
	
	public static final String SELECTIDBYPERID = "SELECT Id FROM tb_person WHERE PerID = ?";
	public static final String SEARCHPERSON = "SELECT * FROM tb_person WHERE 1 =1";

	public static final String UPDATEPERSON = "UPDATE tb_person SET Name=?,Branch=?,"
			+ "Duty=?,Sex=?,BornDate=?,Nation=?,Health=?,BornSite=?,Diploma=?,FinishSchool=?,"
			+ "Address=?,Tel=?,IdCard=?,Photo=?,Password=default where PerId = ?";

	public static final String DELETEPERSON = "DELETE FROM tb_person WHERE PerId = ?";
	
	public static final String UPDATEPASSWORD = "UPDATE tb_person SET Password = ? WHERE Id = ?";

	public static final String SEARCH = "查询";
	
	/***********************************工作管理*****************************/
	public static final String SQLBRANCHTABLE = "select * from BranchTable";
	public static final String SQLEMPTABLE = "select * from tb_person where Branch = ?";
	
	public static final String FORMATDATE1 = "yyyy-MM-dd hh:mm:ss";
	public static final String FORMATDATE2 = "yyyy-MM-dd";
	
	public static final String ADDWORKJILUTABLE = "insert into WorkJiLuTable values(?,?,?,?,?,?,?,?,?)";

	public static final String ADDJINDUTABLE = "insert into WorkJinDuTable values(?,?,?,?,?,?)";
}