public static DataTable GetCamperSummaryReport(int CampYearID, int FedID, string CampID_List, string StatusID_List)
{
DataTable dt = CamperApplicationDA.GetCamperSummaryReport(CampYearID, FedID, CampID_List, StatusID_List);
// we have to calculate total as a new column
int total = 0;
foreach (DataRow dr in dt.Rows)
{
for (int i = 1; i < dt.Columns.Count; i++)
{
if (!(dr[i] == DBNull.Value))
{
total += (int)dr[i];
}
}
dr[dt.Columns.Count - 1] = total;
total = 0;
}
// the default column name is -1 set in store procedure, we have to change it back
dt.Columns[dt.Columns.Count - 1].ColumnName = "Total";
return(dt);
}
private DataSet GetDataFromRepository()
{
var dtFedId = GetSelectedItems(chklistFed.Items);
var dtCampId = GetSelectedItems(chklistCamp.Items);
var dtStatusId = GetSelectedItems(chklistStatus.Items);
int TimesReceivedGrant = 0;
if (chkAllTimers.Checked)
{
TimesReceivedGrant += 1;
}
if (chk1stTimers.Checked)
{
TimesReceivedGrant += 2;
}
if (chk2ndTimers.Checked)
{
TimesReceivedGrant += 4;
}
if (chk3rdTimers.Checked)
{
TimesReceivedGrant += 8;
}
DataSet a = CamperApplicationDA.GetSessionLengthByCamp(Int32.Parse(ddlCampYear.SelectedValue), dtFedId, dtCampId, dtStatusId, TimesReceivedGrant);
return(a);
}
public static DataTable GetCamperCountByCamp(int CampYearID, ProgramType Program, string CampID_List, string StatusName_List, int UserID)
{
StatusName_List += ", [Total By Camp]";
DataTable dt = CamperApplicationDA.GetCamperCountByCamp(CampYearID, Program, CampID_List, StatusName_List, UserID);
Utility.CreateTotalColumnAndRow(dt, "Total By Status");
return(dt);
}
/// <summary>
/// This creates each data table + total table for each camp. It's designed to fit the paging mechanism
/// </summary>
private void CreateMainReport()
{
lblCampName.Text = param.CampsThatHaveDataDict.ElementAt(Int32.Parse(txtPage.Text) - 1).Value;
lblPage.Text = param.CampsThatHaveDataDict.Count.ToString();
lblPage2.Text = lblPage.Text;
DataTable dtTotal;
if (param.CamperOrg == CamperOrgType.EnrollmentConfirmationFJC || param.CamperOrg == CamperOrgType.EnrollmentConfirmationPartner)
{
DataTable dt = CamperApplicationDA.GetFJCCamperReport(param.CamperOrg, param.ProgramTypeID, param.FedID, param.CampYearID, param.CampsThatHaveDataDict.ElementAt(Int32.Parse(txtPage.Text) - 1).Key, param.StatusID_List);
dtTotal = CreateTotalTablePerCamp(dt);
gvPerCamp.DataSource = dt;
gvPerCamp.DataBind();
gvTotalPerCamp.DataSource = dtTotal;
gvTotalPerCamp.DataBind();
divPerCamp.Visible = true;
}
else if (param.CamperOrg == CamperOrgType.Synagogue)
{
DataTable dt = CamperApplicationDA.GetFJCCamperReport(param.CamperOrg, param.ProgramTypeID, param.FedID, param.CampYearID, param.CampsThatHaveDataDict.ElementAt(Int32.Parse(txtPage.Text) - 1).Key, param.StatusID_List);
dtTotal = CreateTotalTablePerSynag(dt);
gvPerSynag.DataSource = dt;
gvPerSynag.DataBind();
gvTotalPerSynag.DataSource = dtTotal;
gvTotalPerSynag.DataBind();
divPerSynag.Visible = true;
}
else if (param.CamperOrg == CamperOrgType.CamperContactInfo)
{
DataTable dt = CamperApplicationDA.GetCamperContactInfoData(UserRole, param.FedID, param.FedID_List, param.CampYearID, param.CampsThatHaveDataDict.ElementAt(Int32.Parse(txtPage.Text) - 1).Key, param.StatusID_List, param.TimesReceivedGrant);
gvCamperContactInfo.DataSource = dt;
gvCamperContactInfo.DataBind();
divCamperContactInfo.Visible = true;
}
else if (param.CamperOrg == CamperOrgType.CamperDetailReport)
{
DataTable dt = CamperApplicationDA.GetCamperDetailReport(UserRole, param.FedID, param.FedID_List, param.CampYearID, param.CampsThatHaveDataDict.ElementAt(Int32.Parse(txtPage.Text) - 1).Key, param.StatusID_List, param.TimesReceivedGrant);
gvCamperDetailReport.DataSource = dt;
gvCamperDetailReport.DataBind();
divCamperDetailReport.Visible = true;
}
}
public static DataSet GetCamperByState(int CampYearID, string FedID_List, string CampID_List, string StatusID_List, int TimesReceivedGrant)
{
using (DataSet ds = CamperApplicationDA.GetCamperByState(CampYearID, FedID_List, CampID_List, StatusID_List, TimesReceivedGrant))
{
DataSet dsOutput = TransformTablesForCamperSummaryByState(ds);
Utility.AssignTableNames(dsOutput, TimesReceivedGrant);
foreach (DataTable dt in dsOutput.Tables)
{
Utility.CreateTotalColumnAndRow(dt, "Total");
}
return(dsOutput);
}
}
/// <summary>
/// The function is created to filter out the selected camps that don't have selected status. Users could get annoyed by seeing
/// so man empty camps that has no matching status, so we do the extra step to filter out.
/// </summary>
/// <returns></returns>
private int CreateCampThatHaveDataDictionaryObject()
{
// Get all the camps that have data with selected status
DataTable dtCampHaveData = null;
// 2010-01-23 switch explosion. Strategy pattern is needed if one more switch is added
if (param.CamperOrg == CamperOrgType.Synagogue)
{
dtCampHaveData = CamperApplicationDA.GetSynagsListThatHaveData(param.CampYearID, param.CampID_List, param.StatusID_List, param.FedID);
}
else if (param.CamperOrg == CamperOrgType.EnrollmentConfirmationFJC || param.CamperOrg == CamperOrgType.EnrollmentConfirmationPartner)
{
//
if (UserRole == Role.FJCAdmin)
{
dtCampHaveData = CamperApplicationDA.GetCampsListThatHaveData(param.CampYearID, param.ProgramTypeID, param.CampID_List, param.StatusID_List, param.FedID, param.TimesReceivedGrant);
}
else if (UserRole == Role.FederationAdmin)
{
dtCampHaveData = CamperApplicationDA.GetCampsListThatHaveData(param.CampYearID, param.ProgramTypeID, param.CampID_List, param.StatusID_List, param.FedID, param.TimesReceivedGrant);
}
else
{
// 2010-01-20 Haven't tested other roles yet, used the most stringent condition, that is, with FedID
dtCampHaveData = CamperApplicationDA.GetCampsListThatHaveData(param.CampYearID, param.ProgramTypeID, param.CampID_List, param.StatusID_List, param.FedID, param.TimesReceivedGrant);
}
}
else if (param.CamperOrg == CamperOrgType.CamperContactInfo || param.CamperOrg == CamperOrgType.CamperDetailReport)
{
dtCampHaveData = CamperApplicationDA.GetCampsListThatHaveDataDAForCamperContactInfo(param.CampYearID, param.FedID_List, param.CampID_List, param.StatusID_List, param.TimesReceivedGrant);
}
string campIDHaveData_list = "";
foreach (DataRow dr in dtCampHaveData.Rows)
{
param.CampsThatHaveDataDict.Add(Convert.ToInt32(dr["CampID"]), dr["Name"].ToString());
campIDHaveData_list += dr["CampID"].ToString();
if (dtCampHaveData.Rows.IndexOf(dr) != dtCampHaveData.Rows.Count - 1)
{
campIDHaveData_list += ", ";
}
}
param.CampID_HaveData_List = campIDHaveData_list;
// Remember to save the changes back to session
Session["ReportParamCampersFJC"] = param;
return(param.CampsThatHaveDataDict.Count);
}
public static DataSet GetCamperSummaryReportByCamp(int CampYearID, string FedID_List, string CampID_List, string StatusID_List, int TimesReceivedGrant)
{
DataSet ds = CamperApplicationDA.GetCamperSummaryReportByCamp(CampYearID, FedID_List, CampID_List, StatusID_List, TimesReceivedGrant);
// Make all the tables have the same number of rows (camps)
DataSet dsOutput = TransformTablesForCamperSummaryReportByCamp(ds);
Utility.AssignTableNames(dsOutput, TimesReceivedGrant);
// Create the total row for each table
foreach (DataTable dt in dsOutput.Tables)
{
Utility.CreateTotalRow(dt, "Total");
}
return(dsOutput);
}
public static DataSet GetCamperTimesInCampCountByState(int CampYearID, string FedID_List, string StatusName_List, int TimesReceivedGrant)
{
StatusName_List += ", [Total By Program]";
DataSet ds = CamperApplicationDA.GetCamperCountByFed(CampYearID, FedID_List, StatusName_List, TimesReceivedGrant);
var nameList = new List <string>();
if ((TimesReceivedGrant & 1) > 0)
{
nameList.Add("AllCampers");
}
if ((TimesReceivedGrant & 2) > 0)
{
nameList.Add("1stYearCampers");
}
if ((TimesReceivedGrant & 4) > 0)
{
nameList.Add("2ndYearCampers");
}
if ((TimesReceivedGrant & 8) > 0)
{
nameList.Add("3rdYearCampers");
}
int i = 0;
foreach (string name in nameList)
{
ds.Tables[i].TableName = name;
i++;
}
foreach (DataTable dt in ds.Tables)
{
Utility.CreateTotalColumnAndRow(dt, "Total By Status");
}
return(ds);
}
public static DataTable GetDuplicateCampers(int CampYearID, string StatusID_List)
{
DataTable dt = CamperApplicationDA.GetDuplicateCampers(CampYearID, StatusID_List);
//var nameList = new List<string>();
//nameList.Add("AllCampers");
//int i = 0;
//foreach (string name in nameList)
//{
// ds.Tables[i].TableName = name;
// i++;
//}
//foreach (DataTable dt in ds.Tables)
//{
// Utility.CreateTotalColumnAndRow(dt, "Total By Status");
//}
return(dt);
}
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();
}
private DataTable GenerateDataTable()
{
DataSet ds = CamperApplicationDA.GetFJCAllocationData(Int32.Parse(ddlCampYear.SelectedValue));
DataTable dtMain = ds.Tables[0];
DataTable dtOverage = ds.Tables[1];
DataTable dtFirstSecondTimers = ds.Tables[2];
dtMain.Columns.Add(new DataColumn("FJC Standard", System.Type.GetType("System.Int32")));
dtMain.Columns.Add(new DataColumn("FJC Overage", System.Type.GetType("System.Int32")));
dtMain.Columns.Add(new DataColumn("Partner Allocated", System.Type.GetType("System.Int32"))); // need this for the correct column order in excel report
dtMain.Columns.Add(new DataColumn("Total Campers", System.Type.GetType("System.Int32")));
dtMain.Columns.Add(new DataColumn("First Time", System.Type.GetType("System.Int32")));
dtMain.Columns.Add(new DataColumn("Second Time", System.Type.GetType("System.Int32")));
foreach (DataRow dr in dtMain.Rows)
{
// This line of code is purely for excel report, because we have to make sure excel report's table columns follow the same order as in html gridview
dr["Partner Allocated"] = dr["PartnerAllocated"];
// first step, filter out those data that has not been selected
bool isSelected = false;
foreach (ListItem li in chklistFed.Items)
{
if (li.Selected)
{
if (li.Value == dr["FederationID"].ToString())
{
isSelected = true;
}
}
}
if (!isSelected)
{
dr.Delete();
}
else
{
// this federation is selected, so we must process the data
DataRow[] drows = dtOverage.Select("FederationID = " + dr["FederationID"]);
if (drows.Length > 0)
{
dr["FJC Overage"] = drows[0]["Overage"];
int FJCStandard = Convert.ToInt32(dr["FJCMatch"]) - Convert.ToInt32(dr["FJC Overage"]);
if (FJCStandard != 0)
{
dr["FJC Standard"] = FJCStandard;
}
}
else
{
dr["FJC Standard"] = dr["FJCMatch"];
}
// First timer and Second timer calculation
int firstTimer = 0;
int secondTimer = 0;
drows = dtFirstSecondTimers.Select("FederationID = " + dr["FederationID"]);
if (drows.Length > 0)
{
foreach (DataRow mydr in drows)
{
// 2010/01/07 Now there is an easier way to access first/seconder timer data in CamperApplications table
if (mydr["TimeInCamp"] is int)
{
int TimeInCamp = Convert.ToInt32(mydr["TimeInCamp"]);
if (TimeInCamp == 1)
{
firstTimer = Convert.ToInt32(mydr["CamperCount"]);
dr["First Time"] = firstTimer;
}
else if (TimeInCamp == 2)
{
secondTimer = Convert.ToInt32(mydr["CamperCount"]);
dr["Second Time"] = secondTimer;
}
}
}
}
dr["Total Campers"] = firstTimer + secondTimer;
}
}
DataRow newrow = dtMain.NewRow();
newrow[0] = "Total";
int total = 0;
for (int i = 1; i < dtMain.Columns.Count; i++)
{
try
{
total = Int32.Parse(dtMain.Compute(String.Format("SUM([{0}])", dtMain.Columns[i].ColumnName), "").ToString());
}
catch
{
total = 0;
}
newrow[i] = total;
}
dtMain.Rows.Add(newrow);
return(dtMain);
}