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_certificates : System.Web.UI.Page
{
    public static DateTime date1;
    public static DateTime date2;
    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 GetEmpData()
    {
       
        string data = "";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select COUNT(id) AS recptno from add_certificates where certtype ='bonafidecertificate' order by id desc limit 1", 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;
    }
        
    public class Department
    {
        public int DeptId { get; set; }
        public string DeptName { get; set; }
        public string studids { get; set; }
        public string DeptIds { get; set; }
        public string Initial { get; set; }
        public string year1 { get; set; }  public string fname { get; set; }  public string mname { get; set; }  public string lastname { 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> PopulateDropDownList15()
    {
        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 List<Department> PopulateDropDownList7(string yrsids, string clids, string divsid)
    {
        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 student_detail where admyears = '" + yrsids + "' and classd = '" + clids + "' and division = '" + 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
                            {
                                DeptName = dtnew.Rows[i]["id"].ToString(),
                                fname = dtnew.Rows[i]["fname"].ToString(),
                                mname = dtnew.Rows[i]["mname"].ToString(),
                                lastname = dtnew.Rows[i]["lname"].ToString(),

                            });
                        }
                    }
                }
            }
        }
        return objDept;
    }
    [WebMethod]
    public static string SaveData(string recptnos, string issudt, string yrs, string clses, string divisions, string studnm, string fathersnm, string certype, string certimes,string nationality, string birplace, string attends, string progresss, string conducts, string leaves, string curclass, string curyear, string reasons, string remarks)
    {
        string data = "";

        if (issudt != "" && issudt != "00-00-0000")
        {

            date1 = DateTime.Parse(issudt);

        }
        if (leaves != "")
        {
            date2 = DateTime.Parse(leaves);
        }
        if (recptnos != "" || yrs != "" || clses != "")
        {

           

                    using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
                    {
                        using (MySqlCommand cmd = new MySqlCommand("insert into add_certificates (receptnos,yrsid,clsids,divsid,studids,fathernm,nationality,birthplace,lastcolleges,progress,conduct,leavingdate,currentclass,currentyear,reasonleaving,remark,issuedate,today,certtype,certtimes,status) values (@receptnos,@yrsid,@clsids,@divsid,@studids,@fathernm,@nationality,@birthplace,@lastcolleges,@progress,@conduct,@leavingdate,@currentclass,@currentyear,@reasonleaving,@remark,@issuedate,@today,@certtype,@certtimes,@status)", con))
                        {
                            if (con.State != ConnectionState.Open) { con.Open(); }

                            cmd.Parameters.AddWithValue("@receptnos", recptnos);
                            cmd.Parameters.AddWithValue("@yrsid", yrs);
                            cmd.Parameters.AddWithValue("@clsids", clses);
                            cmd.Parameters.AddWithValue("@divsid", divisions);
                            cmd.Parameters.AddWithValue("@fathernm", fathersnm);
                            cmd.Parameters.AddWithValue("@nationality", nationality);
                            cmd.Parameters.AddWithValue("@birthplace", birplace);
                            cmd.Parameters.AddWithValue("@lastcolleges", attends);
                            cmd.Parameters.AddWithValue("@progress", progresss);
                            cmd.Parameters.AddWithValue("@conduct", conducts);
                            cmd.Parameters.AddWithValue("@leavingdate", date2.ToString("yyyy/MM/dd"));
                            cmd.Parameters.AddWithValue("@currentclass", curclass);
                            cmd.Parameters.AddWithValue("@currentyear", curyear);
                            cmd.Parameters.AddWithValue("@reasonleaving", reasons);
                            cmd.Parameters.AddWithValue("@remark", remarks);
                            cmd.Parameters.AddWithValue("@studids", studnm);
                            cmd.Parameters.AddWithValue("@issuedate", date1.ToString("yyyy/MM/dd")); 
                            cmd.Parameters.AddWithValue("@today", DateTime.UtcNow.ToString("yyyy/MM/dd"));
                            cmd.Parameters.AddWithValue("@certtype", certype);
                            cmd.Parameters.AddWithValue("@certtimes", certimes);
                            cmd.Parameters.AddWithValue("@status", "Pending For Approval");
                            cmd.ExecuteNonQuery();
                            data = "insert";
                        }
                    }

                }
           
        return data;
    }
    [WebMethod]

    public static object GetData()
    {
        string data = " ";
        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        {
            using (MySqlCommand cmd = new MySqlCommand("select add_certificates.*,date_format(issuedate,'%d-%m-%Y') as paymentdt from  add_certificates", 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 Deletedata(string ids)
    {
        string _data = ""; int dels = 0;
        {

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

                if (con.State != ConnectionState.Open) { con.Open(); }
                using (MySqlCommand cmdpack = new MySqlCommand("delete from add_certificates where id = '" + ids + "'", con))
                {
                    dels = cmdpack.ExecuteNonQuery();
                    con.Close();
                    _data = dels.ToString();
                }
            }

        }

        return _data;
    }
}