public decimal getLeaveBalance(int employeeId)
{
try
{
decimal leaveBalance = 0;
String SqlQuery = "SELECT BalanceLeaves from EmployeeLeaveTransaction where EmployeeId=" + employeeId + ";";
DataTable dataTable = CommanMethodsForSQL.GetDataTable(SqlQuery);
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
leaveBalance = Convert.ToDecimal(dr["BalanceLeaves"]);
}
}
}
return(leaveBalance);
}
catch (Exception ex)
{
throw;
}
}
public IQueryable <Country> GetAll()
{
try
{
List <Country> entites = new List <Country>();
DataTable dataTable = CommanMethodsForSQL.GetDataTable("SELECT CountryDetails.CountryId, RTRIM(LTRIM(CountryDetails.Country)) AS Country, CountryDetails.[Status], CountryDetails.Remark "
+ " FROM CountryDetails ;");
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
Country entity = new Country();
entity.CountryId = Convert.ToInt32(dr["CountryId"]);
entity.CountryName = dr["Country"].ToString();
entity.Status = Convert.ToBoolean(dr["Status"].ToString());
entity.Remark = dr["Remark"].ToString();
entites.Add(entity);
}
}
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
public IQueryable <AcademicYear> GetAll()
{
try
{
List <AcademicYear> entites = new List <AcademicYear>();
DataTable dataTable = CommanMethodsForSQL.GetDataTable("SELECT AcademicYearId, LTRIM(RTRIM(AcademicYear)) AS AcademicYear, StartDate, EndDate, Status, Remark "
+ " FROM AcademicYearDetails;");
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
AcademicYear entity = new AcademicYear();
entity.AcademicYearId = Convert.ToInt32(dr["AcademicYearId"]);
entity.AcademicYearName = dr["AcademicYear"].ToString();
entity.StartDate = Convert.ToDateTime(dr["StartDate"]);
entity.EndDate = Convert.ToDateTime(dr["EndDate"]);
entity.Status = Convert.ToBoolean(dr["Status"].ToString());
entity.Remark = dr["Remark"].ToString();
entites.Add(entity);
}
}
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
public IQueryable <Role> GetAll()
{
try
{
List <Role> entites = new List <Role>();
DataTable dataTable = CommanMethodsForSQL.GetDataTable("SELECT RoleDetails.* FROM RoleDetails;");
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
Role entity = new Role();
entity.Id = Convert.ToInt32(dr["RoleId"]);
entity.RoleName = dr["Role"].ToString();
entity.Status = Convert.ToBoolean(dr["Status"].ToString());
entity.Remark = dr["Remark"].ToString();
entites.Add(entity);
}
}
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
public IQueryable <Religion> GetAll()
{
try
{
List <Religion> entites = new List <Religion>();
DataTable dataTable = CommanMethodsForSQL.GetDataTable("SELECT ReligionDetails.ReligionId, LTRIM(RTRIM(ReligionDetails.Religion)) AS Religion, "
+ " ReligionDetails.Status, ReligionDetails.Remark"
+ " FROM ReligionDetails;");
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
Religion entity = new Religion();
entity.ReligionId = Convert.ToInt32(dr["ReligionId"]);
entity.ReligionName = Convert.ToString(dr["Religion"]);
entity.Status = Convert.ToBoolean(dr["Status"].ToString());
entity.Remark = dr["Remark"].ToString();
entites.Add(entity);
}
}
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
public IQueryable <State> GetAll()
{
try
{
List <State> entites = new List <State>();
DataTable dataTable = CommanMethodsForSQL.GetDataTable("SELECT StateDetails.StateId, StateDetails.CountryId, RTRIM(LTRIM(StateDetails.[State])) AS [State], StateDetails.[Status], StateDetails.Remark, "
+ " RTRIM(LTRIM(CountryDetails.Country)) AS Country "
+ " FROM StateDetails INNER JOIN "
+ " CountryDetails ON StateDetails.CountryId = CountryDetails.CountryId;");
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
State entity = new State();
entity.StateId = Convert.ToInt32(dr["StateId"]);
entity.CountryId = Convert.ToInt32(dr["CountryId"]);
entity.CountryName = dr["Country"].ToString();
entity.StateName = dr["State"].ToString();
entity.Status = Convert.ToBoolean(dr["Status"].ToString());
entity.Remark = dr["Remark"].ToString();
entites.Add(entity);
}
}
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
public IQueryable <Class> GetAll()
{
try
{
List <Class> entites = new List <Class>();
DataTable dataTable = CommanMethodsForSQL.GetDataTable("SELECT ClassId, ClassDetails.Class, "
+ " ClassDetails.Status, ClassDetails.Remark FROM ClassDetails;");
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
Class entity = new Class();
entity.ClassId = Convert.ToInt32(dr["ClassId"]);
entity.ClassName = dr["Class"].ToString();
entity.Status = Convert.ToBoolean(dr["Status"].ToString());
entity.Remark = dr["Remark"].ToString();
entites.Add(entity);
}
}
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
public IQueryable <Cast> GetAll()
{
try
{
List <Cast> entites = new List <Cast>();
DataTable dataTable = CommanMethodsForSQL.GetDataTable("SELECT CastId, LTRIM(RTRIM([Cast])) as CastName, Status, Remark FROM CastDetails;");
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
Cast entity = new Cast();
entity.CastId = Convert.ToInt32(dr["CastId"]);
entity.CastName = Convert.ToString(dr["CastName"]);
entity.Status = Convert.ToBoolean(dr["Status"].ToString());
entity.Remark = dr["Remark"].ToString();
entites.Add(entity);
}
}
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
public IQueryable <City> GetAll()
{
try
{
List <City> entites = new List <City>();
DataTable dataTable = CommanMethodsForSQL.GetDataTable("SELECT CityDetails.CityId, CityDetails.City, CityDetails.DistrictId, DistrictDetails.District,DistrictDetails.District, CityDetails.Status, CityDetails.Remark"
+ " FROM CityDetails INNER JOIN DistrictDetails ON CityDetails.DistrictId = DistrictDetails.DistrictId;");
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
City entity = new City();
entity.CityId = Convert.ToInt32(dr["CityId"]);
entity.CityName = dr["City"].ToString();
entity.DistrictId = Convert.ToInt32(dr["DistrictId"]);
entity.DistrictName = dr["District"].ToString();
entity.Status = Convert.ToBoolean(dr["Status"].ToString());
entity.Remark = dr["Remark"].ToString();
entites.Add(entity);
}
}
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
public IQueryable <Employee> GetAll(int schoolId)
{
try
{
List <Employee> entites = new List <Employee>();
String SqlQuery = "select * from Employee";
if (schoolId != 0)
{
SqlQuery = SqlQuery + " where SchoolId=" + schoolId + ";";
}
DataTable dataTable = CommanMethodsForSQL.GetDataTable(SqlQuery);
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
Employee entity = new Employee();
entity.EmployeeId = Convert.ToInt32(dr["EmployeeId"]);
entity.EmployeeCode = Convert.ToString(dr["EmployeeCode"]);
entity.Password = Convert.ToString(dr["Password"]);
entity.FirstName = Convert.ToString(dr["FirstName"]);
entity.MiddleName = Convert.ToString(dr["MiddleName"]);
entity.LastName = Convert.ToString(dr["LastName"]);
entity.Category = Convert.ToString(dr["Category"]);
entity.Address = Convert.ToString(dr["Address"]);
entity.ContactNo = Convert.ToString(dr["ContactNo"]);
if (dr["DOB"] != DBNull.Value)
{
entity.DOB = Convert.ToDateTime(dr["DOB"]);
}
if (dr["JoiningDate"] != DBNull.Value)
{
entity.JoiningDate = Convert.ToDateTime(dr["JoiningDate"].ToString());
}
entites.Add(entity);
}
}
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
public IQueryable <HomePage> getClasswiseMaleFemaleList(int schoolId)
{
HomePage entity = new HomePage();
entity.classList = new List <String>();
entity.classwiseBoysList = new List <int>();
entity.classwiseGirlsList = new List <int>();
List <HomePage> entites = new List <HomePage>();
try
{
String SqlQuery = "select class, countMale, CountFemale from view_ClassWiseMaleFemale";
if (schoolId != 0)
{
SqlQuery = SqlQuery + " where SchoolId=" + schoolId;
}
SqlQuery = SqlQuery + " order by LEN(Class), Class;";
DataTable dataTable = CommanMethodsForSQL.GetDataTable(SqlQuery);
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
entity.classList.Add(Convert.ToString((dr["Class"])));
entity.classwiseBoysList.Add(Convert.ToInt32((dr["CountMale"])));
entity.classwiseGirlsList.Add(Convert.ToInt32((dr["CountFemale"])));
entites.Add(entity);
}
}
}
else
{
entity.classList.Add("");
entity.classwiseBoysList.Add(0);
entity.classwiseGirlsList.Add(0);
entites.Add(entity);
}
}
catch (Exception ex)
{
throw ex;
}
return(entites.AsQueryable());
}
public IQueryable <FeeClassDivision> GetAll()
{
try
{
List <FeeClassDivision> entites = new List <FeeClassDivision>();
DataTable dataTable = CommanMethodsForSQL.GetDataTable("SELECT FeeClassDivisionDetails.FeeClassDivisionId, FeeClassDivisionDetails.FeeHeadId, "
+ " FeeClassDivisionDetails.ClassDivisionId, FeeClassDivisionDetails.PeriodInMonthly, FeeClassDivisionDetails.AmountInMonthly, "
+ " FeeClassDivisionDetails.AmountInYearly, FeeClassDivisionDetails.[Status], FeeClassDivisionDetails.Remark, "
+ " RTRIM(LTRIM(FeeHeadDetails.FeeHead)) AS FeeHead, "
+ " ClassDivisionDetails.ClassId, ClassDivisionDetails.DivisionId, "
+ " RTRIM(LTRIM(ClassDetails.Class)) AS Class, RTRIM(LTRIM(DivisionDetails.Division)) AS Division "
+ " FROM FeeClassDivisionDetails INNER JOIN "
+ " FeeHeadDetails ON FeeClassDivisionDetails.FeeHeadId = FeeHeadDetails.FeeHeadId INNER JOIN "
+ " ClassDivisionDetails ON FeeClassDivisionDetails.ClassDivisionId = ClassDivisionDetails.ClassDivisionId INNER JOIN "
+ " ClassDetails ON ClassDivisionDetails.ClassId = ClassDetails.ClassId INNER JOIN "
+ " DivisionDetails ON ClassDivisionDetails.DivisionId = DivisionDetails.DivisionId;");
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
FeeClassDivision entity = new FeeClassDivision();
entity.FeeClassDivisionId = Convert.ToInt32(dr["FeeClassDivisionId"]);
entity.FeeHeadId = Convert.ToInt32(dr["FeeHeadId"]);
entity.ClassDivisionId = Convert.ToInt32(dr["ClassDivisionId"]);
entity.PeriodInMonthly = Convert.ToInt32(dr["PeriodInMonthly"]);
entity.AmountInMonthly = Convert.ToDecimal(dr["AmountInMonthly"]);
entity.AmountInYearly = Convert.ToDecimal(dr["AmountInYearly"]);
entity.Status = Convert.ToBoolean(dr["Status"].ToString());
entity.FeeHeadName = Convert.ToString(dr["FeeHead"]);
entity.ClassId = Convert.ToInt32(dr["ClassId"]);
entity.ClassName = Convert.ToString(dr["Class"]);
entity.DivisionId = Convert.ToInt32(dr["DivisionId"]);
entity.DivisionName = Convert.ToString(dr["Division"]);
entity.Remark = dr["Remark"].ToString();
entites.Add(entity);
}
}
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
public IQueryable <Account> GetAll(int schoolId)
{
try
{
List <Account> entites = new List <Account>();
String SqlQuery = "select * from Account";
if (schoolId != 0)
{
SqlQuery = SqlQuery + " where SchoolId=" + schoolId + ";";
}
DataTable dataTable = CommanMethodsForSQL.GetDataTable(SqlQuery);
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
Account entity = new Account();
entity.SrNo = Convert.ToInt32(dr["SrNo"]);
entity.NarrationDetails = Convert.ToString(dr["NarrationDetails"]);
entity.TransactionType = Convert.ToString(dr["TransactionType"]);
entity.PaymentMode = Convert.ToString(dr["PaymentMode"]);
entity.Amount = Convert.ToDecimal(dr["Amount"]);
//entity.Balance = Convert.ToDecimal(dr["Balance"]);
entity.TransactionDate = Convert.ToDateTime(dr["TransactionDate"]);
entity.Remark = dr["Remark"].ToString();
entity.CustomerName = dr["CustomerName"].ToString();
entity.BankName = dr["BankName"].ToString();
entity.ChqDDNumber = dr["ChqDDNumber"].ToString();
entity.ContactNo = dr["ContactNo"].ToString();
entites.Add(entity);
}
}
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
public IQueryable <StudentTransaction> GetAll(int SchoolId)
{
try
{
List <StudentTransaction> entites = new List <StudentTransaction>();
String SqlQuery = "select * from viewGetAllStudentTransaction";
if (SchoolId != 0)
{
SqlQuery = SqlQuery + " where SchoolId=" + SchoolId;
}
DataTable dataTable = CommanMethodsForSQL.GetDataTable(SqlQuery);
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
StudentTransaction entity = new StudentTransaction();
entity.StudentTransactionId = Convert.ToInt32(dr["StudentTransactionId"]);
entity.ClassDivisionId = Convert.ToInt32(dr["ClassDivisionId"]);
entity.ClassId = Convert.ToInt32(dr["ClassId"]);
entity.DivisionId = Convert.ToInt32(dr["DivisionId"]);
entity.ClassName = Convert.ToString(dr["Class"]);
entity.DivisionName = Convert.ToString(dr["Division"]);
entity.StudentId = Convert.ToInt32(dr["StudentId"]);
entity.StudentFullNameWithTitle = Convert.ToString(dr["StudentFullName"]);
entity.TransactionDate = Convert.ToDateTime(dr["TransactionDate"]);
entity.ReceiptNo = Convert.ToInt32(dr["ReceiptNo"]);
entity.ReceiptTotal = Convert.ToDecimal(dr["ReceiptTotal"]);
entity.BankName = dr["BankName"].ToString();
entity.ChequeNumber = dr["ChequeNumber"].ToString();
entity.Status = Convert.ToBoolean(dr["Status"].ToString());
entity.Remark = dr["Remark"].ToString();
entites.Add(entity);
}
}
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
public double getAccountBalance()
{
double returnValue = 0;
DataTable dataTable = CommanMethodsForSQL.GetDataTable("select Balance from Account where SrNo = (select MAX(SrNo) from Account as t)");
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
returnValue = Convert.ToInt32(dr[0]);
}
}
}
return(returnValue);
}
public IQueryable <EmployeeLeaveTransaction> GetAllEmployeeLeaveApply(int schoolId)
{
try
{
List <EmployeeLeaveTransaction> entites = new List <EmployeeLeaveTransaction>();
String SqlQuery = "select * from viewGetEmployeeListForAddLeave";
if (schoolId != 0)
{
SqlQuery = SqlQuery + " where SchoolId=" + schoolId + ";";
}
DataTable dataTable = CommanMethodsForSQL.GetDataTable(SqlQuery);
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
EmployeeLeaveTransaction entity = new EmployeeLeaveTransaction();
entity.EmployeeId = Convert.ToInt32(dr["EmployeeId"]);
entity.EmployeeFullName = Convert.ToString(dr["FirstName"]).Trim() + " " + Convert.ToString(dr["MiddleName"]).Trim() + " " + Convert.ToString(dr["LastName"]).Trim();
entity.BalanceLeaves = 0;
if (dr["BalanceLeaves"] != DBNull.Value)
{
entity.BalanceLeaves = Convert.ToDecimal(dr["BalanceLeaves"]);
}
entites.Add(entity);
}
}
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
public IQueryable <SchoolDetails> GetAll()
{
try
{
List <SchoolDetails> entites = new List <SchoolDetails>();
DataTable dataTable = CommanMethodsForSQL.GetDataTable("SELECT SchoolDetails.* FROM SchoolDetails;");
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
SchoolDetails entity = new SchoolDetails();
entity.SchoolId = Convert.ToInt32(dr["SchoolId"]);
entity.ManagementName = dr["ManagementName"].ToString();
entity.SchoolName = dr["SchoolName"].ToString();
entity.Address = dr["Address"].ToString();
entity.Taluka = dr["Taluka"].ToString();
entity.District = dr["District"].ToString();
entity.ContactNumber = dr["ContactNumber"].ToString();
entity.EmailId = dr["EmailId"].ToString();
entity.SchoolReconginationNo = dr["SchoolReconginationNo"].ToString();
entity.Medium = dr["Medium"].ToString();
entity.UDiscNo = dr["UDiscNo"].ToString();
entity.Board = dr["Board"].ToString();
entity.AffilationNo = dr["AffilationNo"].ToString();
entity.LogoPath = dr["LogoPath"].ToString();
entity.Status = Convert.ToBoolean(dr["Status"].ToString());
entity.Remark = dr["Remark"].ToString();
entites.Add(entity);
}
}
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
public IQueryable <Asset> GetAll(int schoolId)
{
try
{
List <Asset> entites = new List <Asset>();
String SqlQuery = "select * from viewGetAllAssetDetails";
if (schoolId != 0)
{
SqlQuery = SqlQuery + " where SchoolId=" + schoolId + ";";
}
DataTable dataTable = CommanMethodsForSQL.GetDataTable(SqlQuery);
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
Asset entity = new Asset();
entity.SrNo = Convert.ToInt32(dr["SrNo"]);
entity.TypeofAsset = Convert.ToString(dr["TypeofAsset"]);
entity.AssetName = Convert.ToString(dr["AssetName"]);
entity.Quantity = Convert.ToInt32(dr["Quantity"]);
entity.PricePerItem = Convert.ToDecimal(dr["PricePerItem"]);
entity.Total = Convert.ToDecimal(dr["Total"]);
entity.PurchaseDate = Convert.ToDateTime(dr["PurchaseDate"]);
entity.Condition = Convert.ToString(dr["Condition"]);
entity.AssesmentYear = Convert.ToDateTime(dr["AssesmentYear"]);
entity.Status = Convert.ToBoolean(dr["Status"].ToString());
entity.Remark = dr["Remark"].ToString();
entites.Add(entity);
}
}
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
public IQueryable <StudentAddress> GetAll()
{
try
{
List <StudentAddress> entites = new List <StudentAddress>();
DataTable dataTable = CommanMethodsForSQL.GetDataTable("SELECT StudentAddressDetails.* FROM StudentAddressDetails;");
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
StudentAddress entity = new StudentAddress();
entity.StudentId = Convert.ToInt32(dr["StudentId"]);
entity.CurrentAddress = dr["CurrentAddress"].ToString();
entity.CurrentCountryId = Convert.ToInt32(dr["CurrentCountryId"]);
entity.CurrentStateId = Convert.ToInt32(dr["CurrentStateId"]);
entity.CurrentDistrictId = Convert.ToInt32(dr["CurrentDistrictId"]);
entity.CurrentCityId = Convert.ToInt32(dr["CurrentCityId"]);
entity.CurrentPinCode = Convert.ToInt32(dr["CurrentPinCode"]);
entity.PermentAddress = dr["PermentAddress"].ToString();
entity.PermentCountryId = Convert.ToInt32(dr["PermentCountryId"]);
entity.PermentStateId = Convert.ToInt32(dr["PermentStateId"]);
entity.PermentDistrictId = Convert.ToInt32(dr["PermentDistrictId"]);
entity.PermentCityId = Convert.ToInt32(dr["PermentCityId"]);
entity.PermentPinCode = Convert.ToInt32(dr["PermentPinCode"]);
entity.Status = Convert.ToBoolean(dr["Status"].ToString());
entity.Remark = dr["Remark"].ToString();
entites.Add(entity);
}
}
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
public IQueryable <StudentLedger> GetAll()
{
try
{
List <StudentLedger> entites = new List <StudentLedger>();
DataTable dataTable = CommanMethodsForSQL.GetDataTable("SELECT StudentLedger.* FROM StudentLedger;");
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
StudentLedger entity = new StudentLedger();
entity.StudentLedgerId = Convert.ToInt32(dr["StudentLedgerId"]);
entity.StudentId = Convert.ToInt32(dr["StudentId"]);
entity.TransactionDate = Convert.ToDateTime(dr["TransactionDate"]);
entity.FeeHeadId = Convert.ToInt32(dr["FeeHeadId"]);
entity.Cr = Convert.ToDecimal(dr["Cr"]);
entity.Dr = Convert.ToDecimal(dr["Dr"]);
entity.HeadBalance = Convert.ToDecimal(dr["HeadBalance"]);
entity.Balance = Convert.ToDecimal(dr["Balance"]);
entity.ReceiptNo = Convert.ToInt32(dr["ReceiptNo"]);
entity.BankName = Convert.ToString(dr["BankName"]);
entity.ChequeNumber = Convert.ToString(dr["ChequeNumber"]);
entity.Status = Convert.ToBoolean(dr["Status"].ToString());
entity.Remark = dr["Remark"].ToString();
entites.Add(entity);
}
}
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
public IQueryable <ReligionCast> GetAll()
{
try
{
List <ReligionCast> entites = new List <ReligionCast>();
DataTable dataTable = CommanMethodsForSQL.GetDataTable("SELECT ReligionCastDetails.ReligionCastId, ReligionCastDetails.ReligionId, ReligionCastDetails.CastId, ReligionCastDetails.Status, "
+ " ReligionCastDetails.Remark, ReligionCastDetails.ReserveCategory, LTRIM(RTRIM(ReligionDetails.Religion)) AS Religion, LTRIM(RTRIM(CastDetails.Cast)) AS Cast "
+ " FROM ReligionCastDetails INNER JOIN "
+ " CastDetails ON ReligionCastDetails.CastId = CastDetails.CastId INNER JOIN "
+ " ReligionDetails ON ReligionCastDetails.ReligionId = ReligionDetails.ReligionId;");
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
ReligionCast entity = new ReligionCast();
entity.ReligionCastId = Convert.ToInt32(dr["ReligionCastId"]);
entity.ReligionId = Convert.ToInt32(dr["ReligionId"]);
entity.ReligionName = Convert.ToString(dr["Religion"]);
entity.CastId = Convert.ToInt32(dr["CastId"]);
entity.CastName = Convert.ToString(dr["Cast"]);
entity.ReserveCategory = Convert.ToString(dr["ReserveCategory"]);
entity.Status = Convert.ToBoolean(dr["Status"].ToString());
entity.Remark = dr["Remark"].ToString();
entites.Add(entity);
}
}
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
public IQueryable <User> GetAll()
{
try
{
List <User> Users = new List <User>();
DataTable dataTable = CommanMethodsForSQL.GetDataTable("SELECT UserDetails.SrNo, UserDetails.RoleId, ltrim(rtrim(UserDetails.UserId)) as UserId, ltrim(rtrim(UserDetails.Password)) as Password, UserDetails.UserName, UserDetails.Status, UserDetails.Remark, RoleDetails.Role, SchoolId" +
" FROM UserDetails INNER JOIN RoleDetails ON UserDetails.RoleId = RoleDetails.RoleId;");
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
User user = new User();
user.SrNo = Convert.ToInt32(dr["SrNo"]);
user.UserId = dr["UserId"].ToString();
user.RoleId = Convert.ToInt16(dr["RoleId"]);
user.UserName = dr["UserName"].ToString();
user.Status = Convert.ToBoolean(dr["Status"].ToString());
user.Remark = dr["Remark"].ToString();
user.UserRole = new Role();
user.UserRole.RoleName = dr["Role"].ToString();
user.Password = Convert.ToString(dr["Password"]);
user.SchoolId = Convert.ToInt32(dr["SchoolId"]);
Users.Add(user);
}
}
}
return(Users.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
public IQueryable <Student> GetAll(int schoolId)
{
try
{
List <Student> entites = new List <Student>();
String SqlQuery = "select * from viewGetAllStudentDetails";
if (schoolId != 0)
{
SqlQuery = SqlQuery + " where SchoolId=" + schoolId + ";";
}
DataTable dataTable = CommanMethodsForSQL.GetDataTable(SqlQuery);
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
Student entity = new Student();
entity.SrNo = Convert.ToInt32(dr["SrNo"]);
entity.StudentId = Convert.ToInt32(dr["StudentId"]);
entity.RegisterId = Convert.ToInt32(dr["RegisterId"]);
entity.ClassDivisionId = Convert.ToInt32(dr["ClassDivisionId"]);
entity.ClassId = Convert.ToInt32(dr["ClassId"]);
entity.DivisionId = Convert.ToInt32(dr["DivisionId"]);
entity.ClassName = Convert.ToString(dr["Class"]).Trim();
entity.DivisionName = Convert.ToString(dr["Division"]).Trim();
entity.Title = Convert.ToString(dr["Title"]).Trim();
entity.FirstName = Convert.ToString(dr["FirstName"]).Trim();
entity.MiddleName = Convert.ToString(dr["MiddleName"]).Trim();
entity.LastName = Convert.ToString(dr["LastName"]).Trim();
entity.MotherName = Convert.ToString(dr["MotherName"]).Trim();
entity.Gender = Convert.ToString(dr["Gender"]).Trim();
entity.Nationality = Convert.ToString(dr["Nationality"]).Trim();
entity.ReligionCastId = Convert.ToInt32(dr["ReligionCastId"]);
entity.ReligionId = Convert.ToInt32(dr["ReligionId"]);
entity.CastId = Convert.ToInt32(dr["CastId"]);
entity.ReligionName = Convert.ToString(dr["Religion"]).Trim();
entity.CastName = Convert.ToString(dr["Cast"]).Trim();
entity.ReserveCategory = Convert.ToString(dr["ReserveCategory"]).Trim();
entity.DateOfBirth = Convert.ToDateTime(dr["DateOfBirth"]);
entity.PlaceOfBirth = Convert.ToString(dr["PlaceOfBirth"]).Trim();
entity.AdharcardNo = Convert.ToString(dr["AdharcardNo"]).Trim();
entity.DateOfAdmission = Convert.ToDateTime(dr["DateOfAdmission"]);
entity.MotherTounge = Convert.ToString(dr["MotherTounge"]).Trim();
entity.UStudentId = Convert.ToString(dr["UStudentId"]).Trim();
entity.LastSchoolClass = Convert.ToString(dr["LastSchoolClass"]).Trim();
entity.LastSchoolTCNo = Convert.ToString(dr["LastSchoolTCNo"]).Trim();
entity.LastSchoolAttended = Convert.ToString(dr["LastSchoolAttended"]).Trim();
entity.Progress = Convert.ToString(dr["Progress"]).Trim();
entity.Conduct = Convert.ToString(dr["Conduct"]).Trim();
if (dr["DateOfLeavingSchool"] != DBNull.Value)
{
entity.DateOfLeavingSchool = Convert.ToDateTime(dr["DateOfLeavingSchool"]);
}
entity.ClassInWhichStudingAndSinceWhen = Convert.ToString(dr["ClassInWhichStudingAndSinceWhen"]).Trim();
entity.ReasonForLeavingSchool = Convert.ToString(dr["ReasonForLeavingSchool"]).Trim();
entity.RemarkOnTC = Convert.ToString(dr["RemarkOnTC"]).Trim();
entity.Status = Convert.ToBoolean(dr["Status"].ToString());
entity.Remark = dr["Remark"].ToString().Trim();
entity.TCPrinted = Convert.ToBoolean(dr["TCPrinted"].ToString());
if (dr["TCNo"] != null && !string.IsNullOrEmpty(dr["TCNo"].ToString()))
{
entity.TCNo = Convert.ToInt32(dr["TCNo"].ToString());
}
entity.LastSchoolClass = Convert.ToString(dr["LastSchoolClass"]).Trim();
entity.LastSchoolTCNo = Convert.ToString(dr["LastSchoolTCNo"]).Trim();
if (dr["PrevSchoolDateofLiving"] != DBNull.Value)
{
entity.PrevSchoolDateofLiving = Convert.ToDateTime(dr["PrevSchoolDateofLiving"]);
}
entites.Add(entity);
}
}
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
public IQueryable <EmployeeLeaveTransaction> GetAllEmployeeLeaves(int schoolId)
{
try
{
List <EmployeeLeaveTransaction> entites = new List <EmployeeLeaveTransaction>();
String SqlQuery = "SELECT EmployeeCode, FirstName, MiddleName, LastName, TransactionType,EmployeeLeaveTransaction.EmployeeId as EmpId, LeaveType, LeaveFromDate,LeaveToDate, LeavesInDays, BalanceLeaves,Remark, TransactionDate" +
" FROM Employee INNER JOIN EmployeeLeaveTransaction ON Employee.EmployeeId = EmployeeLeaveTransaction.EmployeeId";
if (schoolId != 0)
{
SqlQuery = SqlQuery + " where SchoolId=" + schoolId + ";";
}
DataTable dataTable = CommanMethodsForSQL.GetDataTable(SqlQuery);
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
EmployeeLeaveTransaction entity = new EmployeeLeaveTransaction();
entity.EmployeeId = Convert.ToInt32(dr["EmpId"]);
entity.EmployeeFullName = Convert.ToString(dr["FirstName"]).Trim() + " " + Convert.ToString(dr["MiddleName"]).Trim() + " " + Convert.ToString(dr["LastName"]).Trim();
entity.LeaveType = Convert.ToInt32(dr["LeaveType"]);
entity.LeaveFromDate = Convert.ToDateTime(dr["LeaveFromDate"]);
entity.LeaveToDate = Convert.ToDateTime(dr["LeaveToDate"]);
if (dr["LeavesInDays"] != DBNull.Value)
{
entity.LeavesInDays = Convert.ToDecimal(dr["LeavesInDays"]);
}
if (dr["BalanceLeaves"] != DBNull.Value)
{
entity.BalanceLeaves = Convert.ToDecimal(dr["BalanceLeaves"]);
}
entity.Remark = Convert.ToString(dr["Remark"]);
entity.TransactionDate = Convert.ToDateTime(dr["TransactionDate"]);
entity.TransactionType = 0;
if (Convert.ToString(dr["TransactionType"]).Trim() == "Cr")
{
entity.TransactionType = 1;
}
entites.Add(entity);
}
}
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw;
}
}
public void Add(StudentTransaction entity, int schoolId)
{
String SqlQuery = "select max(StudentTransactionId) As ReceiptNo from StudentTransaction";
if (schoolId != 0)
{
SqlQuery = SqlQuery + " where SchoolId=" + schoolId + ";";
}
DataTable dataTable = CommanMethodsForSQL.GetDataTable(SqlQuery);
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
if (dr["ReceiptNo"] != DBNull.Value)
{
entity.ReceiptNo = Convert.ToInt32(dr["ReceiptNo"]) + 1;
}
else
{
entity.ReceiptNo = 1;
}
}
else
{
entity.ReceiptNo = 1;
}
}
}
try
{
Hashtable parameterlist = new Hashtable();
parameterlist.Add("@ClassDivisionId", entity.ClassDivisionId);
parameterlist.Add("@StudentId", entity.StudentId);
parameterlist.Add("@SchoolId", schoolId);
parameterlist.Add("@TransactionDate", entity.TransactionDate);
parameterlist.Add("@Status", entity.Status);
parameterlist.Add("@Remark", entity.Remark);
parameterlist.Add("@ReceiptNo", entity.ReceiptNo);
parameterlist.Add("@BankName", entity.BankName);
parameterlist.Add("@ChequeNumber", entity.ChequeNumber);
parameterlist.Add("@ReceiptTotal", entity.ReceiptTotal);
parameterlist.Add("@UserId", 0);
parameterlist.Add("@OUT_StudentTransactionId", null);
Hashtable outparameterlist = new Hashtable();
int effetedRows = CommanMethodsForSQL.ExecuteNonQueryProcedure("sp_StudentTransactionInsert", parameterlist, ref outparameterlist);
int studentTransactionId = 0;
if (outparameterlist != null && outparameterlist.Count > 0)
{
foreach (string parametername in outparameterlist.Keys)
{
if (parametername.StartsWith("@OUT_"))
{
studentTransactionId = Convert.ToInt32(outparameterlist[parametername]);
}
}
}
if (studentTransactionId > 0 && entity.StudentTransactionSubList != null && entity.StudentTransactionSubList.Count > 0)
{
foreach (Entities.StudentTransactionSub studentTransactionSub in entity.StudentTransactionSubList)
{
parameterlist = new Hashtable();
parameterlist.Add("@StudentTransactionId", studentTransactionId);
parameterlist.Add("@FeeHead", studentTransactionSub.FeeHeadId);
parameterlist.Add("@Cr", studentTransactionSub.Cr);
parameterlist.Add("@DR", studentTransactionSub.Dr);
parameterlist.Add("@Balance", studentTransactionSub.Balance);
parameterlist.Add("@Remark", studentTransactionSub.Remark);
parameterlist.Add("@StudentId", entity.StudentId);
parameterlist.Add("@TransactionDate", entity.TransactionDate);
parameterlist.Add("@ReceiptNo", entity.ReceiptNo);
parameterlist.Add("@BankName", entity.BankName);
parameterlist.Add("@ChequeNumber", entity.ChequeNumber);
parameterlist.Add("@UserId", 0);
parameterlist.Add("@OUT_StudentTransactionSubId", null);
outparameterlist = new Hashtable();
effetedRows = CommanMethodsForSQL.ExecuteNonQueryProcedure("sp_StudentTransactionSubInsert", parameterlist, ref outparameterlist);
}
}
//string queryString = string.Format(" INSERT INTO [StudentTransaction]([AcademicYearId],[ClassDivisionId],[StudentId],[TransactionDate],[Status],[Remark]) VALUES({0}, {1},{2}, '{3}', {4}, '{5}');",
// entity.AcademicYearId, entity.ClassDivisionId, entity.StudentId,entity.TransactionDate, entity.Status, entity.Remark);
//int effetedRows = CommanMethodsForSQL.ExecuteNonQuery(queryString);
}
catch (Exception ex)
{
throw;
}
}
public IQueryable <HomePage> getChartData(int schoolId)
{
try
{
HomePage entity = new HomePage();
// Default values
entity.CastGeneralCount = 0;
entity.CastNT1Count = 0;
entity.CastNT2Count = 0;
entity.CastNT3Count = 0;
entity.CastNT4Count = 0;
entity.CastOBCCount = 0;
entity.CastSBCCount = 0;
entity.CastSCCount = 0;
entity.CastSTCount = 0;
entity.CastVJCount = 0;
entity.CastVJ1Count = 0;
List <HomePage> entites = new List <HomePage>();
String strQuery = "select ReserveCategory, Count from viewCasteChart";
if (schoolId != 0)
{
strQuery = strQuery + " where SchoolId=" + schoolId + " ;";
}
DataTable dataTable = CommanMethodsForSQL.GetDataTable(strQuery);
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
//HomePage entity = new HomePage();
switch ((dr["ReserveCategory"]).ToString())
{
case ("General"):
case ("सामान्य"):
entity.CastGeneralCount = Convert.ToInt32((dr["Count"]));
break;
case ("NT-1"):
case ("एनटी-1"):
entity.CastNT1Count = Convert.ToInt32((dr["Count"]));
break;
case ("NT-2"):
case ("एनटी-2"):
entity.CastNT2Count = Convert.ToInt32((dr["Count"]));
break;
case ("NT-3"):
case ("एनटी-3"):
entity.CastNT3Count = Convert.ToInt32((dr["Count"]));
break;
case ("NT-4"):
case ("एनटी-4"):
entity.CastNT4Count = Convert.ToInt32((dr["Count"]));
break;
case ("OBC"):
case ("ओबीसी"):
entity.CastOBCCount = Convert.ToInt32((dr["Count"]));
break;
case ("SBC"):
case ("एसबीसी"):
entity.CastSBCCount = Convert.ToInt32((dr["Count"]));
break;
case ("SC"):
case ("अनुसूचित जाती"):
entity.CastSCCount = Convert.ToInt32((dr["Count"]));
break;
case ("ST"):
case ("एसटी"):
entity.CastSTCount = Convert.ToInt32((dr["Count"]));
break;
case ("VJ"):
case ("व्हीजे"):
entity.CastVJCount = Convert.ToInt32((dr["Count"]));
break;
case ("VJ-1"):
case ("व्हीजे-1"):
entity.CastVJ1Count = Convert.ToInt32((dr["Count"]));
break;
default:
break;
}
// entity.SrNo = Convert.ToInt32(iCnt++);
//entity.StudentName = Convert.ToString((dr["StudentName"]));
entites.Add(entity);
}
}
}
entites.Add(entity);
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw ex;
}
}
public IQueryable <Export> getExportRecords(int schoolId)
{
try
{
List <Export> entites = new List <Export>();
String strQuery;
strQuery = "select * from viewExport where status = 1";
if (schoolId != 0)
{
strQuery = strQuery + " and SchoolId=" + schoolId;
}
strQuery = strQuery + ";";
DataTable dataTable = CommanMethodsForSQL.GetDataTable(strQuery);
if (dataTable != null && dataTable.Rows.Count > 0)
{
int iCnt = 1;
foreach (DataRow dr in dataTable.Rows)
{
if (dr != null)
{
Export entity = new Export();
entity.SrNo = Convert.ToInt32(iCnt++);
entity.StudentId = Convert.ToString((dr["UStudentId"]));
entity.StudentName = Convert.ToString((dr["StudentName"]));
entity.MotherName = Convert.ToString((dr["MotherName"]));
entity.Class = Convert.ToString((dr["Class"]));
entity.Division = Convert.ToString((dr["Division"]));
entity.GrNo = Convert.ToInt32((dr["GrNo"]));
entity.BirthDate = Convert.ToDateTime((dr["BirthDate"]));
entity.BirthPlace = Convert.ToString((dr["BirthPlace"]));
entity.DateOfAdmission = Convert.ToDateTime((dr["DateOfAdmission"]));
entity.AdmissionClass = Convert.ToString((dr["AdmissionClass"]));
entity.Religion = Convert.ToString((dr["Religion"]));
entity.Subcaste = Convert.ToString((dr["Subcaste"]));
entity.Category = Convert.ToString(dr["Category"]);
entity.AadharCardNo = Convert.ToString((dr["AadharCardNo"]));
entity.ParentsContactNo = Convert.ToString((dr["ParentsContactNo"]));
entity.Address = Convert.ToString((dr["Address"]));
entity.BankName = Convert.ToString((dr["BankName"]));
entity.BankAccountHolderName = Convert.ToString((dr["Bank account holder name"]));
entity.AccountNo = Convert.ToString((dr["AccountNo"]));
entity.Branch = Convert.ToString((dr["Branch"]));
entity.IFSCCode = Convert.ToString((dr["IFSCCode"]));
entites.Add(entity);
}
}
}
else
{
Export entity = new Export();
entity.SrNo = 0;
entity.StudentId = "";
entity.StudentName = "";
entity.MotherName = "";
entity.Class = "";
entity.Division = "";
entity.GrNo = Convert.ToInt32(0);
entity.BirthDate = Convert.ToDateTime("01-01-2017 12:00:00 AM");
entity.BirthPlace = "";
entity.DateOfAdmission = Convert.ToDateTime("01-01-2017 12:00:00 AM");
entity.AdmissionClass = "";
entity.Religion = "";
entity.Subcaste = "";
entity.Category = "";
entity.AadharCardNo = "";
entity.ParentsContactNo = "";
entity.Address = "";
entity.BankName = "";
entity.BankAccountHolderName = "";
entity.AccountNo = "";
entity.Branch = "";
entity.IFSCCode = "";
entites.Add(entity);
}
return(entites.AsQueryable());
}
catch (Exception ex)
{
throw ex;
}
}
