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_feemaster : 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; }
    }
    [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 List<Department> PopulateDropDownList45(string yrsid, string divsid, 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 semistertbl where  classid = '" + clsid + "' and divsid = '" + divsid + "'", 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]["semnos"].ToString(),

                            });
                        }
                    }
                }
            }
        }
        return objDept;
    }
    [WebMethod]
    public static string SavaData(string pkgname, string yrs, string clss, string divs, string semisters, string theoryfee, string practical,string projfee, string processing, string degreefee, string amots, string taxes, string remarks,string status)
    {
        string statuss = "";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            string query = "Insert into examfeemaster(feepackname,yearsid,classid,divsid,semisteryears,theoryfee,practicalfee,projectfee,processingfee,degreefee,totalamount,remarks,taxes,today,status) values(@feepackname,@yearsid,@classid,@divsid,@semisteryears,@theoryfee,@practicalfee,@projectfee,@processingfee,@degreefee,@totalamount,@remarks,@taxes,@today,@status)";
            using (MySqlCommand cmd = new MySqlCommand(query, con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                cmd.Parameters.AddWithValue("@feepackname", pkgname);
                cmd.Parameters.AddWithValue("@yearsid", yrs);
                cmd.Parameters.AddWithValue("@classid", clss);
                cmd.Parameters.AddWithValue("@divsid", divs);
                cmd.Parameters.AddWithValue("@semisteryears", semisters);
                cmd.Parameters.AddWithValue("@theoryfee", theoryfee);
                cmd.Parameters.AddWithValue("@degreefee", degreefee);
                cmd.Parameters.AddWithValue("@practicalfee", practical);
                cmd.Parameters.AddWithValue("@projectfee", projfee);
                cmd.Parameters.AddWithValue("@processingfee", processing);

                cmd.Parameters.AddWithValue("@totalamount", amots);
                cmd.Parameters.AddWithValue("@remarks", remarks);
                cmd.Parameters.AddWithValue("@taxes", taxes); cmd.Parameters.AddWithValue("@status", status);
                cmd.Parameters.AddWithValue("@today", DateTime.UtcNow.ToString("yyyy/MM/dd"));
                cmd.ExecuteNonQuery();
                statuss = "insert";
                con.Close();
            }
        }
        return statuss;
    }

    [WebMethod]
    public static object EditData(string examid)
    {
        string data = "";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select * from examfeemaster where id='" + examid + "'", 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();
                    con.Dispose();
                    cmd.Dispose();

                }
            }
        }
        return data;
    }

    [WebMethod]
    public static object GetData()
    {
        string data = " ";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("SELECT fm.*,fm.id AS examid, cm.class,dm.division,fm.totalamount,academic_year.fromdate,academic_year.todate,semistertbl.semnos  FROM examfeemaster AS fm LEFT JOIN class_master AS cm ON fm.classid=cm.id LEFT JOIN division_master AS dm ON fm.divsid=dm.id left join academic_year on fm.yearsid = academic_year.id left join semistertbl on fm.semisteryears = semistertbl.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();
                    con.Dispose();
                    cmd.Dispose();
                }
            }
        }
        return data;
    }

    [WebMethod]
    public static object UpdateData(string exids, string pkgname, string yrs, string clss, string divs, string semisters, string theoryfee, string practical,string projfee, string processing,string degreefee,string amots,string taxes,string remarks,string status)
    {
        string data = "";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("update examfeemaster set feepackname=@feepackname,theoryfee=@theoryfee,practicalfee=@practicalfee,projectfee=@projectfee,processingfee=@processingfee,degreefee=@degreefee,totalamount=@totalamount,remarks=@remarks,taxes=@taxes,status=@status where id='" + exids + "' ", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                cmd.Parameters.AddWithValue("@feepackname", pkgname);
                cmd.Parameters.AddWithValue("@theoryfee", theoryfee);
                cmd.Parameters.AddWithValue("@practicalfee", practical);
                cmd.Parameters.AddWithValue("@projectfee", projfee);
                cmd.Parameters.AddWithValue("@processingfee", processing);
                cmd.Parameters.AddWithValue("@degreefee", degreefee);
                cmd.Parameters.AddWithValue("@totalamount", amots);

                cmd.Parameters.AddWithValue("@remarks", remarks);
                cmd.Parameters.AddWithValue("@taxes", taxes);
                cmd.Parameters.AddWithValue("@status", status);
                cmd.ExecuteNonQuery();
              
                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 examfeemaster where id='" + id + "'", con))
            {
                cmd.ExecuteNonQuery();
                using (MySqlDataAdapter dr = new MySqlDataAdapter(cmd))
                {
                    con.Close();
                    con.Dispose();
                    cmd.Dispose();
                }
            }
        }
        return data;
    }
}