Monday, October 10, 2016

Compare Two Lists

var jurisdictionMap = {};

tw.local.errorMessage = "";

for (var i = 0; i < tw.local.jurisdictionNVP.listLength; i++) {
    jurisdictionMap[tw.local.jurisdictionNVP[i].name] = tw.local.jurisdictionNVP[i].value;
}

for(var i=0;i< tw.local.clientCashRows.listLength; i++)
{
    var jurisdictionName = tw.local.clientCashRows[i].jurisdiction;
    if(!!jurisdictionName)
    {
         if(!jurisdictionMap[jurisdictionName])
         {
              tw.local.errorMessage += (tw.local.errorMessage ? ", " : "") + jurisdictionName;
              var errorMessage = "Jurisdiction is not present in table Please verify once " + jurisdictionName;
              log4j.error(errorMessage);
         }
    }
}

if (tw.local.errorMessage) {
    tw.local.errorMessage = "The following Jurisdiction are not present in the Jurisdiction Table: " + tw.local.errorMessage;
    tw.local.isError = true;
 }

Tuesday, October 4, 2016

Combine Multiple Statements into one Query(SQL Server)



<# tw.local.parameters = new tw.object.listOf.SQLParameter(); #>

declare @wItemId bigint = ?; <# APCAddSqlParameter(tw.local.parameters, tw.local.wItemId); #>
declare @userName nvarchar(255) = ?; <# APCAddSqlParameter(tw.local.parameters, tw.local.userName); #>
declare @historyLength int = 10;

declare @itemId bigint;
declare @roomId bigint;
declare @homeId bigint;

select top 1
    @itemId = ERP.itemId ,
    @roomId = PRJ.roomId ,
    @homeId = ENG.homeId
from WItem2EntityReportingPeriod WI2ERP
    join EntityReportingPeriod ERP on WI2ERP.entityReportingPeriodId = ERP.entityReportingPeriodId
    join Project PRJ on PRJ.roomId = ERP.roomId
    join Engagement ENG on ENG.engagementId = PRJ.engagementId
where WI2ERP.wItemId = @wItemId ;

update UserActivity
set lastUpdated = CURRENT_TIMESTAMP
where userName = @userName
    and itemId = @itemId
    and roomId = @roomId
    and homeId = @homeId ;

if @@ROWCOUNT = 0
begin
    -- delete old rows for user from UserActivity
    with uaDates as (
        select userActivityId
            , row_number() over (order by lastUpdated desc) as rowNumber
        from UserActivity
        where userName = @userName
    )
    delete UserActivity
    from UserActivity UA
        join uaDates on UA.userActivityId = uaDates.userActivityId
    where uaDates.rowNumber >= @historyLength;

    -- add new UserActivity
    insert into UserActivity (userActivityId, userName, homeId , roomId , itemId , lastUpdated)
    values (next value for UserActivity_Seq, @userName, @homeId , @roomId , @itemId , CURRENT_TIMESTAMP);
end