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 InseminationHistory InsertInseminationHistory(InseminationHistory ins)
{
InseminationHistory ReturnObject = new InseminationHistory();
int ModifiedBy = (int)HttpContext.Current.Session["UserNo"];
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.InsertInseminationHistory;
cmdI.CommandType = System.Data.CommandType.StoredProcedure;
cmdI.Parameters.AddWithValue("@AnimalId", ins.AnimalId);
cmdI.Parameters.AddWithValue("@TubeId", ins.TubeId);
cmdI.Parameters.AddWithValue("@ModifiedDate", DateTime.Now);
cmdI.Parameters.AddWithValue("@ModifiedBy", ModifiedBy);
cmdI.Parameters.AddWithValue("@CompanyId", ins.CompanyId);
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 <InseminationHistory> GetInseminationHistories()
{
List <InseminationHistory> ReturnObject = new List <InseminationHistory>();
InseminationHistory 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.GetTubeHistories + " @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 InseminationHistory();
ins.TubeId = Convert.ToInt32(drI["TubeId"]);
ins.CompanyId = Convert.ToInt32(drI["CompanyId"]);
ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]);
ins.ModifiedBy = Convert.ToInt32(drI["ModifiedBy"]);
ins._Username = drI["Username"].ToString();
ins._Company = drI["Company"].ToString();
ins._AnimalName = drI["Animal"].ToString();
ins.AnimalId = Convert.ToInt32(drI["AnimalId"]);
ins.HistoryId = Convert.ToInt32(drI["HistoryId"]);
ReturnObject.Add(ins);
}
}
}
}
}
return(ReturnObject);
}
public List <InseminationHistory> GetInseminationHistories(int AnimalId)
{
List <InseminationHistory> ReturnObject = new List <InseminationHistory>();
InseminationHistory ins;
DataBaseConnection dbConn = new DataBaseConnection();
using (var con = dbConn.SqlConn())
{
con.Open();
using (SqlCommand cmd = new SqlCommand("exec " + CommonStrings.GetTubeHistoryForAnimal + " @AnimalId", con))
{
cmd.Parameters.AddWithValue("@AnimalId", AnimalId);
using (var drI = cmd.ExecuteReader())
{
while (drI.Read())
{
ins = new InseminationHistory();
ins.TubeId = Convert.ToInt32(drI["TubeId"]);
ins.CompanyId = Convert.ToInt32(drI["CompanyId"]);
ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]);
ins.ModifiedBy = Convert.ToInt32(drI["ModifiedBy"]);
ins._Username = drI["Username"].ToString();
ins._Company = drI["Company"].ToString();
ins._AnimalName = drI["Animal"].ToString();
ins.AnimalId = Convert.ToInt32(drI["AnimalId"]);
ins.HistoryId = Convert.ToInt32(drI["HistoryId"]);
ReturnObject.Add(ins);
}
}
}
}
return(ReturnObject);
}
public InseminationHistory InsertInseminationHistorySim(InseminationHistory ins)
{
InseminationHistory ReturnObject = new InseminationHistory();
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.InsertInseminationHistory;
cmdI.CommandType = System.Data.CommandType.StoredProcedure;
cmdI.Parameters.AddWithValue("@AnimalId", ins.AnimalId);
cmdI.Parameters.AddWithValue("@TubeId", ins.TubeId);
cmdI.Parameters.AddWithValue("@ModifiedDate", ins.ModifiedDate);
cmdI.Parameters.AddWithValue("@ModifiedBy", ins.ModifiedBy);
cmdI.Parameters.AddWithValue("@CompanyId", ins.CompanyId);
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<InseminationHistory> GetInseminationHistories(int AnimalId)
{
List<InseminationHistory> ReturnObject = new List<InseminationHistory>();
InseminationHistory ins;
DataBaseConnection dbConn = new DataBaseConnection();
using (var con = dbConn.SqlConn())
{
con.Open();
using (SqlCommand cmd = new SqlCommand("exec " + CommonStrings.GetTubeHistoryForAnimal + " @AnimalId", con))
{
cmd.Parameters.AddWithValue("@AnimalId", AnimalId);
using (var drI = cmd.ExecuteReader())
{
while (drI.Read())
{
ins = new InseminationHistory();
ins.TubeId = Convert.ToInt32(drI["TubeId"]);
ins.CompanyId = Convert.ToInt32(drI["CompanyId"]);
ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]);
ins.ModifiedBy = Convert.ToInt32(drI["ModifiedBy"]);
ins._Username = drI["Username"].ToString();
ins._Company = drI["Company"].ToString();
ins._AnimalName = drI["Animal"].ToString();
ins.AnimalId = Convert.ToInt32(drI["AnimalId"]);
ins.HistoryId = Convert.ToInt32(drI["HistoryId"]);
ReturnObject.Add(ins);
}
}
}
}
return ReturnObject;
}
public List<InseminationHistory> GetInseminationHistories()
{
List<InseminationHistory> ReturnObject = new List<InseminationHistory>();
InseminationHistory 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.GetTubeHistories + " @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 InseminationHistory();
ins.TubeId = Convert.ToInt32(drI["TubeId"]);
ins.CompanyId = Convert.ToInt32(drI["CompanyId"]);
ins.ModifiedDate = Convert.ToDateTime(drI["ModifiedDate"]);
ins.ModifiedBy = Convert.ToInt32(drI["ModifiedBy"]);
ins._Username = drI["Username"].ToString();
ins._Company = drI["Company"].ToString();
ins._AnimalName = drI["Animal"].ToString();
ins.AnimalId = Convert.ToInt32(drI["AnimalId"]);
ins.HistoryId = Convert.ToInt32(drI["HistoryId"]);
ReturnObject.Add(ins);
}
}
}
}
}
return ReturnObject;
}
public JsonResult _InsertInsemination(InseminationHistory ins, int AnimalId)
{
ins.AnimalId = AnimalId;
ins = aiRep.InsertInseminationHistory(ins);
return Json(new GridModel(commonRep.GetInseminationHistories(AnimalId)));
}