private List <DriverInfo> GetDriverInfo()
{
var result = new List <DriverInfo>();
var dbConnectionSQL = DbConnectionSQL.Instance();
string sql = @" select D.Id as DriverId, D.Name as DriverName, DT.Type as DriverType, DT.Id as DriverTypeId from driver D with (nolock)
inner join DriverType DT with (nolock)
on D.DriverTypeId = DT.Id
where D.Status != 2";
var dataTable = dbConnectionSQL.ExecuteRawSql(sql);
result = dbConnectionSQL.ConvertDataTableToList <DriverInfo>(dataTable);
return(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);
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddDbContext <ApplicationDbContext>(options =>
options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"),
b => b.MigrationsAssembly("Infrastructure.EF")));
//Initial connection string SQL.
DbConnectionSQL.Instance(Configuration);
#region Swagger
services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new Info {
Title = "Transportation APIs", Version = "V1"
});
var security = new Dictionary <string, IEnumerable <string> >
{
{ "Bearer", new string[] { } },
};
c.AddSecurityDefinition("Bearer", new ApiKeyScheme
{
Description = "JWT Authorization header using the Bearer scheme. Example: \"Authorization: Bearer {token}\"",
Name = "Authorization",
In = "header",
Type = "apiKey"
});
c.AddSecurityRequirement(security);
});
#endregion
#region Identity
var builder = services.AddIdentityCore <ApplicationUser>(o =>
{
// configure identity options
o.Password.RequireDigit = false;
o.Password.RequireLowercase = false;
o.Password.RequireUppercase = false;
o.Password.RequireNonAlphanumeric = false;
o.Password.RequiredLength = 6;
});
builder = new IdentityBuilder(builder.UserType, typeof(ApplicationRole), builder.Services);
builder.AddEntityFrameworkStores <ApplicationDbContext>().AddDefaultTokenProviders();
builder.AddRoleManager <RoleManager <ApplicationRole> >();
#endregion
#region JWT
// Get options from app settings
var jwtAppSettingOptions = Configuration.GetSection(nameof(JwtIssuerOptions));
// Configure JwtIssuerOptions
services.Configure <JwtIssuerOptions>(options =>
{
options.Issuer = jwtAppSettingOptions[nameof(JwtIssuerOptions.Issuer)];
options.Audience = jwtAppSettingOptions[nameof(JwtIssuerOptions.Audience)];
options.SigningCredentials = new SigningCredentials(_signingKey, SecurityAlgorithms.HmacSha256);
});
var tokenValidationParameters = new TokenValidationParameters
{
ValidateIssuer = true,
ValidIssuer = jwtAppSettingOptions[nameof(JwtIssuerOptions.Issuer)],
ValidateAudience = true,
ValidAudience = jwtAppSettingOptions[nameof(JwtIssuerOptions.Audience)],
ValidateIssuerSigningKey = true,
IssuerSigningKey = _signingKey,
RequireExpirationTime = false,
ValidateLifetime = true,
ClockSkew = TimeSpan.Zero
};
//services.AddAuthentication(options =>
//{
// options.DefaultAuthenticateScheme = JwtBearerDefaults.AuthenticationScheme;
// options.DefaultChallengeScheme = JwtBearerDefaults.AuthenticationScheme;
//})
services.AddAuthentication(JwtBearerDefaults.AuthenticationScheme)
.AddJwtBearer(configureOptions =>
{
configureOptions.ClaimsIssuer = jwtAppSettingOptions[nameof(JwtIssuerOptions.Issuer)];
configureOptions.TokenValidationParameters = tokenValidationParameters;
configureOptions.SaveToken = true;
});
// api user claim policy
services.AddAuthorization(options =>
{
options.AddPolicy("ApiUser", policy => policy.RequireClaim(Constants.Strings.JwtClaimIdentifiers.Rol, Constants.Strings.JwtClaims.ApiAccess));
options.AddPolicy(CommonConstants.Authorize.PolicyAdmin, policy => policy.RequireRole(CommonConstants.Authorize.Admin));
options.AddPolicy(CommonConstants.Authorize.Staff, policy => policy.RequireRole(CommonConstants.Authorize.Admin, CommonConstants.Authorize.Staff));
});
#endregion
#region CORS
var domains = new List <string>();
Configuration.GetSection("Domains").Bind(domains);
services.AddCors(options =>
{
options.AddPolicy("AllowAnyOrigin",
build => build
.WithOrigins(domains.ToArray())
.AllowAnyMethod()
.AllowAnyHeader());
});
#endregion
AddServices(services);
RegisterMapper(services);
services.AddMvc()
//Chinh format json reponse
.AddJsonOptions(options =>
{
options.SerializerSettings.ContractResolver = new DefaultContractResolver();
})
.SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
}
