<?php

class m200203_175647_drop_trigger_ativa_cliente extends CDbMigration
{
	public function up()
	{
		$this->execute('DROP TRIGGER IF EXISTS `cliente_ativado`');
	}

	public function down()
	{
		$createTriggerSql = <<< SQL
CREATE TRIGGER `cliente_ativado` BEFORE UPDATE ON `clientes`
 FOR EACH ROW BEGIN
  IF (OLD.CLI_DTATIVACAO <> NEW.CLI_DTATIVACAO or (OLD.CLI_DTATIVACAO is null AND NEW.CLI_DTATIVACAO is not null)) THEN
    IF (select count(*) from atendimentos where ATD_IDCLIENTE = NEW.CLI_IDCLIENTE and ATD_IDMOTIVO = 456) > 0 THEN
      update atendimentos set ATD_DTCADASTRO = NEW.CLI_DTATIVACAO, ATD_DTLANCAMENTO = NEW.CLI_DTATIVACAO where ATD_IDCLIENTE = NEW.CLI_IDCLIENTE and ATD_IDMOTIVO = 456;
    ELSE
      INSERT INTO atendimentos (ATD_IDCLIENTE, ATD_DTLANCAMENTO, ATD_DTCADASTRO, ATD_IDFUNCIONARIO, ATD_SITUACAO, ATD_TIPO, ATD_HORAINICIO, ATD_HORAFINAL, ATD_TOTALHORAS, ATD_OBS, ATD_IDMOTIVO ) VALUES (NEW.CLI_IDCLIENTE, now(), now(), 16, "R", "O", DATE_FORMAT(NOW(), '%H:%i:%s'),ADDTIME(DATE_FORMAT(NOW(), '%H:%i:%s'),DATE_FORMAT(NOW(), '%00:%1:%00')), '00:01:00', "Cliente ativado nesta data", "456");
    END IF;
    
    IF 
      NEW.CLI_DTATIVACAO >= DATE_FORMAT(NOW(), '%Y-%m-%d') AND 
      NEW.CLI_DT_PROXIMA_AVALIACAO_IMPLANTACAO is null AND 
      NEW.CLI_NOTA_SATISFACAO_IMPLANTACAO is null AND
      NEW.CLI_IDMOTIVO not in (79, 67) AND
      (select count(*) from regioescidades rcd left join revendas rev on rcd.RCD_IDREVENDA = rev.REV_IDREVENDA where rev.REV_CONTROLE_IMPLANTACAO = 1 AND rcd.RCD_IDCIDADE = NEW.CLI_CIDADE) > 0 AND 
      not exists(select clt.CLT_IDCLIENTE_AUTORIZADO from clientes_transferencias clt where clt.CLT_IDCLIENTE_AUTORIZADO = NEW.CLI_IDCLIENTE and clt.CLT_DTSOLICITACAO >= (SELECT DATE_ADD(NOW(), INTERVAL -30 DAY))) AND
      not exists(select cot.COT_IDCLIENTE from contratos cot where cot.COT_SITUACAO = 8 AND cot.COT_IDCLIENTE = NEW.CLI_IDCLIENTE)
    THEN
      
      IF (select dia from(select ADDDATE(LAST_DAY(SUBDATE(now(), INTERVAL 1 MONTH)),1) + INTERVAL @a DAY dia,if( date_format((ADDDATE(LAST_DAY(SUBDATE(now(), INTERVAL 1 MONTH)),1) + INTERVAL @a DAY),'%w') between 1 and 5, @b:=@b+1 , NULL) dia_util ,@a:= @a+1 as ultimo from (select @a:= 0)a ,(select @b:=0)b  , information_schema.character_sets)z where dia_util = 5) >= now()  THEN
        SET NEW.CLI_DT_PROXIMA_AVALIACAO_IMPLANTACAO = LAST_DAY(now());
        SET NEW.CLI_AVALIACAO_IMPLANTACAO = 1;
      ELSE
        SET NEW.CLI_DT_PROXIMA_AVALIACAO_IMPLANTACAO = DATE_ADD(now(), INTERVAL 1 MONTH);
        SET NEW.CLI_AVALIACAO_IMPLANTACAO = 1;
      END IF;

    END IF;

  END IF;
END
SQL;
$this->execute($createTriggerSql);
	}
}