35 lines
1.4 KiB
SQL
35 lines
1.4 KiB
SQL
-- Create a temporary table with the new structure
|
|
CREATE TABLE `projectFinanceMonthly_new` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`projectId` INT NOT NULL,
|
|
`month` DATE NOT NULL,
|
|
`wip` DECIMAL(15, 2) DEFAULT 0.00,
|
|
`opening_balance` DECIMAL(15, 2) DEFAULT 0.00,
|
|
`billing` DECIMAL(15, 2) DEFAULT 0.00,
|
|
`expenses` DECIMAL(15, 2) DEFAULT 0.00,
|
|
`is_overridden` BOOLEAN NOT NULL DEFAULT FALSE,
|
|
`createdAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
`updatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`projectId`) REFERENCES `projects`(`id`) ON DELETE CASCADE,
|
|
UNIQUE KEY `project_month` (`projectId`, `month`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- Migrate the data
|
|
INSERT INTO `projectFinanceMonthly_new` (projectId, month, wip, opening_balance, billing, expenses, is_overridden)
|
|
SELECT
|
|
projectId,
|
|
month,
|
|
MAX(CASE WHEN metricName = 'wip' THEN value ELSE 0 END) as wip,
|
|
MAX(CASE WHEN metricName = 'opening_balance' THEN value ELSE 0 END) as opening_balance,
|
|
MAX(CASE WHEN metricName = 'billing' THEN value ELSE 0 END) as billing,
|
|
MAX(CASE WHEN metricName = 'expenses' THEN value ELSE 0 END) as expenses,
|
|
MAX(is_overridden) as is_overridden
|
|
FROM projectFinanceMonthly
|
|
GROUP BY projectId, month;
|
|
|
|
-- Drop the old table
|
|
DROP TABLE projectFinanceMonthly;
|
|
|
|
-- Rename the new table
|
|
RENAME TABLE projectFinanceMonthly_new TO projectFinanceMonthly;
|