using Dapper; using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Threading.Tasks; using VueWebCoreApi.Models; using VueWebCoreApi.Models.AttendanceSetting; using VueWebCoreApi.Tools; namespace VueWebCoreApi.DLL.DAL { public class HourlyWageDAL { public static DataTable dt; //定义全局变量dt public static bool res; //定义全局变量dt public static ToMessage mes = new ToMessage(); //定义全局返回信息对象 public static string strProcName = ""; //定义全局sql变量 public static List listStr = new List(); //定义全局参数集合 public static SqlParameter[] parameters; //定义全局SqlParameter参数数组 #region[考勤设置查询] public static ToMessage AttendanceSettSearch(string wkshop_code, string creatuser, string paydatestartdate, string paydateenddate, int startNum, int endNum, string prop, string order) { var dynamicParams = new DynamicParameters(); string search = ""; try { if (wkshop_code != "" && wkshop_code != null) { search += " and S.wkshop_code=@wkshop_code "; dynamicParams.Add("@wkshop_code", wkshop_code); } if (creatuser != "" && creatuser != null) { search += " and S.lm_user=@creatuser "; dynamicParams.Add("@creatuser", creatuser); } if (paydatestartdate != "" && paydatestartdate != null) { search += " and S.lm_date between @paydatestartdate and @paydateenddate "; dynamicParams.Add("@paydatestartdate", paydatestartdate + " 00:00:00"); dynamicParams.Add("@paydateenddate", paydateenddate + " 23:59:59"); } // --------------查询指定数据-------------- var total = 0; //总条数 var sql = @"select S.wkshop_code,T.torg_name as wkshop_name,S.workhouse,S.uphouse,S.closehouse,S.downhouse,S.workprice,overprice,U.username,S.lm_date from AttendanceSettings S left join TOrganization T on S.wkshop_code=T.torg_code left join TUser U on S.lm_user=U.usercode where 1=1 " + search; var data = DapperHelper.GetPageList(sql, dynamicParams, prop, order, startNum, endNum, out total); mes.code = "200"; mes.message = "查询成功!"; mes.count = total; mes.data = data.ToList(); } catch (Exception e) { mes.code = "300"; mes.count = 0; mes.message = e.Message; mes.data = null; } return mes; } #endregion #region[考勤设置新增/编辑] public static ToMessage AttendanceSettAddUpdate(AttendanceSett attendancesett, string OperType, User us) { string sql = ""; var dynamicParams = new DynamicParameters(); List list = new List(); try { if (OperType == "Add") { //写入计时工资设置表 sql = @"insert into AttendanceSettings(wkshop_code,workhouse,closehouse,workprice,overprice,uphouse,downhouse,lm_user,lm_date) values(@wkshop_code,@workhouse,@closehouse,@workprice,@overprice,@uphouse,@downhouse,@lm_user,@lm_date)"; list.Add(new { str = sql, parm = new { wkshop_code = attendancesett.wkshop_code, workhouse = attendancesett.workhouse, closehouse = attendancesett.closehouse, workprice = attendancesett.workprice, overprice = attendancesett.overprice, uphouse = attendancesett.uphouse, downhouse = attendancesett.downhouse, lm_user = us.usercode, lm_date = DateTime.Now.ToString() } }); bool aa = DapperHelper.DoTransaction(list); if (aa) { //写入操作记录表 LogHelper.DbOperateLog(us.usercode, "新增", "新增了考勤设置车间为:" + attendancesett.wkshop_code + ",上班时间为:" + attendancesett.workhouse + ",下班时间为:" + attendancesett.closehouse + "的标准", us.usertype); mes.code = "200"; mes.count = 0; mes.message = "考勤设置新建成功!"; mes.data = null; } else { mes.code = "300"; mes.count = 0; mes.message = "考勤设置新建失败!"; mes.data = null; } } if (OperType == "Update") { //修改工单表 sql = @"update AttendanceSettings set workhouse=@workhouse,closehouse=@closehouse,workprice=@workprice,overprice=@overprice,uphouse=@uphouse,downhouse=@downhouse, lm_user=@lm_user,lm_date=@lm_date where wkshop_code=@wkshop_code"; list.Add(new { str = sql, parm = new { wkshop_code = attendancesett.wkshop_code, workhouse = attendancesett.workhouse, closehouse = attendancesett.closehouse, workprice = attendancesett.workprice, overprice = attendancesett.overprice, uphouse = attendancesett.uphouse, downhouse = attendancesett.downhouse, lm_user = us.usercode, lm_date = DateTime.Now.ToString() } }); bool aa = DapperHelper.DoTransaction(list); if (aa) { //写入操作记录表 LogHelper.DbOperateLog(us.usercode, "修改", "修改了考勤设置车间为:" + attendancesett.wkshop_code + ",上班时间为:" + attendancesett.workhouse + ",下班时间为:" + attendancesett.closehouse + "的标准", us.usertype); mes.code = "200"; mes.count = 0; mes.message = "考勤设置修改成功!"; mes.data = null; } else { mes.code = "300"; mes.count = 0; mes.message = "考勤设置修改失败!"; mes.data = null; } } } catch (Exception e) { mes.code = "300"; mes.count = 0; mes.message = e.Message; mes.data = null; } return mes; } #endregion #region[考勤设置删除] public static ToMessage AttendanceSettDelete(AttendanceSett attendancesett, User us) { var sql = ""; List list = new List(); var dynamicParams = new DynamicParameters(); try { //校验当前考勤设置是否被引用 sql = @"select * from Attendance where wkshop_code=@wkshop_code"; dynamicParams.Add("@wkshop_code", attendancesett.wkshop_code); var data = DapperHelper.selectdata(sql, dynamicParams); if (data.Rows.Count > 0) { mes.code = "300"; mes.count = 0; mes.message = "当前考勤设置已经生成考勤记录,不允许删除!"; mes.data = null; return mes; } else { //计时工资设置表 sql = @"delete AttendanceSettings where wkshop_code=@wkshop_code"; list.Add(new { str = sql, parm = new { wkshop_code = attendancesett.wkshop_code } }); } bool aa = DapperHelper.DoTransaction(list); LogHelper.WriteLogData(aa.ToString()); if (aa) { //写入操作记录表 LogHelper.DbOperateLog(us.usercode, "删除", "删除了考勤设置车间为:" + attendancesett.wkshop_code + ",上班时间为:" + attendancesett.workhouse + ",下班时间为:" + attendancesett.closehouse + "的标准", us.usertype); mes.code = "200"; mes.count = 0; mes.message = "考勤设置删除成功!"; mes.data = null; } else { mes.code = "300"; mes.count = 0; mes.message = "考勤设置删除失败!"; mes.data = null; } } catch (Exception e) { mes.code = "300"; mes.count = 0; mes.message = e.Message; mes.data = null; } return mes; } #endregion #region[考勤打卡信息查询] public static ToMessage AttendanceSearch(string usercode) { var dynamicParams = new DynamicParameters(); string search = ""; Dictionary dir = new Dictionary(); try { if (usercode == "" || usercode == null) { mes.code = "300"; mes.count = 0; mes.message = "工号不能为空!"; mes.data = null; return mes; } else { search += " and A.usercode=@usercode "; dynamicParams.Add("@usercode", usercode); } // --------------查询指定数据-------------- var sql = @"select A.wkshop_code,T.torg_name as wkshop_name,U.username,A.createdate,S.workprice from Attendance A left join AttendanceSettings S on A.wkshop_code=S.wkshop_code left join TOrganization T on A.wkshop_code=T.torg_code left join TUser U on A.usercode=U.usercode where convert(date, A.createdate) = convert(date, getdate()) " + search; var data = DapperHelper.selectdata(sql, dynamicParams); //获取用户信息 var sql0 = @"select U.usercode,U.username,U.storg_code,T.torg_name, STUFF((SELECT ',' + P.postname FROM TPost P WHERE CHARINDEX(',' + P.postcode + ',', ',' + U.post_code + ',') > 0 FOR XML PATH('')), 1, 1, '') AS postname from TUser U left join TOrganization T on U.storg_code=T.torg_code where U.usercode=@usercode"; dynamicParams.Add("@usercode", usercode); var data0 = DapperHelper.selectdata(sql0, dynamicParams); dir.Add("userdate", data0); dir.Add("checkcrd", data); mes.code = "200"; mes.message = "查询成功!"; mes.count = dir.Count; mes.data = dir; } catch (Exception e) { mes.code = "300"; mes.count = 0; mes.message = e.Message; mes.data = null; } return mes; } #endregion #region[考勤打卡信息提交] public static ToMessage AttendanceSave(Attendance attendance) { string sql = ""; List list = new List(); try { //写入计时工资考勤打卡表 sql = @"insert into Attendance(wkshop_code,usercode,createdate) values(@wkshop_code,@usercode,@createdate)"; list.Add(new { str = sql, parm = new { wkshop_code = attendance.wkshop_code, usercode = attendance.user_code, createdate = DateTime.Now.ToString() } }); bool aa = DapperHelper.DoTransaction(list); if (aa) { //写入操作记录表 LogHelper.DbOperateLog(attendance.user_code, "打卡", "用户:" + attendance.user_code + "打卡,打卡时间为:" + DateTime.Now.ToString() + "", "PC"); mes.code = "200"; mes.count = 0; mes.message = "打卡成功!"; mes.data = null; } else { mes.code = "300"; mes.count = 0; mes.message = "打卡失败!"; mes.data = null; } } catch (Exception e) { mes.code = "300"; mes.count = 0; mes.message = e.Message; mes.data = null; } return mes; } #endregion #region[考勤记录查询] public static ToMessage AttendanceRecordSearch(string wkshop_code, string creatusercode, string paydatestartdate, string paydateenddate,string status, int startNum, int endNum, string prop, string order) { var dynamicParams = new DynamicParameters(); string search = ""; try { if (wkshop_code != "" && wkshop_code != null) { search += " and S.wkshop_code=@wkshop_code "; dynamicParams.Add("@wkshop_code", wkshop_code); } if (creatusercode != "" && creatusercode != null) { search += " and A.usercode=@creatusercode"; dynamicParams.Add("@creatusercode", creatusercode); } if (paydatestartdate != "" && paydatestartdate != null) { search += " and A.createdate between @paydatestartdate and @paydateenddate "; dynamicParams.Add("@paydatestartdate", paydatestartdate + " 00:00:00"); dynamicParams.Add("@paydateenddate", paydateenddate + " 23:59:59"); } if (status != "" && status != null) { search += " and A.status=@status "; dynamicParams.Add("@status", status); } // --------------查询指定数据-------------- var total = 0; //总条数 var sql = @"select A.id,S.wkshop_code,T.torg_name as wkshop_name,A.usercode,U.username,A.createdate,S.workprice,A.status from Attendance A left join AttendanceSettings S on A.wkshop_code=S.wkshop_code left join TOrganization T on A.wkshop_code=T.torg_code left join TUser U on A.usercode=U.usercode where 1=1 " + search; var data = DapperHelper.GetPageList(sql, dynamicParams, prop, order, startNum, endNum, out total); mes.code = "200"; mes.message = "查询成功!"; mes.count = total; mes.data = data.ToList(); } catch (Exception e) { mes.code = "300"; mes.count = 0; mes.message = e.Message; mes.data = null; } return mes; } #endregion #region[考勤记录新增编辑] public static ToMessage AttendanceRecordAddUpdate(AttendanceRecord attendanceRecord, string operType, User us) { string sql = ""; var dynamicParams = new DynamicParameters(); List list = new List(); try { if (operType == "Add") { //写入计时工资考勤打卡表 sql = @"insert into Attendance(wkshop_code,usercode,createdate,status) values(@wkshop_code,@usercode,@createdate,@status)"; list.Add(new { str = sql, parm = new { wkshop_code = attendanceRecord.wkshop_code, usercode = attendanceRecord.creatusercode, createdate = attendanceRecord.creatdate, status = attendanceRecord.status } }); bool aa = DapperHelper.DoTransaction(list); if (aa) { //写入操作记录表 LogHelper.DbOperateLog(us.usercode, "新增", "新增了打卡记录车间为:" + attendanceRecord.wkshop_code + ",人员为:" + attendanceRecord.creatusercode + ",考勤时间为:" + attendanceRecord.creatdate + "", us.usertype); mes.code = "200"; mes.count = 0; mes.message = "新增打卡记录成功!"; mes.data = null; } else { mes.code = "300"; mes.count = 0; mes.message = "新增打卡记录失败!"; mes.data = null; } } if (operType == "Update") { //修改计时工资考勤打卡表 sql = @"update Attendance set createdate=@createdate where wkshop_code=@wkshop_code and usercode=@usercode and id=@id"; list.Add(new { str = sql, parm = new { id= attendanceRecord.id, wkshop_code = attendanceRecord.wkshop_code, usercode = attendanceRecord.creatusercode, createdate = attendanceRecord.creatdate } }); bool aa = DapperHelper.DoTransaction(list); if (aa) { //写入操作记录表 LogHelper.DbOperateLog(us.usercode, "修改", "修改了打卡记录id为"+attendanceRecord.id+",车间为:" + attendanceRecord.wkshop_code + ",人员为:" + attendanceRecord.creatusercode + ",考勤时间为:" + attendanceRecord.creatdate + "", us.usertype); mes.code = "200"; mes.count = 0; mes.message = "修改打卡记录成功!"; mes.data = null; } else { mes.code = "300"; mes.count = 0; mes.message = "修改打卡记录失败!"; mes.data = null; } } } catch (Exception e) { mes.code = "300"; mes.count = 0; mes.message = e.Message; mes.data = null; } return mes; } #endregion #region[考勤记录删除] public static ToMessage AttendanceRecordDelete(AttendanceRecord attendanceRecord, User us) { var sql = ""; List list = new List(); var dynamicParams = new DynamicParameters(); try { //计时工资考勤打卡表 sql = @"delete Attendance where id=@id"; list.Add(new { str = sql, parm = new { id = attendanceRecord.id } }); bool aa = DapperHelper.DoTransaction(list); LogHelper.WriteLogData(aa.ToString()); if (aa) { //写入操作记录表 LogHelper.DbOperateLog(us.usercode, "删除", "删除了打卡记录车间为:" + attendanceRecord.wkshop_code + ",人员为:" + attendanceRecord.creatusercode + ",考勤时间为:" + attendanceRecord.creatdate + "", us.usertype); mes.code = "200"; mes.count = 0; mes.message = "打卡记录删除成功!"; mes.data = null; } else { mes.code = "300"; mes.count = 0; mes.message = "打卡记录删除失败!"; mes.data = null; } } catch (Exception e) { mes.code = "300"; mes.count = 0; mes.message = e.Message; mes.data = null; } return mes; } #endregion #region[考勤记录核算提交] public static ToMessage AttendanceRecordSave(AttendanceSave attendanceSave, User us) { var sql = ""; var dynamicParams = new DynamicParameters(); string paydatestartdate = ""; //开始时间 string paydateenddate = ""; //结束时间 try { if (attendanceSave.creatdate != "" && attendanceSave.creatdate != null) { paydatestartdate = attendanceSave.creatdate.Split('~')[0].ToString(); paydateenddate = attendanceSave.creatdate.Split('~')[1].ToString(); } //存储过程名 DataTable dt = new DataTable(); sql = @"h_p_MES_HourlyWage"; dynamicParams.Add("@wkshop_code", attendanceSave.wkshop_code); dynamicParams.Add("@usercode", attendanceSave.creatusercode); dynamicParams.Add("@startdate", paydatestartdate); dynamicParams.Add("@closedate", paydateenddate); dynamicParams.Add("@status", attendanceSave.status); bool a = DapperHelper.IsProcedure(sql, dynamicParams); if (a) { //写入操作记录表 LogHelper.DbOperateLog(us.usercode, "计时工资核算", "用户:" + us.usercode + "进行核算,操作时间为：" + DateTime.Now.ToString() + "", "PC"); mes.code = "200"; mes.count = 0; mes.message = "核算成功!"; mes.data = null; } else { mes.code = "300"; mes.count = 0; mes.message = "核算失败!"; mes.data = null; } } catch (Exception e) { mes.code = "300"; mes.count = 0; mes.message = e.Message; mes.data = null; } return mes; } #endregion #region[日计时工资报表查询] public static ToMessage DailyWageReportSearch(string wkshop_code, string creatusercode, string paydatestartdate, string paydateenddate, int startNum, int endNum, string prop, string order) { var dynamicParams = new DynamicParameters(); string search = ""; try { if (wkshop_code != "" && wkshop_code != null) { search += " and S.wkshop_code=@wkshop_code "; dynamicParams.Add("@wkshop_code", wkshop_code); } if (creatusercode != "" && creatusercode != null) { search += " and S.usercode=@creatusercode"; dynamicParams.Add("@creatusercode", creatusercode); } if (paydatestartdate != "" && paydatestartdate != null) { search += " and S.checkindate between @paydatestartdate and @paydateenddate "; dynamicParams.Add("@paydatestartdate", paydatestartdate + " 00:00:00"); dynamicParams.Add("@paydateenddate", paydateenddate + " 23:59:59"); } // --------------查询指定数据-------------- var total = 0; //总条数 var sql = @"select S.id,S.wkshop_code,T.torg_name as wkshop_name,S.usercode,U.username,S.checkindate,S.workcount,S.workprice, S.overcount,S.overprice,S.deductcount,S.deductmoney,S.timimoney from AttendanceSum S left join TOrganization T on S.wkshop_code=T.torg_code left join TUser U on S.usercode=U.usercode where 1=1 " + search; var data = DapperHelper.GetPageList(sql, dynamicParams, prop, order, startNum, endNum, out total); mes.code = "200"; mes.message = "查询成功!"; mes.count = total; mes.data = data.ToList(); } catch (Exception e) { mes.code = "300"; mes.count = 0; mes.message = e.Message; mes.data = null; } return mes; } #endregion #region[日计时工资修改扣除时长、扣除薪资] public static ToMessage DailyWageReportUpdate(DailyWageReport dailyWageReport, User us) { var sql = ""; List list = new List(); var dynamicParams = new DynamicParameters(); try { //计时工资日汇总表 sql = @"update AttendanceSum set deductcount=@deductcount,deductmoney=@deductmoney where id=@id"; list.Add(new { str = sql, parm = new { id = dailyWageReport.id, deductcount = dailyWageReport.deductcount, deductmoney = dailyWageReport.deductmoney } }); bool aa = DapperHelper.DoTransaction(list); LogHelper.WriteLogData(aa.ToString()); if (aa) { //写入操作记录表 LogHelper.DbOperateLog(us.usercode, "修改", "修改了工资日汇总表,车间为:"+dailyWageReport.whshop_code+",打卡人:"+dailyWageReport.usercode+",打卡时间为:"+dailyWageReport.checkindate+",扣除时长为:" + dailyWageReport.deductcount + ",扣除工资为:" + dailyWageReport.deductmoney + "的工资记录", us.usertype); mes.code = "200"; mes.count = 0; mes.message = "工资修改成功!"; mes.data = null; } else { mes.code = "300"; mes.count = 0; mes.message = "工资修改失败!"; mes.data = null; } } catch (Exception e) { mes.code = "300"; mes.count = 0; mes.message = e.Message; mes.data = null; } return mes; } #endregion #region[日计时工资报表导出] public static ToMessage DailyWageReportExcelSearch(string wkshop_code, string creatusercode, string paydatestartdate, string paydateenddate) { var dynamicParams = new DynamicParameters(); string search = ""; try { if (wkshop_code != "" && wkshop_code != null) { search += " and S.wkshop_code=@wkshop_code "; dynamicParams.Add("@wkshop_code", wkshop_code); } if (creatusercode != "" && creatusercode != null) { search += " and S.usercode=@creatusercode"; dynamicParams.Add("@creatusercode", creatusercode); } if (paydatestartdate != "" && paydatestartdate != null) { search += " and S.checkindate between @paydatestartdate and @paydateenddate "; dynamicParams.Add("@paydatestartdate", paydatestartdate + " 00:00:00"); dynamicParams.Add("@paydateenddate", paydateenddate + " 23:59:59"); } // --------------查询指定数据-------------- var total = 0; //总条数 var sql = @"select S.wkshop_code as 所属车间编码,T.torg_name as 所属车间,S.usercode as 打卡人员编码,U.username as 打卡人员,S.checkindate as 打卡日期, S.workcount as '上班时长(小时)',S.workprice as '工价(元/小时)',S.overcount as '加班时长(小时)',S.overprice as '加班工价(元/小时)', S.deductcount as '扣除时长(小时)',S.deductmoney as '扣除工资(元)',S.timimoney as '计时工资(元)',(S.timimoney-S.deductmoney) as '结算工资(元)' from AttendanceSum S left join TOrganization T on S.wkshop_code=T.torg_code left join TUser U on S.usercode=U.usercode where 1=1 " + search; DataTable data = DapperHelper.selectdata(sql, dynamicParams); data.TableName = "Table"; //设置DataTable的名称 string msg = DownLoad.DataTableToExcel(data, "日计时工资报表"); mes.code = "200"; mes.message = "查询成功!"; mes.count = total; mes.data = msg; } catch (Exception e) { mes.code = "300"; mes.count = 0; mes.message = e.Message; mes.data = null; } return mes; } #endregion #region[月计时工资报表查询] public static ToMessage MonthlyWageReportSearch(string wkshop_code, string paydatestartdate, string paydateenddate, int startNum, int endNum, string prop, string order) { var dynamicParams = new DynamicParameters(); string search = ""; try { if (wkshop_code != "" && wkshop_code != null) { search += " and S.wkshop_code=@wkshop_code "; dynamicParams.Add("@wkshop_code", wkshop_code); } if (paydatestartdate != "" && paydatestartdate != null) { search += " and S.checkindate between @paydatestartdate and @paydateenddate "; dynamicParams.Add("@paydatestartdate", paydatestartdate + " 00:00:00"); dynamicParams.Add("@paydateenddate", paydateenddate + " 23:59:59"); } // --------------查询指定数据-------------- var total = 0; //总条数 var sql = @"select T.torg_name as wkshop_name,U.username, sum(S.workcount) as TotalWorkCount, avg(S.workprice) as workprice, sum(S.overcount) AS TotalOverCount, avg(S.overprice) as overprice, (sum(S.workcount) * avg(S.workprice)) AS TotalWorkValue, (sum(S.overcount) * avg(S.overprice)) AS TotalOverValue, sum(S.timimoney) AS TotalTimedMoney from AttendanceSum S left join TOrganization T on S.wkshop_code=T.torg_code left join TUser U on S.usercode=U.usercode where 1=1 " + search+ " group by T.torg_name,U.username"; var data = DapperHelper.GetPageList(sql, dynamicParams, prop, order, startNum, endNum, out total); mes.code = "200"; mes.message = "查询成功!"; mes.count = total; mes.data = data.ToList(); } catch (Exception e) { mes.code = "300"; mes.count = 0; mes.message = e.Message; mes.data = null; } return mes; } #endregion #region[月计时工资报表导出] public static ToMessage MonthlyWageReportExcelSearch(string wkshop_code, string paydatestartdate, string paydateenddate) { var dynamicParams = new DynamicParameters(); string search = ""; try { if (wkshop_code != "" && wkshop_code != null) { search += " and S.wkshop_code=@wkshop_code "; dynamicParams.Add("@wkshop_code", wkshop_code); } if (paydatestartdate != "" && paydatestartdate != null) { search += " and S.checkindate between @paydatestartdate and @paydateenddate "; dynamicParams.Add("@paydatestartdate", paydatestartdate + " 00:00:00"); dynamicParams.Add("@paydateenddate", paydateenddate + " 23:59:59"); } // --------------查询指定数据-------------- var total = 0; //总条数 var sql = @"select T.torg_name as '所属车间',U.username as '打卡人员', sum(S.workcount) as '总工作时长(小时)', avg(S.workprice) as '每小时单价(元)', sum(S.overcount) as '总加班时长(小时)', avg(S.overprice) as '加班每小时单价(元)', (sum(S.workcount) * avg(S.workprice)) as '总工作工资(元)', (sum(S.overcount) * avg(S.overprice)) as '总加班工资(元)', sum(S.timimoney) AS '总计时工资(元)' from AttendanceSum S left join TOrganization T on S.wkshop_code=T.torg_code left join TUser U on S.usercode=U.usercode where 1=1 " + search + " group by T.torg_name,U.username"; DataTable data = DapperHelper.selectdata(sql, dynamicParams); data.TableName = "Table"; //设置DataTable的名称 string msg = DownLoad.DataTableToExcel(data, "月计时工资报表"); mes.code = "200"; mes.message = "查询成功!"; mes.count = total; mes.data = msg; } catch (Exception e) { mes.code = "300"; mes.count = 0; mes.message = e.Message; mes.data = null; } return mes; } #endregion } }