/// <summary>
/// Add pickslips to bucket
/// </summary>
/// <param name="bucketId"></param>
/// <param name="customerId"></param>
/// <param name="dimensions"></param>
/// <param name="vwhId"></param>
/// <param name="updateBucketName"></param>
public void AddPickslipsPerDim(int bucketId, string customerId, IList <Tuple <PickslipDimension, object> > dimensions, string vwhId, bool updateBucketName)
{
const string QUERY = @"
DECLARE
LBUCKET_NAME <proxy />BUCKET.NAME%TYPE;
CURSOR PICKSLIP_CURSOR IS
SELECT DEMPS.PICKSLIP_ID AS PICKSLIP_ID,
SUBSTR(C.NAME,1, 10) || ' ' || SUBSTR(DEMPS.CUSTOMER_ORDER_ID,1,9) || TO_CHAR(DEMPS.DC_CANCEL_DATE, ' MM/DD') AS BUCKET_NAME
FROM <proxy />DEM_PICKSLIP DEMPS
LEFT OUTER JOIN <proxy />MASTER_CUSTOMER C
ON C.CUSTOMER_ID = DEMPS.CUSTOMER_ID
WHERE DEMPS.PS_STATUS_ID = 1
AND DEMPS.CUSTOMER_ID = :CUSTOMER_ID
AND DEMPS.VWH_ID = :VWH_ID
AND {0};
PICKSLIP_COUNT BINARY_INTEGER := 0;
BEGIN
FOR PICKSLIP_REC IN PICKSLIP_CURSOR LOOP
PICKSLIP_COUNT := PICKSLIP_COUNT + 1;
<proxy />PKG_DATA_EXCHANGE.GET_PICKSLIP(PICKSLIP_REC.PICKSLIP_ID, :BUCKET_ID);
LBUCKET_NAME := PICKSLIP_REC.BUCKET_NAME;
END LOOP;
IF PICKSLIP_COUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20000, 'No pickslips were added');
END IF;
<if c='$updateBucketName'>
UPDATE <proxy />BUCKET SET NAME = LBUCKET_NAME WHERE BUCKET_ID = :BUCKET_ID;
</if>
END;";
var binder = SqlBinder.Create();
var bucket = new PickWave();
binder.Parameter("BUCKET_ID", bucketId)
.Parameter("CUSTOMER_ID", customerId)
.Parameter("VWH_ID", vwhId)
.ParameterXPath("updateBucketName", updateBucketName)
;
var attrs = PickWaveHelpers.GetEnumMemberAttributes <PickslipDimension, DataTypeAttribute>();
var clauses = new List <string>(2);
foreach (var dim in dimensions)
{
clauses.Add(GetDimensionWhereClause(dim.Item1, dim.Item2));
if (attrs.ContainsKey(dim.Item1) && attrs[dim.Item1].DataType == DataType.Date)
{
binder.Parameter(dim.Item1.ToString(), Convert.ToDateTime(dim.Item2));
}
else
{
binder.Parameter(dim.Item1.ToString(), Convert.ToString(dim.Item2));
}
}
var queryFinal = string.Format(QUERY, string.Join(" AND ", clauses));
_db.ExecuteDml(queryFinal, binder);
}
/// <summary>
/// Populates the passed PickslipMatrixPartialViewModel
/// </summary>
/// <param name="model"> </param>
/// <param name="customerId"> </param>
/// <param name="selectedRowDimIndex"> </param>
/// <param name="selectedColDimIndex"> </param>
/// <returns>Whether pickslips exist which can be added to a bucket</returns>
/// <remarks>
/// Passed selectedRowDimIndex and selectedColDimIndex are ignored if it turns out that either of them have no pickslips
/// </remarks>
private bool PopulatePickslipMatrixPartialModel(IndexViewModel model)
{
model.VwhList = (from item in _service.Value.GetVWhListOfCustomer(model.CustomerId)
select new SelectListItem
{
Text = item.VWhId + " : " + item.Description,
Value = item.VWhId,
Selected = item.VWhId == model.VwhId
}).ToList();
if (model.VwhList.Count == 0)
{
return(false);
}
if (string.IsNullOrWhiteSpace(model.VwhId))
{
// If Vwh has not been passed, use this one
model.VwhId = model.VwhList.Select(p => p.Value).First();
}
var summary = _service.Value.GetOrderSummary(model.CustomerId, model.VwhId, model.GroupDimIndex, model.SubgroupDimIndex);
if (summary.CountValuesPerDimension == null)
{
// No more pickslips which can be added to a bucket. The dimension matrix will be empty.
// This happens when the last set of pickslips has been added to the bucket.
return(false);
}
var requery = false;
if (summary.CountValuesPerDimension[model.GroupDimIndex] == 0)
{
model.GroupDimIndex = summary.CountValuesPerDimension.First(p => p.Value > 0).Key;
requery = true;
}
if (summary.CountValuesPerDimension[model.SubgroupDimIndex] == 0)
{
model.SubgroupDimIndex = summary.CountValuesPerDimension.First(p => p.Value > 0).Key;
requery = true;
}
if (requery)
{
summary = _service.Value.GetOrderSummary(model.CustomerId, model.VwhId, model.GroupDimIndex, model.SubgroupDimIndex);
}
model.GroupDimDisplayName = PickWaveHelpers.GetEnumMemberAttributes <PickslipDimension, DisplayAttribute>()[model.GroupDimIndex].Name;
model.SubgroupDimDisplayName = PickWaveHelpers.GetEnumMemberAttributes <PickslipDimension, DisplayAttribute>()[model.SubgroupDimIndex].Name;
const int MAX_COL_DIMENSIONS = 30;
model.GroupDimensionList = (from kvp in PickWaveHelpers.GetEnumMemberAttributes <PickslipDimension, DisplayAttribute>()
let count = summary.CountValuesPerDimension[kvp.Key]
select new SelectListItem
{
Value = count == 0 ? "" : ((int)(kvp.Key)).ToString(),
Text = string.Format("{0} ({1:N0})", kvp.Value.Name, count),
Selected = kvp.Key == model.GroupDimIndex
}).OrderBy(p => p.Text).ToArray();
// Dimensions which have too many distinct values are not displayed as column dimensions
model.SubgroupDimensionList = (from kvp in PickWaveHelpers.GetEnumMemberAttributes <PickslipDimension, DisplayAttribute>()
let count = summary.CountValuesPerDimension[kvp.Key]
where count <= MAX_COL_DIMENSIONS
select new SelectListItem
{
Value = count > MAX_COL_DIMENSIONS || count == 0 ? "" : ((int)(kvp.Key)).ToString(),
Text = string.Format("{0} ({1:N0})", kvp.Value.Name, count),
Selected = kvp.Key == model.SubgroupDimIndex
}).OrderBy(p => p.Text).ToArray();
//model.Rows = (from rowVal in summary.AllValues.RowValues
// let row = summary.AllValues.GetRow(rowVal)
// select new RowDimensionModel
// {
// PickslipCounts = row.ToDictionary(p => FormatDimensionValue(p.Key), p => p.Value.PickslipCount),
// OrderedPieces = row.ToDictionary(p => FormatDimensionValue(p.Key), p => p.Value.OrderedPieces),
// DimensionValue = FormatDimensionValue(rowVal)
// }).ToArray();
//model.Rows = (from rowVal in summary.AllValues3.FirstKeys
// //let row = summary.AllValues.GetRow(rowVal)
// select new RowDimensionModel
// {
// PickslipCounts = summary.AllValues3[rowVal].ToDictionary(p => FormatDimensionValue(p.Key), p => p.Value.PickslipCount),
// OrderedPieces = summary.AllValues3[rowVal].ToDictionary(p => FormatDimensionValue(p.Key), p => p.Value.OrderedPieces),
// DimensionValue = FormatDimensionValue(rowVal)
// }).ToArray();
var query = from item in summary.AllValues3
select Tuple.Create(DimensionValue.FromValue(item.Key.Item1),
DimensionValue.FromValue(item.Key.Item2),
new DimensionValueModel(item.Value));
model.DimensionMatrix = new SparseMatrix <DimensionValue, DimensionValue, DimensionValueModel>();
model.DimensionMatrix.AddRange(query);
//var list = new List<RowDimensionModel>();
//foreach (var rowVal in summary.AllValues2.RowValues())
//{
// var x = new RowDimensionModel
// {
// PickslipCounts = summary.AllValues2.PickslipCounts(rowVal),
// OrderedPieces = summary.AllValues2.OrderedPieces(rowVal),
// DimensionValue = SparseMatrix.FormatValue(rowVal)
// };
// list.Add(x);
//}
//model.ColDimensionValues = summary.AllValues.ColValues.Select(p => FormatDimensionValue(p)).ToArray();
return(true);
}
/// <summary>
/// Returns the pickslips of specified customer from dem_pickslip
/// </summary>
/// <param name="customerId"> </param>
/// <param name="dimensions"> </param>
/// <returns></returns>
public IEnumerable <Pickslip> GetPickslips(string customerId, string vwhId, IList <Tuple <PickslipDimension, object> > dimensions)
{
if (string.IsNullOrEmpty(customerId))
{
throw new ArgumentNullException("customerId");
}
const string QUERY = @"
SELECT DEMPS.PICKSLIP_ID AS PICKSLIP_ID,
DEMPS.DC_CANCEL_DATE AS DC_CANCEL_DATE,
DEMPS.CANCEL_DATE AS CANCEL_DATE,
DEMPS.Delivery_Date AS Delivery_Date,
DEMPS.PICKSLIP_IMPORT_DATE AS PICKSLIP_IMPORT_DATE,
DEMPS.CUSTOMER_ORDER_ID AS CUSTOMER_ORDER_ID,
DEMPS.CUSTOMER_ID AS CUSTOMER_ID,
DEMPS.CUSTOMER_STORE_ID AS CUSTOMER_STORE_ID,
DEMPS.CUSTOMER_DIST_CENTER_ID AS CUSTOMER_DIST_CENTER_ID
FROM <proxy />DEM_PICKSLIP DEMPS
WHERE DEMPS.PS_STATUS_ID = 1
AND DEMPS.CUSTOMER_ID = :CUSTOMER_ID
AND DEMPS.VWH_ID = :VWH_ID
AND {0}
ORDER BY DEMPS.PRIORITY_ID, DEMPS.PICKSLIP_ID DESC";
var binder = SqlBinder.Create(row => new Pickslip
{
PickslipId = row.GetLong("PICKSLIP_ID").Value,
DcCancelDate = row.GetDate("DC_CANCEL_DATE"),
CancelDate = row.GetDate("CANCEL_DATE"),
StartDate = row.GetDate("Delivery_Date"),
PickslipImportDate = row.GetDate("PICKSLIP_IMPORT_DATE"),
PurchaseOrder = row.GetString("CUSTOMER_ORDER_ID"),
CustomerId = row.GetString("CUSTOMER_ID"),
CustomerStoreId = row.GetString("CUSTOMER_STORE_ID"),
CustomerDcId = row.GetString("CUSTOMER_DIST_CENTER_ID")
}).Parameter("CUSTOMER_ID", customerId)
.Parameter("VWH_ID", vwhId);
var attrs = PickWaveHelpers.GetEnumMemberAttributes <PickslipDimension, DataTypeAttribute>();
var clauses = new List <string>(2);
foreach (var dim in dimensions)
{
if (dim.Item2 == null)
{
throw new ArgumentNullException("dimensions[i].Item2");
}
clauses.Add(GetDimensionWhereClause(dim.Item1, dim.Item2));
if (attrs.ContainsKey(dim.Item1) && attrs[dim.Item1].DataType == DataType.Date)
{
binder.Parameter(dim.Item1.ToString(), Convert.ToDateTime(dim.Item2));
}
else
{
// Sharad 16 Dec 2014: Trimming the value because we get leading spaces in priority once in a while
binder.Parameter(dim.Item1.ToString(), Convert.ToString(dim.Item2).Trim());
}
}
var queryFinal = string.Format(QUERY, string.Join(" AND ", clauses));
return(_db.ExecuteReader(queryFinal, binder, 2000));
}
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));
}
