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_addmissionnextclass : System.Web.UI.Page
{
    public static string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    public static MySqlConnection con = new MySqlConnection(constring);

    public static DateTime date1;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    [WebMethod]

    public static object GetData(string years, string classes, string divisions)
    {
        string data = " ";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,package_detail.id as pkgsids,academic_year.id as yearid from  student_detail inner join package_detail on student_detail.packageid = package_detail.id AND student_detail.admyears = package_detail.year AND student_detail.classd = package_detail.classdetail AND student_detail.division = package_detail.divisiondet left join class_master on student_detail.classd = class_master.id left join division_master on student_detail.division = division_master.id  left join academic_year on student_detail.admyears = academic_year.id  where student_detail.admyears = '" + years + "' and  student_detail.classd ='" + classes + "' and student_detail.division='" + divisions + "' and student_detail.transferstatus = ' '", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                DataTable dt = new DataTable();
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                {
                    da.Fill(dt);
                    if (dt.Rows.Count > 0)
                    {
                        data = JsonConvert.SerializeObject(dt);
                    }
                    con.Close();
                  
                }
            }
        }
        return data;
    }
    [WebMethod]

    public static object GetData12(string studentids, string packagesidss, string acdyears)
    {
        string data = " ";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select installmentstudent.*,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,date_format(installmentstudent.paydate,'%d-%m-%Y') as instdate,package_detail.id as pkgsids,division_master.division AS divsname from  installmentstudent INNER JOIN student_detail ON installmentstudent.studid =  student_detail.studid left join package_detail on installmentstudent.packageids = package_detail.id AND student_detail.admyears = package_detail.year AND  student_detail.classd = package_detail.classdetail  AND student_detail.division = package_detail.divisiondet LEFT JOIN division_master ON student_detail.division = division_master.id where installmentstudent.studid = '" + studentids + "' GROUP BY installmentstudent.id ORDER BY installmentstudent.id ", 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();

                }
            }
        }
        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; }
    }
    [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> PopulateDropDownList7()
    {
        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 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 string SavaData(string stids, string pksid,string clsold,string divsold,string yrsold, string yearsnews, string classesnew, string divisionsnews, string packegesnews)
    {
        string status = "";
        Random rand = new Random();
        string pkgid = rand.Next(0, 10000).ToString();
        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        {
            using (MySqlCommand cmdnew = new MySqlCommand("SELECT * from student_detail where studid = '" + stids + "' and admyears = '" + yrsold + "' and classd = '" + clsold + "' and division = '" + divsold + "'", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                using (MySqlDataAdapter danew = new MySqlDataAdapter(cmdnew))
                {
                    DataTable dtnew = new DataTable();
                    danew.Fill(dtnew);
                    con.Close();
                    if (dtnew.Rows.Count > 0)
                    {
                        using (MySqlConnection con1 = new MySqlConnection(constring))
                        {
                            string query = "Insert into student_detail(studid,title,fname,mname,lname,pname,mothernme,country,state,city,address,resiaddrss,peraddrss,pin,castcategory,bloodgroup,gender,dob,age,mobile1,mobile2,email,admyears,classd,division,highquali,adharcard,status,isprovisional,packageid,today) values(@studid,@title,@fname,@mname,@lname,@pname,@mothernme,@country,@state,@city,@address,@resiaddrss,@peraddrss,@pin,@castcategory,@bloodgroup,@gender,@dob,@age,@mobile1,@mobile2,@email,@admyears,@classd,@division,@highquali,@adharcard,@status,@isprovisional,@packageid,@today)";
                            using (MySqlCommand cmd = new MySqlCommand(query, con1))
                            {
                                if (con1.State != ConnectionState.Open) { con1.Open(); }
                                cmd.Parameters.AddWithValue("@studid", stids);
                                cmd.Parameters.AddWithValue("@title", dtnew.Rows[0]["title"].ToString());
                                cmd.Parameters.AddWithValue("@fname", dtnew.Rows[0]["fname"].ToString());
                                cmd.Parameters.AddWithValue("@mname", dtnew.Rows[0]["mname"].ToString());
                                cmd.Parameters.AddWithValue("@lname", dtnew.Rows[0]["lname"].ToString());
                                cmd.Parameters.AddWithValue("@pname", dtnew.Rows[0]["pname"].ToString());
                                cmd.Parameters.AddWithValue("@mothernme", dtnew.Rows[0]["mothernme"].ToString());
                                cmd.Parameters.AddWithValue("@country", dtnew.Rows[0]["country"].ToString());
                                cmd.Parameters.AddWithValue("@state", dtnew.Rows[0]["state"].ToString());
                                cmd.Parameters.AddWithValue("@city", dtnew.Rows[0]["city"].ToString());
                                cmd.Parameters.AddWithValue("@address", dtnew.Rows[0]["address"].ToString());
                                cmd.Parameters.AddWithValue("@resiaddrss", dtnew.Rows[0]["resiaddrss"].ToString());
                                cmd.Parameters.AddWithValue("@peraddrss", dtnew.Rows[0]["peraddrss"].ToString());
                                cmd.Parameters.AddWithValue("@pin", dtnew.Rows[0]["pin"].ToString());
                                cmd.Parameters.AddWithValue("@castcategory", dtnew.Rows[0]["castcategory"].ToString());
                                cmd.Parameters.AddWithValue("@bloodgroup", dtnew.Rows[0]["bloodgroup"].ToString());
                                cmd.Parameters.AddWithValue("@gender", dtnew.Rows[0]["gender"].ToString());
                                if (dtnew.Rows[0]["dob"].ToString() != "")
                                {
                                    date1 = DateTime.Parse(dtnew.Rows[0]["dob"].ToString());
                                        
                                    cmd.Parameters.AddWithValue("@dob", date1.ToString("yyyy/MM/dd"));
                                }
                                else
                                {
                                    cmd.Parameters.AddWithValue("@dob", null);
                                }
                                cmd.Parameters.AddWithValue("@age", dtnew.Rows[0]["age"].ToString());
                                cmd.Parameters.AddWithValue("@mobile1", dtnew.Rows[0]["mobile1"].ToString());
                                cmd.Parameters.AddWithValue("@mobile2", dtnew.Rows[0]["mobile2"].ToString());
                                cmd.Parameters.AddWithValue("@email", dtnew.Rows[0]["email"].ToString());
                                cmd.Parameters.AddWithValue("@admyears", yearsnews);
                                cmd.Parameters.AddWithValue("@classd", classesnew);
                                cmd.Parameters.AddWithValue("@today", DateTime.UtcNow.ToString("yyyy/MM/dd"));
                                cmd.Parameters.AddWithValue("@division", divisionsnews);

                                cmd.Parameters.AddWithValue("@highquali", dtnew.Rows[0]["highquali"].ToString());
                                cmd.Parameters.AddWithValue("@adharcard", dtnew.Rows[0]["adharcard"].ToString());
                                cmd.Parameters.AddWithValue("@status", dtnew.Rows[0]["status"].ToString());
                                cmd.Parameters.AddWithValue("@isprovisional", dtnew.Rows[0]["isprovisional"].ToString());
                                cmd.Parameters.AddWithValue("@packageid", packegesnews);
                                cmd.ExecuteNonQuery();

                                con1.Close(); status = "insert";
                            }
                        }
                        using (MySqlConnection con2 = new MySqlConnection(constring))
                        {
                            string query = "update student_detail set transferstatus = 'transferred' where studid = '" + stids + "' and packageid = '" + pksid + "' and admyears ='" + yrsold + "'  and classd = '" + clsold + "' and division= '" + divsold + "'";
                            using (MySqlCommand cmd2 = new MySqlCommand(query, con2))
                            {
                                if (con2.State != ConnectionState.Open) { con2.Open(); }
                                cmd2.ExecuteNonQuery();
                                con2.Close();
                            }
                        }
                    }
                }
            }
        }
        return status;
    }
}