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_scholershipmaster : 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 double tutionfee; public static double develpfee; public static double totalfee;
    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; }
    }
    [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(),
                                DeptIds = 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> PopulateDropDownList5(string classesid)
    {
        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 = '"+ classesid +"' ", 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> 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 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 string SavaData(string yrsid, string clsid, string divsid, string scheme, string schdate, string catids, string scholtutionamt, string scholdevelopamt,string itype,string amount, string statuss)
    {
        string data = "";
        if (schdate != "")
        {
            date1 = DateTime.Parse(schdate);
        }
        if (scholtutionamt != "")
        {
            tutionfee = Convert.ToDouble(scholtutionamt);
        }
        if (scholdevelopamt != "")
        {
            develpfee = Convert.ToDouble(scholdevelopamt);
        }
        totalfee = tutionfee + develpfee;
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            string query = "Insert into scholershipmaster(schemename,scholdate,categoryid,yearsid,classid,divisionid,tutionfees,dvelopementfees,installtype,amount,totalscholership,status) values(@schemename,@scholdate,@categoryid,@yearsid,@classid,@divisionid,@tutionfees,@dvelopementfees,@installtype,@amount,@totalscholership,@status)";
            using (MySqlCommand cmd = new MySqlCommand(query, con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                cmd.Parameters.AddWithValue("@schemename", scheme);
                cmd.Parameters.AddWithValue("@scholdate", date1.ToString("yyyy/MM/dd"));
                cmd.Parameters.AddWithValue("@categoryid", catids);
                cmd.Parameters.AddWithValue("@yearsid", yrsid);
                cmd.Parameters.AddWithValue("@classid", clsid);
                cmd.Parameters.AddWithValue("@divisionid", divsid);
                cmd.Parameters.AddWithValue("@tutionfees", scholtutionamt);
                cmd.Parameters.AddWithValue("@dvelopementfees", scholdevelopamt);
                cmd.Parameters.AddWithValue("@amount", amount);
                cmd.Parameters.AddWithValue("@installtype", itype);
                cmd.Parameters.AddWithValue("@totalscholership", totalfee);

                cmd.Parameters.AddWithValue("@status", statuss);
                cmd.ExecuteNonQuery();

                con.Close(); data = "Insert";
            }
        }
        return data;
    }
    [WebMethod]

    public static object GetData()
    {
        string data = " ";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select scholershipmaster.*,scholershipmaster.id as scholid,class_master.class as clsname,division_master.division as divsname,fee_category.feesname  from  scholershipmaster left join class_master on scholershipmaster.classid = class_master.id left join division_master on scholershipmaster.divisionid = division_master.id left join fee_category on scholershipmaster.categoryid = fee_category.id  ", 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 EditData(string ids)
    {
        string data = "";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select scholershipmaster.* from scholershipmaster where id='" + ids + "'", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        data = JsonConvert.SerializeObject(ds.Tables[0]);
                    }
                    cmd.ExecuteNonQuery();
                    con.Close();


                }
            }
        }
        return data;
    }
    [WebMethod]
    public static object UpdateData(string sids,string yrsid, string clsid, string divsid, string scheme, string schdate, string catids, string scholtutionamt, string scholdevelopamt, string itype, string amount, string statuss)
    {
        string data = "";
        if (schdate != "")
        {
            date1 = DateTime.Parse(schdate);
        }
       
        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
        {
            using (MySqlCommand cmd = new MySqlCommand("update scholershipmaster set schemename=@schemename,scholdate=@scholdate,categoryid=@categoryid,yearsid=@yearsid,classid=@classid,divisionid=@divisionid,tutionfees=@tutionfees,dvelopementfees=@dvelopementfees,installtype=@installtype,amount=@amount,totalscholership=@totalscholership,status=@status where id=@id", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                cmd.Parameters.AddWithValue("@id", sids);
                cmd.Parameters.AddWithValue("@schemename", scheme);
                cmd.Parameters.AddWithValue("@scholdate", date1.ToString("yyyy/MM/dd"));
                cmd.Parameters.AddWithValue("@categoryid", catids);
                cmd.Parameters.AddWithValue("@yearsid", yrsid);
                cmd.Parameters.AddWithValue("@classid", clsid);
                cmd.Parameters.AddWithValue("@divisionid", divsid);
                cmd.Parameters.AddWithValue("@tutionfees", scholtutionamt);
                cmd.Parameters.AddWithValue("@dvelopementfees", scholdevelopamt);
                cmd.Parameters.AddWithValue("@amount", amount);
                cmd.Parameters.AddWithValue("@installtype", itype);
                cmd.Parameters.AddWithValue("@totalscholership", totalfee);

                cmd.Parameters.AddWithValue("@status", statuss);
                cmd.ExecuteNonQuery();

                con.Close(); data = "update";

                con.Close();

            }
        }
        return data;
    }
    [WebMethod]
    public static object DeleteData(string id)
    {
        string data = "";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            if (con.State != ConnectionState.Open) { con.Open(); }
            using (MySqlCommand cmd = new MySqlCommand("delete from scholershipmaster where id='" + id + "'", con))
            {

                cmd.ExecuteNonQuery();
                using (MySqlDataAdapter dr = new MySqlDataAdapter(cmd))
                {
                    con.Close();
                    con.Dispose();
                    cmd.Dispose();

                }
            }
        }
        return data;
    }
}