public async Task <EmployeeVehicleReportDto> GetEmployeeVehicleReportAsync(string yearMonth)
{
var sql1 = " SELECT ROW_NUMBER() OVER( ORDER BY [Employee].[Id] ASC) AS No,[EmpName],[LicensePlate] " +
" , CASE WHEN VehicleTypeId = 1 THEN '1' ELSE '' END as VehicleType1 " +
" ,( SELECT ParkingFee FROM MstVehicleType WHERE Id = 1) as ParkingFee1 " +
" , CASE WHEN VehicleTypeId = 2 THEN '1' ELSE '' END as VehicleType2 " +
" ,( SELECT ParkingFee FROM MstVehicleType WHERE Id = 2) as ParkingFee2 " +
" ,[VehicleComment],Id " +
" FROM [dbo].[Employee] " +
" WHERE [PosId] not IN (1) " +
" AND SUBSTRING(convert(varchar, [EmpStartDate], 112), 1, 6) < @yearMonth " +
" AND (SUBSTRING(convert(varchar, [EmpEndDate], 112), 1, 6) >= @yearMonth OR [EmpEndDate] is null)";
var lst1 = await SqlMapper.QueryAsync <EmployeeVehicleDetailReportDto>(Connection, sql1, new { yearMonth = yearMonth }, transaction : Transaction);
var sql2 = " SELECT sum(AAA.VehicleType1) as CountVehicleType1 " +
" ,(AAA.ParkingFee1 * sum(AAA.VehicleType1)) as TotalParkingFee1 " +
" ,AAA.ParkingFee1 " +
" ,sum(AAA.VehicleType2) as CountVehicleType2 " +
" , (AAA.ParkingFee2 * sum(AAA.VehicleType2)) as TotalParkingFee2 " +
" , AAA.ParkingFee2 " +
" ,(AAA.ParkingFee1 * sum(AAA.VehicleType1)) + (AAA.ParkingFee2 * sum(AAA.VehicleType2)) as totalFee " +
" FROM ( " +
" SELECT " +
" CASE WHEN VehicleTypeId = 1 THEN 1 ELSE 0 END as VehicleType1 " +
" ,( SELECT ParkingFee FROM MstVehicleType WHERE Id = 1) as ParkingFee1 " +
" , CASE WHEN VehicleTypeId = 2 THEN 1 ELSE 0 END as VehicleType2 " +
" ,( SELECT ParkingFee FROM MstVehicleType WHERE Id = 2) as ParkingFee2 " +
" FROM [dbo].[Employee] WHERE [PosId] not IN (1) " +
" AND SUBSTRING(convert(varchar, [EmpStartDate], 112), 1, 6) < @yearMonth " +
" AND (SUBSTRING(convert(varchar, [EmpEndDate], 112), 1, 6) >= @yearMonth OR [EmpEndDate] is null) " +
" ) as AAA " +
" group by AAA.ParkingFee1,AAA.ParkingFee2";
var lst2 = await SqlMapper.QuerySingleOrDefaultAsync <EmployeeVehicleTotalReportDto>(Connection, sql2, new { yearMonth = yearMonth }, transaction : Transaction);
if (lst2 == null)
{
var sql = "SELECT * FROM MstVehicleType";
var _lstmst = await SqlMapper.QueryAsync <MstVehicleType>(Connection, sql, transaction : Transaction);
lst2 = new EmployeeVehicleTotalReportDto();
lst2.ParkingFee1 = _lstmst.Where(x => x.Id == 1).FirstOrDefault().ParkingFee.Value;
lst2.ParkingFee2 = _lstmst.Where(x => x.Id == 2).FirstOrDefault().ParkingFee.Value;
}
var result = new EmployeeVehicleReportDto()
{
VehicleDetail = lst1,
VehicleTotal = lst2
};
return(result);
}
public async Task <EmployeeVehicleReportDto> GetDownloadEmployeeVehicleReportAsync(IEnumerable <int> empIdString)
{
var inconditions = empIdString.Distinct().ToArray();
var sql1 = " SELECT ROW_NUMBER() OVER( ORDER BY [Employee].[Id] ASC) AS No,[EmpName],[LicensePlate] " +
" , CASE WHEN VehicleTypeId = 1 THEN '1' ELSE '' END as VehicleType1 " +
" ,( SELECT ParkingFee FROM MstVehicleType WHERE Id = 1) as ParkingFee1 " +
" , CASE WHEN VehicleTypeId = 2 THEN '1' ELSE '' END as VehicleType2 " +
" ,( SELECT ParkingFee FROM MstVehicleType WHERE Id = 2) as ParkingFee2 " +
" ,[VehicleComment] , Id" +
" FROM [dbo].[Employee] WHERE [Id] IN @ids ";
var lst1 = await SqlMapper.QueryAsync <EmployeeVehicleDetailReportDto>(Connection, sql1, new { ids = inconditions }, transaction : Transaction);
var sql2 = " SELECT sum(AAA.VehicleType1) as CountVehicleType1 " +
" ,(AAA.ParkingFee1 * sum(AAA.VehicleType1)) as TotalParkingFee1 " +
" ,sum(AAA.VehicleType2) as CountVehicleType2 " +
" , (AAA.ParkingFee2 * sum(AAA.VehicleType2)) as TotalParkingFee2 " +
" ,(AAA.ParkingFee1 * sum(AAA.VehicleType1)) + (AAA.ParkingFee2 * sum(AAA.VehicleType2)) as totalFee " +
" FROM ( " +
" SELECT " +
" CASE WHEN VehicleTypeId = 1 THEN 1 ELSE 0 END as VehicleType1 " +
" ,( SELECT ParkingFee FROM MstVehicleType WHERE Id = 1) as ParkingFee1 " +
" , CASE WHEN VehicleTypeId = 2 THEN 1 ELSE 0 END as VehicleType2 " +
" ,( SELECT ParkingFee FROM MstVehicleType WHERE Id = 2) as ParkingFee2 " +
" FROM [dbo].[Employee] WHERE [Id] IN @ids " +
" ) as AAA " +
" group by AAA.ParkingFee1,AAA.ParkingFee2";
var lst2 = await SqlMapper.QuerySingleOrDefaultAsync <EmployeeVehicleTotalReportDto>(Connection, sql2,
new { ids = inconditions }, transaction : Transaction);
var result = new EmployeeVehicleReportDto()
{
VehicleDetail = lst1,
VehicleTotal = lst2
};
return(result);
}
