public OrdersByShipperResponse OrdersByShipper(IDbConnection connection, OrdersByShipperRequest request)
{
var fld = OrderRow.Fields;
var year = DateTime.Today.Year;
var startOfMonth = new DateTime(year, DateTime.Today.Month, 1);
var startingFrom = startOfMonth.AddMonths(-11);
var response = new OrdersByShipperResponse();
var shippers = connection.List <ShipperRow>(q => q.SelectTableFields().OrderBy(ShipperRow.Fields.CompanyName));
response.ShipperKeys = shippers.Select(x => "s" + x.ShipperID.Value).ToList();
response.ShipperLabels = shippers.Select(x => x.CompanyName).ToList();
var monthExpr = "DATEPART(MONTH, " + fld.OrderDate.Expression + ")";
var byMonth = connection.Query(
new SqlQuery()
.From(fld)
.Select(monthExpr, "Month")
.Select(Sql.Count(), "Count")
.Select(fld.ShipVia, "ShipVia")
.GroupBy(monthExpr)
.GroupBy(fld.ShipVia)
.Where(
fld.OrderDate.IsNotNull() &
fld.ShipVia.IsNotNull() &
fld.OrderDate < startOfMonth.AddMonths(1) &
fld.OrderDate >= startingFrom))
.ToDictionary(x => new Tuple <int, int>((int)x.Month, (int)x.ShipVia), x => (int)x.Count);
response.Values = new List <Dictionary <string, object> >();
var month = startingFrom.Month;
int mc;
for (var i = 0; i < 12; i++)
{
var d = new Dictionary <string, object>();
d["Month"] = new DateTime(1999, month, 1)
.ToString("MMM");
foreach (var p in shippers)
{
d["s" + p.ShipperID] = byMonth.TryGetValue(
new Tuple <int, int>(month, p.ShipperID.Value), out mc) ? mc : 0;
}
response.Values.Add(d);
if (++month > 12)
{
month = 1;
}
}
return(response);
}
public OrdersByShipperResponse OrdersByShipper(IDbConnection connection, OrdersByShipperRequest request)
{
var fld = OrderRow.Fields;
var year = 1998; // as Northwind data is old, this is hardcoded, use DateTime.Today.Year for other tables
var startOfMonth = new DateTime(year, DateTime.Today.Month, 1);
var startingFrom = startOfMonth.AddMonths(-11);
var response = new OrdersByShipperResponse();
var shippers = connection.List<ShipperRow>(q => q.SelectTableFields().OrderBy(ShipperRow.Fields.CompanyName));
response.ShipperKeys = shippers.Select(x => "s" + x.ShipperID.Value).ToList();
response.ShipperLabels = shippers.Select(x => x.CompanyName).ToList();
var monthExpr = "DATEPART(MONTH, " + fld.OrderDate.Expression + ")";
var byMonth = connection.Query(
new SqlQuery()
.From(fld)
.Select(monthExpr, "Month")
.Select(Sql.Count(), "Count")
.Select(fld.ShipVia, "ShipVia")
.GroupBy(monthExpr)
.GroupBy(fld.ShipVia)
.Where(
fld.OrderDate.IsNotNull() &
fld.OrderDate < startOfMonth.AddMonths(1) &
fld.OrderDate >= startingFrom))
.ToDictionary(x => new Tuple<int, int>(x.Month, x.ShipVia), x => (int)x.Count);
response.Values = new List<Dictionary<string, object>>();
var month = startingFrom.Month;
int mc;
for (var i = 0; i < 12; i++)
{
var d = new Dictionary<string, object>();
d["Month"] = new DateTime(1999, month, 1)
.ToString("MMM");
foreach (var p in shippers)
d["s" + p.ShipperID] = byMonth.TryGetValue(
new Tuple<int, int>(month, p.ShipperID.Value), out mc) ? mc : 0;
response.Values.Add(d);
if (++month > 12)
month = 1;
}
return response;
}
public static jQueryXmlHttpRequest OrdersByShipper(OrdersByShipperRequest request, Action <OrdersByShipperResponse> onSuccess, ServiceCallOptions options = null)
{
return(null);
}
public static jQueryXmlHttpRequest OrdersByShipper(OrdersByShipperRequest request, Action<OrdersByShipperResponse> onSuccess, ServiceCallOptions options = null)
{
return null;
}
