static void UpdateCurrency(string baseCurrencyCode, string quoteCurrencyCode, DateTime startDate)
{
// Need a minimum span of months to force Oanda to display monthly data vs. weekly data
DateTime oandaStartDate = startDate;
if (DateTime.UtcNow.AddMonths(-12) < oandaStartDate)
{
oandaStartDate = DateTime.UtcNow.AddMonths(-12);
}
string url = BuildRequestUrl(baseCurrencyCode, quoteCurrencyCode, oandaStartDate);
string csvString = "";
try
{
var client = new System.Net.WebClient();
csvString = client.DownloadString(url);
}
catch (Exception)
{
}
string[] allRows = csvString.Split('\n');
string[] dateRows = GetDateRows(allRows);
foreach (string row in dateRows)
{
string[] lineData = LineSplitter(row).ToArray();
if (lineData.Count() >= 1)
{
DateTime conversionDate = DateTime.Parse(lineData[0]);
decimal conversionFactor = 1 / Convert.ToDecimal(lineData[1]);
if (conversionDate != null && conversionFactor != null)
{
CURRENCY_XREF entry = (from cx in entities.CURRENCY_XREF
where cx.CURRENCY_CODE == quoteCurrencyCode &&
cx.EFF_YEAR == conversionDate.Year &&
cx.EFF_MONTH == conversionDate.Month
select cx).FirstOrDefault();
if (entry == null)
{
entry = new CURRENCY_XREF()
{
EFF_YEAR = conversionDate.Year,
EFF_MONTH = conversionDate.Month,
CURRENCY_CODE = quoteCurrencyCode,
BASE_CURRENCY_RATE = conversionFactor
};
entities.CURRENCY_XREF.AddObject(entry);
}
}
}
}
}
protected void rbSave_Click(object sender, EventArgs e)
{
int currentMonth = ((DateTime)radPeriodSelect.SelectedDate).Month;
int currentYear = ((DateTime)radPeriodSelect.SelectedDate).Year;
foreach (RepeaterItem ri in rptCurrency.Items)
{
HiddenField hf = (HiddenField)ri.FindControl("hfCurrencyCode");
RadNumericTextBox tb = (RadNumericTextBox)ri.FindControl("tbRate");
if (hf != null && tb != null)
{
string currencyCode = hf.Value;
//decimal rate = 0;
//Decimal.TryParse(tb.Text, out rate);
double rateVal = 0;
Double.TryParse(tb.Text, out rateVal);
if (rateVal > 0)
{
//rate = (decimal)rateVal;
var entities = new PSsqmEntities();
var queryObject = (from cx in entities.CURRENCY_XREF where cx.EFF_YEAR == currentYear &&
cx.EFF_MONTH == currentMonth &&
cx.CURRENCY_CODE == currencyCode
select cx).FirstOrDefault();
if (queryObject == null)
{
CURRENCY_XREF cur = new CURRENCY_XREF()
{
EFF_YEAR = currentYear,
EFF_MONTH = currentMonth,
CURRENCY_CODE = currencyCode,
BASE_CURRENCY_RATE = (decimal)rateVal
};
entities.CURRENCY_XREF.AddObject(cur);
}
else
{
queryObject.BASE_CURRENCY_RATE = (decimal)rateVal;
}
entities.SaveChanges();
}
}
}
lblMessage.Visible = true;
lblMessage.Text = "<div style=\"padding: 12px 0;\">Changes have been saved.</div>";
}
public void BindProfileInputHdr(EHSProfile profile)
{
ToggleVisible(pnlProfileInputHdr);
lblPlantName_out.Text = profile.Plant.PLANT_NAME;
lblPeriodFrom_out.Text = SQMBasePage.FormatDate(profile.InputPeriod.PeriodDate, "MM/yyyy", false);
lblDueDate_out.Text = SQMBasePage.FormatDate(profile.InputPeriod.DueDate.AddMonths(1), "d", false);
DateTime lastUpdateDate;
TaskStatus status = profile.PeriodStatus(new string[0] {
}, true, out lastUpdateDate);
lblInputStatus1_out.Text = profile.InputPeriod.NumRequiredComplete.ToString();
lblInputStatus2_out.Text = profile.InputPeriod.NumRequired.ToString();
lblLastUpdateBy_out.Text = lblLastUpdate_out.Text = "";
EHS_PROFILE_INPUT lastinput = profile.InputPeriod.GetLastInput();
if (lastinput == null || lastinput.LAST_UPD_DT.HasValue == false)
{
if (profile.InputPeriod.PlantAccounting != null && !string.IsNullOrEmpty(profile.InputPeriod.PlantAccounting.LAST_UPD_BY))
{
lblLastUpdateBy_out.Text = profile.InputPeriod.PlantAccounting.LAST_UPD_BY;
if (profile.InputPeriod.PlantAccounting.LAST_UPD_DT.HasValue)
{
lblLastUpdate_out.Text = SQMBasePage.FormatDate((DateTime)profile.InputPeriod.PlantAccounting.LAST_UPD_DT, "", false);
}
}
}
else
{
lblLastUpdateBy_out.Text = lastinput.LAST_UPD_BY;
lblLastUpdate_out.Text = SQMBasePage.FormatDate((DateTime)lastinput.LAST_UPD_DT, "", false);
}
CURRENCY_XREF exchangeRate = profile.InputPeriod.PeriodExchangeRate(profile.Plant);
if (exchangeRate != null && exchangeRate.EFF_MONTH > 0)
{
phRateStatus.Visible = true;
lblCurrency.Text = profile.Plant.CURRENCY_CODE;
lblRateStatus.Text = (System.Environment.NewLine + SQMBasePage.FormatDate(new DateTime(exchangeRate.EFF_YEAR, exchangeRate.EFF_MONTH, DateTime.DaysInMonth(exchangeRate.EFF_YEAR, exchangeRate.EFF_MONTH)), "d", false));
}
}
public static CURRENCY_XREF CurrentRate(string currencyCode, int effYear, int effMonth)
{
CURRENCY_XREF currentRate = new CURRENCY_XREF();
try
{
using (PSsqmEntities entities = new PSsqmEntities())
{
if (effYear > 0 && effMonth > 0)
{
if (string.IsNullOrEmpty(currencyCode))
{
currentRate = (from r in entities.CURRENCY_XREF
where (r.EFF_YEAR == effYear && r.EFF_MONTH == effMonth)
select r).FirstOrDefault();
}
else
{
currentRate = (from r in entities.CURRENCY_XREF
where (r.CURRENCY_CODE == currencyCode && r.EFF_YEAR == effYear && r.EFF_MONTH == effMonth)
select r).FirstOrDefault();
}
}
else
{
currentRate = (from r in entities.CURRENCY_XREF
select r).OrderByDescending(r => r.EFF_YEAR).ThenByDescending(r => r.EFF_MONTH).Where(l => l.CURRENCY_CODE == currencyCode).First();
}
}
}
catch (Exception e)
{
// SQMLogger.LogException(e);
}
return(currentRate);
}
static string ProcessEnvironmental()
{
PSsqmEntities entities = new PSsqmEntities();
SETTINGS setting = null;
string nextStep = "";
DateTime toDate = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, 1);
DateTime fromDate = toDate.AddMonths(-3);
int status = 0;
DateTime currencyDate = DateTime.MinValue;
WriteLine("ENVIRONMENTAL Rollup Started: " + DateTime.UtcNow.ToString("hh:mm MM/dd/yyyy"));
List <SETTINGS> sets = SQMSettings.SelectSettingsGroup("AUTOMATE", ""); // ABW 20140805
try
{
setting = sets.Where(x => x.SETTING_CD == "ROLLUP_ENV_PERIODSPAN").FirstOrDefault();
if (setting != null)
{
fromDate = toDate.AddMonths(Convert.ToInt32(setting.VALUE) * -1);
}
setting = sets.Where(x => x.SETTING_CD == "ROLLUP_ENV_NEXTPAGE").FirstOrDefault();
if (setting != null && !string.IsNullOrEmpty(setting.VALUE) && setting.VALUE.Length > 1)
{
nextStep = setting.VALUE;
}
CURRENCY_XREF latestCurrency = CurrencyMgr.GetLatestRecord(entities);
if (latestCurrency != null)
{
currencyDate = new DateTime(latestCurrency.EFF_YEAR, latestCurrency.EFF_MONTH, DateTime.DaysInMonth(latestCurrency.EFF_YEAR, latestCurrency.EFF_MONTH));
}
WriteLine("Max Currency Date = " + currencyDate.ToShortDateString());
List <EHSProfile> profileList = new List <EHSProfile>();
foreach (decimal plantID in (from p in entities.EHS_PROFILE select p.PLANT_ID).ToList())
{
profileList.Add(new EHSProfile().Load(Convert.ToDecimal(plantID), false, true));
}
foreach (EHSProfile profile in profileList) // do each plant having a metric profile
{
WriteLine(profile.Plant.PLANT_NAME);
DateTime periodDate = fromDate;
while (periodDate <= toDate) // do each month within the rollup span
{
WriteLine(" " + periodDate.Year.ToString() + "/" + periodDate.Month.ToString());
if (profile.InputPeriod == null || profile.InputPeriod.PeriodDate != periodDate)
{
profile.LoadPeriod(periodDate);
}
if (profile.ValidPeriod())
{
if (!profile.InputPeriod.PlantAccounting.APPROVAL_DT.HasValue)
{
profile.InputPeriod.PlantAccounting.APPROVAL_DT = toDate;
profile.InputPeriod.PlantAccounting.APPROVER_ID = 1m; // default to the sysadmin user
}
status = profile.UpdateMetricHistory(periodDate); // new roll-up logic
WriteLine(" ... " + status.ToString());
}
periodDate = periodDate.AddMonths(1);
}
}
WriteLine("ENVIRONMENTAL Rollup Completed: " + DateTime.UtcNow.ToString("hh:mm MM/dd/yyyy"));
}
catch (Exception ex)
{
WriteLine("ENVIRONMENTAL RollUp Error: " + ex.ToString());
}
return(nextStep);
}
protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack)
{
System.Web.UI.ScriptManager.RegisterStartupScript(this, GetType(), "closePage", "window.onunload = CloseWindow();", true);
return;
}
output = new StringBuilder();
SETTINGS setting = null;
bool validIP = true;
string pageURI = HttpContext.Current.Request.Url.AbsoluteUri;
string nextPage = "";
DateTime toDate = new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, 1);
DateTime fromDate = toDate.AddMonths(-3);
WriteLine("ENV Data Rollup Started: " + DateTime.UtcNow.ToString("hh:mm MM/dd/yyyy"));
WriteLine(pageURI);
try
{
string currentIP = GetIPAddress();
List <SETTINGS> sets = SQMSettings.SelectSettingsGroup("AUTOMATE", ""); // ABW 20140805
string strValidIP = sets.Find(x => x.SETTING_CD == "ValidIP").VALUE.ToString();
setting = sets.Where(x => x.SETTING_CD == "ROLLUP_ENV_PERIODSPAN").FirstOrDefault();
if (setting != null)
{
fromDate = toDate.AddMonths(Convert.ToInt32(setting.VALUE) * -1);
}
setting = sets.Where(x => x.SETTING_CD == "ROLLUP_ENV_NEXTPAGE").FirstOrDefault();
if (setting != null && !string.IsNullOrEmpty(setting.VALUE) && setting.VALUE.Length > 1)
{
nextPage = setting.VALUE;
}
/*
* if (strValidIP.Equals(currentIP))
* {
* WriteLine("Main Incident RollUp being accessed from a valid IP address " + currentIP);
* validIP = true;
*
* if (Request.QueryString["validation"] != null)
* {
* if (Request.QueryString["validation"].ToString().Equals("Vb12M11a4"))
* validIP = true;
* }
* else
* {
* WriteLine("Main Incident RollUp requested from incorrect source.");
* validIP = false;
* }
* }
* else
* {
* WriteLine("Main Incident RollUp being accessed from invalid IP address " + currentIP);
* validIP = false;
* }
*/
}
catch (Exception ex)
{
validIP = false;
WriteLine("Main ENV Data RollUp Error validating IP Address: " + ex.ToString());
}
// make sure this code is NOT moved to production
//validIP = true;
if (!validIP)
{
WriteLine("Main ENV Data RollUp Invalid IP Address");
ltrStatus.Text = output.ToString().Replace("\n", "<br/>");
WriteLogFile();
System.Web.UI.ScriptManager.RegisterStartupScript(this, GetType(), "closePage", "window.onunload = CloseWindow();", true);
return;
}
try
{
PSsqmEntities entities = new PSsqmEntities();
int status = 0;
DateTime currencyDate = DateTime.MinValue;
CURRENCY_XREF latestCurrency = CurrencyMgr.GetLatestRecord(entities);
if (latestCurrency != null)
{
currencyDate = new DateTime(latestCurrency.EFF_YEAR, latestCurrency.EFF_MONTH, DateTime.DaysInMonth(latestCurrency.EFF_YEAR, latestCurrency.EFF_MONTH));
}
WriteLine("Max Currency Date = " + currencyDate.ToShortDateString());
List <EHSProfile> profileList = new List <EHSProfile>();
foreach (decimal plantID in (from p in entities.EHS_PROFILE select p.PLANT_ID).ToList())
{
profileList.Add(new EHSProfile().Load(Convert.ToDecimal(plantID), false, true));
}
foreach (EHSProfile profile in profileList) // do each plant having a metric profile
{
WriteLine(profile.Plant.PLANT_NAME);
DateTime periodDate = fromDate;
while (periodDate <= toDate) // do each month within the rollup span
{
WriteLine(" " + periodDate.Year.ToString() + "/" + periodDate.Month.ToString());
if (profile.InputPeriod == null || profile.InputPeriod.PeriodDate != periodDate)
{
profile.LoadPeriod(periodDate);
}
if (profile.ValidPeriod())
{
if (!profile.InputPeriod.PlantAccounting.APPROVAL_DT.HasValue)
{
profile.InputPeriod.PlantAccounting.APPROVAL_DT = toDate;
profile.InputPeriod.PlantAccounting.APPROVER_ID = 1m; // default to the sysadmin user
}
status = profile.UpdateMetricHistory(periodDate); // new roll-up logic
WriteLine(" ... " + status.ToString());
periodDate = periodDate.AddMonths(1);
}
}
}
}
catch (Exception ex)
{
WriteLine("Main ENV Data RollUp Error - " + ex.ToString());
}
WriteLine("");
WriteLine("Completed: " + DateTime.UtcNow.ToString("hh:mm MM/dd/yyyy"));
ltrStatus.Text = output.ToString().Replace("\n", "<br/>");
WriteLogFile();
try
{
if (!string.IsNullOrEmpty(nextPage))
{
int s1 = pageURI.LastIndexOf('/');
int s2 = pageURI.LastIndexOf('.') > -1 ? pageURI.LastIndexOf('.') : pageURI.Length;
string nextPageURI = pageURI.Substring(0, s1 + 1) + nextPage + pageURI.Substring(s2, pageURI.Length - s2);
Response.Redirect(nextPageURI);
}
}
catch (Exception ex)
{
output = new StringBuilder();
WriteLine("RollUp Redirect Error - " + ex.ToString());
WriteLogFile();
}
System.Web.UI.ScriptManager.RegisterStartupScript(this, GetType(), "closePage", "window.onunload = CloseWindow();", true);
}
public void rptProfileStatus_OnItemDataBound(object sender, RepeaterItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.AlternatingItem || e.Item.ItemType == ListItemType.Item)
{
Label lbl;
try
{
EHSProfile profile = (EHSProfile)e.Item.DataItem;
Button btn = (Button)e.Item.FindControl("btnRollup");
btn.CommandArgument = profile.Plant.PLANT_ID.ToString();
btn = (Button)e.Item.FindControl("btnRollupYTD");
btn.CommandArgument = profile.Plant.PLANT_ID.ToString();
DateTime lastUpdateDate;
TaskStatus status = profile.PeriodStatus(new string[0] {
}, true, out lastUpdateDate);
lbl = (Label)e.Item.FindControl("lblInputs");
lbl.Text += profile.InputPeriod.NumComplete + " of " + profile.MeasureList(false).Count + " (Total)";
lbl = (Label)e.Item.FindControl("lblReqdInputs");
lbl.Text = profile.InputPeriod.NumRequiredComplete.ToString() + " of " + profile.InputPeriod.NumRequired.ToString() + " (Required)";
LinkButton lnk = (LinkButton)e.Item.FindControl("lnkInputs");
lnk.CommandArgument = profile.Plant.PLANT_ID.ToString();
lbl = (Label)e.Item.FindControl("lblRollupStatus");
lnk = (LinkButton)e.Item.FindControl("lnkHistory");
lnk.CommandArgument = profile.Plant.PLANT_ID.ToString();
if (profile.InputPeriod.PlantAccounting != null && profile.InputPeriod.PlantAccounting.FINALIZE_DT != null)
{
lbl.Text = SQMModelMgr.FormatPersonListItem(SQMModelMgr.LookupPerson((decimal)profile.InputPeriod.PlantAccounting.FINALIZE_ID, ""));
lbl.Text += " ";
lbl.Text += SQMBasePage.FormatDate((DateTime)profile.InputPeriod.PlantAccounting.FINALIZE_DT, "d", false);
lnk.Visible = true;
}
string currencyCode = SQMModelMgr.LookupPlant(profile.Plant.PLANT_ID).CURRENCY_CODE;
lbl = (Label)e.Item.FindControl("lblRateStatus");
lbl.Text = currencyCode + ": ";
CURRENCY_XREF currentRate = CurrencyConverter.CurrentRate(currencyCode, profile.InputPeriod.PeriodYear, profile.InputPeriod.PeriodMonth);
if (currentRate != null && currentRate.EFF_MONTH > 0)
{
lbl.Text += (System.Environment.NewLine + SQMBasePage.FormatDate(new DateTime(currentRate.EFF_YEAR, currentRate.EFF_MONTH, DateTime.DaysInMonth(currentRate.EFF_YEAR, currentRate.EFF_MONTH)), "d", false));
}
if (profile.InputPeriod.PlantAccounting != null)
{
SETTINGS sets = SQMSettings.GetSetting("EHS", "ACCTFIELDS"); // try to retrieve fields to display for this client
Ucl_EHSList ucl = (Ucl_EHSList)e.Item.FindControl("uclProdList");
ucl.BindProdFieldsList(profile, sets == null ? "" : sets.VALUE);
if (profile.InputPeriod.PlantAccounting.APPROVER_ID.HasValue && profile.InputPeriod.PlantAccounting.APPROVAL_DT.HasValue)
{
lbl = (Label)e.Item.FindControl("lblFinalStatus");
lbl.Text = SQMModelMgr.FormatPersonListItem(SQMModelMgr.LookupPerson((decimal)profile.InputPeriod.PlantAccounting.APPROVER_ID, ""));
lbl.Text += " ";
lbl.Text += SQMBasePage.FormatDate((DateTime)profile.InputPeriod.PlantAccounting.APPROVAL_DT, "", false);
}
}
}
catch (Exception ex)
{
;
}
}
}
