Location: PHPKode > projects > Opensheet > opensheet/functions/base.functions.php
<?php
#ini_set('display_errors',1);
#error_reporting(E_ALL);

function safe($value){ 
   return mysql_real_escape_string($value); 
}



#Какие типы проектов бывают
$proj_types = array('project','tender','office','off_hours');
$project_types = array('project','tender','office','off_hours');
$month_names = array("0"=>"Декабрь","1" => "Январь","2"=>"Февраль","3"=>"Март","4"=>"Апрель","5"=>"Май","6"=>"Июнь","7"=>"Июль","8"=>"Август","9"=>"Сентябрь","10"=>"Октябрь","11"=>"Ноябрь","12"=>"Декабрь","13" => "Январь");

require ('db_connect.php');

$lang['ru_RU'] = array (
"lang" => "Язык",
"timesheet" => "Карточка учета рабочего времени",
"date" => "Дата",
"go" => "Ввести",
"prev_month" => "Предыдущий месяц",
"next_month" => "Следующий Месяц",
"br" => "Среднесписочная численность",
"ftee" => "Коэффициен загрузки",
"project" => "Проекты",
"tender" => "Тендеры",
"office" => "Офисная работа",
"off_hours" => "Часы отсутствия",
"overtime" => "Сверхурочная работа",
"index" => "Индекс",
"sign" => "Подпись",
"summ" => "всего",
"reports" => "Отчеты",
"exit" => "Выход",
"setup" => "Настройки",
"company_name" => "Рамболь Москва"


);






function projects_switch() {

echo "<form action='' method='POST'>";
echo '<select name="projects_switch">';
for ( $i=0; $i < count($proj_types); $i++){
	echo "<option value=$proj_types[$i]>'.$proj_types[$i].'</option>";
}
echo "</select>";
echo '<input type="submit" value="GO" >';
echo "</form>";
}



#Получаем спасик пользователей и сортируем
function users_list()
{
db_connect();
$query = " SELECT user_name FROM users";
$res = mysql_query($query);
while($row = mysql_fetch_assoc($res)) {$users_list[]=$row['user_name'];} 
sort($users_list);    
return $users_list;
}

/**



*/


function users_list_active()
{
db_connect();

$query = " SELECT real_name  FROM users WHERE `status` = 'Y' ORDER BY `real_name`  DESC";

$res = mysql_query($query);
$i=0;
while($row = mysql_fetch_assoc($res)) {

$users_list[$i] = array(real_name=>$row['real_name']);	

$i++;
} 

sort($users_list); 
$z=0;
foreach ($users_list as  $key) {  
	foreach ($key as  $value) { 
$query1 = " SELECT user_name,user_index,real_name  FROM users WHERE `status` = 'Y' and `real_name` = '$value' ORDER BY `real_name`  DESC";

$res1 = mysql_query($query1);

while($row = mysql_fetch_assoc($res1)) {

$users_list2[$z] = array(user_index=>$row['user_index'],user_name=>$row['user_name'],real_name=>$row['real_name']);	

$z++;
} 
}
}

return $users_list2;
}


function proj_index_to_name($proj_index)
{
#global $proj_name ;
db_connect();
$query = "SELECT `proj_name`  FROM  `projects`  WHERE  `proj_index` LIKE  '$proj_index'";
$res = mysql_query($query);
$ar_row = mysql_fetch_row($res);
$proj_name = $ar_row[0];
return $proj_name;


}




function proj_name_to_index($proj_name)
{

db_connect();
$query = "SELECT `proj_index`  FROM  `projects`  WHERE  `proj_name` LIKE  '$proj_name'";
$res = mysql_query($query);
$ar_row = mysql_fetch_row($res);
$proj_index = $ar_row[0];


return $proj_index;

}


function user_index_to_real_name($user_index)
{


db_connect();
$query = "SELECT `real_name`  FROM  `users`  WHERE  `user_index` LIKE  '$user_index'";
$res = mysql_query($query);
$aru_row = mysql_fetch_row($res);
$user_name = $aru_row[0];
return $user_name;
}


function user_index_to_name($user_index)
{

db_connect();
$query = "SELECT `user_name`  FROM  `users`  WHERE  `user_index` LIKE  '$user_index'";
$res = mysql_query($query);
$aru_row = mysql_fetch_row($res);
$user_name = $aru_row[0];
return $user_name;



}

function user_name_to_index($user_name)
{
# global $user_index;

db_connect();
$query = "SELECT `user_index`  FROM  `users`  WHERE  `user_name` LIKE  '$user_name'";
$res = mysql_query($query);
$aru_row = mysql_fetch_row($res);
$user_index = $aru_row[0];
return $user_index;


}

function proj_hours_sum($user_index,$proj_index,$year,$month)
{
# global $user_index;

db_connect();
$query = "SELECT SUM(hours) FROM `timesteps` WHERE `user_index` = '$user_index' AND `proj_index` = '$proj_index' AND MONTH( DATE ) = $month AND YEAR(DATE) = $year";
$res = mysql_query($query);
$aru_row = mysql_fetch_row($res);
$proj_hours_sum = $aru_row[0];
return $proj_hours_sum;


}

#преобразует тип проекта в название
function proj_type_to_name($proj_type){
	$proj_type_to_name = array('Проект','Тендер','Офисная Работа','Часы отсутствия');
	if ($proj_type == 'project') {
		return $proj_type_to_name['0'];
	} elseif ($proj_type == 'tender') {
		return $proj_type_to_name['1'];
	} elseif ($proj_type == 'office') {
		return $proj_type_to_name['2'];
	} elseif ($proj_type == 'off_hours') {
		return $proj_type_to_name['3'];
	}
}




function proj_list($user_index, $proj_type, $status,$year,$month)
{
#db_connect();
$stat="and status='Y'";
if ($status == 'Y'){
$query = "SELECT tableb.proj_index FROM (select proj_index from projects where proj_type='".$proj_type."')tablea join (select proj_index from users_projects where user_index='".$user_index."' ".$stat." )tableb on tablea.proj_index=tableb.proj_index";
}else{
	$query = "SELECT distinct tableb.proj_index FROM (select proj_index from projects where proj_type='".$proj_type."')tablea join (select proj_index from timesteps where user_index='".$user_index."' and month (date)='".$month."' and year(date)='".$year."' )tableb on tablea.proj_index=tableb.proj_index ";
}

#за конкретный месяц: select distinct project_id from timesteps where user_id='11' and month (date)='7' and year(date)='2009' and project_id=any(select id from projects where proj_type='office');

#echo $query;
#echo "<br>";
$res = mysql_query($query);
while($row = mysql_fetch_assoc($res)) {$proj_list[]=$row['proj_index'];} 
sort($proj_list);
return $proj_list;


}





function getasks_user($user_index,$year,$month) {
		db_connect();
		if 	($user_index!=''){	
	
			if (($month==date(n))and ($year==date(Y)) ){
 				$query= "select usertask.task_index, task_name, parent, usertask.status, billable from (select task_index, parent,status from users_tasks where status='Y' and user_index='".$user_index."' ) as usertask join (select task_name, task_index, status, billable from tasks where  (month(start)<=".$month." and year(start)<=".$year.") and status='Y') as taskprop on usertask.task_index=taskprop.task_index order by task_index ASC";
	
				}
			else {
				#$query="select distinct task_index,parent from tasksteps where user_index='".$user_index."' and (hours!='' and hours!=0) order by task_index ASC";}
		 	$query= "select usertask.task_index, task_name, parent, usertask.status, billable from (select task_index, parent,status from users_tasks where status='Y' and user_index='".$user_index."' ) as usertask join (select task_name, task_index, status, billable from tasks where  (month(start)<=".$month." and year(start)<=".$year.") and status='Y') as taskprop on usertask.task_index=taskprop.task_index join (select distinct task_index from tasksteps where user_index='sbs_index' and(year(date)=".$year." and month(date)=".$month.")and (hours!='' and hours!=0))as task_not_null on taskprop.task_index=task_not_null.task_index order by task_index ASC";
					
				}
		}	
		else {
			$query="select distinct taskprop.task_index, task_name, taskprop.parent, status, billable from (select task_index, parent from users_tasks) as usertask right join (select task_name, task_index, status, billable, parent from tasks where  (month(start)<=".$month." and year(start)<=".$year.")) as taskprop on usertask.task_index=taskprop.task_index order by task_index ASC";
		}

		$res=mysql_query($query);

		while ($row=mysql_fetch_assoc($res)){
			if (key_exists($row['parent'],$tasks)!=1){$i=0;} else {$i++;}
				$tasks[$row['parent']][$i]=array(task_index => $row['task_index'], task_name =>$row['task_name'],status => $row['status'], parent => $row['parent']);

		}


		return $tasks;
	}


	
	
	
	

# отдать таски проекта
function getproj_tasks($proj_index){
db_connect();	
$sql =  "SELECT task_index, task_name,status,start FROM  `tasks` WHERE `parent` =  '$proj_index'";
#echo $sql;
$res=mysql_query($sql);
$i=0;
while ($row=mysql_fetch_assoc($res)){
$tasks[$i]['task_index']=$row['task_index'];
$tasks[$i]['task_name']=$row['task_name'];
$tasks[$i]['status']=$row['status'];
$tasks[$i]['start']=$row['start'];
$i++;	
}
return $tasks;
}


function gettask_users($task_index){
db_connect();	
$sql =  "select user_index from users_tasks where `task_index` = '$task_index' and `status` = 'Y'";
#echo $sql;
$res=mysql_query($sql);
$i=0;
while ($row=mysql_fetch_assoc($res)){
$task_users[$row['user_index']]=$row['user_index'];

$i++;	
}

#print_r($task_users);
return $task_users;
}

function getproj_users($proj_index){
db_connect();	
$sql =  "select user_index from users_projects where proj_index = '$proj_index' and `status` = 'Y'";
#echo $sql;
$res=mysql_query($sql);
$i=0;
while ($row=mysql_fetch_assoc($res)){
$proj_users[$row['user_index']]=$row['user_index'];

$i++;	
}

return $proj_users;
}

function getask_parent($task_index){
db_connect();	
$sql =  "select parent from tasks where task_index = '$task_index'";
#echo $sql;
$res=mysql_query($sql);
while ($row=mysql_fetch_assoc($res)){
$proj_index=$row['parent'];

$i++;	
}

return $proj_index;
}







function sum_time_per_proj($user_index, $month, $year)
{
db_connect();
$query ="SELECT DISTINCT tablea.hours as hours, tableb.proj_index as proj_index FROM ( SELECT sum(hours) AS hours, proj_index FROM timesteps WHERE user_index ='".$user_index."' AND MONTH( DATE ) =".$month." AND YEAR( DATE ) =".$year." GROUP BY proj_index )tablea JOIN ( SELECT proj_name, proj_index FROM projects WHERE billable = 'Y')tableb ON tablea.proj_index = tableb.proj_index";
$sum_per_proj=array();
$res = mysql_query($query);
$i=0;
while($row = mysql_fetch_assoc($res)) {
$result_hours[$i]=$row[hours];
$result_proj_index[$i]=$row[proj_index];
$i++;
            }
$sum_per_proj=array_combine($result_proj_index,$result_hours);
#print_r ($sum_per_proj);
return $sum_per_proj;
}

function sum_all_time_per_proj($user_index, $month, $year)
{
db_connect();
$query ="SELECT DISTINCT tablea.hours as hours, tableb.proj_index as proj_index FROM ( SELECT sum(hours) AS hours, proj_index FROM timesteps WHERE user_index ='".$user_index."' AND MONTH( DATE ) =".$month." AND YEAR( DATE ) =".$year." GROUP BY proj_index )tablea JOIN ( SELECT proj_name, proj_index FROM projects)tableb ON tablea.proj_index = tableb.proj_index";
$sum_per_proj=array();
$res = mysql_query($query);
$i=0;
while($row = mysql_fetch_assoc($res)) {
$result_hours[$i]=$row[hours];
$result_proj_index[$i]=$row[proj_index];
$i++;
            }
$sum_per_proj=array_combine($result_proj_index,$result_hours);
#print_r ($sum_per_proj);
return $sum_per_proj;
}



#Cумма оплачеваемых часов за месяц для пользователя
 function bill_sum_hours($user_index,$month,$year)
{
db_connect();
$query = "SELECT DISTINCT sum(tablea.hours) as hours  FROM (SELECT sum( hours ) AS hours, proj_index FROM timesteps WHERE user_index ='".$user_index."' AND MONTH( DATE ) =".$month." AND YEAR (DATE)=".$year." GROUP BY proj_index)tablea right JOIN (SELECT proj_name, proj_index FROM projects WHERE billable ='Y') tableb ON tablea.proj_index = tableb.proj_index";
$res = mysql_query($query);
while($row = mysql_fetch_assoc($res)) {$proj_bill_sum_arr[]=$row['hours'];} 
$proj_bill_sum=$proj_bill_sum_arr[0];
return $proj_bill_sum;
}

#сумма всех оплачиваемых часов по организации
function bill_all_sum_hours($month,$year)
{
db_connect();
$query = "SELECT DISTINCT sum(tablea.hours) as hours  FROM (SELECT sum( hours ) AS hours, proj_index FROM timesteps WHERE MONTH( DATE ) =".$month." AND YEAR (DATE)=".$year." GROUP BY proj_index)tablea right JOIN (SELECT proj_name, proj_index FROM projects WHERE billable ='Y') tableb ON tablea.proj_index = tableb.proj_index";
$res = mysql_query($query);
while($row = mysql_fetch_assoc($res)) {$proj_bill_sum_arr[]=$row['hours'];} 
$bill_all_sum=$proj_bill_sum_arr[0];
return $bill_all_sum;
}

#Billing ratio per user 
 function bill_ratio($user_index,$year,$month)
{
db_connect();
$query = "SELECT DISTINCT sum(tablea.hours) as hours  FROM (SELECT sum( hours ) AS hours, proj_index FROM timesteps WHERE user_index ='".$user_index."' AND MONTH( DATE ) =".$month." AND YEAR (DATE)=".$year." GROUP BY proj_index)tablea right JOIN (SELECT proj_name, proj_index FROM projects WHERE proj_type='project' or proj_type='tender') tableb ON tablea.proj_index = tableb.proj_index";
$query1="SELECT DISTINCT sum(tablea.hours) as hours  FROM (SELECT sum( hours ) AS hours, proj_index FROM timesteps WHERE user_index ='".$user_index."' AND MONTH( DATE ) =".$month." AND YEAR (DATE)=".$year." GROUP BY proj_index)tablea right JOIN (SELECT proj_name, proj_index FROM projects) tableb ON tablea.proj_index = tableb.proj_index";

$res = mysql_query($query);
$res1= mysql_query($query1);
while($row = mysql_fetch_assoc($res)) {$sum_bill[]=$row['hours'];} 
while ($row1=mysql_fetch_assoc($res1)) {$sum_all[]=$row1['hours'];}
$bill_ratio=$sum_bill[0]/$sum_all[0];
return $bill_ratio;
}

function summ_bill_ratio($month,$year)

{
$query = "SELECT DISTINCT sum(tablea.hours) as hours  FROM (SELECT sum( hours ) AS hours, proj_index FROM timesteps WHERE MONTH( DATE ) =".$month." AND YEAR (DATE)=".$year." GROUP BY proj_index)tablea right JOIN (SELECT proj_name, proj_index FROM projects WHERE type='project' or type='tender') tableb ON tablea.proj_index = tableb.proj_index";
$query1="SELECT DISTINCT sum(tablea.hours) as hours  FROM (SELECT sum( hours ) AS hours, proj_index FROM timesteps WHERE MONTH( DATE ) =".$month." AND YEAR (DATE)=".$year." GROUP BY proj_index)tablea right JOIN (SELECT proj_name, proj_index FROM projects) tableb ON tablea.proj_index = tableb.proj_index";
$res = mysql_query($query);
$res1= mysql_query($query1);
while($row = mysql_fetch_assoc($res)) {$sum_bill[]=$row['hours'];} 
while ($row1=mysql_fetch_assoc($res1)) {$sum_all[]=$row1['hours'];}
$summ_bill_ratio=$sum_bill[0]/$sum_all[0];
return $summ_bill_ratio;
}


function proj_type_check($user_index, $proj_type)# проверка есть ли такой тип проектов у пользователя
{
db_connect();
$query = "SELECT tableb.proj_index FROM (select proj_index, proj_type from projects where proj_type='".$proj_type."')tablea join (select proj_index from users_projects where user_index='".$user_index."')tableb on tablea.proj_index=tableb.proj_index";
#echo $query;
$res = mysql_query($query);
while($row = mysql_fetch_assoc($res)) {$proj_check[]=$row['proj_index'];} 
if (count($proj_check)!=0) {
        $proj_type_check="Y";
                }
    else 
        {
        $proj_type_check="N";
        }
 #  echo "<pre>";
 #          print_r ($proj_type_check);
 #         echo "</pre>";
return $proj_type_check;
}

#получить список проектов по типу
function proj_list_types($proj_type)
{
db_connect();
$query = "SELECT proj_name  FROM  `projects`  WHERE  `proj_type` =  '$proj_type'";
$res = mysql_query($query);
while($row = mysql_fetch_assoc($res)) {$proj_list_types[]=$row['proj_name'];} 
#$proj_list_types = mysql_fetch_arroy($res);

sort($proj_list_types);

return $proj_list_types;
}


function get_proj_notes($user_index,$proj_index,$year,$month,$day){
db_connect();
$query = "SELECT note FROM  `timesteps` WHERE  `user_index` =  '$user_index' AND  `proj_index` =  '$proj_index' AND  `date` = '$year-$month-$day'";
#echo "<pre>";
$res = mysql_query($query);
$row = mysql_fetch_row($res);

return ($row[0]);
	
}
function get_notes ($user_index, $year, $month){
db_connect();
$query="select proj_index,day(date) as day, hours,note from timesteps where user_index='".$user_index."' and (hours!='' and hours!='0')and (month(date)=".$month." and year(date)=".$year.") ORDER BY day(date) ASC";
$res=mysql_query($query);
$query1="select task_name, usertasks.task_index as task_index, day ,parent, hours, note from (select task_name,task_index, parent from tasks) as taskprop   join (select task_index, hours,note, day(date) as day from tasksteps where user_index='".$user_index."' and (hours!='' and hours !=0) and (month(date)=".$month." and year(date)=".$year.") order by day) as usertasks on taskprop.task_index=usertasks.task_index ORDER BY `usertasks`.`day` ASC";
$res1=mysql_query($query1);

while ($row1=mysql_fetch_assoc($res1)){
$tasks_notes[$row1['day']][$row1['parent']][$row1['task_index']]=array(task_index => $row1['task_index'], task_name =>$row1['task_name'],status => $row1['status'],hours => $row1['hours'],note =>$row1['note'] );

}

while ($row=mysql_fetch_assoc($res)){
$proj_notes[$row['day']][$row['proj_index']]=array(hours => $row['hours'],note =>$row['note'] );



}

#$summ = array_merge_recursive($proj_notes,$tasks_notes);

#print_r($proj_notes);


#echo "<pre>";
$days=array_keys($proj_notes); #print_r($days);

foreach ($days as $key) {
	$projects_indexs=array_keys($proj_notes[$key]);#print_r($projects_indexs);
	$tasks_parents=array_keys($tasks_notes[$key]);#print_r($tasks_notes[$key]);
		#print_r($key);
	
	$task_max = count($tasks_parents);
	$proj_max = count($projects_indexs);
	for ($i=0;$i<$task_max;$i++) {
		for ($z=0;$z<$proj_max;$z++) {
			if ($projects_indexs[$z] == $tasks_parents[$i]) {
			
		$proj_notes[$key][$projects_indexs[$z]]=$tasks_notes[$key][$tasks_parents[$i]]	;
		}
	}
}
	}

#print_r($tasks_parents_indexs);
return $proj_notes;	
}


			function set_lang($lang){
				
				if($lang == 'russian'){
				
				putenv("LANG=ru_RU");
				setlocale (LC_ALL, "ru_RU.UTF-8");	
				$domain = 'ru';
				bindtextdomain ($domain, "./locale");
				textdomain ($domain);
				bind_textdomain_codeset($domain, 'UTF-8');	
		
					
				}elseif($lang == 'english'){
				putenv("LANG=en_EN");
				setlocale (LC_ALL, "en_EN.UTF-8");	
				$domain = 'en';
				bindtextdomain ($domain, "./locale");
				textdomain ($domain);
				bind_textdomain_codeset($domain, 'UTF-8');
				}
				
			}

			#remove count from ad answer
			function rCountRemover($array) {
 			 foreach($array as $key=>$value) {
  			  if($key === "count") 
    			  unset($array[$key]);
   					 elseif(is_array($value))
    			  $array[$key] = rCountRemover($array[$key]);
  					}
 				 return $array;
			} 
			
	

?>
Web-based Email Marketing Software for sending email newsletters
Return current item: Opensheet