public List <Mating> GetMating(MatingFilter filter, int pageIndex, int pageSize, out int totalCount)
{
string querySql = "SELECT \"row_number\" () OVER (ORDER BY M.\"MatingDate\" desc) \"rownum\",M.\"Id\",M.\"FemaleId\",M.\"MaleId\",M.\"MatingDate\",M.\"IsRemindful\",M.\"PrincipalId\",M.\"OperatorId\",M.\"CreateTime\",M.\"Remark\", sf.\"SerialNumber\" AS \"FemaleSerialNumber\", sm.\"SerialNumber\" AS \"MaleSerialNumber\", bf.\"Name\" AS \"FemaleBreed\", bm.\"Name\" AS \"MaleBreed\", u.\"UserName\" AS \"OperatorName\", e.\"Name\" AS \"PrincipalName\" FROM \"T_Mating\" M JOIN \"T_Sheep\" sm ON sm.\"Id\" =M.\"MaleId\" JOIN \"T_Sheep\" sf ON sf.\"Id\" =M.\"FemaleId\" JOIN \"T_Breed\" bf ON bf.\"Id\" = sf.\"BreedId\" JOIN \"T_Breed\" bm ON bm.\"Id\" = sm.\"BreedId\" JOIN \"T_User\" u ON u.\"Id\" =M.\"OperatorId\" JOIN \"T_Employee\" e ON e.\"Id\" =M.\"PrincipalId\"";
string countSql = "SELECT COUNT (m.\"Id\") FROM \"T_Mating\" M JOIN \"T_Sheep\" sm ON sm.\"Id\" =M.\"MaleId\" JOIN \"T_Sheep\" sf ON sf.\"Id\" =M.\"FemaleId\" JOIN \"T_Breed\" bf ON bf.\"Id\" = sf.\"BreedId\" JOIN \"T_Breed\" bm ON bm.\"Id\" = sm.\"BreedId\" JOIN \"T_User\" u ON u.\"Id\" =M.\"OperatorId\" JOIN \"T_Employee\" e ON e.\"Id\" =M.\"PrincipalId\"";
List <Mating> list = GetPagedData <Mating, MatingFilter>(pageIndex, pageSize, out totalCount, countSql, querySql, filter);
//EDC预产期
string getEDCSql = "select \"Value\" from \"T_SheepParameter\" sp JOIN \"T_Settings\" s on s.\"Id\"=sp.\"SettingsId\" where s.\"Type\"=:type";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("type", (int)SettingsEnum.PreDeliveryRemaindful);
int edcDays = Convert.ToInt32(AdoTemplate.ExecuteScalar(CommandType.Text, getEDCSql, pms));
list.ForEach(m => { m.EDC = m.MatingDate.AddDays(edcDays); });
return(list);
}
public void MatingTest()
{
//var result= Helper.GetService().AddMating("1617140b-c257-4fff-b05f-654fc6f96761", "cbca2fa4-cc98-4d1d-9564-ab8369b68997", DateTime.Now.AddMonths(-1), "3013EAF8-18C0-404C-A847-7844E9A5D2DF", "9bed1d4d-e0c0-427f-afdc-abe26a095719", null);
int pageIndex = 0;
int pageSize = 5;
int totalCount = 0;
MatingFilter filter = new MatingFilter()
{
IsRemindful = true
};
var list = Helper.GetService().GetMating(filter, pageIndex, pageSize, out totalCount);
Assert.AreEqual(3, totalCount);
//MatingFilter filter = new MatingFilter() { IsRemindful = true };
//var list = Helper.GetService().GetMating(filter);
//Assert.AreEqual(3,list.Count);
}
public List <Mating> GetMating(MatingFilter filter, int rowsCount)
{
string sql = "SELECT \"row_number\" () OVER (ORDER BY M.\"MatingDate\" desc) \"rownum\",M.\"Id\",M.\"FemaleId\",M.\"MaleId\",M.\"MatingDate\",M.\"IsRemindful\",M.\"PrincipalId\",M.\"OperatorId\",M.\"CreateTime\",M.\"Remark\", sf.\"SerialNumber\" AS \"FemaleSerialNumber\", sm.\"SerialNumber\" AS \"MaleSerialNumber\", bf.\"Name\" AS \"FemaleBreed\", bm.\"Name\" AS \"MaleBreed\", u.\"UserName\" AS \"OperatorName\", e.\"Name\" AS \"PrincipalName\" FROM \"T_Mating\" M JOIN \"T_Sheep\" sm ON sm.\"Id\" =M.\"MaleId\" JOIN \"T_Sheep\" sf ON sf.\"Id\" =M.\"FemaleId\" JOIN \"T_Breed\" bf ON bf.\"Id\" = sf.\"BreedId\" JOIN \"T_Breed\" bm ON bm.\"Id\" = sm.\"BreedId\" JOIN \"T_User\" u ON u.\"Id\" =M.\"OperatorId\" JOIN \"T_Employee\" e ON e.\"Id\" =M.\"PrincipalId\"";
return(GetRuledRowsData <Mating, MatingFilter>(rowsCount, sql, filter));
}