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_canceladmissionaccount : 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 object Getdata(string clasr, string division, string acdyear)
    {
        string data = " ";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.studid as studids,class_master.class as clsname,division_master.division as divsname,package_detail.*,package_detail.id as pkgid,scholershipassign.firstinstall,scholershipassign.secondinstall,(SELECT (COALESCE(SUM(inst.paidamt),0)) FROM installmentstudent AS inst   where inst.studid = student_detail.studid and inst.classids ='" + clasr + "' and inst.divisionsids='" + division + "' and inst.yearsids = '" + acdyear + "' and inst.packageids != '') AS totalpaidamt,(select (COALESCE(SUM(inst.paidamt),0)) FROM installmentstudent AS inst   where inst.studid = student_detail.studid and inst.packageids = '' and inst.classids ='" + clasr + "' and inst.divisionsids='" + division + "' and inst.yearsids = '" + acdyear + "' ) AS scholerhipstudent,(select inst.paidamt FROM installmentstudent AS inst   where inst.studid = student_detail.studid and inst.packageids = '' and inst.classids ='" + clasr + "' and inst.divisionsids='" + division + "' and inst.yearsids = '" + acdyear + "' and scholershipstatus = 'Against Scholership') AS scholerhipamount,class_master.id as clsids,division_master.id as divsid,student_detail.admyears as admyrs,date_format(student_detail.today,'%d-%m-%Y') as admdate,(SELECT DISTINCT  p1.tuitionfee FROM installmentstudent AS inst  inner  join package_detail AS p1 ON inst.packageids = p1.id  where inst.studid = student_detail.studid AND inst.packageids = student_detail.packageid and  inst.classids ='" + clasr + "' and inst.divisionsids='" + division + "' and inst.yearsids = '" + acdyear + "') AS totaltutionfee,(SELECT DISTINCT  p1.developfee FROM installmentstudent AS inst  inner  join package_detail AS p1 ON inst.packageids = p1.id  where inst.studid = student_detail.studid AND inst.packageids = student_detail.packageid and inst.classids ='" + clasr + "' and inst.divisionsids='" + division + "' and inst.yearsids = '" + acdyear + "') AS totaldevelopfee  from  student_detail left join class_master on student_detail.classd = class_master.id left join division_master on student_detail.division = division_master.id left join package_detail on student_detail.packageid = package_detail.id left join scholershipassign on student_detail.studid = scholershipassign.studid where student_detail.classd ='" + clasr + "' and student_detail.division='" + division + "' and student_detail.admyears = '" + acdyear + "' and student_detail.canceladmission ='1'", 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 string Getdata12(string studids)
    {
        string _data = "";
        {

            using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
            {

                if (con.State != ConnectionState.Open) { con.Open(); }
                using (MySqlCommand cmdpack = new MySqlCommand("SELECT *,date_format(paydate,'%d-%m-%Y') as recptdate,date_format(chequedate,'%d-%m-%Y') as chqdt from installmentstudent where studid = '" + studids + "'", con))
                {
                    using (MySqlDataAdapter sda29 = new MySqlDataAdapter(cmdpack))
                    {
                        DataTable dt29 = new DataTable();
                        sda29.Fill(dt29);
                        con.Close();

                        if (dt29.Rows.Count > 0)
                        {
                            _data = JsonConvert.SerializeObject(dt29);
                            return _data;
                        }

                    }
                }
            }

        }

        return _data;
    }
}