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

namespace LHDN.CRM.BC.Repositories
{
    public class CaseRepository : BaseRepository<CRMContext, Case>
    {
        private Repositories.WorkflowRepository repoWorkflow = new Repositories.WorkflowRepository();

        public CaseViewModel GetCase(long CaseID, UserInfoViewModel currentUser = null)
        {
            DataSet ds = ExecuteSP("proc_GetCase", "@CaseID", CaseID);
            //CaseViewModel vmCase = new CaseViewModel();
            CaseViewModel vmCase = DataTableToList<CaseViewModel>(ds.Tables[0]).FirstOrDefault();

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

                    if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
                        vmCase.TP = DataTableToList<TPViewModel>(ds.Tables[1]).FirstOrDefault();

                    if (ds.Tables[2] != null && ds.Tables[2].Rows.Count > 0)
                    {
                        vmCase.TotalActivity = Convert.ToInt32(ds.Tables[2].Rows[0]["TotalActivity"]);
                        vmCase.TotalActBlmSelesai = Convert.ToInt32(ds.Tables[2].Rows[0]["TotalActBlmSelesai"]);
                        vmCase.TotalAliranMasuk = Convert.ToInt32(ds.Tables[2].Rows[0]["TotalAliranMasuk"]);
                        vmCase.TotalAliranKeluar = Convert.ToInt32(ds.Tables[2].Rows[0]["TotalAliranKeluar"]);
                        vmCase.TotalTaskBlmSelesai = Convert.ToInt32(ds.Tables[2].Rows[0]["TotalTaskBlmSelesai"]);
                        vmCase.ManagerFuntionRoleID = ds.Tables[2].Rows[0]["ManagerFuntionRoleID"].ToString();
                        vmCase.JenisPelangganID = ds.Tables[2].Rows[0]["JenisPelangganID"].ToIntSafe();
                        vmCase.IsPelangganPembayarSama = ds.Tables[2].Rows[0]["IsPelangganPembayarSama"] != DBNull.Value ? Convert.ToBoolean(ds.Tables[2].Rows[0]["IsPelangganPembayarSama"]) : vmCase.IsPelangganPembayarSama;
                        vmCase.TPNoCukai = ds.Tables[2].Rows[0]["TPNoCukai"].ToString();
                        vmCase.CanCloseCase = currentUser.RolesObj.Any(x => x.RoleCode == Constant.Authentication.Cases.CloseCase);
                        vmCase.TP.PelangganNama = ds.Tables[2].Rows[0]["PelangganNama"].ToString();
                        vmCase.IsShowPenerimaanDocTab = Convert.ToBoolean(ds.Tables[2].Rows[0]["IsShowPenerimaanDocTab"]);
                        vmCase.CanCreateUpdateCaseCatatan = currentUser.RolesObj.Any(x => x.RoleCode == Constant.Authentication.Cases.CreateUpdateCatatan);
                        vmCase.CFFFirstActivityID = Convert.IsDBNull(ds.Tables[2].Rows[0]["CFFFirstActivityID"]) ? 0 : Convert.ToInt64(ds.Tables[2].Rows[0]["CFFFirstActivityID"]);
                    }
                }

                vmCase.CatatanLst = GetAllCaseCatatan(CaseID, 0);

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

                List<CaseKategoriDocReceivedViewModel> lst = new List<CaseKategoriDocReceivedViewModel>();
                ds = ExecuteSP("proc_GetActDocReceivedForCase", "@CaseID", CaseID, "@ActID", 0);

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

                        //distint activity list
                        var activityLst = actDocLst.GroupBy(x => x.ActivityID).Select(grp => grp.First()).ToList();

                        foreach (var a in activityLst)
                        {
                            CaseKategoriDocReceivedViewModel obj = new CaseKategoriDocReceivedViewModel
                            {
                                ActivityID = a.ActivityID,
                                ActRefNo = a.ActRefNo,
                                KategoriDocReceived = new List<KategoriDocReceived>()
                            };
                            lst.Add(obj);
                        }

                        //get each activity kat doc list
                        foreach (var a in lst)
                        {
                            var katDocLst = actDocLst.Where(x => x.ActivityID == a.ActivityID).GroupBy(x => x.DocListID).Select(grp => grp.First()).ToList();
                            List<KategoriDocReceived> _lstKatDoc = new List<KategoriDocReceived>();
                            foreach (var b in katDocLst)
                            {
                                KategoriDocReceived obj = new KategoriDocReceived
                                {
                                    DocListID = b.DocListID,
                                    KategoriDocCode = b.KategoriDocCode,
                                    KategoriDocName = b.KategoriDocName,
                                    ActDocReceived = new List<ActDocReceivedViewModel>()
                                };
                                a.KategoriDocReceived.Add(obj);
                            }
                        }

                        //get each doc list act doc received
                        foreach (var a in lst)
                        {
                            foreach (var b in a.KategoriDocReceived)
                            {
                                foreach (var c in actDocLst)
                                {

                                    if (c.ActivityID == a.ActivityID && c.DocListID == b.DocListID)
                                    {
                                        b.ActDocReceived.Add(c);
                                    }
                                }
                            }
                        }
                    }
                    vmCase.AllDocReceived = lst;
                }
                vmCase.WorkflowDetail = repoWorkflow.GetExistingWorkflowRecord(vmCase.WorkFlowStatusID);
                if (vmCase.WorkflowDetail != null)
                {
                    WorkflowUserViewModel assignedUser = new WorkflowUserViewModel();
                    assignedUser.DisplayName = vmCase.WorkflowDetail.AssignToName;
                    assignedUser.ID = vmCase.WorkflowDetail.AssignToLogin;
                    assignedUser.Email = vmCase.WorkflowDetail.AssignToEmail;
                    assignedUser.AssignToType = vmCase.WorkflowDetail.AssignToTypeID;
                    vmCase.WorkflowDetail.AssignTo = assignedUser;
                    if (assignedUser.AssignToType == 20) // Assigned Unit
                    {
                        // Get Unit manager
                        var objWfUser = vmCase.WorkflowDetail.wfUser.Where(s => s.AssignToLogin == currentUser.LoginID).FirstOrDefault();
                        if (objWfUser != null)
                        {
                            if ((objWfUser.AssignToLogin.ToLower() == currentUser.LoginID.ToLower()) &&
                                (vmCase.WorkflowDetail.AssignBy.ToLower() == currentUser.LoginID.ToLower()))
                            {
                                vmCase.WorkflowDetail.IsCurrentLogin = true;
                                vmCase.WorkflowDetail.IsOtherLogin = true;
                            }
                            else if ((objWfUser.AssignToLogin.ToLower() == currentUser.LoginID.ToLower()))
                            {
                                vmCase.WorkflowDetail.IsCurrentLogin = true;
                                vmCase.WorkflowDetail.IsOtherLogin = false;
                            }
                            else if ((vmCase.WorkflowDetail.AssignBy.ToLower() == currentUser.LoginID.ToLower()))
                            {
                                vmCase.WorkflowDetail.IsCurrentLogin = false;
                                vmCase.WorkflowDetail.IsOtherLogin = true;
                            }
                            else
                            {
                                vmCase.WorkflowDetail.IsCurrentLogin = false;
                                vmCase.WorkflowDetail.IsOtherLogin = false;
                            }
                        }// Current view for Assignee
                        else if (vmCase.WorkflowDetail.AssignBy.ToLower() == currentUser.LoginID.ToLower())
                        {
                            vmCase.WorkflowDetail.IsCurrentLogin = false;
                            vmCase.WorkflowDetail.IsOtherLogin = true;
                        }
                        else // Third person view
                        {
                            vmCase.WorkflowDetail.IsCurrentLogin = false;
                            vmCase.WorkflowDetail.IsOtherLogin = false;
                        }
                    }
                    else if (assignedUser.AssignToType == 21)  //Assigned User
                    {
                        if ((vmCase.WorkflowDetail.AssignToLogin.ToLower() == currentUser.LoginID.ToLower()) &&
                                (vmCase.WorkflowDetail.AssignBy.ToLower() == currentUser.LoginID.ToLower()))
                        {
                            vmCase.WorkflowDetail.IsCurrentLogin = true;
                            vmCase.WorkflowDetail.IsOtherLogin = true;
                        }
                       else  if (vmCase.WorkflowDetail.AssignToLogin.ToLower() == currentUser.LoginID.ToLower())
                        {
                            vmCase.WorkflowDetail.IsCurrentLogin = true;
                            vmCase.WorkflowDetail.IsOtherLogin = false;

                            var managerLoginId = currentUser.Manager != null ? currentUser.Manager.ID : null; //Get Manager LoginId
                            
                            if (managerLoginId != null)
                            {
                                assignedUser.ManagerId = managerLoginId != null ? managerLoginId : "";
                                assignedUser.keyword = "";
                                var managerDetail = GetWorkFlowAllUserOrUnit(assignedUser);
                                var objManagerDetail = managerDetail.Where(s => s.ID == assignedUser.ManagerId).FirstOrDefault();
                                if (objManagerDetail != null)
                                {
                                    assignedUser.DisplayName = objManagerDetail.DisplayName;
                                    assignedUser.ID = objManagerDetail.ID;
                                    assignedUser.Email = objManagerDetail.Email;
                                }
                            }
                            else
                            {
                                vmCase.WorkflowDetail.AssignToTypeID = 0;
                            }
                        }
                        else if (vmCase.WorkflowDetail.AssignBy.ToLower() == currentUser.LoginID.ToLower())
                        {
                            vmCase.WorkflowDetail.IsCurrentLogin = false;
                            vmCase.WorkflowDetail.IsOtherLogin = true;
                        }
                        else if (vmCase.WorkflowDetail.AssignToLogin.ToLower() != currentUser.LoginID.ToLower() &&
                                  vmCase.WorkflowDetail.AssignBy.ToLower() != currentUser.LoginID.ToLower())
                        {
                            vmCase.WorkflowDetail.IsCurrentLogin = false;
                            vmCase.WorkflowDetail.IsOtherLogin = false;
                        }
                    }
                }
                //if (vmCase.WorkflowDetail != null)
                //{
                //    WorkflowUserViewModel assignedUser = new WorkflowUserViewModel();
                //    assignedUser.DisplayName = vmCase.WorkflowDetail.AssignToName;
                //    assignedUser.ID = vmCase.WorkflowDetail.AssignToLogin;
                //    assignedUser.Email = vmCase.WorkflowDetail.AssignToEmail;
                //    assignedUser.AssignToType = vmCase.WorkflowDetail.AssignToTypeID;
                //    vmCase.WorkflowDetail.AssignTo = assignedUser;

                //    if (assignedUser.AssignToType == 20) // Assigned Unit
                //    {
                //        if (vmCase.WorkflowDetail.wfUser != null)
                //        {
                //            var objWfUser = vmCase.WorkflowDetail.wfUser.Where(s => s.AssignToLogin == currentUser.LoginID).FirstOrDefault();


                //            var blnIsAssignor = vmCase.WorkflowDetail.AssignBy == currentUser.LoginID;
                //            if (objWfUser != null)
                //            {
                //                // if user is manager of the unit
                //                // manager role is allow to edit if the case is assigned to unit
                //                if ((objWfUser != null && objWfUser.AssignToLogin.ToLower() == currentUser.LoginID.ToLower()))
                //                {
                //                    vmCase.WorkflowDetail.IsCurrentLogin = true;
                //                    vmCase.WorkflowDetail.IsOtherLogin = false;
                //                }
                //                else if (objWfUser.AssignToLogin.ToLower() != currentUser.LoginID.ToLower() &&
                //                          objWfUser.AssignBy.ToLower() != currentUser.LoginID.ToLower())
                //                {
                //                    vmCase.WorkflowDetail.IsCurrentLogin = false;
                //                    vmCase.WorkflowDetail.IsOtherLogin = true;
                //                }
                //            }
                //            else
                //            {
                //                if (vmCase.WorkflowDetail.AssignBy.ToLower() == currentUser.LoginID.ToLower())
                //                {
                //                    vmCase.WorkflowDetail.IsCurrentLogin = true;
                //                    vmCase.WorkflowDetail.IsOtherLogin = false;
                //                }
                //                else
                //                {
                //                    vmCase.WorkflowDetail.IsCurrentLogin = false;
                //                    vmCase.WorkflowDetail.IsOtherLogin = true;
                //                }
                //            }
                //        }
                //    }
                //    else if (assignedUser.AssignToType == 21)  //Assigned User
                //    {
                //        if (vmCase.WorkflowDetail.AssignToLogin.ToLower() == currentUser.LoginID.ToLower())
                //        {

                //            var managerLoginId = currentUser.Manager != null ? currentUser.Manager.ID : null; //Get Manager LoginId

                //            //var managerLoginId = currentUser.Manager.ID;
                //            if (managerLoginId != null)
                //            {
                //                assignedUser.ManagerId = managerLoginId != null ? managerLoginId : "";
                //                assignedUser.keyword = "";
                //                var managerDetail = GetWorkFlowAllUserOrUnit(assignedUser);
                //                var objManagerDetail = managerDetail.Where(s => s.ID == assignedUser.ManagerId).FirstOrDefault();
                //                if (objManagerDetail != null)
                //                {
                //                    assignedUser.DisplayName = objManagerDetail.DisplayName;
                //                    assignedUser.ID = objManagerDetail.ID;
                //                    assignedUser.Email = objManagerDetail.Email;
                //                }
                //            }
                //            else
                //            {
                //                vmCase.WorkflowDetail.AssignToTypeID = 0;
                //            }

                //            vmCase.WorkflowDetail.IsCurrentLogin = true;
                //            vmCase.WorkflowDetail.IsOtherLogin = false;

                //        }
                //        else if (vmCase.WorkflowDetail.AssignBy.ToLower() == currentUser.LoginID.ToLower())
                //        {
                //            vmCase.WorkflowDetail.IsCurrentLogin = true;
                //        }
                //        else if (vmCase.WorkflowDetail.AssignToLogin.ToLower() != currentUser.LoginID.ToLower() &&
                //                  vmCase.WorkflowDetail.AssignBy.ToLower() != currentUser.LoginID.ToLower())
                //        {
                //            vmCase.WorkflowDetail.IsOtherLogin = true;
                //        }

                //    }
                //}
            }

            if (vmCase != null) //IsUnitManager?
            {
                bool blnIsUnitManager = false;
                string[] lstRoles = vmCase.ManagerFuntionRoleID.Split("||");
                foreach (var role in lstRoles)
                {
                    if (currentUser.RoleGroups.Any(x => x.FunctionalRoleID == Convert.ToInt32(role)))
                    {
                        blnIsUnitManager = true;
                        break;
                    }
                }
                vmCase.IsUnitManager = blnIsUnitManager;
            }
            return vmCase;
        }

        public CaseViewModel GetCaseSummary(long id)
        {
            DataSet ds = ExecuteSP("proc_GetCase", "@CaseID", id);
            CaseViewModel vmCase = new CaseViewModel();
            if (vmCase != null)
            {
                if (ds != null)
                {
                    if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                    {
                        vmCase = DataTableToList<CaseViewModel>(ds.Tables[0]).FirstOrDefault();
                    }
                }
            }
            return vmCase;
        }

        public List<CaseCatatanViewModel> GetAllCaseCatatan(long caseID, long actID = 0)
        {
            List<CaseCatatanViewModel> lstCatatan = new List<CaseCatatanViewModel>();
            List<AttachmentViewModel> lstAttachment = null;

            DataSet ds = ExecuteSP("proc_GetAllCaseCatatan", "@CaseID", caseID,
                                                             "@ActID", actID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    lstCatatan = DataTableToList<CaseCatatanViewModel>(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 string GetAllCatatanInString(long caseID, long actID = 0)
        {
            string catatanStr = string.Empty;
            DataSet ds = ExecuteSP("proc_GetAllCatatanInString", "@CaseID", caseID,
                                                            "@ActID", actID);
            if (ds != null)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    catatanStr = row["Result"].ToString();
                }
            }
            return catatanStr;
        }

        public DataSet GetOpenCase(string strSortExpr, int intPageIndex, int intPageSize, long TPID)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", intPageIndex,
                                        "@PageSize", intPageSize,
                                        "@TPID", TPID
                                    });

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

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

        public CaseViewModel GetCaseForActivity(long CaseID)
        {

            CaseViewModel cls = null;
            DataSet ds = ExecuteSP("proc_GetCaseForActivity", "@CaseID", CaseID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    cls = DataTableToList<CaseViewModel>(ds.Tables[0]).FirstOrDefault();
                }
            }

            return cls;
        }

        public ReMSBackOfficeQueueViewModel GetBackOfficeFromCase(long lngCaseID)
        {

            ReMSBackOfficeQueueViewModel cls = null;
            DataSet ds = ExecuteSP("proc_GetReMSBackOfficeForCase", "@CaseID", lngCaseID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    cls = DataTableToList<ReMSBackOfficeQueueViewModel>(ds.Tables[0]).FirstOrDefault();
                }
            }

            return cls;
        }

        public void InsertBackOfficeQueue(ReMSBackOfficeQueueViewModel model)
        {
            DataTable dTblBackOffice = Extensions.ToDataTable<ReMSBackOfficeQueueViewModel>();
            if (model != null)
            {
                dTblBackOffice = model.ToDataTable<ReMSBackOfficeQueueViewModel>();
            }
            DataSet ds = ExecuteSP("proc_InsertReMSTaskBackOfficeQueue",
                                                "@tblBackOfficeQueue", dTblBackOffice);
            //long lngCaseCatatanID = 0;
            //if (ds != null && ds.Tables.Count > 0)
            //{
            //    if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
            //    {
            //        lngCaseCatatanID = Convert.ToInt64(ds.Tables[0].Rows[0][0]);
            //    }
            //}

            //return lngCaseCatatanID;
        }


        public CaseCatatanViewModel GetActCatatanByID(long lngCaseCatatanID)
        {
            CaseCatatanViewModel clsCatatan = null;
            DataSet ds = ExecuteSP("proc_GetCaseCatatanByID", "@CaseCatatanID", lngCaseCatatanID);

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


        public long InsertCaseCatatan(CaseCatatanViewModel 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_InsertCaseCatatan",
                                                "@tblCatatanAttachments", dTblCatatanAttachment,
                                                "@CaseID", model.CaseID,
                                                "@Catatan", model.Catatan,
                                                "@CreatedBy", strUserID,
                                                "@CreatedByName", strUserName,
                                                "@CreatedDateTime", dtUpdated);
            long lngCaseCatatanID = 0;
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    lngCaseCatatanID = Convert.ToInt64(ds.Tables[0].Rows[0][0]);
                }
            }

            return lngCaseCatatanID;
        }

        public void UpdateCaseCatatan(CaseCatatanViewModel 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_UpdateCaseCatatan",
                                                "@tblCatatanAttachments", dTblCatatanAttachment,
                                                "@CaseID", model.CaseID,
                                                "@CaseCatatanID", model.CatatanID,
                                                "@Catatan", model.Catatan,
                                                "@LastUpdatedBy", strUserID,
                                                "@LastUpdatedByName", strUserName,
                                                "@LastUpdatedDateTime", dtUpdated);

        }

        public void DeleteCaseCatatan(long lngCaseCatatanID)
        {
            ExecuteSP("proc_DeleteCaseCatatan",
                      "@CaseCatatanID", lngCaseCatatanID);

        }

        public DataTable GetAttachment(string type, long AttachmentID)
        {
            DataSet ds = ExecuteSP("proc_GetAttachment",
                                             "@Type", type,
                                             "@AttachmentID", AttachmentID);
            DataTable data = new DataTable();
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    data = ds.Tables[0];
                }
            }
            return data;
        }

        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 DataTable UpdateCase(CaseUpdateViewModel caseUpdateVM)
        {
            DataTable dTblNota = Extensions.ToDataTable<NotaTemudugaViewModel>();
            if (caseUpdateVM.Case.NotaTemuduga != null)
            {
                if (caseUpdateVM.Case.NotaTemuduga.NTTarikhTerima.HasValue)
                    caseUpdateVM.Case.NotaTemuduga.NTTarikhTerima = caseUpdateVM.Case.NotaTemuduga.NTTarikhTerima.Value;
                dTblNota = caseUpdateVM.Case.NotaTemuduga.ToDataTable<NotaTemudugaViewModel>();
            }

            dTblNota.Columns["ID"].ColumnName = "CaseID";
            dTblNota.Columns.Remove("NotaTemudugaID");
            dTblNota.Columns.Remove("NTCawTindakanName");
            dTblNota.Columns.Remove("NTUnitName");
            dTblNota.Columns.Remove("NTUnit");

            DataTable dTblCase = caseUpdateVM.Case.ToDataTable<CaseDetailViewModel>();
            dTblCase.Columns.Remove("NotaTemuduga");
            dTblCase.Columns.Remove("ChangeStamp");
            //dTblCase.Columns.Remove("AssignedToEmail");


            DataSet ds = ExecuteSP("proc_UpdateCase", "@tblCase", dTblCase,
                                                    "@tblCaseNotaTemuduga", dTblNota,
                                                    "@LastUpdatedBy", caseUpdateVM.Case.LastUpdatedBy,
                                                    "@LastUpdatedByName", caseUpdateVM.Case.LastUpdatedByName,
                                                    "@LastUpdatedDateTime", caseUpdateVM.Case.LastUpdatedDateTime,
                                                    "@LastUpdatedEmail", caseUpdateVM.Case.AssignedToEmail);

            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 List<long> GetCFFListByCaseID(long lngCaseID)
        {
            List<long> lstCFFFormID = new List<long>();
            DataSet ds = ExecuteSP("proc_GetCFFIDByCaseID", "@CaseID", lngCaseID);
            if (ds != null)
            {
                if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                {
                    lstCFFFormID = (from row in ds.Tables[0].AsEnumerable()
                                    select Convert.ToInt64(row[0])
                                   ).ToList();
                }
            }
            return lstCFFFormID;
        }

        public byte[] ReopenCase(EntityUpdateViewModel model, string strUserName, string strUserID, DateTime dtUpdated)
        {
            DataSet ds = ExecuteSP("proc_ReopenCase", "@CaseID", model.CaseID,
                                        "@CaseStatusLevel1ID", model.CaseStatusLevel1ID,
                                        "@CaseStatusLevel2ID", model.CaseStatusLevel2ID,
                                        "@CaseStatusLevel3ID", model.CaseStatusLevel3ID,
                                        "@LastUpdatedBy", strUserID,
                                        "@LastUpdatedByName", strUserName,
                                        "@LastUpdatedDateTime", dtUpdated);

            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 DeleteCase(long CaseID, string strUserID, string strUserName, DateTime dtUpdated, out DataTable dtblLinkedActivity)
        {
            DataSet ds = ExecuteSP("proc_DeleteCase", "@CaseID", CaseID,
                                    "@LastUpdatedBy", strUserID,
                                    "@LastUpdatedByName", strUserName,
                                    "@LastUpdatedDateTime", dtUpdated);
            bool blnIsSuccess = false;
            dtblLinkedActivity = 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 (blnIsSuccess)
                {
                    if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
                    {
                        dtblLinkedActivity = ds.Tables[1];
                    }
                }
            }
            return blnIsSuccess;
        }

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

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

        }

        public DataTable InsertCaseWorkflow(CaseWorkflowUpdateViewModel caseWorkflow, WorkflowUserViewModel assignedUser, DateTime now)
        {
            DataSet ds = ExecuteSP("proc_InsertCaseWorkflowAction", "@CaseID", caseWorkflow.Workflow.CaseID,
                                                "@AssignToTypeID", assignedUser.AssignToType,
                                                "@AssignToLogin", assignedUser.ID,
                                                "@AssignToName", assignedUser.DisplayName,
                                                "@AssignToEmail", assignedUser.Email,
                                                "@ManagerFunctionRoleID", assignedUser.ManagerFunctionRoleID,
                                                "@WFStatusID", caseWorkflow.Workflow.WFStatusID,
                                                "@StartDate", now,
                                                "@Remarks", caseWorkflow.Workflow.Remarks,
                                                "@AssignBy", caseWorkflow.Workflow.AssignBy,
                                                "@AssignByName", caseWorkflow.Workflow.AssignByName,
                                                "@CreatedBy", caseWorkflow.Workflow.CreatedBy,
                                                "@CreatedDateTime", now,
                                                "@LastUpdatedBy", caseWorkflow.Workflow.LastUpdatedBy,
                                                "@LastUpdatedDateTime", now);

            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 InsertCase(CaseUpdateViewModel caseUpdateVM)
        {
            DataTable dTblNota = Extensions.ToDataTable<NotaTemudugaViewModel>();
            if (caseUpdateVM.Case.NotaTemuduga != null)
            {
                if (caseUpdateVM.Case.NotaTemuduga.NTTarikhTerima.HasValue)
                    caseUpdateVM.Case.NotaTemuduga.NTTarikhTerima = caseUpdateVM.Case.NotaTemuduga.NTTarikhTerima.Value;
                dTblNota = caseUpdateVM.Case.NotaTemuduga.ToDataTable<NotaTemudugaViewModel>();
            }
            dTblNota.Columns["ID"].ColumnName = "CaseID";
            dTblNota.Columns.Remove("NotaTemudugaID");
            dTblNota.Columns.Remove("NTCawTindakanName");
            dTblNota.Columns.Remove("NTUnitName");
            dTblNota.Columns.Remove("NTUnit");

            DataTable dTblCase = caseUpdateVM.Case.ToDataTable<CaseDetailViewModel>();
            dTblCase.Columns.Remove("NotaTemuduga");
            dTblCase.Columns.Remove("ChangeStamp");
            //dTblCase.Columns.Remove("AssignedToEmail");

            DataSet ds = ExecuteSP("proc_InsertCase", "@tblCase", dTblCase,
                                                    "@tblCaseNotaTemuduga", dTblNota,
                                                    "@LastUpdatedBy", caseUpdateVM.Case.LastUpdatedBy,
                                                    "@LastUpdatedByName", caseUpdateVM.Case.LastUpdatedByName,
                                                    "@LastUpdatedDateTime", caseUpdateVM.Case.LastUpdatedDateTime,
                                                    "@LastUpdatedEmail", caseUpdateVM.Case.AssignedToEmail);

            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 long InsertWorkflowHistory(CasesWorkflowActionViewModel OriCaseWorkflowData, DateTime dtUpdated)
        {
            long i = ExecuteUpdateSP("proc_InsertWorkFlowHistory", "@CaseID", OriCaseWorkflowData.CaseID,
                                                             "@ActID", 0,
                                                            "@AssignBy", OriCaseWorkflowData.AssignBy,
                                                            "@AssignByName", OriCaseWorkflowData.AssignByName,
                                                            "@WFStatusID", OriCaseWorkflowData.WFStatusID,
                                                            "@StartDate", OriCaseWorkflowData.StartDate,
                                                            "@CompletedDate", DateTime.Now,
                                                            "@DecisionID", OriCaseWorkflowData.DecisionID,
                                                            "@Remarks", OriCaseWorkflowData.Remarks,
                                                            "@CloseRemarks", OriCaseWorkflowData.CloseRemarks,
                                                            "CreatedDateTime", dtUpdated
                                                            );

            return i;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="CaseID"></param>
        /// <returns></returns>
        public CasesWorkflowActionViewModel GetExistingWorkflowRecord(long CaseID)
        {
            DataSet ds = ExecuteSP("proc_GetCaseWorkflowDetail", "@CaseID", CaseID);
            CasesWorkflowActionViewModel Workflow = DataTableToList<CasesWorkflowActionViewModel>(ds.Tables[0]).FirstOrDefault();
            if (Workflow != null)
            {
                if (ds != null)
                {
                    if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
                    {
                        Workflow = DataTableToList<CasesWorkflowActionViewModel>(ds.Tables[0]).FirstOrDefault();
                    }

                    if (ds.Tables[1] != null && ds.Tables[1].Rows.Count > 0)
                        Workflow.wfUser = DataTableToList<CasesWorkflowActionViewModel>(ds.Tables[1]).ToList();
                }
            }

            return Workflow;
        }

        public CaseWorkflowAssignToUnitIDViewModel GetWorkflowAssignToValue(CaseWorkflowAssignToUnitIDViewModel Kategory)
        {
            return ExecuteSP<CaseWorkflowAssignToUnitIDViewModel>("proc_GetWorkflowAssignToUnitID", "@Kategori1ID", Kategory.Kategori1,
                                                                  "@Kategori2ID", Kategory.Kategori2,
                                                                  "@Kategori3ID", Kategory.Kategori3,
                                                                  "@TPID", Kategory.TPID).FirstOrDefault();
        }

        public void UpdateCaseTPInfo(long lgCaseID)
        {
            ExecuteSP("proc_UpdateCaseTPInfo", "@CaseID", lgCaseID);
        }

        public List<WorkflowUserViewModel> GetWorkFlowAllUserOrUnit(WorkflowUserViewModel datainfo)
        {
            var data = ExecuteSP<WorkflowUserViewModel>("proc_GetWorkflowSearchUserOrUnit", "@keyword", datainfo.keyword,
                                               "@AssignToType", datainfo.AssignToType,
                                               "@ManagerLoginId", datainfo.ManagerId).ToList();

            return data;
        }

        public DataSet GetCaseListByID(string strSortExpr, int intPageIndex, int intPageSize, long TPID, int Kategori1ID, int Kategori2ID, int Kategori3ID)
        {
            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", intPageIndex,
                                        "@PageSize", intPageSize,
                                        "@TPID", TPID,
                                        "@Kategori1ID", Kategori1ID,
                                        "@Kategori2ID", Kategori2ID,
                                        "@Kategori3ID", Kategori3ID

                                    });

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

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

        public byte[] GetChangeStampbyID(int intTableType, long ID)
        {
            byte[] changeStamp = null;
            DataSet ds = ExecuteSP("proc_GetChangeStamp", "@Table", intTableType, "@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 DataSet GetDashboardMyCreatedCase(string CreatedBy, CaseBelumSelesaiAssignToDashboardViewModel 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_GetDashboardMyCreatedCase", lstParam.ToArray());

        }

        public DataSet GetDashboardMyPendingCase(string CreatedBy, CaseBelumSelesaiAssignToDashboardViewModel 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_GetDashboardMyPendingCase", lstParam.ToArray());

        }

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

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

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

        public List<CaseForTaskViewModel> GetAllCaseBelumSelesaiByAssignToLogin()
        {
            return ExecuteSP<CaseForTaskViewModel>("proc_GetAllCaseBlmSelesaiByAssignToLogin").ToList();
        }

        public DataSet GetAllCaseBelumSelesaiDetail(DateTime assignDate, string AssignToNameUser)
        {
            return ExecuteSP("proc_GetAllCaseBlmSelesaiForNotifcation", "@CaseAssignedDate", assignDate, "@AssignToName", AssignToNameUser);
        }

        public void UpdateCaseKategoriByCFFSubKategori(int intKategori1ID, string strLastUpdatedBy, string strLastUpdatedByName, DateTime dtLastUpdatedDateTime, long lngCaseID)
        {
            ExecuteSP("proc_UpdateCaseKategoriByCFFSubKategori", "@Kategori1ID", intKategori1ID,
                                                                        "@LastUpdatedBy", strLastUpdatedBy,
                                                                        "@LastUpdatedByName", strLastUpdatedByName,
                                                                        "@LastUpdatedDateTime", dtLastUpdatedDateTime,
                                                                        "@CaseID", lngCaseID);
        }

        public List<ActivityDetailViewModel> GetAllRecentOrphanCase()
        {
            return ExecuteSP<ActivityDetailViewModel>("proc_GetAllRecentOrphanCase").ToList();

        }

        public void UpdateOrphanCase(List<OrphanUpdateViewModel> lstOrphan, string strLastUpdatedByName, string strLastUpdatedBy, DateTime dtUpdated)
        {
            DataTable dTblOrphan = Extensions.ToDataTable<OrphanUpdateViewModel>();
            if (lstOrphan != null)
            {
                dTblOrphan = lstOrphan.ToDataTable<OrphanUpdateViewModel>();
            }
            ExecuteSP("proc_UpdateTPForOrphanCase", "@tblOrphan", dTblOrphan
                        , "@LastUpdatedBy", strLastUpdatedBy
                        , "@LastUpdatedByName", strLastUpdatedByName
                        , "@LastUpdatedDateTime", dtUpdated);
        }

        public DataSet GetCaseBulkAssignListing(CaseAssignmentViewModel paging)
        {
            DataSet ds = new DataSet();

            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", paging.PageIndex,
                                        "@PageSize", paging.Length,
                                        "@AssignBy", paging.AssignBy,
                                        "@KategoriID", paging.Kategori1ID,
                                        "@UnitTypeID", paging.AssignToTypeID,
                                        "@LoginID", paging.LoginID,
                                        "@RefNo", paging.RefNo,
                                        "@Kategori2ID",paging.Kategori2ID,
                                        "@Kategori3ID",paging.Kategori3ID
                                    });

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

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

        public DataSet GetCaseReassignListing(CaseAssignmentViewModel paging)
        {
            DataSet ds = new DataSet();

            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", paging.PageIndex,
                                        "@PageSize", paging.Length,
                                        "@AssignBy", paging.AssignBy,
                                        "@AssignTo", paging.AssignTo,
                                        "@KategoriID", paging.Kategori1ID,
                                        "@UnitTypeID", paging.AssignToTypeID,
                                        "@LoginID", paging.LoginID,
                                        "@RefNo", paging.RefNo
                                    });

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

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

    }

}