//category and company search public CatComSearch GetSearchItem(int itemID) { string query = "select ComName,catName,ItemName,ReorderLevel, AvailableQuantity = ISNULL((select SUM(Stockin) from T_StockIn WHERE ItemID=" + itemID + "),0) - ISNULL((select SUM(StockOutQuantity) from T_StockOut WHERE ItemID=" + itemID + "),0) from T_Item,T_Category,T_Company WHERE T_Category.catID=T_Item.CategoryID AND T_Company.ComID=T_Item.CompanyID AND ID=" + itemID + ""; CatComSearch aItem = new CatComSearch(); try { cmd = new SqlCommand(query, oConnectionClass.GetConnection()); itemIdReader = cmd.ExecuteReader(); while (itemIdReader.Read()) { aItem.Company = itemIdReader["ComName"].ToString(); aItem.Category = itemIdReader["catName"].ToString(); aItem.Item = itemIdReader["ItemName"].ToString(); aItem.ReorderLevel = (int)itemIdReader["ReorderLevel"]; aItem.AvilableQuantity = (int)itemIdReader["AvailableQuantity"]; } } finally { itemIdReader.Close(); oConnectionClass.GetClose(); } return(aItem); }
public List <CatComSearch> GetItemList(int companyid, int catagoryid) { List <CatComSearch> aList = new List <CatComSearch>(); List <int> itemIdList = new List <int>(); string itemIdQuery; if (companyid > 0 && catagoryid > 0) { itemIdQuery = "SELECT ID FROM T_Item WHERE categoryID = " + catagoryid + " AND companyID = " + companyid + ""; } else if (companyid > 0) { itemIdQuery = "SELECT ID FROM T_Item WHERE companyID = " + companyid + ""; } else { itemIdQuery = "SELECT ID FROM T_Item WHERE categoryID = " + catagoryid + ""; } CatComSearch aItem; try { cmd = new SqlCommand(itemIdQuery, oConnectionClass.GetConnection()); itemIdReader = cmd.ExecuteReader(); while (itemIdReader.Read()) { aItem = new CatComSearch(); int itemid = (int)itemIdReader["ID"]; itemIdList.Add(itemid); } foreach (int item in itemIdList) { aItem = GetSearchItem(item); aList.Add(aItem); } } finally { itemIdReader.Close(); oConnectionClass.GetClose(); } return(aList); }