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