public DateRange(int days)
{
days--;
End = DateTime.Today;
Start = End.AddDays(-days);
}
public Reservation Map()
{
var reservation = new Reservation();
reservation.Start = Start;
reservation.End = End.AddDays(WeeksCount * 7);
reservation.Subject = Subject;
reservation.ReservationDays = WeekDays.GetDays();
reservation.Room = new Room();
reservation.Room.Number = RoomNumber?.Split('/').Last().Trim();
if (RoomNumber.Contains('/'))
{
var floorStr = RoomNumber.Split('/').First().Trim();
int.TryParse(floorStr, out var floor);
reservation.Room.Floor = floor;
}
int.TryParse(BuildingNumber, out var bn);
reservation.Room.Building = new Building()
{
Number = bn
};
return(reservation);
}
private void cbxAddMonth_SelectedIndexChanged(object sender, EventArgs e)
{
Int32 Year = Convert.ToInt32(cbxAddYear.SelectedValue);
Int32 Month = Convert.ToInt32(cbxAddMonth.SelectedValue);
DateTime Start;
DateTime End;
if (Year == -1)
{
Start = new DateTime(1988, 1, 1);
End = Start.AddYears(100);
End = End.AddMonths(12);
End = End.AddDays(-1);
}
else
{
if (Month == -1)
{
Start = new DateTime(Year, 1, 1);
End = Start.AddMonths(12);
End = End.AddDays(-1);
}
else
{
Start = new DateTime(Year, Month, 1);
End = Start.AddMonths(1);
End = End.AddDays(-1);
}
}
this.stockAddTableAdapter.FillByDate(this.farmManageDataSet.StockAdd, Start, End);
}
private void FillRemove()
{
Int32 Year = Convert.ToInt32(cbxRemoveYear.SelectedValue);
Int32 Month = Convert.ToInt32(cbxRemoveMonth.SelectedValue);
DateTime Start;
DateTime End;
if (Year == -1)
{
Start = new DateTime(1990, 1, 1);
End = Start.AddYears(100);
End = End.AddMonths(12);
End = End.AddDays(-1);
}
else
{
if (Month == -1)
{
Start = new DateTime(Year, 1, 1);
End = Start.AddMonths(12);
End = End.AddDays(-1);
}
else
{
Start = new DateTime(Year, Month, 1);
End = Start.AddMonths(1);
End = End.AddDays(-1);
}
}
this.stockRemoveListViewTableAdapter.FillByStockID(this.farmManageDataSet.StockRemoveListView, StockID, Start, End);
}
public void MoveToWeek(DateTime weekStart)
{
int weeks = (int)Math.Ceiling((weekStart - Start).TotalDays / 7);
Start = Start.AddDays(7 * weeks);
End = End.AddDays(7 * weeks);
}
public override string ToString()
{
if (1 < End.Subtract(Start).Days)
{
return(string.Format("{0:MM/dd}-{1:MM/dd}", Start, End.AddDays(-1)));
}
return(Start.ToString("MM/dd"));
}
public Request(DateTime start)
{
while (!IsWorkingDay(start))
{
start = start.AddDays(1);
}
Start = start;
End = start.AddDays(1);
while (!IsWorkingDay(End))
{
End = End.AddDays(1);
}
}
public IndexRescueRoomDay(DateTime time)
{
var db = new EiSDbContext();
this.End = time.Date.AddHours(8);
this.Start = End.AddDays(-1);
var queryIn = db.RescueRoomInfos.Where(c => this.Start <= c.InDepartmentTime && c.InDepartmentTime < this.End);
var queryStay = db.RescueRoomInfos.Where(c => c.InDepartmentTime < this.Start && (this.Start < c.OutDepartmentTime || c.OutDepartmentTime == null));
var query = queryIn.Union(queryStay);
var queryOrdered = query.OrderBy(c => c.InDepartmentTime).ThenBy(c => c.RescueRoomInfoId);
var list = queryOrdered.ToList();
this.List = list.Select(c => new Item(c)).ToList();
}
protected override void OnInitialized()
{
SelectedInterval = IntervalFilter.GetIntervalFilters()[3];
SelectedFilterInterval = IntervalFilter.GetIntervalFilters()[3].Duration;
ActiveTimeFilterDuration = TimeFilter.GetFilters()[4].Duration;
Start = End.AddDays(-4);
FilterIntervals(ActiveTimeFilterDuration.Value);
TimeFilters.Add(new TimeFilter()
{
Name = "MAX", Duration = (long)(MaxDate - MinDate).TotalMilliseconds
});
base.OnInitialized();
}
public DateTime GetNext(DateTime dt)
{
var nextTime = dt.AddMinutes(1);
if (transactionDates.Contains(nextTime.Date))
{
var time = new TimeSpan(nextTime.Hour, nextTime.Minute, 0);
if (time <= new TimeSpan(9, 25, 0))
{
return(new DateTime(nextTime.Year, nextTime.Month, nextTime.Day, 9, 25, 0));
}
if (time > new TimeSpan(9, 25, 0) && time <= new TimeSpan(9, 30, 0))
{
return(new DateTime(nextTime.Year, nextTime.Month, nextTime.Day, 9, 30, 0));
}
if (time > new TimeSpan(9, 30, 0) && time <= new TimeSpan(11, 29, 0))
{
return(new DateTime(nextTime.Year, nextTime.Month, nextTime.Day, nextTime.Hour, nextTime.Minute, 0));
}
if (time > new TimeSpan(11, 29, 0) && time <= new TimeSpan(13, 0, 0))
{
return(new DateTime(nextTime.Year, nextTime.Month, nextTime.Day, 13, 0, 0));
}
if (time > new TimeSpan(13, 0, 0) && time <= new TimeSpan(15, 0, 0))
{
return(new DateTime(nextTime.Year, nextTime.Month, nextTime.Day, nextTime.Hour, nextTime.Minute, 0));
}
if (time > new TimeSpan(15, 0, 0) && nextTime.Date < transactionDates.Last())
{
var nextDate = transactionDates[transactionDates.IndexOf(nextTime.Date) + 1];
return(new DateTime(nextDate.Year, nextDate.Month, nextDate.Day, 9, 25, 0));
}
}
else
{
var res = transactionDates.FirstOrDefault(c => c > nextTime.Date);
if (res != default(DateTime))
{
return(new DateTime(res.Year, res.Month, res.Day, 9, 25, 0));
}
}
return(End.AddDays(1));
}
protected override void GenerateReport()
{
var connection = DataAdapter.SelectCommand.Connection;
var supplierDeliveryIdSql = @"DROP TEMPORARY TABLE IF EXISTS reports.TempIntersection;
CREATE TEMPORARY TABLE reports.TempIntersection (
AddressId INT unsigned,
SupplierDeliveryId varchar(255),
index(AddressId)
) engine=MEMORY;
insert into reports.TempIntersection(SupplierDeliveryId, AddressId)
SELECT ai.SupplierDeliveryId, ai.AddressId
FROM Customers.Intersection i
JOIN Customers.AddressIntersection ai on ai.IntersectionId = i.Id
JOIN Customers.Clients c ON c.Id = i.ClientId
JOIN usersettings.RetClientsSet r ON r.clientcode = c.Id
JOIN usersettings.PricesData pd ON pd.pricecode = i.PriceId
JOIN Customers.Suppliers supplier ON supplier.Id = pd.firmcode
JOIN usersettings.PricesRegionalData prd ON prd.regioncode = i.RegionId AND prd.pricecode = pd.pricecode
JOIN usersettings.RegionalData rd ON rd.RegionCode = i.RegionId AND rd.FirmCode = pd.firmcode
WHERE c.Status = 1
and (supplier.RegionMask & i.RegionId) > 0
and (c.maskregion & i.RegionId) > 0
and (r.WorkRegionMask & i.RegionId) > 0
and pd.agencyenabled = 1
and pd.enabled = 1
and prd.enabled = 1
and i.AvailableForClient = 1
and i.AgencyEnabled = 1
and supplier.Id = ?supplierId
group by ai.AddressId, ai.SupplierDeliveryId";
var supplierClientIdSql = @"DROP TEMPORARY TABLE IF EXISTS reports.TempIntersection;
CREATE TEMPORARY TABLE reports.TempIntersection (
SupplierClientId varchar(255),
LegalEntityId INT unsigned,
index(LegalEntityId)
) engine=MEMORY;
insert into reports.TempIntersection(SupplierClientId, LegalEntityId)
SELECT i.SupplierClientId, i.LegalEntityId
FROM Customers.Intersection i
JOIN Customers.Clients c ON c.Id = i.ClientId
JOIN usersettings.RetClientsSet r ON r.clientcode = c.Id
JOIN usersettings.PricesData pd ON pd.pricecode = i.PriceId
JOIN Customers.Suppliers supplier ON supplier.Id = pd.firmcode
JOIN usersettings.PricesRegionalData prd ON prd.regioncode = i.RegionId AND prd.pricecode = pd.pricecode
JOIN usersettings.RegionalData rd ON rd.RegionCode = i.RegionId AND rd.FirmCode = pd.firmcode
WHERE c.Status = 1
and (supplier.RegionMask & i.RegionId) > 0
and (c.maskregion & i.RegionId) > 0
and (r.WorkRegionMask & i.RegionId) > 0
and pd.agencyenabled = 1
and pd.enabled = 1
and prd.enabled = 1
and i.AvailableForClient = 1
and i.AgencyEnabled = 1
and supplier.Id = ?supplierId
group by i.LegalEntityId, i.SupplierClientId";
if (_grouping.Group.Match("a.LegalEntityId"))
{
connection.Execute(supplierClientIdSql, new { supplierId = _supplierId });
}
else if (_grouping.Group.Match("oh.AddressId"))
{
connection.Execute(supplierDeliveryIdSql, new { supplierId = _supplierId });
}
var userIds = connection.Read <uint>(String.Format(@"
select oh.UserId
from Orders.OrdersHead oh
where oh.WriteTime > ?begin
and oh.WriteTime < ?end
and oh.Deleted = 0
and oh.Submited = 1
and oh.RegionCode in ({0})
group by oh.UserId", _regions.Implode()), new { begin = Begin, end = End })
.ToArray();
connection.Execute(@"
drop temporary table if exists Reports.UserStat;
create temporary table Reports.UserStat(
UserId int unsigned not null,
RequestCount int unsigned not null,
primary key (UserId)
) engine=memory;
drop temporary table if exists Reports.UserAllStat;
create temporary table Reports.UserAllStat(
Id int auto_increment primary key,
UserId int unsigned not null,
RequestCount int unsigned not null
) engine=memory;");
if (userIds.Length > 0)
{
connection.Execute(String.Format(@"
insert into Reports.UserStat(UserId, RequestCount)
select l.UserId, count(*)
from Logs.AnalitfUpdates l
where l.UserId in ({0})
and l.UpdateType in (4, 11)
and l.RequestTime > ?begin
and l.RequestTime < ?end
group by l.UserId;
insert into Reports.UserAllStat(UserId, RequestCount)
select r.UserId, count(*)
from Logs.RequestLogs r
where r.Userid in ({0})
and r.CreatedOn > ?begin
and r.CreatedOn < ?end
and r.IsCompleted = 1
and r.IsFaulted = 0
and r.UpdateType = 'OrdersController'
group by r.UserId
union all
select *
from Reports.UserStat s
group by s.UserId" , userIds.Implode()), new { begin = Begin, end = End });
}
DataAdapter.SelectCommand.CommandText = String.Format(@"
drop temporary table if exists Reports.KeyToUser;
create temporary table Reports.KeyToUser(
GroupKey int unsigned not null,
UserId int unsigned not null,
primary key(GroupKey, UserId)
) engine=memory;
insert into Reports.KeyToUser(GroupKey, UserId)
select {1}, UserId
from Orders.OrdersHead oh
join Usersettings.PricesData pd on pd.PriceCode = oh.PriceCode
join Customers.Clients c on c.Id = oh.ClientCode
join Customers.Users u on u.ClientId = c.Id and oh.UserId = u.Id
join Customers.Addresses a on a.Id = oh.AddressId
join Billing.LegalEntities le on le.Id = a.LegalEntityId
where oh.WriteTime > ?begin
and oh.WriteTime < ?end
and oh.Deleted = 0
and oh.Submited = 1
and oh.RegionCode in ({0})
and pd.IsLocal = 0
group by {1}, UserId;
drop temporary table if exists Reports.KeyToCount;
create temporary table Reports.KeyToCount(
GroupKey int unsigned not null,
RequestCount int unsigned not null,
primary key(GroupKey)
) engine=memory;
insert into Reports.KeyToCount(GroupKey, RequestCount)
select k.GroupKey, sum(s.RequestCount)
from Reports.KeyToUser k
join Reports.UserAllStat s on s.UserId = k.UserId
group by k.GroupKey;
drop temporary table if exists Reports.PreResult;
create temporary table Reports.PreResult(
GroupKey int unsigned not null,
{6},
TotalSum decimal(12, 2),
SupplierSum decimal(12, 2),
SuppliersCount int unsigned,
LastOrder time,
primary key(GroupKey)
) engine=memory;
insert into Reports.PreResult(GroupKey, {5}, TotalSum, SupplierSum, SuppliersCount, LastOrder)
select {1} as GroupKey,
{2},
sum(ol.Cost * ol.Quantity) as TotalSum,
sum(if(pd.FirmCode = ?SupplierId, ol.Cost * ol.Quantity, 0)) as SupplierSum,
count(distinct pd.FirmCode) as SuppliersCount,
time(min(oh.WriteTime)) as LastOrder
from Orders.OrdersHead oh
join Orders.OrdersList ol on ol.OrderId = oh.RowId
join Customers.Clients c on c.Id = oh.ClientCode
join Customers.Users u on u.ClientId = c.Id and oh.UserId = u.Id
join Customers.Addresses a on a.Id = oh.AddressId
join Billing.LegalEntities le on le.Id = a.LegalEntityId
join Usersettings.PricesData pd on pd.PriceCode = oh.PriceCode
{4}
where oh.WriteTime > ?begin
and oh.WriteTime < ?end
and oh.Deleted = 0
and oh.Submited = 1
and oh.RegionCode in ({0})
and pd.IsLocal = 0
group by {1}
order by {3};
drop temporary table if exists Reports.PrevResult;
create temporary table Reports.PrevResult(
GroupKey int unsigned not null,
TotalSum decimal(12, 2),
SupplierSum decimal(12, 2),
primary key(GroupKey)
) engine=memory;
insert into Reports.PrevResult(GroupKey, TotalSum, SupplierSum)
select {1} as GroupKey,
sum(ol.Cost * ol.Quantity) as TotalSum,
sum(if(pd.FirmCode = ?SupplierId, ol.Cost * ol.Quantity, 0)) as SupplierSum
from Orders.OrdersHead oh
join Orders.OrdersList ol on ol.OrderId = oh.RowId
join Customers.Clients c on c.Id = oh.ClientCode
join Customers.Users u on u.ClientId = c.Id and oh.UserId = u.Id
join Customers.Addresses a on a.Id = oh.AddressId
join Billing.LegalEntities le on le.Id = a.LegalEntityId
join Usersettings.PricesData pd on pd.PriceCode = oh.PriceCode
{4}
where oh.WriteTime > ?prevBegin
and oh.WriteTime < ?prevEnd
and oh.Deleted = 0
and oh.Submited = 1
and oh.RegionCode in ({0})
and pd.IsLocal = 0
group by {1};
select r.*, k.RequestCount as OrderSendRequestCount, pr.SupplierSum as PrevSupplierSum, pr.TotalSum as PrevTotalSum
from Reports.PreResult r
left join Reports.KeyToCount k on k.GroupKey = r.GroupKey
left join Reports.PrevResult pr on pr.GroupKey = r.GroupKey;",
_regions.Implode(),
_grouping.Group,
_grouping.Columns.Implode(c => $"{c.Sql} as {c.Name}"),
_grouping.Columns.Where(c => c.Order).Implode(c => c.Name),
_grouping.Join,
_grouping.Columns.Implode(c => c.Name),
_grouping.Columns.Implode(c => $"{c.Name} varchar(255)"));
var prevBegin = Begin.AddDays(-(int)(End - Begin).TotalDays);
var prevEnd = Begin;
if (ReportPeriod == ReportPeriod.ByToday)
{
prevBegin = Begin.AddDays(-1);
prevEnd = End.AddDays(-1);
}
else if (ReportPeriod == ReportPeriod.ByPrevMonth)
{
prevBegin = Begin.AddMonths(-1);
prevEnd = End.AddMonths(-1);
}
DataAdapter.SelectCommand.Parameters.AddWithValue("?SupplierId", _supplierId);
DataAdapter.SelectCommand.Parameters.AddWithValue("?begin", Begin);
DataAdapter.SelectCommand.Parameters.AddWithValue("?end", End);
DataAdapter.SelectCommand.Parameters.AddWithValue("?prevBegin", prevBegin);
DataAdapter.SelectCommand.Parameters.AddWithValue("?prevEnd", prevEnd);
#if DEBUG
ProfileHelper.WriteLine(DataAdapter.SelectCommand);
#endif
DataAdapter.Fill(_dsReport, "data");
connection.Execute(@"
drop temporary table if exists reports.TempIntersection;
drop temporary table if exists reports.UserStat;
drop temporary table if exists reports.UserAllStat;
drop temporary table if exists Reports.KeyToCount;
drop temporary table if exists Reports.KeyToUser;
drop temporary table if exists Reports.PreResult;
");
var data = _dsReport.Tables["data"];
var result = _dsReport.Tables.Add("Results");
foreach (var column in _grouping.Columns)
{
var dataColumn = result.Columns.Add(column.Name);
dataColumn.Caption = column.Caption;
}
result.Columns.Add("Share", typeof(string));
result.Columns.Add("ShareDiff", typeof(string));
result.Columns.Add("SupplierSum", typeof(string));
if (ShowAllSum)
{
result.Columns.Add("TotalSum", typeof(string));
}
result.Columns.Add("SuppliersCount", typeof(string));
result.Columns.Add("OrderSendRequestCount", typeof(string));
result.Columns.Add("LastOrder", typeof(string));
var supplier = Session.Get <Supplier>(_supplierId);
var regions = _regions
.Select(id => Session.Load <global::Common.Models.Region>(Convert.ToUInt64(id)));
Header.Add("Поставщик: " + supplier.Name);
Header.Add("Регионы: " + regions.Implode(r => r.Name));
if (_shareMoreThan.HasValue)
{
Header.Add($"Из отчета ИСКЛЮЧЕНЫ юр. лица, клиенты, адреса, по которым доля НЕ превышает {_shareMoreThan.Value}%");
}
else
{
Header.Add("Из отчета ИСКЛЮЧЕНЫ юр. лица, клиенты, адреса," +
" по которым отсутствуют заказы на любых поставщиков за период формирования отчета");
}
Header.Add("");
result.Columns["SupplierSum"].Caption = $"Сумма по '{supplier.Name}'";
if (ShowAllSum)
{
result.Columns["TotalSum"].Caption = "Сумма по всем поставщикам";
}
result.Columns["Share"].Caption = $"Доля '{supplier.Name}', %";
result.Columns["ShareDiff"].Caption = "Изменение доли";
result.Columns["SuppliersCount"].Caption = "Кол-во поставщиков";
result.Columns["OrderSendRequestCount"].Caption = "Кол-во сессий отправки заказов";
result.Columns["LastOrder"].Caption = "Самая поздняя заявка";
foreach (var row in data.Rows.Cast <DataRow>())
{
var resultRow = result.NewRow();
var share = SetTotalSum(row, resultRow);
resultRow["SuppliersCount"] = row["SuppliersCount"];
resultRow["LastOrder"] = row["LastOrder"];
resultRow["OrderSendRequestCount"] = row["OrderSendRequestCount"];
if (ShowAllSum)
{
resultRow["TotalSum"] = row["TotalSum"];
}
foreach (var column in _grouping.Columns)
{
resultRow[column.Name] = row[column.Name];
resultRow[column.Name] = row[column.Name];
}
if (!_shareMoreThan.HasValue || share > _shareMoreThan.Value)
{
result.Rows.Add(resultRow);
}
}
var emptyRowCount = EmptyRowCount;
for (var i = 0; i < emptyRowCount; i++)
{
result.Rows.InsertAt(result.NewRow(), 0);
}
}
protected virtual void Create(Dto src, string user, IDbSession session)
{
Start = src.Start ?? MinimumDate;
End = src.End ?? MaximumDate;
var set = session.DbContext.Set <T>();
var items = set.Where(SeriesExpression).ToArray();
var replacing = items.Where(args => Start <= args.Start && args.End <= End);
set.RemoveRange(replacing);
var front = items.Where(args => args.Start < Start && args.End >= Start && args.End <= End);
foreach (var item in front)
{
if (CanMerge(item))
{
set.Remove(item);
if (item.Start < Start)
{
Start = item.Start;
}
}
else
{
item.End = Start.AddDays(-1);
}
}
var back = items.Where(args => Start <= args.Start && args.Start <= End && End < args.End);
foreach (var item in back)
{
if (CanMerge(item))
{
set.Remove(item);
if (item.End > End)
{
End = item.End;
}
}
else
{
item.Start = End.AddDays(1);
}
}
var overlap = items.Where(args => args.Start <Start && args.End> End);
foreach (var item in overlap)
{
if (CanMerge(item))
{
set.Remove(item);
if (item.Start < Start)
{
Start = item.Start;
}
if (item.End > End)
{
End = item.End;
}
}
else
{
var clone = item.Clone(user);
item.End = Start.AddDays(-1);
clone.Start = End.AddDays(1);
set.Add(clone);
}
}
base.Create(user);
}
public override string ToString()
{
return(string.Format("{0:MM/dd} - {1:MM/dd}", Start, End.AddDays(-1)));
}
/// <summary>
/// Se esse torneio deve ou não aparecer no menu, com relação a data em que está sendo listado
/// </summary>
public bool ShouldAppear(DateTime date)
{
return((Start.AddDays(-14) <= date) && (date <= End.AddDays(14)));
}
public DateTimeRange SpreadByDays(int daysStart, int daysEnd)
{
return(new DateTimeRange(
start: Start.AddDays(-daysStart),
end: End.AddDays(daysEnd)));
}
/// <summary>
/// 前后扩展N天
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
public DateRange ExtendDay(double value)
{
return(new DateRange(Start.AddDays(-value), End.AddDays(value)));
}
public DateRange ExtendEndDay(double value)
{
return(new DateRange(Start, End.AddDays(value).Subtract(Start)));
}
public DateRangePickerVM(MainWindowVM mainWindowVM)
{
End = DateTime.Now;
Start = End.AddDays(-100);
Main = mainWindowVM;
}
