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_academicyearr : 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 BookingID;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    [WebMethod]
    public static string savedata(string year, string fromdate, string todate)
    {
        string data = "1";
      
        if (fromdate != "")
        {
            date1 = DateTime.Parse(fromdate);
        }
        if (todate != "")
        {
            date2 = DateTime.Parse(todate);
        }
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            string query = "Insert into academic_year(year,yearcode,fromdate,todate) values(@year,@yearcode,@fromdate,@todate)";
            using (MySqlCommand cmd = new MySqlCommand(query, con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                Random rr = new Random();
                BookingID = rr.Next(0, 999).ToString();
                string yearcode = "YR" + BookingID;
                cmd.Parameters.AddWithValue("@year", year);
                cmd.Parameters.AddWithValue("@yearcode", yearcode);
                cmd.Parameters.AddWithValue("@fromdate", date1.ToString("yyyy/MM/dd"));
                cmd.Parameters.AddWithValue("@todate", date2.ToString("yyyy/MM/dd"));

                cmd.ExecuteNonQuery();
                con.Close();
                con.Dispose();
            }
        }
        return data;
    }
    [WebMethod]

    public static object GetData()
    {
        string data = " ";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select *,date_format(fromdate,'%d-%m-%Y') as acdyear,date_format(todate,'%d-%m-%Y') as acdyear1 from  academic_year", 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 EditData(string id)
    {
        string data = "";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select academic_year.*,date_format(academic_year.fromdate,'%d-%m-%Y') as acdodb,date_format(academic_year.todate,'%d-%m-%Y') as acdodb1 from academic_year 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();
                  

                }
            }
        }
        return data;
    }
    [WebMethod]
    public static object UpdateData(string acdid, string year, string fromdate, string todate, string yrcode)
    {
        string data = "";
        if (fromdate != "")
        {
            date1 = DateTime.Parse(fromdate);
        }
        if (todate != "")
        {
            date2 = DateTime.Parse(todate);
        }
        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
        {
            using (MySqlCommand cmd = new MySqlCommand("update academic_year set year= @year,fromdate= @fromdate,todate= @todate  where id=@id and yearcode = @yearcode", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                cmd.Parameters.AddWithValue("@year", year);
                cmd.Parameters.AddWithValue("@id", acdid);
                cmd.Parameters.AddWithValue("@fromdate", date1.ToString("yyyy/MM/dd"));
                cmd.Parameters.AddWithValue("@todate", date2.ToString("yyyy/MM/dd"));
                cmd.Parameters.AddWithValue("@yearcode", yrcode);
                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 academic_year where id='" + id + "'", con))
            {

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

                }
            }
        }
        return data;
    }
}