public CardBO()
{
_cardID = string.Empty;
_cardName = string.Empty;
_desription = string.Empty;
_image = null;
_details = null;
_merchantcount = 0;
}
public MerchantCollection SelectOnlyMerchant(string cardID)
{
MerchantCollection MerchantColl = new MerchantCollection();
SqlCommand cmd = new SqlCommand("SELECT * FROM Merchant WHERE cardid=@cardid", cnn);
cmd.Parameters.AddWithValue("@cardid", cardID);
cnn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
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);
}
cnn.Close();
return MerchantColl;
}
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;
}
///////////////////////////////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;
}
/// <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;
}