public List<Role> dropdownRoles()
{
List<Role> ls = new List<Role>();
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var rolescollection = from roles in ctx.Roles
select roles;
foreach (var item in rolescollection)
{
ls.Add(item);
}
}
return ls;
}
public List<ActivityType> dropdownActivity()
{
List<ActivityType> ls = new List<ActivityType>();
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var activotyCollection = from activityType in ctx.ActivityTypes
select activityType;
foreach (var activityType in activotyCollection)
{
ls.Add(activityType);
}
}
return ls;
}
public string CaseFirstAndLastName(int p0)
{
string returnstring = string.Empty;
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var ol = from c in ctx.Cases
where c.CaseID == p0
select new
{
c.FirstName,
c.LastName
};
foreach (var item in ol)
{
returnstring = item.FirstName + " " + item.LastName;
}
}
return returnstring;
}
public string DoesThisEmailExists(string theEmailKey)
{
string emailExistAnswer = "FALSE";
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
IQueryable<String> emailQueryable = from x in ctx.Users
where x.Email == theEmailKey
select x.Email;
foreach (var emailStringToBeCompared in emailQueryable)
{
if (emailStringToBeCompared == theEmailKey)
{
emailExistAnswer = "TRUE";
}
else
{
emailExistAnswer = "FALSE";
}
}
}
return emailExistAnswer;
}
public DateTime GetEnteredByDate(int activityId)
{
DateTime enteredByDate = new DateTime();
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var datecollection = from caseActivity in ctx.CaseActivities
where caseActivity.ActivityID == activityId
select new
{
caseActivity.CreatedDate
};
foreach (var item in datecollection)
{
enteredByDate = (DateTime) item.CreatedDate;
}
}
return enteredByDate;
}
public List<User> GetEditUserGeneral(short userId)
{
List<User> ls = new List<User>();
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var objectList = from user in ctx.Users
where user.UserID == userId
select user;
foreach (var item in objectList)
{
ls.Add(item);
}
}
return ls;
}
public List<Object> GetALlTasksForCase(int _caseNumber)
{
List<Object> objectList = new List<Object>();
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var taskobjects = from u1 in ctx.CaseActivities
join u2 in ctx.Tasks on u1.TaskID equals u2.TaskID
join u3 in ctx.TaskAssignees on u2.TaskID equals u3.TaskID
join u4 in ctx.Statuses on u2.StatusID equals u4.StatusID
where u1.CaseID == _caseNumber
select new
{
u2.DueDate,
u1.CaseID,
u2.EndDate,
u2.LastReminded,
u2.PriorityID,
u2.ModifiedBy,
u2.ModifiedDate,
u2.AllDayEvent,
u1.Comment,
u1.CreatedBy,
u1.CreatedDate,
u2.Subject,
u2.Reminder,
u4.StatusName
};
foreach (var item in taskobjects)
{
objectList.Add(item);
}
}
return objectList;
}
public string GetSetOfficerNameForCase(int setOfficerId)
{
string SetOfficerName = string.Empty;
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var objectlist = from u in ctx.Users
join userRoles in ctx.UserRoles on u.UserID equals userRoles.UserID
join role in ctx.Roles on userRoles.RoleID equals role.RoleID
where u.UserID == setOfficerId
select new
{
u.FirstName,
u.LastName
};
foreach (var item in objectlist)
{
SetOfficerName = item.FirstName + " " + item.LastName;
}
}
return SetOfficerName;
}
public List<object> DropdownUsers()
{
List<object> ls = new List<object>();
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var objectList = from users in ctx.Users
where users.IsActive == true
select new
{
users.FirstName,
users.LastName
};
foreach (var item in objectList)
{
ls.Add(item);
}
}
return ls;
}
public List<Object> GetAttorneEACPA()
{
List<object> CaseManagersList = new List<object>();
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var caseMangersObjects = from user in ctx.Users
join userRoles in ctx.UserRoles on user.UserID equals userRoles.UserID
join role in ctx.Roles on userRoles.RoleID equals role.RoleID
where role.RoleID == 6
select new { user.UserID, user.FirstName, user.LastName };
foreach (var item in caseMangersObjects)
{
CaseManagersList.Add(item);
}
}
return CaseManagersList;
}
public string GetFirstNameandLastName(short caseNumber)
{
string firstAndLastName = string.Empty;
using (var ctx = new GLBWeb_glbtsEntities())
{
var objectlist = from cases in ctx.Cases
where cases.CaseID == caseNumber
select new
{
cases.FirstName,
cases.LastName
};
foreach (var item in objectlist)
{
firstAndLastName = item.FirstName + " " + item.LastName;
}
}
return firstAndLastName;
}
public List<object> GetCaseWorkers()
{
List<object> CaseWorkersList = new List<object>();
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var CaseWorkersObjects = from user in ctx.Users
join userRoles in ctx.UserRoles on user.UserID equals userRoles.UserID
join role in ctx.Roles on userRoles.RoleID equals role.RoleID
orderby user.FirstName ascending
where role.RoleID == 4 && user.IsActive == true
select new { user.UserID, user.FirstName, user.LastName };
foreach (var item in CaseWorkersObjects)
{
CaseWorkersList.Add(item);
}
}
return CaseWorkersList;
}
public string GetCaseStatus(int casenumber)
{
string _casestring = string.Empty;
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var ol = from c in ctx.Cases
join s in ctx.Statuses on c.StatusID equals s.StatusID
where c.CaseID == casenumber
select new
{
s.StatusName
};
foreach (var item in ol)
{
_casestring = item.StatusName;
}
}
return _casestring;
}
public string GetCaseNotes(int caseNumber)
{
string caseNotes = string.Empty;
using (var ctx = new GLBWeb_glbtsEntities())
{
var caseNotesCollection = ctx.Cases.Where(cases => cases.CaseID == caseNumber).Select(cases => new
{
cases.Notes
});
foreach (var item in caseNotesCollection)
{
caseNotes = item.Notes;
}
}
return caseNotes;
}
public object GetCaseInvoiceItems(int casenumber)
{
var ls = new List<object>();
using (var ctx = new GLBWeb_glbtsEntities())
{
var ol = from ci in ctx.CaseInvoices
where ci.CaseID == casenumber
select new
{
ci.Description,
ci.Date,
ci.Quantity,
ci.UnitPrice,
ci.CaseInvoiceID,
ci.CreatedBy,
ci.CreatedDate,
ci.ModifiedBy,
ci.ModifiedDate
};
ls.AddRange(Enumerable.Cast<object>(ol));
}
return ls;
}
public List<Case> GetCaseInformationForPopulating(int _caseID)
{
List<Case> CaseList = new List<Case>();
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var objectlist = from u1 in ctx.Cases
// join caseAccount in ctx.CaseAccounts on u1.CaseID equals caseAccount.CaseID
//join caseAmortization in ctx.CaseAmortizations on caseAccount.CaseID equals caseAmortization.CaseID
//join caseBankAccount in ctx.CaseBankAccounts on caseAmortization.CaseID equals caseBankAccount.CaseID
//join caseCredit in ctx.CaseCredits on caseBankAccount.CaseID equals caseCredit.CaseID
//join caseDependent in ctx.CaseDependents on caseCredit.CaseID equals caseDependent.CaseID
//join caseInvoice in ctx.CaseInvoices on caseDependent.CaseID equals caseInvoice.CaseID
//join caseDocument in ctx.CaseDocuments on caseInvoice.CaseID equals caseDocument.CaseID
//join caseActivity in ctx.CaseActivities on caseDocument.CaseID equals caseActivity.CaseID
//join caseHistory in ctx.CaseHistories on caseActivity.CaseID equals caseHistory.CaseID
//join caseInvestment in ctx.CaseInvestments on caseHistory.CaseID equals caseInvestment.CaseID
//join caseLifeInsurance in ctx.CaseLifeInsurances on caseInvestment.CaseID equals caseLifeInsurance.CaseID
where u1.CaseID == _caseID
select new
{
u1
};
foreach (var item in objectlist)
{
CaseList.Add(item.u1);
}
}
return CaseList;
}
public List<CaseAccount> GetCaseAccounts(int _caseId)
{
List<CaseAccount> lsCaseAccounts = new List<CaseAccount>();
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var objectList = from caseAccount in ctx.CaseAccounts
where caseAccount.CaseID == _caseId
select new
{
caseAccount
};
foreach (var item in objectList)
{
lsCaseAccounts.Add(item.caseAccount);
}
}
return lsCaseAccounts;
}
public string GetEnteredByText(int activityId)
{
string EnteredByName = string.Empty;
int UserID = GetUserIDByActivityID(activityId);
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var UserCollectionItem = from user in ctx.Users
where user.UserID == UserID
select new
{
user.FirstName, user.LastName
};
foreach (var item in UserCollectionItem)
{
EnteredByName = item.FirstName + " " + item.LastName;
}
}
return EnteredByName;
}
public List<object> GetLeadSources()
{
List<Object> LeadSourcesList = new List<object>();
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var LeadSourcesObjects = from ls in ctx.Sources
select new { ls.SourceDesc, ls.SourceName };
foreach (var item in LeadSourcesObjects)
{
LeadSourcesList.Add(item);
}
}
return LeadSourcesList;
}
public List<String> GetProbabilities()
{
List<String> ProbLIst = new List<string>();
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var probOBjects = from u1 in ctx.Probabilities
select new { u1.ProbabilityID, u1.ProbabilityName };
}
return ProbLIst;
}
public List<object> GetInvoiceItems(int caseNumber)
{
List<object> ls = new List<object>();
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var objectlist = from caseinvoice in ctx.CaseInvoices
join invoicetype in ctx.InvoiceTypes on caseinvoice.InvoiceTypeID equals invoicetype.InvoiceTypeID
where caseinvoice.CaseID == caseNumber
select new
{
caseinvoice.Date,
invoicetype.Name,
caseinvoice.Description,
caseinvoice.Quantity,
caseinvoice.UnitPrice
};
foreach (var item in objectlist)
{
ls.Add(item);
}
}
return ls;
}
public List<Object> GetAllUsersForGridPanel()
{
List<Object> ls = new List<object>();
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var objectList = from user in ctx.Users
join userRole in ctx.UserRoles on user.UserID equals userRole.UserID
join role in ctx.Roles on userRole.RoleID equals role.RoleID
where user.IsActive == true
select new
{
user.UserID,
user.IsActive,
user.FirstName,
user.LastName,
user.Email,
user.HomePhone,
user.CellPhone,
role.RoleName
};
foreach (var item in objectList)
{
ls.Add(item);
}
}
return ls;
}
public List<object> GetPaymentForCase(int caseNumber)
{
List<object> ls = new List<object>();
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var objectList = from c in ctx.CasePayments
join p in ctx.PaymentTypes on c.PaymentTypeID equals p.PaymentTypeID
where c.CaseID == caseNumber
select new
{
c.PaidDate,
c.Amount,
c.TransactionStatus,
c.TransactionComment,
c.CreatedBy
};
foreach (var item in objectList)
{
ls.Add(item);
}
}
return ls;
}
// 25 Active Client null file_edit.ico null 3 1 #DCFFEC #000000 3 null
//58 Non-Collectible null notification_remove.ico null 5 2 null #666666 3 null
//61 Suspended null file_warning.ico null 4 2 #FF3300 null 3 null
//62 Settled/Completed
public List<Object> GetClientCases()
{
List<Object> clientCasesList = new List<Object>();
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var prospectobjects = from c in ctx.Cases
join d in ctx.Statuses on c.StatusID equals d.StatusID
join e in ctx.TaxTypes on c.TaxTypeID equals e.TaxTypeID
join folder in ctx.Folders on d.FolderID equals folder.FolderID
orderby c.CaseID ascending
select new
{
c.CaseID,
c.FirstName,
c.LastName,
c.HomePhone,
c.State,
e.TaxTypeName,
d.StatusName,
folder.FolderName
};
foreach (var item in prospectobjects)
{
if (item.FolderName.Contains("Active Client"))
{
clientCasesList.Add(item);
}
else if (item.FolderName.Contains("Non-Collectible"))
{
clientCasesList.Add(item);
}
else if (item.FolderName.Contains("Suspended"))
{
clientCasesList.Add(item);
}
else if (item.FolderName.Contains("Settled/Completed"))
{
clientCasesList.Add(item);
}
}
}
return clientCasesList;
}
public List<string> GetRoleForUser(string emailtext)
{
List<string> listRoleList = new List<string>();
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var list = from user in ctx.Users
join userRoles in ctx.UserRoles on user.UserID equals userRoles.UserID
join role in ctx.Roles on userRoles.RoleID equals role.RoleID
where user.IsActive == true && user.Email == emailtext
select new
{
role.RoleName
};
foreach (var role in list)
{
listRoleList.Add(role.ToString());
}
}
return listRoleList;
}
public string getClientType(int casenumber)
{
string clientType = string.Empty;
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var ob = from c in ctx.Cases
join statuse in ctx.Statuses on c.StatusID equals statuse.StatusID
join folder in ctx.Folders on statuse.FolderID equals folder.FolderID
where c.CaseID == casenumber
select new
{
folder.FolderName,
statuse.StatusName
};
foreach (var item in ob)
{
clientType = item.FolderName + " - " + item.StatusName;
}
}
return clientType;
}
public string GetServiceId(int serviceId)
{
string servicetxt = string.Empty;
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var serviceObject = from ser in ctx.Services
where ser.ServiceID == serviceId
select new
{
ser.ServiceName
};
foreach (var item in serviceObject)
{
servicetxt = item.ServiceName;
}
}
return servicetxt;
}
public string GetCreatedBy(string userEmail)
{
string CreatedBY = string.Empty;
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var firstandlast = ctx.Users.Where(n => n.Email == userEmail);
}
return CreatedBY;
}
public List<object> GetSettledCompleted()
{
List<object> ls = new List<object>();
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
var objectList = from s in ctx.Statuses
join b in ctx.Folders on s.FolderID equals b.FolderID
where b.FolderName == "Settled/Completed"
select new
{
s.StatusID,
s.StatusName,
s.StatusDesc,
s.OrderNo,
s.FolderID,
b.FolderName
};
}
return ls;
}
public List<string> GetDocsRequiredForCase()
{
List<string> ls = new List<string>();
using (GLBWeb_glbtsEntities ctx = new GLBWeb_glbtsEntities())
{
}
return ls;
}
