//Export
        protected void BtnExportToExcel_Click(object sender, EventArgs e)
        {
            string clientID = string.Empty;

            clientID = new SurveyBL().GetPortalClientId().ToString();

            DataSet objDataSet = new SurveyBL().SurveyDisplay(clientID, Convert.ToDateTime(lblfirstdate.Text), Convert.ToDateTime(lblLastDate.Text));

            FileInfo rptFileName = new FileInfo(Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["Reports"].ToString()) + @"\Survey Responses from_" + lblfirstdate.Text.Replace("/", "-").Replace(":", "-") + ".xlsx");

            // If any file exists in this directory having name 'Sample1.xlsx', then delete it
            if (rptFileName.Exists)
            {
                rptFileName.Delete(); // ensures we create a new workbook
                rptFileName = new FileInfo(Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["Reports"].ToString()) + @"\Survey Responses from_" + lblfirstdate.Text.Replace("/", "-").Replace(":", "-") + ".xlsx");
            }


            if (objDataSet != null & objDataSet.Tables.Count > 0)
            {
                ExcelSheetGenerator objExcel = new ExcelSheetGenerator();
                objExcel.GenerateReportGraph(objDataSet, rptFileName, "Survey Responses", "UserName", Convert.ToDateTime(lblfirstdate.Text), Convert.ToDateTime(lblLastDate.Text));
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AppendHeader("Content-Disposition", "attachment; filename=\"" + "Examity Survey Responses Report From -" + lblfirstdate.Text.Replace("/", "-").Replace(":", "-") + " " + lblLastDate.Text.Replace("/", "-").Replace(":", "-") + "" + ".xlsx");
                Response.TransmitFile(rptFileName.ToString());
                Response.End();
            }
        }
Example #2
0
        //UnScheduled Report
        private void UnScheduledReport(DataTable dtUnSch)
        {
            DataTable objDt = dtUnSch;

            objDt.Merge(dtUnSch);

            objDt.AcceptChanges();
            // objDt.Columns.Remove("UserID");
            // objDt.Columns.Remove("row_number");

            objDt.Columns["CourseName"].ColumnName     = "Course Name";
            objDt.Columns["InstructorName"].ColumnName = "Instructor Name";
            objDt.Columns["ExamName"].ColumnName       = "Exam Name";
            objDt.Columns["FirstName"].ColumnName      = "Student First Name";
            objDt.Columns["LastName"].ColumnName       = "Student Last Name";
            objDt.Columns["EmailAddress"].ColumnName   = "Email Address";

            string Examsummary = ConfigurationManager.AppSettings["Reports"].ToString() + '\\' + "UnscheduledAppointmentReport" + DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss").Replace("/", "-").Replace(":", "-") + ".xls";

            if (File.Exists(Examsummary))
            {
                File.Delete(Examsummary);
            }
            FileInfo rptFileName = new FileInfo(Examsummary);

            //  this.DeleteHistoricFiles();

            ExcelSheetGenerator objExcel = new ExcelSheetGenerator();

            objExcel.GenerateReport(objDt, rptFileName, " UnscheduleAppointment Report ", "UserName");
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AppendHeader("Content-Disposition", "attachment; filename=" + "UnscheduledAppointmentReport" + DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss").Replace("/", "-") + ".xlsx");
            Response.TransmitFile(rptFileName.ToString());
            Response.End();
        }
        public void GenerateReport()
        {
            BEAdmin objBEAdmin = new BEAdmin();
            BAdmin  objBAdmin  = new BAdmin();

            objBEAdmin.strCourseID = GetCourseIds();
            objBEAdmin.IntUserID   = Convert.ToInt32(Session[EnumPageSessions.USERID]);
            objBAdmin.BGetExamSummaryReportDetails(objBEAdmin);

            DataTable objDt = objBEAdmin.DtResult;



            objDt.AcceptChanges();
            objDt.Columns.Remove("CourseID");
            objDt.Columns.Remove("ExamID");


            objDt.Columns["CourseName"].ColumnName              = "Course Name";
            objDt.Columns["Instructor Name"].ColumnName         = "Instructor Name";
            objDt.Columns["ExamName"].ColumnName                = "Exam Name";
            objDt.Columns["ExamStartDate"].ColumnName           = "Exam Start Date";
            objDt.Columns["ExamEndDate"].ColumnName             = "Exam End Date";
            objDt.Columns["StudentsEnrolled"].ColumnName        = "Total Students";
            objDt.Columns["ScheduledAppointments"].ColumnName   = "Scheduled Appointments";
            objDt.Columns["Unscheduledappointments"].ColumnName = "Unscheduled Appointments";


            string Examsummary = Server.MapPath(ConfigurationManager.AppSettings["Reports"].ToString()) + '\\' + "Schedule status" + DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss").Replace("/", "-").Replace(":", "-") + ".xls";

            if (File.Exists(Examsummary))
            {
                File.Delete(Examsummary);
            }
            FileInfo rptFileName = new FileInfo(Examsummary);

            //  this.DeleteHistoricFiles();

            ExcelSheetGenerator objExcel = new ExcelSheetGenerator();

            objExcel.GenerateReport(objDt, rptFileName, " Schedule status ", "UserName");

            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

            Response.AppendHeader("Content-Disposition", "attachment; filename=" + "Schedule status" + DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss").Replace("/", "-") + ".xlsx");


            Response.TransmitFile(rptFileName.ToString());
            Response.End();
        }
        public void GenerateReport()
        {
            BEAdmin objBEAdmin = new BEAdmin();
            BAdmin  objBAdmin  = new BAdmin();

            objBEAdmin.strCourseID = GetExamIds();
            objBEAdmin.IntUserID   = 0; //for admin
            objBAdmin.BGetAppointmentScheduleReportDetails(objBEAdmin);

            DataTable objDt = objBEAdmin.DtResult;


            objDt.Columns["ExamID"].ColumnName                  = "Exam ID";
            objDt.Columns["StudentFirstName"].ColumnName        = "Student First Name";
            objDt.Columns["StudentLastName"].ColumnName         = "Student Last Name";
            objDt.Columns["EmailAddress"].ColumnName            = "Email Address";
            objDt.Columns["CourseName"].ColumnName              = "Course Name";
            objDt.Columns["ExamName"].ColumnName                = "Exam Name";
            objDt.Columns["InstructorName"].ColumnName          = "Instructor Name";
            objDt.Columns["AppointmentCreationDate"].ColumnName = "Appointment Creation Date [EST]";
            objDt.Columns["AppointmentDate"].ColumnName         = "Appointment Date [EST]";
            objDt.Columns["ExamDuration"].ColumnName            = "Exam Duration";
            objDt.Columns["FairExamLevel"].ColumnName           = "Fair ExamLevel";
            objDt.Columns["StatusName"].ColumnName              = "Status";

            objDt.Columns.Remove("CourseID");


            string Examsummary = Server.MapPath(ConfigurationManager.AppSettings["Reports"].ToString()) + '\\' + "Schedule details" + DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss").Replace("/", "-").Replace(":", "-") + ".xls";

            if (File.Exists(Examsummary))
            {
                File.Delete(Examsummary);
            }
            FileInfo rptFileName = new FileInfo(Examsummary);

            //  this.DeleteHistoricFiles();

            ExcelSheetGenerator objExcel = new ExcelSheetGenerator();

            objExcel.GenerateReport(objDt, rptFileName, " Schedule details ", "UserName");

            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

            Response.AppendHeader("Content-Disposition", "attachment; filename=" + "Schedule details" + DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss").Replace("/", "-") + ".xlsx");


            Response.TransmitFile(rptFileName.ToString());
            Response.End();
        }
        public void GenerateReport()
        {
            int      clientID  = 0;
            SurveyBL objSurvey = new SurveyBL();

            clientID = objSurvey.GetPortalClientId();


            int examId = 0;

            if (!string.IsNullOrEmpty(txtExamID.Text))
            {
                examId = Convert.ToInt32(txtExamID.Text);
            }

            SurveyBL objBl = new SurveyBL();
            DataSet  ds    = objBl.GetSurveyIndividualReport(clientID.ToString(), txtStudentName.Text, examId, rdpFromDate.SelectedDate.Value, rdpToDate.SelectedDate.Value);



            FileInfo rptFileName = new FileInfo(Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["Reports"].ToString()) + @"\ Individual Responses from_" + rdpFromDate.SelectedDate.Value.ToString("MM/dd/yyyy HH:mm:ss").Replace("/", "-").Replace(":", "-") + ".xls");

            // If any file exists in this directory having name 'Sample1.xlsx', then delete it
            if (rptFileName.Exists)
            {
                rptFileName.Delete(); // ensures we create a new workbook
                rptFileName = new FileInfo(Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["Reports"].ToString()) + @"\ Individual Responses from_" + rdpFromDate.SelectedDate.Value.ToString("MM/dd/yyyy HH:mm:ss").Replace("/", "-").Replace(":", "-") + ".xls");
            }
            // this.DeleteHistoricFiles();

            if (ds != null & ds.Tables.Count > 0)
            {
                ExcelSheetGenerator objExcel = new ExcelSheetGenerator();
                objExcel.GenerateReport(ds.Tables[0], rptFileName, "Individual Responses", "UserName");
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AppendHeader("Content-Disposition", "attachment; filename=\"" + "Individual Responses Report From -" + rdpFromDate.SelectedDate.Value.ToString("MM/dd/yyyy").Replace("/", "-").Replace(":", "-") + " " + rdpToDate.SelectedDate.Value.ToString("MM/dd/yyyy").Replace("/", "-").Replace(":", "-") + "" + ".xlsx");
                Response.TransmitFile(rptFileName.ToString());
                Response.End();
                gReport.DataSource = ds.Tables[0];
            }
            else
            {
                gReport.DataSource = new object[0];
            }
        }
        public void SendEmail()
        {
            lblResult.Text = "";
            BEAdmin objBEAdmin = new BEAdmin();
            BAdmin  objBAdmin  = new BAdmin();

            objBEAdmin.strCourseID = GetCourseIds();
            objBEAdmin.IntUserID   = Convert.ToInt32(Session[EnumPageSessions.USERID]);
            objBAdmin.BGetExamSummaryReportDetails(objBEAdmin);

            DataTable objDt = objBEAdmin.DtResult;



            objDt.AcceptChanges();
            objDt.Columns.Remove("CourseID");
            objDt.Columns.Remove("ExamID");


            objDt.Columns["CourseName"].ColumnName              = "Course Name";
            objDt.Columns["Instructor Name"].ColumnName         = "Instructor Name";
            objDt.Columns["ExamName"].ColumnName                = "Exam Name";
            objDt.Columns["ExamStartDate"].ColumnName           = "Exam Start Date";
            objDt.Columns["ExamEndDate"].ColumnName             = "Exam End Date";
            objDt.Columns["StudentsEnrolled"].ColumnName        = "Total Students";
            objDt.Columns["ScheduledAppointments"].ColumnName   = "Scheduled Appointments";
            objDt.Columns["Unscheduledappointments"].ColumnName = "Unscheduled Appointments";


            FileInfo rptFileName = new FileInfo(Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["Reports"].ToString()) + @"\Schedule status from_" + DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss").Replace("/", "-").Replace(":", "-") + ".xls");

            // If any file exists in this directory having name 'Sample1.xlsx', then delete it
            if (rptFileName.Exists)
            {
                rptFileName.Delete(); // ensures we create a new workbook
                rptFileName = new FileInfo(Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["Reports"].ToString()) + @"\Schedule status from_" + DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss").Replace("/", "-").Replace(":", "-") + ".xls");
            }

            ExcelSheetGenerator objExcel = new ExcelSheetGenerator();

            objExcel.GenerateReport(objDt, rptFileName, "Schedule status _", "UserName");
            string ToEmail = txtEmail.Text;

            System.Net.Mail.MailMessage email = new System.Net.Mail.MailMessage();
            StringBuilder body = new StringBuilder();

            email.From = new MailAddress("*****@*****.**");
            email.To.Add(ToEmail.Replace(" ", ""));
            email.Subject = "Schedule status";
            body.Append("<table style='font-family:Helvetica;font-size:9pt;width:600px;'>");
            body.Append(@"<tr><td>Hi,<br/><br/>Please find the enclosed Schedule status report.
<br/><br/><br/>Thank you,<br/>Examity.<br/><b>***DO NOT REPLY TO THIS EMAIL***</b></td></tr>");
            body.Append("</table>");
            // Attachment goes here
            Attachment attachment = new Attachment(rptFileName.ToString());

            attachment.Name = "Schedule status" + ".xlsx";
            email.Attachments.Add(attachment);  //add the attachment

            email.Body       = body.ToString();
            email.IsBodyHtml = true;
            SmtpClient obj = new SmtpClient
            {
                Host                  = ConfigurationManager.AppSettings["smtpServer"],
                Port                  = Convert.ToInt32(ConfigurationManager.AppSettings["smtpPort"]),
                EnableSsl             = true,
                DeliveryMethod        = SmtpDeliveryMethod.Network,
                UseDefaultCredentials = false,
                Credentials           = new System.Net.NetworkCredential(ConfigurationManager.AppSettings["smtpUser"].ToString(), ConfigurationManager.AppSettings["Reportspassword"].ToString())
            };

            obj.Send(email);

            lblResult.Text = "<font color='Blue' size='4px'>" + "Report emailed sucessfully." + "</font>";
        }
Example #7
0
        public void GenerateReport()
        {
            BEAdmin objBEAdmin = new BEAdmin();
            BAdmin  objBAdmin  = new BAdmin();

            objBEAdmin.strCourseID = GetExamIds();


            objBEAdmin.IntUserID = 0; //for admin
            DateTime ST = Convert.ToDateTime(ExamStartRadDatePicker.SelectedDate);
            DateTime ED = Convert.ToDateTime(ExamEndRadDatePicker.SelectedDate);

            objBEAdmin.DtStartDate = ST.ToString("MM/dd/yyyy");
            objBEAdmin.DtEndDate   = ED.ToString("MM/dd/yyyy");
            objBAdmin.BGetTestResultReportDetails(objBEAdmin);


            if (gvReports.Items.Count > 0)
            {
                DataTable objDt = objBEAdmin.DtResult;



                objDt.AcceptChanges();


                objDt.Columns["ExamID"].ColumnName                  = "Exam ID";
                objDt.Columns["StudentFirstName"].ColumnName        = "Student First Name";
                objDt.Columns["StudentLastName"].ColumnName         = "Student Last Name";
                objDt.Columns["EmailAddress"].ColumnName            = "Email Address";
                objDt.Columns["CourseName"].ColumnName              = "Course Name";
                objDt.Columns["ExamName"].ColumnName                = "Exam Name";
                objDt.Columns["InstructorName"].ColumnName          = "Instructor Name";
                objDt.Columns["AppointmentCreationDate"].ColumnName = "Appointment Creation Date [EST]";
                objDt.Columns["AppointmentDate"].ColumnName         = "Appointment Date [EST]";
                objDt.Columns["ExamDuration"].ColumnName            = "Exam Duration";
                objDt.Columns["FairExamLevel"].ColumnName           = "Fair ExamLevel";
                objDt.Columns["StatusName"].ColumnName              = "Status";
                objDt.Columns["AlertCount"].ColumnName              = "Blue";
                objDt.Columns["Alert"].ColumnName       = "Blue Comments";
                objDt.Columns["Green"].ColumnName       = "Green Comments";
                objDt.Columns["GreenCount"].ColumnName  = "Green";
                objDt.Columns["Orange"].ColumnName      = "Yellow Comments";
                objDt.Columns["OrangeCount"].ColumnName = "Yellow";
                objDt.Columns["Red"].ColumnName         = "Red Comments";
                objDt.Columns["RedCount"].ColumnName    = "Red";


                objDt.Columns.Remove("CourseID");



                string Examsummary = Server.MapPath(ConfigurationManager.AppSettings["Reports"].ToString()) + '\\' + "Exam status" + DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss").Replace("/", "-").Replace(":", "-") + ".xls";
                if (File.Exists(Examsummary))
                {
                    File.Delete(Examsummary);
                }
                FileInfo rptFileName = new FileInfo(Examsummary);

                //  this.DeleteHistoricFiles();

                ExcelSheetGenerator objExcel = new ExcelSheetGenerator();
                objExcel.GenerateReport(objDt, rptFileName, " Exam status ", "UserName");

                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

                Response.AppendHeader("Content-Disposition", "attachment; filename=" + "Examstatus" + DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss").Replace("/", "-") + ".xlsx");


                Response.TransmitFile(rptFileName.ToString());
                Response.End();
            }
        }
        public void GenerateReport()
        {
            DateTime firstDate = GetFirstDateOfWeek(txtFromDate.SelectedDate.Value, DayOfWeek.Monday);
            DateTime lastDate  = GetLastDateOfWeek(txtFromDate.SelectedDate.Value, DayOfWeek.Sunday);

            BEReports objBEReports = new BEReports();
            BReports  objBReports  = new BReports();

            objBEReports.StartDate = firstDate;
            objBEReports.EndDate   = lastDate;
            objBReports.BGETLAUNCHTIMEREPORT(objBEReports);
            DataTable objDt = objBEReports.dsResult.Tables[0];

            objDt.Columns.Remove("LaunchTimeIn");
            objDt.Columns.Remove("ExamDate");

            objDt.Columns["ID"].ColumnName                    = "Exam ID";
            objDt.Columns["StudentName"].ColumnName           = "Student name";
            objDt.Columns["Coursename"].ColumnName            = "Course name";
            objDt.Columns["Examname"].ColumnName              = "Exam name";
            objDt.Columns["Authenticationstarted"].ColumnName = "Authentication start time";
            objDt.Columns["Authenticationended"].ColumnName   = "Exam start time";
            objDt.Columns["Launchtime"].ColumnName            = "Launch time";

            // DataRow dr1;

            // dr1 = objDt.NewRow();
            //// dr1[5] = "Average launch time";
            // dr1[6] = objBEReports.dsResult.Tables[2].Rows[0]["TotalAVG"].ToString();
            // objDt.Rows.Add(dr1);
            //PendingatAuditor

            // Access Your DataTable

            FileInfo rptFileName = new FileInfo(Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["Reports"].ToString()) + @"\launch time report from_" + DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss").Replace("/", "-").Replace(":", "-") + ".xls");

            // If any file exists in this directory having name 'Sample1.xlsx', then delete it
            if (rptFileName.Exists)
            {
                rptFileName.Delete(); // ensures we create a new workbook
                rptFileName = new FileInfo(Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["Reports"].ToString()) + @"\launch time report from_" + DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss").Replace("/", "-").Replace(":", "-") + ".xls");
            }
            this.DeleteHistoricFiles();

            ExcelSheetGenerator objExcel = new ExcelSheetGenerator();

            objExcel.GenerateReport(objDt, rptFileName, "Launch Time Report", "UserName");
            string ToEmail = txtEmail.Text;

            if (chkSendMail.Checked)
            {
                MailMessage   email = new MailMessage();
                StringBuilder body  = new StringBuilder();
                email.From = new MailAddress("*****@*****.**");
                email.To.Add(ToEmail.Replace(" ", ""));
                email.Subject = "launch time report from " + firstDate.ToString("MMM - dd") + " - " + lastDate.ToString("MMM - dd");
                body.Append("<table style='font-family:Helvetica;font-size:9pt;width:600px;'>");
                body.Append(@"<tr><td>Hi,<br/><br/>Please find the enclosed attachment report for  launch time report from " + firstDate.ToString("MMM - dd") + " - " + lastDate.ToString("MMM - dd") + @".
<br/><br/><br/><p style='font-size:10px;'>This is a post-only mailing. Replies to this message are not monitored or answered.</p></td></tr>");
                body.Append("</table>");
                // Attachment goes here
                Attachment attachment = new Attachment(rptFileName.ToString());
                attachment.Name = "launch time report from" + firstDate.ToString("MMM - dd") + " - " + lastDate.ToString("MMM - dd") + ".xlsx";
                email.Attachments.Add(attachment);      //add the attachment

                email.Body       = body.ToString();
                email.IsBodyHtml = true;
                SmtpClient obj = new SmtpClient
                {
                    Host                  = ConfigurationManager.AppSettings["smtpServer"],
                    Port                  = Convert.ToInt32(ConfigurationManager.AppSettings["smtpPort"]),
                    EnableSsl             = true,
                    DeliveryMethod        = SmtpDeliveryMethod.Network,
                    UseDefaultCredentials = false,
                    Credentials           = new System.Net.NetworkCredential(ConfigurationManager.AppSettings["smtpUser"].ToString(), ConfigurationManager.AppSettings["Reportspassword"].ToString())
                };
                obj.Send(email);
                Response.Redirect(Request.Url.AbsoluteUri);
            }
            else
            {
                // Response.ContentType = "application/octet-stream";
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AppendHeader("Content-Disposition", "attachment; filename=\"" + "launch time report from " + firstDate.ToString("MMM - dd") + " - " + lastDate.ToString("MMM - dd") + "" + ".xlsx");
                Response.TransmitFile(rptFileName.ToString());
                Response.End();
            }
        }
        //UnScheduled Report
        private void UnScheduledReport(DataTable dtUnSch, int mail)
        {
            lblResult.Text = "";
            DataTable objDt = dtUnSch;

            objDt.Merge(dtUnSch);

            objDt.AcceptChanges();
            // objDt.Columns.Remove("UserID");
            // objDt.Columns.Remove("row_number");

            objDt.Columns["CourseName"].ColumnName     = "Course Name";
            objDt.Columns["InstructorName"].ColumnName = "Instructor Name";
            objDt.Columns["ExamName"].ColumnName       = "Exam Name";
            objDt.Columns["FirstName"].ColumnName      = "Student First Name";
            objDt.Columns["LastName"].ColumnName       = "Student Last Name";
            objDt.Columns["EmailAddress"].ColumnName   = "Email Address";

            string Examsummary = Server.MapPath(ConfigurationManager.AppSettings["Reports"].ToString()) + '\\' + "UnscheduledAppointmentReport" + DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss").Replace("/", "-").Replace(":", "-") + ".xls";

            if (File.Exists(Examsummary))
            {
                File.Delete(Examsummary);
            }
            FileInfo rptFileName = new FileInfo(Examsummary);

            //  this.DeleteHistoricFiles();
            if (mail == 0)
            {
                ExcelSheetGenerator objExcel = new ExcelSheetGenerator();
                objExcel.GenerateReport(objDt, rptFileName, " UnscheduleAppointment Report ", "UserName");
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AppendHeader("Content-Disposition", "attachment; filename=" + "UnscheduledAppointmentReport" + DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss").Replace("/", "-") + ".xlsx");
                Response.TransmitFile(rptFileName.ToString());
                Response.End();
            }

            else if (mail == 1)
            {
                ExcelSheetGenerator objExcel = new ExcelSheetGenerator();
                objExcel.GenerateReport(objDt, rptFileName, "Unscheduled Appointment details _", "UserName");
                string ToEmail = txtEmail.Text;

                System.Net.Mail.MailMessage email = new System.Net.Mail.MailMessage();
                StringBuilder body = new StringBuilder();
                email.From = new MailAddress("*****@*****.**");
                email.To.Add(ToEmail.Replace(" ", ""));
                email.Subject = "Unscheduled appointment details";
                body.Append("<table style='font-family:Helvetica;font-size:9pt;width:600px;'>");
                body.Append(@"<tr><td>Hi,<br/><br/>Please find the enclosed unscheduled appointment details report.
<br/><br/><br/>Thank you,<br/>Examity.<br/><b>***DO NOT REPLY TO THIS EMAIL***</b></td></tr>");
                body.Append("</table>");
                // Attachment goes here
                Attachment attachment = new Attachment(rptFileName.ToString());
                attachment.Name = "Unscheduled appointment details" + ".xlsx";
                email.Attachments.Add(attachment);      //add the attachment

                email.Body       = body.ToString();
                email.IsBodyHtml = true;
                SmtpClient obj = new SmtpClient
                {
                    Host                  = ConfigurationManager.AppSettings["smtpServer"],
                    Port                  = Convert.ToInt32(ConfigurationManager.AppSettings["smtpPort"]),
                    EnableSsl             = true,
                    DeliveryMethod        = SmtpDeliveryMethod.Network,
                    UseDefaultCredentials = false,
                    Credentials           = new System.Net.NetworkCredential(ConfigurationManager.AppSettings["smtpUser"].ToString(), ConfigurationManager.AppSettings["Reportspassword"].ToString())
                };
                obj.Send(email);
                lblResult.Text = "<font color='Blue' size='4px'>" + "Report emailed sucessfully." + "</font>";
            }
        }
Example #10
0
        public void GenerateReport()
        {
            DateTime firstDate = Convert.ToDateTime(lblfirstdate.Text);
            DateTime lastDate  = Convert.ToDateTime(lblLastDate.Text);

            DataSet objResponse = new SurveyBL().SurveyDetails(firstDate.ToShortDateString(), lastDate.ToShortDateString(), hdnClientId.Value);

            DataTable dtSurvey = new DataTable();

            dtSurvey.Columns.Add("Id", typeof(string));
            dtSurvey.Columns.Add("AId", typeof(string));
            dtSurvey.Columns.Add("Name", typeof(string));
            dtSurvey.Columns.Add("From", typeof(string));
            dtSurvey.Columns.Add("Golive", typeof(string));

            for (int i = 0; i < objResponse.Tables[0].Rows.Count; i++)
            {
                dtSurvey.Rows.Add(-1, -1, "<strong>" + objResponse.Tables[0].Rows[i]["Name"].ToString() + "</strong>", "<strong>" + objResponse.Tables[0].Rows[i]["RatingFrom"].ToString() + "</strong>", "<strong>" + objResponse.Tables[0].Rows[i]["RatingGolive"].ToString() + "</strong>");
            }

            DataTable dtSurveyCounts = new DataTable();

            dtSurveyCounts.Columns.Add("Qid", typeof(string));
            dtSurveyCounts.Columns.Add("QText", typeof(string));
            dtSurveyCounts.Columns.Add("AnswerID", typeof(string));
            dtSurveyCounts.Columns.Add("AText", typeof(string));
            dtSurveyCounts.Columns.Add("FP", typeof(string));
            dtSurveyCounts.Columns.Add("GP", typeof(string));

            for (int k = 0; k < objResponse.Tables[2].Rows.Count; k++)
            {
                if (objResponse.Tables[1].Rows[k]["qid"].ToString() == objResponse.Tables[2].Rows[k]["qid"].ToString())
                {
                    dtSurveyCounts.Rows.Add(objResponse.Tables[1].Rows[k]["qid"].ToString(),
                                            objResponse.Tables[1].Rows[k]["Qtext"].ToString(),
                                            objResponse.Tables[1].Rows[k]["AnswerID"].ToString(),
                                            objResponse.Tables[1].Rows[k]["answertext"].ToString(),
                                            objResponse.Tables[1].Rows[k]["Percentage"].ToString(),
                                            objResponse.Tables[2].Rows[k]["Percentage"].ToString());
                }
            }

            for (int j = 0; j < dtSurveyCounts.Rows.Count; j++)
            {
                string Qid         = dtSurveyCounts.Rows[j]["Qid"].ToString();
                bool   Qidexists   = dtSurvey.Select().ToList().Exists(row => row["Id"].ToString() == Qid);
                string qtext       = "Q" + Qid + ". " + dtSurveyCounts.Rows[j]["QText"].ToString();
                bool   qtextexists = dtSurvey.Select().ToList().Exists(row => row["Name"].ToString() == qtext);
                if (!Qidexists && !qtextexists)
                {
                    dtSurvey.Rows.Add(Qid, 0, "<strong>" + qtext + "</strong>", string.Empty, string.Empty);
                    dtSurvey.Rows.Add(Qid, dtSurveyCounts.Rows[j]["AnswerID"].ToString(), dtSurveyCounts.Rows[j]["AText"].ToString(), dtSurveyCounts.Rows[j]["FP"].ToString() + "%", dtSurveyCounts.Rows[j]["GP"].ToString() + "%");
                }
                else
                {
                    bool Qidexists1 = dtSurvey.Select().ToList().Exists(row => row["Id"].ToString() == dtSurveyCounts.Rows[j]["Qid"].ToString());
                    bool Aidexists1 = dtSurvey.Select().ToList().Exists(row => row["AId"].ToString() == dtSurveyCounts.Rows[j]["AnswerID"].ToString());
                    if (Qidexists1 && !Aidexists1)
                    {
                        dtSurvey.Rows.Add(Qid, dtSurveyCounts.Rows[j]["AnswerID"].ToString(), dtSurveyCounts.Rows[j]["AText"].ToString(), dtSurveyCounts.Rows[j]["FP"].ToString() + "%", dtSurveyCounts.Rows[j]["GP"].ToString() + "%");
                    }
                }
            }
            gvResponses.DataSource = dtSurvey;
            gvResponses.DataBind();


            FileInfo rptFileName = new FileInfo(Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["Reports"].ToString()) + @"Survey details_" + firstDate.ToString("MM/dd/yyyy").Replace("/", "-").Replace(":", "-") + ".xls");

            // If any file exists in this directory having name 'Sample1.xlsx', then delete it
            if (rptFileName.Exists)
            {
                rptFileName.Delete(); // ensures we create a new workbook
                rptFileName = new FileInfo(Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["Reports"].ToString()) + @"\Survey details from_" + firstDate.ToString("MM/dd/yyyy").Replace("/", "-").Replace(":", "-") + ".xls");
            }
            // this.DeleteHistoricFiles();
            ExcelSheetGenerator objExcel = new ExcelSheetGenerator();

            objExcel.GenerateReport(dtSurvey, rptFileName, "Surveydetails", "UserName");

            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AppendHeader("Content-Disposition", "attachment; filename=\"" + " Survey details Report From -" + firstDate.ToString("MM/dd/yyyy").Replace("/", "-").Replace(":", "-") + " " + lastDate.ToString("MM/dd/yyyy").Replace("/", "-").Replace(":", "-") + "" + ".xlsx");
            Response.TransmitFile(rptFileName.ToString());
            Response.End();
        }