public IHttpActionResult GetList(QuotationSearch search)
{
AuthDetails authdet = LoginUserDetails();
var filters = new List <MySqlParameter>
{
datatableService1.CreateSqlParameter("@pUserId", authdet.UserId, MySqlDbType.Int32)
};
var result = service1.GetList(search, filters);
return(Ok(result));
}
public async Task <ActionResult> SearchAsync(QuotationSearch search)
{
var meta = await AppUsers.GetCurrentAsync().ConfigureAwait(true);
try
{
var result = await Quotations.GetSearchViewAsync(this.Tenant, meta.OfficeId, search).ConfigureAwait(true);
return(this.Ok(result));
}
catch (Exception ex)
{
return(this.Failed(ex.Message, HttpStatusCode.InternalServerError));
}
}
public DataTable <QuotationDatatable> GetList(QuotationSearch search, List <MySqlParameter> filters)
{
try
{
using (var ctx = new AppDb())
{
var result = datatableService.GetDataTableResult <QuotationDatatable>("quotation_list_sp", search, filters);
return(result);
}
}
catch (Exception ex)
{
throw ex;
}
}
public static async Task <IEnumerable <dynamic> > GetSearchViewAsync(string tenant, int officeId, QuotationSearch search)
{
using (var db = DbProvider.Get(FrapidDbServer.GetConnectionString(tenant), tenant).GetDatabase())
{
var sql = new Sql("SELECT * FROM purchase.quotation_search_view");
sql.Where("value_date BETWEEN @0 AND @1", search.From, search.To);
sql.And("expected_date BETWEEN @0 AND @1", search.ExpectedFrom, search.ExpectedTo);
sql.And("quotation_id::text LIKE @0", search.Id.ToSqlLikeExpression().ToLower());
sql.And("LOWER(reference_number) LIKE @0", search.ReferenceNumber.ToSqlLikeExpression().ToLower());
sql.And("LOWER(supplier) LIKE @0", search.Supplier.ToSqlLikeExpression().ToLower());
sql.And("LOWER(terms) LIKE @0", search.Terms.ToSqlLikeExpression().ToLower());
sql.And("LOWER(memo) LIKE @0", search.Memo.ToSqlLikeExpression().ToLower());
sql.And("LOWER(posted_by) LIKE @0", search.PostedBy.ToSqlLikeExpression().ToLower());
sql.And("LOWER(office) LIKE @0", search.Office.ToSqlLikeExpression().ToLower());
if (search.Amount > 0)
{
sql.And("total_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));
}
}
