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.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;

public partial class module_studentlistclswise : 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;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    [WebMethod]
    public static object GetData()
    {
        string data = " ";
        string ai = "0";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,date_format(student_detail.today,'%d-%m-%Y') as admdate,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,fee_category.feesname as feesnm 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 left join fee_category on student_detail.castcategory = fee_category.id where student_detail.canceladmission = '"+ ai +"'", 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 GetDataSearch(string clasr, string division, string acdyear, string castes, string genders, string stdate, string enddt)
    {
        string data = "";
        string ai = "0";
        if (clasr != "0" && division == "0" && acdyear != "0" && castes == "0" && genders == "")
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,date_format(student_detail.today,'%d-%m-%Y') as admdate,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,fee_category.feesname as feesnm 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 left join fee_category on student_detail.castcategory = fee_category.id where student_detail.admyears = '" + acdyear + "' and student_detail.classd = '" + clasr + "'  and student_detail.canceladmission = '" + ai + "'", 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 (clasr != "0" && division != "0" && acdyear != "0" && castes == "0" && genders == "")
        {
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,date_format(student_detail.today,'%d-%m-%Y') as admdate,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,fee_category.feesname as feesnm 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 left join fee_category on student_detail.castcategory = fee_category.id where student_detail.admyears = '" + acdyear + "' and student_detail.classd = '" + clasr + "' and student_detail.division = '" + division + "' and student_detail.canceladmission = '" + ai + "'", 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 (clasr != "0" && division != "0" && acdyear != "0" && castes != "0" && genders == "")
        {
            using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,date_format(student_detail.today,'%d-%m-%Y') as admdate,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 = '" + acdyear + "' and student_detail.classd = '" + clasr + "' and student_detail.division = '" + division + "' and student_detail.castcategory = '" + castes + "' and student_detail.canceladmission = '" + ai + "'", con))
                {
                    DataSet ds = new DataSet();
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds);
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }
                        con.Close();
                    }
                }
            }
        }
        else if (clasr != "0" && division != "0" && acdyear != "0" && genders != "" && castes == "0")
        {
            using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,date_format(student_detail.today,'%d-%m-%Y') as admdate,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 = '" + acdyear + "' and student_detail.classd = '" + clasr + "' and student_detail.division = '" + division + "' and student_detail.gender = '" + genders + "' and student_detail.canceladmission = '" + ai + "'", con))
                {
                    DataSet ds = new DataSet();
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds);
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }
                        con.Close();
                    }
                }
            }
        }
        else if (clasr != "0" && division != "0" && acdyear != "0" && genders != "" && castes != "0")
        {
            using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,date_format(student_detail.today,'%d-%m-%Y') as admdate,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 = '" + acdyear + "' and student_detail.classd = '" + clasr + "' and student_detail.division = '" + division + "' and student_detail.gender = '" + genders + "' and student_detail.castcategory = '" + castes + "' and student_detail.canceladmission = '" + ai + "'", con))
                {
                    DataSet ds = new DataSet();
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds);
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }
                        con.Close();
                    }
                }
            }
        }
        else if (clasr != "0" && division != "0" && acdyear != "0" && stdate != "" && enddt != "")
        {
            if (stdate != "")
            {
                date1 = DateTime.Parse(stdate);
            }
            if (enddt != "")
            {
                date2 = DateTime.Parse(enddt);
            }
            using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,date_format(student_detail.today,'%d-%m-%Y') as admdate,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.today between '" + date1.ToString("yyyy/MM/dd") + "' and '" + date2.ToString("yyyy/MM/dd") + "' and  student_detail.admyears = '" + acdyear + "' and student_detail.classd = '" + clasr + "' and student_detail.division = '" + division + "' and student_detail.canceladmission = '" + ai + "'", con))
                {
                    DataSet ds = new DataSet();
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds);
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }
                        con.Close();
                    }
                }
            }
        }
        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> 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> 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 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> PopulateDropDownList6(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> PopulateDropDownListCaste()
    {
        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 string Export(string clasr, string division, string acdyear, string castes, string genders, string stdate, string enddt)
    {
        string _data = "";
        if (clasr != "" && division != "" && acdyear != "" && castes == "0" && genders == "")
        {
            using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,date_format(student_detail.today,'%d-%m-%Y') as admdate,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 = '" + acdyear + "' and student_detail.classd = '" + clasr + "' and student_detail.division = '" + division + "'", con))
                {
                    DataSet ds = new DataSet();
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds);
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            _data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }
                        con.Close();
                    }
                }
            }
        }
        else if (clasr != "" && division != "" && acdyear != "" && castes != "0" && genders == "")
        {
            using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,date_format(student_detail.today,'%d-%m-%Y') as admdate,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 = '" + acdyear + "' and student_detail.classd = '" + clasr + "' and student_detail.division = '" + division + "' and student_detail.castcategory = '"+ castes +"'", con))
                {
                    DataSet ds = new DataSet();
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds);
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            _data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }
                        con.Close();
                    }
                }
            }
        }
        else if (clasr != "" && division != "" && acdyear != ""  && genders != "")
        {
            using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,date_format(student_detail.today,'%d-%m-%Y') as admdate,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 = '" + acdyear + "' and student_detail.classd = '" + clasr + "' and student_detail.division = '" + division + "' and student_detail.gender = '" + genders + "'", con))
                {
                    DataSet ds = new DataSet();
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds);
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            _data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }
                        con.Close();
                    }
                }
            }
        }
        else if (clasr != "" && division != "" && acdyear != "")
        {
            if (stdate != "")
            {
                date1 = DateTime.Parse(stdate);
            }
            if (enddt != "")
            {
                date2 = DateTime.Parse(enddt);
            }
            using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,date_format(student_detail.today,'%d-%m-%Y') as admdate,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.today between '"+ date1.ToString("yyyy/MM/dd") +"' and '"+ date2.ToString("yyyy/MM/dd") +"' and  student_detail.admyears = '" + acdyear + "' and student_detail.classd = '" + clasr + "' and student_detail.division = '" + division + "'", con))
                {
                    DataSet ds = new DataSet();
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds);
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            _data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }
                        con.Close();
                    }
                }
            }
        }
        else if (clasr != "" && division != "" && acdyear != "" && castes != "" && genders != "")
        {
            if (stdate != "")
            {
                date1 = DateTime.Parse(stdate);
            }
            if (enddt != "")
            {
                date2 = DateTime.Parse(enddt);
            }
            using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,date_format(student_detail.today,'%d-%m-%Y') as admdate,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.today between '" + date1.ToString("yyyy/MM/dd") + "' and '" + date2.ToString("yyyy/MM/dd") + "' and  student_detail.admyears = '" + acdyear + "' and student_detail.classd = '" + clasr + "' and student_detail.division = '" + division + "' and student_detail.gender = '" + genders + "'  and student_detail.castcategory = '" + castes + "'", con))
                {
                    DataSet ds = new DataSet();
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds);
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            _data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }
                        con.Close();
                    }
                }
            }
        }
        return _data;
    }
}