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_cashbook : System.Web.UI.Page
{
    public static string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    public static MySqlConnection con = new MySqlConnection(constring);
    public static DateTime date1; public static DateTime date2;
    public static DateTime date3; public static DateTime date4;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    [WebMethod]

    public static object Getdata(string paydate, string paydate1, string clsnm, string divsid, string yearsid)
    {
        string data = " ";
        if (paydate != "")
        {
            date1 = DateTime.Parse(paydate);
        }
        if (paydate1 != "")
        {
            date2 = DateTime.Parse(paydate1);
        }
       if(clsnm != "0"){
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT inst.*,st.studid,st.title,st.fname,st.mname,st.lname,class_master.class as clsname,division_master.division  as divsname,date_format(inst.paydate,'%d-%m-%Y') as ppaymentdate FROM installmentstudent AS inst inner JOIN student_detail AS st ON inst.studid = st.studid AND inst.divisionsids = st.division AND inst.yearsids = st.admyears left join class_master on st.classd = class_master.id left join division_master on st.division = division_master.id WHERE  inst.paydate between '" + paydate + "' and '" + paydate1 + "' and inst.classids ='" + clsnm + "' ORDER BY inst.paydate asc", con))
                {
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    DataSet ds = new DataSet();
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds); con.Close();
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }


                    }
                }
            }
       }else{
 using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT inst.*,st.studid,st.title,st.fname,st.mname,st.lname,class_master.class as clsname,division_master.division  as divsname,date_format(inst.paydate,'%d-%m-%Y') as ppaymentdate FROM installmentstudent AS inst inner JOIN student_detail AS st ON inst.studid = st.studid AND inst.divisionsids = st.division AND inst.yearsids = st.admyears left join class_master on st.classd = class_master.id left join division_master on st.division = division_master.id WHERE  inst.paydate between '" + paydate + "' and '" + paydate1 + "'  ORDER BY inst.paydate asc", con))
                {
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    DataSet ds = new DataSet();
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds); con.Close();
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }


                    }
                }
}
}
        return data;
    }
    [WebMethod]

    public static object Getdataincome(string paydate, string paydate1, string clsnms, string divsid, string yearsid)
    {
        string data = " ";
        if (paydate != "")
        {
            date1 = DateTime.Parse(paydate);
        }
        if (paydate1 != "")
        {
            date2 = DateTime.Parse(paydate1);
        }
      if(clsnms != "0"){
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT inc.*,i1.incomes AS incname,st.title,st.fname,st.mname,st.lname,class_master.class as clsname,division_master.division  as divsname,DATE_format(inc.paydate,'%d-%m-%Y') AS incpdate FROM incomeentry AS inc LEFT JOIN incomasters AS i1 ON inc.incomeheads = i1.id LEFT JOIN student_detail AS st ON inc.studid = st.studid left join class_master on st.classd = class_master.id left join division_master on st.division = division_master.id  WHERE inc.paydate between '" + date1.ToString("yyyy/MM/dd") + "' and '" + date2.ToString("yyyy/MM/dd") + "' and classes = '" + clsnms + "' ", con))
                {
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    DataSet ds = new DataSet();
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds); con.Close();
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }


                    }
                }
            }
        } else{
 using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT inc.*,i1.incomes AS incname,st.title,st.fname,st.mname,st.lname,class_master.class as clsname,division_master.division  as divsname,DATE_format(inc.paydate,'%d-%m-%Y') AS incpdate FROM incomeentry AS inc LEFT JOIN incomasters AS i1 ON inc.incomeheads = i1.id LEFT JOIN student_detail AS st ON inc.studid = st.studid left join class_master on st.classd = class_master.id left join division_master on st.division = division_master.id  WHERE inc.paydate between '" + date1.ToString("yyyy/MM/dd") + "' and '" + date2.ToString("yyyy/MM/dd") + "' ", con))
                {
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    DataSet ds = new DataSet();
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds); con.Close();
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }


                    }
                }
            }
}
        return data;
    }
    [WebMethod]
    public static object Getdataexam(string paydate, string paydate1, string clsnms, string divsid, string yearsid)
    {
        string data = "";
        if (paydate != "")
        {
            date1 = DateTime.Parse(paydate);
        }
        if (paydate1 != "")
        {
            date2 = DateTime.Parse(paydate1);
        }
      if(clsnms != "0"){
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT ex.*,i1.feepackname AS incname,i1.totalamount AS tamount,st.title,st.fname,st.mname,st.lname,class_master.class as clsname,division_master.division  as divsname,DATE_format(ex.examdt,'%d-%m-%Y') AS payexamdate FROM exampackageassign AS ex LEFT JOIN examfeemaster AS i1 ON ex.exampkgid = i1.id LEFT JOIN student_detail AS st ON ex.studids = st.studid left join class_master on ex.classids = class_master.id left join division_master on ex.divisonsid = division_master.id  WHERE ex.examdt BETWEEN '" + date1.ToString("yyyy/MM/dd") + "' AND '" + date2.ToString("yyyy/MM/dd") + "' and ex.classids = '" + clsnms + "'", con))
                {
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    DataSet ds = new DataSet();
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds); con.Close();
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }


                    }
                }
            }
     }
else{
using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT ex.*,i1.feepackname AS incname,i1.totalamount AS tamount,st.title,st.fname,st.mname,st.lname,class_master.class as clsname,division_master.division  as divsname,DATE_format(ex.examdt,'%d-%m-%Y') AS payexamdate FROM exampackageassign AS ex LEFT JOIN examfeemaster AS i1 ON ex.exampkgid = i1.id LEFT JOIN student_detail AS st ON ex.studids = st.studid left join class_master on ex.classids = class_master.id left join division_master on ex.divisonsid = division_master.id  WHERE ex.examdt BETWEEN '" + date1.ToString("yyyy/MM/dd") + "' AND '" + date2.ToString("yyyy/MM/dd") + "' ", con))
                {
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    DataSet ds = new DataSet();
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds); con.Close();
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }


                    }
                }
            }
}
        return data;
    }
    [WebMethod]
    public static object Exportsexamfee(string paydate, string paydate1, string clsnm, string divsid, string yearsid)
    {
        string data = "";
        if (paydate != "")
        {
            date1 = DateTime.Parse(paydate);
        }
        if (paydate1 != "")
        {
            date2 = DateTime.Parse(paydate1);
        }
        if (clsnm != "0")
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT ex.*,i1.feepackname AS incname,i1.totalamount AS tamount,st.title,st.fname,st.mname,st.lname,class_master.class as clsname,division_master.division  as divsname,DATE_format(ex.examdt,'%d-%m-%Y') AS payexamdate FROM exampackageassign AS ex LEFT JOIN examfeemaster AS i1 ON ex.exampkgid = i1.id LEFT JOIN student_detail AS st ON ex.studids = st.studid AND ex.yearid = st.admyears AND ex.classids = st.classd  left join class_master on ex.classids = class_master.id left join division_master on ex.divisonsid = division_master.id  WHERE ex.examdt BETWEEN '" + date1.ToString("yyyy/MM/dd") + "' AND '" + date2.ToString("yyyy/MM/dd") + "' and ex.classids = '" + clsnm + "'", con))
                {
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    DataSet ds = new DataSet();
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds); con.Close();
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }


                    }
                }
            }
        }
        else
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT ex.*,i1.feepackname AS incname,i1.totalamount AS tamount,st.title,st.fname,st.mname,st.lname,class_master.class as clsname,division_master.division  as divsname,DATE_format(ex.examdt,'%d-%m-%Y') AS payexamdate FROM exampackageassign AS ex LEFT JOIN examfeemaster AS i1 ON ex.exampkgid = i1.id LEFT JOIN student_detail AS st ON ex.studids = st.studid AND ex.yearid = st.admyears AND ex.classids = st.classd  left join class_master on ex.classids = class_master.id left join division_master on ex.divisonsid = division_master.id  WHERE ex.examdt BETWEEN '" + date1.ToString("yyyy/MM/dd") + "' AND '" + date2.ToString("yyyy/MM/dd") + "'", con))
                {
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    DataSet ds = new DataSet();
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds); con.Close();
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }


                    }
                }
            }
        }
        return data;
    }
    [WebMethod]

    public static object Exports1(string paydate, string paydate1, string clsnms, string divsid, string yearsid)
    {
        string data = " ";
        if (paydate != "")
        {
            date1 = DateTime.Parse(paydate);
        }
      if(clsnms != "0"){
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT inc.*,i1.incomes AS incname,st.title,st.fname,st.mname,st.lname,class_master.class as clsname,division_master.division  as divsname,DATE_format(inc.paydate,'%d-%m-%Y') AS incpdate FROM incomeentry AS inc LEFT JOIN incomasters AS i1 ON inc.incomeheads = i1.id LEFT JOIN student_detail AS st ON inc.studid = st.studid left join class_master on st.classd = class_master.id left join division_master on st.division = division_master.id   WHERE inc.paydate between '" + date1.ToString("yyyy/MM/dd") + "' and '" + date2.ToString("yyyy/MM/dd") + "' and classes = '" + clsnms + "'", con))
                {
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    DataSet ds = new DataSet();
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds); con.Close();
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }


                    }
                }
            }
      }
else{
  using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT inc.*,i1.incomes AS incname,st.title,st.fname,st.mname,st.lname,class_master.class as clsname,division_master.division  as divsname,DATE_format(inc.paydate,'%d-%m-%Y') AS incpdate FROM incomeentry AS inc LEFT JOIN incomasters AS i1 ON inc.incomeheads = i1.id LEFT JOIN student_detail AS st ON inc.studid = st.studid left join class_master on st.classd = class_master.id left join division_master on st.division = division_master.id   WHERE inc.paydate between '" + date1.ToString("yyyy/MM/dd") + "' and '" + date2.ToString("yyyy/MM/dd") + "' ", con))
                {
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    DataSet ds = new DataSet();
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds); con.Close();
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }


                    }
                }
            }
}
        
        return data;
    }
    [WebMethod]

    public static object Exports(string paydate, string paydate1, string clsnm, string divisionsid, string years)
    {
        string data = " ";
        if (paydate != "")
        {
            date1 = DateTime.Parse(paydate);
        }
        if (clsnm != "0")
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT inst.*,st.studid,st.title,st.fname,st.mname,st.lname,class_master.class as clsname,division_master.division  as divsname,date_format(inst.paydate,'%d-%m-%Y') as ppaymentdate FROM installmentstudent AS inst inner JOIN student_detail AS st ON inst.studid = st.studid AND inst.divisionsids = st.division AND inst.yearsids = st.admyears left join class_master on st.classd = class_master.id left join division_master on st.division = division_master.id where inst.paydate between '" + paydate + "' and '" + paydate1 + "' and inst.classids ='" + clsnm + "'  ORDER BY inst.paydate asc", con))
                {
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    DataSet ds = new DataSet();
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds); con.Close();
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }


                    }
                }
            }
        }
        else
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT inst.*,st.studid,st.title,st.fname,st.mname,st.lname,class_master.class as clsname,division_master.division  as divsname,date_format(inst.paydate,'%d-%m-%Y') as ppaymentdate FROM installmentstudent AS inst inner JOIN student_detail AS st ON inst.studid = st.studid AND inst.divisionsids = st.division AND inst.yearsids = st.admyears left join class_master on st.classd = class_master.id left join division_master on st.division = division_master.id where inst.paydate between '" + paydate + "' and '" + paydate1 + "'   ORDER BY inst.paydate asc", con))
                {
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    DataSet ds = new DataSet();
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds); con.Close();
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }


                    }
                }
            }
        }
        return data;
    }
    public class Department
    {
        public int DeptId { get; set; }
        public string DeptName { get; set; }  public string year1 { get; set; }
    }
    [WebMethod]
    public static List<Department> PopulateDropDownListclass()
    {
        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> 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> PopulateDropDownListdivision(string clids)
    {
        DataTable dtnew = new DataTable();
        DataTable dtnew1 = new DataTable();
        List<Department> objDept = new List<Department>();

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

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

        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        {
            using (MySqlCommand cmdnew = new MySqlCommand("SELECT * from expensesasters", 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]["expenses"].ToString(),

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

    public static object Getdataexpenses(string paydate, string paydate1)
    {
        string data = " ";
        if (paydate != "")
        {
            date3 = DateTime.Parse(paydate);
        }
        if (paydate1 != "")
        {
            date4 = DateTime.Parse(paydate1);
        }
        //if (expid == "0")
       // {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT ex.expenses as exnsmr,expenses_setup.*,DATE_FORMAT(expenses_setup.paydate,'%d-%m-%Y') AS expaydt FROM expenses_setup LEFT JOIN expensesasters AS ex ON expenses_setup.exepnses = ex.id  WHERE expenses_setup.paydate between '" + date3.ToString("yyyy/MM/dd") + "' and '" + date4.ToString("yyyy/MM/dd") + "'", con))
                {
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    DataSet ds = new DataSet();
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(ds); con.Close();
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }


                    }
                }
            }
        //}
        //else
        //{
        //    using (MySqlConnection con = new MySqlConnection(constring))
        //    {
        //        using (MySqlCommand cmd = new MySqlCommand("SELECT ex.expenses as exnsmr,expenses_setup.*,DATE_FORMAT(expenses_setup.paydate,'%d-%m-%Y') AS expaydt FROM expenses_setup LEFT JOIN expensesasters AS ex ON expenses_setup.exepnses = ex.id  WHERE expenses_setup.paydate between '" + date3.ToString("yyyy/MM/dd") + "' and '" + date4.ToString("yyyy/MM/dd") + "' and exepnses ='"+ expid +"'", con))
        //        {
        //            if (con.State != ConnectionState.Open) { con.Open(); }
        //            DataSet ds = new DataSet();
        //            using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
        //            {
        //                da.Fill(ds); con.Close();
        //                if (ds.Tables[0].Rows.Count > 0)
        //                {
        //                    data = JsonConvert.SerializeObject(ds.Tables[0]);
        //                }


        //            }
        //        }
        //    }
        //}
        return data;
    }
    [WebMethod]

    public static object Exports2(string paydate, string paydate1)
    {
        string data = " ";
        if (paydate != "")
        {
            date3 = DateTime.Parse(paydate);
        }
        if (paydate1 != "")
        {
            date4 = DateTime.Parse(paydate1);
        }
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("SELECT ex.expenses as exnsmr,expenses_setup.*,DATE_FORMAT(expenses_setup.paydate,'%d-%m-%Y') AS expaydt FROM expenses_setup LEFT JOIN expensesasters AS ex ON expenses_setup.exepnses = ex.id  WHERE expenses_setup.paydate between '" + date3.ToString("yyyy/MM/dd") + "' and '" + date4.ToString("yyyy/MM/dd") + "'", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                DataSet ds = new DataSet();
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                {
                    da.Fill(ds); con.Close();
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        data = JsonConvert.SerializeObject(ds.Tables[0]);
                    }


                }
            }
        }
        return data;
    }
}