<?
/**
 * Datenbankabfragefunktionen des Bereichs Finanzmonitor
 */

/**
 * Gibt Daten fuer Eingangs- Ausgangsuebersicht zurueck<br>
 * - Einsatz im Finanzmonitor - Eingangs- Ausgangsuebersicht
 *
 * @author Philipp Wabinski (02.11.2007)<philipp-wabinski@pepe-web.de>
 *
 * @param $kst_id_arr array Kst-Ids als einfaches Array <br>
 *
 * @param $date_start string Start des Zeitraums im Format 2007-01-31
 *
 * @param $date_end string Ende des Zeitraums im Format 2007-01-31
 * 
 * @param $kreditor_id_arr array Kreditor-Ids als einfaches Array <br>
 *
 * @param $date_type string Typ des Datums <br>
 * "billdate" => Einschr�nkung nach Rechungsdatum 
 * "billreceiveddate" => Einschr�nkung nach Rechungseingangsdatum (TODO)
 * "paymentdate" => Einschr�nkung nach Zahlungsadtum (TODO)
 * 
 * @param $attachment_id (int) if assigned all payments linked to this attachment will be returned
 * 
 * @param $bo_objects_data_id (int) if assigned all payments linked to this bo objects data will be returned
 * 
 * @param $bill_id (int) if assigned all payments linked to this bill will be returned
 * 
 * @param $payment_id (int) if assigned only the payment with the given Id will be returned will be returned
 * 
 * @param $type state "-1" / null => alle, "0" => prepared, "1" => ok
 * 
 * @param $creditor_id creditor id
 * @param $project_id project id
 * @param $debtor_id debtor id
 * 
 *  @param $arrSearch search array
 * 	@param $arrSearch[0] payment id
 * 	@param $arrSearch[0] process status id
 * 	@param $arrSearch[0] process type id
 * 	@param $arrSearch[0] process number
 *  @param $arrSearch[0] full text
 * 
 * @return array result[i]["*"]<br>
 * - "idpayment" => Id Zahlung
 * - "billdate" => Rechungsdatum (2007-08-31) => kein Datum: false
 * - "billreceiveddate" => Rechungseingangsdatum (2007-08-31)  => kein Datum: false
 * - "paymentdate" => Zahlungsdatum (2007-08-31) => kein Datum: false
 * - "net" => Netto-Summe
 * - "gross" => Brutto-Summe 
 * - "paymenttax_name" => Buchungsschluessel
 * - "costcenter_no" => Kostenstelle Nummer
 * - "costcenter_id" => Kostenstelle Id
 * - "costcenter_description" => Titel der Kst.
 * - "belegno" => Belegfeld
 */
function get_payment_data($kst_id_arr, 
						  $date_start, 
						  $date_end, 
						  $date_type, 
						  $kreditor_id_arr, 
						  $encode, 
						  $attachment_id, 
						  $bo_objects_data_id, 
						  $bill_id, 
						  $payment_id, 
						  $type, 
						  $search, 
						  $creditor_id, 
						  $project_id, 
						  $debtor_id,
						  $arrSearch, 
						  $start_index = null,
						  $limit_option = null,
						  $idpayment_accounts = null)
{

	require_once(INCLUDEPATH . 'flar/_skonto.php');
	require_once(INCLUDEPATH . 'flar/functions/db_person.php');
	require_once(INCLUDEPATH . 'flar/functions/db_blog.php' );
	require_once(INCLUDEPATH . 'flar/functions/db_bill.php');
	require_once(INCLUDEPATH . 'flar/Payment.php');
	require_once(INCLUDEPATH . 'flar/Costcenter.php');
	require_once(INCLUDEPATH . 'flar/Payment_tax_key.php');
	require_once(INCLUDEPATH . 'flar/Payment_has_attachment.php');
	require_once(INCLUDEPATH . 'flar/Payment_has_bo_objects_data.php');
	require_once(INCLUDEPATH . 'flar/Payment_has_bill_saved_head.php');
	require_once(INCLUDEPATH . 'flar/Customer.php');
	require_once(INCLUDEPATH . 'flar/Payment_accounts.php');
	require_once(INCLUDEPATH . 'flar/Skonto.php');
	require_once(INCLUDEPATH . 'flar/Bo_objects_data.php');
	require_once(INCLUDEPATH . 'flar/Bo_process_data.php');
	require_once(INCLUDEPATH . 'flar/Bo_process.php');
	require_once(INCLUDEPATH . 'flar/Bo_state.php');
	require_once(INCLUDEPATH . 'flar/Bo_process_data_number.php');
	require_once(INCLUDEPATH . 'flar/Currency.php');
	require_once(INCLUDEPATH . 'flar/functions/db_bp.php');
	require_once(INCLUDEPATH . 'flar/functions/tools.php');
	$langVars = parse_ini_file_quotes_safe(ROOTPATH."/modules/general/lang/flare/bp.".$_SESSION["MGW"]->spkz.".lang");
	
	$process_array = bo_get_bp();
	
	$db_payment          = new Flar_Payment();
	$db_costcenter       = new Flar_Costcenter();
	$db_payment_tax_key  = new Flar_Payment_tax_key();
	$db_pha              = new Flar_Payment_has_attachment();
	$db_phbood           = new Flar_Payment_has_bo_objects_data();
	$db_phbsh            = new Flar_Payment_has_bill_saved_head();
	$db_customer         = new Flar_Customer();
	$db_payment_accounts = new Flar_Payment_accounts();
	$db_bo_objects_data  = new Flar_Bo_objects_data();
	$db_bo_process_data  = new Flar_Bo_process_data();
	$db_bo_process  	 = new Flar_Bo_process();
	$db_bo_state  	 	 = new Flar_Bo_state();
	$process_data_number = new Flar_Bo_process_data_number();
	$db_currency 		 = new Flar_Currency();
	
	// query
	$whereAdd = "";
	
	if ( (int)$project_id) {
		$whereAdd = "Where (pm.`Costcenter_COSTCENTER_ID` = ".$project_id." ) ";
	}
	
	// Einschraenkung des Zeitraums
	if(strlen($date_start) > 0 and strlen($date_end) > 0)
	{
		if($date_type == "billdate")
		{
			if(strlen($whereAdd) > 0)
			{
				$whereAdd .= " AND (pm.`billdate` >= '".$date_start."' AND pm.`billdate` <= '".$date_end."'";
			}
			else
			{
				$whereAdd = "WHERE (pm.`billdate` >= '".$date_start."' AND pm.`billdate` <= '".$date_end."'";
			}
			
			$whereAdd .= ")";
		}	
	}
	
	// limitation of attachment id
	if($attachment_id > 0)
	{
			if(strlen($whereAdd) > 0)
			{
				$whereAdd .= " AND pha.`attachment_id` = ".$attachment_id;
			}
			else
			{
				$whereAdd = "WHERE pha.`attachment_id` = ".$attachment_id;
			}
	}
	
	// limitation of bo objects data id
	if($bo_objects_data_id > 0)
	{
			if(strlen($whereAdd) > 0)
			{
				$whereAdd .= " AND phbood.`bo_objects_data_idbo_objects_data` = ".$bo_objects_data_id;
			}
			else
			{
				$whereAdd = "WHERE phbood.`bo_objects_data_idbo_objects_data` = ".$bo_objects_data_id;
			}
	}
	
	// limitation of bill
	if($bill_id > 0)
	{
			if(strlen($whereAdd) > 0)
			{
				$whereAdd .= " AND phbsh.`bill_saved_head_BILL_SAVED_HEAD_ID` = ".$bill_id;
			}
			else
			{
				$whereAdd = "WHERE phbsh.`bill_saved_head_BILL_SAVED_HEAD_ID` = ".$bill_id;
			}
	}
	
	// payment_id
	if($payment_id > 0)
	{
			if(strlen($whereAdd) > 0)
			{
				$whereAdd .= " AND pm.`idpayment` = ".$payment_id;
			}
			else
			{
				$whereAdd = "WHERE pm.`idpayment` = ".$payment_id;
			}
	}
	
	// type
	if(strlen($type) and $type !== "-1")
	{
			if(strlen($whereAdd) > 0)
			{
				$whereAdd .= " AND pm.`state` = ".$type;
			}
			else
			{
				$whereAdd = "WHERE pm.`state` = ".$type;
			}
	}

	// 	search for payment by id
	if ($search) $whereAdd = " WHERE pm.`idpayment` = ".$search;
	
	if ($arrSearch){
		if ($arrSearch[1]){
			$whereAdd .= " AND bo_state.`idbo_state` = ".$arrSearch[1];
		}
		
		if ($arrSearch[2]){
			$whereAdd .= " AND bo_process.`idbo_process` = ".$arrSearch[2];
		}
		
		if ($arrSearch[3]){
			// explode into year and number
			$exploded = explode ('-', $arrSearch[3]);
			// if year empty, the set to current year
			if ((int)$exploded[0] and !(int)$exploded[1]) {
			  	$year    = 0;			
			    $process = $exploded[0];
			  } else {
			  	$year    = $exploded[0];
			  	$process = $exploded[1];
			  }
			
			if (!$year) $year = date("Y",time());
				
			$whereAdd .= " AND process_data_number.`process_year` = ".$year." AND process_data_number.`process_number` = ".$process;
		}
	}
	
	// limit option
	$limitAdd = "";
	if ( $start_index and (int)$limit_option )
	{
		$limitAdd = " LIMIT ".($start_index).", ".$limit_option;
	}
  	
  	if ((int)$creditor_id){
  		$whereAdd .= " AND cust.`CUSTOMER_ID` = ".$creditor_id;
  	}
  	
  	if ((int)$debtor_id){
  		$whereAdd .= " AND cust_deb.`CUSTOMER_ID` = ".$debtor_id;
  	}
  	
  	if ((int)$idpayment_accounts){
  		$whereAdd .= " AND pm.`payment_accounts_idpayment_accounts` = ".$idpayment_accounts;
  	}
  	
	$query = "Select pm.`idpayment`, pm.`Customer_CUSTOMER_ID`, pm.`billdate`, pm.`billreceiveddate`, pm.`booking_date`, pm.`paymentdate`, pm.`payment_value`, pm.`netto`, pm.`netto`,  pm.`brutto`, pm.`belegno`, pm.`belegnr`, pm.`state`,  pm.`dir`,  
				pm.`sday1`, pm.`skonto1`, pm.`sday2`, pm.`skonto2`, pm.`nettoday`, pm.`last_modified`, pm.`lastmodified_person`, pm.`created`, pm.`created_person`, bo_process_data.`idbo_process_data`, bo_state.`titel` as process_state,
				cc.`COSTCENTER_NO`, cc.`COSTCENTER_ID`, cc.`description` as costcenter_description, ptk.`name` as paymenttax_name, ptk.`tax`, ptk.`number` as paymenttax_no, bo_process.`idbo_process`, currency.`currency_title`,  
				COUNT(pha.`payment_idpayment`) AS count_pha, COUNT(phbood.`payment_idpayment`) AS count_phbood, phbood.`bo_objects_data_idbo_objects_data`, COUNT(phbsh.`payment_idpayment`) AS count_phbsh,
				cust.`name` AS cust_name, cust.`customer_no` AS cust_no, cust.`short` AS cust_short, cust_deb.`name` AS cust_name_deb, cust_deb.`customer_no` AS cust_no_deb, cust_deb.`short` AS cust_short_deb,
				pa.`description` AS pa_description, pa.`number` AS pa_number
				FROM `".$db_payment->__table."` as pm
				LEFT OUTER JOIN `".$db_costcenter->__table."` as cc ON cc.`COSTCENTER_ID` = pm.`Costcenter_COSTCENTER_ID`
				LEFT OUTER JOIN `".$db_payment_tax_key->__table."` as ptk ON pm.`payment_tax_key_idpayment_tax_key` = ptk.`idpayment_tax_key`
				LEFT OUTER JOIN `".$db_pha->__table."` as pha ON pha.`payment_idpayment` = pm.`idpayment`
				LEFT OUTER JOIN `".$db_phbood->__table."` as phbood ON phbood.`payment_idpayment` = pm.`idpayment`
				LEFT OUTER JOIN `".$db_bo_objects_data->__table."` as bo_objects_data ON bo_objects_data.`idbo_objects_data` = phbood.`bo_objects_data_idbo_objects_data`
				LEFT OUTER JOIN `".$db_bo_state->__table."` as bo_state ON bo_state.`idbo_state` = bo_objects_data.`bo_state_idbo_state`
				LEFT OUTER JOIN `".$db_bo_process_data->__table."` as bo_process_data ON bo_process_data.`idbo_process_data` = bo_objects_data.`bo_process_data_idbo_process_data`
				LEFT OUTER JOIN `".$db_bo_process->__table."` as bo_process ON bo_process.`idbo_process` = bo_process_data.`bo_process_idbo_process`
				LEFT OUTER JOIN `".$process_data_number->__table."` as process_data_number ON process_data_number.`bo_process_data_idbo_process_data` = bo_process_data.`idbo_process_data`
				LEFT OUTER JOIN `".$db_phbsh->__table."` as phbsh ON phbsh.`payment_idpayment` = pm.`idpayment`
				LEFT OUTER JOIN `".$db_customer->__table."` as cust ON cust.`CUSTOMER_ID` = pm.`Customer_CUSTOMER_ID`
				LEFT OUTER JOIN `".$db_payment_accounts->__table."` as pa ON pa.`idpayment_accounts` = pm.`payment_accounts_idpayment_accounts`
				LEFT OUTER JOIN `".$db_customer->__table."` as cust_deb ON cust_deb.`CUSTOMER_ID` = cc.`Customer_CUSTOMER_ID`
				LEFT OUTER JOIN `".$db_currency->__table."` as currency ON currency.`idcurrency` = pm.`currency_idcurrency`
				".$whereAdd."
				GROUP BY pm.`idpayment`
				ORDER BY pm.`idpayment` ASC".$limitAdd;
//	echo $query;
//	return;
	$db_payment->query($query);
	$result = array();
	
	while($db_payment->fetch())
	{
		
		// Brutto
		if($db_payment->netto !== null)
		{
			$net = (float)$db_payment->netto;
		}
		else
		{
			$net = false;
		}
		
		// Brutto
		if($db_payment->brutto !== null)
		{
			$gross = (float)$db_payment->brutto;
		}
		else
		{
			$gross = false;
		}
		
		// MWSt.
		if($db_payment->paymenttax_name !== null)
		{
			//$paymenttax_name = ($encode) ? urlencode($db_payment->paymenttax_no.' ('.$db_payment->paymenttax_name.')') : $db_payment->paymenttax_name;
			$paymenttax_name = ($encode) ? urlencode($db_payment->paymenttax_no) : $db_payment->paymenttax_no;

		}
		else
		{
			$paymenttax_name = false;
		}
		
		// debitor
		if(strlen($db_payment->cust_short_deb))
		{
			//$debitor_name = ($encode) ? urlencode($db_payment->cust_no_deb.' ('.$db_payment->cust_short_deb.')') : $db_payment->cust_no_deb.' ('.$db_payment->cust_short_deb.')';
			$debitor_name = ($encode) ? urlencode($db_payment->cust_no_deb.' '.$db_payment->cust_short_deb) : $db_payment->cust_no_deb.' '.$db_payment->cust_short_deb;

		}
		elseif(strlen($db_payment->cust_name_deb))
		{
			//$debitor_name = ($encode) ? urlencode($db_payment->cust_no_deb.' ('.$db_payment->cust_name_deb.')') : $db_payment->cust_no_deb.' ('.$db_payment->cust_name_deb.')';
			$debitor_name = ($encode) ? urlencode($db_payment->cust_no_deb.' '.$db_payment->cust_name_deb) : $db_payment->cust_no_deb.' '.$db_payment->cust_name_deb;

		}
		else
		{
			$debitor_name = false;
		}

		//Add debtor number
		$debitor_number = $db_payment->cust_no_deb;
		
		// kreditor
		if(strlen($db_payment->cust_short))
		{
			//$kreditor_name = ($encode) ? urlencode($db_payment->cust_no.' ('.$db_payment->cust_short.')') : $db_payment->cust_no.' ('.$db_payment->cust_short.')';
			$kreditor_name = ($encode) ? urlencode($db_payment->cust_no.' '.$db_payment->cust_short) : $db_payment->cust_no.' '.$db_payment->cust_short;

		}
		elseif(strlen($db_payment->cust_name))
		{
			//$kreditor_name = ($encode) ? urlencode($db_payment->cust_no.' ('.$db_payment->cust_name.')') : $db_payment->cust_no.' ('.$db_payment->cust_name.')';
			$kreditor_name = ($encode) ? urlencode($db_payment->cust_no.' '.$db_payment->cust_name) : $db_payment->cust_no.' '.$db_payment->cust_name;

		}
		else
		{
			$kreditor_name = false;
		}
		
		//Add creditor number
		$kreditor_number = $db_payment->cust_no;

		// skonto
		$temp_skonto = calculate_skonto_data((float)$db_payment->sday1, 
	 										(float)$db_payment->skonto1, 
	 										(float)$db_payment->sday2, 
	 										(float)$db_payment->skonto2, 
	 										(float)$db_payment->nettoday, 
	 										(int)$billdate, 
	 										(float)$db_payment->netto, 
	 										(float)$db_payment->tax);

		// creation person
		if($db_payment->created_person > 0) 
		{
			$creation_person_array = get_person_data(array($db_payment->created_person));
			$creation_person = $creation_person_array[0]["lastname"].", ".$creation_person_array[0]["firstname"];
		}
		else
		{
			$creation_person = false;
		}
		
		// last modified person
		if($db_payment->lastmodified_person > 0) 
		{
			$last_modified_person_array = get_person_data(array($db_payment->lastmodified_person));
			$last_modified_person = $last_modified_person_array[0]["lastname"].", ".$last_modified_person_array[0]["firstname"];
		}
		else
		{
			$last_modified_person = false;
		}
		
		// row color
		// if payment out (Zahlunsgausgang vorhanden) then set to green
		if ((float)$db_payment->payment_value) $row_color = 3;
		  else if ((int)$db_payment->state) $row_color = 1;
		    else $row_color = 2;
		
		// get blog value
		$blog = blog_get_icon_value( 1, $db_payment->idpayment);
		
		$db_bill = get_payment_bill_items($db_payment->idpayment);
		
		$process_type = "";
		for ($q =0; $q < count($process_array); $q++){
			if ($db_payment->idbo_process == $process_array[$q]['id']){
				$process_type = $process_array[$q]['titel'];
			}
		}
		
		if ((int)$db_payment->idbo_process_data){
			$process_number = get_business_process_number_of_bp($db_payment->idbo_process_data);
		}
		
		$result[] = array("idpayment"              	 => $db_payment->idpayment,
							"billdate"               => ($db_payment->billdate AND $db_payment->billdate!="0000-00-00") ? $db_payment->billdate : false,                 // Receipt Date (Belegdatum)
							"billreceiveddate"       => ($db_payment->billreceiveddate AND $db_payment->billreceiveddate!="0000-00-00") ? $db_payment->billreceiveddate : false, // Inbox Date (Eingangsdatum)
							"booking_date"           => ($db_payment->booking_date AND $db_payment->booking_date!="0000-00-00") ? $db_payment->booking_date : false,   		 // Booking Date (Buchungsdatum)
							"net"                    => $net,
							"gross"                  => $gross,
							"bill_id"                => $db_bill[0]["bill_id"],
							"paymenttax_name"        => $paymenttax_name,
							"kreditor_id"            => $db_payment->Customer_CUSTOMER_ID,
							"costcenter_no"          => $db_payment->COSTCENTER_NO,
							"costcenter_id"          => $db_payment->COSTCENTER_ID,
							"costcenter_description" => ($encode) ? urlencode($db_payment->costcenter_description) : $db_payment->costcenter_description,
							"belegno"                => ($encode) ? urlencode($db_payment->belegno) : $db_payment->belegno,
							"receipt_number"         => ($encode) ? urlencode($db_payment->belegnr) : $db_payment->belegnr,
							"count_pha"              => $db_payment->count_pha,
							"count_phbood"           => $db_payment->count_phbood,
							"count_phbsh"            => $db_payment->count_phbsh,
							"dir"                    => $db_payment->dir,
							"kreditor_name"          => $kreditor_name,
							"debitor_name"           => $debitor_name,
							"idbo_objects_data"      => $db_payment->bo_objects_data_idbo_objects_data,
							"payment_account"        => (strlen($db_payment->pa_number)) ? ($encode) ? urlencode($db_payment->pa_number.' '.$db_payment->pa_description) : $db_payment->pa_number.' '.$db_payment->pa_description : false,
							"skonto1"                => ((float)$db_payment->skonto1 > 0) ? $db_payment->skonto1 : false,
							"skonto2"                => ((float)$db_payment->skonto2 > 0) ? $db_payment->skonto2 : false,
							"sdate1"                 => $temp_skonto["sdate_1"],
							"sdate2"                 => $temp_skonto["sdate_2"],
							"ssum_1_gross"           => $temp_skonto["ssum_1_gross"],
							"ssum_2_gross"           => $temp_skonto["ssum_2_gross"],
							"act_sum"                => $temp_skonto["act_sum"],
							"creation_date"          => ((int)$db_payment->created > 0) ? date("Y-m-d", $db_payment->created) : false,
							"creation_person"        => ($encode) ? urlencode($creation_person) : $creation_person,
							"modified_date"          => ((int)$db_payment->last_modified > 0) ? date("Y-m-d", $db_payment->last_modified) : false,
							"modified_person"        => $last_modified_person,
							"state"                  => "--",
							"process"                => $process_number,        // process numbers of process linked with document (by object) or object
							"process_type"			 => $process_type,
							"process_state"          => $langVars[$db_payment->process_state],       // state of this process
							"payment_amount"         => $db_payment->payment_value,
							"payment_date"           => ($db_payment->paymentdate  AND $db_payment->paymentdate!="0000-00-00") ? $db_payment->paymentdate : false,
							"row_color"              => $row_color,
							"blog"                   => $blog,
							"blog_id"                   => BLOG_PAYMENT,
							"process_id"             => $db_payment->idbo_process,
							"debitor_number"		 => $debitor_number, //Debtor number
							"kreditor_number"		 => $kreditor_number, //Creditor number
							"receipt_text"			 => "Dummy data", 
							"currency_id"			 => 1,                 // dummy
							"currency_string"		 => $db_payment->currency_title
							);
	}
	
	$db_payment->free();
	$db_costcenter->free();
	$db_payment_tax_key->free();
	$db_pha->free();
	$db_phbood->free();
	$db_phbsh->free();
	$db_customer->free();
	$db_payment_accounts->free();
	$db_bo_objects_data->free();
	$process_data_number->free();
	$db_currency->free();
	
//	print_r($result);
//	return;
	return $result;
}

/**
 * Gibt alle Top Kategoreien fuer Auswahllisten zurueck<br>
 *	- sortiert nach dem Namen aufsteigend
 * @author Philipp Wabinski (01.11.2007)<philipp-wabinski@pepe-web.de>
 * 
 * @param $encode (bool) Kodieren der Textdaten zur Uebertragung per JSON-RPC
 * 
 * @return array result[i]["*"]<br>
 * - "id" => Top Kategorie Id
 * - "name" => Name der Top Kategorie
 */
function get_top_categories($encode)
{
	require_once(INCLUDEPATH . 'flar/Payment_top_categories.php');

	$db_payment_top_categories = new Flar_Payment_top_categories();
	$db_payment_top_categories->orderBy("name");
	$db_payment_top_categories->find();
	
	$result = array();
	for($i = 0; $db_payment_top_categories->fetch(); $i++)
	{		
		$result[$i] = array("id" => $db_payment_top_categories->idpayment_top_categories,
							"name" => (!$encode) ? $db_payment_top_categories->name : urlencode($db_payment_top_categories->name));
	}
	//print_r($result);
	return $result;
}

/**
 * Gibt Main Kategorien fuer Auswahllisten zurueck<br>
 *	- sortiert nach dem Namen aufsteigend
 *
 * @author Philipp Wabinski (01.11.2007)<philipp-wabinski@pepe-web.de>
 * 
 * @param $encode (bool) Kodieren der Textdaten zur Uebertragung per JSON-RPC
 * 
 * @param $top_category_id (int) Id der Top Kategorie
 * 
 * @return array result[i]["*"]<br>
 * - "id" => Main Kategorie Id
 * - "name" => Name der Main Kategorie
 */
function get_main_categories($encode, $top_category_id)
{
	require_once(INCLUDEPATH . 'flar/Payment_main_categories.php');

	$db_payment_main_categories = new Flar_Payment_main_categories();
	$db_payment_main_categories->payment_top_categories_idpayment_top_categories = $top_category_id;
	$db_payment_main_categories->orderBy("name");
	$db_payment_main_categories->find();
	
	$result = array();
	for($i = 0; $db_payment_main_categories->fetch(); $i++)
	{		
		$result[$i] = array("id" => $db_payment_main_categories->idpayment_main_categories,
							"name" => (!$encode) ? $db_payment_main_categories->name : urlencode($db_payment_main_categories->name));
	}
	//print_r($result);
	return $result;
}

/**
 * Gibt Kategorien fuer Auswahllisten zurueck<br>
 *	- sortiert nach dem Namen aufsteigend
 *
 * @author Philipp Wabinski (01.11.2007)<philipp-wabinski@pepe-web.de>
 * 
 * @param $encode (bool) Kodieren der Textdaten zur Uebertragung per JSON-RPC
 * 
 * @param $main_category_id (int) Id der Main Kategorie
 * 
 * @return array result[i]["*"]<br>
 * - "id" => Main Kategorie Id
 * - "name" => Name der Kategorie
 */
function get_categories($encode, $main_category_id)
{
	require_once(INCLUDEPATH . 'flar/Payment_categories.php');

	$db_payment_categories = new Flar_Payment_categories();
	$db_payment_categories->payment_main_categories_idpayment_main_categories = $main_category_id;
	$db_payment_categories->orderBy("name");
	$db_payment_categories->find();
	
	$result = array();
	for($i = 0; $db_payment_categories->fetch(); $i++)
	{		
		$result[$i] = array("id" => $db_payment_categories->idpayment_categories,
							"name" => (!$encode) ? $db_payment_categories->name : urlencode($db_payment_categories->name));
	}
	//print_r($result);
	return $result;
}

/**
 * Sucht ein Konto anhand der Nummer und gibt die Beschreibung zurueck<br>
 *
 * @author Philipp Wabinski (02.11.2007)<philipp-wabinski@pepe-web.de>
 * 
 * @param $encode (bool) Kodieren der Textdaten zur Uebertragung per JSON-RPC
 * 
 * @param $account_number (bool) Nummer des Kontos
 * 
 * @return array result["*"]<br>
 * - "description" => Beschreibung des Kontos
 * - false falls kein Konto mit dieser Nummer gefunden wurde
 */
function find_account_by_number($encode, $account_number)
{
	require_once(INCLUDEPATH . 'flar/Payment_accounts.php');
	
	$db_payment_accounts = new Flar_Payment_accounts();
	$db_payment_accounts->number = $account_number;
	$db_payment_accounts->find(true);
	
	if($db_payment_accounts->idpayment_accounts > 0 and $encode)
	{
		return array("description" => urlencode($db_payment_accounts->description));
	}
	else if($db_payment_accounts->idpayment_accounts > 0)
	{
		return array("description" => $db_payment_accounts->description);
	}
	else
	{
		return false;
	}
}

/**
 * Sucht einen Buchungsschluessel anhand der Nummer und gibt den Namen zurueck<br>
 *
 * @author Philipp Wabinski (02.11.2007)<philipp-wabinski@pepe-web.de>
 * 
 * @param $encode (bool) Kodieren der Textdaten zur Uebertragung per JSON-RPC
 * 
 * @param $number (bool) Nummer des Schluessels
 * 
 * @return array result["*"]<br>
 * - "name" => Name des Schluessels
 * - "tax" => Steuersatz
 * - false falls kein Konto mit dieser Nummer gefunden wurde
 */
function find_tax_key($encode, $number)
{
	require_once(INCLUDEPATH . 'flar/Payment_tax_key.php');
	
	$db_tax_key = new Flar_Payment_tax_key();
	$db_tax_key->number = $number;
	$db_tax_key->find(true);
	
	if($db_tax_key->idpayment_tax_key > 0 and $encode)
	{
		return array("name" => urlencode($db_tax_key->name),
					 "tax" => $db_tax_key->tax);
	}
	else if($db_tax_key->idpayment_tax_key > 0)
	{
		return array("name" => $db_tax_key->name,
					 "tax" => $db_tax_key->tax);
	}
	else
	{
		return false;
	}
}

/**
 * Erstellt und aktualisiert eine Zahlung
 * 
 * @author Philipp Wabinski (03.11.2007) <info@pepe-web.de>
 * 
 * @param $payment_id (int) Wenn groesser 0 wird geupdated
 * @param $category_id (int) Kategorie Id
 * @param $costcenter_id (int) Kostenstellen ID
 * @param $account_number (int) Nummer des Kontos
 * @param $payment_tax_key_number (int) Nummer des Buchungsschluessels
 * @param $receipt_field (string) Belegfeld
 * @param $bill_date (string) gew. Rechungsdatum (2007-12-31)
 * @param $bill_received_date (string) gew. Rechungseingangsdatum (2007-12-31)
 * @param $net (float) Nettobetrag
 * @param $gross (float) Bruttobetrag
 * @param $skonto_id (int) gew. Skokntoloesung (0 falls keine gew.)
 * @param $sday1 (int) Skonto 1 Tage
 * @param $skonto1 (float) Skonto 1 %
 * @param $sday2 (int) Skonto 2 Tage
 * @param $skonto2 (float) Skonto 2 %
 * @param $net_days (int) Skonto Nettotage 
 * @param $payment_date (string) gew. Zahlungsdatum (2007-12-31)
 * @param $payment_mode_id (int) Id des Zahlungsmodus
 * @param $payment_method_id (int) Id der Zahlungsmethode
 * @param $kreditor_id (int) Id from the kreditor
 * @param $attachmentd_id (int) ID of the document that should belong to this payment (link between this document and new payment will be created - only when a new payment is created)
 * @param $bo_objects_data_id (int) ID of the bo objects data that should belong to this payment (link between this bo objects data and new payment will be created - only when a new payment is created)4
 * @param $bill_id (int) ID of the bill that should belong to this payment (link between this bill and new payment will be created - only when a new payment is created)
 * @param $receipt_number number of receipt
 * @param $state state of payment
 * @param $dir direction of payment
 * @param $tax_id tax id
 * @param $foreign_tax foreign taxation
 * @param $booking_date booking date (2007-12-31 || false)
 * @param $payment_value payment value
 * @param $payment_value_id
 * 
 * @return false, falls Eintrag nicht erstellt wurde ansonsten die ID des neuen Eintrags
 */
function save_payment(	$payment_id,
						$category_id, 
						$costcenter_id, 
						$account_number, 
						$payment_tax_key_number, 
						$receipt_field,
						$bill_date,
						$bill_received_date,
						$net,
						$gross,
						$skonto_id, 
						$sday1, 
						$skonto1, 
						$sday2, 
						$skonto2,
						$net_days,
						$payment_date,
						$payment_mode_id,
						$payment_method_id,
						$kreditor_id,
						$attachment_id,
						$bo_objects_data_id,
						$bill_id,
						$receipt_number,
						$state,
						$dir,
						$tax_id,
						$foreign_tax,
						$booking_date,
						$payment_value,
						$payment_value_id,
						$currency_id)
{
		
	//if($costcenter_id > 0)
	//{
		
		require_once(INCLUDEPATH . 'flar/Payment.php');
		require_once(INCLUDEPATH . 'flar/Payment_accounts.php');
		require_once(INCLUDEPATH . 'flar/Payment_tax_key.php');
		require_once(INCLUDEPATH . 'flar/Payment_has_attachment.php');
		require_once(INCLUDEPATH . 'flar/Payment_has_bo_objects_data.php');
		require_once(INCLUDEPATH . 'flar/Payment_has_bill_saved_head.php');
		
		# notwendige Daten ermitteln
		
		// Konto Id
		$account_id = false;
		if(strlen($account_number))
		{
			$db_payment_accounts = new Flar_Payment_accounts();
			$db_payment_accounts->number = $account_number;
			$db_payment_accounts->find(true);
			
			if($db_payment_accounts->idpayment_accounts > 0)
			{	
				$account_id = $db_payment_accounts->idpayment_accounts;
			}
		}
		
		// Buchungschluessel Id
		$tax_key_id = false;
		if(strlen($payment_tax_key_number))
		{
			$db_tax_key = new Flar_Payment_tax_key();
			$db_tax_key->number = $payment_tax_key_number;
			$db_tax_key->find(true);
			
			if($db_tax_key->idpayment_tax_key > 0)
			{
				$tax_key_id = $db_tax_key->idpayment_tax_key;
			}
		} else $tax_key_id = 0;
	
		$db_payment = new Flar_Payment();
		if($payment_id > 0) $db_payment->get($payment_id);
		if ($category_id) $db_payment->payment_categories_idpayment_categories = $category_id;
		if ($costcenter_id) $db_payment->Costcenter_COSTCENTER_ID = $costcenter_id;
		if ($account_id) $db_payment->payment_accounts_idpayment_accounts = $account_id;
		// if ($tax_key_id) $db_payment->payment_tax_key_idpayment_tax_key = $tax_key_id;
		$db_payment->payment_tax_key_idpayment_tax_key = $tax_key_id;
		
		$db_payment->belegnr = urldecode($receipt_number);
		$db_payment->belegno = urldecode($receipt_field);
		if($bill_date !== false) $db_payment->billdate = $bill_date;
		if($bill_received_date !== false) $db_payment->billreceiveddate = $bill_received_date;
		$db_payment->netto = $net;
		$db_payment->brutto = $gross;
		if ($skonto_id > 0)          $db_payment->SKONTO_ID = $skonto_id;
		if (strlen($sday1))          $db_payment->sday1 = $sday1;
		if (strlen($skonto1))        $db_payment->skonto1 = $skonto1;
		if (strlen($sday2))          $db_payment->sday2 = $sday2;
		if (strlen($skonto2))        $db_payment->skonto2 = $skonto2;
		if (strlen($net_days))       $db_payment->nettoday = $net_days;
		if ($payment_mode_id > 0)    $db_payment->paymentmode = $payment_mode_id;
		if ($payment_method_id > 0)  $db_payment->payment_method_idpayment_method = $payment_method_id;
		if ($kreditor_id)            $db_payment->Customer_CUSTOMER_ID = $kreditor_id;
		if ($currency_id)            $db_payment->currency_idcurrency = $currency_id;
		
		if ($booking_date !== false) $db_payment->booking_date = $booking_date;
		/*if (strlen($foreign_tax))*/    $db_payment->vat          = $foreign_tax;
		/*if (strlen($tax_id))*/         $db_payment->vatno        = $tax_id;
		
		$db_payment->state = $state;
		$db_payment->dir = $dir;
		
		// store payment date and amount
		// case: outgoing bill		
		if ( $payment_value_id and $bill_id) {
			
			require_once(INCLUDEPATH . 'flar/Bill_saved_receipt.php');
			$db_bill_receipt = new Flar_Bill_saved_receipt();
			$db_bill_receipt->get( $payment_value_id );
			$db_bill_receipt->receipt_amount = $payment_value;
			$db_bill_receipt->receipt_date   = $payment_date;
			$db_bill_receipt->update();
			$db_bill_receipt->free();
		} else if ( $payment_value_id===0 and $bill_id) {
			
			// store payment date and amount directly into the bill
			// (because there is not payment value id)
			require_once(INCLUDEPATH . 'flar/Bill_saved_head.php');
			$bsh = new Flar_Bill_saved_head();
			$bsh->get($bill_id);
			$bsh->bill_receipt      = $payment_value;
			$bsh->bill_receipt_date = $payment_date;
			$bsh->update();
			$bsh->free();
		} /*else if ( $bo_objects_data_id ) {
			//echo "hallo";
			require_once(INCLUDEPATH . 'flar/Bo_objects_data.php');
			$db_bo_data = new Flar_Bo_objects_data();
			$db_bo_data->idbo_objects_data = $bo_objects_data_id;
			$db_bo_data->get();
			$db_bo_data->receipt_date = $payment_date;
			$db_bo_data->receipt      = $payment_value;
			$db_bo_data->update();
			$db_bo_data->free();
			
		} */else {
			if($payment_date !== false) $db_payment->paymentdate = $payment_date;	
			if($payment_value) $db_payment->payment_value = $payment_value;
		}
		
				
		if((int)$payment_id == 0)
		{
			$db_payment->created_person = $_SESSION['MGW']->userid;
			$db_payment->created = time();
		
			$new_entry_id = $db_payment->insert();
			
			if($new_entry_id > 0)
			{
				// save Link to document
				if((int)$attachment_id > 0)
				{
					$db_payment_has_attachment = new Flar_Payment_has_attachment();
					$db_payment_has_attachment->payment_idpayment = $new_entry_id;
					$db_payment_has_attachment->attachment_ID = (int)$attachment_id;
					$db_payment_has_attachment->insert();
				}
				
				// save Link to bo objects data
				if((int)$bo_objects_data_id > 0)
				{
					$db_payment_has_bood = new Flar_Payment_has_bo_objects_data();
					$db_payment_has_bood->payment_idpayment = $new_entry_id;
					$db_payment_has_bood->bo_objects_data_idbo_objects_data = (int)$bo_objects_data_id;
					$db_payment_has_bood->insert();
				}
				
				// save Link to bill
				if((int)$bill_id > 0)
				{
					$db_payment_has_bsh = new Flar_Payment_has_bill_saved_head();
					$db_payment_has_bsh->payment_idpayment = $new_entry_id;
					$db_payment_has_bsh->bill_saved_head_BILL_SAVED_HEAD_ID = (int)$bill_id;
					$db_payment_has_bsh->insert();
				}
				return $new_entry_id;
			}
			else
			{
				return false;
			}
		}
		else
		{
			$db_payment->lastmodified_person = $_SESSION['MGW']->userid;
			$db_payment->last_modified= time();
			
			if($db_payment->update() !== false)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		
		
	//}
	//else
	//{
	//	return false;
	//}
	
	
	
}

/**
 * Fragt die Liste der Zahlungsmethoden ab
 *	- sortiert nach dem Titel Aufsteigend
 *
 * @author Philipp Wabinski (06.11.2007)<philipp-wabinski@pepe-web.de>
 * 
 * @param $encode (bool) Kodieren der Textdaten zur Uebertragung per JSON-RPC
 * 
 * @return array result[i]["*"]<br>
 * - "id" => Id der Zahlungsmethode
 * - "title" => Titel der Zahlungsmethode
 */
function get_payment_method_list($encode)
{
	require_once(INCLUDEPATH . 'flar/Payment_method.php');
	
	# Skontoloesungen
	$payment_method = new Flar_Payment_method();
	$payment_method->orderBy('title');
	$payment_method->find();

	while($payment_method->fetch())
	{
		// schreiben der Daten in Array fuer die Ausgabe
		$method_list[] = array( 'id' => $payment_method->idpayment_method,
					   'title' => ($encode) ? urlencode($payment_method->title) : $payment_method->title);
	}
	
	if(count($method_list))
	{
		return $method_list;
	}
	else
	{
		return false;
	}
}

/**
 * Fragt Daten einer Zahlung ab
 *	- Verwendung in Bearbeitungsformular
 *
 * @author Philipp Wabinski (06.11.2007)<philipp-wabinski@pepe-web.de>
 * 
 * @param $encode (bool) Kodieren der Textdaten zur Uebertragung per JSON-RPC
 * 
 * @param $payment_id (int) ID der Zahlung
 * 
 * @param $payment_id (int) document id if given the first payment id is queried 
 * 
 * @return array result["*"]<br>
 * - "top_category_id" => ID der Top-Kategorie
 * - "main_category_id" => ID der Main-Kategorie
 * - "category_id" => ID der Kategorie
 * - "customer_id" => ID des ausgew. Auftraggebers
 * - "costcenter_id" => ID der KSt.
 * - "account_number" => Nummer des Kontos
 * - "payment_tax_key_number" => Nummer des Buchungsschluessels
 * - "receipt_field" => belegfeld
 * - "bill_date" => Rechnungsdatum "2007-12-31" oder flase falls nicht vorhanden
 * - "bill_received_date" => Rechnungseingangsdatum "2007-12-31" oder flase falls nicht vorhanden
 * - "gross" => Brutto-Summe
 * - "net" => Netto-Summe
 * - "skonto_id" => Skonto_ID
 * - "sday1" => Skonto 1 Tage
 * - "skonto1" => Skonto 1 %
 * - "sday2" => Skonto 1 Tage
 * - "skonto2" => Skonto 2 %
 * - "nettoday" => Nettotage
 * - "payment_mode_id"  => ID fuer den Zahlungsmodus
 * - "payment_date" => Datum der Zahlung "2007-12-31" oder flase falls nicht vorhanden
 * - "payment_method_id" => ID der Zahlungsmethode
 */
function get_single_payment_data($encode, $payment_id, $document_id)
{
	require_once(INCLUDEPATH . 'flar/Payment.php');
	require_once(INCLUDEPATH . 'flar/Payment_main_categories.php');
	require_once(INCLUDEPATH . 'flar/Payment_categories.php');
	require_once(INCLUDEPATH . 'flar/Costcenter.php');
	require_once(INCLUDEPATH . 'flar/Payment_accounts.php');
	require_once(INCLUDEPATH . 'flar/Payment_tax_key.php');
	require_once(INCLUDEPATH . 'flar/Payment_has_attachment.php');
	
	if($document_id > 0)
	{
		$payment_has_attachment = new Flar_Payment_has_attachment();
		$payment_has_attachment->attachment_ID = $document_id;
		$payment_has_attachment->orderBy("payment_idpayment DESC");
		$payment_has_attachment->find(true);
		
		$payment_id = $payment_has_attachment->payment_idpayment;
	}


	$payment = new Flar_Payment();
	$payment->get($payment_id);
	$payment->find(true);
	
	if($payment->idpayment > 0)
	{
		// Katgorien ermitteln	
		if($payment->payment_categories_idpayment_categories > 0)
		{
			$category = new Flar_Payment_categories();
			$category->get($payment->payment_categories_idpayment_categories);
			$category->find(true);
			
			$return_array["category_id"] = $payment->payment_categories_idpayment_categories;
			$return_array["main_category_id"] = $category->payment_main_categories_idpayment_main_categories;
			
			// Top-Kategorie ermitteln
			$main_category = new Flar_Payment_main_categories();
			$main_category->get($category->payment_main_categories_idpayment_main_categories);
			$main_category->find(true);
			
			$return_array["top_category_id"] = $main_category->payment_top_categories_idpayment_top_categories;
		}
		else
		{
			$return_array["category_id"] = 0;
			$return_array["main_category_id"] = 0;
			$return_array["top_category_id"] = 0;
		}
		
		// Kst. - Agg. ermitteln
		if($payment->Costcenter_COSTCENTER_ID > 0)
		{
			$return_array["costcenter_id"] = $payment->Costcenter_COSTCENTER_ID;
			
			// Top-Kategorie ermitteln
			$costcenter = new Flar_Costcenter();
			$costcenter->get($payment->Costcenter_COSTCENTER_ID);
			$costcenter->find(true);
			
			$return_array["customer_id"] = $costcenter->Customer_CUSTOMER_ID;
		}
		else
		{
			$return_array["costcenter_id"] = 0;
			$return_array["customer_id"] = 0;
		}
		
		// Nummer des Kontos
		if($payment->payment_accounts_idpayment_accounts > 0)
		{
			$account = new Flar_Payment_accounts();
			$account->get($payment->payment_accounts_idpayment_accounts);
			$account->find(true);
			
			$return_array["account_number"] = $account->number;
		}
		else
		{
			$return_array["account_number"] = "";
		}
		
		// Nummer des Kontos
		if($payment->payment_tax_key_idpayment_tax_key > 0)
		{
			$tax_key = new Flar_Payment_tax_key();
			$tax_key->get($payment->payment_tax_key_idpayment_tax_key);
			$tax_key->find(true);
			
			$return_array["payment_tax_key_number"] = $tax_key->number;
		}
		else
		{
			$return_array["payment_tax_key_number"] = "";
		}
		
		$return_array["payment_id"]        = $payment_id;
		$return_array["receipt_field"]     = ($encode) ? urlencode($payment->belegno) : $payment->belegno;
		$return_array["bill_date"]         = (strlen($payment->billdate)) ? $payment->billdate : false;
		$return_array["bill_received_date"] = (strlen($payment->billreceiveddate)) ? $payment->billreceiveddate : false;
		$return_array["gross"]             = $payment->brutto;
		$return_array["net"]               = $payment->netto;
		$return_array["skonto_id"]         = ($payment->SKONTO_ID > 0) ? $payment->SKONTO_ID : 0;
		$return_array["sday1"]             = $payment->sday1;
		$return_array["skonto1"]           = $payment->skonto1;
		$return_array["sday2"]             = $payment->sday2;
		$return_array["skonto2"]           = $payment->skonto2;
		$return_array["nettoday"]          = $payment->nettoday;
		$return_array["payment_mode_id"]   = $payment->paymentmode;
		$return_array["kreditor_id"]       = $payment->Customer_CUSTOMER_ID;
		$return_array["payment_date"]      = (strlen($payment->paymentdate)) ? $payment->paymentdate : false;
		$return_array["payment_method_id"] = ($payment->payment_method_idpayment_method > 0) ? $payment->payment_method_idpayment_method : 0;
		$return_array["receipt_number"]    = ($encode) ? urlencode($payment->belegnr) : $payment->belegnr;
		$return_array["state"]             = $payment->state;
		$return_array["dir"]               = $payment->dir;
		
		// NEUE WERTE
		$return_array["tax_id"]            = ($encode) ? urlencode($payment->vatno) : $payment->vatno;
		$return_array["foreign_tax"]       = ($payment->vat) ? $payment->vat : false;
		$return_array["booking_date"]      = (strlen($payment->booking_date)) ? $payment->booking_date : false;
		$return_array["currency_id"]       = $payment->currency_idcurrency;
		
		return $return_array;
	}
	else
	{
		return false;
	}
}

/**
 * Loescht alle Zahlungseintraege mit den uebergeben Ids
 *
 * @author Philipp Wabinski (07.11.2007)<philipp-wabinski@pepe-web.de>
 * 
 * @return true
 */
function get_delete_payment($payment_id_arr)
{
	require_once(INCLUDEPATH . 'flar/Payment.php');
	require_once(INCLUDEPATH . 'flar/Payment_has_bo_objects_data.php');
	require_once(INCLUDEPATH . 'flar/Payment_has_attachment.php');
	
	# Skontoloesungen
	$payment                        = new Flar_Payment();
	$db_payment_has_bo_objects_data = new Flar_Payment_has_bo_objects_data();
 	$attachments                    = new Flar_Payment_has_attachment();

	for($i = 0; $i < count($payment_id_arr); $i++)
	{
		$payment->whereAdd('idpayment= '.$payment_id_arr[$i], 'OR');
		$db_payment_has_bo_objects_data->whereAdd('payment_idpayment= '.$payment_id_arr[$i], 'OR');
		$attachments->whereAdd('payment_idpayment= '.$payment_id_arr[$i], 'OR');		
	}
	
	$payment->delete(true);
    $db_payment_has_bo_objects_data->delete(true);
    $attachments->delete(true);    
    
	$payment->free();
    $db_payment_has_bo_objects_data->free();
    $attachments->free();
    
	return true;
}


/**
 * adds new link between bill and payment
 *
 * @author Claas Richter 05.02.2008
 * 
 * @param $payment_id - payment id
 * @param $bill_id    - bill id
 * 
 * @return true
 */
function add_new_link_payment_bill($payment_id, $bill_id)
{
	require_once(INCLUDEPATH . 'flar/Payment_has_bill_saved_head.php');
	$data = new Flar_Payment_has_bill_saved_head();
	
	$data->payment_idpayment                  = $payment_id;
	$data->bill_saved_head_BILL_SAVED_HEAD_ID = $bill_id;
	$data->insert();
	$data->free();
}

/**
 * adds new link between business_object_data and payment
 *
 * @author Claas Richter 22.03.2008
 * 
 * @param $payment_id              - payment id
 * @param $business_object_data    - business_object_data id
 * 
 * @return true
 */
function add_new_link_payment_business_object_data($payment_id, $business_object_data)
{
	require_once(INCLUDEPATH . 'flar/Payment_has_bo_objects_data.php');
	$data = new Flar_Payment_has_bo_objects_data();
	
	$data->payment_idpayment                 = $payment_id;
	$data->bo_objects_data_idbo_objects_data = $business_object_data;
	$data->insert();
	$data->free();

}

/**
 * erase payments for a business_object_data
 *
 * @author Claas Richter 2008-04-03
 * 
 * @param $business_object_data    - business_object_data id
 * 
 * @return true
 */
function erase_payments_for_business_object_data( $business_object_data )
{
	require_once(INCLUDEPATH . 'flar/Payment_has_bo_objects_data.php');
	require_once(INCLUDEPATH . 'flar/Payment.php');
	
	$data = new Flar_Payment_has_bo_objects_data();
	
	//$data->payment_idpayment                 = $payment_id;
	$data->bo_objects_data_idbo_objects_data = $business_object_data;
	$data->find();
	
	while( $data->fetch() )
	{
		$payment = new Flar_Payment();
		$payment->get( $data->payment_idpayment );
		$payment->delete();
		$payment->free();
		
		$data->delete();
	}
	
	$data->free();

}

/**
 * check if confirmed payment exists for business object data
 *
 * @author Claas
 * 
 * @param $business_object_data    - business_object_data id
 * 
 * @return true
 */
function check_confirmed_payment_for_business_object_data( $business_object_data )
{
	require_once(INCLUDEPATH . 'flar/Payment_has_bo_objects_data.php');
	require_once(INCLUDEPATH . 'flar/Payment.php');
		
	$db_payment_has_bo_objects_data = new Flar_Payment_has_bo_objects_data();
    $payment                        = new Flar_Payment();
    
 $query = "SELECT *
              FROM `".$db_payment_has_bo_objects_data->__table."` as phbo
              LEFT OUTER JOIN `".$payment->__table."` as payment
              ON phbo.payment_idpayment = payment.idpayment 
              WHERE payment.state = 1";

 $db_payment_has_bo_objects_data->query( $query );
 
 return $db_payment_has_bo_objects_data->fetch();
 	
 $db_payment_has_bo_objects_data->free();
 
}


/**
 * creates new payment which is linked with bill
 *
 * @author Claas Richter 05.02.2008
 * 
 * @param $bill_id - bill id
 * 
 * @return true
 */

function create_new_payment_for_bill($bill_id)
{
	require_once(INCLUDEPATH . 'flar/functions/db_bill.php');
	require_once(INCLUDEPATH . 'flar/Costcenter.php');
	
	// set bill to state "ok"
	$bill_data = bill_ok($bill_id);
	
	// get skonto data
	$skonto_data = new Flar_Skonto();
	$skonto_data->get($bill_data->SKONTO_ID);
	//print_r($skonto_data);
	
	// get type: netto or gross
	$costcenter =new Flar_Costcenter();
	$costcenter->get($bill_data->Costcenter_COSTCENTER_ID);
	
	if ($costcenter->bill_type) {
		// gross
		$account_number         = 4400;
		$payment_tax_key_number = 3;
	} else {
		// netto
		$account_number         = 4337;
		$payment_tax_key_number = 0;
	}
	$costcenter->free();
	
  	// create new payment
  	//$receive_date = date('Y',time()).'-'.date('m',time()).'-'.date('d',time());	
	$payment_id = save_payment(	0, 
	                16, 
	                $bill_data->Costcenter_COSTCENTER_ID,
	                $account_number,	
	                $payment_tax_key_number, 
	                $bill_data->project, 
	                $bill_data->bill_date,
	                $bill_data->bill_date,
	                $bill_data->netto,
	                $bill_data->brutto,
	                $bill_data->SKONTO_ID,
	                $skonto_data->sday1,
	                $skonto_data->skonto1,
	                $skonto_data->sday2,
	                $skonto_data->skonto2,$skonto_data->nettoday,
	                0,
	                1,
	                1,
	                0,
	                0,
	                0,
	                0,
	                $bill_data->bill_no,
	                0 );
						
	$skonto_data->free();

	// add new link between bill and payment
	add_new_link_payment_bill($payment_id, $bill_id);
	
}

/**
 * erases all payments for one bill and their links
 *
 * @author Claas Richter 05.02.2008
 * 
 * @param $bill_id - bill id
 * 
 * @return true
 */
function delete_payments_for_bill($bill_id)
{
	require_once(INCLUDEPATH . 'flar/Payment.php');
	require_once(INCLUDEPATH . 'flar/Payment_has_bill_saved_head.php');
	
	$data = new Flar_Payment_has_bill_saved_head();
	$data->bill_saved_head_BILL_SAVED_HEAD_ID = $bill_id;
	
	$data->find();

	while($data->fetch())
	{
		// erase payment
		$db_payment = new Flar_Payment();
		$db_payment->get($data->payment_idpayment);
		$db_payment->delete();
		$db_payment->free();
		
		// erase link
		$data->delete();
	}
	
	$data->free();
	
}

/**
 * set state to "archive" 
 * for all bills which are linked to the payment
 *
 * @author Claas Richter 21.03.2008
 * 
 * @param $payment_id - payment id
 * 
 * @return true
 */
function set_bill_state( $payment_id, $state )
{
	require_once(INCLUDEPATH . 'flar/Payment_has_bill_saved_head.php');
	require_once(INCLUDEPATH . 'flar/Bill_saved_head.php');
	
	//echo $payment_id;
	
	$db_payment = new Flar_Payment_has_bill_saved_head();
	$db_payment->payment_idpayment = $payment_id;
	
	$db_payment->find();

	while($db_payment->fetch())
	{
		//echo $state;
		$db_bill = new Flar_Bill_saved_head();
		$db_bill->get( $db_payment->bill_saved_head_BILL_SAVED_HEAD_ID );
		$db_bill->state = $state;
		$db_bill->update();
		$db_bill->free();
	}
	
	$db_payment->free();
	
}


/**
 * creates new payments for a business process data which is linked with business object data
 *
 * @author Claas Richter 05.02.2008
 * 
 * @param $bill_id - bill id
 * 
 * @return true
 */

function create_new_payment_for_business_process_data( $bo_process_data_id )
{
	require_once(INCLUDEPATH . 'flar/Bo_objects_data.php');
	require_once(INCLUDEPATH . 'flar/Bo_process_data.php');
	require_once(INCLUDEPATH . 'flar/Costcenter.php');
	
	// get costcenter of the business process data to which the business object data is being linked
	$db_bo_process_data = new Flar_Bo_process_data();
	$db_bo_process_data->get( $bo_process_data_id );
	
	// loop for all business object data which linked to the process data
	$db_bo_object_data  = new Flar_Bo_objects_data();
	
	$db_bo_object_data->bo_process_data_idbo_process_data = $bo_process_data_id;
	$db_bo_object_data->bo_objects_idbo_objects = 1090;
	
	
	//echo $bo_process_data_id." ";
	$db_bo_object_data->find();

	$receive_date = date('Y',time()).'-'.date('m',time()).'-'.date('d',time());	
	$account_number         = '';
	$payment_tax_key_number = '';
	
	/*
	if ($costcenter->bill_type) {
		// gross
		$account_number         = 4400;
		$payment_tax_key_number = 3;
	} else {
		// netto
		$account_number         = 4337;
		$payment_tax_key_number = 0;
	}
	*/
	
	while($db_bo_object_data->fetch())
	{
			//echo "create payment";
			//echo "<br>Kreditor: ".$db_bo_process_data->Customer_CUSTOMER_ID;
		  	// create new payment
			$payment_id = save_payment(	0, 
	                3, 
	                $db_bo_process_data->Costcenter_COSTCENTER_ID,
	                $account_number,	
	                $payment_tax_key_number, 
	                null,
	                $receive_date,
	                $receive_date,
	                $db_bo_object_data->netto,
	                null,
	                null,
	                null,
	                null,
	                null,
	                null,
	                null,
	                0,
	                1,
	                0,
	                $db_bo_process_data->Customer_CUSTOMER_ID,
	                0,
	                0,
	                0,
	                0,
	                0,
	                0 );
						
	// add new link between bill and payment
	add_new_link_payment_business_object_data( $payment_id, $db_bo_object_data->idbo_objects_data );
		
		
	}	
	
	$db_bo_object_data->free();
	$db_bo_process_data->free();
	
}

/**
 * get list of linked payments of a payment
 * 
 * - acording to the given data
 * 
 * @author Philipp Wabinski 10.04.2008
 * 
 * @param $payment_id (int) payment_id
 * @param $bo_object_data_id (int) id of business objects data
 * @param $bill_id (int) bill id
 * 
 */

function get_linked_payment_list( $payment_id, $bo_object_data_id, $bill_id)
{
  // echo $payment_id." ".$bo_object_data_id." ".$bill_id;
  // get receipts from bill	
  if ($bill_id>0) {
	require_once(INCLUDEPATH . 'flar/Bill_saved_receipt.php');
	$db_bill_receipt = new Flar_Bill_saved_receipt();
	
	// case: bill_id
	//if ( $bo_object_data_id ==  $bill_id)
	   // if there are entries in table bill_saved_receipt then return them
	   // otherwise return the data and about stored in the table bill_saved_head
	   
	//$db_bill_receipt->bill_saved_head_BILL_SAVED_HEAD_ID = (int)$bill_id;
	$db_bill_receipt->bill_saved_head_BILL_SAVED_HEAD_ID = $bill_id;
	//echo $payment_id." ".$bo_object_data_id." ".$bill_id;
	
	$result = array();
	if ( $db_bill_receipt->find() )
	{
		while( $db_bill_receipt->fetch() ) {

			if ($db_bill_receipt->receipt_date) {
			  $result[] = array("payment_id" => $db_bill_receipt->idbill_saved_receipt,
					        "payment_date" => $db_bill_receipt->receipt_date,
					       "payment_value" => $db_bill_receipt->receipt_amount);	
			}
			//echo "id ".$db_bill_receipt->idbill_saved_receipt." ".$db_bill_receipt->receipt_date." ".$db_bill_receipt->receipt_amount;
		}
	} else {
	 	require_once(INCLUDEPATH . 'flar/Bill_saved_head.php');
	 	
	 	$db_bill = new Flar_Bill_saved_head();
		$db_bill->get($bill_id);
		
		if ($db_bill->bill_receipt_date)
		{
		  $result[] = array("payment_id" => 0,
			  		      "payment_date" => $db_bill->bill_receipt_date,
					     "payment_value" => $db_bill->bill_receipt);
		}
		$db_bill->free();
	}
	
	
	// return dummy data
	/*
	$result[] = array("payment_id" => 1,
					 "payment_date" => "2007-08-04",
					 "payment_value" => 20.56);
					 
	$result[] = array("payment_id" => 2,
					 "payment_date" => "2007-12-01",
					 "payment_value" => 10220.56);	
					 			 
	$result[] = array("payment_id" => 3,
					 "payment_date" => "2009-05-24",
					 "payment_value" => 0.56);	
		*/
	// return false; (if no entries found)
} if ( $bo_object_data_id > 0) {
	
	// get receipts from business object
	require_once(INCLUDEPATH . 'flar/Bo_objects_data.php');
	$db_bo_objects_data = new Flar_Bo_objects_data();
	$db_bo_objects_data->get( $bo_object_data_id );
	
	if ( $db_bo_objects_data->receipt_date ) {
	  $result[] = array("payment_id" => 0,
				      "payment_date" => $db_bo_objects_data->receipt_date,
			         "payment_value" => $db_bo_objects_data->receipt );
	}
	$db_bo_objects_data->free();
	
} else {
	require_once(INCLUDEPATH . 'flar/Payment.php');
	$db_payment = new Flar_Payment();
	$db_payment->get($payment_id);
	
	if ($db_payment->paymentdate) {
	  $result[] = array("payment_id" => 0,
				    	"payment_date" => $db_payment->paymentdate,
			       		"payment_value" => $db_payment->payment_value );
	}
	
}

	if(!count($result)) return false;	
					 
	return $result;
}
/**
 * load data for line bar graph detail
 * @author hiepnv
 * @param $params[0] first monday
 * @param $params[1] (int) project_id
 * @param $params[2] (int) widget id
 * 
 */
function get_table_detail($firt_monday, $project_id, $widget_id){
	
	$return_array = array();
		
		for($j = 0; $j < 5; $j++)
		{
			$i = $j*2500;
			
			$return_array[] =array( 'id'			=>$j,
									'title'			=>"test 1",
									'first_mon'		=>356+$i,
									'first_tue'		=>152+$i,
									'first_wed'		=>152+$i,
									'first_thu'		=>458+$i,
									'first_fri'		=>485+$i,
									'first_sat'		=>487+$i,
									'first_sun'		=>698+$i,
									'second_mon'	=>478+$i,
									'second_tue'	=>458+$i,
									'second_wed'	=>699+$i,
									'second_thu'	=>578+$i,
									'second_fri'	=>257+$i,
									'second_sat'	=>668+$i,
									'second_sun'	=>778+$i,
									'third_mon'		=>698+$i,
									'third_tue'		=>357+$i,
									'third_wed'		=>258+$i,
									'third_thu'		=>789+$i,
									'third_fri'		=>896+$i,
									'third_sat'		=>789+$i,
									'third_sun'		=>784+$i,
									
								);	
		}
		
//		print_r($return_array);												
		
	  	return $return_array;
	
}
?>