public static DataSet GetCamperDetailReportInBatch(Role UserRole, int FedID, string FedID_List, int CampYearID, string CampID_List, string StatusID_List, int TimesReceivedGrant)
{
DataTable dt;
dt = CamperApplicationDA.GetCamperDetailReportInBatch(UserRole, FedID, FedID_List, CampYearID, CampID_List, StatusID_List, TimesReceivedGrant);
var ds = new DataSet();
// now, create each table per camp, and store each table into a dataset
if (dt.Rows.Count > 0)
{
int LastCampID = Convert.ToInt32(dt.Rows[0]["CampID"]);
int CampID = LastCampID;
DataTable dtTemp = dt.Clone();
foreach (DataRow dr in dt.Rows)
{
CampID = Convert.ToInt32(dr["CampID"]);
if (CampID != LastCampID)
{
ds.Tables.Add(dtTemp);
dtTemp = null;
dtTemp = dt.Clone();
}
LastCampID = CampID;
dtTemp.ImportRow(dr);
}
// the last table must also be added because it never goes into the if condition right above
ds.Tables.Add(dtTemp);
}
// Delete the first column, because we don't need to display it
foreach (DataTable dtt in ds.Tables)
{
dtt.Columns.RemoveAt(0);
}
return(ds);
}
protected void btnReport_Click(object sender, EventArgs e)
{
DataSet ds;
// Work sheet #1 - Data grouped by camp
// the data content section of the report
if (param.CamperOrg == CamperOrgType.CamperContactInfo)
{
ds = CamperApplicationBL.GetCamperContactInfoReportInBatch(UserRole, param.FedID, param.FedID_List, param.CampYearID, param.CampID_List, param.StatusID_List, param.TimesReceivedGrant);
}
else if (param.CamperOrg == CamperOrgType.CamperDetailReport)
{
ds = CamperApplicationBL.GetCamperDetailReportInBatch(UserRole, param.FedID, param.FedID_List, param.CampYearID, param.CampID_List, param.StatusID_List, param.TimesReceivedGrant);
}
else
{
ds = CamperApplicationBL.GetFJCCamperReportInBatch(param.CamperOrg, param.ProgramTypeID, param.FedID, param.CampYearID, param.CampID_HaveData_List, param.StatusID_List, param.TimesReceivedGrant);
}
string templateFile = Server.MapPath(@"~/Docs/Templates/CamperDetailReport.xls");
string workFileDir = Server.MapPath(@"~/Docs");
// Make a excel report
ExcelLite.SetLicense("EL6N-Z669-AZZG-3LS7");
ExcelFile excel = new ExcelFile();
excel.LoadXls(templateFile);
ExcelWorksheet ws = excel.Worksheets["Sheet2"];
//We start at first row, because for ExcelLite control, the header row is not included
int BEGIN_COLUMN_INDEX;
if (param.CamperOrg == CamperOrgType.CamperContactInfo || param.CamperOrg == CamperOrgType.CamperDetailReport)
{
BEGIN_COLUMN_INDEX = 0;
}
else
{
BEGIN_COLUMN_INDEX = 1;
}
const int CAMP_NAME_MERGED_CELL_NUMBER = 6;
const int REPORT_HEADER_CELL_NUMBER = 6;
const int REPORT_SUB_HEADER_CELL_NUMBER = 4;
int iRow = 1;
// Global artistic seeting
ws.Columns[0].Width = 20 * 20;
// Create Report Header
CellStyle styleReportHeader = new CellStyle();
styleReportHeader.Font.Color = System.Drawing.Color.Blue;
styleReportHeader.Font.Size = 22 * 20;
styleReportHeader.Font.Weight = ExcelFont.BoldWeight;
CellRange ReportHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER);
ReportHeader.Merged = true;
ReportHeader.Style = styleReportHeader;
var fileName = "report";
ReportHeader.Value = fileName = GetReportName(param.CamperOrg);
ws.Name = "By Camp";
//
iRow += 1;
// Create Report SubHeader
CellStyle styleSubHeader = new CellStyle();
styleSubHeader.Font.Size = 16 * 20;
styleSubHeader.Font.Weight = ExcelFont.BoldWeight;
CellRange SubHeader = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER);
SubHeader.Merged = true;
SubHeader.Value = String.Format("Camp Year: {0} Generated on {1}", param.CampYear, DateTime.Now);
SubHeader.Style = styleSubHeader;
iRow += 2;
CellStyle cs = new CellStyle();
//cs.Font.Color = System.Drawing.Color.Red;
cs.Font.Size = 18 * 20;
cs.Font.Weight = ExcelFont.BoldWeight;
CellStyle csFirst = new CellStyle();
csFirst.FillPattern.SetSolid(Color.Yellow);
csFirst.Font.Weight = ExcelFont.BoldWeight;
csFirst.WrapText = true;
ws.Columns[0].Width = 20 * 256;
CellStyle tableHeaderStyle = new CellStyle();
tableHeaderStyle.Font.Weight = ExcelFont.BoldWeight;
tableHeaderStyle.FillPattern.SetSolid(Color.DarkGray);
CellStyle redText = new CellStyle();
redText.Font.Color = Color.Red;
foreach (DataTable dt in ds.Tables)
{
CellRange cr = ws.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, CAMP_NAME_MERGED_CELL_NUMBER);
cr.Merged = true;
cr.Value = dt.Rows[0]["Camp Name"].ToString();
//if (param.CamperOrg == CamperOrgType.CamperContactInfo)
//{
// cr.Value = dt.Rows[0]["Camp Name"].ToString();
//}
//else
//{
// cr.Value = param.CampsThatHaveDataDict.ElementAt(ds.Tables.IndexOf(dt)).Value;
//}
cr.Style = cs;
ws.Rows[iRow].Height = 25 * 20;
iRow += 1;
ws.Rows[iRow].Height = 25 * 20;
CellRange crFirst = ws.Cells.GetSubrangeAbsolute(iRow, 0, iRow, 0);
if (param.CamperOrg != CamperOrgType.CamperContactInfo && param.CamperOrg != CamperOrgType.CamperDetailReport)
{
crFirst.Value = "Registered at Camp: Enter a Y / N here";
crFirst.Style = csFirst;
}
ws.InsertDataTable(dt, iRow, BEGIN_COLUMN_INDEX, true);
// 2012-10-28 Need to make allow contact emails stand out so admins won't contact campers who wish not to be contacted
if (param.CamperOrg == CamperOrgType.CamperContactInfo)
{
for (int i = 0; i <= dt.Rows.Count; i++)
{
if (ws.Cells[i + iRow, 14].Value.ToString() == "No")
{
ws.Cells[i + iRow, 14].Style = redText;
ws.Cells[i + iRow, 15].Style = redText;
ws.Cells[i + iRow, 16].Style = redText;
}
}
}
else
{
for (int i = 0; i <= dt.Rows.Count; i++)
{
//if (ws.Cells[i + iRow, 14].Value.ToString() == "No")
//{
// ws.Cells[i + iRow, 14].Style = redText;
// ws.Cells[i + iRow, 15].Style = redText;
//}
}
}
// loop through each column and 1.set the width of each colum, 2. set the header style of each column
for (int i = BEGIN_COLUMN_INDEX; i <= dt.Columns.Count; i++)
{
if (param.CamperOrg == CamperOrgType.CamperContactInfo)
{
ws.Cells[iRow, i].Style = tableHeaderStyle;
if (i == 2)
{
ws.Columns[i].Width = 22 * 256;
}
else if (i == 3)
{
ws.Columns[i].Width = 16 * 256;
}
else if (i == 4)
{
ws.Columns[i].Width = 16 * 256;
}
else if (i == 5)
{
ws.Columns[i].Width = 16 * 256;
}
else if (i == 7)
{
ws.Columns[i].Width = 23 * 256;
}
else if (i == 5)
{
ws.Columns[i].Width = 15 * 256;
}
else if (i == 11)
{
ws.Columns[i].Width = 26 * 256;
}
else if (i == 12)
{
ws.Columns[i].Width = 15 * 256;
}
else if (i == 13)
{
ws.Columns[i].Width = 10 * 256;
}
else if (i == 14)
{
ws.Columns[i].Width = 10 * 256;
}
else
{
ws.Columns[i].Width = 20 * 256;
}
}
else
{
ws.Cells[iRow, i].Style = tableHeaderStyle;
if (i == 3)
{
ws.Columns[i].Width = 30 * 256;
}
else if (i == 4)
{
ws.Columns[i].Width = 16 * 256;
}
else if (i == 5)
{
ws.Columns[i].Width = 16 * 256;
}
else if (i == 7)
{
ws.Columns[i].Width = 23 * 256;
}
else if (i == 5)
{
ws.Columns[i].Width = 15 * 256;
}
else if (i == 11)
{
ws.Columns[i].Width = 26 * 256;
}
else if (i == 12)
{
ws.Columns[i].Width = 15 * 256;
}
else
{
ws.Columns[i].Width = 20 * 256;
}
}
}
iRow += dt.Rows.Count + 2;
DataTable dtTotal;
if (param.CamperOrg == CamperOrgType.EnrollmentConfirmationFJC || param.CamperOrg == CamperOrgType.EnrollmentConfirmationPartner)
{
dtTotal = CreateTotalTablePerCamp(dt);
}
else if (param.CamperOrg == CamperOrgType.Synagogue)
{
dtTotal = CreateTotalTablePerSynag(dt);
}
else
{
dtTotal = null;
}
if (param.CamperOrg != CamperOrgType.CamperContactInfo && param.CamperOrg != CamperOrgType.CamperDetailReport)
{
ws.InsertDataTable(dtTotal, iRow, BEGIN_COLUMN_INDEX, true);
for (int i = BEGIN_COLUMN_INDEX; i <= dtTotal.Columns.Count; i++)
{
ws.Cells[iRow, i].Style = tableHeaderStyle;
}
iRow += dtTotal.Rows.Count + 3;
}
}
// ************************** second tab ********************************
// Second alternate sheet that list all rows as table format
// Camper Detail Report (for Program admin) doesn't need to show the whole list
if (param.CamperOrg != CamperOrgType.EnrollmentConfirmationFJC && param.CamperOrg != CamperOrgType.EnrollmentConfirmationPartner)
{
var ws2 = excel.Worksheets["Sheet1"];
ws2.Name = fileName;
DataTable dtAlternate;
if (param.CamperOrg == CamperOrgType.CamperContactInfo)
{
dtAlternate = CamperApplicationDA.GetCamperContactInfoReportInBatch(UserRole, param.FedID, param.FedID_List,
param.CampYearID, param.CampID_List, param.StatusID_List, param.TimesReceivedGrant);
}
else if (param.CamperOrg == CamperOrgType.CamperDetailReport)
{
dtAlternate = CamperApplicationDA.GetCamperDetailReportInBatch(UserRole, param.FedID, param.FedID_List,
param.CampYearID, param.CampID_List, param.StatusID_List, param.TimesReceivedGrant);
}
else
{
dtAlternate = CamperApplicationDA.GetFJCCamperReportInBatch(param.CamperOrg, param.ProgramTypeID,
param.FedID, param.CampYearID, param.CampID_HaveData_List, param.StatusID_List);
}
dtAlternate.Columns.RemoveAt(0); // We don't want the first column, which is CampID
iRow = 1;
ReportHeader = ws2.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_HEADER_CELL_NUMBER);
ReportHeader.Merged = true;
ReportHeader.Style = styleReportHeader;
ReportHeader.Value = GetReportName(param.CamperOrg);
ws2.Rows[iRow].Height = 25 * 20;
iRow += 1;
ws2.Rows[iRow].Height = 25 * 20;
// Create Report SubHeader
SubHeader = ws2.Cells.GetSubrangeAbsolute(iRow, BEGIN_COLUMN_INDEX, iRow, REPORT_SUB_HEADER_CELL_NUMBER);
SubHeader.Merged = true;
SubHeader.Value = "Camp Year: " + param.CampYear;
SubHeader.Style = styleSubHeader;
iRow += 2;
ws2.InsertDataTable(dtAlternate, iRow, BEGIN_COLUMN_INDEX, true);
// 2012-10-28 Need to make allow contact emails stand out so admins won't contact campers who wish not to be contacted
if (param.CamperOrg == CamperOrgType.CamperContactInfo)
{
for (int i = 0; i <= dtAlternate.Rows.Count; i++)
{
if (ws2.Cells[i + iRow, 14].Value.ToString() == "No")
{
ws2.Cells[i + iRow, 14].Style = redText;
ws2.Cells[i + iRow, 15].Style = redText;
ws2.Cells[i + iRow, 16].Style = redText;
}
}
}
ws2.Rows[iRow].Style = tableHeaderStyle;
if (param.CamperOrg == CamperOrgType.CamperContactInfo)
{
ws2.Columns[BEGIN_COLUMN_INDEX].Width = 16 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 1].Width = 28 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 2].Width = 28 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 3].Width = 14 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 4].Width = 16 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 5].Width = 23 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 10].Width = 26 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 11].Width = 15 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 14].Width = 25 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 15].Width = 25 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 16].Width = 25 * 256;
}
else
{
// Camper Detail Report
ws2.Columns[BEGIN_COLUMN_INDEX].Width = 15 * 256; // CampID
ws2.Columns[BEGIN_COLUMN_INDEX + 1].Width = 30 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 2].Width = 25 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 3].Width = 25 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 4].Width = 30 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 5].Width = 18 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 6].Width = 30 * 256; // Camp Name
ws2.Columns[BEGIN_COLUMN_INDEX + 9].Width = 22 * 256; // Session Datea
ws2.Columns[BEGIN_COLUMN_INDEX + 10].Width = 18 * 256; // Timer
ws2.Columns[BEGIN_COLUMN_INDEX + 12].Width = 25 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 13].Width = 25 * 256;
ws2.Columns[BEGIN_COLUMN_INDEX + 14].Width = 25 * 256;
}
}
else
{
// since there is no whole list report, we need to delete sheet1
excel.Worksheets[0].Delete();
}
excel.Worksheets.ActiveWorksheet = excel.Worksheets[0];
// Save to a file on the local file system
string filename = String.Format("\\{0}{1}{2}{3} {4}.xls", DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Millisecond, fileName);
string newFile = workFileDir + filename;
excel.SaveXls(newFile);
string[] strFileParts = newFile.Split(new string[] { "\\" }, StringSplitOptions.None);
//Display excel spreadsheet
this.Response.Clear();
this.Response.Buffer = true;
this.Response.AddHeader("Content-Disposition", "attachment; filename=" + strFileParts[strFileParts.Length - 1]);
this.Response.ContentType = "application/vnd.ms-excel";
this.Response.Charset = "";
if (newFile.Length == 0)
{
this.Response.Write("Error encountered - no spreadsheet to display");
}
else
{
this.Response.WriteFile(newFile);
}
Response.End();
}