From 04a14d3a56f9a64668a920e2dfe21d2a7daf89b3 Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期一, 06 三月 2023 16:37:42 +0800
Subject: [PATCH] 1.节拍工价新增、编辑加工价类型字段区分自制、外协 2.工序编辑，工序类型切换保存时删除对应节拍工价表 3.工单派发时判断区分自制、外协工序 4.计件工资报表增加剔除工序参数
---
VueWebApi/DLL/DAL/ReportManagerDAL.cs | 303 +++++++++++++++++++++++++++----------------------
1 files changed, 166 insertions(+), 137 deletions(-)
diff --git a/VueWebApi/DLL/DAL/ReportManagerDAL.cs b/VueWebApi/DLL/DAL/ReportManagerDAL.cs
index b09bb44..aaeedbc 100644
--- a/VueWebApi/DLL/DAL/ReportManagerDAL.cs
+++ b/VueWebApi/DLL/DAL/ReportManagerDAL.cs
@@ -221,129 +221,17 @@
#region[鐝粍宸ヨ祫鎶ヨ〃璁板綍鏌ヨ]
- public static ToMessage GroupSalaryReportSearch(string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate,string rejectstepcode, int startNum, int endNum, string prop, string order)
+ public static ToMessage GroupSalaryReportSearch(string compute, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate, string rejectstepcode, int startNum, int endNum, string prop, string order)
{
var dynamicParams = new DynamicParameters();
string search = "";
try
{
- if (wocode != "" && wocode != null)
+ if (compute == "last") //鏈亾宸ュ簭
{
- search += "and A.wo_code like '%'+@wocode+'%' ";
- dynamicParams.Add("@wocode", wocode);
+ search += "and P.isend=@isend ";
+ dynamicParams.Add("@isend", "Y");
}
- if (partcode != "" && partcode != null)
- {
- search += "and M.partcode like '%'+@partcode+'%' ";
- dynamicParams.Add("@partcode", partcode);
- }
- if (partname != "" && partname != null)
- {
- search += "and M.partname like '%'+@partname+'%' ";
- dynamicParams.Add("@partname", partname);
- }
- if (partspec != "" && partspec != null)
- {
- search += "and M.partspec like '%'+@partspec+'%' ";
- dynamicParams.Add("@partspec", partspec);
- }
- if (stepname != "" && stepname != null)
- {
- search += "and T.stepname like '%'+@stepname+'%' ";
- dynamicParams.Add("@stepname", stepname);
- }
- if (groupcode != "" && groupcode != null)
- {
- search += "and G.group_code=@groupcode ";
- dynamicParams.Add("@groupcode", groupcode);
- }
- if (username != "" && username != null)
- {
- search += "and U.username like '%'+@username+'%' ";
- dynamicParams.Add("@username", username);
- }
- if (operopendate != "" && operopendate != null)
- {
- search += "and B.report_date between @operopendate and @operclosedate ";
- dynamicParams.Add("@operopendate", operopendate + " 00:00:00");
- dynamicParams.Add("@operclosedate", operclosedate + " 23:59:59");
- }
- if (rejectstepcode != "" && rejectstepcode != null)
- {
- string[] s1 = Array.ConvertAll<string, string>(rejectstepcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string鍒嗗壊杞瑂tring[]
- search+= "and A.step_code not in @s1";
- dynamicParams.Add("@s1", s1);
- }
- if (search == "")
- {
- search = "and 1=1 ";
- }
- search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
- // --------------鏌ヨ鎸囧畾鏁版嵁--------------
- var total = 0; //鎬绘潯鏁�
- var sql = @"select distinct A.id,A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,A.task_qty,G.group_code,G.group_name,A.good_qty,
- isnull(S.unprice,0) as unprice,A.good_qty*isnull(S.unprice,0) as usermoney,U.username as lm_user,A.lm_date,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
- left join TStep T on A.step_code=T.stepcode
- left join TUser U on A.lm_user=U.usercode
- where " + 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 GroupSalaryReportSearchUser(string id)
- {
- string sql = "";
- var dynamicParams = new DynamicParameters();
- try
- {
- //鑾峰彇鎶ュ伐浜哄憳
- sql = @"select U.usercode,U.username from TK_Wrk_RecordSub A
- inner join TUser U on A.report_person=U.usercode
- where m_id=@id";
- dynamicParams.Add("@id", id);
- var data = DapperHelper.selectdata(sql, dynamicParams);
- mes.code = "200";
- mes.Message = "鏌ヨ鎴愬姛!";
- mes.data = data;
- }
- catch (Exception e)
- {
- mes.code = "300";
- mes.count = 0;
- mes.Message = e.Message;
- mes.data = null;
- }
- return mes;
- }
- #endregion
-
- #region[鐝粍宸ヨ祫鎶ヨ〃璁板綍瀵煎嚭]
- public static ToMessage GroupSalaryReportExcelSearch(string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate,string rejectstepcode)
- {
- var dynamicParams = new DynamicParameters();
- string search = "";
- try
- {
if (wocode != "" && wocode != null)
{
search += "and A.wo_code like '%'+@wocode+'%' ";
@@ -395,7 +283,130 @@
{
search = "and 1=1 ";
}
- search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ //search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select distinct A.id,A.wo_code,M.partcode,M.partname,M.partspec,T.stepcode,T.stepname,A.task_qty,G.group_code,G.group_name,A.good_qty,
+ isnull(S.unprice,0) as unprice,A.good_qty*isnull(S.unprice,0) as usermoney,U.username as lm_user,A.lm_date,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 TWoPrteEqp_Stad S on A.wo_code=S.wo and 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
+ 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 A.lm_user=U.usercode
+ where G.group_code<>'' " + 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 GroupSalaryReportSearchUser(string id)
+ {
+ string sql = "";
+ var dynamicParams = new DynamicParameters();
+ try
+ {
+ //鑾峰彇鎶ュ伐浜哄憳
+ sql = @"select U.usercode,U.username from TK_Wrk_RecordSub A
+ inner join TUser U on A.report_person=U.usercode
+ where m_id=@id";
+ dynamicParams.Add("@id", id);
+ var data = DapperHelper.selectdata(sql, dynamicParams);
+ mes.code = "200";
+ mes.Message = "鏌ヨ鎴愬姛!";
+ mes.data = data;
+ }
+ catch (Exception e)
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.Message = e.Message;
+ mes.data = null;
+ }
+ return mes;
+ }
+ #endregion
+
+ #region[鐝粍宸ヨ祫鎶ヨ〃璁板綍瀵煎嚭]
+ public static ToMessage GroupSalaryReportExcelSearch(string compute, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string username, string operopendate, string operclosedate, string rejectstepcode)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (compute == "last") //鏈亾宸ュ簭
+ {
+ search += "and P.isend=@isend ";
+ dynamicParams.Add("@isend", "Y");
+ }
+ if (wocode != "" && wocode != null)
+ {
+ search += "and A.wo_code like '%'+@wocode+'%' ";
+ dynamicParams.Add("@wocode", wocode);
+ }
+ if (partcode != "" && partcode != null)
+ {
+ search += "and M.partcode like '%'+@partcode+'%' ";
+ dynamicParams.Add("@partcode", partcode);
+ }
+ if (partname != "" && partname != null)
+ {
+ search += "and M.partname like '%'+@partname+'%' ";
+ dynamicParams.Add("@partname", partname);
+ }
+ if (partspec != "" && partspec != null)
+ {
+ search += "and M.partspec like '%'+@partspec+'%' ";
+ dynamicParams.Add("@partspec", partspec);
+ }
+ if (stepname != "" && stepname != null)
+ {
+ search += "and T.stepname like '%'+@stepname+'%' ";
+ dynamicParams.Add("@stepname", stepname);
+ }
+ if (groupcode != "" && groupcode != null)
+ {
+ search += "and G.group_code=@groupcode ";
+ dynamicParams.Add("@groupcode", groupcode);
+ }
+ if (username != "" && username != null)
+ {
+ search += "and U.username like '%'+@username+'%' ";
+ dynamicParams.Add("@username", username);
+ }
+ if (operopendate != "" && operopendate != null)
+ {
+ search += "and B.report_date between @operopendate and @operclosedate ";
+ dynamicParams.Add("@operopendate", operopendate + " 00:00:00");
+ dynamicParams.Add("@operclosedate", operclosedate + " 23:59:59");
+ }
+ if (rejectstepcode != "" && rejectstepcode != null)
+ {
+ string[] s1 = Array.ConvertAll<string, string>(rejectstepcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string鍒嗗壊杞瑂tring[]
+ search += "and A.step_code not in @s1";
+ dynamicParams.Add("@s1", s1);
+ }
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ //search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
var sql = @"select distinct A.wo_code as 宸ュ崟缂栧彿,M.partcode as 浜у搧缂栫爜,M.partname as 浜у搧鍚嶇О,M.partspec as 浜у搧瑙勬牸,
@@ -403,14 +414,14 @@
A.good_qty as 鎶ュ伐鏁伴噺,isnull(S.unprice,0) as 宸ュ簭鍗曚环,A.good_qty*isnull(S.unprice,0) as 璁′欢宸ヨ祫,U.username as 鎿嶄綔浜哄憳,A.lm_date as 鎿嶄綔鏃堕棿,B.report_date as 鎶ュ伐鏃堕棿
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
+ 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
+ left join TWoPrteEqp_Stad S on A.wo_code=S.wo and 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
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 A.lm_user=U.usercode
- where " + search;
+ where G.group_code<>'' " + search;
DataTable data = DapperHelper.selectdata(sql, dynamicParams);
data.TableName = "Table"; //璁剧疆DataTable鐨勫悕绉�
string msg = DownLoad.DataTableToExcel(data, "鐝粍璁′欢宸ヨ祫鎶ヨ〃");
@@ -432,12 +443,17 @@
#region[浜哄憳宸ヨ祫鏄庣粏鎶ヨ〃]
- public static ToMessage PeopleSalaryReportSearch(string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string reportname, string reportopendate, string reportclosedate, int startNum, int endNum, string prop, string order)
+ public static ToMessage PeopleSalaryReportSearch(string compute, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string reportname, string reportopendate, string reportclosedate, string rejectstepcode, int startNum, int endNum, string prop, string order)
{
var dynamicParams = new DynamicParameters();
string search = "";
try
{
+ if (compute == "last") //鏈亾宸ュ簭
+ {
+ search += "and AA.isend=@isend ";
+ dynamicParams.Add("@isend", "Y");
+ }
if (wocode != "" && wocode != null)
{
search += "and AA.wo_code like '%'+@wocode+'%' ";
@@ -479,7 +495,12 @@
dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
}
-
+ if (rejectstepcode != "" && rejectstepcode != null)
+ {
+ string[] s1 = Array.ConvertAll<string, string>(rejectstepcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string鍒嗗壊杞瑂tring[]
+ search += "and AA.stepcode not in @s1";
+ dynamicParams.Add("@s1", s1);
+ }
if (search == "")
{
search = "and 1=1 ";
@@ -488,19 +509,18 @@
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
var sql = @"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.username,AA.report_date,colum
+ AA.task_qty,AA.group_code,AA.group_name,AA.report_qty,AA.unprice,AA.ratio,AA.isend,
+ AA.moneys as usermoney,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.report_qty*isnull(S.unprice,0) as moneys,U.username,B.report_date,
- (select distinct count(*) from TK_Wrk_RecordSub S where S.m_id=B.m_id) as colum
+ 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,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
+ left join TWoPrteEqp_Stad S on A.wo_code=S.wo and 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
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
@@ -524,12 +544,17 @@
#endregion
#region[浜哄憳宸ヨ祫鏄庣粏鎶ヨ〃瀵煎嚭]
- public static ToMessage PeopleSalaryReportExcelSearch(string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string reportname, string reportopendate, string reportclosedate)
+ public static ToMessage PeopleSalaryReportExcelSearch(string compute, string wocode, string partcode, string partname, string partspec, string stepname, string groupcode, string reportname, string reportopendate, string reportclosedate, string rejectstepcode)
{
var dynamicParams = new DynamicParameters();
string search = "";
try
{
+ if (compute == "last") //鏈亾宸ュ簭
+ {
+ search += "and AA.isend=@isend ";
+ dynamicParams.Add("@isend", "Y");
+ }
if (wocode != "" && wocode != null)
{
search += "and AA.wo_code like '%'+@wocode+'%' ";
@@ -571,7 +596,12 @@
dynamicParams.Add("@reportopendate", reportopendate + " 00:00:00");
dynamicParams.Add("@reportclosedate", reportclosedate + " 23:59:59");
}
-
+ if (rejectstepcode != "" && rejectstepcode != null)
+ {
+ string[] s1 = Array.ConvertAll<string, string>(rejectstepcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string鍒嗗壊杞瑂tring[]
+ search += "and AA.stepcode not in @s1";
+ dynamicParams.Add("@s1", s1);
+ }
if (search == "")
{
search = "and 1=1 ";
@@ -579,20 +609,19 @@
search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select AA.wo_code as 宸ュ崟缂栧彿,AA.partcode as 浜у搧缂栫爜,AA.partname as 浜у搧鍚嶇О,AA.partspec as 浜у搧瑙勬牸,AA.stepcode as 宸ュ簭缂栫爜,AA.stepname as 宸ュ簭鍚嶇О,
- AA.task_qty as 浠诲姟鏁伴噺,AA.group_code as 鐢熶骇鐝粍缂栫爜,AA.group_name as 鐢熶骇鐝粍鍚嶇О,AA.report_qty as 鎶ュ伐鏁伴噺,AA.unprice as 宸ュ簭鍗曚环,
- AA.moneys/colum as 璁′欢宸ヨ祫,AA.username as 鎶ュ伐浜哄憳,AA.report_date as 鎶ュ伐鏃堕棿,colum as 鐝粍鎶ュ伐浜烘暟
+ var sql = @"select AA.wo_code as 宸ュ崟缂栧彿,AA.partcode as 浜у搧缂栫爜,AA.partname as 浜у搧鍚嶇О,AA.partspec as 浜у搧瑙勬牸,AA.stepcode as 宸ュ簭缂栫爜,AA.stepname as 宸ュ簭鍚嶇О,AA.isend as 鏄惁鏈亾宸ュ簭,
+ AA.task_qty as 浠诲姟鏁伴噺,AA.group_code as 鐢熶骇鐝粍缂栫爜,AA.group_name as 鐢熶骇鐝粍鍚嶇О,AA.report_qty as 鎶ュ伐鏁伴噺,AA.unprice as 宸ュ簭鍗曚环,AA.ratio as 鍒嗛厤姣斾緥,
+ AA.moneys as 璁′欢宸ヨ祫,AA.username as 鎶ュ伐浜哄憳,AA.report_date as 鎶ュ伐鏃堕棿
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,U.username,B.report_date,
- (select distinct count(*) from TK_Wrk_RecordSub S where S.m_id=B.m_id) as colum
+ 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,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
+ left join TWoPrteEqp_Stad S on A.wo_code=S.wo and 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
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
--
Gitblit v1.9.3