| Server IP : 103.6.199.200 / Your IP : 3.133.108.47 Web Server : Microsoft-IIS/10.0 System : Windows NT EMPUSA 10.0 build 20348 (Windows Server 2016) i586 User : EMPUSA$ ( 0) PHP Version : 7.4.33 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : C:/Domains/iscommy2/ws.i-3s.com.my/i3sWebServiceStg/ |
Upload File : |
using System;
using System.IO;
using System.Net;
using System.Text;
using System.Web;
using System.Web.Http;
using System.Collections.Generic;
using System.Web.Script.Serialization;
using i3sWebService.Models;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Data;
using i3sAuth;
using System.Net.Mail;
using Newtonsoft.Json;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html.simpleparser;
using System.Globalization;
using System.Web.UI;
namespace i3sWebService
{
public class ClassClass
{
SqlClass Cls = new SqlClass();
public object GetClassList(string SchoolId, string TeacherId, string Type, string Username)
{
var value = string.Empty;
string query = string.Empty;
Class kls = new Class();
List<ClassProperty> dataList = new List<ClassProperty>();
string strConnString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
using (MySqlConnection connection = new MySqlConnection(strConnString))
{
if (Type == "Teacher")
{
query += "SELECT * FROM ";
query += "(";
query += "(SELECT c.CLASSID, c.STAFFID, CONCAT_WS(' ', a.STAFFGIVENNAME, a.STAFFFAMILYNAME) FULLNAME,";
query += "CASE WHEN c.CLASSID IS NOT NULL THEN 2 END NOTICETYPE, ";
query += "c.CLASSNAME, b.SCHOOLID, b.SCHOOLNAME, c.IMAGEURL, c.STATUSCODE ";
query += "FROM tbl_staffs a LEFT JOIN ";
query += "tbl_schools b ON b.SCHOOLID = a.SCHOOLID LEFT JOIN ";
query += "V_CLASSES c ON c.STAFFID = a.STAFFID ";
query += "WHERE a.STATUSCODE = 1) ";
query += "UNION ALL ";
query += "(";
query += "SELECT a.CLUBID CLASSID, a.STAFFID, c.FULLNAME,";
query += "CASE WHEN a.CLUBID IS NOT NULL THEN 3 END NOTICETYPE,";
query += "a.CLUBNAME CLASSNAME, a.SCHOOLID, d.SCHOOLNAME, a.IMAGEURL, a.STATUSCODE ";
query += "FROM V_CLUBS a LEFT JOIN ";
query += "tbl_clubmembers b ON b.CLUBID = a.CLUBID AND b.USERNAME = '" + Username + "' LEFT JOIN ";
query += "tbl_users c ON c.USERNAME = b.USERNAME LEFT JOIN ";
query += "tbl_schools d ON d.SCHOOLID = a.SCHOOLID ";
query += "WHERE a.SCHOOLID = '" + SchoolId + "' AND ";
query += "((a.STAFFID IN (" + TeacherId + ")) OR (b.USERNAME = '" + Username + "')) ";
query += "AND a.STATUSCODE = 1 ";
query += ")) AS T1 ";
query += "WHERE SCHOOLID = '" + SchoolId + "' AND STAFFID IN (" + TeacherId + ") AND STATUSCODE = 1 ";
}
else if (Type == "Headmaster")
{
query += "SELECT * FROM ";
query += "(";
query += "(SELECT b.SCHOOLID CLASSID, a.STAFFID, CONCAT_WS(' ', a.STAFFGIVENNAME, a.STAFFFAMILYNAME) FULLNAME, ";
query += "CASE WHEN b.SCHOOLID IS NOT NULL THEN 1 END NOTICETYPE, ";
query += "b.SCHOOLNAME CLASSNAME, b.SCHOOLID, CONCAT_WS(', ',c.DISTRICT, d.STATE) SCHOOLNAME, b.IMAGEURL, b.STATUSCODE ";
query += "FROM tbl_staffs a LEFT JOIN ";
query += "tbl_schools b ON b.SCHOOLID = a.SCHOOLID LEFT JOIN ";
query += "tbl_districts c ON c.ID = b.DISTRICTID LEFT JOIN ";
query += "tbl_states d ON d.ID = b.STATEID ";
query += "WHERE a.STAFFTYPE = 3 AND a.STAFFID = '" + TeacherId + "' AND ";
query += "a.STATUSCODE = 1 ";
query += ")";
query += "UNION ALL ";
query += "(";
query += "SELECT c.CLASSID, c.STAFFID, CONCAT_WS(' ', a.STAFFGIVENNAME, a.STAFFFAMILYNAME) FULLNAME,";
query += "CASE WHEN c.CLASSID IS NOT NULL THEN 2 END NOTICETYPE, ";
query += "c.CLASSNAME, b.SCHOOLID, b.SCHOOLNAME, c.IMAGEURL, c.STATUSCODE ";
query += "FROM tbl_staffs a LEFT JOIN ";
query += "tbl_schools b ON b.SCHOOLID = a.SCHOOLID LEFT JOIN ";
query += "V_CLASSES c ON c.STAFFID = a.STAFFID ";
query += "WHERE a.STATUSCODE = 1 AND c.STAFFID IN (" + TeacherId + ") ";
query += ")";
query += "UNION ALL ";
query += "(";
query += "SELECT a.CLUBID CLASSID, a.STAFFID, c.FULLNAME,";
query += "CASE WHEN a.CLUBID IS NOT NULL THEN 3 END NOTICETYPE,";
query += "a.CLUBNAME CLASSNAME, a.SCHOOLID, d.SCHOOLNAME, a.IMAGEURL, a.STATUSCODE ";
query += "FROM V_CLUBS a LEFT JOIN ";
query += "tbl_clubmembers b ON b.CLUBID = a.CLUBID AND b.USERNAME = '" + Username + "' LEFT JOIN ";
query += "tbl_users c ON c.USERNAME = b.USERNAME LEFT JOIN ";
query += "tbl_schools d ON d.SCHOOLID = a.SCHOOLID ";
query += "WHERE a.SCHOOLID = '" + SchoolId + "' AND ";
query += "((a.STAFFID IN (" + TeacherId + ")) OR (b.USERNAME = '" + Username + "')) ";
query += "AND a.STATUSCODE = 1 ";
query += ")) AS T1 ";
}
else if (Type == "Staff")
{
query += "SELECT a.CLUBID CLASSID, a.STAFFID, c.FULLNAME, CASE WHEN a.CLUBID IS NOT NULL THEN 3 END NOTICETYPE, ";
query += "a.CLUBNAME CLASSNAME, a.SCHOOLID, d.SCHOOLNAME, a.IMAGEURL, a.STATUSCODE ";
query += "FROM V_CLUBS a LEFT JOIN ";
query += "tbl_clubmembers b ON b.CLUBID = a.CLUBID LEFT JOIN ";
query += "tbl_users c ON c.USERNAME = b.USERNAME LEFT JOIN ";
query += "tbl_schools d ON d.SCHOOLID = a.SCHOOLID ";
query += "WHERE a.SCHOOLID = '" + SchoolId + "' AND ((a.STAFFID IN (" + TeacherId + ")) OR (b.USERNAME = '" + Username + "')) AND a.STATUSCODE = 1 ";
}
MySqlCommand cmd = new MySqlCommand(query, connection);
connection.Open();
JavaScriptSerializer js = new JavaScriptSerializer();
MySqlDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows == true)
{
while (dataReader.Read())
{
ClassProperty prop = new ClassProperty();
prop.ClassId = dataReader["CLASSID"].ToString();
prop.CategoryId = dataReader["NOTICETYPE"].ToString();
prop.ClassName = dataReader["CLASSNAME"].ToString();
prop.SchoolId = dataReader["SCHOOLID"].ToString();
prop.SchoolName = dataReader["SCHOOLNAME"].ToString();
prop.TeacherId = dataReader["STAFFID"].ToString();
prop.TeacherName = dataReader["FULLNAME"].ToString();
prop.ImageUrl = dataReader["IMAGEURL"].ToString();
dataList.Add(prop);
}
kls.Status = "S";
kls.ListClass = dataList;
kls.Remarks = "OK";
}
else
{
kls.Status = "F";
kls.Remarks = "No record found!";
}
//close Data Reader
dataReader.Close();
//close Connection
connection.Close();
return kls;
}
}
public object GetMemberListByClub(string SchoolId, string ClubId)
{
var value = string.Empty;
string query = string.Empty;
Student stu = new Student();
List<StudentProperty> dataList = new List<StudentProperty>();
string strConnString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
using (MySqlConnection connection = new MySqlConnection(strConnString))
{
query += "SELECT * FROM ";
query += "(";
query += "(SELECT a.USERID, c.PARENTGIVENNAME FULLNAME, ";
query += "c.PARENTIMAGEURL IMAGEURL, b.USERNAME, a.USERROLE, ";
query += "c.MYKAD, a.CLUBID, d.SCHOOLID ";
query += "FROM tbl_clubmembers a LEFT JOIN ";
query += "tbl_users b ON b.USERNAME = a.USERNAME LEFT JOIN ";
query += "V_PARENTS c ON c.MYKAD = a.USERNAME LEFT JOIN ";
query += "tbl_clubs d ON d.CLUBID = a.CLUBID ";
query += "WHERE d.SCHOOLID = '" + SchoolId + "' AND ";
query += "a.USERROLE = 'Parent' AND ";
query += "a.CLUBID = '" + ClubId + "' AND a.STATUSCODE = 1 ";
query += ") ";
query += "UNION ALL ";
query += "(";
query += "SELECT a.USERID, CONCAT_WS(' ', c.STAFFGIVENNAME, c.STAFFFAMILYNAME)FULLNAME, ";
query += "c.STAFFIMAGEURL IMAGEURL, b.USERNAME, a.USERROLE, ";
query += "c.MYKAD, a.CLUBID, d.SCHOOLID ";
query += "FROM tbl_clubmembers a LEFT JOIN ";
query += "tbl_users b ON b.USERNAME = a.USERNAME LEFT JOIN ";
query += "tbl_staffs c ON c.MYKAD = a.USERNAME LEFT JOIN ";
query += "tbl_clubs d ON d.CLUBID = a.CLUBID ";
query += "WHERE d.SCHOOLID = '" + SchoolId + "' AND ";
query += "a.USERROLE = 'Staff' AND ";
query += "a.CLUBID = '" + ClubId + "' AND a.STATUSCODE = 1 ";
query += ")";
query += "UNION ALL ";
query += "(";
query += "SELECT b.STUDENTID USERID, CONCAT_WS(' ', b.STUDENTGIVENNAME, b.STUDENTFAMILYNAME) FULLNAME, ";
query += "b.STUDENTIMAGEURL IMAGEURL, b.MYKID USERNAME, a.USERROLE, b.MYKID MYKAD, a.CLUBID, b.SCHOOLID ";
query += "FROM tbl_clubmembers a LEFT JOIN ";
query += "tbl_students b ON b.MYKID = a.USERNAME LEFT JOIN ";
query += "tbl_clubs c ON c.CLUBID = a.CLUBID ";
query += "WHERE c.SCHOOLID = '" + SchoolId + "' AND ";
query += "a.USERROLE = 'Student' AND ";
query += "a.CLUBID = '" + ClubId + "' AND a.STATUSCODE = 1";
query += ")) AS T1 ";
MySqlCommand cmd = new MySqlCommand(query, connection);
connection.Open();
JavaScriptSerializer js = new JavaScriptSerializer();
MySqlDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows == true)
{
while (dataReader.Read())
{
StudentProperty prop = new StudentProperty();
prop.StudentId = dataReader["USERID"].ToString();
prop.StudentName = dataReader["FULLNAME"].ToString();
prop.StudentNo = dataReader["USERROLE"].ToString();
prop.ICNo = dataReader["MYKAD"].ToString();
prop.ClassId = dataReader["CLUBID"].ToString();
prop.SchoolId = dataReader["SCHOOLID"].ToString();
prop.ImageUrl = dataReader["IMAGEURL"].ToString();
dataList.Add(prop);
}
stu.Status = "S";
stu.ListStudent = dataList;
stu.Remarks = "OK";
}
else
{
stu.Status = "F";
stu.Remarks = "No record found!";
}
//close Data Reader
dataReader.Close();
//close Connection
connection.Close();
return stu;
}
}
public object GetStudentListByClass(string SchoolId, string ClassId)
{
var value = string.Empty;
string query = string.Empty;
Student stu = new Student();
List<StudentProperty> dataList = new List<StudentProperty>();
string strConnString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
using (MySqlConnection connection = new MySqlConnection(strConnString))
{
query += "SELECT STUDENTID, CONCAT_WS(' ', STUDENTGIVENNAME, STUDENTFAMILYNAME) STUDENTNAME, ";
query += "STUDENTIMAGEURL, STUDENTNO, MYKID, CLASSID, SCHOOLID FROM tbl_students ";
query += "WHERE SCHOOLID = '" + SchoolId + "' AND CLASSID = '" + ClassId + "' AND STATUSCODE = 1 ";
MySqlCommand cmd = new MySqlCommand(query, connection);
connection.Open();
JavaScriptSerializer js = new JavaScriptSerializer();
MySqlDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows == true)
{
while (dataReader.Read())
{
StudentProperty prop = new StudentProperty();
prop.StudentId = dataReader["STUDENTID"].ToString();
prop.StudentName = dataReader["STUDENTNAME"].ToString();
prop.StudentNo = dataReader["STUDENTNO"].ToString();
prop.ICNo = dataReader["MYKID"].ToString();
prop.ClassId = dataReader["CLASSID"].ToString();
prop.SchoolId = dataReader["SCHOOLID"].ToString();
prop.ImageUrl = dataReader["STUDENTIMAGEURL"].ToString();
dataList.Add(prop);
}
stu.Status = "S";
stu.ListStudent = dataList;
stu.Remarks = "OK";
}
else
{
stu.Status = "F";
stu.Remarks = "No record found!";
}
//close Data Reader
dataReader.Close();
//close Connection
connection.Close();
return stu;
}
}
public object GetByCategoryNoticeList(string SchoolId, string ClassId, string TeacherId, string CategoryId)
{
var value = string.Empty;
string query = string.Empty;
Class kls = new Class();
List<ClassProperty> dataList = new List<ClassProperty>();
string strConnString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
using (MySqlConnection connection = new MySqlConnection(strConnString))
{
query += "SELECT a.NOTICEID, a.SCHOOLID, a.CCID, b.STAFFID, a.NOTICES, a.ATTACHMENTURL, b.STAFFIMAGEURL IMAGEURL, ";
query += "CONCAT_WS(' ', b.STAFFGIVENNAME, b.STAFFFAMILYNAME) FULLNAME, DATE_FORMAT(a.CREATEDDATE, '%M %d, %Y') CREATEDDATE ";
query += "FROM tbl_notices a ";
query += "LEFT JOIN tbl_staffs b ON b.STAFFID = a.STAFFID ";
//query += "LEFT JOIN tbl_classes c ON c.STAFFID = b.STAFFID ";
query += "WHERE a.SCHOOLID = '" + SchoolId + "' AND a.STATUSCODE = 1 AND ";
if (!string.IsNullOrEmpty(ClassId))
{
query += "a.CCID = '" + ClassId + "' AND ";
}
//if (!string.IsNullOrEmpty(TeacherId))
//{
// query += "b.STAFFID = '" + TeacherId + "' AND ";
//}
if (!string.IsNullOrEmpty(CategoryId))
{
query += "a.NOTICETYPE IN (" + CategoryId + ") AND ";
}
query = query.Remove(query.Length - 4);
query += "ORDER BY a.CREATEDDATE DESC ";
MySqlCommand cmd = new MySqlCommand(query, connection);
connection.Open();
JavaScriptSerializer js = new JavaScriptSerializer();
MySqlDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows == true)
{
while (dataReader.Read())
{
ClassProperty prop = new ClassProperty();
prop.TeacherId = dataReader["STAFFID"].ToString();
prop.NoticeId = dataReader["NOTICEID"].ToString();
prop.SchoolId = dataReader["SCHOOLID"].ToString();
prop.Notice = dataReader["NOTICES"].ToString();
prop.TeacherName = dataReader["FULLNAME"].ToString();
prop.CreatedDate = dataReader["CREATEDDATE"].ToString();
prop.AttachmentUrl = dataReader["ATTACHMENTURL"].ToString();
prop.ImageUrl = dataReader["IMAGEURL"].ToString();
dataList.Add(prop);
}
kls.Status = "S";
kls.ListClass = dataList;
kls.Remarks = "OK";
}
else
{
kls.Status = "F";
kls.Remarks = "No record found!";
}
//close Data Reader
dataReader.Close();
//close Connection
connection.Close();
return kls;
}
}
public object GetClassAttendanceList(string SchoolId, string ClassId, string strDate) //special dates
{
var value = string.Empty;
string query = string.Empty;
string[] splitDt = strDate.Split('-');
var sYear = splitDt[0];
var sMonth = splitDt[1];
Attendance cls = new Attendance();
List<AttendanceProperty> dataList = new List<AttendanceProperty>();
string strConnString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
using (MySqlConnection connection = new MySqlConnection(strConnString))
{
query += "SELECT DATEOFENTRY, YEAR,MONTH,DAY,TOTAL_STUDENT,TOTAL_ATTEND,TOTAL_LATEIN,TOTAL_HALFDAY,TOTAL_ABSENT,";
query += "CONCAT(ATTEND_DESC,LATEIN_DESC,HALFDAY_DESC,ABSENT_DESC) DESCRIPTION,PERCENTAGE ";
query += "FROM (SELECT b.DATEOFENTRY, ";
query += "DATE_FORMAT(b.DATEOFENTRY,'%Y') YEAR, ";
query += "DATE_FORMAT(b.DATEOFENTRY, '%m') MONTH, ";
query += "DATE_FORMAT(b.DATEOFENTRY, '%d') DAY, ";
query += "COUNT(a.STUDENTID) TOTAL_STUDENT, ";
query += "SUM(CASE WHEN b.ATTENDANCE = 1 THEN 1 ELSE 0 END) TOTAL_ATTEND, ";
query += "SUM(CASE WHEN b.ATTENDANCE = 2 THEN 1 ELSE 0 END) TOTAL_LATEIN, ";
query += "SUM(CASE WHEN b.ATTENDANCE = 3 THEN 1 ELSE 0 END) TOTAL_ABSENT, ";
query += "SUM(CASE WHEN b.ATTENDANCE = 4 THEN 1 ELSE 0 END) TOTAL_HALFDAY, ";
query += "CASE WHEN SUM(CASE WHEN b.ATTENDANCE = 1 THEN 1 ELSE 0 END) > 0 THEN 'P' ELSE '' END ATTEND_DESC, ";
query += "CASE WHEN SUM(CASE WHEN b.ATTENDANCE = 2 THEN 1 ELSE 0 END) > 0 THEN 'L' ELSE '' END LATEIN_DESC, ";
query += "CASE WHEN SUM(CASE WHEN b.ATTENDANCE = 4 THEN 1 ELSE 0 END) > 0 THEN 'H' ELSE '' END HALFDAY_DESC, ";
query += "CASE WHEN SUM(CASE WHEN b.ATTENDANCE = 3 THEN 1 ELSE 0 END) > 0 THEN 'A' ELSE '' END ABSENT_DESC, ";
query += "((SUM(CASE WHEN b.ATTENDANCE = 1 THEN 1 ELSE 0 END) / COUNT(a.STUDENTID)) * 100) PERCENTAGE ";
query += "FROM tbl_students a LEFT JOIN ";
query += "tbl_attendancereport b ON b.STUDENTID = a.STUDENTID ";
query += "WHERE a.CLASSID = '" + ClassId + "' AND a.SCHOOLID = '" + SchoolId + "' AND b.DATEOFENTRY LIKE '%" + strDate + "%' ";
query += "GROUP BY b.DATEOFENTRY) AS T1 ";
MySqlCommand cmd = new MySqlCommand(query, connection);
connection.Open();
JavaScriptSerializer js = new JavaScriptSerializer();
MySqlDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows == true)
{
while (dataReader.Read())
{
AttendanceProperty prop = new AttendanceProperty();
prop.DateOfEntry = dataReader["DATEOFENTRY"].ToString();
prop.Year = dataReader["YEAR"].ToString();
prop.Month = dataReader["MONTH"].ToString();
prop.Day = dataReader["DAY"].ToString();
prop.AttendanceDesc = dataReader["DESCRIPTION"].ToString();
dataList.Add(prop);
}
cls.Status = "S";
cls.ListAttendance = dataList;
cls.Remarks = "OK";
}
else
{
cls.Status = "F";
cls.Remarks = "No record found!";
}
}
return cls;
}
public object GetClassAttendanceSummary(string SchoolId, string ClassId, string EntryDate)
{
var value = string.Empty;
string query = string.Empty;
string[] splitDt = EntryDate.Split('-');
var sYear = splitDt[2];
var sMonth = splitDt[1];
var sDay = splitDt[0];
Attendance cls = new Attendance();
List<AttendanceProperty> dataList = new List<AttendanceProperty>();
string strConnString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
using (MySqlConnection connection = new MySqlConnection(strConnString))
{
query += "SELECT b.DATEOFENTRY, ";
query += "COUNT(a.STUDENTID) TOTAL_STUDENT, ";
query += "SUM(CASE WHEN b.ATTENDANCE = 1 THEN 1 ELSE 0 END) TOTAL_ATTEND, ";
query += "SUM(CASE WHEN b.ATTENDANCE = 2 THEN 1 ELSE 0 END) TOTAL_LATEIN, ";
query += "SUM(CASE WHEN b.ATTENDANCE = 3 THEN 1 ELSE 0 END) TOTAL_ABSENT, ";
query += "SUM(CASE WHEN b.ATTENDANCE = 4 THEN 1 ELSE 0 END) TOTAL_HALFDAY, ";
query += "((SUM(CASE WHEN b.ATTENDANCE = 1 THEN 1 ELSE 0 END) / COUNT(a.STUDENTID)) * 100) PERCENTAGE, ";
query += "CONCAT_WS('/', (COUNT(a.STUDENTID) - SUM(CASE WHEN b.ATTENDANCE = 3 THEN 1 ELSE 0 END)), COUNT(a.STUDENTID)) TOTAL ";
query += "FROM tbl_students a LEFT JOIN tbl_attendancereport b ON b.STUDENTID = a.STUDENTID ";
query += "WHERE a.CLASSID = '" + ClassId + "' AND a.SCHOOLID = '" + SchoolId + "' AND ";
query += "b.DATEOFENTRY LIKE '%" + sYear + "-" + sMonth + "-" + sDay + "%' GROUP BY b.DATEOFENTRY ";
try
{
MySqlCommand cmd = new MySqlCommand(query, connection);
connection.Open();
JavaScriptSerializer js = new JavaScriptSerializer();
MySqlDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows == true)
{
while (dataReader.Read())
{
AttendanceProperty prop = new AttendanceProperty();
prop.DateOfEntry = dataReader["DATEOFENTRY"].ToString();
prop.TotalAttend = dataReader["TOTAL_ATTEND"].ToString();
prop.TotalInLate = dataReader["TOTAL_LATEIN"].ToString();
prop.TotalAbsent = dataReader["TOTAL_ABSENT"].ToString();
prop.TotalHalfDay = dataReader["TOTAL_HALFDAY"].ToString();
prop.TotalPercent = dataReader["PERCENTAGE"].ToString();
prop.TotalStudent = dataReader["TOTAL_STUDENT"].ToString();
dataList.Add(prop);
}
cls.Status = "S";
cls.ListAttendance = dataList;
cls.Remarks = "OK";
}
else
{
cls.Status = "F";
cls.Remarks = "No record found!";
}
}
catch (Exception ex)
{
cls.Status = "F";
cls.Remarks = ex.Message;
}
}
return cls;
}
public object GetAttendanceByClassList(string SchoolId, string ClassId, string EntryDate, string AttendDesc)
{
var value = string.Empty;
string query = string.Empty;
Student stu = new Student();
List<StudentProperty> dataList = new List<StudentProperty>();
string strConnString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
using (MySqlConnection connection = new MySqlConnection(strConnString))
{
query += "SELECT a.STUDENTID, CONCAT_WS(' ',a.STUDENTGIVENNAME, a.STUDENTFAMILYNAME) FULLNAME, ";
query += "b.ATTENDANCE, c.DESCRIPTION ATTENDANCEDESC, ";
query += "DATE_FORMAT(DATE(b.DATEOFENTRY), '%d-%m-%Y') DATEOFENTRY, ";
query += "TIME_FORMAT(TIME(b.DATEOFENTRY), '%r') TIMEOFENTRY, ";
query += "b.REASONFORABSENT, d.DESCRIPTION REASONFORABSENTDESC, a.STUDENTIMAGEURL ";
query += "FROM tbl_students a LEFT JOIN ";
query += "tbl_attendancereport b ON b.STUDENTID = a.STUDENTID LEFT JOIN ";
query += "tbl_lookupstatus c ON c.CODE = b.ATTENDANCE AND c.TABLENAME = 'ATTENDANCE' LEFT JOIN ";
query += "tbl_lookupstatus d ON d.CODE = b.REASONFORABSENT AND d.TABLENAME = 'REASONFORABSENT' ";
if (AttendDesc == "All")
{
query += "WHERE a.SCHOOLID = '" + SchoolId + "' AND a.CLASSID = '" + ClassId + "' AND ";
}
else {
query += "WHERE a.SCHOOLID = '" + SchoolId + "' AND a.CLASSID = '" + ClassId + "' AND c.DESCRIPTION = '" + AttendDesc + "' AND ";
}
query += "DATE_FORMAT(b.DATEOFENTRY, '%d-%m-%Y') = '" + EntryDate + "' ORDER BY FULLNAME";
try
{
MySqlCommand cmd = new MySqlCommand(query, connection);
connection.Open();
JavaScriptSerializer js = new JavaScriptSerializer();
MySqlDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows == true)
{
while (dataReader.Read())
{
StudentProperty prop = new StudentProperty();
prop.StudentId = dataReader["STUDENTID"].ToString();
prop.StudentName = dataReader["FULLNAME"].ToString();
prop.AttendanceId = dataReader["ATTENDANCE"].ToString();
prop.AttendanceDesc = dataReader["ATTENDANCEDESC"].ToString();
prop.DateOfEntry = dataReader["DATEOFENTRY"].ToString();
prop.TimeOfEntry = dataReader["TIMEOFENTRY"].ToString();
prop.ReasonId = dataReader["REASONFORABSENT"].ToString();
prop.ReasonForAbsent = dataReader["REASONFORABSENTDESC"].ToString();
prop.ImageUrl = dataReader["STUDENTIMAGEURL"].ToString();
dataList.Add(prop);
}
stu.Status = "S";
stu.ListStudent = dataList;
stu.Remarks = "OK";
}
else
{
stu.Status = "F";
stu.Remarks = "No record found!";
}
dataReader.Close();
}
catch (Exception ex)
{
stu.Status = "F";
stu.Remarks = ex.Message;
}
connection.Close();
}
return stu;
}
public object UpdateClassAttendance(string StudentId, string ActionId, string ReasonId, string DateOfEntry)
{
string query = "";
var value = string.Empty;
string strConnString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
List<StudentProperty> dataList = new List<StudentProperty>();
Student stu = new Student();
using (MySqlConnection connection = new MySqlConnection(strConnString))
{
query += "SELECT * FROM tbl_attendancereport ";
query += "WHERE STUDENTID='" + StudentId + "' AND ";
query += "DATE_FORMAT(DATEOFENTRY,'%d-%m-%Y')='" + DateOfEntry + "' ";
try
{
MySqlCommand cmd = new MySqlCommand(query, connection);
string strSql = "";
connection.Open();
MySqlDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows == true)
{
strSql += "UPDATE tbl_attendancereport SET ";
strSql += "ATTENDANCE =" + ActionId + " , ";
if (!string.IsNullOrEmpty(ReasonId))
{
strSql += "REASONFORABSENT = " + ReasonId + " ";
}
else {
strSql += "REASONFORABSENT = NULL ";
}
strSql += "WHERE STUDENTID=" + StudentId + " AND ";
strSql += "DATE_FORMAT(DATEOFENTRY,'%d-%m-%Y')='" + DateOfEntry + "' ";
try
{
if (dataReader != null) dataReader.Close();
MySqlCommand cmd2 = new MySqlCommand(strSql, connection);
//connection.Open();
int i = 0;
i = cmd2.ExecuteNonQuery();
if (i > 0)
{
stu.success = true;
stu.message = "Attendance updated successfully";
}
cmd2.Dispose();
connection.Close();
}
catch (Exception ex)
{
stu.success = false;
stu.message = ex.Message;
}
}
}
catch (Exception ex)
{
stu.success = false;
stu.message = ex.Message;
}
return stu;
}
}
public string GetLeaveClass(string SchoolId, string Type, string CCId, string StaffId, string Name)
{
string val = string.Empty;
string query = string.Empty;
string strTbl = string.Empty;
string strStaffId = string.Empty;
string strId = string.Empty;
string strConn = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
if (Type == "2")
{
DataTable dtCls = GetClasses(CCId, strConn);
if (dtCls.Rows.Count > 0)
{
foreach (DataRow row in dtCls.Rows)
{
strStaffId = Convert.ToString(row["STAFFID"]);
}
}
}
else if (Type == "3")
{
DataTable dtClb = GetClubs(CCId, strConn);
if (dtClb.Rows.Count > 0)
{
foreach (DataRow row in dtClb.Rows)
{
strStaffId = Convert.ToString(row["STAFFID"]);
}
}
}
strStaffId = strStaffId + ",";
strId = StaffId + ",";
strStaffId = strStaffId.Replace(strId, "");
if (strStaffId.Length > 0)
{
strStaffId = strStaffId.Substring(0, strStaffId.Length - 1);
}
if (Type == "2")
{
query = "UPDATE tbl_classes SET ";
query += "UPDATEDBY = '" + Name + "', ";
query += "UPDATEDDATE = NOW(), ";
query += "STAFFID = '" + strStaffId + "' ";
query += "WHERE CLASSID = '" + CCId + "' AND SCHOOLID = '" + SchoolId + "' ";
}
else if (Type == "3")
{
query = "UPDATE tbl_clubs SET ";
query += "UPDATEDBY = '" + Name + "', ";
query += "UPDATEDDATE = NOW(), ";
query += "STAFFID = '" + strStaffId + "' ";
query += "WHERE CLUBID = '" + CCId + "' AND SCHOOLID = '" + SchoolId + "' ";
}
string strConnString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
using (MySqlConnection connection = new MySqlConnection(strConnString))
{
try
{
MySqlCommand cmd = new MySqlCommand(query, connection);
connection.Open();
int i = 0;
i = cmd.ExecuteNonQuery();
if (i > 0)
{
//query = "";
if (Type == "2")
{
//query += "SELECT STUDENTID FROM tbl_students ";
//query += "WHERE SCHOOLID = '" + SchoolId + "' AND CLASSID = '" + CCId + "' ";
//MySqlCommand cmd2 = new MySqlCommand(query, connection);
//cmd.Dispose();
//MySqlDataReader dataReader = cmd.ExecuteReader();
//if (dataReader.HasRows == true)
//{
// query += "UPDATE tbl_students SET ";
// query += "CLASSID = NULL ";
// query += "WHERE SCHOOLID = '" + SchoolId + "' AND CLASSID = '" + CCId + "' ";
// try
// {
// MySqlCommand cmd3 = new MySqlCommand(query, connection);
// if (dataReader != null) dataReader.Close();
// int k = 0;
// k = cmd3.ExecuteNonQuery();
// if (k > 0)
// {
// val = "S#You have successfully leave the class";
// }
// }
// catch (Exception ex)
// {
// val = "F#" + ex.ToString();
// }
//}
//else
//{
val = "S#You have successfully leave the class";
//}
}
else if (Type == "3")
{
val = "S#You have successfully leave the club";
}
}
}
catch (Exception ex)
{
val = "F#" + ex.ToString();
}
connection.Close();
}
return val;
}
public DataTable GetClasses(string ClassId, string strConn)
{
string strSQL = "";
strSQL += "SELECT * FROM tbl_classes ";
if (!string.IsNullOrEmpty(ClassId))
{
strSQL += "WHERE CLASSID = '" + ClassId + "' AND ";
}
strSQL = strSQL.Substring(0, strSQL.Length - 4);
strSQL += "ORDER BY CLASSNAME";
return Cls.GetDataTable(strSQL, strConn);
}
public DataTable GetClubs(string ClubId, string strConn)
{
string strSQL = "";
strSQL += "SELECT * FROM tbl_clubs ";
if (!string.IsNullOrEmpty(ClubId))
{
strSQL += "WHERE CLUBID = '" + ClubId + "' AND ";
}
strSQL = strSQL.Substring(0, strSQL.Length - 4);
strSQL += "ORDER BY CLUBNAME";
return Cls.GetDataTable(strSQL, strConn);
}
public string PostNewPost(NoticeProperty notice)
{
AndroidGCMPushNotification gcm = new AndroidGCMPushNotification();
PushNotificationApple apns = new PushNotificationApple();
string value = string.Empty;
string FullName = string.Empty;
string NoticeId = string.Empty;
string query = string.Empty;
string colsId = string.Empty;
string colsName = string.Empty;
string SpecialId = string.Empty;
string SpecialName = string.Empty;
if (notice.CategoryId == "2")
{
query += "SELECT b.CLASSID, b.CLASSNAME, CONCAT_WS(' ', a.STAFFGIVENNAME, a.STAFFFAMILYNAME) FULLNAME ";
query += "FROM tbl_staffs a LEFT JOIN ";
query += "tbl_classes b ON b.STAFFID = a.STAFFID ";
query += "WHERE a.STAFFID = '" + notice.TeacherId + "' ";
colsId = "CLASSID";
colsName = "CLASSNAME";
}
else if (notice.CategoryId == "3")
{
query += "SELECT b.CLUBID, b.CLUBNAME, CONCAT_WS(' ', a.STAFFGIVENNAME, a.STAFFFAMILYNAME) FULLNAME ";
query += "FROM tbl_staffs a LEFT JOIN ";
query += "tbl_clubs b ON b.STAFFID = a.STAFFID ";
query += "WHERE a.STAFFID = '" + notice.TeacherId + "' AND a.STATUSCODE = 1 ";
colsId = "CLUBID";
colsName = "CLUBNAME";
}
else {
query += "SELECT a.SCHOOLID, b.SCHOOLNAME, CONCAT_WS(' ', a.STAFFGIVENNAME, a.STAFFFAMILYNAME) FULLNAME ";
query += "FROM tbl_staffs a LEFT JOIN ";
query += "tbl_schools b ON b.SCHOOLID = a.SCHOOLID ";
query += "WHERE a.STAFFID = '" + notice.TeacherId + "' AND a.STATUSCODE = 1 ";
colsId = "SCHOOLID";
colsName = "SCHOOLNAME";
}
string strConnString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
using (MySqlConnection connection = new MySqlConnection(strConnString))
{
try
{
MySqlCommand cmd = new MySqlCommand(query, connection);
connection.Open();
MySqlDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows == true)
{
while (dataReader.Read())
{
SpecialId = dataReader[colsId].ToString();
SpecialName = dataReader[colsName].ToString();
FullName = dataReader["FULLNAME"].ToString();
}
string strCCId;
if (notice.CategoryId == "1")
{
strCCId = "NULL";
}
else {
strCCId = notice.CCId;
}
query = "";
query += "INSERT INTO tbl_notices(SCHOOLID, CCID, STAFFID, NOTICES, NOTICETYPE, CREATEDBY, CREATEDDATE, STATUSCODE) VALUES";
query += "(";
query += "" + notice.SchoolId + ", " + strCCId + ", " + notice.TeacherId + ", '" + notice.PostMessage + "', " + notice.CategoryId + ", '" + FullName + "', NOW(), 1";
query += ")";
try
{
if (dataReader != null) dataReader.Close();
MySqlCommand cmd2 = new MySqlCommand(query, connection);
int i = 0;
i = cmd2.ExecuteNonQuery();
if (i > 0)
{
cmd2.Dispose();
if (!string.IsNullOrEmpty(notice.Base64))
{
query = "";
query += "SELECT NOTICEID FROM tbl_notices ";
query += "WHERE SCHOOLID = " + notice.SchoolId + " AND ";
query += "STAFFID = " + notice.TeacherId + " AND ";
query += "NOTICETYPE = " + notice.CategoryId + " ORDER BY CREATEDDATE DESC LIMIT 1 ";
try
{
MySqlCommand cmd3 = new MySqlCommand(query, connection);
MySqlDataReader dataReader2 = cmd3.ExecuteReader();
if (dataReader2.HasRows == true)
{
while (dataReader2.Read())
{
NoticeId = dataReader2["NOTICEID"].ToString();
}
byte[] fileBytes = Convert.FromBase64String(notice.Base64);
string directory = "ftp://ftp.i-3s.com.my/ws.i-3s.com.my/i3sWebServiceStg/Images/Notice/" + NoticeId;
string pass = "49GvqVrr47!";
string user = "iscommy2";
if (CheckIfDirectoryExists(directory, user, pass) == false)
{
WebRequest request = WebRequest.Create("ftp://ftp.i-3s.com.my/ws.i-3s.com.my/i3sWebServiceStg/Images/Notice/" + NoticeId);
request.Credentials = new NetworkCredential(user, pass);
request.Method = WebRequestMethods.Ftp.MakeDirectory;
FtpWebResponse makeDirectoryResponse = (FtpWebResponse)request.GetResponse();
if (makeDirectoryResponse.StatusCode == FtpStatusCode.PathnameCreated)
{
try
{
//Create FTP Request.
FtpWebRequest ftprequest = (FtpWebRequest)WebRequest.Create("ftp://ftp.i-3s.com.my/ws.i-3s.com.my/i3sWebServiceStg/Images/Notice/" + NoticeId + "/" + notice.FileName);
ftprequest.Method = WebRequestMethods.Ftp.UploadFile;
//Enter FTP Server credentials.
ftprequest.Credentials = new NetworkCredential(user, pass);
ftprequest.ContentLength = fileBytes.Length;
ftprequest.UsePassive = true;
ftprequest.UseBinary = true;
ftprequest.ServicePoint.ConnectionLimit = fileBytes.Length;
ftprequest.EnableSsl = false;
using (Stream requestStream = ftprequest.GetRequestStream())
{
requestStream.Write(fileBytes, 0, fileBytes.Length);
requestStream.Close();
}
FtpWebResponse ftpresponse = (FtpWebResponse)ftprequest.GetResponse();
//lblMessage.Text += fileName + " uploaded.<br />";
ftpresponse.Close();
}
catch (WebException ex)
{
throw new Exception((ex.Response as FtpWebResponse).StatusDescription);
}
}
}
else
{
try
{
//Create FTP Request.
FtpWebRequest ftprequest = (FtpWebRequest)WebRequest.Create("ftp://ftp.i-3s.com.my/ws.i-3s.com.my/i3sWebServiceStg/Images/Notice/" + NoticeId + "/" + notice.FileName);
ftprequest.Method = WebRequestMethods.Ftp.UploadFile;
//Enter FTP Server credentials.
ftprequest.Credentials = new NetworkCredential(user, pass);
ftprequest.ContentLength = fileBytes.Length;
ftprequest.UsePassive = true;
ftprequest.UseBinary = true;
ftprequest.ServicePoint.ConnectionLimit = fileBytes.Length;
ftprequest.EnableSsl = false;
using (Stream requestStream = ftprequest.GetRequestStream())
{
requestStream.Write(fileBytes, 0, fileBytes.Length);
requestStream.Close();
}
FtpWebResponse ftpresponse = (FtpWebResponse)ftprequest.GetResponse();
//lblMessage.Text += fileName + " uploaded.<br />";
ftpresponse.Close();
}
catch (WebException ex)
{
throw new Exception((ex.Response as FtpWebResponse).StatusDescription);
}
}
cmd3.Dispose();
query = "";
query += "UPDATE tbl_notices SET ";
query += "ATTACHMENTURL = '/Images/Notice/" + NoticeId + "/" + notice.FileName + "' ";
query += "WHERE NOTICEID = '" + NoticeId + "' ";
query += "";
try
{
if (dataReader2 != null) dataReader2.Close();
MySqlCommand cmd4 = new MySqlCommand(query, connection);
int j = 0;
j = cmd4.ExecuteNonQuery();
if (j > 0)
{
string DeviceId = string.Empty;
string DeviceModel = string.Empty;
cmd4.Dispose();
//send push notification
query = "";
if (notice.CategoryId == "2") //Class notify
{
query += "SELECT a.USERID, a.DEVICETOKEN, a.DEVICEMODEL ";
query += "FROM tbl_userdevicetoken a LEFT JOIN ";
query += "V_PARENTS b ON b.MYKAD = a.USERNAME LEFT JOIN ";
query += "tbl_students c ON c.PARENTID = b.PARENTID ";
query += "WHERE c.SCHOOLID = '" + notice.SchoolId + "' AND c.CLASSID = '" + SpecialId + "' ";
query += "GROUP BY a.USERID, a.DEVICETOKEN, a.DEVICEMODEL ";
}
else if (notice.CategoryId == "3") //club notify
{
query += "SELECT a.USERID, a.DEVICETOKEN, a.DEVICEMODEL ";
query += "FROM tbl_userdevicetoken a LEFT JOIN ";
query += "tbl_clubmembers b ON b.USERID = a.USERID LEFT JOIN ";
query += "tbl_clubs c ON c.CLUBID = b.CLUBID ";
query += "WHERE c.SCHOOLID = '" + notice.SchoolId + "' AND b.CLUBID = '" + SpecialId + "' ";
query += "GROUP BY USERID, DEVICETOKEN, DEVICEMODEL ";
}
else if (notice.CategoryId == "1") //school
{
query += "SELECT a.USERID, a.DEVICETOKEN, a.DEVICEMODEL ";
query += "FROM tbl_userdevicetoken a LEFT JOIN ";
query += "V_PARENTS b ON b.MYKAD = a.USERNAME LEFT JOIN ";
query += "tbl_students c ON c.PARENTID = b.PARENTID ";
query += "WHERE c.SCHOOLID = '" + notice.SchoolId + "' ";
query += "GROUP BY USERID, DEVICETOKEN, DEVICEMODEL ";
}
try
{
MySqlCommand cmd5 = new MySqlCommand(query, connection);
//connection.Open();
MySqlDataReader dataReader3 = cmd5.ExecuteReader();
if (dataReader3.HasRows == true)
{
while (dataReader3.Read())
{
DeviceId = dataReader3["DEVICETOKEN"].ToString();
DeviceModel = dataReader3["DEVICEMODEL"].ToString();
if ((notice.CategoryId == "2") || (notice.CategoryId == "1"))
{
if (DeviceModel == "Android")
{
string response = gcm.SendNotification(DeviceId, notice.Notice, "notice", notice.CreatedBy);
}
else if (DeviceModel == "iOS")
{
string response = apns.pushMessage(DeviceId, notice.Notice, "notice", notice.CreatedBy);
}
}
}
}
cmd5.Dispose();
}
catch (Exception ex)
{
value = ex.ToString();
}
value = "S#Message successfully posted";
}
}
catch (Exception ex)
{
value = ex.ToString();
}
}
}
catch (Exception ex)
{
value = ex.ToString();
}
}
else {
string DeviceId = string.Empty;
string DeviceModel = string.Empty;
//send push notification
query = "";
if (notice.CategoryId == "2") //Class notify
{
query += "SELECT a.USERID, a.DEVICETOKEN, a.DEVICEMODEL ";
query += "FROM tbl_userdevicetoken a LEFT JOIN ";
query += "V_PARENTS b ON b.MYKAD = a.USERNAME LEFT JOIN ";
query += "tbl_students c ON c.PARENTID = b.PARENTID ";
query += "WHERE c.SCHOOLID = '" + notice.SchoolId + "' AND c.CLASSID = '" + SpecialId + "' ";
query += "GROUP BY a.USERID, a.DEVICETOKEN, a.DEVICEMODEL ";
}
else if (notice.CategoryId == "3") //club notify
{
query += "SELECT a.USERID, a.DEVICETOKEN, a.DEVICEMODEL ";
query += "FROM tbl_userdevicetoken a LEFT JOIN ";
query += "tbl_clubmembers b ON b.USERID = a.USERID LEFT JOIN ";
query += "tbl_clubs c ON c.CLUBID = b.CLUBID ";
query += "WHERE c.SCHOOLID = '" + notice.SchoolId + "' AND b.CLUBID = '" + SpecialId + "' ";
query += "GROUP BY USERID, DEVICETOKEN, DEVICEMODEL ";
}
else if (notice.CategoryId == "1") //school
{
query += "SELECT a.USERID, a.DEVICETOKEN, a.DEVICEMODEL ";
query += "FROM tbl_userdevicetoken a LEFT JOIN ";
query += "V_PARENTS b ON b.MYKAD = a.USERNAME LEFT JOIN ";
query += "tbl_students c ON c.PARENTID = b.PARENTID ";
query += "WHERE c.SCHOOLID = '" + notice.SchoolId + "' ";
query += "GROUP BY USERID, DEVICETOKEN, DEVICEMODEL ";
}
try
{
MySqlCommand cmd3 = new MySqlCommand(query, connection);
//connection.Open();
MySqlDataReader dataReader3 = cmd3.ExecuteReader();
if (dataReader3.HasRows == true)
{
while (dataReader3.Read())
{
DeviceId = dataReader3["DEVICEID"].ToString();
DeviceModel = dataReader3["DEVICEMODEL"].ToString();
if ((notice.CategoryId == "2") || (notice.CategoryId == "1"))
{
//NoticeProperty note = new NoticeProperty()
//{
// NoticeId = notice.NoticeId,
// SchoolId = notice.SchoolId,
// TeacherId = notice.TeacherId,
// TeacherName = FullName,
// SpecialName = SpecialName,
// Notice = notice.Notice,
// AttachmentUrl = notice.AttachmentUrl,
// Base64 = notice.Base64,
// CategoryId = notice.CategoryId,
// CreatedBy = notice.CreatedBy
//};
//var JSONString = JsonConvert.SerializeObject(note); //JSON encoded
string response = gcm.SendNotification(DeviceId, notice.Notice, "notice", FullName);
}
}
}
cmd3.Dispose();
}
catch (Exception ex)
{
value = ex.ToString();
}
value = "S#Message successfully posted";
}
}
}
catch (Exception ex)
{
value = ex.ToString();
}
}
}
catch (Exception ex)
{
value = ex.ToString();
}
connection.Close();
}
return value;
}
public bool CheckIfDirectoryExists(string directory, string user, string pswd)
{
bool directoryExists;
var request = (FtpWebRequest)WebRequest.Create(directory);
request.Method = WebRequestMethods.Ftp.ListDirectory;
request.Credentials = new NetworkCredential(user, pswd);
try
{
using (request.GetResponse())
{
directoryExists = true;
}
}
catch (WebException)
{
directoryExists = false;
}
return directoryExists;
}
public object GetClassAttendanceByEmail(string SchoolId, string ClassId, string Month_Date, string Email)
{
var value = string.Empty;
string query = string.Empty;
string strClsName = string.Empty;
string strSchName = string.Empty;
bool success = false;
string message = string.Empty;
decimal TotAmt = 0;
decimal TotFee = 0;
decimal TotNet = 0;
School sch = new School();
List<TerminalProperty> dataList = new List<TerminalProperty>();
string strConnString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
using (MySqlConnection connection = new MySqlConnection(strConnString))
{
query += "SELECT t2.SCHOOLNAME, t1.CLASSNAME ";
query += "FROM v_classes t1 LEFT JOIN ";
query += "tbl_schools t2 ON t1.SCHOOLID = t2.SCHOOLID ";
query += "WHERE t1.SCHOOLID = '" + SchoolId + "' AND t1.CLASSID = '" + ClassId + "' ";
MySqlCommand cmd = new MySqlCommand(query, connection);
connection.Open();
JavaScriptSerializer js = new JavaScriptSerializer();
MySqlDataReader dataReader = cmd.ExecuteReader();
if (dataReader.HasRows == true)
{
while (dataReader.Read())
{
strSchName = dataReader["SCHOOLNAME"].ToString();
strClsName = dataReader["CLASSNAME"].ToString();
}
if (dataReader != null) dataReader.Close();
cmd.Dispose();
DateTime dt2 = DateTime.ParseExact(Month_Date + "-01 12:00:00 AM", "yyyy-MM-dd hh:mm:ss tt", CultureInfo.InvariantCulture);
//string newdate = dt2.ToString("MMM yyyy");
var firstDayOfMonth = new DateTime(dt2.Year, dt2.Month, 1);
var lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddDays(-1);
//var FirstDay = firstDayOfMonth.ToString("yyyy-MM-dd");
var LastDay = Month_Date + "-" + lastDayOfMonth.ToString("dd");
query = "";
query += "SELECT * FROM v_attendance_summary ";
query += "WHERE SCHOOLID = '" + SchoolId + "' AND CLASSID = '" + ClassId + "' AND ";
query += "DATEOFENTRY = '" + Month_Date + "' AND USERROLE = 'Student' ";
query += "ORDER BY FULLNAME ";
MySqlCommand cmd2 = new MySqlCommand(query, connection);
MySqlDataReader dataReader2 = cmd2.ExecuteReader();
if (dataReader2.HasRows == true)
{
int DiM = DateTime.DaysInMonth(Convert.ToInt16(Month_Date.Split('-')[0]), Convert.ToInt16(Month_Date.Split('-')[1]));
int intCol = DiM + 2;
DataTable dt = new DataTable();
//DataTable dt3 = new DataTable();
//dt3.Load(dataReader2);
dt.Columns.AddRange(new DataColumn[7] {
new DataColumn("Full Name"),
new DataColumn("Total Present"),
new DataColumn("Total Late In"),
new DataColumn("Total Half Day"),
new DataColumn("Total Absent"),
new DataColumn("Total Weekend"),
new DataColumn("Total Of Days Attended")
});
//int i = 1;
while (dataReader2.Read())
{
dt.Rows.Add(dataReader2["FULLNAME"],
dataReader2["TOTAL_PRESENT"].ToString(),
dataReader2["TOTAL_LATEIN"].ToString(),
dataReader2["TOTAL_HALFDAY"].ToString(),
dataReader2["TOTAL_ABSENT"].ToString(),
dataReader2["TOTAL_WEEKEND"].ToString(),
dataReader2["TOTAL_ATTENDED"].ToString()
);
}
//switch (intCol)
//{
// case 30:
// dt.Columns.AddRange(new DataColumn[30] {
// new DataColumn("Full Name"),
// new DataColumn("01/" + Month_Date.Split('-')[1]),
// new DataColumn("02/" + Month_Date.Split('-')[1]),
// new DataColumn("03/" + Month_Date.Split('-')[1]),
// new DataColumn("04/" + Month_Date.Split('-')[1]),
// new DataColumn("05/" + Month_Date.Split('-')[1]),
// new DataColumn("06/" + Month_Date.Split('-')[1]),
// new DataColumn("07/" + Month_Date.Split('-')[1]),
// new DataColumn("08/" + Month_Date.Split('-')[1]),
// new DataColumn("09/" + Month_Date.Split('-')[1]),
// new DataColumn("10/" + Month_Date.Split('-')[1]),
// new DataColumn("11/" + Month_Date.Split('-')[1]),
// new DataColumn("12/" + Month_Date.Split('-')[1]),
// new DataColumn("13/" + Month_Date.Split('-')[1]),
// new DataColumn("14/" + Month_Date.Split('-')[1]),
// new DataColumn("15/" + Month_Date.Split('-')[1]),
// new DataColumn("16/" + Month_Date.Split('-')[1]),
// new DataColumn("17/" + Month_Date.Split('-')[1]),
// new DataColumn("18/" + Month_Date.Split('-')[1]),
// new DataColumn("19/" + Month_Date.Split('-')[1]),
// new DataColumn("20/" + Month_Date.Split('-')[1]),
// new DataColumn("21/" + Month_Date.Split('-')[1]),
// new DataColumn("22/" + Month_Date.Split('-')[1]),
// new DataColumn("23/" + Month_Date.Split('-')[1]),
// new DataColumn("24/" + Month_Date.Split('-')[1]),
// new DataColumn("25/" + Month_Date.Split('-')[1]),
// new DataColumn("26/" + Month_Date.Split('-')[1]),
// new DataColumn("27/" + Month_Date.Split('-')[1]),
// new DataColumn("28/" + Month_Date.Split('-')[1]),
// new DataColumn("Total")
// });
// break;
// case 31:
// dt.Columns.AddRange(new DataColumn[31] {
// new DataColumn("Full Name"),
// new DataColumn("01/" + Month_Date.Split('-')[1]),
// new DataColumn("02/" + Month_Date.Split('-')[1]),
// new DataColumn("03/" + Month_Date.Split('-')[1]),
// new DataColumn("04/" + Month_Date.Split('-')[1]),
// new DataColumn("05/" + Month_Date.Split('-')[1]),
// new DataColumn("06/" + Month_Date.Split('-')[1]),
// new DataColumn("07/" + Month_Date.Split('-')[1]),
// new DataColumn("08/" + Month_Date.Split('-')[1]),
// new DataColumn("09/" + Month_Date.Split('-')[1]),
// new DataColumn("10/" + Month_Date.Split('-')[1]),
// new DataColumn("11/" + Month_Date.Split('-')[1]),
// new DataColumn("12/" + Month_Date.Split('-')[1]),
// new DataColumn("13/" + Month_Date.Split('-')[1]),
// new DataColumn("14/" + Month_Date.Split('-')[1]),
// new DataColumn("15/" + Month_Date.Split('-')[1]),
// new DataColumn("16/" + Month_Date.Split('-')[1]),
// new DataColumn("17/" + Month_Date.Split('-')[1]),
// new DataColumn("18/" + Month_Date.Split('-')[1]),
// new DataColumn("19/" + Month_Date.Split('-')[1]),
// new DataColumn("20/" + Month_Date.Split('-')[1]),
// new DataColumn("21/" + Month_Date.Split('-')[1]),
// new DataColumn("22/" + Month_Date.Split('-')[1]),
// new DataColumn("23/" + Month_Date.Split('-')[1]),
// new DataColumn("24/" + Month_Date.Split('-')[1]),
// new DataColumn("25/" + Month_Date.Split('-')[1]),
// new DataColumn("26/" + Month_Date.Split('-')[1]),
// new DataColumn("27/" + Month_Date.Split('-')[1]),
// new DataColumn("28/" + Month_Date.Split('-')[1]),
// new DataColumn("29/" + Month_Date.Split('-')[1]),
// new DataColumn("Total")
// });
// break;
// case 32:
// dt.Columns.AddRange(new DataColumn[32] {
// new DataColumn("Full Name"),
// new DataColumn("01/" + Month_Date.Split('-')[1]),
// new DataColumn("02/" + Month_Date.Split('-')[1]),
// new DataColumn("03/" + Month_Date.Split('-')[1]),
// new DataColumn("04/" + Month_Date.Split('-')[1]),
// new DataColumn("05/" + Month_Date.Split('-')[1]),
// new DataColumn("06/" + Month_Date.Split('-')[1]),
// new DataColumn("07/" + Month_Date.Split('-')[1]),
// new DataColumn("08/" + Month_Date.Split('-')[1]),
// new DataColumn("09/" + Month_Date.Split('-')[1]),
// new DataColumn("10/" + Month_Date.Split('-')[1]),
// new DataColumn("11/" + Month_Date.Split('-')[1]),
// new DataColumn("12/" + Month_Date.Split('-')[1]),
// new DataColumn("13/" + Month_Date.Split('-')[1]),
// new DataColumn("14/" + Month_Date.Split('-')[1]),
// new DataColumn("15/" + Month_Date.Split('-')[1]),
// new DataColumn("16/" + Month_Date.Split('-')[1]),
// new DataColumn("17/" + Month_Date.Split('-')[1]),
// new DataColumn("18/" + Month_Date.Split('-')[1]),
// new DataColumn("19/" + Month_Date.Split('-')[1]),
// new DataColumn("20/" + Month_Date.Split('-')[1]),
// new DataColumn("21/" + Month_Date.Split('-')[1]),
// new DataColumn("22/" + Month_Date.Split('-')[1]),
// new DataColumn("23/" + Month_Date.Split('-')[1]),
// new DataColumn("24/" + Month_Date.Split('-')[1]),
// new DataColumn("25/" + Month_Date.Split('-')[1]),
// new DataColumn("26/" + Month_Date.Split('-')[1]),
// new DataColumn("27/" + Month_Date.Split('-')[1]),
// new DataColumn("28/" + Month_Date.Split('-')[1]),
// new DataColumn("29/" + Month_Date.Split('-')[1]),
// new DataColumn("30/" + Month_Date.Split('-')[1]),
// new DataColumn("Total")
// });
// break;
// case 33:
// dt.Columns.AddRange(new DataColumn[33] {
// new DataColumn("Full Name"),
// new DataColumn("01/" + Month_Date.Split('-')[1]),
// new DataColumn("02/" + Month_Date.Split('-')[1]),
// new DataColumn("03/" + Month_Date.Split('-')[1]),
// new DataColumn("04/" + Month_Date.Split('-')[1]),
// new DataColumn("05/" + Month_Date.Split('-')[1]),
// new DataColumn("06/" + Month_Date.Split('-')[1]),
// new DataColumn("07/" + Month_Date.Split('-')[1]),
// new DataColumn("08/" + Month_Date.Split('-')[1]),
// new DataColumn("09/" + Month_Date.Split('-')[1]),
// new DataColumn("10/" + Month_Date.Split('-')[1]),
// new DataColumn("11/" + Month_Date.Split('-')[1]),
// new DataColumn("12/" + Month_Date.Split('-')[1]),
// new DataColumn("13/" + Month_Date.Split('-')[1]),
// new DataColumn("14/" + Month_Date.Split('-')[1]),
// new DataColumn("15/" + Month_Date.Split('-')[1]),
// new DataColumn("16/" + Month_Date.Split('-')[1]),
// new DataColumn("17/" + Month_Date.Split('-')[1]),
// new DataColumn("18/" + Month_Date.Split('-')[1]),
// new DataColumn("19/" + Month_Date.Split('-')[1]),
// new DataColumn("20/" + Month_Date.Split('-')[1]),
// new DataColumn("21/" + Month_Date.Split('-')[1]),
// new DataColumn("22/" + Month_Date.Split('-')[1]),
// new DataColumn("23/" + Month_Date.Split('-')[1]),
// new DataColumn("24/" + Month_Date.Split('-')[1]),
// new DataColumn("25/" + Month_Date.Split('-')[1]),
// new DataColumn("26/" + Month_Date.Split('-')[1]),
// new DataColumn("27/" + Month_Date.Split('-')[1]),
// new DataColumn("28/" + Month_Date.Split('-')[1]),
// new DataColumn("29/" + Month_Date.Split('-')[1]),
// new DataColumn("30/" + Month_Date.Split('-')[1]),
// new DataColumn("31/" + Month_Date.Split('-')[1]),
// new DataColumn("Total")
// });
// break;
//}
//foreach (DataRow row in dt3.Rows)
//{
// object[] array = new object[7];
// for (int d = 0; d <= 0; d++)
// {
// int b = 0;
// int p = 0; //present
// int l = 0; //late in
// int h = 0; //half day
// int a = 0; //absent
// int n = 0; //no working time
// int x = 0; //unknown
// int w = 0; //weekend
// int i = 0;
// array[0] = row["FULLNAME"];
// for (b = 1; b <= DiM; b++)
// {
// if (CheckDayOfWeek(row["DATEOFENTRY"].ToString()) == false)
// {
// if (b.ToString().Length == 1)
// {
// if (row["DATEOFENTRY"].ToString() == Month_Date + "-0" + b.ToString())
// {
// if (row["ATTENDANCE"].ToString() == "Present")
// {
// //array[b] = "P";
// p++;
// i++;
// }
// else if (row["ATTENDANCE"].ToString() == "Late In")
// {
// //array[b] = "L";
// l++;
// i++;
// }
// else if (row["ATTENDANCE"].ToString() == "Half Day")
// {
// //array[b] = "H";
// h++;
// i++;
// }
// else if (row["ATTENDANCE"].ToString() == "Absent")
// {
// //array[b] = "A";
// a++;
// i++;
// }
// }
// else
// {
// //array[b] = "X";
// x++;
// i++;
// }
// }
// else
// {
// if (row["DATEOFENTRY"].ToString() == Month_Date + "-" + b.ToString())
// {
// if (row["ATTENDANCE"].ToString() == "Present")
// {
// //array[b] = "P";
// p++;
// i++;
// }
// else if (row["ATTENDANCE"].ToString() == "Late In")
// {
// //array[b] = "L";
// l++;
// i++;
// }
// else if (row["ATTENDANCE"].ToString() == "Half Day")
// {
// //array[b] = "H";
// h++;
// i++;
// }
// else if (row["ATTENDANCE"].ToString() == "Absent")
// {
// //array[b] = "A";
// a++;
// i++;
// }
// }
// else
// {
// //array[b] = "X";
// x++;
// i++;
// }
// }
// }
// else
// {
// //array[b] = "W";
// w++;
// //i++;
// }
// }
// array[1] = p.ToString();
// array[2] = l.ToString();
// array[3] = h.ToString();
// array[4] = a.ToString();
// array[5] = w.ToString();
// array[6] = (p + l + h).ToString();
// }
// dt.Rows.Add(array);
//}
message = SendPDFEmailClassAttendance(dt, Email, strSchName, strClsName, "Teacher", Month_Date);
if (message.Split('#')[0] == "S")
{
sch.Status = "S";
sch.Remarks = message.Split('#')[1];
}
else
{
sch.Status = "F";
sch.Remarks = message.Split('#')[1];
}
dataReader2.Close();
connection.Close();
}
else
{
sch.Status = "F";
sch.Remarks = "No record found.";
}
}
else
{
sch.Status = "F";
sch.Remarks = "Email not found! Please update and save your email address under edit profile settings.";
}
}
return sch;
}
private bool CheckDayOfWeek(string DateOfEntry)
{
bool val = false;
DateTime oDate = DateTime.ParseExact(DateOfEntry, "yyyy-MM-dd", null);
DayOfWeek today = oDate.DayOfWeek;
if (today == DayOfWeek.Sunday || today == DayOfWeek.Saturday)
{
val = true;
}
return val;
}
private string SendPDFEmailClassAttendance(DataTable dt, string EmailTo, string SchoolName, string ClassName,
string TeacherName, string strMonth)
{
string val = string.Empty;
//bool success = false;
using (StringWriter sw = new StringWriter())
{
using (HtmlTextWriter hw = new HtmlTextWriter(sw))
{
DateTime dt2 = DateTime.ParseExact(strMonth + "-01 12:00:00 AM", "yyyy-MM-dd hh:mm:ss tt", CultureInfo.InvariantCulture);
string newdate = dt2.ToString("MMM yyyy");
StringBuilder sb = new StringBuilder();
sb.Append("<table width='100%' cellspacing='0' cellpadding='2'>");
sb.Append("<tr><td align='right' style='background-color: #18B5F0' colspan='2'><img src='http://www.ws.i-3s.com.my/i3sWebService/Images/i3s_logo_full.png' height='50' width='100%' align='center'/></td></tr>");
sb.Append("<tr><td align='center' style='background-color: #18B5F0' colspan='2'><b>Staff Attendance Report for " + newdate + "</b></td></tr>");
sb.Append("<tr><td colspan = '3'></td></tr>");
sb.Append("<tr><td><font size='1'><b>School Name : </b>");
sb.Append(SchoolName);
sb.Append("</font></td><td align='right'><font size='1'><b>Date Printed: </b>");
sb.Append(DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss tt"));
sb.Append("</font></td></tr>");
sb.Append("<tr><td colspan='3'><font size='1'><b>Class Name : </b>");
sb.Append(ClassName);
sb.Append("</font></td></tr>");
sb.Append("</table>");
sb.Append("<br />");
sb.Append("<table border = '1'>");
sb.Append("<tr>");
foreach (DataColumn column in dt.Columns)
{
sb.Append("<th style='background-color:#D20B0C;color:#aaaaaa'><font color='#000000' align='center' size='1' >");
sb.Append(column.ColumnName);
sb.Append("</font></th>");
}
sb.Append("</tr>");
foreach (DataRow row in dt.Rows)
{
sb.Append("<tr>");
foreach (DataColumn column in dt.Columns)
{
if (column.ColumnName == "Full Name")
{
sb.Append("<td align='left' width='27%'><font size='1'>");
sb.Append(row[column]);
sb.Append("</font></td>");
}
else if (column.ColumnName == "Total")
{
sb.Append("<td align='center' width='3%'><font size='1'>");
sb.Append(row[column]);
sb.Append("</font></td>");
}
else
{
sb.Append("<td align='center'><font size='1'>");
sb.Append(row[column]);
sb.Append("</font></td>");
}
}
sb.Append("</tr>");
}
//footer
sb.Append("</table>");
StringReader sr = new StringReader(sb.ToString());
Document pdfDoc = new Document(PageSize.A4.Rotate(), 20f, 20f, 20f, 20f);
HTMLWorker htmlparser = new HTMLWorker(pdfDoc);
using (MemoryStream memoryStream = new MemoryStream())
{
PdfWriter writer = PdfWriter.GetInstance(pdfDoc, memoryStream);
pdfDoc.Open();
htmlparser.Parse(sr);
pdfDoc.Close();
byte[] bytes = memoryStream.ToArray();
memoryStream.Close();
try
{
MailMessage mm = new MailMessage();
mm.From = new MailAddress("noreply@i-3s.com.my");
mm.To.Add(EmailTo);
mm.Subject = "i-3s Class Attendance for " + newdate;
mm.Body = CreateSchollAttEmailBody(TeacherName, newdate);
mm.Attachments.Add(new Attachment(new MemoryStream(bytes), "Class_Attendance_for_" + DateTime.Now.ToString("MMM_yyyy") + ".pdf"));
mm.IsBodyHtml = true;
SmtpClient SmtpServer = new SmtpClient("mail.i-3s.com.my");
SmtpServer.Port = 25;
SmtpServer.Credentials = new System.Net.NetworkCredential("noreply@i-3s.com.my", "n0rep|y");
//SmtpServer.EnableSsl = true;
//SmtpServer.DeliveryMethod = SmtpDeliveryMethod.Network;
//SmtpServer.UseDefaultCredentials = false;
ServicePointManager.Expect100Continue = false;
SmtpServer.Send(mm);
//success = true;
val = "S#Mail sent successfully.";
}
catch (Exception ex)
{
val = "F#" + ex.Message;
//success = false;
}
}
}
}
return val;
}
private string CreateSchollAttEmailBody(string fullname, string month)
{
string body = string.Empty;
using (StreamReader reader = new StreamReader(HttpContext.Current.Server.MapPath("~/EmailTemplate/ClassAttendanceEmail.html")))
{
body = reader.ReadToEnd();
}
body = body.Replace("{FullName}", fullname);
body = body.Replace("{Month}", month);
return body;
}
}
}