private static bool AddLink(TrainingSign_Entities context, Guid trainingID, List <tbl_lector> lectors) { //TODO 这里还没有检查lectors id的存在性 foreach (var lec in lectors) { //关联 var entry = new tbl_training_lector_link { ref_training_id = trainingID, ref_lector_id = lec.id }; if (!context.tbl_training_lector_link.Any(x => x.ref_lector_id == entry.ref_lector_id && x.ref_training_id == entry.ref_training_id)) { try { context.tbl_training_lector_link.Add(entry); context.SaveChanges(); } catch (Exception ex) { LogHelper.WriteError(typeof(TrainingInfo), ex); return(false); } } } return(true); }
internal static IList <tbl_lector> LoadByCourseID(string sCourID) { Guid gid = Guid.Empty; if (!Guid.TryParse(sCourID, out gid)) { return(null); } using (var context = new TrainingSign_Entities()) { var recs = from c in context.tbl_course from l in context.tbl_lector_course_link from lec in context.tbl_lector where c.id == l.ref_course_id && l.ref_lector_id == lec.id && c.id == gid select lec; if (recs.Any()) { return(recs.ToList()); } return(null); } }
internal static Tuple <bool, Guid> Insert(string courID, List <tbl_lector> lectors, string sOrganizer, string sVenue, DateTime dtStart, DateTime dtEnd, out string serr) { var res = new Tuple <bool, Guid>(false, Guid.Empty); bool bOk = false; serr = string.Empty; Guid gid = Guid.Empty; if (!Guid.TryParse(courID, out gid)) { serr = "Wrong course ID"; return(res); } Guid newID = Guid.Empty; using (var context = new TrainingSign_Entities()) { using (var dbTran = context.Database.BeginTransaction()) { //insert var entity = new tbl_training() { id = Guid.NewGuid(), ref_course_id = gid, organizer = sOrganizer, venue = sVenue, plan_start_time = dtStart, plan_end_time = dtEnd }; try { context.tbl_training.Add(entity); context.SaveChanges(); bOk = true; } catch (Exception ex) { //serr = ex.Message; serr = "创建培训失败!"; return(res); } newID = entity.id; //link lector bOk = bOk && AddLink(context, entity.id, lectors); if (bOk) { dbTran.Commit(); } } } return(new Tuple <bool, Guid>(bOk, newID)); }
public static List <TTrainingInfo> LoadUnfinishTraining() { using (var context = new TrainingSign_Entities()) { var its = ((from tr in context.tbl_training where false == tr.actual_end_time.HasValue join cour in context.tbl_course on tr.ref_course_id equals cour.id select new { id = tr.id, course_no = cour.course_no, course_content = cour.course_context, organizer = tr.organizer, venue = tr.venue, plan_start_time = tr.plan_start_time, plan_end_time = tr.plan_end_time }).AsEnumerable() .Select(x => new TTrainingInfo { id = x.id, course_no = x.course_no, course_content = x.course_content, organizer = x.organizer, venue = x.venue, lectors_str = string.Join("/", GetCourseLectors(x.course_no)), plan_time_str = MakeTimeRangeStr(x.plan_start_time, x.plan_end_time), text = $"【{x.course_no}】 {x.course_content}({MakeTimeRangeStr(x.plan_start_time, x.plan_end_time)})" }) ).ToList(); return(its); } }
//下载所有课程本月的记录 internal static byte[] ExportToExcelMon(DateTime dt) { using (var context = new TrainingSign_Entities()) { var conn = context.Database.Connection as SqlConnection; var sFmt = @" select p.workid, p.name, p.department, p.organizer, c.course_no, c.course_context, t.venue, dbo.Get_training_lectors_en(t.id) as Lectors, dbo.Get_Training_Time_str(t.plan_start_time,t.plan_end_time) as Training_Time, Convert(varchar(30),p.signinTime,120) as Signin_Time from tbl_trainee p, tbl_training t, tbl_course c where p.ref_training_id=t.id and t.ref_course_id=c.id and substring(CONVERT(varchar(30), p.signinTime, 120), 0, 8)='{0}' order by c.course_no, p.signinTime "; var sSql = string.Format(sFmt, dt.ToString("yyyy-MM")); var dr = SqlServerHelper.ExecuteQueryReader(conn, sSql); if (!dr.HasRows) { return(null); } return(EPPExcelHelper.BuilderExcel(dr)); } }
private static bool InsertLector(string sWorkID, string eName, string cName, out string serr) { bool bOk = false; serr = string.Empty; using (var context = new TrainingSign_Entities()) { //insert var entity = new tbl_lector() { id = Guid.NewGuid(), lector_workid = sWorkID, lector_en_name = eName, lector_cn_name = cName }; try { context.tbl_lector.Add(entity); context.SaveChanges(); bOk = true; } catch (Exception ex) { //serr = ex.Message; serr = "此工号已存在,不能重复添加"; } } return(bOk); }
public static bool DeleteUser(int id, out string errmsg) { bool bOk = false; errmsg = string.Empty; using (var mContext = new TrainingSign_Entities()) { var persons = from p in mContext.sys_user where p.id == id select p; if (persons.Any()) { var obj = persons.First(); mContext.sys_user.Remove(obj); try { mContext.SaveChanges(); bOk = true; } catch (Exception ex) { errmsg = ex.Message; } } } return(bOk); }
private static bool RemoveGrantAll(string lectorId) { var bOk = false; Guid gid = Guid.Empty; if (!Guid.TryParse(lectorId, out gid)) { return(false); } using (var context = new TrainingSign_Entities()) { var its = from p in context.tbl_lector_course_link where p.ref_lector_id == gid select p; if (its.Any()) { context.tbl_lector_course_link.RemoveRange(its); try { context.SaveChanges(); bOk = true; } catch (Exception ex) { bOk = false; } } } return(bOk); }
//置空此讲师已经讲过的课 internal static bool UnlinkLector(Guid gLectorID) { bool bOk = false; using (var context = new TrainingSign_Entities()) { var its = from p in context.tbl_training_lector_link where p.ref_lector_id == gLectorID select p; if (its.Any()) { context.tbl_training_lector_link.RemoveRange(its); } try { context.SaveChanges(); bOk = true; } catch (Exception ex) { LogHelper.WriteError(typeof(TrainingInfo), ex); } } return(bOk); }
public static bool EnableUser(int uid, bool bEnabled, out string errmsg) { bool bOk = false; using (var context = new TrainingSign_Entities()) { var persons = from p in context.sys_user where p.id == uid select p; foreach (var obj in persons) { obj.IsValid = bEnabled; } try { context.SaveChanges(); bOk = true; errmsg = string.Empty; } catch (Exception ex) { errmsg = ex.Message; } } return(bOk); }
public static bool DeleteByID(string sid, out string serr) { var bOk = false; serr = string.Empty; int id = -1; if (!int.TryParse(sid, out id)) { return(false); } using (var context = new TrainingSign_Entities()) { var its = from p in context.tbl_trainee where p.id == id select p; if (its.Any()) { var obj = its.First(); context.tbl_trainee.Remove(obj); LogDeleteTrainee(obj); } try { context.SaveChanges(); bOk = true; } catch (Exception ex) { serr = "删除学员失败!"; } } return(bOk); }
internal static List <sys_user> LoadAll() { using (var context = new TrainingSign_Entities()) { var users = from x in context.sys_user select x; return(users.ToList()); } }
//取记录数 internal static int GetCountByTrainingID(Guid gTrainingID) { using (var context = new TrainingSign_Entities()) { var cnt = context.tbl_trainee.Where(x => x.ref_training_id == gTrainingID).Count(); return(cnt); } }
public static void Update(sys_user user) { using (var context = new TrainingSign_Entities()) { //user.LastLogon = DateTime.Now; context.Entry(user).State = EntityState.Modified; context.SaveChanges(); } }
/// <summary> /// 检查是否讲师或是否已经有授权 /// </summary> /// <returns></returns> internal static int CheckByWorkID(string sWorkID, string courseid, out TPersonInfo lecInfo, out string serr) { lecInfo = null; serr = string.Empty; var custom = WorkIDInfo.GetEmployeeECardInfo(sWorkID); if (null == custom) { serr = "没有找到员工信息"; return(-1); } var workid = custom.OutID; if (null == GetByWorkID(workid)) { serr = "不是讲师"; return(-1); } Guid gid = Guid.Empty; if (!Guid.TryParse(courseid, out gid)) { serr = "无效课程ID"; return(-1); } //check for course privilege using (var context = new TrainingSign_Entities()) { var people = from p in context.tbl_lector from q in context.tbl_lector_course_link from c in context.tbl_course where string.Compare(p.lector_workid, workid, StringComparison.InvariantCultureIgnoreCase) == 0 && c.id == gid && q.ref_lector_id == p.id && q.ref_course_id == c.id select new TPersonInfo { id = p.id, workid = p.lector_workid, cn_name = p.lector_cn_name, en_name = p.lector_en_name }; if (people.Any()) { lecInfo = people.First(); return(1); } else { serr = "讲师没有此课程的授权"; return(0); } } }
public static bool DeleteByID(string id, out string serr) { var bOk = false; Guid gid = Guid.Empty; serr = string.Empty; if (!Guid.TryParse(id, out gid)) { return(false); } using (var context = new TrainingSign_Entities()) { var its = from p in context.tbl_course where p.id == gid select p; if (its.Any()) { var obj = its.First(); //2018-03-04 由于部署到正式库之前,已经有了有用的数据,但在拷贝库的时候导致约束失效 // 所以 FK_TBL_LECT_REFERENCE_TBL_COUR 一直没有起作用 // 只能手动检查了 var qry = from q in context.tbl_lector_course_link where q.ref_course_id == obj.id select q; if (qry.Any()) { serr = "课程已授权给讲师,无法删除。<br>请先取消课程授权!"; return(false); } var qry2 = from q in context.tbl_training where q.ref_course_id == obj.id select q; if (qry2.Any()) { serr = "课程已开过课,无法删除。"; return(false); } context.tbl_course.Remove(obj); } try { context.SaveChanges(); bOk = true; } catch (Exception ex) { serr = "课程已授权给讲师,无法删除。<br>请先取消课程授权!"; } } return(bOk); }
internal static bool ImportDatatable(DataTable dt, out string serr) { using (var context = new TrainingSign_Entities()) { var conn = context.Database.Connection; if (conn.State == ConnectionState.Closed) { conn.Open(); } return(DatabaseHelper.ImportDatatable(conn, dt, "tbl_course", out serr)); } }
public static bool HasOtherUsers() { //sys_user user = null; using (var context = new TrainingSign_Entities()) { var peoples = context.sys_user; if (peoples.Any()) { return(true); } } return(false); }
private static List <string> GetCourseLectors(string course_no) { using (var context = new TrainingSign_Entities()) { var qry = from c in context.tbl_course from l in context.tbl_lector_course_link from lec in context.tbl_lector where c.course_no.Equals(course_no) && c.id.Equals(l.ref_course_id) && lec.id.Equals(l.ref_lector_id) select lec.lector_en_name; return(qry.ToList()); } }
internal static IList <tbl_lector> LoadAll() { using (var context = new TrainingSign_Entities()) { var recs = from p in context.tbl_lector orderby p.lector_workid select p; if (recs.Any()) { return(recs.ToList()); } return(null); } }
internal static bool AddLinkByCourseID(string courseId, List <string> lectors) { Guid courID = Guid.Empty; if (!Guid.TryParse(courseId, out courID)) { return(false); } using (var context = new TrainingSign_Entities()) { var qc = from c in context.tbl_course where c.id == courID select c; if (!qc.Any()) { return(false); } foreach (var lec in lectors) { Guid lecID = Guid.Empty; if (!Guid.TryParse(lec, out lecID)) { continue; } //关联 var entry = new tbl_lector_course_link { ref_course_id = courID, ref_lector_id = lecID }; if (!context.tbl_lector_course_link.Any(x => x.ref_lector_id == entry.ref_lector_id && x.ref_course_id == entry.ref_course_id)) { try { context.tbl_lector_course_link.Add(entry); context.SaveChanges(); } catch (Exception ex) { LogHelper.WriteError(typeof(LectorCourseLinkInfo), ex); } } } } return(true); }
internal static Tuple <int, float> CalcSum(string sTrainingID) { var res = new Tuple <int, float>(0, 0.0f); Guid gid = Guid.Empty; if (!Guid.TryParse(sTrainingID, out gid)) { return(res); } float fCourse = 0.0f; int ncnt = 0; using (var context = new TrainingSign_Entities()) { var qry = from t in context.tbl_training from r in context.tbl_trainee where t.id == gid && t.id == r.ref_training_id select r; ncnt = qry.Count(); var qry2 = from tt in context.tbl_training where tt.id == gid select tt; if (qry2.Any()) { var item = qry2.First(); //实际计数 //var diff = DateTime.Now - item.plan_start_time.Value; var dtEnd = item.plan_end_time.HasValue ? item.plan_end_time.Value : DateTime.Now; var diff = dtEnd - item.plan_start_time.Value; if (diff.TotalHours > 0.0f) { fCourse = (float)diff.TotalHours; } else { // 使用标准时长 var citem = CourseInfo.GetByID(item.ref_course_id.ToString()); if (citem != null) { fCourse = citem.course_time.HasValue ? (float)citem.course_time.Value : 0.0f; } } } } return(new Tuple <int, float>(ncnt, fCourse)); }
public static IList <tbl_course> LoadAll() { using (var context = new TrainingSign_Entities()) { var recs = from p in context.tbl_course orderby p.course_no select p; if (recs.Any()) { return(recs.ToList()); } return(null); } }
internal static tbl_lector GetByWorkID(string sWorkID) { using (var context = new TrainingSign_Entities()) { var people = from p in context.tbl_lector where 0 == string.Compare(p.lector_workid, sWorkID, StringComparison.InvariantCultureIgnoreCase) select p; if (people.Any()) { return(people.First()); } return(null); } }
//TODO 结束培训时的讲师工号没有作有效性检查 internal static bool EndTrain(Guid gid, int nPlanReach, int nActualReach, DateTime dtEndTime, double fTotTrainingTime, int nPass, string endWorkid, out string serr) { serr = string.Empty; //Guid gid = Guid.Empty; //if (!Guid.TryParse(sTrainingID, out gid)) //{ // serr = "training ID has problem"; // return false; //} var bOk = false; using (var context = new TrainingSign_Entities()) { var qry = from t in context.tbl_training where t.id == gid select t; if (!qry.Any()) { serr = "training ID not found!"; return(false); } var item = qry.First(); item.actual_end_time = dtEndTime; item.plan_reach = nPlanReach; item.actual_reach = nActualReach; item.total_training_time = fTotTrainingTime; item.pass = nPass; item.end_lector_workid = endWorkid; try { context.SaveChanges(); bOk = true; } catch (Exception ex) { LogHelper.WriteError(typeof(TrainingInfo), ex); serr = "Close Training failed"; } } return(bOk); }
public static void UpdateUserLoginTimeByAd(string sAdName) { using (var context = new TrainingSign_Entities()) { var people = from p in context.sys_user where (0 == String.Compare(p.ADAccount, sAdName, StringComparison.InvariantCultureIgnoreCase)) select p; if (people.Any()) { var user = people.First(); user.LastLogon = DateTime.Now; context.Entry(user).State = EntityState.Modified; context.SaveChanges(); } } }
public static sys_user GetUserInfoByAd(string sAdName) { sys_user user = null; using (var context = new TrainingSign_Entities()) { var people = from p in context.sys_user where (0 == String.Compare(p.ADAccount, sAdName, StringComparison.InvariantCultureIgnoreCase)) select p; if (people.Any()) { user = people.First(); } } return(user); }
internal static void AddLink(string sCourseNo, string[] lectorWorkIDs) { using (var context = new TrainingSign_Entities()) { var qp = from p in context.tbl_lector where lectorWorkIDs.Contains(p.lector_workid) select p; var qc = from c in context.tbl_course where 0 == string.Compare(c.course_no, sCourseNo, StringComparison.InvariantCultureIgnoreCase) select c; var people = qp.ToList(); var course = qc.ToList(); if (!people.Any() || !course.Any()) { return; } foreach (var lec in people) { var c = course.First(); //关联 var entry = new tbl_lector_course_link { ref_course_id = c.id, ref_lector_id = lec.id }; if (!context.tbl_lector_course_link.Any(x => x.ref_lector_id == entry.ref_lector_id && x.ref_course_id == entry.ref_course_id)) { try { context.tbl_lector_course_link.Add(entry); context.SaveChanges(); } catch (Exception ex) { LogHelper.WriteError(typeof(LectorCourseLinkInfo), ex); } } } } }
//TODO 也许该与GetTrainingLog整合 internal static List <TTrainingLogInfo> LoadTrainingLog(Guid gTrainingID) { var lst = new List <TTrainingLogInfo>(); using (var context = new TrainingSign_Entities()) { var qry = from p in context.tbl_trainee from t in context.tbl_training from c in context.tbl_course where t.id == gTrainingID && p.ref_training_id == t.id && t.ref_course_id == c.id select new { id = p.id, workid = p.workid, cn_name = p.name, dept_name = p.department, organ_name = p.organizer, course_no = c.course_no, course_context = c.course_context, plan_start_time = t.plan_start_time, plan_end_time = t.plan_end_time, signinTime = p.signinTime }; var its = from x in qry.AsEnumerable() select new TTrainingLogInfo { id = x.id, workid = x.workid, cn_name = x.cn_name, dept_name = x.dept_name, organ_name = x.organ_name, course_no = x.course_no, course_context = x.course_context, training_time = LocalFormatStr.GetLocalTimeStrHHMM(x.plan_start_time.Value) + (x.plan_end_time.HasValue ? "~" + LocalFormatStr.GetLocalTimeStrHHMM(x.plan_end_time.Value) : ""), signin_time = x.signinTime.HasValue ? LocalFormatStr.GetLocalTimeStr(x.signinTime.Value) : "" }; return(its.Any() ? its.ToList() : lst); } }
internal static DataTable QueryMonthSummary(string sYearMon, bool bShowEmpty) { using (var context = new TrainingSign_Entities()) { var conn = context.Database.Connection as SqlConnection; var sFmt = string.Empty; if (bShowEmpty) { sFmt = @" select c.course_no as '课程号', c.course_context as '课程主题', c.course_time as '课程时长', count(distinct(t.id)) as '开课次数', COUNT(tr.name) AS '受训总人数', CASE WHEN COUNT(tr.name)>0 THEN sum(c.course_time) ELSE 0 END as '总培训时长' from tbl_course c LEFT JOIN tbl_training t ON c.id=t.ref_course_id AND substring(CONVERT(varchar(30), t.plan_start_time, 120), 0, 8)='{0}' LEFT JOIN tbl_trainee tr ON tr.ref_training_id=t.id group by c.course_no, c.course_context, c.course_time order by c.course_no; "; } else { sFmt = @" select c.course_no as '课程号', c.course_context as '课程主题', c.course_time as '课程时长', count(distinct(t.id)) as '开课次数', COUNT(tr.name) AS '受训总人数', CASE WHEN COUNT(tr.name)>0 THEN sum(c.course_time) ELSE 0 END as '总培训时长' from tbl_course c JOIN tbl_training t ON c.id=t.ref_course_id AND substring(CONVERT(varchar(30), t.plan_start_time, 120), 0, 8)='{0}' JOIN tbl_trainee tr ON tr.ref_training_id=t.id group by c.course_no, c.course_context, c.course_time order by c.course_no; "; } var sSql = string.Format(sFmt, sYearMon); return(SqlServerHelper.ExecuteQuery(conn, sSql)); } }