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