1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
-- Add payment categories with accents from payment with accents
INSERT INTO
payment_category (name, category, created_at)
SELECT
DISTINCT lower(payment.name), payment_category.category, datetime('now')
FROM
payment
INNER JOIN
payment_category
ON
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(lower(payment.name), 'é', 'e'), 'è', 'e'), 'à', 'a'), 'û', 'u'), 'â', 'a'), 'ê', 'e'), 'â', 'a'), 'î', 'i'), 'ï', 'i'), 'ô', 'o'), 'ë', 'e') = payment_category.name
WHERE
payment.name
IN
(SELECT DISTINCT payment.name FROM payment WHERE lower(payment.name) NOT IN (SELECT payment_category.name FROM payment_category) AND payment.deleted_at IS NULL);
-- Remove unused payment categories
DELETE FROM
payment_category
WHERE
name NOT IN (SELECT DISTINCT lower(name) FROM payment);
|
