public virtual ActionResult AddPickslipsOfDim(int?bucketId, string customerId,
PickslipDimension groupDimIndex, DimensionValue groupDimVal,
PickslipDimension subgroupDimIndex, DimensionValue subgroupDimVal,
string vwhId)
{
if (string.IsNullOrWhiteSpace(customerId))
{
throw new ArgumentNullException("customerId");
}
if (string.IsNullOrWhiteSpace(vwhId))
{
throw new ArgumentNullException("vwhId");
}
using (var trans = _service.Value.BeginTransaction())
{
// If bucket is not created.create it first.
if (bucketId == null)
{
//Now Create bucket
bucketId = _service.Value.CreateDefaultWave();
AddStatusMessage(string.Format("Pick Wave {0} created.", bucketId));
}
// Add pickslip to bucket
_service.Value.AddPickslipsPerDim(bucketId.Value, customerId,
groupDimIndex,
groupDimVal, subgroupDimIndex, subgroupDimVal, vwhId);
trans.Commit();
}
AddStatusMessage(string.Format("Pickslips added to Pick Wave {0}", bucketId));
return(RedirectToAction(Actions.Index(customerId, groupDimIndex, subgroupDimIndex,
vwhId, bucketId, groupDimVal)));
}
public void AddPickslipsPerDim(int bucketId, string customerId,
PickslipDimension dimRow, DimensionValue dimRowVal,
PickslipDimension dimCol, DimensionValue dimColVal, string vwhId)
{
_repos.AddPickslipsPerDim(bucketId, customerId, new[] {
Tuple.Create(dimRow, dimRowVal),
Tuple.Create(dimCol, dimColVal)
}, vwhId, false);
}
public virtual ActionResult AddPickslipsToBucket(int?bucketId, long[] pickslips, string customerId, PickslipDimension groupDimIndex,
DimensionValue GroupDimVal, PickslipDimension subgroupDimIndex, DimensionValue subgroupDimVal, string vwhId)
{
if (pickslips == null || pickslips.Length == 0)
{
AddStatusMessage("Please select pickslip.");
}
else
{
using (var trans = _service.Value.BeginTransaction())
{
if (bucketId == null)
{
bucketId = _service.Value.CreateDefaultWave();
AddStatusMessage(string.Format("New PickWave {1} created", pickslips.Length, bucketId));
}
_service.Value.AddPickslipsToWave(bucketId.Value, pickslips);
trans.Commit();
AddStatusMessage(string.Format("{0} pickslips have been added to PickWave {1}", pickslips.Length, bucketId));
}
}
return(RedirectToAction(this.Actions.PickslipList(customerId, groupDimIndex, GroupDimVal, subgroupDimIndex, subgroupDimVal, vwhId, bucketId)));
}
/// <summary>
/// For the passed customer and vwh, groups results by col1 and returns a row for each unique value of col1.
/// Each row contains an array of pickslip counts for each unique value of col2
/// </summary>
/// <param name="customerId">Supmmary of pickslip counts and pieces ordered are required for this customer</param>
/// <param name="vwhId">Consider only those orders of this vwhId</param>
/// <param param name="col1">First Group by column</param>
/// <param name="col2">Second Group by column</param>
/// <returns>Item1 is a list of rows for each unique value of col1. Item2 is number of pickslips per dimension</returns>
/// <remarks>
/// If too many rows for the dimension are returned, then null is returned. If no rows are returned for the dimension, and empty collection is returned.
/// Thus null is different from empty.
/// </remarks>
internal CustomerOrderSummary GetOrderSummaryForCustomer(string customerId, string vwhId, PickslipDimension col1, PickslipDimension col2)
{
if (string.IsNullOrWhiteSpace(customerId))
{
throw new ArgumentNullException("customerId");
}
if (string.IsNullOrWhiteSpace(vwhId))
{
throw new ArgumentNullException("vwhId");
}
if (col1 == PickslipDimension.NotSet)
{
throw new ArgumentOutOfRangeException("col1");
}
if (col2 == PickslipDimension.NotSet)
{
throw new ArgumentOutOfRangeException("col2");
}
var dimMap = new Dictionary <PickslipDimension, Tuple <string, Type> >
{
{ PickslipDimension.Priority, Tuple.Create("LPAD(T.PRIORITY_ID, 10)", typeof(string)) },
{ PickslipDimension.CustomerStore, Tuple.Create("T.CUSTOMER_STORE_ID", typeof(string)) },
{ PickslipDimension.CustomerDcCancelDate, Tuple.Create("TRUNC(T.DC_CANCEL_DATE)", typeof(DateTime)) },
{ PickslipDimension.Label, Tuple.Create("T.PICKSLIP_TYPE", typeof(string)) },
{ PickslipDimension.ImportDate, Tuple.Create("TRUNC(T.PICKSLIP_IMPORT_DATE)", typeof(DateTime)) },
{ PickslipDimension.StartDate, Tuple.Create("TRUNC(T.DELIVERY_DATE)", typeof(DateTime)) },
{ PickslipDimension.CancelDate, Tuple.Create("TRUNC(T.CANCEL_DATE)", typeof(DateTime)) },
{ PickslipDimension.CustomerOrderType, Tuple.Create("t.CUSTOMER_ORDER_TYPE", typeof(string)) },
{ PickslipDimension.SaleTypeId, Tuple.Create("T.SALES_TYPE_ID", typeof(string)) },
{ PickslipDimension.PurchaseOrder, Tuple.Create("T.CUSTOMER_ORDER_ID", typeof(string)) },
{ PickslipDimension.CustomerDc, Tuple.Create("T.CUSTOMER_DIST_CENTER_ID", typeof(string)) }
};
const string QUERY = @"
WITH Q1 AS
(SELECT t.PICKSLIP_ID,
T.TOTAL_QUANTITY_ORDERED,
{0} AS PICKSLIP_DIMENSION,
{1} AS DIM_COL,
{2}
FROM <proxy />DEM_PICKSLIP T
WHERE T.PS_STATUS_ID = 1
AND T.CUSTOMER_ID = :CUSTOMER_ID
AND T.VWH_ID = :VWH_ID)
SELECT PICKSLIP_DIMENSION, {3}, CAST(DIM_COL_XML AS VARCHAR2(4000)) AS DIM_COL_XML
FROM Q1 PIVOT XML(COUNT(PICKSLIP_ID) AS PICKSLIP_COUNT,SUM(Q1.TOTAL_QUANTITY_ORDERED) AS ORDER_COUNT FOR DIM_COL IN(ANY))
ORDER BY PICKSLIP_DIMENSION
";
var query = string.Format(QUERY,
dimMap[col1].Item1, //{0}
dimMap[col2].Item1, //{1}
string.Join(", ", dimMap.Select(p => string.Format("COUNT(UNIQUE {0}) OVER() AS {1}", p.Value.Item1, p.Key.ToString()))), //{2}
string.Join(", ", dimMap.Select(p => p.Key.ToString())) //{3}
);
/* The value of query will look something like this
* WITH Q1 AS
* (SELECT t.PICKSLIP_ID,
* T.TOTAL_QUANTITY_ORDERED,
* TRUNC(T.DC_CANCEL_DATE) AS PICKSLIP_DIMENSION,
* T.CUSTOMER_DIST_CENTER_ID AS DIM_COL,
* COUNT(UNIQUE LPAD(T.PRIORITY_ID, 10)) OVER() AS Priority,
* COUNT(UNIQUE T.CUSTOMER_STORE_ID) OVER() AS CustomerStore,
* COUNT(UNIQUE TRUNC(T.DC_CANCEL_DATE)) OVER() AS CustomerDcCancelDate,
* COUNT(UNIQUE T.PICKSLIP_TYPE) OVER() AS Label,
* COUNT(UNIQUE TRUNC(T.PICKSLIP_IMPORT_DATE)) OVER() AS ImportDate,
* COUNT(UNIQUE TRUNC(T.DELIVERY_DATE)) OVER() AS StartDate,
* COUNT(UNIQUE TRUNC(T.CANCEL_DATE)) OVER() AS CancelDate,
* COUNT(UNIQUE t.CUSTOMER_ORDER_TYPE) OVER() AS CustomerOrderType,
* COUNT(UNIQUE T.SALES_TYPE_ID) OVER() AS SaleTypeId,
* COUNT(UNIQUE T.CUSTOMER_ORDER_ID) OVER() AS PurchaseOrder,
* COUNT(UNIQUE T.CUSTOMER_DIST_CENTER_ID) OVER() AS CustomerDc
* FROM DEM_PICKSLIP T
* WHERE T.PS_STATUS_ID = 1
* AND T.CUSTOMER_ID = :CUSTOMER_ID
* AND T.VWH_ID = :VWH_ID)
* SELECT *
* FROM Q1 PIVOT XML(COUNT(PICKSLIP_ID) AS PICKSLIP_COUNT, SUM(Q1.TOTAL_QUANTITY_ORDERED) AS ORDER_COUNT FOR DIM_COL IN(ANY))
* ORDER BY PICKSLIP_DIMENSION
*/
var result = new CustomerOrderSummary();
var binder = SqlBinder.Create(row =>
{
if (result.CountValuesPerDimension == null)
{
result.CountValuesPerDimension = new Dictionary <PickslipDimension, int>();
foreach (var item in dimMap)
{
result.CountValuesPerDimension.Add(item.Key, row.GetInteger(item.Key.ToString()) ?? 0);
}
}
return(MapOrderSummaryXml3(dimMap[col1].Item2 == typeof(DateTime) ? (object)row.GetDate("pickslip_dimension") : (object)row.GetString("pickslip_dimension"),
row.GetString("DIM_COL_XML"), dimMap[col2].Item2 == typeof(DateTime)));
});
binder.Parameter("CUSTOMER_ID", customerId)
.Parameter("VWH_ID", vwhId)
;
var rows = _db.ExecuteReader(query, binder);
result.AllValues3.AddRange(rows.SelectMany(p => p));
return(result);
}
/// <summary>
/// Returns the where clause corresponding to the passed dimension
/// </summary>
/// <param name="dim"></param>
/// <param name="dimVal"></param>
/// <returns></returns>
/// <remarks>
/// The where clause assumes that the alias of the dem_pickslip table is DEMPS.
/// </remarks>
private string GetDimensionWhereClause(PickslipDimension dim, object dimVal)
{
if (dimVal == null)
{
throw new ArgumentNullException("dimVal");
}
string clause;
switch (dim)
{
case PickslipDimension.Priority:
clause = "DEMPS.PRIORITY_ID = :{0}";
break;
case PickslipDimension.CustomerStore:
clause = "DEMPS.CUSTOMER_STORE_ID = :{0}";
break;
case PickslipDimension.Label:
clause = "DEMPS.Pickslip_Type = :{0}";
break;
case PickslipDimension.ImportDate:
clause = "(DEMPS.PICKSLIP_IMPORT_DATE >= TRUNC(CAST(:{0} AS DATE)) AND DEMPS.PICKSLIP_IMPORT_DATE < TRUNC(CAST(:{0} AS DATE)) + 1)";
break;
case PickslipDimension.StartDate:
clause = "(DEMPS.DELIVERY_DATE >= TRUNC(CAST(:{0} AS DATE)) AND DEMPS.DELIVERY_DATE < TRUNC(CAST(:{0} AS DATE)) + 1)";
break;
case PickslipDimension.CancelDate:
clause = "(DEMPS.CANCEL_DATE >= TRUNC(CAST(:{0} AS DATE)) AND DEMPS.CANCEL_DATE < TRUNC(CAST(:{0} AS DATE)) + 1)";
break;
case PickslipDimension.CustomerOrderType:
clause = "DEMPS.CUSTOMER_ORDER_TYPE = :{0}";
break;
case PickslipDimension.SaleTypeId:
clause = "DEMPS.SALES_TYPE_ID = :{0}";
break;
case PickslipDimension.PurchaseOrder:
clause = "DEMPS.CUSTOMER_ORDER_ID = :{0}";
break;
case PickslipDimension.CustomerDcCancelDate:
clause = "(DEMPS.DC_CANCEL_DATE >= TRUNC(CAST(:{0} AS DATE)) AND DEMPS.DC_CANCEL_DATE < TRUNC(CAST(:{0} AS DATE)) + 1)";
break;
case PickslipDimension.CustomerDc:
clause = "DEMPS.CUSTOMER_DIST_CENTER_ID = :{0}";
break;
default:
throw new NotImplementedException();
}
clause = string.Format(clause, dim.ToString());
return(clause);
}
/// <summary>
/// Special handling priority to provide numeric sort
/// </summary>
/// <param name="customerId"></param>
/// <param name="vwhId"> </param>
/// <param name="dimRow"> </param>
/// <param name="dimCol"> </param>
/// <returns></returns>
/// <remarks>
/// If too many rows for the dimension are returned, then null is returned. If no rows are returned for the dimension, and empty collection is returned.
/// Thus null is different from empty.
/// </remarks>
internal CustomerOrderSummary GetOrderSummary(string customerId, string vwhId, PickslipDimension dimRow, PickslipDimension dimCol)
{
return(_repos.GetOrderSummaryForCustomer(customerId, vwhId, dimRow, dimCol));
}
public IEnumerable <Pickslip> GetPickslipList(string customerId, string vwhId, PickslipDimension dimRow, string dimRowVal, PickslipDimension dimCol, string dimColVal)
{
return(_repos.GetPickslips(customerId, vwhId, new[] { Tuple.Create(dimRow, (object)dimRowVal), Tuple.Create(dimCol, (object)dimColVal) }));
}
public virtual ActionResult PickslipList(
[Required]
string customerId,
PickslipDimension groupDimIndex,
[Required]
DimensionValue groupDimVal,
PickslipDimension subgroupDimIndex,
[Required]
DimensionValue subgroupDimVal,
[Required]
string vwhId,
int?bucketId)
{
if (string.IsNullOrWhiteSpace(customerId))
{
throw new ArgumentNullException("customerId");
}
if (string.IsNullOrWhiteSpace(vwhId))
{
throw new ArgumentNullException("vwhId");
}
//if (string.IsNullOrWhiteSpace(groupDimVal))
//{
// throw new ArgumentNullException("rowDimVal");
//}
//if (string.IsNullOrWhiteSpace(subgroupDimVal))
//{
// throw new ArgumentNullException("colDimVal");
//}
var model = new PickslipListViewModel();
model.CustomerId = customerId;
model.GroupDimIndex = groupDimIndex;
model.SubgroupDimIndex = subgroupDimIndex;
model.VwhId = vwhId;
model.GroupDimVal = groupDimVal;
model.SubgroupDimVal = subgroupDimVal;
// Pickslip list of passed dimension.
var pickslips = _service.Value.GetPickslipList(model.CustomerId, model.VwhId, model.GroupDimIndex, model.GroupDimVal, model.SubgroupDimIndex, model.SubgroupDimVal);
model.PickslipList = (from pickslip in pickslips
let routePickslip = Url.RouteCollection[DcmsLibrary.Mvc.PublicRoutes.DcmsConnect_SearchPickslipImported1]
// let routePickslip = Url.RouteCollection[DcmsLibrary.Mvc.PublicRoutes.DcmsConnect_SearchPickslip1]
let routePo = Url.RouteCollection[DcmsLibrary.Mvc.PublicRoutes.DcmsConnect_SearchPoImported3]
select new CreateWavePickslipModel(pickslip)
{
UrlInquiryPickslip = routePickslip == null ? null : Url.RouteUrl(DcmsLibrary.Mvc.PublicRoutes.DcmsConnect_SearchPickslipImported1, new
{
id = pickslip.PickslipId
}),
UrlInquiryPurchaseOrder = routePo == null ? null : Url.RouteUrl(DcmsLibrary.Mvc.PublicRoutes.DcmsConnect_SearchPoImported3, new
{
id = pickslip.PurchaseOrder,
pk1 = pickslip.CustomerId,
pk2 = pickslip.Iteration
})
}).ToArray();
model.ManagerRoleName = ROLE_WAVE_MANAGER;
model.GroupDimDisplayName = PickWaveHelpers.GetEnumMemberAttributes <PickslipDimension, DisplayAttribute>()[model.GroupDimIndex].Name;
model.SubgroupDimDisplayName = PickWaveHelpers.GetEnumMemberAttributes <PickslipDimension, DisplayAttribute>()[model.SubgroupDimIndex].Name;
model.GroupDimVal = model.GroupDimVal;
model.CustomerName = _service.Value.GetCustomerName(customerId);
model.BucketId = bucketId;
if (model.BucketId.HasValue)
{
var bucket = _service.Value.GetBucket(model.BucketId.Value);
model.Bucket = new BucketModel(bucket, _service.Value.GetCustomerName(customerId), BucketModelFlags.Default);
}
return(View(Views.PickslipList, model));
}
public virtual ActionResult Index(string customerId,
PickslipDimension groupDimIndex = PickslipDimension.NotSet,
PickslipDimension subgroupDimIndex = PickslipDimension.NotSet,
string vwhId = null,
int?lastBucketId = null,
DimensionValue groupDimVal = default(DimensionValue))
{
if (string.IsNullOrWhiteSpace(customerId))
{
throw new NotImplementedException();
}
var model = new IndexViewModel
{
GroupDimVal = groupDimVal
};
//Showing only those area where order of customer are available.
//var areas = _service.Value.GetAreasForCustomer(customerId);
#region Manage Cookie
// SelectedDimension : If null, then a reasonable default is used. The default is cookie => factory default
// If non null, then it is used and written to a cookie.
var cookie = Request.Cookies[COOKIE_PICKWAVE];
if (groupDimIndex == PickslipDimension.NotSet || subgroupDimIndex == PickslipDimension.NotSet)
{
// Read cookie
int dimRowIndex, dimColIndex;
if (cookie != null && cookie[COOKIE_ROW_DIMENSION] != null && int.TryParse(cookie[COOKIE_ROW_DIMENSION], out dimRowIndex) &&
int.TryParse(cookie[COOKIE_COL_DIMENSION], out dimColIndex))
{
model.GroupDimIndex = (PickslipDimension)dimRowIndex;
model.SubgroupDimIndex = (PickslipDimension)dimColIndex;
}
else
{
// Factory defaults
model.GroupDimIndex = PickslipDimension.CustomerDcCancelDate;
model.SubgroupDimIndex = PickslipDimension.CustomerDc;
}
}
else
{
model.GroupDimIndex = groupDimIndex;
model.SubgroupDimIndex = subgroupDimIndex;
// Write cookie
if (cookie == null)
{
cookie = new HttpCookie(COOKIE_PICKWAVE)
{
Expires = DateTime.Now.AddDays(7)
};
cookie.Values.Add(COOKIE_ROW_DIMENSION, model.GroupDimIndex.ToString());
cookie.Values.Add(COOKIE_COL_DIMENSION, model.SubgroupDimIndex.ToString());
}
else
{
if (cookie[COOKIE_ROW_DIMENSION] != null)
{
cookie.Values.Set(COOKIE_ROW_DIMENSION, model.GroupDimIndex.ToString());
}
if (cookie[COOKIE_COL_DIMENSION] != null)
{
cookie.Values.Set(COOKIE_COL_DIMENSION, model.SubgroupDimIndex.ToString());
}
}
HttpContext.Response.Cookies.Add(cookie);
}
#endregion
model.CustomerId = customerId;
//var cust = _service.Value.GetCustomer(model.CustomerId);
model.CustomerName = _service.Value.GetCustomerName(model.CustomerId);
// Make sure that selected row and dimension are within the bounds of their respective drop downs
if (!PopulatePickslipMatrixPartialModel(model))
{
AddStatusMessage("No more pickslips available to add");
if (lastBucketId.HasValue)
{
return(RedirectToAction(MVC_PickWaves.PickWaves.ManageWaves.WavePickslips(lastBucketId.Value)));
}
else
{
return(RedirectToAction(Actions.Index(customerId, groupDimIndex, subgroupDimIndex, vwhId, lastBucketId, groupDimVal)));
}
}
// If Bucket is created.
if (lastBucketId.HasValue)
{
// Retrive some information of bucket.
model.LastBucketId = lastBucketId;
var bucket = _service.Value.GetPickWave(model.LastBucketId.Value);
if (bucket != null)
{
model.PullAreaShortName = bucket.PullAreaShortName;
model.PitchAreaShortName = bucket.PitchAreaShortName;
model.PickslipCount = bucket.PickslipCount;
}
}
return(View(Views.Index, model));
}