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_assignspackagesstud : System.Web.UI.Page
{
    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 ptitle { get; set; }
        public string fname { get; set; }  public string mname { get; set; }  public string lname { 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> 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 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 List<Department> PopulateDropDownListCaste()
    {
        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 fee_category", 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]["feesname"].ToString(),

                            });
                        }
                    }
                }
            }
        }
        return objDept;
    }
    [WebMethod]
    public static List<Department> PopulateDropDownListstudent(string yrs1, string clsid, string divsid, string castes)
    {
        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 student_detail where admyears ='" + yrs1 + "' and classd ='" + clsid + "' and division ='" + divsid + "' and castcategory ='" + castes + "'", 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
                                {

                                    DeptName = dtnew.Rows[i]["studid"].ToString(),
                                    ptitle = dtnew.Rows[i]["title"].ToString(),
                                    fname = dtnew.Rows[i]["fname"].ToString(),
                                    mname = dtnew.Rows[i]["mname"].ToString(),
                                    lname = dtnew.Rows[i]["lname"].ToString(),
                                });
                            }
                        }
                    }
                }
            }
      
        return objDept;
    }
    [WebMethod]
    public static List<Department> PopulateDropDownListstudent1(string studids, string packagesid)
    {
        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 student_detail where studid ='" + studids + "' and packageid = '"+ packagesid +"'", 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
                            {

                                DeptName = dtnew.Rows[i]["studid"].ToString(),
                                ptitle = dtnew.Rows[i]["title"].ToString(),
                                fname = dtnew.Rows[i]["fname"].ToString(),
                                mname = dtnew.Rows[i]["mname"].ToString(),
                                lname = dtnew.Rows[i]["lname"].ToString(),
                            });
                        }
                    }
                }
            }
        }

        return objDept;
    }
    [WebMethod]

    public static object GetDataUpdate1()
    {
        string data = " ";

        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        {
            using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,student_detail.division as divisionname 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  where  student_detail.packageid != '' 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();
                    con.Dispose();
                    cmd.Dispose();
                }
            }
        }


        return data;
    }
    [WebMethod]

    public static object GetDataUpdate(string studids)
    {
        string data = " ";

        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        {
            using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,student_detail.division as divisionname 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  where student_detail.studid ='" + studids + "' and packageid != ''", 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;
    }
    [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 * from package_detail ", 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]["packgenme"].ToString(),

                            });
                        }
                    }
                }
            }
        }
        return objDept;
    }
    [WebMethod]
    public static string updatestudetails(string studidsids, string yrid, string classids, string divsid, string pkgid, string pkgoldid)
    {
        string data = "";
        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        {
            using (MySqlCommand cmd = new MySqlCommand("select *  from   student_detail  where studid ='" + studidsids + "' and admyears = '" + yrid + "' and classd='" + classids + "' and division ='" + divsid + "' and packageid = '" + pkgoldid + "'", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                DataTable ds = new DataTable();
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                {
                    da.Fill(ds);
                    if (ds.Rows.Count > 0)
                    {
                        using (MySqlConnection con1 = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
                        {
                            using (MySqlCommand cmd1 = new MySqlCommand("update student_detail set packageid=@packgenme where classd='" + classids + "' and division ='" + divsid + "' and studid ='" + studidsids + "' and  admyears = '" + yrid + "' and  packageid = '" + pkgoldid + "' ", con1))
                            {
                                if (con1.State != ConnectionState.Open) { con1.Open(); }
                                cmd1.Parameters.AddWithValue("@packgenme", pkgid);

                                cmd1.ExecuteNonQuery();

                                con1.Close();
                                data = "update";
                            }
                        }
                        using (MySqlConnection con3 = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
                        {
                            using (MySqlCommand cmd3 = new MySqlCommand("select *  from   installmentstudent  where studid ='" + studidsids + "' and yearsids = '" + yrid + "' and classids='" + classids + "' and divisionsids ='" + divsid + "' and packageids = '" + pkgoldid + "'", con3))
                            {
                                if (con3.State != ConnectionState.Open) { con3.Open(); }
                                DataTable ds3 = new DataTable();
                                using (MySqlDataAdapter da3 = new MySqlDataAdapter(cmd3))
                                {
                                    da3.Fill(ds3);
                                    con3.Close();
                                    if (ds3.Rows.Count > 0)
                                    {
                                        for (int j = 0; j < ds3.Rows.Count; j++)
                                        {
                                            using (MySqlConnection con1 = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
                                            {
                                                using (MySqlCommand cmd1 = new MySqlCommand("update installmentstudent set packageids=@packgenme where id = '" + ds3.Rows[j]["id"].ToString() + "' and  classids='" + ds3.Rows[j]["classids"].ToString() + "' and divisionsids ='" + ds3.Rows[j]["divisionsids"].ToString() + "' and studid ='" + ds3.Rows[j]["studid"].ToString() + "' and  yearsids = '" + ds3.Rows[j]["yearsids"].ToString() + "' and  packageids = '" + ds3.Rows[j]["packageids"].ToString() + "' ", con1))
                                                {
                                                    if (con1.State != ConnectionState.Open) { con1.Open(); }
                                                    cmd1.Parameters.AddWithValue("@packgenme", pkgid);

                                                    cmd1.ExecuteNonQuery();

                                                    con1.Close();
                                                    data = "update";
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
        return data;
    }
}