using Hh.Mes.Common.Reflect;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SqlSugar;
using Hh.Mes.Common.log;
using Hh.Mes.Common.Request;
using Hh.Mes.Service.Repository;
using Hh.Mes.POJO.Entity;
using Hh.Mes.Pojo.System;
using Hh.Mes.Common.DataTableTo;
using Hh.Mes.POJO.Response;
namespace Hh.Mes.Service.Configure
{
/// <summary>
/// 工位表
/// </summary>
public class StationService : RepositorySqlSugar<base_work_station>
{
/// <summary>
/// //获取列表
/// </summary>
public Response GetStationList(PageReq pageReq, base_work_station model)
{
var result = new Response();
string orderBy = (pageReq == null || string.IsNullOrEmpty(pageReq.field)) ? " id desc" : $"{pageReq.field} {pageReq.order} ";
string sqlWhere = SqlWhere(model);
var stringBuilder = new StringBuilder();
//页码，页数
//Exel ture 不分页
if (!model.Exel && pageReq != null)
{
stringBuilder.Append("declare @pageIndex int,@pageSize int,@offset int");
stringBuilder.AppendLine($" select @pageIndex={pageReq.page}, @pageSize={pageReq.limit}, @offset=(@pageIndex - 1) * @pageSize");
}
stringBuilder.AppendLine($@" select
t1.* , t2.lineName,
t4.workCenterName
from base_work_station t1 with(nolock)
left join base_line t2 with(nolock) on t1.lineCode = t2.lineCode
left join base_work_center_station_rel t3 on t3.workStationCode =t1.workStationCode
left join base_work_center t4 on t4.workCenterCode = t3.workCenterCode
where {sqlWhere}
order by {orderBy} ");
//Exel ture 不分页
if (!model.Exel)
{
stringBuilder.AppendLine(" offset @offset row fetch next @pageSize row only ");
stringBuilder.Append($" select rowTotal= count(*) from base_work_station t1 with(nolock) where {sqlWhere}");
}
var ds = base.Context.Ado.GetDataSetAll(stringBuilder.ToString(), new List<SugarParameter>(){
new SugarParameter("@lineCode", model.lineCode),
new SugarParameter("@workStationName", model.workStationName),
new SugarParameter("@attribute", model.attribute),
});
//result.Result = ds.Tables[0];
#region 对数据行进行处理，一个工位关联多个工作中心时拼接
DataTable dt = ds.Tables[0].Copy();
dt.Rows.Clear();
var rowGroup = from a in ds.Tables[0].AsEnumerable()
group a by a.Field<string>("workStationCode") into g
select g;
foreach (var item in rowGroup)
{
var row = item.First();
row.SetField<string>("workCenterName", item.Select(t => t.Field<string>("workCenterName")).Distinct().Aggregate((a, b) => a + "," + b));
dt.ImportRow(row);
}
#endregion
result.Result = dt;
result.Count = dt.Rows.Count;//model.Exel ? (int)result.Result.Rows.Count : (int)ds.Tables[1].Rows[0]["rowTotal"];
return result;
}
public string SqlWhere(base_work_station model)
{
var stringBuilder = new StringBuilder();
stringBuilder.Append("1=1");
if (model.lineCode != null) stringBuilder.Append(" and t1.lineCode =@lineCode ");
if (model.workStationName != null) stringBuilder.Append(" and t1.workStationName =@workStationName ");
if (!string.IsNullOrEmpty(model.attribute)) stringBuilder.Append(" and t1.attribute = @attribute ");
return stringBuilder.ToString();
}
/// <summary>
/// /左侧列表 公司+ 工厂+车间+线体
/// </summary>
public DataTable GetTreeList()
{
string sql = $@"select id=companyId,
name,
keys ='00000000-0000-0000-0000-000000000000',
parentId=null,isok='',lineCode=''
from base_company where isDelete={SystemVariable.AddOrUpdateFlag}
union all
select id,
name= factoryName,
keys=factoryKey,
parentId ='00000000-0000-0000-0000-000000000000',isok='',lineCode=''
from base_factory
union all
select id,workShopName,keys=workShopKey,
parentId=(select factoryKey from base_factory t1 where t1.factoryKey=base_workshop.factoryKey ),
isok='',lineCode=''
from base_workshop
union all
select id,lineName,keys=lineKey,
parentId=(select workshopKey from base_workshop t1 where t1.workShopKey=base_line.workshopKey ),
isok='true',lineCode
from base_line";
var dt = base.Context.Ado.GetDataTableAsync(sql).Result;
return dt == null || dt.Rows.Count == 0 ? null : dt;
}
/// <summary>
/// //获取 工位看板列表
/// </summary>
public Response GetBoardList(PageReq pageReq, base_work_station model)
{
var result = new Response();
//工位看板
model.attribute = "board";
string sqlWhere = SqlWhere(model);
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine($@" select t1.* , t2.lineName
from [dbo].base_work_station t1 with(nolock)
left join base_line t2 with(nolock) on t1.lineCode = t2.lineCode
where {sqlWhere} ");
var dt = base.Context.Ado.GetDataTable(stringBuilder.ToString(), new List<SugarParameter>(){
new SugarParameter("@lineCode", model.lineCode),
new SugarParameter("@workStationName", model.workStationName),
new SugarParameter("@attribute", model.attribute),
});
if (dt.IsEmpty())
{
result.Result = "[]";
}
else
{
result.Result = dt;
}
return result;
}
/// <summary>
/// 新增 code = 字典前缀+对应表标识最大值
/// </summary>
/// <returns></returns>
public dynamic Ins(base_work_station model)
{
var response = new Response();
return ExceptionsHelp.Instance.ExecuteT(() =>
{
if (Context.Queryable<base_work_station>().Where(x => x.monitorIP == model.monitorIP).Any())
{
response.Status = false;
response.Message = $"IP为{model.monitorIP}的工位已经存在！";
return response;
}
model.createBy = sysWebUser?.Account;
model.createTime = DateTime.Now;
response.Status = Add(model);
if (!response.Status) response.Message = SystemVariable.dataActionError;
return response;
});
}
/// <summary>
/// 根据主键数组 删除
/// </summary>
public dynamic DelByIds(int[] ids)
{
var response = new Response();
return ExceptionsHelp.Instance.ExecuteT(() =>
{
response.Status = Context.Deleteable<base_work_station>().In(ids).ExecuteCommand() > 0;
if (!response.Status) response.Message = SystemVariable.dataActionError;
return response;
});
}
/// <summary>
/// 更新
/// </summary>
public dynamic Upd(base_work_station model)
{
var response = new Response();
return ExceptionsHelp.Instance.ExecuteT(() =>
{
if (Context.Queryable<base_work_station>().Where(x => x.monitorIP == model.monitorIP).Count() > 1)
{
response.Status = false;
response.Message = $"IP为{model.monitorIP}的工位已经存在！";
return response;
}
model.updateBy = sysWebUser?.Account;
model.updateTime = DateTime.Now;
response.Status = Update(model);
if (!response.Status) response.Message = SystemVariable.dataActionError;
return response;
});
}
/// <summary>
/// 读取工作中心对应的所有工位
/// </summary>
/// <returns></returns>
public dynamic LoadStationCodeByworkCenterCode(string workCenterCode, string lineCode)
{
var response = new Response();
return ExceptionsHelp.Instance.ExecuteT(() =>
{
if (string.IsNullOrEmpty(workCenterCode))
{
var data = Context.Queryable<base_work_station>().Where(x => x.lineCode == lineCode).Select(x => new
{
x.workStationCode,
x.workStationName
}).ToList();
response.Result = data;
}
else
{
var sql = @"select t1.workStationCode,t2.workStationName from (
select t1.workStationCode from base_work_center_station_rel t1
where t1.workCenterCode=@workCenterCode
)t1
left join base_work_station t2 on t1.workStationCode=t2.workStationCode";
var dt = base.Context.Ado.GetDataTable(sql, new List<SugarParameter>(){
new SugarParameter("@workCenterCode", workCenterCode)
});
if (dt.Rows.Count == 0)
{
return response.ResponseError($"请在工厂模型=>工作中心【{workCenterCode}】,对工作中心分配工位!");
}
response.Result = dt;
}
return response;
});
}
/// <summary>
/// 导出
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public dynamic ExportData(base_work_station entity)
{
return ExceptionsHelp.Instance.ExecuteT(() =>
{
var result = new Response();
var ds = GetStationList(null, entity);
if (ds == null || ds.Count == 0)
{
result.Result = "[]";
result.Count = 0;
}
else
{
result.Result = ds.Result;
result.Count = ds.Count;
}
return result;
}, catchRetrunValue: "list");
}
}
}