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_Feesetup : 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)
    {

    }
    [WebMethod]
    public static string SavaData(string packgenme, string year, string classdetail, string divisiondet, string admissionfee, string tuitionfee, string cautionmny, string collegefee, string identycrdfee, string insurance, string unifees, string eligibilityfee, string busfee, string exminfee, string enrollfee, string lockerfee, string gmespofee, string boardfee, string exactivityfee, string developfee, string amount, string remark, string tax)
    {
        string status = "1";
        Random rand = new Random();
        string pkgid = rand.Next(0, 10000).ToString(); 
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            string query = "Insert into package_detail(packgenme,packageids,year,classdetail,divisiondet,admissionfee,tuitionfee,cautionmny,collegefee,identycrdfee,insurance,unifees,eligibilityfee,busfee,exminfee,enrollfee,lockerfee,gmespofee,boardfee,exactivityfee,developfee,amount,remark,tax,createdate) values(@packgenme,@packageids,@year,@classdetail,@divisiondet,@admissionfee,@tuitionfee,@cautionmny,@collegefee,@identycrdfee,@insurance,@unifees,@eligibilityfee,@busfee,@exminfee,@enrollfee,@lockerfee,@gmespofee,@boardfee,@exactivityfee,@developfee,@amount,@remark,@tax,@createdate)";
            using (MySqlCommand cmd = new MySqlCommand(query, con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                cmd.Parameters.AddWithValue("@packgenme", packgenme);
                cmd.Parameters.AddWithValue("@packageids", pkgid);
                cmd.Parameters.AddWithValue("@year", year);
                cmd.Parameters.AddWithValue("@classdetail", classdetail);
                cmd.Parameters.AddWithValue("@divisiondet", divisiondet);
                cmd.Parameters.AddWithValue("@admissionfee", admissionfee);
                cmd.Parameters.AddWithValue("@tuitionfee", tuitionfee);
                cmd.Parameters.AddWithValue("@cautionmny", cautionmny);
                cmd.Parameters.AddWithValue("@collegefee", collegefee);
                cmd.Parameters.AddWithValue("@identycrdfee", identycrdfee);
                cmd.Parameters.AddWithValue("@insurance", insurance);
                cmd.Parameters.AddWithValue("@unifees", unifees);
                cmd.Parameters.AddWithValue("@eligibilityfee", eligibilityfee);
                cmd.Parameters.AddWithValue("@busfee", busfee);
                cmd.Parameters.AddWithValue("@exminfee", exminfee);
                cmd.Parameters.AddWithValue("@enrollfee", enrollfee);
                cmd.Parameters.AddWithValue("@lockerfee", lockerfee);
                cmd.Parameters.AddWithValue("@gmespofee", gmespofee);
                cmd.Parameters.AddWithValue("@boardfee", boardfee);
                cmd.Parameters.AddWithValue("@exactivityfee", exactivityfee);
                cmd.Parameters.AddWithValue("@developfee", developfee);
                cmd.Parameters.AddWithValue("@amount", amount);
                cmd.Parameters.AddWithValue("@remark", remark);
                cmd.Parameters.AddWithValue("@createdate", DateTime.UtcNow.ToString("yyyy/MM/dd"));
                cmd.Parameters.AddWithValue("@tax", tax);
               

                cmd.ExecuteNonQuery();
                cmd.Connection = con;
                con.Close();
            }
        }
        return status;
    }
    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> 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 object EditData(string id)
    {
        string data = "";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select * from package_detail where id='" + id + "'", 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 UpdateData(string id, string packgenme, string year, string classdetail, string divisiondet, string admissionfee, string tuitionfee, string cautionmny, string collegefee, string identycrdfee, string insurance, string unifees, string eligibilityfee, string busfee, string exminfee, string enrollfee, string lockerfee, string gmespofee, string boardfee, string exactivityfee, string developfee, string amount, string remark, string tax)
    {
        string data = "";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("update package_detail set packgenme=@packgenme,year=@year,classdetail=@classdetail,divisiondet=@divisiondet,admissionfee=@admissionfee,tuitionfee=@tuitionfee,cautionmny=@cautionmny,identycrdfee=@identycrdfee,insurance=@insurance,unifees=@unifees,eligibilityfee=@eligibilityfee,busfee=@busfee,exminfee=@exminfee,enrollfee=@enrollfee,lockerfee=@lockerfee,gmespofee=@gmespofee,boardfee=@boardfee,exactivityfee=@exactivityfee,developfee=@developfee,amount=@amount,remark=@remark,tax=@tax where id='" + id + "' ", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                cmd.Parameters.AddWithValue("@packgenme", packgenme);
                cmd.Parameters.AddWithValue("@year", year);
                cmd.Parameters.AddWithValue("@classdetail", classdetail);
                cmd.Parameters.AddWithValue("@divisiondet", divisiondet);
                cmd.Parameters.AddWithValue("@admissionfee", admissionfee);
                cmd.Parameters.AddWithValue("@tuitionfee", tuitionfee);
                cmd.Parameters.AddWithValue("@cautionmny", cautionmny);
                cmd.Parameters.AddWithValue("@collegefee", collegefee);
                cmd.Parameters.AddWithValue("@identycrdfee", identycrdfee);
                cmd.Parameters.AddWithValue("@insurance", insurance);
                cmd.Parameters.AddWithValue("@unifees", unifees);
                cmd.Parameters.AddWithValue("@eligibilityfee", eligibilityfee);
                cmd.Parameters.AddWithValue("@busfee", busfee);
                cmd.Parameters.AddWithValue("@id", id);
                cmd.Parameters.AddWithValue("@exminfee", exminfee);
                cmd.Parameters.AddWithValue("@enrollfee", enrollfee);
                cmd.Parameters.AddWithValue("@lockerfee", lockerfee);
                cmd.Parameters.AddWithValue("@gmespofee", gmespofee);
                cmd.Parameters.AddWithValue("@boardfee", boardfee);
                cmd.Parameters.AddWithValue("@exactivityfee", exactivityfee);
                cmd.Parameters.AddWithValue("@developfee", developfee);
                cmd.Parameters.AddWithValue("@amount", amount);
                cmd.Parameters.AddWithValue("@remark", remark);
                cmd.Parameters.AddWithValue("@tax", tax);
                cmd.ExecuteNonQuery();
                cmd.Connection = con;
                con.Close();
                con.Dispose();
                cmd.Dispose();
            }
        }
        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 package_detail where id='" + id + "'", con))
            {

                cmd.ExecuteNonQuery();
                using (MySqlDataAdapter dr = new MySqlDataAdapter(cmd))
                {
                    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 package_detail.*,class_master.class as clsname,division_master.division as divsname,academic_year.fromdate,academic_year.todate,date_format(package_detail.createdate,'%d-%m-%Y') as pkgdate from  package_detail left join class_master on package_detail.classdetail = class_master.id left join division_master on package_detail.divisiondet = division_master.id left join academic_year on package_detail.year = academic_year.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 string Getdata12(string clasr, string division)
    {
        string _data = "";
        {

            using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
            {

                if (con.State != ConnectionState.Open) { con.Open(); }
                using (MySqlCommand cmdpack = new MySqlCommand("SELECT   wo.*,sd.fname,sd.mname,sd.lname,sd.classd  FROM weekly_off AS wo LEFT JOIN student_detail AS sd ON wo.id=sd.id WHERE sd.classd='" + clasr + "' AND sd.division='" + division + "' ", con))
                {
                    using (MySqlDataAdapter sda29 = new MySqlDataAdapter(cmdpack))
                    {
                        DataTable dt29 = new DataTable();
                        sda29.Fill(dt29);
                        con.Close();

                        if (dt29.Rows.Count > 0)
                        {
                            _data = JsonConvert.SerializeObject(dt29);
                            return _data;
                        }

                    }
                }
            }

        }

        return _data;
    }
   
}