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;
public partial class module_scholershipassign : 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 string[] splitstr;
    public static string scholersids;
    public static double paidamt; public static double totalamt; public static double totalbalcncesamt;
    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; }
    }
    [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;
    }
    [WebMethod]
    public static List<Department> PopulateDropDownList12()
    {
        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 scholershipmaster where status ='Active'", 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]["schemename"].ToString(),

                            });
                        }
                    }
                }
            }
        }
        return objDept;
    }
    [WebMethod]
    public static List<Department> PopulateDropDownList11()
    {
        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 object GetData(string years, string classs, string divisions, string cat)
    {
        string data = " ";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,sch.installtype,sch.firstinstall,sch.secondinstall,Date_Format(student_detail.dob,'%d-%m-%Y') as studdob,class_master.class as classname,division_master.division as divname ,academic_year.fromdate,academic_year.todate,f1.feesname as feename,(SELECT (COALESCE(SUM(inst.paidamt),0)) FROM installmentstudent AS inst   where inst.studid = student_detail.studid and inst.packageids != '') AS totalpaidamt,(SELECT pkg.amount FROM package_detail AS pkg   where  student_detail.packageid =  pkg.id) AS totalamtss,student_detail.studid as studids from  student_detail inner join package_detail on student_detail.packageid = package_detail.id 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 left join fee_category as f1 on student_detail.castcategory = f1.id left join scholershipassign as sch on  student_detail.studid = sch.studid where student_detail.status = 'Scholorship' and student_detail.admyears ='" + years + "' and student_detail.classd = '" + classs + "' and  student_detail.division = '" + divisions + "' and student_detail.castcategory = '" + cat + "'", 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 string UpdateData(string studids, string scholids, string yid, string cids, string dids)
    {
        string data = "";
        Random rand = new Random();
        string pkgid = rand.Next(0, 10000).ToString();
        //splitstr = studids.Split(',');
        //for (int i = 0; i < splitstr.Length; i++)
        //{
          //  scholersids = splitstr[i].ToString();
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("select * from student_detail where studid ='" + studids + "' and status = 'Scholorship'", con))
                {
                    if (con.State != ConnectionState.Open) { con.Open(); }
                    DataTable dt = new DataTable();
                    using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                    {
                        da.Fill(dt); con.Close();
                        if (dt.Rows.Count > 0)
                        {
                            using (MySqlConnection con7 = new MySqlConnection(constring))
                            {
                                using (MySqlCommand cmd7 = new MySqlCommand("select * from scholershipassign where studid ='" + studids + "' and yrsid ='" + yid + "' and clsid ='" + cids + "' and diivisionid = '" + dids + "' and firstinstall != ''", con7))
                                {
                                    if (con7.State != ConnectionState.Open) { con7.Open(); }
                                    DataTable dt7 = new DataTable();
                                    using (MySqlDataAdapter da7 = new MySqlDataAdapter(cmd7))
                                    {
                                        da7.Fill(dt7); con7.Close();
                                        if (dt7.Rows.Count > 0)
                                        {
                                            data = "available";
                                        }
                                        else
                                        {
                                            using (MySqlConnection con1 = new MySqlConnection(constring))
                                            {
                                                using (MySqlCommand cmd1 = new MySqlCommand("select * from scholershipmaster where id='" + scholids + "' and categoryid ='" + dt.Rows[0]["castcategory"].ToString() + "' and classid = '" + dt.Rows[0]["classd"].ToString() + "' and divisionid = '" + dt.Rows[0]["division"].ToString() + "' and installtype = 'firstinst'", con1))
                                                {
                                                    if (con1.State != ConnectionState.Open) { con1.Open(); }
                                                    DataTable dt1 = new DataTable();
                                                    using (MySqlDataAdapter da1 = new MySqlDataAdapter(cmd1))
                                                    {
                                                        da1.Fill(dt1); con1.Close();
                                                        if (dt1.Rows.Count > 0)
                                                        {
                                                            double scholamt = Convert.ToDouble(dt1.Rows[0]["totalscholership"].ToString());
                                                            using (MySqlConnection con2 = new MySqlConnection(constring))
                                                            {
                                                                using (MySqlCommand cmd2 = new MySqlCommand("select inst.id as installid,student_detail.* ,((SELECT package_detail.amount FROM package_detail WHERE package_detail.id = '" + dt.Rows[0]["packageid"].ToString() + "' ORDER BY package_detail.id ) - (SELECT (COALESCE(SUM(installmentstudent.paidamt),0))  FROM installmentstudent where installmentstudent.studid= inst.studid ORDER BY installmentstudent.id)) AS balatotalamts,pkg.amount from  student_detail  left  join  installmentstudent as inst on inst.studid = student_detail.studid LEFT JOIN package_detail AS pkg ON student_detail.packageid = pkg.id where student_detail.packageid ='" + dt.Rows[0]["packageid"].ToString() + "' and student_detail.studid = '" + studids + "' GROUP BY inst.id  ORDER BY inst.id DESC LIMIT 1", con2))
                                                                {
                                                                    if (con2.State != ConnectionState.Open) { con2.Open(); }
                                                                    DataTable dt2 = new DataTable();
                                                                    using (MySqlDataAdapter da2 = new MySqlDataAdapter(cmd2))
                                                                    {
                                                                        da2.Fill(dt2); con2.Close();
                                                                        if (dt2.Rows.Count > 0)
                                                                        {
                                                                            paidamt = Convert.ToDouble(dt2.Rows[0]["balatotalamts"].ToString());
                                                                            totalamt = scholamt + paidamt;
                                                                            totalbalcncesamt = Convert.ToDouble(dt2.Rows[0]["amount"].ToString()) - totalamt;
                                                                            using (MySqlConnection con3 = new MySqlConnection(constring))
                                                                            {
                                                                                string query = "update student_detail set scholershipstatus = @scholershipstatus,scholershipid=@scholershipid where studid = '" + studids + "' and admyears = '" + dt2.Rows[0]["admyears"].ToString() + "' and classd = '" + dt2.Rows[0]["classd"].ToString() + "' and division = '" + dt2.Rows[0]["division"].ToString() + "'";


                                                                                using (MySqlCommand cmd3 = new MySqlCommand(query, con3))
                                                                                {
                                                                                    if (con3.State != ConnectionState.Open) { con3.Open(); }
                                                                                    cmd3.Parameters.AddWithValue("@scholershipstatus", "Approved");
                                                                                    cmd3.Parameters.AddWithValue("@scholershipid", dt1.Rows[0]["id"].ToString());
                                                                                    cmd3.ExecuteNonQuery();
                                                                                    con3.Close();
                                                                                }


                                                                            }
                                                                            using (MySqlConnection con3 = new MySqlConnection(constring))
                                                                            {

                                                                                string query = "insert  into  scholershipassign(installtype,assigndt,scholerahipid,firstinstall,studid,yrsid,clsid,diivisionid) values(@installtype,@assigndt,@scholerahipid,@firstinstall,@studid,@yrsid,@clsid,@divisionid)";

                                                                                using (MySqlCommand cmd3 = new MySqlCommand(query, con3))
                                                                                {
                                                                                    if (con3.State != ConnectionState.Open) { con3.Open(); }



                                                                                    cmd3.Parameters.AddWithValue("@assigndt", DateTime.UtcNow.ToString("yyyy/MM/dd"));

                                                                                    cmd3.Parameters.AddWithValue("@installtype", dt1.Rows[0]["installtype"].ToString());
                                                                                    cmd3.Parameters.AddWithValue("@firstinstall", dt1.Rows[0]["amount"].ToString());
                                                                                    cmd3.Parameters.AddWithValue("@scholerahipid", dt1.Rows[0]["id"].ToString());

                                                                                    cmd3.Parameters.AddWithValue("@studid", studids);
                                                                                    cmd3.Parameters.AddWithValue("@yrsid", dt2.Rows[0]["admyears"].ToString());
                                                                                    cmd3.Parameters.AddWithValue("@clsid", dt2.Rows[0]["classd"].ToString());
                                                                                    cmd3.Parameters.AddWithValue("@divisionid", dt2.Rows[0]["division"].ToString());

                                                                                    cmd3.ExecuteNonQuery();
                                                                                    con3.Close();
                                                                                    data = "insert";
                                                                                }


                                                                            }
                                                                        }
                                                                    }
                                                                }
                                                            }

                                                        }
                                                        else
                                                        {
                                                            using (MySqlConnection con5 = new MySqlConnection(constring))
                                                            {
                                                                using (MySqlCommand cmd5 = new MySqlCommand("select * from scholershipmaster where id='" + scholids + "' and categoryid ='" + dt.Rows[0]["castcategory"].ToString() + "' and classid = '" + dt.Rows[0]["classd"].ToString() + "' and divisionid = '" + dt.Rows[0]["division"].ToString() + "' and installtype = 'secondinst'", con5))
                                                                {
                                                                    if (con5.State != ConnectionState.Open) { con5.Open(); }
                                                                    DataTable dt5 = new DataTable();
                                                                    using (MySqlDataAdapter da5 = new MySqlDataAdapter(cmd5))
                                                                    {
                                                                        da5.Fill(dt5); con5.Close();
                                                                        if (dt5.Rows.Count > 0)
                                                                        {
                                                                            double scholamt = Convert.ToDouble(dt5.Rows[0]["totalscholership"].ToString());
                                                                            using (MySqlConnection con6 = new MySqlConnection(constring))
                                                                            {
                                                                                using (MySqlCommand cmd6 = new MySqlCommand("select inst.id as installid,student_detail.* ,((SELECT package_detail.amount FROM package_detail WHERE package_detail.id = '" + dt.Rows[0]["packageid"].ToString() + "' ORDER BY package_detail.id ) - (SELECT (COALESCE(SUM(installmentstudent.paidamt),0))  FROM installmentstudent where installmentstudent.studid= inst.studid ORDER BY installmentstudent.id)) AS balatotalamts,pkg.amount from  student_detail  left  join  installmentstudent as inst on inst.studid = student_detail.studid LEFT JOIN package_detail AS pkg ON student_detail.packageid = pkg.id where student_detail.packageid ='" + dt.Rows[0]["packageid"].ToString() + "' and student_detail.studid = '" + studids + "' GROUP BY inst.id  ORDER BY inst.id DESC LIMIT 1", con6))
                                                                                {
                                                                                    if (con6.State != ConnectionState.Open) { con6.Open(); }
                                                                                    DataTable dt3 = new DataTable();
                                                                                    using (MySqlDataAdapter da3 = new MySqlDataAdapter(cmd6))
                                                                                    {
                                                                                        da3.Fill(dt3); con6.Close();
                                                                                        if (dt3.Rows.Count > 0)
                                                                                        {
                                                                                            paidamt = Convert.ToDouble(dt3.Rows[0]["balatotalamts"].ToString());
                                                                                            totalamt = scholamt + paidamt;
                                                                                            totalbalcncesamt = Convert.ToDouble(dt3.Rows[0]["amount"].ToString()) - totalamt;
                                                                                            using (MySqlConnection con3 = new MySqlConnection(constring))
                                                                                            {
                                                                                                //string query = "update installmentstudent set paidamt = @paidamt where studid = '" + scholersids + "' and id = '" + dt2.Rows[i]["installid"].ToString() + "'";
                                                                                                string query = "update   scholershipassign set secondassigndt=@assigndt,secondschid=@scholerahipid,secondinstall=@secondinstall where studid=@studid and yrsid=@yrsid and clsid=@clsid and diivisionid=@divisionid";

                                                                                                using (MySqlCommand cmd3 = new MySqlCommand(query, con3))
                                                                                                {
                                                                                                    if (con3.State != ConnectionState.Open) { con3.Open(); }



                                                                                                    cmd3.Parameters.AddWithValue("@assigndt", DateTime.UtcNow.ToString("yyyy/MM/dd"));


                                                                                                    cmd3.Parameters.AddWithValue("@secondinstall", dt1.Rows[0]["amount"].ToString());
                                                                                                    cmd3.Parameters.AddWithValue("@scholerahipid", dt1.Rows[0]["id"].ToString());

                                                                                                    cmd3.Parameters.AddWithValue("@studid", studids);
                                                                                                    cmd3.Parameters.AddWithValue("@yrsid", dt5.Rows[0]["admyears"].ToString());
                                                                                                    cmd3.Parameters.AddWithValue("@clsid", dt5.Rows[0]["classd"].ToString());
                                                                                                    cmd3.Parameters.AddWithValue("@divisionid", dt5.Rows[0]["division"].ToString());

                                                                                                    cmd3.ExecuteNonQuery();
                                                                                                    con3.Close();
                                                                                                    data = "update";
                                                                                                }


                                                                                            }
                                                                                        }
                                                                                    }
                                                                                }
                                                                            }
                                                                        }

                                                                    }
                                                                }
                                                            }

                                                        }
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        
        return data;
    }
}