private void LoadFormEnterData()
{
DateTextBox.Text = DateTime.Now.ToString();
//-- get CategoriesItems combinated
List <KPICategoyCombination> theCombinatedList = new List <KPICategoyCombination>();
try
{
theCombinatedList = KPICategoryCombinationBLL.GetCategoryItemsCombinatedByKpiId(Convert.ToInt32(KPIIdHiddenField.Value));
}
catch (Exception exc)
{
log.Error("Error en GetCategoryItemsCombinatedByKpiId para kpiId: " + KPIIdHiddenField.Value, exc);
SystemMessages.DisplaySystemErrorMessage(exc.Message);
return;
}
if (theCombinatedList.Count <= 0)
{
theCombinatedList.Add(new KPICategoyCombination());
}
EnterDataRepeater.DataSource = theCombinatedList;
EnterDataRepeater.DataBind();
}
private void LoadKpiData()
{
int kpiId = Convert.ToInt32(KpiIdHiddenField.Value);
int userId = UserBLL.GetUserIdByUsername(User.Identity.Name);
UserIdHiddenField.Value = userId.ToString();
KPI kpi = KPIBLL.GetKPIById(kpiId);
KpiNameLiteral.Text = kpi.Name;
//Inicializo los valores conocidos
string lang = LanguageUtilities.GetLanguageFromContext();
KPITypeBLL theBll = new KPITypeBLL();
KPIType type = theBll.GetKPITypesByID(kpi.KpiTypeID, lang);
KpiType.Text = type != null ? type.TypeName : kpi.KpiTypeID;
//WebServiceId.Text = "<div class='col-md-4 col-sm-4'>Web Service ID:</div><div class='col-md-8 col-sm-8'>SERV-Reliavility</div>";
ReportingUnit.Text = kpi.ReportingUnitName;
KpiTarget.Text = (kpi.TargetPeriod == 0 ? Resources.KpiDetails.NoTargetLabel : kpi.TargetPeriod + " " + kpi.ReportingUnitID);
//if (caso <= 50)
//{
// StartingDate.Text = "<div class='col-md-4 col-sm-4'>Starting Date:</div><div class='col-md-8 col-sm-8'>01/15/16</div>";
// MeanTimeGraphic.Visible = true;
// MeanTimeProgress.Visible = true;
//}
//else
//{
StartingDate.Text = kpi.StartDate != DateTime.MinValue ? kpi.StartDate.ToShortDateString() : "-";
RevenueCollectionGraphic.Visible = true;
//RevenueCollectionProgress.Visible = true;
ChartControl.KpiId = kpiId;
ExportControl.KpiId = kpiId;
StatsControl.KpiId = kpiId;
MeasurementsControl.KpiId = kpiId;
MeasurementsControl.Unit = kpi.UnitID;
MeasurementsControl.Currency = kpi.Currency;
MeasurementsControl.CurrencyUnit = kpi.CurrencyUnitForDisplay;
UnitIdHiddenField.Value = kpi.UnitID;
CurrencyHiddenField.Value = kpi.Currency;
CurrencyUnitHiddenField.Value = kpi.CurrencyUnitForDisplay;
//CurrencyUnitBLL currencyUnitBll = new CurrencyUnitBLL();
//CurrencyUnit currencyUnit = currencyUnitBll.GetCurrencyUnitsById(lang, ;
//MeasurementsControl.Currency = kpi.Currency;
//}
List <KPICategoyCombination> categories = KPICategoryCombinationBLL.GetCategoryItemsCombinatedByKpiId(kpiId);
if (categories.Count > 0)
{
CategoriesRepeater.DataSource = categories;
CategoriesRepeater.DataBind();
CategoriesPanel.Visible = true;
}
}
protected void UploadDataButton_Click(object sender, EventArgs e)
{
string[] validFileTypes = { ".xlsx" };
string ext = System.IO.Path.GetExtension(FileUpload.PostedFile.FileName);
bool isValidFile = false;
for (int i = 0; i < validFileTypes.Length; i++)
{
if (ext == validFileTypes[i])
{
isValidFile = true;
break;
}
}
if (!isValidFile)
{
ValidateFile.ForeColor = System.Drawing.Color.Red;
ValidateFile.Text = Resources.ImportData.InvalidaFile + string.Join(",", validFileTypes);
return;
}
//-- get Categories
List <Category> theCategoryList = new List <Category>();
try
{
theCategoryList = CategoryBLL.GetCategoriesByKpiId(Convert.ToInt32(KPIIdHiddenField.Value));
}
catch (Exception exc)
{
SystemMessages.DisplaySystemErrorMessage(exc.Message);
return;
}
//-- get CategoriesItems combinated
List <KPICategoyCombination> theCombinatedList = new List <KPICategoyCombination>();
try
{
theCombinatedList = KPICategoryCombinationBLL.GetCategoryItemsCombinatedByKpiId(Convert.ToInt32(KPIIdHiddenField.Value));
}
catch (Exception exc)
{
log.Error("Error en GetCategoryItemsCombinatedByKpiId para kpiId: " + KPIIdHiddenField.Value, exc);
SystemMessages.DisplaySystemErrorMessage(exc.Message);
return;
}
//-- get Measurements
List <KPIMeasurements> theMeasurementList = new List <KPIMeasurements>();
try
{
theMeasurementList = KpiMeasurementBLL.GetKPIMeasurementCategoriesByKpiId(Convert.ToInt32(KPIIdHiddenField.Value), "", "");
}
catch (Exception exc)
{
log.Error("Error en GetKPIMeasurementCategoriesByKpiId para kpiId: " + KPIIdHiddenField.Value, exc);
SystemMessages.DisplaySystemErrorMessage(exc.Message);
return;
}
try
{
using (ExcelPackage package = new ExcelPackage(FileUpload.FileContent))
{
// get the first worksheet in the workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
if (worksheet.Dimension == null)
{
SystemMessages.DisplaySystemErrorMessage(Resources.ImportData.EmptyFile);
return;
}
}
string timeFormat = Resources.Validations.TimeDataFormat;
Regex regexTime = new Regex(timeFormat);
//-- leer Excel
List <ExColumn> columns = new List <ExColumn>();
columns.Add(new DateExColumn(Resources.ImportData.DateColumn, true, false));
foreach (Category theCategory in theCategoryList)
{
columns.Add(new ListExColumn(theCategory.ID, true, true, theCategory.ItemsList.Split(';').ToList().FindAll(i => !string.IsNullOrEmpty(i))));
}
switch (UnitIdHiddenField.Value)
{
case "TIME":
columns.Add(new StringExColumn(Resources.ImportData.ValueColumn, true, false, timeFormat));
break;
case "INT":
columns.Add(new IntegerExColumn(Resources.ImportData.ValueColumn, true, false, true));
break;
default:
columns.Add(new DecimalExColumn(Resources.ImportData.ValueColumn, true, false, true));
break;
}
List <String> errors = new List <string>();
DataSet newDataSet = ExProcess.ReadExcelSpreadhseet(FileUpload.FileContent, columns, errors);
if (errors.Count > 0)
{
ErrorFileRepeater.DataSource = errors;
ErrorFileRepeater.DataBind();
pnlErrorData.Visible = true;
ErrorFileLabel.Text = string.Format(Resources.ImportData.ErrorsInFile, FileUpload.FileName);
return;
}
List <KPIMeasurements> theList = new List <KPIMeasurements>();
KPIMeasurements theData = null;
List <string> theItemList = null;
string itemCategories = "";
DateTime date = DateTime.MinValue;
string value = "";
for (int i = 0; i < newDataSet.Tables[0].Rows.Count; i++)
{
DataRow theRow = newDataSet.Tables[0].Rows[i];
theData = new KPIMeasurements();
theData.Date = DateTime.Parse(theRow[Resources.ImportData.DateColumn].ToString().Trim());
if (UnitIdHiddenField.Value.Equals("TIME"))
{
theData.DataTime = GetMeasurementTime(theRow[Resources.ImportData.ValueColumn].ToString().Trim(), regexTime);
}
else
{
value = theRow[Resources.ImportData.ValueColumn].ToString().Trim();
switch (UnitIdHiddenField.Value)
{
case "INT":
Regex formatInt = new Regex("^[0-9]{1,21}$");
Match matchesInt = formatInt.Match(value.ToString());
if (!matchesInt.Success)
{
errors.Add(string.Format(Resources.ImportData.ErrorValueIntInFile, (i + 2).ToString(), value));
}
break;
case "PERCENT":
Regex formatPercent = new Regex("^(([1-9]{1,2}|10|20|30|40|50|60|70|80|90)([\\.\\,][0-9]{1,3})*|100)$");
Match matchesPercent = formatPercent.Match(value.ToString());
if (!matchesPercent.Success)
{
errors.Add(string.Format(Resources.ImportData.ErrorValuePercentInFile, (i + 2).ToString(), value));
}
break;
default:
Regex formatDecimal = new Regex("^[0-9]{1,18}([\\.\\,][0-9]{1,3})*$");
Match matchesDecimal = formatDecimal.Match(value.ToString());
if (!matchesDecimal.Success)
{
errors.Add(string.Format(Resources.ImportData.ErrorValueDecimalInFile, (i + 2).ToString(), value));
}
break;
}
theData.Measurement = Convert.ToDecimal(value);
}
//--verifiy item categories
if (theCategoryList.Count > 0)
{
theItemList = new List <string>();
foreach (Category theCategory in theCategoryList)
{
theItemList.Add(theRow[theCategory.ID].ToString().Trim());
}
itemCategories = string.Join(", ", theItemList.Select(item => item));
if (!theCombinatedList.Exists(t => t.ItemsList.Equals(itemCategories)))
{
errors.Add(string.Format(Resources.ImportData.ErrorDataInFile, (i + 2).ToString(), itemCategories));
continue;
}
theData.Detalle = itemCategories;
theData.Categories = theCombinatedList.Find(t => t.ItemsList.Trim().Equals(itemCategories)).CategoriesList;
//--verifiy if exists measurement in file
if (theList.Exists(m => m.Date == date && m.Detalle.Equals(theData.Detalle) && m.Categories.Equals(theData.Categories)))
{
continue;
}
//-- verify if exists measurement in BD
if (theMeasurementList.Exists(m => m.Date == theData.Date && m.Detalle.Equals(theData.Detalle) && m.Categories.Equals(theData.Categories)))
{
theData.TypeImport = "U";
theData.MeasurementIDsToReplace = string.Join(";", theMeasurementList.FindAll(m => m.Date == theData.Date && m.Detalle.Equals(theData.Detalle) && m.Categories.Equals(theData.Categories)).Select(d => d.MeasurementID));
}
else
{
theData.TypeImport = "I";
}
}
else
{
theData.Detalle = "";
theData.Categories = "";
//--verifiy if exists measurement in file
if (theList.Exists(m => m.Date == date))
{
continue;
}
//-- verify if exists measurement in BD
if (theMeasurementList.Exists(m => m.Date == theData.Date))
{
theData.TypeImport = "U";
theData.MeasurementIDsToReplace = string.Join(";", theMeasurementList.FindAll(m => m.Date == theData.Date).Select(d => d.MeasurementID));
}
else
{
theData.TypeImport = "I";
}
}
theList.Add(theData);
theItemList = null;
itemCategories = "";
date = DateTime.MinValue;
value = "";
}
if (errors.Count > 0)
{
ErrorFileRepeater.DataSource = errors;
ErrorFileRepeater.DataBind();
pnlErrorData.Visible = true;
ErrorFileLabel.Text = string.Format(Resources.ImportData.ErrorsInFile, FileUpload.FileName);
return;
}
if (theList.Count == 0)
{
SystemMessages.DisplaySystemWarningMessage(Resources.ImportData.NoDataInFile);
return;
}
pnlData.Visible = true;
pnlMeasurement.Visible = false;
DataGridView.DataSource = theList;
DataGridView.DataBind();
if (theCategoryList.Count == 0)
{
DataGridView.Columns[1].Visible = false;
}
Session["KpiMeasurementList"] = theList;
}
catch (Exception exc)
{
SystemMessages.DisplaySystemErrorMessage(exc.Message);
}
}