public RequestDeliveryAgreementVo GetListDeliveryAgreementVo(IQueryInfo queryInfo)
{
var result = GetById(queryInfo.QueryId);
var listTracking =
PersistenceService.CurrentWorkspace.Context.Trackings.Where(o => o.RequestId == queryInfo.QueryId).Select(o => o.Distance).ToList();
double?requestDistance = 0;
if (listTracking.Count > 0)
{
requestDistance += listTracking.Sum(d => d.GetValueOrDefault());
}
return(new RequestDeliveryAgreementVo()
{
Id = result.Id,
RequestNo = result.RequestNo,
Signature = result.Signature != null?Convert.ToBase64String(result.Signature, 0, result.Signature.Length) : string.Empty,
RequestAgreed = result.IsAgreed ?? false,
RequestFrom = result.LocationFromObj.Address1
+ " " + result.LocationFromObj.Address2
+ ", " + result.LocationFromObj.City
+ ", " + result.LocationFromObj.StateOrProvinceOrRegion
+ ", " + result.LocationFromObj.Zip,
RequestTo = result.LocationToObj.Address1
+ " " + result.LocationToObj.Address2
+ ", " + result.LocationToObj.City
+ ", " + result.LocationToObj.StateOrProvinceOrRegion
+ ", " + result.LocationToObj.Zip,
RequestTimes = result.ActualStartTime == null || result.ActualEndTime == null ? 0 : (result.ActualEndTime - result.ActualStartTime).Value.Minutes,
RequestDistance = requestDistance.MetersToMiles(2).ToString("N"),
RequestFromName = result.LocationFromObj.Name,
RequestToName = result.LocationToObj.Name,
IsAgreed = result.IsAgreed,
});
}
public dynamic GetListHoldingRequest(IQueryInfo queryInfo)
{
var requestQueryInfo = (HoldingRequestQueryInfo)queryInfo;
var startDate = requestQueryInfo.StartDate.ToUniversalTime();
var endDate = requestQueryInfo.EndDate.ToUniversalTime();
//Expression<Func<HoldingRequest, bool>> expression = s => s.CreatedOn >= startDate && s.CreatedOn <= endDate;
BuildSortExpression(queryInfo);
var searchString = SearchStringForGetData(queryInfo);
var queryResult = (from entity in GetAll().Where(o => o.SendDate >= startDate && o.SendDate <= endDate)
select new { entity }).Select(s => new HoldingRequestVo()
{
Id = s.entity.Id,
LocationFromId = s.entity.LocationFrom,
LocationFrom = s.entity.Location.Name,
LocationToId = s.entity.LocationTo,
LocationTo = s.entity.Location1.Name,
Description = s.entity.Description,
EndTime = s.entity.EndTime,
EndTimeNoFormat = s.entity.EndTime,
StartTime = s.entity.StartTime,
StartTimeNoFormat = s.entity.StartTime,
SendDate = s.entity.SendDate
}).Where(searchString);
var data = queryResult.OrderBy(queryInfo.SortString).ToList();
queryInfo.TotalRecords = queryResult.Count();
return(new { Data = data, TotalRowCount = queryInfo.TotalRecords });
}
public override IQueryable <ReadOnlyGridVo> BuildQueryToGetDataForGrid(IQueryInfo queryInfo)
{
var queryResult = (from entity in GetAll()
select new { entity }).Select(s => new LocationGridVo
{
Id = s.entity.Id,
Name = s.entity.Name,
Address1 = s.entity.Address1,
Address2 = s.entity.Address2,
City = s.entity.City,
Zip = s.entity.Zip,
FullAddressSearch = s.entity.Address1
+ (!string.IsNullOrEmpty(s.entity.Address2)?", " + s.entity.Address2:"")
+ (!string.IsNullOrEmpty(s.entity.City) ? ", " + s.entity.City : "")
+ (!string.IsNullOrEmpty(s.entity.StateOrProvinceOrRegion) ? ", " + s.entity.StateOrProvinceOrRegion : "")
+ (!string.IsNullOrEmpty(s.entity.Zip) ? " " + s.entity.Zip : "")
+ (s.entity.CountryOrRegion != null && !string.IsNullOrEmpty(s.entity.CountryOrRegion.Name) ? ", " + s.entity.CountryOrRegion.Name : ""),
AvailableTimeNoFormat = s.entity.AvailableTime,
OpenHourNoFormat = s.entity.OpenHour,
CloseHourNoFormat = s.entity.CloseHour,
//
StateOrProvinceOrRegion = s.entity.StateOrProvinceOrRegion,
CountryOrRegion = s.entity.CountryOrRegion != null ? s.entity.CountryOrRegion.Name : "",
}).OrderBy(queryInfo.SortString);
return(queryResult);
}
/// <summary>
/// Create search condition to get data in grid
/// </summary>
/// <param name="queryInfo"></param>
/// <returns></returns>
public virtual string SearchStringForGetData(IQueryInfo queryInfo)
{
var searchString = string.Empty;
if (!string.IsNullOrEmpty(queryInfo.SearchString))
{
var searchConditionList = new List <string>();
queryInfo.SearchString = queryInfo.SearchString.Replace(' ', '+');
queryInfo.SearchString = Encoding.UTF8.GetString(Convert.FromBase64String(queryInfo.SearchString));
queryInfo.ParseParameters(queryInfo.SearchString);
if (!string.IsNullOrEmpty(queryInfo.SearchTerms))
{
var keyword = queryInfo.SearchTerms;
var searchCondition = new StringBuilder();
searchCondition.Append("(");
searchCondition.Append(String.Join(" OR ", SearchColumns.Select(column => string.Format(" {0}.Contains(\"{1}\")", column, keyword)).ToArray()));
searchCondition.Append(")");
searchConditionList.Add(searchCondition.ToString());
searchString = String.Join(" OR ", searchConditionList.ToArray <string>());
}
}
return(string.IsNullOrEmpty(searchString) ? " 1 = 1" : searchString);
}
protected override void BuildSortExpression(IQueryInfo queryInfo)
{
if (queryInfo.Sort == null || queryInfo.Sort.Count == 0)
{
queryInfo.Sort = new List <Sort> {
new Sort {
Field = "Id", Dir = "desc"
}
};
}
queryInfo.Sort.ForEach(x =>
{
if (x.Field == "CreatedDate")
{
x.Field = "CreatedDateNoFormat";
}
else if (x.Field == "CreatedBy")
{
x.Field = "LastNameCreatedBy";
}
else
{
x.Field = String.Format("{0}", x.Field);
}
});
}
protected override void BuildSortExpression(IQueryInfo queryInfo)
{
if (queryInfo.Sort == null || queryInfo.Sort.Count == 0)
{
queryInfo.Sort = new List <Sort> {
new Sort {
Field = "FirstName", Dir = ""
}
};
}
queryInfo.Sort.ForEach(x =>
{
if (x.Field == "FullName")
{
x.Field = "FirstName";
}
else if (x.Field == "HomePhoneInFormat")
{
x.Field = "HomePhone";
}
else if (x.Field == "MobilePhoneInFormat")
{
x.Field = "MobilePhone";
}
else
{
x.Field = string.Format("{0}", x.Field);
}
});
}
public override IQueryable <ReadOnlyGridVo> BuildQueryToGetDataForGrid(IQueryInfo queryInfo)
{
var queryUser = queryInfo as UserQueryInfo;
var queryResult = (from entity in GetAll()
where entity.UserRole.Name != "Courier" && entity.Id != queryUser.CurrentUserId
select new { entity }).Select(s => new UserGridVo
{
Id = s.entity.Id,
UserName = String.IsNullOrEmpty(s.entity.UserName) ? "" : s.entity.UserName,
Password = s.entity.Password,
FirstName = String.IsNullOrEmpty(s.entity.FirstName) ? "" : s.entity.FirstName,
MiddleName = String.IsNullOrEmpty(s.entity.MiddleName) ? "" : s.entity.MiddleName,
LastName = String.IsNullOrEmpty(s.entity.LastName) ? "" : s.entity.LastName,
FullNameSearch = s.entity.LastName + " " + s.entity.FirstName + " " + s.entity.MiddleName,
Role = s.entity.UserRole != null ? s.entity.UserRole.Name : "",
Email = String.IsNullOrEmpty(s.entity.Email) ? "" : s.entity.Email,
HomePhone = String.IsNullOrEmpty(s.entity.HomePhone) ? "" : s.entity.HomePhone,
MobilePhone = String.IsNullOrEmpty(s.entity.MobilePhone) ? "" : s.entity.MobilePhone,
IsActive = s.entity.IsActive,
Avatar = s.entity.Avatar,
UserRoleId = s.entity.UserRoleId >= 0 ? s.entity.UserRoleId:null,
//IsQuickspatchUser = s.entity.IsQuickspatchUser==true ? true:false
}).OrderBy(queryInfo.SortString);
var test = queryResult;
return(queryResult);
}
/// <summary>
/// This is default sort expression for simple masterfile
/// Need to custom on web page.
/// </summary>
/// <param name="queryInfo"></param>
protected virtual void BuildSortExpression(IQueryInfo queryInfo)
{
BuildDefaultSortExpression(queryInfo);
queryInfo.Sort.ForEach(x =>
{
x.Field = string.Format("entity.{0}", x.Field);
});
}
public List <T> Find(IQueryInfo queryInfo, string condition, params DbParameter[] paramList)
{
string str = string.Format("SELECT {0} FROM {1}{2}", queryInfo.SelectedFields, queryInfo.TableName, string.IsNullOrWhiteSpace(condition) ? string.Empty : " WHERE " + condition);
if (!string.IsNullOrWhiteSpace(queryInfo.SortField))//排序字段不为空时才排序
{
str += string.Format(" ORDER BY {3} {4}", queryInfo.SortField, queryInfo.IsDescending ? "DESC" : "ASC");
}
return(this.GetList(str, paramList));
}
public virtual string CreateMatching
(
string sourceTable,
ColumnInfo column,
IQueryInfo queryInfo,
IList <string> matchValues
)
{
if ((null != matchValues) && (matchValues.Count != 0))
{
MatchBuilder matchBuilder = new MatchBuilder( );
if (column.IsDateTime && matchValues.Count >= 2)
{
matchBuilder.Column(column).GreaterThanOrEqual( ).Value(matchValues [0]).And( ).
Column(column).LessThanOrEqual( ).Value(matchValues [1]);
}
else
{
for (int valueIndex = 0; valueIndex < matchValues.Count; valueIndex++)
{
string stringValue = matchValues[valueIndex];
if (string.IsNullOrWhiteSpace(stringValue))
{
continue;
}
matchBuilder.Column(column);
//TODO:??
//if ( queryInfo.)
if (queryInfo.ExactMatch)
{
matchBuilder.Equals( );
}
else
{
matchBuilder.Like( );
}
matchBuilder.Value(stringValue);
if (valueIndex != matchValues.Count - 1)
{
matchBuilder.Or( );
}
}
}
return(matchBuilder.Match.ToString( ));
}
return("");
}
protected override void BuildSortExpression(IQueryInfo queryInfo)
{
if (queryInfo.Sort == null || queryInfo.Sort.Count == 0)
{
queryInfo.Sort = new List <Sort> {
new Sort {
Field = "SendDate", Dir = "desc"
}
};
}
}
/// <summary>
/// Default sort by ShortOrder then ShortName
/// </summary>
/// <param name="queryInfo"></param>
protected virtual void BuildDefaultSortExpression(IQueryInfo queryInfo)
{
if (queryInfo.Sort == null || queryInfo.Sort.Count == 0)
{
queryInfo.Sort = new List <Sort> {
new Sort {
Field = "Id", Dir = "desc"
}
};
}
}
public override IQueryable <ReadOnlyGridVo> BuildQueryToGetDataForGrid(IQueryInfo queryInfo)
{
var queryResult = (from entity in GetAll()
select new { entity }).Select(s => new CountryOrRegionGridVo
{
Id = s.entity.Id,
Name = s.entity.Name
});
return(queryResult);
}
public override IQueryable <ReadOnlyGridVo> BuildQueryToGetDataForGrid(IQueryInfo queryInfo)
{
var queryResult = GetAll().OrderBy(queryInfo.SortString).Select(s => new StateGridVo
{
Id = s.Id,
Name = s.Name,
AbbreviationName = s.AbbreviationName,
});
return(queryResult);
}
protected override void BuildSortExpression(IQueryInfo queryInfo)
{
if (queryInfo.Sort == null || (queryInfo.Sort != null && queryInfo.Sort.Count == 0))
{
queryInfo.Sort = new List <Sort> {
new Sort {
Field = "Name", Dir = "asc"
}
};
}
}
public override IQueryable <ReadOnlyGridVo> BuildQueryToGetDataForGrid(IQueryInfo queryInfo)
{
var queryResult = (from entity in GetAll()
select new { entity }).Select(s => new ModuleGridVo()
{
Id = s.entity.Id,
Name = s.entity.Name
}).OrderBy(queryInfo.SortString);
return(queryResult);
}
protected override void BuildSortExpression(IQueryInfo queryInfo)
{
if (queryInfo.Sort == null || queryInfo.Sort.Count == 0)
{
queryInfo.Sort = new List <Sort> {
new Sort {
Field = "EndTime", Dir = "desc"
}
};
}
queryInfo.Sort.ForEach(x =>
{
if (x.Field == "Courier")
{
x.Field = "LastNameCourier";
}
else if (x.Field == "LocationFromName")
{
x.Field = "LocationFromName";
}
else if (x.Field == "LocationToName")
{
x.Field = "LocationToName";
}
else if (x.Field == "Time")
{
x.Field = "TimeNoFormat";
}
else if (x.Field == "Status")
{
x.Field = "StatusId";
}
else if (x.Field == "Type")
{
x.Field = "Type";
}
else if (x.Field == "Note")
{
x.Field = "Note";
}
else if (x.Field == "CreatedDate")
{
x.Field = "CreatedDateNoFormat";
}
else if (x.Field == "CreatedBy")
{
x.Field = "LastNameCreatedBy";
}
else
{
x.Field = String.Format("{0}", x.Field);
}
});
}
public override IQueryable <ReadOnlyGridVo> BuildQueryToGetDataForGrid(IQueryInfo queryInfo)
{
var queryResult = (from entity in GetAll()
select new { entity }).OrderBy(queryInfo.SortString).Select(s => new SystemConfigurationGridVo
{
Id = s.entity.Id,
Name = s.entity.Name,
Value = s.entity.Value
});
return(queryResult.OrderBy(o => o.Id));
}
public override IQueryable <ReadOnlyGridVo> BuildQueryToGetDataForGrid(IQueryInfo queryInfo)
{
var queryResult = (from entity in GetAll()
select new { entity }).OrderBy(queryInfo.SortString).Select(s => new TemplateGridVo
{
Id = s.entity.Id,
Title = s.entity.Title,
TemplateTypeId = (int)s.entity.TemplateType
});
return(queryResult);
}
public TEntity FindSingle <TEntity>(IQueryInfo q, string condition, params DbParameter[] paramList)
where TEntity : new()
{
TEntity local = default(TEntity);
List <TEntity> list = this.Find <TEntity>(q, condition, paramList);
if (list.Count > 0)
{
local = list[0];
}
return(local);
}
public TEntity FindSingle <TEntity>(IQueryInfo q, string condition, params DbParameter[] parameters)
where TEntity : BaseEntity, new()
{
TEntity local = new TEntity();
List <TEntity> list = this.Find <TEntity>(q, condition, parameters);
if (list.Count > 0)
{
local = list[0];
}
return(local);
}
public override IQueryable <ReadOnlyGridVo> BuildQueryToGetDataForGrid(IQueryInfo queryInfo)
{
// Show all user who is not admin
var queryResult = (from entity in GetAll()
select new { entity }).OrderBy(queryInfo.SortString).Select(s => new UserRoleGridVo
{
Id = s.entity.Id,
Name = String.IsNullOrEmpty(s.entity.Name) ? "" : s.entity.Name,
AppRoleName = String.IsNullOrEmpty(s.entity.AppRoleName) ? "" : s.entity.AppRoleName,
});
return(queryResult);
}
/// <summary>
/// 判断指定的Entity是否有主键信息值
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="t"></param>
/// <param name="queryInfo"></param>
/// <returns></returns>
private bool HasPrimaryKey <TEntity>(TEntity t, IQueryInfo queryInfo, out object value)
{
value = null;
foreach (PropertyInfo info in typeof(TEntity).GetProperties())
{
if (info.Name == queryInfo.PrimaryKey)
{
value = info.GetValue(t, null);
return(SafeTypeCheck(value));
}
}
return(false);
}
public override IQueryable <ReadOnlyGridVo> BuildQueryToGetDataForGrid(IQueryInfo queryInfo)
{
var noteRequestQueryInfo = (NoteRequestQueryInfo)queryInfo;
var queryResult = GetAll().Where(p => p.RequestId == noteRequestQueryInfo.RequestId).Select(entity => new NoteRequestGridVo()
{
Id = entity.Id,
Description = entity.Description,
CreatedDateNoFormat = entity.CreatedOn,
FirstNameCreatedBy = entity.CreatedBy != null ? entity.CreatedBy.FirstName : "",
MiddleNameCreatedBy = entity.CreatedBy != null ? entity.CreatedBy.MiddleName : "",
LastNameCreatedBy = entity.CreatedBy != null ? entity.CreatedBy.LastName : "",
}).OrderBy(noteRequestQueryInfo.SortString);
return(queryResult);
}
protected override void BuildSortExpression(IQueryInfo queryInfo)
{
if (queryInfo.Sort == null || queryInfo.Sort.Count == 0)
{
queryInfo.Sort = new List <Sort> {
new Sort {
Field = "Name", Dir = "asc"
}
};
}
queryInfo.Sort.ForEach(x =>
{
x.Field = String.Format("entity.{0}", x.Field);
});
}
private int DeleteWithTransaction(IQueryInfo q, string id)
{
int affectedRows = 0;
if (q.Relationship.Count > 0)
{
//先删除关系表的记录
foreach (var r in QueryInfo.Relationship)
{
affectedRows += DeleteWithTransaction(r.TableName, r.FieldName ?? q.PrimaryKey, id);
}
}
//删除主表记录
affectedRows += DeleteWithTransaction(q.TableName, q.PrimaryKey, id);
return(affectedRows);
}
protected override void BuildSortExpression(IQueryInfo queryInfo)
{
if (queryInfo.Sort == null || queryInfo.Sort.Count == 0)
{
queryInfo.Sort = new List <Sort> {
new Sort {
Field = "Id", Dir = "desc"
}
};
}
queryInfo.Sort.ForEach(x =>
{
if (x.Field == "Name")
{
x.Field = "Name";
}
});
}
/// <summary>
/// Get data for create a grid for entity
/// </summary>
/// <param name="queryInfo">Query info</param>
/// <returns></returns>
public virtual dynamic GetDataForGridMasterfile(IQueryInfo queryInfo)
{
BuildSortExpression(queryInfo);
// Caculate for search string
var searchString = SearchStringForGetData(queryInfo);
var finalResult = BuildQueryToGetDataForGrid(queryInfo).AsQueryable().Where(searchString);
queryInfo.TotalRecords = finalResult.Count();
var data = finalResult.Skip(queryInfo.Skip)
.Take(queryInfo.Take)
.ToList();
dynamic result = new ExpandoObject();
result.Data = data;
result.TotalRowCount = queryInfo.TotalRecords;
return(result);
}
public override IQueryable <ReadOnlyGridVo> BuildQueryToGetDataForGrid(IQueryInfo queryInfo)
{
var requestQueryInfo = (RequestQueryInfo)queryInfo;
var startDate = requestQueryInfo.StartDate.ToUniversalTime();
var endDate = requestQueryInfo.EndDate.ToUniversalTime();
Expression <Func <Request, bool> > expression = s => s.CreatedOn >= startDate && s.CreatedOn <= endDate;
var finalResult = GetAll().Where(expression).Select(request => new RequestGridVo
{
Id = request.Id,
RequestNo = request.RequestNo,
CourierId = request.CourierId,
FirstNameCourier = request.Courier != null && request.Courier.User != null ? request.Courier.User.FirstName : "",
MiddleNameCourier = request.Courier != null && request.Courier.User != null ? request.Courier.User.MiddleName : "",
LastNameCourier = request.Courier != null && request.Courier.User != null ? request.Courier.User.LastName : "",
CourierSearch = request.Courier != null && request.Courier.User != null ? (request.Courier.User.FirstName + " " + request.Courier.User.LastName + (!string.IsNullOrEmpty(request.Courier.User.MiddleName) ? " " + request.Courier.User.MiddleName : "")) : "",
LocationFromId = request.LocationFrom,
LocationToId = request.LocationTo,
LocationFromName = request.LocationFromObj != null ? request.LocationFromObj.Name : "",
LocationToName = request.LocationToObj != null ? request.LocationToObj.Name : "",
Type = request.IsStat == true ? "Priority Job" : "Normal",
StatusId = request.Status,
TimeNoFormat = request.SendingTime,
SendingTime = request.SendingTime,
StartTime = request.StartTime,
StartTimeNoFormat = request.StartTime,
EndTime = request.EndTime,
EndTimeNoFormat = request.EndTime,
Note = request.Description.Length > 200 ? request.Description.Substring(0, 200) + "..." : request.Description,
CreatedDateNoFormat = request.CreatedOn,
FirstNameCreatedBy = request.CreatedBy != null ? request.CreatedBy.FirstName : "",
MiddleNameCreatedBy = request.CreatedBy != null ? request.CreatedBy.MiddleName : "",
LastNameCreatedBy = request.CreatedBy != null ? request.CreatedBy.LastName : "",
IsExpired = (request.IsExpired ?? false),
IsSchedule = request.HistoryScheduleId != null,
IsAgreed = (request.IsAgreed ?? false),
IsWarning = (request.IsWarning ?? false),
CreatedOn = request.CreatedOn
}).OrderBy(requestQueryInfo.SortString);//.OrderByDescending(o=>o.CreatedOn);
return(finalResult);
}
public static string GetShellInfoTipText(IntPtr pIDL, bool fAllowSlow)
{
if (pIDL != IntPtr.Zero)
{
IShellFolder ppv = null;
IQueryInfo o = null;
try {
IntPtr ptr;
if (PInvoke.SHBindToParent(pIDL, ExplorerGUIDs.IID_IShellFolder, out ppv, out ptr) == 0)
{
Guid riid = ExplorerGUIDs.IID_IQueryInfo;
IntPtr[] apidl = new IntPtr[] { ptr };
uint rgfReserved = 0;
object obj2;
if (ppv.GetUIObjectOf(IntPtr.Zero, 1, apidl, ref riid, ref rgfReserved, out obj2) == 0)
{
string str;
o = obj2 as IQueryInfo;
if ((o != null) && (o.GetInfoTip(fAllowSlow ? 8 : 0, out str) == 0))
{
return(str);
}
}
}
}
catch (Exception exception) {
QTUtility2.MakeErrorLog(exception, null);
}
finally {
if (ppv != null)
{
Marshal.ReleaseComObject(ppv);
}
if (o != null)
{
Marshal.ReleaseComObject(o);
}
}
}
return(null);
}
public static bool GetIQueryInfo(ShellItem item, out IntPtr iQueryInfoPtr, out IQueryInfo iQueryInfo)
{
ShellItem parent = item.ParentItem != null ? item.ParentItem : item;
if (parent.ShellFolder.GetUIObjectOf(
IntPtr.Zero,
1,
new IntPtr[] { item.PIDLRel.Ptr },
ref ShellAPI.IID_IQueryInfo,
IntPtr.Zero,
out iQueryInfoPtr) == ShellAPI.S_OK)
{
iQueryInfo =
(IQueryInfo)Marshal.GetTypedObjectForIUnknown(iQueryInfoPtr, typeof(IQueryInfo));
return true;
}
else
{
iQueryInfo = null;
iQueryInfoPtr = IntPtr.Zero;
return false;
}
}
public static bool GetIQueryInfo(ShellNode item, out IntPtr iQueryInfoPtr, out IQueryInfo iQueryInfo)
{
ShellNode parent = item.ParentItem ?? item;
if (parent.ShellFolder.GetUIObjectOf(IntPtr.Zero, 1, new[] { item.PIDLRel.Ptr }, ref ShellGuids.IQueryInfo, IntPtr.Zero, out iQueryInfoPtr) == 0)
{
iQueryInfo = (IQueryInfo)Marshal.GetTypedObjectForIUnknown(iQueryInfoPtr, typeof(IQueryInfo));
return true;
}
else
{
iQueryInfo = null;
iQueryInfoPtr = IntPtr.Zero;
return false;
}
}
