userloginid_to_partyid.sql

国外的一套开源CRM

-- This script changes the data base so the user party ID is used instead of the user login ID in various places.

----------
-- DEAL Table
----------

-- created_by
select concat(
	'update deal set created_by = \'',
	u.party_id,
	'\' where deal_id = \'',
	d.deal_id,
	'\';')
into outfile 'c:/temp/deal_created_by.sql'
from deal d, user_login u
where d.created_by = u.user_login_id
	and d.created_by is not null
	and d.created_by <> ''
order by deal_id;

source c:/temp/deal_created_by.sql;

-- modified_by
select concat(
	'update deal set modified_by = \'',
	u.party_id,
	'\' where deal_id = \'',
	d.deal_id,
	'\';')
into outfile 'c:/temp/deal_modified_by.sql'
from deal d, user_login u
where d.modified_by = u.user_login_id
	and d.modified_by is not null
	and d.modified_by <> ''
order by deal_id;

source c:/temp/deal_modified_by.sql;


----------
-- ENTITY_ACCESS Table
----------

-- created_by
select concat(
	'update entity_access set created_by = \'',
	u.party_id,
	'\' where entity_access_id = \'',
	d.entity_access_id,
	'\';')
into outfile 'c:/temp/entity_access_created_by.sql'
from entity_access d, user_login u
where d.created_by = u.user_login_id
	and d.created_by is not null
	and d.created_by <> ''
order by entity_access_id;

source c:/temp/entity_access_created_by.sql;

-- modified_by
select concat(
	'update entity_access set modified_by = \'',
	u.party_id,
	'\' where entity_access_id = \'',
	d.entity_access_id,
	'\';')
into outfile 'c:/temp/entity_access_modified_by.sql'
from entity_access d, user_login u
where d.modified_by = u.user_login_id
	and d.modified_by is not null
	and d.modified_by <> ''
order by entity_access_id;

source c:/temp/entity_access_modified_by.sql;


----------
-- IT_ISSUE Table
----------

-- created_by
select concat(
	'update it_issue set created_by = \'',
	u.party_id,
	'\' where issue_id = \'',
	d.issue_id,
	'\';')
into outfile 'c:/temp/it_issue_created_by.sql'
from it_issue d, user_login u
where d.created_by = u.user_login_id
	and d.created_by is not null
	and d.created_by <> ''
order by issue_id;

source c:/temp/it_issue_created_by.sql;

-- modified_by
select concat(
	'update it_issue set modified_by = \'',
	u.party_id,
	'\' where issue_id = \'',
	d.issue_id,
	'\';')
into outfile 'c:/temp/it_issue_modified_by.sql'
from it_issue d, user_login u
where d.modified_by = u.user_login_id
	and d.modified_by is not null
	and d.modified_by <> ''
order by issue_id;

source c:/temp/it_issue_modified_by.sql;

-- assigned_to
select concat(
	'update it_issue set assigned_to = \'',
	u.party_id,
	'\' where issue_id = \'',
	d.issue_id,
	'\';')
into outfile 'c:/temp/it_issue_assigned_to.sql'
from it_issue d, user_login u
where d.assigned_to = u.user_login_id
	and d.assigned_to is not null
	and d.assigned_to <> ''
order by issue_id;

source c:/temp/it_issue_assigned_to.sql;


----------
-- IT_ISSUE_HISTORY Table
----------

-- created_by
select concat(
	'update it_issue_history set created_by = \'',
	u.party_id,
	'\' where issue_id = \'',
	d.issue_id,
	'\' and history_id = \'',
	d.history_id,
	'\';')
into outfile 'c:/temp/it_issue_history_created_by.sql'
from it_issue_history d, user_login u
where d.created_by = u.user_login_id
	and d.created_by is not null
	and d.created_by <> ''
order by issue_id;

source c:/temp/it_issue_history_created_by.sql;

-- modified_by
select concat(
	'update it_issue_history set modified_by = \'',
	u.party_id,
	'\' where issue_id = \'',
	d.issue_id,
	'\' and history_id = \'',
	d.history_id,
	'\';')
into outfile 'c:/temp/it_issue_history_modified_by.sql'
from it_issue_history d, user_login u
where d.modified_by = u.user_login_id
	and d.modified_by is not null
	and d.modified_by <> ''
order by issue_id;

source c:/temp/it_issue_history_modified_by.sql;

-- assigned_to
select concat(
	'update it_issue_history set assigned_to = \'',
	u.party_id,
	'\' where issue_id = \'',
	d.issue_id,
	'\' and history_id = \'',
	d.history_id,
	'\';')
into outfile 'c:/temp/it_issue_history_assigned_to.sql'
from it_issue_history d, user_login u
where d.assigned_to = u.user_login_id
	and d.assigned_to is not null
	and d.assigned_to <> ''
order by issue_id;

source c:/temp/it_issue_history_assigned_to.sql;


----------
-- TEAM Table
----------

-- created_by
select concat(
	'update team set created_by = \'',
	u.party_id,
	'\' where team_id = \'',
	d.team_id,
	'\';')
into outfile 'c:/temp/team_created_by.sql'
from team d, user_login u
where d.created_by = u.user_login_id
	and d.created_by is not null
	and d.created_by <> ''
order by team_id;

source c:/temp/team_created_by.sql;

-- modified_by
select concat(
	'update team set modified_by = \'',
	u.party_id,
	'\' where team_id = \'',
	d.team_id,
	'\';')
into outfile 'c:/temp/team_modified_by.sql'
from team d, user_login u
where d.modified_by = u.user_login_id
	and d.modified_by is not null
	and d.modified_by <> ''
order by team_id;

source c:/temp/team_modified_by.sql;


----------
-- TEAM_MEMBER Table
----------

-- created_by
select concat(
	'update team_member set created_by = \'',
	u.party_id,
	'\' where team_member_id = \'',
	d.team_member_id,
	'\';')
into outfile 'c:/temp/team_member_created_by.sql'
from team_member d, user_login u
where d.created_by = u.user_login_id
	and d.created_by is not null
	and d.created_by <> ''
order by team_member_id;

source c:/temp/team_member_created_by.sql;

-- modified_by
select concat(
	'update team_member set modified_by = \'',
	u.party_id,
	'\' where team_member_id = \'',
	d.team_member_id,
	'\';')
into outfile 'c:/temp/team_member_modified_by.sql'
from team_member d, user_login u
where d.modified_by = u.user_login_id
	and d.modified_by is not null
	and d.modified_by <> ''
order by team_member_id;

source c:/temp/team_member_modified_by.sql;