public void GetTotalPages(PurchaseOrderFilter filter)
{
var context = Context();
filter.TotalRow = context.Bill.Where(u => u.IdNavigation.OrganisationId == filter.OrganisationId &&
u.IdNavigation.ExpenseStatusId != (int)ExpenseStatusEnum.Delete &&
(filter.PurchaseOrderStatusId == 0 || u.IdNavigation.ExpenseStatusId == filter.PurchaseOrderStatusId) &&
(filter.VendorId == 0 || u.IdNavigation.VendorId == filter.VendorId) &&
(
(filter.DateTypeFilter.Id == (int)BillDateTypeFilterEnum.All &&
(filter.FromDate == null || u.IdNavigation.Date >= filter.FromDate || u.IdNavigation.DeliveryDate >= filter.FromDate) &&
(filter.ToDate == null || u.IdNavigation.Date <= filter.ToDate || u.IdNavigation.DeliveryDate <= filter.ToDate)) ||
(
filter.DateTypeFilter.Id == (int)BillDateTypeFilterEnum.Date &&
(filter.FromDate == null || u.IdNavigation.Date >= filter.FromDate) &&
(filter.ToDate == null || u.IdNavigation.Date <= filter.ToDate)
) ||
(
filter.DateTypeFilter.Id == (int)BillDateTypeFilterEnum.DueDate &&
(filter.FromDate == null || u.IdNavigation.DeliveryDate >= filter.FromDate) &&
(filter.ToDate == null || u.IdNavigation.DeliveryDate <= filter.ToDate)
)
) &&
(string.IsNullOrEmpty(filter.SearchText) || u.IdNavigation.Number.Contains(filter.SearchText))
).Count();
}
public List <ExpenseStatus> GetExpenseStatuses(PurchaseOrderFilter filter)
{
var context = Context();
var statuses = context.VendorExpense.Where(u => u.IdNavigation.OrganisationId == filter.OrganisationId &&
u.IdNavigation.ExpenseStatusId != (int)ExpenseStatusEnum.Delete &&
(filter.VendorId == 0 || u.IdNavigation.VendorId == filter.VendorId) &&
(filter.FromDate == null || u.IdNavigation.Date >= filter.FromDate) &&
(filter.ToDate == null || u.IdNavigation.Date <= filter.ToDate) &&
(string.IsNullOrEmpty(filter.SearchText) || u.IdNavigation.Number.Contains(filter.SearchText))
)
.GroupBy(u => new { u.IdNavigation.ExpenseStatusId })
.Select(u => new
{
Id = u.Key.ExpenseStatusId,
Count = u.Count()
}).ToList();
var result = context.ExpenseStatus.Where(u => u.Id != (int)ExpenseStatusEnum.Delete).ToList()
.Select(u => new ExpenseStatus
{
Id = u.Id,
Name = u.Name,
Count = statuses.Any(c => c.Id == u.Id) ? statuses.FirstOrDefault(c => c.Id == u.Id).Count : 0
}).ToList();
result.Insert(0, new ExpenseStatus
{
Id = 0,
Name = "All",
Count = statuses.Sum(u => u.Count)
});
return(result);
}
public List <Expense> GetExpenses(PurchaseOrderFilter filter)
{
var context = Context();
var orders = context.VendorExpense.Where(u => u.IdNavigation.OrganisationId == filter.OrganisationId &&
u.IdNavigation.ExpenseStatusId != (int)ExpenseStatusEnum.Delete &&
(filter.PurchaseOrderStatusId == 0 || u.IdNavigation.ExpenseStatusId == filter.PurchaseOrderStatusId) &&
(filter.VendorId == 0 || u.IdNavigation.VendorId == filter.VendorId) &&
(filter.FromDate == null || u.IdNavigation.Date >= filter.FromDate) &&
(filter.ToDate == null || u.IdNavigation.Date <= filter.ToDate) &&
(string.IsNullOrEmpty(filter.SearchText) || u.IdNavigation.Number.Contains(filter.SearchText))
).OrderByDescending(u => u.IdNavigation.Number)
.Skip((filter.Page - 1) * filter.PageSize)
.Take(filter.PageSize)
.Select(u => new Expense
{
ApprovedBy = u.IdNavigation.ApprovedBy,
ApprovedDate = u.IdNavigation.ApprovedDate,
BaseCurrencyExchangeRate = u.IdNavigation.BaseCurrencyExchangeRate.Value,
BilledBy = u.IdNavigation.BilledBy,
BilledDate = u.IdNavigation.BilledDate,
Created = u.IdNavigation.Created,
CreatedBy = u.IdNavigation.CreatedBy,
Currency = new Currency
{
Id = u.IdNavigation.CurrencyId,
Code = u.IdNavigation.Currency.Code,
Name = u.IdNavigation.Currency.Name,
Symbole = u.IdNavigation.Currency.Symbole
},
Date = u.IdNavigation.Date,
DeliveryDate = u.IdNavigation.DeliveryDate,
Note = u.IdNavigation.Note,
Id = u.Id,
CurrencyId = u.IdNavigation.CurrencyId,
OrderNumber = u.IdNavigation.Number,
OrganisationId = u.IdNavigation.OrganisationId,
ExpenseStatus = new ExpenseStatus
{
Id = u.IdNavigation.ExpenseStatusId,
Name = u.IdNavigation.ExpenseStatus.Name,
},
ExpenseStatusId = u.IdNavigation.ExpenseStatusId,
RefNo = u.IdNavigation.RefNo,
TaxCurrencyExchangeRate = u.IdNavigation.TaxCurrencyExchangeRate.Value,
Total = u.IdNavigation.Total,
TotalIncludeTax = u.IdNavigation.TotalIncludeTax,
VendorId = u.IdNavigation.VendorId,
Vendor = new Vendor
{
Id = u.IdNavigation.VendorId,
DisplayName = u.IdNavigation.Vendor.DisplayName
}
}).ToList();
return(orders);
}
public IActionResult Get([FromQuery] PurchaseOrderFilter parameters = null)
{
try
{
var model = repository.GetView(parameters);
var modelList = model.ToList();
return(Ok(model));
}
catch (Exception ex)
{
logger.LogError(ex.GetExceptionMessages());
return(StatusCode(StatusCodes.Status500InternalServerError, Constants.ErrorMessages.FetchError));
}
}
public IActionResult GetExpenseStatuses([FromBody] PurchaseOrderFilter filter)
{
filter.OrganisationId = billService.OrganisationId;
return(Ok(billService.GetExpenseStatuses(filter)));
}
public IActionResult GetTotalPages([FromBody] PurchaseOrderFilter filter)
{
filter.OrganisationId = billService.OrganisationId;
billService.GetTotalPages(filter);
return(Ok(filter));
}
public List <Expense> GetPurchaseOrders(PurchaseOrderFilter filter)
{
var context = Context();
var khmer = IsKhmer;
var orders = context.Bill.Where(u => u.IdNavigation.OrganisationId == filter.OrganisationId &&
u.IdNavigation.ExpenseStatusId != (int)ExpenseStatusEnum.Delete &&
(filter.PurchaseOrderStatusId == 0 || u.IdNavigation.ExpenseStatusId == filter.PurchaseOrderStatusId) &&
(filter.VendorId == 0 || u.IdNavigation.VendorId == filter.VendorId) &&
(
(filter.DateTypeFilter.Id == (int)BillDateTypeFilterEnum.All &&
(filter.FromDate == null || u.IdNavigation.Date >= filter.FromDate || u.IdNavigation.DeliveryDate >= filter.FromDate) &&
(filter.ToDate == null || u.IdNavigation.Date <= filter.ToDate || u.IdNavigation.DeliveryDate <= filter.ToDate)) ||
(
filter.DateTypeFilter.Id == (int)BillDateTypeFilterEnum.Date &&
(filter.FromDate == null || u.IdNavigation.Date >= filter.FromDate) &&
(filter.ToDate == null || u.IdNavigation.Date <= filter.ToDate)
) ||
(
filter.DateTypeFilter.Id == (int)BillDateTypeFilterEnum.DueDate &&
(filter.FromDate == null || u.IdNavigation.DeliveryDate >= filter.FromDate) &&
(filter.ToDate == null || u.IdNavigation.DeliveryDate <= filter.ToDate)
)
) &&
(string.IsNullOrEmpty(filter.SearchText) || u.IdNavigation.Number.Contains(filter.SearchText))
).OrderByDescending(u => u.IdNavigation.Number)
.Skip((filter.Page - 1) * filter.PageSize)
.Take(filter.PageSize)
.Select(u => new Expense
{
ApprovedBy = u.IdNavigation.ApprovedBy,
ApprovedDate = u.IdNavigation.ApprovedDate,
BaseCurrencyExchangeRate = u.IdNavigation.BaseCurrencyExchangeRate.Value,
BilledBy = u.IdNavigation.BilledBy,
BilledDate = u.IdNavigation.BilledDate,
Created = u.IdNavigation.Created,
CreatedBy = u.IdNavigation.CreatedBy,
Currency = new Currency
{
Id = u.IdNavigation.CurrencyId,
Code = u.IdNavigation.Currency.Code,
Name = u.IdNavigation.Currency.Name,
Symbole = u.IdNavigation.Currency.Symbole
},
Date = u.IdNavigation.Date,
DeliveryDate = u.IdNavigation.DeliveryDate,
Note = u.IdNavigation.Note,
Id = u.Id,
CurrencyId = u.IdNavigation.CurrencyId,
OrderNumber = u.IdNavigation.Number,
OrganisationId = u.IdNavigation.OrganisationId,
ExpenseStatus = new ExpenseStatus
{
Id = u.IdNavigation.ExpenseStatusId,
Name = khmer?u.IdNavigation.ExpenseStatus.NameKh: u.IdNavigation.ExpenseStatus.Name,
},
ExpenseStatusId = u.IdNavigation.ExpenseStatusId,
RefNo = u.IdNavigation.RefNo,
TaxCurrencyExchangeRate = u.IdNavigation.TaxCurrencyExchangeRate.Value,
Total = u.IdNavigation.Total,
TotalIncludeTax = u.IdNavigation.TotalIncludeTax,
VendorId = u.IdNavigation.VendorId,
Vendor = new Vendor
{
Id = u.IdNavigation.VendorId,
DisplayName = u.IdNavigation.Vendor.DisplayName
},
CloseDate = u.IdNavigation.CloseDate
}).ToList();
var orderIs = orders.Select(u => u.Id).ToList();
var closeAttachments = context.ExpenseAttachentFile.Where(u => u.IsFinalOfficalFile == true && orderIs.Any(t => t == u.ExpenseId))
.Select(u => new Attachment
{
ExpenseId = u.ExpenseId,
FileName = u.FileName,
FileUrl = u.FileUrl,
IsFinalOfficalFile = true
}).ToList();
foreach (var order in orders)
{
var attachment = closeAttachments.FirstOrDefault(u => u.ExpenseId == order.Id);
order.HasCloseDoc = attachment != null;
order.CloseAttachment = attachment == null ? new Attachment() : attachment;
}
return(orders);
}
public IActionResult Gets([FromBody] PurchaseOrderFilter filter)
{
filter.OrganisationId = purchaseOrderService.OrganisationId;
return(Ok(purchaseOrderService.GetPurchaseOrders(filter)));
}
public SqlRawParameter GetSqlRawParameter(PurchaseOrderFilter parameters)
{
var sqlrawParams = new SqlRawParameter();
var sqlQry = new StringBuilder();
sqlQry.AppendLine(@"SELECT * FROM PurchaseOrderViews");
if (parameters == null)
{
sqlrawParams.SqlQuery = sqlQry.ToString();
sqlrawParams.SqlParameters = new List <SqlParameter>();
return(sqlrawParams);
}
var whereClauses = new List <string>();
var sqlParams = new List <SqlParameter>();
if (!String.IsNullOrEmpty(parameters.PONum))
{
sqlParams.Add(new SqlParameter(nameof(parameters.PONum).Parametarize(), parameters.PONum));
whereClauses.Add($"{nameof(parameters.PONum)} = {nameof(parameters.PONum).Parametarize()}");
}
else
{
if (parameters.DTEffectivity.HasValue)
{
sqlParams.Add(new SqlParameter(nameof(parameters.DTEffectivity).Parametarize(), parameters.DTEffectivity));
whereClauses.Add($"Year({nameof(parameters.DTEffectivity)}) = Year({nameof(parameters.DTEffectivity).Parametarize()})");
whereClauses.Add($"Month({nameof(parameters.DTEffectivity)}) = Month({nameof(parameters.DTEffectivity).Parametarize()})");
}
if (parameters.SupplierId > 0)
{
sqlParams.Add(new SqlParameter(nameof(parameters.SupplierId).Parametarize(), parameters.SupplierId));
whereClauses.Add($"{nameof(parameters.SupplierId)} = {nameof(parameters.SupplierId).Parametarize()}");
}
if (parameters.RawMaterialId > 0)
{
sqlParams.Add(new SqlParameter(nameof(parameters.RawMaterialId).Parametarize(), parameters.RawMaterialId));
whereClauses.Add($"{nameof(parameters.RawMaterialId)} = {nameof(parameters.RawMaterialId).Parametarize()}");
}
if (!String.IsNullOrEmpty(parameters.POType))
{
sqlParams.Add(new SqlParameter(nameof(parameters.POType).Parametarize(), parameters.POType));
whereClauses.Add($"{nameof(parameters.POType)} = {nameof(parameters.POType).Parametarize()}");
}
if (parameters.IsActive.HasValue)
{
sqlParams.Add(new SqlParameter(nameof(parameters.IsActive).Parametarize(), parameters.IsActive));
whereClauses.Add($"{nameof(parameters.IsActive)} = {nameof(parameters.IsActive).Parametarize()}");
}
}
if (whereClauses.Count > 0)
{
sqlQry.AppendLine(" WHERE ");
sqlQry.AppendLine(String.Join(" AND ", whereClauses.ToArray()));
}
return(new SqlRawParameter()
{
SqlParameters = sqlParams, SqlQuery = sqlQry.ToString()
});
}
public IQueryable <PurchaseOrderView> GetView(PurchaseOrderFilter parameters = null)
{
var sqlRawParams = GetSqlRawParameter(parameters);
return(dbContext.PurchaseOrderViews.FromSqlRaw(sqlRawParams.SqlQuery, sqlRawParams.SqlParameters.ToArray()).AsNoTracking());
}
public IActionResult Gets([FromBody] PurchaseOrderFilter filter)
{
filter.OrganisationId = vendorExpenseService.OrganisationId;
return(Ok(vendorExpenseService.GetExpenses(filter)));
}
