protected void Page_Load(object sender, EventArgs e)
{
AqlQuery query = WAFContext.Session.CreateQuery();
AqlAliasShop aliasShop = new AqlAliasShop();
AqlAliasOrder aliasOrder = new AqlAliasOrder();
AqlAliasRelShopOrders relShopOrder = new AqlAliasRelShopOrders(aliasShop, aliasOrder);
query.From(relShopOrder);
AqlResultInteger orderId = query.Select(aliasOrder.NodeId);
AqlResultDateTime orderDate = query.Select(aliasOrder.CreateDate);
AqlResultFloat orderTotal = query.Select(aliasOrder.OrderTotal);
query.Where(aliasShop.NodeId == WAFShopHelper.GetCurrentShopId());
query.Where(aliasOrder.OrderStatus != (int)OrderStatus.Basket);
query.OrderBy(aliasOrder.CreateDate, true);
contentListLatestOrders.Query = query;
}
protected int GetNumberOfOrdersShipped(DateRange range)
{
AqlQuery query = WAFContext.Session.CreateQuery();
AqlAliasOrder aliasOrder = new AqlAliasOrder();
query.From(aliasOrder);
AqlResultInteger count = query.Select(Aql.Count(aliasOrder.NodeId));
query.Where(aliasOrder.SiteId == WAFContext.Session.SiteId);
query.Where(aliasOrder.OrderStatus == (int)OrderStatus.Shipped);
query.Where(aliasOrder.DateShipped > GetRangeStart(range));
query.Where(aliasOrder.DateShipped < GetRangeEnd(range));
AqlResultSet rs = query.Execute();
while (rs.Read()) {
return count.Value;
}
return 0;
}
protected double GetRevenueInPeriod(DateRange range)
{
AqlQuery query = WAFContext.Session.CreateQuery();
AqlAliasOrder aliasOrder = new AqlAliasOrder();
query.From(aliasOrder);
AqlResultFloat revenueInPeriod = query.Select(Aql.Sum(aliasOrder.OrderTotal));
query.Where(aliasOrder.SiteId == WAFContext.Session.SiteId);
query.Where(aliasOrder.OrderStatus == (int)OrderStatus.Shipped | aliasOrder.OrderStatus == (int)OrderStatus.Ordered);
query.Where(aliasOrder.CreateDate > GetRangeStart(range));
query.Where(aliasOrder.CreateDate < GetRangeEnd(range));
AqlResultSet rs = query.Execute();
while (rs.Read()) {
return revenueInPeriod.Value;
}
return 0;
}
protected void Page_Load(object sender, EventArgs e)
{
AqlQuery query = WAFContext.Session.CreateQuery();
AqlAliasShop aliasShop = new AqlAliasShop();
AqlAliasOrder aliasOrder = new AqlAliasOrder();
AqlAliasOrderItem aliasOrderItem = new AqlAliasOrderItem();
AqlAliasProductBase aliasProduct = new AqlAliasProductBase();
AqlAliasRelShopOrders relShopOrder = new AqlAliasRelShopOrders(aliasShop, aliasOrder);
AqlAliasRelOrderItems relOrderItems = new AqlAliasRelOrderItems(relShopOrder, aliasOrderItem, aliasOrder);
AqlAliasRelProductOrdeItems relProductOrderItems = new AqlAliasRelProductOrdeItems(aliasProduct, relOrderItems, aliasOrderItem);
query.From(relProductOrderItems);
// AqlResultContent<ProductBase> product = query.Select<ProductBase>(aliasProduct);
AqlResultInteger nodeId = query.Select(aliasProduct.NodeId);
AqlResultShortString nameStr = query.Select(aliasProduct.Name);
AqlResultDateTime orderDate = query.Select(aliasOrder.CreateDate);
AqlResultInteger orderId = query.Select(aliasOrder.NodeId);
query.Where(aliasShop.NodeId == WAFShopHelper.GetCurrentShopId());
query.OrderBy(aliasOrder.CreateDate, true);
contentListLastOrderedProducts.Query = query;
}
private void UpdateCountryDDL()
{
int countryId = Utils.GetIntegerOnly(ddlCountryFilter.SelectedValue);
if (WAFShopHelper.GetCurrentShopId() > 0) {
Shop shop = WAFContext.Session.GetContent<Shop>(WAFShopHelper.GetCurrentShopId());
List<Country> countries = new List<Country>();
AqlQuery q = WAFContext.Session.CreateQuery();
AqlAliasShop aliasShop = new AqlAliasShop();
AqlAliasOrder aliasOrder = new AqlAliasOrder();
AqlAliasCountry aliaCountry = new AqlAliasCountry();
AqlAliasRelShopOrders relShopOrder = new AqlAliasRelShopOrders(aliasShop, aliasOrder);
AqlAliasRelOrderShippingCountry relOrderShippingCountry = new AqlAliasRelOrderShippingCountry(relShopOrder, aliaCountry, aliasOrder);
q.From(relOrderShippingCountry);
AqlResultContent<Country> country = q.Select<Country>(aliaCountry);
AqlResultShortString name = q.Select(aliaCountry.Name);
q.Where(aliasShop.NodeId == WAFShopHelper.GetCurrentShopId());
q.Distinct();
q.OrderBy(aliaCountry.Name);
AqlResultSet rs = q.Execute();
while (rs.Read()) {
countries.Add(country.Value);
}
ddlCountryFilter.DataSource = countries;
ddlCountryFilter.DataBind();
}
if (countryId > 0) {
ddlCountryFilter.SelectedValue = countryId.ToString();
}
}
public AqlQuery GetOrderSelectionQuery(OrderFilter filter)
{
if (WAFShopHelper.SiteHasShop(WAFContext.Request.SiteId)) {
if (WAFShopHelper.GetCurrentShopId() > 0) {
AqlQuery query = WAFContext.Session.CreateQuery();
AqlAliasShop aliasShop = new AqlAliasShop();
AqlAliasOrder aliasOrder = new AqlAliasOrder();
AqlAliasOrderItem aliasOrderItem = new AqlAliasOrderItem();
AqlAliasProductBase aliasProductBase = new AqlAliasProductBase();
AqlAliasCountry aliaCountry = new AqlAliasCountry();
AqlAliasRelShopOrders relShopOrder = new AqlAliasRelShopOrders(aliasShop, aliasOrder);
AqlAliasRelOrderItems relOrderItems = new AqlAliasRelOrderItems(relShopOrder, aliasOrderItem, aliasOrder);
AqlAliasRelProductOrdeItems relProductOrderItems = new AqlAliasRelProductOrdeItems(aliasProductBase, relOrderItems, aliasOrderItem);
AqlAliasRelOrderShippingCountry relOrderShippingCountry = new AqlAliasRelOrderShippingCountry(relShopOrder, aliaCountry, aliasOrder);
switch (filter) {
case OrderFilter.NoFilter:
query.From(relShopOrder);
break;
case OrderFilter.OrderStatus:
query.From(relShopOrder);
query.Where(aliasOrder.OrderStatus == Utils.GetIntegerOnly(ddlOrderStatusFilter.SelectedValue));
break;
case OrderFilter.NumItemsOrdered:
//int rangeType = Utils.GetIntegerOnly(ddlNumItemsCriteria.SelectedValue);
//query.From(relProductOrderItems);
//AqlResultInteger orderItemsCount = query.Select(Aql.Count(aliasOrder.NodeId));
//query.GroupBy(aliasOrder.NodeId);
//query.GroupBy(aliasOrder.LCID);
//query.GroupBy(aliasOrder.Revision);
//query.GroupBy(aliasOrder.ContentClassId);
//switch ((RangeCriteria)rangeType) {
// case RangeCriteria.Above:
// int aboveNumber = Utils.GetIntegerOnly(txtNumItemsOrderedFilterLevel1.Text);
// query.Having(Aql.Count(aliasOrder.NodeId) >= aboveNumber);
// break;
// case RangeCriteria.Below:
// int belowNum = Utils.GetIntegerOnly(txtNumItemsOrderedFilterLevel1.Text);
// query.Having(Aql.Count(aliasOrder.NodeId) <= belowNum);
// break;
// case RangeCriteria.Between:
// int level1Num = Utils.GetIntegerOnly(txtNumItemsOrderedFilterLevel1.Text);
// int level2Num = Utils.GetIntegerOnly(txtNumItemsOrderedFilterLevel2.Text);
// query.Having(Aql.Count(aliasOrder.NodeId) > level1Num & Aql.Count(aliasOrder.NodeId) < level2Num);
// break;
// case RangeCriteria.EqualTo:
// int orderedNumber = Utils.GetIntegerOnly(txtNumItemsOrderedFilterLevel1.Text);
// query.Having(Aql.Count(aliasOrder.NodeId) == orderedNumber);
// break;
// default:
// break;
//}
break;
case OrderFilter.OrderTotal:
int rangeOrderTotalType = Utils.GetIntegerOnly(ddlOrderTotalCriteria.SelectedValue);
switch ((RangeCriteria)rangeOrderTotalType) {
case RangeCriteria.Above:
int aboveNumber = Utils.GetIntegerOnly(txtOrderTotalFilterLevel1.Text);
query.Where(aliasOrder.OrderTotal >= aboveNumber);
break;
case RangeCriteria.Below:
int belowNum = Utils.GetIntegerOnly(txtOrderTotalFilterLevel1.Text);
query.Where(aliasOrder.OrderTotal <= belowNum);
break;
case RangeCriteria.Between:
int level1Num = Utils.GetIntegerOnly(txtOrderTotalFilterLevel1.Text);
int level2Num = Utils.GetIntegerOnly(txtOrderTotalFilterLevel2.Text);
query.Where(aliasOrder.OrderTotal > level1Num & aliasOrder.OrderTotal < level2Num);
break;
case RangeCriteria.EqualTo:
int orderedNumber = Utils.GetIntegerOnly(txtOrderTotalFilterLevel1.Text);
query.Where(aliasOrder.OrderTotal == orderedNumber);
break;
default:
break;
}
query.From(relShopOrder);
break;
case OrderFilter.Country:
int countryId = Utils.GetIntegerOnly(ddlCountryFilter.SelectedValue);
query.Where(aliaCountry.NodeId == countryId);
query.From(relOrderShippingCountry);
break;
case OrderFilter.Search:
int searchTypeId = Utils.GetIntegerOnly(ddlSearchType.SelectedValue);
if (searchTypeId == 0) { //order id
query.Where(aliasOrder.NodeId == Utils.GetIntegerOnly(txtSearch.Text));
} else if (searchTypeId == 1) { //surname
query.Where(Aql.Like(aliasOrder.ShippingSurname, "%" + txtSearch.Text + "%"));
} else if (searchTypeId == 2) { //forename
query.Where(Aql.Like(aliasOrder.ShippingForename, "%" + txtSearch.Text + "%"));
} else if (searchTypeId == 4) {
query.Where(Aql.Like(aliasOrder.BillingCompany, "%" + txtSearch.Text + "%"));
} else { //regular index search
var nodeIds = from o in WAFContext.Session.Search<Order>("%" + txtSearch.Text + "%", null, true) select o.NodeId;
query.Where(Aql.In(aliasOrder.NodeId, nodeIds.ToList<int>()));
}
query.From(relShopOrder);
break;
case OrderFilter.DateOrdered:
int numDateCriteriaFilter = Utils.GetIntegerOnly(ddlDateCriteria.SelectedValue);
DateTime dt1 = new DateTime();
DateTime dt2 = new DateTime();
switch ((DateCriteria)numDateCriteriaFilter) {
case DateCriteria.Before:
//int aboveNumber = Utils.GetIntegerOnly(txtNumItemsOrderedFilterLevel1.Text);
if (DateTime.TryParse(datePicker1.Text, out dt1)) {
query.Where(aliasOrder.DateOrdered <= (DateTime)dt1);
}
break;
case DateCriteria.After:
if (DateTime.TryParse(datePicker1.Text, out dt1)) {
query.Where(aliasOrder.DateOrdered >= (DateTime)dt1);
}
break;
case DateCriteria.Between:
if (DateTime.TryParse(datePicker1.Text, out dt1)) {
if (DateTime.TryParse(datePicker2.Text, out dt2)) {
if (dt1 != null && dt2 != null) {
query.Where(aliasOrder.DateOrdered >= (DateTime)dt1);
query.Where(aliasOrder.DateOrdered <= (DateTime)dt2);
} else if (dt1 == null && dt2 != null) {
query.Where(aliasOrder.DateOrdered <= (DateTime)dt2);
} else if (dt1 != null && dt2 == null) {
query.Where(aliasOrder.DateOrdered >= (DateTime)dt1);
}
}
}
break;
default:
break;
}
query.From(relShopOrder);
break;
default:
break;
}
if (filter != OrderFilter.OrderStatus) {
if (!chkIncludeCartStatus.Checked) {
query.Where(aliasOrder.OrderStatus != (int)OrderStatus.Basket);
}
}
query.Distinct();
//query.Select<Order>();
query.Select(aliasOrder);
query.Select(aliasOrder.NodeId);
query.Select(aliasOrder.BillingForename);
query.Select(aliasOrder.BillingSurname);
query.Select(aliasOrder.OrderTotal);
query.Select(aliasOrder.ItemsSubtotalExVat);
//query.Select(aliasOrder.C);
query.Select(aliasOrder.VATAmount);
query.Select(aliasOrder.ShippingAmount);
query.Select(aliasOrder.CreateDate);
query.Select(aliasOrder.ChangeDate);
query.Select(aliasOrder.DateOrdered);
query.Where(AqlShop.NodeId == WAFShopHelper.GetCurrentShopId());
query.OrderBy(aliasOrder.DateOrdered, true);
query.OrderBy(aliasOrder.ChangeDate, true);
return query;
//contentListOrders.Query = query;
//contentListOrders.DataBind();
} else {
AqlQuery q = WAFContext.Session.CreateQuery();
AqlAliasOrder aliasOrder = new AqlAliasOrder();
q.Select(aliasOrder);
q.From(aliasOrder);
q.Where(aliasOrder.SiteId == WAFContext.Request.SiteId);
q.Where(aliasOrder.Name == "e4r5345345 25er grf d");
return q;
}
} else {
AqlQuery q = WAFContext.Session.CreateQuery();
AqlAliasOrder aliasOrder = new AqlAliasOrder();
q.Select(aliasOrder);
q.From(aliasOrder);
q.Where(aliasOrder.SiteId == WAFContext.Request.SiteId);
q.Where(aliasOrder.Name == "e4r5345345 25er grf d");
return q;
//contentListOrders.Query = q;
//contentListOrders.DataBind();
}
}
private int GetNumberOfMonthsBetweenFirstAndLatestOrder()
{
DateTime firstOrder = new DateTime();
DateTime lastOrder = new DateTime();
AqlQuery q = WAFRuntime.ContextSession.CreateQuery();
AqlAliasOrder orderAlias = new AqlAliasOrder();
AqlResultDateTime latestOrderDate = q.Select(Aql.Max(orderAlias.DateOrdered));
AqlResultDateTime firstOrderDate = q.Select(Aql.Min(orderAlias.DateOrdered));
q.From(orderAlias);
q.Where(orderAlias.SiteId == WAFContext.Session.SiteId);
q.Where(orderAlias.OrderStatus == (int)OrderStatus.Ordered | orderAlias.OrderStatus == (int)OrderStatus.Shipped);
AqlResultSet rs = q.Execute();
while (rs.Read()) {
lastOrder = latestOrderDate.Value;
firstOrder = firstOrderDate.Value;
}
TimeSpan ts = new TimeSpan();
ts = lastOrder.Subtract(firstOrder);
return monthDifference(firstOrder, lastOrder);
}