public IEnumerable <dynamic> GetList() { var queryDevice = DevicesRepository.GetQuery(o => o.Status == 1); var queryDict = SysDataDictionaryRepository.GetQuery(); var query = from x in queryDevice join y in queryDict on x.CategoryId equals y.DicSN select new { x.Id, x.Brand, x.CategoryId, x.DeviceId, x.Spec, x.Status, x.Title, CategoryTitle = y.Title }; var list = query.ToList(); return(list); }
public List <Devices> GetDeviceInput(string searchName) { if (searchName.IsNullOrEmpty()) { return(null); } var queryDev = DevicesRepository.GetQuery(); var queryDict = SysDataDictionaryRepository.GetQuery(); var query = from x in queryDev join y in queryDict on x.CategoryId equals y.DicSN select new { x.DeviceId, x.Title, x.CategoryId, CategoryTitle = y.Title }; query = query.Where(o => o.Title.Contains(searchName) || o.CategoryTitle.Contains(searchName)); return(query.Take(20).AsEnumerable().Select(o => new Devices() { DeviceId = o.DeviceId, Title = o.Title, CategoryId = o.CategoryId }).ToList()); }
public IEnumerable <dynamic> GetPageList(System.Collections.Specialized.NameValueCollection nvl, out int recordCount) { //状态 var State = (nvl["State"] ?? "").Trim(); //类别 var Category = (nvl["Category"] ?? "").Trim(); //关键字类型 var keywordType = (nvl["keywordType"] ?? "").Trim(); //关键字 var keyword = (nvl["keyword"] ?? "").Trim(); //商户域名信息 var QRealm = RealmRepository.GetQuery(o => o.CID != -1); //商户信息 var QTraders = TradersRepository.GetQuery(); //一级域名 var QSysData = SysDataDictionaryRepository.GetQuery(o => o.DicPSN == 600 && o.Status); if (State != "-2") { short Sta = Convert.ToInt16(State); QRealm = QRealm.Where(o => o.State == Sta); } if (Category != "-1") { short Catego = Convert.ToInt16(Category); QRealm = QRealm.Where(o => o.Category == Catego); } if (!keywordType.IsNullOrEmpty() && !keyword.IsNullOrEmpty()) { if (keywordType == "1") { if (!Tool.IsNumber(keyword) || keyword.Length > 7) { keyword = "0"; } int key = Convert.ToInt32(keyword); QRealm = QRealm.Where(o => o.CID == key); } } var q = from r in QRealm join t in QTraders on r.CID equals t.CID into tt from ttt in tt.DefaultIfEmpty() join s in QSysData on r.Domain1 equals s.DicSN into ss from sss in ss.DefaultIfEmpty() select new { r.Id, r.Category, r.CID, TradersFullTitle = ttt == null ? "" : ttt.FullTitle, Domain = r.Name + "." + sss.Title, r.Remark, r.CreateDT, r.State }; if (keywordType == "3" && !keywordType.IsNullOrEmpty() && !keyword.IsNullOrEmpty()) { q = q.Where(o => o.Domain.Contains(keyword)); } if (keywordType == "2" && !keywordType.IsNullOrEmpty() && !keyword.IsNullOrEmpty()) { q = q.Where(o => o.TradersFullTitle.Contains(keyword)); } recordCount = q.Count(); return(q.ToPageList()); }
public IEnumerable <dynamic> GetPageList(System.Collections.Specialized.NameValueCollection nvl, out int recordCount) { //设备 var query = DevicesRepository.GetQuery(); //账户 var userInfo = SysUserInfoRepository.GetQuery(); //数据字典 var dataDictionary = SysDataDictionaryRepository.GetQuery(o => o.Status); //设备授权信息 var dAuthorize = DeviceAuthorizeRepository.GetQuery(); //分类 var CategoryId = (nvl["CategoryId"] ?? "").Trim(); //品牌 var Brand = (nvl["Brand"] ?? "").Trim(); //维护人 var FullName = (nvl["FullName"] ?? "").Trim(); //设备名称 var Title = (nvl["Title"] ?? "").Trim(); if (!CategoryId.IsNullOrEmpty()) { var c = short.Parse(CategoryId); query = query.Where(w => w.CategoryId == c); } if (!Brand.IsNullOrEmpty()) { query = query.Where(w => w.Brand.Contains(Brand)); } if (!Title.IsNullOrEmpty()) { query = query.Where(w => w.Title.Contains(Title) || w.Spec.Contains(Title)); } var q = from quer in query let da = from dAu in dAuthorize where dAu.DeviceId == quer.DeviceId select dAu join u in userInfo on quer.CreateUID equals u.UserId into v from w in v.DefaultIfEmpty() join d in dataDictionary on quer.CategoryId equals d.DicSN into x from y in x.DefaultIfEmpty() select new { quer.Id, quer.DeviceId, quer.CategoryId, quer.Title, quer.Brand, quer.Spec, number = da.Count(), quer.Status, quer.CreateDT, quer.CreateUID, dTitle = y == null ? "" : y.Title, FullName = w == null ? "" : w.FullName }; if (!FullName.IsNullOrEmpty() && FullName.Trim() != "全部") { q = q.Where(w => w.FullName.Contains(FullName)); } recordCount = q.Count(); return(q.ToPageList()); }
/// <summary> /// 导入 /// </summary> /// <param name="obj"></param> /// <param name="httpFiles"></param> /// <param name="fieldName"></param> /// <param name="columnName"></param> /// <returns></returns> public OpResult Import(ImportSet obj, System.Web.HttpFileCollectionBase httpFiles, string fieldName, string columnName) { var op = new OpResult(); var errLs = new List <string>(); int count = 0; try { Dictionary <string, char> fieldCols = null; DataTable dt = null; op = ImportSetService.ImportSet(obj, httpFiles, fieldName, columnName, ref fieldCols, ref dt); if (!op.Successed) { return(op); } //数据字典 var dataDictionary = SysDataDictionaryRepository.GetQuery(); //设备分类名称 var CIdx = Convert.ToInt32(fieldCols["CategoryId"]) - 65; //设备名称 var TitleIdx = Convert.ToInt32(fieldCols["Title"]) - 65; //品牌 var BrandIdx = Convert.ToInt32(fieldCols["Brand"]) - 65; //型号 var SpecIdx = Convert.ToInt32(fieldCols["Spec"]) - 65; count = dt.Rows.Count; for (int i = dt.Rows.Count - 1; i >= 0; i--) { try { var dr = dt.Rows[i]; #region 验证 //设备分类名称 var text = dr[CIdx].ToString().Trim(); if (text.IsNullOrEmpty()) { errLs.Add("行号[" + (i + 1) + "]设备分类为空!"); dt.Rows.RemoveAt(i); continue; } var dd = dataDictionary.Where(o => o.DicPSN == 197 && o.Title == text); if (!dd.Any()) { errLs.Add("行号[" + (i + 1) + "]设备分类在字典中不存在,请先添加!"); dt.Rows.RemoveAt(i); continue; } else { dr[CIdx] = dd.Select(o => o.DicSN).FirstOrDefault(); } //设备名称 var text2 = dr[TitleIdx].ToString().Trim(); if (text2.IsNullOrEmpty()) { errLs.Add("行号[" + (i + 1) + "]设备名称为空!"); dt.Rows.RemoveAt(i); continue; } //品牌 var text3 = dr[BrandIdx].ToString().Trim(); if (text3.IsNullOrEmpty()) { errLs.Add("行号[" + (i + 1) + "]品牌为空!"); dt.Rows.RemoveAt(i); continue; } //型号 var text4 = dr[SpecIdx].ToString().Trim(); if (text4.IsNullOrEmpty()) { errLs.Add("行号[" + (i + 1) + "]型号为空!"); dt.Rows.RemoveAt(i); continue; } //设备分类ID var cid = dataDictionary.Where(o => o.DicPSN == 197 && o.Title == text).Select(o => o.DicSN).FirstOrDefault(); var isExist = DevicesRepository.GetQuery(o => o.CategoryId == cid && o.Title == text2 && o.Brand == text3 && o.Spec == text4); if (isExist.Any()) { errLs.Add("行号[" + (i + 1) + "]设备记录已经存在!"); dt.Rows.RemoveAt(i); continue; } #endregion } catch (Exception e) { throw new Exception("创建相关记录失败," + e.Message, e); } } StringBuilder sb = new StringBuilder(); sb.Append("begin tran "); foreach (DataRow dr in dt.Rows) { Devices devices = new Devices(); devices.DeviceId = CommonService.GUID.ToUpper(); devices.CreateUID = CurrentUser.UID; devices.CreateDT = DateTime.Now; sb.Append("insert into "); sb.Append(obj.TableName); sb.Append("(Status,DeviceId,CreateDT,CreateUID,"); sb.Append(string.Join(",", fieldCols.Keys)); sb.Append(") values(1,'" + devices.DeviceId + "','" + devices.CreateDT + "','" + devices.CreateUID + "',"); foreach (var de in fieldCols) { var index = Convert.ToInt32(de.Value) - 65; try { var text = dr[index].ToString().Trim(); sb.Append("'" + text + "',"); } catch (Exception e) { throw new Exception("列选择超过范围!", e); } } sb = sb.Remove(sb.Length - 1, 1); sb.Append(");"); } sb.Append(" commit tran"); new DBFramework.DBHelper().ExecuteNonQueryText(sb.ToString(), null); } catch (Exception ex) { op.Message = ex.Message; op.Successed = false; LogEngine.WriteError(ex); errLs.Add("导入出现异常!"); } return(CommonService.GenerateImportHtml(errLs, count)); }
/// <summary> /// 获取设备分类 /// </summary> /// <returns></returns> public List <SysDataDictionary> getDataList() { return(SysDataDictionaryRepository.GetQuery(w => w.DicPSN == 197 && w.Status).OrderBy(o => o.DicSN).ToList()); }