租用有库存产品的日期不可用 [英] Getting Unavailable dates for renting a product that has stocks
问题描述
数据库查询通常很简单，但有时却很困难. (大脑教练)
Database queries, normally so simple, yet sometimes so difficult. (brain trainer)
所以我有产品，股票和rentStockOrders.这些产品可以租用几天.股票还有可用的日期.是否可以租用新产品(库存)取决于该产品已租用的库存.
So I have products, stocks and rentStockOrders. These products can be rented for a set of days. The stocks also have a date when they are available. If a new product (stock) can be rented depends on the already rented stocks of that product.
- 无法在可用日期之前租用库存物品.
- 一个rentStockOrder(在订单和库存之间链接)包含预订，因此为
rentStartDate和rentEndDate. - 产品可以租用几天，而没有给出开始日期.选择产品后，使用日期时间选择器选择开始租赁的日期.
- 应用了整个最小日期和最大日期(大约提前一年).
- A stock item cannot be rented before it's available date.
- A rentStockOrder (linked between order and stocks) contains the bookings, thus
rentStartDateandrentEndDate. - A product can be rented for a set of days, where the start date is not given. The product is selected, and after that a date time picker is used to select a starting day for renting.
- An overall minimum and maximum date is applied (about a year ahead).
这个想法是，用户尚未选择开始日期，因此在用户能够执行此操作之前，我想在datetimepicker中禁用某些日期，这些日期不能用作开始日期，因为没有可用的库存.产品的租用期限.
The idea is that the user didn't select the start date yet, before the user is able to do that I want to disable certain dates in a datetimepicker that cannot be used as starting date because there are no stocks available for the product's renting period.
将其放在上下文中:选择了一种产品，用户可以选择指定要租用该产品的天数(1周，2周或3周).用户选择了该日期后，他们必须选择一个开始日期.与其每次都显示不可用该日期的错误，不如说是先禁用开始日期.
To put it in a context: One product is selected, the user is given the option to specify a length in days he wants to rent this product (1 week, 2 weeks or 3 weeks). When the user has selected that, they must select a start date. Instead of every time showing an error that this date is not available, I rather disable start dates before hand.
由于通常有一种产品可供出租而不是可供出租，所以我认为最好将不可用的选择日期列表发送到我的网站，而不是将可用日期的整个列表发送给我.因此，无法在日期时间选择器中单击不可用的日期.
Since it is more often that a product is available for renting then not, I think it is better to send a list of unavailable select dates to my website instead of a whole list of available dates. So the days that are not available cannot be clicked in a date time picker.
到目前为止，我发现的大多数示例都包含一个我没有的开始和结束日期输入参数，我所有想要租用的产品的天数长度以及在特定时间范围内已经租用了多少库存的输入参数
Most examples I have found so far include a input parameter for start and end date which I don't have, all I have a length in days a product wants to be rented and how many stocks are already rented for certain time frames.
根据要求，测试数据和表格:
As requested, test data and tables:
股票
+---------+-----------+-------------------+
| stockId | productId | availableFromDate |
+---------+-----------+-------------------+
| 1 | 1 | 1-01-2016 |
| 2 | 1 | 1-01-2016 |
+---------+-----------+-------------------+
RentStockOrders
+------------------+---------+----------------+----------------+
| rentStockOrderId | stockId | beginRentDate | endRentDate |
+------------------+---------+----------------+----------------+
| 1 | 1 | 15-1-2016 | 14-2-2016 |
| 2 | 2 | 30-1-2016 | 20-2-2016 |
| 3 | 2 | 26-2-2016 | 7-3-2016 |
| 4 | 1 | 29-2-2016 | 14-3-2016 |
+------------------+---------+----------------+----------------+
基于这些记录，我想生成一个不可用日期的列表. 为简单起见，我省略了一些列
Based on these records, I want to generate a list of unavailable dates. I've left out some columns for simplicity
输入的是一天和一个productId. 因此，如果我输入天: 14和 productId: 1，我将得到以下一些预期结果:
Input is a day and a productId. So if I would input for days: 14 and for productId: 1 I would have some of the following expected results:
- 2016年1月25日(库存编号1已被预订，库存2即将预订，不可能14天.
- 2016年1月30日(均已预订)
- 2016年2月13日(库存1还没有回来)
- 2016年2月17日(已预订库存2，库存1将在13天内租用，不足14天).
- ..还有很多已经租用了这两种股票的地方.
例如，我不会期望的是15年2月15日，因为库存1将在接下来的14天内可用.
What I would NOT expect is for example 15-02-2016, because Stock 1 would be available for the next 14 days.
如果遇到困难，那么获取可用日期可能会更简单，我将在代码中进行切换.在此示例中，从数据库中提取的数据较少，但是实际上一种产品大约有250项，因此获得不可用的日期也许更好.
If it is to difficult, then perhaps getting the available dates is simpler and I will switch this around in code. In this example it would be less data to pull from the database, but in reality there are about 250 items of one product so getting the unavailable dates perhaps better.
我已尝试此答案以获取可用日期，但尚未成功，没有错误，仅返回了否数据.
I've tried this answer to get the available dates, with no success yet, no errors, just returns no data.
declare @startDate datetime, @endDate datetime, @days int
select @startDate = '2016/01/01', @endDate='2016/03/31', @days=2
select stockId, min(endRentDate)
from
(
select stockId ,endRentDate,
(select top 1 endRentDate
from RentStockOrders sInner
where sInner.endRentDate > sOuter.beginRentDate
and sInner.stockId = sOuter.stockId
and sInner.endRentDate between @startDate and @endDate
order by sInner.endRentDate) as nextAvailableDate
from RentStockOrders sOuter
where sOuter.beginRentDate between @startDate and @endDate
) sub
group by stockId, nextAvailableDate
having dateDiff(d, min(endRentDate), isNull(nextAvailableDate,dateAdd(d,1,@endDate))) >= @days
推荐答案
如果您想尽可能地仅使用SQL，那么这个问题就很难解决.我已经使用Oracle PL/SQL和SQL做出了一个非常优化/高效的解决方案.您可以轻松地将其转换为TSQL. 该函数返回日期的集合/集合. 我还制作了另一个版本，该版本返回带有空格分隔日期值的一个大字符串，例如"01-01-2016 02-01-2016 03-01-2016"等. 您还可以制作一个版本，该版本返回所有单独的日期作为日期句柄字符串，例如"01-01-2016/10-01-2016 15-01-2016/25-01-2016"，然后可以轻松地将其发送至并进行解析-在您的应用程序中.
This question is pretty difficult if you want to just use SQL if not impossible. I have made an pretty optimized/performant solution using Oracle PL/SQL and SQL. You can easily translate it to TSQL. The function returns a set/collection of dates. I also made another version which returns one big string with space seperated date values like "01-01-2016 02-01-2016 03-01-2016" etc... You could also make a version which return all seperate dates as date periodstrings for example "01-01-2016/10-01-2016 15-01-2016/25-01-2016" which you can then easily send to- and parse- in your application.
set serveroutput on;
drop table Product cascade constraints;
drop table Stocks cascade constraints;
drop table RentStockOrders cascade constraints;
create table Product (
productId number primary key,
description varchar2(255)
);
create table Stocks (
stockId number primary key,
productId number references Product(productId),
availableFromDate date
);
create table RentStockOrders (
rentStockOrderId number primary key,
stockId number references Stocks(stockId),
beginRentDate date,
endRentDate date
);
insert into Product values (1,'product 1');
insert into Product values (2,'product 2');
insert into Stocks values (1,1,to_date('01-01-2016','dd-mm-yyyy'));
insert into Stocks values (2,1,to_date('01-01-2016','dd-mm-yyyy'));
insert into Stocks values (3,2,to_date('01-01-2016','dd-mm-yyyy'));
insert into Stocks values (4,2,to_date('01-01-2016','dd-mm-yyyy'));
insert into RentStockOrders values (1,1,to_date('15-01-2016','dd-mm-yyyy'),to_date('14-02-2016','dd-mm-yyyy'));
insert into RentStockOrders values (2,2,to_date('30-01-2016','dd-mm-yyyy'),to_date('20-02-2016','dd-mm-yyyy'));
insert into RentStockOrders values (3,2,to_date('26-02-2016','dd-mm-yyyy'),to_date('07-03-2016','dd-mm-yyyy'));
insert into RentStockOrders values (4,1,to_date('29-02-2016','dd-mm-yyyy'),to_date('14-03-2016','dd-mm-yyyy'));
--insert into RentStockOrders values (5,3,to_date('15-01-2016','dd-mm-yyyy'),to_date('14-02-2016','dd-mm-yyyy'));
insert into RentStockOrders values (6,4,to_date('20-01-2016','dd-mm-yyyy'),to_date('25-01-2016','dd-mm-yyyy'));
--insert into RentStockOrders values (7,4,to_date('01-01-2016','dd-mm-yyyy'),to_date('01-04-2016','dd-mm-yyyy'));
insert into RentStockOrders values (8,3,to_date('17-01-2016','dd-mm-yyyy'),to_date('25-01-2016','dd-mm-yyyy'));
--stocks with productId X which are rented for coming year from date Y with rentPeriode Z
select *
from RentStockOrders rso, Stocks s
where rso.stockId=s.stockId
and s.productId=1
and rso.beginRentDate>=to_date('01-01-2016','dd-mm-yyyy')-14
and rso.endRentDate<=to_date('01-01-2016','dd-mm-yyyy')+365
order by beginRentDate;
create or replace package my_globals
as
--type has to be globally declared to be used as a return type
type t_dates is table of date INDEX BY pls_integer;
cursor c_searchRentData(p_productid number, p_beginDate date, p_endDate date, p_rentPeriod pls_integer) is
select beginRentDate,endRentDate
from RentStockOrders rso, Stocks s
where rso.stockId=s.stockId
and s.productId=p_productid
and rso.beginRentDate>=p_beginDate-p_rentPeriod
and rso.endRentDate<=p_endDate
order by beginRentDate;
end;
/
--helper function tot return more future (or larger) date of two dates
create or replace function maxDate (p_date1 date, p_date2 date)
return date
is
begin
if p_date1>=p_date2 then
return p_date1;
else
return p_date2;
end if;
end;
/
create or replace function getBlockedDates (p_productid number, p_beginDate date, p_endDate date, p_rentPeriod pls_integer)
return my_globals.t_dates
as
v_dates my_globals.t_dates;
v_begindate date;
v_enddate date;
i pls_integer;
begin
i:=1; --collection counts from 1
v_enddate:=p_beginDate-1;
for r_date in my_globals.c_searchRentData(p_productid, p_beginDate, p_endDate, p_rentPeriod)
loop
if (v_enddate < r_date.beginRentDate) or (v_enddate < r_date.endRentDate)
then
--if previous enddate is bigger use that one
v_begindate:=maxDate(r_date.beginRentDate-p_rentPeriod,v_enddate+1); --first date of blocked period
v_enddate:=maxDate(r_date.endRentDate,v_enddate+1); --last date of blocked period
for j in 1..v_enddate-v_begindate+1 loop
v_dates(i):=v_begindate+j-1;
i:=i+1;
end loop;
end if;
end loop;
return v_dates;
end;
/
create or replace function getBlockedDatesAsStr (p_productid number, p_beginDate date, p_endDate date, p_rentPeriod pls_integer)
return varchar2
as
v_dates varchar2(4096) := ''; --should be sufficient for one year of blocked dates
v_begindate date;
v_enddate date;
i pls_integer;
begin
i:=1; --collection counts from 1
v_enddate:=p_beginDate-1;
for r_date in my_globals.c_searchRentData(p_productid, p_beginDate, p_endDate, p_rentPeriod)
loop
if (v_enddate < r_date.beginRentDate) or (v_enddate < r_date.endRentDate)
then
--if previous enddate is bigger use that one
v_begindate:=maxDate(r_date.beginRentDate-p_rentPeriod,v_enddate+1); --first date of blocked period
v_enddate:=maxDate(r_date.endRentDate,v_enddate+1); --last date of blocked period
for j in 1..v_enddate-v_begindate+1 loop
v_dates:=v_dates||' '||to_char(v_begindate+j-1,'dd-mm-yyyy');
i:=i+1;
end loop;
end if;
end loop;
return ltrim(v_dates);
end;
/
create or replace function FindAndSplit(haystack in out varchar2, needle in varchar2)
return varchar2
is
s2 varchar2(1000);
idx pls_integer;
begin
--dbms_output.put_line('in:'||haystack);
idx:=instr(haystack,needle);
if (idx=0) then
--return full haystack when needle not found
s2:=haystack;
--remaining haystack is empty
haystack:='';
return s2;
end if;
--find string left at idx
s2:=substr(haystack,1,idx-1);
--dbms_output.put_line('out:'||s2);
--remaining haystack is string right at idx
haystack:=substr(haystack,idx+1,length(haystack)-idx);
--dbms_output.put_line('return:'||haystack);
return s2;
end;
/
--testcases
declare
v_dates my_globals.t_dates;
i pls_integer;
begin
--store the result of stored function in local collection
v_dates:=getBlockedDates(1, to_date('01-01-2016','dd-mm-yyyy'), to_date('01-01-2016','dd-mm-yyyy')+365, 1);
--iterate through collection
FOR i IN 1 .. v_dates.count LOOP
dbms_output.put_line('Blocked date: '||v_dates(i));
end loop;
dbms_output.put_line('');
--store the result of stored function in local collection
v_dates:=getBlockedDates(2, to_date('01-01-2016','dd-mm-yyyy'), to_date('01-01-2016','dd-mm-yyyy')+365, 1);
--iterate through collection
FOR i IN 1 .. v_dates.count LOOP
dbms_output.put_line('Blocked date: '||v_dates(i));
end loop;
end;
/
declare
v_dates varchar2(4096);
v_date varchar2(10);
i pls_integer;
begin
--store the result of stored function in local string
v_dates:=getBlockedDatesAsStr(1, to_date('01-01-2016','dd-mm-yyyy'), to_date('01-01-2016','dd-mm-yyyy')+365, 1);
dbms_output.put_line(v_dates);
--iterate through string
loop
v_date:=FindAndSplit(v_dates,' ');
dbms_output.put_line('Blocked date: '||v_date);
exit when v_dates is null;
end loop;
dbms_output.put_line('');
--store the result of stored function in local string
v_dates:=getBlockedDatesAsStr(2, to_date('01-01-2016','dd-mm-yyyy'), to_date('01-01-2016','dd-mm-yyyy')+365, 1);
--iterate through string
loop
v_date:=FindAndSplit(v_dates,' ');
dbms_output.put_line('Blocked date: '||v_date);
exit when v_dates is null;
end loop;
end;
这篇关于租用有库存产品的日期不可用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
