public PersonalStatisticsInfo GetPersonalStatisticsGL2(DateTime beginTime, DateTime endTime, string name, string workCode, List<int> role)
{
StringBuilder sb = new StringBuilder();
sb.Append(@"select
除去拒绝治疗病历数=sum(case when mpr.IFRefuseTreatment<>'拒绝治疗' then 1 else 0 end),
sum(case when mpr.CPRIFSuccess ='ROSC' then 1 else 0 end ) as ROSC数,
count(*) as 病历数
into #temp
from M_PatientRecord mpr
where mpr.MedicalRecordGenerationTime >= '" + beginTime + "' and mpr.MedicalRecordGenerationTime <='" + endTime + "' ");
if (role.Contains(10))
{
WhereClauseUtility.AddStringEqual("mpr.Driver", name, sb);
}
else if (role.Contains(1))
{
WhereClauseUtility.AddStringEqual("mpr.DoctorAndNurse", name, sb);
}
else if (role.Contains(3))
{
WhereClauseUtility.AddStringEqual("mpr.DoctorAndNurse", name, sb);
}
else if (role.Contains(28))
{
WhereClauseUtility.AddStringEqual("mpr.StretcherBearersI", name, sb);
}
else
{
WhereClauseUtility.AddStringEqual("1", "2", sb);
}
sb.Append(@" select isnull(sum(有救治措施除去只有搬运和护送),0) as 有救治措施除去只有搬运和护送
into #temp1
from( select distinct mprm.TaskCode,
有救治措施除去只有搬运和护送= (case when mprm.RescueMeasureCode in(229,231,282,296,317,318,320,322,325,329,332,333,334,335,338,341,342,344,351,352,353
,397,398,399,400,402,404,405,406,407,408,410,418,419,420,422,436,449,450,489,490)
then 1 else 0 end)
from M_PatientRecordMeasure mprm
inner join M_PatientRecord mpr on mpr.TaskCode = mprm.TaskCode and mpr.PatientOrder = mprm.PatientOrder
where mpr.MedicalRecordGenerationTime >= '" + beginTime + "' and mpr.MedicalRecordGenerationTime <='" + endTime + "' ");
if (role.Contains(10))
{
WhereClauseUtility.AddStringEqual("mpr.Driver", name, sb);
}
else if (role.Contains(1))
{
WhereClauseUtility.AddStringEqual("mpr.DoctorAndNurse", name, sb);
}
else if (role.Contains(3))
{
WhereClauseUtility.AddStringEqual("mpr.DoctorAndNurse", name, sb);
}
else if (role.Contains(28))
{
WhereClauseUtility.AddStringEqual("mpr.StretcherBearersI", name, sb);
}
else
{
WhereClauseUtility.AddStringEqual("1", "2", sb);
}
sb.Append(@" ) t
select isnull(sum(静脉开通数),0) as 静脉开通数
into #temp2
from
(select distinct mprm.TaskCode,(case when mprm.RescueMeasureCode in(329,422,436) then 1 else 0 end) as 静脉开通数
from M_PatientRecordMeasure mprm
inner join M_PatientRecord mpr on mpr.TaskCode = mprm.TaskCode and mpr.PatientOrder = mprm.PatientOrder
where mpr.MedicalRecordGenerationTime >= '" + beginTime + "' and mpr.MedicalRecordGenerationTime <='" + endTime + "' ");
if (role.Contains(10))
{
WhereClauseUtility.AddStringEqual("mpr.Driver", name, sb);
}
else if (role.Contains(1))
{
WhereClauseUtility.AddStringEqual("mpr.DoctorAndNurse", name, sb);
}
else if (role.Contains(3))
{
WhereClauseUtility.AddStringEqual("mpr.DoctorAndNurse", name, sb);
}
else if (role.Contains(28))
{
WhereClauseUtility.AddStringEqual("mpr.StretcherBearersI", name, sb);
}
else
{
WhereClauseUtility.AddStringEqual("1", "2", sb);
}
sb.Append(@" ) t1
select isnull(sum(心肺复苏数),0) as 心肺复苏数
into #temp3
from
(select distinct mprm.TaskCode,(case when mprm.RescueMeasureCode in (341,397,449,450) then 1 else 0 end) as 心肺复苏数
from M_PatientRecordMeasure mprm
inner join M_PatientRecord mpr on mpr.TaskCode = mprm.TaskCode and mpr.PatientOrder = mprm.PatientOrder
where mpr.MedicalRecordGenerationTime >= '" + beginTime + "' and mpr.MedicalRecordGenerationTime <='" + endTime + "' ");
if (role.Contains(10))
{
WhereClauseUtility.AddStringEqual("mpr.Driver", name, sb);
}
else if (role.Contains(1))
{
WhereClauseUtility.AddStringEqual("mpr.DoctorAndNurse", name, sb);
}
else if (role.Contains(3))
{
WhereClauseUtility.AddStringEqual("mpr.DoctorAndNurse", name, sb);
}
else if (role.Contains(28))
{
WhereClauseUtility.AddStringEqual("mpr.StretcherBearersI", name, sb);
}
else
{
WhereClauseUtility.AddStringEqual("1", "2", sb);
}
sb.Append(@" ) t2
select 除去拒绝治疗病历数,ROSC数,病历数,有救治措施除去只有搬运和护送,静脉开通数,心肺复苏数
from #temp,#temp1,#temp2,#temp3
drop table #temp1
drop table #temp2
drop table #temp3
drop table #temp ");
using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null))
{
PersonalStatisticsInfo AEinfo = new PersonalStatisticsInfo();
if (dr.Read())
{
AEinfo.PPRNumberExceptRefuseTreatment = DBConvert.ConvertStringToString(dr["除去拒绝治疗病历数"]);
AEinfo.PDisposeNumber = DBConvert.ConvertStringToString(dr["有救治措施除去只有搬运和护送"]);
AEinfo.PVeinNumber = DBConvert.ConvertStringToString(dr["静脉开通数"]);
AEinfo.PROSCNumber = DBConvert.ConvertStringToString(dr["ROSC数"]);
AEinfo.PCPRNumber = DBConvert.ConvertStringToString(dr["心肺复苏数"]);
AEinfo.PPRNumber = DBConvert.ConvertStringToString(dr["病历数"]);
}
return AEinfo;
}
}
//获取个人管理块收费
public PersonalStatisticsInfo GetPersonalStatisticsGLCharge(DateTime beginTime, DateTime endTime)
{
// long start = DateTime.Now.Ticks;
List<int> role = UserOperateContext.Current.Session_UsrRole;
string selfName = UserOperateContext.Current.Session_UsrInfo.Name;
string selfWorkCode = UserOperateContext.Current.Session_UsrInfo.WorkCode;
//司机
if (role.Contains(10))
{
PersonalStatisticsInfo AEinfo = new PersonalStatisticsInfo();
int PKilometer = 0; int PChargeCarFee = 0; int PChargeWaitFee = 0; int PChargeAidFee = 0; int PCharge = 0;
DataTable tableall = GetStatisticsCharge(beginTime, endTime);
foreach (DataRow dr in tableall.Rows)
{
if (Convert.ToString(dr["Driver"]) == selfName)
{
PKilometer += Convert.ToInt32(dr["收费公里数"]);
PChargeCarFee += Convert.ToInt32(dr["车费"]);
PChargeWaitFee += Convert.ToInt32(dr["等候费"]);
PChargeAidFee += Convert.ToInt32(dr["治疗费"]);
PCharge += Convert.ToInt32(dr["收费金额"]);
}
}
AEinfo.PKilometer = Convert.ToString(PKilometer);
AEinfo.PChargeCarFee = Convert.ToString(PChargeCarFee);
AEinfo.PChargeWaitFee = Convert.ToString(PChargeWaitFee);
AEinfo.PChargeAidFee = Convert.ToString(PChargeAidFee);
AEinfo.PCharge = Convert.ToString(PCharge);
return AEinfo;
}
//医生，护士
else if (role.Contains(1) || role.Contains(3))
{
PersonalStatisticsInfo AEinfo = new PersonalStatisticsInfo();
int PKilometer = 0; int PChargeCarFee = 0; int PChargeWaitFee = 0; int PChargeAidFee = 0; int PCharge = 0;
DataTable tableall = GetStatisticsCharge(beginTime, endTime);
foreach (DataRow dr in tableall.Rows)
{
if (Convert.ToString(dr["DoctorAndNurse"]) == selfName)
{
PKilometer += Convert.ToInt32(dr["收费公里数"]);
PChargeCarFee += Convert.ToInt32(dr["车费"]);
PChargeWaitFee += Convert.ToInt32(dr["等候费"]);
PChargeAidFee += Convert.ToInt32(dr["治疗费"]);
PCharge += Convert.ToInt32(dr["收费金额"]);
}
}
AEinfo.PKilometer = Convert.ToString(PKilometer);
AEinfo.PChargeCarFee = Convert.ToString(PChargeCarFee);
AEinfo.PChargeWaitFee = Convert.ToString(PChargeWaitFee);
AEinfo.PChargeAidFee = Convert.ToString(PChargeAidFee);
AEinfo.PCharge = Convert.ToString(PCharge);
return AEinfo;
}
//担架工
else if (role.Contains(28))
{
PersonalStatisticsInfo AEinfo = new PersonalStatisticsInfo();
int PKilometer = 0; int PChargeCarFee = 0; int PChargeWaitFee = 0; int PChargeAidFee = 0; int PCharge = 0;
DataTable tableall = GetStatisticsCharge(beginTime, endTime);
foreach (DataRow dr in tableall.Rows)
{
if (Convert.ToString(dr["StretcherBearersI"]) == selfName)
{
PKilometer += Convert.ToInt32(dr["收费公里数"]);
PChargeCarFee += Convert.ToInt32(dr["车费"]);
PChargeWaitFee += Convert.ToInt32(dr["等候费"]);
PChargeAidFee += Convert.ToInt32(dr["治疗费"]);
PCharge += Convert.ToInt32(dr["收费金额"]);
}
}
AEinfo.PKilometer = Convert.ToString(PKilometer);
AEinfo.PChargeCarFee = Convert.ToString(PChargeCarFee);
AEinfo.PChargeWaitFee = Convert.ToString(PChargeWaitFee);
AEinfo.PChargeAidFee = Convert.ToString(PChargeAidFee);
AEinfo.PCharge = Convert.ToString(PCharge);
return AEinfo;
}
else return new PersonalStatisticsInfo();
// long aaa= DateTime.Now.Ticks - start;
}
public PersonalStatisticsInfo GetPersonalStatisticsGL1(DateTime beginTime, DateTime endTime, string name, string workCode, List<int> role)
{
StringBuilder sb = new StringBuilder();
sb.Append(@"
select 收费公里数=isnull(sum(chargekm),0) ,车费=isnull(sum(CarFee),0),等候费=isnull(sum(WaitingFee),0),
治疗费=sum((PaidMoney-isnull(CarFee,0)-isnull(WaitingFee,0))),收费金额=isnull(sum(PaidMoney),0)
from M_PatientCharge mpc
inner join M_PatientRecord mpr on mpc.TaskCode =mpr.TaskCode and mpc.PatientOrder= mpr.PatientOrder
where mpc.Date >= '" + beginTime + "' and mpc.Date <='" + endTime + @"'
");
if (role.Contains(10))
{
WhereClauseUtility.AddStringEqual("mpr.Driver", name, sb);
}
else if (role.Contains(1))
{
WhereClauseUtility.AddStringEqual("mpr.DoctorAndNurse", name, sb);
}
else if (role.Contains(3))
{
WhereClauseUtility.AddStringEqual("mpr.DoctorAndNurse", name, sb);
}
else if (role.Contains(28))
{
WhereClauseUtility.AddStringEqual("mpr.StretcherBearersI", name, sb);
}
else
{
WhereClauseUtility.AddStringEqual("1", "2", sb);
}
using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null))
{
PersonalStatisticsInfo AEinfo = new PersonalStatisticsInfo();
if (dr.Read())
{
AEinfo.PKilometer = DBConvert.ConvertStringToString(dr["收费公里数"]);
AEinfo.PChargeCarFee = DBConvert.ConvertStringToString(dr["车费"]);
AEinfo.PChargeWaitFee = DBConvert.ConvertStringToString(dr["等候费"]);
AEinfo.PChargeAidFee = DBConvert.ConvertStringToString(dr["治疗费"]);
AEinfo.PCharge = DBConvert.ConvertStringToString(dr["收费金额"]);
}
return AEinfo;
}
}
public PersonalStatisticsInfo GetPersonalStatisticsDD(DateTime beginTime, DateTime endTime)
{
string selfWorkCode = UserOperateContext.Current.Session_UsrInfo.WorkCode;
DataTable tableall = GetStatisticsDD(beginTime, endTime);
//在表里循环遍历，当某一行的PersonalCode跟session 中获取的ID 相等时，将那一行的数据取出，赋给Model，Return
PersonalStatisticsInfo AEinfo = new PersonalStatisticsInfo();
foreach (DataRow dr in tableall.Rows)
{
if (Convert.ToString(dr["WorkCode"]) == selfWorkCode)
{
AEinfo.PersonName = DBConvert.ConvertStringToString(dr["Name"]);
AEinfo.PTaskTimes = DBConvert.ConvertStringToString(dr["SendCarTimes"]);
AEinfo.PFirstAidTimes = DBConvert.ConvertStringToString(dr["FirstAidTimes"]);
AEinfo.PTransTimes = DBConvert.ConvertStringToString(dr["TransTimes"]);
AEinfo.PGoHomeTimes = DBConvert.ConvertStringToString(dr["GoHomeTimes"]);
AEinfo.PDays = DBConvert.ConvertStringToString(dr["Days"]);
AEinfo.PTaskTimesEveryDay = DBConvert.ConvertStringToString(dr["AvgDaySendCarTimes"]);
AEinfo.PAvgSendTime = DBConvert.ConvertStringToString(dr["AvgSendCarTime"]);
AEinfo.PAvgArriveTime = DBConvert.ConvertStringToString(dr["AvgArrivingSceneTime"]);
AEinfo.PAvgLocalAidTime = DBConvert.ConvertStringToString(dr["AvgSceneRescueTime"]);
AEinfo.PAvgTransTime = DBConvert.ConvertStringToString(dr["AvgTransportTime"]);
AEinfo.PAvgHospitalTransTime = DBConvert.ConvertStringToString(dr["AvgHospitalDeliveryTime"]);
AEinfo.PAvgReactTime = DBConvert.ConvertStringToString(dr["AvgResponseTime"]);
AEinfo.PAvgTurnTime = DBConvert.ConvertStringToString(dr["AvgRevolveTime"]);
}
}
return AEinfo;
}