public void SendOTP(string smsText, string sendTo, bool flag)
{
string responseMessage = string.Empty;
HttpWebRequest request = null;
string postURL = ConfigurationManager.AppSettings["SMSGatewayPostURL"];
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
postURL = String.Format(postURL, sendTo, smsText);
request = (HttpWebRequest)WebRequest.Create(postURL);
// Send the request and get a response
using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
{
// Read the response
using (StreamReader srResponse = new StreamReader(response.GetResponseStream()))
{
responseMessage = srResponse.ReadToEnd();
}
}
if (flag)
{
DashboardHub hub = new DashboardHub();
hub.SMSCreditLeft();
}
}
}
catch (Exception objException)
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
string query = @"INSERT INTO sms_record
(`phoneNumber`,
`message`,
`sms_status`,
date_time)
VALUES
(@phone,
@message,
@status,
@date_time)";
con.Execute(query,
new
{
phone = sendTo,
message = smsText,
status = "Failed",
date_time = System.DateTime.Now.AddMinutes(dateTimeOffSet)
});
}
throw objException;
}
}
public async Task SendBirthdayWish()
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
IEnumerable <dailyBirthdayWish> std;
string query = @"SELECT
CONCAT(IFNULL(std_first_name, ''),
' ',
IFNULL(std_last_name, '')) std_name,
COALESCE(std_contact, std_contact1, std_contact2) std_contact
FROM
sr_register
WHERE
MONTH(std_dob) = MONTH(CURDATE())
AND DAY(std_dob) = DAY(CURDATE())
AND std_active = 'Y'
UNION SELECT
CONCAT(IFNULL(FirstName, ''),
' ',
IFNULL(LastName, '')) std_name,
COALESCE(contact, contact1, contact2) std_contact
FROM
emp_profile a
WHERE
MONTH(dob) = MONTH(CURDATE())
AND DAY(dob) = DAY(CURDATE())
AND emp_active = 1";
#if !DEBUG
std = con.Query <dailyBirthdayWish>(query);
SMSMessage sms = new SMSMessage();
foreach (var item in std)
{
foreach (var bdy in sms.smsbody("birthday"))
{
await sms.SendSMS(bdy.Replace("#name#", item.std_name), item.std_contact, false);
}
}
DashboardHub hub = new DashboardHub();
hub.SMSCreditLeft();
#endif
}
}
public async Task SendDuesReminder()
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
IEnumerable <duesReminder> std;
string query;
mst_sessionMain session = new mst_sessionMain();
if (System.DateTime.Now.AddMinutes(dateTimeOffSet).Month >= 4 && System.DateTime.Now.AddMinutes(dateTimeOffSet).Month <= 12)
{
query = @"SELECT
std_name, sr_number, SUM(dues) dues, std_contact
FROM
(SELECT
CONCAT(IFNULL(b.std_first_name, ''), ' ', IFNULL(b.std_last_name, '')) std_name,
a.sr_number,
SUM(IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0)) dues,
COALESCE(b.std_contact, std_contact1, std_contact2) std_contact
FROM
out_standing a, sr_register b
WHERE
IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0) != 0
AND month_no <= MONTH(CURDATE())
AND session = @session
AND a.sr_number = b.sr_number
AND b.std_active = 'Y'
AND month_no BETWEEN 4 AND 12
GROUP BY sr_number UNION ALL SELECT
CONCAT(IFNULL(b.std_first_name, ''), ' ', IFNULL(b.std_last_name, '')) std_name,
a.sr_number,
SUM(IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0)) dues,
COALESCE(b.std_contact, std_contact1, std_contact2) std_contact
FROM
out_standing a, sr_register b
WHERE
IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0) != 0
AND a.session != @session
AND a.sr_number = b.sr_number
AND b.std_active = 'Y'
GROUP BY sr_number) a
WHERE
a.sr_number IN (SELECT
sr_num
FROM
mst_std_class
WHERE
session = @session
AND class_id IN (SELECT
class_id
FROM
enable_dues_reminder
WHERE
session = @session AND enable = 1))
GROUP BY a.sr_number";
}
else if (System.DateTime.Now.AddMinutes(dateTimeOffSet).Month == 1)
{
query = @"SELECT
std_name, sr_number, SUM(dues) dues, std_contact
FROM
(SELECT
CONCAT(IFNULL(b.std_first_name, ''), ' ', IFNULL(b.std_last_name, '')) std_name,
a.sr_number,
SUM(IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0)) dues,
COALESCE(b.std_contact, std_contact1, std_contact2) std_contact
FROM
out_standing a, sr_register b
WHERE
IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0) != 0
AND month_no NOT IN (2 , 3)
AND session = @session
AND a.sr_number = b.sr_number
AND b.std_active = 'Y'
GROUP BY sr_number UNION ALL SELECT
CONCAT(IFNULL(b.std_first_name, ''), ' ', IFNULL(b.std_last_name, '')) std_name,
a.sr_number,
SUM(IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0)) dues,
COALESCE(b.std_contact, std_contact1, std_contact2) std_contact
FROM
out_standing a, sr_register b
WHERE
IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0) != 0
AND a.session != @session
AND a.sr_number = b.sr_number
AND b.std_active = 'Y'
GROUP BY sr_number) a
WHERE
a.sr_number IN (SELECT
sr_num
FROM
mst_std_class
WHERE
session = @session
AND class_id IN (SELECT
class_id
FROM
enable_dues_reminder
WHERE
session = @session AND enable = 1))
GROUP BY a.sr_number";
}
else if (System.DateTime.Now.AddMinutes(dateTimeOffSet).Month == 2)
{
query = @"SELECT
std_name, sr_number, SUM(dues) dues, std_contact
FROM
(SELECT
CONCAT(IFNULL(b.std_first_name, ''), ' ', IFNULL(b.std_last_name, '')) std_name,
a.sr_number,
SUM(IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0)) dues,
COALESCE(b.std_contact, std_contact1, std_contact2) std_contact
FROM
out_standing a, sr_register b
WHERE
IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0) != 0
AND month_no != 3
AND session = @session
AND a.sr_number = b.sr_number
AND b.std_active = 'Y'
GROUP BY sr_number UNION ALL SELECT
CONCAT(IFNULL(b.std_first_name, ''), ' ', IFNULL(b.std_last_name, '')) std_name,
a.sr_number,
SUM(IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0)) dues,
COALESCE(b.std_contact, std_contact1, std_contact2) std_contact
FROM
out_standing a, sr_register b
WHERE
IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0) != 0
AND a.session != @session
AND a.sr_number = b.sr_number
AND b.std_active = 'Y'
GROUP BY sr_number) a
WHERE
a.sr_number IN (SELECT
sr_num
FROM
mst_std_class
WHERE
session = @session
AND class_id IN (SELECT
class_id
FROM
enable_dues_reminder
WHERE
session = @session AND enable = 1))
GROUP BY a.sr_number";
}
else
{
query = @"SELECT
std_name, sr_number, SUM(dues) dues, std_contact
FROM
(SELECT
CONCAT(IFNULL(b.std_first_name, ''), ' ', IFNULL(b.std_last_name, '')) std_name,
a.sr_number,
SUM(IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0)) dues,
COALESCE(b.std_contact, std_contact1, std_contact2) std_contact
FROM
out_standing a, sr_register b
WHERE
IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0) != 0
AND session = @session
AND a.sr_number = b.sr_number
AND b.std_active = 'Y'
GROUP BY sr_number UNION ALL SELECT
CONCAT(IFNULL(b.std_first_name, ''), ' ', IFNULL(b.std_last_name, '')) std_name,
a.sr_number,
SUM(IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0)) dues,
COALESCE(b.std_contact, std_contact1, std_contact2) std_contact
FROM
out_standing a, sr_register b
WHERE
IFNULL(outstd_amount, 0) - IFNULL(rmt_amount, 0) != 0
AND a.session != @session
AND a.sr_number = b.sr_number
AND b.std_active = 'Y'
GROUP BY sr_number) a
WHERE
a.sr_number IN (SELECT
sr_num
FROM
mst_std_class
WHERE
session = @session
AND class_id IN (SELECT
class_id
FROM
enable_dues_reminder
WHERE
session = @session AND enable = 1))
GROUP BY a.sr_number";
}
std = con.Query <duesReminder>(query, new { session = session.findFinal_Session() });
SMSMessage sms = new SMSMessage();
foreach (var item in std)
{
foreach (var bdy in sms.smsbody("dues_reminder"))
{
string body = bdy.Replace("#student_name#", item.std_name);
body = body.Replace("#sr_number#", item.sr_number.ToString());
body = body.Replace("#dues#", item.dues.ToString());
body = body.Replace("#current_date#", DateTime.Now.ToString("dddd, dd MMMM yyyy"));
#if !DEBUG
await sms.SendSMS(body, item.std_contact, false);
#endif
}
}
DashboardHub hub = new DashboardHub();
hub.SMSCreditLeft();
}
}
public async Task SendMultiSms(string smsText, string sendTo, IEnumerable <string> phone)
{
string responseMessage = string.Empty;
HttpWebRequest request = null;
string postURL = ConfigurationManager.AppSettings["SMSGatewayPostURL"];
try
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
postURL = String.Format(postURL, sendTo, smsText);
request = (HttpWebRequest)WebRequest.Create(postURL);
// Send the request and get a response
using (HttpWebResponse response = (HttpWebResponse)await request.GetResponseAsync())
{
// Read the response
using (StreamReader srResponse = new StreamReader(response.GetResponseStream()))
{
responseMessage = srResponse.ReadToEnd();
}
// Logic to interpret response from your gateway goes here
//Response.Write(String.Format("Response from gateway: {0}", responseMessage));
foreach (var i in phone)
{
string query = @"INSERT INTO sms_record
(`phoneNumber`,
`message`,
`sms_status`,
date_time)
VALUES
(@phone,
@message,
@status,
@date_time)";
con.Execute(query,
new
{
phone = i,
message = smsText,
status = "Success",
date_time = System.DateTime.Now.AddMinutes(dateTimeOffSet)
});
}
}
DashboardHub hub = new DashboardHub();
hub.SMSCreditLeft();
}
}
catch (Exception objException)
{
using (MySqlConnection con = new MySqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
{
foreach (var i in phone)
{
string query = @"INSERT INTO sms_record
(`phoneNumber`,
`message`,
`sms_status`,
date_time)
VALUES
(@phone,
@message,
@status,
@date_time)";
con.Execute(query,
new
{
phone = i,
message = smsText,
status = "Failed",
date_time = System.DateTime.Now.AddMinutes(dateTimeOffSet)
});
}
throw objException;
}
}
}