Esempio n. 1
0
        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)
            {
            }
        }
Esempio n. 2
0
        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
                }
            }
        }
Esempio n. 3
0
        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); }
        }