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); }