<?php
	set_time_limit(0);
	date_default_timezone_set("Europe/Moscow");
	require_once 'php/Classes/PHPExcel.php';
	require_once 'php/Classes/PHPExcel/IOFactory.php';
	function create_guid($namespace = ''){
		static $guid = '';
		$uid = uniqid("", true);
		$data = $namespace;
		$data .= $_SERVER['REQUEST_TIME'];
		$data .= $_SERVER['HTTP_USER_AGENT'];
		$data .= $_SERVER['LOCAL_ADDR'];
		$data .= $_SERVER['LOCAL_PORT'];
		$data .= $_SERVER['REMOTE_ADDR'];
		$data .= $_SERVER['REMOTE_PORT'];
		$hash = strtoupper(hash('ripemd128', $uid . $guid . md5($data)));
		$guid = '{' .  
				substr($hash,  0,  8) .
				'-' .
				substr($hash,  8,  4) .
				'-' .
				substr($hash, 12,  4) .
				'-' .
				substr($hash, 16,  4) .
				'-' .
				substr($hash, 20, 12) .
				'}';
		return $guid;
	}
	function num2str($num){
		$nul='ноль';
		$ten=array(
			array('','один','два','три','четыре','пять','шесть','семь', 'восемь','девять'),
			array('','одна','две','три','четыре','пять','шесть','семь', 'восемь','девять'),
		);
		$a20=array('десять','одиннадцать','двенадцать','тринадцать','четырнадцать' ,'пятнадцать','шестнадцать','семнадцать','восемнадцать','девятнадцать');
		$tens=array(2=>'двадцать','тридцать','сорок','пятьдесят','шестьдесят','семьдесят' ,'восемьдесят','девяносто');
		$hundred=array('','сто','двести','триста','четыреста','пятьсот','шестьсот', 'семьсот','восемьсот','девятьсот');
		$unit=array(
			array('копейка' ,'копейки' ,'копеек'	,1),
			array('рубль'   ,'рубля'   ,'рублей'    ,0),
			array('тысяча'  ,'тысячи'  ,'тысяч'     ,1),
			array('миллион' ,'миллиона','миллионов' ,0),
			array('миллиард','милиарда','миллиардов',0),
		);

		list($rub,$kop) = explode('.',sprintf("%015.2f", floatval($num)));
		$out = array();
		if(intval($rub)>0) {
			foreach(str_split($rub,3) as $uk=>$v){
				if(!intval($v))
					continue;
				$uk = sizeof($unit)-$uk-1;
				$gender = $unit[$uk][3];
				list($i1,$i2,$i3) = array_map('intval',str_split($v,1));
				
				$out[] = $hundred[$i1]; # 1xx-9xx
				if($i2>1)
					$out[]= $tens[$i2].' '.$ten[$gender][$i3]; # 20-99
				else
					$out[]= $i2>0 ? $a20[$i3] : $ten[$gender][$i3]; # 10-19 | 1-9

				if($uk>1)
					$out[]= morph($v,$unit[$uk][0],$unit[$uk][1],$unit[$uk][2]);
			}
		}
		else
			$out[] = $nul;
		$out[] = morph(intval($rub), $unit[1][0],$unit[1][1],$unit[1][2]);
		$out[] = $kop.' '.morph($kop,$unit[0][0],$unit[0][1],$unit[0][2]);
		return trim(preg_replace('/ {2,}/', ' ', join(' ',$out)));
	}

	function morph($n, $f1, $f2, $f5) {
		$n = abs(intval($n)) % 100;
		if ($n>10 && $n<20) return $f5;
		$n = $n % 10;
		if ($n>1 && $n<5) return $f2;
		if ($n==1) return $f1;
		return $f5;
	}
	$buf = create_guid();
	$GUID = substr($buf, 1, 32);
	$f = fopen("app_num.csv", "r");
	$data = fgetcsv($f, 1000, ';');
	$Number = $data[0];
	$Number++;
	$data = fgetcsv($f, 1000, ';');
	$Act = $data[0];
	$Act++;
	$data = fgetcsv($f, 1000, ';');
	$Bill = $data[0];
	$Bill++;
	$data = fgetcsv($f, 1000, ';');
	$Deal = $data[0];
	$Deal++;
	$data = fgetcsv($f, 1000, ';');
	$Application = $data[0];
	$Application++;
	fclose($f);
	session_start();
	$con = mysql_connect("localhost", "root", "root");
	mysql_select_db("tpo", $con);
	$query = 'INSERT INTO application SET number = '.$Number;
	if($_POST['Client'] == "Organization"){
		if($_POST['org_id'] == "0"){
			$org = 'INSERT INTO customer_org SET org_name = "'.addslashes($_POST['org_name']).'", org_full_name = "'.addslashes($_POST['org_full_name']).'", org_INN = "'.$_POST['org_INN'].'", org_CPP = "'.$_POST['org_CPP'].'", org_address = "'.addslashes($_POST['org_address']).'", org_fio = "'.$_POST['org_fio'].'", org_app = "'.addslashes($_POST['org_app']).'", org_doc = "'.addslashes($_POST['org_doc']).'";';
			mysql_query($org);
			$org = mysql_query('SELECT * FROM customer_org WHERE org_full_name = "'.$_POST['org_full_name'].'";');
			$obj = mysql_fetch_object($org);
			$org_id = $obj->id;
		}else{
			$org_id = $_POST['org_id'];
		}
		$query .= ', customer_org_id = '.$org_id.', person_fio = "'.$_POST['org_fio_cont'].'"';
	}
	if($_POST['Client'] == "Person"){
		$query .= ', person_fio = "'.$_POST['Clientfio'].'"';
		$query .= ', customer_org_id = 0';
	}
	if($_REQUEST['TP'] == "on"){
		$query .= ', tp = 1';
	}else{
		$query .= ', tp = 0, pass_series = "'.$_POST['pass_series'].'", pass_number = "'.$_POST['pass_number'].'", pass_given = "'.addslashes($_POST['pass_given']).'", pass_date = "'.$_POST['pass_date'].'", pass_code = "'.$_POST['pass_code'].'", pass_reg = "'.$_POST['pass_reg'].'"';
	}
	if($_REQUEST['KV'] == "on")
		$query .= ', parcel_num = "'.$_POST['parcel_num'].'"';
	if($_REQUEST['KP'] == "on")
		$query .= ', object_num = "'.$_POST['obj_num'].'"';
	
	if($_POST['OKATO'])
		$query .= ', okato = "'.$_POST['OKATO'].'"';
	if($_POST['KLADR'])
		$query .= ', kladr = "'.$_POST['KLADR'].'"';
	if($_POST['Postal_Code'])
		$query .= ', postal_code = "'.$_POST['Postal_Code'].'"';
	if($_POST['region'])
		$query .= ', region = "'.$_POST['region'].'"';
	if($_POST['District'])
		$query .= ', district = "'.addslashes($_POST['District']).'"';
	if($_POST['City'])
		$query .= ', city = "'.addslashes($_POST['City']).'"';
	if($_POST['City_District'])
		$query .= ', city_district = "'.addslashes($_POST['City_District']).'"';
	if($_POST['Soviet_Village'])
		$query .= ', soviet_village = "'.$_POST['Soviet_Village'].'"';
	if($_POST['Locality'])
		$query .= ', locality_type = "'.$_POST['Locality_type'].'", locality = "'.addslashes($_POST['Locality']).'"';
	if($_POST['Street_Name'])
		$query .= ', street_type = "'.$_POST['Street_Type'].'", street = "'.addslashes($_POST['Street_Name']).'"';
	if($_POST['Level1_Name'])
		$query .= ', l1_type = "'.$_POST['Level1_Type'].'", l1 = "'.addslashes($_POST['Level1_Name']).'"';
	if($_POST['Level2_Name'])
		$query .= ', l2_type = "'.$_POST['Level2_Type'].'", l2 = "'.addslashes($_POST['Level2_Name']).'"';
	if($_POST['Level3_Name'])
		$query .= ', l3_type = "'.$_POST['Level3_Type'].'", l3 = "'.addslashes($_POST['Level3_Name']).'"';
	if($_POST['Level4_Name'])
		$query .= ', apartment_type = "'.$_POST['Level4_Type'].'", apartment = "'.addslashes($_POST['Level4_Name']).'"';
	if($_POST['note'])
		$query .= ', note = "'.$_POST['note'].'"';
	
	if($_POST['prepayment'])
		$query .= ', prepayment = "'.$_POST['prepayment'].'"';
	if($_POST['telephone'])
		$query .= ', telephone = "'.addslashes($_POST['telephone']).'"';
	if($_POST['addinfo'])
		$query .= ', addinfo = "'.addslashes($_POST['addinfo']).'"';
	if($_POST['app_note'])
		$query .= ', app_note = "'.addslashes($_POST['app_note']).'"';
	
	$timestamp = time();
	$date_time_array = getdate($timestamp);

	$month = $date_time_array['mon'];
	$day = $date_time_array['mday'];
	$year = $date_time_array['year'];
	
	$timestamp = mktime(0,0,0,$month,$day,$year);
	$date = strftime('%d.%m.%Y',$timestamp);
	$query .= ', closed = 0';
	$query .= ', dispatch = "'.$_POST['dispatch'].'"';
	$query .= ', date = "'.$date.'";';
	mysql_query($query);
	
	
	for($i = 1; $i <= $_POST['numservice']; $i++){
		$query = 'INSERT INTO service SET application_num = '.$Number;
		$query .= ', type = '.$_POST['type'.$i];
		$query .= ', cost = '.$_POST['cost'.$i];
		if($_POST['koef'.$i] == ""){
			$query .= ', koef = 1';
			$_POST['koef'.$i] = 1;
		}else{
			$query .= ', koef = '.$_POST['koef'.$i];
		}
		$query .= ', number = "'.$_POST['number'.$i].'"';
		$query .= ', days = "'.$_POST['days'.$i].'"';
		$query .= ', name = "'.addslashes($_POST['name'.$i]).'"';
		$query .= ', ord_num = '.$i;
		$timestamp = time();
		$date_time_array = getdate($timestamp);

		$month = $date_time_array['mon'];
		$day = $date_time_array['mday'];
		$year = $date_time_array['year'];
		
		$timestamp = mktime(0,0,0,$month,$day + $_POST['days'.$i],$year);
		$date = strftime('%d.%m.%Y',$timestamp);
		$query .= ', date = "'.$date.'";';
		mysql_query($query);
	}
	
	$_month['01'] = 'января';
	$_month['02'] = 'февраля';
	$_month['03'] = 'марта';
	$_month['04'] = 'апреля';
	$_month['05'] = 'мая';
	$_month['06'] = 'июня';
	$_month['07'] = 'июля';
	$_month['08'] = 'августа';
	$_month['09'] = 'сентября';
	$_month['10'] = 'октября';
	$_month['11'] = 'ноября';
	$_month['12'] = 'декабря';
	
	$service['1'] = 'Технический план';
	$service['2'] = 'Межевой план';
	$service['3'] = 'Топографическая съёмка местности';
	$service['4'] = 'Акт обследования';
	$service['5'] = 'Вынос границ земельного участка в натуру';
	$service['6'] = 'План для суда';
	
	$timestamp = time();
	$date_time_array = getdate($timestamp);

	$month = $date_time_array['mon'];
	$day = $date_time_array['mday'];
	$year = $date_time_array['year'];
	
	$timestamp = mktime(0,0,0,$month,$day,$year);
	$date = strftime('%d.%m.%Y',$timestamp);
	
	$today = explode(".", $date);
	//Акт
	$objPHPExcel = PHPExcel_IOFactory::load("excel/application/Акт.xlsx");
	$objPHPExcel->setActiveSheetIndex(0);
	$objPHPExcel->getActiveSheet()->setCellValue('B6', iconv("Windows-1251", "UTF-8", "Акт № ".$Act." от ".$today[0]." ".$_month[$today[1]]." ".$today[2]." г."));
	if($_POST['Client'] == "Organization")
		$objPHPExcel->getActiveSheet()->setCellValue('B9', iconv("Windows-1251", "UTF-8", "Заказчик: ".$_POST['org_full_name']));
	else
		$objPHPExcel->getActiveSheet()->setCellValue('B9', iconv("Windows-1251", "UTF-8", "Заказчик: ".$_POST['Clientfio']));
	if($_POST['numservice'] > 1){
		$objPHPExcel->getActiveSheet()->insertNewRowBefore(12, ($_POST['numservice'] - 1));
		for($i = 1; $i <= $_POST['numservice'] - 1; $i++){
			$objPHPExcel->getActiveSheet()->mergeCells('C'.(11 + $i).':D'.(11 + $i));
		}
	}
	$summ = 0;
	for($i = 1; $i <= $_POST['numservice']; $i++){
		$objPHPExcel->getActiveSheet()->setCellValue('B'.(11 + $i), iconv("Windows-1251", "UTF-8", $i));
		if($_POST['name'.$i] == "")
			$objPHPExcel->getActiveSheet()->setCellValue('C'.(11 + $i), iconv("Windows-1251", "UTF-8", $service[$_POST['type'.$i]]));
		else
			$objPHPExcel->getActiveSheet()->setCellValue('C'.(11 + $i), iconv("Windows-1251", "UTF-8", $_POST['name'.$i]));
		$objPHPExcel->getActiveSheet()->setCellValue('E'.(11 + $i), iconv("Windows-1251", "UTF-8", "шт"));
		$objPHPExcel->getActiveSheet()->setCellValue('F'.(11 + $i), iconv("Windows-1251", "UTF-8", $_POST['number'.$i]));
		$objPHPExcel->getActiveSheet()->setCellValue('G'.(11 + $i), iconv("Windows-1251", "UTF-8", ($_POST['koef'.$i]*$_POST['cost'.$i])));
		$summ += $_POST['number'.$i]*$_POST['koef'.$i]*$_POST['cost'.$i];
		$objPHPExcel->getActiveSheet()->setCellValue('H'.(11 + $i), iconv("Windows-1251", "UTF-8", ($_POST['number'.$i]*$_POST['koef'.$i]*$_POST['cost'.$i])));
	}
	$objPHPExcel->getActiveSheet()->setCellValue('H'.(12 + $_POST['numservice']), iconv("Windows-1251", "UTF-8", $summ));
	$objPHPExcel->getActiveSheet()->setCellValue('E'.(14 + $_POST['numservice']), iconv("Windows-1251", "UTF-8", num2str($summ)));
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter->save("excel/out_application/Акт.xlsx");
	
	
	
	//Счет
	$objPHPExcel = PHPExcel_IOFactory::load("excel/application/Счет.xlsx");
	$objPHPExcel->setActiveSheetIndex(0);
	$objPHPExcel->getActiveSheet()->setCellValue('B14', iconv("Windows-1251", "UTF-8", "Счет № ".$Bill." от ".$today[0]." ".$_month[$today[1]]." ".$today[2]." г."));
	if($_POST['Client'] == "Organization")
		$objPHPExcel->getActiveSheet()->setCellValue('B17', iconv("Windows-1251", "UTF-8", "Плательщик: ".$_POST['org_full_name']));
	else
		$objPHPExcel->getActiveSheet()->setCellValue('B17', iconv("Windows-1251", "UTF-8", "Плательщик: ".$_POST['Clientfio']));
	if($_POST['numservice'] > 1){
		$objPHPExcel->getActiveSheet()->insertNewRowBefore(20, ($_POST['numservice'] - 1));
		for($i = 1; $i <= $_POST['numservice'] - 1; $i++){
			$objPHPExcel->getActiveSheet()->mergeCells('C'.(19 + $i).':D'.(19 + $i));
		}
	}
	$summ = 0;
	for($i = 1; $i <= $_POST['numservice']; $i++){
		$objPHPExcel->getActiveSheet()->setCellValue('B'.(19 + $i), iconv("Windows-1251", "UTF-8", $i));
		if($_POST['name'.$i] == "")
			$objPHPExcel->getActiveSheet()->setCellValue('C'.(19 + $i), iconv("Windows-1251", "UTF-8", $service[$_POST['type'.$i]]));
		else
			$objPHPExcel->getActiveSheet()->setCellValue('C'.(19 + $i), iconv("Windows-1251", "UTF-8", $_POST['name'.$i]));
		$objPHPExcel->getActiveSheet()->setCellValue('E'.(19 + $i), iconv("Windows-1251", "UTF-8", "шт"));
		$objPHPExcel->getActiveSheet()->setCellValue('F'.(19 + $i), iconv("Windows-1251", "UTF-8", $_POST['number'.$i]));
		$objPHPExcel->getActiveSheet()->setCellValue('G'.(19 + $i), iconv("Windows-1251", "UTF-8", ($_POST['koef'.$i]*$_POST['cost'.$i])));
		$summ += $_POST['number'.$i]*$_POST['koef'.$i]*$_POST['cost'.$i];
		$objPHPExcel->getActiveSheet()->setCellValue('H'.(19 + $i), iconv("Windows-1251", "UTF-8", ($_POST['number'.$i]*$_POST['koef'.$i]*$_POST['cost'.$i])));
	}
	$objPHPExcel->getActiveSheet()->setCellValue('H'.(20 + $_POST['numservice']), iconv("Windows-1251", "UTF-8", $summ));
	$objPHPExcel->getActiveSheet()->setCellValue('H'.(22 + $_POST['numservice']), iconv("Windows-1251", "UTF-8", $summ));
	$objPHPExcel->getActiveSheet()->setCellValue('B'.(24 + $_POST['numservice']), iconv("Windows-1251", "UTF-8", "Всего наименований ".$_POST['numservice'].", на сумму"));
	$objPHPExcel->getActiveSheet()->setCellValue('E'.(24 + $_POST['numservice']), iconv("Windows-1251", "UTF-8", $summ));
	$objPHPExcel->getActiveSheet()->setCellValue('B'.(25 + $_POST['numservice']), iconv("Windows-1251", "UTF-8", num2str($summ)));
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter->save("excel/out_application/Счет.xlsx");
	
	
	
	//Заявка
	$objPHPExcel = PHPExcel_IOFactory::load("excel/application/Заявка.xlsx");
	$objPHPExcel->setActiveSheetIndex(0);
	if($_POST['Client'] == "Organization")
		$objPHPExcel->getActiveSheet()->setCellValue('C2', iconv("Windows-1251", "UTF-8", $_POST['org_fio_cont']));
	else
		$objPHPExcel->getActiveSheet()->setCellValue('C2', iconv("Windows-1251", "UTF-8", $_POST['Clientfio']));
	$objPHPExcel->getActiveSheet()->setCellValue('C3', iconv("Windows-1251", "UTF-8", $_POST['pass_reg']));
	$objPHPExcel->getActiveSheet()->setCellValue('C4', iconv("Windows-1251", "UTF-8", "Паспорт серия ".$_POST['pass_series']." № ".$_POST['pass_number']));
	$objPHPExcel->getActiveSheet()->setCellValue('C5', iconv("Windows-1251", "UTF-8", $_POST['pass_date']." г. ".$_POST['pass_given'].", код подразделения ".$_POST['pass_code']));
	$objPHPExcel->getActiveSheet()->setCellValue('C6', iconv("Windows-1251", "UTF-8", $_POST['telephone']));
	$objPHPExcel->getActiveSheet()->setCellValue('B8', iconv("Windows-1251", "UTF-8", "Заявка № ".$Application." от ".$date));
	$f = fopen("Regions.csv", "r");
	$data = fgetcsv($f, 1000, ';');
	for($j = 1;;$j++){
			if($data[0] == $_POST['region'])
			{
				$regionname = $data[1];
				break;
			}
			$data = fgetcsv($f, 1000, ';');
	}
	fclose($f);
	
	
	if($_POST['region'] != "01"){
		$address = $regionname;
		if($_POST['District']){
			$address .= ", ".$_POST['District']." р-н";
		}
		if($_POST['City']){
			$address .= ", г ".$_POST['City'];
		}
		if($_POST['City_District']){
			$address .= ", ".$_POST['City_District']." р-н";
		}
		if($_POST['Soviet_Village']){
			$address .= ", ".$_POST['Soviet_Village']." c/c";
		}
		if($_POST['Locality']){
			$address .= ", ".$_POST['Locality_type']." ".$_POST['Locality'];
		}
		if($_POST['Street_Name']){
			$address .= ", ".$_POST['Street_Type']." ".$_POST['Street_Name'];
		}
		if($_POST['Level1_Name']){
			$address .= ", ".$_POST['Level1_Type']." ".$_POST['Level1_Name'];
		}
		if($_POST['Level2_Name']){
			$address .= ", ".$_POST['Level2_Type']." ".$_POST['Level2_Name'];
		}
		if($_POST['Level3_Name']){
			$address .= ", ".$_POST['Level3_Type']." ".$_POST['Level3_Name'];
		}
		if($_POST['Level4_Name']){
			$address .= ", ".$_POST['Level4_Type']." ".$_POST['Level4_Name'];
		}
		$objPHPExcel->getActiveSheet()->setCellValue('A12', iconv("Windows-1251", "UTF-8", $address));
	}
	if($_POST['numservice'] > 1){
		$objPHPExcel->getActiveSheet()->insertNewRowBefore(16, ($_POST['numservice'] - 1));
		for($i = 1; $i <= $_POST['numservice'] - 1; $i++){
			$objPHPExcel->getActiveSheet()->mergeCells('A'.(15 + $i).':G'.(15 + $i));
			$objPHPExcel->getActiveSheet()->mergeCells('H'.(15 + $i).':I'.(15 + $i));
		}
	}
	for($i = 1; $i <= $_POST['numservice']; $i++){
		$curservice = $i.". ";
		if($_POST['name'.$i] == "")
			$curservice .= $service[$_POST['type'.$i]];
		else
			$curservice .= $_POST['name'.$i];
		if($_POST['number'.$i] > 1)
			$curservice .= " (".$_POST['number'.$i]." шт.)";
		$objPHPExcel->getActiveSheet()->setCellValue('A'.(15 + $i), iconv("Windows-1251", "UTF-8", $curservice));
		$objPHPExcel->getActiveSheet()->setCellValue('H'.(15 + $i), iconv("Windows-1251", "UTF-8", $_POST['number'.$i]*$_POST['koef'.$i]*$_POST['cost'.$i]));
	}
	
	$objPHPExcel->getActiveSheet()->setCellValue('A'.(18 + $_POST['numservice']), iconv("Windows-1251", "UTF-8", "С суммой предварительной оплаты ".$_POST['prepayment']." руб. согласен, в случае превышения фактической стоимости работ над внесенной суммой , доплату гарантирую."));
	
	if($_POST['Client'] == "Organization")
		$fio = explode(" ", $_POST['org_fio_cont']);
	else
		$fio = explode(" ", $_POST['Clientfio']);
	$name = $fio[1];
	$patr = $fio[2];
	if($patr != ""){
		$tmp = "/ ".$fio[0]." ".$name[0].". ".$patr[0].". /";
	}else{
		$tmp = "/ ".$fio[0]." ".$name[0].". /";
	}
	$objPHPExcel->getActiveSheet()->setCellValue('C'.(20 + $_POST['numservice']), iconv("Windows-1251", "UTF-8", $tmp));
	$objPHPExcel->getActiveSheet()->setCellValue('H'.(20 + $_POST['numservice']), iconv("Windows-1251", "UTF-8", $date));
	
	$objPHPExcel->getActiveSheet()->setCellValue('C'.(22 + $_POST['numservice']), iconv("Windows-1251", "UTF-8", "/ ".$_POST['dispatch']." /"));
	$objPHPExcel->getActiveSheet()->setCellValue('H'.(22 + $_POST['numservice']), iconv("Windows-1251", "UTF-8", $date));
	
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter->save("excel/out_application/Заявка.xlsx");
	
	$out = $Number."\n".$Act."\n".$Bill."\n".$Deal."\n".$Number;
	$fout = fopen("app_num.csv", "w");
	fwrite($fout, $out);
	fclose($fout);
?>