/* 用户和菜单动作关系试图 */
drop view if exists vUsersMenuAction;
create view vUsersMenuAction
as
	select distinct t1.usId,t5.meaId
	from smUsers t1
	inner join smUsPermission t2 on t1.usId=t2.usId
	inner join smUsPermRole t3 on t2.uspId=t3.uspId
	inner join smRole t4 on t3.roId=t4.roId and t4.ptValid=1
	inner join smRoleMenuAction t5 on t3.roId=t5.roId 
;

drop view if exists vPlatform;
create view vPlatform
as
	SELECT 
	plId, pltId, beId,siId, plCode, plName, plLoginType, ptValid, plReg, plLogo, plPermNum, plStartDate, plEndDate, ptUpdater, ptUpTime,
	ifnull((select 1 from dual where (NOW() BETWEEN  IFNULL(t.plStartDate,now()) AND IFNULL(t.plEndDate,now())) and t.ptValid),0) plStatus
	FROM smPlatform t;
	
drop view if exists vQueryVarItem;
create view vQueryVarItem
as
	select t1.vaiId code,t1.vaiCodeExt codeExt,t1.vaiName name,t2.vaCode 
	from mtVarItem t1
	inner join mtVariable t2 on t1.vaId=t2.vaId
	order by t1.vaiSort asc;
	
drop view if exists vDistrict;
create view vDistrict
as
	select t1.diId,t1.diName,t2.ciId,t2.ciName,t3.prId,t3.prName, t4.coId,t4.coName
	from mtDistrict t1
	left join mtCity t2 on t1.ciId=t2.ciId
	left join mtProvince t3 on t2.prId=t3.prId
	left join mtCountry t4 on t3.coId=t4.coId;
/*
--create view vPlatform
--as
--	SELECT 
--	plId, pltId, beId,siId, plCode, plName, plLoginType, ptValid, plReg, plLogo, plPermNum, plStartDate, plEndDate, ptUpdater, ptUpTime,
--	ifnull((select 1 from dual where sysdate() between ifnull(t.plStartDate,sysdate()) and ifnull(t.plEndDate,sysdate())),0) plStatus
--	FROM smPlatform t;
*/

drop view if exists vOrgSetOfBook;
create view vOrgSetOfBook
as
SELECT
    t1.orId,
    t5.chId,
    ifnull(t3.bebId,t2.bebId) bebId,
    ifnull(t3.orbDefault,ifnull(t2.orbDefault,0)) orbDefault,
    ifnull(t2.orbDefault,1) orbEnable,
    ifnull(t3.orbSort,t2.orbSort) orbSort
FROM mtOrgInfo t1
INNER JOIN mtOrganization t4 on t1.orParent=t4.orId  /*关联经营主体*/
INNER JOIN mtBizChannel t5 on t5.beId=t4.beId		 /*关联经营主体的结算帐套,用于查询当前组织有多少经营渠道*/
INNER JOIN mtBizEntity t6 ON t4.beId = t6.beId AND t6.ptValid = 1
LEFT JOIN mtOrgSetOfBook t2 ON t1.orParent = t2.orId and t2.chId=t5.chId   /* 关联当前组织的所有上级关联的渠道 */
LEFT JOIN mtOrgSetOfBook t3 ON t1.orId = t3.orId and t3.chId=t5.chId       /* 关联当前组织已经设置过的经营渠道*/
WHERE t1.orLevel = (  /*取最近的上级*/
    SELECT ifnull(max(orLevel),t1.orLevel)     /*查最近上级有设置过组织渠道,的如果查不到,用自己的级别去关联*/
    FROM mtOrgInfo s1
    INNER JOIN mtOrgSetOfBook s2 ON s1.orParent = s2.orId
    WHERE s1.orId<>s1.orParent AND s1.orId = t1.orId
);

drop view if exists vOrgChannel;
create view vOrgChannel
as
	select concat('-',chId) id,chId itemId,chName name,chId,null parent,1 sort from mtChannel where ptValid=1
	union all
	select 
	concat(t1.orId,concat('-',t1.chId)) id,t1.orId itemId,t2.orName name,t1.chId,
	concat(ifnull(t2.orParent,''),concat('-',t1.chId)) parent,
	t2.orSort sort
	from vOrgSetOfBook t1
	inner join mtOrganization t2 on t1.orId=t2.orId
	INNER JOIN mtBizEntity t3 ON t2.beId = t3.beId
	where t1.orbDefault=1 AND t1.orbEnable = 1 AND t3.ptValid = 1
;