public static List <SampleAnalizeCrotalia> getSampleAnalizeCrotalia(int fermaid, int nrpar, int curryear)
{
// int curryear = DateTime.Now.Year;
List <SampleAnalizeCrotalia> samples = new List <SampleAnalizeCrotalia>();
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AdditionalInformation"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
// cmd.CommandText = "SELECT c.Crotalia,m.NrComanda, m.DataTestare,SUM(m.CantitateLaPrelevare) as Cant, SUM(m.Grasime) as Grasime,"
// + "SUM(m.ProcentProteine) as Proteine,SUM(m.Caseina) as Caseina,SUM(m.ProcentLactoza) as Lactoza,SUM(m.SubstantaUscata) as Solide,"
// +"SUM(m.PH) as Ph,SUM(m.Urea) as Urea,SUM(m.NumarCeluleSomatice) as NCS FROM Crotalii c, MostreTancuri m "
//+"WHERE c.FermaId =m.FermaId AND c.CodBare = m.CodFerma AND m.Validat = 1 And c.FermaId ="+fermaid +
// " GROUP BY c.Crotalia,m.NrComanda,m.DataTestare ORDER BY c.Crotalia,m.NrComanda";
cmd.CommandText = "SELECT m.CodFerma as Crotalia,m.NrComanda, m.DataTestare,SUM(m.CantitateLaPrelevare) as Cant, "
+ " SUM(m.Grasime) as Grasime, SUM(m.ProcentProteine) as Proteine,SUM(m.Caseina) as Caseina,SUM(m.ProcentLactoza) as Lactoza,"
+ "SUM(m.SubstantaUscata) as Solide,SUM(m.PH) as Ph,SUM(m.Urea) as Urea,SUM(m.NumarCeluleSomatice) as NCS FROM MostreTancuri m "
+ "WHERE m.Validat = 1 And m.FermaId =" + fermaid +
" and Year(m.DataTestare) =" + curryear +
" GROUP BY m.CodFerma,m.NrComanda,m.DataTestare ORDER BY Crotalia,m.NrComanda";
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
DataTable table = ds.Tables[0];
if (table.Rows.Count > 0)
{
DataRow row = table.Rows[0];
string nrcrot = row["Crotalia"].ToString();
string refcrot = row["Crotalia"].ToString();
string refcontrol = row["NrComanda"].ToString();
string year = refcontrol.Substring(refcontrol.Length - 4);
int nrrows = table.Rows.Count;
int count = 0;
int maxcount = 20;
while (count < nrrows)
{
int countcrot = 0;
SampleAnalizeCrotalia sample = new SampleAnalizeCrotalia();
sample.Crot = refcrot;
sample.Year = year;
row = table.Rows[count];
nrcrot = row["Crotalia"].ToString();
while (count < nrrows && nrcrot.Equals(refcrot))
{
//
if (countcrot < maxcount)
{
string nrcom = row["NrComanda"].ToString();
string nrcontrol = nrcom; //.Substring(0, 2);
int nrc = 1;
if (curryear == 2012)
{
nrc = 3;
}
if (nrcontrol.StartsWith("C"))
{
try
{
int cratima = -1;
if (nrcontrol.IndexOf("-") > 0)
{
cratima = nrcontrol.IndexOf("-");
}
if (cratima > 2)
{
nrc = Convert.ToInt32(nrcontrol.Substring(1, cratima - 1));
}
else
{
nrc = Convert.ToInt32(nrcontrol.Substring(1, 1));
}
}
catch { }
}
// get cant, val par
double cant = 0;
try
{
cant = Convert.ToDouble(row["Cant"].ToString());
}
catch { cant = 0; }
double val = 0;
try
{
if (nrpar != 9)
{
val = Convert.ToDouble(row[4 + nrpar].ToString());
}
else
{
double gras = Convert.ToDouble(row["Grasime"]);
double prot = Convert.ToDouble(row["Proteine"]);
if (prot > 0)
{
val = gras / prot;
}
}
}
catch { val = 0; }
//set parameter
switch (nrc)
{
case 1: sample.C1 = val; sample.L1 = cant; break;
case 2: sample.C2 = val; sample.L2 = cant; break;
case 3: sample.C3 = val; sample.L3 = cant; break;
case 4: sample.C4 = val; sample.L4 = cant; break;
case 5: sample.C5 = val; sample.L5 = cant; break;
case 6: sample.C6 = val; sample.L6 = cant; break;
case 7: sample.C7 = val; sample.L7 = cant; break;
case 8: sample.C8 = val; sample.L8 = cant; break;
case 9: sample.C9 = val; sample.L9 = cant; break;
case 10: sample.C10 = val; sample.L10 = cant; break;
case 11: sample.C11 = val; sample.L11 = cant; break;
case 12: sample.C12 = val; sample.L12 = cant; break;
case 13: sample.C13 = val; sample.L13 = cant; break;
case 14: sample.C14 = val; sample.L14 = cant; break;
case 15: sample.C15 = val; sample.L15 = cant; break;
case 16: sample.C16 = val; sample.L16 = cant; break;
case 17: sample.C17 = val; sample.L17 = cant; break;
case 18: sample.C18 = val; sample.L18 = cant; break;
case 19: sample.C19 = val; sample.L19 = cant; break;
case 20: sample.C20 = val; sample.L20 = cant; break;
case 21: sample.C21 = val; sample.L21 = cant; break;
case 22: sample.C22 = val; sample.L22 = cant; break;
default: break;
}
}
count++;
countcrot++;
if (count < nrrows)
{
row = table.Rows[count];
nrcrot = row["Crotalia"].ToString();
}
}
refcrot = nrcrot;
refcontrol = row["NrComanda"].ToString();
year = refcontrol.Substring(refcontrol.Length - 4);
samples.Add(sample);
// add crot
}
} // if data exists
return(samples);
}
public static List<SampleAnalizeCrotalia> getSampleAnalizeCrotalia(int fermaid, int nrpar, int curryear)
{
// int curryear = DateTime.Now.Year;
List<SampleAnalizeCrotalia> samples = new List<SampleAnalizeCrotalia>();
SqlConnection conn =
new SqlConnection(ConfigurationManager.ConnectionStrings["AdditionalInformation"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
// cmd.CommandText = "SELECT c.Crotalia,m.NrComanda, m.DataTestare,SUM(m.CantitateLaPrelevare) as Cant, SUM(m.Grasime) as Grasime,"
// + "SUM(m.ProcentProteine) as Proteine,SUM(m.Caseina) as Caseina,SUM(m.ProcentLactoza) as Lactoza,SUM(m.SubstantaUscata) as Solide,"
// +"SUM(m.PH) as Ph,SUM(m.Urea) as Urea,SUM(m.NumarCeluleSomatice) as NCS FROM Crotalii c, MostreTancuri m "
//+"WHERE c.FermaId =m.FermaId AND c.CodBare = m.CodFerma AND m.Validat = 1 And c.FermaId ="+fermaid +
// " GROUP BY c.Crotalia,m.NrComanda,m.DataTestare ORDER BY c.Crotalia,m.NrComanda";
cmd.CommandText = "SELECT m.CodFerma as Crotalia,m.NrComanda, m.DataTestare,SUM(m.CantitateLaPrelevare) as Cant, "
+
" SUM(m.Grasime) as Grasime, SUM(m.ProcentProteine) as Proteine,SUM(m.Caseina) as Caseina,SUM(m.ProcentLactoza) as Lactoza,"
+
"SUM(m.SubstantaUscata) as Solide,SUM(m.PH) as Ph,SUM(m.Urea) as Urea,SUM(m.NumarCeluleSomatice) as NCS FROM MostreTancuri m "
+ "WHERE m.Validat = 1 And m.FermaId =" + fermaid +
" and Year(m.DataTestare) =" + curryear +
" GROUP BY m.CodFerma,m.NrComanda,m.DataTestare ORDER BY Crotalia,m.NrComanda";
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
DataTable table = ds.Tables[0];
if (table.Rows.Count > 0)
{
DataRow row = table.Rows[0];
string nrcrot = row["Crotalia"].ToString();
string refcrot = row["Crotalia"].ToString();
string refcontrol = row["NrComanda"].ToString();
string year = refcontrol.Substring(refcontrol.Length - 4);
int nrrows = table.Rows.Count;
int count = 0;
int maxcount = 20;
while (count < nrrows)
{
int countcrot = 0;
SampleAnalizeCrotalia sample = new SampleAnalizeCrotalia();
sample.Crot = refcrot;
sample.Year = year;
row = table.Rows[count];
nrcrot = row["Crotalia"].ToString();
while (count < nrrows && nrcrot.Equals(refcrot))
{
//
if (countcrot < maxcount)
{
string nrcom = row["NrComanda"].ToString();
string nrcontrol = nrcom; //.Substring(0, 2);
int nrc = 1;
if (curryear == 2012)
nrc = 3;
if (nrcontrol.StartsWith("C"))
{
try
{
int cratima = -1;
if (nrcontrol.IndexOf("-") > 0)
cratima = nrcontrol.IndexOf("-");
if (cratima > 2)
nrc = Convert.ToInt32(nrcontrol.Substring(1, cratima - 1));
else
nrc = Convert.ToInt32(nrcontrol.Substring(1, 1));
}
catch
{
}
}
// get cant, val par
double cant = 0;
try
{
cant = Convert.ToDouble(row["Cant"].ToString(), CultureInfo.InvariantCulture);
}
catch
{
cant = 0;
}
double val = 0;
try
{
if (nrpar != 9)
val = Convert.ToDouble(row[4 + nrpar].ToString(), CultureInfo.InvariantCulture);
else
{
double gras = Convert.ToDouble(row["Grasime"], CultureInfo.InvariantCulture);
double prot = Convert.ToDouble(row["Proteine"], CultureInfo.InvariantCulture);
if (prot > 0)
val = gras / prot;
}
}
catch
{
val = 0;
}
//set parameter
switch (nrc)
{
case 1:
sample.C1 = val;
sample.L1 = cant;
break;
case 2:
sample.C2 = val;
sample.L2 = cant;
break;
case 3:
sample.C3 = val;
sample.L3 = cant;
break;
case 4:
sample.C4 = val;
sample.L4 = cant;
break;
case 5:
sample.C5 = val;
sample.L5 = cant;
break;
case 6:
sample.C6 = val;
sample.L6 = cant;
break;
case 7:
sample.C7 = val;
sample.L7 = cant;
break;
case 8:
sample.C8 = val;
sample.L8 = cant;
break;
case 9:
sample.C9 = val;
sample.L9 = cant;
break;
case 10:
sample.C10 = val;
sample.L10 = cant;
break;
case 11:
sample.C11 = val;
sample.L11 = cant;
break;
case 12:
sample.C12 = val;
sample.L12 = cant;
break;
case 13:
sample.C13 = val;
sample.L13 = cant;
break;
case 14:
sample.C14 = val;
sample.L14 = cant;
break;
case 15:
sample.C15 = val;
sample.L15 = cant;
break;
case 16:
sample.C16 = val;
sample.L16 = cant;
break;
case 17:
sample.C17 = val;
sample.L17 = cant;
break;
case 18:
sample.C18 = val;
sample.L18 = cant;
break;
case 19:
sample.C19 = val;
sample.L19 = cant;
break;
case 20:
sample.C20 = val;
sample.L20 = cant;
break;
case 21:
sample.C21 = val;
sample.L21 = cant;
break;
case 22:
sample.C22 = val;
sample.L22 = cant;
break;
default:
break;
}
}
count++;
countcrot++;
if (count < nrrows)
{
row = table.Rows[count];
nrcrot = row["Crotalia"].ToString();
}
}
refcrot = nrcrot;
refcontrol = row["NrComanda"].ToString();
year = refcontrol.Substring(refcontrol.Length - 4);
samples.Add(sample);
// add crot
}
} // if data exists
return samples;
}
