include_once (dirname(__DIR__).'/application/config.php');

$values = array();
$array = array();

$sql_request = "
      SELECT
        r.userId as user_id,
        CURRENT_TIMESTAMP as month_year,
        (SELECT spf.plan_value FROM settings_planning_figure AS spf WHERE DATE_FORMAT(spf.month,'%Y-%m') = '2019-01' AND spf.role = u.rules) AS plan,
        CAST(AVG(
          CASE
            WHEN r.reject_date IS NULL AND DATE_FORMAT(r.date_add, '%Y-%m') = '2019-01' THEN TIMESTAMPDIFF(MINUTE,r.date_add,r.approve_date)
            WHEN r.approve_date IS NULL AND DATE_FORMAT(r.date_add, '%Y-%m') = '2019-01' THEN TIMESTAMPDIFF(MINUTE,r.date_add,r.reject_date)
          END) AS DECIMAL(10,1)) AS fact,
        (SELECT COUNT(*)
          FROM (
            SELECT *
            FROM log_user_activity AS lua
            WHERE lua.user_id IN
               (SELECT u.id FROM users AS u WHERE u.rules IN ('21','25','29') AND
                  u.block_status = 0 AND
                  u.id != 120
               ) AND
                DATE_FORMAT(lua.action_date, '%Y-%m') = '2019-01'
                GROUP BY DATE_FORMAT(lua.action_date, '%Y-%m-%d'), lua.user_id
               ) AS count_days
         WHERE count_days.user_id = u.id
         GROUP BY userId) AS work_days
      FROM request AS r
      INNER JOIN users AS u ON u.id = r.userId
      WHERE r.userId IN(
              SELECT u.id
              FROM users AS u
              WHERE u.rules IN ('21','25','29') AND u.block_status = 0 AND u.id != 120) AND
              (CAST(r.date_add AS date) = CAST(r.reject_date AS date) OR CAST(r.date_add AS date) = CAST(r.approve_date AS date))
      GROUP BY r.userId";

$sql_cpf = "SELECT *
            FROM cron_planning_figure as cpf
            WHERE date_format(cpf.month_year,'%Y-%m') = date_format(CURRENT_DATE,'%Y-%m')";

$query_cpf  = mysql_query($sql_cpf,$connect_db);

while($result = mysql_fetch_assoc($query_cpf)){
    $array['date'] = $result['month_year'];
    $array['user_id'][] = $result['user_id'];
}

$query = mysql_query($sql_request,$connect_db);

while($result = mysql_fetch_assoc($query)) {

    $values['string'][] = "('$result[user_id]', '$result[month_year]',
                  '$result[plan]',    '$result[fact]', '$result[work_days]'
                 )";
    $values['user_id'][] = $result['user_id'];
}

if(date('Y-m',strtotime($array['date'])) == date('Y-m') || !empty(array_diff($values['user_id'],$array['user_id']))){
    $sql = "UPDATE";
}
else
{
    $sql = "INSERT INTO cron_planning_figure (user_id, month_year, plan, fact, work_days)
            VALUES "  .implode(",",$values['string']);
}