private void ViewReport()
{
litWarning.Text = string.Empty;
DateTime aggStartDate = ppAgg.SelectedPeriod;
DateTime repDate = ppRep.SelectedPeriod;
//first thing check if repDate is more than one year after aggStartDate - this causes errors in MakeReport (useCol will be zero because 12 is hard-coded)
if ((repDate - aggStartDate).TotalDays >= 365)
{
litWarning.Text = @"<div class=""warning"">The report date must be within one year of the aggregation start date.</div>";
return;
}
//simple checks
if (aggStartDate > repDate)
{
litWarning.Text = @"<div class=""warning"">The aggregation start date must precede the report date.</div>";
return;
}
DateTime maxRepDate = GetReportDateBasedOnGeneratedReports(aggStartDate);
if (repDate > maxRepDate)
{
litWarning.Text = string.Format(@"<div class=""warning"">The report is too far in the future. The maximum allowed period is {0:MMMM yyyy}.</div>", maxRepDate);
return;
}
//find out if today is within 4 business days of beginning of month
var sd = repDate.AddMonths(1);
var ed = sd.AddMonths(1);
DateTime businessDay = Utility.NextBusinessDay(sd, Utility.GetHolidays(sd, ed));
if (DateTime.Now.Date < businessDay)
{
litWarning.Text = @"<div class=""warning"">It's not the fourth business day of the reporting period. You should know the risk of using premature NNIN report</div>";
}
bool makeAggData = chkForceRefresh.Checked || NNINDA.CumulativeUserExists(repDate);
string xlsReportName = string.Format("AS{0:yyyyMM}_Report{1:yyyyMM}_{2}.xls", aggStartDate, repDate, litCurrentThreshold.Text);
string newfilepath = MakeReport(aggStartDate, repDate, makeAggData, xlsReportName);
Refresh();
}
private string MakeReport(DateTime aggStartDate, DateTime repDate, bool makeAggData, string xlsReportName)
{
//if the month is the aggStart use the blank template. Otherwise, use last months
string xlsBaseFileName;
if (aggStartDate == repDate)
{
xlsBaseFileName = "NNIN Data Blank.xls";
}
else
{
xlsBaseFileName = string.Format("AS{0:yyyyMM}_Report{1:yyyyMM}_{2}.xls", aggStartDate, repDate.AddMonths(-1), litCurrentThreshold.Text);
}
string xlsBaseFilePath = XlsFilePath + xlsBaseFileName;
string xlsReportPath = XlsFilePath + xlsReportName;
DateTime period = repDate;
DateTime sDate = period;
DateTime eDate = sDate.AddMonths(1);
DataSet ds = NNINDA.GetTablesWithMinimumMinutes(period, (int)ClientPrivilege.LabUser, makeAggData, Convert.ToDouble(litCurrentThreshold.Text) / 60);
ds.Tables[0].TableName = "TechnicalInterest_Hours";
ds.Tables[1].TableName = "OrgType_Hours";
ds.Tables[2].TableName = "TechnicalInterest_Users";
ds.Tables[3].TableName = "OrgType_Users";
ds.Tables[4].TableName = "TechnicalInterest_Fees";
ds.Tables[5].TableName = "OrgType_Fees";
DataColumn[] pk;
pk = new DataColumn[] { ds.Tables["TechnicalInterest_Fees"].Columns["TechnicalInterestID"] };
ds.Tables["TechnicalInterest_Fees"].PrimaryKey = pk;
pk = new DataColumn[] { ds.Tables["OrgType_Fees"].Columns["OrgTypeID"] };
ds.Tables["OrgType_Fees"].PrimaryKey = pk;
//get raw cumulative user data
DataTable dtCumUser = NNINDA.GetCumulativeUserAggregateData(aggStartDate, repDate);
DataTable dtReport = new DataTable();
dtReport.Columns.Add("BaseRow", typeof(int));
dtReport.Columns.Add("TotalRow", typeof(int));
dtReport.Columns.Add("RepType", typeof(string)); //fees, hours, cum,
dtReport.Columns.Add("ByType", typeof(string)); //orgtype, techid, none
dtReport.Columns.Add("DateOnly", typeof(bool)); //orgtype, techid, none
AddReportRow(dtReport, 10, 0, string.Empty, string.Empty, true);
AddReportRow(dtReport, 24, 43, "Hours", "TechnicalInterest", false);
AddReportRow(dtReport, 11, 43, "Hours", "OrgType", false);
AddReportRow(dtReport, 40, 0, string.Empty, string.Empty, true);
AddReportRow(dtReport, 69, 88, "Users", "TechnicalInterest", false);
AddReportRow(dtReport, 55, 88, "Users", "OrgType", false);
AddReportRow(dtReport, 85, 0, string.Empty, string.Empty, true);
AddReportRow(dtReport, 161, 180, "Fees", "TechnicalInterest", false);
AddReportRow(dtReport, 146, 180, "Fees", "OrgType", false);
AddReportRow(dtReport, 177, 0, string.Empty, string.Empty, true);
AddReportRow(dtReport, 116, 136, "CumUser", "TechnicalInterest", false);
AddReportRow(dtReport, 101, 136, "CumUser", "OrgType", false);
AddReportRow(dtReport, 133, 0, string.Empty, string.Empty, true);
AddReportRow(dtReport, 226, 0, "CumUser", "DemGender", false);
AddReportRow(dtReport, 232, 0, "CumUser", "DemEthnic", false);
AddReportRow(dtReport, 238, 0, "CumUser", "DemRace", false);
AddReportRow(dtReport, 247, 0, "CumUser", "DemDisability", false);
ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
ExcelFile SpreadSheet = new ExcelFile();
SpreadSheet.LoadXls(xlsBaseFilePath);
ExcelWorksheet ws = SpreadSheet.Worksheets["UM SSEL"];
DataRow[] fdr;
int lastVal, useCol = 0, useRow;
double hardTotals; //for recording internal time without formula
foreach (DataRow drReport in dtReport.Rows)
{
//write dates
for (int j = 0; j < 12; j++)
{
ws.Cells[Convert.ToInt32(drReport["BaseRow"]), j + 1].Value = aggStartDate.AddMonths(j);
if (aggStartDate.AddMonths(j) == repDate)
{
useCol = j + 1;
}
}
if (!Convert.ToBoolean(drReport["DateOnly"]))
{
hardTotals = 0;
if (drReport["RepType"].ToString() == "CumUser")
{
fdr = dtCumUser.Select(string.Format("TableName = '{0}'", drReport["ByType"]));
for (int j = 0; j < fdr.Length; j++)
{
useRow = drReport.Field <int>("BaseRow") + fdr[j].Field <int>("Value");
if (aggStartDate == repDate)
{
ws.Cells[useRow, useCol].Value = fdr[j]["Count"];
}
else
{
lastVal = Convert.ToInt32(ws.Cells[useRow, 1].Value);
for (int i = 2; i < useCol; i++)
{
if (!string.IsNullOrEmpty(ws.Cells[useRow, i].Formula))
{
string form = ws.Cells[useRow, i].Formula;
lastVal += Convert.ToInt32(form.Substring(form.IndexOf("+") + 1));
}
}
string formula = "=" + Convert.ToChar(64 + useCol) + (useRow + 1).ToString() + "+" + (Convert.ToInt32(fdr[j]["Count"]) - lastVal).ToString();
ws.Cells[useRow, useCol].Formula = formula;
}
hardTotals += Convert.ToDouble(fdr[j]["Count"]);
}
if (Convert.ToInt32(drReport["TotalRow"]) != 0)
{
ws.Cells[Convert.ToInt32(drReport["TotalRow"]), useCol].Value = hardTotals;
}
}
else
{
foreach (DataRow dr in ds.Tables[string.Format("{0}_{1}", drReport["ByType"], drReport["RepType"])].Rows)
{
useRow = drReport.Field <int>("BaseRow") + dr.Field <int>(drReport["ByType"].ToString() + "ID");
double val = Convert.ToDouble(dr[drReport["RepType"].ToString()]); //must use Convert.ToDouble here because dr[...] may be different types depending on the rep type (e.g. double or int)
ws.Cells[useRow, useCol].Value = val;
hardTotals += val;
}
ws.Cells[Convert.ToInt32(drReport["TotalRow"]), useCol].Value = hardTotals;
}
}
}
SpreadSheet.SaveXls(xlsReportPath);
SpreadSheet = null;
GC.Collect();
return(xlsReportPath);
}
private string MakeReport(DateTime aggStartDate, DateTime repDate, bool makeAggData, string xlsReportName)
{
//if the month is the aggStart use the blank template. Otherwise, use last months
string xlsBaseFileName;
if (aggStartDate == repDate)
{
xlsBaseFileName = "NNIN Data Blank.xls";
}
else
{
xlsBaseFileName = "AS" + aggStartDate.ToString("yyyyMM") + "_Report" + repDate.AddMonths(-1).ToString("yyyyMM") + ".xls";
}
string xlsBaseFilePath = xlsFilePath + xlsBaseFileName;
string xlsReportPath = xlsFilePath + xlsReportName;
DateTime period = repDate;
DateTime sDate = period;
DateTime eDate = sDate.AddMonths(1);
Compile compile = new Compile();
//this gets the aggregated costs for rooms and tools
DataSet dsCostTables = NNINDA.GetCostTables(period);
DataTable dtRoomCost = dsCostTables.Tables[0];
DataTable dtToolCost = dsCostTables.Tables[1];
//get Client info
DataSet ds = DataCommand()
.Param("Action", "GetAllTables")
.Param("Period", period)
.Param("Privs", (int)ClientPrivilege.LabUser)
.Param("MakeCumUser", makeAggData)
.FillDataSet("dbo.NNIN_Select");
ds.Tables[0].TableName = "ClientTechInt";
ds.Tables[1].TableName = "AcctOrgType";
ds.Tables[2].TableName = "TechnicalInterest_Hours";
ds.Tables[3].TableName = "OrgType_Hours";
ds.Tables[4].TableName = "TechnicalInterest_Users";
ds.Tables[5].TableName = "OrgType_Users";
ds.Tables[6].TableName = "TechnicalInterest_Fees";
ds.Tables[7].TableName = "OrgType_Fees";
DataColumn[] pk;
pk = new DataColumn[] { ds.Tables["TechnicalInterest_Fees"].Columns["TechnicalInterestID"] };
ds.Tables["TechnicalInterest_Fees"].PrimaryKey = pk;
pk = new DataColumn[] { ds.Tables["OrgType_Fees"].Columns["OrgTypeID"] };
ds.Tables["OrgType_Fees"].PrimaryKey = pk;
//calc fees by tech interest and orgtype
DataRow drTI, drOT;
DataRow[] fdr;
foreach (DataTable dt in dsCostTables.Tables)
{
foreach (DataRow dr in dt.Rows)
{
fdr = ds.Tables["ClientTechInt"].Select(string.Format("ClientID = {0}", dr["ClientID"]));
if (fdr.Length > 0)
{
drTI = ds.Tables["TechnicalInterest_Fees"].Rows.Find(fdr[0]["TechnicalInterestID"]);
drTI["Fees"] = drTI.Field <double>("Fees") + dr.Field <double>("TotalCalcCost");
fdr = ds.Tables["AcctOrgType"].Select(string.Format("AccountID = {0}", dr["AccountID"]));
if (fdr.Length > 0)
{
drOT = ds.Tables["OrgType_Fees"].Rows.Find(fdr[0]["OrgTypeID"]);
drOT["Fees"] = drOT.Field <double>("Fees") + dr.Field <double>("TotalCalcCost");
}
}
}
}
//get raw cumulative user data
DataCommand()
.Param("Action", "Aggregate")
.Param("sDate", aggStartDate)
.Param("eDate", repDate)
.FillDataSet(ds, "dbo.CumUser_Select", "CumUser");
DataTable dtReport = new DataTable();
dtReport.Columns.Add("BaseRow", typeof(int));
dtReport.Columns.Add("TotalRow", typeof(int));
dtReport.Columns.Add("RepType", typeof(string)); //fees, hours, cum
dtReport.Columns.Add("ByType", typeof(string)); //orgtype, techid, none
dtReport.Columns.Add("DateOnly", typeof(bool)); //orgtype, techid, none
AddReportRow(dtReport, new object[] { 10, 0, string.Empty, string.Empty, true });
AddReportRow(dtReport, new object[] { 24, 43, "Hours", "TechnicalInterest", false });
AddReportRow(dtReport, new object[] { 11, 43, "Hours", "OrgType", false });
AddReportRow(dtReport, new object[] { 40, 0, string.Empty, string.Empty, true });
AddReportRow(dtReport, new object[] { 69, 88, "Users", "TechnicalInterest", false });
AddReportRow(dtReport, new object[] { 55, 88, "Users", "OrgType", false });
AddReportRow(dtReport, new object[] { 85, 0, string.Empty, string.Empty, true });
AddReportRow(dtReport, new object[] { 161, 180, "Fees", "TechnicalInterest", false });
AddReportRow(dtReport, new object[] { 146, 180, "Fees", "OrgType", false });
AddReportRow(dtReport, new object[] { 177, 0, string.Empty, string.Empty, true });
AddReportRow(dtReport, new object[] { 116, 136, "CumUser", "TechnicalInterest", false });
AddReportRow(dtReport, new object[] { 101, 136, "CumUser", "OrgType", false });
AddReportRow(dtReport, new object[] { 133, 0, string.Empty, string.Empty, true });
AddReportRow(dtReport, new object[] { 226, 0, "CumUser", "DemGender", false });
AddReportRow(dtReport, new object[] { 232, 0, "CumUser", "DemEthnic", false });
AddReportRow(dtReport, new object[] { 238, 0, "CumUser", "DemRace", false });
AddReportRow(dtReport, new object[] { 247, 0, "CumUser", "DemDisability", false });
ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
ExcelFile SpreadSheet = new ExcelFile();
SpreadSheet.LoadXls(xlsBaseFilePath);
ExcelWorksheet ws = SpreadSheet.Worksheets["UM SSEL"];
int lastVal, useCol = 0, useRow;
double hardTotals; //for recording internal time without formula
foreach (DataRow drReport in dtReport.Rows)
{
//write dates
for (int j = 0; j < 12; j++)
{
ws.Cells[Convert.ToInt32(drReport["BaseRow"]), j + 1].Value = aggStartDate.AddMonths(j);
if (aggStartDate.AddMonths(j) == repDate)
{
useCol = j + 1;
}
}
if (!Convert.ToBoolean(drReport["DateOnly"]))
{
hardTotals = 0;
if (drReport["RepType"].ToString() == "CumUser")
{
fdr = ds.Tables["CumUser"].Select(string.Format("TableName = '{0}'", drReport["ByType"]));
for (int j = 0; j < fdr.Length; j++)
{
useRow = drReport.Field <int>("BaseRow") + fdr[j].Field <int>("Value");
if (aggStartDate == repDate)
{
ws.Cells[useRow, useCol].Value = fdr[j]["Count"];
}
else
{
lastVal = Convert.ToInt32(ws.Cells[useRow, 1].Value);
for (int i = 2; i < useCol; i++)
{
string form = ws.Cells[useRow, i].Formula;
if (!string.IsNullOrEmpty(form))
{
lastVal += Convert.ToInt32(form.Substring(form.IndexOf("+") + 1));
}
}
ws.Cells[useRow, useCol].Formula = "=" + Convert.ToChar(64 + useCol) + (useRow + 1).ToString() + "+" + (Convert.ToInt32(fdr[j]["Count"]) - lastVal).ToString();
}
hardTotals += fdr[j].Field <double>("Count");
}
if (Convert.ToInt32(drReport["TotalRow"]) != 0)
{
ws.Cells[Convert.ToInt32(drReport["TotalRow"]), useCol].Value = hardTotals;
}
}
else
{
foreach (DataRow dr in ds.Tables[string.Format("{0}_{1}", drReport["ByType"], drReport["RepType"])].Rows)
{
useRow = drReport.Field <int>("BaseRow") + dr.Field <int>(string.Format("{0}ID", drReport["ByType"]));
ws.Cells[useRow, useCol].Value = dr[drReport["RepType"].ToString()];
hardTotals += Convert.ToDouble(dr[drReport["RepType"].ToString()]);
}
ws.Cells[Convert.ToInt32(drReport["TotalRow"]), useCol].Value = hardTotals;
}
}
}
SpreadSheet.SaveXls(xlsReportPath);
SpreadSheet = null;
System.GC.Collect();
return(xlsReportPath);
}