<?php

/* CLASS FILE
----------------------------------*/

class affiliates extends db {

  public $dt;
  public $settings;
  public $sys;
  private $csv_sep = ',';

  public function activate($id, $p) {
    db::db_query("UPDATE `" . DB_PREFIX . "affiliates` SET
    `pass`     = '" . mswPassHash(array('type' => 'add', 'pass' => $p)) . "',
	  `approved` = 'yes',
	  `en`       = 'yes',
    `sys1`     = '',
    `sys2`     = '',
    `sys3`     = 'passchange'
    WHERE `id` = '{$id}'
    ");
  }

  public function exportReferrals($fm) {
    $data = [];
    $csv  = '';
    $file = SYS_TEMP_DIR . 'export-referrals-affiliates-' . $fm['id'] . '.csv';
    $this->sys->fileclear(PATH . 'backup', '.csv');
    $sql = 'WHERE (`affid` = \'' . $fm['id'] . '\') ';
    if ($fm['q']) {
      $sql .= "AND (`referrer` LIKE '%" . db::db_escape($fm['q']) . "%')";
    }
    $Q  = db::db_query("SELECT *,
          (SELECT `name` FROM `" . DB_PREFIX . "products` WHERE `" . DB_PREFIX . "products`.`id` = `" . DB_PREFIX . "affiliates_tracking`.`product`) AS `productName`
          FROM `" . DB_PREFIX . "affiliates_tracking`
          $sql
          ORDER BY `count` DESC
          ");
    while ($RF = db::db_object($Q)) {
      $data[] = mswCSV(mswCD($RF->referrer)) . $this->csv_sep .
      mswCSV(mswCD($RF->productName)) . $this->csv_sep .
      mswCSV(mswNFM($RF->count));
    }
    if (!empty($data)) {
      $csv = $fm['header'] . mswNL() . implode(mswNL(), $data);
      mswPUT($file, $csv);
    }
    return array($csv, $file);
  }

  public function export($fm) {
    $data = [];
    $csv  = '';
    $file = SYS_TEMP_DIR . 'export-affiliates-' . $this->dt->ts() . '.csv';
    $this->sys->fileclear(PATH . 'backup', '.csv');
    if (isset($_GET['fname'])) {
      foreach (array('fname','lname','email','tsf','payinfo','notes','commtier','ip','code','sort') AS $sK) {
        switch($sK) {
          case 'tsf':
            if (isset($_GET[$sK],$_GET['tst']) && $this->dt->jstots($_GET[$sK]) > 0 && $this->dt->jstots($_GET['tst']) > 0) {
              $search_sql[] = (empty($search_sql) ? 'WHERE (' : 'AND (') . "DATE(FROM_UNIXTIME(`ts`)) >= '" . date('Y-m-d', $this->dt->jstots($_GET[$sK])) . "' AND DATE(FROM_UNIXTIME(`ts`)) <= '" . date('Y-m-d', $this->dt->jstots($_GET['tst'])) . "')";
            }
            break;
          case 'code':
            if (isset($_GET[$sK])) {
              $search_sql[] = (empty($search_sql) ? 'WHERE (' : 'AND (') . "`code` LIKE '%" . db::db_escape($_GET[$sK]) . "%')";
            }
            break;
          case 'sort':
            if (isset($_GET[$sK]) && in_array($_GET[$sK], array_keys($fm['sort']['options']))) {
              $fm['sort']['def'] = $_GET[$sK];
            }
            break;
          default:
            if (isset($_GET[$sK]) && $_GET[$sK]) {
              $search_sql[] = (empty($search_sql) ? 'WHERE (' : 'AND (') . "`" . $sK . "` LIKE '%" . db::db_escape($_GET[$sK]) . "%')";
            }
            break;
        }
      }
      if (empty($search_sql)) {
        return array($csv, $file);
      } else {
        $search_sql[] = "AND (`approved` = 'yes')";
        // Order by..
        switch((in_array($fm['sort']['def'], array_keys($fm['sort']['options'])) ? $fm['sort']['def'] : 'name_asc')) {
          case 'date_asc': $orderBy = '`ts`'; break;
          case 'date_desc': $orderBy = '`ts` DESC'; break;
          case 'name_asc': $orderBy = '`fname`,`fname`'; break;
          case 'name_desc': $orderBy = '`fname` DESC,`lname` DESC'; break;
          case 'email_asc': $orderBy = '`email`'; break;
          case 'email_desc': $orderBy = '`email` DESC'; break;
          case 'comm_asc': $orderBy = '`affCommissions` * 100'; break;
          case 'comm_desc': $orderBy = '`affCommissions` * 100 DESC'; break;
          case 'pay_asc': $orderBy = '`affPayments`  * 100'; break;
          case 'pay_desc': $orderBy = '`affPayments` * 100 DESC'; break;
          case 'referrals_asc': $orderBy = '`affReferrals`'; break;
          case 'referrals_desc': $orderBy = '`affReferrals` DESC'; break;
        }
        $Q  = db::db_query("SELECT *,
              (SELECT ROUND(SUM(`commtotal`),2) FROM `" . DB_PREFIX . "affiliates_commissions` WHERE `" . DB_PREFIX . "affiliates_commissions`.`affid` = `" . DB_PREFIX . "affiliates`.`id` AND `" . DB_PREFIX . "affiliates_commissions`.`en` = 'yes' AND `" . DB_PREFIX . "affiliates_commissions`.`approved` = 'yes') AS `affCommissions`,
              (SELECT ROUND(SUM(`payamount`),2) FROM `" . DB_PREFIX . "affiliates_payments` WHERE `" . DB_PREFIX . "affiliates_payments`.`affid` = `" . DB_PREFIX . "affiliates`.`id`) AS `affPayments`,
              (SELECT SUM(`count`) FROM `" . DB_PREFIX . "affiliates_tracking` WHERE `" . DB_PREFIX . "affiliates_tracking`.`affid` = `" . DB_PREFIX . "affiliates`.`id`) AS `affReferrals`
              FROM `" . DB_PREFIX . "affiliates`
              " . implode(mswNL(), $search_sql) . "
              ORDER BY " . $orderBy
              );
      }
    } else {
      $sql = 'WHERE `approved` = \'yes\' ';
      if ($fm['q']) {
        $sql .= "AND (`fname` LIKE '%" . db::db_escape($fm['q']) . "%' OR `lname` LIKE '%" . db::db_escape($fm['q']) . "%' OR `email` LIKE '%" . db::db_escape($fm['q']) . "%' OR `notes` LIKE '%" . db::db_escape($fm['q']) . "%')";
      }
      $Q  = db::db_query("SELECT *,
            (SELECT ROUND(SUM(`commtotal`),2) FROM `" . DB_PREFIX . "affiliates_commissions` WHERE `" . DB_PREFIX . "affiliates_commissions`.`affid` = `" . DB_PREFIX . "affiliates`.`id` AND `" . DB_PREFIX . "affiliates_commissions`.`en` = 'yes' AND `" . DB_PREFIX . "affiliates_commissions`.`approved` = 'yes') AS `affCommissions`,
            (SELECT ROUND(SUM(`payamount`),2) FROM `" . DB_PREFIX . "affiliates_payments` WHERE `" . DB_PREFIX . "affiliates_payments`.`affid` = `" . DB_PREFIX . "affiliates`.`id`) AS `affPayments`,
            (SELECT SUM(`count`) FROM `" . DB_PREFIX . "affiliates_tracking` WHERE `" . DB_PREFIX . "affiliates_tracking`.`affid` = `" . DB_PREFIX . "affiliates`.`id`) AS `affReferrals`
            FROM `" . DB_PREFIX . "affiliates`
            $sql
            ORDER BY `fname`,`lname`
            ");
    }
    while ($AF = db::db_object($Q)) {
      $due = mswFPCE($AF->affCommissions - $AF->affPayments);
      if ($due < 1) {
        $due = '0.00';
      }
      $data[] = mswCSV($this->dt->datedisplay($this->settings, $AF->ts)) . $this->csv_sep .
      mswCSV(mswCD($AF->fname . ' ' . $AF->lname)) . $this->csv_sep .
      mswCSV(mswCD($AF->email)) . $this->csv_sep .
      mswCSV(($AF->commtier ? $AF->commtier : $fm['lang'][0])) . $this->csv_sep .
      mswCSV(mswAff($this->settings, $AF)) . $this->csv_sep .
      mswCSV(mswFPCE($AF->affCommissions, true)) . $this->csv_sep .
      mswCSV(mswFPCE($AF->affPayments, true)) . $this->csv_sep .
      mswCSV(mswFPCE($due, true)) . $this->csv_sep .
      mswCSV(mswNFM($AF->affReferrals)) . $this->csv_sep .
      mswCSV($AF->notes);
    }
    if (!empty($data)) {
      $csv = $fm['header'] . mswNL() . implode(mswNL(), $data);
      mswPUT($file, $csv);
    }
    return array($csv, $file);
  }

  public function add($fm) {
    db::db_query("INSERT INTO `" . DB_PREFIX . "affiliates` (
    `ts`,
	  `code`,
	  `fname`,
	  `lname`,
	  `email`,
	  `pass`,
	  `ip`,
	  `commtier`,
    `payinfo`,
	  `notes`,
    `approved`,
	  `en`,
    `sys1`,
    `sys2`,
    `sys3`
    ) VALUES (
    '{$fm['ts']}',
	  '" . db::db_escape($fm['code']) . "',
	  '" . db::db_escape($fm['fname']) . "',
	  '" . db::db_escape($fm['lname']) . "',
	  '" . db::db_escape($fm['email']) . "',
	  '" . mswPassHash(array('type' => 'add', 'pass' => $fm['pass'])) . "',
	  '" . db::db_escape($fm['ip']) . "',
	  '" . ($fm['commtier'] ? preg_replace('/[^0-9.]/', '', $fm['commtier']) . '%' : '') . "',
	  '" . db::db_escape($fm['payinfo']) . "',
	  '" . db::db_escape($fm['notes']) . "',
    'yes',
	  '{$fm['en']}',
    '',
    '',
    'passchange'
    )");
    $ID = db::db_last_insert_id();
    return $ID;
  }

  public function update($fm) {
    db::db_query("UPDATE `" . DB_PREFIX . "affiliates` SET
    `ts`       = '{$fm['ts']}',
	  `code`     = '" . db::db_escape($fm['code']) . "',
	  `fname`    = '" . db::db_escape($fm['fname']) . "',
	  `lname`    = '" . db::db_escape($fm['lname']) . "',
	  `email`    = '" . db::db_escape($fm['email']) . "',
	  `pass`     = '" . ($fm['pass'] ? mswPassHash(array('type' => 'add', 'pass' => $fm['pass'])) : db::db_escape($fm['opass'])) . "',
	  `ip`       = '" . db::db_escape($fm['ip']) . "',
	  `commtier` = '" . ($fm['commtier'] ? preg_replace('/[^0-9.]/', '', $fm['commtier']) . '%' : '') . "',
    `payinfo`  = '" . db::db_escape($fm['payinfo']) . "',
	  `notes`    = '" . db::db_escape($fm['notes']) . "',
	  `en`       = '{$fm['en']}',
    `sys3`     = '" . ($fm['payinfo'] == '' ? 'passchange' : '') . "'
    WHERE `id` = '{$fm['id']}'
    ");
  }

  public function updatereferral($fm) {
    db::db_query("UPDATE `" . DB_PREFIX . "affiliates_tracking` SET
    `product`  = '{$fm['product']}',
	  `referrer` = '" . db::db_escape($fm['referrer']) . "',
	  `count`    = '{$fm['count']}'
    WHERE `id` = '{$fm['id']}'
    ");
  }

  public function import($fm) {
    $c = array(0, '', 0);
    try {
      if (is_uploaded_file($fm['file'])) {
        $r       = 0;
        $csv     = [];
        try {
          $handle  = fopen($fm['file'], 'r');
          if ($handle) {
            while (($data = fgetcsv($handle, 0, FIELDS_TERMINATED_BY, ENCLOSED_BY, ESCAPED_BY))!==false) {
              if (isset($data[1], $data[3])) {
                $code = (isset($data[0]) ? substr($data[0], 0, 250) : '');
                $fname = (isset($data[1]) ? substr($data[1], 0, 250) : '');
                $lname = (isset($data[2]) ? substr($data[2], 0, 250) : '');
                $email = (isset($data[3]) && mswEmVal($data[3]) == 'yes' ? $data[3] : '');
                $ip = (isset($data[4]) && filter_var($data[4], FILTER_VALIDATE_IP, FILTER_FLAG_NO_PRIV_RANGE | FILTER_FLAG_NO_RES_RANGE) !== false ? $data[4] : '');
                $commtier = (isset($data[5]) && $data[5] ? preg_replace('/[^0-9.%]/', '', $data[5]) : '');
                $payinfo = (isset($data[6]) ? $data[6] : '');
                $notes = (isset($data[7]) ? $data[7] : '');
                if ($fname && $email) {
                  db::db_query("INSERT INTO `" . DB_PREFIX . "affiliates` (
                  `code`,
                  `fname`,
                  `lname`,
                  `email`,
                  `ip`,
                  `commtier`,
                  `payinfo`,
                  `notes`
                  ) VALUES (
                  '" . db::db_escape($code) . "',
                  '" . db::db_escape($fname) . "',
                  '" . db::db_escape($lname) . "',
                  '" . db::db_escape($email) . "',
                  '" . db::db_escape($ip) . "',
                  '" . db::db_escape($commtier) . "',
                  '" . db::db_escape($payinfo) . "',
                  '" . db::db_escape($notes) . "'
                  )");
                  if (db::db_aff_rows() > 0) {
                    ++$r;
                  }
                }
              }
            }
          }
          fclose($handle);
        } catch(ErrorException $e) {
          Debugger::log($e->getMessage());
        }
        // Update affiliates that were imported..
        if ($r > 0) {
          $p = affiliates::password();
          db::db_query("UPDATE `" . DB_PREFIX . "affiliates` SET
          `ts`       = '" . $this->dt->ts() . "',
          `pass`     = '" . mswPassHash(array('type' => 'add', 'pass' => $p)) . "',
          `en`       = 'yes',
          `approved` = 'yes',
          `sys3`     = 'passchange'
          WHERE `ts` = '0'
          ");
          // Remove duplicates..
          db::db_query("DELETE `a` FROM `" . DB_PREFIX . "affiliates` AS `a`, `" . DB_PREFIX . "affiliates` AS `b`
          WHERE `a`.`id` > `b`.`id`
          AND `a`.`email` <=> `b`.`email`
          ");
          $d = db::db_aff_rows();
          if (file_exists($fm['file'])) {
            try {
              unlink($fm['file']);
            } catch(ErrorException $e) {
              Debugger::log($e->getMessage());
            }
          }
        }
        $c = array($r, $p, $d);
      }
    } catch(ErrorException $e) {
      Debugger::log($e->getMessage());
    }
    return $c;
  }
  
  public function deleteref($fm) {
    if (!empty($fm['ids'])) {
      db::db_query("DELETE FROM `" . DB_PREFIX . "affiliates_tracking` WHERE `id` IN(" . db::db_escape(implode(',', $fm['ids'])) . ")");
      db::db_truncate(array('affiliates_tracking'));
    }
  }

  public function delete($fm) {
    if (!empty($fm['ids'])) {
      db::db_query("DELETE FROM `" . DB_PREFIX . "affiliates` WHERE `id` IN(" . db::db_escape(implode(',', $fm['ids'])) . ")");
      db::db_query("DELETE FROM `" . DB_PREFIX . "affiliates_commissions` WHERE `affid` IN(" . db::db_escape(implode(',', $fm['ids'])) . ")");
      db::db_query("DELETE FROM `" . DB_PREFIX . "affiliates_payments` WHERE `affid` IN(" . db::db_escape(implode(',', $fm['ids'])) . ")");
      db::db_query("DELETE FROM `" . DB_PREFIX . "affiliates_tracking` WHERE `affid` IN(" . db::db_escape(implode(',', $fm['ids'])) . ")");
      db::db_truncate(array('affiliates','affiliates_commissions','affiliates_payments','affiliates_tracking'));
    }
  }

  public function search() {
    $ar  = [];
    $q   = db::db_query("SELECT `id`,CONCAT(`fname`,' ',`lname`) AS `name`,`email` FROM `" . DB_PREFIX . "affiliates`
           WHERE (`fname` LIKE '%" . db::db_escape($_GET['term'])  . "%'
             OR `lname` LIKE '%" . db::db_escape($_GET['term'])  . "%'
             OR `email` LIKE '%" . db::db_escape($_GET['term'])  . "%'
           )
           AND `en` = 'yes'
           AND `sys1` = ''
           ORDER BY `fname`,`lname`
           ");
    while ($A = db::db_object($q)) {
      $ar[] = array(
        'value' => $A->id,
        'label' => mswSH($A->name . ' (' . $A->email . ')')
      );
    }
    return $ar;
  }

  public function password($special = 'yes', $max = 0) {
    $pass = '';
    $a1 = range('A','Z');
    $a2 = range('a','z');
    $a3 = range(0, 9);
    $a4 = ($special == 'yes' ? array('[', ']', '&', '*', '(', ')', '#', '!', '%', '@', '^') : []);
    $sec = array_merge($a1, $a2, $a3, $a4);
    shuffle($sec);
    for ($i = 0; $i < count($sec); $i++) {
      $rand = rand(0, (count($sec) - 1));
      $char = $sec[$rand];
      $pass .= $char;
      if ($max > 0) {
        if ($max == ($i + 1)) {
          return $pass;
        }
      } else {
        if (((int) MIN_AUTO_PASS_LENGTH > 0 ? (int) MIN_AUTO_PASS_LENGTH : 10) == ($i + 1)) {
          return $pass;
        }
      }
    }
    return $pass;
  }

}

?>