public DataSet getTransactionDetails_ALL_Paging(int start, int end)
{
DataSet dt = new DataSet();
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"select * from
(select (ROW_NUMBER() OVER (ORDER BY TransactionLog.ID ))AS ROW,
* from TransactionLog ) as res where row between " + start + " and " + end + " ";
// dbManager.Command.CommandText = "Select id,title,substring(detail,1,500) as DetailsFirstHalf,detail,tags from articlesforposting";
dbManager.LoadDataSet(dt);
}
return dt;
}
public int geteWalletBalancebyEnum(string enumber)
{
enumber = utilities.Encrypt(enumber);
int balance = -1;
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"SELECT CurrentBalance from ewallet where enumber=@enumber";
dbManager.Command.Parameters.AddWithValue("@enumber", enumber);
dbManager.LoadDataSet(ds);
if (ds.Tables[0].Rows.Count > 0)
{
balance = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
}
}
}
catch (Exception exp)
{
throw;
}
return balance;
}
public DataSet updateTransaction_ERROR_ewallet(DateTime transactionDate, string responseCode, string responseDescripton, string txnRef, string ewalletNum, int amount)
{
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"Update TransactionLog
Set TransactionDate=@tdate ,ResponseCode=@response,ResponseDescription=@rdescription
where TransactionNumber=@tnumber;update StudentApplications set status=5 ;
Insert into eWallet_History(ewalletNumber,Description,Amount,Type,TransasctionRef,UserID,status)
Values (@1,@2,@3,@4,@5,@6,@7) ;";
dbManager.Command.Parameters.AddWithValue("@response", responseCode);
dbManager.Command.Parameters.AddWithValue("@tdate", transactionDate);
dbManager.Command.Parameters.AddWithValue("@rdescription", responseDescripton);
dbManager.Command.Parameters.AddWithValue("@tnumber", txnRef);
dbManager.Command.Parameters.AddWithValue("@1", ewalletNum);
dbManager.Command.Parameters.AddWithValue("@2", "Deposit Funds into eWallet Account");
dbManager.Command.Parameters.AddWithValue("@3", amount);
dbManager.Command.Parameters.AddWithValue("@4", "Debit");
dbManager.Command.Parameters.AddWithValue("@5", txnRef);
dbManager.Command.Parameters.AddWithValue("@6", Membership.GetUser().ProviderUserKey.ToString());
dbManager.Command.Parameters.AddWithValue("@7", 5);
dbManager.LoadDataSet(ds);
}
}
catch (Exception exp)
{
throw;
}
return ds;
}
public DataSet getTransactionDetails_User_Paging(int start, int end, string ewalletNumber)
{
DataSet dt = new DataSet();
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"select * from
(select (ROW_NUMBER() OVER (ORDER BY eWallet_History.ID ))AS ROW,
* from eWallet_History where ewalletNumber=@ewalletNumber ) as res where row between " + start + " and " + end + " ";
dbManager.Command.Parameters.AddWithValue("@ewalletNumber", ewalletNumber);
dbManager.LoadDataSet(dt);
}
return dt;
}
public DataSet getTransactionTable_Amount_feetype(string transactionNumber)
{
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"SELECT Amount,feetype from TransactionLog Where TransactionNumber=@trnumber";
dbManager.Command.Parameters.AddWithValue("@trnumber", transactionNumber);
dbManager.LoadDataSet(ds);
}
}
catch (Exception exp)
{
throw;
}
return ds;
}
public DataSet loadCbtScheduleUserID(string userID)
{
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"SELECT *,(Select top 1 ID from CBT_Schedules cs
where cs.ScheduleDate=si.ScheduleDate and cs.scheduleTime=si.ScheduleTime) as timeID from Students_CbtScheduleInfo si where userID=@uID";
dbManager.Command.Parameters.AddWithValue("@uID", userID);
dbManager.LoadDataSet(ds);
}
}
catch (Exception exp)
{
throw;
}
return ds;
}
public DataSet loadOlevelDataUserID_fs2(string userID)
{
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"SELECT * from Student_OlevelInfo_SecondSitting where userID=@uID";
dbManager.Command.Parameters.AddWithValue("@uID", userID);
dbManager.LoadDataSet(ds);
}
}
catch (Exception exp)
{
throw;
}
return ds;
}
public DataSet getApplication_allUser_Admin(int start, int end)
{
int count = 0;
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"select * from
(select (ROW_NUMBER() OVER (ORDER BY sp.ID ))AS ROW, sp.ID,sp.UserID,(Select ProgramName from ProgramsTable where id=sp.ProgramID) as programName,
(Select Course from CoursesTable where id=sp.FirstCourse) as Course1,
(Select Course from CoursesTable where id=sp.SecondCourse) as Course2,
(Select ScheduleDate +'<br />'+ScheduleTime from Students_CbtScheduleInfo where Userid=sp.UserID) as CbtSchedule,
Campus,
CASE WHEN FeePaid = 1 THEN 'Yes' ELSE 'No' END AS FeePaid,
CASE WHEN OptionalCourse = 1 THEN 'Yes' ELSE 'No' END AS OptionalCourse,
CASE WHEN FormFilled = 1 THEN 'Yes' ELSE 'No' END AS FormFilled
,txrefnumber,sp.DateCreated
,Status
,formnumber ,ct.cbtscore,ct.jambscore,ct.admissionscore from StudentApplications sp
left join dbo.Students_CbtResults ct on ct.UserID=sp.userID where
sp.formfilled=1 and sp.AdmissionGranted=0 ) as res where row between " + start + " and " + end + " ";
dbManager.LoadDataSet(ds);
}
}
catch (Exception exp)
{
throw;
}
return ds;
}
public DataSet getSingleTimes_CBT(string date, int pid, string time)
{
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"SELECT * from CBT_Schedules where programID=@pid and ScheduleDate=@date and ScheduleTime=@time ";
dbManager.Command.Parameters.AddWithValue("@pid", pid);
dbManager.Command.Parameters.AddWithValue("@date", date);
dbManager.Command.Parameters.AddWithValue("@time", time);
dbManager.LoadDataSet(ds);
}
}
catch (Exception exp)
{
throw;
}
return ds;
}
public DataSet getAllTimes_CBT(string date, int pid)
{
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"SELECT * from CBT_Schedules cb where programID=@pid and ScheduleDate=@date and CAST(Capacity AS INT) > CAST(Used AS INT) ";
dbManager.Command.Parameters.AddWithValue("@pid", pid);
dbManager.Command.Parameters.AddWithValue("@date", date);
dbManager.LoadDataSet(ds);
}
}
catch (Exception exp)
{
throw;
}
return ds;
}
public DataSet getGrades()
{
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"SELECT * from Application_OlevelSubjects where status=0 ";
dbManager.LoadDataSet(ds);
}
}
catch (Exception exp)
{
throw;
}
return ds;
}
public DataSet getAreas_States(int stateID)
{
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"SELECT * from Application_Areas where state=@state and status=0 ";
dbManager.Command.Parameters.AddWithValue("@state", stateID);
dbManager.LoadDataSet(ds);
}
}
catch (Exception exp)
{
throw;
}
return ds;
}
public bool checkUniqueEwalletNumber(string walletNumber)
{
bool flag = false;
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"SELECT ID from ewallet where Enumber=@enum";
dbManager.Command.Parameters.AddWithValue("@enum", walletNumber);
dbManager.LoadDataSet(ds);
if (ds.Tables[0].Rows.Count > 0)
{
flag = false;
}
else
{
flag = true;
}
}
}
catch (Exception exp)
{
throw;
}
return flag; ;
}
public DataSet getEwalletNumber_pin(string enumber)
{
enumber = utilities.Encrypt(enumber);
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"SELECT enumber,CurrentBalance,pin,status from ewallet where enumber=@enumber";
dbManager.Command.Parameters.AddWithValue("@enumber", enumber);
dbManager.LoadDataSet(ds);
}
}
catch (Exception exp)
{
throw;
}
return ds;
}
public DataSet geteWalletrow(int rowID)
{
string enumber = string.Empty;
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"SELECT * from eWallet_History where ID=@userID";
dbManager.Command.Parameters.AddWithValue("@userID", rowID);
dbManager.LoadDataSet(ds);
}
}
catch (Exception exp)
{
throw;
}
return ds;
}
public DataSet getTransactionDetails_txtRef(string transactionnumber)
{
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"SELECT * FROM Transactionlog where TransactionNumber=@tnumber ";
dbManager.Command.Parameters.AddWithValue("@tnumber", transactionnumber);
dbManager.LoadDataSet(ds);
}
}
catch (Exception exp)
{
}
return ds;
}
public int getDepositFormAmount(string transactionNumber)
{
DataSet ds = new DataSet();
int amount = 0;
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"SELECT AmountPaid from eWalletPaymentdeposit_application Where TransactionNo=@trnumber";
SqlParameter param = new SqlParameter("@trnumber",transactionNumber);
dbManager.Command.Parameters.Add(param);
dbManager.LoadDataSet(ds);
if (ds.Tables[0].Rows.Count > 0)
{
String amt = ds.Tables[0].Rows[0][0].ToString();
amount = Convert.ToInt32(utilities.Decrypt(amt));
}
}
}
catch (Exception exp)
{
throw;
}
return amount;
}
public DataSet getdates_CBT(int piD)
{
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"SELECT distinct(ScheduleDate) from CBT_Schedules where programID=@pid and status=0 ";
dbManager.Command.Parameters.AddWithValue("@pid", piD);
dbManager.LoadDataSet(ds);
}
}
catch (Exception exp)
{
throw;
}
return ds;
}
public DataSet loadJamDataByUserID(string userID)
{
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"SELECT * from JAMB_Data where userID=@uID";
dbManager.Command.Parameters.AddWithValue("@uID", userID);
dbManager.LoadDataSet(ds);
}
}
catch (Exception exp)
{
throw;
}
return ds;
}
public int getApplicationCount_User(string userID)
{
int count = 0;
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"SELECT Count(id) from StudentApplications where userID=@userID";
dbManager.Command.Parameters.AddWithValue("@userID", userID);
dbManager.LoadDataSet(ds);
if (ds.Tables[0].Rows.Count > 0)
{
count = Convert.ToInt16(ds.Tables[0].Rows[0][0].ToString());
}
}
}
catch (Exception exp)
{
throw;
}
return count;
}
public DataSet loadPreviousAcademicRecordByUserID(string userID)
{
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"SELECT * from Students_PreviousRecord where userID=@uID";
dbManager.Command.Parameters.AddWithValue("@uID", userID);
dbManager.LoadDataSet(ds);
}
}
catch (Exception exp)
{
throw;
}
return ds;
}
public DataSet getApplication_User(string userID)
{
int count = 0;
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"delete from StudentApplications where FormNumber is null and userID=@userid; select sp.ID,sp.UserID,(Select ProgramName from ProgramsTable where id=sp.ProgramID) as programName,
(Select Course from CoursesTable where id=sp.FirstCourse) as Course1,
(Select Course from CoursesTable where id=sp.SecondCourse) as Course2,
(Select ScheduleDate +'<br />'+ScheduleTime from Students_CbtScheduleInfo where Userid=sp.UserID) as CbtSchedule,
Campus,
CASE WHEN FeePaid = 1 THEN 'Yes' ELSE 'No' END AS FeePaid,
CASE WHEN OptionalCourse = 1 THEN 'Yes' ELSE 'No' END AS OptionalCourse,
CASE WHEN FormFilled = 1 THEN 'Yes' ELSE 'No' END AS FormFilled
,txrefnumber,sp.DateCreated
,Status
,formnumber ,ct.cbtscore,ct.jambscore,ct.admissionscore from StudentApplications sp
left join dbo.Students_CbtResults ct on ct.regno=sp.formnumber where
sp.UserID=@userID";
dbManager.Command.Parameters.AddWithValue("@userID", userID);
dbManager.LoadDataSet(ds);
}
}
catch (Exception exp)
{
throw;
}
return ds;
}
public DataSet getAlluserRelatedInfo(string userID)
{
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"select *,(Select State from Application_States at Where at.ID=sb.state ) as stateNew,
(Select area from Application_Areas ar Where ar.ID=sb.LocalGovtArea ) as govtNew,
(Select InstitutionName from Application_JambInstitutions aji
Where aji.ID=spr.InstitutionAttented ) as InstitutionNew
from Students_BioData sb
join dbo.Students_PreviousRecord spr on sb.UserID=spr.userID
join dbo.Students_CbtScheduleInfo cbt on sb.UserID=cbt.userID
where sb.UserID=@uID
";
dbManager.Command.Parameters.AddWithValue("@uID", userID);
dbManager.LoadDataSet(ds);
}
}
catch (Exception exp)
{
throw;
}
return ds;
}
public DataSet loadAdmission(string formnumber)
{
int count = 0;
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"Select * from admissionlist where Regno=@regno";
dbManager.Command.Parameters.AddWithValue("@regno", formnumber);
dbManager.LoadDataSet(ds);
}
}
catch (Exception exp)
{
throw;
}
return ds;
}
public int getApplicationID_transcationno(string txn)
{
DataSet ds = new DataSet();
int ID = -1;
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"SELECT ID from students_applications where TxRefNumber=@tr ";
dbManager.Command.Parameters.AddWithValue("@tr", txn);
dbManager.LoadDataSet(ds);
if (ds.Tables[0].Rows.Count > 0)
{
ID = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
}
}
}
catch (Exception exp)
{
throw;
}
return ID;
}
public DataSet getApplication_ID(string ID)
{
int count = 0;
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"select sp.ID,sp.UserID,(Select ProgramName from ProgramsTable where id=sp.ProgramID) as programName,
(Select Course from CoursesTable where id=sp.FirstCourse) as Course1,
(Select Course from CoursesTable where id=sp.SecondCourse) as Course2,
cbt.ScheduleDate +', '+cbt.ScheduleTime as cbtschedule,
Campus,cb.firstname+' '+cb.surname as fullname,gender,address,phonenumber,email
,state+'/'+localgovtarea as state,cbtusername,cbtpassword,cb.profilepic,
CASE WHEN FeePaid = 1 THEN 'Yes' ELSE 'No' END AS FeePaid,
CASE WHEN OptionalCourse = 1 THEN 'Yes' ELSE 'No' END AS OptionalCourse,
CASE WHEN FormFilled = 1 THEN 'Yes' ELSE 'No' END AS FormFilled
,txrefnumber,sp.DateCreated ,Status,jmb.subject1+','+jmb.subject2+','+jmb.subject3+','+jmb.subject4 as jambsubjects ,
,formnumber ,ct.cbtscore,ct.jambscore,ct.admissionscore
,spr.*,olvl.examtype,olvl.exammonth,olvl.examyear,olvl.examnumber
from StudentApplications sp
left join dbo.Students_CbtResults ct on ct.regno=sp.formnumber
left join dbo.Students_BioData cb on cb.regno=sp.formnumber
left join dbo.Students_CbtScheduleInfo cbt on cbt.regno=sp.formnumber
left join dbo.Students_PreviousRecord spr on spr.regno=sp.formnumber
left join dbo.JAMB_Data jmb on jmb.regno=sp.formnumber
left join dbo.Student_OlevelInfo_FirstSitting olvl on olvl.regno=sp.formnumber where
sp.ID=@ID";
dbManager.Command.Parameters.AddWithValue("@ID", ID);
dbManager.LoadDataSet(ds);
}
}
catch (Exception exp)
{
throw;
}
return ds;
}
public DataSet updateTransaction_ERROR(DateTime transactionDate, string responseCode, string responseDescripton, string txnRef)
{
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"Update TransactionLog
Set TransactionDate=@tdate ,ResponseCode=@response,ResponseDescription=@rdescription
where TransactionNumber=@tnumber;update StudentApplications set status=5 ";
dbManager.Command.Parameters.AddWithValue("@response", responseCode);
dbManager.Command.Parameters.AddWithValue("@tdate", transactionDate);
dbManager.Command.Parameters.AddWithValue("@rdescription", responseDescripton);
dbManager.Command.Parameters.AddWithValue("@tnumber", txnRef);
dbManager.LoadDataSet(ds);
}
}
catch (Exception exp)
{
throw;
}
return ds;
}
public string getApplication_Courses(int programID)
{
int count = 0;
DataSet ds = new DataSet();
string courses = string.Empty;
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"select (Select Course from CoursesTable where id=sp.FirstCourse) ,
(Select Course from CoursesTable where id=sp.SecondCourse)
from StudentApplications sp where sp.programID=@ID";
dbManager.Command.Parameters.AddWithValue("@ID", programID);
dbManager.LoadDataSet(ds);
if (ds.Tables[0].Rows.Count > 0)
{
courses = ds.Tables[0].Rows[0][0].ToString();
if (ds.Tables[0].Rows[0][1].ToString() != "")
{
courses = courses + "," + ds.Tables[0].Rows[0][1].ToString();
}
}
}
}
catch (Exception exp)
{
throw;
}
return courses;
}
public DataSet getCourse_paging(int start, int end)
{
DataSet dt = new DataSet();
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"select * from
(select (ROW_NUMBER() OVER (ORDER BY ct.ID ))AS ROW,
ct.ID,Course,CourseDescription,(Select programname from ProgramsTable where ID=pc.ProgramID) as programName from CoursesTable ct
join ProgramCourse_Mapping pc on pc.CourseID=ct.ID
) as res where row between " + start + " and " + end + " ";
dbManager.LoadDataSet(dt);
}
return dt;
}
public int geteWalletBalance(string userID)
{
int balance = 0;
DataSet ds = new DataSet();
try
{
using (DM dbManager = new DM())
{
dbManager.Command.CommandText = @"SELECT CurrentBalance from ewallet where userID=@userID";
dbManager.Command.Parameters.AddWithValue("@userID", userID);
dbManager.LoadDataSet(ds);
if (ds.Tables[0].Rows.Count > 0)
{
balance = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
}
}
}
catch (Exception exp)
{
throw;
}
return balance;
}