| | |
|---|
| | | public static SqlParameter[] parameters; //定义全局SqlParameter参数数组 |
|---|
| | | |
|---|
| | | #region[App个人计件工资] |
|---|
| | | public static ToMessage PieceRateWage(string usercode,string startopendate, string endclosedate) |
|---|
| | | public static ToMessage PieceRateWage(string compute, string usercode,string startopendate, string endclosedate) |
|---|
| | | { |
|---|
| | | Dictionary<object, object> dList = new Dictionary<object, object>(); |
|---|
| | | var dynamicParams = new DynamicParameters(); |
|---|
| | | string search = ""; |
|---|
| | | try |
|---|
| | | { |
|---|
| | | if (compute == "last") //末道工序 |
|---|
| | | { |
|---|
| | | search += "and P.isend=@isend "; |
|---|
| | | dynamicParams.Add("@isend", "Y"); |
|---|
| | | } |
|---|
| | | dynamicParams.Add("@usercode", usercode); |
|---|
| | | dynamicParams.Add("@startopendate", startopendate + " 00:00:00"); |
|---|
| | | dynamicParams.Add("@endclosedate", endclosedate + " 23:59:59"); |
|---|
| | | |
|---|
| | | // --------------查询报工数、良品扣除、计算工资收入-------------- |
|---|
| | | 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 B.report_person=@usercode and B.report_date between @startopendate and @endclosedate) as AA) as BB "; |
|---|
| | | 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.ratio,AA.isend, |
|---|
| | | AA.moneys as usermoney,AA.badmoneys as userbadmoney,AA.ngmoneys as userngmoney,AA.username,AA.report_date |
|---|
| | | 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.ratio,P.isend, |
|---|
| | | (B.report_qty*isnull(S.unprice,0))*(B.ratio/100) as moneys,(B.ng_qty*isnull(S.unprice,0))*(B.ratio/100) as ngmoneys,(B.bad_qty*isnull(S.unprice,0))*(B.ratio/100) as badmoneys, |
|---|
| | | B.report_person,U.username,B.report_date |
|---|
| | | 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 |
|---|
| | | 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 B.report_person=@usercode and B.report_date between @startopendate and @endclosedate "+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, |
|---|
| | | 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.ratio, |
|---|
| | | 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_qty,B.ng_qty,B.bad_qty,B.ratio, |
|---|
| | | 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' |
|---|
| | | inner join TK_Wrk_Step P on A.wo_code=P.wo_code and A.step_code=P.step_code |
|---|
| | | 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 B.report_person=@usercode and B.report_date between @startopendate and @endclosedate) 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"; |
|---|
| | | where B.report_person=@usercode and B.report_date between @startopendate and @endclosedate "+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.ratio,AA.report_person,AA.username,colum"; |
|---|
| | | var data1 = DapperHelper.selectdata(sql1, dynamicParams); |
|---|
| | | dList.Add("dt1", data1); |
|---|
| | | mes.code = "200"; |
|---|
