function dcl_upgrade0_9_4_4()
{
// And you thought dcl_upgrade0_9_3 was nasty???? You ain't seen nothin' yet...
function dcl_upgrade0_9_4_4_write_message($sMessage)
{
global $phpgw_setup;
if (!$phpgw_setup->oProc->m_bDeltaOnly) {
echo $sMessage;
flush();
}
}
global $phpgw_setup, $setup_info, $dcl_domain, $dcl_domain_info;
$sType = $dcl_domain_info[$dcl_domain]['dbType'];
$phpgw_setup->oProc->DropPrimaryKey('dcl_wiki');
$phpgw_setup->oProc->CreatePrimaryKey('dcl_wiki', array('dcl_entity_type_id', 'dcl_entity_id', 'dcl_entity_id2', 'page_name'));
if ($sType == 'pgsql') {
// Fix table defaults for auto fields
$aTables = array('departments' => 'id', 'faq' => 'faqid', 'faqtopics' => 'topicid', 'faqquestions' => 'questionid', 'faqanswers' => 'answerid');
foreach ($aTables as $sTable => $sField) {
$phpgw_setup->oProc->Query("ALTER TABLE {$sTable} ALTER {$sField} SET DEFAULT nextval('seq_{$sTable}')");
}
}
$phpgw_setup->oProc->AddColumn('products', 'is_versioned', array('type' => 'char', 'precision' => 1, 'nullable' => false, 'default' => 'N'));
$phpgw_setup->oProc->CreateTable('dcl_product_build', array('fd' => array('product_build_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'product_version_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'product_build_descr' => array('type' => 'varchar', 'precision' => 50, 'nullable' => false), 'product_build_on' => array('type' => 'timestamp', 'nullable' => false)), 'pk' => array('product_build_id'), 'fk' => array(), 'ix' => array(), 'uc' => array('uc_dcl_product_build_descr' => array('product_build_descr'))));
$phpgw_setup->oProc->CreateTable('dcl_product_build_except', array('fd' => array('product_build_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'entity_type_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'entity_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'entity_id2' => array('type' => 'int', 'precision' => 4, 'nullable' => false)), 'pk' => array('product_build_id', 'entity_type_id', 'entity_id', 'entity_id2'), 'fk' => array(), 'ix' => array(), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_product_build_sccs', array('fd' => array('product_build_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'sccs_xref_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false)), 'pk' => array('product_build_id', 'sccs_xref_id'), 'fk' => array(), 'ix' => array(), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_product_version', array('fd' => array('product_version_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'product_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'product_version_text' => array('type' => 'varchar', 'precision' => 50, 'nullable' => false), 'product_version_descr' => array('type' => 'varchar', 'precision' => 100, 'nullable' => false), 'product_version_target_date' => array('type' => 'timestamp', 'nullable' => false), 'product_version_actual_date' => array('type' => 'timestamp', 'nullable' => true)), 'pk' => array('product_version_id'), 'fk' => array(), 'ix' => array(), 'uc' => array('uc_dcl_product_version_text' => array('product_version_text'))));
$phpgw_setup->oProc->CreateTable('dcl_product_version_item', array('fd' => array('product_version_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'entity_type_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'entity_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'entity_id2' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'version_status_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'version_item_submit_on' => array('type' => 'timestamp', 'nullable' => true), 'version_item_apply_on' => array('type' => 'timestamp', 'nullable' => true)), 'pk' => array('product_version_id', 'entity_type_id', 'entity_id', 'entity_id2'), 'fk' => array(), 'ix' => array(), 'uc' => array()));
$phpgw_setup->oProc->AlterColumn('personnel', 'short', array('type' => 'varchar', 'precision' => 25, 'nullable' => false));
// Tidy up the short field
$phpgw_setup->oProc->Query('update personnel set short = rtrim(short)');
// Adding support for public interfaces and ticket/work order sources
$phpgw_setup->oProc->CreateTable('dcl_entity_source', array('fd' => array('entity_source_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'entity_source_name' => array('type' => 'varchar', 'precision' => 50, 'nullable' => false), 'active' => array('type' => 'char', 'precision' => 1, 'default' => 'Y', 'nullable' => false)), 'pk' => array('entity_source_id'), 'fk' => array(), 'ix' => array(), 'uc' => array()));
$phpgw_setup->oProc->AddColumn('workorders', 'entity_source_id', array('type' => 'int', 'precision' => 4));
$phpgw_setup->oProc->AddColumn('workorders', 'is_public', array('type' => 'char', 'precision' => 1, 'nullable' => false, 'default' => 'N'));
$phpgw_setup->oProc->AddColumn('tickets', 'entity_source_id', array('type' => 'int', 'precision' => 4));
$phpgw_setup->oProc->AddColumn('tickets', 'is_public', array('type' => 'char', 'precision' => 1, 'nullable' => false, 'default' => 'N'));
$phpgw_setup->oProc->AddColumn('timecards', 'is_public', array('type' => 'char', 'precision' => 1, 'nullable' => false, 'default' => 'N'));
$phpgw_setup->oProc->AddColumn('ticketresolutions', 'is_public', array('type' => 'char', 'precision' => 1, 'nullable' => false, 'default' => 'N'));
$phpgw_setup->oProc->AddColumn('products', 'is_public', array('type' => 'char', 'precision' => 1, 'nullable' => false, 'default' => 'N'));
$phpgw_setup->oProc->Query("insert into dcl_config (dcl_config_name, dcl_config_field, dcl_config_varchar) values ('DCL_BUILD_MANAGER_ENABLED', 'dcl_config_varchar', 'N')");
$phpgw_setup->oProc->Query("insert into dcl_config (dcl_config_name, dcl_config_field, dcl_config_varchar) values ('DCL_SMTP_AUTH_REQUIRED', 'dcl_config_varchar', 'N')");
$phpgw_setup->oProc->Query("insert into dcl_config (dcl_config_name, dcl_config_field, dcl_config_varchar) values ('DCL_SMTP_AUTH_USER', 'dcl_config_varchar', 'smtp_user')");
$phpgw_setup->oProc->Query("insert into dcl_config (dcl_config_name, dcl_config_field, dcl_config_varchar) values ('DCL_SMTP_AUTH_PWD', 'dcl_config_varchar', 'smtp_pass')");
$phpgw_setup->oProc->CreateTable('dcl_addr_type', array('fd' => array('addr_type_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'addr_type_name' => array('type' => 'varchar', 'precision' => 30, 'nullable' => false)), 'pk' => array('addr_type_id'), 'fk' => array(), 'ix' => array(), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_email_type', array('fd' => array('email_type_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'email_type_name' => array('type' => 'varchar', 'precision' => 30, 'nullable' => false)), 'pk' => array('email_type_id'), 'fk' => array(), 'ix' => array(), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_note_type', array('fd' => array('note_type_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'note_type_name' => array('type' => 'varchar', 'precision' => 30, 'nullable' => false)), 'pk' => array('note_type_id'), 'fk' => array(), 'ix' => array(), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_phone_type', array('fd' => array('phone_type_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'phone_type_name' => array('type' => 'varchar', 'precision' => 30, 'nullable' => false)), 'pk' => array('phone_type_id'), 'fk' => array(), 'ix' => array(), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_url_type', array('fd' => array('url_type_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'url_type_name' => array('type' => 'varchar', 'precision' => 30, 'nullable' => false)), 'pk' => array('url_type_id'), 'fk' => array(), 'ix' => array(), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_org_type', array('fd' => array('org_type_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'org_type_name' => array('type' => 'varchar', 'precision' => 30, 'nullable' => false)), 'pk' => array('org_type_id'), 'fk' => array(), 'ix' => array(), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_contact_type', array('fd' => array('contact_type_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'contact_type_name' => array('type' => 'varchar', 'precision' => 30, 'nullable' => false)), 'pk' => array('contact_type_id'), 'fk' => array(), 'ix' => array(), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_contact', array('fd' => array('contact_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'first_name' => array('type' => 'varchar', 'precision' => 50, 'nullable' => false), 'last_name' => array('type' => 'varchar', 'precision' => 50, 'nullable' => false), 'middle_name' => array('type' => 'varchar', 'precision' => 50, 'nullable' => true), 'active' => array('type' => 'varchar', 'precision' => 30, 'nullable' => false, 'default' => 'Y'), 'created_on' => array('type' => 'timestamp', 'nullable' => false), 'created_by' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'modified_on' => array('type' => 'timestamp', 'nullable' => true), 'modified_by' => array('type' => 'int', 'precision' => 4, 'nullable' => true)), 'pk' => array('contact_id'), 'fk' => array(), 'ix' => array('ix_dcl_contact_name_id' => array('last_name', 'first_name', 'contact_id')), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_contact_addr', array('fd' => array('contact_addr_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'contact_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'addr_type_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'add1' => array('type' => 'varchar', 'precision' => 50, 'nullable' => true), 'add2' => array('type' => 'varchar', 'precision' => 50, 'nullable' => true), 'city' => array('type' => 'varchar', 'precision' => 50, 'nullable' => true), 'state' => array('type' => 'varchar', 'precision' => 30, 'nullable' => true), 'zip' => array('type' => 'varchar', 'precision' => 20, 'nullable' => true), 'country' => array('type' => 'varchar', 'precision' => 40, 'nullable' => true), 'preferred' => array('type' => 'char', 'precision' => 1, 'default' => 'N', 'nullable' => false), 'created_on' => array('type' => 'timestamp', 'nullable' => false), 'created_by' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'modified_on' => array('type' => 'timestamp', 'nullable' => true), 'modified_by' => array('type' => 'int', 'precision' => 4, 'nullable' => true)), 'pk' => array('contact_addr_id'), 'fk' => array(), 'ix' => array('ix_dcl_contact_addr_contact' => array('contact_id')), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_contact_email', array('fd' => array('contact_email_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'contact_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'email_type_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'email_addr' => array('type' => 'varchar', 'precision' => 100, 'nullable' => true), 'preferred' => array('type' => 'char', 'precision' => 1, 'default' => 'N', 'nullable' => false), 'created_on' => array('type' => 'timestamp', 'nullable' => false), 'created_by' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'modified_on' => array('type' => 'timestamp', 'nullable' => true), 'modified_by' => array('type' => 'int', 'precision' => 4, 'nullable' => true)), 'pk' => array('contact_email_id'), 'fk' => array(), 'ix' => array('ix_dcl_contact_email_contact' => array('contact_id')), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_contact_note', array('fd' => array('contact_note_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'contact_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'note_type_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'note_text' => array('type' => 'text', 'nullable' => false), 'created_on' => array('type' => 'timestamp', 'nullable' => false), 'created_by' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'modified_on' => array('type' => 'timestamp', 'nullable' => true), 'modified_by' => array('type' => 'int', 'precision' => 4, 'nullable' => true)), 'pk' => array('contact_note_id'), 'fk' => array(), 'ix' => array('ix_dcl_contact_note_contact' => array('contact_id')), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_contact_phone', array('fd' => array('contact_phone_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'contact_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'phone_type_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'phone_number' => array('type' => 'varchar', 'precision' => 30, 'nullable' => false), 'preferred' => array('type' => 'char', 'precision' => 1, 'default' => 'N', 'nullable' => false), 'created_on' => array('type' => 'timestamp', 'nullable' => false), 'created_by' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'modified_on' => array('type' => 'timestamp', 'nullable' => true), 'modified_by' => array('type' => 'int', 'precision' => 4, 'nullable' => true)), 'pk' => array('contact_phone_id'), 'fk' => array(), 'ix' => array('ix_dcl_contact_phone_contact' => array('contact_id')), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_contact_type_xref', array('fd' => array('contact_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'contact_type_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false)), 'pk' => array('contact_id', 'contact_type_id'), 'fk' => array(), 'ix' => array(), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_contact_url', array('fd' => array('contact_url_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'contact_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'url_type_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'url_addr' => array('type' => 'varchar', 'precision' => 150, 'nullable' => false), 'preferred' => array('type' => 'char', 'precision' => 1, 'default' => 'N', 'nullable' => false), 'created_on' => array('type' => 'timestamp', 'nullable' => false), 'created_by' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'modified_on' => array('type' => 'timestamp', 'nullable' => true), 'modified_by' => array('type' => 'int', 'precision' => 4, 'nullable' => true)), 'pk' => array('contact_url_id'), 'fk' => array(), 'ix' => array('ix_dcl_contact_url_contact' => array('contact_id')), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_org', array('fd' => array('org_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'name' => array('type' => 'varchar', 'precision' => 50, 'nullable' => false), 'active' => array('type' => 'varchar', 'precision' => 1, 'default' => 'Y', 'nullable' => false), 'parent' => array('type' => 'int', 'precision' => 4, 'nullable' => true), 'created_on' => array('type' => 'timestamp', 'nullable' => false), 'created_by' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'modified_on' => array('type' => 'timestamp', 'nullable' => true), 'modified_by' => array('type' => 'int', 'precision' => 4, 'nullable' => true)), 'pk' => array('org_id'), 'fk' => array(), 'ix' => array('ix_dcl_org_name_id' => array('name', 'org_id')), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_org_addr', array('fd' => array('org_addr_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'org_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'addr_type_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'add1' => array('type' => 'varchar', 'precision' => 50, 'nullable' => true), 'add2' => array('type' => 'varchar', 'precision' => 50, 'nullable' => true), 'city' => array('type' => 'varchar', 'precision' => 50, 'nullable' => true), 'state' => array('type' => 'varchar', 'precision' => 30, 'nullable' => true), 'zip' => array('type' => 'varchar', 'precision' => 20, 'nullable' => true), 'country' => array('type' => 'varchar', 'precision' => 40, 'nullable' => true), 'preferred' => array('type' => 'char', 'precision' => 1, 'default' => 'N', 'nullable' => false), 'created_on' => array('type' => 'timestamp', 'nullable' => false), 'created_by' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'modified_on' => array('type' => 'timestamp', 'nullable' => true), 'modified_by' => array('type' => 'int', 'precision' => 4, 'nullable' => true)), 'pk' => array('org_addr_id'), 'fk' => array(), 'ix' => array('ix_dcl_org_addr_org' => array('org_id')), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_org_alias', array('fd' => array('org_alias_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'org_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'alias' => array('type' => 'varchar', 'precision' => 50, 'nullable' => true), 'created_on' => array('type' => 'timestamp', 'nullable' => false), 'created_by' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'modified_on' => array('type' => 'timestamp', 'nullable' => true), 'modified_by' => array('type' => 'int', 'precision' => 4, 'nullable' => true)), 'pk' => array('org_alias_id'), 'fk' => array(), 'ix' => array('ix_dcl_org_alias_org' => array('org_id')), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_org_contact', array('fd' => array('org_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'contact_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'created_on' => array('type' => 'timestamp', 'nullable' => false), 'created_by' => array('type' => 'int', 'precision' => 4, 'nullable' => false)), 'pk' => array('org_id', 'contact_id'), 'fk' => array(), 'ix' => array('ix_dcl_org_contact_contact' => array('contact_id')), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_org_email', array('fd' => array('org_email_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'org_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'email_type_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'email_addr' => array('type' => 'varchar', 'precision' => 100, 'nullable' => true), 'preferred' => array('type' => 'char', 'precision' => 1, 'default' => 'N', 'nullable' => false), 'created_on' => array('type' => 'timestamp', 'nullable' => false), 'created_by' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'modified_on' => array('type' => 'timestamp', 'nullable' => true), 'modified_by' => array('type' => 'int', 'precision' => 4, 'nullable' => true)), 'pk' => array('org_email_id'), 'fk' => array(), 'ix' => array('ix_dcl_org_email_org' => array('org_id')), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_org_note', array('fd' => array('org_note_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'org_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'note_type_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'note_text' => array('type' => 'text', 'nullable' => false), 'created_on' => array('type' => 'timestamp', 'nullable' => false), 'created_by' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'modified_on' => array('type' => 'timestamp', 'nullable' => true), 'modified_by' => array('type' => 'int', 'precision' => 4, 'nullable' => true)), 'pk' => array('org_note_id'), 'fk' => array(), 'ix' => array('ix_dcl_org_note_org' => array('org_id')), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_org_phone', array('fd' => array('org_phone_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'org_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'phone_type_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'phone_number' => array('type' => 'varchar', 'precision' => 30, 'nullable' => false), 'preferred' => array('type' => 'char', 'precision' => 1, 'default' => 'N', 'nullable' => false), 'created_on' => array('type' => 'timestamp', 'nullable' => false), 'created_by' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'modified_on' => array('type' => 'timestamp', 'nullable' => true), 'modified_by' => array('type' => 'int', 'precision' => 4, 'nullable' => true)), 'pk' => array('org_phone_id'), 'fk' => array(), 'ix' => array('ix_dcl_org_phone_org' => array('org_id')), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_org_type_xref', array('fd' => array('org_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'org_type_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false)), 'pk' => array('org_id', 'org_type_id'), 'fk' => array(), 'ix' => array(), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_org_url', array('fd' => array('org_url_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'org_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'url_type_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'url_addr' => array('type' => 'varchar', 'precision' => 150, 'nullable' => false), 'preferred' => array('type' => 'char', 'precision' => 1, 'default' => 'N', 'nullable' => false), 'created_on' => array('type' => 'timestamp', 'nullable' => false), 'created_by' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'modified_on' => array('type' => 'timestamp', 'nullable' => true), 'modified_by' => array('type' => 'int', 'precision' => 4, 'nullable' => true)), 'pk' => array('org_url_id'), 'fk' => array(), 'ix' => array('ix_dcl_org_url_org' => array('org_id')), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_projects_audit', array('fd' => array('projectid' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'name' => array('type' => 'varchar', 'precision' => 100), 'reportto' => array('type' => 'int', 'precision' => 4), 'createdby' => array('type' => 'int', 'precision' => 4), 'createdon' => array('type' => 'timestamp'), 'projectdeadline' => array('type' => 'date'), 'description' => array('type' => 'text'), 'status' => array('type' => 'int', 'precision' => 4), 'lastactivity' => array('type' => 'timestamp'), 'finalclose' => array('type' => 'date'), 'parentprojectid' => array('type' => 'int', 'precision' => 4), 'audit_on' => array('type' => 'timestamp', 'nullable' => false), 'audit_by' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'audit_version' => array('type' => 'int', 'precision' => 4, 'nullable' => false)), 'pk' => array('projectid', 'audit_version'), 'fk' => array(), 'ix' => array(), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_wo_account_audit', array('fd' => array('wo_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'seq' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'account_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'audit_on' => array('type' => 'timestamp', 'nullable' => false), 'audit_by' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'audit_type' => array('type' => 'int', 'precision' => 4, 'nullable' => false)), 'pk' => array(), 'fk' => array(), 'ix' => array('ix_dcl_wo_account_audit' => array('wo_id', 'seq', 'account_id')), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('projectmap_audit', array('fd' => array('projectid' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'jcn' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'seq' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'audit_on' => array('type' => 'timestamp', 'nullable' => false), 'audit_by' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'audit_type' => array('type' => 'int', 'precision' => 4, 'nullable' => false)), 'pk' => array('projectid', 'jcn', 'seq'), 'fk' => array(), 'ix' => array('ix_projectmap_audit' => array('projectid', 'jcn', 'seq'), 'ix_projectmap_audit_wo' => array('jcn', 'seq')), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('tickets_audit', array('fd' => array('ticketid' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'product' => array('type' => 'int', 'precision' => 4), 'account' => array('type' => 'int', 'precision' => 4), 'createdby' => array('type' => 'int', 'precision' => 4), 'createdon' => array('type' => 'timestamp'), 'responsible' => array('type' => 'int', 'precision' => 4), 'closedby' => array('type' => 'int', 'precision' => 4), 'closedon' => array('type' => 'timestamp'), 'status' => array('type' => 'int', 'precision' => 4), 'statuson' => array('type' => 'timestamp'), 'lastactionon' => array('type' => 'timestamp'), 'priority' => array('type' => 'int', 'precision' => 4), 'type' => array('type' => 'int', 'precision' => 4), 'issue' => array('type' => 'text'), 'version' => array('type' => 'varchar', 'precision' => 20), 'summary' => array('type' => 'varchar', 'precision' => 100), 'seconds' => array('type' => 'int', 'precision' => 4), 'module_id' => array('type' => 'int', 'precision' => 4), 'entity_source_id' => array('type' => 'int', 'precision' => 4), 'is_public' => array('type' => 'char', 'precision' => 1), 'contact_id' => array('type' => 'int', 'precision' => 4), 'audit_on' => array('type' => 'timestamp', 'nullable' => false), 'audit_by' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'audit_version' => array('type' => 'int', 'precision' => 4, 'nullable' => false)), 'pk' => array('ticketid', 'audit_version'), 'fk' => array(), 'ix' => array(), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('workorders_audit', array('fd' => array('jcn' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'seq' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'product' => array('type' => 'int', 'precision' => 4), 'createby' => array('type' => 'int', 'precision' => 4), 'createdon' => array('type' => 'timestamp'), 'closedby' => array('type' => 'int', 'precision' => 4), 'closedon' => array('type' => 'date'), 'status' => array('type' => 'int', 'precision' => 4), 'statuson' => array('type' => 'timestamp'), 'lastactionon' => array('type' => 'timestamp'), 'deadlineon' => array('type' => 'date'), 'eststarton' => array('type' => 'date'), 'estendon' => array('type' => 'date'), 'starton' => array('type' => 'date'), 'esthours' => array('type' => 'float', 'precision' => 8), 'totalhours' => array('type' => 'float', 'precision' => 8), 'priority' => array('type' => 'int', 'precision' => 4), 'severity' => array('type' => 'int', 'precision' => 4), 'summary' => array('type' => 'varchar', 'precision' => 100), 'notes' => array('type' => 'text'), 'description' => array('type' => 'text'), 'responsible' => array('type' => 'int', 'precision' => 4), 'revision' => array('type' => 'varchar', 'precision' => 20), 'etchours' => array('type' => 'float', 'precision' => 8), 'module_id' => array('type' => 'int', 'precision' => 4), 'wo_type_id' => array('type' => 'int', 'precision' => 4), 'entity_source_id' => array('type' => 'int', 'precision' => 4), 'is_public' => array('type' => 'char', 'precision' => 1), 'contact_id' => array('type' => 'int', 'precision' => 4), 'audit_on' => array('type' => 'timestamp', 'nullable' => false), 'audit_by' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'audit_version' => array('type' => 'int', 'precision' => 4, 'nullable' => false)), 'pk' => array('jcn', 'seq', 'audit_version'), 'fk' => array(), 'ix' => array(), 'uc' => array()));
$phpgw_setup->oProc->CreateTable('dcl_entity', array('fd' => array('entity_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'entity_desc' => array('type' => 'varchar', 'precision' => 50, 'nullable' => false), 'active' => array('type' => 'char', 'precision' => 1, 'nullable' => false)), 'pk' => array('entity_id'), 'fk' => array(), 'ix' => array(), 'uc' => array()));
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (0, 'Global', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (1, 'Project', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (2, 'Work Order', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (3, 'Ticket', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (4, 'Product', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (5, 'Organization', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (6, 'Department', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (7, 'Personnel', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (8, 'Action', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (9, 'Status', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (10, 'Priority', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (11, 'Severity', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (12, 'Time Card', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (13, 'Ticket Resolution', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (14, 'Contact', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (15, 'FAQ', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (16, 'FAQ Topic', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (17, 'FAQ Question', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (18, 'FAQ Answer', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (19, 'Form', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (20, 'Administration', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (21, 'Attribute Set', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (22, 'Form Template', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (23, 'Address Type', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (24, 'E-Mail Type', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (25, 'Note Type', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (26, 'Phone Type', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (27, 'URL Type', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (28, 'Source', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (29, 'Lookup', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (30, 'Preferences', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (31, 'Product Module', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (32, 'Saved Search', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (33, 'Work Order Type', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (34, 'ChangeLog', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (35, 'Session', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity VALUES (36, 'Role', 'Y')");
$phpgw_setup->oProc->CreateTable('dcl_perm', array('fd' => array('perm_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'perm_desc' => array('type' => 'varchar', 'precision' => 50, 'nullable' => false), 'active' => array('type' => 'char', 'precision' => 1, 'nullable' => false)), 'pk' => array('perm_id'), 'fk' => array(), 'ix' => array(), 'uc' => array()));
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (0, 'Administration', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (1, 'Add', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (2, 'Modify', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (3, 'Delete', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (4, 'View', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (5, 'View (Private)', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (6, 'View (Organization)', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (7, 'View (Submitted)', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (8, 'Copy To Work Order', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (9, 'Assign', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (10, 'Action', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (11, 'Change Password', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (12, 'Import', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (13, 'Search', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (14, 'Schedule', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (15, 'Report', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (16, 'Add Task', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (17, 'Remove Task', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (18, 'Attach File', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (19, 'Remove File', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (20, 'View Wiki', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (21, 'Public Items Only', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (22, 'View File Attachment', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_perm VALUES (23, 'Audit', 'Y')");
$phpgw_setup->oProc->CreateTable('dcl_entity_perm', array('fd' => array('entity_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'perm_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false)), 'pk' => array('entity_id', 'perm_id'), 'fk' => array(), 'ix' => array(), 'uc' => array()));
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (0, 0)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (0,21)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (1,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (1,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (1,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (1,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (1,9)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (1,16)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (1,17)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (1,18)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (1,19)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (1,20)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (1,22)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (1,23)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (2,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (2,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (2,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (2,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (2,6)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (2,7)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (2,9)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (2,10)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (2,12)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (2,13)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (2,14)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (2,15)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (2,18)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (2,19)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (2,20)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (2,22)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (2,23)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (3,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (3,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (3,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (3,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (3,6)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (3,7)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (3,8)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (3,9)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (3,10)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (3,12)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (3,13)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (3,15)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (3,18)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (3,19)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (3,20)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (3,22)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (3,23)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (4,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (4,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (4,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (4,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (4,20)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (5,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (5,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (5,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (5,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (5,20)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (6,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (6,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (6,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (6,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (6,20)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (7,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (7,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (7,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (7,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (7,11)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (8,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (8,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (8,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (8,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (9,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (9,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (9,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (9,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (10,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (10,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (10,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (10,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (11,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (11,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (11,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (11,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (12,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (12,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (12,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (13,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (13,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (13,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (14,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (14,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (14,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (14,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (15,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (15,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (15,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (15,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (16,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (16,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (16,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (17,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (17,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (17,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (18,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (18,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (18,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (19,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (19,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (19,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (19,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (19,9)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (20,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (20,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (20,11)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (21,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (21,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (21,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (21,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (22,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (22,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (22,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (22,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (23,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (23,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (23,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (23,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (24,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (24,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (24,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (24,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (25,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (25,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (25,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (25,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (26,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (26,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (26,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (26,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (27,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (27,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (27,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (27,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (28,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (28,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (28,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (28,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (29,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (29,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (29,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (29,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (30,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (30,11)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (31,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (31,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (31,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (31,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (32,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (32,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (32,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (32,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (33,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (33,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (33,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (33,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (34,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (34,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (34,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (34,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (35,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (35,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (36,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (36,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (36,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (36,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (37,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (37,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (37,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (37,4)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (38,1)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (38,2)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (38,3)");
$phpgw_setup->oProc->Query("INSERT INTO dcl_entity_perm VALUES (38,4)");
$phpgw_setup->oProc->CreateTable('dcl_role', array('fd' => array('role_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'role_desc' => array('type' => 'varchar', 'precision' => 50, 'nullable' => false), 'active' => array('type' => 'char', 'precision' => 1, 'nullable' => false)), 'pk' => array('role_id'), 'fk' => array(), 'ix' => array(), 'uc' => array()));
$phpgw_setup->oProc->Query("INSERT INTO dcl_role (role_desc, active) VALUES ('Administrator (Level 9)', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_role (role_desc, active) VALUES ('Guest (Level 2)', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_role (role_desc, active) VALUES ('Submit (Level 3)', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_role (role_desc, active) VALUES ('Action (Level 4)', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_role (role_desc, active) VALUES ('Assign (Level 5)', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_role (role_desc, active) VALUES ('Modify (Level 7)', 'Y')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_role (role_desc, active) VALUES ('Delete (Level 8)', 'Y')");
$phpgw_setup->oProc->CreateTable('dcl_role_perm', array('fd' => array('role_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'entity_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'perm_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false)), 'pk' => array('role_id', 'entity_id', 'perm_id'), 'fk' => array(), 'ix' => array(), 'uc' => array()));
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (1,0,0)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (2,1,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (2,1,22)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (2,2,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (2,2,13)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (2,2,22)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (2,3,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (2,3,13)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (2,3,22)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (2,12,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (2,13,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (2,15,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (2,30,11)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (3,1,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (3,1,22)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (3,2,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (3,2,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (3,2,13)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (3,2,18)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (3,2,22)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (3,3,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (3,3,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (3,3,13)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (3,3,18)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (3,3,22)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (3,12,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (3,13,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (3,15,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (3,30,11)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,1,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,1,20)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,1,22)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,2,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,2,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,2,10)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,2,13)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,2,18)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,2,20)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,2,22)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,3,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,3,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,3,10)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,3,13)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,3,18)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,3,20)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,3,22)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,5,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,5,20)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,12,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,13,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,15,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (4,30,11)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,1,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,1,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,1,9)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,1,16)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,1,17)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,1,18)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,1,19)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,1,20)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,1,22)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,2,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,2,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,2,9)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,2,10)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,2,12)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,2,13)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,2,14)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,2,15)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,2,18)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,2,19)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,2,20)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,2,22)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,3,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,3,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,3,8)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,3,9)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,3,10)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,3,12)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,3,13)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,3,15)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,3,18)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,3,19)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,3,20)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,3,22)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,4,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,4,20)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,5,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,5,20)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,6,20)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,12,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,13,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,15,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,15,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,16,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,17,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,18,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,19,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,19,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,19,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,19,9)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,22,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,30,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (5,30,11)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,1,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,1,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,1,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,1,9)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,1,16)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,1,17)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,1,18)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,1,19)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,1,20)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,1,22)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,2,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,2,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,2,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,2,9)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,2,10)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,2,12)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,2,13)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,2,14)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,2,15)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,2,18)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,2,19)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,2,20)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,2,22)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,3,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,3,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,3,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,3,8)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,3,9)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,3,10)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,3,12)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,3,13)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,3,15)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,3,18)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,3,19)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,3,20)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,3,22)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,4,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,4,20)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,5,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,5,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,5,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,5,20)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,6,20)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,12,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,12,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,13,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,13,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,14,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,14,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,14,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,15,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,15,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,15,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,16,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,16,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,17,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,17,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,18,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,18,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,19,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,19,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,19,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,19,9)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,22,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,30,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (6,30,11)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,1,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,1,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,1,3)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,1,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,1,9)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,1,16)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,1,17)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,1,18)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,1,19)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,1,20)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,1,22)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,2,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,2,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,2,3)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,2,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,2,9)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,2,10)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,2,12)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,2,13)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,2,14)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,2,15)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,2,18)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,2,19)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,2,20)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,2,22)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,3,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,3,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,3,3)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,3,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,3,8)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,3,9)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,3,10)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,3,12)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,3,13)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,3,15)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,3,18)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,3,19)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,3,20)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,3,22)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,4,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,4,20)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,5,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,5,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,5,3)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,5,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,5,20)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,6,20)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,12,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,12,3)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,12,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,13,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,13,3)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,13,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,14,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,14,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,14,3)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,14,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,15,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,15,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,15,3)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,15,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,16,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,16,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,16,3)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,17,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,17,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,17,3)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,18,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,18,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,18,3)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,19,1)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,19,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,19,3)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,19,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,19,9)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,22,4)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,30,2)');
$phpgw_setup->oProc->Query('INSERT INTO dcl_role_perm VALUES (7,30,11)');
$phpgw_setup->oProc->CreateTable('dcl_user_role', array('fd' => array('personnel_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'entity_type_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'entity_id1' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'entity_id2' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'role_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false)), 'pk' => array('personnel_id', 'entity_type_id', 'entity_id1', 'entity_id2', 'role_id'), 'fk' => array(), 'ix' => array(), 'uc' => array()));
$phpgw_setup->oProc->Query('INSERT INTO dcl_user_role SELECT id, 0, 0, 0, 1 FROM personnel WHERE security > 8');
$phpgw_setup->oProc->Query('INSERT INTO dcl_user_role SELECT id, 0, 0, 0, 2 FROM personnel WHERE security < 3');
$phpgw_setup->oProc->Query('INSERT INTO dcl_user_role SELECT id, 0, 0, 0, 3 FROM personnel WHERE security = 3');
$phpgw_setup->oProc->Query('INSERT INTO dcl_user_role SELECT id, 0, 0, 0, 4 FROM personnel WHERE security = 4');
$phpgw_setup->oProc->Query('INSERT INTO dcl_user_role SELECT id, 0, 0, 0, 5 FROM personnel WHERE security = 5');
$phpgw_setup->oProc->Query('INSERT INTO dcl_user_role SELECT id, 0, 0, 0, 6 FROM personnel WHERE security = 7 OR security = 6');
$phpgw_setup->oProc->Query('INSERT INTO dcl_user_role SELECT id, 0, 0, 0, 7 FROM personnel WHERE security = 8');
// Convert accounts table to real organization entries
$phpgw_setup->oProc->Query("INSERT INTO dcl_org_type (org_type_name) VALUES ('Converted')");
if (!$phpgw_setup->oProc->m_bDeltaOnly) {
$iOrgTypeID = $phpgw_setup->oProc->m_odb->GetLastInsertID('dcl_org_type');
}
$phpgw_setup->oProc->Query("INSERT INTO dcl_contact_type (contact_type_name) VALUES ('Converted')");
if (!$phpgw_setup->oProc->m_bDeltaOnly) {
$iContactTypeID = $phpgw_setup->oProc->m_odb->GetLastInsertID('dcl_contact_type');
}
if ($sType == 'mssql' || $sType == 'sybase') {
$phpgw_setup->oProc->Query('SET IDENTITY_INSERT dcl_org ON');
}
dcl_upgrade0_9_4_4_write_message('<br>Converting orgs...');
$phpgw_setup->oProc->Query("insert into dcl_org (org_id, name, active, created_on, created_by) select id, name, active, " . $phpgw_setup->oProc->m_odb->GetDateSQL() . ", 1 from accounts");
$phpgw_setup->oProc->UpdateSequence('dcl_org', 'org_id');
if ($sType == 'mssql' || $sType == 'sybase') {
$phpgw_setup->oProc->Query('SET IDENTITY_INSERT dcl_org OFF');
}
if (!$phpgw_setup->oProc->m_bDeltaOnly) {
$phpgw_setup->oProc->Query("insert into dcl_org_alias (org_id, alias, created_on, created_by) select id, short, " . $phpgw_setup->oProc->m_odb->GetDateSQL() . ", 1 from accounts WHERE short IS NOT NULL AND short != ''");
$phpgw_setup->oProc->Query("insert into dcl_phone_type (phone_type_name) values ('Voice')");
$phpgw_setup->oProc->Query("insert into dcl_org_phone (org_id, phone_type_id, phone_number, preferred, created_on, created_by) select id, 1, voice, 'Y', " . $phpgw_setup->oProc->m_odb->GetDateSQL() . ", 1 from accounts where voice is not null and voice != ''");
$phpgw_setup->oProc->Query("insert into dcl_phone_type (phone_type_name) values ('Fax')");
$phpgw_setup->oProc->Query("insert into dcl_org_phone (org_id, phone_type_id, phone_number, preferred, created_on, created_by) select id, 2, fax, 'N', " . $phpgw_setup->oProc->m_odb->GetDateSQL() . ", 1 from accounts where fax is not null and fax != ''");
$phpgw_setup->oProc->Query("insert into dcl_phone_type (phone_type_name) values ('Data1')");
$phpgw_setup->oProc->Query("insert into dcl_org_phone (org_id, phone_type_id, phone_number, preferred, created_on, created_by) select id, 3, data1, 'N', " . $phpgw_setup->oProc->m_odb->GetDateSQL() . ", 1 from accounts where data1 is not null and data1 != ''");
$phpgw_setup->oProc->Query("insert into dcl_phone_type (phone_type_name) values ('Data2')");
$phpgw_setup->oProc->Query("insert into dcl_org_phone (org_id, phone_type_id, phone_number, preferred, created_on, created_by) select id, 4, data2, 'N', " . $phpgw_setup->oProc->m_odb->GetDateSQL() . ", 1 from accounts where data2 is not null and data2 != ''");
$phpgw_setup->oProc->Query("insert into dcl_addr_type (addr_type_name) values ('Default')");
$phpgw_setup->oProc->Query("insert into dcl_org_addr (org_id, addr_type_id, add1, add2, city, state, zip, preferred, created_on, created_by) select id, 1, add1, add2, city, state, zip, 'Y', " . $phpgw_setup->oProc->m_odb->GetDateSQL() . ", 1 from accounts where (add1 is not null and add1 != '') or (add2 is not null and add2 != '') or (city is not null and city != '') or (state is not null and state != '') or (zip is not null and zip != '')");
$phpgw_setup->oProc->Query("insert into dcl_note_type (note_type_name) values ('Converted')");
$phpgw_setup->oProc->Query("insert into dcl_org_note (org_id, note_type_id, note_text, created_on, created_by) select id, 1, notes, " . $phpgw_setup->oProc->m_odb->GetDateSQL() . ", 1 from accounts where notes is not null and notes not like ''");
$phpgw_setup->oProc->Query("INSERT INTO dcl_org_type_xref SELECT org_id, {$iOrgTypeID} FROM dcl_org");
}
$phpgw_setup->oProc->DropTable('accounts');
// Now is the time to convert the contacts
if (!$phpgw_setup->oProc->m_bDeltaOnly) {
// Temporary working table
$phpgw_setup->oProc->CreateTable('dcl_contact_cnv', array('fd' => array('cnv_id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'entity_type_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'entity_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'entity_id2' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'contactname' => array('type' => 'varchar', 'precision' => 80), 'contactphone' => array('type' => 'varchar', 'precision' => 20), 'contactemail' => array('type' => 'varchar', 'precision' => 100), 'lastname' => array('type' => 'varchar', 'precision' => 50), 'firstname' => array('type' => 'varchar', 'precision' => 50), 'contactphone_num' => array('type' => 'varchar', 'precision' => 20), 'contact_id' => array('type' => 'int', 'precision' => 4)), 'pk' => array('cnv_id'), 'fk' => array(), 'ix' => array('ix_dcl_contact_cnv_1' => array('lastname', 'firstname', 'contactphone_num'), 'ix_dcl_contact_cnv_2' => array('entity_type_id')), 'uc' => array()));
// users
dcl_upgrade0_9_4_4_write_message('<br>Getting user contacts...');
$phpgw_setup->oProc->Query('insert into dcl_contact_cnv (entity_type_id, entity_id, entity_id2, lastname, firstname, contactemail) select 7, id, 0, lastname, firstname, email from personnel');
// work orders
dcl_upgrade0_9_4_4_write_message('<br>Getting work order contacts...');
$phpgw_setup->oProc->Query("insert into dcl_contact_cnv (entity_type_id, entity_id, entity_id2, contactname, contactphone) select 2, jcn, seq, contact, contactphone from workorders where (contact is not null and contact != '') or (contactphone is not null and contactphone != '')");
// tickets
dcl_upgrade0_9_4_4_write_message('<br>Getting ticket contacts...');
$phpgw_setup->oProc->Query('insert into dcl_contact_cnv (entity_type_id, entity_id, entity_id2, contactname, contactphone, contactemail) select 3, ticketid, 0, contact, contactphone, contactemail from tickets');
// Scrub the data - very basic, nothing fancy
$oDB = new dclDB();
$i = 0;
dcl_upgrade0_9_4_4_write_message('<br/>Scrubbing contact data (# = 1000): ');
$oDB->Query('SELECT cnv_id, contactphone, contactname FROM dcl_contact_cnv WHERE contactname IS NOT NULL ORDER BY cnv_id');
$iStartTime = time();
$phpgw_setup->oProc->m_odb->BeginTransaction();
while ($oDB->next_record()) {
// Phone number
$iID = $oDB->f(0);
$sPhoneNum = trim($oDB->f(1));
$sName = trim($oDB->f(2));
$sPhoneNumOnly = ereg_replace('[^0-9]', '', $sPhoneNum);
$sLastName = '';
$sFirstName = '';
// last, first, "first last", or just last
$iPos = strpos($sName, ',');
if ($iPos !== false) {
$aName = split(',', $sName);
$sLastName = trim($aName[0]);
if (count($aName) > 1) {
$sFirstName = trim($aName[1]);
}
if (count($aName) > 2) {
for ($j = 2; $j < count($aName); $j++) {
$sFirstName .= ' ' . trim($aName[$j]);
}
}
} else {
$iPos = strpos($sName, ' ');
if ($iPos !== false) {
$aName = split(' ', $sName);
$sFirstName = trim($aName[0]);
if (count($aName) > 1) {
$sLastName = trim($aName[1]);
}
if (count($aName) > 2) {
for ($j = 2; $j < count($aName); $j++) {
$sLastName .= ' ' . trim($aName[$j]);
}
}
} else {
$sLastName = $sName;
$sFirstName = '_';
}
}
if ($sPhoneNumOnly != '' || $sLastName != '' || $sFirstName != '') {
$sPhoneNumOnly = $phpgw_setup->oProc->m_odb->DBAddSlashes($sPhoneNumOnly);
$sLastName = $phpgw_setup->oProc->m_odb->DBAddSlashes($sLastName);
$sFirstName = $phpgw_setup->oProc->m_odb->DBAddSlashes($sFirstName);
$sName = $phpgw_setup->oProc->m_odb->DBAddSlashes($sName);
$phpgw_setup->oProc->Query("UPDATE dcl_contact_cnv SET contactphone_num = '{$sPhoneNumOnly}', lastname = '{$sLastName}', firstname = '{$sFirstName}' WHERE cnv_id = {$iID}");
if (++$i % 1000 == 0) {
dcl_upgrade0_9_4_4_write_message('# ');
$phpgw_setup->oProc->m_odb->EndTransaction();
$phpgw_setup->oProc->m_odb->BeginTransaction();
set_time_limit(30);
// add some more time
}
}
}
$phpgw_setup->oProc->m_odb->EndTransaction();
$oDB->FreeResult();
// More time!
set_time_limit(30);
// Create contact records for the personnel records
$i = 0;
$iStartTime = time();
dcl_upgrade0_9_4_4_write_message('<br>Creating contact records for personnel (# = 500): ');
$oDB->Query('SELECT lastname, firstname, cnv_id FROM dcl_contact_cnv WHERE entity_type_id = 7 ORDER BY lastname, firstname, contactemail');
$phpgw_setup->oProc->m_odb->BeginTransaction();
while ($oDB->next_record()) {
$sLastName = $phpgw_setup->oProc->m_odb->DBAddSlashes($oDB->f(0));
$sFirstName = $phpgw_setup->oProc->m_odb->DBAddSlashes($oDB->f(1));
$iCnvID = $oDB->f(2);
$phpgw_setup->oProc->Query("INSERT INTO dcl_contact (first_name, last_name, created_on, created_by) VALUES ('{$sFirstName}', '{$sLastName}', " . $phpgw_setup->oProc->m_odb->GetDateSQL() . ", 1)");
$iID = $phpgw_setup->oProc->m_odb->GetLastInsertID('dcl_contact');
$sSQL = "UPDATE dcl_contact_cnv SET contact_id = {$iID} WHERE cnv_id = {$iCnvID}";
$phpgw_setup->oProc->Query($sSQL);
if (++$i % 500 == 0) {
dcl_upgrade0_9_4_4_write_message('# ');
$phpgw_setup->oProc->m_odb->EndTransaction();
$phpgw_setup->oProc->m_odb->BeginTransaction();
set_time_limit(30);
// add some more time
}
}
$phpgw_setup->oProc->m_odb->EndTransaction();
$oDB->FreeResult();
// More time!
set_time_limit(30);
// Create contact records for the distinct name/phone combinations, this will undoubtedly
// create duplicates, but there will be a way to "merge" contacts together
$i = 0;
$iStartTime = time();
dcl_upgrade0_9_4_4_write_message('<br>Creating contact records for wo/tickets (# = 500): ');
$oDB->Query('SELECT lastname, firstname, contactphone_num FROM dcl_contact_cnv GROUP BY lastname, firstname, contactphone_num ORDER BY UPPER(lastname), UPPER(firstname), contactphone_num');
$phpgw_setup->oProc->m_odb->BeginTransaction();
$sLastLastName = '';
$sLastFirstName = '';
$sLastPhone = '';
$iID = -1;
while ($oDB->next_record()) {
// for case-sensitive databases, we'll skip multiple instances of same uppercase name/phone
if (strtoupper($oDB->f(0)) != $sLastLastName || strtoupper($oDB->f(1)) != $sLastFirstName || $oDB->f(2) != $sLastPhone) {
$sLastLastName = strtoupper($oDB->f(0));
$sLastFirstName = strtoupper($oDB->f(1));
$sLastPhone = $oDB->f(2);
$sLastName = $phpgw_setup->oProc->m_odb->DBAddSlashes($oDB->f(0));
$sFirstName = $phpgw_setup->oProc->m_odb->DBAddSlashes($oDB->f(1));
$phpgw_setup->oProc->Query("INSERT INTO dcl_contact (first_name, last_name, created_by) VALUES ('{$sFirstName}', '{$sLastName}', 1)");
$iID = $phpgw_setup->oProc->m_odb->GetLastInsertID('dcl_contact');
}
if ($iID == -1) {
continue;
}
$sSQL = "UPDATE dcl_contact_cnv SET contact_id = {$iID} WHERE ";
$sSQL .= "lastname " . ($oDB->IsFieldNull(0) ? 'IS NULL' : "= '" . $phpgw_setup->oProc->m_odb->DBAddSlashes($oDB->f(0)) . "'");
$sSQL .= " AND firstname " . ($oDB->IsFieldNull(1) ? 'IS NULL' : "= '" . $phpgw_setup->oProc->m_odb->DBAddSlashes($oDB->f(1)) . "'");
$sSQL .= " AND contactphone_num " . ($oDB->IsFieldNull(2) ? 'IS NULL' : "= '" . $phpgw_setup->oProc->m_odb->DBAddSlashes($oDB->f(2)) . "'");
$phpgw_setup->oProc->Query($sSQL);
if (++$i % 500 == 0) {
dcl_upgrade0_9_4_4_write_message('# ');
$phpgw_setup->oProc->m_odb->EndTransaction();
$phpgw_setup->oProc->m_odb->BeginTransaction();
set_time_limit(30);
// add some more time
}
}
$phpgw_setup->oProc->m_odb->EndTransaction();
$oDB->FreeResult();
// Add records for phone and email
set_time_limit(30);
$phpgw_setup->oProc->Query("INSERT INTO dcl_contact_phone (contact_id, phone_type_id, phone_number, preferred, created_on, created_by) SELECT DISTINCT contact_id, 1, contactphone, 'N', " . $phpgw_setup->oProc->m_odb->GetDateSQL() . ", 1 FROM dcl_contact_cnv WHERE contactphone IS NOT NULL AND contactphone != '' AND contact_id IS NOT NULL");
$phpgw_setup->oProc->Query("INSERT INTO dcl_email_type (email_type_name) VALUES ('Default')");
$phpgw_setup->oProc->Query("INSERT INTO dcl_contact_email (contact_id, email_type_id, email_addr, preferred, created_on, created_by) SELECT DISTINCT contact_id, 1, contactemail, 'N', " . $phpgw_setup->oProc->m_odb->GetDateSQL() . ", 1 FROM dcl_contact_cnv WHERE contactemail IS NOT NULL AND contactemail != '' AND contact_id IS NOT NULL");
// Update preferred status for email
$i = 0;
$iStartTime = time();
dcl_upgrade0_9_4_4_write_message('<br>Setting preferred email address (# = 1000): ');
$oDB->Query('SELECT MIN(contact_email_id) FROM dcl_contact_email GROUP BY contact_id');
$phpgw_setup->oProc->m_odb->BeginTransaction();
while ($oDB->next_record()) {
$iID = $oDB->f(0);
$phpgw_setup->oProc->Query("UPDATE dcl_contact_email SET preferred = 'Y' WHERE contact_email_id = {$iID}");
if (++$i % 1000 == 0) {
dcl_upgrade0_9_4_4_write_message('# ');
$phpgw_setup->oProc->m_odb->EndTransaction();
$phpgw_setup->oProc->m_odb->BeginTransaction();
set_time_limit(30);
// add some more time
}
}
$phpgw_setup->oProc->m_odb->EndTransaction();
$oDB->FreeResult();
// Update preferred status for phone numbers
$i = 0;
$iStartTime = time();
dcl_upgrade0_9_4_4_write_message('<br>Setting preferred phone numbers (# = 1000): ');
$oDB->Query('SELECT MIN(contact_phone_id) FROM dcl_contact_phone GROUP BY contact_id');
$phpgw_setup->oProc->m_odb->BeginTransaction();
while ($oDB->next_record()) {
$iID = $oDB->f(0);
$phpgw_setup->oProc->Query("UPDATE dcl_contact_phone SET preferred = 'Y' WHERE contact_phone_id = {$iID}");
if (++$i % 1000 == 0) {
dcl_upgrade0_9_4_4_write_message('# ');
$phpgw_setup->oProc->m_odb->EndTransaction();
$phpgw_setup->oProc->m_odb->BeginTransaction();
set_time_limit(30);
// add some more time
}
}
$phpgw_setup->oProc->m_odb->EndTransaction();
$oDB->FreeResult();
printf("<b>Completed contact conversion of %d records in %d seconds (%d / sec)</b><br/>", $i, time() - $iStartTime, time() - $iStartTime > 0 ? $i / (time() - $iStartTime) : 0);
}
$phpgw_setup->oProc->AddColumn('workorders', 'contact_id', array('type' => 'int', 'precision' => 4));
$phpgw_setup->oProc->AddColumn('tickets', 'contact_id', array('type' => 'int', 'precision' => 4));
$phpgw_setup->oProc->AddColumn('personnel', 'contact_id', array('type' => 'int', 'precision' => 4));
if ($sType == 'mysql') {
dcl_upgrade0_9_4_4_write_message('<br>Setting personnel contact field ');
$phpgw_setup->oProc->Query('UPDATE personnel P, dcl_contact_cnv C SET P.contact_id = C.contact_id WHERE C.entity_type_id = 7 AND C.entity_id = P.id');
dcl_upgrade0_9_4_4_write_message('<br>Setting work orders contact field ');
$phpgw_setup->oProc->Query('UPDATE workorders W, dcl_contact_cnv C SET W.contact_id = C.contact_id WHERE C.entity_type_id = 2 AND C.entity_id = W.jcn AND C.entity_id2 = W.seq');
dcl_upgrade0_9_4_4_write_message('<br>Setting tickets contact field ');
$phpgw_setup->oProc->Query('UPDATE tickets T, dcl_contact_cnv C SET T.contact_id = C.contact_id WHERE C.entity_type_id = 3 AND C.entity_id = T.ticketid');
} else {
dcl_upgrade0_9_4_4_write_message('<br>Setting personnel contact field ');
$phpgw_setup->oProc->Query('UPDATE personnel SET contact_id = dcl_contact_cnv.contact_id FROM dcl_contact_cnv WHERE dcl_contact_cnv.entity_type_id = 7 AND dcl_contact_cnv.entity_id = personnel.id');
dcl_upgrade0_9_4_4_write_message('<br>Setting work orders contact field ');
$phpgw_setup->oProc->Query('UPDATE workorders SET contact_id = dcl_contact_cnv.contact_id FROM dcl_contact_cnv WHERE dcl_contact_cnv.entity_type_id = 2 AND dcl_contact_cnv.entity_id = workorders.jcn AND dcl_contact_cnv.entity_id2 = workorders.seq');
dcl_upgrade0_9_4_4_write_message('<br>Setting tickets contact field ');
$phpgw_setup->oProc->Query('UPDATE tickets SET contact_id = dcl_contact_cnv.contact_id FROM dcl_contact_cnv WHERE dcl_contact_cnv.entity_type_id = 3 AND dcl_contact_cnv.entity_id = tickets.ticketid');
}
// Set up a default "converted" type for contacts
dcl_upgrade0_9_4_4_write_message('<br>Setting contact types ');
if (!$phpgw_setup->oProc->m_bDeltaOnly) {
$phpgw_setup->oProc->Query("INSERT INTO dcl_contact_type_xref SELECT contact_id, {$iContactTypeID} FROM dcl_contact");
}
// Now we blindly start adding all distinct combinations of orgs/contacts
// The end result could be messy in some cases, but it's better than manually setting these up later
dcl_upgrade0_9_4_4_write_message('<br>Setting default org/contact relationships');
$phpgw_setup->oProc->Query('insert into dcl_org_contact select distinct account, contact_id, ' . $phpgw_setup->oProc->m_odb->GetDateSQL() . ', 1 from tickets where account is not null and contact_id is not null union select distinct a.account_id, w.contact_id, ' . $phpgw_setup->oProc->m_odb->GetDateSQL() . ', 1 from workorders w, dcl_wo_account a where w.jcn = a.wo_id and w.seq = a.seq and a.account_id is not null and w.contact_id is not null');
if (!$phpgw_setup->oProc->m_bDeltaOnly) {
$phpgw_setup->oProc->DropTable('dcl_contact_cnv');
}
// Bye bye free form fields
$phpgw_setup->oProc->DropColumn('workorders', array('fd' => array('jcn' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'seq' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'product' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'createby' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'createdon' => array('type' => 'timestamp', 'nullable' => false), 'closedby' => array('type' => 'int', 'precision' => 4), 'closedon' => array('type' => 'date'), 'status' => array('type' => 'int', 'precision' => 4), 'statuson' => array('type' => 'timestamp', 'nullable' => false), 'lastactionon' => array('type' => 'timestamp'), 'deadlineon' => array('type' => 'date'), 'eststarton' => array('type' => 'date'), 'estendon' => array('type' => 'date'), 'starton' => array('type' => 'date'), 'esthours' => array('type' => 'float', 'precision' => 8), 'totalhours' => array('type' => 'float', 'precision' => 8), 'priority' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'severity' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'summary' => array('type' => 'varchar', 'precision' => 100, 'nullable' => false), 'notes' => array('type' => 'text'), 'description' => array('type' => 'text', 'nullable' => false), 'responsible' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'revision' => array('type' => 'varchar', 'precision' => 20), 'etchours' => array('type' => 'float', 'precision' => 8), 'module_id' => array('type' => 'int', 'precision' => 4), 'wo_type_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'entity_source_id' => array('type' => 'int', 'precision' => 4, 'nullable' => true), 'is_public' => array('type' => 'char', 'precision' => 1, 'nullable' => false, 'default' => 'N'), 'contact_id' => array('type' => 'int', 'precision' => 4, 'nullable' => true)), 'pk' => array('jcn', 'seq'), 'fk' => array(), 'ix' => array(), 'uc' => array()), array('contact', 'contactphone'));
$phpgw_setup->oProc->DropColumn('tickets', array('fd' => array('ticketid' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'product' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'account' => array('type' => 'int', 'precision' => 4), 'createdby' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'createdon' => array('type' => 'timestamp'), 'responsible' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'closedby' => array('type' => 'int', 'precision' => 4), 'closedon' => array('type' => 'timestamp'), 'status' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'statuson' => array('type' => 'timestamp', 'nullable' => false), 'lastactionon' => array('type' => 'timestamp'), 'priority' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'type' => array('type' => 'int', 'precision' => 4, 'nullable' => false), 'issue' => array('type' => 'text', 'nullable' => false), 'version' => array('type' => 'varchar', 'precision' => 20), 'summary' => array('type' => 'varchar', 'precision' => 100, 'nullable' => false), 'seconds' => array('type' => 'int', 'precision' => 4, 'default' => 0, 'nullable' => false), 'module_id' => array('type' => 'int', 'precision' => 4), 'entity_source_id' => array('type' => 'int', 'precision' => 4), 'is_public' => array('type' => 'char', 'precision' => 1, 'nullable' => false, 'default' => 'N'), 'contact_id' => array('type' => 'int', 'precision' => 4, 'nullable' => true)), 'pk' => array('ticketid'), 'fk' => array(), 'ix' => array(), 'uc' => array()), array('contact', 'contactphone'));
$phpgw_setup->oProc->DropColumn('personnel', array('fd' => array('id' => array('type' => 'auto', 'precision' => 4, 'nullable' => false), 'short' => array('type' => 'varchar', 'precision' => 25, 'nullable' => false), 'reportto' => array('type' => 'int', 'precision' => 4), 'department' => array('type' => 'int', 'precision' => 4), 'pwd' => array('type' => 'varchar', 'precision' => 50, 'nullable' => false), 'active' => array('type' => 'char', 'precision' => 1, 'default' => 'Y', 'nullable' => false), 'contact_id' => array('type' => 'int', 'precision' => 4, 'nullable' => false)), 'pk' => array('id'), 'fk' => array(), 'ix' => array(), 'uc' => array()), array('lastname', 'firstname', 'security', 'email'));
$phpgw_setup->oProc->Query("DELETE FROM dcl_config WHERE dcl_config_name IN ('DCL_ADD_USER', 'DCL_DEL_WO', 'DCL_MOD_WO', 'DCL_ASSIGN_WO', 'DCL_ADD_WO', 'DCL_HAVE_WO', 'DCL_CHG_PWD', 'DCL_COLOR_DARK', 'DCL_COLOR_LIGHT', 'DCL_WIKI_VIEW', 'DCL_WIKI_VIEW')");
$phpgw_setup->oProc->Query("insert into dcl_config (dcl_config_name, dcl_config_field, dcl_config_int) values ('DCL_GATEWAY_WO_ACCOUNT', 'dcl_config_int', 0)");
$phpgw_setup->oProc->Query("insert into dcl_config (dcl_config_name, dcl_config_field, dcl_config_int) values ('DCL_GATEWAY_WO_REPLY_LOGGED_BY', 'dcl_config_int', 0)");
$phpgw_setup->oProc->Query("UPDATE dcl_config SET dcl_config_varchar='0.9.5RC1' WHERE dcl_config_name='DCL_VERSION'");
$setup_info['dcl']['currentver'] = '0.9.5RC1';
return $setup_info['dcl']['currentver'];
}
function ShowReport($query, $iGroupColumn)
{
$bExport = isset($_REQUEST['export']) && $_REQUEST['export'] == '1';
$objS = CreateObject('dcl.dbStatuses');
$objPr = CreateObject('dcl.dbPriorities');
$objSe = CreateObject('dcl.dbSeverities');
$objW = CreateObject('dcl.dbWorkorders');
$oPM = CreateObject('dcl.dbProjectmap');
$objDB = new dclDB();
$aGroupOptions = array('1' => 'Project', '2' => 'Action', '3' => 'Date', '4' => 'Product', '5' => 'by');
$groupBy = $_REQUEST['groupby'];
if (!array_key_exists($groupBy, $aGroupOptions)) {
$groupBy = '0';
}
$oMeta =& CreateObject('dcl.DCL_MetadataDisplay');
$responsible = 0;
$department = 0;
if ($_REQUEST['bytype'] == '1') {
if (($responsible = DCL_Sanitize::ToInt($_REQUEST['responsible'])) === null) {
trigger_error('Data sanitize failed.');
return;
}
$sReportFor = $oMeta->GetPersonnel($responsible);
} else {
if (($department = DCL_Sanitize::ToInt($_REQUEST['department'])) === null) {
trigger_error('Data sanitize failed.');
return;
}
$sReportFor = $oMeta->GetDepartment($department);
}
if (($begindate = DCL_Sanitize::ToDate($_REQUEST['begindate'])) === null || ($enddate = DCL_Sanitize::ToDate($_REQUEST['enddate'])) === null) {
trigger_error('Data sanitize failed.');
return;
}
if ($objDB->Query($query) != -1) {
if ($objDB->next_record()) {
$lastJCN = 0;
$lastSeq = 0;
$lastGroup = $thisGroup = '<< undefined >>';
$arrayIndex = -1;
$count = 0;
$subEstHours = 0.0;
$subAppliedHours = 0.0;
$subEtcHours = 0.0;
$subTimeHours = 0.0;
$totalEstHours = 0.0;
$totalAppliedHours = 0.0;
$totalEtcHours = 0.0;
$totalTimeHours = 0.0;
$oDate = new DCLDate();
$aByDate = array();
do {
$thisJCN = $objDB->f('jcn');
$thisSeq = $objDB->f('seq');
if ($groupBy == '3') {
$oDate->SetFromDB($objDB->f('actionon'));
$thisGroup = $oDate->ToDisplay();
} else {
if ($groupBy == '5') {
$thisGroup = $objDB->f('short');
} else {
if ($groupBy != '0') {
if ($objDB->IsFieldNull('name')) {
$thisGroup = ' ';
} else {
$thisGroup = $objDB->f('name');
}
}
}
}
// Skip multiple time cards
if ($thisJCN != $lastJCN || $thisSeq != $lastSeq || $thisGroup != $lastGroup) {
if ($groupBy != '0' && $thisGroup != $lastGroup && $lastGroup != '<< undefined >>') {
// Subtotals
$arrayIndex++;
if ($bExport) {
$reportArray[$arrayIndex][0] = 'Subtotal for ' . $lastGroup;
} else {
$reportArray[$arrayIndex][0] = '<b>Subtotal for ' . $lastGroup . '</b>';
}
if ($groupBy != '1') {
$reportArray[$arrayIndex][] = '';
}
if ($_REQUEST['bytype'] == '2') {
if ($groupBy == '5') {
$reportArray[$arrayIndex][] = $lastGroup;
} else {
$reportArray[$arrayIndex][] = '';
}
}
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = $subEstHours;
$reportArray[$arrayIndex][] = $subEtcHours;
$reportArray[$arrayIndex][] = $subAppliedHours;
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = $subTimeHours;
$ouHours = -($subEstHours - $subAppliedHours);
$diffHours = $ouHours;
if ($diffHours < 0) {
$diffHours = -$diffHours;
}
$ouPct = 0.0;
$sign = '';
if ($subEstHours > 0) {
$ouPct = $diffHours / $subEstHours * 100;
if ($subEstHours > $subAppliedHours && $subEstHours > 0) {
$sign = '-';
} else {
if ($subAppliedHours > $subEstHours && $subAppliedHours > 0) {
$sign = '+';
}
}
}
if ($bExport) {
$reportArray[$arrayIndex][] = sprintf('%s%0.2f (%s%0.2f%%)', $sign, abs($ouHours), $sign, abs($ouPct));
} else {
$reportArray[$arrayIndex][] = sprintf('%s%0.2f (%s%0.2f%%)', $sign, abs($ouHours), $sign, abs($ouPct));
}
if ($groupBy != '0' && ($_REQUEST['bytype'] != '2' || $groupBy != '5')) {
$reportArray[$arrayIndex][] = $lastGroup;
}
$subEstHours = 0.0;
$subAppliedHours = 0.0;
$subEtcHours = 0.0;
$subTimeHours = 0.0;
}
$arrayIndex++;
$objW->Load($thisJCN, $thisSeq);
$objS->Load($objW->status);
$objPr->Load($objW->priority);
$objSe->Load($objW->severity);
if ($bExport) {
$reportArray[$arrayIndex][0] = '[' . $thisJCN . '-' . $thisSeq . '] ' . $objW->summary;
} else {
$reportArray[$arrayIndex][0] = '[<a href="main.php?menuAction=boWorkorders.viewjcn&jcn=' . $thisJCN . '&seq=' . $thisSeq . '">' . $thisJCN . '-' . $thisSeq . '</a>] ' . htmlentities($objW->summary);
}
if ($groupBy != '1') {
if ($oPM->LoadByWO($thisJCN, $thisSeq) != -1) {
if ($bExport) {
$reportArray[$arrayIndex][] = '[' . $oPM->projectid . '] ' . $oMeta->GetProject($oPM->projectid);
} else {
$reportArray[$arrayIndex][] = '[<a href="main.php?menuAction=boProjects.viewproject&project=' . $oPM->projectid . '">' . $oPM->projectid . '</a>] ' . htmlentities($oMeta->GetProject($oPM->projectid));
}
} else {
$reportArray[$arrayIndex][] = '';
}
}
if ($_REQUEST['bytype'] == '2') {
$reportArray[$arrayIndex][] = $objDB->f('short');
}
$reportArray[$arrayIndex][] = $objS->name;
$reportArray[$arrayIndex][] = $objPr->name;
$reportArray[$arrayIndex][] = $objSe->name;
$reportArray[$arrayIndex][] = (double) $objW->esthours;
$reportArray[$arrayIndex][] = (double) $objW->etchours;
$reportArray[$arrayIndex][] = (double) $objW->totalhours;
$reportArray[$arrayIndex][] = $objW->eststarton;
$reportArray[$arrayIndex][] = $objW->estendon;
$reportArray[$arrayIndex][] = $objW->starton;
$reportArray[$arrayIndex][] = $objW->closedon;
$reportArray[$arrayIndex][] = (double) $objDB->f('hours');
$ouHours = -($objW->esthours - $objW->totalhours);
$diffHours = $ouHours;
if ($diffHours < 0) {
$diffHours = -$diffHours;
}
$ouPct = 0.0;
$sign = '';
if ($objW->esthours > 0) {
$ouPct = $diffHours / $objW->esthours * 100;
if ($objW->esthours > $objW->totalhours && $objW->esthours > 0) {
$sign = '-';
} else {
if ($objW->totalhours > $objW->esthours && $objW->totalhours > 0) {
$sign = '+';
}
}
}
$reportArray[$arrayIndex][] = sprintf('%s%0.2f (%s%0.2f%%)', $sign, abs($ouHours), $sign, abs($ouPct));
if ($groupBy != '0' && ($_REQUEST['bytype'] != '2' || $groupBy != '5')) {
$reportArray[$arrayIndex][] = $thisGroup;
}
$sKey = sprintf('%d-%d', $thisJCN, $thisSeq);
if ($groupBy != '3' || !isset($aByDate[$sKey])) {
$subEstHours += (double) $objW->esthours;
$subAppliedHours += (double) $objW->totalhours;
$subEtcHours += (double) $objW->etchours;
$totalEstHours += (double) $objW->esthours;
$totalAppliedHours += (double) $objW->totalhours;
$totalEtcHours += (double) $objW->etchours;
$aByDate[$sKey] = true;
}
$lastJCN = $thisJCN;
$lastSeq = $thisSeq;
$lastGroup = $thisGroup;
} else {
$iOrdinal = 11;
if ($groupBy != '1') {
$iOrdinal++;
}
if ($_REQUEST['bytype'] == '2') {
$iOrdinal++;
}
$reportArray[$arrayIndex][$iOrdinal] += (double) $objDB->f('hours');
}
$subTimeHours += $objDB->f('hours');
$totalTimeHours += $objDB->f('hours');
$count++;
} while ($objDB->next_record());
// Subtotals
$arrayIndex++;
if ($bExport) {
$reportArray[$arrayIndex][0] = 'Subtotal for ' . $lastGroup;
} else {
$reportArray[$arrayIndex][0] = '<b>Subtotal for ' . $lastGroup . '</b>';
}
if ($groupBy != '1') {
$reportArray[$arrayIndex][] = '';
}
if ($_REQUEST['bytype'] == '2') {
if ($groupBy == '5') {
$reportArray[$arrayIndex][] = $lastGroup;
} else {
$reportArray[$arrayIndex][] = '';
}
}
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = $subEstHours;
$reportArray[$arrayIndex][] = $subEtcHours;
$reportArray[$arrayIndex][] = $subAppliedHours;
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = $subTimeHours;
$ouHours = -($subEstHours - $subAppliedHours);
$diffHours = $ouHours;
if ($diffHours < 0) {
$diffHours = -$diffHours;
}
$ouPct = 0.0;
$sign = '';
if ($subEstHours > 0) {
$ouPct = $diffHours / $subEstHours * 100;
if ($subEstHours > $subAppliedHours && $subEstHours > 0) {
$sign = '-';
} else {
if ($subAppliedHours > $subEstHours && $subAppliedHours > 0) {
$sign = '+';
}
}
}
if ($bExport) {
$reportArray[$arrayIndex][] = sprintf('%s%0.2f (%s%0.2f%%)', $sign, abs($ouHours), $sign, abs($ouPct));
} else {
$reportArray[$arrayIndex][] = sprintf('%s%0.2f (%s%0.2f%%)', $sign, abs($ouHours), $sign, abs($ouPct));
}
if ($groupBy != '0' && ($_REQUEST['bytype'] != '2' || $groupBy != '5')) {
$reportArray[$arrayIndex][] = $lastGroup;
}
$subEstHours = 0.0;
$subAppliedHours = 0.0;
$subEtcHours = 0.0;
$subTimeHours = 0.0;
if ($bExport) {
$arrayIndex++;
$reportArray[$arrayIndex][0] = 'Totals';
if ($groupBy != '1') {
$reportArray[$arrayIndex][] = '';
}
if ($_REQUEST['bytype'] == '2') {
$reportArray[$arrayIndex][] = '';
}
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = $totalEstHours;
$reportArray[$arrayIndex][] = $totalEtcHours;
$reportArray[$arrayIndex][] = $totalAppliedHours;
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = '';
$reportArray[$arrayIndex][] = $totalTimeHours;
$ouHours = -($totalEstHours - $totalAppliedHours);
$diffHours = $ouHours;
if ($diffHours < 0) {
$diffHours = -$diffHours;
}
$ouPct = 0.0;
$sign = '';
if ($totalEstHours > 0) {
$ouPct = $diffHours / $totalEstHours * 100;
if ($totalEstHours > $totalAppliedHours && $totalEstHours > 0) {
$sign = '-';
} else {
if ($totalAppliedHours > $totalEstHours && $totalAppliedHours > 0) {
$sign = '+';
}
}
}
if ($bExport) {
$reportArray[$arrayIndex][] = sprintf('%s%0.2f (%s%0.2f%%)', $sign, abs($ouHours), $sign, abs($ouPct));
} else {
$reportArray[$arrayIndex][] = sprintf('%s%0.2f (%s%0.2f%%)', $sign, abs($ouHours), $sign, abs($ouPct));
}
if ($groupBy != '0') {
$reportArray[$arrayIndex][] = '';
}
$nameArray = array();
$nameArray[] = STR_WOST_SUMMARY;
if ($groupBy != '1') {
$nameArray[] = STR_WO_PROJECT;
}
if ($_REQUEST['bytype'] == '2') {
$nameArray[] = STR_CMMN_BY;
}
$nameArray[] = STR_WO_STATUS;
$nameArray[] = STR_WO_PRIORITY;
$nameArray[] = STR_WO_SEVERITY;
$nameArray[] = STR_WOST_BUDGET;
$nameArray[] = STR_WOST_ETC;
$nameArray[] = STR_WOST_TODATE;
$nameArray[] = STR_WOST_ESTSTART;
$nameArray[] = STR_WOST_ESTEND;
$nameArray[] = STR_WOST_START;
$nameArray[] = STR_WOST_END;
$nameArray[] = STR_WOST_TIME;
$nameArray[] = '+ / -';
if ($groupBy != '0' && ($_REQUEST['bytype'] != '2' || $groupBy != '5')) {
$nameArray[] = '';
}
ExportArray($nameArray, $reportArray);
} else {
$oTable = CreateObject('dcl.htmlTable');
$oTable->addFooter('Totals');
if ($groupBy != '1') {
$oTable->addFooter('');
}
if ($_REQUEST['bytype'] == '2') {
$oTable->addFooter('');
}
$oTable->addFooter('');
$oTable->addFooter('');
$oTable->addFooter('');
$oTable->addFooter($totalEstHours);
$oTable->addFooter($totalEtcHours);
$oTable->addFooter($totalAppliedHours);
$oTable->addFooter('');
$oTable->addFooter('');
$oTable->addFooter('');
$oTable->addFooter('');
$oTable->addFooter($totalTimeHours);
$ouHours = -($totalEstHours - $totalAppliedHours);
$diffHours = $ouHours;
if ($diffHours < 0) {
$diffHours = -$diffHours;
}
$ouPct = 0.0;
$sign = '';
if ($totalEstHours > 0) {
$ouPct = $diffHours / $totalEstHours * 100;
if ($totalEstHours > $totalAppliedHours && $totalEstHours > 0) {
$sign = '-';
} else {
if ($totalAppliedHours > $totalEstHours && $totalAppliedHours > 0) {
$sign = '+';
}
}
}
$oTable->addFooter(sprintf('%s%0.2f (%s%0.2f%%)', $sign, abs($ouHours), $sign, abs($ouPct)));
if ($groupBy != '0' && ($_REQUEST['bytype'] != '2' || $groupBy != '5')) {
$oTable->addFooter('');
}
$oTable->addColumn(STR_WOST_SUMMARY, 'html');
if ($groupBy != '1') {
$oTable->addColumn(STR_WO_PROJECT, 'html');
}
if ($_REQUEST['bytype'] == '2') {
$oTable->addColumn(STR_CMMN_BY, 'string');
}
$oTable->addColumn(STR_WO_STATUS, 'string');
$oTable->addColumn(STR_WO_PRIORITY, 'string');
$oTable->addColumn(STR_WO_SEVERITY, 'string');
$oTable->addColumn(STR_WOST_BUDGET, 'numeric');
$oTable->addColumn(STR_WOST_ETC, 'numeric');
$oTable->addColumn(STR_WOST_TODATE, 'numeric');
$oTable->addColumn(STR_WOST_ESTSTART, 'string');
$oTable->addColumn(STR_WOST_ESTEND, 'string');
$oTable->addColumn(STR_WOST_START, 'string');
$oTable->addColumn(STR_WOST_END, 'string');
$oTable->addColumn(STR_WOST_TIME, 'numeric');
$oTable->addColumn('+ / -', 'html');
if (array_key_exists($groupBy, $aGroupOptions) && ($_REQUEST['bytype'] != '2' || $groupBy != '5')) {
$oTable->addColumn($aGroupOptions[$groupBy], 'string');
}
$oTable->setData($reportArray);
$oTable->setCaption(sprintf(STR_WOST_ACTIVITYTITLE, $sReportFor, $_REQUEST['begindate'], $_REQUEST['enddate']));
$oTable->addToolbar(menuLink('', sprintf('menuAction=reportPersonnelActivity.execute&export=1&responsible=%s&begindate=%s&enddate=%s&bytype=%d&groupby=%d&department=%d', $responsible, $begindate, $enddate, $_REQUEST['bytype'], $groupBy, $department)), STR_VW_EXPORTRESULTS);
$oTable->addGroup($iGroupColumn);
$oTable->setShowRownum(true);
$oTable->render();
}
} else {
if ($bExport) {
commonHeader();
}
trigger_error(STR_WOST_NOACTIVITY, E_USER_NOTICE);
$this->getparameters(false);
}
} else {
if ($bExport) {
commonHeader();
}
trigger_error(STR_WOST_QUERYERR, E_USER_ERROR);
}
}
