/// <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(QueryKLQuestion query, string order, int currentPage, int pageSize, out int totalCount)
{
string where = string.Empty;
if (query.KLID != Constant.INT_INVALID_VALUE)
{
where += " And KLQuestion.KLID=" + query.KLID;
}
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>
public bool IsExistsByKLQID(long KLQID)
{
QueryKLQuestion query = new QueryKLQuestion();
query.KLQID = KLQID;
DataTable dt = new DataTable();
int count = 0;
dt = GetKLQuestion(query, string.Empty, 1, 1, out count);
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
public string getQuestionWhere(QueryKLQuestion query)
{
string where = string.Empty;
//问题 标题
if (query.Ask != Constant.STRING_INVALID_VALUE)
{
where += " AND KLQuestion.Ask like '%" + StringHelper.SqlFilter(query.Ask) + "%'";
}
//知识点状态
if (query.StatusS != Constant.STRING_INVALID_VALUE)
{
where += " AND KnowledgeLib.Status IN (" + Dal.Util.SqlFilterByInCondition(query.StatusS) + ")";
}
//创建时间
if (query.BeginTime != Constant.STRING_INVALID_VALUE)
{
where += " AND KLQuestion.CreateTime>='" + StringHelper.SqlFilter(query.BeginTime) + " 0:00:00'";
}
if (query.EndTime != Constant.STRING_INVALID_VALUE)
{
where += " AND KLQuestion.CreateTime<='" + StringHelper.SqlFilter(query.EndTime) + " 23:59:59'";
}
//创建人
if (query.CreateUserID != Constant.INT_INVALID_VALUE)
{
where += " AND KLQuestion.CreateUserID=" + query.CreateUserID;
}
//试题状态
if (query.QuestionStatusS != Constant.STRING_INVALID_VALUE)
{
where += " AND KLQuestion.Status IN (" + Dal.Util.SqlFilterByInCondition(query.QuestionStatusS) + ")";
}
//KCID
if (query.KCID != Constant.INT_INVALID_VALUE)
{
where += " AND KnowLedgeLib.KCID=" + query.KCID;
}
return(where);
}
/// <summary>
/// 按照查询条件查询(供知识库管理列表页使用 刘学文 10.24)
/// </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 GetKLQuestionMnage(QueryKLQuestion query, string order, int currentPage, int pageSize, string wherePlus, out int totalCount)
{
string where = wherePlus;
if (query.Ask != Constant.STRING_INVALID_VALUE)
{
where += " AND a.Ask like '%" + StringHelper.SqlFilter(query.Ask) + "%'";
}
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.ModifyUserID != Constant.INT_INVALID_VALUE)
{
where += " AND a.ModifyUserid=" + query.ModifyUserID;
}
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.AskCategorys != Constant.STRING_INVALID_VALUE)
{
where += " AND a.askcategory IN (" + Dal.Util.SqlFilterByInCondition(query.AskCategorys) + ")";
}
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_KLQuestion_Manage", parameters);
totalCount = (int)(parameters[5].Value);
return(ds.Tables[0]);
}
//删除
public void deleteKnowledgeLib(out string msg)
{
msg = string.Empty;
string[] KLIDs = RequestKLID.Split(',');
if (KLIDs.Length == 0)
{
msg += "{'msg':'没有对应的ID值，操作失败'}";
return;
}
string klqids = string.Empty; //记录试题库中的主键信息；已 1,2,3|4,5,6|7,8,9 形式隔开
for (int j = 0; j < KLIDs.Length; j++)
{
//根据KLID找到试题库中的信息
QueryKLQuestion query = new QueryKLQuestion();
query.KLID = long.Parse(KLIDs[j]);
int count;
DataTable dt = BLL.KLQuestion.Instance.GetKLQuestion(query, "", 1, 10000, out count);
for (int m = 0; m < dt.Rows.Count; m++)
{
klqids += dt.Rows[m]["KLQID"].ToString() + ",";
}
klqids = klqids.TrimEnd(',') + "|";
}
klqids = klqids.TrimEnd('|');
#region 除 事务操作
string connectionstrings = ConfigurationUtil.GetAppSettingValue("ConnectionStrings_CC");
SqlConnection connection = new SqlConnection(connectionstrings);
connection.Open();
SqlTransaction tran = connection.BeginTransaction("SampleTransaction");
try
{
//1 将知识库信息状态修改为 4已删除
operateKnowledgeLib(4, tran, out msg);
for (int i = 0; i < KLIDs.Length; i++)
{
//2 将KLQuestion知识库试题记录修改为 -1已删除
string[] klqid1 = klqids.Split('|');
for (int n = 0; n < klqid1.Length; n++)
{
string[] klqid2 = klqid1[n].Split(',');
for (int p = 0; p < klqid2.Length; p++)
{
deleteKLQuestionByKLID(tran, klqid2[p]);
}
}
//3 将KLUploadFile 知识库上传文件信息表记录 修改为 -1已删除
deleteKLUploadFileByKLID(tran, KLIDs[i]);
//4 将KLFAQ知识库FAQ表记录修改为 -1已删除
deleteKLFAQByKLID(tran, KLIDs[i]);
//5 插入一条记录到KLOptionLog知识库操作日志表
InsertKLOptionLog(tran, (int)Entities.EnumOptStatus.Delete, KLIDs[i]);
}
//事务提交
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
msg = "{'msg':'" + ex.Message.ToString() + "'}";
}
finally
{
connection.Close();
}
#endregion
}
public DataTable GetKLQuestionManage(QueryKLQuestion query, string order, int currentPage, int pageSize, string wherePlus, out int totalCount)
{
return(Dal.KLQuestion.Instance.GetKLQuestionMnage(query, order, currentPage, pageSize, wherePlus, out totalCount));
}