/// <summary>
/// 通過機種ID獲取機種可用路由
/// </summary>
/// <param name="skuid"></param>
/// <param name="DB"></param>
/// <returns></returns>
public List <C_ROUTE> GetAvailableRoutesForSkuBySkuid(string skuid, OleExec DB)
{
string strSql = $@"select * from c_route c where (c.default_skuno is null or c.default_skuno in(" +
$@"select skuno from c_sku where id=:skuid))and c.id not in(" +
$@"select B.ROUTE_ID from c_sku a,r_sku_route b where a.id=B.SKU_ID and A.id=:skuid1)";
OleDbParameter[] paramet = new OleDbParameter[2];
paramet[0] = new OleDbParameter(":skuid", skuid);
paramet[1] = new OleDbParameter(":skuid1", skuid);
DataTable res = DB.ExecuteDataTable(strSql, CommandType.Text, paramet);
List <C_ROUTE> getC_ROUTE = new List <C_ROUTE>();
if (res.Rows.Count > 0)
{
for (int i = 0; i < res.Rows.Count; i++)
{
Row_C_ROUTE ret = (Row_C_ROUTE)NewRow();
ret.loadData(res.Rows[i]);
getC_ROUTE.Add(ret.GetDataObject());
}
return(getC_ROUTE);
}
else
{
return(null);
}
}
public List <string> GetRouteBySkuno(OleExec DB, string skuno)
{
DataTable dt = null;
Row_C_ROUTE row_route = null;
//List<C_ROUTE> routes = new List<C_ROUTE>();
List <string> routes = new List <string>();
if (DBType == DB_TYPE_ENUM.Oracle)
{
string sql = $@"select r.* from c_sku s
left join r_sku_route sr on s.id=sr.sku_id
left join c_route r on sr.route_id=r.id
where s.skuno='{skuno}' ";
dt = DB.ExecSelect(sql).Tables[0];
foreach (DataRow dr in dt.Rows)
{
//row_route = (Row_C_ROUTE) NewRow();
//row_route.loadData(dr);
//routes.Add(row_route.GetDataObject());
routes.Add(dr["route_name"].ToString());
}
return(routes);
}
else
{
throw new Exception(MESReturnMessage.GetMESReturnMessage("MES00000019", new string[] { this.DBType.ToString() }));
}
}
//add by LLF 2017-12-21 end
/// <summary>
/// 通過路由名，不區分大小寫，模糊查找，為分頁而寫，當路由名為空時查找全部
/// </summary>
/// <param name="DB"></param>
/// <param name="RouteName">路由名</param>
/// <param name="CurrentPage">當前頁（要獲取的第幾頁）</param>
/// <param name="PageSize">每頁數據的行數</param>
/// <param name="TotalData">總頁數</param>
/// <returns>找到就返回 List<C_ROUTE>，找不到就返回NULL</returns>
public List <C_ROUTE> GetByNameForPagination(OleExec DB, string RouteName, int CurrentPage, int PageSize, out int TotalData)
{
bool isGetAll = true;
OleDbParameter[] paramet;
DataTable res = new DataTable();
List <C_ROUTE> getC_ROUTE = new List <C_ROUTE>();
string strSql = $@"select count(*) from c_route a ";
if (RouteName.Length > 0)
{
strSql = strSql + $@"where upper(a.route_name) like'%{RouteName}%'";
isGetAll = false;
}
TotalData = Convert.ToInt32(DB.ExecuteScalar(strSql, CommandType.Text));
strSql = $@"select * from (select rownum rnumber,a.* from c_route a ";
if (isGetAll)
{
strSql = strSql + " order by edit_time desc) where rnumber>((:CurrentPage-1)*:PageSize) and rnumber<=((:CurrentPage1-1)*:PageSize1+:PageSize2) order by edit_time desc";
//oldb 的參數只能是按照順序對應，不能復用，
paramet = new OleDbParameter[] {
new OleDbParameter(":CurrentPage", CurrentPage),
new OleDbParameter(":PageSize", PageSize),
new OleDbParameter(":CurrentPage1", CurrentPage),
new OleDbParameter(":PageSize1", PageSize),
new OleDbParameter(":PageSize2", PageSize)
};
res = DB.ExecuteDataTable(strSql, CommandType.Text, paramet);
}
else
{
strSql = strSql + $@" where upper(a.route_name) like'%{RouteName}%' order by edit_time desc) where rnumber>((:CurrentPage-1)*:PageSize) and rnumber<=((:CurrentPage1-1)*:PageSize1+:PageSize2) order by edit_time desc";
//oldb 的參數只能是按照順序對應，不能復用，
paramet = new OleDbParameter[] {
new OleDbParameter(":CurrentPage", CurrentPage),
new OleDbParameter(":PageSize", PageSize),
new OleDbParameter(":CurrentPage1", CurrentPage),
new OleDbParameter(":PageSize1", PageSize),
new OleDbParameter(":PageSize2", PageSize)
};
res = DB.ExecuteDataTable(strSql, CommandType.Text, paramet);
}
if (res.Rows.Count > 0)
{
for (int i = 0; i < res.Rows.Count; i++)
{
Row_C_ROUTE ret = (Row_C_ROUTE)NewRow();
ret.loadData(res.Rows[i]);
getC_ROUTE.Add(ret.GetDataObject());
}
return(getC_ROUTE);
}
else
{
return(null);
}
}
/// <summary>
/// 通過路由名獲取
/// </summary>
/// <param name="RouteName">路由名</param>
/// <param name="DB"></param>
/// <returns>查找到就返回C_ROUTE對象，查找不到返回NULL</returns>
public C_ROUTE GetByRouteName(string RouteName, OleExec DB)
{
string strSql = $@"select * from c_route where route_name=:RouteName";
OleDbParameter[] paramet = new OleDbParameter[] { new OleDbParameter(":RouteName", RouteName) };
DataTable table = DB.ExecuteDataTable(strSql, CommandType.Text, paramet);
if (table.Rows.Count > 0)
{
Row_C_ROUTE ret = (Row_C_ROUTE)NewRow();
ret.loadData(table.Rows[0]);
return(ret.GetDataObject());
}
else
{
return(null);
}
}
/// <summary>
/// 通過路由ID獲取
/// </summary>
/// <param name="id">路由ID</param>
/// <param name="DB"></param>
/// <returns>找到就返回一個C_ROUTE對象，找不到就返回null</returns>
public C_ROUTE GetById(string id, OleExec DB)
{
string strSql = $@"select * from c_route where id=:id";
OleDbParameter[] paramet = new OleDbParameter[] { new OleDbParameter(":id", OleDbType.VarChar, 240) };
paramet[0].Value = id;
DataTable res = DB.ExecuteDataTable(strSql, CommandType.Text, paramet);
if (res.Rows.Count > 0)
{
Row_C_ROUTE ret = (Row_C_ROUTE)NewRow();
ret.loadData(res.Rows[0]);
return(ret.GetDataObject());
}
else
{
return(null);
}
}