# /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.
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
}
# ------------------------------------------
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&[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&user_group_name=all&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 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: <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 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" } {
if { $flag_val == "manager_clearance_mail" } {
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" } {
if { $flag_val == "manager_clearance_mail" } {
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" } {
if { $flag_val == "manager_clearance_mail" } {
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" } {
if { $flag_val == "manager_clearance_mail" } {
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 ""
}