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