/// <summary>
/// 获取某用户对应关系的仓库ID集合
/// add2020-04-03 plq
/// </summary>
/// <param name="ID"></param>
/// <returns></returns>
public List <int> GetStorageIDListByUser(int ID)
{
List <int> list = new List <int>();
StringBuilder sb = new StringBuilder();
sb.Append(" select StorageID ");
sb.Append(@"
from FIXED_StoragePerson ");
sb.Append(@"
where 1=1 ");
WhereClauseUtility.AddIntEqual("UserID", ID, sb);
try
{
DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
int StorageID = Convert.ToInt32(dr["StorageID"].ToString());
list.Add(StorageID);
}
}
return(list);
}
catch (Exception ex)
{
LogUtility.Error("DealInnerCommDAL/GetStorageIDListByUser", "获取出错：" + ex.Message);
return(null);
}
}
/// <summary>
/// 获取仓库物联数据
/// </summary>
/// <param name="CangKuMingCheng"></param>
/// <returns></returns>
public DataTable GetStorageIOTDataTable(string CangKuMingCheng)
{
StringBuilder sb = new StringBuilder();
sb.Append(" select identity(int,1,1) as 序号,仓库ID=仓库编码,仓库名称=仓库名称,读写器ID=扫描仪串号 ");
sb.Append(@"
into #temp
from FIXED_Storage fs");
sb.Append(@"
where 1=1 ");
sb.Append(@"
and 是否有效=1 "); //add20200327
sb.Append(@"
and 父级仓库编码=" + Convert.ToInt32(Utility.AnchorEnum.EParentStorageID.分站)); //对应分站下的子仓库
WhereClauseUtility.AddStringLike("仓库名称", CangKuMingCheng, sb);
sb.Append(@"
order by 仓库编码 ");
sb.Append(@"
select A.* from #temp A order by 序号 ");
sb.Append(@"
drop table #temp ");
try
{
DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
return(ds.Tables[0]);
}
catch (Exception ex)
{
LogUtility.Error("DealDataGridViewQueryDAL/GetStorageIOTDataTable", "获取仓库物联数据出错：" + ex.Message);
return(null);
}
}
/// <summary>
/// 获取告警查询列表
/// </summary>
/// <param name="aqInfo"></param>
/// <param name="ref_errMsg_aqr"></param>
/// <returns></returns>
public List <Rfid_AlarmLogInfo> GetAlarmQueryList(AlarmQueryInfo aqInfo, ref string ref_errMsg_aqr)
{
int ckID = (aqInfo.CangKuBianMa == "" || aqInfo.CangKuBianMa == null) ? -1 : Convert.ToInt32(aqInfo.CangKuBianMa); //仓库编码
int typeID = (aqInfo.GaoJingLeiXingBianMa == "") ? -1 : Convert.ToInt32(aqInfo.GaoJingLeiXingBianMa); //告警类型编码
DateTime DateStart = Convert.ToDateTime(aqInfo.KaiShiShiJian); //开始时间
DateTime DateEnd = Convert.ToDateTime(aqInfo.JieShuShiJian).AddDays(1); //结束时间
List <Rfid_AlarmLogInfo> list = new List <Rfid_AlarmLogInfo>();
StringBuilder sb = new StringBuilder();
sb.Append(" select * ");
sb.Append(@"
from Rfid_AlarmLog fa");
sb.Append(@"
where 1=1 ");
if (ckID >= 0) //如果选了仓库
{
WhereClauseUtility.AddIntEqual("仓库ID", ckID, sb);
}
if (typeID >= 0) //如果选了告警类型
{
WhereClauseUtility.AddIntEqual("操作类型编码", typeID, sb);
}
WhereClauseUtility.AddDateTimeGreaterThan("记录时间", DateStart, sb);
WhereClauseUtility.AddDateTimeLessThan("记录时间", DateEnd, sb);
sb.Append(@"
order by 记录时间 ");
try
{
DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
Rfid_AlarmLogInfo info = new Rfid_AlarmLogInfo();
info.BianMa = Convert.ToInt32(dr["编码"].ToString());
info.JiLuShiJian = Convert.ToDateTime(dr["记录时间"].ToString());
info.strJiLuShiJian = info.JiLuShiJian.ToString("yyyy-MM-dd HH:mm:ss");
info.CaoZuoLeiXingBianMa = Convert.ToInt32(dr["操作类型编码"].ToString());
info.SheBeiID = dr["设备ID"].ToString();
info.SheBeiMingCheng = dr["设备名称"].ToString();
info.RFIDKaHao = dr["RFID卡号"].ToString();
info.CangKuID = Convert.ToInt32(dr["仓库ID"].ToString());
info.CangKuMingCheng = dr["仓库名称"].ToString();
info.DuXieQiID = dr["读写器ID"].ToString();
info.CangKuLeiXingBianMa = Convert.ToInt32(dr["仓库类型编码"].ToString());
info.ChuLiZhuangTaiBianMa = Convert.ToInt32(dr["处理状态编码"].ToString());
list.Add(info);
}
}
return(list);
}
catch (Exception ex)
{
ref_errMsg_aqr = "获取告警查询列表出错：" + ex.Message;
LogUtility.Error("RfidGiveAlarmDAL/GetAlarmQueryList", "获取告警查询列表出错：" + ex.Message);
return(null);
}
}
/// <summary>
/// 获取追溯查询数据
/// </summary>
/// <param name="sbId"></param>
/// <param name="DateStart"></param>
/// <param name="DateEnd"></param>
/// <returns></returns>
public DataTable GetRetrosQueryDataTable(string sbId, DateTime DateStart, DateTime DateEnd)
{
StringBuilder sb = new StringBuilder();
sb.Append(" select identity(int,1,1) as 序号,时间=记录时间,类型编码=操作类型编码,对象ID=仓库ID ");
sb.Append(" ,对象名称=仓库名称,读写器ID=读写器ID,设备ID=设备ID,设备名称=设备名称,RFID卡号=RFID卡号 ");
sb.Append(" ,处理状态编码=处理状态编码,处理时间=处理时间,操作人编码=操作人编码,操作人=pu.Name ");
//类型
sb.Append(@",类型=(case
when 操作类型编码=" + (int)Utility.AnchorEnum.ERfid_OperationType.初次绑定 + " then '初次绑定'");//对应AnchorEnum中枚举 ERfid_OperationType
sb.Append(@" when 操作类型编码=" + (int)Utility.AnchorEnum.ERfid_OperationType.物联绑定 + " then '物联绑定'");
sb.Append(@" when 操作类型编码=" + (int)Utility.AnchorEnum.ERfid_OperationType.物资不存在 + " then '物资不存在'");
sb.Append(@" when 操作类型编码=" + (int)Utility.AnchorEnum.ERfid_OperationType.物联解绑 + " then '物联解绑'");
sb.Append(@" when 操作类型编码=" + (int)Utility.AnchorEnum.ERfid_OperationType.丢失 + " then '丢失' ");
sb.Append(@" else '异常操作类型'
end ) ");
//处理状态
sb.Append(@",处理状态=(case
when 处理状态编码=" + (int)Utility.AnchorEnum.ERfid_ProcessingState.未处理 + " then '未处理'");//对应AnchorEnum中枚举 ERfid_ProcessingState
sb.Append(@" when 处理状态编码=" + (int)Utility.AnchorEnum.ERfid_ProcessingState.已绑定设备 + " then '已绑定设备'");
sb.Append(@" when 处理状态编码=" + (int)Utility.AnchorEnum.ERfid_ProcessingState.已物联绑定 + " then '已物联绑定'");
sb.Append(@" when 处理状态编码=" + (int)Utility.AnchorEnum.ERfid_ProcessingState.已解绑 + " then '已解绑'");
sb.Append(@" when 处理状态编码=" + (int)Utility.AnchorEnum.ERfid_ProcessingState.已丢失 + " then '已丢失' ");
sb.Append(@" else '异常处理状态'
end ) ");
sb.Append(@"
into #temp
from Rfid_AlarmLog ral");
sb.Append(@"
left join P_User pu on pu.ID=ral.操作人编码 ");
sb.Append(@"
where 1=1 ");
WhereClauseUtility.AddStringEqual("设备ID", sbId, sb); //筛选设备
WhereClauseUtility.AddDateTimeGreaterThan("记录时间", DateStart, sb);
WhereClauseUtility.AddDateTimeLessThan("记录时间", DateEnd, sb);
sb.Append(@"
order by 记录时间 ");
// sb.Append(@"
// select A.* from #temp A order by 序号 ");
sb.Append(@"
select A.序号,A.时间,物品名称=A.设备名称,绑定关系=A.类型,绑定对象=A.对象名称
,A.处理状态,A.处理时间,A.操作人
from #temp A order by 序号 ");
sb.Append(@"
drop table #temp ");
try
{
DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
return(ds.Tables[0]);
}
catch (Exception ex)
{
LogUtility.Error("DealDataGridViewQueryDAL/GetRetrosQueryDataTable", "获取告警查询数据出错：" + ex.Message);
return(null);
}
}
/// <summary>
/// 获取追溯查询列表
/// </summary>
/// <param name="aqqInfo"></param>
/// <param name="ref_errMsg_rqr"></param>
/// <returns></returns>
public List <Rfid_AlarmLogInfo> GetRetrospectiveQueryList(AlarmQueryInfo aqqInfo, ref string ref_errMsg_rqr)
{
string sbID = aqqInfo.SheBeiID; //设备ID
DateTime DateStart = Convert.ToDateTime(aqqInfo.KaiShiShiJian); //开始时间
DateTime DateEnd = Convert.ToDateTime(aqqInfo.JieShuShiJian).AddDays(1); //结束时间
List <Rfid_AlarmLogInfo> list = new List <Rfid_AlarmLogInfo>();
StringBuilder sb = new StringBuilder();
sb.Append(" select * ");
sb.Append(@"
from Rfid_AlarmLog fa");
sb.Append(@"
where 1=1 ");
WhereClauseUtility.AddDateTimeGreaterThan("记录时间", DateStart, sb);
WhereClauseUtility.AddDateTimeLessThan("记录时间", DateEnd, sb);
if (!String.IsNullOrEmpty(sbID))
{
sb.Append(@"
and 设备ID='" + sbID + "' "); //筛选对应设备的告警记录
}
sb.Append(@"
order by 记录时间 ");
try
{
DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
Rfid_AlarmLogInfo info = new Rfid_AlarmLogInfo();
info.BianMa = Convert.ToInt32(dr["编码"].ToString());
info.JiLuShiJian = Convert.ToDateTime(dr["记录时间"].ToString());
info.strJiLuShiJian = info.JiLuShiJian.ToString("yyyy-MM-dd HH:mm:ss");
info.CaoZuoLeiXingBianMa = Convert.ToInt32(dr["操作类型编码"].ToString());
info.SheBeiID = dr["设备ID"].ToString();
info.SheBeiMingCheng = dr["设备名称"].ToString();
info.RFIDKaHao = dr["RFID卡号"].ToString();
info.CangKuID = Convert.ToInt32(dr["仓库ID"].ToString());
info.CangKuMingCheng = dr["仓库名称"].ToString();
info.DuXieQiID = dr["读写器ID"].ToString();
info.CangKuLeiXingBianMa = Convert.ToInt32(dr["仓库类型编码"].ToString());
info.ChuLiZhuangTaiBianMa = Convert.ToInt32(dr["处理状态编码"].ToString());
list.Add(info);
}
}
return(list);
}
catch (Exception ex)
{
ref_errMsg_rqr = "获取追溯查询列表出错：" + ex.Message;
LogUtility.Error("RfidGiveAlarmDAL/GetRetrospectiveQueryList", "获取追溯查询列表出错：" + ex.Message);
return(null);
}
}
private List <M_Dictionary> GetDictionary(string Conn, string code)
{
M_Dictionary info;
List <M_Dictionary> list = new List <M_Dictionary>();
StringBuilder strSQL = new StringBuilder();
if (m_TableName == "TStation")
{
strSQL.Append(" select 编码,名称,顺序号,是否有效 from " + m_TableName + " ");
strSQL.Append(" where 是否有效=1 ");
WhereClauseUtility.AddIntEqual("中心编码", Convert.ToInt32(code), strSQL);
strSQL.Append(" order by 顺序号");
}
else if (m_TableName == "TAmbulance")
{
strSQL.Append(" select 编码=车辆编码,名称=实际标识,顺序号=1,是否有效 from " + m_TableName + " ");
strSQL.Append(" where 是否有效=1 ");
WhereClauseUtility.AddStringEqual("分站编码", code, strSQL);
strSQL.Append(" order by 实际标识");
}
else if (m_TableName == "TPerson")
{
strSQL.Append(" select 编码,名称=姓名,顺序号,是否有效 from " + m_TableName + " ");
strSQL.Append(" where 是否有效=1 and 类型编码=2 order by 姓名");
}
else
{
strSQL.Append(" select 编码,名称,顺序号,是否有效 from " + m_TableName + " ");
strSQL.Append(" where 是否有效=1 order by 顺序号");
}
try
{
using (DataSet ds = SqlHelper.ExecuteDataSet(Conn, CommandType.Text, strSQL.ToString(), null))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
info = new M_Dictionary();
info.ID = DBConvert.ConvertStringToString(dr["编码"]);
info.Name = DBConvert.ConvertStringToString(dr["名称"]);
info.SN = Convert.ToInt32(dr["顺序号"]);
info.IsActive = Convert.ToBoolean(dr["是否有效"]);
list.Add(info);
}
return(list);
}
}
catch (Exception)
{
//Log4Net.LogError("DictionaryDAL/GetDictionary", ex.Message);
return(null);
}
}
/// <summary>
/// 获取告警列表
/// </summary>
/// <param name="ref_errMsg_gga"></param>
/// <returns></returns>
public List <Rfid_AlarmLogInfo> GetGiveAlarmList(int UserID, ref string ref_errMsg_gga)
{
List <Rfid_AlarmLogInfo> list = new List <Rfid_AlarmLogInfo>();
StringBuilder sb = new StringBuilder();
sb.Append(" select * ");
sb.Append(@"
from Rfid_AlarmLog fa");
sb.Append(@"
where 1=1 ");
sb.Append(@"
and 是否有效=1 "); //add2020-04-10 plq 添加是否有效过滤--用来过滤历史告警数据
//List<string> sIDList = GetStorageIDListByUserID(UserID);
//string s1 = "'" + string.Join("','", sIDList.ToArray()) + "'";
List <int> sIDList = GetStorageIDListByUserID(UserID);
string s1 = "" + string.Join(",", sIDList.ToArray()) + "";
WhereClauseUtility.AddInSelectQuery("仓库ID", s1, sb);
sb.Append(@"
order by 处理状态编码 desc,记录时间 ");
try
{
DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
Rfid_AlarmLogInfo info = new Rfid_AlarmLogInfo();
info.BianMa = Convert.ToInt32(dr["编码"].ToString());
info.JiLuShiJian = Convert.ToDateTime(dr["记录时间"].ToString());
info.strJiLuShiJian = info.JiLuShiJian.ToString("yyyy-MM-dd HH:mm:ss");
info.CaoZuoLeiXingBianMa = Convert.ToInt32(dr["操作类型编码"].ToString());
info.SheBeiID = dr["设备ID"].ToString();
info.SheBeiMingCheng = dr["设备名称"].ToString();
info.RFIDKaHao = dr["RFID卡号"].ToString();
info.CangKuID = Convert.ToInt32(dr["仓库ID"].ToString());
info.CangKuMingCheng = dr["仓库名称"].ToString();
info.DuXieQiID = dr["读写器ID"].ToString();
info.CangKuLeiXingBianMa = Convert.ToInt32(dr["仓库类型编码"].ToString());
info.ChuLiZhuangTaiBianMa = Convert.ToInt32(dr["处理状态编码"].ToString());
list.Add(info);
}
}
return(list);
}
catch (Exception ex)
{
ref_errMsg_gga = "获取告警列表出错：" + ex.Message;
LogUtility.Error("RfidGiveAlarmDAL/GetGiveAlarmList", "获取告警列表出错：" + ex.Message);
return(null);
}
}
public List <CommonTree> GetMSDictionaryTreeInfoList(string tableName, string ParentID, string TypeID)
{
try
{
StringBuilder sbSQL = new StringBuilder();
if (TypeID == "")
{
//if (tableName == "M_ZICDNew" || tableName == "M_ZSymptomPendingInvestigation")
//{
sbSQL.Append(" select id=CONVERT(varchar(20),td.ID),td.Name,ParentID=CONVERT(varchar(20),td.ParentID),td.SN,td.IsActive,TypeID='',PinYin=isnull(PinYin,8888) ");
//}
//else
//{
// sbSQL.Append(" select id=CONVERT(varchar(20),td.ID),td.Name,ParentID=CONVERT(varchar(20),td.ParentID),td.SN,td.IsActive,TypeID='',PinYin='' ");
//}
}
else
{
sbSQL.Append(" select id=td.ID,td.Name,td.ParentID,td.SN,td.IsActive,td.TypeID,PinYin='' ");
}
sbSQL.Append(" from " + tableName + " td ");
sbSQL.Append(" where td.IsActive=1 ");
//WhereClauseUtility.AddStringEqual("td.ParentID", ParentID, sbSQL);
if (ParentID != "")
{
if (TypeID == "")
{
sbSQL.Append(" and td.ParentID=" + ParentID + "");
}
else
{
sbSQL.Append(" and td.ParentID='" + ParentID + "'");
}
}
WhereClauseUtility.AddStringEqual("td.TypeID", TypeID, sbSQL);
sbSQL.Append(" order by SN ");
List <CommonTree> Templist = db.Database.SqlQuery <CommonTree>(sbSQL.ToString()).ToList();
return(Templist);
}
catch (Exception ex)
{
LogUtility.Error("M_DictionaryTreeDAL/GetMSDictionaryTreeInfoList()", ex.ToString());
return(null);
}
}
public object GetList(int pageSize, int pageIndex, string name, string workCode, string depID, string roletypeName)
{
List <I_StoragePersonExt> list = new List <I_StoragePersonExt>();
StringBuilder strSQL = new StringBuilder();
strSQL.Append(@"select distinct identity(int,1,1) as 行号, ID=u.ID, Dep=d.Name, roletypeName=dbo.fmerg_personrole(u.ID),Name=u.Name,WorkCode=u.WorkCode,
storage=dbo.fmerg_personstorage(u.ID) into #temp
from P_UserRole pur
left join P_User u on pur.UserID = u.ID
left join P_Role r on r.ID = pur.RoleID
left join P_Department d on d.ID = r.DepID
where pur.IsActive = 1");
WhereClauseUtility.AddStringEqual("u.Name", name, strSQL);
WhereClauseUtility.AddStringEqual("u.WorkCode", workCode, strSQL);
WhereClauseUtility.AddStringEqual("d.Name", depID, strSQL);
WhereClauseUtility.AddStringEqual("r.ID", roletypeName, strSQL);
strSQL.Append(" order by u.ID ");
strSQL.Append("select top " + pageSize + " a.* from #temp a where 行号>" + (pageIndex - 1) * pageSize + " order by 行号 ");
strSQL.Append(" SELECT COUNT(*) FROM #temp t");
strSQL.Append(" drop table #temp");
DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, strSQL.ToString(), null);
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
I_StoragePersonExt info = new I_StoragePersonExt();
info.uid = Convert.ToInt32(dr["ID"]);
info.roleName = dr["roletypeName"].ToString();
info.userName = dr["Name"].ToString();
info.storage = dr["storage"].ToString();
info.workCode = dr["WorkCode"].ToString();
info.depName = dr["Dep"].ToString();
list.Add(info);
}
}
int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
var result = new { total = total, rows = list };
return(result);
}
/// <summary>
/// 获取车辆物联列表
/// </summary>
/// <param name="ChePaiHao"></param>
/// <returns></returns>
public List <RfidStorage_AmbulanceInfo> GetCarIOTList(string ChePaiHao)
{
List <RfidStorage_AmbulanceInfo> list = new List <RfidStorage_AmbulanceInfo>();
StringBuilder sb = new StringBuilder();
sb.Append(" select 车辆ID=仓库编码,车牌号=仓库名称,读写器ID=扫描仪串号 ");
sb.Append(@"
from FIXED_Storage fs");
sb.Append(@"
where 1=1 ");
sb.Append(@"
and 是否有效=1 ");
sb.Append(@"
and 父级仓库编码=" + Convert.ToInt32(Utility.AnchorEnum.EParentStorageID.车辆)); //对应车辆下的子仓库
//WhereClauseUtility.AddStringEqual("仓库名称", ChePaiHao, sb);
WhereClauseUtility.AddStringLike("仓库名称", ChePaiHao, sb);
sb.Append(@"
order by 仓库编码 ");
try
{
DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
RfidStorage_AmbulanceInfo info = new RfidStorage_AmbulanceInfo();
info.CangKuID = dr["车辆ID"].ToString();
info.ChePaiHao = dr["车牌号"].ToString();
info.DuXieQiID = dr["读写器ID"].ToString();
list.Add(info);
}
}
return(list);
}
catch (Exception ex)
{
LogUtility.Error("DealDataGridViewQueryDAL/GetCarIOTList", "获取车辆物联列表出错：" + ex.Message);
return(null);
}
}
public List <M_Dictionary> GetMSDictionaryInfos(string typeCode, string isPatient)
{
M_Dictionary info;
List <M_Dictionary> list = new List <M_Dictionary>();
StringBuilder sbSQL = new StringBuilder();
sbSQL.Append(" select td.ID,td.Name,td.TypeID,Type=tdt.Description,td.SN,td.IsActive ");
//SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.ConnectionPatientRecordTransaction);//取管理库的链接字符串
//是病历字典表
if (isPatient == "1")
{
sbSQL.Append(" from dbo.M_Dictionary td ");
sbSQL.Append(" left join dbo.M_DictionaryType tdt on tdt.TypeID=td.TypeID ");
sbSQL.Append(" where td.IsActive=1 ");
WhereClauseUtility.AddStringInSelectQuery("td.TypeID", typeCode, sbSQL);//20150723修改为允许一次查询多个类
}
else
{
sbSQL.Append(" from dbo.TDictionary td ");
sbSQL.Append(" left join dbo.TDictionaryType tdt on tdt.TypeID=td.TypeID ");
sbSQL.Append(" where td.IsActive=1 ");//具体再修改
WhereClauseUtility.AddStringEqual("td.TypeID", typeCode, sbSQL);
}
sbSQL.Append("order by td.SN ");
using (DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sbSQL.ToString(), null))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
info = new M_Dictionary();
info.ID = DBConvert.ConvertStringToString(dr["ID"]);
info.Name = DBConvert.ConvertStringToString(dr["Name"]);
info.TypeID = DBConvert.ConvertStringToString(dr["TypeID"]);
info.SN = Convert.ToInt32(dr["SN"]);
info.IsActive = Convert.ToBoolean(dr["IsActive"]);
list.Add(info);
}
return(list);
}
}
public object GetOverdue(int page, int rows, DateTime startTime, DateTime endTime, string mTypeId, string mName, int remainTime, string mCode)
{
StringBuilder sb = new StringBuilder();
sb.Append(@"select identity(int,1,1) as 行号,
MTypeID = td1.Name,
Name = m.Name,
MCode = m.MCode,
ValidDate = s.ValidityDate,
EntryCode = ed.EntryCode,
EntryData = ed.EntryDate,
Manufacturer = m.Manufacturer,
Vendor = m.Vendor,
Unit = m.Unit,
Specification = m.Specification,
Storage = td2.Name,
Surplus = s.Surplus,
isOverdue = (case when s.ValidityDate < convert(char(10),getdate(),120) then 1 else 0 end)
into #temp
from I_Surplus s
left join I_Material m on m.ID = s.MaterialID
left join I_EntryDetail ed on ed.MaterialID = m.ID
left join TDictionary td1 on td1.ID = m.MTypeID
left join TDictionary td2 on td2.ID = s.StorageCode " );
WhereClauseUtility.AddDateTimeGreaterThan("ed.EntryDate", startTime, sb);
WhereClauseUtility.AddDateTimeLessThan("ed.EntryDate", endTime, sb);
WhereClauseUtility.AddStringLike("m.Name", mName, sb);
//WhereClauseUtility.AddStringEqual("m.MTypeID", mTypeId, sb);
if (!string.IsNullOrEmpty(mTypeId))
{
sb.Append(" AND m.MTypeID = '" + mTypeId + "' or td1.ParentID = '" + mTypeId + "' ");
}
WhereClauseUtility.AddStringEqual("m.mCode", mCode, sb);
//将现在获取的时间DateTime.Now，加上告警天数的值，与有效期做比较，有效期小于这个值。
DateTime times = Convert.ToDateTime(DateTime.Now.AddDays(remainTime).ToString("yyyy-MM-dd"));
WhereClauseUtility.AddDateTimeLessThan("s.ValidityDate", times, sb);
sb.Append(@"select top " + rows + " A.* from #temp A where 行号>" + (page - 1) * rows + " order by 行号 ");
sb.Append(@"SELECT count(*) FROM #temp t ");
sb.Append("drop table #temp ");
DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
List <I_OverdueExt> list = new List <I_OverdueExt>();
if (ds.Tables[0].Rows.Count > 0)
{
I_OverdueExt info = new I_OverdueExt();
foreach (DataRow dr in ds.Tables[0].Rows)
{
info = new I_OverdueExt();
info.MTypeID = dr["MTypeID"].ToString();
info.Name = dr["Name"].ToString();
info.MCode = dr["MCode"].ToString();
info.ValidDate = Convert.ToDateTime(dr["ValidDate"]);
info.EntryCode = dr["EntryCode"].ToString();
info.EntryData = Convert.ToDateTime(dr["EntryData"]);
info.Manufacturer = dr["Manufacturer"].ToString();
info.Vendor = dr["Vendor"].ToString();
info.Unit = dr["Unit"].ToString();
info.Specification = dr["Specification"].ToString();
info.Storage = dr["Storage"].ToString();
info.Surplus = Convert.ToDouble(dr["Surplus"]);
info.isOverdue = Convert.ToBoolean(dr["isOverdue"]);
list.Add(info);
}
}
else
{
list = null;
var resultK = new { total = 0, rows = 0 };//当查询没有数据返回;
return(resultK);
}
int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
var result = new { total = total, rows = list };
//return list;
return(result);
}
/// <summary>
/// 获取告警列表数据源
/// </summary>
/// <param name="ckId"></param>
/// <param name="gjId"></param>
/// <param name="clID"></param>
/// <returns></returns>
public DataTable GetAlarmDataTable(string ckId, string gjId, string clID)
{
StringBuilder sb = new StringBuilder();
sb.Append(" select identity(int,1,1) as 序号,时间=记录时间,类型编码=操作类型编码,对象ID=仓库ID ");
sb.Append(" ,对象名称=仓库名称,读写器ID=读写器ID,设备ID=设备ID,设备名称=设备名称,RFID卡号=RFID卡号 ");
sb.Append(" ,处理状态编码=处理状态编码,处理时间=处理时间,操作人编码=操作人编码,操作人=pu.Name ");
//类型
sb.Append(@",类型=(case
when 操作类型编码=" + (int)Utility.AnchorEnum.ERfid_OperationType.初次绑定 + " then '初次绑定'");//对应AnchorEnum中枚举 ERfid_OperationType
sb.Append(@" when 操作类型编码=" + (int)Utility.AnchorEnum.ERfid_OperationType.物联绑定 + " then '物联绑定'");
sb.Append(@" when 操作类型编码=" + (int)Utility.AnchorEnum.ERfid_OperationType.物资不存在 + " then '物资不存在'");
sb.Append(@" when 操作类型编码=" + (int)Utility.AnchorEnum.ERfid_OperationType.物联解绑 + " then '物联解绑'");
sb.Append(@" when 操作类型编码=" + (int)Utility.AnchorEnum.ERfid_OperationType.丢失 + " then '丢失' ");
sb.Append(@" else '异常操作类型'
end ) ");
//处理状态
sb.Append(@",处理状态=(case
when 处理状态编码=" + (int)Utility.AnchorEnum.ERfid_ProcessingState.未处理 + " then '未处理'");//对应AnchorEnum中枚举 ERfid_ProcessingState
sb.Append(@" when 处理状态编码=" + (int)Utility.AnchorEnum.ERfid_ProcessingState.已绑定设备 + " then '已绑定设备'");
sb.Append(@" when 处理状态编码=" + (int)Utility.AnchorEnum.ERfid_ProcessingState.已物联绑定 + " then '已物联绑定'");
sb.Append(@" when 处理状态编码=" + (int)Utility.AnchorEnum.ERfid_ProcessingState.已解绑 + " then '已解绑'");
sb.Append(@" when 处理状态编码=" + (int)Utility.AnchorEnum.ERfid_ProcessingState.已丢失 + " then '已丢失' ");
sb.Append(@" else '异常处理状态'
end ) ");
sb.Append(@"
into #temp
from Rfid_AlarmLog ral");
sb.Append(@"
left join P_User pu on pu.ID=ral.操作人编码 ");
sb.Append(@"
where 1=1 ");
sb.Append(@"
and 是否有效=1 "); //add2020-04-10 plq 添加是否有效过滤--用来过滤历史告警数据
//WhereClauseUtility.AddDateTimeGreaterThan("记录时间", DateStart, sb);
//WhereClauseUtility.AddDateTimeLessThan("记录时间", DateEnd, sb);
if (!String.IsNullOrEmpty(ckId))
{
WhereClauseUtility.AddIntEqual("仓库ID", Convert.ToInt32(ckId), sb); //筛选仓库
}
if (!String.IsNullOrEmpty(gjId))
{
WhereClauseUtility.AddIntEqual("操作类型编码", Convert.ToInt32(gjId), sb); //筛选告警类型
}
if (!String.IsNullOrEmpty(clID))
{
WhereClauseUtility.AddIntEqual("处理状态编码", Convert.ToInt32(clID), sb); //筛选处理状态
}
sb.Append(@"
order by 记录时间,处理状态编码 desc ");
// sb.Append(@"
// select A.* from #temp A order by 序号 ");
sb.Append(@"
select A.序号,A.时间,A.类型,A.对象名称,A.设备名称,A.RFID卡号,A.处理状态
,A.处理时间,A.操作人
from #temp A order by 序号 ");
sb.Append(@"
drop table #temp ");
try
{
DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
return(ds.Tables[0]);
}
catch (Exception ex)
{
LogUtility.Error("DealDataGridViewQueryDAL/GetAlarmDataTable", "获取告警数据出错：" + ex.Message);
return(null);
}
}
public static object GetNoticeList(int page, int rows, string order, string sort, DateTime startTime, DateTime endTime,
int sendType, string station, string vehicle, Anchor.FA.Utility.ButtonPower bp, C_WorkerDetail UserInfo)
{
using (MainDataContext dbContext = new MainDataContext())
{
StringBuilder strSQL = new StringBuilder();
strSQL.Append("SELECT distinct ID=A.编码,Num=A.编码,content=A.内容,sendTime=A.发送时刻,A.操作员编码,worker=P.姓名,sendList=dbo.GetStrRecAmb(A.编码),typeName=tznt.名称,typeCode=A.类型编码 ");
strSQL.Append(@",backCount=(select COUNT(*) from MobileGet
where Mobile in (select 目的地编码 from TNoticeLog where 通知单编码=A.编码)
and [GetDate] > (select 发送时刻 from TNotice where 编码=A.编码)
and [GetDate] < (select dateadd(n,30,发送时刻) from TNotice where 编码=A.编码)) ");
strSQL.Append("from TNotice A ");
//strSQL.Append("left join B_WORKER_ROLE B on rtrim(A.操作员编码)=rtrim(B.TPerson编码) and rtrim(B.TPerson编码) !='' ");
//strSQL.Append("left join B_WORKER W on B.WorkerID=W.ID ");
strSQL.Append("inner join TPerson P on A.操作员编码=P.编码 ");
strSQL.Append("inner join TStation S on P.分站编码=S.编码 ");
strSQL.Append("left join TZNoticeType tznt on A.类型编码=tznt.编码 ");
StringBuilder sbWhereClause = new StringBuilder();
//车辆通知0、台通知1、短信通知2、分中心通知3、分站通知4、收到短信5
switch (sendType)
{
case 0: //车辆通知
if (vehicle != "-1") //选择了车：只查询车辆通知
{
strSQL.Append(" inner join TNoticeLog L on A.编码=L.通知单编码 and L.目的地编码 = '" + vehicle + "' and A.类型编码=0 ");
}
else if (station.Trim() != "-1") //没选车，选了分站
{
strSQL.Append(" inner join TNoticeLog L on A.编码=L.通知单编码 and (L.目的地编码 in( select 车辆编码 from TAmbulance where 分站编码='" + station + "') and A.类型编码=0) "); //车辆通知
}
WhereClauseUtility.AddIntEqual("A.类型编码", sendType, sbWhereClause);
break;
case 1: //台通知
WhereClauseUtility.AddIntEqual("A.类型编码", sendType, sbWhereClause);
break;
case 2: //短信通知
if (station.Trim() != "-1") //选了分站
{
strSQL.Append(" inner join TNoticeLog L on A.编码=L.通知单编码 and ( L.目的地编码 in(select EmpNo from B_WORKER_ROLE");
strSQL.Append(" where WorkerID in (select wo.WorkerID from B_WORKER_ORGANIZATION wo left join B_ORGANIZATION b on wo.OrgID = b.ID where b.编码='" + station + "')) and A.类型编码=2) "); //短信通知2
}
WhereClauseUtility.AddIntEqual("A.类型编码", sendType, sbWhereClause);
break;
case 4: //分站通知
if (station.Trim() != "-1") //选了分站
{
strSQL.Append(" inner join TNoticeLog L on A.编码=L.通知单编码 and ( L.目的地编码 ='" + station + "' and A.类型编码=4)"); //分站通知4
}
WhereClauseUtility.AddIntEqual("A.类型编码", sendType, sbWhereClause);
break;
default: //没有选择发送类型
if (vehicle != "-1") //选择了车：只查询车辆通知
{
strSQL.Append(" inner join TNoticeLog L on A.编码=L.通知单编码 and L.目的地编码 = '" + vehicle + "' and A.类型编码=0 ");
sbWhereClause.Append(" where A.类型编码=0 ");
}
else if (station.Trim() != "-1") //没选车，选了分站
{
strSQL.Append(" inner join TNoticeLog L on A.编码=L.通知单编码 ");
strSQL.Append(" and (L.目的地编码 in( select 车辆编码 from TAmbulance where 分站编码='" + station + "') and A.类型编码=0 "); //车辆通知
strSQL.Append(" or L.目的地编码 in(select EmpNo from B_WORKER_ROLE"); //短信通知2
strSQL.Append(" where WorkerID in (select wo.WorkerID from B_WORKER_ORGANIZATION wo left join B_ORGANIZATION b on wo.OrgID = b.ID where b.编码='" + station + "')) and A.类型编码=2 "); //短信通知2
strSQL.Append(" or L.目的地编码 ='" + station + "' and A.类型编码=4)"); //分站通知4
sbWhereClause.Append(" where A.类型编码 in(0,2,4) ");
}
break;
}
WhereClauseUtility.AddDateTimeGreaterThan("A.发送时刻", startTime, sbWhereClause);
WhereClauseUtility.AddDateTimeLessThan("A.发送时刻", endTime, sbWhereClause);
switch (bp.GetGroupRangePower("searchBound"))
{
case "SearchAll":
break;
case "SearchCenter": //查找所属分中心
WhereClauseUtility.AddIntEqual("S.中心编码", UserInfo.CenterCode, sbWhereClause);
break;
default: //没有设置查询权限
return(null);
//break;
}
strSQL.Append(sbWhereClause);
strSQL.Append(" order by A.发送时刻 desc");
string sql = strSQL.ToString();
var list1 = dbContext.ExecuteQuery <C_Notice>(sql);
var list2 = dbContext.ExecuteQuery <C_Notice>(sql);
long total = list1.LongCount();
list2 = list2.OrderByDescending(t => t.sendTime);
list2 = list2.Skip((page - 1) * rows).Take(rows);
return(new { total = total, rows = list2.ToList() });
}
}
/// <summary>
/// 事件信息列表
/// </summary>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="start"></param>
/// <param name="end"></param>
/// <param name="mainSuit"></param>
/// <param name="telephoneNumber"></param>
/// <param name="localAddress"></param>
/// <param name="patientName"></param>
/// <param name="sendAddress"></param>
/// <param name="dispatcher"></param>
/// <param name="driver"></param>
/// <param name="doctor"></param>
/// <param name="nurse"></param>
/// <param name="eventType"></param>
/// <param name="illnessJudgment"></param>
/// <param name="eventCode"></param>
/// <param name="station"></param>
/// <param name="ambulanceCode"></param>
/// <param name="eventSource"></param>
/// <returns></returns>
public object GetEventInfoList(int pageSize, int pageIndex, DateTime start, DateTime end, string mainSuit, string telephoneNumber, string localAddress, string patientName, string sendAddress,
string dispatcher, string driver, string doctor, string nurse, string stretcher, string eventType, string illnessJudgment, string eventCode, string station, string ambulanceCode,
string eventSource, E_StatisticsPermisson em, string selfWorkCode, string selfCenterID, string selfStationID)
{
List <EventInfo> list = new List <EventInfo>();
StringBuilder sb = new StringBuilder();
sb.Append(@"select distinct identity(int,1,1) as 行号,tae.事件编码,首次呼救电话,事件名称=现场地址+患者姓名,受理次数,首次调度员编码
,首次受理时刻,事件类型=tzaet.名称,事件来源=tzaeo.名称,派车次数=(select count(*) from dbo.TTask tt where tt.事件编码=tae.事件编码)
,正常完成=(select count(*) from dbo.TTask tt where tt.事件编码=tae.事件编码 and tt.是否正常结束=1)
into #tempa
from TAlarmEvent tae
left join TZAlarmEventType tzaet on tae.事件类型编码 = tzaet.编码
left join TZAlarmEventOrigin tzaeo on tae.事件来源编码 = tzaeo.编码
left join TTask tt on tae.事件编码 = tt.事件编码
left join TTaskPersonLink ttpl on tt.任务编码 = ttpl.任务编码
left join TStation ts on ts.编码 = tt.分站编码
where 是否测试=0 ");
WhereClauseUtility.AddStringEqual("tae.事件编码", eventCode, sb);
WhereClauseUtility.AddStringLike("tae.主诉", mainSuit, sb);
WhereClauseUtility.AddStringLike("tae.首次呼救电话", telephoneNumber, sb);
WhereClauseUtility.AddStringLike("tae.现场地址", localAddress, sb);
WhereClauseUtility.AddStringEqual("tae.患者姓名", patientName, sb);
WhereClauseUtility.AddStringLike("tae.送往地点", sendAddress, sb);
WhereClauseUtility.AddStringEqual("tae.首次调度员编码", dispatcher, sb);
//WhereClauseUtility.AddStringEqual("ttpl.人员编码", driver, sb);
//WhereClauseUtility.AddStringEqual("ttpl.人员编码", doctor, sb);
//WhereClauseUtility.AddStringEqual("ttpl.人员编码", nurse, sb);
//WhereClauseUtility.AddStringEqual("ttpl.人员编码", stretcher, sb);
WhereClauseUtility.AddStringEqual("tae.事件类型编码", eventType, sb);
WhereClauseUtility.AddStringEqual("tae.病情编码", illnessJudgment, sb);
WhereClauseUtility.AddStringEqual("tt.车辆编码", ambulanceCode, sb);
WhereClauseUtility.AddDateTimeGreaterThan("tae.首次受理时刻", start, sb);
WhereClauseUtility.AddDateTimeLessThan("tae.首次受理时刻", end, sb);
// WhereClauseUtility.AddStringEqual("ts.名称", station, sb);
WhereClauseUtility.AddIntEqual("tae.事故类型编码", -1, sb);
WhereClauseUtility.AddStringEqual("tae.事件来源编码", eventSource, sb);
if (em == E_StatisticsPermisson.None)
{
return(null);
}
else if (em == E_StatisticsPermisson.ALL)
{
WhereClauseUtility.AddStringEqual("ttpl.人员编码", driver, sb);
WhereClauseUtility.AddStringEqual("ttpl.人员编码", doctor, sb);
WhereClauseUtility.AddStringEqual("ttpl.人员编码", nurse, sb);
WhereClauseUtility.AddStringEqual("ttpl.人员编码", stretcher, sb);
WhereClauseUtility.AddStringEqual("ts.名称", station, sb);
sb.Append(@"
order by 首次受理时刻 desc
select * into #temp from #tempa where 1=1 ");
sb.Append(@"
select top " + pageSize + " A.* from #temp A where 行号>" + (pageIndex - 1) * pageSize + " order by 行号 ");
sb.Append(@"
SELECT count(*) FROM #temp t
drop table #tempa,#temp ");
DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
EventInfo info = new EventInfo();
info.callPhone = dr["首次呼救电话"].ToString();
info.acceptTimes = Convert.ToInt32(dr["受理次数"]);
info.eventName = dr["事件名称"].ToString();
info.firstAcceptTime = dr["首次受理时刻"].ToString();
info.firstDispatcher = dr["首次调度员编码"].ToString();
info.sendCarTimes = Convert.ToInt32(dr["派车次数"]);
info.finishedTimes = Convert.ToInt32(dr["正常完成"]);
list.Add(info);
}
}
int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
var result = new { total = total, rows = list };
return(result);
}
else if (em == E_StatisticsPermisson.CENTER)
{
WhereClauseUtility.AddStringEqual("ttpl.人员编码", driver, sb);
WhereClauseUtility.AddStringEqual("ttpl.人员编码", doctor, sb);
WhereClauseUtility.AddStringEqual("ttpl.人员编码", nurse, sb);
WhereClauseUtility.AddStringEqual("ttpl.人员编码", stretcher, sb);
WhereClauseUtility.AddStringEqual("tae.中心编码", selfCenterID, sb);
WhereClauseUtility.AddStringEqual("tt.分站编码", station, sb);
sb.Append(@"
order by 首次受理时刻 desc
select * into #temp from #tempa where 1=1 ");
sb.Append(@"
select top " + pageSize + " A.* from #temp A where 行号>" + (pageIndex - 1) * pageSize + " order by 行号 ");
sb.Append(@"
SELECT count(*) FROM #temp t
drop table #tempa,#temp ");
DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
EventInfo info = new EventInfo();
info.callPhone = dr["首次呼救电话"].ToString();
info.acceptTimes = Convert.ToInt32(dr["受理次数"]);
info.eventName = dr["事件名称"].ToString();
info.firstAcceptTime = dr["首次受理时刻"].ToString();
info.firstDispatcher = dr["首次调度员编码"].ToString();
info.sendCarTimes = Convert.ToInt32(dr["派车次数"]);
info.finishedTimes = Convert.ToInt32(dr["正常完成"]);
list.Add(info);
}
}
int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
var result = new { total = total, rows = list };
return(result);
}
else if (em == E_StatisticsPermisson.STATION)
{
WhereClauseUtility.AddStringEqual("ttpl.人员编码", driver, sb);
WhereClauseUtility.AddStringEqual("ttpl.人员编码", doctor, sb);
WhereClauseUtility.AddStringEqual("ttpl.人员编码", nurse, sb);
WhereClauseUtility.AddStringEqual("ttpl.人员编码", stretcher, sb);
WhereClauseUtility.AddStringEqual("tt.分站编码", selfStationID, sb);
sb.Append(@"
order by 首次受理时刻 desc
select * into #temp from #tempa where 1=1 ");
sb.Append(@"
select top " + pageSize + " A.* from #temp A where 行号>" + (pageIndex - 1) * pageSize + " order by 行号 ");
sb.Append(@"
SELECT count(*) FROM #temp t
drop table #tempa,#temp ");
DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
EventInfo info = new EventInfo();
info.callPhone = dr["首次呼救电话"].ToString();
info.acceptTimes = Convert.ToInt32(dr["受理次数"]);
info.eventName = dr["事件名称"].ToString();
info.firstAcceptTime = dr["首次受理时刻"].ToString();
info.firstDispatcher = dr["首次调度员编码"].ToString();
info.sendCarTimes = Convert.ToInt32(dr["派车次数"]);
info.finishedTimes = Convert.ToInt32(dr["正常完成"]);
list.Add(info);
}
}
int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
var result = new { total = total, rows = list };
return(result);
}
else if (em == E_StatisticsPermisson.SELF)
{
sb.Append("and ttpl.人员编码 = '" + selfWorkCode + @"' ");
sb.Append(@"
order by 首次受理时刻 desc
select * into #temp from #tempa where 1=1 ");
sb.Append(@"
select top " + pageSize + " A.* from #temp A where 行号>" + (pageIndex - 1) * pageSize + " order by 行号 ");
sb.Append(@"
SELECT count(*) FROM #temp t
drop table #tempa,#temp ");
DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.AttemperConnectionString, CommandType.Text, sb.ToString(), null);
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
EventInfo info = new EventInfo();
info.callPhone = dr["首次呼救电话"].ToString();
info.acceptTimes = Convert.ToInt32(dr["受理次数"]);
info.eventName = dr["事件名称"].ToString();
info.firstAcceptTime = dr["首次受理时刻"].ToString();
info.firstDispatcher = dr["首次调度员编码"].ToString();
info.sendCarTimes = Convert.ToInt32(dr["派车次数"]);
info.finishedTimes = Convert.ToInt32(dr["正常完成"]);
list.Add(info);
}
}
int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
var result = new { total = total, rows = list };
return(result);
}
else
{
return(null);
}
}
public object GetMaterialList(int page, int rows, DateTime startTime, DateTime endTime,
string vender, string isActive, string mTypeId, string mName, string mCode)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.MainConnectionString);//取管理库的链接字符串
StringBuilder sbSQL = new StringBuilder();
//bool isActive1 = Convert.ToBoolean("isActive");
sbSQL.Append(@"select identity(int,1,1) as 行号,
ID= u.ID,
CreatorDate = u.CreatorDate,
Name = u.Name,
MCode = u.MCode,
Unit = isnull(td1.Name,u.Unit),
Specification = u.Specification,
Manufacturer = u.Manufacturer,
Vendor = u.Vendor,
AlarmCounts = u.AlarmCounts,
RealPrice = u.RealPrice,
TransferPrice = u.TransferPrice,
Remark = u.Remark,
MTypeID = td.name,
isActive = u.IsActive
into #temp
from I_Material u
left join TDictionary td on td.ID=u.MTypeID
left join TDictionary td1 on td1.ID =u.Unit
where 1 = 1 ");
WhereClauseUtility.AddDateTimeGreaterThan("u.CreatorDate", startTime, sbSQL);
WhereClauseUtility.AddDateTimeLessThan("u.CreatorDate", endTime, sbSQL);
WhereClauseUtility.AddStringEqual("u.isActive", isActive, sbSQL);
WhereClauseUtility.AddStringLike("u.Vendor", vender, sbSQL);
//WhereClauseUtility.AddStringLikeOr("u.MTypeID", mTypeId,"", sbSQL);
WhereClauseUtility.AddStringLike("u.Name", mName, sbSQL);
WhereClauseUtility.AddStringLike("u.MCode", mCode, sbSQL);
if (!string.IsNullOrEmpty(mTypeId))
{
sbSQL.Append(" AND (u.MTypeID = '" + mTypeId + "' or td.ParentID ='" + mTypeId + "') ");
}
sbSQL.Append("order by u.CreatorDate desc ");
sbSQL.Append(" select top " + rows + " A.* from #temp A where 行号>" + (page - 1) * rows + " order by 行号 ");
sbSQL.Append(" SELECT COUNT(*) FROM #temp ");
sbSQL.Append(" drop table #temp");
DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sbSQL.ToString(), null);
List <MaterialInfo> list = new List <MaterialInfo>();
if (ds.Tables[0].Rows.Count > 0)
{
MaterialInfo info = new MaterialInfo();
foreach (DataRow dr in ds.Tables[0].Rows)
{
info = new MaterialInfo();
info.ID = Convert.ToInt32(dr["ID"]);
info.CreatorDate = Convert.ToDateTime(dr["CreatorDate"]);
info.Name = dr["Name"].ToString();
info.MCode = dr["MCode"].ToString();
info.Unit = dr["Unit"].ToString();
info.Specification = dr["Specification"].ToString();
info.Manufacturer = dr["Manufacturer"].ToString();
info.Vendor = dr["Vendor"].ToString();
info.AlarmCounts = Convert.ToInt32(dr["AlarmCounts"]);
info.RealPrice = Convert.ToDecimal(dr["RealPrice"]);
info.TransferPrice = Convert.ToDecimal(dr["TransferPrice"]);
info.MTypeID = dr["MTypeID"].ToString();
info.IsActive = Convert.ToBoolean(dr["isActive"]);
info.Remark = dr["Remark"].ToString();
list.Add(info);
}
int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
var result = new { total = total, rows = list };
return(result);
}
else
{
list = null;
var resultK = new { total = 0, rows = 0 };//当查询没有数据返回;
return(resultK);
}
}
/// <summary>
/// 出库单查询 朱传海
/// </summary>
/// <returns></returns>
public object GetDeliveryOrder(int page, int rows, DateTime startTime, DateTime endTime, string deliveryType,
string deliveryCode, string entryStorageCode, string operatorName, string mName, string receivingStoreID, string consigneeName, string mCode)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.MainConnectionString);//取管理库的链接字符串
StringBuilder sbSQL = new StringBuilder();
sbSQL.Append(@"select identity(int,1,1) as 行号,
mName=tm.Name,
DeliveryCode =d.DeliveryCode,
DeliveryTime = d.DeliveryTime,
operatorName=tp1.Name,
consigneeName=tp2.Name,
BatchNo =dd.BatchNo,
EntryStorageName =s1.Name,
ReceivingStoreName=s.Name,
DeliveryCounts = dd.DeliveryCounts,
DeliveryType=td.Name,
Remark =d.Remark
into #temp
from I_Delivery d
left join I_DeliveryDetail dd on dd.DeliveryCode=d.DeliveryCode
left join I_Material tm on tm.ID=dd.MaterialID
left join P_User tp1 on tp1.ID=d.OperatorCode
left join P_User tp2 on tp2.ID=d.ConsigneeID
left join I_Storage s on s.StorageID=d.ReceivingStoreID
left join I_Storage s1 on s1.StorageID=dd.EntryStorageCode
left join TDictionary td on td.ID=d.DeliveryType
where 1 = 1
");
WhereClauseUtility.AddDateTimeGreaterThan("d.DeliveryTime", startTime, sbSQL);
WhereClauseUtility.AddDateTimeLessThan("d.DeliveryTime", endTime, sbSQL);
WhereClauseUtility.AddStringEqual("d.DeliveryCode", deliveryCode, sbSQL);
WhereClauseUtility.AddStringLike("tp1.Name", operatorName, sbSQL);
WhereClauseUtility.AddStringLike("tp2.Name", consigneeName, sbSQL);
WhereClauseUtility.AddStringEqual("dd.ReceivingStoreID", receivingStoreID, sbSQL);
WhereClauseUtility.AddStringEqual("dd.EntryStorageCode", entryStorageCode, sbSQL);
WhereClauseUtility.AddStringLike("tm.Name", mName, sbSQL);
WhereClauseUtility.AddStringEqual("tm.MCode", mCode, sbSQL);
WhereClauseUtility.AddStringEqual("d.DeliveryType", deliveryType, sbSQL);
sbSQL.Append(" group by d.DeliveryCode,tm.Name,d.DeliveryTime,tp1.Name,tp2.Name,dd.BatchNo,s1.Name,s.Name,dd.DeliveryCounts,td.Name,d.Remark ");
sbSQL.Append("order by d.DeliveryCode desc ");
sbSQL.Append(" select top " + rows + " A.* from #temp A where 行号>" + (page - 1) * rows + " order by 行号 ");
sbSQL.Append(" SELECT COUNT(*) FROM #temp ");
sbSQL.Append(" drop table #temp");
DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sbSQL.ToString(), null);
List <I_DeliveryOrder> list = new List <I_DeliveryOrder>();
if (ds.Tables[0].Rows.Count > 0)
{
I_DeliveryOrder info = new I_DeliveryOrder();
foreach (DataRow dr in ds.Tables[0].Rows)
{
info = new I_DeliveryOrder();
info.mName = dr["mName"].ToString();
info.DeliveryCode = dr["DeliveryCode"].ToString();
info.DeliveryTime = Convert.ToDateTime(dr["DeliveryTime"]);
info.OperatorName = dr["operatorName"].ToString();
info.ConsigneeName = dr["consigneeName"].ToString();
info.Remark = dr["Remark"].ToString();
info.BatchNo = dr["BatchNo"].ToString();
info.TotalDelivery = dr["DeliveryCounts"].ToString();
info.ReceivingStoreName = dr["ReceivingStoreName"].ToString();
info.EntryStorageName = dr["EntryStorageName"].ToString();
info.DeliveryType = dr["DeliveryType"].ToString();
////info. = Convert.ToInt32(dr["Code"]);
//info.DeliveryType = Convert.ToInt32(dr["DeliveryType"]);
//info.ItemCode = dr["ItemCode"].ToString() == "" ? -1 : Convert.ToInt32(dr["ItemCode"]);
list.Add(info);
}
}
else
{
list = null;
var resultK = new { total = 0, rows = 0 };//当查询没有数据返回;
return(resultK);
}
int total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
var result = new { total = total, rows = list };
return(result);
}
