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 MySql.Data.MySqlClient;
using System.Configuration;
using System.Web.Services;
using Newtonsoft.Json;
using System.Web.Script.Serialization;
public partial class module_examfeereport : System.Web.UI.Page
{
    public static string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    public static MySqlConnection con = new MySqlConnection(constring);

    protected void Page_Load(object sender, EventArgs e)
    {

    }
    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 studids { get; set; }
        public string titles { get; set; } public string fname { get; set; } public string mname { get; set; } public string lname { get; set; }
    }
    [WebMethod]
    public static List<Department> PopulateDropDownList3()
    {
        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> 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 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> PopulateDropDownList(string clsid)
    {
        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 = '" + clsid + "'", 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> PopulateDropDownList45(string yrsid, string divsid, string clsid)
    {
        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 semistertbl where classid = '" + clsid + "' and divsid = '" + divsid + "'", 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]["semnos"].ToString(),

                            });
                        }
                    }
                }
            }
        }
        return objDept;
    }
    [WebMethod]

    public static object GetData1(string years, string classes, string divisions, string semid,string paymode, string payst)
    {
        string data = "";
        if (semid == "1" || semid == "9" || semid == "17" || semid == "21" || semid == "25")
        {
            if (payst == "paid")
            {
                if (paymode == "")
                {
                    using (MySqlConnection con = new MySqlConnection(constring))
                    {
                        using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,academic_year.id as yearid,examfeemaster.feepackname,examfeemaster.totalamount ,examfeemaster.semisteryears,student_detail.title,student_detail.fname,student_detail.mname,student_detail.lname,exampackageassign.examrecpt,exampackageassign.paymode,exampackageassign.utrnos,DATE_FORMAT(exampackageassign.examdt,'%d-%m-%Y') AS examdate,exampackageassign.exampkgid AS exampkgs,semistertbl.semnos from  student_detail inner join package_detail on student_detail.packageid = package_detail.id  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 exampackageassign ON student_detail.studid = exampackageassign.studids AND student_detail.sem1 = exampackageassign.semisterid left join examfeemaster on exampackageassign.exampkgid =  examfeemaster.id LEFT JOIN semistertbl ON  exampackageassign.semisterid = semistertbl.id where student_detail.admyears = '" + years + "' and  student_detail.classd = '" + classes + "' and student_detail.division= '" + divisions + "' and student_detail.sem1 = '" + semid + "'   and  student_detail.transferstatus ='' AND student_detail.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();

                            }
                        }
                    }
                }
                else
                {
                    using (MySqlConnection con = new MySqlConnection(constring))
                    {
                        using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,academic_year.id as yearid,examfeemaster.feepackname,examfeemaster.totalamount ,examfeemaster.semisteryears,student_detail.title,student_detail.fname,student_detail.mname,student_detail.lname,exampackageassign.examrecpt,exampackageassign.paymode,exampackageassign.utrnos,DATE_FORMAT(exampackageassign.examdt,'%d-%m-%Y') AS examdate,exampackageassign.exampkgid AS exampkgs,semistertbl.semnos from  student_detail inner join package_detail on student_detail.packageid = package_detail.id  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 exampackageassign ON student_detail.studid = exampackageassign.studids AND student_detail.sem1 = exampackageassign.semisterid left join examfeemaster on exampackageassign.exampkgid =  examfeemaster.id LEFT JOIN semistertbl ON  exampackageassign.semisterid = semistertbl.id where student_detail.admyears = '" + years + "' and  student_detail.classd = '" + classes + "' and student_detail.division= '" + divisions + "' and student_detail.sem1 = '" + semid + "' and  exampackageassign.paymode = '" + paymode + "'  and  student_detail.transferstatus ='' AND student_detail.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();

                            }
                        }
                    }
                }
            }
            else if (payst == "unpaid")
            {
                using (MySqlConnection con = new MySqlConnection(constring))
                {
                    using (MySqlCommand cmd = new MySqlCommand("SELECT student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,academic_year.id as yearid FROM student_detail INNER JOIN package_detail ON student_detail.packageid = package_detail.id 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  WHERE  student_detail.admyears = '" + years + "' and  student_detail.classd ='" + classes + "' and student_detail.division='" + divisions + "' and student_detail.sem1 = '' and  student_detail.transferstatus ='' AND student_detail.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();

                        }
                    }
                }
            }
        }
        else if (semid == "2" || semid == "10" || semid == "22" || semid == "26")
        {
            if (payst == "paid")
            {
                if (paymode == "")
                {
                    using (MySqlConnection con = new MySqlConnection(constring))
                    {
                        using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,package_detail.id as pkgsids,academic_year.id as yearid,examfeemaster.feepackname,examfeemaster.totalamount ,examfeemaster.semisteryears,student_detail.title,student_detail.fname,student_detail.mname,student_detail.lname,exampackageassign.examrecpt,exampackageassign.paymode,exampackageassign.utrnos,DATE_FORMAT(exampackageassign.examdt,'%d-%m-%Y') AS examdate,exampackageassign.exampkgid AS exampkgs,semistertbl.semnos from  student_detail inner join package_detail on student_detail.packageid = package_detail.id  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 exampackageassign ON student_detail.studid = exampackageassign.studids AND student_detail.sem2 = exampackageassign.semisterid left join examfeemaster on exampackageassign.exampkgid =  examfeemaster.id LEFT JOIN semistertbl ON  exampackageassign.semisterid = semistertbl.id where student_detail.admyears = '" + years + "' and  student_detail.classd = '" + classes + "' and student_detail.division= '" + divisions + "' and student_detail.sem2 = '" + semid + "'    and  student_detail.transferstatus ='' AND student_detail.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();

                            }
                        }
                    }
                }
                else
                {
                    using (MySqlConnection con = new MySqlConnection(constring))
                    {
                        using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,package_detail.id as pkgsids,academic_year.id as yearid,examfeemaster.feepackname,examfeemaster.totalamount ,examfeemaster.semisteryears,student_detail.title,student_detail.fname,student_detail.mname,student_detail.lname,exampackageassign.examrecpt,exampackageassign.paymode,exampackageassign.utrnos,DATE_FORMAT(exampackageassign.examdt,'%d-%m-%Y') AS examdate,exampackageassign.exampkgid AS exampkgs,semistertbl.semnos from  student_detail inner join package_detail on student_detail.packageid = package_detail.id  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 exampackageassign ON student_detail.studid = exampackageassign.studids AND student_detail.sem2 = exampackageassign.semisterid left join examfeemaster on exampackageassign.exampkgid =  examfeemaster.id LEFT JOIN semistertbl ON  exampackageassign.semisterid = semistertbl.id where student_detail.admyears = '" + years + "' and  student_detail.classd = '" + classes + "' and student_detail.division= '" + divisions + "' and student_detail.sem2 = '" + semid + "' and  exampackageassign.paymode = '" + paymode + "'  and  student_detail.transferstatus ='' AND student_detail.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();

                            }
                        }
                    }
                }
            }
            else if (payst == "unpaid")
            {
                using (MySqlConnection con = new MySqlConnection(constring))
                {
                    using (MySqlCommand cmd = new MySqlCommand("SELECT student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,academic_year.id as yearid FROM student_detail INNER JOIN package_detail ON student_detail.packageid = package_detail.id 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  WHERE  student_detail.admyears = '" + years + "' and  student_detail.classd ='" + classes + "' and student_detail.division='" + divisions + "' and student_detail.sem2 = '' and  student_detail.transferstatus ='' AND student_detail.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();

                        }
                    }
                }
            }
        }
        else if (semid == "3" || semid == "11" || semid == "19" || semid == "23" || semid == "27")
        {
            if (payst == "paid")
            {
                if (paymode == "")
                {
                    using (MySqlConnection con = new MySqlConnection(constring))
                    {
                        using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,package_detail.id as pkgsids,academic_year.id as yearid,examfeemaster.feepackname,examfeemaster.totalamount ,examfeemaster.semisteryears,student_detail.title,student_detail.fname,student_detail.mname,student_detail.lname,exampackageassign.examrecpt,exampackageassign.paymode,exampackageassign.utrnos,DATE_FORMAT(exampackageassign.examdt,'%d-%m-%Y') AS examdate,exampackageassign.exampkgid AS exampkgs,semistertbl.semnos from  student_detail inner join package_detail on student_detail.packageid = package_detail.id  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 exampackageassign ON student_detail.studid = exampackageassign.studids AND student_detail.sem3 = exampackageassign.semisterid left join examfeemaster on exampackageassign.exampkgid =  examfeemaster.id LEFT JOIN semistertbl ON  exampackageassign.semisterid = semistertbl.id where student_detail.admyears = '" + years + "' and  student_detail.classd = '" + classes + "' and student_detail.division= '" + divisions + "' and student_detail.sem3 = '" + semid + "'   and  student_detail.transferstatus ='' AND student_detail.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();

                            }
                        }
                    }
                }
                else
                {
                    using (MySqlConnection con = new MySqlConnection(constring))
                    {
                        using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,package_detail.id as pkgsids,academic_year.id as yearid,examfeemaster.feepackname,examfeemaster.totalamount ,examfeemaster.semisteryears,student_detail.title,student_detail.fname,student_detail.mname,student_detail.lname,exampackageassign.examrecpt,exampackageassign.paymode,exampackageassign.utrnos,DATE_FORMAT(exampackageassign.examdt,'%d-%m-%Y') AS examdate,exampackageassign.exampkgid AS exampkgs,semistertbl.semnos from  student_detail inner join package_detail on student_detail.packageid = package_detail.id  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 exampackageassign ON student_detail.studid = exampackageassign.studids AND student_detail.sem3 = exampackageassign.semisterid left join examfeemaster on exampackageassign.exampkgid =  examfeemaster.id LEFT JOIN semistertbl ON  exampackageassign.semisterid = semistertbl.id where student_detail.admyears = '" + years + "' and  student_detail.classd = '" + classes + "' and student_detail.division= '" + divisions + "' and student_detail.sem3 = '" + semid + "'  and  exampackageassign.paymode = '" + paymode + "' and  student_detail.transferstatus ='' AND student_detail.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();

                            }
                        }
                    }
                }
            }
            else if (payst == "unpaid")
            {
                using (MySqlConnection con = new MySqlConnection(constring))
                {
                    using (MySqlCommand cmd = new MySqlCommand("SELECT student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,academic_year.id as yearid FROM student_detail INNER JOIN package_detail ON student_detail.packageid = package_detail.id 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  WHERE  student_detail.admyears = '" + years + "' and  student_detail.classd ='" + classes + "' and student_detail.division='" + divisions + "' and student_detail.sem3 = '' and  student_detail.transferstatus ='' AND student_detail.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();

                        }
                    }
                }
            }

        }
        else if (semid == "4" || semid == "12" || semid == "20" || semid == "24" || semid == "28")
        {
            if (payst == "paid")
            {
                if (paymode == "")
                {
                    using (MySqlConnection con = new MySqlConnection(constring))
                    {
                        using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,package_detail.id as pkgsids,academic_year.id as yearid,examfeemaster.feepackname,examfeemaster.totalamount ,examfeemaster.semisteryears,student_detail.title,student_detail.fname,student_detail.mname,student_detail.lname,exampackageassign.examrecpt,exampackageassign.paymode,exampackageassign.utrnos,DATE_FORMAT(exampackageassign.examdt,'%d-%m-%Y') AS examdate,exampackageassign.exampkgid AS exampkgs,semistertbl.semnos from  student_detail inner join package_detail on student_detail.packageid = package_detail.id  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 exampackageassign ON student_detail.studid = exampackageassign.studids AND student_detail.sem4 = exampackageassign.semisterid left join examfeemaster on exampackageassign.exampkgid =  examfeemaster.id LEFT JOIN semistertbl ON  exampackageassign.semisterid = semistertbl.id where student_detail.admyears = '" + years + "' and  student_detail.classd = '" + classes + "' and student_detail.division= '" + divisions + "' and student_detail.sem4 = '" + semid + "'   and  student_detail.transferstatus ='' AND student_detail.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();

                            }
                        }
                    }
                }
                else
                {
                    using (MySqlConnection con = new MySqlConnection(constring))
                    {
                        using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,package_detail.id as pkgsids,academic_year.id as yearid,examfeemaster.feepackname,examfeemaster.totalamount ,examfeemaster.semisteryears,student_detail.title,student_detail.fname,student_detail.mname,student_detail.lname,exampackageassign.examrecpt,exampackageassign.paymode,exampackageassign.utrnos,DATE_FORMAT(exampackageassign.examdt,'%d-%m-%Y') AS examdate,exampackageassign.exampkgid AS exampkgs,semistertbl.semnos from  student_detail inner join package_detail on student_detail.packageid = package_detail.id  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 exampackageassign ON student_detail.studid = exampackageassign.studids AND student_detail.sem4 = exampackageassign.semisterid left join examfeemaster on exampackageassign.exampkgid =  examfeemaster.id LEFT JOIN semistertbl ON  exampackageassign.semisterid = semistertbl.id where student_detail.admyears = '" + years + "' and  student_detail.classd = '" + classes + "' and student_detail.division= '" + divisions + "' and student_detail.sem4 = '" + semid + "' and  exampackageassign.paymode = '" + paymode + "'  and  student_detail.transferstatus ='' AND student_detail.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();

                            }
                        }
                    }
                }
            }
            else if (payst == "unpaid")
            {
                using (MySqlConnection con = new MySqlConnection(constring))
                {
                    using (MySqlCommand cmd = new MySqlCommand("SELECT student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,academic_year.id as yearid FROM student_detail INNER JOIN package_detail ON student_detail.packageid = package_detail.id 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  WHERE  student_detail.admyears = '" + years + "' and  student_detail.classd ='" + classes + "' and student_detail.division='" + divisions + "' and student_detail.sem4 = '' and  student_detail.transferstatus ='' AND student_detail.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();

                        }
                    }
                }
            }

        }
        else if (semid == "5")
        {
            if (payst == "paid")
            {
                if (paymode == "")
                {
                    using (MySqlConnection con = new MySqlConnection(constring))
                    {
                        using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,package_detail.id as pkgsids,academic_year.id as yearid,examfeemaster.feepackname,examfeemaster.totalamount ,examfeemaster.semisteryears,student_detail.title,student_detail.fname,student_detail.mname,student_detail.lname,exampackageassign.examrecpt,exampackageassign.paymode,exampackageassign.utrnos,DATE_FORMAT(exampackageassign.examdt,'%d-%m-%Y') AS examdate,exampackageassign.exampkgid AS exampkgs,semistertbl.semnos from  student_detail inner join package_detail on student_detail.packageid = package_detail.id  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 exampackageassign ON student_detail.studid = exampackageassign.studids AND student_detail.sem5 = exampackageassign.semisterid left join examfeemaster on exampackageassign.exampkgid =  examfeemaster.id LEFT JOIN semistertbl ON  exampackageassign.semisterid = semistertbl.id where student_detail.admyears = '" + years + "' and  student_detail.classd = '" + classes + "' and student_detail.division= '" + divisions + "' and student_detail.sem5 = '" + semid + "'   and  student_detail.transferstatus ='' AND student_detail.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();

                            }
                        }
                    }
                }
                else
                {
                    using (MySqlConnection con = new MySqlConnection(constring))
                    {
                        using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,package_detail.id as pkgsids,academic_year.id as yearid,examfeemaster.feepackname,examfeemaster.totalamount ,examfeemaster.semisteryears,student_detail.title,student_detail.fname,student_detail.mname,student_detail.lname,exampackageassign.examrecpt,exampackageassign.paymode,exampackageassign.utrnos,DATE_FORMAT(exampackageassign.examdt,'%d-%m-%Y') AS examdate,exampackageassign.exampkgid AS exampkgs,semistertbl.semnos from  student_detail inner join package_detail on student_detail.packageid = package_detail.id  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 exampackageassign ON student_detail.studid = exampackageassign.studids AND student_detail.sem5 = exampackageassign.semisterid left join examfeemaster on exampackageassign.exampkgid =  examfeemaster.id LEFT JOIN semistertbl ON  exampackageassign.semisterid = semistertbl.id where student_detail.admyears = '" + years + "' and  student_detail.classd = '" + classes + "' and student_detail.division= '" + divisions + "' and student_detail.sem5 = '" + semid + "' and  exampackageassign.paymode = '" + paymode + "'  and  student_detail.transferstatus ='' AND student_detail.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();

                            }
                        }
                    }
                }
            }
            else if (payst == "unpaid")
            {
                using (MySqlConnection con = new MySqlConnection(constring))
                {
                    using (MySqlCommand cmd = new MySqlCommand("SELECT student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,academic_year.id as yearid FROM student_detail INNER JOIN package_detail ON student_detail.packageid = package_detail.id 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  WHERE  student_detail.admyears = '" + years + "' and  student_detail.classd ='" + classes + "' and student_detail.division='" + divisions + "' and student_detail.sem5 = '' and  student_detail.transferstatus ='' AND student_detail.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();

                        }
                    }
                }
            }

        }
        else if (semid == "6")
        {
            if (payst == "paid")
            {
                if (paymode == "")
                {
                    using (MySqlConnection con = new MySqlConnection(constring))
                    {
                        using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,package_detail.id as pkgsids,academic_year.id as yearid,examfeemaster.feepackname,examfeemaster.totalamount ,examfeemaster.semisteryears,student_detail.title,student_detail.fname,student_detail.mname,student_detail.lname,exampackageassign.examrecpt,exampackageassign.paymode,exampackageassign.utrnos,DATE_FORMAT(exampackageassign.examdt,'%d-%m-%Y') AS examdate,exampackageassign.exampkgid AS exampkgs,semistertbl.semnos from  student_detail inner join package_detail on student_detail.packageid = package_detail.id  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 exampackageassign ON student_detail.studid = exampackageassign.studids AND student_detail.sem6 = exampackageassign.semisterid left join examfeemaster on exampackageassign.exampkgid =  examfeemaster.id LEFT JOIN semistertbl ON  exampackageassign.semisterid = semistertbl.id where student_detail.admyears = '" + years + "' and  student_detail.classd = '" + classes + "' and student_detail.division= '" + divisions + "' and student_detail.sem6 = '" + semid + "'   and  student_detail.transferstatus ='' AND student_detail.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();

                            }
                        }
                    }
                }
                else
                {
                    using (MySqlConnection con = new MySqlConnection(constring))
                    {
                        using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,package_detail.id as pkgsids,academic_year.id as yearid,examfeemaster.feepackname,examfeemaster.totalamount ,examfeemaster.semisteryears,student_detail.title,student_detail.fname,student_detail.mname,student_detail.lname,exampackageassign.examrecpt,exampackageassign.paymode,exampackageassign.utrnos,DATE_FORMAT(exampackageassign.examdt,'%d-%m-%Y') AS examdate,exampackageassign.exampkgid AS exampkgs,semistertbl.semnos from  student_detail inner join package_detail on student_detail.packageid = package_detail.id  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 exampackageassign ON student_detail.studid = exampackageassign.studids AND student_detail.sem6 = exampackageassign.semisterid left join examfeemaster on exampackageassign.exampkgid =  examfeemaster.id LEFT JOIN semistertbl ON  exampackageassign.semisterid = semistertbl.id where student_detail.admyears = '" + years + "' and  student_detail.classd = '" + classes + "' and student_detail.division= '" + divisions + "' and student_detail.sem6 = '" + semid + "' and  exampackageassign.paymode = '" + paymode + "'   and  student_detail.transferstatus ='' AND student_detail.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();

                            }
                        }
                    }
                }
            }
            else if (payst == "unpaid")
            {
                using (MySqlConnection con = new MySqlConnection(constring))
                {
                    using (MySqlCommand cmd = new MySqlCommand("SELECT student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,academic_year.id as yearid FROM student_detail INNER JOIN package_detail ON student_detail.packageid = package_detail.id 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  WHERE  student_detail.admyears = '" + years + "' and  student_detail.classd ='" + classes + "' and student_detail.division='" + divisions + "' and student_detail.sem6 = '' and  student_detail.transferstatus ='' AND student_detail.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();

                        }
                    }
                }
            }

        }
        else if (semid == "7")
        {
            if (payst == "paid")
            {
                if (paymode == "")
                {
                    using (MySqlConnection con = new MySqlConnection(constring))
                    {
                        using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,package_detail.id as pkgsids,academic_year.id as yearid,examfeemaster.feepackname,examfeemaster.totalamount ,examfeemaster.semisteryears,student_detail.title,student_detail.fname,student_detail.mname,student_detail.lname,exampackageassign.examrecpt,exampackageassign.paymode,exampackageassign.utrnos,DATE_FORMAT(exampackageassign.examdt,'%d-%m-%Y') AS examdate,exampackageassign.exampkgid AS exampkgs,semistertbl.semnos from  student_detail inner join package_detail on student_detail.packageid = package_detail.id  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 exampackageassign ON student_detail.studid = exampackageassign.studids AND student_detail.sem7 = exampackageassign.semisterid left join examfeemaster on exampackageassign.exampkgid =  examfeemaster.id LEFT JOIN semistertbl ON  exampackageassign.semisterid = semistertbl.id where student_detail.admyears = '" + years + "' and  student_detail.classd = '" + classes + "' and student_detail.division= '" + divisions + "' and student_detail.sem7 = '" + semid + "'   and  student_detail.transferstatus ='' AND student_detail.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();

                            }
                        }
                    }
                }
                else
                {
                    using (MySqlConnection con = new MySqlConnection(constring))
                    {
                        using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,package_detail.id as pkgsids,academic_year.id as yearid,examfeemaster.feepackname,examfeemaster.totalamount ,examfeemaster.semisteryears,student_detail.title,student_detail.fname,student_detail.mname,student_detail.lname,exampackageassign.examrecpt,exampackageassign.paymode,exampackageassign.utrnos,DATE_FORMAT(exampackageassign.examdt,'%d-%m-%Y') AS examdate,exampackageassign.exampkgid AS exampkgs,semistertbl.semnos from  student_detail inner join package_detail on student_detail.packageid = package_detail.id  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 exampackageassign ON student_detail.studid = exampackageassign.studids AND student_detail.sem7 = exampackageassign.semisterid left join examfeemaster on exampackageassign.exampkgid =  examfeemaster.id LEFT JOIN semistertbl ON  exampackageassign.semisterid = semistertbl.id where student_detail.admyears = '" + years + "' and  student_detail.classd = '" + classes + "' and student_detail.division= '" + divisions + "' and student_detail.sem7 = '" + semid + "' and  exampackageassign.paymode = '" + paymode + "'  and  student_detail.transferstatus ='' AND student_detail.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();

                            }
                        }
                    }
                }
            }
            else if (payst == "unpaid")
            {
                using (MySqlConnection con = new MySqlConnection(constring))
                {
                    using (MySqlCommand cmd = new MySqlCommand("SELECT student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,academic_year.id as yearid FROM student_detail INNER JOIN package_detail ON student_detail.packageid = package_detail.id 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  WHERE  student_detail.admyears = '" + years + "' and  student_detail.classd ='" + classes + "' and student_detail.division='" + divisions + "' and student_detail.sem7 = '' and  student_detail.transferstatus ='' AND student_detail.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();

                        }
                    }
                }
            }

        }
        else if (semid == "8")
        {
            if (payst == "paid")
            {
                if (paymode == "")
                {
                    using (MySqlConnection con = new MySqlConnection(constring))
                    {
                        using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,package_detail.id as pkgsids,academic_year.id as yearid,examfeemaster.feepackname,examfeemaster.totalamount ,examfeemaster.semisteryears,student_detail.title,student_detail.fname,student_detail.mname,student_detail.lname,exampackageassign.examrecpt,exampackageassign.paymode,exampackageassign.utrnos,DATE_FORMAT(exampackageassign.examdt,'%d-%m-%Y') AS examdate,exampackageassign.exampkgid AS exampkgs,semistertbl.semnos from  student_detail inner join package_detail on student_detail.packageid = package_detail.id  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 exampackageassign ON student_detail.studid = exampackageassign.studids AND student_detail.sem8 = exampackageassign.semisterid left join examfeemaster on exampackageassign.exampkgid =  examfeemaster.id LEFT JOIN semistertbl ON  exampackageassign.semisterid = semistertbl.id where student_detail.admyears = '" + years + "' and  student_detail.classd = '" + classes + "' and student_detail.division= '" + divisions + "' and student_detail.sem8 = '" + semid + "'   and  student_detail.transferstatus ='' AND student_detail.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();

                            }
                        }
                    }
                }
                else
                {
                    using (MySqlConnection con = new MySqlConnection(constring))
                    {
                        using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,package_detail.id as pkgsids,academic_year.id as yearid,examfeemaster.feepackname,examfeemaster.totalamount ,examfeemaster.semisteryears,student_detail.title,student_detail.fname,student_detail.mname,student_detail.lname,exampackageassign.examrecpt,exampackageassign.paymode,exampackageassign.utrnos,DATE_FORMAT(exampackageassign.examdt,'%d-%m-%Y') AS examdate,exampackageassign.exampkgid AS exampkgs,semistertbl.semnos from  student_detail inner join package_detail on student_detail.packageid = package_detail.id  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 exampackageassign ON student_detail.studid = exampackageassign.studids AND student_detail.sem8 = exampackageassign.semisterid left join examfeemaster on exampackageassign.exampkgid =  examfeemaster.id LEFT JOIN semistertbl ON  exampackageassign.semisterid = semistertbl.id where student_detail.admyears = '" + years + "' and  student_detail.classd = '" + classes + "' and student_detail.division= '" + divisions + "' and student_detail.sem8 = '" + semid + "' and  exampackageassign.paymode = '" + paymode + "'  and  student_detail.transferstatus ='' AND student_detail.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();

                            }
                        }
                    }
                }
            }
            else if (payst == "unpaid")
            {
                using (MySqlConnection con = new MySqlConnection(constring))
                {
                    using (MySqlCommand cmd = new MySqlCommand("SELECT student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,academic_year.id as yearid FROM student_detail INNER JOIN package_detail ON student_detail.packageid = package_detail.id 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  WHERE  student_detail.admyears = '" + years + "' and  student_detail.classd ='" + classes + "' and student_detail.division='" + divisions + "' and student_detail.sem8 = '' and  student_detail.transferstatus ='' AND student_detail.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();

                        }
                    }
                }
            }

        }
        return data;
    }
}