1.1增加是否开发选项,
string msg;
msg=CommonFunction.CheckUserType(Session,DropDownList1,DropDownList2);//返回值:地级市，组区域，全部，error
MessageBox.Show(this,msg);
已完成根据查询方式来控制组区域或地级市
1.2,修改查询医院选择中查询医院的相关语句，要根据用户的不同组合查询语句，使用动态执行sql的方式
主体
select a.hisid,a.hisname,a.pym,a.area,a.hislevel,isnull(a.mzl,0) as mzl,isnull(a.bcs,0) as bcs,a.demo,a.isover,a.areaname,b.groupid
from zy_yp1_his a,zy_yp1_area b
where a.area=b.areaidhis
动态部分
当是组区域和全部时，取组区域，全部中如果是全部，就取全部，条件是b.groupid
当是地级市时，条件为a.area
2.1增加历史医院查询，以便对当前医院做覆盖品种的维护,当招商经理，公司员工，招商专员三种角色进入时，根据查询方式的不同，提取的医院信息也不同
2.1.1当以组方式查询进入时，显示组选择，地级市灰化
当公司员工，招商经理，招商专员进入crm查询所生成的各种情况的语句便如下
--当是组区域和全部时，取组区域，全部中如果是全部，就取全部，条件是b.groupid
--当是地级市时，条件为a.area
--参数
declare @QueryType char(10)
declare @querycode char(10)
declare @tempQuery char(1000)
declare @selectQuery char(1000)
declare @whereQuery char(400)
declare @order char(800)
set @order=' order by a.area'
set @QueryType='全部'
set @querycode='10100'
set @whereQuery=''
set @selectQuery='select a.hisid,a.hisname,a.pym,a.area,a.hislevel,isnull(a.mzl,0) as mzl,isnull(a.bcs,0) as bcs,a.demo,a.isover,a.areaname,b.groupid
from zy_yp1_his a,zy_yp1_area b
where a.area=b.areaidhis'--查询主体
--组合where 条件
if(rtrim(@QueryType)='组区域')--组区域可以增加全部选项
select @whereQuery=' and b.groupid='''+ rtrim(@querycode)+ ''''--按组区域查询
if(rtrim(@QueryType)='地级市')
select @whereQuery=' and a.area='''+ rtrim(@querycode)+ ''''--按地级市查询
if(rtrim(@QueryType)='全部')
select @whereQuery=' '--按地级市查询
set @tempQuery=rtrim(@selectQuery)+rtrim(@whereQuery)+rtrim(@order)
print @tempQuery
exec (@tempQuery)
--61.155.8.170
exec CRM_GetHospitalListByQueryType '全部','1001',''
exec CRM_GetHospitalListByQueryType '组区域','1001',''
exec CRM_GetHospitalListByQueryType '地级市','1001',''
--员工
exec CRM_GetHospitalListByQueryType @QueryType = '全部', @querycode = '', @HisName = '南京'
select a.hisid,a.hisname,a.pym,a.area,a.hislevel,isnull(a.mzl,0) as mzl,isnull(a.bcs,0) as bcs,a.demo,a.isover,a.areaname,b.groupid
from zy_yp1_his a,zy_yp1_area b
where a.area=b.areaidhis and a.hisname like'%南京%' order by a.area
exec CRM_GetHospitalListByQueryType @QueryType = '全部', @querycode = '', @HisName = ''
select a.hisid,a.hisname,a.pym,a.area,a.hislevel,isnull(a.mzl,0) as mzl,isnull(a.bcs,0) as bcs,a.demo,a.isover,a.areaname,b.groupid
from zy_yp1_his a,zy_yp1_area b
where a.area=b.areaidhis order by a.area
--招商经理
exec CRM_GetHospitalListByQueryType @QueryType = '组区域', @querycode = '1013', @HisName = '中医'
select a.hisid,a.hisname,a.pym,a.area,a.hislevel,isnull(a.mzl,0) as mzl,isnull(a.bcs,0) as bcs,a.demo,a.isover,a.areaname,b.groupid
from zy_yp1_his a,zy_yp1_area b
where a.area=b.areaidhis and b.groupid='1013' and a.hisname like'%中医%' order by a.area
exec CRM_GetHospitalListByQueryType @QueryType = '组区域', @querycode = '1013', @HisName = ''
select a.hisid,a.hisname,a.pym,a.area,a.hislevel,isnull(a.mzl,0) as mzl,isnull(a.bcs,0) as bcs,a.demo,a.isover,a.areaname,b.groupid
from zy_yp1_his a,zy_yp1_area b
where a.area=b.areaidhis and b.groupid='1013' order by a.area
--专员
exec CRM_GetHospitalListByQueryType @QueryType = '地级市', @querycode = '320107', @HisName = ''
select a.hisid,a.hisname,a.pym,a.area,a.hislevel,isnull(a.mzl,0) as mzl,isnull(a.bcs,0) as bcs,a.demo,a.isover,a.areaname,b.groupid
from zy_yp1_his a,zy_yp1_area b
where a.area=b.areaidhis and a.area='320107' order by a.area
exec CRM_GetHospitalListByQueryType @QueryType = '地级市', @querycode = '320100', @HisName = '南钢'
select a.hisid,a.hisname,a.pym,a.area,a.hislevel,isnull(a.mzl,0) as mzl,isnull(a.bcs,0) as bcs,a.demo,a.isover,a.areaname,b.groupid
from zy_yp1_his a,zy_yp1_area b
where a.area=b.areaidhis and a.area='320100' and a.hisname like'%南钢%' order by a.area
--为招商经理增加领导，这样一些功能权限上可使用上级的权限
ALTER TABLE dbo.zy_yp1_manager ADD--招商经理ID
directorID char(10) NULL
ALTER TABLE dbo.zy_yp1_proxy ADD
directorID char(10) NULL
ALTER TABLE dbo.zy_sys2_ygbm ADD
directorID char(10) NULL
在设计crm系统中，代理商不能按组，地级市，全部等查询方式来处理，他只能查本身的信息，所有这是一个特例