<?php
/*
    Contribution Name: Manufacturer Sales Report
    Contribution Version: 2.3

    Creation of this file 
    Author Name: Cyril Jacquenot
    Author E-Mail Address: cyril.jacquenot@laposte.net
    Author Website: http://groms.dyndns.org
    Donations: www.paypal.com
    Donations Email: cyril.jacquenot@laposte.net
  
  WARNING:
    This file must be attached with the parent file "stats_manufacturers_sales.php"

*/

  
  function getMySQLraw($whichSQL) {
    global $start_date, $end_date, $manufacturer_id, $customer_id;
    
    $select = "sum(op.products_quantity) as sum_pq, sum(op.final_price*op.products_quantity) as sum_fp";
    $join = "";
    $max_date = "max(o.date_purchased) as dp, ";
    
    switch ($whichSQL) {
    case "m":
        $by = "m.manufacturers_name";
        $select = "m.manufacturers_id, " . $by . ", " . $select;
        $and = "AND m.manufacturers_id IS NOT NULL AND m.manufacturers_id <> ''"; 
        break;
    case "c":
        $by = "o.customers_name";
        $select = "o.customers_id, " . $by . ", " . $max_date . $select; 
        $and = "AND m.manufacturers_id = " . $manufacturer_id;
        break;
    case "p":
        $by = "op.products_name, opa.products_options, opa.products_options_values";
        $select = "op.products_id, " . $by . ", " . $max_date . $select; 
        $join = "LEFT JOIN " . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " AS opa ON op.orders_products_id = opa.orders_products_id"; 
        $and = "AND p.manufacturers_id = " . $manufacturer_id;
    if ($customer_id != "all") {
        $and .= " AND o.customers_id = " . $customer_id;
    }
        break;
    }
    $sql = "SELECT DISTINCT " . $select . " 
            FROM " . TABLE_ORDERS_PRODUCTS . " AS op
            LEFT JOIN " . TABLE_PRODUCTS . " AS p ON op.products_id = p.products_id
            LEFT JOIN " . TABLE_MANUFACTURERS . " AS m on p.manufacturers_id = m.manufacturers_id
            LEFT JOIN " . TABLE_ORDERS . " AS o ON op.orders_id = o.orders_id " .
            $join . "
            WHERE o.date_purchased BETWEEN '" . $start_date . " 00:00:00' AND '" . $end_date . " 23:59:59' " .
            $and . "
            GROUP BY " . $by . "
            ORDER BY " . $by;
    return $sql;
  }

  function getCustomerProductsBlock() {
    global $print, $total_quantity, $total_sales, $customer_id, $currencies, $start_date, $end_date;
    
    $total_quantity = 0;
    $total_sales = 0;
    
    $table = '<hr>
      <table border="0" width="100%" cellspacing="0" cellpadding="2">
        <tr>
          <td class="main" colspan="1" cellpadding="40">';
          
    //recherche du client dans la base
    if (($customer_id != "all_by_once") && ($customer_id != "all")) {
        $orders_query_raw = "select * FROM " . TABLE_ORDERS . " WHERE customers_id = " . $customer_id . " ORDER BY orders_id DESC";
        $orders_query = tep_db_query($orders_query_raw);
        if ($orders = tep_db_fetch_array($orders_query)) {
            // customer found!
            $order = new order($orders['orders_id']);
            $table .= "<span class='pageHeadingSmall'>".TABLE_CUSTOMER_NAME."</span> : ";
            if ( (!empty($order->delivery['name'])) && (!empty($order->delivery['street_address'])) ) {
                $table .= tep_address_format($order->delivery['format_id'], $order->delivery, 1, ' ', '<br>');
            } else if ($order->billing) {
                $table .= tep_address_format($order->billing['format_id'], $order->billing, 1, ' ', '<br>');
            }
            $table .=  "<br><br>";
        } else {
            $table .=  CUSTOMER_NOT_FOUND; 
        }
    } else {
        $table .= "<h3>".ALL_CUSTOMERS."</h3";
    }

    $table .= '
          </td>
          <td class="smallText" align="right" colspan="3">';
    if (!$print) {
        $table .= '<a href="'. tep_href_link(FILENAME_STATS_MANUFACTURERS, tep_get_all_get_params(array("cID","start_date","end_date")) . 'print=yes&customer_only=yes&cID='.$customer_id . '&start_date=' . $start_date . '&end_date=' . $end_date ) . '" target="print">'; 
        $table .= TEXT_BUTTON_REPORT_PRINT_THIS_CUSTOMER."</a>";
    }

    $table .= '
          </td>
        </tr>
        <tr class="dataTableHeadingRow">
          <td class="dataTableHeadingContent" nowrap width="100%">'.TABLE_PRODUCT_NAME.'</td>
          <td class="dataTableHeadingContent" align="center" nowrap>'.TABLE_ORDER_PURCHASED.'</td>
          <td class="dataTableHeadingContent" align="right" nowrap>'.TABLE_PRODUCT_QUANTITY.'</td>
          <td class="dataTableHeadingContent" align="right" nowrap>'.TABLE_PRODUCT_REVENUE.'</td>
        </tr>';

    // =======================================================================================================================================================================    
    // list of all products bought for manufucter_id and customer_id
    // =======================================================================================================================================================================    
    $man_cust_products_query = tep_db_query(getMySQLraw("p"));
        while ($man_cust_products = tep_db_fetch_array($man_cust_products_query)) {
            $products_model = $man_cust_products['products_model'];
            $products_name = $man_cust_products['products_name'];
            if ($man_cust_products['products_options_values'] != "") {$products_name .= " (" . $man_cust_products['products_options'] . " : " . $man_cust_products['products_options_values'] . ")"; }
                $products_quantity = $man_cust_products['sum_pq'];
                $final_price = $man_cust_products['sum_fp'];
      $table .= '
          <tr class="dataTableRow">
            <td class="dataTableContent" width="100%">';
            
      if (!$print) {
        $table .= '<a href="' . tep_href_link(FILENAME_CATEGORIES, 'action=new_product_preview&read=only&pID=' . $man_cust_products['products_id'] . '&origin=' . FILENAME_STATS_MANUFACTURERS . '?page=' . $_GET['page'], 'NONSSL') . '">' . $products_name . '</a>';
      }
      else {
        $table .= $products_name;
      }

      $table .= '
            </td>
            <td class="dataTableContent" align="center">'.strftime(DATE_FORMAT_SHORT, strtotime($man_cust_products['dp'])).'</td>
            <td class="dataTableContent" align="right">'.$products_quantity.'</td>
            <td class="dataTableContent" align="right">'.$currencies->format($final_price).'</td>
          </tr>';

          $total_quantity = $total_quantity + $products_quantity;
          $total_sales = ($total_sales + $final_price);
        }

    $table .= '
          <tr>
            <td class="dataTableTotalRow" colspan="2" align="right">'.ENTRY_TOTAL.'</td>
            <td class="dataTableTotalRow" align="right">'.$total_quantity.'</td>
            <td class="dataTableTotalRow" align="right">'.$currencies->format($total_sales).'</td>
          </tr>
      </table>';

    return $table;
  }
  
  if(!function_exists('getShortDate')){
      function getShortDate($strDate) {
          return strftime(DATE_FORMAT_SHORT, strtotime($strDate));
      }
  }
?>