void GetOrderActivities(List<Activity> activityList, ActivityCriteria criteria)
{
var orderClient = _quartetClientFactory.GetCustomersQueryServiceClient();
var orders = orderClient.GetCustomerOrders(criteria.CustomerId, false, false);
if (orders != null && orders.Length > 0)
{
foreach (var order in orders)
{
int itemCount = 0;
if (order.ProductsOrdered != null && order.ProductsOrdered.Length > 0)
foreach (var item in order.ProductsOrdered)
itemCount += item.Quantity;
if (order.OrderDateUtc.HasValue)
activityList.Add(new Activity
{
ActivityType = "Order",
DisplayDateUtc = order.OrderDateUtc.Value,
ItemCount = itemCount,
OrderStatus = order.Status,
OrderId = order.OrderId,
EstimatedOrderAmount = order.EstimatedOrderAmount
});
}
}
}
void GetNoteActivities(List<Activity> activityList, ActivityCriteria criteria, Customer customer)
{
if (customer.Notes != null && customer.Notes.Count > 0)
{
foreach (var note in customer.Notes.Values)
{
activityList.Add(new Activity
{
ActivityType = "Note",
Content = note.Content,
DisplayDateUtc = note.DateCreatedUtc
});
}
}
}
public ActivitySummary GetActivitySummary(ActivityCriteria criteria)
{
var activityList = new List<Activity>();
var client = _quartetClientFactory.GetCustomersQueryServiceClient();
var customer = client.GetCustomer(criteria.CustomerId);
GetOrderActivities(activityList, criteria);
GetNoteActivities(activityList, criteria, customer);
GetTaskActivities(activityList, criteria);
var activities = activityList
.OrderByDescending(a => a.DisplayDateUtc)
.Skip((criteria.Page * criteria.PageSize) - criteria.PageSize)
.Take(criteria.PageSize)
.ToArray();
return new ActivitySummary { Activities = activities, Count = activityList.Count };
}
internal static string BuildSql(
string Number,
IList <PipeMillSizeType> CheckedPipeTypes,
ActivityCriteria Activity,
HashSet <PipeMillStatus> CheckedStatusTypes,
DateTime externalCoatingDate,
DateTime internalCoatingDate,
DateTime weldingDate,
string HeatNumber)
{
StringBuilder sb = new StringBuilder();
sb.Append(
@" SELECT
[Pipe].[id] as Id,
[Pipe].[number],
[Heat].[number] as HeatNumber,
[Pipe].[mill] as Mill,
[Pipe].[productionDate],
[Pipe].[pipeMillStatus] as PipeMillStatus,
[Pipe].[isActive],
[PurchaseOrder].[number] as PurchaseOrderNumber,
[PipeMillSizeType].[type] as [Type]
FROM
[Pipe] join [Plate] on ([Plate].[id] = [Pipe].[plateId])
join [Heat] on ([Heat].[id] = [Plate].[heatId])
left join [PurchaseOrder] on ([PurchaseOrder].[id] = [Pipe].[purchaseOrderId])
left join [PipeMillSizeType] on ([PipeMillSizeType].[id] = [Pipe].[typeId]) ");
sb.Append(string.Format(@" WHERE [Pipe].[number] LIKE N'{0}%' ESCAPE '\' ", Number.EscapeCharacters()));
sb.Append(string.Format(@" AND [Heat].[number] LIKE N'{0}%' ESCAPE '\' ", HeatNumber.EscapeCharacters()));
if (CheckedPipeTypes.Count > 0)
{
sb.Append(" AND [Pipe].[typeId] IN (");
foreach (var t in CheckedPipeTypes)
{
sb.Append(string.Format(" N'{0}',", t.Id.ToString()));
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" )");
}
if (Activity == ActivityCriteria.StatusActive)
{
sb.Append(string.Format(" AND [Pipe].[isActive] = N'{0}'", true));
}
else if (Activity == ActivityCriteria.StatusUnactive)
{
sb.Append(string.Format(" AND [Pipe].[isActive] = N'{0}'", false));
}
if (CheckedStatusTypes.Count > 0)
{
sb.Append(" AND PipeMillStatus IN (");
foreach (var s in CheckedStatusTypes)
{
sb.Append(string.Format(" N'{0}',", Enum.GetName(typeof(PipeMillStatus), s)));
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" )");
}
string coatingDateQuery = @" AND EXISTS(
SELECT 1
FROM
[Coat]
WHERE
[Coat].[pipeId] = [Pipe].[id] AND
[Coat].[type] = N'{0}' AND
CAST([Coat].[date] AS DATE) = CAST(N'{1}' AS DATE))";
string sqlDateTimeFormat = "yyyy-MM-dd HH:mm:ss";
if (externalCoatingDate != DateTime.MinValue)
{
sb.AppendFormat(coatingDateQuery,
CoatingType.External,
externalCoatingDate.Date.ToString(sqlDateTimeFormat));
}
if (internalCoatingDate != DateTime.MinValue)
{
sb.AppendFormat(coatingDateQuery,
CoatingType.Internal,
internalCoatingDate.Date.ToString(sqlDateTimeFormat));
}
if (weldingDate != DateTime.MinValue)
{
sb.AppendFormat(@" AND EXISTS(
SELECT 1
FROM
[Weld]
WHERE
[Weld].[pipeId] = [Pipe].[id] AND
CAST([Weld].[date] AS DATE) = CAST(N'{0}' AS DATE))",
weldingDate.Date.ToString(sqlDateTimeFormat));
}
sb.AppendLine("ORDER BY [Pipe].[number] ASC");
return(sb.ToString());
}
internal static string BuildSql(System.ComponentModel.BindingList <PartType> partTypes, string number, ActivityCriteria Activity)
{
if (partTypes.Count == 0)
{
return(" ");
}
if (!string.IsNullOrWhiteSpace(number))
{
if (Activity == ActivityCriteria.StatusActive)
{
number = string.Format(@"WHERE isActive = N'{0}' and number LIKE N'{1}%' ESCAPE '\' ", true, number.EscapeCharacters());
}
else if (Activity == ActivityCriteria.StatusUnactive)
{
number = string.Format(@"WHERE isActive = N'{0}' and number LIKE N'{1}%' ESCAPE '\' ", false, number.EscapeCharacters());
}
else
{
number = string.Format(@"WHERE number LIKE N'{0}%' ESCAPE '\' ", number.EscapeCharacters());
}
}
else
{
if (Activity == ActivityCriteria.StatusActive)
{
number = string.Format(@"WHERE isActive = N'{0}' ", true);
}
else if (Activity == ActivityCriteria.StatusUnactive)
{
number = string.Format(@"WHERE isActive = N'{0}' ", false);
}
}
StringBuilder sb = new StringBuilder();
List <string> queries = new List <string>();
foreach (var item in partTypes)
{
switch (item)
{
case PartType.Undefined:
break;
case PartType.Pipe:
queries.Add(string.Format(" SELECT id, number, isActive,'{0}' FROM pipe {1}", PartType.Pipe, number));
break;
case PartType.Spool:
queries.Add(string.Format(" SELECT id, number, isActive, '{0}' FROM Spool {1}", PartType.Spool, number));
break;
case PartType.Component:
queries.Add(string.Format(" SELECT id, number, isActive, '{0}' FROM Component {1}", PartType.Component, number));
break;
default:
break;
}
queries.Add(" UNION ");
}
//delete last UNION
queries.RemoveAt(queries.Count - 1);
queries.Add(" ORDER BY number ASC ");
queries.ForEach(x => sb.Append(x));
return(sb.ToString());
}
void GetTaskActivities(List<Activity> activityList, ActivityCriteria criteria)
{
var client = _quartetClientFactory.GetTaskQueryServiceClient();
var results = client.QueryTaskListByCustomerId(new CustomerIdParameters{ CustomerId = criteria.CustomerId });
if (results != null && results.Length > 0)
{
foreach (var result in results)
activityList.Add(_taskMapping.Map(result));
}
}
internal static string BuildSql(System.ComponentModel.BindingList<PartType> partTypes, string number, ActivityCriteria Activity)
{
if(partTypes.Count == 0)
{
return " ";
}
if (!string.IsNullOrWhiteSpace(number))
{
if (Activity == ActivityCriteria.StatusActive)
{
number = string.Format(@"WHERE isActive = N'{0}' and number LIKE N'{1}%' ESCAPE '\' ", true ,number.EscapeCharacters());
}
else if (Activity == ActivityCriteria.StatusUnactive)
{
number = string.Format(@"WHERE isActive = N'{0}' and number LIKE N'{1}%' ESCAPE '\' ", false, number.EscapeCharacters());
}
else
{
number = string.Format(@"WHERE number LIKE N'{0}%' ESCAPE '\' ", number.EscapeCharacters());
}
}
else
{
if (Activity == ActivityCriteria.StatusActive)
{
number = string.Format(@"WHERE isActive = N'{0}' ", true);
}
else if (Activity == ActivityCriteria.StatusUnactive)
{
number = string.Format(@"WHERE isActive = N'{0}' ", false);
}
}
StringBuilder sb = new StringBuilder();
List<string> queries = new List<string>();
foreach(var item in partTypes)
{
switch(item)
{
case PartType.Undefined:
break;
case PartType.Pipe:
queries.Add(string.Format(" SELECT id, number, isActive,'{0}' FROM pipe {1}", PartType.Pipe, number));
break;
case PartType.Spool:
queries.Add(string.Format(" SELECT id, number, isActive, '{0}' FROM Spool {1}", PartType.Spool, number));
break;
case PartType.Component:
queries.Add(string.Format(" SELECT id, number, isActive, '{0}' FROM Component {1}",PartType.Component, number));
break;
default:
break;
}
queries.Add(" UNION ");
}
//delete last UNION
queries.RemoveAt(queries.Count - 1);
queries.Add(" ORDER BY number ASC ");
queries.ForEach(x => sb.Append(x));
return sb.ToString();
}
private void LoadActivityData()
{
PagingInfo _Page = new PagingInfo(cmsActivityFeed.MaxResults);
_Page.CurrentPage = currentPageNumber;
ActivityCriteria criteria = new ActivityCriteria();
criteria.PagingInfo = _Page;
criteria.OrderByDirection = EkEnumeration.OrderByDirection.Descending;
criteria.OrderByField = Ektron.Cms.Activity.ActivityProperty.Date;
List<long> userIdlist = new List<long>();
List<long> groupIdList = new List<long>();
foreach (string item in FriendPreferences)
{
if (userInfo.DisplayName.ToLower() == item.Substring(item.IndexOf(">")+1).ToLower())
{
userIdlist.Add(userInfo.Id);
}
for (int i = 0; i <= friendList.Length - 1; i++)
{
if (friendList[i].DisplayName == item.Substring(item.IndexOf(">") + 1))
{
userIdlist.Add(friendList[i].Id);
}
}
}
foreach (string groupItem in GroupPreferences)
{
for (int i = 0; i <= groupList.Length - 1; i++)
{
if (groupList[i].GroupName == groupItem.Substring(groupItem.IndexOf(">") + 1))
{
groupIdList.Add(groupList[i].GroupId);
}
}
}
criteria.Condition = LogicalOperation.Or;
if (userIdlist.Count > 0)
{
criteria.AddFilter(Ektron.Cms.Activity.ActivityProperty.ActionUserId, CriteriaFilterOperator.In, userIdlist);
}
if (groupIdList.Count > 0)
{
CriteriaFilterGroup<Ektron.Cms.Activity.ActivityProperty> group = new CriteriaFilterGroup<Ektron.Cms.Activity.ActivityProperty>();
group.Condition = LogicalOperation.And;
group.AddFilter(Ektron.Cms.Activity.ActivityProperty.ScopeObjectType, CriteriaFilterOperator.EqualTo, EkEnumeration.ActivityFeedType.CommunityGroup);
group.AddFilter(Ektron.Cms.Activity.ActivityProperty.ScopeObjectId, CriteriaFilterOperator.In, groupIdList);
criteria.FilterGroups.Add(group);
}
if (userIdlist.Count > 0 || groupIdList.Count > 0)
{
ActivityData = _activityApi.GetUserActivityStream(criteria, _activityApi.RequestInformation.UserId);
totalPagesNumber = criteria.PagingInfo.TotalPages;
}
PageSettings();
}
internal static string BuildSql(
string Number,
IList<PipeMillSizeType> CheckedPipeTypes,
ActivityCriteria Activity,
HashSet<PipeMillStatus> CheckedStatusTypes,
DateTime externalCoatingDate,
DateTime internalCoatingDate,
DateTime weldingDate)
{
StringBuilder sb = new StringBuilder();
sb.Append(
@" SELECT
[Pipe].[id] as Id,
[Pipe].[number],
[Heat].[number] as HeatNumber,
[Pipe].[mill] as Mill,
[Pipe].[productionDate],
[Pipe].[pipeMillStatus] as PipeMillStatus,
[Pipe].[isActive],
[PurchaseOrder].[number] as PurchaseOrderNumber,
[PipeMillSizeType].[type] as [Type]
FROM
[Pipe] join [Plate] on ([Plate].[id] = [Pipe].[plateId])
join [Heat] on ([Heat].[id] = [Plate].[heatId])
left join [PurchaseOrder] on ([PurchaseOrder].[id] = [Pipe].[purchaseOrderId])
left join [PipeMillSizeType] on ([PipeMillSizeType].[id] = [Pipe].[typeId]) ");
sb.Append(string.Format(@" WHERE [Pipe].[number] LIKE N'{0}%' ESCAPE '\' ", Number.EscapeCharacters()));
if (CheckedPipeTypes.Count > 0)
{
sb.Append(" AND [Pipe].[typeId] IN (");
foreach (var t in CheckedPipeTypes)
{
sb.Append(string.Format(" N'{0}',", t.Id.ToString()));
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" )");
}
if (Activity == ActivityCriteria.StatusActive)
{
sb.Append(string.Format(" AND [Pipe].[isActive] = N'{0}'", true));
}
else if (Activity == ActivityCriteria.StatusUnactive)
{
sb.Append(string.Format(" AND [Pipe].[isActive] = N'{0}'", false));
}
if (CheckedStatusTypes.Count > 0)
{
sb.Append(" AND PipeMillStatus IN (");
foreach (var s in CheckedStatusTypes)
{
sb.Append(string.Format(" N'{0}',", Enum.GetName(typeof(PipeMillStatus),s)));
}
sb.Remove(sb.Length - 1, 1);
sb.Append(" )");
}
string coatingDateQuery = @" AND EXISTS(
SELECT 1
FROM
[Coat]
WHERE
[Coat].[pipeId] = [Pipe].[id] AND
[Coat].[type] = N'{0}' AND
CAST([Coat].[date] AS DATE) = CAST(N'{1}' AS DATE))";
string sqlDateTimeFormat = "yyyy-MM-dd HH:mm:ss";
if (externalCoatingDate != DateTime.MinValue)
{
sb.AppendFormat(coatingDateQuery,
CoatingType.External,
externalCoatingDate.Date.ToString(sqlDateTimeFormat));
}
if(internalCoatingDate != DateTime.MinValue)
{
sb.AppendFormat(coatingDateQuery,
CoatingType.Internal,
internalCoatingDate.Date.ToString(sqlDateTimeFormat));
}
if (weldingDate != DateTime.MinValue)
{
sb.AppendFormat(@" AND EXISTS(
SELECT 1
FROM
[Weld]
WHERE
[Weld].[pipeId] = [Pipe].[id] AND
CAST([Weld].[date] AS DATE) = CAST(N'{0}' AS DATE))",
weldingDate.Date.ToString(sqlDateTimeFormat));
}
sb.AppendLine("ORDER BY [Pipe].[number] ASC");
return sb.ToString();
}
