658 lines
38 KiB
C#
658 lines
38 KiB
C#
using System;
|
|
using Payroll.BO;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using Ease.CoreV35.Model;
|
|
using System.Data.SqlClient;
|
|
using Ease.CoreV35.DataAccess;
|
|
using System.Collections.Generic;
|
|
using Ease.CoreV35.DataAccess.SQL;
|
|
using Payroll.BO;
|
|
|
|
namespace Payroll.Service
|
|
{
|
|
partial class HREmployeeDA
|
|
{
|
|
#region parent's functions
|
|
|
|
#region Get function
|
|
|
|
public static IDataReader GetEmployees(TransactionContext tc, ID empID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM Employee where EmployeeID = %n", empID.Integer);
|
|
}
|
|
public static IDataReader GetByEmployeeNo(TransactionContext tc, string code)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM Employee where EmployeeNo = %s", code);
|
|
}
|
|
public static bool IsExists(string TableName, string ColName, string sCode, TransactionContext tc)
|
|
{
|
|
object ob = tc.ExecuteScalar("SELECT COUNT(*) FROM " + TableName + " WHERE " + ColName + "=%s", sCode);
|
|
return (Convert.ToInt32(ob) > 0);
|
|
}
|
|
public static IDataReader GetUserGroups(TransactionContext tc)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM Employee Where UserGroups IS Not NULL");
|
|
}
|
|
public static IDataReader GetEmployeeByIds(TransactionContext tc, string ids)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM Employee where EmployeeID in (%q)", ids);
|
|
}
|
|
public static string GetSubordinateIDs(TransactionContext tc, int nodeID)
|
|
{
|
|
string subordinatesIDs = string.Empty;
|
|
|
|
DataSet ds = tc.ExecuteDataSet("Select NodeID from RAHierarchyNode where parentNodeID=%n", nodeID);
|
|
|
|
foreach (DataRow row in ds.Tables[0].Rows)
|
|
{
|
|
if (subordinatesIDs.Length > 0)
|
|
subordinatesIDs = subordinatesIDs + ",";
|
|
|
|
subordinatesIDs = subordinatesIDs + row.ItemArray[0].ToString();
|
|
string str = GetSubordinateIDs(tc, (int)row.ItemArray[0]);
|
|
if (str.Length > 0)
|
|
subordinatesIDs = subordinatesIDs + ",";
|
|
subordinatesIDs = subordinatesIDs + str;
|
|
}
|
|
return subordinatesIDs;
|
|
}
|
|
public static int GetNewID(TransactionContext tc)
|
|
{
|
|
return tc.GenerateID("Employee", "EmployeeID");
|
|
}
|
|
public static int GetNewID(TransactionContext tc, string tableName, string columnName)
|
|
{
|
|
return tc.GenerateID(tableName, columnName);
|
|
}
|
|
public static bool IsExists(TransactionContext tc, string sSearch)
|
|
{
|
|
string str = SQLParser.MakeSQL("SELECT COUNT(*) FROM Employee %q", sSearch);
|
|
object obj = tc.ExecuteScalar(str);
|
|
return Convert.ToInt32(obj) > 0;
|
|
}
|
|
public static IDataReader GetEmployees(TransactionContext tc, string sQuery)
|
|
{
|
|
return tc.ExecuteReader(sQuery, "");
|
|
}
|
|
public static DataSet GetSubordinatesEmps(TransactionContext tc, string Queries)
|
|
{
|
|
DataSet ds = tc.ExecuteDataSet(Queries);
|
|
return ds;
|
|
}
|
|
internal static IDataReader GetEmpWorkPlanSetup(TransactionContext tc, ID nID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmployeeWorkPlanSetup WHERE EmployeeID=%n", nID.Integer);
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
#region update function
|
|
|
|
public static void UpdatePassword(TransactionContext tc, string password, int id)
|
|
{
|
|
tc.ExecuteNonQuery("UPDATE Employee SET DESKTOPUSERPASS = %s WHERE EMPLOYEEID=%n", password, id);
|
|
}
|
|
public static void Update(TransactionContext tc, HREmployee item)
|
|
{
|
|
string sql = SQLParser.MakeSQL(@"Update Employee SET NAME=%s,GENDER=%n,BIRTHDATE=%d,
|
|
TINNO=%s,JOININGDATE=%d,CATEGORYID=%n,PhotoPath=%s,
|
|
USERID=%n,FATHERNAME=%s,RELIGIONID=%n,MARITALSTATUSID=%n,
|
|
MotherName=%s,FatherOccupationID=%n,mobileNo=%s,
|
|
MotherOccupationID=%n,BirthPlace=%s,BloodGroup=%n,
|
|
PassportNo=%s,Photograph=%s,NationalityID=%n,ShortName=%s,
|
|
EMAILADDRESS=%s,DATEOFCONFIRMATION=%d,EMPSIGNATURE=%s,
|
|
FunctionID=%n,FirstName=%s,MiddleName=%s,LastName=%s,
|
|
CompanyID=%n,GLOBALID=%s,NationalID=%s,DrivingLicenceNo =%s,
|
|
PassportIssuePlace=%s,PassportIssueDate=%d,PassportExpDate=%d,
|
|
Role=%n,FileNo=%s
|
|
WHERE EMPLOYEEID=%n",
|
|
item.Name, item.Gender, DataReader.GetNullValue(item.BirthDate), item.TinNo, item.JoiningDate,
|
|
item.CategoryID.Integer,item.PhotoPath,item.ID.Integer, item.FatherName, item.ReligionID.Integer,
|
|
item.MaritalStatus, item.MotherName, DataReader.GetNullValue(item.FatherOccupationID.Integer),
|
|
item.MobileNo,DataReader.GetNullValue(item.MotherOccupationID.Integer),item.BirthPlace, item.BloodGroup, item.PassportNo,
|
|
item.PhotoPath, item.NationalityID.Integer, item.NickName, item.EmailAddress, DataReader.GetNullValue(item.ConfirDate),
|
|
DataReader.GetNullValue(item.Signature), DataReader.GetNullValue(item.FunctionID, IDType.Integer), item.FirstName,
|
|
item.MiddleName, item.LastName, DataReader.GetNullValue(item.CompanyID, IDType.Integer), item.GlobalID, item.NationalID,
|
|
item.DrivingLicenceNo, item.PassportIssuePlace,DataReader.GetNullValue(item.PassportIssueDate),
|
|
DataReader.GetNullValue( item.PassportExpDate),(int)item.Role,DataReader.GetNullValue(item.FileNo), item.ID.Integer);
|
|
|
|
tc.ExecuteNonQuery(sql);
|
|
}
|
|
|
|
public static void Update(TransactionContext tc, EmpContact item)
|
|
{
|
|
string sql = SQLParser.MakeSQL(@"Update EmpContact SET
|
|
EmployeeID = %n, ParmanentAddress= %s, ParmanentDistrictID= %n,
|
|
ParmanentThanaID= %n, ParmanentTelephone= %s,
|
|
PresentAddress= %s, PresentDistrictID= %n, PresentThanaID= %n,
|
|
PresentTelephone= %s, Mobile= %s, PersonalEMail= %s,
|
|
OfficialEMail= %s, Fax= %s, EmergencyContactAddress= %s,
|
|
EmergencyContactPerson= %s, EmergencyTelephone= %s,
|
|
PARMANENTMOBILE= %s, PRESENTMOBILE= %s, PERSONALTELEPHONE= %s,
|
|
EMERGENCYMOBILE= %s, CPRELATIONID= %n
|
|
WHERE ContactID=%n",
|
|
item.EmployeeID.Integer,item.ParmanentAddress, DataReader.GetNullValue(item.ParmanentDistrictID, IDType.Integer), DataReader.GetNullValue(item.ParmanentThanaID, IDType.Integer), item.ParmanentTelephone,
|
|
item.PresentAddress, DataReader.GetNullValue(item.PresentDistrictID, IDType.Integer), DataReader.GetNullValue(item.PresentThanaID, IDType.Integer), item.PresentTelephone, item.Mobile, item.PersonalEMail,
|
|
item.OfficalEMail, item.Fax, item.EmergencyContactAddress, item.EmergencyContactPerson, item.EmergencyTelephone, DataReader.GetNullValue(item.ParmanentMobile),
|
|
DataReader.GetNullValue(item.PresentMobile), DataReader.GetNullValue(item.PersonalTelephone), DataReader.GetNullValue(item.EmergencyMobile), DataReader.GetNullValue(item.ContactPersonRelationId, IDType.Integer)
|
|
,item.ID.Integer);
|
|
tc.ExecuteNonQuery(sql);
|
|
if (item.OfficalEMail != null && item.OfficalEMail != "")
|
|
{
|
|
tc.ExecuteNonQuery("UPDATE Employee SET EMAILADDRESS=%s WHERE EMPLOYEEID=%n", item.OfficalEMail, item.EmployeeID.Integer);
|
|
}
|
|
}
|
|
|
|
public static void UpdateForEmpLifeCycle(TransactionContext tc, HREmployee item,ID EmpID)
|
|
{
|
|
string sql = SQLParser.MakeSQL("Update Employee SET Role=%n, GradeID=%n,CategoryID=%n,CompanyID=%n,FunctionID=%n,DesignationID=%n,LocationID=%n,DepartmentID=%n,BasicSalary=%n,GrossSalary=%n,Status=%n,DESIGDESCRIPTION=%s WHERE EMPLOYEEID=%n", (int)item.Role,
|
|
DataReader.GetNullValue(item.GradeID, IDType.Integer), DataReader.GetNullValue(item.CategoryID, IDType.Integer),
|
|
DataReader.GetNullValue(item.CompanyID, IDType.Integer), DataReader.GetNullValue(item.FunctionID, IDType.Integer),
|
|
DataReader.GetNullValue(item.DesignationID, IDType.Integer), DataReader.GetNullValue(item.LocationID, IDType.Integer),
|
|
DataReader.GetNullValue(item.DepartmentID, IDType.Integer),item.BasicSalary,item.GrossSalary,item.Status,item.DescriptionText, EmpID.Integer);
|
|
|
|
tc.ExecuteNonQuery(sql);
|
|
}
|
|
|
|
public static void UpdateForSelfService(TransactionContext tc, HREmployee item)
|
|
{
|
|
string sql = SQLParser.MakeSQL("Update Employee SET TINNO=%s,USERID=%n,PassportNo=%s,NationalID=%s WHERE EMPLOYEEID=%n",
|
|
item.TinNo, item.ID.Integer, item.PassportNo,item.NationalID,item.ID.Integer);
|
|
|
|
tc.ExecuteNonQuery(sql);
|
|
}
|
|
public static void UpdateForSelfService(TransactionContext tc, EmpContact contact)
|
|
{
|
|
string sql = SQLParser.MakeSQL("Update EmpContact SET PresentAddress=%s, PresentDistrictID=%n, PresentThanaID=%n, PresentTelephone=%s, PresentMobile=%s,Mobile=%s, PersonalEMail=%s, OfficialEMail=%s, Fax=%s,PersonalTelephone=%s, EmergencyContactAddress=%s, EmergencyContactPerson=%s, EmergencyTelephone=%s,ParmanentAddress=%s,ParmanentDistrictID=%n,ParmanentThanaID=%n,ParmanentMobile=%s WHERE EmployeeID=%n",
|
|
contact.PresentAddress, contact.PresentDistrictID.Integer, contact.PresentThanaID.Integer, contact.PresentTelephone, contact.PresentMobile,contact.Mobile, contact.PersonalEMail, contact.OfficalEMail,contact.PersonalTelephone, contact.Fax, contact.EmergencyContactAddress, contact.EmergencyContactPerson, contact.EmergencyTelephone, contact.ParmanentAddress,contact.ParmanentDistrictID.Integer,contact.ParmanentThanaID.Integer,contact.ParmanentMobile,contact.EmployeeID.Integer);
|
|
tc.ExecuteNonQuery(sql);
|
|
|
|
sql = SQLParser.MakeSQL("Update Employee SET EMAILADDRESS=%s WHERE EmployeeID=%n", contact.OfficalEMail, contact.EmployeeID.Integer);
|
|
tc.ExecuteNonQuery(sql);
|
|
}
|
|
public static void UpdateStatus(TransactionContext tc, ID employeeID, EnumEmployeeStatus status)
|
|
{
|
|
tc.ExecuteNonQuery("UPDATE Employee SET Status= %n WHERE EMPLOYEEID=%n",
|
|
(int)status, employeeID.Integer);
|
|
}
|
|
public static void UpdateConfirm(TransactionContext tc, ID employeeID, int status)
|
|
{
|
|
tc.ExecuteNonQuery("UPDATE Employee SET isConfirmed= %n WHERE EMPLOYEEID=%n",
|
|
status, employeeID.Integer);
|
|
}
|
|
public static void UpdatePFInfo(TransactionContext tc, ID employeeID, EnumPFMembershipType eType,DateTime dt)
|
|
{
|
|
tc.ExecuteNonQuery("UPDATE Employee SET pFMemberType= %n,pfMemberShipDt=%d WHERE EMPLOYEEID=%n",
|
|
(int)eType,dt, employeeID.Integer);
|
|
}
|
|
|
|
public static void UpdatePFAndConfirmationInfo(TransactionContext tc, ID employeeID, EnumPFMembershipType eType, DateTime confirmDate)
|
|
{
|
|
tc.ExecuteNonQuery("UPDATE Employee SET pFMemberType= %n,pfMemberShipDt=%d, DATEOFCONFIRMATION=%d WHERE EMPLOYEEID=%n",
|
|
(int)eType, confirmDate, confirmDate, employeeID.Integer);
|
|
}
|
|
|
|
public static void UpdateEndofContact(TransactionContext tc, ID employeeID, DateTime dt)
|
|
{
|
|
tc.ExecuteNonQuery("UPDATE Employee SET ENDOFCONTRACTDATE= %d WHERE EMPLOYEEID=%n",
|
|
dt, employeeID.Integer);
|
|
}
|
|
public static void UpdatemiddleOfMonthDiscontinue(TransactionContext tc, ID employeeID, EnumEmployeeStatus status, DateTime endofContactDate)
|
|
{
|
|
tc.ExecuteNonQuery("UPDATE Employee SET status=%n, MonthStatusUpdate= %n, endOfContractDate=%d WHERE EMPLOYEEID=%n",
|
|
EnumEmployeeStatus.Live, (int)status, endofContactDate, employeeID.Integer);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region insert function
|
|
|
|
internal static void Insert(TransactionContext tc, HREmployee item)
|
|
{
|
|
string sSql = SQLParser.MakeSQL("INSERT INTO Employee(EmployeeID, globalID, employeeNo, name, gender, birthDate,PhotoPath, "
|
|
+ "joiningDate, endOfContractDate, emailAddress, mobileNo, tinNo, categoryID, "
|
|
+ "foreignExPat, continueGratuity, taxCircle, isConfirmed, status, isShownInTaxSheet, "
|
|
+ "pFMemberType, pfMemberShipDt, branchID, accountNo, outPayBranchID, outPayAccountNo, "
|
|
+ "departmentID, locationID, religionID, maritalStatusID, designationID, gradeID, "
|
|
+ "basicSalary, EmpHistoryID, prevBasic, paymentMode, fatherName, isEligibleOT, "
|
|
+ "desigDescription, desktopUserPass, payrollTypeID, IsAutoProcess, cardID, grossSalary, "
|
|
+ "payScaleId, taxAmount, dateOfConfirmation, CreatedBy, "
|
|
+ "CreationDate,MonthStatusUpdate, ShortName, BloodGroup, PassportNo,"
|
|
+ "NationalityID, FatherOccupationID, MotherName, MotherOccupationID,EMPSIGNATURE,FunctionID,"
|
|
+ "CompanyID,FirstName,MiddleName,LastName,BIRTHPLACE,NationalID,"
|
|
+ "DrivingLicenceNo,PassportIssuePlace,PassportIssueDate,PassportExpDate,Role,FileNo)"
|
|
+ " VALUES(%n, %s, %s, %s, %n, %d,%s, "
|
|
+ " %d, %d, %s, %s, %s, %n, "
|
|
+ " %n, %n, %s, %n, %n, %n, "
|
|
+ " %n, %d, %n, %s, %n, %s, "
|
|
+ " %n, %n, %n, %n, %n, %n, "
|
|
+ " %n, %n, %n, %n, %s, %n, "
|
|
+ " %s, %s, %n, %n, %n, %n, "
|
|
+ " %n, %n, %d,%n, "
|
|
+ " %d, %n, %s, %n, %s,"
|
|
+ " %n, %n, %s, %n,%s,%n,%n,%s,%s,%s,%s,%s,%s,%s,%d,%d,%n,%s)", item.ID.Integer, item.GlobalID, item.EmployeeNo, item.Name, item.Gender, DataReader.GetNullValue(item.BirthDate),item.PhotoPath,
|
|
DataReader.GetNullValue(item.JoiningDate), DataReader.GetNullValue(item.EndOfContractDate), item.EmailAddress, item.MobileNo, item.TinNo, DataReader.GetNullValue(item.CategoryID, IDType.Integer),
|
|
item.ForeignExPat, item.ContinueGratuity, item.TaxCircle, item.IsConfirmed, item.Status, item.IsShownInTaxSheet,
|
|
item.PFMemberType, DataReader.GetNullValue(item.PFMemberShiptDate), DataReader.GetNullValue(item.BranchID, IDType.Integer), item.AccountNo, DataReader.GetNullValue(item.OutPayBranchID, IDType.Integer), item.OutPayAccountNo,
|
|
DataReader.GetNullValue(item.DepartmentID, IDType.Integer), DataReader.GetNullValue(item.LocationID, IDType.Integer), DataReader.GetNullValue(item.ReligionID, IDType.Integer), item.MaritalStatus, DataReader.GetNullValue(item.DesignationID, IDType.Integer), DataReader.GetNullValue(item.GradeID, IDType.Integer),
|
|
item.BasicSalary, DataReader.GetNullValue(item.CurrentHistoryID,IDType.Integer), item.PrevBasic, item.PaymentMode, item.FatherName, item.IsEligibleOT,
|
|
item.DescriptionText, item.DesktopUserPass, DataReader.GetNullValue(item.PayrollTypeID, IDType.Integer), item.IsAutoProcess, DataReader.GetNullValue(item.CardID, IDType.Integer), item.GrossSalary,
|
|
DataReader.GetNullValue(item.PayScaleId, IDType.Integer), item.TaxAmount, DataReader.GetNullValue(item.ConfirDate),item.CreatedBy.Integer,
|
|
item.CreatedDate, item.MonthStatusUpdate, item.NickName, item.BloodGroup, item.PassportNo,
|
|
item.NationalityID.Integer, DataReader.GetNullValue(item.FatherOccupationID,IDType.Integer), item.MotherName, item.MotherOccupationID.Integer,DataReader.GetNullValue(item.Signature), DataReader.GetNullValue(item.FunctionID, IDType.Integer), DataReader.GetNullValue(item.CompanyID, IDType.Integer),
|
|
item.FirstName,item.MiddleName,item.LastName,item.BirthPlace,item.NationalID,item.DrivingLicenceNo,item.PassportIssuePlace,DataReader.GetNullValue(item.PassportIssueDate),DataReader.GetNullValue(item.PassportExpDate) ,(int)item.Role,item.FileNo);
|
|
tc.ExecuteNonQuery(sSql);
|
|
}
|
|
|
|
public static void Insert(TransactionContext tc, EmpContact item)
|
|
{
|
|
string sql = SQLParser.MakeSQL("INSERT INTO EmpContact(" +
|
|
" EmployeeID, ContactID, ParmanentAddress, ParmanentDistrictID, ParmanentThanaID, ParmanentTelephone,"+
|
|
" PresentAddress, PresentDistrictID, PresentThanaID, PresentTelephone, Mobile, PersonalEMail,"+
|
|
" OfficialEMail, Fax, EmergencyContactAddress, EmergencyContactPerson, EmergencyTelephone, PARMANENTMOBILE, "+
|
|
" PRESENTMOBILE, PERSONALTELEPHONE, EMERGENCYMOBILE, CPRELATIONID)" +
|
|
" VALUES("+
|
|
" %n, %n, %s, %n, %n, %s, "+
|
|
" %s, %n, %n, %s, %s, %s,"+
|
|
" %s, %s, %s, %s, %s, %s,"+
|
|
" %s, %s, %s, %n)",
|
|
item.EmployeeID.Integer, item.ID.Integer, item.ParmanentAddress, DataReader.GetNullValue(item.ParmanentDistrictID, IDType.Integer), DataReader.GetNullValue(item.ParmanentThanaID, IDType.Integer), item.ParmanentTelephone,
|
|
item.PresentAddress, DataReader.GetNullValue(item.PresentDistrictID, IDType.Integer), DataReader.GetNullValue(item.PresentThanaID, IDType.Integer), item.PresentTelephone, item.Mobile, item.PersonalEMail,
|
|
item.OfficalEMail, item.Fax, item.EmergencyContactAddress, item.EmergencyContactPerson, item.EmergencyTelephone, DataReader.GetNullValue(item.ParmanentMobile),
|
|
DataReader.GetNullValue(item.PresentMobile), DataReader.GetNullValue(item.PersonalTelephone), DataReader.GetNullValue(item.EmergencyMobile), DataReader.GetNullValue(item.ContactPersonRelationId, IDType.Integer));
|
|
tc.ExecuteNonQuery(sql);
|
|
if (item.OfficalEMail != null && item.OfficalEMail != "")
|
|
{
|
|
tc.ExecuteNonQuery("UPDATE Employee SET EMAILADDRESS=%s WHERE EMPLOYEEID=%n", item.OfficalEMail, item.EmployeeID.Integer);
|
|
}
|
|
|
|
}
|
|
|
|
public static void Insert(TransactionContext tc, EmpSpouse spouse)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO EmpSpouse("+
|
|
" EmployeeID, SpouseID, MarriageDate, Name, EducationLevelID, OccupationID,PassportNo,PassportIssuePlace,PassportIssueDate,PassportExpDate)" +
|
|
"VALUES(%n, %n, %d, %s, %n, %n,%s,%s,%d,%d)",
|
|
spouse.EmployeeID.Integer, spouse.ID.Integer, DataReader.GetNullValue(spouse.MarriageDate), spouse.Name, DataReader.GetNullValue(spouse.EducationLevelID.Integer), DataReader.GetNullValue(spouse.OccupationID.Integer),spouse.PassportNo,spouse.PassportIssuePlace,DataReader.GetNullValue(spouse.PassportIssueDate),DataReader.GetNullValue(spouse.PassportExpDate));
|
|
}
|
|
public static void Insert(TransactionContext tc, EmpChildren child)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO EmpChildren("+
|
|
" EmployeeID, ChildID, Name, Gender, BirthDate, MaritalStatus, OccupationID,PassportNo,PassportIssuePlace,PassportIssueDate,PassportExpDate)" +
|
|
"VALUES(%n, %n, %s, %n, %d, %n, %n,%s,%s,%d,%d)",
|
|
child.EmployeeID.Integer, child.ID.Integer, child.Name, child.Gender, DataReader.GetNullValue(child.BirthDate), child.MaritalStatus, DataReader.GetNullValue(child.OccupationID.Integer), child.PassportNo, child.PassportIssuePlace, DataReader.GetNullValue(child.PassportIssueDate),DataReader.GetNullValue( child.PassportExpDate));
|
|
}
|
|
public static void Insert(TransactionContext tc, EmpExperience experience)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO EmpExperience("+
|
|
" EmployeeID, ExperienceID, Employer, ContactPerson, Address, Telephone,"+
|
|
" EmployerActivity, Designation, JobDescription, FromDate, ToDate, LastJob)" +
|
|
" VALUES("+
|
|
" %n, %n, %s, %s, %s, %s,"+
|
|
" %s, %s, %s, %d, %d, %n)", experience.EmployeeID.Integer, experience.ID.Integer, experience.Employer, experience.ContactPerson, experience.Address, experience.Telephone, experience.EmployerActivity, experience.Designation, experience.JobDescription, DataReader.GetNullValue(experience.FromDate), DataReader.GetNullValue(experience.ToDate), experience.LastJob);
|
|
}
|
|
public static void Insert(TransactionContext tc, EmpTraining training)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO EmpTraining("+
|
|
" EmpTrainingID, EmployeeID, Name, Description, CountryID, InstitutionID,"+
|
|
" TrainingPlace, Achievement, Fees, OtherCost, Fromdate, ToDate,"+
|
|
" TrainingMonth, TrainingDay, TrainingHour, NatureOfTrainingID, TrainingTypeID, TrainingCompletedFrom)" +
|
|
"VALUES("+
|
|
" %n, %n, %s, %s, %n, %n,"+
|
|
" %s, %s, %n, %n, %d, %d, "+
|
|
" %n, %n, %n, %n, %n, %n)",
|
|
training.ID.Integer, training.EmployeeID.Integer, training.Name, training.Description, training.CountryID.Integer, training.InstitutionID.Integer,
|
|
training.Place, training.Achievement, training.Fees, training.OtherCost, DataReader.GetNullValue(training.FromDate), DataReader.GetNullValue(training.ToDate),
|
|
training.TrainingMonth, training.TrainingDay, training.TrainingHour, DataReader.GetNullValue(training.NatureOfTrainingID.Integer), DataReader.GetNullValue(training.TrainingTypeID.Integer), training.TrainingCompletedFrom);
|
|
}
|
|
public static void Insert(TransactionContext tc, EmpAcademic academic)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO EmpAcademic("+
|
|
" AcademicID, EmployeeID, EducationLevelID, DisciplineID, InstitutionID, PassingYear,"+
|
|
" CLASSORDIVISION, GPAOrMarks, LastLevel, OutOf, ResultTypeID, InstituteName,EducationTypeID)" +
|
|
" VALUES("+
|
|
" %n, %n, %n, %n, %n, %n,"+
|
|
" %s, %n, %n, %n, %n, %s,%n)",
|
|
academic.ID.Integer, academic.EmployeeID.Integer, academic.EducationLevelID.Integer, DataReader.GetNullValue(academic.DisciplineID.Integer), DataReader.GetNullValue(academic.InstitutionID.Integer), DataReader.GetNullValue(academic.PassingYear),
|
|
DataReader.GetNullValue(academic.ClassOrDivision) , academic.GPAOrMarks, academic.LastLevel, academic.OutOf, DataReader.GetNullValue(academic.ResultTypeID.Integer), DataReader.GetNullValue(academic.InstituteName),DataReader.GetNullValue(academic.EducationTypeID.Integer));
|
|
}
|
|
public static void Insert(TransactionContext tc, EmpAchievement item)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO EmpAchivement(EmployeeID, EmpAchivementID, AchivementID) " +
|
|
" VALUES(%n, %n, %n)", item.EmployeeID.Integer, item.ID.Integer, item.AchievementID.Integer);
|
|
}
|
|
public static void Insert(TransactionContext tc, EmpHobby item)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO EmpHobby(EmployeeID, EmpHobbyID, HobbyID)" +
|
|
" VALUES(%n, %n, %n)", item.EmployeeID.Integer, item.ID.Integer, item.HobbyID.Integer);
|
|
}
|
|
public static void Insert(TransactionContext tc, EmpOtherTalent item)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO EmpOtherTalent(EmployeeID, EmpOtherTalentID, OtherTalentID)" +
|
|
" VALUES(%n, %n, %n)", item.EmployeeID.Integer, item.ID.Integer, item.OtherTalentID.Integer);
|
|
}
|
|
public static void Insert(TransactionContext tc, EmpCurricularActivity item)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO EmpExtraCurricularActivity(EmployeeID, EmpExtraCurricularActivityID, ExtraCurricularActivityID) " +
|
|
" VALUES(%n, %n, %n)", item.EmployeeID.Integer, item.ID.Integer, item.CurricularActivityID.Integer);
|
|
}
|
|
public static void Insert(TransactionContext tc, EmpAllergy item)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO EmpAllergy(EMPALLERGYID, EMPLOYEEID, ALLERGYID)" +
|
|
" VALUES(%n, %n, %n)", item.ID.Integer, item.EmployeeID.Integer, item.AllergyID.Integer);
|
|
}
|
|
public static void Insert(TransactionContext tc, EmpPublication publication)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO EmpPUBLICATION("+
|
|
" PUBLICATIONID, EMPLOYEEID, TITLE, DESCRIPTION, REMARKS, PUBLICATIONDATE, "+
|
|
" PUBLICATIONTYPE, PUBLISHERSNAME)" +
|
|
" VALUES("+
|
|
" %n, %n, %s, %s, %s, %d,"+
|
|
" %s, %s)",
|
|
DataReader.GetNullValue(publication.ID.Integer), DataReader.GetNullValue(publication.EmployeeID.Integer), DataReader.GetNullValue(publication.Title), DataReader.GetNullValue(publication.Description), DataReader.GetNullValue(publication.Remarks), DataReader.GetNullValue(publication.PublicationDate),
|
|
publication.PublicationType, publication.PublishersName);
|
|
}
|
|
public static void Insert(TransactionContext tc, EmpReference reference)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO EmpReference("+
|
|
" EmployeeID, ReferenceID, Name, OccupationID, Address, Telephone,"+
|
|
" EmailAddress, RelationID,ReferenceMobile)" +
|
|
"VALUES("+
|
|
" %n, %n, %s, %n, %s, %s,"+
|
|
" %s, %n, %s)",
|
|
reference.EmployeeID.Integer, reference.ID.Integer, reference.Name, reference.OccupationID.Integer, reference.Address, reference.Telephone,
|
|
reference.EmailAddress, reference.RelationID.Integer, reference.ReferenceMobile);
|
|
}
|
|
public static void Insert(TransactionContext tc, EmpNominee nominee)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO EmpNominee("+
|
|
" EmployeeID, NomineeID, NominationPurposeID, NominationDate, Name, RelationID, "+
|
|
" Percentage, BirthDate, OccupationID, Address, PHOTOPATH, TelePhone,"+
|
|
" EmailAddress, SIGNATURE)" +
|
|
" VALUES("+
|
|
" %n, %n, %n, %d, %s, %n,"+
|
|
" %n, %d, %n, %s, %s, %s,"+
|
|
" %s, %s)",
|
|
nominee.EmployeeID.Integer, nominee.ID.Integer, nominee.NominationPurposeID.Integer, DataReader.GetNullValue(nominee.NominationDate), nominee.Name, nominee.RelationID.Integer,
|
|
nominee.Percentage, DataReader.GetNullValue(nominee.BirthDate), nominee.OccupationID.Integer, nominee.Address, DataReader.GetNullValue(nominee.Photograph), nominee.TelePhone,
|
|
nominee.EmailAddress, DataReader.GetNullValue(nominee.Signature));
|
|
}
|
|
public static void Insert(TransactionContext tc, EmpHospitalization item)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO EmpHospitalization("+
|
|
" HospitalizationID, EmployeeID, RegistrationDate, RegisteredPerson, RelationId, BirthDate, "+
|
|
" OccupationId, Address, Photograph, Telephone, Email,Mobile)" +
|
|
" VALUES("+
|
|
" %n, %n, %d, %s, %n, %d,"+
|
|
" %n, %s, %s, %s, %s,%s)",
|
|
item.ID.Integer, item.EmployeeID.Integer, DataReader.GetNullValue(item.RegistrationDate), item.RegisteredPerson, DataReader.GetNullValue(item.RelationId.Integer), DataReader.GetNullValue(item.BirthDate),
|
|
DataReader.GetNullValue(item.OccupationID.Integer), DataReader.GetNullValue(item.Address), DataReader.GetNullValue(item.Photograph), DataReader.GetNullValue(item.Telephone), DataReader.GetNullValue(item.Email), DataReader.GetNullValue(item.Mobile));
|
|
}
|
|
public static void Insert(TransactionContext tc, EmpGuarantor guarantor)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO EmpGuarantor("+
|
|
" GUARANTORID, EMPLOYEEID, NAME, OCCUPATIONID, ADDRESS, TELEPHONE,"+
|
|
" EMAILADDRESS, CATEGORYDOCID, DOCUMENTPATH, MOBILE)" +
|
|
" VALUES("+
|
|
" %n, %n, %s, %n, %s, %s,"+
|
|
" %s, %n, %s, %s)",
|
|
guarantor.ID.Integer, guarantor.EmployeeID.Integer, guarantor.Name, guarantor.OccupationID.Integer, guarantor.Address, guarantor.Telephone,
|
|
guarantor.EmailAddress, DataReader.GetNullValue(guarantor.CategotyDocId,IDType.Integer), guarantor.DocumentPath, guarantor.GuarantorMobile);
|
|
}
|
|
public static void Insert(TransactionContext tc, EmpLanguage language)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO EmpLanguage(" +
|
|
" EmpLanguageID, LanguageName, SpokenStatus, WrittenStatus, EmployeeID)" +
|
|
" VALUES(" +
|
|
" %n, %s, %n, %n, %n)",
|
|
language.ID.Integer,language.LanguageName,language.SpokenStatus,language.WrittenStatus,language.EmployeeID.Integer);
|
|
}
|
|
|
|
public static void Insert(TransactionContext tc, EmpRelative relative)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO EmpRelative(" +
|
|
" EmpRelativeID, Name, Designation, RelationID, JoiningDate, EndDate, EmployeeID,EmpNo)" +
|
|
" VALUES(" +
|
|
" %n, %s, %s, %n, %d, %d, %n,%s)",
|
|
relative.ID.Integer, relative.Name, relative.Designation, relative.RelationID.Integer,DataReader.GetNullValue(relative.JoiningDate),DataReader.GetNullValue(relative.EndDate), relative.EmployeeID.Integer,DataReader.GetNullValue(relative.EmpNo));
|
|
}
|
|
|
|
public static void Insert(TransactionContext tc, EmpMembership membership)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO EmpMembership(" +
|
|
" EmpMembershipID, Organization, Activity, FromDate, EndDate, EmployeeID,OrganizationType)" +
|
|
" VALUES(" +
|
|
" %n, %s, %s, %d, %d, %n,%n)",
|
|
membership.ID.Integer,membership.Organization,membership.Activity,membership.FromDate,DataReader.GetNullValue(membership.EndDate), membership.EmployeeID.Integer,membership.OrganizationType);
|
|
}
|
|
|
|
public static void Insert(TransactionContext tc, EmpRelation relation)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO EmpRelation(" +
|
|
" EmpRelationID, Name, Relation, Occupation, EmployeeID)" +
|
|
" VALUES(" +
|
|
" %n, %s, %n, %n, %n)",
|
|
relation.ID.Integer, relation.Name, relation.RelationID.Integer,relation.OccupationID.Integer,relation.EmployeeID.Integer);
|
|
}
|
|
|
|
public static void Insert(TransactionContext tc, EmpHRQuestionAnswer questionAnswer)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO EmpHRQuestionAnswer(" +
|
|
" EmpHRQuestionAnswerID, QuestionaryID, QuestionNo, QAnswer, EmployeeID,Question)" +
|
|
" VALUES(" +
|
|
" %n, %n, %s, %s, %n,%s)",
|
|
questionAnswer.ID.Integer,questionAnswer.QuestionaryID.Integer,questionAnswer.QuestionNo,questionAnswer.QAnswer, questionAnswer.EmployeeID.Integer,questionAnswer.QuestionDes);
|
|
}
|
|
|
|
internal static void Insert(TransactionContext tc, EmpWorkPlanSetup item)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO EmployeeWorkPlanSetup(EmployeeWorkPlanSetupID, EmployeeID, ShiftID, StartDate, WorkPlanGroupID, WeekEndOn, CreatedBy, CreatedDate)" +
|
|
" VALUES(%n, %n, %n, %d, %n, %n, %n, %d)", item.ID.Integer, item.EmployeeID.Integer, item.ShiftID.Integer, DataReader.GetNullValue(item.StartDate), item.WorkPlanGroupID.Integer, item.WeekEndOn, item.CreatedBy.Integer, DataReader.GetNullValue(item.CreatedDate));
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Delete function
|
|
|
|
public static void Delete(TransactionContext tc, String tableName, string columnName, ID empID)
|
|
{
|
|
string query = "Delete from " + tableName + " Where " + columnName + " = %n";
|
|
tc.ExecuteNonQuery(query, empID.Integer);
|
|
}
|
|
public static void Delete(TransactionContext tc, ID empID)
|
|
{
|
|
tc.ExecuteNonQuery("DELETE FROM Employee", empID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#endregion
|
|
|
|
#region Child's Get
|
|
#region EmpContact Get
|
|
public static IDataReader GetEmpContact(TransactionContext tc, ID empID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpContact where EmployeeID = %n", empID.Integer);
|
|
}
|
|
public static IDataReader GetEmpContact(TransactionContext tc)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpContact");
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region EmpSpouse Get
|
|
public static IDataReader GetEmpSpouse(TransactionContext tc, ID empID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpSpouse where EmployeeID = %n", empID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region EmpChildren Get
|
|
public static IDataReader GetEmpChildrens(TransactionContext tc, ID empID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpChildren where EmployeeID = %n", empID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region EmpPublications Get
|
|
public static IDataReader GetEmpPublications(TransactionContext tc, ID empID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpPublication where EmployeeID = %n", empID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region EmpReferences Get
|
|
public static IDataReader GetEmpReferences(TransactionContext tc, ID empID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpReference where EmployeeID = %n", empID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region EmpExperiences Get
|
|
public static IDataReader GetEmpExperiences(TransactionContext tc, ID empID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpExperience where EmployeeID = %n", empID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region EmpTrainings Get
|
|
public static IDataReader GetEmpTrainings(TransactionContext tc, ID empID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpTraining where EmployeeID = %n", empID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region EmpNominees Get
|
|
public static IDataReader GetEmpNominees(TransactionContext tc, ID empID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpNominee where EmployeeID = %n", empID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region EmpAcademics Get
|
|
public static IDataReader GetEmpAcademics(TransactionContext tc, ID empID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpAcademic where EmployeeID = %n", empID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region EmpAchievements Get
|
|
public static IDataReader GetEmpAchievements(TransactionContext tc, ID empID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpAchivement where EmployeeID = %n", empID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region EmpCurricularActivity Get
|
|
public static IDataReader GetEmpCurricularActivities(TransactionContext tc, ID empID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpExtraCurricularActivity where EmployeeID = %n", empID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region EmpOtherTalent Get
|
|
public static IDataReader GetEmpOtherTalents(TransactionContext tc, ID empID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpOtherTalent where EmployeeID = %n", empID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region EmpHobby Get
|
|
public static IDataReader GetEmployeeHobbys(TransactionContext tc, ID empID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpHobby where EmployeeID = %n", empID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region EmpAllergie Get
|
|
public static IDataReader GetEmpAllergies(TransactionContext tc, ID empID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpAllergy where EmployeeID = %n", empID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region EmpHospitalization Get
|
|
public static IDataReader GetEmpHospitalizations(TransactionContext tc, ID empID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpHospitalization where EmployeeID = %n", empID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region EmpGuarantors Get
|
|
public static IDataReader GetEmpGuarantors(TransactionContext tc, ID empID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpGuarantor where EmployeeID = %n", empID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region EmpLanguages Get
|
|
public static IDataReader GetEmpLanguages(TransactionContext tc, ID empID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpLanguage where EmployeeID = %n", empID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region EmpRelative Get
|
|
public static IDataReader GetEmpRelatives(TransactionContext tc, ID empID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpRelative where EmployeeID = %n", empID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region EmpMembership Get
|
|
public static IDataReader GetEmpMemberships(TransactionContext tc, ID empID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpMembership where EmployeeID = %n", empID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region EmpRelation Get
|
|
public static IDataReader GetEmpRelations(TransactionContext tc, ID empID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpRelation where EmployeeID = %n", empID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region EmpHRQuestionAnswer Get
|
|
public static IDataReader GetEmpHRQuestionAnswers(TransactionContext tc, ID empID)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpHRQuestionAnswer where EmployeeID = %n", empID.Integer);
|
|
}
|
|
#endregion
|
|
|
|
#region EmpCreditCard
|
|
internal static IDataReader GetEmpCreditCard(TransactionContext tc, ID id)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM EmpCreditCard WHERE UserTypeID=%n", id.Integer);
|
|
}
|
|
#endregion
|
|
#endregion
|
|
|
|
internal static void UpdateUserGroup(TransactionContext tc, HREmployee item)
|
|
{
|
|
|
|
}
|
|
|
|
internal static IDataReader GetEmployee(TransactionContext tc, string employeeNo)
|
|
{
|
|
return tc.ExecuteReader("SELECT * FROM Employee WHERE EmployeeNo=%s AND PAYROLLTYPEID = %n", employeeNo, SystemInformation.CurrentSysInfo.PayrollTypeID.Integer);
|
|
}
|
|
|
|
|
|
|
|
internal static void Insert(TransactionContext tc, EmpCreditCard item)
|
|
{
|
|
tc.ExecuteNonQuery("INSERT INTO EmpCreditCard(" +
|
|
" EmpCreditCardID, EmployeeID, UserType, UserTypeID, CardNo, CardName,BankID,CardLimit,"+
|
|
" CardExpiryDate,TIN)" +
|
|
" VALUES(" +
|
|
" %n,%n,%n,%n, %s, %s,%n,%n, %d,%s)",
|
|
item.ID.Integer,item.EmployeeID.Integer,(int)item.UserType,item.UserTypeID.Integer, item.CardNo,
|
|
DataReader.GetNullValue(item.CardName), item.BankID.Integer,item.CardLimit, item.CardExpiryDate,
|
|
DataReader.GetNullValue(item.TIN));
|
|
}
|
|
}
|
|
}
|