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