/*Ejecutar antes de restaurar.*/

*- Crear el usuario "replicacion" contraseña "hwxn440"
(en caso de olvidarse y correr la restauracion correr esto:
GRANT ALL ON SCHEMA replicacion TO replicacion;
GRANT ALL ON TABLE replicacion.rep_instrucciones TO replicacion;
despues de restaurar)

CREATE OR REPLACE FUNCTION comp_mon_cotiz_base(integer)
  RETURNS double precision AS
$BODY$--recibe id de comp de cliente y devuelve cotiz. de la moneda del comprobante
select 1::float8$BODY$
  LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION art_comp_en_rd(integer, integer)
  RETURNS integer AS
$BODY$select 0::int4$BODY$
  LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION cant_cd_rd_por_comp(integer, integer)
  RETURNS double precision AS
'SELECT 0::float8'
  LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION cant_cd(integer, integer)
  RETURNS double precision AS
'SELECT 0::float8'
  LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION art_rd_en_comp(integer, integer)
  RETURNS integer AS
$BODY$select 0::int4$BODY$
  LANGUAGE 'sql' STABLE;

CREATE OR REPLACE FUNCTION art_rd_en_oc(integer, integer)
  RETURNS integer AS
$BODY$select 0::int4$BODY$
  LANGUAGE 'sql' STABLE;

CREATE OR REPLACE FUNCTION cant_ocd_rd_por_rd(integer, integer)
  RETURNS double precision AS
'SELECT 0::float8'
  LANGUAGE 'sql' STABLE;

CREATE OR REPLACE FUNCTION cant_rd(integer, integer)
  RETURNS double precision AS
'SELECT 0::float8'
  LANGUAGE 'sql' STABLE;

CREATE OR REPLACE FUNCTION cant_cd_rd_por_rd(integer, integer)
  RETURNS double precision AS
'SELECT 0::float8'
  LANGUAGE 'sql' STABLE;

/* Si el restore tiene nueve errores, está ok:WARNING: errors ignored on restore: 9
Son las 9 funcs. que ya existen.

ASÍ EL RESTORE DEMORA 6 MINUTOS*/

Correr esto para agregar unos campos 

ALTER TABLE sujetos ADD COLUMN memo character varying(4096);
DROP VIEW comp_v;
CREATE OR REPLACE VIEW comp_v AS
SELECT c.id_comp, c.letra, c.id_puesto, c.nro_comp, c.fecha, c.id_area, 
c.id_sujeto, c.obse, c.id_usuario, c.imp_total,
c.obse_impre, c.id_moneda, c.estado_imp, c.fec_auto, c.obse_auto, 
c.id_usuario_auto, c.logcausas_auto, c.id_tipo_comp,
c.pago, c.total_fiscal, c.id_vendedor, c.rem_fiscal, c.ult_modif, 
c.cotiz_base_comp, c.id_rubro_comp, c.nroz,
c.id_comp_terceros, c.id_cp, ((COALESCE(ct.tc, ''::character 
varying)::text || COALESCE(c.letra, ''::character varying)::text) || 
'-'::text)
|| compdes(c.id_puesto, c.nro_comp)::text AS nro, m.moneda, m.signo, 
s.sujeto, a.area, s.doc AS cuit, s.direccion, s.memo, ci.condicion,
ci.cod_cf AS id_cond_cf, ci.completra, ci.ci, l.localidad, 
sdt.tipo_doc_cf, ct.signo_comp, ct.tipo_comp, ct.tc, ct.esventa, 
cr.rubro_comp,
s.cod_postal, p.provincia, a.id_tipo_comp_simple, cts.tipo_comp AS 
tipo_comp_simple, cp.descripcion AS descr_cp
  FROM comp c
  LEFT JOIN sujetos s ON c.id_sujeto = s.id_sujeto
  LEFT JOIN condiciones_iva ci ON s.id_condicion = ci.id_condicion
  LEFT JOIN localidades l ON s.cod_postal::text = l.id_localidad::text
  LEFT JOIN provincias p ON l.id_provincia = p.id_provincia
  LEFT JOIN areas a ON c.id_area = a.id_area
  LEFT JOIN monedas m ON c.id_moneda = m.id_moneda
  LEFT JOIN sujetos_doc_tipos sdt ON s.id_tipo_doc = sdt.id_tipo_doc
  LEFT JOIN comp_tipos ct ON c.id_tipo_comp = ct.id_tipo_comp
  LEFT JOIN comp_rubros cr ON c.id_rubro_comp = cr.id_rubro_comp
  LEFT JOIN comp_tipos cts ON a.id_tipo_comp_simple = cts.id_tipo_comp
  LEFT JOIN centros_productivos cp ON c.id_cp = cp.id_cp;





/*Ejecutar después de restaurar*/
CREATE OR REPLACE FUNCTION comp_mon_cotiz_base(integer)
  RETURNS double precision AS
$BODY$--recibe id de comp de cliente y devuelve cotiz. de la moneda del comprobante
select cotiz_base_comp from comp where id_comp = $1$BODY$
  LANGUAGE 'sql' STABLE;


CREATE OR REPLACE FUNCTION art_comp_en_rd(integer, integer)
  RETURNS integer AS
$BODY$select distinct rd.id_articulo from cd_rd 
left join remitos_detalles rd on cd_rd.id_rem = rd.id_remito and cd_rd.id_rem_det = rd.id_det
where cd_rd.id_comp = $1 and cd_rd.id_comp_det = $2$BODY$
  LANGUAGE 'sql' STABLE;
COMMENT ON FUNCTION art_comp_en_rd(integer, integer) IS 'Dada una fila de comprobante devuelve los arts de los remitos det. asociados. Deberia ser un solo art. y el mismo que el del detalle del comp';


CREATE OR REPLACE FUNCTION cant_cd_rd_por_comp(integer, integer)
  RETURNS double precision AS
'SELECT coalesce(sum(coalesce(cant,0)),0) FROM cd_rd WHERE id_comp = $1 AND id_comp_det = $2'
  LANGUAGE 'sql' STABLE;

CREATE OR REPLACE FUNCTION cant_cd(integer, integer)
  RETURNS double precision AS
'SELECT coalesce(cant,0) FROM comp_det WHERE id_comp = $1 AND id_det = $2'
  LANGUAGE 'sql' STABLE;

CREATE OR REPLACE FUNCTION art_rd_en_comp(integer, integer)
  RETURNS integer AS
$BODY$select distinct cd.id_articulo from cd_rd 
   left join comp_det cd on cd_rd.id_comp = cd.id_comp and cd_rd.id_comp_det = cd.id_det
   where cd_rd.id_rem = $1 and cd_rd.id_rem_det = $2$BODY$
  LANGUAGE 'sql' STABLE;
COMMENT ON FUNCTION art_rd_en_comp(integer, integer) IS 'Dada una fila de remito devuelve los arts de comp_det asociados. Deberia ser un solo art. y el mismo que el del detalle del remito';

CREATE OR REPLACE FUNCTION art_rd_en_oc(integer, integer)
  RETURNS integer AS
$BODY$select distinct ocd.id_articulo from ocd_rd 
left join ordenes_compra_det ocd on ocd_rd.id_oc = ocd.id_oc and ocd_rd.id_oc_det = ocd.id_det
where ocd_rd.id_rem = $1 and ocd_rd.id_rem_det = $2$BODY$
  LANGUAGE 'sql' STABLE;
COMMENT ON FUNCTION art_rd_en_oc(integer, integer) IS 'Dada una fila de remito devuelve los arts de las ordenes_compra_det asociados. Deberia ser un solo art. y el mismo que el del detalle del remito';

CREATE OR REPLACE FUNCTION cant_ocd_rd_por_rd(integer, integer)
  RETURNS double precision AS
'SELECT coalesce(sum(coalesce(cant,0)),0) FROM ocd_rd WHERE id_rem = $1 AND id_rem_det = $2'
  LANGUAGE 'sql' STABLE;

CREATE OR REPLACE FUNCTION cant_rd(integer, integer)
  RETURNS double precision AS
'SELECT coalesce(cant,0) FROM remitos_detalles WHERE id_remito = $1 and id_det = $2'
  LANGUAGE 'sql' STABLE;

CREATE OR REPLACE FUNCTION cant_cd_rd_por_rd(integer, integer)
  RETURNS double precision AS
'SELECT coalesce(sum(coalesce(cant,0)),0) FROM cd_rd WHERE id_rem = $1 AND id_rem_det = $2'
  LANGUAGE 'sql' STABLE;




DROP VIEW sujetos_v;
CREATE OR REPLACE VIEW sujetos_v AS
SELECT s.*, s.doc AS cuit, ci.condicion, ci.completra, 
ci.id_tipo_doc_pred, t.transporte, td.tipo_doc, l.localidad,
le.localidad AS localidad_efec, rgt.afip_norm, l.id_provincia, 
p.provincia, ci.id_condicion_tercero, td.id_tipo_doc_tercero,
lt.sujeto AS lug_tra, pl.listap AS listap_cli, ec.estado_civil, 
pr.profesion, c.calle AS dir_calle, pa.pais AS nacionalidad, ci.ci,
lpad(s.id_sujeto::text, 13, 0::text) AS cod_barras
  FROM sujetos s
  LEFT JOIN localidades l ON s.cod_postal::text = l.id_localidad::text
  LEFT JOIN localidades le ON s.cod_postal_efec::text = le.id_localidad::text
  LEFT JOIN provincias p ON l.id_provincia = p.id_provincia
  LEFT JOIN transporte t ON s.id_transporte = t.id_transporte
  LEFT JOIN condiciones_iva ci ON s.id_condicion = ci.id_condicion
  LEFT JOIN ret_gan_tipos rgt ON rgt.id_rg = s.id_rg
  LEFT JOIN sujetos_doc_tipos td ON s.id_tipo_doc = td.id_tipo_doc
  LEFT JOIN sujetos lt ON s.id_lug_tra = lt.id_sujeto
  LEFT JOIN precios_listas pl ON s.id_listap_cli = pl.id_listap
  LEFT JOIN estados_civiles ec ON s.id_estado_civil = ec.id_estado_civil
  LEFT JOIN profesiones pr ON s.id_profesion = pr.id_profesion
  LEFT JOIN calles c ON s.id_calle = c.id_calle
  LEFT JOIN paises pa ON s.id_nacionalidad = pa.id_pais;




CREATE OR REPLACE VIEW comp_combo_v AS
 SELECT NULL::integer AS id_comp, NULL::integer AS id_det, NULL::integer AS
id_listap, NULL::integer AS id_articulo, NULL::character varying AS
descripcion, NULL::character varying AS descrart, NULL::numeric AS precio,
NULL::numeric AS cant, NULL::numeric AS p_unitario, NULL::numeric AS costo,
NULL::numeric AS iva, NULL::numeric AS impint, NULL::double precision AS
cant_en_combo, NULL::double precision AS subtotal;
COMMENT ON VIEW comp_combo_v IS 'Tabla que se usa para enganiar al motor de
regente para que nos cree el atributo info en el listado de componentes, es
necesario para cuando se usan los combos y la habilidad para detallar las
partes de los mismos';
CREATE OR REPLACE FUNCTION art_cant_en_combo(integer, integer) RETURNS 
double precision AS
$BODY$--$1 idarticulo, $2 idcombo Devuelve la cantidad del art id_articulo($1) que participa en el combo $2
select cant from articulos_combos where id_articulo = $1 and 
id_art_combo =$2;$BODY$
  LANGUAGE 'sql' STABLE;



ALTER TABLE sujetos ADD COLUMN memo character varying(4096);
DROP VIEW comp_v;
CREATE OR REPLACE VIEW comp_v AS
SELECT c.id_comp, c.letra, c.id_puesto, c.nro_comp, c.fecha, c.id_area, 
c.id_sujeto, c.obse, c.id_usuario, c.imp_total,
c.obse_impre, c.id_moneda, c.estado_imp, c.fec_auto, c.obse_auto, 
c.id_usuario_auto, c.logcausas_auto, c.id_tipo_comp,
c.pago, c.total_fiscal, c.id_vendedor, c.rem_fiscal, c.ult_modif, 
c.cotiz_base_comp, c.id_rubro_comp, c.nroz,
c.id_comp_terceros, c.id_cp, ((COALESCE(ct.tc, ''::character 
varying)::text || COALESCE(c.letra, ''::character varying)::text) || 
'-'::text)
|| compdes(c.id_puesto, c.nro_comp)::text AS nro, m.moneda, m.signo, 
s.sujeto, a.area, s.doc AS cuit, s.direccion, s.memo, ci.condicion,
ci.cod_cf AS id_cond_cf, ci.completra, ci.ci, l.localidad, 
sdt.tipo_doc_cf, ct.signo_comp, ct.tipo_comp, ct.tc, ct.esventa, 
cr.rubro_comp,
s.cod_postal, p.provincia, a.id_tipo_comp_simple, cts.tipo_comp AS 
tipo_comp_simple, cp.descripcion AS descr_cp
 FROM comp c
 LEFT JOIN sujetos s ON c.id_sujeto = s.id_sujeto
 LEFT JOIN condiciones_iva ci ON s.id_condicion = ci.id_condicion
 LEFT JOIN localidades l ON s.cod_postal::text = l.id_localidad::text
 LEFT JOIN provincias p ON l.id_provincia = p.id_provincia
 LEFT JOIN areas a ON c.id_area = a.id_area
 LEFT JOIN monedas m ON c.id_moneda = m.id_moneda
 LEFT JOIN sujetos_doc_tipos sdt ON s.id_tipo_doc = sdt.id_tipo_doc
 LEFT JOIN comp_tipos ct ON c.id_tipo_comp = ct.id_tipo_comp
 LEFT JOIN comp_rubros cr ON c.id_rubro_comp = cr.id_rubro_comp
 LEFT JOIN comp_tipos cts ON a.id_tipo_comp_simple = cts.id_tipo_comp
 LEFT JOIN centros_productivos cp ON c.id_cp = cp.id_cp;