private void BindGridView() { List <KPIMeasurements> theList = new List <KPIMeasurements>(); try { if (UnitIdHiddenField.Value.Equals("TIME")) { theList = KpiMeasurementBLL.GetKPIMeasurementCategoriesTimeByKpiId(Convert.ToInt32(KPIIdHiddenField.Value), "", ""); } else { theList = KpiMeasurementBLL.GetKPIMeasurementCategoriesByKpiId(Convert.ToInt32(KPIIdHiddenField.Value), "", ""); } } catch (Exception exc) { SystemMessages.DisplaySystemErrorMessage(exc.Message); } KpiMeasurementGridView.DataSource = theList; KpiMeasurementGridView.DataBind(); if (theList.FindAll(i => !string.IsNullOrEmpty(i.Detalle)).Count > 0) { KpiMeasurementGridView.Columns[2].Visible = true; } else { KpiMeasurementGridView.Columns[2].Visible = false; } }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { List <KPIMeasurements> theList = new List <KPIMeasurements>(); try { if (Unit.Equals("TIME")) { theList = KpiMeasurementBLL.GetKPIMeasurementCategoriesTimeByKpiId(KpiId, CategoryId, CategoryItemId); } else { theList = KpiMeasurementBLL.GetKPIMeasurementCategoriesByKpiId(KpiId, CategoryId, CategoryItemId); } } catch (Exception exc) { SystemMessages.DisplaySystemErrorMessage(exc.Message); } MeasurementsGridView.DataSource = theList; MeasurementsGridView.DataBind(); if (theList.FindAll(i => !string.IsNullOrEmpty(i.Detalle)).Count > 0 && string.IsNullOrEmpty(CategoryItemId)) { MeasurementsGridView.Columns[1].Visible = true; } else { MeasurementsGridView.Columns[1].Visible = false; } } }
protected void ExportButton_Click(object sender, EventArgs e) { byte[] excelByteArray = null; try { decimal target = 0; string strategy = ""; string startingPeriod = ""; string endingPeriod = ""; int firstDayOfWeek = Artexacta.App.Configuration.Configuration.GetFirstDayOfWeek(); List <KpiChartData> kpiSeries = KpiMeasurementBLL.GetKPIMeasurementForChart(KpiId, CategoryId, CategoryItemId, firstDayOfWeek, ref strategy, ref target, ref startingPeriod, ref endingPeriod); bool hasTarget = target > -1; excelByteArray = GetChartAsExcelByteArray(kpiSeries, target, hasTarget); } catch (Exception ex) { log.Error("Error exporting chart data", ex); } if (excelByteArray == null) { return; } try { Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment; filename=\"KPIChart_" + KpiId + ".xlsx\""); Response.BinaryWrite(excelByteArray); } catch (Exception q) { log.Error("Error al exportar", q); SystemMessages.DisplaySystemErrorMessage("Error al exportar el libro de venta a Excel."); } finally { Response.Flush(); Response.End(); } }
protected void KpiMeasurementGridView_RowCommand(object sender, GridViewCommandEventArgs e) { string measurementId = e.CommandArgument.ToString(); if (e.CommandName.Equals("DeleteData")) { try { KpiMeasurementBLL.DeleteKpiMeasuerement(Convert.ToInt32(measurementId)); SystemMessages.DisplaySystemMessage(Resources.ImportData.DeletedData); } catch (Exception exc) { SystemMessages.DisplaySystemErrorMessage(exc.Message); return; } BindGridView(); } }
protected void SaveUploadDataButton_Click(object sender, EventArgs e) { List <KPIMeasurements> theList = null; try { theList = (List <KPIMeasurements>)Session["KpiMeasurementList"]; } catch (Exception exc) { log.Error("Error al obtener la informacion de KpiMeasurementList en session", exc); } if (theList == null) { SystemMessages.DisplaySystemErrorMessage(Resources.ImportData.NoDataInSession); return; } try { KpiMeasurementBLL.InsertKpiMeasuerementImported(Convert.ToInt32(KPIIdHiddenField.Value), theList, TypeRadioButtonList.SelectedValue); } catch (Exception exc) { SystemMessages.DisplaySystemErrorMessage(exc.Message); return; } SystemMessages.DisplaySystemMessage(Resources.ImportData.RegisteredData); pnlData.Visible = false; pnlMeasurement.Visible = true; DataGridView.DataSource = null; DataGridView.DataBind(); BindGridView(); Session["KpiMeasurementList"] = null; }
public static string VerifiyData(int kpiId, string date, string detalle, string categories) { string listIds = KpiMeasurementBLL.VerifyKPIMeasurements(kpiId, TextUtilities.GetStringToDateTime(date), detalle, categories); return(listIds); }
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(); }
public void BuildChart() { int kpiId = KpiId; string strategyId = ""; string startingPeriod = ""; string endingPeriod = ""; decimal target = 0; int firstDayOfWeek = Artexacta.App.Configuration.Configuration.GetFirstDayOfWeek(); List <KpiChartData> measurements = KpiMeasurementBLL.GetKPIMeasurementForChart(kpiId, CategoryId, CategoryItemId, firstDayOfWeek, ref strategyId, ref target, ref startingPeriod, ref endingPeriod); Dictionary <string, object> standardSerie = new Dictionary <string, object>(); Dictionary <string, object> targetStandardSerie = new Dictionary <string, object>(); Dictionary <string, object> sumSerie = new Dictionary <string, object>(); Dictionary <string, object> targetSumSerie = new Dictionary <string, object>(); KPI objKpi = KPIBLL.GetKPIById(kpiId); bool hasTarget = target != -1; bool isSum = strategyId == "SUM"; bool isTargetUsable = false; //bool isSerieUsable = false; decimal sumMeasurement = 0; decimal sumTarget = 0; foreach (var item in measurements) { //if (!string.IsNullOrEmpty(startingPeriod) && item.Period == startingPeriod) // isSerieUsable = true; //if (isSerieUsable) standardSerie.Add(item.Period, item.Measurement); //else // standardSerie.Add(item.Period, null); if (isSum) { sumMeasurement = sumMeasurement + item.Measurement; //if (isSerieUsable) sumSerie.Add(item.Period, sumMeasurement); //else // sumSerie.Add(item.Period, null); if (hasTarget) { if (!string.IsNullOrEmpty(startingPeriod) && item.Period == startingPeriod) { isTargetUsable = true; } if (!string.IsNullOrEmpty(endingPeriod) && item.Period == endingPeriod) { isTargetUsable = false; } if (isTargetUsable) { sumTarget = sumTarget + target; targetSumSerie.Add(item.Period, sumTarget); } else { targetSumSerie.Add(item.Period, null); } } } if (hasTarget) { if (!string.IsNullOrEmpty(startingPeriod) && item.Period == startingPeriod) { isTargetUsable = true; } if (!string.IsNullOrEmpty(endingPeriod) && item.Period == endingPeriod) { isTargetUsable = false; } if (isTargetUsable) { targetStandardSerie.Add(item.Period, target); } else { targetStandardSerie.Add(item.Period, null); } } } List <Series> series = new List <Series>(); series.Add(new Series { Name = Resources.KpiDetails.ValuesLabel, Data = new Data(standardSerie.Values.ToArray <object>()) }); if (hasTarget) { series.Add(new Series { Name = Resources.KpiDetails.KpiTargetLabel, Color = System.Drawing.Color.Red, Data = new Data(targetStandardSerie.Values.ToArray <object>()) }); } DotNet.Highcharts.Highcharts chart = new DotNet.Highcharts.Highcharts(ClientID) .InitChart(new Chart() { Type = ChartTypes.Line }) .SetTitle(new Title() { Text = "" }) .SetXAxis(new XAxis { Categories = standardSerie.Keys.ToArray <string>() }) .SetSeries(series.ToArray()) .SetLegend(new Legend() { Layout = Layouts.Horizontal, Align = HorizontalAligns.Center, VerticalAlign = VerticalAligns.Bottom, BorderWidth = 0 }); if (objKpi.UnitID != "TIME") { //chart.SetTooltip(new Tooltip() //{ // ValueSuffix = " " + objKpi.uni.ToLower() + "s" //}); } else { chart.SetTooltip(new Tooltip() { Formatter = "function (){" + "return decimalToYYMMDDhhmm(this.y).toString('" + Resources.DataTime.YearsValueSingle + "','" + Resources.DataTime.YearsValue + "'," + "'" + Resources.DataTime.MonthsValueSingle + "'," + "'" + Resources.DataTime.MonthsValue + "'," + "'" + Resources.DataTime.DaysValueSingle + "'," + "'" + Resources.DataTime.DaysValue + "'," + "'" + Resources.DataTime.HoursValueSingle + "'," + "'" + Resources.DataTime.HoursValue + "'," + "'" + Resources.DataTime.MinutesValueSingle + "'," + "'" + Resources.DataTime.MinutesValue + "');" + "}" }); } ChartLiteral.Text = chart.ToHtmlString(); if (isSum) { series = new List <Series>(); series.Add(new Series { Name = Resources.KpiDetails.ValuesLabel, Data = new Data(sumSerie.Values.ToArray <object>()) }); if (hasTarget) { series.Add(new Series { Name = Resources.KpiDetails.KpiTargetLabel, Color = System.Drawing.Color.Red, Data = new Data(targetSumSerie.Values.ToArray <object>()) }); } chart = new DotNet.Highcharts.Highcharts(ClientID + "_sum") .InitChart(new Chart() { Type = ChartTypes.Line }) .SetTitle(new Title() { Text = "" }) .SetXAxis(new XAxis { Categories = standardSerie.Keys.ToArray <string>() }) .SetSeries(series.ToArray()) .SetLegend(new Legend() { Layout = Layouts.Horizontal, Align = HorizontalAligns.Center, VerticalAlign = VerticalAligns.Bottom, BorderWidth = 0 }); if (objKpi.UnitID != "TIME") { //chart.SetTooltip(new Tooltip() //{ // ValueSuffix = " " + objKpi.uni.ToLower() + "s" //}); } else { chart.SetTooltip(new Tooltip() { Formatter = "function (){" + "return decimalToYYMMDDhhmm(this.y).toString('" + Resources.DataTime.YearsValueSingle + "','" + Resources.DataTime.YearsValue + "'," + "'" + Resources.DataTime.MonthsValueSingle + "'," + "'" + Resources.DataTime.MonthsValue + "'," + "'" + Resources.DataTime.DaysValueSingle + "'," + "'" + Resources.DataTime.DaysValue + "'," + "'" + Resources.DataTime.HoursValueSingle + "'," + "'" + Resources.DataTime.HoursValue + "'," + "'" + Resources.DataTime.MinutesValueSingle + "'," + "'" + Resources.DataTime.MinutesValue + "');" + "}" }); } SumChartLiteral.Text = chart.ToHtmlString(); SumChartPanel.Visible = true; } }