public EmployeeSearchSalaryOutput getSalaryDetailEmployee(string employeeId, string salaryMonth)
{
EmployeeSearchSalaryOutput employeeSearchSalaryOutput = new EmployeeSearchSalaryOutput();
string salaryId = String.Format("{0}{1}", employeeId, salaryMonth.Substring(0, 7).Replace("/", ""));
using (sys_employeeEntities db = new sys_employeeEntities())
{
var query = (from s in db.salary
where s.salaryId.Equals(salaryId)
select new
{
s.salaryId,
s.employeeId,
s.paymentDate,
s.salaryDetailFilePath,
s.totalPaidAmount,
s.deductionAmount,
s.paidAmount
}).FirstOrDefault();
if (query == null)
{
return(null);
}
employeeSearchSalaryOutput.SalaryId = query.salaryId;
employeeSearchSalaryOutput.EmployeeId = query.employeeId;
employeeSearchSalaryOutput.PaymentDate = query.paymentDate;
employeeSearchSalaryOutput.SalaryDetailFilePath = query.salaryDetailFilePath;
employeeSearchSalaryOutput.TotalPaidAmount = query.totalPaidAmount == null ? "" : query.totalPaidAmount.ToString();
employeeSearchSalaryOutput.DeductionAmount = query.deductionAmount == null ? "" : query.deductionAmount.ToString();
employeeSearchSalaryOutput.PaidAmount = query.paidAmount == null ? "" : query.paidAmount.ToString();
return(employeeSearchSalaryOutput);
}
}
public static Dictionary <string, string> getPersonalDocuments(string employeeId)
{
Dictionary <string, string> documents = new Dictionary <string, string>();
var directoryPath = HttpContext.Current.Server.MapPath("~/Content/EmployeeInformation/" + "/" + employeeId + "/" + employeeId);
using (sys_employeeEntities db = new sys_employeeEntities())
{
var query = (from e in db.employee
where e.employeeId == employeeId
select e).First();
if (!Directory.Exists(directoryPath))
{
Directory.CreateDirectory(directoryPath);
query.personalDocumentPath = directoryPath.Substring(directoryPath.IndexOf("Content\\EmployeeInformation\\"));
}
documents.Add("name", query.name);
db.SaveChanges();
}
string[] files = Directory.GetFiles(directoryPath);
foreach (string file in files)
{
FileInfo f = new FileInfo(file);
string pathLink = f.FullName.Substring(f.FullName.IndexOf("\\Content\\EmployeeInformation\\"));
documents.Add(f.Name, pathLink);
}
return(documents);
}
public EmployeeLoginOutput Login(EmployeeLoginInput employeeLoginInput)
{
EmployeeLoginOutput employeeLoginOutput = new EmployeeLoginOutput();
try
{
string passWord = HelperCommon.hashPassword(employeeLoginInput.PassWord);
using (sys_employeeEntities db = new sys_employeeEntities())
{
var query = (from e in db.employee
join c in db.customer
on e.customerId equals c.customerId into ecGroup
from ec in ecGroup.DefaultIfEmpty()
where e.userName.Equals(employeeLoginInput.UserName) && e.passWord.Equals(passWord)
select new
{
e.employeeId,
e.name,
e.kataName,
e.mailAddress,
e.telephoneNumber,
e.entryDate,
customerName = ec.name,
e.address,
e.accountBankInfo,
e.personalNumber,
e.dateOfBirth,
e.authorityId,
e.avatarFilePath
}).FirstOrDefault();
if (query == null)
{
throw new Exception("ユーザネームまたパスワードが違います");
}
employeeLoginOutput.Id = query.employeeId;
employeeLoginOutput.Name = query.name;
employeeLoginOutput.KataName = query.kataName;
employeeLoginOutput.Email = query.mailAddress;
employeeLoginOutput.TelephoneNumber = query.telephoneNumber;
employeeLoginOutput.EntryDate = query.entryDate;
employeeLoginOutput.CustomerName = query.customerName;
employeeLoginOutput.Address = query.address;
employeeLoginOutput.AccountBankInfo = query.accountBankInfo;
employeeLoginOutput.PersonalNunber = query.personalNumber;
employeeLoginOutput.DateOfBirth = query.dateOfBirth.GetValueOrDefault().ToString("yyyy/MM/dd");
employeeLoginOutput.AuthorityId = query.authorityId;
employeeLoginOutput.AvatarFilePath = query.avatarFilePath;
}
}
catch (Exception e)
{
throw e;
}
return(employeeLoginOutput);
}
//編集社員取得
public EmployeeUpdateInput getEmployeeUpdate(string employeeId)
{
EmployeeUpdateInput employeeUpdate = new EmployeeUpdateInput();
using (sys_employeeEntities db = new sys_employeeEntities())
{
var query = (from e in db.employee where e.employeeId == employeeId
select new
{
e.employeeId,
e.name,
e.kataName,
e.authorityId,
e.telephoneNumber,
e.dateOfBirth,
e.address,
e.mailAddress,
e.customerId,
e.managerId,
e.personalNumber,
e.accountBankInfo,
e.entryDate,
e.leavingDate,
e.depentdentFamily,
e.userName,
e.passWord,
e.avatarFilePath,
e.description
}).Single();
if (query == null)
{
return(null);
}
employeeUpdate.Id = query.employeeId;
employeeUpdate.Name = query.name;
employeeUpdate.KataName = query.kataName;
employeeUpdate.AuthorityId = query.authorityId;
employeeUpdate.TelephoneNumber = query.telephoneNumber;
employeeUpdate.DateOfBirth = query.dateOfBirth.GetValueOrDefault().ToString("yyyy/MM/dd");
employeeUpdate.Address = query.address;
employeeUpdate.MailAddress = query.mailAddress;
employeeUpdate.CustomerId = query.customerId;
employeeUpdate.ManagerId = query.managerId;
employeeUpdate.PersonalNumber = query.personalNumber;
employeeUpdate.AccountBankInfo = query.accountBankInfo;
employeeUpdate.EntryDate = query.entryDate;
employeeUpdate.LeavingDate = query.leavingDate;
employeeUpdate.DepentdentFamily = query.depentdentFamily.GetValueOrDefault();
employeeUpdate.UserName = query.userName;
employeeUpdate.PassWord = query.passWord;
employeeUpdate.Description = query.description;
employeeUpdate.AvatarFilePath = query.avatarFilePath;
}
return(employeeUpdate);
}
public static string getEmployeeUsername(string employeeId, sys_employeeEntities db)
{
var query = (from e in db.employee
where e.employeeId.Equals(employeeId)
select new
{
e.userName
}).FirstOrDefault();
string employeeUsername = query.userName;
return(employeeUsername);
}
public static bool isDuplicateUsername(string username, sys_employeeEntities db)
{
var query = (from e in db.employee
where e.userName.Equals(username)
select e).FirstOrDefault();
if (query == null)
{
return(false);
}
return(true);
}
// 社員追加
public void addEmployee(EmployeeAddInput employee)
{
try
{
using (sys_employeeEntities db = new sys_employeeEntities())
{
if (DataBaseCommon.isDuplicateEmployee(employee.Id, db))
{
throw new Exception("社員Idが重複発生です");
}
if (DataBaseCommon.isDuplicateUsername(employee.UserName, db))
{
throw new Exception("社員Usernameが重複発生です");
}
var path = "";
var avatarFile = employee.AvatarFile;
if (avatarFile != null && avatarFile.ContentLength > 0)
{
path = HelperCommon.saveAvatarFile(avatarFile, employee.Id);
}
employee employeeEntity = new employee();
employeeEntity.employeeId = employee.Id;
employeeEntity.managerId = employee.ManagerId;
employeeEntity.userName = employee.UserName;
employeeEntity.passWord = HelperCommon.hashPassword(employee.PassWord);
employeeEntity.authorityId = employee.AuthorityId;
employeeEntity.dateOfBirth = DateTime.Parse(employee.DateOfBirth);
employeeEntity.address = employee.Address;
employeeEntity.personalNumber = employee.PersonalNumber;
employeeEntity.name = employee.Name;
employeeEntity.kataName = employee.KataName;
employeeEntity.telephoneNumber = employee.TelephoneNumber;
employeeEntity.mailAddress = employee.MailAddress;
employeeEntity.customerId = employee.CustomerId;
employeeEntity.accountBankInfo = employee.AccountBankInfo;
employeeEntity.avatarFilePath = path;
employeeEntity.depentdentFamily = employee.DepentdentFamily;
employeeEntity.entryDate = employee.EntryDate;
employeeEntity.description = employee.Description;
db.employee.Add(employeeEntity);
db.SaveChanges();
}
} catch (Exception e)
{
throw e;
}
}
public static bool isDuplicateEmployee(string employeeId, sys_employeeEntities db)
{
var query = (from e in db.employee
where e.employeeId.Equals(employeeId)
select new
{
e.employeeId
}).FirstOrDefault();
if (query == null)
{
return(false);
}
return(true);
}
public static bool addPersonalDocument(HttpPostedFileBase postFile, string employeeId)
{
using (sys_employeeEntities db = new sys_employeeEntities())
{
var query = (from e in db.employee
where e.employeeId == employeeId
select e).First();
var directoryPath = Path.Combine(HttpContext.Current.Server.MapPath("~/"), query.personalDocumentPath);
var fullFilePath = String.Format("{0}\\{1}", directoryPath, postFile.FileName);
postFile.SaveAs(fullFilePath);
}
return(true);
}
public static bool isEmployeeExist(string id, string name, sys_employeeEntities db)
{
var query = (from e in db.employee
where e.employeeId == id && e.name == name
select new
{
e.employeeId
}).Count();
if (query == 0)
{
return(false);
}
return(true);
}
public string uploadSalaryDetailFile(string salaryMonth, HttpPostedFileBase salaryDetailFileStream)
{
try
{
string salaryFileName = "";
using (sys_employeeEntities db = new sys_employeeEntities())
{
FileStream salaryFileStream = HelperCommon.saveSalaryDetail(salaryMonth, salaryDetailFileStream);
salaryFileName = updateSalary(salaryMonth, salaryFileStream, db);
}
return(salaryFileName);
} catch (Exception e)
{
throw e;
}
}
public static bool isSalaryExist(string salaryId, sys_employeeEntities db)
{
var query = (from s in db.salary
where s.salaryId == salaryId
select new
{
s.salaryId
}
).Count();
if (query == 0)
{
return(false);
}
return(true);
}
public void ChangePassword(string employeeId, string password, string newPassword)
{
string hashPassword = HelperCommon.hashPassword(password);
using (sys_employeeEntities db = new sys_employeeEntities())
{
var query = (from e in db.employee
where e.employeeId.Equals(employeeId) && e.passWord.Equals(hashPassword)
select e).FirstOrDefault();
if (query == null)
{
throw new Exception("パスワードが間違いで、再入力ください");
}
query.passWord = HelperCommon.hashPassword(newPassword);
db.SaveChanges();
}
}
//社員リスト取得
public List <EmployeeSearchOutput> getEmployees()
{
List <EmployeeSearchOutput> employees = new List <EmployeeSearchOutput>();
using (sys_employeeEntities db = new sys_employeeEntities())
{
var query = (from e in db.employee
join c in db.customer
on e.customerId equals c.customerId into ecGroup
from ec in ecGroup.DefaultIfEmpty()
join a in db.authority
on e.authorityId equals a.authorityId into ecaGroup
from eca in ecaGroup.DefaultIfEmpty()
where e.leavingDate == null
select new
{
e.employeeId,
e.name,
e.kataName,
e.telephoneNumber,
e.mailAddress,
customerName = ec.name,
entryDate = e.entryDate,
authority = eca.authorityName,
e.description
})
.ToList();
foreach (var item in query)
{
EmployeeSearchOutput employee = new EmployeeSearchOutput();
employee.Id = item.employeeId;
employee.Name = item.name;
employee.KataName = item.kataName;
employee.TelephoneNumber = item.telephoneNumber;
employee.MailAddress = item.mailAddress;
employee.CustomerName = item.customerName;
employee.EntryDate = item.entryDate;
employee.Authority = item.authority;
employee.Description = item.description;
employees.Add(employee);
}
}
return(employees);
}
public bool uploadSalaryDetailZipFiles(string salaryMonth, HttpPostedFileBase salaryDetailFolderStream)
{
string salaryFolderName = salaryMonth.Substring(0, 7).Replace("/", "");
MemoryStream ms = new MemoryStream();
salaryDetailFolderStream.InputStream.CopyTo(ms);
string salaryDetailFolderPath = Path.Combine(ConstantCommon.SALARYFOLDERROOTPATH, salaryFolderName);
List <FileStream> salaryDetailFileStreams = HelperCommon.extractZipfileToFileStream(ms, salaryDetailFolderPath);
using (sys_employeeEntities db = new sys_employeeEntities())
{
foreach (FileStream salaryDetailFileStream in salaryDetailFileStreams)
{
updateSalary(salaryMonth, salaryDetailFileStream, db);
}
}
return(false);
}
public static Dictionary <string, string> getAuthority()
{
Dictionary <string, string> authoritys = new Dictionary <string, string>();
using (sys_employeeEntities db = new sys_employeeEntities())
{
var query = (from a in db.authority
select new
{
a.authorityId,
a.authorityName
}).ToList();
foreach (var item in query)
{
authoritys.Add(item.authorityName, item.authorityId);
}
}
return(authoritys);
}
public static Dictionary <string, string> getCustomer()
{
Dictionary <string, string> customers = new Dictionary <string, string>();
customers.Add("", "");
using (sys_employeeEntities db = new sys_employeeEntities())
{
var query = (from c in db.customer
select new
{
c.customerId,
c.name
}).ToList();
foreach (var item in query)
{
customers.Add(item.name, item.customerId);
}
}
return(customers);
}
public static Dictionary <string, string> getManager()
{
Dictionary <string, string> managers = new Dictionary <string, string>();
managers.Add("", "");
using (sys_employeeEntities db = new sys_employeeEntities())
{
var query = (from e in db.employee
where e.authorityId.Equals(ConstantCommon.MANAGER)
select new
{
e.employeeId,
e.name
}).ToList();
foreach (var item in query)
{
managers.Add(item.name, item.employeeId);
}
}
return(managers);
}
public List <SalaryView> getSalaryByMonth(string paymentDate)
{
List <SalaryView> salarys = new List <SalaryView>();
using (sys_employeeEntities db = new sys_employeeEntities())
{
var query = (from e in db.employee
join s in db.salary
on e.employeeId equals s.employeeId into esGroup
from es in esGroup.DefaultIfEmpty()
where es.paymentDate.Substring(0, 7).Equals(paymentDate.Substring(0, 7))
select new
{
es.employeeId,
e.name,
es.totalPaidAmount,
es.deductionAmount,
es.paidAmount,
es.salaryDetailFilePath,
es.description
}).ToList();
foreach (var item in query)
{
SalaryView salary = new SalaryView();
salary.EmployeeId = item.employeeId;
salary.Name = item.name;
salary.TotalPaidAmount = item.totalPaidAmount.ToString();
salary.DeductionAmount = item.deductionAmount.ToString();
salary.PaidAmount = item.paidAmount.ToString();
salary.SalaryDetailFilePath = item.salaryDetailFilePath;
salary.FileName = item.salaryDetailFilePath.Substring(item.salaryDetailFilePath.LastIndexOf("\\") + 1);
salary.Description = item.description;
salarys.Add(salary);
}
}
return(salarys);
}
// 社員情報編集
public EmployeeUpdateInput updateEmployee(EmployeeUpdateInput employeeUpdateInput)
{
try
{
using (sys_employeeEntities db = new sys_employeeEntities())
{
var path = "";
var avatarFile = employeeUpdateInput.AvatarFile;
if (avatarFile != null && avatarFile.ContentLength > 0)
{
path = HelperCommon.saveAvatarFile(employeeUpdateInput.AvatarFile, employeeUpdateInput.Id);
}
var query = (from e in db.employee
where e.employeeId == employeeUpdateInput.Id
select e).First();
if (!query.userName.Equals(employeeUpdateInput.UserName))
{
if (DataBaseCommon.isDuplicateUsername(employeeUpdateInput.UserName, db))
{
throw new Exception("社員Usernameが存在です");
}
}
employee employeeUpdate = (employee)query;
employeeUpdate.employeeId = employeeUpdateInput.Id;
employeeUpdate.name = employeeUpdateInput.Name;
employeeUpdate.kataName = employeeUpdateInput.KataName;
employeeUpdate.authorityId = employeeUpdateInput.AuthorityId;
employeeUpdate.telephoneNumber = employeeUpdateInput.TelephoneNumber;
employeeUpdate.dateOfBirth = DateTime.Parse(employeeUpdateInput.DateOfBirth);
employeeUpdate.address = employeeUpdateInput.Address;
employeeUpdate.mailAddress = employeeUpdateInput.MailAddress;
employeeUpdate.customerId = employeeUpdateInput.CustomerId;
employeeUpdate.managerId = employeeUpdateInput.ManagerId;
employeeUpdate.personalNumber = employeeUpdateInput.PersonalNumber;
employeeUpdate.accountBankInfo = employeeUpdateInput.AccountBankInfo;
employeeUpdate.entryDate = employeeUpdateInput.EntryDate;
employeeUpdate.leavingDate = employeeUpdateInput.LeavingDate;
employeeUpdate.depentdentFamily = employeeUpdateInput.DepentdentFamily;
employeeUpdate.userName = employeeUpdateInput.UserName;
if (!employeeUpdate.passWord.Equals(employeeUpdateInput.PassWord))
{
employeeUpdate.passWord = HelperCommon.hashPassword(employeeUpdateInput.PassWord);
}
employeeUpdate.description = employeeUpdateInput.Description;
if (!path.Equals(""))
{
employeeUpdate.avatarFilePath = path;
}
db.SaveChanges();
}
}
catch (Exception e)
{
throw e;
}
return(employeeUpdateInput);
}
public string updateSalary(string salaryMonth, FileStream salaryDetailFileStream, sys_employeeEntities db)
{
//FileStream salaryFileStream = HelperCommon.saveSalaryDetail(salaryMonth, salaryDetailFileStream);
string salaryFilePath = salaryDetailFileStream.Name;
FileInfo salaryFileInfo = new FileInfo(salaryFilePath);
string salaryFileName = Path.GetFileNameWithoutExtension(salaryDetailFileStream.Name);
string employeeId = String.Format("{0}{1}", "vis", salaryFileName.Substring(0, 3));
string employeeName = salaryFileName.Substring(4);
if (!salaryFileInfo.Extension.Equals(".pdf") || !DataBaseCommon.isEmployeeExist(employeeId, employeeName, db))
{
salaryDetailFileStream.Flush();
salaryDetailFileStream.Close();
File.Delete(salaryFileName);
throw new Exception("PDFファイルではないまた社員存在しません");
}
salaryDetailFileStream.Flush();
salaryDetailFileStream.Close();
string salaryId = string.Format("{0}{1}", employeeId, salaryMonth.Substring(0, 7).Replace("/", ""));
string salaryDetailFilePath = salaryFilePath.Substring(salaryFilePath.IndexOf(ConstantCommon.SALARYFOLDERPATH));
if (DataBaseCommon.isSalaryExist(salaryId, db))
{
updateSalaryDetailFilePath(salaryId, salaryDetailFilePath, db);
}
else
{
addSalaryDetailFilePath(employeeId, salaryMonth, salaryDetailFilePath, db);
}
return(salaryFileInfo.Name);
}
private bool addSalaryDetailFilePath(string employeeId, string salaryMonth, string salaryDetailFilePath, sys_employeeEntities db)
{
string salaryId = string.Format("{0}{1}", employeeId, salaryMonth.Substring(0, 7).Replace("/", ""));
salary salary = new salary();
salary.salaryId = salaryId;
salary.employeeId = employeeId;
salary.paymentDate = string.Format("{0}/{1}", salaryMonth.Substring(0, 7), "15");
salary.salaryDetailFilePath = salaryDetailFilePath;//fileName.Substring(fileName.IndexOf(ConstantCommon.SALARYFOLDERPATH));
salary.description = "";
db.salary.Add(salary);
db.SaveChanges();
return(false);
}
private bool updateSalaryDetailFilePath(string salaryId, string salaryDetailFilePath, sys_employeeEntities db)
{
var query = (from s in db.salary
where s.salaryId == salaryId
select s).First();
salary salary = (salary)query;
salary.salaryDetailFilePath = salaryDetailFilePath;
db.SaveChanges();
return(false);
}
// 名前またIDで社員検索
public List <EmployeeSearchOutput> searchEmployees(EmployeeSearchInput employeeSearchInput)
{
List <EmployeeSearchOutput> employees = new List <EmployeeSearchOutput>();
using (sys_employeeEntities db = new sys_employeeEntities())
{
var query = (from e in db.employee
join c in db.customer
on e.customerId equals c.customerId into ecGroup
from ec in ecGroup.DefaultIfEmpty()
join a in db.authority
on e.authorityId equals a.authorityId into ecaGroup
from eca in ecaGroup.DefaultIfEmpty()
select new
{
e.employeeId,
e.name,
e.kataName,
e.telephoneNumber,
e.mailAddress,
customerName = ec.name,
entryDate = e.entryDate,
authority = eca.authorityName,
eca.authorityId,
e.leavingDate,
e.customerId,
e.description
});
if (!string.IsNullOrWhiteSpace(employeeSearchInput.Id))
{
query = query.Where(p => p.employeeId.Equals(employeeSearchInput.Id));
}
if (!string.IsNullOrWhiteSpace(employeeSearchInput.Name))
{
query = query.Where(p => p.name.Contains(employeeSearchInput.Name));
}
switch (employeeSearchInput.WorkSituation)
{
case ConstantCommon.ZAISHA:
query = query.Where(p => p.leavingDate == null);
break;
case ConstantCommon.TAISHA:
query = query.Where(p => p.leavingDate != null);
break;
default:
break;
}
if (!string.IsNullOrWhiteSpace(employeeSearchInput.CustomerId))
{
query = query.Where(p => p.customerId.Equals(employeeSearchInput.CustomerId));
}
if (!string.IsNullOrWhiteSpace(employeeSearchInput.AuthorityId))
{
query = query.Where(p => p.authorityId.Equals(employeeSearchInput.AuthorityId));
}
query.ToList();
foreach (var item in query)
{
EmployeeSearchOutput employee = new EmployeeSearchOutput();
employee.Id = item.employeeId;
employee.Name = item.name;
employee.KataName = item.kataName;
employee.TelephoneNumber = item.telephoneNumber;
employee.MailAddress = item.mailAddress;
employee.CustomerName = item.customerName;
employee.EntryDate = item.entryDate;
employee.Authority = item.authority;
employee.Description = item.description;
employees.Add(employee);
}
}
return(employees);
}
