public void Otvori(int godina)
{
ConvertXlsToCsv.Convert(ref put);
if (put == null)
{
return;
}
int rowsAffected = 0;
string constring = "datasource=localhost;port=3306;username=root;password=pass123";
MySqlConnection con = new MySqlConnection(constring);
string query = string.Format("INSERT INTO poreznaura.hzzo (datum, dokument, brojRn, " +
"datumRn, izvor, opis, iznos, placeniIznos, id) " +
"VALUES (@datum, @dokument, @brojRn, @datumRn, @izvor, " +
"@opis, @iznos, @placeniIznos, @identifikator);");
con.Open();
try
{
string[] lines = File.ReadAllLines(put);
for (int i = 9; i < lines.Length - 1; ++i)
{
string[] text = lines[i].Split(';', '\n');
string[] temp = text[2].Split('-');
if (text[5].Contains("CEZ"))
{
continue;
}
if (Int32.Parse(temp[2].Split('/')[1]) != godina)
{
continue;
}
string identifikator = temp[0] + text[1].Split('-')[2] + text[4].Split('/')[0];
int brRn = Int32.Parse(temp[0]);
long exists = 0;
MySqlConnection con2 = new MySqlConnection(constring);
con2.Open();
using (MySqlCommand com = new MySqlCommand($"SELECT COUNT(*) FROM poreznaura.hzzo WHERE id='{identifikator.Trim()}'", con2)) {
exists = (long)com.ExecuteScalar();
}
con2.Close();
if (exists != 0)
{
continue;
}
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("@datum", DateTime.ParseExact(text[0].ToString().Substring(0, 10), "dd.MM.yyyy", null));
cmd.Parameters.AddWithValue("@dokument", text[1].ToString());
cmd.Parameters.AddWithValue("@brojRn", brRn);
cmd.Parameters.AddWithValue("@datumRn", text[3].ToString().Substring(0, 10));
cmd.Parameters.AddWithValue("@izvor", text[4].ToString().Trim());
cmd.Parameters.AddWithValue("@opis", text[5].ToString().Trim());
cmd.Parameters.AddWithValue("@iznos", Convert.ToDouble(text[6].ToString().Trim()));
cmd.Parameters.AddWithValue("@placeniIznos", Convert.ToDouble(text[7].ToString().Trim()));
cmd.Parameters.AddWithValue("@identifikator", identifikator);
rowsAffected += cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
query = string.Format("CALL poreznaura.placeno();");
MySqlCommand call = new MySqlCommand(query, con);
call.ExecuteNonQuery();
con.Close();
MessageBox.Show($"Unešeno je {rowsAffected} redova u bazu");
}
}
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);
}