通用:东软请求和返回日志表
配置表(请求参数、返回参数说明)
CREATE TABLE `neusoft_log_column_remark` ( `request_type` varchar(255) NOT NULL COMMENT 'input请求参数,output输出参数', `data_type` varchar(255) NOT NULL COMMENT '0,读卡,1,结算', `column_index` int(8) NOT NULL COMMENT '序号', `remark` varchar(255) NOT NULL COMMENT '字段名称', PRIMARY KEY (`request_type`,`data_type`,`column_index`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
日志表(东软接口请求参数和返回参数)
CREATE TABLE `neusoft_log` ( `Payment_Mode_Code` varchar(255) DEFAULT NULL COMMENT '支付方式编码', `Data_Type` varchar(255) DEFAULT NULL COMMENT '0,读卡,1,结算', `input1` text, `input2` text, `input3` text, `input4` text, `input5` text, `input6` text, `input7` text, `input8` text, `input9` text, `input10` text, `input11` text, `input12` text, `input13` text, `input14` text, `input15` text, `input16` text, `input17` text, `input18` text, `input19` text, `input20` text, `input21` text, `input22` text, `input23` text, `input24` text, `input25` text, `input26` text, `input27` text, `input28` text, `input29` text, `input30` text, `output1` text, `output2` text, `output3` text, `output4` text, `output5` text, `output6` text, `output7` text, `output8` text, `output9` text, `output10` text, `output11` text, `output12` text, `output13` text, `output14` text, `output15` text, `output16` text, `output17` text, `output18` text, `output19` text, `output20` text, `output21` text, `output22` text, `output23` text, `output24` text, `output25` text, `output26` text, `output27` text, `output28` text, `output29` text, `output30` text, `output31` text, `output32` text, `output33` text, `output34` text, `output35` text, `output36` text, `output37` text, `output38` text, `output39` text, `output40` text, `output41` text, `output42` text, `output43` text, `output44` text, `output45` text, `output46` text, `output47` text, `output48` text, `output49` text, `output50` text, `output51` text, `output52` text, `output53` text, `output54` text, `output55` text, `output56` text, `output57` text, `output58` text, `output59` text, `output60` text, `output61` text, `output62` text, `output63` text, `output64` text, `output65` text, `output66` text, `output67` text, `output68` text, `output69` text, `output70` text, `output71` text, `output72` text, `output73` text, `output74` text, `output75` text, `output76` text, `output77` text, `output78` text, `output79` text, `output80` text, `output81` text, `output82` text, `output83` text, `output84` text, `output85` text, `output86` text, `output87` text, `output88` text, `output89` text, `output90` text, `output91` text, `output92` text, `output93` text, `output94` text, `output95` text, `output96` text, `output97` text, `output98` text, `output99` text, `output100` text ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
自定义函数(获取查询sql)
DELIMITER $$ create function GetSelect(datatype int)returns varchar(5000) BEGIN -- 声明一个变量 使用declare 变量名 数据类型 默认值 declare str varchar(5000) default ' select Data_Type,Payment_Mode_Code,'; declare remark2 varchar(500) default ''; -- 使用set 对变量进行赋值 declare i int DEFAULT 1; while i <= 30 do select Remark into remark2 from neusoft_log_column_remark where column_Index=i and Data_Type=datatype and request_type='input'; if (remark2!='' and remark2 is not null ) then set remark2=replace(remark2,'(','_'); set remark2=replace(remark2,')','_'); set str = CONCAT(str,'input',i , ' as in',remark2,'_',i,','); end if; set remark2=''; set i = i + 1; end while; set i=1; while i <= 100 do select Remark into remark2 from neusoft_log_column_remark where column_Index=i and Data_Type=datatype and request_type='output'; if (remark2!='' and remark2 is not null ) then set remark2=replace(remark2,'(','_'); set remark2=replace(remark2,')','_'); set str = CONCAT(str,'output',i , ' as out',remark2,'_',i,','); end if; set remark2=''; set i = i + 1; end while; set str = CONCAT(str,' '''' from neusoft_log where Data_Type=''',datatype, ''' and Payment_Mode_Code= '''' ' ); return str; end $$ DELIMITER ;
插入测试数据
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1', 1, '住院号(门诊号)');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1', 2, '单据号');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1',3, '结算日期');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1',4, '报销类别');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1',5, '账户使用标志');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1',6, '中途结算标志');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1',7, '经办人');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1',8, '是否保存处方标志');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1',9, '备用');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1',10, '报销预付款');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1',11, '备用');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('input', '1',12, '使用账户金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',1, '本次医疗费用');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',2, '本次帐户支出');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',3, '本次基本统筹支出');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',4, '本次定点医疗机构分担金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',5, '本次大病(额)支出');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',6, '本次补充医疗支出');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',7, '本次现金支出');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',8, '本次离休统筹支出');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',9, '本次伤残军人补助支出');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',10, '起付标准');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',11, '基本统筹分段自付');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',12, '本次进入基本统筹金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',13, '自费总金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',14, '帐户余额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',15, '人员类别');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',16, '低保人员医院分担金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',17, '低保标志');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',18, '公立医院补助标志');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',19, '公立医院住院补助金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',20, '门慢累计金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',21, '参保人所属统筹区编号');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',22, '按床日付费医院承担金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',23, '单病种医院承担金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',24, '保底报销医院承担金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',25, '大病分段自付');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',26, '本次进入大病金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',27, '本次乙类先付金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',28, '本次民政医疗救助金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',29, '本次健康扶贫补充保险报销金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',30, '本次自费费用超比例医院分担');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',31, '民政医疗救助人员类别');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',32, '民政医疗救助对象属地');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '1',33, '单病种结余奖励金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',1, '个人编号');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',2, '单位编号');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',3, '身份证号');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',4, '姓名');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',5, '性别');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',6, '民族');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',7, '出生日期');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',8, '社会保障卡卡号');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',9, '医疗人员类别');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',10, '医疗证号');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',11, '人员状态');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',12, '参保状态');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',13, '社保卡密码');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',14, '行政职务');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',15, '原系统医保个人编号');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',16, '副县副团标志');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',17, '灵活就业人员本次医疗连续缴费月数');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',18, '基金类型');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',19, '单位名称');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',20, '参保地');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',21, '单位类型');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',22, '特困企业标志');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',23, '比例类别');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',24, '年度');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',25, '上年结转金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',26, '本年个人缴费部分本金');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',27, '本年单位缴费划拨部分本金');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',28, '本年补充医疗注入');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',29, '本年企业补充医疗保险注入');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',30, '本年机关补充医疗保险注入');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',31, '继承金额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',32, '本年利息增加额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',33, '历年利息增加额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',34, '截止上年末基本医疗累计缴费月数');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',35, '基本医疗本年缴费月数');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',36, '帐户余额');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',37, '在院状态');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',38, '本年医疗费累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',39, '本年现金支出累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',40, '本年帐户支出累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',41, '本年统筹支出累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',42, '本年补充医疗支出累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',43, '本年大额(病)支出累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',44, '本年离休统筹支出累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',45, '本年伤残军人补助支出累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',46, '本年住院次数');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',47, '本年进入分段统筹费用累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',48, '本年异地安置住院费用累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',49, '本年超大额统筹个人自付累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',50, '本年门诊特检特治结算次数累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',51, '月结单类别');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',52, '公立医院补助资格标志');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',53, '门诊慢性病年度累计支付');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',54, '门诊慢性病名称');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',55, '城乡居民人员类型');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',56, '城乡居民人员类别');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',57, '民政医疗救助人员类别');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',58, '本年民政医疗救助金额累计');
INSERT INTO `neusoft_log_column_remark`(`request_type`, `data_type`, `column_index`, `remark`) VALUES ('output', '0',59, '本年健康扶贫补充保险报销金额累计');
使用方法:
select GetSelect(0) '获取:读卡日志的查询sql '; select GetSelect(1) '获取:结算日志的查询sql';