public async Task <IActionResult> Excel(TransferSearch search, int page = 1)
{
try
{
TransferWhereBuilder transferWhereBuilder = new TransferWhereBuilder(search);
string searchParams = search != default ? search.ToString() : default;
List <TransferPreviewViewModel> transfers = await GetPage(search, transferWhereBuilder, searchParams, page);
if (transfers == default)
{
return(BadRequest());
}
return(await base.FormatModel <TransferPreviewViewModel>(transfers, MedicDataLocalization.Plannings, FormattableFactory));
}
catch (Exception ex)
{
Task <int> _ = MedicLoggerService.SaveAsync(new Log()
{
Message = ex.Message,
InnerExceptionMessage = ex?.InnerException?.Message ?? null,
Source = ex.Source,
StackTrace = ex.StackTrace,
Date = DateTime.Now
});
throw;
}
}
public async Task <ActionResult> SearchAsync(TransferSearch search)
{
var meta = await AppUsers.GetCurrentAsync().ConfigureAwait(true);
try
{
var result = await InventoryTransfers.GetSearchViewAsync(this.Tenant, meta.OfficeId, search).ConfigureAwait(true);
return(this.Ok(result));
}
catch (Exception ex)
{
return(this.Failed(ex.Message, HttpStatusCode.InternalServerError));
}
}
public async Task <IActionResult> Index(TransferSearch search, int page = 1)
{
try
{
TransferWhereBuilder plannedWhereBuilder = new TransferWhereBuilder(search);
string searchParams = search != default ? search.ToString() : default;
List <TransferPreviewViewModel> trasnfers = await GetPage(search, plannedWhereBuilder, searchParams, page);
string trasnferCountKey = $"{MedicConstants.Transfers} - {searchParams}";
if (!base.MedicCache.TryGetValue(trasnferCountKey, out int trasnfersCount))
{
trasnfersCount = await TransferService
.GetTrasnfersCountAsync(plannedWhereBuilder);
base.MedicCache.Set(trasnferCountKey, trasnfersCount);
}
return(View(new TransferPageIndexModel()
{
Transfers = trasnfers,
Title = MedicDataLocalization.Get(MedicDataLocalization.Transfers),
Description = MedicDataLocalization.Get(MedicDataLocalization.Transfers),
Keywords = MedicDataLocalization.Get(MedicDataLocalization.TransfersSummary),
Search = search,
CurrentPage = page,
TotalPages = base.TotalPages((int)search.Length, trasnfersCount),
TotalResults = trasnfersCount
}));
}
catch (Exception ex)
{
Task <int> _ = MedicLoggerService.SaveAsync(new Log()
{
Message = ex.Message,
InnerExceptionMessage = ex?.InnerException?.Message ?? null,
Source = ex.Source,
StackTrace = ex.StackTrace,
Date = DateTime.Now
});
throw ex;
}
}
private async Task <List <TransferPreviewViewModel> > GetPage(TransferSearch search, TransferWhereBuilder plannedWhereBuilder, string searchParams, int page)
{
int pageLength = (int)search.Length;
int startIndex = base.GetStartIndex(pageLength, page);
string transferKey = $"{nameof(TransferPreviewViewModel)} - {startIndex} - {searchParams}";
if (!base.MedicCache.TryGetValue(transferKey, out List <TransferPreviewViewModel> plannings))
{
TransferHelperBuilder helperBuilder = new TransferHelperBuilder(search);
plannings = await TransferService.GetTrasnfersAsync(plannedWhereBuilder, helperBuilder, startIndex);
base.MedicCache.Set(transferKey, plannings);
}
return(plannings);
}
public static async Task <IEnumerable <dynamic> > GetSearchViewAsync(string tenant, int officeId, TransferSearch search)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
var sql = new Sql("SELECT * FROM inventory.transfer_search_view");
sql.Where("value_date BETWEEN @0 AND @1", search.From, search.To);
sql.And("CAST(tran_id AS varchar(100)) LIKE @0", search.TranId.ToSqlLikeExpression().ToLower());
sql.And("LOWER(tran_code) LIKE @0", search.TranCode.ToSqlLikeExpression().ToLower());
sql.And("LOWER(COALESCE(reference_number, '')) LIKE @0", search.ReferenceNumber.ToSqlLikeExpression().ToLower());
sql.And("LOWER(COALESCE(statement_reference, '')) LIKE @0", search.StatementReference.ToSqlLikeExpression().ToLower());
sql.And("LOWER(posted_by) LIKE @0", search.PostedBy.ToSqlLikeExpression().ToLower());
sql.And("LOWER(office) LIKE @0", search.Office.ToSqlLikeExpression().ToLower());
sql.And("LOWER(COALESCE(status, '')) LIKE @0", search.Status.ToSqlLikeExpression().ToLower());
sql.And("LOWER(COALESCE(verified_by, '')) LIKE @0", search.VerifiedBy.ToSqlLikeExpression().ToLower());
sql.And("LOWER(COALESCE(reason, '')) LIKE @0", search.Reason.ToSqlLikeExpression().ToLower());
if (search.Amount > 0)
{
sql.And("amount = @0", search.Amount);
}
sql.And("office_id IN(SELECT * FROM core.get_office_ids(@0))", officeId);
return(await db.SelectAsync <dynamic>(sql).ConfigureAwait(false));
}
}
public TransferHelperBuilder(TransferSearch transferSearch)
{
TransferSearch = transferSearch;
}
public TransferWhereBuilder(TransferSearch transferSearch)
{
TransferSearch = transferSearch;
}
