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