public Boolean reverseStockITem(stockitem3 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 static string getUnitForSelectedStockItem(string ItemID, string ItemName)
{
stockitem3 sitem = new stockitem3();
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(stockitem3 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 updateStockItem(stockitem3 sitem, stockitem3 prevsitem)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "update StockItem set " +
"Name='" + sitem.Name +
"', Level1GCode='" + sitem.Group1Code +
"', Level2GCode='" + sitem.Group2Code +
"', Level3GCode='" + sitem.Group3Code +
"', Unit='" + sitem.Unit +
"', ReorderLevel=" + sitem.ReorderLevel +
", Status=" + sitem.status +
", ForwarderList='" + sitem.ForwarderList + "'" +
", HSNCode='" + sitem.HSNCode + "'" +
" 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 List <stockitem3> getStockItems()
{
stockitem3 sitem;
List <stockitem3> StockItems = new List <stockitem3>();
try
{
SqlConnection conn = new SqlConnection(Login.connString);
string query = "select StockItemID, Name, ReorderLevel,Level1Gcode,Level1GDescription, " +
" Level2Gcode,Level2GDescription,Level3Gcode,Level3GDescription,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 stockitem3();
try
{
sitem.StockItemID = reader.GetString(0);
sitem.Name = reader.GetString(1);
sitem.ReorderLevel = reader.GetInt32(2);
sitem.Group1CodeDescription = reader.GetString(3);
//sitem.CategoryDescription = reader.GetString(4);
sitem.Group2CodeDescription = reader.GetString(5);
//sitem.GroupDescription = reader.GetString(6);
sitem.Group3CodeDescription = reader.GetString(7);
//sitem.TypeDescription = reader.GetString(8);
sitem.Unit = reader.GetString(9);
sitem.UnitDescription = reader.GetString(10);
sitem.status = reader.GetInt32(11);
sitem.documentStatus = reader.GetInt32(12);
sitem.CreateUser = reader.GetString(13);
sitem.ForwardUser = reader.GetString(14);
sitem.ApproveUser = reader.GetString(15);
sitem.CreateUserName = reader.GetString(16);
sitem.ForwardUserName = reader.GetString(17);
sitem.ApproveUserName = reader.GetString(18);
}
catch (Exception)
{
}
StockItems.Add(sitem);
}
conn.Close();
}
catch (Exception)
{
MessageBox.Show("Error querying Stock Item Data");
}
return(StockItems);
}
public Boolean validateStockItem(stockitem3 sitem)
{
Boolean status = true;
string utString = "";
try
{
//if (sitem.StockItemID.Trim().Length == 0 || sitem.StockItemID == null)
//{
// return false;
//}
if (sitem.HSNCode.Trim().Length == 0 || sitem.HSNCode == 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 ex)
{
return(false);
}
return(status);
}
public Boolean insertStockItem(stockitem3 sitem)
{
Boolean status = true;
string utString = "";
try
{
string updateSQL = "insert into StockItem " +
" (StockItemID,Name,Reorderlevel,Level1GCode,Level2GCode,Level3GCode,Unit,Status,DocumentStatus,CreateTime,CreateUser,ForwarderList,HSNCode)" +
"values (" +
"IDENT_CURRENT('StockItem')" +
",'" + sitem.Name + "'," +
sitem.ReorderLevel + "," +
"'" + sitem.Group1Code + "'," +
"'" + sitem.Group2Code + "'," +
"'" + sitem.Group3Code + "'," +
"'" + sitem.Unit + "'," +
sitem.status + "," +
sitem.documentStatus + "," +
"GETDATE()" + "," +
"'" + Login.userLoggedIn + "'" +
",'" + sitem.ForwarderList + "','" + sitem.HSNCode + "')";
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 <stockitem3> getFilteredStockItems(string userList, int opt)
{
stockitem3 sitem;
List <stockitem3> StockItems = new List <stockitem3>();
try
{
SqlConnection conn = new SqlConnection(Login.connString);
string query1 = "select StockItemID, Name, ReorderLevel,Level1Gcode,Level1GDescription, " +
" Level2Gcode,Level2GDescription,Level3Gcode,Level3GDescription,Unit,UnitDescription, " +
" Status,DocumentStatus,isnull(CreateUser,' '),isnull(ForwardUser,' '),isnull(ApproveUser,' '), " +
" CreatorName,ForwarderName,ApproverName,ForwarderList,HSNCode " +
" 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,Unit,UnitDescription, " +
" Status,DocumentStatus,isnull(CreateUser,' '),isnull(ForwardUser,' '),isnull(ApproveUser,' '), " +
" CreatorName,ForwarderName,ApproverName,ForwarderList,HSNCode " +
" 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,Unit,UnitDescription, " +
" Status,DocumentStatus,isnull(CreateUser,' '),isnull(ForwardUser,' '),isnull(ApproveUser,' '), " +
" CreatorName,ForwarderName,ApproverName,ForwarderList,HSNCode " +
" 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,Unit,UnitDescription, " +
" Status,DocumentStatus,isnull(CreateUser,' '),isnull(ForwardUser,' '),isnull(ApproveUser,' '), " +
" CreatorName,ForwarderName,ApproverName,ForwarderList,HSNCode " +
" 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 stockitem3();
try
{
sitem.StockItemID = reader.GetString(0);
sitem.Name = reader.IsDBNull(1) ? "" : reader.GetString(1);
sitem.ReorderLevel = reader.GetInt32(2);
sitem.Group1Code = reader.IsDBNull(3) ? "" : reader.GetString(3);
if (!reader.IsDBNull(4))
{
sitem.Group1CodeDescription = reader.GetString(4);
}
else
{
sitem.Group1CodeDescription = "";
}
sitem.Group2Code = reader.IsDBNull(5) ? "" : reader.GetString(5);
if (!reader.IsDBNull(6))
{
sitem.Group2CodeDescription = reader.GetString(6);
}
else
{
sitem.Group2CodeDescription = "";
}
sitem.Group3Code = reader.IsDBNull(7) ? "" : reader.GetString(7);
if (!reader.IsDBNull(8))
{
sitem.Group3CodeDescription = reader.GetString(8);
}
else
{
sitem.Group3CodeDescription = "";
}
sitem.Unit = reader.IsDBNull(9)?"":reader.GetString(9);
sitem.UnitDescription = reader.IsDBNull(10) ? "" : reader.GetString(10);
sitem.status = reader.GetInt32(11);
sitem.documentStatus = reader.GetInt32(12);
sitem.CreateUser = reader.GetString(13);
sitem.ForwardUser = reader.IsDBNull(14) ? "" : reader.GetString(14);
sitem.ApproveUser = reader.IsDBNull(15) ? "" : reader.GetString(15);
sitem.CreateUserName = reader.IsDBNull(16) ? "" : reader.GetString(16);
sitem.ForwardUserName = reader.IsDBNull(17) ? "" : reader.GetString(17);
sitem.ApproveUserName = reader.IsDBNull(18) ? "" : reader.GetString(18);
sitem.HSNCode = reader.IsDBNull(20)?"": reader.GetString(20);
if (!reader.IsDBNull(19))
{
sitem.ForwarderList = reader.GetString(19);
}
else
{
sitem.ForwarderList = "";
}
}
catch (Exception ex)
{
}
StockItems.Add(sitem);
}
conn.Close();
}
catch (Exception)
{
MessageBox.Show("Error querying Stock Item Data");
}
return(StockItems);
}