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

    }
    [WebMethod]

    public static object GetData(string studids, string inst, string recptnos, string pkgid, string yearsid, string classid, string divisid)
    {
        string data = " ";
        installsids = Convert.ToInt32(recptnos) - 1;
        using (MySqlConnection con = new MySqlConnection(constring))
        {
            using (MySqlCommand cmd = new MySqlCommand("SELECT inst.*,st.*,pkg.packgenme,class_master.class,division_master.division as divsname,date_format(inst.paydate,'%d-%m-%Y') as recptdate,date_format(inst.chequedate,'%d-%m-%Y') as chkdt,pkg.*,(select COALESCE(balanceamt,0) FROM installmentstudent where receiptnos = '" + installsids + "') as balprevious,(SELECT (COALESCE(baldevelopemntfee,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 = inst.packageids and installmentstudent.receiptnos = '" + installsids + "')  as baldevelops,(SELECT (COALESCE(SUM(baldevelopemntfee),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 = inst.packageids)  as baldevelops1,(SELECT (COALESCE(baladmfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids and installmentstudent.receiptnos = '" + installsids + "')  as prevbaladmissions,(SELECT  (COALESCE(SUM(baladmfees),0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids)  as prevbaladmissions1,(SELECT (COALESCE(baltutionfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids and installmentstudent.receiptnos = '" + installsids + "')  as prevbaltutuions,(SELECT  (COALESCE(SUM(baltutionfees),0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids)  as prevbaltutuions2,(SELECT (COALESCE(baltutionfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY installmentstudent.studid LIMIT 1)  as prevbaltutuions1,(SELECT (COALESCE(balcollegefess,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids and installmentstudent.receiptnos = '" + installsids + "')  as prevbalcolleges,(SELECT (COALESCE(SUM(balcollegefess),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids)  as prevbalcolleges1,(SELECT (COALESCE(balcautinmon,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids and installmentstudent.receiptnos = '" + installsids + "')  as prevbalcautions,(SELECT (COALESCE(SUM(balcautinmon),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids)  as prevbalcautions1,(SELECT (COALESCE(balidentitys,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids and installmentstudent.receiptnos = '" + installsids + "')  as prevbalidentitys,(SELECT (COALESCE(SUM(balidentitys),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids)  as prevbalidentitys1,(SELECT (COALESCE(balinsurance,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids and installmentstudent.receiptnos = '" + installsids + "')  as prevbalinsur,(SELECT  (COALESCE(SUM(balinsurance),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids)  as prevbalinsur1,(SELECT (COALESCE(universesfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids and installmentstudent.receiptnos = '" + installsids + "')  as prevbaluniversity,(SELECT (COALESCE(SUM(universesfees),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids)  as prevbaluniversity1,(SELECT (COALESCE(baleligible,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids and installmentstudent.receiptnos = '" + installsids + "')  as prevbalbaleligible,(SELECT (COALESCE(SUM(baleligible),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids)  as prevbalbaleligible1,(SELECT (COALESCE(balbusfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids and installmentstudent.receiptnos = '" + installsids + "') as prevbalbusfees,(SELECT (COALESCE(SUM(balbusfees),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids) as prevbalbusfees1,(SELECT (COALESCE(balexamfess,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids and installmentstudent.receiptnos = '" + installsids + "') as prevbalexamination,(SELECT  (COALESCE(SUM(balexamfess),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids) as prevbalexamination1,(SELECT (COALESCE(balenrollment,0))  FROM installmentstudent  where installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids and installmentstudent.receiptnos = '" + installsids + "') as prevbalenrollments,(SELECT  (COALESCE(SUM(balenrollment),0)) FROM installmentstudent  where installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids) as prevbalenrollments1,(SELECT (COALESCE(lockerfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids and installmentstudent.receiptnos = '" + installsids + "') as prevballockerfees,(SELECT  (COALESCE(SUM(lockerfees),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids) as prevballockerfees1,(SELECT (COALESCE(balgamesfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids and installmentstudent.receiptnos = '" + installsids + "') as prevbalgamesfees,(SELECT (COALESCE(SUM(balgamesfees),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids) as prevbalgamesfees1,(SELECT (COALESCE(balboardfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids and installmentstudent.receiptnos = '" + installsids + "') as prevbalboardsfees,(SELECT  (COALESCE(SUM(balboardfees),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids) as prevbalboardsfees1,(SELECT (COALESCE(balextraactivity,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids  and installmentstudent.receiptnos = '" + installsids + "') as prevbalextraactivity,(SELECT (COALESCE(SUM(balextraactivity),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids) as prevbalextraactivity1 FROM installmentstudent AS inst LEFT JOIN student_detail AS st ON inst.studid = st.studid AND st.admyears = inst.yearsids AND st.classd = inst.classids AND st.division = inst.divisionsids AND st.packageid = inst.packageids LEFT JOIN package_detail AS pkg ON inst.packageids = pkg.id left join class_master ON pkg.classdetail  = class_master.id left join division_master on pkg.divisiondet = division_master.id WHERE inst.receiptnos = '" + recptnos + "' AND inst.studid = '" + studids + "' and inst.yearsids = '" + yearsid + "' and inst.classids = '" + classid + "' and inst.divisionsids = '" + divisid + "' and inst.packageids = '" + pkgid + "'", con))
           // using (MySqlCommand cmd = new MySqlCommand("SELECT inst.*,st.*,pkg.packgenme,class_master.class,division_master.division as divsname,date_format(inst.paydate,'%d-%m-%Y') as recptdate,date_format(inst.chequedate,'%d-%m-%Y') as chkdt,pkg.*,(select COALESCE(balanceamt,0) FROM installmentstudent where receiptnos = '57') as balprevious,(SELECT (COALESCE(baldevelopemntfee,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.receiptnos = '57')  as baldevelops,(SELECT (COALESCE(SUM(baldevelopemntfee),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid)  as baldevelops1,(SELECT (COALESCE(baladmfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.receiptnos = '57')  as prevbaladmissions,(SELECT  (COALESCE(SUM(baladmfees),0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid)  as prevbaladmissions1,(SELECT (COALESCE(baltutionfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.receiptnos = '57')  as prevbaltutuions,(SELECT  (COALESCE(SUM(baltutionfees),0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid)  as prevbaltutuions2,(SELECT (COALESCE(baltutionfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid ORDER BY installmentstudent.studid LIMIT 1)  as prevbaltutuions1,(SELECT (COALESCE(balcollegefess,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.receiptnos = '57')  as prevbalcolleges,(SELECT (COALESCE(SUM(balcollegefess),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid)  as prevbalcolleges1,(SELECT (COALESCE(balcautinmon,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.receiptnos = '57')  as prevbalcautions,(SELECT (COALESCE(SUM(balcautinmon),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid)  as prevbalcautions1,(SELECT (COALESCE(balidentitys,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.receiptnos = '57')  as prevbalidentitys,(SELECT (COALESCE(SUM(balidentitys),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid)  as prevbalidentitys1,(SELECT (COALESCE(balinsurance,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.receiptnos = '57')  as prevbalinsur,(SELECT  (COALESCE(SUM(balinsurance),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid)  as prevbalinsur1,(SELECT (COALESCE(universesfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.receiptnos = '57')  as prevbaluniversity,(SELECT (COALESCE(SUM(universesfees),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid )  as prevbaluniversity1,(SELECT (COALESCE(baleligible,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.receiptnos = '57')  as prevbalbaleligible,(SELECT (COALESCE(SUM(baleligible),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid)  as prevbalbaleligible1,(SELECT (COALESCE(balbusfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.receiptnos = '57') as prevbalbusfees,(SELECT (COALESCE(SUM(balbusfees),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid) as prevbalbusfees1,(SELECT (COALESCE(balexamfess,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.receiptnos = '57') as prevbalexamination,(SELECT  (COALESCE(SUM(balexamfess),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid) as prevbalexamination1,(SELECT (COALESCE(balenrollment,0))  FROM installmentstudent  where installmentstudent.studid = inst.studid and installmentstudent.receiptnos = '57') as prevbalenrollments,(SELECT  (COALESCE(SUM(balenrollment),0)) FROM installmentstudent  where installmentstudent.studid = inst.studid) as prevbalenrollments1,(SELECT (COALESCE(lockerfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.receiptnos = '57') as prevballockerfees,(SELECT  (COALESCE(SUM(lockerfees),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid) as prevballockerfees1,(SELECT (COALESCE(balgamesfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.receiptnos = '57') as prevbalgamesfees,(SELECT (COALESCE(SUM(balgamesfees),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid) as prevbalgamesfees1,(SELECT (COALESCE(balboardfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.receiptnos = '57') as prevbalboardsfees,(SELECT  (COALESCE(SUM(balboardfees),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid ) as prevbalboardsfees1,(SELECT (COALESCE(balextraactivity,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid  and installmentstudent.receiptnos = '57') as prevbalextraactivity,(SELECT (COALESCE(SUM(balextraactivity),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid) as prevbalextraactivity1 FROM installmentstudent AS inst LEFT JOIN student_detail AS st ON inst.studid = st.studid LEFT JOIN package_detail AS pkg ON inst.packageids = pkg.id left join class_master ON pkg.classdetail  = class_master.id left join division_master on pkg.divisiondet = division_master.id WHERE inst.receiptnos = '" + recptnos + "' AND inst.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)
                    {
                        data = JsonConvert.SerializeObject(ds);
                    }
                 else{
                  using (MySqlConnection con1 = new MySqlConnection(constring))
                    {
                        using (MySqlCommand cmd1 = new MySqlCommand("SELECT inst.*,st.*,pkg.packgenme,class_master.class,division_master.division as divsname,date_format(inst.paydate,'%d-%m-%Y') as recptdate,date_format(inst.chequedate,'%d-%m-%Y') as chkdt,pkg.*,(select COALESCE(balanceamt,0) FROM installmentstudent ORDER BY receiptnos LIMIT 1,1) as balprevious,(SELECT (COALESCE(baldevelopemntfee,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY receiptnos LIMIT 1,1)  as baldevelops,(SELECT (COALESCE(SUM(baldevelopemntfee),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY installmentstudent.studid LIMIT 1,1)  as baldevelops1,(SELECT (COALESCE(baladmfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY  installmentstudent.receiptnos LIMIT 1,1)  as prevbaladmissions,(SELECT  (COALESCE(SUM(baladmfees),0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY installmentstudent.studid LIMIT 1,1)  as prevbaladmissions1,(SELECT (COALESCE(baltutionfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY  installmentstudent.receiptnos LIMIT 1,1 )  as prevbaltutuions,(SELECT  (COALESCE(SUM(baltutionfees),0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY installmentstudent.studid LIMIT 1,1)  as prevbaltutuions2,(SELECT (COALESCE(baltutionfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY installmentstudent.studid LIMIT 1)  as prevbaltutuions1,(SELECT (COALESCE(balcollegefess,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY  installmentstudent.receiptnos LIMIT 1,1)  as prevbalcolleges,(SELECT (COALESCE(SUM(balcollegefess),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY installmentstudent.studid LIMIT 1,1)  as prevbalcolleges1,(SELECT (COALESCE(balcautinmon,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY  installmentstudent.receiptnos LIMIT 1,1)  as prevbalcautions,(SELECT (COALESCE(SUM(balcautinmon),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY installmentstudent.studid LIMIT 1,1)  as prevbalcautions1,(SELECT (COALESCE(balidentitys,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY  installmentstudent.receiptnos LIMIT 1,1)  as prevbalidentitys,(SELECT (COALESCE(SUM(balidentitys),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY installmentstudent.studid LIMIT 1,1)  as prevbalidentitys1,(SELECT (COALESCE(balinsurance,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY  installmentstudent.receiptnos LIMIT 1,1)  as prevbalinsur,(SELECT  (COALESCE(SUM(balinsurance),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY installmentstudent.studid LIMIT 1,1)  as prevbalinsur1,(SELECT (COALESCE(universesfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY  installmentstudent.receiptnos LIMIT 1,1)  as prevbaluniversity,(SELECT (COALESCE(SUM(universesfees),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY  installmentstudent.studid LIMIT 1,1)  as prevbaluniversity1,(SELECT (COALESCE(baleligible,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY  installmentstudent.receiptnos LIMIT 1,1)  as prevbalbaleligible,(SELECT (COALESCE(SUM(baleligible),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY installmentstudent.studid LIMIT 1,1)  as prevbalbaleligible1,(SELECT (COALESCE(balbusfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY  installmentstudent.receiptnos LIMIT 1,1) as prevbalbusfees,(SELECT (COALESCE(SUM(balbusfees),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY installmentstudent.studid LIMIT 1,1) as prevbalbusfees1,(SELECT (COALESCE(balexamfess,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY  installmentstudent.receiptnos LIMIT 1,1) as prevbalexamination,(SELECT  (COALESCE(SUM(balexamfess),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY installmentstudent.studid LIMIT 1,1) as prevbalexamination1,(SELECT (COALESCE(balenrollment,0))  FROM installmentstudent  where installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY  installmentstudent.receiptnos LIMIT 1,1) as prevbalenrollments,(SELECT  (COALESCE(SUM(balenrollment),0)) FROM installmentstudent  where installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY installmentstudent.studid LIMIT 1,1) as prevbalenrollments1,(SELECT (COALESCE(lockerfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY  installmentstudent.receiptnos LIMIT 1,1) as prevballockerfees,(SELECT  (COALESCE(SUM(lockerfees),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY installmentstudent.studid LIMIT 1,1) as prevballockerfees1,(SELECT (COALESCE(balgamesfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY  installmentstudent.receiptnos LIMIT 1,1) as prevbalgamesfees,(SELECT (COALESCE(SUM(balgamesfees),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY installmentstudent.studid LIMIT 1,1) as prevbalgamesfees1,(SELECT (COALESCE(balboardfees,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY  installmentstudent.receiptnos LIMIT 1,1) as prevbalboardsfees,(SELECT  (COALESCE(SUM(balboardfees),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY installmentstudent.studid LIMIT 1,1) as prevbalboardsfees1,(SELECT (COALESCE(balextraactivity,0))  FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids ORDER BY  installmentstudent.receiptnos LIMIT 1,1) as prevbalextraactivity,(SELECT (COALESCE(SUM(balextraactivity),0)) FROM installmentstudent WHERE installmentstudent.studid = inst.studid and installmentstudent.classids = inst.classids and installmentstudent.divisionsids = inst.divisionsids and installmentstudent.packageids = inst.packageids) as prevbalextraactivity1 FROM installmentstudent AS inst LEFT JOIN student_detail AS st ON inst.studid = st.studid AND st.admyears = inst.yearsids AND st.classd = inst.classids AND st.division = inst.divisionsids AND st.packageid = inst.packageids LEFT JOIN package_detail AS pkg ON inst.packageids = pkg.id left join class_master ON pkg.classdetail  = class_master.id left join division_master on pkg.divisiondet = division_master.id WHERE inst.receiptnos = '" + recptnos + "' AND inst.studid = '" + studids + "' and inst.yearsids = '" + yearsid + "' and inst.classids = '" + classid + "' and inst.divisionsids = '" + divisid + "' and inst.packageids = '" + pkgid + "'", con))
                     {
                      if (con1.State != ConnectionState.Open) { con1.Open(); }
                DataTable ds1 = new DataTable();
                using (MySqlDataAdapter da1 = new MySqlDataAdapter(cmd1))
                {
                    da1.Fill(ds); con1.Close();
                    if (ds1.Rows.Count > 0)
                    {
                        data = JsonConvert.SerializeObject(ds1);
                    }


                }
                     }
                     }
                  }

                }
            }
        }
        return data;
    }
}