internal List <AspNetUsersEntity> GetAspNetUsersListByFilter(string filter) { List <AspNetUsersEntity> lstEntity = null; string sqlString = string.Empty; switch (filter) { case "": sqlString = @"SELECT U.Id, U.UserName, U.PasswordHash, U.SecurityStamp, U.Discriminator, U.EmailID, U.MobileNumber, U.UserImage,U.BranchCode, B.BranchName FROM AspNetUsers U inner join Branch B on B.BranchCode=U.BranchCode" ; break; default: sqlString = string.Format(@"SELECT U.Id, U.UserName, U.PasswordHash, U.SecurityStamp, U.Discriminator, U.EmailID, U.MobileNumber, U.UserImage,U.BranchCode, B.BranchName FROM AspNetUsers U inner join Branch B on B.BranchCode=U.BranchCode where {0} ORDER BY U.UserName " , filter); break; } SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, CommandType.Text, sqlString); lstEntity = ObjectMapHelper <AspNetUsersEntity> .MapObject(reader); return(lstEntity); }
public List <UserAccessPageEntity> GetUserAccessPageListByFilter(string filter) { List <UserAccessPageEntity> lstEntity = null; string sqlString = string.Empty; switch (filter) { case "": sqlString = @"SELECT UAP.ID, UAP.UserName, UAP.ManuID, UAP.[Status],N.NameOption FROM UserAccessPage UAP INNER JOIN Navbar N ON N.ID=UAP.ManuID"; break; default: sqlString = string.Format(@"SELECT UAP.ID, UAP.UserName, UAP.ManuID, UAP.[Status],N.NameOption FROM UserAccessPage UAP INNER JOIN Navbar N ON N.ID=UAP.ManuID WHERE {0} ", filter); break; } SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, CommandType.Text, sqlString); lstEntity = ObjectMapHelper <UserAccessPageEntity> .MapObject(reader); return(lstEntity); }
internal List <NavbarEntity> GetNavbarListByFilter(string filter) { List <NavbarEntity> lstEntity = null; string sqlString = string.Empty; switch (filter) { case "": sqlString = @"SELECT DISTINCT N.* FROM UserAccessPage UAP INNER JOIN Navbar N ON N.ID=UAP.ManuID order by Displayorder"; break; default: sqlString = string.Format(@"SELECT DISTINCT N.* FROM UserAccessPage UAP INNER JOIN Navbar N ON N.ID=UAP.ManuID WHERE {0} order by Displayorder ", filter); break; } SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, CommandType.Text, sqlString); lstEntity = ObjectMapHelper <NavbarEntity> .MapObject(reader); return(lstEntity); }
public List <ProcessEmpSalaryStructureEntity> GetEmpYearlyTaxInfo(string filter) { List <ProcessEmpSalaryStructureEntity> lstEntity = null; string sqlString = string.Empty; switch (filter) { case "": sqlString = @"select E.EmpCode,E.EmpName,E.Department,E.Designation,PES.PeriodName,T.TaxYearName,SY.YearName,PES.Amount from ProcessEmpSalaryStructure PES INNER JOIN EmployeeInfo E ON E.EmpCode=PES.EmpCode INNER JOIN TaxYearInfo T ON T.ID=PES.TaxYearID INNER JOIN SalaryYear SY ON SY.ID=PES.YearID"; break; default: sqlString = string.Format(@"select E.EmpCode,E.EmpName,E.Department,E.Designation,PES.PeriodName,T.TaxYearName,SY.YearName,PES.Amount from ProcessEmpSalaryStructure PES INNER JOIN EmployeeInfo E ON E.EmpCode=PES.EmpCode INNER JOIN TaxYearInfo T ON T.ID=PES.TaxYearID INNER JOIN SalaryYear SY ON SY.ID=PES.YearID WHERE {0} ", filter); break; } SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, CommandType.Text, sqlString); lstEntity = ObjectMapHelper <ProcessEmpSalaryStructureEntity> .MapObject(reader); return(lstEntity); }
public List <ProvidentFundDetailsEntity> GetProvidentFundDetails(string EmpCode, int FromPeriodID, int ToPeriodID) { List <ProvidentFundDetailsEntity> lstEntity = null; SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, "spPFDetails", new object[] { EmpCode, FromPeriodID, ToPeriodID }); lstEntity = ObjectMapHelper <ProvidentFundDetailsEntity> .MapObject(reader); return(lstEntity); }
internal List <EmployeeInfoEntity> SaveEmployeeImageInDataBase(string EmpCode) { List <EmployeeInfoEntity> lstEntity = null; string sqlString = string.Empty; SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, "UpdateImage", new object[] { EmpCode }); lstEntity = ObjectMapHelper <EmployeeInfoEntity> .MapObject(reader); return(lstEntity); }
internal List <EmployeeInfoEntity> GetEmployeeConfirmationInfo( ) { List <EmployeeInfoEntity> lstEntity = null; string sqlString = string.Empty; SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, "spConfirmationDueList", new object[] { }); lstEntity = ObjectMapHelper <EmployeeInfoEntity> .MapObject(reader); return(lstEntity); }
internal List <EmployeeInfoEntity> GetEmployeeAutoNumber(string filter) { List <EmployeeInfoEntity> lstEntity = null; string sqlString = string.Empty; SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, "spEmployeeAutoNumber", new object[] { filter }); lstEntity = ObjectMapHelper <EmployeeInfoEntity> .MapObject(reader); return(lstEntity); }
internal List <SalaryFixationEntity> CalculateSalaryFix(string Grade, decimal Basic, int NumOfChild, int LocationID) { List <SalaryFixationEntity> lstEntity = null; string sqlString = string.Empty; SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, "spSalaryFixation", new object[] { Grade, Basic, NumOfChild, LocationID }); lstEntity = ObjectMapHelper <SalaryFixationEntity> .MapObject(reader); return(lstEntity); }
public List <SalaryFixationEntity> GetSalaryFixationList(string filter) { List <SalaryFixationEntity> lstEntity = null; string sqlString = string.Empty; switch (filter) { case "": //This query should be changed after createing CarLoanPayment table sqlString = @"Select s.ID, EmpCode, EmpName,FileNo,FatherName,MotherName,Address, NID,TINNo, MobNo, Designation, Gender, Convert(nvarchar(12),Cast(JoiningDate as Date), 103)JoiningDate, Convert(nvarchar(12),Cast(BirthDate as Date), 103) BirthDate, case when LastIncreDate is null then LastIncreDate when LastIncreDate='0' then LastIncreDate when LastIncreDate='' then LastIncreDate else Convert(nvarchar(12),Cast(LastIncreDate as Date), 103) end LastIncreDate, Convert(nvarchar(12),Cast(LastPromotionDate as Date), 103) LastPromotionDate, Convert(nvarchar(12),Cast(DateOfPresentScale as Date), 103) DateOfPresentScale, CurrentGrade, NumberOfChild, s.Basic, CurBasic, LastIncreDate, PostingPlace, NewBasic, HouseRent, Medical, ChildEduAllow, DA, GrossSalary, isApproved,Case When isApproved=0 Then 'Draft' When isApproved=1 Then 'Processing' When isApproved=2 Then 'Approved' End Status, EntryBy, EntryDate, ApprovedBy, AppovalDate, BasicASONJune2015, InitialBasic, Increment, StartingBasic, NewBasicPoint, (NewBasic-BasicASONJune2015)BasicIncrement,(BasicASONJune2015+PersonalPay)Total34,(BasicASONJune2015+PersonalPay-InitialBasic)Total56,(BasicASONJune2015+PersonalPay+StartingBasic-InitialBasic)Total78,GradeDes2009,GradeDes2015,(select top 1 PayScale2009 from StructureType where StructureName=CurrentGrade)PayScale2009,(select top 1 PayScale2015 from StructureType where StructureName=CurrentGrade)PayScale2015,NewBasicConDec15Basic FROM SalaryFixation S left join Branch B on b.BranchCode =s.BranchCode " ; break; default: sqlString = string.Format(@" Select s.ID, EmpCode, EmpName,FileNo,FatherName,MotherName,Address, NID,TINNo, MobNo, Designation, Gender, Convert(nvarchar(12),Cast(JoiningDate as Date), 103)JoiningDate, Convert(nvarchar(12),Cast(BirthDate as Date), 103) BirthDate, case when LastIncreDate is null then LastIncreDate when LastIncreDate='0' then LastIncreDate when LastIncreDate='' then LastIncreDate else Convert(nvarchar(12),Cast(LastIncreDate as Date), 103) end LastIncreDate, Convert(nvarchar(12),Cast(LastPromotionDate as Date), 103) LastPromotionDate, Convert(nvarchar(12),Cast(DateOfPresentScale as Date), 103) DateOfPresentScale, CurrentGrade, NumberOfChild, s.Basic, CurBasic, LastIncreDate, PostingPlace, NewBasic, HouseRent, Medical, ChildEduAllow, DA, GrossSalary, isApproved,Case When isApproved=0 Then 'Draft' When isApproved=1 Then 'Processing' When isApproved=2 Then 'Approved' End Status, EntryBy, EntryDate, ApprovedBy, AppovalDate, BasicASONJune2015, InitialBasic, Increment, StartingBasic, NewBasicPoint, (NewBasic-BasicASONJune2015)BasicIncrement,(BasicASONJune2015+PersonalPay)Total34,(BasicASONJune2015+PersonalPay-InitialBasic)Total56,(BasicASONJune2015+PersonalPay+StartingBasic-InitialBasic)Total78,GradeDes2009,GradeDes2015,(select top 1 PayScale2009 from StructureType where StructureName=CurrentGrade)PayScale2009,(select top 1 PayScale2015 from StructureType where StructureName=CurrentGrade)PayScale2015,NewBasicConDec15Basic FROM SalaryFixation S WHERE {0} " , filter); break; } SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, CommandType.Text, sqlString); lstEntity = ObjectMapHelper <SalaryFixationEntity> .MapObject(reader); return(lstEntity); }
internal List <EmployeeInfoEntity> GetEmployeeInfoForEdit(string filter) { List <EmployeeInfoEntity> lstEntity = null; string sqlString = string.Empty; switch (filter) { case "": sqlString = @"SELECT ID, EmpCode,saveType,refCont1,refCont2,RefEmail1,RefEmail2, Prefix,NomShare1,NomShare2,NomShare3,NomShare4,NomRel1,NomRel2,NomRel3,NomRel4,NomCont1,NomCont2,NomCont3,NomCont4, EmpName, FirstName, MiddleName, LastName, Designation, JobGrade,EmpImage, Division, Department, OfficeBranch, DutyStation, Location, Convert(varchar(12),cast(JoiningDate as DATE),103)JoiningDate, Convert(varchar(12),cast(ConfirmationDate as DATE),103)ConfirmationDate, ContractPeriod, CONVERT(NVarchar(12),CAST( LastContractDay as DATE),103)LastContractDay, CONVERT(NVarchar(12),CAST( ResignationDay as DATE),103)ResignationDay,CONVERT(NVarchar(12),CAST( ResignDate as DATE),103)ResignDate, CONVERT(NVarchar(12),CAST( ReleaseDate as DATE),103)ReleaseDate, SupervisorID1, SuperName1, SupervisorDesignation1,SupervisorID2, SuperName2, SupervisorDesignation2, ReviewerID, ReviewerName, ReviewerDesignation, EmploymentStatus, EmploymentType, Mobile, PABX, EmailID, UserID, FatherName, MotherName, Gender, Religion, CONVERT(nvarchar(12),cast( BirthDate as DATE),103)BirthDate, BloodGroup, MaritalStatus, Spouse, SpouseDOB, Child1, Child1DOB, Child2, Child2DOB, Child3, Child3DOB, PresentAddress, PermanentAddress, JurisdictionDiv, HighestEducation, AcademicSubject, Institution, LastOrganization, PositionHeld, LJReleaseDate, PreExperience, AccNo, WalletNo, TinNo, Bank, BankBranch, Resident, PayBy, ProvidentFund, PFDeductAmt, PFDeductRule, [Status],_Group, StatusDate, NationalID, [Type], IncomeTex, TaxDeductAmt, IsBlock, ChangeDate, IsCompanyCar, AreaTypeId, WC_GrpLeaderId, WC_DayWagesRate, PunchCardNo, SalaryFundSource, SalaryPayFromAccount, MobileAllo,FuelAllo,CarManAllo,DriverAllo, MiscAllo,OtherAllo,RelocationExpense,AirTicket,TemporaryAccomodation, WalletPayfromAccount, HaveDailyAllowance, HaveMobileAllowance, DailyAllowance, MobileAllowance, ConfirmationText, AptCode, ImgFileName,nominee1,nominee2,nominee3,nominee4,FatherCont,MotherCont,RefName1 ,RefDesig1 , RefORG1 ,RefEmail1 ,RefName2 ,RefDesig2 ,RefORG2 ,RefCont2 , RefEmail2 ,NomGratuity1,NomGratuity2,NomGratuity3,NomGratuity4 FROM EmployeeInfo"; break; default: sqlString = string.Format(@"SELECT ID, EmpCode,saveType,refCont1,refCont2,RefEmail1,RefEmail2, Prefix,NomShare1,NomShare2,NomShare3,NomShare4,NomRel1,NomRel2,NomRel3,NomRel4,NomCont1,NomCont2,NomCont3,NomCont4, EmpName, FirstName, MiddleName, LastName, Designation, JobGrade,EmpImage, Division, Department, OfficeBranch, DutyStation, Location, Convert(varchar(12),cast(JoiningDate as DATE),103)JoiningDate, Convert(varchar(12),cast(ConfirmationDate as DATE),103)ConfirmationDate, ContractPeriod, CONVERT(NVarchar(12),CAST( LastContractDay as DATE),103)LastContractDay, CONVERT(NVarchar(12),CAST( ResignationDay as DATE),103)ResignationDay,CONVERT(NVarchar(12),CAST( ResignDate as DATE),103)ResignDate, CONVERT(NVarchar(12),CAST( ReleaseDate as DATE),103)ReleaseDate, SupervisorID1, SuperName1, SupervisorDesignation1,SupervisorID2, SuperName2, SupervisorDesignation2, ReviewerID, ReviewerName, ReviewerDesignation, EmploymentStatus, EmploymentType, Mobile, PABX, EmailID, UserID, FatherName, MotherName, Gender, Religion, CONVERT(nvarchar(12),cast( BirthDate as DATE),103)BirthDate, BloodGroup, MaritalStatus, Spouse, SpouseDOB, Child1, Child1DOB, Child2, Child2DOB, Child3, Child3DOB, PresentAddress, PermanentAddress, JurisdictionDiv, HighestEducation, AcademicSubject, Institution, LastOrganization, PositionHeld, LJReleaseDate, PreExperience, AccNo, WalletNo, TinNo, Bank, BankBranch, Resident, PayBy, ProvidentFund, PFDeductAmt, PFDeductRule, [Status],_Group, StatusDate, NationalID, [Type], IncomeTex, TaxDeductAmt, IsBlock, ChangeDate, IsCompanyCar, AreaTypeId, WC_GrpLeaderId, WC_DayWagesRate, PunchCardNo, SalaryFundSource, SalaryPayFromAccount, MobileAllo,FuelAllo,CarManAllo,DriverAllo, MiscAllo,OtherAllo,RelocationExpense,AirTicket,TemporaryAccomodation, WalletPayfromAccount, HaveDailyAllowance, HaveMobileAllowance, DailyAllowance, MobileAllowance, ConfirmationText, AptCode, ImgFileName,nominee1,nominee2,nominee3,nominee4,FatherCont,MotherCont,RefName1 ,RefDesig1 , RefORG1 ,RefEmail1 ,RefName2 ,RefDesig2 ,RefORG2 ,RefCont2 , RefEmail2 ,NomGratuity1,NomGratuity2,NomGratuity3,NomGratuity4 FROM EmployeeInfo WHERE {0} ", filter); break; } SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, CommandType.Text, sqlString); lstEntity = ObjectMapHelper <EmployeeInfoEntity> .MapObject(reader); return(lstEntity); }
internal List <EmployeeInfoEntity> GetEmployeeProfileStrengthInfo(string EmpCode) { List <EmployeeInfoEntity> lstEntity = null; try { SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, "ProfileStrengthCount", new object[] { EmpCode }); lstEntity = ObjectMapHelper <EmployeeInfoEntity> .MapObject(reader); } catch (Exception ex) { throw ex; } return(lstEntity); }
internal List <EmployeeInfoEntity> DuplicateDataCheck(string AccNo) { List <EmployeeInfoEntity> lstEntity = null; try { SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, "CheckDuplicateData", new object[] { AccNo }); lstEntity = ObjectMapHelper <EmployeeInfoEntity> .MapObject(reader); } catch (Exception ex) { throw ex; } return(lstEntity); }
internal List <NavbarEntity> GetNavbarListForAdminuser(string filter) { List <NavbarEntity> lstEntity = null; string sqlString = string.Empty; switch (filter) { case "": sqlString = @"SELECT * FROM Navbar order by Displayorder "; break; default: sqlString = string.Format(@"SELECT * FROM Navbar WHERE {0} order by Displayorder ", filter); break; } SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, CommandType.Text, sqlString); lstEntity = ObjectMapHelper <NavbarEntity> .MapObject(reader); return(lstEntity); }
public List <EmployeeInfoEntity> GetList(string filter) { List <EmployeeInfoEntity> lstEntity = null; string sqlString = string.Empty; switch (filter) { case "": sqlString = "SELECT * FROM EmployeeInfo ORDER BY EmpCode ASC "; break; default: sqlString = string.Format("SELECT * FROM EmployeeInfo WHERE {0} ORDER BY EmpCode ASC ", filter); break; } SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, CommandType.Text, sqlString); lstEntity = ObjectMapHelper <EmployeeInfoEntity> .MapObject(reader); return(lstEntity); }
internal List <OfficeBranchEntity> GetBranchNameListForDropdown(string filter) { List <OfficeBranchEntity> lstEntity = null; string sqlString = string.Empty; switch (filter) { case "": sqlString = "select ID,BranchCode,BranchName from Branch order by BranchName"; break; default: sqlString = string.Format("select ID,BranchCode,BranchName from Branch WHERE {0} order by BranchName ", filter); break; } SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, CommandType.Text, sqlString); lstEntity = ObjectMapHelper <OfficeBranchEntity> .MapObject(reader); return(lstEntity); }
internal List <OfficeBranchEntity> GetBranchListByFilter(string filter) { List <OfficeBranchEntity> lstEntity = null; string sqlString = string.Empty; switch (filter) { case "": sqlString = "SELECT * FROM OfficeBranch"; break; default: sqlString = string.Format("SELECT * FROM OfficeBranch WHERE {0} ", filter); break; } SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, CommandType.Text, sqlString); lstEntity = ObjectMapHelper <OfficeBranchEntity> .MapObject(reader); return(lstEntity); }
internal List <UserRegistrationEntity> LoggedIn(string filter) { List <UserRegistrationEntity> lstEntity = null; string sqlString = string.Empty; switch (filter) { case "": sqlString = "select EmailID,Password from UserRegistration"; break; default: sqlString = string.Format("select EmailID,Password from UserRegistration WHERE {0} ", filter); break; } SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, CommandType.Text, sqlString); lstEntity = ObjectMapHelper <UserRegistrationEntity> .MapObject(reader); return(lstEntity); }
internal List <EmployeeInfoEntity> GetEmployeeInfoListByFilter(string filter) { List <EmployeeInfoEntity> lstEntity = null; string sqlString = string.Empty; switch (filter) { case "": sqlString = "SELECT CONVERT(varchar(12),cast(JoiningDate as DATE),103)JoiningDate,dbo.ProfileStrengthCount(EmpCode)Strength, * FROM EmployeeInfo order by EmpCode"; break; default: sqlString = string.Format("SELECT CONVERT(varchar(12),cast(JoiningDate as DATE),103)JoiningDate,dbo.ProfileStrengthCount(EmpCode)Strength, * FROM EmployeeInfo WHERE {0} order by EmpCode ", filter); break; } SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, CommandType.Text, sqlString); lstEntity = ObjectMapHelper <EmployeeInfoEntity> .MapObject(reader); return(lstEntity); }
internal List <NavbarEntity> GetUsernameForDropDownList(string filter) { List <NavbarEntity> lstEntity = null; string sqlString = string.Empty; switch (filter) { case "": sqlString = @"SELECT UserName FROM AspNetUsers"; break; default: sqlString = string.Format(@"SELECT UserName FROM AspNetUsers WHERE {0} ", filter); break; } SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, CommandType.Text, sqlString); lstEntity = ObjectMapHelper <NavbarEntity> .MapObject(reader); return(lstEntity); }
public List <TaxYearInfoEntity> GetTaxYearInfoListByFilter(string filter) { List <TaxYearInfoEntity> lstEntity = null; string sqlString = string.Empty; switch (filter) { case "": sqlString = "SELECT ID,TaxYearName FROM TaxYearInfo"; break; default: sqlString = string.Format("SELECT ID,TaxYearName FROM TaxYearInfo WHERE {0} ", filter); break; } SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, CommandType.Text, sqlString); lstEntity = ObjectMapHelper <TaxYearInfoEntity> .MapObject(reader); return(lstEntity); }
internal List <SalaryPeriodEntity> GetSalaryPeriodForDropDown(string filter) { List <SalaryPeriodEntity> lstEntity = null; string sqlString = string.Empty; switch (filter) { case "": sqlString = @"SELECT ID, PeriodName FROM dbo.SalaryPeriod"; break; default: sqlString = string.Format(@"SELECT ID, PeriodName FROM dbo.SalaryPeriod WHERE {0} ", filter); break; } SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, CommandType.Text, sqlString); lstEntity = ObjectMapHelper <SalaryPeriodEntity> .MapObject(reader); return(lstEntity); }
public List <StructureTypeEntity> GetStructureTypeListByFilter(string filter) { List <StructureTypeEntity> lstEntity = null; string sqlString = string.Empty; switch (filter) { case "": sqlString = @"Select ID, StructureName,PayScale2009 FROM StructureType order by cast(SUBSTRING( StructureName,7,3)as int) "; break; default: sqlString = string.Format(@"Select ID, StructureName,PayScale2009 FROM StructureType WHERE {0} order by cast(SUBSTRING( StructureName,7,3)as int) ", filter); break; } SqlDataReader reader = SqlHelper.ExecuteReader(Constants.ConnectionString, CommandType.Text, sqlString); lstEntity = ObjectMapHelper <StructureTypeEntity> .MapObject(reader); return(lstEntity); }