public void Upis(string put) { string constring = "datasource=localhost;port=3306;username=root;password=pass123"; MySqlConnection con = new MySqlConnection(constring); string query = string.Format("SELECT Rbr, Datum_racuna FROM poreznaura.ura ORDER BY Rbr DESC LIMIT 1;"); con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); var dt = new DataTable(); dt.Load(cmd.ExecuteReader()); var rows = dt.AsEnumerable().ToArray(); int zadnjiRed = 0; try { zadnjiRed = Convert.ToInt32(rows[0]["Rbr"]) == 0 ? -1 : Convert.ToInt32(rows[0]["Rbr"]); int godina = Convert.ToInt32(rows[0]["Datum_racuna"].ToString().Substring(0, 4)); } catch { } Poruka p = new Poruka(); p.Prikazi(); ConvertXlsToCsv.Convert(ref put); if (put == null) { return; } query = string.Format("INSERT INTO poreznaura.ura (Rbr, Datum_racuna, Broj_racuna, Za_uplatu, " + "Naziv_dobavljaca, Sjediste_dobavljaca, OIB, Iznos_s_porezom, Porezna_osn0, " + "Porezna_osn5, Porezna_osn13, Porezna_osn25, Ukupni_pretporez, por5, por13, por25, " + "br_primke, storno, odobr) " + "VALUES (@Rbr, @Datum_racuna, @Broj_racuna, @Za_uplatu, @Naziv_dobavljaca, " + "@Sjediste_dobavljaca, @OIB, @Iznos_s_porezom, @Porezna_osn0, @Porezna_osn5, " + "@Porezna_osn13, @Porezna_osn25, @Ukupni_pretporez, @por5, @por13, @por25, " + "@br_primke, @storno, @odobr);"); int rowsAffected = 0; try { string[] lines = File.ReadAllLines(put); foreach (string line in lines) { string[] text = line.Split(';', '\n'); if (text[0] == "" || !Char.IsDigit(text[0], 0)) { continue; } int trenutniRed = Int32.Parse(text[0]); if (zadnjiRed > 0 && zadnjiRed >= trenutniRed) { continue; } DateTime dt1 = DateTime.Parse(text[2]);//Datum koji se uzima sa csv text[6] = dt1.ToString("yyyy-MM-dd"); cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("@Rbr", text[1].ToString()); cmd.Parameters.AddWithValue("@Datum_racuna", text[6].ToString()); cmd.Parameters.AddWithValue("@Broj_racuna", text[3].ToString().Trim()); cmd.Parameters.AddWithValue("@Za_uplatu", Convert.ToDouble(text[11].ToString().Trim())); cmd.Parameters.AddWithValue("@Naziv_dobavljaca", text[12].ToString().Trim()); cmd.Parameters.AddWithValue("@br_primke", Convert.ToDouble(text[13].ToString().Trim())); cmd.Parameters.AddWithValue("@Sjediste_dobavljaca", text[16].ToString().Trim()); cmd.Parameters.AddWithValue("@OIB", text[17].ToString().Trim()); cmd.Parameters.AddWithValue("@Iznos_s_porezom", Convert.ToDouble(text[18].ToString().Trim())); cmd.Parameters.AddWithValue("@Porezna_osn0", Convert.ToDouble(text[19].ToString().Trim())); cmd.Parameters.AddWithValue("@Porezna_osn5", Convert.ToDouble(text[20].ToString().Trim())); cmd.Parameters.AddWithValue("@Porezna_osn13", Convert.ToDouble(text[24].ToString().Trim())); cmd.Parameters.AddWithValue("@Porezna_osn25", Convert.ToDouble(text[28].ToString().Trim())); cmd.Parameters.AddWithValue("@Ukupni_pretporez", Convert.ToDouble(text[30].ToString().Trim())); cmd.Parameters.AddWithValue("@por5", Convert.ToDouble(text[21].ToString().Trim())); cmd.Parameters.AddWithValue("@por13", Convert.ToDouble(text[25].ToString().Trim())); cmd.Parameters.AddWithValue("@por25", Convert.ToDouble(text[29].ToString().Trim())); cmd.Parameters.AddWithValue("@storno", text[5].ToString().Trim()); cmd.Parameters.AddWithValue("@odobr", text[37].ToString().Trim()); rowsAffected += cmd.ExecuteNonQuery(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } con.Close(); p.Zatvori(); MessageBox.Show($"Unešeno {rowsAffected} novih redaka.", "Unos", MessageBoxButtons.OK, MessageBoxIcon.Information); }
public static void Otvori(ref string put) { string constring = "datasource=localhost;port=3306;username=root;password=pass123"; MySqlConnection con = new MySqlConnection(constring); string query = string.Format("SELECT Rbr FROM poreznaura.ira ORDER BY Rbr DESC LIMIT 1;"); con.Open(); MySqlCommand cmd = new MySqlCommand(query, con); Object result = cmd.ExecuteScalar(); int zadnjiRed = 0; try { zadnjiRed = Convert.ToInt32(result) == 0 ? -1 : Convert.ToInt32(result); } catch { } Poruka p = new Poruka(); p.Prikazi(); ConvertXlsToCsv.Convert(ref put); if (put == null) { return; } query = "INSERT INTO poreznaura.ira (Rbr, datum_rn, br_rn, kupac, " + "iznos_uk, osn0, osn5, pdv5, osn13, " + "pdv13, osn25, pdv25, pdv_uk, storno_iz)" + "VALUES (@Rbr, @Datum_racuna, @Broj_racuna, @Kupac, @Iznos, " + "@Osn0, @Osn5, @Pdv5, @Osn13, @Pdv13, " + "@Osn25, @Pdv25, @Pdv_uk, @storno_iz);"; int rowsAffected = 0; try { string[] lines = File.ReadAllLines(put); foreach (string line in lines) { string[] text = line.Split(';', '\n'); if (text[0] == "" || !Char.IsDigit(text[0], 0)) { continue; } DateTime dt = DateTime.Parse(text[5]); text[5] = dt.ToString("yyyy-MM-dd"); int trenutniRed = Int32.Parse(text[0]); if (zadnjiRed > 0 && zadnjiRed >= trenutniRed) { continue; } cmd = new MySqlCommand(query, con); cmd.Parameters.AddWithValue("@Rbr", text[0]); cmd.Parameters.AddWithValue("@Datum_racuna", text[5].ToString()); cmd.Parameters.AddWithValue("@Broj_racuna", text[2].ToString().Trim()); cmd.Parameters.AddWithValue("@Kupac", text[8].ToString().Trim()); cmd.Parameters.AddWithValue("@Iznos", Convert.ToDouble(text[10].ToString().Trim())); cmd.Parameters.AddWithValue("@Osn0", Convert.ToDouble(text[14].ToString().Trim())); cmd.Parameters.AddWithValue("@Osn5", Convert.ToDouble(text[15].ToString().Trim())); cmd.Parameters.AddWithValue("@Pdv5", Convert.ToDouble(text[16].ToString().Trim())); cmd.Parameters.AddWithValue("@Osn13", Convert.ToDouble(text[19].ToString().Trim())); cmd.Parameters.AddWithValue("@Pdv13", Convert.ToDouble(text[20].ToString().Trim())); cmd.Parameters.AddWithValue("@Osn25", Convert.ToDouble(text[23].ToString().Trim())); cmd.Parameters.AddWithValue("@Pdv25", Convert.ToDouble(text[24].ToString().Trim())); cmd.Parameters.AddWithValue("@Pdv_uk", Convert.ToDouble(text[25].ToString().Trim())); cmd.Parameters.AddWithValue("@storno_iz", Convert.ToInt32(text[4].ToString().Trim())); rowsAffected += cmd.ExecuteNonQuery(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } con.Close(); p.Zatvori(); MessageBox.Show($"Unešeno {rowsAffected} novih redova.", "Unos", MessageBoxButtons.OK, MessageBoxIcon.Information); }