public async Task <IActionResult> BulkUploadProviderWiseTrans([FromForm] string providerId) { ExcelFileAC excelFileAC = new ExcelFileAC(); IFormFile file = Request.Form.Files[0]; excelFileAC.File = file; excelFileAC.FolderName = "TempUpload"; ExcelUploadResponseAC exceluploadDetail = _iBillUploadRepository.UploadNewExcel(excelFileAC); string userId = HttpContext.User.Claims.FirstOrDefault(c => c.Type == "user_id").Value; string fullname = HttpContext.User.Claims.FirstOrDefault(c => c.Type == "fullname").Value; return(Ok(await _iConfigurationRepository.BulkUploadProviderWiseTrans(Convert.ToInt64(userId), exceluploadDetail, Convert.ToInt64(providerId), fullname))); }
public async Task <IActionResult> BulkAssginTelePhone() { ExcelFileAC excelFileAC = new ExcelFileAC(); IFormFile file = Request.Form.Files[0]; excelFileAC.File = file; excelFileAC.FolderName = "TempUpload"; ExcelUploadResponseAC exceluploadDetail = _iBillUploadRepository.UploadNewExcel(excelFileAC); string userId = HttpContext.User.Claims.FirstOrDefault(c => c.Type == "user_id").Value; string fullname = HttpContext.User.Claims.FirstOrDefault(c => c.Type == "fullname").Value; return(Ok(await _iTelephoneRepository.UploadBulkAssignTelePhone(Convert.ToInt64(userId), exceluploadDetail, fullname))); }
public async Task <BulkAssignTelephoneResponseAC> UploadBulkAssignTelePhone(long userId, ExcelUploadResponseAC exceluploadDetail, string loginUserName) { BulkAssignTelephoneResponseAC bulkAssignTelephoneResponseAC = new BulkAssignTelephoneResponseAC(); List <ExcelUploadResult> excelUploadResultList = new List <ExcelUploadResult>(); FileInfo fileinfo = new FileInfo(Path.Combine(exceluploadDetail.FilePath, exceluploadDetail.FileNameGuid)); try { using (ExcelPackage package = new ExcelPackage(fileinfo)) { for (int i = 1; i <= package.Workbook.Worksheets.Count(); i++) { ExcelWorksheet workSheet = package.Workbook.Worksheets[i]; string sheetName = package.Workbook.Worksheets[i].Name; int totalRows = workSheet.Dimension.Rows; int totalColums = workSheet.Dimension.Columns; for (int j = 1; j <= totalRows - 1; j++) { string mobileNumber = workSheet.Cells[j + 1, 1].Value != null ? workSheet.Cells[j + 1, 1].Value.ToString() : string.Empty; string assignType = workSheet.Cells[j + 1, 2].Value != null ? workSheet.Cells[j + 1, 2].Value.ToString() : string.Empty; string pfNumber = workSheet.Cells[j + 1, 3].Value != null ? workSheet.Cells[j + 1, 3].Value.ToString() : string.Empty; string packageName = workSheet.Cells[j + 1, 4].Value != null ? workSheet.Cells[j + 1, 4].Value.ToString() : string.Empty; string startDate = workSheet.Cells[j + 1, 5].Value != null ? workSheet.Cells[j + 1, 5].Value.ToString() : string.Empty; string endDate = workSheet.Cells[j + 1, 6].Value != null ? workSheet.Cells[j + 1, 6].Value.ToString() : string.Empty; if (!string.IsNullOrEmpty(mobileNumber) && !string.IsNullOrEmpty(assignType) && !string.IsNullOrEmpty(pfNumber) && !string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(packageName) && !string.IsNullOrEmpty(endDate)) { Telephonenumberallocation telephoneNumberAllocation = new Telephonenumberallocation(); Telephonenumberallocationpackage telePhoneNumberAllocationPackage = new Telephonenumberallocationpackage(); bulkAssignTelephoneResponseAC.TotalRecords += 1; Telephonenumber telephoneNumber = await _dbTeleBilling_V01Context.Telephonenumber.FirstOrDefaultAsync(x => x.TelephoneNumber1.ToLower().Trim() == mobileNumber.ToLower().Trim() && x.IsActive && !x.IsDelete); if (telephoneNumber != null) { FixAssigntype fixAssigntype = await _dbTeleBilling_V01Context.FixAssigntype.FirstOrDefaultAsync(x => x.IsActive && x.Name == assignType); if (fixAssigntype != null) { MstEmployee mstEmployee = await _dbTeleBilling_V01Context.MstEmployee.FirstOrDefaultAsync(x => x.IsActive && !x.IsDelete && x.EmpPfnumber.ToLower().Trim() == pfNumber.ToLower().Trim()); if (mstEmployee != null) { Providerpackage providerPackage = await _dbTeleBilling_V01Context.Providerpackage.FirstOrDefaultAsync(x => x.IsActive && !x.IsDelete && x.Name.ToLower().Trim() == packageName.ToLower().Trim()); if (providerPackage != null) { if (!string.IsNullOrEmpty(startDate)) { if (!string.IsNullOrEmpty(endDate)) { DateTime newStartDate; DateTime newEndDate; if (DateTime.TryParse(startDate, out newStartDate)) { String.Format("{0:d/MM/yyyy}", newStartDate); if (DateTime.TryParse(endDate, out newEndDate)) { String.Format("{0:d/MM/yyyy}", newEndDate); string lineStatus = workSheet.Cells[j + 1, 7].Value != null ? workSheet.Cells[j + 1, 7].Value.ToString() : ""; FixLinestatus fixLineStatus = await _dbTeleBilling_V01Context.FixLinestatus.FirstOrDefaultAsync(x => x.IsActive && x.Name.ToLower().Trim() == lineStatus.ToLower().Trim()); if (fixLineStatus != null) { if (telephoneNumber.ProviderId == providerPackage.ProviderId) { Telephonenumberallocation newTelephoneNumberAllocation = _dbTeleBilling_V01Context.Telephonenumberallocation.FirstOrDefault(x => x.TelephoneNumberId == telephoneNumber.Id && !x.IsDelete); if (newTelephoneNumberAllocation != null) { Telephonenumberallocationpackage newTelePhoneNumberAllocationPackage = await _dbTeleBilling_V01Context.Telephonenumberallocationpackage.FirstOrDefaultAsync(x => x.TelephoneNumberAllocationId == newTelephoneNumberAllocation.Id && x.PackageId == providerPackage.Id && !x.IsDelete); if (newTelePhoneNumberAllocationPackage == null) { newTelePhoneNumberAllocationPackage = new Telephonenumberallocationpackage(); newTelePhoneNumberAllocationPackage.ServiceId = providerPackage.ServiceTypeId; newTelePhoneNumberAllocationPackage.StartDate = newStartDate; newTelePhoneNumberAllocationPackage.EndDate = newEndDate; newTelePhoneNumberAllocationPackage.PackageId = providerPackage.Id; bulkAssignTelephoneResponseAC.SuccessRecords += 1; newTelePhoneNumberAllocationPackage.TelephoneNumberAllocationId = newTelephoneNumberAllocation.Id; await _dbTeleBilling_V01Context.AddAsync(newTelePhoneNumberAllocationPackage); await _dbTeleBilling_V01Context.SaveChangesAsync(); await _iLogManagement.SaveAuditActionLog((int)EnumList.AuditLogActionType.AssignBulkTelephone, loginUserName, userId, "Telephone Assign(" + newTelephoneNumberAllocation.TelephoneNumber + ")", (int)EnumList.ActionTemplateTypes.Upload, newTelephoneNumberAllocation.Id); } else { //TelphoneNumbeAlready Assigned bulkAssignTelephoneResponseAC.SkipRecords += 1; string message = _iStringConstant.TelphoneNumberPackageAlreadyAssigned.Replace("{{$telephonenumber$}}", mobileNumber); excelUploadResultList = AddedFileDataResponse(1, j + 1, packageName, message, sheetName, excelUploadResultList); } } else { telephoneNumberAllocation.EmployeeId = mstEmployee.UserId; telephoneNumberAllocation.EmpPfnumber = mstEmployee.EmpPfnumber; telephoneNumberAllocation.TelephoneNumberId = telephoneNumber.Id; telephoneNumberAllocation.TelephoneNumber = telephoneNumber.TelephoneNumber1; telephoneNumberAllocation.LineStatusId = fixLineStatus.Id; telephoneNumberAllocation.IsActive = true; telephoneNumberAllocation.TransactionId = _iLogManagement.GenerateTeleBillingTransctionID(); telephoneNumberAllocation.CreatedBy = userId; telephoneNumberAllocation.CreatedDate = DateTime.Now; telephoneNumberAllocation.AssignTypeId = fixAssigntype.Id; bulkAssignTelephoneResponseAC.SuccessRecords += 1; await _dbTeleBilling_V01Context.AddAsync(telephoneNumberAllocation); await _dbTeleBilling_V01Context.SaveChangesAsync(); telePhoneNumberAllocationPackage.ServiceId = providerPackage.ServiceTypeId; telePhoneNumberAllocationPackage.StartDate = newStartDate; telePhoneNumberAllocationPackage.EndDate = newEndDate; telePhoneNumberAllocationPackage.PackageId = providerPackage.Id; telePhoneNumberAllocationPackage.TelephoneNumberAllocationId = telephoneNumberAllocation.Id; await _dbTeleBilling_V01Context.AddAsync(telePhoneNumberAllocationPackage); await _dbTeleBilling_V01Context.SaveChangesAsync(); await _iLogManagement.SaveAuditActionLog((int)EnumList.AuditLogActionType.AssignBulkTelephone, loginUserName, userId, "Telephone Assign(" + newTelephoneNumberAllocation.TelephoneNumber + ")", (int)EnumList.ActionTemplateTypes.Upload, newTelephoneNumberAllocation.Id); } } else { //Packge not same bulkAssignTelephoneResponseAC.SkipRecords += 1; excelUploadResultList = AddedFileDataResponse(4, j + 1, "Given package not valid for mobilenumber(" + mobileNumber + ")", _iStringConstant.PackageNotMatchWithTelephoneNumber, sheetName, excelUploadResultList); } } else { //LineStatus Not Exists bulkAssignTelephoneResponseAC.SkipRecords += 1; excelUploadResultList = AddedFileDataResponse(7, j + 1, lineStatus, _iStringConstant.LineStatusNotExists, sheetName, excelUploadResultList); } } else { //End Date Not Valid bulkAssignTelephoneResponseAC.SkipRecords += 1; excelUploadResultList = AddedFileDataResponse(6, j + 1, endDate, _iStringConstant.EndDateNotValid, sheetName, excelUploadResultList); } } else { //Start Date Not Valid bulkAssignTelephoneResponseAC.SkipRecords += 1; excelUploadResultList = AddedFileDataResponse(5, j + 1, startDate, _iStringConstant.StartDateNotValid, sheetName, excelUploadResultList); } } else { //End Date Is Empty bulkAssignTelephoneResponseAC.SkipRecords += 1; excelUploadResultList = AddedFileDataResponse(6, j + 1, endDate, _iStringConstant.EndDateIsEmpty, sheetName, excelUploadResultList); } } else { //Start Date Is Empty bulkAssignTelephoneResponseAC.SkipRecords += 1; excelUploadResultList = AddedFileDataResponse(5, j, startDate, _iStringConstant.StartDateIsEmpty, sheetName, excelUploadResultList); } } else { //Package Not Exists bulkAssignTelephoneResponseAC.SkipRecords += 1; excelUploadResultList = AddedFileDataResponse(4, j + 1, packageName, _iStringConstant.PackageNotExists, sheetName, excelUploadResultList); } } else { //Employee Not Exists bulkAssignTelephoneResponseAC.SkipRecords += 1; excelUploadResultList = AddedFileDataResponse(3, j, pfNumber, _iStringConstant.EmployeeNotExists, sheetName, excelUploadResultList); } //else //{ //TelphoneNumbeAlready Assigned //bulkAssignTelephoneResponseAC.SkipRecords += 1; //excelUploadResultList = AddedFileDataResponse(1, j + 1, mobileNumber, _iStringConstant.TelphoneNumberAlreadyAssigned, sheetName, excelUploadResultList); //} } else { //Employee Not Exists bulkAssignTelephoneResponseAC.SkipRecords += 1; excelUploadResultList = AddedFileDataResponse(2, j, assignType, _iStringConstant.AssignTypeNotExists, sheetName, excelUploadResultList); } } else { //PhoneNumber Not Exists bulkAssignTelephoneResponseAC.SkipRecords += 1; excelUploadResultList = AddedFileDataResponse(1, j + 1, mobileNumber, _iStringConstant.PhoneNumberNotExists, sheetName, excelUploadResultList); } } } } } if (File.Exists(Path.Combine(exceluploadDetail.FilePath, exceluploadDetail.FileNameGuid))) { File.Delete(Path.Combine(exceluploadDetail.FilePath, exceluploadDetail.FileNameGuid)); } bulkAssignTelephoneResponseAC.excelUploadResultList = excelUploadResultList; return(bulkAssignTelephoneResponseAC); } catch (Exception ex) { if (File.Exists(Path.Combine(exceluploadDetail.FilePath, exceluploadDetail.FileNameGuid))) { File.Delete(Path.Combine(exceluploadDetail.FilePath, exceluploadDetail.FileNameGuid)); } throw ex; } }
public async Task <BulkAssignTelephoneResponseAC> BulkUploadOperatorCallLog(long userId, ExcelUploadResponseAC exceluploadDetail, string loginUserName) { BulkAssignTelephoneResponseAC bulkAssignTelephoneResponseAC = new BulkAssignTelephoneResponseAC(); List <ExcelUploadResult> excelUploadResultList = new List <ExcelUploadResult>(); List <Operatorcalllog> operatorCallLogList = new List <Operatorcalllog>(); FileInfo fileinfo = new FileInfo(Path.Combine(exceluploadDetail.FilePath, exceluploadDetail.FileNameGuid)); try { using (ExcelPackage package = new ExcelPackage(fileinfo)) { for (int i = 1; i <= package.Workbook.Worksheets.Count(); i++) { ExcelWorksheet workSheet = package.Workbook.Worksheets[i]; if (workSheet != null) { string sheetName = workSheet.Name; int totalRows = workSheet.Dimension.Rows; int totalColums = workSheet.Dimension.Columns; for (int j = 1; j <= totalRows - 1; j++) { Operatorcalllog operatorCallLog = new Operatorcalllog(); bulkAssignTelephoneResponseAC.TotalRecords += 1; string callDate = workSheet.Cells[j + 1, 1].Value != null ? workSheet.Cells[j + 1, 1].Value.ToString() : string.Empty; if (!string.IsNullOrEmpty(callDate)) { DateTime newCallDate; if (DateTime.TryParse(callDate, out newCallDate)) { String.Format("{0:d/MM/yyyy}", newCallDate); string pfNumber = workSheet.Cells[j + 1, 2].Value != null ? workSheet.Cells[j + 1, 2].Value.ToString() : string.Empty; pfNumber = !string.IsNullOrEmpty(pfNumber) ? pfNumber.ToLower().Trim() : string.Empty; MstEmployee mstEmployee = await _dbTeleBilling_V01Context.MstEmployee.FirstOrDefaultAsync(x => x.IsActive && !x.IsDelete && x.EmpPfnumber.ToLower().Trim() == pfNumber); if (mstEmployee != null) { string telephoneNumber = workSheet.Cells[j + 1, 3].Value != null ? workSheet.Cells[j + 1, 3].Value.ToString() : string.Empty; if (!string.IsNullOrEmpty(telephoneNumber)) { if (telephoneNumber.Length < 50) { string provider = workSheet.Cells[j + 1, 4].Value != null ? workSheet.Cells[j + 1, 4].Value.ToString() : string.Empty; provider = !string.IsNullOrEmpty(provider) ? provider.ToLower().Trim() : string.Empty; TeleBillingUtility.Models.Provider providerObj = await _dbTeleBilling_V01Context.Provider.FirstOrDefaultAsync(x => x.IsActive && !x.IsDelete && x.Name.ToLower().Trim() == provider); if (providerObj != null) { string callType = workSheet.Cells[j + 1, 5].Value != null ? workSheet.Cells[j + 1, 5].Value.ToString() : string.Empty; callType = !string.IsNullOrEmpty(callType) ? callType.ToLower().Trim() : string.Empty; FixCalltype callTypeObj = await _dbTeleBilling_V01Context.FixCalltype.FirstOrDefaultAsync(x => x.IsActive && x.Name.ToLower().Trim() == callType); if (callTypeObj != null) { operatorCallLog.CallDate = newCallDate; operatorCallLog.CallTypeId = callTypeObj.Id; operatorCallLog.ProviderId = providerObj.Id; operatorCallLog.EmployeeId = mstEmployee.UserId; operatorCallLog.EmpPfnumber = mstEmployee.EmpPfnumber; operatorCallLog.ExtensionNumber = mstEmployee.ExtensionNumber; operatorCallLog.TransactionId = _iLogManagement.GenerateTeleBillingTransctionID(); operatorCallLog.DialedNumber = telephoneNumber; operatorCallLog.CreatedBy = userId; operatorCallLog.CreatedDate = DateTime.Now; operatorCallLogList.Add(operatorCallLog); bulkAssignTelephoneResponseAC.SuccessRecords += 1; } else { //Call Type Not Exists bulkAssignTelephoneResponseAC.SkipRecords += 1; excelUploadResultList = AddedFileDataResponse(4, j, callType, _iStringConstant.CallTypeNotExists, sheetName, excelUploadResultList); } } else { //Provider Not Exists bulkAssignTelephoneResponseAC.SkipRecords += 1; excelUploadResultList = AddedFileDataResponse(4, j, provider, _iStringConstant.ProviderNotExists, sheetName, excelUploadResultList); } } else { //Telephone Number length bulkAssignTelephoneResponseAC.SkipRecords += 1; excelUploadResultList = AddedFileDataResponse(3, j, telephoneNumber, _iStringConstant.TelePhoneNumberMaxLength, sheetName, excelUploadResultList); } } else { //Telephone number is empty bulkAssignTelephoneResponseAC.SkipRecords += 1; excelUploadResultList = AddedFileDataResponse(3, j, telephoneNumber, _iStringConstant.TelePhoneNumberIsEmpty, sheetName, excelUploadResultList); } } else { //Employee Not Exists bulkAssignTelephoneResponseAC.SkipRecords += 1; excelUploadResultList = AddedFileDataResponse(2, j, pfNumber, _iStringConstant.EmployeeNotExists, sheetName, excelUploadResultList); } } else { //Start Date Not Valid bulkAssignTelephoneResponseAC.SkipRecords += 1; excelUploadResultList = AddedFileDataResponse(1, j + 1, callDate, _iStringConstant.CallDateNotValid, sheetName, excelUploadResultList); } } else { //Call Date Is Empty bulkAssignTelephoneResponseAC.SkipRecords += 1; excelUploadResultList = AddedFileDataResponse(1, j, callDate, _iStringConstant.CallDateIsEmpty, sheetName, excelUploadResultList); } } } } } if (File.Exists(Path.Combine(exceluploadDetail.FilePath, exceluploadDetail.FileNameGuid))) { File.Delete(Path.Combine(exceluploadDetail.FilePath, exceluploadDetail.FileNameGuid)); } if (operatorCallLogList.Any()) { await _dbTeleBilling_V01Context.AddRangeAsync(operatorCallLogList); await _dbTeleBilling_V01Context.SaveChangesAsync(); await _iLogManagement.SaveAuditActionLog((int)EnumList.AuditLogActionType.BulkUploadOperatorCallLog, loginUserName, userId, "Bulk Operator call log", (int)EnumList.ActionTemplateTypes.Upload, null); } bulkAssignTelephoneResponseAC.excelUploadResultList = excelUploadResultList; return(bulkAssignTelephoneResponseAC); } catch (Exception ex) { if (File.Exists(Path.Combine(exceluploadDetail.FilePath, exceluploadDetail.FileNameGuid))) { File.Delete(Path.Combine(exceluploadDetail.FilePath, exceluploadDetail.FileNameGuid)); } throw ex; } }
public async Task <BulkAssignTelephoneResponseAC> BulkUploadProviderWiseTrans(long userId, ExcelUploadResponseAC exceluploadDetail, long providerId, string loginUserName) { BulkAssignTelephoneResponseAC bulkAssignTelephoneResponseAC = new BulkAssignTelephoneResponseAC(); List <ExcelUploadResult> excelUploadResultList = new List <ExcelUploadResult>(); List <Transactiontypesetting> transactionTypeSettingList = new List <Transactiontypesetting>(); FileInfo fileinfo = new FileInfo(Path.Combine(exceluploadDetail.FilePath, exceluploadDetail.FileNameGuid)); try { using (ExcelPackage package = new ExcelPackage(fileinfo)) { for (int i = 1; i <= package.Workbook.Worksheets.Count(); i++) { ExcelWorksheet workSheet = package.Workbook.Worksheets[i]; string sheetName = package.Workbook.Worksheets[i].Name; int totalRows = workSheet.Dimension.Rows; int totalColums = workSheet.Dimension.Columns; for (int j = 1; j <= totalRows - 1; j++) { Transactiontypesetting transactionTypeSetting = new Transactiontypesetting(); bulkAssignTelephoneResponseAC.TotalRecords += 1; string transactionType = workSheet.Cells[j + 1, 1].Value != null ? workSheet.Cells[j + 1, 1].Value.ToString() : string.Empty; if (!string.IsNullOrEmpty(transactionType)) { if (!await _dbTeleBilling_V01Context.Transactiontypesetting.AnyAsync(x => x.ProviderId == providerId && !x.IsDelete && x.TransactionType.ToLower().Trim() == transactionType.ToLower().Trim())) { transactionTypeSetting.ProviderId = providerId; transactionTypeSetting.TransactionType = transactionType.Trim(); transactionTypeSetting.CreatedBy = userId; transactionTypeSetting.CreatedDate = DateTime.Now; transactionTypeSetting.TransactionId = _iLogManagement.GenerateTeleBillingTransctionID(); bulkAssignTelephoneResponseAC.SuccessRecords += 1; transactionTypeSettingList.Add(transactionTypeSetting); } else { //PhoneNumber Not Exists bulkAssignTelephoneResponseAC.SkipRecords += 1; excelUploadResultList = AddedFileDataResponse(1, j + 1, transactionType, _iStringConstant.ProviderWiseTransactionTypeAlreadyExists, sheetName, excelUploadResultList); } } else { //PhoneNumber Not Exists bulkAssignTelephoneResponseAC.SkipRecords += 1; excelUploadResultList = AddedFileDataResponse(1, j + 1, transactionType, _iStringConstant.TransactionTypeIsEmpty, sheetName, excelUploadResultList); } } } } if (File.Exists(Path.Combine(exceluploadDetail.FilePath, exceluploadDetail.FileNameGuid))) { File.Delete(Path.Combine(exceluploadDetail.FilePath, exceluploadDetail.FileNameGuid)); } if (transactionTypeSettingList.Any()) { await _dbTeleBilling_V01Context.AddRangeAsync(transactionTypeSettingList); await _dbTeleBilling_V01Context.SaveChangesAsync(); await _iLogManagement.SaveAuditActionLog((int)EnumList.AuditLogActionType.BulkUploadTransactionType, loginUserName, userId, "Provider wise transaction", (int)EnumList.ActionTemplateTypes.Upload, null); } bulkAssignTelephoneResponseAC.excelUploadResultList = excelUploadResultList; return(bulkAssignTelephoneResponseAC); } catch (Exception ex) { if (File.Exists(Path.Combine(exceluploadDetail.FilePath, exceluploadDetail.FileNameGuid))) { File.Delete(Path.Combine(exceluploadDetail.FilePath, exceluploadDetail.FileNameGuid)); } throw ex; } }