SP Areas
unknown
mysql
3 years ago
18 kB
6
Indexable
DROP PROCEDURE `usp_ci_upload_areas`
GO
CREATE PROCEDURE `usp_ci_upload_areas`(
int_year int,
int_period int,
int_userid int,
int_error int,
int_typeid int
)
BEGIN
declare has_error int default 0;
declare required_count int DEFAULT 0;
declare file_required_count int DEFAULT 0;
declare sql_territories varchar(1000) DEFAULT '';
declare sql_areas varchar(1000) DEFAULT '';
drop table if exists tmp_rawdata;
create table tmp_rawdata
(
position varchar(250) DEFAULT NULL,
areacode varchar(250) DEFAULT NULL,
areatype varchar(250) DEFAULT NULL,
areadescription varchar(250) DEFAULT NULL,
areatypeid varchar(250) DEFAULT NULL,
userroleid varchar(250) DEFAULT NULL,
useraccesslevel varchar(250) DEFAULT NULL,
areaaccesslevel varchar(250) DEFAULT NULL,
errormessage varchar(8000) DEFAULT '',
etimestamp timestamp DEFAULT CURRENT_TIMESTAMP,
invalid int default 0
);
drop table if exists error_logs;
create temporary table error_logs
(
position varchar(250) DEFAULT NULL,
areacode varchar(250) DEFAULT NULL,
areatype varchar(250) DEFAULT NULL,
areadescription varchar(250) DEFAULT NULL,
errormessage varchar(8000) DEFAULT NULL
);
drop table if exists tmp_duplicate_logs;
create temporary table tmp_duplicate_logs
(
areacode varchar(250) DEFAULT NULL,
areatype varchar(250) DEFAULT NULL,
areadescription varchar(250) DEFAULT NULL
);
drop table if exists tmp_return_table;
create temporary table tmp_return_table
(
returncode varchar(250) DEFAULT NULL,
message varchar(250) DEFAULT NULL,
pathoutfile varchar(250) DEFAULT NULL
);
drop temporary table if exists temp;
create temporary table temp
(
Position varchar(500) DEFAULT NULL,
TerritoryName varchar(500) DEFAULT NULL,
TerritoryDescription varchar(500) DEFAULT NULL,
territoryareatype varchar(250) DEFAULT NULL,
TerritoryCode varchar(500) DEFAULT NULL,
Eyear varchar(500) DEFAULT NULL,
UserRoleID varchar(500) DEFAULT NULL,
UserAccessLevel varchar(500) DEFAULT NULL,
errormessage varchar(8000) DEFAULT '',
etimestamp timestamp DEFAULT CURRENT_TIMESTAMP,
invalid int default 0
);
drop temporary table if exists tmp_error_logs;
create temporary table tmp_error_logs
(
Position varchar(500) DEFAULT NULL,
TerritoryName varchar(500) DEFAULT NULL,
TerritoryDescription varchar(500) DEFAULT NULL,
TerritoryCode varchar(500) DEFAULT NULL,
errormessage varchar(8000) DEFAULT NULL
);
drop table if exists tmp_terr_duplicate_logs;
create temporary table tmp_terr_duplicate_logs
(
TerritoryName varchar(500) DEFAULT NULL,
TerritoryDescription varchar(500) DEFAULT NULL,
TerritoryCode varchar(500) DEFAULT NULL
);
IF EXISTS (select 1 from tbl_upload_areas where areacode <> '' and areatype <> '' and areadescription <> '' and areatype <> 'Field Personnel Area') THEN
INSERT INTO tmp_rawdata(position, areacode, areatype, areadescription)
SELECT position, areacode, areatype, areadescription
FROM tbl_upload_areas where areatype <> 'Field Personnel Area';
UPDATE tmp_rawdata a
INNER JOIN areatype at
ON a.areatype = at.areatypename
SET a.areatypeid = at.areatypeid
WHERE at.areatypeid in (1,2,3,4);
UPDATE tmp_rawdata SET areaaccesslevel = CASE
WHEN areatypeid = 1 THEN 740
WHEN areatypeid = 2 THEN 730
WHEN areatypeid = 3 THEN 720
WHEN areatypeid = 4 THEN 710
END;
UPDATE tmp_rawdata a
INNER JOIN user_roles ur
ON a.position = ur.rolename
INNER JOIN employeepositions ep
ON ur.userpositionid = ep.employeepositionid
SET a.userroleid = ur.roleid, a.useraccesslevel = ep.accesslevelid
WHERE ep.accesslevelid in (710,720,730,740);
delete from tmp_rawdata
where position is NULL and
areacode is NULL and
areatype is NULL and
areadescription is NULL;
SELECT COUNT(1) INTO required_count
FROM user_roles a
INNER JOIN employeepositions b ON a.userpositionid = b.employeepositionid
WHERE b.accesslevelid IN (710,720,730,740);
IF EXISTS (select 1 from tmp_rawdata where areacode is NULL or areacode = '') THEN
update tmp_rawdata set invalid = 1, errormessage = concat(errormessage, '| AreaCode is Empty') where areacode is NULL or areacode = '';
END IF;
IF EXISTS (select 1 from tmp_rawdata where areatype is NULL or areatype = '') THEN
update tmp_rawdata set invalid = 1, errormessage = concat(errormessage, '| AreaType is Empty') where areatype is NULL or areatype = '';
END IF;
IF EXISTS (select 1 from tmp_rawdata where areadescription is NULL or areadescription = '') THEN
update tmp_rawdata set invalid = 1, errormessage = concat(errormessage, '| AreaDescription is Empty') where areadescription is NULL or areadescription = '';
END IF;
IF EXISTS (select 1 from tmp_rawdata where position is NULL or position = '') THEN
update tmp_rawdata set invalid = 1, errormessage = concat(errormessage, '| Position is Empty') where position is NULL or position = '';
END IF;
IF EXISTS (select 1 from tmp_rawdata tv
inner join areas a
on a.areacode = tv.areacode) THEN
update tmp_rawdata tv
inner join areas a
on a.areacode = tv.areacode
set invalid = 1, errormessage = concat(errormessage, '| AreaCode already Exists');
END IF;
IF EXISTS (select 1 from tmp_rawdata tv
inner join areas a
on a.areadescription = tv.areadescription) THEN
update tmp_rawdata tv
inner join areas a
on a.areadescription = tv.areadescription
set invalid = 1, errormessage = concat(errormessage, '| AreaDescription already Exists');
END IF;
IF EXISTS (select * FROM tmp_rawdata
WHERE areacode REGEXP '[^a-zA-Z0-9[.hyphen-minus.][.space.]]') THEN
update tmp_rawdata set invalid = 1, errormessage = concat(errormessage, '| Invalid AreaCode')
WHERE areacode REGEXP '[^a-zA-Z0-9[.hyphen-minus.][.space.]]';
END IF;
IF EXISTS (select * FROM tmp_rawdata
WHERE areadescription REGEXP '[^a-zA-Z0-9[.hyphen-minus.][.space.]]') THEN
update tmp_rawdata set invalid = 1, errormessage = concat(errormessage, '| Invalid AreaDescription')
WHERE areadescription REGEXP '[^a-zA-Z0-9[.hyphen-minus.][.space.]]';
END IF;
IF EXISTS (select 1 FROM tmp_rawdata
WHERE areatypeid is NULL) THEN
update tmp_rawdata set invalid = 1, errormessage = concat(errormessage, '| Unknown AreaType')
WHERE areatypeid is NULL;
END IF;
IF EXISTS (select 1 FROM tmp_rawdata
WHERE userroleid is NULL) THEN
update tmp_rawdata set invalid = 1, errormessage = concat(errormessage, '| Unknown Position')
WHERE userroleid is NULL;
END IF;
IF EXISTS (select 1 FROM tmp_rawdata
group by areacode having count(1) > 1) THEN
insert into tmp_duplicate_logs(areacode, areatype, areadescription)
select areacode, areatype, areadescription
from tmp_rawdata group by areacode having count(1) > 1;
update tmp_rawdata set invalid = 1, errormessage = concat(errormessage, '| Duplicate AreaCode')
where (areacode) in (
select areacode from tmp_duplicate_logs);
END IF;
truncate table tmp_duplicate_logs;
IF EXISTS (select 1 FROM tmp_rawdata
group by areadescription having count(1) > 1) THEN
insert into tmp_duplicate_logs(areacode, areatype, areadescription)
select areacode, areatype, areadescription
from tmp_rawdata group by areadescription having count(1) > 1;
update tmp_rawdata set invalid = 1, errormessage = concat(errormessage, '| Duplicate AreaDescription')
where (areadescription) in (
select areadescription from tmp_duplicate_logs);
END IF;
SELECT COUNT(distinct userroleid) INTO file_required_count
FROM tmp_rawdata
WHERE invalid <> 1;
IF (file_required_count <> required_count) THEN
update tmp_rawdata
set invalid = 1, errormessage = concat(errormessage, '| Please select 1 for every Positions');
END IF;
IF EXISTS (SELECT 1 FROM tmp_rawdata
WHERE useraccesslevel <> areaaccesslevel) THEN
update tmp_rawdata set invalid = 1, errormessage = concat(errormessage, '| Incorrect AreaType Assignment')
WHERE useraccesslevel <> areaaccesslevel;
END IF;
IF EXISTS (select 1 from tmp_rawdata where invalid = 1) THEN
set sql_areas = 'union all (select position, areacode, areatype, areadescription, errormessage
from tmp_rawdata where invalid = 1) ';
END IF;
ELSE
INSERT INTO tmp_return_table(returncode, message, pathoutfile)
VALUES(2, 'Empty File', '');
END IF;
IF EXISTS(select 1 from tbl_upload_areas where areacode <> '' and areatype <> '' and areadescription <> '' and areatype = 'Field Personnel Area') THEN
insert into temp(Position, TerritoryName, TerritoryDescription, territoryareatype, TerritoryCode)
select Position, AreaCode, AreaDescription, Areatype, AreaCode from tbl_upload_areas where areatype = 'Field Personnel Area';
update temp a
inner join territories t
on a.TerritoryName = t.TerritoryName and a.TerritoryDescription = t.territorydescription
set a.Eyear = t.Eyear;
UPDATE temp a
INNER JOIN user_roles ur
ON a.position = ur.rolename
INNER JOIN employeepositions ep
ON ur.userpositionid = ep.employeepositionid
SET a.userroleid = ur.roleid, a.useraccesslevel = ep.accesslevelid
WHERE ep.accesslevelid in (800);
if exists(select 1 from temp where Eyear is null) then
update temp set Eyear = YEAR(CURDATE())
where Eyear is null;
end if;
delete from temp where
Position is null and
TerritoryName is null and
TerritoryDescription is null and
TerritoryCode is null and
Eyear is null;
if exists(select 1 from temp where TerritoryDescription is null or TerritoryDescription = '') then
update temp set invalid = 1, errormessage = concat(errormessage, '| TerritoryDescription is Empty ') where TerritoryDescription is NULL or TerritoryDescription = '';
end if;
if exists(select 1 from temp where TerritoryCode is null or TerritoryCode = '') then
update temp set invalid = 1, errormessage = concat(errormessage, '| TerritoryCode is Empty ') where TerritoryCode is NULL or TerritoryCode = '';
end if;
if exists(select 1 from temp where Position is null or Position = '') then
update temp set invalid = 1, errormessage = concat(errormessage, '| Position is Empty ') where Position is NULL or Position = '';
end if;
IF EXISTS (select 1 from temp a
inner join territories t
on t.TerritoryDescription = a.TerritoryDescription) THEN
update temp a
inner join territories t
on a.TerritoryDescription = t.TerritoryDescription
set invalid = 1, errormessage = concat(errormessage, '| TerritoryDescription already Exists');
END IF;
IF EXISTS (select 1 from temp a
inner join territories t
on t.TerritoryCode = a.TerritoryCode) THEN
update temp a
inner join territories t
on a.TerritoryCode = t.TerritoryCode
set invalid = 1, errormessage = concat(errormessage, '| TerritoryCode already Exists');
END IF;
IF EXISTS (select 1 from temp a
inner join territories t
on t.TerritoryCode = a.TerritoryCode
and a.Eyear = t.Eyear) THEN
update temp a
inner join territories t
on a.TerritoryCode = t.TerritoryCode
and a.Eyear = t.Eyear
set invalid = 1, errormessage = concat(errormessage, '| TerritoryCode - Year already Exists');
END IF;
if exists(select TerritoryName from temp group by TerritoryName having count(1) > 1) then
insert into tmp_terr_duplicate_logs(TerritoryName, TerritoryCode, TerritoryDescription)
select TerritoryName, TerritoryCode, TerritoryDescription
from temp group by TerritoryDescription having count(TerritoryDescription) > 1;
update temp set invalid = 1, errormessage = concat(errormessage, '| Duplicate TerritoryDescription')
where TerritoryDescription in (
select TerritoryDescription from tmp_terr_duplicate_logs);
end if;
if exists(select TerritoryCode from temp group by TerritoryCode having count(1) > 1) then
insert into tmp_terr_duplicate_logs(TerritoryName, TerritoryCode, TerritoryDescription)
select TerritoryName, TerritoryCode, TerritoryDescription
from temp group by TerritoryCode having count(TerritoryCode) > 1;
update temp set invalid = 1, errormessage = concat(errormessage, '| Duplicate TerritoryCode')
where TerritoryCode in (
select TerritoryCode from tmp_terr_duplicate_logs);
end if;
if exists(select TerritoryDescription from temp
WHERE TerritoryDescription REGEXP '[^a-zA-Z0-9[.space.][.hyphen-minus.]]') THEN
update temp set invalid = 1, errormessage = concat(errormessage, '| Invalid TerritoryDescription')
WHERE TerritoryDescription REGEXP '[^a-zA-Z0-9[.space.][.hyphen-minus.]]';
end if;
if exists(select TerritoryCode from temp
WHERE TerritoryCode REGEXP '[^a-zA-Z0-9[.space.][.hyphen-minus.]]') THEN
update temp set invalid = 1, errormessage = concat(errormessage, '| Invalid TerritoryCode')
WHERE TerritoryCode REGEXP '[^a-zA-Z0-9[.space.][.hyphen-minus.]]';
end if;
IF EXISTS (SELECT 1 FROM temp
WHERE useraccesslevel <> 800) THEN
update temp set invalid = 1, errormessage = concat(errormessage, '| Incorrect User Role Assignment')
WHERE useraccesslevel <> 800;
END IF;
IF EXISTS (select 1 FROM temp
WHERE userroleid is NULL) THEN
UPDATE temp set invalid = 1, errormessage = concat(errormessage, '| Unknown Position')
WHERE userroleid is NULL;
END IF;
if exists(select 1 from temp where invalid = 1) THEN
set sql_territories = 'union all (select Position, TerritoryCode, TerritoryDescription, Eyear, errormessage
from temp where invalid = 1) ';
end IF;
ELSE
INSERT INTO tmp_return_table(returncode, message, pathoutfile)
VALUES(2, 'Empty File', '');
END IF;
if (sql_areas = '' and sql_territories = '') THEN
INSERT INTO tmp_return_table(returncode, message, pathoutfile)
VALUES(0, 'Successful', '');
if exists(select 1 from temp where Errormessage = '') then
INSERT INTO territories(TerritoryCode, TerritoryName, ETimeStamp, Eyear, RoleID, territorydescription)
SELECT a.TerritoryCode, a.TerritoryName, a.ETimeStamp, YEAR(CURDATE()), a.UserRoleID, a.territorydescription
FROM temp a;
end if;
INSERT INTO areas(AreaName, Active, ETimeStamp, AreaTypeID, AreaCode, AreaDescription, RoleID)
SELECT tv.areacode, 1, CURRENT_TIMESTAMP, tv.areatypeid, tv.areacode, tv.areadescription, tv.userroleid
FROM tmp_rawdata tv;
END IF;
drop table if exists tbl_areas;
create table tbl_areas
(
position varchar(250) DEFAULT NULL,
areacode varchar(250) DEFAULT NULL,
areatype varchar(250) DEFAULT NULL,
areadescription varchar(250) DEFAULT NULL
);
drop table if exists areas_data_error;
create table areas_data_error
(
position varchar(250) DEFAULT NULL,
areacode varchar(250) DEFAULT NULL,
areatype varchar(250) DEFAULT NULL,
areadescription varchar(250) DEFAULT NULL,
errormessage varchar(8000) DEFAULT NULL
);
insert into tbl_areas(position, areacode, areatype, areadescription)
select Position, AreaCode, AreaType, AreaDescription from tmp_rawdata where invalid != 1
UNION
select Position, TerritoryCode, territoryareatype, TerritoryDescription from temp where invalid != 1;
INSERT INTO areas_data_error(position, areacode, areatype, areadescription, errormessage)
SELECT position, areacode, areatype, areadescription, errormessage FROM tmp_rawdata where invalid = 1
UNION
select Position, TerritoryCode, territoryareatype, TerritoryDescription,errormessage from temp where invalid = 1;
if exists(select 1 from areas_data_error)then
if has_error != 2 then
set has_error = 3;
end if;
end if;
truncate tbl_upload_areas;
select has_error;
ENDEditor is loading...