244 lines
9.1 KiB
Transact-SQL
244 lines
9.1 KiB
Transact-SQL
-- =============================================
|
|
-- Reset Company Data for Testing
|
|
-- =============================================
|
|
-- This script completely removes all transactional data for a company
|
|
-- while preserving lookup tables (statuses, priorities, categories, etc.)
|
|
--
|
|
-- Usage:
|
|
-- 1. Set the @CompanyId or @CompanyCode variable below
|
|
-- 2. Review the data to be deleted (uncomment SELECT statements)
|
|
-- 3. Execute the script
|
|
-- =============================================
|
|
|
|
-- CONFIGURATION: Set ONE of these (comment out the other)
|
|
DECLARE @CompanyCode NVARCHAR(50) = 'DEMO'; -- Use company code
|
|
-- DECLARE @CompanyId INT = 1; -- OR use company ID directly
|
|
|
|
-- Get CompanyId from CompanyCode if needed
|
|
IF @CompanyCode IS NOT NULL AND EXISTS(SELECT 1 FROM Companies WHERE CompanyCode = @CompanyCode)
|
|
BEGIN
|
|
SELECT @CompanyId = Id FROM Companies WHERE CompanyCode = @CompanyCode;
|
|
END
|
|
|
|
-- Validate company exists
|
|
IF @CompanyId IS NULL OR NOT EXISTS(SELECT 1 FROM Companies WHERE Id = @CompanyId)
|
|
BEGIN
|
|
RAISERROR('Company not found! Please check @CompanyId or @CompanyCode', 16, 1);
|
|
RETURN;
|
|
END
|
|
|
|
-- Display company information
|
|
DECLARE @CompanyName NVARCHAR(200);
|
|
SELECT @CompanyName = CompanyName, @CompanyCode = CompanyCode FROM Companies WHERE Id = @CompanyId;
|
|
|
|
PRINT '=============================================';
|
|
PRINT 'RESETTING DATA FOR COMPANY:';
|
|
PRINT ' Company ID: ' + CAST(@CompanyId AS NVARCHAR(10));
|
|
PRINT ' Company Code: ' + @CompanyCode;
|
|
PRINT ' Company Name: ' + @CompanyName;
|
|
PRINT '=============================================';
|
|
PRINT '';
|
|
|
|
-- Begin transaction for safety
|
|
BEGIN TRANSACTION;
|
|
|
|
BEGIN TRY
|
|
DECLARE @RowCount INT = 0;
|
|
DECLARE @TotalDeleted INT = 0;
|
|
|
|
-- =============================================
|
|
-- DELETE ORDER: Child tables first to avoid FK violations
|
|
-- =============================================
|
|
|
|
-- 1. Job Photos
|
|
PRINT 'Deleting Job Photos...';
|
|
DELETE FROM JobPhotos
|
|
WHERE JobId IN (SELECT Id FROM Jobs WHERE CompanyId = @CompanyId);
|
|
SET @RowCount = @@ROWCOUNT;
|
|
SET @TotalDeleted = @TotalDeleted + @RowCount;
|
|
PRINT ' Deleted ' + CAST(@RowCount AS NVARCHAR(10)) + ' job photo(s)';
|
|
|
|
-- 2. Job Notes
|
|
PRINT 'Deleting Job Notes...';
|
|
DELETE FROM JobNotes
|
|
WHERE JobId IN (SELECT Id FROM Jobs WHERE CompanyId = @CompanyId);
|
|
SET @RowCount = @@ROWCOUNT;
|
|
SET @TotalDeleted = @TotalDeleted + @RowCount;
|
|
PRINT ' Deleted ' + CAST(@RowCount AS NVARCHAR(10)) + ' job note(s)';
|
|
|
|
-- 3. Job Items
|
|
PRINT 'Deleting Job Items...';
|
|
DELETE FROM JobItems
|
|
WHERE JobId IN (SELECT Id FROM Jobs WHERE CompanyId = @CompanyId);
|
|
SET @RowCount = @@ROWCOUNT;
|
|
SET @TotalDeleted = @TotalDeleted + @RowCount;
|
|
PRINT ' Deleted ' + CAST(@RowCount AS NVARCHAR(10)) + ' job item(s)';
|
|
|
|
-- 4. Jobs
|
|
PRINT 'Deleting Jobs...';
|
|
DELETE FROM Jobs WHERE CompanyId = @CompanyId;
|
|
SET @RowCount = @@ROWCOUNT;
|
|
SET @TotalDeleted = @TotalDeleted + @RowCount;
|
|
PRINT ' Deleted ' + CAST(@RowCount AS NVARCHAR(10)) + ' job(s)';
|
|
|
|
-- 5. Quote Items
|
|
PRINT 'Deleting Quote Items...';
|
|
DELETE FROM QuoteItems
|
|
WHERE QuoteId IN (SELECT Id FROM Quotes WHERE CompanyId = @CompanyId);
|
|
SET @RowCount = @@ROWCOUNT;
|
|
SET @TotalDeleted = @TotalDeleted + @RowCount;
|
|
PRINT ' Deleted ' + CAST(@RowCount AS NVARCHAR(10)) + ' quote item(s)';
|
|
|
|
-- 6. Quotes
|
|
PRINT 'Deleting Quotes...';
|
|
DELETE FROM Quotes WHERE CompanyId = @CompanyId;
|
|
SET @RowCount = @@ROWCOUNT;
|
|
SET @TotalDeleted = @TotalDeleted + @RowCount;
|
|
PRINT ' Deleted ' + CAST(@RowCount AS NVARCHAR(10)) + ' quote(s)';
|
|
|
|
-- 7. Appointments (if table exists)
|
|
IF OBJECT_ID('Appointments', 'U') IS NOT NULL
|
|
BEGIN
|
|
PRINT 'Deleting Appointments...';
|
|
DELETE FROM Appointments WHERE CompanyId = @CompanyId;
|
|
SET @RowCount = @@ROWCOUNT;
|
|
SET @TotalDeleted = @TotalDeleted + @RowCount;
|
|
PRINT ' Deleted ' + CAST(@RowCount AS NVARCHAR(10)) + ' appointment(s)';
|
|
END
|
|
|
|
-- 8. Inventory Transactions
|
|
PRINT 'Deleting Inventory Transactions...';
|
|
DELETE FROM InventoryTransactions
|
|
WHERE InventoryItemId IN (SELECT Id FROM InventoryItems WHERE CompanyId = @CompanyId);
|
|
SET @RowCount = @@ROWCOUNT;
|
|
SET @TotalDeleted = @TotalDeleted + @RowCount;
|
|
PRINT ' Deleted ' + CAST(@RowCount AS NVARCHAR(10)) + ' inventory transaction(s)';
|
|
|
|
-- 9. Inventory Items
|
|
PRINT 'Deleting Inventory Items...';
|
|
DELETE FROM InventoryItems WHERE CompanyId = @CompanyId;
|
|
SET @RowCount = @@ROWCOUNT;
|
|
SET @TotalDeleted = @TotalDeleted + @RowCount;
|
|
PRINT ' Deleted ' + CAST(@RowCount AS NVARCHAR(10)) + ' inventory item(s)';
|
|
|
|
-- 10. Maintenance Records
|
|
PRINT 'Deleting Maintenance Records...';
|
|
DELETE FROM MaintenanceRecords
|
|
WHERE EquipmentId IN (SELECT Id FROM Equipment WHERE CompanyId = @CompanyId);
|
|
SET @RowCount = @@ROWCOUNT;
|
|
SET @TotalDeleted = @TotalDeleted + @RowCount;
|
|
PRINT ' Deleted ' + CAST(@RowCount AS NVARCHAR(10)) + ' maintenance record(s)';
|
|
|
|
-- 11. Equipment
|
|
PRINT 'Deleting Equipment...';
|
|
DELETE FROM Equipment WHERE CompanyId = @CompanyId;
|
|
SET @RowCount = @@ROWCOUNT;
|
|
SET @TotalDeleted = @TotalDeleted + @RowCount;
|
|
PRINT ' Deleted ' + CAST(@RowCount AS NVARCHAR(10)) + ' equipment record(s)';
|
|
|
|
-- 12. Catalog Items (if table exists)
|
|
IF OBJECT_ID('CatalogItems', 'U') IS NOT NULL
|
|
BEGIN
|
|
PRINT 'Deleting Catalog Items...';
|
|
DELETE FROM CatalogItems WHERE CompanyId = @CompanyId;
|
|
SET @RowCount = @@ROWCOUNT;
|
|
SET @TotalDeleted = @TotalDeleted + @RowCount;
|
|
PRINT ' Deleted ' + CAST(@RowCount AS NVARCHAR(10)) + ' catalog item(s)';
|
|
END
|
|
|
|
-- 13. Catalog Categories (if table exists)
|
|
IF OBJECT_ID('CatalogCategories', 'U') IS NOT NULL
|
|
BEGIN
|
|
PRINT 'Deleting Catalog Categories...';
|
|
DELETE FROM CatalogCategories WHERE CompanyId = @CompanyId;
|
|
SET @RowCount = @@ROWCOUNT;
|
|
SET @TotalDeleted = @TotalDeleted + @RowCount;
|
|
PRINT ' Deleted ' + CAST(@RowCount AS NVARCHAR(10)) + ' catalog categor(y/ies)';
|
|
END
|
|
|
|
-- 14. Suppliers (if table exists)
|
|
IF OBJECT_ID('Suppliers', 'U') IS NOT NULL
|
|
BEGIN
|
|
PRINT 'Deleting Suppliers...';
|
|
DELETE FROM Suppliers WHERE CompanyId = @CompanyId;
|
|
SET @RowCount = @@ROWCOUNT;
|
|
SET @TotalDeleted = @TotalDeleted + @RowCount;
|
|
PRINT ' Deleted ' + CAST(@RowCount AS NVARCHAR(10)) + ' supplier(s)';
|
|
END
|
|
|
|
-- 15. Customers
|
|
PRINT 'Deleting Customers...';
|
|
DELETE FROM Customers WHERE CompanyId = @CompanyId;
|
|
SET @RowCount = @@ROWCOUNT;
|
|
SET @TotalDeleted = @TotalDeleted + @RowCount;
|
|
PRINT ' Deleted ' + CAST(@RowCount AS NVARCHAR(10)) + ' customer(s)';
|
|
|
|
-- 16. Pricing Tiers
|
|
PRINT 'Deleting Pricing Tiers...';
|
|
DELETE FROM PricingTiers WHERE CompanyId = @CompanyId;
|
|
SET @RowCount = @@ROWCOUNT;
|
|
SET @TotalDeleted = @TotalDeleted + @RowCount;
|
|
PRINT ' Deleted ' + CAST(@RowCount AS NVARCHAR(10)) + ' pricing tier(s)';
|
|
|
|
-- 17. Company Operating Costs
|
|
PRINT 'Deleting Company Operating Costs...';
|
|
DELETE FROM CompanyOperatingCosts WHERE CompanyId = @CompanyId;
|
|
SET @RowCount = @@ROWCOUNT;
|
|
SET @TotalDeleted = @TotalDeleted + @RowCount;
|
|
PRINT ' Deleted ' + CAST(@RowCount AS NVARCHAR(10)) + ' operating cost record(s)';
|
|
|
|
-- 18. Company Preferences (if table exists)
|
|
IF OBJECT_ID('CompanyPreferences', 'U') IS NOT NULL
|
|
BEGIN
|
|
PRINT 'Deleting Company Preferences...';
|
|
DELETE FROM CompanyPreferences WHERE CompanyId = @CompanyId;
|
|
SET @RowCount = @@ROWCOUNT;
|
|
SET @TotalDeleted = @TotalDeleted + @RowCount;
|
|
PRINT ' Deleted ' + CAST(@RowCount AS NVARCHAR(10)) + ' preference record(s)';
|
|
END
|
|
|
|
-- =============================================
|
|
-- PRESERVED DATA (NOT DELETED)
|
|
-- =============================================
|
|
PRINT '';
|
|
PRINT 'PRESERVED DATA (not deleted):';
|
|
PRINT ' - Company record';
|
|
PRINT ' - Users associated with company';
|
|
PRINT ' - Job Status Lookups';
|
|
PRINT ' - Job Priority Lookups';
|
|
PRINT ' - Quote Status Lookups';
|
|
PRINT ' - Inventory Category Lookups';
|
|
PRINT ' - Appointment Status Lookups (if exists)';
|
|
PRINT ' - Appointment Type Lookups (if exists)';
|
|
PRINT '';
|
|
|
|
-- Summary
|
|
PRINT '=============================================';
|
|
PRINT 'RESET SUMMARY:';
|
|
PRINT ' Total records deleted: ' + CAST(@TotalDeleted AS NVARCHAR(10));
|
|
PRINT ' Company preserved: ' + @CompanyName + ' (' + @CompanyCode + ')';
|
|
PRINT '=============================================';
|
|
PRINT '';
|
|
|
|
-- Commit the transaction
|
|
PRINT 'Committing transaction...';
|
|
COMMIT TRANSACTION;
|
|
PRINT 'SUCCESS! Company data has been reset.';
|
|
PRINT '';
|
|
PRINT 'You can now re-seed the company data via:';
|
|
PRINT ' Platform Management > Seed Data > Seed Data for ' + @CompanyName;
|
|
|
|
END TRY
|
|
BEGIN CATCH
|
|
-- Rollback on error
|
|
PRINT '';
|
|
PRINT 'ERROR! Rolling back transaction...';
|
|
ROLLBACK TRANSACTION;
|
|
|
|
PRINT 'Error Message: ' + ERROR_MESSAGE();
|
|
PRINT 'Error Line: ' + CAST(ERROR_LINE() AS NVARCHAR(10));
|
|
|
|
-- Re-throw the error
|
|
THROW;
|
|
END CATCH;
|