public void get_msc_code_information(string msc_code)
{
try
{
string strQuery = "use dst;select dmc.dst_msc_id,dmc.dst_msc_circle_type,dmc.dst_msc_cdma_gsm,dmc.dst_msc_access_code,dmc.dst_msc_series,dmc.dst_operator_id,dod.dst_operator_name,dmc.dst_circle_id,dcd.dst_circle_name,dmc.dst_msc_code_yr_allotment " +
" from dst_msc_codes dmc, " +
" dst_circle_details dcd, " +
" dst_operator_details dod" +
" where " +
" dcd.dst_circle_id = dmc.dst_circle_id and " +
" dod.dst_operator_id = dmc.dst_operator_id and" +
" (dst_msc_code_0 = " + msc_code + " or dmc.dst_msc_code_1 = " + msc_code + " or dmc.dst_msc_code_2 = " + msc_code + " or dmc.dst_msc_code_3 = " + msc_code + " or dmc.dst_msc_code_4 = " + msc_code + " or dmc.dst_msc_code_5 = " + msc_code + " or dmc.dst_msc_code_6 = " + msc_code + " or dmc.dst_msc_code_7 = " + msc_code + " or dmc.dst_msc_code_8 = " + msc_code + " or dmc.dst_msc_code_9 = " + msc_code + ");";
DataTable dt = DL.DL_ExecuteSimpleQuery(strQuery).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
long dST_msc_id = Convert.ToInt64(dt.Rows[0]["dst_msc_id"]);
string dST_msc_circle_type = Convert.ToString(dt.Rows[0]["dst_msc_circle_type"]);
string dST_msc_cdma_gsm = Convert.ToString(dt.Rows[0]["dst_msc_cdma_gsm"]);
string dST_msc_access_code = Convert.ToString(dt.Rows[0]["dst_msc_access_code"]);
string dST_msc_series = Convert.ToString(dt.Rows[0]["dst_msc_series"]);
string dST_operator_name = Convert.ToString(dt.Rows[0]["dst_operator_name"]);
string dST_circle_name = Convert.ToString(dt.Rows[0]["dst_circle_name"]);
long dST_operator_id = Convert.ToInt64(dt.Rows[0]["dst_operator_id"]);
long dST_circle_id = Convert.ToInt64(dt.Rows[0]["dst_circle_id"]);
this._mSC_Mobile_Info = new MSC_Mobile(dST_msc_id, dST_msc_circle_type, dST_msc_cdma_gsm, dST_msc_access_code, dST_msc_series, dST_operator_id, dST_operator_name, dST_circle_id, dST_circle_name);
this._mSC_LandLine_Info = null;
this._caller_type = CallerType.MOBILE;
this._caller_origin = CallerOrigin.DOMESTIC;
}
else if (dt != null && dt.Rows.Count == 0)
{
int std_start_length = 2;
string std_code = msc_code.Substring(0, std_start_length);
bool flag_found_landline = false;
while (!perform_landline_detection(std_code, out flag_found_landline) && std_start_length != 5)
{
std_code = msc_code.Substring(0, ++std_start_length);
}
if (!flag_found_landline)
{
this._mSC_LandLine_Info = null;
this._mSC_Mobile_Info = new MSC_Mobile(0, "UNKNOWN", "UNKNOWN", "UNKNOWN", "UNKNOWN", 0, "UNKNOWN", 0, "UNKNOWN");
this._caller_origin = CallerOrigin.INTERNATIONAL;
this._caller_type = CallerType.MOBILE;
}
}
else
{
throw new MSCException("error:database returning null dataset");
}
}
catch (Exception exc)
{
____logconfig.Log_Write(____logconfig.LogLevel.EXC, 0, "::MSC.cs::MSC::get_msc_code_information():: --" + exc.ToString());
}
}
public DataSet GetGroupList(string unm)
{
try
{
____logconfig.Log_Write(____logconfig.LogLevel.INFO, 18, "BL:GetGroupList()::Function starts here.");
//string sqlstmt = "select g.GroupName, g.GroupId,count(c.contacts) as No.of Contacts from comgt c,groups g where c.GroupID=g.GroupID and g.UserName=?global.username group by g.Groupname";
string str_getgroupid = "select GroupID from groups where UserName=?global.username;";
string[] arr_groupid = new string[] { unm };
DataSet ds_groupid = DL.DL_ExecuteQuery(str_getgroupid, arr_groupid);
List <string> lstgroupid = new List <string>();
if (ds_groupid != null && ds_groupid.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr_groupid in ds_groupid.Tables[0].Rows)
{
lstgroupid.Add(dr_groupid["GroupID"].ToString());
}
}
DataSet dr;
if (lstgroupid.Count > 0)
{
//string sqlstmt = "select g.GroupName, g.GroupId,count(c.contacts) as Contacts from comgt c,groups g where c.GroupID=g.GroupID and g.UserName=?global.username group by g.Groupname";
string sqlstmt = "select g.GroupName, g.GroupId,count(c.cid) as Contacts from comgt c inner join groups g on c.GroupID=g.GroupID where g.GroupId in (" + string.Join(",", lstgroupid) + ") group by g.GroupId;";
string[] arr = new string[] { unm };
____logconfig.Log_Write(____logconfig.LogLevel.DB, 22, "BL:GetGroupList()::Query to select group details:" + sqlstmt);
dr = DL.DL_ExecuteSimpleQuery(sqlstmt);
____logconfig.Log_Write(____logconfig.LogLevel.DB, 24, "BL:GetGroupList()::Result of query to select group details, no of rows:" + dr.Tables[0].Rows.Count.ToString());
}
else
{
dr = null;
}
return(dr);
}
catch (Exception ex)
{
____logconfig.Error_Write(____logconfig.LogLevel.EXC, 29, ex.Message.ToString(), "BL");
return(null);
}
}
public bool perform_landline_detection(string std_code, out bool flag_found_landline)
{
try
{
string strQuery = "use dst;select dst_std_id,dst_circle_id, dst_std_service_area_name, dst_std_service_area, dst_std_lcda_name, dst_std_sdca_name, dst_std_sdca_code, dst_std_isactive " +
"from dst.dst_std_code dsc where dst_std_sdca_code = " + std_code + ";";
DataTable dt = DL.DL_ExecuteSimpleQuery(strQuery).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
long dST_std_id = Convert.ToInt64(dt.Rows[0]["dst_std_id"]);
long dST_circle_id = Convert.ToInt64(dt.Rows[0]["dst_circle_id"]);
string dST_std_service_area_name = Convert.ToString(dt.Rows[0]["dst_std_service_area_name"]);
string dST_std_service_area = Convert.ToString(dt.Rows[0]["dst_std_service_area"]);
string dts_std_lcda_name = Convert.ToString(dt.Rows[0]["dst_std_lcda_name"]);
string dts_std_sdca_name = Convert.ToString(dt.Rows[0]["dst_std_sdca_name"]);
int dST_std_sdca_code = Convert.ToInt32(dt.Rows[0]["dst_std_sdca_code"]);
string dst_landline_number = "";
string dst_operator_name = "UNKNOWN";
if (std_code.Length == 2)
{
dst_landline_number = this._dst_number.Substring(2, this._dst_number.Length - 2);
dst_operator_name = get_landline_operator_name(dst_landline_number, dST_std_sdca_code);
}
else if (std_code.Length == 3)
{
dst_landline_number = this._dst_number.Substring(3, this._dst_number.Length - 3);
dst_operator_name = get_landline_operator_name(dst_landline_number, dST_std_sdca_code);
}
else if (std_code.Length == 4)
{
dst_landline_number = this._dst_number.Substring(4, this._dst_number.Length - 4);
dst_operator_name = get_landline_operator_name(dst_landline_number, dST_std_sdca_code);
}
this._mSC_Mobile_Info = null;
this._mSC_LandLine_Info = new MSC_LandLine(dST_std_id, dST_circle_id, dST_std_service_area, dST_std_service_area_name, dts_std_lcda_name, dts_std_sdca_name, dst_operator_name, dST_std_sdca_code);
this._caller_origin = CallerOrigin.DOMESTIC;
this._caller_type = CallerType.LANDLINE;
flag_found_landline = true;
return(true);
}
else if (dt != null && dt.Rows.Count == 0)
{
flag_found_landline = false;
return(false);
}
else
{
flag_found_landline = false;
throw new MSCException("error:database returning null dataset");
}
}
catch (Exception exc)
{
____logconfig.Log_Write(____logconfig.LogLevel.EXC, 0, "::MSC.cs::MSC::get_msc_code_information():: --" + exc.ToString());
flag_found_landline = false;
return(false);
}
}
public static Dictionary <string, long> getsmscount(string startdate, string enddate, string str_username, string send_conn, int type)
{
try
{
Dictionary <string, long> dict_userdata = new Dictionary <string, long>();
string smsconnstring = MSCon.DecryptConnectionString(send_conn);
//246
string misdlrconnstring = MSCon.DecryptConnectionString(ConfigurationManager.AppSettings["ConStr"].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;
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,sum(ncpr_dnc_cr_dc_n+ncpr_dnc_cr_dc_u+ncpr_dnc_cr_rf_n+ncpr_dnc_cr_rf_u) as dncReject" +
" 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);
// 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','Number Blacklisted.'), delivercount*messagelength,0)) as Other,senddate
from dlr_summary
where username='******'" +
" and senddate>=" + startdate + " and senddate<= " + enddate + "" +
" group by username;";
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")
{
if (type == 1)
{
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_sentcount.Tables[0].Rows[0]["dncReject"] == DBNull.Value ? 0 : long.Parse(ds_sentcount.Tables[0].Rows[0]["dncReject"].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);
}
else
{
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);
return(dict_userdata);
}
else
{
return(dict_userdata);
}
}
else
{
return(dict_userdata);
}
}
else
{
return(dict_userdata);
}
#endregion
}
catch (Exception ex)
{ return(null); }
}