Atributos

Índices

Anexos

Tabela de Empresas do Grupo Tramontina (Company)

Tabela de Idiomas (FileLanguage)

Tabela de Tipos de arquivos (FileType)

Resoluções (FileResolution)

Tipos de extensões (FileExtension)

Exemplos

• Retorna todos os arquivos e imagens do produto 22202000 da empresa Cutelaria.

SELECT *
  FROM vw_ProductFileBDSite
 WHERE ProductCode = '22202000'
   AND Company = 2

• Retorna apenas a imagem Padrão com resolução 95×83 em português ou Multi idioma do produto 22202000 da empresa Cutelaria.

SELECT *
  FROM vw_ProductFileBDSite
 WHERE ProductCode = '22202000'
   AND Company = 2
   AND FileType = 'PD'
   AND FileLanguage IN ('M','P')
   AND FileResolution = 'C'

Código da View

CREATE VIEW vw_ProductFileBDSite
       (Company,
       ProductCode,
       FileLanguage,
       FileType,
       FileResolution,
       FileExtension,
       FileSequential,
       FileURL,
       FileName) AS
SELECT `pra`.`empcod` AS `Company`,
       `pra`.`itecod` AS `ProductCode`,
       `pra`.`pracodidioma` AS `FileLanguage`,
       `pra`.`pratip` AS `FileType`,
       `pra`.`prareso` AS `FileResolution`,
       `pra`.`praexten` AS `FileExtension`,
       `pra`.`praseq` AS `FileSequential`,
       (CASE `pra`.`pratip`
            WHEN 'FT' THEN concat('https://www.tramontina.net/foto360/foto360.html?ref=',`pra`.`itecod`,'&lang=',`pra`.`pracodidioma`,'&view=',CONVERT(lpad(`pra`.`praseq`,3,'0') USING latin1))
            ELSE TRIM(`pur`.`pururl`)
       END) AS `FileURL`,
       (CASE `pra`.`pratip`
            WHEN 'FT' THEN ''
            WHEN 'LV' THEN ''
            ELSE concat(`par`.`parvl`,`emp`.`empsigla`,'/',`pra`.`pranomearq`)
       END) AS `FileName`
  FROM ((((`diapra` `pra`
         LEFT JOIN `diapur` `pur` ON(((`pra`.`empcod` = `pur`.`empcod`)
                                       AND (`pra`.`itecod` = `pur`.`itecod`)
                                       AND (`pra`.`pracodidioma` = `pur`.`pracodidioma`)
                                       AND (`pra`.`praseq` = `pur`.`praseq`))))
       JOIN `diaprc` `prc`)
      JOIN `diapar` `par`)
     JOIN `diaemp` `emp`)
 WHERE ((((`pra`.`pratip` IN ('PD',
                              'IX',
                              'DD',
                              'FD',
                              'DT',
                              'EB',
                              'SE',
                              'SL',
                              'PR',
                              'DC'))
        AND (`pra`.`prasit` = 'S')
        AND (`pra`.`prareso` IN ('C',
                                 'D',
                                 'E',
                                 'F',
                                 'G'))
        AND (`pra`.`praexten` = 'JPG'))
      OR ((`pra`.`pratip` IN ('PN',
                              'AN',
                              'RN',
                              'EN'))
         AND (`pra`.`prasit` = 'S')
         AND (`pra`.`prareso` IN ('C',
                                  'D',
                                  'E',
                                  'F',
                                  'G'))
         AND (`pra`.`praexten` = 'PNG'))
     OR ((`pra`.`pratip` IN ('DX',
                             'RV',
                             'SK',
                             'DW',
                             'MN',
                             'MI',
                             'CN',
                             'IN',
                             'FI',
                             'FL',
                             'RE',
                             'RC',
                             'RT',
                             'CA',
                             'GA'))
         AND (`pra`.`prasit` = 'S'))
      OR ((`pra`.`pratip` = 'LV')
         AND (`pra`.`prasit` = 'S'))
      OR ((`pra`.`pratip` = 'FT')
         AND (`pra`.`prasit` = 'S'))
      OR ((`pra`.`pratip` = 'GI')
         AND (`pra`.`prasit` = 'S')))
         AND (`pra`.`itecod` = `prc`.`itecod`)
         AND (`pra`.`empcod` = `prc`.`empcod`)
         AND (`par`.`empcod` = 20)
         AND (`par`.`sissist` = 'DI')
         AND (`par`.`parcod` = 14)
         AND (`pra`.`empcod` = `emp`.`empcod`))