private DataSet GenerateDataTable()
{
string FedID_List = "";
foreach (ListItem li in chklistFed.Items)
{
if (li.Selected)
{
if (FedID_List == "")
{
FedID_List = li.Value;
}
else
{
FedID_List += ", " + li.Value;
}
}
}
string StatusName_List = "";
foreach (ListItem li in chklistStatus.Items)
{
if (li.Selected)
{
if (StatusName_List == "")
{
StatusName_List = String.Format("[{0}]", li.Text.Trim());
}
else
{
StatusName_List += String.Format(", [{0}]", li.Text.Trim());
}
}
}
int TimesReceivedGrant = 0;
if (chkAllTimers.Checked)
{
TimesReceivedGrant += 1;
}
if (chk1stTimers.Checked)
{
TimesReceivedGrant += 2;
}
if (chk2ndTimers.Checked)
{
TimesReceivedGrant += 4;
}
if (chk3rdTimers.Checked)
{
TimesReceivedGrant += 8;
}
return(CamperApplicationBL.GetCamperCountByFed(Int32.Parse(ddlCampYear.SelectedValue), FedID_List, StatusName_List, TimesReceivedGrant));
}
public static DataTable GetDuplicateCampers(Role myUserRole, int CampYearID, string StatusID_List)
{
DataTable dt = null;
if (myUserRole == Role.FJCAdmin)
{
dt = CamperApplicationBL.GetDuplicateCampers(CampYearID, StatusID_List);
}
return(dt);
}
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();
}
private static DataTable GenerateDataTable(ReportParamCampersFJC param)
{
return(CamperApplicationBL.GetCamperSummaryReport(param.CampYearID, param.FedID, param.CampID_List, param.StatusID_List));
}
private DataTable GenerateDataTable()
{
// Prepare the necessary data before query the database
string CampID_List = "";
foreach (ListItem li in chklistCamp.Items)
{
if (li.Selected)
{
if (CampID_List == "")
{
CampID_List = li.Value;
}
else
{
CampID_List += ", " + li.Value;
}
}
}
string StatusName_List = "";
foreach (ListItem li in chklistStatus.Items)
{
if (li.Selected)
{
if (StatusName_List == "")
{
StatusName_List = String.Format("[{0}]", li.Text.Trim());
}
else
{
StatusName_List += String.Format(", [{0}]", li.Text.Trim());
}
}
}
DataTable dt;
ProgramType pt;
string FedID_List = "";
foreach (ListItem li in ddlProgram.Items)
{
if (li.Selected)
{
if (FedID_List == "")
{
FedID_List = li.Value;
}
else
{
FedID_List += ", " + li.Value;
}
}
}
if (ddlProgram.SelectedValue == "0")
{
pt = ProgramType.CIP;
}
else if (ddlProgram.SelectedValue == "1")
{
pt = ProgramType.JWest;
}
else
{
pt = ProgramType.All;
}
// We have to know if the user is FJC or Fed Admin. For Fed Admin, we can only show camps that are associated with specific Federation
// There is one to one relationship between Fed Admin and a Federation, so UserID of FedAdmin is enough to know FedID
if (UserRole == Role.FederationAdmin)
{
dt = CamperApplicationBL.GetCamperCountByCamp(Int32.Parse(ddlCampYear.SelectedValue), pt, CampID_List, StatusName_List, Convert.ToInt32(Session["UserID"]));
}
else if (UserRole == Role.FJCAdmin)
{
dt = CamperApplicationBL.GetCamperCountByCamp(Int32.Parse(ddlCampYear.SelectedValue), pt, CampID_List, StatusName_List, -1);
}
else if (UserRole == Role.CampDirector)
{
dt = CamperApplicationBL.GetCamperCountByCamp(Int32.Parse(ddlCampYear.SelectedValue), pt, CampID_List, StatusName_List, -1);
}
else
{
// Other possible roles, use the most stringent rule first
dt = CamperApplicationBL.GetCamperCountByCamp(Int32.Parse(ddlCampYear.SelectedValue), pt, CampID_List, StatusName_List, Convert.ToInt32(Session["UserID"]));
}
return(dt);
}
private DataSet GenerateDataSet()
{
string FedID_List = "";
foreach (ListItem li in chklistFed.Items)
{
if (li.Selected)
{
if (FedID_List == "")
{
FedID_List = li.Value;
}
else
{
FedID_List += ", " + li.Value;
}
}
}
if (FedID_List == "")
{
FedID_List = "3, 4";
}
string CampID_List = "";
foreach (ListItem li in chklistCamp.Items)
{
if (li.Selected)
{
if (CampID_List == "")
{
CampID_List = li.Value;
}
else
{
CampID_List += ", " + li.Value;
}
}
}
string StatusID_List = "";
foreach (ListItem li in chklistStatus.Items)
{
if (li.Selected)
{
if (StatusID_List == "")
{
StatusID_List = String.Format("{0}", li.Value.Trim());
}
else
{
StatusID_List += String.Format(", {0}", li.Value.Trim());
}
}
}
int TimesReceivedGrant = 0;
if (chkAllTimers.Checked)
{
TimesReceivedGrant += 1;
}
if (chk1stTimers.Checked)
{
TimesReceivedGrant += 2;
}
if (chk2ndTimers.Checked)
{
TimesReceivedGrant += 4;
}
if (chk3rdTimers.Checked)
{
TimesReceivedGrant += 8;
}
return(CamperApplicationBL.GetCamperByState(Int32.Parse(ddlCampYear.SelectedValue), FedID_List, CampID_List, StatusID_List, TimesReceivedGrant));
}
