public bool updateProjectMeeting(ProjectMeetingSummary meetingSum, Guid ProjectId, List <Guid> empGuids, List <string> empNames, List <string> acces)
{
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
DbCommand meetingCmd = dba.CreateCommand("UPDATE ProjectMeetingSummarys SET StartTime =@StartTime, Content=@Content, Result=@Result,CreateUser=@CreateUser,CreateTime=@CreateTime WHERE MeetingId = @MeetingId");
DbCommand empDelCmd = dba.CreateCommand("DELETE FROM ProjectMeetingEmployees WHERE MeetingId =@MeetingId");
DbCommand empCmd = dba.CreateCommand("INSERT INTO ProjectMeetingEmployees VALUES (@MettingEmployeeId, @EmployeeName, @MeetingId, @EmployeeId)");
DbCommand accesCmd = dba.CreateCommand("INSERT INTO MeetingAccessory VALUES (@AccessoryId, @Url, @MeetingId)");
DbCommand accesDelCmd = dba.CreateCommand("DELETE FROM MeetingAccessory WHERE MeetingId =@MeetingId ");
int num = 0;
int i = 0;
using (var tran = dba.BeginTransaction())
{
ProjectMeetingEmployee pEmp = null;
MeetingAccessory meetingAcces = null;
UpdateMeetingParameters(dba, meetingCmd, meetingSum);
num += dba.ExecuteNonQuery(meetingCmd);
dba.AddInParameter(empDelCmd, "MeetingId", meetingSum.MeetingId);
dba.ExecuteNonQuery(empDelCmd);
if (empGuids != null && empGuids.Count() > 0)
{
AddEmployeeParameters(dba, empCmd);
for (; i < empGuids.Count(); i++)
{
pEmp = new ProjectMeetingEmployee();
pEmp.MettingEmployeeId = Guid.NewGuid();
pEmp.EmployeeName = empNames[i];
updateEmployeeParameters(empCmd, pEmp, empGuids[i], meetingSum.MeetingId);
num += dba.ExecuteNonQuery(empCmd);
}
}
dba.AddInParameter(accesDelCmd, "MeetingId", meetingSum.MeetingId);
dba.ExecuteNonQuery(accesDelCmd);
if (acces != null && acces.Count() > 0)
{
AddAccessoryParameters(dba, accesCmd);
for (i = 0; i < acces.Count(); i++)
{
meetingAcces = new MeetingAccessory();
meetingAcces.AccessoryId = Guid.NewGuid();
meetingAcces.Url = acces[i];
updateAccessoryParameters(accesCmd, meetingAcces, meetingSum.MeetingId);
num += dba.ExecuteNonQuery(accesCmd);
}
}
tran.Complete();
}
return(num > 0);
}
}
public bool UpdateProject(Project project, Guid[] teamEmployees = null, Budget[] budgets = null)
{
Guard.ArgumentNotNull(project, "project");
int affectRows = 0;
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
using (var conn = dba.GetOpenConnection())
{
Project old = context.Projects
.Where(p => p.ProjectId == project.ProjectId)
.SingleOrDefault();
if (old == null)
{
throw new ModuleLogicException("正在更新的项目信息不存在，或已被删除。");
}
old.ProjectName = project.ProjectName;
old.ProjectType = project.ProjectType;
old.BeginTime = project.BeginTime;
old.EndTime = project.EndTime;
old.Status = project.Status;
old.PM = project.PM;
old.ParentId = project.ParentId;
if (teamEmployees != null && teamEmployees.Length > 0)
{
DbCommand cmd = dba.CreateCommand("DELETE FROM [ProjectTeam] WHERE ProjectId = @ProjectId");
dba.AddInParameter(cmd, "ProjectId", DbType.Guid, project.ProjectId);
affectRows += dba.ExecuteNonQuery(cmd);
affectRows += InsertProjectTeam(dba, project.ProjectId, teamEmployees);
}
if (budgets != null)
{
DbCommand cmd = dba.CreateCommand("DELETE FROM [Budget] WHERE ProjectId = @ProjectId");
dba.AddInParameter(cmd, "ProjectId", DbType.Guid, old.ProjectId);
affectRows += dba.ExecuteNonQuery(cmd);
foreach (var b in budgets)
{
b.BudgetId = Guid.NewGuid();
b.Project = old;
context.Budgets.Add(b);
}
}
affectRows += context.SaveChanges();
return(affectRows > 0);
}
}
}
public void EmployeeDictionary(out List <EmployeeSelectItem> employees, Guid?pmId = null)
{
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
EmployeeLogic elogic = new EmployeeLogic();
employees = elogic.GetEmployeesDict(pmId, dba);
}
}
public bool DeleteRequestLeaveBillById(Guid id)
{
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
int effectRows = dba.ExecuteNonQuery(
"DELETE FROM [RequestLeaveBill] WHERE Id = @p0",
new object[] { id });
return(effectRows > 0);
}
}
public void ProjectStageDictionary(out List <ProjectStageSelectItem> projectStages, Guid?pmId = null)
{
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
ProjectLogic logic = new ProjectLogic();
projectStages = logic.GetProjectStageDict(pmId, dba);
}
}
public void DepartmentDictionary(out List <DepartmentSelectItem> parentDepartments, bool getAll)
{
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
DepartmentLogic logic = new DepartmentLogic();
parentDepartments = logic.GetDepartmentsDict(dba, getAll);
}
}
public void PositionDictionary(out List <PositionSelectItem> Positions)
{
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
PositionLogic logic = new PositionLogic();
Positions = logic.GetPositionsDict(dba);
}
}
public void OrganizationDictionary(out List <OrganizationSelectItem> parentOrganizations, bool getAll)
{
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
OrganizationLogic logic = new OrganizationLogic();
parentOrganizations = logic.GetOrganizationsDict(dba, getAll);
}
}
public bool DeleteWorkTimes(Guid[] workTimeIdArray)
{
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
DbCommand cmd = dba.CreateCommand("DELETE FROM [WorkTime] WHERE WorkTimeId = @p0");
IList <object[]> parameters = QueryParameterUtility.TransferMultipleParameters <Guid>(workTimeIdArray);
int affectRows = dba.ExecuteNonQueryMultiple(cmd, parameters);
return(affectRows > 0);
}
}
public List <Object> getProjectMeetingSummarys(Guid?project, String isSelf, int pageIndex, int pageSize, out int rowCount)
{
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
DbCommand cmd = dba.CreateCommand();
StringBuilder sql = new StringBuilder();
sql.Append("SELECT pms.MeetingId, pms.ProjectId, p.ProjectName, s.StageId, s.StageName, pms.Content, pms.Result, pms.CreateUser, pms.StartTime, e.EmployeeId, e.EmployeeName, may.Url ");
sql.Append(" FROM ProjectMeetingSummarys pms");
if (isSelf.Equals("1"))
{
sql.Append(" INNER JOIN (SELECT * FROM ProjectMeetingEmployees");
Guid userId = UserContext.CurrentUser.UserId;
sql.Append(" WHERE EmployeeId = @p2 ");
dba.AddInParameter(cmd, "p2", userId);
sql.Append(" )pme ON pms.MeetingId = pme.MeetingId");
}
sql.Append(" INNER JOIN ProjectMeetingEmployees e ON pms.MeetingId = e.MeetingId ");
sql.Append(" LEFT JOIN MeetingAccessory may ON pms.MeetingId = may.MeetingId");
sql.Append(" INNER JOIN Project p ON pms.ProjectId = p.ProjectId");
sql.Append(" INNER JOIN (SELECT * FROM Stage WHERE Status = '103-01') s ON p.ProjectId = s.ProjectId");
sql.Append(" WHERE 1=1");
if (!project.Equals(Guid.Empty))
{
sql.Append(" AND pms.ProjectId = @p1 ");
dba.AddInParameter(cmd, "p1", project.Value);
}
cmd.CommandText = sql.ToString();
List <Object> data = dba.ExecuteReaderPaging <List <Object> >(
cmd, (d, r) =>
{
d.Add(new
{
MeetingId = r.GetGuidOrNull("MeetingId"),
ProjectId = r.GetGuidOrNull("ProjectId"),
ProjectName = r.GetStringOrNull("ProjectName"),
StageId = r.GetGuidOrNull("StageId"),
StageName = r.GetStringOrNull("StageName"),
Content = r.GetStringOrNull("Content"),
Result = r.GetStringOrNull("Result"),
CreateUser = r.GetStringOrNull("CreateUser"),
StartTime = r.GetDateTimeOrDefault("StartTime"),
EmployeeName = r.GetStringOrNull("EmployeeName"),
EmployeeId = r.GetGuidOrNull("EmployeeId"),
Url = r.GetStringOrNull("Url")
});
}, pageIndex, pageSize, out rowCount);
return(data);
}
}
public List <EmployeeSelectItem> GetEmployeesDict(Guid?pmId = null, DatabaseAccess dba = null)
{
bool flag = false;
TeamDBContext context = null;
if (dba == null)
{
flag = true;
context = new TeamDBContext();
dba = context.DatabaseAccess();
}
DbCommand cmd = dba.CreateCommand();
StringBuilder sqlBuilder = new StringBuilder("SELECT DISTINCT u.UserId, u.RealName FROM auth_UserExtend u");
if (pmId == null)
{
sqlBuilder.Append(" WHERE u.Position IS NOT NULL");
}
else
{
sqlBuilder.Append(" INNER JOIN ProjectTeam pt ON pt.EmployeeId = u.UserId")
.Append(" INNER JOIN(SELECT DISTINCT p.ProjectId FROM Project p INNER JOIN Stage s ON s.ProjectId = p.ProjectId")
.Append(" WHERE (p.PM = @PM OR s.Manager = @PM)) t ON pt.ProjectId = t.ProjectId");
}
cmd.CommandText = sqlBuilder.ToString();
dba.AddInParameter(cmd, "PM", DbType.Guid, pmId);
try
{
List <EmployeeSelectItem> data = dba.ExecuteReader <List <EmployeeSelectItem> >(
cmd,
(d, r) =>
{
d.Add(new EmployeeSelectItem
{
EmployeeId = Guid.Parse(r["UserId"].ToString()),
RealName = r.GetStringOrNull("RealName")
});
});
return(data);
}
catch
{
throw;
}
finally
{
if (flag)
{
context.Dispose();
}
}
}
public List <ProjectStageSelectItem> GetProjectStageDict(Guid?pmId = null, DatabaseAccess dba = null)
{
bool flag = false;
TeamDBContext context = null;
if (dba == null)
{
flag = true;
context = new TeamDBContext();
dba = context.DatabaseAccess();
}
DbCommand cmd = dba.CreateCommand();
if (pmId == null)
{
cmd.CommandText = "SELECT p.ProjectId, s.StageId, s.StageName FROM Project p INNER JOIN Stage s ON p.ProjectId = s.ProjectId ORDER BY p.ProjectName, s.SortValue";
}
else
{
cmd.CommandText = "SELECT t.ProjectId, t.StageId, t.StageName FROM (SELECT p.ProjectId, s.StageId, s.StageName, p.ProjectName, s.SortValue FROM Project p INNER JOIN Stage s ON p.ProjectId = s.ProjectId WHERE (p.PM = @PM) OR s.Manager = @PM) t ORDER BY t.ProjectName, t.SortValue";
dba.AddInParameter(cmd, "PM", DbType.Guid, pmId);
}
try
{
List <ProjectStageSelectItem> data = dba.ExecuteReader <List <ProjectStageSelectItem> >(
cmd,
(d, r) =>
{
d.Add(new ProjectStageSelectItem
{
StageId = (Guid)r["StageId"],
StageName = r.GetStringOrNull("StageName"),
ProjectId = (Guid)r["ProjectId"]
});
});
return(data);
}
catch
{
throw;
}
finally
{
if (flag)
{
context.Dispose();
}
}
}
public bool UpdateWorkTime(WorkTime workTime, ref bool isApproved)
{
Guard.ArgumentNotNull(workTime, "workTime");
Guard.ArgumentNotNullOrEmpty(workTime.JobContent, "workTime.JobContent");
Guard.ArgumentNotNull(workTime.BeginTime, "workTime.BeginTime");
Guard.ArgumentNotNull(workTime.EndTime, "workTime.EndTime");
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
using (var conn = dba.GetOpenConnection())
{
WorkTime old = context.WorkTimes
.Where(w => w.WorkTimeId == workTime.WorkTimeId)
.Single();
if (old.Approved == true)
{
isApproved = true;
return(false);
}
if (workTime.WorkTimeSettingId == null)
{
workTime.WorkTimeSettingId = old.WorkTimeSettingId;
}
else
{
old.WorkTimeSettingId = workTime.WorkTimeSettingId;
}
PrepareWorkTime(workTime, null, context);
old.EmployeeId = workTime.EmployeeId;
old.BeginTime = workTime.BeginTime;
old.EndTime = workTime.EndTime;
old.Hour = workTime.Hour;
old.ProjectId = workTime.ProjectId;
old.StageId = workTime.StageId;
old.ActivityId = workTime.ActivityId;
old.JobContent = workTime.JobContent;
old.AttendanceType = workTime.AttendanceType;
old.FaultFlag = workTime.FaultFlag;
old.FaultReason = workTime.FaultReason;
old.Approved = workTime.Approved;
old.ApprovedManager = workTime.ApprovedManager;
old.ApprovedTime = workTime.ApprovedTime;
int affectRows = context.SaveChanges();
return(affectRows > 0);
}
}
}
public List <ProjectSelectItem> GetProjectDict(Guid?pmId = null, DatabaseAccess dba = null)
{
bool flag = false;
TeamDBContext context = null;
if (dba == null)
{
flag = true;
context = new TeamDBContext();
dba = context.DatabaseAccess();
}
DbCommand cmd = dba.CreateCommand();
if (pmId != null)
{
cmd.CommandText = "SELECT p.ProjectId, p.ProjectName, p.ParentId, MAX(p.BeginTime) AS BeginTime FROM Project p INNER JOIN Stage s ON s.ProjectId = p.ProjectId WHERE p.PM = @PM OR s.Manager = @PM GROUP BY p.ParentId, p.ProjectId, p.ProjectName ORDER BY BeginTime DESC";
dba.AddInParameter(cmd, "PM", DbType.Guid, pmId);
}
else
{
cmd.CommandText = "SELECT p1.ProjectId, p1.ProjectName, p1.ParentId FROM Project p1 INNER JOIN (SELECT ParentId, COUNT(ProjectId) AS ChildrenCount FROM Project GROUP BY ParentId HAVING ParentId IS NOT NULL) t ON p1.ProjectId = t.ParentId AND t.ChildrenCount > 1 UNION ALL SELECT p2.ProjectId, p2.ProjectName, t.ParentId FROM Project p2 LEFT JOIN (SELECT ParentId, COUNT(ProjectId) AS ChildrenCount FROM Project GROUP BY ParentId HAVING ParentId IS NOT NULL) t ON p2.ParentId = t.ParentId AND t.ChildrenCount > 1 WHERE p2.ParentId IS NOT NULL";
}
try
{
List <ProjectSelectItem> data = dba.ExecuteReader <List <ProjectSelectItem> >(
cmd,
(d, r) =>
{
d.Add(new ProjectSelectItem
{
ProjectId = Guid.Parse(r["ProjectId"].ToString()),
ProjectName = r.GetStringOrNull("ProjectName"),
ParentId = r.GetGuidOrNull("ParentId")
});
});
return(data);
}
catch
{
throw;
}
finally
{
if (flag)
{
context.Dispose();
}
}
}
/// <summary>
/// 获取某一年登记工时的日期
/// </summary>
/// <param name="year"></param>
/// <returns></returns>
public List <DateTime> GetWorkDateByYear(Guid employeeId, int year)
{
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
DbCommand cmd = dba.CreateCommand("SELECT t.DateText FROM(SELECT SUBSTRING(CONVERT(varchar, BeginTime, 120), 0, 11) AS DateText FROM WorkTime WHERE YEAR(BeginTime) = @Year AND EmployeeId = @EmployeeId) t GROUP BY t.DateText");
dba.AddInParameter(cmd, "Year", DbType.Int32, (object)year);
dba.AddInParameter(cmd, "EmployeeId", DbType.Guid, employeeId);
List <DateTime> workDates = dba.ExecuteReader <List <DateTime> >(
cmd, (d, r) =>
{
d.Add(DateTime.Parse(r["DateText"].ToString()));
});
return(workDates);
}
}
public List <OrganizationSelectItem> GetOrganizationsDict(DatabaseAccess dba = null, bool getAll = true)
{
bool flag = false;
TeamDBContext context = null;
StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM Organization org");
if (!getAll)
{
sqlBuilder.Append(" WHERE org.ParentId IS null");
}
if (dba == null)
{
flag = true;
context = new TeamDBContext();
dba = context.DatabaseAccess();
}
DbCommand cmd = dba.CreateCommand();
cmd.CommandText = sqlBuilder.ToString();
try
{
List <OrganizationSelectItem> data = dba.ExecuteReader <List <OrganizationSelectItem> >(
cmd,
(d, r) =>
{
d.Add(new OrganizationSelectItem
{
OrganizationId = Guid.Parse(r["OrganizationId"].ToString()),
OrganizationName = r.GetStringOrNull("OrganizationName")
});
});
return(data);
}
catch
{
throw;
}
finally
{
if (flag)
{
context.Dispose();
}
}
}
public EmployeeModel GetEmployeeInfoById(Guid employeeId)
{
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
EmployeeModel employeeInfo = null;
DbCommand cmd = dba.CreateCommand("SELECT u.UserName, u.[Enabled], ue.*, e.*, di.DictItemValue AS StatusName FROM auth_User u INNER JOIN auth_UserExtend ue ON u.UserId = ue.UserId INNER JOIN Employee e ON ue.UserId = e.EmployeeId LEFT JOIN sys_DictionaryItem di ON ue.[Status] = di.DictItemId WHERE u.UserId = @EmployeeId");
dba.AddInParameter(cmd, "EmployeeId", DbType.Guid, employeeId);
using (DbDataReader reader = dba.ExecuteReader(cmd))
{
if (reader.Read())
{
employeeInfo = ReadEmployeeInfo <EmployeeQueryModel>(reader, false);
}
}
return(employeeInfo);
}
}
public void OrganizationDictionary(
out List <OrganizationSelectItem> organizations,
out List <DepartmentSelectItem> departments,
out List <PositionSelectItem> positions)
{
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
using (var conn = dba.GetOpenConnection())
{
OrganizationLogic oLogic = new OrganizationLogic();
organizations = oLogic.GetOrganizationsDict(dba);
DepartmentLogic dLogic = new DepartmentLogic();
departments = dLogic.GetDepartmentsDict(dba);
PositionLogic pLogic = new PositionLogic();
positions = pLogic.GetPositionsDict(dba);
}
}
}
public List <StageTemplete> FindStageTemplete(string projectType, DatabaseAccess dba = null)
{
Guard.ArgumentNotNullOrEmpty(projectType, "projectType");
bool flag = false;
TeamDBContext context = null;
if (dba == null)
{
flag = true;
context = new TeamDBContext();
dba = context.DatabaseAccess();
}
try
{
DbCommand cmd = dba.CreateCommand("SELECT * FROM [StageTemplete] WHERE ProjectType = @ProjectType ORDER BY SortValue");
dba.AddInParameter(cmd, "ProjectType", DbType.String, projectType);
List <StageTemplete> data = dba.ExecuteReader <List <StageTemplete> >(
cmd,
(d, r) =>
{
d.Add(new StageTemplete
{
StageTempleteId = (Guid)r["StageTempleteId"],
ProjectType = r["ProjectType"].ToString(),
StageName = r.GetStringOrNull("StageName"),
Description = r.GetStringOrNull("Description")
});
});
return(data);
}
catch
{
throw;
}
finally
{
if (flag)
{
context.Dispose();
}
}
}
public bool InsertWorkTime(WorkTime workTime, WorkTimeSetting workTimeSetting)
{
Guard.ArgumentNotNull(workTime, "workTime");
Guard.ArgumentNotNullOrEmpty(workTime.JobContent, "workTime.JobContent");
Guard.ArgumentNotNull(workTime.BeginTime, "workTime.BeginTime");
Guard.ArgumentNotNull(workTime.EndTime, "workTime.EndTime");
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
using (var conn = dba.GetOpenConnection())
{
workTime.WorkTimeId = Guid.NewGuid();
PrepareWorkTime(workTime, workTimeSetting, context);
context.WorkTimes.Add(workTime);
int affectRows = context.SaveChanges();
return(affectRows > 0);
}
}
}
public List <LeaveBillModel> GetLeaveBillsByIdArray(string[] idArray)
{
Guard.ArgumentNotNull(idArray, "billIdList");
if (idArray.Length > 100)
{
throw new ModuleLogicException("一次最多可以获取100个请假单");
}
List <LeaveBillModel> result = new List <LeaveBillModel>(idArray.Length);
if (idArray.Length == 0)
{
return(result);
}
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
DbCommand cmd = dba.CreateCommand("SELECT b.*, di.DictItemValue AS LeaveTypeName, ue.RealName AS EmployeeName, ue.DepartmentName FROM RequestLeaveBill b INNER JOIN sys_DictionaryItem di ON b.LeaveType = di.DictItemId INNER JOIN auth_UserExtend ue ON b.EmployeeId = ue.UserId WHERE b.Id = @BillId");
dba.AddInParameter(cmd, "BillId", DbType.Guid, null);
using (var conn = dba.GetOpenConnection())
{
foreach (string id in idArray)
{
if (!String.IsNullOrWhiteSpace(id))
{
cmd.Parameters[0].Value = Guid.Parse(id.Trim());
using (DbDataReader reader = dba.ExecuteReader(cmd))
{
if (reader.Read())
{
result.Add(ReadLeaveBill(reader));
}
}
}
}
}
}
return(result);
}
public bool DeleteHolidaySetting(HolidaySetting[] holidays)
{
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
DbCommand cmd = dba.CreateCommand("DELETE FROM HolidaySettings WHERE HolidayDate = @p1");
dba.AddInParameter(cmd, "p1", null);
var len = holidays.Count();
var val = 0;
using (var tran = dba.BeginTransaction())
{
foreach (HolidaySetting hs in holidays)
{
cmd.Parameters[0].Value = hs.HolidayDate;
val += dba.ExecuteNonQuery(cmd);
}
tran.Complete();
}
return(val > 0);
}
}
/// <summary>
/// 工时确认
/// </summary>
/// <param name="workTimeArray">工时ID array</param>
/// <param name="approved">是否确认</param>
/// <param name="approvedManager">确认人ID</param>
/// <param name="faultFlag">是否工时填报错误</param>
/// <param name="faultReason">工时填报错误原因</param>
/// <returns></returns>
public bool WorkTimeApproved(Guid[] workTimeArray, bool?approved, Guid approvedManager, bool?faultFlag, string faultReason)
{
Guard.ArgumentNotNull(workTimeArray, "workTimeArray");
if (faultFlag != null && faultFlag.Value)
{
approved = false;
if (String.IsNullOrEmpty(faultReason))
{
throw new ModuleLogicException("没有填写工时错误原因");
}
}
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
DbCommand cmd = dba.CreateCommand("UPDATE [WorkTime] SET Approved = @p1, ApprovedTime = @p2, ApprovedManager = @p3, FaultFlag = @p4, FaultReason = @p5 WHERE WorkTimeId = @p0");
IList <object[]> parameters = QueryParameterUtility.TransferMultipleParameters <Guid>(
workTimeArray, new object[] { approved, DateTime.Now, approvedManager, faultFlag, faultReason });
int affectRows = dba.ExecuteNonQueryMultiple(cmd, parameters);
return(affectRows == workTimeArray.Length);
}
}
public List <WorkTimeModel> GetWorkTime(DateTime begin, DateTime end, Guid employeeId)
{
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
DbCommand cmd = dba.CreateCommand("SELECT w.*, p.ProjectName, s.StageName, a.ActivityText, di.DictItemValue AS AttendanceTypeName, ue.RealName AS ApprovedManagerName FROM WorkTime w LEFT JOIN Project p ON w.ProjectId = p.ProjectId LEFT JOIN Stage s ON w.StageId = s.StageId LEFT JOIN Activity a ON w.ActivityId = a.ActivityId LEFT JOIN auth_UserExtend ue ON w.ApprovedManager = ue.UserId INNER JOIN sys_DictionaryItem di ON w.AttendanceType = di.DictItemId WHERE w.BeginTime >= @BeginTime AND w.EndTime <= @EndTime AND w.EmployeeId = @EmployeeId");
dba.AddInParameter(cmd, "BeginTime", DbType.DateTime, QueryParameterUtility.DateTimeStart(begin));
dba.AddInParameter(cmd, "EndTime", DbType.DateTime, QueryParameterUtility.DateTimeEnd(end));
dba.AddInParameter(cmd, "EmployeeId", DbType.Guid, employeeId);
List <WorkTimeModel> workTimes = dba.ExecuteReader <List <WorkTimeModel> >(
cmd, (d, r) =>
{
d.Add(new WorkTimeModel
{
WorkTimeId = (Guid)r["WorkTimeId"],
BeginTime = Convert.ToDateTime(r["BeginTime"]),
EndTime = Convert.ToDateTime(r["EndTime"]),
ProjectId = r.GetGuidOrNull("ProjectId"),
ProjectName = r.GetStringOrNull("ProjectName"),
Hour = r.GetDecimalOrDefault("Hours", 0),
StageId = r.GetGuidOrNull("StageId"),
StageName = r.GetStringOrNull("StageName"),
ActivityId = r.GetGuidOrNull("ActivityId"),
ActivityName = r.GetStringOrNull("ActivityText"),
JobContent = r.GetStringOrNull("JobContent"),
AttendanceType = r.GetStringOrNull("AttendanceType"),
AttendanceTypeName = r.GetStringOrNull("AttendanceTypeName"),
Approved = r.GetBooleanOrDefault("Approved", false),
ApprovedTime = r.GetDateTimeOrDefault("ApprovedTime"),
ApprovedManager = r.GetGuidOrNull("ApprovedManager"),
ApprovedManagerName = r.GetStringOrNull("ApprovedManagerName"),
FaultFlag = r.GetBooleanOrDefault("FaultFlag"),
FaultReason = r.GetStringOrNull("FaultReason"),
WorkTimeSettingId = r.GetGuidOrNull("WorkTimeSettingId")
});
});
return(workTimes);
}
}
public List <DepartmentModel> GetAllDepartments(string departmentName, string organizationName, int pageIndex, int pageSize, out int rowCount)
{
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
DbCommand cmd = dba.CreateCommand();
StringBuilder sqlBuilder = new StringBuilder("SELECT dept.*, dept2.DepartmentName AS ParentName, org.OrganizationName AS OrganizationName FROM Department dept");
sqlBuilder.Append(" INNER JOIN Organization org ON dept.OrganizationId = org.OrganizationId")
.Append(" LEFT JOIN Department dept2 on dept.ParentId = dept2.DepartmentId")
.Append(" WHERE 1 = 1");
if (!String.IsNullOrEmpty(departmentName))
{
sqlBuilder.Append(" AND dept.DepartmentName LIKE @DepartmentName");
dba.AddInParameter(cmd, "DepartmentName", DbType.String, String.Concat("%", departmentName, "%"));
}
if (!String.IsNullOrEmpty(organizationName))
{
sqlBuilder.Append(" AND org.OrganizationName LIKE @OrganizationName");
dba.AddInParameter(cmd, "OrganizationName", DbType.String, String.Concat("%", organizationName, "%"));
}
sqlBuilder.Append(" ORDER BY dept.OrganizationId, dept.ParentId, dept.DepartmentName");
cmd.CommandText = sqlBuilder.ToString();
List <DepartmentModel> data = dba.ExecuteReaderPaging <List <DepartmentModel> >(
cmd, (d, r) =>
{
d.Add(new DepartmentModel
{
DepartmentId = Guid.Parse(r["DepartmentId"].ToString()),
DepartmentName = r.GetStringOrNull("DepartmentName"),
DepartmentType = r.GetStringOrNull("DepartmentType"),
ParentId = r.GetGuidOrNull("ParentId"),
ParentName = r.GetStringOrNull("ParentName"),
OrganizationId = r.GetGuidOrNull("OrganizationId"),
OrganizationName = r.GetStringOrNull("OrganizationName")
});
}, pageIndex, pageSize, out rowCount);
return(data);
}
}
public List <PositionModel> GetAllPositions(string positionName, string departmentName, int pageIndex, int pageSize, out int rowCount)
{
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
DbCommand cmd = dba.CreateCommand();
StringBuilder sqlBuilder = new StringBuilder("SELECT p.*, d.DepartmentName, dict.DictItemValue AS PositionLevelName FROM Position p");
sqlBuilder.Append(" LEFT JOIN sys_DictionaryItem dict ON p.PositionLevel = dict.DictItemId ")
.Append(" INNER JOIN Department d on d.DepartmentId = p.DepartmentId")
.Append(" WHERE 1 = 1");
if (!String.IsNullOrEmpty(positionName))
{
sqlBuilder.Append(" AND p.PositionName LIKE @PositionName");
dba.AddInParameter(cmd, "PositionName", DbType.String, String.Concat("%", positionName, "%"));
}
if (!String.IsNullOrEmpty(departmentName))
{
sqlBuilder.Append(" AND d.DepartmentName LIKE @DepartmentName");
dba.AddInParameter(cmd, "DepartmentName", DbType.String, String.Concat("%", departmentName, "%"));
}
sqlBuilder.Append(" ORDER BY p.DepartmentId, p.PositionName");
cmd.CommandText = sqlBuilder.ToString();
List <PositionModel> data = dba.ExecuteReaderPaging <List <PositionModel> >(
cmd, (d, r) =>
{
d.Add(new PositionModel
{
PositionId = Guid.Parse(r["PositionId"].ToString()),
PositionName = r.GetStringOrNull("PositionName"),
PositionLevel = r.GetStringOrNull("PositionLevel"),
PositionLevelName = r.GetStringOrNull("PositionLevelName"),
DepartmentId = r.GetGuidOrNull("DepartmentId"),
DepartmentName = r.GetStringOrNull("DepartmentName")
});
}, pageIndex, pageSize, out rowCount);
return(data);
}
}
/// <summary>
/// 获取当前所有在职的项目经理
/// </summary>
/// <returns></returns>
public List <EmployeeSelectItem> GetProjectManager()
{
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
DbCommand cmd = dba.CreateCommand("SELECT ue.UserId, ue.RealName, ue.Organization, ue.OrganizationName, ue.Department, ue.DepartmentName, ue.Position, ue.PositionName FROM auth_Role r INNER JOIN auth_UsersInRoles ur ON r.RoleId = ur.RoleId INNER JOIN auth_UserExtend ue ON ur.UserId = ue.UserId WHERE r.RoleName = 'PM' AND ue.[Status] = '1-01' ORDER BY ue.DepartmentName, ue.RealName");
List <EmployeeSelectItem> data = dba.ExecuteReader <List <EmployeeSelectItem> >(
cmd, (d, r) => {
d.Add(new EmployeeSelectItem
{
EmployeeId = (Guid)r["UserId"],
RealName = r.GetStringOrNull("RealName"),
Organization = r.GetStringOrNull("Organization"),
OrganizationName = r.GetStringOrNull("OrganizationName"),
Department = r.GetStringOrNull("Department"),
DepartmentName = r.GetStringOrNull("DepartmentName"),
Position = r.GetStringOrNull("Position"),
PositionName = r.GetStringOrNull("PositionName")
});
});
return(data);
}
}
public List <OrganizationModel> GetAllOrganizations(string OrganizationName, int pageIndex, int pageSize, out int rowCount)
{
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
DbCommand cmd = dba.CreateCommand();
StringBuilder sqlBuilder = new StringBuilder("SELECT org.*, dict.DictItemValue AS OrganizationTypeName FROM Organization org");
sqlBuilder.Append(" INNER JOIN sys_DictionaryItem dict ON org.OrganizationType = dict.DictItemId ")
.Append(" WHERE 1 = 1");
if (!String.IsNullOrEmpty(OrganizationName))
{
sqlBuilder.Append(" AND org.OrganizationName LIKE @OrganizationName");
dba.AddInParameter(cmd, "OrganizationName", DbType.String, String.Concat("%", OrganizationName, "%"));
}
sqlBuilder.Append(" ORDER BY org.OrganizationName");
cmd.CommandText = sqlBuilder.ToString();
List <OrganizationModel> data = dba.ExecuteReaderPaging <List <OrganizationModel> >(
cmd, (d, r) =>
{
d.Add(new OrganizationModel
{
OrganizationId = Guid.Parse(r["OrganizationId"].ToString()),
OrganizationName = r.GetStringOrNull("OrganizationName"),
FullName = r.GetStringOrNull("FullName"),
Address = r.GetStringOrNull("Address"),
LinePhoneNumber = r.GetStringOrNull("LinePhoneNumber"),
LinePhoneNumber2 = r.GetStringOrNull("LinePhoneNumber2"),
CEO = r.GetStringOrNull("CEO"),
CEOId = r.GetGuidOrNull("CEOId"),
OrganizationType = r.GetStringOrNull("OrganizationType"),
OrganizationTypeName = r.GetStringOrNull("OrganizationTypeName"),
ParentId = r.GetGuidOrNull("ParentId")
});
}, pageIndex, pageSize, out rowCount);
return(data);
}
}
public List <PositionSelectItem> GetPositionsDict(DatabaseAccess dba = null)
{
bool flag = false;
TeamDBContext context = null;
if (dba == null)
{
flag = true;
context = new TeamDBContext();
dba = context.DatabaseAccess();
}
try
{
List <PositionSelectItem> data = dba.ExecuteReader <List <PositionSelectItem> >(
"SELECT * FROM [Position]",
(d, r) =>
{
d.Add(new PositionSelectItem
{
PositionId = Guid.Parse(r["PositionId"].ToString()),
PositionName = r.GetStringOrNull("PositionName")
});
});
return(data);
}
catch
{
throw;
}
finally
{
if (flag)
{
context.Dispose();
}
}
}
public Boolean DeleteTemplete(Guid?stageTempleteId)
{
bool flag = false;
if (stageTempleteId != null)
{
using (TeamDBContext context = new TeamDBContext())
{
DatabaseAccess dba = context.DatabaseAccess();
DbCommand cmd = dba.CreateCommand("DELETE FROM StageTemplete WHERE StageTempleteId=@StageTempleteId");
dba.AddInParameter(cmd, "StageTempleteId", DbType.Guid, stageTempleteId);
int EffectRow = dba.ExecuteNonQuery(cmd);
if (EffectRow > 0)
{
flag = true;
}
}
}
else
{
flag = true;
}
return(flag);
}