using Dapper;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Web;
|
using VueWebApi.Tools;
|
|
namespace VueWebApi.DLL.DAL
|
{
|
public class AppPersonalDAL
|
{
|
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[App个人计件工资]
|
public static ToMessage PieceRateWage(string usercode,string startopendate, string endclosedate)
|
{
|
Dictionary<object, object> dList = new Dictionary<object, object>();
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (usercode != "" && usercode != null)
|
{
|
search += "and B.report_person=@usercode ";
|
dynamicParams.Add("@usercode", usercode);
|
}
|
if (startopendate != "" && startopendate != null)
|
{
|
search += "and B.report_date between @startopendate and @endclosedate ";
|
dynamicParams.Add("@startopendate", startopendate + " 00:00:00");
|
dynamicParams.Add("@endclosedate", endclosedate + " 23:59:59");
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询报工数、良品扣除、计算工资收入--------------
|
var sql = @"select isnull(sum(BB.report_qty),0) as report_qty,(isnull(sum(BB.userngmoney),0)+isnull(sum(BB.userbadmoney),0)) as userngbadmoney,
|
isnull(sum(BB.usermoney),0)-(isnull(sum(BB.userngmoney),0)+isnull(sum(BB.userbadmoney),0)) as usermoney
|
from (
|
select AA.wo_code,AA.partcode,AA.partname,AA.partspec,AA.stepcode,AA.stepname,
|
AA.task_qty,AA.group_code,AA.group_name,AA.report_qty,AA.unprice,
|
AA.moneys/colum as usermoney,AA.ngmoneys/colum as userngmoney,AA.badmoneys/colum as userbadmoney,AA.report_person,AA.username,AA.report_date,colum
|
from (
|
select distinct A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,
|
A.task_qty,G.group_code,G.group_name,B.report_qty,isnull(S.unprice,0) as unprice,
|
B.report_qty*isnull(S.unprice,0) as moneys,B.ng_qty*isnull(S.unprice,0) as ngmoneys,B.bad_qty*isnull(S.unprice,0) as badmoneys,
|
B.report_person,U.username,B.report_date,
|
(select distinct count(*) from TK_Wrk_RecordSub S where S.m_id=B.m_id) as colum
|
from TK_Wrk_Record A
|
inner join TK_Wrk_RecordSub B on A.id=B.m_id
|
inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code and P.isend='Y'
|
left join TK_Wrk_Man K on A.wo_code=K.wo_code
|
left join TGroup G on B.usergroup_code=G.group_code
|
left join TPrteEqp_Stad S on A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code and K.wkshp_code=S.wkspcode
|
left join TMateriel_Info M on A.materiel_code=M.partcode
|
left join TStep T on A.step_code=T.stepcode
|
left join TUser U on B.report_person=U.usercode
|
where "+search+") as AA) as BB ";
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
dList.Add("dt0", data0);
|
// --------------查询报工明细(按照工单、产品、工序分组)--------------
|
var sql1 = @"select AA.wo_code,AA.partcode,AA.partname,AA.stepcode,AA.stepname,
|
AA.task_qty,AA.group_code,AA.group_name,AA.unprice,
|
isnull(sum(AA.report_qty),0) as report_qty,isnull(sum(AA.ng_qty),0) as ng_qty,isnull(sum(AA.bad_qty),0) as bad_qty,
|
AA.report_person,AA.username,AA.colum
|
from (
|
select distinct A.wo_code,M.partcode,M.partname,T.stepcode,T.stepname,
|
A.task_qty,G.group_code,G.group_name,isnull(S.unprice,0) as unprice,
|
B.report_qty,B.ng_qty,B.bad_qty,
|
B.report_person,U.username,B.report_date,
|
(select distinct count(*) from TK_Wrk_RecordSub S where S.m_id=B.m_id) as colum
|
from TK_Wrk_Record A
|
inner join TK_Wrk_RecordSub B on A.id=B.m_id
|
inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code and P.isend='Y'
|
left join TK_Wrk_Man K on A.wo_code=K.wo_code
|
left join TGroup G on B.usergroup_code=G.group_code
|
left join TPrteEqp_Stad S on A.materiel_code=S.materiel_code and A.eqp_code=S.eqp_code and A.step_code=S.step_code and K.route_code=S.route_code and K.wkshp_code=S.wkspcode
|
left join TMateriel_Info M on A.materiel_code=M.partcode
|
left join TStep T on A.step_code=T.stepcode
|
left join TUser U on B.report_person=U.usercode
|
where "+search+") as AA group by AA.wo_code,AA.partcode,AA.partname,AA.stepcode,AA.stepname,AA.task_qty,AA.group_code,AA.group_name,AA.unprice,AA.report_person,AA.username,colum";
|
var data1 = DapperHelper.selectdata(sql1, dynamicParams);
|
dList.Add("dt1", data1);
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.data = dList;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
}
|
}
|
