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