public List <BirthHistory> GetBirthHistories()
{
List <BirthHistory> ReturnObject = new List <BirthHistory>();
BirthHistory ins;
DataBaseConnection dbConn = new DataBaseConnection();
var table = GetCompaniesDBVariable();
if (table != null)
{
using (var con = dbConn.SqlConn())
{
con.Open();
using (SqlCommand cmd = new SqlCommand("exec " + CommonStrings.GetBirthHistories + " @Companies", con))
{
var pList = new SqlParameter("@Companies", SqlDbType.Structured);
pList.TypeName = "dbo.IntList";
pList.Value = table;
cmd.Parameters.Add(pList);
using (var drI = cmd.ExecuteReader())
{
while (drI.Read())
{
ins = new BirthHistory();
ins.TubeId = Convert.ToInt32(drI["TubeId"]);
ins.MaleParentId = Convert.ToInt32(drI["MaleParentId"]);
ins.FemaleParentId = Convert.ToInt32(drI["FemaleParentId"]);
ins.ChildId = Convert.ToInt32(drI["ChildId"]);
ins.CompanyId = Convert.ToInt32(drI["CompanyId"]);
ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]);
ins.ModifiedBy = Convert.ToInt32(drI["ModifiedBy"]);
ins._User = drI["Username"].ToString();
ins._Company = drI["Company"].ToString();
ins.Success = Convert.ToBoolean(drI["Success"]);
ins._MaleParent = drI["_MaleParent"].ToString();
ins._FemaleParent = drI["_FemaleParent"].ToString();
ins._Child = drI["_Child"].ToString();
ins.BirthTypeId = Convert.ToInt32(drI["BirthTypeId"]);
ins.HistoryId = Convert.ToInt32(drI["HistoryId"]);
ins._BirthType = drI["BirthType"].ToString();
ReturnObject.Add(ins);
}
}
}
}
}
return(ReturnObject);
}
public JsonResult AddBirthScenario(int FemaleId, int MaleId, int ChildId, int TubeId, int BirthTypeId, int Success, int CompanyId, int UserKey, DateTime ModifiedDate)
{
//...Check if Tube was used
if(TubeId != 0)
{
//...Tube was used, save history record
InseminationHistory insem = new InseminationHistory();
insem.AnimalId = FemaleId;
insem.TubeId = TubeId;
insem.CompanyId = CompanyId;
insem.ModifiedBy = UserKey;
insem.ModifiedDate = ModifiedDate.AddDays(-274);
insem = aiRep.InsertInseminationHistorySim(insem);
if(insem.HistoryId == 0)
{
return Json(insem.HistoryId, JsonRequestBehavior.AllowGet);
}
}
//...Insert Birth History
BirthHistory birth = new BirthHistory();
birth.BirthTypeId = BirthTypeId;
birth.ChildId = ChildId;
birth.CompanyId = CompanyId;
birth.FemaleParentId = FemaleId;
birth.MaleParentId = MaleId;
birth.ModifiedBy = UserKey;
birth.Success = (Success == 1) ? true : false;
birth.TubeId = TubeId;
birth.ModifiedDate = ModifiedDate;
birth = aiRep.InsertBirthHistorySim(birth);
if ((birth.HistoryId != 0) && (birth.ChildId != 0))
{
AnimalRelationship father = new AnimalRelationship();
father.ParentAnimalId = birth.MaleParentId;
father.ChildAnimalId = birth.ChildId;
father.ModiefiedDate = ModifiedDate;
father.ModifiedBy = UserKey;
father = aniRep.InsertAnimalRelationshipSim(father);
AnimalRelationship mother = new AnimalRelationship();
mother.ParentAnimalId = birth.FemaleParentId;
mother.ChildAnimalId = birth.ChildId;
mother.ModiefiedDate = ModifiedDate;
mother.ModifiedBy = UserKey;
mother = aniRep.InsertAnimalRelationshipSim(mother);
}
return Json(birth.HistoryId, JsonRequestBehavior.AllowGet);
}
public BirthHistory UpdateBirthHistory(BirthHistory ins)
{
int ModifiedBy = (int)HttpContext.Current.Session["UserNo"];
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
con.Open();
SqlCommand cmdI = con.CreateCommand();
cmdI.Connection = con;
cmdI.Parameters.Clear();
cmdI.CommandText = CommonStrings.UpdateBirthHistory;
cmdI.CommandType = System.Data.CommandType.StoredProcedure;
cmdI.Parameters.AddWithValue("@HistoryId", ins.HistoryId);
cmdI.Parameters.AddWithValue("@BirthTypeId", ins.BirthTypeId);
cmdI.Parameters.AddWithValue("@FemaleParentId", ins.FemaleParentId);
cmdI.Parameters.AddWithValue("@MaleParentId", ins.MaleParentId);
if (ins.ChildId == 0)
{
cmdI.Parameters.AddWithValue("@ChildId", DBNull.Value);
}
else
{
cmdI.Parameters.AddWithValue("@ChildId", ins.ChildId);
}
if (ins.TubeId == 0)
{
cmdI.Parameters.AddWithValue("@TubeId", DBNull.Value);
}
else
{
cmdI.Parameters.AddWithValue("@TubeId", ins.TubeId);
}
cmdI.Parameters.AddWithValue("@Success", ins.Success);
cmdI.Parameters.AddWithValue("@ModifiedDate", DateTime.Now);
cmdI.Parameters.AddWithValue("@ModifiedBy", ModifiedBy);
cmdI.Parameters.AddWithValue("@CompanyId", ins.CompanyId);
cmdI.Parameters.AddWithValue("@Removed", false);
cmdI.ExecuteNonQuery();
cmdI.Connection.Close();
con.Dispose();
return(ins);
}
public BirthHistory GetBirthHistory(int HistoryId)
{
BirthHistory ins = new BirthHistory();
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI = con.CreateCommand();
cmdI.Connection = con;
cmdI.Parameters.Clear();
cmdI.CommandText = CommonStrings.GetBirthHistory;
cmdI.CommandType = System.Data.CommandType.StoredProcedure;
cmdI.Parameters.AddWithValue("@HistoryId", HistoryId);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
if (drI.HasRows)
{
while (drI.Read())
{
ins.TubeId = Convert.ToInt32(drI["TubeId"]);
ins.MaleParentId = Convert.ToInt32(drI["MaleParentId"]);
ins.FemaleParentId = Convert.ToInt32(drI["FemaleParentId"]);
ins.ChildId = Convert.ToInt32(drI["ChildId"]);
ins.CompanyId = Convert.ToInt32(drI["CompanyId"]);
ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]);
ins.ModifiedBy = Convert.ToInt32(drI["ModifiedBy"]);
ins._User = drI["Username"].ToString();
ins._Company = drI["Company"].ToString();
ins.Success = Convert.ToBoolean(drI["Success"]);
ins._MaleParent = drI["_MaleParent"].ToString();
ins._FemaleParent = drI["_FemaleParent"].ToString();
ins._Child = drI["_Child"].ToString();
ins.BirthTypeId = Convert.ToInt32(drI["BirthTypeId"]);
ins.HistoryId = Convert.ToInt32(drI["HistoryId"]);
ins._BirthType = drI["BirthType"].ToString();
}
}
cmdI.Connection.Close();
con.Dispose();
return(ins);
}
public BirthHistory GetBirthHistory(int HistoryId)
{
BirthHistory ins = new BirthHistory();
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
SqlCommand cmdI = con.CreateCommand();
cmdI.Connection = con;
cmdI.Parameters.Clear();
cmdI.CommandText = CommonStrings.GetBirthHistory;
cmdI.CommandType = System.Data.CommandType.StoredProcedure;
cmdI.Parameters.AddWithValue("@HistoryId", HistoryId);
cmdI.Connection.Open();
SqlDataReader drI = cmdI.ExecuteReader();
if (drI.HasRows)
{
while (drI.Read())
{
ins.TubeId = Convert.ToInt32(drI["TubeId"]);
ins.MaleParentId = Convert.ToInt32(drI["MaleParentId"]);
ins.FemaleParentId = Convert.ToInt32(drI["FemaleParentId"]);
ins.ChildId = Convert.ToInt32(drI["ChildId"]);
ins.CompanyId = Convert.ToInt32(drI["CompanyId"]);
ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]);
ins.ModifiedBy = Convert.ToInt32(drI["ModifiedBy"]);
ins._User = drI["Username"].ToString();
ins._Company = drI["Company"].ToString();
ins.Success = Convert.ToBoolean(drI["Success"]);
ins._MaleParent = drI["_MaleParent"].ToString();
ins._FemaleParent = drI["_FemaleParent"].ToString();
ins._Child = drI["_Child"].ToString();
ins.BirthTypeId = Convert.ToInt32(drI["BirthTypeId"]);
ins.HistoryId = Convert.ToInt32(drI["HistoryId"]);
ins._BirthType = drI["BirthType"].ToString();
}
}
cmdI.Connection.Close();
con.Dispose();
return ins;
}
public List <BirthHistory> GetBirthHistories(int AnimalId)
{
List <BirthHistory> ReturnObject = new List <BirthHistory>();
BirthHistory ins;
DataBaseConnection dbConn = new DataBaseConnection();
using (var con = dbConn.SqlConn())
{
con.Open();
using (SqlCommand cmd = new SqlCommand("exec " + CommonStrings.GetBirthHistoryForAnimal + " @AnimalId", con))
{
cmd.Parameters.AddWithValue("@AnimalId", AnimalId);
using (var drI = cmd.ExecuteReader())
{
while (drI.Read())
{
ins = new BirthHistory();
ins.TubeId = Convert.ToInt32(drI["TubeId"]);
ins.MaleParentId = Convert.ToInt32(drI["MaleParentId"]);
ins.FemaleParentId = Convert.ToInt32(drI["FemaleParentId"]);
ins.ChildId = Convert.ToInt32(drI["ChildId"]);
ins.CompanyId = Convert.ToInt32(drI["CompanyId"]);
ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]);
ins.ModifiedBy = Convert.ToInt32(drI["ModifiedBy"]);
ins._User = drI["Username"].ToString();
ins._Company = drI["Company"].ToString();
ins.Success = Convert.ToBoolean(drI["Success"]);
ins._MaleParent = drI["_MaleParent"].ToString();
ins._FemaleParent = drI["_FemaleParent"].ToString();
ins._Child = drI["_Child"].ToString();
ins.BirthTypeId = Convert.ToInt32(drI["BirthTypeId"]);
ins.HistoryId = Convert.ToInt32(drI["HistoryId"]);
ins._BirthType = drI["BirthType"].ToString();
ReturnObject.Add(ins);
}
}
}
}
return(ReturnObject);
}
public JsonResult _InsertBirthHistory(BirthHistory ins)
{
ins = aiRep.InsertBirthHistory(ins);
if((ins.HistoryId != 0) && (ins.ChildId != 0))
{
AnimalRelationship father = new AnimalRelationship();
father.ParentAnimalId = ins.MaleParentId;
father.ChildAnimalId = ins.ChildId;
father = animalRep.InsertAnimalRelationship(father);
AnimalRelationship mother = new AnimalRelationship();
mother.ParentAnimalId = ins.FemaleParentId;
mother.ChildAnimalId = ins.ChildId;
mother = animalRep.InsertAnimalRelationship(mother);
}
return Json(new GridModel(commonRep.GetBirthHistories()));
}
public BirthHistory InsertBirthHistorySim(BirthHistory ins)
{
BirthHistory ReturnObject = new BirthHistory();
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
con.Open();
SqlCommand cmdI = con.CreateCommand();
SqlTransaction trx = con.BeginTransaction(CommonStrings.InsertTransaction);
cmdI.Connection = con;
cmdI.Transaction = trx;
try
{
cmdI.Parameters.Clear();
cmdI.CommandText = CommonStrings.InsertBirthHistory;
cmdI.CommandType = System.Data.CommandType.StoredProcedure;
cmdI.Parameters.AddWithValue("@BirthTypeId", ins.BirthTypeId);
cmdI.Parameters.AddWithValue("@FemaleParentId", ins.FemaleParentId);
cmdI.Parameters.AddWithValue("@MaleParentId", ins.MaleParentId);
if (ins.ChildId == 0)
{
cmdI.Parameters.AddWithValue("@ChildId", DBNull.Value);
}
else
{
cmdI.Parameters.AddWithValue("@ChildId", ins.ChildId);
}
if (ins.TubeId == 0)
{
cmdI.Parameters.AddWithValue("@TubeId", DBNull.Value);
}
else
{
cmdI.Parameters.AddWithValue("@TubeId", ins.TubeId);
}
cmdI.Parameters.AddWithValue("@Success", ins.Success);
cmdI.Parameters.AddWithValue("@ModifiedDate", ins.ModifiedDate);
cmdI.Parameters.AddWithValue("@ModifiedBy", ins.ModifiedBy);
cmdI.Parameters.AddWithValue("@CompanyId", ins.CompanyId);
cmdI.Parameters.AddWithValue("@Removed", false);
ins.HistoryId = (int)cmdI.ExecuteScalar();
trx.Commit();
cmdI.Connection.Close();
}
catch (SqlException ex)
{
if (trx != null)
{
trx.Rollback();
}
}
finally
{
if (con.State != ConnectionState.Closed)
{
con.Close();
}
con.Dispose();
cmdI.Dispose();
trx.Dispose();
}
return(ins);
}
public BirthHistory UpdateBirthHistory(BirthHistory ins)
{
int ModifiedBy = (int)HttpContext.Current.Session["UserNo"];
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
con.Open();
SqlCommand cmdI = con.CreateCommand();
cmdI.Connection = con;
cmdI.Parameters.Clear();
cmdI.CommandText = CommonStrings.UpdateBirthHistory;
cmdI.CommandType = System.Data.CommandType.StoredProcedure;
cmdI.Parameters.AddWithValue("@HistoryId", ins.HistoryId);
cmdI.Parameters.AddWithValue("@BirthTypeId", ins.BirthTypeId);
cmdI.Parameters.AddWithValue("@FemaleParentId", ins.FemaleParentId);
cmdI.Parameters.AddWithValue("@MaleParentId", ins.MaleParentId);
if (ins.ChildId == 0)
{
cmdI.Parameters.AddWithValue("@ChildId", DBNull.Value);
}
else
{
cmdI.Parameters.AddWithValue("@ChildId", ins.ChildId);
}
if (ins.TubeId == 0)
{
cmdI.Parameters.AddWithValue("@TubeId", DBNull.Value);
}
else
{
cmdI.Parameters.AddWithValue("@TubeId", ins.TubeId);
}
cmdI.Parameters.AddWithValue("@Success", ins.Success);
cmdI.Parameters.AddWithValue("@ModifiedDate", DateTime.Now);
cmdI.Parameters.AddWithValue("@ModifiedBy", ModifiedBy);
cmdI.Parameters.AddWithValue("@CompanyId", ins.CompanyId);
cmdI.Parameters.AddWithValue("@Removed", false);
cmdI.ExecuteNonQuery();
cmdI.Connection.Close();
con.Dispose();
return ins;
}
public BirthHistory InsertBirthHistorySim(BirthHistory ins)
{
BirthHistory ReturnObject = new BirthHistory();
DataBaseConnection dbConn = new DataBaseConnection();
SqlConnection con = dbConn.SqlConn();
con.Open();
SqlCommand cmdI = con.CreateCommand();
SqlTransaction trx = con.BeginTransaction(CommonStrings.InsertTransaction);
cmdI.Connection = con;
cmdI.Transaction = trx;
try
{
cmdI.Parameters.Clear();
cmdI.CommandText = CommonStrings.InsertBirthHistory;
cmdI.CommandType = System.Data.CommandType.StoredProcedure;
cmdI.Parameters.AddWithValue("@BirthTypeId", ins.BirthTypeId);
cmdI.Parameters.AddWithValue("@FemaleParentId", ins.FemaleParentId);
cmdI.Parameters.AddWithValue("@MaleParentId", ins.MaleParentId);
if (ins.ChildId == 0)
{
cmdI.Parameters.AddWithValue("@ChildId", DBNull.Value);
}
else
{
cmdI.Parameters.AddWithValue("@ChildId", ins.ChildId);
}
if (ins.TubeId == 0)
{
cmdI.Parameters.AddWithValue("@TubeId", DBNull.Value);
}
else
{
cmdI.Parameters.AddWithValue("@TubeId", ins.TubeId);
}
cmdI.Parameters.AddWithValue("@Success", ins.Success);
cmdI.Parameters.AddWithValue("@ModifiedDate", ins.ModifiedDate);
cmdI.Parameters.AddWithValue("@ModifiedBy", ins.ModifiedBy);
cmdI.Parameters.AddWithValue("@CompanyId", ins.CompanyId);
cmdI.Parameters.AddWithValue("@Removed", false);
ins.HistoryId = (int)cmdI.ExecuteScalar();
trx.Commit();
cmdI.Connection.Close();
}
catch (SqlException ex)
{
if (trx != null) trx.Rollback();
}
finally
{
if (con.State != ConnectionState.Closed)
{
con.Close();
}
con.Dispose();
cmdI.Dispose();
trx.Dispose();
}
return ins;
}
public List<BirthHistory> GetBirthHistories(int AnimalId)
{
List<BirthHistory> ReturnObject = new List<BirthHistory>();
BirthHistory ins;
DataBaseConnection dbConn = new DataBaseConnection();
using (var con = dbConn.SqlConn())
{
con.Open();
using (SqlCommand cmd = new SqlCommand("exec " + CommonStrings.GetBirthHistoryForAnimal + " @AnimalId", con))
{
cmd.Parameters.AddWithValue("@AnimalId", AnimalId);
using (var drI = cmd.ExecuteReader())
{
while (drI.Read())
{
ins = new BirthHistory();
ins.TubeId = Convert.ToInt32(drI["TubeId"]);
ins.MaleParentId = Convert.ToInt32(drI["MaleParentId"]);
ins.FemaleParentId = Convert.ToInt32(drI["FemaleParentId"]);
ins.ChildId = Convert.ToInt32(drI["ChildId"]);
ins.CompanyId = Convert.ToInt32(drI["CompanyId"]);
ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]);
ins.ModifiedBy = Convert.ToInt32(drI["ModifiedBy"]);
ins._User = drI["Username"].ToString();
ins._Company = drI["Company"].ToString();
ins.Success = Convert.ToBoolean(drI["Success"]);
ins._MaleParent = drI["_MaleParent"].ToString();
ins._FemaleParent = drI["_FemaleParent"].ToString();
ins._Child = drI["_Child"].ToString();
ins.BirthTypeId = Convert.ToInt32(drI["BirthTypeId"]);
ins.HistoryId = Convert.ToInt32(drI["HistoryId"]);
ins._BirthType = drI["BirthType"].ToString();
ReturnObject.Add(ins);
}
}
}
}
return ReturnObject;
}
public List<BirthHistory> GetBirthHistories()
{
List<BirthHistory> ReturnObject = new List<BirthHistory>();
BirthHistory ins;
DataBaseConnection dbConn = new DataBaseConnection();
var table = GetCompaniesDBVariable();
if (table != null)
{
using (var con = dbConn.SqlConn())
{
con.Open();
using (SqlCommand cmd = new SqlCommand("exec " + CommonStrings.GetBirthHistories + " @Companies", con))
{
var pList = new SqlParameter("@Companies", SqlDbType.Structured);
pList.TypeName = "dbo.IntList";
pList.Value = table;
cmd.Parameters.Add(pList);
using (var drI = cmd.ExecuteReader())
{
while (drI.Read())
{
ins = new BirthHistory();
ins.TubeId = Convert.ToInt32(drI["TubeId"]);
ins.MaleParentId = Convert.ToInt32(drI["MaleParentId"]);
ins.FemaleParentId = Convert.ToInt32(drI["FemaleParentId"]);
ins.ChildId = Convert.ToInt32(drI["ChildId"]);
ins.CompanyId = Convert.ToInt32(drI["CompanyId"]);
ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]);
ins.ModifiedBy = Convert.ToInt32(drI["ModifiedBy"]);
ins._User = drI["Username"].ToString();
ins._Company = drI["Company"].ToString();
ins.Success = Convert.ToBoolean(drI["Success"]);
ins._MaleParent = drI["_MaleParent"].ToString();
ins._FemaleParent = drI["_FemaleParent"].ToString();
ins._Child = drI["_Child"].ToString();
ins.BirthTypeId = Convert.ToInt32(drI["BirthTypeId"]);
ins.HistoryId = Convert.ToInt32(drI["HistoryId"]);
ins._BirthType = drI["BirthType"].ToString();
ReturnObject.Add(ins);
}
}
}
}
}
return ReturnObject;
}
public JsonResult _UpdateBirthHistory(BirthHistory ins)
{
ins = aiRep.UpdateBirthHistory(ins);
return Json(new GridModel(commonRep.GetBirthHistories()));
}
