using LHDN.CRM.Models.Models;
using LHDN.CRM.Models.ViewModels;
using System.Collections.Generic;
using System.Linq;
using System;
using System.Data;
using System.Reflection;
using System.Web;
using LHDN.CRM.BC.Common;
using System.Text.RegularExpressions;

namespace LHDN.CRM.BC.Repositories
{
    public class TaxPayerRepository : BaseRepository<CRMContext, TaxPayer>
    {
        public List<TaxPayer> GetAllTaxPayers()
        {
            return ExecuteSP<TaxPayer>("proc_GetAllTaxPayers").ToList();
        }

        public TaxPayerViewModel GetTaxPayer(long TPID)
        {
            TaxPayerViewModel taxpayerDetail = new TaxPayerViewModel();
            CountCaseOutboundHeaderViewModel countdetail = new CountCaseOutboundHeaderViewModel();
            taxpayerDetail = ExecuteSP<TaxPayerViewModel>("proc_GetTaxPayer", "@TPID", TPID).FirstOrDefault();
            if (taxpayerDetail != null)
            {
                taxpayerDetail.SpouseDetails = GetAllSpouseDetail(taxpayerDetail.TPID);
                taxpayerDetail.PemilikanSyarikat = GetTPPemilikanSyarikat(taxpayerDetail.TPID);
                taxpayerDetail.SktPerjalanan75A = GetTaxPayerSekatanPjn75A(taxpayerDetail.TPID);
                taxpayerDetail.ShareholderDetail = GetTaxPayerShareHolderDetail(taxpayerDetail.TPID);
                taxpayerDetail.CukaiMajikanDetail = GetTPMajikanCukaiByID(taxpayerDetail.TPID);
                taxpayerDetail.EjenCukaiDetail = GetTaxPayerAgent(taxpayerDetail.TPID);
                //taxpayerDetail.FailPenduaDetail = GetTaxPayerFailPenduaDetail(taxpayerDetail.TPID);
                //taxpayerDetail.StatusBayaranBalik = GetTPGetTPStatusBayarBalik(taxpayerDetail.TPID);

                //Count for Total Case and Outbound
                countdetail = GetHeaderCountDetailByID(taxpayerDetail.TPID);
                taxpayerDetail.TotalCase = countdetail.TotalCase;
                taxpayerDetail.TotalOutbound = countdetail.TotalOutbound;
                taxpayerDetail.CaseBlmSelesai = countdetail.CaseBlmSelesai;
                taxpayerDetail.OutboundBlmSelesai = countdetail.OutboundBlmSelesai;
                taxpayerDetail.ActBlmSelesai = countdetail.ActBlmSelesai;
            }

            return taxpayerDetail;

        }

        public List<TPSekatanPerjalanan75AModel> GetTaxPayerSekatanPjn75A(long TPID)
        {
            List<TPSekatanPerjalanan75AModel> SktPerjalanan75AInfo = new List<TPSekatanPerjalanan75AModel>();
            DataSet ds = ExecuteSP("proc_GetTPSekatanPerjalanan75A", "@TPID", TPID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    SktPerjalanan75AInfo = DataTableToList<TPSekatanPerjalanan75AModel>(ds.Tables[0]).ToList();
                }
            }
            return SktPerjalanan75AInfo;
        }

        public List<TPShareHolderViewModel> GetTaxPayerShareHolderDetail(long TPID)
        {
            List<TPShareHolderViewModel> ShareholderInfo = new List<TPShareHolderViewModel>();
            DataSet ds = ExecuteSP("proc_GetTPShareHolderDetail", "@TPID", TPID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    ShareholderInfo = DataTableToList<TPShareHolderViewModel>(ds.Tables[0]).ToList();
                }
            }
            return ShareholderInfo;
        }

        public TPForActivityViewModel GetTPForActivity(long TPID)
        {

            TPForActivityViewModel clsTP = null;
            DataSet ds = ExecuteSP("proc_GetTPForActivity", "@TPID", TPID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    clsTP = DataTableToList<TPForActivityViewModel>(ds.Tables[0]).FirstOrDefault();
                }
            }

            return clsTP;
        }

        public int SaveBoundDetail(CorrespondenceUpdateViewModel model, string strUserName, string strUserID, DateTime dtUpdated)
        {
            DataTable dTbl = model.ToDataTable<CorrespondenceUpdateViewModel>();
            dTbl.Columns.Remove("ChangeStamp");
            int i = ExecuteUpdateSP("proc_UpdateCorrespondenceInOutBoundDetail",
                                    "@tblCorrespond", dTbl,
                                    "@LastUpdatedBy", strUserID,
                                    "@LastUpdatedByName", strUserName,
                                    "@LastUpdatedDateTime", dtUpdated);
            return i;

        }

        public void UpdateTPCorrespondence(ActivityUpdateViewModel model, string strAliran, string strUserName, string strUserID, DateTime dtUpdated)
        {

            //TaxPayer tp = this.GetByID(model.Activity.TPID);
            UpdateTPCorrespondenceActViewModel tp = new UpdateTPCorrespondenceActViewModel();
            tp = ExecuteSP<UpdateTPCorrespondenceActViewModel>("proc_GetTaxPayer", "@TPID", model.Activity.TPID).FirstOrDefault();

            if (tp != null)
            {
                tp.IsPelangganPembayarSama = model.Activity.IsPelangganPembayarSama;
                tp.FBID = model.Activity.TPFBID;
                tp.FBDisplayName = model.Activity.TPFBDisplayName;
                tp.TWID = model.Activity.TPTWID;
                tp.TWDisplayName = model.Activity.TPTWDisplayName;
                tp.LanguageID = model.Activity.IsPelangganPembayarSama == true ? model.Activity.LanguageID : tp.LanguageID;
                tp.LastUpdatedBy = strUserID;
                tp.LastUpdatedByName = strUserName;
                tp.LastUpdatedDateTime = dtUpdated;
                if (strAliran == Constant.Aliran.Outbound)
                {
                    if ((string.IsNullOrEmpty(tp.OutTelefonPejabat) ? string.Empty : tp.OutTelefonPejabat) != model.Activity.TPTelefonPejabat)
                    {
                        tp.OutTelefonPejabat = model.Activity.TPTelefonPejabat;
                        tp.OutUpdateTPDate = dtUpdated;
                    }

                    if ((string.IsNullOrEmpty(tp.OutTelefonBimbit) ? string.Empty : tp.OutTelefonBimbit) != model.Activity.TPTelefonMB)
                    {
                        tp.OutTelefonBimbit = model.Activity.TPTelefonMB;
                        tp.OutUpdateTelefonBimbitDate = dtUpdated;
                        tp.OutUpdateTPDate = dtUpdated;
                        tp.IsGenerateFileTP = true;
                    }

                    if ((string.IsNullOrEmpty(tp.OutEmailMB) ? string.Empty : tp.OutEmailMB) != model.Activity.TPEmailMB)
                    {
                        tp.OutEmailMB = model.Activity.TPEmailMB;
                        tp.OutUpdateEmailMBDate = dtUpdated;
                        tp.OutUpdateTPDate = dtUpdated;
                        tp.IsGenerateFileTP = true;
                    }

                }
                else
                {
                    if ((string.IsNullOrEmpty(tp.InTelefonMB) ? string.Empty : tp.InTelefonMB) != model.Activity.TPTelefonMB)
                    {
                        tp.InTelefonMB = model.Activity.TPTelefonMB;
                        tp.InUpdateTelefonMBDate = dtUpdated;
                        tp.IsGenerateFileTP = true;
                    }

                    if ((string.IsNullOrEmpty(tp.InEmailMB) ? string.Empty : tp.InEmailMB) != model.Activity.TPEmailMB)
                    {
                        tp.InEmailMB = model.Activity.TPEmailMB;
                        tp.InUpdateEmailMBDate = dtUpdated;
                        tp.IsGenerateFileTP = true;
                    }
                }

                DataTable dTbl = tp.ToDataTable<UpdateTPCorrespondenceActViewModel>();
                int i = ExecuteUpdateSP("proc_UpdateTaxpayer",
                                        "@tbl", dTbl);


                //this.Edit(tp);
                //this.Save();
            }
        }
        public List<TPEjenCukaiViewModel> GetTaxPayerAgent(long TPID)
        {
            List<TPEjenCukaiViewModel> EjenCukaiInfo = new List<TPEjenCukaiViewModel>();
            DataSet ds = ExecuteSP("proc_GetTaxPayerAgent", "@TPID", TPID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    EjenCukaiInfo = DataTableToList<TPEjenCukaiViewModel>(ds.Tables[0]).ToList();
                }
            }
            return EjenCukaiInfo;
        }

        public DataSet GetChangesHistory(string strSortExpr, int intPageIndex, int intPageSize, long TPID, long ActivityID, long CaseID, long ReMSDataID, int JenisKempenID, long EmailDataID
                                        , string strFieldName, string strLastUpdatedBy, DateTime? DateFrom, DateTime? DateTo)
        {
            Regex r = new Regex("^[a-zA-Z]+$");
            if (strLastUpdatedBy !=null)
            {
                if(!r.IsMatch(strLastUpdatedBy))
                {
                    strLastUpdatedBy = string.Empty;
                }
            }


            //if(ReMSDataID > 0)
            //{
            //    if (!intCheck.IsMatch(ReMSDataID.ToString()))
            //    {
            //        ReMSDataID = 0;
            //    }
            //}


            Regex intCheck = new Regex("^[0-9]+$");
            if (!string.IsNullOrEmpty(ReMSDataID.ToString()))
            {
                if (!intCheck.IsMatch(ReMSDataID.ToString()))
                {
                    ReMSDataID = 0;
                }
            }

            //if (!string.IsNullOrEmpty(ReMSDataID.ToString()))
            //{
            //    if (!intCheck.IsMatch(ReMSDataID.ToString()))
            //    {
            //        ReMSDataID = 0;
            //    }
            //}

            //if (JenisKempenID > 0)
            //{
            //    if (!intCheck.IsMatch(JenisKempenID.ToString()))
            //    {
            //        JenisKempenID = 0;
            //    }
            //}

            if (!string.IsNullOrEmpty(JenisKempenID.ToString()))
            {
                if (!intCheck.IsMatch(JenisKempenID.ToString()))
                {
                    JenisKempenID = 0;
                }
            }

            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", intPageIndex,
                                        "@PageSize", intPageSize,
                                        "@TPID", TPID,
                                        "@ActivityID", ActivityID,
                                        "@CaseID", CaseID,
                                        "@ReMSDataID", ReMSDataID,
                                        "@JenisKempenID",JenisKempenID,
                                        "@EmailDataID",EmailDataID,
                                        "@FieldName", strFieldName,
                                        "@LastUpdatedBy", strLastUpdatedBy,
                                        "@DateFrom", DateFrom,
                                        "@DateTo", DateTo

                                    });

            if (!string.IsNullOrEmpty(strSortExpr))
            {
                lstParam.AddRange(new object[] {
                "@SortExpression", strSortExpr
                });
            }

            return ExecuteSP("proc_GetChangeHistory", lstParam.ToArray());
        }

        public List<TPFailPenduaViewModel> GetTaxPayerFailPenduaDetail(long TPID)
        {
            List<TPFailPenduaViewModel> FailPenduaInfo = new List<TPFailPenduaViewModel>();
            DataSet ds = ExecuteSP("proc_GetTPFailPenduaDetail", "@TPID", TPID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    FailPenduaInfo = DataTableToList<TPFailPenduaViewModel>(ds.Tables[0]).ToList();
                }
            }
            return FailPenduaInfo;

            //return ExecuteSP<TPFailPenduaViewModel>("proc_GetTPFailPenduaDetail", "@TPID", TPID).ToList();
        }

        public CountCaseOutboundHeaderViewModel GetHeaderCountDetailByID(long TPID)
        {
            CountCaseOutboundHeaderViewModel countdetail = new CountCaseOutboundHeaderViewModel();
            DataSet ds = ExecuteSP("proc_GetCountForCaseAndOutbound", "@TPID", TPID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    countdetail = DataTableToList<CountCaseOutboundHeaderViewModel>(ds.Tables[0]).FirstOrDefault();
                }

                if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
                {
                    countdetail.TotalOutbound = Convert.ToInt32(ds.Tables[1].Rows[0][0].ToString()); ;
                }

                if (ds.Tables[2] != null && ds.Tables[2].Rows.Count > 0)
                {
                    countdetail.OutboundBlmSelesai = Convert.ToInt32(ds.Tables[2].Rows[0][0].ToString()); ;
                }
            }

            return countdetail;
        }

        public List<TPCukaiMajikanViewModel> GetTPMajikanCukaiByID(long lngTPID)
        {
            List<TPCukaiMajikanViewModel> CukaiMajikanInfo = new List<TPCukaiMajikanViewModel>();
            DataSet ds = ExecuteSP("proc_GetTPMajikanCukai", "@TPID", lngTPID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    CukaiMajikanInfo = DataTableToList<TPCukaiMajikanViewModel>(ds.Tables[0]).ToList();
                }
            }
            return CukaiMajikanInfo;
        }

        public List<TPCukaiMajikanViewModel> GetTPActiveMajikanByID(long lngTPID)
        {
            List<TPCukaiMajikanViewModel> CukaiMajikanInfo = new List<TPCukaiMajikanViewModel>();
            DataSet ds = ExecuteSP("proc_GetTPActiveMajikan", "@TPID", lngTPID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    CukaiMajikanInfo = DataTableToList<TPCukaiMajikanViewModel>(ds.Tables[0]).ToList();
                }
            }
            return CukaiMajikanInfo;
        }


        public List<TPForActivityViewModel> SearchTaxPayer(int? intIDType, string strIDNo, string strFBID, string strTWID, string strNoCukai)
        {
            List<TPForActivityViewModel> lstTP = null;

            DataSet ds = ExecuteSP("proc_SearchTaxPayer", "@IDTypeID", intIDType,
                                                "@IDNo", strIDNo,
                                                "@FBNo", strFBID,
                                                "@TWNo", strTWID,
                                                "@NoCukai", strNoCukai);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    lstTP = DataTableToList<TPForActivityViewModel>(ds.Tables[0]).ToList();

                    if (lstTP != null)
                    {

                        foreach (var item in lstTP)
                        {

                            item.TPEjenCukais = GetTaxPayerAgent(item.TPID);

                            item.TPSpouseList = GetAllSpouseDetail(item.TPID);

                            item.TPShareholder = GetTaxPayerShareHolderDetail(item.TPID);

                            item.TPMajikanList = GetTPActiveMajikanByID(item.TPID);
                        }
                    }
                }
            }

            return lstTP;
        }


        /// <summary>
        /// Insert into change history record 
        /// @tableType value should be 1 - TaxPayer, 2 - Case, 3 - Activity
        /// </summary>
        /// <param name="model">Change history view model</param>
        /// <param name="strUserName"></param>
        /// <param name="strUserID"></param>
        /// <param name="dtUpdated"></param>
        /// <returns></returns>
        public int InsertChangeHistory(List<ChangeHistoryUpdateViewModel> lst, string strUserName, string strUserID, DateTime dtUpdated)
        {
            DataTable dTbl = lst.ToDataTable<ChangeHistoryUpdateViewModel>();
            dTbl.Columns.Remove("IsChanged");
            //dTbl.Columns.Remove("RefID");

            int i = ExecuteUpdateSP("proc_InsertChangeHistory",
                                    "@tbl", dTbl,
                                    "@TableType", (int)Constant.TableType.TaxPayer,
                                    "@CreatedByName", strUserName,
                                    "@CreatedBy", strUserID,
                                    "@CreatedDateTime", dtUpdated);
            return i;

        }

        public List<TPPemilikanSyarikatViewModel> GetTPPemilikanSyarikat(long TPID)
        {
            List<TPPemilikanSyarikatViewModel> PemilikanSyarikatInfo = new List<TPPemilikanSyarikatViewModel>();
            DataSet ds = ExecuteSP("proc_GetTPPemilikanSyarikat", "@TPID", TPID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    PemilikanSyarikatInfo = DataTableToList<TPPemilikanSyarikatViewModel>(ds.Tables[0]).ToList();
                }
            }
            return PemilikanSyarikatInfo;
        }

        public List<TPSpouseViewModel> GetAllSpouseDetail(long TPID)
        {
            List<TPSpouseViewModel> SpouseInfo = new List<TPSpouseViewModel>();
            DataSet ds = ExecuteSP("proc_GetTPSpouseDetail", "@TPID", TPID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    SpouseInfo = DataTableToList<TPSpouseViewModel>(ds.Tables[0]).ToList();
                }
            }
            return SpouseInfo;
        }

        public DataSet GetOuboundList(OutboundTabPageListViewModel paging)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", paging.PageIndex,
                                        "@PageSize", paging.Length,
                                        "@TPID", paging.TPID,
                                        "@JenisKempenID",paging.JenisKempenID,
                                        "@JenisCampaignName",paging.CampaignName,
                                        "@Tahun",paging.TahunTaksiran,
                                        "@JenisCukai",paging.JenisCukai,
                                        "@StatusID",paging.OutboundStatusID});

            if (!string.IsNullOrEmpty(paging.SortExpression))
            {
                lstParam.AddRange(new object[] {
                "@SortExpression", paging.SortExpression
                });
            }

            return ExecuteSP("proc_GetOutboundListByID", lstParam.ToArray());
        }

        public List<TPViewModel> GetAllTPForGenerateTPFile()
        {
            return ExecuteSP<TPViewModel>("proc_GetAllTPForGenerateTPFile").ToList();

        }
        public void UpdateGenerateTPFileFlag(List<long> lstTPID)
        {
            DataTable dtblTPID = new DataTable();
            dtblTPID.Columns.Add("ID");

            foreach (long lngID in lstTPID)
            {
                dtblTPID.Rows.Add(lngID);
            }

            ExecuteSP("proc_UpdateGenerateTPFileFlag", "@tblTPID", dtblTPID);
        }

        public TPAdditionalDataViewModel GetAdditionalDataByTP(TaxPayerParamViewModel model)
        {
            TPAdditionalDataViewModel vm = null;
            DataSet ds = ExecuteSP("proc_GetTPAdditionalDetails", "@NoCukai", model.NoCukaiSTS, "@NoCukaiCode", model.NoCukaiCode, "@FileTypeID", model.FileTypeID);

            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    vm = DataTableToList<TPAdditionalDataViewModel>(ds.Tables[0]).FirstOrDefault();

                    if (model.FileTypeID == (int)Constant.TPFileTypeID.SG)
                    {
                        vm.MyIdentity = ExecuteSP<TPMyIdentityViewModel>("proc_GetTPMyIdentityData", "@NoCukai", model.NoCukaiSTS, "@NoCukaiCode", model.NoCukaiCode, "@FileTypeID", model.FileTypeID).FirstOrDefault();
                        vm.PerniagaanStatusFail = ExecuteSP<TPBusinessFileViewModel>("proc_GetTPBusinessDetails", "@NoCukai", model.NoCukaiSTS, "@NoCukaiCode", model.NoCukaiCode, "@FileTypeID", model.FileTypeID).FirstOrDefault();
                        vm.FailPenduaList = ExecuteSP<TPFailPenduaViewModel>("proc_GetTPFailPenduaDetail", "@NoCukai", model.NoCukaiSTS, "@NoCukaiCode", model.NoCukaiCode, "@FileTypeID", model.FileTypeID).ToList();
                    }

                    if (model.FileTypeID == (int)Constant.TPFileTypeID.FA)
                    {
                        vm.PerniagaanStatusFail = ExecuteSP<TPBusinessFileViewModel>("proc_GetTPBusinessDetails", "@NoCukai", model.NoCukaiSTS, "@NoCukaiCode", model.NoCukaiCode, "@FileTypeID", model.FileTypeID).FirstOrDefault();
                        vm.PenganjurList = ExecuteSP<TPPenganjurViewModel>("proc_GetTPPenganjurDetails", "@NoCukai", model.NoCukaiSTS, "@NoCukaiCode", model.NoCukaiCode, "@FileTypeID", model.FileTypeID).ToList();
                        vm.FailPenduaList = ExecuteSP<TPFailPenduaViewModel>("proc_GetTPFailPenduaDetail", "@NoCukai", model.NoCukaiSTS, "@NoCukaiCode", model.NoCukaiCode, "@FileTypeID", model.FileTypeID).ToList();
                    }

                    if (model.FileTypeID == (int)Constant.TPFileTypeID.D || model.FileTypeID == (int)Constant.TPFileTypeID.TP)
                    {
                        vm.PerniagaanStatusFail = ExecuteSP<TPBusinessFileViewModel>("proc_GetTPBusinessDetails", "@NoCukai", model.NoCukaiSTS, "@NoCukaiCode", model.NoCukaiCode, "@FileTypeID", model.FileTypeID).FirstOrDefault();
                        vm.AlamatPerniagaan = ExecuteSP<TPAddressViewModel>("proc_GetTPBusinessAddress", "@NoCukai", model.NoCukaiSTS, "@NoCukaiCode", model.NoCukaiCode, "@FileTypeID", model.FileTypeID).FirstOrDefault();
                        //vm.AhliKongsiList = DataTableToList<TPAssociatePersonViewModel>(ds.Tables[3]).ToList();
                        vm.FailPenduaList = ExecuteSP<TPFailPenduaViewModel>("proc_GetTPFailPenduaDetail", "@NoCukai", model.NoCukaiSTS, "@NoCukaiCode", model.NoCukaiCode, "@FileTypeID", model.FileTypeID).ToList();
                    }

                    if (model.FileTypeID == (int)Constant.TPFileTypeID.C)
                    {
                        vm.PerniagaanStatusFail = ExecuteSP<TPBusinessFileViewModel>("proc_GetTPBusinessDetails", "@NoCukai", model.NoCukaiSTS, "@NoCukaiCode", model.NoCukaiCode, "@FileTypeID", model.FileTypeID).FirstOrDefault();
                        vm.AlamatPerniagaan = ExecuteSP<TPAddressViewModel>("proc_GetTPBusinessAddress", "@NoCukai", model.NoCukaiSTS, "@NoCukaiCode", model.NoCukaiCode, "@FileTypeID", model.FileTypeID).FirstOrDefault();
                    }

                    if (model.FileTypeID == (int)Constant.TPFileTypeID.E)
                    {
                        vm.PerniagaanStatusFail = ExecuteSP<TPBusinessFileViewModel>("proc_GetTPBusinessDetails", "@NoCukai", model.NoCukaiSTS, "@NoCukaiCode", model.NoCukaiCode, "@FileTypeID", model.FileTypeID, "@EmplFileTypeID", model.EmplFileTypeID, "@EmplNoCukaiSTS", model.EmplNoCukaiSTS).FirstOrDefault();
                        vm.AlamatPerniagaan = ExecuteSP<TPAddressViewModel>("proc_GetTPBusinessAddress", "@NoCukai", model.NoCukaiSTS, "@NoCukaiCode", model.NoCukaiCode, "@FileTypeID", model.FileTypeID, "@EmplFileTypeID", model.EmplFileTypeID, "@EmplNoCukaiSTS", model.EmplNoCukaiSTS).FirstOrDefault();
                        vm.FailPenduaList = ExecuteSP<TPFailPenduaViewModel>("proc_GetTPFailPenduaDetail", "@NoCukai", model.NoCukaiSTS, "@NoCukaiCode", model.NoCukaiCode, "@FileTypeID", model.FileTypeID).ToList();
                    }

                }
            }
            return vm;
        }

        public List<TPRingkasanByrBalikViewModel> GetRingkasanBayaranBalikTP(TaxPayerParamViewModel model)
        {
            List<TPRingkasanByrBalikViewModel> lst = null;
            DataSet ds = ExecuteSP("proc_GetTPRingkasanBayaranBalik", "@NoCukai", model.NoCukaiSTS, "@NoCukaiCode", model.NoCukaiCode);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    lst = DataTableToList<TPRingkasanByrBalikViewModel>(ds.Tables[0]).ToList();
                }
            }
            return lst;
        }

        public List<TPLejerViewModel> GetLejerDataByTP(TaxPayerParamViewModel model)
        {
            List<TPLejerViewModel> lst = null;
            DataSet ds = ExecuteSP("proc_GetTPLejerData", "@NoCukai", model.NoCukaiSTS, "@NoCukaiCode", model.NoCukaiCode);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    lst = DataTableToList<TPLejerViewModel>(ds.Tables[0]).ToList();
                }
            }
            return lst;
        }

        public List<TPLejerViewModel> GetRingkasanLejerByTP(TaxPayerParamViewModel model)
        {
            List<TPLejerViewModel> lst = null;
            DataSet ds = ExecuteSP("proc_GetTPRingksanLejer", "@NoCukai", model.NoCukaiSTS, "@NoCukaiCode", model.NoCukaiCode);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    lst = DataTableToList<TPLejerViewModel>(ds.Tables[0]).ToList();
                }
            }
            return lst;
        }

        public List<TPKawalTerimaBorangViewModel> GetKawalBorangDataByTP(TaxPayerParamViewModel model)
        {
            List<TPKawalTerimaBorangViewModel> lst = null;
            DataSet ds = ExecuteSP("proc_GetTPKawalTerimaBorang", "@NoCukai", model.NoCukaiSTS, "@NoCukaiCode", model.NoCukaiCode);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    lst = DataTableToList<TPKawalTerimaBorangViewModel>(ds.Tables[0]).ToList();
                }
            }
            return lst;
        }

        public List<TPAnsuranViewModel> GetAllAnsuranByTP(TaxPayerParamViewModel model)
        {
            List<TPAnsuranViewModel> lst = null;
            DataSet ds = ExecuteSP("proc_GetTPAnsuran", "@NoCukai", model.NoCukaiSTS, "@NoCukaiCode", model.NoCukaiCode);
            if (ds != null)
            {
                if (ds.Tables[0] != null)
                {
                    lst = DataTableToList<TPAnsuranViewModel>(ds.Tables[0]).ToList();
                }
            }
            return lst;
        }


        public List<TPAnggaranViewModel> GetAllAnggaranByTP(TaxPayerParamViewModel model)
        {
            List<TPAnggaranViewModel> lst = null;
            if (model.FileTypeID == (int)Constant.TPFileTypeID.C)
            {
                DataSet ds = ExecuteSP("proc_GetTPAnggaranListForCompany", "@NoCukai", model.NoCukaiSTS, "@NoCukaiCode", model.NoCukaiCode);
                if (ds != null)
                {
                    if (ds.Tables[0] != null)
                    {
                        lst = DataTableToList<TPAnggaranViewModel>(ds.Tables[0]).ToList();
                    }
                }
            }
            return lst;
        }

        public List<TPRingkasanTaksiranViewModel> GetRingkasanTaksiranByTP(TaxPayerParamViewModel model)
        {
            List<TPRingkasanTaksiranViewModel> lst = null;
            DataSet ds = ExecuteSP("proc_GetTPRingkasanTaksiran", "@NoCukai", model.NoCukaiSTS, "@NoCukaiCode", model.NoCukaiCode);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    lst = DataTableToList<TPRingkasanTaksiranViewModel>(ds.Tables[0]).ToList();
                }
            }
            return lst;
        }


        public List<TPStatusBayaranBalikViewModel> GetStatusBayaranBalikbyTP(TaxPayerParamViewModel model)
        {
            List<TPStatusBayaranBalikViewModel> lst = null;
            DataSet ds = ExecuteSP("proc_GetTPStatusBayaranBalik", "@NoCukai", model.NoCukai, "@NoCukaiCode", model.NoCukaiCode);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    lst = DataTableToList<TPStatusBayaranBalikViewModel>(ds.Tables[0]).ToList();
                }
            }
            return lst;
        }


        public TPPungutanCPViewModel GetPungutanCPbyTPForNC(string strNoCukai)
        {
            TPPungutanCPViewModel vm = new TPPungutanCPViewModel();
            DataSet ds = ExecuteSP("proc_GetTPPungutanCPForNC", "@NoCukai", strNoCukai);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    vm.AnsuranArahanPotonganGaji = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[0]).FirstOrDefault();
                }

                if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
                {
                    vm.AnsuranSendiri = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[1]).FirstOrDefault();
                }

                if (ds.Tables[2] != null && ds.Tables[2].Rows.Count > 0)
                {
                    vm.AnsuranAudit = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[2]).FirstOrDefault();
                }

                if (ds.Tables[3] != null && ds.Tables[3].Rows.Count > 0)
                {
                    vm.AnsuranPenyiasatan = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[3]).FirstOrDefault();
                }

                if (ds.Tables[4] != null && ds.Tables[4].Rows.Count > 0)
                {
                    vm.AnsuranGuamanSivil = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[4]).FirstOrDefault();
                }

                if (ds.Tables[5] != null && ds.Tables[5].Rows.Count > 0)
                {
                    vm.AnsuranKhas = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[5]).FirstOrDefault();
                }

                if (ds.Tables[6] != null && ds.Tables[6].Rows.Count > 0)
                {
                    vm.SuratPenyelesainCukai = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[6]).FirstOrDefault();
                }
                if (ds.Tables[7] != null && ds.Tables[7].Rows.Count > 0)
                {
                    vm.Sekatan104 = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[7]).FirstOrDefault();
                }
                if (ds.Tables[8] != null && ds.Tables[8].Rows.Count > 0)
                {
                    vm.GuamanSivil = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[8]).FirstOrDefault();
                }
                if (ds.Tables[9] != null && ds.Tables[9].Rows.Count > 0)
                {
                    vm.Kaveat = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[9]).FirstOrDefault();
                }
                if (ds.Tables[10] != null && ds.Tables[10].Rows.Count > 0)
                {
                    vm.HapuskiraCadangan = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[10]).FirstOrDefault();
                }
                if (ds.Tables[11] != null && ds.Tables[11].Rows.Count > 0)
                {
                    vm.HapuskiraPerakuan = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[11]).FirstOrDefault();
                }
                if (ds.Tables[12] != null && ds.Tables[12].Rows.Count > 0)
                {
                    vm.CukaiKebelakangan = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[12]).FirstOrDefault();
                }
                if (ds.Tables[13] != null && ds.Tables[13].Rows.Count > 0)
                {
                    vm.KedudukanCukai = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[13]).FirstOrDefault();
                }
            }
            return vm;
        }

        public TPPungutanCPViewModel GetPungutanCPbyTPForCompany(string strNoCukai)
        {
            TPPungutanCPViewModel vm = new TPPungutanCPViewModel();
            DataSet ds = ExecuteSP("proc_GetTPPungutanCPForCompany", "@NoCukai", strNoCukai);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    vm.AnsuranPemungutan = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[0]).FirstOrDefault();
                }

                if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
                {
                    vm.AnsuranAudit = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[1]).FirstOrDefault();
                }

                if (ds.Tables[2] != null && ds.Tables[2].Rows.Count > 0)
                {
                    vm.AnsuranPenyiasatan = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[2]).FirstOrDefault();
                }

                if (ds.Tables[3] != null && ds.Tables[3].Rows.Count > 0)
                {
                    vm.AnsuranGuamanSivil = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[3]).FirstOrDefault();
                }

                if (ds.Tables[4] != null && ds.Tables[4].Rows.Count > 0)
                {
                    vm.GuamanSivil = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[4]).FirstOrDefault();
                }
                if (ds.Tables[5] != null && ds.Tables[5].Rows.Count > 0)
                {
                    vm.HapuskiraCadangan = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[5]).FirstOrDefault();
                }
                if (ds.Tables[6] != null && ds.Tables[6].Rows.Count > 0)
                {
                    vm.HapuskiraPerakuan = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[6]).FirstOrDefault();
                }
                if (ds.Tables[7] != null && ds.Tables[7].Rows.Count > 0)
                {
                    vm.Kaveat = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[7]).FirstOrDefault();
                }
                if (ds.Tables[8] != null && ds.Tables[8].Rows.Count > 0)
                {
                    vm.KedudukanCukai = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[8]).FirstOrDefault();
                }
                if (ds.Tables[9] != null && ds.Tables[9].Rows.Count > 0)
                {
                    vm.PermohonanSetOff = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[9]).FirstOrDefault();
                }
                if (ds.Tables[10] != null && ds.Tables[10].Rows.Count > 0)
                {
                    vm.Sekatan75A = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[10]).FirstOrDefault();
                }
                if (ds.Tables[11] != null && ds.Tables[11].Rows.Count > 0)
                {
                    vm.CukaiKebelakangan = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[11]).FirstOrDefault();
                }
                if (ds.Tables[12] != null && ds.Tables[12].Rows.Count > 0)
                {
                    vm.RayuanPengemukaan = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[12]).FirstOrDefault();
                }
            }
            return vm;
        }

        public TPPungutanCKHTViewModel GetPungutanCKHTbyTPForNC(string strNoCukai)
        {
            TPPungutanCKHTViewModel vm = new TPPungutanCKHTViewModel();
            DataSet ds = ExecuteSP("proc_GetTPPungutanCKHTForNC", "@NoCukai", strNoCukai);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    vm.AnsuranCKHT = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[0]).FirstOrDefault();
                }

                if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
                {
                    vm.AnsuranGuamanSivil = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[1]).FirstOrDefault();
                }

                if (ds.Tables[2] != null && ds.Tables[2].Rows.Count > 0)
                {
                    vm.Sekatan22 = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[2]).FirstOrDefault();
                }

                if (ds.Tables[3] != null && ds.Tables[3].Rows.Count > 0)
                {
                    vm.HapuskiraCadangan = DataTableToList<TPPungutanUnitViewModel>(ds.Tables[3]).FirstOrDefault();
                }


            }
            return vm;
        }

        public List<LanguageResponseViewModel> GetLanguageRequest(LanguageRequestViewModel LanguageRequest)
        {
            List<LanguageResponseViewModel> LanguageVM = new List<LanguageResponseViewModel>();
            DataSet dsGetLanguageRequest = new DataSet();
            dsGetLanguageRequest = ExecuteSP("proc_CCGetLanguageRequest",
                "@phoneNo", LanguageRequest.PhoneNo);

            if (dsGetLanguageRequest != null)
            {
                if (dsGetLanguageRequest.Tables[0] != null && dsGetLanguageRequest.Tables[0].Rows.Count > 0)
                {
                    LanguageVM = DataTableToList<LanguageResponseViewModel>(dsGetLanguageRequest.Tables[0]).ToList();
                }
            }

            return LanguageVM;
        }

        public List<VerifyResponseViewModel> GetVerifyRequest(VerifyRequestViewModel VerifyRequest)
        {
            List<VerifyResponseViewModel> VerifyVM = new List<VerifyResponseViewModel>();
            DataSet dsGetVerifyRequest = new DataSet();
            dsGetVerifyRequest = ExecuteSP("proc_CCGetVerifyRequest",
                "@IDNo", VerifyRequest.IDNo,
                "@IDType", VerifyRequest.IDType,
                "@SocialName", VerifyRequest.SocialName
                );

            if (dsGetVerifyRequest != null)
            {
                if (dsGetVerifyRequest.Tables[0] != null && dsGetVerifyRequest.Tables[0].Rows.Count > 0)
                {
                    VerifyVM = DataTableToList<VerifyResponseViewModel>(dsGetVerifyRequest.Tables[0]).ToList();
                    foreach (var item in VerifyVM)
                    {
                        item.Authenticated = true;
                        item.Email = dsGetVerifyRequest.Tables[0].Rows[0]["Email"].ToString();
                        item.FacebookID = dsGetVerifyRequest.Tables[0].Rows[0]["FBID"].ToString();
                        item.IDNo = dsGetVerifyRequest.Tables[0].Rows[0]["IdentityNo"] is DBNull ? "" : dsGetVerifyRequest.Tables[0].Rows[0]["IdentityNo"].ToString();
                        item.IDType = dsGetVerifyRequest.Tables[0].Rows[0]["IDTypeID"] is DBNull ? 0 : Int32.Parse(dsGetVerifyRequest.Tables[0].Rows[0]["IDTypeID"].ToString());
                        item.Name = dsGetVerifyRequest.Tables[0].Rows[0]["TPName"].ToString();
                        item.PhoneNo = dsGetVerifyRequest.Tables[0].Rows[0]["TelNo"] is DBNull ? dsGetVerifyRequest.Tables[0].Rows[0]["MobileNo"].ToString() : dsGetVerifyRequest.Tables[0].Rows[0]["TelNo"].ToString();
                        item.TaxNo = dsGetVerifyRequest.Tables[0].Rows[0]["NoRujukanCukai"].ToString();
                        item.TwitterID = dsGetVerifyRequest.Tables[0].Rows[0]["TWID"].ToString();
                    }


                    if ((VerifyVM != null && VerifyVM.Count > 0) && dsGetVerifyRequest.Tables[1] != null && dsGetVerifyRequest.Tables[1].Rows.Count > 0)
                    {
                        VerifyVM[0].VIP = Convert.ToBoolean(dsGetVerifyRequest.Tables[1].Rows[0]["isVIP"]);
                        VerifyVM[0].S104 = Convert.ToBoolean(dsGetVerifyRequest.Tables[1].Rows[0]["isS104"]);
                        VerifyVM[0].S22 = Convert.ToBoolean(dsGetVerifyRequest.Tables[1].Rows[0]["isS22"]);
                        VerifyVM[0].S75A = Convert.ToBoolean(dsGetVerifyRequest.Tables[1].Rows[0]["isS75A"]);
                        VerifyVM[0].DebitList = Convert.ToBoolean(dsGetVerifyRequest.Tables[1].Rows[0]["isDebitList"]);
                    }
                }
            }

            return VerifyVM;
        }

        public BlacklistResponseViewModel GetBlacklistRequest(BlacklistRequestViewModel BlacklistRequest)
        {
            BlacklistResponseViewModel BlackListVM = new BlacklistResponseViewModel();
            DataSet dsGetBlacklistRequest = new DataSet();
            dsGetBlacklistRequest = ExecuteSP("proc_CCGetBlacklistRequest",
                "@IDNo", BlacklistRequest.IDNo,
                "@IDType", BlacklistRequest.IDType
                );

            if (dsGetBlacklistRequest != null)
            {
                if (dsGetBlacklistRequest.Tables[0].Columns.Count == 1) //only return count column if duplicte TPID ; else return all flag
                {
                    BlackListVM = DataTableToList<BlacklistResponseViewModel>(dsGetBlacklistRequest.Tables[0]).FirstOrDefault();
                    BlackListVM.Count = Convert.ToInt32(dsGetBlacklistRequest.Tables[0].Rows[0]["Count"]);
                }
                else
                {
                    if (dsGetBlacklistRequest != null)
                    {
                        if (dsGetBlacklistRequest.Tables[0] != null && dsGetBlacklistRequest.Tables[0].Rows.Count > 0)
                        {
                            BlackListVM = DataTableToList<BlacklistResponseViewModel>(dsGetBlacklistRequest.Tables[0]).FirstOrDefault();

                            BlackListVM.VIP = Convert.ToBoolean(dsGetBlacklistRequest.Tables[0].Rows[0]["isVIP"]);
                            BlackListVM.S104 = Convert.ToBoolean(dsGetBlacklistRequest.Tables[0].Rows[0]["isS104"]);
                            BlackListVM.S22 = Convert.ToBoolean(dsGetBlacklistRequest.Tables[0].Rows[0]["isS22"]);
                            BlackListVM.S75A = Convert.ToBoolean(dsGetBlacklistRequest.Tables[0].Rows[0]["isS75A"]);
                            BlackListVM.DebitList = Convert.ToBoolean(dsGetBlacklistRequest.Tables[0].Rows[0]["isDebitList"]);
                        }
                    }
                }
            }

            return BlackListVM;
        }

        public List<ServiceVerificationResponseViewModel> GetServiceVerificationRequest(ServiceVerificationRequestViewModel ServiceVerificationRequest)
        {
            List<ServiceVerificationResponseViewModel> VerifyVM = new List<ServiceVerificationResponseViewModel>();
            DataSet dsGetServiceVerificationRequest = new DataSet();
            dsGetServiceVerificationRequest = ExecuteSP("proc_CCGetServiceVerificationRequest",
                "@IDNo", ServiceVerificationRequest.IDNo,
                "@IDType", ServiceVerificationRequest.IDType,
                "@DOB", ServiceVerificationRequest.Detailed?.DOB,
                "@Poskod", ServiceVerificationRequest.Detailed?.PostCode,
                "@AccountNo", ServiceVerificationRequest.Detailed?.AccountNo,
                "@PhoneNo", ServiceVerificationRequest.Detailed?.PhoneNo
                );

            if (dsGetServiceVerificationRequest != null)
            {
                if (dsGetServiceVerificationRequest.Tables[0] != null && dsGetServiceVerificationRequest.Tables[0].Rows.Count > 0)
                {
                    VerifyVM = DataTableToList<ServiceVerificationResponseViewModel>(dsGetServiceVerificationRequest.Tables[0]).ToList();
                }
            }

            foreach (var item in VerifyVM)
            {
                item.IDNo = dsGetServiceVerificationRequest.Tables[0].Rows[0]["IdentityNo"] is DBNull ? "" : dsGetServiceVerificationRequest.Tables[0].Rows[0]["IdentityNo"].ToString();
                item.IDType = dsGetServiceVerificationRequest.Tables[0].Rows[0]["IDTypeID"] is DBNull ? 0 : Int32.Parse(dsGetServiceVerificationRequest.Tables[0].Rows[0]["IDTypeID"].ToString());
                item.SocialName = dsGetServiceVerificationRequest.Tables[0].Rows[0]["FBID"] is DBNull ? dsGetServiceVerificationRequest.Tables[0].Rows[0]["TWID"].ToString() : dsGetServiceVerificationRequest.Tables[0].Rows[0]["FBID"].ToString();
                item.DOB = DateTime.Parse(dsGetServiceVerificationRequest.Tables[0].Rows[0]["DOB"].ToString());
                item.PostCode = dsGetServiceVerificationRequest.Tables[0].Rows[0]["Poskod"].ToString();
                item.AccountNo = dsGetServiceVerificationRequest.Tables[0].Rows[0]["AccountNo"].ToString();
                item.PhoneNo = dsGetServiceVerificationRequest.Tables[0].Rows[0]["TelNo"] is DBNull ? dsGetServiceVerificationRequest.Tables[0].Rows[0]["MobileNo"].ToString() : dsGetServiceVerificationRequest.Tables[0].Rows[0]["TelNo"].ToString();
            }

            return VerifyVM;
        }

        public List<HotlineResponseViewModel> GetHotlineRequest(HotlineRequestViewModel HotlineRequest)
        {
            List<HotlineResponseViewModel> HotlineVM = new List<HotlineResponseViewModel>();
            DataSet dsGetHotlineRequest = new DataSet();
            dsGetHotlineRequest = ExecuteSP("proc_CCGetHotlineRequest",
                "@IDNo", HotlineRequest.IDNo,
                "@IDType", HotlineRequest.IDType
                );

            if (dsGetHotlineRequest != null)
            {
                if (dsGetHotlineRequest.Tables[0] != null && dsGetHotlineRequest.Tables[0].Rows.Count > 0)
                {
                    HotlineVM = DataTableToList<HotlineResponseViewModel>(dsGetHotlineRequest.Tables[0]).ToList();
                }
            }

            foreach (var item in HotlineVM)
            {
                item.IDNo = dsGetHotlineRequest.Tables[0].Rows[0]["IdentityNo"] is DBNull ? "" : dsGetHotlineRequest.Tables[0].Rows[0]["IdentityNo"].ToString();
                item.IDType = dsGetHotlineRequest.Tables[0].Rows[0]["IDTypeID"] is DBNull ? 0 : Int32.Parse(dsGetHotlineRequest.Tables[0].Rows[0]["IDTypeID"].ToString());
                item.Name = dsGetHotlineRequest.Tables[0].Rows[0]["TPName"].ToString();
                item.TaxFileNumber = dsGetHotlineRequest.Tables[0].Rows[0]["NoRujukanCukai"].ToString();
                item.LanguageID = dsGetHotlineRequest.Tables[0].Rows[0]["LanguageID"] is DBNull ? 0 : Int32.Parse(dsGetHotlineRequest.Tables[0].Rows[0]["LanguageID"].ToString());
                item.TaxFileLocation = dsGetHotlineRequest.Tables[0].Rows[0]["TaxFileLocation"].ToString();
                item.TaxFileLocationCode = dsGetHotlineRequest.Tables[0].Rows[0]["TaxFileLocationCode"].ToString();
                item.PhoneNo = dsGetHotlineRequest.Tables[0].Rows[0]["TelNo"] is DBNull ? dsGetHotlineRequest.Tables[0].Rows[0]["MobileNo"].ToString() : dsGetHotlineRequest.Tables[0].Rows[0]["TelNo"].ToString();
            }
            return HotlineVM;
        }

        public TaxPayerViewModel GetLanguageID(UpdateLanguageRequestViewModel model)
        {
            TaxPayerViewModel hrv = new TaxPayerViewModel();
            hrv = ExecuteSP<TaxPayerViewModel>("proc_CCGetLanguageID",
                "@IDNo", model.IDNo == null ? "0" : model.IDNo,
                "@IDType", model.IDType,
                 "@PhoneNo", model.PhoneNo == null ? "" : model.PhoneNo.Trim()).FirstOrDefault();
            return hrv;
        }

        public int UpdateLanguageID(UpdateLanguageRequestViewModel model, string strUserName, string strUserID, DateTime dtUpdated)
        {
            int i = ExecuteUpdateSP("proc_CCUpdateLanguageID",
                                    "@LanguageID", model.LanguageID,
                                    "@IDNo", model.IDNo == null ? "0" : model.IDNo,
                                    "@IDType", model.IDType,
                                    "@PhoneNo", model.PhoneNo == null ? "" : model.PhoneNo.Trim(),
                                    "@LastUpdatedBy", strUserID,
                                    "@LastUpdatedByName", strUserName,
                                    "@LastUpdatedDateTime", dtUpdated);
            return i;

        }

        public List<TaxPayerViewModel> GetTP(string strIDType, string strIDNo, string strNoRujukanCukai = "", string strJenisCWMappingColumn = "")
        {
            return ExecuteSP<TaxPayerViewModel>("proc_GetTPByIDTypeIDNo", "@IDType", strIDType,
                                                                          "@IDNo", strIDNo,
                                                                          "@JenisCwMappingColumn", strJenisCWMappingColumn,
                                                                          "@NoRujukanCukai", strNoRujukanCukai).ToList();
        }

        public List<TaxPayerViewModel> GetTPByIDTypeIDCukaiNo(int intIDTypeID, string strIDNo, int intFileTypeID = 0, string strNoCukaiSTS = "")
        {
            return ExecuteSP<TaxPayerViewModel>("proc_GetTPByIDTypeIDCukaiNo", "@IDTypeID", intIDTypeID,
                                                                          "@IDNo", strIDNo,
                                                                          "@FileTypeID", intFileTypeID,
                                                                          "@NoCukaiSTS", strNoCukaiSTS).ToList();
        }

        public List<TaxpayerSTSNoCukaiViewModel> GetSTSValidNoCukaiByIDNumber(int intFileTypeID, int intIDTypeID, string strIDNumber)
        {
            return ExecuteSP<TaxpayerSTSNoCukaiViewModel>("proc_GetSTSNoCukaiByIDTypeIDNo", "@FileTypeID", intFileTypeID,
                                                                           "@IDTypeID", intIDTypeID,
                                                                           "@IDNumber", strIDNumber).ToList();

        }

        public List<TaxpayerSTSNoCukaiViewModel> GetSTSNCValidNoCukaiByTPNoCukai(int intFileTypeID, string strNoCukaiCode, string strNoCukai)
        {
            return ExecuteSP<TaxpayerSTSNoCukaiViewModel>("proc_GetSTSNCNoCukaiByTPNoCukai", "@FileTypeID", intFileTypeID,
                                                                          "@NoCukaiCode", strNoCukaiCode,
                                                                          "@NoCukai", strNoCukai).ToList();


        }

        public List<TaxpayerSTSNoCukaiViewModel> GetSTSCValidNoCukaiByTPNoCukai(int intFileTypeID, string strNoCukaiCode, string strNoCukai)
        {
            return ExecuteSP<TaxpayerSTSNoCukaiViewModel>("proc_GetSTSCNoCukaiByTPNoCukai", "@FileTypeID", intFileTypeID,
                                                                          "@NoCukaiCode", strNoCukaiCode,
                                                                          "@NoCukai", strNoCukai).ToList();

        }

        public void InsertSTSValidNoCukaiToTempQueue(List<TaxpayerSTSNoCukaiViewModel> lst)
        {
            if (lst != null)
            {
                DataTable dTbl = lst.ToDataTable<TaxpayerSTSNoCukaiViewModel>();

                ExecuteSP("proc_InsertTempSTSNoCukaiQueue", "@tblTempSTSNoCukai", dTbl);
            }
        }
    }
}
