using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.Reporting.WebForms;
using System.Data;
 
using KMASchoolERP;
using System.IO;
using System.Configuration;
using System.Data.OleDb;
 
using System.Data.SqlClient;

public partial class Admin_Fee_uploadStudentByExcel : System.Web.UI.Page
{
    Service sr = new Service();
    
    protected void Page_Load(object sender, EventArgs e)
    {
        //if (Session["groupId"]== null || Session["OfficeMenu"] == null|| Session["sessionId"]==null)
        //{ Response.Redirect("../../AdminLogin/Login.aspx"); }
        if (Page.IsPostBack == false)
        { 
            bindSchool(); bindSchoolClass(); bindRegistrationType();
            //getdata();
        }
    }
    private void bindSchool()
    {
      
    }
    private void bindSchoolClass()
    {
         
    }
    private void bindClassSection()
    {
     
    }
    private void bindRegistrationType()
    {
         

    }
    protected void btnUpload_Click(object sender, EventArgs e)
    {
        lblmsg.Text = "It will Delete Stored Excel Data When You Upload New File";
        if (FileUploadControl.HasFile)
        {
            string Ext = Path.GetExtension(FileUploadControl.PostedFile.FileName);
            if (Ext == ".xls" || Ext == ".xlsx")
            {
                lblErrorMessage.Visible = false;
                string Name = Path.GetFileName(FileUploadControl.PostedFile.FileName);
                string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
                string FilePath = Server.MapPath(FolderPath + "Data"+ Ext);
                FileUploadControl.SaveAs(FilePath);
                FillGridFromExcelSheet(FilePath, Ext, ddlIsHeaderExists.SelectedValue);
            }
            else
            {
                lblmsg.Visible = true;
                lblmsg.Text = "Please upload valid Excel File";
                ExcelGridView.DataSource = null;
                ExcelGridView.DataBind();
            }
        }
        else { lblmsg.Text = "Please Select Excel File !"; }
    }
    private void FillGridFromExcelSheet(string FilePath, string ext, string isHader)
    {
        
            try {
            //lblmsg.Text = "";
          string Query = @" delete from tempDatainsert 
           DBCC CHECKIDENT('tempDatainsert', RESEED, 0)  ";
            string connectionString = "";
        if (ext == ".xls")
        {   //For Excel 97-03
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
        }
        else if (ext == ".xlsx")
        {    //For Excel 07 and greater
            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
        }

        connectionString = String.Format(connectionString, FilePath, isHader);
        OleDbConnection conn = new OleDbConnection(connectionString);
        OleDbCommand cmd = new OleDbCommand();
        OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
        DataTable dt = new DataTable();

        cmd.Connection = conn;
        //Fetch 1st Sheet Name
        conn.Open();
        DataTable dtSchema;
        dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string ExcelSheetName = dtSchema.Rows[0]["TABLE_NAME"].ToString();
        conn.Close();
        //Read all data of fetched Sheet to a Data Table
        conn.Open();
        cmd.CommandText = "SELECT * From [" + ExcelSheetName + "]";
        dataAdapter.SelectCommand = cmd;
        dataAdapter.Fill(dt);
        conn.Close();
            //Bind Sheet Data to GridView
            // ExcelGridView.Caption = Path.GetFileName(FilePath);

            string name = "", Fname = "", Regno = "", Mobileno="" ,DOB ="";
        for (int i = 0; i < dt.Rows.Count; i++)
        {
                name = ""; Fname = ""; Regno = "";
                name = dt.Rows[i]["NameofCandidate"].ToString().Replace("'", "").Trim();
                Fname = dt.Rows[i]["FatherName"].ToString().Replace("'", "").Trim();
                Regno = dt.Rows[i]["RegistraionNumber"].ToString().Replace("'", "").Trim();
                Mobileno= dt.Rows[i]["MobileNo"].ToString().Replace("'", "").Trim();
                DOB = dt.Rows[i]["DOB"].ToString().Replace("'", "").Trim();
                if (DOB.Length>0 & name.Length > 0 & Fname.Length>0 & Regno.Length>0 & Mobileno.Length>0)
                {
                    Query = Query + @" insert into tempDatainsert (admDate,Name,Fname,MName,Course,RegNo,TrainingCenterName,DOB,Mobileno,Email) values('" + dt.Rows[i]["AdmissionDate"].ToString().Replace("'", "").Trim() + "','"  +
                     dt.Rows[i]["NameofCandidate"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["FatherName"].ToString().Replace("'", "").Trim() + "', '" + dt.Rows[i]["MotherName"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["Course"].ToString().Replace("'", "").Trim() + "'" +
                     @",'" + dt.Rows[i]["RegistraionNumber"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["TrainingCenterName"].ToString().Replace("'", "").Trim() +  "','"+ dt.Rows[i]["DOB"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["MobileNo"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["Email"].ToString().Replace("'", "").Trim() + "'  )";
                    // srn = dt.Rows[i]["SRN"].ToString().Replace("'", "");
                    //Query = Query + @" insert into tempDatainsert (SRN,rollNo,admissionNo,sectionName,name,srName,dob,admissionDate,Adharnum,gender,category,religion,fName,fMobileNo,fadharNum,fQualification,fOcupation,fAnuualIncome,MName,mMobileNo,MadharNum,MQualification,MOcupation,mAnuualIncome,address,mobileNo,state,district,city,schoolId,classId) values('" + srn + "'"+ dt.Rows[i]["rollNo"].ToString().Replace("'", "") == "" ? "0" : dt.Rows[i]["rollNo"].ToString().Replace("'", "");
                }
            }
            Query =Query+ @" select admDate,Name,Fname,MName,Course,RegNo,TrainingCenterName,DOB,MobileNo,Email  from tempDatainsert ";
            SqlCommand cmd1 = new SqlCommand();
            SqlConnection con1 = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString);
            cmd1.Connection = con1;
            SqlDataAdapter adp = new SqlDataAdapter(Query,con1);
            DataTable dt1 = new DataTable();
            adp.Fill(dt1);

            getdata(dt1);
            //ExcelGridView.DataSource = dt1;
            //ExcelGridView.DataBind();
            btnInsert.Enabled = true;
            con1.Close();
        }
        catch(Exception ex) { lblmsg.Text = ex.Message; }
       
}
   
    private void getdata(DataTable dt)
    {

        //SqlConnection con1 = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString);
        //SqlDataAdapter adp = new SqlDataAdapter("select admDate,Name,Fname,MName,Course,NSFQLevel,Duration,RegNo,TrainingCenterName,Photo  from tempDatainsert", con1);
        //DataTable dt1 = new DataTable();
        //adp.Fill(dt1);
        //con1.Close();
        ExcelGridView.DataSource = dt;
        ExcelGridView.DataBind();
    }

    public void ExportTableData(DataTable dtdata)
    {
        string attach = "attachment;filename=DataFormat"+ ".xls";
        Response.ClearContent();
        Response.AddHeader("content-disposition", attach);
        Response.ContentType = "application/ms-excel";
        if (dtdata != null)
        {
            foreach (DataColumn dc in dtdata.Columns)
            {
                Response.Write(dc.ColumnName + "\t");
                //sep = ";";
            }
            Response.Write(System.Environment.NewLine);
            foreach (DataRow dr in dtdata.Rows)
            {
                for (int i = 0; i < dtdata.Columns.Count; i++)
                {
                    Response.Write(dr[i].ToString() + "\t");
                }
                Response.Write("\n");
            }
            Response.End();
        }
    }

    protected void btexportsheet_Click(object sender, EventArgs e)
    {
        DataTable dt = new DataTable();

        dt.Columns.Add("AdmissionDate", typeof(string));
        dt.Columns.Add("NameofCandidate", typeof(string));
        dt.Columns.Add("FatherName", typeof(string));
        dt.Columns.Add("MotherName", typeof(string));
        dt.Columns.Add("Course", typeof(string));
       // dt.Columns.Add("NSQFLevel", typeof(string));
        //dt.Columns.Add("Duration", typeof(string));
        dt.Columns.Add("RegistraionNumber", typeof(string));
        dt.Columns.Add("TrainingCenterName", typeof(string));
       dt.Columns.Add("DOB", typeof(string));
       dt.Columns.Add("MobileNo", typeof(string));
       dt.Columns.Add("Email", typeof(string));

        DataRow dr = dt.NewRow();


        dr["AdmissionDate"] = "04-25-2020";
        dr["NameofCandidate"] = "Student Name";
        dr["FatherName"] = "Father Name";
        dr["MotherName"] = "Mother Name";
        dr["Course"] = "Course Name";
        //dr["NSQFLevel"] = "NSQFLevel";
       // dr["Duration"] = "Duration";
        dr["RegistraionNumber"] = "1001";
        dr["TrainingCenterName"] = "Gramin Skill";
        dr["DOB"] = "05-17-2000";
        dr["MobileNo"] = "9999988888";
        dr["Email"] = "graminskill@gmail.com";
        //dr["PhotoofCandidate"] = "";

        dt.Rows.Add(dr);
        ExportTableData(dt);


    }

    protected void ddlschool_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (ddlschool.SelectedIndex > 0)
        {
          //  bindSchoolClass();
        }
    }

    protected void ddlclass_SelectedIndexChanged(object sender, EventArgs e)
    {
        ExcelGridView.DataSource = null;
        ExcelGridView.DataBind();
        if (ddlclass.SelectedIndex>0)
        { bindClassSection(); showStoredData(); }
       
    }
    private void showStoredData()
    {
     /*   string Query = @" select Remark,SRN,rollNo,admissionNo,mcsName as Section,name,srName,dob,DOBInHindi as DOBinword,admissionDate,
 Adharnum,gender,category,religion,fName,fMobileNo,fadharNum,fQualification,fOcupation,
 fAnuualIncome,MName,mMobileNo,MadharNum,MQualification,MOcupation,mAnuualIncome,address,
 mobileNo,state,district,city from tempDatainsert inner join masterclasssection on mcsId=sectionId where classId =" + ddlclass.SelectedValue + " and schoolId =" + ddlschool.SelectedValue;
        DL obj = new DL();
        prop prp = new prop();
        prp.Query = Query;
        DataTable dt= obj.DeveloperResult(prp);
        ExcelGridView.DataSource = dt; 
        ExcelGridView.DataBind();
        if (dt.Rows.Count > 0) { 
        btnInsert.Enabled = true;
        }
        else { btnInsert.Enabled = false; }
        */
    }

    protected void btnInsert_Click(object sender, EventArgs e)
    {
        /*
        DL objI = new DL();
        prop prpI = new prop();
        prpI.Query = "exec insStudentExcelData ";
        objI.DeveloperResult(prpI);

        string Query = @" select Remark,SRN,rollNo,admissionNo,mcsName as Section,name,srName,dob,DOBInHindi as DOBinword,admissionDate,
 Adharnum,gender,category,religion,fName,fMobileNo,fadharNum,fQualification,fOcupation,
 fAnuualIncome,MName,mMobileNo,MadharNum,MQualification,MOcupation,mAnuualIncome,address,
 mobileNo,state,district,city from tempDatainsert inner join masterclasssection on mcsId=sectionId where classId =" + ddlclass.SelectedValue + " and schoolId =" + ddlschool.SelectedValue;
        DL obj = new DL();
        prop prp = new prop();
        prp.Query = Query;
        DataTable dt = obj.DeveloperResult(prp);
        ExcelGridView.DataSource = dt;
        ExcelGridView.DataBind();
        if (dt.Rows.Count > 0)
        {
            btnInsert.Enabled = true;
        }
        else { btnInsert.Enabled = false; }
        */

        SqlCommand cmd1 = new SqlCommand();
        SqlConnection con1 = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString);
        cmd1.Connection = con1;
        if (con1.State == ConnectionState.Closed)
            con1.Open();
        cmd1.CommandText = "insFromExcel";
        cmd1.CommandType = CommandType.StoredProcedure;
       lblmsg.Text= cmd1.ExecuteScalar().ToString();
        cmd1.Dispose();
        con1.Close();

    }


    protected void btnUploadCombine_Click(object sender, EventArgs e)
    {
        lblmsgCombine.Text = "It will Delete Stored Excel Data When You Upload New File";
        if (FileUploadCombine.HasFile)
        {
            string Ext = Path.GetExtension(FileUploadCombine.PostedFile.FileName);
            if (Ext == ".xls" || Ext == ".xlsx")
            {
                lblErrorMessage.Visible = false;
                string Name = Path.GetFileName(FileUploadCombine.PostedFile.FileName);
                string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
                string FilePath = Server.MapPath(FolderPath + "Data" + Ext);
                FileUploadCombine.SaveAs(FilePath);
                FillGridFromExcelSheetCombine(FilePath, Ext, ddlIsHeaderExists.SelectedValue);
            }
            else
            {
               
                lblmsgCombine.Text = "Please upload valid Excel File";
                ExcelGridView.DataSource = null;
                ExcelGridView.DataBind();
            }
        }
        else { lblmsgCombine.Text = "Please Select Excel File !"; }
    }
    private void FillGridFromExcelSheetCombine(string FilePath, string ext, string isHader)
    {
        if (ddlschool.SelectedIndex > 0)
        {
            try
            {
                //lblmsg.Text = "";
                string Query = @" delete from tempDatainsert 
           DBCC CHECKIDENT('tempDatainsert', RESEED, 0)  ";
                string connectionString = "";
                if (ext == ".xls")
                {   //For Excel 97-03
                    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
                }
                else if (ext == ".xlsx")
                {    //For Excel 07 and greater
                    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
                }

                connectionString = String.Format(connectionString, FilePath, isHader);
                OleDbConnection conn = new OleDbConnection(connectionString);
                OleDbCommand cmd = new OleDbCommand();
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
                DataTable dt = new DataTable();

                cmd.Connection = conn;
                //Fetch 1st Sheet Name
                conn.Open();
                DataTable dtSchema;
                dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string ExcelSheetName = dtSchema.Rows[0]["TABLE_NAME"].ToString();
                conn.Close();
                //Read all data of fetched Sheet to a Data Table
                conn.Open();
                cmd.CommandText = "SELECT * From [" + ExcelSheetName + "]";
                dataAdapter.SelectCommand = cmd;
                dataAdapter.Fill(dt);
                conn.Close();
                //Bind Sheet Data to GridView
                // ExcelGridView.Caption = Path.GetFileName(FilePath);

                string srn = "",RegEnrolNo= "",Caste="", admnum = "", name = "",Sectionname="",ClassName="",fanualIncome="",ManualIncome="", OpeningBalance="",Admchar="", mspAdmCharacterStart="", TeamName="";
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    srn = ""; admnum = ""; name = ""; Sectionname = ""; ClassName = ""; OpeningBalance = "0";
                     srn = dt.Rows[i]["rollNo"].ToString().Replace("'", "").Trim();
                    admnum = dt.Rows[i]["admissionNo"].ToString().Replace("'", "").Trim();
                    name = dt.Rows[i]["name"].ToString().Replace("'", "").Trim();
                    Sectionname = dt.Rows[i]["sectionName"].ToString().Replace("'", "").Trim();
                    ClassName = dt.Rows[i]["ClassName"].ToString().Replace("'", "").Trim();
                    OpeningBalance = dt.Rows[i]["OpeningBalance"].ToString().Trim();
                    fanualIncome = dt.Rows[i]["fAnuualIncome"].ToString().Replace("'", "").Trim();
                    fanualIncome= fanualIncome.Length > 0 ? fanualIncome : "0";
                    ManualIncome = dt.Rows[i]["mAnuualIncome"].ToString().Replace("'", "").Trim();
                    Admchar= dt.Rows[i]["admissionNoChar"].ToString().Replace("'", "").Trim();
                    ManualIncome = ManualIncome.Length > 0 ? ManualIncome : "0";
                    OpeningBalance = OpeningBalance.Length > 0 ? OpeningBalance : "0";
                    Admchar = Admchar.Length > 0 ? Admchar : "";
                    mspAdmCharacterStart= dt.Rows[i]["AdmCharacterStart"].ToString().Replace("'", "").Trim();
                    TeamName= dt.Rows[i]["TeamName"].ToString().Replace("'", "").Trim();
                    RegEnrolNo = dt.Rows[i]["RegEnrolNo"].ToString();
                    Caste = dt.Rows[i]["Caste"].ToString();
                    if (srn.Length > 0 & admnum.Length > 0 & name.Length > 0 & ClassName.Length>0 & Sectionname.Length>0)
                    {
                        Query = Query + @" insert into tempDatainsert (SRN,rollNo,admissionNo,sectionName,name,srName,dob,DOBInHindi,admissionDate,Adharnum,gender,category,religion,fName,fMobileNo,fadharNum,fQualification,fOcupation,fAnuualIncome,MName,mMobileNo,MadharNum,MQualification,MOcupation,mAnuualIncome,address,mobileNo,state,district,city,schoolId,sessionId,staffId,groupId,regTypeId,feeDepartmentId,ClassName,balance,AdmCharacter,mspAdmCharacterStart,TeamName,RegEnrolNo,Caste) values('" + dt.Rows[i]["SRN"].ToString().Replace("'", "").Trim() + "'," + srn.Trim() + ",'" +
                         dt.Rows[i]["admissionNo"].ToString().Replace("'", "").Trim() + "','"+dt.Rows[i]["sectionName"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["title"].ToString().Replace("'", "").Trim() + " " + dt.Rows[i]["name"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["srName"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["dob"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["DOBinword"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["admissionDate"].ToString().Replace("'", "").Trim() + "'" +
                         @",'" + dt.Rows[i]["Adharnum"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["gender"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["category"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["religion"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["fName"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["fMobileNo"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["fadharNum"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["fQualification"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["fOcupation"].ToString().Replace("'", "").Trim() + "'" +
                         @",'" + fanualIncome + "','" + dt.Rows[i]["MName"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["mMobileNo"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["MadharNum"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["MQualification"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["MOcupation"].ToString().Replace("'", "").Trim() + "','" + ManualIncome + "','" + dt.Rows[i]["address"].ToString().Replace("'", "").Trim() + "'" +
                         @",'" + dt.Rows[i]["mobileNo"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["state"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["district"].ToString().Replace("'", "").Trim() + "','" + dt.Rows[i]["city"].ToString().Replace("'", "").Trim() + "'," + ddlschool.SelectedValue + "," + Session["sessionId"].ToString() + "," + Session["OfficeMenu"].ToString() + "," + Session["groupId"].ToString() + "," + ddlRegistration.SelectedValue + ",1,'" + dt.Rows[i]["ClassName"].ToString().Replace("'", "").Trim() + "',"+ OpeningBalance + ",'"+ Admchar+"','"+ mspAdmCharacterStart+"','"+ TeamName+"','"+RegEnrolNo+"','"+ Caste+"') ";
                        // srn = dt.Rows[i]["SRN"].ToString().Replace("'", "");
                        //Query = Query + @" insert into tempDatainsert (SRN,rollNo,admissionNo,sectionName,name,srName,dob,admissionDate,Adharnum,gender,category,religion,fName,fMobileNo,fadharNum,fQualification,fOcupation,fAnuualIncome,MName,mMobileNo,MadharNum,MQualification,MOcupation,mAnuualIncome,address,mobileNo,state,district,city,schoolId,classId) values('" + srn + "'"+ dt.Rows[i]["rollNo"].ToString().Replace("'", "") == "" ? "0" : dt.Rows[i]["rollNo"].ToString().Replace("'", "");
                    }
                }
                Query = Query + @" update tempDatainsert set classId=mClassid,sectionId=mcsId,date=getdate(),teamId=(select mHouseId from masterHouse where mHouseName=TeamName and mHouseGroupId=(select mSchoolGroupId from masterschool where mschoolId=schoolId)) from tempDatainsert
 inner join  masterclass on ClassName=mClassName
 inner join masterclasssection on mcsName=sectionName and mcsClassid=mClassid
 where mcsSchoolId=" + ddlschool.SelectedValue+ @"  select SRN,rollNo,admissionNo,mClassName as Class,mcsName as Section,name,srName,dob,DOBInHindi as DOBinword,admissionDate,
 Adharnum,gender,category,religion,fName,fMobileNo,fadharNum,fQualification,fOcupation,
 fAnuualIncome,MName,mMobileNo,MadharNum,MQualification,MOcupation,mAnuualIncome,address,
 mobileNo,state,district,city from tempDatainsert inner join masterclasssection on mcsId=sectionId inner join  masterclass on classId=mClassid
 where schoolId =" + ddlschool.SelectedValue;
               // DL obj = new DL();
                prop prp = new prop();
                prp.Query = Query;
                //obj.DeveloperResult(prp);
              //  ExcelGridView.DataSource = obj.DeveloperResult(prp);
                ExcelGridView.DataBind();
                btInsetCombine.Enabled = true;
            }
            catch (Exception ex) { lblmsgCombine.Text = ex.Message; }
        }
        else { lblmsgCombine.Text = "* Field Is Required !"; }
    }

    protected void btexportCombineFormat_Click(object sender, EventArgs e)
    {
        DataTable dt = new DataTable();
         
        dt.Columns.Add("AdmissionDate", typeof(string));
        dt.Columns.Add("NameofCandidate", typeof(string)); 
        dt.Columns.Add("FatherName", typeof(string));
        dt.Columns.Add("MotherName", typeof(string));
        dt.Columns.Add("Course", typeof(string));
        dt.Columns.Add("NSQFLevel", typeof(string));
        dt.Columns.Add("Duration", typeof(string));
        dt.Columns.Add("RegistraionNumber", typeof(string));
        dt.Columns.Add("TrainingCenterName", typeof(string));
        dt.Columns.Add("PhotoofCandidate", typeof(string));
         
        DataRow dr = dt.NewRow();
       
       
        dr["AdmissionDate"] = "01-25-1990";
        dr["NameofCandidate"] = "Student Name";
        dr["FatherName"] = "Father Name";
        dr["MotherName"] = "Mother Name";
        dr["Course"] = "Course Name";
        dr["NSQFLevel"] = "NSQFLevel";
        dr["Duration"] = "Duration";
        dr["RegistraionNumber"] = "1001";
        dr["TrainingCenterName"] = "Gramin Skill";
        dr["PhotoofCandidate"] = "";
       
         dt.Rows.Add(dr);
        ExportTableData(dt);
    }

    protected void btnViewExisiting_Click(object sender, EventArgs e)
    {
        /*
        string Query = @"  select SRN,rollNo,admissionNo,mClassName as Class,mcsName as Section,name,srName,dob,DOBInHindi as DOBinword,admissionDate,
Adharnum,gender,category,religion,fName,fMobileNo,fadharNum,fQualification,fOcupation,
fAnuualIncome,MName,mMobileNo,MadharNum,MQualification,MOcupation,mAnuualIncome,address,
mobileNo,state,district,city,Balance as OpeningBalance,TeamName,RegEnrolNo,Caste from tempDatainsert inner join masterclasssection on mcsId=sectionId inner join  masterclass on classId=mClassid
 where schoolId =" + ddlschool.SelectedValue;
        DL obj = new DL();
        prop prp = new prop();
        prp.Query = Query;
        DataTable dt = obj.DeveloperResult(prp);
        ExcelGridView.DataSource = dt;
        ExcelGridView.DataBind();
        if (dt.Rows.Count > 0)
        {
            btInsetCombine.Enabled = true;
        }
        else { btInsetCombine.Enabled = false; }
        */
    }

    protected void btInsetCombine_Click(object sender, EventArgs e)
    {
        try { 
        //DL objI = new DL();
        prop prpI = new prop();
        prpI.Query = "exec insStudentExcelData ";
//        objI.DeveloperResult(prpI);

        string Query = @" update masterstudentpromotion set mspGroupNo=mspProfileid where mspGroupNo=0 or mspGroupNo is null  select SRN,rollNo,admissionNo,mClassName as Class,mcsName as Section,name,srName,dob,DOBInHindi as DOBinword,admissionDate,
Adharnum,gender,category,religion,fName,fMobileNo,fadharNum,fQualification,fOcupation,
fAnuualIncome,MName,mMobileNo,MadharNum,MQualification,MOcupation,mAnuualIncome,address,
mobileNo,state,district,city from tempDatainsert inner join masterclasssection on mcsId=sectionId inner join  masterclass on classId=mClassid
 where schoolId =" + ddlschool.SelectedValue;
  //      DL obj = new DL();
        prop prp = new prop();
        prp.Query = Query;
        ///DataTable dt = obj.DeveloperResult(prp);
       // ExcelGridView.DataSource = dt;
        ExcelGridView.DataBind();
        //if (dt.Rows.Count > 0)
        //{
        //    btInsetCombine.Enabled = true;
        //}
        //else { btInsetCombine.Enabled = false; }
        }catch(Exception ex) { lblmsgCombine.Text = ex.Message.ToLower(); }
    }

    protected void btnUnInsertedDataExport_Click(object sender, EventArgs e)
    {
        try
        {
            string Query = @"  select SRN,rollNo,mspAdmCharacterStart as AdmCharacterStart,admissionNo,AdmCharacter as admissionNoChar,ClassName,sectionName,'' as title ,name,srName,convert(varchar,dob,101)dob,DOBInHindi as DOBinword,convert(varchar,admissionDate,101)admissionDate,
Adharnum,gender,category,religion,fName,fMobileNo,fadharNum,fQualification,fOcupation,
fAnuualIncome,MName,mMobileNo,MadharNum,MQualification,MOcupation,mAnuualIncome,address,
mobileNo,state,district,city,Balance as OpeningBalance,TeamName,mSchoolName as SchoolName,Remark,RegEnrolNo,Caste from tempDatainsert inner join masterclasssection on mcsId=sectionId inner join  masterclass on classId=mClassid
inner join masterschool on mSchoolId=schoolId 
 where schoolId =" + ddlschool.SelectedValue;
          //  DL obj = new DL();
            prop prp = new prop();
            prp.Query = Query;
          //  DataTable dt = obj.DeveloperResult(prp);
           // ExportTableData(dt);
        }
        catch(Exception ex) { lblmsgCombine.Text = ex.Message.ToString(); }
    }
}