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;
using System.Web.Script;
using System.Web.Script.Services;
public partial class module_examfeesemister : 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 object GetData1(string years, string classes, string divisions, string semid, string payst)
    {
        string data = " ";
       
      
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,package_detail.id as pkgsids,academic_year.id as yearid,examfeemaster.feepackname,examfeemaster.totalamount ,examfeemaster.semisteryears,student_detail.title,student_detail.fname,student_detail.mname,student_detail.lname,exampackageassign.examrecpt,exampackageassign.paymode,exampackageassign.utrnos,DATE_FORMAT(exampackageassign.examdt,'%d-%m-%Y') AS examdate,exampackageassign.exampkgid AS exampkgs,semistertbl.semnos from  student_detail inner join package_detail on student_detail.packageid = package_detail.id  left join class_master on student_detail.classd = class_master.id left join division_master on student_detail.division = division_master.id  left join academic_year on student_detail.admyears = academic_year.id  LEFT JOIN exampackageassign ON student_detail.studid = exampackageassign.studids left join examfeemaster on exampackageassign.exampkgid =  examfeemaster.id LEFT JOIN semistertbl ON  exampackageassign.semisterid = semistertbl.id where student_detail.admyears = '" + years + "' and  student_detail.classd ='" + classes + "' and student_detail.division='" + divisions + "' and exampackageassign.semisterid = '" + semid + "'  and  student_detail.transferstatus ='' 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);
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            data = JsonConvert.SerializeObject(ds.Tables[0]);
                        }
                        con.Close();

                    }
                }
            }
        
        return data;
    }
    [WebMethod]

    public static object GetData()
    {
        string data = " ";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,package_detail.id as pkgsids,academic_year.id as yearid,examfeemaster.feepackname,examfeemaster.totalamount ,examfeemaster.semisteryears,student_detail.title,student_detail.fname,student_detail.mname,student_detail.lname,exampackageassign.examrecpt,exampackageassign.paymode,exampackageassign.utrnos,DATE_FORMAT(exampackageassign.examdt,'%d-%m-%Y') AS examdate,exampackageassign.exampkgid AS exampkgs from  student_detail inner join package_detail on student_detail.packageid = package_detail.id  left join class_master on student_detail.classd = class_master.id left join division_master on student_detail.division = division_master.id  left join academic_year on student_detail.admyears = academic_year.id  LEFT JOIN exampackageassign ON student_detail.studid = exampackageassign.studids left join examfeemaster on exampackageassign.exampkgid =  examfeemaster.id where  transferstatus =''", 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;
    }
    [WebMethod]

    public static object exports(string years, string classes, string divisions, string semid)
    {
        string data = " ";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select student_detail.*,student_detail.id as studids,class_master.class as clsname,division_master.division as divsname,package_detail.packgenme as pkgname,package_detail.packageids as pkgids,class_master.id as clsids,division_master.id as divsids,date_format(student_detail.today,'%d-%m-%Y') as regdate,academic_year.fromdate,academic_year.todate,package_detail.id as pkgsids,academic_year.id as yearid,examfeemaster.feepackname,examfeemaster.totalamount ,examfeemaster.semisteryears,student_detail.title,student_detail.fname,student_detail.mname,student_detail.lname,exampackageassign.examrecpt,exampackageassign.paymode,exampackageassign.utrnos,DATE_FORMAT(exampackageassign.examdt,'%d-%m-%Y') AS examdate,exampackageassign.exampkgid AS exampkgs,semistertbl.semnos from  student_detail inner join package_detail on student_detail.packageid = package_detail.id  left join class_master on student_detail.classd = class_master.id left join division_master on student_detail.division = division_master.id  left join academic_year on student_detail.admyears = academic_year.id  LEFT JOIN exampackageassign ON student_detail.studid = exampackageassign.studids left join examfeemaster on exampackageassign.exampkgid =  examfeemaster.id LEFT JOIN semistertbl ON  exampackageassign.semisterid = semistertbl.id where student_detail.admyears = '" + years + "' and  student_detail.classd ='" + classes + "' and student_detail.division='" + divisions + "' and exampackageassign.semisterid = '" + semid + "'  and  student_detail.transferstatus ='' 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);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        data = JsonConvert.SerializeObject(ds.Tables[0]);
                    }
                    con.Close();

                }
            }
        }
        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; }public string studids { get; set; } 
        public string titles { get; set; } public string fname { get; set; } public string mname { get; set; } public string lname { get; set; }
    }
    [WebMethod]
    public static List<Department> PopulateDropDownList3()
    {
        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> PopulateDropDownListStudent(string years, string classes, string divisions)
    {
        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 ='" + years + "' and classd ='" + classes + "' and division ='" + divisions + "'", 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(),
                                titles = dtnew.Rows[i]["title"].ToString(),
                                fname = dtnew.Rows[i]["fname"].ToString(),
                                mname = dtnew.Rows[i]["mname"].ToString(),
                                lname = dtnew.Rows[i]["lname"].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 yrsid, string divsid, string clsid)
    {

        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 fname  LIKE '%" + pre + "%' and admyears = '" + yrsid + "' and classd ='" + clsid + "' and division ='" + divsid + "'   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 + "%' and admyears = '" + yrsid + "' and classd ='" + clsid + "' and division ='" + divsid + "' ";
                            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 + "%' and admyears = '" + yrsid + "' and classd ='" + clsid + "' and division ='" + divsid + "' ";
                                        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 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 * from class_master where class != 'D.Pharm'", 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> PopulateDropDownList(string clsid)
    {
        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 = '" + clsid + "'", 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> PopulateDropDownList45(string yrsid, string divsid, string clsid)
    {
        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 where  classid = '" + clsid + "' and divsid = '" + 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
                            {
                                DeptId = Convert.ToInt32(dtnew.Rows[i]["id"]),
                                DeptName = dtnew.Rows[i]["semnos"].ToString(),

                            });
                        }
                    }
                }
            }
        }
        return objDept;
    }

    [WebMethod]
    public static object DeleteData(string id, string studentids, string yrsids, string classid, string divisions)
    {
        string data = "";
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            if (con.State != ConnectionState.Open) { con.Open(); }
            using (MySqlCommand cmd = new MySqlCommand("delete from exampackageassign where examrecpt='" + id + "'", con))
            {

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

                }
            }
        }
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            if (con.State != ConnectionState.Open) { con.Open(); }
            using (MySqlCommand cmd = new MySqlCommand("update student_detail set  exampkgid= '',examrectnos = '',examfeestatus = '' where examrectnos='" + id + "' and studid = '" + studentids + "' and admyears ='" + yrsids + "' and classd = '" + classid + "' and division = '" + divisions + "' and examfeestatus ='Assigned'", con))
            {

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


                }
            }
        }
        return data;
    }
    [WebMethod]
    public static List<Department> PopulateDropDownList46(string yrsid, string divsid, string clsid, string semdids)
    {
        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 examfeemaster where yearsid = '" + yrsid + "' and  classid = '" + clsid + "' and divsid = '" + divsid + "' and semisteryears = '" + semdids + "' and status = 'Active'", 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]["feepackname"].ToString(),

                            });
                        }
                    }
                }
            }
        }
        return objDept;
    }
    [WebMethod]
    public static string AssignData(string stdids, string clsids, string divs, string yrs, string semis, string expkgid, string exampaydt, string pymode, string utrnos)
    {
        string data = "";
        int  recptnos = 1;
        if (exampaydt != "")
        {
            date1 = DateTime.Parse(exampaydt);
        }
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("select student_detail.* from  student_detail inner join package_detail on student_detail.packageid = package_detail.id   where student_detail.admyears = '" + yrs + "' and  student_detail.classd ='" + clsids + "' and student_detail.division='" + divs + "'", con))
            {
                if (con.State != ConnectionState.Open) { con.Open(); }
                DataTable ds = new DataTable();
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                {
                    da.Fill(ds);
                    if (ds.Rows.Count > 0)
                    {
                        //Random rand = new Random();
                        //recptnos = rand.Next(0, 10000).ToString();
                        using (MySqlConnection con4 = new MySqlConnection(constring))
                        {
                            using (MySqlCommand cmd4 = new MySqlCommand("select examrecpt as reptsnos from  exampackageassign order by exampackageassign.id desc limit 1", con4))
                            {
                                if (con4.State != ConnectionState.Open) { con4.Open(); }
                                DataTable ds4 = new DataTable();
                                using (MySqlDataAdapter da4 = new MySqlDataAdapter(cmd4))
                                {
                                    da4.Fill(ds4);
                                    if (ds4.Rows.Count > 0)
                                    {
                                        recptnos = Convert.ToInt32(ds4.Rows[0]["reptsnos"].ToString());
                                        if (recptnos == 0)
                                        {
                                            recptnos = 1;
                                        }
                                        else
                                        {
                                            recptnos = recptnos + 1;
                                        }
                                    }
                                }
                            }
                        }
                        if (semis == "1" || semis == "9" || semis == "17" || semis == "21" || semis == "25")
                        {
                            using (MySqlConnection con1 = new MySqlConnection(constring))
                            {
                                using (MySqlCommand cmd1 = new MySqlCommand("update student_detail set sem1 = '" + semis + "' where studid='" + stdids + "' and  admyears='" + yrs + "' and classd = '" + clsids + "' and division = '" + divs + "'", con1))
                                {
                                    if (con1.State != ConnectionState.Open) { con1.Open(); }

                                    cmd1.ExecuteNonQuery();
                                    con1.Close();
                                }
                            }
                        }
                        if (semis == "2" || semis == "10" || semis == "22" || semis == "26" || semis == "29")
                        {
                            using (MySqlConnection con1 = new MySqlConnection(constring))
                            {
                                using (MySqlCommand cmd1 = new MySqlCommand("update student_detail set sem2 = '" + semis + "' where studid='" + stdids + "' and  admyears='" + yrs + "' and classd = '" + clsids + "' and division = '" + divs + "'", con1))
                                {
                                    if (con1.State != ConnectionState.Open) { con1.Open(); }

                                    cmd1.ExecuteNonQuery();
                                    con1.Close();
                                }
                            }
                        }
                        if (semis == "3" || semis == "11" || semis == "19" || semis == "23" || semis == "27")
                        {
                            using (MySqlConnection con1 = new MySqlConnection(constring))
                            {
                                using (MySqlCommand cmd1 = new MySqlCommand("update student_detail set sem3 = '" + semis + "' where studid='" + stdids + "' and  admyears='" + yrs + "' and classd = '" + clsids + "' and division = '" + divs + "'", con1))
                                {
                                    if (con1.State != ConnectionState.Open) { con1.Open(); }

                                    cmd1.ExecuteNonQuery();
                                    con1.Close();
                                }
                            }
                        }
                        if (semis == "4" || semis == "12" || semis == "20" || semis == "24" || semis == "28")
                        {
                            using (MySqlConnection con1 = new MySqlConnection(constring))
                            {
                                using (MySqlCommand cmd1 = new MySqlCommand("update student_detail set sem4 = '" + semis + "' where studid='" + stdids + "' and  admyears='" + yrs + "' and classd = '" + clsids + "' and division = '" + divs + "'", con1))
                                {
                                    if (con1.State != ConnectionState.Open) { con1.Open(); }

                                    cmd1.ExecuteNonQuery();
                                    con1.Close();
                                }
                            }
                        }
                        if (semis == "5")
                        {
                            using (MySqlConnection con1 = new MySqlConnection(constring))
                            {
                                using (MySqlCommand cmd1 = new MySqlCommand("update student_detail set sem5 = '" + semis + "' where studid='" + stdids + "' and  admyears='" + yrs + "' and classd = '" + clsids + "' and division = '" + divs + "'", con1))
                                {
                                    if (con1.State != ConnectionState.Open) { con1.Open(); }

                                    cmd1.ExecuteNonQuery();
                                    con1.Close();
                                }
                            }
                        }
                        if (semis == "6")
                        {
                            using (MySqlConnection con1 = new MySqlConnection(constring))
                            {
                                using (MySqlCommand cmd1 = new MySqlCommand("update student_detail set sem6 = '" + semis + "' where studid='" + stdids + "' and  admyears='" + yrs + "' and classd = '" + clsids + "' and division = '" + divs + "'", con1))
                                {
                                    if (con1.State != ConnectionState.Open) { con1.Open(); }

                                    cmd1.ExecuteNonQuery();
                                    con1.Close();
                                }
                            }
                        }
                        if (semis == "7")
                        {
                            using (MySqlConnection con1 = new MySqlConnection(constring))
                            {
                                using (MySqlCommand cmd1 = new MySqlCommand("update student_detail set sem7 = '" + semis + "' where studid='" + stdids + "' and  admyears='" + yrs + "' and classd = '" + clsids + "' and division = '" + divs + "'", con1))
                                {
                                    if (con1.State != ConnectionState.Open) { con1.Open(); }

                                    cmd1.ExecuteNonQuery();
                                    con1.Close();
                                }
                            }
                        }
                        if (semis == "8")
                        {
                            using (MySqlConnection con1 = new MySqlConnection(constring))
                            {
                                using (MySqlCommand cmd1 = new MySqlCommand("update student_detail set sem8 = '" + semis + "' where studid='" + stdids + "' and  admyears='" + yrs + "' and classd = '" + clsids + "' and division = '" + divs + "'", con1))
                                {
                                    if (con1.State != ConnectionState.Open) { con1.Open(); }

                                    cmd1.ExecuteNonQuery();
                                    con1.Close();
                                }
                            }
                        }
                        using (MySqlConnection con2 = new MySqlConnection(constring))
                        {
                            using (MySqlCommand cmd2 = new MySqlCommand("insert into exampackageassign(studids,yearid,classids,divisonsid,semisterid,exampkgid,examrecpt,examdt,paymode,utrnos) values(@studids,@yearid,@classids,@divisonsid,@semisterid,@exampkgid,@examrecpt,@examdt,@paymode,@utrnos)", con2))
                            {
                                if (con2.State != ConnectionState.Open) { con2.Open(); }
                                cmd2.Parameters.AddWithValue("@studids", stdids);
                                cmd2.Parameters.AddWithValue("@yearid", yrs);
                                cmd2.Parameters.AddWithValue("@classids", clsids);
                                cmd2.Parameters.AddWithValue("@divisonsid", divs);
                                cmd2.Parameters.AddWithValue("@examrecpt", recptnos);
                                cmd2.Parameters.AddWithValue("@semisterid", semis);
                                cmd2.Parameters.AddWithValue("@exampkgid", expkgid);
                                cmd2.Parameters.AddWithValue("@paymode", pymode);
                                cmd2.Parameters.AddWithValue("@utrnos", utrnos);
                                cmd2.Parameters.AddWithValue("@examdt", date1.ToString("yyyy/MM/dd"));
                                cmd2.ExecuteNonQuery();
                               int exid = Convert.ToInt32( cmd2.LastInsertedId);
                               con2.Close(); data = recptnos.ToString();
                            }
                        }
                    }
                }
            }
        }
        return data;
    }
} 