//对查询整个表的数据进行分页 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); }