public string HisBalance(ParamBalance pb)
{
int zyh = pb.zyh;
string hr = string.Empty;
StringBuilder sb = null;
int iHr = -1;
HrPreClearing hrPreClearing = null;
SqlConnection hisConn = new SqlConnection(GSettings.HisConnStr);
SqlTransaction hisTrans = null;
DCCbhisDataContext hisDb = new DCCbhisDataContext(hisConn);
try
{
hisConn.Open();
var brInfo = (from _r in hisDb.RY where _r.ZYH == zyh && _r.ZT == 0 && _r.ZF == 0 && _r.RegID==null select _r).FirstOrDefault();
if (brInfo==null)
{
throw new Exception("未找到患者有效的在院信息或者该患者不是现金及农合患者！");
}
#region 预处理
//记床位费;
hisDb.ExecuteCommand("exec 住院收费_记单个病人床位费 {0}", zyh);
//查询已记账未发药的单据
var noPutHjds = hisDb.ExecuteQuery<string>(@"select a.hjdh from jzd a join hjd b on a.hjdh=b.hjdh join bm c on a.kdks=c.bmdm join bm d on a.zxks=d.bmdm join zg e on a.czy=e.zgdm left join zg f on a.ys=f.zgdm where a.zyh={0} and a.hjdh is not null and b.fyrq is null and b.zf<>1", zyh).ToArray();
if (noPutHjds!=null&&noPutHjds.Length>0)
{
throw new Exception(string.Format(@"划价单：{0} 已记账未发药！", string.Join(",", noPutHjds)));
}
//查询划价单已作废，记账单未作废的单据
var noDelInvoidJzds = hisDb.ExecuteQuery<string>(@"select jzdh from jzd where jzdh in (select jzdh from hjd where zf=1) and zyh={0} and zf=0",zyh).ToArray();
if (noDelInvoidJzds!=null &&noDelInvoidJzds.Length>0)
{
throw new Exception(string.Format(@"划价单：{0} 已记账未发药！", string.Join(",", noDelInvoidJzds)));
}
var operatorTicketSerial = (from _t in hisDb.DQDJH where _t.CZY == GSettings.OperatorID && _t.DJLXDM==4 select _t).FirstOrDefault();
if (operatorTicketSerial==null)
{
throw new Exception("当前操作员未分配单据号!");
}
int curTickSerail = hisDb.ExecuteQuery<int>("SELECT SJH=ISNULL(MAX(CYFPH),1) FROM CYFP WHERE CYFPH>0 AND CZY={0}", GSettings.OperatorID).First();
if (curTickSerail==1)
{
curTickSerail = operatorTicketSerial.KSDJH.Value;
}
else
{
curTickSerail += 1;
}
if (curTickSerail>operatorTicketSerial.JSDJH.Value)
{
throw new Exception("当前操作员出院发票已用完，请联系分配人员重新分配住院发票！");
}
int cyxh = hisDb.ExecuteQuery<int>("select isnull(max(cyxh),0)+1 from cy").FirstOrDefault();
var sumFee = hisDb.ExecuteQuery<OutValue_sp_GetInHosSumFee_List>("exec sp_GetInHosSumFee_List {0}", zyh).FirstOrDefault();
var zjBqye = hisDb.ExecuteQuery<decimal>(@"select top 1 bqye from cyfp where zyh={0} and cyxh is null order by rq desc, ztjz_zzrq desc, cyfph desc", zyh).FirstOrDefault();
sumFee.yjk += zjBqye;
DateTime serverTime = hisDb.ExecuteQuery<DateTime>("select getdate()").FirstOrDefault();
#endregion
#region 预结算
//预结算一下，并判断两边的费用是否一致；如果不一致则抛出错误，让结算人员重新上传费用；
var nhRegisterInfo = (from _r in hisDb.WyNhRegister where _r.Zyh == zyh && _r.IsFail == 0 select _r).FirstOrDefault();
if (nhRegisterInfo!=null)
{
//先判断一下是否有有效的结算信息
var _validBalance = (from _b in nhRegisterInfo.WyNhBalance where _b.IsFail == 0 select _b).FirstOrDefault();
if (_validBalance!=null)
{
throw new Exception("该农合患者已存在有效的结算信息，请检查！");
}
//如果nhRegisterInfo不为Null，则是农合患者，先预结算一下；
sb = new StringBuilder(500);
iHr = NhLocalWrap.PreClearing(string.Format("{0}$${1}", nhRegisterInfo.OrganCode, nhRegisterInfo.AccountYear),
nhRegisterInfo.CoopMedCode,
nhRegisterInfo.AiIDNo,
int.Parse(nhRegisterInfo.DiagNo),
0, hisDb.getTsByZyh(zyh,pb.outDate).Value,
pb.outDate.ToString("yyyy-MM-dd HH:mm:ss"),
serverTime.ToString("yyyy-MM-dd HH:mm:ss"),
"1",
sb);
if (iHr<0)
{
throw new Exception(string.Format(@"预结算错误：{0}", sb.ToString()));
}
hrPreClearing = sb.ToString();
if (hrPreClearing.TotalFee!=sumFee.yszje)
{
throw new Exception("农合服务器金额和本地His金额不一致，请检查费用是否全部上传完毕！");
}
}
#endregion
//开始事务处理
hisTrans = hisConn.BeginTransaction();
hisDb.Transaction = hisTrans;
//①，insert to cy
CY newCy = new CY();
newCy.CYXH = cyxh;
newCy.ZYH = zyh;
newCy.CYFPH = curTickSerail;
newCy.ksdm = brInfo.KSDM.Value;
newCy.CYRQ = pb.outDate;
newCy.HJJE = sumFee.zje;
newCy.HJJE_YS = sumFee.yszje;
newCy.YJJE = sumFee.yjk;
newCy.BJJE = sumFee.yszje - sumFee.yjk;
newCy.QFJE = 0; newCy.QF = 0; newCy.BXJE = 0;
newCy.QFLBMC = null;
newCy.czy = GSettings.OperatorID;
newCy.fkfs = "现金";
newCy.jsdw = "";
newCy.ZF = 0;
newCy.jbsj = serverTime;
hisDb.CY.InsertOnSubmit(newCy);
hisDb.SubmitChanges();
//②，insert to cyfp
hisDb.ExecuteCommand(@"INSERT INTO CYFP(CYFPH, CYXH, RQ, JSDW, HJJE, HJJE_YS, YJJE, BJJE, CZY, BXJE, ZYH, SQYE, ZF) SELECT CYFPH,CYXH, CYRQ,'', HJJE, HJJE_YS, YJJE, BJJE, CZY, BXJE, ZYH, {1}, 0 FROM CY WHERE CYXH = {0}", cyxh,zjBqye);
//③，insert to cyfpmx
var cyfpmxs = hisDb.ExecuteQuery<OutValue_sp_GetInHosSumFee>("exec sp_GetInHosSumFee @Zyh={0}, @LSH='%'",zyh);
short _xh = 1;
foreach (var cyfpmx in cyfpmxs)
{
hisDb.CYFPMX.InsertOnSubmit(new CYFPMX() {CYFPH=curTickSerail,XH=_xh++,YJKMDM=cyfpmx.yjkmdm,JE=cyfpmx.zje,YSJE=cyfpmx.ysje });
}
//④更新相关表，Ry，zybr_child,bc,bcsyjl
brInfo.ZT = 1;
brInfo.JBSJ = serverTime;
brInfo.CYRQ = pb.outDate;
brInfo.CYBJJE = -1 * brInfo.YE;
brInfo.YE = 0;
hisDb.SubmitChanges();
hisDb.ExecuteCommand(@"update zybr_child set cyrq={0} where zyh={1}", pb.outDate, zyh);
hisDb.ExecuteCommand(@"UPDATE BC SET ZYH=NULL,FCRQ=NULL, FYJSRQ=NULL WHERE ZYH={0}",zyh);
hisDb.ExecuteCommand(@"UPDATE BCSYJL SET JSRQ=b.CYRQ, BS=2 FROM BCSYJL a JOIN RY b ON a.ZYH = b.ZYH WHERE b.ZYH={0} AND a.JSRQ IS NULL", zyh);
//如果是农合患者，则进行农合结算；
WyNhBalance nhBalance = null;
if (nhRegisterInfo!=null)
{
nhBalance = new WyNhBalance();
nhBalance.BalanceID = Guid.NewGuid();
nhBalance.NhRegID = nhRegisterInfo.NhRegID;
nhBalance.IsFail = 0;
nhBalance.DayCount = hisDb.getTsByZyh(zyh, pb.outDate).Value;
nhBalance.OutDate = pb.outDate;
nhBalance.JsDate = serverTime;
nhBalance.Zyh = zyh;
nhBalance.Cyfph = curTickSerail;
hisDb.WyNhBalance.InsertOnSubmit(nhBalance);
hisDb.SubmitChanges();
sb = new StringBuilder(500);
iHr = NhLocalWrap.PreClearing(string.Format("{0}$${1}", nhRegisterInfo.OrganCode, nhRegisterInfo.AccountYear),
nhRegisterInfo.CoopMedCode,
nhRegisterInfo.AiIDNo,
int.Parse(nhRegisterInfo.DiagNo),
1, nhBalance.DayCount,
nhBalance.OutDate.ToString("yyyy-MM-dd HH:mm:ss"),
nhBalance.JsDate.ToString("yyyy-MM-dd HH:mm:ss"),
"1",
sb);
if (iHr < 0)
{
throw new Exception(string.Format(@"结算错误：{0}", sb.ToString()));
}
hrPreClearing = sb.ToString();
}
hisTrans.Commit();
//实际结算工作已成功，做结算的后处理；；；update wynhbalance表，如果是异地还需要数据回写当地和管办服务器
if (nhRegisterInfo!=null && nhBalance!=null)
{
//update 结算信息；
nhBalance.TotalFee = hrPreClearing.TotalFee;
nhBalance.ReimRangeFee = hrPreClearing.ReimRangeFee;
nhBalance.ReimFee = hrPreClearing.ReimFee;
nhBalance.HospitalReduction = hrPreClearing.HospitalReduction;
nhBalance.CivilPay = hrPreClearing.CivilPay;
nhBalance.ScAmount = hrPreClearing.ScAmount;
nhBalance.HospitalCost = hrPreClearing.HospitalCost;
nhBalance.BeginLimite = hrPreClearing.BeginLimite;
nhBalance.SpecialIllHospitalCost = hrPreClearing.SpecialIllHospitalCost;
nhBalance.YearLimite = hrPreClearing.YearLimite;
nhBalance.YearTotalReimFee = hrPreClearing.YearTotalReimFee;
hisDb.SubmitChanges();
//如果是异地则回写当地和管办服务器
if (nhRegisterInfo.OrganCode==GSettings.OrganIDRemote)
{
sb = new StringBuilder(500);
iHr = NhLocalWrap.zzGetDataBack(GSettings.ParamRemoteOrganID, nhRegisterInfo.AreaCode, nhRegisterInfo.CoopMedCode, nhRegisterInfo.AiIDNo, Convert.ToInt32(nhRegisterInfo.DiagNo), nhRegisterInfo.ExpressionID, sb);
if (iHr<0)
{
throw new Exception(string.Format(@"异地农合回写错误：{0}", sb.ToString()));
}
}
}
}
catch (System.Exception ex)
{
hr = ex.Message;
try
{
hisTrans.Rollback();
}
catch (System.Exception exx)
{
Console.WriteLine("roll back Error" + exx.Message);
}
}
finally
{
hisConn.Close();
}
return hr;
}
public string HisBalance(ParamBalance pb)
{
int zyh = pb.zyh;
string hr = string.Empty;
StringBuilder sb = null;
int iHr = -1;
HrPreClearing hrPreClearing = null;
SqlConnection hisConn = new SqlConnection(GSettings.HisConnStr);
SqlTransaction hisTrans = null;
DCCbhisDataContext hisDb = new DCCbhisDataContext(hisConn);
try
{
hisConn.Open();
var brInfo = (from _r in hisDb.RY where _r.ZYH == zyh && _r.ZT == 0 && _r.ZF == 0 && _r.RegID == null select _r).FirstOrDefault();
if (brInfo == null)
{
throw new Exception("未找到患者有效的在院信息或者该患者不是现金及农合患者！");
}
#region 预处理
//记床位费;
hisDb.ExecuteCommand("exec 住院收费_记单个病人床位费 {0}", zyh);
//查询已记账未发药的单据
var noPutHjds = hisDb.ExecuteQuery <string>(@"select a.hjdh from jzd a join hjd b on a.hjdh=b.hjdh join bm c on a.kdks=c.bmdm join bm d on a.zxks=d.bmdm join zg e on a.czy=e.zgdm left join zg f on a.ys=f.zgdm where a.zyh={0} and a.hjdh is not null and b.fyrq is null and b.zf<>1", zyh).ToArray();
if (noPutHjds != null && noPutHjds.Length > 0)
{
throw new Exception(string.Format(@"划价单：{0} 已记账未发药！", string.Join(",", noPutHjds)));
}
//查询划价单已作废，记账单未作废的单据
var noDelInvoidJzds = hisDb.ExecuteQuery <string>(@"select jzdh from jzd where jzdh in (select jzdh from hjd where zf=1) and zyh={0} and zf=0", zyh).ToArray();
if (noDelInvoidJzds != null && noDelInvoidJzds.Length > 0)
{
throw new Exception(string.Format(@"划价单：{0} 已记账未发药！", string.Join(",", noDelInvoidJzds)));
}
var operatorTicketSerial = (from _t in hisDb.DQDJH where _t.CZY == GSettings.OperatorID && _t.DJLXDM == 4 select _t).FirstOrDefault();
if (operatorTicketSerial == null)
{
throw new Exception("当前操作员未分配单据号!");
}
int curTickSerail = hisDb.ExecuteQuery <int>("SELECT SJH=ISNULL(MAX(CYFPH),1) FROM CYFP WHERE CYFPH>0 AND CZY={0}", GSettings.OperatorID).First();
if (curTickSerail == 1)
{
curTickSerail = operatorTicketSerial.KSDJH.Value;
}
else
{
curTickSerail += 1;
}
if (curTickSerail > operatorTicketSerial.JSDJH.Value)
{
throw new Exception("当前操作员出院发票已用完，请联系分配人员重新分配住院发票！");
}
int cyxh = hisDb.ExecuteQuery <int>("select isnull(max(cyxh),0)+1 from cy").FirstOrDefault();
var sumFee = hisDb.ExecuteQuery <OutValue_sp_GetInHosSumFee_List>("exec sp_GetInHosSumFee_List {0}", zyh).FirstOrDefault();
var zjBqye = hisDb.ExecuteQuery <decimal>(@"select top 1 bqye from cyfp where zyh={0} and cyxh is null order by rq desc, ztjz_zzrq desc, cyfph desc", zyh).FirstOrDefault();
sumFee.yjk += zjBqye;
DateTime serverTime = hisDb.ExecuteQuery <DateTime>("select getdate()").FirstOrDefault();
#endregion
#region 预结算
//预结算一下，并判断两边的费用是否一致；如果不一致则抛出错误，让结算人员重新上传费用；
var nhRegisterInfo = (from _r in hisDb.WyNhRegister where _r.Zyh == zyh && _r.IsFail == 0 select _r).FirstOrDefault();
if (nhRegisterInfo != null)
{
//先判断一下是否有有效的结算信息
var _validBalance = (from _b in nhRegisterInfo.WyNhBalance where _b.IsFail == 0 select _b).FirstOrDefault();
if (_validBalance != null)
{
throw new Exception("该农合患者已存在有效的结算信息，请检查！");
}
//如果nhRegisterInfo不为Null，则是农合患者，先预结算一下；
sb = new StringBuilder(500);
iHr = NhLocalWrap.PreClearing(string.Format("{0}$${1}", nhRegisterInfo.OrganCode, nhRegisterInfo.AccountYear),
nhRegisterInfo.CoopMedCode,
nhRegisterInfo.AiIDNo,
int.Parse(nhRegisterInfo.DiagNo),
0, hisDb.getTsByZyh(zyh, pb.outDate).Value,
pb.outDate.ToString("yyyy-MM-dd HH:mm:ss"),
serverTime.ToString("yyyy-MM-dd HH:mm:ss"),
"1",
sb);
if (iHr < 0)
{
throw new Exception(string.Format(@"预结算错误：{0}", sb.ToString()));
}
hrPreClearing = sb.ToString();
if (hrPreClearing.TotalFee != sumFee.yszje)
{
throw new Exception("农合服务器金额和本地His金额不一致，请检查费用是否全部上传完毕！");
}
}
#endregion
//开始事务处理
hisTrans = hisConn.BeginTransaction();
hisDb.Transaction = hisTrans;
//①，insert to cy
CY newCy = new CY();
newCy.CYXH = cyxh;
newCy.ZYH = zyh;
newCy.CYFPH = curTickSerail;
newCy.ksdm = brInfo.KSDM.Value;
newCy.CYRQ = pb.outDate;
newCy.HJJE = sumFee.zje;
newCy.HJJE_YS = sumFee.yszje;
newCy.YJJE = sumFee.yjk;
newCy.BJJE = sumFee.yszje - sumFee.yjk;
newCy.QFJE = 0; newCy.QF = 0; newCy.BXJE = 0;
newCy.QFLBMC = null;
newCy.czy = GSettings.OperatorID;
newCy.fkfs = "现金";
newCy.jsdw = "";
newCy.ZF = 0;
newCy.jbsj = serverTime;
hisDb.CY.InsertOnSubmit(newCy);
hisDb.SubmitChanges();
//②，insert to cyfp
hisDb.ExecuteCommand(@"INSERT INTO CYFP(CYFPH, CYXH, RQ, JSDW, HJJE, HJJE_YS, YJJE, BJJE, CZY, BXJE, ZYH, SQYE, ZF) SELECT CYFPH,CYXH, CYRQ,'', HJJE, HJJE_YS, YJJE, BJJE, CZY, BXJE, ZYH, {1}, 0 FROM CY WHERE CYXH = {0}", cyxh, zjBqye);
//③，insert to cyfpmx
var cyfpmxs = hisDb.ExecuteQuery <OutValue_sp_GetInHosSumFee>("exec sp_GetInHosSumFee @Zyh={0}, @LSH='%'", zyh);
short _xh = 1;
foreach (var cyfpmx in cyfpmxs)
{
hisDb.CYFPMX.InsertOnSubmit(new CYFPMX()
{
CYFPH = curTickSerail, XH = _xh++, YJKMDM = cyfpmx.yjkmdm, JE = cyfpmx.zje, YSJE = cyfpmx.ysje
});
}
//④更新相关表，Ry，zybr_child,bc,bcsyjl
brInfo.ZT = 1;
brInfo.JBSJ = serverTime;
brInfo.CYRQ = pb.outDate;
brInfo.CYBJJE = -1 * brInfo.YE;
brInfo.YE = 0;
hisDb.SubmitChanges();
hisDb.ExecuteCommand(@"update zybr_child set cyrq={0} where zyh={1}", pb.outDate, zyh);
hisDb.ExecuteCommand(@"UPDATE BC SET ZYH=NULL,FCRQ=NULL, FYJSRQ=NULL WHERE ZYH={0}", zyh);
hisDb.ExecuteCommand(@"UPDATE BCSYJL SET JSRQ=b.CYRQ, BS=2 FROM BCSYJL a JOIN RY b ON a.ZYH = b.ZYH WHERE b.ZYH={0} AND a.JSRQ IS NULL", zyh);
//如果是农合患者，则进行农合结算；
WyNhBalance nhBalance = null;
if (nhRegisterInfo != null)
{
nhBalance = new WyNhBalance();
nhBalance.BalanceID = Guid.NewGuid();
nhBalance.NhRegID = nhRegisterInfo.NhRegID;
nhBalance.IsFail = 0;
nhBalance.DayCount = hisDb.getTsByZyh(zyh, pb.outDate).Value;
nhBalance.OutDate = pb.outDate;
nhBalance.JsDate = serverTime;
nhBalance.Zyh = zyh;
nhBalance.Cyfph = curTickSerail;
hisDb.WyNhBalance.InsertOnSubmit(nhBalance);
hisDb.SubmitChanges();
sb = new StringBuilder(500);
iHr = NhLocalWrap.PreClearing(string.Format("{0}$${1}", nhRegisterInfo.OrganCode, nhRegisterInfo.AccountYear),
nhRegisterInfo.CoopMedCode,
nhRegisterInfo.AiIDNo,
int.Parse(nhRegisterInfo.DiagNo),
1, nhBalance.DayCount,
nhBalance.OutDate.ToString("yyyy-MM-dd HH:mm:ss"),
nhBalance.JsDate.ToString("yyyy-MM-dd HH:mm:ss"),
"1",
sb);
if (iHr < 0)
{
throw new Exception(string.Format(@"结算错误：{0}", sb.ToString()));
}
hrPreClearing = sb.ToString();
}
hisTrans.Commit();
//实际结算工作已成功，做结算的后处理；；；update wynhbalance表，如果是异地还需要数据回写当地和管办服务器
if (nhRegisterInfo != null && nhBalance != null)
{
//update 结算信息；
nhBalance.TotalFee = hrPreClearing.TotalFee;
nhBalance.ReimRangeFee = hrPreClearing.ReimRangeFee;
nhBalance.ReimFee = hrPreClearing.ReimFee;
nhBalance.HospitalReduction = hrPreClearing.HospitalReduction;
nhBalance.CivilPay = hrPreClearing.CivilPay;
nhBalance.ScAmount = hrPreClearing.ScAmount;
nhBalance.HospitalCost = hrPreClearing.HospitalCost;
nhBalance.BeginLimite = hrPreClearing.BeginLimite;
nhBalance.SpecialIllHospitalCost = hrPreClearing.SpecialIllHospitalCost;
nhBalance.YearLimite = hrPreClearing.YearLimite;
nhBalance.YearTotalReimFee = hrPreClearing.YearTotalReimFee;
hisDb.SubmitChanges();
//如果是异地则回写当地和管办服务器
if (nhRegisterInfo.OrganCode == GSettings.OrganIDRemote)
{
sb = new StringBuilder(500);
iHr = NhLocalWrap.zzGetDataBack(GSettings.ParamRemoteOrganID, nhRegisterInfo.AreaCode, nhRegisterInfo.CoopMedCode, nhRegisterInfo.AiIDNo, Convert.ToInt32(nhRegisterInfo.DiagNo), nhRegisterInfo.ExpressionID, sb);
if (iHr < 0)
{
throw new Exception(string.Format(@"异地农合回写错误：{0}", sb.ToString()));
}
}
}
}
catch (System.Exception ex)
{
hr = ex.Message;
try
{
hisTrans.Rollback();
}
catch (System.Exception exx)
{
Console.WriteLine("roll back Error" + exx.Message);
}
}
finally
{
hisConn.Close();
}
return(hr);
}
