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