public bool EditMerchant(string merchantid, decimal value, decimal pay, decimal save, string notes)
{
try
{
MerchantBO merchantBO = new MerchantBO();
merchantBO.MerchantID = merchantid;
merchantBO.Value = value;
merchantBO.Pay = pay;
merchantBO.Save = save;
merchantBO.Notes = notes;
cardDAO.UpdateMerchant(merchantBO);
return true;
}
catch
{
return false;
}
}
public void Add(MerchantBO merchantBO)
{
List.Add(merchantBO);
}
public bool AddMerchant(string cardid,decimal value,decimal pay,decimal save,string notes)
{
try
{
MerchantBO merBO = new MerchantBO();
merBO.CardID = cardid;
merBO.Value = value;
merBO.Pay = pay;
merBO.Save = save;
merBO.Notes = notes;
cardDAO.InsertMerchant(merBO);
return true;
}
catch
{
return false;
}
}
//////INSERT//////////
public void InsertMerchant(MerchantBO merBO)
{
SqlCommand cmd;
cmd = new SqlCommand("INSERT INTO Merchant VALUES(dbo.NewMerChantID(),@CardID,@value,@pay,@save,@notes)",cnn);
cmd.Parameters.AddWithValue("@CardId",merBO.CardID);
cmd.Parameters.AddWithValue("@value",merBO.Value);
cmd.Parameters.AddWithValue("@pay",merBO.Pay);
cmd.Parameters.AddWithValue("@save",merBO.Save);
cmd.Parameters.AddWithValue("@notes",merBO.Notes);
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
UpMerchantCount();
UpCardCount();
}
//////UPDATE//////////
public void UpdateMerchant(MerchantBO merBO)
{
SqlCommand cmd = null;
cmd = new SqlCommand("UPDATE Merchant SET Value=@value,Pay=@pay,[Save]=@save,Notes=@notes WHERE MerchantID=@MerchantID", cnn);
cmd.Parameters.AddWithValue("@value", merBO.Value);
cmd.Parameters.AddWithValue("@pay", merBO.Pay);
cmd.Parameters.AddWithValue("@save", merBO.Save);
cmd.Parameters.AddWithValue("@notes", merBO.Notes);
cmd.Parameters.AddWithValue("@MerchantID", merBO.MerchantID);
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
UpMerchantCount();
UpCardCount();
}
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;
}