using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using Microsoft.Reporting.WebForms;
using System.IO;
using System.Security.AccessControl;
using System.Net.Mail;
using System.Web.Security;
using Sapphire.Ohu.Web.Helper;

namespace OccuCare.Report.Pages
{
    public partial class ReportPDF : System.Web.UI.Page
    {
        public static string DefaultOrgId;
        private clConn objC
        {
            get
            {
                return new clConn();
            }
            set { }
        }

        private const string AntiXsrfTokenKey = "__AntiXsrfToken";
        private const string AntiXsrfUserNameKey = "__AntiXsrfUserName";
        private string _antiXsrfTokenValue;

        protected void Page_Init(object sender, EventArgs e)
        {
            var requestCookie = Request.Cookies[AntiXsrfTokenKey];
            Guid requestCookieGuidValue;
            if (requestCookie != null && Guid.TryParse(requestCookie.Value, out requestCookieGuidValue))
            {
                _antiXsrfTokenValue = requestCookie.Value;
                Page.ViewStateUserKey = _antiXsrfTokenValue;
            }
            else
            {
                _antiXsrfTokenValue = Guid.NewGuid().ToString("N");
                Page.ViewStateUserKey = _antiXsrfTokenValue;

                var responseCookie = new HttpCookie(AntiXsrfTokenKey)
                {
                    HttpOnly = true,
                    Value = _antiXsrfTokenValue
                };
                if (FormsAuthentication.RequireSSL && Request.IsSecureConnection)
                {
                    responseCookie.Secure = true;
                }
                Response.Cookies.Set(responseCookie);
            }

            Page.PreLoad += master_Page_PreLoad;
        }

        protected void master_Page_PreLoad(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                ViewState[AntiXsrfTokenKey] = Page.ViewStateUserKey;
                ViewState[AntiXsrfUserNameKey] = Context.User.Identity.Name ?? String.Empty;
            }
            else
            {
                if ((string)ViewState[AntiXsrfTokenKey] != _antiXsrfTokenValue
                    || (string)ViewState[AntiXsrfUserNameKey] != (Context.User.Identity.Name ?? String.Empty))
                {
                    throw new InvalidOperationException("Validation of Anti-XSRF token failed.");
                }
            }
        }


        protected void Page_Load(object sender, EventArgs e)
        {
            if (SecuritySession.OrgId == null)
            {
                ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert('Session Timeout');window.close();", true);
                //_MessageBox.Show("No Records found for the selected criteria", MessageType.Error);
                return;
            }
            CreatePDF();
        }

        private void CreatePDF()
        {
            Warning[] warnings;
            string[] streamIds;
            string mimeType = string.Empty;
            string encoding = string.Empty;
            string extension = "pdf";

            //ReportGlobalSettingsHelper rptGlobalSettingsHelper = new ReportGlobalSettingsHelper();
            ReportViewer viewer = new ReportViewer();
            viewer.ProcessingMode = ProcessingMode.Local;

            ReportDataSource ReportInfoDatasource = new ReportDataSource("ReportInfo", ReportSession.ReportDetail.SubReport[0].DataSet.Tables[0]);
            viewer.LocalReport.DataSources.Clear();
            viewer.LocalReport.DataSources.Add(ReportInfoDatasource);

            ReportDataSource datasource = new ReportDataSource(ReportSession.ReportDetail.DataSetName, ReportSession.ReportDetail.DataSet.Tables[0]);
            RDLCSubReportProcessing subReport = new RDLCSubReportProcessing(DefaultOrgId);
            subReport.AddSubReportDataSource(ReportInfoDatasource, viewer);
            viewer.LocalReport.DataSources.Add(datasource);

            if (ReportSession.ReportDetail.DataSetName2 != null && ReportSession.ReportDetail.DataSet2 != null)
            {
                ReportDataSource datasource1 = new ReportDataSource(ReportSession.ReportDetail.DataSetName2, ReportSession.ReportDetail.DataSet2.Tables[0]);
                viewer.LocalReport.DataSources.Add(datasource1);
            }

            viewer.LocalReport.ReportPath = Server.MapPath(ReportSession.ReportDetail.ReportLocationPath);
            viewer.LocalReport.SubreportProcessing += new SubreportProcessingEventHandler(LocalReport_SubreportProcessing);

            string deviceInfo = null;

            if (ReportSession.ReportDetail.PaperSize == "Legal")
                deviceInfo = @"<DeviceInfo><OutputFormat>PDF</OutputFormat><PageWidth>8.5in</PageWidth><PageHeight>14in</PageHeight></DeviceInfo>";

            byte[] bytes = viewer.LocalReport.Render("PDF", deviceInfo, out mimeType, out encoding, out extension, out streamIds, out warnings);

            string savepath;
            string ReportName = ReportSession.ReportDetail.CustomFileName;
            if (ReportSession.ReportDetail.IsCustomFile)
            {
                if (!string.IsNullOrEmpty(ReportSession.ReportDetail.CustomFolderName))
                    savepath = Server.MapPath("~/ReportPDF/" + ReportSession.ReportDetail.CustomFolderName);
                else
                    savepath = Server.MapPath("~/ReportPDF");
            }
            else if (!string.IsNullOrEmpty(ReportSession.ReportDetail.CustomFileName))
            {
                savepath = Server.MapPath("~/ReportPDF/TempReportForMail");
            }
            else
            {
                savepath = Server.MapPath("~/ReportPDF");
            }
            if (!Directory.Exists(savepath))
            {
                Directory.CreateDirectory(savepath);
                DirectoryInfo dir1 = new DirectoryInfo(savepath);
                System.Security.Principal.WindowsIdentity self = System.Security.Principal.WindowsIdentity.GetCurrent();
                DirectorySecurity ds = dir1.GetAccessControl();
                ds.AddAccessRule(new FileSystemAccessRule(self.Name,
                FileSystemRights.FullControl,
                InheritanceFlags.ObjectInherit |
                InheritanceFlags.ContainerInherit,
                PropagationFlags.None,
                AccessControlType.Allow));
                dir1.SetAccessControl(ds);
            }
            Guid UniqueID = Guid.NewGuid();
            FileStream fs;
            string FileMapPath;
            if (ReportSession.ReportDetail.IsCustomFile)
            {
                fs = new FileStream(Path.Combine(savepath, ReportSession.ReportDetail.CustomFileName + ".pdf"), FileMode.OpenOrCreate);
                fs.Write(bytes, 0, bytes.Length);
                fs.Close();
            }
            else if (!string.IsNullOrEmpty(ReportSession.ReportDetail.CustomFileName))
            {
                if (File.Exists(Path.Combine(savepath, ReportSession.ReportDetail.CustomFileName + ".pdf")))
                {
                    fs = new FileStream(Path.Combine(savepath, ReportSession.ReportDetail.CustomFileName + ".pdf"), FileMode.Open, FileAccess.Read);
                }
                else
                {
                    fs = new FileStream(Path.Combine(savepath, ReportSession.ReportDetail.CustomFileName + ".pdf"), FileMode.CreateNew);
                    fs.Write(bytes, 0, bytes.Length);
                }
                fs.Close();

                FileMapPath = savepath + @"\" + ReportSession.ReportDetail.CustomFileName + ".pdf";
                string filepath = @"..\..\..\" + FileMapPath.Substring(System.Web.HttpContext.Current.Request.PhysicalApplicationPath.Length).Replace("\\", "/");
                Response.Redirect(filepath, false);

                if (!string.IsNullOrEmpty(ReportSession.ReportDetail.CustomFileName))
                {
                    System.Net.Mail.MailMessage mail = new System.Net.Mail.MailMessage();
                    SmtpClient SmtpServer = new SmtpClient();
                    mail.From = new MailAddress("dev@occucare.co.in");
                    mail.To.Add("rajeshwar.rathod@vidyalayaschoolsoftware.com");
                    mail.Subject = "Test Mail - 1";
                    mail.Body = "mail with attachment";

                    System.Net.Mail.Attachment attachment;
                    attachment = new System.Net.Mail.Attachment(Server.MapPath("~/ReportPDF/TempReportForMail/" + ReportSession.ReportDetail.CustomFileName + ".pdf"));

                    mail.Attachments.Add(attachment);
                    SmtpServer.Host = "smtp.zoho.com";
                    SmtpServer.Port = 587;

                    SmtpServer.UseDefaultCredentials = false;
                    SmtpServer.Credentials = new System.Net.NetworkCredential("dev@occucare.co.in", "zoho@12345");
                    SmtpServer.EnableSsl = true;

                    SmtpServer.Send(mail);
                }
            }
            else
            {
                fs = new FileStream(Path.Combine(savepath, UniqueID + ".pdf"), FileMode.OpenOrCreate);
                fs.Write(bytes, 0, bytes.Length);
                fs.Close();
                FileMapPath = savepath + @"\" + UniqueID + ".pdf";
                string filepath = @"..\..\..\" + FileMapPath.Substring(System.Web.HttpContext.Current.Request.PhysicalApplicationPath.Length).Replace("\\", "/");
                Response.Redirect(filepath, false);
                if (Session["HealthCardCompanyEmail"] != null)
                {
                    if (!string.IsNullOrEmpty(Session["HealthCardCompanyEmail"].ToString()))
                    {
                        try
                        {

                            int Id = 2;
                            int MailTypeId = 2;
                            //if (ConfigurationHelper.GetUserConfigurationValue("Alert.EMailProvider").ToString() != null)
                            //{
                            //    if (ConfigurationHelper.GetUserConfigurationValue("Alert.EMailProvider").ToString() == "smtp")
                            //    {
                            //        Id = 1;
                            //        MailTypeId = 1;
                            //    }
                            //    else
                            //    {
                            //        Id = 2;
                            //        MailTypeId = 2;
                            //    }
                            //}
                            //else
                            //{
                            //    Id = 1;
                            //    MailTypeId = 1;
                            //}

                            String sqlQuery = "select SMTPPort,FromMail,SendPassword,SMTPServer,isnull(APIKey,'') as APIKey,isnull(SecretKey,'') as SecretKey from sAlert.tEmailConfiguration where OrgId=@rOrgId and MailTypeId=@rMailTypeId and Id = @rId";

                            DataSet Ds = objC.QueryDataReader(sqlQuery,
                                objC.GetSqlParam("@rOrgId", SqlDbType.Int, 4, ConfigurationManager.AppSettings["DefaultOrgId"].ToString()),
                                objC.GetSqlParam("@rMailTypeId", SqlDbType.Int, 4, MailTypeId.ToString()),
                                objC.GetSqlParam("@rId", SqlDbType.Int, 4, Id.ToString())
                                );

                            int SMTPPort = int.Parse(Ds.Tables[0].Rows[0]["SMTPPort"].ToString());
                            string FromMail = Ds.Tables[0].Rows[0]["FromMail"].ToString();
                            string SendPassword = Ds.Tables[0].Rows[0]["SendPassword"].ToString();
                            string SMTPServer = Ds.Tables[0].Rows[0]["SMTPServer"].ToString();
                            string APIKey = Ds.Tables[0].Rows[0]["APIKey"].ToString();
                            string SecretKey = Ds.Tables[0].Rows[0]["SecretKey"].ToString();

                            String sqlQuery1 = "select Isnull(Template,'') Template,Isnull(MailSubject,'') MailSubject From sAlert.tEventTemplate where OrgId=@rOrgId and AlertModeId=@rAlertModeId and EventId =@rEventId ";
                            string Message = "Your Health Card Report";
                            string Subject = "Your Health Card Report";

                            DataSet Ds1 = objC.QueryDataReader(sqlQuery1,
                                objC.GetSqlParam("@rOrgId", SqlDbType.Int, 4, ConfigurationManager.AppSettings["DefaultOrgId"].ToString()),
                                objC.GetSqlParam("@rAlertModeId", SqlDbType.Int, 4, "1"),
                                objC.GetSqlParam("@rEventId", SqlDbType.Int, 4, "1")
                                );

                            if (Ds1 != null && Ds1.Tables.Count > 0 && Ds1.Tables[0].Rows.Count > 0)
                            {
                                Message = Ds1.Tables[0].Rows[0]["Template"].ToString();
                                Subject = Ds1.Tables[0].Rows[0]["MailSubject"].ToString();
                            }
                            //objC.SendExternalEmail(Session["HealthCardCompanyEmail"].ToString(), "Health Card", "Health Card", "", "", filepath);
                            objC.SendExternalEmail(Id, Session["HealthCardCompanyEmail"].ToString(), FromMail, Subject, Message, "", "", filepath, APIKey, SecretKey, SMTPPort, SMTPServer, SendPassword);
                            //objC.CreateEmailLog(Session["HealthCardCompanyEmail"].ToString(), Message, Subject, "Custom", 1, Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["DefaultOrgId"].ToString()));
                            Session["HealthCardCompanyEmail"] = null;
                        }
                        catch
                        {
                            Session["HealthCardCompanyEmail"] = null;
                        }
                    }
                }
            }
        }

        public void LocalReport_SubreportProcessing(object sender, SubreportProcessingEventArgs e)
        {
            string ExaminationIdText;
            //string Code = "";
            string PolicyId;
            string IncidentId;
            string PermitNumber;
            switch (e.ReportPath)
            {
                case "EmploymentDetail":
                    ExaminationIdText = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("Employment", FetchHealthCardEmployment(ExaminationIdText)));
                    break;
                case "HealthCardFullGeneralSub":
                    ExaminationIdText = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("HealthCardFull_General", FetchHealthCardGeneral(ExaminationIdText)));
                    break;
                case "HealthCardFullGeneralSub_New2":
                    ExaminationIdText = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("HealthCardFull_General", FetchHealthCardGeneral(ExaminationIdText)));
                    break;
                case "HealthCardFullTestSub":
                    ExaminationIdText = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("HealthCardFull_Test", FetchHealthCardFullTestSub(ExaminationIdText)));
                    break;
                case "PulmanaryResult":
                    ExaminationIdText = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("PulmonaryResult", FetchPulmonaryResult(ExaminationIdText)));
                    break;
                case "AudiometryTest":
                    ExaminationIdText = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("Audiometry", FetchAudiometry(ExaminationIdText)));
                    break;
                case "HealthCardFullGeneralSub_Filter":
                    ExaminationIdText = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("HealthCardFull_General", FetchHealthCardGeneral(ExaminationIdText)));
                    break;
                case "ClaimStatus":
                    PolicyId = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("ClaimStatus", FetchClaimStatus(PolicyId)));
                    break;
                case "ClaimRelationship":
                    PolicyId = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("ClaimRelationship", FetchClaimRelationship(PolicyId)));
                    break;
                case "ClaimCare":
                    PolicyId = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("ClaimCare", FetchClaimCare(PolicyId)));
                    break;
                case "ClaimTopHospitals":
                    PolicyId = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("ClaimTopHospitals", FetchClaimTopHospitals(PolicyId)));
                    break;
                case "ClaimTopAilments":
                    PolicyId = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("ClaimTopAilments", FetchClaimTopAilments(PolicyId)));
                    break;
                case "ClaimTreatment":
                    PolicyId = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("ClaimTreatment", FetchClaimTreatment(PolicyId)));
                    break;
                case "ClaimTopAilmentCare":
                    PolicyId = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("ClaimTopAilmentCare", FetchClaimTopAilmentCare(PolicyId)));
                    break;
                case "ClaimTopAilmentRelationship":
                    PolicyId = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("ClaimTopAilmentRelationship", FetchClaimTopAilmentRelationship(PolicyId)));
                    break;
                case "ApproversList":
                    PermitNumber = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("ApproversList", FetchPermitNumber(PermitNumber)));
                    break;
                case "IncidentTeamFormation":
                    IncidentId = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("IncidentTeamFormation", CFTFromation(IncidentId)));
                    break;
                case "InitialProblemDescription":
                    IncidentId = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("InitialProblemDescription", ProblemDescription(IncidentId)));
                    break;
                case "ContainmentAction":
                    IncidentId = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("ContainmentAction", ContainmentAction(IncidentId)));
                    break;
                case "RootCauseInvestigation":
                    IncidentId = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("RootCauseInvestigation", RootCauseInvestigation(IncidentId)));
                    break;
                case "RootCauseValidation":
                    IncidentId = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("RootCauseValidation", RootCauseValidation(IncidentId)));
                    break;
                case "IncidentCorrectiveAction":
                    IncidentId = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("IncidentCorrectiveAction", IncidentCorrectiveAction(IncidentId)));
                    break;
                case "CorrectiveActionValidation":
                    IncidentId = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("CorrectiveActionValidation", CorrectiveActionValidation(IncidentId)));
                    break;
                case "PreventOccurrence":
                    IncidentId = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("PreventOccurrence", PreventOccurrence(IncidentId)));
                    break;
                case "TeamRecognition":
                    IncidentId = e.Parameters[0].Values[0].ToString();
                    e.DataSources.Add(new ReportDataSource("TeamRecognition", TeamRecognition(IncidentId)));
                    break;

            }
        }
        public DataTable CFTFromation(string IncidentId)
        {
            DataSet ds = new DataSet();
            string sql = "sSafety.pRptIncidentTeamFormationGet";

            ds = objC.QuerySQL(sql,
                  objC.GetSqlParam("@rIncidentId", SqlDbType.Int, IncidentId)
                );

            return ds.Tables[0];
        }
        public DataTable TeamRecognition(string IncidentId)
        {
            DataSet ds = new DataSet();
            string sql = "sSafety.pRptIncidentTeamRecognitionGet";

            ds = objC.QuerySQL(sql,
                  objC.GetSqlParam("@rIncidentId", SqlDbType.Int, IncidentId)
                );

            return ds.Tables[0];
        }
        public DataTable PreventOccurrence(string IncidentId)
        {
            DataSet ds = new DataSet();
            string sql = "sSafety.pRptPreventOccurrenceGet";

            ds = objC.QuerySQL(sql,
                  objC.GetSqlParam("@rIncidentId", SqlDbType.Int, IncidentId)
                );

            return ds.Tables[0];
        }
        public DataTable CorrectiveActionValidation(string IncidentId)
        {
            DataSet ds = new DataSet();
            string sql = "sSafety.pRptCorrectiveActionValidationGet";

            ds = objC.QuerySQL(sql,
                  objC.GetSqlParam("@rIncidentId", SqlDbType.Int, IncidentId)
                );

            return ds.Tables[0];
        }
        public DataTable IncidentCorrectiveAction(string IncidentId)
        {
            DataSet ds = new DataSet();
            string sql = "sSafety.pRptIncidentCorrectiveActionGet";

            ds = objC.QuerySQL(sql,
                  objC.GetSqlParam("@rIncidentId", SqlDbType.Int, IncidentId)
                );

            return ds.Tables[0];
        }
        public DataTable RootCauseValidation(string IncidentId)
        {
            DataSet ds = new DataSet();
            string sql = "sSafety.pRptRootCauseValidationGet";

            ds = objC.QuerySQL(sql,
                  objC.GetSqlParam("@rIncidentId", SqlDbType.Int, IncidentId)
                );

            return ds.Tables[0];
        }

        public DataTable RootCauseInvestigation(string IncidentId)
        {
            DataSet ds = new DataSet();
            string sql = "sSafety.pRptRootCauseInvestigationGet";

            ds = objC.QuerySQL(sql,
                  objC.GetSqlParam("@rIncidentId", SqlDbType.Int, IncidentId)
                );

            return ds.Tables[0];
        }
        public DataTable ContainmentAction(string IncidentId)
        {
            DataSet ds = new DataSet();
            string sql = "sSafety.pRptContainmentActionGet";

            ds = objC.QuerySQL(sql,
                  objC.GetSqlParam("@rIncidentId", SqlDbType.Int, IncidentId)
                );

            return ds.Tables[0];
        }
        public DataTable ProblemDescription(string IncidentId)
        {
            DataSet ds = new DataSet();
            string sql = "sSafety.pRptInitialProblemDescriptionGet";

            ds = objC.QuerySQL(sql,
                  objC.GetSqlParam("@rIncidentId", SqlDbType.Int, IncidentId)
                );

            return ds.Tables[0];
        }

        public DataTable FetchPermitNumber(string PermitNumber)
        {
            DataSet ds = new DataSet();
            string sql = "[sPermit].[pApproverPermitListGet]";

            ds = objC.QuerySQL(sql,
                objC.GetSqlParam("@PermitNumber", SqlDbType.NVarChar, PermitNumber)
                );

            return ds.Tables[0];
        }
        public DataTable FetchClaimStatus(string PolicyId)
        {
            DataSet ds = new DataSet();
            string sql = "[sSupport].[pRptClaimStatus]";

            ds = objC.QuerySQL(sql,
                objC.GetSqlParam("@rPolicyId", SqlDbType.Int, PolicyId)
                );

            return ds.Tables[0];
        }
        public DataTable FetchClaimRelationship(string PolicyId)
        {
            DataSet ds = new DataSet();
            string sql = "[sSupport].[pRptClaimRelationShip]";

            ds = objC.QuerySQL(sql,
                objC.GetSqlParam("@rPolicyId", SqlDbType.Int, PolicyId)
                );

            return ds.Tables[0];
        }

        public DataTable FetchClaimCare(string PolicyId)
        {
            DataSet ds = new DataSet();
            string sql = "sSupport.pRptClaimCare";

            ds = objC.QuerySQL(sql,
                objC.GetSqlParam("@rPolicyId", SqlDbType.Int, PolicyId)
                );

            return ds.Tables[0];
        }
        public DataTable FetchClaimTopHospitals(string PolicyId)
        {
            DataSet ds = new DataSet();
            string sql = "sSupport.pRptClaimTopHospitals";

            ds = objC.QuerySQL(sql,
                objC.GetSqlParam("@rPolicyId", SqlDbType.Int, PolicyId)
                );

            return ds.Tables[0];
        }
        public DataTable FetchClaimTopAilments(string PolicyId)
        {
            DataSet ds = new DataSet();
            string sql = "sSupport.pRptClaimTopAilments";

            ds = objC.QuerySQL(sql,
                objC.GetSqlParam("@rPolicyId", SqlDbType.Int, PolicyId)
                );

            return ds.Tables[0];
        }
        public DataTable FetchClaimTreatment(string PolicyId)
        {
            DataSet ds = new DataSet();
            string sql = "sSupport.pRptClaimTreatment";

            ds = objC.QuerySQL(sql,
                objC.GetSqlParam("@rPolicyId", SqlDbType.Int, PolicyId)
                );

            return ds.Tables[0];
        }
        public DataTable FetchClaimTopAilmentCare(string PolicyId)
        {
            DataSet ds = new DataSet();
            string sql = "sSupport.pRptClaimTopAilmentCareTypeWise";

            ds = objC.QuerySQL(sql,
                objC.GetSqlParam("@rPolicyId", SqlDbType.Int, PolicyId)
                );

            return ds.Tables[0];
        }
        public DataTable FetchClaimTopAilmentRelationship(string PolicyId)
        {
            DataSet ds = new DataSet();
            string sql = "sSupport.pRptClaimTopAilmentRelationWise";

            ds = objC.QuerySQL(sql,
                objC.GetSqlParam("@rPolicyId", SqlDbType.Int, PolicyId)
                );

            return ds.Tables[0];
        }

        public DataTable FetchHealthCardGeneral(string ExaminationIdText)
        {
            DataSet ds = new DataSet();
            string sql = "sExamination.pRptGetHealthCardSubGeneralParameters";

            ds = objC.QuerySQL(sql,
                objC.GetSqlParam("@rExaminationId", SqlDbType.BigInt, ExaminationIdText)
                );

            return ds.Tables[0];
        }

        public DataTable FetchHealthCardEmployment(string ExaminationIdText)
        {
            DataSet ds = new DataSet();
            string sql = "[sExamination].[pRptGetHealthCardEmploymentDetail]";

            ds = objC.QuerySQL(sql,
                objC.GetSqlParam("@rExaminationId", SqlDbType.BigInt, ExaminationIdText)
                );

            return ds.Tables[0];
        }

        public DataTable FetchHealthCardFullTestSub(string ExaminationIdText)
        {
            DataSet ds = new DataSet();
            string sql = "sExamination.pRptGetHealthCardSubTestsParameters";

            ds = objC.QuerySQL(sql,
                objC.GetSqlParam("@rExaminationId", SqlDbType.BigInt, ExaminationIdText)
                );

            return ds.Tables[0];
        }

        public DataTable FetchPulmonaryResult(string ExaminationIdText)
        {
            DataSet ds = new DataSet();
            string sql = "sExamination.pRptPulmonaryResult";

            ds = objC.QuerySQL(sql,
                objC.GetSqlParam("@rExaminationId", SqlDbType.BigInt, ExaminationIdText)
                );

            return ds.Tables[0];
        }

        public DataTable FetchAudiometry(string ExaminationIdText)
        {
            DataSet ds = new DataSet();
            string sql = "sExamination.pRptAudiometrytest";

            ds = objC.QuerySQL(sql,
                objC.GetSqlParam("@rExaminationId", SqlDbType.BigInt, ExaminationIdText)
                );

            return ds.Tables[0];
        }

        //private DataTable FetchPeriodicGujarat(string ExaminationId)
        //{
        //    DataSet ds = new DataSet();
        //    string sql = "sExamination.pRptPeriodicForm";

        //    ds = objC.QuerySQL(sql ,
        //        objC.GetSqlParam("@rReportId", SqlDbType.NVarChar, "4"),
        //        objC.GetSqlParam("@rStoreLocationId", SqlDbType.NVarChar, SecuritySession.UserProfile.StoreLocationId.ToString()),
        //        objC.GetSqlParam("@rExaminationTypeId", SqlDbType.Int, "1"),
        //        objC.GetSqlParam("@rRoundId", SqlDbType.BigInt, "999900517"),
        //        objC.GetSqlParam("@rFunctionId", SqlDbType.NVarChar, "0"),
        //        objC.GetSqlParam("@rDepartmentGroupId", SqlDbType.NVarChar, "0"),
        //        objC.GetSqlParam("@rDepartmentId", SqlDbType.NVarChar, "0"),
        //        objC.GetSqlParam("@rGradeId", SqlDbType.NVarChar, "0"),
        //        objC.GetSqlParam("@rDesignationId", SqlDbType.NVarChar, "0"),
        //        objC.GetSqlParam("@rJobTitleId", SqlDbType.NVarChar, "0"),
        //        objC.GetSqlParam("@rEmployeeTypeId", SqlDbType.NVarChar, "0"),  
        //        objC.GetSqlParam("@rCompanyId", SqlDbType.NVarChar, "0"),
        //        objC.GetSqlParam("@rSectorId", SqlDbType.NVarChar, "0"),
        //        objC.GetSqlParam("@rVerticalId", SqlDbType.NVarChar, "0"),
        //        objC.GetSqlParam("@rCompanyGroupId", SqlDbType.NVarChar, "0"),
        //        objC.GetSqlParam("@rMedicalAgencyId", SqlDbType.NVarChar, "0"),
        //        objC.GetSqlParam("@rTargetGroupId", SqlDbType.NVarChar, "0"),
        //        objC.GetSqlParam("@rProcessTypeId", SqlDbType.Int, "0"),
        //        objC.GetSqlParam("@rContractorId", SqlDbType.NVarChar, "0"),
        //        objC.GetSqlParam("@rGenderId", SqlDbType.Int, "0"),
        //        objC.GetSqlParam("@rStatusId", SqlDbType.Int, "0"),
        //        objC.GetSqlParam("@rAgeFrom", SqlDbType.NVarChar, ""),
        //        objC.GetSqlParam("@rAgeTo", SqlDbType.NVarChar, ""),
        //        objC.GetSqlParam("@rEmployeeTypeGroupId", SqlDbType.NVarChar, "0"),
        //        objC.GetSqlParam("@rEmployeeSubTypeId", SqlDbType.NVarChar, "0"),
        //        objC.GetSqlParam("@rEmployeeGroupId", SqlDbType.NVarChar, "0"),
        //        objC.GetSqlParam("@rOrgId", SqlDbType.NVarChar, SecuritySession.UserProfile.OrgId.ToString()),
        //        objC.GetSqlParam("@rLocationId", SqlDbType.NVarChar, SecuritySession.UserProfile.LocationId.ToString()),
        //        objC.GetSqlParam("@rEmployeeCode", SqlDbType.NVarChar, ""),
        //        objC.GetSqlParam("@rExaminationId", SqlDbType.BigInt, ExaminationId)
        //        );

        //    return ds.Tables[0];
        //}
    }
}