/**
 * Copyright 2015 Ministerio de Industria, Energía y Turismo
 *
 * Este fichero es parte de "Facturae-APC".
 *
 * Licencia con arreglo a la EUPL, Versión 1.1 o –en cuanto sean aprobadas por la Comisión Europea– versiones posteriores de la EUPL (la Licencia);
 * Solo podrá usarse esta obra si se respeta la Licencia.
 *
 * Puede obtenerse una copia de la Licencia en:
 *
 * http://joinup.ec.europa.eu/software/page/eupl/licence-eupl
 *
 * Salvo cuando lo exija la legislación aplicable o se acuerde por escrito, el programa distribuido con arreglo a la Licencia se distribuye «TAL CUAL»,
 * SIN GARANTÍAS NI CONDICIONES DE NINGÚN TIPO, ni expresas ni implícitas.
 * Véase la Licencia en el idioma concreto que rige los permisos y limitaciones que establece la Licencia.
 */
package es.mityc.appfacturae.hibernate;



public class FacturaeStatics {

	//ACCOUNT
	public static final String QUERY_ACCOUNT_ID_BY_ADDRESS = "SELECT ACCOUNT_ID FROM ACCOUNT WHERE ADDRESS=";
	
	//ADDRESS
	public static final String QUERY_ADDRESS_ID_BY_ADDRESS = "SELECT ADDRESS_ID FROM ADDRESS WHERE ADDRESS=";
	
	//ADMCENTRE
	public static final String QUERY_ADMCENTRE_BY_ID ="SELECT ADMCENTRE_ID FROM ADMCENTRE WHERE ADDRESS=";
	public static final String QUERY_ADDRESS_CONTACT_ADMCENTRE_BY_ID = "SELECT ADDRESS, CONTACT FROM ADMCENTRE WHERE ADMCENTRE_ID =";
	public static final String QUERY_ADMCENTRE_BY_CONTACT = "SELECT ADMCENTRE_ID FROM ADMCENTRE WHERE CONTACT=";

	//FACTURAE
	public static final String QUERY_FACTURAE_ID_BY_PARTY_ID_SELLER ="SELECT FACTURAE_ID FROM FACTURAE WHERE PARTY_ID_SELLER=";
	public static final String QUERY_FACTURAE_BY_NUMBER = "SELECT PARTY_ID_BUYER,PARTY_ID_SELLER FROM FACTURAE WHERE FACTURAE_ID IN (SELECT FACTURAE_ID FROM FACTURAE_INVOICES WHERE INVOICE_ID IN (SELECT INVOICE_ID FROM INVOICE WHERE NVL(SERIES_CODE+NUMBER,NUMBER)='$1'))";
	public static final String QUERY_FACTURAE_XML_BY_CODE_NUMBER = "SELECT FACTURAE.XML FROM FACTURAE WHERE FACTURAE_ID IN (SELECT FACTURAE_ID FROM FACTURAE_INVOICES WHERE INVOICE_ID IN (SELECT INVOICE_ID FROM INVOICE WHERE NVL(INVOICE.SERIES_CODE + INVOICE.NUMBER,INVOICE.NUMBER) = '$1'))";
	public static final String QUERY_FACTURAE_BY_NUMBER_STATE0 = "SELECT FACTURAE_ID,PARTY_ID_SELLER,PARTY_ID_BUYER,XML FROM FACTURAE WHERE FACTURAE_ID IN (SELECT FACTURAE_ID FROM FACTURAE_INVOICES WHERE INVOICE_ID IN (SELECT INVOICE_ID FROM INVOICE WHERE NVL(SERIES_CODE+NUMBER,NUMBER) = '$1' AND STATE=0))";
	public static final String QUERY_FACTURAE_XML_BY_ID = "SELECT XML FROM FACTURAE WHERE FACTURAE_ID IN (SELECT FACTURAE_ID FROM FACTURAE_INVOICES WHERE INVOICE_ID = '$1')";
	public static final String QUERY_FACTURAE_VERSION_BY_ID = "SELECT SCHEMA_VERSION FROM FACTURAE WHERE FACTURAE_ID=";
	
	//FACTURAE_INVOICES
	public static final String QUERY_FACTURAE_INVOICES_BY_ID = "SELECT INVOICE_ID FROM FACTURAE_INVOICES WHERE FACTURAE_ID =";
	public static final String QUERY_FACTURAE_INVOICES_BY_STATE_CLASS = "SELECT FACTURAE_ID,INVOICE_ID FROM FACTURAE_INVOICES WHERE INVOICE_ID IN (SELECT INVOICE_ID FROM INVOICE WHERE STATE = '$1' AND CLASS = '$2')";
	public static final String QUERY_FACTURAE_INVOICES_BY_STATE = "SELECT FACTURAE_ID,INVOICE_ID FROM FACTURAE_INVOICES WHERE INVOICE_ID IN (SELECT INVOICE_ID FROM INVOICE WHERE STATE = '$1')";
	
	//INDIVIDUAL
	public static final String QUERY_PARTY_ID_INDIVIDUAL_BY_ADDRESS = "SELECT PARTY_ID FROM INDIVIDUAL WHERE ADDRESS=";
	public static final String QUERY_PARTY_ID_INDIVIDUAL_BY_CONTACT = "SELECT PARTY_ID FROM INDIVIDUAL WHERE CONTACT=";

	//INVOICE
	public static final String QUERY_INVOICE_BY_STATE_CLASS = "SELECT INVOICE_ID,NVL(SERIES_CODE+NUMBER,NUMBER),VERSION FROM INVOICE WHERE STATE='$1' AND CLASS='$2'";
	public static final String QUERY_INVOICE_BY_STATE_CLASS_NAME = "SELECT INVOICE_ID, NVL(SERIES_CODE+NUMBER,NUMBER) AS INVOICE_NAME, VERSION FROM INVOICE "
		+ "WHERE STATE='$1' AND CLASS='$2' AND INVOICE_NAME='$3'";
	
	//INVOICE_ITEM
	public static final String QUERY_ITEM_ID_BY_INVOICE_ID = "SELECT ITEM_ID FROM INVOICE_ITEM WHERE INVOICE_ID=";
	
	//INVOICE_PAYMENT
	public static final String QUERY_PAYMENT_ID_BY_INVOICE_ID = "SELECT PAYMENT_ID FROM INVOICE_PAYMENT WHERE INVOICE_ID = ";

	//ITEM
	public static final String QUERY_ITEM_ID_BY_DESCRIPTION = "SELECT ITEM_ID FROM ITEM WHERE DESCRIPTION=";
	
	//LEGAL_ENTITY
	public static final String QUERY_PARTY_ID_LEGAL_ENTITY_BY_ADDRESS = "SELECT PARTY_ID FROM LEGAL_ENTITY WHERE ADDRESS=";
	public static final String QUERY_PARTY_ID_LEGAL_ENTITY_BY_REGISTRATION_DATA = "SELECT PARTY_ID FROM LEGAL_ENTITY WHERE REGISTRATION_DATA=";
	public static final String QUERY_PARTY_ID_LEGAL_ENTITY_BY_CONTACT ="SELECT PARTY_ID FROM LEGAL_ENTITY WHERE CONTACT=";
	public static final String QUERY_PARTY_ID_LEGAL_ENTITY_BY_CORPORATE_NAME ="SELECT PARTY_ID FROM LEGAL_ENTITY WHERE CORPORATE_NAME =";
	public static final String QUERY_PARTY_ID_LEGAL_ENTITY_BY_TAX_IDENTIFICATION =  "SELECT PARTY_ID FROM LEGAL_ENTITY,PARTY,TAX_IDENTIFICATION WHERE LEGAL_ENTITY.PARTY_ID = PARTY.PARTY_ID AND PARTY.TAX_IDENTIFICATION = TAX_IDENTIFICATION.TAX_ID_NUMBER";

	//PARTY
	public static final String QUERY_PARTY_BY_ID = "SELECT PARTY_ID,TAX_IDENTIFICATION,CONTACT,ADDRESS FROM PARTY,INDIVIDUAL WHERE PARTY.PARTY_ID=INDIVIDUAL.PARTY_ID AND PARTY.PARTY_ID=";
	public static final String QUERY_PARTY_BY_ID_NOT_INDIVIDUAL = "SELECT PARTY_ID,TAX_IDENTIFICATION,CONTACT,ADDRESS,REGISTRATION_DATA FROM PARTY,LEGAL_ENTITY WHERE PARTY.PARTY_ID=LEGAL_ENTITY.PARTY_ID AND PARTY.PARTY_ID=";
	public static final String QUERY_PARTY_ID_BY_INDIVIDUAL_TAX_IDENTIFICATION = "SELECT PARTY_ID FROM INDIVIDUAL,PARTY,TAX_IDENTIFICATION WHERE INDIVIDUAL.PARTY_ID = PARTY.PARTY_ID AND PARTY.TAX_IDENTIFICATION = TAX_IDENTIFICATION.TAX_ID_NUMBER";
	public static final String QUERY_NAME_BY_INDIVIDUAL_PARTY = "SELECT FIRST_SURNAME, SECOND_SURNAME, NAME, TAX_IDENTIFICATION FROM INDIVIDUAL,PARTY WHERE INDIVIDUAL.PARTY_ID=PARTY.PARTY_ID AND INDIVIDUAL.PARTY_ID=";	
	public static final String QUERY_NAME_BY_LEGAL_ENTITY_PARTY ="SELECT CORPORATE_NAME,TAX_IDENTIFICATION FROM LEGAL_ENTITY,PARTY WHERE LEGAL_ENTITY.PARTY_ID=PARTY.PARTY_ID AND LEGAL_ENTITY.PARTY_ID=";
	
	//PARTY_ADMCENTRE
	public static final String QUERY_PARTY_ADMCENTRE_BY_ID = "SELECT ADMCENTRE_ID FROM PARTY_ADMCENTRE WHERE PARTY_ID =";
	public static final String QUERY_PARTY_ADMCENTRE_BY_ADMCENTRE_ID = "SELECT ADMCENTRE_ID FROM PARTY_ADMCENTRE WHERE ADMCENTRE_ID =";

	//PAYMENT_DETAIL
	public static final String QUERY_PAYMENT_DETAIL_BY_ID = "SELECT ACCOUNT_TO_BE_CREDITED,ACCOUNT_TO_BE_DEBITED FROM PAYMENT_DETAIL WHERE PAYMENT_ID = ";
	public static final String QUERY_PAYMENT_DETAIL_BY_DUE_DATE = "SELECT PAYMENT_ID FROM PAYMENT_DETAIL WHERE DUE_DATE =";
	public static final String QUERY_PAYMENT_DETAIL_BY_ACCOUNT_TO_BE_CREDITED = "SELECT * FROM PAYMENT_DETAIL WHERE ACCOUNT_TO_BE_CREDITED = ";
	
	//TAX_ID
	public static final String QUERY_TAX_ID_BY_ITEM_ID = "SELECT TAX_ID FROM ITEM_TAX WHERE ITEM_ID = ";
	
	//Parameters
	public static final String parametersInd = "PARTY_ID,NAME,FIRST_SURNAME,SECOND_SURNAME,TYPE_VERSION,ADDRESS.ADDRESS,POST_CODE,TOWN,PROVINCE,COUNTRY,TAX_IDENTIFICATION,RESIDENCE_TYPE,CONTACT";
	public static final String parametersLEn = "PARTY_ID,CORPORATE_NAME,TRADE_NAME,TYPE_VERSION,ADDRESS.ADDRESS,POST_CODE,TOWN,PROVINCE,COUNTRY,TAX_IDENTIFICATION,RESIDENCE_TYPE,CONTACT,REGISTRATION_DATA,FACE";
	public static final String QUERY_SERIE_BY_ID = "SELECT SERIES_CODE FROM INVOICE WHERE SERIES_CODE+NUMBER='$1'";
	public static final String QUERY_NUMBER_BY_ID = "SELECT NUMBER FROM INVOICE WHERE SERIES_CODE+NUMBER='$1'";
}
