using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Data;
using System.Web.UI.HtmlControls;
using System.Web.Services;
using System.Web.Script.Serialization;
using Newtonsoft.Json;
using System.Net;
using System.Collections;
using System.IO;
using System.Diagnostics;
using System.Web.Script.Services;
public partial class module_previewaccount : System.Web.UI.Page
{
    public static string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    public static MySqlConnection con = new MySqlConnection(constring);
    public static DateTime date1;
    public static DateTime date2;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    [WebMethod]

    public static object Getdata()
    {
        string data = " ";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select st.*,inst.*,pkg.packgenme as pkgnm,pkg.amount as pkamt,st.studid as studids,class_master.class as clsname,division_master.division as divsname,acd.fromdate,acd.todate,(SELECT (COALESCE(SUM(installmentstudent.paidamt),0))  FROM  installmentstudent where installmentstudent.studid= inst.studid ORDER BY installmentstudent.id ) as totalpaidamt,st.admyears as admyrs,st.classd as clsids,st.division as divsid,st.packageid as pkgid from student_detail as st inner join installmentstudent as inst  on st.studid = inst.studid left join package_detail as pkg  on inst.packageids = pkg.id left join class_master on st.classd = class_master.id left join division_master on st.division = division_master.id left join academic_year as acd on st.admyears = acd.id group by inst.studid order by inst.studid 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 getpaidamt()
    {
        string data = " ";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select (SELECT (COALESCE(SUM(installmentstudent.paidamt),0))  FROM  installmentstudent where installmentstudent.studid= inst.studid ORDER BY installmentstudent.id ) as totalpaidamt from student_detail as st inner join installmentstudent as inst  on st.studid = inst.studid left join package_detail as pkg  on inst.packageids = pkg.id where inst.paydate = '" + DateTime.UtcNow.ToString("yyyy/MM/dd") + "'  order by inst.studid 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 GetdataSearch(string yrs, string cls, string divs, string fromdt, string todt)
    {
        string data = " ";
        if (fromdt != "" && todt != "")
        {
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("select st.*,inst.*,pkg.packgenme as pkgnm,pkg.amount as pkamt,st.studid as studids,class_master.class as clsname,division_master.division as divsname,acd.fromdate,acd.todate,(SELECT (COALESCE(SUM(installmentstudent.paidamt),0))  FROM  installmentstudent where installmentstudent.studid= inst.studid ORDER BY installmentstudent.id ) as totalpaidamt,st.admyears as admyrs,st.classd as clsids,st.division as divsid,st.packageid as pkgid from student_detail as st inner join installmentstudent as inst  on st.studid = inst.studid left join package_detail as pkg  on inst.packageids = pkg.id left join class_master on st.classd = class_master.id left join division_master on st.division = division_master.id left join academic_year as acd on st.admyears = acd.id where inst.yearsids = '" + yrs + "' and inst.classids = '" + cls + "' and inst.divisionsids = '" + divs + "' group by inst.studid order by inst.studid 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
        {
            if (fromdt != "")
            {
                date1 = DateTime.Parse(fromdt);
            }
            if (todt != "")
            {
                date2 = DateTime.Parse(todt);
            }
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("select st.*,inst.*,pkg.packgenme as pkgnm,pkg.amount as pkamt,st.studid as studids,class_master.class as clsname,division_master.division as divsname,acd.fromdate,acd.todate,(SELECT (COALESCE(SUM(installmentstudent.paidamt),0))  FROM  installmentstudent where installmentstudent.studid= inst.studid ORDER BY installmentstudent.id ) as totalpaidamt,st.admyears as admyrs,st.classd as clsids,st.division as divsid,st.packageid as pkgid from student_detail as st inner join installmentstudent as inst  on st.studid = inst.studid left join package_detail as pkg  on inst.packageids = pkg.id left join class_master on st.classd = class_master.id left join division_master on st.division = division_master.id left join academic_year as acd on st.admyears = acd.id where inst.paydate  between '" + date1.ToString("yyyy/MM/dd") + "' and  '" + date2.ToString("yyyy/MM/dd") + "'inst.yearsids = '" + yrs + "' and inst.classids = '" + cls + "' and inst.divisionsids = '" + divs + "' and   group by inst.studid order by inst.studid 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 Getdata1(string studids, string yrs, string clsids, string divs, string packgid)
    {
        string data = " ";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("SELECT inst.*,date_format(inst.paydate,'%d-%m-%Y') as inspaydt,date_format(inst.chequedate,'%d-%m-%Y') as chkdate FROM student_detail AS st INNER JOIN installmentstudent AS inst ON st.studid = inst.studid WHERE inst.studid= '" + studids + "' AND inst.yearsids = '" + yrs + "'  AND  inst.classids = '" + clsids + "' AND inst.divisionsids = '" + divs + "' AND  inst.packageids = '" + packgid + "' ORDER BY inst.id desc ", 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 DeptIds { get; set; }
        public string Initial { get; set; }
        public string year1 { get; set; } public string firstname { get; set; } public string midname { get; set; } public string lastname { get; set; }
    }
    [WebMethod]
    public static List<Department> PopulateDropDownList8()
    {
        DataTable dtnew = new DataTable();
        DataTable dtnew1 = new DataTable();
        List<Department> objDept = new List<Department>();

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

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

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

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

                            });
                        }
                    }
                }
            }
        }
        return objDept;
    }
}