pax_global_header 0000666 0000000 0000000 00000000064 13751511246 0014517 g ustar 00root root 0000000 0000000 52 comment=2f965934abd521c401697be9560ccc6381c494ee intranet-csv-import-v5-0-4-0-0/ 0000775 0000000 0000000 00000000000 13751511246 0016130 5 ustar 00root root 0000000 0000000 intranet-csv-import-v5-0-4-0-0/README.md 0000664 0000000 0000000 00000051436 13751511246 0017420 0 ustar 00root root 0000000 0000000 # ]po[ CSV Import This package is part of ]project-open[, an open-source enterprise project management system. For more information about ]project-open[ please see: * [Documentation Wiki](http://www.project-open.com/en/) * [V5.0 Download](https://sourceforge.net/projects/project-open/files/project-open/V5.0/) * [Installation Instructions](http://www.project-open.com/en/list-installers) About ]po[ CSV Import:
This package allows for comfortable import of data in CSV (Comma Separated Values) format into ]po[ objects for:
# Online Reference Documentation ## Procedure Files
tcl/intranet-csv-import-parser-procs.tcl | ||
tcl/intranet-csv-import-procs.tcl |
csv_norm | Performs normalization including trim, tolower, replace non-ascii with "_". | |
im_csv_import_check_list_of_lists | Check that the parameter is a list of lists with all lines having the same length. | |
im_csv_import_guess_im_budget_item | ||
im_csv_import_guess_im_company | ||
im_csv_import_guess_im_conf_item | ||
im_csv_import_guess_im_expense | ||
im_csv_import_guess_im_expense_bundle | ||
im_csv_import_guess_im_hour | ||
im_csv_import_guess_im_invoice | ||
im_csv_import_guess_im_project | ||
im_csv_import_guess_im_risk | ||
im_csv_import_guess_im_ticket | ||
im_csv_import_guess_im_timesheet_task | ||
im_csv_import_guess_map | Returns the best guess for a DynField for the field. | |
im_csv_import_guess_parser | Returns the best guess for a parser for the given field as a list with: <ul> <li>The parser name, <li>the parser args and <li>the field name to map to </ul> | |
im_csv_import_guess_person | ||
im_csv_import_guess_rels | ||
im_csv_import_label_from_object_type | Returns the main navbar lable for the object_type | |
im_csv_import_object_fields | Returns a list of database columns for the specified object type. | |
im_csv_import_parser_boolean | Boolean - argument is mapped to SQL boolean: 't' or 'f' | |
im_csv_import_parser_budget_item_parent_nrs | Returns a budget_item_id from a list of parent budget item numbers | |
im_csv_import_parser_category | Parses a category into a category_id | |
im_csv_import_parser_company_name | Returns a company_id from im_companies | |
im_csv_import_parser_conf_item_parent_nrs | Returns a conf_item_id from a list of conf_item Nrs. | |
im_csv_import_parser_cost_center | Parses a cost center into a cost_center_id | |
im_csv_import_parser_date | Generic date parser - front-end for all available date formats | |
im_csv_import_parser_date_american | Parses a American date format like '12/31/2011' as the 31st of December, 2011 | |
im_csv_import_parser_date_european | Parses a European date format like '08.06.2011' as the 8th of June, 2011 | |
im_csv_import_parser_date_european_slashes | Parses a European date format like '08/06/2011' as the 8th of June, 2011 | |
im_csv_import_parser_date_iso | Parses ISO date format like '2011-06-08' as the 8th of June, 2011 | |
im_csv_import_parser_hard_coded | Empty parser - returns the argument | |
im_csv_import_parser_material | Parses a material name or nr into a material_id | |
im_csv_import_parser_no_change | Dummy parser without transformation | |
im_csv_import_parser_number | Parses a generic number. | |
im_csv_import_parser_number_american | Validates a US number format | |
im_csv_import_parser_number_european | Parses a European number format like '20.000,00' as twenty thousand | |
im_csv_import_parser_office_name | Returns a office_id from im_companies | |
im_csv_import_parser_percentage | Parses a percentage number. | |
im_csv_import_parser_project_name | Returns a project_id from project_name | |
im_csv_import_parser_project_nr | Returns a project_id from project_nr | |
im_csv_import_parser_project_parent_nrs | Returns a project_id from a list of project_nr's | |
im_csv_import_parser_user_name | Returns a user_id from parties | |
im_csv_import_parsers | Returns the list of available parsers |
sql/postgresql/upgrade/upgrade-5.0.2.0.0-5.0.2.0.1.sql | ||
sql/postgresql/upgrade/upgrade-5.0.2.4.0-5.0.2.4.1.sql |
www/ | |
examples/ | |
im_budget_item.csv | |
im_company.csv | |
im_conf_item.csv | |
im_expense.csv | |
im_expense_bundle.csv | |
im_hour.csv | |
im_invoice.csv | |
im_project.csv | |
im_relation.csv | |
im_risk.csv | |
person.csv | |
import-2.adp | |
import-2.tcl | Starts the analysis process for the file imported |
import-im_budget_item.tcl | Starts the analysis process for the file imported |
import-im_company.tcl | Starts the analysis process for the file imported |
import-im_conf_item.tcl | Starts the analysis process for the file imported |
import-im_expense.tcl | Starts the analysis process for the file imported |
import-im_expense_bundle.tcl | Starts the analysis process for the file imported |
import-im_hour.tcl | |
import-im_invoice.tcl | Starts the analysis process for the file imported |
import-im_membership.tcl | Starts the analysis process for the file imported |
import-im_project.tcl | Starts the analysis process for the file imported |
import-im_risk.tcl | Starts the analysis process for the file imported |
import-im_ticket.tcl | Starts the analysis process for the file imported |
import-person.tcl | Starts the analysis process for the file imported |
import-rels.tcl | Starts the analysis process for the file imported |
index.adp | |
index.tcl | Serve the user a form to upload a new file or URL |
reports/ | |
export-projects.tcl | Report listing all main projects in the system with all available fields + DynFields from projects and customers |
\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] # --------------------------------------------------------------------- # Code set check - detects wrong unicode chars or the beginning of an Excel file... # --------------------------------------------------------------------- set max_char 0 set hex_chars {} foreach char [split $header ""] { set c [scan $char %c] if {$c > $max_char} { set max_char $c } lappend hex_chars [format "%X" $c] } # ad_return_complaint 1 "sep='$separator',
\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_budget_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 "
[ad_print_stack_trace]\n" } continue } } else { if {$ns_write_p} { ns_write "
$err_msg" } continue } # Add the budget_item lead to the list of budget_item members if {"" != $budget_item_owner_id} { set role_id [im_biz_object_role_full_member] im_biz_object_add_role $budget_item_owner_id $budget_item_id $role_id } # ------------------------------------------------------- # Import DynFields set budget_item_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_budget_item: 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_budget_item: name=$attribute_name already exists." continue } set attributes_hash($key) $table_name switch $table_name { im_budget_items { lappend budget_item_dynfield_updates "$attribute_name = :$attribute_name" } default { ad_return_complaint 1 "Dynfield Budgetiguration Error:
$err_msg" } } } if {$ns_write_p} { ns_write "
\n"
ns_write "Return to Budget_Item Page\n"
}
# ------------------------------------------------------------
# Render Report Footer
if {$ns_write_p} {
ns_write [im_footer]
}
intranet-csv-import-v5-0-4-0-0/www/import-im_company.tcl 0000664 0000000 0000000 00000031443 13751511246 0023130 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'.
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 ('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-4-0-0/www/import-im_conf_item.tcl 0000664 0000000 0000000 00000034136 13751511246 0023427 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-4-0-0/www/import-im_expense.tcl 0000775 0000000 0000000 00000043255 13751511246 0023140 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-4-0-0/www/import-im_expense_bundle.tcl 0000775 0000000 0000000 00000033074 13751511246 0024467 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-4-0-0/www/import-im_hour.tcl 0000775 0000000 0000000 00000042574 13751511246 0022451 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-4-0-0/www/import-im_membership.tcl 0000775 0000000 0000000 00000023700 13751511246 0023615 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-4-0-0/www/import-im_project.tcl 0000664 0000000 0000000 00000065137 13751511246 0023137 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 {0 && !$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 {"" eq $project_nr && "" eq $parent_nrs} { 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-4-0-0/www/import-im_risk.tcl 0000664 0000000 0000000 00000025371 13751511246 0022435 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-4-0-0/www/import-im_ticket.tcl 0000664 0000000 0000000 00000040615 13751511246 0022746 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-4-0-0/www/import-person.tcl 0000775 0000000 0000000 00000055620 13751511246 0022311 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]
set user_is_admin_p [im_is_user_site_wide_or_intranet_admin $current_user_id]
set add_users_p [im_permission $current_user_id "add_users"]
if {!$add_users_p} {
ad_return_complaint 1 "You don't have permissions to create new user."
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] # 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 "
$sql
$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-4-0-0/www/import-rels.tcl 0000775 0000000 0000000 00000015634 13751511246 0021751 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-4-0-0/www/index.adp 0000664 0000000 0000000 00000024644 13751511246 0020563 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-4-0-0/www/index.tcl 0000775 0000000 0000000 00000005436 13751511246 0020602 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 Absences') as report_id_export_absemces,
(select report_id from im_reports where report_name = 'Export Budget Items') as report_id_export_budget_items,
(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 Conf Items') as report_id_export_conf_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 Finance Documents') as report_id_export_finance_documents,
(select report_id from im_reports where report_name = 'Export Hours') as report_id_export_hours,
(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 Persons') as report_id_export_persons,
(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 Projects') as report_id_export_projects,
(select report_id from im_reports where report_name = 'Export Risks') as report_id_export_risks,
(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 Tickets') as report_id_export_tickets
from dual
"
if { "" eq $return_url } { set return_url "/intranet-csv-import/" }
intranet-csv-import-v5-0-4-0-0/www/js/ 0000775 0000000 0000000 00000000000 13751511246 0017370 5 ustar 00root root 0000000 0000000 intranet-csv-import-v5-0-4-0-0/www/js/jquery-save-as-you-type/ 0000775 0000000 0000000 00000000000 13751511246 0024035 5 ustar 00root root 0000000 0000000 intranet-csv-import-v5-0-4-0-0/www/js/jquery-save-as-you-type/dependencies/ 0000775 0000000 0000000 00000000000 13751511246 0026463 5 ustar 00root root 0000000 0000000 intranet-csv-import-v5-0-4-0-0/www/js/jquery-save-as-you-type/dependencies/jquery-cookie.js 0000664 0000000 0000000 00000003056 13751511246 0031613 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-4-0-0/www/js/jquery-save-as-you-type/source/ 0000775 0000000 0000000 00000000000 13751511246 0025335 5 ustar 00root root 0000000 0000000 intranet-csv-import-v5-0-4-0-0/www/js/jquery-save-as-you-type/source/sayt.min.jquery.js 0000664 0000000 0000000 00000010605 13751511246 0030755 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-4-0-0/www/reports/ 0000775 0000000 0000000 00000000000 13751511246 0020452 5 ustar 00root root 0000000 0000000 intranet-csv-import-v5-0-4-0-0/www/reports/export-projects.tcl 0000664 0000000 0000000 00000027255 13751511246 0024341 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 = 'im_budget_item'" -default ""]] %>
Budget Item
<%= [im_gif csv-doc] %>
Please note that this import requires exact naming of column headers.
Budget 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_company'" -default ""]] %>
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_conf_item'" -default ""]] %>
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 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_invoice'" -default ""]] %>
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.
Financial document lines can be imported together with the headers (please see the example document).
Cost 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_gantt_task'" -default "" -default ""]] %>
Gantt Task
<%= [im_gif csv-doc] %>
<%= [im_gif database_table] %>
Allows to import Timesheet Tasks separately from projects/sub-projects.
<%= [im_gif [db_string gif "select object_type_gif from acs_object_types where object_type = 'person'" -default ""]] %>
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_project'" -default ""]] %>
Project
<%= [im_gif csv-doc] %>
<%= [im_gif database_table] %>
Project 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_risk'" -default ""]] %>
Risk
<%= [im_gif csv-doc] %>
Risk DynFields are also recognized by their "pretty name" or "column name".
<%= [im_gif hourglass] %>
Timesheet Hour
<%= [im_gif csv-doc] %>
<%= [im_gif database_table] %>
DynFields for hours are not supported.
<%= [im_gif [db_string gif "select object_type_gif from acs_object_types where object_type = 'im_ticket'" -default ""]] %>
Tickets
<%= [im_gif csv-doc] %>
<%= [im_gif database_table] %>
Ticket 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_user_absence'" -default ""]] %>
User Absence
Absence 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 reporting]
\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"}
}