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_expensessetup : System.Web.UI.Page
{
    public static string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    public static MySqlConnection con = new MySqlConnection(constring);
    public static  DateTime date1;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    [WebMethod]
    public static string savedata(string narrationss, string expnm, string amount, string reptsnos, string years, string clsids, string paydate, string paymode, string array, string utrnos)
    {
        string data = "1";
        DataTable dt = JsonConvert.DeserializeObject<DataTable>(array);
        if (paydate != "")
        {
            date1 = DateTime.Parse(paydate);
        }
        if (dt.Rows.Count > 0)
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                using (MySqlConnection con = new MySqlConnection(constring))
                {
                    string query = "Insert into expenses_setup(exepnses,paydate,amounts,pymodes,rectpsnos,yrsid,clsid,narrations,today,utrnos) values(@exepnses,@paydate,@amounts,@pymodes,@rectpsnos,@yrsid,@clsid,@narrations,@today,@utrnos)";
                    using (MySqlCommand cmd = new MySqlCommand(query, con))
                    {
                        if (con.State != ConnectionState.Open) { con.Open(); }
                        cmd.Parameters.AddWithValue("@exepnses", dt.Rows[i]["exepensenm"].ToString());
                        cmd.Parameters.AddWithValue("@paydate", date1.ToString("yyyy/MM/dd"));
                        cmd.Parameters.AddWithValue("@amounts", dt.Rows[i]["amounts"].ToString());
                        cmd.Parameters.AddWithValue("@rectpsnos", reptsnos);
                        cmd.Parameters.AddWithValue("@yrsid", years);
                        cmd.Parameters.AddWithValue("@clsid", clsids);
                        cmd.Parameters.AddWithValue("@pymodes", paymode);
                        cmd.Parameters.AddWithValue("@utrnos", utrnos);
                        cmd.Parameters.AddWithValue("@narrations", dt.Rows[i]["particular"].ToString());
                        cmd.Parameters.AddWithValue("@today", DateTime.UtcNow.ToString("yyyy/MM/dd"));

                        cmd.ExecuteNonQuery();
                        con.Close();
                       
                    }
                }
            }
        }
        return data;
    }

    [WebMethod]
    public static object EditData(string id)
    {
        string data = "";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select * from expenses_setup 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 GetData()
    {
        string data = " ";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select expenses_setup.*,date_format(expenses_setup.paydate,'%d-%m-%Y') as exedate,sum(expenses_setup.amounts) as totalamounts,expensesasters.expenses as expname from  expenses_setup left join expensesasters on expenses_setup.exepnses = expensesasters.id group by rectpsnos", 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 id, string name, string subjectcode, string subjecttype, string creditpoint)
    {
        string data = "";
        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
        {
            using (MySqlCommand cmd = new MySqlCommand("update expenses_setup set name= @name,subjectcode= @subjectcode,subjecttype= @subjecttype,creditpoint=@creditpoint  where id=@id", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                cmd.Parameters.AddWithValue("@name", name);
                cmd.Parameters.AddWithValue("@subjectcode", subjectcode);
                cmd.Parameters.AddWithValue("@id", id);
                cmd.Parameters.AddWithValue("@subjecttype", subjecttype);
                cmd.Parameters.AddWithValue("@creditpoint", creditpoint);
                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 expenses_setup where rectpsnos='" + id + "'", con))
            {

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

                }
            }
        }
        return data;
    }
    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> PopulateDropDownList56()
    {
        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> 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 expensesasters ", 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]["expenses"].ToString(),
                             
                            });
                        }
                    }
                }
            }
        }
        return objDept;
    }
    [WebMethod]
    public static string GetEmpData()
    {
        string BookingID = "";
        //Random rr = new Random();
        //BookingID = rr.Next(0, 999999).ToString();

        string _data = "";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select count(*) as expcounts from  expenses_setup", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                DataTable dt = new DataTable();
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                {
                    da.Fill(dt);
                    if (dt.Rows.Count > 0)
                    {
                        _data = JsonConvert.SerializeObject(dt);
                    }
                    con.Close();
                 
                }
            }
        }
       // _data = JsonConvert.SerializeObject(BookingID);

        return _data;
    }
}