public override bool Perform()
{
Guid webAppId = Web.Site.WebApplication.Id;
try
{
SPSecurity.RunWithElevatedPrivileges(() =>
{
LogMessage("Connecting to the database . . .", 2);
string epmLiveCnStr = CoreFunctions.getConnectionString(webAppId);
using (var epmLiveCn = new SqlConnection(epmLiveCnStr))
{
epmLiveCn.Open();
// add LastSubmittedByName column
if (!epmLiveCn.ColumnExist("dbo.TSTIMESHEET", "LastSubmittedByName"))
{
epmLiveCn.AddColumn("dbo.TSTIMESHEET", "LastSubmittedByName [varchar](255) NULL");
LogMessage("LastSubmittedByName column added to TSTIMESHEET table", MessageKind.SUCCESS, 4);
}
else
{
LogMessage("LastSubmittedByName column already exists in TSTIMESHEET table", MessageKind.SKIPPED, 4);
}
// add LastSubmittedByUser column
if (!epmLiveCn.ColumnExist("dbo.TSTIMESHEET", "LastSubmittedByUser"))
{
epmLiveCn.AddColumn("dbo.TSTIMESHEET", "LastSubmittedByUser [varchar](255) NULL");
LogMessage("LastSubmittedByUser column added to TSTIMESHEET table", MessageKind.SUCCESS, 4);
}
else
{
LogMessage("LastSubmittedByUser column already exists in TSTIMESHEET table", MessageKind.SKIPPED, 4);
}
}
});
return(true);
}
catch (Exception exception)
{
string message = exception.InnerException != null
? exception.InnerException.Message
: exception.Message;
LogMessage(message, MessageKind.FAILURE, 4);
return(false);
}
}
public override bool Perform()
{
Guid webAppId = Web.Site.WebApplication.Id;
try
{
SPSecurity.RunWithElevatedPrivileges(() =>
{
LogMessage("Connecting to the database . . .", 2);
string epmLiveCnStr = CoreFunctions.getConnectionString(webAppId);
using (var epmLiveCn = new SqlConnection(epmLiveCnStr))
{
epmLiveCn.Open();
// add Rate column
if (!epmLiveCn.ColumnExist("dbo.TSITEM", "Rate"))
{
epmLiveCn.AddColumn("dbo.TSITEM", "Rate [varchar](50) NULL");
LogMessage("Rate column added to TSITEM table", MessageKind.SUCCESS, 4);
}
else
{
LogMessage("Rate column already exists in TSITEM table", MessageKind.SKIPPED, 4);
}
}
});
}
catch (Exception exception)
{
string message = exception.InnerException != null
? exception.InnerException.Message
: exception.Message;
LogMessage(message, MessageKind.FAILURE, 4);
}
try
{
SPSecurity.RunWithElevatedPrivileges(() =>
{
LogMessage("Connecting to the database . . .", 2);
string epmLiveCnStr = CoreFunctions.getConnectionString(webAppId);
using (var epmLiveCn = new SqlConnection(epmLiveCnStr))
{
epmLiveCn.Open();
var metaViewDefinition = epmLiveCn.GetViewDefinition("dbo.vwMeta");
var rateColumn = "TSITEM_1.Rate";
if (metaViewDefinition != null && !metaViewDefinition.Contains(rateColumn))
{
#region ViewCode
epmLiveCn.ExecuteNonQuery($@"ALTER VIEW [dbo].[vwMeta] AS
SELECT dbo.TSTIMESHEET.USERNAME AS Username, dbo.TSTIMESHEET.RESOURCENAME AS [Resource Name], dbo.TSUSER.USER_ID AS SharePointAccountID, dbo.TSITEM.LIST_UID, dbo.TSITEM.ITEM_ID, dbo.TSITEM.TS_ITEM_UID AS [Item UID],
dbo.TSITEM.TITLE AS [Item Name], dbo.TSITEM.PROJECT AS Project, dbo.TSITEM.PROJECT_ID AS ProjectID, COALESCE (dbo.TSMETA.ListName + '_' + dbo.TSMETA.ColumnName, 'TempColumn')
AS ColumnName, dbo.TSMETA.DisplayName, dbo.TSMETA.ColumnValue, dbo.TSITEM.ITEM_TYPE AS [Item Type], dbo.TSITEM.TS_UID AS [Timesheet UID], dbo.TSPERIOD.PERIOD_ID AS [Period Id],
dbo.TSPERIOD.SITE_ID, dbo.TSITEM.LIST AS List, dbo.TSITEMHOURS.TS_ITEM_DATE AS Date, dbo.TSITEMHOURS.TS_ITEM_HOURS AS Hours, dbo.TSITEMHOURS.TS_ITEM_TYPE_ID AS [Type Id],
dbo.TSTYPE.TSTYPE_NAME AS [Type Name], dbo.TSPERIOD.PERIOD_START AS [Period Start], dbo.TSPERIOD.PERIOD_END AS [Period End],
CASE dbo.TSTIMESHEET.SUBMITTED WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END AS Submitted,
CASE dbo.TSTIMESHEET.APPROVAL_STATUS WHEN 0 THEN 'Pending' WHEN 1 THEN 'Approved' WHEN 2 THEN 'Rejected' END AS [Approval Status],
CASE dbo.TSITEM.APPROVAL_STATUS WHEN 0 THEN 'Pending' WHEN 1 THEN 'Approved' WHEN 2 THEN 'Rejected' END AS [PM Approval Status], CONVERT(varchar(8000),
COALESCE (dbo.TSTIMESHEET.APPROVAL_NOTES, '')) AS [Approval Notes], dbo.TSTIMESHEET.LASTMODIFIEDBYN, CONVERT(varchar(MAX), dbo.TSNOTES.TS_ITEM_NOTES) AS TS_ITEM_NOTES,
dbo.TSTIMESHEET.APPROVAL_DATE, COALESCE (dbo.TSTIMESHEET.LastSubmittedByName, dbo.TSTIMESHEET.LASTMODIFIEDBYN) AS LastSubmittedByName,
COALESCE (dbo.TSTIMESHEET.LastSubmittedByUser, dbo.TSTIMESHEET.LASTMODIFIEDBYU) AS LastSubmittedByUser
FROM dbo.TSITEM INNER JOIN
dbo.TSTIMESHEET ON dbo.TSITEM.TS_UID = dbo.TSTIMESHEET.TS_UID INNER JOIN
dbo.TSPERIOD ON dbo.TSTIMESHEET.PERIOD_ID = dbo.TSPERIOD.PERIOD_ID AND dbo.TSTIMESHEET.SITE_UID = dbo.TSPERIOD.SITE_ID INNER JOIN
dbo.TSITEMHOURS ON dbo.TSITEM.TS_ITEM_UID = dbo.TSITEMHOURS.TS_ITEM_UID LEFT OUTER JOIN
dbo.TSNOTES ON dbo.TSITEM.TS_ITEM_UID = dbo.TSNOTES.TS_ITEM_UID AND dbo.TSITEMHOURS.TS_ITEM_DATE = dbo.TSNOTES.TS_ITEM_DATE LEFT OUTER JOIN
dbo.TSMETA ON dbo.TSITEM.TS_ITEM_UID = dbo.TSMETA.TS_ITEM_UID LEFT OUTER JOIN
dbo.TSTYPE ON dbo.TSTIMESHEET.SITE_UID = dbo.TSTYPE.SITE_UID AND dbo.TSITEMHOURS.TS_ITEM_TYPE_ID = dbo.TSTYPE.TSTYPE_ID LEFT OUTER JOIN
dbo.TSUSER ON dbo.TSTIMESHEET.TSUSER_UID = dbo.TSUSER.TSUSERUID
UNION
SELECT dbo.TSTIMESHEET.USERNAME AS Username, dbo.TSTIMESHEET.RESOURCENAME AS [Resource Name], dbo.TSUSER.USER_ID AS SharePointAccountID, dbo.TSITEM.LIST_UID, dbo.TSITEM.ITEM_ID, dbo.TSITEM.TS_ITEM_UID AS [Item UID],
dbo.TSITEM.TITLE AS [Item Name], dbo.TSITEM.PROJECT AS Project, dbo.TSITEM.PROJECT_ID AS ProjectID, COALESCE (dbo.TSMETA.ColumnName, 'TempColumn') AS ColumnName, dbo.TSMETA.DisplayName,
dbo.TSMETA.ColumnValue, dbo.TSITEM.ITEM_TYPE AS [Item Type], dbo.TSITEM.TS_UID AS [Timesheet UID], dbo.TSPERIOD.PERIOD_ID AS [Period Id], dbo.TSPERIOD.SITE_ID, dbo.TSITEM.LIST AS List,
dbo.TSITEMHOURS.TS_ITEM_DATE AS Date, dbo.TSITEMHOURS.TS_ITEM_HOURS AS Hours, dbo.TSITEMHOURS.TS_ITEM_TYPE_ID AS [Type Id], dbo.TSTYPE.TSTYPE_NAME AS [Type Name],
dbo.TSPERIOD.PERIOD_START AS [Period Start], dbo.TSPERIOD.PERIOD_END AS [Period End], CASE dbo.TSTIMESHEET.SUBMITTED WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END AS Submitted,
CASE dbo.TSTIMESHEET.APPROVAL_STATUS WHEN 0 THEN 'Pending' WHEN 1 THEN 'Approved' WHEN 2 THEN 'Rejected' END AS [Approval Status],
CASE dbo.TSITEM.APPROVAL_STATUS WHEN 0 THEN 'Pending' WHEN 1 THEN 'Approved' WHEN 2 THEN 'Rejected' END AS [PM Approval Status], CONVERT(varchar(8000),
COALESCE (dbo.TSTIMESHEET.APPROVAL_NOTES, '')) AS [Approval Notes], dbo.TSTIMESHEET.LASTMODIFIEDBYN, CONVERT(varchar(MAX), dbo.TSNOTES.TS_ITEM_NOTES) AS Expr1,
dbo.TSTIMESHEET.APPROVAL_DATE, COALESCE (dbo.TSTIMESHEET.LastSubmittedByName, dbo.TSTIMESHEET.LASTMODIFIEDBYN) AS LastSubmittedByName,
COALESCE (dbo.TSTIMESHEET.LastSubmittedByUser, dbo.TSTIMESHEET.LASTMODIFIEDBYU) AS LastSubmittedByUser
FROM dbo.TSITEM INNER JOIN
dbo.TSTIMESHEET ON dbo.TSITEM.TS_UID = dbo.TSTIMESHEET.TS_UID INNER JOIN
dbo.TSPERIOD ON dbo.TSTIMESHEET.PERIOD_ID = dbo.TSPERIOD.PERIOD_ID AND dbo.TSTIMESHEET.SITE_UID = dbo.TSPERIOD.SITE_ID INNER JOIN
dbo.TSITEMHOURS ON dbo.TSITEM.TS_ITEM_UID = dbo.TSITEMHOURS.TS_ITEM_UID LEFT OUTER JOIN
dbo.TSNOTES ON dbo.TSITEMHOURS.TS_ITEM_DATE = dbo.TSNOTES.TS_ITEM_DATE AND dbo.TSITEM.TS_ITEM_UID = dbo.TSNOTES.TS_ITEM_UID LEFT OUTER JOIN
dbo.TSMETA ON dbo.TSITEM.TS_ITEM_UID = dbo.TSMETA.TS_ITEM_UID AND dbo.TSITEM.LIST = dbo.TSMETA.ListName LEFT OUTER JOIN
dbo.TSTYPE ON dbo.TSTIMESHEET.SITE_UID = dbo.TSTYPE.SITE_UID AND dbo.TSITEMHOURS.TS_ITEM_TYPE_ID = dbo.TSTYPE.TSTYPE_ID LEFT OUTER JOIN
dbo.TSUSER ON dbo.TSTIMESHEET.TSUSER_UID = dbo.TSUSER.TSUSERUID
UNION
SELECT TSTIMESHEET_1.USERNAME, TSTIMESHEET_1.RESOURCENAME AS [Resource Name], TSUSER_1.USER_ID AS SharePointAccountID, TSITEM_1.LIST_UID, TSITEM_1.ITEM_ID, TSITEM_1.TS_ITEM_UID, TSITEM_1.TITLE, TSITEM_1.PROJECT,
TSITEM_1.PROJECT_ID, 'Resource_' + dbo.TSRESMETA.ColumnName AS listcolumnname, dbo.TSRESMETA.DisplayName,
CASE dbo.TSRESMETA.ColumnName WHEN 'StandardRate' THEN CASE TSITEM_1.Rate WHEN null THEN dbo.TSRESMETA.ColumnValue ELSE TSITEM_1.Rate END END AS ColumnValue,
TSITEM_1.ITEM_TYPE, TSITEM_1.TS_UID,TSTIMESHEET_1.PERIOD_ID, TSTIMESHEET_1.SITE_UID, TSITEM_1.LIST, TSITEMHOURS_1.TS_ITEM_DATE, TSITEMHOURS_1.TS_ITEM_HOURS, TSITEMHOURS_1.TS_ITEM_TYPE_ID,
TSTYPE_1.TSTYPE_NAME, TSPERIOD_1.PERIOD_START AS Start, TSPERIOD_1.PERIOD_END AS [End], CASE TSTIMESHEET_1.SUBMITTED WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END AS Submitted,
CASE TSTIMESHEET_1.APPROVAL_STATUS WHEN 0 THEN 'Pending' WHEN 1 THEN 'Approved' WHEN 2 THEN 'Rejected' END AS [Approval Status],
CASE TSITEM_1.APPROVAL_STATUS WHEN 0 THEN 'Pending' WHEN 1 THEN 'Approved' WHEN 2 THEN 'Rejected' END AS [PM Approval Status], CONVERT(varchar(8000),
COALESCE (TSTIMESHEET_1.APPROVAL_NOTES, '')) AS [Approval Notes], TSTIMESHEET_1.LASTMODIFIEDBYN, CONVERT(varchar(MAX), dbo.TSNOTES.TS_ITEM_NOTES) AS TS_ITEM_NOTES,
TSTIMESHEET_1.APPROVAL_DATE, COALESCE (TSTIMESHEET_1.LastSubmittedByName, TSTIMESHEET_1.LASTMODIFIEDBYN) AS LastSubmittedByName, COALESCE (TSTIMESHEET_1.LastSubmittedByUser,
TSTIMESHEET_1.LASTMODIFIEDBYU) AS LastSubmittedByUser
FROM dbo.TSTIMESHEET AS TSTIMESHEET_1 INNER JOIN
dbo.TSRESMETA ON TSTIMESHEET_1.TS_UID = dbo.TSRESMETA.TS_UID INNER JOIN
dbo.TSITEM AS TSITEM_1 ON TSTIMESHEET_1.TS_UID = TSITEM_1.TS_UID INNER JOIN
dbo.TSITEMHOURS AS TSITEMHOURS_1 ON TSITEM_1.TS_ITEM_UID = TSITEMHOURS_1.TS_ITEM_UID INNER JOIN
dbo.TSPERIOD AS TSPERIOD_1 ON TSTIMESHEET_1.PERIOD_ID = TSPERIOD_1.PERIOD_ID AND TSTIMESHEET_1.SITE_UID = TSPERIOD_1.SITE_ID LEFT OUTER JOIN
dbo.TSNOTES ON TSITEMHOURS_1.TS_ITEM_DATE = dbo.TSNOTES.TS_ITEM_DATE AND TSITEM_1.TS_ITEM_UID = dbo.TSNOTES.TS_ITEM_UID LEFT OUTER JOIN
dbo.TSTYPE AS TSTYPE_1 ON TSTIMESHEET_1.SITE_UID = TSTYPE_1.SITE_UID AND TSITEMHOURS_1.TS_ITEM_TYPE_ID = TSTYPE_1.TSTYPE_ID LEFT OUTER JOIN
dbo.TSUSER AS TSUSER_1 ON TSTIMESHEET_1.TSUSER_UID = TSUSER_1.TSUSERUID" );
#endregion
LogMessage("TSItem.Rate, TSItem.Rate columns added to the vwMeta view", MessageKind.SUCCESS, 4);
}
else
{
LogMessage("TSItem.Rate, TSItem.Rate columns already exists in the vwMeta view", MessageKind.SKIPPED, 4);
}
}
});
return(true);
}
catch (Exception exception)
{
string message = exception.InnerException != null
? exception.InnerException.Message
: exception.Message;
LogMessage(message, MessageKind.FAILURE, 4);
return(false);
}
}
