/// <summary>
/// 创建EF上下文对象,已存在就直接取,不存在就创建,保证线程内是唯一
/// </summary>
public static DbContext Create()
{
DbContext dbContext = CallContext.GetData("DbContext") as DbContext;
if (dbContext == null)
{
dbContext = new HouseSelectionDBEntities();
CallContext.SetData("DbContext", dbContext);
}
return(dbContext);
}
public List <DialedInfo> GetSubscriberDialingStatus(int GroupID, int StartSeq, int EndSeq)
{
using (HouseSelectionDBEntities DB = new HouseSelectionDBEntities())
{
DateTime?dt = null;
return((from Temp in ((from SN in DB.ShakingNumberResult
join M in DB.SubscriberProjectMapping on SN.SubscriberProjectMappingID equals M.ID
join S in DB.Subscriber on M.SubscriberID equals S.ID
join Htmp in DB.HouseSelectPeriod on SN.ID equals Htmp.ShakingNumberResultID into temp
from H in temp.DefaultIfEmpty()
where SN.ProjectGroupID == GroupID &&
SN.SelectHouseSequance >= StartSeq &&
SN.SelectHouseSequance <= EndSeq
select new
{
ShakingNumberResultId = SN.ID,
ShakingNumberSequance = SN.ShakingNumberSequance,
SelectHouseSequence = SN.SelectHouseSequance,
Name = S.Name,
IdentityID = S.IdentityNumber,
//BeginTime = H == null ? null : H.StartTime.ToString("yyyy-MM-dd HH:mm:ss"),
BeginTime = H == null ? dt : H.StartTime,
//EndTime = H == null ? null : H.EndTime.ToString("yyyy-MM-dd HH:mm:ss"),
EndTime = H == null ? dt : H.EndTime,
CallTimes = SN.NoticeTime,
IsContacted = SN.IsContacted,
IsCallBack = SN.IsCallBack,
IsMessageSend = SN.IsMessageSend,
ResultType = (SN.TelephoneNoticeRecord.FirstOrDefault()) == null ? 0 : SN.TelephoneNoticeRecord.OrderByDescending(x => x.ID).FirstOrDefault().ResultType,
//LastCallTime = (SN.LastUpdate ?? SN.CreateTime).ToString("yyyy-MM-dd HH:mm:ss"),
LastCallTime = SN.NoticeTime == 0 ? null : SN.LastUpdate,
Phone = S.Telephone
}).ToList())
select new DialedInfo
{
ShakingNumberResultId = Temp.ShakingNumberResultId,
ShakingNumberSequance = Temp.ShakingNumberSequance,
SelectHouseSequence = Temp.SelectHouseSequence,
Name = Temp.Name,
IdentityID = Temp.IdentityID,
BeginTime = Temp.BeginTime == null ? null : ((DateTime)Temp.BeginTime).ToString("yyyy-MM-dd HH:mm:ss"),
EndTime = Temp.EndTime == null ? null : ((DateTime)Temp.EndTime).ToString("yyyy-MM-dd HH:mm:ss"),
CallTimes = Temp.CallTimes,
IsContacted = Temp.IsContacted,
IsCallBack = Temp.IsCallBack,
IsMessageSend = Temp.IsMessageSend,
ResultType = Temp.ResultType,
LastCallTime = Temp.LastCallTime == null ? null : ((DateTime)Temp.LastCallTime).ToString("yyyy-MM-dd HH:mm:ss"),
Phone = Temp.Phone
}).ToList());
}
}
public bool ValidHouseSelection(int ShakingNumberID, int HouseID)
{
using (HouseSelectionDBEntities DB = new HouseSelectionDBEntities())
{
var _shaking = DB.ShakingNumberResult.FirstOrDefault(x => x.ID == ShakingNumberID);
var house = (from H in DB.House
where H.ID == HouseID &&
H.SubscriberID == null &&
DB.RoleProjectGroupAndHouseGroup.Any(x => x.HouseGroupID == H.GroupID && x.ProjectGroupID == _shaking.ProjectGroupID) &&
DB.RoleFamilyNumberAndRoomType.Any(x => x.RoomTypeID == H.RoomTypeID && x.FamilyNumber == _shaking.SubscriberProjectMapping.Subscriber.FamilyMemberNumber) &&
DB.RoleProjectGroupAndRoomType.Any(x => x.RoomTypeID == H.RoomTypeID && x.ProjectGroupID == _shaking.ProjectGroupID)
select H).FirstOrDefault();
return(house != null);
}
}
public List <House> GetValidHouses(int ShakingNumberID, int HouseEstateID, int Building)
{
using (HouseSelectionDBEntities DB = new HouseSelectionDBEntities())
{
var _shaking = DB.ShakingNumberResult.FirstOrDefault(x => x.ID == ShakingNumberID);
return((from H in DB.House
where H.HouseEstateID == HouseEstateID &&
H.Building == Building &&
H.SubscriberID == null &&
DB.RoleProjectGroupAndHouseGroup.Any(x => x.HouseGroupID == H.GroupID && x.ProjectGroupID == _shaking.ProjectGroupID) &&
DB.RoleFamilyNumberAndRoomType.Any(x => x.RoomTypeID == H.RoomTypeID && x.FamilyNumber == _shaking.SubscriberProjectMapping.Subscriber.FamilyMemberNumber) &&
DB.RoleProjectGroupAndRoomType.Any(x => x.RoomTypeID == H.RoomTypeID && x.ProjectGroupID == _shaking.ProjectGroupID)
select H).ToList());
}
}
public List <SubscriberSelectionEntity> GetSubscriberSelectionRecord(int SubscriberID)
{
using (var DB = new HouseSelectionDBEntities())
{
return((from S in DB.ShakingNumberResult
join m in DB.SubscriberProjectMapping on S.SubscriberProjectMappingID equals m.ID
join h in DB.HouseSelectionRecord on m.SubscriberID equals h.SubscriberID into temp from t in temp.DefaultIfEmpty()
where S.SubscriberProjectMapping.SubscriberID == SubscriberID
select new SubscriberSelectionEntity
{
ProjectID = S.ProjectGroup.Project.ID,
ProjectNumber = S.ProjectGroup.Project.Number,
ProjectName = S.ProjectGroup.Project.Name,
NoticeStatus = S.IsContacted ? 1 : (S.TelephoneNoticeRecord.OrderBy(x => x.ID).FirstOrDefault(x => x.ShakingNumberResultID == S.ID) == null ? 0 : S.TelephoneNoticeRecord.OrderBy(x => x.ID).FirstOrDefault(x => x.ShakingNumberResultID == S.ID).ResultType),
AuthStatus = S.IsAuthorized ? 1 : 0,
SelectionStatus = t == null ? 0 : 1,
ConfirmStatus = t == null ? 0 : (t.IsConfirm ? 1 : 0),
AbandonStatus = t == null ? 0 : (t.IsAbandon ? 1 : 0),
ShakingResultID = S.ID,
HouseSelectionID = t.ID
}).ToList());
}
}
