public Ferme_CCL GetFarmByName(string numeFerma)
{
Ferme_CCL farm = null;
var cn = new SqlConnection(connectionString);
SqlDataReader reader = null;
try
{
var cmd =
new SqlCommand(
"SELECT Cod,Ferme_CCL,ID,Nume,FabricaID,Strada,Numar,Oras,Judet,JudetID,Telefon,Email,FermierID,CodPostal,Fax,PersonaDeContact,TelPersoanaContact,SendSms FROM Ferme_CCL WHERE Cod IS NOT NULL AND Nume = @nume",
cn);
cmd.Parameters.AddWithValue("@nume", numeFerma);
cn.Open();
reader = cmd.ExecuteReader();
if (reader.Read())
{
farm = new Ferme_CCL
{
Cod = reader[0] as String,
Ferme = reader[1] as String,
Id = reader[2] as Int32?,
Nume = reader[3] as String,
FabricaId = reader[4] as Int32?,
Strada = reader[5] as String,
Numar = reader[6] as String,
Oras = reader[7] as String,
Judet = reader[8] as String,
JudetId = reader[9] as Int32?,
Telefon = reader[10] as String,
Email = reader[11] as String,
FermierId = reader[12] as Int32?,
CodPostal = reader[13] as String,
Fax = reader[14] as String,
PersoanaDeContact = reader[15] as String,
TelPersoanaContact = reader[16] as String,
SendSms = (bool)reader[17]
};
}
}
catch (Exception ex)
{
logger.Error(string.Format("GetFarmByName|numeFerma:{0} ERROR:{1}", numeFerma, ex.Message));
}
finally
{
if (reader != null)
{
reader.Close();
}
cn.Close();
}
return(farm);
}
public bool UpdateFarm(Ferme_CCL farm)
{
var cn = new SqlConnection(connectionString);
int affectedRows = 0;
try
{
var cmd =
new SqlCommand(
"UPDATE Ferme_CCL SET Cod = @Cod,Nume = @Nume,FabricaID = @FabricaID,Strada = @Strada,Numar = @Numar, Oras = @Oras,Judet = @Judet,CodPostal = @CodPostal,Telefon = @Telefon,Fax = @Fax,Email = @Email,FermierID = @FermierID,PersonaDeContact = @PersonaDeContact,TelPersoanaContact = @TelPersoanaContact,Ferme_CCL = @Ferme_CCL,JudetID =@JudetID,SendSms = @SendSms WHERE ID = @Id");
cmd.Parameters.AddWithValue("@ID", farm.Id);
cmd.Parameters.AddWithValue("@Cod", farm.Cod);
cmd.Parameters.AddWithValue("@Nume", farm.Nume);
cmd.Parameters.AddWithValue("@FabricaID", farm.FabricaId);
cmd.Parameters.AddWithValue("@Strada", farm.Strada);
cmd.Parameters.AddWithValue("@Numar", farm.Numar);
cmd.Parameters.AddWithValue("@Oras", farm.Oras);
cmd.Parameters.AddWithValue("@Judet", farm.Judet);
cmd.Parameters.AddWithValue("@CodPostal", farm.CodPostal);
cmd.Parameters.AddWithValue("@Telefon", farm.Telefon);
cmd.Parameters.AddWithValue("@Fax", farm.Fax);
cmd.Parameters.AddWithValue("@Email", farm.Email);
cmd.Parameters.AddWithValue("@FermierID", farm.FermierId);
cmd.Parameters.AddWithValue("@PersonaDeContact", farm.PersoanaDeContact);
cmd.Parameters.AddWithValue("@TelPersoanaContact", farm.TelPersoanaContact);
cmd.Parameters.AddWithValue("@Ferme_CCL", farm.Ferme);
cmd.Parameters.AddWithValue("@JudetID", farm.JudetId);
cmd.Parameters.AddWithValue("@SendSms", farm.SendSms);
cmd.Connection = cn;
cn.Open();
affectedRows = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
logger.Error(string.Format("UpdateFarm|farm:{0} ERROR:{1}", farm.Id, ex.Message));
}
finally
{
cn.Close();
}
return(affectedRows > 0);
}
public bool InsertFarm(Ferme_CCL farm)
{
var cn = new SqlConnection(connectionString);
int affectedRows = 0;
try
{
var cmd =
new SqlCommand(
"INSERT INTO Ferme_CCL(Cod,Nume,FabricaID,Strada,Numar,Oras,Judet,CodPostal,Telefon,Fax,Email,FermierID,PersonaDeContact,TelPersoanaContact,Ferme_CCL,JudetID,SendSms) VALUES (@Cod,@Nume,@FabricaID,@Strada,@Numar,@Oras,@Judet,@CodPostal,@Telefon,@Fax,@Email,@FermierID,@PersonaDeContact,@TelPersoanaContact,@Ferme_CCL,@JudetID,@SendSms)");
cmd.Parameters.AddWithValue("@Cod", farm.Cod);
cmd.Parameters.AddWithValue("@Nume", farm.Nume);
cmd.Parameters.AddWithValue("@FabricaID", farm.FabricaId);
cmd.Parameters.AddWithValue("@Strada", farm.Strada);
cmd.Parameters.AddWithValue("@Numar", farm.Numar);
cmd.Parameters.AddWithValue("@Oras", farm.Oras);
cmd.Parameters.AddWithValue("@Judet", farm.Judet);
cmd.Parameters.AddWithValue("@CodPostal", farm.CodPostal);
cmd.Parameters.AddWithValue("@Telefon", farm.Telefon);
cmd.Parameters.AddWithValue("@Fax", farm.Fax);
cmd.Parameters.AddWithValue("@Email", farm.Email);
cmd.Parameters.AddWithValue("@FermierID", farm.FermierId);
cmd.Parameters.AddWithValue("@PersonaDeContact", farm.PersoanaDeContact);
cmd.Parameters.AddWithValue("@TelPersoanaContact", farm.TelPersoanaContact);
cmd.Parameters.AddWithValue("@Ferme_CCL", farm.Ferme);
cmd.Parameters.AddWithValue("@JudetID", farm.JudetId);
cmd.Parameters.AddWithValue("@SendSms", farm.SendSms);
cmd.Connection = cn;
cn.Open();
affectedRows = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
logger.Error(string.Format("InsertFarm|farm:{0} ERROR:{1}", farm.Id, ex.Message));
}
finally
{
cn.Close();
}
return(affectedRows > 0);
}
public void VerificareBV(string filepath, string filescan)
{
string strConn;
int posOfSlash = filepath.LastIndexOf(@"\");
string dirServer = StaticDataHelper.SettingsManager.CaleFizicaServer;
loggerReceptie.Info("*************************************************************************************************");
loggerReceptie.Info("*************************************************************************************************");
loggerReceptie.Info("Verificare BV start pentru fisierul {0}", filescan);
SqlConnection cnn = new SqlConnection(CopConnectionString);
filepath = filepath.Replace(@"\", @"\\");
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filepath + ";" +
"Extended Properties='Excel 8.0;HDR=No;IMEX=1;READONLY=FALSE;'";
OleDbConnection cn;
DataTable dtTables;
cn = new OleDbConnection(strConn);
cn.Open();
dtTables = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string tblstr = dtTables.Rows[dtTables.Rows.Count - 1]["TABLE_NAME"].ToString();
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [" + tblstr + "]", cn);
OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
adapter.FillSchema(dataset, SchemaType.Source);
DataTable table;
table = dataset.Tables[0];
cn.Close();
filescan = filescan.Replace(@"\", @"\\");
string strConns = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filescan + ";" +
"Extended Properties='Excel 8.0;HDR=No;IMEX=1;READONLY=FALSE'";
OleDbConnection cns;
DataTable dtScanTables;
cns = new OleDbConnection(strConns);
cns.Open();
dtScanTables = cns.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string tblscan = dtScanTables.Rows[0]["TABLE_NAME"].ToString();
OleDbDataAdapter secCommand = new OleDbDataAdapter();
secCommand.SelectCommand = new OleDbCommand("SELECT * FROM [" + tblscan + "]", cns);
DataSet secDataSet = new DataSet();
secCommand.Fill(secDataSet);
DataTable scan;
scan = secDataSet.Tables[0];
cns.Close();
try
{
// For each row, print the values of each column.
string dataprelevare = "";
double litri = 0.0;
bool isNum;
string nrmatricol = "";
string codbare = "";
string prelevator = "";
string serieanaliza = "";
Ferme_CCL ferma = new Ferme_CCL();
string filename = Path.GetFileNameWithoutExtension(filescan);
string[] tokens = filename.Split('_');
string codexpl = string.Empty;
if (tokens.Count() > 2)
{
codexpl = tokens[2].Replace(" ", "").Replace("-", "");
}
if (string.IsNullOrEmpty(codexpl))
{
throw new Exception(string.Format("Nu am putut extrage cod de exploatatie din numele fisierului: {0}", filename));
}
loggerReceptie.Info("Am obtinut codul de exploatatie: {0} din numele fisierului: {1}", codexpl, filename);
string numeFerma;
int fermaid = ferma.GetFermeExpl(codexpl, out numeFerma);
if (fermaid <= 0)
{
throw new Exception(string.Format("Nu am gasit ID pentru codul de exploatatie: {0}", codexpl));
}
loggerReceptie.Info("Am gasit ID-ul de ferma: {0} cu numele {1} pentrul codul de exploatatie: {2}", fermaid, numeFerma, codexpl);
List<Order> orders = new List<Order>();
OrderManager oManager = new OrderManager(ctx);
for (int j = 1; j < scan.Rows.Count; j++)
{
DataRow row = scan.Rows[j];
serieanaliza = row[0].ToString().Trim();
nrmatricol = row[1].ToString().Trim();
codbare = row[2].ToString().Trim();
dataprelevare = row[3].ToString().Trim();
string cant = row[4].ToString().Trim();
prelevator = row[5].ToString().Trim();
if (cant.Length > 0)
{
isNum = double.TryParse(cant, NumberStyles.Integer, CultureInfo.CreateSpecificCulture("en-GB"),
out litri);
}
bool found = false;
if (codbare.Length > 0 && nrmatricol.Length > 0)
{
for (int i = 2; i < dataset.Tables[0].Rows.Count; i++)
{
if (codbare.Equals(dataset.Tables[0].Rows[i][4].ToString().Trim()))
{
found = true;
dataset.Tables[0].Rows[i][0] = serieanaliza;
dataset.Tables[0].Rows[i][3] = nrmatricol;
dataset.Tables[0].Rows[i][1] = prelevator;
dataset.Tables[0].Rows[i][5] = cant;
dataset.Tables[0].Rows[i][7] = dataprelevare;
dataset.Tables[0].Rows[i].AcceptChanges();
break;
}
}
if (!found)
{
loggerReceptie.Info("Codul de bare: {0} - {1} nu exista!", codbare, nrmatricol);
}
}
}
// adapter.Update(dataset.Tables[0]);
dataset.Tables[0].AcceptChanges();
dataset.AcceptChanges();
if (false)
{
WriteMergedFileForDebug(dataset);
}
table = dataset.Tables[0];
//
// For each row, print the values of each column.
DateTime dataPrelevare = DateTime.Now;
string ferma_ccl_cod = "";
string crot = "";
string rasa = "";
int ferma_ccl_id = 0;
int fabricaID = 0;
int header = 0;
bool error = false;
string strcodbare = "";
string prelevatorini = table.Rows[0][1].ToString();
DateTime datat_testare = new DateTime(int.Parse(tblstr.Substring(8, 4)), int.Parse(tblstr.Substring(6, 2)), int.Parse(tblstr.Substring(4, 2)));
error = !VerificareFisierReceptie(table, 8, 7);
if (error)
{
loggerReceptie.Error("Eroare la verificare fisierului, oprire import");
return;
}
foreach (DataRow row in table.Rows)
{
header++;
if (header > 2 && row[3].ToString().Trim() != "")
{
error = false;
crot = row[3].ToString().Trim();
strcodbare = row[4].ToString().Trim();
if (ExistaCodBare(strcodbare) != 0)
{
loggerReceptie.Info("Codul de bare: {0} exista deja!", strcodbare);
error = true;
}
else
{
// strDataPrelevare = row[5].ToString().Trim().Replace(@".", @"\");
//try
//{
dataPrelevare = DateTime.ParseExact(row[7].ToString().Trim().Substring(0, 10), "dd/MM/yyyy", new CultureInfo("fr-FR"));
//}
//catch (Exception de)
//{
// loggerReceptie.Error("Eroare la interpretarea date pentru crotalie: {0}, data: {1}, eroare: {2}", crot, dataPrelevare.ToShortDateString(), de.Message);
//}
prelevator = row[1].ToString().Trim();
string lit = row[5].ToString().Trim();
ferma_ccl_cod = crot;
ferma_ccl_id = fermaid;
if (ferma_ccl_id == 0)
{
error = true;
loggerReceptie.Error("Nu exista ferma pt. codul de exploatatie:" + codexpl);
}
else
{
string crotalia = ferma.GetFermeCrot(crot, fermaid);
if (crotalia == null || crotalia.Trim().Length == 0)
loggerReceptie.Error("Nu exista crotalie pt. codul: {0}, crotalia: {1}", strcodbare, crot);
}
string farmName;
fabricaID = ferma.GetFerma(ferma_ccl_id, out farmName);
rasa = ferma.GetFermeRasa(crot, fermaid);
if (!error)
{
try
{
string nrComanda = row[0].ToString().Trim();
DateTime dataPrimirii = DateTime.ParseExact(row[8].ToString().Trim(), "dd/MM/yyyy", new CultureInfo("fr-FR"));
AddMostra(0, Convert.ToInt32(row[2].ToString().Trim()), strcodbare, nrComanda,
ferma_ccl_cod, ferma_ccl_id, fabricaID, "", lit
, "0", "0", "0", "0", dataPrelevare, "0", dataPrimirii,
"0", datat_testare, "0", 0, prelevator, 0, 0, datat_testare, "0", "0", "0", rasa);
//loggerReceptie.Info("Mostra cod bare {0} pentru ferma Id {1}, fabrica Id {2}, nr comanda {3} adaugata cu succes.", strcodbare, ferma_ccl_id, fabricaID, nrComanda);
Order currentOrder = orders.FirstOrDefault(o => o.FullOrderNumber == nrComanda);
if (currentOrder != null)
{
currentOrder.SampleCount++;
currentOrder.AnalyzedSampleCount++;
}
else
{
string[] arr = nrComanda.Split('-', '/');
int orderNumber;
if (arr.Length == 3 && int.TryParse(arr[1], out orderNumber))
{
currentOrder = new Order
{
AnalyzedSampleCount = 1,
ClientId = fabricaID > 0 ? fabricaID : -1,
ClientName = farmName + " (" + fabricaID + ")",
FullOrderNumber = nrComanda,
Imported = true,
OrderNumber = orderNumber,
ReceivedDate = dataPrimirii,
SampleCount = 1,
SampleDate = dataPrelevare
};
orders.Add(currentOrder);
}
else
{
loggerReceptie.Error("Adaugare comanda esuata: {0}", nrComanda);
}
}
}
catch (Exception ex)
{
loggerReceptie.Error("Adaugare mostra esuata: {0} , eroare: {1}", strcodbare, ex.Message);
}
}
}
}
}
oManager.Save(orders);
ctx.SaveChanges();
loggerReceptie.Info("Importarea fisierului de receptie a fost efectuata!");
string filepath_ver = filepath.Replace(".xls", "_" + tblstr.Substring(1, tblstr.IndexOf("cop") - 5) + ".xls");
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText =
"SELECT NrComanda, NumePrelevator,IdZilnic,CodFerma,CodBare,CantitateLaPrelevare,DataPrelevare,DataPrimirii from MostreTancuri Where DataTestare = CONVERT(date, '" +
datat_testare.ToShortDateString() + "', 103)";
cnn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
table = ds.Tables[0];
CreateExcelFile(table, filepath_ver);
}
catch (Exception ex)
{
loggerReceptie.Error("Eroare receptie: {0}", ex.Message);
}
finally
{
cnn.Close();
cn.Close();
}
}
public void ImportFisReceptie(string filepath)
{
Ferme_CCL ferma = new Ferme_CCL();
bool error = false;
string strcodbare;
string strConn;
int posOfSlash = filepath.LastIndexOf(@"\");
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filepath + ";" +
"Extended Properties='Excel 8.0;HDR=No;IMEX=1'";
OleDbConnection cn;
DataTable dtTables;
cn = new OleDbConnection(strConn);
cn.Open();
dtTables = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string tblstr = dtTables.Rows[dtTables.Rows.Count - 1]["TABLE_NAME"].ToString();
string tbldate = tblstr.Substring(4, 8);
string filelogpath = filepath.Substring(0, filepath.Length - 4) + "_" + tbldate + ".txt";
string filelogInhibpath = filepath.Substring(0, filepath.Length - 4) + "_Inhib" + tbldate + ".txt";
string logfilename = filelogpath.Substring(posOfSlash + 1);
filelogpath = filelogpath.Replace(" ", "_");
logfilename = logfilename.Replace(" ", "_");
loggerReceptie.Info("*************************************************************************************************");
loggerReceptie.Info("*************************************************************************************************");
loggerReceptie.Info("Start import receptie fisier: {0}, data: {1}, numet sheet: {2}", filepath, DateTime.Now, tblstr);
// end log files
filepath = filepath.Replace(@"\", @"\\");
OleDbDataAdapter myCommand = new OleDbDataAdapter();
myCommand.SelectCommand = new OleDbCommand("SELECT * FROM [" + tblstr + "]", cn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet);
DataTable table;
table = myDataSet.Tables[0];
cn.Close();
// For each row, print the values of each column.
string ferma_ccl_cod = "";
string crot = "";
string rasa = "";
int ferma_ccl_id = 0;
int fabricaID = 0;
int header = 0;
string prelevator = "";
double prelid;
bool isNum;
string prelevatorini = table.Rows[0][1].ToString();
List<Order> orders = new List<Order>();
OrderManager oManager = new OrderManager(ctx);
if (!VerificareFisierReceptie(table, 6, 5))
{
loggerReceptie.Error("Eroare la verificare fisierului, oprire import");
return;
}
foreach (DataRow row in table.Rows)
{
header++;
if (header > 2 && row[3].ToString().Trim() != "")
{
error = false;
strcodbare = row[3].ToString().Trim();
if (ExistaCodBare(strcodbare) != 0)
{
loggerReceptie.Info("Codul de bare: {0} exista deja", strcodbare);
error = true;
}
else
{
DateTime datat_testare = new DateTime(int.Parse(tblstr.Substring(8, 4)),
int.Parse(tblstr.Substring(6, 2)), int.Parse(tblstr.Substring(4, 2)));
// strDataPrelevare = row[5].ToString().Trim().Replace(@".", @"\");
DateTime dataPrelevare = DateTime.ParseExact(row[5].ToString().Trim(), "dd/MM/yyyy", new CultureInfo("fr-FR"));
prelevator = row[1].ToString().Trim();
/*
if (prelevator == "")
prelevator = prelevatorini;
else
prelevatorini = prelevator;
*/
isNum = double.TryParse(prelevator, NumberStyles.Integer, CultureInfo.CreateSpecificCulture("en-GB"), out prelid);
if (!isNum)
{
loggerReceptie.Info("La codul de bare: {0} nu exista prelevator.", strcodbare);
error = true;
}
else
{
crot = strcodbare.Substring(0, 7);
// verific crotalia
ferma_ccl_cod = crot;
ferma_ccl_id = ferma.GetFermeId(crot);
rasa = ferma.GetFermeRasa(crot);
if (ferma_ccl_id == 0)
{
error = true;
loggerReceptie.Info("Nu exista crotalie pt. codul: {0}, crotalie: {1}", strcodbare, crot);
}
string farmName;
fabricaID = ferma.GetFerma(ferma_ccl_id, out farmName);
if (!error)
{
try
{
DateTime dataPrimirii = DateTime.ParseExact(row[6].ToString().Trim(), "dd/MM/yyyy", new CultureInfo("fr-FR"));
//DateTime dataPrimirii;
//if (!DateTime.TryParse(row[6].ToString().Trim(), new CultureInfo("fr-FR"), DateTimeStyles.None, out dataPrimirii))
//{
// dataPrimirii = DateTime.Now;
//}
string nrComanda = row[0].ToString().Trim();
AddMostra(0, Convert.ToInt32(row[2].ToString().Trim()), strcodbare, nrComanda,
ferma_ccl_cod, ferma_ccl_id, fabricaID, ""
, "0", "0", "0", "0", "0", dataPrelevare, "0", dataPrimirii,
"0", datat_testare, "0", Convert.ToInt32(prelevator), "", 0, 0, datat_testare, "0",
"0", "0", rasa);
loggerReceptie.Info(
"Mostra cod bare {0} pentru ferma Id {1}, fabrica Id {2}, nr comanda {3} adaugata cu succes.",
strcodbare, ferma_ccl_id, fabricaID, nrComanda);
Order currentOrder = orders.FirstOrDefault(o => o.FullOrderNumber == nrComanda);
if (currentOrder != null)
{
currentOrder.SampleCount++;
currentOrder.AnalyzedSampleCount++;
}
else
{
string[] arr = nrComanda.Split('-', '/');
int orderNumber;
if (arr.Length == 3 && int.TryParse(arr[1], out orderNumber))
{
//if (!DateTime.TryParse(row[5].ToString().Trim(), new CultureInfo("fr-FR"), DateTimeStyles.None, out dataPrelevare))
//{
// dataPrelevare = DateTime.Now;
//}
currentOrder = new Order
{
AnalyzedSampleCount = 1,
ClientId = fabricaID > 0 ? fabricaID : -1,
ClientName = farmName + " (" + fabricaID + ")",
FullOrderNumber = nrComanda,
Imported = true,
OrderNumber = orderNumber,
ReceivedDate = dataPrimirii,
SampleCount = 1,
SampleDate = dataPrelevare
};
orders.Add(currentOrder);
}
else
{
loggerReceptie.Info("Adaugare comanda esuata: {0}", nrComanda);
}
}
}
catch (Exception ex)
{
loggerReceptie.Info("Adaugare mostra esuata: {0}, err: {1}", strcodbare, ex.Message);
throw;
}
}
}
}
}
}
oManager.Save(orders);
ctx.SaveChanges();
loggerReceptie.Info("Importarea fisierului de receptie a fost efectuata!");
}
public void ImportFisReceptie(string filepath)
{
Ferme_CCL ferma = new Ferme_CCL();
bool error = false;
string strcodbare;
string strConn;
int posOfSlash = filepath.LastIndexOf(@"\");
string dirServer = StaticDataHelper.SettingsManager.CaleFizicaServer;
string saveDir = dirServer + @"Downloads\FisiereImportateAutomat";
if (!Directory.Exists(saveDir))
Directory.CreateDirectory(saveDir);
saveDir = saveDir + @"\";
// DataTable schemaTable;
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filepath + ";" + "Extended Properties='Excel 8.0;HDR=No;IMEX=1'";
//You must use the $ after the object you reference in the spreadsheet"+numesheet+"
// OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]",strConn);
OleDbConnection cn;
DataTable dtTables;
cn = new OleDbConnection(strConn);
cn.Open();
dtTables = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
string tblstr = dtTables.Rows[dtTables.Rows.Count - 1]["TABLE_NAME"].ToString();
// OleDbCommand myCommand = new OleDbCommand ("SELECT * FROM [" + tblstr+ " $]", cn);
// make log files
string tbldate = tblstr.Substring(4, 8);
loggerReceptie.Info("Data: " + DateTime.Now.ToString() + " nume sheet:" + tblstr);
loggerReceptie.Info("*****************************************************************************");
loggerReceptie.Info("*****************************************************************************");
loggerReceptie.Info("Data: " + DateTime.Now.ToString() + " nume sheet:" + tblstr);
// end log files
filepath = filepath.Replace(@"\", @"\\");
OleDbDataAdapter myCommand = new OleDbDataAdapter();
myCommand.SelectCommand = new OleDbCommand("SELECT * FROM [" + tblstr + "]", cn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet);
DataTable table;
table = myDataSet.Tables[0];
cn.Close();
// For each row, print the values of each column.
string strDataPrelevare;
string ferma_ccl_cod = "";
int ferma_ccl_id = 0;
int fabricaID = 0;
int header = 0;
string prelevator = "";
string datat_testare = "";
double prelid;
bool isNum;
string prelevatorini = table.Rows[0][1].ToString();
List<Order> orders = new List<Order>();
OrderManager oManager = new OrderManager(ctx);
foreach (DataRow row in table.Rows)
{
header++;
if (header > 2 && row[4].ToString().Trim() != "")
{
error = false;
strcodbare = row[4].ToString().Trim();
if (ExistaCodBare(strcodbare) != 0)
{
loggerReceptie.Info("Codul de bare:" + strcodbare + " exista deja");
error = true;
}
else
{
datat_testare = tblstr.Substring(4);
datat_testare = datat_testare.Substring(0, 2) + "/" + datat_testare.Substring(2, 2) + "/" + datat_testare.Substring(4, 4);
// strDataPrelevare = row[5].ToString().Trim().Replace(@".", @"\");
strDataPrelevare = row[7].ToString().Trim();
prelevator = row[1].ToString().Trim();
/*
if (prelevator == "")
prelevator = prelevatorini;
else
prelevatorini = prelevator;
*/
isNum = double.TryParse(prelevator, NumberStyles.Integer, CultureInfo.CurrentCulture, out prelid);
loggerReceptie.Info("Codul de bare: " + strcodbare + " are inhibitor:" + row[5].ToString().Trim());
string nrComanda = row[0].ToString().Trim();
string strDataPrimirii = row[8].ToString().Trim();
string numeFerma = string.Empty;
if (!isNum)
{
numeFerma = row[9].ToString().Trim();
AddMostraFCB(0, Convert.ToInt32(row[3].ToString().Trim()), strcodbare, nrComanda,
prelevator, row[2].ToString().Trim(), strDataPrelevare, strDataPrimirii,
datat_testare, datat_testare, numeFerma, row[10].ToString().Trim(),
row[11].ToString().Trim(), row[12].ToString().Trim(), 0, 0, "0", "0", "0", "0", "0", "0", row[5].ToString().Trim(), "0", "0", "0");
}
else
{
if (Convert.ToInt32(prelevator) == 0)
prelevator = strcodbare.Substring(0, 5);
ferma_ccl_cod = strcodbare.Substring(0, 5);
ferma_ccl_id = GetFermeCCL_ID(ferma_ccl_cod);
if (ferma_ccl_id == 0)
{
loggerReceptie.Info("La id Zilnic : " + row[3].ToString().Trim() + " Codul de ferma :" + ferma_ccl_cod + " nu exista !!! Mostra nu a fost importata");
error = true;
}
else
{
fabricaID = ferma.GetFerma(ferma_ccl_id, out numeFerma);
loggerReceptie.Info("Gasit Ferma id: {0}; nume: {1}", ferma_ccl_id, numeFerma);
}
if (!error)
try
{
AddMostra(0, Convert.ToInt32(row[3].ToString().Trim()), strcodbare, nrComanda, ferma_ccl_cod, ferma_ccl_id, fabricaID
, row[5].ToString().Trim(), "0", "0", "0", "0", "0", strDataPrelevare, "0", strDataPrimirii,
"0", datat_testare, "0", Convert.ToInt32(prelevator), 0, 0, datat_testare, "0", "0", "0");
}
catch (Exception ex)
{
loggerReceptie.Info("Adaugare mostra esuata:" + strcodbare + " " + ex.Message);
}
}
Order currentOrder = orders.FirstOrDefault(o => o.FullOrderNumber == nrComanda);
if (currentOrder != null)
{
currentOrder.SampleCount++;
currentOrder.AnalyzedSampleCount++;
}
else
{
string[] arr = nrComanda.Split('-', '/');
int orderNumber;
if (arr.Length == 2 && int.TryParse(arr[0], out orderNumber))
{
DateTime dataPrimirii;
if (!DateTime.TryParseExact(strDataPrimirii, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out dataPrimirii))
{
dataPrimirii = DateTime.Now;
}
DateTime dataPrelevare;
if (!DateTime.TryParseExact(strDataPrelevare, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out dataPrelevare))
{
dataPrelevare = DateTime.Now;
}
currentOrder = new Order
{
AnalyzedSampleCount = 1,
ClientId = fabricaID > 0 ? fabricaID : -1,
ClientName = numeFerma + " (" + fabricaID + ")",
FullOrderNumber = nrComanda,
Imported = true,
OrderNumber = orderNumber,
ReceivedDate = dataPrimirii,
SampleCount = 1,
SampleDate = dataPrelevare
};
orders.Add(currentOrder);
}
else
{
loggerReceptie.Info("Adaugare comanda esuata: " + nrComanda);
}
}
}
}
}
oManager.Save(orders);
ctx.SaveChanges();
loggerReceptie.Info("Importarea fisierului de receptie a fost efectuata!");
}
//
public void UpdatePrelevatori(string filepath)
{
Ferme_CCL ferma = new Ferme_CCL();
string strcodbare;
string strConn;
int posOfSlash = filepath.LastIndexOf(@"\");
string dirServer = StaticDataHelper.SettingsManager.CaleFizicaServer;
string filename = filepath.Substring(posOfSlash + 1);
loggerReceptie.Info("Start Actualizare prelevatori");
filepath = filepath.Replace(@"\", @"\\");
// DataTable schemaTable;
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filepath + ";" + "Extended Properties='Excel 8.0;HDR=No;IMEX=1'";
//You must use the $ after the object you reference in the spreadsheet"+numesheet+"
// OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]",strConn);
OleDbConnection cn;
DataTable dtTables;
cn = new OleDbConnection(strConn);
cn.Open();
dtTables = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
// string tblstr = dtTables.Rows[dtTables.Rows.Count - 1]["TABLE_NAME"].ToString();
// OleDbCommand myCommand = new OleDbCommand ("SELECT * FROM [" + tblstr+ " $]", cn);
for (int i = 0; i < dtTables.Rows.Count; i++)
{
string tblstr = dtTables.Rows[i]["TABLE_NAME"].ToString();
OleDbDataAdapter myCommand = new OleDbDataAdapter();
myCommand.SelectCommand = new OleDbCommand("SELECT * FROM [" + tblstr + "]", cn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet);
DataTable table;
table = myDataSet.Tables[0];
cn.Close();
// For each row, print the values of each column.
string strDataPrelevare;
int header = 0;
string prelevator = "";
string datat_testare = "";
double prelid;
bool isNum;
string prelevatorini = table.Rows[0][1].ToString();
foreach (DataRow row in table.Rows)
{
header++;
if (header > 2 && row[4].ToString().Trim() != "")
{
strcodbare = row[4].ToString().Trim();
datat_testare = tblstr.Substring(4);
datat_testare = datat_testare.Substring(0, 2) + "/" + datat_testare.Substring(2, 2) + "/" + datat_testare.Substring(4, 4);
strDataPrelevare = row[7].ToString().Trim();
prelevator = row[1].ToString().Trim();
if (prelevator == "")
{
prelevator = prelevatorini;
isNum = double.TryParse(prelevator, NumberStyles.Integer, CultureInfo.CurrentCulture, out prelid);
if (isNum)
{
UpdateMostraP(strcodbare, prelevator, false);
}
else
UpdateMostraP(strcodbare, prelevator, true);
loggerReceptie.Info("Codbare: " + strcodbare + " Prelevator: " + prelevator + "Datatestare :" + datat_testare);
}
else
prelevatorini = prelevator;
}
}
} //end update table
}