dbconn.cs

食堂管理提示系统。适用于各种环境下。能自动完成各种统计和计算工作。

		/// 按条件筛选提醒操作的结果
		/// </summary>
		/// <param name="startDate">操作始于日期</param>
		/// <param name="endDate">操作止于日期</param>
		/// <param name="itemClassID">类目类型：午餐、晚餐或早餐</param>
		/// <param name="userID">操作用户</param>
		/// <param name="ipAddress">操作用户的IP地址</param>
		/// <returns>返回数据记录集</returns>
		public static DataSet Results(string startDate,string endDate,string itemClassID,string userID,string ipAddress){
			
			return null;

		}

		#endregion

		/// <summary>
		/// 检查内网的用户名和密码是否正确
		/// </summary>
		/// <param name="userName"></param>
		/// <param name="pwd"></param>
		/// <returns></returns>
		public static bool UserIsRight(string userName,string pwd){

			bool result = true;
			
			//连接问题
			string[] udbs = Public.usersDbInfo.Split('|');

			//连接
			SqlConnection conn = new SqlConnection(udbs[0].ToString());

			SqlDataAdapter sda = new SqlDataAdapter("select * from account where email='"+ userName +"' and password='"+ pwd +"'",conn);
			DataSet ds = new DataSet();
			sda.Fill(ds,"u");
			if(ds.Tables.Count >0 && ds.Tables[0].Rows.Count > 0) {
				//
			}else{
				result = false;
			}

			conn.Close();
			ds = null;

			return result;
		}

		/// <summary>
		/// 注册IP
		/// </summary>
		/// <param name="userName"></param>
		/// <param name="?"></param>
		/// <returns></returns>
		public static string SaveUserIP(string userName,string userIP){

			try{
				//检查是否已存在
				SqlDataAdapter sda = new SqlDataAdapter("select * from ESST_MAS_Users where userName='"+ userName +"' and userIP='"+ userIP +"'",Public.conn);
				DataSet ds = new DataSet();
				sda.Fill(ds);
				 
				if(ds.Tables.Count == 1 && ds.Tables[0].Rows.Count == 0){

					//保存
					Public.conn.Open();
					SqlCommand cmd = new SqlCommand("insert into ESST_MAS_Users (userName,userIP) values ('"+ userName +"','"+ userIP +"')",Public.conn);
					cmd.ExecuteNonQuery();
					Public.conn.Close();
				}

				ds  = null;
				sda = null;

				return "OK";

			}catch(Exception ex){
				return ex.Message.ToString();
			}
		}

		/// <summary>
		/// 根据IP返回这个IP绑定的用户
		/// </summary>
		/// <param name="ip"></param>
		/// <returns></returns>
		public static DataTable UsersForIP(String ip){
			DataTable result = null;
			try{

				//SQL
				string sql = "select * from ESST_MAS_Users";

				if(ip != null){
					sql += " where userIP='"+ ip +"'";
				}

				SqlDataAdapter sda = new SqlDataAdapter(sql,Public.conn);

				DataSet ds = new DataSet();
				sda.Fill(ds,"users");
				result = ds.Tables[0].Copy();
			}catch(Exception ex){
				//System.Windows.Forms.MessageBox.Show(ex.Message.ToString());
			}

			return result;
		}

		/// <summary>
		/// 根据用户ID返回用户姓名
		/// </summary>
		/// <param name="username"></param>
		/// <returns></returns>
		public static string GetUserRealName(string userName){
			
			string result = "";
			
			//连接问题
			string[] udbs = Public.usersDbInfo.Split('|');

			//连接
			SqlConnection conn = new SqlConnection(udbs[0].ToString());
			
			SqlDataAdapter sda = new SqlDataAdapter("select * from account where email='"+ userName +"'",conn);
			DataSet ds = new DataSet();
			sda.Fill(ds,"u");

			if(ds.Tables.Count >0 && ds.Tables[0].Rows.Count > 0) {
				result = ds.Tables[0].Rows[0]["name"].ToString();
			}

			ds = null;

			return result;

		}
		
		/// <summary>
		/// 取出所有用户数据
		/// </summary>
		/// <returns></returns>
		public static DataSet GetAllUsers(){

			//连接问题
			string[] udbs = Public.usersDbInfo.Split('|');

			//连接
			SqlConnection conn = new SqlConnection(udbs[0].ToString());
			conn.Open();
			SqlDataAdapter sda = new SqlDataAdapter(udbs[4].ToString(),conn);
			DataSet ds = new DataSet();
			sda.Fill(ds,"u");
			conn.Close();

			return ds;
		}

		/// <summary>
		/// 获取服务器时间
		/// </summary>
		/// <returns></returns>
		public static DateTime GetServerTime(){
			try{
				SqlDataAdapter sda = new SqlDataAdapter("select GetDate() as ServerDate",Public.conn);
				DataSet ds = new DataSet();
				sda.Fill(ds,"ServerDate");
				return Convert.ToDateTime(ds.Tables[0].Rows[0][0].ToString());
			}catch(Exception ex){
			//	System.Windows.Forms.MessageBox.Show("获取服务器时间时出错：" + ex.Message.ToString());
				return DateTime.Now;
			}
		}

		/// <summary>
		/// 获取提醒时间段
		/// </summary>
		/// <returns></returns>
		public static DataTable GetAlertTime(){

			try{
				SqlDataAdapter sda = new SqlDataAdapter("select startTime,endTime,isMod from ESST_AlertTime where itemID='001'",Public.conn);
				DataSet ds = new DataSet();
				sda.Fill(ds,"ServerDate");
				return ds.Tables[0].Copy();
			}catch(Exception ex){
			//	System.Windows.Forms.MessageBox.Show("获取提醒时间段时出错：" + ex.Message.ToString());
				return null;
			}
		}

		/// <summary>
		/// 更新提醒时间段 
		/// </summary>
		/// <param name="startTime"></param>
		/// <param name="endTime"></param>
		/// <param name="isMod"></param>
		/// <returns></returns>
		public static string UpdateAlertTime(string startTime,string endTime,string isMod){

			string result = "OK";
			Public.conn.Open();

			try{
				SqlCommand cmd = new SqlCommand("update ESST_AlertTime set startTime='"+ startTime +"',endTime='"+ endTime +"',isMod="+ isMod +"",Public.conn);
				cmd.ExecuteNonQuery();
			}catch(Exception ex){
				result = ex.Message.ToString();
			}finally{
				Public.conn.Close();
			}

			return result;
		}

		/// <summary>
		/// 获取今日的选择
		/// </summary>
		/// <param name="userID"></param>
		/// <returns></returns>
		public static DataTable GetTodaySelected(string userID){
		
			string today = DBConn.GetServerTime().Date.ToString().Replace(" 0:00:00","");
			string tomow = DBConn.GetServerTime().Date.AddDays(1).ToString().Replace(" 0:00:00","");

			string sql = "select * from ESST_MAS_Results where ((itemClassID = '002' or itemClassID = '003') and doUserID = '"+ userID +"' and (doDateTime >= '"+ today + " 0:00:00" +"' and doDateTime <= '"+ today + " 23:59:59" +"')) or (itemClassID = '001') and doUserID = '"+ userID +"' and (doDateTime >= '"+ tomow + " 0:00:00" +"' and doDateTime <= '"+ tomow + " 23:59:59" +"')";

			SqlDataAdapter sda = new SqlDataAdapter(sql,Public.conn);
			DataSet ds = new DataSet();
			sda.Fill(ds,"ServerDate");
			return ds.Tables[0].Copy();
		}

		/// <summary>
		/// 某日
		/// </summary>
		/// <param name="userID"></param>
		/// <returns></returns>
		public static DataTable GetDaySelected(string userID,DateTime date){
		
			string today = date.Date.ToString().Replace(" 0:00:00","");
			string tomow = date.Date.AddDays(1).ToString().Replace(" 0:00:00","");

			string sql = "select * from ESST_MAS_Results where ((itemClassID = '002' or itemClassID = '003') and doUserID = '"+ userID +"' and (doDateTime >= '"+ today + " 0:00:00" +"' and doDateTime <= '"+ today + " 23:59:59" +"')) or (itemClassID = '001') and doUserID = '"+ userID +"' and (doDateTime >= '"+ tomow + " 0:00:00" +"' and doDateTime <= '"+ tomow + " 23:59:59" +"')";

			SqlDataAdapter sda = new SqlDataAdapter(sql,Public.conn);
			DataSet ds = new DataSet();
			sda.Fill(ds,"ServerDate");
			return ds.Tables[0].Copy();
		}

		/// <summary>
		/// 获取系统信息
		/// </summary>
		/// <returns></returns>
		public static DataSet GetSystemInfo(){
			string sql = "select * from ESST_SystemInfo";
			SqlDataAdapter sda = new SqlDataAdapter(sql,Public.conn); 
			DataSet ds = new DataSet();
			sda.Fill(ds,"si");
			return ds;
		}
		

		/// <summary>
		/// 验证用户表数据库的连接是否可用
		/// </summary>
		/// <returns></returns>
		public static string CheckUsersDBConn(){

			string result = "OK";

			try{

				//连接问题
				string[] udbs = Public.usersDbInfo.Split('|');

				//连接
				SqlConnection conn = new SqlConnection(udbs[0].ToString());

				conn.Open();
				conn.Close();

			}catch(Exception ex){
				result = ex.Message.ToString();
			}

			return result;
		}

		/// <summary>
		/// 查找用户
		/// </summary>
		/// <param name="userID"></param>
		/// <param name="userIP"></param>
		/// <returns></returns>
		public static DataSet UsersIPs(string userID,string userIP){
			
			string sql = "select * from ESST_MAS_Users where autoID > 0";

			if(userID != null && userID != ""){
				sql += " and userName = '"+ userID +"'";
			}

			if(userIP != null && userIP != ""){
				sql += " and userIP = '"+ userIP +"'";
			}

			SqlDataAdapter sda = new SqlDataAdapter(sql,Public.conn);
			DataSet ds = new DataSet();
			sda.Fill(ds,"USERs");

			return ds;
		}

		/// <summary>
		/// 删除用户对应的IP
		/// </summary>
		/// <param name="autoID"></param>
		/// <returns></returns>
		public static string DelUserIp(string autoID){

			string result = "OK";

			Public.conn.Open();

			try{
				SqlCommand cmd = new SqlCommand("delete from ESST_MAS_Users where autoID = "+ autoID +"",Public.conn);
				cmd.ExecuteNonQuery();
			}catch(Exception ex){
				result = ex.Message.ToString();
			}finally{
				Public.conn.Close();
			}

			return result;
		}

		/// <summary>
		/// 删除用户对应的IP
		/// </summary>
		/// <param name="autoID"></param>
		/// <returns></returns>
		public static string UpdateUserAdmin(string autoID,string isAdmin){

			string result = "OK";

			Public.conn.Open();

			try{
				SqlCommand cmd = new SqlCommand("update ESST_MAS_Users set isAdmin='"+ isAdmin +"' where autoID = "+ autoID +"",Public.conn);
				cmd.ExecuteNonQuery();
			}catch(Exception ex){
				result = ex.Message.ToString();
			}finally{
				Public.conn.Close();
			}

			return result;
		}

		/// <summary>
		/// 检查某用户是否管理员
		/// </summary>
		/// <param name="userID"></param>
		/// <returns></returns>
		public static bool CheckUserIsAdmin(string userID){

			bool result = false;
			
			SqlDataAdapter sda = new SqlDataAdapter("select * from ESST_MAS_USERS where username='"+ userID +"' and isAdmin=1",Public.conn);
			DataSet ds = new DataSet();
			sda.Fill(ds,"User");
			if(ds.Tables[0].Rows.Count > 0){
				result = true;
			}

			ds = null;

			return result;
		}

		/// <summary>
		/// 返回管理员权限代码
		/// </summary>
		/// <param name="userID"></param>
		/// <returns></returns>
		public static string GetAdminRuleCode(string userID){

			string result = null;

			try{
				SqlDataAdapter sda = new SqlDataAdapter("select * from ESST_RuleCodes where userName='"+ userID +"'",Public.conn);
				DataSet ds = new DataSet();
				sda.Fill(ds,"User");
				if(ds.Tables[0].Rows.Count > 0){
					result = ds.Tables[0].Rows[0]["ruleCodes"].ToString();

					if(result == "") result = null;
				}
				ds = null;
			}catch(Exception ex){
				throw new Exception("无法获取管理员权限代码，操作不能继续！\n\n系统异常信息为：" + ex.Message.ToString());
			}

			return result;
		}

		/// <summary>
		/// 删除用户对应的IP
		/// </summary>
		/// <param name="autoID"></param>
		/// <returns></returns>
		public static string UpdateAdminRuleCode(string userName,string rcs){

			string result = "OK";

			Public.conn.Open();

			try{

				SqlCommand cmd = new SqlCommand("delete from ESST_RuleCodes where userName = '"+ userName +"'",Public.conn);
				cmd.ExecuteNonQuery();

				cmd = new SqlCommand("insert into ESST_RuleCodes (userName,ruleCodes) values ('"+ userName +"','"+ rcs.Replace("'","''") +"')",Public.conn);
				cmd.ExecuteNonQuery();
			}catch(Exception ex){
				result = ex.Message.ToString();
			}finally{
				Public.conn.Close();
			}

			return result;
		}

		/// <summary>
		/// 删除用户对应的IP
		/// </summary>
		/// <param name="autoID"></param>
		/// <returns></returns>
		public static string DeleteAdminRuleCode(string userName){

			string result = "OK";

			Public.conn.Open();

			try{

				SqlCommand cmd = new SqlCommand("delete from ESST_RuleCodes where userName = '"+ userName +"'",Public.conn);
				cmd.ExecuteNonQuery();

			}catch(Exception ex){
				result = ex.Message.ToString();
			}finally{
				Public.conn.Close();
			}

			return result;
		}

		/// <summary>
		/// 登记修改日志以备查，默认取出最后一条记录
		/// </summary>
		/// <param name="doDate"></param>
		/// <param name="results"></param>
		/// <param name="userName"></param>
		/// <param name="doUserName"></param>
		public static string SaveEditLog(string doDate,string results,string userName,string doUserName){

			string result = "OK";
			Public.conn.Open();
			try{
				SqlCommand cmd = new SqlCommand("insert into ESST_MAS_EditLog (doDate,results,userName,realName,doUserName,doRealName) values ('"+ doDate +"','"+ results +"','"+ userName +"','"+ DBConn.GetUserRealName(userName) +"','"+ doUserName +"','"+ DBConn.GetUserRealName(doUserName) +"')",Public.conn);
				cmd.ExecuteNonQuery();
			}catch(Exception ex){
				result = ex.Message.ToString();
			}finally{
				Public.conn.Close();
			}

			return result;
		}

		/// <summary>
		/// 获取最后的修改日志
		/// </summary>
		/// <returns></returns>
		public static string[] GetLastEditLog(string userName,string doDate){

			string[] result = null;

			SqlDataAdapter sda = new SqlDataAdapter("select top 1 * from ESST_MAS_EditLog where userName = '"+ userName +"' and doDate > = '"+ doDate + " 0:00:00" +"' and doDate <= '"+ doDate + " 23:59:59" +"' order by doDate desc",Public.conn);
			DataSet ds = new DataSet();
			sda.Fill(ds,"el");
			if(ds.Tables[0].Rows.Count == 1){
				result = new string[2];
				result[0] = ds.Tables[0].Rows[0]["doUserName"].ToString() + "("+ ds.Tables[0].Rows[0]["doRealName"].ToString() +")";
			}

			return result;
		}

	}
}