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<SqlParameter> listStr = new List<SqlParameter>(); //定义全局参数集合
|
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<object>(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<object> list = new List<object>();
|
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<object> list = new List<object>();
|
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<object, object> dir = new Dictionary<object, object>();
|
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<object> list = new List<object>();
|
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<object>(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<object> list = new List<object>();
|
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<object> list = new List<object>();
|
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<object>(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<object> list = new List<object>();
|
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<object>(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
|
}
|
}
|
