//对查询整个表的数据进行分页
public List <R_WO_REGION> ShowAllDataAndShowPage(OleExec DB, string strWorkOrder, int CurrentPage, int PageSize, out int TotalData)
{
string strSql = string.Empty;
bool isGetAll = true;
DataTable dt = new DataTable();
OleDbParameter[] paramet;
List <R_WO_REGION> LanguageList = new List <R_WO_REGION>();
strSql = $@" select count(*) from r_wo_region a ";
if (strWorkOrder.Length > 0)
{
strSql = strSql + $@"where upper(a.workorderno) like'%{strWorkOrder}%'";
isGetAll = false;
}
TotalData = Convert.ToInt32(DB.ExecuteScalar(strSql, CommandType.Text));
strSql = $@"select * from (select rownum rnumber,a.* from r_wo_region 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)
};
dt = DB.ExecuteDataTable(strSql, CommandType.Text, paramet);
}
else
{
strSql = strSql + $@" where upper(a.workorderno) like'%{strWorkOrder}%' 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)
};
dt = DB.ExecuteDataTable(strSql, CommandType.Text, paramet);
}
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Row_R_WO_REGION ret = (Row_R_WO_REGION)NewRow();
ret.loadData(dt.Rows[i]);
LanguageList.Add(ret.GetDataObject());
}
return(LanguageList);
}
else
{
return(null);
}
}
//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);
}
}
public int GetCountByDirectlinkId(string directlinkId, OleExec DB)
{
string strSql = $@"select count(*) from c_route_detail_directlink where directlink_route_detail_id=:directlink_route_detail_id";
OleDbParameter[] paramet = new OleDbParameter[] { new OleDbParameter(":directlink_route_detail_id", directlinkId) };
int res = Convert.ToInt32(DB.ExecuteScalar(strSql, CommandType.Text, paramet));
return(res);
}
public int GetCountByReturn_Route_Detail_Id(string return_route_detailid, OleExec DB)
{
string strSql = $@"select count(*) from c_route_detail_return where return_route_detail_id=:return_route_detail_id";
OleDbParameter[] paramet = new OleDbParameter[] { new OleDbParameter(":return_route_detail_id", return_route_detailid) };
int res = Convert.ToInt32(DB.ExecuteScalar(strSql, CommandType.Text, paramet));
return(res);
}
public int GetCountByWOAndStationNotContailFail(string strWo, string stationname, OleExec DB)
{
string strSql = $@"select count(distinct m.sn) from r_sn_station_detail m where workorderno=:strWo and station_name=:stationname and REPAIR_FAILED_FLAG<>1";
OleDbParameter[] paramet = new OleDbParameter[2];
paramet[0] = new OleDbParameter(":strWo", strWo);
paramet[1] = new OleDbParameter(":stationname", stationname);
int result = Convert.ToInt32(DB.ExecuteScalar(strSql, CommandType.Text, paramet));
return(result);
}
string GetNextId(OleExec sfcdb)
{
string sql = $@"select max(substr(id, 4, 8))+1 max_id from c_series ";
string nextNum = sfcdb.ExecuteScalar(sql, CommandType.Text, null);
//int len = nextNum.Length;
while (nextNum.Length < 6)
{
nextNum = "0" + nextNum;
//len++;
}
return(this.BU + nextNum);
}
/// <summary>
/// 獲取工單在某個工站過站數量
/// </summary>
/// <param name="strWo"></param>
/// <param name="stationname"></param>
/// <param name="DB"></param>
/// <returns></returns>
public int GetCountByWOAndStation(string strWo, string stationname, OleExec DB)
{
string strSql = $@"select count(distinct m.sn) from r_sn_station_detail m where workorderno=:strWo and station_name=:stationname";
OleDbParameter[] paramet = new OleDbParameter[2];
paramet[0] = new OleDbParameter(":strWo", OleDbType.VarChar, 100);
paramet[0].Value = strWo;
paramet[1] = new OleDbParameter(":stationname", OleDbType.VarChar, 100);
paramet[1].Value = stationname;
int result = Convert.ToInt32(DB.ExecuteScalar(strSql, CommandType.Text, paramet));
return(result);
}
/// <summary>
/// 獲取新的默認路由名稱
/// </summary>
/// <param name="DB"></param>
/// <returns></returns>
public string GetNewRouteName(OleExec DB)
{
string strSql = $@"select :NewRoute||(max(route_name)+1) as NewRouteName from(" +
$@"select to_number(substr(route_name,9)) as route_name from c_route where regexp_like(route_name,:Regex))";
OleDbParameter[] paramet = new OleDbParameter[2];
paramet[0] = new OleDbParameter(":NewRoute", "NewRoute");
paramet[1] = new OleDbParameter(":Regex", "^NewRoute([0-9]+)$");
string res = DB.ExecuteScalar(strSql, CommandType.Text, paramet);
if (res == null || res.Length <= 0)
{
res = "NewRoute0";
}
return(res);
}
