/// <summary>
/// 获取任务对象
/// </summary>
public static C_TaskInfoDetail GetC_TaskInfoDetail(string Code)
{
//查询任务信息
SqlParameterTool sqltt = new SqlParameterTool();
sqltt.commandText.Append(@"SELECT Code=TT.任务编码,EventCode=事件编码,AcceptOrder=受理序号,TaskOrder=TT.任务流水号,UserOrder=TT.用户流水号,OperatorCode=责任调度人编码
,AmbulanceCode=TT.车辆编码,IsPerforming=是否执行中,CreateTaskTime=生成任务时刻,ReceiveCmdTime=接收命令时刻,AmbulanceLeaveTime=出车时刻,ArriveSceneTime=到达现场时刻
,LeaveSceneTime=离开现场时刻,ArriveHospitalTime=到达医院时刻,FinishTime=完成时刻,ReturnTime=返回站中时刻,HelpDistance=急救公里数,行驶公里数=行驶公里数
,IsNormalFinish=是否正常结束,AbnormalReasonId=异常结束原因编码,CureAmount=实际救治人数,IsFromStation=是否站内出动,Driver=TT.司机
,Doctor=TT.医生,Nurse=TT.护士,Litter=TT.担架工,Salver=TT.抢救员,ReassignTaskCode=改派前任务编码,RealSendAddr=实际送往地点,Remark=备注,CenterId=TT.中心编码
,TS.名称 as StationName,TA.车牌号码 as TradeMark,TA.实际标识 as RealSign
,TA.随车电话 as FollowTel,TZA.名称 as AmbulanceTypeName,TZT.名称 as AbnormalReasonName
FROM TTask TT
left join TStation TS on TS.编码=TT.分站编码
left join TAmbulance TA on TA.车辆编码 = TT.车辆编码
left join TZAmbulanceType TZA on TZA.编码 = TA.车辆类型编码
left join TZTaskAbendReason TZT on TZT.编码 = TT.异常结束原因编码
where TT.任务编码=@p0");
sqltt.commandObj.Add(Code);
using (MainDataContext dbContext = new MainDataContext(AppConfig.ConnectionStringDispatch))
{
C_TaskInfoDetail tt = dbContext.ExecuteQuery <C_TaskInfoDetail>(
sqltt.commandText.ToString()
, sqltt.commandObj.ToArray()).FirstOrDefault();
return(tt);
}
}
/// <summary>
/// 查询接收人反馈信息
/// </summary>
public static object GetRecs(int BGCode, string isRead)
{
SqlParameterTool sql = new SqlParameterTool();
sql.commandText.Append(string.Format(@"
select t.编码
,t.是否已阅
,t.查阅时间
,接收人=bw.Name
from TOfficeReceive t
left join B_WORKER bw on bw.ID=t.接收人编码
where t.办公编码=@p{0}", sql.i++));
sql.commandObj.Add(BGCode);
sql.AddObjectEqual("t.是否已阅", isRead);
sql.commandText.Append(@"
ORDER BY t.查阅时间");
using (MainDataContext dbContext = new MainDataContext())
{
var templist1 = dbContext.ExecuteQuery <C_OfficeReceive>(
sql.commandText.ToString()
, sql.commandObj.ToArray());
var templist2 = dbContext.ExecuteQuery <C_OfficeReceive>(
sql.commandText.ToString()
, sql.commandObj.ToArray());
long total = templist1.LongCount();
var result = new { total = total, rows = templist2.ToList() };
return(result);
}
}
/// <summary>
/// 获取受理对象
/// </summary>
public static C_AccInfo GetC_AccInfo(string eventCode, int orderNumber)
{
//查询受理信息
SqlParameterTool sqltac = new SqlParameterTool();
sqltac.commandText.Append(@"SELECT EventCode=事件编码,AcceptOrder=受理序号,TypeId=受理类型编码,DetailReasonId=受理类型子编码,AcceptPersonCode=责任受理人编码,AlarmTel=呼救电话
,HangUpTime=挂起时刻,RingTime=电话振铃时刻,AcceptBeginTime=开始受理时刻,AcceptEndTime=结束受理时刻,CommandTime=发送指令时刻,LocalAddr=现场地址,WaitAddr=等车地址
,SendAddr=送往地点,LocalAddrTypeId=往救地点类型编码,SendAddrTypeId=送往地点类型编码,LinkMan=联系人,LinkTel=联系电话,Extension=分机,PatientName=患者姓名,Sex=性别
,Age=年龄,Folk=民族,[National]=国籍,AlarmReason=主诉,Judge=病种判断,IllStateId=病情编码,IsNeedLitter=是否需要担架,PatientCount=患者人数,SpecialNeed=特殊要求,IsLabeled=是否标注
,X=X坐标,Y=Y坐标,AmbulanceList=派车列表,Remark=备注,AcceptType=TZET.名称,Dispatcher=TP.姓名
FROM TAcceptEvent TAE
left join TZAcceptEventType TZET on TZET.编码 = TAE.受理类型编码
left join TPerson TP on TP.编码 = TAE.责任受理人编码
where 事件编码 = @p0 and 受理序号=@p1");
sqltac.commandObj.Add(eventCode);
sqltac.commandObj.Add(orderNumber);
using (MainDataContext dbContext = new MainDataContext(AppConfig.ConnectionStringDispatch))
{
C_AccInfo tac = dbContext.ExecuteQuery <C_AccInfo>(
sqltac.commandText.ToString()
, sqltac.commandObj.ToArray()).FirstOrDefault();
return(tac);
}
}
private static SqlParameterTool tosql_Parameters(XmlNode xmlNode, int i)
{
SqlParameterTool sql1_p = new SqlParameterTool();
sql1_p.BeginInt = i;
//int begini=i;
if (xmlNode.ChildNodes.Count > 0)
{
//sql1_p.commandText.Append("(");
foreach (XmlNode xn in xmlNode.ChildNodes)
{
//if (xn.Attributes["radio2"].Value == "*")//sql中没有*，这里*代表特殊条件，在此条件下不能再加查询了
//{
//}
//else
{
string radio1 = xn.Attributes["radio1"].Value;
string columnName = xn.Attributes["columnName"].Value;
string radio2 = xn.Attributes["radio2"].Value;
string caseText = xn.Attributes["caseText"].Value;
if (radio2 == "like")
{
caseText = "%" + caseText + "%";
}
sql1_p.commandText.AppendFormat(" {0} ", radio1);
//if (begini == i)
//{
// sql1_p.commandText.Append("(");//在第一个循环体的 and 或 or 后添加括号
//}
if (xn.HasChildNodes)
{
sql1_p.commandText.Append("(");//含有孩子结点添加括号
}
sql1_p.commandText.AppendFormat("[{0}]{1} @p{2}", columnName, radio2, i);
sql1_p.commandParameters.Add(new System.Data.SqlClient.SqlParameter("@p" + i.ToString(), caseText));
i++; sql1_p.BeginInt++;
if (xn.HasChildNodes)
{
SqlParameterTool sql2_p = tosql_Parameters(xn, i);
sql1_p.commandText.Append(sql2_p.commandText);
sql1_p.commandParameters.AddRange(sql2_p.commandParameters);
i = sql2_p.BeginInt; sql1_p.BeginInt = i;
sql1_p.commandText.Append(")");
}
}
}
//sql1_p.commandText.Append(")");
}
return(sql1_p);
}
/// <summary>
/// 获取任务车辆对象
/// </summary>
public static List <C_AmbulanceStateTimeInfo> GetAmbulanceStateTimeLs(string Code)
{
//查询对应任务车辆状态变化信息
SqlParameterTool sqltast = new SqlParameterTool();
sqltast.commandText.Append(@"select Code=A.编码,TaskCode=A.任务编码,AmbuCode=A.车辆编码,RealSign=B.实际标识,WorkStateId=车辆状态编码,C.名称 as WorkStateName
,KeyPressTime=时刻值,SaveTime=记录时刻,SourceCode=操作来源编码,D.名称 as SourceName,OperationCode=操作员编码,E.姓名 as OperationName,JobCode=E.工号,IsOnline=车辆是否在线
from dbo.TAmbulanceStateTime A
left join dbo.TAmbulance B on A.车辆编码=B.车辆编码
left join dbo.TZAmbulanceState C on A.车辆状态编码=C.编码
left join dbo.TZOperationOrigin D on A.操作来源编码=D.编码
left join dbo.TPerson E on A.操作员编码=E.编码
where a.任务编码=@p0");
sqltast.commandObj.Add(Code);
using (MainDataContext dbContext = new MainDataContext(AppConfig.ConnectionStringDispatch))
{
List <C_AmbulanceStateTimeInfo> tastLs = dbContext.ExecuteQuery <C_AmbulanceStateTimeInfo>(
sqltast.commandText.ToString()
, sqltast.commandObj.ToArray()).ToList();
return(tastLs);
}
}
public static object GetOfficeList(int page, int rows, string order, string sort, string type, DateTime startTime, DateTime endTime, string title, string writer, int loginID)
{
endTime = endTime.AddDays(1);
SqlParameterTool sql = new SqlParameterTool();
sql.commandText.Append(@"
select
OfficeType=td.名称
,ID=tof.编码
,Title=tof.标题
,Writer=tof.作者
,CreateTime=tof.创建时间
,SendType=case tof.发送类型编码 when 1 then '全体人员' when 2 then '发送到部门' when 3 then '发送到个人' else '其它' end
,AnnexCount=(select count(*) from TOfficeAttachment tofa where tofa.办公编码=tof.编码)
,ReadCount=case when tof.发送类型编码=3 then (select count(*) from TOfficeReceive tofr where tofr.办公编码=tof.编码 and tofr.是否已阅=1) else null end
,IsSelf=case when tof.发送人编码=@p{0} then convert(bit,1) else convert(bit,0) end
from TOffice tof
left join TDictionary td on td.编码=tof.办公类型编码
");
sql.commandText.Append("where tof.创建时间>=").Append("@p" + sql.i++);
sql.commandText.Append(" and tof.创建时间<").Append("@p" + sql.i++);
sql.commandObj.Add(startTime);
sql.commandObj.Add(endTime);
//if (type != "")
if (!string.IsNullOrEmpty(type))
{
sql.AddObjectEqual("tof.办公类型编码", type);
}
sql.AddObjectLike("tof.作者", writer);
sql.AddObjectLike("tof.标题", title);
sql.commandText.Append(@"
and (tof.发送类型编码=1
or (tof.发送人编码=@p{0})
or (tof.发送类型编码=2 and exists(select * from f_B_ORGANIZATION_cid(
(select OrgID from B_WORKER_ORGANIZATION where WorkerID=@p{0})
) as b where ','+tof.接收部门编码+',' like '%,'+convert(varchar(10),b.id)+',%'))
or (tof.发送类型编码=3 and ','+tof.接收人编码+',' like ('%,'+convert(varchar(10),@p{0})+',%'))
)
ORDER BY tof.创建时间 desc");
string str = string.Format(sql.commandText.ToString(), sql.i++);
sql.commandObj.Add(loginID);
using (MainDataContext dbContext = new MainDataContext())
{
var templist1 = dbContext.ExecuteQuery <C_OfficeList>(
str
, sql.commandObj.ToArray());
var templist2 = dbContext.ExecuteQuery <C_OfficeList>(
str
, sql.commandObj.ToArray());
long total = templist1.LongCount();
var result = new { total = total, rows = templist2.Skip((page - 1) * rows).Take(rows).ToList() };
return(result);
}
//using (MainDataContext dbContext = new MainDataContext())
//{
// var list = from t in dbContext.TOffice
// join d in dbContext.TDictionary on t.办公类型编码 equals d.编码 into left_d
// from d in left_d.DefaultIfEmpty()
// where t.创建时间 >= startTime && t.创建时间 <= endTime
// select new {
// 办公类型编码 = t.办公类型编码,
// 办公类型 = d.名称,
// 编码 = t.编码,
// 标题 = t.标题,
// 作者 = t.作者,
// 创建时间 = t.创建时间,
// 发送类型 = t.发送类型编码 == 1 ? "全体人员" : t.发送类型编码 == 2 ? "发送到部门" : t.发送类型编码 == 3 ? "发送到个人" : "发送到个人",
// 接收人 = t.发送类型编码 == 1 ? t.接收人:t.接收部门,
// 附件个数 = (from a in dbContext.TOfficeAttachment where t.编码 == a.办公编码 select a).LongCount(),
// 阅读人数 = (from b in dbContext.TOfficeReceive where b.是否已阅 == true select b).LongCount(),
// };
//if (!string.IsNullOrEmpty(type) && type != "--请选择--")
//{
// list = list.Where(t => t.办公类型编码 == type);
//}
//if (!string.IsNullOrEmpty(writer))
//{
// list = list.Where(t => t.作者.Contains(writer));
//}
//if (!string.IsNullOrEmpty(title))
//{
// list = list.Where(t => t.标题.Contains(title));
//}
//long total = list.LongCount();
//list = list.OrderByDescending(t => t.创建时间);
//list = list.Skip((page - 1) * rows).Take(rows);
//var listOffice = list.ToList().Select(o => new
//{
// ID = o.编码,
// OfficeType = o.办公类型,
// Recipient = o.接收人,
// Title = o.标题,
// Writer = o.作者,
// CreateTime = o.创建时间.ToString("yyyy-MM-dd HH:mm:ss"),
// SendType = o.发送类型,
// AttachmentNum = o.附件个数,
// ReadNum = o.阅读人数,
//});
//var result = new { total = total, rows = listOffice.ToList() };
//return result;
//}
}
public static void GetExportSql(string selectColumnsXml, string tableName, string functionCaseXml, string inputCaseXml, out SqlParameterTool show, out List <int> ColumnsNumber, out List <string> ColumnsName)
{
show = new SqlParameterTool();
//------------------------------------------------select...
show.commandText.Append(@"select");
XmlDocument xmldocColumns = new XmlDocument();
XmlNode xmltablebodyColumns = xmldocColumns.CreateElement("tablebody");
xmltablebodyColumns.InnerXml = selectColumnsXml;
xmldocColumns.LoadXml("<?xml version=\"1.0\" encoding=\"utf-8\" ?><root></root>");
xmldocColumns.DocumentElement.AppendChild(xmltablebodyColumns);
ColumnsNumber = new List <int>(); //用来存储数字类型列 数字依次增加
ColumnsName = new List <string>(); //用来存储选择列名
int i = 0;
foreach (XmlNode xn in xmltablebodyColumns.ChildNodes)
{
show.commandText.AppendFormat(@"
[{0}],", xn.Attributes["Name"].Value);
ColumnsName.Add(xn.Attributes["Name"].Value);
switch (xn.Attributes["TypeName"].Value)
{
case "decimal":
case "numeric":
case "int":
case "float":
case "money":
ColumnsNumber.Add(i);
break;
}
i++;
}
show.commandText.Remove(show.commandText.Length - 1, 1);
//-------------------------------------------------------------from...
show.commandText.AppendFormat(@"
from [REPORT_{0}]", tableName);
if (!string.IsNullOrEmpty(functionCaseXml))
{
show.commandText.Append(@"(");
XmlDocument xmldocfunctionCase = new XmlDocument();
XmlNode xmltablebodyfunctionCase = xmldocfunctionCase.CreateElement("tablebody");
xmltablebodyfunctionCase.InnerXml = functionCaseXml;
xmldocfunctionCase.LoadXml("<?xml version=\"1.0\" encoding=\"utf-8\" ?><root></root>");
xmldocfunctionCase.DocumentElement.AppendChild(xmltablebodyfunctionCase);
foreach (XmlNode xn in xmltablebodyfunctionCase.ChildNodes)
{
string parameterName = "@p" + show.BeginInt;
show.commandText.Append(parameterName).Append(",");
show.AddObject(parameterName, xn.Attributes["value"].Value);
show.BeginInt++;
}
show.commandText.Remove(show.commandText.Length - 1, 1);
show.commandText.Append(@")");
}
//-------------------------------------------将此表名称转化为 a 方便 特殊条件下可能的关联
show.commandText.Append(@" as a");
//-------------------------------------------where...
if (!string.IsNullOrEmpty(inputCaseXml))
{
show.commandText.Append(@"
where");
XmlDocument xmldocinputCase = new XmlDocument();
XmlNode xmltablebodyinputCase = xmldocinputCase.CreateElement("tablebody");
xmltablebodyinputCase.InnerXml = inputCaseXml;
xmldocinputCase.LoadXml("<?xml version=\"1.0\" encoding=\"utf-8\" ?><root></root>");
xmldocinputCase.DocumentElement.AppendChild(xmltablebodyinputCase);
SqlParameterTool sql1_p = tosql_Parameters(xmltablebodyinputCase, show.BeginInt);
show.commandText.Append(sql1_p.commandText);
show.commandParameters.AddRange(sql1_p.commandParameters);
}
}