public Activaterslhirerchy(string username, List <string> user_name)
{
try
{
string query = "select * from customer where parentresellername='" + username + "'";
DataSet ds = DL.DL_ExecuteSimpleQuery(query, MSCon.DecryptConnectionString(ConfigurationManager.AppSettings["sms_connstring"].ToString()));
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (ds.Tables[0].Rows[i]["AccountType"].ToString().Trim().ToLower() == "reseller")
{
user_name.Add(ds.Tables[0].Rows[i]["username"].ToString().Trim());
_resellerh.Add(new Activaterslhirerchy(ds.Tables[0].Rows[i]["UserName"].ToString(), user_name));
}
}
}
}
catch (Exception ex)
{
}
}
public static void Sendalert(int days, string startdate, string enddate, string startdate_numric, string enddate_numric, string str_getusername)
{
DataSet ds_username = DL.DL_ExecuteSimpleQuery(str_getusername);
if (ds_username != null && ds_username.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr_username in ds_username.Tables[0].Rows)
{
StringBuilder sb = new StringBuilder();
#region For HTTP Users
if (dr_username["user_type"].ToString() == "1")
{
sb.Append("<table cellspacing='0' border='1' cellpadding='5' width='70%'><tr align='left' valign='top' style='background-color:#9c9c9c'><th>Sr No.</th><th>User Name</th><th>Total Sent </th><th>Delivered </th><th>Expired </th><th>Undelivered </th><th>Dnc Reject </th><th> Other</th><th>SMSC </th>");
if (dr_username["show_balance"].ToString() == "1")
{
sb.Append("<th>Balance</th>");
}
if (dr_username["show_invoice_count"].ToString() == "1")
{
sb.Append("<th>Credit deducted/Invoice count</th>");
}
sb.Append("</tr>");
int count = 1;
Dictionary <string, long> finalusersummery = GetCount.getsmscount(startdate_numric, enddate_numric, dr_username["dlr_username"].ToString(), 1, int.Parse(dr_username["show_balance"].ToString()), int.Parse(dr_username["dlr_server_id"].ToString()), dr_username["server_connstring"].ToString());
if (finalusersummery != null && finalusersummery.Count > 0)
{
sb.Append("<tr><td>" + count + "</td><td>" + dr_username["dlr_username"].ToString() + "</td><td>" + finalusersummery["TotalSent"] + "</td><td>" + finalusersummery["DELIVRD"] + "</td><td>" + finalusersummery["EXPIRD"] + "</td><td>" + finalusersummery["UNDELIVRD"] + "</td><td>" + finalusersummery["DNC"] + "</td><td>" + finalusersummery["Other"] + "</td><td>" + finalusersummery["SMSC"] + "</td>");
if (dr_username["show_balance"].ToString() == "1")
{
sb.Append("<td>" + finalusersummery["balance"] + "</td>");
}
if (dr_username["show_invoice_count"].ToString() == "1")
{
sb.Append("<td>" + finalusersummery["Invoicecount"] + "</td>");
}
sb.Append("</tr>");
count++;
}
sb.Append("</table><br>");
}
#endregion
#region For HTTP Reseller
if (dr_username["user_type"].ToString() == "2")
{
sb.Append("Reseller Name : <b>" + dr_username["dlr_username"].ToString() + "</b><br>");
if (dr_username["show_rslbalance"].ToString() == "1")
{
string qry_getbalrsl = "select c.Total_Credits_Assigned-r.Credits_Assigned as bal from customer c,resellers r where c.UserName=r.username and c.UserName='******';";
DataSet ds_getrslbal = DL.DL_ExecuteSimpleQuery(qry_getbalrsl, MSCon.DecryptConnectionString(ConfigurationManager.AppSettings["sms_connstring"].ToString()));
if (ds_getrslbal != null && ds_getrslbal.Tables[0].Rows.Count > 0)
{
sb.Append("Balance : <b>" + ds_getrslbal.Tables[0].Rows[0]["bal"] + "</b><br>");
}
}
string str_smsconnstring = MSCon.DecryptConnectionString(ConfigurationManager.AppSettings["sms_connstring"].ToString());
string getuserlist = "select distinct c.username from customer c,usc_smscount usc where c.username=usc.username and c.parentresellername='" + dr_username["dlr_username"].ToString() + "' and usc.send_date>=" + startdate_numric + " and usc.send_date<=" + enddate_numric + ";";
DataSet ds_userlist = DL.DL_ExecuteSimpleQuery(getuserlist, str_smsconnstring);
if (ds_userlist != null && ds_userlist.Tables[0].Rows.Count > 0)
{
sb.Append("<table cellspacing='0' border='1' cellpadding='5' width='70%'><tr align='left' valign='top' style='background-color:#9c9c9c'><th>Sr No.</th><th>User Name</th><th>Total Sent </th><th>Delivered </th><th>Expired </th><th>Undelivered </th><th>Dnc Reject </th><th> Other</th><th>SMSC </th>");
if (dr_username["show_balance"].ToString() == "1")
{
sb.Append("<th>Balance</th>");
}
if (dr_username["show_invoice_count"].ToString() == "1")
{
sb.Append("<th>Credit deducted/Invoice count</th>");
}
sb.Append("</tr>");
int count = 1;
foreach (DataRow dr in ds_userlist.Tables[0].Rows)
{
Dictionary <string, long> finalusersummery = GetCount.getsmscount(startdate_numric, enddate_numric, dr["username"].ToString(), 1, int.Parse(dr_username["show_balance"].ToString()), int.Parse(dr_username["dlr_server_id"].ToString()), dr_username["server_connstring"].ToString());
sb.Append("<tr><td>" + count + "</td><td>" + dr["username"].ToString() + "</td><td>" + finalusersummery["TotalSent"] + "</td><td>" + finalusersummery["DELIVRD"] + "</td><td>" + finalusersummery["EXPIRD"] + "</td><td>" + finalusersummery["UNDELIVRD"] + "</td><td>" + finalusersummery["DNC"] + "</td><td>" + finalusersummery["Other"] + "</td><td>" + finalusersummery["SMSC"] + "</td>");
if (dr_username["show_balance"].ToString() == "1")
{
sb.Append("<td>" + finalusersummery["balance"] + "</td>");
}
if (dr_username["show_invoice_count"].ToString() == "1")
{
sb.Append("<td>" + finalusersummery["Invoicecount"] + "</td>");
}
count++;
sb.Append("</tr>");
}
sb.Append("</table><br>");
}
}
#endregion
#region For SMPP user
if (dr_username["user_type"].ToString() == "3")
{
sb.Append("SMPP Users:-" + "<br><br>");
sb.Append("<table cellspacing='0' border='1' cellpadding='5' width='70%'><tr align='left' valign='top' style='background-color:#9c9c9c'><th>Sr No.</th><th>User Name</th><th>Total Sent </th><th>Delivered </th><th>Expired </th><th>Undelivered </th><th>Dnc Reject </th><th> Other</th><th>SMSC </th>");
if (dr_username["show_balance"].ToString() == "1")
{
sb.Append("<th>Balance</th>");
}
if (dr_username["show_invoice_count"].ToString() == "1")
{
sb.Append("<th>Credit deducted/Invoice count</th>");
}
sb.Append("</tr>");
int count = 1;
Dictionary <string, long> finalusersummery = GetCount.getsmscount(startdate_numric, enddate_numric, dr_username["dlr_username"].ToString(), 2, int.Parse(dr_username["show_balance"].ToString()), int.Parse(dr_username["dlr_server_id"].ToString()), dr_username["server_connstring"].ToString());
if (finalusersummery != null && finalusersummery.Count > 0)
{
sb.Append("<tr><td>" + count + "</td><td>" + dr_username["dlr_username"].ToString() + "</td><td>" + finalusersummery["TotalSent"] + "</td><td>" + finalusersummery["DELIVRD"] + "</td><td>" + finalusersummery["EXPIRD"] + "</td><td>" + finalusersummery["UNDELIVRD"] + "</td><td>" + finalusersummery["DNC"] + "</td><td>" + finalusersummery["Other"] + "</td><td>" + finalusersummery["SMSC"] + "</td>");
if (dr_username["show_balance"].ToString() == "1")
{
sb.Append("<td>" + finalusersummery["balance"] + "</td>");
}
if (dr_username["show_invoice_count"].ToString() == "1")
{
sb.Append("<td>" + finalusersummery["Invoicecount"] + "</td>");
}
sb.Append("</tr>");
count++;
}
sb.Append("</table><br><br>");
}
#endregion
#region For whole reseller hirerchy
if (dr_username["user_type"].ToString() == "4")
{
string username = dr_username["dlr_username"].ToString();
List <string> user_name = new List <string>();
user_name.Add(username);
Activaterslhirerchy act_rsl_hirerchy = new Activaterslhirerchy(username, user_name);
if (user_name.Count > 0)
{
foreach (string str_rslname in user_name)
{
string str_smsconnstring = MSCon.DecryptConnectionString(ConfigurationManager.AppSettings["sms_connstring"].ToString());
string getuserlist = "select distinct c.username from customer c,usc_smscount usc where c.username=usc.username and c.parentresellername='" + str_rslname + "' and usc.send_date>=" + startdate_numric + " and usc.send_date<=" + enddate_numric + ";";
DataSet ds_userlist = DL.DL_ExecuteSimpleQuery(getuserlist, str_smsconnstring);
if (ds_userlist != null && ds_userlist.Tables[0].Rows.Count > 0)
{
sb.Append("Reseller Name : <b>" + str_rslname + "</b><br>");
sb.Append("<table cellspacing='0' border='1' cellpadding='5' width='70%'><tr align='left' valign='top' style='background-color:#9c9c9c'><th>Sr No.</th><th>User Name</th><th>Total Sent </th><th>Delivered </th><th>Expired </th><th>Undelivered </th><th>Dnc Reject </th><th> Other</th><th>SMSC </th>");
if (dr_username["show_balance"].ToString() == "1")
{
sb.Append("<th>Balance</th>");
}
if (dr_username["show_invoice_count"].ToString() == "1")
{
sb.Append("<th>Credit deducted/Invoice count</th>");
}
sb.Append("</tr>");
int count = 1;
foreach (DataRow dr in ds_userlist.Tables[0].Rows)
{
Dictionary <string, long> finalusersummery = GetCount.getsmscount(startdate_numric, enddate_numric, dr["username"].ToString(), 1, int.Parse(dr_username["show_balance"].ToString()), int.Parse(dr_username["dlr_server_id"].ToString()), dr_username["server_connstring"].ToString());
if (finalusersummery != null && finalusersummery.Count > 0)
{
sb.Append("<tr><td>" + count + "</td><td>" + dr["username"].ToString() + "</td><td>" + finalusersummery["TotalSent"] + "</td><td>" + finalusersummery["DELIVRD"] + "</td><td>" + finalusersummery["EXPIRD"] + "</td><td>" + finalusersummery["UNDELIVRD"] + "</td><td>" + finalusersummery["DNC"] + "</td><td>" + finalusersummery["Other"] + "</td><td>" + finalusersummery["SMSC"] + "</td>");
if (dr_username["show_balance"].ToString() == "1")
{
sb.Append("<td>" + finalusersummery["balance"] + "</td>");
}
if (dr_username["show_invoice_count"].ToString() == "1")
{
sb.Append("<td>" + finalusersummery["Invoicecount"] + "</td>");
}
sb.Append("</tr>");
count++;
}
}
sb.Append("</table><br>");
}
}
}
}
#endregion
sb.Append("<br>Thanks & Regards<br>");
sb.Append("Vivaconnect Technical Team.");
#region Send Mail
try
{
____logconfig.Log_Write(____logconfig.LogLevel.DEBUG, 0, "Email sending started for sales person==>" + dr_username["dlr_username"].ToString());
EmailDetails obj = new EmailDetails(ConfigurationSettings.AppSettings["from_emailid"].ToString(),
ConfigurationSettings.AppSettings["host_address"].ToString(), ConfigurationSettings.AppSettings["credential_emailid"].ToString(),
ConfigurationSettings.AppSettings["credential_passworrd"].ToString(), Convert.ToInt32(ConfigurationSettings.AppSettings["port"].ToString()),
Convert.ToInt32(ConfigurationSettings.AppSettings["ssl_isactive"].ToString()), Convert.ToInt32(ConfigurationSettings.AppSettings["bodyhtml_isactive"].ToString()));
obj.Subject = dr_username["subject"].ToString() + "(" + startdate + " to " + enddate + ")";
obj.EmailText = sb.ToString();
obj.ToEmailId = dr_username["toemailid"].ToString();
if (int.Parse(dr_username["cc_isactive"].ToString()) == 1)
{
obj.CC = dr_username["ccid"].ToString();
}
if (int.Parse(dr_username["bcc_isactive"].ToString()) == 1)
{
obj.Bcc = dr_username["bccid"].ToString();
}
obj.SendEmail(dr_username["ccid"].ToString(), dr_username["bccid"].ToString());
____logconfig.Log_Write(____logconfig.LogLevel.DEBUG, 0, "Email sending Ends for sales person==>" + dr_username["dlr_username"].ToString());
}
catch (Exception ex)
{
____logconfig.Log_Write(____logconfig.LogLevel.DEBUG, 0, "Problem occured for the username" + dr_username["dlr_username"].ToString() + " and exception is==>" + ex.ToString());
____logconfig.Error_Write(____logconfig.LogLevel.EXC, 0, ex);
}
#endregion
}
}
}
public static Dictionary <string, long> getsmscount(string startdate, string enddate, string str_username, int type, int show_balance, int dlrId, string sendConnstring)
{
try
{
Dictionary <string, long> dict_userdata = new Dictionary <string, long>();
string smsconnstring = MSCon.DecryptConnectionString(sendConnstring);
string misdlrconnstring = MSCon.DecryptConnectionString(ConfigurationManager.AppSettings["mis_connstring"].ToString());
long sent_count = 0;
long delivrdcount = 0;
long expirdcount = 0;
long undelivrdcount = 0;
long dnccount = 0;
long smsccount = 0;
long othercount = 0;
long invoicecount = 0;
long balance = 0;
string getsentcount = "";
DataSet ds_sentcount;
if (type == 1)
{
getsentcount = "select username,sum(Usms+Nsms+ncpr_dnc_cr_dc_n+ncpr_dnc_cr_dc_u+ncpr_dnc_cr_rf_n+ncpr_dnc_cr_rf_u) as sentcount from usc_smscount where UserName='******' and send_date>=" + startdate + " and send_date<=" + enddate + ";";
ds_sentcount = DL.DL_ExecuteSimpleQuery(getsentcount, smsconnstring);
if (show_balance == 1)
{
string qry_getbal = "select Total_Credits_Assigned-Total_Credits_Used as balance from customer where UserName='******';";
DataSet ds_getbal = DL.DL_ExecuteSimpleQuery(qry_getbal, smsconnstring);
if (ds_getbal != null && ds_getbal.Tables[0].Rows.Count > 0)
{
balance = long.Parse(ds_getbal.Tables[0].Rows[0]["balance"].ToString());
}
}
}
else
{
getsentcount = "select smppname_username as username,sum(smppcount) as sentcount from mis_smpp_count where smppname_username='******' and senddate>=" + startdate + " and senddate<=" + enddate + " group by smppname_username;";
ds_sentcount = DL.DL_ExecuteSimpleQuery(getsentcount, smsconnstring);
}
#region Check DLR
if (ds_sentcount != null && ds_sentcount.Tables[0].Rows.Count > 0)
{
sent_count = ds_sentcount.Tables[0].Rows[0]["sentcount"] == DBNull.Value ? 0 : long.Parse(ds_sentcount.Tables[0].Rows[0]["sentcount"].ToString());
if (sent_count > 0)
{
string deliveredcount = @"select username,sum(if(dlrstatus like 'DEL%', delivercount*messagelength,0)) as DELIVRD,
sum(if(dlrstatus like 'EXP%', delivercount*messagelength,0)) as EXPIRD,
sum(if(dlrstatus like 'UNDE%', delivercount*messagelength,0)) as UNDELIV ,
sum(if(dlrstatus like 'DNC%', delivercount,0)) as DNC_REJECT,
sum(if(dlrstatus not in('DELIVRD','DNC REJECT.','ndnc_reject','EXPIRED','UNDELIV'), delivercount*messagelength,0)) as Other,senddate
from dlr_summary
where username='******'" +
" and senddate>=" + startdate + " and senddate<= " + enddate + "" +
" group by username";
if (type == 1)
{
deliveredcount += " ," + dlrId + "";
}
DataSet ds_dlrcount = DL.DL_ExecuteSimpleQuery(deliveredcount, misdlrconnstring);
if (ds_dlrcount != null && ds_dlrcount.Tables[0].Rows.Count > 0)
{
string uname = ds_dlrcount.Tables[0].Rows[0]["username"] == DBNull.Value ? "NA" : ds_dlrcount.Tables[0].Rows[0]["username"].ToString();
if (uname != "NA")
{
delivrdcount = ds_dlrcount.Tables[0].Rows[0]["DELIVRD"] == DBNull.Value ? 0 : long.Parse(ds_dlrcount.Tables[0].Rows[0]["DELIVRD"].ToString());
expirdcount = ds_dlrcount.Tables[0].Rows[0]["EXPIRD"] == DBNull.Value ? 0 : long.Parse(ds_dlrcount.Tables[0].Rows[0]["EXPIRD"].ToString());
undelivrdcount = ds_dlrcount.Tables[0].Rows[0]["UNDELIV"] == DBNull.Value ? 0 : long.Parse(ds_dlrcount.Tables[0].Rows[0]["UNDELIV"].ToString());
dnccount = ds_dlrcount.Tables[0].Rows[0]["DNC_REJECT"] == DBNull.Value ? 0 : long.Parse(ds_dlrcount.Tables[0].Rows[0]["DNC_REJECT"].ToString());
othercount = ds_dlrcount.Tables[0].Rows[0]["Other"] == DBNull.Value ? 0 : long.Parse(ds_dlrcount.Tables[0].Rows[0]["Other"].ToString());
smsccount = sent_count - (delivrdcount + expirdcount + undelivrdcount + dnccount + othercount);
}
invoicecount = delivrdcount + smsccount;
dict_userdata.Add("TotalSent", sent_count);
dict_userdata.Add("DELIVRD", delivrdcount);
dict_userdata.Add("UNDELIVRD", undelivrdcount);
dict_userdata.Add("DNC", dnccount);
dict_userdata.Add("EXPIRD", expirdcount);
dict_userdata.Add("Other", othercount);
dict_userdata.Add("SMSC", long.Parse(smsccount.ToString()));
dict_userdata.Add("Invoicecount", invoicecount);
dict_userdata.Add("balance", balance);
return(dict_userdata);
}
else
{
return(dict_userdata);
}
}
else
{
return(dict_userdata);
}
}
else
{
return(dict_userdata);
}
#endregion
}
catch (Exception ex)
{ return(null); }
}