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);
}
