public Unit GetUnitByUIC(string uic)
{
secdivDataContext db = new secdivDataContext();
var unit = db.Units.Where(u => u.UIC.Equals(uic));
if (unit != null)
return unit.First();
return null;
}
//Search the Mached Unit By Unit Identification Code
public bool ExistUnitMached(string uic)
{
bool isMached = false;
secdivDataContext db = new secdivDataContext();
var units = db.Units.Where(u => u.UIC.Equals(uic));
if (units.Count() > 0)
isMached = true;
return isMached;
}
//By the Office Name
public List<OFFICE> GetOfficesByName(string name)
{
secdivDataContext db = new secdivDataContext();
var offices = db.OFFICEs.Where(o => o.OfficeName.StartsWith(name));
if (offices.Count() > 0)
{
return offices.ToList();
}
return null;
}
//By the Unit Identification Code(UIC)
public List<OFFICE> GetOfficesByUIC(string uic)
{
secdivDataContext db = new secdivDataContext();
var offices = db.OFFICEs.Where(o => o.UIC.Equals(uic));
if (offices.Count() > 0)
{
return offices.ToList();
}
return null;
}
//By Unit Identification Code
public List<Unit> GetUnitsByUIC(string uic)
{
secdivDataContext db = new secdivDataContext();
var units = db.Units.Where(u => u.UIC.StartsWith(uic));
if (units.Count() > 0)
{
return units.ToList();
}
return null;
}
/// <summary>
/// Scripter : YONGTOK KIM
/// Scripted Date : 3 Feb 2012
/// Get the unit datas
/// </summary>
//By Installation Code
public List<Unit> GetUnitsByInstallation(string ic)
{
secdivDataContext db = new secdivDataContext();
var units = db.Units.Where(u => u.INSTALLATIONCODE.Equals(ic));
if (units.Count() > 0)
{
return units.ToList();
}
return null;
}
/// <summary>
/// Scripter : YONGTOK KIM
/// Scripted Date : 3 Feb 2012
/// Get the office data
/// </summary>
//By the Unit Name
public List<OFFICE> GetOfficesByUnitName(string unitName)
{
secdivDataContext db = new secdivDataContext();
Unit unit = db.Units.Where(u => u.UnitName.StartsWith(unitName)).First();
var offices = db.OFFICEs.Where(o => o.UIC.Equals(unit.UIC));
if (offices.Count() > 0)
{
return offices.ToList();
}
return null;
}
public List<OFFICE> GetOfficesByOfficeCode(string code)
{
officeSearchResult = false;
secdivDataContext db = new secdivDataContext();
var offices = db.OFFICEs.Where(o => o.OFFICECODE.Equals(code));
if (offices.Count() > 0)
{
officeSearchResult = true;
return offices.ToList();
}
return null;
}
//By Unit Name
public List<Unit> GetUnitsByName(string name)
{
//string unitName = string.Format("%{0}%", name);
secdivDataContext db = new secdivDataContext();
//List<Unit> unit = db.GetUnitByUnitName(unitName);
var units = db.Units.Where(u => u.UnitName.StartsWith(name));
if (units.Count() > 0)
{
return units.ToList();
}
return null;
}
//Get all of the units
public List<Unit> GetUnitsAll()
{
secdivDataContext db = new secdivDataContext();
var units = from u in db.Units
orderby u.UnitName ascending
select u;
if (units.Count() > 0)
{
return units.ToList();
}
return null;
}
//By the Installation Code
public List<OFFICE> GetOfficesByInstallationCode(string ic)
{
secdivDataContext db = new secdivDataContext();
var offices = from o in db.OFFICEs
join u in db.Units
on o.UIC equals u.UIC
where u.INSTALLATIONCODE == ic
select o;
if (offices.Count() > 0)
{
return offices.ToList();
}
return null;
}
public LoginInfo GetLoginInfo(string id, byte[] passwd)
{
secdivDataContext db = new secdivDataContext();
var users = db.UserAccounts.Where(ua => ua.UserName.Equals(id) && ua.Password.Equals(passwd));
if (users.Count() > 0)
{
UserAccount user = users.First();
var userinfo = db.Persons.Where(p => p.PID.Equals(user.PID));
if (userinfo.Count() > 0)
{
LoginInfo li;
Person person = userinfo.First();
if (String.IsNullOrEmpty(person.MiddleName))
{
li = new LoginInfo
{
UserName = user.UserName,
Name = string.Format("{0}, {1} {2}", person.LastName, person.FirstName, person.MiddleName),
UnitName = person.OFFICE.Unit.UnitName,
TypeOfUser = user.TypeOfUser,
UAStatus = user.UAStatus
};
}
else
{
li = new LoginInfo
{
UserName = user.UserName,
Name = string.Format("{0}, {1}", person.LastName, person.FirstName),
UnitName = person.OFFICE.Unit.UnitName,
TypeOfUser = user.TypeOfUser,
UAStatus = user.UAStatus
};
}
return li;
}
}
return null;
}
public bool UpdatePerson(Trainee trainee)
{
// update person
try
{
secdivDataContext db = new secdivDataContext();
var persons = db.Persons.Where(p => p.PID.Equals(trainee.PID));
if (persons.Count() > 0)
{
Person person = persons.First();
person.LastName = trainee.LastName;
person.FirstName = trainee.FirstName;
person.MiddleName = trainee.MiddleName;
person.TypeOfPID = trainee.TypeOfPID;
person.EMail = trainee.Email;
person.JobTitle = trainee.JobTitle;
person.OFFICECODE = trainee.OfficeCode;
person.HomePhone = trainee.HomePhone;
person.MobilePhone = trainee.MobilePhone;
person.Gender = trainee.Gender;
person.DateOfBirth = trainee.DateOfBirth;
person.PersonStatus = "Current";
person.OfficePhone = trainee.OfficePhone;
person.Fax = trainee.Fax;
person.Photo = trainee.Photo;
person.RankID = trainee.RankID;
db.SubmitChanges();
return true;
}
else
return false;
}
catch
{
return false;
}
}
public bool UpdatePersonDetail(Trainee trainee)
{
// update person details
try
{
secdivDataContext db = new secdivDataContext();
var personDetails = db.PersonDetails.Where(pd => pd.PID.Equals(trainee.PID));
if (personDetails.Count() > 0)
{
PersonDetail detailPerson = personDetails.First();
detailPerson.CountryCode = trainee.Nationality;
db.SubmitChanges();
return true;
}
else
return false;
}
catch
{
return false;
}
}
public bool UpdateUserAccount(Trainee trainee)
{
// update user account
try
{
string userName = string.Format("{0}{1}{2}", trainee.LastName, trainee.FirstName.Substring(0, 1), trainee.PID.Substring(trainee.PID.Length - 4, 4));
string password = string.Format("{0}{1}", trainee.LastName, trainee.PID.Substring(trainee.PID.Length - 4, 4));
byte[] passwd = DataHash.GetHashData(password, "SHA1");
secdivDataContext db = new secdivDataContext();
var userAccounts = db.UserAccounts.Where(u => u.PID.Equals(trainee.PID));
if (userAccounts.Count() > 0)
{
UserAccount userAccount = userAccounts.First();
if (userAccount.UserName == userName)
{
userAccount.Password = passwd;
userAccount.PID = trainee.PID;
userAccount.TypeOfUser = trainee.OperatorType;
userAccount.UAStatus = "Current";
db.SubmitChanges();
return true;
}
else
{
UserAccount ua = new UserAccount
{
UserName = userName,
Password = passwd,
PID = trainee.PID,
TypeOfUser = trainee.OperatorType,
UAStatus = "Current"
};
db.UserAccounts.InsertOnSubmit(ua);
db.SubmitChanges();
return true;
}
}
else
return false;
}
catch
{
return false;
}
}
public bool UpdateDbidsCertificate(Trainee trainee)
{
try
{
secdivDataContext db = new secdivDataContext();
var dbidsCertificates = db.DbidsCertificates.Where(d => d.OperatorPID.Equals(trainee.PID));
string remarks = string.Format("{0}. - Update thru trainee registration program", trainee.PersonRemarks);
if (dbidsCertificates.Count() > 0)
{
DbidsCertificate dbidsCertificate = dbidsCertificates.First();
dbidsCertificate.CertificateStatus = "Current";
dbidsCertificate.CertificateRemarks = remarks;
dbidsCertificate.CertificateUpdatedDate = trainee.LastTrainedDate;
dbidsCertificate.CertificateExpiredDate = trainee.LastTrainedDate.AddYears(1);
db.SubmitChanges();
return true;
}
else
return false;
}
catch
{
return false;
}
}
//All of the Offices
public List<OFFICE> GetAllOfOffices()
{
secdivDataContext db = new secdivDataContext();
var offices = from o in db.OFFICEs
orderby o.OfficeName ascending
select o;
if (offices.Count() > 0)
{
return offices.ToList();
}
return null;
}
public bool UpdateDbidsOperator(Trainee trainee)
{
// update dbids operator
try
{
secdivDataContext db = new secdivDataContext();
var dbidsOperators = db.DbidsOperators.Where(d => d.PID.Equals(trainee.PID));
if (dbidsOperators.Count() > 0)
{
DbidsOperator dbidsOperator = dbidsOperators.First();
dbidsOperator.OperatorType = trainee.OperatorType;
dbidsOperator.LastTrainedDate = trainee.LastTrainedDate;
db.SubmitChanges();
return true;
}
else
return false;
}
catch
{
return false;
}
}
// Check whether db connect
private void DbConnectCheck()
{
StringBuilder errorMessages = new StringBuilder();
try
{
if (!dbLogin)
{
// Check user privilege
secdivDataContext db = new secdivDataContext();
if (db.DatabaseExists() == true)
{
LogIn login = new LogIn(db.DatabaseExists());
// Toggle upload and office button
if (login.ShowDialog() == true)
dbLogin = true;
}
else
MessageBox.Show("Database couldn't find!!");
}
}
catch (SqlException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
errorMessages.Append("Index #" + i + "\n" +
"Message: " + ex.Errors[i].Message + "\n" +
"LineNumber: " + ex.Errors[i].LineNumber + "\n" +
"Source: " + ex.Errors[i].Source + "\n" +
"Procedure: " + ex.Errors[i].Procedure + "\n");
}
MessageBox.Show(errorMessages.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}