private ApplicationListResult R0042Export(ApplicationMetadata application, PaginatedSearchRequestDto dto)
{
var refDate = dto.SearchValues;
var dates = refDate.Split('/'); // comes as 04/2018
var newDto = new PaginatedSearchRequestDto {
NeedsCountUpdate = false
};
var year = int.Parse(dates[1]);
var month = int.Parse(dates[0]);
var extractionDate = new DateTime(year, month, 1);
newDto.AppendWhereClauseFormat("month(asset.changedate) = {0} and year(asset.changedate) = {1}", month, year);
newDto.AppendSearchEntry("status", "!=DECOMMISSIONED");
var dbList = base.GetList(application, newDto);
if (!dbList.ResultObject.Any())
{
return(dbList);
}
var map = R0042QueryHelper.BuildIdMap(dbList);
var assetIds = map.Keys.Select(s => s.AssetId).ToList();
var assetNums = map.Keys.Select(s => s.AssetNum).ToList();
var historicData = GetDAO().FindByNativeQuery(
"select * from HIST_ASSETR0042 m inner join (select assetid,max(extractiondate) as max_date from HIST_ASSETR0042 where assetid in (:p0) and ExtractionDate <= :p1 group by assetid)i on (m.assetid = i.assetid and m.extractiondate = i.max_date) where m.assetid in (:p0) and m.extractiondate <= :p1", assetIds, extractionDate);
var imacDataSet = GetImacDataSet();
var imacs = imacDataSet.GetClosedImacIdsForR0042(assetNums, month, year);
R0042QueryHelper.MergeImacData(map, imacs);
R0042QueryHelper.MergeData(map, historicData);
return(dbList);
}
//for each assetnum/site tuple the
public IDictionary <R0042AssetKey, ISet <string> > GetClosedImacIdsForR0042(List <string> assetIds, int month, int year)
{
var result = new Dictionary <R0042AssetKey, ISet <string> >();
var compAppMetadata = MetadataProvider.Application(ApplicationName());
var dto = new PaginatedSearchRequestDto {
NeedsCountUpdate = false
};
dto.ProjectionFields.Add(ProjectionField.Default("ticketid"));
dto.ProjectionFields.Add(ProjectionField.Default("ticketuid"));
dto.ProjectionFields.Add(ProjectionField.Default("assetnum"));
dto.ProjectionFields.Add(ProjectionField.Default("siteid"));
dto.ProjectionFields.Add(ProjectionField.Default("woactivity_.OWNERGROUP"));
dto.AppendWhereClauseFormat("woactivity_.OWNERGROUP = 'I-EUS-DE-CSC-SDK-ASSET' and month(woactivity_.ACTFINISH) = {0} and year(woactivity_.ACTFINISH) = {1} and imac.assetnum in ({2})", month, year, BaseQueryUtil.GenerateInString(assetIds));
var entityMetadata = MetadataProvider.SlicedEntityMetadata(new ApplicationMetadataSchemaKey("detail"), "imac");
var searchEntityResult = EntityRepository.GetAsRawDictionary(entityMetadata, dto);
foreach (var imac in searchEntityResult.ResultList)
{
var key = new R0042AssetKey {
AssetNum = imac["assetnum"].ToString(),
SiteId = imac["siteid"].ToString()
};
if (result.ContainsKey(key))
{
result[key].Add(imac["ticketid"].ToString());
}
else
{
result[key] = new HashSet <string> {
imac["ticketid"].ToString()
};
}
}
return(result);
}
