/// <summary>
/// 按照查询条件查询(供知识库管理列表页使用 刘学文)
/// </summary>
/// <param name="query">查询条件</param>
/// <param name="order">排序</param>
/// <param name="currentPage">页号,-1不分页</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="totalCount">总行数</param>
/// <returns>集合</returns>
public DataTable GetKLQuestion(QueryKnowledgeLib query, string order, int currentPage, int pageSize, out int totalCount)
{
string where = string.Empty;
where = Dal.KnowledgeLib.Instance.getCommonWhere(3, query);
DataSet ds;
SqlParameter[] parameters =
{
new SqlParameter("@where", SqlDbType.NVarChar, 40000),
new SqlParameter("@order", SqlDbType.NVarChar, 200),
new SqlParameter("@pagesize", SqlDbType.Int, 4),
new SqlParameter("@indexpage", SqlDbType.Int, 4),
new SqlParameter("@totalRecorder", SqlDbType.Int, 4)
};
parameters[0].Value = where;
parameters[1].Value = order;
parameters[2].Value = pageSize;
parameters[3].Value = currentPage;
parameters[4].Direction = ParameterDirection.Output;
ds = SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.StoredProcedure, P_KLQUESTION_SELECT, parameters);
totalCount = (int)(parameters[4].Value);
return(ds.Tables[0]);
}
/// <summary>
/// 统计（知识库管理列表页 刘学文）
/// </summary>
/// <param name="query">查询条件</param>
/// <param name="order">排序</param>
/// <param name="currentPage">页号,-1不分页</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="totalCount">总行数</param>
/// <returns>集合</returns>
public DataTable GetKnowledgeLibCount(QueryKnowledgeLib query, int currentPage, int pageSize, out int totalCount)
{
totalCount = 0;
string where = Dal.UserGroupDataRigth.Instance.GetSqlRightstr("a", "BGID", "CreateUserID", query.UserID);
if (query.MBeginTime != Constant.STRING_INVALID_VALUE)
{
where += " AND a.CreateTime>='" + StringHelper.SqlFilter(query.MBeginTime) + " 0:00:00'";
}
if (query.MEndTime != Constant.STRING_INVALID_VALUE)
{
where += " AND a.CreateTime<='" + StringHelper.SqlFilter(query.MEndTime) + " 23:59:59'";
}
DataSet ds;
SqlParameter[] parameters =
{
new SqlParameter("@where", SqlDbType.NVarChar, 4000),
new SqlParameter("@kcid", SqlDbType.Int, 4),
new SqlParameter("@pageSize", SqlDbType.Int, 4),
new SqlParameter("@pageIndex", SqlDbType.Int, 4),
new SqlParameter("@TotalCount", SqlDbType.Int, 4)
};
parameters[0].Value = where;
parameters[1].Value = query.KCID;
parameters[2].Value = pageSize;
parameters[3].Value = currentPage;
parameters[4].Direction = ParameterDirection.Output;
ds = SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.StoredProcedure, "p_KnowledgeLib_Count_new", parameters);
totalCount = (int)(parameters[4].Value);
return(ds.Tables[0]);
}
//获取分类下拉列表
private void bindKnowledgeCategory()
{
try
{
int qCount;
int parentParentId = -1;
DataTable dtkc = new DataTable();
if (KLType == "0")
{
Entities.QueryKnowledgeLib queryM = new QueryKnowledgeLib();
queryM.KLID = int.Parse(KLID);
dtkc = BLL.KnowledgeLib.Instance.GetKnowledgeLib(queryM, "", 1, 1, out qCount);
}
else if (KLType == "1")
{
Entities.QueryKLFAQ queryF = new QueryKLFAQ();
queryF.KLID = int.Parse(KLID);
dtkc = BLL.KLFAQ.Instance.GetKLFAQ(queryF, "", 1, 1, out qCount);
}
if (dtkc != null && dtkc.Rows.Count > 0)
{
KnowledgeCategory categoryEntity = BLL.KnowledgeCategory.Instance.GetKnowledgeCategory(int.Parse(dtkc.Rows[0]["KCID"].ToString()));
KCid = categoryEntity.KCID;
KCPid = categoryEntity.Pid.Value;
KLPLevel = categoryEntity.Level.Value;
if (KCid != 0)
{
KnowledgeCategory categoryEntityPP = BLL.KnowledgeCategory.Instance.GetKnowledgeCategory(KCPid);
parentParentId = categoryEntityPP.Pid.Value;
}
}
Entities.QueryKnowledgeCategory query = new Entities.QueryKnowledgeCategory();
//query.Level = 1;
if (parentParentId != -1)
{
query.Pid = parentParentId;
}
else
{
query.Pid = 0;
}
query.Regionid = RegionID;
int count;
DataTable dt = BLL.KnowledgeCategory.Instance.GetKnowledgeCategory(query, "", 1, 10000, out count);
selKCID1.DataSource = dt;
selKCID1.DataTextField = "Name";
selKCID1.DataValueField = "KCID";
selKCID1.DataBind();
selKCID1.Items.Insert(0, new ListItem()
{
Text = "请选择", Value = "-1"
});
}
catch (Exception ex)
{
BLL.Loger.Log4Net.Error("异常：" + ex.Message);
}
}
//获取所有的知识点总数（状态为审核通过，内容不为空）
public int GetKLIDAllCount(QueryKnowledgeLib query, out int totalCount)
{
string where = string.Empty;
where += getCommonWhere(1, query);
SqlParameter[] parameters =
{
new SqlParameter("@where", SqlDbType.NVarChar, 40000)
};
parameters[0].Value = where;
totalCount = int.Parse(SqlHelper.ExecuteScalar(CONNECTIONSTRINGS, CommandType.StoredProcedure, "p_KnowledgeLib_KLIDAllCount", parameters).ToString());
return(totalCount);
}
//全部标记为 已读
public void allMarkRead(out string msg)
{
msg = string.Empty;
string connectionstrings = ConfigurationUtil.GetAppSettingValue("ConnectionStrings_CC");
SqlConnection connection = new SqlConnection(connectionstrings);
connection.Open();
SqlTransaction tran = connection.BeginTransaction("SampleTransaction");
try
{
QueryKnowledgeLib query = new QueryKnowledgeLib();
query.Status = 2;
query.Content = "";
int count;
DataTable dt = BLL.KnowledgeLib.Instance.GetKnowledgeLib(query, "", 1, 100000, out count);
for (int i = 0; i < dt.Rows.Count; i++)
{
long klid = long.Parse(dt.Rows[i]["KLID"].ToString());
int userID = BLL.Util.GetLoginUserID();
//删除 该登陆者在所有知识点的标记记录
BLL.KLReadTag.Instance.DeleteByUserID(tran, userID, klid);
//新增 该登陆者在所有知识点的标记记录
Entities.KLReadTag model = new KLReadTag();
model.KLID = klid;
model.UserID = userID;
model.ReadTag = 1;
model.CreateUserID = userID;
model.CreateTime = DateTime.Now;
BLL.KLReadTag.Instance.Insert(tran, model);
BLL.Util.InsertUserLog(tran, "【全部标记为已读】知识库阅读标记表【插入】知识点ID：【" + model.KLID + "】阅读标记：【已读】的记录");
}
//事务提交
tran.Commit();
msg = "{msg:'操作成功，已全部标记为已读'}";
}
catch (Exception ex)
{
tran.Rollback();
msg = "{msg:'" + ex.Message.ToString() + "'}";
}
finally
{
connection.Close();
}
}
/// <summary>
/// 是否存在该记录
/// </summary>
public bool IsExistsByKLID(long KLID)
{
QueryKnowledgeLib query = new QueryKnowledgeLib();
query.KLID = KLID;
DataTable dt = new DataTable();
int count = 0;
dt = GetKnowledgeLib(query, string.Empty, 1, 1, out count);
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
public DataTable GetKLFAQ(QueryKnowledgeLib query, string order, int currentPage, int pageSize, string wherePlug, out int totalCount)
{
return(Dal.KLFAQ.Instance.GetKLFAQForManage(query, order, currentPage, pageSize, wherePlug, out totalCount));
}
/*
* //在知识点管理里支持区域，暂时未实现。
* public DataTable GetKnowledgeLibManage(QueryKnowledgeLib query, string order, int currentPage, int pageSize, out int totalCount, string wherePlus = "")
* {
* string where = wherePlus;
*
*
* if (query.Title != Constant.STRING_INVALID_VALUE)
* {
* where += " AND KnowledgeLib.Title like '%" + StringHelper.SqlFilter(query.Title) + "%'";
* }
* if (query.UnRead != Constant.STRING_INVALID_VALUE) // 未读
* {
* if (query.UnRead == string.Empty && query.UserID != Constant.INT_INVALID_VALUE)
* {
* where += " AND KLID NOT IN ( SELECT KLReadTag.KLID FROM KLReadTag WHERE KLReadTag.KLID=KnowledgeLib.KLID AND ReadTag=1 AND UserID=" + query.UserID + ")";
* }
* }
*
*
* if (query.BeginTime != Constant.STRING_INVALID_VALUE)
* {
* where += " AND KnowledgeLib.CreateTime>='" + StringHelper.SqlFilter(query.BeginTime) + " 0:00:00'";
* }
* if (query.EndTime != Constant.STRING_INVALID_VALUE)
* {
* where += " AND KnowledgeLib.CreateTime<='" + StringHelper.SqlFilter(query.EndTime) + " 23:59:59'";
* }
* if (query.MBeginTime != Constant.STRING_INVALID_VALUE)
* {
* where += " AND KnowledgeLib.LastModifyTime>='" + StringHelper.SqlFilter(query.MBeginTime) + " 0:00:00'";
* }
* if (query.MEndTime != Constant.STRING_INVALID_VALUE)
* {
* where += " AND KnowledgeLib.LastModifyTime<='" + StringHelper.SqlFilter(query.MEndTime) + " 23:59:59'";
* }
* if (query.CreateUserID != Constant.INT_INVALID_VALUE)
* {
* where += " AND KnowledgeLib.CreateUserID=" + query.CreateUserID;
* }
* if (query.LastModifyUserID != Constant.INT_INVALID_VALUE)
* {
* where += " AND KnowledgeLib.LastModifyUserID=" + query.LastModifyUserID;
* }
* if (query.StatusS != Constant.STRING_INVALID_VALUE)
* {
* where += " AND KnowledgeLib.Status IN (" + query.StatusS + ")";
* }
*
* if (query.Property != Constant.STRING_INVALID_VALUE) // 属性 1：有附件 2：有FAQ 3：有试题
* {
* string[] property = query.Property.Split(',');
* for (int i = 0; i < property.Length; i++)
* {
* switch (property[i])
* {
* case "1": where += " AND KnowledgeLib.UploadFileCount != 0";
* break;
* case "2": where += " AND KnowledgeLib.FAQCount != 0";
* break;
* case "3": where += " AND KnowledgeLib.QuestionCount != 0";
* break;
* }
* }
* }
*
*
*
* where += " AND KnowledgeLib.Status!=4 ";
*
*
*
* DataSet ds;
*
* SqlParameter[] parameters = {
* new SqlParameter("@where", SqlDbType.NVarChar, 40000),
* new SqlParameter("@order", SqlDbType.NVarChar, 200),
* new SqlParameter("@pagesize", SqlDbType.Int, 4),
* new SqlParameter("@indexpage", SqlDbType.Int, 4),
* new SqlParameter("@kcid", SqlDbType.Int, 4),
* new SqlParameter("@userid", SqlDbType.Int, 4),
* new SqlParameter("@totalRecorder", SqlDbType.Int, 4)
* };
*
* parameters[0].Value = where;
* parameters[1].Value = order;
* parameters[2].Value = pageSize;
* parameters[3].Value = currentPage;
* parameters[4].Value = query.KCID;
* parameters[5].Value = query.UserID;
* parameters[6].Direction = ParameterDirection.Output;
*
* ds = SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.StoredProcedure, P_KNOWLEDGELIB_SELECT, parameters);
* totalCount = (int)(parameters[6].Value);
* return ds.Tables[0];
* }
*/
/// <summary>
/// 根据不同的表格选择条件
/// </summary>
/// <param name="n">1：知识点；2：FAQ；3：试题</param>
/// <param name="query"></param>
/// <returns></returns>
public string getCommonWhere(int n, QueryKnowledgeLib query)
{
string where = string.Empty;
if (query.Keywords != Constant.STRING_INVALID_VALUE)
{
where += " AND (Title like '%" + StringHelper.SqlFilter(query.Keywords) + "%' OR Abstract like '%" + StringHelper.SqlFilter(query.Keywords) + "%')";
}
if (query.UnRead != Constant.STRING_INVALID_VALUE) // 未读
{
if (query.UnRead == string.Empty && query.UserID != Constant.INT_INVALID_VALUE)
{
where += " AND KLID NOT IN ( SELECT KLReadTag.KLID FROM KLReadTag WHERE KLReadTag.KLID=KnowledgeLib.KLID AND ReadTag=1 AND UserID=" + query.UserID + ")";
}
}
if (query.Content != Constant.STRING_INVALID_VALUE)
{
if (query.Content == string.Empty)
{
where += " AND isnull(datalength(Content),0) <> 0 ";
}
}
switch (n)
{
case 1:
case 4: if (query.Category != Constant.STRING_INVALID_VALUE) // 题型
{
string[] category = Util.SqlFilterByInCondition(query.Category).Split(',');
where += " AND KLID IN (SELECT a.KLID FROM KLQuestion AS a WHERE a.KLID=KnowledgeLib.KLID AND (";
for (int i = 0; i < category.Length; i++)
{
where += " a.AskCategory=" + category[i] + " OR";
}
where = where.TrimEnd('O', 'R') + ") AND a.Status=0)";
}
;
break;
case 2: if (query.Category != Constant.STRING_INVALID_VALUE) // FAQ
{
string[] category = Util.SqlFilterByInCondition(query.Category).Split(',');
where += " AND KLFAQ.KLID IN (SELECT a.KLID FROM KLQuestion AS a WHERE a.KLID=KLFAQ.KLID AND (";
for (int i = 0; i < category.Length; i++)
{
where += " a.AskCategory=" + category[i] + " OR";
}
where = where.TrimEnd('O', 'R') + ") AND a.Status=0)";
}
;
break;
case 3: if (query.Category != Constant.STRING_INVALID_VALUE) // 试题
{
string[] category = Util.SqlFilterByInCondition(query.Category).Split(',');
where += " AND (";
for (int i = 0; i < category.Length; i++)
{
where += " KLQuestion.AskCategory=" + category[i] + " OR";
}
where = where.TrimEnd('O', 'R') + ") AND KLQuestion.Status=0 ";
}
;
break;
}
if (query.Title != Constant.STRING_INVALID_VALUE)
{
where += " AND KnowledgeLib.Title like '%" + StringHelper.SqlFilter(query.Title) + "%'";
}
if (query.Property != Constant.STRING_INVALID_VALUE) // 属性 1：有附件 2：有FAQ 3：有试题
{
string[] property = query.Property.Split(',');
for (int i = 0; i < property.Length; i++)
{
switch (property[i])
{
case "1": where += " AND KnowledgeLib.UploadFileCount != 0";
break;
case "2": where += " AND KnowledgeLib.FAQCount != 0";
break;
case "3": where += " AND KnowledgeLib.QuestionCount != 0";
break;
}
}
}
if (query.KLFAQID != Constant.INT_INVALID_VALUE)
{
where += " and KLFAQ.KLFAQID=" + query.KLFAQID;
}
if (query.BeginTime != Constant.STRING_INVALID_VALUE)
{
where += " AND KnowledgeLib.CreateTime>='" + StringHelper.SqlFilter(query.BeginTime) + " 0:00:00'";
}
if (query.EndTime != Constant.STRING_INVALID_VALUE)
{
where += " AND KnowledgeLib.CreateTime<='" + StringHelper.SqlFilter(query.EndTime) + " 23:59:59'";
}
if (query.MBeginTime != Constant.STRING_INVALID_VALUE)
{
where += " AND KnowledgeLib.LastModifyTime>='" + StringHelper.SqlFilter(query.MBeginTime) + " 0:00:00'";
}
if (query.MEndTime != Constant.STRING_INVALID_VALUE)
{
where += " AND KnowledgeLib.LastModifyTime<='" + StringHelper.SqlFilter(query.MEndTime) + " 23:59:59'";
}
if (query.CreateUserID != Constant.INT_INVALID_VALUE)
{
where += " AND KnowledgeLib.CreateUserID=" + query.CreateUserID;
}
if (query.LastModifyUserID != Constant.INT_INVALID_VALUE)
{
where += " AND KnowledgeLib.LastModifyUserID=" + query.LastModifyUserID;
}
if (query.Status != Constant.INT_INVALID_VALUE)
{
where += " AND KnowledgeLib.Status IN (" + query.Status + ")";
}
if (query.StatusS != Constant.STRING_INVALID_VALUE)
{
where += " AND KnowledgeLib.Status IN (" + Dal.Util.SqlFilterByInCondition(query.StatusS) + ")";
}
if (query.KCID != Constant.INT_INVALID_VALUE)
{
where += " AND KnowledgeLib.KCID IN (" + Dal.Util.SqlFilterByInCondition(query.KCID.ToString()) + ")";
}
if (query.KCIDS != Constant.STRING_INVALID_VALUE)
{
where += " AND KnowLedgeLib.KCID IN (SELECT ID from f_Cid(" + Dal.Util.SqlFilterByInCondition(query.KCIDS) + "))";
}
if (query.KLID != Constant.INT_INVALID_VALUE)
{
where += " AND KnowledgeLib.KLID=" + query.KLID.ToString();
}
//除了统计，其余列表的状态不能是已删除(统计需要统计状态为已删除的总数)
if (n != 4)
{
where += " AND KnowledgeLib.Status!=4 ";
}
return(where);
}
public void BindData()
{
Entities.QueryKnowledgeLib query = new QueryKnowledgeLib();
//if (!right_Approval && !right_Reject && !right_Disable) //如果不具备 审核通过、驳回、停用的权限 则只能查看到自己提交的信息
//{
// query.CreateUserID = userID;
//} --mod by yangyh 开放权限
if (!right_Approval)
{
btnApproval.Style.Add("display", "none");
}
if (!right_Reject)
{
btnReject.Style.Add("display", "none");
}
if (!right_Disable)
{
btnDisable.Style.Add("display", "none");
}
if (!right_Move)
{
btnMove.Style.Add("display", "none");
}
if (!right_Delete)
{
btnDelete.Style.Add("display", "none");
}
if (RequestTitle != "")
{
query.Title = RequestTitle;
}
if (RequestKCID != "")
{
query.KCIDS = RequestKCID;
}
if (RequestProperty != "")
{
query.Property = RequestProperty;
}
if (RequestStatus != "")
{
query.StatusS = RequestStatus;
}
if (RequestCreateUserID != "")
{
query.CreateUserID = int.Parse(RequestCreateUserID);
}
if (RequestBeginTime != "")
{
query.BeginTime = RequestBeginTime;
}
if (RequestEndTime != "")
{
query.EndTime = RequestEndTime;
}
if (RequestMBeginTime != "")
{
query.MBeginTime = RequestMBeginTime;
}
if (RequestMEndTime != "")
{
query.MEndTime = RequestMEndTime;
}
if (RequestModifyUserID != "")
{
query.LastModifyUserID = int.Parse(RequestModifyUserID);
}
if (RequestCategory != "")
{
query.Category = RequestCategory;
}
string wherePlus = BLL.UserGroupDataRigth.Instance.GetSqlRightstr("KnowledgeLib", "BGID", "CreateUserID", BLL.Util.GetLoginUserID()) + " AND KnowledgeLib.Status!=5 ";
DataTable dt = BLL.KnowledgeLib.Instance.GetKnowledgeLib(query, "LastModifyTime DESC", BLL.PageCommon.Instance.PageIndex, PageSize, out RecordCount, wherePlus);
repeaterTableList.DataSource = dt;
repeaterTableList.DataBind();
litPagerDown.Text = BLL.PageCommon.Instance.LinkStringByPost(BLL.Util.GetUrl(), GroupLength, RecordCount, PageSize, BLL.PageCommon.Instance.PageIndex, 1);
}
public DataTable GetKnowledgeLibCount(QueryKnowledgeLib query, int currentPage, int pageSize, out int totalCount)
{
return(Dal.KnowledgeLib.Instance.GetKnowledgeLibCount(query, currentPage, pageSize, out totalCount));
}
/// <summary>
/// 按照查询条件查询
/// </summary>
/// <param name="query">查询条件</param>
/// <param name="order">排序</param>
/// <param name="currentPage">页号,-1不分页</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="totalCount">总行数</param>
/// <returns>集合</returns>
public DataTable GetKnowledgeLib(QueryKnowledgeLib query, string order, int currentPage, int pageSize, out int totalCount, string wherePlus = "")
{
return(Dal.KnowledgeLib.Instance.GetKnowledgeLib(query, order, currentPage, pageSize, out totalCount, wherePlus));
}
public int GetKLIDAllCount(QueryKnowledgeLib query, out int totalCount)
{
return(Dal.KnowledgeLib.Instance.GetKLIDAllCount(query, out totalCount));
}
public DataTable GetKLFAQForManage(QueryKnowledgeLib query, string order, int currentPage, int pageSize, string wherePlug, out int totalCount)
{
string where = wherePlug;
if (query.Title != Constant.STRING_INVALID_VALUE)
{
where += " AND a.Ask like '%" + StringHelper.SqlFilter(query.Title) + "%'";
}
if (query.BeginTime != Constant.STRING_INVALID_VALUE)
{
where += " AND a.CreateTime>='" + StringHelper.SqlFilter(query.BeginTime) + " 0:00:00'";
}
if (query.EndTime != Constant.STRING_INVALID_VALUE)
{
where += " AND a.CreateTime<='" + StringHelper.SqlFilter(query.EndTime) + " 23:59:59'";
}
if (query.MBeginTime != Constant.STRING_INVALID_VALUE)
{
where += " AND a.ModifyTime>='" + StringHelper.SqlFilter(query.MBeginTime) + " 0:00:00'";
}
if (query.MEndTime != Constant.STRING_INVALID_VALUE)
{
where += " AND a.ModifyTime<='" + StringHelper.SqlFilter(query.MEndTime) + " 23:59:59'";
}
if (query.CreateUserID != Constant.INT_INVALID_VALUE)
{
where += " AND a.CreateUserID=" + query.CreateUserID;
}
if (query.LastModifyUserID != Constant.INT_INVALID_VALUE)
{
where += " AND a.ModifyUserid=" + query.LastModifyUserID;
}
if (query.StatusS != Constant.STRING_INVALID_VALUE)
{
where += " AND (CASE when b.Status =5 THEN a.STATUS ELSE b.STATUS END) IN (" + Dal.Util.SqlFilterByInCondition(query.StatusS) + ")";
}
//if (query.StatusS != Constant.STRING_INVALID_VALUE)
//{
// where += " AND a.Status IN (" + query.StatusS + ")";
//}
DataSet ds;
SqlParameter[] parameters =
{
new SqlParameter("@kcid", query.KCID),
new SqlParameter("@where", where),
new SqlParameter("@order", order),
new SqlParameter("@pagesize", pageSize),
new SqlParameter("@pageIndex", currentPage),
new SqlParameter("@totalCount", SqlDbType.Int, 4)
};
parameters[5].Direction = ParameterDirection.Output;
ds = SqlHelper.ExecuteDataset(CONNECTIONSTRINGS, CommandType.StoredProcedure, "p_KLFAQ_Manage", parameters);
totalCount = (int)(parameters[5].Value);
return(ds.Tables[0]);
}
public DataTable GetKLQuestion(QueryKnowledgeLib query, string order, int currentPage, int pageSize, out int totalCount)
{
return(Dal.KLQuestion.Instance.GetKLQuestion(query, order, currentPage, pageSize, out totalCount));
}
