<?php
/**
 * Clubdata Invoiceletter view
 *
 * Contains classes to generate and display serveral invoice list to send invoices to members
 *
 * @package List
 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
 * @author Franz Domes <franz.domes@gmx.de>
 * @version $Revision: 1.4 $
 * @version 2.0
 * @copyright Copyright (c) 2009, Franz Domes
 */

/**
 *
 */
if (defined('MEMBERLIST_CLASS')) {
    return 0;
} else {
    define('MEMBERLIST_CLASS', TRUE);
}

require_once('include/membertype_dep.php');
require_once('include/dblist.class.php');
require_once('include/addresses.class.php');
require_once('include/createPDF.class.php');
require_once('include/search.class.php');

/**
 * This class generates memberlist for various purposes
 *
 * @author Franz Domes <franz.domes@gmx.de>
 * @version $Revision: 1.3 $
 * @package List
 */
class vInvoiceletter
{
	/** @var additional columns for invoice */
	var $invoiceCols = array('`###_Members`.MemberID',
							 '`###_Memberfees`.id',
				           	 '`###_Memberfees`.`InvoiceNumber`',
           		           	 "DATE_FORMAT(`###_Memberfees`.InvoiceDate, '%e.%c.%Y') as `InvoiceDate`",
           		           	 "DATE_FORMAT(`###_Memberfees`.DueTo,'%e.%c.%Y') as `DueToDate`",
           		           	 '`###_Memberfees`.Period',
           		           	 '`###_Memberfees`.Amount',
           		           	 '`###_Memberfees`.Remarks',
           		           	 '`###_Memberfees`.DemandLevel',
#           		           	 '`###_Members`.DirectDebit_yn as `DirectDebit`',
           		           	 '`###_Memberfees`.Amount - IFNULL(`###_Membertype`.Amount , 0) as `Difference`'
							);

	/** @var additional columns for invoice excel generation */
	var $invoiceColsExcel = array('`###_Members`.MemberID',
							 '`###_Memberfees`.id',
				           	 '`###_Memberfees`.`InvoiceNumber`',
           		           	 "DATE_FORMAT(`###_Memberfees`.InvoiceDate, '%e.%c.%Y') as `InvoiceDate`",
           		           	 "DATE_FORMAT(`###_Memberfees`.DueTo,'%e.%c.%Y') as `DueToDate`",
           		           	 '`###_Memberfees`.Period',
           		           	 '`###_Memberfees`.Amount',
           		           	 '`###_Memberfees`.Remarks',
           		           	 '`###_Memberfees`.DemandLevel',
#           		           	 '`###_Members`.DirectDebit_yn as `DirectDebit`',
           		           	 '`###_Memberfees`.Amount - IFNULL(`###_Membertype`.Amount , 0) as `Difference`'
							);

    /** @var mailingtype defining invoice mails */
    var $invoiceMailingType = 3;

	/** @var invoice year, passed as parameter */
	var $invoiceYear;

    /** @var string name of command type to display*/
    var $command;

    /** @var object current list object*/
    var $mlist;

    var $db;

    /** @var integer set to 1 if new view has to be initialized*/
    var $initView;

    /** @var string holds SQL statement as generated by setSqlAndColumns()*/
    var $sql;

    /** @var strings holds columns to show, as generated by setSqlAndColumns()*/
    var $cols;

    /**
     * @var object
     */
    var $smarty;

    /**
     * @var object
     */
    var $formsgeneration;

    /**
    * Constructor of class List
    * @return integer Always OK
    */
    function vInvoiceletter($db, $command, $initView, &$smarty, &$formsgeneration)
    {
        $this->db = $db;
        $this->command = $command;
        $this->initView = $initView;
        $this->smarty = &$smarty;
        $this->formsgeneration = &$formsgeneration;

        $quicksearch = getGlobVar('quicksearch');
        if ( !empty($quicksearch) )
        {
            // Force initView if quicksearch is set !!
            $this->initView = 1;
        }

        $this->pageNr = getGlobVar('PageNr','::number::');
        if (empty($this->pageNr) )
        {
            $this->pageNr = 1;
        }

                // get invoice year from parameters
        $this->invoiceYear = getGlobVar('invoiceYear', '::number::');

        $this->cols = join(',', $this->setColumnAlias($this->invoiceCols));
//         echo "COLS: $this->cols<BR>";

        // Set up SQL, cols
        $this->sql = $this->generateSql('VIEW');

        if ( $this->initView != 1 )
        {
            $this->mlist = new DbList($this->db, 'memberlist');
        }
        else
      {
          $searchObj = new Search($this->db, $this->formsgeneration, 'Advanced', 'Search');
          $cond = $searchObj->generateSelectCMD();

          if ( !empty($this->invoiceYear) )
          {
          	$cond .= " AND `###_Memberfees`.Period = {$this->invoiceYear}";
          }
          $this->mlist = new DbList($this->db, 'memberlist',
                            array('changeFlg' => FALSE,
                                  'sql' => $this->sql,
                                  'cols' => $this->cols,
                                  'cond' => $cond,
                                  'selectRowsFlg' => true,
                                  // geändert 20090822, ohne ###_ bei MemberID wegen Member Search
                                  'idFieldName' => clubdata_mysqli::replaceDBprefix('MemberID', DB_TABLEPREFIX),
                                  'selectedRows' => (getConfigEntry($this->db, 'CheckedCheckboxes') ? 'ALL' : 'NONE'),
                                  'listLinks' => array ( 'Detail' => INDEX_PHP . '?mod=members&view=Overview'),
                                  'linkParams' => "&Command={$this->command}&mod=list&view=Memberlist"
                                  ));
          debug_r('M_LIST', $this->mlist, "[vInvoiceletter, vInvoiceletter] MLIST (COND: $cond)");

          if ( getGlobVar('resetList','[01]','PG') == 1 )
          {
              $this->mlist->resetAll();
          }

          if ( $this->mlist->getConfig('sort') == '' )
          {
              $this->mlist->setConfig('sort', '`###_Members`.`MemberID`');
          }
      }
    }

    function getSmartyTemplate()
    {
        return 'list/v_Memberlist.inc.tpl';
    }

    function setSmartyValues()
    {
        $this->mlist->prepareRecordList($this->pageNr);
//         debug_r('SMARTY', $this->mlist, "[V_Memberlist, setSmartyValues]: mlist");
        $this->smarty->assign_by_ref('MemberList', $this->mlist);
        $errTxt .= $this->formsgeneration->AddInput(array(
                        "TYPE"=>"hidden",
                        "NAME"=>'invoiceYear',
                        "ID"=>'invoiceYear',
                        "VALUE"=>$this->invoiceYear,
                        ));
    }

    /**
    * generates SQL command
    * All addresses are added dynamically.
    * @param $mode 'VIEW' generate SQL for viewing, 'EXCEL' generate SQL for Excel-export
    * @return boolean true : save ok, false: error
    */
    function generateSql($mode)
    {

        $adrObj = new Addresses($this->db, $this->formsgeneration);

        switch ( $mode )
        {
          case 'VIEW':
            $from = $adrObj->generateAdrTableList('           `###_Members`
            										LEFT JOIN `###_Members_Attributes` ON `###_Members`.MemberID = `###_Members_Attributes`.MemberID
            									    LEFT JOIN `###_Memberfees` ON `###_Members`.MemberID = `###_Memberfees`.MemberID
             										LEFT JOIN `###_Membertype` ON `###_Members`.Membertype_ref = `###_Membertype`.id',
                                                  '`###_Members`.`MemberID`');

            $sql = "SELECT DISTINCT * FROM $from";
            break;

          case 'EXCEL':
            $from = $adrObj->generateLetterAddressfields($this->invoiceMailingType,
                                                        $this->invoiceColsExcel,
            											'           `###_Members`
            											  LEFT JOIN `###_Members_Attributes` ON `###_Members`.MemberID = `###_Members_Attributes`.MemberID
            									    	  LEFT JOIN `###_Memberfees` ON `###_Members`.MemberID = `###_Memberfees`.MemberID
             											  LEFT JOIN `###_Membertype` ON `###_Members`.Membertype_ref = `###_Membertype`.id',
                                                  		'`###_Members`.`MemberID`' );
            $sql = "SELECT DISTINCT * FROM ($from) A";
            break;
        }


        debug('M_LIST', "[vInvoiceletter, generateSql] MLIST (SQL: $sql)");
        return $sql;

    }

    /**
    * generates appropriate columns aliases for address columns
    * Address_{addresstype_id}.{fieldname} will be converted to
    *   Address_{addresstype_id}.{fieldname} as {addresstype_description}_{fieldname}
    * @return string new columns definition
    */
    function setColumnAlias($additionalCols)
    {
//         print("COLS: <PRE>"); print_r($cols);print("</PRE>");
        $cols = getGlobVar('DisplayCols');
        if ( empty($cols) )
        {
            $cols = getConfigEntry($this->db, 'DefaultCols');
        }

        // Make sure, the first column is MemberID.
        // Use array_unique to eliminate other MemberIDs.
        // Join afterwards to have comma-delimited list of columns
        if ( is_array($cols) )
        {
            $colsArr = array_unique(array_merge(array('`###_Members`.MemberID'),array_keys($cols),$additionalCols));
        }
        else
        {
            $colsArr = array_unique(array_merge(array('`###_Members`.MemberID'),preg_split("/[\s,]+/",$cols),$additionalCols));
        }
//           echo "COLS: $cols<BR>";
//        print("<PRE>");print_r(array_keys($cols)+$additionalCols);print_r($colsArr);print("</PRE>");

        $adrObj = new Addresses($this->db, $this->formsgeneration);
        $addresstypes = $adrObj->getAddresstypes();

        for ( $i=0; $i < count($addresstypes) ; $i++ )
        {
            $adrID = $addresstypes[$i]['id'];
            $adrInfo[$adrID]['id'] = $adrID;
            $adrInfo[$adrID]['description'] = getDescriptionTxt($addresstypes[$i]);
        }

        $cols1 = array();
        foreach ( $colsArr as $col )
        {
        	// Do not process columns which have already an alias !
        	if (stripos($col, 'AS') === false)
        	{
		        $cols1[] = preg_replace(array('/Addresses_([0-9]+)\.([^ ,]+)/e',              // process lines starting with Addresses_
		                                    '/([, ]*)((?<!Addresses_)[^.`]+)\.([^ ,]+)/e'), // do not process columns which starts by 'Addresses_' anymore
		                              array("\"`Addresses_\$1`.`\$2` AS `\" . lang(\$adrInfo[\\1]['description']) . '_' . lang('\$2') . '`'",
		                                    "\"$1`$2`.`$3` AS `$2%$3`\""),
		                             $col);
        	}
        	else
        	{
        		$cols1[] = $col;
        	}
        }

//         print("COLS: <PRE>"); print_r($cols1);print("</PRE>");

        return $cols1;
    }


    /**
    * saves values passed via POST
    * @return boolean true : save ok, false: error
    */
    function doAction($action)
    {

        switch ( $action )
        {
            case 'SETCHECKED':
                $id = getGlobVar('id', '::number::');
                $newState = getGlobVar('newState', 'false|true');

                $this->mlist->setSelectedRows($id, ($newState == 'false' ? 0 : 1));

			      // Exit if ajax call is used
		          if ( getGlobVar('byAjax', 'true|false') == true )
		          {
			      	exit;
		          }
                break;

            case 'SELECTALL':
                $this->mlist->setAllSelectedRows(1);
                break;

            case 'DESELECTALL':
                $this->mlist->setAllSelectedRows(0);
                break;

            case 'EXCEL':
                $this->mlist->exportExcel();
                break;

            case 'PDF':
                $pdf = new createPDF($this->db, $formsgeneration, true);
                $pdf->createPDFbyMlist($this->mlist);
                break;

//             case 'INFOLETTER':
//                 include('modules/list/exportInfoLetter.php');
//                 break;

            case 'INVOICE':
                debug('M_LIST', "[vInvoiceletter, vInvoiceletter, INVOICE] BEFORE GENERATE EXCEL");
                $sql = $this->generateSql('EXCEL');
                debug('M_LIST', "[vInvoiceletter, vInvoiceletter, INVOICE] AFTER
                 GENERATE EXCEL");
                $invoiceletterList = $this->mlist->copyList('memberlist');

                $invoiceletterList->setSQL($sql);
                $invoiceletterList->setColumns('*');
                $invoiceletterList->setConfig("sort", "1");

                debug_r('M_LIST', $this->mlist, "[vInvoiceletter, vInvoiceletter, INVOICE] MLIST (COND: $cond)");
//                exit;
                $invoiceletterList->exportExcel();
                break;
        }
        return true;
    }

}
