public async Task <ActionResult> GetAllCustomersPaginate(string first_name, string last_name, string mobile, int status_id, int page = 1, int pagesize = 10)
{
try
{
if (!ModelState.IsValid)
{
return(BadRequest());
}
var query = $" select count(1) OVER() AS row_count,c.*,b.title as bank_title from public.\"Customers\" c left join public.banks b on c.bank_id = b.\"Id\" where 1 = 1 and \"firstName\" like '%{first_name}%' and \"lastName\" like '%{last_name}%' and mobile like '%{mobile}%' " + (status_id > 0 ? $" and \"StatusId\"= {status_id} " : string.Empty) + $" ORDER BY c.\"Id\" LIMIT {pagesize} OFFSET ({pagesize} * ({page}-1)) ";
var cus = await dBDapperRepository.RunQueryAsync <CustomerReportModel>(query);
//var cus = await _dBRepository.Customers.AsNoTracking().ToListAsync();
return(Ok(new CoreResponse()
{
isSuccess = true, data = cus, total_items = cus.First()?.row_count, current_page = page, total_pages = (cus.First()?.row_count / pagesize) + 1
}));
}
catch (Exception ex)
{
return(Ok(new CoreResponse()
{
isSuccess = false, data = null, devMessage = ex.Message
}));
}
}
public async Task <ActionResult> GetCoinData()
{
try
{
if (!ModelState.IsValid)
{
return(BadRequest());
}
var config = await dBRepository.Config.AsNoTracking().FirstOrDefaultAsync();
var cachedata = cacheManager.GetCoinLog();
if (cachedata != null && cachedata.Count() > 0)
{
return(Ok(cachedata.ConvertToCoinDataView(config.TetherRialValue)));
}
else
{
var lst = await dBDapperRepository.RunQueryAsync <CoinData>(" WITH lastseriesdate AS ( SELECT \"SeriesDate\" FROM public.\"CoinDatas\" order by \"Id\" desc limit 1) SELECT * FROM public.\"CoinDatas\" WHERE \"SeriesDate\" = (SELECT \"SeriesDate\" FROM lastseriesdate) ");
return(Ok(lst.ConvertToCoinDataView(config.TetherRialValue)));
}
}
catch (Exception ex)
{
return(NoContent());
}
}
public async Task <ActionResult> GetCustomers(string first_name, string last_name, string mobile)
{
try
{
if (!ModelState.IsValid)
{
return(BadRequest());
}
var query = $@"select * from public.accounts
where account_type_id = 1 " +
(!string.IsNullOrWhiteSpace(first_name) ? $"and first_name like '%{first_name}%' " : string.Empty) +
(!string.IsNullOrWhiteSpace(last_name) ? $"and last_name like '%{last_name}%' " : string.Empty) +
(!string.IsNullOrWhiteSpace(mobile) ? $"and mobile like '%{mobile}%' " : string.Empty);
var lst = await _dBDapperRepository.RunQueryAsync <AccountPaginatedModel>(query); //await _dBRepository.accounts.Where(l => l.account_type_id == 1).AsNoTracking().ToListAsync();
return(Ok(new CoreResponse()
{
is_success = true, data = lst
}));
}
catch (Exception ex)
{
return(Ok(new CoreResponse()
{
is_success = false, data = null, dev_message = ex.Message
}));
}
}
public async Task <ActionResult> GetClientSaleInvoiceDetails(long customer_id)
{
try
{
var query = $@"
select h.id as invoice_id,d.id as sale_invoice_details_id,h.create_date as invoice_date,d.product_name,d.session_qty,d.session_used,(d.session_qty - d.session_used) > 0 as can_use
from public.sale_invoice_details d
join public.sale_invoice_headers h on d.invoice_id = h.id
where 1=1
and d.is_deleted='0'
and h.account_id = {customer_id}
order by d.id desc ";
var lst = await _dBDapperRepository.RunQueryAsync <ClientSessionUsageReportModel>(query);
return(Ok(new CoreResponse()
{
is_success = true, data = lst
}));
}
catch (Exception ex)
{
return(Ok(new CoreResponse()
{
is_success = false, data = null, dev_message = ex.Message
}));
}
}
public async Task <ActionResult> GetSaleInvoicesPaginate(long customer_id, string mobile, string from_date, string to_date, string first_name, string last_name, int page = 1, int pagesize = 10)
{
try
{
if (!ModelState.IsValid)
{
return(BadRequest());
}
var query = $@"
select count(1) OVER() AS row_count,h.*,a.title as account_title, a.first_name, a.last_name,d.product_id, d.product_name,
d.qty, d.price as product_price, d.reduction_percent, d.reduction_price, d.session_qty,
d.session_reserved, d.session_used,p.sale_invoice_payment_type_id, p.price aS payment_price,
p.description,pt.title as payment_type_title
from public.sale_invoice_headers h
left join public.sale_invoice_details d on h.id = d.invoice_id
left join public.sale_invoice_payments p on h.id = p.invoice_id
left join public.sale_invoice_payment_types pt on p.sale_invoice_payment_type_id = pt.id
left join public.accounts a on h.account_id = a.Id where h.is_deleted = '0' and d.is_deleted = '0' "
+ (!string.IsNullOrWhiteSpace(mobile) ? $"and a.mobile like '%{mobile}%'" : string.Empty)
+ (customer_id > 0 ? $"and a.id = {customer_id}" : string.Empty)
+ (!string.IsNullOrWhiteSpace(from_date) ? $"and h.create_date >= '{from_date.ToDateTimeStr()}'" : string.Empty)
+ (!string.IsNullOrWhiteSpace(to_date) ? $"and h.create_date <= '{to_date.ToDateTimeStr()}'" : string.Empty)
+ (!string.IsNullOrWhiteSpace(first_name) ? $"and a.first_name like '%{first_name}%'" : string.Empty)
+ (!string.IsNullOrWhiteSpace(last_name) ? $"and a.last_name like '%{last_name}%'" : string.Empty)
+ @$ "ORDER BY id
LIMIT {pagesize}
OFFSET({pagesize}*({page}-1)) ";
var lst = await _dBDapperRepository.RunQueryAsync <SaleInvoiceReportPaginateModel>(query);
return(Ok(new CoreResponse()
{
is_success = true, data = lst
}));
}
catch (Exception ex)
{
return(Ok(new CoreResponse()
{
is_success = false, data = null, dev_message = ex.Message
}));
}
}
public async Task <ActionResult> GetAccountsPaginate(string first_name, string last_name, string title, string mobile, int account_type_id, int page = 1, int pagesize = 10)
{
try
{
if (!ModelState.IsValid)
{
return(BadRequest());
}
var query = $@" select count(1) OVER() AS row_count,a.*,at.title as account_type_title
from public.accounts a
join public.account_types at on a.account_type_id = at.id
where account_type_id != 1 " +
(!string.IsNullOrWhiteSpace(first_name) ? $" and first_name like '%{first_name}%' " : string.Empty) +
(!string.IsNullOrWhiteSpace(last_name) ? $" and last_name like '%{last_name}%' " : string.Empty) +
(!string.IsNullOrWhiteSpace(mobile) ? $" and mobile like '%{mobile}%' " : string.Empty) +
(!string.IsNullOrWhiteSpace(title) ? $" and a.title like '%{title}%' " : string.Empty) +
(account_type_id > 0 ? $" and a.account_type_id = {account_type_id} " : string.Empty) +
@$ " ORDER BY id
LIMIT {pagesize}
OFFSET ({pagesize} * ({page}-1)) ";
var lst = await _dBDapperRepository.RunQueryAsync <AccountPaginatedModel>(query);
return(Ok(new CoreResponse()
{
is_success = true, data = lst, total_items = lst.First()?.row_count, current_page = page, total_pages = (lst.First()?.row_count / pagesize) + 1
}));
//return Ok(new CoreResponse() { is_success = true, data = lst });
}
catch (Exception ex)
{
return(Ok(new CoreResponse()
{
is_success = false, data = null, dev_message = ex.Message
}));
}
}
