Example #1
0
        public async Task<object> SelectPrimaryEvidenceWithTeacherDetail()
        {
            DBConnector d = new DBConnector();
            if (!d.SQLConnect())
                return WebApiApplication.CONNECTDBERRSTRING;
            Evidence_with_teacher_curri_indicator_detail result = new Evidence_with_teacher_curri_indicator_detail();
            d.iCommand.CommandText = string.Format("select {0},{1},{2}.{3},{2}.{4},{5},{6},{7},{8} " +
            "from {9},{2},{10},{11},{12} " +
            "where {9}.{13} = '{14}' and {9}.{15} = {16} " +
            "and {9}.{15} = {2}.{17} " +
            "and {10}.{18} = {9}.{13} " +
            "and {19} = {20} " +

            "and {12}.{21} = {2}.{4} " +
            "and {12}.{22} = (select max({12}.{22}) from {12} where {12}.{22} <= {2}.{3}) " +
            "and {12}.{22} = {2}.{3} ",
            FieldName.EVIDENCE_NAME, Cu_curriculum.FieldName.CURR_TNAME,/*2 primary_evidence*/FieldName.TABLE_NAME,
            FieldName.ACA_YEAR, FieldName.INDICATOR_NUM, Indicator.FieldName.INDICATOR_NAME_T,
            Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME, Teacher.FieldName.EMAIL,
            /*9*/Primary_evidence_status.FieldName.TABLE_NAME,/*10*/Cu_curriculum.FieldName.TABLE_NAME,
            /*11*/User_list.FieldName.TABLE_NAME,/*12*/Indicator.FieldName.TABLE_NAME,
            Primary_evidence_status.FieldName.CURRI_ID, curri_id, Primary_evidence_status.FieldName.PRIMARY_EVIDENCE_NUM,
            primary_evidence_num, FieldName.PRIMARY_EVIDENCE_NUM, Cu_curriculum.FieldName.CURRI_ID,
            Primary_evidence_status.FieldName.TEACHER_ID, User_list.FieldName.USER_ID,
            /*21*/Indicator.FieldName.INDICATOR_NUM, Indicator.FieldName.ACA_YEAR);

            try
            {
                System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
                if (res.HasRows)
                {
                    DataTable data = new DataTable();
                    data.Load(res);
                    foreach (DataRow item in data.Rows)
                    {
                        result.evidence_name = item.ItemArray[data.Columns[FieldName.EVIDENCE_NAME].Ordinal].ToString();
                        result.aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]);
                        result.curr_tname = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURR_TNAME].Ordinal].ToString();
                        result.indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]);
                        result.indicator_name_t = item.ItemArray[data.Columns[Indicator.FieldName.INDICATOR_NAME_T].Ordinal].ToString();

                        result.t_name = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString()) +
                                     item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString();

                        result.email = item.ItemArray[data.Columns[Teacher.FieldName.EMAIL].Ordinal].ToString();
                    }
                    data.Dispose();
                }
                else
                {
                    //Reserved for return error string
                }
                res.Close();
            }
            catch (Exception ex)
            {
                //Handle error from sql execution
                return ex.Message;
            }
            finally
            {
                //Whether it success or not it must close connection in order to end block
                d.SQLDisconnect();
            }
            return result;
        }
Example #2
0
        public static async Task<object> sendNotificationPrimaryEvidenceIndividual(Evidence_with_teacher_curri_indicator_detail data)
        {
            SmtpSection smtpSection = (SmtpSection)ConfigurationManager.GetSection("system.net/mailSettings/smtp");
            try
            {
                MailMessage mailMsg = new MailMessage();
                // To
                mailMsg.To.Add(new MailAddress(data.email));
                //mailMsg.To.Add(new MailAddress("*****@*****.**"));

                // From
                mailMsg.From = new MailAddress(smtpSection.Network.UserName, "Educationalproject");

                mailMsg.Subject = string.Format("Educational Project - แจ้งเตือนหลักฐานค้างส่งของ {0}",data.t_name);
                mailMsg.IsBodyHtml = true;
                mailMsg.Body = string.Format("เรียน {0} <br>", data.t_name) +
                               "ท่านมีหลักฐานที่ค้างส่งอยู่ โดยหลักฐานที่จะให้ท่านรีบดำเนินการส่งโดยเร็วที่สุดคือ <br>" +
                               string.Format("<ul><li>{0} <b>หลักสูตร</b> {1} <b>ปีการศึกษา</b> {2}</li></ul><br>", data.evidence_name, data.curr_tname, data.aca_year) +
                               "จึงเรียนมาเพื่อทราบ<br><br><br><br>" +

                               "หากอีเมล์ของท่านถูกแอบอ้างสร้างบัญชี หรือ ไม่ได้จงใจที่จะใช้งานเว็บแอพพลิเคชั่นของเราจริงๆ ท่านสามารถข้ามอีเมล์นี้ หรือ ลบทิ้งได้ทันที";
                mailMsg.Priority = MailPriority.High;
                
                // Init SmtpClient and send
                SmtpClient smtpClient = new SmtpClient(smtpSection.Network.Host, 587);
                smtpClient.EnableSsl = true;
                NetworkCredential credentials = new NetworkCredential(smtpSection.Network.UserName, smtpSection.Network.Password);
                smtpClient.Credentials = credentials;
                await smtpClient.SendMailAsync(mailMsg);
                smtpClient.Dispose();
                return null;
            }
            catch (Exception ex)
            {
                return ex;
            }
        }