<?php
// $Id$

/**
 * @file
 * Tracks affiliate referrals and commissions
 *
 * Uses role based authorization to track affiliate referrals
 * and affiliate commissions
 */

/**
 * Implementation of hook_help().
 */
function uc_affiliate_help($section = '') {
  switch ($section) {
    case 'admin/modules#description':
      return t('Tracks affiliate referrals and commissions');
      break;
    case 'admin/modules/affiliate':
      return t('Display user hierarchy.');
      break;
  }
}

/**
 * Implementation of hook_perm().
 */
function uc_affiliate_perm() {
  return array('act as affiliate', 'adjust user commissions'); // administer affiliates
}

/**
 * Implementation of hook_menu().
 */
function uc_affiliate_menu($may_cache) {
  if ($may_cache) {
    // $access = user_access('administer affiliates');
    $access = user_access('administer users') && user_access('act as affiliate', $account);
    $items[] = array(
      'path' => 'admin/store/affiliate',
      'title' => t('Affiliates'),
      'description' => t('Manage site affiliates'),
      'callback' => 'uc_affiliate_admin_manage',
      'access' => $access
    );
    $items[] = array(
      'path' => 'admin/store/affiliate/manage',
      'title' => t('Manage'),
      'type' => MENU_DEFAULT_LOCAL_TASK,
      'weight' => -10
    );
    $items[] = array(
      'path' => 'admin/store/affiliate/settings',
      'title' => t('Settings'),
      'callback' => 'drupal_get_form',
      'callback arguments' => array('uc_affiliate_admin_settings'),
      'access' => $access,
      'type' => MENU_LOCAL_TASK
    );
    $items[] = array(
      'path' => 'admin/store/affiliate/referral-fees',
      'title' => t('Referral Fees'),
	  'callback' => 'uc_affiliate_commissions_outstanding', // uc_affiliate_monthly_report
      'access' => $access,
      'type' => MENU_LOCAL_TASK
    );
	$items[] = array(
      'path' => 'admin/store/affiliate/referral-fees/outstanding',
      'title' => t('Outstanding'),
	  'callback' => 'uc_affiliate_commissions_outstanding',
      'access' => $access,
      'type' => MENU_DEFAULT_LOCAL_TASK,
	  'weight' => 0
    );
	$items[] = array(
      'path' => 'admin/store/affiliate/referral-fees/paid',
      'title' => t('Paid'),
	  'callback' => 'uc_affiliate_commissions_paid',
      'access' => $access,
      'type' => MENU_LOCAL_TASK,
	  'weight' => 1
    );
  }
  else {
    // This function sets $_SESSION['affiliate'] and increments click count
    uc_affiliate_click();
    // If user profile, add affiliate menus
    if (arg(0)=='user' && is_numeric(arg(1))) {
      global $user;
      $account = user_load(array('uid' => arg(1)));
      $access = (($user->uid == arg(1)) && user_access('act as affiliate', $account)) || (user_access('administer users') && user_access('act as affiliate', $account));
	  
	  // Affiliate information that only Admins can see
	  $access_admin = (user_access('administer users') && user_access('act as affiliate', $account));
      
	  $items[] = array(
        'path' => join('/', array('user', $account->uid, 'affiliate')),
        'title' => t('Affiliate Account'),
        'access' => $access,
        'callback' => 'uc_affiliate_commissions',
        'type' => MENU_LOCAL_TASK,
        'weight' => -9
      );
      $items[] = array(
        'path' => join('/', array('user', $account->uid, 'affiliate', 'commissions')),
        'title' => t('Referral History'),
        'access' => $access,
        'callback' => 'uc_affiliate_commissions',
        'type' => MENU_DEFAULT_LOCAL_TASK,
        'weight' => 0
      );
      $items[] = array(
        'path' => join('/', array('user', $account->uid, 'affiliate', 'clicks')),
        'title' => t('Click Count'),
        'access' => $access,
        'callback' => 'uc_affiliate_clicks',
        'type' => MENU_LOCAL_TASK,
        'weight' => 1
      );
      $items[] = array(
        'path' => join('/', array('user', $account->uid, 'affiliate', 'downline')),
        'title' => t('Affiliates'),
        'access' => $access, // was access_admin, but the downline is fixed so put it back to access Feb23/09
        'callback' => 'uc_affiliate_downline',
        'type' => MENU_LOCAL_TASK,
        'weight' => 2
      );
      $items[] = array(
        'path' => join('/', array('user', $account->uid, 'affiliate', 'upline')),
        'title' => t('Upline'),
        'access' => $access_admin,
        'callback' => 'uc_affiliate_upline',
        'type' => MENU_LOCAL_TASK,
        'weight' => 3
      );
    }
  }
  return $items;
}

/**
 * Define the admin settings form.
 */
function uc_affiliate_admin_settings() {
  $form['affiliate_hierarchy_depth'] = array(
    '#type' => 'textfield',
    '#title' => t('Affiliate hierarchy depth'),
    '#description' => t('Controls the depth to which affiliates referrals and commissions are tracked'),
    '#default_value' => variable_get('affiliate_hierarchy_depth', 5),
    '#size' => 3
  );
  $form['affiliate_commission_structure_web'] = array(
    '#type' => 'textfield',
    '#title' => t('Default Affiliate Commission Structure (Web)'),
    '#description' => t('Sets the default commission structure for orders created through the web (can be overridden on a per user basis). This should be a comma separated list of numbers with the first value corresponding to the first level in the hierarchy and each successive value corresponding to the next level in the hierarchy.  Example: 5,4,2,2,1'),
    '#default_value' => variable_get('affiliate_commission_structure_web', '5,3,2,2,1'),
    '#size' => 10
  );
  $form['affiliate_commission_structure_admin'] = array(
    '#type' => 'textfield',
    '#title' => t('Default Affiliate Commission Structure (Admin)'),
    '#description' => t('Sets the default commission structure for orders created through the administrator interface (can be overridden on a per user basis). This should be a comma separated list of numbers with the first value corresponding to the first level in the hierarchy and each successive value corresponding to the next level in the hierarchy.  Example: 5,4,2,2,1'),
    '#default_value' => variable_get('affiliate_commission_structure_admin', '5,3,2,2,1'),
    '#size' => 10
  );
  /*
  foreach (uc_order_status_list('general') as $status) {
    $statuses[$status['id']] = $status['title'];
  }
  $form['affiliate_commission_order_status'] = array(
    '#type' => 'select',
    '#title' => t('Order status'),
    '#default_value' => variable_get('affiliate_commission_order_status', 'completed'),
    '#description' => t('Where in the order status that commissions will be assigned.'),
    '#options' => $statuses,
  );
  */
  $form['affiliate_invalid_redirect'] = array(
    '#type' => 'textfield',
    '#title' => t('On an invalid affiliate URL, redirect to'),
    '#description' => t('When an invalid username is used as a subdomain, the user will be redirected to this URL.'),
    '#default_value' => variable_get('affiliate_invalid_redirect', '')
  );
  return system_settings_form($form);
}

/**
 * Validate the admin settings form
 */
function uc_affiliate_admin_settings_validate($form_id, $form_values) {
  $url = $form_values['affiliate_invalid_redirect'];
  if ($url != check_url($url) || strpos($url, '://')===false) {
    form_set_error('affiliate_invalid_redirect', t('Please enter a valid URL.'));
  }
}

/**
 * Define the affiliate users listing
 */
function uc_affiliate_admin_manage() {
  // $user_id = arg(3);
  // if ($user_id && is_numeric($user_id)) {
  //   return uc_affiliate_admin_users($user_id);
  // }

  // setup the table
  $header = array(
    array('data' => t('Username'), 'field' => 'u.name'),
    array('data' => t('Status'), 'field' => 'u.status'),
    array('data' => t('Member for'), 'field' => 'u.created', 'sort' => 'desc'),
    array('data' => t('Last access'), 'field' => 'u.access'),
    array('data' => t('Clicks'), 'field' => 'clicks'),
    array('data' => t('Sub-affiliates'), 'field' => ''), // 'field' => 'users'
    // array('data' => t('Orders'), 'field' => 'orders'),
  );
  
  // Select all users with the "act as affiliate" role, along with their click counts
  $sql = "SELECT u.uid, u.name, u.status, u.created, u.access, SUM(ac.clicks) AS clicks FROM {users} u INNER JOIN {users_roles} ur ON u.uid = ur.uid LEFT OUTER JOIN {uc_affiliate_counts} ac ON u.uid = ac.aid WHERE ur.rid IN (SELECT r.rid from {role} r INNER JOIN {permission} p ON r.rid = p.rid WHERE p.perm LIKE '%act as affiliate%') GROUP BY u.uid";
  $sql .= tablesort_sql($header);
  $result = db_query($sql);

  $status = array(t('blocked'), t('active'));
  
  while ($account = db_fetch_object($result)) {
    $sub_affs = 0;
	
	// Select all records in the "uc_affiliate_users" table that match the affiliate ID
	$result2 = db_query("SELECT uid FROM {uc_affiliate_users} WHERE aid = ".$account->uid);
	
	if ($result2) {
	  while ($row = db_fetch_array($result2)) {
	    // Load the user object and check to see if the user has the "act as affiliate" permission
		$loaded_user = user_load(array('uid' => $row['uid']));
		
		if ($loaded_user && user_access('act as affiliate', $loaded_user)) {
		  $sub_affs++;
		}
	  }
	}
    
    if ($sub_affs > 0) {
	  $sub_aff_link = l('Click to view', 'user/'.$account->uid.'/affiliate/downline').' ('.$sub_affs.')';
	} else {
	  $sub_aff_link = 'None';
	}
	
	$rows[] = array(
      // l($account->name, 'admin/store/affiliate/'. $account->uid),
      l($account->name, 'user/'.$account->uid.'/affiliate/commissions'),
	  $status[$account->status],
      format_interval(time() - $account->created),
      $account->access ? t('@time ago', array('@time' => format_interval(time() - $account->access))) : t('never'),
      $account->clicks ? $account->clicks : '0',
	  $sub_aff_link,
	  // $account->users ? $account->users : '0',
      // $account->orders ? $account->orders : '0',
    );
  }
  
  /*
  // define the query
  $sql = "SELECT u.uid, u.name, u.status, u.created, u.access, ac.time, SUM(ac.clicks) AS clicks, SUM(ac.users) AS users, SUM(ac.orders) AS orders FROM {users} u INNER JOIN {users_roles} ur ON u.uid = ur.uid LEFT OUTER JOIN {uc_affiliate_counts} ac ON u.uid = ac.aid WHERE ur.rid IN (SELECT r.rid from {role} r INNER JOIN {permission} p ON r.rid = p.rid WHERE p.perm LIKE '%act as affiliate%') GROUP BY u.uid";
  $sql .= tablesort_sql($header);
  $result = db_query($sql);

  $status = array(t('blocked'), t('active'));
  
  while ($account = db_fetch_object($result)) {
    if ($account->users > 0) {
	  $acc_users = l('Click to view', 'user/'.$account->uid.'/affiliate/downline').' ('.$account->users.')';
	} else {
	  $acc_users = 'None';
	}
	$rows[] = array(
      // l($account->name, 'admin/store/affiliate/'. $account->uid),
      l($account->name, 'user/'.$account->uid.'/affiliate/commissions'),
	  $status[$account->status],
      format_interval(time() - $account->created),
      $account->access ? t('@time ago', array('@time' => format_interval(time() - $account->access))) : t('never'),
      $account->clicks ? $account->clicks : '0',
	  $acc_users
	  // $account->users ? $account->users : '0',
      // $account->orders ? $account->orders : '0',
    );
  }
  */
  
  $output = "<p>" . t("Click on an affiliate user to view their monthly sales and commissions.");
  $output .= theme('table', $header, $rows);
  $output .= theme('pager', NULL, 50, 0);
  return $output;
}

/**
 * View an affiliate's sales and commissions.
 */
/* 
function uc_affiliate_admin_users($uid) {
  $monsel = arg(4);

  // setup the table
  $header = array(
    array('data' => t('Month'), 'field' => 'date'),
    array('data' => t('Sales'), 'field' => 'sales'),
    array('data' => t('Commissions'), 'field' => 'commissions'),
    array('data' => t('Paid (click to edit)'), 'field' => 'paid'),
  );
  
  // define the query
  $sql = 'SELECT ac.commission, uo.order_total, uo.created FROM {uc_affiliate_commission} AS ac LEFT OUTER JOIN {uc_orders} AS uo ON ac.order_id = uo.order_id WHERE ac.aid = %d';
  $result = db_query($sql, $uid);
  $user = user_load(array('uid' => $uid));

  $status = array(t('pending'), t('final'));
  $sumsales = array();
  $sumcomm = array();
  
  while ($rec = db_fetch_array($result)) {
    $darray = getdate($rec['created']);
    $mondate = mktime(0, 0, 0, $darray['mon'], 1, $darray['year']);
    $sumsales[$mondate] = isset($sumsales[$mondate]) ? $sumsales[$mondate]+$rec['order_total'] : $rec['order_total'];
    $sumcomm[$mondate] = isset($sumcomm[$mondate]) ? $sumcomm[$mondate]+$rec['commission'] : $rec['commission'];
  }
  
  foreach ($sumsales as $date => $sales) {
    // load current amount paid
    $amt = db_result(db_query('SELECT amount FROM {uc_affiliate_paid} WHERE aid=%d AND date=%d', $uid, $date));
    if ($amt===false) {
      $amt = 0;
    }
    $amt = uc_currency_format($amt);

    // format paid field as link or form
    if ($monsel && is_numeric($monsel) && $date==$monsel) {
      $pform['amount'] = array(
        '#type' => 'textfield',
        '#title' => t('Amount paid'),
        '#default_value' => $amt,
        '#size' => 8,
      );
      $pform['submit'] = array(
        '#type' => 'submit',
        '#value' => t('Save'),
      );
      $pform['#validate'] = array(
        // 'uc_affiliate_admin_users_paid_validate' => array($uid, $date)
		'uc_affiliate_admin_users_paid_validate' => array($uid)
      );
      $pform['#submit'] = array(
        // 'uc_affiliate_admin_users_paid_submit' => array($uid, $date)
		'uc_affiliate_admin_users_paid_submit' => array($uid)
      );
      // TODO check this alternative to using drupal_get_form
      drupal_process_form('uc_affiliate_'. $uid .'_paid_'. $date, $pform);
      drupal_prepare_form('uc_affiliate_'. $uid .'_paid_'. $date, $pform);
      $paid = drupal_render_form('uc_affiliate_'. $uid .'_paid_'. $date, $pform);
    }
    else {
      $paid = l($amt, join('/', array(arg(0), arg(1), arg(2), arg(3), $date)));
    }
    
    // generate record
    $rows[] = array(
      format_date($date, 'custom', 'M Y'),
      uc_currency_format($sales),
      uc_currency_format($sumcomm[$date]),
      $paid,
    );
  }
  
  $output = "<p>" . t("Displaying affiliate sales and commissions for "). theme('username', $user) .".";
  $output .= theme('table', $header, $rows);
  return $output;
}
*/

/**
 * Outstanding commissions report.
 */
function uc_affiliate_commissions_outstanding() {
  $uidsel = arg(4);
  $affiliates = array();
  
  // Prepare the table header
  $header = array(
    array('data' => t('Affiliate'), 'field' => 'aid'),
    array('data' => t('Referral Fees (USD)'), 'field' => 'outstanding_fees_usd'),
	array('data' => t('Referral Fees (CAD)'), 'field' => 'outstanding_fees_cad'),
	array('data' => t('Payments'), 'field' => 'paid')
  );
  
  // Select all users with the "act as affiliate" permission
  $sql = "SELECT DISTINCT u.uid, u.name FROM {users} u LEFT JOIN {users_roles} ur ON u.uid = ur.uid LEFT JOIN {permission} p ON ur.rid = p.rid WHERE u.uid != 0 AND (p.perm IS NOT NULL AND (p.perm LIKE '%act as affiliate%')) ORDER BY u.name ASC";
  $result = db_query($sql);
  
  if ($result) { 
	$i = 0;
    while ($row = db_fetch_array($result)) {
	  $affiliates[$i] = array();
	  $affiliates[$i]['uid'] = $row['uid'];
	  $affiliates[$i]['name'] = $row['name'];
	  
	  // Loop through the uc_affiliate_commission table and grab the amounts oweing from the most recent record
	  $result2 = db_query('SELECT outstanding_fees_usd, outstanding_fees_cad FROM {uc_affiliate_commission} WHERE aid = %d ORDER BY commission_id DESC LIMIT 1', $row['uid']);
	  
	  if ($result2) {
		while ($commission = db_fetch_array($result2)) { 
		  $affiliates[$i]['fees_usd'] = $commission['outstanding_fees_usd'];
	      $affiliates[$i]['fees_cad'] = $commission['outstanding_fees_cad'];
		}
	  } else {
		$affiliates[$i]['fees_usd'] = NULL;
		$affiliates[$i]['fees_cad'] = NULL;
	  }
	  $i++;
	}
  }
  
  // Prepare the table rows
  $rows = array();
  
  if (count($affiliates) > 0) {
    for ($i = 0; $i < count($affiliates); $i++) {
	  if ($affiliates[$i]['fees_usd'] > 0 || $affiliates[$i]['fees_cad'] > 0) {
		  
		$uid = $affiliates[$i]['uid'];
	  
	    // format paid field as link or form
        if ($uidsel && is_numeric($uidsel) && $uid == $uidsel) {
          $pform['currency'] = array(
            '#type' => 'select',
            '#title' => t('Currency'),
            '#options' => array(
			  'USD' => t('USD'),
			  'CAD' => t('CAD'),
		    ),
          );
		  $pform['amount'] = array(
            '#type' => 'textfield',
            '#title' => t('Amount'),
            '#default_value' => uc_currency_format(0.00),
            '#size' => 8,
          );
          $pform['submit'] = array(
            '#type' => 'submit',
            '#value' => t('Save'),
          );
          $pform['#validate'] = array(
		    'uc_affiliate_admin_users_paid_validate' => array($uid)
          );
          $pform['#submit'] = array(
		    'uc_affiliate_admin_users_paid_submit' => array($uid)
          );
	      $pform['#redirect'] = 'admin/store/affiliate/referral-fees/outstanding';
      
	      drupal_process_form('uc_affiliate_'. $uid .'_paid', $pform);
          drupal_prepare_form('uc_affiliate_'. $uid .'_paid', $pform);
          $paid = drupal_render_form('uc_affiliate_'. $uid .'_paid', $pform);
        }
        else {
	      $paid = l(t('Make payment'), join('/', array(arg(0), arg(1), arg(2), arg(3), $uid)));
        }
		  
		$rows[] = array(
          l($affiliates[$i]['name'], 'user/'.$affiliates[$i]['uid'].'/affiliate'),
          uc_currency_format(round($affiliates[$i]['fees_usd'], 2)),
		  uc_currency_format(round($affiliates[$i]['fees_cad'], 2)),
		  $paid
        );
      }
	}
  }
  
  $output = theme('table', $header, $rows);
  return $output;
}

/**
 * Validate the "admin users paid" form.
 */
function uc_affiliate_admin_users_paid_validate($form_id, $form_values) {
  $sign = variable_get('uc_currency_sign', '$');
  $amt = $form_values['amount'];
  if (!is_numeric($amt)) {
    $amt = trim($amt);
    if (substr($amt, 0, 1) == $sign) {
      $amt = substr($amt, 1);
    }
  }
  if (!is_numeric($amt) || $amt < 0) {
    form_set_error('amount', t('Please specify an amount paid to this affiliate.'));
  }
}

/**
 * Submit the "admin users paid" form.
 */
function uc_affiliate_admin_users_paid_submit($form_id, $form_values, $uid) {
  $sign = variable_get('uc_currency_sign', '$');
  $amt = $form_values['amount'];
  $currency = $form_values['currency'];
  
  if (!is_numeric($amt)) {
    $amt = trim($amt);
    if (substr($amt, 0, 1) == $sign) {
      $amt = substr($amt, 1);
    }
  }
  $user = user_load(array('uid' => $uid));
  
  // Select all commission records which belong to the user
  $result = db_query('SELECT commission FROM {uc_affiliate_commission} WHERE aid = '.$uid);
  $total = 0.00;
  
  // Calculate the user's commission total
  if ($result) {
    while ($commission = db_fetch_array($result)) {
	  $total += (float)$commission['commission'];
    }
  }
  // Update the database
  $note = t('Payment of @currency @amount for affiliate <a href="'.base_path().'user/@uid/affiliate">@name</a>.', array('@currency' => $currency, '@amount' => uc_currency_format($amt), '@uid' => $uid, '@name' => $user->name));
  $comm = -$amt;
  $comm_type = 'Payment';
  
  // Loop through the uc_affiliate_commission table and grab the amounts oweing from the most recent record
  $result2 = db_query('SELECT outstanding_fees_usd, outstanding_fees_cad FROM {uc_affiliate_commission} WHERE aid = %d ORDER BY commission_id DESC LIMIT 1', $uid);
  
  if ($result2) {
	while ($row = db_fetch_array($result2)) { 
	  $fees_usd = $row['outstanding_fees_usd'];
	  $fees_cad = $row['outstanding_fees_cad'];
	}
  } else {
	$fees_usd = NULL;
	$fees_cad = NULL;
  }
  
  // Determine which outstanding balance to apply the payment to (CAD or USD)
  if ($currency == 'CAD') {
	if (isset($fees_cad)) {
	  $fees_cad += $comm;
	}
  } else
  if ($currency == 'USD') {
	if (isset($fees_usd)) {
	  $fees_usd += $comm;
	}
  }
		  
  db_query("INSERT {uc_affiliate_commission} (aid, time, currency, commission, commission_type, commission_notes, outstanding_fees_usd, outstanding_fees_cad) VALUES (%d, %d, '%s', %f, '%s', '%s', %f, %f)", $uid, time(), $currency, $comm, $comm_type, $note, $fees_usd, $fees_cad);
  
  drupal_set_message(t('Processed payment of @currency @amount for @name on @date.', array('@currency' => $currency, '@amount' => uc_currency_format($amt), '@name' => $user->name, '@date' => format_date(time(), 'custom', 'M j Y'))));
  return 'admin/store/affiliate/referral-fees';
}

/**
 * Paid commissions report.
 */
function uc_affiliate_commissions_paid() {
  $uidsel = arg(4);
  $affiliates = array();
  
  // Prepare the table header
  $header = array(
    array('data' => t('Affiliate'), 'field' => 'aid'),
	array('data' => t('Payment'), 'field' => ''),
	array('data' => t('Date'), 'field' => 'time')
  );
  
  // Select all users with the "act as affiliate" permission
  $sql = "SELECT DISTINCT u.uid, u.name FROM {users} u LEFT JOIN {users_roles} ur ON u.uid = ur.uid LEFT JOIN {permission} p ON ur.rid = p.rid WHERE u.uid != 0 AND (p.perm IS NOT NULL AND (p.perm LIKE '%act as affiliate%')) ORDER BY u.name ASC";
  $result = db_query($sql);
  
  if ($result) { 
	$i = 0;
    while ($row = db_fetch_array($result)) {
	  $affiliates[$i] = array();
	  $affiliates[$i]['uid'] = $row['uid'];
	  $affiliates[$i]['name'] = $row['name'];
	  $affiliates[$i]['commissions'] = array();
	  
	  // Select all commission records which belong to a given user
      $result2 = db_query('SELECT aid, time, currency, commission, commission_type FROM {uc_affiliate_commission} WHERE aid = '.$row['uid']);
	
	  if ($result2) {
	    $ii = 0;  
		while ($commission = db_fetch_array($result2)) {
		  if ($commission['commission_type'] == 'Payment') {
		    $affiliates[$i]['commissions'][$ii]['aid'] = $commission['aid'];
		    $affiliates[$i]['commissions'][$ii]['time'] = $commission['time'];
			$affiliates[$i]['commissions'][$ii]['currency'] = $commission['currency'];
			$affiliates[$i]['commissions'][$ii]['commission'] = $commission['commission'];
		    $affiliates[$i]['commissions'][$ii]['commission_type'] = $commission['commission_type'];
		    $ii++;
		  }
		}
	  }
	  $i++;
	}
  }
  
  // Prepare the table rows
  $rows = array();
  
  if (count($affiliates) > 0) {
    for ($i = 0; $i < count($affiliates); $i++) {
	  for ($ii = 0; $ii < count($affiliates[$i]['commissions']); $ii++) {
		$rows[] = array(
          l($affiliates[$i]['name'], 'user/'.$affiliates[$i]['uid'].'/affiliate/commissions'),
          uc_currency_format(abs($affiliates[$i]['commissions'][$ii]['commission'])).' '.$affiliates[$i]['commissions'][$ii]['currency'],
		  // format_date($affiliates[$i]['commissions'][$ii]['time'], 'custom', 'M j Y')
		  format_date($affiliates[$i]['commissions'][$ii]['time'], 'custom', 'Y-m-d, g:i a')
        );
	  }
	}
  }
  
  $output = theme('table', $header, $rows);
  return $output;
}

/*
 * Increment affiliate counts.
 */
function uc_affiliate_add_count($aff, $clicks = 0, $users = 0, $orders = 0) {
  $time = uc_affiliate_current_time();
  
  if ($clicks > 0) {
    db_query('INSERT INTO {uc_affiliate_clicks} (aid, time) VALUES (%d, %d)', $aff, $time);
  }
  
  $res = db_fetch_object(db_query('SELECT * FROM {uc_affiliate_counts} WHERE aid=%d AND time=%d', $aff, $time));
  if ($res && $res->time) {
    db_query('UPDATE {uc_affiliate_counts} SET clicks=(clicks+%d), users=(users+%d), orders=(orders+%d) WHERE aid=%d AND time=%d', $clicks, $users, $orders, $aff, $time);
  }
  else {
    db_query('INSERT {uc_affiliate_counts} (aid, clicks, users, orders, time) VALUES (%d, %d, %d, %d, %d)', $aff, $clicks, $users, $orders, $time);
  }
}

/**
 * Implementation of hook_user().
 */
function uc_affiliate_user($op, &$edit, &$user, $category = NULL) {
  switch ($op) {
    case 'insert':
      // update user count for affiliate and associate user with affiliate
      $aff = $_SESSION['affiliate'];
      if ($aff) {
        db_query('INSERT INTO {uc_affiliate_users} (aid, uid) VALUES (%d, %d)', $aff, $user->uid);
        uc_affiliate_add_count($aff, 0, 1, 0);
        watchdog('user', t('User %name referred by user id %affiliate', array('%name' => theme('username', $user), '%affiliate' => theme('placeholder', $aff))));
      }
      break;
    case 'view':
      if (user_access('act as affiliate', $user)) {
        $items['affiliate'] = array(
          'value' => t('Manage your business '). l(t('here'), "user/{$user->uid}/affiliate"),
          'class' => 'affiliate'
        );
        return array(t('Affiliates') => $items);
      }
      break;
	case 'form':
      if (user_access('adjust user commissions')) {
        $form['commission_settings'] = array(
          '#type' => 'fieldset',
          '#title' => t('Affiliate Commission Settings'),
          '#collapsible' => TRUE,
          '#weight' => 10,
        );
        $form['commission_settings']['commission_web'] = array(
          '#type' => 'textfield',
          '#title' => t('Commission (Web)'),
          '#default_value' => $edit['commission_web'],
          '#description' => t('The commission percentages for this user (comma-separated numbers), when an order is created through the website. Leave blank to use the global commission percentages.')
        );
		$form['commission_settings']['commission_admin'] = array(
          '#type' => 'textfield',
          '#title' => t('Commission (Admin)'),
          '#default_value' => $edit['commission_admin'],
          '#description' => t('The commission percentages for this user (comma-separated numbers), when an order is created through the administrator interface. Leave blank to use the global commission percentages.')
        );
      }
      return $form;
      break;
  }
}

/*
 * Display affiliate commission records.
 */
function uc_affiliate_commissions() {
  global $user;
  $account = user_load(array('uid' => arg(1)));

  $access = (($user->uid == arg(1)) && user_access('act as affiliate', $account)) || (user_access('administer users') && user_access('act as affiliate', $account));

  if (user_access('administer users', $user) && user_access('act as affiliate', $user)) {
    drupal_set_title(t($account->name . '\'s Referral History'));
  } else
  if ($user->uid == arg(1) && user_access('act as affiliate', $user)) {
    drupal_set_title(t('My Referral History'));
  }

  $account = user_load(array('uid' => arg(1)));
  
  // Setup the table
  $header = array(
    array('data' => t('Log Date')),
	array('data' => t('Notes')),
    array('data' => t('Order')),
	array('data' => t('Customer')),
	array('data' => t('Web or Phone')),
	array('data' => t('Currency')),
	array('data' => t('Order Amount')),
	array('data' => t('Referral Fee')),
	array('data' => t('Payment Amount')),
	array('data' => t('Balance (USD)')),
	array('data' => t('Balance (CAD)'))
  );

  $sql = 'SELECT * FROM {uc_affiliate_commission} WHERE aid = '. $account->uid;
  $sql_count = 'SELECT COUNT(*) FROM {uc_affiliate_commission} WHERE aid = '. $account->uid;
  $result = pager_query($sql, 50, 0, $sql_count);
  
  while ($commission = db_fetch_array($result)) {
	$order_id = "-";
	$order_name = "-";
	$order_currency = "-";
	$order_web_phone = "-";
	$fees_usd = uc_currency_format(round($commission['outstanding_fees_usd'], 2));
	$fees_cad = uc_currency_format(round($commission['outstanding_fees_cad'], 2));
	
	// Order ID, name and country
	if ($commission['order_id'] != NULL) {
	  $order_id = l($commission['order_id'], 'admin/store/orders/'.$commission['order_id']);
	  
	  $result2 = db_query('SELECT uc.billing_first_name, uc.billing_last_name, uc.billing_country FROM {uc_orders} uc JOIN {users} u ON uc.uid = u.uid WHERE uc.order_id = %d', $commission['order_id']);
	  
	  if ($result2) {
		while ($row = db_fetch_array($result2)) {
		  $order_name = ucfirst($row['billing_first_name']).' '.ucfirst($row['billing_last_name']);
		  
		  // Translate the country code into a currency code (we're only checking for 2 countries here!)
		  if ($row['billing_country'] != 124) {
		    $order_currency = "USD";
		  } else {
		    $order_currency = "CAD";
		  }
		  
		  $result3 = db_query('SELECT category_id FROM {wb_orders_category} WHERE order_id = %d', $commission['order_id']);
		  
		  if ($result3) {
			while ($category = db_fetch_array($result3)) {
			  switch ($category['category_id']) {
			    // These values are defined in the wb_orders module
				case 1: $cat = "Web"; break;
				case 2: $cat = "Phone"; break;
				case 3: $cat = "Store"; break;
				case 4: $cat = "Affiliate"; break;
				case 5: $cat = "Reseller 1"; break;
				case 6: $cat = "Reseller 2"; break;
				case 7: $cat = "Reseller 3"; break;
				case 8: $cat = "Reseller 4"; break;
			  }
			  
			  if ($order_web_phone == "-") {
			    $order_web_phone = $cat;
			  } else {
			    $order_web_phone .= ', '.$cat;
			  }
		    }
		  }
		  
		}
	  }
	}
	
	// Order sub-total
	if ($commission['order_sub_total'] == NULL) {
	  $sub_total = "-";
	} else {
	  $sub_total = uc_currency_format(round($commission['order_sub_total'], 2));
	}
	
	// Commissions and payments
	if ($commission['commission_type'] == 'Payment') {
	  $commission_amt = "-";
      $payment = uc_currency_format(abs(round($commission['commission'], 2)));
	} else {
	  $commission_amt = uc_currency_format(round($commission['commission'], 2));
	  $payment = "-";
	}
    
	// Prepare table rows
	if ($commission['commission_type']) {
	  $row_class = str_replace(" ", "-", strtolower($commission['commission_type']));
	} else {
	  $row_class = "";
	}
	
	$rows[$commission['time']] = array(
	  'data' => array(
	    format_date($commission['time'], 'custom', 'Y-m-d, g:i a'),
		$commission['commission_notes'],
	    $order_id,
	    $order_name,
		$order_web_phone,
	    $order_currency,
        $sub_total,
        $commission_amt,
	    $payment,
	    $fees_usd,
		$fees_cad
	  ),
	  'class' => $row_class
    );
  }
  
  if($rows) {
		krsort($rows);
  }
  
  /*
  // Totals (below the table)
  $total_sales = 0;
  $total_commission = 0;
  
  $sql = 'SELECT order_sub_total, commission FROM {uc_affiliate_commission} WHERE aid = '. $account->uid;
  $result = db_query($sql);
  
  if ($result) {
    while ($commission = db_fetch_array($result)) {
      $total_sales += round($commission['order_sub_total'], 2);
      $total_commmission += round($commission['commission'], 2);
    }
  }
  */
  
  $output = theme('table', $header, $rows);
  $output .= theme('pager');
  
  /*
  $output .= '<div class="totals">';
  if ($total_sales) {
    $output .= '<p class="sales">'.t('Total sales').': '.uc_currency_format(round($total_sales, 2)).'</p>';
  }
  if ($total_commmission) {
    $output .= '<p class="commmission">'.t('Referral fees').': '.uc_currency_format(round($total_commmission, 2)).'</p>';
  }
  $output .= '</div>';
  */
  
  // $output .= drupal_get_form('uc_affiliate_time_selection_form');
  
  return $output;
}

/*
 * Display affiliate clicks.
 */
function uc_affiliate_clicks() {
  global $user;
  $account = user_load(array('uid' => arg(1)));

  $access = (($user->uid == arg(1)) && user_access('act as affiliate', $account)) || (user_access('administer users') && user_access('act as affiliate', $account));

  if (user_access('administer users', $user) && user_access('act as affiliate', $user)) {
    drupal_set_title(t($account->name . '\'s Click count'));
  } else
  if ($user->uid == arg(1) && user_access('act as affiliate', $user)) {
    drupal_set_title(t('My Click count'));
  }
  
  $account = user_load(array('uid' => arg(1)));
  
  // setup the table
  $header = array(
    array('data' => t('Date'), 'field' => "click_date", 'sort' => 'desc'),
    array('data' => t('Visits'), 'field' => "clicks"),
  );
  
  // Create sql to display level of affiliate specified
  $dates = uc_affiliate_predefined_range(arg(4));
  $sql = "SELECT unix_timestamp(from_unixtime(time, '%%Y-%%m-%%d')) as click_date, count(click_id) as clicks from {uc_affiliate_clicks} WHERE aid = " . $account->uid . " AND time between " . $dates['start'] . " AND " . $dates['end'] . " GROUP BY click_date";
  
  $sql .= tablesort_sql($header);
  $result = db_query($sql);
  
  $total_clicks = 0;
  while ($click_day = db_fetch_array($result)) {
    $rows[] = array(format_date($click_day['click_date'], 'custom', 'n-j-Y'), $click_day['clicks']);
    $total_clicks += $click_day['clicks'];
  }
  $rows[] = array(t('Total:'), $total_clicks);
  
  // Build the output
  // global $pager_total_items;

  $output = theme('table', $header, $rows);
  $output .= drupal_get_form('uc_affiliate_time_selection_form');
  
  return $output;
}

/*
 * Display affiliate's downline.
 */
function uc_affiliate_downline($level = 1) {
  global $user;
  $account = user_load(array('uid' => arg(1)));
    
  // $access = (($user->uid == arg(1)) && user_access('act as affiliate', $account)) || (user_access('administer users') && user_access('act as affiliate', $account));

  if (user_access('administer users', $user) && user_access('act as affiliate', $user)) {
    drupal_set_title(t($account->name . '\'s Affiliates'));
  } else
  if ($user->uid == arg(1) && user_access('act as affiliate', $user)) {
    drupal_set_title(t('My Affiliates'));
  }

  // make sure user doesn't try to manipulate depth
  if ($level > variable_get('affiliate_hierarchy_depth', 5)) {
    $level = variable_get('affiliate_hierarchy_depth', 5);
  }
  
  // setup the table
  $header = array(
    array('data' => t('Affiliate'), 'field' => "u$level.name"),
    // array('data' => t('Act as affiliate'), 'field' => "u$level.status"),
    array('data' => t('Member for'), 'field' => "u$level.created", 'sort' => 'desc'),
    // array('data' => t('Last access'), 'field' => "u$level.access"),
    // array('data' => t('Level'), 'field' => "u$level.level"),
    array('data' => t('Customers'), 'field' => "uc.user_count")
  );
  
  // Create sql to display level of affiliate specified
  $sql = "SELECT u1.uid, uc.user_count FROM {users} u1 ";
  for ($i = 1; $i <= $level; $i++) {
    $next = $i+1;
    $sql .= "INNER JOIN {uc_affiliate_users} au$i ON au$i.uid = u$i.uid ";
    $sql .= "INNER JOIN {users} u$next ON au$i.aid = u$next.uid ";
  }
  $sql .= "LEFT OUTER JOIN (SELECT au.aid, COUNT(au.uid) as user_count FROM {uc_affiliate_users} au GROUP BY au.aid) uc on uc.aid = u1.uid ";
  $sql .= "WHERE u$next.uid = " . $account->uid;
  $sql .= tablesort_sql($header);
  $result = pager_query($sql, 25);
  
  // create the data rows for our table
  while ($affiliate = db_fetch_object($result)) {
    $loaded_user = user_load(array('uid' => $affiliate->uid));
	
	// Only print records where "act as affiliate" is "Yes" (it would be more efficient to filter these records out in the above SQL query)
	if (user_access('act as affiliate', $loaded_user) == 'Yes') {
      $rows[] = array(
        l($loaded_user->name, 'affiliate/'.$loaded_user->name.'/account'),
        // user_access('act as affiliate', $loaded_user) ? 'Yes' : 'No',
        format_interval(time() - $loaded_user->created),
        // $loaded_user->access ? t('%time ago', array('%time' => format_interval(time() - $loaded_user->access))) : t('never'),
        // $level,
        $affiliate->user_count ? $affiliate->user_count : 0
      );
	}
  }
  
  /*
  // Build the output
  $output = '<ul class="tabs secondary">';
  for ($i=1; $i<=variable_get('affiliate_hierarchy_depth', 5); $i++) {
    $output .= ($i == $level ? '<li class="active">' : '<li>');
    $output .= l(t("Level") . " " . $i, 'user/' . $account->uid . '/affiliate/downline/' . $i) . "</li>";
  }
  $output .= "</ul>";
  */
  
  // global $pager_total_items;
  // $output .= '<h2>' . t('Total Level !level, Customers: !customers', array('!level' => $level, '!customers' => $pager_total_items[0])) . '</h2>';
  
  if (user_access('administer users', $user) && user_access('act as affiliate', $user)) {
    $output = '<h2>' . t($account->name.' has referred !affiliates affiliates', array('!affiliates' => count($rows))) . '</h2>';
  } else
  if ($user->uid == arg(1) && user_access('act as affiliate', $user)) {
    $output = '<h2>' . t('I have referred !affiliates affiliates', array('!affiliates' => count($rows))) . '</h2>';
  }
  
  // Output as definition list
  $output .= '<dl>';
  for ($i = 0; $i < count($rows); $i++) {
    $output .= '<dt>'.$rows[$i][0].'</dt>';
	$output .= '<dd>'.t('Affiliate for ').$rows[$i][1].'</dd>';
	/*
	if ($rows[$i][2] > 0) {
	  $output .= '<dd>Has referred '.$rows[$i][2].' customers</dd>'; // How is the user_count number being generated? (SQL is too complex for me)
	}
	*/
  }
  $output .= '</dl>';
  
  // Output as table
  // $output .= theme('table', $header, $rows);
  
  $output .= theme('pager', NULL, 50, 0);

  return $output;
  // return '<p>Affiliate downline is currently unavailable.</p>'; 
}

/*
 * Display affiliate's upline.
 */
function uc_affiliate_upline() {
  global $user;
  $account = user_load(array('uid' => arg(1)));

  $access = (($user->uid == arg(1)) && user_access('act as affiliate', $account)) || (user_access('administer users') && user_access('act as affiliate', $account));

  if (user_access('administer users', $user) && user_access('act as affiliate', $user)) {
	drupal_set_title(t($account->name . '\'s Upline'));
  } else
  if ($user->uid == arg(1) && user_access('act as affiliate', $user)) {
	drupal_set_title(t('My Upline'));
  }
  
  $account = user_load(array('uid' => arg(1)));
  
  // setup the table
  $header = array(
    array('data' => t('Level')),
    array('data' => t('Username')),
    array('data' => t('Member for')),
    array('data' => t('Last access')),
    array('data' => t('Customers')),
  );
  
  // Create sql to display user's upline
  $level = variable_get('affiliate_hierarchy_depth', 5);
  $rows = array();
  $next_uid = $account->uid;
  for ($i = 1; $i <= $level; $i++) {
    $sql = "SELECT u.uid, u.name, u.status, u.created, u.access, uc.user_count, " . $i . " AS level ";
    $sql .= "FROM {users} u INNER JOIN {uc_affiliate_users} au ON au.aid = u.uid ";
    $sql .= "LEFT OUTER JOIN (SELECT au2.aid, COUNT(au2.uid) as user_count FROM {uc_affiliate_users} au2 GROUP BY au2.aid) uc on uc.aid = u.uid ";
    $sql .= "WHERE au.uid = " . $next_uid;
    
    // create the data rows for our table
    $result = db_query($sql);
    $status = array(t('blocked'), t('active'));
    while ($affiliate = db_fetch_object($result)) {
      $next_uid = $affiliate->uid;
      $rows[] = array(
        $affiliate->level,
        theme('username', $affiliate),
        format_interval(time() - $affiliate->created),
        $affiliate->access ? t('%time ago', array('%time' => format_interval(time() - $account->access))) : t('never'),
        $affiliate->user_count ? $affiliate->user_count : 0
      );
    }
  }
  
  if (count($rows) > 0) {
    $output = theme('table', $header, $rows);
  }
  else {
    $output = "<p>" . t("You are a top level affiliate.  There are no affiliates in your upline.") . "</p>";
  }
  return $output;
}

/*
 * Implementation of hook_cart_item().
 * Assigns the affiliate ID to the cart items (before the customer has completed checkout).
 */
function uc_affiliate_cart_item($op, &$item) {
  switch ($op) {
    case 'load':
      if (!empty($_SESSION['affiliate'])) {
        $item->data['affiliate'] = $_SESSION['affiliate'];
      }
      break;
  }
}

/*
 * Implementation of hook_order().
 * Tracks affiliate commissions.
 */
function uc_affiliate_order($op, &$arg1, $arg2) {
  switch ($op) {
  
      case 'save':
		  
		  // The affiliate ID is initially assigned to the order's products in hook_cart_item(), before the customer has completed checkout.
		  // The following code is executed when an existing order has been edited by an admin (via the "admin/store/orders/[order_id]/edit" form).
		  
		  // If the affiliate selection form has been submitted (see uc_affiliate_order_pane_assign)
		  if (isset($_POST['affiliate_select']) && is_numeric($_POST['affiliate_select'])) { // ($form_values['affiliate_select'] doesn't seem to be working)
		      // Get the ID of the new affiliate
		      if ($_POST['affiliate_select'] == 0) {
				  $new_affiliate = NULL;
		      } else {
				  $new_affiliate = $_POST['affiliate_select'];
			  }
			  // Update the product's data field with the new affiliate information
			  for ($i = 0; $i < count($arg1->products); $i++) {
				  $arg1->products[$i]->data['affiliate'] = $new_affiliate;
				  db_query("UPDATE {uc_order_products} SET data = '%s' WHERE order_id = %d AND order_product_id = %d", serialize($arg1->products[$i]->data), $arg1->order_id, $arg1->products[$i]->order_product_id);
		      }
		  }
          break;
		  
		  /*
		  // Find all products included in the order
		  $result = db_query('SELECT order_product_id, data FROM {uc_order_products} WHERE order_id = %d', $arg1->order_id);
			  
		  if ($result) {
			  while ($row = db_fetch_array($result)) {
				  $data = unserialize($row['data']);
				  
				  // If a new affiliate is being assigned to the order (see hook_order_pane: edit-process), Update the product's data field
				  if (defined('UC_AFFILIATE_ORDER_NEW_AFFILIATE') && $data['affiliate'] != UC_AFFILIATE_ORDER_NEW_AFFILIATE) {
					  $data['affiliate'] = UC_AFFILIATE_ORDER_NEW_AFFILIATE;
				      $data_string = serialize($data);
				      db_query("UPDATE {uc_order_products} SET data = '%s' WHERE order_id = %d AND order_product_id = %d", $data_string, $arg1->order_id, $row['order_product_id']);    
				  }
			  }
		  }
		  */
	  
	  case 'update':
	      
		  // Write commission records
		  
		  $old_status = $arg1->order_status;
		  $new_status = $arg2;
		  
		  // Get the title of the new status
		  $statuses = uc_order_status_list();
		  for ($i = 0; $i < count($statuses); $i++) {
		      if ($statuses[$i]['id'] == $new_status) {
			      $new_status_title = $statuses[$i]['title'];
			  }
		  }
		  
		  // If the order state has changed
		  if ($new_status != $old_status) {
		      // Determine which action to take, based on the order status
			  if ($new_status == 'completed') {
			      uc_affiliate_apply_commission($arg1, 'Order: '.$new_status_title);
			  } else
			  if ($old_status == 'completed') {
			      uc_affiliate_revoke_commission($arg1, 'Order: '.$new_status_title);
			  }
		  }
		  break;
		  
		  /*
		  // If the order status has changed
		  if ($arg2) {
			  // Determine which order state is associated with the order status
			  // (Is there an existing function that can do this automatically? uc_order_status_data?)
			  $statuses = uc_order_status_list();
			  
			  for ($i = 0; $i < count($statuses); $i++) {
				  if ($statuses[$i]['id'] == $arg1->order_status) {
					  $old_state = $statuses[$i]['state'];
				  }
				  if ($statuses[$i]['id'] == $arg2) {
					  $new_state = $statuses[$i]['state'];
				  }
			  }
			  // If the order state has changed
			  if ($new_state != $old_state) {
				  // Determine which action to take, based on the order state
				  if ($new_state == 'completed') {
					  uc_affiliate_apply_commission($arg1);
				  } else
				  if ($new_state == 'canceled') {
					  uc_affiliate_revoke_commission($arg1);
				  }
			  }
		  // If the order status has not changed
		  } else {
			  // If the affiliate selection form has been submitted (see uc_affiliate_order_pane_assign)
			  if (isset($_POST['affiliate_select']) && is_numeric($_POST['affiliate_select'])) { // ($form_values['affiliate_select'] doesn't seem to be working)
				  // Get the ID of the new affiliate
				  if ($_POST['affiliate_select'] == 0) {
				      $new_affiliate = NULL;
				  } else {
				      $new_affiliate = $_POST['affiliate_select'];
				  }
				  // Find all products included in the order
				  $result = db_query('SELECT order_product_id, data FROM {uc_order_products} WHERE order_id = %d', $arg1->order_id);
					  
				  if ($result) {
					  while ($row = db_fetch_array($result)) {
						  $data = unserialize($row['data']);
						  
						  // If the product already has an affiliate associated with it, get the affiliate ID
						  if (isset($data['affiliate']) && is_numeric($data['affiliate'])) {
						      $old_affiliate = $data['affiliate'];
						  }
						  // If the product does not have an affiliate associated with it, or if and a different affiliate is being assigned to the product, update the product's data field
						  if (!isset($old_affiliate) || isset($old_affiliate) && $old_affiliate != $new_affiliate) {
							  $data['affiliate'] = $new_affiliate;
							  $data_string = serialize($data);
							  db_query("UPDATE {uc_order_products} SET data = '%s' WHERE order_id = %d AND order_product_id = %d", $data_string, $arg1->order_id, $row['order_product_id']);
						  }
					  }
				  }
				  // Determine which order state is associated with the order status
				  // (Is there an existing function that can do this automatically? uc_order_status_data?)
				  $statuses = uc_order_status_list();
				  
				  for ($i = 0; $i < count($statuses); $i++) {
					  if ($statuses[$i]['id'] == $arg1->order_status) {
						  $state = $statuses[$i]['state'];
					  }
				  }
				  // If the order is in one of the completed states
				  if ($state == 'completed') {
					  // If a different affiliate is being assigned to the order, revoke/apply commissions
					  if ($new_affiliate !== NULL && $old_affiliate !== $new_affiliate) {
						  // Revoke the affiliate's commission for this order
						  uc_affiliate_revoke_commission($arg1, 'Order updated');
						  // Award the commission to the new affiliate instead
						  uc_affiliate_apply_commission($arg1, 'Order updated', $new_affiliate);
					  }
				  }
			  }
		  }
		  */
  }
}

/**
 * Apply a commission to a given order (for a given affiliate).
 *   @order - An order object.
 *   @op - An operation.
 *   @aff_id - An affiliate ID.
 */
function uc_affiliate_apply_commission($order, $op = 'Order: Completed', $aff_id = NULL) {  
  // Find all affiliates included in order
  $affcom = array();
  
  // Sum the prices of products from each affiliate store
  foreach ($order->products as $product) {
    // TODO enable setting for excluding certain products from commissionable total
    if (isset($aff_id) && is_numeric($aff_id)) {
	  $affiliate = $aff_id;
	} else
	if (isset($product->data['affiliate'])) {
      $affiliate = $product->data['affiliate'];
	}
    $price = $product->price * $product->qty;
    $affcom[$affiliate] = isset($affcom[$affiliate]) ? $affcom[$affiliate] + $price : $price;
  }
  
  // Determine whether the order was created through the website, or the admin interface
  $order_type = 'web'; // Assume 'web' by default
  
  $result = db_query('SELECT ac.message FROM {uc_order_admin_comments} ac JOIN {uc_orders} od ON ac.order_id = od.order_id WHERE od.order_id = %d', $order->order_id);
  
  if ($result) {
	while ($row = db_fetch_array($result)) { 
	  if ($row['message'] == 'Order created by the administration.') {
	    $order_type = 'admin';
	  }
	}
  }
  
  // write commission records
  foreach ($affcom as $affid => $total) {
    $affs = _uc_affiliate_get_upline_ids($affid);
        
	for ($i = 0; $i < count($affs); $i++) {
      $aff = $affs[$i];
	  $aff_name = user_load(array('uid' => $aff));
	  $aff_name = $aff_name->name;
      $level = $i + 1;
	  
	  if ($order_type == 'admin') {
	    $comm_pct = _uc_affiliate_get_commission_percentage($level, $aff, 'admin');
	  } else {
	    $comm_pct = _uc_affiliate_get_commission_percentage($level, $aff, 'web');
	  }
	  
	  if ($comm_pct > 0) {
        // TODO should this be rounded to nearest penny???
		$note = t('@operation. Level @level referral fee of @percent% recorded for affiliate <a href="'.base_path().'user/@uid/affiliate">@name</a>. Order created by @type.', array('@operation' => $op, '@level' => $level, '@percent' => $comm_pct*100, '@uid' => $aff, '@name' => $aff_name, '@type' => $order_type));
        $comm = $total * $comm_pct;
		$comm_type = 'Debenture';
		  
		// Loop through the uc_affiliate_commission table and grab the amounts oweing from the most recent record
		$result = db_query('SELECT outstanding_fees_usd, outstanding_fees_cad FROM {uc_affiliate_commission} WHERE aid = %d ORDER BY commission_id DESC LIMIT 1', $aff);
		  
		if ($result) {
		  while ($row = db_fetch_array($result)) { 
			$fees_usd = $row['outstanding_fees_usd'];
			$fees_cad = $row['outstanding_fees_cad'];
		  }
		} else {
		  $fees_usd = 0;
		  $fees_cad = 0;
		}
		  
		// Determine which outstanding balance to add the new commission to (CAD or USD)
		// (TODO - It would be nice to incorporate the ISO 3166 Country Codes API module - http://drupal.org/project/countries_api - or some sort of currency code module)
		if ($order->billing_country == 124) {
		  $fees_cad += $comm;
		  $currency = 'CAD';
		} else
		if ($order->billing_country == 840) {
	      $fees_usd += $comm;
		  $currency = 'USD';
		} else {
		  $currency = NULL;
		}
		
	    db_query("INSERT {uc_affiliate_commission} (order_id, aid, time, currency, order_sub_total, commission, commission_type, commission_notes, outstanding_fees_usd, outstanding_fees_cad) VALUES (%d, %d, %d, '%s', %f, %f, '%s', '%s', %f, %f)", $order->order_id, $aff, time(), $currency, $total, $comm, $comm_type, $note, $fees_usd, $fees_cad);
        uc_order_comment_save($order->order_id, $user->uid, $note);
        uc_affiliate_add_count($aff, 0, 0, 1);
      }
    }
  }
}

/**
 * Revoke a commission from a given order.
 */
function uc_affiliate_revoke_commission($order, $op = 'Order: Canceled') {
  // Find all affiliates included in order
  $affcom = array();
	
  // Sum the prices of products from each affiliate store
  foreach ($order->products as $product) {
    // TODO enable setting for excluding certain products from commissionable total
    if (isset($product->data['affiliate'])) {
      $affiliate = $product->data['affiliate'];
      $price = $product->price * $product->qty;
      $affcom[$affiliate] = isset($affcom[$affiliate]) ? $affcom[$affiliate] + $price : $price;
    }
  }
	
  // write commission records
  foreach ($affcom as $affid => $total) {
    $affs = _uc_affiliate_get_upline_ids($affid);
        
	for ($i = 0; $i < count($affs); $i++) {
      $aff = $affs[$i];
	  $aff_name = user_load(array('uid' => $aff));
	  $aff_name = $aff_name->name;
      $level = $i + 1;
      
	  // Because the affiliate's commission percentages may have changed, we need to calculate what the percentage was when the commission was first issued.
	  // Loop through the uc_affiliate_commission table and grab the order sub-total and commission from the most recent record
      $result = db_query("SELECT order_sub_total, commission FROM {uc_affiliate_commission} WHERE aid = %d AND commission_type = '%s' ORDER BY commission_id DESC LIMIT 1", $aff, 'Debenture');
		  
	  if ($result) {
		while ($row = db_fetch_array($result)) { 
		  $comm_pct = $row['commission'] / $row['order_sub_total'];
		}
	  }
	      
	  if ($comm_pct > 0) {
        // TODO should this be rounded to nearest penny???
		$note = t('@operation. Level @level referral fee of @percent% revoked for affiliate <a href="'.base_path().'user/@uid/affiliate">@name</a>.', array('@operation' => $op, '@level' => $level, '@percent' => $comm_pct*100, '@uid' => $aff, '@name' => $aff_name));
		$comm = -($total * $comm_pct);
		$comm_type = 'Revocation';
		  
		// Loop through the uc_affiliate_commission table and grab the amounts oweing from the most recent record
		$result = db_query('SELECT currency, outstanding_fees_usd, outstanding_fees_cad FROM {uc_affiliate_commission} WHERE aid = %d ORDER BY commission_id DESC LIMIT 1', $aff);
		  
		if ($result) {
		  while ($row = db_fetch_array($result)) { 
			$currency = $row['currency'];
			$fees_usd = $row['outstanding_fees_usd'];
			$fees_cad = $row['outstanding_fees_cad'];
		  }
		} else {
		  $currency = NULL;
		  $fees_usd = 0;
		  $fees_cad = 0;
		}
		  
		// Determine which outstanding balance to subtract the amount from (CAD or USD)
		if ($currency == 'CAD') {
		  $fees_cad += $comm;
		} else
		if ($currency == 'USD') {
		  $fees_usd += $comm;
		}
		  
	    db_query("INSERT {uc_affiliate_commission} (order_id, aid, time, currency, order_sub_total, commission, commission_type, commission_notes, outstanding_fees_usd, outstanding_fees_cad) VALUES (%d, %d, %d, '%s', %f, %f, '%s', '%s', %f, %f)", $order->order_id, $aff, time(), $currency, -$total, $comm, $comm_type, $note, $fees_usd, $fees_cad);
        uc_order_comment_save($order->order_id, $user->uid, $note);
      }
    }
  }
}

/**
 * uc_affiliate helper methods.
 */

/*
 * Period Selection form.
 */
function uc_affiliate_time_selection_form($method='') {
  // Date Setup
  $dates = uc_affiliate_predefined_range(arg(4));
  $dt_first = $dates['start'];
  $dt_last = $dates['end'];
  
  // Create the form
  $form = array();
  $form['range_display'] = array(
    '#prefix' => '<p><strong>',
    '#value' => t('Currently Displaying: '). date('Y-n-j', $dt_first) .t(' through '). date('Y-n-j', $dt_last),
    '#suffix' => '</strong></p>'
  );
  $form['time_selector'] = array(
    '#title' => t('Customize This Report'),
    '#type' => 'fieldset',
    '#collapsible' => TRUE,
    '#collapsed' => TRUE
  );
  $form['periods'] = array(
    '#type' => 'value',
    '#value' => uc_affiliate_predefined_range_options()
  );
  $form['time_selector']['period'] = array(
    '#title' => t('Select a predefined range'),
    '#type' => 'select',
    '#options' => $form['periods']['#value'],
    '#default_value' => arg(4)
  );
  $form['time_selector']['use_dates'] = array(
    '#type' => 'checkbox',
    '#title' => t('Search using date range.'),
    '#default_value' => arg(5) != 0 ? 1 : 0,
  );
  $form['time_selector']['begin_date'] = array(
    '#prefix' => '<span style="float:left;margin-right:15px;">',
    '#suffix' => '</span>',
    '#title' => t('Start Date'),
    '#type' => 'date',
    '#description' => t('Select a beginning date.'),
    '#default_value' => array(
      'month' => date('n', $dt_first),
      'day' => date('j', $dt_first),
      'year' => date('Y', $dt_first)
      ),
    '#required' => FALSE
  );
  $form['time_selector']['end_date'] = array(
    '#prefix' => '<span style="float:left;">',
    '#suffix' => '</span><br class="clearfix"/>',
    '#title' => t('End Date'),
    '#type' => 'date',
    '#description' => t('Select an end date.'),
    '#default_value' => array(
      'month' => date('n', $dt_last),
      'day' => date('j', $dt_last),
      'year' => date('Y', $dt_last)
      )
  );
  $form['time_selector']['submit'] = array(
    '#type' => 'submit',
    '#value' => t('Submit')
  );
  return $form;
}

/**
 * Period Selection form submit.
 */
function uc_affiliate_time_selection_form_submit($form, $form_values) {
  if ($form_values['use_dates']) {
    $start_date = mktime(0, 0, 0, $form_values['begin_date']['month'], $form_values['begin_date']['day'], $form_values['begin_date']['year']);
    $end_date = mktime(23, 59, 59, $form_values['end_date']['month'], $form_values['end_date']['day'], $form_values['end_date']['year']);
  }
  else {
    $start_date = '0';
    $end_date = '0';
  }
  drupal_goto(arg(0) .'/'. arg(1) .'/'. arg(2) .'/'. arg(3) .'/'. $form_values['period'] .'/'. $start_date .'/'. $end_date);
}

/**
 * Pre-defined range options.
 */
function uc_affiliate_predefined_range_options() {
  return array(t('This Month'), t('Last Month'), t('Last 30 days'), t('Last 60 Days'), t('Last 90 Days'));
}

/**
 * 
 */
function uc_affiliate_predefined_range($type=0) {
  $now = uc_affiliate_user_time();
  $dates = array();
  if (arg(5)!=0 && arg(6)!=0) {
    $dates['start'] = arg(5);
    $dates['end'] = arg(6);
  }
  else {
    switch ($type) {
      case 0:
        $dates['start'] = mktime(0, 0, 0, date("m", $now) , 1, date("Y", $now)) - date("Z", $now);
        $dates['end'] = mktime(23, 59, 59, date("m", $now)+1 , 0, date("Y", $now)) - date("Z", $now);
        break;
      case 1:
        $dates['start'] = mktime(0, 0, 0, date("m", $now)-1 , 1, date("Y", $now)) - date("Z", $now);
        $dates['end'] = mktime(23, 59, 59, date("m", $now) , 0, date("Y", $now)) - date("Z", $now);
        break;
      case 2:
        $start = strtotime("-30 days", $now);
        $dates['start'] = mktime(0, 0, 0, date("m", $start) , date("d", $start), date("Y", $start)) - date("Z", $start);
        $dates['end'] = mktime(23, 59, 59, date("m", $now) , date("d", $now), date("Y", $now)) - date("Z", $now);
        break;
      case 3:
        $start = strtotime("-60 days", $now);
        $dates['start'] = mktime(0, 0, 0, date("m", $start) , date("d", $start), date("Y", $start)) - date("Z", $start);
        $dates['end'] = mktime(23, 59, 59, date("m", $now) , date("d", $now), date("Y", $now)) - date("Z", $now);
        break;
      case 4:
        $start = strtotime("-90 days", $now);
        $dates['start'] = mktime(23, 59, 59, date("m", $start) , date("d", $start), date("Y", $start)) - date("Z", $start);
        $dates['end'] = mktime(23, 59, 59, date("m", $now) , date("d", $now), date("Y", $now)) - date("Z", $now);
        break;
    }
  }
  return $dates;
}

/**
 * 
 */
function uc_affiliate_user_time() {
  global $user;

  if (variable_get('configurable_timezones', 1) && $user->uid && strlen($user->timezone)) {
    return (time() - date("Z")) + $user->timezone;
  }
  else {
    return (time() - date("Z")) + variable_get('date_default_timezone', 0);
  }
}

/**
 *  Return the current time rounded down to the most recent chunk (day).
 */
function uc_affiliate_current_time() {
  $date_stamp = mktime(0, 0, 0, date("n"), date("j"), date("Y"));
  return $date_stamp;
  
  // $time = time();
  // return $time - ($time % 86400);
}

/**
 * Determine an affiliate's upline.
 */
function _uc_affiliate_get_upline_ids($aff) {
  if ($aff) {
    $level = variable_get('affiliate_hierarchy_depth', 5);
    $affids = array($aff);
    while ($aff = uc_affiliate_get_user_affiliate($aff)) {
      if (count($affids)>=$level) {
        break;
      }
      $affids[] = $aff;
    }
    return $affids;
  }
  else {
    return array();
  }
}

/**
 * Extract the subdomain from the host variable.
 */
function uc_affiliate_get_subdomain() {
  $host_array = explode('.', $_SERVER['HTTP_HOST']);
  if (count($host_array) <= 2) {
    return '';
  }
  else {
    array_pop($host_array);
    array_pop($host_array);
    return implode('.', $host_array);
  }
}

/**
 * Records affiliate ID in the user session and counts the click.
 */
function uc_affiliate_click() {
  // See if the URL references an affiliate node in argument 1

  if (arg(1) != '') {
  	$nid = arg(1);
	
    // Lookup the node id
    $node = node_load(array('nid' => $nid));
	
	// Check to be sure this node is an affiliate_intro (bio) node
	if ($node->type == 'affiliate_profile') {

		// Lookup the node author
		$account = user_load(array('uid' => $node->uid));

		// make sure we have a valid account and make sure they have affiliate perms
		if ($account && user_access('act as affiliate', $account)) {

			// store affiliate id in session and increment click count
			if (!$_SESSION['affiliate']) {
				$_SESSION['affiliate'] = $account->uid;
				uc_affiliate_add_count($account->uid, 1, 0, 0);
			}
		}
	}
	/*
	else {
      // User not an affiliate, redirect to default URL
      $default_url = variable_get('affiliate_invalid_redirect', '');
      if (!empty($default_url)) {
        header("Location: $default_url");
        exit(0);
      }
      else {
        drupal_set_message(t('You are attempting to access an affiliate store that does not exist. Please check the address and try again.'), 'error');
      }
    }
	*/
  } else {
    /*[e+] else we use a link like this site.com/?a=uid*/
    $aid = (int)$_REQUEST['a'];
    if ($aid !== 0) {
      // Lookup the affiliate's account
      $account = user_load(array('uid' => $aid));
      // make sure we have a valid account and make sure they have affiliate perms
      if ($account && user_access('act as affiliate', $account)) {
        // store affiliate id in session and increment click count
        if (!$_SESSION['affiliate']) {
          $_SESSION['affiliate'] = $account->uid;
          uc_affiliate_add_count($account->uid, 1, 0, 0);
        }
      }
      /*we need no redirection*/
    }
    /*[e-]*/
  }
}

/**
 * Get the affiliate that a user belongs to.
 */
function uc_affiliate_get_user_affiliate($uid) {
  $res = db_query('SELECT * FROM {uc_affiliate_users} WHERE uid = %d', $uid);
  if (!db_num_rows($res)) {
    return 0;
  }
  else {
    $affiliate = db_fetch_array($res);
    return $affiliate['aid'];
  }
}

/**
 * Implementation of hook_order_pane().
 */
function uc_affiliate_order_pane() {
	$panes[] = array(
		'id' => 'uc_affiliate_order_pane',
		'callback' => 'uc_affiliate_order_pane_assign',
		'title' => t('Affiliate assigned to order'),
		'desc' => t('Assign a specific affiliate to this order.'),
		'class' => 'pos-left',
		'weight' => 11,
		'show' => array('view', 'edit'),
	);
	return $panes;
}

/**
 * Callback function.
 */
function uc_affiliate_order_pane_assign($op, $arg1) {
	switch ($op) {
	
		case 'view':
			// Loop through the products in the order and set the value to the product's affiliate ID (if one exists)
			foreach ($arg1->products as $product) {
			    if (isset($product->data['affiliate'])) {
				    $aff = $product->data['affiliate'];
			    }
			}
			if (is_numeric($aff)) {
			    $result = db_query('SELECT name FROM {users} WHERE uid = %d', $aff);
				while ($row = db_fetch_array($result)) {
			        $aff_name = $row['name'];
		        }
		        return '<a href="'.base_path().drupal_get_path_alias('user/'.$aff.'/affiliate').'">'.$aff_name.'</a>';
			}
			else {
				return '<span>None</span>';
			}
			
		case 'edit-form':
		    // Prepare the 'select' options array 
		    $options = array();
			// Set the default value to zero
			$options[0] = t('Please select');
			$default = 0;
			
			// Get the ID of the 'affiliate' role
			$role_ids = user_roles(TRUE, 'act as affiliate');
			
			foreach ($role_ids as $key => $value) {
			    if (is_numeric($key) && $value == 'affiliate') {
				    $role_id = $key;
				}
			}
			// Get an array of all users with the 'affiliate' role
			$result = db_query('SELECT u.uid, u.name FROM {users} u JOIN {users_roles} r ON u.uid = r.uid WHERE r.rid = %d', $role_id);
			
		    // Populate the options array with the user names and ID's
		    while ($row = db_fetch_array($result)) {
			    $options[$row['uid']] = $row['name'];
		    }
			// Loop through the products in the order and set the default form value to the product's affiliate ID (if one exists)
			foreach ($arg1->products as $product) {
			  if (isset($product->data['affiliate'])) {
				$default = $product->data['affiliate'];
			  }
			}
			// Prepare the form
			$form['affiliate_select'] = array(
				'#type' => 'select',
				'#options' => $options,
				'#title' => t('Affiliate'),
				'#description' => 'If unable to locate the appropriate affiliate,<br /> use the <a href="/admin/store/affiliate/directory" target="_blank">Affiliate Directory Listing</a> to cross reference with their given name.'
			);
			$form['affiliate_select']['#default_value'] = $default;
			
		    return $form;
		
		case 'edit-theme':
		    // Render the form
		    return drupal_render($arg1['affiliate_select']);
		
		case 'edit-process':
			/*
			// Load the order object
			$order = uc_order_load($arg1['order_id']);
			
			// Get the ID of the affiliate that's assigned to the order
			// (This is stored individually, in each of the order's products)
			// (All of the products in the order should have the same affiliate assigned to them, so we're operating on this assumption)
			foreach ($order->products as $product) {
			    if (isset($product->data['affiliate'])) {
				    $aff = $product->data['affiliate'];
			    }
			}
			// If the affiliate has changed
			if (isset($aff) && $arg1['affiliate_select'] != $aff) {
			    // Revoke the affiliate's commission for this order (and their sub-affiliates)
			    uc_affiliate_revoke_commission($order, 'Order updated');
			  
			    // Give the commission to the new affiliate (and their sub-affiliates) instead
			    uc_affiliate_apply_commission($order, 'Order updated', $arg1['affiliate_select']);
			}
			// Assign the new affiliate ID to all of the products in the order
		    // (Note: Since hook_order gets called AFTER this function, we need to pass the new affiliate ID to hook_order, and update the uc_order_products table there [see hook_order: save])
			define(UC_AFFILIATE_ORDER_NEW_AFFILIATE, $arg1['affiliate_select']);
			*/
			return;
	}
}

/**
 * Get an affiliates commission percentage for the given level.
 *   @level - The commission level. 
 *   @uid - The user ID of a given affiliate. If provided, the function will return that user's percentage. If not, the current session value will be used.
 *   @order_type - The type of order the commission is being applied to (order was created through the website, or through the admin interface).
 */
function _uc_affiliate_get_commission_percentage($level = 1, $uid = 0, $order_type = 'web') {
	$commission_percents = array();
	
	// Determine which user to load
	if ($uid != 0) {
	    // If a user ID has been supplied, load that user
        $account = user_load(array('uid' => $uid));
    } else {
	    // Else, load the user who's ID is currently stored in the session variable
        $account = user_load(array('uid' => $_SESSION['affiliate']));
    }
	
	// Determine which commission structure to retrieve
	if ($order_type == 'admin') {
	    // If the order was created through the admin interface, retrieve the affiliate's "admin" commission structure
		if ($account->commission_admin) {
			// An "admin" commission structure already exists for this affiliate
			$commission_percents = explode(',', $account->commission_admin);
		} else {
			// Fall back to the global "admin" commission structure
			$structure = variable_get('affiliate_commission_structure_admin', '5,3,2,2,1');
			$commission_percents = explode(',', $structure);
		}
	} else {
	    // Else, assume that the order was created through the website and retrieve the affiliate's "web" commission structure
		if ($account->commission_web) {
			// A "web" commission structure already exists for this affiliate
			$commission_percents = explode(',', $account->commission_web);
		} else {
			// Fall back to the global "web" commission structure
			$structure = variable_get('affiliate_commission_structure_web', '5,3,2,2,1');
			$commission_percents = explode(',', $structure);
		}
	}
	
	// If the requested commission percentage level is valid, return it
    if ($level <= count($commission_percents)) {
        return $commission_percents[$level-1] / 100;
    } else {
	    return 0;
	}
}