public int GetPurchItemCount(PurchItemGetType getType, int custId = 0, string custEmail = null, int purchCartId = 0, DateTime?fromPurchDate = null, DateTime?toPurchDate = null)
{
var purchItems = GetPurchItemDetails(getType, custId, custEmail, purchCartId, fromPurchDate, toPurchDate);
if (purchItems != null)
{
return(purchItems.Count());
}
else
{
return(0);
}
}
public List <PurchaseItemObject> GetPurchItemDetails(PurchItemGetType getType, int custId = 0, string custEmail = null, int purchCartId = 0, DateTime?fromPurchDate = null, DateTime?toPurchDate = null, PurchItemOrderBy orderBy = PurchItemOrderBy.purchDateAsc, int limit = 0, int currentPage = 0)
{
List <PurchaseItemObject> returnList = new List <PurchaseItemObject>();
using (MySqlConnection con = ConnectionManager.GetOpenConnection())
{
StringBuilder sbSqlString = new StringBuilder();
sbSqlString.Append("SELECT pi.*, pc.purchased_date, it.title ");
sbSqlString.Append("FROM purchase_items pi, purchase_cart pc, items it ");
sbSqlString.Append("WHERE pi.purchase_cart_id = pc.id ");
sbSqlString.Append("AND pi.item_id = it.id ");
sbSqlString.Append("AND ");
switch (getType)
{
case PurchItemGetType.customerEmail:
sbSqlString.Append("pc.cust_email_address = @custEmail ");
break;
case PurchItemGetType.customerId:
sbSqlString.Append("pc.cust_id = @custId ");
break;
case PurchItemGetType.purchCartId:
sbSqlString.Append("pc.id = @cartId ");
break;
case PurchItemGetType.purchDate:
sbSqlString.Append("pc.purchased_date BETWEEN @fromPurchDate AND @toPurchDate ");
break;
default:
break;
}
switch (orderBy)
{
case PurchItemOrderBy.purchDateAsc:
sbSqlString.Append("ORDER BY pc.purchased_date ASC ");
break;
case PurchItemOrderBy.purchDateDesc:
sbSqlString.Append("ORDER BY pc.purchased_date DESC ");
break;
case PurchItemOrderBy.OrderIdAsc:
sbSqlString.Append("ORDER BY pi.id ASC ");
break;
case PurchItemOrderBy.OrderIdDesc:
sbSqlString.Append("ORDER BY pi.id DESC ");
break;
default:
break;
}
if (limit > 0)
{
sbSqlString.Append("LIMIT " + currentPage + "," + limit);
}
string sqlString = sbSqlString.ToString();
using (MySqlCommand com = new MySqlCommand(sqlString, con))
{
switch (getType)
{
case PurchItemGetType.customerEmail:
com.Parameters.AddWithValue("@custEmail", custEmail);
break;
case PurchItemGetType.customerId:
com.Parameters.AddWithValue("@custId", custId);
break;
case PurchItemGetType.purchCartId:
com.Parameters.AddWithValue("@cartId", purchCartId);
break;
case PurchItemGetType.purchDate:
com.Parameters.AddWithValue("@fromPurchDate", fromPurchDate);
com.Parameters.AddWithValue("@toPurchDate", toPurchDate);
break;
default:
break;
}
MySqlDataReader dr = com.ExecuteReader();
while (dr.Read())
{
returnList.Add(GetInfoAddedPurchItemObject(dr));
}
}
}
return(returnList);
}
public static void AddPurchItemPagination(int currentPage, int currentViewPerPage, ref HtmlGenericControl paginationCtrl, PurchItemGetType getType, int custId = 0, string custEmail = null, int purchCartId = 0, DateTime?fromPurchDate = null, DateTime?toPurchDate = null)
{
var itemCount = new PurchItemModel().GetPurchItemCount(getType, custId, custEmail, purchCartId, fromPurchDate, toPurchDate);
var noOfPages = Convert.ToInt32(Math.Ceiling(((double)itemCount / (double)currentViewPerPage)));
var displayingPages = CommonManager.GetDisplayingPages(currentPage, noOfPages);
CreateCommonPaginationControl(currentPage, ref paginationCtrl, displayingPages, noOfPages);
}
