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_accountreport : 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 pkgid;
    public static int recptsno;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    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 Getdata(string clasr, string division, string acdyear)
    {
        string data = " ";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.studid as studids,class_master.class as clsname,division_master.division as divsname,package_detail.*,package_detail.id as pkgid,scholershipassign.firstinstall,scholershipassign.secondinstall,(SELECT (COALESCE(SUM(inst.paidamt),0)) FROM installmentstudent AS inst   where inst.studid = student_detail.studid and inst.classids ='" + clasr + "' and inst.divisionsids='" + division + "' and inst.yearsids = '" + acdyear + "' and inst.packageids != '') AS totalpaidamt,(select (COALESCE(SUM(inst.paidamt),0)) FROM installmentstudent AS inst   where inst.studid = student_detail.studid and inst.packageids = '' and inst.classids ='" + clasr + "' and inst.divisionsids='" + division + "' and inst.yearsids = '" + acdyear + "' ) AS scholerhipstudent,(select inst.paidamt FROM installmentstudent AS inst   where inst.studid = student_detail.studid and inst.packageids = '' and inst.classids ='" + clasr + "' and inst.divisionsids='" + division + "' and inst.yearsids = '" + acdyear + "' and scholershipstatus = 'Against Scholership') AS scholerhipamount,class_master.id as clsids,division_master.id as divsid,student_detail.admyears as admyrs,date_format(student_detail.today,'%d-%m-%Y') as admdate,(SELECT DISTINCT  p1.tuitionfee FROM installmentstudent AS inst  inner  join package_detail AS p1 ON inst.packageids = p1.id  where inst.studid = student_detail.studid AND inst.packageids = student_detail.packageid and  inst.classids ='" + clasr + "' and inst.divisionsids='" + division + "' and inst.yearsids = '" + acdyear + "') AS totaltutionfee,(SELECT DISTINCT  p1.developfee FROM installmentstudent AS inst  inner  join package_detail AS p1 ON inst.packageids = p1.id  where inst.studid = student_detail.studid AND inst.packageids = student_detail.packageid and inst.classids ='" + clasr + "' and inst.divisionsids='" + division + "' and inst.yearsids = '" + acdyear + "') AS totaldevelopfee  from  student_detail left join class_master on student_detail.classd = class_master.id left join division_master on student_detail.division = division_master.id left join package_detail on student_detail.packageid = package_detail.id left join scholershipassign on student_detail.studid = scholershipassign.studid where student_detail.classd ='" + clasr + "' and student_detail.division='" + division + "' and student_detail.admyears = '" + acdyear + "' and student_detail.canceladmission ='0'", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                DataSet ds = new DataSet();
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                {
                    da.Fill(ds); con.Close();
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        data = JsonConvert.SerializeObject(ds.Tables[0]);
                    }
               
                   
                }
            }
        }
        return data;
    }
    [WebMethod]
    public static string Export(string clasr, string division, string acdyear)
    {
        string _data = "";
        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        {
            using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.studid as studids,class_master.class as clsname,division_master.division as divsname,package_detail.*,package_detail.id as pkgid,scholershipassign.firstinstall,scholershipassign.secondinstall,(SELECT (COALESCE(SUM(inst.paidamt),0)) FROM installmentstudent AS inst   where inst.studid = student_detail.studid and inst.classids ='" + clasr + "' and inst.divisionsids='" + division + "' and inst.yearsids = '" + acdyear + "' ) AS totalpaidamt,(select (COALESCE(SUM(inst.paidamt),0)) FROM installmentstudent AS inst   where inst.studid = student_detail.studid and inst.paymode = 'obc scholership' and inst.classids ='" + clasr + "' and inst.divisionsids='" + division + "' and inst.yearsids = '" + acdyear + "' ) AS scholerhipstudent,class_master.id as clsids,division_master.id as divsid,student_detail.admyears as admyrs,date_format(student_detail.today,'%d-%m-%Y') as admdate,(SELECT DISTINCT  p1.tuitionfee FROM installmentstudent AS inst  inner  join package_detail AS p1 ON inst.packageids = p1.id  where inst.studid = student_detail.studid AND inst.packageids = student_detail.packageid and  inst.classids ='" + clasr + "' and inst.divisionsids='" + division + "' and inst.yearsids = '" + acdyear + "') AS totaltutionfee,(SELECT DISTINCT  p1.developfee FROM installmentstudent AS inst  inner  join package_detail AS p1 ON inst.packageids = p1.id  where inst.studid = student_detail.studid AND inst.packageids = student_detail.packageid and inst.classids ='" + clasr + "' and inst.divisionsids='" + division + "' and inst.yearsids = '" + acdyear + "') AS totaldevelopfee  from  student_detail left join class_master on student_detail.classd = class_master.id left join division_master on student_detail.division = division_master.id left join package_detail on student_detail.packageid = package_detail.id left join scholershipassign on student_detail.studid = scholershipassign.studid where student_detail.classd ='" + clasr + "' and student_detail.division='" + division + "' and student_detail.admyears = '" + acdyear + "' and student_detail.canceladmission ='0'", con))
            {
                DataSet ds = new DataSet();
                if (con.State != ConnectionState.Open) { con.Open(); }
                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;
    }
    [WebMethod]

    public static object GetDatafeedetails(string pkgids, string stids, string years, string classs, string divisions)
    {
        string data = " ";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select * from installmentstudent  where installmentstudent.studid ='" + stids + "' and installmentstudent.packageids = '" + pkgids + "' and installmentstudent.yearsids = '" + years + "' and installmentstudent.classids = '" + classs + "' and installmentstudent.divisionsids = '" + divisions + "' and installmentstudent.packageids = '" + pkgids + "'", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                DataTable ds = new DataTable();
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                {
                    da.Fill(ds); con.Close();
                    if (ds.Rows.Count > 0)
                    {
                        using (MySqlConnection con1 = new MySqlConnection(constring))
                        {
                            using (MySqlCommand cmd1 = new MySqlCommand("select student_detail.*,student_detail.studid as studids,inst.*,inst.balanceamt AS remainsamt,pkg.*,(SELECT (COALESCE(SUM(installmentstudent.paidamt),0)) FROM installmentstudent   where installmentstudent.studid = inst.studid and installmentstudent.packageids !='') AS totalpaidamt,((SELECT package_detail.admissionfee FROM package_detail WHERE package_detail.id = '" + pkgids + "' ORDER BY package_detail.id ) - (SELECT (COALESCE(SUM(installmentstudent.baladmfees),0))  from  installmentstudent where installmentstudent.studid =  inst.studid AND installmentstudent.yearsids = inst.yearsids AND installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids AND installmentstudent.packageids = '" + pkgids + "' ORDER BY installmentstudent.id)) AS balanceadm,((SELECT package_detail.tuitionfee FROM package_detail WHERE package_detail.id = '" + pkgids + "' ORDER BY package_detail.id ) - (SELECT (COALESCE(SUM(installmentstudent.baltutionfees),0)) from  installmentstudent where installmentstudent.studid= inst.studid AND installmentstudent.yearsids = inst.yearsids AND installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids AND installmentstudent.packageids = '" + pkgids + "' ORDER BY installmentstudent.id )) AS baltution,((SELECT package_detail.cautionmny FROM package_detail WHERE package_detail.id = '" + pkgids + "' ORDER BY package_detail.id) - (SELECT (COALESCE(SUM(installmentstudent.balcautinmon),0))  FROM  installmentstudent where installmentstudent.studid= inst.studid AND installmentstudent.yearsids = inst.yearsids AND installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids AND installmentstudent.packageids = '" + pkgids + "' ORDER BY installmentstudent.id)) AS balcaution,((SELECT package_detail.collegefee FROM package_detail WHERE package_detail.id = '" + pkgids + "' ORDER BY package_detail.id ) - (SELECT (COALESCE(SUM(installmentstudent.balcollegefess),0))  FROM  installmentstudent where installmentstudent.studid= inst.studid AND installmentstudent.yearsids = inst.yearsids AND installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids AND installmentstudent.packageids = '" + pkgids + "' ORDER BY installmentstudent.id)) AS balcollege,((SELECT  package_detail.identycrdfee FROM package_detail WHERE package_detail.id = '" + pkgids + "' ORDER BY package_detail.id ) - (SELECT (COALESCE(SUM(installmentstudent.balidentitys),0))  FROM installmentstudent where installmentstudent.studid= inst.studid AND installmentstudent.yearsids = inst.yearsids AND installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids AND installmentstudent.packageids = '" + pkgids + "' ORDER BY installmentstudent.id)) AS balstudids,((SELECT  package_detail.insurance FROM package_detail WHERE package_detail.id = '" + pkgids + "' ORDER BY package_detail.id DESC LIMIT 1) - (SELECT (COALESCE(SUM(installmentstudent.balinsurance),0))  FROM  installmentstudent where installmentstudent.studid= inst.studid AND installmentstudent.yearsids = inst.yearsids AND installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids AND installmentstudent.packageids = '" + pkgids + "' ORDER BY installmentstudent.id )) AS balinsuranes,((SELECT package_detail.unifees FROM package_detail WHERE package_detail.id = '" + pkgids + "' ORDER BY package_detail.id ) - (SELECT (COALESCE(SUM(installmentstudent.universesfees),0))  FROM installmentstudent where installmentstudent.studid= inst.studid AND installmentstudent.yearsids = inst.yearsids AND installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids AND installmentstudent.packageids = '" + pkgids + "' ORDER BY installmentstudent.id  )) AS baluniversity,((SELECT package_detail.eligibilityfee FROM package_detail WHERE package_detail.id = '" + pkgids + "' ORDER BY package_detail.id ) - (SELECT (COALESCE(SUM(installmentstudent.baleligible),0))  FROM  installmentstudent where installmentstudent.studid= inst.studid AND installmentstudent.yearsids = inst.yearsids AND installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids AND installmentstudent.packageids = '" + pkgids + "' ORDER BY installmentstudent.id)) AS baleligibles,((SELECT package_detail.busfee FROM package_detail WHERE package_detail.id = '" + pkgids + "' ORDER BY package_detail.id ) - (SELECT (COALESCE(SUM(installmentstudent.balbusfees),0))  FROM installmentstudent where installmentstudent.studid= inst.studid AND installmentstudent.yearsids = inst.yearsids AND installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids AND installmentstudent.packageids = '" + pkgids + "' ORDER BY installmentstudent.id)) AS balbusfess,((SELECT package_detail.exminfee FROM package_detail WHERE package_detail.id = '" + pkgids + "' ORDER BY package_detail.id ) - (SELECT (COALESCE(SUM(installmentstudent.balexamfess),0))  FROM  installmentstudent where installmentstudent.studid= inst.studid AND installmentstudent.yearsids = inst.yearsids AND installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids AND installmentstudent.packageids = '" + pkgids + "' ORDER BY installmentstudent.id)) AS balexamfee,((SELECT package_detail.enrollfee FROM package_detail WHERE package_detail.id = '" + pkgids + "' ORDER BY package_detail.id ) - (SELECT (COALESCE(SUM(installmentstudent.balenrollment),0))  FROM  installmentstudent where installmentstudent.studid= inst.studid AND installmentstudent.yearsids = inst.yearsids AND installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids AND installmentstudent.packageids = '" + pkgids + "' ORDER BY installmentstudent.id)) AS balenrollfee,((SELECT package_detail.lockerfee FROM package_detail WHERE package_detail.id = '" + pkgids + "' ORDER BY package_detail.id) - (SELECT (COALESCE(SUM(installmentstudent.lockerfees),0))   FROM  installmentstudent where installmentstudent.studid= inst.studid AND installmentstudent.yearsids = inst.yearsids AND installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids AND installmentstudent.packageids = '" + pkgids + "' ORDER BY installmentstudent.id)) AS ballockerfee,((SELECT package_detail.gmespofee FROM package_detail WHERE package_detail.id = '" + pkgids + "' ORDER BY package_detail.id ) - (SELECT (COALESCE(SUM(installmentstudent.balgamesfees),0))  FROM installmentstudent where installmentstudent.studid= inst.studid AND installmentstudent.yearsids = inst.yearsids AND installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids AND installmentstudent.packageids = '" + pkgids + "' ORDER BY installmentstudent.id)) AS balgamesfee,((SELECT package_detail.boardfee FROM package_detail WHERE package_detail.id = '" + pkgids + "' ORDER BY package_detail.id ) - (SELECT (COALESCE(SUM(installmentstudent.balboardfees),0))  FROM  installmentstudent where installmentstudent.studid= inst.studid AND installmentstudent.yearsids = inst.yearsids AND installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids AND installmentstudent.packageids = '" + pkgids + "' ORDER BY installmentstudent.id)) AS balnboard,((SELECT package_detail.exactivityfee FROM package_detail WHERE package_detail.id = '" + pkgids + "' ORDER BY package_detail.id ) - (SELECT (COALESCE(SUM(installmentstudent.balextraactivity),0))  FROM  installmentstudent where installmentstudent.studid= inst.studid AND installmentstudent.yearsids = inst.yearsids AND installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids AND installmentstudent.packageids = '" + pkgids + "' ORDER BY installmentstudent.id)) AS balextra,((SELECT package_detail.developfee FROM package_detail WHERE package_detail.id = '" + pkgids + "' ORDER BY package_detail.id ) - (SELECT (COALESCE(SUM(installmentstudent.baldevelopemntfee),0))  FROM installmentstudent where installmentstudent.studid= inst.studid and installmentstudent.paymode != 'obc scholership' AND installmentstudent.yearsids = inst.yearsids AND installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids AND installmentstudent.packageids = '" + pkgids + "' ORDER BY installmentstudent.id)) AS baldevelope,((SELECT package_detail.amount FROM package_detail WHERE package_detail.id = '" + pkgids + "'  ORDER BY package_detail.id ) - (SELECT (COALESCE(SUM(installmentstudent.paidamt),0))  FROM installmentstudent where installmentstudent.studid= inst.studid AND installmentstudent.yearsids = inst.yearsids AND installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids AND installmentstudent.packageids = '" + pkgids + "' ORDER BY installmentstudent.id)) AS balatotalamts,(SELECT (COALESCE(SUM(installmentstudent.baldevelopemntfee),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid AND    installmentstudent.packageids = '' AND installmentstudent.yearsids = inst.yearsids AND installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids AND installmentstudent.packageids = '" + pkgids + "') AS scholbaldevelop,(SELECT (COALESCE(SUM(installmentstudent.baltutionfees),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid AND  installmentstudent.packageids = '' AND installmentstudent.yearsids = inst.yearsids AND installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids AND installmentstudent.packageids = '" + pkgids + "') AS scholbaltutions from  student_detail  left  join  installmentstudent as inst on inst.studid = student_detail.studid AND inst.yearsids = inst.yearsids AND inst.classids = inst.classids and inst.divisionsids = inst.divisionsids and inst.packageids = student_detail.packageid LEFT JOIN package_detail AS pkg ON student_detail.packageid = pkg.id where student_detail.packageid ='" + pkgids + "' and student_detail.studid = '" + stids + "' and student_detail.admyears = '" + years + "' and student_detail.classd = '" + classs + "' and student_detail.division = '" + divisions + "' GROUP BY inst.studid,inst.yearsids,inst.classids,inst.divisionsids,inst.packageids  ORDER BY inst.id DESC LIMIT 1", con1))
                            {
                                if (con1.State != ConnectionState.Open) { con1.Open(); }
                                DataTable ds1 = new DataTable();
                                using (MySqlDataAdapter da1 = new MySqlDataAdapter(cmd1))
                                {
                                    da1.Fill(ds1); con1.Close();
                                    if (ds1.Rows.Count > 0)
                                    {
                                        data = JsonConvert.SerializeObject(ds1);
                                    }


                                }
                            }
                        }
                    }
                    else
                    {
                        using (MySqlConnection con2 = new MySqlConnection(constring))
                        {
                            using (MySqlCommand cmd2 = new MySqlCommand("select student_detail.*,student_detail.studid as studids,package_detail.*,package_detail.id as pkgid,package_detail.admissionfee as balanceadm,package_detail.tuitionfee as baltution,package_detail.cautionmny as balcaution,package_detail.collegefee as balcollege,package_detail.identycrdfee as balstudids,package_detail.insurance as balinsuranes,package_detail.unifees as baluniversity,package_detail.eligibilityfee as baleligibles,package_detail.busfee as balbusfess,package_detail.exminfee as balexamfee,package_detail.enrollfee as balenrollfee,package_detail.lockerfee as ballockerfee,package_detail.gmespofee as  balgamesfee,package_detail.boardfee as balnboard,package_detail.exactivityfee as balextra,package_detail.developfee as baldevelope,package_detail.amount AS balatotalamts,(SELECT (COALESCE(SUM(installmentstudent.baldevelopemntfee),0)) FROM installmentstudent WHERE installmentstudent.studid = '" + stids + "' AND    installmentstudent.packageids = '') AS scholbaldevelop,(SELECT (COALESCE(SUM(installmentstudent.baltutionfees),0)) FROM installmentstudent WHERE installmentstudent.studid = '" + stids + "' AND   installmentstudent.packageids = '' and installmentstudent.yearsids = '" + years + "' and installmentstudent.classids = '" + classs + "' and installmentstudent.divisionsids = '" + divisions + "') AS scholbaltutions from  student_detail left join package_detail on student_detail.packageid = package_detail.id  where student_detail.packageid ='" + pkgids + "' and student_detail.studid = '" + stids + "' and student_detail.admyears = '" + years + "' and student_detail.classd = '" + classs + "' and student_detail.division = '"+ divisions +"'", con2))
                            {
                                if (con2.State != ConnectionState.Open) { con2.Open(); }
                                DataTable ds2 = new DataTable();
                                using (MySqlDataAdapter da2 = new MySqlDataAdapter(cmd2))
                                {
                                    da2.Fill(ds2); con2.Close();
                                    if (ds2.Rows.Count > 0)
                                    {
                                        data = JsonConvert.SerializeObject(ds2);
                                    }


                                }
                            }
                        }
                    }
                }
            }
        }
        return data;
    }
    [WebMethod]
    public static string SavaData(string adm, string tutionfess, string collegemag, string cautinmon, string identitys, string insurance, string university, string eligible, string busfees, string examfess, string enrollment, string lockerfee, string gamesfees, string boardfees, string extraactivity, string developemntfee, string paidamt, string totalpkgamt, string balamt, string payemntdt, string paymode, string chkdd,string bankname, string taxes, string remarks, string studids,string cyears,string cclasses,string cdivs, string packid)
    {
        string data = "";
       
        
        if (payemntdt != "")
        {
            date1 = DateTime.Parse(payemntdt);
        }
        if (chkdd != "")
        {
            date2 = DateTime.Parse(payemntdt);
        }
        //using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        //{
        //    using (MySqlCommand cmd = new MySqlCommand("select * from installmentstudent where studid = '" + studids + "'", con))
        //    {
        //        if (con.State != ConnectionState.Open) { con.Open(); }
        //        DataTable ds = new DataTable();
        //        using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
        //        {
        //            da.Fill(ds); con.Close();
        //            if (ds.Rows.Count > 0)
        //            {
        //                //Random rand = new Random();
        //              //  string receiptnos = rand.Next(0, 10000).ToString();
        //                recptsno = Convert.ToInt32(ds.Rows[0]["receiptnos"].ToString());
        //                recptsno = recptsno + 1;
        //                pkgid =  recptsno.ToString();
        //            }
        //            else
        //            {
                        using (MySqlConnection con4 = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
                        {
                            using (MySqlCommand cmd34 = new MySqlCommand("select * from installmentstudent order by id desc limit 1", con4))
                            {
                                if (con4.State != ConnectionState.Open) { con4.Open(); }
                                DataTable ds2 = new DataTable();
                                using (MySqlDataAdapter da2 = new MySqlDataAdapter(cmd34))
                                {
                                    da2.Fill(ds2); con4.Close();
                                    if(ds2.Rows.Count > 0){
                                   // Random rand = new Random();
                                   // string receiptnos1 = rand.Next(0, 10000).ToString();
                                        recptsno = Convert.ToInt32(ds2.Rows[0]["receiptnos"].ToString()) + 1;
                                   
                                }
                            }
                        }
                   }
               // }
           // }
      //  }
        using (MySqlConnection con1 = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("insert into installmentstudent(totalamount,baladmfees,paidadmfess,baltutionfees,paidtuionfess,balcollegefess,paidcollegefess,balcautinmon,paidcaution,balidentitys,paididentitys,balinsurance,paidinsu,universesfees,paiduniversityfess,baleligible,paideligible,balbusfees,paidbusfees,balexamfess,paidexamfees,balenrollment,paidenrollment,lockerfees,paidlockerfee,balgamesfees,paidgamesfees,balboardfees,paidboardfees,balextraactivity,paidextra,baldevelopemntfee,paiddevelopment,paydate,paymode,chequedate,chequenos,bankname,studid,yearsids,classids,divisionsids,taxs,remark,baltotalamt,paidamt,balanceamt,receiptnos,status,packageids) values(@totalamount,@baladmfees,@paidadmfess,@baltutionfees,@paidtutuionfess,@balcollegefess,@paidcollegefess,@balcautinmon,@paidcaution,@balidentitys,@paididentitys,@balinsurance,@paidinsu,@baluniversity,@paiduniversityfess,@baleligible,@paideligible,@balbusfees,@paidbusfees,@balexamfess,@paidexamfees,@balenrollment,@paidenrollment,@ballockerfee,@paidlockerfee,@balgamesfees,@paidgamesfees,@balboardfees,@paidboardfees,@balextraactivity,@paidextra,@baldevelopemntfee,@paiddevelopment,@paydate,@paymode,@chequedate,@chequenos,@bankname,@studid,@yearsids,@classids,@divisionsids,@taxs,@remark,@baltotalamt,@paidamt,@balanceamt,@receiptnos,@status,@packageids)", con1))
            {
                if (con1.State != ConnectionState.Open) { con1.Open(); }
                cmd.Parameters.AddWithValue("@totalamount", totalpkgamt);
                cmd.Parameters.AddWithValue("@baladmfees", adm);
                cmd.Parameters.AddWithValue("@paidadmfess", "0");
                cmd.Parameters.AddWithValue("@baltutionfees", tutionfess);
                cmd.Parameters.AddWithValue("@paidtutuionfess", "0");
                 cmd.Parameters.AddWithValue("@balcollegefess", collegemag);
                cmd.Parameters.AddWithValue("@paidcollegefess", "0");
                cmd.Parameters.AddWithValue("@balcautinmon", cautinmon);
                cmd.Parameters.AddWithValue("@paidcaution", "0");
                cmd.Parameters.AddWithValue("@balidentitys", identitys);
                cmd.Parameters.AddWithValue("@paididentitys", "0");
                cmd.Parameters.AddWithValue("@balinsurance",insurance);

                cmd.Parameters.AddWithValue("@paidinsu", "0");
                cmd.Parameters.AddWithValue("@baluniversity", university);
                cmd.Parameters.AddWithValue("@paiduniversityfess", "0");
                cmd.Parameters.AddWithValue("@baleligible", eligible);
                cmd.Parameters.AddWithValue("@paideligible", "0");
                cmd.Parameters.AddWithValue("@balbusfees", busfees);
                cmd.Parameters.AddWithValue("@paidbusfees", "0");

                cmd.Parameters.AddWithValue("@balexamfess", examfess);
                cmd.Parameters.AddWithValue("@paidexamfees", "0");
                cmd.Parameters.AddWithValue("@balenrollment", enrollment);
                cmd.Parameters.AddWithValue("@paidenrollment", "0");
                cmd.Parameters.AddWithValue("@ballockerfee", lockerfee);
                cmd.Parameters.AddWithValue("@paidlockerfee", "0");
                cmd.Parameters.AddWithValue("@balgamesfees", gamesfees);
                cmd.Parameters.AddWithValue("@paidgamesfees", "0");
                cmd.Parameters.AddWithValue("@balboardfees", boardfees);
                cmd.Parameters.AddWithValue("@paidboardfees", "0");

                cmd.Parameters.AddWithValue("@balextraactivity", extraactivity);
                cmd.Parameters.AddWithValue("@paidextra", "0");
                cmd.Parameters.AddWithValue("@baldevelopemntfee", developemntfee);
                cmd.Parameters.AddWithValue("@paiddevelopment", "0");

                cmd.Parameters.AddWithValue("@paydate", date1.ToString("yyyy/MM/dd"));
                cmd.Parameters.AddWithValue("@paymode", paymode);
                if (chkdd != "")
                {
                    date2 = DateTime.Parse(payemntdt);
                    cmd.Parameters.AddWithValue("@chequedate", date2.ToString("yyyy/MM/dd"));
                }
                else
                {
                    cmd.Parameters.AddWithValue("@chequedate", null);
                }
                cmd.Parameters.AddWithValue("@chequenos", chkdd);
                cmd.Parameters.AddWithValue("@bankname", bankname);
                cmd.Parameters.AddWithValue("@studid", studids);
                cmd.Parameters.AddWithValue("@yearsids", cyears);
                cmd.Parameters.AddWithValue("@classids", cclasses);
                cmd.Parameters.AddWithValue("@divisionsids", cdivs);
                cmd.Parameters.AddWithValue("@taxs", taxes);
                cmd.Parameters.AddWithValue("@remark", remarks);
                cmd.Parameters.AddWithValue("@baltotalamt", balamt);
                cmd.Parameters.AddWithValue("@paidamt", paidamt);
                cmd.Parameters.AddWithValue("@balanceamt", balamt); cmd.Parameters.AddWithValue("@receiptnos", recptsno);
                cmd.Parameters.AddWithValue("@status","Paid");cmd.Parameters.AddWithValue("@packageids",packid);
                cmd.ExecuteNonQuery();
                con1.Close();

                data = "insert";
            }
        }
        using (MySqlConnection con1 = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd1 = new MySqlCommand("Update student_detail set paidamount =@paidamount,balanceamt=@balanceamt where studid = '" + studids + "' ", con1))
            {
                if (con1.State != ConnectionState.Open) { con1.Open(); }
                cmd1.Parameters.AddWithValue("@balanceamt", balamt);
                cmd1.Parameters.AddWithValue("@paidamount", paidamt);
                cmd1.ExecuteNonQuery();
                con1.Close();
            }
        }
        return data;
    }
    [WebMethod]
    public static string saveremainsscholerhip(string schstud, string schadmyrs, string schclsids, string schdivsid, string schpaymode, string cheques1, string banknms, string schpaydt, string schremarks, string schremainsamt)
    {
        string data = "";
        recptsno = 0;
        if (schpaydt != "")
        {
            date2 = DateTime.Parse(schpaydt);
        }
        using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString))
        {
            using (MySqlCommand cmd = new MySqlCommand("select * from installmentstudent order by id desc limit 1", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                DataTable ds = new DataTable();
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                {
                    da.Fill(ds); con.Close();
                    if (ds.Rows.Count > 0)
                    {
                        // Random rand = new Random();
                        // string receiptnos1 = rand.Next(0, 10000).ToString();
                        recptsno = Convert.ToInt32(ds.Rows[0]["receiptnos"].ToString()) + 1;

                    }
                }
            }
        }
        using (MySqlConnection con1 = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd1 = new MySqlCommand("insert into installmentstudent(totalamount,paydate,paymode,chequedate,chequenos,bankname,studid,yearsids,classids,divisionsids,remark,baltotalamt,paidamt,balanceamt,receiptnos,scholershipstatus) values(@totalamount,@paydate,@paymode,@chequedate,@chequenos,@bankname,@studid,@yearsids,@classids,@divisionsids,@remark,@baltotalamt,@paidamt,@balanceamt,@receiptnos,@scholershipstatus)", con1))
            {
                if (con1.State != ConnectionState.Open) { con1.Open(); }
                cmd1.Parameters.AddWithValue("@totalamount", schremainsamt);

                cmd1.Parameters.AddWithValue("@scholershipagainstamt", schremainsamt);
                cmd1.Parameters.AddWithValue("@paydate", date2.ToString("yyyy/MM/dd"));
                cmd1.Parameters.AddWithValue("@paymode", schpaymode);
              
                    cmd1.Parameters.AddWithValue("@chequedate", date2.ToString("yyyy/MM/dd"));

                    cmd1.Parameters.AddWithValue("@chequenos", cheques1);
                    cmd1.Parameters.AddWithValue("@bankname", banknms);
                    cmd1.Parameters.AddWithValue("@studid", schstud);
                    cmd1.Parameters.AddWithValue("@yearsids", schadmyrs);
                    cmd1.Parameters.AddWithValue("@classids", schclsids);
                    cmd1.Parameters.AddWithValue("@divisionsids", schdivsid);

                    cmd1.Parameters.AddWithValue("@remark", schremarks);
                    cmd1.Parameters.AddWithValue("@baltotalamt", schremainsamt);
                    cmd1.Parameters.AddWithValue("@paidamt", schremainsamt);
                    cmd1.Parameters.AddWithValue("@balanceamt", schremainsamt);
                    cmd1.Parameters.AddWithValue("@receiptnos", recptsno);
                    cmd1.Parameters.AddWithValue("@scholershipstatus", "Against Scholership"); 
                cmd1.ExecuteNonQuery();
                con1.Close();

                data = "insert";
            }
        }
        return data;
    }
    [WebMethod]
    public static string Getdata12(string studids, string curryear, string sclass, string scurrdivision, string pkgssid)
    {
        string _data = "";
        {

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

                if (con.State != ConnectionState.Open) { con.Open(); }
                using (MySqlCommand cmdpack = new MySqlCommand("SELECT installmentstudent.*,student_detail.*,installmentstudent.packageids AS pkgddids,installmentstudent.yearsids AS yids,installmentstudent.classids AS clsid,installmentstudent.divisionsids AS divsids,date_format(paydate,'%d-%m-%Y') as recptdate,date_format(chequedate,'%d-%m-%Y') as chqdt from installmentstudent inner join student_detail on student_detail.studid = installmentstudent.studid AND student_detail.admyears = installmentstudent.yearsids AND student_detail.classd = installmentstudent.classids AND student_detail.division = installmentstudent.divisionsids AND student_detail.packageid = installmentstudent.packageids where installmentstudent.studid = '" + studids + "' and installmentstudent.yearsids = '" + curryear + "' and installmentstudent.classids = '" + sclass + "' and installmentstudent.divisionsids = '" + scurrdivision + "' AND installmentstudent.packageids = '" + pkgssid + "'", 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;
    }
    [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 installmentstudent where id = '" + ids + "' and paymode !='obc scholership'", con))
                {
                   dels = cmdpack.ExecuteNonQuery();
                    con.Close();
                    _data = dels.ToString() ;
                }
            }

        }

        return _data;
    }
}