public KeyValuePair<Guid, string> GetWinner(int eventId, List<Guid> exclusionList) { var rsvpUsers = _context.Rsvps.Where(w => w.EventId == eventId).Where(w => !exclusionList.Contains(w.UserId)).OrderBy(o => o.RsvpTime) .Include(r => r.User);//.Select(s => s.User.FirstName + " " + s.User.LastName); int totalRemainingUsers = rsvpUsers.Count(); var results = new KeyValuePair<Guid, string>(); if (totalRemainingUsers <= 0) { return new KeyValuePair<Guid, string>(Guid.Empty, "No More Users"); } var index = new Random().Next(rsvpUsers.Count()); var user = rsvpUsers.Skip(index).FirstOrDefault().User; results = new KeyValuePair<Guid, string>(user.Id, user.FirstName + " " + user.LastName); return results; }
protected void Page_Load(object sender, EventArgs e) { var ds = (SQLDataService)DataServiceProvider.DataService; var склады = ds.Query<Склад>(Склад.Views.СкладL).ToList(); var всеТоварыНаСкладах = ds.Query<ТоварНаСкладе>(ТоварНаСкладе.Views.ТоварНаСкладеE).ToList(); var результат = new List<ТоварНаСкладе>(); foreach (var склад in склады) { var товарыНаСкладе = всеТоварыНаСкладах.Where(x => x.Склад.__PrimaryKey.Equals(склад.__PrimaryKey)).ToList(); var максКоличество = товарыНаСкладе.Select(x => x.Количество).Max(); var максКоличествоТоваров = товарыНаСкладе.Where(x => x.Количество == максКоличество).ToList(); while (максКоличествоТоваров.Count >= 1) { результат.Add(максКоличествоТоваров.FirstOrDefault()); максКоличествоТоваров.Remove(максКоличествоТоваров.FirstOrDefault()); } } dataList.DataSource = результат; dataList.DataBind(); }
public static IEnumerable<Employee> GetStNewbieList(int? idDepartment = null, bool userCanViewHiddenEmps = false) { SqlParameter pIdEmpState = new SqlParameter() { ParameterName = "id_emp_state", SqlValue = EmpState.GetNewbieState().Id, SqlDbType = SqlDbType.Int }; SqlParameter pIdDepartment = new SqlParameter() { ParameterName = "id_department", SqlValue = idDepartment, SqlDbType = SqlDbType.Int }; var dt = Db.Stuff.ExecuteQueryStoredProcedure("get_other_employee_list", pIdEmpState, pIdDepartment); var lst = new List<Employee>(); foreach (DataRow row in dt.Rows) { var emp = new Employee(row); if (emp.IsHidden && !userCanViewHiddenEmps) continue; lst.Add(emp); } return lst; }
public static IEnumerable<Employee> GetNewbieList(DateTime dateCreate) { SqlParameter pDateCreate = new SqlParameter() { ParameterName = "date_came", SqlValue = dateCreate, SqlDbType = SqlDbType.Date }; var dt = Db.Stuff.ExecuteQueryStoredProcedure("get_employees_newbie", pDateCreate); var lst = new List<Employee>(); foreach (DataRow row in dt.Rows) { var emp = new Employee(row); lst.Add(emp); } return lst; }
public static IEnumerable<Employee> GetMonthBirthdayList(int month) { SqlParameter pMonth = new SqlParameter() { ParameterName = "month", SqlValue = month, SqlDbType = SqlDbType.Int }; var dt = Db.Stuff.ExecuteQueryStoredProcedure("get_employees_birthday", pMonth); var lst = new List<Employee>(); foreach (DataRow row in dt.Rows) { var emp = new Employee(row); lst.Add(emp); } return lst; }
public static IEnumerable<Employee> GetList(int? idDepartment = null, bool getPhoto = false, int? idCity = null, int? idManager = null, bool userCanViewHiddenEmps = false, bool showHidden = true, int? idBudget = null) { SqlParameter pIdDepartment = new SqlParameter() { ParameterName = "id_department", SqlValue = idDepartment, SqlDbType = SqlDbType.Int }; SqlParameter pGetPhoto = new SqlParameter() { ParameterName = "get_photo", SqlValue = getPhoto, SqlDbType = SqlDbType.Bit }; SqlParameter pIdCity = new SqlParameter() { ParameterName = "id_city", SqlValue = idCity, SqlDbType = SqlDbType.Int }; SqlParameter pIdManager = new SqlParameter() { ParameterName = "id_manager", SqlValue = idManager, SqlDbType = SqlDbType.Int }; SqlParameter pIdBudget = new SqlParameter() { ParameterName = "id_budget", SqlValue = idBudget, SqlDbType = SqlDbType.Int }; //if (sysNamePositions == null) sysNamePositions = new[] {""}; //SqlParameter pSysNamePositions = new SqlParameter() { ParameterName = "lst_pos_sys_name", SqlValue = String.Join(",", sysNamePositions), SqlDbType = SqlDbType.NVarChar }; var dt = Db.Stuff.ExecuteQueryStoredProcedure("get_employee_list", pIdDepartment, pGetPhoto, pIdCity, pIdManager, pIdBudget); var lst = new List<Employee>(); foreach (DataRow row in dt.Rows) { var emp = new Employee(row); if (emp.IsHidden && (!userCanViewHiddenEmps || !showHidden)) continue; lst.Add(emp); } return lst; }
public static IEnumerable<string> GetHolidayWorkDeliveryRecipientList() { var dt = Db.Stuff.ExecuteQueryStoredProcedure("get_holiday_work_delivery_recipient_list"); List<string> result = new List<string>(); foreach (DataRow row in dt.Rows) { string email = Db.DbHelper.GetValueString(row, "email"); result.Add(email); } return result; }
public static IEnumerable<string> GetFullRecipientList(int? idCity=null) { List<string> result = new List<string>(); foreach (Employee emp in GetList(idCity: idCity)) { if (!String.IsNullOrEmpty(emp.Email)) result.Add(emp.Email); } return result; }
public Tuple<int, List<PersonReportRatingDO>> GetRatings( SqlConnection conn, DateTime? fromDatePeriodFrom = null, DateTime? fromDatePeriodTo = null, DateTime? toDatePeriodFrom = null, DateTime? toDatePeriodTo = null, int? ratingClassId = null, int? authorizationId = null, int? aircraftTypeCategoryId = null, int? lin = null, int? limitationId = null, int? ratingTypeId = null, string sortBy = null, int? showAllPerPersonId = null, int offset = 0, int limit = 10) { string limCode = limitationId.HasValue ? this.nomRepository.GetNomValue(limitationId.Value).Code : null; string ratingTypeCode = ratingTypeId.HasValue ? this.nomRepository.GetNomValue(ratingTypeId.Value).Code : null; bool showAllPerPerson = showAllPerPersonId.HasValue ? this.nomRepository.GetNomValue(showAllPerPersonId.Value).Code == "Y" : false; Dictionary<string, string> sortByToTableColumn = new Dictionary<string, string>() { {"lin", "p.Lin"}, {"fromDate", "re.DocDateValidFrom"}, {"toDate", "re.DocDateValidTo"}, {"firstIssueDate", "re2.DocDateValidFrom"}, {"personRatingLevel", "rl.Code"}, {"ratingTypes", "r.RatingTypes"}, {"locationIndicator", "li.Code"}, {"sector", "r.Sector"}, {"limitations", "re.Limitations"}, {"authorizationCode", "a.Code"}, }; string orderBy = !string.IsNullOrEmpty(sortBy) && sortByToTableColumn.ContainsKey(sortBy) ? sortByToTableColumn[sortBy] : "re.DocDateValidFrom"; string selectQuery = @"SELECT COUNT(*) OVER() as allResultsCount, p.Lin, r.LotId, lastEdition.RatingPartIndex, re2.DocDateValidFrom AS firstIssueDate, re.RatingSubClasses, re.Limitations, re.DocDateValidFrom, re.DocDateValidTo, r.RatingTypes, r.Sector, rc.Code as RatingClass, a.Code as AuthorizationCode, ct.Code as AircraftTypeCategory, atg.Code as AircraftTypeGroup, li.Code as LocationIndicator, rl.Code as RatingLevel, r.PartIndex FROM GvaViewPersonRatings r INNER JOIN GvaViewPersons p ON r.LotId = p.LotId INNER JOIN (select r.LotId, max(re.PartIndex) as edition_part_index, re.RatingPartIndex FROM GvaViewPersonRatings r INNER JOIN GvaViewPersonRatingEditions re on r.LotId = re.LotId and r.PartIndex = re.RatingPartIndex group by re.RatingPartIndex, r.LotId) lastEdition on lastEdition.LotId = r.LotId and lastEdition.RatingPartIndex = r.PartIndex INNER JOIN GvaViewPersonRatingEditions re on lastEdition.LotId = re.LotId and re.PartIndex = lastEdition.edition_part_index INNER JOIN (select r.LotId, min(re.PartIndex) AS edition_part_index, re.RatingPartIndex FROM GvaViewPersonRatings r INNER JOIN GvaViewPersonRatingEditions re on r.LotId = re.LotId and r.PartIndex = re.RatingPartIndex group by re.RatingPartIndex, r.LotId) firstEdition on firstEdition.LotId = lastEdition.LotId and firstEdition.RatingPartIndex = lastEdition.RatingPartIndex INNER JOIN GvaViewPersonRatingEditions re2 on firstEdition.LotId = re2.LotId and re2.PartIndex = firstEdition.edition_part_index LEFT JOIN NomValues rc ON rc.NomValueId = r.RatingClassId LEFT JOIN NomValues a ON a.NomValueId = r.AuthorizationId LEFT JOIN NomValues ct ON ct.NomValueId = r.AircraftTypeCategoryId LEFT JOIN NomValues atg ON atg.NomValueId = r.AircraftTypeGroupId LEFT JOIN NomValues li ON li.NomValueId = r.LocationIndicatorId LEFT JOIN NomValues rl ON rl.NomValueId = r.RatingLevelId "; List<DbClause> clauses = new List<DbClause>() { new DbClause("and re.DocDateValidFrom >= {0}", fromDatePeriodFrom), new DbClause("and re.DocDateValidFrom <= {0}", fromDatePeriodTo), new DbClause("and re.DocDateValidTo >= {0}", toDatePeriodFrom), new DbClause("and re.DocDateValidTo <= {0}", toDatePeriodTo), new DbClause("and p.Lin = {0}", lin), new DbClause("and r.RatingClassId = {0}", ratingClassId), new DbClause("and r.AuthorizationId = {0}", authorizationId), new DbClause("and r.AircraftTypeGroupId = {0}", aircraftTypeCategoryId), new DbClause("and (re.Limitations like {0} + '$$%' or re.Limitations like '%$$' + {0} or re.Limitations like '%$$' + {0} + '$$%' or re.Limitations like {0})", limCode), new DbClause("and (r.RatingTypes like {0} + ', %' or r.RatingTypes like '%, ' + {0} or r.RatingTypes like '%, ' + {0} + ',%' or r.RatingTypes like {0})", ratingTypeCode) }; DbCommand command = null; if (showAllPerPerson) { var filteredResults = conn.CreateStoreCommand(selectQuery + @"WHERE 1=1 {0} {1} {2} {3} {4} {5} {6} {7} {8} {9}", clauses.ToArray()) .Materialize(r => new { LotId = r.Field<int>("lotId"), }) .Select(l => l.LotId) .ToArray(); command = conn.CreateStoreCommand(selectQuery + @"WHERE 1=1 {0} ORDER BY " + orderBy + @" DESC OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY", new DbClause("and r.LotId in " + string.Format("({0})", string.Join(",", filteredResults))), new DbClause("{0}", offset), new DbClause("{0}", limit)); } else { clauses = clauses .Union(new List<DbClause> { new DbClause("{0}", offset), new DbClause("{0}", limit) }) .ToList(); command = conn.CreateStoreCommand(selectQuery + @"WHERE 1=1 {0} {1} {2} {3} {4} {5} {6} {7} {8} {9} ORDER BY " + orderBy + @" DESC OFFSET {10} ROWS FETCH NEXT {11} ROWS ONLY", clauses.ToArray()); } var queryResult = command .Materialize(r => new Tuple<int, PersonReportRatingDO> ( r.Field<int>("allResultsCount"), new PersonReportRatingDO() { Lin = r.Field<int?>("lin"), LotId = r.Field<int>("lotId"), PartIndex = r.Field<int>("partIndex"), RatingSubClasses = r.Field<string>("RatingSubClasses"), Limitations = !string.IsNullOrEmpty(r.Field<string>("Limitations")) ? r.Field<string>("Limitations").Replace(GvaConstants.ConcatenatingExp, ", ") : null, FirstIssueDate = r.Field<DateTime?>("FirstIssueDate"), DateValidFrom = r.Field<DateTime?>("DocDateValidFrom"), DateValidTo = r.Field<DateTime?>("DocDateValidTo"), RatingTypes = r.Field<string>("RatingTypes"), Sector = r.Field<string>("Sector"), RatingClass = r.Field<string>("RatingClass"), AuthorizationCode = r.Field<string>("AuthorizationCode"), AircraftTypeCategory = r.Field<string>("AircraftTypeCategory"), AircraftTypeGroup = r.Field<string>("AircraftTypeGroup"), LocationIndicator = r.Field<string>("LocationIndicator"), RatingLevel = r.Field<string>("RatingLevel") })) .ToList(); var ratings = queryResult.Select(q => q.Item2).ToList(); int count = queryResult.Select(q => q.Item1).FirstOrDefault(); return new Tuple<int, List<PersonReportRatingDO>>(count, ratings); }
public List<PersonReportPaperDO> GetPapers( SqlConnection conn, int? paperId) { List<PersonReportPaperDO> paperResults = new List<PersonReportPaperDO>(); var issuedPaperData = conn.CreateStoreCommand(@"SELECT p.GvaPaperId, p.Name, p.FirstNumber, p.FromDate, p.ToDate, ple.StampNumber FROM GvaViewPersonLicenceEditions ple RIGHT JOIN GvaPapers p ON ple.PaperId = p.GvaPaperId WHERE 1=1 {0}", new DbClause("and p.GvaPaperId = {0}", paperId) ) .Materialize(r => new { PaperId = r.Field<int>("GvaPaperId"), PaperName = r.Field<string>("Name"), FirstNumber = r.Field<int>("FirstNumber"), FromDate = r.Field<DateTime>("FromDate"), ToDate = r.Field<DateTime>("ToDate"), StampNumber = r.Field<string>("StampNumber") }).ToList(); foreach (var papers in issuedPaperData.GroupBy(p => p.PaperId)) { var allStampNumbersForSplit = papers.Where(p => p.StampNumber != null && p.StampNumber.Contains(GvaConstants.ConcatenatingExp)) .SelectMany(p => p.StampNumber.Split(GvaConstants.ConcatenatingExp.ToArray())) .Where(sn => !string.IsNullOrEmpty(sn)) .ToList(); var allOtherStampNumbers = papers.Where(p => p.StampNumber != null && !p.StampNumber.Contains(GvaConstants.ConcatenatingExp)) .Select(p => p.StampNumber) .ToList(); var allNumbers = allStampNumbersForSplit.Union(allOtherStampNumbers); var paperInfo = papers.First(); int? maxNumber = allNumbers.Count() > 0 ? allNumbers.Max(sn => int.Parse(sn.Trim())) : (int?)null; int issuedCount = allNumbers.Count(); int skippedCount = maxNumber.HasValue ? maxNumber.Value - (paperInfo.FirstNumber + issuedCount) + 1 : 0; paperResults.Add(new PersonReportPaperDO() { PaperId = paperInfo.PaperId, PaperName = paperInfo.PaperName, FirstNumber = paperInfo.FirstNumber, IssuedCount = maxNumber != null ? issuedCount : 0, SkippedCount = skippedCount, LastIssuedNumber = maxNumber, FromDate = paperInfo.FromDate, ToDate = paperInfo.ToDate }); } return paperResults; }