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 System.Web.Script;
using System.Web.Script.Services;
using Newtonsoft.Json;
using System.Web.Script.Serialization;

public partial class module_otherincomes : 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 string fullname;
    public static string recptinitials;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    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; }
    }
    [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 incomasters ", 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]["incomes"].ToString(),
                                
                            });
                        }
                    }
                }
            }
        }
        return objDept;
    }
    public class Top
    {
        public string studids { get; set; }
        public string studname { get; set; }

        public string titles { get; set; }

        public string firstnm { get; set; }
        public string midnm { get; set; }
        public string lastnm { get; set; }

    }
    [WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public static List<Top> GetCompanyName(string pre, string years, string divionss, string classids)
    {

        DataTable dtnew = new DataTable();
        DataTable dtnew11 = new DataTable();
        DataTable dtnew12 = new DataTable();
        DataTable dtnew1 = new DataTable();
        List<Top> objDept = new List<Top>();

        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        {
            using (MySqlCommand cmdnew = new MySqlCommand("SELECT * from student_detail  where admyears = '" + years + "' and classd = '" + classids + "'  and division = '" + divionss + "' and  fname  LIKE '%" + pre + "%'   order by id limit 20", 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 Top
                            {
                                studids = dtnew.Rows[i]["studid"].ToString(),
                                titles = dtnew.Rows[i]["title"].ToString(),
                                firstnm = dtnew.Rows[i]["fname"].ToString(),
                                midnm = dtnew.Rows[i]["mname"].ToString(),

                                lastnm = dtnew.Rows[i]["lname"].ToString(),
                             

                            });
                        }
                    }
                    else
                    {
                        using (MySqlConnection con1 = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
                        {
                            if (con1.State != ConnectionState.Open) { con1.Open(); }
                            string query12 = "SELECT * from student_detail where mobile1 LIKE '%" + pre + "%' ";
                            using (MySqlDataAdapter sd12 = new MySqlDataAdapter(query12, con1))
                            {
                                sd12.Fill(dtnew11);
                                con1.Close();
                                if (dtnew11.Rows.Count > 0)
                                {

                                    for (int j = 0; j < dtnew11.Rows.Count; j++)
                                    {
                                        objDept.Add(new Top
                                        {
                                            studids = dtnew.Rows[j]["studid"].ToString(),
                                            titles = dtnew.Rows[j]["title"].ToString(),
                                            firstnm = dtnew.Rows[j]["fname"].ToString(),
                                            midnm = dtnew.Rows[j]["mname"].ToString(),

                                            lastnm = dtnew.Rows[j]["lname"].ToString(),
                                        });
                                    }
                                }
                                else
                                {
                                    using (MySqlConnection con2 = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
                                    {
                                        if (con2.State != ConnectionState.Open) { con2.Open(); }
                                        string query13 = "SELECT * from student_detail where studid LIKE '%" + pre + "%' ";
                                        using (MySqlDataAdapter sd123 = new MySqlDataAdapter(query13, con2))
                                        {
                                            sd123.Fill(dtnew12);
                                            con2.Close();
                                            if (dtnew12.Rows.Count > 0)
                                            {
                                                for (int k = 0; k < dtnew12.Rows.Count; k++)
                                                {
                                                    objDept.Add(new Top
                                                    {
                                                        studids = dtnew.Rows[k]["studid"].ToString(),
                                                        titles = dtnew.Rows[k]["title"].ToString(),
                                                        firstnm = dtnew.Rows[k]["fname"].ToString(),
                                                        midnm = dtnew.Rows[k]["mname"].ToString(),

                                                        lastnm = dtnew.Rows[k]["lname"].ToString(),
                                                    });
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
        return objDept.ToList();
    }
    [WebMethod]
    public static string[] GetAutoCompletestudent(string prefix)
    {
        List<string> customers = new List<string>();
        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        {
            using (MySqlCommand cmd = new MySqlCommand("select * from student_detail where studid  LIKE '%" + prefix + "%'", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }

                // cmd.Parameters.AddWithValue("@username", string.Format("%{0}%", username));
                using (MySqlDataAdapter danew = new MySqlDataAdapter(cmd))
                {
                    // danew.Fill(dtnew);
                    //  con.Close();
                    MySqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        customers.Add(string.Format("{0}_{1}", dr["studid"], dr["id"]));
                    }
                    con.Close();
                }
            }
            return customers.ToArray();
        }
    }
    [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> PopulateDropDownListstud(string yrs, string cls, string divs)
    {
        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 = '" + yrs + "' and classd='" + cls + "' and division = '" + divs + "'", 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
                            {
                                studids = dtnew.Rows[i]["studid"].ToString(),
                                DeptName = dtnew.Rows[i]["title"].ToString() + "." + dtnew.Rows[i]["fname"].ToString() + " " + dtnew.Rows[i]["mname"].ToString() + " " + dtnew.Rows[i]["lname"].ToString(),

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

        string _data = "";

        _data = JsonConvert.SerializeObject(BookingID);

        return _data;
    }
    [WebMethod]
    public static string SaveData(string recinitials,string recptdt, string yrs, string arrays, string subtotals, string totlaamts, string clses, string divisions, string semisters, string studnm, string paymode, string utrnos)
    {
        string data = "";int  rectsnos = 1;string recp1 = "";int recp2 = 0;

        DataTable dt = JsonConvert.DeserializeObject<DataTable>(arrays);
        if (recptdt != "" && recptdt != "00-00-0000")
        {

            date1 = DateTime.Parse(recptdt);

        }
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select incomeentry.* from  incomeentry order by id desc limit 1", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                DataTable dtnew = new DataTable();
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                {
                    da.Fill(dtnew);
                    if (dtnew.Rows.Count > 0)
                    {
                        recp1 = dtnew.Rows[0]["recptstr"].ToString();
                        if (recp1 == "")
                        {
                            recp2 = 1;
                            recptinitials = recinitials + recp2.ToString();
                            if (yrs != "" || totlaamts != "")
                            {

                                if (dt.Rows.Count > 0)
                                {
                                    for (int i = 0; i < dt.Rows.Count; i++)
                                    {


                                        using (MySqlConnection con1 = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
                                        {
                                            using (MySqlCommand cmd1 = new MySqlCommand("insert into incomeentry (incomeheads,recptnos,recptstr,yreasid,classes,divsid,semids,studid,paymodes,paydate,narrations,amount,totalamts,subtotals,utrnos) values (@incomeheads,@recptnos,@recptstr,@yreasid,@classes,@divsid,@semids,@studid,@paymodes,@paydate,@narrations,@amount,@totalamts,@subtotals,@utrnos)", con1))
                                            {
                                                if (con1.State != ConnectionState.Open) { con1.Open(); }

                                                cmd1.Parameters.AddWithValue("@incomeheads", dt.Rows[i]["incomeh"].ToString());
                                                cmd1.Parameters.AddWithValue("@recptnos", recptinitials);
                                                cmd1.Parameters.AddWithValue("@recptstr", recp2);
                                                cmd1.Parameters.AddWithValue("@narrations", dt.Rows[i]["particular"].ToString());
                                                cmd1.Parameters.AddWithValue("@amount", dt.Rows[i]["amounts"].ToString());
                                                cmd1.Parameters.AddWithValue("@yreasid", yrs);
                                                cmd1.Parameters.AddWithValue("@classes", clses);
                                                cmd1.Parameters.AddWithValue("@divsid", divisions);
                                                cmd1.Parameters.AddWithValue("@semids", semisters);
                                                cmd1.Parameters.AddWithValue("@studid", studnm);
                                                cmd1.Parameters.AddWithValue("@paydate", date1.ToString("yyyy/MM/dd"));
                                                cmd1.Parameters.AddWithValue("@paymodes", paymode);
                                                cmd1.Parameters.AddWithValue("@utrnos", utrnos);
                                                cmd1.Parameters.AddWithValue("@totalamts", totlaamts);
                                                cmd1.Parameters.AddWithValue("@subtotals", subtotals);
                                                cmd1.ExecuteNonQuery();
                                                con1.Close();
                                                data = "insert";
                                            }
                                        }

                                    }

                                }
                            }
                        }
                        else
                        {

                            recp2 = Convert.ToInt32(recp1);
                            recp2++;
                            recptinitials = recinitials + recp2.ToString();
                            if (yrs != "" || totlaamts != "")
                            {

                                if (dt.Rows.Count > 0)
                                {
                                    for (int i = 0; i < dt.Rows.Count; i++)
                                    {


                                        using (MySqlConnection con2 = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
                                        {
                                            using (MySqlCommand cmd2 = new MySqlCommand("insert into incomeentry (incomeheads,recptnos,recptstr,yreasid,classes,divsid,semids,studid,paymodes,paydate,narrations,amount,totalamts,subtotals,utrnos) values (@incomeheads,@recptnos,@recptstr,@yreasid,@classes,@divsid,@semids,@studid,@paymodes,@paydate,@narrations,@amount,@totalamts,@subtotals,@utrnos)", con2))
                                            {
                                                if (con2.State != ConnectionState.Open) { con2.Open(); }

                                                cmd2.Parameters.AddWithValue("@incomeheads", dt.Rows[i]["incomeh"].ToString());
                                                cmd2.Parameters.AddWithValue("@recptnos", recptinitials);
                                                cmd2.Parameters.AddWithValue("@recptstr", recp2);
                                                cmd2.Parameters.AddWithValue("@narrations", dt.Rows[i]["particular"].ToString());
                                                cmd2.Parameters.AddWithValue("@amount", dt.Rows[i]["amounts"].ToString());
                                                cmd2.Parameters.AddWithValue("@yreasid", yrs);
                                                cmd2.Parameters.AddWithValue("@classes", clses);
                                                cmd2.Parameters.AddWithValue("@divsid", divisions);
                                                cmd2.Parameters.AddWithValue("@semids", semisters);
                                                cmd2.Parameters.AddWithValue("@studid", studnm);
                                                cmd2.Parameters.AddWithValue("@paydate", date1.ToString("yyyy/MM/dd"));
                                                cmd2.Parameters.AddWithValue("@paymodes", paymode);
                                                cmd2.Parameters.AddWithValue("@utrnos", utrnos);
                                                cmd2.Parameters.AddWithValue("@totalamts", totlaamts);
                                                cmd2.Parameters.AddWithValue("@subtotals", subtotals);
                                                cmd2.ExecuteNonQuery();
                                                con2.Close();
                                                data = "insert";
                                            }
                                        }

                                    }

                                }
                            }
                        }
                    }
                    else
                    {
                        recp2 =  1;
                        recptinitials = recinitials + recp2.ToString();
                        if (yrs != "" || totlaamts != "")
                        {

                            if (dt.Rows.Count > 0)
                            {
                                for (int i = 0; i < dt.Rows.Count; i++)
                                {


                                    using (MySqlConnection con1 = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
                                    {
                                        using (MySqlCommand cmd1 = new MySqlCommand("insert into incomeentry (incomeheads,recptnos,recptstr,yreasid,classes,divsid,semids,studid,paymodes,paydate,narrations,amount,totalamts,subtotals,utrnos) values (@incomeheads,@recptnos,@recptstr,@yreasid,@classes,@divsid,@semids,@studid,@paymodes,@paydate,@narrations,@amount,@totalamts,@subtotals,@utrnos)", con1))
                                        {
                                            if (con1.State != ConnectionState.Open) { con1.Open(); }

                                            cmd1.Parameters.AddWithValue("@incomeheads", dt.Rows[i]["incomeh"].ToString());
                                            cmd1.Parameters.AddWithValue("@recptnos", recptinitials);
                                            cmd1.Parameters.AddWithValue("@recptstr", recp2);
                                            cmd1.Parameters.AddWithValue("@narrations", dt.Rows[i]["particular"].ToString());
                                            cmd1.Parameters.AddWithValue("@amount", dt.Rows[i]["amounts"].ToString());
                                            cmd1.Parameters.AddWithValue("@yreasid", yrs);
                                            cmd1.Parameters.AddWithValue("@classes", clses);
                                            cmd1.Parameters.AddWithValue("@divsid", divisions);
                                            cmd1.Parameters.AddWithValue("@semids", semisters);
                                            cmd1.Parameters.AddWithValue("@studid", studnm);
                                            cmd1.Parameters.AddWithValue("@paydate", date1.ToString("yyyy/MM/dd"));
                                            cmd1.Parameters.AddWithValue("@paymodes", paymode);
                                            cmd1.Parameters.AddWithValue("@utrnos", utrnos);
                                            cmd1.Parameters.AddWithValue("@totalamts", totlaamts);
                                            cmd1.Parameters.AddWithValue("@subtotals", subtotals);
                                            cmd1.ExecuteNonQuery();
                                            con1.Close();
                                            data = "insert";
                                        }
                                    }

                                }

                            }
                        }
                    }
                }
            }
        }
        return data;
    }
    [WebMethod]

    public static object GetData()
    {
        string data = " ";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select incomeentry.*,date_format(paydate,'%d-%m-%Y') as paymentdt,st.*,class_master.class as clsnm,division_master.division as divsnm,incomasters.incomes as incname from  incomeentry left join student_detail as st on incomeentry.studid = st.studid left join class_master on incomeentry.classes = class_master.id left join division_master on incomeentry.divsid = division_master.id left join incomasters on incomeentry.incomeheads = incomasters.id  group by incomeentry.recptnos", 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 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 incomeentry where recptnos='" + id + "'", con))
            {

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

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