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 System.Web.Script;
using System.Web.Script.Services;
using Newtonsoft.Json;
using System.Web.Script.Serialization;
public partial class module_feeheadwisereport : 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 string fullname;
    public static DateTime date2;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    public class Department
    {
        public int DeptId { get; set; }
        public string DeptName { get; set; }
        public string studids { get; set; }
        public string DeptIds { get; set; }
        public string Initial { get; set; }
        public string year1 { get; set; }
    }
    [WebMethod]
    public static List<Department> PopulateDropDownList4()
    {
        DataTable dtnew = new DataTable();
        DataTable dtnew1 = new DataTable();
        List<Department> objDept = new List<Department>();

        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        {
            using (MySqlCommand cmdnew = new MySqlCommand("SELECT *,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> PopulateDropDownList15()
    {
        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 object GetData(string pdate, string pdate1, string incomeid, string yrsid, string divid, string clsid, string paymodes)
    {
        string data = "";
        if (pdate != "")
        {
            date1 = DateTime.Parse(pdate);
        }
        if (pdate1 != "")
        {
            date2 = DateTime.Parse(pdate1);
        }
        if (incomeid == "tuitionfee")
        {
           
                using (MySqlConnection con = new MySqlConnection(constring))
                {
                    using (MySqlCommand cmd = new MySqlCommand("SELECT (select sum(installmentstudent.baltutionfees) FROM installmentstudent AS ins WHERE installmentstudent.studid = ins.studid GROUP BY ins.studid) AS totaltution ,installmentstudent.studid,installmentstudent.yearsids,installmentstudent.classids,installmentstudent.divisionsids,ac1.fromdate,ac1.todate,cls.class,divs.division,DATE_FORMAT(installmentstudent.paydate,'%d-%m-%Y') AS paymentdt,installmentstudent.paymode,installmentstudent.chequenos,sts.title,sts.fname,sts.mname,sts.lname,sts.studid FROM  installmentstudent LEFT JOIN academic_year AS ac1 ON installmentstudent.yearsids = ac1.id LEFT JOIN division_master AS divs ON installmentstudent.divisionsids = divs.id LEFT JOIN class_master AS cls ON installmentstudent.classids = cls.id LEFT JOIN student_detail AS sts ON installmentstudent.studid = sts.studid  WHERE installmentstudent.paydate BETWEEN '" + date1.ToString("yyyy/MM/dd") + "' AND '" + date2.ToString("yyyy/MM/dd") + "' AND installmentstudent.yearsids = '" + yrsid + "' AND installmentstudent.classids = '" + clsid + "' AND installmentstudent.divisionsids = '" + divid + "' and installmentstudent.paymode = '" + paymodes + "' and  installmentstudent.baltutionfees != '0'  GROUP BY installmentstudent.studid", 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 (incomeid == "admissionfee")
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT (select sum(installmentstudent.baladmfees) FROM installmentstudent AS ins WHERE installmentstudent.studid = ins.studid GROUP BY ins.studid) AS totaltution ,installmentstudent.studid,installmentstudent.yearsids,installmentstudent.classids,installmentstudent.divisionsids,ac1.fromdate,ac1.todate,cls.class,divs.division,DATE_FORMAT(installmentstudent.paydate,'%d-%m-%Y') AS paymentdt,installmentstudent.paymode,installmentstudent.chequenos,sts.title,sts.fname,sts.mname,sts.lname,sts.studid FROM  installmentstudent LEFT JOIN academic_year AS ac1 ON installmentstudent.yearsids = ac1.id LEFT JOIN division_master AS divs ON installmentstudent.divisionsids = divs.id LEFT JOIN class_master AS cls ON installmentstudent.classids = cls.id LEFT JOIN student_detail AS sts ON installmentstudent.studid = sts.studid  WHERE installmentstudent.paydate BETWEEN '" + date1.ToString("yyyy/MM/dd") + "' AND '" + date2.ToString("yyyy/MM/dd") + "' AND installmentstudent.yearsids = '" + yrsid + "' AND installmentstudent.classids = '" + clsid + "' AND installmentstudent.divisionsids = '" + divid + "' and installmentstudent.paymode = '" + paymodes + "' and  installmentstudent.baladmfees != '0'  GROUP BY installmentstudent.studid", 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 (incomeid == "cautionmny")
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT (select sum(installmentstudent.balcautinmon) FROM installmentstudent AS ins WHERE installmentstudent.studid = ins.studid GROUP BY ins.studid) AS totaltution ,installmentstudent.studid,installmentstudent.yearsids,installmentstudent.classids,installmentstudent.divisionsids,ac1.fromdate,ac1.todate,cls.class,divs.division,DATE_FORMAT(installmentstudent.paydate,'%d-%m-%Y') AS paymentdt,installmentstudent.paymode,installmentstudent.chequenos,sts.title,sts.fname,sts.mname,sts.lname,sts.studid FROM  installmentstudent LEFT JOIN academic_year AS ac1 ON installmentstudent.yearsids = ac1.id LEFT JOIN division_master AS divs ON installmentstudent.divisionsids = divs.id LEFT JOIN class_master AS cls ON installmentstudent.classids = cls.id LEFT JOIN student_detail AS sts ON installmentstudent.studid = sts.studid  WHERE installmentstudent.paydate BETWEEN '" + date1.ToString("yyyy/MM/dd") + "' AND '" + date2.ToString("yyyy/MM/dd") + "' AND installmentstudent.yearsids = '" + yrsid + "' AND installmentstudent.classids = '" + clsid + "' AND installmentstudent.divisionsids = '" + divid + "' and installmentstudent.paymode = '" + paymodes + "' and installmentstudent.balcautinmon != '0'  GROUP BY installmentstudent.studid", 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 (incomeid == "collegefee")
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT (select sum(installmentstudent.balcollegefess) FROM installmentstudent AS ins WHERE installmentstudent.studid = ins.studid GROUP BY ins.studid) AS totaltution ,installmentstudent.studid,installmentstudent.yearsids,installmentstudent.classids,installmentstudent.divisionsids,ac1.fromdate,ac1.todate,cls.class,divs.division,DATE_FORMAT(installmentstudent.paydate,'%d-%m-%Y') AS paymentdt,installmentstudent.paymode,installmentstudent.chequenos,sts.title,sts.fname,sts.mname,sts.lname,sts.studid FROM  installmentstudent LEFT JOIN academic_year AS ac1 ON installmentstudent.yearsids = ac1.id LEFT JOIN division_master AS divs ON installmentstudent.divisionsids = divs.id LEFT JOIN class_master AS cls ON installmentstudent.classids = cls.id LEFT JOIN student_detail AS sts ON installmentstudent.studid = sts.studid  WHERE installmentstudent.paydate BETWEEN '" + date1.ToString("yyyy/MM/dd") + "' AND '" + date2.ToString("yyyy/MM/dd") + "' AND installmentstudent.yearsids = '" + yrsid + "' AND installmentstudent.classids = '" + clsid + "' AND installmentstudent.divisionsids = '" + divid + "' and installmentstudent.paymode = '" + paymodes + "' and installmentstudent.balcollegefess != '0'  GROUP BY installmentstudent.studid", 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 (incomeid == "identycrdfee")
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT (select sum(installmentstudent.balidentitys) FROM installmentstudent AS ins WHERE installmentstudent.studid = ins.studid GROUP BY ins.studid) AS totaltution ,installmentstudent.studid,installmentstudent.yearsids,installmentstudent.classids,installmentstudent.divisionsids,ac1.fromdate,ac1.todate,cls.class,divs.division,DATE_FORMAT(installmentstudent.paydate,'%d-%m-%Y') AS paymentdt,installmentstudent.paymode,installmentstudent.chequenos,sts.title,sts.fname,sts.mname,sts.lname,sts.studid FROM  installmentstudent LEFT JOIN academic_year AS ac1 ON installmentstudent.yearsids = ac1.id LEFT JOIN division_master AS divs ON installmentstudent.divisionsids = divs.id LEFT JOIN class_master AS cls ON installmentstudent.classids = cls.id LEFT JOIN student_detail AS sts ON installmentstudent.studid = sts.studid  WHERE installmentstudent.paydate BETWEEN '" + date1.ToString("yyyy/MM/dd") + "' AND '" + date2.ToString("yyyy/MM/dd") + "' AND installmentstudent.yearsids = '" + yrsid + "' AND installmentstudent.classids = '" + clsid + "' AND installmentstudent.divisionsids = '" + divid + "' and installmentstudent.paymode = '" + paymodes + "' and installmentstudent.balidentitys != '0'  GROUP BY installmentstudent.studid", 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 (incomeid == "insurance")
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT (select sum(installmentstudent.balinsurance) FROM installmentstudent AS ins WHERE installmentstudent.studid = ins.studid GROUP BY ins.studid) AS totaltution ,installmentstudent.studid,installmentstudent.yearsids,installmentstudent.classids,installmentstudent.divisionsids,ac1.fromdate,ac1.todate,cls.class,divs.division,DATE_FORMAT(installmentstudent.paydate,'%d-%m-%Y') AS paymentdt,installmentstudent.paymode,installmentstudent.chequenos,sts.title,sts.fname,sts.mname,sts.lname,sts.studid FROM  installmentstudent LEFT JOIN academic_year AS ac1 ON installmentstudent.yearsids = ac1.id LEFT JOIN division_master AS divs ON installmentstudent.divisionsids = divs.id LEFT JOIN class_master AS cls ON installmentstudent.classids = cls.id LEFT JOIN student_detail AS sts ON installmentstudent.studid = sts.studid  WHERE installmentstudent.paydate BETWEEN '" + date1.ToString("yyyy/MM/dd") + "' AND '" + date2.ToString("yyyy/MM/dd") + "' AND installmentstudent.yearsids = '" + yrsid + "' AND installmentstudent.classids = '" + clsid + "' AND installmentstudent.divisionsids = '" + divid + "' and installmentstudent.paymode = '" + paymodes + "' and installmentstudent.balinsurance != '0'  GROUP BY installmentstudent.studid", 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 (incomeid == "unifees")
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT (select sum(installmentstudent.universesfees) FROM installmentstudent AS ins WHERE installmentstudent.studid = ins.studid GROUP BY ins.studid) AS totaltution ,installmentstudent.studid,installmentstudent.yearsids,installmentstudent.classids,installmentstudent.divisionsids,ac1.fromdate,ac1.todate,cls.class,divs.division,DATE_FORMAT(installmentstudent.paydate,'%d-%m-%Y') AS paymentdt,installmentstudent.paymode,installmentstudent.chequenos,sts.title,sts.fname,sts.mname,sts.lname,sts.studid FROM  installmentstudent LEFT JOIN academic_year AS ac1 ON installmentstudent.yearsids = ac1.id LEFT JOIN division_master AS divs ON installmentstudent.divisionsids = divs.id LEFT JOIN class_master AS cls ON installmentstudent.classids = cls.id LEFT JOIN student_detail AS sts ON installmentstudent.studid = sts.studid  WHERE installmentstudent.paydate BETWEEN '" + date1.ToString("yyyy/MM/dd") + "' AND '" + date2.ToString("yyyy/MM/dd") + "' AND installmentstudent.yearsids = '" + yrsid + "' AND installmentstudent.classids = '" + clsid + "' AND installmentstudent.divisionsids = '" + divid + "' and installmentstudent.paymode = '" + paymodes + "' and installmentstudent.universesfees != '0'  GROUP BY installmentstudent.studid", 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 (incomeid == "busfee")
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT (select sum(installmentstudent.balbusfees) FROM installmentstudent AS ins WHERE installmentstudent.studid = ins.studid GROUP BY ins.studid) AS totaltution ,installmentstudent.studid,installmentstudent.yearsids,installmentstudent.classids,installmentstudent.divisionsids,ac1.fromdate,ac1.todate,cls.class,divs.division,DATE_FORMAT(installmentstudent.paydate,'%d-%m-%Y') AS paymentdt,installmentstudent.paymode,installmentstudent.chequenos,sts.title,sts.fname,sts.mname,sts.lname,sts.studid FROM  installmentstudent LEFT JOIN academic_year AS ac1 ON installmentstudent.yearsids = ac1.id LEFT JOIN division_master AS divs ON installmentstudent.divisionsids = divs.id LEFT JOIN class_master AS cls ON installmentstudent.classids = cls.id LEFT JOIN student_detail AS sts ON installmentstudent.studid = sts.studid  WHERE installmentstudent.paydate BETWEEN '" + date1.ToString("yyyy/MM/dd") + "' AND '" + date2.ToString("yyyy/MM/dd") + "' AND installmentstudent.yearsids = '" + yrsid + "' AND installmentstudent.classids = '" + clsid + "' AND installmentstudent.divisionsids = '" + divid + "' and installmentstudent.paymode = '" + paymodes + "' and installmentstudent.balbusfees != '0'  GROUP BY installmentstudent.studid", 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 (incomeid == "eligibilityfee")
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT (select sum(installmentstudent.baleligible) FROM installmentstudent AS ins WHERE installmentstudent.studid = ins.studid GROUP BY ins.studid) AS totaltution ,installmentstudent.studid,installmentstudent.yearsids,installmentstudent.classids,installmentstudent.divisionsids,ac1.fromdate,ac1.todate,cls.class,divs.division,DATE_FORMAT(installmentstudent.paydate,'%d-%m-%Y') AS paymentdt,installmentstudent.paymode,installmentstudent.chequenos,sts.title,sts.fname,sts.mname,sts.lname,sts.studid FROM  installmentstudent LEFT JOIN academic_year AS ac1 ON installmentstudent.yearsids = ac1.id LEFT JOIN division_master AS divs ON installmentstudent.divisionsids = divs.id LEFT JOIN class_master AS cls ON installmentstudent.classids = cls.id LEFT JOIN student_detail AS sts ON installmentstudent.studid = sts.studid  WHERE installmentstudent.paydate BETWEEN '" + date1.ToString("yyyy/MM/dd") + "' AND '" + date2.ToString("yyyy/MM/dd") + "' AND installmentstudent.yearsids = '" + yrsid + "' AND installmentstudent.classids = '" + clsid + "' AND installmentstudent.divisionsids = '" + divid + "' and installmentstudent.paymode = '" + paymodes + "' and installmentstudent.baleligible != '0'  GROUP BY installmentstudent.studid", 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 (incomeid == "exminfee")
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT (select sum(installmentstudent.balexamfess) FROM installmentstudent AS ins WHERE installmentstudent.studid = ins.studid GROUP BY ins.studid) AS totaltution ,installmentstudent.studid,installmentstudent.yearsids,installmentstudent.classids,installmentstudent.divisionsids,ac1.fromdate,ac1.todate,cls.class,divs.division,DATE_FORMAT(installmentstudent.paydate,'%d-%m-%Y') AS paymentdt,installmentstudent.paymode,installmentstudent.chequenos,sts.title,sts.fname,sts.mname,sts.lname,sts.studid FROM  installmentstudent LEFT JOIN academic_year AS ac1 ON installmentstudent.yearsids = ac1.id LEFT JOIN division_master AS divs ON installmentstudent.divisionsids = divs.id LEFT JOIN class_master AS cls ON installmentstudent.classids = cls.id LEFT JOIN student_detail AS sts ON installmentstudent.studid = sts.studid  WHERE installmentstudent.paydate BETWEEN '" + date1.ToString("yyyy/MM/dd") + "' AND '" + date2.ToString("yyyy/MM/dd") + "' AND installmentstudent.yearsids = '" + yrsid + "' AND installmentstudent.classids = '" + clsid + "' AND installmentstudent.divisionsids = '" + divid + "' and installmentstudent.paymode = '" + paymodes + "' and installmentstudent.balexamfess != '0'  GROUP BY installmentstudent.studid", 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 (incomeid == "enrollfee")
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT (select sum(installmentstudent.balenrollment) FROM installmentstudent AS ins WHERE installmentstudent.studid = ins.studid GROUP BY ins.studid) AS totaltution ,installmentstudent.studid,installmentstudent.yearsids,installmentstudent.classids,installmentstudent.divisionsids,ac1.fromdate,ac1.todate,cls.class,divs.division,DATE_FORMAT(installmentstudent.paydate,'%d-%m-%Y') AS paymentdt,installmentstudent.paymode,installmentstudent.chequenos,sts.title,sts.fname,sts.mname,sts.lname,sts.studid FROM  installmentstudent LEFT JOIN academic_year AS ac1 ON installmentstudent.yearsids = ac1.id LEFT JOIN division_master AS divs ON installmentstudent.divisionsids = divs.id LEFT JOIN class_master AS cls ON installmentstudent.classids = cls.id LEFT JOIN student_detail AS sts ON installmentstudent.studid = sts.studid  WHERE installmentstudent.paydate BETWEEN '" + date1.ToString("yyyy/MM/dd") + "' AND '" + date2.ToString("yyyy/MM/dd") + "' AND installmentstudent.yearsids = '" + yrsid + "' AND installmentstudent.classids = '" + clsid + "' AND installmentstudent.divisionsids = '" + divid + "' and installmentstudent.paymode = '" + paymodes + "' and installmentstudent.balenrollment != '0'  GROUP BY installmentstudent.studid", 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 (incomeid == "lockerfee")
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT (select sum(installmentstudent.lockerfees) FROM installmentstudent AS ins WHERE installmentstudent.studid = ins.studid GROUP BY ins.studid) AS totaltution ,installmentstudent.studid,installmentstudent.yearsids,installmentstudent.classids,installmentstudent.divisionsids,ac1.fromdate,ac1.todate,cls.class,divs.division,DATE_FORMAT(installmentstudent.paydate,'%d-%m-%Y') AS paymentdt,installmentstudent.paymode,installmentstudent.chequenos,sts.title,sts.fname,sts.mname,sts.lname,sts.studid FROM  installmentstudent LEFT JOIN academic_year AS ac1 ON installmentstudent.yearsids = ac1.id LEFT JOIN division_master AS divs ON installmentstudent.divisionsids = divs.id LEFT JOIN class_master AS cls ON installmentstudent.classids = cls.id LEFT JOIN student_detail AS sts ON installmentstudent.studid = sts.studid  WHERE installmentstudent.paydate BETWEEN '" + date1.ToString("yyyy/MM/dd") + "' AND '" + date2.ToString("yyyy/MM/dd") + "' AND installmentstudent.yearsids = '" + yrsid + "' AND installmentstudent.classids = '" + clsid + "' AND installmentstudent.divisionsids = '" + divid + "' and installmentstudent.paymode = '" + paymodes + "' and installmentstudent.lockerfees != '0'  GROUP BY installmentstudent.studid", 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 (incomeid == "gamespofee")
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT (select sum(installmentstudent.balgamesfees) FROM installmentstudent AS ins WHERE installmentstudent.studid = ins.studid GROUP BY ins.studid) AS totaltution ,installmentstudent.studid,installmentstudent.yearsids,installmentstudent.classids,installmentstudent.divisionsids,ac1.fromdate,ac1.todate,cls.class,divs.division,DATE_FORMAT(installmentstudent.paydate,'%d-%m-%Y') AS paymentdt,installmentstudent.paymode,installmentstudent.chequenos,sts.title,sts.fname,sts.mname,sts.lname,sts.studid FROM  installmentstudent LEFT JOIN academic_year AS ac1 ON installmentstudent.yearsids = ac1.id LEFT JOIN division_master AS divs ON installmentstudent.divisionsids = divs.id LEFT JOIN class_master AS cls ON installmentstudent.classids = cls.id LEFT JOIN student_detail AS sts ON installmentstudent.studid = sts.studid  WHERE installmentstudent.paydate BETWEEN '" + date1.ToString("yyyy/MM/dd") + "' AND '" + date2.ToString("yyyy/MM/dd") + "' AND installmentstudent.yearsids = '" + yrsid + "' AND installmentstudent.classids = '" + clsid + "' AND installmentstudent.divisionsids = '" + divid + "' and installmentstudent.paymode = '" + paymodes + "' and installmentstudent.balgamesfees != '0'  GROUP BY installmentstudent.studid", 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 (incomeid == "boardfee")
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT (select sum(installmentstudent.balboardfees) FROM installmentstudent AS ins WHERE installmentstudent.studid = ins.studid GROUP BY ins.studid) AS totaltution ,installmentstudent.studid,installmentstudent.yearsids,installmentstudent.classids,installmentstudent.divisionsids,ac1.fromdate,ac1.todate,cls.class,divs.division,DATE_FORMAT(installmentstudent.paydate,'%d-%m-%Y') AS paymentdt,installmentstudent.paymode,installmentstudent.chequenos,sts.title,sts.fname,sts.mname,sts.lname,sts.studid FROM  installmentstudent LEFT JOIN academic_year AS ac1 ON installmentstudent.yearsids = ac1.id LEFT JOIN division_master AS divs ON installmentstudent.divisionsids = divs.id LEFT JOIN class_master AS cls ON installmentstudent.classids = cls.id LEFT JOIN student_detail AS sts ON installmentstudent.studid = sts.studid  WHERE installmentstudent.paydate BETWEEN '" + date1.ToString("yyyy/MM/dd") + "' AND '" + date2.ToString("yyyy/MM/dd") + "' AND installmentstudent.yearsids = '" + yrsid + "' AND installmentstudent.classids = '" + clsid + "' AND installmentstudent.divisionsids = '" + divid + "' and installmentstudent.paymode = '" + paymodes + "' and installmentstudent.balboardfees != '0'  GROUP BY installmentstudent.studid", 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 (incomeid == "exactivityfee")
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT (select sum(installmentstudent.balextraactivity) FROM installmentstudent AS ins WHERE installmentstudent.studid = ins.studid GROUP BY ins.studid) AS totaltution ,installmentstudent.studid,installmentstudent.yearsids,installmentstudent.classids,installmentstudent.divisionsids,ac1.fromdate,ac1.todate,cls.class,divs.division,DATE_FORMAT(installmentstudent.paydate,'%d-%m-%Y') AS paymentdt,installmentstudent.paymode,installmentstudent.chequenos,sts.title,sts.fname,sts.mname,sts.lname,sts.studid FROM  installmentstudent LEFT JOIN academic_year AS ac1 ON installmentstudent.yearsids = ac1.id LEFT JOIN division_master AS divs ON installmentstudent.divisionsids = divs.id LEFT JOIN class_master AS cls ON installmentstudent.classids = cls.id LEFT JOIN student_detail AS sts ON installmentstudent.studid = sts.studid  WHERE installmentstudent.paydate BETWEEN '" + date1.ToString("yyyy/MM/dd") + "' AND '" + date2.ToString("yyyy/MM/dd") + "' AND installmentstudent.yearsids = '" + yrsid + "' AND installmentstudent.classids = '" + clsid + "' AND installmentstudent.divisionsids = '" + divid + "' and installmentstudent.paymode = '" + paymodes + "' and installmentstudent.balextraactivity != '0'  GROUP BY installmentstudent.studid", 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 (incomeid == "developfee")
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT (select sum(installmentstudent.baldevelopemntfee) FROM installmentstudent AS ins WHERE installmentstudent.studid = ins.studid GROUP BY ins.studid) AS totaltution ,installmentstudent.studid,installmentstudent.yearsids,installmentstudent.classids,installmentstudent.divisionsids,ac1.fromdate,ac1.todate,cls.class,divs.division,DATE_FORMAT(installmentstudent.paydate,'%d-%m-%Y') AS paymentdt,installmentstudent.paymode,installmentstudent.chequenos,sts.title,sts.fname,sts.mname,sts.lname,sts.studid FROM  installmentstudent LEFT JOIN academic_year AS ac1 ON installmentstudent.yearsids = ac1.id LEFT JOIN division_master AS divs ON installmentstudent.divisionsids = divs.id LEFT JOIN class_master AS cls ON installmentstudent.classids = cls.id LEFT JOIN student_detail AS sts ON installmentstudent.studid = sts.studid  WHERE installmentstudent.paydate BETWEEN '" + date1.ToString("yyyy/MM/dd") + "' AND '" + date2.ToString("yyyy/MM/dd") + "' AND installmentstudent.yearsids = '" + yrsid + "' AND installmentstudent.classids = '" + clsid + "' AND installmentstudent.divisionsids = '" + divid + "' and installmentstudent.paymode = '" + paymodes + "' and installmentstudent.baldevelopemntfee != '0'  GROUP BY installmentstudent.studid", 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;
    }
}