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.IO;
using System.Collections;

using System.Data.OleDb;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Web.Services;
using Newtonsoft.Json;
using System.Web.Script.Serialization;
using System.Web.Script.Services;
public partial class module_Registration3 : System.Web.UI.Page
{
    public static string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    public static MySqlConnection con = new MySqlConnection(constring);
    public static DateTime date1;
    public static DateTime date2;
    public static DateTime date3;
    public static int grouplastid;
    public static string dtmonths;
    public static string dtyears;
    public static string[] dtsplits;
    public static string studentids;
    public static string sttitle;
    public static string stfname;
    public static string stmname;
    public static string stlname;
    public static string stpname;
    public static string stmothernm;
    public static string stcountry;
    public static string ststates;
    public static string stcitys;
  
    public static string staddress;
    public static string stresiaddress;
    public static string stpermaddress;
    public static string stpincodes; public static string stcastes; public static string stsubcastes; public static string stbloodgrps;
    public static string stgenders; public static string stdob; public static string stages; public static string stmobiles;
    public static string stgemials; public static string styeasrs; public static string stclssesid; public static string stdivsid;
    public static string sthighquali; public static string staadharids; public static string ststauss; public static string isprovi;
    public static string regdate;
    public static int studeregid;public static string Studid;
    public static DateTime mdate1;
    public static DateTime mdate;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    [WebMethod]
    public static string savedata(string uhid, string studtitle, string fname, string mname, string lname, string pname, string mothernme, string country, string state, string city, string address, string resiaddrss, string peraddrss, string pin, string castcategory, string castes, string bloodgroup, string gender, string dob, string age, string mobile1, string mobile2, string email, string stdyears, string classd, string division, string highquali, string adharcard, string admission, string bplcard, string scheme, string coursename, string status, string isprovisional)
    {
        string data = "1";
        if (dob != "")
        {
            date1 = DateTime.Parse(dob);
        }
        if (admission != "")
        {
            date2 = DateTime.Parse(admission);
        }
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select student_detail.* from  student_detail  where student_detail.studid = '" + uhid + "'", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                DataTable ds = new DataTable();
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                {
                    da.Fill(ds); con.Close();
                    if (ds.Rows.Count > 0)
                    {
                        using (MySqlConnection con3 = new MySqlConnection(constring))
                        {
                            using (MySqlCommand cmd23 = new MySqlCommand("select student_detail.* from  student_detail  order by id desc limit 1", con3))
                            {
                                if (con3.State != ConnectionState.Open) { con3.Open(); }
                                DataTable ds1 = new DataTable();
                                using (MySqlDataAdapter da1 = new MySqlDataAdapter(cmd23))
                                {
                                    da1.Fill(ds1);
                                    con3.Close();
                                    if (ds1.Rows.Count > 0)
                                    {
                                        studeregid = Convert.ToInt32(ds1.Rows[0]["id"].ToString()) + 1;
                                        if (studeregid >= 10)
                                        {
                                            studeregid = 0 + studeregid;
                                        }
                                       
                                        Studid = "STUD/" + DateTime.Now.Month.ToString("0#") + DateTime.Now.ToString("yy") + "/" + studeregid;

                                        using (MySqlConnection con1 = new MySqlConnection(constring))
                                        {
                                            string query = "Insert into student_detail(studid,title,fname,mname,lname,pname,mothernme,country,state,city,address,resiaddrss,peraddrss,pin,castcategory,caste,bloodgroup,gender,dob,age,mobile1,mobile2,email,admyears,classd,division,highquali,adharcard,bplcard,scheme,coursename,status,isprovisional,today) values(@uhid,@title,@fname,@mname,@lname,@pname,@mothernme,@country,@state,@city,@address,@resiaddrss,@peraddrss,@pin,@castcategory,@caste,@bloodgroup,@gender,@dob,@age,@mobile1,@mobile2,@email,@admyears,@classd,@division,@highquali,@adharcard,@bplcard,@scheme,@coursename,@status,@isprovisional,@today)";

                                            //string query = "Insert into student_detail(fname,mname,lname,pname,mothernme,country,state,city,address,resiaddrss,peraddrss,pin,castcategory,bloodgroup,gender,dob,age,mobile1,mobile2,email,classd,division,highquali,adharcard,votingcard,bplcard,scheme,coursename,status,isprovisional) values(@fname,@mname,@lname,@pname,@mothernme,@country,@state,@city,@address,@resiaddrss,@peraddrss,@pin,@castcategory,@bloodgroup,@gender,@dob,@age,@mobile1,@mobile2,@email,@classd,@division,@highquali,@adharcard,@votingcard,@bplcard,@scheme,@coursename,@status,@isprovisional)";
                                            using (MySqlCommand cmd12 = new MySqlCommand(query, con1))
                                            {
                                                if (con1.State != ConnectionState.Open) { con1.Open(); }
                                                cmd12.Parameters.AddWithValue("@uhid", Studid);
                                                cmd12.Parameters.AddWithValue("@title", studtitle);
                                                cmd12.Parameters.AddWithValue("@fname", fname);
                                                cmd12.Parameters.AddWithValue("@mname", mname);
                                                cmd12.Parameters.AddWithValue("@lname", lname);
                                                cmd12.Parameters.AddWithValue("@pname", pname);
                                                cmd12.Parameters.AddWithValue("@mothernme", mothernme);
                                                cmd12.Parameters.AddWithValue("@country", country);
                                                cmd12.Parameters.AddWithValue("@state", state);
                                                cmd12.Parameters.AddWithValue("@city", city);
                                                cmd12.Parameters.AddWithValue("@address", address);
                                                cmd12.Parameters.AddWithValue("@resiaddrss", resiaddrss);
                                                cmd12.Parameters.AddWithValue("@peraddrss", peraddrss);
                                                cmd12.Parameters.AddWithValue("@pin", pin);
                                                cmd12.Parameters.AddWithValue("@castcategory", castcategory);
                                                cmd12.Parameters.AddWithValue("@caste", castes);
                                                cmd12.Parameters.AddWithValue("@bloodgroup", bloodgroup);
                                                cmd12.Parameters.AddWithValue("@gender", gender);
                                                cmd12.Parameters.AddWithValue("@dob", date1.ToString("yyyy/MM/dd"));

                                                cmd12.Parameters.AddWithValue("@age", age);
                                                cmd12.Parameters.AddWithValue("@mobile1", mobile1);
                                                cmd12.Parameters.AddWithValue("@mobile2", mobile2);
                                                cmd12.Parameters.AddWithValue("@email", email);
                                                cmd12.Parameters.AddWithValue("@admyears", stdyears);
                                                cmd12.Parameters.AddWithValue("@classd", classd);
                                                cmd12.Parameters.AddWithValue("@division", division);
                                                cmd12.Parameters.AddWithValue("@highquali", highquali);
                                                cmd12.Parameters.AddWithValue("@adharcard", adharcard);
                                                // cmd.Parameters.AddWithValue("@votingcard", votingcard);
                                                cmd12.Parameters.AddWithValue("@bplcard", bplcard);
                                                cmd12.Parameters.AddWithValue("@scheme", scheme);
                                                cmd12.Parameters.AddWithValue("@coursename", coursename);
                                                cmd12.Parameters.AddWithValue("@status", status);
                                                cmd12.Parameters.AddWithValue("@isprovisional", isprovisional);
                                                cmd12.Parameters.AddWithValue("@today", date2.ToString("yyyy/MM/dd"));
                                                cmd12.ExecuteNonQuery();
                                                con1.Close();

                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                    else
                    {
                        using (MySqlConnection con1 = new MySqlConnection(constring))
                        {
                            string query = "Insert into student_detail(studid,title,fname,mname,lname,pname,mothernme,country,state,city,address,resiaddrss,peraddrss,pin,castcategory,caste,bloodgroup,gender,dob,age,mobile1,mobile2,email,admyears,classd,division,highquali,adharcard,bplcard,scheme,coursename,status,isprovisional,today) values(@uhid,@title,@fname,@mname,@lname,@pname,@mothernme,@country,@state,@city,@address,@resiaddrss,@peraddrss,@pin,@castcategory,@caste,@bloodgroup,@gender,@dob,@age,@mobile1,@mobile2,@email,@admyears,@classd,@division,@highquali,@adharcard,@bplcard,@scheme,@coursename,@status,@isprovisional,@today)";

                            //string query = "Insert into student_detail(fname,mname,lname,pname,mothernme,country,state,city,address,resiaddrss,peraddrss,pin,castcategory,bloodgroup,gender,dob,age,mobile1,mobile2,email,classd,division,highquali,adharcard,votingcard,bplcard,scheme,coursename,status,isprovisional) values(@fname,@mname,@lname,@pname,@mothernme,@country,@state,@city,@address,@resiaddrss,@peraddrss,@pin,@castcategory,@bloodgroup,@gender,@dob,@age,@mobile1,@mobile2,@email,@classd,@division,@highquali,@adharcard,@votingcard,@bplcard,@scheme,@coursename,@status,@isprovisional)";
                            using (MySqlCommand cmd12 = new MySqlCommand(query, con1))
                            {
                                if (con1.State != ConnectionState.Open) { con1.Open(); }
                                cmd12.Parameters.AddWithValue("@uhid", uhid);
                                cmd12.Parameters.AddWithValue("@title", studtitle);
                                cmd12.Parameters.AddWithValue("@fname", fname);
                                cmd12.Parameters.AddWithValue("@mname", mname);
                                cmd12.Parameters.AddWithValue("@lname", lname);
                                cmd12.Parameters.AddWithValue("@pname", pname);
                                cmd12.Parameters.AddWithValue("@mothernme", mothernme);
                                cmd12.Parameters.AddWithValue("@country", country);
                                cmd12.Parameters.AddWithValue("@state", state);
                                cmd12.Parameters.AddWithValue("@city", city);
                                cmd12.Parameters.AddWithValue("@address", address);
                                cmd12.Parameters.AddWithValue("@resiaddrss", resiaddrss);
                                cmd12.Parameters.AddWithValue("@peraddrss", peraddrss);
                                cmd12.Parameters.AddWithValue("@pin", pin);
                                cmd12.Parameters.AddWithValue("@castcategory", castcategory);
                                cmd12.Parameters.AddWithValue("@caste", castes);
                                cmd12.Parameters.AddWithValue("@bloodgroup", bloodgroup);
                                cmd12.Parameters.AddWithValue("@gender", gender);
                                cmd12.Parameters.AddWithValue("@dob", date1.ToString("yyyy/MM/dd"));

                                cmd12.Parameters.AddWithValue("@age", age);
                                cmd12.Parameters.AddWithValue("@mobile1", mobile1);
                                cmd12.Parameters.AddWithValue("@mobile2", mobile2);
                                cmd12.Parameters.AddWithValue("@email", email);
                                cmd12.Parameters.AddWithValue("@admyears", stdyears);
                                cmd12.Parameters.AddWithValue("@classd", classd);
                                cmd12.Parameters.AddWithValue("@division", division);
                                cmd12.Parameters.AddWithValue("@highquali", highquali);
                                cmd12.Parameters.AddWithValue("@adharcard", adharcard);
                                // cmd.Parameters.AddWithValue("@votingcard", votingcard);
                                cmd12.Parameters.AddWithValue("@bplcard", bplcard);
                                cmd12.Parameters.AddWithValue("@scheme", scheme);
                                cmd12.Parameters.AddWithValue("@coursename", coursename);
                                cmd12.Parameters.AddWithValue("@status", status);
                                cmd12.Parameters.AddWithValue("@isprovisional", isprovisional);
                                cmd12.Parameters.AddWithValue("@today", date2.ToString("yyyy/MM/dd"));
                                cmd12.ExecuteNonQuery();
                                con1.Close();

                            }
                        }
                    }
                }
            }
        }
        return data;
    }
    [WebMethod]
    public static string canceladm(string studentids, string sid, string canceldt)
    {
        string data = "1";
        if (canceldt != "")
        {
            date1 = DateTime.Parse(canceldt);
        }
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            string query = "update student_detail set canceladmission= '1',canceldate='" + date1.ToString("yyyy/MM/dd") + "' where studid = '" + studentids + "'";

            //string query = "Insert into student_detail(fname,mname,lname,pname,mothernme,country,state,city,address,resiaddrss,peraddrss,pin,castcategory,bloodgroup,gender,dob,age,mobile1,mobile2,email,classd,division,highquali,adharcard,votingcard,bplcard,scheme,coursename,status,isprovisional) values(@fname,@mname,@lname,@pname,@mothernme,@country,@state,@city,@address,@resiaddrss,@peraddrss,@pin,@castcategory,@bloodgroup,@gender,@dob,@age,@mobile1,@mobile2,@email,@classd,@division,@highquali,@adharcard,@votingcard,@bplcard,@scheme,@coursename,@status,@isprovisional)";
            using (MySqlCommand cmd = new MySqlCommand(query, con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                cmd.ExecuteNonQuery();
                con.Close();
                data = "update";
            }
        }
        return data;
    }
    public class Department
    {
        public int DeptId { get; set; }
        public string DeptName { get; set; }
        public string DeptIds { get; set; }
        public string Initial { get; set; }
        public string year1 { get; set; } public string firstname { get; set; } public string midname { get; set; } public string lastname { get; set; }
    }
    [WebMethod]
    public static List<Department> PopulateDropDownList4()
    {
        DataTable dtnew = new DataTable();
        DataTable dtnew1 = new DataTable();
        List<Department> objDept = new List<Department>();

        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        {
            using (MySqlCommand cmdnew = new MySqlCommand("SELECT * from fee_category ", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                using (MySqlDataAdapter danew = new MySqlDataAdapter(cmdnew))
                {
                    danew.Fill(dtnew);
                    con.Close();
                    if (dtnew.Rows.Count > 0)
                    {
                        for (int i = 0; i < dtnew.Rows.Count; i++)
                        {
                            objDept.Add(new Department
                            {
                                DeptId = Convert.ToInt32(dtnew.Rows[i]["id"]),
                                DeptName = dtnew.Rows[i]["feesname"].ToString(),
                               
                            });
                        }
                    }
                }
            }
        }
        return objDept;
    }
    [WebMethod]
    public static List<Department> PopulateDropDownList5()
    {
        DataTable dtnew = new DataTable();
        DataTable dtnew1 = new DataTable();
        List<Department> objDept = new List<Department>();

        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        {
            using (MySqlCommand cmdnew = new MySqlCommand("SELECT * from tbl_countries where id = 101", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                using (MySqlDataAdapter danew = new MySqlDataAdapter(cmdnew))
                {
                    danew.Fill(dtnew);
                    con.Close();
                    if (dtnew.Rows.Count > 0)
                    {
                        for (int i = 0; i < dtnew.Rows.Count; i++)
                        {
                            objDept.Add(new Department
                            {
                                DeptId = Convert.ToInt32(dtnew.Rows[i]["id"]),
                                DeptName = dtnew.Rows[i]["name"].ToString(),

                            });
                        }
                    }
                }
            }
        }
        return objDept;
    }
    [WebMethod]
    public static List<Department> PopulateDropDownList6()
    {
        DataTable dtnew = new DataTable();
        DataTable dtnew1 = new DataTable();
        List<Department> objDept = new List<Department>();

        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        {
            using (MySqlCommand cmdnew = new MySqlCommand("SELECT * from tbl_states where id= 22", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                using (MySqlDataAdapter danew = new MySqlDataAdapter(cmdnew))
                {
                    danew.Fill(dtnew);
                    con.Close();
                    if (dtnew.Rows.Count > 0)
                    {
                        for (int i = 0; i < dtnew.Rows.Count; i++)
                        {
                            objDept.Add(new Department
                            {
                                DeptId = Convert.ToInt32(dtnew.Rows[i]["id"]),
                                DeptName = dtnew.Rows[i]["name"].ToString(),

                            });
                        }
                    }
                }
            }
        }
        return objDept;
    }
    [WebMethod]
    public static List<Department> PopulateDropDownList7()
    {
        DataTable dtnew = new DataTable();
        DataTable dtnew1 = new DataTable();
        List<Department> objDept = new List<Department>();

        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        {
            using (MySqlCommand cmdnew = new MySqlCommand("SELECT * from tbl_cities where state_id = '22' order by id ASC", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                using (MySqlDataAdapter danew = new MySqlDataAdapter(cmdnew))
                {
                    danew.Fill(dtnew);
                    con.Close();
                    if (dtnew.Rows.Count > 0)
                    {
                        for (int i = 0; i < dtnew.Rows.Count; i++)
                        {
                            objDept.Add(new Department
                            {
                                DeptId = Convert.ToInt32(dtnew.Rows[i]["id"]),
                                DeptName = dtnew.Rows[i]["name"].ToString(),

                            });
                        }
                    }
                }
            }
        }
        return objDept;
    }
    [WebMethod]
    public static List<Department> PopulateDropDownList8()
    {
        DataTable dtnew = new DataTable();
        DataTable dtnew1 = new DataTable();
        List<Department> objDept = new List<Department>();

        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        {
            using (MySqlCommand cmdnew = new MySqlCommand("SELECT *,date_format(fromdate,'%Y-%m-%d') as acdyear1,date_format(todate,'%Y-%m-%d') as acdyear2 from academic_year", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                using (MySqlDataAdapter danew = new MySqlDataAdapter(cmdnew))
                {
                    danew.Fill(dtnew);
                    con.Close();
                    if (dtnew.Rows.Count > 0)
                    {
                        for (int i = 0; i < dtnew.Rows.Count; i++)
                        {
                            objDept.Add(new Department
                            {
                                DeptId = Convert.ToInt32(dtnew.Rows[i]["id"]),
                                DeptName = dtnew.Rows[i]["acdyear1"].ToString(),
                                year1 = dtnew.Rows[i]["acdyear2"].ToString(),
                            });
                        }
                    }
                }
            }
        }
        return objDept;
    }
    [WebMethod]
    public static List<Department> PopulateDropDownList9()
    {
        DataTable dtnew = new DataTable();
        DataTable dtnew1 = new DataTable();
        List<Department> objDept = new List<Department>();

        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        {
            using (MySqlCommand cmdnew = new MySqlCommand("SELECT * from class_master", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                using (MySqlDataAdapter danew = new MySqlDataAdapter(cmdnew))
                {
                    danew.Fill(dtnew);
                    con.Close();
                    if (dtnew.Rows.Count > 0)
                    {
                        for (int i = 0; i < dtnew.Rows.Count; i++)
                        {
                            objDept.Add(new Department
                            {
                                DeptId = Convert.ToInt32(dtnew.Rows[i]["id"]),
                                DeptName = dtnew.Rows[i]["class"].ToString(),

                            });
                        }
                    }
                }
            }
        }
        return objDept;
    }
    [WebMethod]
    public static List<Department> PopulateDropDownList10(string clids)
    {
        DataTable dtnew = new DataTable();
        DataTable dtnew1 = new DataTable();
        List<Department> objDept = new List<Department>();

        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        {
            using (MySqlCommand cmdnew = new MySqlCommand("SELECT * from division_master where classid = '"+ clids +"'", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                using (MySqlDataAdapter danew = new MySqlDataAdapter(cmdnew))
                {
                    danew.Fill(dtnew);
                    con.Close();
                    if (dtnew.Rows.Count > 0)
                    {
                        for (int i = 0; i < dtnew.Rows.Count; i++)
                        {
                            objDept.Add(new Department
                            {
                                DeptId = Convert.ToInt32(dtnew.Rows[i]["id"]),
                                DeptName = dtnew.Rows[i]["division"].ToString(),

                            });
                        }
                    }
                }
            }
        }
        return objDept;
    }
    [WebMethod]
    public static List<Department> PopulateDropDownList11(string yrsid, string clsid, string divisids)
    {
        DataTable dtnew = new DataTable();
        DataTable dtnew1 = new DataTable();
        List<Department> objDept = new List<Department>();

        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        {
            using (MySqlCommand cmdnew = new MySqlCommand("SELECT * from student_detail where admyears = '" + yrsid + "' and  classd = '" + clsid + "' and division = '" + divisids + "'", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                using (MySqlDataAdapter danew = new MySqlDataAdapter(cmdnew))
                {
                    danew.Fill(dtnew);
                    con.Close();
                    if (dtnew.Rows.Count > 0)
                    {
                        for (int i = 0; i < dtnew.Rows.Count; i++)
                        {
                            objDept.Add(new Department
                            {
                                DeptName = dtnew.Rows[i]["studid"].ToString(),
                                firstname = dtnew.Rows[i]["fname"].ToString(),
                                midname = dtnew.Rows[i]["mname"].ToString(),
                                lastname = dtnew.Rows[i]["lname"].ToString(),
                            });
                        }
                    }
                }
            }
        }
        return objDept;
    }
    [WebMethod]
    public static object GetDatasearch(string yrsid, string cldid, string divsid, string studsid)
    {
        string data = " ";
        if (studsid == "0")
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studentids,Date_Format(student_detail.dob,'%d-%m-%Y') as studdob,class_master.class as classname,division_master.division as divname ,academic_year.fromdate,academic_year.todate,f1.feesname as feename,tbl_states.name as stname,tbl_cities.name as cityname from  student_detail left join class_master on student_detail.classd = class_master.id left join division_master on student_detail.division = division_master.id left join academic_year  on student_detail.admyears = academic_year.id left join fee_category as f1 on student_detail.castcategory = f1.id left join tbl_states on student_detail.state = tbl_states.id left join tbl_cities on student_detail.city = tbl_cities.id where student_detail.admyears = '" + yrsid + "' and  student_detail.classd = '" + cldid + "' and  student_detail.division = '" + divsid + "' ", con))
                {
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    DataSet ds = new DataSet();
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds);
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }
                        con.Close();
                        con.Dispose();
                        cmd.Dispose();
                    }
                }
            }
        }
        else if (studsid != "0")
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studentids,Date_Format(student_detail.dob,'%d-%m-%Y') as studdob,class_master.class as classname,division_master.division as divname ,academic_year.fromdate,academic_year.todate,f1.feesname as feename,tbl_states.name as stname,tbl_cities.name as cityname from  student_detail left join class_master on student_detail.classd = class_master.id left join division_master on student_detail.division = division_master.id left join academic_year  on student_detail.admyears = academic_year.id left join fee_category as f1 on student_detail.castcategory = f1.id left join tbl_states on student_detail.state = tbl_states.id left join tbl_cities on student_detail.city = tbl_cities.id where student_detail.admyears = '" + yrsid + "' and  student_detail.classd = '" + cldid + "' and  student_detail.division = '" + divsid + "' and studid = '"+ studsid +"'", con))
                {
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    DataSet ds = new DataSet();
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds);
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }
                        con.Close();
                        con.Dispose();
                        cmd.Dispose();
                    }
                }
            }

        }
        return data;
    }
    [WebMethod]
    public static object GetData()
    {
        string data = " ";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studentids,Date_Format(student_detail.dob,'%d-%m-%Y') as studdob,class_master.class as classname,division_master.division as divname ,academic_year.fromdate,academic_year.todate,f1.feesname as feename,tbl_states.name as stname,tbl_cities.name as cityname from  student_detail left join class_master on student_detail.classd = class_master.id left join division_master on student_detail.division = division_master.id left join academic_year  on student_detail.admyears = academic_year.id left join fee_category as f1 on student_detail.castcategory = f1.id left join tbl_states on student_detail.state = tbl_states.id left join tbl_cities on student_detail.city = tbl_cities.id where canceladmission='0'", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                DataSet ds = new DataSet();
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                {
                    da.Fill(ds);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        data = JsonConvert.SerializeObject(ds.Tables[0]);
                    }
                    con.Close();
                    con.Dispose();
                    cmd.Dispose();
                }
            }
        }
        return data;
    }
    [WebMethod]
    public static object EditData(string id)
    {
        string data = "";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,date_format(student_detail.dob,'%d-%m-%Y') as studdob from student_detail where id='" + id + "'", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        data = JsonConvert.SerializeObject(ds.Tables[0]);
                    }
                    cmd.ExecuteNonQuery();
                    con.Close();
                    con.Dispose();
                    cmd.Dispose();

                }
            }
        }
        return data;
    }
    [WebMethod]
    public static object UpdateData(string upid, string stitle, string fname, string mname, string lname, string pname, string mothernme, string country, string state, string city, string address, string resiaddrss, string peraddrss, string pin, string castcategory, string castes, string bloodgroup, string dob, string gender, string age, string mobile1, string mobile2, string email, string stdyears, string classd, string division, string highquali, string adharcard,string admdate, string status, string isprovisional)
    {
        string data = "";
        if (dob != "")
        {
            date1 = DateTime.Parse(dob);
        }
        if (admdate != "")
        {
            date2 = DateTime.Parse(admdate);
        }
        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
        {
            using (MySqlCommand cmd = new MySqlCommand("update student_detail set title = @title, fname= @fname,mname= @mname,lname= @lname,pname=@pname,mothernme=@mothernme,country=@country,state=@state,city=@city,address=@address,resiaddrss=@resiaddrss,peraddrss=@peraddrss,pin=@pin,castcategory=@castcategory,caste=@caste,bloodgroup=@bloodgroup,gender=@gender,dob=@dob,age=@age,mobile1=@mobile1,mobile2=@mobile2,email=@email,admyears=@admyears,classd=@classd,division=@division,highquali=@highquali,adharcard=@adharcard,status=@status,isprovisional=@isprovisional,today=@today  where id='" + upid + "'", con))

            //using (MySqlCommand cmd = new MySqlCommand("update scheme_list set fname= @fname,mname= @mname,lname= @lname,pname=@pname,mothernme=@mothernme,country=@country,state=@state,city=@city,address=@address,resiaddrss=@resiaddrss,peraddrss=@peraddrss,pin=@pin,castcategory=@castcategory,bloodgroup=@bloodgroup,gender=@gender,dob=@dob,age=@age,mobile1=@mobile1,mobile2=@mobile2,email=@email,classd=@classd,division=@division,highquali=@highquali,adharcard=@adharcard,votingcard=@votingcard,bplcard=@bplcard,scheme=@scheme,coursename=@coursename,status=@status,isprovisional=@isprovisional  where id=@id", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                cmd.Parameters.AddWithValue("@title", stitle);
                cmd.Parameters.AddWithValue("@fname", fname);
                cmd.Parameters.AddWithValue("@mname", mname);
                cmd.Parameters.AddWithValue("@lname", lname);
                cmd.Parameters.AddWithValue("@pname", pname);
                cmd.Parameters.AddWithValue("@mothernme", mothernme);
                cmd.Parameters.AddWithValue("@country", country);
                cmd.Parameters.AddWithValue("@state", state);
                cmd.Parameters.AddWithValue("@city", city);
                cmd.Parameters.AddWithValue("@address", address);
                cmd.Parameters.AddWithValue("@resiaddrss", resiaddrss);
                cmd.Parameters.AddWithValue("@peraddrss", peraddrss);
                cmd.Parameters.AddWithValue("@pin", pin);
                cmd.Parameters.AddWithValue("@castcategory", castcategory);
                cmd.Parameters.AddWithValue("@caste", castes);
                cmd.Parameters.AddWithValue("@bloodgroup", bloodgroup);
                cmd.Parameters.AddWithValue("@gender", gender);

               cmd.Parameters.AddWithValue("@dob", date1.ToString("yyyy/MM/dd"));
                cmd.Parameters.AddWithValue("@age", age);
                cmd.Parameters.AddWithValue("@mobile1", mobile1);
                cmd.Parameters.AddWithValue("@mobile2", mobile2);
                cmd.Parameters.AddWithValue("@email", email);
                cmd.Parameters.AddWithValue("@admyears",stdyears);
                cmd.Parameters.AddWithValue("@classd", classd);
                cmd.Parameters.AddWithValue("@division", division);
                cmd.Parameters.AddWithValue("@highquali", highquali);
                cmd.Parameters.AddWithValue("@adharcard", adharcard);
             
              
                cmd.Parameters.AddWithValue("@status", status);
                cmd.Parameters.AddWithValue("@isprovisional", isprovisional);
                cmd.Parameters.AddWithValue("@today", date2.ToString("yyyy/MM/dd"));
                cmd.ExecuteNonQuery();
                cmd.Connection = con;
                con.Close();
                con.Dispose();
                cmd.Dispose();
            }
        }
        return data;
    }
    [WebMethod]
    public static object DeleteData(string id)
    {
        string data = "";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            if (con.State != ConnectionState.Open) { con.Open(); }
            using (MySqlCommand cmd = new MySqlCommand("delete from student_detail where id='" + id + "'", con))
            {

                cmd.ExecuteNonQuery();
                using (MySqlDataAdapter dr = new MySqlDataAdapter(cmd))
                {
                    con.Close();
                    con.Dispose();
                    cmd.Dispose();

                }
            }
        }
        return data;
    }

    [WebMethod]
    public static string GetInitial()
    {
        string BookingID = "";
        Random rr = new Random();
        BookingID = rr.Next(0, 999999).ToString();

        string _data = "";

        _data = JsonConvert.SerializeObject(BookingID);

        return _data;
    }
    [WebMethod]
    public static string GetEmpData()
    {
        string _data = "";
        string BookingID;
        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        {
            string query = "select id from student_detail order by id Desc";
            if (con.State != ConnectionState.Open) { con.Open(); }
            using (MySqlCommand cmd = new MySqlCommand(query, con))
            {
                MySqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    int uhid = Convert.ToInt32((dr[0].ToString())) + 1;
                    BookingID = uhid.ToString("000");

                }
                else if (Convert.IsDBNull(dr))
                {
                    BookingID = ("001");

                }
                else
                {

                    BookingID = ("001");
                }
                dr.Close();
                cmd.Dispose();
                con.Close();

                _data = JsonConvert.SerializeObject(BookingID);
            }
        }
        return _data;
    }
    [WebMethod]
    public static void UploadAndProcessFile(string file, string name)
    {

        string BookingID = "";

        //if (filename3.HasFile)
        {
            FileInfo fileInfo = new FileInfo(file);
            if (fileInfo.Name.Contains(".csv"))
            {
                string fileName = fileInfo.Name.Replace(".csv", "").ToString();
                string dd = ("~/Document") + "\\" + fileInfo.Name;
                // string csvFilePath = Server.MapPath(dd) ;

                // Save the CSV file in the Server inside 'UploadedCSVFiles'   
                //filename3.SaveAs(csvFilePath);

                // Fetch the location of CSV file  
                string df = "~/Document";
                string filePath = HttpContext.Current.Server.MapPath(df) + "\\";

                string strSql = string.Format("SELECT * FROM [{0}]", fileInfo.Name);
                string strCSVConnString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='text;HDR=YES;FMT=CSVDelimited'", filePath);

                // load the data from CSV to DataTable   
                DataTable dtCSV = new DataTable();
                DataTable dtSchema = new DataTable();
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(strSql, strCSVConnString))
                {
                    //adapter.FillSchema(dtCSV, SchemaType.Mapped);
                    adapter.Fill(dtCSV);
                }

                if (dtCSV.Rows.Count > 0)
                {

                    string fileFullPath = filePath + fileInfo.Name;

                    if (dtCSV != null)
                    {
                        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
                        {
                            using (MySqlCommand opd = new MySqlCommand("Select * from student_detail order by id desc limit 1", con))
                            {
                                if (con.State != ConnectionState.Open) { con.Open(); }

                                using (MySqlDataAdapter rdr = new MySqlDataAdapter(opd))
                                {
                                    DataSet ds = new DataSet();
                                    rdr.Fill(ds); con.Close();

                                    if (ds.Tables[0].Rows.Count > 0)
                                    {
                                        grouplastid = ds.Tables[0].Rows.Count;
                                    }
                                }

                            }
                        }
                        //List<Customer> list = new List<Customer>();
                        for (int i = 0; i < dtCSV.Rows.Count; i++)
                        {
                            // Customer obj = new Customer();
                            using (MySqlConnection con34 = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
                            {
                                string query = "select id from student_detail order by id Desc";
                                if (con34.State != ConnectionState.Open) { con34.Open(); }
                                using (MySqlCommand cmd = new MySqlCommand(query, con34))
                                {
                                    MySqlDataReader dr = cmd.ExecuteReader();
                                    if (dr.Read())
                                    {
                                        int uhid = Convert.ToInt32((dr[0].ToString())) + 1;
                                        BookingID = uhid.ToString("000");

                                    }
                                    else if (Convert.IsDBNull(dr))
                                    {
                                        BookingID = ("001");

                                    }
                                    else
                                    {

                                        BookingID = ("001");
                                    }
                                    //DateTime dt = DateTime.Now;
                                    //string _dateString = dt.ToString("dd/MM/yyyy");
                                    //dtsplits = _dateString.Split('/');
                                    //dtmonths = dtsplits[1];
                                    //dtyears = dtsplits[2];

                                    studentids = "STUD/" + DateTime.Now.Month.ToString("0#") + DateTime.Now.ToString("yy") + "/" + BookingID;
                               
                                    dr.Close();
                                    cmd.Dispose();
                                    con34.Close();
                                 
                                 
                                }
                            }
                            sttitle = dtCSV.Rows[i]["title"].ToString();

                            stfname = dtCSV.Rows[i]["fname"].ToString();
                            stmname = dtCSV.Rows[i]["mname"].ToString();
                         stlname = dtCSV.Rows[i]["lname"].ToString();
                         stpname = dtCSV.Rows[i]["pname"].ToString();
                         stmothernm = dtCSV.Rows[i]["mothernme"].ToString();
                         stcountry = dtCSV.Rows[i]["country"].ToString();
                         ststates = dtCSV.Rows[i]["state"].ToString();
                         stcitys = dtCSV.Rows[i]["city"].ToString();
                            staddress = dtCSV.Rows[i]["address"].ToString();
                            stresiaddress = dtCSV.Rows[i]["resiaddrss"].ToString();
                            stpermaddress = dtCSV.Rows[i]["peraddrss"].ToString();
                            stpincodes  = dtCSV.Rows[i]["pin"].ToString();
                            stcastes  = dtCSV.Rows[i]["castcategory"].ToString();
                            stsubcastes = dtCSV.Rows[i]["subcaste"].ToString();
                            stbloodgrps =  dtCSV.Rows[i]["bloodgroup"].ToString();
                            stgenders = dtCSV.Rows[i]["gender"].ToString();
                            stdob = dtCSV.Rows[i]["dob"].ToString();

                            if (stdob != "")
                            {
                                date2 = DateTime.Parse(stdob);
                            }
                            stages = dtCSV.Rows[i]["age"].ToString();
                            stmobiles = dtCSV.Rows[i]["mobile1"].ToString();
                            stgemials = dtCSV.Rows[i]["email"].ToString();
                            styeasrs = dtCSV.Rows[i]["admyears"].ToString();
                            stclssesid = dtCSV.Rows[i]["classd"].ToString();
                            stdivsid = dtCSV.Rows[i]["division"].ToString();
                            sthighquali = dtCSV.Rows[i]["highquali"].ToString();
                            staadharids = dtCSV.Rows[i]["adharcard"].ToString();
                            ststauss = dtCSV.Rows[i]["status"].ToString();
                            isprovi = dtCSV.Rows[i]["isprovisional"].ToString();
                            regdate = dtCSV.Rows[i]["today"].ToString();
                            if (regdate != "")
                            {
                                date3 = DateTime.Parse(regdate);
                            }
                            using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
                            {
                                using (MySqlCommand opd = new MySqlCommand("Select * from student_detail where studid ='" + studentids + "'", con))
                                {
                                    if (con.State != ConnectionState.Open) { con.Open(); }

                                    using (MySqlDataAdapter rdr = new MySqlDataAdapter(opd))
                                    {
                                        DataTable dt = new DataTable();
                                        rdr.Fill(dt); con.Close();

                                        if (dt.Rows.Count > 0)
                                        {
                                           
                                        
                                            using (MySqlConnection con2 = new MySqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
                                            {

                                                string query1 = "update student_detail set title=@title,fname=@fname,mname=@mname,lname=@lname,pname=@pname,mothernme=@mothernme,country=@country,state=@state,city=@city,address=@address,resiaddrss=@resiaddrss,peraddrss=@peraddrss,pin=@pin,castcategory=@castcategory,caste=@caste,bloodgroup=@bloodgroup,gender=@gender,dob=@dob,age=@age,mobile1=@mobile1,mobile2=@mobile2,email=@email,admyears=@admyears,classd=@classd,division=@division,highquali=@highquali,adharcard=@adharcard,votingcard=@votingcard,bplcard=@bplcard,scheme=@scheme,coursename=@coursename,status=@status,isprovisional=@isprovisional, today=@today where studid = @studnos";
                                                using (MySqlCommand cmd = new MySqlCommand(query1, con2))
                                                {

                                                    if (con2.State != ConnectionState.Open) { con2.Open(); }


                                                    cmd.Parameters.AddWithValue("@studnos", studentids);
                                                    cmd.Parameters.AddWithValue("@title", sttitle);

                                                    cmd.Parameters.AddWithValue("@fname", stfname);
                                                    cmd.Parameters.AddWithValue("@mname", stmname);
                                                    cmd.Parameters.AddWithValue("@lname", stlname);
                                                    cmd.Parameters.AddWithValue("@pname", stpname);
                                                    cmd.Parameters.AddWithValue("@mothernme", stmothernm);
                                                    cmd.Parameters.AddWithValue("@country", stcountry);
                                                    cmd.Parameters.AddWithValue("@state", ststates);
                                                    //  cmd.Parameters.AddWithValue("@pass", dtCSV.Rows[i]["custname"].ToString());
                                                    cmd.Parameters.AddWithValue("@city", stcitys);
                                                    cmd.Parameters.AddWithValue("@address", staddress);
                                                    cmd.Parameters.AddWithValue("@resiaddrss", stresiaddress);
                                                    cmd.Parameters.AddWithValue("@peraddrss", stpermaddress);
                                                    cmd.Parameters.AddWithValue("@pin", stpincodes);
                                                    cmd.Parameters.AddWithValue("@castcategory", stcastes);
                                                    cmd.Parameters.AddWithValue("@caste", stsubcastes);
                                                    cmd.Parameters.AddWithValue("@bloodgroup", stbloodgrps);
                                                    cmd.Parameters.AddWithValue("@gender", stgenders);
                                                    cmd.Parameters.AddWithValue("@dob", date2.ToString("yyyy/MM/dd"));
                                                    cmd.Parameters.AddWithValue("@age", stages);
                                                    cmd.Parameters.AddWithValue("@mobile1", stmobiles);
                                                    cmd.Parameters.AddWithValue("@email", stgemials);
                                                    cmd.Parameters.AddWithValue("@admyears", styeasrs);
                                                    cmd.Parameters.AddWithValue("@classd", stclssesid);
                                                    cmd.Parameters.AddWithValue("@division", stdivsid);
                                                    cmd.Parameters.AddWithValue("@highquali", sthighquali);
                                                    cmd.Parameters.AddWithValue("@adharcard", staadharids);
                                                    cmd.Parameters.AddWithValue("@status", ststauss);
                                                    cmd.Parameters.AddWithValue("@isprovisional", isprovi);
                                                    cmd.Parameters.AddWithValue("@today", date3.ToString("yyyy/MM/dd"));

                                                  
                                                    cmd.ExecuteNonQuery();
                                                    con2.Close();
                                                }
                                            }

                                        }
                                        else
                                        {
                                           
                                            //if (dt.Rows.Count > 0)
                                            //{
                                            //for (int j = 0; j < i; i++)
                                            //{
                                           
                                            // }
                                            //  }
                                            using (MySqlConnection con2 = new MySqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
                                            {

                                                string query1 = "Insert into student_detail(studid,title,fname,mname,lname,pname,mothernme,country,state,city,address,resiaddrss,peraddrss,pin,castcategory,caste,bloodgroup,gender,dob,age,mobile1,email,admyears,classd,division,highquali,adharcard,status,isprovisional,today) values(@studnos,@title,@fname,@mname,@lname,@pname,@mothernme,@country,@state,@city,@address,@resiaddrss,@peraddrss,@pin,@castcategory,@caste,@bloodgroup,@gender,@dob,@age,@mobile1,@email,@admyears,@classd,@division,@highquali,@adharcard,@status,@isprovisional,today)";
                                                using (MySqlCommand cmd = new MySqlCommand(query1, con2))
                                                {

                                                    if (con2.State != ConnectionState.Open) { con2.Open(); }


                                                    cmd.Parameters.AddWithValue("@studnos", studentids);
                                                    cmd.Parameters.AddWithValue("@title", sttitle);

                                                    cmd.Parameters.AddWithValue("@fname", stfname);
                                                    cmd.Parameters.AddWithValue("@mname", stmname);
                                                    cmd.Parameters.AddWithValue("@lname", stlname);
                                                    cmd.Parameters.AddWithValue("@pname", stpname);
                                                    cmd.Parameters.AddWithValue("@mothernme", stmothernm);
                                                    cmd.Parameters.AddWithValue("@country", stcountry);
                                                    cmd.Parameters.AddWithValue("@state", ststates);
                                                    //  cmd.Parameters.AddWithValue("@pass", dtCSV.Rows[i]["custname"].ToString());
                                                    cmd.Parameters.AddWithValue("@city", stcitys);
                                                    cmd.Parameters.AddWithValue("@address", staddress);
                                                    cmd.Parameters.AddWithValue("@resiaddrss", stresiaddress);
                                                    cmd.Parameters.AddWithValue("@peraddrss", stpermaddress);
                                                    cmd.Parameters.AddWithValue("@pin", stpincodes);
                                                    cmd.Parameters.AddWithValue("@castcategory",stcastes);
                                                    cmd.Parameters.AddWithValue("@caste", stsubcastes);
                                                    cmd.Parameters.AddWithValue("@bloodgroup", stbloodgrps);
                                                    cmd.Parameters.AddWithValue("@gender", stgenders);
                                                    cmd.Parameters.AddWithValue("@dob", date2.ToString("yyyy/MM/dd"));
                                                    cmd.Parameters.AddWithValue("@age", stages);
                                                    cmd.Parameters.AddWithValue("@mobile1", stmobiles);
                                                    cmd.Parameters.AddWithValue("@email", stgemials);
                                                    cmd.Parameters.AddWithValue("@admyears", styeasrs);
                                                    cmd.Parameters.AddWithValue("@classd", stclssesid);
                                                    cmd.Parameters.AddWithValue("@division", stdivsid);
                                                    cmd.Parameters.AddWithValue("@highquali", sthighquali);
                                                    cmd.Parameters.AddWithValue("@adharcard", staadharids);
                                                    cmd.Parameters.AddWithValue("@status", ststauss);
                                                    cmd.Parameters.AddWithValue("@isprovisional", isprovi);
                                                    cmd.Parameters.AddWithValue("@today", date3.ToString("yyyy/MM/dd"));

                                                    cmd.ExecuteNonQuery();
                                                    con2.Close();
                                                }
                                            }
                                        }


                                    }
                                }
                            }
                            //list.Add(obj);
                        }

                        // Insert(Repeater1.DataSource as List<Customer>);
                    }

                    //LoadDataToDatabase("medicine", fileFullPath, ",");
                    //Label1.Text = string.Format("({0}) records has been loaded to the table {1}.", dtCSV.Rows.Count, fileName);
                }
                else
                {
                    HttpContext.Current.Response.Write("file empty");
                    //lblError.Text = "File is empty.";
                }
            }
            //else
            //{
            //    Response.Write("file empty1");
            //   // lblError.Text = "Unable to recognize file.";
            //}
        }
    }
    public partial class SalesOrderDetail
    {
        public int id { get; set; }
        public string Uhid { get; set; }
        public string firstName { get; set; }
        public string middleName { get; set; }
        public string lastName { get; set; }
        public string age { get; set; }
        public string gender { get; set; }
        public string mobile { get; set; }
        public string address { get; set; }
        public string address1 { get; set; }
        public string email { get; set; }
        public string fullname { get; set; }
        public string registrationdt { get; set; }
        public string appointmentdt { get; set; }
        public string referredby { get; set; }
        public string initial { get; set; }
        public string category { get; set; }
        public string remark { get; set; }
        public string path { get; set; }
        public string stitle { get; set; }
        public string studid { get; set; }
        public string statenm { get; set; }
        public string pinnos { get; set; }
        public string feenm { get; set; }
        public string studentdob { get; set; }   public string clsnm { get; set; }   public string divsnm { get; set; }
        public string aadharcardnos { get; set; }   public string admissiondt { get; set; }   public string ststaus { get; set; }
        public string fromdt { get; set; }   public string todt { get; set; }  public string stryear { get; set; } public string isprovi { get; set; } public string studaddress { get; set; } 
    }

    public class DataTables
    {
        public int draw { get; set; }
        public int recordsTotal { get; set; }
        public int recordsFiltered { get; set; }
        public List<SalesOrderDetail> data { get; set; }
        public string prefix { get; set; }
        public string prefix1 { get; set; }
    }
    [WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json, UseHttpGet = true)]
    public static object GetEmpData14()
    {
        // Initialization.    
        DataTables result = new DataTables();
        // Capture Jquery Datatables Plugin Properties.    
        string search = HttpContext.Current.Request.Params["search[value]"];
        var draw12 = HttpContext.Current.Request.Params["draw"];
        string order12 = HttpContext.Current.Request.Params["columns[" + HttpContext.Current.Request.Params["order[0][column]"] + "][name]"];
        string orderDir12 = HttpContext.Current.Request.Params["order[0][dir]"];
        int startRec = Convert.ToInt32(HttpContext.Current.Request.Params["start"]);
        int pageSize = Convert.ToInt32(HttpContext.Current.Request.Params["length"]);
    

       

        // Load data from text file.   
        List<SalesOrderDetail> data = new List<SalesOrderDetail>();
        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        {
            using (MySqlCommand cmdnew = new MySqlCommand("select student_detail.*,student_detail.id as studentids,Date_Format(student_detail.dob,'%d-%m-%Y') as studdob,Date_Format(student_detail.today,'%d-%m-%Y') as admdt,class_master.class as classname,division_master.division as divname,academic_year.fromdate,academic_year.todate,f1.feesname as feename,tbl_states.name as stname,tbl_cities.name as cityname from  student_detail left join class_master on student_detail.classd = class_master.id left join division_master on student_detail.division = division_master.id left join academic_year  on student_detail.admyears = academic_year.id left join fee_category as f1 on student_detail.castcategory = f1.id left join tbl_states on student_detail.state = tbl_states.id left join tbl_cities on student_detail.city = tbl_cities.id where canceladmission='0'", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                DataTable dtnew = new DataTable();
                MySqlDataAdapter danew = new MySqlDataAdapter(cmdnew);
                danew.Fill(dtnew);
                con.Close();
                if (dtnew.Rows.Count > 0)
                {
                    for (int i = 0; i < dtnew.Rows.Count; i++)
                    {
                        if (dtnew.Rows[i]["fromdate"].ToString() != "")
                        {
                            mdate = DateTime.Parse(dtnew.Rows[i]["fromdate"].ToString());
                        }
                        if (dtnew.Rows[i]["todate"].ToString() != "")
                        {
                            mdate1 = DateTime.Parse(dtnew.Rows[i]["todate"].ToString());
                        }
                       
                        data.Add(new SalesOrderDetail
                        {
                            id = Convert.ToInt32(dtnew.Rows[i]["studentids"]),
                            stitle = dtnew.Rows[i]["title"].ToString(),
                            studid = dtnew.Rows[i]["studid"].ToString(),
                            firstName = dtnew.Rows[i]["fname"].ToString(),
                            middleName = dtnew.Rows[i]["mname"].ToString(),
                            lastName = dtnew.Rows[i]["lname"].ToString(),
                            age = dtnew.Rows[i]["resiaddrss"].ToString(),
                            gender = dtnew.Rows[i]["cityname"].ToString(),
                            statenm = dtnew.Rows[i]["stname"].ToString(),
                            pinnos = dtnew.Rows[i]["pin"].ToString(),
                            mobile = dtnew.Rows[i]["mobile1"].ToString(),
                            feenm = dtnew.Rows[i]["feename"].ToString(),
                            fromdt = mdate.Year.ToString(),
                            todt = mdate1.Year.ToString(),
                            stryear = mdate.Year.ToString() + "-" + mdate1.Year.ToString(),
                            email = dtnew.Rows[i]["email"].ToString(),
                            fullname = dtnew.Rows[i]["title"].ToString()+"."
                            + dtnew.Rows[i]["fname"].ToString()
                            + " " + dtnew.Rows[i]["mname"].ToString()
                            + " " + dtnew.Rows[i]["lname"].ToString()
                            + " / " + dtnew.Rows[i]["age"].ToString()
                            + " / " + dtnew.Rows[i]["gender"].ToString()
                         
                            + "/" + dtnew.Rows[i]["feename"].ToString(),
                        
                            studaddress = dtnew.Rows[i]["resiaddrss"].ToString()
                            + "," + dtnew.Rows[i]["cityname"].ToString()
                            +","+ dtnew.Rows[i]["stname"].ToString()
                            +","+ dtnew.Rows[i]["pin"].ToString(),
                            studentdob = dtnew.Rows[i]["studdob"].ToString(),
                            clsnm = dtnew.Rows[i]["classname"].ToString(),
                            divsnm = dtnew.Rows[i]["divname"].ToString(),
                            aadharcardnos =  dtnew.Rows[i]["adharcard"].ToString(),
                            admissiondt = dtnew.Rows[i]["admdt"].ToString(),
                         
                            ststaus = dtnew.Rows[i]["status"].ToString(),
                            isprovi = dtnew.Rows[i]["isprovisional"].ToString(),
                          

                        });
                    }
                }

            }
        }


        // Configure Jquery Datatable property Total record count property.   
        int totalRecords = 0;
        totalRecords = data.Count;

        // Apply server-side data searching.   

        if (!string.IsNullOrEmpty(search))
        {
            data = data.Where(x => x.studid.ToLower().Contains(search.ToLower())).ToList<SalesOrderDetail>();
        }


        if (orderDir12.Equals("desc") || orderDir12.Equals("DESC"))
        {

            data = data.OrderByDescending(x => x.Uhid).ToList<SalesOrderDetail>();

        }
        else
        {


        }

        // Configure Jquery Datatable property Filter record count property after applying searching and sorting.    

        int recFilter = data.Count;
        // Apply server-side pagination.    
        data = data.Skip(startRec).Take(pageSize).ToList<SalesOrderDetail>();
        result.data = data;
        // Mapping final configuration settings for Jquery Datatables plugin.    
        result.draw = Convert.ToInt32(draw12);
        result.recordsTotal = totalRecords;
        result.recordsFiltered = recFilter;
       
        return result;
    }
    [WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json, UseHttpGet = true)]
    public static object GetEmpData15()
    {
        // Initialization.    
        DataTables result = new DataTables();
        // Capture Jquery Datatables Plugin Properties.    
        string search = HttpContext.Current.Request.Params["search[value]"];
        var draw12 = HttpContext.Current.Request.Params["draw"];
        string order12 = HttpContext.Current.Request.Params["columns[" + HttpContext.Current.Request.Params["order[0][column]"] + "][name]"];
        string orderDir12 = HttpContext.Current.Request.Params["order[0][dir]"];
        int startRec = Convert.ToInt32(HttpContext.Current.Request.Params["start"]);
        int pageSize = Convert.ToInt32(HttpContext.Current.Request.Params["length"]);
        string prefix = HttpContext.Current.Request.Params["prefix"];
        string prefix1 = HttpContext.Current.Request.Params["prefix1"];
        string prefix2 = HttpContext.Current.Request.Params["prefix2"];
       string prefix3 = HttpContext.Current.Request.Params["prefix3"];


        // Load data from text file.   
        List<SalesOrderDetail> data = new List<SalesOrderDetail>();
        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        {
            using (MySqlCommand cmdnew = new MySqlCommand("select student_detail.*,student_detail.id as studentids,Date_Format(student_detail.dob,'%d-%m-%Y') as studdob,Date_Format(student_detail.today,'%d-%m-%Y') as admdt,class_master.class as classname,division_master.division as divname ,academic_year.fromdate,academic_year.todate,f1.feesname as feename,tbl_states.name as stname,tbl_cities.name as cityname from  student_detail left join class_master on student_detail.classd = class_master.id left join division_master on student_detail.division = division_master.id left join academic_year  on student_detail.admyears = academic_year.id left join fee_category as f1 on student_detail.castcategory = f1.id left join tbl_states on student_detail.state = tbl_states.id left join tbl_cities on student_detail.city = tbl_cities.id where student_detail.admyears = '" + prefix + "' and  student_detail.classd = '" + prefix1 + "' and  student_detail.division = '" + prefix2 + "' and  student_detail.studid = '" + prefix3 + "'", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                DataTable dtnew = new DataTable();
                MySqlDataAdapter danew = new MySqlDataAdapter(cmdnew);
                danew.Fill(dtnew);
                con.Close();
                if (dtnew.Rows.Count > 0)
                {
                    for (int i = 0; i < dtnew.Rows.Count; i++)
                    {
                        if (dtnew.Rows[i]["fromdate"].ToString() != "")
                        {
                            mdate = DateTime.Parse(dtnew.Rows[i]["fromdate"].ToString());
                        }
                        if (dtnew.Rows[i]["todate"].ToString() != "")
                        {
                            mdate1 = DateTime.Parse(dtnew.Rows[i]["todate"].ToString());
                        }

                        data.Add(new SalesOrderDetail
                        {
                            id = Convert.ToInt32(dtnew.Rows[i]["studentids"]),
                            stitle = dtnew.Rows[i]["title"].ToString(),
                            studid = dtnew.Rows[i]["studid"].ToString(),
                            firstName = dtnew.Rows[i]["fname"].ToString(),
                            middleName = dtnew.Rows[i]["mname"].ToString(),
                            lastName = dtnew.Rows[i]["lname"].ToString(),
                            age = dtnew.Rows[i]["resiaddrss"].ToString(),
                            gender = dtnew.Rows[i]["cityname"].ToString(),
                            statenm = dtnew.Rows[i]["stname"].ToString(),
                            pinnos = dtnew.Rows[i]["pin"].ToString(),
                            mobile = dtnew.Rows[i]["mobile1"].ToString(),
                            feenm = dtnew.Rows[i]["feename"].ToString(),
                            fromdt = mdate.Year.ToString(),
                            todt = mdate1.Year.ToString(),
                            stryear = mdate.Year.ToString() + "-" + mdate1.Year.ToString(),
                            email = dtnew.Rows[i]["email"].ToString(),
                            fullname = dtnew.Rows[i]["title"].ToString() + "."
                            + dtnew.Rows[i]["fname"].ToString()
                            + " " + dtnew.Rows[i]["mname"].ToString()
                            + " " + dtnew.Rows[i]["lname"].ToString()
                            + " / " + dtnew.Rows[i]["age"].ToString()
                            + " / " + dtnew.Rows[i]["gender"].ToString()
                           
                            + "/" + dtnew.Rows[i]["feename"].ToString(),
                          
                            studaddress = dtnew.Rows[i]["resiaddrss"].ToString()
                            + "," + dtnew.Rows[i]["cityname"].ToString()
                            + "," + dtnew.Rows[i]["stname"].ToString()
                            + "," + dtnew.Rows[i]["pin"].ToString(),
                            studentdob = dtnew.Rows[i]["studdob"].ToString(),
                            clsnm = dtnew.Rows[i]["classname"].ToString(),
                            divsnm = dtnew.Rows[i]["divname"].ToString(),
                            aadharcardnos = dtnew.Rows[i]["adharcard"].ToString(),
                            admissiondt = dtnew.Rows[i]["admdt"].ToString(),

                            ststaus = dtnew.Rows[i]["status"].ToString(),
                            isprovi = dtnew.Rows[i]["isprovisional"].ToString(),


                        });
                    }
                }

            }
        }


        // Configure Jquery Datatable property Total record count property.   
        int totalRecords = 0;
        totalRecords = data.Count;

        // Apply server-side data searching.   

        if (!string.IsNullOrEmpty(search))
        {
            data = data.Where(x => x.studid.ToLower().Contains(search.ToLower())).ToList<SalesOrderDetail>();
        }


        if (orderDir12.Equals("desc") || orderDir12.Equals("DESC"))
        {

            data = data.OrderByDescending(x => x.Uhid).ToList<SalesOrderDetail>();

        }
        else
        {


        }

        // Configure Jquery Datatable property Filter record count property after applying searching and sorting.    

        int recFilter = data.Count;
        // Apply server-side pagination.    
        data = data.Skip(startRec).Take(pageSize).ToList<SalesOrderDetail>();
        result.data = data;
        // Mapping final configuration settings for Jquery Datatables plugin.    
        result.draw = Convert.ToInt32(draw12);
        result.recordsTotal = totalRecords;
        result.recordsFiltered = recFilter;

        return result;
    }
}