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); }