-- phpMyAdmin SQL Dump
-- version 4.1.9
-- http://www.phpmyadmin.net
--
-- Host: localhost:8889
-- Generation Time: Ago 12, 2015 alle 11:18
-- Versione del server: 5.5.34
-- PHP Version: 5.5.10

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `tracciamentoCampioni`
--

-- --------------------------------------------------------

--
-- Struttura della tabella `bb_anamnesi`
--

CREATE TABLE `bb_anamnesi` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` char(10) NOT NULL,
  `apf1` int(11) NOT NULL,
  `apf2` int(11) NOT NULL,
  `apf3` int(11) NOT NULL,
  `apf4` int(11) NOT NULL,
  `apf5` int(11) NOT NULL,
  `apf6` int(11) NOT NULL,
  `apf7` int(11) NOT NULL,
  `apf8` int(11) NOT NULL,
  `apf9` int(11) NOT NULL,
  `apf10` int(11) NOT NULL,
  `apf11` int(11) NOT NULL,
  `apf12` int(11) NOT NULL,
  `apf13` int(11) NOT NULL,
  `apf14` int(11) NOT NULL,
  `apf15` int(11) NOT NULL,
  `apf16` int(11) NOT NULL,
  `apf17` int(11) NOT NULL,
  `apf18` int(11) NOT NULL,
  `af1` int(11) NOT NULL,
  `af2` int(11) NOT NULL,
  `af3` int(11) NOT NULL,
  `af4` int(11) NOT NULL,
  `apr1` int(11) NOT NULL,
  `apr2` int(11) NOT NULL,
  `created_by` int(11) NOT NULL,
  `edited_by` int(11) NOT NULL,
  `date_birth` date NOT NULL,
  `date_registration` datetime NOT NULL,
  `date_modification` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Struttura della tabella `bb_box`
--

CREATE TABLE `bb_box` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `barcode` varchar(45) DEFAULT '0',
  `num_rows` int(11) NOT NULL,
  `num_cols` int(11) NOT NULL,
  `description` text NOT NULL,
  `created_by` int(11) NOT NULL,
  `edited_by` int(11) NOT NULL,
  `date_registration` datetime NOT NULL,
  `date_modification` datetime NOT NULL,
  `date_elimination` datetime NOT NULL,
  `eliminated` int(11) NOT NULL,
  `delete_container` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7318 ;

-- --------------------------------------------------------

--
-- Struttura della tabella `bb_box_position`
--

CREATE TABLE `bb_box_position` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idBox` varchar(10) NOT NULL,
  `coordinate_box` varchar(10) CHARACTER SET utf8 NOT NULL,
  `idCampione` int(11) NOT NULL,
  `created_by` int(11) NOT NULL,
  `edited_by` int(11) NOT NULL,
  `date_registration` datetime NOT NULL,
  `date_modification` datetime NOT NULL,
  `date_elimination` datetime NOT NULL,
  `eliminated` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=25 ;

-- --------------------------------------------------------

--
-- Struttura della tabella `bb_campioni`
--

CREATE TABLE `bb_campioni` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nome` varchar(45) CHARACTER SET utf8 NOT NULL,
  `barcode` varchar(45) CHARACTER SET utf8 NOT NULL DEFAULT '0',
  `tipo` int(11) NOT NULL,
  `volume` float DEFAULT NULL,
  `paziente` varchar(8) CHARACTER SET utf8 DEFAULT NULL,
  `box` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `coordinate_box` varchar(10) CHARACTER SET utf8 NOT NULL,
  `tipo_contenitore` int(11) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `anatomical_site` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `concentrazione` float DEFAULT NULL,
  `purezza` float DEFAULT NULL,
  `purezza_diluenti` float DEFAULT NULL,
  `fattore_diluzione` int(11) DEFAULT NULL,
  `giorni_astinenza` int(11) DEFAULT NULL,
  `tempo_liquifazione` time DEFAULT NULL,
  `ph` float DEFAULT NULL,
  `viscosita` float DEFAULT NULL,
  `conc_spermatozoi` int(11) DEFAULT NULL,
  `num_spermatozoi` int(11) DEFAULT NULL,
  `motilita_rpr` int(11) DEFAULT NULL,
  `motilita_pr` int(11) DEFAULT NULL,
  `motilita_is` int(11) DEFAULT NULL,
  `motilita_i` int(11) DEFAULT NULL,
  `vitalita` int(11) DEFAULT NULL,
  `morfologia` int(11) DEFAULT NULL,
  `leuciti` int(11) DEFAULT NULL,
  `descrizione` text CHARACTER SET utf8,
  `consenso` int(11) DEFAULT '0',
  `created_by` int(11) NOT NULL,
  `date_registration` datetime NOT NULL,
  `edited_by` int(11) NOT NULL,
  `date_modification` datetime NOT NULL,
  `date_elimination` datetime NOT NULL,
  `eliminated` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `barcode` (`barcode`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=25 ;

-- --------------------------------------------------------

--
-- Struttura della tabella `bb_consensi`
--

CREATE TABLE `bb_consensi` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `titolo` varchar(255) NOT NULL,
  `data` date DEFAULT NULL,
  `file` text NOT NULL,
  `descrizione` text,
  `idCampione` int(11) DEFAULT NULL,
  `idPaziente` varchar(8) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `date_registration` datetime NOT NULL,
  `edited_by` int(11) NOT NULL,
  `date_modification` datetime NOT NULL,
  `date_elimination` datetime NOT NULL,
  `eliminated` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

--
-- Struttura della tabella `bb_containers`
--

CREATE TABLE `bb_containers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `location` text NOT NULL,
  `temperature` int(11) NOT NULL,
  `description` text NOT NULL,
  `created_by` int(11) NOT NULL,
  `edited_by` int(11) NOT NULL,
  `date_registration` datetime NOT NULL,
  `date_modification` datetime NOT NULL,
  `date_elimination` datetime NOT NULL,
  `eliminated` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7273 ;

-- --------------------------------------------------------

--
-- Struttura della tabella `bb_contatto`
--

CREATE TABLE `bb_contatto` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `address` varchar(255) DEFAULT NULL,
  `zip` int(11) DEFAULT NULL,
  `phone` int(45) DEFAULT NULL,
  `city` varchar(45) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `country` varchar(45) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `date_registration` datetime NOT NULL,
  `edited_by` int(11) NOT NULL,
  `date_modification` datetime NOT NULL,
  `date_elimination` datetime NOT NULL,
  `eliminated` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=35 ;

-- --------------------------------------------------------

--
-- Struttura della tabella `bb_parent_containers`
--

CREATE TABLE `bb_parent_containers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_box_container` int(11) NOT NULL,
  `type` int(11) NOT NULL,
  `type_parent` int(11) NOT NULL,
  `id_container` int(11) NOT NULL,
  `delete_container` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_box_container` (`id_box_container`),
  UNIQUE KEY `id_box_container_2` (`id_box_container`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

-- --------------------------------------------------------

--
-- Struttura della tabella `bb_pazienti`
--

CREATE TABLE `bb_pazienti` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(8) NOT NULL,
  `name` text NOT NULL,
  `surname` text NOT NULL,
  `taxcode` text NOT NULL,
  `sex` int(11) DEFAULT NULL,
  `ethnic_group` int(11) NOT NULL,
  `birth_date` datetime NOT NULL,
  `survey_data` varchar(255) DEFAULT NULL,
  `death_date` datetime DEFAULT NULL,
  `death_cause` varchar(255) DEFAULT NULL,
  `contact_information` int(11) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `edited_by` int(11) NOT NULL,
  `date_registration` datetime NOT NULL,
  `date_modification` datetime NOT NULL,
  `date_elimination` datetime NOT NULL,
  `eliminated` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Struttura della tabella `bb_relazioni_pazienti`
--

CREATE TABLE `bb_relazioni_pazienti` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `anamnesi_id` int(11) NOT NULL,
  `code_paziente` varchar(8) NOT NULL,
  `date_registration` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Struttura della tabella `bb_relazioni_ruoli_utenti`
--

CREATE TABLE `bb_relazioni_ruoli_utenti` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=35 ;

-- --------------------------------------------------------

--
-- Struttura della tabella `bb_ruoli`
--

CREATE TABLE `bb_ruoli` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role` varchar(45) NOT NULL,
  `parent_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `role` (`role`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

-- --------------------------------------------------------

--
-- Struttura della tabella `bb_sop`
--

CREATE TABLE `bb_sop` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `titolo` varchar(45) CHARACTER SET utf8 NOT NULL,
  `descrizione` text CHARACTER SET utf8,
  `created_by` int(11) NOT NULL,
  `date_registration` datetime NOT NULL,
  `edited_by` int(11) NOT NULL,
  `date_modification` datetime NOT NULL,
  `date_elimination` datetime NOT NULL,
  `eliminated` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

--
-- Struttura della tabella `bb_sop_avanzate`
--

CREATE TABLE `bb_sop_avanzate` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `titolo` varchar(45) CHARACTER SET utf8 NOT NULL,
  `descrizione` text CHARACTER SET utf8,
  `codice` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `metodo` int(11) DEFAULT NULL,
  `data` datetime DEFAULT NULL,
  `paziente_1` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `paziente_2` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `id_ciclo` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `pickup` datetime DEFAULT NULL,
  `ora_post_ph` time DEFAULT NULL,
  `tot_ore` int(11) DEFAULT NULL,
  `campioni_residui` int(11) DEFAULT NULL,
  `campioni_utilizzati` int(11) DEFAULT NULL,
  `campioni_scartati` int(11) DEFAULT NULL,
  `livello_coltura` int(11) DEFAULT NULL,
  `ora_post_ins` time DEFAULT NULL,
  `ora_inizio` time DEFAULT NULL,
  `ora_fine` time DEFAULT NULL,
  `medium` int(11) DEFAULT NULL,
  `lotto` int(11) DEFAULT NULL,
  `data_scadenza` datetime DEFAULT NULL,
  `codice_box` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `date_registration` datetime NOT NULL,
  `edited_by` int(11) NOT NULL,
  `date_modification` datetime NOT NULL,
  `date_elimination` datetime NOT NULL,
  `eliminated` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

-- --------------------------------------------------------

--
-- Struttura della tabella `bb_sop_azioni`
--

CREATE TABLE `bb_sop_azioni` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSop` int(11) NOT NULL,
  `titolo` varchar(45) NOT NULL,
  `testo` text,
  `avviso` text,
  `gravita_avviso` int(11) DEFAULT '0',
  `propedeuticita` int(11) DEFAULT '0',
  `created_by` int(11) DEFAULT NULL,
  `date_registration` datetime DEFAULT NULL,
  `edited_by` int(11) DEFAULT NULL,
  `date_modification` datetime DEFAULT NULL,
  `date_elimination` datetime DEFAULT NULL,
  `eliminated` int(11) DEFAULT '0',
  `note` text,
  `errori` text,
  `stato` int(11) DEFAULT '0',
  `date_execution` datetime DEFAULT NULL,
  `executed_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

--
-- Struttura della tabella `bb_sop_tracking_azioni`
--

CREATE TABLE `bb_sop_tracking_azioni` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSop` int(11) NOT NULL,
  `idAzione` int(11) NOT NULL,
  `data_esecuzione` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `eseguito_da` int(11) NOT NULL,
  `note` varchar(255) DEFAULT NULL,
  `errori` varchar(255) DEFAULT NULL,
  `stato` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Struttura della tabella `bb_subcontainers`
--

CREATE TABLE `bb_subcontainers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_type_subcontainer` int(11) NOT NULL,
  `name` varchar(45) NOT NULL,
  `description` text NOT NULL,
  `created_by` int(11) NOT NULL,
  `edited_by` int(11) NOT NULL,
  `date_registration` datetime NOT NULL,
  `date_modification` datetime NOT NULL,
  `date_elimination` datetime NOT NULL,
  `eliminated` int(11) NOT NULL,
  `delete_container` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6895 ;

-- --------------------------------------------------------

--
-- Struttura della tabella `bb_tipologia_subcontainers`
--

CREATE TABLE `bb_tipologia_subcontainers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `if_container` int(11) NOT NULL,
  `name` varchar(45) NOT NULL,
  `description` text NOT NULL,
  `created_by` int(11) NOT NULL,
  `edited_by` int(11) NOT NULL,
  `date_registration` datetime NOT NULL,
  `date_modification` datetime NOT NULL,
  `date_elimination` datetime NOT NULL,
  `eliminated` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

--
-- Struttura della tabella `bb_tipologie_campioni`
--

CREATE TABLE `bb_tipologie_campioni` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nome` varchar(54) CHARACTER SET utf8 NOT NULL,
  `descrizione` text CHARACTER SET utf8,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Struttura della tabella `bb_utenti`
--

CREATE TABLE `bb_utenti` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `surname` varchar(45) NOT NULL,
  `usermail` varchar(45) NOT NULL,
  `pwd` varchar(255) NOT NULL,
  `email` varchar(45) NOT NULL,
  `taxcode` varchar(45) DEFAULT NULL,
  `address` varchar(45) DEFAULT NULL,
  `cap` varchar(45) DEFAULT NULL,
  `city` varchar(45) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `mobile` varchar(45) DEFAULT NULL,
  `status` int(11) NOT NULL,
  `contact_information` int(11) NOT NULL,
  `created_by` int(11) NOT NULL,
  `edited_by` int(11) NOT NULL,
  `date_registration` datetime NOT NULL,
  `date_modification` datetime NOT NULL,
  `date_elimination` datetime NOT NULL,
  `eliminated` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `usermail` (`usermail`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=17 ;
