/// <summary> /// 更改用户密码 /// </summary> /// <param name="user">用户名</param> /// <param name="pw">密码</param> /// <returns>是否成功</returns> public static bool PwdSet(string user, string pw) { string sql = "UPDATE [WebApp].[dbo].[DL_用户名]SET [密码] =@参数一 WHERE 用户名='" + user.Trim() + "'"; SqlParameter par1 = new SqlParameter("@参数一", Sys.getMd5(pw.Trim())); SqlParameter[] par = { par1 }; DAL dal = new DAL(); dal.RunSql(sql, par); int n = dal.ExecuteRowCount; if (n > 0) { return (true); } else { return (false); } }
/// <summary> /// 新增用户 /// </summary> /// <param name="name">用户名</param> /// <param name="context"></param> public void adduser(string name, HttpContext context) { string nono = "对不起,你没有权限进行此操作!"; string[] xy = { "【运行所】", "【系统管理员】" }; //需要的权限才能操作 if (testQX(xy, context) == false) { context.Response.Write(nono); return; } string SQL = "INSERT INTO [WebApp].[dbo].[DL_用户名]([用户名],[密码],[权限])VALUES('{0}','f379eaf3c831b04de153469d1bec345e','【普通】')"; SQL = string.Format(SQL, name.Trim()); DAL dal = new DAL(); bool ok = dal.RunSqlExists("SELECT [ID] FROM [WebApp].[dbo].[DL_用户名] where 用户名='" + name.Trim() + "'"); if (ok == true) { context.Response.Write("添加失败:用户[" + name.Trim() + "]已经存在!"); } else { dal.RunSql(SQL); if (dal.ExecuteRowCount == 1) { setupUserML(name); Sys.addSteps(getname(context), "新增用户:" + name.Trim(), "[用户管理]"); context.Response.Write("成功"); } else { context.Response.Write(dal.ErrorMsg); } } }
/// <summary> /// 插入版本控制记录 /// </summary> /// <param name="context"></param> public void VerAdd(HttpContext context) { string name = context.Request["name"]; string ver = context.Request["ver"]; string info = context.Request["info"]; string conf = context.Server.MapPath("~/Public/CNGconfig.xml"); string oldver = Sys.xmlRead(conf, "缓存配置", "全局版本", "0");//读取版本号 string retext = "{{ \"ok\":{0},\"msg\":\"{1}\",\"data\":@替换对象@ }}"; if (double.Parse(ver) <= double.Parse(oldver)) { retext = string.Format(retext, "false", "新版本号必须比老版本号大,更改数据失败!"); retext = retext.Replace("@替换对象@", "0"); context.Response.Write(retext); return; } string SQL = "INSERT INTO [WebApp].[dbo].[WEB_版本控制]([日期],[版本号],[更新说明],[操作人员]) VALUES(getdate(),@版本号,@更新说明,@操作人员)"; DAL dal = new DAL(); SqlParameter par1 = new SqlParameter("版本号", ver); SqlParameter par2 = new SqlParameter("更新说明", info); SqlParameter par3 = new SqlParameter("操作人员", name); dal.RunSql(SQL, new SqlParameter[] { par1, par2, par3 }); if (dal.ExecuteRowCount == 1) { Sys.xmlAdd(conf, "缓存配置", "全局版本", ver); retext = string.Format(retext, "true", "提交版本号成功!当前版本号:" + ver); retext = retext.Replace("@替换对象@", "0"); } else { retext = string.Format(retext, "false", "错误信息:" + dal.ErrorMsg); retext = retext.Replace("@替换对象@", "0"); } context.Response.Write(retext); Sys.DebugMes(retext); }
/// <summary> /// 处理出勤情况,生成出勤时间(分钟) /// </summary> /// <param name="dt"></param> /// <param name="dd"></param> public void KQ_出勤情况(DateTime dt, int dd) { DateTime 起始时间 = dt.AddDays(0 - dd); string SQL = "UPDATE [CNGTZ].[dbo].[TZ_人员_考勤表] SET [出勤情况] ='正常',[出勤时间] = datediff(mi,[上班签到],[下班签退]) WHERE [实到]=2 AND 日期 BETWEEN @起始 AND @结束"; SqlParameter par1 = new SqlParameter("起始", 起始时间); SqlParameter par2 = new SqlParameter("结束", dt); DAL dal = new DAL(); dal.RunSql(SQL, new SqlParameter[] { par1, par2 }); SQL = "UPDATE [CNGTZ].[dbo].[TZ_人员_考勤表] SET [出勤情况] ='异常' WHERE [实到]<>2 AND 日期 BETWEEN @起始 AND @结束"; dal.RunSql(SQL, new SqlParameter[] { par1, par2 }); if (this.SJ_指纹处理 != null) { Even_指纹系统处理 e = new Even_指纹系统处理(); e.mesg = "处理考勤情况【" + 起始时间.ToShortDateString() + " 至 " + dt.ToShortDateString() + "】到完成!"; //触发事件 this.SJ_指纹处理(this, e); } }
/// <summary> /// 删除用户 /// </summary> /// <param name="name">用户名</param> /// <param name="context"></param> public void deluser(string name, HttpContext context) { string nono = "对不起,你没有权限进行此操作!"; string[] xy = { "【运行所】", "【系统管理员】" }; //需要的权限才能操作 if (testQX(xy, context) == false) { context.Response.Write(nono); return; } if (name == null) { context.Response.Write(""); return; } string SQL = "DELETE FROM [WebApp].[dbo].[DL_用户名] WHERE 用户名='" + name.Trim() + "'"; DAL dal = new DAL(); dal.RunSql(SQL); if (dal.ExecuteRowCount == 1) { dal.RunSql("DELETE FROM [WebApp].[dbo].[DL_操作日志] WHERE [用户名]='" + name.Trim() + "'"); FileManager.DeleteFolder("E:\\CNGfile\\个人文档\\" + name.Trim()); Sys.addSteps(getname(context), "删除用户:" + name.Trim(), "[用户管理]"); context.Response.Write("成功"); } else { context.Response.Write(dal.ErrorMsg); } }
/// <summary> /// 设置修正值 /// </summary> /// <param name="context"></param> public void SetXzz(HttpContext context) { int zd = int.Parse(context.Request["zd"]); int bz = int.Parse(context.Request["bz"]); DateTime day = DateTime.Parse(context.Request["day"]); string name = context.Request["name"]; double val = double.Parse(context.Request["val"]); DAL dal = new DAL(); string SQL = "SELECT [ID] FROM [CNGQT].[dbo].[CW_每班修正] where 日期=@日期 and 站点=@站点 and 班组=@班组"; SqlParameter par1 = new SqlParameter("日期", day); SqlParameter par2 = new SqlParameter("站点", zd); SqlParameter par3 = new SqlParameter("班组", bz); SqlParameter par4 = new SqlParameter("val", val); string id = dal.RunSqlGetID(SQL, new SqlParameter[] { par1, par2, par3 }); if (id == null) { SQL = "INSERT INTO [CNGQT].[dbo].[CW_每班修正](日期,站点,班组,@参数@,更新时间)VALUES(@日期,@站点,@班组,@val,getdate())"; SQL = SQL.Replace("@参数@", name); dal.RunSql(SQL, new SqlParameter[] { par1, par2, par3, par4 }); } else { SQL = "UPDATE [CNGQT].[dbo].[CW_每班修正] SET @参数@ = @val,更新时间=getdate() WHERE ID=@ID"; SQL = SQL.Replace("@参数@", name); dal.RunSql(SQL, new SqlParameter[] { new SqlParameter("ID", id) ,par4}); } context.Response.Write("ok"); }
/// <summary> /// 新增一条数据信息 /// </summary> public void add() { string SQL = @"INSERT INTO [CNGTZ].[dbo].[TZ_人员_资质证书] ([人员ID] ,[自分类] ,[作业类别] ,[作业代号] ,[资格类型] ,[项目名称] ,[准操项目] ,[工种] ,[证书编号] ,[发证时间] ,[有效时间] ,[发证单位] ,[更新时间] ,[备注]) VALUES (@人员ID ,@自分类 ,@作业类别 ,@作业代号 ,@资格类型 ,@项目名称 ,@准操项目 ,@工种 ,@证书编号 ,@发证时间 ,@有效时间 ,@发证单位 ,getdate() ,@备注)"; SqlParameter par1 = new SqlParameter("@人员ID", _人员ID); SqlParameter par2 = new SqlParameter("@自分类", _自分类); SqlParameter par3 = new SqlParameter("@作业类别", _作业类别); SqlParameter par4 = new SqlParameter("@作业代号", _作业代号); SqlParameter par5 = new SqlParameter("@资格类型", _资格类型); SqlParameter par6 = new SqlParameter("@项目名称", _项目名称); SqlParameter par7 = new SqlParameter("@准操项目", _准操项目); SqlParameter par8 = new SqlParameter("@工种", _工种); SqlParameter par9 = new SqlParameter("@证书编号", _证书编号); SqlParameter par10 = _发证时间 == "" ? new SqlParameter("@发证时间", DBNull.Value) : new SqlParameter("@发证时间", DateTime.Parse(_发证时间)); SqlParameter par11 = _有效时间 == "" ? new SqlParameter("@有效时间", DBNull.Value) : new SqlParameter("@有效时间", DateTime.Parse(_有效时间)); SqlParameter par12 = new SqlParameter("@发证单位", _发证单位); SqlParameter par13 = new SqlParameter("@备注", _备注); DAL dal = new DAL(); try { dal.RunSql(SQL, new SqlParameter[] { par1, par2, par3, par4, par5, par6, par7, par8, par9, par10, par11, par12, par13 }); } catch (Exception e) { throw e; } }
/// <summary> /// 更新一条数据 /// </summary> public void update() { string SQL = @"UPDATE [CNGTZ].[dbo].[TZ_人员_资质证书] SET [自分类] = @自分类 ,[作业类别] = @作业类别 ,[作业代号] = @作业代号 ,[资格类型] = @资格类型 ,[项目名称] = @项目名称 ,[准操项目] = @准操项目 ,[工种] = @工种 ,[证书编号] = @证书编号 ,[发证时间] = @发证时间 ,[有效时间] = @有效时间 ,[发证单位] = @发证单位 ,[备注] = @备注 ,[更新时间] = getdate() WHERE ID=@ID"; SqlParameter par1 = new SqlParameter("@ID", id); SqlParameter par2 = new SqlParameter("@自分类", _自分类); SqlParameter par3 = new SqlParameter("@作业类别", _作业类别); SqlParameter par4 = new SqlParameter("@作业代号", _作业代号); SqlParameter par5 = new SqlParameter("@资格类型", _资格类型); SqlParameter par6 = new SqlParameter("@项目名称", _项目名称); SqlParameter par7 = new SqlParameter("@准操项目", _准操项目); SqlParameter par8 = new SqlParameter("@工种", _工种); SqlParameter par9 = new SqlParameter("@证书编号", _证书编号); SqlParameter par10 = _发证时间 == "" ? new SqlParameter("@发证时间", DBNull.Value) : new SqlParameter("@发证时间", DateTime.Parse(_发证时间)); SqlParameter par11 = _有效时间 == "" ? new SqlParameter("@有效时间", DBNull.Value) : new SqlParameter("@有效时间", DateTime.Parse(_有效时间)); SqlParameter par12 = new SqlParameter("@发证单位", _发证单位); SqlParameter par13 = new SqlParameter("@备注", _备注); DAL dal = new DAL(); try { dal.RunSql(SQL, new SqlParameter[] { par1, par2, par3, par4, par5, par6, par7, par8, par9, par10, par11, par12, par13 }); } catch (Exception e) { throw e; } }
/// <summary> /// 修改考勤处理 /// </summary> /// <param name="context"></param> public void KQ_update(HttpContext context) { string ID = context.Request["ID"]; string time1 = context.Request["time1"]; string time2 = context.Request["time2"]; string cqqk = context.Request["cqqk"]; string name = context.Request["name"]; string sm = context.Request["sm"]; int cq = 0;//实到 根据签到情况来判断 if (time1 != "") { cq += 1; } else { time1 = null; } if (time2 != "") { cq += 1; } else { time2 = null; } int cqsj = 0;//计算出勤时间 if (time1 != null && time2 != null) { DateTime t1 = DateTime.Parse(time1); DateTime t2 = DateTime.Parse(time2); TimeSpan sp = t2.Subtract(t1); cqsj = (int)sp.TotalMinutes; } string SQL = @"UPDATE [CNGTZ].[dbo].[TZ_人员_考勤表] SET [上班签到] = @上班 ,[下班签退] = @下班 ,[实到] =@实到 ,[出勤情况] = @出勤情况 ,[出勤时间] = @出勤时间 ,[修改] =1 ,[说明] =@说明 WHERE ID=@ID"; SqlParameter par1 = new SqlParameter("ID", ID); SqlParameter par2 = new SqlParameter("说明", sm); SqlParameter par3 = new SqlParameter("实到", cq); SqlParameter par4 = new SqlParameter("出勤情况", cqqk); SqlParameter par5 = new SqlParameter("出勤时间", cqsj); SqlParameter par6 = (time1 == null) ? new SqlParameter("上班", DBNull.Value) : new SqlParameter("上班", time1); SqlParameter par7 = (time2 == null) ? new SqlParameter("下班", DBNull.Value) : new SqlParameter("下班", time2); SqlParameter[] par = { par1, par2, par3, par4, par5, par6, par7 }; DAL dal = new DAL(); dal.RunSql(SQL, par);//执行操作 Sys.addSteps(name, "修改ID=" + ID, "异常考勤"); context.Response.Write("ok"); }
/// <summary> /// 修改版本控制记录 /// </summary> /// <param name="context"></param> public void VerUpdate(HttpContext context) { string SQL = "UPDATE [WebApp].[dbo].[WEB_版本控制] SET [更新说明] = @更新说明 WHERE 版本号=@版本号"; string ver = context.Request["ver"]; string info = context.Request["info"] ?? ""; string retext = "{{ \"ok\":{0},\"msg\":\"{1}\",\"data\":@替换对象@ }}"; DAL dal = new DAL(); SqlParameter par1 = new SqlParameter("版本号", ver); SqlParameter par2 = new SqlParameter("更新说明", info); dal.RunSql(SQL, new SqlParameter[] { par1, par2 }); if (dal.ExecuteRowCount == 1) { retext = string.Format(retext, "true", "更改数据成功!"); retext = retext.Replace("@替换对象@", "0"); } else { retext = string.Format(retext, "false", "错误信息:" + dal.ErrorMsg); retext = retext.Replace("@替换对象@", "0"); } context.Response.Write(retext); }
/// <summary> /// 删除版本控制记录 /// </summary> /// <param name="context"></param> public void VerDel(HttpContext context) { string SQL = "DELETE FROM [WebApp].[dbo].[WEB_版本控制] WHERE 版本号=@版本号"; string ver = context.Request["ver"]; string conf = context.Server.MapPath("~/Public/CNGconfig.xml"); string oldver = Sys.xmlRead(conf, "缓存配置", "全局版本", "1.00");//读取版本号 string retext = "{{ \"ok\":{0},\"msg\":\"{1}\",\"data\":@替换对象@ }}"; DAL dal = new DAL(); SqlParameter par1 = new SqlParameter("版本号", ver); dal.RunSql(SQL, new SqlParameter[] { par1 }); if (dal.ExecuteRowCount == 1) { if (double.Parse(ver) >= double.Parse(oldver)) { SQL = "SELECT TOP 1 [版本号] FROM [WebApp].[dbo].[WEB_版本控制] ORDER BY 版本号 DESC"; oldver = dal.RunSqlGetID(SQL) ?? "1.00"; Sys.xmlAdd(conf, "缓存配置", "全局版本", oldver); } retext = string.Format(retext, "true", "更改数据成功,当前版本号为:" + oldver); retext = retext.Replace("@替换对象@", "0"); } else { retext = string.Format(retext, "false", "错误信息:" + dal.ErrorMsg); retext = retext.Replace("@替换对象@", "0"); } context.Response.Write(retext); }
/// <summary> /// 更改错误数据 /// </summary> /// <param name="context"></param> public void Check_err(HttpContext context) { int zd = int.Parse(context.Request["zd"]);//站点 string kh = context.Request["kh"];//卡号 string lsh = context.Request["lsh"];//流水号 string rq = context.Request["rq"];//日期 string name = context.Request["name"];//操作用户 string zdIP = "", zdName = "", km = "", cp = ""; switch (zd) { case 2: zdIP = "192.168.173.3"; zdName = "南高"; break;//南高 case 3: zdIP = "192.168.172.23"; zdName = "江阳西路"; break;//江阳西路 case 4: zdIP = "192.168.171.3"; zdName = "麻柳沱"; break; //麻柳沱 case 5: zdIP = "192.168.174.3"; zdName = "华阳"; break; //华阳 default: break; } DAL dal = new DAL(); string SQL = "SELECT TOP 1 a.[UserCardNo],a.[CarNo],b.ICardName FROM [CSERVER].[dbo].[UserCards] a left JOIN [CSERVER].dbo.ICardType b ON a.ICardType=b.ICardType where a.UserCardNo=" + kh; string[] temp = dal.RunSqlStrings(SQL); if (temp != null) { km = temp[2].Trim();//取得卡名 cp = temp[1].Trim();//取的车牌号 } else { context.Response.Write("未找到对应车牌信息!"); return; } SQL = "UPDATE [cngms].[dbo].[PayGas] SET [CardName] = '" + km + "',[CarNo] = '" + cp + "' WHERE TradeDate='" + rq + "' AND [TradeNo]=" + lsh + " AND UserCardNo=" + kh; //更改站点数据 DAL Zdal = new DAL(zdIP);//用于连接站点数据库 if (Zdal.TestCon(2))//先测试连接 { Zdal.RunSql(SQL); } else { context.Response.Write("连接站点数据库错误! 更改失败!"); return; } //更改心数据库数据 SQL = "UPDATE [CSERVER].[dbo].[GAS] SET [CardName] ='" + km + "',[CarNO] = '" + cp + "' where StationNO=" + zd.ToString() + " AND TradeDate='" + rq + "' AND TradeNO=" + lsh + " AND UserCardNo=" + kh; dal.RunSql(SQL); SQL = "UPDATE [CSERVER].[dbo].[TEMPGAS] SET [CardName] ='" + km + "',[CarNO] = '" + cp + "' where StationNO=" + zd.ToString() + " AND TradeDate='" + rq + "' AND TradeNO=" + lsh + " AND UserCardNo=" + kh; dal.RunSql(SQL); //更改 充装记录数据 SQL = "UPDATE [CNGQT].[dbo].[CNG_充装检查] SET [卡名] ='" + km + "',[车牌号] ='" + cp + "',[是否完成]=0 where 站点=" + zd.ToString() + " and 日期='" + rq + "' AND 流水号=" + lsh + " AND 用户号=" + kh; dal.RunSql(SQL); string tt = "站点[{0}] 日期[{1}] 卡号[{2}] 流水号[{3}]"; tt = string.Format(tt, zd.ToString(), rq, kh, lsh); Sys.addSteps(name, tt, "修改错误数据"); context.Response.Write("所有数据更改完成,请检查数据是否成功更改!"); }
/// <summary> /// 插入新指纹数据 并更新数据库数据 /// </summary> /// <param name="dt"></param> private int Update_KQ(DataTable dt) { DAL dal = new DAL(); //先删除临时表数据 string SQL = "DELETE FROM [CNGTZ].[dbo].[TZ_人员_考勤TEMP]"; dal.RunSql(SQL); foreach (DataRow row in dt.Rows) { SqlParameter par1 = new SqlParameter("刷机时间", row["checktime"].ToString()); #region 用于处理字符串后面的特殊字符 //处理原因为,从Access数据库生成的 table,姓名字段,取出的数据,后面带有特殊不能显示的字符,原因不明 byte[] t = System.Text.Encoding.Default.GetBytes((string)row["name"]); int x = 0; foreach (var b in t) { if (b == 0) { break; } x = x + 1; } string name = Encoding.Default.GetString(t, 0, x); #endregion //Sys.DebugMes(name); SqlParameter par2 = new SqlParameter("姓名", name); SQL = "INSERT INTO [CNGTZ].[dbo].[TZ_人员_考勤TEMP]([刷机时间],[姓名]) VALUES(@刷机时间,@姓名)"; dal.RunSql(SQL, new SqlParameter[] { par1, par2 }); } SQL = @"Insert into [CNGTZ].[dbo].[TZ_人员_考勤_刷机时间]( 刷机时间, 姓名) SELECT 刷机时间, 姓名 from [CNGTZ].[dbo].[TZ_人员_考勤TEMP] b where not exists(select * from [CNGTZ].[dbo].[TZ_人员_考勤_刷机时间] where b.刷机时间= 刷机时间 and b.姓名= 姓名 )"; dal.RunSql(SQL);//运行 复制数据 存储过程 if (this.SJ_指纹处理 != null) { Even_指纹系统处理 e = new Even_指纹系统处理(); e.mesg = "复制纹数据:" + dal.ExecuteRowCount.ToString() + "条"; //触发事件 this.SJ_指纹处理(this, e); } return dal.ExecuteRowCount; }
/// <summary> /// 生成班组考勤表 /// </summary> /// <param name="ry">班组人员列表</param> /// <param name="dt">日期</param> /// <param name="zd">站点</param> /// <param name="bz">班组</param> /// <param name="yb">是否夜班</param> private void KQ_生成班组考勤数据(DataTable ry, DateTime dt, int zd, int bz, bool yb) { string 上班时间 = "", 下班时间 = "", 白班夜班 = ""; SqlParameter par1, par2, par3, par4, par5, par6, par7, par8; string SQL = @"INSERT INTO [CNGTZ].[dbo].[TZ_人员_考勤表] ([人员ID] ,[日期] ,[站点] ,[姓名] ,[班组] ,[白班夜班] ,[上班时间] ,[下班时间]) VALUES (@人员ID ,@日期 ,@站点 ,@姓名 ,@班组 ,@白班夜班 ,@上班时间 ,@下班时间 )"; par1 = new SqlParameter("日期", dt); par2 = new SqlParameter("站点", zd); par3 = new SqlParameter("班组", bz); DAL dal = new DAL(); dal.saveErrorLog(false);//关闭记录错误日志 // 南高站 与 西路站 if (zd == 2 || zd == 3) { if (yb) { 白班夜班 = "夜班"; 上班时间 = dt.ToShortDateString() + " 20:00:00"; 下班时间 = dt.AddDays(1).ToShortDateString() + " 9:00:00"; } else { 白班夜班 = "白班"; 上班时间 = dt.ToShortDateString() + " 9:00:00"; 下班时间 = dt.ToShortDateString() + " 20:00:00"; } par4 = new SqlParameter("白班夜班", 白班夜班); par5 = new SqlParameter("上班时间", 上班时间); par6 = new SqlParameter("下班时间", 下班时间); foreach (DataRow row in ry.Rows) { par7 = new SqlParameter("人员ID", int.Parse(row["ID"].ToString())); par8 = new SqlParameter("姓名", row["姓名"].ToString()); dal.RunSql(SQL, new SqlParameter[] { par1, par2, par3, par4, par5, par6, par7, par8 }); } } //麻柳沱站 if (zd == 4) { if (bz == 2 || bz == 4) {//如果班组为2 或 4 不进行麻柳沱站处理 return; } 上班时间 = dt.ToShortDateString() + " 9:00:00"; 下班时间 = dt.AddDays(2).ToShortDateString() + " 9:00:00"; par4 = new SqlParameter("白班夜班", ""); par5 = new SqlParameter("上班时间", 上班时间); par6 = new SqlParameter("下班时间", 下班时间); foreach (DataRow row in ry.Rows) { par7 = new SqlParameter("人员ID", int.Parse(row["ID"].ToString())); par8 = new SqlParameter("姓名", row["姓名"].ToString()); dal.RunSql(SQL, new SqlParameter[] { par1, par2, par3, par4, par5, par6, par7, par8 }); } } if (this.SJ_指纹处理 != null) { Even_指纹系统处理 e = new Even_指纹系统处理(); e.mesg = string.Format("生成考勤表:日期{0};站点={1};班组:{2}", dt.ToShortDateString(), zd.ToString(), bz.ToString()); //触发事件 this.SJ_指纹处理(this, e); } }
/// <summary> /// 签到时间生成 /// </summary> /// <param name="dt">结束日期</param> /// <param name="dd">处理天数</param> /// <param name="wc">指纹刷机误差小时</param> /// <param name="cdwc">迟到误差分钟</param> /// <param name="ztwc">早退误差分钟</param> public void KQ_签到处理(DateTime dt, int dd, int wc, int cdwc, int ztwc) { //dt = DateTime.Parse(dt.ToShortDateString()); DateTime 起始时间 = dt.AddDays(0 - dd); //签到处理 string SQL = "SELECT [ID],[姓名],[上班时间],[下班时间] FROM [CNGTZ].[dbo].[TZ_人员_考勤表] where 上班签到 IS NULL and 日期 between @起始 and @结束"; SqlParameter par1 = new SqlParameter("起始", 起始时间); SqlParameter par2 = new SqlParameter("结束", dt); //Sys.DebugMes(起始时间.ToString() + " " + dt.ToString()); DAL dal = new DAL(); DataTable renName = dal.RunSqlDataTable(SQL, new SqlParameter[] { par1, par2 });//取得人员列表 foreach (DataRow row in renName.Rows) { DateTime t = DateTime.Parse(row["上班时间"].ToString()); SqlParameter p1 = new SqlParameter("起始", t.AddHours(0 - wc)); SqlParameter p2 = new SqlParameter("结束", t.AddHours(wc)); SqlParameter p3 = new SqlParameter("姓名", row["姓名"].ToString()); if (row["姓名"].ToString() == "陈兴华") { Sys.DebugMes("XXX"); } SQL = "SELECT top 1 [刷机时间] FROM [CNGTZ].[dbo].[TZ_人员_考勤_刷机时间] where 姓名=@姓名 AND 刷机时间 BETWEEN @起始 AND @结束 ORDER BY 刷机时间"; string qdsj = dal.RunSqlGetID(SQL, new SqlParameter[] { p1, p2, p3 });//找到最早指纹签到时间 if (qdsj == null) { continue;//如果没有找到签到时间 就到下一条记录 } int cd = 0;//判断是否迟到 DateTime tt = DateTime.Parse(qdsj);//签到时间 TimeSpan temp = tt.Subtract(t); cd = temp.Minutes;//迟到分钟 cd = (cd > cdwc) ? 1 : 0; SQL = "UPDATE [CNGTZ].[dbo].[TZ_人员_考勤表] SET [上班签到] = @签到时间,[实到] =[实到]+ 1,[迟到]= " + cd.ToString() + " WHERE ID =@ID"; SqlParameter p4 = new SqlParameter("ID", int.Parse(row["ID"].ToString())); SqlParameter p5 = new SqlParameter("签到时间", DateTime.Parse(qdsj)); dal.RunSql(SQL, new SqlParameter[] { p4, p5 });//更新签到时间列表 } //进行签退处理 SQL = "SELECT [ID],[姓名],[上班时间],[下班时间] FROM [CNGTZ].[dbo].[TZ_人员_考勤表] where 下班签退 IS NULL and 日期 between @起始 and @结束"; par1 = new SqlParameter("起始", 起始时间); par2 = new SqlParameter("结束", dt); renName = dal.RunSqlDataTable(SQL, new SqlParameter[] { par1, par2 });//取得人员列表 foreach (DataRow row in renName.Rows) { DateTime t = DateTime.Parse(row["下班时间"].ToString()); SqlParameter p1 = new SqlParameter("起始", t.AddHours(0 - wc)); SqlParameter p2 = new SqlParameter("结束", t.AddHours(wc)); SqlParameter p3 = new SqlParameter("姓名", row["姓名"].ToString()); SQL = "SELECT top 1 [刷机时间] FROM [CNGTZ].[dbo].[TZ_人员_考勤_刷机时间] where 姓名=@姓名 AND 刷机时间 BETWEEN @起始 AND @结束 ORDER BY 刷机时间 desc"; string qdsj = dal.RunSqlGetID(SQL, new SqlParameter[] { p1, p2, p3 });//找到最后下班指纹签到时间 if (qdsj == null) { continue;//如果没有找到签到时间 就到下一条记录 } int zt = 0;//判断是否早退 DateTime tt = DateTime.Parse(qdsj); TimeSpan temp = t.Subtract(tt); zt = temp.Minutes; zt = (zt > ztwc) ? 1 : 0; SQL = "UPDATE [CNGTZ].[dbo].[TZ_人员_考勤表] SET [下班签退] = @签到时间,[实到] =[实到]+ 1,[早退]= " + zt.ToString() + " WHERE ID =@ID"; SqlParameter p4 = new SqlParameter("ID", int.Parse(row["ID"].ToString())); SqlParameter p5 = new SqlParameter("签到时间", DateTime.Parse(qdsj)); dal.RunSql(SQL, new SqlParameter[] { p4, p5 });//更新签到时间列表 } //记录处理时间 SQL = "INSERT INTO [CNGTZ].[dbo].[TZ_人员_考勤处理时间]([时间]) VALUES ('" + DateTime.Now.ToString() + "')"; dal.RunSql(SQL); if (this.SJ_指纹处理 != null) { Even_指纹系统处理 e = new Even_指纹系统处理(); e.mesg = "处理考勤签到【" + 起始时间.ToShortDateString() + " 至 " + dt.ToShortDateString() + "】到完成!"; //触发事件 this.SJ_指纹处理(this, e); } }
/// <summary> /// 重置用户密码 /// </summary> /// <param name="name">用户名</param> /// <param name="context"></param> public void restuser(string name, HttpContext context) { string nono = "对不起,你没有权限进行此操作!"; string[] xy = { "【运行所】", "【系统管理员】" }; //需要的权限才能操作 if (testQX(xy, context) == false) { context.Response.Write(nono); return; } if (name == null) { context.Response.Write(""); return; } string SQL = "UPDATE [WebApp].[dbo].[DL_用户名] SET [密码]='f379eaf3c831b04de153469d1bec345e',[签名密码] ='f379eaf3c831b04de153469d1bec345e' WHERE 用户名='" + name.Trim() + "'"; DAL dal = new DAL(); dal.RunSql(SQL); if (dal.ExecuteRowCount == 1) { Sys.addSteps(getname(context), "重置用户:[" + name.Trim() + "]密码", "[用户管理]"); context.Response.Write("成功"); } else { context.Response.Write(dal.ErrorMsg); } }
public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/plain"; context.Response.Charset = "utf-8"; HttpPostedFile file = context.Request.Files["Filedata"];//文件 对象 string name = context.Request["name"];//上传用户 string title = context.Request["title"];//分类 string path = context.Request["path"];//文件路径 if (path == null) { path = ""; } string fuPaht = ""; string Suser = ""; switch (title) { case "公司文件": fuPaht = "E:\\CNGfile\\公司文件" + path; break; case "车用燃气事业部文档": fuPaht = "E:\\CNGfile\\车用燃气事业部" + path; break; case "用户个人文档": fuPaht = "E:\\CNGfile\\个人文档\\" + name + "\\" + path; Suser = name; break; default: break; } DAL dal = new DAL(); if (file != null) { if (!Directory.Exists(fuPaht)) { Directory.CreateDirectory(fuPaht); } file.SaveAs(fuPaht + "\\" + file.FileName); FileItem fi = FileManager.GetItemInfo(fuPaht + "\\" + file.FileName); if (fi.Name.Length > 0)//判断创建文件是否完成 { string SQL = "SELECT [ID] FROM [WebApp].[dbo].[FI_公司文件] where 路径='" + fi.FullName + "'"; string ID = dal.RunSqlGetID(SQL); if (ID != null) { dal.RunSql("DELETE FROM [WebApp].[dbo].[FI_公司文件] WHERE ID=" + ID); } SQL = "INSERT INTO [WebApp].[dbo].[FI_公司文件]([名称],[路径],[修改日期],[文件大小],[上传用户],[目录],[所属用户],[分类])VALUES(@名称,@路径,@修改日期,@文件大小,@上传用户,@目录,@所属用户,@分类)"; SqlParameter par1 = new SqlParameter("@名称", SqlDbType.VarChar, 200); par1.Value = fi.Name; SqlParameter par2 = new SqlParameter("@路径", SqlDbType.VarChar, 800); par2.Value = fi.FullName; SqlParameter par3 = new SqlParameter("@修改日期", fi.LastWriteDate); SqlParameter par4 = new SqlParameter("@文件大小", fi.Size); SqlParameter par5 = new SqlParameter("@上传用户", name); SqlParameter par6 = new SqlParameter("@目录", path); SqlParameter par7 = new SqlParameter("@所属用户", name); SqlParameter par8 = new SqlParameter("@分类", title); Sys.DebugMes(par6.Size.ToString()); dal.RunSql(SQL, new SqlParameter[] { par1, par2, par3, par4, par5, par6, par7, par8 }); Sys.addSteps(name, "上传:[" + fi.Name + "]", "文档操作"); //下面这句代码缺少的话,上传成功后上传队列的显示不会自动消失 context.Response.Write("1"); } } else { context.Response.Write("0"); } }
/// <summary> /// 保存站点积分公告 /// </summary> public void JF_GG_save(HttpContext context) { int zd = int.Parse(context.Request["zd"]); string txt = context.Request["txt"]; string SQL = "UPDATE [CNGTZ].[dbo].[TZ_站点] SET [积分公告] = @公告 WHERE ID=@站点"; DAL dal = new DAL(); SqlParameter par1 = new SqlParameter("站点", zd); SqlParameter par2 = new SqlParameter("公告", txt); dal.RunSql(SQL, new SqlParameter[] {par1, par2}); string retext = ""; if (dal.ExecuteRowCount ==1) { retext = "yes"; } context.Response.Write(retext); }
public bool FF_锁定站点数据(int zd, DateTime time) { string zdIP = ""; string zdName = ""; Even_更新站点数据 e = new Even_更新站点数据(); switch (zd) { case 2: zdIP = "192.168.173.3"; zdName = "南高"; break;//南高 case 3: zdIP = "192.168.172.23"; zdName = "江阳西路"; break;//江阳西路 case 4: zdIP = "192.168.171.3"; zdName = "麻柳沱"; break; //麻柳沱 default: break; } DAL Zdal = new DAL(zdIP);//用于连接站点数据库 if (Zdal.TestCon(3) == false)//先测试连接,失败就返回 { e.ok = false; e.mes = zdName + " 更新失败"; } else { string SQL = "UPDATE [CNGMS].[dbo].[endtime] SET [endtime] ='{0}' WHERE id=1"; SQL = string.Format(SQL, time.ToString()); Zdal.RunSql(SQL); //string path = Sys.GetRunPath() + "\\CngSystem.xml"; //Sys.xmlAdd(path, "服务器站点更新", "锁定时间为", time.ToString()); e.mes = zdName + " 更新成功 锁定时间:" + time.ToString(); e.ok = true; } if (this.SJ_锁定站点数据 != null) {//触发事件 this.SJ_锁定站点数据(this, e); } return e.ok; }
/// <summary> /// 加入一条积分数据 /// </summary> /// <param name="context"></param> public void JF_add(HttpContext context) { string name = context.Request["name"]; string info = context.Request["info"]; string user = context.Request["user"]; int num = int.Parse(context.Request["num"]); string SQL = "INSERT INTO [CNGTZ].[dbo].[TZ_人员_积分]([姓名] ,[积分] ,[说明],[操作人])VALUES(@姓名,@积分,@说明,@操作人)"; string text = ""; DAL dal = new DAL(); SqlParameter par1 = new SqlParameter("姓名", name); SqlParameter par2 = new SqlParameter("积分", num); SqlParameter par3 = new SqlParameter("说明", info); SqlParameter par4 = new SqlParameter("操作人", user); dal.RunSql(SQL, new SqlParameter[] { par1, par2, par3, par4 }); if (dal.ExecuteRowCount == 1) { text = "yes"; } context.Response.Write(text); }
/// <summary> /// 删除积分操作 /// </summary> /// <param name="context"></param> public void JF_del(HttpContext context) { string id = context.Request["id"]; string SQL = "DELETE FROM [CNGTZ].[dbo].[TZ_人员_积分] WHERE ID=@ID"; string text = ""; DAL dal = new DAL(); SqlParameter par1 = new SqlParameter("ID", id); dal.RunSql(SQL, new SqlParameter[] { par1 }); if (dal.ExecuteRowCount == 1) { text = "yes"; } context.Response.Write(text); }
/// <summary> /// 新增IC卡 /// </summary> /// <param name="context"></param> public void IcAdd(HttpContext context) { string bzno = context.Request["bzno"]; string zdcode = context.Request["zdcode"]; string icno = context.Request["icno"]; string SQL = @"INSERT INTO [CNGTZ].[dbo].[IC_绑定] ([卡号] ,[班组] ,[所属站点] ,[更新时间]) VALUES (@卡号 ,@班组 ,@所属站点 ,getdate())"; var par1 = new SqlParameter("@卡号", icno); var par2 = new SqlParameter("@班组", bzno); var par3 = new SqlParameter("@所属站点", zdcode); string retext = "{\"ok\":@ok@,\"msg\":\"@msg@\"}"; var dal = new DAL(); try { dal.RunSql(SQL, new SqlParameter[] { par1, par2, par3 }); if (dal.ExecuteRowCount == 1) { retext = retext.Replace("@ok@", "true"); retext = retext.Replace("@msg@", "添加IC卡成功!请重新刷新加载数据!"); } else { retext = retext.Replace("@ok@", "false"); retext = retext.Replace("@msg@", "添加IC卡失败!<br/>注意:IC卡号不能重复!"); } } catch (Exception) { retext = retext.Replace("@ok@", "false"); retext = retext.Replace("@msg@", dal.ErrorMsg); } context.Response.Write(retext); }
/// <summary> /// 删除IC卡 /// </summary> /// <param name="context"></param> public void IcDel(HttpContext context) { string icno = context.Request["icno"]; string SQL = "DELETE FROM [CNGTZ].[dbo].[IC_绑定] WHERE 卡号='" + icno + "'"; var dal = new DAL(); dal.RunSql(SQL); string retext = "{\"ok\":@ok@,\"msg\":\"@msg@\"}"; ; if (dal.ExecuteRowCount == 1) { retext = retext.Replace("@ok@", "true"); retext = retext.Replace("@msg@", "删除IC卡成功!"); } else { retext = retext.Replace("@ok@", "false"); retext = retext.Replace("@msg@", "删除IC卡失败!请重新刷新,加载数据显示!"); } context.Response.Write(retext); }
/// <summary> /// 调岗操作 /// </summary> /// <param name="context"></param> public void DG_code(HttpContext context) { string code = context.Request["code"]; string name = context.Request["name"]; DAL dal = new DAL(); SqlParameter par1 = new SqlParameter("@code", code); SqlParameter par2 = new SqlParameter("@name", name); string SQL = "SELECT [ID],[所属部门] FROM [CNGTZ].[dbo].[TZ_人员] where 姓名=@name"; string[] dqcode = dal.RunSqlStrings(SQL, new SqlParameter[] { par2 }); SQL = "UPDATE [CNGTZ].[dbo].[TZ_人员] SET [所属部门] =@code,[更新时间] = getdate() WHERE 姓名=@name"; dal.RunSql(SQL, new SqlParameter[] { par1, par2 }); if (dqcode[1] == "02") {//如果是离职人员 调岗操作 SQL = "UPDATE [CNGTZ].[dbo].[TZ_人员_档案2] SET [离职日期] =null,[离职原因] ='',[更新时间] = getdate() WHERE ID=" + dqcode[0]; dal.RunSql(SQL); } if (dal.ExecuteRowCount > 0) { if (dqcode[1] == "02") {//如果是离职人员 调岗操作 SQL = "UPDATE [CNGTZ].[dbo].[TZ_人员_档案2] SET [离职日期] =null,[离职原因] ='',[更新时间] = getdate() WHERE ID=" + dqcode[0]; dal.RunSql(SQL); } //调岗成功 解除相关人员的IC卡绑定 SQL = "UPDATE [CNGTZ].[dbo].[IC_绑定] SET [人员] = '',[更新时间]=getdate() WHERE [人员]='" + name + "'"; dal.RunSql(SQL); context.Response.Write("true"); } else { context.Response.Write("false"); } }
/// <summary> /// 删除一个资质信息 /// </summary> /// <param name="context"></param> public void ZZ_cut(HttpContext context) { string ID = context.Request["ID"]; string retext = "{\"ok\":@ok@,\"msg\":\"@msg@\"}"; string SQL = "DELETE FROM [CNGTZ].[dbo].[TZ_人员_资质证书] WHERE ID=" + ID; try { DAL dal = new DAL(); dal.RunSql(SQL); if (dal.ExecuteRowCount == 1) { retext = retext.Replace("@ok@", "true"); retext = retext.Replace("@msg@", "删除资质证书成功!"); } else { retext = retext.Replace("@ok@", "false"); retext = retext.Replace("@msg@", "删除资质证书失败!"); } } catch (Exception ex) { retext = retext.Replace("@ok@", "false"); retext = retext.Replace("@msg@", ex.Message); } context.Response.Write(retext); }
/// <summary> /// 新增员工 /// </summary> /// <param name="context"></param> public void user_new(HttpContext context) { string name = context.Request["name"]; name = name.Trim(); string py = PY.GetTop(name); string SQL = "INSERT INTO [CNGTZ].[dbo].[TZ_人员]([姓名],[拼音],[是否在职],[所属部门],[更新时间]) VALUES(@name,@拼音,1,'03',getdate())"; DAL dal = new DAL(); SqlParameter par1 = new SqlParameter("@name", name); SqlParameter par2 = new SqlParameter("@拼音", py); dal.RunSql(SQL, new SqlParameter[] { par1, par2 }); if (dal.ExecuteRowCount == 1) { SQL = "SELECT [ID] FROM [CNGTZ].[dbo].[TZ_人员] where 姓名='" + name + "'"; string ID = dal.RunSqlGetID(SQL); SQL = "INSERT INTO [CNGTZ].[dbo].[TZ_人员_基本信息] ([ID],[更新时间])VALUES(" + ID + ",getdate())"; dal.RunSql(SQL); SQL = "INSERT INTO [CNGTZ].[dbo].[TZ_人员_档案1] ([ID],[更新时间])VALUES(" + ID + ",getdate())"; dal.RunSql(SQL); SQL = "INSERT INTO [CNGTZ].[dbo].[TZ_人员_档案2] ([ID],[更新时间])VALUES(" + ID + ",getdate())"; dal.RunSql(SQL); context.Response.Write("true"); } else { context.Response.Write("false"); } }
private void button1_Click(object sender, EventArgs e) { //初始化 FI_公司文件 表 string SQL = "INSERT INTO [WebApp].[dbo].[FI_公司文件]([名称],[路径],[修改日期],[文件大小],[上传用户])VALUES(@名称,@路径,@修改日期,@文件大小,'姜雪松')"; DAL dal = new DAL(); List<FileItem> dirs = FileManager.GetDirectoryItems(@"E:\CNGfile\公司文件"); foreach (FileItem dir in dirs) { List<FileItem> files = FileManager.GetFileItems(dir.FullName); foreach (FileItem fi in files) { SQL = "INSERT INTO [WebApp].[dbo].[FI_公司文件]([名称],[路径],[修改日期],[文件大小],[上传用户],[目录])VALUES(@名称,@路径,@修改日期,@文件大小,@上传用户,@目录)"; SqlParameter par1 = new SqlParameter("@名称", fi.Name); SqlParameter par2 = new SqlParameter("@路径", fi.FullName); SqlParameter par3 = new SqlParameter("@修改日期", fi.LastWriteDate); SqlParameter par4 = new SqlParameter("@文件大小", fi.Size); SqlParameter par5 = new SqlParameter("@上传用户", "姜雪松"); SqlParameter par6 = new SqlParameter("@目录", fi.FullName.Replace(fi.Name, "")); dal.RunSql(SQL, new SqlParameter[] { par1, par2, par3, par4, par5, par6 }); } //Sys.DebugMes(files.Count.ToString()); } Sys.DebugMes("完成"); }
/// <summary> /// 修改站点月度计划数 /// </summary> /// <param name="context"></param> public void UpdateJH(HttpContext context) { string year = context.Request["year"]; string yue = context.Request["yue"]; string zd = context.Request["zd"]; string val = context.Request["val"]; string SQL = @"UPDATE [CNGQT].[dbo].[CW_月度统计] SET [计划]=@计划@,[更新时间] = GETDATE() WHERE 年=@年@ and 月=@月@ and 站点=@站点@"; string retext = "{{ \"ok\":{0},\"msg\":\"{1}\" }}"; SQL = SQL.Replace("@计划@", val); SQL = SQL.Replace("@年@", year); SQL = SQL.Replace("@月@", yue); SQL = SQL.Replace("@站点@", zd); DAL dal = new DAL(); dal.RunSql(SQL); if (dal.ExecuteRowCount == 1) { retext = string.Format(retext, "true", "计划数修改成功!"); } else { retext = string.Format(retext, "false", "计划数未修改成功!"); } context.Response.Write(retext); }
/// <summary> /// 锁定 解锁 操作 /// </summary> /// <param name="context"></param> public void lockData(HttpContext context) { string year = context.Request["year"]; string yue = context.Request["yue"]; string val = context.Request["val"]; string retext = "{{ \"ok\":{0},\"msg\":\"{1}\" }}"; string SQL = @"UPDATE [CNGQT].[dbo].[CW_月度统计] SET 锁定=@锁定@ WHERE 年=@年@ and 月=@月@ "; SQL = SQL.Replace("@锁定@", val); SQL = SQL.Replace("@年@", year); SQL = SQL.Replace("@月@", yue); DAL dal = new DAL(); dal.RunSql(SQL); if (dal.ExecuteRowCount == 3) { retext = string.Format(retext, "true", "操作成功!"); } else { retext = string.Format(retext, "false", "操作失败!"); } context.Response.Write(retext); }
/// <summary> /// 更新用户权限 /// </summary> /// <param name="name">用户名</param> /// <param name="context"></param> public void updateqx(string name, HttpContext context) { string nono = "对不起,你没有权限进行此操作!"; string[] xy = { "【运行所】", "【系统管理员】" }; //需要的权限才能操作 if (testQX(xy, context) == false) { context.Response.Write(nono); return; } if (name == null) { context.Response.Write(""); return; } string val = context.Request["val"]; string SQL = "UPDATE [WebApp].[dbo].[DL_用户名] SET [权限]='" + val + "' WHERE 用户名='" + name.Trim() + "'"; DAL dal = new DAL(); dal.RunSql(SQL); if (dal.ExecuteRowCount == 1) { Sys.addSteps(getname(context), "更改用户:[" + name.Trim() + "]权限", "[用户管理]"); context.Response.Write("成功"); } else { context.Response.Write(dal.ErrorMsg); } }