DELIMITER $$

DROP PROCEDURE IF EXISTS raport_kontrola_dostaw_biedronka$$

CREATE DEFINER=root@localhost PROCEDURE raport_kontrola_dostaw_biedronka(
IN _MODE VARCHAR(38),
IN _DATAOD DATE,
IN _DATADO DATE,
IN _PlatnikGUID VARCHAR(38),
IN _ParamA VARCHAR(50),
IN _ParamB VARCHAR(50),
IN _ParamC VARCHAR(50)
)

/*
CALL raport_kontrola_dostaw_biedronka('INIT',_DATAOD,_DATADO,_PlatnikGUID,_ParamA,_ParamB,_ParamC);
CALL raport_kontrola_dostaw_biedronka('INIT','2014-10-26','2014-10-26','','','','');
CALL raport_kontrola_dostaw_biedronka('','2014-10-26','2014-10-26','','','','');
*/

BEGIN

SET _PlatnikGUID=IF(_PlatnikGUID='','{B3B597BB-72E1-473B-B972-33DA256FF062}',_PlatnikGUID);
-- kontrguid='{F9E71DB7-ABC9-4A7F-B54F-23E7E4265490}'
-- update temp_ecod_potw_po set idntowr='' where idntowr like "2014%";
UPDATE temp_ecod_potw_po t, TowrNg n  set t.idntowr = n.guid,t.towrsymb =  if(length(t.towrsymb) = 0, n.symbol, t.towrsymb),t.towrnazwa = if(length(t.towrnazwa) = 0, n.nazwa, t.towrnazwa) WHERE n.kod_kreskowy1=t.kod_kreskowy and n.promocja_status<>2 and n.deleted = 0 and t.idntowr='';

IF _MODE = 'INIT' THEN
  CALL raport_kontrola_dostaw_biedronka('DONE',_DATAOD,_DATADO,_PlatnikGUID,_ParamA,_ParamB,_ParamC);

  CREATE TEMPORARY TABLE temp_bied_kontrola (
  data date DEFAULT NULL,
  nrzm varchar(200) DEFAULT NULL,
  nrwz varchar(200) DEFAULT NULL,
  kontrguid varchar(38) NOT NULL,
  towrsymb varchar(38) NOT NULL,
  idntowr varchar(38) NOT NULL,
  ean varchar(38) NOT NULL,
  iloscpotw double(21,4) DEFAULT 0.000,
  ilosc double(16,4) NOT NULL DEFAULT 0.0000
  );
  IF (_DATADO IS NULL) THEN
    SET _DATADO=_DATAOD;
  END IF;
  
  INSERT INTO temp_bied_kontrola
  SELECT n.terminrealizacji as data,
  #group_concat(distinct n.nrzamwkontrah) as nrzm,
  group_concat(distinct n.nrzamw) as nrzm,
  NULL as nrwz, 
  n.kontrguid,p.towrsymb,p.idntowr,p.kod_kreskowy as ean,sum(p.ilosc) as iloscpotw ,0
  FROM temp_ecod_potw_po p, temp_ecod_potw_ng n 
  WHERE n.guid=p.idnng and (n.terminrealizacji BETWEEN _DATAOD AND _DATADO)
  GROUP BY data,kontrguid,idntowr;

  INSERT INTO temp_bied_kontrola
  SELECT n.datawystaw as data,NULL,group_concat(distinct n.nrdokmag),
  n.kontrguid,p.towrsymb,
  p.idntowr,
  t.kod_kreskowy as ean,0,sum(p.ilosc) as ilosc
  FROM oppo p,opng n, TowrNg t 
  WHERE t.guid=p.idntowr and n.guid=p.idnoper and (n.datawystaw BETWEEN _DATAOD AND _DATADO) and n.rodzaj=10 and n.platnikguid=_PlatnikGUID
  GROUP BY data,kontrguid,idntowr;

-- korekta bulek 2108bGD -> 2108b:
  UPDATE temp_bied_kontrola set idntowr='{0AE3786D-F991-4FB6-8F03-6883C16B880C}',towrsymb='2108bGD' where idntowr='{79704151-D4D8-44D5-8AD0-5FA36CFF7BD7}';

  CREATE TABLE temp_bied_kontrola_group
  SELECT min(t.data) as dataod,max(t.data) as datado,k.symbol as kontrahent,
  left(group_concat(distinct t.nrzm),100) as nrzm,
  left(group_concat(distinct t.nrwz),100) as nrwz,
  t.towrsymb,t.idntowr,left(group_concat(distinct nullif(t.ean,'')),100) as ean,sum(t.iloscpotw) as iloscpotw,sum(t.ilosc) as iloscWZ,
  k.katcol1,
  (select valuex from v_kkn1 v where v.klucz=k.katcol1) as kierownik
  FROM temp_bied_kontrola t, kontrah k
  WHERE k.guid=t.kontrguid
  GROUP BY kontrguid,idntowr
  HAVING iloscpotw<>iloscwz 
  ;

END IF;

IF _MODE = 'SELECT' THEN
  SELECT * FROM temp_bied_kontrola_group;  
END IF;

IF _MODE = 'DONE' THEN
  DROP TEMPORARY TABLE IF EXISTS temp_bied_kontrola;  
  DROP TABLE IF EXISTS temp_bied_kontrola_group;  
END IF;

IF _MODE = '' THEN
  CALL raport_kontrola_dostaw_biedronka('INIT',_DATAOD,_DATADO,_PlatnikGUID,_ParamA,_ParamB,_ParamC);
  CALL raport_kontrola_dostaw_biedronka('SELECT',_DATAOD,_DATADO,_PlatnikGUID,_ParamA,_ParamB,_ParamC);
  CALL raport_kontrola_dostaw_biedronka('DONE',_DATAOD,_DATADO,_PlatnikGUID,_ParamA,_ParamB,_ParamC);
END IF;

END$$

DELIMITER ;

