private void UpgradePfeDatabase()
{
var connectionProvider = new PfeData.ConnectionProvider();
LogMessage("Connecting to the database.", 2);
using (TransactionScope scope = new TransactionScope())
using (var connection = connectionProvider.CreateConnection(Web))
{
connection.ExecuteNonQuery(AlterView_EPG_VW_RPT_Availability);
LogMessage("Alter View EPG_VW_RPT_Availability.", MessageKind.SUCCESS, 1);
connection.ExecuteNonQuery(DropProcedure_EPG_SP_RPT_GetCostCategories);
LogMessage("Drop Procedure EPG_SP_RPT_GetCostCategories.", MessageKind.SUCCESS, 1);
connection.ExecuteNonQuery(CreateProcedure_EPG_SP_RPT_GetCostCategories);
LogMessage("Create Procedure EPG_SP_RPT_GetCostCategories.", MessageKind.SUCCESS, 1);
connection.ExecuteNonQuery(AlterProcedure_EPG_SP_RPT_GetCost);
LogMessage("Alter Procedure EPG_SP_RPT_GetCost.", MessageKind.SUCCESS, 1);
connection.ExecuteNonQuery(AlterProcedure_EPG_SP_RPT_GetListCostCategory);
LogMessage("Alter Procedure EPG_SP_RPT_GetListCostCategory.", MessageKind.SUCCESS, 1);
connection.ExecuteNonQuery(AlterProcedure_EPG_SP_RPT_GetAvailability);
LogMessage("Alter Procedure EPG_SP_RPT_GetAvailability.", MessageKind.SUCCESS, 1);
connection.ExecuteNonQuery(AlterProcedure_EPG_SP_RPT_GetResources);
LogMessage("Alter Procedure EPG_SP_RPT_GetResources.", MessageKind.SUCCESS, 1);
scope.Complete();
}
}
private void UpgradePfeDatabase()
{
var connectionProvider = new PfeData.ConnectionProvider();
LogMessage("Connecting to the database.", 2);
using (var connection = connectionProvider.CreateConnection(Web))
{
var tableExists = connection.TableExist(ProjectRatesTableName);
if (!tableExists)
{
connection.ExecuteNonQuery(ProjectRatesCreateTableQuery);
connection.ExecuteNonQuery(ProjectRatesCreateIndexQuery);
LogMessage($"{ProjectRatesTableName} table created.", MessageKind.SUCCESS, 2);
}
else
{
LogMessage($"{ProjectRatesTableName} table already exists.", MessageKind.SKIPPED, 2);
}
var recordCount = connection.ExecuteReader(ProjectRatesGetMaintenanceRecordCountQuery, reader => reader.Read() ? reader.GetInt32(0) : 0);
if (recordCount == 0)
{
connection.ExecuteNonQuery(ProjectRatesPopulateMaintenanceRecordsQuery);
LogMessage("Added configuration records.", MessageKind.SUCCESS, 2);
}
else
{
LogMessage("Configuration records already exists.", MessageKind.SKIPPED, 2);
}
LogMessage("Rate per project feature ready to use.", MessageKind.SUCCESS, 1);
}
}
private void UpgradePfeDatabase()
{
var connectionProvider = new PfeData.ConnectionProvider();
LogMessage("Connecting to the database.", 2);
using (var connection = connectionProvider.CreateConnection(Web))
{
connection.ExecuteNonQuery(CreateIndexScript);
LogMessage("Index IX_EPGP_COST_CATEGORIES_BC_ID ON EPGP_COST_CATEGORIES has been successfully created.", MessageKind.SUCCESS, 1);
}
}
private void UpgradePfeDatabase()
{
var connectionProvider = new PfeData.ConnectionProvider();
LogMessage("Connecting to the database.", 2);
using (var connection = connectionProvider.CreateConnection(Web))
{
connection.ExecuteNonQuery(CreateIndexScript);
LogMessage("Index created.", MessageKind.SUCCESS, 1);
}
}
private void UpgradePfeDatabase()
{
var connectionProvider = new PfeData.ConnectionProvider();
LogMessage("Connecting to the database.", 2);
using (var connection = connectionProvider.CreateConnection(Web))
{
connection.ExecuteNonQuery(script);
LogMessage("EPG_SP_GetResourceRates has been successfully updated.", MessageKind.SUCCESS, 1);
}
}
private void UpgradePfeDatabase()
{
var connectionProvider = new PfeData.ConnectionProvider();
LogMessage("Connecting to the database.", 2);
using (var connection = connectionProvider.CreateConnection(Web))
{
connection.ExecuteNonQuery(CreateIndexScript);
LogMessage("Index IX_EPG_RESOURCEPLANS_HOURS_CMT_UID_PRD_ID_CMH_PENDING ON EPG_RESOURCEPLANS_HOURS has been successfully created.", MessageKind.SUCCESS, 1);
}
}
/// <summary>
/// Upgrades the pfe database:
/// * Create column for projects table - column will be used as source for calculations in PFE instead of sharepoint list
/// * Create discount columns in detail values table - will help us on server side recalculations when discount rate is changed, so we will not have to find original rate
/// * Update stored procedure used to get cost detail values from VB6 code
/// * Populate configuration details table with missing data (partially data already populated by 6.4.0.1 step)
/// </summary>
private void UpgradePfeDatabase()
{
var connectionProvider = new PfeData.ConnectionProvider();
LogMessage("Connecting to the database.", 2);
using (var connection = connectionProvider.CreateConnection(Web))
{
UpdateStoredProcedure(connection, "EPG_SP_ReadCBAttribs", EPG_SP_ReadCBAttribs_UpdateQuery);
LogMessage("EPG_SP_ReadCBAttribs updated.", MessageKind.SUCCESS, 1);
}
}
/// <summary>
/// Upgrades the pfe database:
/// * Create column for projects table - column will be used as source for calculations in PFE instead of sharepoint list
/// * Create discount columns in detail values table - will help us on server side recalculations when discount rate is changed, so we will not have to find original rate
/// * Update stored procedure used to get cost detail values from VB6 code
/// * Populate configuration details table with missing data (partially data already populated by 6.4.0.1 step)
/// </summary>
private void UpgradePfeDatabase()
{
var connectionProvider = new PfeData.ConnectionProvider();
LogMessage("Connecting to the database.", 2);
using (var connection = connectionProvider.CreateConnection(Web))
{
AddTableColumn(connection, ProjectsTableName, ProjectDiscountRateColumnName, ProjectAddDiscountRateColumnQuery);
AddTableColumn(connection, DetailValuesTableName, DetailValuesDiscountRateColumnName, DetailValuesAddDiscountRateColumnQuery);
AddTableColumn(connection, DetailValuesTableName, DetailValuesDiscountValueColumnName, DetailValuesAddDiscountValueColumnQuery);
UpdateStoredProcedure(connection, DetailValuesStoredProcedureName, DetailValuesStoredProcedureUpdateQuery);
AddMissingConfigurationRecords(connection);
LogMessage("PFE Database is ready to use with discount rate per project feature.", MessageKind.SUCCESS, 1);
}
}
private void UpgradePfeDatabase()
{
var connectionProvider = new PfeData.ConnectionProvider();
LogMessage("Connecting to the database.", 2);
using (var connection = connectionProvider.CreateConnection(Web))
{
var columnExists = connection.ColumnExist(ProjectsTableName, ProjectArchivedColumnName);
if (!columnExists)
{
connection.ExecuteNonQuery(ProjectAddArchivedColumnQuery);
LogMessage($"{ProjectArchivedColumnName} column added to {ProjectsTableName} table.", MessageKind.SUCCESS, 2);
}
else
{
LogMessage($"{ProjectArchivedColumnName} column already exists in {ProjectsTableName}.", MessageKind.SKIPPED, 2);
}
var procedures = new Dictionary <string, string>()
{
{ ReadActualWorkProcedureName, ReadActualWorkProcedureUpdateQuery },
{ ReadScheduledWorkProcedureName, ReadScheduledWorkProcedureUpdateQuery }
};
foreach (var procedure in procedures)
{
var definition = connection.GetSpDefinition(procedure.Key);
if (definition != null && !definition.Contains(VersionMarker))
{
connection.ExecuteNonQuery(procedure.Value);
LogMessage($"{procedure.Key} procedure updated.", MessageKind.SUCCESS, 2);
}
else
{
LogMessage($"{procedure.Key} already have been updated.", MessageKind.SKIPPED, 2);
}
}
LogMessage("PFE Database is ready to use with archive restore project feature.", MessageKind.SUCCESS, 1);
}
}
private void UpgradePfeDatabase()
{
var connectionProvider = new PfeData.ConnectionProvider();
LogMessage("Connecting to the database.", 2);
using (var connection = connectionProvider.CreateConnection(Web))
{
string sqlPFEIndicesUpgrade = @"IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='I_EPGP_DETAIL_VALUES_CB_ID_CT_ID_PROJECT_ID' AND object_id = OBJECT_ID('dbo.EPGP_DETAIL_VALUES'))
BEGIN
CREATE NONCLUSTERED INDEX [I_EPGP_DETAIL_VALUES_CB_ID_CT_ID_PROJECT_ID]
ON [dbo].[EPGP_DETAIL_VALUES] ([CB_ID],[CT_ID],[PROJECT_ID])
END
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='I_EPG_WE_CHARGES_PROJECT_ID' AND object_id = OBJECT_ID('dbo.EPG_WE_CHARGES'))
BEGIN
CREATE NONCLUSTERED INDEX [I_EPG_WE_CHARGES_PROJECT_ID]
ON [dbo].[EPG_WE_CHARGES] ([PROJECT_ID])
END
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='I_EPG_WE_CHARGES_WRES_ID' AND object_id = OBJECT_ID('dbo.EPG_WE_CHARGES'))
BEGIN
CREATE NONCLUSTERED INDEX [I_EPG_WE_CHARGES_WRES_ID]
ON [dbo].[EPG_WE_CHARGES] ([WRES_ID])
END
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='I_EPGP_COST_VALUES_CB_ID_CT_ID_PROJECT_ID' AND object_id = OBJECT_ID('dbo.EPGP_COST_VALUES'))
BEGIN
CREATE NONCLUSTERED INDEX [I_EPGP_COST_VALUES_CB_ID_CT_ID_PROJECT_ID]
ON [dbo].[EPGP_COST_VALUES] ([CB_ID],[CT_ID],[PROJECT_ID])
END
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='I_COST_BREAKDOWN_ATTRIBS_CB_ID_BA_RATETYPE_UID_BA_CODE_UID' AND object_id = OBJECT_ID('dbo.EPGP_COST_BREAKDOWN_ATTRIBS'))
BEGIN
CREATE NONCLUSTERED INDEX [I_COST_BREAKDOWN_ATTRIBS_CB_ID_BA_RATETYPE_UID_BA_CODE_UID]
ON [dbo].[EPGP_COST_BREAKDOWN_ATTRIBS] ([CB_ID],[BA_RATETYPE_UID],[BA_CODE_UID])
INCLUDE ([BA_BC_UID],[BA_PRD_ID],[BA_FTE_CONV])
END
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='I_COST_BREAKDOWN_ATTRIBS_BA_FTE_CONV_BA_RATE' AND object_id = OBJECT_ID('dbo.EPGP_COST_BREAKDOWN_ATTRIBS'))
BEGIN
CREATE NONCLUSTERED INDEX [I_COST_BREAKDOWN_ATTRIBS_BA_FTE_CONV_BA_RATE]
ON [dbo].[EPGP_COST_BREAKDOWN_ATTRIBS] ([BA_FTE_CONV],[BA_RATE])
END
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='I_EPG_RESOURCEPLANS_HOURS_CMH_PENDING' AND object_id = OBJECT_ID('dbo.EPG_RESOURCEPLANS_HOURS'))
BEGIN
CREATE NONCLUSTERED INDEX [I_EPG_RESOURCEPLANS_HOURS_CMH_PENDING]
ON [dbo].[EPG_RESOURCEPLANS_HOURS] ([CMH_PENDING])
INCLUDE ([CMT_UID],[PRD_ID],[CMH_HOURS],[CMH_REVENUES])
END
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='I_EPGP_COST_DETAILS_CB_ID_CT_ID_PROJECT_ID' AND object_id = OBJECT_ID('dbo.EPGP_COST_DETAILS'))
BEGIN
CREATE NONCLUSTERED INDEX [I_EPGP_COST_DETAILS_CB_ID_CT_ID_PROJECT_ID]
ON [dbo].[EPGP_COST_DETAILS] ([CB_ID], [CT_ID], [PROJECT_ID])
END
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='I_EPGP_AVAIL_CATEGORIES_CT_ID' AND object_id = OBJECT_ID('dbo.EPGP_AVAIL_CATEGORIES'))
BEGIN
CREATE NONCLUSTERED INDEX [I_EPGP_AVAIL_CATEGORIES_CT_ID]
ON [dbo].[EPGP_AVAIL_CATEGORIES] ([CT_ID])
END
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='I_EPGP_LOOKUP_VALUES_LOOKUP_UID' AND object_id = OBJECT_ID('dbo.EPGP_LOOKUP_VALUES'))
BEGIN
CREATE NONCLUSTERED INDEX [I_EPGP_LOOKUP_VALUES_LOOKUP_UID]
ON [dbo].[EPGP_LOOKUP_VALUES] ([LOOKUP_UID])
END
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='I_EPG_PERIODS_CB_ID_PRD_ID' AND object_id = OBJECT_ID('dbo.EPG_PERIODS'))
BEGIN
CREATE NONCLUSTERED INDEX [I_EPG_PERIODS_CB_ID_PRD_ID]
ON [dbo].[EPG_PERIODS] ([CB_ID], [PRD_ID])
END
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='I_EPGP_PI_WORK2_PROJECT_ID_WRES_ID' AND object_id = OBJECT_ID('dbo.EPGP_PI_WORK2'))
BEGIN
CREATE NONCLUSTERED INDEX [I_EPGP_PI_WORK2_PROJECT_ID_WRES_ID]
ON [dbo].[EPGP_PI_WORK2] ([PROJECT_ID],[WRES_ID])
INCLUDE ([PW_MAJORCATEGORY],[PW_DATE],[PW_WORK])
END
";
connection.ExecuteNonQuery(sqlPFEIndicesUpgrade);
LogMessage("PPM indices were updated successfully.", MessageKind.SUCCESS, 2);
}
}
