Tr version 4.0.4

来自ling
跳转至: 导航搜索

0002691: 决策树的研发费用加计扣除模块有排序,但是业务处理中做的一开始就是结束

http://113.204.139.16/mantis/view.php?id=2691

--确认id是57还是125
select * from tr_InformationMoudleRelation;
select * from tr_Knowledge t where t.id=125 or t.id=57
update tr_ChecklistMoudle set  moudleId=125 where moudleId=57;
update tr_InformationMoudleRelation set moudleId=125 where moudleId=57;

索引优化

分析的sql语句

--0.106S-->
select defaultuse0_.* from BASE_USER defaultuse0_ where defaultuse0_.USERNAME_='NYAN';

--1
create index IDX_USER_USERNAME on BASE_USER (USERNAME_ ASC,ENABLED_ ASC);

--DROP INDEX IDX_USER_USERNAME ON BASE_USER;

select distinct defaultrol0_.* from BASE_ROLE defaultrol0_ 
cross join BASE_ROLE_MEMBER rolemember1_ where rolemember1_.ROLE_ID_=defaultrol0_.ID_ 
and rolemember1_.USERNAME_='nyan' order by defaultrol0_.sortOrder desc;

create index IDX_ROLEMEMBER_ROLEID on BASE_ROLE_MEMBER (ROLE_ID_ ASC,USERNAME_ ASC);

--DROP INDEX IDX_ROLEMEMBER_ROLEID ON BASE_ROLE_MEMBER;

--0.105s -->0.112S
 select defaultuse0_.* from BASE_USER defaultuse0_ where defaultuse0_.tradmin=1;
-- create index IDX_BASE_USER_ISTRADMIN on BASE_USER (tradmin ASC);

--DROP INDEX IDX_BASE_USER_ISTRADMIN ON BASE_USER;


 select distinct defaultuse0_.*
  from BASE_USER defaultuse0_ cross join BASE_ROLE defaultrol1_ cross join BASE_ROLE_MEMBER 
  rolemember2_ where rolemember2_.ROLE_ID_=defaultrol1_.ID_ and defaultuse0_.USERNAME_=rolemember2_.USERNAME_
   and defaultrol1_.NAME_='admin';


   select defaulturl1_.* from BASE_ROLE_RESOURCE roleresour0_ cross join 
   BASE_URL defaulturl1_ where roleresour0_.URL_ID_=defaulturl1_.ID_ and roleresour0_.ROLE_ID_='';


create index IDX_ROLERESOURCE_URLID_ROLEID on BASE_ROLE_RESOURCE (ROLE_ID_ ASC,URL_ID_ ASC);


select fileitem0_.*
 from FM_FILE fileitem0_ where 1=1
  and (exists (select 1 from QRR_Project qrrproject1_ 
  where fileitem0_.PROJECT_ID=qrrproject1_.id and 
  (qrrproject1_.PIC='nyan' or qrrproject1_.PPIC='nyan' or qrrproject1_.MIC='nyan' or
   qrrproject1_.MIC2='nyan' or qrrproject1_.SPIC='nyan' or qrrproject1_.AIC like 'nyan'
    or qrrproject1_.STAFF like 'nyan' or qrrproject1_.NTC like 'nyan' or qrrproject1_.id
	 in (select qrritement2_.projectItemId from QRR_QrrItemEntity qrritement2_ 
	 where qrrproject1_.id=qrritement2_.projectItemId and (qrritement2_.AIC='nyan' or
	  qrritement2_.STAFF='nyan'))))) and fileitem0_.FLAG=1 and fileitem0_.ITEM_TYPE=''
	   and fileitem0_.UPDATE_TIME>='' and fileitem0_.UPDATE_TIME<'' order by fileitem0_.UPDATE_TIME desc


	   select * from QRR_Project qrrproject1_ 
  where  
  (qrrproject1_.PIC='nyan' or qrrproject1_.PPIC='nyan' or qrrproject1_.MIC='nyan' or
   qrrproject1_.MIC2='nyan' or qrrproject1_.SPIC='nyan' or qrrproject1_.AIC like 'nyan'
    or qrrproject1_.STAFF like 'nyan' or qrrproject1_.NTC like 'nyan' or qrrproject1_.id
	 in (select qrritement2_.projectItemId from QRR_QrrItemEntity qrritement2_ 
	 where qrrproject1_.id=qrritement2_.projectItemId and (qrritement2_.AIC='nyan' or
	  qrritement2_.STAFF='nyan')));


	  select * from QRR_Project qrrproject1_ 
  where  
  qrrproject1_.PIC='nyan' or qrrproject1_.PPIC='nyan' or qrrproject1_.MIC='nyan' or
   qrrproject1_.MIC2='nyan' or qrrproject1_.SPIC='nyan' or qrrproject1_.AIC like 'nyan'
    or qrrproject1_.STAFF like 'nyan' or qrrproject1_.NTC like 'nyan';


	SELECT (SELECT SUM(CASE T1.STATUS WHEN 'CLOSE' THEN 1 ELSE 0 END) FROM TR_CHECKLISTITEM T1 WHERE T1.PROJECTID=T.ID) CLCLOSE,
	 (SELECT COUNT(*) FROM TR_CHECKLISTITEM T1 WHERE T1.PROJECTID=T.ID ) CLCOUNT, 
	 (SELECT SUM(CASE Q.STATE WHEN '0' THEN 1 ELSE 0 END) FROM TR_QUESTION Q,TR_CHECKLISTITEM T1
	 			WHERE T1.PROJECTID=T.ID AND Q.CHECKLISTID=T1.ID) QLCLOSE,
				 (SELECT COUNT(*) FROM TR_QUESTION Q,TR_CHECKLISTITEM T1 WHERE T1.PROJECTID=T.ID AND Q.CHECKLISTID=T1.ID) QLCOUNT,
				  (SELECT SUM(CASE I.STATUS WHEN '0' THEN 1 ELSE 0 END) FROM TR_ISSUESUMMARY I,TR_CHECKLISTITEM T1		
				  	WHERE T1.PROJECTID=T.ID AND I.CHECKLISTID=T1.ID) ISCLOSE, (SELECT COUNT(*) FROM TR_ISSUESUMMARY I,TR_CHECKLISTITEM T1 
					WHERE T1.PROJECTID=T.ID AND I.CHECKLISTID=T1.ID) ISCOUNT, (SELECT SUM(CASE TM.STATUS WHEN '0' THEN 1 ELSE 0 END) 
					FROM TR_TAXMEMO TM WHERE TM.PROJECTITEMID=T.ID) TMCLOSE,
					 (SELECT COUNT(*) FROM TR_TAXMEMO TM WHERE TM.PROJECTITEMID=T.ID) TMCOUNT,
					  (SELECT COUNT(*) FROM TR_ITEMENTITY IE WHERE IE.PROJECTITEMID=T.ID) IECOUNT, 
	(SELECT COUNT(*) FROM TR_ITEMENTITY IE,TR_CHECKOPERATEREPORT COR,TR_CHECKLISTENTITY CE		
		WHERE IE.PROJECTITEMID=T.ID AND IE.ID=CE.ENTITYID AND COR.CHECKLISTID=CE.CHECKLISTID AND OPERATE='FINISH') 
		IECLOSE,	T.ID,T.MIC,T.PIC FROM TR_PROJECTITEM T WHERE T.ISDELETE=0 and t.isRecord=0 
		 AND (T.PIC='nyan' OR MIC='nyan' OR EXISTS (SELECT 1 FROM TR_ITEMENTITY IE WHERE IE.PROJECTITEMID=T.ID 
		 AND (IE.AIC='nyan' OR IE.STAFF='nyan')));


select itementity1_.*
from TR_ProjectItem projectite0_ cross join TR_ItemEntity itementity1_ 
where projectite0_.istemplate=0 and projectite0_.id=itementity1_.projectItemId 
and projectite0_.isRecord=0 and projectite0_.isDelete=0 and 
(projectite0_.aic='nyan' or projectite0_.mic='nyan' or projectite0_.pic='nyan' 
or projectite0_.staff='nyan' or projectite0_.ppic='nyan' or itementity1_.aic='nyan' 
or itementity1_.staff='nyan')

select count(projectite0_.id) as col_0_0_ from TR_ProjectItem projectite0_ 
where projectite0_.istemplate=0 and 1=1 and projectite0_.isRecord=0 and projectite0_.isDelete=0 
and (projectite0_.aic='nyan' or projectite0_.mic='nyan' 
or projectite0_.pic='nyan' or projectite0_.staff='nyan' or projectite0_.ppic='nyan') 
and  not (exists (select 1 from TR_ItemEntity itementity1_ where projectite0_.id=itementity1_.projectItemId))


select top 6 notices0_.*
 from TR_Notices notices0_ where notices0_.NoticesType=1 order by notices0_.CreateDate desc;

 select top 10 notices0_.* from TR_Notices notices0_ where notices0_.NoticesType=2 order by notices0_.CreateDate desc

 select count(*) as col_0_0_ from TR_SendInfo sendinfo0_ 
 cross join TR_Message message1_ where sendinfo0_.message_id=message1_.id and 
 sendinfo0_.is_Close=1 and sendinfo0_.is_Read=0 and sendinfo0_.recipient='nyan' and message1_.bussiness_Type='';


 select count(*) as col_0_0_ from TR_SendInfo sendinfo0_ cross join TR_Message message1_ 
 where sendinfo0_.message_id=message1_.id and sendinfo0_.is_Close=1 
 and sendinfo0_.is_Read=0 and sendinfo0_.recipient='nyan' and message1_.bussiness_Type=1;

 
 --数据少
 select count(*) as col_0_0_ from TR_WorkPlanCustom workplancu0_ where workplancu0_.userId='nyan'
  and (workplancu0_.msgStatus in (0)) and year(workplancu0_.startTime)=year(getdate()) 
  and month(workplancu0_.startTime)=month(getdate()) and day(workplancu0_.startTime)=day(getdate());

SELECT ISNULL(( SELECT top(1) 1 FROM qrr_Project qp WHERE 1=1  and  
(AIC like 'nyan' or MIC ='nyan' or MIC2 ='nyan' or PIC ='nyan' 
or STAFF like 'nyan' or PPIC ='nyan' or ntc like 'nyan' or spic ='nyan'  
 or qp.id in (select projectItemId from QRR_QrrItemEntity  
 where qp.id = projectItemId AND (aic ='nyan' or staff ='nyan'  ))) ), 0);


select count(*) as col_0_0_ from TR_ProjectItem projectite0_ 
where projectite0_.istemplate=0 and 1=1 and 
projectite0_.isDelete=0 and projectite0_.fisY>=2015 and projectite0_.fisY<=2016;

select top 20 projectite0_.*
 from TR_ProjectItem projectite0_ 
 where projectite0_.istemplate=0 and 1=1 
 and projectite0_.isDelete=0 and 
 projectite0_.fisY>=2015 and projectite0_.fisY<=2018 
 order by projectite0_.isRecord, projectite0_.LASTMODIFIED desc, 
 projectite0_.clientCode, projectite0_.year;

 select projectite0_.* from TR_ProjectItemApp projectite0_ 
 where 1=1 and projectite0_.projectItemId=11 and 
 projectite0_.validity=1 and projectite0_.type='' 
 and projectite0_.status<>'';

 select itementity0_.* from TR_ItemEntity itementity0_ where 1=1 
 and itementity0_.projectItemId=11 and itementity0_.isHidden=0 order by itementity0_.id desc;

 select entitygrou0_.*
  from TR_EntityGroupId entitygrou0_ where entitygrou0_.entityId=11


  select auditinfor0_.*
   from TR_AuditInformation auditinfor0_ where 1=1 and auditinfor0_.projectItemId=11

   select br.name_ from base_user bu,base_role_member brm ,
   base_role br where bu.username_=brm.username_ and brm.role_id_=br.id_ 
   and bu.username_= 'NYAN' order by br.sortOrder desc 

   select count(*) as col_0_0_ from BASE_USER defaultuse0_ 
   cross join BASE_ROLE defaultrol1_ cross join BASE_ROLE_MEMBER 
   rolemember2_ where 1=1 and defaultuse0_.USERNAME_=rolemember2_.USERNAME_ 
   and rolemember2_.ROLE_ID_=defaultrol1_.ID_ and 
   (defaultrol1_.NAME_ in ('')) and defaultuse0_.ENABLED_=1;

   select b.SERVICEGROUP_,SERVICEGROUP_Desc 
   from BASE_USER b where b.SERVICEGROUP_Desc is not null  
   	and b.SERVICEGROUP_ is not null  	and b.SERVICEGROUP_ !=''  	
	and b.SERVICEGROUP_Desc !=''  and b.ENABLED_=1  AND b.department=''
	group by b.SERVICEGROUP_,b.SERVICEGROUP_Desc order by b.SERVICEGROUP_Desc 


	select distinct top 10 defaultuse0_.* 
	from BASE_USER defaultuse0_ cross join BASE_ROLE defaultrol1_ 
	cross join BASE_ROLE_MEMBER rolemember2_ where 1=1 and 
	defaultuse0_.USERNAME_=rolemember2_.USERNAME_ and 
	rolemember2_.ROLE_ID_=defaultrol1_.ID_ and (defaultrol1_.NAME_ in ('11')) 
	and (defaultuse0_.SERVICEGROUP_ in ('1' , '2' , '3')) and defaultuse0_.STAFFNO_='23' 
	and defaultuse0_.ENABLED_=1 and (defaultuse0_.department like '2%')


	select count(itementity1_.id) as col_0_0_ from TR_ProjectItem projectite0_ cross join TR_ItemEntity itementity1_
	 where projectite0_.istemplate=0 and projectite0_.id=itementity1_.projectItemId 
	 and isRecord=1 and fisY>=2015 and fisY<=2018 and projectite0_.isDelete=0 and 
	 itementity1_.isHidden=0 and (itementity1_.mic='nyan' or itementity1_.pic='nyan' or itementity1_.aic='nyan' 
	 or itementity1_.staff='nyan')


	select count(projectite0_.id) as col_0_0_ from TR_ProjectItem projectite0_ 
	where projectite0_.istemplate=0 and 1=1 and projectite0_.isRecord=1 and 
	projectite0_.fisY>=2015 and projectite0_.fisY<=2018 and projectite0_.isDelete=0 and
	 (projectite0_.aic='nyan' or projectite0_.mic='nyan' or projectite0_.pic='nyan' or projectite0_.staff='nyan' 
	 or projectite0_.ppic='nyan')


	 select itementity0_.*
	  from TR_ItemEntity itementity0_ where 1=1 and itementity0_.projectItemId=11 
	  and itementity0_.isHidden=0 order by itementity0_.id desc

	  select entitygrou0_.* from TR_EntityGroupId entitygrou0_ where entitygrou0_.entityId=1

	  select top 100 projectite0_.*
	   from TR_ProjectItem projectite0_ where projectite0_.istemplate=0 and 1=1 and 
	   projectite0_.isDelete=0 and projectite0_.fisY>=2015 and projectite0_.fisY<=2018 
	   order by projectite0_.isRecord, projectite0_.LASTMODIFIED desc, projectite0_.clientCode, 
	   projectite0_.year

 select top 19 * from (	select tp.clientname,tp.isgroup,'' as customername,tp.businesstype,tp.auditTaskCode,
 tp.pic,tp.mic,tp.aic,tp.staff,    tp.auditcobber,tp.auditmanager,tap.roleid,tap.status, 
    tap.initiateuserid,tap.newuserid,tap.approver,tap.applicantdate,tap.validity,tap.id,tp.ppic,
	tp.engProjectDesc,tp.clientCode     from tr_projectitemapp tap left join tr_projectitem tp 
	on tap.projectitemid=tp.id 
 where 1 = 1 and tap.type='main' and tp.isRecord ='0'  
 and tap.validity=1 and tap.status=1 and tap.initiateuserid='nyan'   
  union all  select tp.clientName,tp.isgroup,ti.customername,tp.businesstype,tp.auditTaskCode,tp.pic,tp.mic,ti.aic,ti.staff		   ,tp.auditcobber,tp.auditmanager,tap.roleid,tap.status,tap.initiateuserid,tap.newuserid 		  
    ,tap.approver,tap.applicantdate,tap.validity,tap.id,tp.ppic,tp.engProjectDesc,tp.clientCode
	 from tr_projectitemapp tap left join tr_itementity  ti on tap.projectitemid=ti.id 		 
	   left join tr_projectitem tp on ti.projectitemid=tp.id where ti.isHidden='0'  and tap.type='comply' 
	   and tp.isRecord ='0'  and tap.validity=1 and tap.status=1 and tap.initiateuserid='nyan'  )a 
	   order by a.applicantdate

--
select top 100 * from (	select tp.clientname,tp.isgroup,'' as customername,tp.businesstype,tp.auditTaskCode,tp.pic,tp.mic,tp.aic,tp.staff,    tp.auditcobber,tp.auditmanager,tap.roleid,tap.status,    tap.initiateuserid,tap.newuserid,tap.approver,tap.applicantdate,tap.validity,tap.oldUserId     from tr_projectitemapp tap left join tr_projectitem tp on tap.projectitemid=tp.id 
where 1 = 1 and tap.type='main' and tp.isRecord ='0'  and tap.status=1 and tap.validity=1     
union all  select tp.clientName,tp.isgroup,ti.customername,tp.businesstype,tp.auditTaskCode,tp.pic,tp.mic,ti.aic,ti.staff		   ,tp.auditcobber,tp.auditmanager,tap.roleid,tap.status,tap.initiateuserid,tap.newuserid 		    ,tap.approver,tap.applicantdate,tap.validity,tap.oldUserId from tr_projectitemapp tap left join tr_itementity  ti on tap.projectitemid=ti.id 		   left join tr_projectitem tp on ti.projectitemid=tp.id where ti.isHidden='0'  and tap.type='comply' and tp.isRecord ='0' 
 and tap.status=1 and tap.validity=1 and tap.projectitemid in ( select t.id from TR_ItemEntity t where 1=1  
  and (t.aic ='nyan' or t.staff ='nyan') ))a order by a.applicantdate

select top 19 * from (	select tp.clientname,tp.isgroup,'' as customername,tp.businesstype,tp.auditTaskCode,tp.pic,tp.mic,tp.aic,tp.staff,    tp.auditcobber,tp.auditmanager,tap.roleid,tap.status,    tap.initiateuserid,tap.newuserid,tap.approver,tap.applicantdate,tap.validity,tap.id,tp.ppic,tp.engProjectDesc,tp.clientCode     
from tr_projectitemapp tap left join tr_projectitem tp on tap.projectitemid=tp.id where 1 = 1 
and tap.type='main' and tp.isRecord ='0'  and tap.validity=1 and tap.status=1 and tap.initiateuserid='nyan'   
  union all  select tp.clientName,tp.isgroup,ti.customername,tp.businesstype,
  tp.auditTaskCode,tp.pic,tp.mic,ti.aic,ti.staff		   ,tp.auditcobber,tp.auditmanager,tap.roleid,tap.status,
  tap.initiateuserid,tap.newuserid 		    ,tap.approver,tap.applicantdate,tap.validity,tap.id,tp.ppic,
  tp.engProjectDesc,tp.clientCode from tr_projectitemapp tap left join tr_itementity  ti 
  on tap.projectitemid=ti.id 		   left join tr_projectitem tp on ti.projectitemid=tp.id
   where ti.isHidden='0'  and tap.type='comply' and tp.isRecord ='0'  and tap.validity=1 and tap.status=1
    and tap.initiateuserid='nyan')a order by a.applicantdate

--人员生效
	select * from (   select tap.id,tp.id proid,tp.clientname,tp.isgroup,tp.clientcode,tp.businesstype,tp.auditTaskCode,   tp.auditCobber,tp.auditManager, tap.roleid,tap.olduserid,tap.newuserid,tap.applicantdate,tap.submitDate   ,tp.begDate,tp.endDate,tp.auditAIC,u.department,tap.applyReason,tap.initiateUserId  
	from tr_projectitemapp tap left join tr_projectitem tp on tap.projectitemid=tp.id LEFT JOIN BASE_USER  u on tap.newuserid=u.USERNAME_   where tap.validity='0' and tap.status ='0003'  and tap.type='main' and tp.isRecord ='0'    
	union all    select tap.id,ti.id proid, tp.clientname,tp.isgroup,ti.customername, tp.businesstype,tp.auditTaskCode,    tp.auditCobber,tp.auditManager, tap.roleid,tap.olduserid,tap.newuserid,tap.applicantdate,    tap.submitDate,tp.begDate,tp.endDate,tp.auditAIC,u.department,tap.applyReason,tap.initiateUserId 
	from tr_projectitemapp tap left join tr_itementity ti on tap.projectitemid=ti.id LEFT JOIN BASE_USER  u on tap.newuserid=u.USERNAME_  left join tr_projectitem tp on ti.projectitemid = tp.id where ti.isHidden='0' and tp.isRecord ='0'   and tap.validity='0' and  tap.status ='0003' and tap.type='comply'  )a order by a.applicantDate

--项目code填写
select top 100 projectite0_.* from TR_ProjectItem projectite0_ 
where projectite0_.istemplate=0 and 1=1 and codestatus=1 and 
(projectite0_.isRecord=0 or projectite0_.isRecord is null) and 
projectite0_.isDelete=0 order by projectite0_.applydate, projectite0_.LASTMODIFIED desc, 
projectite0_.clientCode, projectite0_.year
--项目code填写
select top 100 projectite0_.id as id2_100_, projectite0_.aic as aic3_100_, projectite0_.applydate as applydat4_100_, projectite0_.auditAIC as auditAIC5_100_, projectite0_.auditCobber as auditCob6_100_, projectite0_.auditcostcenter as auditcos7_100_, projectite0_.auditManager as auditMan8_100_, projectite0_.auditStatus as auditSta9_100_, projectite0_.auditTaskCode as auditTa10_100_, projectite0_.backReason as backRea11_100_, projectite0_.bark as bark12_100_, projectite0_.begDate as begDate13_100_, projectite0_.businessType as busines14_100_, projectite0_.cTSNo as cTSNo15_100_, projectite0_.clientCode as clientC16_100_, projectite0_.clientName as clientN17_100_, projectite0_.code as code18_100_, projectite0_.codestatus as codesta19_100_, projectite0_.company as company20_100_, projectite0_.ctaCode as ctaCode21_100_, projectite0_.endDate as endDate22_100_, projectite0_.estimatedBegDate as estimat23_100_, projectite0_.estimatedEndDate as estimat24_100_, projectite0_.feeApplyStatus as feeAppl25_100_, projectite0_.FIRSTINSERT as FIRSTIN26_100_, projectite0_.groupCode as groupCo27_100_, projectite0_.hasManuscript as hasManu28_100_, projectite0_.INSERTMAN as INSERTM29_100_, projectite0_.isDelete as isDelet30_100_, projectite0_.isGroup as isGroup31_100_, projectite0_.isRecord as isRecor32_100_, projectite0_.itemstatus as itemsta33_100_, projectite0_.LASTMODIFIED as LASTMOD34_100_, projectite0_.mic as mic35_100_, projectite0_.name as name36_100_, projectite0_.pic as pic37_100_, projectite0_.ppic as ppic38_100_, projectite0_.remark as remark39_100_, projectite0_.remarkTwo as remarkT40_100_, projectite0_.rrRate as rrRate41_100_, projectite0_.serviceID as service42_100_, projectite0_.serviceoffering as service43_100_, projectite0_.serviceofferingdescription as service44_100_, projectite0_.staff as staff45_100_, projectite0_.taxCobber as taxCobb46_100_, projectite0_.taxDirector as taxDire47_100_, projectite0_.taxManager as taxMana48_100_, projectite0_.taxSecondCobber as taxSeco49_100_, projectite0_.tempcode as tempcod50_100_, projectite0_.UPDATEMAN as UPDATEM51_100_, projectite0_.workload as workloa52_100_, projectite0_.year as year53_100_, projectite0_.engProjectDesc as engProj54_100_, projectite0_.fisY as fisY55_100_, projectite0_.isUserOpen as isUserO56_100_, projectite0_.oppSrvID as oppSrvI57_100_, projectite0_.sapKey as sapKey58_100_ from TR_ProjectItem projectite0_ where projectite0_.istemplate=0 and 1=1 and 
codestatus=1 and (projectite0_.isRecord=0 or projectite0_.isRecord is null) and projectite0_.isDelete=0 
and (projectite0_.clientCode like '%dafds%') order by projectite0_.applydate, 
projectite0_.LASTMODIFIED desc, projectite0_.clientCode, projectite0_.year
--参与项目年度差异
select projectite0_.*
 from TR_ProjectItem projectite0_ where 
 projectite0_.istemplate=0 and 1=1 and projectite0_.year=2017 
 and (projectite0_.aic='nyan' or projectite0_.pic='nyan' or projectite0_.mic='nyan' or projectite0_.staff='nyan') 
 order by projectite0_.isRecord, projectite0_.LASTMODIFIED desc, projectite0_.clientCode, projectite0_.year
--select br.name_ from base_user bu,base_role_member brm ,base_role br where bu.username_=brm.username_ and brm.role_id_=br.id_ and bu.username_= 'nyan' order by br.sortOrder desc 
--调整rr生成code
select top 100 projectite0_.*
 from TR_ProjectItem projectite0_ where projectite0_.istemplate=0 
 and 1=1 and projectite0_.isDelete=0 and (projectite0_.clientName like '%dd%') and (projectite0_.pic='nyan' or projectite0_.mic='nyan' 
 or projectite0_.aic='nyan' or projectite0_.staff='nyan' or exists 
 (select 1 from TR_ItemEntity itementity1_ where itementity1_.projectItemId=projectite0_.id 
 and (itementity1_.aic='nyan' or itementity1_.staff='nyan'))) and projectite0_.isRecord=0 
 order by projectite0_.isRecord, projectite0_.LASTMODIFIED desc, 
 projectite0_.clientCode, projectite0_.year
 --工作进度查询
 SELECT top 100 * FROM (SELECT ID,NAME,CTACODE,CLIENTNAME,CLIENTCODE,BEGDATE,ENDDATE,ISGROUP,PIC,MIC,AIC,STAFF,ITEMSTATUS,AUDITSTATUS,CLPERCENT, CASE WHEN CLPERCENT=100 AND ISPERCENT=100 AND QLPERCENT=0 THEN 100 ELSE QLPERCENT END QLPERCENT, ISPERCENT,TMPERCENT,RETURNED,APPLY,APPROVE,GROUPTYPE,AUDITCOBBER,AUDITMANAGER,AUDITAIC,AUDITAPPROVE,t_Year,engProjectDesc FROM   (SELECT T.ID,T.NAME,T.CTACODE,T.CLIENTNAME,T.CLIENTCODE,T.BEGDATE,T.ENDDATE,T.ISGROUP,AUDITCOBBER,AUDITMANAGER,AUDITAIC,  (SELECT TOP 1 ENAME_ FROM BASE_USER BU WHERE BU.USERNAME_=T.PIC) PIC,  (SELECT TOP 1 ENAME_ FROM BASE_USER BU WHERE BU.USERNAME_=T.MIC) MIC,  T.AIC,T.STAFF,T.ITEMSTATUS,T.AUDITSTATUS,  CASE T.ISGROUP WHEN 'Y' THEN		(SELECT ISNULL(ROUND(SUM(CASE T1.STATUS WHEN 'CLOSE' THEN 1 ELSE 0 END)/CONVERT(FLOAT,COUNT(*)),2),0) * 100			FROM TR_CHECKLISTITEM T1 WHERE T1.PROJECTID=T.ID )  ELSE (SELECT ISNULL(ROUND(SUM(CASE CLM.MOUDLESTATUS WHEN 'CLOSE' THEN 1 ELSE 0 END)/CONVERT(FLOAT,COUNT(*)),2),0) * 100			FROM TR_CHECKLISTITEM T1,TR_CHECKLISTMOUDLE CLM				 WHERE T1.PROJECTID=T.ID AND T1.ID=CLM.CHECKLISTID AND CLM.MOUDLESTATUS IN ('FINISH','CLOSE','OPEN')) END CLPERCENT,  (SELECT ISNULL(ROUND(SUM(CASE Q.STATE WHEN '0' THEN 1 ELSE 0 END)/CONVERT(FLOAT,COUNT(*)),2),0) * 100			FROM TR_QUESTION Q,TR_CHECKLISTITEM T1	    WHERE T1.PROJECTID=T.ID AND Q.CHECKLISTID=T1.ID) QLPERCENT,  (SELECT ISNULL(ROUND(SUM(CASE I.STATUS WHEN '0' THEN 1 ELSE 0 END)/CONVERT(FLOAT,COUNT(*)),2),0) * 100			FROM TR_ISSUESUMMARY I,TR_CHECKLISTITEM T1	    WHERE T1.PROJECTID=T.ID AND I.CHECKLISTID=T1.ID   AND isnull(I.emergency,'') <> '1'   ) ISPERCENT,  (SELECT ISNULL(ROUND(SUM(CASE TM.STATUS WHEN '0' THEN 1 ELSE 0 END)/CONVERT(FLOAT,COUNT(*)),2),0) * 100 			FROM TR_TAXMEMO TM WHERE TM.PROJECTITEMID=T.ID) TMPERCENT,  ((SELECT TOP 1 TOTALAGREEDFEE FROM TR_BILLING B WHERE B.PROJECTITEMID=T.ID) +  (SELECT SUM(ISNULL(DISBURSEMENTAMT,0)) FROM TR_BILLING B WHERE B.PROJECTITEMID=T.ID)) RETURNED,  (SELECT SUM(APPLYFEE + ISNULL(DISBURSEMENTAMT,0)) FROM TR_BILLING B WHERE B.EXAMSTATUS IN (0,1,2,3,4,5,6,7,8,9,11,12,13,14) AND B.PROJECTITEMID=T.ID) APPLY,  (SELECT SUM(APPLYFEE + ISNULL(DISBURSEMENTAMT,0)) FROM TR_BILLING B WHERE B.EXAMSTATUS IN (0,8,9,12,13,14) AND B.PROJECTITEMID=T.ID) APPROVE,  (SELECT SUM(APPLYFEE + ISNULL(DISBURSEMENTAMT,0)) FROM TR_BILLING B WHERE B.EXAMSTATUS=0 AND B.PROJECTITEMID=T.ID) AUDITAPPROVE,T.year as t_Year,  (SELECT TOP 1 ISGROUP FROM TR_AUDITINFORMATION AI WHERE AI.PROJECTITEMID=T.ID) GROUPTYPE, T.engProjectDesc FROM TR_PROJECTITEM T WHERE ISDELETE=0  AND ISRECORD=0) INNERTABLE) TMP WHERE 1=1  order by TMP.t_Year desc;
 --日历
 select workplancu0_.*
  from TR_WorkPlanCustom workplancu0_ where 1=1 and workplancu0_.userId='nyan'
--我的项目
select distinct top 100 t.clientName, t.isGroup, t.audittaskcode, t.auditCobber, t.auditManager, t.ctaCode, t.PIC, t.MIC, t.AIC, t.Staff, t.estimatedBegDate, t.estimatedEndDate, t.id, t.auditStatus, t.hasManuscript,  t.clientCode, t.code,  t.ppic,  t.engProjectDesc  from  tr_projectitem  t  where
  t.isRecord = 0 and t.isDelete = 0
 and  (t.AIC='nyan' or t.MIC='nyan' or t.PIC='nyan' or t.STAFF='nyan' or t.ppic='nyan'
  or t.auditAIC = 'nyan'or t.auditManager = 'nyan'
  or t.auditCobber = 'nyan'  or t.id in (select i.projectItemId from tr_itemEntity i 
  where i.aic='nyan' or i.staff='nyan')) 
   order by t.clientCode

   select checklisti0_.* from TR_ChecklistItem checklisti0_ where checklisti0_.projectId=1;
   select checkopera0_.*
    from TR_CheckOperateReport checkopera0_ where
	 checkopera0_.checklistId=1 and (checkopera0_.operate in (1 , 2));

	 SELECT (SELECT SUM(CASE T1.STATUS WHEN 'CLOSE' THEN 1 ELSE 0 END) 
	 FROM TR_CHECKLISTITEM T1 WHERE T1.PROJECTID=T.ID) CLCLOSE, 
	 (SELECT COUNT(*) FROM TR_CHECKLISTITEM T1 WHERE T1.PROJECTID=T.ID ) CLCOUNT,
	  (SELECT SUM(CASE Q.STATE WHEN '0' THEN 1 ELSE 0 END) 
	  FROM TR_QUESTION Q,TR_CHECKLISTITEM T1			
	  WHERE T1.PROJECTID=T.ID AND Q.CHECKLISTID=T1.ID) QLCLOSE, 
	  (SELECT COUNT(*) FROM TR_QUESTION Q,TR_CHECKLISTITEM T1 
	  WHERE T1.PROJECTID=T.ID AND Q.CHECKLISTID=T1.ID) QLCOUNT, 
	  (SELECT SUM(CASE I.STATUS WHEN '0' THEN 1 ELSE 0 END) FROM 
	  TR_ISSUESUMMARY I,TR_CHECKLISTITEM T1			
	  WHERE T1.PROJECTID=T.ID AND I.CHECKLISTID=T1.ID) ISCLOSE, 
	  (SELECT COUNT(*) FROM TR_ISSUESUMMARY I,TR_CHECKLISTITEM T1 
	  WHERE T1.PROJECTID=T.ID AND I.CHECKLISTID=T1.ID) ISCOUNT, 
	  (SELECT SUM(CASE TM.STATUS WHEN '0' THEN 1 ELSE 0 END) FROM TR_TAXMEMO
	   TM WHERE TM.PROJECTITEMID=T.ID) TMCLOSE, (SELECT COUNT(*) FROM TR_TAXMEMO TM 
	   WHERE TM.PROJECTITEMID=T.ID) TMCOUNT, (SELECT COUNT(*) FROM TR_ITEMENTITY IE 
	   WHERE IE.PROJECTITEMID=T.ID) IECOUNT, (SELECT COUNT(*) FROM TR_ITEMENTITY IE,
	   TR_CHECKOPERATEREPORT COR,TR_CHECKLISTENTITY CE			
	   WHERE IE.PROJECTITEMID=T.ID AND IE.ID=CE.ENTITYID AND COR.CHECKLISTID=CE.CHECKLISTID 
	   AND OPERATE='FINISH') IECLOSE,	T.ID,T.MIC,T.PIC FROM TR_PROJECTITEM T 
	   WHERE T.ISDELETE=0 and t.isRecord=0  AND T.ID=1;

--select  case ?  when staff then 'STAFF'  when aic then 'AIC'  when mic then 'MIC' when pic then 'PIC' when ppic then 'PPIC' when auditManager then 'audit mic' when auditCobber then 'audit pic' when auditAIC then 'audit aic' end from TR_ProjectItem where id =?;

--底稿上传
select manuscript0_.*
 from TR_ManuscriptItem manuscript0_ where 1=1 and 
 (manuscript0_.group_Id='1' and manuscript0_.entity_Id=1 or manuscript0_.group_Id='1' and
 (manuscript0_.entity_Id is null)) order by manuscript0_.upload_Date;
--check list
select checklistm0_.*
from TR_ChecklistMoudle checklistm0_ cross join TR_Knowledge knowledge1_ 
cross join TR_Knowledge knowledge2_ where checklistm0_.moudleId=knowledge1_.id 
and knowledge1_.parentId=knowledge2_.id and (checklistm0_.moudleId is not null)
 and checklistm0_.checklistId=1 order by knowledge2_.name;

 select knowledge0_.*
  from TR_Knowledge knowledge0_ left outer join 
  TR_Knowledge knowledge1_ on knowledge0_.parentId=knowledge1_.id where knowledge0_.id=1

select templateit0_.*
from TR_TemplateItem templateit0_ where templateit0_.isRemoved=0 and templateit0_.moudleId=1

select this_.*
 from TR_WorkLoadLog this_ where this_.userId='nyan' and this_.projectId=1 
 and this_.status='11' and  CONVERT(VARCHAR,workDate,112)='20171206';

  select checklista0_.* from TR_ChecklistAnswer checklista0_ 
  where checklista0_.checklistMoudleId=1 and checklista0_.status=1
   order by checklista0_.id
 
--模块变更历史 
select moudleoper0_.* from TR_MoudleOperateReport moudleoper0_ 
where moudleoper0_.checklistId=1;
--模块问题回答
select step0_.*
 from TR_Step step0_ left outer join TR_Knowledge knowledge1_ 
 on step0_.knowledgeId=knowledge1_.id left outer join TR_Knowledge knowledge2_ 
 on knowledge1_.parentId=knowledge2_.id left outer join TR_Step step3_ 
 on step0_.parentId=step3_.id where step0_.id=1;

 select step0_.*
  from TR_Step step0_ where 1=1 and step0_.parentId=1 and step0_.isDelete=0
   order by step0_.sortOrder asc
--
select answer0_.*
 from TR_Answer answer0_ where 1=1 and (answer0_.parentId is null) 
 and answer0_.stepId=1 and answer0_.isDelete=0 order by answer0_.sortOrder asc

 ---quetion list
 select projectite0_.*
  from TR_ProjectItemApp projectite0_ where 1=1 
  and projectite0_.projectItemId=1 and projectite0_.validity=1 
  and projectite0_.type='main' and projectite0_.status<>'32';

  select billings0_.*
   from tr_Billing billings0_ where billings0_.projectItemId=1

  select top 100 question0_.*
   from TR_Question question0_ where 1=1 and (question0_.parentQuestionId is null)
    and question0_.type=1 and question0_.checklistId=1 order by question0_.id asc
--issue summay
select auditinfor0_.* 
from TR_AuditInformation auditinfor0_ cross join TR_ProjectItem projectite1_ 
where projectite1_.istemplate=0 and auditinfor0_.projectItemId=projectite1_.id and 
projectite1_.auditStatus='0003' and 1=1 and auditinfor0_.projectItemId=1

select top 10000 issuesumma0_.*
 from TR_ISSUESUMMARY issuesumma0_ where 1=1 and issuesumma0_.checkListId=1;

 select replyrecor0_.*
  from TR_ReplyRecord replyrecor0_ where 1=1 and replyrecor0_.businessId=1 
  and replyrecor0_.target='' and replyrecor0_.valid=1 order by replyrecor0_.replyTime asc;

select comments0_.*
 from TR_Comments comments0_ where 1=1 and comments0_.id=1;

--独立性
select declaratio0_.*
 from TR_Declaration declaratio0_ where 1=1
  and declaratio0_.projectItemId=1 order by declaratio0_.declardate asc
--审计信息填写

select * from base_country;--194
select * from BASE_SHAREHOLDER;--365
select * from BASE_CUSTOMER;--12658
select * from TR_AuditInformation;--28099
select * from TR_informationEditing;--708
select shareholde0_.*
 from BASE_SHAREHOLDER shareholde0_ cross join base_country country1_ 
 cross join BASE_CUSTOMER customer2_ where shareholde0_.customerId=customer2_.id 
 and customer2_.CUSTOMERCODE='aaa' and shareholde0_.countryId=country1_.id;


 --审计信息下拉菜单
select crossborde0_.* from TR_CrossBorderPay crossborde0_ where 1=1 order by crossborde0_.id
select bondedfaci0_.* from BASE_BONDEDFACILITY bondedfaci0_ where 1=1
select aptitude0_.* from BASE_APTITUDE aptitude0_ where 1=1
select revenuepri0_.* from BASE_REVENUEPRIVILEGE revenuepri0_ 
where 1=1 and (revenuepri0_.revCode not in  ('00013' , '00014'))
--客户信息
select distinct projectite0_.* from TR_ProjectItem projectite0_ where 
projectite0_.istemplate=0 and projectite0_.groupCode='323'
--底稿
select  manuscript0_.*
 from TR_ManuscriptItem manuscript0_ where 1=1 and 
 manuscript0_.status=1 and manuscript0_.file_Type='excel';
 select workbook0_.* from DOC_WorkBook workbook0_ where 1=1 and workbook0_.relationId=? and workbook0_.flag=?;
select sheets0_.* from DOC_Sheet sheets0_ where sheets0_.workBookId=?;
select manuscript0_.* from TR_ManuscriptItem manuscript0_ where manuscript0_.file_Id in (88958) order by manuscript0_.upload_Date desc;
select sheet0_.* from DOC_Sheet sheet0_ left outer join DOC_WorkBook workbook1_ on sheet0_.workBookId=workbook1_.id where sheet0_.id=?;

-- update TR_ManuscriptItem set status=3 where file_Path like 'd:\%' and status=1




--doc
select  manuscript0_.*
 from TR_ManuscriptItem manuscript0_ where 1=1 and 
 manuscript0_.status=1 and manuscript0_.file_Type='excel';
select * from DOC_WorkBook;--56263
select * from DOC_Sheet;--465436
select * from DOC_row;--0
select * from DOC_SelectInfo;--220
select * from DOC_QUESTION_MANUSCRIPT_SELECTINFO;--12213
select * from DOC_NotKeySelectInfo;--26
select * from DOC_NotKeySearchContent;--0
select * from DOC_KeyWord;--15
select * from DOC_KeySum;;--173


select  manuscript0_.*
 from TR_ManuscriptItem manuscript0_ where 1=1 and 
 manuscript0_.status=1 and manuscript0_.file_Type='excel';

 select workbook0_.* from DOC_WorkBook workbook0_ where 1=1 and workbook0_.relationId=1 and workbook0_.flag=1;

select sheets0_.* from DOC_Sheet sheets0_ where sheets0_.workBookId=1;

select manuscript0_.* from TR_ManuscriptItem manuscript0_ where manuscript0_.file_Id in (88958) order by manuscript0_.upload_Date desc;

select sheet0_.* from DOC_Sheet sheet0_ left outer join DOC_WorkBook workbook1_ on sheet0_.workBookId=workbook1_.id where sheet0_.id=1;

select keysum0_.* from DOC_KeySum keysum0_ where 1=1 and keysum0_.businessId=1 and keysum0_.businessGroup='aa' and keysum0_.keyid<>0

select selectinfo0_.* from DOC_SelectInfo selectinfo0_ where 1=1 and selectinfo0_.keyid=1 and selectinfo0_.businessId=1 and selectinfo0_.sheetId=1;

select keysum0_.* from DOC_KeySum keysum0_ where 1=1 and keysum0_.businessId=1 and keysum0_.businessGroup='dd' and keysum0_.keyid<>0;

select sum(selectinfo0_.selectNumber) as col_0_0_ from DOC_SelectInfo selectinfo0_ where selectinfo0_.businessId=1 and selectinfo0_.keyid=1;

--历史项目查询
SELECT top 100 * FROM (SELECT T.ID,T.CLIENTNAME,T.CLIENTCODE,T.BEGDATE,T.ENDDATE,T.ISGROUP,AUDITTASKCODE,AUDITCOBBER,AUDITMANAGER,CTACODE,ITEMSTATUS, ((SELECT TOP 1 TOTALAGREEDFEE FROM TR_BILLING B WHERE B.PROJECTITEMID=T.ID) +  (SELECT SUM(ISNULL(DISBURSEMENTAMT,0)) FROM TR_BILLING B WHERE B.PROJECTITEMID=T.ID)) RETURNED,  (SELECT SUM(APPLYFEE + ISNULL(DISBURSEMENTAMT,0)) FROM TR_BILLING B WHERE B.EXAMSTATUS IN (0,1,2) AND B.PROJECTITEMID=T.ID) APPLY,  (SELECT SUM(APPLYFEE + ISNULL(DISBURSEMENTAMT,0)) FROM TR_BILLING B WHERE B.EXAMSTATUS=0 AND B.PROJECTITEMID=T.ID) APPROVE,	t.staff,t.aic,t.mic,t.pic,t.ppic
 FROM TR_PROJECTITEM T WHERE 1=1 AND ( ( T.PIC='nyan' OR T.MIC='nyan' OR T.AIC='nyan' OR T.STAFF='nyan' ) 
 OR T.ID IN (SELECT DISTINCT PI.ID FROM TR_PROJECTITEM PI left join TR_ITEMENTITY IE on PI.ID=IE.PROJECTITEMID  
 WHERE (IE.PIC='nyan' OR IE.MIC='nyan' OR IE.AIC='nyan' OR IE.STAFF='nyan'  ) ) OR T.clientcode in
  (SELECT DISTINCT PI.clientcode FROM TR_PROJECTITEM PI  WHERE (PI.PIC='nyan' OR PI.MIC='nyan' OR PI.AIC='nyan' OR PI.STAFF='nyan') 
  AND PI.isDelete =0 AND PI.isRecord=0) ) AND (YEAR(T.BEGDATE)=2015 OR YEAR(T.ENDDATE)=2018) AND T.ISRECORD=1 AND T.ISDELETE=0) 
  TMP order by  tmp.BEGDATE desc 
--项目计时
select w.projectId,w.userId,w.workDate,sum(w.workHours) workHours,w.workType,w.calType,sum(w.modifyWorkHours) modifyWorkHours 
 from TR_WorkPlanPerDay w where 1=1   and w.workType = 'R'   and w.projectId =1  and w.userId ='nyan'
   GROUP BY w.projectId,w.userId,w.workDate,w.workType,w.calType ;

   select * from TR_WorkPlanPerDay;

索引

ALTER TABLE BASE_USER ADD  CONSTRAINT PK_BASE_USER PRIMARY KEY (USERNAME_);
ALTER TABLE BASE_URL ADD  CONSTRAINT PK_BASE_URL PRIMARY KEY (ID_);
--ALTER TABLE FM_FILE ADD  CONSTRAINT PK_FM_FILE PRIMARY KEY (ID);
ALTER TABLE BASE_ROLE ADD  CONSTRAINT PK_BASE_ROLE PRIMARY KEY (ID_);
ALTER TABLE BASE_URL_COMPONENT ADD  CONSTRAINT PK_BASE_URL_COMPONENT PRIMARY KEY (ID_);

create index IDX_USER_USERNAME on BASE_USER (USERNAME_ ASC,ENABLED_ ASC);
create index IDX_ROLEMEMBER_ROLEID on BASE_ROLE_MEMBER (ROLE_ID_ ASC,USERNAME_ ASC);
create index IDX_ROLERESOURCE_URLID_ROLEID on BASE_ROLE_RESOURCE (ROLE_ID_ ASC,URL_ID_ ASC);


--TR_PROJECTITEM
ALTER TABLE TR_PROJECTITEM ADD  CONSTRAINT PK_TR_PROJECTITEM PRIMARY KEY (ID);

--create index IDX_PROJECT_USERS on TR_ProjectItem (aic ASC,mic ASC,pic ASC,staff ASC,ppic ASC);
CREATE NONCLUSTERED INDEX IDX_PROJECT_EFFECTFUL ON [dbo].[TR_ProjectItem] ([istemplate],[isDelete],[fisY]);
CREATE NONCLUSTERED INDEX IDX_PROJECT_USERS_NONCLUSTERED ON TR_ProjectItem (istemplate,isRecord,isDelete) INCLUDE (aic,mic,pic,staff,ppic);
create index IDX_PROJECT_PROJECTINFO on TR_ProjectItem (clientCode ASC,clientName ASC,fisY ASC,istemplate,isRecord,isDelete,groupCode);
--create index IDX_PROJECT_USERS_NONCLUSTERED on dbo.TR_ProjectItem (isRecord ASC,isDelete ASC,istemplate ASC,aic ASC,mic ASC,pic ASC,staff ASC,ppic ASC);
--TR_ITEMENTITY
ALTER TABLE TR_ITEMENTITY ADD CONSTRAINT PK_TR_ITEMENTITY PRIMARY KEY (ID);
--ALTER TABLE TR_ITEMENTITY ADD CONSTRAINT FK_PROJECTITEM_ITEMENTITY FOREIGN KEY (projectItemId) REFERENCES TR_PROJECTITEM (id); 
create index IDX_ItemEntity_PROJECTID on TR_ItemEntity (projectItemId ASC);
create index IDX_ITEMENTITY_USERS on TR_ItemEntity (aic ASC,staff ASC,projectItemId ASC);
--TR_CHECKOPERATEREPORT
ALTER TABLE TR_CHECKOPERATEREPORT ADD  CONSTRAINT PK_TR_CHECKOPERATEREPORT PRIMARY KEY (ID);
create index IDX_CHECKREPORT_PROJECTID on TR_CheckOperateReport (projectId ASC);
create index IDX_CHECKREPORT_CHECKLISTID on TR_CheckOperateReport (checklistId ASC);
--TR_CHECKLISTENTITY
create index IDX_REL_ChecklistEntity on TR_ChecklistEntity (entityId ASC,checklistId ASC);
--TR_ChecklistItem
ALTER TABLE TR_ChecklistItem ADD  CONSTRAINT PK_TR_ChecklistItem PRIMARY KEY (ID);
create index IDX_ChecklistItem_PROJECTID on TR_ChecklistItem (projectId ASC);
--TR_QUESTION
ALTER TABLE TR_QUESTION ADD  CONSTRAINT PK_TR_QUESTION PRIMARY KEY (ID);
create index IDX_Question_CHECKLIST on TR_Question (checklistId ASC);
create index IDX_Question_PARENT on TR_Question (parentQuestionId ASC);
--TR_ISSUESUMMARY
ALTER TABLE TR_ISSUESUMMARY ADD  CONSTRAINT PK_TR_ISSUESUMMARY PRIMARY KEY (ID);
create index IDX_ISSUESUMMARY_CHECKLIST on TR_ISSUESUMMARY (checkListId ASC,entityId ASC);
--TR_TAXMEMO
ALTER TABLE TR_TAXMEMO ADD  CONSTRAINT PK_TR_TAXMEMO PRIMARY KEY (ID);
create index IDX_TAXMEMO_PROJECT_CHECKLIST on TR_TAXMEMO (checklistId ASC,projectItemId ASC);

--TR_ManuscriptItem
ALTER TABLE TR_ManuscriptItem ADD  CONSTRAINT PK_TR_ManuscriptItem PRIMARY KEY (ID);
create index IDX_ManuscriptItem_MAIN on TR_ManuscriptItem (entity_Id ASC,group_Id ASC,file_Id);
--create index IDX_ManuscriptItem_TYPE on TR_ManuscriptItem (file_Type ASC,status ASC);
--TR_Message
ALTER TABLE TR_Message ADD  CONSTRAINT PK_TR_Message PRIMARY KEY (ID);
--TR_SendInfo
ALTER TABLE TR_SendInfo ADD  CONSTRAINT PK_TR_SendInfo PRIMARY KEY (ID);
create index IDX_SendInfo_MESSAGEID on TR_SendInfo (message_id ASC);
CREATE NONCLUSTERED INDEX IDX_SendInfo_MESSAGE ON TR_SendInfo (is_Read,is_Close,recipient) INCLUDE (message_id);
--create index IDX_SendInfo_MESSAGE on TR_SendInfo (is_Read ASC,is_Send ASC,message_id ASC,recipient ASC,is_Close ASC);

--TR_ProjectItemApp
ALTER TABLE TR_ProjectItemApp ADD  CONSTRAINT PK_TR_ProjectItemApp PRIMARY KEY (ID);
create index IDX_ProjectItemApp_PROJECT on TR_ProjectItemApp (projectItemId ASC);

CREATE NONCLUSTERED INDEX IDX_ProjectItemApp_APP
ON [TR_ProjectItemApp] ([initiateUserId],[type])
INCLUDE ([id],[applicantDate],[approver],[newUserId],[projectItemId],[roleId],[status],[validity])

--TR_EntityGroupId
create index IDX_EntityGroupId_MAIN on TR_EntityGroupId (entityId ASC,projectId ASC);

--TR_AuditInformation
ALTER TABLE TR_AuditInformation ADD  CONSTRAINT PK_TR_AuditInformation PRIMARY KEY (ID);
create index IDX_AuditInformation_PROJECT on TR_AuditInformation (projectItemId ASC);

--IDX_WorkPlanCustom_USER
ALTER TABLE TR_WorkPlanCustom ADD  CONSTRAINT PK_TR_WorkPlanCustom PRIMARY KEY (ID);
create index IDX_WorkPlanCustom_USER on TR_WorkPlanCustom (userId ASC)

--TR_ChecklistMoudle
ALTER TABLE TR_ChecklistMoudle ADD  CONSTRAINT PK_TR_ChecklistMoudle PRIMARY KEY (ID);
create index IDX_ChecklistMoudle_MAIN on TR_ChecklistMoudle (checklistId ASC,moudleId ASC);
--TR_Knowledge
ALTER TABLE TR_Knowledge ADD  CONSTRAINT PK_TR_Knowledge PRIMARY KEY (ID);
create index IDX_Knowledge_PARENT on TR_Knowledge (parentId ASC);
DROP INDEX IDX_Knowledge ON TR_Knowledge;
--TR_Step
ALTER TABLE TR_Step ADD  CONSTRAINT PK_TR_Step PRIMARY KEY (ID);
create index IDX_Step_PARENT on TR_Step (parentId ASC);
DROP INDEX IDX_Step ON TR_Step;
create index IDX_Step_KNOWLEDGE on TR_Step (knowledgeId ASC);
--TR_Answer
ALTER TABLE TR_Answer ADD  CONSTRAINT PK_TR_Answer PRIMARY KEY (ID);
create index IDX_Answer_PARENT on TR_Answer (parentId ASC);
DROP INDEX IDX_Answer ON TR_Answer;
create index IDX_Answer_STEP on TR_Answer (stepId ASC);
--TR_WorkLoadLog
ALTER TABLE TR_WorkLoadLog ADD  CONSTRAINT PK_TR_WorkLoadLog PRIMARY KEY (ID);
create index IDX_WorkLoadLog_MAIN on TR_WorkLoadLog (projectId ASC,status ASC,userId ASC);
--TR_MoudleOperateReport
ALTER TABLE TR_MoudleOperateReport ADD  CONSTRAINT PK_TR_MoudleOperateReport PRIMARY KEY (ID);
create index IDX_MoudleOperateReport_main on TR_MoudleOperateReport (checklistId ASC,checklistMoudleId);

--tr_Billing
ALTER TABLE tr_Billing ADD  CONSTRAINT PK_tr_Billing PRIMARY KEY (ID);
create index IDX_Billing_PROJECT on tr_Billing (projectItemId);

--TR_Comments
ALTER TABLE TR_Comments ADD  CONSTRAINT PK_TR_Comments PRIMARY KEY (ID);
create index IDX_Comments_businessId on TR_Comments (businessId);

--TR_informationEditing
ALTER TABLE TR_informationEditing ADD  CONSTRAINT PK_TR_informationEditing PRIMARY KEY (ID);
create index IDX_informationEditing_PROJECT on TR_informationEditing (PROJECTID,entityId);

ALTER TABLE base_country ADD  CONSTRAINT PK_base_country PRIMARY KEY (ID);
ALTER TABLE BASE_SHAREHOLDER ADD  CONSTRAINT PK_BASE_SHAREHOLDER PRIMARY KEY (ID);
create index IDX_SHAREHOLDER_MAIN on BASE_SHAREHOLDER (countryId,customerId);

ALTER TABLE BASE_CUSTOMER ADD  CONSTRAINT PK_BASE_CUSTOMER PRIMARY KEY (ID);

ALTER TABLE BASE_CUSTOMERDETAIL ADD  CONSTRAINT PK_BASE_CUSTOMERDETAIL PRIMARY KEY (ID);
create index IDX_CUSTOMERDETAIL_MAIN on BASE_CUSTOMERDETAIL (customerId);

--TR_Resumption
--select * from TR_Resumption;
ALTER TABLE TR_Resumption ADD  CONSTRAINT PK_TR_Resumption PRIMARY KEY (ID);
create index IDX_Resumption_MAIN on TR_Resumption (CHECKLISTID);

--TR_Declaration
ALTER TABLE TR_Declaration ADD  CONSTRAINT PK_TR_Declaration PRIMARY KEY (ID);
create index IDX_Declaration_PROJECT on TR_Declaration (projectItemId);

--TR_overYearsProfitOrLoss
--select * from TR_overYearsProfitOrLoss;
ALTER TABLE TR_overYearsProfitOrLoss ADD  CONSTRAINT PK_TR_overYearsProfitOrLoss PRIMARY KEY (ID);
create index IDX_overYearsProfitOrLoss_main on TR_overYearsProfitOrLoss (projectId,entityid);

ALTER TABLE DOC_WorkBook ADD  CONSTRAINT PK_DOC_WorkBook PRIMARY KEY (ID);
create index IDX_WorkBook_main on DOC_WorkBook (RELATIONID);

ALTER TABLE DOC_Sheet ADD  CONSTRAINT PK_DOC_Sheet PRIMARY KEY (ID);
create index IDX_Sheet_main on DOC_Sheet (workBookId);

ALTER TABLE DOC_SelectInfo ADD  CONSTRAINT PK_DOC_SelectInfo PRIMARY KEY (ID);
create index IDX_SelectInfo_main on DOC_SelectInfo (keyid,businessId,sheetId);

--ALTER TABLE DOC_QUESTION_MANUSCRIPT_SELECTINFO ADD  CONSTRAINT PK_DOC_QUESTION_MANUSCRIPT_SELECTINFO PRIMARY KEY (ID);
ALTER TABLE DOC_NotKeySelectInfo ADD  CONSTRAINT PK_DOC_NotKeySelectInfo PRIMARY KEY (ID);
create index IDX_NotKeySelectInfo_main on DOC_NotKeySelectInfo (businessId);

ALTER TABLE DOC_NotKeySearchContent ADD  CONSTRAINT PK_DOC_NotKeySearchContent PRIMARY KEY (ID);
create index IDX_NotKeySearchContent_main on DOC_NotKeySearchContent (businessId);

ALTER TABLE DOC_KeyWord ADD  CONSTRAINT PK_DOC_KeyWord PRIMARY KEY (ID);

ALTER TABLE DOC_KeySum ADD  CONSTRAINT PK_DOC_KeySum PRIMARY KEY (ID);
create index IDX_KeySum_main on DOC_KeySum (businessId,businessGroup,keyid);

ALTER TABLE DOC_QUESTION_MANUSCRIPT_SELECTINFO ADD  CONSTRAINT PK_DOC_QUESTION_MANUSCRIPT_SELECTINFO PRIMARY KEY (ID);
create index IDX_QUESTION_MANUSCRIPT_SELECTINFO_main on DOC_QUESTION_MANUSCRIPT_SELECTINFO (questionId);

--TR_WorkPlanPerDay
ALTER TABLE TR_WorkPlanPerDay ADD  CONSTRAINT PK_TR_WorkPlanPerDay PRIMARY KEY (ID);
create index IDX_WorkPlanPerDay_main on TR_WorkPlanPerDay (userid,projectid);

--[QRR_Project]
--CREATE NONCLUSTERED INDEX IDX_QRR_Project_USERS ON QRR_Project (proStatus,SPIC) INCLUDE (AIC,MIC,MIC2,PIC,PPIC,STAFF,NTC,SPIC_STATUS);
CREATE INDEX IDX_QRR_Project_USERS ON QRR_Project (proStatus,SPIC,AIC,MIC,MIC2,PIC,PPIC,STAFF,NTC,SPIC_STATUS);
--QRR_QrrItemEntity
create index IDX_QrrItemEntity_USERS on QRR_QrrItemEntity (AIC ASC,STAFF ASC,projectItemId ASC);