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 SearchRepository : BaseRepository<CRMContext, TaxPayer>
    {
        /// <summary>
        /// @tableType value should be 1 - TaxPayer, 2 - Case, 3 - Activity
        /// </summary>
        /// <param name="tabletype"></param>
        /// <returns></returns>
        public List<SearchViewsViewModel> GetSearchViewsDDL(int tableType, long intUserProfileID)
        {
            return ExecuteSP<SearchViewsViewModel>("proc_GetSearchSavedViewNames", 
                                "@TableType", tableType,
                                "@UserprofileID", intUserProfileID
                                ).ToList();
        }

        public List<SearchViewDetailViewModel> GetSearchViewDetailList(int intTableType)
        { 
            return ExecuteSP<SearchViewDetailViewModel>("proc_GetSearchSavedViewDetails", "@TableType", intTableType).ToList();
        }

        public DataSet GetSearchTPResult(string strSortExpr, int intPageIndex, int intPageSize, int viewID,
                                            string jenisPengenalanID, string noPengenalan, string nama, string rujukanNo,
                                            bool isExact, bool isSavedView, bool isAdvancedView, int tableType,
                                            List<AdvanceRulesViewModel> lstRules,
                                            bool blnIsDynamicDisplayColumns,
                                            List<DisplayColumnViewModel> lstDisplayColumns,
                                            List<RulesSortingViewModel> lstFieldsSorting)
        {

            //lstRules = Utilities.GenerateRowIDForAdvanceRules(lstRules);
            DataSet ds = new DataSet();
            ds = Utilities.GenerateAdvanceRulesViewTables(Utilities.GenerateRowIDForAdvanceRules(lstRules));

            DataTable dtblDisplayCol = (new List<DisplayColumnViewModel>()).ToDataTable<DisplayColumnViewModel>();
            DataTable dtblFieldsSorting = (new List<RulesSortingViewModel>()).ToDataTable<RulesSortingViewModel>();

            if (lstDisplayColumns != null) dtblDisplayCol = lstDisplayColumns.ToDataTable<DisplayColumnViewModel>();
            if (lstFieldsSorting != null) dtblFieldsSorting = lstFieldsSorting.ToDataTable<RulesSortingViewModel>();

            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", intPageIndex,
                                        "@PageSize", intPageSize,
                                        "@ViewID", viewID,
                                        "@JenisPengenalanID", jenisPengenalanID,
                                        "@NoPengenalan", noPengenalan,
                                        "@Nama", nama,
                                        "@RujukanNo", rujukanNo,
                                        "@IsExact", isExact,
                                        "@IsSavedView", isSavedView,
                                        "@IsAdvancedView", isAdvancedView,
                                        "@view", ds.Tables[0],
                                        "@RuleGroup", ds.Tables[1],
                                        "@FieldValue",  ds.Tables[2],
                                        "@IsDynamic", blnIsDynamicDisplayColumns,
                                        "@displayColumn",dtblDisplayCol,
                                        "@FieldSorting",dtblFieldsSorting
                                    });

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

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

        public List<SearchSavedViewViewModel> GetSavedViewColumns(int viewID, int tableType , bool blnIsDynamic, List<DisplayColumnViewModel> lstDisplayCol)
        {
            DataTable dtblDisplayCol = (new List<DisplayColumnViewModel>()).ToDataTable<DisplayColumnViewModel>();

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

            dtblDisplayCol.TableName = "DisplayColumn";
            return ExecuteSPDynamic<SearchSavedViewViewModel>("proc_GetSearchSavedViewColumns",
                                                    "@ViewID", viewID,
                                                    "@TableType", tableType,
                                                    "@IsDynamic", blnIsDynamic,
                                                    "@DisplayColumn", dtblDisplayCol  ).ToList();
        }

        public DataSet GetSearchResultAct(string strSortExpr, int intPageIndex, int intPageSize, int viewID,
            string jenisPengenalanID, string noPengenalan, string nama, string rujukanNo, bool isExact, bool isSavedView,
            string actRefNo, string actChannelID, int isOprhan, int statusID, bool isQuickWidget, string CurrentUserLoginID, bool isAdvancedView, int tableType,
                                            List<AdvanceRulesViewModel> lstRules,
                                            bool blnIsDynamicDisplayColumns,
                                            List<DisplayColumnViewModel> lstDisplayColumns,
                                            List<RulesSortingViewModel> lstFieldsSorting)
        {


            DataSet ds = new DataSet();
            ds = Utilities.GenerateAdvanceRulesViewTables(Utilities.GenerateRowIDForAdvanceRules(lstRules));

            DataTable dtblDisplayCol = (new List<DisplayColumnViewModel>()).ToDataTable<DisplayColumnViewModel>();
            DataTable dtblFieldsSorting = (new List<RulesSortingViewModel>()).ToDataTable<RulesSortingViewModel>();

            if (lstDisplayColumns != null) dtblDisplayCol = lstDisplayColumns.ToDataTable<DisplayColumnViewModel>();
            if (lstFieldsSorting != null) dtblFieldsSorting = lstFieldsSorting.ToDataTable<RulesSortingViewModel>();

            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", intPageIndex,
                                        "@PageSize", intPageSize,
                                        "@ViewID", viewID,
                                        "@JenisPengenalanID", jenisPengenalanID,
                                        "@NoPengenalan", noPengenalan,
                                        "@Nama", nama,
                                        "@RujukanNo", rujukanNo,
                                        "@IsExact", isExact,
                                        "@IsSavedView", isSavedView,
                                        "@ActRefNo", actRefNo,
                                        "@IsOprhan", isOprhan,
                                        "@ActChannelID", actChannelID,
                                        "@StatusID", statusID,
                                        "@IsQuickWidget", isQuickWidget,
                                        "@Username",CurrentUserLoginID,
                                        "@IsAdvancedView", isAdvancedView,
                                        "@view", ds.Tables[0],
                                        "@RuleGroup", ds.Tables[1],
                                        "@FieldValue",  ds.Tables[2],
                                        "@IsDynamic", blnIsDynamicDisplayColumns,
                                        "@displayColumn",dtblDisplayCol,
                                        "@FieldSorting",dtblFieldsSorting
                                    });

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

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

        public DataSet GetSearchResultCase(string strSortExpr, int intPageIndex, int intPageSize, int viewID, string jenisPengenalanID,
            string noPengenalan, string nama, string rujukanNo, bool isExact, bool isSavedView, string caseRefNo, int statusID,
            int isOprhan, string kategori1ID, bool IsQuickWidget, string CurrentUserLoginID, bool IsCaseToday, bool isAdvancedView, int tableType,
                                            List<AdvanceRulesViewModel> lstRules, bool blnIsDynamicDisplayColumns,
                                            List<DisplayColumnViewModel> lstDisplayColumns,
                                            List<RulesSortingViewModel> lstFieldsSorting)
        {
            DataSet ds = new DataSet();
            ds = Utilities.GenerateAdvanceRulesViewTables(Utilities.GenerateRowIDForAdvanceRules(lstRules));

            DataTable dtblDisplayCol = (new List<DisplayColumnViewModel>()).ToDataTable<DisplayColumnViewModel>();
            DataTable dtblFieldsSorting = (new List<RulesSortingViewModel>()).ToDataTable<RulesSortingViewModel>();

            if (lstDisplayColumns != null) dtblDisplayCol = lstDisplayColumns.ToDataTable<DisplayColumnViewModel>();
            if (lstFieldsSorting != null) dtblFieldsSorting = lstFieldsSorting.ToDataTable<RulesSortingViewModel>();

            List<object> lstParam = new List<object>(new object[] {
                                        "@PageIndex", intPageIndex,
                                        "@PageSize", intPageSize,
                                        "@ViewID", viewID,
                                        "@JenisPengenalanID", jenisPengenalanID,
                                        "@NoPengenalan", noPengenalan,
                                        "@Nama", nama,
                                        "@RujukanNo", rujukanNo,
                                        "@IsExact", isExact,
                                        "@IsSavedView", isSavedView,
                                        "@CaseRefNo", caseRefNo,
                                        "@StatusID", statusID,
                                        "@IsOprhan", isOprhan,
                                        "@kategori1ID", kategori1ID,
                                        "@IsQuickWidget", IsQuickWidget,
                                        "@Username",CurrentUserLoginID,
                                        "@IsCaseToday",IsCaseToday,
                                        "@IsAdvancedView", isAdvancedView,
                                        "@view", ds.Tables[0],
                                        "@RuleGroup", ds.Tables[1],
                                        "@FieldValue",  ds.Tables[2],
                                        "@IsDynamic", blnIsDynamicDisplayColumns,
                                        "@displayColumn",dtblDisplayCol,
                                        "@FieldSorting",dtblFieldsSorting
                                    });

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

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


        public int UpdateAdvRulesView( AdvancedRulesMainViewModel advRuleViewVM, List<AdvanceRulesViewModel> advRuleVM,
                                          List<RuleGroupViewModel> advRuleGroupVM, List<FieldValueViewModel> advRuleGroupFieldsVM,
                                          List<DisplayColumnViewModel> lstDisplayCol, List<RulesSortingViewModel> lstFieldsSorting,
                                          string strLastUpdatedBy, DateTime dtLastUpdatedDateTime) {

            DataTable dTblAdvRuleView= advRuleViewVM.ToDataTable<AdvancedRulesMainViewModel>(); 
            dTblAdvRuleView.Columns.Remove("TableType");
            dTblAdvRuleView.Columns.Remove("JenisKempenID");
            dTblAdvRuleView.Columns.Remove("DisplaySetting");
            dTblAdvRuleView.Columns.Remove("Rules");
            dTblAdvRuleView.Columns.Remove("ChangeStamp");            
            dTblAdvRuleView.Columns.Remove("PermissionUsers");
            dTblAdvRuleView.Columns.Remove("PermissionRoles");
            dTblAdvRuleView.Columns.Remove("PermissionTypeDesc");


            DataTable dTblAdvRule = advRuleVM.ToDataTable<AdvanceRulesViewModel>();
            DataTable dTblAdvRuleGroup = advRuleGroupVM.ToDataTable<RuleGroupViewModel>();
            DataTable dTblAdvRuleGroupFields = advRuleGroupFieldsVM.ToDataTable<FieldValueViewModel>();
            DataTable dtblDisplayCol = (new List<DisplayColumnViewModel>()).ToDataTable<DisplayColumnViewModel>();
            if (lstDisplayCol != null) dtblDisplayCol = lstDisplayCol.ToDataTable<DisplayColumnViewModel>();

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

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

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

            dTblAdvRuleGroupFields.Columns.Remove("IsMultiple");

            int intViewID = 0;
            //Update Insert 
            DataSet ds= ExecuteSP("proc_UpdateSearchAdvRulesView", "@tableType", advRuleViewVM.TableType
                                    , "@tblView", dTblAdvRuleView
                                    , "@tblAdvRule", dTblAdvRule
                                    , "@tblAdvRuleGroup", dTblAdvRuleGroup
                                    , "@tblAdvRuleGroupFields", dTblAdvRuleGroupFields
                                    , "@tblDisplayCol", dtblDisplayCol
                                    , "@tblSorting", dtblFieldsSorting
                                    , "@LastUpdatedBy", strLastUpdatedBy
                                    , "@LastUpdatedDateTime", dtLastUpdatedDateTime);

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

        }

        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;
        }

    }

}