public CardCollection SelectMerchant(List<string> MerIDList)
{
string sql = "SELECT MerchantID,Card.CardID,CardName,Image,Value,[Save],Pay,Notes ";
sql += "FROM Merchant INNER JOIN Card ON Merchant.CardID=Card.CardID ";
sql +="WHERE MerchantID= '"+MerIDList[0]+"'";
for(int i=1;i<MerIDList.Count;i++)
{
sql += "OR ";
sql+= "MerchantID='"+MerIDList[i]+"' ";
}
SqlCommand cmd = new SqlCommand(sql, cnn);
CardCollection cardColl = new CardCollection();
cnn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CardBO cardBO = new CardBO();
cardBO.Image = dr["Image"].ToString();
cardBO.CardName = dr["CardName"].ToString();
MerchantCollection MerchantColl = new MerchantCollection();
MerchantBO merBO = new MerchantBO();
merBO.MerchantID = dr["MerchantID"].ToString();
merBO.Value = Convert.ToDecimal(dr["Value"].ToString());
merBO.Pay = Convert.ToDecimal(dr["Pay"].ToString());
merBO.Save = Convert.ToDecimal(dr["Save"].ToString());
merBO.Notes = dr["Notes"].ToString();
//Add to merchantColl
MerchantColl.Add(merBO);
//Assing Card.Detail = MerchantColl
cardBO.Details = MerchantColl;
//Add Card to cardColl
cardColl.Add(cardBO);
}
cnn.Close();
return cardColl;
}
public CardCollection RandomCardList(CardCollection cardcoll, int quantity)
{
int max = cardcoll.Count;
try
{
CardCollection rescoll= new CardCollection();
while(cardcoll.Count>0)
{
Random rd = new Random();
int index = rd.Next(0,max);
if(cardcoll.Index(index).Image!="~/images/CardImages/no_images.jpg")
rescoll.Add(cardcoll.Index(index));
cardcoll.Remove(index);
max--;
if(rescoll.Count==quantity)break;
}
return rescoll;
}
catch
{
return null;
}
}
///////////////////////////////SELECT//////////////////////////////////////
public CardCollection SelectMerchant(string cardID)
{
SqlCommand cmd = new SqlCommand("SELECT MerchantID,Card.CardID,CardName,Image,Value,[Save],Pay,Notes FROM Merchant INNER JOIN Card ON Merchant.CardID=Card.CardID WHERE Card.cardid=@cardid Order By CardName", cnn);
cmd.Parameters.AddWithValue("@cardid", cardID);
CardCollection cardColl = new CardCollection();
cnn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CardBO cardBO = new CardBO();
cardBO.Image = dr["Image"].ToString();
cardBO.CardName = dr["CardName"].ToString();
MerchantCollection MerchantColl = new MerchantCollection();
MerchantBO merBO = new MerchantBO();
merBO.MerchantID = dr["MerchantID"].ToString();
merBO.Value = Convert.ToDecimal(dr["Value"].ToString());
merBO.Pay = Convert.ToDecimal(dr["Pay"].ToString());
merBO.Save = Convert.ToDecimal(dr["Save"].ToString());
merBO.Notes = dr["Notes"].ToString();
//Add to merchantColl
MerchantColl.Add(merBO);
//Assing Card.Detail = MerchantColl
cardBO.Details = MerchantColl;
//Add Card to cardColl
cardColl.Add(cardBO);
}
cnn.Close();
return cardColl;
}
public CardCollection SelectOutOfStockMerchant(int catid)
{
CardCollection CardColl = new CardCollection();
SqlCommand cmd = new SqlCommand("SELECT CardID,CardName AdID, CatID, CardName, Description, Image FROM Card WHERE (CardID NOT IN (SELECT CardID FROM Merchant)) AND card.catid=@catid Order By CardName", cnn);
cmd.Parameters.AddWithValue("@catid", catid);
cnn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CardBO cardBO = new CardBO();
cardBO.CardID = dr["CardID"].ToString();
cardBO.CardName = dr["CardName"].ToString();
cardBO.Image = dr["Image"].ToString();
CardColl.Add(cardBO);
}
cnn.Close();
return CardColl;
}
/// <summary>
/// //////////////////////for buy-cards.aspx////////////////////////////////////
public CardCollection SelectDisCountMerchant(int catid)
{
CardCollection CardColl = new CardCollection();
SqlCommand cmd = new SqlCommand("SELECT Card.CardID,CardName,Image, MAX(Merchant.[Save]) AS Save1,MerchantCount FROM Card INNER JOIN Merchant ON Card.CardID = Merchant.CardID WHERE card.catid=@catid GROUP BY Card.CardID,Image,CardName,MerchantCount HAVING MerchantCount>0 Order By CardName", cnn);
cmd.Parameters.AddWithValue("@catid", catid);
cnn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CardBO cardBO = new CardBO();
MerchantCollection Mercoll = new MerchantCollection();
MerchantBO merBO = new MerchantBO();
cardBO.CardID = dr["CardID"].ToString();
cardBO.CardName = dr["CardName"].ToString();
cardBO.Image = dr["Image"].ToString();
cardBO.MerchantCount = Convert.ToInt32(dr["merchantcount"].ToString());
merBO.Save = Convert.ToDecimal(dr["Save1"].ToString());
Mercoll.Add(merBO);
cardBO.Details = Mercoll;
CardColl.Add(cardBO);
}
cnn.Close();
return CardColl;
}
public CardCollection SelectRelatedCard(string cardid)
{
CardCollection CardColl = new CardCollection();
string sql = "SELECT Card.CardID,CardName,Image, MAX(Merchant.[Save]) AS Save1,MerchantCount ";
sql += "FROM Card LEFT OUTER JOIN Merchant ON Card.CardID = Merchant.CardID ";
sql += "WHERE Card.CardID<>@cardid AND catid=(SELECT catid from Card where cardid=@cardID) ";
sql += "GROUP BY Card.CardID,Image,CardName,MerchantCount Order By CardName";
SqlCommand cmd = new SqlCommand(sql, cnn);
cmd.Parameters.AddWithValue("@cardid", cardid);
cnn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CardBO cardBO = new CardBO();
MerchantCollection Mercoll = new MerchantCollection();
MerchantBO merBO = new MerchantBO();
cardBO.CardID = dr["CardID"].ToString();
cardBO.CardName = dr["CardName"].ToString();
cardBO.Image = dr["Image"].ToString();
cardBO.MerchantCount = Convert.ToInt32(dr["merchantcount"].ToString());
if(dr["Save1"].ToString()!="")
merBO.Save = Convert.ToDecimal(dr["Save1"].ToString());
Mercoll.Add(merBO);
cardBO.Details = Mercoll;
CardColl.Add(cardBO);
}
cnn.Close();
return CardColl;
}
public CardCollection SelectCard(int catid)//Get every cards in a Category
{
CardCollection cardColl = new CardCollection();
SqlCommand cmd = null;
if (catid != -1)
{
cmd = new SqlCommand("SELECT * FROM Card where catid=@cateid Order By CardName", cnn);
cmd.Parameters.AddWithValue("@cateid", catid);
}
else
cmd = new SqlCommand("SELECT * FROM Card Order By CardName", cnn);
cnn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CardBO cardBO = new CardBO();
cardBO.CardID = dr["CardID"].ToString();
cardBO.CatID = Convert.ToInt32(dr["CatID"].ToString());
cardBO.CardName = dr["CardName"].ToString();
cardBO.Description = dr["Description"].ToString();
cardBO.Image = dr["Image"].ToString();
cardBO.MerchantCount=Convert.ToInt32(dr["MerchantCount"].ToString());
cardColl.Add(cardBO);
}
cnn.Close();
return cardColl;
}