using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using Newtonsoft.Json.Linq;
using System.Data;
using Newtonsoft.Json;
using System.Data.SqlClient;
using System.IO;
using System.Globalization;
using System.Configuration;
using System.Security.Cryptography;

namespace WorkShop_Services
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service1" in code, svc and config file together.
    // NOTE: In order to launch WCF Test Client for testing this service, please select Service1.svc or Service1.svc.cs at the Solution Explorer and start debugging.
    public partial class Service1 : IService1
    {

        //String cs = @"Data Source=.\MSSQLSERVER2016;UID=fahad;PWD=Pattang@123;Initial Catalog=asad;integrated security=false;Connect Timeout=60";
        //String cs = "Data Source=SISTER;UID=sa;PWD=pattang123;Initial Catalog=asad;integrated security=false;Connect Timeout=60";
        String cs = System.Configuration.ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;

        // String cs = "Data Source=DESKTOP-K0MOT95;UID=sa;PWD=pattang123;Initial Catalog=asad;integrated security=false;Connect Timeout=60";
        String payroll_cs = "Data Source=DESKTOP-K1LLNI1;UID=sa;PWD=pattang123;Initial Catalog=hfppay;integrated security=false;Connect Timeout=60";

        SqlConnection con = null;
        SqlCommand cm = null;
        SqlDataAdapter adp = null;
        SqlCommandBuilder cmd = null;
        DataTable dt = null;
        DataSet ds = null;
        String app_key = "PO2PRNH0VOTEBNC";
        Random random = new Random();

        public String Login(String username, String password)
        {

            dt = new DataTable();
            String old;
            String json = "";

            old = IList_ExecuteScalerValue_String("select USERS_PASS from users where USERS_NAME=N'" + username + "'");

            if (old.Trim() == String.Empty)
            {
                con.Close();
                return "";
            }

            if (!PasswordHashing.Decrypt(old).Equals(password))
            {
                return "";
            }

            String query = " select  * from USERS u inner join ROLEMASTER rm on u.ROLE_ID=rm.ROLE_ID " +
                           " inner join RoleDetail rd on u.ROLE_ID=rd.Role " +
                           " where u.users_name='" + username + "'" +
                           " and ISACTIVE=1";

            json = IList_GetTable(query);

            return json;
        }


        public int Authorization(String key)
        {
            return 1;
            String query = "Select USERS from users where token='" + key + "'";
            int isValid = 0;
            try
            {
                using (con = new SqlConnection(cs))
                {
                    con.Open();
                    cm = new SqlCommand(query, con);
                    if (cm.ExecuteScalar() != null)
                    {
                        isValid = Convert.ToInt32(cm.ExecuteScalar());
                    }


                }
            }
            catch (Exception ex)
            {
                excp(ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
            return isValid;
        }

        public String App_Version()
        {
            String query = " Select app_version from companysetup ";

            String isValid = "";
            try
            {
                using (con = new SqlConnection(cs))
                {
                    con.Open();
                    cm = new SqlCommand(query, con);
                    isValid = Convert.ToString(cm.ExecuteScalar());

                }
            }
            catch (Exception ex)
            {
                excp(ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
            return isValid;
        }

        void update(string version)
        {


            if (version == "1.4")
            {

                String query = "alter table Stock_Transfer_Detail add remarks varchar(500); ";
                query += "alter table stock add remarks varchar(500);";
                query += "update companysetup set app_version = '1.5'";
                IList_execute(query);

            }

            if (version == "1.5")
            {

                String query = "alter table General_Journal alter column prtclar varchar(250); ";
                query += "alter table General_Journal alter column tnr varchar(1000);";
                query += "update companysetup set app_version = '1.6'";
                IList_execute(query);

            }

            if (version.Trim().Equals("1.6"))
            {

                String query = "update companysetup set app_version = '1.7';";

                IList_execute(query);

            }
            if (version.Trim().Equals("1.7"))
            {

                String query = "alter table Purchase_Detail add arrival_no int;";
                query += "alter table Purchase_Detail add arrival_key int;";
                query += "update companysetup set app_version = '1.8';";

                IList_execute(query);

            }

            if (version.Trim().Equals("1.8"))
            {

                String query = "insert into module values('hoc','Head Of Customer','',null);";
                query += "insert into module values('hos','Head Of Supplier','',null);";
                query += "insert into module values('kpt_place','Kept Place For Items','',null);";
                query += "alter table ac add person nvarchar(250);";
                query += "update companysetup set app_version = '1.9';";

                IList_execute(query);

            }
            if (version.Trim().Equals("1.9"))
            {

                String query = "";
                query += "update companysetup set app_version = '1.10';";

                IList_execute(query);

            }
            if (version.Trim().Equals("1.10"))
            {

                String query = "";
                query += "update companysetup set app_version = '1.11';";

                IList_execute(query);

            }

        }

        public int AddAc(String json)
        {
            JObject json_object = JObject.Parse(json);
            String token = json_object["token"].ToString();
            if (token != app_key)
            {
                return 404;
            }
            String data = "";
            String query = "";
            int rowsEffected = 0;

            {
                try
                {
                    String ttype = json_object["type"].ToString();
                    String ac = IList_ExecuteScalerValue_String("select top 1 code from ac where parent='" + json_object["parent"].ToString() + "' order by REVERSE( LEFT(REVERSE(code), CHARINDEX('-', REVERSE(code)) - 1)) desc");
                    if (ac == string.Empty)
                    {
                        ac = json_object["parent"].ToString() + "-00001";
                    }
                    else
                    {
                        String ne = (Convert.ToInt32(ac.Substring(ac.LastIndexOf('-') + 1)) + 1).ToString().PadLeft(5, '0');
                        ac = ac.Remove(ac.LastIndexOf("-")) + "-" + ne;
                    }

                    String name = json_object["name"].ToString();
                    String parent = json_object["parent"].ToString();
                    String date = Convert.ToDateTime(json_object["date"].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
                    String bal = (json_object["balance"]).ToString();
                    String type = json_object["type"].ToString();
                    String iden = json_object["iden"].ToString();
                    String nat = json_object["nat"].ToString();
                    String limit = ((json_object["limit"].ToString().Trim()) == "" ? "null" : Convert.ToDecimal(json_object["limit"].ToString()).ToString());
                    String person = json_object["person"].ToString(); ;

                    if (json_object["iden"].ToString().Trim().Equals("OTAC"))
                    {
                        if (json_object["action"].ToString().Trim().Equals("add"))
                        {

                            query = "Insert Into AC(code,title,parent,type,clkdate,bank_Ac,opening_balance,t_type,iden,nat,d_limit,person) values(" +
                                "'" + ac + "','" + name + "','" + parent + "','Child','" + date + "','" + json_object["bank"].ToString() + "'" +
                                   "," + bal + ",'" + type + "','" + iden + "','" + nat + "'," + limit + ",'" + person + "');SELECT SCOPE_IDENTITY(); ";

                            rowsEffected = IList_ExecuteScalerValue(query);
                            if (rowsEffected > 0)
                            {
                                query += " insert into Ledger(edate,description,details,type,d_acode,d_amt,c_acode,c_amt,pidno) values('" + date + "'," +
                                 "'OPENING BALANCE','OPENING BALANCE','OP','" + (ttype == "D" ? ac : "20-001-00001") + "'," + bal + ",'" + (ttype == "C" ? ac : "20-001-00001") + "'," +
                                 bal + "," + rowsEffected + ")";

                                String Log = " insert into Log(Entry,type,edate,s_user,l_user,remarks,data,users_id) values" +
                                            " (" + rowsEffected + ",'ac','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + (json_object["s_user"].ToString()) + "','" + (json_object["User"].ToString()) + "','New Entry',''," + (json_object["UserId"].ToString()) + ")";
                                IList_execute(Log);
                            }

                        }
                        else
                        {
                            query = " Update ac set title='" + json_object["name"].ToString() + "',clkdate='" + Convert.ToDateTime(json_object["date"].ToString()).ToString("yyyy-MM-dd HH:mm:ss") + "'" +
                                    ",bank_ac='" + json_object["bank"].ToString() + "',opening_balance=" + (json_object["balance"]).ToString() +
                                    ",t_type='" + json_object["type"].ToString() + "',d_limit=" + ((json_object["limit"].ToString()).Trim() == "" ? "null" : Convert.ToDecimal(json_object["limit"].ToString()).ToString()) + ",person='" + person + "' where rtrim(code)='" + json_object["code"].ToString().Trim() + "'";

                            rowsEffected = IList_execute(query);
                            if (rowsEffected > 0)
                            {
                                query = "select idno from ac where code='" + json_object["code"].ToString().Trim() + "'";
                                int id = IList_ExecuteScalerValue(query);

                                query += " update Ledger set edate='" + date + "',d_acode='" + (ttype == "D" ? json_object["code"].ToString() : "20-001-00001") + "',d_amt=" + Convert.ToDecimal(json_object["balance"].ToString()).ToString()
                                      + ",c_acode='" + (ttype == "C" ? json_object["code"].ToString() : "20-001-00001")
                                      + "',c_amt=" + (json_object["balance"].ToString()).ToString() + " where (d_acode='" + json_object["code"].ToString() + "' or c_acode='" + json_object["code"].ToString() + "') and type='OP'";

                                String Log = " insert into Log(Entry,type,edate,s_user,l_user,remarks,data,users_id) values" +
                                            " (" + id + ",'ac','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + (json_object["s_user"].ToString()) + "','" + (json_object["User"].ToString()) + "','Modified Entry',''," + (json_object["UserId"].ToString()) + ")";
                                IList_execute(Log);
                            }
                        }

                    }
                    else
                    {
                        if (json_object["action"].ToString().Trim().Equals("add"))
                        {

                            query = "Insert Into AC(code,title,parent,type,clkdate,bank_ac,opening_balance,t_type,iden,nat,city_code,market,d_limit,person) values(" +
                                "'" + ac + "','" + json_object["name"].ToString() + "','" + json_object["parent"].ToString() + "','Child','" +
                                Convert.ToDateTime(json_object["date"].ToString()).ToString("yyyy-MM-dd HH:mm:ss") + "','" + json_object["bank"].ToString() + "'" +
                                "," + Convert.ToDecimal(json_object["balance"].ToString()).ToString() + ",'" + json_object["type"].ToString() + "','" +
                                 json_object["iden"].ToString() + "','" + json_object["nat"].ToString() + "','" + json_object["city"].ToString() + "'," +
                                 (json_object["market"].ToString() == "null" ? "null" : "'" + json_object["market"].ToString() + "'") + "," +
                                 ((json_object["limit"].ToString().Trim()) == "" ? "null" : Convert.ToDecimal(json_object["limit"].ToString()).ToString()) + ",'" + person + "');SELECT SCOPE_IDENTITY(); ";

                            rowsEffected = IList_ExecuteScalerValue(query);
                            if (rowsEffected > 0)
                            {
                                query += " insert into Ledger(edate,description,details,type,d_acode,d_amt,c_acode,c_amt,pidno) values('" + Convert.ToDateTime(json_object["date"].ToString()).ToString("yyyy-MM-dd HH:mm:ss") + "'," +
                               "'OPENING BALANCE','OPENING BALANCE','OP','" + (ttype == "D" ? json_object["code"].ToString() : "20-001-00001") + "'," + Convert.ToDecimal(json_object["balance"].ToString()).ToString() + ",'" + (ttype == "C" ? json_object["code"].ToString() : "20-001-00001") + "'," +
                               Convert.ToDecimal(json_object["balance"].ToString()).ToString() + "," + rowsEffected + ")";

                                String Log = " insert into Log(Entry,type,edate,s_user,l_user,remarks,data,users_id) values" +
                                            " (" + rowsEffected + ",'ac','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + (json_object["s_user"].ToString()) + "','" + (json_object["User"].ToString()) + "','New Entry',''," + (json_object["UserId"].ToString()) + ")";
                                IList_execute(Log);
                            }

                        }
                        else
                        {
                            query = " Update ac set title='" + json_object["name"].ToString() + "',clkdate='" + Convert.ToDateTime(json_object["date"].ToString()).ToString("yyyy-MM-dd HH:mm:ss") + "'" +
                                  ",bank_ac='" + json_object["bank"].ToString() + "',opening_balance=" + Convert.ToDecimal(json_object["balance"].ToString()).ToString() +
                                  ",t_type='" + json_object["type"].ToString() + "',city_code='" + json_object["city"].ToString() + "',market='" + json_object["market"].ToString() + "',d_limit=" + ((json_object["limit"].ToString().Trim()) == "" ? "null" : Convert.ToDecimal(json_object["limit"].ToString()).ToString()) + ",person='" + person + "' where rtrim(code)='" + json_object["code"].ToString().Trim() + "'";
                            rowsEffected = IList_execute(query);
                            if (rowsEffected > 0)
                            {
                                query += " update Ledger set edate='" + date + "',d_acode='" + (ttype == "D" ? json_object["code"].ToString() : "20-001-00001") + "',d_amt=" + Convert.ToDecimal(json_object["balance"].ToString()).ToString()
                                 + ",c_acode='" + (ttype == "C" ? json_object["code"].ToString() : "20-001-00001")
                                 + "',c_amt=" + Convert.ToDecimal(json_object["balance"].ToString()).ToString() + " where (d_acode='" + json_object["code"].ToString() + "' or c_acode='" + json_object["code"].ToString() + "') and type='OP'";

                                query = "select idno from ac where code='" + json_object["code"].ToString().Trim() + "'";
                                int id = IList_ExecuteScalerValue(query);
                                String Log = " insert into Log(Entry,type,edate,s_user,l_user,remarks,data,users_id) values" +
                                            " (" + id + ",'ac','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + (json_object["s_user"].ToString()) + "','" + (json_object["User"].ToString()) + "','Modified Entry',''," + (json_object["UserId"].ToString()) + ")";
                                IList_execute(Log);
                            }

                        }
                    }



                }
                catch (Exception ex)
                {
                    excp(ex.Message);
                }
            }

            return rowsEffected;
        }

        public string IsValidUser(String json)
        {
            JObject json_object = JObject.Parse(json);
            int id = Convert.ToInt32(json_object["id"].ToString());
            String app_version = json_object["version"].ToString();
            string version = IList_ExecuteScalerValue_String("select app_version from companysetup");
            string data = "";
            update(version);
            version = IList_ExecuteScalerValue_String("select app_version from companysetup");


            if (version.ToString().Trim() == app_version.Trim())
            {

                String query = "select u.USERS,u.USERS_NAME,rm.ROLE_NAME from USERS u inner join ROLEMASTER rm on rm.ROLE_ID=u.ROLE_ID where u.USERS=" + id;
                data = IList_GetTable(query);
            }
            else
            {
                data = "-1";
            }

            return data;
        }

        public string GetData()
        {
            return string.Format("You entered");
        }

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

            String query = " select md.MODULE_TYPE,isnull(rd.Can_Access,0) as Can_Access, isnull(rd.Can_Add,0) as Can_Add, isnull(rd.Can_Edit,0) as Can_Edit,  isnull(rd.Can_Delete,0) as Can_Delete, isnull(rd.Can_Print,0) as Can_Print, isnull(rd.Can_Post,0) as Can_Post,isnull(rd.Can_Docx,0) as Can_Docx, isnull(rd.Can_Special,0) as Can_Special,rd.time,rd.unit " +
                         " from MODULE md  left join RoleDetail rd on md.MODULE_TYPE=rd.Module and rd.Role=" + idno +
                         " order by md.MODULE_NAME ";

            return IList_GetTable(query);
        }

        public int IAccess(String access, int id, string type)
        {
            int isaccess = 0;
            String q1 = "select " + access + " from USERS u ";
            String q2 = "inner join ROLEMASTER rm on u.ROLE_ID=rm.ROLE_ID ";
            String q3 = "inner join RoleDetail rd on u.ROLE_ID=rd.Role ";
            String q4 = "where rd.Module='" + type + "' and u.USERS=" + id;
            String query = q1 + q2 + q3 + q4;

            int rows = 0;
            try
            {
                using (con = new SqlConnection(cs))
                {
                    con.Open();
                    cm = new SqlCommand(q1 + q2 + q3 + q4, con);
                    var val = cm.ExecuteScalar();
                    if (val != DBNull.Value)
                    {
                        rows = Convert.ToInt32(cm.ExecuteScalar());
                    }
                }
            }
            catch (Exception ex)
            {
                excp(ex.Message);
            }
            finally
            {

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

                }
            }

            if (rows > 0)
            {
                isaccess = 1;
            }

            return isaccess;


        }

        public int IList_Json_Insert(String json, String table)
        {
            int rowsEffected = 0;
            String query = "";
            int update = 0;
            JObject json_object = JObject.Parse(json);
            String token = json_object["token"] == null ? "" : json_object["token"].ToString();

            if (Authorization(token) <= 0)
            {
                return 404;
            }

            if (table == "state")
            {
                if (json_object["id"].ToString().Trim() == String.Empty)
                {
                    query = "Insert Into state(Code,Name) values('" + json_object["code"].ToString() + "','" + json_object["name"].ToString() + "'); SELECT SCOPE_IDENTITY() AS CODE";

                }
                else
                {
                    query = " Update state set code='" + json_object["code"].ToString() + "',name='" + json_object["name"].ToString() + "' where id=" + json_object["id"].ToString();
                }
                rowsEffected = IList_execute(query);
            }

            else if (table == "market")
            {
                if (json_object["id"].ToString().Trim() == String.Empty)
                {
                    query = "Insert Into market(Code,Name,city) values('" + json_object["code"].ToString() + "','" + json_object["name"].ToString() + "','" + json_object["head"].ToString() + "'); SELECT SCOPE_IDENTITY() AS CODE";

                }
                else
                {
                    query = " Update market set code='" + json_object["code"].ToString() + "',name='" + json_object["name"].ToString() + "',city='" + json_object["head"].ToString() + "' where id=" + json_object["id"].ToString();
                }
                rowsEffected = IList_execute(query);
            }

            else if (table == "city")
            {
                if (json_object["id"].ToString().Trim() == String.Empty)
                {
                    query = "Insert Into city(Code,Name,state) values('" + json_object["code"].ToString() + "','" + json_object["name"].ToString() + "','" + json_object["head"].ToString() + "'); SELECT SCOPE_IDENTITY() AS CODE";

                }
                else
                {
                    query = " Update city set code='" + json_object["code"].ToString() + "',name='" + json_object["name"].ToString() + "',state='" + json_object["head"].ToString() + "' where idno=" + json_object["id"].ToString();
                }
                rowsEffected = IList_execute(query);
            }

            else if (table == "subhead")
            {
                if (json_object["action"].ToString().Trim() == "add")
                {
                    query = "insert into ac(code,title,type,parent,nat)  values('" + json_object["code"].ToString() + "','" + json_object["name"].ToString() + "','Parent','" + json_object["head"].ToString() + "','" + json_object["nat"].ToString() + "');";

                }
                else
                {
                    query = " Update ac set title='" + json_object["name"].ToString() + "' where rtrim(code)='" + json_object["code"].ToString().Trim() + "';";
                }
                rowsEffected = IList_execute(query);
            }

            else if (table == "maker" || table == "brand" || table == "reference" || table == "origin")
            {

                if (json_object["code"].ToString().Trim() == String.Empty)
                {
                    query = " select top 1 cast(code as int) from " + table + " order by cast (code as Int)  desc ";
                    string value = (Convert.ToInt32(IList_ExecuteScalerValue(query)) + 1).ToString().PadLeft(3, '0').ToString();
                    query = "insert into " + table + "(code,name,short_code)  values('" + value + "','" + json_object["name"].ToString() + "','" + json_object["short_code"].ToString() + "');";

                }
                else
                {
                    query = " Update " + table + " set name='" + json_object["name"].ToString() + "',short_code='" + json_object["short_code"].ToString() + "' where rtrim(code)='" + json_object["code"].ToString().Trim() + "';";
                }
                rowsEffected = IList_execute(query);

            }

            else if (table == "units")
            {
                int defaul = Convert.ToInt32(json_object["active"].ToString());

                if (json_object["code"].ToString().Trim() == String.Empty)
                {
                    if (defaul>0)
                    {
                        IList_execute("update units set mrk_default=0");
                    }
                    query = " select top 1 cast(code as int) from " + table + " order by cast (code as Int)  desc ";
                    string value = (Convert.ToInt32(IList_ExecuteScalerValue(query)) + 1).ToString().PadLeft(3, '0').ToString();
                    query = "insert into " + table + "(code,name,short_code,mrk_default)  values('" + value + "','" + json_object["name"].ToString() + "','" + json_object["short_code"].ToString() + "',"+Convert.ToInt32(json_object["active"].ToString())+");";

                }
                else
                {
                    if (defaul > 0)
                    {
                        IList_execute("update units set mrk_default=0");
                    }
                    query = " Update " + table + " set name='" + json_object["name"].ToString() + "',short_code='" + json_object["short_code"].ToString() + "',mrk_default="+ Convert.ToInt32(json_object["active"].ToString()) + " where rtrim(code)='" + json_object["code"].ToString().Trim() + "';";
                }
                rowsEffected = IList_execute(query);

            }

            else if (table == "prft_per")
            {
                int defaul = Convert.ToInt32(json_object["active"].ToString());

                if (json_object["id"].ToString().Trim() == String.Empty)
                {
                    if (defaul > 0)
                    {
                        IList_execute("update prft_per set mrk_default=0");
                    }
                    query = " select top 1 id from " + table + " order by id desc ";
                    string value = (Convert.ToInt32(IList_ExecuteScalerValue(query)) + 1).ToString();
                    query = "insert into prft_per(id,profit,mrk_default)  values('" + value + "'," + Convert.ToDecimal( json_object["profit"].ToString()) + "," + Convert.ToInt32(json_object["active"].ToString()) + ");";

                }
                else
                {
                    if (defaul > 0)
                    {
                        IList_execute("update prft_per set mrk_default=0");
                    }
                    query = " Update prft_per set profit=" +Convert.ToDecimal( json_object["profit"].ToString()) + ",mrk_default=" + Convert.ToInt32(json_object["active"].ToString()) + " where id=" + json_object["id"].ToString().Trim() + ";";
                }
                rowsEffected = IList_execute(query);

            }

            else if (table == "labour_per")
            {
                int defaul = Convert.ToInt32(json_object["active"].ToString());

                if (json_object["id"].ToString().Trim() == String.Empty)
                {
                    if (defaul > 0)
                    {
                        IList_execute("update labour_estimated set mrk_default=0");
                    }
                    query = " select top 1 id from labour_estimated order by id desc ";
                    string value = (Convert.ToInt32(IList_ExecuteScalerValue(query)) + 1).ToString();
                    query = "insert into labour_estimated(id,charges,mrk_default)  values('" + value + "'," + Convert.ToDecimal(json_object["profit"].ToString()) + "," + Convert.ToInt32(json_object["active"].ToString()) + ");";

                }
                else
                {
                    if (defaul > 0)
                    {
                        IList_execute("update labour_estimated set mrk_default=0");
                    }
                    query = " Update labour_estimated set charges=" + Convert.ToDecimal(json_object["profit"].ToString()) + ",mrk_default=" + Convert.ToInt32(json_object["active"].ToString()) + " where id=" + json_object["id"].ToString().Trim() + ";";
                }

                rowsEffected = IList_execute(query);

            }
            else if (table == "model")
            {
                if (json_object["code"].ToString().Trim() == String.Empty)
                {
                    query = " select top 1 cast(code as int) from model order by cast (code as Int)  desc ";
                    string value = (Convert.ToInt32(IList_ExecuteScalerValue(query)) + 1).ToString().PadLeft(3, '0').ToString();
                    query = "insert into model(code,name,maker)  values('" + value + "','" + json_object["name"].ToString() + "','" + json_object["head"].ToString() + "');";

                }
                else
                {
                    query = " Update model set name='" + json_object["name"].ToString() + "',maker='" + json_object["head"].ToString() + "' where id=" + json_object["code"].ToString().Trim();
                }
                rowsEffected = IList_execute(query);

            }

            else if (table == "item")
            {

                if (json_object["code"].ToString().Trim() == String.Empty)
                {

                    query = " select top 1 cast(code as int) from " + table + " order by cast (code as Int)  desc ";
                    string value = (Convert.ToInt32(IList_ExecuteScalerValue(query)) + 1).ToString().PadLeft(5, '0').ToString();
                    query = "insert into " + table + "(code,name,short_code,reference)  values('" + value + "','" + json_object["name"].ToString() + "','" + json_object["short_code"].ToString() + "','" + json_object["head"].ToString() + "');";

                }
                else
                {
                    query = " Update " + table + " set name='" + json_object["name"].ToString() + "',short_code='" + json_object["short_code"].ToString() + "',reference='" + json_object["head"].ToString() + "' where rtrim(code)='" + json_object["code"].ToString().Trim() + "';";
                }
                rowsEffected = IList_execute(query);
            }

            else if (table == "itemlinked")
            {

                if (json_object["id"].ToString().Trim() == String.Empty)
                {
                    if (json_object["linkno"].ToString().Trim() != json_object["partno"].ToString())
                    {
                        query = " select * from itemlinked where LTRIM(RTRIM(linkno))='" + json_object["linkno"].ToString().Trim() + "' ";
                        rowsEffected = IList_ExecuteScalerValue(query);
                        if (rowsEffected <= 0)
                        {
                            return -2;
                        }
                    }

                    query = "Select idno from itemlinked where rtrim(partno)='" + json_object["partno"].ToString() + "'";
                    rowsEffected = IList_ExecuteScalerValue(query);
                    if (rowsEffected > 0)
                    {
                        return -1;
                    }
                    else
                    {

                        query = " select top 1 idno from itemlinked order by  idno  desc ";
                        int value = (Convert.ToInt32(IList_ExecuteScalerValue(query)) + 1);

                        String system = json_object["User"].ToString();
                        String User = json_object["System"].ToString();

                        query = "insert into Log(Entry,type,edate,s_user,l_user,remarks,data) values(" + value + ",'Item','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + User + "','" + system + "','New Entry','');";


                        query += " INSERT INTO [dbo].[itemlinked]" +
                                "   ([idno],[origin],[maker],[model_maker],[model],[brnad],[part],[partno]" +
                                "   ,[linkno],[desc1],[desc2],[desc_cr],[model_cr],[op_qty],[op_cost],[op_amt]" +
                                "   ,[unit],[re_qty],[re_level],[kept_place],[pu_max],[pu_min],[pu_qty],[sa_max],[sa_min]" +
                                "   ,[sa_qty],[item_code],[model_year],[package],[wght_gross],[wght_net],[oem],[misc1],[misc2],[size],[engine_num],[verified],profit_per) values" +

                                "  (" + value + ",'" + json_object["origin"] + "','" + json_object["maker"] + "'," + json_object["model"] + ",(select top 1 code from model where id=" + json_object["model"] + "),'" + json_object["brand"] + "','" + json_object["part"] + "','" + json_object["partno"] + "'" +
                                "   ,'" + json_object["linkno"] + "','" + json_object["desc1"] + "','" + json_object["desc2"] + "','" + json_object["desc_cr"] + "','" + json_object["model_cr"] + "'," + json_object["op_qty"] + "," + json_object["op_cost"] + "," + json_object["op_amount"] +
                                "   ,'" + json_object["unit"] + "'," + json_object["re_qty"] + "," + json_object["re_level"] + ",'" + json_object["kept_place"] + "'," + json_object["pu_max"] + "," + json_object["pu_min"] + "," + json_object["pu_qty"] + "," + json_object["sa_max"] + "," + json_object["sa_min"] +
                                "   ," + json_object["sa_qty"] + ",'" + json_object["itemcode"] + "','" + json_object["modelyear"] + "','" + json_object["package"] + "'" +
                                "," + json_object["weight_gross"] + "," + json_object["weight_net"] + ",'" + json_object["oem"] + "','" + json_object["misc1"] + "','" + json_object["misc2"] + "','" + json_object["size"] + "','" + json_object["engine"] + "',0,"+Convert.ToDecimal(json_object["profit"].ToString()) +"); ";

                        query += " insert into stock(edate,estatus,product,description,rec,rate,gross_amount,net_amount)  " +
                            " values('" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','OS'," + value + ",'" + json_object["desc_cr"] + "'," + json_object["op_qty"] + "," + json_object["op_cost"] + "," + json_object["op_amount"] + "," + json_object["op_amount"] + ");";

                    }

                }
                else
                {
                    if (json_object["linkno"].ToString().Trim() != json_object["partno"].ToString())
                    {
                        query = " select idno from itemlinked where LTRIM(RTRIM(linkno))='" + json_object["linkno"].ToString().Trim() + "' and idno!=" + json_object["id"].ToString();
                        rowsEffected = IList_ExecuteScalerValue(query);
                        if (rowsEffected <= 0)
                        {
                            return -2;
                        }
                    }
                    String data = IList_GetTable("Select * from itemlinked where idno=" + json_object["id"].ToString());
                    String system = json_object["User"].ToString();
                    String User = json_object["System"].ToString();

                    query = "insert into Log(Entry,type,edate,s_user,l_user,remarks,data) values(" + json_object["id"] + ",'Item','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + User + "','" + system + "','Modified Entry','" + data + "');";


                    query += " Update  [dbo].[itemlinked] set " +

                            "  origin='" + json_object["origin"] + "',maker='" + json_object["maker"] + "',model_maker=" + json_object["model"] + ",model=(select top 1 code from model where id=" + json_object["model"] + "),part='" + json_object["part"].ToString() + "',brnad='" + json_object["brand"].ToString() + "',partno='" + json_object["partno"].ToString() + "'" +
                            "  ,linkno='" + json_object["linkno"] + "',desc1='" + json_object["desc1"] + "',desc2='" + json_object["desc2"] + "',desc_cr='" + json_object["desc_cr"] + "',model_cr='" + json_object["model_cr"] + "',op_qty=" + json_object["op_qty"] + ",op_cost=" + json_object["op_cost"] + ",op_amt=" + json_object["op_amount"] +
                            "  ,unit='" + json_object["unit"] + "',re_qty=" + json_object["re_qty"] + ",re_level=" + json_object["re_level"] + ",kept_place='" + json_object["kept_place"] + "',pu_max=" + json_object["pu_max"] + ",pu_min=" + json_object["pu_min"] + ",pu_qty=" + json_object["pu_qty"] + ",sa_max=" + json_object["sa_max"] + ",sa_min=" + json_object["sa_min"] +
                            "  ,sa_qty=" + json_object["sa_qty"] + ",item_code='" + json_object["itemcode"] + "',model_year='" + json_object["modelyear"] + "',package='" + json_object["package"] + "'" +
                            "  ,wght_gross=" + json_object["weight_gross"] + ",wght_net=" + json_object["weight_net"] + ",oem='" + json_object["oem"] + "',misc1='" + json_object["misc1"] + "',misc2='" + json_object["misc2"] + "'," +
                            "  size = '" + json_object["size"] + "',engine_num = '" + json_object["engine"] + "',profit_per="+Convert.ToDecimal(json_object["profit"].ToString()) +
                            "  where idno=" + json_object["id"];

                    query += " update stock set description='" + json_object["desc_cr"] + "',rec=" + json_object["op_qty"] + ",rate=" + json_object["op_cost"] + ",gross_amount=" + json_object["op_amount"] + ",net_amount=" + json_object["op_amount"] + " where product=" + json_object["id"] + " and estatus='OS';";

                }
                rowsEffected = IList_execute(query);
            }

            else if (table == "warehouse")
            {
                query = "Insert Into warehouse(Name) values('" + json_object["data"] + "'); SELECT SCOPE_IDENTITY() AS CODE";
                rowsEffected = IList_execute(query);
            }

            else if (table == "bank")
            {
                query = " select top 1 isnull(Code,0) from bank  order by CAST(Code AS INT) desc";
                String code = IList_ExecuteScalerValue_String(query);
                if (code == null)
                {
                    code = "00001";
                }
                else
                {
                    code = (Convert.ToInt32(cm.ExecuteScalar()) + 1).ToString().PadLeft(5, '0');
                }
                query = "Insert Into bank(Code,Name) values('" + code + "','" + json_object["data"] + "');";
                rowsEffected = IList_execute(query);
            }

            else if (table == "Particulars")
            {
                query = " select top 1 isnull(Code,0) from " + table + "  order by CAST(Code AS INT) desc";
                String code = IList_ExecuteScalerValue_String(query);
                if (code == String.Empty)
                {
                    code = "00001";
                }
                else
                {
                    code = (Convert.ToInt32(code) + 1).ToString().PadLeft(5, '0');
                }
                query = "Insert Into Particulars(Code,Name) values('" + code + "','" + json_object["data"] + "');";
                rowsEffected = IList_execute(query);
            }

            else if (table == "UserReg")
            {
                string Password = PasswordHashing.Encrypt(Convert.ToString(json_object["pass"]));

                String users = "insert into USERS(USERS_NAME,USERS_PASS,ROLE_ID,DESIGNATION,DEPARTMENT,ISACTIVE,web_Access) " +
                "values('" + json_object["name"] + "','" + Password + "'," +
                 Convert.ToInt32(json_object["role"].ToString()) + ",'" + json_object["desig"] + "','" +
                 json_object["depart"] + "'," + Convert.ToInt32(json_object["active"].ToString()) +
                 "," + Convert.ToInt32(json_object["access"].ToString()) + "); SELECT SCOPE_IDENTITY() AS CODE";


                rowsEffected = IList_ExecuteScalerValue(users);

            }

            else if (table == "RoleReg")
            {
                String Role = "Insert into RoleMaster(Role_Name) values('" + json_object["name"] + "'); SELECT SCOPE_IDENTITY() AS CODE";
                int id = IList_Lastid(Role);

                DataTable dt = (DataTable)JsonConvert.DeserializeObject(json_object["detail"].ToString(), (typeof(DataTable)));

                foreach (DataRow row in dt.Rows)
                {
                    query += "insert into RoleDetail(role,Module,Can_Access,Can_Add,Can_Edit,Can_Delete,Can_Print,Can_Post,Can_Docx,Can_Special) values(" +
                    id + ",'" + row["module"] + "'," + Convert.ToInt32(row["Access"]) + "," + Convert.ToInt32(row["Add"]) + "," +
                  Convert.ToInt32(row["Edit"]) + "," + Convert.ToInt32(row["Delete"]) + "," + Convert.ToInt32(row["Print"]) + "," +
                     Convert.ToInt32(row["Post"]) + "," + Convert.ToInt32(row["Docx"]) + "," + Convert.ToInt32(row["Special"]) + ")";


                }
                rowsEffected = IList_execute(query);

            }

            else if (table == "tool_category")
            {
                query = " select top 1 isnull(code,0) from tool_category  order by CAST(code AS INT) desc";
                String code = IList_ExecuteScalerValue_String(query);
                if (code == String.Empty)
                {
                    code = "00001";
                }
                else
                {
                    code = (Convert.ToInt32(code) + 1).ToString().PadLeft(5, '0');
                }
                query = "Insert Into tool_category(code,Name,active) values('" + code + "','" + json_object["name"].ToString() + "'," + Convert.ToInt32(json_object["active"].ToString()) + ");";
                rowsEffected = IList_execute(query);
            }

            else if (table == "employee_category")
            {
                query = " select top 1 isnull(code,0) from employee_category  order by CAST(code AS INT) desc";
                String code = IList_ExecuteScalerValue_String(query);
                if (code == String.Empty)
                {
                    code = "00001";
                }
                else
                {
                    code = (Convert.ToInt32(code) + 1).ToString().PadLeft(5, '0');
                }
                query = "Insert Into employee_category(code,Name,active) values('" + code + "','" + json_object["name"].ToString() + "'," + Convert.ToInt32(json_object["active"].ToString()) + ");";
                rowsEffected = IList_execute(query);
            }

            else if (table == "employees")
            {
                query = " select top 1 isnull(code,0) from employees  order by CAST(code AS INT) desc";
                String code = IList_ExecuteScalerValue_String(query);
                if (code == String.Empty)
                {
                    code = "00001";
                }
                else
                {
                    code = (Convert.ToInt32(code) + 1).ToString().PadLeft(5, '0');
                }
                String name = json_object["name"].ToString();
                int active = Convert.ToInt32(json_object["active"].ToString());
                int cat = Convert.ToInt32(json_object["cat"].ToString());
                String edate = json_object["edate"].ToString();
                query = "Insert Into employees(code,name,active,cat,edate) values('" + code + "','" + name + "'," + active + "," + cat + ",'" + edate + "');";
                rowsEffected = IList_execute(query);
            }

            else if (table == "inspection_category")
            {
                query = " select top 1 isnull(code,0) from inspection_category  order by CAST(code AS INT) desc";
                String code = IList_ExecuteScalerValue_String(query);
                if (code == String.Empty)
                {
                    code = "00001";
                }
                else
                {
                    code = (Convert.ToInt32(code) + 1).ToString().PadLeft(5, '0');
                }
                query = "Insert Into inspection_category(code,Name,active) values('" + code + "','" + json_object["name"].ToString() + "'," + Convert.ToInt32(json_object["active"].ToString()) + ");";
                rowsEffected = IList_execute(query);
            }

            else if (table == "inspection_parameter")
            {
                query = " select top 1 isnull(code,0) from inspection_parameter order by CAST(code AS INT) desc";
                String code = IList_ExecuteScalerValue_String(query);
                if (code == String.Empty)
                {
                    code = "00001";
                }
                else
                {
                    code = (Convert.ToInt32(code) + 1).ToString().PadLeft(5, '0');
                }
                String name = json_object["name"].ToString();
                int active = Convert.ToInt32(json_object["active"].ToString());
                int cat = Convert.ToInt32(json_object["cat"].ToString());
                String edate = json_object["edate"].ToString();
                query = "Insert Into inspection_parameter(code,name,active,cat,edate) values('" + code + "','" + name + "'," + active + "," + cat + ",'" + edate + "');";
                rowsEffected = IList_execute(query);
            }

            else if (table == "inspection_parameter_type")
            {
                query = " select top 1 isnull(code,0) from inspection_parameter_type  order by CAST(code AS INT) desc";
                String code = IList_ExecuteScalerValue_String(query);
                if (code == String.Empty)
                {
                    code = "00001";
                }
                else
                {
                    code = (Convert.ToInt32(code) + 1).ToString().PadLeft(5, '0');
                }
                query = "Insert Into inspection_parameter_type(code,Name,active) values('" + code + "','" + json_object["name"].ToString() + "'," + Convert.ToInt32(json_object["active"].ToString()) + ");";
                rowsEffected = IList_execute(query);
            }

            else if (table == "inspection_parameter_op")
            {
                String child = json_object["Child"].ToString();
                DataTable dt = (DataTable)JsonConvert.DeserializeObject(child, (typeof(DataTable)));
                int parameter, type;

                parameter = type = 0;
                foreach (DataRow row in dt.Rows)
                {
                    parameter = Convert.ToInt32(row["parameter"].ToString());
                    type = Convert.ToInt32(row["type"].ToString());

                    query += "Insert Into inspection_parameter_op(parameter,ptype) values(" + parameter + "," + type + ");";

                }
                query = "DELETE FROM inspection_parameter_op where parameter=" + parameter + ";" + query;
                rowsEffected = IList_execute(query);
            }

            return rowsEffected;
        }

        public int IList_Json_Update(String json, String table)
        {
            String query = "";
            int rowsEffected = 0;
            JObject json_object = JObject.Parse(json);

            String token = json_object["token"] == null ? "" : json_object["token"].ToString();
            if (Authorization(token) <= 0)
            {
                return 404;
            }

            /*   if (table == "COA")
               {
                   query = "Update AC set Title='" + json_object["name"] + "',name_urdu=N'" + json_object["name_urdu"] + "' where ac_no='" + (json_object["code"] + "'");
                   rowsEffected = IList_execute(query);

               }*/

            else if (table == "COA info")
            {



                query = "update ac set add1='" + json_object["address"].ToString() + "',city='" + Convert.ToString(json_object["city"]) + "'," +
                    "ph1='" + json_object["phone1"].ToString() + "',ph2='" + json_object["phone2"].ToString() + "',ph3='" + json_object["phone3"].ToString() + "'," +
                    "fax='" + json_object["fax"].ToString() + "',cell1='" + json_object["cell1"].ToString() + "',cell2='" + json_object["cell2"].ToString() + "'," +
                    "irn='" + json_object["cnic"].ToString() + "',ntn='" + json_object["ntn"].ToString() + "',stn='" + json_object["strn"].ToString() + "'," +
                    "rmarks1='" + json_object["remarks1"].ToString() + "',rmarks2='" + json_object["remarks2"].ToString() + "',rmarks3='" + json_object["remarks3"].ToString() + "'," +
                   "email='" + json_object["emails"].ToString() + "',deals='" + json_object["deals"].ToString() + "',contactperson='" + json_object["contactperson"].ToString() + "'," +
                     "n_code='" + json_object["c_code"].ToString() + "'" +
                    " where code='" + (json_object["id"].ToString()) + "';";
                rowsEffected = IList_execute(query);

            }

            else if (table == "CompanySetup")
            {
                query = "Update companysetup Set" +
                    " name='" + json_object["name"] + "',address='" + json_object["address"] + "'," +
                     "stregno='" + json_object["stno"] + "',ntnno='" + json_object["ntno"] + "'," +
                    " yearstart='" + json_object["ystart"] + "',yearend='" + json_object["yend"] + "'," +
                    " contactno='" + json_object["contactno"] + "', " +
                    " image_saving='" + json_object["logo"] + "',doc_db='" + json_object["doc_db"] + "',cash_in_hnd='" + json_object["cash_in_hnd"].ToString().Trim() +"'";
                rowsEffected = IList_execute(query);
            }

            else if (table == "warehouse")
            {
                query = "Update warehouse set name='" + json_object["data"] + "' where id=" + Convert.ToInt32(json_object["code"].ToString());
                rowsEffected = IList_execute(query);

            }

            else if (table == "password")
            {
                int id = Convert.ToInt32(json_object["id"].ToString());
                string Password = PasswordHashing.Encrypt(json_object["pass"].ToString());

                query = " SELECT u.USERS FROM USERS u where u.USERS=" + id + " and u.USERS_PASS='" + Password + "' ";
                String val = IList_ExecuteScalerValue_String(query);
                if (val != String.Empty)
                {
                    query = "Update USERS set USERS_PASS='" + (json_object["pass"]) + "' where USERS=" + id;
                    rowsEffected = IList_execute(query);
                }
                else
                {
                    rowsEffected = -1;
                }
            }

            else if (table == "bank")
            {

                query = "Update " + table + " set name='" + json_object["data"] + "' where code='" + json_object["code"] + "'";
                rowsEffected = IList_execute(query);

            }

            else if (table == "Particulars")
            {

                query = "Update " + table + " set name='" + json_object["data"] + "' where code='" + json_object["code"] + "'";
                rowsEffected = IList_execute(query);

            }

            else if (table == "UserReg")
            {
                String password = PasswordHashing.Encrypt(json_object["pass"].ToString());
                String users = "Update USERS set USERS_NAME='" + json_object["name"] + "'," +
                  "USERS_PASS='" + password + "',ROLE_ID=" + Convert.ToInt32(json_object["role"].ToString()) + "," +
                  "DESIGNATION='" + json_object["desig"] + "',DEPARTMENT='" + json_object["depart"] + "'," +
                  "ISACTIVE=" + Convert.ToInt32(json_object["active"].ToString()) + ",web_Access= " + Convert.ToInt32(json_object["active"].ToString()) +
                  " where users=" + Convert.ToInt32(json_object["id"].ToString());


                rowsEffected = IList_execute(users);

            }

            else if (table == "RoleReg")
            {
                String Role = "Update  RoleMaster set Role_Name='" + json_object["name"] + "' where role_id=" + Convert.ToInt32(json_object["role"].ToString());
                int id = IList_execute(Role);
                id = Convert.ToInt32(json_object["role"].ToString());

                DataTable dt = (DataTable)JsonConvert.DeserializeObject(json_object["detail"].ToString(), (typeof(DataTable)));
                query = "Delete from RoleDetail where role=" + Convert.ToInt32(json_object["role"].ToString()); ;
                foreach (DataRow row in dt.Rows)
                {
                    query += "insert into RoleDetail(role,Module,Can_Access,Can_Add,Can_Edit,Can_Delete,Can_Print,Can_Post,Can_Docx,Can_Special) values(" +
                  id + ",'" + row["module"] + "'," + Convert.ToInt32(row["Access"]) + "," + Convert.ToInt32(row["Add"]) + "," +
                Convert.ToInt32(row["Edit"]) + "," + Convert.ToInt32(row["Delete"]) + "," + Convert.ToInt32(row["Print"]) + "," +
                   Convert.ToInt32(row["Post"]) + "," + Convert.ToInt32(row["Docx"]) + "," + Convert.ToInt32(row["Special"]) + ")";


                }
                rowsEffected = IList_execute(query);
            }


            else if (table == "ReOrder Closed")
            {

                query = " update Purchase_Order set closed=" + Convert.ToInt32(json_object["closed"].ToString()) + "  where key_pk=" + json_object["idno"];
                rowsEffected = IList_execute(query);

            }

            else if (table == "Marked Aging")
            {
                query = "Update Sales_Master set marked=" + Convert.ToInt32(json_object["marked"].ToString()) + "  where key_pk=" + json_object["idno"];
                rowsEffected = IList_execute(query);
            }

            else if (table == "tool_category")
            {
                String code = json_object["code"].ToString();
                String name = json_object["name"].ToString();
                int active = Convert.ToInt32(json_object["active"].ToString());
                query = "Update tool_category set Name='" + name + "',active=" + active + " where LTRIM(RTRIM(code))='" + code + "';";
                rowsEffected = IList_execute(query);
            }

            else if (table == "employee_category")
            {
                String code = json_object["code"].ToString();
                String name = json_object["name"].ToString();
                int active = Convert.ToInt32(json_object["active"].ToString());
                query = "Update employee_category set Name='" + name + "',active=" + active + " where LTRIM(RTRIM(code))='" + code + "';";
                rowsEffected = IList_execute(query);
            }

            else if (table == "employees")
            {
                String id = json_object["id"].ToString();
                String name = json_object["name"].ToString();
                int active = Convert.ToInt32(json_object["active"].ToString());
                String edate = json_object["edate"].ToString();
                int cat = Convert.ToInt32(json_object["active"].ToString());
                query = "Update employees set Name='" + name + "',active=" + active + ",cat=" + cat + "," +
                    " edate='" + edate + "' where id=" + id + ";";
                rowsEffected = IList_execute(query);
            }

            else if (table == "inspection_category")
            {
                String code = json_object["code"].ToString();
                String name = json_object["name"].ToString();
                int active = Convert.ToInt32(json_object["active"].ToString());
                query = "Update inspection_category set Name='" + name + "',active=" + active + " where LTRIM(RTRIM(code))='" + code + "';";
                rowsEffected = IList_execute(query);
            }

            else if (table == "inspection_parameter")
            {
                String id = json_object["id"].ToString();
                String name = json_object["name"].ToString();
                int active = Convert.ToInt32(json_object["active"].ToString());
                String edate = json_object["edate"].ToString();
                int cat = Convert.ToInt32(json_object["active"].ToString());
                query = "Update inspection_parameter set Name='" + name + "',active=" + active + ",cat=" + cat + "," +
                    " edate='" + edate + "' where id=" + id + ";";
                rowsEffected = IList_execute(query);
            }

            else if (table == "inspection_parameter_type")
            {
                String code = json_object["code"].ToString();
                String name = json_object["name"].ToString();
                int active = Convert.ToInt32(json_object["active"].ToString());
                query = "Update inspection_parameter_type set Name='" + name + "',active=" + active + " where LTRIM(RTRIM(code))='" + code + "';";
                rowsEffected = IList_execute(query);
            }

            return rowsEffected;
        }

        public int Ilist_Json_Delete(String json, String table)
        {
            int rowsEffected = 0;
            JObject json_object = JObject.Parse(json);

            String token = json_object["token"].ToString();

            if (token != app_key)
            {
                return 404;
            }

            String query = "";

            if (table == "ac")
            {
                String code = json_object["code"].ToString().Trim();
                query = "select idno from ac where code='" + code + "'";
                int id = IList_ExecuteScalerValue(query);

                query = "  delete from ac where idno=" + id + " and rtrim(code)='" + code + "';";
                rowsEffected = IList_execute(query);
                if (rowsEffected > 0)
                {

                    query = " delete from ledger where type='OP' and (d_acode='" + code + "'" +
                             "  or c_acode='" + code + "')";
                    rowsEffected = IList_execute(query);

                    String Log = " insert into Log(Entry,type,edate,s_user,l_user,remarks,data,users_id) values" +
                                           " (" + id + ",'ac','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + (json_object["s_user"].ToString()) + "','" + (json_object["User"].ToString()) + "','Deleted Entry',''," + (json_object["UserId"].ToString()) + ")";
                    IList_execute(Log);
                }
            }

            else if (table == "maker" || table == "brand" || table == "origin" || table == "reference" || table == "part")
            {

                query = " delete from  " + table + "  where rtrim(code)='" + json_object["code"].ToString().Trim() + "';";

                rowsEffected = IList_execute(query);

            }

            else if (table == "prft_per")
            {
                query = "delete from prft_per where id=" + Convert.ToInt32(json_object["id"].ToString().Trim()) + ";";
                rowsEffected = IList_execute(query);
            }

            else if (table == "labour_per")
            {
                query = "delete from labour_estimated where id=" + Convert.ToInt32(json_object["id"].ToString().Trim()) + ";";
                rowsEffected = IList_execute(query);
            }

            else if (table == "item")
            {

                query = "delete from item where code='" + (json_object["code"].ToString().Trim()) + "'";
                rowsEffected = IList_execute(query);

            }

            else if (table == "bank")
            {

                query = "delete from bank where code='" + (json_object["code"].ToString().Trim()) + "'";
                rowsEffected = IList_execute(query);

            }

            else if (table == "Particulars")
            {

                query = "delete from bank where code='" + (json_object["code"].ToString().Trim()) + "'";
                rowsEffected = IList_execute(query);

            }

            else if (table == "warehouse")
            {

                query = "delete from warehouse where id=" + Convert.ToInt32(json_object["code"].ToString().Trim());
                rowsEffected = IList_execute(query);

            }

            else if (table == "model")
            {

                query = "delete from model where id=" + Convert.ToInt32(json_object["id"].ToString().Trim());
                rowsEffected = IList_execute(query);
            }

            else if (table == "itemlinked")
            {
                String data = IList_GetTable("select * from itemlinked where idno=" + Convert.ToInt32(json_object["code"].ToString().Trim()));
                InsertLog(Convert.ToInt32(json_object["code"].ToString().Trim()), "item", json_object["System"].ToString(), json_object["User"].ToString(), "Delete Entry", data);
                query = "delete from itemlinked where idno=" + Convert.ToInt32(json_object["code"].ToString().Trim());
                rowsEffected = IList_execute(query);
            }

            else if (table == "tool_category")
            {
                String code = json_object["code"].ToString();
                query = "delete from  tool_category  where LTRIM(RTRIM(code))='" + code + "';";
                rowsEffected = IList_execute(query);
            }

            else if (table == "tools")
            {
                String id = json_object["id"].ToString();
                query = "select * from tools where id=" + id;
                String data = IList_GetTable(query);
                query = "delete from inventory_stock where estatus='OP' and product=" + id + ";";
                query += "delete from tools  where id=" + id + ";";
                rowsEffected = IList_execute(query);
                if (rowsEffected > 0)
                {
                    String Log = "Insert into Log(Entry,type,edate,s_user,l_user,remarks,data,users_id) values(" + rowsEffected + ",'TL','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + json_object["System"].ToString() + "','" + json_object["User"].ToString() + "','Delete Entry','" + data + "'," + json_object["UserId"].ToString() + ")";
                    IList_execute(Log);
                }
            }

            else if (table == "employee_category")
            {
                String code = json_object["code"].ToString();
                query = "delete from employee_category  where LTRIM(RTRIM(code))='" + code + "';";
                rowsEffected = IList_execute(query);
            }

            else if (table == "employees")
            {
                String code = json_object["id"].ToString();
                query = "delete from  employees  where id=" + code;
                rowsEffected = IList_execute(query);
            }

            else if (table == "inspection_category")
            {
                String code = json_object["code"].ToString();
                query = "delete from  inspection_category  where LTRIM(RTRIM(code))='" + code + "';";
                rowsEffected = IList_execute(query);
            }

            else if (table == "inspection_parameter")
            {
                String id = json_object["id"].ToString();
                query += "delete from inspection_parameter  where id=" + id + ";";
                rowsEffected = IList_execute(query);
            }

            else if (table == "inspection_parameter_type")
            {
                String code = json_object["code"].ToString();
                query += "delete from inspection_parameter_type  where code='" + code + "';";
                rowsEffected = IList_execute(query);
            }

            return rowsEffected;
        }

        void InsertLog(int entryno, string vtype, String system, String user, String status, String data)
        {
            String query = "insert into Log(Entry,type,edate,s_user,l_user,remarks,data) " +
              "values(" + entryno + ",'" + vtype + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" +
              system + "','" + user + "','" + status + "','" + data + "')";
            IList_execute(query);

        }

        public int IList_Delete(String table, int id)
        {
            int IsUpdate = 0;
            String query = "";

            if (table == "city")
            {
                query = "Delete from " + table + " where idno=" + id;
            }
            else if (table == "market")
            {
                query = "Delete from " + table + " where id=" + id;

            }
            else if (table == "state")
            {
                query = "Delete from " + table + " where id=" + id;
            }


            if (table == "RoleReg")
            {
                query = "Delete from RoleDetail where Role=" + id + ";Delete from ROLEMASTER where Role_Id=" + id;
            }
            else if (table == "Users")
            {
                query = "Delete from User_Warehouse where Users=" + id + ";Delete from Users where Users=" + id;
            }
            else if (table == "COA")
            {
                query = "DELETE FROM ADDRES where id=" + id + "; DELETE  FROM AC WHERE acode=" + id;
            }
            IsUpdate = IList_execute(query);

            return IsUpdate;
        }

        public String IList_Data(String table, String token)
        {
            String query = "";
            String json = "";
            table = table.Trim();


            if (token != app_key)
            {
                json = "In-Valid";
                return json;
            }

            if (table == "notify")
            {

                query = " select notify from companysetup ";
                json = IList_ExecuteScalerValue_String(query);
            }

            if (table == "Vehicle")
            {

                query = " Select * from Vehicle";

                json = IList_GetTable(query);
            }
            else if (table == "city")
            {

                query = "select City.code,City.name,state.name as State,isnull(state.code,'') as [State Code],City.idno as id from City left join state on City.state=state.code order By name;" +
                        "  select * from state order by name;";
                json = IList_GetDataSet(query);

            }

            else if (table == "market")
            {

                query = " select market.code,market.name,city.name as City,city.code as [City Code],market.id from market left join city on city.code=market.city order by market.name;" +
                    "select City.code,City.name,state.name as State,isnull(state.code,'') as [State Code],City.idno as id from City left join state on City.state=state.code order By name;";

                json = IList_GetDataSet(query);

            }

            else if (table == "state")
            {
                query = " select * from state;";

                json = IList_GetDataSet(query);

            }

            else if (table == "subhead")
            {
                query = "select Code,Title as Name,isnull(Parent,'') as Parent,(select title from ac ac1 where ac1.code=ac.parent) as Type from ac where type='Parent' and code not in(select distinct parent from ac where type='Child');" +
                        "select Code,Title,Parent,(select title from ac ac1 where ac1.code=ac.parent) as Type from ac where type='Parent';";
                json = IList_GetDataSet(query);

            }

            else if (table == "maker" || table == "brand" || table == "reference" || table == "origin")
            {

                query = " Select code as Code,name as Name,short_code as Short_Code from " + table + " order by Name;";
                json = IList_GetDataSet(query);

            }

            else if (table == "units")
            {
                query = " Select code as Code,name as Name,short_code as Short_Code,isnull(mrk_default,0) as [Default] from " + table + " order by Name;";
                json = IList_GetDataSet(query);
            }
            else if (table == "prft_per")
            {
                query = " Select id as Id,profit as [Profit(%)],isnull(mrk_default,0) as [Default] from prft_per order by profit;";
                json = IList_GetDataSet(query);
            }

            else if (table == "labour_per")
            {
                query = " Select id as Id,charges as [Labour(%)],isnull(mrk_default,0) as [Default] from labour_estimated order by charges;";
                json = IList_GetDataSet(query);
            }
            
            else if (table == "model")
            {


                query = " Select id,model.code as Code,model.name as Name,maker.name as Maker,isnull(maker.code,'') as maker_code  from model left join maker on maker.code=model.maker order by model.name;" +
                        " Select code as Code,name as Name,short_code as Short_Code from maker order by Name;";

                json = IList_GetDataSet(query);
            }

            else if (table == "item")
            {

                query = " Select item.code as Code,item.name as Name,isnull(item.short_code,'') as Short_Code,rf.name as Reference,isnull(rf.code,'') as Ref_code  from Item left join reference rf on rf.code=item.reference order by name;" +
                         "  Select code as Code,name as Name from reference order by name;";
                json = IList_GetDataSet(query);
            }

            else if (table == "COA")
            {
                query = "select Code,Title as Name,Type,isnull(parent,'0-') as Parent,ISNULL(iden,'') as iden from ac ;" +
                        " select * from city order by name;select * from market order by name;" +
                         " select code,title as name from (" +
                        "   select * from ac where type='Parent' and code not in(select distinct parent from ac where parent is not null ) " +
                        "    union all" +
                        "   select * from ac where type='Parent' and code in (select distinct parent from ac where type='Child') " +
                        "    )a order by a.code;";
                json = IList_GetDataSet(query);
            }

            else if (table == "COA Add")
            {
                query = "select acode,TITLE,Type,ac_no,name_urdu,isnull(parent,0) as Parent from ac where Type='Parent';";
                json = IList_GetDataSet(query);
            }

            else if (table == "Ledger Ac")
            {

                query = " select * from ac order by title;";
                json = IList_GetTable(query);
            }

            else if (table == "CompanySetup")
            {
                query = " Select * from CompanySetup;" +
                    "select code,title from ac where type='Child'order by title;";
                json = IList_GetDataSet(query);
            }

            if (table == "warehouse")
            {
                query = "SELECT RIGHT(CONCAT('00000', ISNULL(id,'')), 5) as CODE,NAME FROM " + table;
                json = IList_GetTable(query);
            }

            if (table == "bank")
            {
                query = "SELECT  CODE,NAME FROM " + table;
                json = IList_GetTable(query);
            }

            if (table == "Particulars")
            {
                query = "SELECT  CODE,NAME FROM " + table;
                json = IList_GetTable(query);
            }

            if (table == "Log")
            {
                query = "select USERS,USERS_NAME from USERS order by USERS_NAME;";
                json = IList_GetTable(query);
            }

            else if (table == "Pending Cheque")
            {

                query = "select  v.id,vm.Entry,Convert(varchar(11) ,v.cheqdate,105) as Date,v.chequeno,v.credit,v.bank,v.chequetitle  from VoucherMaster vm  inner join VoucherDetail v on vm.idno=v.MEntry ";
                query += "   where  vm.type='BR' and v.Id not in(select distinct issued from VoucherMaster vm inner join VoucherDetail vd on vm.idno=vd.MEntry where vm.type='BP' and issued is not null)   ";

                json = IList_GetTable(query);
            }

            else if (table == "Pending Authorization")
            {
                query = "select * from ( " +
                        " select om.*,ac.title,'Sales Order' as type from Order_Master om inner join ac on ac.code=om.acode where om.auth_by is null " +
                        " union all " +
                        " select om.*,ac.title,'Purchase Order' from Purchase_Order om inner join ac on ac.code=om.acode where om.auth_by is null " +
                        " union all " +
                        " select om.*,ac.title,'Quotation' from Quotation_Master om inner join ac on ac.code=om.acode where om.auth_by is null " +
                        " )a order by cast(a.clkdate as Date),a.invoice_no,a.type";
                json = IList_GetTable(query);

            }

            else if (table == "UserManagament")
            {
                String warehouse = " Select id,Code,Name from Warehouse; ";
                String users = "Select * from users;";
                String user_warehouse = "Select * from user_warehouse;";
                String Role = "select * from ROLEMASTER rm where rm.ROLE_ID in( select rd.Role from RoleDetail rd where rd.Role=rm.ROLE_ID);";
                String module = " select * from MODULE Order By Module_Name;";
                String role_Detail = " select * from RoleDetail;";
                query = warehouse + users + user_warehouse + Role + module + role_Detail;
                json = IList_GetDataSet(query);
            }

            else if (table == "Get Product")
            {
                query = " select idno,partno,linkno from itemlinked;";
                json = IList_GetTable(query);
            }

            else if (table == "Get Customer")
            {
                query = " select ac.Code,Title,iden,city_code,market,City.name as City,market.name as [Market Name] from ac " +
                          " left join City on City.code=ac.city_code " +
                          " left join market on market.code=ac.market " +
                          " where type='Child' order By Title;";

                json = IList_GetTable(query);
            }

            else if (table == "Find Quotation Load")
            {
                query = "Select distinct vn_number from Quotation_Master order by vn_number;";
                query += "Select distinct reg_number from Quotation_Master order by reg_number;";
                query += "Select distinct contactno from Quotation_Master order by contactno;";

                json = IList_GetDataSet(query);
            }

            else if (table == "tool_category")
            {
                query = " select Code,Name,Active from tool_category order by id desc;";
                json = IList_GetTable(query);
            }

            else if (table == "tools")
            {
                query = " Select * from tools order by id desc;select * from tool_category order by name;";
                json = IList_GetDataSet(query);
            }

            else if (table == "employee_category")
            {
                query = " select Code,Name,Active from employee_category order by id desc;";
                json = IList_GetTable(query);
            }

            else if (table == "employees")
            {
                query = " select * from employee_category order by name;Select * from employees order by id desc";
                json = IList_GetDataSet(query);
            }

            else if (table == "inspection_category")
            {
                query = " select Code,Name,Active from inspection_category order by id desc;";
                json = IList_GetTable(query);
            }

            else if (table == "inspection_parameter")
            {
                query = " select id,Code,Name,Active from inspection_category order by name;Select * from inspection_parameter order by id desc;";
                json = IList_GetDataSet(query);
            }

            else if (table == "inspection_parameter_type")
            {
                query = " select Code,Name,Active from inspection_parameter_type order by id desc;";
                json = IList_GetTable(query);
            }



            return json;
        }

        public String IList_Data_Json(String table, String json)
        {
            String query = "";
            String data = "";
            JObject json_object = JObject.Parse(json);

            String token = json_object["token"] == null ? "" : json_object["token"].ToString();
            if (token != "")
            {
                if (token != app_key)
                {
                    json = "In-Valid";
                    return json;
                }
            }

            if (table=="Validate_Partno")
            {
                String part = (json_object["partno"].ToString().Trim());
                String tab = (json_object["table"].ToString().Trim());
                int inspect_id =Convert.ToInt32 (json_object["inspect_id"].ToString().Trim());

                query = " select prtno from "+tab+" where inspection_id="+inspect_id;
            }
            if (table == "Vehicle")
            {
                String vin = (json_object["vin"].ToString().Trim());

                query = " Select * from Vehicle where vin='" + vin + "'";

                data = IList_GetTable(query);
            }

            if (table == "inspection_parameter_op")
            {
                String code = (json_object["parameter"].ToString().Trim());

                query = " select it.id,it.name,op.id as op_id,ip.name from inspection_parameter_op op " +
                        " inner join inspection_parameter ip on op.parameter = ip.id and op.parameter=" + code +
                        " right join inspection_parameter_type it on it.id = op.ptype;  ";

                data = IList_GetTable(query);
            }

            if (table == "inspection_parameter_byid")
            {
                String id = (json_object["parameter"].ToString().Trim());

                query = " select it.id,it.name,op.id as op_id,ip.name from inspection_parameter_op op " +
                        " inner join inspection_parameter ip on op.parameter = ip.id " +
                        " inner join inspection_parameter_type it on it.id = op.ptype  " +
                        " where ip.id=" + id;

                data = IList_GetTable(query);
            }

            if (table == "subhead_code")
            {
                String code = (json_object["code"].ToString().Trim());

                query = " select top 1 code from ac where rtrim(parent)='" + code + "' order by REVERSE( LEFT(REVERSE(code), CHARINDEX('-', REVERSE(code)) - 1)) desc ;";
                data = IList_GetTable(query);

            }

            else if (table == "COA Get")
            {
                query = "select * from ac where rtrim(code)='" + json_object["code"].ToString() + "'";
                data = IList_GetTable(query);
            }

            else if (table == "GJ Balance")
            {
                query = " select isnull(isnull(a.debit,0)-ISNULL(b.credit,0),0) " +
                    "     from ac " +
                   "     left join (select d_acode,sum(isnull(d_amt,0)) as debit from Ledger where rtrim(d_acode)='" + json_object["code"].ToString().Trim() + "' group by d_acode)a on a.d_acode=ac.code " +
                   "     left join (select c_acode,sum(isnull(c_amt,0)) as credit from Ledger where rtrim(c_acode)='" + json_object["code"].ToString().Trim() + "' group by c_acode)b " +
                   "     on b.c_acode=ac.code left join city on city.code=ac.city_code left join market on market.code=ac.market " +
                   "     where ac.type='Child' and rtrim(ac.code)='" + json_object["code"].ToString().Trim() + "' order by ac.title";
                data = IList_GetTable(query);
            }

            else if (table == "GJ Print")
            {

                query = " select gj.*,db.title as debit,cd.title as credit,im.customer from General_Journal gj " +
                        " inner join ac db on db.code=gj.d_acode " +
                        " inner join ac cd on cd.code=gj.c_acode "+
                        " left join inspection_master im on im.id=gj.insp_id "+
                        " where key_pk=" + json_object["key"].ToString() + ";" +
                        " Select name,address,contactno from companysetup; " +
                        " Select top 1 l_user from Log where type='GJ' and Entry=" + json_object["entry"].ToString() + " order by id ";
                data = IList_GetDataSet(query);

            }

            else if (table == "GJ List")
            {
                query = " select ac.Title,city.name as City,market.name as Market , " +
                        "    case when(isnull(a.debit,0)-ISNULL(b.credit,0))<0 then (isnull(a.debit,0)-ISNULL(b.credit,0))*-1 else (isnull(a.debit,0)-ISNULL(b.credit,0)) end as Balance, " +
                        "    Case when(isnull(a.debit,0)-ISNULL(b.credit,0))=0  then 'Nil' else case when(isnull(a.debit,0)-ISNULL(b.credit,0))>0 then 'DR' else 'CR' end end as type ,ac.Code from ac " +
                        "    left join (select d_acode,sum(isnull(d_amt,0)) as debit from Ledger group by d_acode)a on a.d_acode=ac.code " +
                        "    left join (select c_acode,sum(isnull(c_amt,0)) as credit from Ledger group by c_acode)b " +
                        "     on b.c_acode=ac.code left join city on city.code=ac.city_code left join market on market.code=ac.market  where ac.type='Child' order by ac.title";
                data = IList_GetTable(query);
            }

            else if (table == "Get Linkno")
            {
                String where = "";
                string link = " select linkno from itemlinked i where LTRIM(RTRIM(i.partno))='" + json_object["partno"].ToString().Trim() + "'";
                string linkno = IList_ExecuteScalerValue_String(link);
                if (linkno.Trim() != "")
                {
                    where += " or LTRIM(RTRIM(p.linkno))='" + linkno.Trim() + "'";
                }

                query = "  select  p.idno as [Itemno],brand.name as Brand,item.name as Part,p.desc1 as Description,p.unit as Unit,p.inhnd as InHand, " +
                  "     (select top 1 dscrate from  Sales_Detail sd " +
                  "     where sd.acitno = p.idno and sd.key_fk = (Select max(key_fk) from Sales_Detail where acitno = p.idno)) as [Last Sa Rate],p.last_pu as [Last Pu Rate],p.model_cr as [Models(s) For Circular], " +
                  "     p.partno as [PartNumber],p.linkno as [LinkNo],p.vcode as[VendorCode],p.kept_place as[Kept Place],  " +
                  "     rtrim(maker.name) as Maker,rtrim(model.name) as Model,rtrim(origin.name) as [Origin],rtrim(rf.name) as [Reference] from itemlinked p  " +
                  "    left  join origin on origin.code=p.origin  left join brand on brand.code=p.brnad left join item on item.code=p.part " +
                  "    left join model on model.id=p.model_maker  left join maker on maker.code=model.maker left join reference rf on rf.code=item.reference " +

                  "    where  p.idno>0 and LTRIM(RTRIM(p.linkno))='" + json_object["linkno"].ToString().Trim() + "'" + where;
                data = IList_GetTable(query);
            }

            else if (table == "Get Invoice")
            {

                query = " select distinct  sm.key_pk,sm.clkdate,sm.days,sm.duedate,sm.dscr_p as inv_disc,sm.disc as inv_disc_amt,sm.mrdminv_no,sm.invtype,sm.invoice_no,sm.acode,sm.prtclar,sm.expmnininv,sm.courier,sm.gstp,sm.gsta,sm.itp,sm.ita,sm.prtclar,sm.collection,sm.balance,sm.closed,sd.citmno,sd.prtno,sd.bn" +
                        " ,sd.key_fk,sd.key_pk as sn,sd.acitno,sd.invdesc,sd.subdesc,sd.qty,sd.rate,sd.amt,sd.dscrate,sd.disc,sd.dscr_p,sd.ntamt from " + json_object["Master"] + " sm inner join " + json_object["Child"] + " sd on sm.key_pk=sd.key_fk " +
                        " where sm.key_pk=" + json_object["id"].ToString() + " or sm.invoice_no=" + json_object["invoice_no"].ToString() + " order by sn;";

                data = IList_GetTable(query);

            }

            else if (table == "Invoice Next")
            {
                int invoice = 0;
                invoice = Invoice_Next(Convert.ToInt32(json_object["id"].ToString()), json_object["Master"].ToString(), json_object["Child"].ToString());
                query = "select distinct  sm.key_pk,sm.clkdate,sm.days,sm.duedate,sm.dscr_p as inv_disc,sm.disc as inv_disc_amt, case when sd.line_warehouse is null then sm.warehouse else sd.line_warehouse end as warehouse,sm.mrdminv_no,sm.invtype,sm.invoice_no,sm.acode,sm.prtclar,sm.expmnininv,sm.courier,sm.gstp,sm.gsta,sm.itp,sm.ita,sm.prtclar,sm.collection,sm.balance,sm.closed,sd.citmno,sd.prtno,sd.bn" +
                        ",sd.key_fk,sd.key_pk as sn,sd.acitno,sd.invdesc,sd.subdesc,sd.qty,sd.rate,sd.amt,sd.dscrate,sd.disc,sd.dscr_p,sd.ntamt,sd.line_warehouse from " + json_object["Master"] + " sm inner join " + json_object["Child"] + " sd on sm.key_pk=sd.key_fk " +
                        " where sm.key_pk=" + invoice + " order by sn";

                data = IList_GetTable(query);
            }

            else if (table == "Invoice Previous")
            {
                int invoice = 0;
                invoice = Invoice_Previous(Convert.ToInt32(json_object["id"].ToString()), json_object["Master"].ToString(), json_object["Child"].ToString());
                query = "select distinct  sm.key_pk,sm.clkdate,sm.days,sm.duedate,sm.dscr_p as inv_disc,sm.disc as inv_disc_amt,sm.mrdminv_no,sm.invtype,sm.invoice_no,sm.acode,sm.prtclar,sm.expmnininv,sm.courier,sm.gstp,sm.gsta,sm.itp,sm.ita,sm.prtclar,sm.collection,sm.balance,sm.closed,sd.citmno,sd.prtno,sd.bn" +
                         ",sd.key_fk,sd.key_pk as sn,sd.acitno,sd.invdesc,sd.subdesc,sd.qty,sd.rate,sd.amt,sd.dscrate,sd.disc,sd.dscr_p,sd.ntamt,sd.line_warehouse from " + json_object["Master"] + " sm inner join " + json_object["Child"] + " sd on sm.key_pk=sd.key_fk " +
                         " where sm.key_pk=" + invoice + " order by sn";
                data = IList_GetTable(query);
            }

            else if (table == "Invoice First")
            {
                int invoice = 0;
                invoice = Invoice_First(json_object["Master"].ToString(), json_object["Child"].ToString());
                query = "select distinct  sm.key_pk,sm.clkdate,sm.days,sm.duedate,sm.dscr_p as inv_disc,sm.disc as inv_disc_amt,sm.mrdminv_no,sm.invtype,sm.invoice_no,sm.acode,sm.prtclar,sm.expmnininv,sm.courier,sm.gstp,sm.gsta,sm.itp,sm.ita,sm.prtclar,sm.collection,sm.balance,sm.closed,sd.citmno,sd.prtno,sd.bn" +
                        ",sd.key_fk,sd.key_pk as sn,sd.acitno,sd.invdesc,sd.subdesc,sd.qty,sd.rate,sd.amt,sd.dscrate,sd.disc,sd.dscr_p,sd.ntamt,sd.line_warehouse from " + json_object["Master"] + " sm inner join " + json_object["Child"] + " sd on sm.key_pk=sd.key_fk " +
                      " where sm.key_pk=" + invoice + " order by sn";
                data = IList_GetTable(query);
            }

            else if (table == "Invoice Last")
            {
                int invoice = 0;
                invoice = Invoice_Last(json_object["Master"].ToString(), json_object["Child"].ToString());
                query = "select distinct  sm.key_pk,sm.clkdate,sm.days,sm.duedate,sm.dscr_p as inv_disc,sm.disc as inv_disc_amt,sm.mrdminv_no,sm.invtype,sm.invoice_no,sm.acode,sm.prtclar,sm.expmnininv,sm.courier,sm.gstp,sm.gsta,sm.itp,sm.ita,sm.prtclar,sm.collection,sm.balance,sm.closed,sd.citmno,sd.prtno,sd.bn" +
                         ",sd.key_fk,sd.key_pk as sn,sd.acitno,sd.invdesc,sd.subdesc,sd.qty,sd.rate,sd.amt,sd.dscrate,sd.disc,sd.dscr_p,sd.ntamt,sd.line_warehouse from " + json_object["Master"] + " sm inner join " + json_object["Child"] + " sd on sm.key_pk=sd.key_fk " +
                        " where sm.key_pk=" + invoice + " order by sn";
                data = IList_GetTable(query);
            }

            else if (table == "General Current")
            {

                query = "select * from General_Journal where key_pk=" + Convert.ToInt32(json_object["id"].ToString()) + " Order by key_pk asc";

                data = IList_GetTable(query);

            }

            else if (table == "Get General")
            {

                query = "select *,convert(varchar,cast(date as date), 105) as edate from General_Journal  where gjno=" + Convert.ToInt32(json_object["invoice_no"].ToString()) + " Order by key_pk asc";

                data = IList_GetTable(query);

            }

            else if (table == "Find ReOrder")
            {
                JObject json_Object = JObject.Parse(json);

                String where = " where sm.key_pk>0 ";
                if (json_Object["from"].ToString() != String.Empty)
                {
                    where += " and  CAST(sm.clkdate AS DATE) >= CAST( '" + json_Object["from"].ToString() + "' AS DATE) ";

                }
                if (json_Object["to"].ToString() != String.Empty)
                {
                    where += " and CAST(sm.clkdate AS DATE) <= CAST( '" + json_Object["to"].ToString() + "' AS DATE) ";

                }


                if (json_Object["reorder_no"].ToString() != String.Empty)
                {
                    where += " and sm.invoice_no=" + Convert.ToInt32(json_Object["reorder_no"].ToString().Trim());
                }


                if (json_Object["entry_no"].ToString() != String.Empty)
                {
                    where += " and sm.key_pk=" + Convert.ToInt32(json_Object["entry_no"].ToString().Trim());
                }


                query = " select " + json_Object["records"].ToString() + " sm.key_pk as [Computrized No],sm.invoice_no as [Reorder.no],FORMAT(Cast( sm.clkdate as Date), 'dd-MM-yyyy') as [Reorder.Date]," +
                       " count(*) as [Items],Case when isnull(sm.closed,0)=0 then ''  else 'Closed'  end as Status" +
                       " from Purchase_Order sm inner join Purchase_Order_Detail sd on sm.key_pk=sd.key_fk  " +
                        where +
                        "  group by sm.key_pk,sm.invoice_no,sm.clkdate,sm.closed" +
                        " order by [Reorder.no],[Reorder.Date]";

                return IList_GetTable(query);
            }

            else if (table == "Get Stock")
            {
                query = "select sm.key_pk,sm.clkdate,sm.invoice_no,sm.mrdminv_no,sm.remarks,sd.citmno,sd.prtno,sd.bn " +
                ",sd.acitno,sd.invdesc,sd.tfq,sd.tflctncd as wh_From,sd.ttlctncd as wh_to,wh1.name as name_from,wh2.name as name_to,sd.remarks as line_remarks from " + json_object["Master"] + " sm inner join " + json_object["Child"] + " sd on sm.key_pk=sd.key_fk " +
                "inner join warehouse as wh1 on wh1.id=sd.tflctncd inner join warehouse as wh2 on wh2.id=sd.ttlctncd   where key_fk=" + json_object["id"].ToString();
                data = IList_GetTable(query);
            }

            else if (table == "Stock_Ledger")
            {

                String product = json_object["product"].ToString();
                query = "declare @id int;set @id=" + product + ";";

                query += " select s.idno,s.pidno,s.edate,ac.title,s.estatus," +
                            " s.rate,s.gross_amount,s.disc_rate,s.disc_p,s.disc,s.rec,s.iss,s.loose_iss,wh.name as warehouse,s.refno,s.description,s.remarks from stock s " +
                            " left join ac on ac.code=s.acode left join warehouse wh on wh.id=s.warehouse where product=@id order by edate,idno;";

                query += " select * from( " +
                         " select s.idno,s.pidno,s.edate,s.estatus,case when estatus='OP' then 'OPENING' when estatus='PU'" +
                         " or estatus='PI' then 'PURCHASE' when estatus='SR' then 'SALES RETURN'  end Particular," +
                         " ac.title,s.rate,s.gross_amount,s.disc_rate,s.disc_p,s.disc,s.rec as Qty,null as 'SubIssued',s.refno,s.description,wh.name as warehouse " +
                         "  from stock s left join warehouse wh on wh.id=s.warehouse left join ac on ac.code=s.acode where product=@id and ISNULL(rec,0)>0 " +

                         "  union all select s.idno,s.pidno,s.edate,s.estatus,case when estatus='SI' then 'SALES' " +
                         "  when estatus='PR' then 'PURCHASE RETURN'  end Particular,ac.title,s.rate,s.gross_amount,s.disc_rate,s.disc_p,s.disc," +
                         " s.iss as qty,case when isnull(s.loose_iss,0)>0 then " +
                         " (s.gross_amount-isnull(s.disc,0)) else null end,s.refno ,s.description,wh.name as warehouse from stock s " +
                         " left join warehouse wh on wh.id=s.warehouse " +
                         "  left join  ac on ac.code=s.acode where product=@id and ISNULL(rec,0)=0 " +
                         "  ) a order by a.edate";

                query += " select im.op_qty as Opening_Qty,im.op_amt as Opening_Amount,SI.Qty as Sales_Qty,SI.Amount as Sales_Amount,SR.Qty as Sales_Return_Qty,SR.Amount as Sales_Return_Amount," +
                         "   PI.Qty as Purchase_Qty,PI.Amount as Purchase_Amount,PR.Qty as Purchase_Return_Qty,PR.Amount as Purchase_Return_Amount from itemlinked im " +
                         "  left join " +
                         "  (select sd.acitno,sum(sd.qty) as Qty,sum((sd.qty*sd.rate)-sd.disc) as Amount from  Sales_Detail sd  where acitno=@id group by sd.acitno " +
                         "  )SI on SI.acitno=im.idno   left join " +
                         "  (select sd.acitno,sum(sd.qty) as Qty,sum((sd.qty*sd.rate)-sd.disc) as Amount from  Sales_Return_Detail sd  where acitno=@id group by sd.acitno " +
                         "  )SR on SI.acitno=im.idno left join " +
                         "  (select sd.acitno,sum(sd.qty) as Qty,sum((sd.qty*sd.rate)-sd.disc) as Amount from  Purchase_Detail sd  where acitno=@id group by sd.acitno " +
                         "  )PI on PI.acitno=im.idno   left join  " +
                         "  (select sd.acitno,sum(sd.qty) as Qty,sum((sd.qty*sd.rate)-sd.disc) as Amount from  Purchase_Return_Detail sd  where acitno=@id group by sd.acitno " +
                         "  )PR on PR.acitno=im.idno " +
                         "  where im.idno=@id;";

                query += "   select sm.key_pk,sm.clkdate,sm.invoice_no,sd.ttq as qty,w2.name as from_wh,w1.name as to_wh,sd.remarks from Stock_Transfer_Master sm " +
                         "   inner join Stock_Transfer_Detail sd on sm.key_pk=sd.key_fk " +
                         "    inner join warehouse w1 on rtrim(w1.id)=rtrim(sd.ttlctncd) " +
                         "    inner join warehouse w2 on rtrim(w2.id)=rtrim(sd.tflctncd) " +
                         "    where acitno=@id;";

                query += "  select t1.key_pk,t1.invoice_no,ac.title,t1.vn_number,t1.reg_number,t1.date,t1.Quotation_Qty,t2.Sales_Qty,t2.Inv_Rate,u.USERS_NAME,ac.code,au.USERS_NAME as auth_by,t1.closed,qd.* from(  " +
                          "  select qm.key_pk,qm.invoice_no,qm.date,qm.acode,qm.vn_number,qm.reg_number,qm.users,qm.auth_by,qm.closed,qd.acitno,qd.rate, sum(qd.qty) as Quotation_Qty " +
                          "  from Quotation_Master qm inner join Quotation_Detail qd on qm.key_pk = qd.key_fk where qd.acitno=@id  " +
                          "  group by qm.key_pk,qm.invoice_no,qm.vn_number,qm.reg_number,qm.date,qm.acode,qm.users,qm.closed,qm.auth_by, qd.acitno,qd.rate)t1 left JOIN(  " +
                          "  select qm.key_pk, qm.quotation_no, qd.acitno,qd.rate as Inv_Rate, sum(qd.qty) as Sales_Qty from Sales_Master qm  " +
                          "  inner join Sales_Detail qd on qm.key_pk = qd.key_fk  " +
                          "  where acitno=@id  and quotation_no is not null group by qm.quotation_no,qm.key_pk,qd.acitno,qd.rate)t2 on t1.key_pk = t2.quotation_no   " +
                          "  left join USERS u on u.USERS=t1.users left join USERS au on au.USERS=t1.auth_by inner join ac on ac.code=t1.acode  inner join itemlinked im on im.idno=t1.acitno inner join brand br on im.brnad=br.code " +
                          "  inner join Quotation_Detail qd on qd.key_fk=t1.key_pk and qd.acitno=@id ;";

                query += " select distinct sm.invoice_no as [Invno],FORMAT(Cast( sm.clkdate as Date), 'dd-MM-yyyy') as [InvDate], " +
                         "   sd.qty as [Qty],sd.rate as [Rate],sd.amt as [Amount],sd.dscrate as[Disc-Rate],sd.dscr_p as [Disc-Per], " +
                         "  sd.disc as [DiscAmount],gstp as [Gst - Per],gsta as [GstAmount],(sd.amt - sd.disc + gsta) as [NetAmount],isnull(sm.closed, 0) as Closed,u.USERS_NAME,sm.key_pk " +
                         "  from Purchase_Order sm inner " +
                         "  join Purchase_Order_Detail sd on sm.key_pk = sd.key_fk " +
                         " left join USERS u on u.USERS=sm.users " +
                         "  where sd.acitno=@id order by Invno;";

                query += " select *,FORMAT(Cast( am.clkdate as Date), 'dd-MM-yyyy') as [ArrDate] from Arrivals_Master am " +
                         " inner join Arrival_Detail ad on am.key_pk=ad.key_fk left join USERS u on u.USERS=am.users where acitno=@id order by am.key_pk;";

                query += " select st.warehouse,wh.name,(sum(ISNULL(rec,0))-sum(ISNULL(iss,0))) as Balance from stock st " +
                         "    inner join warehouse wh on wh.id = st.warehouse inner join itemlinked im on im.idno = st.product " +
                         "   where im.idno= @id group by product,st.warehouse,wh.name,im.partno order by wh.name;";
                return IList_GetDataSet(query);
            }

            else if (table == "Log")
            {
                String user = Convert.ToString(json_object["user"]).Trim();
                String module = Convert.ToString(json_object["module"]).Trim();
                String from = Convert.ToString(json_object["from"]).Trim();
                String to = Convert.ToString(json_object["to"]).Trim();
                int entry = Convert.ToInt32(json_object["entry"].ToString());


                query = "  select id as Idno,Entry as Entryno,edate as Datetime,type as Module,l_user as Users,s_user as System_Users,remarks as Reamrks,type" +
                        "    from Log where id is not null";

                if (from != String.Empty && to != String.Empty)
                {

                    query += " and CAST(edate AS DATE) >= CAST( '" + from + "' AS DATE)  AND CAST(edate AS DATE) <= CAST( '" + to + "' AS DATE) ";

                }
                if (user != String.Empty)
                {
                    query += " and l_user='" + user + "'";
                }
                if (module != String.Empty)
                {
                    query += " and type='" + module + "'";
                }
                if (entry > 0)
                {
                    query += " and Entry=" + entry;
                }


                data = IList_GetTable(query);
            }

            else if (table == "Get Log")
            {
                int entry = Convert.ToInt32(json_object["id"].ToString());
                query = "select data  from Log where id=" + entry;
                data = IList_ExecuteScalerValue_String(query);
            }

            else if (table == "Log Insert")
            {

                String system = json_object["User"].ToString();
                String User = json_object["System"].ToString();
                String desc = json_object["desc"].ToString();

                query = "insert into Log(Entry,type,edate,s_user,l_user,remarks,data) values(null,'Lg','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + (User) + "','" + (system) + "','" + desc + "','')";
                data = Convert.ToString(IList_execute(query));
            }
            
            else if (table == "Find CR")
            {
                String entry = Convert.ToString(json_object["entry"]).Trim();
                String maincode = Convert.ToString(json_object["maincode"]).Trim();
                String acode = Convert.ToString(json_object["acode"]).Trim();
                String from = Convert.ToString(json_object["from"]).Trim();
                String to = Convert.ToString(json_object["to"]).Trim();
                String amount = Convert.ToString(json_object["amount"]).Trim();
                String type = Convert.ToString(json_object["type"]).Trim();
                String chequeno = Convert.ToString(json_object["chequeno"]).Trim();
                query = " select vm.idno,vm.Entry,Convert(varchar(11) ,vm.edate,105) as Date,a.TITLE,vd.description,isnull(vd.debit,0)+ISNULL(vd.credit,0) as Amount,vd.chequeno from VoucherMaster vm,VoucherDetail vd ,ac a where vm.idno=vd.MEntry and vd.acode=a.code and vm.type='" + type + "'";

                if (entry != String.Empty)
                {
                    query += " and vm.entry= " + Convert.ToInt32(entry);
                }
                if (maincode != String.Empty)
                {
                    query += " and RTRIM(vm.maincode)='" + (maincode.ToString().Trim()) + "'";
                }
                if (acode != String.Empty)
                {
                    query += " and RTRIM(vd.acode)='" + (acode).ToString().Trim() + "'";
                }

                if (from != String.Empty && to != String.Empty)
                {

                    query += "  and CAST(vm.edate AS DATE) >= CAST( '" + from + "' AS DATE)  AND CAST(vm.edate AS DATE) <= CAST( '" + to + "' AS DATE) ";

                }

                if (amount != String.Empty)
                {
                    query += " and (vd.debit=" + Convert.ToDecimal(amount) + " or vd.credit=" + Convert.ToDecimal(amount) + ")";
                }
                if (chequeno != String.Empty)
                {
                    query += " and vd.chequeno='" + chequeno + "'";
                }

                data = IList_GetTable(query);
            }

            else if (table == "Find RT")
            {
                String date = Convert.ToString(json_object["cheqdate"]).Trim();
                String amount = Convert.ToString(json_object["amount"]).Trim();
                String chequeno = Convert.ToString(json_object["cheqno"]).Trim();
                query = " select vd.id,vm.Entry,Convert(varchar(11) ,vm.edate,105) as Date,a.TITLE,vd.description,isnull(vd.debit,0)+ISNULL(vd.credit,0) as Amount,vd.chequeno,vm.idno from VoucherMaster vm,VoucherDetail vd ,ac a where vm.idno=vd.MEntry and vd.acode=a.code and vm.type='BP'";


                if (date != String.Empty)
                {

                    query += "  and CAST(vd.cheqdate AS DATE) >= CAST( '" + date + "' AS DATE) ";

                }

                if (amount != String.Empty)
                {
                    query += " and (vd.debit=" + Convert.ToDecimal(amount) + " or vd.credit=" + Convert.ToDecimal(amount) + ")";
                }
                if (chequeno != String.Empty)
                {
                    query += " and vd.chequeno='" + chequeno.Trim() + "'";
                }

                data = IList_GetTable(query);
            }

            else if (table == "Get Pending Cheque")
            {
                bool valid = false;
                String where = " ";
                DataTable dt = (DataTable)JsonConvert.DeserializeObject(json_object["data"].ToString(), (typeof(DataTable)));
                foreach (DataRow row in dt.Rows)
                {
                    if (!valid)
                    {
                        where += " where vd.id=" + row[0].ToString();
                        valid = true;
                    }
                    else
                    {
                        where += " or vd.id=" + row[0].ToString();
                    }
                }


                query = "select *,(select acode from VoucherDetail where Id=vd.issued) as Rec from VoucherMaster vm inner join VoucherDetail vd on vm.idno=vd.MEntry  " + where;

                data = IList_GetTable(query);
            }

            else if (table == "Print Bank Voucher")
            {

                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.idno=" + json_object["idno"].ToString()
                        + " Select name,address,contactno from companysetup "
                        + " Select top 1 l_user from Log where type='" + json_object["type"].ToString() + "' and Entry=" + json_object["vno"].ToString() + " order by id ";

                data = IList_GetDataSet(query);
            }

            else if (table == "Get Product Like")
            {
                query = " select idno,partno,linkno from itemlinked where linkno like '%" + Convert.ToString(json_object["txt"]).Trim() + "%' ;";
                data = IList_GetTable(query);
            }

            else if (table == "Find Invoice")
            {
                String child = "";
                string where = "";
                if (json != String.Empty)
                {
                    json_object = JObject.Parse(json);


                    if (Convert.ToString(json_object["type"]) != String.Empty)
                    {
                        where += " and ac.iden='" + Convert.ToString(json_object["type"]) + "' ";
                    }
                    if (Convert.ToString(json_object["from"]).Trim() != String.Empty)
                    {
                        where += "  and CAST(sm.date AS DATE) >= CAST( '" + Convert.ToString(json_object["from"]) + "' AS DATE) ";

                    }

                    if (Convert.ToString(json_object["to"]).Trim() != String.Empty)
                    {
                        where += "  AND CAST(sm.date AS DATE) <= CAST( '" + Convert.ToString(json_object["to"]) + "' AS DATE) ";

                    }
                    if (Convert.ToString(json_object["invoice"]) != String.Empty)
                    {
                        where += " and sm.invoice_no=" + Convert.ToString(json_object["invoice"]) + " ";
                    }

                    if (Convert.ToString(json_object["bookno"]) != String.Empty)
                    {
                        where += " and sm.mrdminv_no='" + Convert.ToString(json_object["bookno"]) + "' ";
                    }

                    if (Convert.ToString(json_object["netamount"]) != String.Empty)
                    {
                        where += " and sm.ttlamtnet=" + Convert.ToDecimal(json_object["netamount"]) + " ";
                    }

                    if (Convert.ToString(json_object["warehouse"]) != String.Empty)
                    {

                        where += " and  sm.warehouse=" + Convert.ToInt32(json_object["warehouse"].ToString()) + " ";
                    }

                    if (Convert.ToString(json_object["city"]) != String.Empty)
                    {
                        where += " and  ac.city_code='" + (json_object["city"]) + "' ";
                    }
                    if (Convert.ToString(json_object["account"]) != String.Empty)
                    {
                        where += " and  ac.code='" + Convert.ToString(json_object["account"]) + "' ";
                    }
                    if (Convert.ToString(json_object["mode"]) != String.Empty)
                    {
                        where += " and  sm.invtype='" + Convert.ToString(json_object["mode"]) + "' ";
                    }
                }
                if (json_object["type"].ToString() == "Sales_Master")
                {
                    table = "Sales_Master";
                    child = " Sales_Detail";
                }

                if (json_object["type"].ToString() == "Sales_Return_Master")
                {
                    table = " Sales_Return_Master";
                    child = " Sales_Return_Detail ";
                }

                if (json_object["type"].ToString() == "Purchase_Master")
                {
                    table = "Purchase_Master";
                    child = " Purchase_Detail ";
                }

                if (json_object["type"].ToString() == "Purchase_Return_Master")
                {
                    table = "Purchase_Return_Master";
                    child = " Purchase_Return_Detail ";
                }

                query = " Select " + json_object["record"].ToString().Trim() + " sm.key_pk,sm.invoice_no as [Cmp-InvNo],sm.mrdminv_no as [Book-InvNo],Convert(varchar(11) ,sm.date,105) as [Inv-Date],ac.title as [Party-Name],ac.code as [Party-Code],sum(ISNULL(sd.qty,0)) as TotalItem,sum(ISNULL(sd.ntamt,0))as [Total-Amt-Net],sm.invtype as Type,wh.name as GoDown from " + table + " as sm" +
                  " left join ac on ac.code=sm.acode left join warehouse wh on wh.id=sm.warehouse" +
                  " inner join " + child + " sd on sm.key_pk=sd.key_fk   where sm.key_pk is not null " + where + "" +
                  "  group by sm.key_pk,sm.invoice_no,sm.mrdminv_no ,ac.title,ac.code,sm.invtype,sm.date ,wh.name order by sm.invoice_no desc";

            }


            return data;
        }

        int IList_Lastid(String query)
        {
            int IsInsert = 0;
            try
            {
                using (con = new SqlConnection(cs))
                {
                    con.Open();
                    cm = new SqlCommand(query, con);
                    var val = cm.ExecuteScalar();
                    IsInsert = (val == null) ? 1 : (Convert.ToInt32(val));
                }
            }
            catch (Exception ex)
            {
                excp(ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
            return IsInsert;
        }

        int IList_execute(String query)
        {
            int IsInsert = 0;
            SqlTransaction trans = null;
            try
            {
                using (con = new SqlConnection(cs))
                {
                    con.Open();
                    trans = con.BeginTransaction();
                    cm = new SqlCommand(query, con, trans);
                    IsInsert = Convert.ToInt32(cm.ExecuteNonQuery());
                    if (IsInsert > 0)
                    {
                        trans.Commit();
                    }
                    else
                    {
                        trans.Rollback();
                        IsInsert = 0;
                    }
                }
            }
            catch (Exception ex)
            {

                IsInsert = 0;
                excp(ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
            return IsInsert;
        }

        int IList_ExecuteScalerValue(String query)
        {
            int IsInsert = 0;
            //  File.WriteAllText(@"D:\error.txt",query);
            try
            {
                using (con = new SqlConnection(cs))
                {
                    con.Open();
                    cm = new SqlCommand(query, con);
                    IsInsert = Convert.ToInt32(cm.ExecuteScalar());
                }
            }
            catch (Exception ex)
            {
                IsInsert = 0;
                excp(ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
            return IsInsert;
        }

        String IList_ExecuteScalerValue_String(String query)
        {
            String value = "";
            try
            {
                using (con = new SqlConnection(cs))
                {
                    con.Open();
                    cm = new SqlCommand(query, con);
                    value = Convert.ToString(cm.ExecuteScalar());
                }
            }
            catch (Exception ex)
            {
                value = "";
                excp(ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
            return value;
        }

        int IList_bulkexecute(SqlCommand cm, String query)
        {
            int IsInsert = 0;
            try
            {
                using (con = new SqlConnection(cs))
                {
                    con.Open();
                    cm = new SqlCommand(query, con);
                    IsInsert = Convert.ToInt32(cm.ExecuteNonQuery());
                }
            }
            catch (Exception ex)
            {
                excp(ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
            return IsInsert;
        }

        int Invoice_Next(int id, String master, String child)
        {
            String query = "select distinct top 1  sm.key_pk from " + master + " sm where sm.key_pk>" + id + " order by sm.key_pk asc";

            return IList_ExecuteScalerValue(query);
        }

        int Invoice_Previous(int id, String master, String child)
        {
            String query = "select distinct top 1  sm.key_pk from " + master + " sm   where sm.key_pk < " + id + " order by sm.key_pk desc";


            return IList_ExecuteScalerValue(query);
        }

        int Invoice_First(String master, String child)
        {
            String query = "select distinct top 1 sm.key_pk from " + master + " sm order by sm.key_pk asc ";
            return IList_ExecuteScalerValue(query);
        }

        int Invoice_Last(String master, String child)
        {
            String query = "select distinct top 1 sm.key_pk from " + master + " sm order by sm.key_pk desc ";

            return IList_ExecuteScalerValue(query);
        }

        int General_Next(int id)
        {
            String query = "select distinct top 1  sm.key_pk from General_Journal sm where sm.key_pk>" + id + " order by sm.key_pk asc";
            return IList_ExecuteScalerValue(query);
        }

        int General_Previous(int id)
        {
            String query = "select distinct top 1  sm.key_pk from General_Journal sm   where sm.key_pk < " + id + " order by sm.key_pk desc";
            return IList_ExecuteScalerValue(query);
        }

        public String IList_GetTable(String query)
        {
            String JSONString = "";
            dt = new DataTable();
            try
            {
                using (con = new SqlConnection(cs))
                {
                    con.Open();


                    using (adp = new SqlDataAdapter(query, con))
                    {
                        cmd = new SqlCommandBuilder(adp);
                        adp.Fill(dt);

                    }

                }

                JSONString = JsonConvert.SerializeObject(dt);


            }
            catch (Exception ex)
            {
                excp(ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }

            return JSONString;

        }

        public String IList_GetDataSet(String query)
        {
            String JSONString = "";
            ds = new DataSet();
            try
            {
                using (con = new SqlConnection(cs))
                {
                    con.Open();


                    using (adp = new SqlDataAdapter(query, con))
                    {
                        cmd = new SqlCommandBuilder(adp);
                        adp.Fill(ds);

                    }

                }

                JSONString = JsonConvert.SerializeObject(ds, Formatting.Indented);


            }
            catch (Exception ex)
            {
                excp(ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }

            return JSONString;

        }

        public String List_Accounts()
        {
            dt = new DataTable();
            string JSONString = string.Empty;
            String Query = "SELECT acode,cast(LTRIM(RTRIM(ac.TITLE)) as varchar) +''+ cast( LTRIM(RTRIM(isnull(t.NAME,''))) as varchar) as Title FROM AC left join TOWN t on t.CODE= ac.TOWN  where   SOUL is null and type='Child' and ac.ACTIVE=1 order by title";
            try
            {
                using (con = new SqlConnection(cs))
                {
                    con.Open();


                    using (adp = new SqlDataAdapter(Query, con))
                    {
                        cmd = new SqlCommandBuilder(adp);
                        adp.Fill(dt);

                    }

                }

                JSONString = JsonConvert.SerializeObject(dt);


            }
            catch (Exception ex)
            {
                excp(ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }

            return JSONString;

        }

        public String Ledger(int code, String from, String to)
        {
            dt = new DataTable();

            string JSONString = string.Empty;
            String Query = "       select l.* from AC_LEDGER(" + code + ") as l left join Notes n on l.type=estatus and l.Ref=n.refno  where   CAST(edate AS DATE) >= CAST( '" + from + "' AS DATE)  AND CAST(edate AS DATE) <= CAST( '" + to + "' AS DATE) ";

            // String Query = query1 + query2 + date;

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


                    using (adp = new SqlDataAdapter(Query, con))
                    {
                        cmd = new SqlCommandBuilder(adp);
                        adp.Fill(dt);

                    }

                }

                JSONString = JsonConvert.SerializeObject(dt);


            }
            catch (Exception ex)
            {
                excp(ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }

            return JSONString;

        }

        public String Order_Entry(int entry, String type)
        {


            String q1 = " select om.zone,om.supervisor,om.saleman,om.town,om.customer,om.remarks,om.transport,isnull(om.com,0) as com,om.freight,om.extfreight,om.bilty,om.posted,om.edate,om.refno,om.driver,om.vehicle,om.round,od.*, cast( round(od.pcs_box*od.to_pcs*od.qty,0) as decimal(10,2)) as trade ,om.refno,isnull(od.com,0) as comrate,isnull(od.to_target,0) as totarget from OrderMaster om ";
            String q2 = " inner join order_detail od on om.Entry=od.MEntry where estatus='" + type + "' and om.Entry=" + entry;
            String query = q1 + q2;
            return IList_GetTable(query);

        }

        void excp(String msg)
        {
            String query = " insert into Excep(msg,edate) values(@msg,@edate)";
            try
            {
                using (con = new SqlConnection(cs))
                {
                    con.Open();
                    cm = new SqlCommand(query, con);
                    cm.Parameters.AddWithValue("msg", msg);
                    cm.Parameters.AddWithValue("edate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    cm.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                excp(ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }

        }

        string get_unique_string()
        {
            int string_length = 25;
            using (var rng = new RNGCryptoServiceProvider())
            {
                var bit_count = (string_length * 6);
                var byte_count = ((bit_count + 7) / 8); // rounded up
                var bytes = new byte[byte_count];
                rng.GetBytes(bytes);
                return Convert.ToBase64String(bytes);
            }
        }



    }
}
