Raporty

<Argument>
<Type>List</Type>
<Name>DocumentType</Name>
<VisibleName>Licz dla</VisibleName>
<DefaultSql>
<![CDATA[
DECLARE @docTypeId INT
SET @docTypeId = (SELECT COALESCE(
(
SELECT TOP 1 CAST(ruav.ArgumentValue AS nvarchar(max))
FROM esm.ReportsUserArguments ruav WHERE ruav.UserName = @LoginName AND ruav.ReportFileName = @ReportFileName AND ruav.ArgumentName = 'DocumentType'
)
, ( SELECT 1)
))
			  
SELECT * FROM (
SELECT 0 as Id, 'Zamówień' as Name
UNION ALL
SELECT 1, 'Ofert'
UNION ALL
SELECT 2, 'Faktur'
 ) p WHERE p.Id = @docTypeId 
]]>
</DefaultSql>
<ListSql>
<![CDATA[
SELECT 0, 'Zamówień'
UNION ALL
SELECT 1, 'Ofert'
UNION ALL
SELECT 2, 'Faktur'
]]>
</ListSql>
</Argument>

<Argument>
<Type>Date</Type>
<Name>DateFrom</Name>
<VisibleName>Data początkowa</VisibleName>
<DefaultSql>
<![CDATA[
SELECT COALESCE(
(
SELECT TOP 1 CAST(ruav.ArgumentValue AS DATETIME)
FROM esm.ReportsUserArguments ruav WHERE ruav.UserName = @LoginName AND ruav.ReportFileName = @ReportFileName AND ruav.ArgumentName = 'DateFrom'
)
, ( SELECT esm.GetDateFromParts ( YEAR(GETDATE()), 1, 1 ) )
)
]]>
</DefaultSql>
<ListSql></ListSql>
</Argument>

<Argument>
<Type>Custom</Type>
<CustomTypeName>Operators</CustomTypeName>
<Name>Operators</Name>
<VisibleName>Operatorzy</VisibleName>
<IsRequired>true</IsRequired>
</Argument>

<Argument>
<Type>Text</Type>
<Name>ArticleCode</Name>
<VisibleName>Kod artykułu</VisibleName>
<IsRequired>false</IsRequired>
</Argument>

<Argument>
<Type>Autocomplete</Type>
<Name>LocationCode</Name>
<VisibleName>Położenie</VisibleName>
<AutocompleteSqlProc>
<Name>WMS.Reports_GetLocationsAutocompleteData</Name>
<Parameters>
<ArgumentValueParameter paramname="WarehouseId">WarehouseId</ArgumentValueParameter>
</Parameters>
</AutocompleteSqlProc>
<DefaultSql></DefaultSql>
<IsRequired>false</IsRequired>
</Argument>

<LayoutRow>
<SpaceColumn width="10"/>
<SpaceColumn width="5"/>
<SpaceColumn width="10"/>
<SpaceColumn width="5"/>
</LayoutRow>

<HeaderRow>
<TextColumn>Rejon</TextColumn>
<TextColumn>Plan</TextColumn>
<TextColumn precision="2">Realizacja</TextColumn>
<TextColumn precision="2">%</TextColumn>
</HeaderRow>

<Element>
<Id>1</Id>
<HeaderSql><![CDATA[select 'Zrealizowane pozycje w okresie ' + left(convert(varchar, @DateFrom, 120), 10)  + ' - ' + left(convert(varchar, @DateTo, 120), 10)]]></HeaderSql>
<Type>Table</Type>
<Sql>
<![CDATA[
set @DateTo = dateadd(second, -1, dateadd(day, 1, @DateTo))
declare @CurrentOperator nvarchar(128)
select @CurrentOperator = UserName from WMS.vCurrentSession
;with data as
(
select 
CONVERT(date, coalesce(i.RealizationDate, d.DocumentAceptanceDate)) as RealizationDate,
a.Code as Article, 
d.NumberString as DocumentNumber,
dt.Code as DocumentType, 
luo.Barcode as LogisticUnit, 
i.OperatorLogin as LoginName,
i.RealizedQuantity
from wms.Documents d with (nolock)
join wms.vItems i with (nolock) on d.Id=i.DocumentId
join [WMS].[Dispatcher_GetSubordinateOperators](@CurrentOperator, 1) S on S.SubordinateLoginName = i.OperatorLogin
join wms.varticles a with (nolock) on a.id = i.articleid
join wms.DocumentTypes dt with (nolock) on d.DocumentTypeId=dt.Id
left join wms.LogisticUnitObjects luo with (nolock) on i.LogisticUnitId=luo.Id
WHERE d.DocumentStateId = 2 and 
((i.RealizationPercent >= 100 and i.RealizationDate between @DateFrom and @DateTo) 
or (i.RealizationPercent > 0 and d.DocumentAceptanceDate between @DateFrom and @DateTo)))
select 
left(convert(varchar, RealizationDate, 120), 10) as Date,
Article, 
DocumentNumber,  
DocumentType, 
LogisticUnit, 
LoginName, 
sum(RealizedQuantity) as Quantity
from data
group by RealizationDate, Article, DocumentNumber, DocumentType, LogisticUnit, LoginName
]]>
</Sql>
<TableHeader>
<LayoutRow>
<SpaceColumn width="10"/><SpaceColumn width="13"/><SpaceColumn width="8"/><SpaceColumn width="4"/><SpaceColumn width="6"/><SpaceColumn width="10"/>&lt;SpaceColumn width="10"/>
</LayoutRow>
<HeaderRow>
<TextColumn>Data</TextColumn>
<TextColumn>Towar</TextColumn>
<TextColumn>Dyspozycja</TextColumn>
<TextColumn>Typ</TextColumn>
<TextColumn>Ilość</TextColumn>
<TextColumn>Jednostka logistyczna</TextColumn>
<TextColumn>Wykonał</TextColumn>
</HeaderRow>
</TableHeader>
<NonGroupingColumns>
<SqlColumn>Date</SqlColumn>
<SqlColumn>Article</SqlColumn>
<SqlColumn>DocumentNumber</SqlColumn>
<SqlColumn>DocumentType</SqlColumn>
<SqlColumn align="right">Quantity</SqlColumn>
<SqlColumn>LogisticUnit</SqlColumn>
<SqlColumn>LoginName</SqlColumn>
</NonGroupingColumns>
</Element>

<TotalSummaryColumns>
<TextColumn align="center">Podsumowanie</TextColumn>
<TextColumn></TextColumn>
<SqlColumn align="center" precision="2">TotalOrdersNetValue</SqlColumn>
<SqlColumn align="center" precision="2">TotalOrdersGrossValue</SqlColumn>
<SqlColumn align="center">TotalOrdersNumber</SqlColumn>
</TotalSummaryColumns>

<Element>
<Id>1</Id>
<HeaderSql>
<![CDATA[select 'Szczegółowy raport pracy magazynierów za okres ' + left(convert(varchar, @DateFrom, 120), 10)  + ' - ' + left(convert(varchar, @DateTo, 120), 10)]]>
</HeaderSql>
<Type>PivotTable</Type>
<Sql>
<![CDATA[
set @DateTo = dateadd(second, -1, dateadd(day, 1, @DateTo))
declare @CurrentOperator nvarchar(128)
select @CurrentOperator = UserName from WMS.vCurrentSession
;with DocumentsOperatorsData as
(select distinct i.OperatorLogin, i.DocumentId from wms.items i with (nolock)
where I.RealizationPercent > 0 and i.OperatorLogin is not null), data as
(select O.LoginName, E.Name, E.Surname, DT.Code AS DispositionCode, 
case DT.Id 
when 1 then 'Awizo dostawy' 
when 2 then 'Zlecenie wydania z magazynu' 
when 3 then 'Przesunięcie wewnątrzmagazynowe' 
when 4 then 'Inwentaryzacja' 
when 5 then 'Bilans stanu towarów' end as DispositionName,
left(convert(date, d.DocumentAceptanceDate), 10) as DocumentDate, count(*) as DispositionsCount
from [WMS].[vOperators] O with (nolock)
join [WMS].[Dispatcher_GetSubordinateOperators](@CurrentOperator, 1) S on S.SubordinateLoginName = O.LoginName
left join @Operators_SelectedOperators so on so.Item = O.LoginName
join [WMS].[vEmployees] E with (nolock) ON E.Id = O.EmployeeId
join DocumentsOperatorsData DO on DO.OperatorLogin = O.LoginName
join [WMS].[Documents] D with (nolock) on D.Id = DO.DocumentId 
join [WMS].[DocumentTypes] DT with (nolock) on Dt.Id = D.DocumentTypeId
where d.DocumentAceptanceDate is not null and d.DocumentAceptanceDate between @DateFrom and @DateTo
and (so.Item is not null or @Operators_SelectedOperators_all = 1)
group by O.LoginName, E.Name, E.Surname, DT.Code, DT.Id, convert(date, d.DocumentAceptanceDate))
select LoginName + 
case when (Name <> '' or Surname <> '') then ' (' + Name + ' ' + Surname + ')' else '' end as Operator, DispositionCode + ' (' + DispositionName + ')' as DispositionType, DocumentDate, DispositionsCount from data
]]>
</Sql>
<PivotTable showTotalSummary="true">
<RowTemplate>
<ColumnTemplate width="2" name="Operator" sqlColumn="Operator" showSummary="true"/>
<ColumnTemplate width="2" name="Typ dyspozycji" sqlColumn="DispositionType" showSummary="true"/>
<ColumnTemplate width="2" name="Data wykonania" sqlColumn="DocumentDate" />
<ColumnTemplate width="1" name="Ilość zadań" groupingType="Sum" sqlColumn="DispositionsCount" align="right" />
</RowTemplate>
</PivotTable>
</Element>

<Element>
<Id>1</Id>
<HeaderSql>...</HeaderSql>
<Type>Line</Type>
<Sql>...</Sql>
<ChartColumns>
<LabelsSqlColumn>Date</LabelsSqlColumn>
<SeriesSqlColumn>LoginName</SeriesSqlColumn>
<ValuesSqlColumn>DispositionsCount</ValuesSqlColumn>
</ChartColumns>
</Element>

<VerticalLayout width="1" height="1000px">
<WidthSql><![CDATA[
select cast(300 + DATEDIFF (day , @DateFrom , @DateTo) * 30 as varchar)+'px'
]]></WidthSql>
<Element height="2">2</Element>
<Horizontal height="3" minwidth="925">
<Vertical width="5">
<Element height="3">3</Element>
<Element cssclass="average-performance-element" height="2">4</Element>
</Vertical>
<Element width="7">1</Element>
</Horizontal>
</VerticalLayout>

<Css>
.avg-perf { color: white; font-weight: bold; height: 100%; }
.avg-perf-value { font-size: 5em; }
.avg-perf-title { font-size: 2em; }
.customHeaderColumn {height: 35px;}
.average-performance-element>.report-element-content { background-color: #a8c6d5; padding: 5px 50px 15px 50px; }
.weekend-day { color: red; }
</Css>