protected void Button_department_Click(object sender, EventArgs e)
{
using (MySqlDBContext db = new MySqlDBContext())
{
db.Database.ExecuteSqlCommand("TRUNCATE TABLE departments");
var data = dd.GetDepartment();
List <department> departmentData = new List <department>();
foreach (var item in data)
{
departmentData.Add(new department
{
departmentId = item.id,
name = item.name
});
}
db.department.AddRange(departmentData);
if (db.SaveChanges() > 0)
{
Response.Write("<script>if(confirm('部门数据更新完成')){window.location='/xuanze.aspx'} else{window.location='/xuanze.aspx'}</script>");
}
else
{
var redirect = "<script>if(confirm('暂时没有更新项，是否重新更新 ')){window.location='/xuanze.aspx'} else " +
"{window.location='/shujugengxin.aspx'}</script>";
Response.Write(redirect);
}
}
}
private void SetContext(DataBaseType DbType, Connections Conn)
{
switch (DbType)
{
case DataBaseType.DB2:
_DB2DBContext = new DB2DBContext(Conn);
QueryProcessor = _DB2DBContext;
break;
case DataBaseType.MSSql:
_MSSQLDBContext = new MSSQLDBContext(Conn);
QueryProcessor = _MSSQLDBContext;
break;
case DataBaseType.MySQL:
_MySqlDBContext = new MySqlDBContext(Conn);
QueryProcessor = _MySqlDBContext;
break;
case DataBaseType.PostgreSQL:
_PostgreSQLLDBContext = new PostgreSQLLDBContext(Conn);
QueryProcessor = _PostgreSQLLDBContext;
break;
}
}
protected void Button_delete_Click(object sender, EventArgs e)
{
using (MySqlDBContext db = new MySqlDBContext())
{
var sql = $"DELETE from users where user_id in(select user_id from(SELECT * from users where class_grade = '{NameValue.Text}')t)";
if (db.Database.ExecuteSqlCommand(sql) > 0)
{
Response.Write("<script>alert('部门下用户删除完成')</script>");
}
else
{
Response.Write("<script>alert('部门下用户暂无删除项')</script>");
}
}
}
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>");
}
}
}
// 支付回调 进行数据迁移
public void Callback(int memberId, int sourceId)
{
try
{
using (IDBContext dbcontext = new MySqlDBContext())
{
t_member_temp temp = dbcontext.Get <t_member_temp>(sourceId);
if (temp != null && temp.memberId == memberId)
{
try
{
dbcontext.BeginTransaction();
t_member_points points = dbcontext.Select <t_member_points>(c => c.memberId == temp.memberId).FirstOrDefault();
dbcontext.Execute($"update t_member_points set playerPoints=playerPoints+{points.playerPoints},coachPoints=coachPoints+{points.coachPoints},refereePoints=refereePoints+{points.refereePoints} where memberId={temp.tomemberId}");
dbcontext.Execute($"update t_member_temp set updateTime='{DateTime.Now}',tempStatus={(int)TempStatusEm.已绑定} where id={temp.id}");
dbcontext.Execute($"update t_member_points_record set memberId={temp.tomemberId} where memberId={temp.memberId} and isdelete=0");
dbcontext.Execute($"update t_event_player_signup set memberId={temp.tomemberId} where memberId={temp.memberId} and isdelete=0");
//对垒表也要修改
//1 循环赛
dbcontext.Execute($"update t_event_cycling_match_playerresult set playerId={temp.tomemberId} where playerId={temp.memberId} and isdelete=0");
//2 淘汰赛
dbcontext.CommitChanges();
}
catch (Exception ex)
{
dbcontext.Rollback();
LogUtils.LogError("MemberTempService.CallbackTran", ex);
}
}
}
}
catch (Exception ex)
{
LogUtils.LogError("MemberTempService.Callback", ex);
}
}
public ProgressController(MySqlDBContext context)
{
_context = context;
}
//全部更新
protected void Button_Quanbugengxin_Click(object sender, EventArgs e)
{
using (MySqlDBContext db = new MySqlDBContext())
{
db.Database.ExecuteSqlCommand("TRUNCATE TABLE yuanshijilus");
//获取所有需要打卡人的数据 getdata字段为是否需要获取打卡数据
var userList = db.user.Where(x => x.getdata == 1).ToList();
//获取yuanshijilu表里面的最晚一条的数据的时间作为更新打卡数据的起始时间
var updateTime = db.yuanshijilu.Any() ? Convert.ToDateTime(db.yuanshijilu.Max(x => x.考勤时间)).Date : new DateTime(2019, 2, 25);
List <DateTime> dateTimes = new List <DateTime>();
//当前时间的天数与打卡起始时间的天 差数 因为 钉钉的接口一次只能获取七天之内的打卡数据 所有需要分批 分时间段取数据
int days = (DateTime.Now.Date - updateTime).Days;
//按时间天数差 循环
for (int i = 0; i < days; i++)
{
dateTimes.Add(DateTime.Now.Date.AddDays(-i));
}
//七个一批 分成多少批
var index = dateTimes.Count % 7 == 0 ? dateTimes.Count / 7 : dateTimes.Count / 7 + 1;
int offset = 0;
var datas = new List <yuanshijilu>();
//取出每一个用户的所有数据
var stoptime = new Stopwatch();
stoptime.Start();
for (int i = 0; i < userList.Count; i++)
{
var user = userList[i];
//每一个用户取多少批
for (int t = 0; t < index; t++)
{
//当前这一批的所有时间
var times = dateTimes.Skip(t * 7).Take(7).ToList();
//去钉钉取数据
var data = dd.GetAttendance(user.user_id, times.Min(), times.Max().AddDays(1).AddMilliseconds(-1), offset, 50);
//处理取出来的所有数据
for (int x = 0; x < data.recordresult.Count; x++)
{
var result = data.recordresult[x];
var jl = new yuanshijilu()
{
user_id = result.userId,
打卡时间 = StringToDateTime(result.userCheckTime.ToString()).AddHours(8),
打卡结果 = ToTimeResult(result.timeResult),
考勤时间 = StringToDateTime(result.baseCheckTime.ToString()).AddHours(8),
考勤日期 = StringToDateTime(result.workDate.ToString())
};
//插入数据库
datas.Add(jl);
}
}
}
stoptime.Stop();
var chaxuntime = stoptime.ElapsedMilliseconds;
stoptime.Restart();
db.BulkInsert(datas);
//db.yuanshijilu.AddRange(datas);
try
{
db.SaveChanges();
stoptime.Stop();
var charutime = stoptime.ElapsedMilliseconds;
//更新完成 弹出全部更新完成
Response.Write("<script>if(confirm('全部更新完成。查询用时{" + chaxuntime + "}。插入用时{" + charutime + "}')){window.location='/xuanze.aspx'} else{window.location='/xuanze.aspx'}</script>");
}
catch (Exception ex)
{
//弹出暂时没有更新项，是否重新更新
var redirect = "<script>if(confirm('暂时没有更新项，是否重新更新 ' " + $"或者错误信息{ex.Message}" + ")){window.location='/xuanze.aspx'} else " +
"{window.location='/shujugengxin.aspx'}</script>";
Response.Write(redirect);
};
}
}
protected void Button_qingjia_Click(object sender, EventArgs e)
{
using (MySqlDBContext db = new MySqlDBContext())
{
//删除所有请假数据 重新全部添加
db.Database.ExecuteSqlCommand("TRUNCATE TABLE qingjias");
var userList = db.user.Where(x => x.getdata.Value == 1).Select(x => x.user_id).ToList();
foreach (var item in userList)
{
List <string> ids = GetIdList(item);//获取具体用户的所有审批id
foreach (var id in ids)
{
var data = dd.GetProcessInstance(id);
var operation_records = data.process_instance.operation_records.Count;
//审批通过
if (data.process_instance.result == "agree")
{
var qjData = JsonHelper.DeserializeJsonToObject <dynamic>(Convert.ToString(data.process_instance.form_component_values[0].value));
string startTime = Convert.ToString(qjData[0]);
if (startTime.Contains("上午"))
{
startTime = startTime.Replace("上午", " 08:30:00");
}
else if (startTime.Contains("下午"))
{
startTime = startTime.Replace("下午", " 17:00:00");
}
string endTime = Convert.ToString(qjData[1]);
if (endTime.Contains("上午"))
{
endTime = endTime.Replace("上午", " 08:30:00");
}
else if (endTime.Contains("下午"))
{
endTime = endTime.Replace("下午", " 17:00:00");
}
qingjia qj = new qingjia
{
user_id = item,
开始时间 = startTime,
结束时间 = endTime,
请假类型 = Convert.ToString(qjData[4])
};
db.qingjia.Add(qj);
}
}
}
if (db.SaveChanges() > 0)
{
Response.Write("<script>if(confirm('请假数据更新完成')){window.location='/xuanze.aspx'} else{window.location='/xuanze.aspx'}</script>");
}
else
{
var redirect = "<script>if(confirm('暂时没有更新项，是否重新更新 ')){window.location='/xuanze.aspx'} else " +
"{window.location='/shujugengxin.aspx'}</script>";
Response.Write(redirect);
}
}
}
/// <summary>
/// 打卡详情更新 原理和打卡记录相同 只是这个里面的数据是获取所有的打卡记录
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button_xiangqinggengxin_Click(object sender, EventArgs e)
{
using (MySqlDBContext db = new MySqlDBContext())
{
db.Database.ExecuteSqlCommand("TRUNCATE TABLE dakaxiangqings");
var userList = db.user.Where(x => x.getdata == 1).ToList();
var updateTime = db.dakaxiangqing.Any() ? Convert.ToDateTime(db.dakaxiangqing.Max(x => x.打卡时间)).Date : new DateTime(2019, 2, 25);
List <DateTime> dateTimes = new List <DateTime>();
int days = (DateTime.Now.Date - updateTime).Days;
for (int i = 0; i < days; i++)
{
dateTimes.Add(DateTime.Now.Date.AddDays(-i));
}
var index = dateTimes.Count % 7 == 0 ? dateTimes.Count / 7 : dateTimes.Count / 7 + 1;
var dkxqDatas = new List <dakaxiangqings>();
for (int i = 0; i < userList.Count; i++)
{
var datas = new List <dakaxiangqings>();
var user = userList[i];
for (int t = 0; t < index; t++)
{
var times = dateTimes.Skip(t * 7).Take(7).ToList();
var data = dd.GetAttendanceListRecord(user.user_id, times.Min(), times.Max().AddDays(1).AddMilliseconds(-1));
for (int d = 0; d < data.recordresult.Count; d++)
{
var result = data.recordresult[d];
var dk = new dakaxiangqings
{
userId = result.userId,
审批id = result.procInstId,
工作日 = StringToDateTime(result.workDate.ToString()).AddHours(8),
打卡时间 = StringToDateTime(result.userCheckTime.ToString()).AddHours(8)
};
if (result.baseCheckTime == null)
{
dk.msg = result.invalidRecordMsg;
}
else
{
dk.基准时间 = StringToDateTime(result.baseCheckTime.ToString()).AddHours(8);
}
dkxqDatas.Add(dk);
}
}
}
db.BulkInsert(dkxqDatas);
try
{
db.SaveChanges();
Response.Write("<script>if(confirm('全部更新完成')){window.location='/xuanze.aspx'} else{window.location='/xuanze.aspx'}</script>");
}
catch (Exception ex)
{
var redirect = "<script>if(confirm('暂时没有更新项，是否重新更新 ' " + $"或者错误信息{ex.Message}" + "))){window.location='/xuanze.aspx'} else " +
"{window.location='/shujugengxin.aspx'}</script>";
Response.Write(redirect);
}
}
}
public GoalsController(MySqlDBContext context)
{
_context = context;
}
public StudentRepo(MySqlDBContext mySqlDBContext)
{
_context = mySqlDBContext;
}
//部分更新的原理和全部更新的原理一致 只是获取打卡数据的开始时间是自己设定的
protected void Button_Queding_Click(object sender, EventArgs e)
{
var kaishiTime = Convert.ToDateTime(TextBox_Kaishishijian.Text);
var jieshuTime = Convert.ToDateTime(TextBox_Jieshushijian.Text + " 23:59:59");
if ((jieshuTime - kaishiTime).Days > 7)
{
Response.Write("<script>if(confirm('结束时间和开始时间最大范围不能超过7天')){window.location='/bufengengxin.aspx'} else{window.location='/xuanze.aspx'}</script>");
return;
}
using (MySqlDBContext db = new MySqlDBContext())
{
var userList = db.user.Where(x => x.getdata == 1).ToList();
var maxTime = new DateTime(2019, 2, 25);
if (db.yuanshijilu.Any())
{
maxTime = Convert.ToDateTime(db.yuanshijilu.Max(x => x.考勤时间));
}
if (Convert.ToDateTime(maxTime) > jieshuTime)
{
Response.Write("<script>if(confirm('当前时间段的数据已经更新存在于数据库中,请往后选择时间段更新')){window.location='/bufengengxin.aspx'} else{window.location='/xuanze.aspx'}</script>");
return;
}
if (maxTime > kaishiTime)
{
kaishiTime = maxTime;
}
List <DateTime> dateTimes = new List <DateTime>();
int days = (jieshuTime - kaishiTime).Days;
for (int i = 0; i < days; i++)
{
dateTimes.Add(jieshuTime.Date.AddDays(-i));
}
var index = dateTimes.Count % 7 == 0 ? dateTimes.Count / 7 : dateTimes.Count / 7 + 1;
int offset = 0;
for (int i = 0; i < userList.Count; i++)
{
var user = userList[i];
for (int t = 0; t < index; t++)
{
var times = dateTimes.Skip(t * 7).Take(7).ToList();
var data = dd.GetAttendance(user.user_id, times.Min(), times.Max().AddDays(1).AddMilliseconds(-1), offset, 50);
for (int x = 0; x < data.recordresult.Count; x++)
{
var result = data.recordresult[x];
var jl = new yuanshijilu()
{
user_id = result.userId,
打卡时间 = StringToDateTime(result.userCheckTime.ToString()).AddHours(8),
打卡结果 = ToTimeResult(result.timeResult),
考勤时间 = StringToDateTime(result.baseCheckTime.ToString()).AddHours(8),
考勤日期 = StringToDateTime(result.workDate.ToString())
};
db.yuanshijilu.Add(jl);
}
}
}
if (db.SaveChanges() > 0)
{
Response.Write("<script>if(confirm('部分更新完成')){window.location='/xuanze.aspx'} else{window.location='/xuanze.aspx'}</script>");
}
else
{
Response.Write("<script>if(confirm('暂时没有更新项，是否重新更新')){window.location='/xuanze.aspx'} else{window.location='/ shujugengxin.aspx'}</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>");
}
}
}
public ComsumeService(IRepositoryFactory repositoryFactory, MySqlDBContext mydbcontext) : base(mydbcontext)
{
}
public TasksController(MySqlDBContext context)
{
_context = context;
}
