public IEnumerable <Message> GetMessagesInRange(MessageSearchCriteria searchCriteria)
{
var messages = from country in _context.Query <Country>()
join message in _context.Query <ShortMessageService>()
on country.ID equals message.CountryId
join messageState in _context.Query <MessageState>()
on message.ID equals messageState.SmsId
join messageStatus in _context.Query <MessageStatus>()
on messageState.MessageStatusId equals messageStatus.ID
select new Message
{
CountryName = country.CountryName,
MobileCountryCode = country.MobileCountryCode,
CountryCode = country.CountryCode,
MessageStateCode = messageStatus.MessageStateCode,
MessgeStateDescription = messageStatus.MessgeStateDescription,
SentDateTime = message.SentDateTime,
From = message.From,
To = message.To,
Text = message.Text
};
var fromDate = searchCriteria.DateTimeFrom;
if (fromDate.HasValue)
{
messages = messages.Where(msgItem => msgItem.SentDateTime >= fromDate);
}
var tillDate = searchCriteria.DateTimeTo;
if (tillDate.HasValue)
{
messages = messages.Where(msgItem => msgItem.SentDateTime <= tillDate);
}
var recordsToSkip = searchCriteria.Skip;
if (recordsToSkip.HasValue)
{
messages = messages.OrderBy(msgItem => msgItem.SentDateTime).Skip(recordsToSkip.Value);
}
var recordsToTake = searchCriteria.Take;
if (recordsToTake.HasValue)
{
messages = messages.Take(recordsToTake.Value);
}
var searchResult = messages.ToList();
return(searchResult);
}
public IEnumerable <Statistic> GetStatisticsInRange(MessageSearchCriteria searchCriteria)
{
var messages = from country in _context.Query <Country>()
join package in _context.Query <MessagePackage>()
on country.ID equals package.CountryId
join message in _context.Query <ShortMessageService>()
on country.ID equals message.CountryId
join messageState in _context.Query <MessageState>()
on message.ID equals messageState.SmsId
join messageStatus in _context.Query <MessageStatus>()
on messageState.MessageStatusId equals messageStatus.ID
select new Statistic
{
MobileCountryCode = country.MobileCountryCode,
SentDateTime = message.SentDateTime,
From = message.From,
To = message.To,
State = messageStatus.MessageStateCode == "Sent"?MessageStateEnum.Sent : MessageStateEnum.Failed,
Price = package.PricePerSms
};
var fromDate = searchCriteria.DateTimeFrom;
if (fromDate.HasValue)
{
messages = messages.Where(msgItem => msgItem.SentDateTime >= fromDate);
}
var tillDate = searchCriteria.DateTimeTo;
if (tillDate.HasValue)
{
messages = messages.Where(msgItem => msgItem.SentDateTime <= tillDate);
}
var recordsToTake = searchCriteria.Take;
if (recordsToTake.HasValue)
{
messages = messages.Take(recordsToTake.Value);
}
var searchResult = messages.ToList();
return(searchResult);
}
public ActionResult List(MessageSearchCriteria filter = null)
{
if (filter == null)
{
filter = new MessageSearchCriteria();
filter.pg = 1;
}
filter.PageSize = Define.PAGE_SIZE;
filter.UserId = UserId;
filter.Role = (CommonEnum.GroupOfCustomer)_customerService.GetEntityById(UserId).GroupId;
ViewBag.MessageSourceList = _messageService.GetSourceList();
ViewBag.MessageList = _messageService.GetEntitsList(filter);
return(View());
}
public void GetMessagedTest()
{
IMittoMessageDao dal = Container.Resolve <IMittoMessageDao>();
var messageCriteris = new MessageSearchCriteria()
{
DateTimeFrom = DateTime.Now.AddDays(-5),
DateTimeTo = DateTime.Now,
Skip = 5,
Take = 10
};
var result = dal.GetMessagesInRange(messageCriteris);
Assert.IsNotNull(result);
}
public StatisticsResponse GetStatistics(DateTime dateTimeFrom,
DateTime dateTimeTo, string mcc)
{
MessageSearchCriteria criteria = new MessageSearchCriteria()
{
DateTimeFrom = dateTimeFrom,
DateTimeTo = dateTimeTo,
Mcc = mcc
};
var messages = _dal.GetStatisticsInRange(criteria).ToList();
var response = new StatisticsResponse()
{
Total = messages.Count,
Statistics = messages
};
return(response);
}
public SentMessageResponse GetSentSMS(
DateTime dateTimeFrom,
DateTime dateTimeTo,
int skip,
int take)
{
MessageSearchCriteria criteria = new MessageSearchCriteria()
{
DateTimeFrom = dateTimeFrom,
DateTimeTo = dateTimeTo,
Skip = skip,
Take = take
};
var messages = _dal.GetMessagesInRange(criteria).ToList();
var response = new SentMessageResponse()
{
Messages = messages,
TotalMessages = messages.Count
};
return(response);
}
/// <summary>
/// To be moved to DAL
/// </summary>
/// <param name="referenceSearchCriteria"></param>
/// <param name="orderBy"></param>
/// <returns></returns>
List<Thinkgate.Base.Classes.MessageCenter> SearchMessages(MessageSearchCriteria messageSearchCriteria, string orderBy = "PostOn DESC")
{
SessionObject obj = (SessionObject)Session["SessionObject"];
UserInfo userinfo = (UserInfo)Session["KenticoUserInfo"];
/* Kentico related parameters like site name, username etc would go configurable */
List<Thinkgate.Base.Classes.MessageCenter> messages = new List<Thinkgate.Base.Classes.MessageCenter>();
TreeNodeDataSet messagesDataSet = CMS.DocumentEngine.DocumentHelper.GetDocuments(ConfigurationManager.AppSettings["CMSSiteName"], ConfigurationManager.AppSettings["CMSTreePathToMessages"], "en-us", true, "TG.MessageCenter", messageSearchCriteria.SearchCriteria, orderBy, 5, false, treeProvider);
if (messagesDataSet != null && messagesDataSet.Tables.Count > 0 && messagesDataSet.Tables[0].Rows.Count > 0)
{
table = messagesDataSet.Tables[0];
bool hasPermission = UserHasPermission(Permission.Add_Message);
bool includeForAllClients = false;
for (int i = 0; i < messagesDataSet.Tables[0].Rows.Count; i++)
{
DataRow row = messagesDataSet.Tables[0].Rows[i];
if (!string.IsNullOrEmpty(row["DocumentID"].ToString()))
{
if (IsMessageSearchable(row, obj, userinfo))
{ messages.Add(AddMessage(row)); }
}
}
return messages;
}
else
{
return (new List<Thinkgate.Base.Classes.MessageCenter>());
}
}
private void PopulateGridWithCMSData(MessageSearchCriteria where, string orderBy)
{
lookupData = KenticoHelper.GetLookupDetailsByClient(null, null);
List<Thinkgate.Base.Classes.MessageCenter> messages = SearchMessages(where, orderBy);
radGridResults.DataSource = messages;
radGridResults.DataBind();
gridResultsPanel.Visible = true;
initMessage.Visible = false;
}
/// <summary>
/// Gets criteria lookup data from Kentico.
/// </summary>
/// <returns></returns>
//List<LookupDetails> GetLookupDetailsFromCMS()
//{
// List<LookupDetails> lookupData = new List<LookupDetails>();
// DataClassInfo customTable = DataClassInfoProvider.GetDataClass("TG.LookupDetails");
// if (customTable != null)
// {
// string kenticoUserName = KenticoHelper.getKenticoUser(SessionObject.LoggedInUser.ToString());
// UserInfo userInfo = UserInfoProvider.GetUserInfo(kenticoUserName);
// DataSet lookupDataSet = (new CMS.SiteProvider.CustomTableItemProvider(userInfo)).GetItems("TG.LookupDetails", string.Empty, string.Empty);
// if (lookupDataSet != null && lookupDataSet.Tables.Count > 0 && lookupDataSet.Tables[0].Rows.Count > 0)
// {
// foreach (DataRow row in lookupDataSet.Tables[0].Rows)
// {
// LookupDetails lookupTableEntity = new LookupDetails();
// lookupTableEntity.ID = DataIntegrity.ConvertToInt(row["ItemID"]);
// lookupTableEntity.Description = row["Description"].ToString();
// lookupTableEntity.Enum = DataIntegrity.ConvertToInt(row["Enum"]);
// lookupTableEntity.LookupEnum = (LookupType)Enum.Parse(typeof(LookupType), row["LookupEnum"].ToString());
// lookupData.Add(lookupTableEntity);
// }
// return lookupData;
// }
// }
// return null;
//}
#endregion
#region " Extract criteria controls criteria as WHERE condition "
/// <summary>
/// Should be moved to BL
/// </summary>
/// <param name="criteriaController"></param>
/// <returns></returns>
MessageSearchCriteria GetSelectedCriteria(CriteriaController criteriaController)
{
/* Get selected user group criteria value */
MessageSearchCriteria messageSearchCriteria = new MessageSearchCriteria();
string documentSearchCriteria = string.Empty;
List<Thinkgate.Controls.E3Criteria.DropDownList.ValueObject> selectedCriteria = null;
selectedCriteria = criteriaController.ParseCriteria<Thinkgate.Controls.E3Criteria.DropDownList.ValueObject>("UserGroup").ToList();
if (selectedCriteria.Count > 0)
{
documentSearchCriteria += " UserGroupEnum = '" + selectedCriteria[0].Value + "' ";
}
selectedCriteria = criteriaController.ParseCriteria<Thinkgate.Controls.E3Criteria.DropDownList.ValueObject>("Type").ToList();
if (selectedCriteria.Count > 0)
{
documentSearchCriteria = AppendSQLClause(documentSearchCriteria);
documentSearchCriteria += " MessageCenterEnum = '" + selectedCriteria[0].Value + "' ";
}
string searchText = string.Empty;
string searchOption = string.Empty;
List<TextWithDropdown.ValueObject> textSearchList = criteriaController.ParseCriteria<TextWithDropdown.ValueObject>("TextSearch");
if (textSearchList.Count > 0)
{
NameValue confirmedOption = TextSearchDropdownValues().Find(x => x.Name == textSearchList[0].Option) ?? TextSearchDropdownValues().First();
if (!String.IsNullOrEmpty(textSearchList[0].Text))
{
searchText = textSearchList[0].Text;
searchOption = confirmedOption.Value;
string[] searchWords = System.Text.RegularExpressions.Regex.Split(searchText, " ");
string[] searchColumns = new string[] { "Title", "Description", "Keyword" };
documentSearchCriteria = AppendSQLClause(documentSearchCriteria);
if (searchOption == "key")
{
documentSearchCriteria += " (Keyword LIKE '%" + searchText + "%') ";
}
else if (searchOption == "any")
{
string anyWordsCondition = TextSearchCondition(searchWords, searchColumns, true);
documentSearchCriteria += anyWordsCondition;
}
else if (searchOption == "all")
{
string allWordsCondition = TextSearchCondition(searchWords, searchColumns, false);
documentSearchCriteria += allWordsCondition;
}
else if (searchOption == "exact")
{
documentSearchCriteria += " ( Title LIKE '%" + searchText + "%' ";
documentSearchCriteria += " OR Keyword LIKE '%" + searchText + "%' ";
documentSearchCriteria += " OR Description LIKE '%" + searchText + "%') ";
}
}
}
var dateSelected = criteriaController.ParseCriteria<Thinkgate.Controls.E3Criteria.DateRange.ValueObject>("DateAdded").ToList();
if (dateSelected.Count > 0)
{
documentSearchCriteria = AppendSQLClause(documentSearchCriteria);
Master.ChangeCriteriaOrder(ref dateSelected);
if (dateSelected[0].Type == "Start")
{
documentSearchCriteria += " (convert(varchar,DateAdded,111) >= convert(datetime,'" + dateSelected[0].Date + "',111) ";
if (dateSelected.Count > 1 && !string.IsNullOrEmpty(dateSelected[1].Date))
documentSearchCriteria += " And convert(varchar,DateAdded,111) <= convert(datetime,'" + dateSelected[1].Date + "',111)) ";
else
documentSearchCriteria += ")";
}
else if (!string.IsNullOrEmpty(dateSelected[0].Date))
{ documentSearchCriteria += " (convert(varchar,DateAdded,111) <= convert(datetime,'" + dateSelected[0].Date + "',111))"; }
}
var dateSelected1 = criteriaController.ParseCriteria<Thinkgate.Controls.E3Criteria.DateRange.ValueObject>("PostOn").ToList();
if (dateSelected1.Count > 0)
{
documentSearchCriteria = AppendSQLClause(documentSearchCriteria);
Master.ChangeCriteriaOrder(ref dateSelected1);
if (dateSelected1[0].Type == "Start")
{
documentSearchCriteria += " (convert(varchar,PostOn,111) >= convert(datetime,'" + dateSelected1[0].Date + "',111) ";
if (dateSelected1.Count > 1 && !string.IsNullOrEmpty(dateSelected1[1].Date))
documentSearchCriteria += " And PostOn <= convert(datetime,'" + dateSelected1[1].Date + "',111)) ";
else
documentSearchCriteria += ")";
}
else if(!string.IsNullOrEmpty(dateSelected1[0].Date))
{ documentSearchCriteria += " (convert(varchar,PostOn,111) <= convert(datetime,'" + dateSelected1[0].Date + "',111))"; }
}
var dateSelected2 = criteriaController.ParseCriteria<Thinkgate.Controls.E3Criteria.DateRange.ValueObject>("RemoveOn").ToList();
if (dateSelected2.Count > 0)
{
documentSearchCriteria = AppendSQLClause(documentSearchCriteria);
Master.ChangeCriteriaOrder(ref dateSelected2);
if (dateSelected2[0].Type == "Start")
{
documentSearchCriteria += " (convert(varchar,RemoveOn,111) >= convert(datetime,'" + dateSelected2[0].Date + "',111) ";
if (dateSelected2.Count > 1 && !string.IsNullOrEmpty(dateSelected2[1].Date))
documentSearchCriteria += " And convert(varchar,RemoveOn,111) <= convert(datetime,'" + dateSelected2[1].Date + "',111)) ";
else
documentSearchCriteria += ")";
}
else if (!string.IsNullOrEmpty(dateSelected2[0].Date))
{ documentSearchCriteria += " (convert(varchar,RemoveOn,111) <= convert(datetime,'" + dateSelected2[0].Date + "',111))";}
}
if (!string.IsNullOrEmpty(documentSearchCriteria))
{
string lastString = documentSearchCriteria.Trim().Substring(documentSearchCriteria.Trim().Length - 3);
documentSearchCriteria = lastString.ToUpper() == "AND" ? documentSearchCriteria.Trim().Substring(0, documentSearchCriteria.Trim().Length - 3) : documentSearchCriteria;
}
messageSearchCriteria.SearchCriteria = documentSearchCriteria;
return messageSearchCriteria;
}
protected void ExportGridImgBtn_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
Master.RegisterScript();
if (gridResultsPanel.Visible == true) // code added for TFS 9942 if Grid is visible then process further.
{
MessageSearchCriteria messageSearchCriteria;
var criteria = this.Master.CurrentCriteria();
if (criteria == null)
{
messageSearchCriteria = new MessageSearchCriteria();
messageSearchCriteria.SearchCriteria = "";
}
else
{ messageSearchCriteria = this.GetSelectedCriteria(criteria); }
SessionObject obj = (SessionObject)Session["SessionObject"];
UserInfo userinfo = (UserInfo)Session["KenticoUserInfo"];
DataSet messagesDataSet = CMS.DocumentEngine.DocumentHelper.GetDocuments(ConfigurationManager.AppSettings["CMSSiteName"], ConfigurationManager.AppSettings["CMsTreePathToMessages"], "en-us", true, "TG.MessageCenter", messageSearchCriteria.SearchCriteria, "PostOn DESC", 5, false, treeProvider);
if (messagesDataSet != null && messagesDataSet.Tables.Count > 0 && messagesDataSet.Tables[0].Rows.Count > 0)
{
lookupData = KenticoHelper.GetLookupDetailsByClient(null, null);
using (DataTable dt = new DataTable())
{
table = messagesDataSet.Tables[0].Copy();
bool addMessagePermission = UserHasPermission(Thinkgate.Base.Enums.Permission.Add_Message);
foreach (DataColumn col in messagesDataSet.Tables[0].Columns)
{
switch (col.ColumnName)
{
case "Title":
case "UserGroupEnum":
case "MessageCenterEnum":
case "DateAdded":
case "PostOn":
case "RemoveOn":
case "ClientTargets":
case "DocumentCreatedByUserID":
break;
default:
table.Columns.Remove(col.ColumnName);
break;
}
}
dt.Columns.Add("Title");
dt.Columns.Add("UserGroup");
dt.Columns.Add("Type");
dt.Columns.Add("DateAdded");
if (addMessagePermission)
{
dt.Columns.Add("PostOn");
dt.Columns.Add("RemoveOn");
}
foreach (DataRow row in table.Rows)
{
DataRow dr = dt.NewRow();
dr["Title"] = row["Title"].ToString();
if (string.IsNullOrWhiteSpace(row["UserGroupEnum"].ToString()))
{ dr["UserGroup"] = string.Empty; }
else if (row["UserGroupEnum"].ToString() == "0")
{ dr["UserGroup"] = string.Empty; }
else
{ dr["UserGroup"] = lookupData.Where(l => l.Enum == DataIntegrity.ConvertToInt(row["UserGroupEnum"].ToString())).FirstOrDefault().Description; }
dr["Type"] = string.IsNullOrWhiteSpace(row["MessageCenterEnum"].ToString()) ? string.Empty : lookupData.Where(l => l.Enum == DataIntegrity.ConvertToInt(row["MessageCenterEnum"].ToString())).FirstOrDefault().Description;
if (row["DateAdded"] != null && row["DateAdded"] != DBNull.Value && DateTime.Parse(row["DateAdded"].ToString()) != DateTime.MinValue)
dr["DateAdded"] = DataIntegrity.ConvertToDate(row["DateAdded"].ToString()).ToShortDateString();
if (addMessagePermission)
{
if (row["PostOn"] != null && row["PostOn"] != DBNull.Value && DateTime.Parse(row["PostOn"].ToString()) != DateTime.MinValue)
dr["PostOn"] = DataIntegrity.ConvertToDate(row["PostOn"].ToString()).ToShortDateString();
else
dr["PostOn"] = string.Empty;
if (row["RemoveOn"] != null && row["RemoveOn"] != DBNull.Value && DateTime.Parse(row["RemoveOn"].ToString()) != DateTime.MinValue)
dr["RemoveOn"] = DataIntegrity.ConvertToDate(row["RemoveOn"].ToString()).ToShortDateString();
else
dr["RemoveOn"] = string.Empty;
}
if (IsMessageSearchable(row, obj, userinfo))
{ dt.Rows.Add(dr); }
}
if (dt.Rows.Count > 0)
{ ExportToExcel(dt); }
}
}
}
}
