--删除旧记录
--delete from Report_Objects where Code='HO.Clinical.Dev.000002' --删除脚本内容
IF not EXISTS(SELECT * FROM Report_Objects WHERE code='HO.Clinical.Dev.000002')
begin --插入
Declare @Report_Id_1056 int
Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1056 out
Declare @Report_Id_1056_CateID int
Set @Report_Id_1056_CateID = (select id from Report_Categories where code='HIS.3')
insert into Report_Objects(ID,Code,Name,Description,Password,CategoryID,xType,Classification,ProductID,ProgramID,ElementID,CreateDate,UpdateDate,ISSUANCEDATE,Config,SetupPath,IsMenu,Enabled,GroupName,Authorized,RefreshInterval) Values(@Report_Id_1056, 'HO.Clinical.Dev.000002', '医生站扩展参数选项', '限制药品重复开药选项等', Null, @Report_Id_1056_CateID, 1, 4, 100, 0, Null, '2014-05-19 14:36:00', '2017-02-06 13:58:15', Null, '
A202F89413EBBA9839C32D986E20C566
--系统发布的都是固定选项。后面可以追加
select s.fid,s.fname,cast(s.fid as varchar)+''--''+s.fname as sfname
from (
select 0 fid,''按药品规格限制用药(X)天数,且距上次用药剩余(Y)天数内才能再开'' fname
union all
select 1 ,''按药品品种限制用药(X)天数,且距上次用药剩余(Y)天数内才能再开''
union all
select 2,''icd疾病用药(X)天数限制''
union all
select 3,''所有药品限制用药(X)天数,且距上次用药剩余(Y)天数内才能再开''
union all
select 4,''抗菌药品限制DDD''
union all
select 5,''医保病人处方，医保和自费药品不允许在同一张处方''
union all
select 6,''医保病人处方，精神类和非精神类药品不允许在同一张处方''
union all
select 7,''医保病人处方，对应相同医保药品编码的西成药品一天内只允许开一次''
union all
select 8,''医保病人处方，所有医保药品限制最大用药(X)天数''
union all
select 9,''医保病人处方，处方诊断限制最大条数''
union all
select 10,''医保挂急诊号病人限制最大开药(X)天数''
union all
select 11,''医保处方诊断为行动不便病人限制最大开药(X)天数''
union all
select 12,''医保病人医保药品3、7、14、30处方天数提前开药限制''
union all
select 13,''医保病人医保处方草药单方不报判断''
union all
select 14,''医保病人限制开大额药品的医保险种判断''
union all
select 15,''医保分配基金限制判断，当月所开医保报销金额大于分配基金，则禁止医师在开医保处方''
union all
select 16,''医保病人处方，对应相同医保药品编码的西成药品不允许在同一张处方''
union all
select 17,''门诊处方，医师所开西成药用药天数跟总量、用量计算出的天数超过设定值禁止保存''
union all
select 18,''医保病人草药处方，可报与不可报草药药品不允许在同一张处方''
union all
select 19,''医保病人处方，西成药品按总量、用量计算出的天数最大不能超过(X)天数''
union all
select 20,''门诊处方，西成药品总量必须大于零''
union all
select 21,''医保病人处方，限定同检查类型的诊疗项目一天内只允许开一次''
union all
select 22,''医保病人处方，当药品总量是1时，对应医师开药天数不在限制，但总量与用量计算出的天数仍然限制''
union all
select 23 ,''医保病人处方，所有药品按医保编码限制用药(X)天数,且距上次用药剩余(Y)天数内才能再开''
union all
select 24 ,''一次开多条相同医技项目医嘱，第二条及以上医嘱单独生成条码''
union all
select 25 ,''对住院手术有关抗生素类用药疗程及时机控制提示''
union all
select 26 ,''对应类别下诊疗项目，门诊医保病人当天重复开时增加提示''
union all
select 27 ,''默认提取最后一次医嘱时，需要剔除不提取的诊疗类别''
union all
select 28 ,''启用药品规格中的限制用量判断，下达医嘱时判断单次用量''
union all
select 29,''门诊处方，当药品总量是1时，对应医师开药天数不在限制，但总量与用量计算出的天数仍然限制''
union all
select 30,''住院手术医嘱，限制必须输入切口等级''
union all
select 31,''住院按病人类别及科室设置药品费用(Y)及总费用(X)限制值''
union all
select 32,''门诊医生站刷会员卡做简易挂号时，病人费别默认提取会员卡对应信息''
union all
select 33,''第三方处方点评浏览调用地址''
union all
select 34,''限定检查类型必须输入病历摘要''
union all
select 35,''限定检查类型必须输入辅助检查''
union all
select 36,''限定检查类型必须输入检查目的''
union all
select 37,''门诊手术医嘱，手术室审核安排后就不能在回退或取消医嘱''
union all
select 38,''住院药品临嘱必须输入频次''
union all
select 39,''调用第三方仪器检查报告(设置对应浏览调用地址和参数)''
union all
select 40,''住院术后超过24小时禁止使用抗生素''
union all
select 41,''住院术前超过0.5--2小时禁止使用抗生素''
union all
select 42,''自动提取病史摘要天数限制，门诊(Y)天及住院(X)天内的病史摘要''
union all
select 43,''下达医嘱限制诊疗类项目禁止输入小数，门诊(Y)及住院(X)大于零表示禁止''
union all
select 44,''门诊下达医嘱窗口，隐藏病人信息框把其显示在标题栏''
union all
select 45,''门诊病人超过一定岁数，必须在首页输入收缩压、舒张压''
union all
select 46,''门诊医师站在检索病人地方显示自定义查询按钮''
union all
select 47,''门诊医保病人保内西成药品处方，不能下达自费药品''
union all
select 48,''门诊编辑医嘱(Y)及回退或医嘱退费(X)大于零表示必须刷会员卡才能继续操作''
union all
select 49,''下达医嘱限制诊疗类项目数量不能输入零，门诊(Y)及住院(X)大于零表示禁止''
union all
select 50,''门诊医保病人保内处方，不能下达执行性质为自费的项目''
union all
select 51,''门诊医保病人，下达处方默认医保处方类型''
union all
select 52,''门诊医保病人保内中草药品处方，不能下达不可报药品''
union all
select 53,''门诊简易挂号，病人类别为医保，一卡通扣费余额不够时也允许挂号''
union all
select 54,''门诊简易挂号，同一个病人当天同一个科室同一个医生只能挂号一次''
union all
select 55,''下达医嘱时，根据医保明细项目中备注进行提示''
union all
select 56,''下达门诊处方诊断或住院出院诊断时，调用慢性病确诊接口，门诊(Y)及住院(X)大于零表示调用''
union all
select 57,''眼科专科疾病信息调用填写''
union all
select 58,''检查报告页面使用中间表浏览图文报告''
union all
select 59,''合理用药审核处方时传入历史有效医嘱设置，门诊(Y)及住院(X)大于零表示传入。''
union all
select 60,''查看病人医疗信息web地址设置(设置对应浏览调用地址和参数)''
union all
select 61,''调用第三方超声系统查看超声影像报告接口程序路径设置''
union all
select 62,''医护站发送医嘱后实时上传医保费用，门诊(Y)及住院(X)大于零表示启用''
union all
select 63,''医生站费用诊间结算(1=医保卡2=银行pos 3=两种都支持)，门诊(Y)及住院(X)大于零表示启用''
union all
select 64,''新版大通合理用药医院编码设置''
union all
select 65,''大通合理用药审核处方时，返回一般或其他信息提示时，医生站提示是否继续保存医嘱。''
union all
select 66,''调用第三方影像报告IE接口时嵌入到第三方影像报告页面浏览''
union all
select 67,''四川中医平台web地址设置''
union all
select 68,''分级转诊平台web地址设置(入参通过报表设置)''
union all
select 69,''医生给病人选择分配治疗套餐计划web地址''
union all
select 70,''北京五洲妇儿医院门诊医生站排队叫号接口(项目列维护ip，y列为端口号)''
union all
select 71,''门诊诊间结算打印收费小票，医保(Y)及银行pos(X)大于零表示启用''
union all
select 72,''医保限制内容提醒，门诊(Y)及住院(X)大于零表示启用''
union all
select 73,''下达医嘱限制修改检验样本类型，门诊(Y)及住院(X)大于零表示禁止''
union all
select 74,''门诊首页修改保存时，不判断限制年龄跟出生日期年份不一致的问题.''
union all
select 75,''下达医嘱医保病人只限制开医保目录内的药品，门诊(Y)及住院(X)大于零表示禁止''
union all
select 76,''门诊接诊病人时弹出复诊选项窗口''
union all
select 77,''门诊处方限制口服和注射不能开在同一张处方''
union all
select 78,''第三方手麻病历调阅web地址''
union all
select 79,''下达医嘱界面给药途径、频次、剂量、天数单独列显示''
union all
select 80,''第三方病理报告调阅web地址''
union all
select 81,''用血管理接口(1:西安金智；2=唐山启奥),对应(X)列值''
union all
select 82,''门诊处方治疗申请单打印启用报表授权判断''
union all
select 83,''住院下达会诊医嘱启用会诊申请单模式''
union all
select 84,''住院下达医嘱前进行术后24、48、72小时用药未停止医嘱提醒''
union all
select 85,''住院会诊申请医嘱及申请单在执行科室对应病区提醒及打印''
union all
select 86,''下达医嘱时显示药品产地，门诊(Y)及住院(X)大于零表示显示''
union all
select 87,''下达医嘱时启用病历日志，门诊(Y)及住院(X)大于零表示启用''
union all
select 88,''住院入科时限制质控员，一级质控员(Y)及二级质控员(X)大于零必须输入''
union all
select 89,''使用HO老版本pacs''
union all
select 90,''启用合理用药及自助机接口日志记录''
union all
select 91,''医保病人，相同药品编码判断，根据药品规格对应备用编码进行比较限制''
union all
select 92,''住院临嘱自动发送时，只有发往手术室的手术医嘱自动发送.''
union all
select 93,''住院发送医嘱，对应药品设置为不发药既拒绝执行.''
union all
select 94,''门诊诊间结算支持医保报销后剩余部分可以继续用别的支付方式付款.''
union all
select 95,''下达医嘱选择诊断时，使用老样式窗口选择诊断.''
union all
select 96,''医生站简易挂号时，病人姓名和手机号码一致时只能检索挂号不能新增.''
union all
select 97,''门诊发送医嘱或记账保存时启用卫材自动冲减库存功能.''
union all
select 98,''下达医嘱诊断时，只针对当前处方，不自动增加别的处方诊断.''
union all
select 100,''住院下达出院医嘱时启用出院小结模式''
union all
select 101,''护士站不启用合理用药接口''
union all
select 102,''住院下达手术医嘱必须要输入诊断''
union all
select 103,''住院护士站启用首页床位卡片外显屏''
union all
select 104,''精准扶贫接口web地址设置(项目名称栏输入地址、(Y)栏目对应接口类型1=数据库 2=webservices 3=本地+webservices)''
union all
select 105,''xml文档文件在HoServer服务端存放路径(项目名称栏输入路径)''
union all
select 106,''启用分单规则时，医保病人在分单规则基础上按多少条再分单(Y)栏目输入对应条数''
union all
select 107,''门诊医保病人一天只允许开一张药品处方''
union all
select 108,''门诊医生站手工打印处方或申请单时不受报表权限控制''
union all
select 109,''门诊医生站检索病人时不受开始结束时间限制''
union all
select 110,''门诊医保用药历史记录下载，(Y)栏目对应间隔下载时间(分),间隔分钟需要大于1''
union all
select 111,''门诊接诊预约病人时才弹出复诊选项窗口''
union all
select 112,''门诊启用当天回诊查看检查结果列表队列,(Y)栏目对应呼叫规则(1=医生自由呼叫 2=交替呼叫)''
union all
select 113,''下达卫材医嘱判断库存，门诊(Y)及住院(X),值大于0表示启用库存判断''
union all
select 114,''北京医保共享信息停止时，就不再判断跨院记录''
union all
select 115,''住院病人档案启用无纸化流程管理''
union all
select 116,''门诊分配治疗套餐时按整个套餐选择，不允许选套餐中某个阶段''
union all
select 117,''门诊分配治疗套餐时隐藏[现在执行]列''
union all
select 118,''启用分单规则时，在分单规则基础上启用分单标示的项目单独分单''
union all
select 119,''启用住院病人类别为门诊特病住院期间医保治疗方案判断''
union all
select 120,''医生站费用诊间结算(1=微信2=支付宝 3=两种都支持)，门诊(Y)及住院(X)大于零表示启用''
union all
select 121,''门诊医生站启用诊室ipad排队信息显示''
union all
select 122,''下达草药医嘱默认剂数,门诊(Y)及住院(X)大于零表示启用''
union all
select 123,''医生站调用第三方体检检查报告,门诊(Y)及住院(X)大于零表示启用(1=调用dll或exe 2=web方式''
union all
select 124,''门诊医生站下达诊断时不使用树形列表''
union all
select 125,''下达手术医嘱时，隐藏手术申请单输入窗口，门诊(Y)及住院(X)大于零表示启用''
union all
select 126,''合理用药单个药品说明书通过右键菜单查看''
union all
select 127,''医生站观片时不启用双屏判断，门诊(Y)及住院(X)大于零表示参数启用''
union all
select 128,''门诊执行治疗套餐时允许按单个套餐项目选择''
union all
select 129,''门诊处方，医师所开西成药用药天数跟总量、用量计算出的天数不一致时提醒医生''
union all
select 130,''下达手术医嘱时，卫材只检索维护的执行科室，门诊(Y)及住院(X)大于零表示启用''
union all
select 131,''记账划价时，卫材只检索维护的执行科室，门诊(Y)及住院(X)大于零表示启用''
union all
select 132,''医技、手术启用CRM回访接口调用''
union all
select 133,''下达医嘱时，只能检索诊断不允许手工输入，门诊(Y)及住院(X)大于零表示启用''
union all
select 134,''下达医嘱时，门诊处方判断药品条数限定时包含所有药品''
union all
select 135,''门诊套餐医嘱删除时，把对应套餐更新成拒绝执行''
union all
select 136,''药品、诊疗项目说明提示，设置值(Y)大于2(秒)时表示超过此时间自动关闭''
union all
select 137,''住院抗生素皮试医嘱，没有出来皮试结果多长时间((X)分钟)内禁止在下达抗生素类药品，列(X)大于零表示启用''
union all
select 138,''医保限制内容提醒，所有病人都提醒，门诊(Y)及住院(X)大于零表示启用''
union all
select 139,''门诊发送医嘱扣费后自动打印汇总收费小票,会员卡扣费(Y)及诊间结算(X)大于零表示启用''
union all
select 140,''住院临嘱发送时，不要自动根据医嘱调整病情''
union all
select 141,''紧急用药时，可以越级使用药品,门诊(Y)及住院(X)大于零表示启用（1=越1级，2=越2级）''
union all
select 142,''下达医嘱选择手术医生时，启用手术等级判断,门诊(Y)及住院(X)大于零表示启用''
union all
select 143,''门诊下达或发送医嘱可以选择不同处方类型合并打印，具体在报表(HO.Clinical.HBPrint.01)中设置''
union all
select 144,''下达医嘱选择治疗方案时，方案明细显示参考价''
union all
select 145,''门诊医生站发送医嘱会员扣费，会员卡金额不够时启用诊间会员充值功能''
) s
', Null, 0, 1, Null, 0, Null)
end
else --更新
begin
update Report_Objects set Config='
A202F89413EBBA9839C32D986E20C566
--系统发布的都是固定选项。后面可以追加
select s.fid,s.fname,cast(s.fid as varchar)+''--''+s.fname as sfname
from (
select 0 fid,''按药品规格限制用药(X)天数,且距上次用药剩余(Y)天数内才能再开'' fname
union all
select 1 ,''按药品品种限制用药(X)天数,且距上次用药剩余(Y)天数内才能再开''
union all
select 2,''icd疾病用药(X)天数限制''
union all
select 3,''所有药品限制用药(X)天数,且距上次用药剩余(Y)天数内才能再开''
union all
select 4,''抗菌药品限制DDD''
union all
select 5,''医保病人处方，医保和自费药品不允许在同一张处方''
union all
select 6,''医保病人处方，精神类和非精神类药品不允许在同一张处方''
union all
select 7,''医保病人处方，对应相同医保药品编码的西成药品一天内只允许开一次''
union all
select 8,''医保病人处方，所有医保药品限制最大用药(X)天数''
union all
select 9,''医保病人处方，处方诊断限制最大条数''
union all
select 10,''医保挂急诊号病人限制最大开药(X)天数''
union all
select 11,''医保处方诊断为行动不便病人限制最大开药(X)天数''
union all
select 12,''医保病人医保药品3、7、14、30处方天数提前开药限制''
union all
select 13,''医保病人医保处方草药单方不报判断''
union all
select 14,''医保病人限制开大额药品的医保险种判断''
union all
select 15,''医保分配基金限制判断，当月所开医保报销金额大于分配基金，则禁止医师在开医保处方''
union all
select 16,''医保病人处方，对应相同医保药品编码的西成药品不允许在同一张处方''
union all
select 17,''门诊处方，医师所开西成药用药天数跟总量、用量计算出的天数超过设定值禁止保存''
union all
select 18,''医保病人草药处方，可报与不可报草药药品不允许在同一张处方''
union all
select 19,''医保病人处方，西成药品按总量、用量计算出的天数最大不能超过(X)天数''
union all
select 20,''门诊处方，西成药品总量必须大于零''
union all
select 21,''医保病人处方，限定同检查类型的诊疗项目一天内只允许开一次''
union all
select 22,''医保病人处方，当药品总量是1时，对应医师开药天数不在限制，但总量与用量计算出的天数仍然限制''
union all
select 23 ,''医保病人处方，所有药品按医保编码限制用药(X)天数,且距上次用药剩余(Y)天数内才能再开''
union all
select 24 ,''一次开多条相同医技项目医嘱，第二条及以上医嘱单独生成条码''
union all
select 25 ,''对住院手术有关抗生素类用药疗程及时机控制提示''
union all
select 26 ,''对应类别下诊疗项目，门诊医保病人当天重复开时增加提示''
union all
select 27 ,''默认提取最后一次医嘱时，需要剔除不提取的诊疗类别''
union all
select 28 ,''启用药品规格中的限制用量判断，下达医嘱时判断单次用量''
union all
select 29,''门诊处方，当药品总量是1时，对应医师开药天数不在限制，但总量与用量计算出的天数仍然限制''
union all
select 30,''住院手术医嘱，限制必须输入切口等级''
union all
select 31,''住院按病人类别及科室设置药品费用(Y)及总费用(X)限制值''
union all
select 32,''门诊医生站刷会员卡做简易挂号时，病人费别默认提取会员卡对应信息''
union all
select 33,''第三方处方点评浏览调用地址''
union all
select 34,''限定检查类型必须输入病历摘要''
union all
select 35,''限定检查类型必须输入辅助检查''
union all
select 36,''限定检查类型必须输入检查目的''
union all
select 37,''门诊手术医嘱，手术室审核安排后就不能在回退或取消医嘱''
union all
select 38,''住院药品临嘱必须输入频次''
union all
select 39,''调用第三方仪器检查报告(设置对应浏览调用地址和参数)''
union all
select 40,''住院术后超过24小时禁止使用抗生素''
union all
select 41,''住院术前超过0.5--2小时禁止使用抗生素''
union all
select 42,''自动提取病史摘要天数限制，门诊(Y)天及住院(X)天内的病史摘要''
union all
select 43,''下达医嘱限制诊疗类项目禁止输入小数，门诊(Y)及住院(X)大于零表示禁止''
union all
select 44,''门诊下达医嘱窗口，隐藏病人信息框把其显示在标题栏''
union all
select 45,''门诊病人超过一定岁数，必须在首页输入收缩压、舒张压''
union all
select 46,''门诊医师站在检索病人地方显示自定义查询按钮''
union all
select 47,''门诊医保病人保内西成药品处方，不能下达自费药品''
union all
select 48,''门诊编辑医嘱(Y)及回退或医嘱退费(X)大于零表示必须刷会员卡才能继续操作''
union all
select 49,''下达医嘱限制诊疗类项目数量不能输入零，门诊(Y)及住院(X)大于零表示禁止''
union all
select 50,''门诊医保病人保内处方，不能下达执行性质为自费的项目''
union all
select 51,''门诊医保病人，下达处方默认医保处方类型''
union all
select 52,''门诊医保病人保内中草药品处方，不能下达不可报药品''
union all
select 53,''门诊简易挂号，病人类别为医保，一卡通扣费余额不够时也允许挂号''
union all
select 54,''门诊简易挂号，同一个病人当天同一个科室同一个医生只能挂号一次''
union all
select 55,''下达医嘱时，根据医保明细项目中备注进行提示''
union all
select 56,''下达门诊处方诊断或住院出院诊断时，调用慢性病确诊接口，门诊(Y)及住院(X)大于零表示调用''
union all
select 57,''眼科专科疾病信息调用填写''
union all
select 58,''检查报告页面使用中间表浏览图文报告''
union all
select 59,''合理用药审核处方时传入历史有效医嘱设置，门诊(Y)及住院(X)大于零表示传入。''
union all
select 60,''查看病人医疗信息web地址设置(设置对应浏览调用地址和参数)''
union all
select 61,''调用第三方超声系统查看超声影像报告接口程序路径设置''
union all
select 62,''医护站发送医嘱后实时上传医保费用，门诊(Y)及住院(X)大于零表示启用''
union all
select 63,''医生站费用诊间结算(1=医保卡2=银行pos 3=两种都支持)，门诊(Y)及住院(X)大于零表示启用''
union all
select 64,''新版大通合理用药医院编码设置''
union all
select 65,''大通合理用药审核处方时，返回一般或其他信息提示时，医生站提示是否继续保存医嘱。''
union all
select 66,''调用第三方影像报告IE接口时嵌入到第三方影像报告页面浏览''
union all
select 67,''四川中医平台web地址设置''
union all
select 68,''分级转诊平台web地址设置(入参通过报表设置)''
union all
select 69,''医生给病人选择分配治疗套餐计划web地址''
union all
select 70,''北京五洲妇儿医院门诊医生站排队叫号接口(项目列维护ip，y列为端口号)''
union all
select 71,''门诊诊间结算打印收费小票，医保(Y)及银行pos(X)大于零表示启用''
union all
select 72,''医保限制内容提醒，门诊(Y)及住院(X)大于零表示启用''
union all
select 73,''下达医嘱限制修改检验样本类型，门诊(Y)及住院(X)大于零表示禁止''
union all
select 74,''门诊首页修改保存时，不判断限制年龄跟出生日期年份不一致的问题.''
union all
select 75,''下达医嘱医保病人只限制开医保目录内的药品，门诊(Y)及住院(X)大于零表示禁止''
union all
select 76,''门诊接诊病人时弹出复诊选项窗口''
union all
select 77,''门诊处方限制口服和注射不能开在同一张处方''
union all
select 78,''第三方手麻病历调阅web地址''
union all
select 79,''下达医嘱界面给药途径、频次、剂量、天数单独列显示''
union all
select 80,''第三方病理报告调阅web地址''
union all
select 81,''用血管理接口(1:西安金智；2=唐山启奥),对应(X)列值''
union all
select 82,''门诊处方治疗申请单打印启用报表授权判断''
union all
select 83,''住院下达会诊医嘱启用会诊申请单模式''
union all
select 84,''住院下达医嘱前进行术后24、48、72小时用药未停止医嘱提醒''
union all
select 85,''住院会诊申请医嘱及申请单在执行科室对应病区提醒及打印''
union all
select 86,''下达医嘱时显示药品产地，门诊(Y)及住院(X)大于零表示显示''
union all
select 87,''下达医嘱时启用病历日志，门诊(Y)及住院(X)大于零表示启用''
union all
select 88,''住院入科时限制质控员，一级质控员(Y)及二级质控员(X)大于零必须输入''
union all
select 89,''使用HO老版本pacs''
union all
select 90,''启用合理用药及自助机接口日志记录''
union all
select 91,''医保病人，相同药品编码判断，根据药品规格对应备用编码进行比较限制''
union all
select 92,''住院临嘱自动发送时，只有发往手术室的手术医嘱自动发送.''
union all
select 93,''住院发送医嘱，对应药品设置为不发药既拒绝执行.''
union all
select 94,''门诊诊间结算支持医保报销后剩余部分可以继续用别的支付方式付款.''
union all
select 95,''下达医嘱选择诊断时，使用老样式窗口选择诊断.''
union all
select 96,''医生站简易挂号时，病人姓名和手机号码一致时只能检索挂号不能新增.''
union all
select 97,''门诊发送医嘱或记账保存时启用卫材自动冲减库存功能.''
union all
select 98,''下达医嘱诊断时，只针对当前处方，不自动增加别的处方诊断.''
union all
select 100,''住院下达出院医嘱时启用出院小结模式''
union all
select 101,''护士站不启用合理用药接口''
union all
select 102,''住院下达手术医嘱必须要输入诊断''
union all
select 103,''住院护士站启用首页床位卡片外显屏''
union all
select 104,''精准扶贫接口web地址设置(项目名称栏输入地址、(Y)栏目对应接口类型1=数据库 2=webservices 3=本地+webservices)''
union all
select 105,''xml文档文件在HoServer服务端存放路径(项目名称栏输入路径)''
union all
select 106,''启用分单规则时，医保病人在分单规则基础上按多少条再分单(Y)栏目输入对应条数''
union all
select 107,''门诊医保病人一天只允许开一张药品处方''
union all
select 108,''门诊医生站手工打印处方或申请单时不受报表权限控制''
union all
select 109,''门诊医生站检索病人时不受开始结束时间限制''
union all
select 110,''门诊医保用药历史记录下载，(Y)栏目对应间隔下载时间(分),间隔分钟需要大于1''
union all
select 111,''门诊接诊预约病人时才弹出复诊选项窗口''
union all
select 112,''门诊启用当天回诊查看检查结果列表队列,(Y)栏目对应呼叫规则(1=医生自由呼叫 2=交替呼叫)''
union all
select 113,''下达卫材医嘱判断库存，门诊(Y)及住院(X),值大于0表示启用库存判断''
union all
select 114,''北京医保共享信息停止时，就不再判断跨院记录''
union all
select 115,''住院病人档案启用无纸化流程管理''
union all
select 116,''门诊分配治疗套餐时按整个套餐选择，不允许选套餐中某个阶段''
union all
select 117,''门诊分配治疗套餐时隐藏[现在执行]列''
union all
select 118,''启用分单规则时，在分单规则基础上启用分单标示的项目单独分单''
union all
select 119,''启用住院病人类别为门诊特病住院期间医保治疗方案判断''
union all
select 120,''医生站费用诊间结算(1=微信2=支付宝 3=两种都支持)，门诊(Y)及住院(X)大于零表示启用''
union all
select 121,''门诊医生站启用诊室ipad排队信息显示''
union all
select 122,''下达草药医嘱默认剂数,门诊(Y)及住院(X)大于零表示启用''
union all
select 123,''医生站调用第三方体检检查报告,门诊(Y)及住院(X)大于零表示启用(1=调用dll或exe 2=web方式''
union all
select 124,''门诊医生站下达诊断时不使用树形列表''
union all
select 125,''下达手术医嘱时，隐藏手术申请单输入窗口，门诊(Y)及住院(X)大于零表示启用''
union all
select 126,''合理用药单个药品说明书通过右键菜单查看''
union all
select 127,''医生站观片时不启用双屏判断，门诊(Y)及住院(X)大于零表示参数启用''
union all
select 128,''门诊执行治疗套餐时允许按单个套餐项目选择''
union all
select 129,''门诊处方，医师所开西成药用药天数跟总量、用量计算出的天数不一致时提醒医生''
union all
select 130,''下达手术医嘱时，卫材只检索维护的执行科室，门诊(Y)及住院(X)大于零表示启用''
union all
select 131,''记账划价时，卫材只检索维护的执行科室，门诊(Y)及住院(X)大于零表示启用''
union all
select 132,''医技、手术启用CRM回访接口调用''
union all
select 133,''下达医嘱时，只能检索诊断不允许手工输入，门诊(Y)及住院(X)大于零表示启用''
union all
select 134,''下达医嘱时，门诊处方判断药品条数限定时包含所有药品''
union all
select 135,''门诊套餐医嘱删除时，把对应套餐更新成拒绝执行''
union all
select 136,''药品、诊疗项目说明提示，设置值(Y)大于2(秒)时表示超过此时间自动关闭''
union all
select 137,''住院抗生素皮试医嘱，没有出来皮试结果多长时间((X)分钟)内禁止在下达抗生素类药品，列(X)大于零表示启用''
union all
select 138,''医保限制内容提醒，所有病人都提醒，门诊(Y)及住院(X)大于零表示启用''
union all
select 139,''门诊发送医嘱扣费后自动打印汇总收费小票,会员卡扣费(Y)及诊间结算(X)大于零表示启用''
union all
select 140,''住院临嘱发送时，不要自动根据医嘱调整病情''
union all
select 141,''紧急用药时，可以越级使用药品,门诊(Y)及住院(X)大于零表示启用（1=越1级，2=越2级）''
union all
select 142,''下达医嘱选择手术医生时，启用手术等级判断,门诊(Y)及住院(X)大于零表示启用''
union all
select 143,''门诊下达或发送医嘱可以选择不同处方类型合并打印，具体在报表(HO.Clinical.HBPrint.01)中设置''
union all
select 144,''下达医嘱选择治疗方案时，方案明细显示参考价''
union all
select 145,''门诊医生站发送医嘱会员扣费，会员卡金额不够时启用诊间会员充值功能''
) s
' where Code='HO.Clinical.Dev.000002'
end
GO
--删除旧记录
--delete from Report_Objects where Code='HO.Clinical.HSRateRep.01' --删除脚本内容
IF not EXISTS(SELECT * FROM Report_Objects WHERE code='HO.Clinical.HSRateRep.01')
begin --插入
Declare @Report_Id_1419 int
Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1419 out
Declare @Report_Id_1419_CateID int
Set @Report_Id_1419_CateID = (select id from Report_Categories where code='HIS.5')
insert into Report_Objects(ID,Code,Name,Description,Password,CategoryID,xType,Classification,ProductID,ProgramID,ElementID,CreateDate,UpdateDate,ISSUANCEDATE,Config,SetupPath,IsMenu,Enabled,GroupName,Authorized,RefreshInterval) Values(@Report_Id_1419, 'HO.Clinical.HSRateRep.01', '护士站费用查询记录报表打印', Null, Null, @Report_Id_1419_CateID, 1, 3, 100, 0, Null, '2017-11-08 13:15:04', '2017-11-08 13:15:04', Null, Null, Null, 0, 1, Null, 0, Null)
end
else --更新
begin
print 'HO.Clinical.HSRateRep.01 已经存在.'
end
GO
--删除旧记录
--delete from Report_Objects where Code='HO.Clinical.OtherSXGL.01' --删除脚本内容
IF not EXISTS(SELECT * FROM Report_Objects WHERE code='HO.Clinical.OtherSXGL.01')
begin --插入
Declare @Report_Id_1420 int
Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1420 out
Declare @Report_Id_1420_CateID int
Set @Report_Id_1420_CateID = (select id from Report_Categories where code='HIS.3')
insert into Report_Objects(ID,Code,Name,Description,Password,CategoryID,xType,Classification,ProductID,ProgramID,ElementID,CreateDate,UpdateDate,ISSUANCEDATE,Config,SetupPath,IsMenu,Enabled,GroupName,Authorized,RefreshInterval) Values(@Report_Id_1420, 'HO.Clinical.OtherSXGL.01', '第三方输血管理调用设置', Null, Null, @Report_Id_1420_CateID, 1, 4, 100, 0, Null, '2017-11-08 13:23:22', '2017-11-08 13:23:22', Null, '
15535BCF60D9E59BE551E10B6A86806D
declare @VAA07 varchar(20),@Re_ParaValue varchar(1024),@bce01 varchar(20),@bce02 varchar(20),@bce03 varchar(64),@ACF01 varchar(20),@VAA01 varchar(20)
set @ACF01 = %d
set @VAA07 = %d
set @bce01 = %d
set @bce02 = %s
set @bce03 = %s
--需要返回的参数及值都在这里设定
if @ACF01=2
select @VAA01= VAA01
from VAE1 a with(nolock)
where a.VAE01 = @VAA07
else
select @VAA01= VAA01
from VAC1 a with(nolock)
where a.VAC01 = @VAA07
select ''http://172.20.138.23/orgview.aspx?org=xxx&user=yyy&key=zzz&idno=''+(select dbo.GetO2E(dbo.GetASCII(VAA15)) from VAA1 with(nolock) where VAA01=@VAA01) PValue
--select ''http://192.168.1.137:9999/optometry/cf?VAA01=''+@VAA01+''&ACF01=''+@ACF01+''&VAA07=''+@VAA07+''&BCE01=''+@BCE01+''&BCE03=''+@BCE03+''&STATUS=0'' PValue
', Null, 0, 1, Null, 0, Null)
end
else --更新
begin
print 'HO.Clinical.OtherSXGL.01 已经存在.'
end
GO
--删除旧记录
--delete from Report_Objects where Code='HO.Clinical.PACsystem.01' --删除脚本内容
IF not EXISTS(SELECT * FROM Report_Objects WHERE code='HO.Clinical.PACsystem.01')
begin --插入
Declare @Report_Id_1421 int
Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1421 out
Declare @Report_Id_1421_CateID int
Set @Report_Id_1421_CateID = (select id from Report_Categories where code='HIS.3')
insert into Report_Objects(ID,Code,Name,Description,Password,CategoryID,xType,Classification,ProductID,ProgramID,ElementID,CreateDate,UpdateDate,ISSUANCEDATE,Config,SetupPath,IsMenu,Enabled,GroupName,Authorized,RefreshInterval) Values(@Report_Id_1421, 'HO.Clinical.PACsystem.01', 'PAC系统调用配置', Null, Null, @Report_Id_1421_CateID, 1, 4, 100, 0, Null, '2017-11-09 10:11:10', '2017-11-09 10:12:14', Null, '
A1B5AA1AD224680E3E3C08FA664C84D5
declare @VAA07 varchar(20),@Re_ParaValue varchar(1024),@bce01 varchar(20),@bce02 varchar(20)
declare @bce03 varchar(64),@ACF01 varchar(20),@VAA01 varchar(20),@bck01 int
set @ACF01 = %d
set @VAA07 = %d
set @bce01 = %d
set @bce02 = %s
set @bce03 = %s
set @bck01 = %d
--需要返回的参数及值都在这里设定 如果调用的是exe程序，程序需要放到ho根目录，配置如下：PAC.exe 或 文件夹名称\PAC.exe
--如果调用时url，则直接写url地址既可，后面跟上需要传入的参数
--字段psign 1=表示调用exe程序 2=表示调用url地址
if @ACF01=2
select @VAA01= VAA01
from VAE1 a with(nolock)
where a.VAE01 = @VAA07
else
select @VAA01= VAA01
from VAC1 a with(nolock)
where a.VAC01 = @VAA07
select 1 psign,''返回调用地址及参数'' PValue
', Null, 0, 1, Null, 0, Null)
end
else --更新
begin
print 'HO.Clinical.PACsystem.01 已经存在.'
end
GO
--删除旧记录
--delete from Report_Objects where Code='HO.ThirdDB.Set.000001' --删除脚本内容
IF not EXISTS(SELECT * FROM Report_Objects WHERE code='HO.ThirdDB.Set.000001')
begin --插入
Declare @Report_Id_1422 int
Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1422 out
Declare @Report_Id_1422_CateID int
Set @Report_Id_1422_CateID = (select id from Report_Categories where code='HIS.3')
insert into Report_Objects(ID,Code,Name,Description,Password,CategoryID,xType,Classification,ProductID,ProgramID,ElementID,CreateDate,UpdateDate,ISSUANCEDATE,Config,SetupPath,IsMenu,Enabled,GroupName,Authorized,RefreshInterval) Values(@Report_Id_1422, 'HO.ThirdDB.Set.000001', '传染病及慢性病上传设置', Null, Null, @Report_Id_1422_CateID, 1, 4, 100, 0, Null, '2017-11-09 15:19:47', '2017-11-09 15:41:50', Null, '
A7ED196E12CFEB678AA6E322BF0BD298
select ''http://192.168.1.137:7001/phms/services/RequestDispatchQZ?wsdl'' as defWSDL ,
''http://192.168.1.137:7001/phms/services/RequestDispatchQZ?wsdl'' as defURL,
''330881041'' as sysCode, ''999'' as SrcSystem,''D901'' as Integration,
''test'' as UserName,''123456'' as Password,
''33080214'' as OrgCountyCode,''九华乡'' as OrgCountyName,''330881041'' as OrgCode,
''浙江省衢州市江山市贝林医院'' as OrgName
', Null, 0, 1, Null, 0, Null)
end
else --更新
begin
print 'HO.ThirdDB.Set.000001 已经存在.'
end
GO
--删除旧记录
--delete from Report_Objects where Code='HO.Clinical.HBPrint.01' --删除脚本内容
IF not EXISTS(SELECT * FROM Report_Objects WHERE code='HO.Clinical.HBPrint.01')
begin --插入
Declare @Report_Id_1426 int
Exec Core_NewId 'Report_Objects', 'id', @Report_Id_1426 out
Declare @Report_Id_1426_CateID int
Set @Report_Id_1426_CateID = (select id from Report_Categories where code='HIS.3')
insert into Report_Objects(ID,Code,Name,Description,Password,CategoryID,xType,Classification,ProductID,ProgramID,ElementID,CreateDate,UpdateDate,ISSUANCEDATE,Config,SetupPath,IsMenu,Enabled,GroupName,Authorized,RefreshInterval) Values(@Report_Id_1426, 'HO.Clinical.HBPrint.01', '门诊下达医嘱合并处方打印设置', Null, Null, @Report_Id_1426_CateID, 1, 4, 100, 0, Null, '2017-11-18 16:40:48', '2017-11-18 16:55:13', Null, '
4F2A8E1F96C09750704AEB0FA3866DE6
--可以在此设置分类设置 合并打印的处方
--此数据集返回四个字段 varchar(128) scbm01 医嘱单id,cbm06,cbm07,tinyint IsHb 是否是合并打印(0=否 1=是)
--,varchar(64) sRepNo 报表编码 是合并打印(IsHb=1)则返回对应调用的报表 编码，否则返回''''
--合并打印对应报表传参，只传入scbm01,是有多个医嘱单id用逗号分开的字符串
--是合并打印的cbm06,cbm07返回值 cbm06=99 cbm07=99,否则返回原值
declare @kbcbm table(CBM01 int,CBM06 int,CBM07 int)
insert into @kbcbm(CBM01,CBM06,CBM07 )
select CBM01,CBM06,CBM07
from CBM1
where CBM01 in (%s) and CBM06 in (1,2,4,5,9)
--返回
select cast('''' as varchar(64)) scbm01,0 cbm06,0 cbm07,0 IsHb, cast('''' as varchar(64)) sRepNo
', Null, 0, 1, Null, 0, Null)
end
else --更新
begin
print 'HO.Clinical.HBPrint.01 已经存在.'
end
GO
--增加分类
GO
IF not EXISTS(SELECT * FROM SYS_ScriptCategories WHERE id=26)
insert into SYS_ScriptCategories(ID,Code,Name,ComponentID,xRowNum,ParentID,xLevel,Description) Values(26, 'SC26', 'HOKernel核心管理', 3, 0, 0, 0, Null)
else
print 'SYS_ScriptCategories.id=26 已经存在.'
GO
If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 26)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=26
GO
--删除旧记录
delete from SYS_Scripts where id=1468 --删除脚本内容
GO
GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=1468)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode,Custom) Values(1468, 75, 'SYS_LoadRoleUsers', '加载角色被那些用户使用的列表', '加载角色被那些用户使用的列表', 1, 0, Null, 1, 'DECLARE @Id int, @xType int
SET @Id = %d
SET @xType = 0
--获取程序权限列表明细，以便界面处理修改
SELECT distinct b.ProgramId into #tmpPermissions from SYS_RolePermissions a
join Core_ProgramElements b on a.Elementid=b.id
where a.RoleId=@Id AND a.xType=@xType AND b.Enabled=1
--创建程序元素临时表
SELECT IDENTITY(int,1,1) i, @id AS RoleId, a.ID AS ElementID,0 AS Permission
INTO #tmpElements_Role FROM Core_ProgramElements a join #tmpPermissions b on a.ProgramID=b.ProgramId
where not exists(select * from SYS_RolePermissions c where c.Elementid=a.ID and c.RoleID=@Id AND c.xType=@xType)
declare @NewId int=(select max(ID) from SYS_RolePermissions)
INSERT INTO SYS_RolePermissions(ID, RoleID, xType, ElementID, Permission)
SELECT @NewId +i --明细ID
,RoleId --角色ID
,0 --类型
,ElementID --程序元素ID
,Permission --权限
FROM #tmpElements_Role
--删除临时表
DROP TABLE #tmpPermissions
DROP TABLE #tmpElements_Role
--返回用户列表
SELECT a.id, b.code, b.Name,c.ABW01 FROM SYS_UserRoles a
JOIN SYS_Users b ON a.UserID=b.ID
JOIN BCE1 c ON b.EmployeeID=c.BCE01
WHERE a.RoleID=@Id ', '2017-11-29 14:31:52', '(8088)王海涛', 0, 0)
else
print 'SYS_Scripts.id=1468 已经存在.'
GO
If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 1468)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=1468
GO
--增加分类
GO
IF not EXISTS(SELECT * FROM SYS_ScriptCategories WHERE id=26)
insert into SYS_ScriptCategories(ID,Code,Name,ComponentID,xRowNum,ParentID,xLevel,Description) Values(26, 'SC26', 'HOKernel核心管理', 3, 0, 0, 0, Null)
else
print 'SYS_ScriptCategories.id=26 已经存在.'
GO
If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 26)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=26
GO
--删除旧记录
delete from SYS_Scripts where id=1465 --删除脚本内容
GO
GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=1465)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode,Custom) Values(1465, 75, 'SYS_LoadUserRoles', '加载用户所拥有的角色', '加载用户所拥有的角色', 1, 0, Null, 1, 'DECLARE @Id int, @xType int,@NewId int
SET @Id = %d
SET @xType = 0
--获取程序权限列表明细，以便界面处理修改
SELECT distinct b.ProgramId into #tmpPermissions from SYS_UserPermissions a
join Core_ProgramElements b on a.Elementid=b.id
where a.UserId=@Id AND a.xType=0 AND b.Enabled=1
--创建程序元素临时表 #tmpElements
SELECT IDENTITY(int,1,1) i, @id AS UserId, a.ID AS ElementID,0 AS Permission
INTO #tmpElements_User FROM Core_ProgramElements a join #tmpPermissions b on a.ProgramID=b.ProgramId
where not exists(select * from SYS_UserPermissions where UserId=@id AND ElementID=a.ID AND xType=0)
select @NewId=max(ID) from SYS_UserPermissions
--EXEC CORE_NEWID ''SYS_UserPermissions'', ''ID'', @NewId OUT
INSERT INTO SYS_UserPermissions(ID, UserID, xType, ElementID, Permission)
SELECT @NewId +i --明细ID
,UserId --角色ID
,0 --类型
,ElementID --程序元素ID
,Permission --权限
FROM #tmpElements_User
--删除临时表
DROP TABLE #tmpPermissions
DROP TABLE #tmpElements_User
SELECT a.ID, A.UserID, a.RoleID, b.Name, b.Description FROM SYS_UserRoles a
LEFT JOIN SYS_Roles b ON a.RoleID=b.ID where a.UserId=@Id', '2017-11-29 14:56:08', '(8088)王海涛', 0, 0)
else
print 'SYS_Scripts.id=1465 已经存在.'
GO
If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 1465)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=1465
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--存储过程.SYS_Permissions_Upate
ALTER PROC [SYS_Permissions_Upate] @xml nText, @id int out,@State int = 0
AS
--创建授权列表，明细
--8056 于 2009.9.1 创建
--8056 于 2010.1.12 修改
---------------------------判断前台传递参数的合法性-----------------------------------------
IF @State<1 OR @State>4
BEGIN
--@State 1:用户独立程序授权 2:用户独立报表授权 3:角色程序授权 4:角色报表授权
RAISERROR('提交的命令不正确,错误命令:%d.', 16, 1, @State) WITH NOWAIT
RETURN @State
END
IF @State<3 and NOT EXISTS(SELECT * FROM SYS_Users WHERE id=@id) --提交的数据为空，则终止执行; 如果存在记录，则开始增加
BEGIN
--检测用户ID是否存在
RAISERROR('需要授权的用户不存在.', 16, 1) WITH NOWAIT
RETURN
END
IF @State>2 and NOT EXISTS(SELECT * FROM SYS_Roles WHERE id=@id) --提交的数据为空，则终止执行; 如果存在记录，则开始增加
BEGIN
--检测角色ID是否存在
RAISERROR('需要授权的角色不存在.', 16, 1) WITH NOWAIT
RETURN
END
------------------------------------解析前台传递的XML数据---------------------------------------
DECLARE @iDOM int, @iret int
Exec @iret = sp_xml_preparedocument @iDOM OUTPUT, @XML
if @iret > 0
BEGIN
EXEC sp_xml_removedocument @iDOM
RAISERROR('解析XML文档时发生错误，错误号: %d.', 16, 1, @iret) WITH NOWAIT
RETURN @iret
END
--------------------------------------读取授权模块临时表---------------------------------------
SELECT IDENTITY(int,1,1) i,[ID], Permission INTO #tmpPermissions --要授权的模块列表
FROM OpenXml(@iDOM, '/Root/Permissions/Ie', 8)
WITH (
[Id] int --程序ID
,Permission tinyint --权限
)
--------------------------------------释放XML------------------------------------------------
EXEC sp_xml_removedocument @iDOM
--------------------------------------以下更新授权信息---------------------------------------
--定义循环变量,总记录数，新ID
DECLARE @I int, @Count int, @NewId int
--1:用户独立程序授权
IF @State=1
BEGIN
--创建程序元素临时表 #tmpElements
SELECT IDENTITY(int,1,1) i, @id AS UserId, a.ID AS ElementID,0 AS Permission
INTO #tmpElements_User FROM Core_ProgramElements a join #tmpPermissions b on a.ProgramID=b.id
where not exists(select * from SYS_UserPermissions where UserId=@id AND ElementID=a.ID AND xType=0)
--更新存在的权限信息
--UPDATE a SET a.Permission=b.Permission FROM #tmpElements_User a,SYS_UserPermissions b WHERE b.UserId=@id AND a.ElementID=b.ElementID AND b.xType=0
--删除独立授权表中该用户已经授权的信息
--DELETE FROM SYS_UserPermissions WHERE UserId=@id AND xType=0
--SELECT @I = 0, @COUNT = COUNT(*) FROM #tmpElements_User
--循环插入到明细
--WHILE @I<@COUNT
BEGIN
select @NewId=max(ID) from SYS_UserPermissions
--EXEC CORE_NEWID 'SYS_UserPermissions', 'ID', @NewId OUT
INSERT INTO SYS_UserPermissions(ID, UserID, xType, ElementID, Permission)
SELECT @NewId +i --明细ID
,UserId --角色ID
,0 --类型
,ElementID --程序元素ID
,Permission --权限
FROM #tmpElements_User --WHERE i=@I+1
--SET @I = @I + 1
END
--删除临时表
DROP TABLE #tmpElements_User
END
--2:用户独立报表授权
ELSE IF @State=2
BEGIN
--更新新数据集中的授权状态
UPDATE a SET a.Permission=b.Permission FROM #tmpPermissions a,SYS_UserPermissions b WHERE b.UserId=@id AND a.id=b.ElementID AND b.xType=1
--删除独立授权表中该用户已经报表授权的信息
DELETE FROM SYS_UserPermissions WHERE UserId=@id AND xType=1
SELECT @I = 0, @COUNT = COUNT(*) FROM #tmpPermissions
--循环插入到明细
WHILE @I<@COUNT
BEGIN
EXEC CORE_NEWID 'SYS_UserPermissions', 'ID', @NewId OUT
INSERT INTO SYS_UserPermissions(ID, UserID, xType, ElementID, Permission)
SELECT @NewId --明细ID
,@id --角色ID
, 1 --报表角色
,[id] --程序元素ID
,Permission --权限
FROM #tmpPermissions WHERE i=@I+1
SET @I = @I + 1
END
END
--3:角色程序授权
ELSE IF @State=3
BEGIN
--创建程序元素临时表 #tmpElements
SELECT IDENTITY(int,1,1) i, @id AS RoleId, a.ID AS ElementID,0 AS Permission
INTO #tmpElements_Role FROM Core_ProgramElements a join #tmpPermissions b on a.ProgramID=b.id
where not exists(select * from SYS_RolePermissions c where c.Elementid=a.ID and c.RoleID=@Id and c.xType=0)
--更新存在的权限信息
--UPDATE a SET a.Permission=b.Permission FROM #tmpElements_Role a,SYS_RolePermissions b WHERE b.RoleID=@id AND a.ElementID=b.ElementID AND b.xType=0
--删除独立授权表中该用户已经授权的信息
--DELETE FROM SYS_RolePermissions WHERE RoleId=@id AND xType=0
--SELECT @I = 0, @COUNT = COUNT(*) FROM #tmpElements_Role
--循环插入到明细
--WHILE @I<@COUNT
BEGIN
-- EXEC CORE_NEWID 'SYS_RolePermissions', 'ID', @NewId OUT
select @NewId=max(ID) from SYS_RolePermissions
INSERT INTO SYS_RolePermissions(ID, RoleID, xType, ElementID, Permission)
SELECT @NewId +i --明细ID
,RoleId --角色ID
,0 --类型 0:程序 1:报表
,ElementID --程序元素ID
,Permission --权限
FROM #tmpElements_Role
--WHERE i=@I+1
-- SET @I = @I + 1
END
--删除临时表
DROP TABLE #tmpElements_Role
END
--4:角色报表授权
ELSE IF @State=4
BEGIN
--更新新数据集中的授权状态
UPDATE a SET a.Permission=b.Permission FROM #tmpPermissions a,SYS_RolePermissions b WHERE b.RoleId=@id AND a.id=b.ElementID AND b.xType=1
--删除角色授权表中该用户已经报表授权的信息
DELETE FROM SYS_RolePermissions WHERE RoleId=@id AND xType=1
SELECT @I = 0, @COUNT = COUNT(*) FROM #tmpPermissions
--循环插入到明细
WHILE @I<@COUNT
BEGIN
EXEC CORE_NEWID 'SYS_RolePermissions', 'ID', @NewId OUT
INSERT INTO SYS_RolePermissions(ID, RoleID, xType, ElementID, Permission)
SELECT @NewId --明细ID
,@id --角色ID
,1 --报表角色
,[id] --程序元素ID
,Permission --权限
FROM #tmpPermissions WHERE i=@I+1
SET @I = @I + 1
END
END
--删除临时表
DROP TABLE #tmpPermissions
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
GO
alter table VBU3 alter column VCB04 varchar(64)
GO
insert into bpc1(bbx01,bce01)
select bbx01,bce01 from BBX1 a where BBX15=0 and BCE01>0 and exists(select * from bef1 where bbx01a=a.bbx01) and not exists(select * from bpc1 where bbx01=a.bbx01 and bce01=a.bce01)
GO
Update a
set a.ACV12 = b.ABC12 ,
a.ACV13 = b.ABC13
from ACV1 a
join ABC1 b on b.ABC02 = a.ABC02
where ACV12 is null
GO
--增加分类
GO
IF not EXISTS(SELECT * FROM SYS_ScriptCategories WHERE id=1)
insert into SYS_ScriptCategories(ID,Code,Name,ComponentID,xRowNum,ParentID,xLevel,Description) Values(1, 'SC1', 'HOMaster 基础管理', 1, 0, 0, 0, Null)
else
print 'SYS_ScriptCategories.id=1 已经存在.'
GO
If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 1)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=1
GO
--删除旧记录
delete from SYS_Scripts where id=1047 --删除脚本内容
GO
GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=1047)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode,Custom) Values(1047, 31, 'Sql_个人治疗方案', Null, ' 个人治疗方案', 1, 1, Null, 1, 'DECLARE @lACF01 int, @lBCE01 int,@lBCK01 int
Declare @lDate varchar(30)
SET @lACF01 = %d
SET @lBCE01 = %d
Set @lBCK01 = %d
Set @lDate = Convert(varchar(10),GetDate(),21)+'' 23:59:59''
declare @tpbef table(BBX05 varchar(256),BBX01 int,BBX15 tinyint,FBBX15 varchar(20),BDO01 int)
insert into @tpbef(BBX05,BBX01,BBX15,FBBX15,BDO01)
SELECT a.BBX05,a.BBX01,a.BBX15,case a.BBX15 when 0 then ''个人'' when 1 then ''科室'' when 2 then ''全院'' end FBBX15,a.BDO01
FROM BBX1 a with(nolock)
WHERE a.BDA01=''9'' AND ((a.BBX15=0 and exists(select * from BPC1 c5 with(nolock) where c5.BBX01=a.BBX01 and c5.BCE01=@lBCE01))
or a.BCE01=@lBCE01 Or (a.BBX15 = 1 And Exists(Select * From BAR1 b with(nolock) Where a.BBX01 = b.BBX01 And b.BCK01 = @lBCK01)))
AND @lACF01 = (a.ACF01 & @lACF01) and a.BBX25 > @lDate
--order by a.BBX15 Desc,a.BBX01
if exists(select * from BLP1 where BLP02=144)
begin
select m.BBX05,m.BBX01,m.BBX15,m.FBBX15,m.BDO01,n.fvaj38
from @tpbef m
left join (
select s.BBX01A,SUM(s.bMoney) fvaj38
from (
select a.BBX01A,sum(ISNULL(a.BEF11,0)*b.Price) as bMoney
from BEF1 a with(nolock)
left join V_BDU b on b.BBX01=a.BBX01C
where a.BBY01=0 and exists(select * from @tpbef c where c.BBX01=a.BBX01A)
group by a.BBX01A
union all
select a.BBX01A,sum(ISNULL(a.BEF11,0)*b.BBY25) as bMoney
from BEF1 a with(nolock)
join BBY1 b with(nolock) on b.BBY01=a.BBY01
where a.BBY01>0 and exists(select * from @tpbef c where c.BBX01=a.BBX01A)
group by a.BBX01A ) s
group by s.BBX01A ) n on n.BBX01A=m.BBX01
order by m.BBX15 desc,m.BBX01
end
else
select m.BBX05,m.BBX01,m.BBX15,m.FBBX15,m.BDO01,0 fvaj38
from @tpbef m
order by m.BBX15 desc,m.BBX01', '2017-12-18 13:32:38', '(8088)王海涛', 0, 0)
else
print 'SYS_Scripts.id=1047 已经存在.'
GO
If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 1047)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=1047
GO
--增加分类
GO
IF not EXISTS(SELECT * FROM SYS_ScriptCategories WHERE id=1)
insert into SYS_ScriptCategories(ID,Code,Name,ComponentID,xRowNum,ParentID,xLevel,Description) Values(1, 'SC1', 'HOMaster 基础管理', 1, 0, 0, 0, Null)
else
print 'SYS_ScriptCategories.id=1 已经存在.'
GO
If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 1)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=1
GO
--删除旧记录
delete from SYS_Scripts where id=2014 --删除脚本内容
GO
GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=2014)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode,Custom) Values(2014, 77, 'SQL_医技执行信息', Null, '获取医技执行信息，执行要求时间，数次，或正在执行信息', 1, 0, Null, 1, 'DECLARE @VBI01 INT, @NOW Datetime
SET @VBI01 = %d
SET @NOW = GetDate()
if exists(select * from v_vbi_1 where vbi01 = @VBI01)
SELECT top 1
ISNULL(B.VBJ01, 0) VBJ01 --ID
,ISNULL(B.VBI03, A.VBI03) VBI03 --发送号
,ISNULL(B.VBJ04, A.VBI09) VBJ04 --要求时间
,A.PARTS
,A.VBI07 --发送次数
,A.VBI01 --
,A.VBI05 --收费单据ID VAI01
,A.VAF01 --医嘱ID
,0 VAF11 --医嘱类型 长，临
,0 VAJ01
,ISNULL(B.VBJ05, a.VBI35) VBJ05 --本次数次
,B.VBJ06 --执行摘要
,B.BCE03A --执行人
,@NOW VBJ08 --执行时间
,B.BCE03B --登记人
,@NOW VBJ10 --登记时间
,B.VBJ11 --记录哪几组医嘱一起执行的
,B.BCE03C --接单人
,B.BCE03D --配药人
,B.VBJ14 --保存本次执行一共有几组
,B.VBJ15 --保存次序
,B.VBJ16 --本组的滴速
,B.VBJ17 --本组的滴系数
,B.VBJ18 --药品的液体量
,B.VBJ19 --执行完需要用的时间，单位秒
,B.VBJ20 --提前多少时间进行提醒，单位秒,-1表示不提醒，0表示到期提醒，>0表示提前的时间
,B.VBJ21 --接单护士填写药品执行时的相关说明，如先输，避光
,a.VBI10, a.VBI35
from V_VBI_1 A
LEFT JOIN VBJ1 B with(nolock) ON A.VAF01=B.VAF01 AND A.VBI03=B.VBI03 and VBJ05>0 and VBJ05<=a.VBI35
WHERE A.VBI01 = @VBI01
order by b.VBJ01 desc
else
SELECT top 1
ISNULL(B.VBJ01, 0) VBJ01 --ID
,ISNULL(B.VBI03, A.VBI03) VBI03 --发送号
,ISNULL(B.VBJ04, A.VBI09) VBJ04 --要求时间
,A.PARTS
,A.VBI07 --发送次数
,A.VBI01 --
,A.VBI05 --收费单据ID VAI01
,A.VAF01 --医嘱ID
,0 VAF11 --医嘱类型 长，临
,0 VAJ01
,ISNULL(B.VBJ05, a.VBI35) VBJ05 --本次数次
,B.VBJ06 --执行摘要
,B.BCE03A --执行人
,ISNULL(B.VBJ08, @NOW) VBJ08 --执行时间
,B.BCE03B --登记人
,ISNULL(B.VBJ10, @NOW) VBJ10 --登记时间
,B.VBJ11 --记录哪几组医嘱一起执行的
,B.BCE03C --接单人
,B.BCE03D --配药人
,B.VBJ14 --保存本次执行一共有几组
,B.VBJ15 --保存次序
,B.VBJ16 --本组的滴速
,B.VBJ17 --本组的滴系数
,B.VBJ18 --药品的液体量
,B.VBJ19 --执行完需要用的时间，单位秒
,B.VBJ20 --提前多少时间进行提醒，单位秒,-1表示不提醒，0表示到期提醒，>0表示提前的时间
,B.VBJ21 --接单护士填写药品执行时的相关说明，如先输，避光
,a.VBI10,a.VBI35
from V_VBI_2 A
LEFT JOIN VBJ1 B with(nolock) ON A.VAF01=B.VAF01 AND A.VBI03=B.VBI03 and VBJ05>0 and VBJ05<=a.VBI35
WHERE A.VBI01 = @VBI01
order by b.VBJ01 desc', '2017-12-22 18:01:43', '(00702)付颖奇', 0, 0)
else
print 'SYS_Scripts.id=2014 已经存在.'
GO
If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 2014)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=2014
GO
--增加分类
GO
IF not EXISTS(SELECT * FROM SYS_ScriptCategories WHERE id=1)
insert into SYS_ScriptCategories(ID,Code,Name,ComponentID,xRowNum,ParentID,xLevel,Description) Values(1, 'SC1', 'HOMaster 基础管理', 1, 0, 0, 0, Null)
else
print 'SYS_ScriptCategories.id=1 已经存在.'
GO
If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 1)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=1
GO
--删除旧记录
delete from SYS_Scripts where id=2081 --删除脚本内容
GO
GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=2081)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode,Custom) Values(2081, 1, 'Sql_数据完整性校验', Null, '基础数据完整合法性校验', 1, 0, '说明：', 1, 'select *
from (
SELECT cast(''药品品种完整性校验'' as varchar(128)) FName,cast(''药品品种和药品品种特性表不一致'' as varchar(128)) FCaption, cast(2083 as int) FID
,cast(''说明：用于校验药品品种和药品品种特性表中的数据是否存在非法数据'' as varchar(512)) FItem
union all
select ''药品规格完整性校验'',''药品规格和药品目录表不一致'',2084,''说明：用于校验药品规格和药品目录表中的数据是否存在非法数据''
union all
SELECT ''判断规格是否对应有药品品种'',''未对应品种的规格记录'',2082,''''
union all
select ''收费项目收入归类校验'',''收费项目未对应收入项目'',2085,''说明：校验哪些收费项目没有对应收入项目''
union all
select ''药品剂型合法性校验'',''药品剂型合法性校验'', 8122, ''说明：药品剂型合法性校验''
union all
select ''费用明细记录完整性校验'',''校验收费明细记录完整性'',2090,''说明：校验收费明细完整性''
) s', '2017-12-12 15:49:30', '(8058)高瑜', 0, 0)
else
print 'SYS_Scripts.id=2081 已经存在.'
GO
If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 2081)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=2081
GO
--增加分类
GO
IF not EXISTS(SELECT * FROM SYS_ScriptCategories WHERE id=1)
insert into SYS_ScriptCategories(ID,Code,Name,ComponentID,xRowNum,ParentID,xLevel,Description) Values(1, 'SC1', 'HOMaster 基础管理', 1, 0, 0, 0, Null)
else
print 'SYS_ScriptCategories.id=1 已经存在.'
GO
If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 1)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=1
GO
--删除旧记录
delete from SYS_Scripts where id=2124 --删除脚本内容
GO
GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=2124)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode,Custom) Values(2124, 32, 'Sql_门诊拼音码检索病', Null, '门诊检索条件按拼音码检索病人', 1, 0, '
DECLARE @aDate Datetime, @aday int , @lPara varchar(20),@BAS02 varchar(20),@lBCE01 int
DECLARE @lBCK01 int, @lDt1 varchar(30), @lDt2 varchar(30), @lTxt varchar(100),@blp109 int,@allDept tinyint
set @allDept=%d
set @aDay = %d
SET @lBCK01 = %d
Set @lDt1 = Convert(varchar(10),Cast(%s as datetime),21)+'' 00:00:00.000''
Set @lDt2 = Convert(varchar(10),Cast(%s as datetime),21)+'' 23:59:59.999''
set @ltxt = %s
set @lPara = %s
set @BAS02 = %s
set @lBCE01 = %d
set @blp109=0
if exists(select * from blp1 where blp02=109)
set @blp109=1
if @aDay = 0
set @aDate = ''1899-12-30''
else
Set @aDate = Convert(varchar(10),DateAdd(Day,-(@aDay-1),GetDate()),21)
set @aDate = getdate()
declare @kbmBCK table(BCK01 int)
insert into @kbmBCK(BCK01)
SELECT a.BCK01
FROM BCK1 a
Where EXISTS(SELECT b.* FROM BAZ1 b
WHERE a.BCK01=b.BCK01 AND b.BAU01 = ''01'' AND b.ACF01 IN(1,3))
AND EXISTS(SELECT c.* FROM BDR1 c WHERE a.BCK01 = c.BCK01 AND c.BCE01 = @lBCE01 )
--b.BCK01A = @lBCK01
select VAC01,VAA01,VAA02,VAA03,VAA05,ABW02,ABBRP,Agep,BCE03A,VAC36,VAC34,Fstate,VCB04,BCK03,BCK01A,IAK05,BDP02
from (
SELECT a.VAA01,a.VAA03,a.VAA05,e.ABW02,a.ABBRP
,Agep = CASE WHEN isnull(a.VAA10,0)=0 THEN NULL else (ltrim(str(a.VAA10))+f.AAU02) END
,b.VAC01,b.BCE03A,b.VAC36,b.VAC34, ''候诊'' Fstate,a.VAA02,h.VCB04,ISNULL(b.BCK01A,0) BCK01A,b1.BCK03,a.IAK05,b.BDP02
FROM VAA1 AS a
JOIN VAC1 AS b ON a.VAA01 = b.VAA01
left join BCK1 b1 on b1.BCK01 = b.BCK01A
LEFT JOIN ABW1 AS e ON a.ABW01 = e.ABW01
LEFT JOIN AAU1 AS f ON a.AAU01 = f.AAU01
LEFT JOIN VCB1 h ON a.VBU01 = h.VBU01 and h.VBU01>0
WHERE b.VAC09 <> -3 and b.VAC34 = 0 and b.VAC51 = 0 AND b.VAC45 = 1 and @aDate <= b.VAC72 and %s @ltxt
AND ((@lPara = ''05'' or (@lPara = ''06'' and (b.BCE01A = @lBCE01 or ISNULL(b.BCE01A,0)=0)))
or (@lPara not in(''05'',''06'') and exists(select * from @kbmBCK m where m.BCK01 = b.BCK01A)
and ((@lPara = ''03'')or(@lPara = ''01'' and (b.BCE01A = @lBCE01 or ISNULL(b.BCE01A,0)=0))
or(@lPara = ''02'' and b.BAS02A = @BAS02)or (@lPara = ''04'' and b.BCE01A = @lBCE01) ) ) )
union all
SELECT a.VAA01,a.VAA03,a.VAA05,e.ABW02,a.ABBRP
,Agep = CASE WHEN isnull(a.VAA10,0)=0 THEN NULL else (ltrim(str(a.VAA10))+f.AAU02) END
,b.VAC01,b.BCE03A,b.VAC36,b.VAC34,case b.VAC34 when 1 then ''就诊'' else ''已诊'' end
,a.VAA02,h.VCB04,ISNULL(b.BCK01A,0) BCK01A,b1.BCK03,a.IAK05,b.BDP02
FROM VAA1 AS a
JOIN VAC1 AS b ON a.VAA01 = b.VAA01
left join VCJ1 c on c.VAA07 = b.VAC01
left join BCK1 b1 on b1.BCK01 = b.BCK01A
LEFT JOIN ABW1 AS e ON a.ABW01 = e.ABW01
LEFT JOIN AAU1 AS f ON a.AAU01 = f.AAU01
LEFT JOIN VCB1 h ON a.VBU01 = h.VBU01 and h.VBU01>0
WHERE b.VAC09 <> -3 and b.VAC34 >= 1 AND ((@lPara = ''05'' or (@lPara = ''06'' and (b.BCE01A = @lBCE01 or ISNULL(b.BCE01A,0)=0)))
or (@lPara not in(''05'',''06'') AND exists(select * from @kbmBCK m where m.BCK01 = b.BCK01A)
and (@allDept=1 or (@allDept=0 and b.BCE01A=@lBCE01))))
and b.VAC45=1 and %s @ltxt
AND (@blp109=1 or (@blp109=0 and ((b.VAC35 >= @lDt1 and b.VAC35 <= @lDt2) or (c.VCJ09 >= @lDt1 and c.VCJ09 <= @lDt2))) ) ) gorder by VAC36 desc
', 1, 'DECLARE @aDate Datetime, @aday int , @lPara varchar(20),@BAS02 varchar(20),@lBCE01 int
DECLARE @lBCK01 int, @lDt1 varchar(30), @lDt2 varchar(30), @lTxt varchar(100)
set @aDay = %d
SET @lBCK01 = %d
Set @lDt1 = Convert(varchar(10),Cast(%s as datetime),21)+'' 00:00:00.000''
Set @lDt2 = Convert(varchar(10),Cast(%s as datetime),21)+'' 23:59:59.999''
set @ltxt = %s
set @lPara = %s
set @BAS02 = %s
set @lBCE01 = %d
set @aDate = getdate()
declare @kbmBCK table(BCK01 int)
insert into @kbmBCK(BCK01)
SELECT a.BCK01
FROM BCK1 a
Where EXISTS(SELECT b.* FROM BAZ1 b
WHERE a.BCK01=b.BCK01 AND b.BAU01 = ''01'' AND b.ACF01 IN(1,3))
AND EXISTS(SELECT c.* FROM BDR1 c WHERE a.BCK01 = c.BCK01 AND c.BCE01 = @lBCE01 )
--b.BCK01A = @lBCK01
select VAC01,VAA01,VAA02,VAA03,VAA05,ABW02,ABBRP,Agep,BCE03A,VAC36,VAC34,Fstate,VCB04,BCK01A,BCK03,IAK05
from (
SELECT a.VAA01,a.VAA03,a.VAA05,e.ABW02,a.ABBRP
,Agep = CASE WHEN isnull(a.VAA10,0)=0 THEN NULL else (ltrim(str(a.VAA10))+f.AAU02) END
,b.VAC01,b.BCE03A,b.VAC36,b.VAC34, ''候诊'' Fstate,a.VAA02,h.VCB04,ISNULL(b.BCK01A,0) BCK01A,b1.BCK03,a.IAK05
FROM VAA1 AS a
JOIN VAC1 AS b ON a.VAA01 = b.VAA01
left join BCK1 b1 on b1.BCK01 = b.BCK01A
LEFT JOIN ABW1 AS e ON a.ABW01 = e.ABW01
LEFT JOIN AAU1 AS f ON a.AAU01 = f.AAU01
LEFT JOIN VBU1 h ON a.VBU01 = h.VBU01
WHERE b.VAC09 <> -3 and b.VAC34 = 0 and b.VAC51 = 0 AND b.VAC45 = 1 and @aDate <= b.VAC72 and %s = @ltxt
AND ((@lPara = ''05'' or (@lPara = ''06'' and (b.BCE01A = @lBCE01 or ISNULL(b.BCE01A,0)=0)))
or (@lPara not in(''05'',''06'') and exists(select * from @kbmBCK m where m.BCK01 = b.BCK01A)
and ((@lPara = ''03'')or(@lPara = ''01'' and (b.BCE01A = @lBCE01 or ISNULL(b.BCE01A,0)=0))
or(@lPara = ''02'' and b.BAS02A = @BAS02)or (@lPara = ''04'' and b.BCE01A = @lBCE01) ) ) )
union all
SELECT a.VAA01,a.VAA03,a.VAA05,e.ABW02,a.ABBRP
,Agep = CASE WHEN isnull(a.VAA10,0)=0 THEN NULL else (ltrim(str(a.VAA10))+f.AAU02) END
,b.VAC01,b.BCE03A,b.VAC36,b.VAC34,case b.VAC34 when 1 then ''就诊'' else ''已诊'' end
,a.VAA02,h.VCB04,ISNULL(b.BCK01A,0) BCK01A,b1.BCK03 ,a.IAK05
FROM VAA1 AS a
JOIN VAC1 AS b ON a.VAA01 = b.VAA01
left join VCJ1 c on c.VAA07 = b.VAC01
left join BCK1 b1 on b1.BCK01 = b.BCK01A
LEFT JOIN ABW1 AS e ON a.ABW01 = e.ABW01
LEFT JOIN AAU1 AS f ON a.AAU01 = f.AAU01
LEFT JOIN VBU1 h ON a.VBU01 = h.VBU01
WHERE b.VAC09 <> -3 and b.VAC34 >= 1 AND ((@lPara = ''05'' or (@lPara = ''06'' and (b.BCE01A = @lBCE01 or ISNULL(b.BCE01A,0)=0)))
or (@lPara not in(''05'',''06'') AND exists(select * from @kbmBCK m where m.BCK01 = b.BCK01A)))
and b.VAC45=1 and %s = @ltxt
AND ((b.VAC35 >= @lDt1 and b.VAC35 <= @lDt2) or (c.VCJ09 >= @lDt1 and c.VCJ09 <= @lDt2)) ) g', '2017-12-12 16:59:16', '(8088)王海涛', 0, 0)
else
print 'SYS_Scripts.id=2124 已经存在.'
GO
If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 2124)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=2124
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[HORate_Auto_Drug]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--==============================================================================
--Author <...8065>
--Alter Date <...2017.12.21>
--Description<...门诊结帐对于部分退费已发药药品自动发药>
--==============================================================================
ALTER PROC [HORate_Auto_Drug] @VAK01 int ,
@BCE01 int ,
@BCE03 varchar(20),
@TerminalNO varchar(50)
AS
DECLARE @i int ,
@ErrId int ,
@VAA01 int ,
@VAA07 int ,
@BCK01 int ,
@Count int ,
@PVAK01 int ,
@DeptId int ,
@VAA05 varchar(20) ,
@Ratifier varchar(20) ,--批准人
@Sender varchar(20),--发药人
@VAJ01B int ,
@VAJ01Part int
SELECT @VAA01 = VAA01 , @VAA07 = VAA07 , @PVAK01 = VAK28 FROM VAK1 with(nolock) WHERE VAK01 = @VAK01
SELECT VAK01 INTO #tmpVAK1
FROM VAK1 with(nolock) WHERE VAK28 = @PVAK01
SELECT * INTO #tmpVAI1
FROM (SELECT VAI01
FROM VAI1 a with(nolock)
JOIN #tmpVAK1 b ON b.VAK01 = a.VAK01
WHERE a.VAI17 = 2) a
IF NOT EXISTS(SELECT * FROM #tmpVAI1)
BEGIN
RETURN 1
END
SELECT * INTO #tmpVAJ1
FROM (SELECT a.VAJ01 , a.VAJ01B , a.VAI01 , a.BCK01D
FROM VAJ1 a with(nolock)
JOIN #tmpVAI1 b ON b.VAI01 = a.VAI01
WHERE a.BDN01 <= ''3'' AND a.VAJ48 = 0 AND a.VAJ53 = 0) m
IF NOT EXISTS(SELECT * FROM #tmpVAJ1 )
BEGIN
RETURN 1
END
/*SELECT IDENTITY(int,1,1) AS ID , BCK01 into #tmpBCK1
FROM (SELECT DISTINCT c.BCK01
FROM #tmpVAI1 a
JOIN #tmpVAJ1 b ON b.VAI01 = a.VAI01
JOIN VBY1 c with(nolock) on c.VAJ01 = b.VAJ01B
where c.VBY09 = 1 ) a*/
Create table #tmpBCK1
(
ID int identity( 1 , 1) ,
BCK01 int
)
INSERT INTO #tmpBCK1(BCK01)
SELECT DISTINCT c.BCK01
FROM #tmpVAI1 a
JOIN #tmpVAJ1 b ON b.VAI01 = a.VAI01
JOIN DPD2 c with(nolock) on c.VAJ01 = b.VAJ01B
If NOT EXISTS(SELECT * FROM #tmpBCK1 )
BEGIN
INSERT INTO #tmpBCK1(BCK01)
SELECT DISTINCT c.BCK01
FROM #tmpVAI1 a
JOIN #tmpVAJ1 b ON b.VAI01 = a.VAI01
JOIN DPD2_2 c with(nolock) on c.VAJ01 = b.VAJ01B
END
IF NOT EXISTS(SELECT * FROM #tmpBCK1 )
BEGIN
RETURN 1
END
SELECT @VAA05 = VAA05 FROM VAA1 with(nolock) WHERE VAA01 = @VAA01
SELECT TOP 1 @DeptId = BCK01B FROM VAJ1 with(nolock) WHERE VAK01 = @PVAK01
BEGIN TRAN
Declare @VAI01C int,
@VAI01 int
SELECT @i = 0 , @Count = COUNT(1) FROM #tmpBCK1
--按当前部分退费药品自动调用药房发药
WHILE @i < @Count
BEGIN
SELECT @BCK01 = BCK01 FROM #tmpBCK1 WHERE [ID] = @i + 1
SELECT top 1 @VAJ01B = VAJ01B , @VAI01 = VAI01 from #tmpVAJ1 where BCK01D = @BCK01
If exists(select * from VBY1 where VAJ01 = @VAJ01B and VBY09 = 1)
SET @VAJ01Part = @VAJ01B
ELSE BEGIN
SELECT @VAI01C = VAI01C from V_VAI_1 with(nolock) where VAI01 = @VAI01
SELECT top 1 @VAJ01Part = VAJ01
from V_VAJ_1 with(nolock)
where VAJ01B = @VAJ01B and VAI01 = @VAI01C
order by VAJ01 Desc
END
/*SELECT TOP 1 @Ratifier = BCE03B , @Sender = BCE03B
FROM VBY1 a with(nolock)
JOIN #tmpVAJ1 b ON b.VAJ01B = a.VAJ01
WHERE a.BCK01 = @BCK01 */
SELECT TOP 1 @Ratifier = BCE03B , @Sender = BCE03B
FROM VBY1 a with(nolock)
WHERE a.VAJ01 = @VAJ01Part and a.BCK01 = @BCK01
--调用药房发药过程
EXEC @ErrId = DPS_ExportForm_Update3 @BCK01 , @PVAK01 , @DeptId , @VAA05 , @Ratifier , @Sender , @BCE03 , @BCE01 , @TerminalNO
IF @ErrId > 0
BEGIN
If @ErrId > 0 --如果发生错误则回滚数据
BEGIN
IF @@TRANCOUNT > 0 ROLLBACK TRAN
RAISERROR(''已发药药品部分退费自动发药错误，请去药房发药，错误号为: %d.'', 16, 1, @@ERROR) WITH NOWAIT
Return 1
END
END
SET @i = @i + 1
END
COMMIT TRAN
--删除临时表
IF Object_id(''tempdb..#tmpVAK1'') IS NOT NULL DROP TABLE #tmpVAK1
IF Object_id(''tempdb..#tmpVAI1'') IS NOT NULL DROP TABLE #tmpVAI1
IF Object_id(''tempdb..#tmpVAJ1'') IS NOT NULL DROP TABLE #tmpVAJ1
IF Object_id(''tempdb..#tmpBCK1'') IS NOT NULL DROP TABLE #tmpBCK1
'
END
GO
--增加分类
GO
IF not EXISTS(SELECT * FROM SYS_ScriptCategories WHERE id=1)
insert into SYS_ScriptCategories(ID,Code,Name,ComponentID,xRowNum,ParentID,xLevel,Description) Values(1, 'SC1', 'HOMaster 基础管理', 1, 0, 0, 0, Null)
else
print 'SYS_ScriptCategories.id=1 已经存在.'
GO
If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 1)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=1
GO
--删除旧记录
delete from SYS_Scripts where id=878 --删除脚本内容
GO
GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=878)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode,Custom) Values(878, 33, 'ShowList_VAE1', Null, '显示住院病人列表', 1, 1, Null, 1, '
DECLARE @lBCK01 int, @lType int, @lDt1 varchar(30),@lDt2 varchar(30), @lInput tinyint,@para396 varchar(10),@param406 varchar(10)
SET @lBCK01 = %d
SET @lType = %d
Set @lDt1 = Convert(varchar(10),Cast(%s as datetime),21)+'' 00:00:00.000''
Set @lDt2 = Convert(varchar(10),Cast(%s as datetime),21)+'' 23:59:59.999''
set @lInput = %d
set @param406 = dbo.GetSysParamValue(100,105003,406)
declare @kbmBCV table(BCK01A int,BCK01B int)
declare @kbmVAP table(VAE01 int,fCount int)
if OBJECT_ID(''tempdb..#kbmvbo'') is not null drop table #kbmvbo
if OBJECT_ID(''tempdb..#kbmvae'') is not null drop table #kbmvae
IF Object_id(''tempdb..#kbmVAO'') IS NOT NULL DROP TABLE #kbmVAO
select a.VAA07,min(a.VAO01) VAOid
into #kbmVAO
from VAO2 a with(nolock)
join VAE1 b with(nolock) on b.VAE01 = a.VAA07
where a.ACF01 = 2 and b.VAE44 >= 2 and b.VAE44 <= 3 and a.VAF01 = 0 and a.VAO11 = 2
and ((@lType = 1 and b.BCK01D = @lBCK01)or(@lType = 2 and b.BCK01C = @lBCK01))
group by a.VAA07
set @para396 = dbo.GetSysParamValue(100,105001,396)
insert into @kbmBCV(BCK01A,BCK01B)
select BCK01A,BCK01B from BCV1 where BCK01A = @lBCK01
select v.VAA07,v.BCK01B into #kbmvbo
from VBO1 v with(nolock)
where @lType = 2 and v.VBO04 IS NULL AND v.VBO06 = 3 AND v.VBO19 IS NULL
and EXISTS(SELECT m.* FROM @kbmBCV m WHERE m.BCK01A = @lBCK01 AND v.BCK01B = m.BCK01B)
select a.*,b.VAA03,a.VAE94 VAA04,a.VAE95 VAA05,a.VAE98 VAA15,a.VAE96 ABW01
,b.VAA02,0 BDP05,d.VAM27,d.VAM31,b.VBU01,e1.VAO15,b.VAA82
,Case when a.VAE96=''1'' then ''男'' when a.VAE96=''2'' then ''女'' else ''未知'' end ABW02
into #kbmvae
from VAE1 a with(nolock)
join VAA1 b with(nolock) on b.VAA01 = a.VAA01
left join VAM1 d with(nolock) on d.VAA07 = a.VAE01
left join #kbmvbo v on a.VAE44 = 3 and v.VAA07 = a.VAE01
left join #kbmVAO e on e.VAA07 = a.VAE01
left join VAO2 e1 with(nolock) on e1.VAO01 = e.VAOid
WHERE (@lType=1 and ((a.VAE44 in (2,4) and a.BCK01D = @lBCK01)
or (@lInput = 1 and a.VAE44 = 1 and a.BCK01D = @lBCK01)
or (a.VAE44 = 5 and a.BCK01D = @lBCK01 and a.VAE26 >= @lDt1 And a.VAE26 <= @lDt2 )))
OR (@lType=2 and ((a.VAE44=1 and EXISTS(SELECT m.* FROM @kbmBCV m WHERE m.BCK01A = @lBCK01 AND a.BCK01B = m.BCK01B))
OR (a.VAE44 in (2,3,4) and a.BCK01C = @lBCK01)
OR (a.VAE44 = 3 and EXISTS(SELECT m.* FROM @kbmBCV m WHERE m.BCK01A = @lBCK01 AND v.BCK01B = m.BCK01B))
OR (a.VAE44 = 5 and a.BCK01C = @lBCK01 and a.VAE26 >= @lDt1 And a.VAE26 <= @lDt2 )
)
)
update a set a.BDP05=isnull(b.BDP05,0) from #kbmvae a join BDP1 b with(nolock) on b.BDP02=a.BDP02
insert into @kbmVAP(VAE01,fCount)
select a.VAE01,COUNT(1) fCount
from #kbmvae a
join VAP1 b with(nolock) on b.VAA07 = a.VAE01
where a.VAE44 >= 2 and a.VAE44 <= 4
and ((@ltype = 1 and a.BCK01D = @lBCK01)or(@ltype = 2 and a.BCK01C = @lBCK01))
group by a.VAE01
SELECT a.VAA01,a.VAA02,a.VAA03,a.VAA04,a.VAA05
,a.VAA15,a.ABW02,a.VAE01,a.VAE02,a.ABK02
,Agep = CASE WHEN isnull(a.VAE46,0)=0 THEN NULL else (ltrim(str(a.VAE46))+c.AAU02) END
,case when isnull(v2.BCQ01,0) > 0 and a.BCK01C <> v2.BCK01A then
(case when a.ABV01 = ''99'' then ''(暂离)'' else '''' end) + a.BCQ04B+''(借: ''+v3.BCK03+'')''
else (case when a.ABV01 = ''99'' then ''(暂离)'' else '''' end) + a.BCQ04B end as BCQ04B, v2.BCQ03
,a.AAG01,e.AAG02,a.BDP02,a.ABC02,f.ABJ02, a.VAE11,a.VAE26,a.BCE03C,a.BCE02C ,a.BCE03B
,FStatus = CASE WHEN a.VAE44=2 THEN ''2-在院病人''
when a.VAE44=1 then ''0-入院病人''
WHEN a.VAE44=3 and a.BCK01C <> @lBCK01 THEN ''1-转入病人: ''+g.BCK03
when a.VAE44 = 3 and (a.BCK01C = @lBCK01)and not exists(select * from @kbmBCV s join @kbmBCV s1 on s.BCK01A = s1.BCK01A where s.BCK01B = a.BCK01D and s1.BCK01B = v.BCK01B )
then ''3-转出病人: ''+v1.BCK03
when a.VAE44 = 3 and (a.BCK01C = @lBCK01)and exists(select * from @kbmBCV s join @kbmBCV s1 on s.BCK01A = s1.BCK01A where s.BCK01B = a.BCK01D and s1.BCK01B = v.BCK01B )
then ''1-转出转入：'' + v1.BCK03
when a.VAE44 = 4 then ''4-预出院病人''
WHEN a.VAE44 = 5 THEN ''5-出院病人'' END
,FVAE04 = case when a.VAE04=1 then ''门诊留观病人''
WHEN a.VAE04 = 2 THEN ''住院留观病人'' else ''住院病人'' end
,a.VAE04,a.VAE44,a.VAE80, BCK03 = CASE WHEN a.VAE44 = 3 THEN v1.BCK03 ELSE g.BCK03 END
,e.AAG03,a.BCK01D,a.BCK01C,v1.BCK01 AS BCK01V,a.BCK01A,a.BCK01B
,case when a.VAE44 < 2 then g1.BCK03 else g.BCK03 end as FBCK03
,case when exists(select * from @kbmBCV s join @kbmBCV s1 on s.BCK01A = s1.BCK01A where s.BCK01B = a.BCK01D and s1.BCK01B = v.BCK01B ) then 1 else 0 end FSign
,case when ISNULL(s.fCount,0) = 0 then '''' else CONVERT(varchar(10),s.fCount)+''个小孩'' end FVAP,h.VBM08,h.VBM09,h.VBM12
,h.VBM04,h.VBM05,isnull(h.VBM04,0)+isnull(h1.VBU14,0)-isnull(h.VBM05,0)+isnull(h.VBM08,0)+isnull(h.VBM12,0) as balance
,n.ABO02,ISNULL(c5.VCN13,9) VCN13,isnull(c5.VCN19,0) VCN19,c5.VCN08,c6.VCS07,a.BDP05,a.VAM27,a.VAM31,a.VAE88
,h.VBM13,h.BEP07,a.VAO15,a.VAA82
FROM #kbmvae a
LEFT JOIN AAU1 c with(nolock) ON c.AAU01 = a.AAU01
LEFT JOIN BBY1 e1 with(nolock) ON a.AAG01 = e1.BBY01
LEFT JOIN AAG1 e with(nolock) ON e1.BCF01 = e.AAG01
LEFT JOIN ABJ1 f with(nolock) ON a.ABJ01 = f.ABJ01
LEFT JOIN BCK1 g with(nolock) ON (a.BCK01c = g.BCK01 and @lType=1) or (@lType=2 and a.BCK01d = g.BCK01)
left join BCK1 g1 with(nolock) on a.BCK01B = g1.BCK01
LEFT JOIN #kbmvbo v ON a.VAE44 = 3 AND a.VAE01=v.VAA07
LEFT JOIN BCK1 v1 with(nolock) ON v.BCK01B = v1.BCK01
LEFT JOIN BCQ1 v2 with(nolock) ON a.VAA01 = v2.VAA01 and (v2.BCQ13 = 1 or v2.BCQ13 = 3) and a.BCQ04B = v2.BCQ04
left join BCK1 v3 with(nolock) on v2.BCK01A = v3.BCK01
left join @kbmVAP s on s.VAE01 = a.VAE01
left join VBM2 h with(nolock) on h.VAA07 = a.VAE01 and h.ACF01 = 2
left join VBU1 h1 with(nolock) on h1.VBU01 = a.VBU01 and h1.VBU21 = 1 and @param406 <> ''1''
left join ABO1 n with(nolock) on n.ABO01 = a.VAE22
left join VCN1 c5 with(nolock) on c5.VAA07 = a.VAE01 and c5.VCN33=1
left join VCS1 c6 with(nolock) on c6.VCS04=c5.VCN01
WHERE (@lType=1 and ((a.VAE44 in (2,4) and a.BCK01D = @lBCK01)
or (@lInput = 1 and a.VAE44 = 1 and a.BCK01D = @lBCK01)
or (a.VAE44 = 5 and a.BCK01D = @lBCK01 and a.VAE26 >= @lDt1 And a.VAE26 <= @lDt2 )))
OR (@lType=2 and ((a.VAE44=1 and ((@para396<>''1'' and EXISTS(SELECT m.* FROM @kbmBCV m WHERE m.BCK01A = @lBCK01 AND a.BCK01B = m.BCK01B) )
or(@para396=''1'' and (a.BCK01A = @lBCK01 or (ISNULL(a.BCK01A,0)=0
and EXISTS(SELECT m.* FROM @kbmBCV m WHERE m.BCK01A = @lBCK01 AND a.BCK01B = m.BCK01B)) ) )) )
OR (a.VAE44 in (2,3,4) and a.BCK01C = @lBCK01)
OR (a.VAE44 = 3 and EXISTS(SELECT m.* FROM @kbmBCV m WHERE m.BCK01A = @lBCK01 AND v.BCK01B = m.BCK01B))
OR (a.VAE44 = 5 and a.BCK01C = @lBCK01 and a.VAE26 >= @lDt1 And a.VAE26 <= @lDt2 )
)
)
ORDER BY v2.Rownr, a.BCQ04B
if OBJECT_ID(''tempdb..#kbmvbo'') is not null drop table #kbmvbo
if OBJECT_ID(''tempdb..#kbmvae'') is not null drop table #kbmvae
IF Object_id(''tempdb..#kbmVAO'') IS NOT NULL DROP TABLE #kbmVAO
', '2017-11-18 09:46:44', '(8058)高瑜', 0, 0)
else
print 'SYS_Scripts.id=878 已经存在.'
GO
If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 878)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=878
GO
--增加分类
GO
IF not EXISTS(SELECT * FROM SYS_ScriptCategories WHERE id=1)
insert into SYS_ScriptCategories(ID,Code,Name,ComponentID,xRowNum,ParentID,xLevel,Description) Values(1, 'SC1', 'HOMaster 基础管理', 1, 0, 0, 0, Null)
else
print 'SYS_ScriptCategories.id=1 已经存在.'
GO
If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 1)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=1
GO
--删除旧记录
delete from SYS_Scripts where id=1669 --删除脚本内容
GO
GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=1669)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode,Custom) Values(1669, 47, 'SQL_检索待手术病人', '检索待手术病人', '检索待手术病人--未完成病人,包括门诊病人与住院病人', 1, 0, Null, 1, 'Declare @lBCK01 int, @lMember tinyint, @aDate datetime, @aDay int
Set @lBCK01 = %d
set @lMember = %d
set @aDay = cast(dbo.GetSysParamValue(100,106004,1) as int)
if @aDay = 0
set @aDate = Convert(varchar(10),DateAdd(Day,-60,GetDate()),21)
else
Set @aDate = Convert(varchar(10),DateAdd(Day,-@aDay,GetDate()),21)
--说明：上面两个参数及名称是固定的不能修改，其他条件可以随意加，包括下面的字段也不能改变只能增加不能减少
IF object_id(''tempdb..#tmp_VAT'') IS NOT NULL DROP TABLE #tmp_VAT
IF object_id(''tempdb..#tmp_VAF1'') IS NOT NULL DROP TABLE #tmp_VAF1
IF object_id(''tempdb..#tmp_VAF2'') IS NOT NULL DROP TABLE #tmp_VAF2
SELECT a.VAT01 , a.VAT04 , a.BEE01 , a.BEE03 ,a.VAT33 , a.BCK01 , a.BBX01
, a.VAA01 , a.VAA07 , a.ACF01 , a.VAT08,a.VAT09,a.VAF01,a.VCY01
, ( select Case when MAX(Feeflag)>0 then 1 else 0 end as Feeflag from
(select top 1 1 as Feeflag from VAI1 T2 where t2.VAA01=a.VAA01 and T2.VAI16<2 and T2.CBM01=a.vat01 and T2.VAI18>=5
union all
select top 1 1 as Feeflag from VAI2 T2 where t2.VAA01=a.VAA01 and T2.VAI16<2 and T2.CBM01=a.vat01 and T2.VAI18>=5 ) t3
) as Feeflag
into #tmp_VAT
FROM VAT1 a
where a.VAT04 <= 4 and a.VAT08 >= @aDate
select v.bda01,v.vaf35,v.VAF22,v.BCE03A,v.VAF01,v.CBM01,v.BBX01,v.BCK01A,v.BCK01B,V.BCK01D,v.VAF01A,v.VAA01,v.VAF06,v.VAF47,v.VAF23,v.BCE01A into #tmp_VAF1
from VAF1 v
where v.VAF47 >= @aDate and v.VAF10 >=8 and v.VAF10 <= 9 and (v.bck01b=@lBCK01 or v.bck01d=@lBCK01)
select v.bda01,v.vaf35,v.VAF22,v.BCE03A,v.VAF01,v.CBM01,v.BBX01,v.BCK01A,v.BCK01B,V.BCK01D,v.VAF01A,v.VAA01,v.VAF06,v.VAF47,v.VAF23,v.BCE01A into #tmp_VAF2
from VAF2 v
where v.VAF47 >= @aDate and v.VAF10 >=8 and v.VAF10 <= 9 and (v.bck01b=@lBCK01 or v.bck01d=@lBCK01)
SELECT a.VAT01 , a.VAT04 ,b.VAA03, b.VAA04, b.VAA05 , c.ABW02 , b.VAA10 , a.BEE01 , a.BEE03 ,b.ABBRP,B.ABBRW
,a.VAT33 ,d.BCQ04B ,f.BAK05 ,((case when v.VAF35 = 1 then ''(紧急)'' else '''' end)+isnull(g.BBX05,v.VAF22)) BBX05
, m.BBX05 MBBX05 , n.BCK03 , a.BCK01 , a.BBX01
, a.VAA01 , a.VAA07 , a.ACF01 , d.VAE44,a.VAT08,v.BCE03A,v.VAF01,v.CBM01,a.VAT09,isnull(b.VBU01,0) VBU01
,v.BCE01A,k.BCE01 BCE01C,a.FeeFlag
FROM #tmp_VAT a
JOIN VAA1 b with(nolock) ON a.VAA01 = b.VAA01
JOIN VAE1 d with(nolock) ON a.VAA07 = d.VAE01
JOIN #tmp_VAF2 v ON a.VAF01 = v.VAF01
left JOIN BBX1 g with(nolock) ON v.BBX01 = g.BBX01
LEFT JOIN BCK1 n with(nolock) ON v.BCK01A = n.BCK01
LEFT JOIN BBX1 m with(nolock) ON a.BBX01 = m.BBX01
LEFT JOIN VAO2 e with(nolock) ON a.VAF01 = e.VAF01 AND e.VAO11 = 8
LEFT JOIN BAK1 f with(nolock) ON e.BAK01A = f.BAK01
LEFT JOIN ABW1 c with(nolock) ON b.ABW01 = c.ABW01
LEFT JOIN BCE1 k with(nolock) on k.BCE02=d.BCE02C
where a.VAT04 <= 4 and d.VAE44 >= 1 and d.VAE44<= 5 and a.VAT08 >= @aDate
and v.BCK01B = @lBCK01
union all
SELECT a.VAT01 , a.VAT04 ,b.VAA03, b.VAA04, b.VAA05 , c.ABW02 , b.VAA10 , a.BEE01 , a.BEE03 ,b.ABBRP,B.ABBRW
,a.VAT33 ,d.BCQ04B ,f.BAK05 ,((case when v.VAF35 = 1 then ''(紧急)'' else '''' end)+isnull(g.BBX05,v.VAF22)) BBX05
, m.BBX05 MBBX05 , n.BCK03 , a.BCK01 , a.BBX01
, a.VAA01 , a.VAA07 , a.ACF01 , d.VAE44,a.VAT08,v.BCE03A,v.VAF01,v.CBM01,a.VAT09,isnull(b.VBU01,0) VBU01
,v.BCE01A,k.BCE01 BCE01C,a.FeeFlag
FROM #tmp_VAT a
JOIN VAA1 b with(nolock) ON a.VAA01 = b.VAA01
JOIN VAE1 d with(nolock) ON a.VAA07 = d.VAE01
JOIN #tmp_VAF2 v ON a.VAF01 = v.VAF01
left JOIN BBX1 g with(nolock) ON v.BBX01 = g.BBX01
LEFT JOIN BCK1 n with(nolock) ON v.BCK01A = n.BCK01
LEFT JOIN BBX1 m with(nolock) ON a.BBX01 = m.BBX01
LEFT JOIN VAO2 e with(nolock) ON a.VAF01 = e.VAF01 AND e.VAO11 = 8
LEFT JOIN BAK1 f with(nolock) ON e.BAK01A = f.BAK01
LEFT JOIN ABW1 c with(nolock) ON b.ABW01 = c.ABW01
LEFT JOIN BCE1 k with(nolock) on k.BCE02=d.BCE02C
where a.VAT04 <= 4 and d.VAE44 >= 1 and d.VAE44<= 5 and a.VAT08 >= @aDate
and v.BCK01B <> @lBCK01 and exists(select vaf01 from #tmp_VAF2 v1 where v1.VAF01A = v.VAF01 and v1.BCK01B = @lBCK01)
Union All
SELECT a.VAT01 , a.VAT04 ,b.VAA03, b.VAA03 VAA04, b.VAA05 , c.ABW02 , b.VAA10 , a.BEE01 , a.BEE03 ,b.ABBRP,B.ABBRW
,a.VAT33 ,BCQ04B='''' ,f.BAK05 , g.BBX05 , m.BBX05 MBBX05 , n.BCK03 , a.BCK01 , a.BBX01
, a.VAA01 , a.VAA07 , a.ACF01 , VAE44 = 0 ,a.VAT08,v.BCE03A,v.VAF01,v.CBM01,a.VAT09 ,isnull(b.VBU01,0) VBU01
,v.BCE01A,v.BCE01A as BCE01C,a.FeeFlag
FROM #tmp_VAT a
JOIN VAA1 b with(nolock) ON a.VAA01 = b.VAA01
JOIN VAC1 d with(nolock) ON a.VAA07 = d.VAC01
JOIN #tmp_VAF1 v ON a.VAF01 = v.VAF01
JOIN BBX1 g with(nolock) ON v.BBX01 = g.BBX01
LEFT JOIN BCK1 n with(nolock) ON v.BCK01A = n.BCK01
LEFT JOIN BBX1 m with(nolock) ON a.BBX01 = m.BBX01
LEFT JOIN VAO1 e with(nolock) ON a.VAF01 = e.VAF01 AND e.VAO11 = 8
LEFT JOIN BAK1 f with(nolock) ON e.BAK01A = f.BAK01
LEFT JOIN ABW1 c with(nolock) ON b.ABW01 = c.ABW01
where a.VAT04 <= 4 and a.VAT08 >= @aDate
and (v.BCK01B = @lBCK01 or exists(select vaf01 from #tmp_VAF1 v1 where v1.VAF01A = v.VAF01 and v1.BCK01B = @lBCK01))
and exists(select * from VAI1 s where v.CBM01 = s.CBM01 and ((s.VAI18 = 4) or (@lMember >= 1 and s.VAI18 <= 3) ))
UNION ALL
SELECT a.VAT01 , a.VAT04 ,b.VAA03, b.VAA03 VAA04, b.VAA05 , c.ABW02 , b.VAA10 , a.BEE01 , a.BEE03 ,b.ABBRP,B.ABBRW
, a.VAT33 ,BCQ04B='''' ,f.BAK05 , '''' , m.BBX05 MBBX05 , n.BCK03 , a.BCK01 , a.BBX01
, a.VAA01 , a.VAA07 , a.ACF01 , VAE44 = 0 ,a.VAT08,g.BCE03,0,g.CBM01,a.VAT09,isnull(b.VBU01,0) VBU01
,g.BCE01,g.BCE01 as BCE01C,a.FeeFlag
FROM #tmp_VAT a
JOIN CBM1 g with(nolock) ON a.VAT01 = g.CBM01
JOIN VAA1 b with(nolock) ON a.VAA01 = b.VAA01
LEFT JOIN BCK1 n with(nolock) ON g.BCK01B = n.BCK01
LEFT JOIN BBX1 m with(nolock) ON a.BBX01 = m.BBX01
LEFT JOIN VAO1 e with(nolock) ON a.VAF01 = e.VAF01 AND e.VAO11 = 8
LEFT JOIN BAK1 f with(nolock) ON e.BAK01A = f.BAK01
LEFT JOIN ABW1 c with(nolock) ON b.ABW01 = c.ABW01
WHERE a.VAT04 <= 4 AND a.VAT08 >= @aDate AND a.BCK01 = @lBCK01
AND a.VCY01 > 0 AND a.VAA07 > 0
IF object_id(''tempdb..#tmp_VAT'') IS NOT NULL DROP TABLE #tmp_VAT
IF object_id(''tempdb..#tmp_VAF1'') IS NOT NULL DROP TABLE #tmp_VAF1
IF object_id(''tempdb..#tmp_VAF2'') IS NOT NULL DROP TABLE #tmp_VAF2', '2018-02-02 18:32:26', '(8088)王海涛', 0, 0)
else
print 'SYS_Scripts.id=1669 已经存在.'
GO
If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 1669)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=1669
GO