public DataTable InsertPerson(string userRole, string roleData, string userLogin, IPersonEntity person)
{
//ConnectDB("ConnectionString");
//string queryString = "Insert into Person([person_type_ref], [title], [firstname], [lastname], [emailprimary], [emailalternate], [fax], [phoneprimary], [phonehome], [phonemobile], [phonework], [phoneemergency], [companyname], [position], [description]) values ('" +
// person.PersonType + "','" +
// person.PersonTitle + "','" +
// person.PersonFirstName + "','" +
// person.PersonLastName + "','" +
// person.PersonEmailPrimary + "','" +
// person.PersonEmailAlternate + "','" +
// person.PersonFax + "','" +
// person.PersonPhonePrimary + "','" +
// person.PersonPhoneHome + "','" +
// person.PersonPhoneMobile + "','" +
// person.PersonPhoneWork + "','" +
// person.PersonPhoneEmergency + "','" +
// person.PersonCompanyName + "','" +
// person.PersonPosition + "','" +
// person.PersonDescription + "')";
//SqlCommand sqlcmd = new SqlCommand(queryString, Connection);
//sqlcmd.Connection.Open();
//sqlcmd.ExecuteNonQuery();
//sqlcmd.Connection.Close();
DB obj = new DB();
String storedProcName = "[usp_InsertPerson]";
//List of parameter required
SqlParameter[] param = {
new SqlParameter("@UserRole", userRole),
new SqlParameter("@RoleData", roleData),
new SqlParameter("@UserLogin", userLogin),
new SqlParameter("@PersonType", person.PersonType),
new SqlParameter("@PersonTitle", person.PersonTitle),
new SqlParameter("@PersonFirstName", person.PersonFirstName),
new SqlParameter("@PersonLastName", person.PersonLastName),
new SqlParameter("@PersonEmailPrimary", person.PersonEmailPrimary),
new SqlParameter("@PersonEmailAlternate", person.PersonEmailAlternate),
new SqlParameter("@PersonFax", person.PersonFax),
new SqlParameter("@PersonPhonePrimary", person.PersonPhonePrimary),
new SqlParameter("@PersonPhoneHome", person.PersonPhoneHome),
new SqlParameter("@PersonPhoneMobile", person.PersonPhoneMobile),
new SqlParameter("@PersonPhoneWork", person.PersonPhoneWork),
new SqlParameter("@PersonPhoneEmergency", person.PersonPhoneEmergency),
new SqlParameter("@PersonCompanyName", person.PersonCompanyName),
new SqlParameter("@PersonPosition", person.PersonPosition),
new SqlParameter("@PersonDescription", person.PersonDescription),
new SqlParameter("@IsActive", person.PersonIsActive)
};
DataTable dt = obj.getSPRecords("ConnectionString", storedProcName, param);
return dt;
}
public DataTable getSPUser(IUserEntity user)
{
DB obj = new DB();
string storedProcName = "[usp_ValidateUser]";
//List of parameter required
SqlParameter[] param = {
new SqlParameter("@Username", user.UserName),
new SqlParameter("@Password", user.Password)
};
DataTable dt = obj.getSPRecords("SecurityConnectionString", storedProcName, param);
return dt;
}
public DataTable ListSkill(string userRole, string roleData, string userLogin)
{
DB obj = new DB();
String storedProcName = "[usp_GetSelectedSkill]";
//List of parameter required
SqlParameter[] param = {
new SqlParameter("@UserRole", userRole),
new SqlParameter("@RoleData", roleData),
new SqlParameter("@UserLogin", userLogin)
};
DataTable dt = obj.getSPRecords("ConnectionString", storedProcName, param);
return dt;
}
public DataTable DeactivateUser(string userRole, string roleData, string userLogin, IUserEntity user)
{
DB obj = new DB();
String storedProcName = "[usp_DeactivateUser]";
//List of parameter required
SqlParameter[] param = {
new SqlParameter("@UserRole", userRole),
new SqlParameter("@RoleData", roleData),
new SqlParameter("@UserLogin", userLogin),
new SqlParameter("@Name", user.UserName)
};
DataTable dt = obj.getSPRecords("SecurityConnectionString", storedProcName, param);
return dt;
}
public DataTable InsertContractorSkill(string userRole, string roleData, string userLogin, IContractorSkillEntity contractorskill)
{
DB obj = new DB();
String storedProcName = "[usp_InsertContractorSkill]";
//List of parameter required
SqlParameter[] param = {
new SqlParameter("@UserRole", userRole),
new SqlParameter("@RoleData", roleData),
new SqlParameter("@UserLogin", userLogin),
new SqlParameter("@ContractorId", contractorskill.ContractorID),
new SqlParameter("@SkillId", contractorskill.SkillID)
};
DataTable dt = obj.getSPRecords("ConnectionString", storedProcName, param);
return dt;
}
public DataTable InsertSkill(string userRole, string roleData, string userLogin, ISkillEntity skill)
{
DB obj = new DB();
String storedProcName = "[usp_InsertSkill]";
//List of parameter required
SqlParameter[] param = {
new SqlParameter("@UserRole", userRole),
new SqlParameter("@RoleData", roleData),
new SqlParameter("@UserLogin", userLogin),
new SqlParameter("@SkillType", skill.SkillType),
new SqlParameter("@IsActive", skill.IsActive)
};
DataTable dt = obj.getSPRecords("ConnectionString", storedProcName, param);
return dt;
}
public DataTable ApprovePerson(string userRole, string roleData, string userLogin, IPersonEntity person)
{
DB obj = new DB();
String storedProcName = "[usp_ApprovePerson]";
//List of parameter required
SqlParameter[] param = {
new SqlParameter("@UserRole", userRole),
new SqlParameter("@RoleData", roleData),
new SqlParameter("@UserLogin", userLogin),
new SqlParameter("@PersonID", person.PersonID),
new SqlParameter("@IsActive", person.PersonIsActive)
};
DataTable dt = obj.getSPRecords("ConnectionString", storedProcName, param);
return dt;
}
public DataTable getUser(IUserEntity user)
{
DB obj = new DB();
string queryString = "SELECT r.RoleName, u.* " +
"FROM Users u, UsersToRoles s, Roles r " +
"WHERE u.UserID = s.FKUserID " +
"AND s.FKRoleID = r.RoleID " +
"AND " +
"Name='"
+ user.UserName +
"' and password='******'and IsApproved ='"
+ true +
"'and IsLockedOut ='"
+ false +
"'";
return obj.getRecords(queryString, "SecurityConnectionString");
}
public DataTable InsertUser(string userRole, string roleData, string userLogin, IUserEntity user)
{
DB obj = new DB();
String storedProcName = "[usp_InsertUser]";
//List of parameter required
SqlParameter[] param = {
new SqlParameter("@UserRole", userRole),
new SqlParameter("@RoleData", roleData),
new SqlParameter("@UserLogin", userLogin),
new SqlParameter("@UserName", user.UserName),
new SqlParameter("@Password", user.Password),
new SqlParameter("@Email", user.Email),
new SqlParameter("@IsApproved", user.IsApproved),
new SqlParameter("@IsLockedOut", user.IsLockedOut)
};
DataTable dt = obj.getSPRecords("SecurityConnectionString", storedProcName, param);
return dt;
}
public DataTable UpdatePerson(string userRole, string roleData, string userLogin, IPersonEntity person)
{
DB obj = new DB();
String storedProcName = "[usp_UpdatePerson]";
//List of parameter required
SqlParameter[] param = {
new SqlParameter("@UserRole", userRole),
new SqlParameter("@RoleData", roleData),
new SqlParameter("@UserLogin", userLogin),
new SqlParameter("@PersonID", person.PersonID),
//new SqlParameter("@PersonType", person.PersonType),
//new SqlParameter("@PersonTitle", person.PersonTitle),
new SqlParameter("@PersonFirstName", person.PersonFirstName),
new SqlParameter("@PersonLastName", person.PersonLastName),
//new SqlParameter("@PersonEmailPrimary", person.PersonEmailPrimary),
//new SqlParameter("@PersonEmailAlternate", person.PersonEmailAlternate),
//new SqlParameter("@PersonFax", person.PersonFax),
new SqlParameter("@PersonPhonePrimary", person.PersonPhonePrimary),
//new SqlParameter("@PersonPhoneHome", person.PersonPhoneHome),
new SqlParameter("@PersonPhoneMobile", person.PersonPhoneMobile),
//new SqlParameter("@PersonPhoneWork", person.PersonPhoneWork),
//new SqlParameter("@PersonPhoneEmergency", person.PersonPhoneEmergency),
//new SqlParameter("@PersonCompanyName", person.PersonCompanyName),
//new SqlParameter("@PersonPosition", person.PersonPosition),
//new SqlParameter("@PersonDescription", person.PersonDescription),
//new SqlParameter("@IsActive", person.PersonIsActive)
};
DataTable dt = obj.getSPRecords("ConnectionString", storedProcName, param);
return dt;
}
public DataTable ListPerson(string userRole, string roleData, string userLogin)
{
DB obj = new DB();
//string queryString = "SELECT title, firstname, lastname, emailprimary, emailalternate, fax, phoneprimary, phonehome, phonemobile, phonework, phoneemergency, companyname, position, description FROM person where person_type_ref = 1";
//return obj.getRecords(queryString, "ConnectionString");
String storedProcName = "[usp_GetSelectedPerson]";
//List of parameter required
SqlParameter[] param = {
new SqlParameter("@UserRole", userRole),
new SqlParameter("@RoleData", roleData),
new SqlParameter("@UserLogin", userLogin)
};
DataTable dt = obj.getSPRecords("ConnectionString", storedProcName, param);
return dt;
}