public static string _genSelectBatch(string strMaterial)
{
SqlConnection oConn; NP_Cls NP = new NP_Cls();
oConn = new SqlConnection(NP.ReadFileDB(NP_Cls.PathDB));
if (oConn.State == ConnectionState.Open)
{
oConn.Close();
}
oConn.Open();
try
{
string strTmp = string.Empty;
NP_Cls.SqlSelect = "SELECT MaterialTypeName, RIGHT(YEAR(GETDATE()), 1) AS y, MONTH(GETDATE()) AS m FROM m_Material WHERE (FileStatus = N'1') AND (MaterialCode = N'" + strMaterial + "')";
DataSet tmpDs = new DataSet();
tmpDs = NP.GetClientDataSet(NP_Cls.SqlSelect);
if (tmpDs.Tables[0].Rows.Count > 0)
{
string tmpStr = string.Empty; string tmpYear = tmpDs.Tables[0].Rows[0][1].ToString();
string tmpMonth = tmpDs.Tables[0].Rows[0][2].ToString(); string tmpRunning = string.Empty;
// Find exist rows
DataSet tmpDsExist = new DataSet();
NP_Cls.SqlSelect = "SELECT sMatType, sYear, sMonth, Running FROM t_BatchRunning WHERE (MaterialTypeName = N'" + tmpDs.Tables[0].Rows[0][0].ToString().ToUpper() + "') AND (sYear = N'" + tmpYear + "') AND (sMonth = N'" + tmpMonth + "')"; tmpDsExist = NP.GetClientDataSet(NP_Cls.SqlSelect);
if (tmpDsExist.Tables[0].Rows.Count > 0)
{
tmpDsExist.Tables[0].Rows[0]["Running"] = (Convert.ToInt32(tmpDsExist.Tables[0].Rows[0]["Running"].ToString()) + 1).ToString().PadLeft(7, '0');
return(tmpDsExist.Tables[0].Rows[0]["sMatType"].ToString() + tmpYear + tmpMonth + tmpDsExist.Tables[0].Rows[0]["Running"].ToString());
}
else
{
switch (tmpDs.Tables[0].Rows[0][0].ToString().ToUpper())
{
case "RM":
tmpStr = "1";
break;
case "PK":
tmpStr = "2";
break;
case "WIP":
tmpStr = "3";
break;
case "FG":
tmpStr = "4";
break;
case "SET":
tmpStr = "5";
break;
}
if (tmpMonth.Length > 1)
{
switch (tmpMonth.ToString())
{
case "10":
tmpMonth = "A";
break;
case "11":
tmpMonth = "B";
break;
case "12":
tmpMonth = "C";
break;
}
}
return(tmpStr + tmpYear + tmpMonth + "0000001");
}
}
else
{
return(string.Empty);
}
}
catch (Exception)
{
throw;
}
finally
{
if (oConn.State == ConnectionState.Open)
{
oConn.Close();
}
}
}
public static string _genBatch(string strMaterial, SqlConnection oConnTmp, SqlTransaction TrTmp)
{
try
{
NP_Cls NP = new NP_Cls(); string strTmp = string.Empty;
NP_Cls.SqlSelect = "SELECT MaterialTypeName, RIGHT(YEAR(GETDATE()), 1) AS y, MONTH(GETDATE()) AS m FROM m_Material WHERE (FileStatus = N'1') AND (MaterialCode = N'" + strMaterial + "')";
DataSet tmpDs = new DataSet();
tmpDs = NP.GetDataWithTran(NP_Cls.SqlSelect, TrTmp, oConnTmp);
if (tmpDs.Tables[0].Rows.Count > 0)
{
string tmpStr = string.Empty;
string tmpYear = tmpDs.Tables[0].Rows[0][1].ToString();
string tmpMonth = tmpDs.Tables[0].Rows[0][2].ToString();
string tmpRunning = string.Empty;
if (tmpMonth.Length > 1)
{
switch (tmpMonth.ToString())
{
case "10":
tmpMonth = "A";
break;
case "11":
tmpMonth = "B";
break;
case "12":
tmpMonth = "C";
break;
}
}
// Find exist rows
DataSet tmpDsExist = new DataSet();
NP_Cls.SqlSelect = "SELECT sMatType, sYear, sMonth, Running FROM t_BatchRunning WHERE (MaterialTypeName = N'" + tmpDs.Tables[0].Rows[0][0].ToString().ToUpper() + "') AND (sYear = N'" + tmpYear + "') AND (sMonth = N'" + tmpMonth + "')"; tmpDsExist = NP.GetDataWithTran(NP_Cls.SqlSelect, TrTmp, oConnTmp);
if (tmpDsExist.Tables[0].Rows.Count > 0)
{
tmpDsExist.Tables[0].Rows[0]["Running"] = (Convert.ToInt32(tmpDsExist.Tables[0].Rows[0]["Running"].ToString()) + 1).ToString().PadLeft(7, '0');
strTmp = "UPDATE t_BatchRunning SET Running = '" + tmpDsExist.Tables[0].Rows[0]["Running"].ToString() + "' WHERE (MaterialTypeName = '" + tmpDs.Tables[0].Rows[0][0].ToString().ToUpper() + "') AND (sMatType = '" + tmpDsExist.Tables[0].Rows[0]["sMatType"].ToString() + "') AND (sYear = '" + tmpYear + "') AND (sMonth = '" + tmpMonth + "' )"; string strErrUp = string.Empty;
SqlCommand cmdTmp = new SqlCommand(strTmp, oConnTmp, TrTmp);
cmdTmp.ExecuteNonQuery();
return(tmpDsExist.Tables[0].Rows[0]["sMatType"].ToString() + tmpYear + tmpMonth + tmpDsExist.Tables[0].Rows[0]["Running"].ToString());
}
else
{
switch (tmpDs.Tables[0].Rows[0][0].ToString().ToUpper())
{
case "RM":
tmpStr = "1";
break;
case "PK":
tmpStr = "2";
break;
case "WIP":
tmpStr = "3";
break;
case "FG":
tmpStr = "4";
break;
case "SET":
tmpStr = "5";
break;
}
strTmp = "INSERT INTO t_BatchRunning (MaterialTypeName, sMatType, sYear, sMonth, Running) VALUES ('" + tmpDs.Tables[0].Rows[0][0].ToString().ToUpper() + "', '" + tmpStr + "', '" + tmpYear + "', '" + tmpMonth + "', '0000001')"; string strErr = string.Empty;
SqlCommand cmdTmp = new SqlCommand(strTmp, oConnTmp, TrTmp);
cmdTmp.ExecuteNonQuery();
return(tmpStr + tmpYear + tmpMonth + "0000001");
}
}
else
{
return(string.Empty);
}
}
catch (Exception)
{
throw;
}
}