public static string getUnitForSelectedStockItem(string ItemID, string ItemName)
{
stockitem sitem = new stockitem();
try
{
SqlConnection conn = new SqlConnection(Login.connString);
string query = "select StockItemID, Name,Unit" +
" from ViewStockItem where StockItemID = '" + ItemID + "' and Name = '" + ItemName + "'";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
try
{
if (reader.Read())
{
sitem.StockItemID = reader.GetString(0);
sitem.Name = reader.GetString(1);
sitem.Unit = reader.GetString(2);
}
}
catch (Exception ex)
{
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error querying Stock Item Data");
}
return(sitem.Unit);
}
public Boolean ApproveStockItem(stockitem prevsitem, string id)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update StockItem set StockItemID = '" + id +
"', documentstatus=99" +
", status=1" +
", ApproveUser='******'" +
" where StockItemID='" + prevsitem.StockItemID + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("approve", "StockItem", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public Boolean reverseStockITem(stockitem sitem)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update StockItem set DocumentStatus=" + sitem.documentStatus +
", forwardUser='******'" +
", ForwarderList='" + sitem.ForwarderList + "'" +
" where StockItemID='" + sitem.StockItemID + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "StockItem", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public Boolean updateStockItem(stockitem sitem, stockitem prevsitem)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update StockItem set " +
"Name='" + sitem.Name +
"', Level1Gcode='" + sitem.Group1Code +
"', Level2Gcode='" + sitem.Group2Code +
"', Level3Gcode='" + sitem.Group3Code +
"', Level4Gcode='" + sitem.Group4Code +
"', Level5Gcode='" + sitem.Group5Code +
"', Level6Gcode='" + sitem.Group6Code +
"', Unit='" + sitem.Unit +
"', ReorderLevel=" + sitem.ReorderLevel +
", ForwarderList='" + sitem.ForwarderList + "'" +
" where StockItemID='" + prevsitem.StockItemID + "'";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("update", "StockItem", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public Boolean validateStockItem(stockitem sitem)
{
Boolean status = true;
string utString = "";
try
{
if (sitem.StockItemID.Trim().Length == 0 || sitem.StockItemID == null)
{
return(false);
}
if (sitem.Name.Trim().Length == 0 || sitem.Name == null)
{
return(false);
}
if (sitem.Group1CodeDescription.Trim().Length == 0 || sitem.Group1CodeDescription == null)
{
return(false);
}
//if (sitem.Group2CodeDescription.Trim().Length == 0 || sitem.Group2CodeDescription == null)
//{
// return false;
//}
//if (sitem.Group3CodeDescription.Trim().Length == 0 || sitem.Group3CodeDescription == null)
//{
// return false;
//}
if (sitem.Unit.Trim().Length == 0 || sitem.Unit == null)
{
return(false);
}
if (sitem.ReorderLevel == 0)
{
return(false);
}
}
catch (Exception)
{
}
return(status);
}
public Boolean insertStockItem(stockitem sitem)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "insert into StockItem " +
" (StockItemID,Name,Reorderlevel,Level1GCode,Level2GCode,Level3GCode," +
"Level4GCode,Level5GCode,Level6GCode,Unit,Status,DocumentStatus,CreateTime,CreateUser,ForwarderList)" +
"values (" +
"IDENT_CURRENT('StockItem')" +
",'" + sitem.Name + "'," +
sitem.ReorderLevel + "," +
"'" + sitem.Group1Code + "'," +
"'" + sitem.Group2Code + "'," +
"'" + sitem.Group3Code + "'," +
"'" + sitem.Group4Code + "'," +
"'" + sitem.Group5Code + "'," +
"'" + sitem.Group6Code + "'," +
"'" + sitem.Unit + "'," +
sitem.status + "," +
sitem.documentStatus + "," +
"GETDATE()" + "," +
"'" + Login.userLoggedIn + "'" +
",'" + sitem.ForwarderList + "')";
utString = utString + updateSQL + Main.QueryDelimiter;
utString = utString +
ActivityLogDB.PrepareActivityLogQquerString("insert", "StockItem", "", updateSQL) +
Main.QueryDelimiter;
if (!UpdateTable.UT(utString))
{
status = false;
}
}
catch (Exception)
{
status = false;
}
return(status);
}
public List <stockitem> getStockItems()
{
stockitem sitem;
List <stockitem> StockItems = new List <stockitem>();
try
{
SqlConnection conn = new SqlConnection(Login.connString);
string query = "select StockItemID, Name, ReorderLevel," +
"Level1GCode,Level1GDescription, Level2GCode, Level2GDescription,Level3GCode,Level3GDescription," +
"Level4GCode, Level4GDescription,Level5GCode,Level5GDescription,Level6GCode,Level6GDescription," +
" Unit,UnitDescription, " +
" Status,DocumentStatus,isnull(CreateUser,' '),isnull(ForwardUser,' '),isnull(ApproveUser,' '), " +
" isnull(CreatorName,' '),isnull(ForwarderName,' '),isnull(ApproverName,' ') " +
" from ViewStockItem " +
" order by StockItemID";
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
sitem = new stockitem();
try
{
sitem.StockItemID = reader.GetString(0);
sitem.Name = reader.GetString(1);
sitem.ReorderLevel = reader.GetInt32(2);
sitem.Group1CodeDescription = reader.IsDBNull(3) ? "" : reader.GetString(3);
//sitem.CategoryDescription = reader.GetString(4);
sitem.Group2CodeDescription = reader.IsDBNull(5) ? "" : reader.GetString(5);
//sitem.GroupDescription = reader.GetString(6);
sitem.Group3CodeDescription = reader.IsDBNull(7) ? "" : reader.GetString(7);
//sitem.TypeDescription = reader.GetString(8);
sitem.Group4CodeDescription = reader.IsDBNull(9) ? "" : reader.GetString(9);
//sitem.CategoryDescription = reader.GetString(4);
sitem.Group5CodeDescription = reader.IsDBNull(11) ? "" : reader.GetString(11);
//sitem.GroupDescription = reader.GetString(6);
sitem.Group6CodeDescription = reader.IsDBNull(13) ? "" : reader.GetString(13);
//sitem.TypeDescription = reader.GetString(8);
sitem.Unit = reader.IsDBNull(15) ? "" : reader.GetString(15);
sitem.UnitDescription = reader.IsDBNull(16) ? "" : reader.GetString(16);
sitem.status = reader.GetInt32(17);
sitem.documentStatus = reader.GetInt32(18);
sitem.CreateUser = reader.GetString(19);
sitem.ForwardUser = reader.GetString(20);
sitem.ApproveUser = reader.GetString(21);
sitem.CreateUserName = reader.GetString(22);
sitem.ForwardUserName = reader.GetString(23);
sitem.ApproveUserName = reader.GetString(24);
}
catch (Exception)
{
//MessageBox.Show("Exception retriving StockItem");
}
StockItems.Add(sitem);
}
conn.Close();
}
catch (Exception)
{
MessageBox.Show("Error querying Stock Item Data");
}
return(StockItems);
}
public List <stockitem> getFilteredStockItems(string userList, int opt)
{
stockitem sitem;
List <stockitem> StockItems = new List <stockitem>();
try
{
SqlConnection conn = new SqlConnection(Login.connString);
string query1 = "select StockItemID, Name, ReorderLevel,Level1GCode,Level1GDescription, " +
" Level2GCode, Level2GDescription,Level3GCode,Level3GDescription," +
"Level4GCode, Level4GDescription,Level5GCode,Level5GDescription,Level6GCode,Level6GDescription," +
"Unit,UnitDescription, " +
" Status,DocumentStatus,isnull(CreateUser,' '),isnull(ForwardUser,' '),isnull(ApproveUser,' '), " +
" CreatorName,ForwarderName,ApproverName,ForwarderList " +
" from ViewStockItem " +
" where ((forwarduser='******' and DocumentStatus between 2 and 98) " +
" or (createuser='******' and DocumentStatus=1))" +
" order by StockItemID";
string query2 = "select StockItemID, Name, ReorderLevel,Level1GCode,Level1GDescription, " +
" Level2GCode, Level2GDescription,Level3GCode,Level3GDescription," +
"Level4GCode, Level4GDescription,Level5GCode,Level5GDescription,Level6GCode,Level6GDescription," +
"Unit,UnitDescription, " +
" Status,DocumentStatus,isnull(CreateUser,' '),isnull(ForwardUser,' '),isnull(ApproveUser,' '), " +
" CreatorName,ForwarderName,ApproverName,ForwarderList " +
" from ViewStockItem " +
" where ((createuser='******' and DocumentStatus between 2 and 98 ) " +
" or (ForwarderList like '%" + userList + "%' and DocumentStatus between 2 and 98 and ForwardUser <> '" + Login.userLoggedIn + "'))" +
" order by StockItemID";
string query3 = "select StockItemID, Name, ReorderLevel,Level1GCode,Level1GDescription, " +
" Level2GCode, Level2GDescription,Level3GCode,Level3GDescription," +
"Level4GCode, Level4GDescription,Level5GCode,Level5GDescription,Level6GCode,Level6GDescription," +
"Unit,UnitDescription, " +
" Status,DocumentStatus,isnull(CreateUser,' '),isnull(ForwardUser,' '),isnull(ApproveUser,' '), " +
" CreatorName,ForwarderName,ApproverName,ForwarderList " +
" from ViewStockItem " +
" where ((createuser='******'" +
" or ForwarderList like '%" + userList + "%'" +
" or approveUser='******')" +
" and DocumentStatus = 99) " +
" order by StockItemID";
string query6 = "select StockItemID, Name, ReorderLevel,Level1GCode,Level1GDescription, " +
" Level2GCode, Level2GDescription,Level3GCode,Level3GDescription," +
"Level4GCode, Level4GDescription,Level5GCode,Level5GDescription,Level6GCode,Level6GDescription," +
"Unit,UnitDescription, " +
" Status,DocumentStatus,isnull(CreateUser,' '),isnull(ForwardUser,' '),isnull(ApproveUser,' '), " +
" CreatorName,ForwarderName,ApproverName,ForwarderList " +
" from ViewStockItem " +
" where DocumentStatus = 99" +
" order by Level1GCode,Level2GCode,Level3GCode";
string query = "";
//string query = "select StockItemID, Name, ReorderLevel,Category,CategoryDescription, "+
// " [Group], GroupDescription,Type,TypeDescription,Unit,UnitDescription, "+
// " Status,DocumentStatus,isnull(CreateUser,' '),isnull(ForwardUser,' '),isnull(ApproveUser,' ') " +
// " CreatorName,ForwarderName,ApproverName "+
// " from ViewStockItems where " +
// " Status=0 "+
// " and ((forwardUser in ("+userList+") and documentstatus between 2 and 98) "+
// " or (createuser='******' and DocumentStatus=1))"+
// " order by StockItemID";
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())
{
sitem = new stockitem();
try
{
sitem.StockItemID = reader.GetString(0);
sitem.Name = reader.GetString(1);
sitem.ReorderLevel = reader.GetInt32(2);
sitem.Group1Code = reader.GetString(3);
sitem.Group1CodeDescription = reader.IsDBNull(4)?"": reader.GetString(4);
sitem.Group2Code = reader.IsDBNull(5) ? "" : reader.GetString(5);
sitem.Group2CodeDescription = reader.IsDBNull(6)?"":reader.GetString(6);
sitem.Group3Code = reader.IsDBNull(7) ? "" : reader.GetString(7);
sitem.Group3CodeDescription = reader.IsDBNull(8)?"":reader.GetString(8);
sitem.Group4Code = reader.IsDBNull(9) ? "" : reader.GetString(9);
sitem.Group4CodeDescription = reader.IsDBNull(10)?"":reader.GetString(10);
sitem.Group5Code = reader.IsDBNull(11) ? "" : reader.GetString(11);
sitem.Group5CodeDescription = reader.IsDBNull(12)?"":reader.GetString(12);
sitem.Group6Code = reader.IsDBNull(13) ? "" : reader.GetString(13);
sitem.Group6CodeDescription = reader.IsDBNull(14)?"":reader.GetString(14);
sitem.Unit = reader.IsDBNull(15) ? "" : reader.GetString(15);
sitem.UnitDescription = reader.IsDBNull(16) ? "" : reader.GetString(16);
sitem.status = reader.GetInt32(17);
sitem.documentStatus = reader.GetInt32(18);
sitem.CreateUser = reader.IsDBNull(19) ? "" : reader.GetString(19);
sitem.ForwardUser = reader.IsDBNull(20) ? "" : reader.GetString(20);
sitem.ApproveUser = reader.IsDBNull(21) ? "" : reader.GetString(21);
sitem.CreateUserName = reader.IsDBNull(22) ? "" : reader.GetString(22);
sitem.ForwardUserName = reader.IsDBNull(23) ? "" : reader.GetString(23);
sitem.ApproveUserName = reader.IsDBNull(24) ? "" : reader.GetString(24);
sitem.ForwarderList = reader.IsDBNull(25) ? "" : reader.GetString(25);
}
catch (Exception ex)
{
}
StockItems.Add(sitem);
}
conn.Close();
}
catch (Exception)
{
MessageBox.Show("Error querying Stock Item Data");
}
return(StockItems);
}
