医保退费主要流程
1.系統初始化Init
GetInvoiceInfo with QryInvoice dobeginClose;ParamByName('DanJuID').AsString:=edtDjid.Text;Open;vJiuZhenID:=FieldByName('JiuZhenID').AsInteger;GetClinicInfo(vJiuZhenID);//獲得就診信息pnlDjrq.Caption:=FieldByName('SerialNo').AsString;pnlSkr.Caption:=FieldByName('ShouKuanRen').AsString;pnlSsje.Caption:=FormatFloat('0.00', FieldByName('ShiShouZLJE').AsFloat);pnlJzdh.Caption:=FieldByName('JieZhangDH').AsString;end;
2.
GetDrugInfo(edtDjid.Text);select a.JiuZhenID,a.ChuFangLH,a.SerialNo,a.YaoPinID,a.YaoMing,a.HeSuanBM,a.GuiGe,a.DanWei,a.DanJia,a.PiJia,a.JinJia,a.Jine,a.ZiLiBL,convert(Numeric(10,2),a.ShuLiang*a.TieShu*a.TianShu) as ShuLiang,a.LingYaoKSBM,a.DanJuID,c.FaYaoZT,a.ZhiXingBM,a.TaoCanBM,TuiShu=case when b.ShuLiang is null then 0 else b.ShuLiang end,IsAppend=convert(bit,0) from Out_RecipeDetail a, Out_RetCharge b, Out_DistriMedicStatus c where a.DanJuID=:DanJuID and a.DanJuID*=b.DanJuID anda.YaoPinID*=b.YaoPinID and a.SerialNo*=b.YaoPinXL and a.DanJuID=c.DanJuIDand a.LingYaoKSBM=c.KeShiBM order by a.LingYaoKSBM ///sqlwith QryDrug dobeginClose;ParamByName('DanJuID').AsString:=aDanJuID;Open;end;
3.
pZ_Out_RetChargeHi******************************************/ /*未結帳且不為同一操作員或已結帳的醫保退票處理*/ /******************************************/ CREATE PROCEDURE pZ_Out_RetChargeHi(@DanJuID varchar(10),@JiuZhenID int,@KeShiBM varchar(10), @XingMing varchar(10),@ShouKuanBM varchar(10),@YiShengBM varchar(10),@BingLiHao varchar(20),@YiLiaoDH varchar(12)) AS declare @TuiFeiSJ datetime,@NewDanJuID varchar(10),@SerialNo varchar(23),@RiQi varchar(10),@OldJiuZhenID int set @TuiFeiSJ=convert(datetime,getdate(),120) set @SerialNo=convert(varchar(23),getdate(),121) set @RiQi=convert(varchar(10),getdate(),120) begin tran--刪除發票臨時打印表delete Out_TempPrintInvoice where ShouKuanYBM=@ShouKuanBMif @@Error<>0beginRollBack TranReturn 16 end update Out_invoice set TuiFeiSJ=@TuiFeiSJ where DanJuID=@DanJuIDif @@Error<>0beginRollBack TranReturn 0--更新發票主表endselect @OldJiuZhenID=JiuZhenID from Out_Invoice where DanJuID=@DanJuID/* insert into Out_ClinicInfo select @SerialNo,BingLiHao,@JiuZhenID,BingRenLB,LeiBieBM,XingMing,KeShiBM,JiuZhenRQ,@ShouKuanBM from Out_clinicInfo where JiuZhenID=@OldJiuZhenIDif @@Error<>0beginRollBack TranReturn 20--插入就診主表失敗end */update Out_RetCharge set TuiFeiBZ=1 where DanJuID=@DanJuIDif @@Error<>0beginRollBack TranReturn 1--更新退費記錄表end --Assay: /*update DR_Store set ZhangMianShu=ZhangMianShu+a.ShuLiang*a.TieShu*a.TianShufrom Out_RecipeDetail a,DR_Store bwhere a.YaoPinID=b.YaoPinID and b.KeShiBM=a.LingYaoKSBM and a.DanJuID=@DanJuID */update DR_Store set ZhangMianShu=ZhangMianShu+a.ShuLiangfrom (select YaoPinID,LingYaoKSBM,Sum(convert(Numeric(10,2),ShuLiang*TieShu*TianShu)) as ShuLiang from Out_RecipeDetail whereDanJuID=@DanJuID Group by YaoPinID,LingYaoKSBM) a,DR_Store bwhere a.YaoPinID=b.YaoPinID and b.KeShiBM=a.LingYaoKSBM /* declare Out_Ret Cursorforselect YaoPinID,LingYaoKSBM,ShuLiang*TieShu*TianShu as ShuLiang from Out_RecipeDetail where DanJuID=@DanJuIDopen Out_Retdeclare @YaoPinID varchar(15),@LingYaoKSBM varchar(10),@ShuLiang Numeric(10,2)Fetch Next from Out_Ret into @YaoPinID,@LingYaoKSBM,@ShuLiangwhile (@@Fetch_Status=0)beginupdate DR_Store set ZhangMianShu=ZhangMianShu+@ShuLiang where YaoPinID=@YaoPinID and KeShiBM=@LingYaoKSBMif @@Error<>0beginClose Out_RetDeallocate Out_RetRollBack TranReturn 2--更新藥房帳面庫存endFetch Next from Out_Ret into @YaoPinID,@LingYaoKSBM,@ShuLiangendClose Out_RetDeallocate Out_Ret */insert into Out_Recipe select @SerialNo,@BingLiHao,@JiuZhenID,2,@RiQi,@YiShengBM,@KeShiBM,@ShouKuanBM,-YingShouJE,-YingShouJE,1,0,0from Out_Invoice where DanJuID=@DanJuIDif @@Error<>0beginRollBack TranReturn 3--生成負處方主表endexec @NewDanJuID=pSys_GetBill @ShouKuanBM,1if @@Error<>0beginRollBack TranReturn -1--生成單據號錯誤endif @NewDanJuID=-1 begin RollBack TranReturn -2--單據已用完end--插入單據進入發票臨時表insert into Out_TempPrintInvoice (ShouKuanYBM,DanJuID) values (@ShouKuanBM,@NewDanJuID)if @@Error<>0beginRollBack TranReturn 15end--插入醫保結算主表insert into Z_Out_Clinic (YiLiaoDH, DanJuID, YiBaoKaHao, SheBaoHao,RenYuanLB, JiuZhenSJ, ZongFY, YiBaoFY, ZiFeiFY, ZiLiFY, NDXTZiLiFY,XianFuFY,DangNianZF, LiNianZF,BuZhuZF,BuChongZF, TongChouZF, JiuZhuZF, GongBuKFZF,LiXiuYFZF, ZiFuZF,XianJinZF, BingZhongMC, JiaoYiSJ,BeiZhu)select @YiLiaoDH, @NewDanJuID, YiBaoKaHao, SheBaoHao,RenYuanLB, @SerialNo, -ZongFY,-YiBaoFY, -ZiFeiFY, -ZiLiFY,- NDXTZiLiFY, -XianFuFY,-DangNianZF, -LiNianZF,-BuZhuZF,-BuChongZF, -TongChouZF, -JiuZhuZF, -GongBuKFZF, -LiXiuYFZF,-ZiFuZF, -XianJinZF,BingZhongMC, @SerialNo,BeiZhufrom Z_Out_Clinic where YiLiaoDH=@YiLiaoDH and DanJuID=@DanJuIDif @@Error <>0beginRollback TranReturn 21end declare Out_Rec Cursor forselect SerialNo,YaoPinID,HeSuanBM,ZhiXingBM,YaoMing,GuiGe,DanWei,DanJia,PiJia,JinJia,ShuLiang,TieShu,TianShu,JinE,ZiLiBL,ZiLiJE,LingYaoKSBM,TaoCanBMfrom Out_RecipeDetailwhere DanJuID=@DanJuIDopen Out_Recdeclare @YaoPinID3 varchar (15),@HeSuanBM varchar(5),@ZhiXingBM varchar(10),@YaoMing varchar(30),@GuiGe varchar(15),@DanWei varchar(5),@DanJia Numeric(8,4),@PiJia Numeric(8,4),@JinJia Numeric(8,4),@OldSerialNo varchar(23),@ShuLiang3 Numeric(10,2),@TieShu3 int,@TianShu3 int,@JinE Numeric(10,2),@ZiLiBL Numeric(10,2),@ZiLiJE Numeric(10,2),@LingYaoKSBM3 varchar(10),@TaoCanBM intFetch Next from Out_Rec into @OldSerialNo,@YaoPinID3,@HeSuanBM,@ZhiXingBM,@YaoMing,@GuiGe,@DanWei,@DanJia,@PiJia,@JinJia,@ShuLiang3,@TieShu3,@TianShu3,@JinE,@ZiLiBL,@ZiLiJE,@LingYaoKSBM3,@TaoCanBMwhile (@@Fetch_Status=0)begininsert into Out_RecipeDetail(SerialNo,JiuZhenID,ChuFangLH,DanJuID,YaoPinID,HeSuanBM,ZhiXingBM,YaoMing,GuiGe,DanWei,DanJia,PiJia,JinJia,ShuLiang,TieShu,TianShu,JinE,ZiLiBL,ZiLiJE,LingYaoKSBM) values(@SerialNo,@JiuZhenID,2,@NewDanJuID,@YaoPinID3,@HeSuanBM,@ZhiXingBM,@YaoMing,@GuiGe,@DanWei,@DanJia,@PiJia,@JinJia,-@ShuLiang3,@TieShu3,@TianShu3,-@JinE,@ZiLiBL,-@ZiLiJE,@LingYaoKSBM3)if @@Error<>0beginClose Out_RecDeallocate Out_RecRollBack TranReturn 4--生成負處方明細表endinsert into Z_Out_ClinicDetail (SerialNo,YiLiaoDH,DanJuID,YiYuanBM,YiBaoBM,YaoPinMC,ShouFeiLBYY,ShouFeiLBYB,JiXing,GuiGe,DanWei,DanJia,ShuLiang,ZiFuBL,ZongFY,ZiFeiFY,ZiLiFY,YiBaoFY)select @SerialNo,@YiLiaoDH,@NewDanJuID,YiYuanBM,YiBaoBM,YaoPinMC,ShouFeiLBYY, ShouFeiLBYB,JiXing,GuiGe,DanWei,DanJia,-ShuLiang,ZiFuBL,-ZongFY,-ZiFeiFY,-ZiLiFY,-YiBaoFYfrom Z_Out_ClinicDetail where YiLiaoDH=@YiLiaoDH and DanJuID=@DanJuID and SerialNo=@OldSerialNo if @@Error<>0beginClose Out_RecDeallocate Out_RecRollBack TranReturn 4--生成醫保負處方明細表endinsert into Z_Out_ClinicDetailExt(Serialno,JiuZhenID,ChuFangLH,DanJuID,YaoPinID,PinCi,YongLiang)select @SerialNo,JiuZhenID,2,@NewDanJuID,YaoPinID,PinCi,YongLiangfrom Z_Out_ClinicDetailExt where SerialNo=@OldSerialNo and JiuZhenID=@OldJiuZhenID and YaoPinID=@YaoPinID3and DanJuId=@DanJuIDif @@Error<>0beginClose Out_RecDeallocate Out_RecRollBack TranReturn 4--生成醫保負處方明細擴展表endset @SerialNo=convert(varchar(23),dateAdd(ms,10,@SerialNo),121)Fetch Next from Out_Rec into @OldSerialNo,@YaoPinID3,@HeSuanBM,@ZhiXingBM,@YaoMing,@GuiGe,@DanWei,@DanJia,@PiJia,@JinJia,@ShuLiang3,@TieShu3,@TianShu3,@JinE,@ZiLiBL,@ZiLiJE,@LingYaoKSBM3,@TaoCanBMendClose Out_RecDeallocate Out_Recinsert into Out_DistriMedicStatus select @SerialNo,@NewDanJuID,KeShiBM,FaYaoCZ,1,FaYaoRQ,FaYaoZT from Out_DistriMedicStatus where DanJuID=@DanJuID if @@Error<>0beginRollBack TranReturn 5--生成負的發藥狀態表endinsert into Out_Invoice select SerialNo,@JiuZhenID,@NewDanJuID,@RiQi,XingMing,-YingShouJE,-ShiShouJE,-YingShouZLJE,-ShiShouZLJE,@ShouKuanBM,1,@SerialNo,Nullfrom Out_Invoice where DanJuID=@DanJuIDif @@Error<>0beginRollBack TranReturn 6--生成負的發票主表endinsert into Out_InvoiceDetail select @SerialNo,@JiuZhenID,@NewDanJuID,HeSuanBM,-YingShouJE,-ShiShouJE,-YingShouZLJE,-ShiShouZLJE from Out_InvoiceDetail where DanJuID=@DanJuIDif @@Error<>0beginRollBack TranReturn 7--生成負的發票明細表end update Out_Recipe Set JieSuanBZ=1 where JiuZhenID=@JiuZhenID and ChuFangLH=1--更新處方主表的結算標志為1if @@Error<>0begin RollBack TranReturn 8--更新正處方主表錯誤enddeclare @Num intselect @Num=count(*) from Out_Recipe where JiuZhenID=@JiuZhenID and ChuFangLH=1if @Num=1beginexec @NewDanJuID=pSys_GetBill @ShouKuanBM,1if @@Error<>0beginRollBack TranReturn -1--生成單據號錯誤endif @NewDanJuID=-1 begin RollBack TranReturn -2--單據已用完enddeclare @YingShouJE Numeric(10,2),@ShiShouJE Numeric(10,2),@YingShouZLJE Numeric(10,2),@ShiShouZLJE Numeric(10,2)set @YingShouJE=0.00set @ShiShouJE=0.00set @YingShouZLJE=0.00set @ShiShouZLJE=0.00 declare Out_Invo Cursor forselect HeSuanBM,convert(Numeric(10,2),sum(JinE)),sum(ZiLiJE)from Out_RecipeDetail where JiuZhenID=@JiuZhenID and ChuFangLH=1 Group by HeSuanBMopen Out_Invodeclare @YingShouJE2 Numeric(10,2),@ShiShouJE2 Numeric(10,2),@HeSuanBM2 varchar(30),@YingShouZLJE2 Numeric(10,2),@ShiShouZLJE2 Numeric(10,2)Fetch Next from Out_Invo into @HeSuanBM2,@YingShouJE2,@YingShouZLJE2while (@@Fetch_Status=0)beginexec pSys_GetRound @YingShouJE2,@ShiShouJE2 outif @ShiShouJE2=-1beginClose Out_InvoDeallocate Out_InvoRollBack TranReturn -3endexec pSys_GetRound @YingShouZLJE2,@ShiShouZLJE2 outif @ShiShouZLJE2=-1beginClose Out_InvoDeallocate Out_InvoRollBack TranReturn -3end set @YingShouJE=@YingShouJE+@YingShouJE2set @ShiShouJE=@ShiShouJE+@ShiShouJE2set @YingShouZLJE=@YingShouZLJE+@YingShouZLJE2set @ShiShouZLJE=@ShiShouZLJE+@ShiShouZLJE2--生成發票明細表insert into Out_InvoiceDetail values(@SerialNo,@JiuZhenID,@NewDanJuID,@HeSuanBM2,@YingShouJE2,@ShiShouJE2,@YingShouZLJE2,@ShiShouZLJE2) if @@Error<>0beginClose Out_InvoDeallocate Out_InvoRollBack TranReturn 11--生成發票明細表錯誤endFetch Next from Out_Invo into @HeSuanBM2,@YingShouJE2,@YingShouZLJE2endClose Out_InvoDeallocate Out_Invoupdate Out_RecipeDetail Set DanJuID=@NewDanJuID where JiuZhenID=@JiuZhenID and ChuFangLH=1--更新處方明細表單據號為當前單據號if @@Error<>0begin RollBack TranReturn 9--更新處方明細表錯誤end--插入單據進入發票臨時表insert into Out_TempPrintInvoice (ShouKuanYBM,DanJuID) values (@ShouKuanBM,@NewDanJuID)if @@Error<>0beginRollBack TranReturn 15end--生成發票主表insert into Out_Invoice (SerialNo,JiuZhenID,DanJuID,DanJuRQ,XingMing,YingShouJE,ShiShouJE,YingShouZLJE,ShiShouZLJE,ShouKuanYBM,JieSuanZT,TuiFeiSJ,JieZhangDH)values (@SerialNo,@JiuZhenID,@NewDanJuID,@RiQi,@XingMing,@YingShouJE,@ShiShouJE,@YingShouZLJE,@ShiShouZLJE,@ShouKuanBM,1,null,null)if @@Error<>0begin RollBack TranReturn 10--生成發票主表錯誤end insert into Out_DistriMedicStatus select distinct @SerialNo,@NewDanJuID,a.LingYaoKSBM,b.FaYaoCZ,1,b.FaYaoRQ,b.FayaoZT from Out_RecipeDetail a,Out_DistriMedicStatus b where a.DanJuID=@NewDanJuID and b.DanJuID=@DanJuID and a.LingYaoKSBM=b.KeShiBMif @@Error<>0begin RollBack TranReturn 12--生成發藥狀態表end--Assay: /*update DR_Store set ZhangMianshu=ZhangMianshu-a.ShuLiang*a.TieShu*a.TianShufrom Out_RecipeDetail a,DR_Store bwhere a.YaoPinID=b.YaoPinID anda.LingYaoKSBM=b.KeShiBM and a.JiuZhenID=@JiuZhenID */update DR_Store set ZhangMianShu=ZhangMianShu-a.ShuLiangfrom (select YaoPinID,LingYaoKSBM,Sum(convert(Numeric(10,2),ShuLiang*TieShu*TianShu)) as ShuLiang from Out_RecipeDetail whereJiuZhenID=@JiuZhenID and ChuFangLH=1 Group by YaoPinID,LingYaoKSBM) a,DR_Store bwhere a.YaoPinID=b.YaoPinID and b.KeShiBM=a.LingYaoKSBM /* declare Out_Rep Cursorforselect YaoPinID,ShuLiang,TieShu,TianShu,LingYaoKSBM from Out_RecipeDetail where JiuZhenID=@JiuZhenID and ChuFangLH=1open Out_Repdeclare @YaoPinID2 varchar(15),@ShuLiang2 Numeric(10,2),@TieShu int,@TianShu int,@LingYaoKSBM2 varchar(10)Fetch Next from Out_Rep into @YaoPinID2,@ShuLiang2,@TieShu,@TianShu,@LingYaoKSBM2while (@@Fetch_Status=0) begindeclare @ZhangMianShu Amountset @ZhangMianShu=convert(numeric(10,2),@ShuLiang2*@TieShu*@TianShu)update DR_Store set ZhangMianShu=ZhangMianShu-@ZhangMianShu where YaoPinID=@YaoPinID2 and KeShiBM=@LingYaoKSBM2if @@Error<>0beginClose Out_RepDeallocate Out_RepRollBack TranReturn 13--更新藥房庫存錯誤endFetch Next from Out_Rep into @YaoPinID2,@ShuLiang2,@TieShu,@TianShu,@LingYaoKSBM2 endClose Out_RepDeallocate Out_Rep */end commit tran Return 14 GO
轉載于:https://www.cnblogs.com/zsdentist/archive/2009/12/16/1625706.html
總結
- 上一篇： SQLServer2005数据库自动备份
- 下一篇： .net下绘制统计图工具-请推荐