private void kreirajTablice() { DataTable DTremote = RemoteDB.select("select table_name from information_schema.tables where TABLE_TYPE <> 'VIEW'", "Table").Tables[0]; if (DTremote.Rows.Count > 0) { DataRow[] dataROW = DTremote.Select("table_name = 'avansi'"); if (dataROW.Length == 0) { string sql = "CREATE TABLE avansi" + "(broj_avansa bigint NOT NULL," + "dat_dok timestamp without time zone," + "dat_knj timestamp without time zone," + "id_zaposlenik integer," + "id_zaposlenik_izradio integer," + "model character varying(10)," + "id_nacin_placanja bigint," + "id_valuta integer," + "opis text," + "id_vd character(5)," + "godina_avansa character(6)," + "ukupno numeric," + "ziro character varying(30)," + "nult_stp numeric," + "neoporezivo numeric," + "osnovica10 numeric," + "osnovica_var numeric," + "porez_var numeric," + "id_pdv integer," + "CONSTRAINT broj_avansa PRIMARY KEY (broj_avansa )" + ")"; RemoteDB.select(sql, "avansi"); } dataROW = DTremote.Select("table_name = 'avansi_vd'"); if (dataROW.Length == 0) { string sql = "CREATE TABLE avansi_vd" + "id_vd bigint NOT NULL," + "vd character varying(30)," + "grupa character varying(5)," + "CONSTRAINT primary_key_id_vd PRIMARY KEY (id_vd )" + ")"; RemoteDB.select(sql, "avansi_vd"); RemoteDB.insert("INSERT INTO avansi_vd (id_vd,vd,grupa) VALUES ('Predujam','IP')"); RemoteDB.insert("INSERT INTO avansi_vd (id_vd,vd,grupa) VALUES ('Storno primljenog predujma','PRS')"); } } }
private void stornirajAvansHelper(string broj) { DataTable DSracun = RemoteDB.select("SELECT storno FROM avansi where broj_avansa='" + broj + "'", "avansi").Tables[0]; if (DSracun.Rows[0]["storno"].ToString() == "DA") { MessageBox.Show("Avans je već storniran!"); this.ActiveControl = textBox1; textBox1.SelectAll(); } else { stornirajAvans(broj); MessageBox.Show("Izvršeno."); this.Close(); } }
private void frmMeni_Load(object sender, EventArgs e) { if (DateTime.Now.Year < 2017) { ukupnoPoNacinimaPlacanjaToolStripMenuItem.Enabled = false; ukupnoPoNacinimaPlacanjaToolStripMenuItem.Visible = false; } Class.PostavkeFiskalizacije.getPodaci(); Paint += new PaintEventHandler(Funkcije.Form1_Paint); avansToolStripMenuItem.Visible = false; panelNatpisi.Hide(); try { if (!File.Exists("log.txt")) { File.WriteAllText("log.txt", ""); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } Properties.Settings.Default.verzija_programa = 1.1m; Properties.Settings.Default.Save(); timerBackup.Start(); timerBackup.Interval = 1800000; ClassProvjeraBaze.ProvjeraTablica(); DTBojeForme = classDBlite.LiteSelect("SELECT * FROM FormColors", "FormColors").Tables[0]; DataTable DTremote = RemoteDB.select("select table_name from information_schema.tables where TABLE_TYPE <> 'VIEW'", "Table").Tables[0]; SetCB(); SetGosti(DateTime.Now.Month); ucitano = true; WeatherSet(); Forme.frmKasaPrijava kasaPrijava = new Forme.frmKasaPrijava(); kasaPrijava.ShowDialog(); timerWeather.Start(); }
private void button4_Click(object sender, EventArgs e) { if (MessageBox.Show("Da li ste sigurni da želite obrisati ovaj avans?", "Upozorenje", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes) { RemoteDB.delete(string.Format("DELETE FROM avansi_stavke WHERE broj_avansa = '{0}';", txtBrojAvans.Text)); RemoteDB.delete(string.Format("DELETE FROM avansi WHERE broj_avansa = '{0}';", txtBrojAvans.Text)); RemoteDB.insert(string.Format(@"INSERT INTO aktivnost_zaposlenici (id_zaposlenik,datum,radnja) VALUES ('{0}', '{1}', 'Brisanje cijele fakture br. {2}');", Properties.Settings.Default.id_zaposlenik, DateTime.Now.ToString("yyyy-MM-dd H:mm:ss"), txtBrojAvans.Text)); MessageBox.Show("Obrisano."); edit = false; EnableDisable(false); ControlDisableEnable(true, false, false, true, true, true); deleteFields(); } }
private void button2_Click(object sender, EventArgs e) { frmPartnerTrazi partnerTrazi = new frmPartnerTrazi(); partnerTrazi.ShowDialog(); if (Properties.Settings.Default.id_partner != "") { DataSet partner = new DataSet(); partner = RemoteDB.select("SELECT id_partner,ime_tvrtke FROM partners WHERE id_partner ='" + Properties.Settings.Default.id_partner + "'", "partners"); if (partner.Tables[0].Rows.Count > 0) { txtAvansirati.Text = partner.Tables[0].Rows[0]["id_partner"].ToString(); txtPartner.Text = partner.Tables[0].Rows[0]["ime_tvrtke"].ToString(); } else { MessageBox.Show("Upisana šifra ne postoji.", "Greška"); } } }
private void fillCB() { //fill valuta DSValuta = RemoteDB.select("SELECT * FROM valute", "valute"); cbValuta.DataSource = DSValuta.Tables[0]; cbValuta.DisplayMember = "ime_valute"; cbValuta.ValueMember = "id_valuta"; cbValuta.SelectedValue = 5; //fill vrsta dokumenta DSvd = RemoteDB.select("SELECT * FROM avansi_vd ORDER BY id_vd", "avansi_vd"); cbVD.DataSource = DSvd.Tables[0]; cbVD.DisplayMember = "vd"; cbVD.ValueMember = "id_vd"; //fill komercijalist DS_Zaposlenik = RemoteDB.select("SELECT ime + ' ' + prezime as IME, id_zaposlenik FROM zaposlenici", "zaposlenici"); cbIzradio.DataSource = DS_Zaposlenik.Tables[0]; cbIzradio.DisplayMember = "IME"; cbIzradio.ValueMember = "id_zaposlenik"; }
private void PostavaCijene() { DataTable DTsobe = RemoteDB.select("SELECT * FROM sobe WHERE id_tip_sobe='7' AND id<>'5'", "r_cijenasoba").Tables[0]; for (int s = 0; s < DTsobe.Rows.Count; s++) { DataTable DT = RemoteDB.select("SELECT * FROM r_cijenasoba WHERE id_soba='5'", "r_cijenasoba").Tables[0]; for (int i = 0; i < DT.Rows.Count; i++) { string sss = "INSERT INTO R_CijenaSoba (broj_sobe,id_soba,id_valuta,cijena_nocenja,od_datuma,do_datuma) VALUES (" + "'" + DTsobe.Rows[s]["broj_sobe"].ToString() + "'," + "'" + DTsobe.Rows[s]["id"].ToString() + "'," + "'" + DT.Rows[i]["id_valuta"].ToString() + "'," + "'" + DT.Rows[i]["cijena_nocenja"].ToString().Replace(",", ".") + "'," + "'" + DT.Rows[i]["od_datuma"].ToString() + "'," + "'" + DT.Rows[i]["do_datuma"].ToString() + "'" + ")"; RemoteDB.insert(sss); } } }
private void fillComboBox() { numGodina.Value = Convert.ToInt16(DateTime.Now.Year.ToString()); //fill ziroracun DS_ZiroRacun = RemoteDB.select("SELECT * FROM ziro_racun", "ziro_racun"); cbZiroRacun.DataSource = DS_ZiroRacun.Tables[0]; cbZiroRacun.DisplayMember = "ziroracun"; cbZiroRacun.ValueMember = "id_ziroracun"; cbZiroRacun.SelectedValue = "1"; //fill nacin_placanja DSnazivPlacanja = RemoteDB.select("SELECT * FROM nacin_placanja", "nacin_placanja"); cbNacinPlacanja.DataSource = DSnazivPlacanja.Tables[0]; cbNacinPlacanja.DisplayMember = "naziv_placanja"; cbNacinPlacanja.ValueMember = "id_placanje"; cbNacinPlacanja.SelectedValue = 3; txtSifraNacinPlacanja.Text = cbNacinPlacanja.SelectedValue.ToString(); //DS Valuta DSValuta = RemoteDB.select("SELECT * FROM valute", "valute"); cbValuta.DataSource = DSValuta.Tables[0]; cbValuta.DisplayMember = "ime_valute"; cbValuta.ValueMember = "id_valuta"; cbValuta.SelectedValue = 5; txtTecaj.DataBindings.Add("Text", DSValuta.Tables[0], "tecaj"); txtTecaj.Text = "1"; //DS PDV DSPDV = RemoteDB.select("SELECT * FROM porezi", "porezi"); cbPDV.DataSource = DSPDV.Tables[0]; cbPDV.DisplayMember = "iznos"; cbPDV.ValueMember = "id_porez"; cbPDV.SelectedValue = 1; //fill tko je prijavljen txtIzradio.Text = RemoteDB.select(string.Format(@"SELECT concat(ime, ' ', prezime) as Ime FROM zaposlenici WHERE id_zaposlenik = '{0}';", Properties.Settings.Default.id_zaposlenik), "zaposlenici").Tables[0].Rows[0][0].ToString(); }
private void stornirajAvans(string stariBroj) { //ubaci u racuni i vrati IznosGotovina, IznosKartica, ukupno i novi racun DataTable DTracun = RemoteDB.select("SELECT * FROM avansi where broj_avansa='" + stariBroj + "'", "avansi").Tables[0]; string noviAvans = brojAvansa(); if (noviAvans != "") { noviAvans = (Convert.ToInt64(noviAvans) + 1).ToString(); } else { MessageBox.Show("U bazi ne postoji niti jedan avans!"); return; } //priprema za fiskalizaciju DataTable DTnaknade = new DataTable(); DataTable DTOstaliPor = new DataTable(); bool pdv = ini.Read("Postavke", "u_sustavu_pdva") == "1" ? true : false; string oib = DToib.Rows.Count > 0 ? DToib.Rows[0][0].ToString() : ""; string iznososlobpdv = ""; string iznos_marza = ""; double Porez_potrosnja_sve = 0; string[] porezNaPotrosnju = setPorezNaPotrosnju(); dodajKoloneDTpdv(); DTpdv.Clear(); //priprema za fiskalizaciju double osnovica_sve = 0; double osnovicaVar = Convert.ToDouble(DTracun.Rows[0]["osnovica_var"].ToString()); double porezVar = Convert.ToDouble(DTracun.Rows[0]["porez_var"].ToString()); double osnovica10 = Convert.ToDouble(DTracun.Rows[0]["osnovica10"].ToString()); double porez10 = osnovica10 * .1; //dodajPDV(porez10, osnovica10); //dodajPDV(porezVar, osnovicaVar); if (osnovica10 != 0) { dodajPDV(10.00, Math.Round(osnovica10 * -1, 2)); } if (osnovicaVar != 0) { dodajPDV(Math.Round(porezVar / osnovicaVar, 4) * 100, Math.Round(osnovicaVar * -1, 2)); } osnovica_sve += osnovica10 + osnovicaVar; porezNaPotrosnju[0] = "0"; porezNaPotrosnju[1] = Convert.ToString(osnovica_sve); porezNaPotrosnju[2] = Convert.ToString(Porez_potrosnja_sve); DataTable DTnp = RemoteDB.select("SELECT naziv_placanja FROM nacin_placanja where id_placanje='" + DTracun.Rows[0]["id_nacin_placanja"].ToString() + "'", "avansi").Tables[0]; string npId = DTnp.Rows.Count > 0 ? DTnp.Rows[0][0].ToString() : ""; string np; switch (npId.ToLower()) { case "gotovina": np = "G"; break; case "kartica": np = "K"; break; case "virman": np = "T"; break; default: np = "O"; break; } DateTime datum = DateTime.Now; //string[] fiskalizacija = new string[3]; string[] fiskalizacija = Fiskalizacija.classFiskalizacija.Fiskalizacija( DTtvrtka.Rows[0]["oib"].ToString(), oib, datum, Convert.ToInt32(noviAvans), DTpdv, porezNaPotrosnju, DTOstaliPor, iznososlobpdv, iznos_marza, DTnaknade, Convert.ToDecimal(DTracun.Rows[0]["ukupno"].ToString()) * -1,//.ToString().Replace(",", ".") np, false, "A" ); string sql = "INSERT INTO avansi (broj_avansa,dat_dok,dat_knj,id_zaposlenik,id_zaposlenik_izradio,model" + ",id_nacin_placanja,id_valuta,opis,id_vd,godina_avansa,ukupno,ziro,nult_stp,neoporezivo," + "osnovica10,osnovica_var,porez_var,id_pdv,id_partner,jir,zki,storno) VALUES " + " (" + " '" + noviAvans + "'," + " '" + datum.ToString("yyyy-MM-dd H:mm:ss") + "'," + " '" + DTracun.Rows[0]["dat_knj"].ToString() + "'," + " '" + "0" + "'," + " '" + Properties.Settings.Default.id_zaposlenik + "'," + " '" + DTracun.Rows[0]["model"].ToString() + "'," + " '" + DTracun.Rows[0]["id_nacin_placanja"].ToString() + "'," + " '" + DTracun.Rows[0]["id_valuta"].ToString() + "'," + " '" + DTracun.Rows[0]["opis"].ToString() + "'," + " '" + DTracun.Rows[0]["id_vd"].ToString() + "'," + " '" + DTracun.Rows[0]["godina_avansa"].ToString() + "'," + " '" + (Convert.ToDecimal(DTracun.Rows[0]["ukupno"].ToString()) * -1).ToString().Replace(",", ".") + "'," + " '" + DTracun.Rows[0]["ziro"].ToString() + "'," + " '" + (Convert.ToDecimal(DTracun.Rows[0]["nult_stp"].ToString()) * -1).ToString().Replace(",", ".") + "'," + " '" + (Convert.ToDecimal(DTracun.Rows[0]["neoporezivo"].ToString()) * -1).ToString().Replace(",", ".") + "'," + " '" + (Convert.ToDecimal(DTracun.Rows[0]["osnovica10"].ToString()) * -1).ToString().Replace(",", ".") + "'," + " '" + (Convert.ToDecimal(DTracun.Rows[0]["osnovica_var"].ToString()) * -1).ToString().Replace(",", ".") + "'," + " '" + (Convert.ToDecimal(DTracun.Rows[0]["porez_var"].ToString()) * -1).ToString().Replace(",", ".") + "'," + " '" + DTracun.Rows[0]["id_pdv"].ToString() + "', " + " '" + DTracun.Rows[0]["id_partner"].ToString() + "', " + " '" + fiskalizacija[0] + "', " + " '" + fiskalizacija[1] + "', " + " 'NE' " + ")"; provjera_sql(RemoteDB.insert(sql)); string sqlStorno = "UPDATE avansi SET storno='DA' WHERE broj_avansa='" + stariBroj + "'"; RemoteDB.update(sqlStorno); sql = "INSERT INTO aktivnost_zaposlenici (id_zaposlenik,datum,radnja)" + " VALUES ('" + Properties.Settings.Default.id_zaposlenik + "','" + DateTime.Now.ToString("yyyy-MM-dd H:mm:ss") + "'," + "'Storno avansa br." + stariBroj + "')"; provjera_sql(RemoteDB.insert(sql)); }
private void pictureBox1_Click(object sender, EventArgs e) { DateTime dOtp = Convert.ToDateTime(dtpOD.Value); string dtOd = dOtp.Month + "." + dOtp.Day + "." + dOtp.Year; DateTime dNow = Convert.ToDateTime(dtpDO.Value); string dtDo = dNow.Month + "." + dNow.Day + "." + dNow.Year; string Broj = ""; string Partner = ""; string DateStart = ""; string DateEnd = ""; string VD = ""; string Valuta = ""; string Izradio = ""; string SifraArtikla = ""; if (chbBroj.Checked) { Broj = "avansi.broj_avansa = '" + txtBroj.Text + "' AND "; } if (chbVD.Checked) { VD = "avansi.id_vd = '" + cbVD.SelectedValue.ToString() + "' AND "; } if (chbOD.Checked) { DateStart = "avansi.dat_knj >= '" + dtpOD.Value.ToString("yyyy-MM-dd H:mm:ss") + "' AND "; } if (chbDO.Checked) { DateEnd = "avansi.dat_knj <= '" + dtpDO.Value.ToString("yyyy-MM-dd H:mm:ss") + "' AND "; } if (chbValuta.Checked) { Valuta = "avansi.id_valuta = '" + cbValuta.SelectedValue.ToString() + "' AND "; } if (chbIzradio.Checked) { Izradio = "avansi.id_zaposlenik_izradio='" + cbIzradio.SelectedValue + "' AND "; } string filter = Broj + Partner + VD + DateStart + DateEnd + Valuta + SifraArtikla + Izradio; if (filter.Length != 0) { filter = " WHERE " + filter; filter = filter.Remove(filter.Length - 4, 4); } string top = ""; string remote = ""; if (RemoteDB.remoteConnectionString != "") { remote = " LIMIT 500"; } else { top = " TOP(500) "; } string sql = "SELECT DISTINCT " + top + " avansi.broj_avansa AS [Broj avansa], avansi.dat_knj AS [Datum knjiženja], avansi.dat_dok as [Datum], valute.ime_valute as [Valuta]" + ", avansi.godina_avansa as [Godina], avansi.ukupno as [Ukupno] " + " FROM avansi INNER JOIN valute ON avansi.id_valuta = valute.id_valuta " + filter + " ORDER BY avansi.dat_knj DESC" + remote; DSavansi = RemoteDB.select(sql, "avansi"); dgv.DataSource = DSavansi.Tables[0]; SetDecimalInDgv(dgv, "Ukupno", "NE", "NE"); }
private void btnSpremi_Click(object sender, EventArgs e) { //PROVJERA! if (textUkupno.Text.Trim() == "" || c.ukupno == 0) { if (MessageBox.Show("Da li ste sigurni da želite spremiti avans s ovim iznosom?", "Upozorenje", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.No) { textUkupno.Focus(); return; } } //ako se editira avans try { int avans = Convert.ToInt16(txtBrojAvans.Text); } catch { MessageBox.Show("Unesi numeričku vrijednost za broj avansa!"); txtBrojAvans.Focus(); return; } try { int avans = Convert.ToInt32(txtAvansirati.Text); } catch { MessageBox.Show("Unesi numeričku vrijednost za partnera!", "Greška"); txtAvansirati.Focus(); return; } DataTable DSpar = RemoteDB.select(string.Format(@"SELECT ime_tvrtke FROM partners WHERE id_partner = '{0}';", txtAvansirati.Text), "partners").Tables[0]; if (DSpar.Rows.Count < 1) { MessageBox.Show("Partner pod tim brojem ne postoji!", "Greška"); txtAvansirati.Focus(); return; } string sql = string.Format(@"SELECT * from avansi where broj_avansa = '{0}' AND godina_avansa = '{1}';", txtBrojAvans.Text, numGodina.Value.ToString()); DSAvansi = RemoteDB.select(sql, "avansi").Tables[0]; if (DSAvansi.Rows.Count > 0) { sql = string.Format(@"UPDATE avansi SET broj_avansa = '{0}', dat_dok = '{1}', dat_knj = '{2}', id_zaposlenik = '0', id_zaposlenik_izradio = '{3}', model = '{4}', id_nacin_placanja = '{5}', id_valuta = '{6}', opis = '{7}', godina_avansa = '{8}', ukupno = '{9}', ziro = '{10}', nult_stp = '{11}', neoporezivo = '{12}', osnovica10 = '{13}', osnovica_var = '{14}', porez_var = '{15}', id_pdv = '{16}', storno = '{17}', id_partner = '{18}' WHERE broj_avansa = '{0}' AND godina_avansa = '{8}';", txtBrojAvans.Text, dateTimePicker1.Value.ToString("yyyy-MM-dd H:mm:ss"), dateTimePicker2.Value.ToString("yyyy-MM-dd H:mm:ss"), Properties.Settings.Default.id_zaposlenik, txtModel.Text, cbNacinPlacanja.SelectedValue.ToString(), cbValuta.SelectedValue.ToString(), rtbOpis.Text, numGodina.Value.ToString(), Math.Round(c.ukupno, 2).ToString("#0.00").Replace(",", "."), cbZiroRacun.SelectedValue.ToString(), Math.Round(c.nultaStp, 2).ToString("#0.00").Replace(",", "."), Math.Round(c.neoporezivo, 2).ToString("#0.00").Replace(",", "."), Math.Round(c.osnovica10, 2).ToString("#0.00").Replace(",", "."), Math.Round(c.osnovicaVar, 2).ToString("#0.00").Replace(",", "."), Math.Round(c.porezVar, 2).ToString("#0.00").Replace(",", "."), cbPDV.SelectedValue.ToString(), storno, txtAvansirati.Text.Trim()); provjera_sql(RemoteDB.update(sql)); provjera_sql(RemoteDB.insert(string.Format(@"INSERT INTO aktivnost_zaposlenici (id_zaposlenik,datum,radnja) VALUES ('{0}', '{1}', 'Edit avans br. {2}');", Properties.Settings.Default.id_zaposlenik, DateTime.Now.ToString("yyyy-MM-dd H:mm:ss"), txtBrojAvans.Text))); MessageBox.Show("Spremljeno."); EnableDisable(false); ControlDisableEnable(true, false, false, true, true, true); return; } //ako se editira avans //priprema za fiskalizaciju DataTable DTnaknade = new DataTable(); DataTable DTOstaliPor = new DataTable(); bool pdv = ini.Read("Postavke", "u_sustavu_pdva") == "1" ? true : false; string oib = DToib.Rows.Count > 0 ? DToib.Rows[0][0].ToString() : ""; string iznososlobpdv = ""; string iznos_marza = ""; double osnovica_sve = 0; double Porez_potrosnja_sve = 0; string[] porezNaPotrosnju = setPorezNaPotrosnju(); dodajKoloneDTpdv(); DTpdv.Clear(); if (c.osnovica10 != 0) { dodajPDV(10.00, Math.Round(c.osnovica10, 2)); } if (c.osnovicaVar != 0) { dodajPDV(Math.Round(c.porezVar / c.osnovicaVar, 4) * 100, Math.Round(c.osnovicaVar, 2)); } osnovica_sve += c.osnovica10 + c.osnovicaVar; porezNaPotrosnju[0] = "0"; porezNaPotrosnju[1] = Convert.ToString(osnovica_sve); porezNaPotrosnju[2] = Convert.ToString(Porez_potrosnja_sve); //priprema za fiskalizaciju string np; switch (cbNacinPlacanja.Text.ToLower()) { case "gotovina": np = "G"; break; case "kartica": np = "K"; break; case "virman": np = "T"; break; default: np = "O"; break; } DateTime datum = dateTimePicker1.Value; //string[] fiskalizacija = new string[3]; string[] fiskalizacija = Fiskalizacija.classFiskalizacija.Fiskalizacija( DTtvrtka.Rows[0]["oib"].ToString(), oib, datum, Convert.ToInt32(txtBrojAvans.Text), DTpdv, porezNaPotrosnju, DTOstaliPor, iznososlobpdv, iznos_marza, DTnaknade, Convert.ToDecimal(c.ukupno), np, false, "A" ); //za novi avans sql = string.Format(@"INSERT INTO avansi ( broj_avansa, dat_dok, dat_knj, id_zaposlenik, id_zaposlenik_izradio, model, id_nacin_placanja, id_valuta, opis, godina_avansa, ukupno, ziro, nult_stp, neoporezivo, osnovica10, osnovica_var, porez_var, id_pdv, id_partner, jir, zki, storno ) VALUES ( '{0}', '{1}', '{2}', '0', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}', '{19}', 'NE' );", txtBrojAvans.Text, datum.ToString("yyyy-MM-dd H:mm:ss"), dateTimePicker2.Value.ToString("yyyy-MM-dd H:mm:ss"), Properties.Settings.Default.id_zaposlenik, txtModel.Text, cbNacinPlacanja.SelectedValue.ToString(), cbValuta.SelectedValue.ToString(), rtbOpis.Text, numGodina.Value.ToString(), Math.Round(c.ukupno, 2).ToString("#0.00").Replace(",", "."), cbZiroRacun.SelectedValue.ToString(), Math.Round(c.nultaStp, 2).ToString("#0.00").Replace(",", "."), Math.Round(c.neoporezivo, 2).ToString("#0.00").Replace(",", "."), Math.Round(c.osnovica10, 2).ToString("#0.00").Replace(",", "."), Math.Round(c.osnovicaVar, 2).ToString("#0.00").Replace(",", "."), Math.Round(c.porezVar, 2).ToString("#0.00").Replace(",", "."), cbPDV.SelectedValue.ToString(), txtAvansirati.Text.Trim(), fiskalizacija[0], fiskalizacija[1]); provjera_sql(RemoteDB.insert(sql)); provjera_sql(RemoteDB.insert(string.Format(@"INSERT INTO aktivnost_zaposlenici (id_zaposlenik, datum, radnja) VALUES ( '{0}', '{1}', 'Novi avans br. {2}');", Properties.Settings.Default.id_zaposlenik, DateTime.Now.ToString("yyyy-MM-dd H:mm:ss"), txtBrojAvans.Text))); if (MessageBox.Show("Spremljeno.\r\nŽelite li ispisati dokumenat?", "Spremljeno", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { printaj(txtBrojAvans.Text, numGodina.Value.ToString()); } EnableDisable(false); ControlDisableEnable(true, false, false, false, true, false); deleteFields(); }
public static void ProvjeraTablica() { try { DataSet DS_podaciTtvrtke = classDBlite.LiteSelect("SELECT id FROM temp_podaci_tvrtke;", "temp_podaci_tvrtke"); } catch { string sqlPT = string.Format(@"create table temp_podaci_tvrtke as select * from podaci_tvrtke; drop table podaci_tvrtke; CREATE TABLE [podaci_tvrtke] ( [id] integer PRIMARY KEY AUTOINCREMENT NOT NULL, [ime_tvrtke] varchar(100), [oib] varchar(50), [tel] varchar(100), [fax] varchar(100), [mob] varchar(100), [iban] varchar(100), [iban1] varchar(100), [adresa] varchar(200), [grad] varchar(100), [vl] varchar(100), [poslovnica_adresa] varchar(200), [poslovnica_grad] varchar(100), [email] varchar(200), [opis_na_kraju_fakture] text, [r1] varchar(50), [skraceno_ime] varchar(200) ); insert into podaci_tvrtke select * from temp_podaci_tvrtke; "); classDBlite.LiteSqlCommand(sqlPT); } DataTable DTpostavke = classDBlite.LiteSelect("SELECT * FROM postavke", "postavke").Tables[0]; if (DTpostavke.Columns["naplata_po_sobi"] == null) { string sql = "ALTER TABLE postavke ADD COLUMN naplata_po_sobi int;"; classDBlite.LiteSqlCommand(sql); } if (DTpostavke.Columns["path_image"] == null) { string sql = "ALTER TABLE postavke ADD COLUMN path_image varchar;"; classDBlite.LiteSqlCommand(sql); } if (DTpostavke.Columns["bool_path_image"] == null) { string sql = "ALTER TABLE postavke ADD COLUMN bool_path_image int;"; classDBlite.LiteSqlCommand(sql); } DataTable DTremote = RemoteDB.select("select table_name, column_name, data_type, character_maximum_length from information_schema.columns", "Table").Tables[0]; if (DTremote.Select("table_name='grad' AND column_name='id_drzava'").Length == 0) { string sql = "ALTER TABLE grad ADD COLUMN id_drzava INT DEFAULT 60;"; RemoteDB.insert(sql); } if (DTremote.Rows.Count > 0) { if (DTremote.Select("table_name = 'Rfakture'").Length == 0) { string sql = "CREATE TABLE Rfakture" + " (id serial NOT NULL," + " broj int," + " godina int," + " jir character varying(100)," + " zik character varying(100)," + " datumDVO timestamp without time zone," + " datum timestamp without time zone," + " datum_valute timestamp without time zone," + " valuta numeric," + " id_valuta int," + " id_partner int," + " broj_lezaja numeric," + " nacin_placanja int," + " id_izradio int," + " model character varying(10)," + " storno character varying(2)," + " napomena text," + " ukupno numeric," + " CONSTRAINT id_primary_key_Rfakture PRIMARY KEY (id)" + " )"; RemoteDB.select(sql, "Rfakture"); } if (DTremote.Select("table_name = 'Rfaktura_stavke'").Length == 0) { string sqlagencija = "CREATE TABLE Rfaktura_stavke" + " (id serial NOT NULL," + " broj int," + " dana numeric," + " ukupno numeric," + " rabat numeric," + " porez numeric," + " id_unos_gosta int," + " broj_sobe int," + " avans numeric," + " ime_gosta character varying(150)," + " datumDolaska timestamp without time zone," + " datumOdlaska timestamp without time zone," + " boravisna_pristojba numeric," + " iznos_usluge numeric," + " cijena_sobe numeric," + " CONSTRAINT id_primary_key_Rfakture_stavke PRIMARY KEY (id)" + " )"; RemoteDB.select(sqlagencija, "sobe"); } if (DTremote.Select("table_name = 'rfaktura_stavke' and column_name = 'otpremnica_broj'").Length == 0) { string sql = "alter table rfaktura_stavke add column otpremnica_broj integer default 0;"; RemoteDB.select(sql, "rfaktura_stavke"); } if (DTremote.Select("table_name = 'rfaktura_stavke' and column_name = 'otpremnica_pnp'").Length == 0) { string sql = "alter table rfaktura_stavke add column otpremnica_pnp character varying(20) default '';"; RemoteDB.select(sql, "rfaktura_stavke"); } //if (DTremote.Select("table_name = 'rfaktura_stavke' and column_name = 'otpremnica_sifra_robe'").Length == 0) //{ // string sql = "alter table rfaktura_stavke add column otpremnica_sifra_robe numeric(15,6) default 0;"; // RemoteDB.select(sql, "rfaktura_stavke"); //} if (DTremote.Select("table_name = 'otpremnice' and column_name = 'rfaktura_broj'").Length == 0) { string sql = @"alter table otpremnice add column na_sobu boolean DEFAULT false; alter table otpremnice add column rfaktura_broj integer DEFAULT 0; alter table otpremnice add column rfaktura_poslovnica integer DEFAULT 0; alter table otpremnice add column rfaktura_naplatni_uredaj integer DEFAULT 0;"; RemoteDB.select(sql, "rfaktura_stavke"); } if (DTremote.Select("table_name = 'Rponude'").Length == 0) { string sql = "CREATE TABLE Rponude" + " (id serial NOT NULL," + " broj int," + " godina int," + " datumDVO timestamp without time zone," + " datum timestamp without time zone," + " datum_valute timestamp without time zone," + " valuta numeric," + " id_valuta int," + " id_partner int," + " broj_lezaja numeric," + " nacin_placanja int," + " id_izradio int," + " model character varying(10)," + " napomena text," + " ukupno numeric," + " CONSTRAINT id_primary_key_Rponude PRIMARY KEY (id)" + " )"; RemoteDB.select(sql, "Rfakture"); } if (DTremote.Select("table_name = 'Rponude_stavke'").Length == 0) { string sqlagencija = "CREATE TABLE Rponude_stavke" + " (id serial NOT NULL," + " broj int," + " dana numeric," + " ukupno numeric," + " rabat numeric," + " porez numeric," + " cijena_sobe numeric," + " avans numeric," + " opis_usluge character varying(150)," + " datumDolaska timestamp without time zone," + " datumOdlaska timestamp without time zone," + " boravisna_pristojba numeric," + " iznos_usluge numeric," + " CONSTRAINT id_primary_key_Rponude_stavke PRIMARY KEY (id)" + " )"; RemoteDB.select(sqlagencija, "sobe"); } if (DTremote.Select("table_name = 'R_CijenaSoba'").Length == 0) { string sqlagencija = "CREATE TABLE R_CijenaSoba" + " (id serial NOT NULL," + " broj_sobe character varying(50)," + " id_soba int," + " id_valuta int," + " cijena_nocenja numeric," + " od_datuma timestamp without time zone," + " do_datuma timestamp without time zone," + " CONSTRAINT id_primary_key_R_CijenaSoba PRIMARY KEY (id)" + " )"; RemoteDB.insert(sqlagencija); } if (DTremote.Select("table_name = 'avansi'").Length == 0) { string sql = "CREATE TABLE avansi" + "(broj_avansa bigint NOT NULL," + "dat_dok timestamp without time zone," + "dat_knj timestamp without time zone," + "id_zaposlenik integer," + "id_zaposlenik_izradio integer," + "model character varying(10)," + "id_nacin_placanja bigint," + "id_valuta integer," + "opis text," + "id_vd character(5)," + "godina_avansa character(6)," + "ukupno numeric," + "nult_stp numeric," + "neoporezivo numeric," + "osnovica10 numeric," + "osnovica_var numeric," + "porez_var numeric," + "id_pdv integer," + "id_partner bigint," + "ziro bigint," + "jir character varying(100)," + "zki character varying(100)," + "storno character varying(2)," + "CONSTRAINT broj_avansa PRIMARY KEY (broj_avansa)" + ")"; RemoteDB.select(sql, "avansi"); } if (DTremote.Select("table_name = 'avansi_vd'").Length == 0) { string sql = "CREATE TABLE avansi_vd" + "(id_vd serial NOT NULL," + "vd character varying(30)," + "grupa character varying(5)," + "CONSTRAINT primary_key_id_vd PRIMARY KEY (id_vd )" + ")"; RemoteDB.select(sql, "avansi_vd"); Funkcije.provjera_sql(RemoteDB.insert("INSERT INTO avansi_vd (vd,grupa) VALUES ('Predujam','IP')")); Funkcije.provjera_sql(RemoteDB.insert("INSERT INTO avansi_vd (vd,grupa) VALUES ('Storno primljenog predujma','PRS')")); } if (DTremote.Select("table_name = 'tip_sobe'").Length == 0) { string sql = "CREATE TABLE tip_sobe" + " (id serial NOT NULL," + " tip character varying(70)," + " aktivnost int," + " CONSTRAINT id_tip_sobe_primary_key PRIMARY KEY (id )" + " )"; RemoteDB.select(sql, "tip_sobe"); RemoteDB.insert("INSERT INTO tip_sobe (tip,aktivnost) VALUES ('Sve sobe','1')"); RemoteDB.insert("INSERT INTO tip_sobe (tip,aktivnost) VALUES ('Jednokrevetna','1')"); RemoteDB.insert("INSERT INTO tip_sobe (tip,aktivnost) VALUES ('Dvokrevetna','1')"); } if (DTremote.Select("table_name = 'agencija'").Length == 0) { string sqlagencija = "CREATE TABLE agencija" + " (id serial NOT NULL," + " ime_agencije character varying(100)," + " napomena text," + " aktivnost int," + " CONSTRAINT id_primary_key_agencija PRIMARY KEY (id)" + " )"; RemoteDB.select(sqlagencija, "agencija"); RemoteDB.insert("INSERT INTO agencija (ime_agencije,aktivnost) VALUES ('Bravo','1')"); } if (DTremote.Select("table_name = 'sobe'").Length == 0) { string sqlagencija = "CREATE TABLE sobe" + " (id serial NOT NULL," + " broj_sobe numeric," + " id_tip_sobe int," + " naziv_sobe character varying(100)," + " broj_lezaja numeric," + " cijena_nocenja numeric," + " aktivnost int," + " napomena text," + " CONSTRAINT id_primary_key_sobe PRIMARY KEY (id)" + " )"; RemoteDB.select(sqlagencija, "sobe"); RemoteDB.insert("INSERT INTO sobe (broj_sobe,id_tip_sobe,naziv_sobe,broj_lezaja,napomena,cijena_nocenja,aktivnost) VALUES ('1','1','Soba broj 1','2','','100','1')"); RemoteDB.insert("INSERT INTO sobe (broj_sobe,id_tip_sobe,naziv_sobe,broj_lezaja,napomena,cijena_nocenja,aktivnost) VALUES ('2','1','Soba broj 2','3','','100','1')"); } if (DTremote.Select("table_name = 'vrsta_usluge'").Length == 0) { string sqlagencija = "CREATE TABLE vrsta_usluge" + " (id serial NOT NULL," + " naziv_usluge character varying(100)," + " napomena text," + " iznos decimal," + " aktivnost int," + " CONSTRAINT id_primary_key_vrsta_usluge PRIMARY KEY (id)" + " )"; RemoteDB.select(sqlagencija, "vrsta_usluge"); RemoteDB.insert("INSERT INTO vrsta_usluge (naziv_usluge,iznos,aktivnost) VALUES ('Samo noćenje','0','1')"); RemoteDB.insert("INSERT INTO vrsta_usluge (naziv_usluge,iznos,aktivnost) VALUES ('Noćenje s doručkom','20','1')"); RemoteDB.insert("INSERT INTO vrsta_usluge (naziv_usluge,iznos,aktivnost) VALUES ('Puni pansion','50','1')"); } if (DTremote.Select("table_name = 'boravisna_pristojba'").Length == 0) { string sqlagencija = "CREATE TABLE boravisna_pristojba" + " (id serial NOT NULL," + " oznaka character varying(10)," + " iznos numeric," + " boravisna_pristojba character varying(100)," + " aktivnost int," + " CONSTRAINT id_primary_key_boravisna_pristojba PRIMARY KEY (id)" + " )"; RemoteDB.select(sqlagencija, "boravisna_pristojba"); RemoteDB.insert("INSERT INTO boravisna_pristojba (boravisna_pristojba,iznos,oznaka,aktivnost) VALUES ('Glavna sezona','7','A','1')"); RemoteDB.insert("INSERT INTO boravisna_pristojba (boravisna_pristojba,iznos,oznaka,aktivnost) VALUES ('Predsezona','5.50','A','1')"); RemoteDB.insert("INSERT INTO boravisna_pristojba (boravisna_pristojba,iznos,oznaka,aktivnost) VALUES ('Izvan sezona','4.50','A','1')"); } if (DTremote.Select("table_name = 'unos_gosta'").Length == 0) { string sqlagencija = "CREATE TABLE unos_gosta" + " (id serial NOT NULL," + " broj bigint," + " godina int," + " vrijeme_unosa timestamp without time zone," + " ime_gosta character varying(150)," + " adresa character varying(250)," + " broj_osobne character varying(50)," + " broj_putovnice character varying(50)," + " datum_dolaska timestamp without time zone," + " datum_odlaska timestamp without time zone," + " datum_rodenja timestamp without time zone," + " id_agencija int," + " id_soba int," + " popust numeric," + " porez numeric," + " id_tip_sobe int," + " ukupno numeric," + " id_vrsta_usluge int," + " id_vrsta_gosta int," + " iznos_vu numeric," + " id_boravisna_pristojba int," + " cijena_sobe numeric," + " iznos_bor_pristojbe numeric," + " id_drzava character varying(5)," + " avans numeric," + " dorucak int," + " rucak int," + " vecera int," + " napomena text," + " boja character varying(100)," + " odjava int," + " CONSTRAINT id_primary_key_unos_gosta PRIMARY KEY (id)" + " )"; RemoteDB.select(sqlagencija, "unos_gosta"); } if (DTremote.Select("table_name = 'unos_rezervacije'").Length == 0) { string sqlagencija = "CREATE TABLE unos_rezervacije" + " (id serial NOT NULL," + " broj bigint," + " godina int," + " vrijeme_unosa timestamp without time zone," + " ime_gosta character varying(150)," + " broj_osobne character varying(50)," + " broj_putovnice character varying(50)," + " datum_dolaska timestamp without time zone," + " datum_odlaska timestamp without time zone," + " id_agencija int," + " id_soba int," + " id_vrsta_gosta int," + " id_vrsta_usluge int," + " id_drzava character varying(5)," + " avans numeric," + " dorucak int," + " rucak int," + " vecera int," + " odrasli int," + " djeca int," + " bebe int," + " boja character varying(100)," + " napomena text," + " odjava int," + " CONSTRAINT id_primary_key_unos_rezervacije PRIMARY KEY (id)" + " )"; RemoteDB.select(sqlagencija, "unos_gosta"); } } }
private void SetGosti(int month, int width = 0) { var daysCount = DateTime.DaysInMonth(Convert.ToInt16(nuGodina.Value), month); //Dodaje prvu kolonu za ime sobe dgv.Columns.Add(new DataGridViewColumn() { HeaderText = "Naziv sobe", CellTemplate = new DataGridViewTextBoxCell() }); dgv.Columns[0].Width = 100; if (width != 0) { dgv.Columns[0].Width = width; } //dodaje kolone za dane u mjesecu for (int i = 1; i <= daysCount; i++) { dgv.Columns.Add(new DataGridViewColumn() { HeaderText = i.ToString(), CellTemplate = new DataGridViewTextBoxCell() }); dgv.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; } //dodaje redke za nazive soba DataTable DTs = RemoteDB.select("SELECT naziv_sobe,id FROM sobe WHERE aktivnost=1 ORDER BY id_tip_sobe ASC, naziv_sobe", "sobe").Tables[0]; for (int i = 0; i < DTs.Rows.Count; i++) { dgv.Rows.Add(DTs.Rows[i]["naziv_sobe"].ToString()); string sql = "SELECT id,napomena,broj,datum_dolaska,datum_odlaska,id_soba,ime_gosta,avans,dorucak,rucak,vecera FROM unos_rezervacije " + "WHERE (odjava <> 1 OR odjava is null) AND id_soba='" + DTs.Rows[i]["id"].ToString() + "' " + "AND ((date_part('MONTH', datum_dolaska) = '" + cbMjesec.SelectedValue + "' OR date_part('MONTH', datum_odlaska) = '" + cbMjesec.SelectedValue + "'))" + "AND ((date_part('YEAR', datum_dolaska) = '" + nuGodina.Value + "' OR date_part('YEAR', datum_odlaska) = '" + nuGodina.Value + "'))"; DataTable DTunos = RemoteDB.select(sql, "unos_gosta").Tables[0]; foreach (DataRow row in DTunos.Rows) { DateTime OD = Convert.ToDateTime(row["datum_dolaska"].ToString()); DateTime DO = Convert.ToDateTime(row["datum_odlaska"].ToString()); if (DO.Month > Convert.ToInt16(cbMjesec.SelectedValue)) { DO = Convert.ToDateTime(nuGodina.Value.ToString() + "-" + cbMjesec.SelectedValue + "-" + DateTime.DaysInMonth(DateTime.Now.Year, month) + " 23:59:59"); } if (OD.Month < Convert.ToInt16(cbMjesec.SelectedValue)) { OD = Convert.ToDateTime(nuGodina.Value.ToString() + "-" + cbMjesec.SelectedValue + "-01" + " 00:00:01"); } int broj_nocenja = Funkcije.ReturnDaysFromDate(OD, DO); for (int d = 0; d < broj_nocenja + 1; d++) { if (daysCount < OD.Day + d) { OD = OD.AddDays(-1); } if (dgv.Rows[i].Cells[OD.Day + d].Style.BackColor == Color.SkyBlue || dgv.Rows[i].Cells[OD.Day + d].Style.BackColor == Color.Blue || dgv.Rows[i].Cells[OD.Day + d].Style.BackColor == Color.PowderBlue) { dgv.Rows[i].Cells[OD.Day + d].Style.BackColor = Color.Blue; dgv.Rows[i].Cells[OD.Day + d].Style.ForeColor = Color.Blue; dgv.Rows[i].Cells[OD.Day + d].ErrorText = ""; } else { dgv.Rows[i].Cells[OD.Day + d].Style = new DataGridViewCellStyle { ForeColor = Color.SkyBlue, BackColor = Color.SkyBlue }; } /////////////////////////////////////////////////////////// string tool_tip = dgv.Rows[i].Cells[OD.Day + d].ToolTipText.ToString(); if (tool_tip != "") { string val = "RAZMJENA GOSTI:\r\n\r\n" + tool_tip + "\r\n\r\n____________________________________\r\n\r\n" + SetTitle(DTunos, row["broj"].ToString()); dgv.Rows[i].Cells[OD.Day + d].ToolTipText = val; } else { dgv.Rows[i].Cells[OD.Day + d].ToolTipText = SetTitle(DTunos, row["broj"].ToString()); } if (dgv.Rows[i].Cells[OD.Day + d].FormattedValue.ToString() == "") { dgv.Rows[i].Cells[OD.Day + d].Value = "R" + row["broj"].ToString(); } else { dgv.Rows[i].Cells[OD.Day + d].Value = dgv.Rows[i].Cells[OD.Day + d].FormattedValue.ToString() + "#R" + row["broj"].ToString(); } if (broj_nocenja == d) { if (dgv.Rows[i].Cells[OD.Day + d].Style.BackColor != Color.Blue && (OD.Day + d) != daysCount) { dgv.Rows[i].Cells[OD.Day + d].ErrorText = "Ovaj dan soba je slobodna od 10 sati."; } } } } sql = "SELECT napomena,broj,datum_dolaska,datum_odlaska,id_soba,ime_gosta,avans,dorucak,rucak,vecera FROM unos_gosta WHERE (odjava <> 1 OR odjava is null) AND id_soba='" + DTs.Rows[i]["id"].ToString() + "' AND (date_part('MONTH', datum_dolaska) = '" + cbMjesec.SelectedValue + "' OR date_part('MONTH', datum_odlaska) = '" + cbMjesec.SelectedValue + "')"; DTunos = RemoteDB.select(sql, "unos_gosta").Tables[0]; for (int l = 0; l < DTunos.Rows.Count; l++) { DateTime datumDolaska = Convert.ToDateTime(DTunos.Rows[l]["datum_dolaska"].ToString()); DateTime datumOdlaska = Convert.ToDateTime(DTunos.Rows[l]["datum_odlaska"].ToString()); if (datumOdlaska.Month > Convert.ToInt16(cbMjesec.SelectedValue)) { datumOdlaska = Convert.ToDateTime(nuGodina.Value.ToString() + "-" + cbMjesec.SelectedValue + "-" + DateTime.DaysInMonth(DateTime.Now.Year, month) + " 23:59:59"); } if (datumDolaska.Month < Convert.ToInt16(cbMjesec.SelectedValue)) { datumDolaska = Convert.ToDateTime(nuGodina.Value.ToString() + "-" + cbMjesec.SelectedValue + "-01" + " 00:00:01"); } int broj_nocenja = Funkcije.ReturnDaysFromDate(datumDolaska, datumOdlaska); for (int d = 0; d < broj_nocenja + 1; d++) { if (daysCount < datumDolaska.Day + d) { datumDolaska = datumDolaska.AddDays(-1); } //if (datumDolaska.Day + d > dgv.ColumnCount - 1) // continue; bool postojanje = PostojanjeBroja("U" + DTunos.Rows[l]["broj"].ToString(), dgv.Rows[i].Cells[datumDolaska.Day + d].FormattedValue.ToString()); if (dgv.Rows[i].Cells[datumDolaska.Day + d].FormattedValue.ToString() != "" && dgv.Rows[i].Cells[datumDolaska.Day + d].FormattedValue.ToString() != "U" + DTunos.Rows[l]["broj"].ToString()) { dgv.Rows[i].Cells[datumDolaska.Day + d].Style.BackColor = Color.DarkRed; dgv.Rows[i].Cells[datumDolaska.Day + d].Style.ForeColor = Color.DarkRed; } else { dgv.Rows[i].Cells[datumDolaska.Day + d].Style.BackColor = Color.Red; dgv.Rows[i].Cells[datumDolaska.Day + d].Style.ForeColor = Color.Red; } if (!postojanje) { string tool_tip = dgv.Rows[i].Cells[datumDolaska.Day + d].ToolTipText.ToString(); if (tool_tip != "") { string val = "RAZMJENA GOSTI:\r\n\r\n" + tool_tip + "\r\n\r\n____________________________________\r\n\r\n" + SetTitle(DTunos, DTunos.Rows[l]["broj"].ToString()); dgv.Rows[i].Cells[datumDolaska.Day + d].ToolTipText = val; } else { dgv.Rows[i].Cells[datumDolaska.Day + d].ToolTipText = SetTitle(DTunos, DTunos.Rows[l]["broj"].ToString()); } if (dgv.Rows[i].Cells[datumDolaska.Day + d].FormattedValue.ToString() == "") { dgv.Rows[i].Cells[datumDolaska.Day + d].Value = "U" + DTunos.Rows[l]["broj"].ToString(); } else { dgv.Rows[i].Cells[datumDolaska.Day + d].Value = dgv.Rows[i].Cells[datumDolaska.Day + d].FormattedValue.ToString() + "#U" + DTunos.Rows[l]["broj"].ToString(); } } if (broj_nocenja == d) { dgv.Rows[i].Cells[datumDolaska.Day + d].ErrorText = "Ovaj dan soba je slobodna od 10 sati."; } } } } }