se/SE/superedit-USERS2_OFFERS_COSTS_SALES_PLAN.php

<?php


function USERS2_OFFERS_COSTS_SALES_PLAN() {
	global $ARG1;
	$params = array();

	require_once dirname(__FILE__) . '/' . 'se-lib' . '/' . 'Lib.php';
	Lib::loadClass('V');
	Lib::loadClass('DB');

	$params = array();
	$params['toolbar'] = array();
	$params['toolbar'] []= '<a href="' . "?MENU_INIT=USERS2_OFFERS_COSTS_SALES_PLAN" . "&ARG1=ROZLICZ" . '">' . "ROZLICZ" . '</a>';
	//$params['toolbar'] []= '<a href="' . "?MENU_INIT=USERS2_OFFERS_COSTS_SALES_PLAN" . "&ARG1=DEALS_CHECK" . '">' . "DEALS_CHECK" . '</a>';
	$params['toolbar'] []= '<a href="' . "?MENU_INIT=USERS2_OFFERS_COSTS_SALES_PLAN" . "&ARG1=DEALS_CHECK_END" . '" title="' . "Umowy - daty zakończenia" . '">' . "DEALS_END" . '</a>';
	$params['toolbar'] []= '<a href="' . "?MENU_INIT=USERS2_OFFERS_COSTS_SALES_PLAN" . "&ARG1=DEALS_CHECK_REZ" . '" title="' . "Umowy - rezygnacje" . '">' . "DEALS_REZ" . '</a>';
	$params['toolbar'] []= '<a href="' . "?MENU_INIT=USERS2_OFFERS_COSTS_SALES_PLAN" . "&ARG1=DEALS_CHECK_WYP" . '" title="' . "Umowy - rezygnacje wyprowadzka" . '">' . "DEALS_WYP" . '</a>';

	// update deals status table
	$update = false;
	if (V::get('_update_stats', 0, $_GET, 'int') == 1) {
		$update = true;
		if (V::get('DBG', 0, $_GET, 'int') > 1) {
			echo '<p>'."Update deals stats...".'</p>';
		}
	}
	$dbg_msgs = array();
	Lib::loadClass('ColumnDealsStatus');
	ColumnDealsStatus::run_update($update, $dbg_msgs);
	if (V::get('DBG', 0, $_GET, 'int') > 1) {
		echo'<pre style="max-height:200px;overflow:auto;border:1px solid red;">update dbg_msgs: ';print_r($dbg_msgs);echo'</pre>';
	}

	switch ($ARG1) {
		case 'ROZLICZ': {
			USERS2_OFFERS_COSTS_SALES_PLAN__ROZLICZ( $params );
			break;
		}
		case 'DEALS_CHECK': {
			USERS2_OFFERS_COSTS_SALES_PLAN__DEALS_CHECK( $params );
			break;
		}
		case 'DEALS_CHECK_END': {
			USERS2_OFFERS_COSTS_SALES_PLAN__DEALS_CHECK_END();
			break;
		}
		case 'DEALS_CHECK_REZ': {
			USERS2_OFFERS_COSTS_SALES_PLAN__type('DEALS_CHECK_REZ', $params);
			break;
		}
		case 'DEALS_CHECK_WYP': {
			USERS2_OFFERS_COSTS_SALES_PLAN__type('DEALS_CHECK_WYP', $params);
			break;
		}
		default:
			WIEVTABLE( $params );
	}
}


function USERS2_OFFERS_COSTS_SALES_PLAN_EDIT() {
	EDIT_TABLE_RECORD();
}


function USERS2_OFFERS_COSTS_SALES_PLAN_HIST() {
	WIEVTABLE_HIST();
}


function USERS2_OFFERS_COSTS_SALES_PLAN__ROZLICZ( $params = array() ) {
	SEF("MENU");
	MENU();
	if (!empty($params['toolbar'])) {
		echo '<p>' . implode(" | ", $params['toolbar']) . '</p>';
	}

	echo '<h1>' . "Rozliczenie:" . '</h1>';

	// frm
	$frm = new stdClass();
	$frm->sent = false;
	$frm->method = 'GET';
	$frm->display_field_sep = " ";
	$frm->f['f_data'] = array('type'=>'select', 'values'=>array(), 'label'=>"Wybierz miesiąc");
	$frm->f['f_user'] = array('type'=>'select', 'values'=>array(), 'label'=>"wybierz pracownika");
	foreach ($frm->f as $k_field_name => $v_field) {
		switch ($frm->method == 'POST') {
			case 'POST':
				$frm->v[$k_field_name] = V::get($k_field_name, '', $_POST);
				break;
			case 'GET':
				$frm->v[$k_field_name] = V::get($k_field_name, '', $_GET);
				break;
			default:
				$frm->v[$k_field_name] = V::get($k_field_name, '', $_REQUEST);
		}
	}//end foreach

	// frm - set values
	$sql = "select a.`T_WORKPOINTS_MONTH`
		from `USERS2_OFFERS_COSTS_SALES_PLAN` as a
		where `A_STATUS`!='DELETED'
		group by a.`T_WORKPOINTS_MONTH`
		order by a.`T_WORKPOINTS_MONTH` desc
	";
	Lib::loadClass('DB');
	$res = DB::query( $sql );
	while ($r = DB::fetch( $res )) {
		$frm->f['f_data']['values'][$r->T_WORKPOINTS_MONTH] = $r->T_WORKPOINTS_MONTH;
	}
	$sql = "select a.`T_WORKPOINTS_USER`
		from `USERS2_OFFERS_COSTS_SALES_PLAN` as a
		where `A_STATUS`!='DELETED'
		group by a.`T_WORKPOINTS_USER`
		order by a.`T_WORKPOINTS_USER` asc
	";
	Lib::loadClass('DB');
	$res = DB::query( $sql );
	while ($r = DB::fetch( $res )) {
		$frm->f['f_user']['values'][$r->T_WORKPOINTS_USER] = $r->T_WORKPOINTS_USER;
	}

	// frm - validation
	foreach ($frm->f as $k_field_name => $v_field) {
		switch ($v_field['type']) {
			case 'select':
				if (!array_key_exists($frm->v[$k_field_name], $frm->f[$k_field_name]['values'])) {
					//TODO: $frm->v[$k_field_name] = '';
				}
				break;
			default:// input type="text"
		}
	}

	// frm - sent
	$frm->sent = ($frm->v['f_data'] != '' && $frm->v['f_user'] != '');

	// frm - display
	echo '<form action="" method="' . $frm->method . '">';
		foreach ($frm->f as $k_field_name => $v_field) {
			if (isset($v_field['label'])) {
				echo $v_field['label'] . " ";
			}
			switch ($v_field['type']) {
				case 'select':
					echo '<select name="' . $k_field_name . '">';
					echo '<option>' . " [ wybierz ] " . '</option>';
					foreach ($v_field['values'] as $k_option => $v_value) {
						$sel = ($k_option == $frm->v[$k_field_name])? ' selected="selected"' : '';
						echo '<option value="' . $k_option . '"' . $sel . '>' . $v_value . '</option>';
					}//end foreach
					echo '</select>';
					break;
				default:// input type="text"
					echo '<input type="text" name="' . $k_field_name . '" value="' . $frm->v[$k_field_name] . '" />';
			}
			echo $frm->display_field_sep;
		}//end foreach
		// add hidden fields if GET
		if ($frm->method == 'GET') {
			foreach ($_GET as $k => $v) {
				if (!array_key_exists($k, $frm->f)) {
					echo '<input type="hidden" name="' . $k . '" value="' . $v . '" />';
				}
			}
		}
		echo '<input type="submit" value="' . "OK" . '" />';
	echo '</form>';

	echo '<br />';
	echo '<br />';

	// frm - action
	if ($frm->sent) {
		echo '<p>' . "TODO: rozliczenie " . $frm->v['f_user'] . " w miesiącu " . $frm->v['f_data'] . " ..." . '</p>';
	}
}


function USERS2_OFFERS_COSTS_SALES_PLAN__DEALS_CHECK( $params = array() ) {
	SEF("MENU");
	MENU();
	if (!empty($params['toolbar'])) {
		echo '<p>' . implode(" | ", $params['toolbar']) . '</p>';
	}

	echo '<style type="text/css">' . "
.tbl-view {}
 .tbl-view th {padding:0 2px;}
 .tbl-view td {padding:0 2px;}
.tbl-small {font-size:small;}
 .tbl-small th {padding:0 2px; font-size:small;}
 .tbl-small td {padding:0 2px; font-size:small;}
	" . '</style>';

	$deals = array();

	//$sql_month = date('Y-m');
	$sql_month = date('Y-m', mktime(0,0,0,date('m') - 1, date('d'), date('Y')));

	// pobierz z USERS2_DEALS tylko NORMAL
	$sql = "select
			d.`ID`
	--		, d.`M_DIST_FILES`
			, d.`A_RECORD_CREATE_DATE`
			, d.`A_RECORD_CREATE_AUTHOR`
			, d.`A_RECORD_UPDATE_DATE`
			, d.`A_RECORD_UPDATE_AUTHOR`
	--		, d.`TAB_UPDATE_STAT`
	--		, d.`TAB_UPDATE_DATE`
	--		, d.`TAB_UPDATE_INFO`
	--		, d.`L_APPOITMENT_DATE`
	--		, d.`L_APPOITMENT_USER`
	--		, d.`L_APPOITMENT_PERIOD`
	--		, d.`L_APPOITMENT_INFO`
	--		, d.`L_APPOITMENT_TYPE`
	--		, d.`A_PROBLEM`
	--		, d.`A_PROBLEM_DESC`
	--		, d.`A_PROBLEM_DATE`
			, d.`A_STATUS`
	--		, d.`A_STATUS_CURRENT`
	--		, d.`A_STATUS_INFO`
	--		, d.`A_STATUS_LASTCHANGE_DATE`
	--		, d.`A_UPDATE_DATE`
	--		, d.`A_ADM_COMPANY`
			, d.`DATA_DOK`
	--		, d.`S_ALIAS`
	--		, d.`S_ADDRESS_CITY`
			, d.`S_ADDRESS_STREET`
			, d.`S_OTHER_INFO`
	--		, d.`T_WORKPOINTS`
	--		, d.`T_WORKPOINTS_PROCES`
	--		, d.`T_WORKPOINTS_VALUE`
	--		, d.`T_WORKPOINTS_TYPE`
	--		, d.`T_WORKPOINTS_USER`
	--		, d.`T_WORKPOINTS_DATE`
	--		, d.`T_PENALTIES`
	--		, d.`T_PENALTIES_TYPE`
	--		, d.`T_PENALTIES_VALUE`
			, d.`ID_BILLING_USERS_OLD`
			, d.`ID_BILLING_USERS_NEW`
			, d.`ID_SERVICES_OLD`
			, d.`ID_SERVICES_NEW`
			, d.`P_SERVICE`
			, d.`P_SERVICE_COMPANY`
			, d.`P_SERVICE_NAME`
			, d.`P_OFFER_ID_OLD`
			, d.`P_OFFER_ID_NEW`
			, d.`P_DEAL_DATE_OLD`
			, d.`P_DEAL_DATE_NEW`
			, d.`P_DEALNUMBER_OLD`
			, d.`P_DEALNUMBER_NEW`
			, d.`P_DEALTERM_OLD`
			, d.`P_DEALTERM_NEW`
			, d.`MONTH_COST_OLD`
			, d.`MONTH_COST_NEW`
			, d.`INICJALY_HANDLOWCA`
			, d.`RODZAJ_DZIALANIA_HANDLOWEGO`
		from `USERS2_DEALS` as d
		where
			d.`A_STATUS`='NORMAL'
			and d.`DATA_DOK` like '" . $sql_month . "-%'
	";
	$res = DB::query( $sql );
	while ($r = DB::fetch( $res )) {
		$deals [$r->ID] = $r;
	}


	foreach ($deals as $k_id => $deal) {
		echo '<br />' . '<b>' . "ID=" . $k_id . '</b>';
		$cols = get_object_vars($deal);
		// ID	A_RECORD_CREATE_DATE	A_RECORD_CREATE_AUTHOR	A_RECORD_UPDATE_DATE	A_RECORD_UPDATE_AUTHOR	A_STATUS	DATA_DOK	S_ADDRESS_STREET	S_OTHER_INFO	ID_BILLING_USERS_OLD	ID_BILLING_USERS_NEW	ID_SERVICES_OLD	ID_SERVICES_NEW	P_SERVICE	P_SERVICE_COMPANY	P_SERVICE_NAME	P_OFFER_ID_OLD	P_OFFER_ID_NEW	P_DEAL_DATE_OLD	P_DEAL_DATE_NEW	P_DEALNUMBER_OLD	P_DEALNUMBER_NEW	P_DEALTERM_OLD	P_DEALTERM_NEW	MONTH_COST_OLD	MONTH_COST_NEW	INICJALY_HANDLOWCA	RODZAJ_DZIALANIA_HANDLOWEGO
		echo '<table border="1" cellspacing="0" cellpadding="0" class="tbl-small">';
		echo '<tr>';
		foreach ($cols as $k_key => $v_value) {
			echo '<th>'.$k_key.'</th>';
		}//end foreach
		echo '</tr>';
		echo '<tr>';
		foreach ($cols as $k_key => $v_value) {
			echo '<td>'.$deal->$k_key.'</td>';
		}//end foreach
		echo '</tr>';
		echo '</table>';

		if ($deal->ID_BILLING_USERS_OLD == 0 && $deal->ID_BILLING_USERS_NEW > 0) {
			echo 'Nowa umowa (' . $deal->P_DEALNUMBER_NEW . ') na oferte (' . $deal->P_OFFER_ID_NEW . ')';
		}
		else if ($deal->ID_BILLING_USERS_OLD > 0 && $deal->ID_BILLING_USERS_NEW == 0) {
			echo 'Rezygnacja z umowy (' . $deal->P_DEALNUMBER_OLD . ') na oferte ' . $deal->P_OFFER_ID_OLD;
		}
		else if ($deal->ID_BILLING_USERS_OLD > 0 && $deal->ID_BILLING_USERS_NEW > 0) {
			echo 'Aneks z umowy (' . $deal->P_DEALNUMBER_OLD . ') na umowe (' . $deal->P_DEALNUMBER_NEW . ')';
		}
		else {
			echo 'error?';
		}
		echo '<hr />';

	}//end foreach


}


function USERS2_OFFERS_COSTS_SALES_PLAN__DEALS_CHECK_END( $params = array() ) {
	SEF("MENU");
	MENU();
	if (!empty($params['toolbar'])) {
		echo '<p>' . implode(" | ", $params['toolbar']) . '</p>';
	}

	SalesPlan::print_css();

	$_month = V::get('_month', '', $_GET);
	$month_arr = explode('-', $_month);
	if (count($month_arr) == 2 && strlen($month_arr[0]) == 4 && strlen($month_arr[1]) == 2) {
		$klient = V::get('_klient', 0, $_GET, 'int');
		$deal = V::get('_deal', 0, $_GET, 'int');
		if ($deal > 0 && $klient > 0) {
			echo '<h1>' ;
				echo '<a href="' . "?MENU_INIT=USERS2_OFFERS_COSTS_SALES_PLAN&ARG1=DEALS_CHECK_END" . '">' . "Umowy - daty zakończenia" . '</a>';
				echo ' | ';
				echo '<a href="' . "?MENU_INIT=USERS2_OFFERS_COSTS_SALES_PLAN&ARG1=DEALS_CHECK_END&_month=" . $_month . '">' . " miesiac " . $_month . '</a>';
				echo ' | ';
				echo " klient " . $klient . " (umowa " . $deal . ")";
			echo '</h1>';

			$sql = "select
					d.`ID`
			--		, `M_DIST_FILES`
			--		, `TAB_UPDATE_STAT`, `TAB_UPDATE_DATE`, `TAB_UPDATE_INFO`
					, `DATA_DOK`
			--		, `S_ALIAS`, `S_ADDRESS_CITY`
					, `S_ADDRESS_STREET`
					, `S_OTHER_INFO`
			--		, `T_WORKPOINTS`, `T_WORKPOINTS_PROCES`, `T_WORKPOINTS_VALUE`, `T_WORKPOINTS_TYPE`, `T_WORKPOINTS_USER`, `T_WORKPOINTS_DATE`, `T_PENALTIES`, `T_PENALTIES_TYPE`, `T_PENALTIES_VALUE`
					, `P_SERVICE`
					, `P_SERVICE_COMPANY`
					, `P_SERVICE_NAME`
					, `ID_BILLING_USERS_OLD`, `ID_BILLING_USERS_NEW`
					, `ID_SERVICES_OLD`, `ID_SERVICES_NEW`
					, `P_OFFER_ID_OLD`, `P_OFFER_ID_NEW`
					, `P_DEAL_DATE_OLD`, `P_DEAL_DATE_NEW`
					, `P_DEALNUMBER_OLD`, `P_DEALNUMBER_NEW`
					, `P_DEALTERM_OLD`, `P_DEALTERM_NEW`
					, `MONTH_COST_OLD`, `MONTH_COST_NEW`
					, `INICJALY_HANDLOWCA`
					, `RODZAJ_DZIALANIA_HANDLOWEGO`
					, `A_RECORD_CREATE_DATE`, `A_RECORD_CREATE_AUTHOR`, `A_RECORD_UPDATE_DATE`, `A_RECORD_UPDATE_AUTHOR`
			--		, `L_APPOITMENT_DATE`, `L_APPOITMENT_USER`, `L_APPOITMENT_PERIOD`, `L_APPOITMENT_INFO`, `L_APPOITMENT_TYPE`
			--		, `A_PROBLEM`, `A_PROBLEM_DESC`, `A_PROBLEM_DATE`
					, `A_STATUS`, `A_STATUS_CURRENT`, `A_STATUS_INFO`, `A_STATUS_LASTCHANGE_DATE`
			--		, `A_UPDATE_DATE`
			--		, `A_ADM_COMPANY`
					, IF((d.`P_DEALNUMBER_OLD`='" . $deal . "' or d.`P_DEALNUMBER_NEW`='" . $deal . "')
							, 1
							, 0
						) as DOTYCZY_UMOWY
				from `USERS2_DEALS` as d
				where
					(d.`ID_BILLING_USERS_OLD`='" . $klient . "' or d.`ID_BILLING_USERS_NEW`='" . $klient . "')
				--	(d.`P_DEALNUMBER_OLD`='" . $deal . "' or d.`P_DEALNUMBER_NEW`='" . $deal . "')
				order by `ID` DESC
			";
			$res = DB::query( $sql );
			while ($r = DB::fetch( $res )) {
				$arr []= $r;
			}
			if (empty($arr)) {
				echo '<p>' . "Brak danych" . '</p>';
				return;
			}

			$col_date = array('DATA_DOK','P_DEALTERM_OLD','P_DEALTERM_NEW','P_DEAL_DATE_OLD','P_DEAL_DATE_NEW','A_RECORD_CREATE_DATE','A_RECORD_UPDATE_DATE');
			$cols = array_keys( get_object_vars( reset($arr) ) );
			if (end($cols) == 'DOTYCZY_UMOWY') array_pop($cols);// remove last col - DOTYCZY_UMOWY
			// group cols by OLD, NEW
			$col_groups = array();
			foreach ($cols as $col) {
				if (in_array(substr($col, -4), array('_OLD', '_NEW'))) {
					$col_group_name = substr($col, 0, -4);
					$col_groups[1][$col_group_name] = '';
					$col_groups[2][$col_group_name][$col] = '';
				}
				else {
					$col_groups[1][$col] = '';
				}
			}//end foreach
			echo '<table border="1" cellspacing="0" cellpadding="0" class="tbl-view">';
			echo '<thead>';
				echo '<tr>';
				foreach ($col_groups[1] as $col_group_name => $v_val) {
					$colspan = (!empty($col_groups[2][$col_group_name]))? ' colspan="' . count($col_groups[2][$col_group_name]) . '" style="border-bottom-width:1px;"' : ' rowspan="2"';
					echo '<th' . $colspan . '>' . $col_group_name . '</th>';
				}
				echo '</tr>';
				echo '<tr>';
				foreach ($col_groups[2] as $col_group_name => $v_cols) {
					foreach ($v_cols as $k_col => $v_val) {
						$v_col_name = $k_col;
						$cls = '';
						if (substr($k_col, -4) == '_OLD') {
							$cls = ' class="cell-old"';
							$v_col_name = 'OLD';
						}
						else if (substr($k_col, -4) == '_NEW') {
							$cls = ' class="cell-new"';
							$v_col_name = 'NEW';
						}
						//echo '<th' . $cls . '>' . str_replace('_', ' ', $col) . '</th>';
						echo '<th' . $cls . '>' . $v_col_name . '</th>';
					}
				}
				echo '</tr>';
			echo '</thead>';
			echo '<tbody>';
			$t = 1;
			foreach ($arr as $r) {
				$cls = array();
				$cls []= 'row-' . ($t = 1 - $t);
				if ($r->DOTYCZY_UMOWY) $cls []= 'dotyczy_umowy';
				$cls = (!empty($cls))? ' class="' . implode(' ', $cls) .  '"' : '';
				echo '<tr' . $cls . '>';
				foreach ($cols as $col) {
					$cls = array();
					if (substr($col, -4) == '_OLD') {
						$cls []= 'cell-old';
					}
					else if (substr($col, -4) == '_NEW') {
						$cls []= 'cell-new';
					}
					if (in_array($col, $col_date)) {
						$cls []= 'cell-date';
					}
					if ($col == 'A_STATUS') {
						$cls []= 'cell-' . $r->$col;
					}
					if (substr($col, 0, -4) == 'P_DEALNUMBER' && $r->$col == $deal) {
						$cls []= 'cell-deal';
					}
					$cls = (!empty($cls))? ' class="' . implode(' ' , $cls) . '"' : '';
					echo '<td' . $cls . '>' . $r->$col . '</td>';
				}//end foreach
				echo '</tr>';
			}//end foreach
			echo '</tbody>';
			echo '</table>';
			return;
		}

		echo '<h1>' ;
			echo '<a href="' . "?MENU_INIT=USERS2_OFFERS_COSTS_SALES_PLAN&ARG1=DEALS_CHECK_END" . '">' . "Umowy - daty zakończenia" . '</a>';
			echo ' | ';
			echo " month " . $_month;
		echo '</h1>';

		$adresy = array();
		$adresy_info = array();

		$tbl_filters = array();
		$tbl_filters['L_APOINTMENT_DATE']['values'] = array('%'=>'wszystkie', 'stare'=>'stare');
		$tbl_filters['L_APOINTMENT_DATE']['value'] = V::get('filtr_L_APOINTMENT_DATE', '', $_GET);
		if (!array_key_exists($tbl_filters['L_APOINTMENT_DATE']['value'], $tbl_filters['L_APOINTMENT_DATE']['values'])) {
			$tbl_filters['L_APOINTMENT_DATE']['value'] = null;
		}

		$_limitstart = V::get('_limitstart', 0, $_GET, 'int');

		$conf_page_nav_show = true;

		$deals = ColumnDealsStatus::get_deals_stats_by_month($_month, $_limitstart);

		foreach ($deals as $v_deal) {
			if (!empty($v_deal->uslugi)) {
				foreach ($v_deal->uslugi as $v_srv) {
					// add adresy to fetch from MARKETING
					if ($v_srv->S_ADDRESS_STREET) $adresy[$v_srv->S_ADDRESS_STREET] = true;
				}
			}
		}

		if (!empty($adresy)) {
			$sql_adresy = array();
			foreach ($adresy as $k_street => $v_val) {
				$sql_adresy []= "'" . $k_street . "'";
			}
			$sql = "select m.`ID`, m.`S_ADDRESS_STREET`
					, m.`L_APPOITMENT_DATE`, m.`L_APPOITMENT_USER`, m.`L_APPOITMENT_INFO`, m.`L_APPOITMENT_TYPE`
					, m.`P_NAME`, m.`P_PHONE`, m.`M_REWIR`
				from `USERS2_MARKETING` as m
				where
					m.`S_ADDRESS_STREET` in (" . implode(", ", $sql_adresy) . ")
			";
			$res = DB::query( $sql );
			while ($r = DB::fetch( $res )) {
				$adresy_info [$r->S_ADDRESS_STREET] = $r;
			}
		}

		// simple pagenav
		echo '<div' . ((!$conf_page_nav_show) ? ' style="display:none;"' : '') . '>';
			$link_base = array();
			$link_base []= "MENU_INIT=USERS2_OFFERS_COSTS_SALES_PLAN";
			$link_base []= "ARG1=DEALS_CHECK_END";
			$link_base []= "_month=" . $_month;
			if ($tbl_filters['L_APOINTMENT_DATE']['value']) $link_base []= "filtr_L_APOINTMENT_DATE=" . $tbl_filters['L_APOINTMENT_DATE']['value'];
			$link_href = "?" . implode("&", $link_base) . "&_limitstart=0";
			echo '<a href="' . $link_href . '">' . "|&lt;&lt; " . '</a>' . "&nbsp;";
			if ($_limitstart >= 100) {
				$link_href = "?" . implode("&", $link_base) . "&_limitstart=" . ($_limitstart - 100);
				echo '<a href="' . $link_href . '">' . " &lt;&lt; " . '</a>' . "&nbsp;";
			} else {
				echo '<span>' . " &lt;&lt; " . '</span>' . "&nbsp;";
			}
			echo $_limitstart . " (100)" . "&nbsp;";
			$link_href = "?" . implode("&", $link_base) . "&_limitstart=" . ($_limitstart + 100);
			echo '<a href="' . $link_href . '">' . " &gt;&gt; " . '</a>' . "&nbsp;";
		echo '</div>';
		echo '<table border="1" cellspacing="0" cellpadding="0" class="tbl-view">';
		echo '<thead>';
			echo '<tr>';
				echo '<th rowspan="2">' . "ID" . '</th>';
				echo '<th rowspan="2" class="dealnumber">' . "Nr umowy" . '</th>';
				echo '<th class="id_klienta">' . "Klient" . '</th>';
				echo '<th rowspan="2">' . "Data umowy" . '</th>';
				echo '<th rowspan="2">' . "Data końca umowy" . '</th>';
				echo '<th rowspan="2">' . "Usługi info <br />(brutto)" . '</th>';
				echo '<th rowspan="2">' . "Suma <br />(netto)" . '</th>';
				echo '<th rowspan="2">' . "Adres" . '</th>';
				echo '<th class="l_app" colspan="5">' . "L_APOINTMENT" . '</th>';
			echo '</tr>';
			echo '<tr>';
				echo '<th class="id_klienta">' . "ID" . '</th>';
				echo '<th class="l_app">' . "ID" . '</th>';
				echo '<th class="l_app">' . "DATE" . '</th>';
				echo '<th class="l_app">' . "USER" . '</th>';
				echo '<th class="l_app">' . "TYPE" . '</th>';
				echo '<th class="l_app">' . "INFO" . '</th>';
			echo '</tr>';
			echo '<tr class="find">';
				echo '<form action="" method="get">';
					foreach ($_GET as $k => $v) {
						if (!array_key_exists('filtr_' . $k, $tbl_filters)) {
							echo '<input type="hidden" name="'.$k.'" value="'.$v.'" />';
						}
					}
				echo '<td>';
					echo '<input type="image" value="Szukaj" src="icon/search.png" title="Szukaj">';
				echo '</td>';
				echo '<td>' . "&nbsp;" . '</td>';
				echo '<td>' . "&nbsp;" . '</td>';
				echo '<td>' . "&nbsp;" . '</td>';
				echo '<td>' . "&nbsp;" . '</td>';
				echo '<td>' . "&nbsp;" . '</td>';
				echo '<td>' . "&nbsp;" . '</td>';
				echo '<td>' . "&nbsp;" . '</td>';
				echo '<td>' . "&nbsp;" . '</td>';
				echo '<td>' . "&nbsp;" . '</td>';
				echo '<td>';
				if (!empty($tbl_filters['L_APOINTMENT_DATE']['values'])) {
					echo '<select name="'."filtr_L_APOINTMENT_DATE".'" onchange="this.form.submit();">';
					foreach ($tbl_filters['L_APOINTMENT_DATE']['values'] as $k_filtr => $v_label) {
						$sel = ($tbl_filters['L_APOINTMENT_DATE']['value'] == $k_filtr)? ' selected="selected"' : '';
						echo '<option value="' . $k_filtr . '"' . $sel . '>' . $v_label . '</option>';
					}
					echo '</select>';
				}
				echo '</td>';
				echo '<td>' . "&nbsp;" . '</td>';
				echo '<td>' . "&nbsp;" . '</td>';
				echo '<td>' . "&nbsp;" . '</td>';
				echo '</form>';
			echo '</tr>';
		echo '</thead>';
		echo '<tbody>';
		if (empty($deals)) {
			echo '<td colspan="14" style="padding:16px 30px;">';
			$link_href = "?" . implode("&", $link_base) . "&_limitstart=0";
			echo "Brak danych. Wróć do " . '<a href="'.$link_href.'">' . "pierwszej strony" . '</a>';
			echo '</td>';
		}
		else {
			foreach ($deals as $r) {
				$v_usluga_suma = 0;
				$v_usluga_adresy = array();
				if (!empty($r->uslugi)) {
					foreach ($r->uslugi as $v_usluga) {
						if ($v_usluga->S_ADDRESS_STREET) {
							$v_usluga_adresy [$v_usluga->S_ADDRESS_STREET] = true;
						}
						$v_usluga_suma += $v_usluga->ABONAMENT_VALUE * (100 + $v_usluga->VAT) / 100.0;
					}
				}

				// filters
				$hide_row = false;
				if ($tbl_filters['L_APOINTMENT_DATE']['value'] == 'stare') {
				    $hide_row = true ; //zawsze chowamy wiersz, liczac , ze jakies spotkanie jest nieprzedawnione
					foreach($v_usluga_adresy as $IN=>$VA) {
							if (substr($adresy_info[ $IN ]->L_APPOITMENT_DATE,0,10) <= date('Y-m-d')) {
							$hide_row = false;
							}

					}
				
				
			//	if($adresy_info[ $v_street ]->L_APPOITMENT_DATE=='2012-10-18') {
			//	$hide_row=true;
			//	echo "TAAAK";
			//	}
//				if($r->ID=='206')	$hide_row = true;


		/*
					if (empty($v_usluga_adresy)) {
						$hide_row = true;
					} else {
						$v_street = reset(array_keys($v_usluga_adresy));
						if (!$v_stree || !array_key_exists($v_street, $adresy_info)) {
							$hide_row = true;
						} else if ($adresy_info[ $v_street ]->L_APPOITMENT_DATE >= date('Y-m-d')) {
							$hide_row = true;
						}
					}
		*/
				}

				$cls = '';
				if ($hide_row) {
					//continue;
					$cls = ' style="display:none"';
				}
				echo '<tr' . $cls . '>';
				echo '<td style="color:silver;">';
					echo $r->ID;
				echo '</td>';
				echo '<td class="dealnumber">';
					echo $r->P_DEALNUMBER;
				echo '</td>';
				echo '<td class="id_klienta">';
					echo $r->ID_BILLING_USERS;
					$link_deal = "?MENU_INIT=USERS2_OFFERS_COSTS_SALES_PLAN&ARG1=DEALS_CHECK_END&_month=2012-07&_deal=" . $r->P_DEALNUMBER . "&_klient=" . $r->ID_BILLING_USERS;
					$ico_deal = '<img src="icon/history.gif" alt="' . $r->ID_BILLING_USERS . '" border="0" width="16">';
					echo " " . '<a href="' . $link_deal . '" title="' . "Historia klienta" . '">' . $ico_deal . '</a>';
				echo '</td>';
				echo '<td>';
					echo '<nobr>' . $r->P_DEALDATE . '</nobr>';
				echo '</td>';
				echo '<td>';
					echo '<nobr>' . $r->P_DEALDATE_TERM . '</nobr>';
				echo '</td>';
				echo '<td class="uslugi-info">';
					if (empty($r->uslugi)) {
						echo '<b style="color:red">' . "brak usług!" . '</b>';
					} else {
						foreach ($r->uslugi as $v_usluga) {
							echo '<nobr>' . "(" . $v_usluga->ID . ") ";
								echo '<span class="usluga-'.$v_usluga->A_STATUS.'">' . $v_usluga->A_STATUS . '</span>';
								echo " " . $v_usluga->NAME_LIST_SERVICES . " " . $v_usluga->ABONAMENT_VALUE . " zł (Oferta ";
									echo ($v_usluga->ID_OFFERS > 0)? $v_usluga->ID_OFFERS : '<b style="color:red">' . "N/S;" . '</b>';
								echo ")";
								echo " " . $v_usluga->S_ADDRESS_STREET;
							echo '</nobr>';
							echo '<br />';
						}
					}
				echo '</td>';
				echo '<td>';
					if (!empty($r->uslugi)) {
						echo number_format($v_usluga_suma, 2, '.', ',');
					}
				echo '</td>';
				$cell_cls = '';
				$cell_title = '';
				if (count($v_usluga_adresy) == 0) {
					$cell_cls = ' class="err"';
					$cell_title = ' title="' . "Brak danych" . '"';
				} else if (count($v_usluga_adresy) > 1) {
					$cell_cls = ' class="err"';
					$cell_title = ' title="' . "Za dużo adresów!" . '"';
				}
				echo '<td'.$cell_cls.''.$cell_title.'>';
					if (!empty($r->uslugi)) {
						echo implode(", ", array_keys($v_usluga_adresy));
					}
				echo '</td>';
				if (count($v_usluga_adresy) > 0) {
					$v_street = reset(array_keys($v_usluga_adresy));
					echo '<td class="l_app l_app-id">';
						$link_edit = "?MENU_INIT=USERS2_MARKETING_EDIT&ARG1=EDIT&ARG1_VAL=" . $adresy_info[ $v_street ]->ID;
						$link_edit = "index.php?MENU_INIT=VIEWTABLE_AJAX&ZASOB_ID=23#EDIT/{$adresy_info[$v_street]->ID}";
						$ico_edit = '<img src="icon/edit.gif" alt="' . $adresy_info[ $v_street ]->ID . '" border="0" width="20">';
						echo '<a href="' . $link_edit . '" title="' . "Edycja rekordu MARKETING" . '">' . $ico_edit . '</a>';
					echo '</td>';
					$add_cls = '';
					if ($adresy_info[ $v_street ]->L_APPOITMENT_DATE == '') {
						$add_cls = ' l_app-date_empty';
					} else {
						if ($adresy_info[ $v_street ]->L_APPOITMENT_DATE < date('Y-m-d')) {//$_month . '-00'
							$add_cls = ' l_app-date_past';
						} else {
							$add_cls = ' l_app-date_future';
						}
					}
					echo '<td class="l_app' . $add_cls . '">';
						echo '<nobr>' . $adresy_info[ $v_street ]->L_APPOITMENT_DATE . '</nobr>';
					echo '</td>';
					echo '<td class="l_app">';
						echo $adresy_info[ $v_street ]->L_APPOITMENT_USER;
					echo '</td>';
					echo '<td class="l_app l_app_type-' . $adresy_info[ $v_street ]->L_APPOITMENT_TYPE . '">';
						echo $adresy_info[ $v_street ]->L_APPOITMENT_TYPE;
					echo '</td>';
					echo '<td class="l_app l_app-info">';
						echo $adresy_info[ $v_street ]->L_APPOITMENT_INFO;
					echo '</td>';
				}
				
	/*
				echo '<td>';
					if ($r->DEALS_ID > 0) {
						echo $r->DEALS_ID;
						echo ' / ';
						echo $r->S_ADDRESS_STREET;
					} else {
						echo '<b style="color:red">' . "brak wpisu w DEALS!" . '</b>';
					}
				echo '</td>';
	*/
				echo '</tr>';
				//extra linia dla zapisania notatek by sqix @2012-10-04 TODO
				echo '<tr '.$cls.'>';
					echo '<td colspan=3>Notaki:<br>'.$adresy_info[ $v_street ]->M_REWIR.'<br></td>';
					echo '<td colspan=3>L2: '.$r->BUA_P_NAME.' <i>tel : '.$r->BUA_P_PHONE.'</i><hr>';
						echo 'MK:'.$adresy_info[ $v_street ]->P_NAME.' <i>tel : '.$adresy_info[ $v_street ]->P_PHONE.'</i></td>';
					echo '<td></td><td></td><td></td><td></td><td></td><td></td><td></td>';
				echo '</tr>';
			}
		}
		echo '</tbody>';
		echo '</table>';

		return;
	}

	$force_update = false;
	if (V::get('_update_stats', 0, $_GET, 'int') == 1) {
		$force_update = true;
		if (V::get('DBG', 0, $_GET, 'int') > 1) {
			echo '<p>'."Update deals stats...".'</p>';
		}
	}
	$deals_stats_by_months = ColumnDealsStatus::get_deals_stats_by_months($force_update);

	echo '<h1>' . "Umowy - daty zakończenia" . '</h1>';

	echo '<table border="1" cellspacing="0" cellpadding="0" class="tbl-view">';
	echo '<thead>';
		echo '<tr>';
			echo '<th>' . "Koniec umowy (miesiąc)" . '</th>';
			echo '<th>' . "Ilość" . '</th>';
			echo '<th>' . "Suma" . '</th>';
			echo '<th>' . "&nbsp;" . '</th>';
		echo '</tr>';
	echo '</thead>';
	echo '<tbody>';
	foreach ($deals_stats_by_months as $r) {
		echo '<tr>';
			echo '<td>';
				echo ($r['deal_term_month'] == '0000-00')? "zakończone" : $r['deal_term_month'];
			echo '</td>';
			echo '<td>';
				echo $r['cnt'];
			echo '</td>';
			echo '<td style="text-align:right">';
				if ($r['suma'] > 0) {
					echo number_format($r['suma'], 2, ',', ' ') . " zł";
				}
			echo '</td>';
			echo '<td>';
				echo '<a href="' . "?MENU_INIT=USERS2_OFFERS_COSTS_SALES_PLAN&ARG1=DEALS_CHECK_END&_month=" . $r['deal_term_month'] . '">' . "check" . '</a>';
			echo '</td>';
		echo '</tr>';
	}
	echo '</tbody>';
	echo '</table>';
}


function USERS2_OFFERS_COSTS_SALES_PLAN__type( $type = 'DEALS_CHECK_REZ', $params = array() ) {
	SEF("MENU");
	MENU();
	if (!empty($params['toolbar'])) {
		echo '<p>' . implode(" | ", $params['toolbar']) . '</p>';
	}

	SalesPlan::print_css();
	echo '<style type="text/css">' . "
.tbl-view {}
 .tbl-view th {padding:0 2px;}
 .tbl-view td {padding:0 2px;}
.tbl-small {font-size:small;}
 .tbl-small th {padding:0 2px; font-size:small;}
 .tbl-small td {padding:0 2px; font-size:small;}
	" . '</style>';

	$page = V::get('_page', 0, $_GET, 'int');
	$sql_limit = 100;
	if ($page > 0) {
		$sql_limit .= ' offset ' . ($page * 100);
	}

	$arr = array();
	$streets_arr = array();
	if ($type == 'DEALS_CHECK_WYP') {
		$sql = "select d.`ID`
				, d.`P_DEALNUMBER_OLD`
				, d.`ID_BILLING_USERS`
				, d.`S_ADDRESS_STREET`
				, d.`P_DEALDATE`
				, d.`DEALDESC`
				, d.`A_STATUS`
			from `DEALS_TABLE` as d
			where
				d.`RODZAJ_DZIALANIA_HANDLOWEGO`='REZYGNACJA WYPROWADZKA'
			order by d.`P_DEALDATE` desc
			limit $sql_limit
		";
	} else {// default: DEALS_CHECK_REZ
		$sql = "select d.`ID`
				, d.`P_DEALNUMBER_OLD`
				, d.`ID_BILLING_USERS`
				, d.`S_ADDRESS_STREET`
				, d.`P_DEALDATE`
				, d.`DEALDESC`
				, d.`A_STATUS`
			from `DEALS_TABLE` as d
			where
				d.`P_DEALNUMBER_OLD`>0
				and (d.`P_DEALNUMBER`=0 or d.`P_DEALNUMBER`='' or d.`P_DEALNUMBER` is null)
				and d.`A_STATUS` in('NORMAL', 'WAITING')
			order by d.`P_DEALDATE` desc
			limit $sql_limit
		";
	}
	$res = DB::query( $sql );
	while ($r = DB::fetch( $res )) {
		$arr []= $r;

		if (isset($r->S_ADDRESS_STREET)) {
			$streets_arr[$r->S_ADDRESS_STREET] = true;
		}
	}

	$adresy_info = array();
	$cols_adresy = array();
	$cols_adresy['ID'] = 'ID';
	$cols_adresy['L_APPOITMENT_DATE'] = 'DATE';
	$cols_adresy['L_APPOITMENT_USER'] = 'USER';
	$cols_adresy['L_APPOITMENT_INFO'] = 'TYPE';
	$cols_adresy['L_APPOITMENT_TYPE'] = 'INFO';
	if (!empty($streets_arr)) {
		$sql_adresy = array();
		foreach ($streets_arr as $k_street => $v_val) {
			$sql_adresy []= "'{$k_street}'";
		}
		$db = DB::getDB();
		$sql = "select m.`ID`
				, m.`S_ADDRESS_STREET`
				, m.`L_APPOITMENT_DATE`
				, m.`L_APPOITMENT_USER`
				, m.`L_APPOITMENT_INFO`
				, m.`L_APPOITMENT_TYPE`
				, m.`P_NAME`, m.`P_PHONE`, m.`M_REWIR`
			from `USERS2_MARKETING` as m
			where
				m.`S_ADDRESS_STREET` in (" . implode(", ", $sql_adresy) . ")
		";
		$res = $db->query($sql);
		while ($r = $db->fetch($res)) {
			$adresy_info [$r->S_ADDRESS_STREET] = $r;
		}
	}

	echo '<h1>';
		if ($type == 'DEALS_CHECK_WYP') {
			echo "Umowy - rezygnacje wyprowadzka";
		} else {
			echo "Umowy - rezygnacje";
		}
	echo '</h1>';

	$cols = array_keys(get_object_vars(reset($arr)));
	echo '<p>' . "Strona: ";
		for ($i = 0; $i <= $page; $i++) {
			echo ' <a href="' . "?MENU_INIT=USERS2_OFFERS_COSTS_SALES_PLAN&ARG1={$type}&_page={$i}" . '" title="' . "Umowy - rezygnacje wyprowadzka" . '">' . ($i + 1) . '</a>';
		}
		echo ' <a href="' . "?MENU_INIT=USERS2_OFFERS_COSTS_SALES_PLAN&ARG1={$type}&_page={$i}" . '" title="' . "Umowy - rezygnacje wyprowadzka" . '">' . " next " . '</a>';
	echo '</p>';
	if (empty($arr)) {
		echo '<p>'."Brak danych - wróć do " . '<a href="' . "?MENU_INIT=USERS2_OFFERS_COSTS_SALES_PLAN&ARG1={$type}" . '" title="' . "Umowy - rezygnacje wyprowadzka" . '">' . "pierwszej strony" . '</a>' .'</p>';
	} else {
		echo '<table border="1" cellspacing="0" cellpadding="0" class="tbl-small tbl-view">';
		echo '<thead>';
			echo '<tr>';
				foreach ($cols as $k_field) {
					$cls = '';
					if ($k_field == 'P_DEALNUMBER_OLD') {
						$cls = 'dealnumber';
					} else if ($k_field == 'ID_BILLING_USERS') {
						$cls = 'id_klienta';
					}
					echo '<th rowspan="2" class="'.$cls.'">' . $k_field . '</th>';
				}
				echo '<th colspan="' . count($cols_adresy) . '" class="l_app">' . "L_APOINTMENT" . '</th>';
			echo '</tr>';
			echo '<tr>';
			foreach ($cols_adresy as $col) {
				echo '<th class="l_app">' . $col . '</th>';
			}
			echo '</tr>';
		echo '</thead>';
		echo '<tbody>';
		foreach ($arr as $r) {
			echo '<tr>';
				foreach ($cols as $k_field) {
					$cls = '';
					if ($k_field == 'P_DEALNUMBER_OLD') {
						$cls = 'dealnumber';
					} else if ($k_field == 'ID_BILLING_USERS') {
						$cls = 'id_klienta';
					}
					echo '<td class="'.$cls.'">' . $r->$k_field . '</td>';
				}
				if (isset($r->S_ADDRESS_STREET) && array_key_exists($r->S_ADDRESS_STREET, $adresy_info)) {
					$adres = $adresy_info[$r->S_ADDRESS_STREET];
					foreach ($cols_adresy as $k_col => $v_label) {
						if ($k_col == 'ID') {
							echo '<td class="l_app l_app-'.strtolower($v_label).'">';
							$link_edit = "?MENU_INIT=USERS2_MARKETING_EDIT&ARG1=EDIT&ARG1_VAL={$adres->$k_col}";
							$link_edit = "index.php?MENU_INIT=VIEWTABLE_AJAX&ZASOB_ID=23#EDIT/{$adresy_info[$v_street]->ID}";
							echo '<a href="' . $link_edit . '" title="Edycja rekordu MARKETING">';
								echo '<img src="icon/edit.gif" alt="1385" border="0" width="20">';
							echo '</a>';
							echo '</td>';
						} else if ($k_col == 'L_APPOITMENT_DATE') {
							$add_cls = '';
							if ($adres->$k_col == '') {
								$add_cls = ' l_app-date_empty';
							} else {
								if ($adres->$k_col < date('Y-m-d')) {//$_month . '-00'
									$add_cls = ' l_app-date_past';
								} else {
									$add_cls = ' l_app-date_future';
								}
							}
							echo '<td class="l_app' . $add_cls . '">';
								echo '<nobr>' . $adres->$k_col . '</nobr>';
							echo '</td>';
						} else {
							echo '<td class="l_app l_app-'.strtolower($v_label).'">' . $adres->$k_col . '</td>';
						}
					}
				} else {
					echo '<td colspan="' . count($cols_adresy) . '">'."".'</td>';
				}
			echo '</tr>';
		}
		echo '</tbody>';
		echo '</table>';
	}
}


class SalesPlan {

	function print_css() {
		echo '<style type="text/css">' . "
body{font-family:arial;font-size:small;}
.tbl-view {}
 .tbl-view thead th {background:#f3f3f3; font-size:11px; border-bottom:3px solid #999;}
 .tbl-view th {padding:0 2px;}
 .tbl-view td {padding:0 2px;}
.tbl-small {font-size:small;}
 .tbl-small th {padding:0 2px; font-size:small;}
 .tbl-small td {padding:0 2px; font-size:small;}

.tbl-view .err {background-color:#FD7A7A;}
.tbl-view .dealnumber {background-color:#A3CDF5;}
.tbl-view .id_klienta {background-color:#F9FBBB; white-space:nowrap;}
.tbl-view .klient-tel {background-color:#F9FBBB;}
.tbl-view .uslugi-info {font-size:12px;}
.tbl-view .l_app {background-color:#eee;}
.tbl-view .l_app-id {text-align:center;}
.tbl-view .l_app-info {}
 .tbl-view .cell-old {border-right:1px solid blue; text-align:right;}
 .tbl-view .cell-new {border-left:1px solid blue; text-align:left;}
 .tbl-view .cell-date {white-space:nowrap;}
 .tbl-view .dotyczy_umowy td {background-color:#FDE6BB;}
 .tbl-view .dotyczy_umowy .cell-deal {background-color:#D58B04; color:#fff; font-weight:bold;}

.usluga-NORMAL {background-color:#8F8;}
.usluga-WAITING {background-color:#F8F;}
.usluga-OFF_HARD {background-color:#EEE;}
.usluga-OFF_SOFT {background-color:#EEE;}

.tbl-view tbody td.l_app-date_empty {background-color:#FD7A7A;}
.tbl-view tbody td.l_app-date_past {background-color:cyan;}
.tbl-view tbody td.l_app-date_future {background-color:#BFC290;}

.tbl-view tbody td.l_app_type-ARRANGED {background-color:#FFFF88;}
.tbl-view tbody td.l_app_type-TO_ARRANGE {background-color:#FF88FF;}
.tbl-view tbody td.l_app_type-CANCELLED {background-color:#FF5555;}
.tbl-view tbody td.l_app_type-TO_CANCEL {background-color:#FF55FF;}
.tbl-view tbody td.l_app_type-RELATE {background-color:#FFBBBB;}
.tbl-view tbody td.l_app_type-WAIT {background-color:#88FF88;}

		" . '</style>';
	}

}