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