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; }