Example #1
0
        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");
            }
        }
Example #2
0
        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);
        }