public static List<MostreFabrica> GetMostreFerma(string fermaid, DateTime datatestare)
{
List<MostreFabrica> list = new List<MostreFabrica>();
try
{
logger.Info("GetMostreFerma|fermaid:{0}|datatestare:{1}", fermaid, datatestare);
SqlConnection cnn =
new SqlConnection(ConfigurationManager.ConnectionStrings["fccl2ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
StringBuilder cmdText =
new StringBuilder(
"SELECT Ferme_CCL.ID, Ferme_CCL.Nume,Ferme_CCL.FabricaID,Ferme_CCL.PersonaDeContact, MostreTancuri.DataPrelevare, MostreTancuri.DataPrimirii, MostreTancuri.CodBare,");
cmdText.Append(
"MostreTancuri.Grasime, MostreTancuri.ProcentProteine,MostreTancuri.ProcentLactoza, MostreTancuri.SubstantaUscata, MostreTancuri.PunctInghet, MostreTancuri.Antibiotice,");
cmdText.Append(
"MostreTancuri.NumarCeluleSomatice, MostreTancuri.IncarcaturaGermeni, MostreTancuri.PH,MostreTancuri.Urea,MostreTancuri.GrasimeProv,MostreTancuri.Caseina, MostreTancuri.PrelevatorID,");
cmdText.AppendLine(
"MostreTancuri.DataTestare,MostreTancuri.DataTestareFinala,MostreTancuri.NrComanda,MostreTancuri.CodFerma,MostreTancuri.NumePrelevator ");
cmdText.AppendLine("FROM Ferme_CCL, MostreTancuri ");
cmdText.Append("WHERE MostreTancuri.DataTestareFinala = CONVERT(date, '" + datatestare.ToShortDateString() + "', 103) ");
cmdText.Append(" AND Ferme_CCL.ID = " + fermaid);
cmdText.Append(" AND Ferme_CCL.ID = MostreTancuri.FermaID ");
cmdText.Append(" AND MostreTancuri.Validat = 1 ORDER BY Ferme_CCL.Nume");
cmd.CommandText = cmdText.ToString();
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
logger.Info("GetMostreFerma|executing:{0}", cmdText);
while (reader.Read())
{
MostreFabrica mf = new MostreFabrica();
mf.CodBare = Convert.ToString(reader["CodBare"]);
mf.DataTestare = Convert.ToDateTime(reader["DataTestare"]);
mf.DataTestareFinala = Convert.ToDateTime(reader["DataTestareFinala"]);
mf.DataPrimirii = Convert.ToDateTime(reader["DataPrimirii"]);
mf.DataPrelevare = Convert.ToDateTime(reader["DataPrelevare"]);
mf.Grasime = Utils.NormalizeForDisplay(reader["Grasime"], "-");
mf.Proteina = Utils.NormalizeForDisplay(reader["ProcentProteine"], "-");
mf.Lactoza = Utils.NormalizeForDisplay(reader["ProcentLactoza"], "-");
mf.Substu = Utils.NormalizeForDisplay(reader["SubstantaUscata"], "-");
mf.NCS = Utils.NormalizeForDisplay(reader["NumarCeluleSomatice"], "-");
mf.NTG = Utils.NormalizeForDisplay(reader["IncarcaturaGermeni"], "-");
mf.Puncti = Utils.NormalizeForDisplay(reader["PunctInghet"], "-");
double ntg = 0;
if (mf.NTG != "-" && (double.TryParse(Convert.ToString(ntg), NumberStyles.Any, CultureInfo.CurrentCulture, out ntg) ||
double.TryParse(Convert.ToString(ntg), NumberStyles.Any, CultureInfo.InvariantCulture, out ntg)))
{
if (ntg >= 10000) mf.NTG = ">10000";
}
mf.Apa = "-";
if (!mf.Puncti.Equals("-"))
{
mf.Apa = UMostre.ApaAdaugata(mf.Puncti);
mf.Puncti = "-0." + mf.Puncti;
}
mf.Antib = Convert.ToString(reader["Antibiotice"]);
if (mf.Antib != "")
{
if (mf.Antib == "0")
mf.Antib = "Negativ";
else
mf.Antib = "Pozitiv";
}
else
mf.Antib = "-";
//
mf.Urea = Utils.NormalizeForDisplay(reader["Urea"], "-");
mf.Ph = Utils.NormalizeForDisplay(reader["PH"], "-");
mf.Combi = Convert.ToString(reader["GrasimeProv"]);
mf.PrelevatoriId = Convert.ToString(reader["PrelevatorID"]);
mf.Casein = Utils.NormalizeForDisplay(reader["Caseina"], "-");
mf.CodFerma = Convert.ToString(reader["CodFerma"]);
// mf.PrelevatoriNume = Convert.ToString(reader["NumePrelevator"]);
//get prelevator name
mf.NrComanda = Convert.ToString(reader["NrComanda"]);
if (mf.PrelevatoriId.Equals("0"))
{
mf.PrelevatoriNume = Convert.ToString(reader["NumePrelevator"]);
}
else
{
mf.PrelevatoriNume = Convert.ToString(reader["PersonaDeContact"]);
}
//
mf.FermeNume = Convert.ToString(reader["Nume"]);
list.Add(mf);
}
reader.Close();
cnn.Close();
SqlConnection cnnf =
new SqlConnection(ConfigurationManager.ConnectionStrings["fccl2ConnectionString"].ConnectionString);
SqlCommand cmdf = new SqlCommand();
cmdf.Connection = cnnf;
string prelevatoriIds = "(" +
string.Join(",",
list.Where(
mf =>
!mf.PrelevatoriId.Equals(mf.CodBare.Substring(0, 5)) &&
!mf.PrelevatoriId.Equals("0") &&
!string.IsNullOrWhiteSpace(mf.PrelevatoriId))
.Select(mf => mf.PrelevatoriId)) + ")";
logger.Info(string.Format("GetMostreFerma|prelevatoriIds:{0}", prelevatoriIds));
int cnt = 0;
if (prelevatoriIds.Length > 2)
{
cmdf.CommandText = "SELECT ID, NumePrelevator FROM Prelevatori WHERE CodPrelevator IN " + prelevatoriIds;
cnnf.Open();
SqlDataReader drFerme = cmdf.ExecuteReader();
while (drFerme.Read())
{
string prelevatorId = Convert.ToString(drFerme["ID"]);
foreach (var mostra in list.Where(mf => mf.PrelevatoriId == prelevatorId))
{
mostra.PrelevatoriNume = Convert.ToString(drFerme["NumePrelevator"]);
}
cnt++;
}
drFerme.Close();
cnnf.Close();
}
logger.Info(string.Format("GetMostreFerma|PrelevatoriNume cnt:{0}", cnt));
string barcodes = "('" +
string.Join("','",
list.Where(
mf => !string.IsNullOrWhiteSpace(mf.CodBare) && mf.CodBare.Trim().Length > 7)
.Select(mf => mf.CodBare.Trim().Substring(0, 7))) + "')";
logger.Info(string.Format("GetMostreFerma|CodBare:{0}", barcodes));
cnt = 0;
if (barcodes.Length > 4)
{
// get crotalia : codbare = nr.matricol and fermaid = fermaid
cmdf.CommandText = "SELECT CodBare, Crotalia FROM Crotalii where CodBare IN " + barcodes;
cnnf.Open();
SqlDataReader drCrot = cmdf.ExecuteReader();
while (drCrot.Read())
{
string barcode = Convert.ToString(drCrot["CodBare"]);
foreach (
var mostra in
list.Where(mf => !string.IsNullOrWhiteSpace(mf.CodBare) && mf.CodBare.StartsWith(barcode)))
{
mostra.Crotalia = Convert.ToString(drCrot["Crotalia"]);
cnt++;
}
}
drCrot.Close();
cnnf.Close();
}
logger.Info(string.Format("GetMostreFerma|Crotalia cnt:{0}", cnt));
logger.Info(string.Format("GetMostreFerma|Crotalia by code & ferma cnt:{0}",
list.Count(mf => string.IsNullOrWhiteSpace(mf.Crotalia))));
foreach (var mf in list.Where(mf => string.IsNullOrWhiteSpace(mf.Crotalia)))
{
cmdf.CommandText = "SELECT * FROM Crotalii where CodBare = '" + mf.CodFerma + "' AND FermaId = " +
fermaid;
cnnf.Open();
SqlDataReader drCrot = cmdf.ExecuteReader();
if (drCrot.Read())
mf.Crotalia = Convert.ToString(drCrot["Crotalia"]);
else
mf.Crotalia = " ";
if (mf.Crotalia.Length > 0 && mf.Crotalia.IndexOf("_") > 0)
mf.Crotalia = mf.Crotalia.Substring(mf.Crotalia.IndexOf("_") + 1);
if (mf.Crotalia.Trim().Length == 0)
mf.Crotalia = mf.CodFerma;
logger.Info("GetMostreFerma|Crotalia:{0}|codFerma:{1}|fermaid:{2}", mf.Crotalia, mf.CodFerma, fermaid);
drCrot.Close();
cnnf.Close();
}
list.Sort(delegate(MostreFabrica a, MostreFabrica b) { return a.Crotalia.CompareTo(b.Crotalia); });
}
catch (Exception ex)
{
logger.Fatal("GetMostreFerma|fermaid:{0}|datatestare:{1}|msg:{2}|stack:{3}", fermaid, datatestare,
ex.Message, ex.StackTrace);
}
return list;
}
//get mostra
public static MostreFabrica GetMostraByCodBare(string codbare)
{
SqlConnection cnn = new SqlConnection(
ConfigurationManager.ConnectionStrings
["fccl2ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "SELECT * FROM MostreTancuri WHERE " +
"CodBare ='" + codbare + "'";
//"ID =" + id;
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
List<MostreFabrica> list = new List<MostreFabrica>();
while (reader.Read())
{
MostreFabrica mf = new MostreFabrica();
mf.Id = Convert.ToString(reader["ID"]);
mf.CodBare = Convert.ToString(reader["CodBare"]);
mf.Rasa = Convert.ToString(reader["Rasa"]);
mf.NrComanda = Convert.ToString(reader["NrComanda"]);
mf.DataTestare = Convert.ToDateTime(reader["DataTestare"]);
mf.DataTestareFinala = Convert.ToDateTime(reader["DataTestareFinala"]);
mf.DataPrimirii = Convert.ToDateTime(reader["DataPrimirii"]);
//mf.DataPrimirii = (DateTime)Convert.ToString(reader["DataPrimirii"]);
mf.DataPrelevare = Convert.ToDateTime(reader["DataPrelevare"]);
mf.Grasime = Utils.NormalizeForDisplay(reader["Grasime"], string.Empty);
mf.Proteina = Utils.NormalizeForDisplay(reader["ProcentProteine"], string.Empty);
mf.Lactoza = Utils.NormalizeForDisplay(reader["ProcentLactoza"], string.Empty);
mf.Substu = Utils.NormalizeForDisplay(reader["SubstantaUscata"], string.Empty);
mf.Urea = Utils.NormalizeForDisplay(reader["Urea"], string.Empty);
mf.Ph = Utils.NormalizeForDisplay(reader["PH"], string.Empty);
mf.NCS = Utils.NormalizeForDisplay(reader["NumarCeluleSomatice"], string.Empty);
mf.NTG = Utils.NormalizeForDisplay(reader["IncarcaturaGermeni"], string.Empty);
mf.Casein = Utils.NormalizeForDisplay(reader["Caseina"], string.Empty);
mf.Puncti = Utils.NormalizeForDisplay(reader["PunctInghet"], string.Empty);
mf.Apa = "-";
if (!mf.Puncti.Equals(""))
{
mf.Apa = UMostre.ApaAdaugata(mf.Puncti);
mf.Puncti = "-0." + mf.Puncti;
}
mf.Antib = Convert.ToString(reader["Antibiotice"]);
if (mf.Antib == "-1")
{
mf.Antib = "1";
}
mf.PrelevatoriId = Convert.ToString(reader["PrelevatorID"]);
mf.PrelevatoriNume = Convert.ToString(reader["NumePrelevator"]);
mf.Cant = Convert.ToString(reader["CantitateLaPrelevare"]);
mf.NrComanda = Convert.ToString(reader["NrComanda"]);
mf.NumeProba = Convert.ToString(reader["NumeProba"]);
mf.IdZilnic = Convert.ToString(reader["IdZilnic"]);
mf.Definitiv = Convert.ToBoolean(reader["Definitiv"]);
mf.Validat = Convert.ToBoolean(reader["Validat"]);
mf.Sentsms = Convert.ToBoolean(reader["SentSms"]);
mf.FabriciNume = Convert.ToString(reader["NumeClient"]);
mf.FabriciOras = Convert.ToString(reader["Localitate"]);
mf.FabriciJudet = Convert.ToString(reader["Judet"]);
mf.FabriciStrada = Convert.ToString(reader["AdresaClient"]);
list.Add(mf);
}
cnn.Close();
return list.FirstOrDefault();
}
//
public static List<MostreFabrica> GetMostreFCB(string client, string datatestare)
{
SqlConnection cnn = new SqlConnection(
ConfigurationManager.ConnectionStrings
["fccl2ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "SELECT MostreTancuri.DataPrelevare, MostreTancuri.DataPrimirii, MostreTancuri.CodBare," +
"MostreTancuri.Grasime, MostreTancuri.ProcentProteine,MostreTancuri.ProcentLactoza, MostreTancuri.SubstantaUscata, MostreTancuri.PunctInghet, MostreTancuri.Antibiotice," +
"MostreTancuri.NumarCeluleSomatice, MostreTancuri.IncarcaturaGermeni, MostreTancuri.PH,MostreTancuri.Urea,MostreTancuri.Caseina,MostreTancuri.GrasimeProv,MostreTancuri.PrelevatorID," +
"MostreTancuri.DataTestare,MostreTancuri.DataTestareFinala,MostreTancuri.NrComanda,MostreTancuri.NumeProba, MostreTancuri.NumePrelevator, MostreTancuri.NumeClient,MostreTancuri.AdresaClient,MostreTancuri.Localitate,MostreTancuri.Judet " +
"FROM MostreTancuri WHERE"
+ " MostreTancuri.DataTestareFinala = CONVERT(date, '" +
datatestare + "', 103) "
+ " AND MostreTancuri.NumeClient = '" + client
+ "' AND MostreTancuri.Validat = 1 ORDER BY MostreTancuri.CodBare";
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
List<MostreFabrica> list = new List<MostreFabrica>();
while (reader.Read())
{
MostreFabrica mf = new MostreFabrica();
mf.CodBare = Convert.ToString(reader["CodBare"]);
mf.DataTestare = Convert.ToDateTime(reader["DataTestare"]);
mf.DataTestareFinala = Convert.ToDateTime(reader["DataTestareFinala"]);
mf.DataPrimirii = Convert.ToDateTime(reader["DataPrimirii"]);
mf.DataPrelevare = Convert.ToDateTime(reader["DataPrelevare"]);
mf.Grasime = Utils.NormalizeForDisplay(reader["Grasime"], "-");
mf.Proteina = Utils.NormalizeForDisplay(reader["ProcentProteine"], "-");
mf.Lactoza = Utils.NormalizeForDisplay(reader["ProcentLactoza"], "-");
mf.Substu = Utils.NormalizeForDisplay(reader["SubstantaUscata"], "-");
mf.NCS = Utils.NormalizeForDisplay(reader["NumarCeluleSomatice"], "-");
mf.NTG = Utils.NormalizeForDisplay(reader["IncarcaturaGermeni"], "-");
mf.Puncti = Utils.NormalizeForDisplay(reader["PunctInghet"], "-");
double ntg = 0;
if (mf.NTG != "-" && (double.TryParse(Convert.ToString(ntg), NumberStyles.Any, CultureInfo.CurrentCulture, out ntg) ||
double.TryParse(Convert.ToString(ntg), NumberStyles.Any, CultureInfo.InvariantCulture, out ntg)))
{
if (ntg >= 10000) mf.NTG = ">10000";
}
mf.Apa = "-";
if (!mf.Puncti.Equals("-"))
{
mf.Apa = UMostre.ApaAdaugata(mf.Puncti);
mf.Puncti = "-0." + mf.Puncti;
}
mf.Antib = Convert.ToString(reader["Antibiotice"]);
if (mf.Antib != "")
{
if (mf.Antib == "0")
mf.Antib = "Negativ";
else
mf.Antib = "Pozitiv";
}
else
mf.Antib = "-";
mf.Urea = Utils.NormalizeForDisplay(reader["Urea"], "-");
mf.Ph = Utils.NormalizeForDisplay(reader["PH"], "-");
mf.Combi = Convert.ToString(reader["GrasimeProv"]);
mf.Casein = Utils.NormalizeForDisplay(reader["Caseina"], "-");
mf.NumeProba = Convert.ToString(reader["NumeProba"]);
mf.NumePrelevator = Convert.ToString(reader["NumePrelevator"]);
mf.FabriciStrada = Convert.ToString(reader["AdresaClient"]);
mf.FabriciOras = Convert.ToString(reader["Localitate"]);
mf.FabriciJudet = Convert.ToString(reader["Judet"]);
mf.NrComanda = Convert.ToString(reader["NrComanda"]);
list.Add(mf);
}
cnn.Close();
return list;
}
public void UpdateMostra(MostreFabrica mf)
{
string grasime = mf.Grasime.Trim().Equals("") ? "0" : mf.Grasime.Trim().Equals("0") ? "0.00001" : mf.Grasime.Trim();
string proteina = mf.Proteina.Trim().Equals("") ? "0" : mf.Proteina.Trim().Equals("0") ? "0.00001" : mf.Proteina.Trim();
string lactoza = mf.Lactoza.Trim().Equals("") ? "0" : mf.Lactoza.Trim().Equals("0") ? "0.00001" : mf.Lactoza.Trim();
string caseina = mf.Casein.Trim().Equals("") ? "0" : mf.Casein.Trim().Equals("0") ? "0.00001" : mf.Casein.Trim();
string substu = mf.Substu.Trim().Equals("") ? "0" : mf.Substu.Trim().Equals("0") ? "0.00001" : mf.Substu.Trim();
string pcti = mf.Puncti.Trim().Equals("") ? "0" : mf.Puncti.Trim().Equals("0") ? "0.00001" : mf.Puncti.Trim();
//
if (pcti.Length > 0 && (!pcti.Equals("0.00001")) && pcti.IndexOf('.') > 0)
pcti = pcti.Substring(pcti.IndexOf('.') + 1);
string antib = mf.Antib.Trim();
//
string ncs = mf.NCS.Trim().Equals("") ? "0" : mf.NCS.Trim().Equals("0") ? "0.00001" : mf.NCS.Trim();
string ntg = mf.NTG.Trim().Equals("") ? "0" : mf.NCS.Trim().Equals("0") ? "0.00001" : mf.NTG.Trim();
string urea = mf.Urea.Trim().Equals("") ? "0" : mf.Urea.Trim().Equals("0") ? "0.00001" : mf.Urea.Trim();
string ph = mf.Ph.Trim().Equals("") ? "0" : mf.Ph.Trim().Equals("0") ? "0.00001" : mf.Ph.Trim();
string codferma = mf.CodBare.Substring(0, 5);
string definitiv = mf.Definitiv ? "1" : "0";
string validat = mf.Validat ? "1" : "0";
string sentsms = mf.Sentsms ? "1" : "0";
string cant = mf.Cant.Trim().Equals("") ? "0" : mf.Cant.Trim();
string prelid = mf.PrelevatoriId.Trim().Equals("") ? "0" : mf.PrelevatoriId.Trim();
SqlConnection cnn = new SqlConnection(
ConfigurationManager.ConnectionStrings
["fccl2ConnectionString"].ConnectionString);
string query = "UPDATE MostreTancuri SET IdZilnic="+mf.IdZilnic+",CantitateLaPrelevare="+cant+
",PrelevatorID="+prelid+",DataPrimirii=CONVERT(datetime,'" + mf.DataPrimirii + "',103),"+
"DataPrelevare='"+mf.DataPrelevare+"',DataTestare='"+mf.DataTestare+"',DataTestareFinala='"+mf.DataTestareFinala+"',"+
"Grasime="+grasime+",ProcentProteine="+proteina+",Caseina="+caseina+",ProcentLactoza="+lactoza+",SubstantaUscata="+substu+",PunctInghet="+
pcti+",NumarCeluleSomatice="+ncs+",IncarcaturaGermeni="+ntg+",Urea="+urea+",PH="+ph+",NrComanda='"+mf.NrComanda+"',NumePrelevator='"+
mf.PrelevatoriNume+"',NumeProba='"+mf.NumeProba+"',NumeClient='"+mf.FabriciNume+"',AdresaClient='"+mf.FabriciStrada+"',Localitate='"+
mf.FabriciOras+"',Judet='"+mf.FabriciJudet+"',Definitiv="+definitiv+",Validat="+validat+",Sentsms="+sentsms+",CodFerma='"+codferma+
"' WHERE CodBare='" + mf.CodBare + "'";
SqlCommand cmd = new SqlCommand(query, cnn);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
public void DeleteMostra(MostreFabrica mf)
{
SqlConnection cnn =
new SqlConnection(ConfigurationManager.ConnectionStrings["fccl2ConnectionString"].ConnectionString);
string query = "DELETE FROM MostreTancuri WHERE " + "ID=" + mf.Id;
SqlCommand cmd = new SqlCommand(query, cnn);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
//
//getmostresms
public static List<MostreFabrica> GetMostreSMSTest(DateTime datatestare)
{
SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["fccl2ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "SELECT Ferme_CCL.ID, Ferme_CCL.Nume,Ferme_CCL.FabricaID,Ferme_CCL.Cod,Ferme_CCL.Strada,Ferme_CCL.Numar,Ferme_CCL.Oras,Ferme_CCL.PersonaDeContact,Ferme_CCL.TelPersoanaContact,Ferme_CCL.Ferme_CCL,Ferme_CCL.FermierID,Ferme_CCL.Telefon, "
+ "MostreTancuri.DataPrelevare, MostreTancuri.DataPrimirii, MostreTancuri.CodBare," +
"MostreTancuri.Grasime, MostreTancuri.ProcentProteine,MostreTancuri.ProcentLactoza, MostreTancuri.SubstantaUscata, MostreTancuri.PunctInghet, MostreTancuri.Antibiotice," +
"MostreTancuri.NumarCeluleSomatice, MostreTancuri.IncarcaturaGermeni,MostreTancuri.PH,MostreTancuri.Urea,MostreTancuri.Caseina,MostreTancuri.GrasimeProv, MostreTancuri.PrelevatorID," +
"MostreTancuri.DataTestare,MostreTancuri.DataTestareFinala,MostreTancuri.NrComanda " +
"FROM Ferme_CCL, MostreTancuri WHERE"
+ " MostreTancuri.DataTestareFinala = CONVERT(date, '" + datatestare.ToShortDateString() + "', 103) "
//+ " AND MostreTancuri.CodFerma is not null"
+ " AND MostreTancuri.CodFerma = Ferme_CCL.Cod "
//+ " AND MostreTancuri.SentSms=0"
//TODO: uncomment !!!
+ " AND Ferme_CCL.SendSms=1"
+ " AND MostreTancuri.Validat = 1 ORDER BY Ferme_CCL.Nume";
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
List<MostreFabrica> list = new List<MostreFabrica>();
int pos = 0;
string ferma = "";
while (reader.Read())
{
MostreFabrica mf = new MostreFabrica();
mf.CodBare = Convert.ToString(reader["CodBare"]);
mf.NrComanda = Convert.ToString(reader["NrComanda"]);
mf.DataTestare = Convert.ToDateTime(reader["DataTestare"]);
mf.DataTestareFinala = Convert.ToDateTime(reader["DataTestareFinala"]);
mf.DataPrimirii = Convert.ToDateTime(reader["DataPrimirii"]);
mf.DataPrelevare = Convert.ToDateTime(reader["DataPrelevare"]);
mf.Grasime = Utils.NormalizeForDisplay(reader["Grasime"], "-");
mf.Proteina = Utils.NormalizeForDisplay(reader["ProcentProteine"], "-");
mf.Lactoza = Utils.NormalizeForDisplay(reader["ProcentLactoza"], "-");
mf.Substu = Utils.NormalizeForDisplay(reader["SubstantaUscata"], "-");
mf.NCS = Utils.NormalizeForDisplay(reader["NumarCeluleSomatice"], "-");
mf.NTG = Utils.NormalizeForDisplay(reader["IncarcaturaGermeni"], "-");
mf.Puncti = Utils.NormalizeForDisplay(reader["PunctInghet"], "-");
double ntg = 0;
if (mf.NTG != "-" && (double.TryParse(Convert.ToString(ntg), NumberStyles.Any, CultureInfo.CurrentCulture, out ntg) ||
double.TryParse(Convert.ToString(ntg), NumberStyles.Any, CultureInfo.InvariantCulture, out ntg)))
{
if (ntg >= 10000) mf.NTG = ">10000";
}
mf.Apa = "-";
if (!mf.Puncti.Equals("-"))
{
mf.Apa = UMostre.ApaAdaugata(mf.Puncti);
mf.Puncti = "-0." + mf.Puncti;
}
mf.Antib = Convert.ToString(reader["Antibiotice"]);
if (mf.Antib != "")
{
if (mf.Antib == "0")
mf.Antib = "N";
else
mf.Antib = "P";
}
else
mf.Antib = "-";
mf.Urea = Utils.NormalizeForDisplay(reader["Urea"], "-");
mf.Ph = Utils.NormalizeForDisplay(reader["PH"], "-");
mf.Combi = Convert.ToString(reader["GrasimeProv"]);
mf.Casein = Utils.NormalizeForDisplay(reader["Caseina"], "-");
mf.PrelevatoriId = Convert.ToString(reader["PrelevatorID"]);
//get prelevator name
mf.PrelevatoriNume = Convert.ToString(reader["PersonaDeContact"]);
mf.FermeNume = Convert.ToString(reader["Nume"]);
mf.CodFerma = Convert.ToString(reader["Cod"]);
//
SqlConnection connection =
new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
SqlCommand cmu =
new SqlCommand(
"select u.UserName from UsersInformation i join aspnet_Users u on i.UserId = u.UserId where i.UserCod ='" +
mf.CodFerma + "'");
//new SqlCommand("select * from UsersInformation where UserCod='" + mf.CodFerma + "'", connection);
connection.Open();
using (SqlDataReader rdr = cmu.ExecuteReader())
{
if (rdr.Read())
{
mf.FermeNume = rdr["UserName"].ToString().Trim();
}
rdr.Close();
}
//
if (!ferma.Equals(mf.FermeNume))
{
pos++;
}
mf.Nrf = pos;
ferma = mf.FermeNume;
mf.StradaFerma = Convert.ToString(reader["Strada"]);
mf.NumarFerma = Convert.ToString(reader["Numar"]);
mf.OrasFerma = Convert.ToString(reader["Oras"]);
mf.PersContact = Convert.ToString(reader["PersonaDeContact"]);
mf.TelContact = Convert.ToString(reader["TelPersoanaContact"]);
string fermeccl = Convert.ToString(reader["Ferme_CCL"]);
string fermierid = Convert.ToString(reader["FermierID"]);
list.Add(mf);
}
cnn.Close();
return list;
}
protected void FormView1_ItemUpdating(object sender, FormViewUpdateEventArgs e)
{
SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["fccl2ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
var mostra = new MostreFabrica();
mostra.CodBare = NullifyEmptyValues(e.NewValues["CodBare"]);
mostra.PrelevatoriId = NullifyEmptyValues(e.NewValues["PrelevatoriId"]);
mostra.Rasa = NullifyEmptyValues(e.NewValues["Rasa"]);
mostra.IdZilnic = NullifyEmptyValues(e.NewValues["IdZilnic"]);
DateTime datatestare;
DateTime datatestarefin;
DateTime dataprimirii;
DateTime dataprelevare;
if (!DateTime.TryParse(e.NewValues["DataTestare"].ToString().Trim(), out datatestare))
{
Label1.Text = "Data testarii invalida!";
e.Cancel = true;
return;
}
mostra.DataTestare = datatestare;
if (!DateTime.TryParse(e.NewValues["DataTestareFinala"].ToString().Trim(), out datatestarefin))
{
Label1.Text = "Data testarii finale invalida!";
e.Cancel = true;
return;
}
mostra.DataTestareFinala = datatestarefin;
if (!DateTime.TryParse(e.NewValues["DataPrimirii"].ToString().Trim(), out dataprimirii))
{
Label1.Text = "Data primirii invalida!";
e.Cancel = true;
return;
}
mostra.DataPrimirii = dataprimirii;
if (!DateTime.TryParse(e.NewValues["DataPrelevare"].ToString().Trim(), out dataprelevare))
{
Label1.Text = "Data prelevare invalida!";
e.Cancel = true;
return;
}
mostra.DataPrelevare = dataprelevare;
// verific cod ferma
if (mostra.PrelevatoriId != "0")
{
cmd.CommandText = "SELECT * from FERME_CCL WHERE Cod ='" + mostra.CodBare.Substring(0, 5) + "'";
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (!reader.Read())
{
Label1.Text = "Codul de ferma " + mostra.CodBare.Substring(0, 5) + " nu exista!";
e.Cancel = true;
reader.Close();
cnn.Close();
return;
}
else
{
Label1.Text = "";
reader.Close();
cnn.Close();
}
}
// verific id zilnic
if (mostra.IdZilnic != "")
{
cmd.CommandText = "SELECT * from MostreTancuri Where IdZilnic =" + mostra.IdZilnic + " AND DataTestare = CONVERT(date, '" + mostra.DataTestare.ToShortDateString() + "', 103)";
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
int count = 0;
while (reader.Read())
{
count++;
}
if (count > 1)
{
reader.Close();
//get max id
cmd.CommandText = "SELECT MAX(IdZilnic) AS MaxId from MostreTancuri Where DataTestare = CONVERT(date, '" + mostra.DataTestare.ToShortDateString() + "', 103)";
reader = cmd.ExecuteReader();
int idzilnic = 1;
if (reader.Read())
{
if (!reader.IsDBNull(0))
{
idzilnic = Convert.ToInt32(reader["MaxId"]) + 1;
}
}
e.NewValues["IdZilnic"] = Convert.ToString(idzilnic);
reader.Close();
cnn.Close();
}
}
else
{
cmd.CommandText = "SELECT MAX(IdZilnic) AS MaxId from MostreTancuri Where DataTestare=CONVERT(date, '" + mostra.DataTestare.ToShortDateString() + "', 103)";
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
int idzilnic = 1;
if (reader.Read())
{
if (!reader.IsDBNull(0))
{
idzilnic = Convert.ToInt32(reader["MaxId"]) + 1;
}
}
e.NewValues["IdZilnic"] = Convert.ToString(idzilnic);
reader.Close();
cnn.Close();
}
//verific fcb
mostra.NrComanda = NullifyEmptyValues(e.NewValues["NrComanda"]);
mostra.NumePrelevator = NullifyEmptyValues(e.NewValues["PrelevatoriNume"]);
mostra.FabriciNume = NullifyEmptyValues(e.NewValues["FabriciNume"]);
mostra.FabriciStrada = NullifyEmptyValues(e.NewValues["FabriciStrada"]);
mostra.FabriciOras = NullifyEmptyValues(e.NewValues["FabriciOras"]);
mostra.FabriciJudet = NullifyEmptyValues(e.NewValues["FabriciJudet"]);
mostra.NumeProba = NullifyEmptyValues(e.NewValues["NumeProba"]);
if (mostra.PrelevatoriId != "0" && (mostra.NumePrelevator != "" || mostra.FabriciNume != "" ||
mostra.FabriciStrada != "" || mostra.FabriciOras != "" || mostra.FabriciJudet != "" || mostra.NumeProba != ""))
{
Label1.Text = "Au fost completate simultan date pt. FCB si Cod Bare!";
e.Cancel = true;
return;
}
else
{
Label1.Text = "";
}
if ((mostra.PrelevatoriId == null || mostra.PrelevatoriId.Equals("0")) && (mostra.NumePrelevator == null || mostra.NumePrelevator.Equals("")))
{
Label1.Text = "Nu a fost completat prelevatorul!";
e.Cancel = true;
return;
}
Label1.Text = "";
mostra.Grasime = NullifyEmptyValues(e.NewValues["Grasime"]);
mostra.Proteina = NullifyEmptyValues(e.NewValues["Proteina"]);
mostra.Lactoza = NullifyEmptyValues(e.NewValues["Lactoza"]);
mostra.Casein = NullifyEmptyValues(e.NewValues["Casein"]);
mostra.Substu = NullifyEmptyValues(e.NewValues["Substu"]);
mostra.NCS = NullifyEmptyValues(e.NewValues["NCS"]);
mostra.Urea = NullifyEmptyValues(e.NewValues["Urea"]);
mostra.Ph = NullifyEmptyValues(e.NewValues["Ph"]);
mostra.Id = NullifyEmptyValues(e.NewValues["Id"]);
mostra.Definitiv = e.NewValues["Definitiv"].ToString().Trim() == "True";
mostra.Validat = e.NewValues["Validat"].ToString().Trim() == "True";
mostra.Sentsms = false;
mostra.UpdateMostra(mostra);
}
public void CreateReport()
{
ReportManager rManager = new ReportManager(ctx);
DateTime testDate;
int farmId;
DateTime.TryParse(TextBox1.Text, out testDate);
Int32.TryParse(DropDownList1.SelectedValue, out farmId);
Report b = rManager.GetByDateAndId(testDate, farmId);
string nrb = b.ReportNumber.ToString();
string datab = b.TestDate.ToShortDateString();
int pos = 0;
LocalReport report = new LocalReport();
byte[] file;
string mimeType, encoding, fileExtension;
string[] streams;
Warning[] warnings;
string fabricaid = DropDownList1.SelectedValue;
string fabricaname = DropDownList1.Items[DropDownList1.SelectedIndex].ToString();
// read fabrica
SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["fccl2ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "SELECT Fabrici.Strada,Fabrici.Numar,Fabrici.Oras,Judete.Denloc "
+ "FROM Fabrici,Judete WHERE Fabrici.ID=" + fabricaid + " AND Fabrici.Judet=Judete.ID";
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
string fabricastrada = Convert.ToString(reader["Strada"]);
string fabricanumar = Convert.ToString(reader["Numar"]);
string fabricaoras = Convert.ToString(reader["Oras"]);
string fabricajudet = Convert.ToString(reader["Denloc"]);
reader.Close();
cnn.Close();
string datatestare = TextBox1.Text;
// date fabrica
//deviceinfo
string deviceInfo =
"<DeviceInfo>" +
" <OutputFormat>PDF</OutputFormat>" +
" <PageWidth>29.7cm</PageWidth>" +
" <PageHeight>21cm</PageHeight>" +
" <MarginTop>0.0cm</MarginTop>" +
" <MarginLeft>0.5cm</MarginLeft>" +
" <MarginRight>0.5cm</MarginRight>" +
" <MarginBottom>0.5cm</MarginBottom>" +
"</DeviceInfo>";
string httppath = StaticDataHelper.SettingsManager.CaleRapoarteHttp;
string filepath = StaticDataHelper.SettingsManager.CaleRapoarte;
cmd.CommandTimeout = 300;
cmd.CommandText = "SELECT DISTINCT Ferme_CCL.Nume,Ferme_CCL.ID, MostreTancuri.FermaID, Ferme_CCL.Cod, "
+ "Ferme_CCL.Strada,Ferme_CCL.Numar, Ferme_CCL.Oras, Ferme_CCL.Judet,Ferme_CCL.PersonaDeContact,Ferme_CCL.TelPersoanaContact,Ferme_CCL.Ferme_CCL,Ferme_CCL.FermierID,Judete.Denloc "
+ " FROM MostreTancuri, Ferme_CCL INNER JOIN JUDETE ON Convert(int,Ferme_CCL.Judet,2)=Judete.ID WHERE"
+ " CONVERT(datetime, MostreTancuri.DataTestareFinala, 103) = CONVERT(datetime, '" + datatestare + "', 103) "
+ " AND MostreTancuri.CodFerma = Ferme_CCL.Cod " + " AND Ferme_CCL.FabricaID = " + fabricaid
+ " AND MostreTancuri.Validat = 1 ORDER BY Ferme_CCL.Nume";
cnn.Open();
SqlDataReader drferme = cmd.ExecuteReader();
//prepare report parameters
string laborator = ddlLaborator.Items[ddlLaborator.SelectedIndex].ToString();
string responsabil = ddlResponsabil.Items[ddlResponsabil.SelectedIndex].ToString();
string semnlab = ddlLaborator.SelectedValue;
string semnresp = ddlResponsabil.SelectedValue;
semnresp = semnresp.Substring(0, semnresp.LastIndexOf("."));
semnlab = semnlab.Substring(0, semnlab.LastIndexOf("."));
semnresp = semnresp.ToLower();
semnlab = semnlab.ToLower();
ReportParameter pDatatestare = new ReportParameter("datatestare", datatestare);
ReportParameter pDatab = new ReportParameter("datab", datab);
ReportParameter pNrb = new ReportParameter("nrb", nrb);
ReportParameter pFabricanume = new ReportParameter("fabricanume", fabricaname);
ReportParameter pFabricastrada = new ReportParameter("fabricastrada", fabricastrada);
ReportParameter pFabricanumar = new ReportParameter("fabricanumar", fabricanumar);
ReportParameter pFabricaoras = new ReportParameter("fabricaoras", fabricaoras);
ReportParameter pFabricajudet = new ReportParameter("fabricajudet", fabricajudet);
ReportParameter pLaborator = new ReportParameter("laborator", laborator);
ReportParameter pResponsabil = new ReportParameter("responsabil", responsabil);
List <FileLinks> list = new List <FileLinks>();
while (drferme.Read())
{
FileLinks filelink = new FileLinks();
string fermacod = Convert.ToString(drferme["Cod"]);
string fermanume = Convert.ToString(drferme["Nume"]);
string fermastrada = Convert.ToString(drferme["Strada"]);
string fermanumar = Convert.ToString(drferme["Numar"]);
string fermaoras = Convert.ToString(drferme["Oras"]);
string judet = Convert.ToString(drferme["Denloc"]);
string codferma = Convert.ToString(drferme["Cod"]);
string fermaadresa = "Str. " + fermastrada + " nr. " + fermanumar + ", " + fermaoras;
string fermatelcontact = Convert.ToString(drferme["TelPersoanaContact"]);
string fermaperscontact = Convert.ToString(drferme["PersonaDeContact"]);
ReportDataSource rds = new ReportDataSource();
rds.Name = "MostreFabrica";
List <MostreFabrica> mostre = MostreFabrica.GetMostreFerma(fabricaid, fermacod, datatestare);
rds.Value = mostre;
// build dates interval
string datatmin = datatestare;
string datatmax = datatestare;
string datatesttitlu = datatestare;
string dataprimirii = "";
string datapmin = dataprimirii;
string datapmax = dataprimirii;
string dataprimtitlu = dataprimirii;
string combi = mostre[0].Combi;
foreach (MostreFabrica mf in mostre)
{
string datamin = mf.DataTestare;
string datamax = mf.DataTestareFinala;
dataprimirii = mf.DataPrimirii;
if (datapmin == "")
{
datapmin = dataprimirii;
}
if (datapmax == "")
{
datapmax = dataprimirii;
}
if (datamin != datatmin)
{
DateTime dt1 = new DateTime(Int32.Parse(datamin.Substring(6, 4)), Int32.Parse(datamin.Substring(3, 2)), Int32.Parse(datamin.Substring(0, 2)));
DateTime dt2 = new DateTime(Int32.Parse(datatmin.Substring(6, 4)), Int32.Parse(datatmin.Substring(3, 2)), Int32.Parse(datatmin.Substring(0, 2)));
if (DateTime.Compare(dt1, dt2) < 0)
{
datatmin = datamin;
}
}
if (datamax != datatmax)
{
DateTime dt1 = new DateTime(Int32.Parse(datamax.Substring(6, 4)), Int32.Parse(datamax.Substring(3, 2)), Int32.Parse(datamax.Substring(0, 2)));
DateTime dt2 = new DateTime(Int32.Parse(datatmax.Substring(6, 4)), Int32.Parse(datatmax.Substring(3, 2)), Int32.Parse(datatmax.Substring(0, 2)));
if (DateTime.Compare(dt1, dt2) > 0)
{
datatmax = datamax;
}
}
// data primirii
if (dataprimirii != datapmin)
{
DateTime dt1 = new DateTime(Int32.Parse(dataprimirii.Substring(6, 4)), Int32.Parse(dataprimirii.Substring(3, 2)), Int32.Parse(dataprimirii.Substring(0, 2)));
DateTime dt2 = new DateTime(Int32.Parse(datapmin.Substring(6, 4)), Int32.Parse(datapmin.Substring(3, 2)), Int32.Parse(datapmin.Substring(0, 2)));
if (DateTime.Compare(dt1, dt2) < 0)
{
datapmin = dataprimirii;
}
}
if (dataprimirii != datapmax)
{
DateTime dt1 = new DateTime(Int32.Parse(dataprimirii.Substring(6, 4)), Int32.Parse(dataprimirii.Substring(3, 2)), Int32.Parse(dataprimirii.Substring(0, 2)));
DateTime dt2 = new DateTime(Int32.Parse(datapmax.Substring(6, 4)), Int32.Parse(datapmax.Substring(3, 2)), Int32.Parse(datapmax.Substring(0, 2)));
if (DateTime.Compare(dt1, dt2) > 0)
{
datapmax = dataprimirii;
}
}
}
if (datatmin != datatmax)
{
datatesttitlu = datatmin + " - " + datatmax;
}
if (datapmin != datapmax)
{
dataprimtitlu = datapmin + " si " + datapmax;
}
else
{
dataprimtitlu = datapmin;
}
// end dates interval
report.DataSources.Clear();
report.DataSources.Add(rds);
pos++;
report.ReportPath = "ReportFerme4.rdlc";
report.Refresh();
ReportParameter pDatatitlu = new ReportParameter("datatestare", datatesttitlu);
ReportParameter pDataprimirii = new ReportParameter("dataprimirii", dataprimtitlu);
ReportParameter pCombi = new ReportParameter("combi", combi);
ReportParameter pPos = new ReportParameter("pos", pos.ToString());
ReportParameter pCodferma = new ReportParameter("codferma", codferma);
ReportParameter pFermanume = new ReportParameter("fermanume", fermanume);
ReportParameter pFermaadresa = new ReportParameter("fermaadresa", fermaadresa);
ReportParameter pFermajudet = new ReportParameter("fermajudet", judet);
ReportParameter pFermaperscontact = new ReportParameter("fermaperscontact", fermaperscontact);
ReportParameter pFermatelcontact = new ReportParameter("fermatelcontact", fermatelcontact);
ReportParameter versiuneRaport = new ReportParameter("Versiune", new SettingManager(StaticDataHelper.FCCLDbContext).GetValueByName("ReportFerme4"));
ReportParameter[] p = { pDatatitlu, pDataprimirii, pCombi, pDatab, pNrb, pFabricanume, pFabricastrada, pFabricanumar, pFabricaoras, pFabricajudet, pLaborator, pResponsabil,
pPos, pFermanume, pFermaadresa, pFermajudet, pCodferma, pFermaperscontact, pFermatelcontact, versiuneRaport };
report.SetParameters(p);
file = report.Render("PDF", deviceInfo, out mimeType, out encoding, out fileExtension, out streams, out warnings);
// read from date fixe!!!
fermanume = replace_special_car_null(fermanume);
string raport_name = "RaportFerma" + datatestare.Replace("/", "_") + "_" + fermacod + "-" + fermanume + ".pdf";
string raport_excel = "RaportFerma" + datatestare.Replace("/", "_") + "_" + fermacod + "-" + fermanume + ".xls";
string path_raport_http = "http://" + Request.ServerVariables.Get("HTTP_HOST") + "/" + httppath;
string pdf_link = path_raport_http + @"Ferme/" + raport_name;
string pdf_file = filepath + @"Ferme/" + raport_name;
File.WriteAllBytes(pdf_file, file);
filelink.Url = pdf_link;
filelink.Des = raport_name;
list.Add(filelink);
}
drferme.Close();
cnn.Close();
//raport cumulat
LocalReport report_cum = new LocalReport();
ReportDataSource rdsc = new ReportDataSource();
rdsc.Name = "MostreFabrica1";
rdsc.Value = MostreFabrica.GetMostreFabrica(fabricaid, datatestare);
report_cum.DataSources.Clear();
report_cum.DataSources.Add(rdsc);
report_cum.ReportPath = "ReportCumulat4.rdlc";
report_cum.Refresh();
ReportParameter pSemnlab = new ReportParameter("semnlab", semnlab);
ReportParameter pSemnresp = new ReportParameter("semnresp", semnresp);
ReportParameter pVersiuneRaport = new ReportParameter("Versiune", new SettingManager(StaticDataHelper.FCCLDbContext).GetValueByName("ReportCumulat4"));
ReportParameter[] rp = { pDatatestare, pDatab, pNrb, pFabricanume, pFabricastrada, pFabricanumar, pFabricaoras, pFabricajudet, pLaborator, pResponsabil, pSemnlab, pSemnresp, pVersiuneRaport };
report_cum.SetParameters(rp);
byte[] file_cum = report_cum.Render("PDF", deviceInfo, out mimeType, out encoding, out fileExtension, out streams, out warnings);
string fabricanume = replace_special_car_null(fabricaname);
string raport_name_cum = "RaportCumulat" + datatestare.Replace("/", "_") + "_" + fabricaid + "-" + fabricanume + ".pdf";
string path_raport_http_cum = "http://" + Request.ServerVariables.Get("HTTP_HOST") + "/" + httppath;
string pdf_link_cum = path_raport_http_cum + @"Ferme/" + raport_name_cum;
string pdf_file_cum = filepath + @"Ferme/" + raport_name_cum;
// writefile
File.WriteAllBytes(pdf_file_cum, file_cum);
linkall.Visible = true;
linkall.NavigateUrl = pdf_link_cum;
linkall.Text = raport_name_cum;
//
Repeater1.DataSource = list;
Repeater1.DataBind();
Repeater1.Visible = true;
}
public void CreateReport()
{
LocalReport report = new LocalReport();
byte[] file;
string mimeType, encoding, fileExtension;
string[] streams;
Warning[] warnings;
ReportDataSource rds = new ReportDataSource();
rds.Name = "MostreFabrica";
List <MostreFabrica> mostre = MostreFabrica.GetMostreFabrica(DropDownList1.SelectedValue, TextBox1.Text, TextBox2.Text);
string datatestare = TextBox1.Text;
string datatestare2 = TextBox2.Text;
rds.Value = mostre;
report.DataSources.Clear();
report.DataSources.Add(rds);
report.ReportPath = "ReportFabricaIntre4.rdlc";
report.Refresh();
string deviceInfo =
"<DeviceInfo>" +
" <OutputFormat>PDF</OutputFormat>" +
" <PageWidth>29.7cm</PageWidth>" +
" <PageHeight>21cm</PageHeight>" +
" <MarginTop>0.0cm</MarginTop>" +
" <MarginLeft>0.1cm</MarginLeft>" +
" <MarginRight>0.1cm</MarginRight>" +
" <MarginBottom>0.5cm</MarginBottom>" +
"</DeviceInfo>";
string deviceInfoXls =
"<DeviceInfo>" +
" <OutputFormat>XLS</OutputFormat>" +
" <PageWidth>29.7cm</PageWidth>" +
" <PageHeight>21cm</PageHeight>" +
" <MarginTop>0.0cm</MarginTop>" +
" <MarginLeft>0.0cm</MarginLeft>" +
" <MarginRight>0.1cm</MarginRight>" +
" <MarginBottom>0.1cm</MarginBottom>" +
"</DeviceInfo>";
string fabricaid = DropDownList1.SelectedValue;
string fabricaname = DropDownList1.Items[DropDownList1.SelectedIndex].ToString();
// read fabrica
SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["fccl2ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "SELECT Fabrici.Strada,Fabrici.Numar,Fabrici.Oras,Judete.Denloc "
+ "FROM Fabrici,Judete WHERE Fabrici.ID=" + fabricaid + " AND Fabrici.Judet=Judete.ID";
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
string fabricastrada = Convert.ToString(reader["Strada"]);
string fabricanumar = Convert.ToString(reader["Numar"]);
string fabricaoras = Convert.ToString(reader["Oras"]);
string fabricajudet = Convert.ToString(reader["Denloc"]);
reader.Close();
cnn.Close();
//set report parameters
string laborator = ddlLaborator.Items[ddlLaborator.SelectedIndex].ToString();
string responsabil = ddlResponsabil.Items[ddlResponsabil.SelectedIndex].ToString();
string combi = mostre[0].Combi;
ReportParameter pDatatestare = new ReportParameter("datatestare", datatestare);
ReportParameter pDatatestare2 = new ReportParameter("datatestare2", datatestare2);
ReportParameter pCombi = new ReportParameter("combi", combi);
ReportParameter pFabricanume = new ReportParameter("fabricanume", fabricaname);
ReportParameter pFabricastrada = new ReportParameter("fabricastrada", fabricastrada);
ReportParameter pFabricanumar = new ReportParameter("fabricanumar", fabricanumar);
ReportParameter pFabricaoras = new ReportParameter("fabricaoras", fabricaoras);
ReportParameter pFabricajudet = new ReportParameter("fabricajudet", fabricajudet);
ReportParameter pLaborator = new ReportParameter("laborator", laborator);
ReportParameter pResponsabil = new ReportParameter("responsabil", responsabil);
ReportParameter pVersiune = new ReportParameter("Versiune", new SettingManager(StaticDataHelper.FCCLDbContext).GetValueByName("ReportFabricaIntre4"));
ReportParameter[] p = { pDatatestare, pDatatestare2, pCombi, pFabricanume, pFabricastrada, pFabricanumar, pFabricaoras, pFabricajudet, pLaborator, pResponsabil, pVersiune };
report.SetParameters(p);
file = report.Render("PDF", deviceInfo, out mimeType, out encoding, out fileExtension, out streams, out warnings);
string httppath = StaticDataHelper.SettingsManager.CaleRapoarteHttp;
string filepath = StaticDataHelper.SettingsManager.CaleRapoarte;
fabricaname = replace_special_car_null(fabricaname);
string raport_name = "RaportFabrica" + datatestare.Replace("/", "_") + "_" + datatestare2.Replace("/", "_") + "_" + fabricaid + "-" + fabricaname + ".pdf";
string raport_excel = "RaportFabrica" + datatestare.Replace("/", "_") + "_" + datatestare2.Replace("/", "_") + "_" + fabricaid + "-" + fabricaname + ".xls";
string path_raport_http = "http://" + Request.ServerVariables.Get("HTTP_HOST") + "/" + httppath;
string pdf_link = path_raport_http + @"Fabrici/" + raport_name;
string pdf_file = filepath + @"Fabrici/" + raport_name;
// writefile
string excel_link = path_raport_http + @"Fabrici/" + raport_excel;
string excel_file = filepath + @"Fabrici/" + raport_excel;
report.SetParameters(new ReportParameter("BkImage", ""));
File.WriteAllBytes(pdf_file, file);
byte[] file_xls = report.Render("EXCEL", deviceInfoXls, out mimeType, out encoding, out fileExtension, out streams, out warnings);
File.WriteAllBytes(excel_file, file_xls);
pdflink.Visible = true;
pdflink.NavigateUrl = pdf_link;
pdflink.Text = raport_name;
xlslink.Visible = true;
xlslink.NavigateUrl = excel_link;
xlslink.Text = raport_excel;
}
protected void FormView1_ItemUpdating(object sender, FormViewUpdateEventArgs e)
{
SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["fccl2ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
var mostra = new MostreFabrica();
mostra.CodBare = NullifyEmptyValues(e.NewValues["CodBare"]);
mostra.PrelevatoriId = NullifyEmptyValues(e.NewValues["PrelevatoriId"]);
mostra.Rasa = NullifyEmptyValues(e.NewValues["Rasa"]);
mostra.IdZilnic = NullifyEmptyValues(e.NewValues["IdZilnic"]);
DateTime datatestare;
DateTime datatestarefin;
DateTime dataprimirii;
DateTime dataprelevare;
if (!DateTime.TryParse(e.NewValues["DataTestare"].ToString().Trim(), out datatestare))
{
Label1.Text = "Data testarii invalida!";
e.Cancel = true;
return;
}
mostra.DataTestare = datatestare;
if (!DateTime.TryParse(e.NewValues["DataTestareFinala"].ToString().Trim(), out datatestarefin))
{
Label1.Text = "Data testarii finale invalida!";
e.Cancel = true;
return;
}
mostra.DataTestareFinala = datatestarefin;
if (!DateTime.TryParse(e.NewValues["DataPrimirii"].ToString().Trim(), out dataprimirii))
{
Label1.Text = "Data primirii invalida!";
e.Cancel = true;
return;
}
mostra.DataPrimirii = dataprimirii;
if (!DateTime.TryParse(e.NewValues["DataPrelevare"].ToString().Trim(), out dataprelevare))
{
Label1.Text = "Data prelevare invalida!";
e.Cancel = true;
return;
}
mostra.DataPrelevare = dataprelevare;
// verific cod ferma
if (mostra.PrelevatoriId != "0")
{
cmd.CommandText = "SELECT * from FERME_CCL WHERE Cod ='" + mostra.CodBare.Substring(0, 5) + "'";
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (!reader.Read())
{
Label1.Text = "Codul de ferma " + mostra.CodBare.Substring(0, 5) + " nu exista!";
e.Cancel = true;
reader.Close();
cnn.Close();
return;
}
else
{
Label1.Text = "";
reader.Close();
cnn.Close();
}
}
// verific id zilnic
if (mostra.IdZilnic != "")
{
cmd.CommandText = "SELECT * from MostreTancuri Where IdZilnic =" + mostra.IdZilnic + " AND DataTestare = CONVERT(date, '" + mostra.DataTestare.ToShortDateString() + "', 103)";
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
int count=0;
while (reader.Read())
count++;
if (count >1)
{
reader.Close();
//get max id
cmd.CommandText = "SELECT MAX(IdZilnic) AS MaxId from MostreTancuri Where DataTestare = CONVERT(date, '" + mostra.DataTestare.ToShortDateString() + "', 103)";
reader = cmd.ExecuteReader();
int idzilnic = 1;
if (reader.Read())
if (!reader.IsDBNull(0))
idzilnic = Convert.ToInt32(reader["MaxId"]) + 1;
e.NewValues["IdZilnic"] = Convert.ToString(idzilnic);
reader.Close();
cnn.Close();
}
}
else
{
cmd.CommandText = "SELECT MAX(IdZilnic) AS MaxId from MostreTancuri Where DataTestare=CONVERT(date, '" + mostra.DataTestare.ToShortDateString() + "', 103)";
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
int idzilnic = 1;
if (reader.Read())
if (!reader.IsDBNull(0))
idzilnic = Convert.ToInt32(reader["MaxId"]) + 1;
e.NewValues["IdZilnic"] = Convert.ToString(idzilnic);
reader.Close();
cnn.Close();
}
//verific fcb
mostra.NrComanda = NullifyEmptyValues(e.NewValues["NrComanda"]);
mostra.NumePrelevator = NullifyEmptyValues(e.NewValues["PrelevatoriNume"]);
mostra.FabriciNume = NullifyEmptyValues(e.NewValues["FabriciNume"]);
mostra.FabriciStrada = NullifyEmptyValues(e.NewValues["FabriciStrada"]);
mostra.FabriciOras = NullifyEmptyValues(e.NewValues["FabriciOras"]);
mostra.FabriciJudet = NullifyEmptyValues(e.NewValues["FabriciJudet"]);
mostra.NumeProba = NullifyEmptyValues(e.NewValues["NumeProba"]);
if (mostra.PrelevatoriId != "0" && (mostra.NumePrelevator != "" || mostra.FabriciNume != ""
|| mostra.FabriciStrada != "" || mostra.FabriciOras != "" || mostra.FabriciJudet != "" || mostra.NumeProba != ""))
{
Label1.Text = "Au fost completate simultan date pt. FCB si Cod Bare!";
e.Cancel = true;
return;
}
else
Label1.Text = "";
if ((mostra.PrelevatoriId == null || mostra.PrelevatoriId.Equals("0")) && (mostra.NumePrelevator == null || mostra.NumePrelevator.Equals("")))
{
Label1.Text = "Nu a fost completat prelevatorul!";
e.Cancel = true;
return;
}
Label1.Text = "";
mostra.Grasime = NullifyEmptyValues(e.NewValues["Grasime"]);
mostra.Proteina = NullifyEmptyValues(e.NewValues["Proteina"]);
mostra.Lactoza = NullifyEmptyValues(e.NewValues["Lactoza"]);
mostra.Casein = NullifyEmptyValues(e.NewValues["Casein"]);
mostra.Substu = NullifyEmptyValues(e.NewValues["Substu"]);
mostra.NCS = NullifyEmptyValues(e.NewValues["NCS"]);
mostra.Urea = NullifyEmptyValues(e.NewValues["Urea"]);
mostra.Ph = NullifyEmptyValues(e.NewValues["Ph"]);
mostra.Id = NullifyEmptyValues(e.NewValues["Id"]);
mostra.Definitiv = e.NewValues["Definitiv"].ToString().Trim() == "True";
mostra.Validat = e.NewValues["Validat"].ToString().Trim() == "True";
mostra.Sentsms = false;
mostra.UpdateMostra(mostra);
}
protected void Button1_Click(object sender, EventArgs e)
{
string adresafizicaserver = Server.MapPath("~");
string cale_rap = Server.MapPath("~/Documents/Sms/");
string cale_log = Server.MapPath("~/Documents/Sms/");
string logname = "LogSmsFerme" + DataSelectie.Text.Trim().Replace(@"/", @"_") + ".txt";
string smsname = "SmsTrimise" + DataSelectie.Text.Trim().Replace(@"/", @"_") + ".txt";
string fulllogname = adresafizicaserver + @"\Documents\Sms\" + logname;
string fullsmsname = adresafizicaserver + @"\Documents\Sms\" + smsname;
DateTime datatestare = DateTime.Parse(DataSelectie.Text);
MostreDB mostre = new MostreDB();
List <MostreFabrica> mostresms = MostreFabrica.GetMostreSMS(datatestare);
SerialPort port = new SerialPort();
clsSMS objclsSMS = new clsSMS();
string portname = ConfigurationManager.AppSettings["ComPort"].ToString();
string baudrate = "9600";
port = objclsSMS.OpenPort(portname, baudrate);
StringBuilder updateSql = new StringBuilder("update mostretancuri set SentSms=1 where ");
int countSms = 0;
int countError = 0;
mostre.Addtext(adresafizicaserver + @"\Documents\Sms\" + logname, "************" + "Data: " + DateTime.Now.ToString() + "**********");
mostre.Addtext(adresafizicaserver + @"\Documents\Sms\" + smsname, "************" + "Data: " + DateTime.Now.ToString() + "**********");
int counter = 0;
foreach (MostreFabrica ms in mostresms)
{
// text sms
//TODO : uncomment !!!
if (!string.IsNullOrEmpty(ms.TelContact))
{
if (ms.FermeNume.Length > 30)
{
ms.FermeNume = ms.FermeNume.Substring(0, 30);
}
// if (string.IsNullOrEmpty(ms.TelContact))
// ms.TelContact = "+40722217495";
StringBuilder sms = new StringBuilder();
// sms.Append(ms.FermeNume + " analizele sunt incarcate pe site");
sms.Append("In " + datatestare + " contul dvs. " + ms.FermeNume + " a fost actualizat cu rezultatele analizelor");
string cellnumber = Regex.Replace(ms.TelContact, @"[\s()-]", "");
cellnumber = (cellnumber.StartsWith("0") ? "+4" + cellnumber : cellnumber);
try
{
//todo uncomment
bool res = objclsSMS.sendMsg(port, portname, "9600", cellnumber, sms.ToString());
// bool res = true;
if (res == true)
{
string cond = "(FermaID = " + ms.FermeId + " AND DataTestare = CONVERT(date, ''" + datatestare.ToShortDateString() + "', 103))";
string strupdate = (countSms > 0) ? " or " + cond : cond;
updateSql.Append(strupdate);
countSms++;
mostre.Addtext(adresafizicaserver + @"\Documents\Sms\" + smsname, countSms + ". " + ms.CodFerma + " " + ms.FermeNume + " " + cellnumber + " - " + DateTime.Now.ToString());
mostre.Addtext(adresafizicaserver + @"\Documents\Sms\" + smsname, sms.ToString());
}
else
{
countError++;
mostre.Addtext(adresafizicaserver + @"\Documents\Sms\" + logname, countError + ". " + ms.CodFerma + " " + ms.FermeNume + " " + cellnumber + " - " + DateTime.Now.ToString());
mostre.Addtext(adresafizicaserver + @"\Documents\Sms\" + logname, sms.ToString());
}
}
catch (Exception ex)
{
mostre.Addtext(adresafizicaserver + @"\Documents\Sms\" + logname, ex.Message + " - " + ex.StackTrace);
}
counter++;
}
}
try
{
objclsSMS.ClosePort(port);
}
catch { }
mostre.Addtext(adresafizicaserver + @"\Documents\Sms\" + logname, "************" + "End Data: " + DateTime.Now + "**********");
mostre.Addtext(adresafizicaserver + @"\Documents\Sms\" + smsname, "************" + "End Data: " + DateTime.Now + "**********");
// update moste tancuri set sentsms=1
SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["fccl2ConnectionString"].ConnectionString);
try
{
if (countSms > 0)
{
SqlCommand cmd = new SqlCommand(updateSql.ToString(), cnn);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
}
catch (Exception exU)
{
Label1.Text = " Update MostreTancuri failed! " + exU.Message;
}
fissms.NavigateUrl = HttpContext.Current.Request.ApplicationPath + @"/Documents/Sms/" + smsname;
fissms.Visible = true;
fislog.NavigateUrl = HttpContext.Current.Request.ApplicationPath + @"/Documents/Sms/" + logname;
fislog.Visible = true;
}
public static List<MostreFabrica> GetMostreFerma
(string fabricaid, string codferma, string datatestare)
{
SqlConnection cnn = new SqlConnection(
ConfigurationManager.ConnectionStrings
["fccl2ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandTimeout = 300;
cmd.CommandText = "SELECT Ferme_CCL.ID, Ferme_CCL.Nume,Ferme_CCL.FabricaID,Ferme_CCL.PersonaDeContact, MostreTancuri.DataPrelevare, MostreTancuri.DataPrimirii, MostreTancuri.CodBare," +
"MostreTancuri.Grasime, MostreTancuri.ProcentProteine,MostreTancuri.ProcentLactoza, MostreTancuri.SubstantaUscata, MostreTancuri.PunctInghet, MostreTancuri.Antibiotice," +
"MostreTancuri.NumarCeluleSomatice, MostreTancuri.IncarcaturaGermeni, MostreTancuri.PH,MostreTancuri.Urea,MostreTancuri.GrasimeProv,MostreTancuri.Caseina, MostreTancuri.PrelevatorID," +
"MostreTancuri.DataTestare,MostreTancuri.DataTestareFinala,MostreTancuri.NrComanda " +
"FROM Ferme_CCL, MostreTancuri WHERE"
+ " CONVERT(datetime, MostreTancuri.DataTestareFinala, 103) = CONVERT(datetime, '" + datatestare + "', 103) "
+ " AND MostreTancuri.CodFerma = Ferme_CCL.Cod "
+ " AND Ferme_CCL.FabricaID = " + fabricaid
+ " AND Ferme_CCL.Cod = " + codferma
+ " AND MostreTancuri.Validat = 1 ORDER BY Ferme_CCL.Nume";
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
List<MostreFabrica> list = new List<MostreFabrica>();
while (reader.Read())
{
MostreFabrica mf = new MostreFabrica();
mf.CodBare = Convert.ToString(reader["CodBare"]);
mf.DataTestare = Convert.ToString(reader["DataTestare"]);
mf.DataTestareFinala = Convert.ToString(reader["DataTestareFinala"]);
mf.DataPrimirii = ((DateTime)reader["DataPrimirii"]).ToString("dd/MM/yyyy");
mf.DataPrelevare = Convert.ToString(reader["DataPrelevare"]);
mf.Grasime = (Convert.ToDouble(Convert.ToString(reader["Grasime"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["Grasime"])) == 0.00001) ? "0" : Convert.ToString(reader["Grasime"]));
mf.Proteina = (Convert.ToDouble(Convert.ToString(reader["ProcentProteine"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["ProcentProteine"])) == 0.00001) ? "0" : Convert.ToString(reader["ProcentProteine"]));
mf.Lactoza = (Convert.ToDouble(Convert.ToString(reader["ProcentLactoza"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["ProcentLactoza"])) == 0.00001) ? "0" : Convert.ToString(reader["ProcentLactoza"]));
mf.Substu = (Convert.ToDouble(Convert.ToString(reader["SubstantaUscata"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["SubstantaUscata"])) == 0.00001) ? "0" : Convert.ToString(reader["SubstantaUscata"]));
mf.NCS = (Convert.ToDouble(Convert.ToString(reader["NumarCeluleSomatice"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["NumarCeluleSomatice"])) == 0.00001) ? "0" : Convert.ToString(reader["NumarCeluleSomatice"]));
mf.NTG = (Convert.ToDouble(Convert.ToString(reader["IncarcaturaGermeni"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["IncarcaturaGermeni"])) == 0.00001) ? "0" : Convert.ToString(reader["IncarcaturaGermeni"]));
if (mf.NTG != "-" && Double.Parse(mf.NTG) >= 10000)
mf.NTG = ">10000";
mf.Puncti = Convert.ToString(reader["PunctInghet"]);
mf.Apa = "-";
if (Convert.ToDouble(mf.Puncti) == 0)
mf.Puncti = "-";
else if (Convert.ToDouble(mf.Puncti) == 0.00001)
mf.Puncti = "0";
if (!mf.Puncti.Equals("-"))
{
mf.Apa = UMostre.ApaAdaugata(mf.Puncti);
mf.Puncti = "-0." + mf.Puncti;
}
mf.Antib = Convert.ToString(reader["Antibiotice"]);
if (mf.Antib != "")
{
if (mf.Antib == "0")
mf.Antib = "Negativ";
else
mf.Antib = "Pozitiv";
}
else
mf.Antib = "-";
//
mf.Urea = (Convert.ToDouble(Convert.ToString(reader["Urea"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["Urea"])) == 0.00001) ? "0" : Convert.ToString(reader["Urea"]));
mf.Ph = (Convert.ToDouble(Convert.ToString(reader["PH"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["PH"])) == 0.00001) ? "0" : Convert.ToString(reader["PH"]));
mf.Combi = Convert.ToString(reader["GrasimeProv"]);
mf.Casein = (Convert.ToDouble(Convert.ToString(reader["Caseina"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["Caseina"])) == 0.00001) ? "0" : Convert.ToString(reader["Caseina"]));
mf.PrelevatoriId = Convert.ToString(reader["PrelevatorID"]);
// mf.PrelevatoriNume = Convert.ToString(reader["NumePrelevator"]);
//get prelevator name
mf.PrelevatoriNume = Convert.ToString(reader["PersonaDeContact"]);
if (!mf.PrelevatoriId.Equals(mf.CodBare.Substring(0, 5)))
{
SqlConnection cnnf = new SqlConnection(
ConfigurationManager.ConnectionStrings
["fccl2ConnectionString"].ConnectionString);
SqlCommand cmdf = new SqlCommand();
cmdf.Connection = cnnf;
cmdf.CommandText = "SELECT * FROM Prelevatori WHERE CodPrelevator =" + mf.PrelevatoriId;
cnnf.Open();
SqlDataReader drFerme = cmdf.ExecuteReader();
if (drFerme.Read())
{
mf.PrelevatoriNume = Convert.ToString(drFerme["NumePrelevator"]);
}
drFerme.Close();
cnnf.Close();
}
mf.FermeNume = Convert.ToString(reader["Nume"]);
list.Add(mf);
}
cnn.Close();
return list;
}
//get mostra
public static List<MostreFabrica> GetMostra
(string id)
{
SqlConnection cnn = new SqlConnection(
ConfigurationManager.ConnectionStrings
["fccl2ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandTimeout = 180;
cmd.CommandText = "SELECT * FROM MostreTancuri WHERE " +
//"CodBare ='" + codbare + "'";
"ID =" + id;
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
List<MostreFabrica> list = new List<MostreFabrica>();
while (reader.Read())
{
MostreFabrica mf = new MostreFabrica();
mf.Id = Convert.ToString(reader["ID"]);
mf.CodBare = Convert.ToString(reader["CodBare"]);
mf.NrComanda = Convert.ToString(reader["NrComanda"]);
mf.DataTestare = Convert.ToString(reader["DataTestare"]);
mf.DataTestareFinala = Convert.ToString(reader["DataTestareFinala"]);
mf.DataPrimirii = ((DateTime)reader["DataPrimirii"]).ToString("dd/MM/yyyy");
//mf.DataPrimirii = (DateTime)Convert.ToString(reader["DataPrimirii"]);
mf.DataPrelevare = Convert.ToString(reader["DataPrelevare"]);
mf.Grasime = (Convert.ToDouble(Convert.ToString(reader["Grasime"])) == 0 ? "" : (Convert.ToDouble(Convert.ToString(reader["Grasime"])) == 0.00001) ? "0" : Convert.ToString(reader["Grasime"]));
mf.Proteina = (Convert.ToDouble(Convert.ToString(reader["ProcentProteine"])) == 0 ? "" : (Convert.ToDouble(Convert.ToString(reader["ProcentProteine"])) == 0.00001) ? "0" : Convert.ToString(reader["ProcentProteine"]));
mf.Lactoza = (Convert.ToDouble(Convert.ToString(reader["ProcentLactoza"])) == 0 ? "" : (Convert.ToDouble(Convert.ToString(reader["ProcentLactoza"])) == 0.00001) ? "0" : Convert.ToString(reader["ProcentLactoza"]));
mf.Substu = (Convert.ToDouble(Convert.ToString(reader["SubstantaUscata"])) == 0 ? "" : (Convert.ToDouble(Convert.ToString(reader["SubstantaUscata"])) == 0.00001) ? "0" : Convert.ToString(reader["SubstantaUscata"]));
mf.Urea = (Convert.ToDouble(Convert.ToString(reader["Urea"])) == 0 ? "" : (Convert.ToDouble(Convert.ToString(reader["Urea"])) == 0.00001) ? "0" : Convert.ToString(reader["Urea"]));
mf.Ph = (Convert.ToDouble(Convert.ToString(reader["PH"])) == 0 ? "" : (Convert.ToDouble(Convert.ToString(reader["PH"])) == 0.00001) ? "0" : Convert.ToString(reader["PH"]));
mf.NCS = (Convert.ToDouble(Convert.ToString(reader["NumarCeluleSomatice"])) == 0 ? "" : (Convert.ToDouble(Convert.ToString(reader["NumarCeluleSomatice"])) == 0.00001) ? "0" : Convert.ToString(reader["NumarCeluleSomatice"]));
mf.NTG = (Convert.ToDouble(Convert.ToString(reader["IncarcaturaGermeni"])) == 0 ? "" : (Convert.ToDouble(Convert.ToString(reader["IncarcaturaGermeni"])) == 0.00001) ? "0" : Convert.ToString(reader["IncarcaturaGermeni"]));
mf.Casein = (Convert.ToDouble(Convert.ToString(reader["Caseina"])) == 0 ? "" : (Convert.ToDouble(Convert.ToString(reader["Caseina"])) == 0.00001) ? "0" : Convert.ToString(reader["Caseina"]));
mf.Puncti = Convert.ToString(reader["PunctInghet"]);
mf.Apa = "-";
if (Convert.ToDouble(mf.Puncti) == 0)
mf.Puncti = "";
else if (Convert.ToDouble(mf.Puncti) == 0.00001)
mf.Puncti = "0";
if (!mf.Puncti.Equals(""))
{
mf.Apa = UMostre.ApaAdaugata(mf.Puncti);
mf.Puncti = "-0." + mf.Puncti;
}
mf.Antib = Convert.ToString(reader["Antibiotice"]);
if (mf.Antib == "-1")
{
mf.Antib = "1";
}
mf.PrelevatoriId = Convert.ToString(reader["PrelevatorID"]);
mf.PrelevatoriNume = Convert.ToString(reader["NumePrelevator"]);
mf.Cant = Convert.ToString(reader["CantitateLaPrelevare"]);
mf.NrComanda = Convert.ToString(reader["NrComanda"]);
mf.NumeProba = Convert.ToString(reader["NumeProba"]);
mf.IdZilnic = Convert.ToString(reader["IdZilnic"]);
mf.Definitiv = Convert.ToBoolean(reader["Definitiv"]);
mf.Validat = Convert.ToBoolean(reader["Validat"]);
mf.Sentsms = Convert.ToBoolean(reader["SentSms"]);
mf.FabriciNume = Convert.ToString(reader["NumeClient"]);
mf.FabriciOras = Convert.ToString(reader["Localitate"]);
mf.FabriciJudet = Convert.ToString(reader["Judet"]);
mf.FabriciStrada = Convert.ToString(reader["AdresaClient"]);
list.Add(mf);
}
cnn.Close();
return list;
}
public static List<MostreFabrica> GetMostreFabrica
(string fabricaid, string datatestare)
{
SqlConnection cnn = new SqlConnection(
ConfigurationManager.ConnectionStrings
["fccl2ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandTimeout = 180;
cmd.CommandText = "SELECT Ferme_CCL.ID, Ferme_CCL.Nume,Ferme_CCL.FabricaID,Ferme_CCL.Cod,Ferme_CCL.Strada,Ferme_CCL.Numar,Ferme_CCL.Oras,Ferme_CCL.PersonaDeContact,Ferme_CCL.TelPersoanaContact,Ferme_CCL.Ferme_CCL,Ferme_CCL.FermierID,Judete.Denloc, "
+ "MostreTancuri.DataPrelevare, MostreTancuri.DataPrimirii, MostreTancuri.CodBare," +
"MostreTancuri.Grasime, MostreTancuri.ProcentProteine,MostreTancuri.ProcentLactoza, MostreTancuri.SubstantaUscata, MostreTancuri.PunctInghet, MostreTancuri.Antibiotice," +
"MostreTancuri.NumarCeluleSomatice, MostreTancuri.IncarcaturaGermeni,MostreTancuri.PH,MostreTancuri.Urea,MostreTancuri.Caseina,MostreTancuri.GrasimeProv, MostreTancuri.PrelevatorID," +
"MostreTancuri.DataTestare,MostreTancuri.DataTestareFinala,MostreTancuri.NrComanda " +
"FROM Ferme_CCL, Judete, MostreTancuri WHERE"
+ " CONVERT(datetime, MostreTancuri.DataTestareFinala, 103) = CONVERT(datetime, '" + datatestare + "', 103) "
+ " AND MostreTancuri.CodFerma = Ferme_CCL.Cod "
+ " AND Convert(int,Ferme_CCL.Judet,2)=Judete.ID "
+ " AND Ferme_CCL.FabricaID = " + fabricaid
+ " AND MostreTancuri.Validat = 1 ORDER BY Ferme_CCL.Nume";
/*
SqlParameter f=new SqlParameter("@fabricaid",SqlDbType.Int);
f.Value = fabricaid;
cmd.Parameters.Add(f);
SqlParameter d = new SqlParameter("@datatestare", SqlDbType.NVarChar);
d.Value = datatestare;
cmd.Parameters.Add(d);
*/
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
List<MostreFabrica> list = new List<MostreFabrica>();
int pos = 0;
string ferma = "";
while (reader.Read())
{
MostreFabrica mf = new MostreFabrica();
mf.CodBare = Convert.ToString(reader["CodBare"]);
mf.NrComanda = Convert.ToString(reader["NrComanda"]);
mf.DataTestare = Convert.ToString(reader["DataTestare"]);
mf.DataTestareFinala = Convert.ToString(reader["DataTestareFinala"]);
mf.DataPrimirii = ((DateTime)reader["DataPrimirii"]).ToString("dd/MM/yyyy");
//mf.DataPrimirii = (DateTime)Convert.ToString(reader["DataPrimirii"]);
mf.DataPrelevare = Convert.ToString(reader["DataPrelevare"]);
mf.Grasime = (Convert.ToDouble(Convert.ToString(reader["Grasime"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["Grasime"])) == 0.00001) ? "0" : Convert.ToString(reader["Grasime"]));
mf.Proteina = (Convert.ToDouble(Convert.ToString(reader["ProcentProteine"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["ProcentProteine"])) == 0.00001) ? "0" : Convert.ToString(reader["ProcentProteine"]));
mf.Lactoza = (Convert.ToDouble(Convert.ToString(reader["ProcentLactoza"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["ProcentLactoza"])) == 0.00001) ? "0" : Convert.ToString(reader["ProcentLactoza"]));
mf.Substu = (Convert.ToDouble(Convert.ToString(reader["SubstantaUscata"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["SubstantaUscata"])) == 0.00001) ? "0" : Convert.ToString(reader["SubstantaUscata"]));
mf.NCS = (Convert.ToDouble(Convert.ToString(reader["NumarCeluleSomatice"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["NumarCeluleSomatice"])) == 0.00001) ? "0" : Convert.ToString(reader["NumarCeluleSomatice"]));
mf.NTG = (Convert.ToDouble(Convert.ToString(reader["IncarcaturaGermeni"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["IncarcaturaGermeni"])) == 0.00001) ? "0" : Convert.ToString(reader["IncarcaturaGermeni"]));
if (mf.NTG != "-" && Double.Parse(mf.NTG) >= 10000)
mf.NTG = ">10000";
mf.Puncti = Convert.ToString(reader["PunctInghet"]);
mf.Apa = "-";
if (Convert.ToDouble(mf.Puncti) == 0)
mf.Puncti = "-";
else if (Convert.ToDouble(mf.Puncti) == 0.00001)
mf.Puncti = "0";
if (!mf.Puncti.Equals("-"))
{
mf.Apa = UMostre.ApaAdaugata(mf.Puncti);
mf.Puncti = "-0." + mf.Puncti;
}
mf.Antib = Convert.ToString(reader["Antibiotice"]);
if (mf.Antib != "")
{
if (mf.Antib == "0")
mf.Antib = "Negativ";
else
mf.Antib = "Pozitiv";
}
else
mf.Antib = "-";
/*
mf.Grasime = (Convert.ToString(reader["Grasime"])=="0") ? "": Convert.ToString(reader["Grasime"]);
mf.Proteina = (Convert.ToString(reader["ProcentProteine"]) == "0") ? "" : Convert.ToString(reader["ProcentProteine"]);
mf.Lactoza = (Convert.ToString(reader["ProcentLactoza"]) == "0") ? "" : Convert.ToString(reader["ProcentLactoza"]);
mf.Substu = (Convert.ToString(reader["SubstantaUscata"]) == "0") ? "" : Convert.ToString(reader["SubstantaUscata"]);
mf.NCS = (Convert.ToString(reader["NumarCeluleSomatice"]) == "0") ? "" : Convert.ToString(reader["NumarCeluleSomatice"]);
mf.NTG = (Convert.ToString(reader["IncarcaturaGermeni"]) == "0") ? "" : Convert.ToString(reader["IncarcaturaGermeni"]);
mf.Puncti = Convert.ToString(reader["PunctInghet"]);
mf.Apa = "";
if (Convert.ToDouble(mf.Puncti) == 0)
mf.Puncti = "";
else
{
mf.Apa = UMostre.ApaAdaugata(mf.Puncti);
mf.Puncti = "-0." + mf.Puncti;
}
mf.Antib = Convert.ToString(reader["Antibiotice"]);
if (mf.Antib != "")
{
if (mf.Antib == "0")
mf.Antib = "Negativ";
else
mf.Antib = "Pozitiv";
}
*/
mf.Urea = (Convert.ToDouble(Convert.ToString(reader["Urea"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["Urea"])) == 0.00001) ? "0" : Convert.ToString(reader["Urea"]));
mf.Ph = (Convert.ToDouble(Convert.ToString(reader["PH"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["PH"])) == 0.00001) ? "0" : Convert.ToString(reader["PH"]));
mf.Combi = Convert.ToString(reader["GrasimeProv"]);
mf.Casein = (Convert.ToDouble(Convert.ToString(reader["Caseina"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["Caseina"])) == 0.00001) ? "0" : Convert.ToString(reader["Caseina"]));
mf.PrelevatoriId = Convert.ToString(reader["PrelevatorID"]);
//get prelevator name
mf.PrelevatoriNume = Convert.ToString(reader["PersonaDeContact"]);
if (!mf.PrelevatoriId.Equals(mf.CodBare.Substring(0, 5)))
{
SqlConnection cnnf = new SqlConnection(
ConfigurationManager.ConnectionStrings
["fccl2ConnectionString"].ConnectionString);
SqlCommand cmdf = new SqlCommand();
cmdf.Connection = cnnf;
cmdf.CommandText = "SELECT * FROM Prelevatori WHERE CodPrelevator =" + mf.PrelevatoriId;
cnnf.Open();
SqlDataReader drFerme = cmdf.ExecuteReader();
if (drFerme.Read())
{
mf.PrelevatoriNume = Convert.ToString(drFerme["NumePrelevator"]);
}
drFerme.Close();
cnnf.Close();
}
mf.FermeNume = Convert.ToString(reader["Nume"]);
mf.CodFerma = Convert.ToString(reader["Cod"]);
if (!ferma.Equals(mf.FermeNume))
{
pos++;
}
mf.Nrf = pos;
ferma = mf.FermeNume;
mf.StradaFerma = Convert.ToString(reader["Strada"]);
mf.NumarFerma = Convert.ToString(reader["Numar"]);
mf.OrasFerma = Convert.ToString(reader["Oras"]);
mf.JudetFerma = Convert.ToString(reader["Denloc"]);
mf.PersContact = Convert.ToString(reader["PersonaDeContact"]);
mf.TelContact = Convert.ToString(reader["TelPersoanaContact"]);
string fermeccl = Convert.ToString(reader["Ferme_CCL"]);
string fermierid = Convert.ToString(reader["FermierID"]);
/*
if (!fermeccl.Equals("C") && fermierid !="" && fermierid !=null)
{
SqlConnection cnnf = new SqlConnection(
ConfigurationManager.ConnectionStrings
["fccl2ConnectionString"].ConnectionString);
SqlCommand cmdf = new SqlCommand();
cmdf.Connection = cnnf;
cmdf.CommandText = "SELECT * FROM Fermier WHERE Fermier.FermierID =" + fermierid;
cnnf.Open();
SqlDataReader drFerme = cmdf.ExecuteReader();
if (drFerme.Read())
{
mf.PersContact = Convert.ToString(drFerme["Nume"]);
mf.TelContact = Convert.ToString(drFerme["Telefon"]);
}
drFerme.Close();
cnnf.Close();
}
*/
list.Add(mf);
}
cnn.Close();
return list;
}
public static List<MostreFabrica> GetMostreFabrica
(string fabricaid, string datatestare, string datatestare2)
{
SqlConnection cnn = new SqlConnection(
ConfigurationManager.ConnectionStrings
["fccl2ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
string query_date = " MostreTancuri.DataTestareFinala = CONVERT(date, '" + datatestare + "', 103) ";
if (datatestare2 != "")
{
query_date = " (MostreTancuri.DataTestareFinala >= CONVERT(date, '" + datatestare + "', 103))";
query_date += " AND MostreTancuri.DataTestareFinala <= CONVERT(date, '" + datatestare2 + "', 103))";
}
cmd.CommandText = "SELECT Ferme_CCL.ID, Ferme_CCL.Nume,Ferme_CCL.FabricaID,Ferme_CCL.Cod,Ferme_CCL.Strada,Ferme_CCL.Numar,Ferme_CCL.Oras,Ferme_CCL.PersonaDeContact,Ferme_CCL.TelPersoanaContact,Judete.Denloc, "
+ "MostreTancuri.DataPrelevare, MostreTancuri.DataPrimirii, MostreTancuri.CodBare," +
"MostreTancuri.Grasime, MostreTancuri.ProcentProteine,MostreTancuri.ProcentLactoza, MostreTancuri.SubstantaUscata, MostreTancuri.PunctInghet, MostreTancuri.Antibiotice," +
"MostreTancuri.NumarCeluleSomatice, MostreTancuri.IncarcaturaGermeni,MostreTancuri.PH,MostreTancuri.Urea,MostreTancuri.GrasimeProv,MostreTancuri.Caseina, MostreTancuri.PrelevatorID," +
"MostreTancuri.DataTestare,MostreTancuri.DataTestareFinala,MostreTancuri.NrComanda " +
"FROM Ferme_CCL, Judete, MostreTancuri WHERE"
+ query_date
+ " AND MostreTancuri.CodFerma = Ferme_CCL.Cod "
+ " AND Convert(int,Ferme_CCL.Judet,2)=Judete.ID "
+ " AND Ferme_CCL.FabricaID = " + fabricaid
+ " AND MostreTancuri.Validat = 1 ORDER BY Ferme_CCL.Nume";
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
List<MostreFabrica> list = new List<MostreFabrica>();
int pos = 0;
string ferma = "";
while (reader.Read())
{
MostreFabrica mf = new MostreFabrica();
mf.CodBare = Convert.ToString(reader["CodBare"]);
mf.DataTestare = Convert.ToDateTime(reader["DataTestare"]);
mf.DataTestareFinala = Convert.ToDateTime(reader["DataTestareFinala"]);
mf.DataPrimirii = Convert.ToDateTime(reader["DataPrimirii"]);
//mf.DataPrimirii = (DateTime)Convert.ToString(reader["DataPrimirii"]);
mf.DataPrelevare = Convert.ToDateTime(reader["DataPrelevare"]);
mf.Grasime = Utils.NormalizeForDisplay(reader["Grasime"], "-");
mf.Proteina = Utils.NormalizeForDisplay(reader["ProcentProteine"], "-");
mf.Lactoza = Utils.NormalizeForDisplay(reader["ProcentLactoza"], "-");
mf.Substu = Utils.NormalizeForDisplay(reader["SubstantaUscata"], "-");
mf.NCS = Utils.NormalizeForDisplay(reader["NumarCeluleSomatice"], "-");
mf.NTG = Utils.NormalizeForDisplay(reader["IncarcaturaGermeni"], "-");
mf.Puncti = Utils.NormalizeForDisplay(reader["PunctInghet"], "-");
double ntg = 0;
if (mf.NTG != "-" && (double.TryParse(Convert.ToString(ntg), NumberStyles.Any, CultureInfo.CurrentCulture, out ntg) ||
double.TryParse(Convert.ToString(ntg), NumberStyles.Any, CultureInfo.InvariantCulture, out ntg)))
{
if (ntg >= 10000) mf.NTG = ">10000";
}
// mf.NTG = (Convert.ToString(reader["IncarcaturaGermeni"]) == "0") ? "" : Convert.ToString(reader["IncarcaturaGermeni"]);
mf.Apa = "-";
if (!mf.Puncti.Equals("-"))
{
mf.Apa = UMostre.ApaAdaugata(mf.Puncti);
mf.Puncti = "-0." + mf.Puncti;
}
mf.Antib = Convert.ToString(reader["Antibiotice"]);
if (mf.Antib != "")
{
if (mf.Antib == "0")
mf.Antib = "Negativ";
else
mf.Antib = "Pozitiv";
}
else
mf.Antib = "-";
mf.Urea = Utils.NormalizeForDisplay(reader["Urea"], "-");
mf.Ph = Utils.NormalizeForDisplay(reader["PH"], "-");
mf.Combi = Convert.ToString(reader["GrasimeProv"]);
mf.Casein = Utils.NormalizeForDisplay(reader["Caseina"], "-");
mf.PrelevatoriId = Convert.ToString(reader["PrelevatorID"]);
// mf.PrelevatoriNume = Convert.ToString(reader["NumePrelevator"]);
//get prelevator name
mf.PrelevatoriNume = Convert.ToString(reader["PersonaDeContact"]);
if (!mf.PrelevatoriId.Equals(mf.CodBare.Substring(0, 5)))
{
SqlConnection cnnf = new SqlConnection(
ConfigurationManager.ConnectionStrings
["fccl2ConnectionString"].ConnectionString);
SqlCommand cmdf = new SqlCommand();
cmdf.Connection = cnnf;
cmdf.CommandText = "SELECT * FROM Prelevatori WHERE CodPrelevator =" + mf.PrelevatoriId;
cnnf.Open();
SqlDataReader drFerme = cmdf.ExecuteReader();
if (drFerme.Read())
{
mf.PrelevatoriNume = Convert.ToString(drFerme["NumePrelevator"]);
}
drFerme.Close();
cnnf.Close();
}
mf.FermeNume = Convert.ToString(reader["Nume"]);
mf.CodFerma = Convert.ToString(reader["Cod"]);
if (!ferma.Equals(mf.FermeNume))
{
pos++;
}
mf.Nrf = pos;
ferma = mf.FermeNume;
mf.StradaFerma = Convert.ToString(reader["Strada"]);
mf.NumarFerma = Convert.ToString(reader["Numar"]);
mf.OrasFerma = Convert.ToString(reader["Oras"]);
mf.JudetFerma = Convert.ToString(reader["Denloc"]);
mf.PersContact = Convert.ToString(reader["PersonaDeContact"]);
mf.TelContact = Convert.ToString(reader["TelPersoanaContact"]);
list.Add(mf);
}
cnn.Close();
return list;
}
public void CreateReport()
{
Thread.CurrentThread.CurrentCulture.NumberFormat.NumberDecimalSeparator = ".";
logger.Info(string.Format("GetMostreFerma|CreateReport"));
LocalReport report = new LocalReport();
byte[] file;
string mimeType, encoding, fileExtension;
string[] streams;
Warning[] warnings;
long reportNumber;
ReportManager rManager = new ReportManager(ctx);
Report dalReport = rManager.GetOrCreateReport(FCCLReportType.Crotalii, ObjectId
, DropDownList1.SelectedItem.Text, TestDate, PrintDate, long.TryParse(TextBox3.Text, out reportNumber) ? (long?)reportNumber : null);
TextBox3.Text = dalReport.ReportNumber.ToString();
ReportDataSource rds = new ReportDataSource();
rds.Name = "MostreFabrica";
List <MostreFabrica> mostre = MostreFabrica.GetMostreFerma(DropDownList1.SelectedValue, DateTime.Parse(TextBox1.Text));
logger.Info(string.Format("GetMostreFerma|mostre cnt:{0}", mostre.Count));
rds.Value = mostre;
DateTime datatestare = DateTime.Parse(TextBox1.Text);
DateTime datatmin = datatestare;
DateTime datatmax = datatestare;
string datatesttitlu = datatestare.ToShortDateString();
DateTime datapmin = DateTime.MaxValue;
DateTime datapmax = DateTime.MinValue;
string dataprimtitlu;
string nrcomanda = "";
string combi = mostre[0].Combi;
foreach (MostreFabrica mf in mostre)
{
DateTime datamin = mf.DataTestare;
DateTime datamax = mf.DataTestareFinala;
if (mf.NrComanda.Trim() != "")
{
nrcomanda = mf.NrComanda.Trim();
}
if (DateTime.Compare(datamin, datatmin) < 0)
{
datatmin = datamin;
}
if (DateTime.Compare(datamax, datatmax) > 0)
{
datatmax = datamax;
}
if (DateTime.Compare(mf.DataPrimirii, datapmin) < 0)
{
datapmin = mf.DataPrimirii;
}
if (DateTime.Compare(mf.DataPrimirii, datapmax) > 0)
{
datapmax = mf.DataPrimirii;
}
}
if (datatmin != datatmax)
{
datatesttitlu = datatmin.ToShortDateString() + " - " + datatmax.ToShortDateString();
}
if (datapmin != datapmax)
{
dataprimtitlu = datapmin.ToShortDateString() + " si " + datapmax.ToShortDateString();
}
else
{
dataprimtitlu = datapmin.ToShortDateString();
}
rds.Value = mostre;
report.DataSources.Clear();
report.DataSources.Add(rds);
report.ReportPath = "ReportCrotalii.rdlc";
report.Refresh();
string fermaid = DropDownList1.SelectedValue;
string fermaname = DropDownList1.Items[DropDownList1.SelectedIndex].ToString();
// read fabrica
SqlConnection cnn = new SqlConnection(
ConfigurationManager.ConnectionStrings
["fccl2ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "SELECT Ferme_CCL.Cod,Ferme_CCL.Strada,Ferme_CCL.Numar,Ferme_CCL.Oras,Judete.Denloc "
+ "FROM Ferme_CCL,Judete WHERE Ferme_CCL.ID=" + fermaid
+ " AND Ferme_CCL.Judet=Judete.ID";
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
string fermastrada = Convert.ToString(reader["Strada"]);
string fermanumar = Convert.ToString(reader["Numar"]);
string fermaoras = Convert.ToString(reader["Oras"]);
string fermajudet = Convert.ToString(reader["Denloc"]);
string fermacod = Convert.ToString(reader["Cod"]);
reader.Close();
cnn.Close();
cmd.CommandText = "SELECT Fabrici.Nume, Ferme_CCL.FabricaID from Ferme_CCL, Fabrici "
+ " WHERE Ferme_CCL.ID =" + fermaid + " AND Ferme_CCL.FabricaID = Fabrici.ID";
cnn.Open();
reader = cmd.ExecuteReader();
reader.Read();
string asocid = Convert.ToString(reader["FabricaID"]);
string asocnume = Convert.ToString(reader["Nume"]);
reader.Close();
cnn.Close();
FermaName.Text = fermaname;
FermaCod.Text = fermacod;
FermaId.Text = "" + fermaid;
string datab = TextBox2.Text;
string nrb = dalReport.ReportNumber.ToString();
//set report parameters
string laborator = ddlLaborator.Items[ddlLaborator.SelectedIndex].ToString();
string responsabil = ddlResponsabil.Items[ddlResponsabil.SelectedIndex].ToString();
ReportParameter pAsocnume = new ReportParameter("asocnume", asocnume);
ReportParameter pNrcomanda = new ReportParameter("nrcomanda", nrcomanda);
ReportParameter pDatatestare = new ReportParameter("datatestare", datatesttitlu);
ReportParameter pDataprimirii = new ReportParameter("dataprimirii", dataprimtitlu);
ReportParameter pCombi = new ReportParameter("combi", combi);
ReportParameter pDatab = new ReportParameter("datab", TextBox2.Text);
ReportParameter pNrb = new ReportParameter("nrb", nrb);
ReportParameter pFermanume = new ReportParameter("fabricanume", fermaname);
ReportParameter pFermastrada = new ReportParameter("fabricastrada", fermastrada);
ReportParameter pFermanumar = new ReportParameter("fabricanumar", fermanumar);
ReportParameter pFermaoras = new ReportParameter("fabricaoras", fermaoras);
ReportParameter pFermajudet = new ReportParameter("fabricajudet", fermajudet);
ReportParameter pLaborator = new ReportParameter("laborator", laborator);
ReportParameter pResponsabil = new ReportParameter("responsabil", responsabil);
ReportParameter pVersiune = new ReportParameter("Versiune", new SettingManager(StaticDataHelper.FCCLDbContext).GetValueByName("ReportCrotalii"));
ReportParameter[] p = { pDatatestare, pDataprimirii, pCombi, pNrcomanda, pDatab, pNrb, pFermanume, pFermastrada, pFermanumar, pFermaoras, pFermajudet, pAsocnume, pLaborator
, pResponsabil, pVersiune };
report.SetParameters(p);
file = report.Render("PDF", StaticData.DEVICE_INFO_PDF, out mimeType, out encoding, out fileExtension, out streams, out warnings);
string httppath = StaticDataHelper.SettingsManager.CaleRapoarteHttp;
string filepath = StaticDataHelper.SettingsManager.CaleRapoarte;
fermaname = replace_special_car_null(fermaname);
string nrcom = nrcomanda.Replace("/", "_");
nrcom = nrcom.Replace(".", "");
string raport_name = "Raport" + nrcom + "-" + fermaname + "_" + fermacod + "-" + datatestare.ToShortDateString().Replace("/", "") + ".pdf";
string raport_excel = "Raport" + nrcom + "-" + fermaname + "_" + fermacod + "-" + datatestare.ToShortDateString().Replace("/", "") + ".xls";
string path_raport_http = "http://" + Request.ServerVariables.Get("HTTP_HOST") + "/" + httppath;
string pdf_link = path_raport_http + @"Ferme/" + raport_name;
string pdf_file = filepath + @"Ferme/" + raport_name;
// writefile
string excel_link = path_raport_http + @"Ferme/" + raport_excel;
string excel_file = filepath + @"Ferme/" + raport_excel;
// Trace.Write(pdf_file);
File.WriteAllBytes(pdf_file, file);
logger.Info(string.Format("GetMostreFerma|pdf done"));
dalReport.SampleCount = mostre.Count;
dalReport.PageCount = PDFHelper.GetNumberOfPdfPages(pdf_file);
rManager.Save(dalReport);
ctx.SaveChanges();
logger.Info(string.Format("GetMostreFerma|saved report"));
// raport excel
LocalReport rapexcel = new LocalReport();
ReportDataSource rdse = new ReportDataSource();
rdse.Name = "MostreFabrica";
rdse.Value = mostre;
rapexcel.DataSources.Clear();
rapexcel.DataSources.Add(rdse);
rapexcel.ReportPath = "ReportCrotalii.rdlc";
rapexcel.SetParameters(p);
rapexcel.Refresh();
rapexcel.SetParameters(new ReportParameter("BkImage", ""));
byte[] file_xls = rapexcel.Render("EXCEL", StaticData.DEVICE_INFO_XLS, out mimeType, out encoding, out fileExtension, out streams, out warnings);
// end raport excel
File.WriteAllBytes(excel_file, file_xls);
logger.Info(string.Format("GetMostreFerma|excel done"));
int firstyear;
if (int.TryParse(ConfigurationManager.AppSettings["firstyear"], out firstyear))
{
// test rep. portal
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
SqlCommand cmda = new SqlCommand("Select aspnet_Users.UserName from aspnet_users join usersinformation on aspnet_Users.userid = usersinformation.userid where usersinformation.asocid=" + asocid, connection);
connection.Open();
string username = "";
using (SqlDataReader rdr = cmda.ExecuteReader())
{
if (rdr.Read())
{
username = rdr["UserName"].ToString();
}
rdr.Close();
}
connection.Close();
if (username.Length > 0)
{
try
{
MembershipUser User = Membership.Providers["PortalProvider"].GetUser(username, false);
Username.Text = User.UserName;
//Start generating from test date
for (int year = firstyear; year <= DateTime.Now.Year; year++)
{
logger.Info(string.Format("GetMostreFerma|ext call year:{0}", year));
CreateReportExt(year);
}
}
catch { }
}
}
string raport_csv = "CSV_Registru_" + datatestare.ToShortDateString().Replace("/", "") + "_" + fermaid + "_" + fermaname + ".csv";
string csv_link = path_raport_http + @"Registru/" + raport_csv;
string csv_file = filepath + @"Registru/" + raport_csv;
logger.Info(string.Format("CreateReportExt|write csv file {0}", csv_file));
CreateExcelFile(mostre, csv_file);
logger.Info(string.Format("GetMostreFerma|processing done"));
pdflink.Visible = true;
pdflink.NavigateUrl = pdf_link;
pdflink.Text = raport_name;
xlslink.Visible = true;
xlslink.NavigateUrl = excel_link;
xlslink.Text = raport_excel;
csvlink.Visible = true;
csvlink.NavigateUrl = csv_link;
csvlink.Text = raport_csv;
}
//getmostresms
public static List<MostreFabrica> GetMostreSMS(DateTime datatestare)
{
SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["fccl2ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText =
"SELECT Ferme_CCL.ID, Ferme_CCL.Nume,Ferme_CCL.Cod,Ferme_CCL.TelPersoanaContact, MostreTancuri.NrComanda, MostreTancuri.DataTestare " +
" FROM Ferme_CCL, MostreTancuri WHERE Ferme_CCL.Id =MostreTancuri.FermaId AND " +
"MostreTancuri.DataTestareFinala = CONVERT(date, '" + datatestare.ToShortDateString() + "',103) " +
"AND MostreTancuri.Validat = 1 " +
" AND MostreTancuri.SentSms=0 " +
" AND Ferme_CCL.SendSms=1" +
"GROUP BY Ferme_CCl.ID, ferme_CCL.Nume, Ferme_CCL.Cod,Ferme_CCL.TelPersoanaContact,MostreTancuri.NrComanda,MostreTancuri.DataTestare " +
"ORDER BY Ferme_CCL.Nume";
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
List<MostreFabrica> list = new List<MostreFabrica>();
int pos = 0;
string ferma = "";
while (reader.Read())
{
MostreFabrica mf = new MostreFabrica();
mf.NrComanda = Convert.ToString(reader["NrComanda"]);
mf.DataTestare = Convert.ToDateTime(reader["DataTestare"]);
mf.FermeNume = Convert.ToString(reader["Nume"]);
mf.CodFerma = Convert.ToString(reader["Cod"]);
SqlConnection connection =
new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
SqlCommand cmu =
new SqlCommand(
"select u.UserName from UsersInformation i join aspnet_Users u on i.UserId = u.UserId where i.UserCod ='" +
mf.CodFerma + "'", connection);
//new SqlCommand("select * from UsersInformation where UserCod='" + mf.CodFerma + "'", connection);
connection.Open();
using (SqlDataReader rdr = cmu.ExecuteReader())
{
if (rdr.Read())
{
mf.FermeNume = rdr["UserName"].ToString().Trim();
}
rdr.Close();
}
mf.FermeId = Convert.ToString(reader["ID"]);
if (!ferma.Equals(mf.FermeNume))
{
pos++;
}
mf.Nrf = pos;
ferma = mf.FermeNume;
mf.TelContact = Convert.ToString(reader["TelPersoanaContact"]);
list.Add(mf);
}
cnn.Close();
return list;
}
public void CreateReport()
{
ReportManager rManager = new ReportManager(ctx);
LocalReport report = new LocalReport();
byte[] file;
string mimeType, encoding, fileExtension;
string[] streams;
Warning[] warnings;
long reportNumber;
Report dalReport = rManager.GetOrCreateReport(FCCLReportType.FCB, -1
, DropDownList1.SelectedItem.Text, TestDate, PrintDate, long.TryParse(TextBox3.Text, out reportNumber) ? (long?)reportNumber : null);
TextBox3.Text = dalReport.ReportNumber.ToString();
ReportDataSource rds = new ReportDataSource();
rds.Name = "MostreFabrica";
List <MostreFabrica> mostre = MostreFabrica.GetMostreFCB(DropDownList1.SelectedValue, TextBox1.Text);
rds.Value = mostre;
string datatestare = TextBox1.Text;
string datatmin = datatestare;
string datatmax = datatestare;
string datatesttitlu = datatestare;
string dataprimirii = "";
string datapmin = dataprimirii;
string datapmax = dataprimirii;
string dataprimtitlu = dataprimirii;
string combi = mostre[0].Combi;
foreach (MostreFabrica mf in mostre)
{
string datamin = mf.DataTestare;
string datamax = mf.DataTestareFinala;
dataprimirii = mf.DataPrimirii;
if (datapmin == "")
{
datapmin = dataprimirii;
}
if (datapmax == "")
{
datapmax = dataprimirii;
}
if (datamin != datatmin)
{
DateTime dt1 = new DateTime(Int32.Parse(datamin.Substring(6, 4)), Int32.Parse(datamin.Substring(3, 2)), Int32.Parse(datamin.Substring(0, 2)));
DateTime dt2 = new DateTime(Int32.Parse(datatmin.Substring(6, 4)), Int32.Parse(datatmin.Substring(3, 2)), Int32.Parse(datatmin.Substring(0, 2)));
if (DateTime.Compare(dt1, dt2) < 0)
{
datatmin = datamin;
}
}
if (datamax != datatmax)
{
DateTime dt1 = new DateTime(Int32.Parse(datamax.Substring(6, 4)), Int32.Parse(datamax.Substring(3, 2)), Int32.Parse(datamax.Substring(0, 2)));
DateTime dt2 = new DateTime(Int32.Parse(datatmax.Substring(6, 4)), Int32.Parse(datatmax.Substring(3, 2)), Int32.Parse(datatmax.Substring(0, 2)));
if (DateTime.Compare(dt1, dt2) > 0)
{
datatmax = datamax;
}
}
// data primirii
if (dataprimirii != datapmin)
{
DateTime dt1 = new DateTime(Int32.Parse(dataprimirii.Substring(6, 4)), Int32.Parse(dataprimirii.Substring(3, 2)), Int32.Parse(dataprimirii.Substring(0, 2)));
DateTime dt2 = new DateTime(Int32.Parse(datapmin.Substring(6, 4)), Int32.Parse(datapmin.Substring(3, 2)), Int32.Parse(datapmin.Substring(0, 2)));
if (DateTime.Compare(dt1, dt2) < 0)
{
datapmin = dataprimirii;
}
}
if (dataprimirii != datapmax)
{
DateTime dt1 = new DateTime(Int32.Parse(dataprimirii.Substring(6, 4)), Int32.Parse(dataprimirii.Substring(3, 2)), Int32.Parse(dataprimirii.Substring(0, 2)));
DateTime dt2 = new DateTime(Int32.Parse(datapmax.Substring(6, 4)), Int32.Parse(datapmax.Substring(3, 2)), Int32.Parse(datapmax.Substring(0, 2)));
if (DateTime.Compare(dt1, dt2) > 0)
{
datapmax = dataprimirii;
}
}
}
if (datatmin != datatmax)
{
datatesttitlu = datatmin + " - " + datatmax;
}
if (datapmin != datapmax)
{
dataprimtitlu = datapmin + " si " + datapmax;
}
else
{
dataprimtitlu = datapmin;
}
rds.Value = mostre;
report.DataSources.Clear();
report.DataSources.Add(rds);
report.ReportPath = "ReportFCB4.rdlc";
report.Refresh();
string client = DropDownList1.SelectedValue;
// read fabrica
SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["fccl2ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "SELECT MostreTancuri.AdresaClient,MostreTancuri.NumeClient,MostreTancuri.Localitate,MostreTancuri.Judet,MostreTancuri.NrComanda "
+ "FROM MostreTancuri WHERE MostreTancuri.NumeClient='" + client + "'";
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
string fabricastrada = mostre[0].FabriciStrada;
string fabricaoras = mostre[0].FabriciOras;
string fabricajudet = mostre[0].FabriciJudet;
string nrcomanda = mostre[0].NrComanda;
reader.Close();
cnn.Close();
string nrb = dalReport.ReportNumber.ToString();;
//set report parameters
string laborator = ddlLaborator.Items[ddlLaborator.SelectedIndex].ToString();
string responsabil = ddlResponsabil.Items[ddlResponsabil.SelectedIndex].ToString();
ReportParameter pDatatestare = new ReportParameter("datatestare", datatesttitlu);
ReportParameter pDataprimirii = new ReportParameter("dataprimirii", dataprimtitlu);
ReportParameter pCombi = new ReportParameter("combi", combi);
ReportParameter pDatab = new ReportParameter("datab", TextBox2.Text);
ReportParameter pNrb = new ReportParameter("nrb", nrb);
ReportParameter pFabricanume = new ReportParameter("fabricanume", client);
ReportParameter pFabricastrada = new ReportParameter("fabricastrada", fabricastrada);
ReportParameter pComanda = new ReportParameter("nrcomanda", nrcomanda);
ReportParameter pFabricaoras = new ReportParameter("fabricaoras", fabricaoras);
ReportParameter pFabricajudet = new ReportParameter("fabricajudet", fabricajudet);
ReportParameter pLaborator = new ReportParameter("laborator", laborator);
ReportParameter pResponsabil = new ReportParameter("responsabil", responsabil);
ReportParameter pVersiune = new ReportParameter("Versiune", new SettingManager(StaticDataHelper.FCCLDbContext).GetValueByName("ReportFCB4"));
ReportParameter[] p = { pDatatestare, pDataprimirii, pCombi, pDatab, pNrb, pFabricanume, pFabricastrada, pComanda, pFabricaoras, pFabricajudet, pLaborator, pResponsabil, pVersiune };
report.SetParameters(p);
file = report.Render("PDF", StaticData.DEVICE_INFO_PDF, out mimeType, out encoding, out fileExtension, out streams, out warnings);
string httppath = StaticDataHelper.SettingsManager.CaleRapoarteHttp;
string filepath = StaticDataHelper.SettingsManager.CaleRapoarte;
string parse_client = replace_special_car(client);
string raport_name = "RaportFCB" + datatestare.Replace("/", "_") + "_" + nrb + "_" + parse_client + ".pdf";
string raport_excel = "RaportFCB" + datatestare.Replace("/", "_") + "_" + nrb + "_" + parse_client + ".xls";
string path_raport_http = "http://" + Request.ServerVariables.Get("HTTP_HOST") + "/" + httppath;
string pdf_link = path_raport_http + @"FCB/" + raport_name;
string pdf_file = filepath + @"FCB/" + raport_name;
// writefile
string excel_link = path_raport_http + @"FCB/" + raport_excel;
string excel_file = filepath + @"FCB/" + raport_excel;
report.SetParameters(new ReportParameter("BkImage", ""));
File.WriteAllBytes(pdf_file, file);
dalReport.SampleCount = mostre.Count;
dalReport.PageCount = PDFHelper.GetNumberOfPdfPages(pdf_file);
rManager.Save(dalReport);
ctx.SaveChanges();
byte[] file_xls = report.Render("EXCEL", StaticData.DEVICE_INFO_XLS, out mimeType, out encoding, out fileExtension, out streams, out warnings);
File.WriteAllBytes(excel_file, file_xls);
pdflink.Visible = true;
pdflink.NavigateUrl = pdf_link;
pdflink.Text = raport_name;
xlslink.Visible = true;
xlslink.NavigateUrl = excel_link;
xlslink.Text = raport_excel;
}
/* public void InsertMostra(string CodBare,string IdZilnic, string Cant,string PrelevatoriId,
string DataPrimirii, string DataPrelevare, string DataTestare, string DataTestareFinala,
string Grasime, string Proteina, string Lactoza, string Substu, string Punti,string Antib,
string NCS, string NTG, string Urea, string Ph, string NrComanda,
string PrelevatoriNume, string NumeProba, string FabriciNume, string FabriciStrada,
string FabriciOras, string FabriciJudet, bool Definitiv, bool Validat)*/
public void InsertMostra(MostreFabrica mf)
{
string grasime = mf.Grasime.Trim().Equals("")
? "0"
: mf.Grasime.Trim().Equals("0") ? "0.00001" : mf.Grasime.Trim();
string proteina = mf.Proteina.Trim().Equals("")
? "0"
: mf.Proteina.Trim().Equals("0") ? "0.00001" : mf.Proteina.Trim();
string lactoza = mf.Lactoza.Trim().Equals("")
? "0"
: mf.Lactoza.Trim().Equals("0") ? "0.00001" : mf.Lactoza.Trim();
string caseina = mf.Casein.Trim().Equals("") ? "0" : mf.Casein.Trim().Equals("0") ? "0.00001" : mf.Casein.Trim();
string substu = mf.Substu.Trim().Equals("") ? "0" : mf.Substu.Trim().Equals("0") ? "0.00001" : mf.Substu.Trim();
string pcti = "0.00001";
//
string antib = "";
//
string ncs = mf.NCS.Trim().Equals("") ? "0" : mf.NCS.Trim().Equals("0") ? "0.00001" : mf.NCS.Trim();
string ntg = "0.00001";
string rasa = mf.Rasa;
string urea = mf.Urea.Trim().Equals("") ? "0" : mf.Urea.Trim().Equals("0") ? "0.00001" : mf.Urea.Trim();
string ph = mf.Ph.Trim().Equals("") ? "0" : mf.Ph.Trim().Equals("0") ? "0.00001" : mf.Ph.Trim();
string codferma = mf.CodBare.Substring(0, 5);
string definitiv = mf.Definitiv ? "1" : "0";
string validat = mf.Validat ? "1" : "0";
string sentsms = mf.Sentsms ? "1" : "0";
string cant = "0";
string prelid = mf.PrelevatoriId.Trim().Equals("") ? "0" : mf.PrelevatoriId.Trim();
SqlConnection cnn = new SqlConnection(
ConfigurationManager.ConnectionStrings
["fccl2ConnectionString"].ConnectionString);
string query =
"INSERT INTO MostreTancuri (CodBare,IdZilnic,CantitateLaPrelevare,PrelevatorID,DataPrimirii,DataPrelevare,DataTestare,DataTestarefinala," +
"Grasime, ProcentProteine,Caseina,ProcentLactoza,SubstantaUscata,PunctInghet,NumarCeluleSomatice,IncarcaturaGermeni,Antibiotice," +
"Urea,PH,NrComanda,NumePrelevator,NumeProba,NumeClient,AdresaClient,Localitate,Judet,Definitiv,Validat,Sentsms,CodFerma,GrasimeProv,LactozaProv,SubstuProv,ProteineProv,Rasa) VALUES(" +
"'" + mf.CodBare + "'," + mf.IdZilnic + "," + cant + "," + prelid + ",CONVERT(date,'" + mf.DataPrimirii.ToShortDateString() +
"',103)," + "CONVERT(date,'" + mf.DataPrelevare.ToShortDateString() + "',103), CONVERT(date,'" + mf.DataTestare.ToShortDateString() +
"',103), CONVERT(date,'" + mf.DataTestareFinala.ToShortDateString() + "',103)," +
grasime + "," + proteina + "," + caseina + "," + lactoza + "," + substu + "," + pcti + "," + ncs + "," + ntg +
",'" + antib + "'," + urea + "," + ph + ",'" + mf.NrComanda + "','" + mf.PrelevatoriNume + "','" +
mf.NumeProba + "','" + mf.FabriciNume + "','" +
mf.FabriciStrada + "','" + mf.FabriciOras + "','" + mf.FabriciJudet + "'," + definitiv + "," + validat + "," +
sentsms + ",'" + codferma + "','" + rasa + "',0,0,0,0)";
SqlCommand cmd = new SqlCommand(query, cnn);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
public void UpdateMostra(MostreFabrica mf)
{
MostreFabrica mostraOriginala = GetMostraByCodBare(mf.CodBare);
if (mostraOriginala == null) return;
string grasime = (mf.Grasime != null && mf.Grasime.Equals(mostraOriginala.Grasime)) ? "" : ",Grasime=" + ReformatNumberForSql(Utils.NomalizeForStorage(mf.Grasime));
string proteina = (mf.Proteina != null && mf.Proteina.Equals(mostraOriginala.Proteina)) ? "" : ",ProcentProteine=" + ReformatNumberForSql(Utils.NomalizeForStorage(mf.Proteina));
string lactoza = (mf.Lactoza != null && mf.Lactoza.Equals(mostraOriginala.Lactoza)) ? "" : ",ProcentLactoza=" + ReformatNumberForSql(Utils.NomalizeForStorage(mf.Lactoza));
string caseina = (mf.Casein != null && mf.Casein.Equals(mostraOriginala.Casein)) ? "" : ",Caseina=" + ReformatNumberForSql(Utils.NomalizeForStorage(mf.Casein));
string substu = (mf.Substu != null && mf.Substu.Equals(mostraOriginala.Substu)) ? "" : ",SubstantaUscata=" + ReformatNumberForSql(Utils.NomalizeForStorage(mf.Substu));
string pcti = mf.Validat ? "0.00001" : Utils.NomalizeForStorage(mf.Puncti);
string ncs = (mf.NCS != null && mf.NCS.Equals(mostraOriginala.NCS)) ? "" : ",NumarCeluleSomatice=" + ReformatNumberForSql(Utils.NomalizeForStorage(mf.NCS));
string ntg = mf.Validat ? "0.00001" : Utils.NomalizeForStorage(mf.NTG);
string rasa = mf.Rasa;
string urea = (mf.Urea != null && mf.Urea.Equals(mostraOriginala.Urea)) ? "" : ",Urea=" + ReformatNumberForSql(Utils.NomalizeForStorage(mf.Urea));
string ph = (mf.Ph != null && mf.Ph.Equals(mostraOriginala.Ph)) ? "" : ",PH=" + ReformatNumberForSql(Utils.NomalizeForStorage(mf.Ph));
string codferma = mf.CodBare.Substring(0, 5);
string definitiv = mf.Definitiv ? "1" : "0";
string validat = mf.Validat ? "1" : "0";
string sentsms = mf.Sentsms ? "1" : "0";
//string cant = mf.Validat ? ",CantitateLaPrelevare=0" : string.Empty;
string prelid = mf.PrelevatoriId.Trim().Equals("") ? "0" : mf.PrelevatoriId.Trim();
SqlConnection cnn =
new SqlConnection(ConfigurationManager.ConnectionStrings["fccl2ConnectionString"].ConnectionString);
string query = "UPDATE MostreTancuri SET IdZilnic=" + ReformatNumberForSql(mf.IdZilnic) +// cant +
",PrelevatorID=" + ReformatNumberForSql(prelid) + ",DataPrimirii=CONVERT(date,'" + mf.DataPrimirii.ToShortDateString() + "',103)," +
"DataPrelevare=CONVERT(date,'" + mf.DataPrelevare.ToShortDateString() + "',103),DataTestare=CONVERT(date,'" + mf.DataTestare.ToShortDateString() +
"',103),DataTestareFinala=CONVERT(date,'" + mf.DataTestareFinala.ToShortDateString() + "',103)" +
grasime + proteina + caseina + lactoza + substu + ",PunctInghet=" +
ReformatNumberForSql(pcti) + ncs + ",IncarcaturaGermeni=" + ReformatNumberForSql(ntg) +
urea + ph + ",NrComanda=" + ReformatStringForSql(mf.NrComanda) + ",NumePrelevator=" +
ReformatStringForSql(mf.NumePrelevator) + ",NumeProba=" + ReformatStringForSql(mf.NumeProba) + ",NumeClient=" + ReformatStringForSql(mf.FabriciNume) +
",AdresaClient=" + ReformatStringForSql(mf.FabriciStrada) + ",Localitate=" +
ReformatStringForSql(mf.FabriciOras) + ",Judet=" + ReformatStringForSql(mf.FabriciJudet) + ",Definitiv=" + ReformatNumberForSql(definitiv) + ",Validat=" +
ReformatNumberForSql(validat) + ",Sentsms=" + ReformatNumberForSql(sentsms) + ",CodFerma=" + ReformatStringForSql(codferma) + ",Rasa=" +
ReformatStringForSql(rasa) + " WHERE CodBare=" + ReformatStringForSql(mf.CodBare);
SqlCommand cmd = new SqlCommand(query, cnn);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
string adresafizicaserver = Server.MapPath("~");
string cale_rap = Server.MapPath("~/Documents/Sms/");
string cale_log = Server.MapPath("~/Documents/Sms/");
string logname = "LogSmsFerme" + DataSelectie.Text.Trim().Replace(@"/", @"_") + ".txt";
string smsname = "SmsTrimise" + DataSelectie.Text.Trim().Replace(@"/", @"_") + ".txt";
string fulllogname = adresafizicaserver + @"\Documents\Sms\" + logname;
string fullsmsname = adresafizicaserver + @"\Documents\Sms\" + smsname;
string datatestare = DataSelectie.Text;
MostreDB mostre = new MostreDB();
List <MostreFabrica> mostresms = MostreFabrica.GetMostreSMS(datatestare);
SerialPort port = new SerialPort();
clsSMS objclsSMS = new clsSMS();
string portname = ConfigurationManager.AppSettings["ComPort"].ToString();
string baudrate = "9600";
port = objclsSMS.OpenPort(portname, baudrate);
StringBuilder updateSql = new StringBuilder("update mostretancuri set SentSms=1 where ");
int countSms = 0;
int countError = 0;
mostre.Addtext(adresafizicaserver + @"\Documents\Sms\" + logname, "************" + "Data: " + DateTime.Now.ToString() + "**********");
mostre.Addtext(adresafizicaserver + @"\Documents\Sms\" + smsname, "************" + "Data: " + DateTime.Now.ToString() + "**********");
int counter = 0;
foreach (MostreFabrica ms in mostresms)
{
//create sms string
// G:99,99;P:9,99;C:9,99;L:9,99;SUN:99,99;pH:9,99;Pct ing:-0,513;
//Inhib:N;U:99,99;NTG:10000000;NCS:99999999
// G-grasime; P-proteina; C-cazeina; L-lactoza; SUN- subst. uscata negrasa;
//Pct. ing- punct inghet;
//Inhib- reziduuri inhibitori ce poate fi P-pozitiv sau N-negativ; U- uree
//TODO : uncomment !!!
if (!string.IsNullOrEmpty(ms.TelContact))
{
if (ms.FermeNume.Length > 30)
{
ms.FermeNume = ms.FermeNume.Substring(0, 30);
}
StringBuilder sms = new StringBuilder();
sms.Append(ms.FermeNume + ";");
sms.Append("G:" + ms.Grasime + ";");
sms.Append("P:" + ms.Proteina + ";");
sms.Append("C:" + ms.Casein + ";");
sms.Append("L:" + ms.Lactoza + ";");
sms.Append("SUN:" + ms.Substu + ";");
sms.Append("pH:" + ms.Ph + ";");
sms.Append("Pct.ing.:" + ms.Puncti + ";");
sms.Append("Inhib:" + ms.Antib + ";");
sms.Append("U:" + ms.Urea + ";");
try
{
sms.Append("NTG:" + ms.NTG + "000;");
}
catch
{
sms.Append("NTG:>10000000;");
}
try
{
sms.Append("NCS:" + ms.NCS + "000;");
}
catch
{
sms.Append("NCS:" + ms.NCS + ";");
}
sms.Append("Cod:" + ms.CodBare);
string cellnumber = Regex.Replace(ms.TelContact, @"[\s()-]", "");
cellnumber = (cellnumber.StartsWith("0") ? "+4" + cellnumber : cellnumber);
try
{
bool res = objclsSMS.sendMsg(port, portname, "9600", cellnumber, sms.ToString());
if (res)
{
string strupdate = (countSms > 0) ? " or codbare ='" + ms.CodBare + "' " : " codbare ='" + ms.CodBare + "' ";
updateSql.Append(strupdate);
countSms++;
mostre.Addtext(adresafizicaserver + @"\Documents\Sms\" + smsname, countSms + ". " + ms.CodFerma + " " + ms.FermeNume + " " + cellnumber + " - " + DateTime.Now.ToString());
mostre.Addtext(adresafizicaserver + @"\Documents\Sms\" + smsname, sms.ToString());
}
else
{
countError++;
mostre.Addtext(adresafizicaserver + @"\Documents\Sms\" + logname, countError + ". " + ms.CodFerma + " " + ms.FermeNume + " " + cellnumber + " - " + DateTime.Now.ToString());
mostre.Addtext(adresafizicaserver + @"\Documents\Sms\" + logname, sms.ToString());
}
}
catch (Exception ex)
{
mostre.Addtext(adresafizicaserver + @"\Documents\Sms\" + logname, ex.Message + " - " + ex.StackTrace);
}
counter++;
}
}
try
{
objclsSMS.ClosePort(port);
}
catch { }
mostre.Addtext(adresafizicaserver + @"\Documents\Sms\" + logname, "************" + "End Data: " + DateTime.Now.ToString() + "**********");
mostre.Addtext(adresafizicaserver + @"\Documents\Sms\" + smsname, "************" + "End Data: " + DateTime.Now.ToString() + "**********");
// update moste tancuri set sentsms=1
SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["fccl2ConnectionString"].ConnectionString);
try
{
if (countSms > 0)
{
SqlCommand cmd = new SqlCommand(updateSql.ToString(), cnn);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
}
catch (Exception exU)
{
Label1.Text = " Update MostreTancuri failed! " + exU.Message;
}
fissms.NavigateUrl = HttpContext.Current.Request.ApplicationPath + @"/Documents/Sms/" + smsname;
fissms.Visible = true;
fislog.NavigateUrl = HttpContext.Current.Request.ApplicationPath + @"/Documents/Sms/" + logname;
fislog.Visible = true;
}
//
//getmostresms
public static List<MostreFabrica> GetMostreSMSTest
(string datatestare)
{
SqlConnection cnn = new SqlConnection(
ConfigurationManager.ConnectionStrings
["fccl2ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "SELECT Ferme_CCL.ID, Ferme_CCL.Nume,Ferme_CCL.FabricaID,Ferme_CCL.Cod,Ferme_CCL.Strada,Ferme_CCL.Numar,Ferme_CCL.Oras,Ferme_CCL.PersonaDeContact,Ferme_CCL.TelPersoanaContact,Ferme_CCL.Ferme_CCL,Ferme_CCL.FermierID,Ferme_CCL.Telefon, "
+ "MostreTancuri.DataPrelevare, MostreTancuri.DataPrimirii, MostreTancuri.CodBare," +
"MostreTancuri.Grasime, MostreTancuri.ProcentProteine,MostreTancuri.ProcentLactoza, MostreTancuri.SubstantaUscata, MostreTancuri.PunctInghet, MostreTancuri.Antibiotice," +
"MostreTancuri.NumarCeluleSomatice, MostreTancuri.IncarcaturaGermeni,MostreTancuri.PH,MostreTancuri.Urea,MostreTancuri.Caseina,MostreTancuri.GrasimeProv, MostreTancuri.PrelevatorID," +
"MostreTancuri.DataTestare,MostreTancuri.DataTestareFinala,MostreTancuri.NrComanda " +
"FROM Ferme_CCL, MostreTancuri WHERE"
+ " CONVERT(datetime, MostreTancuri.DataTestareFinala, 103) = CONVERT(datetime, '" + datatestare + "', 103) "
//+ " AND MostreTancuri.CodFerma is not null"
+ " AND MostreTancuri.CodFerma = Ferme_CCL.Cod "
//+ " AND MostreTancuri.SentSms=0"
//TODO: uncomment !!!
+ " AND Ferme_CCL.SendSms=1"
+ " AND MostreTancuri.Validat = 1 ORDER BY Ferme_CCL.Nume";
cnn.Open();
SqlDataReader reader = cmd.ExecuteReader();
List<MostreFabrica> list = new List<MostreFabrica>();
int pos = 0;
string ferma = "";
while (reader.Read())
{
MostreFabrica mf = new MostreFabrica();
mf.CodBare = Convert.ToString(reader["CodBare"]);
mf.NrComanda = Convert.ToString(reader["NrComanda"]);
mf.DataTestare = Convert.ToString(reader["DataTestare"]);
mf.DataTestareFinala = Convert.ToString(reader["DataTestareFinala"]);
mf.DataPrimirii = ((DateTime)reader["DataPrimirii"]).ToString("dd/MM/yyyy");
mf.DataPrelevare = Convert.ToString(reader["DataPrelevare"]);
mf.Grasime = (Convert.ToDouble(Convert.ToString(reader["Grasime"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["Grasime"])) == 0.00001) ? "0" : Convert.ToString(reader["Grasime"]));
mf.Proteina = (Convert.ToDouble(Convert.ToString(reader["ProcentProteine"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["ProcentProteine"])) == 0.00001) ? "0" : Convert.ToString(reader["ProcentProteine"]));
mf.Lactoza = (Convert.ToDouble(Convert.ToString(reader["ProcentLactoza"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["ProcentLactoza"])) == 0.00001) ? "0" : Convert.ToString(reader["ProcentLactoza"]));
mf.Substu = (Convert.ToDouble(Convert.ToString(reader["SubstantaUscata"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["SubstantaUscata"])) == 0.00001) ? "0" : Convert.ToString(reader["SubstantaUscata"]));
mf.NCS = (Convert.ToDouble(Convert.ToString(reader["NumarCeluleSomatice"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["NumarCeluleSomatice"])) == 0.00001) ? "0" : Convert.ToString(reader["NumarCeluleSomatice"]));
mf.NTG = (Convert.ToDouble(Convert.ToString(reader["IncarcaturaGermeni"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["IncarcaturaGermeni"])) == 0.00001) ? "0" : Convert.ToString(reader["IncarcaturaGermeni"]));
if (mf.NTG != "-" && Double.Parse(mf.NTG) >= 10000)
mf.NTG = ">10000";
mf.Puncti = Convert.ToString(reader["PunctInghet"]);
mf.Apa = "-";
if (Convert.ToDouble(mf.Puncti) == 0)
mf.Puncti = "-";
else if (Convert.ToDouble(mf.Puncti) == 0.00001)
mf.Puncti = "0";
if (!mf.Puncti.Equals("-"))
{
mf.Apa = UMostre.ApaAdaugata(mf.Puncti);
mf.Puncti = "-0." + mf.Puncti;
}
mf.Antib = Convert.ToString(reader["Antibiotice"]);
if (mf.Antib != "")
{
if (mf.Antib == "0")
mf.Antib = "N";
else
mf.Antib = "P";
}
else
mf.Antib = "-";
mf.Urea = (Convert.ToDouble(Convert.ToString(reader["Urea"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["Urea"])) == 0.00001) ? "0" : Convert.ToString(reader["Urea"]));
mf.Ph = (Convert.ToDouble(Convert.ToString(reader["PH"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["PH"])) == 0.00001) ? "0" : Convert.ToString(reader["PH"]));
mf.Combi = Convert.ToString(reader["GrasimeProv"]);
mf.Casein = (Convert.ToDouble(Convert.ToString(reader["Caseina"])) == 0 ? "-" : (Convert.ToDouble(Convert.ToString(reader["Caseina"])) == 0.00001) ? "0" : Convert.ToString(reader["Caseina"]));
mf.PrelevatoriId = Convert.ToString(reader["PrelevatorID"]);
//get prelevator name
mf.PrelevatoriNume = Convert.ToString(reader["PersonaDeContact"]);
mf.FermeNume = Convert.ToString(reader["Nume"]);
mf.CodFerma = Convert.ToString(reader["Cod"]);
if (!ferma.Equals(mf.FermeNume))
{
pos++;
}
mf.Nrf = pos;
ferma = mf.FermeNume;
mf.StradaFerma = Convert.ToString(reader["Strada"]);
mf.NumarFerma = Convert.ToString(reader["Numar"]);
mf.OrasFerma = Convert.ToString(reader["Oras"]);
mf.PersContact = Convert.ToString(reader["PersonaDeContact"]);
mf.TelContact = Convert.ToString(reader["TelPersoanaContact"]);
string fermeccl = Convert.ToString(reader["Ferme_CCL"]);
string fermierid = Convert.ToString(reader["FermierID"]);
list.Add(mf);
}
cnn.Close();
return list;
}
