医药CRM系统开发

自已做医药CRM系统有四年了,终于可以算个产品了,努力市场化,今年重种将医药营销的理念加入CRM

导航

crm功能修改备忘

Posted on 2007-11-23 12:49  hhq80  阅读(204)  评论(0编辑  收藏  举报
1.医院信息录入
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系统中,代理商不能按组,地级市,全部等查询方式来处理,他只能查本身的信息,所有这是一个特例