/// <summary>
/// 取得当前需要学习的课程
/// </summary>
/// <param name="AssistCategoryID">教材体系ID</param>
/// <param name="orgID">所属单位ID</param>
/// <param name="postID">岗位ID</param>
/// <param name="isGroupleader">是否班组长</param>
/// <param name="tech">技能等级</param>
/// <param name="row">取得记录条数</param>
/// <returns></returns>
public IList <AssistBook> GetStudyAssistBookInfoByAssistBookCategoryID(int AssistCategoryID, int orgID, int postID, int isGroupleader, int tech, int row)
{
IList <AssistBook> bookList = new List <AssistBook>();
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "USP_ASSIST_BOOK_TRAIN_G";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "p_assist_category_id", DbType.Int32, AssistCategoryID);
db.AddInParameter(dbCommand, "p_train_type_id", DbType.Int32, 0);
db.AddInParameter(dbCommand, "p_org_id", DbType.Int32, orgID);
db.AddInParameter(dbCommand, "p_post_id", DbType.Int32, postID);
db.AddInParameter(dbCommand, "p_is_group_leader", DbType.Int32, isGroupleader);
db.AddInParameter(dbCommand, "p_tech_id", DbType.Int32, tech);
db.AddInParameter(dbCommand, "p_row_num", DbType.Int32, row);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
while (dataReader.Read())
{
AssistBook book = CreateModelObject(dataReader);
bookList.Add(book);
}
}
return(bookList);
}
/// <summary>
/// 查询
/// </summary>
/// <param name="AssistBookId"></param>
/// <param name="bookName"></param>
/// <param name="AssistCategoryId"></param>
/// <param name="bookNo"></param>
/// <param name="publishOrg"></param>
/// <param name="publishDate"></param>
/// <param name="pageCount"></param>
/// <param name="wordCount"></param>
/// <param name="authors"></param>
/// <param name="keyWords"></param>
/// <param name="description">简介</param>
/// <param name="memo">备注</param>
/// <param name="startRowIndex">起始记录行</param>
/// <param name="maximumRows">每页记录条数</param>
/// <param name="orderBy">排序字符串，如"FieldName ASC"</param>
/// <returns></returns>
public IList <AssistBook> GetAssistBook(int AssistBookId, string bookName, int AssistCategoryId, string bookNo, int publishOrg,
DateTime publishDate, int pageCount, int wordCount, string authors, string keyWords, string revisers,
string bookmaker, string coverdesigner, string url, string description, string memo,
int startRowIndex, int maximumRows, string orderBy)
{
IList <AssistBook> books = new List <AssistBook>();
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "USP_ASSIST_BOOK_S";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "p_start_row_index", DbType.Int32, startRowIndex);
db.AddInParameter(dbCommand, "p_page_size", DbType.Int32, maximumRows);
db.AddInParameter(dbCommand, "p_order_by", DbType.AnsiString, GetMappingOrderBy(orderBy));
db.AddOutParameter(dbCommand, "p_count", DbType.Int32, 4);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
while (dataReader.Read())
{
AssistBook book = CreateModelObject(dataReader);
books.Add(book);
}
}
_recordCount = Convert.ToInt32(db.GetParameterValue(dbCommand, "p_count"));
return(books);
}
public IList <AssistBook> GetAssistBookByTrainTypeID(int trainTypeID, string bookName, string keyWords, string authors, int orgid)
{
IList <AssistBook> books = new List <AssistBook>();
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "USP_ASSIST_BOOK_Q_TrainType";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "p_train_type_ID", DbType.Int32, trainTypeID);
db.AddInParameter(dbCommand, "p_book_Name", DbType.String, bookName);
db.AddInParameter(dbCommand, "p_keyWords", DbType.String, keyWords);
db.AddInParameter(dbCommand, "p_authors", DbType.String, authors);
db.AddInParameter(dbCommand, "p_org_id", DbType.Int32, orgid);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
while (dataReader.Read())
{
AssistBook book = CreateModelObject(dataReader);
books.Add(book);
}
}
return(books);
}
public int AddAssistBook(AssistBook book)
{
int bookid = dal.AddAssistBook(book, SessionSet.EmployeeName);
objLogBll.WriteLog("新增辅导教材《" + book.BookName + "》基本信息");
return(bookid);
}
public IList <AssistBook> GetAssistBookByTrainTypeIDPath(string trainTypeIDPath, int orgid)
{
IList <AssistBook> books = new List <AssistBook>();
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "USP_ASSIST_BOOK_S_TrainType";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "p_id_path", DbType.String, trainTypeIDPath);
db.AddInParameter(dbCommand, "p_org_id", DbType.Int32, orgid);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
while (dataReader.Read())
{
AssistBook book = CreateModelObject(dataReader);
books.Add(book);
}
}
return(books);
}
public IList <AssistBook> GetAssistBookByAssistBookCategoryID(int AssistCategoryId, int orgid)
{
IList <AssistBook> books = new List <AssistBook>();
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "USP_ASSIST_BOOK_Q_CategoryID";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "p_assist_category_id", DbType.Int32, AssistCategoryId);
db.AddInParameter(dbCommand, "p_org_id", DbType.Int32, orgid);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
while (dataReader.Read())
{
AssistBook book = CreateModelObject(dataReader);
books.Add(book);
}
}
return(books);
}
/// <summary>
/// 取得所有book信息
/// </summary>
/// <returns></returns>
public IList <AssistBook> GetAllAssistBookInfo(int orgID)
{
IList <AssistBook> bookList = new List <AssistBook>();
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "USP_ASSIST_BOOK_G";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "p_assist_book_id", DbType.Int32, 0);
db.AddInParameter(dbCommand, "p_org_id", DbType.Int32, orgID);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
while (dataReader.Read())
{
AssistBook book = CreateModelObject(dataReader);
bookList.Add(book);
}
}
return(bookList);
}
public void UpdateAssistBook(AssistBook book, string EmployeeName)
{
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "USP_ASSIST_BOOK_U";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "p_assist_book_id", DbType.Int32, book.AssistBookId);
db.AddInParameter(dbCommand, "p_book_name", DbType.String, book.BookName);
db.AddInParameter(dbCommand, "p_assist_category_id", DbType.Int32, book.AssistBookCategoryId);
db.AddInParameter(dbCommand, "p_book_no", DbType.String, book.BookNo);
db.AddInParameter(dbCommand, "p_publish_org", DbType.Int32, book.PublishOrg);
db.AddInParameter(dbCommand, "p_publish_date", DbType.DateTime, book.PublishDate);
db.AddInParameter(dbCommand, "p_authors", DbType.String, book.Authors);
db.AddInParameter(dbCommand, "p_revisers", DbType.String, book.Revisers);
db.AddInParameter(dbCommand, "p_bookmaker", DbType.String, book.Bookmaker);
db.AddInParameter(dbCommand, "p_cover_designer", DbType.String, book.CoverDesigner);
db.AddInParameter(dbCommand, "p_keywords", DbType.String, book.KeyWords);
db.AddInParameter(dbCommand, "p_page_count", DbType.Int32, book.PageCount);
db.AddInParameter(dbCommand, "p_word_count", DbType.Int32, book.WordCount);
db.AddInParameter(dbCommand, "p_description", DbType.String, book.Description);
db.AddInParameter(dbCommand, "p_url", DbType.String, book.url);
db.AddInParameter(dbCommand, "p_last_update_person", DbType.String, EmployeeName);
//db.AddInParameter(dbCommand, "p_last_update_date", DbType.DateTime, DateTime.Now);
db.AddInParameter(dbCommand, "p_memo", DbType.String, book.Memo);
db.AddInParameter(dbCommand, "p_is_group_leader", DbType.Int32, book.IsGroupLearder);
db.AddInParameter(dbCommand, "p_tech_id", DbType.Int32, book.TechnicianTypeID);
db.AddInParameter(dbCommand, "p_order_index", DbType.Int32, book.OrderIndex);
sqlCommand = "USP_ASSIST_BOOK_TRAIN_TYPE_D";
DbCommand dbCommand1 = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand1, "p_assist_book_id", DbType.Int32, book.AssistBookId);
sqlCommand = "USP_ASSIST_BOOK_RANGE_ORG_D";
DbCommand dbCommand2 = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand2, "p_assist_book_id", DbType.Int32, book.AssistBookId);
sqlCommand = "USP_ASSIST_BOOK_RANGE_POST_D";
DbCommand dbCommand3 = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand3, "p_assist_book_id", DbType.Int32, book.AssistBookId);
DbConnection connection = db.CreateConnection();
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
if (book.trainTypeidAL.Count != 0)
{
db.ExecuteNonQuery(dbCommand1, transaction);
}
db.ExecuteNonQuery(dbCommand, transaction);
if (book.orgidAL.Count != 0)
{
db.ExecuteNonQuery(dbCommand2, transaction);
}
if (book.postidAL.Count != 0)
{
db.ExecuteNonQuery(dbCommand3, transaction);
}
for (int i = 0; i < book.orgidAL.Count; i++)
{
sqlCommand = "USP_ASSIST_BOOK_RANGE_ORG_I";
dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "p_assist_book_id", DbType.Int32, book.AssistBookId);
db.AddInParameter(dbCommand, "p_org_id", DbType.Int32, int.Parse(book.orgidAL[i].ToString()));
db.ExecuteNonQuery(dbCommand, transaction);
}
for (int i = 0; i < book.postidAL.Count; i++)
{
sqlCommand = "USP_ASSIST_BOOK_RANGE_POST_I";
dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "p_assist_book_id", DbType.Int32, book.AssistBookId);
db.AddInParameter(dbCommand, "p_post_id", DbType.Int32, int.Parse(book.postidAL[i].ToString()));
db.ExecuteNonQuery(dbCommand, transaction);
}
for (int i = 0; i < book.trainTypeidAL.Count; i++)
{
sqlCommand = "USP_ASSIST_BOOK_TRAIN_TYPE_I";
dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "p_assist_book_id", DbType.Int32, book.AssistBookId);
db.AddInParameter(dbCommand, "p_train_type_id", DbType.Int32, int.Parse(book.trainTypeidAL[i].ToString()));
db.ExecuteNonQuery(dbCommand, transaction);
}
transaction.Commit();
}
catch
{
transaction.Rollback();
}
connection.Close();
}
/// <summary>
/// 根据辅导教材ID查询教材
/// </summary>
/// <param name="bookID"></param>
/// <returns></returns>
public AssistBook GetAssistBook(int bookID)
{
AssistBook book = null;
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "USP_ASSIST_BOOK_G";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "p_assist_book_id", DbType.Int32, bookID);
db.AddInParameter(dbCommand, "p_org_id", DbType.Int32, 0);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
if (dataReader.Read())
{
book = CreateModelObject(dataReader);
}
}
sqlCommand = "USP_ASSIST_BOOK_RANGE_ORG_S";
DbCommand dbCommand1 = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand1, "p_assist_book_id", DbType.Int32, bookID);
sqlCommand = "USP_ASSIST_BOOK_RANGE_POST_S";
DbCommand dbCommand2 = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand2, "p_assist_book_id", DbType.Int32, bookID);
sqlCommand = "USP_ASSIST_BOOK_TRAIN_TYPE_S";
DbCommand dbCommand3 = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand3, "p_assist_book_id", DbType.Int32, bookID);
IDataReader dataReader1 = db.ExecuteReader(dbCommand1);
IDataReader dataReader2 = db.ExecuteReader(dbCommand2);
IDataReader dataReader3 = db.ExecuteReader(dbCommand3);
ArrayList orgidAL = new ArrayList();
ArrayList postidAL = new ArrayList();
ArrayList trainTypeidAL = new ArrayList();
string strTrainTypeNames = string.Empty;
AssistBookCategoryDAL assistDAL = new AssistBookCategoryDAL();
AssistBookCategory assistcategory = assistDAL.GetAssistBookCategory(book.AssistBookCategoryId);
book.AssistBookCategoryName = GetAssistAssistBookCategoryNames("/" + assistcategory.AssistBookCategoryName, assistcategory.ParentId);
while (dataReader1.Read())
{
if (dataReader1["ORG_ID"].ToString() != "")
{
orgidAL.Add(DataConvert.ToInt(dataReader1["ORG_ID"].ToString()));
}
}
while (dataReader2.Read())
{
if (dataReader2["POST_ID"].ToString() != "")
{
postidAL.Add(DataConvert.ToInt(dataReader2["POST_ID"].ToString()));
}
}
while (dataReader3.Read())
{
if (dataReader3["TRAIN_TYPE_ID"].ToString() != "")
{
trainTypeidAL.Add(DataConvert.ToInt(dataReader3["TRAIN_TYPE_ID"].ToString()));
strTrainTypeNames += GetTrainTypeNames("/" + dataReader3["TYPE_NAME"].ToString(), int.Parse(dataReader3["PARENT_ID"].ToString())) + ",";
}
}
if (strTrainTypeNames.Length > 0)
{
strTrainTypeNames = strTrainTypeNames.Substring(0, strTrainTypeNames.Length - 1);
}
book.orgidAL = orgidAL;
book.postidAL = postidAL;
book.trainTypeidAL = trainTypeidAL;
book.trainTypeNames = strTrainTypeNames;
return(book);
}
public void DeleteAssistBook(AssistBook book)
{
DeleteAssistBook(book.AssistBookCategoryId);
objLogBll.WriteLog("删除辅导教材《" + book.BookName + "》基本信息");
}
public void UpdateAssistBook(AssistBook book)
{
dal.UpdateAssistBook(book, SessionSet.EmployeeName);
objLogBll.WriteLog("修改辅导教材《" + book.BookName + "》基本信息");
}
