예제 #1
0
        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);
        }
예제 #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);
        }