<?php
// CRIAR MENSALIDADES
// **********************************
function mensalidadeCriar($options){ global $bd;

  // Informações Geral
  $periodo_mes = $options['periodo_mes'];
  $periodo_ano = $options['periodo_ano'];
  $vencimento = $options['vencimento'];
  $valor_especifico = (isset($options['valor_especifico']) && $options['valor_especifico']!="") ? moeda($options['valor_especifico']) : "";

  $plano_regiao = (isset($options['plano_regiao']) && $options['plano_regiao']>0) ? ' AND pr.id = '.$options['plano_regiao'] : '';
  $where_clube_id = isset($options['id_clube']) ? 'c.id='.$options['id_clube'] : 'c.id>0';

  $id_produto_ads = 20;

  // CLUBES
  // ***********************************
  $sql = $bd->getConexao()->prepare('
  SELECT 
    c.id as id_clube,
    cc.bd,
    cc.plano_sistema,
    cc.plano_nivel,
    pnc.valor as clientes_ativos_max,
    ifnull(faturas.qtd,0) as faturas_qtd,
    ifnull(compras.valor_ads,0) as repasse_ads,
    ifnull(compras.valor,0) as valor_produtos_manual,
    ifnull(compras.ajuste,0) as ajuste_produtos_manual,
    pais.moeda_code
  FROM clubes c
    LEFT JOIN clubes_configuracao cc ON cc.id_clube = c.id
    LEFT JOIN clubes_financeiro cf ON cf.id_clube = c.id AND cf.periodo_mes = :p_mes AND cf.periodo_ano = :p_ano AND cf.status>0
    LEFT JOIN (
                SELECT id_clube, count(DISTINCT id) AS qtd
                FROM clubes_financeiro
                WHERE status>0 AND id_clube in(select id from clubes where status>0 AND interno=0 AND cancelado=0 AND painel_representante=0)
                GROUP BY id_clube
              ) faturas on faturas.id_clube = c.id
    LEFT JOIN (
                SELECT
                  id_clube,
                  sum(CASE WHEN id_produto=:id_produto_ads THEN valor ELSE 0 END) as valor_ads,
                  sum(CASE WHEN id_produto<>:id_produto_ads THEN valor ELSE 0 END) as valor,
                  sum(valor_ajuste) as ajuste
                FROM clubes_compras
                WHERE periodo_mes = :p_mes AND periodo_ano = :p_ano AND status = 0 AND id_clube in(select id from clubes where status>0 AND interno=0 AND cancelado=0 AND painel_representante=0)
                GROUP BY id_clube
              ) compras ON compras.id_clube = c.id
    LEFT JOIN planos_niveis_configs pnc ON pnc.id_plano_config = 6 AND pnc.id_plano_nivel = cc.plano_nivel
    LEFT JOIN planos_niveis pn ON pn.id = cc.plano_nivel
    LEFT JOIN planos_grupos pg ON pg.id = pn.id_plano_grupo
    LEFT JOIN planos_regiao pr ON pr.id = pg.id_plano_regiao
    LEFT JOIN _pw.local_pais pais ON pais.id = pr.pais_id
  WHERE '.$where_clube_id.$plano_regiao.' AND c.status>0 AND c.interno=0 AND c.cancelado=0 AND c.painel_representante=0 AND cf.id is null');
  $sql->bindValue(':p_mes', $periodo_mes, PDO::PARAM_STR);
  $sql->bindValue(':p_ano', $periodo_ano, PDO::PARAM_STR);
  $sql->bindValue(':id_produto_ads', $id_produto_ads, PDO::PARAM_STR);
  if($sql->execute()){$resul_clubes = $sql->fetchall(PDO::FETCH_ASSOC);}


  // CRIANDO AS MENSALIDADES
  // ***********************************
  foreach($resul_clubes as $l){
    $id_clube = $l['id_clube'];

    // FATURA PRINCIPAL DO CLUBE
    // ***********************************
    $documento = $id_clube.'.'.numerodigitos($l['faturas_qtd']+1,3).'.'.$periodo_ano.'-'.numerodigitos($periodo_mes,2);

    // Cria a fatura para o clube
    $sql = $bd->getConexao()->prepare('INSERT INTO clubes_financeiro
    (id_clube, documento, plano_sistema, plano_nivel, moeda_iso, periodo_mes, periodo_ano, vencimento, status) VALUES
    (:id_clube, :documento, :plano_sistema, :plano_nivel, :moeda_iso, :periodo_mes, :periodo_ano, :vencimento, 1)');
    $sql->bindValue(':id_clube', $id_clube, PDO::PARAM_STR);
    $sql->bindValue(':documento', $documento, PDO::PARAM_STR);
    $sql->bindValue(':plano_sistema', $l['plano_sistema'], PDO::PARAM_STR);
    $sql->bindValue(':plano_nivel', $l['plano_nivel'], PDO::PARAM_STR);
    $sql->bindValue(':moeda_iso', strtolower($l['moeda_code']), PDO::PARAM_STR);
    $sql->bindValue(':periodo_mes', $periodo_mes, PDO::PARAM_STR);
    $sql->bindValue(':periodo_ano', $periodo_ano, PDO::PARAM_STR);
    $sql->bindValue(':vencimento', $vencimento, PDO::PARAM_STR);
    if($sql->execute());

    $id_fatura = $bd->lastId();


    // PRODUTOS PARA FATURA
    // ***********************************
    $produtos_insert = array();

    // Valores do clube
    $sql = $bd->getConexao()->prepare('
    SELECT
      pvc.valor as clube_valor,
      pvc.valor_ajuste as clube_valor_ajuste,
      p.id as produto_id,
      p.descricao as produto_descricao,
      p.code as produto_code      
    FROM clubes_configuracao cc
      LEFT JOIN planos_valores_clubes pvc on pvc.id_clube = cc.id_clube AND pvc.id_plano_nivel = cc.plano_nivel
      LEFT JOIN planos_valores pv ON pv.id = pvc.id_plano_valor
      LEFT JOIN produtos p ON p.id = pv.id_produto
    WHERE cc.id_clube = :id_clube');
    $sql->bindValue(':id_clube', $id_clube, PDO::PARAM_STR);
    if($sql->execute()){$resul_valores = $sql->fetchall(PDO::FETCH_ASSOC);}

    // Valor da LICENÇA DE USO (Produto principal da fatura) e CLIENTE EXCEDENTE
    $licenca_uso = array("id"=>0,"descricao"=>"","valor"=>0,"ajuste"=>0);
    $clientes_excedente = array("id"=>0,"descricao"=>"","valor"=>0,"ajuste"=>0);
    $outros_produtos = [];

    foreach($resul_valores as $v){
      if($v['produto_code']=="licenca"){
        $licenca_uso['id'] = $v['produto_id'];
        $licenca_uso['descricao'] = $v['produto_descricao'];
        $licenca_uso['valor'] = $v['clube_valor'];
        $licenca_uso['ajuste'] = $v['clube_valor_ajuste'];
      }
      elseif($v['produto_code']=="clientes_ativos_max"){
        $clientes_excedente['id'] = $v['produto_id'];
        $clientes_excedente['descricao'] = $v['produto_descricao'];
        $clientes_excedente['valor'] = $v['clube_valor'];
        $clientes_excedente['ajuste'] = $v['clube_valor_ajuste'];
      }
      elseif($v['produto_code']=="add_automatico" && ($v['clube_valor']!=0 || $v['clube_valor_ajuste']!=0)){
        $outros_produtos[] = $v;
      }
    }


    // LICENÇA DE USO
    // ***********************************
    $produtos_insert[] = '("'.$id_fatura.'", "'.$id_clube.'", "'.$periodo_mes.'", "'.$periodo_ano.'", "'.$licenca_uso['id'].'", "'.$licenca_uso['descricao'].'", "", "", "'.$licenca_uso['valor'].'", "'.$licenca_uso['ajuste'].'", "", "1", "1")';
    $fatura_valor_total = $licenca_uso['valor'] + $licenca_uso['ajuste'];


    // CLIENTES EXCEDENTES
    // ***********************************
    // Se houver controle de clientes excedentes
    if($clientes_excedente['id']>0 && $l['clientes_ativos_max']>0){

      // Número de Clientes Ativos
      try{
        $bdclube = new ConeccaoClube($l['bd']);

        $sql = $bdclube->getConexao()->prepare('
        SELECT *,
          count(DISTINCT id) as unicos,
          sum(CASE WHEN tipo = 1 THEN 1 ELSE 0 END) AS torneio,
          sum(CASE WHEN tipo = 2 THEN 1 ELSE 0 END) AS cash,
          sum(CASE WHEN tipo = 3 THEN 1 ELSE 0 END) AS bar,
          DATE_FORMAT(data_registro,"%m") AS mes,
          DATE_FORMAT(data_registro,"%Y") AS ano
        FROM (
          SELECT clientes.id, clientes.nome, cash.data_hora_abertura as data_registro, "1" as tipo
          FROM clientes
            LEFT JOIN (
                       SELECT id_jogador, id_cash, cash.data_hora_abertura
                       FROM cash_registros
                         LEFT JOIN cash on cash.id = cash_registros.id_cash
                       WHERE cash.data_hora_abertura BETWEEN (:data_ini) AND (:data_fim)
                       ORDER BY data_hora_abertura DESC
                     ) cash on cash.id_jogador = clientes.id
          WHERE cash.data_hora_abertura != ""
          UNION
          SELECT clientes.id, clientes.nome, torneio.data_hora_abertura as data_registro, "2" as tipo
          FROM clientes
            LEFT JOIN (
                       SELECT id_jogador, id_torneio, torneios.data_hora_abertura
                       FROM torneios_registros
                         LEFT JOIN torneios on torneios.id = torneios_registros.id_torneio
                       WHERE torneios.data_hora_abertura BETWEEN (:data_ini) AND (:data_fim)
                       ORDER BY data_hora_abertura DESC
                     ) torneio on torneio.id_jogador = clientes.id
          WHERE torneio.data_hora_abertura != ""
          UNION
          SELECT clientes.id, clientes.nome, bar.data_hora_abertura as data_registro, "3" as tipo
          FROM clientes
            LEFT JOIN (
                       SELECT id_cliente, bar_comandas.data_hora_abertura
                       FROM bar_comandas
                       WHERE bar_comandas.data_hora_abertura BETWEEN (:data_ini) AND (:data_fim)
                       ORDER BY data_hora_abertura DESC
                     ) bar on bar.id_cliente = clientes.id
          WHERE bar.data_hora_abertura != ""
          UNION
          SELECT clientes.id, clientes.nome, clientes.data_cadastro as data_registro, "4" as tipo
          FROM clientes
          WHERE clientes.data_cadastro BETWEEN (:data_ini) AND (:data_fim)
          ) total
        ORDER BY data_registro ASC, id ASC');
        $sql->bindValue(':data_ini', date("Y-m-21", strtotime("-7 months", strtotime('20'.$periodo_ano.'-'.$periodo_mes.'-21'))), PDO::PARAM_STR);
        $sql->bindValue(':data_fim', date("Y-m-20", strtotime("-1 months", strtotime('20'.$periodo_ano.'-'.$periodo_mes.'-20'))), PDO::PARAM_STR);
        if( $sql->execute() ) { $resul_cliente_ativos = $sql->fetch(PDO::FETCH_ASSOC);}

        $clientes_atv = $resul_cliente_ativos['unicos'];

      }catch(PDOException $e){$clientes_atv=0;}
      $bdclube = null;

      // SE HOUVER CLIENTES EXCEDENTES
      if($clientes_atv > $l['clientes_ativos_max']){
        // Para descrição do número de clientes ativos
        $ativos_data_ini = date("21/m/Y", strtotime("-7 months", strtotime('20'.$periodo_ano.'-'.$periodo_mes.'-21')));
        $ativos_data_fim = date("20/m/Y", strtotime("-1 months", strtotime('20'.$periodo_ano.'-'.$periodo_mes.'-20')));
        $obs_ativos = 'Ref. &agrave; '.$clientes_atv.' Clientes Ativos ('.$ativos_data_ini.' &agrave; '.$ativos_data_fim.')' ;

        $qtd = $clientes_atv - $l['clientes_ativos_max'];
        $valor_total = $qtd * $clientes_excedente['valor'];
        $valor_total_ajuste = $qtd * $clientes_excedente['ajuste'];

        $produtos_insert[] = '("'.$id_fatura.'", "'.$id_clube.'", "'.$periodo_mes.'", "'.$periodo_ano.'", "'.$clientes_excedente['id'].'", "'.$clientes_excedente['descricao'].'", "'.$qtd.'",  "'.$clientes_excedente['valor'].'", "'.$valor_total.'", "'.$valor_total_ajuste.'", "'.$obs_ativos.'", "1", "1")';
        $fatura_valor_total += $valor_total + $valor_total_ajuste;
      }

    }


    // OUTROS PRODUTOS DO PLANO (INCLUSÃO AUTOMÁTICA)
    // ***********************************
    if(count($outros_produtos)>0){
      foreach ($outros_produtos as $p){
        $produtos_insert[] = '("'.$id_fatura.'", "'.$id_clube.'", "'.$periodo_mes.'", "'.$periodo_ano.'", "'.$p['produto_id'].'", "'.$p['produto_descricao'].'", "", "", "'.$p['clube_valor'].'", "'.$p['clube_valor_ajuste'].'", "", "1", "1")';
        $fatura_valor_total += $p['clube_valor'] + $p['clube_valor_ajuste'];
      }
    }

    // CADASTRAR OS PRODUTOS
    if(count($produtos_insert)>0){
      $sql = $bd->getConexao()->prepare('INSERT INTO clubes_compras
      (id_fatura, id_clube, periodo_mes, periodo_ano, id_produto, descricao, qtd, valor_unit, valor, valor_ajuste, obs, produto_versao, status) VALUES '.implode(',', $produtos_insert));
      if($sql->execute()) ;
    }


    // DEFINIÇÃO DO VALOR FINAL DA FATURA

    // Se valor foi definido manualmente
    if($valor_especifico!=""){
      $fatura_valor_final = $valor_especifico;
      $fatura_valor_com_ads = 0;
    }else{
      $fatura_valor_total += $l['valor_produtos_manual'] + $l['ajuste_produtos_manual'];
      $fatura_valor_com_ads = $fatura_valor_total + $l['repasse_ads'];
      $fatura_valor_final = $fatura_valor_com_ads>=0 ? $fatura_valor_com_ads : 0;
    }

    // ATUALIZA A FATURA GERAL
    $sql = $bd->getConexao()->prepare('UPDATE clubes_financeiro SET
    valor = :valor,
    status = :status
    WHERE id = :id');
    $sql->bindValue(':valor', $fatura_valor_final, PDO::PARAM_STR);
    $sql->bindValue(':status', $fatura_valor_final==0 ? 4 : 1, PDO::PARAM_STR);
    $sql->bindValue(':id', $id_fatura, PDO::PARAM_STR);
    if($sql->execute());

    // ATUALIZAR PRODUTOS MANUAIS
    $sql = $bd->getConexao()->prepare('UPDATE clubes_compras SET
      id_fatura = :id_fatura,
      status = 1
    WHERE id_clube = :id_clube AND periodo_mes = :mes AND periodo_ano = :ano');
    $sql->bindValue(':id_fatura', $id_fatura, PDO::PARAM_STR);
    $sql->bindValue(':id_clube', $id_clube, PDO::PARAM_STR);
    $sql->bindValue(':mes', $periodo_mes, PDO::PARAM_STR);
    $sql->bindValue(':ano', $periodo_ano, PDO::PARAM_STR);
    if($sql->execute());

    // Insere novo produto se houver ads acima do teto
    if($fatura_valor_com_ads<0){
      $periodo_mes_proximo = $periodo_mes==12 ? 1 : $periodo_mes+1;
      $periodo_ano_proximo = $periodo_mes==12 ? $periodo_ano+1 : $periodo_ano;

      // ATUAL
      $descricao_atual = 'PW Ads - Valor excedente (para o mês '.$periodo_mes_proximo.'/20'.$periodo_ano_proximo.')';

      $sql = $bd->getConexao()->prepare('INSERT INTO clubes_compras
      (id_clube, periodo_mes, periodo_ano, id_fatura, id_produto, descricao, valor, valor_ajuste, produto_adicional, produto_versao, status) VALUES
      (:id_clube, :periodo_mes, :periodo_ano, :id_fatura, :id_produto, :descricao, :valor, :valor_ajuste, 1, 1, 0)');
      $sql->bindValue(':id_clube', $id_clube, PDO::PARAM_STR);
      $sql->bindValue(':periodo_mes', $periodo_mes, PDO::PARAM_STR);
      $sql->bindValue(':periodo_ano', $periodo_ano, PDO::PARAM_STR);
      $sql->bindValue(':id_fatura', $id_fatura, PDO::PARAM_STR);
      $sql->bindValue(':id_produto', $id_produto_ads, PDO::PARAM_STR);
      $sql->bindValue(':descricao', $descricao_atual, PDO::PARAM_STR);
      $sql->bindValue(':valor', $fatura_valor_com_ads*-1, PDO::PARAM_STR);
      $sql->bindValue(':valor_ajuste', 0, PDO::PARAM_STR);
      if($sql->execute());

      // PRÓXIMO
      $descricao_proximo = 'PW Ads - Valor excedente do mês '.$periodo_mes.'/20'.$periodo_ano;

      $sql = $bd->getConexao()->prepare('INSERT INTO clubes_compras
      (id_clube, periodo_mes, periodo_ano, id_produto, descricao, valor, valor_ajuste, produto_adicional, produto_versao, status) VALUES
      (:id_clube, :periodo_mes, :periodo_ano, :id_produto, :descricao, :valor, :valor_ajuste, 1, 1, 0)');
      $sql->bindValue(':id_clube', $id_clube, PDO::PARAM_STR);
      $sql->bindValue(':periodo_mes', $periodo_mes_proximo, PDO::PARAM_STR);
      $sql->bindValue(':periodo_ano', $periodo_ano_proximo, PDO::PARAM_STR);
      $sql->bindValue(':id_produto', $id_produto_ads, PDO::PARAM_STR);
      $sql->bindValue(':descricao', $descricao_proximo, PDO::PARAM_STR);
      $sql->bindValue(':valor', $fatura_valor_com_ads, PDO::PARAM_STR);
      $sql->bindValue(':valor_ajuste', 0, PDO::PARAM_STR);
      if($sql->execute());

    }


    // Insere valor de ads no caixa (se fatura for zerado e existir valor ads)
    if($fatura_valor_final==0 && $l['repasse_ads']<0){

      $sql = $bd->getConexao()->prepare('
      SELECT
        sum(ifnull(valor,0)) as total
      FROM clubes_compras
      WHERE id_clube = :id_clube AND id_fatura = :id_fatura AND id_produto = :id_produto_ads');
      $sql->bindValue(':id_clube', $id_clube, PDO::PARAM_STR);
      $sql->bindValue(':id_fatura', $id_fatura, PDO::PARAM_STR);
      $sql->bindValue(':id_produto_ads', $id_produto_ads, PDO::PARAM_STR);
      if($sql->execute()){ $valor_ads = $sql->fetch(PDO::FETCH_ASSOC);}

      $valor_ads = $valor_ads['total'];

      $id_categoria = 63;

      $sql = $bd->getConexao()->prepare('INSERT INTO fingeral_registros
      (id_categoria, id_conta, id_clube, id_clube_financeiro, valor, data_lancamento, data_vencimento, data_pagamento, periodo_mes, periodo_ano, pagar_automatico, status_pago, status) VALUES
      (:id_categoria, :id_conta, :id_clube, :id_clube_financeiro, :valor, NOW(), NOW(), NOW(), :periodo_mes, :periodo_ano, 1, 1, 0)');
      $sql->bindValue(':id_categoria', $id_categoria, PDO::PARAM_STR);
      $sql->bindValue(':id_conta', 1, PDO::PARAM_STR);
      $sql->bindValue(':id_clube', $id_clube, PDO::PARAM_STR);
      $sql->bindValue(':id_clube_financeiro', $id_fatura, PDO::PARAM_STR);
      $sql->bindValue(':valor', $valor_ads*-1, PDO::PARAM_STR);
      $sql->bindValue(':periodo_mes', $periodo_mes, PDO::PARAM_STR);
      $sql->bindValue(':periodo_ano', $periodo_ano, PDO::PARAM_STR);
      if( $sql->execute() );

      $id_categoria_ads = 141;

      $sql = $bd->getConexao()->prepare('INSERT INTO fingeral_registros
      (id_categoria, id_conta, id_clube, id_clube_financeiro, valor, data_lancamento, data_vencimento, data_pagamento, periodo_mes, periodo_ano, pagar_automatico, status_pago, status) VALUES
      (:id_categoria, :id_conta, :id_clube, :id_clube_financeiro, :valor, NOW(), NOW(), NOW(), :periodo_mes, :periodo_ano, 1, 1, 0)');
      $sql->bindValue(':id_categoria', $id_categoria_ads, PDO::PARAM_STR);
      $sql->bindValue(':id_conta', 1, PDO::PARAM_STR);
      $sql->bindValue(':id_clube', $id_clube, PDO::PARAM_STR);
      $sql->bindValue(':id_clube_financeiro', $id_fatura, PDO::PARAM_STR);
      $sql->bindValue(':valor', $valor_ads, PDO::PARAM_STR);
      $sql->bindValue(':periodo_mes', $periodo_mes, PDO::PARAM_STR);
      $sql->bindValue(':periodo_ano', $periodo_ano, PDO::PARAM_STR);
      if($sql->execute());
    }



  }

}