protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { string code = Request.QueryString["code"]; string accessToken = Request.QueryString["accessToken"]; string userid = DingHelper.GetUserByCode(accessToken, code);//获取dtuser UserTableBLL bll = new UserTableBLL(); UserInfo user = bll.GetUserByDtUser(userid);//查询dtuser是否有关联 if (user.UserName != "") { Session["TodayDate"] = DateTime.Now.ToString("yyyy-MM-dd"); UserHelper.SaveSession(user); Response.Cookies["ThemeCookie"].Value = user.UserTheme; Response.Cookies["ThemeCookie"].Expires = DateTime.MaxValue; Response.Write("{ \"user\": \"" + userid + "\", \"exists\": \"1\" }"); Response.End(); } else { Response.Write("{ \"user\": \"" + userid + "\", \"exists\": \"0\" }"); Response.End(); } } }
/// <summary> /// Executes a task /// </summary> public void Execute() { using (var db = new AALifeDbContext()) { var startDate = DateTime.Now.AddDays(-1).Date; var endDate = DateTime.Now.Date; var result = db.ItemTable.Where(a => a.ModifyDate >= startDate && a.ModifyDate < endDate); string msg = string.Format("每日统计\n\n{0} 日,共记录消费 {1} 条。", startDate.ToString("yyyy/MM/dd"), result.Count()); var dd = new DingClient(); string accessToken = dd.GetToken(dd.AppKey, dd.AppSecret); //ding DingHelper.SendMessage(dd.AgentId, accessToken, "统计消息", msg, dd.AdminUser); } }
protected void Button_student_Click(object sender, EventArgs e) { if (string.IsNullOrWhiteSpace(NameValue.Text) || string.IsNullOrWhiteSpace(Idvalue.Text)) { Response.Write("<script>alert('请先选择操作的对象')</script>"); return; } using (MySqlDBContext db = new MySqlDBContext()) { DingHelper dd = new DingHelper(); var users = dd.GetUserIds(Idvalue.Text); if (users == null) { Response.Write("<script>alert('部门下不存在数据')</script>"); return; } var sql = $"DELETE from users where user_id in(select user_id from(SELECT * from users where class_grade = '{NameValue.Text}')t)"; db.Database.ExecuteSqlCommand(sql); var userList = new List <User>(); for (int i = 0; i < users.Count; i++) { var userid = users[i]; var user = dd.GetUserDetail(Convert.ToString(userid)); userList.Add(new User { mobile = user.mobile, pass_word = user.mobile, role = user.position, user_id = user.userid, user_name = user.name, class_grade = NameValue.Text, getdata = 1 }); } db.user.AddRange(userList); if (db.SaveChanges() > 0) { Response.Write("<script>alert('部门下学生数据更新完成')</script>"); } else { Response.Write("<script>alert('暂无更新项')</script>"); } } }
protected void Button_tongzhi_Click(object sender, EventArgs e) { DingHelper dd = new DingHelper(); int state = 1; string text = string.Empty; using (MySqlDBContext db = new MySqlDBContext()) { var users = db.user.Where(x => x.getdata == 1).ToList(); var i = DateTime.Now.DayOfWeek - DayOfWeek.Monday; if (i == -1) { i = 6; } var startTime = DateTime.Now.Date.AddDays(-i).ToString("yyyy-MM-dd HH:mm:ss"); var endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); foreach (var item in users) { var where = ""; where += $" and y.考勤时间 >= '{ startTime}'"; where += $" and y.考勤时间 <='{endTime}'"; where += $" and u.user_id='{item.user_id}'"; var qjwhere = ""; qjwhere += $" and q.开始时间 >= '{ startTime}'"; qjwhere += $" and q.开始时间 <='{endTime}'"; qjwhere += $" and u.user_id='{item.user_id}'"; //正常出勤 var zcsql = $"SELECT count(*) FROM( SELECT count(1) as c from yuanshijilus as y " + $"left join users as u on u.user_id = y.user_id where y.打卡结果='正常' {where} GROUP BY DATE_FORMAT(y.考勤时间,'%Y-%m-%d') HAVING c=4 ) as d"; //迟到 var cdSql = $"SELECT count(1) from yuanshijilus as y " + $"left join users as u on u.user_id = y.user_id where y.打卡结果='迟到' {where}"; //早退 var ztSql = $"SELECT count(1) from yuanshijilus as y " + $"left join users as u on u.user_id = y.user_id where " + $" y.打卡结果='早退' {where}"; //请假 var qjSql = $" SELECT count(1) from qingjias as q left join users u " + $" on q.user_id = u.user_id where 1=1 {qjwhere}"; //缺卡 var qkSql = $"select count(1) from ( SELECT any_value(u.user_name) as 名字,any_value(y.考勤时间) as 考勤时间,any_value(y.打卡时间) as 打卡时间," + $" any_value(y.打卡结果) as 打卡结果 from yuanshijilus as y " + $"left join users as u on u.user_id = y.user_id where y.打卡结果='未打卡' {where} AND y.id not in (SELECT y.id from yuanshijilus " + $"as y left join users as u on u.user_id = y.user_id " + $"LEFT join qingjias as q on u.user_id=y.user_id where y.打卡结果='未打卡' {where} AND " + $" y.`打卡时间` BETWEEN q.`开始时间` and q.`结束时间`) " + $" GROUP BY y.id) as d"; //旷课 var kkSql = "select count(1) from ( SELECT any_value(a.名字), CASE WHEN any_value(a.时间) > DATE_FORMAT(any_value(a.时间), '%Y-%m-%d 12:00') THEN DATE_FORMAT(any_value(a.时间), '%Y-%m-%d 下午') " + " ELSE DATE_FORMAT(any_value(a.时间), '%Y-%m-%d 上午') END as 旷课时间 from( SELECT DATE_FORMAT( any_value(y.`考勤时间`), '%Y-%m-%d %H:%i' ) AS 时间," + " any_value(u.user_name) AS 名字,CASE WHEN y.`考勤时间` > DATE_FORMAT(y.`考勤时间`, '%Y-%m-%d 13:00') THEN DATE_FORMAT(y.`考勤时间`, '%Y-%m-%d 18:00') ELSE DATE_FORMAT(y.`考勤时间`, '%Y-%m-%d 12:00' ) END AS 日期 from yuanshijilus as y " + $"left join users as u on u.user_id = y.user_id where y.打卡结果='未打卡' {where} AND y.id not in (SELECT DISTINCT y.id FROM qingjias as q LEFT JOIN yuanshijilus AS y ON q.user_id = y.user_id LEFT JOIN users AS u ON u.user_id = y.user_id where y.打卡结果='未打卡' {where} AND " + $" y.`打卡时间` BETWEEN q.`开始时间` and q.`结束时间`) " + $" GROUP BY y.id) as a GROUP BY a.名字,a.日期 HAVING count(a.日期) > 1) as d "; var ccsql = "SELECT count(*) from ( SELECT d.抽查开始时间,d.抽查结束时间, CASE WHEN ( SELECT count( * ) FROM qingjias AS q LEFT join users as u on u.user_id=q.user_id" + " WHERE DATE_FORMAT( q.`开始时间`, '%Y-%m-%d %H:%i:%s' ) <= d.`抽查开始时间` AND DATE_FORMAT( q.`结束时间`, '%Y-%m-%d %H:%i:%s' ) >= d.`抽查结束时间` " + $" AND u.pass_word ='{item.user_id}' ) > 0 THEN '请假' else d.打卡时间 END as 打卡时间 ," + $"( SELECT msg FROM remark as r LEFT join users as u on r.user_id=u.user_id " + $"WHERE u.pass_word ='{item.user_id}' AND start_time = d.抽查开始时间 AND end_time = d.抽查结束时间 ) AS 备注" + $" from " + "(SELECT 抽查开始时间,抽查结束时间, (SELECT CASE WHEN min(打卡时间) is NULL THEN '无打卡' ELSE " + "min(打卡时间) END as 打卡时间 FROM dakaxiangqings as d left join users as u on d.userId = u.user_id " + $"WHERE d.打卡时间 >= c.`抽查开始时间` and d.打卡时间 <= c.`抽查结束时间` AND u.pass_word ='{ item.user_id}') as 打卡时间" + $" from choucha as c) as d ) as e where e.`抽查开始时间`>='{startTime}' and e.`抽查结束时间`<='{endTime}' and e.`打卡时间`!=''"; var msg = new tongjiModel { msgtype = "oa", oa = new Oa { head = new Head { bgcolor = "FFBBBBBB", text = $"{item.user_name}关于出勤情况" }, body = new Body { author = "考勤", content = $"[{startTime}-{endTime}]", title = $"{item.user_name}出勤情况", form = new List <Form> { new Form { key = "正常.........", value = $"{SqlHelper.ExecuteScalar(zcsql)}次" }, new Form { key = "迟到.........", value = $"{SqlHelper.ExecuteScalar(cdSql)}次" }, new Form { key = "早退.........", value = $"{SqlHelper.ExecuteScalar(ztSql)}次" }, new Form { key = "请假.........", value = $"{SqlHelper.ExecuteScalar(qjSql)}次" }, new Form { key = "旷工.........", value = $"{SqlHelper.ExecuteScalar(kkSql)}次" }, new Form { key = "缺卡.........", value = $"{SqlHelper.ExecuteScalar(qkSql)}次" }, new Form { key = "抽查.........", value = $"{SqlHelper.ExecuteScalar(ccsql)}次" } } } } }; var response = dd.GetCorpconversation(item.user_id, msg); state = response.errcode; msg = response.errmsg; } if (state == 0) { Response.Write("<script>alert('发送完成')</script>"); } else { Response.Write($"<script>alert('{text}')</script>"); } } }