# /packages/intranet-core/tcl/intranet-user-procs.tcl
#
# Copyright (C) 1998-2004 various parties
# The code is based on ArsDigita ACS 3.4
#
# This program is free software. You can redistribute it
# and/or modify it under the terms of the GNU General
# Public License as published by the Free Software Foundation;
# either version 2 of the License, or (at your option)
# any later version. This program is distributed in the
# hope that it will be useful, but WITHOUT ANY WARRANTY;
# without even the implied warranty of MERCHANTABILITY or
# FITNESS FOR A PARTICULAR PURPOSE.
# See the GNU General Public License for more details.

# @author various@arsdigita.com
# @author frank.bergmann@project-open.com


ad_proc -public im_user_permissions { current_user_id user_id view_var read_var write_var admin_var } {
    Fill the "by-reference" variables read, write and admin
    with the permissions of $current_user_id on $user_id
} {
    upvar $view_var view
    upvar $read_var read
    upvar $write_var write
    upvar $admin_var admin

    set view 0
    set read 0
    set write 0
    set admin 0

    if {"" == $user_id} { return }
    if {"" == $current_user_id} { return }

    # Admins and creators can do everything
    set user_is_admin_p [im_is_user_site_wide_or_intranet_admin $current_user_id]
    set creation_user_id [util_memoize "db_string creator {select creation_user from acs_objects where object_id = $user_id} -default 0"]
	set hr_p 0 
	if { [im_profile::member_p -profile "HR Managers" -user_id $current_user_id] && [im_user_is_employee_p $user_id]} {
		set hr_p 1
	}
    if {$user_is_admin_p || $current_user_id == $creation_user_id || $hr_p} {
	set view 1
	set read 1
	set write 1
	set admin 1
	return
    }

    # Get the list of profiles of user_id (the one to be managed)
    # together with the information if current_user_id can read/write
    # it.
    # m.group_id are all the groups to whom user_id belongs
    set profile_perm_sql "
		select
			m.group_id,
			im_object_permission_p(m.group_id, :current_user_id, 'view') as view_p,
			im_object_permission_p(m.group_id, :current_user_id, 'read') as read_p,
			im_object_permission_p(m.group_id, :current_user_id, 'write') as write_p,
			im_object_permission_p(m.group_id, :current_user_id, 'admin') as admin_p
		from
			acs_objects o,
			group_distinct_member_map m
		where
			m.member_id = :user_id
		     	and m.group_id = o.object_id
			and o.object_type = 'im_profile'
    "
    set first_loop 1
    db_foreach profile_perm_check $profile_perm_sql {
	ns_log Notice "im_user_permissions: $group_id: view=$view_p read=$read_p write=$write_p admin=$admin_p"
	if {$first_loop} {
	    # set the variables to 1 if current_user_id is member of atleast
	    # one group. Otherwise, an unpriviliged user could read the data
	    # of another unpriv user
	    set view 1
	    set read 1
	    set write 1
	    set admin 1
	}

	if {[string equal f $view_p]} { set view 0 }
	if {[string equal f $read_p]} { set read 0 }
	if {[string equal f $write_p]} { set write 0 }
	if {[string equal f $admin_p]} { set admin 0 }
	set first_loop 0
    }

    # Myself - I can read and write its data
    if { $user_id == $current_user_id } { 
		set read 1
		set write 1
		set admin 0
    }


    if {$admin} {
		set read 1
		set write 1
    }
    if {$read} { set view 1 }

    ns_log Notice "im_user_permissions: cur=$current_user_id, user=$user_id, view=$view, read=$read, write=$write, admin=$admin"

}


ad_proc -public user_permissions { current_user_id user_id view_var read_var write_var admin_var } {
    Helper being called when calling dynamic permissions
    for objects (im_biz_objects...).<br>
    This procedure is identical to im_user_permissions.
} {
    upvar $view_var view
    upvar $read_var read
    upvar $write_var write
    upvar $admin_var admin

    im_user_permissions $current_user_id $user_id view read write admin
}



ad_proc -public im_user_base_info_component { 
    -user_id:required
    { -return_url ""}
} {
    Returns a formatted piece of HTML showing the user's name and email
} {
    if {"" == $return_url} { set return_url [im_url_with_query] }
    set params [list \
		    [list user_id $user_id] \
		    [list return_url [im_url_with_query]] \
    ]

    set result [ad_parse_template -params $params "/packages/intranet-core/www/users/base-info-component"]
    return [string trim $result]
}


ad_proc -public im_sysadmin_user_default { } {
    Determines the default system Administrator account
    Just takes the lowest user_id from the members of
    the Admin group...
} {

    set user_id [util_memoize "db_string default_admin \"
	select
		min(user_id) as user_id
	from
		acs_rels ar,
		membership_rels mr,
		users u
	where
		ar.rel_id = mr.rel_id
		and u.user_id = ar.object_id_two
		and ar.object_id_one = [im_admin_group_id]
		and mr.member_state = 'approved'
    \" -default 0" 60]

    return $user_id
}



ad_proc -public im_user_options { 
    {-include_empty_p 1} 
    {-include_empty_name ""}
    {-group_id 0}
    {-group_name ""}
    {-biz_object_id ""}
} {
    Returns the options for a select box.
} {
    if {"" != $group_name} {
	set group_id [util_memoize "db_string group \"select group_id from groups where group_name = '$group_name'\" -default 0"]
    }

    set group_select_sql ""
    set biz_object_select_sql ""
    if {0 != $group_id && "" != $group_id} { 
	set group_select_sql "and user_id in (select member_id from group_distinct_member_map where group_id = :group_id)" 
    }
    if {0 != $biz_object_id && "" != $biz_object_id} { 
	set biz_object_select_sql "and user_id in (select object_id_two from acs_rels where object_id_one = :biz_object_id)" 
    }

    set options [db_list_of_lists provider_options "
		select
			im_name_from_user_id(u.user_id) as name, 
			u.user_id
		from
			cc_users u
		where
			1=1
			$group_select_sql
			$biz_object_select_sql
		order by name
    "]
    if {$include_empty_p} { set options [linsert $options 0 [list $include_empty_name "" ]] }
    return $options
}

ad_proc -public im_employee_options { {include_empty 1} } {
    Cost provider options
} {
    set options [db_list_of_lists provider_options "
	select	im_name_from_user_id(user_id) as name, 
		user_id
	from	im_employees_active
	order by name
    "]
    if {$include_empty} { set options [linsert $options 0 { "" "" }] }
    return $options
}

ad_proc -public im_project_manager_options { 
    {-include_empty 1} 
    {-current_pm_id 0}
} {
    Cost provider options
} {
	set current_user_id [ad_get_user_id]
   	set is_mem [db_string is_mem "
		select count(*) 
		from group_distinct_member_map 
		where member_id= :current_user_id and group_id=461
	"]
	
	if {$is_mem} { set company_id_tt [db_string company_id_tt "
		select object_id_one
		from acs_rels r
		where object_id_two = :current_user_id 
		and rel_type = 'im_company_employee_rel'
		"] } \
	else { set company_id_tt 8720 }
	
	set options [db_list_of_lists provider_options "
	select * from (
		select	im_name_from_user_id(r.object_id_two) as name, r.object_id_two
		from 	acs_rels r 
		where 	r.object_id_one = $company_id_tt 
	)t
	order by name
    "]
    if {$include_empty} { set options [linsert $options 0 { "" "" }] }
    return $options
}

ad_proc -public im_nus_co_options { 
    {-include_empty 1} 
    {-current_pm_id 0}
} {
    NUS member list
} {
	set current_user_id [ad_get_user_id]
   	set is_mem [db_string is_mem "
		select count(*) 
		from group_distinct_member_map 
		where member_id= :current_user_id and group_id=461
	"]
	
	if {$is_mem} { set company_id_nus [db_string company_id_nus "
		select object_id_one
		from acs_rels r
		where object_id_two = :current_user_id 
		and rel_type = 'im_company_employee_rel'
		"] } \
	else { set company_id_nus 36804 }
	
	
	set limit_to_group_id_sql "
	and exists (select 1 
		from 
			group_member_map map2,
		        membership_rels mr,
			groups ug
		where 
			map2.group_id = ug.group_id
			and map2.rel_id = mr.rel_id
			and mr.member_state = 'approved'
			and map2.member_id = u.user_id 
			and map2.group_id = [im_customer_group_id]
		)
	"
	
	set dont_allow_sql "
	and not exists (
		select 1 
		from 
			group_member_map map2, 
			membership_rels mr,
			groups ug
		where 
			map2.group_id = ug.group_id
			and map2.rel_id = mr.rel_id
			and mr.member_state = 'approved'
			and map2.member_id = u.user_id 
			and map2.group_id = [im_customer_group_id]
		)
	"
	
	set options [db_list_of_lists provider_options "
	select
		im_name_from_user_id(u.user_id), 
		u.user_id as party_id,
		im_email_from_user_id(u.user_id) as email,
		im_name_from_user_id(u.user_id) as name,
		im_category_from_id(c.category_id) as member_role,
		c.category_gif as role_gif,
		c.category_description as role_description
	from
		users u,
		acs_rels rels
		LEFT OUTER JOIN im_biz_object_members bo_rels ON (rels.rel_id = bo_rels.rel_id)
		LEFT OUTER JOIN im_categories c ON (c.category_id = bo_rels.object_role_id),
		group_member_map m,
		membership_rels mr
	where
		rels.object_id_one = $company_id_nus
		and rels.object_id_two = u.user_id
		and mr.member_state = 'approved'
		and u.user_id = m.member_id
		and mr.member_state = 'approved'
		and m.group_id = acs__magic_object_id('registered_users'::character varying)
		and m.rel_id = mr.rel_id
		and m.container_id = m.group_id
		and m.rel_type = 'membership_rel'

		$limit_to_group_id_sql 
		
	order by lower(im_name_from_user_id(u.user_id))
    "]
    if {$include_empty} { set options [linsert $options 0 { "" "" }] }
    return $options
}
 

ad_proc im_user_select { 
    {-include_empty_p 0} 
    {-add_html 0}
    {-include_empty_name "All"}
    {-group_id 0 }
    select_name 
    { default "" } 
} {
    Returns an html select box named $select_name and defaulted to 
    $default with a list of all the available project_leads in 
    the system
} {

    if {0 == $group_id} { set group_id [im_employee_group_id] }
    # Check if somebody tries to fiddle with SQL
    foreach id $group_id {
		if {![string is integer $id]} {
		    ad_return_complaint 1 "Please notify Frank"
		    ad_script_abort
		}
    }

    set user_options [im_profile::user_options -profile_ids $group_id]
    if {$include_empty_p} { set user_options [linsert $user_options 0 [list $include_empty_name ""]] }
    if { $add_html != 0 } { 
    	return [im_options_to_select_box $select_name $user_options $default $add_html] 
	} else { 
		return [im_options_to_select_box $select_name $user_options $default] 
	}
}

ad_proc im_employee_select_multiple { select_name { defaults "" } { size "6"} {multiple ""}} {
    set bind_vars [ns_set create]
    set employee_group_id [im_employee_group_id]
    set sql "
select
	u.user_id,
	im_name_from_user_id(u.user_id) as employee_name
from
	registered_users u,
	group_distinct_member_map gm
where
	u.user_id = gm.member_id
	and gm.group_id = $employee_group_id
order by lower(im_name_from_user_id(u.user_id))
"
    return [im_selection_to_list_box -translate_p "0" $bind_vars category_select $sql $select_name $defaults $size $multiple]
}    

ad_proc im_filing_select_multiple { select_name { defaults "" } { size "6"} {multiple ""}} {
    set bind_vars [ns_set create]
    set filing_group_id [im_filing_group_id]
    set sql "
select
	u.user_id,
	im_name_from_user_id(u.user_id) as employee_name
from
	registered_users u,
	group_distinct_member_map gm
where
	u.user_id = gm.member_id
	and gm.group_id = $filing_group_id
order by lower(im_name_from_user_id(u.user_id))
"
    return [im_selection_to_list_box -translate_p "0" $bind_vars category_select $sql $select_name $defaults $size $multiple]
}     

# ------------------------------------------
# Created 20-1-2015
# Nihit Malhotra
ad_proc im_trainee_training_complete_add_notification { } {
	set trainee_list [db_list get_trainee_list "select object_id_two from acs_rels where object_id_one=241834 and rel_type='membership_rel' "]
	set test ""
	set i 0
	set trainee_list_final [db_list get_active_list "select user_id from im_employees_active where user_id in ([join $trainee_list ","])"]
	set current_date_int [clock scan [db_string get_date "select to_char(now(), 'YYYY-MM-DD')"]]
	foreach trainee_id $trainee_list_final {
		set date_list [employee_join_terminate_date -user_id $trainee_id]
		set joining_date [lindex $date_list 0]
		set join_date_int [clock scan $joining_date -format "%Y-%m-%d"]
		set reminder_date_int [clock add [clock scan $joining_date -format "%Y-%m-%d"] 6 months ]
		set months_3_reminder_date_int [clock add [clock scan $joining_date -format "%Y-%m-%d"] 3 months ]
		if {$reminder_date_int >= $current_date_int } {
			set reminder_date [clock format $reminder_date_int -format "%Y-%m-%d"]
			set sent ""
			set deleted ""
			set already_listed [db_0or1row chk_data_noti "select object_id, sent, deleted, mail_id from im_notification_list where object_id=:trainee_id and type='training_complete' and date = :reminder_date"]
			if {!$already_listed } {
				db_dml insert_notify "insert into im_notification_list(object_id, type, date) values(:trainee_id, 'training_complete' , :reminder_date)"
			}
		}
		if {$months_3_reminder_date_int >= $current_date_int || $join_date_int > [clock scan "2014-11-01" -format "%Y-%m-%d"]} {
			incr i
			set reminder_date [clock format $months_3_reminder_date_int -format "%Y-%m-%d"]
			set sent ""
			set deleted ""
			set already_listed [db_0or1row chk_data_noti "select object_id, sent, deleted, mail_id from im_notification_list where object_id=:trainee_id and type='training_complete' and date = :reminder_date"]
			if {!$already_listed } {				
				db_dml insert_notify "insert into im_notification_list(object_id, type, user_id, date) values(:trainee_id, 'training_complete' , 1 , :reminder_date)"
			}
			append test "$i [im_name_from_user_id $trainee_id] $reminder_date \n"
		}
	}
	return $test
}

# ------------------------------------------


# ---------------------------s.sehra@xlpat.com-------------------


ad_proc im_filing_but_not_client_select_multiple { select_name { defaults "" } { size "6"} {multiple ""}} {
    set bind_vars [ns_set create]
    
    set sql "
select A.user_id, A.employee_name from (select
	u.user_id,
	im_name_from_user_id(u.user_id) as employee_name
from
	registered_users u,
	group_distinct_member_map gm
where
	u.user_id = gm.member_id
	and gm.group_id = 155702) as A
join 
(select	u.user_id,
	im_name_from_user_id(u.user_id) as employee_name
from
	registered_users u,
	group_distinct_member_map gm
where
	u.user_id = gm.member_id
	and gm.group_id = 463) as B
on (A.user_id = B.user_id)
"
    return [im_selection_to_list_box -translate_p "0" $bind_vars category_select $sql $select_name $defaults $size $multiple]
}    


# --------------------------------------------------------

ad_proc im_software_profile_select_multiple { select_name { defaults "" } { size "6"} {multiple ""}} {
    set bind_vars [ns_set create]
    
    set sql "
select A.user_id, A.employee_name from (select
	u.user_id,
	im_name_from_user_id(u.user_id) as employee_name
from
	registered_users u,
	group_distinct_member_map gm
where
	u.user_id = gm.member_id
	and gm.group_id = 465) as A
join 
(select	u.user_id,
	im_name_from_user_id(u.user_id) as employee_name
from
	registered_users u,
	group_distinct_member_map gm
where
	u.user_id = gm.member_id
	and gm.group_id = 463) as B
on (A.user_id = B.user_id) order by employee_name
"
    return [im_selection_to_list_box -translate_p "0" $bind_vars category_select $sql $select_name $defaults $size $multiple]
}


ad_proc im_pm_select_multiple { select_name { defaults "" } { size "6"} {multiple ""}} {
    set bind_vars [ns_set create]
    set pm_group_id [im_pm_group_id]
    set sql "
select
        u.user_id,
        im_name_from_user_id(u.user_id) as employee_name
from
        registered_users u,
        group_distinct_member_map gm
where
        u.user_id = gm.member_id
        and gm.group_id = $pm_group_id
order by lower(im_name_from_user_id(u.user_id))
"
    return [im_selection_to_list_box -translate_p "0" $bind_vars category_select $sql $select_name $defaults $size $multiple]
}

ad_proc im_active_pm_select_multiple { 
	select_name 
	{ defaults "" } 
	{ size "6"} {multiple ""} 
} { 
	returns html widget with employees having the PM role (im_projects::im_project_lead_id) in currently open projects
} {
    set bind_vars [ns_set create]
    set sql "
        select distinct
                pe.person_id,
                im_name_from_user_id(pe.person_id) as employee_name
        from
                persons pe,
                im_projects p,
                registered_users u
        where
                p.project_lead_id = pe.person_id and
                u.user_id = pe.person_id and
                p.project_status_id not in ([im_project_status_deleted]);
	"
    return [im_selection_to_list_box -translate_p "0" $bind_vars category_select $sql $select_name $defaults $size $multiple]
}

# ------------------------------------------------------
# User Community Component
# Show the most recent user registrations.
# This allows to detect duplicat registrations
# of users with multiple emails
# ------------------------------------------------------

ad_proc -public im_user_registration_component { current_user_id { max_rows 8} } {
    Shows the list of the last n registrations

    This allows to detect duplicat registrations
    of users with multiple emails
} {
    set date_format "YYYY-MM-DD"
    set bgcolor(0) " class=roweven"
    set bgcolor(1) " class=rowodd"
    set user_view_page "/intranet/users/view"
    set return_url [ad_conn url]?[ad_conn query]
    
    set user_id [ad_get_user_id]
    
    if {![im_permission $user_id view_user_regs]} { return "" }

    set rows_html ""
    set ctr 1
    db_foreach registered_users "" {

	regexp {(.*)\@(.*)} $email match email_name email_url
	set email_breakable "$email_name \@ $email_url"

	# Allow to approve non-approved members
	set approve_link ""
	if {"approved" != $member_state} { set approve_link "<a href=\"/acs-admin/users/member-state-change?member_state=approved&amp;[export_url_vars user_id return_url]\">[_ intranet-core.activate]</a>"
	}

	append rows_html "
<tr $bgcolor([expr $ctr % 2])>
  <td>$creation_date</td>
  <td><A href=\"$user_view_page?user_id=$user_id\">$name</A></td>
  <td><A href=\"mailto:$email\">$email_breakable</A></td>
  <td>$member_state $approve_link</td>
</tr>
"
	incr ctr
    }

    return "
<table border=0 cellspacing=1 cellpadding=1>
<tr class=rowtitle><td class=rowtitle align=center colspan=5>[_ intranet-core.Recent_Registrations]</td></tr>
<tr class=rowtitle>
  <td align=center class=rowtitle>[_ intranet-core.Date]</td>
  <td align=center class=rowtitle>[_ intranet-core.Name]</td>
  <td align=center class=rowtitle>[_ intranet-core.Email]</td>
  <td align=center class=rowtitle>[_ intranet-core.State]</td>
</tr>
$rows_html
<tr class=rowblank align=right>
  <td colspan=5>
    <a href=\"/intranet/users/index?view_name=user_community&amp;user_group_name=all&amp;order_by=Creation\">[_ intranet-core.more]</a>
  </td>
</tr>
</table>
"
}


# ------------------------------------------------------------------------
# Create a new user
# ------------------------------------------------------------------------

ad_proc -public im_user_create_new_user { 
    -username:required
    -email:required
    -first_names:required
    -last_name:required
    {-user_id "" }
    {-screen_name ""}
    {-password ""}
    {-password_confirm ""}
    {-url "" }
    {-secret_question ""}
    {-secret_answer "" }
    {-ignore_duplicate_user_p 0}
    {-current_user_id ""}
} {
    Create a new user from scratch
} {
	if {$current_user_id == "" } {
    	set current_user_id [ad_get_user_id]
	}
    set email [string trim $email]
    set similar_user_id [db_string similar_user "select party_id from parties where lower(email) = lower(:email)" -default 0]
    
    if {0 != $similar_user_id} {
	if {$ignore_duplicate_user_p} {
	    return [list creation_status ok]
	} else {
	    set view_similar_user_link "<A href=/intranet/users/view?user_id=$similar_user_id>[_ intranet-core.user]</A>"
	    ad_return_complaint 1 "<li><b>[_ intranet-core.Duplicate_UserB]<br>[_ intranet-core.lt_There_is_already_a_vi]<br>"
	    ad_script_abort
	}
    }
    
    if {"" == $password} {
	set password [ad_generate_random_string]
	set password_confirm $password
    }
    
    array set creation_info [auth::create_user \
				 -user_id $user_id \
				 -verify_password_confirm \
				 -username $username \
				 -email $email \
				 -first_names $first_names \
				 -last_name $last_name \
				 -screen_name $screen_name \
				 -password $password \
				 -password_confirm $password_confirm \
				 -url $url \
				 -secret_question $secret_question \
				 -secret_answer $secret_answer \
    ]

    set creation_status $creation_info(creation_status)
    if {"ok" == $creation_status} {

	set user_id $creation_info(user_id);

	# Update creation user to allow the creator to admin the user
	db_dml update_creation_user_id "
		update acs_objects
		set creation_user = :current_user_id
		where object_id = :user_id
	"

	# Call the "user_create" or "user_update" user_exit
	im_user_exit_call user_create $user_id
    }

    return [array get creation_info]
}




ad_proc -public im_user_update_existing_user { 
    -user_id:required
    -username:required
    -email:required
    -first_names:required
    -last_name:required
    {-screen_name ""}
    {-url "" }
    {-also_add_to_biz_object ""}
    {-profiles ""}
    {-edit_profiles_p 0}
} {
    Update an existing user and make sure he's member of all relevant tables
} {
    # Profile changes its value, possibly because of strange
    # ad_form sideeffects
    set profile_org $profiles

    set current_user_id [ad_get_user_id]

    # Make sure the "person" exists.
    # This may be not the case when creating a user from a party.
    set person_exists_p [db_string person_exists "select count(*) from persons where person_id = :user_id"]
    if {!$person_exists_p} {
	db_dml insert_person "
		    insert into persons (
			person_id, first_names, last_name
		    ) values (
			:user_id, :first_names, :last_name
		    )
	"
	# Convert the party into a person
	db_dml person2party "
		    update acs_objects
		    set object_type = 'person'
		    where object_id = :user_id
	"	
    }

    set user_exists_p [db_string user_exists "select count(*) from users where user_id = :user_id"]
    if {!$user_exists_p} {
	if {"" == $username} { set username $email} 
	db_dml insert_user "
		    insert into users (
			user_id, username
		    ) values (
			:user_id, :username
		    )
	"
	# Convert the person into a user
	db_dml party2user "
		    update acs_objects
		    set object_type = 'user'
		    where object_id = :user_id
	"
    }

    ns_log Notice "/users/new: person::update -person_id=$user_id -first_names=$first_names -last_name=$last_name"
    person::update \
	-person_id $user_id \
	-first_names $first_names \
	-last_name $last_name
    
    ns_log Notice "/users/new: party::update -party_id=$user_id -url=$url -email=$email"
    party::update \
	-party_id $user_id \
	-url $url \
	-email $email
    
    ns_log Notice "/users/new: acs_user::update -user_id=$user_id -screen_name=$screen_name"
    acs_user::update \
	-user_id $user_id \
	-screen_name $screen_name \
	-username $username


    # Add the user to some companies or projects
    array set also_add_hash $also_add_to_biz_object
    foreach oid [array names also_add_hash] {
	set object_type [db_string otype "select object_type from acs_objects where object_id=:oid"]
	set perm_cmd "${object_type}_permissions \$current_user_id \$oid object_view object_read object_write object_admin"
	eval $perm_cmd
	if {$object_write} {
	    set role_id $also_add_hash($oid)
	    im_biz_object_add_role $user_id $oid $role_id
	}
    }

    # For all users (new and existing one):
    # Add a users_contact record to the user since the 3.0 PostgreSQL
    # port, because we have dropped the outer join with it...
    catch { db_dml add_users_contact "insert into users_contact (user_id) values (:user_id)" } errmsg


    # Add the user to the "Registered Users" group, because
    # (s)he would get strange problems otherwise
    set registered_users [db_string registered_users "select object_id from acs_magic_objects where name='registered_users'"]
    set reg_users_rel_exists_p [db_string member_of_reg_users "
		select	count(*) 
		from	group_member_map m, membership_rels mr
		where	m.member_id = :user_id
			and m.group_id = :registered_users
			and m.rel_id = mr.rel_id 
			and m.container_id = m.group_id 
			and m.rel_type::text = 'membership_rel'::text
    "]
    if {!$reg_users_rel_exists_p} {
	relation_add -member_state "approved" "membership_rel" $registered_users $user_id
    }


    # TSearch2: We need to update "persons" in order to trigger the TSearch2
    # triggers
    db_dml update_persons "
		update persons
		set first_names = first_names
		where person_id = :user_id
    "

	
    set membership_del_sql "
	select
		r.rel_id
	from
		acs_rels r,
		acs_objects o
	where
		object_id_two = :user_id
		and object_id_one = :profile_id
		and r.object_id_one = o.object_id
		and o.object_type = 'im_profile'
		and rel_type = 'membership_rel'
    "


    # Get the list of profiles managable for current_user_id
    set managable_profiles [im_profile::profile_options_managable_for_user $current_user_id]

    # Extract only the profile_ids from the managable profiles
    set managable_profile_ids [list]
    foreach g $managable_profiles {
	lappend managable_profile_ids [lindex $g 1]
    }

    foreach profile_tuple [im_profile::profile_options_all] {

	# don't enter into setting and unsetting profiles
	# if the user has no right to change profiles.
	# Probably this is a freelancer or company
	# who is editing himself.
	if {!$edit_profiles_p} { break }
	
	ns_log Notice "profile_tuple=$profile_tuple"
	set profile_name [lindex $profile_tuple 0]
	set profile_id [lindex $profile_tuple 1]

	set is_member [db_string is_member "
		select count(*) 
		from group_distinct_member_map 
		where member_id=:user_id and group_id=:profile_id
	"]

	set should_be_member 0
	if {[lsearch -exact $profile_org $profile_id] >= 0} {
	    set should_be_member 1
	}
	
	if {$is_member && !$should_be_member} {
	    ns_log Notice "/users/new: => remove_member from $profile_name\n"
	    
	    if {[lsearch -exact $managable_profile_ids $profile_id] < 0} {
		ad_return_complaint 1 "<li>
		    [_ intranet-core.lt_You_are_not_allowed_t]"
		return
	    }
	    
	    # db_dml delete_profile $delete_rel_sql
	    db_foreach membership_del $membership_del_sql {
		ns_log Notice "/users/new: Going to delete rel_id=$rel_id"
		membership_rel::delete -rel_id $rel_id
	    }
	    
	    # Special logic: Revoking P/O Admin privileges also removes
	    # Site-Wide-Admin privs
	    if {$profile_id == [im_profile_po_admins]} { 
		ns_log Notice "users/new: Remove P/O Admins => Remove Site Wide Admins"
		permission::revoke -object_id [acs_magic_object "security_context_root"] -party_id $user_id -privilege "admin"
	    }
	    
	    # Remove all permission related entries in the system cache
	    im_permission_flush
	}
	
	
	if {!$is_member && $should_be_member} {
	    ns_log Notice "/users/new: => add_member to profile $profile_name\n"
	    
	    # Check if the profile_id belongs to the managable profiles of
	    # the current user. Normally, only the managable profiles are
	    # shown, which means that a user must have played around with
	    # the HTTP variables in oder to fool us...
	    if {[lsearch -exact $managable_profile_ids $profile_id] < 0} {
		ad_return_complaint 1 "<li>
		    [_ intranet-core.lt_You_are_not_allowed_t_1]"
		return
	    }
	    
	    # Make the user a member of the group (=profile)
	    ns_log Notice "/users/new: => relation_add $profile_id $user_id"
	    set rel_id [relation_add -member_state "approved" "membership_rel" $profile_id $user_id]
	    db_dml update_relation "update membership_rels set member_state='approved' where rel_id=:rel_id"
	    
	    
	    # Special logic for employees and P/O Admins:
	    # PM, Sales, Accounting, SeniorMan => Employee
	    # P/O Admin => Site Wide Admin
	    if {$profile_id == [im_profile_project_managers]} { 
		ns_log Notice "users/new: Project Managers => Employees"
		set rel_id [relation_add -member_state "approved" "membership_rel" [im_profile_employees] $user_id]
		db_dml update_relation "update membership_rels set member_state='approved' where rel_id=:rel_id"
	    }
	    
	    if {$profile_id == [im_profile_accounting]} { 
		ns_log Notice "users/new: Accounting => Employees"
		set rel_id [relation_add -member_state "approved" "membership_rel" [im_profile_employees] $user_id]
		db_dml update_relation "update membership_rels set member_state='approved' where rel_id=:rel_id"
	    }
	    
	    if {$profile_id == [im_profile_sales]} { 
		ns_log Notice "users/new: Sales => Employees"
		set rel_id [relation_add -member_state "approved" "membership_rel" [im_profile_employees] $user_id]
		db_dml update_relation "update membership_rels set member_state='approved' where rel_id=:rel_id"
	    }
	    
	    if {$profile_id == [im_profile_senior_managers]} { 
		ns_log Notice "users/new: Senior Managers => Employees"
		set rel_id [relation_add -member_state "approved" "membership_rel" [im_profile_employees] $user_id]
		db_dml update_relation "update membership_rels set member_state='approved' where rel_id=:rel_id"
	    }
	    
	    if {$profile_id == [im_profile_po_admins]} { 
		ns_log Notice "users/new: P/O Admins => Site Wide Admins"
		permission::grant -object_id [acs_magic_object "security_context_root"] -party_id $user_id -privilege "admin"
		im_security_alert -severity "Info" -location "users/new" -message "New P/O Admin" -value $email
	    }
	    
	    # Remove all permission related entries in the system cache
	    im_permission_flush
	    
	}
    }


    # Add a im_employees record to the user since the 3.0 PostgreSQL
    # port, because we have dropped the outer join with it...
    if {[im_table_exists im_employees]} {
	
	# Simply add the record to all users, even it they are not employees...
	set im_employees_exist [db_string im_employees_exist "select count(*) from im_employees where employee_id = :user_id"]
	if {!$im_employees_exist} {
	    db_dml add_im_employees "insert into im_employees (employee_id) values (:user_id)"
	}
    }


    # Call the "user_create" or "user_update" user_exit
    im_user_exit_call user_update $user_id
}








# ------------------------------------------------------------------------
# functions for printing the org chart
# ------------------------------------------------------------------------

ad_proc im_print_employee {person rowspan} "print function for org chart" {
    set user_id [fst $person]
    set employee_name [snd $person]
    set currently_employed_p [thd $person]

# Removed job title display
#    set job_title [lindex $person 3]

    if { $currently_employed_p == "t" } {

# Removed job title display
#	if { $rowspan>=2 } {
#	    return "<a href=\"/intranet/users/view?[export_url_vars user_id]\">$employee_name</a><br><i>$job_title</i>\n"
#	} else {
	    return "<a href=\"/intranet/users/view?[export_url_vars user_id]\">$employee_name</a><br>\n"
#	}
    } else {
	return "<i>[_ intranet-core.Position_Vacant]</i>"
    }
}

ad_proc im_prune_org_chart {tree} "deletes all leaves where currently_employed_p is set to vacant position" {
    set result [list [head $tree]]
    # First, recursively process the sub-trees.
    foreach subtree [tail $tree] {
	set new_subtree [im_prune_org_chart $subtree]
	if { ![null_p $new_subtree] } {
	    lappend result $new_subtree
	}
    }
    # Now, delete vacant leaves.
    # We also delete vacant inner nodes that have only one child.
    # 1. if the tree only consists of one vacant node
    #    -> return an empty tree
    # 2. if the tree has a vacant root and only one child
    #    -> return the child 
    # 3. otherwise
    #    -> return the tree 
    if { [thd [head $result]] == "f" } {
	switch [llength $result] {
	    1       { return [list] }
	    2       { return [snd $result] }
	    default { return $result }
	}
    } else {
	return $result
    }
}


# ------------------------------------------------------------------------
# Find out the user's subtypes (mapped from groups)
# ------------------------------------------------------------------------

ad_proc -public im_user_subtypes {
    user_id
} { 
    Returns a list of categories representing the user's subtypes.
    The list is derived from mapping users' groups to categories
} {
    # Find out all the groups of the user and map these
    # groups to im_category "Intranet User Type"
    set user_subtypes [db_list user_subtypes "
	select
		c.category_id
	from
		im_categories c,
		group_distinct_member_map gdmm
	where
		member_id = :user_id and
		c.aux_int1 = gdmm.group_id
    "]

    return $user_subtypes
}


# ------------------------------------------------------------------------
# Nuke a User
# ------------------------------------------------------------------------


ad_proc -public im_user_nuke {user_id} { 
    Delete a user from the database -
    Extremely dangerous!
} {
    set user_is_admin_p [im_is_user_site_wide_or_intranet_admin $user_id]
    if {$user_is_admin_p} {
	return "User is an administrator - you can't nuke an administrator"
    }

    set result ""
    set default_user [db_string default_user "
	select	min(person_id)
	from	persons
	where	person_id > 0
    "]

    db_transaction {
	
	# delete data from payroll
	# code shifted from intranet-payroll-procs to here
	# Sneha (19-04-2017) 
	
	set user_to_be_nuked [im_name_from_user_id $user_id] 
	db_dml document_reference_name "update xl_ats_document_reference_number set nuked_user_name=:user_to_be_nuked where issued_to=:user_id"

	ns_write "<li> Delete Appraisal Data </li>"
	ns_write "<ul>"
	
	ns_write " <li>delete from im_appraisal_others</li>"
	db_dml dete_data "delete from im_appraisal_others where employee_id=:user_id"
	
	ns_write " <li>delete from im_appraisal_review_notes</li>"
	db_dml dete_data "delete from im_appraisal_review_notes where employee_id=:user_id"
	
	ns_write "<li>Appraisal Data deleted Successfully </li></ul>"
	
	ns_write "<li> deleting im_database_logs"
	db_dml delete_data "delete from im_database_logs where user_id=:user_id"
	
	ns_write "<li> deleting im_employees_recurring_data"
	db_dml delete_data "delete from im_employees_recurring_data where employee_id=:user_id"
	
	ns_write "<li> deleting im_expenses_petty_cash"
	db_dml delete_data "delete from im_expenses_petty_cash where employee_id=:user_id"
	
	ns_write "<li> deleting im_score"
	db_dml delete_data "delete from im_score where score_for=:user_id"
	
	ns_write "<li> deleting im_score_response"
	db_dml delete_data "delete from im_score_response where s_resource_id=:user_id"
	
	ns_write "<li> deleting im_test_marks"
	db_dml delete_data "delete from im_test_marks where employee_id=:user_id"
	# delete data from payroll
	# code shifted from intranet-payroll-procs to here
	# Sneha (19-04-2017)
	
	# tables added 
	# edited 11-03-2015
	# nihit malhotra
	db_dml delete_work_data "delete from im_employees_absence_groups where employee_id=:user_id"
	db_dml delete_edu_data "delete from im_employees_education where employee_id=:user_id"
	db_dml delete_family_data "delete from im_employees_family where employee_id=:user_id"
	db_dml delete_family_data "delete from im_employees_recurring_data where employee_id=:user_id"
	db_dml delete_family_data "delete from im_employees_tasks where employee_id=:user_id"
	db_dml delete_visa_data "delete from im_employees_visa_details where employee_id=:user_id"
	db_dml delete_work_data "delete from im_employees_work_exp where employee_id=:user_id"	
	db_dml delete_work_data "delete from im_employees_multiple_data where employee_id=:user_id"	
	
	# bboard system
	ns_log Notice "users/nuke2: bboard_email_alerts"
	if {[im_table_exists bboard_email_alerts]} {
	    db_dml delete_user_bboard_email_alerts "delete from bboard_email_alerts where user_id = :user_id"
	    db_dml delete_user_bboard_thread_email_alerts "delete from bboard_thread_email_alerts where user_id = :user_id"
	    db_dml delete_user_bboard_unified "delete from bboard_unified where user_id = :user_id"
	    
	    # deleting from bboard is hard because we have to delete not only a user's
	    # messages but also subtrees that refer to them
	    bboard_delete_messages_and_subtrees_where  -bind [list user_id $user_id] "user_id = :user_id"
	}
    
	# let's do the classifieds now
	ns_log Notice "users/nuke2: classified_auction_bids"
	if {[im_table_exists classified_auction_bids]} {
	    db_dml delete_user_classified_auction_bids "delete from classified_auction_bids where user_id = :user_id"
	    db_dml delete_user_classified_ads "delete from classified_ads where user_id = :user_id"
	    db_dml delete_user_classified_email_alerts "delete from classified_email_alerts where user_id = :user_id"
	    db_dml delete_user_neighbor_to_neighbor_comments "
	delete from general_comments 
	where
		on_which_table = 'neighbor_to_neighbor'
		and on_what_id in (select neighbor_to_neighbor_id 
	from neighbor_to_neighbor 
	where poster_user_id = :user_id)"
	    db_dml delete_user_neighbor_to_neighbor "delete from neighbor_to_neighbor where poster_user_id = :user_id"
	}

	# now the calendar
	ns_log Notice "users/nuke2: calendar"
	if {[im_table_exists calendar]} {
	    db_dml delete_user_calendar "delete from calendar where creation_user = :user_id"
	}

	# contest tables are going to be tough
	ns_log Notice "users/nuke2: entrants_table_name"
	if {[im_table_exists entrants_table_name]} {
	    set all_contest_entrants_tables [db_list unused "select entrants_table_name from contest_domains"]
	    foreach entrants_table $all_contest_entrants_tables {
		db_dml delete_user_contest_entries "delete from $entrants_table where user_id = :user_id"
	    }
	}

	# Component Plugins
	ns_log Notice "users/nuke2: im_component_plugin_user_map"
	db_dml del_comp_map "delete from im_component_plugin_user_map where user_id = :user_id"

	
	# spam history
	ns_log Notice "users/nuke2: spam_history"
	if {[im_table_exists spam_history]} {
	    db_dml delete_user_spam_history "delete from spam_history where creation_user = :user_id"
	    db_dml delete_user_spam_history_sent "update spam_history set last_user_id_sent = NULL
		    where last_user_id_sent = :user_id"
	}
	
	# calendar
	ns_log Notice "users/nuke2: calendar_categories"
	if {[im_table_exists calendar_categories]} {
	    db_dml delete_user_calendar_categories "delete from calendar_categories where user_id = :user_id"
	}
	
	# sessions
	ns_log Notice "users/nuke2: sec_sessions"
	if {[im_table_exists sec_sessions]} {
	    db_dml delete_user_sec_sessions "delete from sec_sessions where user_id = :user_id"
	    db_dml delete_user_sec_login_tokens "delete from sec_login_tokens where user_id = :user_id"
	}
    
	# general comments
	ns_log Notice "users/nuke2: general_comments"
	if {[im_table_exists general_comments]} {
	    db_dml delete_user_general_comments "delete from general_comments where object_id = :user_id"
	}

	ns_log Notice "users/nuke2: comments"
	if {[im_table_exists comments]} {
	    db_dml delete_user_comments "delete from comments where object_id = :user_id"
	}

	ns_log Notice "users/nuke2: links"
	if {[im_table_exists links]} {
	    db_dml delete_user_links "delete from links where user_id = :user_id"
	}
	ns_log Notice "users/nuke2: chat_msgs"
	if {[im_table_exists chat_msgs]} {
	    db_dml delete_user_chat_msgs "delete from chat_msgs where creation_user = :user_id"
	}
	ns_log Notice "users/nuke2: query_strings"
	if {[im_table_exists query_strings]} {
	    db_dml delete_user_query_strings "delete from query_strings where user_id = :user_id"
	}
	ns_log Notice "users/nuke2: user_curriculum_map"
	if {[im_table_exists user_curriculum_map]} {
	    db_dml delete_user_user_curriculum_map "delete from user_curriculum_map where user_id = :user_id"
	}
	ns_log Notice "users/nuke2: user_content_map"
	if {[im_table_exists user_content_map]} {
	    db_dml delete_user_user_content_map "delete from user_content_map where user_id = :user_id"
	}
	ns_log Notice "users/nuke2: user_group_map"
	if {[im_table_exists user_group_map]} {
	    db_dml delete_user_user_group_map "delete from user_group_map where user_id = :user_id"
	}
	
	ns_log Notice "users/nuke2: users_interests"
	if {[im_table_exists users_interests]} {
	    db_dml delete_user_users_interests "delete from users_interests where user_id = :user_id"
	}
	
	ns_log Notice "users/nuke2: users_charges"
	if {[im_table_exists users_charges]} {
	    db_dml delete_user_users_charges "delete from users_charges where user_id = :user_id"
	}
	
	ns_log Notice "users/nuke2: users_demographics"
	if {[im_table_exists users_demographics]} {
	    db_dml set_referred_null_user_users_demographics "update users_demographics set referred_by = null where referred_by = :user_id"
	    db_dml delete_user_users_demographics "delete from users_demographics where user_id = :user_id"
	}
	
	ns_log Notice "users/nuke2: users_preferences"
	if {[im_table_exists users_preferences]} {
	    db_dml delete_user_users_preferences "delete from users_preferences where user_id = :user_id"
	}
	
	if {[im_table_exists user_preferences]} {
	    db_dml delete_user_user_preferences "delete from user_preferences where user_id = :user_id"
	}
	
	if {[im_table_exists users_contact]} {
	    db_dml delete_user_users_contact "delete from users_contact where user_id = :user_id"
	}
    
	# Permissions
	db_dml perms "delete from acs_permissions where grantee_id = :user_id"
	db_dml perms "delete from acs_permissions where object_id = :user_id"
	

	# Reassign objects to a default user...
	db_dml reassign_objects "update acs_objects set modifying_user = :default_user where modifying_user = :user_id"
	db_dml reassign_projects "update acs_objects set creation_user = :default_user where creation_user = :user_id"
	
	# Lang_message_audit
	db_dml lang_message_audit "update lang_messages_audit set overwrite_user = null where overwrite_user = :user_id"
	db_dml lang_message "update lang_messages set creation_user = null where creation_user = :user_id"
	
	# Deleting cost entries in acs_objects that are "dangeling", i.e. that don't have an
	# entry in im_costs. These might have been created during manual deletion of objects
	# Very dirty...
	set list [db_list get_ids "select cost_id from im_costs"]
	if {$list != "" } {
		db_dml dangeling_costs "delete from acs_objects where object_type = 'im_cost' and object_id not in ( [join $list ","])"
	}
	
	# Costs
	db_dml invoice_references "update im_invoices set company_contact_id = null where company_contact_id = :user_id"
	db_dml delete_data "delete from im_repeating_costs where rep_cost_id in (select cost_id from im_costs where cause_object_id = :user_id)"
	db_dml cuase_objects "delete from im_costs where cause_object_id = :user_id"
	db_dml cost_providers "update im_costs set provider_id = :default_user where provider_id = :user_id"

	# Cost Centers
	db_dml reset_cost_center_managers "update im_cost_centers set manager_id = null where manager_id = :user_id"

	# Payments
	db_dml reset_payments "update im_payments set last_modifying_user = :default_user where last_modifying_user = :user_id"
	
	# Forum
	db_dml forum "delete from im_forum_topic_user_map where user_id = :user_id"
	db_dml forum "update im_forum_topics set owner_id = :default_user where owner_id = :user_id"
	db_dml forum "update im_forum_topics set asignee_id = null where asignee_id = :user_id"
	db_dml forum "update im_forum_topics set object_id = :default_user where object_id = :user_id"

	# Timesheet
	db_dml timesheet "delete from im_hours where user_id = :user_id"
	db_dml timesheet "delete from im_user_absences where owner_id = :user_id"
	

	# Remove user from business objects that we don't want to delete...
	db_dml remove_from_companies "update im_companies set manager_id = null where manager_id = :user_id"
	db_dml remove_from_companies "update im_companies set accounting_contact_id = null where accounting_contact_id = :user_id"
	db_dml remove_from_companies "update im_companies set primary_contact_id = null where primary_contact_id = :user_id"
	db_dml remove_from_projects "update im_projects set supervisor_id = null where supervisor_id = :user_id"
	db_dml remove_from_projects "update im_projects set project_lead_id = null where project_lead_id = :user_id"
	
    	db_dml reassign_projects "update acs_objects set creation_user = :default_user where object_type = 'im_office' and creation_user = :user_id"
	db_dml reassign_projects "update acs_objects set creation_user = :default_user where object_type = 'im_company' and creation_user = :user_id"
	db_dml remove_from_companies "update im_offices set contact_person_id = null where contact_person_id = :user_id"


	# Freelance
	if {[im_table_exists im_freelance_skills]} {
	    db_dml trans_tasks "delete from im_freelance_skills where user_id = :user_id"
	    db_dml freelance "delete from im_freelancers where user_id = :user_id"
	    db_dml freelance_conf "update im_freelance_skills set confirmation_user_id = null where confirmation_user_id = :user_id"
	}


	# Helpdesk + ConfDB
	if {[im_table_exists im_tickets]} {
	    db_dml assignees "update im_tickets set ticket_assignee_id = :default_user where ticket_assignee_id = :user_id"
	    db_dml assignees "update im_tickets set ticket_customer_contact_id = :default_user where ticket_customer_contact_id = :user_id"
	}
	if {[im_table_exists im_conf_items]} {
	    db_dml assignees "update im_conf_items set conf_item_owner_id = :default_user where conf_item_owner_id = :user_id"
	}

	# Simple Survey
	if {[im_table_exists survsimp_responses]} {
	    db_dml assignees "update survsimp_responses set related_context_id = :default_user where related_context_id = :user_id"
	    db_dml assignees "update survsimp_responses set related_object_id = :default_user where related_object_id = :user_id"
	}

	
	# Translation
	if {[im_table_exists im_trans_tasks]} {
	    db_dml remove_from_projects "update im_projects set company_contact_id = null where company_contact_id = :user_id"
	    db_dml trans_tasks "update im_trans_tasks set trans_id = null where trans_id = :user_id"
	    db_dml trans_tasks "update im_trans_tasks set edit_id = null where edit_id = :user_id"
	    db_dml trans_tasks "update im_trans_tasks set proof_id = null where proof_id = :user_id"
	    db_dml trans_tasks "update im_trans_tasks set other_id = null where other_id = :user_id"
	    db_dml task_actions "delete from im_task_actions where user_id = :user_id"
	}
	
	# Translation RFQs
	if {[im_table_exists im_trans_rfq_answers]} {
	    db_dml rfq_answers "update im_trans_rfq_answers set answer_user_id = :default_user where answer_user_id = :user_id"
	}
	if {[im_table_exists im_freelance_rfq_answers]} {
	    db_dml rfq_answers "update im_freelance_rfq_answers set answer_user_id = :default_user where answer_user_id = :user_id"
	}

	if {[im_table_exists im_trans_quality_reports]} {
	    db_dml trans_quality "delete from im_trans_quality_entries where report_id in (
		select report_id from im_trans_quality_reports where reviewer_id = :user_id
	    )"
	    db_dml trans_quality "delete from im_trans_quality_reports where reviewer_id = :user_id"
	}

	# Workflow
	db_dml wf "update wf_tasks set holding_user = :default_user where holding_user = :user_id"
	db_dml wf "update wf_case_assignments set party_id = :default_user where party_id = :user_id"
	db_dml wf "update wf_context_assignments set party_id = :default_user where party_id = :user_id"

	#ats 
	set user_to_be_nuked_cid [db_string get_c_id "select c_id from xl_ats_req_candidate_map where user_id=:user_id" -default ""] 
	set ats_candidate_nuked [nuke_candidate -candidate_id $user_to_be_nuked_cid]

	# Filestorage
	db_dml filestorage "delete from im_fs_folder_status where user_id = :user_id"
	db_dml filestorage "delete from im_fs_actions where user_id = :user_id"
	db_dml filestorage "update im_fs_folders set object_id = null where object_id = :user_id"

	# Bug-Tracker
        if {[im_table_exists bt_user_prefs]} {
	    db_dml bt_prefs "delete from bt_user_prefs where user_id = :user_id"
	}
        if {[im_table_exists bt_components]} {
	    db_dml bt_comps "update bt_components set maintainer = null where maintainer = :user_id"
	}
        if {[im_table_exists bt_patch_actions]} {
	    db_dml bt_patch_actions "update bt_patch_actions set actor = :default_user where actor = :user_id"
	}
	
	ns_write "<li>Delete enteries from im_timesheet_conf_objects</li>"
	db_dml delete_data "delete from im_timesheet_conf_objects where conf_user_id =:user_id"
	
	ns_write "<li>Delete enteries from notifications</li>"
	db_dml delete_data "delete from notifications where notif_user =:user_id"
	
	
	
	set rels [db_list rels "select rel_id from acs_rels where object_id_one = :user_id or object_id_two = :user_id"]
	foreach rel_id $rels {
	    db_dml del_rels "delete from group_element_index where rel_id = :rel_id"
	    db_dml del_rels "delete from im_biz_object_members where rel_id = :rel_id"
	    db_dml del_rels "delete from membership_rels where rel_id = :rel_id"
	    db_dml del_rels "delete from acs_rels where rel_id = :rel_id"
	    db_dml del_rels "delete from acs_objects where object_id = :rel_id"
	}
	
	db_dml party_approved_member_map "delete from party_approved_member_map where party_id = :user_id"
	db_dml party_approved_member_map "delete from party_approved_member_map where member_id = :user_id"
	
	if {[im_table_exists im_employees]} {
	    db_dml update_dependent_employees "update im_employees set supervisor_id = null where supervisor_id = :user_id"
	    db_dml delete_employees "delete from im_employees where employee_id = :user_id"
	}
	
	ns_log Notice "users/nuke2: Main user tables"
	db_dml update_creation_users "update acs_objects set creation_user = null where creation_user = :user_id"
	db_dml delete_user "delete from users where user_id = :user_id"
	db_dml delete_user "delete from persons where person_id = :user_id"
	db_dml delete_user "delete from parties where party_id = :user_id"
	db_dml delete_user "delete from acs_objects where object_id = :user_id"

	
	
	# Returning empty string - everything went OK
	return ""	

    } on_error {
	
	set detailed_explanation ""
	if {[ regexp {integrity constraint \([^.]+\.([^)]+)\)} $errmsg match constraint_name]} {
	    set sql "
		select table_name 
		from user_constraints 
		where constraint_name=:constraint_name
	    "
	    db_foreach user_constraints_by_name $sql {
		set detailed_explanation "<p>
	    [_ intranet-core.lt_It_seems_the_table_we]"
	    }
	}

	# Return the error string - indicates that there were errors    
	set result "
	[_ intranet-core.lt_The_nuking_of_user_us]
	$detailed_explanation<p>
	[_ intranet-core.lt_For_good_measure_here]
	<blockquote><pre>\n$errmsg\n</pre></blockquote>
	"
    }

    return $result
}



ad_proc im_upload_cvs_translate_varname { var_name} {
    Translate German var names to English.
    In the future we'll also support translations from other
    Office versions...
} {
    set name [string tolower [im_l10n_normalize_string $var_name]]

    switch $var_name {
	anrede { return "title" }
	vorname { return "first_name" }
	weitere_vornamen { return "middle_name" }
	nachname { return "last_name" }
	suffix { return "suffix" }
	emailadresse { return "e_mail_address" }
	firma { return "company" }
	abteilung { return "department" }
	position { return "job_title" }
	strase_geschaftlich { return "business_street" }
	strase_geschaftlich_2 { return "business_street_2" }
	strase_geschaftlich_3 { return "business_street_3" }
	ort_geschaftlich { return "business_city" }
	region_geschaftlich { return "business_state" }
	postleitzahl_geschaftlich { return "business_postal_code" }
	land_geschaftlich { return "business_country" }
	strase_privat { return "home_street" }
	strase_privat_2 { return "home_street_2" }
	strase_privat_3 { return "home_street_3" }
	ort_privat { return "home_city" }
	region_privat { return "home_state" }
	postleitzahl_privat { return "home_postal_code" }
	land_privat { return "home_country" }
	weitere_strase { return "other_street" }
	weitere_strase_2 { return "other_street_2" }
	weitere_strase_3 { return "other_street_3" }
	weiterer_ort { return "other_city" }
	weitere_region { return "other_state" }
	weitere_postleitzahl { return "other_postal_code" }
	weiteres_land { return "other_country" }
	telefon_assistent { return "assistants_phone" }
	fax_geschaftlich { return "business_fax" }
	telefon_geschaftlich { return "business_phone" }
	telefon_geschaftlich_2 { return "business_phone_2" }
	ruckmeldung { return "callback" }
	autotelefon { return "car_phone" }
	telefon_firma { return "company_main_phone" }
	fax_privat { return "home_fax" }
	telefon_privat { return "home_phone" }
	telefon_privat_2 { return "home_phone_2" }
	isdn { return "isdn" }
	mobiltelefon { return "mobile_phone" }
	weiteres_fax { return "other_fax" }
	weiteres_telefon { return "other_phone" }
	pager { return "pager" }
	haupttelefon { return "primary_phone" }
	mobiltelefon_2 { return "radio_phone" }
	telefon_fur_horbehinderte { return "tty_tdd_phone" }
	telex { return "telex" }
	abrechnungsinformation { return "account" }
	benutzer_1 { return "user_1" }
	benutzer_2 { return "user_2" }
	benutzer_3 { return "user_3" }
	benutzer_4 { return "user_4" }
	beruf { return "job_title" }
	buro { return "office_location" }
	e_mail_adresse { return "e_mail_address" }
	e_mail_typ { return "e_mail_type" }
	e_mail_angezeigter_name { return "e_mail_display_name" }
	e_mail_2_adresse { return "e_mail_2_address" }
	e_mail_2_typ { return "e_mail_2_type" }
	e_mail_2_angezeigter_name { return "e_mail_2_display_name" }
	e_mail_3_adresse { return "e_mail_3_address" }
	e_mail_3_typ { return "e_mail_3_type" }
	e_mail_3_angezeigter_name { return "e_mail_3_display_name" }
	empfohlen_von { return "referred_by" }
	geburtstag { return "birthday" }
	geschlecht { return "gender" }
	hobby { return "hobby" }
	initialen { return "initials" }
	internet_frei_gebucht { return "internet_free_busy" }
	jahrestag { return "anniversary" }
	kategorien { return "categories" }
	kinder { return "children" }
	konto { return "account" }
	name_assistent { return "assistant_s_name" }
	name_des_der_vorgesetzten { return "manager_s_name" }
	notizen { return "notes" }
	organisations_nr { return "organizational_id_number" }
	ort { return "location" }
	partner { return "spouse" }
	postfach_geschaftlich { return "po_box" }
	postfach_privat { return "ttt" }
	prioritat { return "priority" }
	privat { return "private" }
	regierungs_nr { return "government_id_number" }
	reisekilometer { return "mileage" }
	sprache { return "language" }
	stichworter { return "ttt" }
	vertraulichkeit { return "sensitivity" }
	verzeichnisserver { return "directory_server" }
	webseite { return "web_page" }
	weiteres_postfach  { return "po_box" }
    }
    return $var_name
}
 # -----------------------------------------
 # nihit malhotra
 # created march 2015
ad_proc im_employee_info_view_permissions { 
	{-user_id:required}
	{-hr_view_p ""}
} {
	set view 0
	set current_user_id [ad_maybe_redirect_for_registration]
	if {$hr_view_p == "" } {
		if {$user_id == $current_user_id } {
			set view 1
		}
		if { [im_profile::member_p -profile "HR Managers" -user_id $current_user_id]  || [im_is_user_site_wide_or_intranet_admin $current_user_id] || [im_profile::member_p -profile "Senior Managers" -user_id $current_user_id] || [im_profile::member_p -profile "Office Admin" -user_id $current_user_id]  } {
			set view 1
		}
	} else {
		if { [im_profile::member_p -profile "HR Managers" -user_id $current_user_id] || [im_profile::member_p -profile "Senior Managers" -user_id $current_user_id] || [im_is_user_site_wide_or_intranet_admin $current_user_id] || [im_profile::member_p -profile "Office Admin" -user_id $current_user_id] } {
			set view 1
		}
	}
	return $view
}

ad_proc currency_api_request {
	-date:required
	{ -currency_to_check "" }
} {
	set c_date [db_string current_date "select to_char(now(),'YYYY-MM-DD')"]
	if {$c_date<$date} {
		set date $c_date
	}
	set tcl_string [db_string get_data "select json_data from im_rate_conversions_api where date =:date limit 1" -default ""]
	if {$tcl_string != ""} {
		set page_info [json2dict $tcl_string]
		set page_dict [dict get $page_info "rates"]
		if {$currency_to_check != "" } {
			set currency_to_check [string toupper $currency_to_check]
			if { ![dict exists $page_dict $currency_to_check ]} {
				set call_api_2 1
				db_dml delete "delete from im_rate_conversions_api where date =:date"
			} else {
				return $page_info
			}
		} else {
			return $page_info
		}
	}
	set i 0
	while {$i<20} {
		if { [catch {
		   	package require http 
			break

		} err_msg] } {
			incr i
			continue
		}
	}
	package require tls
	set call_api_2 0
	set final_return ""
	http::register https 443 tls::socket
	if {[catch {set token [http::geturl "http://api.ratesapi.io/api/$date" ] } err_msg]} {
		set call_api_2 1
	} else {
		# set token [http::geturl "https://google.com/$date" ]
		set status [http::status $token]
		# return $status
		set answer [http::data $token]
		set validate_json [util::json::validate $answer]
		set json_output_2 $answer
		set tcl_list [json2dict $answer]
		# http::cleanup $token
		# http::unregister https
		# return $tcl_list
		# set validate_json [util::json::validate $json_output_1]
		# set tcl_list [json2dict $json_output_1]
		if { [dict exists $tcl_list "rates"] } {
			set page_info [dict get $tcl_list "rates"]
			dict append page_info "EUR" "1.0"
			# doc_return 200 text/plain $page_info
			set tcl_list [dict replace $tcl_list "rates" $page_info]
			set final_return $tcl_list
			if {$currency_to_check != "" } {
				set currency_to_check [string toupper $currency_to_check]
				if { ![dict exists $page_info $currency_to_check ]} {
					set call_api_2 1
				}
			}
		} else {
			set call_api_2 1
		}

		http::cleanup $token
	}
	
	if { $call_api_2 == 1} {
		# return 2
		set api_key "d996204fde5ca19f579f58e3269bd3a8"
		set json_output_2 [http::data [http::geturl "http://data.fixer.io/api/$date?access_key=$api_key"] ]
		set validate_json [util::json::validate $json_output_2]
		set tcl_list [json2dict $json_output_2] 
		set final_return $tcl_list
	}
	http::unregister https
	if { $final_return != "" && [dict exists $final_return "rates"] } {
		db_dml insert "insert into im_rate_conversions_api (date,json_data) values ('$date','$json_output_2')"
	}
	return $final_return
	
}

# 26-03-2015
# Nihit Malhotra
 
ad_proc convert_amount_in_usd {
	-currency:required
	-date:required
} { 
	# package require http
	set currency [string tolower $currency]
	set new_currency ""
	set usd ""
	# set api_key "d996204fde5ca19f579f58e3269bd3a8"
	# Improved API request
	# 2017-03-09 
	# Nihit Malhotra
	set org_date $date
	if { $currency == "inr" } {
		db_0or1row get_rate "select usd from im_invoice_rbi_rates where date=:date"
		if {$usd == ""} {
			for { set i 1 } { $i < 15 } { incr i } {
				set date_int [clock add [clock scan $date -format "%Y-%m-%d"] -1 days]
				set date [clock format $date_int -format "%Y-%m-%d"]
				db_0or1row get_rate "select usd from im_invoice_rbi_rates where date=:date"
				if {$usd != ""} {
					break
				}
			}
		}
		if {$usd == ""} {
			# set json_output [http::data [http::geturl "http://api.fixer.io/$org_date?base=USD"] ]
			# set json_output [http::data [http::geturl "http://data.fixer.io/api/$org_date?access_key=$api_key"] ]
			# set validate_json [util::json::validate $json_output]
			# set tcl_list [json2dict $json_output]
			
			set tcl_list [currency_api_request -date $org_date]
			if { [dict exists $tcl_list "rates"] } {
				set page_info [dict get $tcl_list "rates"]
				set in_inr [dict get $page_info "INR"]
				set in_usd [dict get $page_info "USD"]
				set usd [expr $in_inr / $in_usd]
			} else {
				set usd 0
				return 0
			}
		}
		return [expr 1 / $usd ]
	}
	if { [db_0or1row chk_column "SELECT column_name FROM information_schema.columns WHERE table_name='im_invoice_rbi_rates' and column_name=:currency"] } {
		set found_p [db_0or1row get_rate "select $currency as new_currency , usd from im_invoice_rbi_rates where date=:date"]
		if {!$found_p} {
			for { set i 1 } { $i < 15 } { incr i } {
				set date_int [clock add [clock scan $date -format "%Y-%m-%d"] -1 days]
				set date [clock format $date_int -format "%Y-%m-%d"]
				set found_p [db_0or1row get_rate "select $currency  as new_currency, usd from im_invoice_rbi_rates where date=:date"]
				if {$found_p } {
					break
				}
			}
		}
	}
	if { $new_currency == ""} {
		# set json_output [http::data [http::geturl "http://api.fixer.io/$org_date?base=$currency"] ]
		# set json_output [http::data [http::geturl "http://data.fixer.io/api/$org_date?access_key=$api_key"] ]
		# set validate_json [util::json::validate $json_output]
		# set tcl_list [json2dict $json_output]
		
		set tcl_list [currency_api_request -date $org_date -currency_to_check $currency ]
		
		# if { [string first "Invalid base" $tcl_list] == -1 } {
			if { [dict exists $tcl_list "rates"] } {
				set page_info [dict get $tcl_list "rates"]
				set in_inr [dict get $page_info "INR"]
				set in_new_curr [dict get $page_info [string toupper $currency]]
				set in_usd [dict get $page_info "USD"]
				set new_currency [expr $in_inr / $in_new_curr]
				set usd [expr $in_inr / $in_usd]
				# set new_currency [dict get $page_info "INR"]
			} else {
				return 0
			}
		# }
		
		# set json_output [http::data [http::geturl "http://api.fixer.io/$org_date?base=USD"] ]
		# set json_output [http::data [http::geturl "http://data.fixer.io/api/$org_date?access_key=$api_key"] ]
		# set validate_json [util::json::validate $json_output]
		# set tcl_list [json2dict $json_output]
		# set tcl_list [currency_api_request -date $org_date]
		# if { [dict exists $tcl_list "rates"] } {
		# 	set page_info [dict get $tcl_list "rates"]
		# 	set in_inr [dict get $page_info "INR"]
		# 	set in_usd [dict get $page_info "USD"]
		# 	set usd [expr $in_inr / $in_usd]
		# } else { 
		# 	set usd 0
		# 	return 0
		# }
	}
	if { $new_currency != "" && $usd != "" } {
		return [expr $new_currency / $usd ]
	} else {
		return 0
	}
}


# 14-03-2017
ad_proc convert_currency_amount {
	-convert_from:required
	-convert_to:required
	-currency_amount:required
	-date:required
} { 
	
	# package require http
	set org_date $date
	set convert_from [string tolower $convert_from]
	set convert_to [string tolower $convert_to]
	set rate1 ""
	set rate2 ""
	# set api_key "d996204fde5ca19f579f58e3269bd3a8"
	set amount_to_convert_in_inr ""
	if { $convert_from != "inr" } {
		# set test_var "select $convert_from from im_invoice_rbi_rates where date=:date"
		# get rate1
		set column_found_p [db_0or1row chk_data "SELECT column_name FROM information_schema.columns WHERE table_name='im_invoice_rbi_rates' and column_name=:convert_from"]
		if {$column_found_p } {
			set rate1 [db_string get_rate1 "select $convert_from from im_invoice_rbi_rates where date=:date" -default ""]		
			if { $rate1 == ""} {
				for { set i 1 } { $i < 15 } { incr i } {
					set date_int [clock add [clock scan $date -format "%Y-%m-%d"] -1 days]
					set date [clock format $date_int -format "%Y-%m-%d"]
					set rate1 [db_string get_rate "select $convert_from from im_invoice_rbi_rates where date=:date" -default ""] 
					if {$rate1 != ""} {
						break
					}
				}
			}
		}
		if {$rate1 == ""} {
			# set json_output [http::data [http::geturl "http://api.fixer.io/$org_date?base=$convert_from"] ]
			# set json_output [http::data [http::geturl "http://data.fixer.io/api/$org_date?access_key=$api_key"] ]
			# set validate_json [util::json::validate $json_output]
			# set tcl_list [json2dict $json_output]
			set tcl_list [currency_api_request -date $org_date -currency_to_check $convert_from]
			if { [lsearch $tcl_list "error"] == -1 } {
				if { [dict exists $tcl_list "rates"] } {
					set page_info [dict get $tcl_list "rates"]
					set temp_rate_inr [dict get $page_info "INR"]
					set temp_rate_from [dict get $page_info [string toupper $convert_from] ]
					set rate1 [expr $temp_rate_inr / $temp_rate_from]
				} else {
					return 0
				}
			} else {
				return 0
			}
		}
		set amount_to_convert_in_inr [expr $rate1 * $currency_amount]
		if {$convert_to == "inr"} {
			return $amount_to_convert_in_inr
		}
		set date $org_date
		set column_found_p2 [db_0or1row chk_data "SELECT column_name FROM information_schema.columns WHERE table_name='im_invoice_rbi_rates' and column_name=:convert_to"]
		if {$column_found_p2 } {
			set rate2 [db_string get_rate1 "select $convert_to from im_invoice_rbi_rates where date=:date" -default ""]		
			if { $rate2 == "" } {
				for { set i 1 } { $i < 15 } { incr i } {
					set date_int [clock add [clock scan $date -format "%Y-%m-%d"] -1 days]
					set date [clock format $date_int -format "%Y-%m-%d"]
					set rate2 [db_string get_rate "select $convert_to from im_invoice_rbi_rates where date=:date" -default ""]
					if {$rate2 != ""} {
						break
					}
				}
			}
		}
		# doc_return 200 "text/html" $rate2
		if {$rate2 == ""} {
			# set json_output [http::data [http::geturl "http://api.fixer.io/$org_date?base=$convert_to"] ]
			# set json_output [http::data [http::geturl "http://data.fixer.io/api/$org_date?access_key=$api_key"] ]
			# # doc_return 200 "text/html" $json_output
			# set validate_json [util::json::validate $json_output]
			# set tcl_list [json2dict $json_output]
			set tcl_list [currency_api_request -date $org_date -currency_to_check $convert_to]
			if { [lsearch $tcl_list "error"] == -1 } {
				if { [dict exists $tcl_list "rates"] } {
					set page_info [dict get $tcl_list "rates"]
					set temp_rate_inr [dict get $page_info "INR"]
					set temp_rate_from [dict get $page_info [string toupper $convert_to] ]
					set rate2 [expr $temp_rate_inr / $temp_rate_from]
				} else {
					return 0
				}
			} else {
				return 0
			}
		}
		# doc_return 200 "text/html" $rate2
		return [ expr (1 / $rate2) * $amount_to_convert_in_inr ]
	
	} else {
		set column_found_p [db_0or1row chk_data "SELECT column_name FROM information_schema.columns WHERE table_name='im_invoice_rbi_rates' and column_name=:convert_to"]
		if {$column_found_p } {
			set rate1 [db_string get_rate1 "select $convert_to from im_invoice_rbi_rates where date=:date" -default ""]		
			if { $rate1 == "" } {
				for { set i 1 } { $i < 15 } { incr i } {
					set date_int [clock add [clock scan $date -format "%Y-%m-%d"] -1 days]
					set date [clock format $date_int -format "%Y-%m-%d"]
					set rate1 [db_string get_rate "select $convert_to from im_invoice_rbi_rates where date=:date" -default ""]
					if {$rate1 != ""} {
						break
					}
				}
			}
		}
		if { $rate1 == "" } {
			# set json_output [http::data [http::geturl "http://api.fixer.io/$org_date?base=$convert_to"] ]
			# set validate_json [util::json::validate $json_output]
			# set tcl_list [json2dict $json_output]
			set tcl_list [currency_api_request -date $org_date -currency_to_check $convert_to]
			if { [lsearch $tcl_list "error"] == -1 } {
				if { [dict exists $tcl_list "rates"] } {
					set page_info [dict get $tcl_list "rates"]
					set temp_rate_inr [dict get $page_info "INR"]
					set temp_rate_from [dict get $page_info [string toupper $convert_to] ]
					set rate1 [expr $temp_rate_inr / $temp_rate_from]
				} else {
					return 0
				}
			} else {
				return 0
			}
		}
			
		return [expr (1 / $rate1) * $currency_amount]
	}
}

ad_proc get_employee_for_petty_cash {
	-user_id:required
} {
	if {$user_id == 8892 } {
		return "non"
	}
	set found_p [db_0or1row get_data "select firm_id from im_expenses_petty_cash where employee_id=:user_id"]
	if {$found_p } {
		return $firm_id
	} 
	set accountant_p [db_0or1row chk_group "select group_id from group_member_map where (group_id=585 or group_id=471 or group_id=459 or group_id=542432) and member_id=:user_id limit 1"]
	if {$accountant_p } {
		return "all"
	}
	return "non"
} 


ad_proc im_subordinates_list { -user_id:required { -flag_val "" } {-ignore_termination 0 } } {
	set flag 1
	set final_list [list]
	# lappend $user_id
	set j 1
	while { $flag } {
		if {$user_id != ""} { 
			if { $flag_val == 1 } { 
				set sub_list [db_list get_sub_list "select employee_id from im_employees where  immediate_supervisor_id=:user_id"] 
			} else { 
				set sub_list [db_list get_sub_list "select employee_id from im_employees where supervisor_id in ($user_id) or immediate_supervisor_id in ($user_id)"] 
			}
		} else {
			set sub_list ""
		}  

		if { $flag_val == 1 } { 
			set return_list_another [list]
			foreach employee_id_val $sub_list {  
				set date_list [employee_join_terminate_date -user_id $employee_id_val]  
				set term_date [lindex $date_list 1] 
				
				set term_date_int [clock scan $term_date -format "%Y-%m-%d"]
				set current_date_int [db_string get_date "select to_char(now(),'YYYY-MM-DD')"] 
				
				if { $current_date_int < $term_date_int } { 
					lappend return_list_another $employee_id_val
				} 
			}   
			return $return_list_another 
		}
		
		set user_id ""
		set i 1
		foreach next_ids $sub_list {
			incr i
			lappend final_list $next_ids
			if {$user_id != ""} {
				append user_id ","
			}
			append user_id "$next_ids"
		}
		if {$i == 1} {
			set flag 0
		}
		incr j
		if {$j > 100} {
			break
		}
	}
	set final_list [lsort -unique $final_list]
	if {$ignore_termination == 1 } {
		return $final_list
	}
	set return_list [list]
	foreach employee_id_val $final_list {
		set date_list [employee_join_terminate_date -user_id $employee_id_val]
		set term_date [lindex $date_list 1]
		set term_date_int [clock scan $term_date -format "%Y-%m-%d"]
		set current_date_int [clock scan [db_string get_date "select to_char(now(),'YYYY-MM-DD')"] -format "%Y-%m-%d"]
		if {$current_date_int < $term_date_int} {
			lappend return_list $employee_id_val
		}
	}  
	
	if { $flag_val != 1 } {  
		return $return_list 
	}
}



ad_proc -public im_subordinates_options {
    { -user_id 0 }
	{ -include_supervisor_p 0}
} {
        Returns a list of (user_id user_name) tuples that are subordinates of a particular user.
} {
    if {"" == $user_id} { return "" }    
	set sub_list [im_subordinates_list -user_id $user_id]
	if {$include_supervisor_p } {
		lappend sub_list $user_id
	}
	set option [list]
	if {$sub_list != ""} {
	# Edited to show all the subordinates till depth
    set options [db_list_of_lists user_options "
                select distinct
                       	acs_object__name(u.user_id) as name,
                       	u.user_id
                from
                       	users_active u,
                       	group_distinct_member_map m,
		       	im_employees e
                where
                       	u.user_id = m.member_id
			and e.employee_id = u.user_id
       		       	and u.user_id in ([join $sub_list ","]) order by name
        "]
	}
	
    return $options
} 

ad_proc im_subordinates_select {
    {-include_empty_p 0}
    {-include_empty_name "All"}
    {-user_id 0 }
	{-include_supervisor_p 0}
    select_name
    { default "" }
} {
    Returns an html select box named $select_name and defaulted to
    $default with a list of all the available project_leads in
    the system
} {
    set user_options [im_subordinates_options -user_id $user_id -include_supervisor_p $include_supervisor_p]
    if {$include_empty_p} { set user_options [linsert $user_options 0 [list $include_empty_name ""]] }
    return [im_options_to_select_box $select_name $user_options $default]
} 


ad_proc employee_profile_data_component { -user_id:required } {
	# component to show promotions, domains and type of projects of employees
	set current_user_id [ad_maybe_redirect_for_registration]
	set edit_p 0
	set admin_p [im_is_user_site_wide_or_intranet_admin $current_user_id]
	set hr_p [im_profile::member_p -profile "HR Managers" -user_id $current_user_id]
	set vp_p [im_profile::member_p -profile "Freelance Managers" -user_id $current_user_id]
	set srm_p [im_profile::member_p -profile "Senior Managers" -user_id $current_user_id]
	set supervisor_p [db_0or1row chk_supr "select department_id from im_employees where employee_id=:user_id and (supervisor_id =:current_user_id or immediate_supervisor_id=:current_user_id)"]
	if {$admin_p || $hr_p || $supervisor_p || $vp_p || $srm_p } {
		set edit_p 1
	}
	set component_html ""
	set data_lists [db_list_of_lists get_emp_data "select data_type, data, to_char(data_date, 'YYYY-MM-DD') from im_employees_multiple_data where employee_id=:user_id order by data_type,data_date "]

	set promotions_data_table_body ""
	set project_type_table_body ""
	set domain_data_table_body ""
	
	set promotions_data_table_head "<table class='common_tab_css'><thead>
	<tr><th  class='table-heading' colspan=2>Promotions</th></tr>
	<tr>
	<th>Designation</th>
	<th>Start Date</th>
	</tr></thead><tbody>"


	set domain_data_table_head "<table class='common_tab_css'><thead>
	<tr><th  class='table-heading' colspan=2>Domains</th></tr>
	</thead><tbody>"


	set project_type_table_head "<table class='common_tab_css'><thead>
	<tr><th colspan=2 class='table-heading'>Project Types</th></tr>
	</thead><tbody>"
	set count_pt 1 
	set count_d 1 
	foreach data_list $data_lists {
		set temp_data_type [lindex $data_list 0]
		set temp_data [lindex $data_list 1]
		set temp_data_date [lindex $data_list 2]
		switch $temp_data_type {
			"promotion" {
				append promotions_data_table_body "<tr>
				<td>$temp_data</td>
				<td>$temp_data_date</td>
				</tr>"				
			}
			"project_type" {
				if {$count_pt == 1 } {
					append project_type_table_body "<tr>"
				}
				append project_type_table_body "<td>[im_category_from_id $temp_data]</td>"
				if {$count_pt == 2 } {
					append project_type_table_body "</tr>"
					set count_pt 0
				}
				incr count_pt
				
			}
			"domain" {
				if {$count_d == 1 } {
					append domain_data_table_body "<tr>"
				}
				append domain_data_table_body "<td>[im_category_from_id $temp_data]</td>"
				if {$count_d == 2 } {
					append domain_data_table_body "</tr>"
					set count_d 0
				}
				incr count_d
			}
		}
	}
	if { $promotions_data_table_body != ""} {
		append promotions_data_table_head "$promotions_data_table_body </tbody></table>"
	} else {
		set promotions_data_table_head ""
	}
	if { $project_type_table_body != ""} {
		if {$count_pt == 2} {
			append project_type_table_body "<td></td></tr>"
		}
		append project_type_table_head "$project_type_table_body </tbody></table>"
	} else {
		set project_type_table_head ""
	}
	if { $domain_data_table_body != ""} {
		if {$count_d == 2} {
			append domain_data_table_body "<td></td></tr>"
		}
		append domain_data_table_head "$domain_data_table_body </tbody></table>"
	} else {
		set domain_data_table_head ""
	}
	append component_html "$promotions_data_table_head"
	append component_html "$project_type_table_head"
	append component_html "$domain_data_table_head"
	if {$component_html == "" } {
		append component_html "No information found click <b>Edit</b> to add new data"
	}
	if {$edit_p } { 
	append component_html "<form action='add-employee-profile-data' method='get' >
		<input type='hidden' name='employee_id' value='$user_id'>
		<input type='submit' value='Edit'>
		</form>"
	} 
	append component_html " <style>
		.table-heading {
			text-align:center;
			background-color:#c5d1fb;
		}
		.common_tab_css {
			width:100%;
		}
		</style>"
	return $component_html
} 

ad_proc appraisal_xlplat_block { 
    { -page_title "" } 
} { 
	set user_id [ad_maybe_redirect_for_registration]
	set cust_p [im_user_is_customer_p $user_id] 
	#amit add training_feedback form tooo and evaluation form too
    if {  $page_title == "Performance Dev Form" || $page_title == "Set Form Dates" || $page_title == "Training Feedback Form" || $page_title == "Training Evaluation Form" ||$page_title == "Score Page" || $page_title == "Score" || $page_title == "Project Score Details" || $cust_p }  { 
        return ""
    } else { 
        
        set current_user_id $user_id
        set is_freelance_p [im_profile::member_p -profile "Freelance Managers" -user_id $user_id ] 
        set is_avp_p [im_profile::member_p -profile "AVP" -user_id $user_id ]
        set c_date [db_string get_date "select to_char(now(),'YYYY-MM-DD')"] 
        # set c_date "2019-03-05"
        set c_year [lindex [split $c_date "-"] 0] 
        set c_month [lindex [split $c_date "-"] 1]
        # this scan is to remove 08 octal error
        scan $c_month %d c_month
        set upper_year [expr $c_year + 1] 
        set second_last_day_employee_review ""
        set lower_year $c_year 
        set no_block 0 
        set employee_review_close "" 
        set second_last_day_employee_review "" 
        set check_flag ""
        set year "$c_year-$upper_year" 
        set lower_year_dummy [expr $c_year - 1]  

        #shivani sharma
        set appraisal_for_previous_year [db_0or1row get_info "select row_id from new_app_form_fields_data where user_id=:user_id and year='$lower_year_dummy-$c_year' and year_part='2' and ( state='closed' or state is null or state='in_review' or state='in_review_manager' or state='locked' ) limit 1"] 
        # doc_return 200 "text/plain" $appraisal_for_previous_year
        set is_appraisal_filled [db_0or1row get_appraisal_filled_details "select row_id from new_app_form_fields_data where user_id=:user_id and year='$lower_year_dummy-$c_year' and year_part='2' limit 1"] 
        
 		set appraisal_for_previous_year_filled [list]
	    set user_subordinate_list [im_subordinates_list -user_id $user_id]
	    if { $user_subordinate_list != ""  } { 
		    if {$appraisal_for_previous_year == 1 } { 
		    	foreach each_subordinate $user_subordinate_list { 
		    		set appraisal_for_previous_year_subordinate [db_0or1row get_info "select row_id from new_app_form_fields_data where user_id=:each_subordinate and year='$lower_year_dummy-$c_year' and year_part='2' and ( state='in_review' or state='locked' or state='in_review_manager') limit 1"]  

		    		# doc_return 200 "text/plain" "$lower_year_dummy-$c_year"
		    		if { $appraisal_for_previous_year == 1 } { 
		    			lappend appraisal_for_previous_year_filled $appraisal_for_previous_year_subordinate
		    		}  
		    	} 
		    	
		    	if { $appraisal_for_previous_year_filled != "" } { 
			    	if { [lsearch -all $appraisal_for_previous_year_filled 1] > -1 } { 
			    		set lower_year [expr $c_year - 1] 
			            set year "$lower_year-$c_year" 
			    	} 
			    } 
			   
		    } else { 
		    	if { $appraisal_for_previous_year == 0 && $is_appraisal_filled == 1 } { 
		            set lower_year [expr $c_year - 1] 
		            set year "$lower_year-$c_year" 
	   	 		}
		    }
	    } else { 
	    	if { $appraisal_for_previous_year == 0 && $is_appraisal_filled == 1 } { 
	            set lower_year [expr $c_year - 1] 
	            set year "$lower_year-$c_year" 
	   	 	}
	    } 


	  	set employee_join_date [employee_join_terminate_date -user_id $user_id] 
	  	set joining_date [lindex [split $employee_join_date " "] 0]
	  	set joining_year [lindex [split $joining_date "-"] 0] 
	  	set joining_month [lindex [split $joining_date "-"] 1] 
	  	scan $joining_month %d joining_month
	  	set fill_mid_year 0
	  	set fill_year_end 0
	  	set fill_next_mid_year 0  
	  	if { $lower_year == $joining_year } { 
	  		if { $joining_month >= 1 && $joining_month < 4 } { 
	  			set fill_mid_year 1 
	  			set fill_year_end 1
	  		} elseif { $joining_month >= 4 && $joining_month < 7 } { 
	  			set fill_year_end 1
	  			set fill_mid_year 0
	  		} elseif { $joining_month >= 7 && $joining_month < 10 } {
	  			set fill_year_end 1
	  			set fill_mid_year 0
	  		} elseif { $joining_month >= 10 && $joining_month < 13 } { 
	  			set fill_next_mid_year 1
	  			set fill_mid_year 0 
	  			set fill_year_end 0
	  		}
	  	} else { 
	  		set fill_mid_year 1
	  		set fill_year_end 1 
	  	}

	    set ignorelist [list]
	  	lappend ignorelist [employee_ignore_list 3]
		set trainee_list [db_list get_trainee_list "select member_id from group_member_map where group_id=241834 and member_id in (select e.user_id from im_costs c, im_employees_active e, im_repeating_costs r where c.cause_object_id= e.user_id and r.end_date >= '$c_date' and r.rep_cost_id = c.cost_id) "]
		set trainee_list [join $trainee_list ","]
		if {$trainee_list != "" } {
			append ignorelist ",$trainee_list"
		}	
		set one_month_from_joining [clock format [clock add [clock scan $joining_date -format "%Y-%m-%d"] +1 months ] -format "%Y-%m-%d" ] 
		if { $c_date < $one_month_from_joining } {
	  		append ignorelist ",$user_id"
	  	} 
		
		set final_ignore_list [split $ignorelist ","] 
		set user_available [lsearch -all $final_ignore_list $user_id]
	  	if { ( $year != "2018-2019" && $user_available == "" ) } { 

	  		set starting_year [lindex [split $year "-"] 0] 
	  		set starting_year [expr $starting_year - 1]

	  		set manager_review_year_end_date [db_string get_last_date "select close_yr_end_review_mgr from new_appraisal_form_dates where year=:starting_year" -default ""]
		    set three_weeks_later [clock format [clock add [clock scan $manager_review_year_end_date -format "%Y-%m-%d"] +21 days ] -format "%Y-%m-%d" ] 
		    set objectives_filled [db_0or1row is_filled "select user_id from new_app_form_fields_data where year=:year and year_part='1' and (state='submitted' or state='in_review' or state='in_review_manager' or state='locked' or (state='saved' and emp_comments is not null and type!='career_journey'))  and user_id=:user_id limit 1"] 
		    set record_exist [db_0or1row get_value "select user_id from new_app_form_fields_data where  year=:year and year_part='1' and user_id=:user_id limit 1"] 

		    if { $objectives_filled == 0 && $fill_mid_year == 0 && $record_exist == 1} { 
		    	set fill_mid_year 1
		    } 

		    if { $c_date > $three_weeks_later && $fill_mid_year == 1 && $objectives_filled == 0 } { 
		    	if { $objectives_filled == 0 && $fill_mid_year == 1 } { 
		    		#155293
		    		if { $user_id == 155293 } { 
		    			return
		    		} else {
		    			ad_returnredirect "/intranet/users/new_appraisal_form?flag=7&show_year=$year" 
		    		}
		    	} 
		    } else { 
	    		set starting_year [lindex [split $year "-"] 0]
	    		set manager_review_last_date [db_string get_last_date "select close_mid_yr_review_mgr from new_appraisal_form_dates where year=:starting_year" -default ""]
	    		set two_weeks_later [clock format [clock add [clock scan $manager_review_last_date -format "%Y-%m-%d"] +14 days ] -format "%Y-%m-%d" ] 
	    		set objectives_filled_year_end [db_0or1row is_filled "select user_id from new_app_form_fields_data where year=:year and year_part='2' and (state='submitted' or state='in_review' or state='in_review_manager' or state='locked' or state='sent_for_sign_off' or state='closed' or (state='saved_emp' and emp_comments is not null and type!='career_journey'))  and user_id=:user_id limit 1"]  
	    		# doc_return 200 "text/plain" "$objectives_filled_year_end $two_weeks_later"
	    		if { $c_date >= $two_weeks_later } { 
		    		if { $objectives_filled_year_end == 0 && $fill_year_end == 1 } { 
		    			#155293
		    			if { $user_id == 155293  } {
			    			return
			    		} else {
			    			ad_returnredirect "/intranet/users/new_appraisal_form?flag=8&show_year=$year" 
			    		}
		    		} 
		    	} 

		    	# else { 
		    	# 	if { $joining_year == $lower_year && $fill_year_end == 1 && $objectives_filled_year_end == 0 } { 
		    	# 		ad_returnredirect "/intranet/users/new_appraisal_form?flag=8&show_year=$year"
		    	# 	}	
		    	# }
		    }
	    }
	    #manager blocking for 2019-2020
	    #shivani sharma
       	if { $is_freelance_p == 1 || $is_avp_p == 1 } { 
       		 set employee_review_close [db_string get_last_review_date "select close_mid_yr_review_mgr from new_appraisal_form_dates where year=:lower_year" -default ""]  
	       
	        if { $employee_review_close != "" } { 
	        	set second_last_day_employee_review [clock format [clock add [clock scan $employee_review_close -format "%Y-%m-%d"] -1 days ] -format "%Y-%m-%d" ] 
	        }
       	} else { 
	        set employee_review_close [db_string get_last_review_date "select close_mid_yr_review_emp from new_appraisal_form_dates where year=:lower_year" -default ""]  
	       
	        if { $employee_review_close != "" } { 
	        	set second_last_day_employee_review [clock format [clock add [clock scan $employee_review_close -format "%Y-%m-%d"] -1 days ] -format "%Y-%m-%d" ] 
	        } 
	    }
	    set immediate_supervisor_list [db_list get_supervisor "select immediate_supervisor_id from im_employees where immediate_supervisor_id is not null"]
	    set appraisal_filled [db_0or1row get_info "select row_id from new_app_form_fields_data where user_id=:user_id and year=:year and year_part='1' and ( state='in_review' or state='locked' or state is null ) limit 1"] 
	    set appraisal_not_filled [db_0or1row get_info "select row_id from new_app_form_fields_data where user_id=:user_id and year=:year and year_part='1' limit 1"]
	    set manager_review_close [db_string get_last_review_date "select close_mid_yr_review_mgr from new_appraisal_form_dates where year=:lower_year" -default ""] 

	    if { $employee_review_close != "" } { 
	    	
	        if { $second_last_day_employee_review == $c_date || $employee_review_close == $c_date || ( $employee_review_close < $c_date && $appraisal_filled != 1 && $appraisal_not_filled != 0 ) } { 
	            set appraisal_filled [db_0or1row get_info "select row_id from new_app_form_fields_data where user_id=:user_id and year=:year and year_part='1' and ( state='in_review' or state='locked' or state='in_review_manager') limit 1"]
	            
	            set employee_join_term_date [employee_join_terminate_date -user_id $user_id] 
	            set join_date [lindex [split $employee_join_term_date " "] 0] 
	            set barrier_date "$c_year-05-01"
	            # if { $join_date >= $barrier_date && $appraisal_not_filled != 1 } { 
	            #     set no_block 1
	            # } 
	            if { $appraisal_not_filled != 1 } { 
	                set no_block 1
	            } 

	            if { $appraisal_filled == 1 || $no_block == 1 || $appraisal_not_filled == 0 } { 
	                if { [lsearch -all $immediate_supervisor_list $user_id] > -1 } { 
	                	set check_flag 1
	                } else { 
	                	return "" 
	                }
	            } else { 
	            	set current_status_part_one [db_string get_status "select state from new_app_form_fields_data where user_id=:current_user_id and year_part='1' and year=:year limit 1" -default ""] 
	            	if { $current_status_part_one == "submitted" || $current_status_part_one == "saved" } { 
	                	ad_returnredirect "/intranet/users/new_appraisal_form?flag=1&show_year=$year" 
	                }
	            }
	        } elseif { $c_date > $employee_review_close } {   
	            set employee_review_close_end_year [db_string get_last_review_date "select close_yr_end_review_emp from new_appraisal_form_dates where year=:lower_year" -default ""]  

	            set second_last_day_employee_review_end_year [clock format [clock add [clock scan $employee_review_close_end_year -format "%Y-%m-%d"] -1 days ] -format "%Y-%m-%d" ] 
	            
	            set appraisal_filled [db_0or1row get_info "select row_id from new_app_form_fields_data where user_id=:user_id and year=:year and year_part='2' and ( state='in_review' or state='locked'  or state='closed' or state='in_review_manager')  limit 1"]  
	            set appraisal_not_filled [db_0or1row get_info "select row_id from new_app_form_fields_data where user_id=:user_id and year=:year and year_part='2' limit 1"]  
	           	if { [lsearch -all $immediate_supervisor_list $user_id] > -1 } { 
	           		set check_flag 1
	           	}
	            if { $second_last_day_employee_review_end_year == $c_date || $employee_review_close_end_year == $c_date || ( $employee_review_close < $c_date && $appraisal_filled != 1 && $appraisal_not_filled != 0 ) } {  
	               
	                set employee_join_term_date [employee_join_terminate_date -user_id $user_id] 
	                set join_date [lindex [split $employee_join_term_date " "] 0] 
	                set barrier_date "$c_year-11-01"
	                # if { $join_date >= $barrier_date && $appraisal_not_filled != 1 } { 
	                #     set no_block 1
	                # } 
	                if { $appraisal_not_filled != 1 } { 
	                    set no_block 1
	                } 
	                if { $appraisal_filled == 1 || $no_block == 1 || $appraisal_not_filled == 0 } { 

	                	if { [lsearch -all $immediate_supervisor_list $user_id] > -1 } { 
	                		set check_flag 1
	                	} else { 
	                		return "" 
	                	}
	                } else {  
	                	
	                	set current_status [db_string get_status "select state from new_app_form_fields_data where user_id=:current_user_id and year_part='2' and year=:year limit 1" -default ""] 
	            		if { ( $second_last_day_employee_review_end_year == $c_date || $employee_review_close_end_year == $c_date || $c_date > $employee_review_close_end_year && $appraisal_filled != 1 ) } {  
	            			if { ( $current_status == "" || $current_status != "sent_for_sign_off" ) } { 
	                			ad_returnredirect "/intranet/users/new_appraisal_form?flag=2&show_year=$year" 
		                	} else { 
		            			ad_returnredirect "/intranet/users/new_appraisal_form?flag=5&show_year=$year" 
		            		}  
		            	} 
	                } 
	            }
	        } else { 
	        	# doc_return 200 "text/plain" "$join_date 1" 
	        	# 	ad_script_abort
	        	return ""
	        } 


	        set flag_val 1 
	        set user_subordinate_list [im_subordinates_list -user_id $user_id -flag_val $flag_val] 
	        set no_flag 0
	        set subordinate_list [list]
	        if { $user_subordinate_list != "" && $check_flag == 1 } { 
		        set manager_review_close [db_string get_last_review_date "select close_mid_yr_review_mgr from new_appraisal_form_dates where year=:lower_year" -default ""]  
		        set second_last_day_manager_review [clock format [clock add [clock scan $manager_review_close -format "%Y-%m-%d"] -1 days ] -format "%Y-%m-%d" ] 
		        set block_list [list] 
		        set left_employees ""
		        foreach each_subordinate $user_subordinate_list {  
		        	set any_manager_review_left [db_0or1row get_info "select user_id from new_app_form_fields_data where user_id=:each_subordinate and year=:year and year_part='1' and (state='locked' or state='submitted' or state='saved') limit 1"] 
		        	set appraisal_not_filled [db_0or1row get_info "select row_id from new_app_form_fields_data where user_id=:each_subordinate and year=:year and year_part='1' limit 1"] 
		        	if { $any_manager_review_left != 1 && $appraisal_not_filled != 0 } { 
		        		set block_xlplat 1
		        		lappend block_list $block_xlplat 
		        		append left_employees "$each_subordinate,"
		        	}
		        }		        
		        if { $second_last_day_manager_review == $c_date || $manager_review_close == $c_date || ( $manager_review_close < $c_date && ([lsearch -all $block_list 1] > -1 )) } {  
		        	foreach each_subordinate $user_subordinate_list {  
			            set appraisal_filled [db_0or1row get_info "select row_id from new_app_form_fields_data where user_id=:each_subordinate and year=:year and year_part='1' and (state='locked' or state='submitted' or state='saved') limit 1"] 
			             set appraisal_not_filled [db_0or1row get_info "select row_id from new_app_form_fields_data where user_id=:each_subordinate and year=:year and year_part='1' limit 1"] 
			            set employee_join_term_date [employee_join_terminate_date -user_id $each_subordinate] 
			            set join_date [lindex [split $employee_join_term_date " "] 0] 
			            set barrier_date "$c_year-05-01"
			            # if { $join_date >= $barrier_date && $appraisal_not_filled == 0 } { 
			            #     set no_block 1
			            # } 
			            # if { $appraisal_not_filled == 0 } { 
			            #     set no_block 1
			            # } 
			            if { $appraisal_filled == 1 || $appraisal_not_filled == 0 } { 
			            	set val 0 
			            	lappend subordinate_list $val
		            	} else {  		
		            		set val 1
		            		lappend subordinate_list $val 
		            	}
			        }   
			        if { [lsearch -exact $subordinate_list 1 ] > -1 } { 
			            set no_flag 1
		            } else { 
		            	set no_flag 0
		            }

					if { $no_flag == 1 } {  
		                ad_returnredirect "/intranet/users/new_appraisal_form?flag=3&remaining_employees=$left_employees&show_year=$year"
		            } else { 
		            	return ""
		            } 
		    	} elseif { $c_date > $manager_review_close } {    
		    		
		            set subordinate_list_two [list]
		            set manager_review_close_end_year [db_string get_last_review_date "select close_yr_end_review_mgr from new_appraisal_form_dates where year=:lower_year" -default ""]  
		            set second_last_day_manager_review_end_year [clock format [clock add [clock scan $manager_review_close_end_year -format "%Y-%m-%d"] -1 days ] -format "%Y-%m-%d" ] 

		            set block_list_two [list] 
			        set left_employees_two ""
			        foreach each_subordinate $user_subordinate_list {  
			        	set any_manager_review_left_two [db_0or1row get_info "select user_id from new_app_form_fields_data where user_id=:each_subordinate and year=:year and year_part='2' and (state='submitted' or state='saved' or state='saved_emp' or state='closed' or state='sent_for_sign_off') limit 1"] 

			        	set appraisal_not_filled [db_0or1row get_info "select row_id from new_app_form_fields_data where user_id=:each_subordinate and year=:year and year_part='2' limit 1"] 
			        	
			        	if { $any_manager_review_left_two != 1 && $appraisal_not_filled != 0 } { 
			        		set block_xlplat 1
			        		lappend block_list_two $block_xlplat 
			        		append left_employees_two "$each_subordinate,"
			        	} 
			        
			        	set any_manager_sign_off_left [db_0or1row get_info "select user_id from new_app_form_fields_data where user_id=:each_subordinate and year=:year and year_part='2' and (state='submitted' or state='saved' or state='saved_emp' or state='closed' or state='sent_for_sign_off') limit 1"] 
			        	 

			        	if { $any_manager_sign_off_left != 1 && $appraisal_not_filled != 0 } { 
			        		append left_employees_sign_off "$each_subordinate,"
			        	} 
			        } 

		            if { $second_last_day_manager_review_end_year == $c_date || $manager_review_close_end_year == $c_date || ( $manager_review_close_end_year < $c_date && ([lsearch -all $block_list_two 1] > -1 )) } { 
		      
		            	foreach each_subordinate $user_subordinate_list {  
			                set appraisal_filled [db_0or1row get_info "select row_id from new_app_form_fields_data where user_id=:each_subordinate and year=:year and year_part='2' and ( state='locked' or state='submitted' or state='saved_emp' or state='closed' or state='saved' or state='sent_for_sign_off') limit 1"]  
		            		
		            		set sent_for_sign_off_check [db_0or1row sent_for_sign_off_done "select state from new_app_form_fields_data where user_id=:each_subordinate and year=:year and year_part='2' and state='locked' limit 1"] 

			        		set appraisal_not_filled [db_0or1row get_info "select row_id from new_app_form_fields_data where user_id=:each_subordinate and year=:year and year_part='2' limit 1"] 

			                set employee_join_term_date [employee_join_terminate_date -user_id $each_subordinate] 
			                set join_date [lindex [split $employee_join_term_date " "] 0] 
			                set barrier_date "$c_year-11-01"
			                # if { $join_date >= $barrier_date && $appraisal_not_filled != 1 } { 
			                #     set no_block 1
			                # } 
			                # if { $appraisal_not_filled != 1 } { 
			                #     set no_block 1
			                # }  

			                if { $appraisal_filled == 1  || $appraisal_not_filled == 0 } { 
			                	set val 0 
			            		lappend subordinate_list_two $val 
			            		lappend sent_for_sign_off_track $sent_for_sign_off_check
			                } else { 
			                	set val 1 
			            		lappend subordinate_list_two $val 
			            		lappend sent_for_sign_off_track $sent_for_sign_off_check
			                }  
			            } 
			           	
			            if { [lsearch -exact $subordinate_list_two 1 ] >= 0 } { 
		                    set no_flag 1
		                } else { 
		                   set no_flag 0 
		                }  
		                # doc_return 200 "text/plain" $sent_for_sign_off_track
		                if { [lsearch -exact $sent_for_sign_off_track 1 ] >= 0  } { 
		                	set sign_off_val 1	
	                	} else { 
	                		set sign_off_val 0
	                	}

		                if { $no_flag == 1 } { 
		                	# doc_return 200 "text/plain" $left_employees_two
		                	 ad_returnredirect "/intranet/users/new_appraisal_form?show_year=$year&flag=4&remaining_employees=$left_employees_two"
		                } else { 
		                	
		                	if { $sign_off_val == 1 } { 
		                		 ad_returnredirect "/intranet/users/new_appraisal_form?show_year=$year&flag=6&left_sign_off_employees=$left_employees_sign_off"
	                		} else  { 
	                			return ""
	                		}
			            }
		            } 
		        } else { 
			        return ""
			    } 
			} 
		} else { 
			return ""
		}
    } 
}   






ad_proc -public im_filing_employees_select { 
	{ -select_name "" }
} { 
	set filing_member_list [db_list_of_lists members_filing "
	select A.user_id, A.employee_name from (select
		u.user_id,
		im_name_from_user_id(u.user_id) as employee_name
	from
		registered_users u,
		group_distinct_member_map gm
	where
		u.user_id = gm.member_id
		and gm.group_id = 155702) as A
	join 
	(select	u.user_id,
		im_name_from_user_id(u.user_id) as employee_name
	from
		registered_users u,
		group_distinct_member_map gm
	where
		u.user_id = gm.member_id
		and gm.group_id = 463) as B
	on (A.user_id = B.user_id)
	"]  
	
	set select_list "<select name='$select_name'>
	<option value=''>Please Select</option>"
	foreach each_members $filing_member_list { 
		set user_id_val [lindex $each_members 0]
		set user_name_val [lindex $each_members 1] 
		append select_list "<option value='$user_id_val'>$user_name_val</option>"
	}
	append select_list "</select>" 
	return $select_list
} 

#shivani sharma 
ad_proc -public deactivate_employee_after_termination { 

} { 
	set output ""
	set member_state "banned"
	set c_date [db_string get_date "select to_char(now(),'YYYY-MM-DD')"]
	set joining_date_list [db_list get_start_date "select rep_cost_id from im_repeating_costs where end_date<=:c_date"]  
	foreach each_joining $joining_date_list { 
		set cost_id_val [db_string get_cost_id "select cause_object_id from im_costs where cost_id=:each_joining" -default ""] 	
		set employee_active [db_0or1row active_employee "select user_id from im_employees_active where user_id=:cost_id_val"] 
		if { $employee_active == 1 } { 
			if {$cost_id_val != 33957} {
				acs_user::change_state -user_id $cost_id_val -state $member_state	
				append output "[im_name_from_user_id $cost_id_val] $cost_id_val \n"
			}
		}	
	}
	return $output
} 

ad_proc -public nine_monthly_approve_percent { 

} { 
	set c_date [db_string get_current_date "select to_char(now(),'YYYY-MM-DD')"] 
	set c_year [lindex [split $c_date "-"] 0] 
	set c_month [lindex [split $c_date "-"] 1]  
	scan $c_month %d c_month 
	set one_month_ago [clock format [clock add [clock scan $c_date -format "%Y-%m-%d"] -1 months ] -format "%Y-%m-%d" ] 
	set previous_month [lindex [split $one_month_ago "-"] 1] 
	scan $previous_month %d previous_month  
	set previous_month_start_date "$c_year-$previous_month-01" 
	set days_in_month [dt_num_days_in_month $c_year $previous_month] 
	set previous_month_end_date "$c_year-$previous_month-$days_in_month"

	set blank_status_entries [db_string get_enteries "select count(view_for) from im_appraisal_task_complete_percent where (status is null or status='Hold') and (percent_save_date>='$previous_month_start_date' and percent_save_date<='$previous_month_end_date')" -default ""] 
	set domain_url [parameter::get_from_package_key -parameter "SystemURL" -package_key "acs-kernel"]
	set mail_to "hr@ttconsultants.com" 
	set from_mail "notifications@ttconsultants.com"
	set subject "Pending Nine Monthly Approval Requests" 
	set message "Hi All" 
	append message ", <br><br>
		Pending Requests for approval of Nine Monthly Bonuses: <b>$blank_status_entries</b>
		<br>
		For approving requests, Kindly click on the link:&nbsp;&nbsp;<a href='$domain_url/intranet/payroll/referral-bonus-claim?flag=1'>Nine Monthly Bonus Approval</a><br><br>
		Regards,<br>
		Team XLPLAT." 
	set extraheaders [ns_set create]
	ns_set put $extraheaders "Content-type" "text/html"
	ns_sendmail $mail_to $from_mail $subject $message $extraheaders
} 


ad_proc -public travel_desk_basic_details { 
} {  

	set user_id_from_search "" 
	set purpose_val ""
	set invitee_val ""
	set invitation_letter ""
	set travel_start_val "" 
	set travel_end_val "" 
	set travel_phase_one "" 
	set current_user_id [ad_maybe_redirect_for_registration] 
	set office_admin_p [im_profile::member_p -profile "Office Admin" -user_id $current_user_id] 
	append travel_phase_one "<br><form name='travel_form' id='travel_form' method='post' enctype='multipart/form-data'><table style='border-collapse:collapse;width:100%' > 
	<tr>
	<td class='bold_data'>Name of The Traveller :</td>" 
	if { $office_admin_p == 1 || [im_user_is_admin_p $current_user_id] } { 
		append travel_phase_one "<td>[im_user_select -include_empty_p 1 -add_html "onchange fill_user_details(this.value) style width:relative%" -include_empty_name "" user_id_from_search $user_id_from_search]</td>" 
	} else { 
		append travel_phase_one "<td><select name='user_id_from_search' style='width:relative' onchange='fill_user_details(this.value)' style='width:relative'>
		<option value=''>Please Select</option>
		<option value='$current_user_id'>[im_name_from_user_id $current_user_id]</option>
		</select>
		</td>" 
	}
	append travel_phase_one "</tr>
	<tr>
	<td class='bold_data'>Purpose of Travel:</td>
	<td><input type='hidden' name='dummy_var' value='1'><input type='hidden' name='dummy_var_another' value='1'><input type='text' name='travel_purpose' value='' ></td>
	</tr>
	<tr>
	<td class='bold_data'>Proposer/Invitee details :</td>
	<td><input type='text' name='invitee_details' value='' id='invitee_details' ></td>
	</tr>
	" 
	append travel_phase_one "<tr id='invite_letter'><td class='bold_data'>Invitation Letter</td>
	<td><input type='file' name='invitation_letter' id='invitation_letter' accept='.doc,.pdf,.docx'></td></tr>"
	append travel_phase_one "<tr>
	<td class='bold_data'>Travel Start Date (Boarding Time)</td>
	<td><input type='text' name='travel_start_date' id='travel_start_date' value=''></td>
	</tr>
	<tr>
	<td class='bold_data'>Travel End Date  (Boarding Time) :</td>
	<td><input type='text' name='travel_end_date' id='travel_end_date' value=''></td>
	</tr>

	<tr>
	<td class='bold_data'>Travel Forex Card Number:</td>
	<td><input type='text' name='forex_card_number' value='' id='forex_card_number' onblur='show_forex_dependent()'></td>
	</tr>   
	<tr class='forex_dependent' style='display:none'>
	<td class='bold_data'>Currency:</td>
	<td><input type='text' name='currency_card' value=''></td>
	</tr>
	<tr class='forex_dependent'>
	<td class='bold_data'>Topup Amount:</td>
	<td><input type='text' name='topup_amount' value=''></td>
	</tr> 
	<tr>
	<td class='bold_data'>Currency(In Cash):</td>
	<td><input type='text' name='currency_cash' value=''></td>
	</tr> 
	<tr>
	<td class='bold_data'>Travel Type:</td>
	<td>[im_category_select -include_empty_name "Please Select" -add_html "style=width:relative onchange='travel_type_val(this.value)'" -include_empty_p 1 "Intranet Travel Category" travel_type]</td>
	</tr> 
	<tr>
	<td class='bold_data'>Aadhar Card:</td>
	<td><input type='text' name='aadhar_id' id='aadhar_card' value=''></td>
	</tr>
	<tr>
	<td class='bold_data'>PAN Card:</td>
	<td><input type='text' name='pan_number' id='pan_card' value=''></td>
	</tr>
	</table>
	</form>" 
} 

ad_proc -public im_ex_employees_list { 

} { 
	set c_date [db_string current_date "select to_char(now(),'YYYY-MM-DD')"] 
	set employee_id_indirect [db_list employee_cost_id "select rep_cost_id from im_repeating_costs where end_date<:c_date"]   
	set employee_list [list]
	foreach each_employee_cost_id $employee_id_indirect {
		set employee_id [db_string employee_id_val "select cause_object_id from im_costs where cost_id=:each_employee_cost_id" -default ""] 
		if { $employee_id != "" } { 
			set employee_name [im_name_from_user_id $employee_id]
			lappend employee_list [list $employee_id $employee_name] 
		}
	}  
	return $employee_list
}

ad_proc -public send_mail_exit { 
	{ send_data ""}
} {
	set data_list [split $send_data "~"]
	set user_list_str [lindex $data_list 0]
	set user_id_value [lindex $data_list 1]
	set flag_val [lindex $data_list 2]
	db_0or1row get "select supervisor_id,immediate_supervisor_id,ttc_code,job_title from im_employees where employee_id=:user_id_value"
	db_0or1row get "select user_resign_date,relieve_date,reason_of_leaving from xlplat_ats_resignation_data where resignation_of=:user_id_value and deleted='false'"
	set job_title_name [im_category_from_id $job_title]
	set user_list [split $user_list_str "_"]
	if { $flag_val == "request_resignation" } {
		set subject "Resignation of [im_name_from_user_id $user_id_value]"
		set message "Hi [im_name_from_user_id $supervisor_id],<br><br>This is to inform that [im_name_from_user_id $user_id_value] ($ttc_code) has resigned from the post of $job_title_name on [clock format [clock scan $user_resign_date -format "%Y-%m-%d"] -format "%d %B, %Y"] and has requested to be relieved from the services by [clock format [clock scan $relieve_date -format "%Y-%m-%d"] -format "%d %B, %Y"].<br><br>The reason of resignation stated is as follows: <br>$reason_of_leaving <br><br>Kindly <a href='https://xlplat.ttconsultants.com/py/xlplat_ats/modify_user_application/$user_id_value' target='_blank'>Approve or Change</a> the relieving date. <br> <br>Thanks & Regards,<br>TTC HR"
	} elseif { $flag_val == "date_approved" } {
		set subject "Approval of Relieving Date of [im_name_from_user_id $user_id_value]"
		set message "Hi,<br><br>Relieving date of [im_name_from_user_id $user_id_value] has been approved as [clock format [clock scan $relieve_date -format "%Y-%m-%d"] -format "%d %B, %Y"].<br><br>Thanks & Regards,<br>TTC HR"
	} elseif { $flag_val == "exit_approval" } {
		set subject "Exit Interview Form of [im_name_from_user_id $user_id_value]"
		set message "Hi,<br><br>[im_name_from_user_id $user_id_value] has filled the <a href='https://xlplat.ttconsultants.com/py/xlplat_ats/exit_employee_form/$user_id_value' target='_blank'>Exit Interview Form</a>. Kindly approve the same.<br><br>Thanks & Regards,<br>TTC HR"
	} elseif { $flag_val == "manager_clearance_approve" } {
		set subject "Clearance Form of [im_name_from_user_id $user_id_value]"
		set message "Hi [im_name_from_user_id $immediate_supervisor_id] ,<br><br>[im_name_from_user_id $user_id_value] has filled the <a href='https://xlplat.ttconsultants.com/py/xlplat_ats/employee_clearance_form/$user_id_value/MANAGER' target='_blank'>Clearance Form</a> as his last date is [clock format [clock scan $relieve_date -format "%Y-%m-%d"] -format "%d %B, %Y"]. Kindly check the same and Sign off or Send back to [im_name_from_user_id $user_id_value] for review’.<br><br>Thanks & Regards,<br>TTC HR"
	} elseif { $flag_val == "manager_clearance_mail" } {
		set subject " Employee Clearance Form of [im_name_from_user_id $user_id_value]"
		set message "Hi,<br><br>Kindly approve the Employee Clearance Form of [im_name_from_user_id $user_id_value] ($ttc_code) by [clock format [clock scan $relieve_date -format "%Y-%m-%d"] -format "%d %B, %Y"].<br><br>
		"
	}
	set message_1 $message
	set from "notifications@ttconsultants.com"
	set to [list]
	foreach user_val $user_list {
		set message $message_1
		set to_special ""
		if { $user_val == "EMPLOYEE"} {
			set to [im_email_from_user_id $user_id_value]
		} elseif { $user_val == "HR" } {
			lappend to "hr@ttconsultants.com"
			if { $flag_val == "manager_clearance_mail" } {
				set to_special "hr@ttconsultants.com"
				append message "<br><a href='https://xlplat.ttconsultants.com/py/xlplat_ats/employee_clearance_form/$user_id_value/HR' target='_blank'>Click here to fill Clearance Form</a><br><br>Thanks & Regards,<br>TTC HR"
			}
		} elseif { $user_val == "MANAGER" } {
			lappend to [im_email_from_user_id $supervisor_id]
		} elseif { $user_val == "MANAGER1" } {
			lappend to [im_email_from_user_id $immediate_supervisor_id]
			if { $flag_val == "manager_clearance_mail" } {
				set to_special [im_email_from_user_id $immediate_supervisor_id]
				append message "<br><a href='https://xlplat.ttconsultants.com/py/xlplat_ats/employee_clearance_form/$user_id_value/MANAGER' target='_blank'>Click here to fill Clearance Form</a><br><br>Thanks & Regards,<br>TTC HR"
			}
		} elseif { $user_val == "ACCOUNTS" } {
			lappend to "accounts@ttconsultants.com"
			if { $flag_val == "manager_clearance_mail" } {
				set to_special "accounts@ttconsultants.com"
				append message "<br><a href='https://xlplat.ttconsultants.com/py/xlplat_ats/employee_clearance_form/$user_id_value/ACCOUNTS' target='_blank'>Click here to fill Clearance Form</a><br><br>Thanks & Regards,<br>TTC HR"
			}
		} elseif { $user_val == "IT" } {
			lappend to "itteam@ttconsultants.com"
			if { $flag_val == "manager_clearance_mail" } {
				set to_special "itteam@ttconsultants.com"
				append message "<br><a href='https://xlplat.ttconsultants.com/py/xlplat_ats/employee_clearance_form/$user_id_value/IT' target='_blank'>Click here to fill Clearance Form</a><br><br>Thanks & Regards,<br>TTC HR"
			}
		} elseif { $user_val == "ADMIN" } {
			lappend to "ritesh.sharma@ttconsultants.com"
			if { $flag_val == "manager_clearance_mail" } {
				set to_special "ritesh.sharma@ttconsultants.com"
				append message "<br><a href='https://xlplat.ttconsultants.com/py/xlplat_ats/employee_clearance_form/$user_id_value/ADMIN' target='_blank'>Click here to fill Clearance Form</a><br><br>Thanks & Regards,<br>TTC HR"
			}
		} elseif { $user_val == "VP" } {
			set vp_id [db_string get "select get_hod_from_user_id($user_id_value)" -default ""]
			if { $vp_id != "" } {
				lappend to [im_email_from_user_id $vp_id]	
			}
		}
		if { $flag_val == "manager_clearance_mail" } {
			set extraheaders [ns_set create] 
			ns_set put $extraheaders "Content-type" "text/html"
			ns_sendmail $to_special $from $subject $message $extraheaders 
		}
		
		
	}
	if { $flag_val != "manager_clearance_mail" && $to != "" } {
		set extraheaders [ns_set create] 
		ns_set put $extraheaders "Content-type" "text/html"
		ns_sendmail [join $to ","] $from $subject $message $extraheaders 
	}
	return $message
}
ad_proc -public specific_user_access {} {
	set u_id [ad_maybe_redirect_for_registration] 
	set url_main [string trim [ns_conn url]]
	set check_permission [db_string get "select url_name from im_specific_user_permissions where user_id=:u_id limit 1" -default "not_exist"]
	if { $check_permission == "not_exist" || $check_permission == ""} {
		return ""
	} else {
		set url_name_list [list]
		set url_name_list [split $check_permission ","]
		if {$url_main in $url_name_list} {
			ad_returnredirect "/intranet/permission_denied"
		}
	}

	return ""

}
ad_proc -public sub_power_user_access {} {
	set check_sub_power [check_sub_power_user]
	if { !$check_sub_power } {
		set url_main [string trim [ns_conn url]]
		set check_url_perm [db_0or1row get "select url_name from im_work_from_home_permissions where trim(url_name)=:url_main or trim(url_name) || '.tcl'=:url_main "]
		if { $check_url_perm } {
			ad_returnredirect "/intranet/permission_denied"
		}
	}
	return ""

}
ad_proc -public check_sub_power_user {} {
	set user_id [ad_maybe_redirect_for_registration] 
	set sub_power [im_profile::member_p -profile "Sub Power User" -user_id $user_id]
	set ipaddress [im_otp_peer_ip]
	set nettype [im_otp_ip_network_type $ipaddress]
	set prev_ip_type [ad_get_cookie "configToken"]
	set cookie_value [db_string get_date "select to_char(now(),'YYYY-MM-DD')"] 
	append cookie_value $nettype
	#for storing ip type value for logging out when ip changes
	if { $prev_ip_type != [ns_sha1 $cookie_value] } {
		ad_user_logout 
		db_release_unused_handles
		ad_returnredirect [ns_conn url]
	}
	if { $sub_power } {
		if {$nettype == "public"} {
			return 0
		}
	}
	return 1

}
ad_proc access_denied_sub_power {
} {
    return "Access Denied!! Please contact HR"
}
ad_proc -public delete_backup_files {
} {
    set c_date [db_string get_date "select to_char(now() - INTERVAL '15 days' , 'DD-MM-YYYY')"]
    set dir "/web/projop/filestorage/backup"
	set all_files_delete [glob -dir $dir -nocomplain "backup-$c_date*"]
	foreach file_delete $all_files_delete {
		exec rm $file_delete
	}
	return ""
}