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