Step 1

Download the database from customers SharePoint Database folder:           https://corpioffice.sharepoint.com/sites/ProfessionalServicesAssetCenter/Shared%20Documents/Forms/AllItems.aspx?viewid=3ac59aa3%2Dc5d2%2D43df%2Daac2%2D20b763bcb34a&id=%2Fsites%2FProfessionalServicesAssetCenter%2FShared%20Documents%2FManagerPlus%2FDatabases

Step 2

Get customers company code from the admin tool and name the file using the following format:

Companycode_year, month, day.bak (ie: airstream_20210825.bak) and move it to the Backup folder

Step 3

Connect to SSMS to restore the database by right clicking the Database section in the left column and selecting Restore Database and rename it using the Company Code

Step 4

Verify the what version of ManagerPlus the database is currently on by running one of the following scripts:

SELECT * FROM Config OR SELECT * FROM Setup. If the Config command works then move forward using the Desktop Upgrade instructions. If the Setup command works follow the instructions for a Pro Upgrade

Step 1

Make sure the database  is running the 7.37 version of ManagerPlus. If it is not you will need to run each of the following scripts in order to get the database on the correct version (example: if database is on 7.24 you will run scripts 7.25 all the way up to 7.37)

Step 2

Run the following Pro Fix Upgrade where the counts are greater than 0

/* Check and fix Schedule Linking */

SELECT COUNT(*) AS badScheduleCount FROM schedules WHERE supercedes is not null and (RESET is not null or reset2 is not null)

UPDATE schedules set supercedes = NULL, RESET = NULL, Reset2 = NULL WHERE supercedes is not null and (RESET is not null or reset2 is not null)

/* Check and fix bad Vendors */

SELECT COUNT(busType) AS badVendorCount FROM vendor WHERE busType = ''

UPDATE vendor SET [busType]= NULL WHERE [busType] = ''

/* Check and fix bad Log Details */

SELECT COUNT(*) AS badLogCount FROM logdetail WHERE (hours < 0 or hours > 1000000) or (distance < 0 or distance > 10000000)

UPDATE logdetail SET [hours] = 0 WHERE hours < 0 OR hours > 1000000

UPDATE logdetail SET [distance] = 0 WHERE distance < 0 OR distance > 10000000

Step 3

Back up the database and then restore a second copy naming it with the company code_pro (ie: airstream_pro)

Step 4

Run the DBTool

• Check the box confirming you have backed up the database. Hit next

• Select the correct SQL Server

• Select the database and hit 'Upgrade'

You may have to get the correct database by selecting 'Change Database'. A separate box will appear with the databases to select from. Select the correct one and then continue with the upgrade.

Step 5

If there are errors when updating use the knowledge base to find the solution and complete that before trying to run the DBTool again

Link to current knowledge base here

Step 6

Once the fix has been applied restart the DBTool and let it complete the upgrade

Step 7

After it has upgraded to 2016.04 run the following script to update the budgets, log types, and part descriptions

Remember to put the correct company code for the database

/* Check and fix budgets with NULL description after DB upgraded to Desktop*/

SELECT COUNT(*) AS BadBudgetCount FROM Budgets b WHERE b.Description IS NULL

UPDATE Budgets SET [DESCRIPTION] = 'Pro Budget' WHERE [DESCRIPTION] IS NULL

 

/* Check and fix log types*/

SELECT * FROM dbo.LogTypes        

 

--Metered logs should allow resets, if not metered, it should not allow resets

UPDATE LogTypes SET Metered = 1 WHERE Allow_Reset = 1 AND Log_Type_ID <> 'Qty'

UPDATE LogTypes SET Allow_Reset = 0 WHERE Metered = 0 AND Log_Type_ID = 'Qty'

 

/*Update part descriptions*/

 

-- copy and paste company code for two different DBs.  Desktop version and pro.

--This code will update part descriptions on pro non-stock parts.

SELECT * FROM companycode.dbo.parts ORDER BY companycode.dbo.parts.Part_ID

UPDATE companycode.dbo.Parts SET Description = WOP.Description FROM

(SELECT ROW_NUMBER() OVER(Partition BY [Part ID] ORDER BY [Work Order Part ID]) AS RowNumber

   , [Part ID]

   , Description

FROM companycode_pro.dbo.WorkOrderParts) WOP

WHERE WOP.RowNumber = 1

AND companycode.dbo.Parts.Part_ID = WOP.[Part ID] AND companycode.dbo.Parts.Description = 'Non-Stock Part' and WOP.Description is not NULL

 

SELECT * FROM

(SELECT ROW_NUMBER() OVER(Partition BY [Part ID] ORDER BY [Work Order Part ID]) AS RowNumber

   , [Part ID]

   , Description

FROM companycode_pro.dbo.WorkOrderParts) WOP2

WHERE WOP2.RowNumber = 1 and WOP2.Description is not NULL

ORDER BY WOP2.[Part ID]

SELECT * FROM companycode.dbo.parts ORDER BY companycode.dbo.parts.Part_ID

Step 8

Next run the Security_UserCreation script replacing the default IM with the correct one and open the 2016.04 version of ManagerPlus

--SELECT * FROM SecurityRoles sr

--SELECT * FROM Entity e

--SELECT * FROM ContactStatus cs

--SELECT * FROM SecurityUsers su

--SELECT * FROM Contacts c

 

/*User ID's*/

/*

lspoklie

mgraham

ddemarchi

eandrews

lkisan

twoo

tleichtle

bpeterson

mthacker

jskinner

*/

 

DECLARE @ContactKEY INT

DECLARE @UserKEY INT

DECLARE @UserID NVARCHAR(24) = 'IM UserID' --Implementation Manager ID

DECLARE @StatusKEY INT

DECLARE @StatusID NVARCHAR(24) = 'USER'

DECLARE @RoleKEY INT

DECLARE @RoleID NVARCHAR(24) = 'Administrator'

 

SET @StatusKEY = (SELECT Status_KEY FROM ContactStatus WHERE Status_ID = @StatusID)

 

SELECT @RoleKEY = sr.Role_KEY FROM SecurityRoles sr WHERE EXISTS(SELECT * FROM SecurityRoleOptions sro WHERE sro.Option_KEY = 1 AND sro.Permission = 1 AND sr.Role_KEY = sro.Role_KEY)

AND @RoleID = sr.Role_Name

IF @RoleKEY IS NULL

BEGIN

SET @RoleKEY = (SELECT TOP (1)sr.Role_KEY FROM SecurityRoles sr WHERE EXISTS(SELECT * FROM SecurityRoleOptions sro WHERE sro.Option_KEY = 1 AND sro.Permission = 1 AND sr.Role_KEY = sro.Role_KEY))

END

 

SELECT @ContactKEY = c.Contact_KEY FROM Contacts c WHERE c.Contact_ID = @UserID

IF @ContactKEY IS NULL

BEGIN

INSERT INTO Contacts (Contact_ID, Internal_Name, Contact_Name, Contact_Type, Status_KEY, Email_Address, Used_in_Work_Orders, Labor_Rate, Tag)

VALUES (@UserID, @UserID, @UserID, 400, @StatusKEY, CONCAT(@UserID, '@managerplus.com'), 0, 0, NULL)

SET @ContactKEY = (SELECT Contact_KEY FROM Contacts WHERE Contact_ID = @UserID)

END

 

INSERT INTO SecurityUsers (User_Name, Password, Contact_KEY, User_Class, Status_KEY, PWSalt, Reset_Password_On_Login, ResetKey)

SELECT @UserID, HASHBYTES('MD5', 'admin1234'), @ContactKEY, 1, @StatusKEY, NULL, 0, NULL FROM Contacts

WHERE Contact_KEY = @ContactKEY AND NOT EXISTS (SELECT * FROM SecurityUsers su WHERE su.Contact_KEY = @ContactKEY)

SET @UserKEY = (SELECT su.User_KEY FROM SecurityUsers su WHERE su.User_Name = @UserID)

 

INSERT INTO ContactEntity (Contact_KEY, Entity_KEY)

SELECT @ContactKEY, e.Entity_KEY FROM Entity e WHERE NOT EXISTS (SELECT * FROM ContactEntity ce WHERE ce.Contact_KEY = @ContactKEY AND ce.Entity_KEY = e.Entity_KEY)

 

INSERT INTO SecurityUsersEntityRoles (User_KEY, Entity_KEY, Role_KEY)

SELECT @UserKEY, e.Entity_KEY, @RoleKEY FROM Entity e WHERE NOT EXISTS (SELECT * FROM SecurityUsersEntityRoles suer WHERE suer.User_KEY = @UserKEY AND suer.Role_KEY = @RoleKEY AND suer.Entity_KEY = e.Entity_KEY)

 

SELECT * FROM Contacts c WHERE c.Contact_ID = @UserID

SELECT * FROM SecurityUsers su WHERE su.User_Name = @UserID

SELECT * FROM ContactEntity ce WHERE ce.Contact_KEY = @ContactKEY

SELECT * FROM SecurityUsersEntityRoles suer WHERE suer.User_KEY = @UserKEY

Step 9

In ManagerPlus under the 'Module' tab go to 'Budgets' and click on the GREEN PLUS sign to add a new budget

• Name the new budget INVENTORY MANAGEMENT and select 'OPERATIONS' from the drop down. Hit 'Save'
• Open 'Cycle Count' and click on the GREEN PLUS sign to add a new cycle count under the budget you just created. Take the count to '0', save and then close Cycle Count

Step 10

Run the cycle count if there are any parts in the table 'Inventory Received'

SELECT * FROM InventoryReceived IR

SELECT * FROM InventoryCycleCountParts ICCP

SELECT * FROM InventoryCycleCountPartsReceived ICCPR

-- Create a cycle count and close it. Do Not post.

-- Modify cycle count number below if necessary.

INSERT INTO InventoryCycleCountParts

SELECT 1 [Inventory_Cycle_Count_ID],

IR.Part_KEY, 1 [Times_Counted],

IR.Quantity AS [Current_Count],

NULL AS [Counted_By], GETDATE() AS [Date_Posted],

NULL AS [Last_Count], 2 [Last_Count_Code], IR.Cost AS [Part_Cost], 1 [Posted]

FROM InventoryReceived IR

 

INSERT INTO InventoryCycleCountPartsReceived

SELECT ICCP.Cycle_Count_Part_KEY, IR.Received_KEY FROM InventoryCycleCountParts ICCP

INNER JOIN InventoryReceived IR ON ICCP.Part_KEY = IR.Part_KEY

Step 11

Clean up Work Types and Part Types by running the following script:

Review Work Types and substitute other options where 'OIL CHANGE' or 'REPAIR' do not already exist

--Ensure REPAIR and OIL CHANGE exist. Otherwise replace with another option.

SELECT * FROM WorkType ORDER BY Work_Type_ID

 

--Move old work type to the purpose field.

UPDATE WorkOrders SET Purpose = '' WHERE Purpose IS NULL

UPDATE wo SET Purpose = wt.Work_Type_ID + '-' + wo.Purpose FROM WorkOrders wo INNER JOIN WorkType wt ON wo.Work_Type_KEY = wt.Work_Type_KEY

 

UPDATE WorkType SET Work_Type_ID = 'CORRECTIVE' WHERE Work_Type_ID = 'REPAIR'

UPDATE WorkType SET Work_Type_ID = 'PREVENTIVE' WHERE Work_Type_ID = 'OIL CHANGE'

 

--Update work types on all existing Work Orders and Schedules.

UPDATE Schedules SET Work_Type_KEY = (SELECT wt.Work_Type_KEY FROM WorkType wt WHERE wt.Work_Type_ID = 'PREVENTIVE')

UPDATE wo SET Work_Type_KEY = (SELECT wt.Work_Type_KEY FROM WorkType wt WHERE wt.Work_Type_ID = 'PREVENTIVE') FROM WorkOrders wo WHERE wo.Schedule_KEY IS NOT NULL

UPDATE wo SET Work_Type_KEY = (SELECT wt.Work_Type_KEY FROM WorkType wt WHERE wt.Work_Type_ID = 'CORRECTIVE') FROM WorkOrders wo WHERE wo.Schedule_KEY IS NULL

 

--Remove bad work types.

DELETE wtcf FROM WorkTypeCustomFields wtcf INNER JOIN WorkType wt ON wtcf.Work_Type_KEY = wt.Work_Type_KEY LEFT JOIN WorkOrders wo ON wt.Work_Type_KEY = wo.Work_Type_KEY WHERE wo.Work_Order_KEY IS NULL

DELETE wt FROM WorkType wt LEFT JOIN WorkOrders wo ON wt.Work_Type_KEY = wo.Work_Type_KEY WHERE wo.Work_Order_KEY IS NULL

Step 12

Clean up 'UNKNOWN' part type. Where parts were added ad-hoc in Pro, set them inactive and rename 'UNKNOWN' to 'NON_STOCK'. Then delete type 'UNKOWN'

UPDATE parts SET Status_KEY = (SELECT Status_KEY FROM PartStatus WHERE Status_ID = 'INACTIVE') WHERE Type_KEY = (SELECT Type_KEY FROM PartType WHERE Type_ID = 'UNKNOWN')

UPDATE iwp SET Track_Levels = 0, Listed = 0 FROM InventoryWarehouseParts iwp INNER JOIN Parts p ON iwp.Part_KEY = p.Part_KEY WHERE Type_KEY = (SELECT Type_KEY FROM PartType WHERE Type_ID = 'UNKNOWN')

 

INSERT INTO PartType (Type_ID) VALUES (N'NON-STOCK')

UPDATE Parts SET Type_KEY = (SELECT Type_KEY FROM PartType WHERE Type_ID = 'NON-STOCK') WHERE Type_KEY = (SELECT Type_KEY FROM PartType WHERE Type_ID = 'UNKNOWN')

 

DELETE FROM PartType WHERE Type_ID = 'UNKNOWN'

DELETE FROM PartStatus WHERE Status_ID = 'UNKNOWN'

Step 13

Continue with the Universal Upgrade script. Make sure you add the correct Company Code

UPDATE Config SET Base_Web_URL = NULL

 

UPDATE Company SET UTCDATE = 1

 

UPDATE configsecurity SET USE_LDAP_EasyLinks = 0

 

UPDATE configsecurity

 

SET USE_LDAP_ManagerPlus = 0 SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE TABLE [dbo].[FeatureToggles]( [Feature_Name] [nvarchar](100) NOT NULL, [Enabled] [bit] NOT NULL, [Reversible] [bit] NOT NULL, CONSTRAINT [PK_FeatureToggle] PRIMARY KEY CLUSTERED ( [Feature_Name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

GO

ALTER TABLE [dbo].[FeatureToggles]

ADD CONSTRAINT [DF_FeatureToggles_Reversible]

DEFAULT ((1)) FOR [Reversible]

GO

 

INSERT INTO FeatureToggles SELECT 'ManagerPlus2',0,0

INSERT INTO FeatureToggles SELECT 'API',0,1

INSERT INTO FeatureToggles SELECT 'Connectors',0,1 UPDATE Schedules

 

SET Purpose = Schedule_ID

WHERE Purpose IS NULL OR Purpose = ''

 

--update company code for these two lines

ALTER DATABASE CompanyCode SET AUTO_CLOSE OFF WITH NO_WAIT

ALTER DATABASE CompanyCode SET RECOVERY SIMPLE

 

Step 14

Verify that both Company Name and Entity are NOT blank

SELECT * FROM Company

SELECT * FROM Entity

*if one of those fields are blank run this fix script

UPDATE c SET Company_Name = e.Entity_Name

FROM Company c INNER JOIN Entity e ON c.Company_KEY = e.Company_KEY

Step 15

Verify what inventory method the customer is on. The customer should be on inventory method 3. If not send a message to the IM and let them know.

1-Last Cost 2- Rolling Cost 3- FIFO (First In First Out) 4- LIFO (Last In First Out)

SELECT * FROM ConfigInventory ci

Continue with the rest of the script

SELECT * FROM WorkPriority wp
    
UPDATE SU SET SU.User_Class = 1 FROM SecurityUsers SU WHERE SU.User_Class = 2

UPDATE configworkorders SET default_note_type = NULL

UPDATE ca SET Address_Type_KEY = 3--, [Default] = 1
FROM ContactAddresses ca
    LEFT JOIN Contacts    c ON ca.Contact_KEY = c.Contact_KEY
WHERE Contact_Type = 200
**RUN THIS PART FIRST AND THEN RUN THE ABOVE SCRIPT. IF THE NUMBERS THAT COME BACK FROM QUERY ARE THE SAME, REMOVE THE 2 DASHES BEHIND THE #3 IN THE TOP SCRIPT AND RE-RUN IT.**                            
SELECT COUNT(DISTINCT(c.Contact_KEY))  FROM ContactAddresses ca
    LEFT JOIN Contacts    c ON ca.Contact_KEY = c.Contact_KEY
WHERE Contact_Type = 200