|
|
using Dapper;
|
|
|
using Estsh.Core.Dapper;
|
|
|
using Estsh.Core.IRepositories;
|
|
|
using Estsh.Core.Model.Result;
|
|
|
using Estsh.Core.Models;
|
|
|
using Estsh.Core.Repository.IRepositories;
|
|
|
using System.Collections;
|
|
|
using System.Data;
|
|
|
using System.Text;
|
|
|
|
|
|
/***************************************************************************************************
|
|
|
*
|
|
|
* 更新人：sitong.dong
|
|
|
* 描述：发运通道维护
|
|
|
* 修改时间：2022.06.22
|
|
|
* 修改日志：系统迭代升级
|
|
|
*
|
|
|
**************************************************************************************************/
|
|
|
namespace Estsh.Core.Repositories
|
|
|
{
|
|
|
public class RollDefineRepository : BaseRepository<GRoll>, IRollDefineRepository
|
|
|
{
|
|
|
public RollDefineRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
|
|
|
{
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 根据查询条件得出结果集
|
|
|
/// </summary>
|
|
|
/// <param name="awhere"></param>
|
|
|
/// <param name="dt"></param>
|
|
|
/// <returns></returns>
|
|
|
//public List<GRoll> GetQuery(string awhere, ref DataTable dt)
|
|
|
public List<GRoll> GetQuery(string awhere)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder sqlStringBuilder = new StringBuilder();
|
|
|
sqlStringBuilder.Append(" SELECT roll_no,roll_name,roll_type,tray_type,order_seq,enabled FROM dbo.g_roll ");
|
|
|
|
|
|
if (!string.IsNullOrEmpty(awhere))
|
|
|
{
|
|
|
sqlStringBuilder.Append(awhere);
|
|
|
}
|
|
|
List<GRoll> result = dbConn.Query<GRoll>(sqlStringBuilder.ToString()).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获得菜单列表数据
|
|
|
/// </summary>
|
|
|
public List<GRoll> getList(string strWhere, string filedOrder)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder strSql = new StringBuilder();
|
|
|
strSql.Append(" select a.*,b.model_name,b.model_desc,(CASE a.roll_type WHEN '0' THEN '库存道' WHEN '1' THEN '排序道' ELSE '未知' END) AS as_roll_type,(CASE tray_type WHEN 'F' THEN '前排托盘' WHEN 'R' THEN '后排托盘' WHEN 'A' THEN '前后排托盘' ELSE '未知' END) AS as_tray_type,type_id from g_roll AS a ");
|
|
|
strSql.Append(" LEFT JOIN dbo.sys_model AS b ON a.model_id=b.model_id ");
|
|
|
strSql.Append(" LEFT JOIN dbo.sys_model_type AS c ON b.model_type_id=c.type_id ");
|
|
|
if (!strWhere.Trim().Equals(""))
|
|
|
{
|
|
|
strSql.Append(" where " + strWhere);
|
|
|
}
|
|
|
if (filedOrder != null && !filedOrder.Trim().Equals(""))
|
|
|
{
|
|
|
strSql.Append(" order by " + filedOrder);
|
|
|
}
|
|
|
|
|
|
List<GRoll> result = dbConn.Query<GRoll>(strSql.ToString()).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取辊道编号
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public List<KeyValueResult> GetRollNO()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder sqlStringBuilder = new StringBuilder(1024);
|
|
|
|
|
|
sqlStringBuilder.Append(" select roll_no as [value],roll_name as [key] from g_roll ");
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sqlStringBuilder.ToString()).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取辊道名称
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public List<KeyValueResult> GetRollName()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder sqlStringBuilder = new StringBuilder(1024);
|
|
|
sqlStringBuilder.Append(" select roll_no as [value],roll_name as [key] from g_roll");
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sqlStringBuilder.ToString()).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
|
|
|
/// <summary>
|
|
|
/// 删除数据
|
|
|
/// </summary>
|
|
|
/// <param name="roll_no"></param>
|
|
|
/// <returns></returns>
|
|
|
public int deleteRoll(String ruid)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
DynamicParameters htparams = new DynamicParameters();
|
|
|
htparams.Add("@ruid", ruid);
|
|
|
String delStr = " update g_roll set Enabled='N' where ruid = @ruid";
|
|
|
int result = dbConn.Execute(delStr, htparams);
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
//启用
|
|
|
public int EnableData(String ids)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
String delStr = "update g_roll set Enabled='Y' WHERE ruid in (" + ids + ")";
|
|
|
int result = dbConn.Execute(delStr);
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
//禁用
|
|
|
public int DisableData(String ids)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
String delStr = "update g_roll set Enabled='N' WHERE ruid in (" + ids + ")";
|
|
|
int result = dbConn.Execute(delStr);
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 插入数据
|
|
|
/// </summary>
|
|
|
/// <param name="htParams"></param>
|
|
|
/// <returns></returns>
|
|
|
public int insertRoll(GRoll htParams)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder sqlStringBuilder = new StringBuilder(1024);
|
|
|
sqlStringBuilder.Append("INSERT INTO dbo.g_roll");
|
|
|
sqlStringBuilder.Append("(roll_no ,");
|
|
|
sqlStringBuilder.Append("roll_name ,");
|
|
|
sqlStringBuilder.Append("roll_type ,");
|
|
|
sqlStringBuilder.Append("max_qty ,");
|
|
|
sqlStringBuilder.Append("min_qty ,");
|
|
|
sqlStringBuilder.Append("qty ,");
|
|
|
sqlStringBuilder.Append("cust_pdline_id,");
|
|
|
sqlStringBuilder.Append(", create_userid ");
|
|
|
sqlStringBuilder.Append(", create_time ");
|
|
|
sqlStringBuilder.Append("model_id");
|
|
|
sqlStringBuilder.Append("enabled");
|
|
|
sqlStringBuilder.Append(")");
|
|
|
sqlStringBuilder.Append("VALUES ( @rollNo ,");
|
|
|
sqlStringBuilder.Append("@rollName ,");
|
|
|
sqlStringBuilder.Append("@rollType ,");
|
|
|
sqlStringBuilder.Append("@maxQty ,");
|
|
|
sqlStringBuilder.Append("@minQty ,");
|
|
|
sqlStringBuilder.Append("@qty ,");
|
|
|
sqlStringBuilder.Append("@custPdlineId,");
|
|
|
sqlStringBuilder.Append(", @createUserid ");
|
|
|
sqlStringBuilder.Append(", CONVERT(varchar(50), GETDATE(), 21)");
|
|
|
sqlStringBuilder.Append("@modelId");
|
|
|
sqlStringBuilder.Append("'Y'");
|
|
|
sqlStringBuilder.Append(" ) ");
|
|
|
|
|
|
int result = dbConn.Execute(sqlStringBuilder.ToString(), htParams);
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 修改数据
|
|
|
/// </summary>
|
|
|
/// <param name="htParams"></param>
|
|
|
/// <returns></returns>
|
|
|
public int updateRoll(GRoll htParams)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("UPDATE g_roll ");
|
|
|
SqlStringBuilder.Append("SET roll_no = @rollNo , ");
|
|
|
SqlStringBuilder.Append(" roll_name = @rollName , ");
|
|
|
SqlStringBuilder.Append(" roll_type = @rollType , ");
|
|
|
SqlStringBuilder.Append(" max_qty = @maxQty , ");
|
|
|
SqlStringBuilder.Append(" min_qty = @minQty , ");
|
|
|
SqlStringBuilder.Append(" qty = @qty , ");
|
|
|
SqlStringBuilder.Append(" model_id = @modelId , ");
|
|
|
SqlStringBuilder.Append(" cust_pdline_id=@custPdlineId, ");
|
|
|
SqlStringBuilder.Append(" update_userid = @updateUserId ,");
|
|
|
SqlStringBuilder.Append(" update_time = CONVERT(varchar(50), GETDATE(), 21) ");
|
|
|
SqlStringBuilder.Append("WHERE ruid = @ruid ");
|
|
|
int result = dbConn.Execute(SqlStringBuilder.ToString(), htParams);
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 根据分页条件获取分页数据列表
|
|
|
/// </summary>
|
|
|
public Hashtable getListByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
Hashtable result = new Hashtable();
|
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
|
|
|
|
parameters.Add( "@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
parameters.Add( "@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
parameters.Add( "@Table", " dbo.g_roll AS a LEFT JOIN dbo.sys_model AS b ON a.model_id=b.model_id ");
|
|
|
parameters.Add( "@Column", " a.*,b.model_name,b.model_desc,(CASE a.roll_type WHEN '0' THEN '库存道' WHEN '1' THEN '排序道' ELSE '未知' END) AS as_roll_type,(CASE tray_type WHEN 'F' THEN '前排托盘' WHEN 'R' THEN '后排托盘' WHEN 'A' THEN '前后排托盘' ELSE '未知' END) AS as_tray_type ");
|
|
|
parameters.Add( "@OrderColumn", OrderBy);
|
|
|
parameters.Add( "@GroupColumn", "");
|
|
|
parameters.Add( "@PageSize", PageSize);
|
|
|
parameters.Add( "@CurrentPage", PageIndex);
|
|
|
parameters.Add( "@Group", 0);
|
|
|
parameters.Add( "@Condition", strWhere);
|
|
|
|
|
|
List<GRoll> defects = dbConn.Query<GRoll>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
|
|
|
result.Add("dataList", defects);
|
|
|
result.Add("totalCount", parameters.Get<int>("@TotalCount"));
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取客户产线列表
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public List<KeyValueResult> GetCustPDLine()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("SELECT cust_pdline_id as [value], ");
|
|
|
SqlStringBuilder.Append("cust_pdline_name + ' : ' + cust_pdline_desc as [key] ");
|
|
|
SqlStringBuilder.Append("FROM dbo.sys_cust_pdline ");
|
|
|
SqlStringBuilder.Append("WHERE enabled = 'Y' ");
|
|
|
SqlStringBuilder.Append("ORDER BY cust_pdline_name ");
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlStringBuilder.ToString()).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 根据客户产线查询车型
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public List<KeyValueResult> GetMode_type(string CustPDLine)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = "select type_id as [value], type_name as [key] from dbo.sys_model_type WHERE enabled='Y' and cust_pdline_id= (select cust_pdline_id from sys_cust_pdline where cust_pdline_id='" + CustPDLine + "')";
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 根据车型编号查询座椅配置
|
|
|
/// </summary>
|
|
|
/// <param name="modelTypeID">车型编号</param>
|
|
|
/// <returns>配置</returns>
|
|
|
public List<KeyValueResult> GetModelList(int ModelType)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string SqlString = "SELECT model_id as [value],model_name as [key] FROM dbo.sys_model WHERE enabled='Y' and model_type_id=@type_id order by model_name";
|
|
|
DynamicParameters Params = new DynamicParameters(1);
|
|
|
Params.Add("@type_id", ModelType);
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlString, Params).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
|
|
|
/// <summary>
|
|
|
/// 查询重复信息
|
|
|
/// </summary>
|
|
|
/// <param name="strWhere"></param>
|
|
|
/// <returns></returns>
|
|
|
public List<GRoll> getRollNoList(string strWhere)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("SELECT roll_no,roll_name,roll_type,alarm_addr,model_id,tray_type,order_seq,layer,enabled FROM dbo.g_roll where 0=0 " + strWhere);
|
|
|
List<GRoll> result = dbConn.Query<GRoll>(SqlStringBuilder.ToString()).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取客户产线编号
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public List<KeyValueResult> GetCustID()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder strSql = new StringBuilder();
|
|
|
strSql.Append("SELECT DISTINCT a.cust_pdline_id as [value],b.cust_pdline_name as [key] FROM dbo.g_roll a LEFT JOIN dbo.sys_cust_pdline b ON a.cust_pdline_id=b.cust_pdline_id WHERE a.enabled='Y' ");
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
}
|