using LHDN.CRM.BC.Common;
using LHDN.CRM.BC.Services;
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.Web;

namespace LHDN.CRM.BC.Repositories
{
    public class ActivityRepository : BaseRepository<CRMContext, Activity>
    {
        public ActivityViewModel GetActivity(long ActivityID)
        {
            ActivityViewModel clsAct = null;
            DataSet ds = ExecuteSP("proc_GetActivity", "@ActivityID", ActivityID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    clsAct = DataTableToList<ActivityViewModel>(ds.Tables[0]).FirstOrDefault();

                    if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
                    {
                        clsAct.EnableDMSIntegration = Convert.ToBoolean(ds.Tables[1].Rows[0]["EnableDMSIntegration"]);
                        clsAct.TotalTaskBlmSelesai = Convert.ToInt32(ds.Tables[1].Rows[0]["TotalTaskBlmSelesai"]);
                        clsAct.ManagerFuntionRoleID = ds.Tables[1].Rows[0]["ManagerFunctionRoleID"].ToString();
                    }
                }
            }

            return clsAct;
        }

        public List<AttachmentViewModel> GetActAttachment(long ActivityID)
        {

            List<AttachmentViewModel> lst = null;

            DataSet ds = ExecuteSP("proc_GetActAttachment", "@ActivityID", ActivityID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    lst = DataTableToList<AttachmentViewModel>(ds.Tables[0]).ToList();
                }
            }

            return lst;
        }

        public ActVerificationViewModel GetActVerification(long ActivityID, long TPID)
        {

            ActVerificationViewModel cls = null;

            DataSet ds = ExecuteSP("proc_GetActivityVerification", "@ActivityID", ActivityID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    cls = DataTableToList<ActVerificationViewModel>(ds.Tables[0]).FirstOrDefault();

                    cls.Spouse = cls.SpouseID == null ? null : Context.TPSpouse.Where(x => x.SpouseID == cls.SpouseID).FirstOrDefault();
                    cls.Shareholder = cls.ShareholderID == null ? null : Context.TPShareholder.Where(x => x.ShareholderID == cls.ShareholderID).FirstOrDefault();
                    cls.EjenCukai = cls.EjenCukaiID == null ? null : Context.TaxAgent.Where(x => x.EjenCukaiID == cls.EjenCukaiID).FirstOrDefault();
                    cls.Majikan = cls.MajikanID == null ? null : Context.TPMajikan.Where(x => x.TPID == TPID && x.STS_EMPL_REF_NO == cls.MajikanID).FirstOrDefault();
                }
            }




            return cls;
        }

        public List<ActAdditionalKategoriViewModel> GetActAdditionalKategori(long ActivityID)
        {

            List<ActAdditionalKategoriViewModel> cls = null;

            DataSet ds = ExecuteSP("proc_GetActAdditionalKategori", "@ActivityID", ActivityID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    cls = DataTableToList<ActAdditionalKategoriViewModel>(ds.Tables[0]).ToList();
                }
            }

            return cls;
        }

        public ActEmailViewModel GetActivityEmail(long ActivityID)
        {

            ActEmailViewModel cls = null;

            DataSet ds = ExecuteSP("proc_GetActivityEmail", "@ActivityID", ActivityID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    cls = DataTableToList<ActEmailViewModel>(ds.Tables[0]).FirstOrDefault();
                    cls.EmailContent = HttpUtility.HtmlDecode(cls.EmailContent);
                }
            }

            return cls;
        }

        public ActCFFViewModel GetActivityCFF(long ActivityID)
        {
            ActCFFViewModel cls = null;

            DataSet ds = ExecuteSP("proc_GetActivityCFF", "@ActivityID", ActivityID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    cls = DataTableToList<ActCFFViewModel>(ds.Tables[0]).FirstOrDefault();
                    cls.EmailContent = HttpUtility.HtmlDecode(cls.EmailContent);
                }
            }

            return cls;
        }

        public NotaTemudugaViewModel GetAllNotaTemuduga(long ActivityID)
        {
            NotaTemudugaViewModel cls = null;

            DataSet ds = ExecuteSP("proc_GetAllNotaTemuduga", "@CaseID", 0, "@ActivityID", ActivityID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    cls = DataTableToList<NotaTemudugaViewModel>(ds.Tables[0]).FirstOrDefault();
                    if (cls != null)
                        cls.NTUnit = DataTableToList<WorkflowUserViewModel>(ds.Tables[1]).FirstOrDefault();
                }
            }

            return cls;
        }

        public List<ActDocReceivedViewModel> GetActDocReceived(long ActivityID)
        {
            List<ActDocReceivedViewModel> lstActDocReceived = null;

            DataSet ds = ExecuteSP("proc_GetActDocReceived", "@ActivityID", ActivityID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    lstActDocReceived = DataTableToList<ActDocReceivedViewModel>(ds.Tables[0]).ToList();

                }
            }

            return lstActDocReceived;
        }

        public List<CaseKategoriDocReceivedViewModel> GetCaseKategoriDocReceived(long ActivityID)
        {
            List<CaseKategoriDocReceivedViewModel> lstCaseKategoriDocReceived = null;

            DataSet ds = ExecuteSP("proc_GetActDocReceivedForCase", "@CaseID", 0, "@ActID", ActivityID);

            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    var lstActDoc = DataTableToList<ActDocReceivedViewModel>(ds.Tables[0]).ToList();

                    //distint activity list
                    var lstDistinctActivity = lstActDoc.GroupBy(x => x.ActivityID).Select(grp => grp.First()).ToList();
                    lstCaseKategoriDocReceived = new List<CaseKategoriDocReceivedViewModel>();

                    foreach (var item in lstDistinctActivity)
                    {
                        CaseKategoriDocReceivedViewModel obj = new CaseKategoriDocReceivedViewModel
                        {
                            ActivityID = item.ActivityID,
                            ActRefNo = item.ActRefNo,
                            KategoriDocReceived = new List<KategoriDocReceived>()
                        };
                        lstCaseKategoriDocReceived.Add(obj);
                    }

                    //get each activity kat doc list
                    foreach (var item in lstCaseKategoriDocReceived)
                    {
                        var lstKatDoc = lstActDoc.Where(x => x.ActivityID == item.ActivityID).GroupBy(x => x.DocListID).Select(grp => grp.First()).ToList();
                        foreach (var katDocLstItem in lstKatDoc)
                        {
                            KategoriDocReceived obj = new KategoriDocReceived
                            {
                                DocListID = katDocLstItem.DocListID,
                                KategoriDocCode = katDocLstItem.KategoriDocCode,
                                KategoriDocName = katDocLstItem.KategoriDocName,
                                ActDocReceived = new List<ActDocReceivedViewModel>()
                            };
                            item.KategoriDocReceived.Add(obj);
                        }
                    }

                    //get each doc list act doc received
                    foreach (var item in lstCaseKategoriDocReceived)
                    {
                        foreach (var kategoriDocReceivedItem in item.KategoriDocReceived)
                        {
                            foreach (var lstActDocItem in lstActDoc)
                            {
                                if (lstActDocItem.ActivityID == item.ActivityID && lstActDocItem.DocListID == kategoriDocReceivedItem.DocListID)
                                    kategoriDocReceivedItem.ActDocReceived.Add(lstActDocItem);
                            }
                        }
                    }

                }
            }

            return lstCaseKategoriDocReceived;
        }

        public ActivityViewModel GetNewActivityByPrevActivity(long PrevActivityID)
        {
            ActivityViewModel clsAct = null;
            DataSet ds = ExecuteSP("proc_GetNewActivityByPrevActivity", "@ActivityID", PrevActivityID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    clsAct = DataTableToList<ActivityViewModel>(ds.Tables[0]).FirstOrDefault();
                }
            }

            return clsAct;
        }

        public ActCFFViewModel GetNewActivityOutBoundCFF(long PrevActivityID)
        {
            ActCFFViewModel cls = null;
            DataSet ds = ExecuteSP("proc_GetNewActivityOutBoundCFF", "@ActivityID", PrevActivityID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    cls = DataTableToList<ActCFFViewModel>(ds.Tables[0]).FirstOrDefault();
                }
            }

            return cls;
        }

        public byte[] GetChangeStampbyID(long ID)
        {
            byte[] ChangeStamp = null;

            DataSet ds = ExecuteSP("proc_GetChangeStamp", "@Table", (int)Constant.TableType.Activity, "@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 List<ActCatatanViewModel> GetAllActCatatan(long ActivityID)
        {
            List<ActCatatanViewModel> lstCatatan = new List<ActCatatanViewModel>();
            List<AttachmentViewModel> lstAttachment = null;

            DataSet ds = ExecuteSP("proc_GetAllActivityCatatan", "@ActivityID", ActivityID);
            
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    lstCatatan = DataTableToList<ActCatatanViewModel>(ds.Tables[0]).ToList();
                }
                if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
                {
                    lstAttachment = DataTableToList<AttachmentViewModel>(ds.Tables[1]).ToList();
                }

            }

            if (lstAttachment != null)
            {
                foreach (var item in lstCatatan)
                {

                    item.CatatanAttachmentList = lstAttachment.Where(r => r.ID == item.CatatanID).ToList();
                }
            }

            return lstCatatan;
        }

        public ActCatatanViewModel GetActCatatanByID(long lngActCatatanID)
        {
            ActCatatanViewModel clsCatatan = null;
            DataSet ds = ExecuteSP("proc_GetActCatatanByID", "@ActCatatanID", lngActCatatanID);

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

                    if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
                    {
                        if (clsCatatan != null)
                        {
                            var tmpList = DataTableToList<AttachmentViewModel>(ds.Tables[1]).ToList();
                            clsCatatan.CatatanAttachmentList = tmpList;
                        }
                    }
                }
            }
            return clsCatatan;
        }

        /// <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.Activity,
                                    "@CreatedByName", strUserName,
                                    "@CreatedBy", strUserID,
                                    "@CreatedDateTime", dtUpdated);
            return i;

        }

        public bool LinkUnlinkActivity(long ActivityID, long CaseID, bool IsLinkCase, string strUserName, string strUserID, DateTime dtUpdated, out ActivityViewModel clsNewAct, bool isDeleteCase)
        {

            DataSet ds = ExecuteSP("proc_LinkUnlinkActivity", "@ActivityID", ActivityID,
                                                  "@CaseID", CaseID,
                                                  "@IsLinkCase", IsLinkCase,
                                                  "@LastUpdatedBy", strUserID,
                                                  "@LastUpdatedByName", strUserName,
                                                  "@LastUpdatedDateTime", dtUpdated,
                                                  "@IsDeleteCase", isDeleteCase);
            bool blnIsSuccess = false;
            clsNewAct = null;

            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]);
                }
                if (ds.Tables.Count == 2)
                {
                    if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
                    {

                        clsNewAct = DataTableToList<ActivityViewModel>(ds.Tables[1]).FirstOrDefault();
                    }
                }
            }
            return blnIsSuccess;
        }

        public byte[] UpdateActivityStatus(long lngActivityID, int intActStatusID, int intActStatus1ID, int? intActStatus2ID, int? intActStatus3ID, string strUserName, string strUserID, DateTime dtUpdated, List<EntityIDViewModel> lstActivityID = null)
        {
            DataTable dbtlSubActivityID = Extensions.ToDataTable<EntityIDViewModel>();

            if (lstActivityID != null)
            {
                dbtlSubActivityID = lstActivityID.ToDataTable<EntityIDViewModel>();
            }

            DataSet ds = ExecuteSP("proc_UpdateActivityStatus", "@MainActivityID", lngActivityID,
                                                  "@ActStatusID", intActStatusID,
                                                  "@ActStatus1ID", intActStatus1ID,
                                                  "@ActStatus2ID", intActStatus2ID,
                                                  "@ActStatus3ID", intActStatus3ID,
                                                  "@LastUpdatedBy", strUserID,
                                                  "@LastUpdatedByName", strUserName,
                                                  "@LastUpdatedDateTime", dtUpdated,
                                                  "@tblSubActivityID", dbtlSubActivityID);
            byte[] changeStamp = null;
            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 bool DeleteActivity(long ActivityID, string strUserName, string strUserID, DateTime dtUpdated)
        {

            DataSet ds = ExecuteSP("proc_DeleteActivity",
                                    "@ActivityID", ActivityID,
                                    "@LastUpdatedBy", strUserID,
                                    "@LastUpdatedByName", strUserName,
                                    "@LastUpdatedDateTime", dtUpdated);
            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 DataTable InsertActivity(ActivityUpdateViewModel model)
        {

            DataTable dTblNota = Extensions.ToDataTable<NotaTemudugaViewModel>();
            if (model.Activity.NotaTemuduga != null)
            {
                if (model.Activity.NotaTemuduga.NTTarikhTerima.HasValue)
                    model.Activity.NotaTemuduga.NTTarikhTerima = model.Activity.NotaTemuduga.NTTarikhTerima.Value;
                dTblNota = model.Activity.NotaTemuduga.ToDataTable<NotaTemudugaViewModel>();
            }
            dTblNota.Columns.Remove("ID");
            dTblNota.Columns.Remove("NotaTemudugaID");
            dTblNota.Columns.Remove("NTCawTindakanName");
            dTblNota.Columns.Remove("NTUnitName");
            dTblNota.Columns.Remove("NTUnit");

            DataTable dTblActDocReceived = Extensions.ToDataTable<ActDocReceivedViewModel>();
            if (model.Activity.ActDocReceived != null)
            {
                int i;
                for (i = 0; i < model.Activity.ActDocReceived.Count; i++)
                {
                    if (model.Activity.ActDocReceived[i].TarikhDokumen.HasValue)
                        model.Activity.ActDocReceived[i].TarikhDokumen = model.Activity.ActDocReceived[i].TarikhDokumen.Value;
                }
                dTblActDocReceived = model.Activity.ActDocReceived.ToDataTable<ActDocReceivedViewModel>();
            }
            dTblActDocReceived.Columns.Remove("ActRefNo");

            DataTable dTblVerification = Extensions.ToDataTable<ActVerificationViewModel>();
            if (model.Activity.ActVerification != null)
            {
                dTblVerification = model.Activity.ActVerification.ToDataTable<ActVerificationViewModel>();
            }
            dTblVerification.Columns.Remove("Spouse");
            dTblVerification.Columns.Remove("Shareholder");
            dTblVerification.Columns.Remove("EjenCukai");
            dTblVerification.Columns.Remove("Majikan");

            DataTable dTblEmail = Extensions.ToDataTable<ActEmailViewModel>();
            if (model.Activity.ActEmail != null)
            {
                model.Activity.ActEmail.EmailContent = HttpUtility.HtmlEncode(model.Activity.ActEmail.EmailContent);
                if (model.Activity.ActEmail.EmailReceivedDate.HasValue)
                    model.Activity.ActEmail.EmailReceivedDate = model.Activity.ActEmail.EmailReceivedDate.Value;

                dTblEmail = model.Activity.ActEmail.ToDataTable<ActEmailViewModel>();
            }

            DataTable dTblCFF = Extensions.ToDataTable<ActCFFViewModel>();
            if (model.Activity.ActCFF != null)
            {
                model.Activity.ActCFF.EmailContent = HttpUtility.HtmlEncode(model.Activity.ActCFF.EmailContent);
                dTblCFF = model.Activity.ActCFF.ToDataTable<ActCFFViewModel>();
            }
            dTblCFF.Columns.Remove("CFFSubKategoriName");
            dTblCFF.Columns.Remove("CFFMainKategoriName");

            DataTable dTblAdditionalKategoris = Extensions.ToDataTable<ActAdditionalKategoriUpdateViewModel>();
            if (model.Activity.AdditionalKategoris != null)
            {
                dTblAdditionalKategoris = model.Activity.AdditionalKategoris.ToDataTable<ActAdditionalKategoriUpdateViewModel>();
            }

            if (model.Activity.PromiseToPayDate.HasValue)
                model.Activity.PromiseToPayDate = model.Activity.PromiseToPayDate.Value;

            if (model.Activity.LetterFaxReceived.HasValue)
                model.Activity.LetterFaxReceived = model.Activity.LetterFaxReceived.Value;

            DataTable dTblActivity = model.Activity.ToDataTable<ActivityDetailViewModel>();
            dTblActivity.Columns.Remove("NotaTemuduga");
            dTblActivity.Columns.Remove("ChangeStamp");
            dTblActivity.Columns.Remove("ActEmail");
            dTblActivity.Columns.Remove("ActVerification");
            dTblActivity.Columns.Remove("AdditionalKategoris");
            dTblActivity.Columns.Remove("ActDocReceived");
            dTblActivity.Columns.Remove("ActCFF");
            dTblActivity.Columns.Remove("Case");
            dTblActivity.Columns.Remove("ActAttachmentList");
            dTblActivity.Columns.Remove("TP");
            dTblActivity.Columns.Remove("ActCatatans");
            
            DataTable dTblActAttachment = Extensions.ToDataTable<AttachmentViewModel>();
            if (model.Activity.ActAttachmentList != null)
            {
                dTblActAttachment = model.Activity.ActAttachmentList.ToDataTable<AttachmentViewModel>();
            }
             
            dTblActAttachment.Columns.Remove("CatatanType");
            dTblActAttachment.Columns.Remove("FileType");
              
            DataSet ds = ExecuteSP("proc_InsertActivity", "@tblActivity", dTblActivity,
                                                   "@tblVerification", dTblVerification,
                                                   "@tblEmail", dTblEmail,
                                                   "@tblCFF", dTblCFF,
                                                   "@tblActAttachment", dTblActAttachment,
                                                   "@tblAdditionalKategoris", dTblAdditionalKategoris,
                                                   "@tblNotaTemuduga", dTblNota,
                                                   "@tblActDocReceived", dTblActDocReceived );

            DataTable dTblResult = null;

            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 UpdateActivity(ActivityUpdateViewModel model)
        {
            DataTable dTblResult = null;
            DataSet ds = new DataSet();

            DataTable dTblActivity = model.Activity.ToDataTable<ActivityDetailViewModel>();
            dTblActivity.Columns.Remove("NotaTemuduga");
            dTblActivity.Columns.Remove("ChangeStamp");
            dTblActivity.Columns.Remove("ActEmail");
            dTblActivity.Columns.Remove("ActVerification");
            dTblActivity.Columns.Remove("AdditionalKategoris");
            dTblActivity.Columns.Remove("ActDocReceived");
            dTblActivity.Columns.Remove("ActCFF");
            dTblActivity.Columns.Remove("Case");
            dTblActivity.Columns.Remove("ActAttachmentList");
            dTblActivity.Columns.Remove("TP");
            dTblActivity.Columns.Remove("ActCatatans");

            if (model.Activity.PromiseToPayDate.HasValue)
                model.Activity.PromiseToPayDate = model.Activity.PromiseToPayDate.Value;

            if (model.Activity.LetterFaxReceived.HasValue)
                model.Activity.LetterFaxReceived = model.Activity.LetterFaxReceived.Value;

            if (model.Activity.TarikhMulaAnsuran.HasValue)
                model.Activity.TarikhMulaAnsuran = model.Activity.TarikhMulaAnsuran.Value;

            ds = ExecuteSP("proc_UpdateActivity", "@tblActivity", dTblActivity,
                 "@IsCFFDraft", model.IsCFFDraft);

            DataTable dTblNota = Extensions.ToDataTable<NotaTemudugaViewModel>();
            if (model.Activity.NotaTemuduga != null)
            {
                if (model.Activity.NotaTemuduga.NTTarikhTerima.HasValue)
                    model.Activity.NotaTemuduga.NTTarikhTerima = model.Activity.NotaTemuduga.NTTarikhTerima.Value;
                dTblNota = model.Activity.NotaTemuduga.ToDataTable<NotaTemudugaViewModel>();

                dTblNota.Columns.Remove("ID");
                dTblNota.Columns.Remove("NotaTemudugaID");
                dTblNota.Columns.Remove("NTCawTindakanName");
                dTblNota.Columns.Remove("NTUnitName");
                dTblNota.Columns.Remove("NTUnit");

                ds = ExecuteSP("proc_UpdateActNotaTemuduga", "@tblActivity", dTblActivity,
                                        "@tblNotaTemuduga", dTblNota);
            }

            DataTable dTblActDocReceived = Extensions.ToDataTable<ActDocReceivedViewModel>();
            if (model.Activity.ActDocReceived != null)
            {
                int i;
                for (i = 0; i < model.Activity.ActDocReceived.Count; i++)
                {
                    if (model.Activity.ActDocReceived[i].TarikhDokumen.HasValue)
                        model.Activity.ActDocReceived[i].TarikhDokumen = model.Activity.ActDocReceived[i].TarikhDokumen.Value;
                }
                dTblActDocReceived = model.Activity.ActDocReceived.ToDataTable<ActDocReceivedViewModel>();
                dTblActDocReceived.Columns.Remove("ActRefNo");

                ds = ExecuteSP("proc_UpdateActDocReceived", "@tblActivity", dTblActivity,
                                        "@tblActDocReceived", dTblActDocReceived);
            }

            DataTable dTblEmail = Extensions.ToDataTable<ActEmailViewModel>();

            if (model.Activity.ActEmail != null)
            {
                model.Activity.ActEmail.EmailContent = HttpUtility.HtmlEncode(model.Activity.ActEmail.EmailContent);

                if (model.Activity.ActEmail.EmailReceivedDate.HasValue)
                    model.Activity.ActEmail.EmailReceivedDate = model.Activity.ActEmail.EmailReceivedDate.Value;

                dTblEmail = model.Activity.ActEmail.ToDataTable<ActEmailViewModel>();

                ds = ExecuteSP("proc_UpdateActEmail", "@tblActivity", dTblActivity,
                                        "@tblEmail", dTblEmail);
            }

            DataTable dTblCFF = Extensions.ToDataTable<ActCFFViewModel>();
            if (model.Activity.ActCFF != null)
            {
                model.Activity.ActCFF.EmailContent = HttpUtility.HtmlEncode(model.Activity.ActCFF.EmailContent);
                dTblCFF = model.Activity.ActCFF.ToDataTable<ActCFFViewModel>();
                dTblCFF.Columns.Remove("CFFSubKategoriName");
                dTblCFF.Columns.Remove("CFFMainKategoriName");

                ds = ExecuteSP("proc_UpdateActCFF", "@tblActivity", dTblActivity,
                                                         "@tblCFF", dTblCFF);
            }

            DataTable dTblAdditionalKategoris = Extensions.ToDataTable<ActAdditionalKategoriUpdateViewModel>();
            if (model.Activity.AdditionalKategoris != null)
            {
                dTblAdditionalKategoris = model.Activity.AdditionalKategoris.ToDataTable<ActAdditionalKategoriUpdateViewModel>();
                ds = ExecuteSP("proc_UpdateActAdditionalKategoris", "@tblActivity", dTblActivity,
                                        "@tblAdditionalKategoris", dTblAdditionalKategoris);
            }

            DataTable dTblVerification = Extensions.ToDataTable<ActVerificationViewModel>();
            if (model.Activity.ActVerification != null)
            {
                dTblVerification = model.Activity.ActVerification.ToDataTable<ActVerificationViewModel>();

                dTblVerification.Columns.Remove("Spouse");
                dTblVerification.Columns.Remove("Shareholder");
                dTblVerification.Columns.Remove("EjenCukai");
                dTblVerification.Columns.Remove("Majikan");

                ds = ExecuteSP("proc_UpdateActVerification", "@tblActivity", dTblActivity,
                                        "@tblVerification", dTblVerification);
            }

            #region comment out
            //DataTable dTblActivity = model.Activity.ToDataTable<ActivityDetailViewModel>();
            //dTblActivity.Columns.Remove("NotaTemuduga");
            //dTblActivity.Columns.Remove("ChangeStamp");
            //dTblActivity.Columns.Remove("ActEmail");
            //dTblActivity.Columns.Remove("ActVerification");
            //dTblActivity.Columns.Remove("AdditionalKategoris");
            //dTblActivity.Columns.Remove("ActDocReceived");
            //dTblActivity.Columns.Remove("ActCFF");
            //dTblActivity.Columns.Remove("Case");
            //dTblActivity.Columns.Remove("ActAttachmentList");
            //dTblActivity.Columns.Remove("TP");
            //dTblActivity.Columns.Remove("ActCatatans");
            #endregion comment out

            DataTable dTblActAttachment = Extensions.ToDataTable<AttachmentViewModel>();
            if (model.Activity.ActAttachmentList != null)
            {
                dTblActAttachment = model.Activity.ActAttachmentList.ToDataTable<AttachmentViewModel>();
                dTblActAttachment.Columns.Remove("CatatanType");
                dTblActAttachment.Columns.Remove("FileType");

                ds = ExecuteSP("proc_UpdateActAttachment", "@tblActivity", dTblActivity,
                                "@tblActAttachment", dTblActAttachment);
            }

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

            #region comment out
            //DataSet ds = ExecuteSP("proc_UpdateActivity", "@tblActivity", dTblActivity,
            //                                         "@tblVerification", dTblVerification,
            //                                         "@tblEmail", dTblEmail,
            //                                         "@tblCFF", dTblCFF,
            //                                         "@tblActAttachment", dTblActAttachment,
            //                                         "@tblAdditionalKategoris", dTblAdditionalKategoris,
            //                                         "@tblNotaTemuduga", dTblNota,
            //                                         "@tblActDocReceived", dTblActDocReceived, 
            //                                         "@IsCFFDraft", model.IsCFFDraft);

            //DataTable dTblResult = null;

            //if (ds != null && ds.Tables.Count > 0)
            //{
            //    if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            //    {
            //        dTblResult = ds.Tables[0];
            //    }
            //}
            #endregion comment out

            return dTblResult;

        }

        public void UpdateActCatatan(ActCatatanViewModel model, string strUserName, string strUserID, DateTime dtUpdated)
        {
            DataTable dTblCatatanAttachment = Extensions.ToDataTable<AttachmentViewModel>();
            if (model.CatatanAttachmentList != null)
            {
                dTblCatatanAttachment = model.CatatanAttachmentList.ToDataTable<AttachmentViewModel>();
            }
            dTblCatatanAttachment.Columns.Remove("CatatanType");
            dTblCatatanAttachment.Columns.Remove("FileType");
            DataSet ds = ExecuteSP("proc_UpdateActCatatan",
                                                "@tblCatatanAttachments", dTblCatatanAttachment,
                                                "@ActivityID", model.ActivityID,
                                                "@ActCatatanID", model.CatatanID,
                                                "@Catatan", model.Catatan,
                                                "@LastUpdatedBy", strUserID,
                                                "@LastUpdatedByName", strUserName,
                                                "@LastUpdatedDateTime", dtUpdated);

        }

        public long InsertActCatatan(ActCatatanViewModel model, string strUserName, string strUserID, DateTime dtUpdated)
        {
            DataTable dTblCatatanAttachment = Extensions.ToDataTable<AttachmentViewModel>();
            if (model.CatatanAttachmentList != null)
            {
                dTblCatatanAttachment = model.CatatanAttachmentList.ToDataTable<AttachmentViewModel>();
            }
            dTblCatatanAttachment.Columns.Remove("CatatanType");
            dTblCatatanAttachment.Columns.Remove("FileType");
            DataSet ds = ExecuteSP("proc_InsertActCatatan",
                                                "@tblCatatanAttachments", dTblCatatanAttachment,
                                                "@ActivityID", model.ActivityID,
                                                "@Catatan", model.Catatan,
                                                "@CreatedBy", strUserID,
                                                "@CreatedByName", strUserName,
                                                "@CreatedDateTime", dtUpdated);
            long lngActCatatanID = 0;
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    lngActCatatanID = Convert.ToInt64(ds.Tables[0].Rows[0][0]);
                }
            }

            return lngActCatatanID;
        }

        public void DeleteActCatatan(long lngActCatatanID)
        {
            ExecuteSP("proc_DeleteActCatatan",
                      "@ActCatatanID", lngActCatatanID);

        }

        public DataTable GetAttachment(string type, long AttachmentID)
        {
            DataSet ds = ExecuteSP("proc_GetAttachment",
                                               "@Type", type,
                                             "@AttachmentID", AttachmentID);
            DataTable dtblResult = null;
            //byte[] fileContent = null;
            //string fileName = string.Empty;
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    dtblResult = ds.Tables[0];
                    //fileName = (byte[])ds.Tables[0].Rows[0][0];
                    //fileContent = (byte[])ds.Tables[0].Rows[0][1];
                }
            }
            //"@CatatanAttachment", dTblAttachment);

            return dtblResult;
        }

        public TempAttachmentViewModel InsertTempAttachment(TempAttachmentViewModel model)
        {
            DataSet ds = ExecuteSP("proc_InsertTempAttachment", "@FileName", model.FileName,
                                                   "@FileContentType", model.FileContentType,
                                                   "@FileContent", model.FileContent,
                                                   "@CreatedDateTime", model.CreatedDateTime);
            long lngTempAttachmentID = 0;
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    lngTempAttachmentID = Convert.ToInt64(ds.Tables[0].Rows[0][0]);
                }
            }
            model.TempAttachmentID = lngTempAttachmentID;
            //Context.TempAttachment.Add(model);
            //Context.SaveChanges();

            model.FileContent = null;
            return model;
        }

        public void DeleteTempAttachment(long TempAttachmentID)
        {
            ExecuteSP("proc_DeleteTempAttachment", "@TempAttachmentID", TempAttachmentID);
        }

        public void InsertActSession(List<ActSession> lst)
        {
            foreach (var item in lst)
            {
                Context.ActSession.Add(item);
            }
            Context.SaveChanges();
        }

        public DataSet GetActivityListByID(string strSortExpr, int intPageIndex, int intPageSize, long TPID, long CaseID, long ReMSDataID, int JenisKempenID, long EmailDataID,
                                           int ActChannelID, int AliranID, int ActStatusID, int Kategori1ID, int Kategori2ID, int Kategori3ID, string ActivityNo)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", intPageIndex,
                                        "@PageSize", intPageSize,
                                        "@TPID", TPID,
                                        "@CaseID", CaseID,
                                        "@ReMSDataID", ReMSDataID,
                                        "@JenisKempenID", JenisKempenID,
                                        "@EmailDataID",EmailDataID,
                                        "@ActChannelID ", ActChannelID,
                                        "@AliranID", AliranID,
                                        "@ActStatusID", ActStatusID,
                                        "@Kategori1ID", Kategori1ID,
                                        "@Kategori2ID", Kategori2ID,
                                        "@Kategori3ID", Kategori3ID,
                                        "@ActivityNo", ActivityNo
                                    });

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

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

        public string GetAllCatatanInString(long caseID, long actID = 0)
        {
            string strCatatan = string.Empty;
            DataSet ds = ExecuteSP("proc_GetAllCatatanInString", "@CaseID", caseID,
                                                            "@ActID", actID);
            if (ds != null)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    strCatatan = row["Result"].ToString();
                }
            }
            return strCatatan;
        }

        //TODO : Change parameter name CreatedBy to AssignedToLogin
        public DataSet GetPendingActivityByName(string CreatedBy, PendingActivityPagedListViewModel paging)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", paging.PageIndex,
                                        "@PageSize", paging.Length,
                                        "@CreatedBy", CreatedBy});

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

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

        }

        public DataTable GetCFFSummaryAttachment(long activityid)
        {
            DataTable dtbl = null;
            DataSet ds = ExecuteSP("proc_GetCFFSummaryAttachment", "@ActivityID", activityid);
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    dtbl = ds.Tables[0];

                }
            }

            return dtbl;
        }

        public DataSet GetPendingActByManagerPool(PendingActivityPagedListViewModel paging, string strLoginID)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", paging.PageIndex,
                                        "@PageSize", paging.Length,
                                        "@UnitId", paging.UnitId,
                                        "@UserLoginID", strLoginID});

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

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



        public DataSet GetAssignedInboundCFFDraftActivity(string strAssignedToLogin, PendingActivityPagedListViewModel paging)
        {
            List<object> lstParam = new List<object>(new object[] {
                                         "@AssignedToLogin", strAssignedToLogin,
                                        "@PageIndex", paging.PageIndex,
                                        "@PageSize", paging.Length});

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

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

        public void InsertSlipKedatanganAction(PrintActionViewModel model)
        {
            ExecuteSP("proc_InsertActSlipKedatanganAction", "@TPID", model.TPID,
                                                            "@ActivityID", model.ActID,
                                                            "@PrintedBy", model.PrintedBy,
                                                            "@PrintedDateTime", model.PrintedDateTime);
        }

        public RouteToAgentViewModel GetRouteToAgentData(long lngActID)
        {
            return ExecuteSP<RouteToAgentViewModel>("proc_GetRouteToAgentData", "@ActivityID", lngActID).FirstOrDefault();
        }

        public void UpdateInteractionID(string strInteractionID, long lngActID)
        {
            ExecuteSP("proc_UpdateInteractionID", "@InteractionID", strInteractionID,
                                                  "@ActivityID", lngActID,
                                                  "@UpdatedDateTime", DateTime.Now);
        }

        public void InsertOutboundCancelVoiceQueue(OutboundCancelVoiceQueueViewModel model)
        {
            ExecuteSP("proc_InsertOutboundCancelVoiceQueue", "@ActivityID", model.ActivityID,
                                                  "@GSW_PHONE", model.GSW_PHONE,
                                                  "@GSW_CAMPAIGN_NAME", model.GSW_CAMPAIGN_NAME,
                                                  "@GSW_CALLING_LIST", model.GSW_CALLING_LIST,
                                                  "@UpdatedBy", model.LastUpdatedBy,
                                                  "@UpdatedDateTime", DateTime.Now,
                                                  "@OutboundID", model.OutboundID,
                                                  "@JenisKempenID", model.JenisKempenID,
                                                  "@ReMSDataID", model.ReMSDataID,
                                                  "@ReMSTableName", model.ReMSTableName);
        }

        public void InsertCFFQueue(CRMUpdateFieldViewModel model, string strUserID)
        {
            if (model.CFFFormIDList.Count > 0)
            {
                foreach (var intCffID in model.CFFFormIDList)
                {
                    ExecuteSP("proc_InsertCFFQueue", "@CFFFormID", intCffID,
                                                      "@CRMStatus", model.CRMStatus,
                                                      "@CRMRemarks", model.CRMRemarks,
                                                      "@CRMLastUpdatedDate", model.CRMLastUpdatedDate,
                                                      "@UpdatedBy", strUserID,
                                                      "@UpdatedDateTime", model.CRMLastUpdatedDate);
                }
            }
        }

        public List<CFFQueueViewModel> GetCFFQueue()
        {
            return ExecuteSP<CFFQueueViewModel>("proc_GetCFFQueue").ToList();
        }

        public void UpdateCFFQueue(CFFQueueViewModel model)
        {
            ExecuteSP("proc_UpdateCFFQueue", "@CFFQueueID", model.CFFQueueID,
                                             "@Status", model.Status,
                                             "@FailAttempt", model.FailAttempt,
                                             "@LastUpdatedBy", model.LastUpdatedBy,
                                             "@LastUpdatedDateTime", model.LastUpdatedDateTime);
        }

        public List<RouteToAgentQueueViewModel> GetRouteToAgentQueue()
        {
            return ExecuteSP<RouteToAgentQueueViewModel>("proc_GetRouteToAgentQueue").ToList();
        }

        public void InsertRouteToAgentQueue(RouteToAgentQueueViewModel model, string strUserID)
        {
            ExecuteSP("proc_InsertRouteToAgentQueue", "@ActivityID", model.ActivityID,
                                                      "@UpdatedBy", strUserID,
                                                      "@UpdatedDateTime", DateTime.Now);
        }

        public void UpdateRouteToAgentQueue(RouteToAgentQueueViewModel model)
        {
            ExecuteSP("proc_UpdateRouteToAgentQueue", "@RouteToAgentQueueID", model.RouteToAgentQueueID,
                                                     "@Status", model.Status,
                                                     "@FailAttempt", model.FailAttempt,
                                                     "@LastUpdatedBy", model.LastUpdatedBy,
                                                     "@LastUpdatedDateTime", model.LastUpdatedDateTime);
        }

        public List<ActivityViewModel> GetAllOpenActivityByCaseID(long lngCaseID, int intAliranID, int intActChannelID)
        {
            List<ActivityViewModel> lst = null;

            DataSet ds = ExecuteSP("proc_GetAllOpenActivityByCaseID", "@CaseID", lngCaseID,
                                    "@AliranID", intAliranID,
                                    "@ActChannelID", intActChannelID);
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    lst = DataTableToList<ActivityViewModel>(ds.Tables[0]).ToList();
                }
            }

            return lst;
        }

        public void InsertUpdateCFFError(long lngCFFFormID, string strUserID, string strStatus)
        {
            ExecuteSP("proc_InsertUpdateCFFError", "@CFFFormID", lngCFFFormID,
                                                      "@UpdatedBy", strUserID,
                                                      "@UpdatedDateTime", DateTime.Now,
                                                      "@Status", strStatus);
        }

        public List<CFFErrorViewModel> GetCFFError()
        {
            return ExecuteSP<CFFErrorViewModel>("proc_GetCFFError").ToList();
        }

        public List<ActivityDetailViewModel> GetAllRecentOrphanActivity()
        {
            return ExecuteSP<ActivityDetailViewModel>("proc_GetAllRecentOrphanActivity").ToList();

        }

        public void UpdateOrphanActivity(List<OrphanUpdateViewModel> lstOrphan, string strLastUpdatedByName, string strLastUpdatedBy, DateTime dtUpdated) {

            DataTable dTblOrphan = Extensions.ToDataTable<OrphanUpdateViewModel>();
            if (lstOrphan != null)
            {
                dTblOrphan = lstOrphan.ToDataTable<OrphanUpdateViewModel>();
            }
            ExecuteSP("proc_UpdateTPForOrphanActivity"
                        , "@tblOrphan", dTblOrphan
                        , "@LastUpdatedBy", strLastUpdatedBy
                        , "@LastUpdatedByName", strLastUpdatedByName
                        , "@LastUpdatedDateTime", dtUpdated);
        }
    }
}
