public Boolean reverseBOM(bomheader bomh)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update BOMHeader set DocumentStatus=" + bomh.DocumentStatus +
", forwardUser='******'" +
", commentStatus='" + bomh.CommentStatus + "'" +
", ForwarderList='" + bomh.ForwarderList + "'" +
" where ProductID='" + bomh.ProductID + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "BOMHeader", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
status = false;
}
return(status);
}
public Boolean ApproveBOM(bomheader bomh)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update BOMHeader set DocumentStatus=99, status=1 " +
", ApproveUser='******'" +
", commentStatus='" + bomh.CommentStatus + "'" +
", ReworkDate = convert(date, getdate())" +
" where ProductID='" + bomh.ProductID + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "BOMHeader", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
status = false;
}
return(status);
}
public Boolean validateBOMHeader(bomheader bh)
{
Boolean status = true;
try
{
if (bh.ProductID.Trim().Length == 0 || bh.ProductID == null)
{
return(false);
}
if (bh.Details.Trim().Length == 0 || bh.Details == null)
{
return(false);
}
if (bh.Cost <= 0)
{
return(false);
}
}
catch (Exception ex)
{
MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
return(false);
}
return(status);
}
public Boolean InsertBOMHeaderAndDetail(bomheader bh, List <bomdetail> BOMDEtails)
{
Boolean status = true;
string utString = "";
string updateSQL = "";
try
{
updateSQL = "insert into BOMHeader (ProductID,Details,Cost,Status," +
" DocumentStatus,CreateUser,CreateTime,ForwarderList,Comments,CommentStatus)" +
"values (" +
"'" + bh.ProductID + "'," +
"'" + bh.Details + "'," +
bh.Cost + "," +
bh.status + "," +
bh.DocumentStatus + "," +
"'" + Login.userLoggedIn + "'," +
"GETDATE()" + "," +
"'" + bh.ForwarderList + "'," +
"'" + bh.Comments + "'," +
"'" + bh.CommentStatus + "'" + ")";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "BOMHeader", "", updateSQL) +
Main.QueryDelimiter;
updateSQL = "Delete from BOMDetail where ProductID='" + bh.ProductID + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("delete", "BOMDetail", "", updateSQL) +
Main.QueryDelimiter;
foreach (bomdetail bd in BOMDEtails)
{
updateSQL = "insert into BOMDetail " +
"(ProductID,StockItemID,Quantity,PurchasePrice,CustomPrice) " +
"values (" +
"'" + bd.ProductID + "'," +
"'" + bd.StockItemID + "'," +
bd.Quantity + "," +
bd.PurchasePrice + "," +
bd.CustomPrice + ")";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "BOMDetail", "", updateSQL) +
Main.QueryDelimiter;
}
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception ex)
{
status = false;
MessageBox.Show("Transaction Exception Occured");
}
return(status);
}
////////ActivityLogDB alDB = new ActivityLogDB();
public List <bomheader> getBOMHeader(string userList, int opt, string userCommentStatusString)
{
bomheader bh;
List <bomheader> BOMHeaders = new List <bomheader>();
try
{
//approved user comment status string
string acStr = "";
try
{
acStr = userCommentStatusString.Substring(0, userCommentStatusString.Length - 2) + "1" + Main.delimiter2;
}
catch (Exception ex)
{
acStr = "";
}
//-----
SqlConnection conn = new SqlConnection(Login.connString);
string query1 = "select a.ProductID, b.Name,a.Details,a.ReworkDate,a.Cost,a.status, " +
"a.DocumentStatus,a.CreateUser,a.CreateTime,a.ForwardUser,a.ApproveUser,a.CommentStatus,a.ForwarderList " +
"from BOMHeader a, StockItem b where a.ProductID=b.StockItemID" +
" and ((a.forwarduser='******' and a.DocumentStatus between 2 and 98) " +
" or (a.createuser='******' and a.DocumentStatus=1)" +
" or (a.commentStatus like '%" + userCommentStatusString + "%' and a.DocumentStatus between 1 and 98)) order by a.ProductID";
string query2 = "select a.ProductID, b.Name,a.Details,a.ReworkDate,a.Cost,a.status, " +
"a.DocumentStatus,a.CreateUser,a.CreateTime,a.ForwardUser,a.ApproveUser,a.CommentStatus,a.ForwarderList " +
"from BOMHeader a, StockItem b where a.ProductID=b.StockItemID" +
" and ((a.createuser='******' and a.DocumentStatus between 2 and 98 ) " +
" or (a.ForwarderList like '%" + userList + "%' and a.DocumentStatus between 2 and 98 and a.ForwardUser <> '" + Login.userLoggedIn + "')" +
" or (a.commentStatus like '%" + acStr + "%' and a.DocumentStatus between 1 and 98)) order by a.ProductID ";
string query3 = "select a.ProductID, b.Name,a.Details,a.ReworkDate,a.Cost,a.status, " +
"a.DocumentStatus,a.CreateUser,a.CreateTime,a.ForwardUser,a.ApproveUser,a.CommentStatus,a.ForwarderList " +
"from BOMHeader a, StockItem b where a.ProductID=b.StockItemID" +
" and ((a.createuser='******'" +
" or a.ForwarderList like '%" + userList + "%'" +
" or a.commentStatus like '%" + acStr + "%'" +
" or a.approveUser='******')" +
" and a.DocumentStatus = 99) order by a.ProductID";
string query6 = "select a.ProductID, b.Name,a.Details,a.ReworkDate,a.Cost,a.status, " +
"a.DocumentStatus,a.CreateUser,a.CreateTime,a.ForwardUser,a.ApproveUser,a.CommentStatus,a.ForwarderList " +
"from BOMHeader a, StockItem b where a.ProductID=b.StockItemID" +
" and a.DocumentStatus = 99 order by a.ProductID";
string query = "";
switch (opt)
{
case 1:
query = query1;
break;
case 2:
query = query2;
break;
case 3:
query = query3;
break;
case 6:
query = query6;
break;
default:
query = "";
break;
}
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
bh = new bomheader();
bh.ProductID = reader.GetString(0);
bh.Name = reader.GetString(1);
bh.Details = reader.GetString(2);
//bh.ReworkDate = reader.GetDateTime(3);
bh.Cost = reader.GetDouble(4);
bh.status = reader.GetInt32(5);
bh.DocumentStatus = reader.GetInt32(6);
bh.CreateUser = reader.GetString(7);
bh.CreateTime = reader.GetDateTime(8);
bh.ForwardUser = !(reader.IsDBNull(9))? reader.GetString(9): "";
bh.ApproveUser = !(reader.IsDBNull(10)) ? reader.GetString(10) : "";
bh.CommentStatus = !(reader.IsDBNull(11)) ? reader.GetString(11) : "";
bh.ForwarderList = !(reader.IsDBNull(12)) ? reader.GetString(12) : "";
BOMHeaders.Add(bh);
}
conn.Close();
}
catch (Exception)
{
MessageBox.Show(this.ToString() + "-" + System.Reflection.MethodBase.GetCurrentMethod().Name + "() : Error");
}
return(BOMHeaders);
}