예제 #1
0
 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;
 }
예제 #2
0
 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;
 }
예제 #3
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;
 }
예제 #4
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;
    }
예제 #5
0
    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();
        }
    }
예제 #6
0
    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();
        }
    }
예제 #7
0
 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);
 }