//对查询整个表的数据进行分页 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); } }
/// <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); } }
/// <summary> /// 查詢SN所在的工單區間 /// </summary> /// <param name="sn"></param> /// <param name="DB"></param> /// <returns></returns> public List <R_WO_REGION> ShowWORegionBySN(string sn, OleExec DB) { string sql = string.Empty; List <R_WO_REGION> WORegionList = new List <R_WO_REGION>(); DataTable dt = new DataTable(); sql = $@"Select * From R_WO_Region Where 1=1 and :strStartSN>=Min_SN and :strEndSN<=Max_SN and Length(:strStartSN)=Length(Min_SN)"; OleDbParameter[] parameter = new OleDbParameter[3]; parameter[0] = new OleDbParameter(":strStartSN", sn); parameter[1] = new OleDbParameter(":strEndSN", sn); parameter[2] = new OleDbParameter(":strStartSN", sn); dt = DB.ExecuteDataTable(sql, CommandType.Text, parameter); 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]); WORegionList.Add(ret.GetDataObject()); } } return(WORegionList); }
public override void Run() { if (SN.Value == null || SN.Value.ToString().Trim().Length <= 0) { throw new Exception("SN Can not be null"); } // string runSql = string.Format(Sqls["strGetWoSN"], WO.Value.ToString()); string runSql = "select * from r_sn where sn=:snno "; string strSn = SN.Value.ToString().Trim(); RunSqls.Add(runSql); OleExec SFCDB = DBPools["SFCDB"].Borrow(); try { OleDbParameter[] paramet = new OleDbParameter[] { new OleDbParameter(":snno", strSn) }; DataTable res = SFCDB.ExecuteDataTable(runSql, CommandType.Text, paramet); ReportTable retTab = new ReportTable(); retTab.LoadData(res, null); retTab.Tittle = "SN RMA Message"; retTab.ColNames.RemoveAt(0); Outputs.Add(retTab); DBPools["SFCDB"].Return(SFCDB); } catch (Exception ex) { DBPools["SFCDB"].Return(SFCDB); throw ex; } }
public override void Init() { string strSql = "select distinct(storage_code) from c_storage_code "; OleExec SFCDB = DBPools["SFCDB"].Borrow(); try { DataTable table = SFCDB.ExecuteDataTable(strSql, CommandType.Text); string[] StorageAssy; if (table.Rows.Count > 0) { StorageAssy = new string[table.Rows.Count + 1]; StorageAssy[0] = ""; for (int i = 0; i < table.Rows.Count; i++) { StorageAssy[i + 1] = (table.Rows[i][0] == null) ? "" : table.Rows[i][0].ToString(); } } else { throw new Exception("獲取倉碼失敗或者沒有數據"); } FromStorage.ValueForUse = StorageAssy; ToStorage.ValueForUse = StorageAssy; DBPools["SFCDB"].Return(SFCDB); } catch (Exception ex) { DBPools["SFCDB"].Return(SFCDB); throw ex; } }
public DataTable GetQueryAll(string field, string value, OleExec sfcdb) { OleDbParameter[] paras = null; StringBuilder builder = new StringBuilder("select sis.id,sis.customer_id,cust.bu,cust.customer_name,sis.series_name,sis.description "); builder.Append("from c_series sis left join c_customer cust on sis.customer_id=cust.id where 1=1 "); switch (field) { case "BU": builder.Append("and cust.bu=:Value "); paras = new OleDbParameter[] { new OleDbParameter(":Value", value) }; break; case "CustomerName": builder.Append("and cust.customer_name=:Value "); paras = new OleDbParameter[] { new OleDbParameter(":Value", value) }; break; case "SeriesName": builder.Append("and sis.series_name=:Value "); paras = new OleDbParameter[] { new OleDbParameter(":Value", value) }; break; case "ID": builder.Append("and sis.id=:Value "); paras = new OleDbParameter[] { new OleDbParameter(":Value", value) }; break; default: //builder.Append("and cust.bu=:BU "); //paras = new OleDbParameter[] { new OleDbParameter(":BU", this.BU) }; break; } return(sfcdb.ExecuteDataTable(builder.ToString(), CommandType.Text, paras)); }
/// <summary> /// 檢查SN是否在工單SN區間,存在返回TRUE,不存在返回FALSE /// 黄杨盛 2018年4月14日16:18:38 修正条码字数长度小于预配区间条码字数长度的时候会返回true的bug /// Eden 2018年4月27日16:49:38未配置工單區間返回FALSE /// </summary> /// <param name="strSN"></param> /// <param name="strWo"></param> /// <param name="DB"></param> /// <returns></returns> public bool CheckSNInWoRange(string strSN, string strWo, OleExec DB) { string sql = string.Empty; DataTable dt = new DataTable(); List <R_WO_REGION> LanguageList = new List <R_WO_REGION>(); sql = $@"SELECT * FROM R_WO_REGION WHERE WORKORDERNO='{strWo}'"; dt = DB.ExecSelect(sql, null).Tables[0]; if (dt.Rows.Count > 0) { //sql = $@"SELECT * FROM R_WO_REGION WHERE WORKORDERNO=:strwo AND :strsn BETWEEN MIN_SN AND MAX_SN AND LENGTH(:strsn)<=LENGTH(MAX_SN)"; //OleDbParameter[] paramet = new OleDbParameter[3]; //paramet[0] = new OleDbParameter(":strwo", strWo); //paramet[1] = new OleDbParameter(":strsn", strSN); //paramet[2] = new OleDbParameter(":strsn", strSN); //dt = DB.ExecuteDataTable(sql, CommandType.Text, paramet); sql = $@"SELECT * FROM R_WO_REGION WHERE WORKORDERNO='{strWo}' AND '{strSN}' BETWEEN MIN_SN AND MAX_SN AND (LENGTH('{strSN}')=LENGTH(MAX_SN) and LENGTH('{strSN}')=LENGTH(MIN_SN) )"; dt = DB.ExecuteDataTable(sql, CommandType.Text); if (dt.Rows.Count > 0) { return(true); } else { return(false); } } else { return(false); } }
/// <summary> /// 條碼后4位流水碼為34進制 /// 取得條碼StartSN&EndSN區間內包含的序號數量 /// 2018/1/24 Rain /// </summary> /// <param name="strStartSN"></param> /// <param name="strEndSN"></param> /// <param name="DB"></param> /// <returns></returns> public int GetQtyBy34HSNRange(string strStartSN, string strEndSN, OleExec DB) { string sql = string.Empty; DataTable dt = new DataTable(); sql = $@" SELECT ((case when (ascii(substr(:strEndSN,1,1)) between 48 and 57) then to_number(substr(:strEndSN,1,1)) when (ascii(substr(:strEndSN,1,1)) between 65 and 72) then ascii(substr(:strEndSN,1,1))-55 when (ascii(substr(:strEndSN,1,1)) between 74 and 78) then ascii(substr(:strEndSN,1,1))-56 when (ascii(substr(:strEndSN,1,1)) between 80 and 90) then ascii(substr(:strEndSN,1,1))-57 else 0 end)*34*34*34+ (case when (ascii(substr(:strEndSN,2,1)) between 48 and 57) then to_number(substr(:strEndSN,2,1)) when (ascii(substr(:strEndSN,2,1)) between 65 and 72) then ascii(substr(:strEndSN,2,1))-55 when (ascii(substr(:strEndSN,2,1)) between 74 and 78) then ascii(substr(:strEndSN,2,1))-56 when (ascii(substr(:strEndSN,2,1)) between 80 and 90) then ascii(substr(:strEndSN,2,1))-57 else 0 end)*34*34+ (case when (ascii(substr(:strEndSN,3,1)) between 48 and 57) then to_number(substr(:strEndSN,3,1)) when (ascii(substr(:strEndSN,3,1)) between 65 and 72) then ascii(substr(:strEndSN,3,1))-55 when (ascii(substr(:strEndSN,3,1)) between 74 and 78) then ascii(substr(:strEndSN,3,1))-56 when (ascii(substr(:strEndSN,3,1)) between 80 and 90) then ascii(substr(:strEndSN,3,1))-57 else 0 end)*34+ (case when (ascii(substr(:strEndSN,4,1)) between 48 and 57) then to_number(substr(:strEndSN,4,1)) when (ascii(substr(:strEndSN,4,1)) between 65 and 72) then ascii(substr(:strEndSN,4,1))-55 when (ascii(substr(:strEndSN,4,1)) between 74 and 78) then ascii(substr(:strEndSN,4,1))-56 when (ascii(substr(:strEndSN,4,1)) between 80 and 90) then ascii(substr(:strEndSN,4,1))-57 else 0 end)) - ((case when (ascii(substr(:strStartSN,1,1)) between 48 and 57) then to_number(substr(:strStartSN,1,1)) when (ascii(substr(:strStartSN,1,1)) between 65 and 72) then ascii(substr(:strStartSN,1,1))-55 when (ascii(substr(:strStartSN,1,1)) between 74 and 78) then ascii(substr(:strStartSN,1,1))-56 when (ascii(substr(:strStartSN,1,1)) between 80 and 90) then ascii(substr(:strStartSN,1,1))-57 else 0 end)*34*34*34+ (case when (ascii(substr(:strStartSN,2,1)) between 48 and 57) then to_number(substr(:strStartSN,2,1)) when (ascii(substr(:strStartSN,2,1)) between 65 and 72) then ascii(substr(:strStartSN,2,1))-55 when (ascii(substr(:strStartSN,2,1)) between 74 and 78) then ascii(substr(:strStartSN,2,1))-56 when (ascii(substr(:strStartSN,2,1)) between 80 and 90) then ascii(substr(:strStartSN,2,1))-57 else 0 end)*34*34+ (case when (ascii(substr(:strStartSN,3,1)) between 48 and 57) then to_number(substr(:strStartSN,3,1)) when (ascii(substr(:strStartSN,3,1)) between 65 and 72) then ascii(substr(:strStartSN,3,1))-55 when (ascii(substr(:strStartSN,3,1)) between 74 and 78) then ascii(substr(:strStartSN,3,1))-56 when (ascii(substr(:strStartSN,3,1)) between 80 and 90) then ascii(substr(:strStartSN,3,1))-57 else 0 end)*34+ (case when (ascii(substr(:strStartSN,4,1)) between 48 and 57) then to_number(substr(:strStartSN,4,1)) when (ascii(substr(:strStartSN,4,1)) between 65 and 72) then ascii(substr(:strStartSN,4,1))-55 when (ascii(substr(:strStartSN,4,1)) between 74 and 78) then ascii(substr(:strStartSN,4,1))-56 when (ascii(substr(:strStartSN,4,1)) between 80 and 90) then ascii(substr(:strStartSN,4,1))-57 else 0 end)) +1 Qty from dual"; OleDbParameter[] parameter = new OleDbParameter[64]; for (int i = 0; i <= 31; i++) { parameter[i] = new OleDbParameter(":strEndSN", strEndSN); } for (int i = 32; i <= 63; i++) { parameter[i] = new OleDbParameter(":strStartSN", strStartSN); } dt = DB.ExecuteDataTable(sql, CommandType.Text, parameter); if (dt.Rows.Count > 0) { return(Convert.ToInt32(dt.Rows[0]["Qty"].ToString())); } else { return(-1); } }
public DateTime GetDBTime(OleExec sfcdb) { DateTime DBTime = DateTime.Now; string sql = "select sysdate from dual"; DataTable table = sfcdb.ExecuteDataTable(sql, CommandType.Text); DBTime = (DateTime)table.Rows[0][0]; return(DBTime); }
public List <C_STORAGE_CODE> GetAll(OleExec DB) { string strSql = $@"select * from c_storage_code "; List <C_STORAGE_CODE> result = new List <C_STORAGE_CODE>(); DataTable res = DB.ExecuteDataTable(strSql, CommandType.Text); if (res.Rows.Count > 0) { for (int i = 0; i < res.Rows.Count; i++) { Row_C_STORAGE_CODE ret = (Row_C_STORAGE_CODE)NewRow(); ret.loadData(res.Rows[i]); result.Add(ret.GetDataObject()); } } return(result); }
public C_ACTION_CODE GetByid(string id, OleExec DB) { string strSql = $@"select * from c_action_code where id=:id"; OleDbParameter[] paramet = new OleDbParameter[] { new OleDbParameter(":id", id) }; DataTable res = DB.ExecuteDataTable(strSql, CommandType.Text, paramet); if (res.Rows.Count > 0) { Row_C_ACTION_CODE ret = (Row_C_ACTION_CODE)NewRow(); ret.loadData(res.Rows[0]); return(ret.GetDataObject()); } 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); } }
public C_ROUTE_DETAIL_RETURN GetById(string id, OleExec DB) { string strSql = $@"select * from c_route_detail_return where id=:id"; OleDbParameter[] paramet = new OleDbParameter[] { new OleDbParameter(":id", id) }; DataTable res = DB.ExecuteDataTable(strSql, CommandType.Text, paramet); if (res.Rows.Count > 0) { Row_C_ROUTE_DETAIL_RETURN ret = (Row_C_ROUTE_DETAIL_RETURN)NewRow(); ret.loadData(res.Rows[0]); return(ret.GetDataObject()); } else { return(null); } }
public List <DataRow> R_PCBA_LINK_GetBYSku(string skuno, OleExec DB) { List <DataRow> datarowlist = new List <DataRow>(); string strSql = $@"select * from mes4.r_pcba_link where skuno=:sku"; OleDbParameter[] paramet = new OleDbParameter[] { new OleDbParameter(":sku", skuno) }; DataTable res = DB.ExecuteDataTable(strSql, CommandType.Text, paramet); if (res.Rows.Count > 0) { for (int i = 0; i < res.Rows.Count; i++) { datarowlist.Add(res.Rows[i]); } } return(datarowlist); }
/// <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); } }
public List <C_STORAGE_CODE> GetByPlant(string strPlant, OleExec DB) { string strSql = $@"select * from c_storage_code where plant=:strPlant "; OleDbParameter[] paramet = new OleDbParameter[] { new OleDbParameter(":strPlant", strPlant) }; List <C_STORAGE_CODE> result = new List <C_STORAGE_CODE>(); DataTable res = DB.ExecuteDataTable(strSql, CommandType.Text, paramet); if (res.Rows.Count > 0) { for (int i = 0; i < res.Rows.Count; i++) { Row_C_STORAGE_CODE ret = (Row_C_STORAGE_CODE)NewRow(); ret.loadData(res.Rows[i]); result.Add(ret.GetDataObject()); } } return(result); }
public List <string> GetByItemName(string ItemName, OleExec DB) { string strSql = $@"select * from c_storage_item where item_name=:ItemName "; OleDbParameter[] paramet = new OleDbParameter[] { new OleDbParameter(":ItemName", ItemName) }; List <string> result = new List <string>(); DataTable res = DB.ExecuteDataTable(strSql, CommandType.Text, paramet); if (res.Rows.Count > 0) { for (int i = 0; i < res.Rows.Count; i++) { Row_C_STORAGE_ITEM ret = (Row_C_STORAGE_ITEM)NewRow(); ret.loadData(res.Rows[i]); result.Add(ret.GetDataObject().STORAGE_CODE); } } return(result); }
public List <DataRow> R_TR_SN_WIP_GetBYTR_SN(string TRSN, OleExec DB) { List <DataRow> datarowlist = new List <DataRow>(); string strSql = $@"select * from mes4.r_tr_sn_wip where tr_sn=:trsn"; OleDbParameter[] paramet = new OleDbParameter[] { new OleDbParameter(":trsn", TRSN) }; DataTable res = DB.ExecuteDataTable(strSql, CommandType.Text, paramet); if (res.Rows.Count > 0) { for (int i = 0; i < res.Rows.Count; i++) { datarowlist.Add(res.Rows[i]); } } return(datarowlist); }
public C_ERROR_CODE GetByErrorCode(string ErrorCode, OleExec DB) { //string strSql = $@"select * from c_error_code where error_code=:ErrorCode"; string strSql = $@"select * from c_error_code where error_code='{ErrorCode}'"; //OleDbParameter[] paramet = new OleDbParameter[] { new OleDbParameter(":ErrorCode", ErrorCode) }; //DataTable res = DB.ExecuteDataTable(strSql, CommandType.Text, paramet); DataTable res = DB.ExecuteDataTable(strSql, CommandType.Text, null); if (res.Rows.Count > 0) { Row_C_ERROR_CODE ret = (Row_C_ERROR_CODE)NewRow(); ret.loadData(res.Rows[0]); return(ret.GetDataObject()); } else { return(null); } }
/// <summary> /// 取到 SN 在R_MRB的數據 /// </summary> /// <param name="strStartSN"></param> /// <param name="strEndSN"></param> /// <param name="db"></param> /// <returns></returns> public List <R_MRB> GetMrbBySN(string sn, OleExec DB) { string strSql = $@" select* from r_mrb where sn=:sn and mrb_flag='1' "; OleDbParameter[] paramet = new OleDbParameter[] { new OleDbParameter(":sn", sn) }; DataTable res = DB.ExecuteDataTable(strSql, CommandType.Text, paramet); List <R_MRB> listSn = new List <R_MRB>(); if (res.Rows.Count > 0) { foreach (DataRow item in res.Rows) { Row_R_MRB ret = (Row_R_MRB)NewRow(); ret.loadData(item); listSn.Add(ret.GetDataObject()); } } return(listSn); }
public List <DataRow> C_Product_Config_GetBYSkuAndVerson(string skuno, string skuverson, OleExec DB) { List <DataRow> datarowlist = new List <DataRow>(); string strSql = $@"select * from mes1.c_product_config where p_no=:sku and p_version=:skuverson"; OleDbParameter[] paramet = new OleDbParameter[] { new OleDbParameter(":sku", skuno), new OleDbParameter(":skuverson", skuverson) }; DataTable res = DB.ExecuteDataTable(strSql, CommandType.Text, paramet); if (res.Rows.Count > 0) { for (int i = 0; i < res.Rows.Count; i++) { datarowlist.Add(res.Rows[i]); } } return(datarowlist); }
public C_SECTION_DETAIL GetById(OleExec DB, string Id) { C_SECTION_DETAIL result = new C_SECTION_DETAIL(); string sql = $@"select * from C_section where id=:id"; DataTable dt = new DataTable(); OleDbParameter[] paramet = new OleDbParameter[] { new OleDbParameter(":id", Id) }; dt = DB.ExecuteDataTable(sql, CommandType.Text, paramet); if (dt.Rows.Count > 0) { result = RowToSection(dt.Rows[0]); } else { result = null; } return(result); }
public C_STORAGE_ITEM GetById(string id, OleExec DB) { string strSql = $@"select * from c_storage_item where id=:id"; OleDbParameter[] paramet = new OleDbParameter[] { new OleDbParameter(":id", id) }; DataTable res = DB.ExecuteDataTable(strSql, CommandType.Text, paramet); if (res.Rows.Count > 0) { Row_C_STORAGE_ITEM ret = (Row_C_STORAGE_ITEM)NewRow(); ret.loadData(res.Rows[0]); return(ret.GetDataObject()); } else { return(null); } }
/// <summary> /// 通過from_Storage查詢sap_flag=0的記錄 /// </summary> /// <param name="strWo">工單號</param> /// <param name="DB"></param> /// <returns></returns> public R_MRB_GT GetByFromStorageAndSAPFlageIsZero(string fromStorage, OleExec DB) { string strSql = $@" select * from r_mrb_gt where from_storage=:wono and sap_flag='0'"; OleDbParameter[] paramet = new OleDbParameter[1]; paramet[0] = new OleDbParameter(":wono", fromStorage); DataTable table = DB.ExecuteDataTable(strSql, CommandType.Text, paramet); R_MRB_GT result = new R_MRB_GT(); if (table.Rows.Count > 0) { Row_R_MRB_GT ret = (Row_R_MRB_GT)NewRow(); ret.loadData(table.Rows[0]); result = ret.GetDataObject(); } else { result = null; } return(result); }
public List <C_ACTION_CODE> GetByFuzzySearch(string ParametValue, OleExec DB) { string strSql = $@"select * from c_action_code where upper(action_code) like'%{ParametValue}%' or upper(english_description) like'%{ParametValue}%' or upper(chinese_description) like'%{ParametValue}%'"; List <C_ACTION_CODE> result = new List <C_ACTION_CODE>(); DataTable res = DB.ExecuteDataTable(strSql, CommandType.Text); if (res.Rows.Count > 0) { for (int i = 0; i < res.Rows.Count; i++) { Row_C_ACTION_CODE ret = (Row_C_ACTION_CODE)NewRow(); ret.loadData(res.Rows[i]); result.Add(ret.GetDataObject()); } return(result); } else { return(null); } }
public List <C_ACTION_CODE> GetAllActionCode(OleExec DB) { string strSql = $@"select * from c_action_code "; List <C_ACTION_CODE> result = new List <C_ACTION_CODE>(); DataTable res = DB.ExecuteDataTable(strSql, CommandType.Text); if (res.Rows.Count > 0) { for (int i = 0; i < res.Rows.Count; i++) { Row_C_ACTION_CODE ret = (Row_C_ACTION_CODE)NewRow(); ret.loadData(res.Rows[i]); result.Add(ret.GetDataObject()); } return(result); } else { return(null); } }
public C_STORAGE_CODE GetByPlantAndStorageCode(string strPlant, string strStorageCode, OleExec DB) { string strSql = $@"select * from c_storage_code where plant=:strPlant and storage_code=:StorageCode "; OleDbParameter[] paramet = new OleDbParameter[] { new OleDbParameter(":strPlant", strPlant), new OleDbParameter(":StorageCode", strStorageCode) }; DataTable res = DB.ExecuteDataTable(strSql, CommandType.Text, paramet); if (res.Rows.Count > 0) { Row_C_STORAGE_CODE ret = (Row_C_STORAGE_CODE)NewRow(); ret.loadData(res.Rows[0]); return(ret.GetDataObject()); } else { return(null); } }
/// <summary> /// 通過工單查詢sap_flag=0的記錄 /// </summary> /// <param name="strWo">工單號</param> /// <param name="DB"></param> /// <returns></returns> public H_MRB_GT GetByWOAndSAPFlageIsZero(string strWo, OleExec DB) { string strSql = $@" select * from h_mrb_gt where workorderno=:wono and sap_flag='0'"; OleDbParameter[] paramet = new OleDbParameter[1]; paramet[0] = new OleDbParameter(":wono", strWo); DataTable table = DB.ExecuteDataTable(strSql, CommandType.Text, paramet); H_MRB_GT result = new H_MRB_GT(); if (table.Rows.Count > 0) { Row_H_MRB_GT ret = (Row_H_MRB_GT)NewRow(); ret.loadData(table.Rows[0]); result = ret.GetDataObject(); } else { result = null; } return(result); }
public C_LINE GetLineById(string Id, OleExec DB) { string sql = string.Empty; DataTable dt = new DataTable(); C_LINE resultLine = new C_LINE(); sql = $@"SELECT * FROM C_LINE WHERE Id=:id"; OleDbParameter[] paramet = new OleDbParameter[] { new OleDbParameter(":id", Id) }; dt = DB.ExecuteDataTable(sql, CommandType.Text, paramet); if (dt.Rows.Count > 0) { resultLine = CreateLanguageClass(dt.Rows[0]); } else { resultLine = null; } return(resultLine); }