public DataTable GetAllData(Entities.QueryWOrderTag query) { string sql = "SELECT RecID ,BusiTypeID,TagName ,PID ,SortNum,Status FROM WOrderTag where status<>'-1'"; List <SqlParameter> listParam = new List <SqlParameter>(); if (!string.IsNullOrEmpty(query.PID)) { sql += " and PID=@PID"; listParam.Add(new SqlParameter("@PID", SqlDbType.Int, 4) { SqlValue = query.PID }); } if (!string.IsNullOrEmpty(query.RecID)) { sql += " and RecID=@RecID"; listParam.Add(new SqlParameter("@RecID", SqlDbType.Int, 4) { SqlValue = query.RecID }); } if (!string.IsNullOrEmpty(query.NoRecID)) { sql += " and RecID<>@NoRecID"; listParam.Add(new SqlParameter("@NoRecID", SqlDbType.Int, 4) { SqlValue = query.NoRecID }); } if (!string.IsNullOrEmpty(query.TagName)) { sql += " and TagName=@TagName"; listParam.Add(new SqlParameter("@TagName", SqlDbType.NVarChar, 50) { SqlValue = query.TagName }); } if (!string.IsNullOrEmpty(query.BusiTypeID)) { sql += " and BusiTypeID=@BusiTypeID"; listParam.Add(new SqlParameter("@BusiTypeID", SqlDbType.Int, 4) { SqlValue = query.BusiTypeID }); } if (!string.IsNullOrEmpty(query.Status)) { //sql += " and Status in (select * from dbo.f_split(@status,','))"; //listParam.Add(new SqlParameter("@status", SqlDbType.NVarChar, 50) { SqlValue = query.Status }); sql += " and Status in (" + SqlFilter(query.Status) + ")"; } sql += " ORDER BY SortNum"; SqlParameter[] parameters = listParam.ToArray(); DataSet ds = SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.Text, sql, parameters); return(ds.Tables[0]); }
private void BindData() { Entities.QueryWOrderTag query = new Entities.QueryWOrderTag(); query.BusiTypeID = BusiTypeId; query.Status = "1"; DataTable dt = BLL.WOrderTag.Instance.GetAllData(query); if (dt != null && dt.Rows.Count > 0) { //一级标签 DataRow[] drs = dt.Select(" PID IS NULL or PID='0' ", " BusiTypeID asc, SortNum asc"); //选中标签 DataRow drselect = null; if (!string.IsNullOrEmpty(TagId)) { //一级、二级都存在 try { drselect = dt.Select("RecID=" + TagId)[0]; var drp = dt.Select("RecID=" + drselect["PID"].ToString())[0]; } catch { drselect = null; } } Lit_left.Text = GetLeveOne(drs, drselect); Lit_right.Text = GetLeveTwo(dt, drs, drselect); } }
public DataTable GetWOrderLabelData(string Verifycode, string businessTypeId, string pId) { string msg = ""; if (BLL.CallRecord_ORIG_Authorizer.Instance.Verify(Verifycode, 1, ref msg, "授权失败。")) { try { Entities.QueryWOrderTag query = new Entities.QueryWOrderTag(); query.Status = "1"; query.BusiTypeID = businessTypeId.Trim(); query.PID = pId; return(BLL.WOrderTag.Instance.GetAllData(query)); } catch (Exception ex) { BLL.Loger.Log4Net.Error("WorkOrderService下的GetWorkOrderLabelData方法异常", ex); return(null); } } else { return(null); } }
/// <summary> /// 获取等级数据 /// </summary> /// <param name="query"></param> /// <returns></returns> public DataTable GetLevelData(Entities.QueryWOrderTag query, string order, int currentPage, int pageSize, out int totalCount) { //child = ISNULL(STUFF(( SELECT ','+ RTRIM(CONVERT(VARCHAR(MAX), TagName)) // FROM WOrderTag // WHERE status <> '-1' // AND PID = x.RecID // {0} // ORDER BY SortNum // FOR XML PATH('')), 1, 1, ''), '') //TODO:CodeReview 2016-08-24 sql写法问题 string sql = @"SELECT RecID ,BusiTypeID,TagName ,PID ,SortNum,Status, ISNULL(STUFF((SELECT ','+cast(TagName as varchar(max)) FROM WOrderTag WHERE status<>'-1' and PID=x.RecID {0} ORDER BY SortNum FOR XML PATH('')),1,1,''),'') as child YanFaFROM WOrderTag x WHERE status<>'-1' AND ISNULL(PID,0)=0"; string sqlstatus = ""; if (!string.IsNullOrEmpty(query.BusiTypeID)) { sql += " and BusiTypeID=" + SqlFilter(query.BusiTypeID); } if (!string.IsNullOrEmpty(query.Status)) { sqlstatus = " and Status in (" + SqlFilter(query.Status) + ")"; sql += sqlstatus; } sql = string.Format(sql, sqlstatus); if (string.IsNullOrEmpty(order)) { order = " SortNum asc "; } SqlParameter[] parameters = { new SqlParameter("@SQL", SqlDbType.NVarChar, 4000), new SqlParameter("@Order", SqlDbType.NVarChar, 200), new SqlParameter("@CurPage", SqlDbType.Int, 4), new SqlParameter("@PageRows", SqlDbType.Int, 4), new SqlParameter("@TotalRecorder", SqlDbType.Int, 4) }; parameters[0].Value = sql; parameters[1].Value = order; parameters[2].Value = currentPage; parameters[3].Value = pageSize; parameters[4].Direction = ParameterDirection.Output; DataSet ds = SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.StoredProcedure, "P_Page", parameters); totalCount = (int)(parameters[4].Value); return(ds.Tables[0]); }
private ReturnMessage UpdateStatus(string nStatus = "") { ReturnMessage message = new ReturnMessage(); message.Success = true; int lCurrId = 0; if (!Int32.TryParse(CurrId, out lCurrId)) { message.Success = false; message.Message = "参数错误"; } int lstatus = 0; if (!int.TryParse(nStatus, out lstatus)) { message.Success = false; message.Message = "参数错误"; } if (lstatus == -1)//删除 { //判断是否使用 Entities.QueryWOrderTag querytag = new Entities.QueryWOrderTag(); querytag.BusiTypeID = CurrId; DataTable dt = BLL.WOrderTag.Instance.GetAllData(querytag); if (dt != null && dt.Rows.Count > 0) { message.Success = false; message.Message = "此业务线已使用,不可以删除"; } } if (message.Success) { Entities.WOrderBusiTypeInfo entity = new Entities.WOrderBusiTypeInfo(); entity.RecID = lCurrId; entity.Status = lstatus; entity.LastUpdateTime = DateTime.Now; entity.LastUpdateUserID = UserID; bool flag = BLL.CommonBll.Instance.UpdateComAdoInfo <Entities.WOrderBusiTypeInfo>(entity); message.Success = flag; } return(message); }
/// <summary> /// 获取在用数据 /// </summary> /// <returns></returns> public ReturnMessage GetData(string status = "1") { ReturnMessage message = new ReturnMessage(); try { //获取标签最多的业务类型------------------- Entities.QueryWOrderTag query = new Entities.QueryWOrderTag() { PID = "0", Status = "1" }; int selbusid = BLL.WOrderTag.Instance.GetMaxBusiTagCount(query); DataTable dt = BLL.WOrderBusiType.Instance.GetAllData(new Entities.QueryWOrderBusiTypeInfo() { Status = status }); List <object> list = new List <object>(); object obj = new object(); bool isSelected = false; for (int i = 0; i < dt.Rows.Count; i++) { isSelected = false; if (dt.Rows[i]["RecID"].ToString() == selbusid.ToString()) { isSelected = true; } obj = new { Selected = isSelected, Id = dt.Rows[i]["RecID"].ToString(), Name = dt.Rows[i]["BusiTypeName"].ToString() }; list.Add(obj); } message.Success = true; message.Data = list; } catch (Exception ex) { message.Success = false; message.Message = ex.Message; } return(message); }
/// <summary> /// 获取标签做多的业务类型 /// </summary> /// <param name="query"></param> /// <returns></returns> public int GetMaxBusiTagCount(Entities.QueryWOrderTag query) { DataTable dt = GetBusiTagCount(query); int busid = 0; try { if (dt.Rows.Count > 0) { busid = int.Parse(dt.Rows[0]["BusiTypeID"].ToString()); } } catch (Exception ex) { throw ex; } return(busid); }
/// <summary> /// 检查是否有重名 /// </summary> /// <param name="name"></param> /// <param name="pid"></param> /// <returns></returns> private bool CheckName(string name, string pid, string id, string busitypeid = "") { Entities.QueryWOrderTag query = new Entities.QueryWOrderTag(); query.TagName = name; query.PID = pid; query.Status = "1,0"; query.NoRecID = id; query.BusiTypeID = busitypeid; DataTable dt = BLL.WOrderTag.Instance.GetAllData(query); // if (dt != null && dt.Rows.Count > 0) { return(true); } return(false); }
/// <summary> /// 获取每个业务类型的标签数量 /// </summary> /// <param name="query"></param> /// <returns></returns> public DataTable GetBusiTagCount(Entities.QueryWOrderTag query) { string strSql = @"SELECT BusiTypeID, COUNT(1)as num FROM WOrderTag WHERE status<>'-1' {0} GROUP BY BusiTypeID ORDER BY num desc "; string where = ""; if (!string.IsNullOrEmpty(query.PID)) { where = " and PID=" + SqlFilter(query.PID) + ""; } strSql = string.Format(strSql, where); DataSet ds = SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.Text, strSql); return(ds.Tables[0]); }
private void BindData() { string strBusiTypeId = ""; try { strBusiTypeId = Int32.Parse(BusiTypeID).ToString(); } catch { } Entities.QueryWOrderTag query = new Entities.QueryWOrderTag(); query.BusiTypeID = strBusiTypeId; query.Status = StrStatus; DataTable dt = BLL.WOrderTag.Instance.GetLevelData(query, "", PageIndex, PageSize, out totalCount); rpt.DataSource = dt; rpt.DataBind(); litPagerDown.Text = BLL.PageCommon.Instance.LinkStringByPost(BLL.Util.GetUrl(), GroupLength, totalCount, PageSize, BLL.PageCommon.Instance.PageIndex, 1); }
private void BindData() { try { string sta = StrStatus.Trim(','); // Entities.QueryWOrderTag query = new Entities.QueryWOrderTag(); query.Status = sta; query.BusiTypeID = BusiTypeID; query.PID = PID; query.IsPIDSearch = true; // DataTable dt = BLL.WOrderTag.Instance.GetAllData(query); rpt.DataSource = dt; rpt.DataBind(); } catch (Exception ex) { BLL.Loger.Log4Net.Error("读取标签层出错," + ex.Message.ToString()); } }
/// <summary> /// 获取等级数据 /// </summary> /// <param name="query"></param> /// <returns></returns> public DataTable GetLevelData(Entities.QueryWOrderTag query, string order, int currentPage, int pageSize, out int totalCount) { return(Dal.WOrderTag.Instance.GetLevelData(query, order, currentPage, pageSize, out totalCount)); }
/// <summary> /// 获取所有数据 /// </summary> /// <param name="status">是否可用,可以用“,”分割</param> /// <returns></returns> public DataTable GetAllData(Entities.QueryWOrderTag query) { return(Dal.WOrderTag.Instance.GetAllData(query)); }
/// <summary> /// 获取每个业务类型的标签数量 /// </summary> /// <param name="query"></param> /// <returns></returns> public DataTable GetBusiTagCount(Entities.QueryWOrderTag query) { return(Dal.WOrderTag.Instance.GetBusiTagCount(query)); }
/// <summary> /// 获取在用数据 /// </summary> /// <returns></returns> public ReturnMessage GetData() { ReturnMessage message = new ReturnMessage(); try { #region 查询条件 Entities.QueryWOrderTag query = new Entities.QueryWOrderTag(); if (!string.IsNullOrEmpty(CurrId)) { try { //必须为数字 query.RecID = Int32.Parse(CurrId).ToString(); } catch { } } if (!string.IsNullOrEmpty(BusiTypeId)) { try { //必须为数字 query.BusiTypeID = Int32.Parse(BusiTypeId).ToString(); } catch { } } if (!string.IsNullOrEmpty(Status)) { query.Status = Status.Trim(','); } if (!string.IsNullOrEmpty(PId)) { try { //必须为数字 query.PID = Int32.Parse(PId).ToString(); } catch { } } #endregion DataTable dt = BLL.WOrderTag.Instance.GetAllData(query); List <object> list = new List <object>(); object obj = new object(); for (int i = 0; i < dt.Rows.Count; i++) { obj = new { RecID = dt.Rows[i]["RecID"].ToString(), TagName = dt.Rows[i]["TagName"].ToString() }; list.Add(obj); } message.Success = true; message.Data = list; } catch (Exception ex) { message.Success = false; message.Message = ex.Message; } return(message); }