public static List <KPIMeasurements> GetKPIMeasurementCategoriesTimeByKpiId(int kpiId, string categoryId, string categoryItemId)
{
if (kpiId <= 0)
{
throw new ArgumentException(Resources.ImportData.ZeroKpiId);
}
List <KPIMeasurements> theList = new List <KPIMeasurements>();
KPIMeasurements theData = null;
try
{
KpiMeasurementDSTableAdapters.KpiMeasurementTableAdapter localAdapter = new KpiMeasurementDSTableAdapters.KpiMeasurementTableAdapter();
KpiMeasurementDS.KpiMeasurementDataTable theTable = localAdapter.GetKpiMeasurementCategoriesByKpiId(kpiId, categoryId, categoryItemId);
if (theTable != null && theTable.Rows.Count > 0)
{
foreach (KpiMeasurementDS.KpiMeasurementRow theRow in theTable)
{
theData = new KPIMeasurements(theRow.measurmentID, theRow.kpiID, theRow.date, theRow.measurement);
theData.Detalle = theRow.IsdetalleNull() ? "" : theRow.detalle;
theData.DataTime = KPIDataTimeBLL.GetKPIDataTimeFromValue(theData.Measurement);
theList.Add(theData);
}
}
}
catch (Exception exc)
{
log.Error("Error en GetKPIMeasurementCategoriesByKpiId para kpiId: " + kpiId, exc);
throw new ArgumentException(Resources.ImportData.GetKPIMeasurements);
}
return(theList);
}
protected void DataGridView_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.DataItem is KPIMeasurements)
{
KPIMeasurements theData = (KPIMeasurements)e.Row.DataItem;
Label valueLabel = (Label)e.Row.FindControl("ValueLabel");
switch (UnitIdHiddenField.Value)
{
case "TIME":
valueLabel.Text = theData.DataTime.TimeDescription;
break;
case "INT":
valueLabel.Text = Convert.ToInt32(theData.Measurement).ToString();
break;
default:
valueLabel.Text = theData.Measurement.ToString(CultureInfo.InvariantCulture);
break;
}
Label imageUpdate = (Label)e.Row.FindControl("ImageUpdate");
if (theData.TypeImport.Equals("U"))
{
imageUpdate.Style["display"] = "inline";
}
else
{
imageUpdate.Style["display"] = "none";
}
}
}
protected void KpiMeasurementGridView_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.DataItem is KPIMeasurements)
{
KPIMeasurements theData = (KPIMeasurements)e.Row.DataItem;
Label valueLabel = (Label)e.Row.FindControl("ValueLabel");
switch (UnitIdHiddenField.Value)
{
case "TIME":
valueLabel.Text = theData.DataTime.TimeDescription;
break;
case "INT":
valueLabel.Text = Convert.ToInt32(theData.Measurement).ToString();
break;
case "PERCENT":
valueLabel.Text = theData.Measurement.ToString(CultureInfo.InvariantCulture) + " %";
break;
case "MONEY":
valueLabel.Text = theData.Measurement.ToString(CultureInfo.InvariantCulture) + " " + CurrencyHiddenField.Value;
break;
default:
valueLabel.Text = theData.Measurement.ToString(CultureInfo.InvariantCulture);
break;
}
}
}
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);
}
}
protected void SaveButton_Click(object sender, EventArgs e)
{
List <KPIMeasurements> theList = new List <KPIMeasurements>();
KPIMeasurements theData = null;
try
{
foreach (RepeaterItem item in EnterDataRepeater.Items)
{
if (item.ItemType == ListItemType.Item || item.ItemType == ListItemType.AlternatingItem)
{
theData = new KPIMeasurements();
theData.Date = Convert.ToDateTime(DateTextBox.Text);
Label detalleLabel = (Label)item.FindControl("DetalleLabel");
HiddenField categoriesLabel = (HiddenField)item.FindControl("CategoriesLabel");
if (detalleLabel != null)
{
theData.Detalle = detalleLabel.Text;
theData.Categories = categoriesLabel.Value;
}
else
{
theData.Detalle = "";
theData.Categories = "";
}
HiddenField measurementIDsHiddenField = (HiddenField)item.FindControl("MeasurementIDsHiddenField");
theData.MeasurementIDsToReplace = measurementIDsHiddenField.Value;
if (UnitIdHiddenField.Value.Equals("TIME"))
{
DropDownList yearsCombobox = (DropDownList)item.FindControl("YearsCombobox");
DropDownList monthsCombobox = (DropDownList)item.FindControl("MonthsCombobox");
DropDownList daysCombobox = (DropDownList)item.FindControl("DaysCombobox");
DropDownList hoursCombobox = (DropDownList)item.FindControl("HoursCombobox");
DropDownList minutesCombobox = (DropDownList)item.FindControl("MinutesCombobox");
if (!yearsCombobox.SelectedValue.Equals("0") || !monthsCombobox.SelectedValue.Equals("0") ||
!daysCombobox.SelectedValue.Equals("0") || !hoursCombobox.SelectedValue.Equals("0") ||
!minutesCombobox.SelectedValue.Equals("0"))
{
theData.DataTime = new KPIDataTime(Convert.ToInt32(yearsCombobox.SelectedValue), Convert.ToInt32(monthsCombobox.SelectedValue),
Convert.ToInt32(daysCombobox.SelectedValue), Convert.ToInt32(hoursCombobox.SelectedValue), Convert.ToInt32(minutesCombobox.SelectedValue));
}
}
else
{
TextBox valueTextBox = (TextBox)item.FindControl("ValueTextBox");
if (!string.IsNullOrEmpty(valueTextBox.Text))
{
theData.Measurement = Convert.ToDecimal(valueTextBox.Text, CultureInfo.InvariantCulture);
}
}
if (theData.Measurement > 0 || theData.DataTime != null)
{
theList.Add(theData);
}
}
}
}
catch (Exception exc)
{
log.Error("Error al obtener los datos de EnterDataRepeater", exc);
SystemMessages.DisplaySystemErrorMessage(Resources.ImportData.ErrorToObtainData);
return;
}
try
{
KpiMeasurementBLL.InsertKpiMeasuerementImported(Convert.ToInt32(KPIIdHiddenField.Value), theList, TypeRadioButtonList1.SelectedValue);
}
catch (Exception exc)
{
SystemMessages.DisplaySystemErrorMessage(exc.Message);
return;
}
SystemMessages.DisplaySystemMessage(Resources.ImportData.RegisteredData);
BindGridView();
LoadFormEnterData();
}
