Месяц: Июль 2016

". ($saveXml ? "update {$this->db_our}[m_PWStatus] set xmlData = (" : '') . "
select 
tt8.priceN * cnt price,
sum(tt5.selected) over (partition by null) selectedCnt,
sum(coalesce(tt8.priceN * cnt,0)*tt5.selected) over (partition by null) selectedSum,
sum(tt5.moot1) over (partition by tt5.h2_id) smoot1,
sum(tt5.moot2) over (partition by tt5.h2_id) smoot2,
(select count(id) from {$this->db_our}[m_PWSubStatus] pw where pw.deleted is null and pw.statusId=1 and pw.pmesId = h2_id) approvedInPmes,
 * from tt5
 outer apply (
	select et.name etName, ek.name ekName, ek.id ekId, eqs.serialNumber from {$this->db_our}[m_listMeasEqui] eqs
	 inner join {$this->db_our}m_typeMeasEqui et on (et.deleted is null and et.id = eqs.typeMeasEquiID)
	 inner join {$this->db_our}m_equiKinds ek on (ek.deleted is null and ek.id = et.equiKindsID)
	where 1=1 
		and eqs.deleted is null 	
		and eqs.id = tt5.measEquiId 
 ) tt7
 outer apply (
	select tt6.workNameF, tt6.price priceN, tt6.kindId kindId2, tt6.id isPriced from tt6
	where 1=1
	and tt6.documentId = tt5.documentID
	and tt6.entityTypeId = tt5.intEntityType
	and coalesce(tt6.kindId,0) = coalesce(tt7.ekId,0)
	and (coalesce(tt7.ekId,0) not in (30,40) or (tt7.ekId in (30,40) and coalesce(tt6.vl,0) = coalesce(tt5.vr,0)))
 ) tt8

 order by h1_id, h2_id, h3_id, TIName, entity, documentID, vr desc, createdD" .
($saveXml ? "
 for xml raw, BINARY BASE64, root('rows'), XMLSCHEMA('urn:api.mneti.ru') ) 
 where deleted is null and pfId={$p->id} and statusId=2" : '');

		$sqlReport = [['select * from (',''], ["
select *, sum(priceSum) over (partition by null) priceTotal from (
select h1_id, h2_id, h3_id, h1_name, h2_name, h3_name, entity, vr2 vr, workNameF, sum(cnt) cnt, sum(price) priceSum from (
",
"
group by h1_id, h2_id, h3_id, h1_name, h2_name, h3_name, entity, vr2, workNameF
 ) tt
 order by h1_id, h2_id, h3_id, h1_name, h2_name, h3_name, vr desc, entity, workNameF"]];

		$sql = "
DECLARE @xmlData XML

select @xmlData = xmlData from {$this->db_our}[m_PWStatus] where deleted is null and pfId={$p->id} and statusId=2

DECLARE @handle INT  
DECLARE @PrepareXmlStatus INT  

EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @xmlData, ''

".$sqlReport[$report][0]."
SELECT *, (case when ekId in (10) then null else vr end) vr2 FROM OPENXML(@handle, '/rows/ttt:row')
	WITH (
	price decimal(25,3)
	,selectedCnt int
	,selectedSum decimal(38,3)
	,smoot1 int
	,smoot2 int
	,approvedInPmes int
	,selected int
	,inOther int
	,id int
	,h1_id int
	,h2_id int
	,h3_id int
	,h1_name varchar(128)
	,h2_name varchar(128)
	,h3_name varchar(128)
	,h3_extId int
	,documentID int
	,dName varchar(128)
	,entity varchar(255)
	,createdD datetime
	,createdDH varchar(30)
	,docDateH varchar(30)
	,TIName varchar(4000)
	,vr float
	,cnt int
	,measEquiId int
	,pfId int
	,unitPriceId int
	,intEntityType int
	,hashedName varchar(128)
	,originalName varchar(256)
	,moot int
	,moot1 int
	,moot2 int
	,etName varchar(128)
	,ekName varchar(255)
	,ekId int
	,serialNumber varchar(64)
	,workNameF varchar(258)
	,priceN decimal(14,3)
	,kindId2 int
	,isPriced int	
	)
) tt ".$sqlReport[$report][1]."
    
EXEC sp_xml_removedocument @handle
";

select *,
 first_value(id) over (partition by gr order by id) fv,
 last_value(id) over (partition by gr order by id) lv0, -- неверный результат, совсем не последнее значение в группе
 last_value(id) over (partition by gr order by id RANGE BETWEEN UNBOUNDED PRECED, ING AND CURRENT ROW) lv1, -- неверный результат, вот оно значение для рамки окна по умолчанию
 last_value(id) over (partition by gr order by id RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) lv2,
 first_value(id) over (partition by gr order by id desc) lv3,
 max(id) over () mx,
 max(id) over (partition by gr) mxg,
 max(id) over (partition by gr order by id) mxg1 -- вот и max сломался, добавили применилась рамка окна по умолчанию
 from (values
 (1,1),(2,1),(3,1),
 (4,2),(5,2),
 (6,3),(7,3),(8,3)
) t(id, gr)
order by id, gr

id	gr	fv	lv0	lv1	lv2	lv3	mx	mxg	mxg1
1	1	1	1	1	3	3	8	3	1
2	1	1	2	2	3	3	8	3	2
3	1	1	3	3	3	3	8	3	3
4	2	4	4	4	5	5	8	5	4
5	2	4	5	5	5	5	8	5	5
6	3	6	6	6	8	8	8	8	6
7	3	6	7	7	8	8	8	8	7
8	3	6	8	8	8	8	8	8	8

Если предложение ORDER BY не указано, то для рамки окна используется весь раздел. Это относится только к тем функциям, которым не требуется предложение ORDER BY. Если предложение ROWS или RANGE не указаны, а указано предложение ORDER BY, то в качестве значения по умолчанию для рамки окна используется RANGE UNBOUNDED PRECEDING AND CURRENT ROW. Это относится только к тем функциям, которые могут принимать дополнительную спецификацию ROWS или RANGE. Например, ранжирующая функция не может принимать предложение ROWS или RANGE, поэтому данная рамка окна не может использоваться, даже несмотря на наличие предложения ORDER BY, а предложение ROWS или RANGE отсутствует.