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