public static IEnumerable<Department> GetList(bool getEmpCount = false, bool userCanViewHiddenDeps = false, bool? hasAdAccount = null)
{
SqlParameter pGetEmpCount = new SqlParameter() { ParameterName = "get_emp_count", SqlValue = getEmpCount, SqlDbType = SqlDbType.Bit };
SqlParameter pHasAdAccount = new SqlParameter() { ParameterName = "has_ad_account", SqlValue = hasAdAccount, SqlDbType = SqlDbType.Bit };
var dt = Db.Stuff.ExecuteQueryStoredProcedure("get_department", pGetEmpCount, pHasAdAccount);
var lst = new List<Department>();
foreach (DataRow row in dt.Rows)
{
var dep = new Department(row);
if (dep.Hidden && !userCanViewHiddenDeps){continue;}
lst.Add(dep);
}
return lst;
}
private void FillSelf(DataRow row)
{
Id = Db.DbHelper.GetValueInt(row["id"]);
Name = row["name"].ToString();
ParentDepartment = new Department() { Id = Db.DbHelper.GetValueInt(row["id_parent"]), Name = row["parent"].ToString() };
Chief = new Employee() { Id = Db.DbHelper.GetValueInt(row["id_chief"]), DisplayName = row["chief"].ToString() };
EmployeeCount = Db.DbHelper.GetValueIntOrDefault(row["emp_count"]);
Hidden = row.Table.Columns.Contains("hidden") && Db.DbHelper.GetValueBool(row["hidden"]);
}
public static IEnumerable<Department> GetDepartmentChildList(int id, bool userCanViewHiddenDeps = false, bool hasAdAccount = true)
{
var deps = GetList(true, userCanViewHiddenDeps, hasAdAccount).ToList();
var result= new Department(id).ChildList = GetDepartmentChilds(id, ref deps, 0);
return result;
}
private void FillSelf(DataRow row)
{
Id = Db.DbHelper.GetValueInt(row["id"]);
AdSid = row.Table.Columns.Contains("ad_sid") ? row["ad_sid"].ToString() : String.Empty;
Manager = new Employee() { Id = row.Table.Columns.Contains("id_manager") ? Db.DbHelper.GetValueInt(row["id_manager"]) : 0, DisplayName = row.Table.Columns.Contains("manager") ? row["manager"].ToString() : String.Empty, Email = row.Table.Columns.Contains("manager_email") ? row["manager_email"].ToString() : String.Empty };
Surname = row.Table.Columns.Contains("surname") ? row["surname"].ToString() : String.Empty;
Name = row.Table.Columns.Contains("name") ? row["name"].ToString() : String.Empty;
Patronymic = row.Table.Columns.Contains("patronymic") ? row["patronymic"].ToString() : String.Empty;
FullName = row.Table.Columns.Contains("full_name") ? row["full_name"].ToString() : String.Empty;
DisplayName = row.Table.Columns.Contains("display_name") ? row["display_name"].ToString() : String.Empty;
Position = new Position() { Id = row.Table.Columns.Contains("id_position") ? Db.DbHelper.GetValueInt(row["id_position"]) : 0, Name = row.Table.Columns.Contains("position") ? row["position"].ToString() : String.Empty };
Organization = new Organization() { Id = row.Table.Columns.Contains("id_organization") ? Db.DbHelper.GetValueInt(row["id_organization"]) : 0, Name = row.Table.Columns.Contains("organization") ? row["organization"].ToString() : String.Empty };
Email = row.Table.Columns.Contains("email") ? row["email"].ToString() : String.Empty;
WorkNum = row.Table.Columns.Contains("work_num") ? row["work_num"].ToString() : String.Empty;
MobilNum = row.Table.Columns.Contains("mobil_num") ? row["mobil_num"].ToString() : String.Empty;
EmpState = new EmpState() { Id = row.Table.Columns.Contains("id_emp_state") ? Db.DbHelper.GetValueInt(row["id_emp_state"]) : 0, Name = row.Table.Columns.Contains("emp_state") ? row["emp_state"].ToString() : String.Empty };
Department = new Department() { Id = row.Table.Columns.Contains("id_department") ? Db.DbHelper.GetValueInt(row["id_department"]) : 0, Name = row.Table.Columns.Contains("department") ? row["department"].ToString() : String.Empty };
City = new City() { Id = row.Table.Columns.Contains("id_city") ? Db.DbHelper.GetValueInt(row["id_city"]) : 0, Name = row.Table.Columns.Contains("city") ? row["city"].ToString() : String.Empty };
DateCame = row.Table.Columns.Contains("date_came") ? Db.DbHelper.GetValueDateTimeOrNull(row["date_came"]) : new DateTime();
BirthDate = row.Table.Columns.Contains("birth_date") ? Db.DbHelper.GetValueDateTimeOrNull(row["birth_date"]) : new DateTime();
Photo = row.Table.Columns.Contains("photo") ? row["photo"] == DBNull.Value ? null : Db.DbHelper.GetByteArr(row["photo"]) : null;
IsChief = row.Table.Columns.Contains("is_chief") ? row["is_chief"].ToString().Equals("0") : false;
Male = row.Table.Columns.Contains("male") ? row["male"].ToString().Equals("1") : true;
PositionOrg = new Position() { Id = row.Table.Columns.Contains("id_position_org") ? Db.DbHelper.GetValueInt(row["id_position_org"]) : 0, Name = row.Table.Columns.Contains("position_org") ? row["position_org"].ToString() : String.Empty };
HasAdAccount = row.Table.Columns.Contains("has_ad_account") ? row["has_ad_account"].ToString().Equals("1") : false;
AdLogin = row.Table.Columns.Contains("ad_login") ? row["ad_login"].ToString() : String.Empty;
FullNameDat = row.Table.Columns.Contains("full_name_dat") ? row["full_name_dat"].ToString() : String.Empty;
FullNameRod = row.Table.Columns.Contains("full_name_rod") ? row["full_name_rod"].ToString() : String.Empty;
ShortNameDat = ShortName(FullNameDat);
ShortNameRod = ShortName(FullNameRod);
IsHidden = row.Table.Columns.Contains("is_hidden") && Db.DbHelper.GetValueBool(row["is_hidden"].ToString());
DateFired = Db.DbHelper.GetValueDateTimeOrNull(row, "date_fired");
IdBudget = Db.DbHelper.GetValueIntOrNull(row, "id_budget");
}
private static bool CheckStuffInChilds(Department dep, string sid2Find)
{
if (!dep.ChildList.Any()) return false;
bool result = false;
foreach (Department child in dep.ChildList)
{
var stuff = Employee.GetList(child.Id);
if (stuff != null && stuff.Any(s => s.AdSid == sid2Find))
{
result = true;
break;
}
else if (CheckStuffInChilds(child, sid2Find))
{
return true;
}
}
return result;
}
internal void Save(bool isRefill = false, bool isAdSync = false)
{
if (HasAdAccount && String.IsNullOrEmpty(Email)) throw new ArgumentException("Невозможно сохранить запись о сотруднике! Заполните поле Email!");
bool isEdit = (Id == 0);
if (String.IsNullOrEmpty(AdSid))
{
AdSid = "";
}
if (Creator == null) Creator = new Employee();
if (Position == null) Position = new Position();
if (Department == null) Department = new Department();
if (Organization == null) Organization = new Organization();
if (City == null) City = new City();
if (PositionOrg == null) PositionOrg = new Position();
if (Manager == null) Manager = new Employee();
if (Department.Id != 0)
{
Department = new Department(Department.Id);
if (Id != Department.Chief.Id)
{
Manager = new Employee() { Id = GetManagerId(Department.Id) };
}
else
{
var parDep = new Department(Department.Id);
if (parDep.ParentDepartment != null && parDep.ParentDepartment.Id > 0)
{
Manager = new Employee() { Id = GetManagerId(parDep.ParentDepartment.Id) };
}
}
}
if (EmpState == null) EmpState = EmpState.GetNewbieState();
Name = StringHelper.Trim(Name);
Surname = StringHelper.Trim(Surname);
Patronymic = StringHelper.Trim(Patronymic);
FullName = $"{Surname} {Name} {Patronymic}";
DisplayName =
$"{Surname} {(!String.IsNullOrEmpty(Name) ? Name[0] + "." : String.Empty)}{(!String.IsNullOrEmpty(Patronymic) ? Patronymic[0] + "." : String.Empty)}";
DisplayName = StringHelper.Trim(DisplayName);
Email = StringHelper.Trim(Email);
WorkNum = StringHelper.Trim(WorkNum);
MobilNum = StringHelper.Trim(MobilNum);
FullNameDat=StringHelper.Trim(FullNameDat);
FullNameRod = StringHelper.Trim(FullNameRod);
SqlParameter pId = new SqlParameter() { ParameterName = "id", SqlValue = Id, SqlDbType = SqlDbType.Int };
SqlParameter pAdSid = new SqlParameter() { ParameterName = "ad_sid", SqlValue = AdSid, SqlDbType = SqlDbType.VarChar };
SqlParameter pManager = new SqlParameter() { ParameterName = "id_manager", SqlValue = Manager.Id, SqlDbType = SqlDbType.Int };
SqlParameter pName = new SqlParameter() { ParameterName = "name", SqlValue = Name, SqlDbType = SqlDbType.NVarChar };
SqlParameter pSurname = new SqlParameter() { ParameterName = "surname", SqlValue = Surname, SqlDbType = SqlDbType.NVarChar };
SqlParameter pPatronymic = new SqlParameter() { ParameterName = "patronymic", SqlValue = Patronymic, SqlDbType = SqlDbType.NVarChar };
SqlParameter pFullName = new SqlParameter() { ParameterName = "full_name", SqlValue = FullName, SqlDbType = SqlDbType.NVarChar };
SqlParameter pDisplayName = new SqlParameter() { ParameterName = "display_name", SqlValue = DisplayName, SqlDbType = SqlDbType.NVarChar };
SqlParameter pPosition = new SqlParameter() { ParameterName = "id_position", SqlValue = Position.Id, SqlDbType = SqlDbType.Int };
SqlParameter pDepartment = new SqlParameter() { ParameterName = "id_department", SqlValue = Department.Id, SqlDbType = SqlDbType.Int };
SqlParameter pOrganization = new SqlParameter() { ParameterName = "id_organization", SqlValue = Organization.Id, SqlDbType = SqlDbType.Int };
SqlParameter pEmail = new SqlParameter() { ParameterName = "email", SqlValue = Email, SqlDbType = SqlDbType.NVarChar };
SqlParameter pWorkNum = new SqlParameter() { ParameterName = "work_num", SqlValue = WorkNum, SqlDbType = SqlDbType.NVarChar };
SqlParameter pMobilNum = new SqlParameter() { ParameterName = "mobil_num", SqlValue = MobilNum, SqlDbType = SqlDbType.NVarChar };
SqlParameter pEmpState = new SqlParameter() { ParameterName = "id_emp_state", SqlValue = EmpState.Id, SqlDbType = SqlDbType.Int };
SqlParameter pCity = new SqlParameter() { ParameterName = "id_city", SqlValue = City.Id, SqlDbType = SqlDbType.Int };
SqlParameter pDateCame = new SqlParameter() { ParameterName = "date_came", SqlValue = DateCame, SqlDbType = SqlDbType.Date };
SqlParameter pBirthDate = new SqlParameter() { ParameterName = "birth_date", SqlValue = BirthDate, SqlDbType = SqlDbType.Date };
SqlParameter pMale = new SqlParameter() { ParameterName = "male", SqlValue = Male, SqlDbType = SqlDbType.Bit };
SqlParameter pPositionOrg = new SqlParameter() { ParameterName = "id_position_org", SqlValue = PositionOrg.Id, SqlDbType = SqlDbType.Int };
SqlParameter pHasAdAccount = new SqlParameter() { ParameterName = "has_ad_account", SqlValue = HasAdAccount, SqlDbType = SqlDbType.Bit };
SqlParameter pCreatorAdSid = new SqlParameter() { ParameterName = "creator_sid", SqlValue = CurUserAdSid, SqlDbType = SqlDbType.VarChar };
SqlParameter pFullNameDat = new SqlParameter() { ParameterName = "full_name_dat", SqlValue = FullNameDat, SqlDbType = SqlDbType.NVarChar };
SqlParameter pFullNameRod = new SqlParameter() { ParameterName = "full_name_rod", SqlValue = FullNameRod, SqlDbType = SqlDbType.NVarChar };
SqlParameter pIdBudget = new SqlParameter() { ParameterName = "id_budget", SqlValue = IdBudget, SqlDbType = SqlDbType.Int };
using (var conn = Db.Stuff.connection)
{
conn.Open();
using (SqlTransaction tran = conn.BeginTransaction())
{
try
{
var dt = Db.Stuff.ExecuteQueryStoredProcedure("save_employee", conn, tran, pId, pAdSid, pManager, pName,
pSurname,pPatronymic, pFullName, pDisplayName, pPosition, pDepartment, pOrganization, pEmail,
pWorkNum,pMobilNum, pEmpState, pCity, pDateCame, pBirthDate, pMale, pPositionOrg, pHasAdAccount, pCreatorAdSid, pFullNameDat, pFullNameRod, pIdBudget);
if (dt.Rows.Count > 0)
{
int id;
int.TryParse(dt.Rows[0]["id"].ToString(), out id);
Id = id;
}
if (Photo != null && Photo.Count() > 0)
{
SqlParameter pIdEmployee = new SqlParameter()
{
ParameterName = "id_employee",
SqlValue = Id,
SqlDbType = SqlDbType.Int
};
SqlParameter pPicture = new SqlParameter()
{
ParameterName = "picture",
SqlValue = Photo,
SqlDbType = SqlDbType.Image
};
Db.Stuff.ExecuteQueryStoredProcedure("save_photo", conn, tran, pIdEmployee, pPicture);
}
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
throw;
}
}
conn.Close();
}
if (HasAdAccount && !isAdSync)
{
//Employee e = new Employee(Id);
bool adCreate;
try
{
string sid = AdHelper.SaveUser(this);
//if (String.IsNullOrEmpty(AdSid) || AdSid != sid)
//{
//var em = new Employee(Id);
if (String.IsNullOrEmpty(AdSid) || AdSid != sid)
{
AdSid = sid;
var emp = this;
emp.Save(isAdSync: true);
}
//}
AdSid = sid;
adCreate = true;
}
catch (Exception ex)
{
adCreate = false;
string body = String.Format("<p>Не удалось создать/обновить пользователя в Active Directory по причине:</p><p>{0}</p>", ex.Message);
MessageHelper.SendMailSmtp("Active Directory ERROR", body, true, ConfigurationManager.AppSettings["Emails4SysError"]);
//throw new Exception(String.Format("Не удалось создать пользователя в Active Directory по причине - {0}", ex.Message));
}
if (!isRefill && isEdit)
{
string body = String.Format("<p>В систему введен новый сотрудник.</p><p>{0}</p><p>{1}</p><p>{2}</p>", FullName, Email,
City.Name);
if (!adCreate) body += "<p style='color:red; font-size: 14pt;'>Не удалось создать пользователя в AD!</p>";
//var recipients = AdHelper.GetRecipientsFromAdGroup(AdGroup.NewEmployeeNote);
var recipients = AdHelper.GetRecipientsFromAdGroup(AdGroup.NewEmployeeNote);
MessageHelper.SendMailSmtp("Новый сотрудник", body, true, recipients);//Оповещение сисадмину
}
}
}
public int GetManagerId(int idDep)
{
var dep = new Department(idDep);
int id = dep.Chief.Id;
if (id == 0 && dep.ParentDepartment != null && dep.ParentDepartment.Id > 0)
{
id = GetManagerId(dep.ParentDepartment.Id);
}
return id;
}