public async Task <ActionResult> SearchAsync(PurchaseSearch search)
{
var meta = await AppUsers.GetCurrentAsync().ConfigureAwait(true);
try
{
var result = await DAL.Backend.Tasks.Purchases.GetSearchViewAsync(this.Tenant, meta.OfficeId, search).ConfigureAwait(true);
return(this.Ok(result));
}
catch (Exception ex)
{
return(this.Failed(ex.Message, HttpStatusCode.InternalServerError));
}
}
public static async Task <IEnumerable <dynamic> > GetSearchViewAsync(string tenant, int officeId, PurchaseSearch search)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
var sql = new Sql("SELECT * FROM purchase.purchase_search_view");
sql.Where("value_date BETWEEN @0 AND @1", search.From, search.To);
sql.And("LOWER(tran_id) 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());
sql.And("LOWER(COALESCE(supplier, '')) LIKE @0", search.Supplier.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));
}
}