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; namespace Payroll.Service { #region EmployeeDA internal class EmployeeDA { #region Constructor private EmployeeDA() { } #endregion #region Insert function internal static void Insert(TransactionContext tc, Employee 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,VendorCode, CreatedBy,Role,OutPayPaymentMode, CreationDate,MonthStatusUpdate,PersonType) VALUES(%n, %s, %s, %s, %n, %d,%s, %d, %d, %s, %s, %s, %n, %n, %n, %s, %b, %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, %s, %n,%n,%n, %d,%n,%n)", 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), DataReader.GetNullValue(item.VendorCode), item.CreatedBy.Integer, item.Role, (int)item.OutPayPaymentMode, DataReader.GetNullValue(item.CreatedDate), item.MonthStatusUpdate,(int) item.PersonType); tc.ExecuteNonQuery(sSql); } #endregion #region Update function internal static void Update(TransactionContext tc, Employee item) { string sSQL = SQLParser.MakeSQL("UPDATE Employee SET " + "globalID=%s, employeeNo=%s, name=%s, gender=%n, birthDate=%d, joiningDate=%d,PhotoPath=%s, " + "endOfContractDate=%d, emailAddress=%s, mobileNo=%s, tinNo=%s, categoryID=%n, foreignExPat=%n, " + "continueGratuity=%n, taxCircle=%s, isConfirmed=%b, status=%n, isShownInTaxSheet=%n, pFMemberType=%n, " + "pfMemberShipDt=%d, branchID=%n, accountNo=%s, OUTPAYBRANCHID=%n, outPayAccountNo=%s, departmentID=%n, " + "locationID=%n, religionID=%n, MARITALSTATUSID=%n, designationID=%n, gradeID=%n, basicSalary=%n, " + "EmpHistoryID=%n, prevBasic=%n, paymentMode=%n, fatherName=%s, isEligibleOT=%n, desigDescription=%s, " + "DESKTOPUSERPASS=%s, payrollTypeID=%n, isAutoProcess=%n, cardID=%n, grossSalary=%n," + "payScaleId=%n, taxAmount=%n, dateOfConfirmation=%d,VendorCode=%s, ModifiedBy=%n, ModifiedDate=%d, " + "MonthStatusUpdate=%n,Role=%n,OutPayPaymentMode=%n ,PersonType=%n" + " WHERE EmployeeID=%n", item.GlobalID, item.EmployeeNo, DataReader.GetNullValue(item.Name), item.Gender, DataReader.GetNullValue(item.BirthDate), DataReader.GetNullValue(item.JoiningDate), item.PhotoPath, 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, 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, DataReader.GetNullValue(item.DesktopUserPass), item.PayrollTypeID.Integer, item.IsAutoProcess, DataReader.GetNullValue(item.CardID, IDType.Integer), item.GrossSalary, DataReader.GetNullValue(item.PayScaleId, IDType.Integer), item.TaxAmount, DataReader.GetNullValue(item.ConfirDate), DataReader.GetNullValue(item.VendorCode), item.ModifiedBy.Integer, item.ModifiedDate, item.MonthStatusUpdate, item.Role, (int)item.OutPayPaymentMode,(int) item.PersonType, item.ID.Integer); tc.ExecuteNonQuery(sSQL); } internal static void UpdateShortInfo(TransactionContext tc, Employee item) { string sSQL = ""; if (item.EmailAddress!="") { sSQL = SQLParser.MakeSQL("UPDATE Employee SET " + "emailAddress=%s WHERE EmployeeNo=%s", item.EmailAddress, item.EmployeeNo); tc.ExecuteNonQuery(sSQL); } if (item.JoiningDate != null && item.JoiningDate > DateTime.MinValue) { sSQL = SQLParser.MakeSQL("UPDATE Employee SET " + "joiningDate=%d WHERE EmployeeNo=%s", DataReader.GetNullValue(item.JoiningDate), item.EmployeeNo); tc.ExecuteNonQuery(sSQL); } if (item.ConfirDate != null && item.ConfirDate > DateTime.MinValue) { sSQL = SQLParser.MakeSQL("UPDATE Employee SET " + " ISCONFIRMED=1,dateOfConfirmation=%d WHERE EmployeeNo=%s", DataReader.GetNullValue(item.ConfirDate), item.EmployeeNo); tc.ExecuteNonQuery(sSQL); } } internal static void UpdateGross(TransactionContext tc, ID EmployeeID, double? dGross) { if (dGross == null) dGross = 0; tc.ExecuteNonQuery("UPDATE Employee SET grossSalary=%n " + " WHERE EmployeeID=%n", dGross, EmployeeID.Integer); } internal static void UpdateGradeSalary(TransactionContext tc, int employeeid, int gradeId, double basicSalary, double grossSalary) { tc.ExecuteNonQuery("UPDATE Employee SET gradeid=%n, BasicSalary=%n, GrossSalary=%n " + " WHERE EmployeeID=%n", DataReader.GetNullValue(gradeId), basicSalary, grossSalary, employeeid); } internal static void UpdateSalary(TransactionContext tc, int employeeid, double basicSalary, double grossSalary) { tc.ExecuteNonQuery("UPDATE Employee SET BasicSalary=%n, GrossSalary=%n " + " WHERE EmployeeID=%n", basicSalary, grossSalary, employeeid); } public static void UpdateStatus(TransactionContext tc, ID employeeID, ID employeeHistoryId, DateTime endofContactDate, EnumEmployeeStatus status) { tc.ExecuteNonQuery("UPDATE Employee SET Status= %n, EmpHistoryID=%n, endOfContractDate=%d WHERE EMPLOYEEID=%n", (int)status, employeeHistoryId.Integer, endofContactDate, 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); } public static void UpdateEmpWithheldStatus(TransactionContext tc, ID employeeID, int status) { tc.ExecuteNonQuery("Update Employee SET MonthStatusUpdate=%n WHERE EMPLOYEEID=%n", status, employeeID.Integer); } //for employee continue public static void DoContinue(TransactionContext tc, ID employeeID) { tc.ExecuteNonQuery("UPDATE Employee SET Status= %n, MonthStatusUpdate= %n, EmpHistoryID=null, endOfContractDate=null WHERE EMPLOYEEID=%n", (int)EnumEmployeeStatus.Live, (int)EnumEmployeeStatus.Live, employeeID.Integer); } public static void DoContinueForLifeCycle(TransactionContext tc, ID employeeID, DateTime dtEffectDate) { tc.ExecuteNonQuery("UPDATE Employee SET Status= %n, MonthStatusUpdate= %n,joiningdate=%d, EmpHistoryID=null, endOfContractDate=null WHERE EMPLOYEEID=%n", (int)EnumEmployeeStatus.Live, (int)EnumEmployeeStatus.Live, dtEffectDate, employeeID.Integer); } public static void DoContinueForLifeCycleForOldEmployee(TransactionContext tc, ID employeeID) { tc.ExecuteNonQuery("UPDATE Employee SET Status= %n, MonthStatusUpdate= %n, EmpHistoryID=null, endOfContractDate=null WHERE EMPLOYEEID=%n", (int)EnumEmployeeStatus.Live, (int)EnumEmployeeStatus.Live, employeeID.Integer); } //added by Hasib 04.05.10 internal static void UpdateCardInformation(TransactionContext tc, ID employeeID, ID CardID, bool IsAutoProcess) { tc.ExecuteNonQuery("UPDATE Employee SET CardID= %n, IsAutoProcess=%b WHERE EMPLOYEEID=%n", DataReader.GetNullValue(CardID,IDType.Integer) , IsAutoProcess, employeeID.Integer); } //For PF internal static void UpdatePFMemship(TransactionContext tc, ID empID, EnumPFMembershipType PFType, DateTime dPFEffectDate) { tc.ExecuteNonQuery("UPDATE Employee SET PFMEMBERTYPE= %n, PFMEMBERSHIPDT=%d WHERE EMPLOYEEID=%n", PFType, dPFEffectDate, empID.Integer); } internal static void UpdateGratuityContinuation(TransactionContext tc, ID empID, bool GratuityContinue, DateTime dPFEffectDate) { tc.ExecuteNonQuery("UPDATE Employee SET CONTINUEGRATUITY= %b WHERE EMPLOYEEID=%n", GratuityContinue, empID.Integer); } // For Confirm Employee //internal static void UpdateConfirmation(TransactionContext tc, ID empID, EnumEmployeeOtherStatus confirmation, DateTime dConfirmEffectDate,bool confirmEmpPF) //{ // tc.ExecuteNonQuery("UPDATE Employee SET pFMemberType=%b,pfMemberShipDt=%d,IsConfirmed= %b,DateOfConfirmation=%d WHERE EMPLOYEEID=%n",confirmEmpPF,dConfirmEffectDate,confirmation,dConfirmEffectDate); //} // For Update Employee Posting internal static void UpdateEmpPosting(TransactionContext tc, EmployeePosting oEmpPosting) { tc.ExecuteNonQuery("UPDATE Employee SET DEPARTMENTID= %n,LocationID=%n,DESIGNATIONID=%n WHERE EMPLOYEEID=%n", DataReader.GetNullValue(oEmpPosting.DepartmentID, IDType.Integer), DataReader.GetNullValue(oEmpPosting.LocationID, IDType.Integer), DataReader.GetNullValue(oEmpPosting.DesignationID, IDType.Integer), oEmpPosting.EmployeeID.Integer); } // For Tax Amount public static void UpdateTaxAmount(TransactionContext tc, ID employeeID, double TaxAmount) { tc.ExecuteNonQuery("UPDATE Employee SET TAXAMOUNT= %n WHERE EMPLOYEEID=%n", TaxAmount, employeeID.Integer); } // For Employee Role public static void UpdateEmpRole(TransactionContext tc, Employee oEmp) { tc.ExecuteNonQuery("UPDATE Employee SET Role= %n WHERE EMPLOYEEID=%n", oEmp.Role, oEmp.ID.Integer); } // For Employee Confirm public static void DoConfirm(TransactionContext tc, ID employeeID, DateTime confirmDate, DateTime pfMShipDate, bool IsConfirm, bool IsPFMember) { tc.ExecuteNonQuery("UPDATE Employee SET ISCONFIRMED= %b,DateOfConfirmation=%d,PFMEMBERTYPE=%b,PFMEMBERSHIPDT=%d WHERE EMPLOYEEID=%n", IsConfirm, confirmDate, IsPFMember, pfMShipDate, employeeID.Integer); } public static void UpdateOTFlag(TransactionContext tc, int nEmployeeID) { tc.ExecuteNonQuery("UPDATE Employee SET isEligibleOT= %b where EmployeeID=%n", true, nEmployeeID); } public static void UpdateOTFlag(TransactionContext tc, string sIDs) { tc.ExecuteNonQuery("UPDATE Employee SET isEligibleOT= %b where GradeID in(%q)", true, sIDs); //Updated by hassan 20 Jan 2016 // considering all grades of OTSetupDetail //tc.ExecuteNonQuery("UPDATE Employee SET isEligibleOT= %b where GradeID not in(%q)", false, sIDs); tc.ExecuteNonQuery("UPDATE Employee SET isEligibleOT= %b where GradeID not in(SELECT DISTINCT TranID FROM OTSetupDetail)", false); } public static void DoConfirm(TransactionContext tc, ID employeeID, DateTime confirmDate, bool IsConfirm) { tc.ExecuteNonQuery("UPDATE Employee SET ISCONFIRMED= %b,DateOfConfirmation=%d WHERE EMPLOYEEID=%n", IsConfirm, confirmDate, employeeID.Integer); } //For Undo Employee public static void UndoConfirm(TransactionContext tc, ID employeeID, bool IsConfirm) { tc.ExecuteNonQuery("UPDATE Employee SET ISCONFIRMED= %b,DateOfConfirmation=null WHERE EMPLOYEEID=%n", IsConfirm, employeeID.Integer); } //For Transfer Employee public static void UpdatePayrollType(TransactionContext tc, ID employeeID, ID payrollTypeID, DateTime dEffectDate) { tc.ExecuteNonQuery("UPDATE Employee SET PAYROLLTYPEID=%n,JoiningDate=%d,categoryID=%n WHERE EMPLOYEEID=%n", payrollTypeID.Integer, dEffectDate, payrollTypeID.Integer, employeeID.Integer); tc.ExecuteNonQuery("DELETE FROM IncomeTaxTemp WHERE EMPLOYEEID=%n", employeeID.Integer); } // For Employee Bank Accoutn public static void UpdateBankAcc(TransactionContext tc, ID employeeID, ID branchID, string accountNo, EnumPaymentMode paymentMode) { tc.ExecuteNonQuery("UPDATE Employee SET paymentMode=%n, branchID=%n, accountNo=%s WHERE EMPLOYEEID=%n", paymentMode, DataReader.GetNullValue(branchID, IDType.Integer), accountNo, employeeID.Integer); } public static void UpdateOPIBankAcc(TransactionContext tc, ID employeeID, ID branchID, string accountNo, EnumPaymentMode paymentMode) { tc.ExecuteNonQuery("UPDATE Employee SET OutPayPaymentMode=%n, OutPayBranchID=%n, OutPayAccountNo=%s WHERE EMPLOYEEID=%n", paymentMode, DataReader.GetNullValue(branchID, IDType.Integer), accountNo, employeeID.Integer); } #endregion #region Get Function internal static IDataReader GetForSuperUser(TransactionContext tc) { string sql = SQLParser.MakeSQL("SELECT * FROM Employee WHERE Status=%n order by EmployeeNo", EnumEmployeeStatus.Live); return tc.ExecuteReader(sql); } internal static IDataReader Get(TransactionContext tc, int payrollTypeID) { string sql = SQLParser.MakeSQL("SELECT * FROM Employee WHERE PAYROLLTYPEID = %n order by EmployeeNo", payrollTypeID); return tc.ExecuteReader(sql); } internal static IDataReader GetWithDiscontinue(TransactionContext tc, int payrollTypeID) { //string sql = SQLParser.MakeSQL("SELECT * FROM Employee WHERE Status=%n AND PAYROLLTYPEID = %n order by EmployeeNo",EnumEmployeeStatus.Live, // SystemInformation.CurrentSysInfo.PayrollTypeID.Integer); string sql = SQLParser.MakeSQL("SELECT * FROM Employee WHERE PAYROLLTYPEID = %n order by EmployeeNo", payrollTypeID); return tc.ExecuteReader(sql); } public static IDataReader GetAllSubordinates(TransactionContext tc, string sql) { return tc.ExecuteReader(sql); } //for Attendance Process internal static IDataReader GetAbsentEmp(TransactionContext tc, DateTime attnDate, EnumWorkPlanGroup wpGroup, int payrollTypeID) { string sql = SQLParser.MakeSQL("SELECT * FROM Employee WHERE Status=%n and PAYROLLTYPEID = %n AND JOININGDATE <=%d" + "AND EMPLOYEEID IN(SELECT EMPLOYEEID FROM EmployeeWorkPlanSetup WHERE WorkPlanGroupID = %n) " + "AND EMPLOYEEID NOT IN(SELECT EMPLOYEEID FROM DailyAttnProcess WHERE AttnDate = %d) order by EmployeeNo", EnumEmployeeStatus.Live, payrollTypeID, attnDate, wpGroup, attnDate); return tc.ExecuteReader(sql); } internal static IDataReader GetAttendaceEmp(TransactionContext tc, EnumWorkPlanGroup wpGroup, int payrollTypeID) { string sql = SQLParser.MakeSQL("SELECT * FROM Employee WHERE Status=%n and PAYROLLTYPEID = %n " + "AND EMPLOYEEID IN(SELECT EMPLOYEEID FROM EmployeeWorkPlanSetup WHERE WorkPlanGroupID = %n) order by EmployeeNo ", EnumEmployeeStatus.Live, payrollTypeID); return tc.ExecuteReader(sql); } internal static IDataReader GetEmpExceptAutoWP(TransactionContext tc, DateTime attnDate, int payrollTypeID) { string sql = SQLParser.MakeSQL("SELECT * FROM Employee WHERE Status=%n and PAYROLLTYPEID = %n AND JOININGDATE <=%d" + "AND EMPLOYEEID IN(SELECT EMPLOYEEID FROM EmployeeWorkPlanSetup WHERE WorkPlanGroupID NOT IN(%n)) order by EmployeeNo ", EnumEmployeeStatus.Live, payrollTypeID, attnDate, EnumWorkPlanGroup.Counter_Clock_1); return tc.ExecuteReader(sql); } internal static IDataReader GetDiscontinueEmp(TransactionContext tc, DateTime attnDate, DateTime currentDate, int payrollTypeID) { string sql = SQLParser.MakeSQL("SELECT * FROM Employee WHERE ENDOFCONTRACTDATE BETWEEN %d AND %d and PAYROLLTYPEID = %n " + "AND EMPLOYEEID IN(SELECT EMPLOYEEID FROM EmployeeWorkPlanSetup WHERE WorkPlanGroupID NOT IN(%n)) order by EmployeeNo ", attnDate, currentDate, payrollTypeID, EnumWorkPlanGroup.Counter_Clock_1); return tc.ExecuteReader(sql); } // internal static IDataReader GetEmpsWithDiscontinue(TransactionContext tc, string empIDs, int payrollTypeID) { return tc.ExecuteReader("SELECT * FROM Employee WHERE PAYROLLTYPEID = %n AND EMPLOYEEID IN(%q) order by EmployeeNo", payrollTypeID, empIDs); } internal static IDataReader GetByEmpIDs(TransactionContext tc, string empIDs, int payrollTypeID) { return tc.ExecuteReader("SELECT * FROM Employee WHERE PAYROLLTYPEID = %n AND EMPLOYEEID IN(%q) order by EmployeeNo", payrollTypeID, empIDs); } internal static IDataReader GetFssEmp(TransactionContext tc, string empIDs, int payrollTypeID) { return tc.ExecuteReader("SELECT * FROM Employee WHERE PAYROLLTYPEID = %n AND EMPLOYEEID IN(%q) order by EmployeeNo", payrollTypeID, empIDs); } internal static IDataReader GetByDeptIDs(TransactionContext tc, string sIDs, int payrollTypeID) { return tc.ExecuteReader("SELECT * FROM Employee WHERE Status=%n and PAYROLLTYPEID = %n AND DEPARTMENTID IN(%q) order by EmployeeNo", EnumEmployeeStatus.Live, payrollTypeID, sIDs); } internal static IDataReader GetSubordinates(TransactionContext tc, ID employeeID) { return tc.ExecuteReader(@"Select * From Employee Where EmployeeID IN( Select EmployeeID from OrganEmployee Where NodeID IN( Select OrganogramID From Organogram Where ParentID=( Select NodeID from OrganEmployee Where EmployeeID=%n)))", employeeID.Integer); } public static IDataReader GetSubordinates(TransactionContext tc, string sql) { return tc.ExecuteReader(sql); } internal static IDataReader GetbyInSQL(TransactionContext tc, string EmpIds, int payrollTypeID) { if (EmpIds == "") { string sql = SQLParser.MakeSQL("SELECT * FROM Employee WHERE PAYROLLTYPEID = %n ", payrollTypeID); return tc.ExecuteReader(sql); } else return tc.ExecuteReader("SELECT * FROM Employee WHERE PAYROLLTYPEID = %n AND EmployeeID IN ( %q )", payrollTypeID, EmpIds); } internal static IDataReader Get(TransactionContext tc, int status, DateTime lastDateOfYear) { string strSQLQuery = SQLParser.MakeSQL("SELECT * FROM Employee WHERE Status = %n AND JoiningDate <= %d ", status, lastDateOfYear); return tc.ExecuteReader(strSQLQuery); } internal static DataSet GetEmpIDsOfManager(TransactionContext tc, int status, DateTime lastDateOfYear) { string strSQLQuery = SQLParser.MakeSQL("SELECT EMPLOYEEID FROM Employee WHERE IsManager = 1 AND Status = %n AND JoiningDate <= %d ", status, lastDateOfYear); return tc.ExecuteDataSet(strSQLQuery); } internal static DataSet GetCurPos(TransactionContext tc) { string strSQLQuery = SQLParser.MakeSQL(@"begin DECLARE @empid int ,@pid int ,@tr int,@deptid INT,@rc VARCHAR(200) ,@dept VARCHAR(200) , @level1 VARCHAR(200),@level2 VARCHAR(200),@level3 VARCHAR(200), @level4 VARCHAR(200),@level5 VARCHAR(200),@level6 VARCHAR(200); DECLARE employees_Cur CURSOR FOR SELECT employeeid,departmentid FROM employee ; DELETE FROM EmpCurrentPosition; OPEN employees_Cur FETCH NEXT FROM employees_Cur INTO @empid,@deptid; While @@FETCH_STATUS=0 BEGIN SET @level1=''; SET @level2=''; SET @level3=''; SET @level4=''; SET @level5=''; SET @level6=''; SET @dept=(SELECT description FROM DEPARTMENT WHERE DEPARTMENTID=@deptid AND status=1); SET @rc=(SELECT rccode FROM DEPARTMENT WHERE DEPARTMENTID=@deptid AND status=1); SET @tr=(SELECT tire FROM DEPARTMENT WHERE DEPARTMENTID=@deptid AND status=1); IF @tr=1 begin SET @level1=@dept; END ELSE IF @tr=2 BEGIN SET @level2=@dept; SET @pid=(select parentid from DEPARTMENT where DEPARTMENTID=@deptid AND status=1); SET @level1=(select description from DEPARTMENT where DEPARTMENTID=@pid AND status=1); END ELSE IF @tr=3 BEGIN SET @level3=@dept; SET @pid=(select parentid from DEPARTMENT where DEPARTMENTID=@deptid AND status=1); SET @level2=(select description from DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @rc=(SELECT rccode FROM DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @pid=(select parentid from DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @level1=(select description from DEPARTMENT where DEPARTMENTID=@pid AND status=1); END ELSE IF @tr=4 BEGIN SET @level4=@dept; SET @pid=(select parentid from DEPARTMENT where DEPARTMENTID=@deptid AND status=1); SET @level3=(select description from DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @pid=(select parentid from DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @level2=(select description from DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @rc=(SELECT rccode FROM DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @pid=(select parentid from DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @level1=(select description from DEPARTMENT where DEPARTMENTID=@pid AND status=1); END ELSE IF @tr=5 BEGIN SET @level5=@dept; SET @pid=(select parentid from DEPARTMENT where DEPARTMENTID=@deptid AND status=1); SET @level4=(select description from DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @pid=(select parentid from DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @level3=(select description from DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @pid=(select parentid from DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @level2=(select description from DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @rc=(SELECT rccode FROM DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @pid=(select parentid from DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @level1=(select description from DEPARTMENT where DEPARTMENTID=@pid AND status=1); END ELSE IF @tr=6 BEGIN SET @level6=@dept; SET @pid=(select parentid from DEPARTMENT where DEPARTMENTID=@deptid AND status=1); SET @level5=(select description from DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @pid=(select parentid from DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @level4=(select description from DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @pid=(select parentid from DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @level3=(select description from DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @pid=(select parentid from DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @level2=(select description from DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @rc=(SELECT rccode FROM DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @pid=(select parentid from DEPARTMENT where DEPARTMENTID=@pid AND status=1); SET @level1=(select description from DEPARTMENT where DEPARTMENTID=@pid AND status=1); END INSERT INTO EmpCurrentPosition VALUES(@empid,@dept,@rc,@level1,@level2,@level3,@level4,@level5,@level6); FETCH NEXT FROM employees_Cur INTO @empid,@deptid; END SELECT * FROM EmpCurrentPosition; CLOSE employees_Cur DEALLOCATE employees_Cur END"); return tc.ExecuteDataSet(strSQLQuery); } internal static DataSet GetOldEmp(TransactionContext tc) { string strSQLQuery = SQLParser.MakeSQL("SELECT DG.Name as Designation, D.DESCRIPTION as Department, * FROM GP_Employee Inner Join GP_DEPARTMENT as D on GP_Employee.DEPARTMENTID = D.DEPARTMENTID Inner Join GP_DESIGNATION as DG on GP_Employee.DESIGNATIONID = DG.DESIGNATIONID"); return tc.ExecuteDataSet(strSQLQuery); } internal static DataSet GetAllEmp(TransactionContext tc) { string strSQLQuery = SQLParser.MakeSQL("SELECT Employeeid,employeeno,Employee.name, DG.Name as Designation, D.DESCRIPTION as Department, * FROM Employee Inner Join DEPARTMENT as D on Employee.DEPARTMENTID = D.DEPARTMENTID Inner Join DESIGNATION as DG on Employee.DESIGNATIONID = DG.DESIGNATIONID"); return tc.ExecuteDataSet(strSQLQuery); } internal static IDataReader Get(TransactionContext tc, ID nID) { return tc.ExecuteReader("SELECT * FROM Employee WHERE EmployeeID=%n ", nID.Integer); } internal static IDataReader GetByCardID(TransactionContext tc, ID nID) { return tc.ExecuteReader("SELECT * FROM Employee WHERE CardID=%n ", nID.Integer); } internal static IDataReader GetParentEmployee(TransactionContext tc, ID nID) { return tc.ExecuteReader(@"Select * From Employee Where EmployeeID IN( Select EMPLOYEEID from OrganEmployee Where NodeID IN( Select OrganogramID From Organogram where OrganogramID in( Select ParentID From Organogram where OrganogramID in( Select nodeid from OrganEmployee Where EmployeeID=%n))))", nID.Integer); } internal static IDataReader GetWithOutcheckPayrollType(TransactionContext tc, string employeeNo) { return tc.ExecuteReader("SELECT * FROM Employee WHERE EmployeeNo=%s ", employeeNo); } internal static IDataReader GetByEmailAddress(TransactionContext tc, string empEmail) { return tc.ExecuteReader("SELECT * FROM Employee WHERE EMAILADDRESS=%s AND STATUS=%n ", empEmail, 1); } internal static IDataReader Get(TransactionContext tc, string employeeNo, int payrollTypeID) { return tc.ExecuteReader("SELECT * FROM Employee WHERE employeeNo=%s AND PayrollTypeID=%n", employeeNo, payrollTypeID); } internal static DataSet GetEmpBasicInfo(TransactionContext tc, string sEmpID) { DataSet oEmpBasicInfos = new DataSet(); try { string sSql = SQLParser.MakeSQL("SELECT Emp.EMPLOYEENO,Emp.DepartmentID,Emp.NAME,Emp.GENDER,RE.NAME AS RName,Emp.BIRTHDATE,EMp.JOININGDATE,Emp.DATEOFCONFIRMATION,Emp.MARITALSTATUSID" + " FROM EMPLOYEE AS Emp,Religion AS RE" + " WHERE Emp.EMPLOYEEID IN(%q) AND emp.RELIGIONID=RE.RELIGIONID order by Emp.EmployeeNo", sEmpID); oEmpBasicInfos = tc.ExecuteDataSet(sSql); } catch (Exception ex) { throw new Exception(ex.Message); } return oEmpBasicInfos; } internal static DataSet GetLineManager(TransactionContext tc, int sEmpID) { DataSet oLineManager = new DataSet(); try { string sql = SQLParser.MakeSQL(@"SELECT Employee.Name FROM Employee, (SELECT EmployeeID FROM OrganEmployee oe WHERE oe.NodeID = ( SELECT ParentID FROM Organogram o WHERE o.OrganogramID= (SELECT NodeID FROM OrganEmployee oe WHERE EmployeeID=%n))) tab1 WHERE Employee.EMPLOYEEID=tab1.EmployeeID", sEmpID); oLineManager = tc.ExecuteDataSet(sql); } catch (Exception ex) { throw new Exception(ex.Message); } return oLineManager; } internal static DataSet GetDivision(TransactionContext tc, int nEmpID) { DataSet oEmpBasicInfos = new DataSet(); try { oEmpBasicInfos = tc.ExecuteDataSet("SELECT * from vw_EmpDivision Where EmployeeID=%n", nEmpID); } catch (Exception ex) { throw new Exception(ex.Message); } return oEmpBasicInfos; } internal static DataSet GetEmpDetails(TransactionContext tc, string sEmpID) { DataSet oEmpDetails = new DataSet(); try { //oEmpDetails = tc.ExecuteDataSet("SELECT Emp.EMPLOYEENO,Emp.Name,DG.NAME AS DegName,Dep.DESCRIPTION AS DepDes,GD.DESCRIPTION AS GDDes," // + " Emp.BIRTHDATE,Emp.JOININGDATE,Emp.DATEOFCONFIRMATION,Emp.GENDER," // + " Emp.MARITALSTATUSID,Emp.MOBILENO,Emp.EMAILADDRESS,BK.NAME AS Bank,BR.NAME AS Branch," // + " Emp.ACCOUNTNO,RG.NAME AS RegName,Emp.BASICSALARY,Emp.TAXAMOUNT" // + " FROM" // + " Employee AS Emp,Designation DG,Department AS Dep,Grades AS GD," // + " Religion AS RG,Banks AS BK,Branches AS BR" // + " WHERE Emp.DESIGNATIONID=DG.DESIGNATIONID" // + " AND Emp.DEPARTMENTID=Dep.DEPARTMENTID" // + " AND Emp.GRADEID=GD.GRADEID" // + " AND Emp.RELIGIONID=RG.RELIGIONID" // + " AND Emp.BRANCHID=BR.BRANCHID" // + " AND BK.BANKID=Br.BANKID" // + " AND Emp.EMPLOYEEID IN(%q) order by Emp.EMPLOYEENO ", sEmpID); string sql = SQLParser.MakeSQL( @"SELECT Emp.EMPLOYEENO,Emp.Name,Emp.DepartmentID,Emp.BIRTHDATE,Emp.JOININGDATE,Emp.DATEOFCONFIRMATION, Emp.GENDER,Emp.MARITALSTATUSID,Emp.MOBILENO,Emp.EMAILADDRESS,Emp.ACCOUNTNO,Emp.OutPayAccountNo,Emp.TINNO, Emp.BASICSALARY,Emp.GrossSalary,Emp.VendorCode,Emp.TAXAMOUNT,Deg.NAME AS DegName,Dep.DESCRIPTION AS DepDes, GD.DESCRIPTION AS GDDes,BK.NAME AS Bank,BR.NAME AS Branch,BKOUT.NAME AS OUTPayBank,BROUT.NAME As OUTPayBranch, RG.NAME AS RegName,Loc.Description as Locdes,IsNull(ac.CardNumber,'') as CardNo,na.Description AS Nationality, Emp.FATHERNAME,Emp.MOTHERNAME,eSpouse.Name AS Spouse,Emp.NationalID,Emp.BLOODGROUP,eContact.PARMANENTADDRESS AS PerVillage, dis1.[NAME] AS PerDist,t1.[NAME] AS PerThana,eContact.PRESENTADDRESS AS TempVillage,dis2.[NAME] AS TempDist, t2.[NAME] AS TempThana FROM EMPLOYEE AS Emp LEFT Outer JOIN DESIGNATION AS Deg ON Emp.DESIGNATIONID=Deg.DESIGNATIONID LEFT OUTER JOIN Department AS Dep ON Emp.DEPARTMENTID=Dep.DEPARTMENTID LEFT OUTER JOIN Grades AS GD ON Emp.GRADEID=GD.GRADEID LEFT OUTER JOIN Religion AS RG ON Emp.RELIGIONID=RG.RELIGIONID LEFT OUTER JOIN Branches AS BR ON Emp.BRANCHID=BR.BRANCHID LEFT OUTER JOIN BANKS AS BK ON BK.BANKID=BR.BANKID LEFT OUTER JOIN Branches AS BROUT ON Emp.OUTPayBRANCHID=BROUT.BRANCHID LEFT OUTER JOIN BANKS AS BKOUT ON BKOUT.BANKID=BROUT.BANKID LEFT OUTER JOIN Location AS Loc ON Emp.LocationID=Loc.LocationID LEFT OUTER JOIN AccessCard ac ON Emp.CardID = ac.AccessCardID LEFT OUTER JOIN NATIONALITY na ON Emp.NationalityID = na.NATIONALITYID LEFT OUTER JOIN EMPSPOUSE eSpouse ON Emp.EMPLOYEEID = eSpouse.EMPLOYEEID LEFT OUTER JOIN EMPCONTACT eContact ON Emp.EMPLOYEEID = eContact.EMPLOYEEID LEFT OUTER JOIN DISTRICT dis1 ON eContact.PARMANENTDISTRICTID = dis1.DISTRICTID LEFT OUTER JOIN THANA t1 ON eContact.PARMANENTTHANAID = t1.THANAID LEFT OUTER JOIN DISTRICT dis2 ON eContact.PRESENTDISTRICTID = dis2.DISTRICTID LEFT OUTER JOIN Thana t2 ON eContact.PRESENTTHANAID = t2.THANAID WHERE Emp.EMPLOYEEID IN(%q) ORDER BY Emp.EMPLOYEENO", sEmpID); oEmpDetails = tc.ExecuteDataSet(sql); //oEmpDetails = tc.ExecuteDataSet("SELECT Emp.EMPLOYEENO,Emp.Name,Emp.BIRTHDATE,Emp.JOININGDATE,Emp.DATEOFCONFIRMATION, " // + " Emp.GENDER,Emp.MARITALSTATUSID,Emp.MOBILENO,Emp.EMAILADDRESS,Emp.ACCOUNTNO," // + " Emp.BASICSALARY,Emp.GrossSalary,Emp.VendorCode,Emp.TAXAMOUNT,Deg.NAME AS DegName,Dep.DESCRIPTION AS DepDes," // + " GD.DESCRIPTION AS GDDes,BK.NAME AS Bank,BR.NAME AS Branch,RG.NAME AS RegName,Loc.Description as Locdes" // + " FROM EMPLOYEE AS Emp " // + " LEFT Outer JOIN DESIGNATION AS Deg ON Emp.DESIGNATIONID=Deg.DESIGNATIONID" // + " LEFT OUTER JOIN Department AS Dep ON Emp.DEPARTMENTID=Dep.DEPARTMENTID " // + " LEFT OUTER JOIN Grades AS GD ON Emp.GRADEID=GD.GRADEID " // + " LEFT OUTER JOIN Religion AS RG ON Emp.RELIGIONID=RG.RELIGIONID" // + " LEFT OUTER JOIN Branches AS BR ON Emp.BRANCHID=BR.BRANCHID" // + " LEFT OUTER JOIN BANKS AS BK ON BK.BANKID=BR.BANKID" // + " LEFT OUTER JOIN Location AS Loc ON Emp.LocationID=Loc.LocationID" // + " WHERE Emp.EMPLOYEEID IN(%q) ORDER BY Emp.EMPLOYEENO", sEmpID); } catch (Exception ex) { throw new Exception(ex.Message); } return oEmpDetails; } // internal static DataSet GetEmployeeCV(TransactionContext tc, string sEmpID) // { // DataSet oEmpCV = new DataSet(); // try // { // string sql = SQLParser.MakeSQL(@"SELECT e.[NAME], e.EMPLOYEENO, e.FATHERNAME, e.MOTHERNAME, e.NationalID,n.[DESCRIPTION] AS Nationality, e.BIRTHDATE,e.GENDER, // e.MARITALSTATUSID,r.[NAME] AS Religion,e2.PARMANENTTELEPHONE AS Telephone1, e2.PRESENTTELEPHONE AS Telephone2, e2.FAX, e.MOBILENO, // e.EMAILADDRESS,e2.PARMANENTADDRESS, t.name AS Thana, d.[NAME] AS District,e2.PRESENTADDRESS, t.name AS Thana, d.[NAME] AS District, // c.[DESCRIPTION] AS Company, dpt.[DESCRIPTION] AS Department, dg.[NAME] AS designation, e.JOININGDATE AS Appointment, // e.[STATUS], g.[DESCRIPTION] AS Grade // FROM EMPLOYEE e // LEFT JOIN EMPCONTACT e2 ON e2.EMPLOYEEID= e.EMPLOYEEID // INNER JOIN RELIGION r ON r.RELIGIONID = e.RELIGIONID // INNER JOIN NATIONALITY n ON n.NATIONALITYID=e.NATIONALITYID // INNER JOIN DISTRICT d ON d.DISTRICTID= e2.PARMANENTDISTRICTID // INNER JOIN THANA t ON t.THANAID=e2.PARMANENTTHANAID // LEFT JOIN COMPANY c ON c.COMPANYID=e.CompanyID // INNER JOIN Department dpt ON dpt.DEPARTMENTID= e.DEPARTMENTID // LEFT JOIN Designation dg ON dg.DESIGNATIONID= e.DESIGNATIONID // LEFT JOIN GRADES g ON g.GRADEID= e.GRADEID // WHERE e.EMPLOYEEID=%q",sEmpID); // oEmpCV = tc.ExecuteDataSet(sql); // } // catch(Exception ex) // { // throw new Exception(ex.Message); // } // return oEmpCV; // } internal static DataSet GetEmpForLetterOfIntroduction(TransactionContext tc, string sEmpID,DateTime month) { DataSet oEmpDetails = new DataSet(); try { string sql = SQLParser.MakeSQL( @"SELECT IsNull(c.DESCRIPTION,'') AS CompanyName,IsNull(ctry.Description,'') AS [Country],e.EMPLOYEENO AS EmpNo, e.NAME AS EmpName,e.BIRTHDATE AS DOB, IsNull(ec.PRESENTADDRESS,'') AS Address, e.JOININGDATE AS DOJ,isNULL(des.NAME,'') AS [Designation], '' AS EmpType, IsNull(s.CHANGEDAMOUNT,0) AS [Salary] FROM (SELECT * from Employee WHERE EmployeeID IN (%q)) as e LEFT JOIN COMPANY c ON e.CompanyID = c.COMPANYID LEFT JOIN NATIONALITY ctry ON ctry.NATIONALITYID = e.NATIONALITYID LEFT JOIN EMPCONTACT ec ON e.EMPLOYEEID = ec.EMPLOYEEID LEFT JOIN DESIGNATION des ON des.DESIGNATIONID = e.DESIGNATIONID LEFT JOIN (SELECT sm.EmployeeID,sm.SalaryMonth,smd.CHANGEDAMOUNT FROM SALARYMONTHLY sm, SALARYMONTHLYDETAIL smd WHERE sm.SALARYMONTHLYID = smd.SALARYMONTHLYID AND smd.ITEMID = -101 AND smd.ItemGroup = 1 AND sm.SalaryMonth = %d) as s on e.EMPLOYEEID = s.EMPLOYEEID", sEmpID, month); oEmpDetails = tc.ExecuteDataSet(sql); } catch (Exception ex) { throw new Exception(ex.Message); } return oEmpDetails; } internal static DataSet GetEmpPosting(TransactionContext tc, string sEmpID) { DataSet oEmpPostings = new DataSet(); try { oEmpPostings = tc.ExecuteDataSet("Select Emp.EMPLOYEENO,Emp.NAME,Dept.DESCRIPTION as Department,Loc.DESCRIPTION as Location,Desg.NAME as Designation,HREmp.EFFECTIVEDATE from EMPLOYEE as Emp,DEPARTMENT as Dept,LOCATION as Loc,DESIGNATION as Desg, HREMPPOSTING as HREmp" + " where Emp.EMPLOYEEID=HREmp.EMPLOYEEID " + " and Desg.DESIGNATIONID=HREmp.DESIGNATIONID" + " and Dept.DEPARTMENTID=HREmp.FUNCTIONID " + " and Loc.LOCATIONID=HREmp.FUNCTIONID " + " and HREmp.EMPLOYEEID in(%q) " + " order By Emp.EMPLOYEENO ", sEmpID); } catch (Exception ex) { throw new Exception(ex.Message); } return oEmpPostings; } internal static DataSet GetEmpConfirmHis(TransactionContext tc, DateTime dEffectDate, DateTime dEffectDate2, int payrollTypeID) { DataSet oEmpConfirms = new DataSet(); try { oEmpConfirms = tc.ExecuteDataSet("Select Emp.Employeeid,eh.emplifecycleid,Eh.status, Emp.EMPLOYEENO,Emp.NAME,Deg.NAME AS DESIGNATION,G.DESCRIPTION as Grade, Emp.JOININGDATE," + " Emp.DATEOFCONFIRMATION as DATEOFCONFIRMATION,EH.SalaryMonth EFFECTDATE,EH.REMARKS as Comments,US.LoginID as CommitedBy,EH.CreationDate" + " from EmpLifeCycle as EH, DBO.EMPLOYEE as Emp ,USERS as US,DESIGNATION as Deg,GRADES as G " + " WHERE Emp.DESIGNATIONID=Deg.DESIGNATIONID AND Emp.GRADEID=G.GRADEID AND US.USERID=EH.CreatedBy AND " + " Emp.DATEOFCONFIRMATION between %d and %d AND eh.isconfirm=1 AND" + " Emp.EMPLOYEEID=EH.EMPLOYEEID AND Emp.PayrollTypeID=%n order by Emp.EMPLOYEENO", dEffectDate, dEffectDate2, payrollTypeID); } catch (Exception ex) { throw new Exception(ex.Message); } return oEmpConfirms; } internal static DataSet GetEmpPFHis(TransactionContext tc, DateTime dEffectDate) { DataSet oEmpConfirms = new DataSet(); try { oEmpConfirms = tc.ExecuteDataSet("Select Emp.EMPLOYEENO,Emp.NAME,G.DESCRIPTION,Emp.PFMEMBERSHIPDT,US.UserID,Emp.CreationDate " + " from EMPLOYEE as Emp,GRADES as G,Users as US" + " Where Emp.GRADEID=G.GRADEID " + " and US.UserID=Emp.CreatedBy " + " and Emp.DATEOFCONFIRMATION between %d and %d" + " order by Emp.EMPLOYEENO", GlobalFunctions.FirstDateOfMonth(dEffectDate), GlobalFunctions.LastDateOfMonth(dEffectDate)); } catch (Exception ex) { throw new Exception(ex.Message); } return oEmpConfirms; } internal static DataSet GetEmpGradeSalaryChangeInfo(TransactionContext tc, DateTime dFrom, DateTime dTo, string selection) { DataSet changeinfo = new DataSet(); string sql = string.Empty; try { if (selection == "Basic Salary") { sql = SQLParser.MakeSQL(@"SELECT DISTINCT e.EMPLOYEENO,e.NAME,g.EffectDate AS EFFECTDATE,g.CreationDate AS EntryDate,g2.DESCRIPTION AS Grade, g.BASICSALARY,u.USERNAME, g.DESCRIPTION AS Remarks FROM EmpLifeCycle AS g,EMPLOYEE AS e,GRADES AS g2 ,USERS AS u WHERE e.EMPLOYEEID=g.EMPLOYEEID And e.GRADEID=g2.GRADEID AND g.CreatedBy=u.USERID AND g.salarymonth BETWEEN %d AND %d And e.payrolltypeid=1 AND g.BasicSalary>0", dFrom, dTo); // AND g.CreatedDate BETWEEN %d AND %d And e.payrolltypeid=1 AND g.TILLDATE IS NOT null", dFrom, dTo); } else { sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO,e.NAME,b.CreationDate AS EntryDate,Max (b.CHANGEDATE) AS EFFECTDATE, g2.DESCRIPTION AS Grade,e.BASICSALARY,u.USERNAME,'Bank Account Change' AS Remarks FROM BANKACCOUNTHISTORY AS b,EMPLOYEE AS e,GRADES AS g2 ,USERS AS u WHERE e.EMPLOYEEID=b.EMPLOYEEID AND b.CreatedBy=u.USERID And e.GRADEID=g2.GRADEID AND b.CreationDate BETWEEN %d AND %d And e.payrolltypeid=%n GROUP BY e.EMPLOYEENO,e.NAME,b.CreationDate,g2.DESCRIPTION ,e.BASICSALARY,u.USERNAME", dFrom, dTo, Payroll.BO.SystemInformation.CurrentSysInfo.PayrollTypeID.Integer); } changeinfo = tc.ExecuteDataSet(sql); } catch(Exception ex) { throw new Exception(ex.Message); } return changeinfo; } internal static DataSet GetEmpJoining(TransactionContext tc, DateTime dEffectDate, DateTime dEffectDate2) { DataSet oEmpConfirms = new DataSet(); try { string sQL = SQLParser.MakeSQL(@"Select emp.basicsalary,emp.maritalstatusid,emp.religionid,Users.UserName createduser,emp.creationdate, emp.employeeid,Emp.EMPLOYEENO,Emp.Gender,Emp.NAME,G.DESCRIPTION,Emp.DESIGDESCRIPTION as DegName,Emp.JoiningDate,Emp.Grosssalary,Emp.BIRTHDATE from EMPLOYEE as Emp,GRADES as G, Users Where Emp.GRADEID=G.GRADEID and Users.USERID=emp.CreatedBy and Emp.JoiningDate between %d and %d and emp.payrolltypeid=%n order by Emp.EMPLOYEENO", dEffectDate, dEffectDate2,Payroll.BO.SystemInformation.CurrentSysInfo.PayrollTypeID.Integer); #region OldCode //string sQL = SQLParser.MakeSQL("Select emp.basicsalary,emp.maritalstatusid,emp.religionid,lc.emplifecycleid,emp.createdby createduser,lc.effectdate,emp.creationdate,emp.employeeid,Emp.EMPLOYEENO,Emp.Gender,Emp.NAME,G.DESCRIPTION,Dg.Name as DegName,Emp.JoiningDate,Emp.Grosssalary,Emp.BIRTHDATE,lc.createdby " // + " from EMPLOYEE as Emp,GRADES as G,Designation as Dg ,emplifecycle lc" // + " Where Emp.GRADEID=G.GRADEID and emp.DesignationID=Dg.DesignationID and " // + " Emp.JoiningDate between %d and %d AND" // + " Emp.status=1 AND" // + " emp.employeeid=lc.employeeID AND" // + " lc.iscontinue=1 AND" // + " Emp.JoiningDate=lc.effectdate" // + " order by Emp.EMPLOYEENO", GlobalFunctions.FirstDateOfMonth(dEffectDate), GlobalFunctions.LastDateOfMonth(dEffectDate)); #endregion oEmpConfirms = tc.ExecuteDataSet(sQL); } catch (Exception ex) { throw new Exception(ex.Message); } return oEmpConfirms; } internal static DataSet GetEmpJoining4Novartis(TransactionContext tc, DateTime dEffectDate, int payrollTypeID) { DataSet oEmpJoining = new DataSet(); try { oEmpJoining = tc.ExecuteDataSet("Select Emp.EMPLOYEENO,Emp.NAME,Dg.Name as Designation,Emp.JoiningDate as DOJ,G.DESCRIPTION as Grade ," + " Emp.Basicsalary as Basic,Emp.BIRTHDATE as DOB,Emp.Gender as Sex,Emp.MaritalStatusID,Rel.Name as Religion,US.LoginID as CommitedBy,Emp.creationdate as CommitedOn " + " from Employee as Emp" + " left outer JOIN Grades AS G ON Emp.GradeID=G.GradeID" + " left outer Join Designation as Dg on emp.DesignationID=Dg.DesignationID" //+ " left outer Join EmpCostcenter as EmpCC on EmpCC.EmployeeID=Emp.EmployeeID" //+ " left outer Join CRG as CC on CC.CRGID=EmpCC.CostCenterID" + " INNER Join Religion as Rel on Emp.ReligionId=Rel.ReligionId " + " INNER Join USERS as US on US.USERID=Emp.CreatedBy " + " and Emp.creationdate between %d and %d ANd Emp.PayrollTypeID=%n" + " order by Emp.EMPLOYEENO", GlobalFunctions.FirstDateOfMonth(dEffectDate), GlobalFunctions.LastDateOfMonth(dEffectDate), payrollTypeID); } catch (Exception ex) { throw new Exception(ex.Message); } return oEmpJoining; } internal static DataSet GetDesignationWiseEmployeeCountsByDepartmentID(TransactionContext tc, int departmentID) { DataSet designationWiseCountDS = new DataSet(); try { designationWiseCountDS = tc.ExecuteDataSet(@"Select '['+de.Code+']-'+de.Name GradeOrDesignation,empC.[Count] From ( Select DesignationID,COUNT(*) [Count] From Employee Where DepartmentID=%n AND [Status] = %n Group by DesignationID )empC ,Designation de Where empC.DesignationID = de.DesignationID", departmentID, (int)EnumEmployeeStatus.Live); } catch (Exception ex) { throw new Exception(ex.Message); } return designationWiseCountDS; } // internal static DataSet GetDFSL(TransactionContext tc) // { // DataSet designationWiseCountDS = new DataSet(); // try // { // tc.ExecuteNonQuery(@"DECLARE @CurrentLevel INT // SET @CurrentLevel = 0 // DELETE FROM Department2 // INSERT INTO Department2 // (ID, Parent_ID, Level, Hierarchy) // SELECT DepartmentID, // ParentID, // 0 AS Level, // [DESCRIPTION] AS Hierarchy // FROM Department // WHERE ParentID IS NULL // WHILE @@ROWCOUNT > 0 // BEGIN // SET @CurrentLevel = @CurrentLevel + 1 --Started at 0 // // INSERT INTO Department2 // (ID, Parent_ID, Level, Hierarchy) // SELECT p.DEPARTMENTID, // p.PARENTID, // @CurrentLevel AS Level, // h.Hierarchy + ',' + p.[DESCRIPTION] // FROM Department p // INNER JOIN Department2 h // ON p.PARENTID = h.ID // AND h.Level = @CurrentLevel - 1 // END"); // designationWiseCountDS = // tc.ExecuteDataSet(@"SELECT * FROM Department2"); // } // catch (Exception ex) // { // throw new Exception(ex.Message); // } // return designationWiseCountDS; // } internal static DataSet GetDFSL(TransactionContext tc) { DataSet designationWiseCountDS = new DataSet(); try { tc.ExecuteNonQuery(@"DECLARE @CurrentLevel INT SET @CurrentLevel = 0 DELETE FROM Department2 INSERT INTO Department2 (ID, Parent_ID, Level, Hierarchy) SELECT DepartmentID, ParentID, 0 AS Level, [DESCRIPTION] AS Hierarchy FROM Department WHERE ParentID IS NULL WHILE @@ROWCOUNT > 0 BEGIN SET @CurrentLevel = @CurrentLevel + 1 --Started at 0 INSERT INTO Department2 (ID, Parent_ID, Level, Hierarchy) SELECT p.DEPARTMENTID, p.PARENTID, @CurrentLevel AS Level, h.Hierarchy + ',' + p.[DESCRIPTION] FROM Department p INNER JOIN Department2 h ON p.PARENTID = h.ID AND h.Level = @CurrentLevel - 1 END"); designationWiseCountDS = tc.ExecuteDataSet(@"SELECT * FROM Department2"); } catch (Exception ex) { throw new Exception(ex.Message); } return designationWiseCountDS; } internal static DataSet GetGradeWiseEmployeeCountsByDepartmentID(TransactionContext tc, int departmentID) { DataSet designationWiseCountDS = new DataSet(); try { designationWiseCountDS = tc.ExecuteDataSet(@"Select '['+gd.Code+']-'+gd.[Description] GradeOrDesignation,empC.[Count] From ( Select GradeID,COUNT(*) [Count] From Employee Where DepartmentID=%n AND [Status] = %n Group by GradeID )empC ,Grades gd where empC.GradeID = gd.GradeID", departmentID, (int)EnumEmployeeStatus.Live); } catch (Exception ex) { throw new Exception(ex.Message); } return designationWiseCountDS; } internal static int GetDays(TransactionContext tc, int nPayrollTypeID) { int nDays = 0; object obj = tc.ExecuteScalar("Select Days from EarnedLeaveDays where PayrollType=%n", nPayrollTypeID); if (obj != null) { nDays = Convert.ToInt32(obj); } else { nDays = 0; } return nDays; } internal static IDataReader GetEmployeesYetNotAssigned(TransactionContext tc) { //string sql = SQLParser.MakeSQL(@"SELECT * FROM Employee WHERE EmployeeNo NOT IN (select LOGINID From Users where UserType=%n) Order By EmployeeNo", // EnumSystemType.Web); string SQL = SQLParser.MakeSQL("SELECT * FROM Employee WHERE EmployeeNo NOT IN (select LOGINID From Users where UserType=%n) Order By EmployeeNo", EnumSystemType.Web); //string no = "23"; //string SQL = SQLParser.MakeSQL("SELECT * FROM Employee WHERE EmployeeNo = %s AND EmployeeNo NOT IN (select LOGINID From Users where UserType=%n)", // no,EnumSystemType.Web); return tc.ExecuteReader(SQL); } public static IDataReader GetEmployeesByJoiningMonth(TransactionContext tc, DateTime fromDate, DateTime toDate) { string SQL = SQLParser.MakeSQL("SELECT * FROM EMPLOYEE Where JoiningDate Between %d and %d order by EMPLOYEEID", fromDate, toDate); return tc.ExecuteReader(SQL); } internal static DataSet GetCashAdvice(TransactionContext tc, string sEmpIDs, string dSalDate) { DataSet FinallSet = new DataSet(); #region Old Code // string ssql = SQLParser.MakeSQL(@"SELECT a.*,l.Employeeno,l.name,loc.description as location // FROM // (select tab1.Employeeid,'Gross' as description,sum(changedamount) as Amount from( // select smd.*,sm.Employeeid from salarymonthlydetail smd,salarymonthly sm where salarymonth=%d // AND sm.salarymonthlyid=smd.salarymonthlyid AND sm.EMPLOYEEID IN(%q) AND sm.branchid is null)tab1 // where tab1.itemgroup=1 // group by tab1.Employeeid // union // select tab1.Employeeid,description,sum(changedamount) as Amount from( // select smd.*,sm.Employeeid from salarymonthlydetail smd,salarymonthly sm where salarymonth=%d // AND sm.salarymonthlyid=smd.salarymonthlyid AND sm.EMPLOYEEID IN(%q) AND sm.branchid is null)tab1 // where tab1.itemgroup=3 // group by tab1.Employeeid,tab1.itemid,tab1.description // union // select tab1.Employeeid,'Signature' as description,0 as Amount from( // select smd.*,sm.Employeeid from salarymonthlydetail smd,salarymonthly sm where salarymonth=%d // AND sm.salarymonthlyid=smd.salarymonthlyid AND sm.EMPLOYEEID IN(%q) AND sm.branchid is null)tab1 // where tab1.itemgroup=1 AND itemcode=-101 AND itemid=-101 // group by tab1.Employeeid,tab1.itemid,tab1.description) a // JOIN // Employee l // ON l.Employeeid = a.Employeeid // join Location loc ON l.locationid=loc.locationid order by Employeeid", dSalDate, sEmpIDs, dSalDate, sEmpIDs, dSalDate, sEmpIDs); #endregion #region Old Code // string sql = SQLParser.MakeSQL(@"SELECT a.*,l.Employeeno,l.name,loc.description as location // FROM // ( // select tt.Employeeid,'Gross' as description,(tt.Amount-IsNull(ot.amount,0)) Amount FROM( // (select ott.Employeeid,'Gross' as description,sum(changedamount) as Amount from( // select smd.*,sm.Employeeid from salarymonthlydetail smd,salarymonthly sm where salarymonth=%d // AND sm.salarymonthlyid=smd.salarymonthlyid AND sm.EMPLOYEEID IN(%q) AND sm.branchid is null // )ott // where ott.itemgroup in(1,8) // group by ott.Employeeid) tt // Left Outer join // ( select Sum(A.amount) as amount,E.EmployeeID from // ( select S.EmployeeID, Sum(sd.changedamount) as Amount // from SALARYMONTHLY S,SalaryMonthlyDetail Sd // where S.SalaryMonthlyID=Sd.SalaryMonthlyID // AND S.SalaryMonth=%d and Sd.ItemGroup IN (2) AND S.EmployeeID IN(%q) // group by S.EmployeeID // ) A, Employee E where A.EmployeeID = E.EmployeeID group by E.EmployeeID)as ot on tt.Employeeid=ot.EmployeeID) // union // select tab1.Employeeid,description,sum(changedamount) as Amount from( // select smd.*,sm.Employeeid from salarymonthlydetail smd,salarymonthly sm where salarymonth=%d // AND sm.salarymonthlyid=smd.salarymonthlyid AND sm.EMPLOYEEID IN(%q) AND sm.branchid is null)tab1 // where tab1.itemgroup=3 // group by tab1.Employeeid,tab1.itemid,tab1.description // union // select tab1.Employeeid,'Signature' as description,0 as Amount from( // select smd.*,sm.Employeeid from salarymonthlydetail smd,salarymonthly sm where salarymonth=%d // AND sm.salarymonthlyid=smd.salarymonthlyid AND sm.EMPLOYEEID IN(%q) AND sm.branchid is null)tab1 // where tab1.itemgroup=1 AND itemcode=-101 AND itemid=-101 // group by tab1.Employeeid,tab1.itemid,tab1.description) a // JOIN // Employee l // ON l.Employeeid = a.Employeeid // join Location loc ON l.locationid=loc.locationid order by Employeeid", dSalDate, sEmpIDs, dSalDate, sEmpIDs, dSalDate, sEmpIDs, dSalDate, sEmpIDs); //union // select tab1.Employeeid,'Signature' as description,0 as Amount,tab1.CostCenter from( // select smd.*,sm.Employeeid,(CC.[DESCRIPTION])as CostCenter // from salarymonthlydetail smd,salarymonthly sm ,CRG CC,SALARYEMPCOSTCENTER empcc // where salarymonth=%d // AND sm.salarymonthlyid=smd.salarymonthlyid // AND sm.EMPLOYEEID IN(%q) // AND sm.branchid is NULL // AND CC.CrgID=empcc.CostCenterID AND sm.EmployeeID=empcc.EmployeeID // AND smd.SALARYMONTHLYID=empcc.SALARYMONTHLYID // )tab1 // where tab1.itemgroup=1 AND itemcode=-101 AND itemid=-101 // group by tab1.Employeeid,tab1.itemid,tab1.description,tab1.CostCenter #endregion string sql = SQLParser.MakeSQL(@"SELECT a.*,l.Employeeno,l.name,loc.description as location FROM ( select tt.Employeeid,'Gross' as description,(tt.Amount-IsNull(ot.amount,0)) Amount,tt.CostCenter FROM( (select ott.Employeeid,'Gross' as description,sum(changedamount) as Amount,ott.CostCenter from( select smd.*,sm.Employeeid,(CC.[DESCRIPTION])as CostCenter from salarymonthlydetail smd,salarymonthly sm,CRG CC,SALARYEMPCOSTCENTER empcc where salarymonth=%d AND sm.salarymonthlyid=smd.salarymonthlyid AND sm.EMPLOYEEID IN(%q) AND sm.branchid is NULL AND CC.CrgID=empcc.CostCenterID AND sm.EmployeeID=empcc.EmployeeID AND smd.SALARYMONTHLYID=empcc.SALARYMONTHLYID )ott where ott.itemgroup in(1,8) group by ott.Employeeid,ott.CostCenter) tt Left Outer join ( select Sum(A.amount) as amount,E.EmployeeID,A.CostCenter from ( select S.EmployeeID, Sum(sd.changedamount) as Amount,(CC.[DESCRIPTION])as CostCenter from SALARYMONTHLY S,SalaryMonthlyDetail Sd,CRG CC,SALARYEMPCOSTCENTER empcc where S.SalaryMonthlyID=Sd.SalaryMonthlyID AND S.SalaryMonth=%d and Sd.ItemGroup IN (2) AND S.EmployeeID IN(%q) AND CC.CrgID=empcc.CostCenterID AND S.EmployeeID=empcc.EmployeeID AND sd.SALARYMONTHLYID=empcc.SALARYMONTHLYID group by S.EmployeeID,CC.[DESCRIPTION] ) A, Employee E where A.EmployeeID = E.EmployeeID group by E.EmployeeID,A.CostCenter)as ot on tt.Employeeid=ot.EmployeeID) union select tab1.Employeeid,description,sum(changedamount) as Amount,tab1.CostCenter from( select smd.*,sm.Employeeid,(CC.[DESCRIPTION])as CostCenter from salarymonthlydetail smd,salarymonthly sm ,CRG CC,SALARYEMPCOSTCENTER empcc where salarymonth=%d AND sm.salarymonthlyid=smd.salarymonthlyid AND sm.EMPLOYEEID IN(%q) AND sm.branchid is NULL AND CC.CrgID=empcc.CostCenterID AND sm.EmployeeID=empcc.EmployeeID AND smd.SALARYMONTHLYID=empcc.SALARYMONTHLYID )tab1 where tab1.itemgroup=3 group by tab1.Employeeid,tab1.itemid,tab1.description,tab1.CostCenter UNION SELECT tab1.Employeeid,'Net Pay' as description,sum(changedamount) as Amount,tab1.CostCenter FROM ( select smd.*,sm.Employeeid,(CC.[DESCRIPTION])as CostCenter from salarymonthlydetail smd,salarymonthly sm ,CRG CC,SALARYEMPCOSTCENTER empcc where salarymonth=%d AND sm.salarymonthlyid=smd.salarymonthlyid AND sm.EMPLOYEEID IN(%q) AND sm.branchid is NULL AND CC.CrgID=empcc.CostCenterID AND sm.EmployeeID=empcc.EmployeeID AND smd.SALARYMONTHLYID=empcc.SALARYMONTHLYID )tab1 where tab1.itemgroup=5 AND tab1.ITEMCODE=-132 AND tab1.ITEMID=-132 group by tab1.Employeeid,tab1.itemid,tab1.description,tab1.CostCenter ) a JOIN Employee l ON l.Employeeid = a.Employeeid join Location loc ON l.locationid=loc.locationid order by Employeeid", dSalDate, sEmpIDs, dSalDate, sEmpIDs, dSalDate, sEmpIDs,dSalDate, sEmpIDs, dSalDate, sEmpIDs); FinallSet = tc.ExecuteDataSet(sql); return FinallSet; } #endregion #region Delete function internal static void Delete(TransactionContext tc, ID nID) { tc.ExecuteNonQuery("UPDATE [Employee] SET CreatedBy=%n,ModifiedBy=%n Where EmployeeID=%n", Payroll.BO.User.CurrentUser.ID.Integer, Payroll.BO.User.CurrentUser.ID.Integer, nID.Integer); tc.ExecuteNonQuery("DELETE FROM [Employee] WHERE EmployeeID=%n", nID); } internal static void DeleteAll(TransactionContext tc) { tc.ExecuteNonQuery("UPDATE [Employee] SET CreatedBy=%n,ModifiedBy=%n", Payroll.BO.User.CurrentUser.ID.Integer, Payroll.BO.User.CurrentUser.ID.Integer); tc.ExecuteNonQuery("DELETE FROM [Employee]"); } #endregion internal static DataSet GetByMonthStartMonthEndForContinueFromDiscontinue(TransactionContext tc, DateTime startmonth, DateTime endmonth, int payrollTypeID) { // string sql = SQLParser.MakeSQL(@"select (Select Name From Employee Where Employee.EMPLOYEEID = TargetData.EmployeeId) As EmployeeName, // (Select EmployeeNo From Employee Where Employee.EMPLOYEEID = TargetData.EmployeeId) As EmployeeNo, // TargetData.EffectDate as ContinueDate, // (Select LOGINID From Users Where Users.UserID = TargetData.CreatedBy) as UserIdWhoChangedIt, // TargetData.creationDate as ChangedDate // from // (select * from employeehistory where (creationDate >= %d And creationDate <= %d) AND (employeehistory.EMPLOYEEID in // (select Em.EMPLOYEEID from Employee As Em Where Em.EMPLOYEEID = employeehistory.EMPLOYEEID And // Em.STATUS = 1 And Em.MonthStatusUpdate = 1))) as TargetData // ", startmonth, endmonth); string sql = SQLParser.MakeSQL("Select Emp.Employeeid,eh.emplifecycleid,Emp.EMPLOYEENO,Emp.NAME EmployeeName,eh.effectdate ContinueDate," + " US.LoginID as UserIdWhoChangedIt,EH.CreationDate ChangedDate" + " from EmpLifeCycle as EH,EMPLOYEE as Emp ,USERS as US" + " WHERE US.USERID=EH.CreatedBy AND " + " eh.effectdate between %d and %d AND eh.iscontinue=1 AND" + " Emp.EMPLOYEEID=EH.EMPLOYEEID AND Emp.PayrollTypeID=%n AND eh.EmployeeID IN (SELECT EMPLOYEEID FROM EmpLifeCycle elc WHERE elc.IsDiscontinue=1 AND elc.EffectDate<%d) order by Emp.EMPLOYEENO", startmonth, endmonth, payrollTypeID, startmonth); return tc.ExecuteDataSet(sql); } internal static DataSet GetITAndBasic(TransactionContext tc, DateTime startmonth, DateTime endmonth) { string sql = SQLParser.MakeSQL(@"select (Select Employee.Name From Employee Where Employee.EmployeeID = TEmp.empId) as Name, (Select Employee.EMPLOYEENO From Employee Where Employee.EmployeeID = TEmp.empId) as EmpNo, (select DESIGNATION.NAME from DESIGNATION Where Designation.DESIGNATIONID in (select Employee.DESIGNATIONID From Employee Where Employee.EMPLOYEEID = Temp.empId)) as Designation, (select Grades.DESCRIPTION from Grades Where Grades.GRADEID in (select Employee.GRADEID From Employee Where Employee.EMPLOYEEID = Temp.empId)) as Grade, (select Employee.GENDER From Employee Where Employee.EMPLOYEEID = Temp.empId) as Gander, TEmp.Amount From (select EMPLOYEEID as empId, CHANGEDAMOUNT as Amount from SALARYMONTHLY ,SALARYMONTHLYDETAIL where SALARYMONTHLY.SALARYMONTHLYID =SALARYMONTHLYDETAIL.SALARYMONTHLYID and SALARYMONTHLY.salarymonth >= %d And SALARYMONTHLY.salarymonth <= %d and SALARYMONTHLYDETAIL.ITEMCODE =-129 and SALARYMONTHLY.PAYROLLTYPEID =1) as Temp", startmonth, endmonth); return tc.ExecuteDataSet(sql); } internal static DataSet GetEmpBasicInfoForPSlip(TransactionContext tc, string empIDs) { string sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO, e.Name, SM.BASICSALARY ActualBasic, e.ACCOUNTNO, e.TINNO, e.JOININGDATE, d.DESCRIPTION Department, desig.NAME Designation, g.CODE Grade, b.NAME BName FROM EMPLOYEE e INNER JOIN DEPARTMENT d ON d.DEPARTMENTID=e.DEPARTMENTID INNER JOIN DESIGNATION desig ON desig.DESIGNATIONID=e.DESIGNATIONID INNER JOIN GRADES g ON g.GRADEID=e.GRADEID INNER JOIN BRANCHES br ON br.BRANCHID=e.BRANCHID INNER JOIN BANKS b on b.BANKID=br.BANKID WHERE e.EMPLOYEEID IN (%q)", empIDs); return tc.ExecuteDataSet(sql); } internal static DataSet GetEmpBasicInfoForPSlip(TransactionContext tc, string empIDs, DateTime dMonth) { string sql = ""; if (dMonth > GlobalFunctions.LastDateOfMonth(new DateTime(2016, 8, 1))) { sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO, e.Name, SM.thismonthbasic ActualBasic, sm.ACCOUNTNO, e.TINNO, e.JOININGDATE, d.DESCRIPTION Department, desig.NAME Designation, g.CODE Grade, b.NAME BName,e.payrolltypeid Category FROM EMPLOYEE e INNER JOIN SalaryMOnthly sm ON sm.Employeeid=e.Employeeid INNER JOIN DEPARTMENT d ON d.DEPARTMENTID=sm.DEPARTMENTID INNER JOIN DESIGNATION desig ON desig.DESIGNATIONID=sm.DESIGNATIONID INNER JOIN GRADES g ON g.GRADEID=sm.GRADEID left outer JOIN BRANCHES br ON br.BRANCHID=sm.BRANCHID left outer JOIN BANKS b on b.BANKID=br.BANKID WHERE e.EMPLOYEEID IN (%q) and salarymonth=%d", empIDs, GlobalFunctions.LastDateOfMonth(dMonth)); } else { sql = SQLParser.MakeSQL(@"SELECT e.EMPLOYEENO, e.Name, SM.thismonthbasic ActualBasic, e.ACCOUNTNO, e.TINNO, e.JOININGDATE, d.DESCRIPTION Department, desig.NAME Designation, g.CODE Grade, b.NAME BName,e.payrolltypeid Category FROM gp_EMPLOYEE e INNER JOIN gp_SalaryMOnthly sm ON sm.Employeeid=e.Employeeid INNER JOIN gp_DEPARTMENT d ON d.DEPARTMENTID=sm.DEPARTMENTID INNER JOIN gp_DESIGNATION desig ON desig.DESIGNATIONID=sm.DESIGNATIONID INNER JOIN gp_GRADES g ON g.GRADEID=sm.GRADEID INNER JOIN gp_BRANCHES br ON br.BRANCHID=sm.BRANCHID INNER JOIN gp_BANKS b on b.BANKID=br.BANKID WHERE e.EMPLOYEEID IN (%q) and monthyeardate=%d", empIDs, GlobalFunctions.LastDateOfMonth(dMonth)); } return tc.ExecuteDataSet(sql); } internal static IDataReader GetAllEmps(TransactionContext tc) { return tc.ExecuteReader("Select * From Employee"); } internal static IDataReader GetJoiningData(TransactionContext tc, DateTime formDate) { string sSQL = SQLParser.MakeSQL("Select * From Employee where JOININGDATE between %d and %d order by EMPLOYEEID", GlobalFunctions.FirstDateOfMonth(formDate), GlobalFunctions.LastDateOfMonth(formDate)); return tc.ExecuteReader(sSQL); } internal static IDataReader Get4Web(TransactionContext tc, string employeeNo) { return tc.ExecuteReader("SELECT * FROM Employee WHERE employeeNo=%s", employeeNo); } internal static IDataReader GetDepartmentHead(TransactionContext tc, EnumOGPositionType positionType, ID DepartmentID) { return tc.ExecuteReader(@"select * from OrganEmployee, Employee where OrganEmployee.EmployeeID= Employee.EmployeeID and NodeID IN (select organogramID from organogram where departmentid=%n and organogramid in ( select nodeid from OrganPosition where positionTypeid=%n ))", DepartmentID.Integer, positionType); } internal static IDataReader GetNew(TransactionContext tc, DateTime dt, int payrollTypeID) { string sql = SQLParser.MakeSQL("SELECT * FROM Employee WHERE Status=%n and PAYROLLTYPEID = %n AND joiningdate<=%d order by EmployeeNo", EnumEmployeeStatus.Live, payrollTypeID, dt); return tc.ExecuteReader(sql); } internal static DataSet GetGradeWiseEmployeeCounts(TransactionContext tc) { DataSet designationWiseCountDS = new DataSet(); try { designationWiseCountDS = tc.ExecuteDataSet(@"Select '['+gd.Code+']-'+gd.[Description] GradeOrDesignation,empC.[Count] From ( Select GradeID,COUNT(*) [Count] From Employee Where [Status] = %n Group by GradeID )empC ,Grades gd where empC.GradeID = gd.GradeID", (int)EnumEmployeeStatus.Live); } catch (Exception ex) { throw new Exception(ex.Message); } return designationWiseCountDS; } internal static DataSet GetRCAssignedEmp(TransactionContext tc) { string sql = SQLParser.MakeSQL(@"SELECT DISTINCT Level1 DepartmentCode,Level2 DepartmentName,rc RCCode FROM EmpCurrentPosition WHERE Level2<>'' AND rc<>'' ORDER BY Level1,Level2"); return tc.ExecuteDataSet(sql); } internal static DataSet GetRCNotAssignedEmp(TransactionContext tc) { string sql = SQLParser.MakeSQL(@"SELECT DISTINCT Level1 DepartmentCode,Level2 DepartmentName,rc RCCode FROM EmpCurrentPosition WHERE Level2<>'' AND rc='' ORDER BY Level1,Level2"); return tc.ExecuteDataSet(sql); } internal static DataSet GetUploadedComponents(TransactionContext tc, DateTime firstDate, DateTime lastDate) { string sql = string.Empty; if (firstDate == DateTime.MinValue || lastDate == DateTime.MinValue) { sql = SQLParser.MakeSQL(@"SELECT UploadedComponentid,convert(nvarchar(20), uc.SalaryMonth, 106)SalaryMonth, (SELECT DISTINCT DESCRIPTION FROM COMPONENTUPLOADSETUP cc WHERE cc.ITEMID=uc.ComponentID) Component, (SELECT Employeeno +' ( '+name+' )' FROM EMPLOYEE WHERE EMPLOYEEID=uc.UploadedBy)UploadedBy,convert(nvarchar(20), uc.UploadedDate, 106)UploadedDate, (SELECT Employeeno +' ( '+name+' )' FROM EMPLOYEE WHERE EMPLOYEEID=uc.ApprovedBy)ApprovedBy,convert(nvarchar(20), uc.ApprovedDate, 106)ApprovedDate FROM UploadedComponent uc ORDER BY uc.SalaryMonth"); } else { sql = SQLParser.MakeSQL(@"SELECT UploadedComponentid,convert(nvarchar(20), uc.SalaryMonth, 106)SalaryMonth,(SELECT DISTINCT DESCRIPTION FROM COMPONENTUPLOADSETUP cc WHERE cc.ITEMID=uc.ComponentID) Component, (SELECT Employeeno +' ( '+name+' )' FROM EMPLOYEE WHERE EMPLOYEEID=uc.UploadedBy)UploadedBy,convert(nvarchar(20), uc.UploadedDate, 106)UploadedDate, (SELECT Employeeno +' ( '+name+' )' FROM EMPLOYEE WHERE EMPLOYEEID=uc.ApprovedBy)ApprovedBy,convert(nvarchar(20), uc.ApprovedDate, 106)ApprovedDate FROM UploadedComponent uc WHERE uc.SalaryMonth BETWEEN %d AND %d ORDER BY uc.SalaryMonth",firstDate, lastDate); } return tc.ExecuteDataSet(sql); } internal static DataSet GetUploadedComponentDetails(TransactionContext tc, int uploadedComponentId) { string sql = SQLParser.MakeSQL(@"SELECT Emp.EMPLOYEENO EmployeeID, emp.NAME,Amount FROM UploadedComponentEmp ue,Employee emp WHERE ue.EmployeeID=emp.EMPLOYEEID AND ue.UploadedComponentID=%n",uploadedComponentId); return tc.ExecuteDataSet(sql); } } #endregion }