public StudentFees GetStudentFeesAccounts(string studUserId, int batchId, string userID) { StudentFees Mod = new StudentFees(); MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlInsert = "SELECT studentfees.id,studentfees.batchid,studentfees.bgroup,studentfees.studuserid,IFNULL(Sum(feevalue),0.00) AS studfess,students.indexNo,students.fname,students.onames,students.sname,IFNULL((SELECT Sum(studentfeepayment.payvalue) FROM studentfeepayment WHERE studentfeepayment.studuserid = studentfees.studuserid AND studentfeepayment.batchid = studentfees.batchid AND studentfeepayment.bgroup = studentfees.bgroup),0) AS feepayments FROM studentfees LEFT OUTER JOIN feescategory ON studentfees.feeid = feescategory.id INNER JOIN students ON studentfees.studuserid = students.userId WHERE studentfees.batchid = @batchid AND students.userId = @studUserId GROUP BY studentfees.bgroup,studentfees.batchid,studentfees.studuserid"; MySqlDataReader dr = null; MySqlCommand cmd; con.Open(); cmd = new MySqlCommand(sqlInsert, con); cmd.Parameters.AddWithValue("@batchid", batchId); cmd.Parameters.AddWithValue("@studUserId", studUserId); dr = cmd.ExecuteReader(); while (dr.Read()) //iterate through the records in the result dataset { //StudentFees Mod = new StudentFees(); Mod.ID = dr.GetInt32(0); Mod.BatchId = dr.GetInt32(1); Mod.Bgroup = dr.GetString(2); Mod.UserId = dr.GetString(3); Mod.xFeevalue = dr.GetDouble(4); Mod.xIndexNo = dr.GetString(5); Mod.xFullName = dr.GetString(6) + " " + dr.GetString(7) + " " + dr.GetString(8); Mod.xPayments = dr.GetDouble(9); Mod.xFeesLeft = dr.GetDouble(4) - dr.GetDouble(9); //retVal.Add(Mod); } con.Close(); return(Mod); }
public StudentFees GetStudentFeesById(int Id, string userID) { StudentFees retVal = new StudentFees(); MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlInsert = "SELECT `id`, `feeid`, `batchid`,`createdate`, `modifydate`,studuserid,bgroup,students.indexNo,CONCAT(students.fname,' ',students.onames,' ',students.sname),studentfees.feevalue,IFNULL((SELECT Sum(studentfeepayment.payvalue) FROM studentfeepayment WHERE studentfeepayment.studuserid = studentfees.studuserid AND studentfeepayment.batchid = studentfees.batchid AND studentfeepayment.bgroup = studentfees.bgroup),0) AS feepayments FROM `studentfees` INNER JOIN students ON studentfees.studuserid = students.userId WHERE id = @Id"; MySqlDataReader dr = null; MySqlCommand cmd; con.Open(); cmd = new MySqlCommand(sqlInsert, con); cmd.Parameters.AddWithValue("@Id", Id); dr = cmd.ExecuteReader(); while (dr.Read()) //iterate through the records in the result dataset { //StudentFees Mod = new StudentFees(); retVal.ID = dr.GetInt32(0); retVal.FeeCateId = dr.GetInt32(1); retVal.BatchId = dr.GetInt32(2); retVal.DateCreted = dr.GetDateTime(3); retVal.LastModified = dr.GetDateTime(4); retVal.UserId = dr.GetString(5); retVal.Bgroup = dr.GetString(6); retVal.xIndexNo = dr.GetString(7); retVal.xFullName = dr.GetString(8); retVal.Feevalue = dr.GetDouble(9); retVal.xPayments = dr.GetDouble(10); retVal.xFeesLeft = dr.GetDouble(9) - dr.GetDouble(10); } con.Close(); return(retVal); }
public List <StudentFees> GetAllStudentFees(string userID) { List <StudentFees> retVal = new List <StudentFees>(); MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlInsert = "SELECT `id`, `feeid`, `batchid`,`createdate`, `modifydate`,studuserid,bgroup,feevalue FROM `studentfees`"; MySqlDataReader dr = null; MySqlCommand cmd; con.Open(); cmd = new MySqlCommand(sqlInsert, con); dr = cmd.ExecuteReader(); while (dr.Read()) //iterate through the records in the result dataset { StudentFees Mod = new StudentFees(); Mod.ID = dr.GetInt32(0); Mod.FeeCateId = dr.GetInt32(1); Mod.BatchId = dr.GetInt32(2); Mod.DateCreted = dr.GetDateTime(3); Mod.LastModified = dr.GetDateTime(4); Mod.UserId = dr.GetString(5); Mod.Bgroup = dr.GetString(6); Mod.Feevalue = dr.GetDouble(7); retVal.Add(Mod); } con.Close(); return(retVal); }
public List <StudentFees> GetStudentFees(string studuserID, int batchId, string userID) { List <StudentFees> retVal = new List <StudentFees>(); MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlInsert = "SELECT studentfees.id,studentfees.feeid,studentfees.batchid,studentfees.bgroup,studentfees.studuserid,studentfees.createdate,studentfees.modifydate,feescategory.title,feevalue,students.indexNo,students.fname,students.onames,students.sname,studentcoursesschedule.schdTitle,studentfees.feevalue FROM studentfees INNER JOIN feescategory ON studentfees.feeid = feescategory.id INNER JOIN students ON studentfees.studuserid = students.userId INNER JOIN studentcoursesschedule ON studentfees.batchid = studentcoursesschedule.id WHERE studentfees.batchid = @batchId AND studentfees.studuserid = @studUserId"; MySqlDataReader dr = null; MySqlCommand cmd; con.Open(); cmd = new MySqlCommand(sqlInsert, con); cmd.Parameters.AddWithValue("@batchId", batchId); cmd.Parameters.AddWithValue("@studUserId", studuserID); dr = cmd.ExecuteReader(); while (dr.Read()) //iterate through the records in the result dataset { StudentFees Mod = new StudentFees(); Mod.ID = dr.GetInt32(0); Mod.FeeCateId = dr.GetInt32(1); Mod.BatchId = dr.GetInt32(2); Mod.Bgroup = dr.GetString(3); Mod.UserId = dr.GetString(4); Mod.DateCreted = dr.GetDateTime(5); Mod.LastModified = dr.GetDateTime(6); Mod.xFeeTitle = dr.GetString(7); Mod.xFeevalue = dr.GetDouble(8); Mod.xIndexNo = dr.GetString(9); Mod.xFullName = dr.GetString(10) + " " + dr.GetString(11) + " " + dr.GetString(12); Mod.xBatchTitle = dr.GetString(13); Mod.Feevalue = dr.GetDouble(14); retVal.Add(Mod); } con.Close(); return(retVal); }
public bool AddStudentFees(StudentFees fp, string userID) { bool result = false; MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlInsert = "INSERT INTO `studentfees`(`feeid`, `batchid`, `createdate`, `modifydate`,studuserid,bgroup,feevalue) VALUES (@feeid,@batchid,@createdate,@modifydate,@studuserid,@bgroup,@feevalue)"; con.Open(); MySqlCommand cmd = new MySqlCommand(sqlInsert, con); cmd.Parameters.AddWithValue("@feeid", fp.FeeCateId); cmd.Parameters.AddWithValue("@batchid", fp.BatchId); cmd.Parameters.AddWithValue("@createdate", fp.DateCreted); cmd.Parameters.AddWithValue("@modifydate", fp.LastModified); cmd.Parameters.AddWithValue("@studuserid", fp.UserId); cmd.Parameters.AddWithValue("@bgroup", fp.Bgroup); cmd.Parameters.AddWithValue("@feevalue", fp.Feevalue); if (cmd.ExecuteNonQuery() > 0) { result = true; } con.Close(); return(result); }
public bool UpdateStudentFees(StudentFees fp, string userID) { bool result = false; MySqlConnection con = new MySqlConnection(DbCon.connectionString); string sqlInsert = "UPDATE `studentfees` SET `feeid`=@feeid,`batchid`=@batchid,`modifydate`=@modifydate,studuserid = @studuserid, bgroup = @bgroup, feevalue =@feevalue WHERE id = @id"; con.Open(); MySqlCommand cmd = new MySqlCommand(sqlInsert, con); cmd.Parameters.AddWithValue("@feeid", fp.FeeCateId); cmd.Parameters.AddWithValue("@batchid", fp.BatchId); cmd.Parameters.AddWithValue("@modifydate", fp.LastModified); cmd.Parameters.AddWithValue("@id", fp.ID); cmd.Parameters.AddWithValue("@studuserid", fp.UserId); cmd.Parameters.AddWithValue("@bgroup", fp.Bgroup); cmd.Parameters.AddWithValue("@feevalue", fp.Feevalue); if (cmd.ExecuteNonQuery() > 0) { result = true; } con.Close(); return(result); }