internal int addMidshipman(Midshipmen midshipmen)
{
string sql = "insert into MIDN (FIRSTNAME, LASTNAME, MI,SUFFIX,ALPHA, SSN , " +
" CLASS, COMPANY,EMAIL,CELL, DOB, SEX, SERVICE, STATUS ) " +
" values ( :f, :l, :m, :s, :a, :i, :c, :n, :e, :p , :b, :em, :svc, :st) ";
int data=0 ;
try
{
data = db.Database.ExecuteSqlCommand(sql,
new OracleParameter(":f", midshipmen.FirstName),
new OracleParameter(":l", midshipmen.LastName),
new OracleParameter(":m", midshipmen.Mi),
new OracleParameter(":s", midshipmen.Suffix),
new OracleParameter(":a", midshipmen.Alpha),
new OracleParameter(":i", midshipmen.Ssn),
new OracleParameter(":c", midshipmen.Class),
new OracleParameter(":n", midshipmen.Company),
new OracleParameter(":e", midshipmen.Email),
new OracleParameter(":p", midshipmen.Cell),
new OracleParameter(":b", midshipmen.Dob),
new OracleParameter(":em", midshipmen.Sex),
new OracleParameter(":svc", midshipmen.Service),
new OracleParameter(":st", midshipmen.Status)
);
}
catch (Exception aclE)
{
logger.Error("Exception caught in addMidshipman : ", aclE);
return data;
}
return data;
}
internal Midshipmen getMidnBySsn(string midnSsn)
{
string sql = "select ID, ALPHA,SSN,FIRSTNAME,LASTNAME,CLASS,COMPANY,EMAIL from MIDN where SSN = :s and active=1";
// string res = db.Database.ExecuteSqlCommand(sql, new OracleParameter(":s", midnSsn));
// logger.Debug("getMidnBySsn: " + midnSsn);
IEnumerable<Midshipmen> result = db.Database.SqlQuery<Midshipmen>(sql, new OracleParameter(":s", midnSsn));
Midshipmen midn = new Midshipmen();
foreach (Midshipmen r in result)
{
midn = r; ;
}
return midn;
}
//Update methods
internal int updateMidshipman(Midshipmen midshipmen)
{
String sql = "update MIDN set lastname=:ln , firstname=:fn , " +
" alpha=:a, sex=:sx, status=:s, class=:c ,assignment=:svc, " +
" mi=:mi, suffix=:sufx, email=:e, cell=:cell, dob=:dob, company=:company " +
" ,contactinfo=:ci " +
" where id = :id";
var data = db.Database.ExecuteSqlCommand(sql,
new OracleParameter(":ln", midshipmen.LastName),
new OracleParameter(":fn", midshipmen.FirstName),
// new OracleParameter(":ssn", midshipmen.Ssn),
new OracleParameter(":a", midshipmen.Alpha),
new OracleParameter(":sx", midshipmen.Sex),
new OracleParameter(":s", midshipmen.StatusId),
new OracleParameter(":c", midshipmen.Class),
new OracleParameter(":svc", midshipmen.Assignment),
new OracleParameter(":mi", midshipmen.Mi),
new OracleParameter(":sufx", midshipmen.Suffix),
new OracleParameter(":e", midshipmen.Email),
new OracleParameter(":cell", midshipmen.Cell),
new OracleParameter(":dob", midshipmen.Dob),
new OracleParameter(":company", midshipmen.Company),
new OracleParameter(":ci", midshipmen.ContactInfo),
new OracleParameter(":id", midshipmen.Id)
);
return data;
}
internal IEnumerable<Midshipmen> getActiveMidnListSearchBy(Midshipmen midn)
{
string constraint;
constraint = " where upper(lastname) like :l ||'%' and upper(firstname) like :f ||'%' " +
" and alpha like :a ||'%' " +
" and sex like :s ||'%' " +
" and class like :cl ||'%' " +
" and statusid like :st ||'%' "
+ " and ssn like :s9 ||'%' ";
if (midn.Assignment != null)
{
constraint += " and assignment like " + midn.Assignment + " ||'%' ";
}
if (midn.Company != null)
{
constraint += " and company like " + midn.Company + " ||'%' ";
}
string sql = "select * from midnview " + constraint;
string ln = midn.LastName != null ? midn.LastName.ToUpper() : "";
string fn = midn.FirstName != null ? midn.FirstName.ToUpper() : "";
var data = db.Database.SqlQuery<Midshipmen>(sql,
new OracleParameter(":l", ln),
new OracleParameter(":f", fn),
new OracleParameter(":a", midn.Alpha),
new OracleParameter(":s", midn.Sex),
new OracleParameter(":cl", midn.Class),
new OracleParameter(":st", midn.Status),
new OracleParameter(":s9", midn.Ssn)
);
return data;
}
