private UIBulkUploadSearch PLoadBulkUpload()
        {
            long?TimeZone = currentUser.ADM_UserPreference.TimezoneLkup != null ? currentUser.ADM_UserPreference.TimezoneLkup : (long)DefaultTimeZone.CentralStandardTime;
            UIBulkUploadSearch      objUIBulkUploadSearch = new UIBulkUploadSearch();
            List <DOGEN_BulkImport> lstDOGEN_BulkImport   = new List <DOGEN_BulkImport>();

            errorMessage = string.Empty;
            try
            {
                long workBasketLkp = currentUser.WorkBasketLkup.ToInt64();
                objUIBulkUploadSearch.WorkbasketLkup  = workBasketLkp;
                objUIBulkUploadSearch.lstWorkbasket   = CacheUtility.GetAllLookupsFromCache(LookupTypes.WorkBasket.ToInt64());
                objUIBulkUploadSearch.lstDiscCategary = CacheUtility.GetAllLookupMasterCorrelationFromCache((long)LookupTypesCorrelation.WorkBasketVsDiscripancyCategory, workBasketLkp);
                objUIBulkUploadSearch.lstDOGEN_BulkImportExcelTemplateMaster = CacheUtility.GetBulkImportExcelTemplateFromCache().lstDOGEN_BulkImportExcelTemplateMaster.Where(x => x.TemplateTypeLkup == TemplateType.BulkUpload.ToInt64()).ToList();
                objUIBulkUploadSearch.TemplateTypeLkup = TemplateType.BulkUpload.ToLong();
                ExceptionTypes result = _objBLBulkUpload.GetBulkUploadSearchResult(TimeZone, objUIBulkUploadSearch, out lstDOGEN_BulkImport, out errorMessage);
                if (result != (long)ExceptionTypes.Success)
                {
                    BLCommon.LogError(currentUser.ADM_UserMasterId, MethodBase.GetCurrentMethod().Name, (long)ErrorModuleName.BulkUpload, (long)ExceptionTypes.Uncategorized, errorMessage, errorMessage);
                }
                objUIBulkUploadSearch.lstDOGEN_BulkImport = lstDOGEN_BulkImport;

                return(objUIBulkUploadSearch);
            }
            catch (Exception ex)
            {
                BLCommon.LogError(currentUser.ADM_UserMasterId, MethodBase.GetCurrentMethod().Name, (long)ErrorModuleName.BulkUpload, (long)ExceptionTypes.Uncategorized, string.Empty, ex.ToString());
                throw ex;
            }
        }
        /// <summary>
        /// Download template based on template ID
        /// </summary>
        /// <param name="templateMasterId"></param>
        /// <returns></returns>

        public FileResult DownloadTemplate(long templateMasterId)
        {
            byte[] fileBytes = null;
            string fileName  = string.Empty;
            List <DOGEN_BulkImportExcelTemplateMaster> lstDOGEN_BulkImportExcelTemplateMaster = new List <DOGEN_BulkImportExcelTemplateMaster>();

            try
            {
                lstDOGEN_BulkImportExcelTemplateMaster = CacheUtility.GetBulkImportExcelTemplateFromCache().lstDOGEN_BulkImportExcelTemplateMaster;

                if (lstDOGEN_BulkImportExcelTemplateMaster.Where(xx => xx.GEN_BulkImportExcelTemplateMasterId == templateMasterId && xx.IsActive == true).Count() > 0)
                {
                    var item = lstDOGEN_BulkImportExcelTemplateMaster.Where(xx => xx.GEN_BulkImportExcelTemplateMasterId == templateMasterId && xx.IsActive == true).FirstOrDefault();
                    fileBytes = System.IO.File.ReadAllBytes(item.ExcelDirectoryPath + item.ExcelTemplateName);
                    fileName  = item.ExcelTemplateName;
                }

                return(File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, fileName));
            }
            catch (Exception ex)
            {
                BLCommon.LogError(currentUser.ADM_UserMasterId, MethodBase.GetCurrentMethod().Name, (long)ErrorModuleName.BulkUpload, (long)ExceptionTypes.Uncategorized, string.Empty, ex.ToString());
                throw;
            }
        }
        private DOGEN_BulkImportExcelTemplateMaster PLoadTemplateByID(int templateID)
        {
            DOGEN_BulkImportExcelTemplateMaster objDOGEN_BulkImportExcelTemplateMaster = new DOGEN_BulkImportExcelTemplateMaster();

            try
            {
                objDOGEN_BulkImportExcelTemplateMaster = CacheUtility.GetBulkImportExcelTemplateFromCache().lstDOGEN_BulkImportExcelTemplateMaster.Where(xx => xx.GEN_BulkImportExcelTemplateMasterId == templateID).FirstOrDefault();
                return(objDOGEN_BulkImportExcelTemplateMaster);
            }
            catch (Exception ex)
            {
                BLCommon.LogError(currentUser.ADM_UserMasterId, MethodBase.GetCurrentMethod().Name, (long)ErrorModuleName.BulkUpload, (long)ExceptionTypes.Uncategorized, string.Empty, ex.ToString());
                throw ex;
            }
        }
        private bool IsExcelFileValid(long excelTemplateId, string filePath, string fileName, out string errorMessage)
        {
            bool isExcelFileValid = true;

            errorMessage = string.Empty;
            bool repeatedColumnFound = false;

            string[] uploadFileColumnsList = null;
            string[] templateColumnsList;
            DOGEN_BulkImportExcelTemplateMaster   objDOGEN_BulkImportExcelTemplateMaster = new DOGEN_BulkImportExcelTemplateMaster();
            List <DOGEN_BulkImportColumnsMapping> lstDOGEN_BulkImportColumnsMapping      = new List <DOGEN_BulkImportColumnsMapping>();

            try
            {
                objDOGEN_BulkImportExcelTemplateMaster = CacheUtility.GetBulkImportExcelTemplateFromCache().lstDOGEN_BulkImportExcelTemplateMaster.Where(xx => xx.GEN_BulkImportExcelTemplateMasterId == excelTemplateId && xx.IsActive == true).FirstOrDefault();
                lstDOGEN_BulkImportColumnsMapping      = CacheUtility.GetBulkImportExcelTemplateFromCache().lstDOGEN_BulkImportColumnsMapping.Where(xx => xx.GEN_BulkImportExcelTemplateMasterRef == excelTemplateId && xx.IsActive == true).ToList();
                templateColumnsList = lstDOGEN_BulkImportColumnsMapping.OrderBy(x => x.ColumnSequence).Select(s => s.ColumnDisplayName).ToArray();

                if (fileName != objDOGEN_BulkImportExcelTemplateMaster.ExcelTemplateName)
                {
                    errorMessage = "Invalid File.";
                    return(false);
                }

                var getXlsxHeaderRowAsStringRetVal = GetCSVHeaderRowAsString(filePath, objDOGEN_BulkImportExcelTemplateMaster.SheetName, objDOGEN_BulkImportExcelTemplateMaster.StartRow, out errorMessage, out repeatedColumnFound);
                //var getXlsxHeaderRowAsStringRetVal = GetXlsxHeaderRowAsString(filePath, objDOGEN_BulkImportExcelTemplateMaster.SheetName, objDOGEN_BulkImportExcelTemplateMaster.StartRow, out errorMessage, out repeatedColumnFound);
                if (repeatedColumnFound)
                {
                    errorMessage     = "There are some repeated columns in the excel.";
                    isExcelFileValid = false;
                    return(isExcelFileValid);
                }

                if (getXlsxHeaderRowAsStringRetVal != null && getXlsxHeaderRowAsStringRetVal.Count > 0)
                {
                    if (getXlsxHeaderRowAsStringRetVal.Count >= objDOGEN_BulkImportExcelTemplateMaster.StartColumn)
                    {
                        uploadFileColumnsList = getXlsxHeaderRowAsStringRetVal.Skip((int)objDOGEN_BulkImportExcelTemplateMaster.StartColumn - 1).ToArray();
                    }

                    bool   retVal             = true;
                    string missingColumnNames = string.Empty;
                    if (uploadFileColumnsList != null)
                    {
                        if (uploadFileColumnsList.Length == templateColumnsList.Length)
                        {
                            for (int i = 0; i < templateColumnsList.Length; i++)
                            {
                                if (uploadFileColumnsList[i].Trim().Length > 0 && templateColumnsList[i].Trim() != uploadFileColumnsList[i].Trim())
                                {
                                    retVal              = false;
                                    missingColumnNames += ',' + templateColumnsList[i];
                                }
                            }
                            if (retVal == false)
                            {
                                errorMessage     = "Following column/s are missing or renamed or not as per template order in the uploaded excel:" + missingColumnNames.TrimStart(',') + ".";
                                isExcelFileValid = false;
                            }
                            else
                            {
                                isExcelFileValid = true;//if all columns are present in uploaded file and in same order then return true
                            }
                        }
                        else if (uploadFileColumnsList.Length > 0)
                        {
                            for (int i = 0; i < templateColumnsList.Length; i++)
                            {
                                //column matching is not casesensitive
                                if (i < uploadFileColumnsList.Length)
                                {
                                    if (templateColumnsList[i] != uploadFileColumnsList[i])
                                    {
                                        missingColumnNames += ',' + templateColumnsList[i];
                                    }
                                }
                                else
                                {
                                    missingColumnNames += ',' + templateColumnsList[i];
                                }
                            }
                            errorMessage     = "Following column/s are missing or renamed or not as per template order in the uploaded excel:" + missingColumnNames.TrimStart(',') + ".";
                            isExcelFileValid = false;
                        }
                    }
                }
                else
                {
                    isExcelFileValid = false;
                }
                if (!isExcelFileValid)
                {
                    BLCommon.LogError(currentUser.ADM_UserMasterId, MethodBase.GetCurrentMethod().Name, (long)ErrorModuleName.BulkUpload, (long)ExceptionTypes.Uncategorized, errorMessage, errorMessage);
                }
            }
            catch (Exception ex)
            {
                errorMessage += ex.Message + ex.StackTrace;
                BLCommon.LogError(currentUser.ADM_UserMasterId, MethodBase.GetCurrentMethod().Name, (long)ErrorModuleName.BulkUpload, (long)ExceptionTypes.Uncategorized, errorMessage, errorMessage);
                isExcelFileValid = false;
            }
            return(isExcelFileValid);
        }