📄 dbconn.cs
字号:
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
namespace XFMAS
{
/// <summary>
/// DBConn 的摘要说明。
/// </summary>
public class DBConn
{
public DBConn(){}
/// <summary>
/// 检查数据库是否可以使用
/// </summary>
/// <returns></returns>
public static bool DbIsAvailable(){
try{
Public.Conn(Public.configXml.Rows[0]["SqlServer"].ToString(),Public.configXml.Rows[0]["SqlUser"].ToString(),Public.DeCode("ESST_PWD",Public.configXml.Rows[0]["SqlPwd"].ToString()));
//SqlDataAdapter sda = new SqlDataAdapter("select * from ESST_SystemInfo",Public.conn);
//DataSet _ds = new DataSet();
//sda.Fill(_ds,"Temp");
//_ds = null;
return true;
}catch{
return false;
}
}
#region 查询数据
/// <summary>
/// 今日
/// </summary>
/// <param name="userID"></param>
/// <param name="username"></param>
/// <param name="userIP"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <returns></returns>
public static DataTable TodayItem(string startDate,string endDate){
//建表
DataTable table = new DataTable("Results");
DataColumn col = new DataColumn("姓名/内网用户名");
col.DataType = System.Type.GetType("System.String");
table.Columns.Add(col);
col = new DataColumn("早餐");
col.DataType = System.Type.GetType("System.String");
table.Columns.Add(col);
col = new DataColumn("午餐");
col.DataType = System.Type.GetType("System.String");
table.Columns.Add(col);
col = new DataColumn("晚餐");
col.DataType = System.Type.GetType("System.String");
table.Columns.Add(col);
//构建条件从数据库中返回数据
string sql = "select * from ESST_MAS_Results where autoID > 0";
sql += " and doDateTime >= '"+ startDate +"' and doDateTime <= '"+ endDate +"'";
SqlDataAdapter sda = new SqlDataAdapter(sql,Public.conn);
DataSet ds = new DataSet();
sda.Fill(ds,"Rs");
ArrayList users = new ArrayList();
//取出所有用户名
for(int i=0;i<ds.Tables[0].Rows.Count;i++){
DataRow row = ds.Tables[0].Rows[i];
bool isSame = false;
for(int n=0;n<users.Count;n++){
if(users[n].ToString() == row["doUserID"].ToString()){
isSame = true;
break;
}
}
if(!isSame) users.Add(row["doUserID"].ToString());
}
int n001 = 0;
int n002 = 0;
int n003 = 0;
//装入数据
for(int i=0;i<users.Count;i++){
//空行
DataRow row = table.NewRow();
//用户名
string _userID = users[i].ToString();
row[0] = DBConn.GetUserRealName(_userID) + "/" + _userID;
foreach(DataRow r in ds.Tables[0].Rows){
if(r["doUserID"].ToString() == _userID){
if(r["itemClassID"].ToString() == "001"){
row[1] = "√";
n001++;
}else if(r["itemClassID"].ToString() == "002"){
row[2] = "√";
n002++;
}else if(r["itemClassID"].ToString() == "003"){
row[3] = "√";
n003++;
}
}
}
table.Rows.Add(row);
}
DataRow _row = table.NewRow();
_row[0] = "合计";
_row[1] = n001.ToString();
_row[2] = n002.ToString();
_row[3] = n003.ToString();
table.Rows.Add(_row);
return table;
}
/// <summary>
/// 按条件查询数据
/// </summary>
/// <param name="userID"></param>
/// <param name="username"></param>
/// <param name="userIP"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <returns></returns>
public static DataSet GetResults(string userID,string username,string userIP,string startDate,string endDate){
//建表
DataTable table = new DataTable("Results");
DataColumn col = new DataColumn("日期");
col.DataType = System.Type.GetType("System.String");
table.Columns.Add(col);
col = new DataColumn("姓名/内网用户名");
col.DataType = System.Type.GetType("System.String");
table.Columns.Add(col);
col = new DataColumn("早餐");
col.DataType = System.Type.GetType("System.String");
table.Columns.Add(col);
col = new DataColumn("午餐");
col.DataType = System.Type.GetType("System.String");
table.Columns.Add(col);
col = new DataColumn("晚餐");
col.DataType = System.Type.GetType("System.String");
table.Columns.Add(col);
col = new DataColumn("确认时间");
col.DataType = System.Type.GetType("System.String");
table.Columns.Add(col);
//用户姓名
username = DBConn.GetUserRealName(userID);
//构建条件从数据库中返回数据
string sql = "select * from ESST_MAS_Results where autoID > 0";
if(userID != ""){
sql += " and doUserID = '"+ userID +"'";
}
if(userIP != ""){
sql += " and userIP = '"+ userIP +"'";
}
sql += " and doDateTime >= '"+ startDate +"' and doDateTime <= '"+ endDate +"'";
SqlDataAdapter sda = new SqlDataAdapter(sql,Public.conn);
DataSet ds = new DataSet();
sda.Fill(ds,"Rs");
//按条件倒入数据到表中
DateTime sd = Convert.ToDateTime(startDate);
DateTime ed = Convert.ToDateTime(endDate);
int n001 = 0;
int n002 = 0;
int n003 = 0;
//循环取出数据
for(DateTime i=sd;i<=ed;i = i.AddDays(1)){
DataRow row = table.NewRow();
string _date = i.Year.ToString() + "-" + i.Month.ToString() + "-" + i.Day.ToString();
DateTime _sd = Convert.ToDateTime(_date + " 0:00:00");
DateTime _ed = Convert.ToDateTime(_date + " 23:59:59");
for(int j=0;j<ds.Tables[0].Rows.Count;j++){
DateTime dbDate = Convert.ToDateTime(ds.Tables[0].Rows[j]["doDateTime"].ToString());
if(dbDate > _sd && dbDate < _ed){
if(ds.Tables[0].Rows[j]["itemClassID"].ToString() == "001"){
row["早餐"] = "√";
n001 ++;
}else if(ds.Tables[0].Rows[j]["itemClassID"].ToString() == "002"){
row["午餐"] = "√";
n002++;
}else if(ds.Tables[0].Rows[j]["itemClassID"].ToString() == "003"){
row["晚餐"] = "√";
n003++;
}
row["确认时间"] = ds.Tables[0].Rows[j]["doDateTime"].ToString();
}
}
row["日期"] = _date;
row["姓名/内网用户名"] = userID + "("+ username +")";
table.Rows.Add(row);
}
DataRow _row = table.NewRow();
_row["日期"] = "合计";
_row["姓名/内网用户名"] = "";
_row["早餐"] = n001.ToString();
_row["午餐"] = n002.ToString();
_row["晚餐"] = n003.ToString();
_row["确认时间"] = "";
table.Rows.Add(_row);
//返回结果
DataSet result = new DataSet();
result.Tables.Add(table);
return result;
}
/// <summary>
/// 返回某月的统计数字
/// </summary>
/// <param name="date"></param>
/// <returns></returns>
public static DataSet GetResultsByMonth(DateTime date,string userID){
DataTable table = new DataTable("Results");
DataColumn col = new DataColumn("日期");
col.DataType = System.Type.GetType("System.String");
table.Columns.Add(col);
col = new DataColumn("早 餐 ");
col.DataType = System.Type.GetType("System.Int32");
table.Columns.Add(col);
col = new DataColumn("午 餐 ");
col.DataType = System.Type.GetType("System.Int32");
table.Columns.Add(col);
col = new DataColumn("晚 餐 ");
col.DataType = System.Type.GetType("System.Int32");
table.Columns.Add(col);
int days = Int32.Parse(Public.GetMonthEndDay(date.Month.ToString()));
//早、中、晚餐数量
int n001 = 0;
int n002 = 0;
int n003 = 0;
//循环取数据
for(int i=1;i<= days;i++){
//新建一行
DataRow row = table.NewRow();
//日期
row["日期"] = date.Year.ToString() + "年" + date.Month.ToString() + "月" + i.ToString() + "日";
string sql = "";
string dat = date.Year.ToString() + "-" + date.Month.ToString() + "-" + i.ToString();
//早餐 ==================================
sql = "select count(*) as NC from ESST_MAS_Results where itemClassID = '001' and doDateTime >= '"+ dat + " 0:00:00" +"' and doDateTime <= '"+ dat + " 23:59:59" +"'";
if(userID != null){
sql += " and doUserID = '"+ userID +"'";
}
SqlDataAdapter sda = new SqlDataAdapter(sql,Public.conn);
DataSet ds = new DataSet();
sda.Fill(ds,"NCS");
int _n001 = Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
n001 += _n001;
//早餐数量
row["早 餐 "] = _n001;
//午餐 ==================================
sql = "select count(*) as NC from ESST_MAS_Results where itemClassID = '002' and doDateTime >= '"+ dat + " 0:00:00" +"' and doDateTime <= '"+ dat + " 23:59:59" +"'";
sda = new SqlDataAdapter(sql,Public.conn);
ds = new DataSet();
sda.Fill(ds,"NCS");
int _n002 = Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
n002 += _n002;
//午餐数量
row["午 餐 "] = _n002;
//晚餐 ==================================
sql = "select count(*) as NC from ESST_MAS_Results where itemClassID = '003' and doDateTime >= '"+ dat + " 0:00:00" +"' and doDateTime <= '"+ dat + " 23:59:59" +"'";
sda = new SqlDataAdapter(sql,Public.conn);
ds = new DataSet();
sda.Fill(ds,"NCS");
int _n003 = Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
n003 += _n003;
//晚餐数量
row["晚 餐 "] = _n003;
//将新行放入表中
table.Rows.Add(row);
}
DataRow _row = table.NewRow();
_row["日期"] = "合计";
_row["晚 餐 "] = n003;
_row["早 餐 "] = n001;
_row["午 餐 "] = n002;
table.Rows.Add(_row);
DataSet result = new DataSet();
result.Tables.Add(table);
return result;
}
#endregion
#region 用户登录模块
/// <summary>
/// 使用用户名或用户编号验证用户有效性
/// </summary>
/// <param name="userName"></param>
/// <param name="userID"></param>
/// <param name="pwd"></param>
/// <returns></returns>
public static bool CheckUserByUser(string userName,string pwd){
//Public.conn.Open();
//SqlCommand cmd = new SqlCommand("select * from ESST_MAS_Users where ","");
return false;
}
/// <summary>
/// 使用IP地址验证用户，如果库中只有一个IP对应一个用户则直接登录
/// 否则要求用户选择用户并输入密码
/// 这里有用户名密码来自于消防内网中的办公系统
/// </summary>
/// <param name="ipAddress"></param>
/// <returns></returns>
public static DataTable CheckUserByIP(string ipAddress){
return null;
}
#endregion
#region 提醒操作结果的数据库操作
/// <summary>
/// 保存用户的选择
/// </summary>
/// <param name="alertDate">提醒时间</param>
/// <param name="itemClassID">类目类型：午餐、晚餐或早餐</param>
/// <param name="doUserID">操作用户ID</param>
/// <param name="doDateTime">操作时间</param>
/// <param name="doIP">操作者的IP地址</param>
/// <returns>操作结果</returns>
public static string SaveSelected(string alertDate,string itemClassID,string doUserID,string doDateTime,string doIP){
string result = "OK";
Public.conn.Open();
try{
SqlCommand cmd = new SqlCommand("insert into ESST_MAS_Results (itemClassID,doUserID,doDateTime,doIP,name) values ('"+ itemClassID +"','"+ doUserID +"','"+ doDateTime +"','"+ doIP +"','"+ DBConn.GetUserRealName(doUserID) +"')",Public.conn);
cmd.ExecuteNonQuery();
}catch(Exception ex){
result = "保存错误：" + ex.Message.ToString();
}finally{
Public.conn.Close();
}
return result;
}
/// <summary>
/// 管理员修改
/// </summary>
/// <param name="alertDate"></param>
/// <param name="itemClassID"></param>
/// <param name="doUserID"></param>
/// <param name="doDateTime"></param>
/// <param name="doIP"></param>
/// <returns></returns>
public static string SaveSelected_(string itemClassID,string doUserID,string doDateTime){
string result = "OK";
//先删除
string s=DBConn.DeleteSelected_(itemClassID,doUserID,doDateTime);
if(s != "OK") throw new Exception(s);
Public.conn.Open();
try{
SqlCommand cmd = new SqlCommand("insert into ESST_MAS_Results (itemClassID,doUserID,doDateTime,name,adminMod) values ('"+ itemClassID +"','"+ doUserID +"','"+ doDateTime + " 1:01:01" +"','"+ DBConn.GetUserRealName(doUserID) +"',1)",Public.conn);
cmd.ExecuteNonQuery();
//System.Windows.Forms.MessageBox.Show("insert into ESST_MAS_Results (itemClassID,doUserID,doDateTime,name,adminMod) values ('"+ itemClassID +"','"+ doUserID +"','"+ doDateTime + " 1:01:01" +"','"+ DBConn.GetUserRealName(doUserID) +"',1)");
}catch(Exception ex){
result = "保存错误：" + ex.Message.ToString();
}finally{
Public.conn.Close();
}
return result;
}
public static string DeleteSelected_(string itemClassID,string doUserID,string doDateTime){
string result = "OK";
Public.conn.Open();
try{
//先清除
SqlCommand cmd = new SqlCommand("delete from ESST_MAS_Results where itemClassID = '"+ itemClassID +"' and ((doDateTime < '"+ doDateTime + " 23:59:59" +"' and doDateTime > '"+ doDateTime + " 0:00:00" +"') or doDateTime = '"+ doDateTime +"') and doUserID = '"+ doUserID +"'",Public.conn);
cmd.ExecuteNonQuery();
}catch(Exception ex){
result = "保存错误：" + ex.Message.ToString();
}finally{
Public.conn.Close();
}
return result;
}
/// <summary>
/// 删除用户的选择
/// </summary>
/// <param name="alertDate">提醒时间</param>
/// <param name="itemClassID">类目类型：午餐、晚餐或早餐</param>
/// <param name="doUserID">操作用户ID</param>
/// <param name="doDateTime">操作时间</param>
/// <param name="doIP">操作者的IP地址</param>
/// <returns>操作结果</returns>
public static string DeleteSelected(string alertDate,string itemClassID,string doUserID,string doDateTime,string doIP){
string result = "OK";
try{
Public.conn.Open();
//今日
SqlCommand cmd = new SqlCommand("delete from ESST_MAS_Results where doUserID='"+ doUserID +"' and doDateTime >= '"+ DBConn.GetServerTime().Date.ToString() +"' and doDateTime <= '"+ DBConn.GetServerTime().Date.ToString().Replace(" 0:00:00","") +" 23:59:59' and (itemClassID='002' or itemClassID='003')",Public.conn);
cmd.ExecuteNonQuery();
string nowDate = DBConn.GetServerTime().Date.AddDays(1).ToString();
//明日
cmd = new SqlCommand("delete from ESST_MAS_Results where doUserID='"+ doUserID +"' and doDateTime >= '"+ nowDate +"' and doDateTime <= '"+ nowDate.Replace(" 0:00:00","") +" 23:59:59' and itemClassID='001'",Public.conn);
cmd.ExecuteNonQuery();
Public.conn.Close();
}catch(Exception ex){
result = ex.Message.ToString();
}
return result;
}
/// <summary>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -