pax_global_header 0000666 0000000 0000000 00000000064 13175625756 0014533 g ustar 00root root 0000000 0000000 52 comment=b6ae311c56a2339876fee93e5e42815ab9a5fb49
intranet-csv-import-v5-0-2-4-1/ 0000775 0000000 0000000 00000000000 13175625756 0016147 5 ustar 00root root 0000000 0000000 intranet-csv-import-v5-0-2-4-1/catalog/ 0000775 0000000 0000000 00000000000 13175625756 0017561 5 ustar 00root root 0000000 0000000 intranet-csv-import-v5-0-2-4-1/catalog/intranet-csv-import.de_DE.ISO-8859-1.xml 0000664 0000000 0000000 00000001751 13175625756 0026375 0 ustar 00root root 0000000 0000000
\n$err" ad_script_abort } # Extract the header line from the file set lines [split $lines_content "\n"] if {"" == $lines} { ad_return_complaint 1 "You didn't select a file in the screen before." } set separator [im_csv_guess_separator $lines] # ad_return_complaint 1 $separator set lines_len [llength $lines] set header [lindex $lines 0] set headers [im_csv_split $header $separator] set header_len [llength $headers] set values_lol [im_csv_get_values $lines_content $separator] # Check if there are lines with less then 4 elements # fraber 160315: Exporting from Excel, the last columns may be shorter # set error [im_csv_import_check_list_of_lists $values_lol] # if {"" != $error} { ad_return_complaint 1 $error } # Take a sample of max_row rows from the file and show set max_row 10 for {set i 1} {$i <= $max_row} {incr i} { set row_$i [im_csv_split [lindex $lines $i] $separator] } # Get the list of all available fields for the object_type set object_fields [im_csv_import_object_fields -object_type $object_type] # Determine the list of parsers for the object_type set parser_pairs [im_csv_import_parsers -object_type $object_type] # --------------------------------------------------------------------- # Create and fill the multirow # --------------------------------------------------------------------- # Setup the multirow with some sample rows multirow create mapping field_name column map parser parser_args for {set i 1} {$i < $max_row} {incr i} { multirow extend mapping "row_$i" } set object_type_pairs [list "" ""] foreach field $object_fields { lappend object_type_pairs [string tolower $field] [string tolower $field] } lappend object_type_pairs "hard_coded" "Hard Coded Functionality" # -------------------------------------------------- # Main Loop # Try to guess the mapping and the parser for each field # -------------------------------------------------- set cnt 0 foreach header_name $headers { ns_log Notice "import-2: otype=$object_type, field_name=$header_name" # Column - Name of the CSV colum set column "" # Parser - convert the value from CSV values to ]po[ values set parser_sample_values [list] for {set i 1} {$i <= $max_row} {incr i} { set row_name "row_$i" set val [lindex [set $row_name] $cnt] if {"" != $val} { lappend parser_sample_values $val } } # Guess the object's field to which to map. set object_field_best_guess [im_csv_import_guess_map -object_type $object_type -field_name $header_name -sample_values $parser_sample_values ] ns_log Notice "import-2: im_csv_import_guess_map -object_type $object_type -field_name $header_name -sample_values {$parser_sample_values}" ns_log Notice "import-2: => $object_field_best_guess" set guess_parser_field_name $header_name if {"" != $object_field_best_guess} { set guess_parser_field_name $object_field_best_guess } # Guess the parser how to convert the field values set defs [im_csv_import_guess_parser -object_type $object_type -field_name $guess_parser_field_name -sample_values $parser_sample_values] ns_log Notice "import-2: im_csv_import_guess_parser -object_type $object_type -field_name $guess_parser_field_name -sample_values {$parser_sample_values}" ns_log Notice "import-2: => $defs" set default_parser [lindex $defs 0] set default_parser_args [lindex $defs 1] set override_map [lindex $defs 2] set parser [im_select parser.$cnt $parser_pairs $default_parser] set args "\n" # Mapping - Map to which object field? set default_map $object_field_best_guess if {"" != $override_map} { set default_map $override_map } set map [im_select map.$cnt $object_type_pairs $default_map] ns_log Notice "import-2: header_name=$header_name, default_map=$default_map, override_map=$override_map, map=$map" if {"hard_coded" == $default_parser} { set map [im_select map.$cnt $object_type_pairs "hard_coded"] } # ad_return_complaint 1 "'$column'" multirow append mapping $header_name $column $map $parser $args \ [lindex $row_1 $cnt] [lindex $row_2 $cnt] [lindex $row_3 $cnt] [lindex $row_4 $cnt] [lindex $row_5 $cnt] incr cnt } # Redirect to a specific page for the import switch $object_type { im_timesheet_task { set redirect_object_type "im_project" } default { set redirect_object_type $object_type } } intranet-csv-import-v5-0-2-4-1/www/import-im_company.tcl 0000664 0000000 0000000 00000031443 13175625756 0023147 0 ustar 00root root 0000000 0000000 # /packages/intranet-csv-import/www/import-im_company.tcl ad_page_contract { Starts the analysis process for the file imported @author frank.bergmann@project-open.com @param mapping_name: Should we store the current mapping in the DB for future use? @param column: Name of the CSV column @param map: Name of the ]po[ object attribute @param parser: Converter for CSV data type -> ]po[ data type } { { return_url "" } { upload_file "" } { import_filename "" } { mapping_name "" } { ns_write_p 1 } { overwrite_existing_company_attributes_p 0 } column:array map:array parser:array parser_args:array } # --------------------------------------------------------------------- # Default & Security # --------------------------------------------------------------------- set current_user_id [auth::require_login] set page_title [lang::message::lookup "" intranet-cvs-import.Upload_Objects "Upload Objects"] set context_bar [im_context_bar "" $page_title] # --------------------------------------------------------------------- # Check and open the file # --------------------------------------------------------------------- if {![file readable $import_filename]} { ad_return_complaint 1 "Unable to read the file '$import_filename'.
\n$err" ad_script_abort } # Extract the header line from the file set lines [split $lines_content "\n"] set separator [im_csv_guess_separator $lines] set lines_len [llength $lines] set header [lindex $lines 0] set header_fields [im_csv_split $header $separator] set header_len [llength $header_fields] set values_list_of_lists [im_csv_get_values $lines_content $separator] # ad_return_complaint 1 "
[array get column]" # ------------------------------------------------------------ # Get DynFields # Determine the list of actually available fields. set mapped_vars [list "''"] foreach k [array names map] { lappend mapped_vars "'$map($k)'" } set dynfield_sql " select distinct aa.attribute_name, aa.object_type, aa.table_name, w.parameters, w.widget as tcl_widget, substring(w.parameters from 'category_type \"(.*)\"') as category_type from im_dynfield_widgets w, im_dynfield_attributes a, acs_attributes aa where a.widget_name = w.widget_name and a.acs_attribute_id = aa.attribute_id and aa.object_type in ('im_company') and (also_hard_coded_p is null OR also_hard_coded_p = 'f') and -- Only overwrite DynFields specified in the mapping aa.attribute_name in ([join $mapped_vars ","]) " set attribute_names [db_list attribute_names " select distinct attribute_name from ($dynfield_sql) t order by attribute_name "] # ------------------------------------------------------------ # Render Result Header if {$ns_write_p} { ad_return_top_of_page " [im_header] [im_navbar] " } # ------------------------------------------------------------ ns_write "
[array get map]
[array get parser]
[array get parser_args]
$header_fields
$err\n" } } } err_msg]} { if {$ns_write_p} { ns_write "
$err_msg" } } } set $target_var_name $var_value } # ------------------------------------------------------- # Specific field transformations # company_name needs to be there if {"" == $company_name} { if {$ns_write_p} { ns_write "
$err_msg\n" } continue } } else { if {$ns_write_p} { ns_write "
$err_msg" } continue } # ------------------------------------------------------- # Import DynFields set company_dynfield_updates {} set task_dynfield_updates {} array unset attributes_hash array set attributes_hash {} db_foreach store_dynfiels $dynfield_sql { ns_log Notice "import-im_company: name=$attribute_name, otype=$object_type, table=$table_name" # Avoid storing attributes multipe times into the same table. # Sub-types can have the same attribute defined as the main type, so duplicate # DynField attributes are OK. set key "$attribute_name-$table_name" if {[info exists attributes_hash($key)]} { ns_log Notice "import-im_company: name=$attribute_name already exists." continue } set attributes_hash($key) $table_name lappend company_dynfield_updates "$attribute_name = :$attribute_name" } if {$ns_write_p} { ns_write "
$err_msg" } } } if {$ns_write_p} { ns_write "
\n"
ns_write "Return to Company Page\n"
}
# ------------------------------------------------------------
# Render Report Footer
if {$ns_write_p} {
ns_write [im_footer]
}
intranet-csv-import-v5-0-2-4-1/www/import-im_conf_item.tcl 0000664 0000000 0000000 00000034145 13175625756 0023446 0 ustar 00root root 0000000 0000000 # /packages/intranet-csv-import/www/import-im_conf_item.tcl
#
ad_page_contract {
Starts the analysis process for the file imported
@author frank.bergmann@project-open.com
@param mapping_name: Should we store the current mapping in the DB for future use?
@param column: Name of the CSV column
@param map: Name of the ]po[ object attribute
@param parser: Converter for CSV data type -> ]po[ data type
} {
{ return_url "" }
{ upload_file "" }
{ import_filename "" }
{ mapping_name "" }
{ ns_write_p 1 }
column:array
map:array
parser:array
parser_args:array
}
# ---------------------------------------------------------------------
# Default & Security
# ---------------------------------------------------------------------
set current_user_id [auth::require_login]
set page_title [lang::message::lookup "" intranet-cvs-import.Upload_Objects "Upload Objects"]
set context_bar [im_context_bar "" $page_title]
set admin_p [im_is_user_site_wide_or_intranet_admin $current_user_id]
if {!$admin_p} {
ad_return_complaint 1 "Only administrators have the right to import objects"
ad_script_abort
}
# ---------------------------------------------------------------------
# Check and open the file
# ---------------------------------------------------------------------
if {![file readable $import_filename]} {
ad_return_complaint 1 "Unable to read the file '$import_filename'.
Please check the file permissions or contact your system administrator.\n"
ad_script_abort
}
set encoding "utf-8"
if {[catch {
set fl [open $import_filename]
fconfigure $fl -encoding $encoding
set lines_content [read $fl]
close $fl
} err]} {
ad_return_complaint 1 "Unable to open file $import_filename:
\n$err" ad_script_abort } # Extract the header line from the file set lines [split $lines_content "\n"] set separator [im_csv_guess_separator $lines] set lines_len [llength $lines] set header [lindex $lines 0] set header_fields [im_csv_split $header $separator] set header_len [llength $header_fields] set values_list_of_lists [im_csv_get_values $lines_content $separator] # ------------------------------------------------------------ # Get DynFields # Determine the list of actually available fields. set mapped_vars [list "''"] foreach k [array names map] { lappend mapped_vars "'$map($k)'" } set dynfield_sql " select distinct aa.attribute_name, aa.object_type, aa.table_name, w.parameters, w.widget as tcl_widget, substring(w.parameters from 'category_type \"(.*)\"') as category_type from im_dynfield_widgets w, im_dynfield_attributes a, acs_attributes aa where a.widget_name = w.widget_name and a.acs_attribute_id = aa.attribute_id and aa.object_type in ('im_conf_item') and (also_hard_coded_p is null OR also_hard_coded_p = 'f') and -- Only overwrite DynFields specified in the mapping aa.attribute_name in ([join $mapped_vars ","]) " set attribute_names [db_list attribute_names " select distinct attribute_name from ($dynfield_sql) t order by attribute_name "] # ------------------------------------------------------------ # Render Result Header if {$ns_write_p} { ad_return_top_of_page " [im_header] [im_navbar] " } # ------------------------------------------------------------ set cnt 1 foreach csv_line_fields $values_list_of_lists { incr cnt if {$ns_write_p} { ns_write "
$err\n" } } set $target_varname $res } } err_msg]} { if {$ns_write_p} { ns_write "
$err_msg" } } } } incr i } if {$ns_write_p} { ns_write "
$err_msg\n" } continue } } else { if {$ns_write_p} { ns_write "
$err_msg" } continue } # Add the conf_item lead to the list of conf_item members if {"" != $conf_item_owner_id} { set role_id [im_biz_object_role_full_member] im_biz_object_add_role $conf_item_owner_id $conf_item_id $role_id } if {"" != $conf_item_project_id} { if {$ns_write_p} { ns_write "
$err_msg" } } } if {$ns_write_p} { ns_write "
\n"
ns_write "Return to Conf_Item Page\n"
}
# ------------------------------------------------------------
# Render Report Footer
if {$ns_write_p} {
ns_write [im_footer]
}
intranet-csv-import-v5-0-2-4-1/www/import-im_expense.tcl 0000775 0000000 0000000 00000043255 13175625756 0023157 0 ustar 00root root 0000000 0000000 # /packages/intranet-csv-import/www/import-im_expense.tcl
#
ad_page_contract {
Starts the analysis process for the file imported
@author frank.bergmann@project-open.com
@param mapping_name: Should we store the current mapping in the DB for future use?
@param column: Name of the CSV column
@param map: Name of the ]po[ object attribute
@param parser: Converter for CSV data type -> ]po[ data type
} {
{ return_url "" }
{ upload_file "" }
{ import_filename "" }
{ mapping_name "" }
{ ns_write_p 1 }
{ overwrite_existing_expense_item_attributes_p 0 }
column:array
map:array
parser:array
parser_args:array
}
# ---------------------------------------------------------------------
# Default & Security
# ---------------------------------------------------------------------
set current_user_id [auth::require_login]
set page_title [lang::message::lookup "" intranet-cvs-import.Upload_Objects "Upload Objects"]
set context_bar [im_context_bar "" $page_title]
set admin_p [im_is_user_site_wide_or_intranet_admin $current_user_id]
if {!$admin_p} {
ad_return_complaint 1 "Only administrators have the right to import objects"
ad_script_abort
}
set default_currency [im_parameter -package_id [im_package_cost_id] "DefaultCurrency" "" "EUR"]
# ---------------------------------------------------------------------
# Check and open the file
# ---------------------------------------------------------------------
if {![file readable $import_filename]} {
ad_return_complaint 1 "Unable to read the file '$import_filename'.
Please check the file permissions or contact your system administrator.\n"
ad_script_abort
}
set encoding "utf-8"
if {[catch {
set fl [open $import_filename]
fconfigure $fl -encoding $encoding
set lines_content [read $fl]
close $fl
} err]} {
ad_return_complaint 1 "Unable to open file $import_filename:
\n$err" ad_script_abort } # Extract the header line from the file set lines [split $lines_content "\n"] set separator [im_csv_guess_separator $lines] set lines_len [llength $lines] set header [lindex $lines 0] set header_fields [im_csv_split $header $separator] set header_len [llength $header_fields] set values_list_of_lists [im_csv_get_values $lines_content $separator] # ------------------------------------------------------------ # Get DynFields # Determine the list of actually available fields. set mapped_vars [list "''"] foreach k [array names map] { lappend mapped_vars "'$map($k)'" } set dynfield_sql " select distinct aa.attribute_name, aa.object_type, aa.table_name, w.parameters, w.widget as tcl_widget, substring(w.parameters from 'category_type \"(.*)\"') as category_type from im_dynfield_widgets w, im_dynfield_attributes a, acs_attributes aa where a.widget_name = w.widget_name and a.acs_attribute_id = aa.attribute_id and aa.object_type in ('im_invoice') and (also_hard_coded_p is null OR also_hard_coded_p = 'f') and -- Only overwrite DynFields specified in the mapping aa.attribute_name in ([join $mapped_vars ","]) " set attribute_names [db_list attribute_names " select distinct attribute_name from ($dynfield_sql) t order by attribute_name "] # ------------------------------------------------------------ # Render Result Header if {$ns_write_p} { ad_return_top_of_page " [im_header] [im_navbar] " } # ------------------------------------------------------------ set cnt 1 set old_cost_name "" foreach csv_line_fields $values_list_of_lists { incr cnt # if {$cnt > 50} { break } if {$ns_write_p} { ns_write "
$err\n" } } set $target_varname $res } } err_msg]} { if {$ns_write_p} { ns_write "
$err_msg" } } incr i } # if {$ns_write_p} { ns_write "
$err_msg\n" } continue } # Update bundle_id if expense has been part of a bundle if { "" != $bundle_id_old } { if {[catch { # Note: We have used im_costs::investment_id to store the cost_id of the expense bundle. To make sure investment_id hasn't been used otherwise, we also check the cost_type set bundle_id_new [db_string get_bundle_id "select cost_id from im_costs where investment_id = :bundle_id_old and cost_type_id = [im_cost_type_expense_bundle]" -default 0] if { 0 != $bundle_id_new } { if {$ns_write_p} { ns_write "
$err_msg\n" } } } # Write Audit Trail im_audit -object_id $cost_id -action after_create } else { if {$ns_write_p} { ns_write "
$err_msg" } continue } } # ------------------------------------------------------- # Associate financial document with projects if {"" ne $project_id} { set rel_exists_p [db_string rel_exists_p " select count(*) from acs_rels r, im_projects p, im_projects sub_p where p.project_id = :project_id and sub_p.tree_sortkey between p.tree_sortkey and tree_right(p.tree_sortkey) and r.object_id_one = sub_p.project_id and r.object_id_two = :cost_id "] if {!$rel_exists_p} { set rel_id [db_exec_plsql create_rel " select acs_rel__new ( null, -- rel_id 'relationship', -- rel_type :project_id, -- object_id_one :cost_id, -- object_id_two null, -- context_id null, -- creation_user null -- creation_ip ) "] } } # ------------------------------------------------------- # Import DynFields set cost_dynfield_updates {} set task_dynfield_updates {} array unset attributes_hash array set attributes_hash {} db_foreach store_dynfiels $dynfield_sql { ns_log Notice "import-im_expense: name=$attribute_name, otype=$object_type, table=$table_name" # Avoid storing attributes multipe times into the same table. # Sub-types can have the same attribute defined as the main type, so duplicate # DynField attributes are OK. set key "$attribute_name-$table_name" if {[info exists attributes_hash($key)]} { ns_log Notice "import-im_expense: name=$attribute_name already exists." continue } set attributes_hash($key) $table_name switch $table_name { im_costs { lappend cost_dynfield_updates "$attribute_name = :$attribute_name" } default { ad_return_complaint 1 "Dynfield Configuration Error:
$err_msg" } } } if {$ns_write_p} { ns_write "
\n"
ns_write "Return to main CVS import page\n"
}
# ------------------------------------------------------------
# Render Report Footer
if {$ns_write_p} {
ns_write [im_footer]
}
intranet-csv-import-v5-0-2-4-1/www/import-im_expense_bundle.tcl 0000775 0000000 0000000 00000033074 13175625756 0024506 0 ustar 00root root 0000000 0000000 # /packages/intranet-csv-import/www/import-im_expense_bundle.tcl
#
# ToDo:
# - provider_name not shown in expense_bundle
ad_page_contract {
Starts the analysis process for the file imported
@author frank.bergmann@project-open.com
@param mapping_name: Should we store the current mapping in the DB for future use?
@param column: Name of the CSV column
@param map: Name of the ]po[ object attribute
@param parser: Converter for CSV data type -> ]po[ data type
} {
{ return_url "" }
{ upload_file "" }
{ import_filename "" }
{ mapping_name "" }
{ ns_write_p 1 }
{ overwrite_existing_expense_bundles_p 0 }
column:array
map:array
parser:array
parser_args:array
}
# ---------------------------------------------------------------------
# Default & Security
# ---------------------------------------------------------------------
set current_user_id [auth::require_login]
set page_title [lang::message::lookup "" intranet-cvs-import.Upload_Objects "Upload Objects"]
set context_bar [im_context_bar "" $page_title]
set admin_p [im_is_user_site_wide_or_intranet_admin $current_user_id]
if {!$admin_p} {
ad_return_complaint 1 "Only administrators have the right to import objects"
ad_script_abort
}
set default_currency [im_parameter -package_id [im_package_cost_id] "DefaultCurrency" "" "EUR"]
# ---------------------------------------------------------------------
# Check and open the file
# ---------------------------------------------------------------------
if {![file readable $import_filename]} {
ad_return_complaint 1 "Unable to read the file '$import_filename'.
Please check the file permissions or contact your system administrator.\n"
ad_script_abort
}
set encoding "utf-8"
if {[catch {
set fl [open $import_filename]
fconfigure $fl -encoding $encoding
set lines_content [read $fl]
close $fl
} err]} {
ad_return_complaint 1 "Unable to open file $import_filename:
\n$err" ad_script_abort } # Extract the header line from the file set lines [split $lines_content "\n"] set separator [im_csv_guess_separator $lines] set lines_len [llength $lines] set header [lindex $lines 0] set header_fields [im_csv_split $header $separator] set header_len [llength $header_fields] set values_list_of_lists [im_csv_get_values $lines_content $separator] # ------------------------------------------------------------ # Get DynFields # Determine the list of actually available fields. set mapped_vars [list "''"] foreach k [array names map] { lappend mapped_vars "'$map($k)'" } set dynfield_sql " select distinct aa.attribute_name, aa.object_type, aa.table_name, w.parameters, w.widget as tcl_widget, substring(w.parameters from 'category_type \"(.*)\"') as category_type from im_dynfield_widgets w, im_dynfield_attributes a, acs_attributes aa where a.widget_name = w.widget_name and a.acs_attribute_id = aa.attribute_id and aa.object_type in ('im_invoice') and (also_hard_coded_p is null OR also_hard_coded_p = 'f') and -- Only overwrite DynFields specified in the mapping aa.attribute_name in ([join $mapped_vars ","]) " set attribute_names [db_list attribute_names " select distinct attribute_name from ($dynfield_sql) t order by attribute_name "] # ------------------------------------------------------------ # Render Result Header if {$ns_write_p} { ad_return_top_of_page " [im_header] [im_navbar] " } # ------------------------------------------------------------ set cnt 1 set old_cost_name "" foreach csv_line_fields $values_list_of_lists { incr cnt if {$ns_write_p} { ns_write "
$err\n
$err_msg
$err_msg\n" } continue } # Write Audit Trail im_audit -object_id $cost_id -action after_create } else { if {$ns_write_p} { ns_write "
$err_msg" } continue } } # ------------------------------------------------------- # Import DynFields set cost_dynfield_updates {} set task_dynfield_updates {} array unset attributes_hash array set attributes_hash {} db_foreach store_dynfiels $dynfield_sql { ns_log Notice "import-im_invoice: name=$attribute_name, otype=$object_type, table=$table_name" # Avoid storing attributes multipe times into the same table. # Sub-types can have the same attribute defined as the main type, so duplicate # DynField attributes are OK. set key "$attribute_name-$table_name" if {[info exists attributes_hash($key)]} { ns_log Notice "import-im_invoice: name=$attribute_name already exists." continue } set attributes_hash($key) $table_name switch $table_name { im_costs { lappend cost_dynfield_updates "$attribute_name = :$attribute_name" } default { ad_return_complaint 1 "Dynfield Configuration Error:
$err_msg" } } } if {$ns_write_p} { ns_write "
\n"
ns_write "Return to CSV Import page\n"
}
# ------------------------------------------------------------
# Render Report Footer
if {$ns_write_p} {
ns_write [im_footer]
}
intranet-csv-import-v5-0-2-4-1/www/import-im_hour.tcl 0000775 0000000 0000000 00000042574 13175625756 0022470 0 ustar 00root root 0000000 0000000 # /packages/intranet-csv-import/www/import-im_hours.tcl
#
ad_page_contract {
@author frank.bergmann@project-open.com
@author klaus.hofeditz@project-open.com
@param mapping_name: Should we store the current mapping in the DB for future use?
@param column: Name of the CSV column
@param map: Name of the ]po[ object attribute
@param parser: Converter for CSV data type -> ]po[ data type
} {
{ return_url "" }
{ upload_file "" }
{ import_filename "" }
{ mapping_name "" }
{ ns_write_p "" }
{ write_log_p 1 }
{ merge_p:optional }
{ test_run_p 0 }
{ output_device_log "screen"}
column:array
map:array
parser:array
parser_args:array
}
ad_proc -private im_write_log {
output_device_log
write_log_p
msg
} {
Writes log message to either screen or textfile
} {
if { !$write_log_p } { return }
set path_tmp [parameter::get -package_id [apm_package_id_from_key intranet-filestorage] -parameter "TmpPathUnix" -default 60]
if { "screen" == $output_device_log } {
ns_write $msg
} else {
if {[catch {
set fp [open "$path_tmp/import-im-hour-log.txt" a+]
puts $fp $msg
close $fp
} err_msg]} {
global errorInfo
ns_log Error "Error writing to log file: $errorInfo"
}
}
}
# ---------------------------------------------------------------------
# Compatibility
# ---------------------------------------------------------------------
if { "" ne $ns_write_p } { set write_log_p $ns_write_p }
# ---------------------------------------------------------------------
# Default & Security
# ---------------------------------------------------------------------
set current_user_id [auth::require_login]
set page_title [lang::message::lookup "" intranet-cvs-import.Upload_Objects "Upload Objects"]
set context_bar [im_context_bar "" $page_title]
set sync_cost_item_immediately_p [parameter::get_from_package_key -package_key intranet-timesheet2 -parameter "SyncHoursImmediatelyAfterEntryP" -default 1]
set path_tmp "[parameter::get -package_id [apm_package_id_from_key intranet-filestorage] -parameter "TmpPathUnix" -default 60]/import-im-hour-log.txt"
# ---------------------------------------------------------------------
# Remove old log file
# ---------------------------------------------------------------------
if {[catch {
file delete $path_tmp
} err_msg]} {
global errorInfo
ns_log Error "Error deleting old logfile: " $errorInfo
}
# ---------------------------------------------------------------------
# Check and open the file
# ---------------------------------------------------------------------
if {![file readable $import_filename]} {
ad_return_complaint 1 "Unable to read the file '$import_filename'.
Please check the file permissions or contact your system administrator.\n"
ad_script_abort
}
set encoding "utf-8"
if {[catch {
set fl [open $import_filename]
fconfigure $fl -encoding $encoding
set lines_content [read $fl]
close $fl
} err]} {
ad_return_complaint 1 "Unable to open file $import_filename:
\n$err" ad_script_abort } # Extract the header line from the file set lines [split $lines_content "\n"] set separator [im_csv_guess_separator $lines] set lines_len [llength $lines] set header [lindex $lines 0] set header_fields [im_csv_split $header $separator] set header_len [llength $header_fields] set values_list_of_lists [im_csv_get_values $lines_content $separator] # ad_return_complaint 1 "
[array get column]" # ------------------------------------------------------------ # Render Result Header if {"screen" == $output_device_log && $write_log_p } { ad_return_top_of_page " [im_header] [im_navbar] " } # ------------------------------------------------------------ set cnt 1 set missing_project_list [list] if { $test_run_p } { im_write_log $output_device_log $write_log_p "TEST RUN - Nothing will be written to the database" } foreach csv_line_fields $values_list_of_lists { incr cnt im_write_log $output_device_log $write_log_p "
[array get map]
[array get parser]
[array get parser_args]
$header_fields
$err
$err_msg
\n" im_write_log $output_device_log $write_log_p "
List of projects not found:
$missing_project_list
\n$err" ad_script_abort } # Extract the header line from the file set lines [split $lines_content "\n"] set separator [im_csv_guess_separator $lines] set lines_len [llength $lines] set header [lindex $lines 0] set header_fields [im_csv_split $header $separator] set header_len [llength $header_fields] set values_list_of_lists [im_csv_get_values $lines_content $separator] # ------------------------------------------------------------ # Get DynFields # Determine the list of actually available fields. set mapped_vars [list "''"] foreach k [array names map] { lappend mapped_vars "'$map($k)'" } set dynfield_sql " select distinct aa.attribute_name, aa.object_type, aa.table_name, w.parameters, w.widget as tcl_widget, substring(w.parameters from 'category_type \"(.*)\"') as category_type from im_dynfield_widgets w, im_dynfield_attributes a, acs_attributes aa where a.widget_name = w.widget_name and a.acs_attribute_id = aa.attribute_id and aa.object_type in ('im_invoice') and (also_hard_coded_p is null OR also_hard_coded_p = 'f') and -- Only overwrite DynFields specified in the mapping aa.attribute_name in ([join $mapped_vars ","]) " set attribute_names [db_list attribute_names " select distinct attribute_name from ($dynfield_sql) t order by attribute_name "] # ------------------------------------------------------------ # Render Result Header if {$ns_write_p} { ad_return_top_of_page " [im_header] [im_navbar] " ns_write "
$err\n" } } set $target_varname $res } } err_msg]} { if {$ns_write_p} { ns_write "
$err_msg" } } incr i } if {$ns_write_p} { ns_write "
$err_msg\n" } continue } # Write Audit Trail im_audit -object_id $cost_id -action after_create } else { if {$ns_write_p} { ns_write "
$err_msg" } continue } # ------------------------------------------------------- # Associate financial document with projects # if {"" ne $project_id} { set rel_exists_p [db_string rel_exists_p " select count(*) from acs_rels r, im_projects p, im_projects sub_p where p.project_id = :project_id and sub_p.tree_sortkey between p.tree_sortkey and tree_right(p.tree_sortkey) and r.object_id_one = sub_p.project_id and r.object_id_two = :cost_id "] if {!$rel_exists_p} { set rel_id [db_exec_plsql create_rel " select acs_rel__new ( null, -- rel_id 'relationship', -- rel_type :project_id, -- object_id_one :cost_id, -- object_id_two null, -- context_id null, -- creation_user null -- creation_ip ) "] } } # ------------------------------------------------------- # Import DynFields set cost_dynfield_updates {} set task_dynfield_updates {} array unset attributes_hash array set attributes_hash {} db_foreach store_dynfiels $dynfield_sql { ns_log Notice "import-im_invoice: name=$attribute_name, otype=$object_type, table=$table_name" # Avoid storing attributes multipe times into the same table. # Sub-types can have the same attribute defined as the main type, so duplicate # DynField attributes are OK. set key "$attribute_name-$table_name" if {[info exists attributes_hash($key)]} { ns_log Notice "import-im_invoice: name=$attribute_name already exists." continue } set attributes_hash($key) $table_name switch $table_name { im_costs { lappend cost_dynfield_updates "$attribute_name = :$attribute_name" } default { ad_return_complaint 1 "Dynfield Configuration Error:
$err_msg" } } } if {$ns_write_p} { ns_write "
\n"
ns_write "Return to Cost Page\n"
}
# ------------------------------------------------------------
# Render Report Footer
if {$ns_write_p} {
ns_write [im_footer]
}
intranet-csv-import-v5-0-2-4-1/www/import-im_membership.tcl 0000775 0000000 0000000 00000023700 13175625756 0023634 0 ustar 00root root 0000000 0000000 # /packages/intranet-csv-import/www/import-im_membership.tcl
#
ad_page_contract {
Starts the analysis process for the file imported
@author frank.bergmann@project-open.com
@param mapping_name: Should we store the current mapping in the DB for future use?
@param column: Name of the CSV column
@param map: Name of the ]po[ object attribute
@param parser: Converter for CSV data type -> ]po[ data type
} {
{ return_url "" }
{ upload_file "" }
{ import_filename "" }
{ mapping_name "" }
{ ns_write_p 1 }
column:array
map:array
parser:array
parser_args:array
}
# ---------------------------------------------------------------------
# Default & Security
# ---------------------------------------------------------------------
set current_user_id [auth::require_login]
set page_title [lang::message::lookup "" intranet-cvs-import.Upload_Objects "Upload Objects"]
set context_bar [im_context_bar "" $page_title]
set admin_p [im_is_user_site_wide_or_intranet_admin $current_user_id]
if {!$admin_p} {
ad_return_complaint 1 "Only administrators have the right to import objects"
ad_script_abort
}
# ---------------------------------------------------------------------
# Check and open the file
# ---------------------------------------------------------------------
if {![file readable $import_filename]} {
ad_return_complaint 1 "Unable to read the file '$import_filename'.
Please check the file permissions or contact your system administrator.\n"
ad_script_abort
}
set encoding "utf-8"
if {[catch {
set fl [open $import_filename]
fconfigure $fl -encoding $encoding
set lines_content [read $fl]
close $fl
} err]} {
ad_return_complaint 1 "Unable to open file $import_filename:
\n$err" ad_script_abort } # Extract the header line from the file set lines [split $lines_content "\n"] set separator [im_csv_guess_separator $lines] set lines_len [llength $lines] set header [lindex $lines 0] set header_fields [im_csv_split $header $separator] set header_len [llength $header_fields] set values_list_of_lists [im_csv_get_values $lines_content $separator] # ------------------------------------------------------------ # Render Result Header if {$ns_write_p} { ad_return_top_of_page " [im_header] [im_navbar] " } # ------------------------------------------------------------ set cnt 1 foreach csv_line_fields $values_list_of_lists { incr cnt if {$ns_write_p} { ns_write "
$err\n" } } set $target_varname $res } } err_msg]} { if {$ns_write_p} { ns_write "
$err_msg" } } } } incr i ns_log Notice "import-im_membership: -------------------------------------------" } # ------------------------------------------------------- # Specific field transformations # either project_id, project_nr or project_path need to be provided if {""eq $project_id && "" eq $project_nr_path && "" eq $project_nr } { if {$ns_write_p} { ns_write "
\n"
ns_write "Return to Project Page\n"
}
# ------------------------------------------------------------
# Render Report Footer
if {$ns_write_p} {
ns_write [im_footer]
}
intranet-csv-import-v5-0-2-4-1/www/import-im_project.tcl 0000664 0000000 0000000 00000061656 13175625756 0023160 0 ustar 00root root 0000000 0000000 # /packages/intranet-csv-import/www/import-im_project.tcl
#
ad_page_contract {
Starts the analysis process for the file imported
@author frank.bergmann@project-open.com
@param mapping_name: Should we store the current mapping in the DB for future use?
@param column: Name of the CSV column
@param map: Name of the ]po[ object attribute
@param parser: Converter for CSV data type -> ]po[ data type
} {
{ return_url "" }
{ upload_file "" }
{ import_filename "" }
{ mapping_name "" }
{ ns_write_p 1 }
column:array
map:array
parser:array
parser_args:array
}
# ---------------------------------------------------------------------
# Default & Security
# ---------------------------------------------------------------------
set current_user_id [auth::require_login]
set page_title [lang::message::lookup "" intranet-cvs-import.Upload_Objects "Upload Objects"]
set context_bar [im_context_bar "" $page_title]
set admin_p [im_is_user_site_wide_or_intranet_admin $current_user_id]
if {!$admin_p} {
ad_return_complaint 1 "Only administrators have the right to import objects"
ad_script_abort
}
# By default, how many hours do an employee work per day?
set hours_per_day [parameter::get_from_package_key -package_key "intranet-timesheet2" -parameter "TimesheetHoursPerDay" -default 8]
# ---------------------------------------------------------------------
# Check and open the file
# ---------------------------------------------------------------------
if {![file readable $import_filename]} {
ad_return_complaint 1 "Unable to read the file '$import_filename'.
Please check the file permissions or contact your system administrator.\n"
ad_script_abort
}
set encoding "utf-8"
if {[catch {
set fl [open $import_filename]
fconfigure $fl -encoding $encoding
set lines_content [read $fl]
close $fl
} err]} {
ad_return_complaint 1 "Unable to open file $import_filename:
\n$err" ad_script_abort } # Extract the header line from the file set lines [split $lines_content "\n"] set separator [im_csv_guess_separator $lines] set lines_len [llength $lines] set header [lindex $lines 0] set header_fields [im_csv_split $header $separator] set header_len [llength $header_fields] set values_list_of_lists [im_csv_get_values $lines_content $separator] # --------------------------------------------------------------------- # Determine header_var_name # --------------------------------------------------------------------- set mapped_vars [list "''"] set debug_rows "
$err\n" } } set $target_varname $res } } err_msg]} { if {$ns_write_p} { ns_write "
$err_msg" } } ns_log Notice "import-im_project: " ns_log Notice "import-im_project: -------------------------------------------" ns_log Notice "import-im_project: " } # ------------------------------------------------------- # Specific field transformations # ------------------------------------------------------- # project_name needs to be there if {"" == $project_name} { if {$ns_write_p} { ns_write "
$err\n" } continue } else { set parent_id $parent0_id if {$ns_write_p} { ns_write "
$err\n" } continue } if {"" ne $parent1_id} { set parent_id $parent1_id if {$ns_write_p} { ns_write "
$err\n" } continue } if {"" ne $parent2_id} { set parent_id $parent2_id if {$ns_write_p} { ns_write "
$err\n" } continue } if {"" ne $parent3_id} { set parent_id $parent3_id if {$ns_write_p} { ns_write "
\n$err_msg" } continue } set parent_id [lindex $result 0] set err [lindex $result 1] if {"" != $err} { if {$ns_write_p} { ns_write "
$err\n" } continue } } # Status is a required field if {"" eq $project_status_id} { set project_status_id [im_id_from_category $project_status "Intranet Project Status"] if {"" eq $project_status_id} { if {$ns_write_p} { ns_write "
$err_msg\n" } continue } } else { if {$ns_write_p} { ns_write "
$err_msg" } continue } # ------------------------------------------------------- # Make sure there is an entry in im_timesheet_tasks if the project is of type task if {$project_type_id == [im_project_type_task]} { set material_id "" if {"" != $material} { set material_id [db_string material_lookup " select min(material_id) from im_materials where ( lower(trim(material_nr)) = lower(trim(:material)) OR lower(trim(material_name)) = lower(trim(:material)) ) " -default ""] if {"" == $material_id} { if {$ns_write_p} { ns_write "
$err_msg" } } } if {$ns_write_p} { ns_write "
$err_msg" } } } if {$ns_write_p} { ns_write "
\n"
ns_write "Return to Project Page\n"
}
# ------------------------------------------------------------
# Render Report Footer
if {$ns_write_p} {
ns_write [im_footer]
}
intranet-csv-import-v5-0-2-4-1/www/import-im_risk.tcl 0000664 0000000 0000000 00000025371 13175625756 0022454 0 ustar 00root root 0000000 0000000 # /packages/intranet-csv-import/www/import-im_risk.tcl
#
ad_page_contract {
Starts the analysis process for the file imported
@author frank.bergmann@project-open.com
@param mapping_name: Should we store the current mapping in the DB for future use?
@param column: Name of the CSV column
@param map: Name of the ]po[ object attribute
@param parser: Converter for CSV data type -> ]po[ data type
} {
{ return_url "" }
{ upload_file "" }
{ import_filename "" }
{ mapping_name "" }
{ ns_write_p 1 }
column:array
map:array
parser:array
parser_args:array
}
# ---------------------------------------------------------------------
# Default & Security
# ---------------------------------------------------------------------
set current_user_id [auth::require_login]
set page_title [lang::message::lookup "" intranet-cvs-import.Upload_Objects "Upload Objects"]
set context_bar [im_context_bar "" $page_title]
# ---------------------------------------------------------------------
# Check and open the file
# ---------------------------------------------------------------------
if {![file readable $import_filename]} {
ad_return_complaint 1 "Unable to read the file '$import_filename'.
Please check the file permissions or contact your system administrator.\n"
ad_script_abort
}
set encoding "utf-8"
if {[catch {
set fl [open $import_filename]
fconfigure $fl -encoding $encoding
set lines_content [read $fl]
close $fl
} err]} {
ad_return_complaint 1 "Unable to open file $import_filename:
\n$err" ad_script_abort } # Extract the header line from the file set lines [split $lines_content "\n"] set separator [im_csv_guess_separator $lines] set lines_len [llength $lines] set header [lindex $lines 0] set header_fields [im_csv_split $header $separator] set header_len [llength $header_fields] set values_list_of_lists [im_csv_get_values $lines_content $separator] # ------------------------------------------------------------ # Get DynFields # Determine the list of actually available fields. set mapped_vars [list "''"] foreach k [array names map] { lappend mapped_vars "'$map($k)'" } set dynfield_sql " select distinct aa.attribute_name, aa.object_type, aa.table_name, w.parameters, w.widget as tcl_widget, substring(w.parameters from 'category_type \"(.*)\"') as category_type from im_dynfield_widgets w, im_dynfield_attributes a, acs_attributes aa where a.widget_name = w.widget_name and a.acs_attribute_id = aa.attribute_id and aa.object_type in ('im_risk') and (also_hard_coded_p is null OR also_hard_coded_p = 'f') and -- Only overwrite DynFields specified in the mapping aa.attribute_name in ([join $mapped_vars ","]) " set attribute_names [db_list attribute_names " select distinct attribute_name from ($dynfield_sql) t order by attribute_name "] # ------------------------------------------------------------ # Render Result Header if {$ns_write_p} { ad_return_top_of_page " [im_header] [im_navbar] " } # ------------------------------------------------------------ set cnt 1 foreach csv_line_fields $values_list_of_lists { incr cnt if {$ns_write_p} { ns_write "
$err\n" } } } err_msg]} { if {$ns_write_p} { ns_write "
$err_msg" } } } set $target_var_name $var_value } # ------------------------------------------------------- # Specific field transformations # risk_name needs to be there if {"" == $risk_name} { if {$ns_write_p} { ns_write "
$err_msg\n" } continue } } else { if {$ns_write_p} { ns_write "
$err_msg" } continue } # ------------------------------------------------------- # Import DynFields set risk_dynfield_updates {} set task_dynfield_updates {} array unset attributes_hash array set attributes_hash {} db_foreach store_dynfiels $dynfield_sql { ns_log Notice "import-im_risk: name=$attribute_name, otype=$object_type, table=$table_name" # Avoid storing attributes multipe times into the same table. # Sub-types can have the same attribute defined as the main type, so duplicate # DynField attributes are OK. set key "$attribute_name-$table_name" if {[info exists attributes_hash($key)]} { ns_log Notice "import-im_risk: name=$attribute_name already exists." continue } set attributes_hash($key) $table_name lappend risk_dynfield_updates "$attribute_name = :$attribute_name" } if {$ns_write_p} { ns_write "
$err_msg" } } } if {$ns_write_p} { ns_write "
\n"
ns_write "Return to Risk Page\n"
}
# ------------------------------------------------------------
# Render Report Footer
if {$ns_write_p} {
ns_write [im_footer]
}
intranet-csv-import-v5-0-2-4-1/www/import-im_ticket.tcl 0000664 0000000 0000000 00000040557 13175625756 0022772 0 ustar 00root root 0000000 0000000 # /packages/intranet-csv-import/www/import-im_ticket.tcl
#
ad_page_contract {
Starts the analysis process for the file imported
@author frank.bergmann@project-open.com
@param mapping_name: Should we store the current mapping in the DB for future use?
@param column: Name of the CSV column
@param map: Name of the ]po[ object attribute
@param parser: Converter for CSV data type -> ]po[ data type
} {
{ return_url "" }
{ upload_file "" }
{ import_filename "" }
{ mapping_name "" }
{ ns_write_p 1 }
column:array
map:array
parser:array
parser_args:array
}
# ---------------------------------------------------------------------
# Default & Security
# ---------------------------------------------------------------------
set current_user_id [auth::require_login]
set page_title [lang::message::lookup "" intranet-cvs-import.Upload_Objects "Upload Objects"]
set context_bar [im_context_bar "" $page_title]
set admin_p [im_is_user_site_wide_or_intranet_admin $current_user_id]
if {!$admin_p} {
ad_return_complaint 1 "Only administrators have the right to import objects"
ad_script_abort
}
# ---------------------------------------------------------------------
# Check and open the file
# ---------------------------------------------------------------------
if {![file readable $import_filename]} {
ad_return_complaint 1 "Unable to read the file '$import_filename'.
Please check the file permissions or contact your system administrator.\n"
ad_script_abort
}
set encoding "utf-8"
if {[catch {
set fl [open $import_filename]
fconfigure $fl -encoding $encoding
set lines_content [read $fl]
close $fl
} err]} {
ad_return_complaint 1 "Unable to open file $import_filename:
\n$err" ad_script_abort } # Extract the header line from the file set lines [split $lines_content "\n"] set separator [im_csv_guess_separator $lines] set lines_len [llength $lines] set header [lindex $lines 0] set header_fields [im_csv_split $header $separator] set header_len [llength $header_fields] set values_list_of_lists [im_csv_get_values $lines_content $separator] # ------------------------------------------------------------ # Get DynFields # Determine the list of actually available fields. set mapped_vars [list "''"] foreach k [array names map] { lappend mapped_vars "'$map($k)'" } set dynfield_sql " select distinct aa.attribute_name, aa.object_type, aa.table_name, w.parameters, w.widget as tcl_widget, substring(w.parameters from 'category_type \"(.*)\"') as category_type from im_dynfield_widgets w, im_dynfield_attributes a, acs_attributes aa where a.widget_name = w.widget_name and a.acs_attribute_id = aa.attribute_id and aa.object_type in ('im_ticket') and (also_hard_coded_p is null OR also_hard_coded_p = 'f') and -- Only overwrite DynFields specified in the mapping aa.attribute_name in ([join $mapped_vars ","]) " set attribute_names [db_list attribute_names " select distinct attribute_name from ($dynfield_sql) t order by attribute_name "] # ------------------------------------------------------------ # Render Result Header if {$ns_write_p} { ad_return_top_of_page " [im_header] [im_navbar] " } # ------------------------------------------------------------ set cnt 1 foreach csv_line_fields $values_list_of_lists { incr cnt if {$ns_write_p} { ns_write "
$err\n" } } set $target_varname $res } } err_msg]} { if {$ns_write_p} { ns_write "
$err_msg" } } } } incr i ns_log Notice "import-im_ticket: -------------------------------------------" } # ------------------------------------------------------- # Specific field transformations # ticket_name needs to be there if {"" eq $ticket_name} { if {$ns_write_p} { ns_write "
\n$err_msg" } continue } # ad_return_complaint 1 "parent_id=$parent_id, result=$result, parent_nrs=$parent_nrs" set parent_id [lindex $result 0] set err [lindex $result 1] if {"" != $err} { if {$ns_write_p} { ns_write "
$err\n" } continue } } if {"" eq $parent_id} { if {$ns_write_p} { ns_write "
$err_msg\n" } continue } } else { if {$ns_write_p} { ns_write "
$err_msg" } continue } # Add the ticket assignee to the list of ticket members if {"" != $ticket_assignee_id} { set role_id [im_biz_object_role_project_manager] im_biz_object_add_role $ticket_assignee_id $ticket_id $role_id } if {"" != $ticket_customer_contact_id} { set role_id [im_biz_object_role_full_member] im_biz_object_add_role $ticket_customer_contact_id $ticket_id $role_id } # ------------------------------------------------------- # Import DynFields set ticket_dynfield_updates {} set project_dynfield_updates {} array unset attributes_hash array set attributes_hash {} db_foreach store_dynfiels $dynfield_sql { ns_log Notice "import-im_ticket: name=$attribute_name, otype=$object_type, table=$table_name" # Avoid storing attributes multipe times into the same table. # Sub-types can have the same attribute defined as the main type, so duplicate # DynField attributes are OK. set key "$attribute_name-$table_name" if {[info exists attributes_hash($key)]} { ns_log Notice "import-im_ticket: name=$attribute_name already exists." continue } set attributes_hash($key) $table_name switch $table_name { im_tickets { lappend ticket_dynfield_updates "$attribute_name = :$attribute_name" } im_projects { lappend project_dynfield_updates "$attribute_name = :$attribute_name" } default { ad_return_complaint 1 "Dynfield Configuration Error:
$err_msg" } } } if {$ns_write_p} { ns_write "
$err_msg" } } } if {$ns_write_p} { ns_write "
\n"
ns_write "Return to Ticket Page\n"
}
# ------------------------------------------------------------
# Render Report Footer
if {$ns_write_p} {
ns_write [im_footer]
}
intranet-csv-import-v5-0-2-4-1/www/import-person.tcl 0000775 0000000 0000000 00000050105 13175625756 0022321 0 ustar 00root root 0000000 0000000 # /packages/intranet-csv-import/www/import-person.tcl
ad_page_contract {
Starts the analysis process for the file imported
@author frank.bergmann@project-open.com
@param mapping_name: Should we store the current mapping in the DB for future use?
@param column: Name of the CSV column
@param map: Name of the ]po[ object attribute
@param parser: Converter for CSV data type -> ]po[ data type
} {
{ return_url "" }
{ upload_file "" }
{ import_filename "" }
{ mapping_name "" }
{ ns_write_p 1 }
{ overwrite_existing_user_attributes_p "0" }
column:array
map:array
parser:array
parser_args:array
}
# ---------------------------------------------------------------------
# ToDo:
# ---------------------------------------------------------------------
#
#
# ---------------------------------------------------------------------
# Default & Security
# ---------------------------------------------------------------------
set current_user_id [auth::require_login]
set page_title [lang::message::lookup "" intranet-cvs-import.Upload_Objects "Upload Objects"]
set context_bar [im_context_bar "" $page_title]
# ---------------------------------------------------------------------
# Check and open the file
# ---------------------------------------------------------------------
if {![file readable $import_filename]} {
ad_return_complaint 1 "Unable to read the file '$import_filename'.
Please check the file permissions or contact your system administrator.\n"
ad_script_abort
}
set encoding "utf-8"
if {[catch {
set fl [open $import_filename]
fconfigure $fl -encoding $encoding
set lines_content [read $fl]
close $fl
} err]} {
ad_return_complaint 1 "Unable to open file $import_filename:
\n$err" ad_script_abort } # Extract the header line from the file set lines [split $lines_content "\n"] set separator [im_csv_guess_separator $lines] set lines_len [llength $lines] set header [lindex $lines 0] set header_fields [im_csv_split $header $separator] set header_len [llength $header_fields] set values_list_of_lists [im_csv_get_values $lines_content $separator] # ad_return_complaint 1 "
[array get column]" # ------------------------------------------------------------ # Get DynFields # Determine the list of actually available fields. set mapped_vars [list "''"] foreach k [array names map] { lappend mapped_vars "'$map($k)'" } set dynfield_sql " select distinct aa.attribute_name, aa.object_type, aa.table_name, w.parameters, w.widget as tcl_widget, substring(w.parameters from 'category_type \"(.*)\"') as category_type from im_dynfield_widgets w, im_dynfield_attributes a, acs_attributes aa where a.widget_name = w.widget_name and a.acs_attribute_id = aa.attribute_id and aa.object_type in ('person') and (also_hard_coded_p is null OR also_hard_coded_p = 'f') and -- Only overwrite DynFields specified in the mapping aa.attribute_name in ([join $mapped_vars ","]) " set attribute_names [db_list attribute_names " select distinct attribute_name from ($dynfield_sql) t order by attribute_name "] # ------------------------------------------------------------ # Render Result Header if {$ns_write_p} { ad_return_top_of_page " [im_header] [im_navbar] " ns_write "
[array get map]
[array get parser]
[array get parser_args]
$header_fields
$err\n" } } } err_msg]} { if {$ns_write_p} { ns_write "
$err_msg" } } } set $target_var_name $var_value } # ------------------------------------------------------- # Specific field transformations # username if {"" == $username} { set username $email} # email if {"" == $email} { if {$ns_write_p} { ns_write "
$err_msg\n" } continue } if {$ns_write_p} { ns_write "
$err_msg
$err_msg" } } } if {$ns_write_p} { ns_write "
\n"
ns_write "Return\n"
}
# ------------------------------------------------------------
# Render Report Footer
if {$ns_write_p} {
ns_write [im_footer]
}
intranet-csv-import-v5-0-2-4-1/www/import-rels.tcl 0000775 0000000 0000000 00000015634 13175625756 0021770 0 ustar 00root root 0000000 0000000 # /packages/intranet-csv-import/www/import-rels.tcl
#
ad_page_contract {
Starts the analysis process for the file imported
@author frank.bergmann@project-open.com
@param mapping_name: Should we store the current mapping in the DB for future use?
@param column: Name of the CSV column
@param map: Name of the ]po[ object attribute
@param parser: Converter for CSV data type -> ]po[ data type
} {
{ return_url "" }
{ upload_file "" }
{ import_filename "" }
{ mapping_name "" }
{ ns_write_p 1 }
column:array
map:array
parser:array
parser_args:array
}
# ---------------------------------------------------------------------
# Default & Security
# ---------------------------------------------------------------------
set current_user_id [auth::require_login]
set page_title [lang::message::lookup "" intranet-cvs-import.Upload_Objects "Upload Objects"]
set context_bar [im_context_bar "" $page_title]
set admin_p [im_is_user_site_wide_or_intranet_admin $current_user_id]
if {!$admin_p} {
ad_return_complaint 1 "Only administrators have the right to import objects"
ad_script_abort
}
# ---------------------------------------------------------------------
# Check and open the file
# ---------------------------------------------------------------------
if {![file readable $import_filename]} {
ad_return_complaint 1 "Unable to read the file '$import_filename'.
Please check the file permissions or contact your system administrator.\n"
ad_script_abort
}
set encoding "utf-8"
if {[catch {
set fl [open $import_filename]
fconfigure $fl -encoding $encoding
set lines_content [read $fl]
close $fl
} err]} {
ad_return_complaint 1 "Unable to open file $import_filename:
\n$err" ad_script_abort } # Extract the header line from the file set lines [split $lines_content "\n"] set separator [im_csv_guess_separator $lines] set lines_len [llength $lines] set header [lindex $lines 0] set header_fields [im_csv_split $header $separator] set header_len [llength $header_fields] set values_list_of_lists [im_csv_get_values $lines_content $separator] # ------------------------------------------------------------ # Get DynFields # Determine the list of actually available fields. set mapped_vars [list "''"] foreach k [array names map] { lappend mapped_vars "'$map($k)'" } # ------------------------------------------------------------ # Render Result Header if {$ns_write_p} { ad_return_top_of_page " [im_header] [im_navbar] " ns_write "
$err\n" } } set $target_varname $res } } err_msg]} { if {$ns_write_p} { ns_write "
$err_msg" } } } } incr i ns_log Notice "import-rels: -------------------------------------------" } # ------------------------------------------------------- # Specific field transformations if { "person" == $object_type_one && "im_project" == $object_type_two} { if { "" eq $object_id_one || "" eq $object_id_two } { if {$ns_write_p} { ns_write "
$errorInfo\n" } continue } } } if {$ns_write_p} { ns_write "
\n"
ns_write "Return to Project Page\n"
}
# ------------------------------------------------------------
# Render Report Footer
if {$ns_write_p} {
ns_write [im_footer]
}
intranet-csv-import-v5-0-2-4-1/www/index.adp 0000664 0000000 0000000 00000022400 13175625756 0020566 0 ustar 00root root 0000000 0000000
Please find below sample CSV files for each of the object types above. The header line shows the attributes that are supported by the CSV import. Column names are case insensitive and spaces may be replaced by "_" (underscore characters). The ]project-open[ team offers professional services to help you import data from
systems including SAP, Navision, Jira, MS-Project and many more.
Please contact us for more information.
Working CSV Examples
intranet-csv-import-v5-0-2-4-1/www/index.tcl 0000775 0000000 0000000 00000004415 13175625756 0020615 0 ustar 00root root 0000000 0000000 # /package/intranet-csv-import/www/index.tcl
#
# Copyright (C) 2004 ]project-open[
#
# 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_page_contract {
Serve the user a form to upload a new file or URL
@author Frank Bergmann (frank.bergmann@project-open.com)
@creation-date July 2003
} {
{ return_url "" }
{ object_type "" }
}
set user_id [auth::require_login]
set page_title [lang::message::lookup "" intranet-csv-import.Upload_file "Upload File"]
set context_bar [im_context_bar "" $page_title]
set main_navbar_label [im_csv_import_label_from_object_type -object_type $object_type]
# Get CSV Export links
db_1row get_report_ids "
select
(select report_id from im_reports where report_name = 'Export Persons') as report_id_export_persons,
(select report_id from im_reports where report_name = 'Export Companies') as report_id_export_companies,
(select report_id from im_reports where report_name = 'Export Projects') as report_id_export_projects,
(select report_id from im_reports where report_name = 'Export Tasks') as report_id_export_tasks,
(select report_id from im_reports where report_name = 'Export Project-Task Relationships') as report_id_export_project_task_relationships,
(select report_id from im_reports where report_name = 'Export Finance Documents') as report_id_export_finance_documents,
(select report_id from im_reports where report_name = 'Export Invoice Items') as report_id_export_invoice_items,
(select report_id from im_reports where report_name = 'Export Expense Bundles') as report_id_export_expense_bundles,
(select report_id from im_reports where report_name = 'Export Expense Items') as report_id_export_expense_items,
(select report_id from im_reports where report_name = 'Export Hours') as report_id_export_hours
from dual
"
if { "" eq $return_url } { set return_url "/intranet-csv-import/" }
intranet-csv-import-v5-0-2-4-1/www/js/ 0000775 0000000 0000000 00000000000 13175625756 0017407 5 ustar 00root root 0000000 0000000 intranet-csv-import-v5-0-2-4-1/www/js/jquery-save-as-you-type/ 0000775 0000000 0000000 00000000000 13175625756 0024054 5 ustar 00root root 0000000 0000000 intranet-csv-import-v5-0-2-4-1/www/js/jquery-save-as-you-type/dependencies/ 0000775 0000000 0000000 00000000000 13175625756 0026502 5 ustar 00root root 0000000 0000000 intranet-csv-import-v5-0-2-4-1/www/js/jquery-save-as-you-type/dependencies/jquery-cookie.js 0000664 0000000 0000000 00000003056 13175625756 0031632 0 ustar 00root root 0000000 0000000 /**
* jQuery Cookie plugin
*
* https://github.com/carhartl/jquery-cookie
*
* Copyright (c) 2010 Klaus Hartl (stilbuero.de)
* Dual licensed under the MIT and GPL licenses:
* http://www.opensource.org/licenses/mit-license.php
* http://www.gnu.org/licenses/gpl.html
*
*/
jQuery.cookie = function (key, value, options) {
// key and at least value given, set cookie...
if (arguments.length > 1 && String(value) !== "[object Object]") {
options = jQuery.extend({}, options);
if (value === null || value === undefined) {
options.expires = -1;
}
if (typeof options.expires === 'number') {
var days = options.expires, t = options.expires = new Date();
t.setDate(t.getDate() + days);
}
value = String(value);
return (document.cookie = [
encodeURIComponent(key), '=',
options.raw ? value : encodeURIComponent(value),
options.expires ? '; expires=' + options.expires.toUTCString() : '', // use expires attribute, max-age is not supported by IE
options.path ? '; path=' + options.path : '',
options.domain ? '; domain=' + options.domain : '',
options.secure ? '; secure' : ''
].join(''));
}
// key and possibly options given, get cookie...
options = value || {};
var result, decode = options.raw ? function (s) { return s; } : decodeURIComponent;
return (result = new RegExp('(?:^|; )' + encodeURIComponent(key) + '=([^;]*)').exec(document.cookie)) ? decode(result[1]) : null;
}; intranet-csv-import-v5-0-2-4-1/www/js/jquery-save-as-you-type/source/ 0000775 0000000 0000000 00000000000 13175625756 0025354 5 ustar 00root root 0000000 0000000 intranet-csv-import-v5-0-2-4-1/www/js/jquery-save-as-you-type/source/sayt.min.jquery.js 0000664 0000000 0000000 00000010605 13175625756 0030774 0 ustar 00root root 0000000 0000000 /*
*******************************************************************************
*
* sayt - Save As You Type
*
*******************************************************************************
*
* This plugin autosaves form input as it is being entered by the
* user. It is saved to a cookie during each keyup and change.
*
* When a page is reloaded for any reason, the form data is automatically
* re-entered for the user by reading the cookie. The cookie can be deleted
* on the fly by the plugin if required.
*
*******************************************************************************
*
* Intructions:
* By: Ben Griffiths, ben@ben-griffiths.com
* Version: 1.4.3
*
* Dependencies:
*
* jquery-cookie 1.0.0 (Relies on the jQuery Cookie plugin https://github.com/carhartl/jquery-cookie)
* - Included.
*
*******************************************************************************
*
* Licensed under The MIT License (MIT)
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.opensource.org/licenses/mit-license.php
*
* Copyright (c) 2011 Ben Griffiths (mail@thecodefoundryltd.com)
*
* Permission is hereby granted, free of charge, to any person obtaining a
* copy of this software and associated documentation files (the "Software"),
* to deal in the Software without restriction, including without limitation
* the rights to use, copy, modify, merge, publish, distribute, sublicense,
* and/or sell copies of the Software, and to permit persons to whom the
* Software is furnished to do so, subject to the following conditions:
*
* The above copyright notice and this permission notice shall be included
* in all copies or substantial portions of the Software.
*
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
* OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
* FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
* THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
* LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
* OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
* THE SOFTWARE.
*
*******************************************************************************
*/
(function(e){e.fn.sayt=function(t){function l(t){var r="";jQuery.each(t,function(e,t){r=r+t.name+":::--FIELDANDVARSPLITTER--:::"+t.value+":::--FORMSPLITTERFORVARS--:::"});e.cookie(s,r,{expires:n["days"]});if(typeof Storage!=="undefined"){localStorage.setItem(s,r)}else{e.cookie(s,r,{expires:n["days"]})}}function c(e,t,n){var r=(e+"").indexOf(t,n||0);return r===-1?false:r}function h(t,n){var r=e.extend({},t);var s=r.find("[data-sayt-exclude]");s.remove();for(i in n){s=r.find(n[i]);s.remove()}var o=r.serializeArray();return o}var n=e.extend({prefix:"autosaveFormCookie-",erase:false,days:3,autosave:true,savenow:false,recover:false,autorecover:true,checksaveexists:false,exclude:[]},t);var r=this;var s=n.prefix+r.attr("id");if(n["erase"]==true){e.cookie(s,null);if(typeof Storage!=="undefined"){localStorage.removeItem(s)}else{e.cookie(s,null)}return true}var o;if(typeof Storage!=="undefined"){o=localStorage.getItem(s)}else{o=e.cookie(s)}if(n["checksaveexists"]==true){if(o){return true}else{return false}return false}if(n["savenow"]==true){var u=h(r,n["exclude"]);l(u);return true}if(n["autorecover"]==true||n["recover"]==true){if(o){var a=o.split(":::--FORMSPLITTERFORVARS--:::");var f={};e.each(a,function(t,n){var r=n.split(":::--FIELDANDVARSPLITTER--:::");if(e.trim(r[0])!=""){if(e.trim(r[0])in f){f[e.trim(r[0])]=f[e.trim(r[0])]+":::--MULTISELECTSPLITTER--:::"+r[1]}else{f[e.trim(r[0])]=r[1]}}});e.each(f,function(t,n){if(c(n,":::--MULTISELECTSPLITTER--:::")>0){var r=n.split(":::--MULTISELECTSPLITTER--:::");e.each(r,function(n,r){e('input[name="'+t+'"], select[name="'+t+'"], textarea[name="'+t+'"]').find('[value="'+r+'"]').prop("selected",true);e('input[name="'+t+'"][value="'+r+'"], select[name="'+t+'"][value="'+r+'"], textarea[name="'+t+'"][value="'+r+'"]').prop("checked",true)})}else{e('input[name="'+t+'"], select[name="'+t+'"], textarea[name="'+t+'"]').val([n])}})}if(n["recover"]==true){return true}}if(n["autosave"]==true){this.find("input, select, textarea").each(function(t){e(this).change(function(){var e=h(r,n["exclude"]);l(e)});e(this).keyup(function(){var e=h(r,n["exclude"]);l(e)})})}}})(jQuery)
intranet-csv-import-v5-0-2-4-1/www/reports/ 0000775 0000000 0000000 00000000000 13175625756 0020471 5 ustar 00root root 0000000 0000000 intranet-csv-import-v5-0-2-4-1/www/reports/export-projects.tcl 0000664 0000000 0000000 00000027243 13175625756 0024355 0 ustar 00root root 0000000 0000000 # /packages/intranet-csv-import/www/projects-main.tcl
#
# Copyright (C) 2003-2008 ]project-open[
#
# All rights reserved. Please check
# http://www.project-open.com/ for licensing details.
ad_page_contract {
Report listing all main projects in the system with all available
fields + DynFields from projects and customers
} {
{ level_of_detail:integer 1}
{ max_depth:integer 3}
{ start_date "" }
{ end_date "" }
{ output_format "html" }
{ number_locale "" }
{ project_id:integer 0}
{ project_status_id:integer 0}
{ project_type_id:integer 0}
{ company_id:integer 0}
}
# ------------------------------------------------------------
# Security
# Label: Provides the security context for this report
# because it identifies unquely the report's Menu and
# its permissions.
set current_user_id [auth::require_login]
set read_p [im_permission $current_user_id view_projects_all]
# ------------------------------------------------------------
# Constants
set locale [lang::user::locale]
if {"" == $number_locale} { set number_locale $locale }
set date_format "YYYY-MM-DD"
set number_format "999,999.99"
# ------------------------------------------------------------
if {!$read_p} {
ad_return_complaint 1 "
[lang::message::lookup "" intranet-csv-import.You_dont_have_permissions "You don't have the necessary permissions to view this page"]"
return
}
# Check that Start & End-Date have correct format
if {"" != $start_date && ![regexp {[0-9][0-9][0-9][0-9]\-[0-9][0-9]\-[0-9][0-9]} $start_date]} {
ad_return_complaint 1 "Start Date doesn't have the right format.
CSV import
Export SQLComments
<%= [im_gif [db_string gif "select object_type_gif from acs_object_types where object_type = 'person'"]] %>
Person
<%= [im_gif csv-doc] %>
<%= [im_gif database_table] %>
<%= [im_gif [db_string gif "select object_type_gif from acs_object_types where object_type = 'im_company'"]] %>
Company
<%= [im_gif csv-doc] %>
<%= [im_gif database_table] %>
Company DynFields are also recognized by their "pretty name" or "column name".
<%= [im_gif [db_string gif "select object_type_gif from acs_object_types where object_type = 'im_invoice'"]] %>
Financial Documents (Invoices, Purchase Orders, etc.)
<%= [im_gif csv-doc] %>
<%= [im_gif database_table] %>
Financial Documents are
cost items with a header and
one more more lines. Financial documents include invoices, quotes, purchase orders and others.
Cost Item DynFields are also recognized by their "pretty name" or "column name".
<%= [im_gif page_white_star] %>
Financial Document Item
<%= [im_gif csv-doc] %>
<%= [im_gif database_table] %>
Allows to import "Items" seperately from headers.
<%= [im_gif [db_string gif "select object_type_gif from acs_object_types where object_type = 'im_project'"]] %>
Project
<%= [im_gif csv-doc] %>
<%= [im_gif database_table] %>
Project DynFields are also recognized by their "pretty name" or "column name".
<%= [im_gif [db_string gif "select object_type_gif from acs_object_types where object_type = 'im_gantt_task'" -default ""]] %>
Gantt Task
<%= [im_gif csv-doc] %>
<%= [im_gif database_table] %>
Allows to import Timesheet Tasks separately from projects/sub-projects.
<%= [im_gif hourglass] %>
Timesheet Hour
<%= [im_gif csv-doc] %>
<%= [im_gif database_table] %>
DynFields for hours are not supported.
<%= [im_gif money] %>
Expense Items
<%= [im_gif csv-doc] %>
<%= [im_gif database_table] %>
Expense DynFields are also recognized by their "pretty name" or "column name".
<%= [im_gif box] %>
Expense Bundles
<%= [im_gif csv-doc] %>
<%= [im_gif database_table] %>
<%= [im_gif [db_string gif "select object_type_gif from acs_object_types where object_type = 'im_conf_item'"]] %>
Conf Item
<%= [im_gif csv-doc] %>
Please note that this import requires exact naming of column headers.
Conf Item DynFields are also recognized by their "pretty name" or "column name".
<%= [im_gif [db_string gif "select object_type_gif from acs_object_types where object_type = 'im_risk'"]] %>
Risk
<%= [im_gif csv-doc] %>
Risk DynFields are also recognized by their "pretty name" or "column name".
<%=[im_gif brick_link]%>
Relationships
<%= [im_gif csv-doc] %>
<%= [im_gif database_table] %>
Import relationships between projects/tasks and persons.
<%= [im_gif [db_string gif "select object_type_gif from acs_object_types where object_type = 'im_user_absence'"]] %>
Absence
Absence import is under active development at the moment.
Please contact us for the current status or updates.
<%= [im_gif [db_string gif "select object_type_gif from acs_object_types where object_type = 'im_ticket'"]] %>
Tickets
Tickets import is under active development at the moment.
Please contact us for the current status or updates.
Current value: '$start_date'
Expected format: 'YYYY-MM-DD'"
}
if {"" != $end_date && ![regexp {[0-9][0-9][0-9][0-9]\-[0-9][0-9]\-[0-9][0-9]} $end_date]} {
ad_return_complaint 1 "End Date doesn't have the right format.
Current value: '$end_date'
Expected format: 'YYYY-MM-DD'"
}
set page_title "Project Report \[Beta\]"
set context_bar [im_context_bar $page_title]
set context ""
# ------------------------------------------------------------
# Defaults
set rowclass(0) "roweven"
set rowclass(1) "rowodd"
set days_in_past 7
db_1row todays_date "
select
to_char(sysdate::date - :days_in_past::integer, 'YYYY') as todays_year,
to_char(sysdate::date - :days_in_past::integer, 'MM') as todays_month,
to_char(sysdate::date - :days_in_past::integer, 'DD') as todays_day
from dual
"
if {"" == $start_date} {
set start_date "$todays_year-$todays_month-01"
}
db_1row end_date "
select
to_char(to_date(:start_date, 'YYYY-MM-DD') + 31::integer, 'YYYY') as end_year,
to_char(to_date(:start_date, 'YYYY-MM-DD') + 31::integer, 'MM') as end_month,
to_char(to_date(:start_date, 'YYYY-MM-DD') + 31::integer, 'DD') as end_day
from dual
"
if {"" == $end_date} {
set end_date "2099-12-31"
}
set company_url "/intranet/companies/view?company_id="
set project_url "/intranet/projects/view?project_id="
set user_url "/intranet/users/view?user_id="
set this_url [export_vars -base "/intranet-csv-import/projects-main" {start_date end_date level_of_detail project_id task_id company_id user_id} ]
# BaseURL for drill-down. Needs company_id, project_id, user_id, level_of_detail
set base_url [export_vars -base "/intranet-csv-import/projects-main" {start_date end_date task_id} ]
# ------------------------------------------------------------
# Conditional SQL Where-Clause
#
set criteria [list]
if {0 != $project_status_id && "" != $project_status_id} { lappend criteria "p.project_status_id in ([join [im_sub_categories $project_status_id] ","])" }
if {0 != $project_type_id && "" != $project_type_id} { lappend criteria "p.project_type_id in ([join [im_sub_categories $project_type_id] ","])" }
if {0 != $company_id && "" != $company_id} { lappend criteria "p.company_id = :company_id" }
if {0 != $project_id && "" != $project_id} { lappend criteria "p.project_id = :project_id" }
set where_clause [join $criteria " and\n "]
if { $where_clause ne "" } {
set where_clause " and $where_clause"
}
# ------------------------------------------------------------
# List of project variables to show
# Global header/footer
set header0 {"Parent Nrs" "Project Nr" "Project Name" "Customer Name" "Project Status" "Project Type" "Start Date" "End Date" "Percent Completed" "Budget" "Budget Hours"}
set footer0 {}
set counters [list]
# Variables per project
set project_vars {
$parent_nrs
$project_nr
$project_name
$customer_name
$project_status
$project_type
$project_start_date_pretty
$project_end_date_pretty
$percent_completed_pretty
$project_budget
$project_budget_hours
}
# ------------------------------------------------------------
# Add all Project and Company DynFields to list
set dynfield_sql "
select aa.attribute_name,
aa.pretty_name,
w.widget as tcl_widget,
w.widget_name as dynfield_widget,
w.deref_plpgsql_function
from
im_dynfield_attributes a,
im_dynfield_widgets w,
acs_attributes aa
where
a.widget_name = w.widget_name
and a.acs_attribute_id = aa.attribute_id
and aa.object_type in ('im_project')
and aa.attribute_name not like 'default%'
and aa.attribute_name not in (
'project_nr', 'project_path', 'parent_id', 'project_name', 'company_id', 'project_status_id', 'project_type_id',
'percent_completed', 'project_budget', 'project_budget_hours'
)
order by
aa.object_type,
aa.sort_order
"
set derefs [list]
db_foreach dynfield_attributes $dynfield_sql {
set deref "${deref_plpgsql_function}($attribute_name) as ${attribute_name}_deref"
lappend header0 $pretty_name
lappend derefs $deref
set var_name "\$${attribute_name}_deref"
lappend project_vars $var_name
}
# ------------------------------------------------------------
# Define the report - SQL, counters, headers and footers
#
set sql "
select
p.*,
acs_object__name(p.company_id) as customer_name,
im_category_from_id(p.project_status_id) as project_status,
im_category_from_id(p.project_type_id) as project_type,
to_char(p.start_date, :date_format) as project_start_date_pretty,
to_char(p.end_date, :date_format) as project_end_date_pretty,
im_name_from_user_id(p.company_contact_id) as company_contact_name,
round(p.percent_completed::numeric, 2) as percent_completed_rounded,
[join $derefs "\t,\n"]
from
im_projects p
where
p.project_status_id not in ([im_project_status_deleted]) and
p.end_date >= to_timestamp(:start_date, 'YYYY-MM-DD') and
p.end_date < to_timestamp(:end_date, 'YYYY-MM-DD') and
tree_level(p.tree_sortkey) <= :max_depth
$where_clause
order by
p.company_id,
p.tree_sortkey
"
set report_def [list \
group_by project_nr \
header $project_vars \
content [list] \
footer [list] \
]
# ------------------------------------------------------------
# Constants
#
set start_years {2000 2000 2001 2001 2002 2002 2003 2003 2004 2004 2005 2005 2006 2006}
set start_months {01 Jan 02 Feb 03 Mar 04 Apr 05 May 06 Jun 07 Jul 08 Aug 09 Sep 10 Oct 11 Nov 12 Dec}
set start_days {01 1 02 2 03 3 04 4 05 5 06 6 07 7 08 8 09 9 10 10 11 11 12 12 13 13 14 14 15 15 16 16 17 17 18 18 19 19 20 20 21 21 22 22 23 23 24 24 25 25 26 26 27 27 28 28 29 29 30 30 31 31}
set levels {1 "Customer Only" 2 "Customer+Project"}
# ------------------------------------------------------------
# Start Formatting the HTML Page Contents
# Write out HTTP header, considering CSV/MS-Excel formatting
im_report_write_http_headers -output_format $output_format -report_name "export-projects.csv"
switch $output_format {
html {
ns_write "
[im_header $page_title]
[im_navbar]
\n"
}
}
im_report_render_row \
-output_format $output_format \
-row $header0 \
-row_class "rowtitle" \
-cell_class "rowtitle"
set footer_array_list [list]
set last_value_list [list]
set class "rowodd"
db_foreach sql $sql {
set percent_completed_pretty [im_report_format_number $percent_completed_rounded $output_format $number_locale]
set project_budget_pretty [im_report_format_number $project_budget $output_format $number_locale]
set project_budget_hours_pretty [im_report_format_number $project_budget_hours $output_format $number_locale]
# Calculate the list of parent_nrs
set parent_nrs {}
set parent_nrs_id $parent_id
while {"" ne $parent_nrs_id} {
lappend parent_nrs [util_memoize [list db_string project_nr "select project_nr from im_projects where project_id = $parent_nrs_id" -default ""]]
set parent_nrs_id [util_memoize [list db_string parent_id "select parent_id from im_projects where project_id = $parent_nrs_id" -default ""]]
}
set parent_nrs [reverse $parent_nrs]
im_report_display_footer \
-output_format $output_format \
-group_def $report_def \
-footer_array_list $footer_array_list \
-last_value_array_list $last_value_list \
-level_of_detail $level_of_detail \
-row_class $class \
-cell_class $class
im_report_update_counters -counters $counters
set last_value_list [im_report_render_header \
-output_format $output_format \
-group_def $report_def \
-last_value_array_list $last_value_list \
-level_of_detail $level_of_detail \
-row_class $class \
-cell_class $class
]
set footer_array_list [im_report_render_footer \
-output_format $output_format \
-group_def $report_def \
-last_value_array_list $last_value_list \
-level_of_detail $level_of_detail \
-row_class $class \
-cell_class $class
]
}
im_report_display_footer \
-output_format $output_format \
-group_def $report_def \
-footer_array_list $footer_array_list \
-last_value_array_list $last_value_list \
-level_of_detail $level_of_detail \
-display_all_footers_p 1 \
-row_class $class \
-cell_class $class
im_report_render_row \
-output_format $output_format \
-row $footer0 \
-row_class $class \
-cell_class $class
# Write out the HTMl to close the main report table
# and write out the page footer.
#
switch $output_format {
html { ns_write "
\n[im_footer]\n"}
}