using LHDN.CRM.BC.Common;
using LHDN.CRM.BC.Repositories;
using LHDN.CRM.Models.Models;
using LHDN.CRM.Models.ViewModels;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LHDN.CRM.BC.Repositories
{
    public class OutboundRepository : BaseRepository<CRMContext, TaxPayer>
    {
        public long AddOutboundSyncSchedule(long scheduleID, DateTime startDate, DateTime endDate)
        {
            var ds = ExecuteSP("proc_AddOutboundSSISTask", "@scheduleID", scheduleID, "@startDate", startDate, "@endDate", endDate);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    long executionID;
                    var exId = ds.Tables[0].Columns["ExecutionID"];
                    if (exId != null && long.TryParse(exId.ToString(), out executionID))
                        return executionID;
                }
            }

            return 0;
        }

        public void AddOutboundSSISTask(long scheduleID, long viewID, string userID, DateTime startDate, DateTime endDate, bool blnIsHCLEmailBlast)
        {
            ExecuteSP("proc_AddOutboundSSISTask", "@scheduleID", scheduleID, "@viewID", viewID, "@user", userID, "@startDate", startDate, "@endDate", endDate, "@IsHCLEmailBlast", blnIsHCLEmailBlast);
        }

        public List<SearchOutboundViewsViewModel> GetOutboundViewDDL(long lgUserprofileID)
        {
            return ExecuteSP<SearchOutboundViewsViewModel>("proc_GetOutboundSavedView",
                                                 "@UserprofileID", lgUserprofileID).ToList();
        }

        public List<SearchOutboundViewsViewModel> GetEmailBlastViewDDL(long lgUserprofileID)
        {
            return ExecuteSP<SearchOutboundViewsViewModel>("proc_GetEmailBlastSavedView",
                                                 "@UserprofileID", lgUserprofileID).ToList();
        }

        public List<SearchSavedViewViewModel> GetOutboundSavedViewColumns(int intViewID, int intJenisKempenID,
                        bool blnIsDynamic, List<DisplayColumnViewModel> lstDisplayCol)
        {
            DataTable dtblDisplayCol = (new List<DisplayColumnViewModel>()).ToDataTable<DisplayColumnViewModel>();

            if (lstDisplayCol != null) dtblDisplayCol = lstDisplayCol.ToDataTable<DisplayColumnViewModel>();

            dtblDisplayCol.TableName = "DisplayColumn";
            List<SearchSavedViewViewModel> lstResult = new List<SearchSavedViewViewModel>();
            lstResult = ExecuteSPDynamic<SearchSavedViewViewModel>("proc_GetOutboundSavedViewColumns",
                                                        "@ViewID", intViewID,
                                                        "@JenisKempenID", intJenisKempenID,
                                                        "@IsDynamic", blnIsDynamic,
                                                        "@DisplayColumn", dtblDisplayCol
                                                        ).ToList();
            return lstResult;
        }

        public List<SearchSavedViewViewModel> GetEmailBlastSavedViewColumns(int intViewID, int intJenisKempenID,
                        bool blnIsDynamic, List<DisplayColumnViewModel> lstDisplayCol)
        {
            DataTable dtblDisplayCol = (new List<DisplayColumnViewModel>()).ToDataTable<DisplayColumnViewModel>();

            if (lstDisplayCol != null) dtblDisplayCol = lstDisplayCol.ToDataTable<DisplayColumnViewModel>();

            dtblDisplayCol.TableName = "DisplayColumn";
            return ExecuteSPDynamic<SearchSavedViewViewModel>("proc_GetEmailBlastSavedViewColumns",
                                                        "@ViewID", intViewID,
                                                        "@JenisKempenID", intJenisKempenID,
                                                        "@IsDynamic", blnIsDynamic,
                                                        "@DisplayColumn", dtblDisplayCol
                                                        ).ToList();
        }

        public JenisKempenDetailViewModel GetJenisKempenDetailByID(int intJenisKempenId, long lngOutboundId, long lngEmailBlastID = 0)
        {
            DataSet ds = ExecuteSP("proc_GetJenisKempenDetailByID", "@JenisKempenID", intJenisKempenId, "@OutboundID", lngOutboundId, "EmailBlastID", lngEmailBlastID);
            JenisKempenDetailViewModel vm = new JenisKempenDetailViewModel();
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    vm = DataTableToList<JenisKempenDetailViewModel>(ds.Tables[0]).FirstOrDefault();
                }
            }

            return vm;
        }

        public OutboundDetailsTunggakanViewModel GetOutboundDetailsTunggakan(string strReMSTableName, int intJenisKempenID, long lngDataID)
        {
            DataSet ds = ExecuteSP("proc_GetOutboundDetailsTunggakan", "@ReMSTableName", strReMSTableName, "@JenisKempenID", intJenisKempenID, "@DataID", lngDataID);
            OutboundDetailsTunggakanViewModel vm = DataTableToList<OutboundDetailsTunggakanViewModel>(ds.Tables[0]).FirstOrDefault();

            if (ds.Tables.Count > 1)
                vm.TP = DataTableToList<TPViewModel>(ds.Tables[1]).FirstOrDefault();

            return vm;
        }

        public OutboundDetailsAnsuranViewModel GetOutboundDetailsAnsuran(string strReMSTableName, int intJenisKempenID, long lngDataID)
        {
            DataSet ds = ExecuteSP("proc_GetOutboundDetailsAnsuran", "@ReMSTableName", strReMSTableName, "@JenisKempenID", intJenisKempenID, "@DataID", lngDataID);
            OutboundDetailsAnsuranViewModel vm = DataTableToList<OutboundDetailsAnsuranViewModel>(ds.Tables[0]).FirstOrDefault();

            if (ds.Tables.Count > 1)
                vm.TP = DataTableToList<TPViewModel>(ds.Tables[1]).FirstOrDefault();

            return vm;
        }

        //public OutboundAnsuranActDetailsViewModel GetOutboundAnsuranActDetails(string strReMSTableName, long lngDataID)
        //{
        //    return ExecuteSP<OutboundAnsuranActDetailsViewModel>("proc_GetOutboundAnsuranActDetails", "@ReMSTableName", strReMSTableName,  "@DataID", lngDataID).FirstOrDefault();

        //}


        public OutboundDetailsCTLViewModel GetOutboundDetailsCTL(string strReMSTableName, int intJenisKempenID, long lngDataID)
        {
            DataSet ds = ExecuteSP("proc_GetOutboundDetailsCTL", "@ReMSTableName", strReMSTableName, "@JenisKempenID", intJenisKempenID, "@DataID", lngDataID);
            OutboundDetailsCTLViewModel vm = DataTableToList<OutboundDetailsCTLViewModel>(ds.Tables[0]).FirstOrDefault();

            if (ds.Tables.Count > 1)
                vm.TP = DataTableToList<TPViewModel>(ds.Tables[1]).FirstOrDefault();

            return vm;
        }

        public DataSet GetOutboundManualAssigned(OutboundManualAssignedPagedListViewModel paging)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", paging.PageIndex,
                                        "@PageSize", paging.Length,
                                        "@JenisKempenID",paging.JenisKempenID,
                                        "@TPName",paging.TPName});
            if (!string.IsNullOrEmpty(paging.SortExpression))
            {
                lstParam.AddRange(new object[] {
                "@SortExpression", paging.SortExpression
                });
            }

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

        public OutboundManualAssignedPagedListViewModel GetManualAgentAssignByID(OutboundManualAssignedPagedListViewModel paging, DateTime dtNow)
        {
            OutboundManualAssignedPagedListViewModel result = new OutboundManualAssignedPagedListViewModel();
            List<object> lstParam = new List<object>(new object[] {
                                        "@OutboundID",paging.OutboundID
                                       });

            DataSet ds = ExecuteSP("proc_GetOutboundManualAssignByID", lstParam.ToArray());
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    result.JenisKempenID = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                    result.DataID = Convert.ToInt32(ds.Tables[0].Rows[0][1]);
                }
            }

            return result;
        }

        public List<OutboundUser> GetAllUser(OutboundUser datainfo)
        {
            var data = ExecuteSP<OutboundUser>("proc_GetAllUser", "@keyword", datainfo.keyword).ToList();

            return data;
        }

        public DataSet GetManualManagerAssignedByID(List<OutboundUpdateViewModel> model, OutboundUser assignUser, OutboundUser CurrentLoginUser, string strRemark, DateTime dtUpdated)
        {
            DataTable dTbl = model.ToDataTable<OutboundUpdateViewModel>();
            dTbl.Columns.Remove("HasError");

            DataSet ds = ExecuteSP("proc_GetOutboundManagerAssignedByID",
                                   "@tbl", dTbl);

            return ds;

        }

        public DataSet GetMyPendingOutboundInDashboard(string CurrentUserLogin, OutboundManualAssignedPagedListViewModel paging)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", paging.PageIndex,
                                        "@PageSize", paging.Length,
                                        "@CurrentUserLogin", CurrentUserLogin});

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

            return ExecuteSP("proc_GetDashboardMyPendingOutbound", lstParam.ToArray());

        }

        public DataSet GetSearchOutboundResult(SearchOutboundPagedListViewModel paging)
        {

            DataSet ds = new DataSet();
            ds = Utilities.GenerateAdvanceRulesViewTables(Utilities.GenerateRowIDForAdvanceRules(paging.Rules));
            DataTable dtblDisplayCol = (new List<DisplayColumnViewModel>()).ToDataTable<DisplayColumnViewModel>();
            DataTable dtblFieldsSorting = (new List<RulesSortingViewModel>()).ToDataTable<RulesSortingViewModel>();

            if (paging.DisplayColumns != null) dtblDisplayCol = paging.DisplayColumns.ToDataTable<DisplayColumnViewModel>();
            if (paging.FieldsSorting != null) dtblFieldsSorting = paging.FieldsSorting.ToDataTable<RulesSortingViewModel>();

            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", paging.PageIndex,
                                        "@PageSize", paging.Length,
                                        "@ViewID", paging.ViewID,
                                        "@CampaignID", paging.CampaignID,
                                        "@JenisPengenalanID", paging.JenisPengenalanID,
                                        "@NoPengenalan", paging.NoPengenalan,
                                        "@BatchRefNo", paging.BatchRefNo,
                                        "@Nama", paging.Nama,
                                        "@RujukanNo", paging.RujukanNo,
                                        "@IsExact", paging.IsExact,
                                        "@JenisKempenID", paging.JenisKempenID,
                                        "@TahunTaksiran", paging.TahunTaksiran,
                                        "@ReMSDateFrom", paging.ReMSDateFrom,
                                        "@ReMSDateTo", paging.ReMSDateTo,
                                        "@ImportedDateFrom", paging.ImportedDateFrom,
                                        "@ImportedDateTo", paging.ImportedDateTo,
                                        "@IsAdvancedRules", paging.IsAdvancedRules,
                                        "@view", ds.Tables[0],
                                        "@RuleGroup", ds.Tables[1],
                                        "@FieldValue", ds.Tables[2],
                                        "@JenisCukai", paging.JenisCukai,
                                        "@OutboundStatusID", paging.OutboundStatusID,
                                        "@IsFromSeg", paging.IsFromSeg,
                                        "@IsReturnCount",paging.IsReturnCount,
                                        "@SkipSavedRules",paging.SkipSavedRules,
                                        "@IsFromListingSeg", paging.IsFromListingSeg,
                                        "@IsDynamic", paging.IsDynamicDisplayColumn,
                                        "@DisplayColumn", dtblDisplayCol,
                                        "@FieldSorting", dtblFieldsSorting
                                    });

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

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

        public DataSet GetSearchEmailBlastResult(SearchOutboundPagedListViewModel paging)
        {

            DataSet ds = new DataSet();
            ds = Utilities.GenerateAdvanceRulesViewTables(Utilities.GenerateRowIDForAdvanceRules(paging.Rules));
            DataTable dtblDisplayCol = (new List<DisplayColumnViewModel>()).ToDataTable<DisplayColumnViewModel>();
            DataTable dtblFieldsSorting = (new List<RulesSortingViewModel>()).ToDataTable<RulesSortingViewModel>();

            if (paging.DisplayColumns != null) dtblDisplayCol = paging.DisplayColumns.ToDataTable<DisplayColumnViewModel>();
            if (paging.FieldsSorting != null) dtblFieldsSorting = paging.FieldsSorting.ToDataTable<RulesSortingViewModel>();

            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", paging.PageIndex,
                                        "@PageSize", paging.Length,
                                        "@ViewID", paging.ViewID,
                                        "@CampaignID", paging.CampaignID,
                                        "@JenisPengenalanID", paging.JenisPengenalanID,
                                        "@NoPengenalan", paging.NoPengenalan,
                                        "@BatchRefNo", paging.BatchRefNo,
                                        "@Nama", paging.Nama,
                                        "@RujukanNo", paging.RujukanNo,
                                        "@IsExact", paging.IsExact,
                                        "@JenisKempenID", paging.JenisKempenID,
                                        "@ImportedDateFrom", paging.ImportedDateFrom,
                                        "@ImportedDateTo", paging.ImportedDateTo,
                                        "@IsAdvancedRules", paging.IsAdvancedRules,
                                        "@view", ds.Tables[0],
                                        "@RuleGroup", ds.Tables[1],
                                        "@FieldValue", ds.Tables[2],
                                        "@IsFromSeg", paging.IsFromSeg,
                                        "@IsReturnCount",paging.IsReturnCount,
                                        "@SkipSavedRules",paging.SkipSavedRules,
                                        "@IsFromListingSeg", paging.IsFromListingSeg,
                                        "@IsFromListingBasicSearch", paging.IsFromListingBasicSearch,
                                        "@IsDynamic", paging.IsDynamicDisplayColumn,
                                        "@DisplayColumn", dtblDisplayCol,
                                        "@FieldSorting", dtblFieldsSorting
                                    });

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

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


        public DataTable UpdateOutboundSegmentise(OutboundSegmentiseViewModel outboundViewVM, List<AdvanceRulesViewModel> advRuleViewRuleVM,
                                                  List<RuleGroupViewModel> advRuleViewRuleGroupVM, List<FieldValueViewModel> advRuleViewRuleGroupFieldsVM,
                                                  List<DisplayColumnViewModel> lstDisplayCol, List<RulesSortingViewModel> lstFieldsSorting,
                                                  string strLastUpdatedBy, DateTime dtLastUpdatedDateTime)
        {
            DataTable dTblOutboundSeg = outboundViewVM.ToDataTable();

            DataTable dTblAdvRuleViewRuleVM = advRuleViewRuleVM.ToDataTable<AdvanceRulesViewModel>();
            DataTable dTblAdvRuleViewRuleGroupVM = advRuleViewRuleGroupVM.ToDataTable<RuleGroupViewModel>();
            DataTable dTblAdvRuleViewRuleGroupFieldsVM = advRuleViewRuleGroupFieldsVM.ToDataTable<FieldValueViewModel>();

            dTblOutboundSeg.Columns.Remove("ChangeStamp");
            dTblOutboundSeg.Columns.Remove("Campaign");
            dTblOutboundSeg.Columns.Remove("IsCampaignStarted");
            dTblOutboundSeg.Columns.Remove("PermissionTypeDesc");

            dTblAdvRuleViewRuleVM.Columns.Remove("selected");
            dTblAdvRuleViewRuleVM.Columns.Remove("RowSelected");
            dTblAdvRuleViewRuleVM.Columns.Remove("RuleGroup");

            dTblAdvRuleViewRuleGroupVM.Columns.Remove("TableName");
            dTblAdvRuleViewRuleGroupVM.Columns.Remove("selected");
            dTblAdvRuleViewRuleGroupVM.Columns.Remove("FieldValues");

            dTblAdvRuleViewRuleGroupFieldsVM.Columns.Remove("IsMultiple");
            DataTable dtblDisplayCol = (new List<DisplayColumnViewModel>()).ToDataTable<DisplayColumnViewModel>();
            if (lstDisplayCol != null) dtblDisplayCol = lstDisplayCol.ToDataTable<DisplayColumnViewModel>();

            DataTable dtblFieldsSorting = (new List<RulesSortingViewModel>()).ToDataTable<RulesSortingViewModel>();
            if (lstFieldsSorting != null) dtblFieldsSorting = lstFieldsSorting.Where(x => x.FieldMappingID != 0).ToList().ToDataTable<RulesSortingViewModel>();

            DataSet ds = ExecuteSP("proc_UpdateOutboundSegmentise", "@tblOutboundView", dTblOutboundSeg,
                                                    "@View", dTblAdvRuleViewRuleVM,
                                                    "@RuleGroup", dTblAdvRuleViewRuleGroupVM,
                                                    "@Fieldvalue", dTblAdvRuleViewRuleGroupFieldsVM,
                                                    "@displayColumn", dtblDisplayCol,
                                                    "@FieldSorting", dtblFieldsSorting,
                                                    "@LastUpdatedBy", strLastUpdatedBy,
                                                    "@LastUpdatedDateTime", dtLastUpdatedDateTime);

            DataTable dTblResult = new DataTable();
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    dTblResult = ds.Tables[0];
                }
            }

            return dTblResult;
        }

        public DataTable UpdateEmailBlastSegmentise(OutboundSegmentiseViewModel outboundViewVM, List<AdvanceRulesViewModel> advRuleViewRuleVM,
                                          List<RuleGroupViewModel> advRuleViewRuleGroupVM, List<FieldValueViewModel> advRuleViewRuleGroupFieldsVM,
                                          List<DisplayColumnViewModel> lstDisplayCol, List<RulesSortingViewModel> lstFieldsSorting,
                                          string strLastUpdatedBy, DateTime dtLastUpdatedDateTime)
        {
            DataTable dTblEmailBlastSeg = outboundViewVM.ToDataTable();

            DataTable dTblAdvRuleViewRuleVM = advRuleViewRuleVM.ToDataTable<AdvanceRulesViewModel>();
            DataTable dTblAdvRuleViewRuleGroupVM = advRuleViewRuleGroupVM.ToDataTable<RuleGroupViewModel>();
            DataTable dTblAdvRuleViewRuleGroupFieldsVM = advRuleViewRuleGroupFieldsVM.ToDataTable<FieldValueViewModel>();
            DataTable dtblDisplayCol = (new List<DisplayColumnViewModel>()).ToDataTable<DisplayColumnViewModel>();
            if (lstDisplayCol != null) dtblDisplayCol = lstDisplayCol.ToDataTable<DisplayColumnViewModel>();

            DataTable dtblFieldsSorting = (new List<RulesSortingViewModel>()).ToDataTable<RulesSortingViewModel>();
            if (lstFieldsSorting != null) dtblFieldsSorting = lstFieldsSorting.Where(x => x.FieldMappingID != 0).ToList().ToDataTable<RulesSortingViewModel>();

            dTblEmailBlastSeg.Columns.Remove("ChangeStamp");
            dTblEmailBlastSeg.Columns.Remove("Campaign");
            dTblEmailBlastSeg.Columns.Remove("IsCampaignStarted");
            dTblEmailBlastSeg.Columns.Remove("PermissionTypeDesc");  


            dTblAdvRuleViewRuleVM.Columns.Remove("selected");
            dTblAdvRuleViewRuleVM.Columns.Remove("RowSelected");
            dTblAdvRuleViewRuleVM.Columns.Remove("RuleGroup");

            dTblAdvRuleViewRuleGroupVM.Columns.Remove("TableName");
            dTblAdvRuleViewRuleGroupVM.Columns.Remove("selected");
            dTblAdvRuleViewRuleGroupVM.Columns.Remove("FieldValues");

            dTblAdvRuleViewRuleGroupFieldsVM.Columns.Remove("IsMultiple");

            DataSet ds = ExecuteSP("proc_UpdateEmailBlastSegmentise", "@tblOutboundView", dTblEmailBlastSeg,
                                                    "@View", dTblAdvRuleViewRuleVM,
                                                    "@RuleGroup", dTblAdvRuleViewRuleGroupVM,
                                                    "@Fieldvalue", dTblAdvRuleViewRuleGroupFieldsVM,
                                                    "@displayColumn", dtblDisplayCol,
                                                    "@FieldSorting", dtblFieldsSorting,
                                                    "@LastUpdatedBy", strLastUpdatedBy,
                                                    "@LastUpdatedDateTime", dtLastUpdatedDateTime);

            DataTable dTblResult = new DataTable();
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    dTblResult = ds.Tables[0];
                }
            }

            return dTblResult;
        }

        public OutboundSegmentiseViewModel GetOutboundSegmentise(long lngViewID)
        {
            return ExecuteSP<OutboundSegmentiseViewModel>("proc_GetOutboundSegmentise", "@ViewID", lngViewID).FirstOrDefault();
        }

        public OutboundSegmentiseViewModel GetEmailBlastSegmentise(long lngViewID)
        {
            return ExecuteSP<OutboundSegmentiseViewModel>("proc_GetEmailBlastSegmentise", "@ViewID", lngViewID).FirstOrDefault();
        }

        public byte[] GetChangeStampbyID(long ID, int intTableID)
        {
            byte[] changeStamp = null;
            DataSet ds = ExecuteSP("proc_GetChangeStamp", "@Table", intTableID, "@ID", ID);

            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    changeStamp = (byte[])ds.Tables[0].Rows[0][0];
                }
            }
            return changeStamp;
        }


        public DataTable UpdateOutboundCampaign(OutboundCampaignViewModel outboundCampaignVM, string strLastUpdatedBy, DateTime dtLastUpdatedDateTime)
        {
            DataTable dTblOutboundCam = outboundCampaignVM.ToDataTable<OutboundCampaignViewModel>();
            dTblOutboundCam.Columns.Remove("ChangeStamp");

            DataSet ds = ExecuteSP("proc_UpdateOutboundCampaign", "@tblOutboundCampaign", dTblOutboundCam,
                                                    "@LastUpdatedBy", strLastUpdatedBy,
                                                    "@LastUpdatedDateTime", dtLastUpdatedDateTime);

            DataTable dTblResult = new DataTable();
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    dTblResult = ds.Tables[0];
                }
            }

            return dTblResult;
        }

        public DataTable UpdateEmailBlastCampaign(OutboundCampaignViewModel outboundCampaignVM, string strLastUpdatedBy, DateTime dtLastUpdatedDateTime)
        {
            DataTable dTblEmailBlastCam = outboundCampaignVM.ToDataTable<OutboundCampaignViewModel>();
            dTblEmailBlastCam.Columns.Remove("ChangeStamp");

            DataSet ds = ExecuteSP("proc_UpdateEmailBlastCampaign", "@tblOutboundCampaign", dTblEmailBlastCam,
                                                    "@LastUpdatedBy", strLastUpdatedBy,
                                                    "@LastUpdatedDateTime", dtLastUpdatedDateTime);

            DataTable dTblResult = new DataTable();
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    dTblResult = ds.Tables[0];
                }
            }

            return dTblResult;
        }

        public OutboundCampaignViewModel GetOutboundCampaign(long lngViewID, int intCampaignID)
        {
            return ExecuteSP<OutboundCampaignViewModel>("proc_GetOutboundCampaign", "@ViewID", lngViewID, "@CampaignID", intCampaignID).FirstOrDefault();
        }

        public OutboundCampaignViewModel GetEmailBlastCampaign(long lngViewID, int intCampaignID)
        {
            return ExecuteSP<OutboundCampaignViewModel>("proc_GetEmailBlastCampaign", "@ViewID", lngViewID, "@CampaignID", intCampaignID).FirstOrDefault();
        }

        public OutboundUpdateViewModel GetOutbound(long lngOutboundID, int intJenisKempenID, long lngDataID)
        {
            return ExecuteSP<OutboundUpdateViewModel>("proc_GetOutbound", "@OutboundID", lngOutboundID, "@JenisKempenID", intJenisKempenID
                                                        , "@@ReMSDataID", lngDataID).FirstOrDefault();
        }

        public int UpdateOutboundDetailsAnsuran(OutboundDetailsAnsuranDetailViewModel vm)
        {
            DataTable dTbl = vm.ToDataTable();
            dTbl.Columns.Remove("ChangeStamp");
            dTbl.Columns.Remove("JenisKempenID");

            dTbl.Columns["NoCukai"].ColumnName = "IT_REF_NO";

            int i = ExecuteUpdateSP("proc_UpdateOutboundDetailsAnsuran", "@tblOutDetails", dTbl,
                                                        "@LastUpdatedBy", vm.LastUpdatedBy,
                                                        "@LastUpdatedByName", vm.LastUpdatedByName,
                                                        "@LastUpdatedDateTime", vm.LastUpdatedDateTime
                                                        );
            return i;
        }

        public int UpdateOutboundDetailsTunggakan(OutboundDetailsTunggakanDetailViewModel vm)
        {
            DataTable dTbl = vm.ToDataTable();
            dTbl.Columns.Remove("ChangeStamp");
            dTbl.Columns.Remove("JenisKempenID");

            dTbl.Columns["TAX_PAYER_NAME1"].ColumnName = "NAME";
            dTbl.Columns["NoCukai"].ColumnName = "IT_REF_NO";
            dTbl.Columns["PHONE1"].ColumnName = "TELEPHONE";
            dTbl.Columns["PHONE2"].ColumnName = "TELEPHONE1";
            dTbl.Columns["PHONE3"].ColumnName = "TELEPHONE2";

            int i = ExecuteUpdateSP("proc_UpdateOutboundDetailsTunggakan", "@tblOutDetails", dTbl,
                                                        "@LastUpdatedBy", vm.LastUpdatedBy,
                                                        "@LastUpdatedByName", vm.LastUpdatedByName,
                                                        "@LastUpdatedDateTime", vm.LastUpdatedDateTime
                                                        );
            return i;
        }

        public int UpdateOutboundDetailsCTL(OutboundDetailsCTLDetailViewModel vm)
        {
            DataTable dTbl = vm.ToDataTable();
            dTbl.Columns.Remove("ChangeStamp");
            dTbl.Columns.Remove("JenisKempenID");

            dTbl.Columns["NoCukai"].ColumnName = "IT_REF_NO";

            int i = ExecuteUpdateSP("proc_UpdateOutboundDetailsCTL", "@tblOutDetails", dTbl,
                                                        "@LastUpdatedBy", vm.LastUpdatedBy,
                                                        "@LastUpdatedByName", vm.LastUpdatedByName,
                                                        "@LastUpdatedDateTime", vm.LastUpdatedDateTime
                                                        );
            return i;
        }

        public void UpdateOutboundReMSActDetails(OutboundReMSForActivityViewModel vm, string strReMSTableName, string strUserName, string strUserID, DateTime dtUpdated)
        {
            if (vm.ACT_TarikhMulaAnsuran.HasValue)
                vm.ACT_TarikhMulaAnsuran = vm.ACT_TarikhMulaAnsuran.Value.ToLocalTime();

            if (vm.ACT_TarikhJanjiBayar.HasValue)
                vm.ACT_TarikhJanjiBayar = vm.ACT_TarikhJanjiBayar.Value.ToLocalTime();

            DataTable dTbl = vm.ToDataTable();
            ExecuteUpdateSP("proc_UpdateOutboundReMSFromAct", "@tblOutActDetails", dTbl,
                                                        "@ReMSTableName", strReMSTableName,
                                                        "@LastUpdatedBy", strUserID,
                                                        "@LastUpdatedByName", strUserName,
                                                        "@LastUpdatedDateTime", dtUpdated
                                                        );
        }

        public long GetOutboundReMSActivityIDByDataID(long lngReMSDataID , string strReMSTableName)
        { 
           
            DataSet ds = ExecuteSP("proc_GetOutboundReMSActivityIDByDataID", "@ReMSDataID", lngReMSDataID,
                                                        "@ReMSTableName", strReMSTableName
                                                        );
            long lngActivityID = 0;
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    lngActivityID = Convert.ToInt64(ds.Tables[0].Rows[0][0]);
                }
            }
            return lngActivityID;
        }

        public void UpdateOutboundHistoryDetailsByUpdatedDate(long lngOutboundID, int intCallStatusID, string strUserID, DateTime dtUpdated)
        {
            ExecuteUpdateSP("proc_UpdateOutboundHistoryDetailsByUpdatedDate", "@OutboundID", lngOutboundID,
                                                        "@CallStatusID", intCallStatusID,
                                                        "@LastUpdatedBy", strUserID,
                                                        "@LastUpdatedDateTime", dtUpdated
                                                        );
        }

        public int InsertChangeHistory(List<ChangeHistoryUpdateViewModel> lst, string strUserName, string strUserID, DateTime dtUpdated)
        {
            DataTable dTbl = lst.ToDataTable<ChangeHistoryUpdateViewModel>();
            dTbl.Columns.Remove("IsChanged");

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

        public DataSet GetAllSegmentiseList(SegmentisePagedListViewModel paging)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", paging.PageIndex,
                                        "@PageSize", paging.Length,
                                        "@JenisKempenID", paging.JenisKempenID
                                    });

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

        public DataSet GetAllEmailBlastSegmentiseList(SegmentisePagedListViewModel paging)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", paging.PageIndex,
                                        "@PageSize", paging.Length,
                                        "@JenisKempenID", paging.JenisKempenID
                                    });

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

        public DataSet GetAllReMSBatchList(ReMSBatchPagedListViewModel paging)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", paging.PageIndex,
                                        "@PageSize", paging.Length,
                                        "@JenisKempenID", paging.JenisKempenID,
                                        "@DateFrom", paging.DateFrom,
                                        "@DateTo", paging.DateTo,
                                        "@BatchRefNo", paging.BatchRefNo
                                    });

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

        public DataSet GetAllEmailBlastBatchList(HCLEmailBlastBatchPagedListViewModel paging)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", paging.PageIndex,
                                        "@PageSize", paging.Length,
                                        "@JenisKempenID", paging.JenisKempenID,
                                        "@DateFrom", paging.DateFrom,
                                        "@DateTo", paging.DateTo,
                                        "@BatchRefNo", paging.BatchRefNo,
                                        "@UploadedByName", paging.UploadedByName
                                    });

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

        public DataSet GetReMSDataByBatchID(ReMSDataPagedListViewModel paging)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", paging.PageIndex,
                                        "@PageSize", paging.Length,
                                        "@BatchID", paging.BatchID,
                                        "@JenisKempenID", paging.JenisKempenID
                                    });

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

        public DataSet GetHCLEmailBlastDataByBatchID(HCLEmailBlastDataPagedListViewModel paging)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", paging.PageIndex,
                                        "@PageSize", paging.Length,
                                        "@BatchID", paging.BatchID,
                                        "@JenisKempenID", paging.JenisKempenID
                                    });

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

        public DataSet GetInvalidReMSDataByBatchID(ReMSDataPagedListViewModel paging)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", paging.PageIndex,
                                        "@PageSize", paging.Length,
                                        "@BatchID", paging.BatchID,
                                        "@JenisKempenID", paging.JenisKempenID
                                    });

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

        public DataSet GetInvalidEmailBlastDataByBatchID(HCLEmailBlastDataPagedListViewModel paging)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", paging.PageIndex,
                                        "@PageSize", paging.Length,
                                        "@BatchID", paging.BatchID,
                                        "@JenisKempenID", paging.JenisKempenID
                                    });

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

        //public void ReturnReMSBatch(ReMSBatchReturnViewModel model)
        //{
        //    ExecuteSP("proc_ReturnReMSBatch", "@BatchID", model.BatchID,
        //                                            "@JenisKempenID", model.JenisKempenID,
        //                                            "@ReturnedBy", model.ReturnedBy,
        //                                            "@ReturnedByName", model.ReturnedByName,
        //                                            "@ReturnedDateTime", model.ReturnedDateTime,
        //                                            "@ReturnReasonID", model.ReturnReasonID,
        //                                            "@Remarks", model.Remarks);

        //}

        public int ReturnReMSByBatchID(ReMSBatchReturnViewModel model)
        {
            return ExecuteUpdateSP("proc_ReturnReMSByBatchID", "@BatchID", model.BatchID,
                                                    "@JenisKempenID", model.JenisKempenID,
                                                    "@ReturnedBy", model.ReturnedBy,
                                                    "@ReturnedByName", model.ReturnedByName,
                                                    "@ReturnedDateTime", model.ReturnedDateTime,
                                                    "@ReturnReasonID", model.ReturnReasonID,
                                                    "@Remarks", model.Remarks);
        }

        public DataSet RejectEmailBlastByBatchID(HCLEmailBlastDataPagedListViewModel model, string strLastUpdatedBy, string strLastUpdateByName, DateTime dtLastUpdatedDateTime)
        {
            return ExecuteSP("proc_RejectEmailBlastByBatchID", "@BatchID", model.BatchID,
                                                    "@LastUpdatedBy", strLastUpdatedBy,
                                                    "@LastUpdatedByName", strLastUpdateByName,
                                                    "@LastUpdatedDateTime", dtLastUpdatedDateTime);
        }

        public ReMSBatchListingViewModel GetReMSBatchDetailsByBatchID(int intBatchID, int intJenisKempenID)
        {
            return ExecuteSP<ReMSBatchListingViewModel>("proc_GetReMSBatchDetailsByBatchID", "@BatchID", intBatchID,
                                                                                            "@JenisKempenID", intJenisKempenID).FirstOrDefault();
        }

        public HCLEmailBlastBatchListingViewModel GetEmailBlastBatchDetailsByBatchID(int intBatchID, int intJenisKempenID)
        {
            return ExecuteSP<HCLEmailBlastBatchListingViewModel>("proc_GetEmailBlastBatchDetailsByBatchID", "@BatchID", intBatchID,
                                                                                            "@JenisKempenID", intJenisKempenID).FirstOrDefault();
        }

        public bool DeleteSegmentise(long intViewID)
        {
            DataSet ds = ExecuteSP("proc_DeleteSegmentise", "@ViewID", intViewID);
            bool blnIsSuccess = false;

            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    blnIsSuccess = Convert.ToBoolean(ds.Tables[0].Rows[0][0]);
                }
            }
            return blnIsSuccess;
        }

        public bool DeleteEmailBlastSegmentise(long intViewID)
        {
            DataSet ds = ExecuteSP("proc_DeleteEmailBlastSegmentise", "@ViewID", intViewID);
            bool blnIsSuccess = false;

            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    blnIsSuccess = Convert.ToBoolean(ds.Tables[0].Rows[0][0]);
                }
            }
            return blnIsSuccess;
        }

        public bool DeleteCampaign(long intViewID)
        {
            DataSet ds = ExecuteSP("proc_DeleteCampaign", "@ViewID", intViewID);
            bool blnIsSuccess = false;

            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    blnIsSuccess = Convert.ToBoolean(ds.Tables[0].Rows[0][0]);
                }
            }
            return blnIsSuccess;
        }

        public bool DeleteEmailBlastCampaign(long intViewID)
        {
            DataSet ds = ExecuteSP("proc_DeleteEmailBlastCampaign", "@ViewID", intViewID);
            bool blnIsSuccess = false;

            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    blnIsSuccess = Convert.ToBoolean(ds.Tables[0].Rows[0][0]);
                }
            }
            return blnIsSuccess;
        }

        public DataSet GetAllOutboundSchedule(string strSortExpr, int intPageIndex, int intPageSize, int intCampaignID)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", intPageIndex,
                                        "@PageSize", intPageSize,
                                        "@CampaignID", intCampaignID

                                    });

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

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

        public DataSet GetAllEmailBlastSchedule(string strSortExpr, int intPageIndex, int intPageSize, int intCampaignID)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", intPageIndex,
                                        "@PageSize", intPageSize,
                                        "@CampaignID", intCampaignID

                                    });

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

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

        public OutboundScheduleViewModel GetOutboundScheduleByID(int intScheduleID)
        {
            return ExecuteSP<OutboundScheduleViewModel>("proc_GetOutboundScheduleByID", "@ScheduleID", intScheduleID).FirstOrDefault();
        }

        public OutboundScheduleViewModel GetEmailBlastScheduleByID(int intScheduleID)
        {
            return ExecuteSP<OutboundScheduleViewModel>("proc_GetEmailBlastScheduleByID", "@ScheduleID", intScheduleID).FirstOrDefault();
        }

        public DataTable UpdateOutboundScheduleByID(OutboundScheduleViewModel outboundScheduleVM, string strLastUpdatedBy, DateTime dtLastUpdatedDateTime)
        {
            DataTable dTblOutboundSchedule = outboundScheduleVM.ToDataTable<OutboundScheduleViewModel>();

            dTblOutboundSchedule.Columns.Remove("ChangeStamp");
            dTblOutboundSchedule.Columns.Remove("HasRun");
            dTblOutboundSchedule.Columns.Remove("JenisKempenID");
            dTblOutboundSchedule.Columns.Remove("OutboundScheduleID");

            DataSet ds = ExecuteSP("proc_UpdateOutboundSchedule", "@tblOutboundSchedule", dTblOutboundSchedule,
                                                    "@LastUpdatedBy", strLastUpdatedBy,
                                                    "@LastUpdatedDateTime", dtLastUpdatedDateTime);

            DataTable dTblResult = new DataTable();
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    dTblResult = ds.Tables[0];
                }
            }

            return dTblResult;
        }

        public DataTable UpdateEmailBlastScheduleByID(OutboundScheduleViewModel outboundScheduleVM, string strLastUpdatedBy, DateTime dtLastUpdatedDateTime)
        {
            DataTable dTblEmailBlastSchedule = outboundScheduleVM.ToDataTable<OutboundScheduleViewModel>();

            dTblEmailBlastSchedule.Columns.Remove("ChangeStamp");
            dTblEmailBlastSchedule.Columns.Remove("HasRun");
            dTblEmailBlastSchedule.Columns.Remove("JenisKempenID");
            dTblEmailBlastSchedule.Columns.Remove("OutboundScheduleID");

            DataSet ds = ExecuteSP("proc_UpdateEmailBlastSchedule", "@tblOutboundSchedule", dTblEmailBlastSchedule,
                                                    "@LastUpdatedBy", strLastUpdatedBy,
                                                    "@LastUpdatedDateTime", dtLastUpdatedDateTime);

            DataTable dTblResult = new DataTable();
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    dTblResult = ds.Tables[0];
                }
            }

            return dTblResult;
        }

        public bool DeleteSchedule(int intScheduleID, string ReMSTableName, string strLastUpdatedBy, string strLastUpdateByName, DateTime dtLastUpdatedDateTime)
        {
            DataSet ds = ExecuteSP("proc_DeleteSchedule",
                                        "@ScheduleID", intScheduleID,
                                        "@ReMsTableName", ReMSTableName,
                                        "@LastUpdatedBy", strLastUpdatedBy,
                                        "@LastUpdatedByName", strLastUpdateByName,
                                        "@LastUpdatedDateTime", dtLastUpdatedDateTime);
            bool blnIsSuccess = false;

            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    blnIsSuccess = Convert.ToBoolean(ds.Tables[0].Rows[0][0]);
                }
            }
            return blnIsSuccess;
        }

        public bool DeleteEmailBlastSchedule(int intScheduleID, string strLastUpdatedBy, string strLastUpdateByName, DateTime dtLastUpdatedDateTime)
        {
            DataSet ds = ExecuteSP("proc_DeleteEmailBlastSchedule",
                                        "@ScheduleID", intScheduleID,
                                        "@LastUpdatedBy", strLastUpdatedBy,
                                        "@LastUpdatedByName", strLastUpdateByName,
                                        "@LastUpdatedDateTime", dtLastUpdatedDateTime);
            bool blnIsSuccess = false;

            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    blnIsSuccess = Convert.ToBoolean(ds.Tables[0].Rows[0][0]);
                }
            }
            return blnIsSuccess;
        }

        public long InsertOutbound(OutboundUpdateViewModel model, string strUserID, DateTime dtUpdated)
        {
            DataTable dTblOutbound = model.ToDataTable<OutboundUpdateViewModel>();
            dTblOutbound.Columns.Remove("HasError");

            return ExecuteUpdateSPRetLong("proc_InsertOutbound",
                                                "@tblOutbound", dTblOutbound,
                                                "@CreatedBy", strUserID,
                                                "@CreatedDateTime", dtUpdated);
        }

        public long InsertOutboundByList(List<OutboundUpdateViewModel> model, string strUserID, DateTime dtUpdated, bool blnIsTestingEmail = false)
        {
            DataTable dTblOutbound = model.ToDataTable<OutboundUpdateViewModel>();
            dTblOutbound.Columns.Remove("HasError");

            return ExecuteUpdateSPRetLong("proc_InsertOutbound",
                                                "@tblOutbound", dTblOutbound,
                                                "@CreatedBy", strUserID,
                                                "@CreatedDateTime", dtUpdated,
                                                "@IsTestingEmail", blnIsTestingEmail);
        }

        public long InsertEmailBlastByList(List<HCLEmailBlastUpdateViewModel> model, string strUserID, DateTime dtUpdated)
        {
            DataTable dTblEmailBlast = model.ToDataTable<HCLEmailBlastUpdateViewModel>();
            dTblEmailBlast.Columns.Remove("HasError");

            return ExecuteUpdateSPRetLong("proc_InsertEmailBlast",
                                                "@tblEmailBlast", dTblEmailBlast,
                                                "@CreatedBy", strUserID,
                                                "@CreatedDateTime", dtUpdated);
        }

        public long LockOutboundSegmentiseData(List<OutboundUpdateViewModel> model, string strUserID, DateTime dtUpdated)
        {
            DataTable dTblOutbound = model.ToDataTable<OutboundUpdateViewModel>();
            dTblOutbound.Columns.Remove("HasError");

            return ExecuteUpdateSPRetLong("proc_LockOutboundSegmentiseData",
                                                "@tblOutbound", dTblOutbound,
                                                "@CreatedBy", strUserID,
                                                "@CreatedDateTime", dtUpdated);
        }

        public long LockEmailBlastSegmentiseData(List<HCLEmailBlastUpdateViewModel> model, string strUserID, DateTime dtUpdated)
        {
            DataTable dTblEmailBlast = model.ToDataTable<HCLEmailBlastUpdateViewModel>();
            dTblEmailBlast.Columns.Remove("HasError");

            return ExecuteUpdateSPRetLong("proc_LockEmailBlastSegmentiseData",
                                                "@tblEmailBlast", dTblEmailBlast,
                                                "@CreatedBy", strUserID,
                                                "@CreatedDateTime", dtUpdated);
        }

        public long ReleaseOutboundSegmentiseData(List<OutboundUpdateViewModel> model, string strUserID, DateTime dtUpdated)
        {
            DataTable dTblOutbound = model.ToDataTable<OutboundUpdateViewModel>();
            dTblOutbound.Columns.Remove("HasError");

            return ExecuteUpdateSPRetLong("proc_ReleaseOutboundSegmentiseData",
                                                "@tblOutbound", dTblOutbound,
                                                "@CreatedBy", strUserID,
                                                "@CreatedDateTime", dtUpdated);
        }

        public long ReleaseEmailBlastSegmentiseData(List<HCLEmailBlastUpdateViewModel> model, string strUserID, DateTime dtUpdated)
        {
            DataTable dTblEmailBlast = model.ToDataTable<HCLEmailBlastUpdateViewModel>();
            dTblEmailBlast.Columns.Remove("HasError");

            return ExecuteUpdateSPRetLong("proc_ReleaseEmailBlastSegmentiseData",
                                                "@tblEmailBlast", dTblEmailBlast,
                                                "@CreatedBy", strUserID,
                                                "@CreatedDateTime", dtUpdated);
        }

        public void UpdateOutboundStatus(long lngOutboundID, int intStatusID, string strUserID, DateTime dtUpdated)
        {
            ExecuteSP("proc_UpdateOutboundStatus",
                                                "@OutboundID", lngOutboundID,
                                                "@StatusID", intStatusID,
                                                "@LastUpdatedBy", strUserID,
                                                "@LastUpdatedDateTime", dtUpdated);
        }

        public long UpdateOutboundReMSStatus(List<OutboundUpdateViewModel> vm, string ReMsTableName, int statusID, string strLastUpdatedBy, string strLastUpdateByName, DateTime dtLastUpdatedDateTime, int intReturnReasonID = 0, bool blnUpdateProfile = true)
        {
            DataTable dTblOutboundData = vm.ToDataTable<OutboundUpdateViewModel>();
            dTblOutboundData.Columns.Remove("HasError");

            return ExecuteUpdateSPRetLong("proc_UpdateOutboundReMSStatus", "@tblOutboundData", dTblOutboundData,
                                                     "@ReMsTableName", ReMsTableName,
                                                     "@StatusID", statusID,
                                                     "@LastUpdatedBy", strLastUpdatedBy,
                                                     "@LastUpdatedByName", strLastUpdateByName,
                                                     "@LastUpdatedDateTime", dtLastUpdatedDateTime,
                                                     "@ReturnReasonID", intReturnReasonID,
                                                     "@IsUpdateProfile", blnUpdateProfile
                                                     );

        }

        public long UpdateEmailBlastEmailDataStatus(List<HCLEmailBlastUpdateViewModel> vm, int statusID, string strLastUpdatedBy, string strLastUpdateByName, DateTime dtLastUpdatedDateTime, bool blnUpdateProfile = true)
        {
            DataTable dTblEmailBlast = vm.ToDataTable<HCLEmailBlastUpdateViewModel>();
            dTblEmailBlast.Columns.Remove("HasError");

            return ExecuteUpdateSPRetLong("proc_UpdateEmailBlastEmailDataStatus", "@tblEmailBlast", dTblEmailBlast,
                                                     "@StatusID", statusID,
                                                     "@LastUpdatedBy", strLastUpdatedBy,
                                                     "@LastUpdatedByName", strLastUpdateByName,
                                                     "@LastUpdatedDateTime", dtLastUpdatedDateTime,
                                                     "@IsUpdateProfile", blnUpdateProfile
                                                     );

        }

        public OutboundUser CheckReMsTableForManualOutboundAssign(string strRemsTableName, int intJenisKempenID, long lngRemsDataID)
        {
            OutboundUser result = new OutboundUser();
            List<object> lstParam = new List<object>(new object[] {
                                        "@RemsTableName",strRemsTableName,
                                        "@JenisKempenID", intJenisKempenID,
                                        "@RemsDataID",lngRemsDataID
                                       });

            DataSet ds = ExecuteSP("proc_GetOutboundManualAssignByLoginName", lstParam.ToArray());
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    result.ID = Convert.ToString(ds.Tables[0].Rows[0][0]);
                    result.DisplayName = Convert.ToString(ds.Tables[0].Rows[0][1]);
                    result.Email = Convert.ToString(ds.Tables[0].Rows[0][2]);
                }
            }

            return result;

        }

        public void UpdatedRemsTableForManualAssigned(string strRemsTableName, int intJenisKempenID, long lngRemsDataID, OutboundUser assignUser, OutboundUser CurrentLoginUser, string strRemark, DateTime dtUpdated)
        {
            ExecuteSP("proc_UpdateRemsTableForManualAssign",
                                   "@RemsTableName", strRemsTableName,
                                   "@JenisKempenID", intJenisKempenID,
                                   "@RemsDataID", lngRemsDataID,
                                   "@AssignBy", CurrentLoginUser.ID, "@AssignByName", CurrentLoginUser.DisplayName,
                                   "@AssignToID", assignUser.ID, "@AssignToName", assignUser.DisplayName, "@AssignToEmail", assignUser.Email,
                                    "@Remarks", strRemark,
                                    "@LastUpdatedTime", dtUpdated);
        }

        public DataSet GetOutboundHistoryListByID(string strSortExpr, int intPageIndex, int intPageSize, int intCampaignID)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", intPageIndex,
                                        "@PageSize", intPageSize,
                                        "@CampaignID", intCampaignID

                                    });

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

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

        public DataSet GetEmailBlastHistoryListByID(string strSortExpr, int intPageIndex, int intPageSize, int intCampaignID)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", intPageIndex,
                                        "@PageSize", intPageSize,
                                        "@CampaignID", intCampaignID

                                    });

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

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

        public OutboundHistoryViewModel GetOutboundHistoryByID(long outboundHistID)
        {
            return ExecuteSP<OutboundHistoryViewModel>("proc_GetOutboundHistoryByID", "@OutboundHistID", outboundHistID).FirstOrDefault();
        }

        public HCLEmailBlastHistoryViewModel GetEmailBlastHistoryByID(long lngEmailHistID)
        {
            return ExecuteSP<HCLEmailBlastHistoryViewModel>("proc_GetEmailBlastHistoryByID", "@EmailHistID", lngEmailHistID).FirstOrDefault();
        }

        public DataSet GetOutboundHistoryDetailListByID(string strSortExpr, int intPageIndex, int intPageSize, long outboundHistID)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", intPageIndex,
                                        "@PageSize", intPageSize,
                                        "@OutboundHistID", outboundHistID

                                    });

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

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

        public DataSet GetEmailBlastHistoryDetailListByID(string strSortExpr, int intPageIndex, int intPageSize, long lngEmailHistID)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", intPageIndex,
                                        "@PageSize", intPageSize,
                                        "@EmailHisID", lngEmailHistID

                                    });

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

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

        public int GetOutboundCountByTP(CountOutboundByTPViewModel model)
        {
            int intCount = 0;
            DataSet ds = ExecuteSP("proc_GetOutboundCountByTP", "@TPID", model.TPID, "@ReMSTableName", model.ReMSTableName, "@ReMSDataID", model.ReMSDataID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    intCount = (int)ds.Tables[0].Rows[0][0];
                }
            }
            return intCount;
        }


        public DataSet InsertEmailBlastBatch(HCLEmailBlastUploadParamViewModel model, string strLastUpdatedBy, string strLastUpdatedByName, DateTime dtLastUpdatedDateTime)
        {
            return ExecuteSP("proc_InsertEmailBlastBatch",
                                                "@JenisKempenID", model.JenisKempenID,
                                                "@FileName", model.AttachmentList[0].FileName,
                                                "@LastUpdatedBy", strLastUpdatedBy,
                                                "@LastUpdatedByName", strLastUpdatedByName,
                                                "@LastUpdatedDateTime", dtLastUpdatedDateTime);
        }

        public HCLEmailBlastDetailsViewModel GetEmailBlastDetails(int intJenisKempenID, long lngDataID)
        {
            DataSet ds = ExecuteSP("proc_GetEmailBlastDetails", "@JenisKempenID", intJenisKempenID, "@DataID", lngDataID);
            HCLEmailBlastDetailsViewModel vm = DataTableToList<HCLEmailBlastDetailsViewModel>(ds.Tables[0]).FirstOrDefault();

            if (ds.Tables.Count > 1)
                vm.TP = DataTableToList<TPViewModel>(ds.Tables[1]).FirstOrDefault();

            return vm;
        }

        public int UpdateEmailBlastDetailsHCL(EmailBlastDetailsHCLDetailsViewModel vm)
        {
            DataTable dTbl = vm.ToDataTable();
            dTbl.Columns.Remove("ChangeStamp");
            dTbl.Columns.Remove("JenisKempenID");

            int i = ExecuteUpdateSP("proc_UpdateEmailBlastDetailsHCL", "@tblEmailBlastDetails", dTbl,
                                                        "@LastUpdatedBy", vm.LastUpdatedBy,
                                                        "@LastUpdatedByName", vm.LastUpdatedByName,
                                                        "@LastUpdatedDateTime", vm.LastUpdatedDateTime
                                                        );
            return i;
        }

        public int InsertEmailBlastChangeHistory(List<ChangeHistoryUpdateViewModel> lst, string strUserName, string strUserID, DateTime dtUpdated)
        {
            DataTable dTbl = lst.ToDataTable<ChangeHistoryUpdateViewModel>();
            dTbl.Columns.Remove("IsChanged");

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

        public long DeleteEmailBlastAfterReject(long lngEmailDataID)
        {
            long i = ExecuteUpdateSP("proc_DeleteEmailBlastAfterReject",
                                      "@EmailDataID", lngEmailDataID);

            return i;
        }

        public long DeleteOutboundAfterReject(long lngDataID)
        {
            long i = ExecuteUpdateSP("proc_DeleteOutboundAfterReject",
                                      "@DataID", lngDataID);

            return i;
        }

        public HCLEmailBlastUpdateViewModel GetCurrentEmailBlastDetailStatus(long lngEmailDataID, int JenisKempenID)
        {
            return ExecuteSP<HCLEmailBlastUpdateViewModel>("proc_GetHCLEmailBlastDetailStatus", "@EmailDataID", lngEmailDataID, "@JenisKempenID", JenisKempenID).FirstOrDefault();
        }


        public OutboundUpdateViewModel GetCurrentOutboundDetailStatus(long lngDataID, int JenisKempenID)
        {
            return ExecuteSP<OutboundUpdateViewModel>("proc_GetOutboundDetailStatus", "@DataID", lngDataID, "@JenisKempenID", JenisKempenID).FirstOrDefault();
        }

        public DataSet GetNewReMSDataByBatchID(ReMSDataPagedListViewModel vm, string strLastUpdatedBy, string strLastUpdatedByName, DateTime dtLastUpdatedDateTime)
        {
            return ExecuteSP("proc_GetNewReMSDataByBatchID",
                                   "@BatchID", vm.BatchID,
                                   "@JenisKempenID", vm.JenisKempenID,
                                   "@LastUpdatedBy", strLastUpdatedBy,
                                   "@LastUpdatedByName", strLastUpdatedByName,
                                   "@LastUpdatedDateTime", dtLastUpdatedDateTime);
        }

        public DataSet GetNewEmailBlastDataByBatchID(int intBatchID, string strLastUpdatedBy, string strLastUpdatedByName, DateTime dtLastUpdatedDateTime)
        {
            return ExecuteSP("proc_GetNewEmailBlastDataByBatchID",
                                   "@BatchID", intBatchID,
                                   "@LastUpdatedBy", strLastUpdatedBy,
                                   "@LastUpdatedByName", strLastUpdatedByName,
                                   "@LastUpdatedDateTime", dtLastUpdatedDateTime);
        }

        public DataSet GetReMSDataDetailsByList(List<OutboundUpdateViewModel> model)
        {
            DataTable dTbl = model.ToDataTable<OutboundUpdateViewModel>();
            dTbl.Columns.Remove("HasError");

            return ExecuteSP("proc_GetReMSDataDetailsByList",
                                   "@tblOutbound", dTbl);
        }

        public DataSet GetEmailBlastDataDetailsByList(List<HCLEmailBlastUpdateViewModel> model)
        {
            DataTable dTbl = model.ToDataTable<HCLEmailBlastUpdateViewModel>();
            dTbl.Columns.Remove("HasError");

            return ExecuteSP("proc_GetEmailBlastDataDetailsByList",
                                   "@tblEmailBlast", dTbl);
        }

        public List<OutboundCancelVoiceQueueViewModel> GetCancelVoiceQueueList()
        {
            return ExecuteSP<OutboundCancelVoiceQueueViewModel>("proc_GetOutboundCancelVoiceQueue").ToList();
        }

        public void UpdateCancelVoiceQueue(OutboundCancelVoiceQueueViewModel model)
        {

            ExecuteSP("proc_UpdateOutboundCancelVoiceQueue",
                                  "@CancelVoiceQueueID", model.CancelVoiceQueueID,
                                  "@Status", model.Status,
                                  "@FailAttempt", model.FailAttempt,
                                  "@GSW_ERROR_NUMBER", model.GSW_ERROR_NUMBER,
                                  "@GSW_ERROR", model.GSW_ERROR,
                                  "@LastUpdatedBy", model.LastUpdatedBy,
                                  "@LastUpdatedDateTime", model.LastUpdatedDateTime);
        }

        public void DeleteCancelVoiceQueue()
        {
            ExecuteSP("proc_DeleteOutboundCancelVoiceQueue");
        }

        public DataSet UpdateCallingList(OutboundCallingListViewModel model)
        {
            return ExecuteSP("proc_UpdateCallingList",
                                  "@OutboundID", model.OutboundID,
                                  "@JenisKempenID", model.JenisKempenID,
                                  "@ReMSDataID", model.ReMSDataID,
                                  "@ReMSTableName", model.ReMSTableName,
                                  "@CallingListSchema", model.CallingListSchema,
                                  "@CancelFromCRM", model.CancelFromCRM,
                                  "@LastUpdatedBy", model.LastUpdatedBy,
                                  "@LastUpdatedDateTime", model.LastUpdatedDateTime);
        }

        public long CancelOutboundCampaignByID(OutboundCancelCampaignViewModel vm, string strLastUpdatedBy, string strLastUpdatedByName, DateTime dtLastUpdatedDateTime)
        {
            long i = ExecuteUpdateSP("proc_CancelOutboundCampaignByID",
                                      "@CampaignID", vm.CampaignID,
                                      "@HisID", vm.HisID,
                                      "@LastUpdatedBy", strLastUpdatedBy,
                                      "@LastUpdatedDateTime", dtLastUpdatedDateTime
                                      );
            return i;
        }

        public long CancelEmailBlastCampaignByID(OutboundCancelCampaignViewModel vm, string strLastUpdatedBy, string strLastUpdatedByName, DateTime dtLastUpdatedDateTime)
        {
            long i = ExecuteUpdateSP("proc_CancelEmailBlastCampaignByID",
                                      "@CampaignID", vm.CampaignID,
                                      "@HisID", vm.HisID,
                                      "@LastUpdatedBy", strLastUpdatedBy,
                                      "@LastUpdatedDateTime", dtLastUpdatedDateTime
                                      );
            return i;
        }

        public long UpdateCallingListByHisID(long lngHisID, int intJenisKempenID, string strLastUpdatedBy, string strLastUpdatedByName, DateTime dtLastUpdatedDateTime)
        {
            long i = ExecuteUpdateSP("proc_UpdateCallingListByHisID",
                                    "@HisID", lngHisID,
                                    "@JenisKempenID", intJenisKempenID,
                                    "@LastUpdatedBy", strLastUpdatedBy,
                                    "@LastUpdatedDateTime", dtLastUpdatedDateTime
                                    );
            return i;
        }      
    }
}
