public virtual IEnumerable glaccountlist()
        {
            foreach (PXResult <GLHistoryByPeriod, RevaluedGLHistory, Account, Branch, FinPeriod, GLHistoryLastRevaluation> res in
                     PXSelectJoin <GLHistoryByPeriod,
                                   InnerJoin <RevaluedGLHistory,
                                              On <RevaluedGLHistory.ledgerID, Equal <GLHistoryByPeriod.ledgerID>,
                                                  And <RevaluedGLHistory.branchID, Equal <GLHistoryByPeriod.branchID>,
                                                       And <RevaluedGLHistory.accountID, Equal <GLHistoryByPeriod.accountID>,
                                                            And <RevaluedGLHistory.subID, Equal <GLHistoryByPeriod.subID>,
                                                                 And <RevaluedGLHistory.finPeriodID, Equal <GLHistoryByPeriod.lastActivityPeriod> > > > > >,
                                              InnerJoin <Account,
                                                         On <Account.accountID, Equal <GLHistoryByPeriod.accountID> >,
                                                         InnerJoin <Branch,
                                                                    On <Branch.branchID, Equal <GLHistoryByPeriod.branchID>,
                                                                        And <Branch.ledgerID, Equal <GLHistoryByPeriod.ledgerID> > >,
                                                                    InnerJoin <FinPeriod,
                                                                               On <GLHistoryByPeriod.finPeriodID, Equal <FinPeriod.finPeriodID>,
                                                                                   And <Branch.organizationID, Equal <FinPeriod.organizationID> > >,
                                                                               LeftJoin <GLHistoryLastRevaluation,
                                                                                         On <GLHistoryByPeriod.ledgerID, Equal <GLHistoryLastRevaluation.ledgerID>,
                                                                                             And <GLHistoryByPeriod.branchID, Equal <GLHistoryLastRevaluation.branchID>,
                                                                                                  And <GLHistoryByPeriod.accountID, Equal <GLHistoryLastRevaluation.accountID>,
                                                                                                       And <GLHistoryByPeriod.subID, Equal <GLHistoryLastRevaluation.subID> > > > > > > > > >,
                                   Where <Account.curyID, Equal <Current <RevalueFilter.curyID> >,
                                          And <FinPeriod.masterFinPeriodID, Equal <Current <RevalueFilter.finPeriodID> >,
                                               And <Where <RevaluedGLHistory.curyFinYtdBalance, NotEqual <decimal0>,
                                                           Or <RevaluedGLHistory.finYtdBalance, NotEqual <decimal0> > > > > > >
                     .Select(this))
            {
                GLHistoryByPeriod        histbyper             = res;
                GLHistoryLastRevaluation lastRevaluationPeriod = res;
                RevaluedGLHistory        hist = PXCache <RevaluedGLHistory> .CreateCopy(res);

                RevaluedGLHistory existing;

                if ((existing = GLAccountList.Locate(hist)) != null)
                {
                    yield return(existing);

                    continue;
                }
                else
                {
                    GLAccountList.Cache.SetStatus(hist, PXEntryStatus.Held);
                }

                if (string.IsNullOrEmpty(hist.CuryRateTypeID = ((Account)res).RevalCuryRateTypeId))
                {
                    hist.CuryRateTypeID = cmsetup.Current.GLRateTypeReval;
                }

                if (string.IsNullOrEmpty(hist.CuryRateTypeID))
                {
                    GLAccountList.Cache.RaiseExceptionHandling <RevaluedGLHistory.curyRateTypeID>(hist, null, new PXSetPropertyException(Messages.RateTypeNotFound));
                }
                else
                {
                    CurrencyRate curyrate = PXSelect <CurrencyRate,
                                                      Where <CurrencyRate.fromCuryID, Equal <Current <RevalueFilter.curyID> >,
                                                             And <CurrencyRate.toCuryID, Equal <Current <Company.baseCuryID> >,
                                                                  And <CurrencyRate.curyRateType, Equal <Required <Account.revalCuryRateTypeId> >,
                                                                       And <CurrencyRate.curyEffDate, LessEqual <Current <RevalueFilter.curyEffDate> > > > > >,
                                                      OrderBy <Desc <CurrencyRate.curyEffDate> > > .Select(this, hist.CuryRateTypeID);

                    if (curyrate == null || curyrate.CuryMultDiv == null)
                    {
                        hist.CuryMultDiv    = "M";
                        hist.CuryRate       = 1m;
                        hist.RateReciprocal = 1m;
                        hist.CuryEffDate    = Filter.Current.CuryEffDate;
                        GLAccountList.Cache.RaiseExceptionHandling <RevaluedGLHistory.curyRate>(hist, 1m, new PXSetPropertyException(Messages.RateNotFound, PXErrorLevel.RowWarning));
                    }
                    else
                    {
                        hist.CuryRate       = curyrate.CuryRate;
                        hist.RateReciprocal = curyrate.RateReciprocal;
                        hist.CuryEffDate    = curyrate.CuryEffDate;
                        hist.CuryMultDiv    = curyrate.CuryMultDiv;
                    }

                    CurrencyInfo info = new CurrencyInfo();
                    info.BaseCuryID  = company.Current.BaseCuryID;
                    info.CuryID      = hist.CuryID;
                    info.CuryMultDiv = hist.CuryMultDiv;
                    info.CuryRate    = hist.CuryRate;

                    decimal baseval;
                    PXCurrencyAttribute.CuryConvBase(currencyinfo.Cache, info, (decimal)hist.CuryFinYtdBalance, out baseval);
                    hist.FinYtdRevalued          = baseval;
                    hist.FinPtdRevalued          = hist.FinYtdRevalued - hist.FinYtdBalance;
                    hist.LastRevaluedFinPeriodID = lastRevaluationPeriod?.LastActivityPeriod;
                }

                yield return(hist);
            }
        }
示例#2
0
        protected virtual void CurrencyRate_RowInserting(PXCache cache, PXRowInsertingEventArgs e)
        {
            CurrencyRate   cr  = e.Row as CurrencyRate;
            CuryRateFilter crf = Filter.Current as CuryRateFilter;

            if (cr.FromCuryID != null && crf.ToCurrency != null && String.Compare(cr.FromCuryID, crf.ToCurrency, true) == 0)
            {
                throw new PXException(Messages.DestShouldDifferFromOrig);
            }

            bool importing = e.Row != null && cache.GetValuePending(e.Row, PXImportAttribute.ImportFlag) != null;

            if (e.ExternalCall && !importing)
            {
                if (cr.CuryEffDate == null)
                {
                    if (crf.EffDate == null)
                    {
                        cr.CuryEffDate = DateTime.Now;
                    }
                    else
                    {
                        cr.CuryEffDate = crf.EffDate;
                    }
                }

                if (cr.CuryRate != null)
                {
                    cr.RateReciprocal = Math.Round((decimal)(1 / cr.CuryRate), 8);
                    if (((CurrencyInfo)cr).CheckRateVariance(cache))
                    {
                        cache.RaiseExceptionHandling <CurrencyRate.curyRate>(cr, cr.CuryRate, new PXSetPropertyException(Messages.RateVarianceExceeded, PXErrorLevel.Warning));
                    }
                }

                if (cr.CuryMultDiv == " " &&
                    cr.FromCuryID != null &&
                    cr.ToCuryID != null &&
                    cr.CuryRateType != null &&
                    cr.CuryEffDate != null)
                {
                    CurrencyRate existRateMultDiv = PXSelectReadonly <CurrencyRate,
                                                                      Where <CurrencyRate.fromCuryID, Equal <Required <CurrencyRate.fromCuryID> >,
                                                                             And <CurrencyRate.toCuryID, Equal <Required <CurrencyRate.toCuryID> >,
                                                                                  And <CurrencyRate.curyRateType, Equal <Required <CurrencyRate.curyRateType> >,
                                                                                       And <CurrencyRate.curyEffDate, LessEqual <Required <CurrencyRate.curyEffDate> > > > > >,
                                                                      OrderBy <Desc <CurrencyRate.curyEffDate> > > .Select(cache.Graph, cr.FromCuryID, cr.ToCuryID, cr.CuryRateType, cr.CuryEffDate);

                    if (existRateMultDiv != null)
                    {
                        cr.CuryMultDiv = existRateMultDiv.CuryMultDiv;
                    }
                }
            }
            if (cr.FromCuryID != null &&
                cr.CuryRateType != null &&
                cr.CuryRateType != null &&
                cr.CuryEffDate != null &&
                cr.CuryRate != null)
            {
                CurrencyRate existRate = PXSelectReadonly <CurrencyRate,
                                                           Where <CurrencyRate.toCuryID, Equal <Required <CurrencyRate.toCuryID> >,
                                                                  And <CurrencyRate.fromCuryID, Equal <Required <CurrencyRate.fromCuryID> >,
                                                                       And <CurrencyRate.curyRateType, Equal <Required <CurrencyRate.curyRateType> >,
                                                                            And <CurrencyRate.curyEffDate, Equal <Required <CurrencyRate.curyEffDate> > > > > > > .Select(cache.Graph, cr.ToCuryID, cr.FromCuryID, cr.CuryRateType, cr.CuryEffDate);

                if (existRate != null)
                {
                    e.Cancel = true;
                    CurrencyRate newExistRate = (CurrencyRate)CuryRateRecordsEntry.Cache.CreateCopy(existRate);
                    newExistRate.CuryRate       = cr.CuryRate;
                    newExistRate.CuryMultDiv    = cr.CuryMultDiv;
                    newExistRate.RateReciprocal = cr.RateReciprocal;
                    CuryRateRecordsEntry.Update(newExistRate);
                    cache.RaiseExceptionHandling <CurrencyRate.curyRate>(cr, cr.CuryRate, new PXSetPropertyException(Messages.SuchRateTypeAlreadyExist, PXErrorLevel.Warning));
                }
            }
        }
        public virtual IEnumerable araccountlist()
        {
            foreach (PXResult <ARHistoryByPeriod, RevaluedARHistory, Customer, ARHistoryLastRevaluation> res in
                     PXSelectJoin <ARHistoryByPeriod,
                                   InnerJoin <RevaluedARHistory,
                                              On <RevaluedARHistory.customerID, Equal <ARHistoryByPeriod.customerID>,
                                                  And <RevaluedARHistory.branchID, Equal <ARHistoryByPeriod.branchID>,
                                                       And <RevaluedARHistory.accountID, Equal <ARHistoryByPeriod.accountID>,
                                                            And <RevaluedARHistory.subID, Equal <ARHistoryByPeriod.subID>,
                                                                 And <RevaluedARHistory.curyID, Equal <ARHistoryByPeriod.curyID>,
                                                                      And <RevaluedARHistory.finPeriodID, Equal <ARHistoryByPeriod.lastActivityPeriod> > > > > > >,
                                              InnerJoin <Customer,
                                                         On <Customer.bAccountID, Equal <ARHistoryByPeriod.customerID> >,
                                                         LeftJoin <ARHistoryLastRevaluation,
                                                                   On <ARHistoryByPeriod.customerID, Equal <ARHistoryLastRevaluation.customerID>,
                                                                       And <ARHistoryByPeriod.branchID, Equal <ARHistoryLastRevaluation.branchID>,
                                                                            And <ARHistoryByPeriod.accountID, Equal <ARHistoryLastRevaluation.accountID>,
                                                                                 And <ARHistoryByPeriod.subID, Equal <ARHistoryLastRevaluation.subID>,
                                                                                      And <ARHistoryByPeriod.curyID, Equal <ARHistoryLastRevaluation.curyID> > > > > > > > >,
                                   Where <ARHistoryByPeriod.curyID, Equal <Current <RevalueFilter.curyID> >,
                                          And <ARHistoryByPeriod.finPeriodID, Equal <Current <RevalueFilter.finPeriodID> >,
                                               And <Where <RevaluedARHistory.curyFinYtdBalance, NotEqual <decimal0>,
                                                           Or <RevaluedARHistory.finPtdRevalued, NotEqual <decimal0> > > > > > >
                     .Select(this))
            {
                ARHistoryByPeriod        histbyper             = res;
                ARHistoryLastRevaluation lastRevaluationPeriod = res;
                RevaluedARHistory        hist = PXCache <RevaluedARHistory> .CreateCopy(res);

                RevaluedARHistory existing;
                Customer          cust = res;

                if ((existing = ARAccountList.Locate(hist)) != null)
                {
                    yield return(existing);

                    continue;
                }
                else
                {
                    ARAccountList.Cache.SetStatus(hist, PXEntryStatus.Held);
                }

                hist.CustomerClassID = cust.CustomerClassID;
                hist.CuryRateTypeID  = cmsetup.Current.ARRateTypeReval ?? cust.CuryRateTypeID;

                if (string.IsNullOrEmpty(hist.CuryRateTypeID))
                {
                    ARAccountList.Cache.RaiseExceptionHandling <RevaluedGLHistory.curyRateTypeID>(hist, null, new PXSetPropertyException(Messages.RateTypeNotFound));
                }
                else
                {
                    CurrencyRate curyrate = PXSelect <CurrencyRate,
                                                      Where <CurrencyRate.fromCuryID, Equal <Current <RevalueFilter.curyID> >,
                                                             And <CurrencyRate.toCuryID, Equal <Current <Company.baseCuryID> >,
                                                                  And <CurrencyRate.curyRateType, Equal <Required <Customer.curyRateTypeID> >,
                                                                       And <CurrencyRate.curyEffDate, LessEqual <Current <RevalueFilter.curyEffDate> > > > > >,
                                                      OrderBy <Desc <CurrencyRate.curyEffDate> > > .Select(this, hist.CuryRateTypeID);

                    if (curyrate == null || curyrate.CuryMultDiv == null)
                    {
                        hist.CuryMultDiv    = "M";
                        hist.CuryRate       = 1m;
                        hist.RateReciprocal = 1m;
                        hist.CuryEffDate    = Filter.Current.CuryEffDate;
                        ARAccountList.Cache.RaiseExceptionHandling <RevaluedARHistory.curyRate>(hist, 1m, new PXSetPropertyException(Messages.RateNotFound, PXErrorLevel.RowWarning));
                    }
                    else
                    {
                        hist.CuryRate       = curyrate.CuryRate;
                        hist.RateReciprocal = curyrate.RateReciprocal;
                        hist.CuryEffDate    = curyrate.CuryEffDate;
                        hist.CuryMultDiv    = curyrate.CuryMultDiv;
                    }

                    CurrencyInfo info = new CurrencyInfo();
                    info.BaseCuryID  = company.Current.BaseCuryID;
                    info.CuryID      = hist.CuryID;
                    info.CuryMultDiv = hist.CuryMultDiv;
                    info.CuryRate    = hist.CuryRate;

                    //hist.CuryFinYtdBalance -= hist.CuryFinYtdDeposits;
                    //hist.FinYtdBalance -= hist.FinYtdDeposits;

                    decimal baseval;
                    PXCurrencyAttribute.CuryConvBase(currencyinfo.Cache, info, (decimal)hist.CuryFinYtdBalance, out baseval);
                    hist.FinYtdRevalued          = baseval;
                    hist.FinPrevRevalued         = string.Equals(histbyper.FinPeriodID, histbyper.LastActivityPeriod) ? hist.FinPtdRevalued : 0m;
                    hist.FinPtdRevalued          = hist.FinYtdRevalued - hist.FinPrevRevalued - hist.FinYtdBalance;
                    hist.LastRevaluedFinPeriodID = lastRevaluationPeriod?.LastActivityPeriod;
                }
                yield return(hist);
            }
        }
        public static void RefreshRates(RefreshFilter filter, List <RefreshRate> list, string apiKey)
        {
            bool hasError = false;

            var date = GetUtcSyncDate(filter.CuryEffDate.Value);

            string ratesRequestURL = String.Format(
                "http://openexchangerates.org/api/time-series.json?app_id={0}&base={1}&start={2:yyyy-MM-dd}&end={2:yyyy-MM-dd}",
                apiKey,
                filter.CuryID,
                date);

            PXTrace.WriteInformation("Refresh rates URL: " + ratesRequestURL);

            var client   = new WebClient();
            var response = client.DownloadString(new Uri(ratesRequestURL));

            JObject json = (JObject)JsonConvert.DeserializeObject(response);

            if (json == null)
            {
                throw new PXException(Messages.CurrencyRateJsonError, response);
            }
            JToken rates = json.SelectToken(String.Format("rates.{0:yyyy-MM-dd}", date), true);

            CuryRateMaint graph = PXGraph.CreateInstance <CuryRateMaint>();

            graph.Filter.Current.ToCurrency = filter.CuryID;
            graph.Filter.Current.EffDate    = date;

            for (int i = 0; i < list.Count; i++)
            {
                RefreshRate rr = list[i];

                var rate = rates.Children().Cast <JProperty>().Where(p => p.Name == rr.FromCuryID).FirstOrDefault();
                if (rate == null)
                {
                    PXProcessing <RefreshRate> .SetError(i, PXMessages.LocalizeFormatNoPrefixNLA(Messages.NoOnlyRatesFoundForCurrency, rr.FromCuryID));

                    hasError = true;
                }
                else
                {
                    CurrencyRate curyRate = (CurrencyRate)graph.CuryRateRecordsEntry.Insert();
                    curyRate.FromCuryID   = rr.FromCuryID;
                    curyRate.ToCuryID     = filter.CuryID;
                    curyRate.CuryRateType = rr.CuryRateType;
                    curyRate.CuryRate     = rate.Value.Value <decimal>() * (1 + rr.OnlineRateAdjustment.GetValueOrDefault(0) / 100);
                    curyRate.CuryMultDiv  = "D";
                    rr.CuryRate           = curyRate.CuryRate;
                    graph.CuryRateRecordsEntry.Update(curyRate);

                    PXProcessing <RefreshRate> .SetInfo(i, ActionsMessages.RecordProcessed);
                }
            }

            graph.Actions.PressSave();

            if (hasError)
            {
                throw new PXOperationCompletedException(Messages.CurrencyRateFailedToRefresh);
            }
        }