/// <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)); }