using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Linq;
using System.ServiceModel;
using System.Web;
using Newtonsoft.Json.Linq;
using System.Data;
using Newtonsoft.Json;
using System.Data.SqlClient;

namespace WorkShop_Services
{
    public partial class Service1 : IService1
    {
        public int voucher_insert(String json)
        {
            JObject json_object = JObject.Parse(json);
            DataTable table = (DataTable)JsonConvert.DeserializeObject(Convert.ToString(json_object["details"]), (typeof(DataTable)));
            String type = Convert.ToString(json_object["type"]);
            String person = Convert.ToString(json_object["person"]);
            String edate = Convert.ToString(json_object["edate"]);
            String maincode = Convert.ToString(json_object["maincode"]);
            String UserName = Convert.ToString(json_object["UserName"]);
            String System_Name = Convert.ToString(json_object["System_Name"]);
            int posted = Convert.ToInt32(json_object["posted"]);
            int rowseffected = 0;
            int IsInsert = 0;
            int rows = 0;
            String S_Name = UserName;
            DataSet da = new DataSet();

            DataTable ds = new DataTable();
            da.Tables.Add(ds);
            SqlTransaction trans = null;
            int entryno = 0;
            String period = json_object["period"].ToString();
            String LastVoucher = "select  top 1 isnull(Entry,0) from Log where type='" + type + "' order by Entry desc";
            String select_master = "SELECT vm.* FROM VoucherMaster VM INNER JOIN VoucherDetail VD ON VM.idno=VD.MEntry WHERE VM.type='" + type + "' and vm.idno=";
            String select_child = "SELECT vd.* FROM VoucherMaster VM INNER JOIN VoucherDetail VD ON VM.idno=VD.MEntry WHERE VM.type='" + type + "' and vm.idno=";
            int voucherno = 0;
            int idno = 0;
            try
            {
                using (con = new SqlConnection(cs))
                {
                    con.Open();
                    trans = con.BeginTransaction();

                    using (cm = new SqlCommand(LastVoucher, con, trans))
                    {

                        voucherno = Convert.ToInt32(cm.ExecuteScalar()) + 1;
                    }
                    using (cm = new SqlCommand("select top 1 isnull(entryno,0)+1 from Ledger order by entryno desc;", con, trans))
                    {

                        entryno = Convert.ToInt32(cm.ExecuteScalar());
                    }

                    using (cm = new SqlCommand("insert into VoucherMaster(Entry,type,person,edate,sysdate,maincode,entry2,posted) values(@Entry,@type,@person,@edate,@sdate,@maincode,@entryno,@posted); SELECT SCOPE_IDENTITY() AS CODE", con, trans))
                    {
                        cm.Parameters.AddWithValue("Entry", voucherno);
                        cm.Parameters.AddWithValue("type", type);
                        cm.Parameters.AddWithValue("person", person);
                        cm.Parameters.AddWithValue("maincode", maincode);
                        cm.Parameters.AddWithValue("edate", edate);
                        cm.Parameters.AddWithValue("sdate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:s"));
                        cm.Parameters.AddWithValue("entryno", entryno);
                        cm.Parameters.AddWithValue("posted", posted);
                        idno = Convert.ToInt32(cm.ExecuteScalar());

                    }
                    if (idno > 0)
                    {
                        using (cm = new SqlCommand("insert into VoucherDetail(Mentry,acode,description,debit,credit,chequeno,cheqdate,issued,Bank,Chequetitle) values(@Mentry,@acode,@desc,@debit,@credit,@chequeno,@chequedate,@issued,@bank,@chequetitle);", con, trans))
                        {

                            cm.Parameters.Add("Mentry", SqlDbType.Int);
                            cm.Parameters.Add("acode", SqlDbType.VarChar);
                            cm.Parameters.Add("desc", SqlDbType.VarChar);
                            cm.Parameters.Add("debit", SqlDbType.Money);
                            cm.Parameters.Add("credit", SqlDbType.Money);
                            cm.Parameters.Add("chequeno", SqlDbType.VarChar);
                            cm.Parameters.Add("chequedate", SqlDbType.Date);
                            cm.Parameters.Add("issued", SqlDbType.Int);
                            cm.Parameters.Add("Bank", SqlDbType.VarChar);
                            cm.Parameters.Add("Chequetitle", SqlDbType.VarChar);

                            for (int i = 0; i < table.Rows.Count; i++)
                            {
                                cm.Parameters["Mentry"].Value = idno;
                                cm.Parameters["acode"].Value = table.Rows[i]["acode"];
                                cm.Parameters["desc"].Value = table.Rows[i]["desc"];
                                cm.Parameters["debit"].Value = table.Rows[i]["debit"];
                                cm.Parameters["credit"].Value = table.Rows[i]["credit"];
                                cm.Parameters["chequeno"].Value = table.Rows[i]["chequeno"];
                                cm.Parameters["chequedate"].Value = table.Rows[i]["chequedate"];
                                cm.Parameters["issued"].Value = table.Rows[i]["issued"];
                                cm.Parameters["Bank"].Value = table.Rows[i]["Bank"];
                                cm.Parameters["Chequetitle"].Value = table.Rows[i]["Chequetitle"];
                                rowseffected = Convert.ToInt32(cm.ExecuteNonQuery());
                            }

                        }
                    }
                    else
                    {
                        trans.Rollback();
                        return 0;
                    }


                    if (rowseffected > 0)
                    {
                        rowseffected = 0;
                        using (cm = new SqlCommand("insert into ledger(edate,description,details,type,ref,mrd,d_acode,d_amt,c_acode,c_amt,pidno,per,posted,entryno) " +
                              "values(@date,@desc,@det,@type,@ref,@mrd,@d_code,@amount,@c_code,@amount,@pidno,@period,@posted,@entryno);", con, trans))
                        {


                            cm.Parameters.Add("date", SqlDbType.DateTime);
                            cm.Parameters.Add("desc", SqlDbType.VarChar);
                            cm.Parameters.Add("det", SqlDbType.VarChar);
                            cm.Parameters.Add("type", SqlDbType.NVarChar, 2);
                            cm.Parameters.Add("ref", SqlDbType.Int);
                            cm.Parameters.Add("mrd", SqlDbType.VarChar);
                            cm.Parameters.Add("d_code", SqlDbType.VarChar);
                            cm.Parameters.Add("amount", SqlDbType.Money);
                            cm.Parameters.Add("c_code", SqlDbType.VarChar);
                            cm.Parameters.Add("pidno", SqlDbType.Int);
                            cm.Parameters.Add("period", SqlDbType.VarChar);
                            cm.Parameters.Add("Posted", SqlDbType.Int);
                            cm.Parameters.Add("entryno", SqlDbType.Int);

                            for (int i = 0; i < table.Rows.Count; i++)
                            {
                                cm.Parameters["date"].Value = table.Rows[i]["date"];
                                cm.Parameters["desc"].Value = table.Rows[i]["desc"];
                                cm.Parameters["det"].Value = table.Rows[i]["det"];
                                cm.Parameters["type"].Value = table.Rows[i]["type"];
                                cm.Parameters["mrd"].Value = table.Rows[i]["mrd"];
                                cm.Parameters["ref"].Value = voucherno;
                                cm.Parameters["d_code"].Value = table.Rows[i]["d_code"];
                                cm.Parameters["amount"].Value = table.Rows[i]["amount"];
                                cm.Parameters["c_code"].Value = table.Rows[i]["c_code"];
                                cm.Parameters["pidno"].Value = idno;
                                cm.Parameters["period"].Value = period;
                                cm.Parameters["Posted"].Value = table.Rows[i]["Posted"];
                                cm.Parameters["entryno"].Value = entryno;
                                rowseffected = Convert.ToInt32(cm.ExecuteNonQuery());
                            }

                        }
                        if (rowseffected > 0)
                        {
                            {
                                select_master += idno;
                                select_child += idno;

                                DataSet dt = new DataSet();
                                using (adp = new SqlDataAdapter(select_master + ";" + select_child, con))
                                {
                                    adp.SelectCommand.Transaction = trans;
                                    cmd = new SqlCommandBuilder(adp);
                                    adp.Fill(dt);
                                    dt.Tables[0].TableName = "Master";
                                    dt.Tables[1].TableName = "Child";

                                }

                                String JSONString = JsonConvert.SerializeObject(dt, Formatting.Indented);

                                using (cm = new SqlCommand("insert into Log(Entry,type,edate,s_user,l_user,remarks,data) values(" + Convert.ToInt32(voucherno) + ",'" + type + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + System_Name + "','" + UserName + "','New Entry','" + JSONString + "')", con, trans))
                                {

                                    rows = cm.ExecuteNonQuery();

                                }
                                if (rows > 0)
                                {
                                    trans.Commit();


                                    IsInsert = voucherno;
                                }
                                else
                                {
                                    trans.Rollback();
                                    IsInsert = 0;
                                    return 0;
                                }
                            }
                        }
                        else
                        {
                            trans.Rollback();
                            IsInsert = 0;
                            return 0;
                        }

                    }
                    else
                    {
                        trans.Rollback();
                        IsInsert = 0;
                        return 0;
                    }

                }

            }
            catch (SqlException ex)
            {

            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();

                }
            }
            return IsInsert;
        }

        public int voucher_update(String json)
        {
            JObject json_object = JObject.Parse(json);
            DataTable table = (DataTable)JsonConvert.DeserializeObject(Convert.ToString(json_object["details"]), (typeof(DataTable)));
            String type = Convert.ToString(json_object["type"]);
            String person = Convert.ToString(json_object["person"]);
            String edate = Convert.ToString(json_object["edate"]);
            String maincode = Convert.ToString(json_object["maincode"]);
            String UserName = Convert.ToString(json_object["UserName"]);
            String System_Name = Convert.ToString(json_object["System_Name"]);
            int posted = Convert.ToInt32(json_object["posted"]);
            int rowseffected = 0;
            int IsInsert = 0;
            int rows = 0;
            String S_Name = UserName;
            DataSet da = new DataSet();

            DataTable ds = new DataTable();
            da.Tables.Add(ds);

            SqlTransaction trans = null;
            int entryno = Convert.ToInt32(json_object["entryno"]);
            int voucherno = Convert.ToInt32(json_object["voucher_no"]);
            int idno = Convert.ToInt32(json_object["idno"]);
            String period = json_object["period"].ToString();
            String select_master = "SELECT vm.* FROM VoucherMaster VM INNER JOIN VoucherDetail VD ON VM.idno=VD.MEntry WHERE VM.type='" + type + "' and vm.idno=" + idno;
            String select_child = "SELECT vd.* FROM VoucherMaster VM INNER JOIN VoucherDetail VD ON VM.idno=VD.MEntry WHERE VM.type='" + type + "' and vm.idno=" + idno;
            String Update_Master = "update VoucherMaster set maincode=@mcode,person=@person,edate=@edate,posted=@posted where idno=@idno and  Entry=@entry and type=@type";

            String Delete = " delete  from VoucherDetail  where id not in(select distinct issued from VoucherDetail where issued is not null) and Mentry=" + idno + ";" +
                            " delete from ledger where type='" + type + "' and ref=" + voucherno + " and pidno=" + idno;

            try
            {
                using (con = new SqlConnection(cs))
                {
                    con.Open();
                    trans = con.BeginTransaction();



                    using (cm = new SqlCommand(Update_Master, con, trans))
                    {
                        cm.Parameters.AddWithValue("idno", idno);
                        cm.Parameters.AddWithValue("Entry", voucherno);
                        cm.Parameters.AddWithValue("type", type);
                        cm.Parameters.AddWithValue("person", person);
                        cm.Parameters.AddWithValue("mcode", maincode);
                        cm.Parameters.AddWithValue("edate", edate);
                        cm.Parameters.AddWithValue("entryno", entryno);
                        cm.Parameters.AddWithValue("posted", posted);
                        rowseffected = Convert.ToInt32(cm.ExecuteNonQuery());

                    }

                    if (rowseffected > 0)
                    {
                        rowseffected = 0;

                        using (cm = new SqlCommand(Delete, con, trans))
                        {
                            rowseffected = cm.ExecuteNonQuery();

                        }
                    }
                    else
                    {
                        trans.Rollback();
                        return 0;
                    }
                    if (rowseffected > 0)
                    {
                        using (cm = new SqlCommand("insert into VoucherDetail(Mentry,acode,description,debit,credit,chequeno,cheqdate,issued,Bank,Chequetitle) values(@Mentry,@acode,@desc,@debit,@credit,@chequeno,@chequedate,@issued,@bank,@chequetitle);", con, trans))
                        {

                            cm.Parameters.Add("Mentry", SqlDbType.Int);
                            cm.Parameters.Add("acode", SqlDbType.VarChar);
                            cm.Parameters.Add("desc", SqlDbType.VarChar);
                            cm.Parameters.Add("debit", SqlDbType.Money);
                            cm.Parameters.Add("credit", SqlDbType.Money);
                            cm.Parameters.Add("chequeno", SqlDbType.VarChar);
                            cm.Parameters.Add("chequedate", SqlDbType.Date);
                            cm.Parameters.Add("issued", SqlDbType.Int);
                            cm.Parameters.Add("Bank", SqlDbType.VarChar);
                            cm.Parameters.Add("Chequetitle", SqlDbType.VarChar);

                            for (int i = 0; i < table.Rows.Count; i++)
                            {
                                cm.Parameters["Mentry"].Value = idno;
                                cm.Parameters["acode"].Value = table.Rows[i]["acode"];
                                cm.Parameters["desc"].Value = table.Rows[i]["desc"];
                                cm.Parameters["debit"].Value = table.Rows[i]["debit"];
                                cm.Parameters["credit"].Value = table.Rows[i]["credit"];
                                cm.Parameters["chequeno"].Value = table.Rows[i]["chequeno"];
                                cm.Parameters["chequedate"].Value = table.Rows[i]["chequedate"];
                                cm.Parameters["issued"].Value = table.Rows[i]["issued"];
                                cm.Parameters["Bank"].Value = table.Rows[i]["Bank"];
                                cm.Parameters["Chequetitle"].Value = table.Rows[i]["Chequetitle"];
                                rowseffected = Convert.ToInt32(cm.ExecuteNonQuery());
                            }

                        }
                    }
                    else
                    {
                        trans.Rollback();
                        return 0;
                    }


                    if (rowseffected > 0)
                    {
                        rowseffected = 0;
                        using (cm = new SqlCommand("insert into ledger(edate,description,details,type,ref,mrd,d_acode,d_amt,c_acode,c_amt,pidno,per,posted,entryno) " +
                              "values(@date,@desc,@det,@type,@ref,@mrd,@d_code,@amount,@c_code,@amount,@pidno,@period,@posted,@entryno);", con, trans))
                        {


                            cm.Parameters.Add("date", SqlDbType.DateTime);
                            cm.Parameters.Add("desc", SqlDbType.VarChar);
                            cm.Parameters.Add("det", SqlDbType.VarChar);
                            cm.Parameters.Add("type", SqlDbType.NVarChar, 2);
                            cm.Parameters.Add("ref", SqlDbType.Int);
                            cm.Parameters.Add("mrd", SqlDbType.VarChar);
                            cm.Parameters.Add("d_code", SqlDbType.VarChar);
                            cm.Parameters.Add("amount", SqlDbType.Money);
                            cm.Parameters.Add("c_code", SqlDbType.VarChar);
                            cm.Parameters.Add("pidno", SqlDbType.Int);
                            cm.Parameters.Add("period", SqlDbType.VarChar);
                            cm.Parameters.Add("Posted", SqlDbType.Int);
                            cm.Parameters.Add("entryno", SqlDbType.Int);

                            for (int i = 0; i < table.Rows.Count; i++)
                            {
                                cm.Parameters["date"].Value = table.Rows[i]["date"];
                                cm.Parameters["desc"].Value = table.Rows[i]["desc"];
                                cm.Parameters["det"].Value = table.Rows[i]["det"];
                                cm.Parameters["type"].Value = table.Rows[i]["type"];
                                cm.Parameters["mrd"].Value = table.Rows[i]["mrd"];
                                cm.Parameters["ref"].Value = voucherno;
                                cm.Parameters["d_code"].Value = table.Rows[i]["d_code"];
                                cm.Parameters["amount"].Value = table.Rows[i]["amount"];
                                cm.Parameters["c_code"].Value = table.Rows[i]["c_code"];
                                cm.Parameters["pidno"].Value = idno;
                                cm.Parameters["period"].Value = period;
                                cm.Parameters["Posted"].Value = table.Rows[i]["Posted"];
                                cm.Parameters["entryno"].Value = entryno;
                                rowseffected = Convert.ToInt32(cm.ExecuteNonQuery());
                            }

                        }
                        if (rowseffected > 0)
                        {
                            {

                                DataSet dt = new DataSet();
                                using (adp = new SqlDataAdapter(select_master + ";" + select_child, con))
                                {
                                    adp.SelectCommand.Transaction = trans;
                                    cmd = new SqlCommandBuilder(adp);
                                    adp.Fill(dt);
                                    dt.Tables[0].TableName = "Master";
                                    dt.Tables[1].TableName = "Child";

                                }

                                String JSONString = JsonConvert.SerializeObject(dt, Formatting.Indented);

                                using (cm = new SqlCommand("insert into Log(Entry,type,edate,s_user,l_user,remarks,data) values(" + Convert.ToInt32(voucherno) + ",'" + type + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + System_Name + "','" + UserName + "','Modified Entry','" + JSONString + "')", con, trans))
                                {

                                    rows = cm.ExecuteNonQuery();

                                }
                                if (rows > 0)
                                {
                                    trans.Commit();


                                    IsInsert = voucherno;
                                }
                                else
                                {
                                    trans.Rollback();
                                    IsInsert = 0;
                                    return 0;
                                }
                            }
                        }
                        else
                        {
                            trans.Rollback();
                            IsInsert = 0;
                            return 0;
                        }

                    }
                    else
                    {
                        trans.Rollback();
                        IsInsert = 0;
                        return 0;
                    }

                }

            }
            catch (SqlException ex)
            {

            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();

                }
            }
            return IsInsert;
        }

        public int voucher_delete(String json)
        {
            JObject json_object = JObject.Parse(json);
            int idno = Convert.ToInt32(json_object["idno"]);
            int voucher_no = Convert.ToInt32(json_object["voucher"]);
            String type = json_object["type"].ToString();
            String UserName = json_object["UserName"].ToString();
            String System = json_object["System"].ToString();
            int IsDelete = 0;
            String Xml = "";

            String select_master = "SELECT vm.* FROM VoucherMaster VM INNER JOIN VoucherDetail VD ON VM.idno=VD.MEntry WHERE VM.type='" + type + "' and vm.Entry=" + voucher_no;
            String select_child = "SELECT vd.* FROM VoucherMaster VM INNER JOIN VoucherDetail VD ON VM.idno=VD.MEntry WHERE VM.type='" + type + "' and vm.Entry=" + voucher_no;
            String select = select_master + ";" + select_child;

            String Delete = "DELETE  FROM VoucherDetail WHERE Mentry=" + idno + "; delete from VoucherMaster where idno=" + idno + ";delete from ledger where type='" + type + "' and ref=" + voucher_no + " and pidno=" + idno;
            SqlTransaction trans = null;


            DataSet dt = new DataSet();


            try
            {
                using (con = new SqlConnection(cs))
                {
                    con.Open();
                    trans = con.BeginTransaction();

                    cm = new SqlCommand(select, con, trans);
                    using (adp = new SqlDataAdapter(select_master + ";" + select_child, con))
                    {
                        adp.SelectCommand.Transaction = trans;
                        cmd = new SqlCommandBuilder(adp);
                        adp.Fill(dt);
                        dt.Tables[0].TableName = "Master";
                        dt.Tables[1].TableName = "Child";

                    }

                    String JSONString = JsonConvert.SerializeObject(dt, Formatting.Indented);



                    using (cm = new SqlCommand("Insert into Log(Entry,type,edate,s_user,l_user,remarks,data) values(" + voucher_no + ",'" + type + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + System + "','" + UserName + "','Delete Entry','" + JSONString + "')", con, trans))
                    {
                        cm.ExecuteNonQuery();
                    }
                    using (cm = new SqlCommand(Delete, con, trans))
                    {
                        int a = cm.ExecuteNonQuery();
                        if (a > 0)
                        {
                            trans.Commit();
                            IsDelete = voucher_no;
                        }
                        else
                        {
                            trans.Rollback();
                            IsDelete = 0;
                        }
                    }
                }

            }
            catch (SqlException ex)
            {
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
            return IsDelete;
        }

        public String report_chq(String json)
        {

            JObject json_object = JObject.Parse(json);

            String token = json_object["token"].ToString();
            if (Authorization(token) <= 0)
            {
                json = "In-Valid";
                return json;
            }

            String query = "";
            int index = Convert.ToInt32(json_object["index"].ToString());
            String from = json_object["from"].ToString();
            String to = json_object["to"].ToString();
            String chq_from = json_object["chq_from"].ToString();
            String chq_to = json_object["chq_to"].ToString();
            String bank = json_object["bank"].ToString();
            String debit_ac = json_object["debit_ac"].ToString();
            String credit_ac = json_object["credit_ac"].ToString();
            String chq = json_object["chq"].ToString();
            String amount = Convert.ToDecimal(json_object["amount"].ToString()).ToString();

            String where = "";

            if (from != String.Empty)
            {
                where += " and cast(vm.edate as Date)>='" + from + "'";
            }
            if (to != String.Empty)
            {
                where += " and cast(vm.edate as Date)<='" + to + "'";
            }
            if (chq_from != String.Empty)
            {
                where += " and cast(vd.cheqdate as Date)>='" + chq_from + "'";
            }
            if (chq_to != String.Empty)
            {
                where += " and cast(vd.cheqdate as Date)<='" + chq_to + "'";
            }
            if (bank != String.Empty)
            {
                where += " and vd.bank='" + bank + "'";
            }
            if (debit_ac != String.Empty)
            {
                where += " and vm.maincode='" + debit_ac.Trim() + "'";
            }
            if (credit_ac != String.Empty)
            {
                where += " and vd.acode='" + credit_ac.Trim() + "'";
            }
            if (chq != String.Empty)
            {

                where += "and vd.chequeno='" + chq + "'";
            }
            if (Convert.ToDecimal(amount) != -1)
            {

                where += "and (isnull(vd.debit,0)+isnull(vd.credit,0))=" + Convert.ToDecimal(amount);
            }
            if (index == 0)
            {
                query = " select Convert(varchar(11) ,vm.edate,105) as Date,vm.Entry,vd.Description,Convert(varchar(11) ,vd.cheqdate,105) as ChqDate,"
                        + " b.name as Bank,vd.chequeno as Chqno,vd.Chequetitle as ChqTitle,ac2.title as Debit,ac.title as Credit,ISNULL(vd.debit,0)+ISNULL(vd.credit,0) as Amount "
                        + " from VoucherMaster vm inner join VoucherDetail vd on vm.idno=vd.MEntry "
                        + " inner join ac on ac.code=vd.acode  inner join ac ac2 on ac2.code=vm.maincode "
                        + " inner join Bank b on b.Code=vd.Bank "
                        + " where vm.type='BR' and  "
                        + " vd.Id not in(select issued from VoucherDetail where issued is not null) " + where;

            }
            if (index == 1)
            {
                query = " select BP.Date,BP.Entry,BP.ChqDate,BP.Bank,"
                       + " BP.Chqno,BP.ChqTitle,case when BR.Credit is null then BP.Credit else BR.Credit end as Rec_From,BP.Debit as Paid_To,RT.Credit as Return_From,BP.Amount from ("

                       + " select vd.Id,vd.issued,Convert(varchar(11) ,vm.edate,105) as Date,vm.Entry,vd.Description,Convert(varchar(11) ,vd.cheqdate,105) as ChqDate,b.name as Bank,vd.chequeno as Chqno,vd.Chequetitle as ChqTitle,ac2.title as Debit,ac.title as Credit,ISNULL(vd.debit,0)+ISNULL(vd.credit,0) as Amount from VoucherMaster vm inner join VoucherDetail vd on vm.idno=vd.MEntry "
                       + " inner join ac on ac.code=vd.acode inner join ac ac2 on ac2.code=vm.maincode inner join Bank b on b.Code=vd.Bank where vm.type='BP')BP left join( "
                       + "  select vd.Id,vd.issued,Convert(varchar(11) ,vm.edate,105) as Date,vm.Entry,vd.Description,Convert(varchar(11) ,vd.cheqdate,105) as ChqDate,b.name as Bank,vd.chequeno as Chqno,vd.Chequetitle as ChqTitle,ac2.title as Debit,ac.title as Credit,ISNULL(vd.debit,0)+ISNULL(vd.credit,0) as Amount  from VoucherMaster vm inner join VoucherDetail vd on vm.idno=vd.MEntry "
                       + "  inner join ac on ac.code=vd.acode inner join ac ac2 on ac2.code=vm.maincode inner join Bank b on b.Code=vd.Bank where vm.type='BR' )BR on BP.issued=BR.Id left join( "

                       + "  select vd.Id,vd.issued,Convert(varchar(11) ,vm.edate,105) as Date,vm.Entry,vd.Description,Convert(varchar(11) ,vd.cheqdate,105) as ChqDate,b.name as Bank,vd.chequeno as Chqno,vd.Chequetitle as ChqTitle,ac2.title as Debit,ac.title as Credit,ISNULL(vd.debit,0)+ISNULL(vd.credit,0) as Amount  from VoucherMaster vm inner join VoucherDetail vd on vm.idno=vd.MEntry "
                       + "  inner join ac on ac.code=vd.acode inner join ac ac2 on ac2.code=vm.maincode inner join Bank b on b.Code=vd.Bank where vm.type='RT')RT on RT.issued=BP.Id ";

            }

            if (index == 2)
            {

                query = " select Convert(varchar(11) ,vm.edate,105) as Date,vm.Entry,vd.Description,Convert(varchar(11) ,vd.cheqdate,105) as ChqDate,"
                                       + " b.name as Bank,vd.chequeno as Chqno,vd.Chequetitle as ChqTitle,ac2.title as Debit,ac.title as Credit,ISNULL(vd.debit,0)+ISNULL(vd.credit,0) as Amount "
                                       + " from VoucherMaster vm inner join VoucherDetail vd on vm.idno=vd.MEntry "
                                       + " inner join ac on ac.code=vd.acode  inner join ac ac2 on ac2.code=vm.maincode "
                                       + " inner join Bank b on b.Code=vd.Bank "
                                       + " where vm.type='RT' " + where;

            }

            json = IList_GetTable(query);
            return json;
        }

        public String voucher_entry(int entry, String type)
        {
            String json = "";
            String query = "";
            if (type == "BR" || type == "RT")
            {
                query = "select vm.Entry,vm.maincode,vm.person,vm.edate,Convert(varchar(105) ,vd.cheqdate,105) as cheqdate,vd.acode,vd.description,vd.debit,vd.credit,vd.chequeno,vd.debit,vd.bank,vd.chequetitle,vd.id,vm.entry2,vm.posted,vm.warehouse" +
              " ,(select issued from VoucherDetail where issued=vd.Id) as Issued,vd.issued as issudb from VoucherMaster vm,VoucherDetail vd where vm.idno=vd.MEntry and vm.type='" + type + "' and vm.idno=" + entry;

            }
            else if (type == "BP")
            {
                query = "select vm.*,vd.*,rt.Paid from VoucherMaster vm inner join VoucherDetail vd on vm.idno=vd.MEntry " +
                        " left join(Select vd.issued as Paid from VoucherMaster vm inner join VoucherDetail vd on vm.idno=vd.MEntry " +
                        " where vm.type='RT')rt on rt.Paid=vd.Id  " +
                        " Where vm.type='BP' and vm.idno=" + entry;
            }
            json = IList_GetTable(query);
            return json;
        }

        public String voucher_list(String type)
        {
            String json = "";
            String Accounts = "SELECT code as acode,ac.Title FROM AC   where   ac.type='Child'  order by title;";
            String Type = "select idno,Entry from VoucherMaster where type='" + type.Trim() + "'; Select   b.code,b.name from Bank b;";
            String warehouse = "Select id as code,name from warehouse;";
            String Query = Accounts + Type + warehouse;
            json = IList_GetDataSet(Query);
            return json;

        }

        public String GetVouchers(String json)
        {
            JObject json_object = JObject.Parse(json);
            String token = json_object["token"].ToString();
            if (token != app_key)
            {
                return "404";
            }

            String query = "";
            String data = "";
            int idno = Convert.ToInt32(json_object["id"].ToString());

            query =  " select *,convert(varchar,cast(date as date), 105) as edate from General_Journal  where key_pk=" + idno + " Order by key_pk desc;";
            query += " select * from cash_book_detail where gjno=" + idno;

            query += " select * from inspection_master where id in ( " +
                     " select top 1 insp_id from General_Journal where op_type='IP' and key_pk=" + idno + "); ";

            query += " select * from Inspection_Invoice_Master where key_pk in ( " +
                     " select top 1 insp_inv from General_Journal where op_type='IP' and key_pk=" + idno + ") ;";

            data = IList_GetDataSet(query);
            return data;
        }
    }
}