#!/usr/local/bin/perl


use LWP::Simple;
use URI::URL;
use LWP::UserAgent;
use HTTP::Cookies;
use HTTP::Request::Common qw(POST);
use HTTP::Headers;


require "../sql.pl";
require "../subs.pl";
require "../config.pl";
require "../mparser.pl";
require "../cript.pl";
require "../zip.pl";
require "../mailer.pl";
$tmp_zip_dir="../tmp"; 	# временная папка доступная для записи скриптом
$protect_seconds=5;		# задержка во время авторизации 




$dbh=&user_connect();
if ($dbh)
{
	&hash_create();
	$black_list=get_black_list();
	$user_data=&login;
	if ($user_data)
	{
		$voteid=$$user_data{id}*17;
		$lang=&set_user_language;
		srand($$user_data{id});
		if ($query->param('action') eq 'login')			{&show_product();}
		elsif ($query->param('action') eq 'show_product')	{&print_header();&show_product();}
		elsif ($query->param('action') eq 'change_language')	{&change_language();}
		elsif ($query->param('action') eq 'get_file' && $query->param('file_id')>0)		{&get_file_user();}
		elsif ($query->param('action') eq 'promotion')						{&promotion();}
		elsif ($query->param('action') eq 'get_keys')						{&get_mail_user();}
		elsif ($query->param('action') eq 'get_upgrade' && $query->param('upgrade_id')>0)	{&get_upgrade();}
		
		elsif ($query->param('action') eq 'show_user_details')	{&show_user_details();}
		elsif ($query->param('action') eq 'change_details')	{&change_details();}
		elsif ($query->param('action') eq 'logout')		{&logout();}
		else {	&print_header();&show_product();}
	}
	&sql_disconnect();
}else
{
	&print_header;
	print "Server busy";
}

sub is_locked_user
{
	my $user_id=@_[0];
	
	$sth = $dbh->prepare("select * from fr_black_user where user_id=?");
	$sth->execute($user_id);
	if ($sth->fetchrow_hashref(NAME_lc))
	{
		return 1;
	}else
	{
		return 0;
	}
}


sub ret_rand_str
{
	my ($slen)=@_;
        my $retp="";
	for (my $i=0;$i<$slen;$i++)
	{
		$retp.=chr(int(rand(10000)) % 256);
	}
	return $retp;
}

sub gen_black_key
{
	my $key=&ret_rand_str(7)."_For bangkalan;)_".&ret_rand_str(5);
	my $df=0;

	for ($i=0;$i<29;$i++)
	{
		my $vr=substr($key,$i,1);
		$vr=unpack('C',$vr);
		$df=$df ^ $vr;
	}
	$key.=chr($df);
	return uc(unpack("H*", $key));
}



sub check_locked_user
{
	my $test_id=@_[0];
	$sth = $dbh->prepare("select * from fr_user where id=?");
	$sth->execute($test_id) or die $sth->errstr."\n";
	
	if ($user_info=$sth->fetchrow_hashref(NAME_lc))
	{
		my $patern='';
		my $is_black=0;
		foreach my $i (@{$$black_list{black_ip}})
		{
		
			if (eval("\$ENV\{REMOTE_ADDR\}=~".$$i{val}) || eval("\$ENV\{HTTP_X_FORWARDED_FOR\}=~".$$i{val}))
			{
				&lock_user($test_id,$$i{val});
				return 1;
			}
		}
		foreach my $i (@{$$black_list{black_email}})
		{
			if (eval("\$\$user_info\{email\}=~".$$i{val}))
			{
				&lock_user($test_id,$$i{val});
				return 1;
			}
		}
	}
	return 0;
}



sub lock_user
{
	my ($lock_id,$pattern)=@_;
	
	my $res_mes='';
	
	
	unless (&is_locked_user($lock_id))
	{
		$sth = $dbh->prepare("insert into fr_black_user (user_id,lock_type) values (?,'auto')");
		$sth->execute($lock_id) or die $sth->errstr."\n";
		$res_mes.="id:$lock_id отключен!\r\n";
	}
	$sth = $dbh->prepare("select * from fr_download_log where user_id=? and load_type=39");
	$sth->execute($lock_id) or die $sth->errstr."\n";
#	if ($is_download=$sth->fetchrow_hashref(NAME_lc))
#	{
#		$res_mes.="замена ключей на триальные не произведена!!\r\n так как он уже качал файлы...\r\n";
#	}else
#	{
		my $c_count=&change_keys($lock_id);
		$res_mes.="$c_count ключей заменено на триальные\r\n";
#	}
	
	&smailer("tmpl/mail_black.tmpl",{
	%ENV,
	result=>$res_mes,
	id=>$lock_id,
	val=>$pattern});

	&smailer("tmpl/sms_black.tmpl",{
	%ENV,
	result=>"lock user",
	id=>$lock_id,
	val=>$pattern});
	
}

sub change_keys
{
	my $lock_id=@_[0];
	$sth = $dbh->prepare("select * from fr_key_product where user_id=? AND is_black='No'");
	$sth->execute($lock_id) or die $sth->errstr."\n";
	my @exist_keys;
	while($row=$sth->fetchrow_hashref(NAME_lc))
	{
		push @exist_keys, $row;
	}
	my $c_count=0;
	foreach my $i (@exist_keys)
	{
		my $new_b_key=&gen_black_key();
		$sth = $dbh->prepare("insert into fr_key (val,product_id,accepted,user_id) values (?,?,'init',?)") or die $dbh->errstr."\n";
		$sth->execute($new_b_key,$$i{product_id},$lock_id) or die $sth->errstr."\n";

		$sth = $dbh->prepare("select id from fr_key where accepted='init' AND product_id=? LIMIT 1") or die $dbh->errstr."\n";
		$sth->execute($$i{product_id}) or die $sth->errstr."\n";
		if ($new_key=$sth->fetchrow_hashref(NAME_lc))
		{
			$sth = $dbh->prepare("update fr_key set accepted='replaced' where id=?") or die $dbh->errstr."\n";
			$sth->execute($$i{key_id}) or die $sth->errstr."\n";
			$sth = $dbh->prepare("update fr_key set accepted='yes' where id=?") or die $dbh->errstr."\n";
			$sth->execute($$new_key{id}) or die $sth->errstr."\n";
			$sth = $dbh->prepare("update fr_key_product set key_id=?, is_black='Yes' where id=?") or die $dbh->errstr."\n";
			$sth->execute($$new_key{id},$$i{id}) or die $sth->errstr."\n";
			$c_count++;
		}
	}
	return $c_count;
}

sub get_black_list
{
	$sth = $dbh->prepare("select * from fr_black_ip");
	$sth->execute() or die $sth->errstr."\n";
	my @black_ip;
	while($row=$sth->fetchrow_hashref(NAME_lc))
	{
		push @black_ip, $row;
	}
	
	$sth = $dbh->prepare("select * from fr_black_email");
	$sth->execute() or die $sth->errstr."\n";
	my @black_email;
	while($row=$sth->fetchrow_hashref(NAME_lc))
	{
		push @black_email, $row;
	}

#	$sth = $dbh->prepare("select * from fr_black_user");
#	$sth->execute() or die $sth->errstr."\n";
#	my %black_user;
#	while($row=$sth->fetchrow_hashref(NAME_lc))
#	{
#		$black_user{$row{id}}=$row;
#	}
	my %result;
	$result{black_ip}=\@black_ip;
	$result{black_email}=\@black_email;
#	$result{black_user}=\%black_user;
	return \%result;
}


sub promotion
{
	$sth = $dbh->prepare("select * from fr_user where fr_user.id=?");
	$sth->execute($$user_data{id}) or die $sth->errstr."\n";
	$row=$sth->fetchrow_hashref(NAME_lc);
	&print_header;
	&user_page_print("tmpl/$lang/show_promotion.tmpl",$row);

}

sub get_upgrade
{

	$upgrade_id=$query->param('upgrade_id');
	$upgrade_id=~s/\D+//igm;
	my $sth = $dbh->prepare("SELECT GET_LOCK(\"session_locker\",60) as res") or print $dbh->errstr."\n";
	$sth->execute() or print $sth->errstr."\n";
	my $result=$sth->fetchrow_hashref(NAME_lc);
	$sth->finish;
	if ($$result{res})
	{
		
	}else
	{
		exit;
	}

	
	$sth = $dbh->prepare("select    fr_registration.date,
					fr_registration.status_int,
					fr_registration_product.product_id
					
					from
						fr_registration_product,
						fr_registration
					where
					fr_registration_product.registration_id=fr_registration.id AND
					fr_registration.user_id=? AND
					(
						fr_registration.status_int=? OR
						fr_registration.status_int=? OR
						fr_registration.diler_id>0 OR
						fr_registration.robot_id>0
					) AND
					fr_registration.status_int!=?
					") or die $dbh->errstr."\n";


	$sth->execute($$user_data{id},
	$_REGISTRATION_STATUS{OPLATA},
	$_REGISTRATION_STATUS{OK},
	$_REGISTRATION_STATUS{CANCEL}
	
	) or die $sth->errstr."\n";


	my @licenz_products;
	while($row=$sth->fetchrow_hashref(NAME_lc))
	{
		push @licenz_products, $row;
	}


	
	$sth = $dbh->prepare("select    fr_upgrade_codes.*,
					fr_upgrade.to_product,
					fr_upgrade.name,
					fr_upgrade.comment,
					fr_upgrade.from_product1,
					fr_upgrade.from_product2,
					fr_upgrade.from_product3
	
					from
						fr_upgrade,
						fr_upgrade_codes
					where
					fr_upgrade.id=fr_upgrade_codes.upgrade_id AND
					(fr_upgrade_codes.reg_type=? OR fr_upgrade_codes.reg_type2=?)
					") or die $dbh->errstr."\n";
	$sth->execute($$user_data{user_type_int},$$user_data{user_type_int}) or die $sth->errstr."\n";

	
	my @upgrade_list;
	while($row=$sth->fetchrow_hashref(NAME_lc))
	{
		push @upgrade_list, $row;
		
	}
	
	
	foreach my $product_row (@licenz_products)
	{
		foreach my $u_line (@b_upgrade)
		{
			if ($$u_line{product_id}==$$product_row{product_id})
			{
				if ($$found_lavel{$$u_line{id}}<$$u_line{m})
				{
					$$found_lavel{$$u_line{id}}=$$u_line{m};
				}
			}
		}
	}
	
	foreach my $upgrade_row (@upgrade_list)
	{
		$$upgrade_row{num_sum}=$$upgrade_row{price_str};
		$$upgrade_row{num_sum}=~s/\D+//igm;
		$$upgrade_row{num_sum2}=$$upgrade_row{price_str2};
		$$upgrade_row{num_sum2}=~s/\D+//igm;
		foreach my $product_row (@licenz_products)
		{
		
		
		
		
			if ($$product_row{product_id}==$$upgrade_row{from_product1})
			{
				$$upgrade_row{is_from_product1}=1;
			}
			if ($$product_row{product_id}==$$upgrade_row{from_product2})
			{
				$$upgrade_row{is_from_product2}=1;
			}
			if ($$product_row{product_id}==$$upgrade_row{from_product3})
			{
				$$upgrade_row{is_from_product3}=1;
			}
			if ($$product_row{product_id}==$$upgrade_row{to_product})
			{
				$$upgrade_row{off}=1;
			}
			if (($$product_row{date} cmp $upgr_date)==-1 && (($$product_row{product_id}==$$upgrade_row{from_product1}) || ($$product_row{product_id}==$$upgrade_row{from_product3})))
			{
				$$upgrade_row{date_good}=1;
			}elsif ($$product_row{num_sum} && !$$product_row{num_sum2})
			{
				$$upgrade_row{date_good}=1;
			}
		}
		if (!$$upgrade_row{m_id})
		{
			foreach my $u_line (@b_upgrade)
			{
				if ($$u_line{product_id}==$$upgrade_row{to_product})
				{
					$$upgrade_row{m}=$$u_line{m};
					$$upgrade_row{m_id}=$$u_line{id};
				}
			}
		}
	}
	
	
	
	
	foreach my $upgrade_row (@upgrade_list)
	{
		if (!$$upgrade_row{off})
		{
			if (
				($$upgrade_row{from_product1}>0 && $$upgrade_row{from_product2}==0 && $$upgrade_row{from_product3}==0 && $$upgrade_row{is_from_product1})
				||
				($$upgrade_row{from_product1}>0 && $$upgrade_row{from_product2}==0 && $$upgrade_row{from_product3}>0 && ($$upgrade_row{is_from_product1} || $$upgrade_row{is_from_product3}))
				||
				($$upgrade_row{from_product1}>0 && $$upgrade_row{from_product2}>0 && $$upgrade_row{from_product3}==0 && ($$upgrade_row{is_from_product1} && $$upgrade_row{is_from_product2}))
				||
				($$upgrade_row{from_product1}>0 && $$upgrade_row{from_product2}>0 && $$upgrade_row{from_product3}>0 && (($$upgrade_row{is_from_product1} || $$upgrade_row{is_from_product3}) && $$upgrade_row{is_from_product2}))
			)
			{
				$$upgrade_row{on}=1;
			}
		}
	}

	
	my (%to_hash,@upgrade_show);
	

	foreach my $upgrade_row (@upgrade_list)
	{
		if ($$upgrade_row{on})
		{
			if ($$found_lavel{$$upgrade_row{m_id}}<$$upgrade_row{m})
			{
				if ($to_hash{$$upgrade_row{to_product}})
				{
					if ($$upgrade_row{num_sum}>0)
					{
						if (
							((
								!$to_hash{$$upgrade_row{to_product}}{date_good} 
								&& 
								$to_hash{$$upgrade_row{to_product}}{num_sum2}>0
							)
							?
							$to_hash{$$upgrade_row{to_product}}{num_sum2}
							:
							$to_hash{$$upgrade_row{to_product}}{num_sum})
								
							>
								
							((
								!$$upgrade_row{date_good} 
								&& 
								$$upgrade_row{num_sum2}>0
							)
							?
							$$upgrade_row{num_sum2}
							:
							$$upgrade_row{num_sum})
						)
						{
							$to_hash{$$upgrade_row{to_product}}=$upgrade_row;
						}
					}else
					{
						$to_hash{$$upgrade_row{to_product}}=$upgrade_row;
					}
				}else
				{
					$to_hash{$$upgrade_row{to_product}}=$upgrade_row;
				}
			}
		}
	}	
	
	
	$retval=0;
	my ($p_product_id);
	foreach my $product_key (keys(%to_hash))
	{
#		print ${$to_hash{$product_key}}{upgrade_id}."=".$upgrade_id."=".${$to_hash{$product_key}}{num_sum}."<br>";
		if (${$to_hash{$product_key}}{upgrade_id}==$upgrade_id && !${$to_hash{$product_key}}{num_sum}>0)
		{
			$retval=1;
			$p_product_id=$product_key;
			$p_product_bild_id=&last_bild($p_product_id);
			
		}
	}	
    	
	if ($retval)
	{
		
	
	

#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>



		my $new_x_id=&gen_x_id($$user_data{email},$$user_data{family}.$$user_data{name});
		
		$sth = $dbh->prepare("insert into fr_registration (
			x_id,
			convertation_type_int,
			date,
			sys_date,
			schet_id,
			user_id,
			mail_id,
			registration_type_int,
			status_int,
			diler_id,
			robot_id,
			worker_id
		) values (?,?,NOW(),NOW(),?,?,?,?,?,?,?,?)") or die $dbh->errstr."\n";

		$sth->execute(
			$new_x_id,
			$_CONVERTATION_TYPE{MANUAL},
			0,
			$$user_data{id},
			'',
			$$user_data{user_type_int},
			$_REGISTRATION_STATUS{OPLATA},
			0,
			0,
			0
		) or die $sth->errstr."\n";



		$sth = $dbh->prepare("select max(id) as max_id from fr_registration") or die $dbh->errstr."\n";
		$sth->execute() or die $sth->errstr."\n";
		my $row=$sth->fetchrow_hashref(NAME_lc);
		my $new_reg_id=$$row{max_id};

		$sth = $dbh->prepare("select product_id from fr_product_bild where id=?") or die $dbh->errstr."\n";
		$sth->execute($p_product_id) or die $sth->errstr."\n";
		my $row=$sth->fetchrow_hashref(NAME_lc);
		my $pr_id=$$row{product_id};



		$sth = $dbh->prepare("insert into fr_registration_product (
					registration_id,
					skidka_usd,
					skidka_rub,
					skidka_eur,
					summa_usd,
					summa_rub,
					summa_eur,
					valuta,
					product_id,
					product_bild_id,
					product_count
				) values (?,?,?,?,?,?,?,?,?,?,?)") or die $dbh->errstr."\n";
		$sth->execute(
					$new_reg_id,
					0,
					0,
					0,
					0,
					0,
					0,
					$_VALUTA{USD},
					$p_product_id,
					$p_product_bild_id,
					1
				) or die $sth->errstr."\n";

		$sth = $dbh->prepare("insert into fr_platezh (
				mail_id,
				robot_id,
				worker_id,
				user_id,
				summa_usd,
				summa_rub,
				summa_eur,
				valuta,
				status,
				date
			) values (?,?,?,?,?,?,?,?,?,NOW())") or die $dbh->errstr."\n";
		$sth->execute(
				0,
				0,
				0,
				$$user_data{id},
				0,
				0,
				0,
				$_VALUTA{USD},
				$_PLATEZH_STATUS{CLOSED}
			) or die $sth->errstr."\n";

		$sth = $dbh->prepare("select max(id) as max_id from fr_platezh") or die $dbh->errstr."\n";
		$sth->execute() or die $sth->errstr."\n";
		my $row=$sth->fetchrow_hashref(NAME_lc);
		my $new_platezh_id=$$row{max_id};
		$sth = $dbh->prepare("insert into fr_platezh_product (
						platezh_id,
						product_id,
						product_bild_id,
						product_count,
						product_closed
					) values (?,?,?,?,?)") or die $dbh->errstr."\n";
		$sth->execute(
						$new_platezh_id,
						$p_product_id,
						$p_product_bild_id,
						1,
						1
					) or die $sth->errstr."\n";


			
		$sth = $dbh->prepare("insert into fr_zachet (
				worker_id,
				user_id,
				date
			) values (?,?,NOW())") or die $dbh->errstr."\n";

		$sth->execute(
				0,
				$$user_data{id}) or die $sth->errstr."\n";
		$sth = $dbh->prepare("select max(id) as max_id from fr_zachet") or die $dbh->errstr."\n";
		$sth->execute() or die $sth->errstr."\n";
		my $row=$sth->fetchrow_hashref(NAME_lc);
		my $new_zachet_id=$$row{max_id};
			             	
		$sth = $dbh->prepare("insert into fr_zachet_pay	 (
						zachet_id,
						platezh_id,
						product_id,
						product_bild_id,
						product_count
					) values (?,?,?,?,?)") or die $dbh->errstr."\n";
		$sth->execute(
						$new_zachet_id,
						$new_platezh_id,
						$p_product_id,
						$p_product_bild_id,
						1) or die $sth->errstr."\n";

		$sth = $dbh->prepare("insert into fr_zachet_registration (
						zachet_id,
						registration_id,
						product_id,
						product_bild_id,
						product_count
					) values (?,?,?,?,?)") or die $dbh->errstr."\n";
		$sth->execute(
						$new_zachet_id,
						$new_reg_id,
						$p_product_id,
						$p_product_bild_id,
						1) or die $sth->errstr."\n";

		&create_reg_key($new_reg_id);
#<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<



	
	}
	&move_to("?");
	
}

sub forgot_pas
{
	&print_header;
#	&tmpl_print("tmpl/forgot_pas.tmpl");
	$lang="en";
	&user_page_print("tmpl/forgot_pas.tmpl",{});
	
}

sub send_me_pas
{
	&print_header;
	$lang="en";
	sleep($protect_seconds);
	if (length(&all_trim($query->param('email')))>0)
	{
		my $send_result=&pass_to_user($query->param('email'),$_DOWNLOAD_TYPE{EMAIL_PASS});
		if ($send_result>0)
		{
			&user_page_print("tmpl/forgot_pas.tmpl",{sended=>1,email=>$query->param('email')});
		}else
		{
			&user_page_print("tmpl/forgot_pas.tmpl",{send_err=>1,email=>$query->param('email')});
		}

	}else
	{
		&tmpl_print("tmpl/forgot_pas.tmpl",{send_err=>1,email=>0});
	}
}


sub logout
{
	print $query->header(-type=>'text/html; charset=windows-1251', -cookie=>'auth=exit@exit');
}

sub user_page_print
{
	my ($template,$hash,$cont_menu)=@_;
	my $page_content=&ret_page(($template?$template:"tmpl/$lang/main_content.tmpl"),{%$user_data,%$hash,voteid=>$voteid});
	my $main_menu=&ret_page("tmpl/$lang/main_menu.tmpl",{%$user_data,%$hash,languages=>&ret_languages_user,voteid=>$voteid});
	my $sub_menu=&ret_page($cont_menu,{%$user_data,%$hash,voteid=>$voteid}) if ($cont_menu);
	&tmpl_print("tmpl/$lang/main_page.tmpl",{%$user_data,($query->param('action')?$query->param('action'):"show_product")=>1,page_content=>$$page_content,main_menu=>$$main_menu,sub_menu=>$$sub_menu,voteid=>$voteid});
}





sub login
{
	my $row;

#	unless (!$ENV{HTTP_REFERER} || $ENV{HTTP_REFERER}=~/^http\:\/\/www\.fastreport\.org/ || $ENV{HTTP_REFERER}=~/^http\:\/\/66\.227\.63\.207/)
#	{
#		&smailer("tmpl/mail_refs.tmpl",{%ENV,
#		cookie=>($query->cookie('auth')?$query->cookie('auth'):''),
#		login=>($query->param('login_name')?$query->param('login_name'):''),
#		password=>($query->param('password')?$query->param('password'):'')});
#	}

	if ($query->param('action') eq 'login')
	{
		$sth = $dbh->prepare("select * from fr_user where login=? AND (password=? OR MD5(password)=?) AND status_int=$_USER_STATUS{ACTIVE}") or die $dbh->errstr."\n";
		$sth->execute($query->param('login_name'),$query->param('password'),$query->param('password')) or die $sth->errstr."\n";
		if ($row=$sth->fetchrow_hashref(NAME_lc))
		{
			$sth = $dbh->prepare("insert into fr_pass_err (
				REMOTE_ADDR,
				HTTP_X_FORWARDED_FOR,
				HTTP_VIA,
				login,
				password,
				QUERY_STRING,
				date,
				user_id,
				is_error
				) values (?,?,?,?,?,?,NOW(),?,?)");

			$sth->execute(	$ENV{REMOTE_ADDR},
					$ENV{HTTP_X_FORWARDED_FOR},
					$ENV{HTTP_VIA},
					$query->param('login_name'),
					$query->param('password'),
					$ENV{QUERY_STRING},
					$$row{id},
					0
					);

			unless (&is_locked_user($$row{id}))
			{
				&check_locked_user($$row{id});
			}else
			{
				&smailer("tmpl/mail_black.tmpl",{
				%ENV,
				result=>'BAD USER LOGIN!',
				id=>$$row{id},
				val=>''});
				
				&smailer("tmpl/sms_black.tmpl",{
				%ENV,
				result=>'BAD USER LOGIN!',
				id=>$$row{id},
				val=>''});
			}

			sleep($protect_seconds);
			print $query->header(-type=>'text/html; charset=windows-1251', -cookie=>'auth='.$query->param('login_name').'@'.$query->param('password'));
			return $row;
		}else
		{
			&smailer("tmpl/mail_pass_err.tmpl",{%ENV,
				login=>($query->param('login_name')?$query->param('login_name'):''),
				password=>($query->param('password')?$query->param('password'):'')});
			$sth = $dbh->prepare("insert into fr_pass_err (
					REMOTE_ADDR,
					HTTP_X_FORWARDED_FOR,
					HTTP_VIA,
					login,
					password,
					QUERY_STRING,
					date,
					user_id,
					is_error
					) values (?,?,?,?,?,?,NOW(),?,?)");

			$sth->execute(	$ENV{REMOTE_ADDR},
					$ENV{HTTP_X_FORWARDED_FOR},
					$ENV{HTTP_VIA},
					$query->param('login_name'),
					$query->param('password'),
					$ENV{QUERY_STRING},
					0,
					1);

	

			sleep($protect_seconds);
			&login_page();
			return 0;
		}
	}else
	{
		if ($query->param('action') eq 'forgot_pas')		{&forgot_pas();return 0;}		
		elsif ($query->param('action') eq 'send_me_pas')	{&send_me_pas();return 0;}
		elsif ($query->cookie('auth'))
		{
			$sth = $dbh->prepare("select * from fr_user where (CONCAT(login,?,password)=? OR CONCAT(login,?,MD5(password))=?) AND status_int=22") or die $dbh->errstr."\n";
			$sth->execute('@',$query->cookie('auth'),'@',$query->cookie('auth')) or die $sth->errstr."\n";
			if ($row=$sth->fetchrow_hashref(NAME_lc))
			{
				unless (&is_locked_user($$row{id}))
				{
					if (&check_locked_user($$row{id}))
					{
						$$row{locked}=1;
					}
				}else
				{
					$$row{locked}=1;
					&smailer("tmpl/mail_black.tmpl",{
					%ENV,
					result=>'BAD USER PAGE LOAD!',
					id=>$$row{id},
					val=>''});
				}
			
				return $row;
			}else
			{
				if ($query->cookie('auth') ne 'exit@exit')
				{
					&smailer("tmpl/mail_pass_err.tmpl",{%ENV,
						cookie=>($query->cookie('auth')?$query->cookie('auth'):''),
						login=>($query->param('login_name')?$query->param('login_name'):''),
						password=>($query->param('password')?$query->param('password'):'')});
				}
				$sth = $dbh->prepare("insert into fr_pass_err (
					REMOTE_ADDR,
					HTTP_X_FORWARDED_FOR,
					HTTP_VIA,
					login,
					password,
					QUERY_STRING,
					date,
					user_id,
					is_error
					) values (?,?,?,?,?,?,NOW(),?,?)");

				$sth->execute(	$ENV{REMOTE_ADDR},
					$ENV{HTTP_X_FORWARDED_FOR},
					$ENV{HTTP_VIA},
					'cookie',
					$query->cookie('auth'),
					$ENV{QUERY_STRING},
					0,
					2);

	

				sleep($protect_seconds);
				&login_page();
				return 0;
			}
		}else
		{
			&login_page();
			return 0;
		}
		
	}
}

sub login_page
{
	&print_header;
#	&tmpl_print("tmpl/login.tmpl");
	$lang="en";
	&user_page_print("tmpl/login.tmpl",{});
}



sub show_product
{
	$upgr_date="2004-09-01";
	$add_hash=@_[0];
	$query->param(-name=>'action',-value=>'show_product');
	$sth = $dbh->prepare("select DISTINCT fr_product_bild.id as product_bild_id,
					fr_product_bild.bild_version,
					fr_product.version,
					fr_product_bild.bild_version,
					fr_product.id as product_id,
					fr_product_bild.comment

					from
						fr_product_bild,
						fr_product,
						fr_registration_product,
						fr_registration
					where
					fr_product.current_product_bild_id=fr_product_bild.id AND
					fr_registration_product.product_id=fr_product.id AND
					fr_registration_product.registration_id=fr_registration.id AND
					fr_registration.user_id=? AND
					(
						fr_registration.status_int=? OR
						fr_registration.status_int=? OR
						fr_registration.diler_id>0 OR
						fr_registration.robot_id>0
					) AND
					fr_registration.status_int!=?
					") or die $dbh->errstr."\n";



	$sth->execute($$user_data{id},
	$_REGISTRATION_STATUS{OPLATA},
	$_REGISTRATION_STATUS{OK},
	$_REGISTRATION_STATUS{CANCEL}
	
	) or die $sth->errstr."\n";

	
		
	


	my @product_list;
	while($row=$sth->fetchrow_hashref(NAME_lc))
	{
		push @product_list, $row;
	}

	





	$sth = $dbh->prepare("select    fr_registration.date,
					fr_registration.status_int,
					fr_registration_product.product_id
					
					from
						fr_registration_product,
						fr_registration
					where
					fr_registration_product.registration_id=fr_registration.id AND
					fr_registration.user_id=? AND
					(
						fr_registration.status_int=? OR
						fr_registration.status_int=? OR
						fr_registration.diler_id>0 OR
						fr_registration.robot_id>0
					) AND
					fr_registration.status_int!=?
					") or die $dbh->errstr."\n";




	$sth->execute($$user_data{id},
	$_REGISTRATION_STATUS{OPLATA},
	$_REGISTRATION_STATUS{OK},
	$_REGISTRATION_STATUS{CANCEL}
	
	) or die $sth->errstr."\n";


	my @licenz_products;
	while($row=$sth->fetchrow_hashref(NAME_lc))
	{
		push @licenz_products, $row;
	}

	
	$sth = $dbh->prepare("select    fr_upgrade_codes.*,
					fr_upgrade.to_product,
					fr_upgrade.name,
					fr_upgrade.comment,
					fr_upgrade.from_product1,
					fr_upgrade.from_product2,
					fr_upgrade.from_product3
	
					from
						fr_upgrade,
						fr_upgrade_codes
					where
					fr_upgrade.id=fr_upgrade_codes.upgrade_id AND
					(fr_upgrade_codes.reg_type=? OR fr_upgrade_codes.reg_type2=?)
					") or die $dbh->errstr."\n";
	$sth->execute($$user_data{user_type_int},$$user_data{user_type_int}) or die $sth->errstr."\n";

	
	my @upgrade_list;

	while($row=$sth->fetchrow_hashref(NAME_lc))
	{
		if ($$row{to_product}!=17)
		{
			push @upgrade_list, $row;
		}
	}
	
	
	foreach my $product_row (@licenz_products)
	{
		foreach my $u_line (@b_upgrade)
		{
			if ($$u_line{product_id}==$$product_row{product_id})
			{
				if ($$found_lavel{$$u_line{id}}<$$u_line{m})
				{
					$$found_lavel{$$u_line{id}}=$$u_line{m};
				}
			}
		}
	}
	
	foreach my $upgrade_row (@upgrade_list)
	{
		$$upgrade_row{num_sum}=$$upgrade_row{price_str};
		$$upgrade_row{num_sum}=~s/\D+//igm;
		$$upgrade_row{num_sum2}=$$upgrade_row{price_str2};
		$$upgrade_row{num_sum2}=~s/\D+//igm;
		
		
	
		foreach my $product_row (@licenz_products)
		{
		
		
		
		
			if ($$product_row{product_id}==$$upgrade_row{from_product1})
			{
				$$upgrade_row{is_from_product1}=1;
			}
			if ($$product_row{product_id}==$$upgrade_row{from_product2})
			{
				$$upgrade_row{is_from_product2}=1;
			}
			if ($$product_row{product_id}==$$upgrade_row{from_product3})
			{
				$$upgrade_row{is_from_product3}=1;
			}
			if ($$product_row{product_id}==$$upgrade_row{to_product})
			{
				$$upgrade_row{off}=1;
			}
			if (($$product_row{date} cmp $upgr_date)==-1 && (($$product_row{product_id}==$$upgrade_row{from_product1}) || ($$product_row{product_id}==$$upgrade_row{from_product3})))
			{
				$$upgrade_row{date_good}=1;
			}elsif ($$product_row{num_sum} && !$$product_row{num_sum2})
			{
				$$upgrade_row{date_good}=1;
			}
		}
		if (!$$upgrade_row{m_id})
		{
			foreach my $u_line (@b_upgrade)
			{
				if ($$u_line{product_id}==$$upgrade_row{to_product})
				{
					$$upgrade_row{m}=$$u_line{m};
					$$upgrade_row{m_id}=$$u_line{id};
				}
			}
		}
	}
	
	
	
	
	foreach my $upgrade_row (@upgrade_list)
	{
		if (!$$upgrade_row{off})
		{
			if (
				($$upgrade_row{from_product1}>0 && $$upgrade_row{from_product2}==0 && $$upgrade_row{from_product3}==0 && $$upgrade_row{is_from_product1})
				||
				($$upgrade_row{from_product1}>0 && $$upgrade_row{from_product2}==0 && $$upgrade_row{from_product3}>0 && ($$upgrade_row{is_from_product1} || $$upgrade_row{is_from_product3}))
				||
				($$upgrade_row{from_product1}>0 && $$upgrade_row{from_product2}>0 && $$upgrade_row{from_product3}==0 && ($$upgrade_row{is_from_product1} && $$upgrade_row{is_from_product2}))
				||
				($$upgrade_row{from_product1}>0 && $$upgrade_row{from_product2}>0 && $$upgrade_row{from_product3}>0 && (($$upgrade_row{is_from_product1} || $$upgrade_row{is_from_product3}) && $$upgrade_row{is_from_product2}))
			)
			{
				$$upgrade_row{on}=1;
			}
		}
	}

	
	my (%to_hash,@upgrade_show);
	
	
	foreach my $upgrade_row (@upgrade_list)
	{
		if ($$upgrade_row{on})
		{
			if ($$found_lavel{$$upgrade_row{m_id}}<$$upgrade_row{m})
			{
				if ($to_hash{$$upgrade_row{to_product}})
				{
					if ($$upgrade_row{num_sum}>0)
					{

						if (
							((
								!$to_hash{$$upgrade_row{to_product}}{date_good} 
								&& 
								$to_hash{$$upgrade_row{to_product}}{num_sum2}>0
							)
							?
							$to_hash{$$upgrade_row{to_product}}{num_sum2}
							:
							$to_hash{$$upgrade_row{to_product}}{num_sum})
								
							>
								
							((
								!$$upgrade_row{date_good} 
								&& 
								$$upgrade_row{num_sum2}>0
							)
							?
							$$upgrade_row{num_sum2}
							:
							$$upgrade_row{num_sum})
						)
						{
							$to_hash{$$upgrade_row{to_product}}=$upgrade_row;
						}
					}else
					{
						$to_hash{$$upgrade_row{to_product}}=$upgrade_row;
					}
				}else
				{
					$to_hash{$$upgrade_row{to_product}}=$upgrade_row;
				}
			}
		}
	}	
	
	
	
	foreach my $upgrade_key (keys(%to_hash))
	{
		
		if (!${$to_hash{$upgrade_key}}{date_good} && ${$to_hash{$upgrade_key}}{link2})
		{
			${$to_hash{$upgrade_key}}{is_sec_link}=1;
		}else
		{
			${$to_hash{$upgrade_key}}{is_sec_link}=0;
		}
		push @upgrade_show, $to_hash{$upgrade_key};
	}	
	
    	@upgrade_show = sort {$$a{m} <=> $$b{m}} (@upgrade_show);
    	
    	
    	
    	
    	
    	
    	
    	
    	
	
	foreach my $product_row (@product_list)
	{
		$sth = $dbh->prepare("select 	id,
						file_name,
						file_type,
						DATE_FORMAT(edit_time,'%Y-%m-%d') AS edit_time

					from	fr_product_files

					where	product_bild_id=?
					") or die $dbh->errstr."\n";
		$sth->execute($$product_row{product_bild_id}) or die $sth->errstr."\n";
		my @product_files;
		while($row=$sth->fetchrow_hashref(NAME_lc))
		{
			push @product_files, {%$row,"is_".$file_type{$$row{file_type}}=>1,is_reg_file=>($$row{file_name} eq 'Fontonizer.reg'?1:'')};
		}
		$$product_row{product_files}=((@product_files)?\@product_files:0);

	}
	
	my $keys=&ret_user_keys($$user_data{id});
	if ($#$keys==-1)
	{
		$keys=0;
	}
	
	&user_page_print("tmpl/$lang/show_product_list.tmpl",{keys=>$keys,%$add_hash,products=>\@product_list,upgrades=>(@upgrade_show?\@upgrade_show:'')});
#	&tmpl_print("tmpl/$lang/show_product_list.tmpl",{products=>\@product_list});
}



#sub get_zip_user
#{
#	&distr_to_user($$user_data{id},$query->param('zip_id'),$_DOWNLOAD_TYPE{ZIP},1);
#}



sub get_file_user
{
	&distr_to_user($$user_data{id},$query->param('file_id'),$_DOWNLOAD_TYPE{FILE},1);
}

#sub get_file_mail_user
#{
#	
#	&print_header;
#	&show_product({mail_sended=>&distr_to_user($$user_data{id},$query->param('file_email_id'),$_DOWNLOAD_TYPE{FILE_EMAIL},1)});
#}


sub get_mail_user
{
	&print_header;
	&show_product({mail_sended=>&distr_to_user($$user_data{id},0,$_DOWNLOAD_TYPE{EMAIL},1)});
#	&user_page_print("tmpl/$lang/main_content.tmpl",{mail_sended=>&distr_to_user($$user_data{id},$query->param('bild_id'),$_DOWNLOAD_TYPE{EMAIL},1)});
#	&tmpl_print("tmpl/$lang/show_main.tmpl",{languages=>&ret_languages_user,mail_sended=>&distr_to_user($$user_data{id},$query->param('bild_id'),$_DOWNLOAD_TYPE{EMAIL},1)});
}


#sub show_main
#{
#	&user_page_print("tmpl/$lang/main_content.tmpl",{});
#	&tmpl_print("tmpl/$lang/show_main.tmpl",{languages=>&ret_languages_user});
#}


sub set_user_language
{
	my $l_int=$$user_data{language_int};
	unless ($l_int)
	{
		$sth = $dbh->prepare("select fr_country.language_int from fr_country,fr_user where fr_user.country_id=fr_country.id AND fr_user.id=?");
		$sth->execute($$user_data{id}) or die $sth->errstr."\n";
		$row=$sth->fetchrow_hashref(NAME_lc);
		$l_int=$$row{language_int};
		$$user_data{language_int}=$l_int;
	}
	foreach my $lang (keys(%_LANGUAGE))
	{
		return lc($lang) if ($_LANGUAGE{$lang}==$l_int);
	}
	return 'en';
}

sub change_language
{
	if ($query->param('id')>0)
	{
		$sth = $dbh->prepare("update fr_user set language_int=? where id=?");
		$sth->execute($query->param('id'),$$user_data{id}) or die $sth->errstr."\n";
	}
	&move_to("?");
}



sub show_user_details
{
	$sth = $dbh->prepare("select * from fr_user where fr_user.id=?");
	$sth->execute($$user_data{id}) or die $sth->errstr."\n";
	$row=$sth->fetchrow_hashref(NAME_lc);
	&print_header;
	&user_page_print("tmpl/$lang/show_user_details.tmpl",$row);
#	&tmpl_print("tmpl/$lang/show_user_details.tmpl",$row);
}


sub change_details
{
	my %out_hash;
	$error_count=0;
	$is_show_ur=0;
        if ($query->param('email') eq '')
        {
	        $out_hash{is_email_error}=1;
        	$error_count++;
        }
        if (length($query->param('password1'))<$user_pas_length)
        {
	        $out_hash{is_password_error}=1;
	        $out_hash{is_password_length_error}=$user_pas_length;
        	$error_count++;
        }
        unless ($query->param('password1') eq $query->param('password2'))
        {
	        $out_hash{is_password_error}=1;
	        $out_hash{is_password_eq_error}=1;
        	$error_count++;
        }

        if (length($query->param('new_login'))<4)
        {
	        $out_hash{is_login_error}=1;
	        $out_hash{is_login_length_error}=1;
        	$error_count++;
        }
        if (length($query->param('new_login'))>10)
        {
	        $out_hash{is_login_error}=1;
	        $out_hash{is_login_length_error}=1;
        	$error_count++;
        }

        unless ($query->param('new_login')=~/^(([a-z]|[0-9]|_)+)$/igm)
        {
	        $out_hash{is_login_error}=1;
	        $out_hash{is_login_char_error}=1;
        	$error_count++;
        }


		$sth = $dbh->prepare("select id,login from fr_user where UPPER(login)=UPPER(?)") or die $dbh->errstr."\n";
		$sth->execute($query->param('new_login')) or die $sth->errstr."\n";
		$row_login=$sth->fetchrow_hashref(NAME_lc);
		my $old_login;
		if ($row_login)
		{
			if ($$row_login{id}!=$$user_data{id})
			{
		        	$out_hash{is_login_error}=1;
		        	$out_hash{is_login_exist_error}=1;
        			$error_count++;
			}else
			{
				$old_login=$$row_login{login};
			}
		}elsif ($query->param('new_login')=~/^user\d+$/igm)
		{
		        $out_hash{is_login_error}=1;
		        $out_hash{is_login_exist_error}=1;
        		$error_count++;
		}





        if ($error_count)
        {
		$sth = $dbh->prepare("select * from fr_user where fr_user.id=?");
		$sth->execute($$user_data{id}) or die $sth->errstr."\n";
		$row=$sth->fetchrow_hashref(NAME_lc);
		$$row{password}='';
		$$row{email}=$query->param('email');
		$$row{phone}=$query->param('phone');
		$$row{icq}=$query->param('icq');
		$$row{error_count}=$error_count;
		$$row{login}=$query->param('new_login');
		
		&print_header;
		&user_page_print("tmpl/$lang/show_user_details.tmpl",{%$row,%out_hash});

        }else
        {

		$sth = $dbh->prepare("update fr_user set 	password=?,
								login=?,
								email=?,
								phone=?,
								icq=?,
								mail_send=? 

						where 		fr_user.id=?");

		$sth->execute(	$query->param('password1'),
				$query->param('new_login'),
				$query->param('email'),
				$query->param('phone'),
				$query->param('icq'),
				($query->param('mail_send') eq 'on')?1:0,
				$$user_data{id}
		) or die $sth->errstr."\n";
		
		
		&bitrix_update_login($$user_data{login},$query->param('new_login'));
		
		
		&bitrix_update($$user_data{id},'id');
		&move_to("?");
        }
}