pax_global_header 0000666 0000000 0000000 00000000064 11764120760 0014516 g ustar 00root root 0000000 0000000 52 comment=8004715a9a00d0c76a512729c3bfd97ecbf84cd2
intranet-csv-import-v4-0-3-3-0/ 0000775 0000000 0000000 00000000000 11764120760 0016130 5 ustar 00root root 0000000 0000000 intranet-csv-import-v4-0-3-3-0/intranet-csv-import.info 0000664 0000000 0000000 00000002351 11764120760 0022733 0 ustar 00root root 0000000 0000000
]project-open[ CSV Import]project-open[ CSV Importftffintranet-csv-importFrank BergmannImport objects from CSV files]project-open[Allows to map CSV column names to ]po[ fields and imports various type of objects0
intranet-csv-import-v4-0-3-3-0/tcl/ 0000775 0000000 0000000 00000000000 11764120760 0016712 5 ustar 00root root 0000000 0000000 intranet-csv-import-v4-0-3-3-0/tcl/intranet-csv-import-procs.tcl 0000664 0000000 0000000 00000025570 11764120760 0024500 0 ustar 00root root 0000000 0000000 # /packages/intranet-cvs-import/tcl/intranet-cvs-import-procs.tcl
#
# Copyright (C) 2011 ]project-open[
#
# All rights reserved. Please check
# http://www.project-open.com/license/ for details.
ad_library {
@author frank.bergmann@project-open.com
}
# ---------------------------------------------------------------------
# Aux functions
# ---------------------------------------------------------------------
ad_proc -public im_id_from_user_name { name } {
Checks for a user with the given name
} {
set user_id [db_string uid "
select min(user_id)
from users
where lower(trim(username)) = lower(trim(:name))
" -default ""]
if {"" == $user_id} {
set user_id [db_string uid "
select min(person_id)
from persons
where lower(trim(im_name_from_user_id(person_id))) = lower(trim(:name))
" -default ""]
}
return $user_id
}
ad_proc -public im_csv_import_parser_date_european {
{-parser_args "" }
arg
} {
Parses a European date format like '08.06.2011' as the 8th of June, 2011
} {
if {[regexp {^(.+)\.(.+)\.(....)$} $arg match dom month year]} {
if {1 == [string length $dom]} { set dom "0$dom" }
if {1 == [string length $month]} { set dom "0$month" }
return [list "$year-$month-$dom" ""]
}
return [list "" "Error parsing European date format '$arg': expected 'dd.mm.yyyy'"]
}
ad_proc -public im_csv_import_parser_date_american {
{-parser_args "" }
arg
} {
Parses a American date format like '12/31/2011' as the 31st of December, 2011
} {
if {[regexp {^(.+)\/(.+)\/(....)$} $arg match month dom year]} {
if {1 == [string length $dom]} { set dom "0$dom" }
if {1 == [string length $month]} { set dom "0$month" }
return [list "$year-$month-$dom" ""]
}
return [list "" "Error parsing American date format '$arg': expected 'dd.mm.yyyy'"]
}
ad_proc -public im_csv_import_parser_category {
{-parser_args "" }
arg
} {
Parses a category into a category_id
} {
# Empty input - empty output
if {"" == $arg} { return [list "" ""] }
# Parse the category
set result [im_id_from_category $arg $parser_args]
if {"" == $result} {
return [list "" "Category parser: We did not find a value='$arg' in category type '$parser_args'."]
} else {
return [list $result ""]
}
}
ad_proc -public im_csv_import_parser_cost_center {
{-parser_args "" }
arg
} {
Parses a cost center into a cost_center_id
} {
# Empty input - empty output
if {"" == $arg} { return [list "" ""] }
# Parse the category
set arg [string trim [string tolower $arg]]
set ccids [db_list ccid1 "
select cost_center_id
from im_cost_centers
where lower(cost_center_code) = :arg OR
lower(cost_center_label) = :arg OR
lower(cost_center_name) = :arg order by cost_center_id
"]
set result [lindex $ccids 0]
if {"" == $result} {
return [list "" "Cost Center parser: We did not find any cost center with label, code or name matching the value='$arg'."]
} else {
return [list $result ""]
}
}
ad_proc -public im_csv_import_parser_hard_coded {
{-parser_args "" }
arg
} {
Empty parser - returns the argument
} {
return [list $arg ""]
}
# ----------------------------------------------------------------------
#
# ----------------------------------------------------------------------
ad_proc -public im_csv_import_label_from_object_type {
-object_type:required
} {
Returns the main navbar lable for the object_type
} {
switch $object_type {
im_company { return "companies" }
im_project { return "projects" }
person { return "users" }
default { return "" }
}
}
# ---------------------------------------------------------------------
# Available Fields per Object Type
# ---------------------------------------------------------------------
ad_proc -public im_csv_import_object_fields {
-object_type:required
} {
Returns a list of database columns for the specified object type.
} {
# Get the list of super-types for object_type, including object_type
# and remove "acs_object" from the list
set super_types [im_object_super_types -object_type $object_type]
set s [list]
foreach t $super_types {
if {$t == "acs_object"} { continue }
lappend s $t
}
set super_types $s
# ---------------------------------------------------------------
# Get the list of tables associated with the object type and its super types
set tables_sql "
select *
from (
select table_name, id_column, 1 as sort_order
from acs_object_types
where object_type in ('[join $super_types "', '"]')
UNION
select table_name, id_column, 2 as sort_order
from acs_object_type_tables
where object_type in ('[join $super_types "', '"]')
) t
order by t.sort_order
"
set columns_sql "
select lower(column_name) as column_name
from user_tab_columns
where lower(table_name) = lower(:table_name)
"
set selected_columns {}
set selected_tables {}
set cnt 0
db_foreach tables $tables_sql {
if {[lsearch $selected_tables $table_name] >= 0} {
ns_log Notice "im_csv_import_object_fields: found duplicate table: $table_name"
continue
}
db_foreach columns $columns_sql {
if {[lsearch $selected_columns $column_name] >= 0} {
ns_log Notice "im_csv_import_object_fields: found ambiguous field: $table_name.$column_name"
continue
}
lappend selected_columns $column_name
}
lappend selected_tables $table_name
incr cnt
}
return [lsort $selected_columns]
}
# ---------------------------------------------------------------------
# Available Parsers
# ---------------------------------------------------------------------
ad_proc -public im_csv_import_parsers {
-object_type:required
} {
Returns the list of available parsers
} {
switch $object_type {
im_project - im_timesheet_task - im_ticket {
set parsers {
no_change "No Change"
hard_coded "Hard Coded Functionality"
date_european "European Date Parser (DD.MM.YYYY)"
date_american "American Date Parser (MM/DD/YYYY)"
category "Category Parser"
cost_center "Cost Center Parser"
}
}
default {
ad_return_complaint 1 "im_csv_import_parsers: Unknown object type '$object_type'"
ad_script_abort
}
}
return $parsers
}
# ---------------------------------------------------------------------
# Guess the most appropriate parser for a column
# ---------------------------------------------------------------------
ad_proc -public im_csv_import_guess_parser {
{-sample_values {}}
-object_type:required
-field_name:required
} {
Returns the best guess for a parser for the given field as
a list with:
The parser name,
the parser args and
the field name to map to
} {
# --------------------------------------------------------
# Hard Coded Mappings
switch $object_type {
im_project - im_timesheet_task - im_ticket {
switch $field_name {
parent_nrs { return [list "hard_coded" "" ""] }
customer_name { return [list "hard_coded" "" ""] }
project_status { return [list "hard_coded" "" "project_status_id"] }
project_type { return [list "hard_coded" "" "project_type_id"] }
on_track_status { return [list "hard_coded" "" "on_track_status_id"] }
customer_contact { return [list "" "" "company_contact_id"] }
project_manager { return [list "hard_coded" "" "project_lead_id"] }
}
}
}
# --------------------------------------------------------
# Date parsers
#
# Abort if there are not enough values
if {[llength $sample_values] >= 1} {
set date_european_p 1
set date_american_p 1
set number_plain_p 1
set number_european_p 1
set number_american_p 1
# set the parserst to 0 if one of the values doesn't fit
foreach val $sample_values {
if {![regexp {^(.+)\.(.+)\.(....)$} $val match]} { set date_european_p 0 }
if {![regexp {^(.+)\/(.+)\/(....)$} $val match]} { set date_american_p 0 }
if {![regexp {^[0-9]+$} $val match]} { set number_plain 0 }
}
if {$date_european_p} { return [list "date_european" "" ""] }
if {$date_american_p} { return [list "date_american" "" ""]}
}
# --------------------------------------------------------
# Get the list of super-types for object_type, including object_type
# and remove "acs_object" from the list
set super_types [im_object_super_types -object_type $object_type]
set s [list]
foreach t $super_types {
if {$t == "acs_object"} { continue }
lappend s $t
}
set super_types $s
ns_log Notice "im_csv_import_guess_parser: field_name=$field_name, super_types=$super_types"
# --------------------------------------------------------
# Parsing for DynFields
#
# There can be 0, 1 or multiple dynfields with the field_name,
# unfortunately.
set dynfield_sql "
select dw.widget as tcl_widget,
dw.parameters as tcl_widget_parameters,
substring(dw.parameters from 'category_type \"(.*)\"') as category_type,
aa.attribute_name
from acs_attributes aa,
im_dynfield_attributes da,
im_dynfield_widgets dw
where aa.object_type in ('[join $super_types "','"]') and
aa.attribute_id = da.acs_attribute_id and
da.widget_name = dw.widget_name and
(lower(aa.attribute_name) = lower(trim(:field_name)) OR
lower(aa.attribute_name) = lower(trim(:field_name))||'_id'
)
"
set result [list "" "" ""]
set ttt_widget ""
db_foreach dynfields $dynfield_sql {
set ttt_widget $tcl_widget
switch $tcl_widget {
"im_category_tree" {
set result [list "category" $category_type $attribute_name]
}
"im_cost_center_tree" {
set result [list "cost_center" "" $attribute_name]
}
default {
# Default: No specific parser
set result [list "" "" $attribute_name]
}
}
}
ns_log Notice "im_csv_import_guess_parser: field_name=$field_name, tcl_widget=$ttt_widget => $result"
return $result
}
# ---------------------------------------------------------------------
# Convert the list of parent_nrs into the parent_id
# ---------------------------------------------------------------------
ad_proc -public im_csv_import_convert_project_parent_nrs {
{-parent_id ""}
parent_nrs
} {
Returns {parent_id err}
} {
ns_log Notice "im_csv_import_convert_project_parent_nrs -parent_id $parent_id $parent_nrs"
# Recursion end - just return the parent.
if {"" == $parent_nrs} { return [list $parent_id ""] }
# Lookup the first parent_nr below the current parent_id
set parent_nr [lindex $parent_nrs 0]
set parent_nrs [lrange $parent_nrs 1 end]
set parent_sql "= $parent_id"
if {"" == $parent_id} { set parent_sql "is null" }
set parent_id [db_string pid "
select project_id
from im_projects
where parent_id $parent_sql and
lower(project_nr) = lower(:parent_nr)
"]
if {"" == $parent_id} {
return [list "" "Didn't find project with project_nr='$project_nr' and parent_id='$parent_id'"]
}
return [im_csv_import_convert_project_parent_nrs -parent_id $parent_id $parent_nrs]
} intranet-csv-import-v4-0-3-3-0/www/ 0000775 0000000 0000000 00000000000 11764120760 0016754 5 ustar 00root root 0000000 0000000 intranet-csv-import-v4-0-3-3-0/www/import-2.adp 0000664 0000000 0000000 00000002665 11764120760 0021124 0 ustar 00root root 0000000 0000000 @page_title@@context_bar@@main_navbar_label@
intranet-csv-import-v4-0-3-3-0/www/import-2.tcl 0000775 0000000 0000000 00000013422 11764120760 0021136 0 ustar 00root root 0000000 0000000 # /packages/intranet-csv-import/www/import-2.tcl
#
ad_page_contract {
Starts the analysis process for the file imported
@author frank.bergmann@project-open.com
} {
{ return_url "" }
{ main_navbar_label "" }
object_type
upload_file
object_type
}
# ---------------------------------------------------------------------
# Default & Security
# ---------------------------------------------------------------------
set current_user_id [ad_maybe_redirect_for_registration]
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
}
# Get the file from the user.
# number_of_bytes is the upper-limit
set max_n_bytes [ad_parameter -package_id [im_package_filestorage_id] MaxNumberOfBytes "" 0]
set tmp_filename [ns_queryget upload_file.tmpfile]
im_security_alert_check_tmpnam -location "import-2.tcl" -value $tmp_filename
if { $max_n_bytes && ([file size $tmp_filename] > $max_n_bytes) } {
ad_return_complaint 1 "Your file is larger than the maximum permissible upload size:
[util_commify_number $max_n_bytes] bytes"
ad_script_abort
}
# Empty return_url?
# Choose depending on the object type...
if {"" == $return_url} {
switch $object_type {
im_project { set return_url "/intranet/projects/index" }
im_company { set return_url "/intranet/companies/index" }
default { set return_url "/intranet" }
}
}
# strip off the C:\directories... crud and just get the file name
if ![regexp {([^//\\]+)$} $upload_file match filename] {
# couldn't find a match
set filename $upload_file
}
if {[regexp {\.\.} $filename]} {
set error "Filename contains forbidden characters"
ad_returnredirect "/error.tcl?[export_url_vars error]"
}
if {![file readable $tmp_filename]} {
ad_return_complaint 1 "Unable to read the file '$tmp_filename'.
Please check the file permissions or contact your system administrator.\n"
ad_script_abort
}
set import_filename "${tmp_filename}_copy"
catch {
exec cp $tmp_filename $import_filename
}
# ---------------------------------------------------------------------
# Open and parse the file
# ---------------------------------------------------------------------
set encoding "utf-8"
if {[catch {
set fl [open $tmp_filename]
fconfigure $fl -encoding $encoding
set lines_content [read $fl]
close $fl
} err]} {
ad_return_complaint 1 "Unable to open file $tmp_filename:
\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]
# 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"
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 }
}
set defs [im_csv_import_guess_parser -object_type $object_type -field_name $header_name -sample_values $parser_sample_values]
ns_log Notice "import-2: otype=$object_type, field_name=$header_name => parser=$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 [string tolower $header_name]
if {"" != $override_map} { set default_map $override_map }
set map [im_select map.$cnt $object_type_pairs $default_map]
if {"hard_coded" == $default_parser} { set map [im_select map.$cnt $object_type_pairs "hard_coded"] }
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 - im_ticket {
set redirect_object_type "im_project"
}
default {
set redirect_object_type $object_type
}
}
intranet-csv-import-v4-0-3-3-0/www/import-im_project.tcl 0000664 0000000 0000000 00000044404 11764120760 0023131 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 [ad_maybe_redirect_for_registration]
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_project', 'im_timesheet_task') 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 "
\n" }
if {$ns_write_p} { ns_write "
Starting to parse line $cnt\n" }
# Preset values, defined by CSV sheet:
set project_name ""
set project_nr ""
set project_path ""
set customer_name ""
set customer_id ""
set parent_nrs ""
set parent_id ""
set project_status ""
set project_status_id ""
set project_type ""
set project_type_id ""
set project_lead_id ""
set start_date ""
set end_date ""
set percent_completed ""
set on_track_status ""
set project_budget ""
set project_budget_currency ""
set project_budget_hours ""
set description ""
set note ""
set customer_contact ""
set customer_contact_id ""
set customer_project_nr ""
set confirm_date ""
set expected_quality_id ""
set final_company ""
set milestone_p ""
set sort_order ""
set source_language_id ""
set subject_area_id ""
set template_p ""
set cost_center ""
set uom ""
set material ""
set planned_units ""
set billable_units ""
set priority ""
set sort_order ""
foreach attribute_name $attribute_names {
set $attribute_name ""
}
# -------------------------------------------------------
# Extract variables from the CSV file
#
set var_name_list [list]
for {set j 0} {$j < $header_len} {incr j} {
set var_name [string trim [lindex $header_fields $j]]
if {"" == $var_name} {
# No variable name - probably an empty column
continue
}
set var_name [string tolower $var_name]
set var_name [string map -nocase {" " "_" "\"" "" "'" "" "/" "_" "-" "_" "\[" "(" "\{" "(" "\}" ")" "\]" ")"} $var_name]
lappend var_name_list $var_name
ns_log notice "upload-companies-2: varname([lindex $header_fields $j]) = $var_name"
set var_value [string trim [lindex $csv_line_fields $j]]
set var_value [string map -nocase {"\"" "'" "\[" "(" "\{" "(" "\}" ")" "\]" ")"} $var_value]
if {[string equal "NULL" $var_value]} { set var_value ""}
# replace unicode characters by non-accented characters
# Watch out! Does not work with Latin-1 characters
set var_name [im_mangle_unicode_accents $var_name]
set cmd "set $var_name \"$var_value\""
ns_log Notice "upload-companies-2: cmd=$cmd"
set result [eval $cmd]
}
# -------------------------------------------------------
# Transform the variables
set i 0
foreach varname $var_name_list {
set p $parser($i)
set p_args $parser_args($i)
set target_varname $map($i)
ns_log Notice "import-im_project: Parser: $varname -> $target_varname"
switch $p {
no_change { }
default {
set proc_name "im_csv_import_parser_$p"
if {[catch {
set val [set $varname]
if {"" != $val} {
set result [$proc_name -parser_args $p_args $val]
set res [lindex $result 0]
set err [lindex $result 1]
ns_log Notice "import-im_project: Parser: '$p -args $p_args $val' -> $target_varname=$res, err=$err"
if {"" != $err} {
if {$ns_write_p} {
ns_write "
Warning: Error parsing field='$target_varname' using parser '$p':
$err
\n"
}
}
set $target_varname $res
}
} err_msg]} {
if {$ns_write_p} {
ns_write "
Warning: Error parsing field='$target_varname' using parser '$p':
$err_msg
"
}
}
}
}
incr i
}
# -------------------------------------------------------
# Specific field transformations
# project_name needs to be there
if {"" == $project_name} {
if {$ns_write_p} {
ns_write "
Error: We have found an empty 'Project Name' in line $cnt.
Please correct the CSV file. Every projects needs to have a unique Project Name.\n"
}
continue
}
# project_nr needs to be there
if {"" == $project_nr} {
if {$ns_write_p} {
ns_write "
Error: We have found an empty 'Project Nr' in line $cnt.
Please correct the CSV file. Every project needs to have a unique Project Nr.\n"
}
continue
}
# parent_nrs contains a space separated list
if {[catch {
set result [im_csv_import_convert_project_parent_nrs $parent_nrs]
} err_msg]} {
if {$ns_write_p} { ns_write "
Error: We have found an error parsing Parent NRs '$parent_nrs'.
\n$err_msg
" }
continue
}
set parent_id [lindex $result 0]
set err [lindex $result 1]
if {"" != $err} {
if {$ns_write_p} { ns_write "
Error:
$err
\n" }
continue
}
# Status is a required field
set project_status_id [im_id_from_category $project_status "Intranet Project Status"]
if {"" == $project_status_id} {
if {$ns_write_p} { ns_write "
Warning: Didn't find project status '$project_status', using default status 'Open'\n" }
set project_status_id [im_project_status_open]
}
# Type is a required field
set project_type_id [im_id_from_category [list $project_type] "Intranet Project Type"]
if {"" == $project_type_id} {
if {$ns_write_p} { ns_write "
Warning: Didn't find project type '$project_type', using default type 'Other'\n" }
set project_type_id [im_project_type_other]
}
# On track status can be NULL without problems
set on_track_status_id [im_id_from_category [list $on_track_status] "Intranet Project On Track Status"]
# customer_id
if {"" == $customer_id } {
set customer_id [db_string cust "select company_id from im_companies where lower(company_name) = trim(lower(:customer_name))" -default ""]
}
if {"" == $customer_id } {
set customer_id [db_string cust "select company_id from im_companies where lower(company_path) = trim(lower(:customer_name))" -default ""]
}
# For compatibility
set company_id $customer_id
if {"" == $customer_id } {
if {$ns_write_p} {
ns_write "
Error: Didn't find customer for '$customer_name'.
Every projects needs a valid customer. Please correct the CSV file.\n"
}
continue
}
set project_lead_id [im_id_from_user_name $project_manager]
if {"" == $project_lead_id && "" != $project_manager} {
if {$ns_write_p} { ns_write "
Warning: Didn't find project manager '$project_manager'.\n" }
}
set customer_contact_id [im_id_from_user_name $customer_contact]
if {"" == $customer_contact_id && "" != $customer_contact} {
if {$ns_write_p} { ns_write "
Warning: Didn't find customer contact '$customer_contact'.\n" }
}
# -------------------------------------------------------
# Check if the project already exists
set parent_id_sql "= $parent_id"
if {"" == $parent_id} {
set parent_id_sql "is null"
}
set project_id [db_string project_id "
select project_id
from im_projects p
where p.parent_id $parent_id_sql and
lower(trim(project_nr)) = lower(trim(:project_nr))
" -default ""]
set project_id2 [db_string project_id "
select project_id
from im_projects p
where p.parent_id $parent_id_sql and
lower(trim(project_name)) = lower(trim(:project_name))
" -default ""]
if {$project_id != $project_id2 && "" != $project_id && "" != $project_id2} {
if {$ns_write_p} {
ns_write "
Error: We have found two different projects, one with
'Project Nr'=$project_nr and a second one with 'Project Name'='$project_name'.
Please change one of the two projects to avoid this ambiguity.
\n"
}
continue
}
if {"" == $project_id} { set project_id $project_id2 }
# Check for problems with project_path
set project_path_exists_p [db_string project_path_existis_p "
select count(*)
from im_projects p
where p.parent_id $parent_id_sql and
p.project_id != :project_id and
lower(trim(project_path)) = lower(trim(:project_path))
"]
if {$project_path_exists_p} {
if {$ns_write_p} { ns_write "
Error: project_path='$project_path' already exists with the parent '$parent_id'" }
continue
}
# Project Path is the same as the if not specified otherwise
if {"" == $project_path} { set project_path $project_nr }
# Create a new project if necessary
if {"" == $project_id} {
if {$ns_write_p} { ns_write "
" }
continue
}
# Add the project lead to the list of project members
if {"" != $project_lead_id} {
set role_id [im_biz_object_role_project_manager]
im_biz_object_add_role $project_lead_id $project_id $role_id
}
# -------------------------------------------------------
# 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 "
Warning: Didn't find material '$material', using 'Default'.\n"
}
}
}
if {"" == $material_id} {
set material_id [im_material_default_material_id]
}
# Task Cost Center
set cost_center_id [db_string cost_center_lookup "
select min(cost_center_id)
from im_cost_centers
where ( lower(trim(cost_center_name)) = lower(trim(:cost_center))
OR lower(trim(cost_center_label)) = lower(trim(:cost_center))
OR lower(trim(cost_center_code)) = lower(trim(:cost_center))
)
" -default ""]
if {"" == $cost_center_id && "" != $cost_center} {
if {$ns_write_p} { ns_write "
Warning: Didn't find cost_center '$cost_center'.\n" }
}
# Task UoM
if {"" == $uom} { set uom "Hour" }
set uom_id [im_id_from_category [list $uom] "Intranet UoM"]
if {"" == $uom_id} {
if {$ns_write_p} { ns_write "
Warning: Didn't find UoM '$uom', using default 'Hour'\n" }
set uom_id [im_uom_hour]
}
set task_exists_p [db_string task_exists_p "
select count(*)
from im_timesheet_tasks
where task_id = :project_id
"]
if {!$task_exists_p} {
db_dml task_insert "
insert into im_timesheet_tasks (
task_id,
material_id,
uom_id
) values (
:project_id,
:material_id,
:uom_id
)
"
db_dml make_project_to_task "
update acs_objects
set object_type = 'im_timesheet_task'
where object_id = :project_id
"
}
db_dml update_task "
update im_timesheet_tasks set
material_id = :material_id,
uom_id = :uom_id,
planned_units = :planned_units,
billable_units = :billable_units,
cost_center_id = :cost_center_id
where
task_id = :project_id
"
}
# -------------------------------------------------------
# Import DynFields
set project_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_project: 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_project: name=$attribute_name already exists."
continue
}
set attributes_hash($key) $table_name
switch $table_name {
im_projects {
lappend project_dynfield_updates "$attribute_name = :$attribute_name"
}
im_timesheet_tasks {
lappend task_dynfield_updates "$attribute_name = :$attribute_name"
}
default {
ad_return_complaint 1 "Dynfield Configuration Error:
Attribute='$attribute_name' in table='$table_name' is not in im_projects or im_timesheet_tasks."
}
}
}
if {$ns_write_p} { ns_write "
Going to update im_project DynFields.\n" }
if {"" != $project_dynfield_updates} {
set project_update_sql "
update im_projects set
[join $project_dynfield_updates ",\n\t\t"]
where project_id = :project_id
"
if {[catch {
db_dml project_dynfield_update $project_update_sql
} err_msg]} {
if {$ns_write_p} { ns_write "
Warning: Error updating im_project dynfields:
$err_msg
" }
}
}
if {$ns_write_p} { ns_write "
Going to update im_timesheet_task DynFields.\n" }
if {"" != $task_dynfield_updates} {
set task_update_sql "
update im_timesheet_tasks set
[join $task_dynfield_updates ",\n\t\t"]
where task_id = :project_id
"
if {[catch {
db_dml task_dynfield_update $task_update_sql
} err_msg]} {
if {$ns_write_p} { ns_write "
We espect that the import file contains the column names in the first row.
intranet-csv-import-v4-0-3-3-0/www/index.tcl 0000775 0000000 0000000 00000002065 11764120760 0020575 0 ustar 00root root 0000000 0000000 # /package/intranet-core/companies/upload-companies.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 [ad_maybe_redirect_for_registration]
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]