Assety

{
    "nameLabel": "Jméno psa", (1)
    "numberLabel": "Číslo zápisu", (2)
    "fontIcon": "fa fa-dog", (3)
    "name": "Pes", (4)
    "singular": "Pes", (4)
    "plural": "Psi", <4>,
    "attributes": [
        {
            "name": "Klubové číslo", (5)
            "type": "TEXT", (6)
            "title": "Klubové číslo psa", (8)
            "placeholder": "POZOR vyplňujte bez mezer!" (9)
        },
        {
            "name": "Datum narození", (5)
            "type": "DATE"  (6)
        },
        {
            "name": "Uchovněný", (5)
            "type": "SELECT",  (6)
            "options": [  (7)
                "-",
                "Ano",
                "Ne"
            ],
            "hiddenOptions":["-"], (15)
            "coloredOptions":[{
                "hex":"#FF0000",
                "options":["Ano"]
            }] (16)
        },
        {
            "name": "Matka", (5)
            "type": "RELATIONSHIP" (6)
        },
        {
            "name": "Otec", (5)
            "type": "RELATIONSHIP" (6)
        },
        {
            "name": "Zdraví",
            "type": "STRUCTURED", (6)
            "subAttributes": [ (12)
                {
                  "name": "Tlak",
                  "type": "TEXT"
                },
                {
                  "name": "Tep",
                  "type": "NUMBER"
                },
                {
                  "name": "Datum posledního očkování",
                  "type": "DATE"
                },
                {
                  "name": "Očkováný na",
                  "type": "SELECT_MULTI",
                  "options": ["Škytavka", "Pěnivka"]
                },
                {
                  "name": "Obecně zdravý",
                  "type": "BOOLEAN"
                },
                {
                  "name": "Společně očkován s",
                  "type": "RELATIONSHIP"
                }
          ]
            "hiddenAttributes":[]
        },
        {
            "name": "Historie očkování",
            "type": "REPEATED", (6)
            "repeatedAttribute": { (13)
                "type": "STRUCTURED",
                "subAttributes": [
                    {
                        "name": "Datum",
                        "type": "DATE"
                    },
                    {
                        "name": "Vakcína",
                        "type": "SELECT_MULTI",
                        "options": ["Covid","Vzteklina"]
                    }
                ]
            }
        },
        {
            "name": "Průkazy v PDF",
            "type":"FILE_ENTRIES" (6)
        },
        {
            "name": "Chovatelská stanice",
            "type":"MEMBER_REFERENCE" <6>,
            "memberSectionType": "PERSON"
        },
        {
              "name": "Fotografie",
              "type": "FILE_ENTRIES", (6)
              "position": "PRIMARY_PICTURE" (17)
        },
        {
              "name": "Počet zubů",
              "type": "INTEGER" (6)
        },
        {
              "name": "Výška",
              "type": "NUMBER", (6)
              "coloredOptions": [
                {
                "hex": "#ff0000",
                "options": [
                    "25.0:999.0"
                ]
                }
              ] (16)
        },
    ],
    "attributesConditionsInCards": [ (18)
        {
            "conditions": {
                "Jméno": "abc"
            },
            "alwaysAdded": true,
            "attributes": []
        },
    ],
    "attributesVisibleInCards" : [
        "visibleByActive": [],
        "visibleByNotActive": [],
        "visibleByRelatedMember": [],
        "visibleByQuest": []
    ], (14)
    "groups": [],  (10)
    "forms": { (11)
        "MS_ASSET_SYNC":[],
        "MS_MEMBER_DETAIL":[],
        "MS_ASSET_LIST_ADD":[],
        "CS_MEMBER_CARD_ADD":[],
        "MS_ASSET_DETAIL_EDIT"[
              {
                "name": "Vykoš",
                "fields": [
                  "Datum narození"
                ],
                "requiredFields": [],
                "conditions": {
                    "Datum narození":"0:1721109029000" (19)
                }
              }
            ],
        "CS_MEMBER_CARD_EDIT":[],
        "MS_ASSET_LIST_BULK_EDIT":[]
    }
}

{
    "nameLabel": "Test",
    "numberLabel": "Test test",
    "fontIcon": "fa fa-help",
    "name": "Test",
    "singular": "Test",
    "plural": "Test",
    "attributes": [],
    "attributesVisibleInCards" : [],
    "groups": [],
    "forms": {}
}

"groups": [
    {
        "name": "Základní údaje",  (1)
        "attributeNames": [  (2)
            "Jméno psa",
            "Číslo zápisu",
            "Klubové číslo",
            "Datum narození",
            "Uchovněný"
        ],
        "collapsed": false (3)
    },
    {
        "name": "Rodiče",  (1)
        "attributeNames": [  (2)
            "Matka",
            "Otec"
        ],
        "collapsed": true (3)
    }
  ]

"forms": {
    "MS_MEMBER_DETAIL": [  (1)
        {
            "name": "Výchozí", (2)
            "fields": [  (3)
                "Jméno psa",
                "Číslo zápisu",
                "Klubové číslo",
                "Datum narození",
                "Uchovněný",
                "Matka",
                "Otec"
            ],
            "requiredFields": [  (4)
                "Jméno psa",
                "Číslo zápisu
            ]
        }
    ]
}

{"Tlak": "100,5", "Datum narození": "11.11.2000"}

["11.11.2000 11:12", "11.11.2001 11:12"]

[
  {"Datum závodu": "11.11.2000", "Čas uběhnutí": "11,7"},
  {"Datum závodu": "11.11.2005", "Čas uběhnutí": "10,7"}
]

# Přejmenování atributu
# Stačí dodat parametry :newName a :oldName (jen název), :assetDefinitionId

-- parametry
\set oldName 'Fotografie'
\set newName 'Profilová fotografie'
\set assetDefinitionId 123

# Kontrola
SELECT attributesinjson =
        jsonb_set(
            a.attributesinjson::jsonb,
            ARRAY[:newName],
            a.attributesinjson::jsonb -> :oldName
        ) - :oldName
FROM PI_CRM_Asset a
WHERE a.assetdefinitionid = :assetDefinitionId
  AND a.attributesinjson::jsonb ? :oldName;

# samotný update
UPDATE pi_crm_asset a
SET attributesinjson =
        jsonb_set(
            a.attributesinjson::jsonb,
            ARRAY[:newName],
            a.attributesinjson::jsonb -> :oldName
        ) - :oldName
WHERE a.assetdefinitionid = :assetDefinitionId
  AND a.attributesinjson::jsonb ? :oldName;

# Transformace jednoduchého attributu do strukturovaného atributu
# Stačí dodat parametry :newName a :oldName (jen název), :assetDefinitionId

SET @oldName = '';
SET @newName = '';
SET @assetDefinitionId = 0;

# Kontrola
SELECT JSON_SET(
           CAST(a.attributesInJson AS JSON), CONCAT('$."', @oldName, '"'),
           JSON_OBJECT(@newName, JSON_EXTRACT(
               CAST(a.attributesInJson AS JSON), CONCAT('$."', @oldName, '"'))
           )
       )
FROM PI_CRM_Asset a
WHERE a.assetDefinitionId = @assetDefinitionId
  AND JSON_EXTRACT(CAST(a.attributesInJson AS JSON), CONCAT('$."', @oldName, '"')) IS NOT NULL;

# samotný update
UPDATE PI_CRM_Asset a
SET a.attributesInJson = JSON_SET(
       CAST(a.attributesInJson AS JSON), CONCAT('$."', @oldName, '"'),
       JSON_OBJECT(@newName, JSON_EXTRACT(
           CAST(a.attributesInJson AS JSON), CONCAT('$."', @oldName, '"'))
       )
    )
WHERE a.assetDefinitionId = @assetDefinitionId
  AND JSON_EXTRACT(CAST(a.attributesInJson AS JSON), CONCAT('$."', @oldName, '"')) IS NOT NULL;

# Změna hodnoty při změně typu z BOOLEAN na SELECT
# Stačí dodat parametry :name (jen název) a :oldValue a :newValue, :assetDefinitionId

SET @name = '';
SET @oldValue = '';
SET @newValue = '';
SET @assetDefinitionId = 0;

# Kontrola
SELECT JSON_REPLACE(CAST(a.attributesInJson AS JSON), CONCAT('$."', @name, '"'), @newValue)
FROM PI_CRM_Asset a
WHERE a.assetDefinitionId = @assetDefinitionId
  AND JSON_EXTRACT(CAST(a.attributesInJson AS JSON), CONCAT('$."', @name, '"')) like @oldValue;

# samotný update
UPDATE PI_CRM_Asset a
SET a.attributesInJson = JSON_REPLACE(CAST(a.attributesInJson AS JSON), CONCAT('$."', @name, '"'), @newValue)
WHERE a.assetDefinitionId = @assetDefinitionId
  AND JSON_EXTRACT(CAST(a.attributesInJson AS JSON), CONCAT('$."', @name, '"')) like @oldValue;

# Smazání atributu
# Stačí dodat parametry :name (jen název), :assetDefinitionId

SET @name = '';
SET @assetDefinitionId = 0;

# Kontrola
SELECT JSON_REMOVE(
    CAST(a.attributesInJson AS JSON),
    CONCAT('$."', @name, '"') )
FROM PI_CRM_Asset a
WHERE a.assetDefinitionId = @assetDefinitionId
  AND JSON_EXTRACT(CAST(a.attributesInJson AS JSON), CONCAT('$."', @name, '"')) IS NOT NULL;

# samotný update
UPDATE PI_CRM_Asset a
SET a.attributesInJson = JSON_REMOVE(
         CAST(a.attributesInJson AS JSON),
    CONCAT('$."', @name, '"') )
WHERE a.assetDefinitionId = @assetDefinitionId
  AND JSON_EXTRACT(CAST(a.attributesInJson AS JSON), CONCAT('$."', @name, '"')) IS NOT NULL;

# Přejmenování hodnoty atributu
# Stačí dodat parametry :attributeName (jen název) a :oldValue a :newValue, :assetDefinitionId

SET @attributeName = '""';
SET @oldOption = '""'; #Může být i číslo '0' | Json pole '{"X":"Y"}'
SET @newOption = '""'; #Může být i číslo '0' | Json pole '{"X":"Y"}'
SET @assetDefinitionId = 0;

UPDATE PI_CRM_Asset a
SET a.attributesInJson =
        JSON_UNQUOTE(JSON_REPLACE(a.attributesInJson, CONCAT('$.', @attributeName), @newOption))
WHERE a.assetDefinitionId = @assetDefinitionId
  AND JSON_UNQUOTE(JSON_EXTRACT(a.attributesInJson, CONCAT('$.', @attributeName))) = @oldOption;

TRUNCATE pi_crm_assetduplicity;

UPDATE counter
SET currentid = 0
WHERE name = 'cz.vrk.pi.crm.model.model.AssetDuplicity';

INSERT INTO PI_CRM_AssetDuplicity(id_, asset1, asset2, duplicityLevel, assetdefinitionid, byattributes)
SELECT row_number() over (order by a.id_), a.id_, a2.id_, 'WEAK', a.assetdefinitionid, pca.numberlabel
FROM PI_CRM_Asset a
     JOIN PI_CRM_Asset a2 ON a.number_ = a2.number_
    AND a.id_ <> a2.id_
    AND a.assetDefinitionId = a2.assetDefinitionId
     JOIN pi_crm_assetdefinition pca on a.assetdefinitionid = pca.id_
WHERE a.number_ != '' and a.id_ < a2.id_
GROUP BY a.id_, a2.id_, pca.numberlabel
HAVING (SELECT COUNT(*)
        FROM pi_crm_asset a2
        WHERE a.assetDefinitionId = a2.assetDefinitionId
          and a.number_ = a2.number_ and a.id_ < a2.id_) > 2;

UPDATE counter
SET currentid = (SELECT MAX(id_) FROM pi_crm_assetduplicity)
WHERE name = 'cz.vrk.pi.crm.model.model.AssetDuplicity';

UPDATE pi_crm_assetduplicity ad
SET duplicityLevel = 'STRONG',
    byattributes   = (SELECT concat(concat(pca.numberlabel, ', '), pca.namelabel)
                      FROM pi_crm_assetdefinition pca
                      WHERE ad.assetdefinitionid = pca.id_)
WHERE id_ in (SELECT ad.id_
              FROM pi_crm_assetduplicity ad
                   JOIN PI_CRM_Asset a1 ON ad.asset1 = a1.id_
                   JOIN PI_CRM_Asset a2 ON ad.asset2 = a2.id_
              WHERE a1.name = a2.name);

SET @newName = 'Číslo zápisu';
SET @assetDefinitionId = 0;

#Kontrola
SELECT JSON_UNQUOTE(JSON_EXTRACT(CAST(a.attributesInJson AS JSON), CONCAT('$."', @newName, '"'))), a.*
FROM PI_CRM_Asset a
WHERE a.assetDefinitionId = @assetDefinitionId

#Nastavení hodnoty těm co mají vyplněno
UPDATE PI_CRM_Asset a
SET a.number_ = JSON_UNQUOTE(JSON_EXTRACT(CAST(a.attributesInJson AS JSON), CONCAT('$."', @newName, '"')))
WHERE a.assetDefinitionId = @assetDefinitionId
  AND JSON_EXTRACT(CAST(a.attributesInJson AS JSON), CONCAT('$."', @newName, '"')) IS NOT NULL;

#Vynulování hodnoty, těm co nemají vyplněno
UPDATE PI_CRM_Asset a
SET a.number_ = ''
WHERE a.assetDefinitionId = @assetDefinitionId
  AND JSON_EXTRACT(CAST(a.attributesInJson AS JSON), CONCAT('$."', @newName, '"')) IS NULL;