public async Task <ResponseResult> UpdateMultiple(List <UpdatePrice> prices)
{
var result = new ResponseResult();
try
{
foreach (var item in prices)
{
var price = await _repo.GetById(item.PriceId);
price.Money = item.Money;
price.UpdatedDate = DateTime.UtcNow;
price.UpdatedBy = contextAccessor.HttpContext.User.FindFirst(ClaimTypes.NameIdentifier).Value;
}
var saveChange = await _repo.SaveChanges(result);
if (saveChange)
{
ResponseResultHelper.MakeSuccess(result, "Lưu bảng giá thành công");
}
}
catch (Exception ex)
{
ResponseResultHelper.MakeException(result, ex);
}
return(await Task.FromResult(result));
}
public async Task <ResponseResult> CheckBangKe(DateTime date)
{
var result = new ResponseResult();
try
{
var transportations = await _transportationRepo.Where(e => e.TransportDate.Month == date.Month &&
e.TransportDate.Year == date.Year &&
e.Status == CommonConstants.Status.Active);
transportations = transportations.OrderBy(e => e.TransportDate);
if (transportations.Count() > 0)
{
ResponseResultHelper.MakeSuccess(result);
}
else
{
ResponseResultHelper.MakeFailure(result, $"Tháng {date.Month}/{date.Year} không có dữ liệu");
}
}
catch (Exception ex)
{
ResponseResultHelper.MakeException(result, ex);
}
return(result);
}
public async Task <ResponseResult> Filter(long distanceId)
{
var result = new ResponseResult();
var pagination = new Pagination();
try
{
var prices = await _repo.Where(p => p.DistanceId == distanceId && p.Status != CommonConstants.Status.Deleted);
var data = prices.ToList();
data.ForEach(p => {
_repo.EntryReference(p, e => e.Capacity);
_repo.EntryReference(p, e => e.Distance);
});
pagination.Records = data.Select(p => _mapper.Map <Price, PriceGetAllViewModel>(p));
result.Data = pagination;
ResponseResultHelper.MakeSuccess(result);
}
catch (Exception ex)
{
ResponseResultHelper.MakeException(result, ex);
}
return(await Task.FromResult(result));
}
public async Task <ResponseResult> UpdateTransportationMoney(DateTime fromDate, DateTime toDate)
{
var result = new ResponseResult();
var trackings = new List <UpdateTransportationMoney>();
try
{
if (fromDate.Date > toDate.Date)
{
ResponseResultHelper.MakeFailure(result, "Từ ngày phải bé hơn Đến ngày");
return(result);
}
if (toDate.Month != fromDate.Month)
{
ResponseResultHelper.MakeFailure(result, "Pham vi phải trong vòng 1 tháng");
return(result);
}
var transportations = _repo.AsQueryable().Where(t => t.Status == CommonConstants.Status.Active && fromDate.Date <= t.TransportDate.Date && t.TransportDate.Date <= toDate.Date).ToList();
if (!transportations.Any())
{
ResponseResultHelper.MakeFailure(result, "Không tìm thấy dữ liệu");
return(result);
}
var datetime = DateTime.UtcNow;
foreach (var transportation in transportations)
{
var obj = new UpdateTransportationMoney();
obj.TransportationId = transportation.Id;
obj.OldMoney = transportation.Money;
obj.TransportationDate = transportation.TransportDate;
var companyIds = JsonConvert.DeserializeObject <List <int> >(transportation.CompanyIds);
var newMoney = await GenerateMoney(companyIds, transportation.CarId);
transportation.Money = newMoney.Data;
transportation.UpdatedDate = datetime;
transportation.UpdatedBy = contextAccessor.HttpContext.User.FindFirst(ClaimTypes.NameIdentifier).Value;
obj.NewMoney = newMoney.Data;
trackings.Add(obj);
}
var saveChange = await _repo.SaveChanges(result);
if (saveChange)
{
result.Data = trackings;
ResponseResultHelper.MakeSuccess(result, "Cập nhập giá thành công");
}
}
catch (Exception ex)
{
ResponseResultHelper.MakeException(result, ex);
}
return(result);
}
public async Task <ResponseResult> SearchCompany(string searchKey)
{
var result = new ResponseResult();
try
{
var entity = await _repo.Where(c => c.Status == CommonConstants.Status.Active && StringHelper.Contains((c.Code + c.Name), searchKey));
result.Data = entity.Take(CommonConstants.Select.Take).ToList();
ResponseResultHelper.MakeSuccess(result);
}
catch (Exception ex)
{
ResponseResultHelper.MakeException(result, ex);
}
return(await Task.FromResult(result));
}
public async Task <ResponseResult> GenerateMoney(List <int> companyIds, long carId)
{
var result = new ResponseResult();
result.Data = 0;
try
{
var generateMoney = await GetTransportationMoney(companyIds, carId);
if (generateMoney.Any())
{
result.Data = generateMoney.OrderBy(p => p.Money).Last().Money;
}
ResponseResultHelper.MakeSuccess(result);
}
catch (Exception ex)
{
ResponseResultHelper.MakeException(result, ex);
}
return(await Task.FromResult(result));
}
public async Task <ResponseResult> BangKe(DateTime date)
{
var result = new ResponseResult();
try
{
//var query = await _transportationRepo.Where(e => e.TransportDate.Month == date.Month
// && e.TransportDate.Year == date.Year
// && e.Status == CommonConstants.Status.Active);
//var transportations = query.OrderBy(e => e.DocumentNumber).ToList();
#region Call StoreProcedure
var dbConnectionSQL = DbConnectionSQL.Instance();
var dbCommand = dbConnectionSQL.GetCommand(dbConnectionSQL.GetConnection(), "Bangke", CommandType.StoredProcedure);
var parameters = new List <DbParameter>();
var dateParam = dbCommand.CreateParameter();
dateParam.DbType = DbType.Date;
dateParam.ParameterName = "Date";
dateParam.Value = date;
parameters.Add(dateParam);
var dataTable = dbConnectionSQL.ExecuteTable("Bangke", parameters);
var transportations = dbConnectionSQL.ConvertDataTableToList <ExportReportViewModel>(dataTable);
#endregion
var title = $"BẢNG KÊ VẬN CHUYỂN THÁNG {date.Month}";
int days = DateTime.DaysInMonth(date.Year, date.Month);
var text = $"TOTAL: TỪ NGÀY 1/{date.Month} ĐẾN NGÀY {days}/{date.Month}";
var filePath = $"{_webRootPath}\\ReportTemplate\\Bangke.xlsx";
using (Stream stream = File.OpenRead(filePath))
{
using (var excelPackage = new ExcelPackage(stream))
{
var workSheets = excelPackage.Workbook.Worksheets;
var mainSheet = workSheets.First();
FillWorkSheet(mainSheet, transportations, title, text, true);
//excelPackage.Save();
List <DriverInfo> driverInfos = GetDriverInfo();
#region Export driver salary
List <ExportDriverSalaryViewModel> driverSalaries = BuildDriverSalary(driverInfos, transportations);
var templateDriverSalary = workSheets[2];
FillWorkSheetDriverSalary(templateDriverSalary, driverSalaries);
#endregion
#region Export reports by Car Number
var transportationsGroupbyCarNumber = transportations.GroupBy(t => t.CarNumber, (key, trans) => new { CarNumber = key, Transportations = trans.ToList(), DriverHeader = GetDriverForHeaderColumn(driverInfos, trans.ToList()) });
var templateSheetCarNumber = workSheets[1];
foreach (var item in transportationsGroupbyCarNumber)
{
var sheetCarNumber = workSheets.Add(item.CarNumber, templateSheetCarNumber);
FillWorkSheet(sheetCarNumber, item.Transportations, title, text, false, item.DriverHeader);
}
workSheets.Delete(templateSheetCarNumber);
mainSheet.Select();
#endregion
var returnStream = new MemoryStream();
excelPackage.SaveAs(returnStream);
returnStream.Position = 0;
result.Data = returnStream;
}
}
ResponseResultHelper.MakeSuccess(result);
}
catch (Exception ex)
{
ResponseResultHelper.MakeException(result, ex);
}
return(result);
}
public async Task <ResponseResult> Filter(DateTime fromDate, DateTime toDate)
{
var result = new ResponseResult();
try
{
if (fromDate.Date > toDate.Date)
{
ResponseResultHelper.MakeFailure(result, "Từ ngày phải bé hơn Đến ngày");
return(result);
}
if ((toDate.Date - fromDate.Date).TotalDays > 92)
{
ResponseResultHelper.MakeFailure(result, "Pham vi lọc phải bé hơn 3 tháng");
return(result);
}
//var query = await _repo.Where(tr => fromDate.Date <= tr.TransportDate.Date && tr.TransportDate.Date <= toDate.Date && tr.Status != CommonConstants.Status.Deleted);
//var transportations = query.OrderBy(tr=>tr.DocumentNumber).ToList();
//var transportationGetAllViewModels = new List<TransportationGetAllViewModel>();
//foreach (var transportation in transportations)
//{
// _repo.EntryReference(transportation, e => e.Car);
// _repo.EntryReference(transportation, e => e.DriverPrimary);
// _repo.EntryReference(transportation, e => e.DriverSecondary);
// var newRecord = _mapper.Map<Transportation, TransportationGetAllViewModel>(transportation);
// var companiesString = new List<string>();
// var companyIds = JsonConvert.DeserializeObject<List<int>>(transportation.CompanyIds);
// foreach (var companyId in companyIds)
// {
// var company = await _companyRepo.GetById(companyId);
// if (company != null)
// {
// companiesString.Add($"{company.Code}|{company.Name}");
// }
// }
// newRecord.Companies = string.Join(" - ", companiesString);
// transportationGetAllViewModels.Add(newRecord);
//}
var dbConnectionSQL = DbConnectionSQL.Instance();
var dbCommand = dbConnectionSQL.GetCommand(dbConnectionSQL.GetConnection(), "TransportationFilter", CommandType.StoredProcedure);
var parameters = new List <DbParameter>();
var fromDateParam = dbCommand.CreateParameter();
fromDateParam.DbType = DbType.Date;
fromDateParam.ParameterName = "FromDate";
fromDateParam.Value = fromDate.Date;
parameters.Add(fromDateParam);
var toDateParam = dbCommand.CreateParameter();
toDateParam.DbType = DbType.Date;
toDateParam.ParameterName = "ToDate";
toDateParam.Value = toDate.Date;
parameters.Add(toDateParam);
var dataTable = dbConnectionSQL.ExecuteTable("TransportationFilter", parameters);
var transportationGetAllViewModels = dbConnectionSQL.ConvertDataTableToList <TransportationGetAllViewModel>(dataTable);
var pagination = new Pagination();
pagination.Records = transportationGetAllViewModels;
pagination.TotalRecords = transportationGetAllViewModels.Count;
result.Data = pagination;
ResponseResultHelper.MakeSuccess(result);
}
catch (Exception ex)
{
ResponseResultHelper.MakeException(result, ex);
}
return(result);
}
