Beispiel #1
0
        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);
        }
Beispiel #2
0
        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);
        }
Beispiel #3
0
        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);
        }
Beispiel #4
0
        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);
        }
Beispiel #5
0
        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);
        }
Beispiel #6
0
        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);
        }