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

namespace WorkShop_Services
{
    public partial class Service1 : IService1
    {
        public int PayRoll_IList_Json_Insert(String json, String table)
        {
            int rowsEffected = 0;
            String query = "";
            int update = 0;
            JObject json_object = JObject.Parse(json);
            if (table == "designation")
            {
                if (json_object["code"].ToString().Trim() == String.Empty)
                {
                    query = " select top 1 cast(code as int) from designation_list order by cast (code as Int)  desc ";
                    string value = (Convert.ToInt32(PayRoll_IList_ExecuteScalerValue(query)) + 1).ToString().PadLeft(4, '0').ToString();

                    query = "Insert Into designation_list(Code,Name) values('" + value + "','" + json_object["name"].ToString() + "');";

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

            if (table == "department")
            {

                if (json_object["code"].ToString().Trim() == String.Empty)
                {
                    query = " select top 1 cast(code as int) from department order by cast (code as Int)  desc ";
                    string value = (Convert.ToInt32(PayRoll_IList_ExecuteScalerValue(query)) + 1).ToString().PadLeft(4, '0').ToString();
                    query = "Insert Into department(Code,Name) values('" + value + "','" + json_object["name"].ToString() + "');";

                }
                else
                {
                    query = " Update department set name='" + json_object["name"].ToString() + "' where code='" + json_object["code"].ToString() + "';";
                }
                rowsEffected = PayRoll_IList_execute(query);

            }
            if (table == "qualification")
            {

                if (json_object["code"].ToString().Trim() == String.Empty)
                {
                    query = " select top 1 cast(code as int) from qualification_list order by cast (code as Int)  desc ";
                    string value = (Convert.ToInt32(PayRoll_IList_ExecuteScalerValue(query)) + 1).ToString().PadLeft(4, '0').ToString();
                    query = "Insert Into qualification_list(Code,Name) values('" + value + "','" + json_object["name"].ToString() + "');";

                }
                else
                {
                    query = " Update qualification_list set name='" + json_object["name"].ToString() + "' where code='" + json_object["code"].ToString() + "';";
                }
                rowsEffected = PayRoll_IList_execute(query);

            }
            if (table == "employe_type")
            {

                if (json_object["code"].ToString().Trim() == String.Empty)
                {
                    query = " select top 1 cast(code as int) from employee_type_list order by cast (code as Int)  desc ";
                    string value = (Convert.ToInt32(PayRoll_IList_ExecuteScalerValue(query)) + 1).ToString().PadLeft(4, '0').ToString();
                    query = "Insert Into employee_type_list(Code,Name) values('" + value + "','" + json_object["name"].ToString() + "');";

                }
                else
                {
                    query = " Update employee_type_list set name='" + json_object["name"].ToString() + "' where code='" + json_object["code"].ToString() + "';";
                }
                rowsEffected = PayRoll_IList_execute(query);

            }
            if (table == "settlement")
            {

                if (json_object["code"].ToString().Trim() == String.Empty)
                {
                    query = " select top 1 cast(code as int) from fsettle order by cast (code as Int)  desc ";
                    string value = (Convert.ToInt32(PayRoll_IList_ExecuteScalerValue(query)) + 1).ToString().PadLeft(4, '0').ToString();
                    query = "Insert Into fsettle(Code,Name) values('" + value + "','" + json_object["name"].ToString() + "');";

                }
                else
                {
                    query = " Update fsettle set name='" + json_object["name"].ToString() + "' where code='" + json_object["code"].ToString() + "';";
                }
                rowsEffected = PayRoll_IList_execute(query);

            }
            if (table == "shift")
            {

                if (json_object["code"].ToString().Trim() == String.Empty)
                {
                    query = " select top 1 cast(code as int) from shifts_list order by cast (code as Int)  desc ";
                    string value = (Convert.ToInt32(PayRoll_IList_ExecuteScalerValue(query)) + 1).ToString().PadLeft(4, '0').ToString();
                    query = "insert into shifts_list(code,name,shift_start_time,shift_end_time,latetime_start,overtime_start,early_go_time,duty_working_hrs,half_day_start,half_day_end)" +
                        " values('" + value + "','" + json_object["name"].ToString() + "','" + json_object["start"].ToString() + "','" + json_object["end"].ToString() + "'" +
                        ",'" + json_object["late"].ToString() + "','" + json_object["over"].ToString() + "','" + json_object["early"].ToString() + "'," + Convert.ToDecimal(json_object["hours"].ToString()) + "" +
                        ",'" + json_object["hstart"].ToString() + "','" + json_object["hend"].ToString() + "');";

                }
                else
                {
                    query = "update shifts_list set name='" + json_object["name"].ToString() + "',shift_start_time='" + json_object["start"].ToString() + "',shift_end_time='" + json_object["end"].ToString() + "',latetime_start='" + json_object["late"].ToString() + "'" +
                    ",overtime_start='" + json_object["over"].ToString() + "',early_go_time='" + json_object["early"].ToString() + "',duty_working_hrs=" + json_object["hours"].ToString() + ",half_day_start='" + json_object["hstart"].ToString() + "',half_day_end='" + json_object["hend"].ToString() + "'" +
                    " where code='" + json_object["code"].ToString() + "';";
                }
                rowsEffected = PayRoll_IList_execute(query);

            }

            if (table == "employee_list")
            {

                query = " SELECT top 1 cast(SUBSTRING(ecode,CHARINDEX('-',ecode)+1,LEN(ecode)) as Int) FROM employee " +
                        " order by cast(SUBSTRING(ecode,CHARINDEX('-',ecode)+1,LEN(ecode)) as Int) desc";

                String ecode = "14-" + (PayRoll_IList_ExecuteScalerValue(query) + 1).ToString().PadLeft(3, '0');

                query = " INSERT INTO [dbo].[employee] " +
                        " ([ecode],[name],[department],[designation],[etype],[gender],[shift],[app_date],[date_of_birth],[disch_date]," +
                        " [address],[cnic],[qualification],[contact_no],[mobile_no],[marital_status],[language],[age],[blood_group] ,[eobino]" +
                        ",[ntnno] ,[religion],[nationality],[vname] ,[replacement_of],[bank_name],[bank_ac_no],[bank_branch],[permanent_date],[pcard_mno]" +
                        ",[emp_category],[sbasic],[shr],[sconvey],[smedical],[sutility],[sothers],[salary],[pay_mode],[allow_overtime]," +
                        "[overtime_perhour],[allow_late],[allow_90_min_late],[late_deduct] ,[late_deduct_days],[employee_not_active])" +

                        " values ('" + ecode + "','" + json_object["name"] + "','" + json_object["depart"] + "','" + json_object["desig"] + "'," +
                        "'" + json_object["etype"] + "','" + json_object["gender"] + "','" + json_object["shift"] + "','" + json_object["app_date"] + "'," +
                        "'" + json_object["dob_date"] + "'," +

                        (json_object["disch_date"].ToString() == String.Empty ? "null" : "'" + json_object["disch_date"] + "'") + "," +

                        "'" + json_object["address"] + "','" + json_object["cnic"] + "','" + json_object["qual"] + "','" + json_object["contact_no"] + "','" + json_object["mobile_no"] + "'," +
                        "'" + json_object["marital"] + "','" + json_object["language"] + "'," + json_object["age"] + ",'" + json_object["bgroup"] + "','" + json_object["eobi"] + "','" + json_object["ntn"] + "'" +
                        ",'" + json_object["religion"] + "','" + json_object["nationality"] + "'," +

                        "'" + json_object["vname"] + "','" + json_object["replacement"] + "','" + json_object["bank_name"] + "','" + json_object["bank_ac"] + "','" + json_object["bank_branch"] + "'" +
                        "," + (json_object["per_date"].ToString() == String.Empty ? "null" : "'" + json_object["per_date"] + "'") + ",'" + json_object["pcardno"] + "'" +
                        ",'" + json_object["emp_cat"] + "'," + Convert.ToDecimal(json_object["basics"]) + "," + Convert.ToDecimal(json_object["rent"]) + "," + Convert.ToDecimal(json_object["convey"]) +
                        "," + Convert.ToDecimal(json_object["medical"]) + "," + Convert.ToDecimal(json_object["utility"]) + "," + Convert.ToDecimal(json_object["others"]) + "," + Convert.ToDecimal(json_object["salary"]) +
                        ",'" + json_object["paymode"] +

                        "'," + Convert.ToInt32(json_object["allow_overtime"]) + "," + Convert.ToDecimal(json_object["overtime"]) +
                         "," + Convert.ToInt32(json_object["allow_late"]) + "," + Convert.ToDecimal(json_object["latemin"]) +
                         "," + Convert.ToInt32(json_object["late_deduct"]) + "," + Convert.ToDecimal(json_object["late_days"]) +
                        "," + Convert.ToInt32(json_object["left"]) + ")";

                rowsEffected = PayRoll_IList_execute(query);
            }

            return rowsEffected;
        }

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

            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 = PayRoll_IList_execute(query);

            }
            else if (table == "employee_list")
            {
                query = " update employee " +
                        " set name='" + json_object["name"] + "',department='" + json_object["depart"] + "'," +
                        " designation='" + json_object["desig"] + "',etype='" + json_object["etype"] + "'," +
                        " gender='" + json_object["gender"] + "',shift='" + json_object["shift"] + "'," +
                        " app_date='" + json_object["app_date"] + "',date_of_birth='" + json_object["dob_date"] + "'," +
                        " disch_date=" + (json_object["disch_date"].ToString() == String.Empty ? "null" : "'" + json_object["disch_date"] + "'") + "," +
                        " address='" + json_object["address"] + "',cnic='" + json_object["cnic"] + "',qualification='" + json_object["qual"] + "'," +
                        " contact_no='" + json_object["contact_no"] + "',mobile_no='" + json_object["mobile_no"] + "'," +
                        " marital_status='" + json_object["marital"] + "',language='" + json_object["language"] + "',age=" + json_object["age"] + "," +
                        " blood_group='" + json_object["bgroup"] + "', eobino='" + json_object["eobi"] + "'," +
                        " ntnno='" + json_object["ntn"] + "', religion='" + json_object["religion"] + "'," +
                        " nationality='" + json_object["nationality"] + "',vname='" + json_object["vname"] + "'," +
                        " replacement_of='" + json_object["replacement"] + "',bank_name='" + json_object["bank_name"] + "'," +
                        " bank_ac_no='" + json_object["bank_ac"] + "',bank_branch='" + json_object["bank_branch"] + "'," +
                        " permanent_date=" + (json_object["per_date"].ToString() == String.Empty ? "null" : "'" + json_object["per_date"] + "'") + "," +
                        " pcard_mno='" + json_object["pcardno"] + "'," +
                        " emp_category='" + json_object["emp_cat"] + "',sbasic=" + Convert.ToDecimal(json_object["basics"]) + "," +
                        " shr=" + Convert.ToDecimal(json_object["rent"]) + ",sconvey=" + Convert.ToDecimal(json_object["convey"]) + "," +
                        " smedical=" + Convert.ToDecimal(json_object["medical"]) + ",sutility=" + Convert.ToDecimal(json_object["utility"]) + "," +
                        " sothers=" + Convert.ToDecimal(json_object["others"]) + ",salary=" + Convert.ToDecimal(json_object["salary"]) + "," +
                        " pay_mode='" + json_object["paymode"] + "',allow_overtime=" + Convert.ToInt32(json_object["allow_overtime"]) + "," +
                        " overtime_perhour=" + Convert.ToDecimal(json_object["overtime"]) + ",allow_late=" + Convert.ToInt32(json_object["allow_late"]) + "," +
                        " allow_90_min_late=" + Convert.ToDecimal(json_object["latemin"]) + ",late_dedut=" + Convert.ToInt32(json_object["late_deduct"]) + "," +
                        " late_deduct_days=" + Convert.ToInt32(json_object["late_days"]) + ",employee_not_active=" + Convert.ToInt32(json_object["left"]) +
                        " where ecode='" + (json_object["ecode"].ToString().Trim()) + "'";
                rowsEffected = PayRoll_IList_execute(query);


            }

            return rowsEffected;
        }

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

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


            }
            if (table == "department")
            {

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


            }
            if (table == "qualification")
            {

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


            }
            if (table == "employe_type")
            {

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


            }
            if (table == "settlement")
            {

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


            }
            if (table == "shift")
            {

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


            }

            if (table == "employee_list")
            {
                query = "delete from Attendance where ecode='" + json_object["ecode"].ToString().Trim() + "'"
                + "delete from employee where rtrim(ecode)='" + json_object["ecode"].ToString().Trim() + "'";

            }

            rowsEffected = PayRoll_IList_execute(query);



            return rowsEffected;
        }

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

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


            IsUpdate = PayRoll_IList_execute(query);

            return IsUpdate;
        }

        public String PayRoll_IList_Data(String table)
        {
            String query = "";
            String json = "";
            table = table.Trim();
            if (table == "designation")
            {

                query = "select * from designation_list order by CAST(code as Int) ";
                json = PayRoll_IList_GetTable(query);

            }
            if (table == "department")
            {

                query = "select * from department order by CAST(code as Int) ";
                json = PayRoll_IList_GetTable(query);

            }
            if (table == "qualification")
            {

                query = "select * from qualification_list order by CAST(code as Int) ";
                json = PayRoll_IList_GetTable(query);

            }
            if (table == "employe_type")
            {

                query = "select * from employee_type_list order by CAST(code as Int) ";
                json = PayRoll_IList_GetTable(query);

            }
            if (table == "settlement")
            {

                query = "select * from fsettle order by CAST(code as Int) ";
                json = PayRoll_IList_GetTable(query);

            }
            if (table == "shift")
            {

                query = "select * from shifts_list order by CAST(code as Int) ";
                json = PayRoll_IList_GetTable(query);

            }
            if (table == "emplyee_list")
            {

                query = " select ecode  ,name from employee name order by name;" +
                        " select  code ,name from department order by name; " +
                        " select  code ,name from designation_list order by name; " +
                        " select  code ,name from qualification_list order by name; " +
                        " select  code ,name from employee_type_list order by name; " +
                        " select  code ,name+'('+shift_start_time+'-'+shift_end_time+')' as name from shifts_list order by name;";

                json = PayRoll_IList_GetDataSet(query);
            }
            if (table == "Single_Attendance_Load")
            {
                query = " select ecode,name from employee order by name";
                json = PayRoll_IList_GetTable(query);
            }

            return json;
        }

        public String PayRoll_IList_Data_Json(String type, String json)
        {
            String query = "";
            String data = "";
            JObject json_object = JObject.Parse(json);
            if (type == "shift")
            {
                String code = (json_object["code"].ToString().Trim());

                query = " select * from shifts_list where rtrim(code)='" + code + "' order by cast(code as Int);";
                data = PayRoll_IList_GetTable(query);

            }
            if (type == "employee_list")
            {
                query = " select * from employee where rtrim(ecode)='" + json_object["ecode"].ToString().Trim() + "'";
                data = PayRoll_IList_GetTable(query);
            }
            if (type == "getatt")
            {
                query = " select d.ecode,d.name,d.shift,ds.* from employee as d " +
                        " cross apply(select top 1 * from Attendance  where ecode = d.ecode and CAST(attdate AS DATE) = CAST('" + json_object["date"] + "' AS DATE) " +
                        " order by idno desc) as ds";
                data = PayRoll_IList_GetTable(query);
            }



            return data;
        }

        int PayRoll_IList_Lastid(String query)
        {
            int IsInsert = 0;
            try
            {
                using (con = new SqlConnection(payroll_cs))
                {
                    con.Open();
                    cm = new SqlCommand(query, con);
                    var val = cm.ExecuteScalar();
                    IsInsert = (val == null) ? 1 : (Convert.ToInt32(val));
                }
            }
            catch (SqlException ex)
            {

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

        int PayRoll_IList_execute(String query)
        {
            int IsInsert = 0;
            try
            {
                using (con = new SqlConnection(payroll_cs))
                {
                    con.Open();
                    cm = new SqlCommand(query, con);
                    IsInsert = Convert.ToInt32(cm.ExecuteNonQuery());
                }
            }
            catch (SqlException ex)
            {

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

        int PayRoll_IList_ExecuteScalerValue(String query)
        {
            int IsInsert = 0;
            try
            {
                using (con = new SqlConnection(payroll_cs))
                {
                    con.Open();
                    cm = new SqlCommand(query, con);
                    IsInsert = Convert.ToInt32(cm.ExecuteScalar());
                }
            }
            catch (SqlException ex)
            {

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

        int PayRoll_IList_bulkexecute(SqlCommand cm, String query)
        {
            int IsInsert = 0;
            try
            {
                using (con = new SqlConnection(payroll_cs))
                {
                    con.Open();
                    cm = new SqlCommand(query, con);
                    IsInsert = Convert.ToInt32(cm.ExecuteNonQuery());
                }
            }
            catch (SqlException ex)
            {

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

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


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

                    }

                }

                JSONString = JsonConvert.SerializeObject(dt);


            }
            catch (SqlException ex)
            {

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

            return JSONString;

        }

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


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

                    }

                }

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


            }
            catch (SqlException ex)
            {

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

            return JSONString;

        }

    }
}