ALTER TABLE public.parametros
  ADD COLUMN group_pos boolean;

COMMENT ON COLUMN public.parametros.group_pos
  IS 'false';


update parametros set group_pos=true;


ALTER TABLE public.tickets
  ADD COLUMN purchase_cost double precision DEFAULT 0;
  
ALTER TABLE public.posventasdeta
  ADD COLUMN purchase_cost double precision DEFAULT 0;
  
update tickets set purchase_cost = articulos.preciocosto 
from articulos 
where articulos.codigo = tickets.codigo;

update posventasdeta set purchase_cost = articulos.preciocosto 
from articulos 
where articulos.codigo = posventasdeta.codigo;

ALTER TABLE public.stocks
  ADD COLUMN stocktype varchar(30) DEFAULT 'N';
  
ALTER TABLE public.stocks
  ADD COLUMN datechanged date DEFAULT CURRENT_DATE;  
  
ALTER TABLE public.stocks
  ADD COLUMN comments text;  
  
  
--Table: public.stockadjust

--DROP TABLE public.stockadjust;

CREATE TABLE public.stockadjust (
  id           serial NOT NULL,
  codigo       varchar(100),
  store        varchar(100),
  status       varchar(30),
  datecreated  date DEFAULT CURRENT_DATE,
  comments     text,
  operation    varchar(30),
  qtty         numeric(18,2) DEFAULT 0,
  /* Keys */
  CONSTRAINT stockadjust_pkey
    PRIMARY KEY (id)
) WITH (
    OIDS = FALSE
  );

ALTER TABLE public.stockadjust
  OWNER TO postgres;  
  
  
ALTER TABLE public.stockadjust
  ADD COLUMN oldstock numeric(8,2);

ALTER TABLE public.stockadjust
  ADD COLUMN newstock numeric(18,2);  
  
  
DROP VIEW public.qtotal_stockadjust;

CREATE VIEW public.qtotal_stockadjust
AS
SELECT 
  stockadjust.store, 
  stockadjust.codigo AS product, 
  stockadjust.status, 
  CASE 
    WHEN (
      (stockadjust.operation)::text = '+'::text
    ) THEN
      (stockadjust.qtty)::double precision 
    ELSE
      (0)::double precision 
  END AS entrances, 
  CASE 
    WHEN (
      (stockadjust.operation)::text = '-'::text
    ) THEN
      (stockadjust.qtty)::double precision 
    ELSE
      (0)::double precision 
  END AS exits, 
  0 AS sales, 
  parametros.lastinventorydate 
FROM stockadjust, parametros 
WHERE
  (
    stockadjust.datecreated < (parametros.lastinventorydate + 1)
  ) 
ORDER BY 
  stockadjust.store, 
  stockadjust.codigo;

ALTER TABLE public.qtotal_stockadjust
  OWNER TO postgres;  
  
  
DROP VIEW public.qinventory_ungrouped;


DROP VIEW public.qinventory_union;

CREATE VIEW public.qinventory_union
AS
SELECT 
  qtotal_entrances_exits.store, 
  qtotal_entrances_exits.product, 
  qtotal_entrances_exits.status, 
  qtotal_entrances_exits.entrances, 
  qtotal_entrances_exits.exits, 
  qtotal_entrances_exits.sales 
FROM qtotal_entrances_exits 
UNION ALL
SELECT 
  qtotal_sales.store, 
  qtotal_sales.product, 
  qtotal_sales.status, 
  qtotal_sales.entrances, 
  qtotal_sales.exits, 
  qtotal_sales.sales 
FROM qtotal_sales
UNION ALL
SELECT 
  qtotal_stockadjust.store, 
  qtotal_stockadjust.product, 
  qtotal_stockadjust.status, 
  qtotal_stockadjust.entrances, 
  qtotal_stockadjust.exits, 
  qtotal_stockadjust.sales 
FROM qtotal_stockadjust;

ALTER TABLE public.qinventory_union
  OWNER TO postgres;


CREATE VIEW public.qinventory_ungrouped
(
  codigo,
  store,
  status,
  entrances,
  exits,
  sales
)
AS
SELECT 
  articulos.codigo, 
  qinventory_union.store, 
  qinventory_union.status, 
  COALESCE(sum(qinventory_union.entrances), (0)::double precision) AS entrances, 
  COALESCE(sum(qinventory_union.exits), (0)::double precision) AS exits, 
  COALESCE(sum(qinventory_union.sales), (0)::double precision) AS sales 
FROM (articulos 
  LEFT JOIN qinventory_union ON 
    (
      (
        (articulos.codigo)::text = (qinventory_union.product)::text
      )
    )) 
GROUP BY 
  articulos.codigo, 
  qinventory_union.store, 
  qinventory_union.status 
ORDER BY 
  articulos.codigo;

ALTER TABLE public.qinventory_ungrouped
  OWNER TO postgres;  
  
  
  
CREATE TABLE public.stockadjustheader (
  id           serial,
  datecreated  date DEFAULT CURRENT_DATE,
  store        varchar(30),
  comments     text
) WITH (
    OIDS = FALSE
  );


ALTER TABLE public.stockadjust
  ADD COLUMN numero integer;



CREATE INDEX stockadjust_index01
  ON public.stockadjust
  (id DESC);  
  
  
  
ALTER TABLE public.adjustments
  ADD COLUMN store varchar(30);  
  
ALTER TABLE public.adjustments
  ADD COLUMN stock numeric(18,2);  
  
ALTER TABLE public.stocks
  ADD COLUMN id serial NOT NULL PRIMARY KEY;