API tools faq
paste
Guest User

dump

a guest
Sep 6th, 2019
150
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 15.97 KB | None | 0 0
raw download clone embed print report
  1. -- MySQL dump 10.13  Distrib 5.1.73, for redhat-linux-gnu (x86_64)
  2. --
  3. -- Host: localhost    Database: interno
  4. -- ------------------------------------------------------
  5. -- Server version   5.1.73-log
  6.  
  7. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  8. /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  9. /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  10. /*!40101 SET NAMES utf8 */;
  11. /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
  12. /*!40103 SET TIME_ZONE='+00:00' */;
  13. /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
  14. /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
  15. /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
  16. /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
  17.  
  18. --
  19. -- Dumping routines for database 'interno'
  20. --
  21. /*!50003 DROP FUNCTION IF EXISTS `afegeix_lletra` */;
  22. /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
  23. /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
  24. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  25. /*!50003 SET character_set_client  = utf8 */ ;
  26. /*!50003 SET character_set_results = utf8 */ ;
  27. /*!50003 SET collation_connection  = utf8_general_ci */ ;
  28. /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
  29. /*!50003 SET sql_mode              = '' */ ;
  30.  
  31. DELIMITER ;;
  32. /*!50003 CREATE*/ /*!50020 DEFINER=`abac`@`%`*/ /*!50003 PROCEDURE `centre_exercici_eliminar_indiscriminat_assentaments`(IN _centreId INT(11), _centre_exerciciId INT(11))
  33. BEGIN
  34.  
  35.     SET foreign_key_checks = 0;
  36.     delete from assentament where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  37.     delete from assentament_cab where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  38.     delete from pred_det where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  39.     delete from pred_cab where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  40.     delete from minutes_det  where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  41.     delete from minutes_cab  where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  42.     delete from fra_det where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  43.     delete from fra_cab where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  44.     DELETE from combo_mesos where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  45.     DELETE from banc where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  46.     DELETE from canvi_patrimoni where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  47.     DELETE from ccost where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  48.     DELETE from cc_vigencia where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  49.     DELETE from inc_presupostari where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  50.  
  51.     DELETE from inv_baixa where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  52.     DELETE from inv_comptes where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  53.     DELETE from inv_ubicacions where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  54.     DELETE from inventari_hist where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  55.     DELETE from inventari where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  56.  
  57.     DELETE FROM amortitza_control where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  58.     DELETE FROM tmp_amortitza_massiu_previ where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  59.  
  60.     DELETE from tmp_importacio_inventari where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  61.     DELETE from tmp_inventari_compara where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  62.  
  63.     DELETE from ass_inv_info where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  64.     DELETE from ass_inv_cancel_saldo where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  65.  
  66.     DELETE from model_303 where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  67.     DELETE from model_303_iva where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  68.     DELETE from pressupost where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  69.     DELETE from tercers where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  70.     DELETE from tipus_venciment where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  71.     DELETE from tmp_imp_tresoreria_cab where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  72.     DELETE from tmp_imp_tresoreria_det where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  73.     DELETE from tmp_importacio_errors where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  74.    
  75.     DELETE from tmp_importacio_nomines where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  76.     DELETE from tmp_imputa_cc_tipus_ordre where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  77.     DELETE from tmp_imputa_pantalla_cc where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  78.     DELETE from tmp_imputa_pantalla_cc_previ where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  79.     DELETE from tmp_imputa_pantalla_recalcul where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  80.    
  81.     DELETE from tmp_mayor where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  82.     DELETE from tmp_prepara_traspas_comptes where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  83.     DELETE from tmp_previ_pressupost where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  84.  
  85.     DELETE from compte where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  86.     DELETE from centre_constants where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  87.     DELETE from tipus_iva where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  88.     DELETE from fra_serie where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  89.     DELETE from sequence_data_comptadors where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  90.     DELETE from combo_mesos where centre_id = _centreId and centre_exercici_id = _centre_exerciciId;
  91.     delete from centre_exercici where centre_id = _centreId and id = _centre_exerciciId;
  92.  
  93. END */;;
  94. DELIMITER ;
  95. /*!50003 SET sql_mode              = @saved_sql_mode */ ;
  96. /*!50003 SET character_set_client  = @saved_cs_client */ ;
  97. /*!50003 SET character_set_results = @saved_cs_results */ ;
  98. /*!50003 SET collation_connection  = @saved_col_connection */ ;
  99. /*!50003 DROP PROCEDURE IF EXISTS `centre_exercici_tancament_obertura` */;
  100. /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
  101. /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
  102. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  103. /*!50003 SET character_set_client  = latin1 */ ;
  104. /*!50003 SET character_set_results = latin1 */ ;
  105. /*!50003 SET collation_connection  = latin1_swedish_ci */ ;
  106. /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
  107. /*!50003 SET sql_mode              = '' */ ;
  108.  
  109. DELIMITER ;;
  110. /*!50003 CREATE*/ /*!50020 DEFINER=`abac`@`%`*/ /*!50003 PROCEDURE `centre_exercici_tancament_obertura`(IN `_centre_id` INT(11) , IN `_centre_exercici_id` INT(11), IN `_comptes_no_usades` INT(1) )
  111. BEGIN
  112.  
  113. -- Estaba aqui con upd comptes
  114.  
  115. DECLARE _SEQ_IMPORTACIO INT(11);
  116. SET _SEQ_IMPORTACIO = nextval('centre_exercici');
  117.  
  118.  
  119. insert into centre_exercici(centre_id, usuari_creacio_id,tipcont, iniejer, finejer
  120. ,any_exercici, digits_compte,digits_subcompte, data_treball, nrr )
  121. select  centre_id,999999,tipcont,DATE_ADD(iniejer, INTERVAL 1 YEAR) as iniejer ,DATE_ADD(finejer, INTERVAL 1 YEAR) as finejer
  122. ,DATE_FORMAT( DATE_ADD(iniejer, INTERVAL 1 YEAR),'%Y'),digits_compte,digits_subcompte,DATE_FORMAT( DATE_ADD(iniejer, INTERVAL 1 YEAR),'01-01-%Y'), _SEQ_IMPORTACIO
  123. from centre_exercici
  124. where id = _centre_exercici_id ;
  125. select 'centre_exercici';
  126.  
  127. select id INTO @id_exercici from centre_exercici where   centre_id = _centre_id and nrr = _SEQ_IMPORTACIO;
  128. select 'recuperar_exercici';
  129.  
  130.  
  131.  
  132.  
  133. insert into compte
  134.         ( centre_id, centre_exercici_id, tipus_pla,    comptabilitat_id,  compte,  compte_pare,  compte_pare_id,  titol,
  135.           balanc,  resultado,  A_P,  tipus_compte,  tipus_auxiliar,  criteri_imputacio,  bloqueig_entrada_directa,  bloquejada,
  136.           D_H,  saldo_cd,  tipus_reg,  cc_id,  tercer,  signo,  signe,  errsct,  PGC,  TITOL50)
  137.         SELECT
  138.           centre_id,  @id_exercici,  tipus_pla,    comptabilitat_id,  compte,  compte_pare,  compte_pare_id,  titol,
  139.           balanc,  resultado,  A_P,  tipus_compte,  tipus_auxiliar,  criteri_imputacio,  bloqueig_entrada_directa,  bloquejada,
  140.           D_H,  saldo_cd,  tipus_reg,  cc_id,  tercer,  signo,  signe,  errsct,  PGC,  TITOL50
  141.         FROM
  142.           compte  
  143.           where centre_id = _centre_id and centre_exercici_id = _centre_exercici_id AND tipus_compte = 'M' ;
  144.          
  145.  
  146.  
  147.  delete from tmp_prepara_traspas_comptes where centre_id = _centre_id  and centre_exercici_id =_centre_exercici_id ;
  148.  
  149. insert into tmp_prepara_traspas_comptes(centre_id,centre_exercici_id,compte, compte_id, import_deure, import_haver)
  150. SELECT centre_id,centre_exercici_id, compte,compte_id, sum(import_deure), sum(import_haver)  FROM assentament
  151. where centre_id = _centre_id  and centre_exercici_id =_centre_exercici_id  
  152. group by compte, compte_id;
  153.  
  154. insert into tmp_prepara_traspas_comptes
  155. ( centre_id,  centre_exercici_id,    compte, compte_id)
  156.  SELECT
  157.           centre_id,  @id_exercici,    compte,  id
  158.         FROM
  159.           compte
  160.              WHERE compte not in ( select compte from  tmp_prepara_traspas_comptes )
  161.           and centre_id = _centre_id   and centre_exercici_id = _centre_exercici_id AND tipus_compte = 'A'
  162.              and saldo_final <> 0  ;
  163.        
  164.  
  165.  
  166.  
  167. IF _comptes_no_usades = 0 THEN
  168.  
  169.  
  170.         insert into compte
  171.         ( centre_id, centre_exercici_id, tipus_pla,    comptabilitat_id,  compte,  compte_pare,  compte_pare_id,  titol,
  172.           balanc,  resultado,  A_P,  tipus_compte,  tipus_auxiliar,  criteri_imputacio,  bloqueig_entrada_directa,  bloquejada,
  173.           D_H,  saldo_cd,  tipus_reg,  cc_id,  tercer,  signo,  signe,  errsct,  PGC,  TITOL50)
  174.          
  175.         SELECT
  176.           centre_id,  @id_exercici,  tipus_pla,    comptabilitat_id,  compte,  compte_pare,  compte_pare_id,  titol,
  177.           balanc,  resultado,  A_P,  tipus_compte,  tipus_auxiliar,  criteri_imputacio,  bloqueig_entrada_directa,  bloquejada,
  178.           D_H,  saldo_cd,  tipus_reg,  cc_id,  tercer,  signo,  signe,  errsct,  PGC,  TITOL50
  179.         FROM
  180.           compte  
  181.           where centre_id = _centre_id and centre_exercici_id = _centre_exercici_id
  182.           and left(compte,1) >= 1 and left(compte,1) <=7
  183.           AND tipus_compte = 'A';
  184.        
  185. else
  186.  
  187.       insert into compte
  188.         ( centre_id, centre_exercici_id, tipus_pla,    comptabilitat_id,  compte,  compte_pare,  compte_pare_id,  titol,
  189.           balanc,  resultado,  A_P,  tipus_compte,  tipus_auxiliar,  criteri_imputacio,  bloqueig_entrada_directa,  bloquejada,
  190.           D_H,  saldo_cd,  tipus_reg,  cc_id,  tercer,  signo,  signe,  errsct,  PGC,  TITOL50)
  191.          
  192.       SELECT
  193.           c.centre_id,  @id_exercici,  c.tipus_pla,    c.comptabilitat_id,  c.compte,  c.compte_pare,  c.compte_pare_id,  c.titol,
  194.           c.balanc,  c.resultado,  c.A_P,  c.tipus_compte,  c.tipus_auxiliar,  c.criteri_imputacio,  c.bloqueig_entrada_directa,
  195.           c.bloquejada,c.D_H,c.saldo_cd,  c.tipus_reg,  c.cc_id,  c.tercer,  c.signo,  c.signe,  c.errsct,  c.PGC,  c.TITOL50
  196.         FROM
  197.           compte c  inner join tmp_prepara_traspas_comptes t
  198.      
  199.           where
  200.           c.centre_id = t.centre_id and c.centre_exercici_id = t.centre_exercici_id and c.id = t.compte_id
  201.           and c.centre_id = _centre_id and c.centre_exercici_id = _centre_exercici_id
  202.          
  203.           AND tipus_compte = 'A';
  204.      end if;
  205.  
  206.  
  207.  
  208.  
  209.  
  210. END */;;
  211. DELIMITER ;
  212. /*!50003 SET sql_mode              = @saved_sql_mode */ ;
  213. /*!50003 SET character_set_client  = @saved_cs_client */ ;
  214. /*!50003 SET character_set_results = @saved_cs_results */ ;
  215. /*!50003 SET collation_connection  = @saved_col_connection */ ;
  216. /*!50003 DROP PROCEDURE IF EXISTS `centre_exercici_tancament_obertura_comptes` */;
  217. /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
  218. /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
  219. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  220. /*!50003 SET character_set_client  = utf8 */ ;
  221. /*!50003 SET character_set_results = utf8 */ ;
  222. /*!50003 SET collation_connection  = utf8_general_ci */ ;
  223. /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
  224. /*!50003 SET sql_mode              = '' */ ;
  225.  
  226. DELIMITER ;;
  227. /*!50003 CREATE*/ /*!50020 DEFINER=`abac`@`%`*/ /*!50003 PROCEDURE `centre_exercici_tancament_obertura_crea_exercici`(IN `_PROCES` VARCHAR(20), IN `_NUM_TANCAMENT` INT(11), IN `_centre_id` INT(11), IN `_centre_exercici_id` INT(11), IN `_comptes_no_usades` INT(1))
  228. BEGIN
  229.  
  230. -- Estaba aqui
  231.  
  232.   INSERT INTO centre_exercici(centre_id, usuari_creacio_id, tipcont, iniejer, finejer,
  233.   any_exercici, digits_compte, digits_subcompte, exercici_ant , data_treball, nrr, sii_activo)
  234.   SELECT centre_id, 999999, tipcont, DATE_ADD(iniejer, INTERVAL 1 YEAR) as iniejer,
  235.   DATE_ADD(finejer, INTERVAL 1 YEAR) as finejer, DATE_FORMAT( DATE_ADD(iniejer, INTERVAL 1 YEAR),'%Y'),
  236.   digits_compte, digits_subcompte, _centre_exercici_id,
  237.   DATE_FORMAT( DATE_ADD(iniejer, INTERVAL 1 YEAR),'01-01-%Y'), _SEQ_IMPORTACIO, sii_activo
  238.   FROM centre_exercici
  239.     WHERE id = _centre_exercici_id ;
  240.  
  241.   SELECT 'centre_exercici';
  242.  
  243.   SELECT id INTO @id_exercici from centre_exercici where   centre_id = _centre_id and nrr = _SEQ_IMPORTACIO;
  244.   SELECT 'recuperar_exercici';
  245.  
  246.   INSERT INTO compte
  247.         ( centre_id, centre_exercici_id, tipus_pla,    comptabilitat_id,  compte,  compte_pare,  compte_pare_id,  titol,
  248.           balanc,  resultado,  A_P,  tipus_compte,  tipus_auxiliar,  criteri_imputacio,  bloqueig_entrada_directa,  bloquejada,
  249.           D_H,  saldo_cd,  tipus_reg,  cc_id,  tercer,  signo,  signe,  errsct,  PGC,  TITOL50)
  250.         SELECT
  251.           centre_id,  @id_exercici,  tipus_pla,    comptabilitat_id,  compte,  compte_pare,  compte_pare_id,  titol,
  252.           balanc,  resultado,  A_P,  tipus_compte,  tipus_auxiliar,  criteri_imputacio,  bloqueig_entrada_directa,  bloquejada,
  253.           D_H,  saldo_cd,  tipus_reg,  cc_id,  tercer,  signo,  signe,  errsct,  PGC,  TITOL50
  254.         FROM
  255.           compte
  256.           where centre_id = _centre_id and centre_exercici_id = _centre_exercici_id AND tipus_compte = 'M' ;
  257.  
  258.   IF _NUM_TANCAMENT <> 0 THEN
  259.    insert into tancaments(proces,num_tancament,centre_id,centre_exercici_id,paso,processada,resultat,centre_id_NOU,centre_exercici_id_NOU,comptes_no_usades)
  260.     values(_PROCES,_NUM_TANCAMENT,_centre_id,_centre_exercici_id,'exercici','1','ok',_centre_id,@id_exercici,_comptes_no_usades);
  261.   END IF;
  262.  
  263.   SELECT "ok";
  264.  
  265. END */;;
  266. DELIMITER ;
  267. /*!50003 SET sql_mode              = @saved_sql_mode */ ;
  268. /*!50003 SET character_set_client  = @saved_cs_client */ ;
  269. /*!50003 SET character_set_results = @saved_cs_results */ ;
  270. /*!50003 SET collation_connection  = @saved_col_connection */ ;
  271. /*!50003 DROP PROCEDURE IF EXISTS `centre_exercici_traspas_saldos` */;
  272. /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
  273. /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
  274. /*!50003 SET @saved_col_connection = @@collation_connection */ ;
  275. /*!50003 SET character_set_client  = latin1 */ ;
  276. /*!50003 SET character_set_results = latin1 */ ;
  277. /*!50003 SET collation_connection  = latin1_swedish_ci */ ;
  278. /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
  279. /*!50003 SET sql_mode              = '' */ ;
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!