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