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!"); }