001/* 002 * Copyright 2020 Anyware Services 003 * 004 * Licensed under the Apache License, Version 2.0 (the "License"); 005 * you may not use this file except in compliance with the License. 006 * You may obtain a copy of the License at 007 * 008 * http://www.apache.org/licenses/LICENSE-2.0 009 * 010 * Unless required by applicable law or agreed to in writing, software 011 * distributed under the License is distributed on an "AS IS" BASIS, 012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 013 * See the License for the specific language governing permissions and 014 * limitations under the License. 015 */ 016package org.ametys.odf.ose.export.impl.odf.db; 017 018import java.sql.Types; 019import java.util.ArrayList; 020import java.util.HashSet; 021import java.util.List; 022import java.util.Set; 023 024import org.apache.commons.lang3.StringUtils; 025import org.apache.commons.lang3.tuple.Pair; 026 027import org.ametys.odf.ose.db.ParameterizableQuery; 028import org.ametys.odf.ose.db.column.Column; 029import org.ametys.odf.ose.db.column.DefaultColumn; 030import org.ametys.odf.ose.db.column.ForeignKeyColumn; 031import org.ametys.odf.ose.db.column.SourceIdColumn; 032import org.ametys.odf.ose.db.parameter.QueryParameter; 033import org.ametys.odf.ose.db.parameter.ValuedQueryParameter; 034import org.ametys.odf.ose.export.ExportUtils; 035 036/** 037 * Helper to build queries to manage the ELEMENT_PEDAGOGIQUE table. 038 */ 039public final class ElementPedagogiqueHelper 040{ 041 private static final String __TABLE_NAME = "ELEMENT_PEDAGOGIQUE"; 042 043 private ElementPedagogiqueHelper() 044 { 045 // Helper 046 } 047 048 /** 049 * Queries to create the table and associated view. 050 * @return List of queries for table and view creation 051 */ 052 public static List<ParameterizableQuery> initialize() 053 { 054 Set<Column> columns = new HashSet<>(); 055 columns.add(new DefaultColumn("ID", "VARCHAR2(100 CHAR)", false, "SOURCE_CODE")); 056 columns.add(new DefaultColumn("LIBELLE", "VARCHAR2(200 CHAR)", false)); 057 columns.add(new DefaultColumn("CODE", "VARCHAR2(50 CHAR)", false)); 058 columns.add(new ForeignKeyColumn("ETAPE_ID", "VARCHAR2(100 CHAR)", false, Pair.of("ETAPE", "SOURCE_CODE"), true)); 059 columns.add(new ForeignKeyColumn("STRUCTURE_ID", "VARCHAR2(100 CHAR)", false, Pair.of("STRUCTURE", "CODE"), false)); 060 columns.add(new ForeignKeyColumn("PERIODE_ID", "VARCHAR2(3 CHAR)", true, Pair.of("PERIODE", "CODE"), false)); 061 columns.add(new DefaultColumn("ANNEE_ID", "NUMBER(38, 0)", false)); 062 columns.add(new SourceIdColumn()); 063 return ExportUtils.initializeTableAndView(__TABLE_NAME, columns); 064 } 065 066 /** 067 * Query to delete all lines from the table. 068 * @return The query for deletion 069 */ 070 public static ParameterizableQuery deleteFrom() 071 { 072 return ExportUtils.deleteFromAmetys(__TABLE_NAME); 073 } 074 075 private static ParameterizableQuery _insertInto(String code, String label, Long oseCatalog, String orgUnit, String stepHolder, String periodType) 076 { 077 List<QueryParameter> parameters = new ArrayList<>(); 078 parameters.add(new ValuedQueryParameter("ID", code, Types.VARCHAR)); 079 parameters.add(new ValuedQueryParameter("LIBELLE", StringUtils.truncate(label, 200), Types.VARCHAR)); 080 parameters.add(new ValuedQueryParameter("CODE", code, Types.VARCHAR)); 081 parameters.add(new ValuedQueryParameter("STRUCTURE_ID", orgUnit, Types.VARCHAR)); 082 parameters.add(new ValuedQueryParameter("ETAPE_ID", stepHolder, Types.VARCHAR)); 083 parameters.add(new ValuedQueryParameter("PERIODE_ID", StringUtils.truncate(periodType, 3), Types.VARCHAR)); 084 parameters.add(new ValuedQueryParameter("ANNEE_ID", oseCatalog, Types.NUMERIC)); 085 return ExportUtils.insertIntoAmetys(__TABLE_NAME, parameters); 086 } 087 088 /** 089 * Queries to insert a line in the table and in NOEUD and SCENARIO_NOEUD. 090 * @param code The course code 091 * @param label The label 092 * @param oseCatalog The OSE catalog 093 * @param orgUnit The orgunit 094 * @param stepHolder The step holder 095 * @param periodType The type of period 096 * @return The list of queries to insert values in the tables 097 */ 098 public static List<ParameterizableQuery> insertInto(String code, String label, Long oseCatalog, String orgUnit, String stepHolder, String periodType) 099 { 100 String nodeId = oseCatalog + "_" + code; 101 102 return List.of( 103 _insertInto(code, label, oseCatalog, orgUnit, stepHolder, periodType), 104 NoeudHelper.insertInto(nodeId, code, false, label, oseCatalog), 105 ScenarioNoeudHelper.insertInto(nodeId) 106 ); 107 } 108}