/// <summary>
/// 获取人员列表
/// </summary>
/// <param name="page"></param>
/// <param name="limit"></param>
/// <param name="name"></param>
/// <param name="sex"></param>
/// <returns></returns>
public KukaPage <user> Get(int page, int limit, string name, string sex)
{
using (IDbConnection conn = new SqlConnection(sqlstr))
{
conn.Open();
KukaPage <user> reModel = new KukaPage <user>();
string query = "SELECT * FROM [user] b ";
var b = conn.Query <user>(query);
var reList = b.ToList();
if (!string.IsNullOrEmpty(name))
{
reList = reList.Where(a => a.name.Contains(name)).ToList();
}
if (!string.IsNullOrEmpty(sex))
{
reList = reList.Where(a => a.sex.Contains(sex)).ToList();
}
reModel.count = reList.Count();
reModel.code = "0";
reModel.msg = "";
reModel.data = reList.Skip((page - 1) * limit).Take(limit).ToList();
return(reModel);
}
}
/// <summary>
/// 获取角色列表
/// </summary>
/// <param name="page"></param>
/// <param name="limit"></param>
/// <param name="name"></param>
/// <returns></returns>
public KukaPage <Role> GetRoleList(int page, int limit, string name)
{
using (IDbConnection conn = new SqlConnection(SQLStr.SqlStrCon))
{
KukaPage <Role> returnList = new KukaPage <Role>();
returnList.code = "0";
returnList.msg = "";
conn.Open();
StringBuilder query = new StringBuilder();
query.Append("SELECT * FROM [role] b where 1=1 ");
StringBuilder queryC = new StringBuilder();
queryC.Append("select count(*) from [role] b where 1=1 ");
if (!string.IsNullOrEmpty(name))
{
query.Append("and b.name like @name");
queryC.Append("and b.name like @name");
}
query.Append(" order by b.id OFFSET @skip ROWS FETCH NEXT @page ROWS ONLY");
var b = conn.Query <Role>(query.ToString(), new
{
skip = (page - 1) * limit,
page = limit,
name = '%' + name + '%'
});
returnList.data = b.ToList();
var count = conn.Query <int>(queryC.ToString(), new
{
name = '%' + name + '%'
});
returnList.count = count.FirstOrDefault();
return(returnList);
}
}
/// <summary>
/// 获取任务列表
/// </summary>
/// <param name="page"></param>
/// <param name="limit"></param>
/// <param name="name"></param>
/// <returns></returns>
public KukaPage <task_proc> GetAllTask(int page, int limit, string name)
{
using (IDbConnection conn = new SqlConnection(SQLStr.SqlStrCon))
{
KukaPage <task_proc> returnPage = new KukaPage <task_proc>();
StringBuilder sql = new StringBuilder();
sql.Append("select * from task_proc t where 1=1");
if (!string.IsNullOrEmpty(name))
{
sql.Append(" and t.taskname like @name");
}
sql.Append(" order by t.id desc OFFSET @skip ROWS FETCH NEXT @page ROWS ONLY");
var model = conn.Query <task_proc>(sql.ToString(), new
{
skip = (page - 1) * limit,
page = limit,
name = '%' + name + '%'
});
StringBuilder sqlCount = new StringBuilder();
sqlCount.Append("select count(*) from task_proc t where 1=1");
if (!string.IsNullOrEmpty(name))
{
sql.Append(" and t.taskname like @name");
}
int count = conn.Query <int>(sqlCount.ToString(), new
{
name = '%' + name + '%'
}).FirstOrDefault();
returnPage.code = "";
returnPage.msg = "";
returnPage.count = count;
returnPage.data = model.ToList();
return(returnPage);
}
}
public KukaPage <kuka_workshop_execute_analysi_resultModel> GetWorkshopExecuteAnalysis(kuka_workshop_execute_analysis_searchModel model)
{
using (IDbConnection conn = new SqlConnection(SQLStr.sqlStrBaseDataConn))
{
KukaPage <kuka_workshop_execute_analysi_resultModel> returnList = new KukaPage <kuka_workshop_execute_analysi_resultModel>();
returnList.code = "0";
returnList.msg = "";
conn.Open();
StringBuilder query1 = new StringBuilder();
query1.Append("SELECT * FROM [kuka_workshop_execute_analysis] b WHERE 1 = 1 ");
StringBuilder query2 = new StringBuilder();
query2.Append("SELECT COUNT(*) FROM [kuka_workshop_execute_analysis] b WHERE 1 = 1 ");
if (!string.IsNullOrEmpty(model.sales_order_no))
{
query1.AppendFormat(" AND b.[sales_order_no] = '{0}' ", model.sales_order_no);
query2.AppendFormat(" AND b.[sales_order_no] = '{0}' ", model.sales_order_no);
}
if (!string.IsNullOrEmpty(model.main_schedule_no))
{
query1.AppendFormat(" AND b.[main_schedule_no] = '{0}' ", model.main_schedule_no);
query2.AppendFormat(" AND b.[main_schedule_no] = '{0}' ", model.main_schedule_no);
}
if (!string.IsNullOrEmpty(model.factory_name))
{
query1.AppendFormat(" AND b.[factory_id] IN ({0}) ", model.factory_name);
query2.AppendFormat(" AND b.[factory_id] IN ({0}) ", model.factory_name);
}
//计划回复交期
#region - 计划回复交期 -
if (!string.IsNullOrEmpty(model.plan_reply_begin_date) && !string.IsNullOrEmpty(model.plan_reply_end_date))
{
query1.AppendFormat(" AND CONVERT(VARCHAR(100), plan_replay_time, 23) >= '{0}' AND CONVERT(VARCHAR(100), plan_replay_time, 23) <= '{1}' ", model.plan_reply_begin_date, model.plan_reply_end_date);
query2.AppendFormat(" AND CONVERT(VARCHAR(100), plan_replay_time, 23) >= '{0}' AND CONVERT(VARCHAR(100), plan_replay_time, 23) <= '{1}' ", model.plan_reply_begin_date, model.plan_reply_end_date);
}
else if (!string.IsNullOrEmpty(model.plan_reply_begin_date))
{
query1.AppendFormat(" AND CONVERT(VARCHAR(100), plan_replay_time, 23) >= '{0}' ", model.plan_reply_begin_date);
query2.AppendFormat(" AND CONVERT(VARCHAR(100), plan_replay_time, 23) >= '{0}' ", model.plan_reply_begin_date);
}
else if (!string.IsNullOrEmpty(model.plan_reply_end_date))
{
query1.AppendFormat(" AND CONVERT(VARCHAR(100), plan_replay_time, 23) <= '{0}' ", model.plan_reply_end_date);
query2.AppendFormat(" AND CONVERT(VARCHAR(100), plan_replay_time, 23) <= '{0}' ", model.plan_reply_end_date);
}
#endregion
//计划装箱日期
#region - 计划装箱日期 -
if (!string.IsNullOrEmpty(model.packing_begin_time) && !string.IsNullOrEmpty(model.packing_end_time))
{
query1.AppendFormat(" AND CONVERT(VARCHAR(100), packing_time, 23) >= '{0}' AND CONVERT(VARCHAR(100), packing_time, 23) <= '{1}' ", model.packing_begin_time, model.packing_end_time);
query2.AppendFormat(" AND CONVERT(VARCHAR(100), packing_time, 23) >= '{0}' AND CONVERT(VARCHAR(100), packing_time, 23) <= '{1}' ", model.packing_begin_time, model.packing_end_time);
}
else if (!string.IsNullOrEmpty(model.packing_begin_time))
{
query1.AppendFormat(" AND CONVERT(VARCHAR(100), packing_time, 23) >= '{0}' ", model.packing_begin_time);
query2.AppendFormat(" AND CONVERT(VARCHAR(100), packing_time, 23) >= '{0}' ", model.packing_begin_time);
}
else if (!string.IsNullOrEmpty(model.packing_end_time))
{
query1.AppendFormat(" AND CONVERT(VARCHAR(100), packing_time, 23) <= '{0}' ", model.packing_end_time);
query2.AppendFormat(" AND CONVERT(VARCHAR(100), packing_time, 23) <= '{0}' ", model.packing_end_time);
}
#endregion
//U9入库时间
#region - U9入库时间 -
if (!string.IsNullOrEmpty(model.storage_begin_date) && !string.IsNullOrEmpty(model.storage_end_date))
{
query1.AppendFormat(" AND CONVERT(VARCHAR(100), actual_item_u9_storage_time, 23) >= '{0}' AND CONVERT(VARCHAR(100), actual_item_u9_storage_time, 23) <= '{1}' ", model.storage_begin_date, model.storage_end_date);
query2.AppendFormat(" AND CONVERT(VARCHAR(100), actual_item_u9_storage_time, 23) >= '{0}' AND CONVERT(VARCHAR(100), actual_item_u9_storage_time, 23) <= '{1}' ", model.storage_begin_date, model.storage_end_date);
}
else if (!string.IsNullOrEmpty(model.storage_begin_date))
{
query1.AppendFormat(" AND CONVERT(VARCHAR(100), actual_item_u9_storage_time, 23) >= '{0}' ", model.storage_begin_date);
query2.AppendFormat(" AND CONVERT(VARCHAR(100), actual_item_u9_storage_time, 23) >= '{0}' ", model.storage_begin_date);
}
else if (!string.IsNullOrEmpty(model.storage_end_date))
{
query1.AppendFormat(" AND CONVERT(VARCHAR(100), actual_item_u9_storage_time, 23) <= '{0}' ", model.storage_end_date);
query2.AppendFormat(" AND CONVERT(VARCHAR(100), actual_item_u9_storage_time, 23) <= '{0}' ", model.storage_end_date);
}
#endregion
//打包完工时间
#region - 打包完工时间 -
if (!string.IsNullOrEmpty(model.package_begin_date) && !string.IsNullOrEmpty(model.package_end_date))
{
query1.AppendFormat(" AND CONVERT(VARCHAR(100), pack_working_procedure_scan_date, 23) >= '{0}' AND CONVERT(VARCHAR(100), pack_working_procedure_scan_date, 23) <= '{1}' ", model.package_begin_date, model.package_end_date);
query2.AppendFormat(" AND CONVERT(VARCHAR(100), pack_working_procedure_scan_date, 23) >= '{0}' AND CONVERT(VARCHAR(100), pack_working_procedure_scan_date, 23) <= '{1}' ", model.package_begin_date, model.package_end_date);
}
else if (!string.IsNullOrEmpty(model.package_begin_date))
{
query1.AppendFormat(" AND CONVERT(VARCHAR(100), pack_working_procedure_scan_date, 23) >= '{0}' ", model.package_begin_date);
query2.AppendFormat(" AND CONVERT(VARCHAR(100), pack_working_procedure_scan_date, 23) >= '{0}' ", model.package_begin_date);
}
else if (!string.IsNullOrEmpty(model.package_end_date))
{
query1.AppendFormat(" AND CONVERT(VARCHAR(100), pack_working_procedure_scan_date, 23) <= '{0}' ", model.package_end_date);
query2.AppendFormat(" AND CONVERT(VARCHAR(100), pack_working_procedure_scan_date, 23) <= '{0}' ", model.package_end_date);
}
#endregion
//包制完成时间
#region - 包制完成时间 -
if (!string.IsNullOrEmpty(model.scan_begin_date) && !string.IsNullOrEmpty(model.scan_end_date))
{
query1.AppendFormat(" AND CONVERT(VARCHAR(100), sofa_working_procedure_scan_date, 23) >= '{0}' AND CONVERT(VARCHAR(100), sofa_working_procedure_scan_date, 23) <= '{1}' ", model.scan_begin_date, model.scan_end_date);
query2.AppendFormat(" AND CONVERT(VARCHAR(100), sofa_working_procedure_scan_date, 23) >= '{0}' AND CONVERT(VARCHAR(100), sofa_working_procedure_scan_date, 23) <= '{1}' ", model.scan_begin_date, model.scan_end_date);
}
else if (!string.IsNullOrEmpty(model.scan_begin_date))
{
query1.AppendFormat(" AND CONVERT(VARCHAR(100), sofa_working_procedure_scan_date, 23) >= '{0}' ", model.scan_begin_date);
query2.AppendFormat(" AND CONVERT(VARCHAR(100), sofa_working_procedure_scan_date, 23) >= '{0}' ", model.scan_begin_date);
}
else if (!string.IsNullOrEmpty(model.scan_end_date))
{
query1.AppendFormat(" AND CONVERT(VARCHAR(100), sofa_working_procedure_scan_date, 23) <= '{1}' ", model.scan_end_date);
query2.AppendFormat(" AND CONVERT(VARCHAR(100), sofa_working_procedure_scan_date, 23) <= '{1}' ", model.scan_end_date);
}
#endregion
//U9未入库
if (!string.IsNullOrEmpty(model.is_not_in_storage))
{
switch (model.is_not_in_storage)
{
case "是":
query1.AppendFormat(" AND actual_item_u9_storage_time IS NULL ");
query2.AppendFormat(" AND actual_item_u9_storage_time IS NULL ");
break;
case "否":
query1.AppendFormat(" AND actual_item_u9_storage_time IS NOT NULL ");
query2.AppendFormat(" AND actual_item_u9_storage_time IS NOT NULL ");
break;
default:
break;
}
}
query1.Append(" ORDER BY b.[sales_order_no] OFFSET @skip ROWS FETCH NEXT @page ROWS ONLY ");
var b = conn.Query <kuka_workshop_execute_analysi_resultModel>(query1.ToString(), new
{
skip = (model.page - 1) * model.limit,
page = model.limit
});
var count = conn.Query <int>(query2.ToString(), new
{
});
returnList.data = b.ToList();
returnList.count = count.FirstOrDefault();
return(returnList);
}
}
