public static IQueryable<NationalityHelper> GetCountries()
{
StormDBEntities db = new StormDBEntities();
List<Country> countryList = db.Countries.ToList();
List<NationalityHelper> nationalHelper = new List<NationalityHelper>();
foreach (Country c in countryList)
{
NationalityHelper ch = new NationalityHelper
{
CountryCode = c.Country_Id,
Nationality = c.Nationality
};
nationalHelper.Add(ch);
}
return nationalHelper.AsQueryable();
}
public static void DeleteAppDocByStId(int stid)
{
StormDBEntities db = new StormDBEntities();
// delete application Documents and corresponding files
var app = (from a in db.Applications
from d in db.Application_Document
where a.Application_Id == d.Application_Id && a.Student_Id == stid
select d).ToList();
foreach (var application in app)
{
string completFileName = HttpContext.Current.Server.MapPath(application.Path + '/' + application.FileName);
System.IO.File.Delete(completFileName);
db.Application_Document.DeleteObject(application);
}
db.SaveChanges();
}
public static void deleteAllApplications(int applicationId)
{
StormDBEntities db = new StormDBEntities();
var applicationsToDelete = db.Applications.Where(x => x.Application_Id == applicationId);
var applicationsCancelToDelete = db.Application_Cancel.Where(x => x.Application_Id == applicationId);
var applicationsDocumentToDelete = db.Application_Document.Where(x => x.Application_Id == applicationId);
var applicationsResultToDelete = db.Application_Result.Where(x => x.Application_Id == applicationId);
if (applicationsToDelete.Count() > 0)
{
foreach (Application applications in applicationsToDelete)
{
db.Applications.DeleteObject(applications);
}
}
if (applicationsCancelToDelete.Count() > 0)
{
foreach (Application_Cancel applicationsCancel in applicationsCancelToDelete)
{
db.Application_Cancel.DeleteObject(applicationsCancel);
}
}
if (applicationsDocumentToDelete.Count() > 0)
{
foreach (Application_Document applicationsDocuments in applicationsDocumentToDelete)
{
db.Application_Document.DeleteObject(applicationsDocuments);
}
}
if (applicationsResultToDelete.Count() > 0)
{
foreach (Application_Result applicationsResult in applicationsResultToDelete)
{
db.Application_Result.DeleteObject(applicationsResult);
}
}
}
public static float GetCountCaseUploads(int caseId)
{
// to get progress of uploaded general documents
// we will divide the count of uploaded documents by
// the count of total no of required documents
StormDBEntities db = new StormDBEntities();
// select all templates assigned to that student
int templates = db.CaseDocuments.Where( doc => doc.Case_Id == caseId).Count();
int uploads = (from d in db.CaseDocuments
where d.UploadedOn != null
&& d.Case_Id == caseId
select d).Count();
if (templates != 0)
{
return (uploads * 100) / templates ;
}
return 0;
}
public static void DeleteCaseDocByAppId(int appId)
{
StormDBEntities db = new StormDBEntities();
// delete application Documents and corresponding files
var app = (from a in db.Applications
from c in db.CaseDocuments
where a.Case_Id == c.Case_Id && a.Application_Id == appId
select c).ToList();
foreach (var application in app)
{
string completFileName = HttpContext.Current.Server.MapPath(application.Path + '/' + application.FileName);
System.IO.File.Delete(completFileName);
application.Path = null;
application.UploadedOn = null;
application.FileName = null;
application.Comment = null;
}
db.SaveChanges();
}
public static float GetCountAppUploads(int courseId, int studentId)
{
// to get progress of uploaded documents for applications
// we will divide the count of uploaded documents by
// the count of total no of required documents for each application
StormDBEntities db = new StormDBEntities();
int templates = db.Template_Document.Where(t => t.Course_Id == courseId).Count();
int uploads = (from a in db.Applications
from d in db.Application_Document
where d.UploadedOn != null
&& a.Student_Id == studentId
&& a.Course_Id == courseId
&& a.Application_Id == d.Application_Id
select d).Count();
if (templates != 0)
{
return (uploads * 100 / templates);
}
return 0;
}
public static bool isAllApplicationDocumentsUploaded(int applicationID)
{
StormDBEntities db = new StormDBEntities();
string appStatus = db.Applications.Single(a => a.Application_Id == applicationID).Status;
//ApplicationController.statusUp(applicationID);
int progressValue = (int)Enum.Parse(typeof(StormWeb.Controllers.ApplicationController.ApplicationStatusType), appStatus);
if (progressValue >= ApplicationController.getProgressValue(ApplicationController.ApplicationStatusType.Documents_Completed.ToString()))
return true;
return false;
}
public static bool isAllCaseDocumentsUploaded(int caseID)
{
StormDBEntities db = new StormDBEntities();
int count = db.CaseDocuments.Where(x => x.Case_Id == caseID && x.FileName != null).Count();
if (count <= 0)
return false;
return true;
}
public static int GetFileId(int applicationId)
{
StormDBEntities db = new StormDBEntities();
int fileName = 0;
var receipt = (from pay in db.Payments
from rec in db.Receipt_File
where pay.Application_Id == applicationId && rec.PaymentId == pay.Id
select rec);
fileName = receipt.FirstOrDefault().File_Id;
return fileName;
}
public static string GetFileName(int applicationId)
{
StormDBEntities db = new StormDBEntities();
string fileName = "";
var receipt = (from pay in db.Payments
from rec in db.Receipt_File
where pay.Application_Id == applicationId && rec.PaymentId == pay.Id
select rec);
fileName = receipt.FirstOrDefault().Path + '/' + receipt.FirstOrDefault().FileName;
return fileName;
}
public static string GetAcceptanceFileName(int applicationId)
{
StormDBEntities db = new StormDBEntities();
string fileName = "";
var result = (from app in db.Application_Result
where app.Application_Id == applicationId && app.Type == ACCEPTANCE_TYPE
select app);
if(result!=null)
fileName = result.FirstOrDefault().Path + '/' + result.FirstOrDefault().FileName;
return fileName;
}
public static Application_Document getAppByTemplate(int id)
{
StormDBEntities db = new StormDBEntities();
foreach (Application_Document ad in db.Application_Document)
{
if (ad.TemplateDoc_Id == id)
{
return ad;
}
}
return null;
}
public static void statusDown(int applicationID)
{
StormDBEntities db = new StormDBEntities();
Application app = db.Applications.SingleOrDefault(x => x.Application_Id == applicationID);
if (app == null)
return;
bool found = false;
string[] names = Enum.GetNames(typeof(ApplicationStatusType));
ApplicationStatusType[] values = (ApplicationStatusType[])Enum.GetValues(typeof(ApplicationStatusType));
for (int i = names.Length-1; i >= 0; i--)
{
if (found)
{
app.Status = names[i];
break;
}
if (getProgressValue(app.Status) == (int)values[i])
{
found = true;
}
}
db.SaveChanges();
}
public static void setStatus(int applicat, int status)
{
StormDBEntities db = new StormDBEntities();
Application application = db.Applications.Single(x => x.Application_Id == applicat);
string newStatus = Enum.GetName(typeof(ApplicationStatusType), status);
application.Status = newStatus;
db.SaveChanges();
return;
}
public static bool requestedApplicationCancel(int applicationId)
{
StormDBEntities db = new StormDBEntities();
bool status = false;
var applicationCancel = db.Application_Cancel.SingleOrDefault(x => x.Application_Id == applicationId);
if (applicationCancel != null)
status = applicationCancel.Status;
return status;
}
public static bool isOfferLetterRead(int applicationID)
{
StormDBEntities db = new StormDBEntities();
Application_Result appDoc = db.Application_Result.SingleOrDefault(x => x.Application_Id == applicationID && x.Type == "O");
if (appDoc == null)
return false;
return true;
}
public static void statusUp(int applicationID)
{
StormDBEntities db = new StormDBEntities();
Application app = db.Applications.SingleOrDefault(x => x.Application_Id == applicationID);
if (app == null)
return;
bool found = false;
foreach(var statusType in Enum.GetValues(typeof(ApplicationStatusType)))
{
if (found)
{
app.Status = statusType.ToString();
break;
}
if (getProgressValue(app.Status) == (int)statusType)
{
found = true;
}
}
db.SaveChanges();
}
public static CaseDocument hasCaseDoc(int caseId)
{
StormDBEntities db = new StormDBEntities();
foreach (CaseDocument cd in db.CaseDocuments)
{
if (cd.Case_Id == caseId)
{
return cd;
}
}
return null;
}
// get number of approved documents to change application status to "Documents_Completed"
public static int getUnApprovedDocs(int applicationId)
{
StormDBEntities db = new StormDBEntities();
int countdocs = db.Application_Document.Where(d => d.Approved == false && d.Application_Id == applicationId).Count();
return countdocs;
}
public static bool fileExists(int applicationId)
{
StormDBEntities db = new StormDBEntities();
var receipt = (from pay in db.Payments
from rec in db.Receipt_File
where pay.Application_Id == applicationId && rec.PaymentId == pay.Id
select rec);
if (receipt.Count() > 0)
return true;
else
return false;
}
public static string getStaffName(int applicationId)
{
StormDBEntities db = new StormDBEntities();
int caseId = db.Applications.SingleOrDefault(x => x.Application_Id == applicationId).Case_Id;
var staffId = db.Case_Staff.SingleOrDefault(x => x.Case_Id == caseId).Staff_Id;
var staff = db.Staffs.SingleOrDefault(x => x.Staff_Id == staffId);
string staffName = staff.FirstName + " " + staff.LastName;
return staffName;
}
