/// <summary>
/// Returns an TUser given the user's id
/// </summary>
/// <param name="userId">The user's id</param>
/// <returns></returns>
public TUser GetUserById(string userId)
{
TUser user = null;
string commandText = "Select * from users where Id = @id";
Dictionary <string, object> parameters = new Dictionary <string, object>()
{
{ "@id", userId }
};
var rows = _database.Query(commandText, parameters);
if (rows != null && rows.Count == 1)
{
var row = rows[0];
user = (TUser)Activator.CreateInstance(typeof(TUser));
user.Id = row["Id"];
user.UserName = row["UserName"];
user.PasswordHash = string.IsNullOrEmpty(row["PasswordHash"]) ? null : row["PasswordHash"];
user.SecurityStamp = string.IsNullOrEmpty(row["SecurityStamp"]) ? null : row["SecurityStamp"];
user.Email = string.IsNullOrEmpty(row["Email"]) ? null : row["Email"];
user.EmailConfirmed = row["EmailConfirmed"] == "1" ? true:false;
user.PhoneNumber = string.IsNullOrEmpty(row["PhoneNumber"]) ? null : row["PhoneNumber"];
user.PhoneNumberConfirmed = row["PhoneNumberConfirmed"] == "1" ? true : false;
user.LockoutEnabled = row["LockoutEnabled"] == "1" ? true : false;
user.LockoutEndDateUtc = string.IsNullOrEmpty(row["LockoutEndDateUtc"]) ? DateTime.Now : DateTime.Parse(row["LockoutEndDateUtc"]);
user.AccessFailedCount = string.IsNullOrEmpty(row["AccessFailedCount"]) ? 0 : int.Parse(row["AccessFailedCount"]);
user.FirstName = string.IsNullOrEmpty(row["FirstName"]) ? null : row["FirstName"];
user.LastName = string.IsNullOrEmpty(row["LastName"]) ? null : row["LastName"];
}
return(user);
}
/// <summary>
/// Gets all upload data from the uploadsdata table by Upload ID
/// </summary>
/// <param name="uploadId">The Branch Id</param>
/// <returns></returns>
///
public List <UploadDataEntity> GetUploadsData(string uploadId)
{
string commandText = "Select Id, UploadId, Narration, Amount, AccountNumber, DebitOrCredit, PostingCode, TranDate, Status, TranID from uploadsdata where UploadId = @uploadId and Status <> 1";
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("@uploadId", uploadId);
List <UploadDataEntity> uploaddata = new List <UploadDataEntity>();
var result = _database.Query(commandText, parameters);
foreach (var res in result)
{
uploaddata.Add(new UploadDataEntity()
{
Id = res["Id"],
AccountNumber = res["AccountNumber"],
Amount = Convert.ToDecimal(res["Amount"]),
Narration = res["Narration"],
UploadId = res["UploadId"],
PostingCode = res["PostingCode"],
Debit1Credit0 = Convert.ToBoolean(res["DebitOrCredit"]),
Status = Convert.ToInt32(res["Status"]),
TranDate = res["TranDate"],
TranID = res["TranID"]
});
}
return(uploaddata);
}
/// <summary>
/// Returns list of Mark Portion name and corresponding percentage given TeacherSubject Id
/// </summary>
/// <param name="teacherSubjectId">Id of corresponding teacherSubject entry</param>
/// <returns>List of TextValuePair where Text is the Portion Name and Value is corresponding percentage</returns>
public List <TextValuePair> GetMarkPortionPercentage(object teacherSubjectId)
{
return(db.Query("getMarkPortionByTSId", new Dictionary <string, object>()
{
{ "@TSId", teacherSubjectId }
}, true).
Select(x => new TextValuePair {
Text = x["portionname"], Value = x["percentage"]
}).ToList());
}
/// <summary>
/// List of classes exist in given year
/// </summary>
/// <param name="yearId">Id of corresponding year entry</param>
/// <returns>List of TextValuePair where Text is the label of class and Value is the level of class</returns>
public List <TextValuePair> GetClassByYear(object yearId)
{
return(db.Query("getClassByYId", new Dictionary <string, object>()
{
{ "@yid", yearId }
}, true).
Select(x => new TextValuePair {
Text = x["class"], Value = x["classid"]
}).ToList());
}
/// <summary>
/// Rerturns a List of term name and termYearClassSectionId
/// </summary>
/// <param name="yearClassSectionId">Id of corresponding yearClassSection entry</param>
/// <returns>A List of TextValuePair where Text is term name and Value is termYearClassSectionId</returns>
public List <TextValuePair> GetTermByYearClassSection(int yearClassSectionId)
{
return(db.Query("getTermByYCSId", new Dictionary <string, object>()
{
{ "@YCSId", yearClassSectionId }
}, true).Select(x => new TextValuePair {
Text = x["term"],
Value = x["id"]
}).ToList());
}
public List <Student> GetStudents(object yearClassSectionId)
{
return(db.Query("getStudentByYCSId", new Dictionary <string, object>()
{
{ "@YCSId", yearClassSectionId }
}, true).
Select(x => new Student {
ID = x["studentid"],
FirstName = x["firstname"],
LastName = x["lastname"],
Roll = Convert.ToInt32(x["roll"]),
UserId = x["userid"]
}).ToList());
}
public List <TextValuePair> GetAllTerm()
{
return(db.Query("getAllTerm", null, true).
Select(x => new TextValuePair {
Text = x["term"], Value = x["termid"]
}).ToList());
}
public List <TextValuePair> GetAllSection()
{
return(db.Query("getAllSection", null, true).
Select(x => new TextValuePair {
Text = x["section"], Value = x["sectionid"]
}).ToList());
}
public List <TextValuePair> GetAllYear()
{
return(db.Query("getAllYear", null, true).
Select(x => new TextValuePair {
Text = x["year"], Value = x["yearid"]
}).ToList());
}
public List <TextValuePair> GetAllClass()
{
return(db.Query("getAllClass", null, true).
Select(x => new TextValuePair {
Text = x["class"], Value = x["classid"]
}).ToList());
}
/// <summary>
/// Returns a bank name given the branchId
/// </summary>
/// <param name="bankId">The Bank Id</param>
/// <returns>Bank name</returns>
public Dictionary <string, string> GetBankName(string bankId)
{
string commandText = "Select BankName, BankAcronym from banks where Id = @id";
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("@id", bankId);
Dictionary <string, string> bankdetail = new Dictionary <string, string>();
var result = _database.Query(commandText, parameters);
foreach (var res in result)
{
bankdetail["BankName"] = res["BankName"];
bankdetail["BankAcronym"] = res["BankAcronym"];
}
return(bankdetail);
}
/// <summary>
/// Returns a account Id, bankId and name of the account number
/// </summary>
/// <param name="accountnumber">The account number</param>
/// <returns>Bank name</returns>
public Dictionary <string, string> GetAccountDetail(string accountNumber)
{
string commandText = "Select Id, AccountName, AccountBranch from accountnumbers where AccountNumber = @accountno";
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("@accountno", accountNumber);
Dictionary <string, string> accountdetail = new Dictionary <string, string>();
var result = _database.Query(commandText, parameters);
foreach (var res in result)
{
accountdetail["Id"] = res["Id"];
accountdetail["AccountName"] = res["AccountName"];
accountdetail["AccountBranch"] = res["AccountBranch"];
}
return(accountdetail);
}
/// <summary>
/// Returns a branch name, bankid and GLAccount given the branchId
/// </summary>
/// <param name="branchId">The branch Id</param>
/// <returns>branch name</returns>
public Dictionary <string, string> GetBranchDetail(string branchId)
{
string commandText = "Select BranchName, BranchCode from branches where Id = @id";
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("@id", branchId);
Dictionary <string, string> branchdetail = new Dictionary <string, string>();
var result = _database.Query(commandText, parameters);
foreach (var res in result)
{
branchdetail["BranchName"] = res["BranchName"];
//branchdetail["GLAccount"] = res["GLAccount"];
branchdetail["BranchCode"] = res["BranchCode"];
}
return(branchdetail);
}
public List <Teacher> GetAllTeacher()
{
return(db.Query("getAllTeacher", null, true).Select(x => new Teacher {
FirstName = x["firstname"],
LastName = x["lastname"],
ID = Convert.ToInt32(x["id"]),
Designation = x["designation"],
Qualification = x["qualification"]
}).ToList());
}
/// <summary>
/// Returns the year where given teacher takes or took class
/// </summary>
/// <param name="teacherUserId"></param>
/// <returns></returns>
public List <TextValuePair> GetYear(object teacherUserId)
{
return(db.Query("getYearByTUId", new Dictionary <string, object>()
{
{ "@TUId", teacherUserId }
}, true).
Select(x => new TextValuePair {
Text = x["year"], Value = x["yearid"]
}).ToList());
}
/// <summary>
/// Returns an TUser given the user's id
/// </summary>
/// <param name="userId">The user's id</param>
/// <returns></returns>
public TUser GetUserById(string userId)
{
TUser user = null;
string commandText = "Select * from Users where Id = @id";
Dictionary <string, object> parameters = new Dictionary <string, object>()
{
{ "@id", userId }
};
var rows = _database.Query(commandText, parameters);
if (rows != null && rows.Count == 1)
{
var row = rows[0];
user = (TUser)Activator.CreateInstance(typeof(TUser));
user.Id = row["Id"];
user.UserName = row["UserName"];
user.FirstName = row["firstname"];
user.LastName = row["lastname"];
user.FatherName = row["father_name"];
user.MotherName = row["mother_name"];
user.PasswordHash = string.IsNullOrEmpty(row["PasswordHash"]) ? null : row["PasswordHash"];
user.SecurityStamp = string.IsNullOrEmpty(row["SecurityStamp"]) ? null : row["SecurityStamp"];
user.Email = string.IsNullOrEmpty(row["Email"]) ? null : row["Email"];
user.EmailConfirmed = row["EmailConfirmed"] == "1" ? true : false;
user.PhoneNumber = string.IsNullOrEmpty(row["PhoneNumber"]) ? null : row["PhoneNumber"];
user.PhoneNumberConfirmed = row["PhoneNumberConfirmed"] == "1" ? true : false;
user.LockoutEnabled = row["LockoutEnabled"] == "1" ? true : false;
user.LockoutEndDateUtc = string.IsNullOrEmpty(row["LockoutEndDateUtc"]) ? DateTime.Now : DateTime.Parse(row["LockoutEndDateUtc"]);
user.AccessFailedCount = string.IsNullOrEmpty(row["AccessFailedCount"]) ? 0 : int.Parse(row["AccessFailedCount"]);
user.Sex = row["sex"];
user.CurrentAddress = row["current_address"];
user.ParmanetAddress = row["parmanent_address"];
user.BirthDate = DateTime.Parse(row["date_of_birth"]);
user.Education = row["education"];
user.BloodGroup = row["blood_group"];
}
return(user);
}
public List <Subject> GetSubject(object yearId)
{
return(db.Query("getSubjectByYId", new Dictionary <string, object>()
{
{ "@YId", yearId }
}, true).
Select(x => new Subject {
Name = x["subject"],
SubjectCode = x["subjectcode"],
ID = x["subjectid"],
TotalMark = Convert.ToInt32(x["totalmark"])
}).ToList());
}
/// <summary>
/// Returns an IdentityUser given the user's id
/// </summary>
/// <param name="userId">The user's id</param>
/// <returns></returns>
public IdentityUser GetUserById(string userId)
{
IdentityUser user = null;
string commandText = "Select * from Users where Id = @id";
Dictionary <string, object> parameters = new Dictionary <string, object>()
{
{ "@id", userId }
};
var rows = _database.Query(commandText, parameters);
if (rows != null && rows.Count == 1)
{
var row = rows[0];
user = new IdentityUser();
user.Id = row["Id"];
user.UserName = row["UserName"];
user.PasswordHash = string.IsNullOrEmpty(row["PasswordHash"]) ? null : row["PasswordHash"];
user.SecurityStamp = string.IsNullOrEmpty(row["SecurityStamp"]) ? null : row["SecurityStamp"];
}
return(user);
}
public List <Student> GetStudents(object yearId, object classId, object sectionId)
{
var YCSId = YCSTable.GetYearClassSectionId(yearId, classId, sectionId);
return(db.Query("getStudentByYCSId", new Dictionary <string, object>()
{
{ "@YCSId", YCSId }
}, true).
Select(x => new Student {
FirstName = x["firstname"],
LastName = x["lastname"],
Roll = int.Parse(x["roll"]),
ID = x["studentid"]
}).ToList());
}
/// <summary>
/// Returns a list of user's roles
/// </summary>
/// <param name="userId">The user's id</param>
/// <returns></returns>
public List <string> FindByUserId(string userId)
{
List <string> roles = new List <string>();
string commandText = "Select roles.Name from userroles, roles where userroles.UserId = @userId and userroles.RoleId = roles.Id";
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("@userId", userId);
var rows = _database.Query(commandText, parameters);
foreach (var row in rows)
{
roles.Add(row["Name"]);
}
return(roles);
}
/// <summary>
/// Returns a list of user's branches
/// </summary>
/// <param name="userId">The user's id</param>
/// <returns></returns>
public IdentityBranch FindByUserId(string userId)
{
IdentityBranch branch = new IdentityBranch();
string commandText = "Select branches.Id, branches.BranchName from userbranches, branches where userbranches.UserId = @userId and userbranches.BranchId = branches.Id";
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("@userId", userId);
var rows = _database.Query(commandText, parameters);
foreach (var row in rows)
{
branch.Id = row["Id"];
branch.Name = row["BranchName"];
}
return(branch);
}
/// <summary>
/// Returns a list of user's logins
/// </summary>
/// <param name="userId">The user's id</param>
/// <returns></returns>
public List <UserLoginInfo> FindByUserId(string userId)
{
List <UserLoginInfo> logins = new List <UserLoginInfo>();
string commandText = "Select * from UserLogins where UserId = @userId";
Dictionary <string, object> parameters = new Dictionary <string, object>()
{
{ "@userId", userId }
};
var rows = _database.Query(commandText, parameters);
foreach (var row in rows)
{
var login = new UserLoginInfo(row["LoginProvider"], row["ProviderKey"]);
logins.Add(login);
}
return(logins);
}
/// <summary>
/// Returns a ClaimsIdentity instance given a userId
/// </summary>
/// <param name="userId">The user's id</param>
/// <returns></returns>
public ClaimsIdentity FindByUserId(string userId)
{
ClaimsIdentity claims = new ClaimsIdentity();
string commandText = "Select * from userclaims where UserId = @userId";
Dictionary <string, object> parameters = new Dictionary <string, object>()
{
{ "@UserId", userId }
};
var rows = _database.Query(commandText, parameters);
foreach (var row in rows)
{
Claim claim = new Claim(row["ClaimType"], row["ClaimValue"]);
claims.AddClaim(claim);
}
return(claims);
}
public List <IdentityBankGLAccount> GetBankGLAccounts(string bankId)
{
string commandText = "Select Id, BankId, GLAccount from bankglaccounts where BankId = @bankid";
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("@bankid", bankId);
List <IdentityBankGLAccount> bankGlAccounts = new List <IdentityBankGLAccount>();
var result = _database.Query(commandText, parameters);
foreach (var res in result)
{
bankGlAccounts.Add(new IdentityBankGLAccount()
{
Id = res["Id"],
BankId = res["BankId"],
GLAccount = res["GLAccount"]
});
}
return(bankGlAccounts);
}
public List <StudentMark> GetMark(object teacherSubjectId)
{
return(db.Query("getMarkByTSId",
new Dictionary <string, object>()
{
{ "@TSId", teacherSubjectId }
}, true)
.Select(x => new StudentMark {
Student = new Student {
ID = (x["studentid"]),
FirstName = x["firstname"],
LastName = x["lastname"],
Roll = Convert.ToInt32(x["roll"])
},
Mark = (x["mark"]),
MarkId = (x["markid"]),
PortionId = x["portionId"],
PortionPercentage = x["portionpercentage"],
Term = x["term"],
TermId = x["termid"],
PortionName = x["portionname"],
Subject = x["subject"]
}).ToList());
}
/// <summary>
/// Returns a uploader Id, branch Id and status of the uploadId
/// </summary>
/// <param name="uploadId">The upload id</param>
/// <returns>Bank name</returns>
public Dictionary <string, string> GetUploadDetail(string uploadId)
{
string commandText = "Select UploaderId, BankId, BranchId, Status from uploads where Id = @id";
Dictionary <string, object> parameters = new Dictionary <string, object>();
parameters.Add("@id", uploadId);
Dictionary <string, string> uploaddetail = new Dictionary <string, string>();
var result = _database.Query(commandText, parameters);
foreach (var res in result)
{
uploaddetail["UploaderId"] = res["UploaderId"];
uploaddetail["BankId"] = res["BankId"];
uploaddetail["BranchId"] = res["BranchId"];
uploaddetail["Status"] = res["Status"];
}
return(uploaddetail);
}