//=====================================
// PerformanceIndicatorForm: unassigned Performance Indicator select
public static List <PerformanceIndicator.Extend> UnassignedPerformanceIndicatorList(Guid PIFID, Guid GCAItemID)
{
LinqMicajahDataContext dc = new LinqMicajahDataContext();
Guid?refGCAId = null;
if (GCAItemID != Guid.Empty)
{
refGCAId = GCAItemID;
}
ISingleResult <Sp_SelectUnassignedPerformanceIndicatorResult> result = dc.Sp_SelectUnassignedPerformanceIndicator(LinqMicajahDataContext.InstanceId, PIFID, refGCAId);
List <PerformanceIndicator.Extend> lPI = new List <PerformanceIndicator.Extend>();
foreach (Sp_SelectUnassignedPerformanceIndicatorResult i in result)
{
PerformanceIndicator.Extend pi = new PerformanceIndicator.Extend();
pi.InstanceId = i.InstanceId == null ? Guid.Empty : (Guid)i.InstanceId;
pi.PerformanceIndicatorID = i.PerformanceIndicatorID == null ? Guid.Empty : (Guid)i.PerformanceIndicatorID;
pi.Name = i.Name.TrimEnd();
pi.Description = i.Description;
pi.Code = i.Code;
pi.GroupCategoryAspectID = i.GroupCategoryAspectID;
pi.Status = i.Status;
pi.Created = (DateTime)i.Created;
pi.Updated = i.Updated;
pi.SectorID = i.SectorID;
pi.Help = i.Help;
pi.RequirementID = i.RequirementID;
pi.SectorName = i.Sector;
pi.RequirementName = i.Requirement;
pi.GCAName = i.GCA;
lPI.Add(pi);
}
return(lPI);
}
protected void Page_Load(object sender, EventArgs e)
{
((Micajah.Common.Pages.MasterPage)Page.Master).VisibleApplicationLogo = false;
((Micajah.Common.Pages.MasterPage)Page.Master).VisibleBreadcrumbs = false;
((Micajah.Common.Pages.MasterPage)Page.Master).VisibleFooter = false;
((Micajah.Common.Pages.MasterPage)Page.Master).VisibleFooterLinks = false;
((Micajah.Common.Pages.MasterPage)Page.Master).VisibleHeader = false;
((Micajah.Common.Pages.MasterPage)Page.Master).VisibleHeaderLinks = false;
((Micajah.Common.Pages.MasterPage)Page.Master).VisibleLeftArea = false;
((Micajah.Common.Pages.MasterPage)Page.Master).VisibleMainMenu = false;
((Micajah.Common.Pages.MasterPage)Page.Master).VisibleHeaderLogo = false;
((Micajah.Common.Pages.MasterPage)Page.Master).VisibleSearchControl = false;
Bll.PerformanceIndicator.Extend pi = Bll.PerformanceIndicator.Get(PerformanceIndicatorID);
lblName.Text = String.IsNullOrEmpty(pi.Name) ? "---" : pi.Name;
lblCode.Text = String.IsNullOrEmpty(pi.Code) ? "---" : pi.Code;
lblAlias.Text = String.IsNullOrEmpty(pi.Alias) ? "---" : pi.Alias;
lblGCA.Text = String.IsNullOrEmpty(pi.GCAName) ? "---" : pi.GCAName;
lblSector.Text = String.IsNullOrEmpty(pi.SectorName) ? "---" : pi.SectorName;
lblRequirement.Text = String.IsNullOrEmpty(pi.RequirementName) ? "---" : pi.RequirementName;
lblDescription.Text = String.IsNullOrEmpty(pi.Description) ? "---" : pi.Description;
lblHelp.Text = String.IsNullOrEmpty(pi.Help) ? "---" : pi.Help;
}
protected void btnAdd_Click(object sender, EventArgs e)
{
//MathParser parser = new MathParser();
Guid?OrgLocationTypeID = null;
char y = 'A';
if (ddlLocationType.SelectedValue != String.Empty)
{
OrgLocationTypeID = new Guid(ddlLocationType.SelectedValue);
y = 'B';
}
//IList<PIMetricInfo> MetricInfo = Bll.PerformanceIndicator.GetPIReportValues(sOrgLocation.OrgLocationID, new Guid(ddlGroup.SelectedValue), int.Parse(ddlFrequency.SelectedValue), OrgLocationTypeID, (DateTime)rdpBeginDate.SelectedDate, (DateTime)rdpEndDate.SelectedDate);
List <Guid> OrgLocationIDs = new List <Guid>();
if (sOrgLocation.OrgLocationID != null)
{
OrgLocationIDs.Add((Guid)sOrgLocation.OrgLocationID);
}
List <Bll.PerformanceIndicator.Extend> PIs;
List <List <string> > Formulas;
Guid GroupID = new Guid(ddlGroup.SelectedValue);
Bll.PerformanceIndicator.GetFormulasWithRealValues((DateTime)rdpBeginDate.SelectedDate, (DateTime)rdpEndDate.SelectedDate, OrgLocationIDs, out PIs, out Formulas, GroupID);
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Create(1);
workbook.Version = ExcelVersion.Excel2007;
IWorksheet sheet = workbook.Worksheets[0];
int x = 1;
// 1 row
sheet.Range[GetCellRange(y, x)].Text = "Report:";
sheet.Range[GetCellRange(y, x)].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range[GetCellRange((char)(y + 1), x)].Text = txtTitle.Text;
sheet.Range[GetCellRange((char)(y + 1), x, (char)(y + 24), x)].Merge(false);
x++;
// 2 row
sheet.Range[GetCellRange(y, x)].Text = "Group:"; sheet.Range["A2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range[GetCellRange((char)(y + 1), x)].Text = ddlGroup.SelectedItem.Text;
sheet.Range[GetCellRange((char)(y + 2), x)].Text = "Period:";
sheet.Range[GetCellRange((char)(y + 2), x)].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
sheet.Range[GetCellRange((char)(y + 3), x)].Text = ((DateTime)rdpBeginDate.SelectedDate).ToShortDateString() + " - " + ((DateTime)rdpEndDate.SelectedDate).ToShortDateString();
x++;
// 3 row
sheet.Range[GetCellRange((char)(y + 1), x)].Text = "Org Location:";
sheet.Range[GetCellRange((char)(y + 2), x)].Text = sOrgLocation.OrgLocationFullName;
sheet.Range[GetCellRange((char)(y + 2), x, (char)(y + 24), x)].Merge(false);
x = x + 2;
// 5 row
if (OrgLocationTypeID != null)
{
sheet.Range[GetCellRange((char)(y - 1), x)].Text = "Location";
sheet.Range[GetCellRange((char)(y - 1), x)].CellStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
}
sheet.Range[GetCellRange(y, x)].Text = "Category";
sheet.Range[GetCellRange((char)(y + 1), x)].Text = "Aspect";
sheet.Range[GetCellRange((char)(y + 2), x)].Text = "Code";
sheet.Range[GetCellRange((char)(y + 3), x)].Text = "Name";
sheet.Range[GetCellRange((char)(y + 4), x)].Text = "Value";
sheet.Range[GetCellRange((char)(y + 5), x)].Text = "Unit Of Measure";
string cLocationName = String.Empty;
Guid? cCategory = Guid.Empty;
Guid? cAspect = Guid.Empty;
Guid cPI = Guid.Empty;
x++;
for (int i = 0; i < PIs.Count; i++)
{
Bll.PerformanceIndicator.Extend PI = PIs[i];;
List <string> PIFormulas = Formulas[i];
double val = MetricTrac.PerformanceIndicatorCalc.CalcStringFormula(PIFormulas);
string rLocationName = String.Empty;
Guid? rCategory = null;
Guid? rAspect = null;
string rCategoryName = String.Empty;
string rAspectName = String.Empty;
/*if (!String.IsNullOrEmpty(MetricInfo[i].LocationFullName))
* rLocationName = MetricInfo[i].LocationFullName;*/
if (rLocationName != cLocationName && OrgLocationTypeID != null)
{
x++;
sheet.Range[GetCellRange((char)(y - 1), x)].Text = rLocationName;
sheet.Range[GetCellRange((char)(y - 1), x)].CellStyle.Font.Underline = ExcelUnderline.Single;
sheet.Range[GetCellRange((char)(y - 1), x, (char)(y + 24), x)].Merge(false);
x++;
}
/*if ((MetricInfo[i].GCA1ID != null) && (MetricInfo[i].GCA2ID != null))
* {
* rCategory = MetricInfo[i].GCA1ID;
* rCategoryName = MetricInfo[i].GCA1Name;
* rAspect = MetricInfo[i].GCAID;
* rAspectName = MetricInfo[i].GCAName;
* }
* else
* if ((MetricInfo[i].GCA1ID != null) && (MetricInfo[i].GCA2ID == null))
* {
* rCategory = MetricInfo[i].GCAID;
* rCategoryName = MetricInfo[i].GCAName;
* }*/
if ((rCategory != cCategory) || (rAspect != cAspect))
{ // start gca row
x++;
sheet.Range[GetCellRange(y, x)].Text = rCategoryName;
sheet.Range[GetCellRange((char)(y + 1), x)].Text = rAspectName;
sheet.Range[GetCellRange(y, x)].CellStyle.Font.Underline = ExcelUnderline.Single;
sheet.Range[GetCellRange(y, x)].CellStyle.Font.Bold = true;
sheet.Range[GetCellRange((char)(y + 1), x)].CellStyle.Font.Underline = ExcelUnderline.Single;
x++;
}
Guid rPI = PI.PerformanceIndicatorID;
string rPIName = PI.Name;
if (cPI != rPI)
{
sheet.Range[GetCellRange((char)(y + 2), x)].Text = PI.Code;
sheet.Range[GetCellRange((char)(y + 3), x)].Text = PI.Name;
sheet.Range[GetCellRange((char)(y + 2), x)].CellStyle.Font.Bold = true;
sheet.Range[GetCellRange((char)(y + 3), x)].CellStyle.Font.Bold = true;
x++;
}
//sheet.Range[GetCellRange((char)(y + 2), x)].Text = MetricInfo[i].MetricCode;
//string MetricName = MetricInfo[i].MetricName;
/*if ((bool)MetricInfo[i].AllowCustomMetricNames && (!String.IsNullOrEmpty(MetricInfo[i].CustomMetricAlias) || !String.IsNullOrEmpty(MetricInfo[i].CustomMetricCode)) )
* {
* if (!String.IsNullOrEmpty(MetricInfo[i].CustomMetricAlias))
* MetricName += " " + MetricInfo[i].CustomMetricAlias + " ";
* if (!String.IsNullOrEmpty(MetricInfo[i].CustomMetricAlias) && !String.IsNullOrEmpty(MetricInfo[i].CustomMetricCode))
* MetricName += "-";
* if (!String.IsNullOrEmpty(MetricInfo[i].CustomMetricCode))
* MetricName += " " + MetricInfo[i].CustomMetricCode;
* }*/
//sheet.Range[GetCellRange((char)(y + 3), x)].Text = MetricName;
sheet.Range[GetCellRange((char)(y + 4), x)].Number = val;// double.Parse(MetricInfo[i].SumValue, System.Globalization.NumberStyles.Any);
//sheet.Range[GetCellRange((char)(y + 5), x)].Text = MetricInfo[i].UoMName;
x++;
cLocationName = rLocationName;
cCategory = rCategory;
cAspect = rAspect;
cPI = rPI;
}
// general
sheet.Range[GetCellRange(y, 1, (char)(y + 5), x + 1)].CellStyle.Font.FontName = "Calibri";
sheet.Range[GetCellRange(y, 1, (char)(y + 5), x + 1)].CellStyle.Font.Size = 11;
sheet.Range[GetCellRange(y, 1, (char)(y + 5), x + 1)].RowHeight = 15;
sheet.Range[GetCellRange(y, 1, (char)(y + 5), x + 1)].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
//label
sheet.Range[GetCellRange(y, 1, y, 2)].RowHeight = 21;
sheet.Range[GetCellRange((char)(y + 1), 1, (char)(y + 1), 2)].CellStyle.Font.Size = 16;
sheet.Range[GetCellRange((char)(y + 1), 1, (char)(y + 1), 2)].CellStyle.Font.Bold = true;
sheet.Range[GetCellRange((char)(y + 3), 2)].CellStyle.Font.Size = 16;
sheet.Range[GetCellRange((char)(y + 3), 2)].CellStyle.Font.Bold = true;
sheet.Range[GetCellRange((char)(y + 2), 3)].CellStyle.Font.Size = 16;
sheet.Range[GetCellRange((char)(y + 2), 3)].CellStyle.Font.Bold = true;
sheet.Range[GetCellRange(y, 5, (char)(y + 5), 5)].CellStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
// autofit
sheet.Range[GetCellRange(y, 1, (char)(y + 24), x + 1)].AutofitColumns();
sheet.Range[GetCellRange(y, 1, (char)(y + 24), x + 1)].AutofitRows();
workbook.SaveAs(txtTitle.Text + ".xlsx", ExcelSaveType.SaveAsXLS, Response, ExcelDownloadType.PromptDialog);
workbook.Close();
excelEngine.Dispose();
}
private static decimal GetValue(Bll.PerformanceIndicator.Extend pi, DateTime StartDate, DateTime EndDate)
{
if (string.IsNullOrEmpty(pi.VariableFormula))
{
return(0);
}
;
string Formula = pi.VariableFormula.Replace(" ", "");
List <string> m;
List <string> l;
int n; string s;
MetricTrac.Bll.PerformanceIndicator.ParseFormulaCodes(pi.VariableFormula, out m, out l);
foreach (string lid in l)
{
Guid OrgLocationID = Guid.Empty;
if (!string.IsNullOrEmpty(lid) && lid.StartsWith("l"))
{
OrgLocationID = MetricTrac.Bll.PerformanceIndicator.ParseGuidAsNumber(lid.Substring(1));
}
while ((n = Formula.IndexOf(lid)) > 0)
{
string func = MetricTrac.Bll.PerformanceIndicator.Functions[0];
s = Formula.Substring(n - func.Length, func.Length);
if (s != func)
{
return(0);
}
int mn = Formula.IndexOf(",", n);
if (mn < 0)
{
return(0);
}
if (mn - n != 17)
{
return(0);
}
mn++;
int dn = Formula.IndexOf(")", mn);
if (dn < 0)
{
return(0);
}
if (dn - mn != 17)
{
return(0);
}
s = Formula.Substring(mn, dn - mn);
Guid MetricID = MetricTrac.Bll.PerformanceIndicator.ParseGuidAsNumber(s);
if (MetricID == Guid.Empty)
{
return(0);
}
}
}
foreach (string mid in m)
{
Guid MetricID = Guid.Empty;
if (!string.IsNullOrEmpty(mid) && mid.StartsWith("m"))
{
MetricID = MetricTrac.Bll.PerformanceIndicator.ParseGuidAsNumber(mid.Substring(1));
}
}
return(0);
}
