internal IEnumerable<MidnStatusType> getMidnStatusTypeList()
{
string sql = "select * from MIDN_STATUS order by status ";
using (MedMidsContext db = new MedMidsContext())
{
var data = db.Database.SqlQuery<MidnStatusType>(sql);
return data.ToList();
}
}
/// <summary>
///
/// </summary>
/// <param name="dateFieldName"></param>
/// <param name="columnName"></param>
/// <param name="status"></param>
/// <param name="midnId"></param>
/// <param name="dtUpdated"></param>
/// <param name="cid"></param>
/// <param name="uid"></param>
/// <returns></returns>
private int new_waiver_status(string dateFieldName, string statSetBy, string columnName, object status, long midnId, DateTime dtUpdated, long cid, string uid)
{
string sql = "";
sql = "insert into WAIVER_STATUS (waiver_status_id," + columnName + "," + dateFieldName + " , midn_id ,case_id,user_id, "+statSetBy+" ) values(waiver_seq.nextval,:c,:u,:m,:x,:y,2)";
using (MedMidsContext db = new MedMidsContext())
{
// string x = "";)(
int res = db.Database.ExecuteSqlCommand(sql,
new OracleParameter(":c", status),
new OracleParameter(":u", dtUpdated),
new OracleParameter(":m", midnId),
new OracleParameter(":x", cid),
new OracleParameter(":y", uid)
);
return res;
}
}
internal int disableMidnDocById(Int64 docId)
{
string sql = "update DOCUMENTS set disable='T' where " +
"document_id= :d ";
using (MedMidsContext db = new MedMidsContext())
{
int res = db.Database.ExecuteSqlCommand(sql, new OracleParameter(":d", docId));
return res;
}
}
/// <summary>
///
/// </summary>
/// <param name="u"></param>
/// <returns></returns>
internal int updateMmUser(Users u)
{
var notifyFlag = u.sendNotifications == true ? 1 : 0 ;
string sql = "update Users set firstname=:f, lastname=:l, email=:e, nmoid=:i1, notification=:x where nmoid=:i";
using (MedMidsContext db = new MedMidsContext())
{
int res = db.Database.ExecuteSqlCommand(sql,
new OracleParameter(":f", u.FIRSTNAME),
new OracleParameter(":l", u.LASTNAME),
new OracleParameter(":e", u.EMAIL),
new OracleParameter(":i1", u.NMOID),
new OracleParameter(":x", notifyFlag),
new OracleParameter(":i", u.NMOID)
);
return res;
}
}
/// <summary>
///
/// </summary>
/// <param name="midnId"></param>
/// <param name="caseId"></param>
/// <param name="rl"></param>
/// <param name="ul"></param>
/// <param name="swo"></param>
/// <param name="usmc"></param>
/// <param name="nuke"></param>
/// <param name="sub"></param>
/// <param name="specwar"></param>
/// <param name="reab"></param>
/// <param name="snfo"></param>
/// <param name="sna"></param>
/// <returns></returns>
internal int updateWaiverStatusById(string midnId, string caseId, string userId, string rl, string ul, string swo, string usmc, string nuke,
string sub, string specwar, string reab, string snfo, string sna, string dive,WaiverFinalDecision wfd)
{
Int64 id = Convert.ToInt64(midnId);
Int64 case_Id = Convert.ToInt64(caseId);
Int64 user_Id = Convert.ToInt64(userId);
string sql = "";
//WaiverStatusLatest currentStatus = this.getWaiverStatusByCaseId(case_Id);
WaiverStatusLatest currentStatus = getWaiverStatusByCidUid(case_Id, id);
Int64 setBy=1;
if (Roles.IsUserInRole("WaiverAdmin") || Roles.IsUserInRole("CA") ){
setBy=1;
}
else if (Roles.IsUserInRole("CCMO"))
{
setBy=3;
}
var data =0;
var dataUpdate=0;
if (!existsWaiverStatusById(midnId))
{
string setby_user = this.getUserNameById(user_Id);
//u r a...i m z
// sql = "update WAIVER_STATUS set dt_updated=sysdate,user_id=:u, rl=:r, ul=:a, swo=:b, usmc=:c, nuke=:d, " +
// " sub=:e, specwar=:f, reab=:g, snfo=:h, sna=:i ,dive=:w " +
// " where midn_id=:m and case_Id=:z";
// u r a..i m z
sql = "insert into WAIVER_STATUS (waiver_status_id, user_id, rl,ul,swo,usmc,nuke,sub,specwar,reab,snfo,sna, dive, midn_id, case_id ) " +
// ", rl_setby, ul_setby, swo_setby,usmc_setby, nuke_setby, sub_setby, specwar_setby, dive_setby, reab_setby, snfo_setby, sna_setby ) "+
" values ( waiver_seq.nextval, :u, :r, :a, :b, :c, :d, :e, :f, :g, :h, :i, :w, :m, :z )";
//,:rl_sb, :ul_sb, :swo_sb, :usmc_sb,:nuke_sb, :sub_sb, :specwar_sb, :dive_sb, :reab_sb, :snfo_sb, :sna_sb ) ";
using (MedMidsContext db = new MedMidsContext())
{
data = db.Database.ExecuteSqlCommand(sql,
new OracleParameter(":u", user_Id),
new OracleParameter(":r", rl),
new OracleParameter(":a", ul),
new OracleParameter(":b", swo),
new OracleParameter(":c", usmc),
new OracleParameter(":d", nuke),
new OracleParameter(":e", sub),
new OracleParameter(":f", specwar),
new OracleParameter(":g", reab),
new OracleParameter(":h", snfo),
new OracleParameter(":i", sna),
new OracleParameter(":w", dive),
new OracleParameter(":m", id),
new OracleParameter(":z", case_Id)
/* ,
new OracleParameter("rl_sb",setBy),
new OracleParameter("ul_sb",setBy),
new OracleParameter("swo_sb",setBy),
new OracleParameter("usmc_sb",setBy),
new OracleParameter("nuke_sb",setBy),
new OracleParameter("sub_sb",setBy),
new OracleParameter("specwar_sb",setBy),
new OracleParameter("dive_sb",setBy),
new OracleParameter("reab_sb",setBy),
new OracleParameter("snfo_sb",setBy),
new OracleParameter("sna_sb",setBy)
*/
);
string comma = "";
StringBuilder sb = new StringBuilder("update waiver_status set ");
if (sb.Length > 26) { comma = " , "; }
if (!string.IsNullOrEmpty(rl)) sb.Append(comma + " dt_rl=sysdate, rl_sb_userid='" + setby_user + "' , rl_setby=" + setBy);
if (sb.Length > 26) { comma = " , "; }
if (!string.IsNullOrEmpty(ul)) sb.Append(comma + " dt_ul=sysdate, ul_sb_userid='" + setby_user + "' , ul_setby=" + setBy);
if (sb.Length > 26) { comma = " , "; }
if (!string.IsNullOrEmpty(swo)) sb.Append(comma + " dt_swo=sysdate, swo_sb_userid='" + setby_user + "' , swo_setby=" + setBy);
if (sb.Length > 26) { comma = " , "; }
if (!string.IsNullOrEmpty(usmc)) sb.Append(comma + " dt_usmc=sysdate, usmc_sb_userid='" + setby_user + "' , usmc_setby=" + setBy);
if (sb.Length > 26) { comma = " , "; }
if (!string.IsNullOrEmpty(nuke)) sb.Append(comma + " dt_nuke=sysdate, nuke_sb_userid='" + setby_user + "' , nuke_setby=" + setBy);
if (sb.Length > 26) { comma = " , "; }
if (!string.IsNullOrEmpty(sub)) sb.Append(comma + " dt_sub=sysdate, sub_sb_userid='" + setby_user + "' , sub_setby=" + setBy);
if (sb.Length > 26) { comma = " , "; }
if (!string.IsNullOrEmpty(specwar)) sb.Append(comma + " dt_specwar=sysdate, specwar_sb_userid='" + setby_user + "' , specwar_setby=" + setBy);
if (sb.Length > 26) { comma = " , "; }
if (!string.IsNullOrEmpty(reab)) sb.Append(comma + " dt_reab=sysdate, reab_sb_userid='" + setby_user + "' , reab_setby=" + setBy);
if (sb.Length > 26) { comma = " , "; }
if (!string.IsNullOrEmpty(snfo)) sb.Append(comma + " dt_snfo=sysdate, snfo_sb_userid='" + setby_user + "' , snfo_setby=" + setBy);
if (sb.Length > 26) { comma = " , "; }
if (!string.IsNullOrEmpty(sna)) sb.Append(comma + " dt_sna=sysdate, sna_sb_userid='" + setby_user + "' , sna_setby=" + setBy);
if (sb.Length > 26) { comma = " , "; }
if (!string.IsNullOrEmpty(dive)) sb.Append(comma + " dt_dive=sysdate, dive_sb_userid='" + setby_user + "' , dive_setby=" + setBy);
sb.Append(" where midn_id=" + id + " and case_id=0 ");
var resinit = db.Database.ExecuteSqlCommand(sb.ToString());
}
}
else
{
string sql2 = buildWaiverSetby(wfd, setBy, currentStatus, user_Id);
using (MedMidsContext db = new MedMidsContext())
{
dataUpdate = db.Database.ExecuteSqlCommand(sql2,
new OracleParameter(":m", id),
new OracleParameter(":z", case_Id)
);
}
}
logger.Debug("Update/Insert Waiver_status " + data + ":" + dataUpdate);
return 1;
}
internal void updateDoctor(Doctors doctorItem)
{
String sql = "update DR set firstname=:fn,lastname=:ln,mi=:m ,email=:e,clinic=:c,speciality=:s,active=:a where id=:y";
using (MedMidsContext db = new MedMidsContext())
{
db.Database.ExecuteSqlCommand(sql, new OracleParameter(":fn", doctorItem.FirstName),
new OracleParameter(":lv", doctorItem.LastName),
new OracleParameter(":m", doctorItem.Mi),
new OracleParameter(":e", doctorItem.Email),
new OracleParameter(":c", doctorItem.Clinic),
new OracleParameter(":s", doctorItem.Speciality),
new OracleParameter(":a", doctorItem.Active),
new OracleParameter(":y", doctorItem.Id));
}
}
internal void updateLocations(Locations locationName)
{
String sql = "update LOCATION set location=:x,active=:a where id=:y";
using (MedMidsContext db = new MedMidsContext())
{
db.Database.ExecuteSqlCommand(sql, new OracleParameter(":x", locationName.location),
new OracleParameter(":a", locationName.Active),
new OracleParameter(":y", locationName.Id));
}
}
internal MidnStatusType getStatusTypeById(string id)
{
String sql = "select * from MIDN_STATUS where id=" + id;
using (MedMidsContext db = new MedMidsContext())
{
var data = db.Database.SqlQuery<MidnStatusType>(sql);
return (MidnStatusType)data.ToArray().GetValue(0);
}
}
/// <summary>
///
/// </summary>
/// <param name="defaultValue"></param>
/// <returns></returns>
internal List<SelectListItem> getStatusTypes(string defaultValue)
{
string sql = "select * from MIDN_STATUS order by status ";
using (MedMidsContext db = new MedMidsContext())
{
var data = db.Database.SqlQuery<SelectItem>(sql);
List<SelectListItem> items = new List<SelectListItem>();
foreach (SelectItem s in data)
{
items.Add(new SelectListItem { Text = s.Status, Value = s.Id.ToString(), Selected = (defaultValue == s.Id.ToString()) });
}
return items;
}
}
internal IEnumerable<IssueSummary> getRecentIssueChanges()
{
string sql = " With Pivot_Data As " +
" ( select midnClass," +
" Case When (( (trunc(Sysdate) - trunc(Datereview)) Between 0 And 4) and openissue=1 ) Then 1 End newOpened ," +
" Case When ( ((trunc(Sysdate) - trunc(Datereview)) Between 0 And 4) and closedissue=1 ) Then 1 End newClosed " +
" From vw_IssuesStatus4 )" +
" select midnclass, count(newOpened)newOpened, count(newClosed) newClosed " +
" From Pivot_Data Pivot" +
" group by midnclass order by midnClass";
using (MedMidsContext db = new MedMidsContext())
{
var data = db.Database.SqlQuery<IssueSummary>(sql);
return data.ToList();
}
}
internal Services getServiceById(string id)
{
String sql = "select * from SERVICE where id=" + id;
using (MedMidsContext db = new MedMidsContext())
{
var data = db.Database.SqlQuery<Services>(sql);
return (Services)data.ToArray().GetValue(0);
}
}
/// <summary>
/// Use pivot table query to return <30,gt30,gt60,gt90 counts of issues open for that many days.
/// </summary>
/// <returns></returns>
internal IEnumerable<IssueSummary> getOverdueIssuesLast4Years()
{
string sql = " With Pivot_Data As " +
" ( select midnClass," +
" Case When ( trunc(Sysdate) - trunc(Dateinserted) Between 0 And 29 ) Then 1 End lt30 ," +
" Case When ( trunc(Sysdate) - trunc(Dateinserted) Between 30 And 59 ) Then 1 End Gt30 ," +
" Case When ( trunc(Sysdate) - trunc(Dateinserted) Between 60 And 89 ) Then 1 End Gt60," +
" Case When ( trunc(Sysdate) - trunc(Dateinserted) > 90 ) Then 1 End Gt90" +
" From vw_Issuesopen )" +
" select midnclass, count(lt30)lt30, count(gt30) gt30, count(gt60) gt60, count(gt90) gt90 " +
" From Pivot_Data Pivot" +
" group by midnclass order by midnClass";
using (MedMidsContext db = new MedMidsContext())
{
var data = db.Database.SqlQuery<IssueSummary>(sql);
return data.ToList();
}
}
internal IEnumerable<ScheduledApptsSummary> getNextBusinessDaysSchedule()
{
XDateTime sDate = new XDateTime(DateTime.Today.ToString(), XDateTimeType.Calendar); //remove the AddDays
DateTime nextBusDate = sDate.NextBusinessDay();
//" from appts a, midn m , appt_status s, midn_status " +
string sql = "select m.class year, count(m.class) count " +
" from appts a, midn m , appt_status s " +
" where " +
" m.status=(select id from midn_status where status='Active') " +
" and m.id= a.midnid " +
" and s.id = a.status " +
// " and s.status != 'Complete' " +
" and s.status = 'Scheduled' " +
" and trunc(a.dateappt)=trunc(to_date('" + nextBusDate + "' ,'MM/DD/YYYY HH:MI:SS AM') )" +
" group by m.class order by m.class desc";
using (MedMidsContext db = new MedMidsContext())
{
var data = db.Database.SqlQuery<ScheduledApptsSummary>(sql);
return data.ToList();
}
}
//GetByID
internal Midshipmen getMidshipmanBySsn(string id)
{
String sql = "select m.id,lastname,firstname,alpha, ssn,sex,class,cell, " +
" email, dob ,suffix,company,mi,service,m.status statusId,s.status status, contactInfo,assignment " +
" from MIDN m, MIDN_STATUS s where m.status=s.id(+) and m.ssn=:y ";
using (MedMidsContext db = new MedMidsContext())
{
var data = db.Database.SqlQuery<Midshipmen>(sql, new OracleParameter(":y", id));
if (data.Count() > 0)
{
return (Midshipmen)data.ToArray().GetValue(0);
}
else
{
return null;
}
}
}
internal void updateClassStatus(ClassStatusViewModel csvm)
{
// String sql = "update MIDN set status=:x where class=(select year from class where id=:y)";
String sql = "update MIDN set status=:x where class=:y ";
using (MedMidsContext db = new MedMidsContext())
{
db.Database.ExecuteSqlCommand(sql, new OracleParameter(":x", csvm.selectedStatus),
new OracleParameter(":y", csvm.selectedClass));
}
}
/// <summary>
///
/// </summary>
/// <param name="p"></param>
/// <returns></returns>
internal WaiverStatusLatest getWaiverStatusByCaseId(long p)
{
logger.Debug(DateTime.Now);
// Int64 id = Convert.ToInt64(midnId);
string sql = "select * from WAIVER_STATUS where case_id =" + p;
using (MedMidsContext db = new MedMidsContext())
{
var data = db.Database.SqlQuery<WaiverStatusLatest>(sql);
if (data.Count() == 0)
{
return new WaiverStatusLatest();
}
else
{
logger.Debug(DateTime.Now);
return data.First();
}
}
}
internal void updateClinic(Clinics clinicName)
{
String sql = "update CLINIC set clinic=:x where id=:y";
using (MedMidsContext db = new MedMidsContext())
{
db.Database.ExecuteSqlCommand(sql, new OracleParameter(":x", clinicName.Clinic),
new OracleParameter(":y", clinicName.Id));
}
}
/// <summary>
///
/// </summary>
/// <param name="p"></param>
/// <returns></returns>
internal WaiverStatusLatest getWaiverStatusByCidUid(long c,long m)
{
// Int64 id = Convert.ToInt64(midnId);
string sql = "select * from WAIVER_STATUS where case_id =" + c +" and midn_id ="+m;
using (MedMidsContext db = new MedMidsContext())
{
var data = db.Database.SqlQuery<WaiverStatusLatest>(sql);
if (data.Count() == 0)
{
return new WaiverStatusLatest();
}
else
{
return data.First();
}
}
}
internal void updateIssueType(IssueTypes issueType)
{
String sql = "update ISSUE_TYPE set type=:x where id=:y";
using (MedMidsContext db = new MedMidsContext())
{
db.Database.ExecuteSqlCommand(sql, new OracleParameter(":x", issueType.Type),
new OracleParameter(":y", issueType.Id));
}
}
internal IEnumerable<WaiverStatusLatest> getWaiverStatusById(string midnId)
{
List<WaiverStatusLatest> def = new List<WaiverStatusLatest>();
Int64 id = Convert.ToInt64(midnId);
string sql = "select * from WAIVER_STATUS where midn_Id=" + id;
string sql2 = "select u.lastname userName, ws.* from waiver_status ws,users u where ws.user_id=u.NMOID and midn_Id=" + id;
using (MedMidsContext db = new MedMidsContext())
{
var data = db.Database.SqlQuery<WaiverStatusLatest>(sql2);
if (data.Count() == 0)
{
WaiverStatusLatest wsl = new WaiverStatusLatest();
def.Add(wsl);
return def;
}
else
{
return data.ToList();
}
}
}
//UPDATES
internal void updateMidnStatusType(MidnStatusType statusType)
{
String sql = "update MIDN_STATUS set status=:x where id=:y";
using (MedMidsContext db = new MedMidsContext())
{
db.Database.ExecuteSqlCommand(sql, new OracleParameter(":x", statusType.Status)
, new OracleParameter(":y", statusType.Id));
}
}
/// <summary>
/// Get list of all local waiver status
/// </summary>
/// <returns></returns>
internal List<WaiverStatusLatest> getWaiverStatusLocal( )
{
List<WaiverStatusLatest> def = new List<WaiverStatusLatest>();
List<WaiverStatusLatest> allWaiverStatus = new List<WaiverStatusLatest>();
string sql = "select * from WAIVER_STATUS ";
using (MedMidsContext db = new MedMidsContext())
{
var data = db.Database.SqlQuery<WaiverStatusLatest>(sql);
if (data.Count() == 0)
{
WaiverStatusLatest wsl = new WaiverStatusLatest();
def.Add(wsl);
return def;
}
allWaiverStatus = data.ToList();
}
// List<WaiverStatusLatest> allWaiverStatus = data.ToList();
String spname="pkg_mm_waivers.get_error_waivers";
ParameterDirection pout = ParameterDirection.Output;
using (var conn = new OracleConnection(dbConn) )
using (var command = new OracleCommand(spname,conn)
{
CommandType=CommandType.StoredProcedure
})
{
OracleParameter[] parameters = new OracleParameter[2];
parameters[0] = new OracleParameter();
parameters[0].ParameterName = "P_CUR";
parameters[0].OracleDbType = OracleDbType.RefCursor;
parameters[0].Direction = pout;
parameters[1] = new OracleParameter();
parameters[1].ParameterName = "P_ERRM";
parameters[1].OracleDbType = OracleDbType.Varchar2;
parameters[1].Direction = pout;
command.Parameters.AddRange(parameters);
conn.Open();
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var wsId = reader["WAIVER_STATUS_ID"];
foreach (WaiverStatusLatest wsl in allWaiverStatus)
{
if (wsl.Waiver_Status_Id == Convert.ToInt64(wsId))
{
wsl.waiverError = 1;
}
// def.Add(wsl);
}
}
logger.Debug(allWaiverStatus.Count());
return allWaiverStatus;
// if (def.Count > 0) {
// return allWaiverStatus;
// }else {
// return data.ToList();
// }
}
//return data.ToList();
}
internal void updateServiceName(Services serviceName)
{
String sql = "update SERVICE set service=:x where id=:y";
using (MedMidsContext db = new MedMidsContext())
{
db.Database.ExecuteSqlCommand(sql, new OracleParameter(":x", serviceName.Service),
new OracleParameter(":y", serviceName.Id));
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
internal string getWebVersion()
{
using (MedMidsContext db = new MedMidsContext())
{
string sql = "select version from meta_versions where created=(select max(created) from meta_versions) ";
// string sqlold = " select value from mm4vars where type='WEB_VERSION' ";
var data = db.Database.SqlQuery<string>(sql);
return data.First();
}
}
/// <summary>
///
/// </summary>
/// <param name="midnId"></param>
/// <returns></returns>
Boolean existsWaiverStatusById(string midnId)
{
List<WaiverStatusLatest> def = new List<WaiverStatusLatest>();
Int64 id = Convert.ToInt64(midnId);
string sql = "select * from WAIVER_STATUS where midn_Id=" + id;
using (MedMidsContext db = new MedMidsContext())
{
var data = db.Database.SqlQuery<WaiverStatusLatest>(sql);
if (data.Count() == 0)
{
return false;
}
else
{
return true;
}
}
}
/// <summary>
///
/// </summary>
/// <param name="_edipi"></param>
/// <returns></returns>
internal Boolean IsUserRegistered(string _edipi)
{
logger.Debug("IsUserRegistered for " + _edipi + " Result: " + _edipi);
string sql = "select * from users where nmoid = :u and active= 1";
using (MedMidsContext db = new MedMidsContext())
{
IEnumerable<Users> data = db.Database.SqlQuery<Users>(sql,
new OracleParameter(":u", _edipi));
if (data.Count() > 0)
{
// updateLoginLog(_edipi, null, "1"); // update with controller to allow more info.
return true;
}
else
{
// updateLoginLog(_edipi, null, "0");
return false;
}
}
}
/// <summary>
///
/// </summary>
/// <param name="dateFieldName"></param>
/// <param name="columnName"></param>
/// <param name="status"></param>
/// <param name="midnId"></param>
/// <param name="dtUpdated"></param>
/// <param name="hasMmStatus"></param>
/// <returns></returns>
private int update_waiver_status(string dateFieldName,string statSetBy, string columnName, object status, long midnId, DateTime dtUpdated,long cid, long origCid)
{
string sql = "";
sql = "UPDATE WAIVER_STATUS set " + columnName + "=:c ," + dateFieldName + "=:u ,"+statSetBy +"=2, case_id=:z where midn_id=:m and case_id=:x ";
using (MedMidsContext db = new MedMidsContext())
{
int res = db.Database.ExecuteSqlCommand(sql,
new OracleParameter(":c", status),
new OracleParameter(":u", dtUpdated),
new OracleParameter(":z", cid),
new OracleParameter(":m", midnId),
new OracleParameter(":x", origCid)
);
return res;
}
}
internal void updateAssignmentType(Assignments assignmentType)
{
String sql = "update ASSIGNMENT set type=:x where id=:y";
using (MedMidsContext db = new MedMidsContext())
{
db.Database.ExecuteSqlCommand(sql, new OracleParameter(":x", assignmentType.Type),
new OracleParameter(":y", assignmentType.Id));
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
internal IEnumerable<String> getActiv4Years()
{
string sql = " select distinct class_year from midnicmview order by class_year asc" ;
using (MedMidsContext db = new MedMidsContext())
{
var data = db.Database.SqlQuery<String>(sql);
return data.ToList();
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
internal Dictionary<string, List<string>> getMidnSsnByLast4Years()
{
Dictionary<string, List<string>> result = new Dictionary<string, List<string>>();
List<string> y1 = new List<string>();
List<string> y2 = new List<string>();
List<string> y3 = new List<string>();
List<string> y4 = new List<string>();
// string sql4 = "select distinct(class) from midn m where m.class > (select max(class) from midn m,midn_status ms where ms.id=m.status and ms.status='Active')-4 order by class";
string sqlActive4 = "select to_char(year) from class where active=1 order by year";
using (MedMidsContext db = new MedMidsContext())
{
var currentFouri = db.Database.SqlQuery<string>(sqlActive4);
List<String> currentFour = currentFouri.ToList();
// List<String> currentFour = currentFouri.ConvertAll<string>(x => x.ToString());
string sql = "select class,ssn from midn m where m.class > (select max(class) from midn m,midn_status ms where ms.id=m.status and ms.status='Active')-4";
var data = db.Database.SqlQuery<Midshipmen>(sql);
foreach (Midshipmen x in data)
{
if (x.Class.Equals(currentFour[0]))
{
y1.Add(x.ssnHyphen);
}
else if (x.Class.Equals(currentFour[1]))
{
y2.Add(x.ssnHyphen);
}
else if (x.Class.Equals(currentFour[2]))
{
y3.Add(x.ssnHyphen);
}
else if (x.Class.Equals(currentFour[3]))
{
y4.Add(x.ssnHyphen);
}
}
result.Add(currentFour[0], y1);
result.Add(currentFour[1], y2);
result.Add(currentFour[2], y3);
result.Add(currentFour[3], y4);
return result;
}
}