Exemplo n.º 1
0
        protected virtual IEnumerable internalResultRecords()
        {
            InventoryTranHistEnqFilter filter = Filter.Current;

            bool summaryByDay      = filter.SummaryByDay ?? false;
            bool includeUnreleased = filter.IncludeUnreleased ?? false;

            var resultList = new List <PXResult <InventoryTranHistEnqResult, INTran, INTranSplit> >();

            if (filter.InventoryID == null)
            {
                PXDelegateResult emptyResult = new PXDelegateResult();
                emptyResult.IsResultFiltered  = true;
                emptyResult.IsResultSorted    = true;
                emptyResult.IsResultTruncated = true;

                return(emptyResult);                 //empty
            }

            PXSelectBase <INTranSplit> cmd = new PXSelectReadonly2 <INTranSplit,
                                                                    InnerJoin <INTran, On <INTranSplit.FK.Tran>,
                                                                               InnerJoin <INSubItem, On <INTranSplit.FK.SubItem>,
                                                                                          InnerJoin <INSite, On <INTran.FK.Site> > > >,
                                                                    Where <INTranSplit.inventoryID, Equal <Current <InventoryTranHistEnqFilter.inventoryID> >, And <Match <INSite, Current <AccessInfo.userName> > > >,
                                                                    OrderBy <Asc <INTranSplit.docType, Asc <INTranSplit.refNbr, Asc <INTranSplit.lineNbr, Asc <INTranSplit.splitLineNbr> > > > > >(this);

            PXSelectBase <INItemSiteHistByDay> cmdBegBalanceNew = new PXSelectReadonly2 <INItemSiteHistByDay,
                                                                                         InnerJoin <INItemSiteHistDay,
                                                                                                    On <INItemSiteHistDay.inventoryID, Equal <INItemSiteHistByDay.inventoryID>,
                                                                                                        And <INItemSiteHistDay.siteID, Equal <INItemSiteHistByDay.siteID>,
                                                                                                             And <INItemSiteHistDay.subItemID, Equal <INItemSiteHistByDay.subItemID>,
                                                                                                                  And <INItemSiteHistDay.locationID, Equal <INItemSiteHistByDay.locationID>,
                                                                                                                       And <INItemSiteHistDay.sDate, Equal <INItemSiteHistByDay.lastActivityDate> > > > > >,
                                                                                                    InnerJoin <INSubItem,
                                                                                                               On <INSubItem.subItemID, Equal <INItemSiteHistByDay.subItemID> >,
                                                                                                               InnerJoin <INSite, On <INSite.siteID, Equal <INItemSiteHistByDay.siteID> > > > >,
                                                                                         Where <INItemSiteHistByDay.inventoryID, Equal <Current <InventoryTranHistEnqFilter.inventoryID> >,
                                                                                                And <INItemSiteHistByDay.date, Equal <Required <INItemSiteHistByDay.date> >,
                                                                                                     And <Match <INSite, Current <AccessInfo.userName> > > > > >(this);

            if (!SubCDUtils.IsSubCDEmpty(filter.SubItemCD) && PXAccess.FeatureInstalled <FeaturesSet.subItem>())
            {
                cmd.WhereAnd <Where <INSubItem.subItemCD, Like <Current <InventoryTranHistEnqFilter.subItemCDWildcard> > > >();
                cmdBegBalanceNew.WhereAnd <Where <INSubItem.subItemCD, Like <Current <InventoryTranHistEnqFilter.subItemCDWildcard> > > >();
            }

            if (filter.SiteID != null && PXAccess.FeatureInstalled <FeaturesSet.warehouse>())
            {
                cmd.WhereAnd <Where <INTranSplit.siteID, Equal <Current <InventoryTranHistEnqFilter.siteID> > > >();
                cmdBegBalanceNew.WhereAnd <Where <INItemSiteHistByDay.siteID, Equal <Current <InventoryTranHistEnqFilter.siteID> > > >();
            }

            if ((filter.LocationID ?? -1) != -1 && PXAccess.FeatureInstalled <FeaturesSet.warehouseLocation>())        // there are cases when filter.LocationID = -1
            {
                cmd.WhereAnd <Where <INTranSplit.locationID, Equal <Current <InventoryTranHistEnqFilter.locationID> > > >();
                cmdBegBalanceNew.WhereAnd <Where <INItemSiteHistByDay.locationID, Equal <Current <InventoryTranHistEnqFilter.locationID> > > >();
            }

            if ((filter.LotSerialNbr ?? "") != "" && PXAccess.FeatureInstalled <FeaturesSet.lotSerialTracking>())
            {
                cmd.WhereAnd <Where <INTranSplit.lotSerialNbr, Like <Current <InventoryTranHistEnqFilter.lotSerialNbrWildcard> > > >();
            }

            if (!includeUnreleased)
            {
                cmd.WhereAnd <Where <INTranSplit.released, Equal <True> > >();
            }

            decimal cumulativeQty = 0m;

            if (filter.StartDate != null)
            {
                foreach (PXResult <INItemSiteHistByDay, INItemSiteHistDay> res in cmdBegBalanceNew.Select(filter.StartDate))
                {
                    INItemSiteHistByDay byday = res;
                    INItemSiteHistDay   hist  = res;

                    cumulativeQty +=
                        ((byday.LastActivityDate != null && byday.Date != null &&
                          byday.Date.Value.Date == byday.LastActivityDate.Value.Date)
                                            ? hist.BegQty
                                            : hist.EndQty) ?? 0m;
                }

                if (includeUnreleased)
                {
                    INSite site = INSite.PK.Find(this, filter.SiteID);

                    int calendarOrganizationID = PXAccess.GetParentOrganizationID(site?.BranchID) ?? FinPeriod.organizationID.MasterValue;

                    string   TranPeriodID;
                    DateTime?PeriodStartDate;

                    try
                    {
                        TranPeriodID    = FinPeriodRepository.GetPeriodIDFromDate(filter.StartDate, calendarOrganizationID);
                        PeriodStartDate = FinPeriodRepository.PeriodStartDate(TranPeriodID, calendarOrganizationID);
                    }
                    catch (PXFinPeriodException)
                    {
                        TranPeriodID    = null;
                        PeriodStartDate = filter.StartDate;
                    }

                    PXSelectBase <OrganizationFinPeriod> periodCmd =
                        new PXSelectGroupBy <OrganizationFinPeriod,
                                             Where <OrganizationFinPeriod.finPeriodID, LessEqual <Required <OrganizationFinPeriod.finPeriodID> >,
                                                    And <OrganizationFinPeriod.iNClosed, Equal <False>,
                                                         Or <OrganizationFinPeriod.finPeriodID, Equal <Required <OrganizationFinPeriod.finPeriodID> > > > >,
                                             Aggregate <GroupBy <OrganizationFinPeriod.finPeriodID> >,
                                             OrderBy <Asc <OrganizationFinPeriod.finPeriodID> > >(this);

                    List <object> periodCmdParams = new List <object>()
                    {
                        TranPeriodID, TranPeriodID
                    };


                    if (calendarOrganizationID != FinPeriod.organizationID.MasterValue)
                    {
                        periodCmd.WhereAnd <Where <OrganizationFinPeriod.organizationID, Equal <Required <OrganizationFinPeriod.organizationID> > > >();

                        periodCmdParams.Add(calendarOrganizationID);
                    }

                    OrganizationFinPeriod firstOpenOrCurrentClosedPeriod = periodCmd.SelectWindowed(0, 1, periodCmdParams.ToArray());

                    if (firstOpenOrCurrentClosedPeriod != null)
                    {
                        TranPeriodID    = firstOpenOrCurrentClosedPeriod.FinPeriodID;
                        PeriodStartDate = FinPeriodRepository.PeriodStartDate(firstOpenOrCurrentClosedPeriod.FinPeriodID, calendarOrganizationID);

                        PXView v2 = new PXView(this, true, cmd.View.BqlSelect
                                               .WhereAnd <Where <INTranSplit.tranDate, GreaterEqual <Required <INTranSplit.tranDate> > > >()
                                               .WhereAnd <Where <INTranSplit.tranDate, Less <Required <INTranSplit.tranDate> > > >()
                                               .WhereAnd <Where <INTranSplit.released, Equal <False> > >()
                                               .AggregateNew <Aggregate <
                                                                  GroupBy <INTranSplit.inventoryID, GroupBy <INTranSplit.invtMult, Sum <INTranSplit.baseQty> > > > >());

                        int splitStartRow  = 0;
                        int splitTotalRows = 0;

                        foreach (PXResult <INTranSplit> res in v2.Select(new object[0], new object[] { PeriodStartDate, filter.StartDate.Value }, new object[0],
                                                                         new string[0], new bool[0], new PXFilterRow[0], ref splitStartRow, 0, ref splitTotalRows))
                        {
                            INTranSplit tsRec = res;
                            cumulativeQty += (tsRec.InvtMult * tsRec.BaseQty) ?? 0m;
                        }
                    }
                }
            }

            if (filter.StartDate != null)
            {
                cmd.WhereAnd <Where <INTranSplit.tranDate, GreaterEqual <Current <InventoryTranHistEnqFilter.startDate> > > >();
            }

            if (filter.EndDate != null)
            {
                cmd.WhereAnd <Where <INTranSplit.tranDate, LessEqual <Current <InventoryTranHistEnqFilter.endDate> > > >();
            }

            AlterSortsAndFilters(out string[] newSortColumns, out bool[] newDescendings, out bool sortsChanged, out PXFilterRow[] newFilters, out bool filtersChanged);

            //if user clicks last, sorts will be inverted
            //as it is not possible to calculate beginning balance from the end
            //we will select without top from the start and then apply reverse order and select top n records
            //for next page we will ommit startrow to set beginning balance correctly
            //top (n, m) will be set in the outer search results since we do not reset PXView.StartRow to 0
            //Also, user can set a filter or sort by a column from result DAC (sortsChanged = true, filtersChanged = true) and need to get full result set.
            int  startRow           = 0;
            bool allowSelectWithTop = !sortsChanged && !filtersChanged && !PXView.ReverseOrder;
            int  maximumRows        = allowSelectWithTop ? PXView.StartRow + PXView.MaximumRows : 0;
            int  totalRows          = 0;

            PXView selectView = !summaryByDay
                            ? cmd.View
                            : new PXView(this, true,
                                         cmd.View.BqlSelect
                                         .AggregateNew <Aggregate <GroupBy <INTranSplit.tranDate, Sum <INTranSplit.qtyIn, Sum <INTranSplit.qtyOut> > > > >());

            List <object> intermediateResult = selectView.Select(PXView.Currents, new object[] { filter.StartDate },
                                                                 new string[newSortColumns.Length], newSortColumns, newDescendings, newFilters, ref startRow, maximumRows,
                                                                 ref totalRows);

            int gridLineNbr = 0;

            foreach (PXResult <INTranSplit, INTran, INSubItem> it in intermediateResult)
            {
                INTranSplit ts_rec = (INTranSplit)it;
                INTran      t_rec  = (INTran)it;

                if (summaryByDay)
                {
                    InventoryTranHistEnqResult item = new InventoryTranHistEnqResult();
                    item.BegQty      = cumulativeQty;
                    item.TranDate    = ts_rec.TranDate;
                    item.QtyIn       = ts_rec.QtyIn;
                    item.QtyOut      = ts_rec.QtyOut;
                    item.EndQty      = item.BegQty + ts_rec.QtyIn - ts_rec.QtyOut;
                    item.GridLineNbr = ++gridLineNbr;
                    resultList.Add(new PXResult <InventoryTranHistEnqResult, INTran, INTranSplit>(item, null, null));
                    cumulativeQty += (ts_rec.QtyIn - ts_rec.QtyOut) ?? 0m;
                }
                else
                {
                    InventoryTranHistEnqResult item = new InventoryTranHistEnqResult();
                    item.BegQty   = cumulativeQty;
                    item.TranDate = ts_rec.TranDate;
                    item.QtyIn    = ts_rec.QtyIn;
                    item.QtyOut   = ts_rec.QtyOut;
                    item.EndQty   = item.BegQty + ts_rec.QtyIn - ts_rec.QtyOut;

                    item.DocType      = ts_rec.DocType;
                    item.RefNbr       = ts_rec.RefNbr;
                    item.LineNbr      = ts_rec.LineNbr;
                    item.SplitLineNbr = ts_rec.SplitLineNbr;
                    item.GridLineNbr  = ++gridLineNbr;

                    decimal?unitcost;
                    if (filter.ShowAdjUnitCost ?? false)
                    {
                        unitcost = ts_rec.TotalQty != null && ts_rec.TotalQty != 0m ? (ts_rec.TotalCost + ts_rec.AdditionalCost) / ts_rec.TotalQty : 0m;
                    }
                    else
                    {
                        unitcost = ts_rec.TotalQty != null && ts_rec.TotalQty != 0m ? ts_rec.TotalCost / ts_rec.TotalQty : 0m;
                    }

                    item.UnitCost = unitcost;
                    resultList.Add(new PXResult <InventoryTranHistEnqResult, INTran, INTranSplit>(item, t_rec, ts_rec));
                    cumulativeQty += (ts_rec.InvtMult * ts_rec.BaseQty) ?? 0m;
                }
            }


            PXDelegateResult delegateResult = new PXDelegateResult();

            delegateResult.IsResultFiltered  = !filtersChanged;
            delegateResult.IsResultSorted    = !sortsChanged;
            delegateResult.IsResultTruncated = totalRows > resultList.Count;

            if (!PXView.ReverseOrder)
            {
                delegateResult.AddRange(resultList);
            }
            else
            {
                var sortedList = PXView.Sort(resultList);
                delegateResult.AddRange(sortedList.Cast <PXResult <InventoryTranHistEnqResult, INTran, INTranSplit> >());
                delegateResult.IsResultSorted = true;
            }

            return(delegateResult);
        }
Exemplo n.º 2
0
        private List <UpdateMCAssignmentResult> CalcMCAssignments(bool updateDB)
        {
            UpdateMCAssignmentSettings us = UpdateSettings.Current;

            List <UpdateMCAssignmentResult> list = new List <UpdateMCAssignmentResult>();

            if (us == null)
            {
                return(list);
            }                                            //empty

            if ((us.SiteID == null) || (us.StartFinPeriodID == null) || (us.EndFinPeriodID == null))
            {
                return(list);
            }                                                                                                                        //empty

            if (updateDB)
            {
                itemsite.Cache.Clear();
            }

            PXSelectBase <INItemSite> cmd = new PXSelectJoin <INItemSite,
                                                              InnerJoin <InventoryItem,
                                                                         On <InventoryItem.inventoryID, Equal <INItemSite.inventoryID>,
                                                                             And <InventoryItem.stkItem, Equal <boolTrue> > > >,
                                                              Where <INItemSite.siteID, Equal <Current <UpdateMCAssignmentSettings.siteID> > > >(this);

            foreach (PXResult <INItemSite, InventoryItem> resultset in cmd.Select())
            {
                INItemSite               currentItemSite = (INItemSite)resultset;
                InventoryItem            inventoryItem   = (InventoryItem)resultset;
                UpdateMCAssignmentResult updateMC        = new UpdateMCAssignmentResult();
                updateMC.MCFixed     = currentItemSite.MovementClassIsFixed;
                updateMC.Descr       = inventoryItem.Descr;
                updateMC.InventoryID = currentItemSite.InventoryID;
                updateMC.OldMC       = currentItemSite.MovementClassID;
                if (updateMC.MCFixed == true)
                {
                    updateMC.NewMC = currentItemSite.MovementClassID;
                }
                else
                {
                    PXSelectBase <INItemSiteHistDay> cmd1 = new PXSelectJoinGroupBy <INItemSiteHistDay,
                                                                                     InnerJoin <INItemSiteHistByLastDayInPeriod,
                                                                                                On <INItemSiteHistDay.inventoryID, Equal <INItemSiteHistByLastDayInPeriod.inventoryID>,
                                                                                                    And <INItemSiteHistDay.siteID, Equal <INItemSiteHistByLastDayInPeriod.siteID>,
                                                                                                         And <INItemSiteHistDay.subItemID, Equal <INItemSiteHistByLastDayInPeriod.subItemID>,
                                                                                                              And <INItemSiteHistDay.locationID, Equal <INItemSiteHistByLastDayInPeriod.locationID>,
                                                                                                                   And <INItemSiteHistDay.sDate, Equal <INItemSiteHistByLastDayInPeriod.lastActivityDate> > > > > > >,
                                                                                     Where <INItemSiteHistDay.siteID, Equal <Current <UpdateMCAssignmentSettings.siteID> >,
                                                                                            And <INItemSiteHistDay.inventoryID, Equal <Required <INItemSiteHistDay.inventoryID> >,
                                                                                                 And <INItemSiteHistDay.sDate, Greater <Current <UpdateMCAssignmentSettings.startDateForAvailableQuantity> >,
                                                                                                      And <INItemSiteHistByLastDayInPeriod.finPeriodID, LessEqual <Current <UpdateMCAssignmentSettings.endFinPeriodID> > > > > >,

                                                                                     Aggregate <
                                                                                         Sum <INItemSiteHistDay.endQty,
                                                                                              Count <INItemSiteHistByLastDayInPeriod.finPeriodID> > > >(this);


                    decimal tranYtdQty = 0m;

                    PXResult <INItemSiteHistDay> ihYtdQty = cmd1.Select(currentItemSite.InventoryID);
                    var itemHistTranYtdQty = (INItemSiteHistDay)ihYtdQty;

                    if (ihYtdQty.RowCount != 0)
                    {
                        tranYtdQty += (itemHistTranYtdQty.EndQty ?? 0m) / ihYtdQty.RowCount.Value;
                    }

                    PXSelectBase <INItemSiteHistDay> cmd2 = new

                                                            PXSelectJoinGroupBy <INItemSiteHistDay,
                                                                                 InnerJoin <MasterFinPeriod,
                                                                                            On <MasterFinPeriod.startDate, LessEqual <INItemSiteHistDay.sDate>,
                                                                                                And <INItemSiteHistDay.sDate, Less <MasterFinPeriod.endDate> > > >,

                                                                                 Where <INItemSiteHistDay.siteID, Equal <Current <UpdateMCAssignmentSettings.siteID> >,
                                                                                        And <INItemSiteHistDay.inventoryID, Equal <Required <INItemCostHist.inventoryID> >,
                                                                                             And <MasterFinPeriod.finPeriodID, GreaterEqual <Current <UpdateMCAssignmentSettings.startFinPeriodID> >,
                                                                                                  And <MasterFinPeriod.finPeriodID, LessEqual <Current <UpdateMCAssignmentSettings.endFinPeriodID> > > > > >,
                                                                                 Aggregate <
                                                                                     Sum <INItemSiteHistDay.qtySales,
                                                                                          Count <MasterFinPeriod.finPeriodID> > > >(this);

                    decimal tranPtdQtySales = 0m;

                    PXResult <INItemSiteHistDay> ihPtdQtySales           = cmd2.Select(currentItemSite.InventoryID);
                    INItemSiteHistDay            itemHistTranPtdQtySales = (INItemSiteHistDay)ihPtdQtySales;

                    if (ihPtdQtySales.RowCount != 0)
                    {
                        tranPtdQtySales += (itemHistTranPtdQtySales.QtySales ?? 0m) / ihPtdQtySales.RowCount.Value;
                    }

                    if (tranYtdQty != 0m || tranPtdQtySales != 0m)
                    {
                        decimal radio = MovementToStockRatio(tranYtdQty, tranPtdQtySales);

                        PXSelectBase <INMovementClass> cmd3 = new PXSelectReadonly <INMovementClass,
                                                                                    Where <INMovementClass.maxTurnoverPct, GreaterEqual <Required <INMovementClass.maxTurnoverPct> > >,
                                                                                    OrderBy <Asc <INMovementClass.maxTurnoverPct> > >(this);

                        INMovementClass movementClass = (INMovementClass)cmd3.Select(radio);
                        if (movementClass != null)
                        {
                            updateMC.NewMC = movementClass.MovementClassID;
                        }
                        else
                        {
                            updateMC.NewMC = null;
                        }
                    }
                }

                updateMC.OldMC = currentItemSite.MovementClassID;
                list.Add(updateMC);

                if (updateDB && (currentItemSite.MovementClassID != updateMC.NewMC))
                {
                    currentItemSite.MovementClassID = updateMC.NewMC;
                    itemsite.Update(currentItemSite);
                }
            }
            if (updateDB)
            {
                this.Actions.PressSave();
            }
            return(list);
        }