private void btnKreirajFajlove_Click(object sender, EventArgs e) { DatumOD = dtpOD.Value; DatumDO = dtpDO.Value; try { KreirajPutanju(DatumOD.Year.ToString(), DatumOD.Month.ToString(), DatumOD.Day.ToString(), System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)); pathTxt = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\" + DatumOD.Year.ToString() + "\\" + DatumOD.Month.ToString() + "\\" + DatumOD.Day.ToString(); pathZip = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\" + DatumOD.Year.ToString() + "\\" + DatumOD.Month.ToString() + "\\" + DatumOD.Day.ToString() + ""; synkracuni(); synkstavke(); synkplacanje(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } Zip.Go(pathTxt, pathZip + "\\" + DTp.Rows[0][0].ToString() + "_" + DatumOD.ToString("ddMMyyyy") + ".zip"); MessageBox.Show("Fajlovi su uspješno kreirani", "Kreirano"); }
private void fillDataset(string sklID, string sklName) { decimal doc, ukupno = 0; string sql; DataRow DTrow; #region FAKTURA sql = "SELECT" + " 'Faktura ' + CAST(faktura_stavke.broj_fakture AS varchar) AS broj, fakture.date AS datum, ime_tvrtke," + " __mpc" + " sum(faktura_stavke.vpc*CAST(REPLACE(kolicina,',','.') AS numeric)) AS vpc," + " sum(CAST(faktura_stavke.nbc AS numeric)*CAST(REPLACE(kolicina,',','.') AS numeric)) AS nbc" + " FROM faktura_stavke LEFT JOIN fakture ON fakture.broj_fakture=faktura_stavke.broj_fakture" + " AND fakture.id_ducan=faktura_stavke.id_ducan AND fakture.id_kasa=faktura_stavke.id_kasa" + " LEFT JOIN partners ON fakture.id_fakturirati=partners.id_partner" + " LEFT JOIN roba ON roba.sifra=faktura_stavke.sifra " + " WHERE " + query_samo_robno + " CAST(fakture.date AS date) BETWEEN '" + DatumOD.ToString("yyyy-MM-dd 00:00:00") + "' AND '" + DatumDO.ToString("yyyy-MM-dd 23:59:59") + "' AND faktura_stavke.id_skladiste=" + sklID + " GROUP BY faktura_stavke.broj_fakture,fakture.date,ime_tvrtke" + " ORDER BY fakture.date"; if (sa_rabatom) { string sss; //= "sum(racun_stavke.vpc*CAST(REPLACE(racun_stavke.kolicina,',','.') AS numeric)*(1 zbroj CAST(REPLACE(racun_stavke.porez,',','.') AS numeric)/100)) AS mpc,"; sss = "sum(CAST(REPLACE(faktura_stavke.kolicina,',','.') AS numeric)*(faktura_stavke.vpc*(1 zbroj CAST(REPLACE(faktura_stavke.porez,',','.') AS numeric)/100)-(((faktura_stavke.vpc*(1 zbroj CAST(REPLACE(faktura_stavke.porez,',','.') AS numeric)/100))*CAST(REPLACE(faktura_stavke.rabat,',','.') AS numeric))/100))) AS mpc,"; sql = sql.Replace("__mpc", sss); } else { sql = sql.Replace("__mpc", "sum(faktura_stavke.vpc*CAST(REPLACE(kolicina,',','.') AS numeric)*(1 zbroj CAST(REPLACE(porez,',','.') AS numeric)/100)) AS mpc,"); } doc = obradiDokument(sql, false); ukupno += doc; DTrow = dSRliste.Tables[0].NewRow(); DTrow["naziv"] = sklName; DTrow["jmj"] = "Ukupno po fakturama"; DTrow["cijena3"] = doc; dSRliste.Tables[0].Rows.Add(DTrow); // Prazan red DTrow = dSRliste.Tables[0].NewRow(); dSRliste.Tables[0].Rows.Add(DTrow); #endregion FAKTURA #region MALOPRODAJA sql = "SELECT" + " 'Račun br. ' + racun_stavke.broj_racuna AS broj, racuni.datum_racuna AS datum, ime_tvrtke," + " __mpc" + " sum(racun_stavke.vpc*CAST(REPLACE(racun_stavke.kolicina,',','.') AS numeric)) AS vpc," + " sum(CAST(racun_stavke.nbc AS numeric)*CAST(REPLACE(racun_stavke.kolicina,',','.') AS numeric)) AS nbc" + " FROM racun_stavke LEFT JOIN racuni ON racuni.broj_racuna=racun_stavke.broj_racuna" + " LEFT JOIN partners ON racuni.id_kupac=partners.id_partner" + " LEFT JOIN roba ON roba.sifra=racun_stavke.sifra_robe " + " WHERE " + query_samo_robno + " CAST(racuni.datum_racuna AS date) BETWEEN '" + DatumOD.ToString("yyyy-MM-dd 00:00:00") + "' AND '" + DatumDO.ToString("yyyy-MM-dd 23:59:59") + "' AND racun_stavke.id_skladiste=" + sklID + " GROUP BY racun_stavke.broj_racuna,racuni.datum_racuna,ime_tvrtke" + " ORDER BY racuni.datum_racuna"; if (sa_rabatom) { string sss; //= "sum(racun_stavke.vpc*CAST(REPLACE(racun_stavke.kolicina,',','.') AS numeric)*(1 zbroj CAST(REPLACE(racun_stavke.porez,',','.') AS numeric)/100)) AS mpc,"; sss = "sum(CAST(REPLACE(racun_stavke.kolicina,',','.') AS numeric)*(racun_stavke.vpc*(1 zbroj CAST(REPLACE(racun_stavke.porez,',','.') AS numeric)/100)-(((racun_stavke.vpc*(1 zbroj CAST(REPLACE(racun_stavke.porez,',','.') AS numeric)/100))*CAST(REPLACE(racun_stavke.rabat,',','.') AS numeric))/100))) AS mpc,"; sql = sql.Replace("__mpc", sss); } else { sql = sql.Replace("__mpc", "sum(racun_stavke.vpc*CAST(REPLACE(racun_stavke.kolicina,',','.') AS numeric)*(1 zbroj CAST(REPLACE(racun_stavke.porez,',','.') AS numeric)/100)) AS mpc,"); } doc = obradiDokument(sql, false); ukupno += doc; DTrow = dSRliste.Tables[0].NewRow(); DTrow["naziv"] = sklName; DTrow["jmj"] = "Ukupno po računima "; DTrow["cijena3"] = doc; dSRliste.Tables[0].Rows.Add(DTrow); // Prazan red DTrow = dSRliste.Tables[0].NewRow(); dSRliste.Tables[0].Rows.Add(DTrow); #endregion MALOPRODAJA #region KALKULACIJA sql = "SELECT" + " 'Kalkulacija ' + CAST(broj AS varchar) + '/' + CAST(id_skladiste AS varchar) AS broj, datum, ime_tvrtke," + " CAST(ukupno_mpc AS numeric) AS mpc," + " CAST(ukupno_vpc AS numeric) AS vpc," + " CAST(fakturni_iznos AS numeric) AS nbc" + " FROM kalkulacija LEFT JOIN partners ON kalkulacija.id_partner=partners.id_partner" + " WHERE CAST(datum AS date) BETWEEN '" + DatumOD.ToString("yyyy-MM-dd 00:00:00") + "' AND '" + DatumDO.ToString("yyyy-MM-dd 23:59:59") + "' AND id_skladiste=" + sklID; doc = obradiDokument(sql, true); ukupno += doc; DTrow = dSRliste.Tables[0].NewRow(); DTrow["naziv"] = sklName; DTrow["jmj"] = "Ukupno po kalkulacijama"; DTrow["cijena3"] = doc; dSRliste.Tables[0].Rows.Add(DTrow); // Prazan red DTrow = dSRliste.Tables[0].NewRow(); dSRliste.Tables[0].Rows.Add(DTrow); #endregion KALKULACIJA #region PRIMKA sql = "SELECT" + " 'PRIMKA ' + CAST(primka.broj AS varchar) + '/' + CAST(primka.id_skladiste AS varchar) AS broj, primka.datum, ime_tvrtke," + " SUM(mpc*CAST(REPLACE(kolicina,',','.') AS numeric)) AS mpc," + " SUM(vpc*CAST(REPLACE(kolicina,',','.') AS numeric)) AS vpc," + " SUM(nbc*CAST(REPLACE(kolicina,',','.') AS numeric)) AS nbc" + " FROM primka_stavke LEFT JOIN primka ON primka.id_primka=primka_stavke.id_primka" + " LEFT JOIN partners ON primka.id_partner=partners.id_partner" + " WHERE CAST(primka.datum AS date) BETWEEN '" + DatumOD.ToString("yyyy-MM-dd 00:00:00") + "' AND '" + DatumDO.ToString("yyyy-MM-dd 23:59:59") + "' AND primka.id_skladiste=" + sklID + " GROUP BY primka.broj,primka.datum,ime_tvrtke,primka.id_skladiste" + " ORDER BY primka.datum"; doc = obradiDokument(sql, true); ukupno += doc; DTrow = dSRliste.Tables[0].NewRow(); DTrow["naziv"] = sklName; DTrow["jmj"] = "Ukupno po primkama"; DTrow["cijena3"] = doc; dSRliste.Tables[0].Rows.Add(DTrow); // Prazan red DTrow = dSRliste.Tables[0].NewRow(); dSRliste.Tables[0].Rows.Add(DTrow); #endregion PRIMKA #region IZDATNICA sql = "SELECT" + " 'Izdatnica ' + CAST(izdatnica.broj AS varchar) + '/' + CAST(izdatnica.id_skladiste AS varchar) AS broj, izdatnica.datum, ime_tvrtke," + " SUM(mpc*CAST(REPLACE(kolicina,',','.') AS numeric)) AS mpc," + " SUM(vpc*CAST(REPLACE(kolicina,',','.') AS numeric)) AS vpc," + " SUM(nbc*CAST(REPLACE(kolicina,',','.') AS numeric)) AS nbc" + " FROM izdatnica_stavke LEFT JOIN izdatnica ON izdatnica.id_izdatnica=izdatnica_stavke.id_izdatnica" + " LEFT JOIN partners ON izdatnica.id_partner=partners.id_partner" + " WHERE CAST(izdatnica.datum AS date) BETWEEN '" + DatumOD.ToString("yyyy-MM-dd 00:00:00") + "' AND '" + DatumDO.ToString("yyyy-MM-dd 23:59:59") + "' AND izdatnica.id_skladiste=" + sklID + " GROUP BY izdatnica.broj,izdatnica.datum,izdatnica.id_skladiste,ime_tvrtke" + " ORDER BY izdatnica.datum"; doc = obradiDokument(sql, false); ukupno += doc; DTrow = dSRliste.Tables[0].NewRow(); DTrow["naziv"] = sklName; DTrow["jmj"] = "Ukupno po izdatnicama"; DTrow["cijena3"] = doc; dSRliste.Tables[0].Rows.Add(DTrow); // Prazan red DTrow = dSRliste.Tables[0].NewRow(); dSRliste.Tables[0].Rows.Add(DTrow); #endregion IZDATNICA #region MEDUSKLADISNICA IZLAZ sql = "SELECT" + " 'Međuskladišnica ' + meduskladisnica.broj + '/' + CAST(meduskladisnica.id_skladiste_od AS varchar) AS broj, meduskladisnica.datum,'' AS ime_tvrtke," + " SUM(CAST(mpc AS numeric)*CAST(REPLACE(kolicina,',','.') AS numeric)) AS mpc," + " SUM(CAST(vpc AS numeric)*CAST(REPLACE(kolicina,',','.') AS numeric)) AS vpc," + " SUM(CAST(nbc AS numeric)*CAST(REPLACE(kolicina,',','.') AS numeric)) AS nbc" + " FROM meduskladisnica_stavke LEFT JOIN meduskladisnica ON meduskladisnica.broj=meduskladisnica_stavke.broj AND meduskladisnica.id_skladiste_od=meduskladisnica_stavke.iz_skladista" + " WHERE CAST(meduskladisnica.datum AS date) BETWEEN '" + DatumOD.ToString("yyyy-MM-dd 00:00:00") + "' AND '" + DatumDO.ToString("yyyy-MM-dd 23:59:59") + "' AND meduskladisnica.id_skladiste_od=" + sklID + " GROUP BY meduskladisnica.broj,meduskladisnica.datum,meduskladisnica.id_skladiste_od" + " ORDER BY meduskladisnica.datum"; doc = obradiDokument(sql, false); ukupno += doc; DTrow = dSRliste.Tables[0].NewRow(); DTrow["naziv"] = sklName; DTrow["jmj"] = "Ukupno po međuskladišnicama izlaz"; DTrow["cijena3"] = doc; dSRliste.Tables[0].Rows.Add(DTrow); // Prazan red DTrow = dSRliste.Tables[0].NewRow(); dSRliste.Tables[0].Rows.Add(DTrow); #endregion MEDUSKLADISNICA IZLAZ #region MEDUSKLADISNICA ULAZ sql = "SELECT" + " 'Međuskladišnica ' + meduskladisnica.broj + '/' + CAST(meduskladisnica.id_skladiste_od AS varchar) AS broj, meduskladisnica.datum,'' AS ime_tvrtke," + " SUM(CAST(mpc AS numeric)*CAST(REPLACE(kolicina,',','.') AS numeric)) AS mpc," + " SUM(CAST(vpc AS numeric)*CAST(REPLACE(kolicina,',','.') AS numeric)) AS vpc," + " SUM(CAST(nbc AS numeric)*CAST(REPLACE(kolicina,',','.') AS numeric)) AS nbc" + " FROM meduskladisnica_stavke LEFT JOIN meduskladisnica ON meduskladisnica.broj=meduskladisnica_stavke.broj AND meduskladisnica.id_skladiste_od=meduskladisnica_stavke.iz_skladista" + " WHERE CAST(meduskladisnica.datum AS date) BETWEEN '" + DatumOD.ToString("yyyy-MM-dd 00:00:00") + "' AND '" + DatumDO.ToString("yyyy-MM-dd 23:59:59") + "' AND meduskladisnica.id_skladiste_do=" + sklID + " GROUP BY meduskladisnica.broj,meduskladisnica.datum,meduskladisnica.id_skladiste_od" + " ORDER BY meduskladisnica.datum"; doc = obradiDokument(sql, true); ukupno += doc; DTrow = dSRliste.Tables[0].NewRow(); DTrow["naziv"] = sklName; DTrow["jmj"] = "Ukupno po međuskladišnicama ulaz"; DTrow["cijena3"] = doc; dSRliste.Tables[0].Rows.Add(DTrow); // Prazan red DTrow = dSRliste.Tables[0].NewRow(); dSRliste.Tables[0].Rows.Add(DTrow); #endregion MEDUSKLADISNICA ULAZ #region POVRAT sql = "SELECT" + " 'Povratnica ' + CAST(povrat_robe.broj AS varchar) AS broj, povrat_robe.datum, ime_tvrtke," + " SUM(vpc*(1 zbroj CAST(REPLACE(pdv,',','.') AS numeric)/100)*CAST(REPLACE(kolicina,',','.') AS numeric)) AS mpc," + " SUM(vpc*CAST(REPLACE(kolicina,',','.') AS numeric)) AS vpc," + " SUM(CAST(nbc AS numeric)*CAST(REPLACE(kolicina,',','.') AS numeric)) AS nbc" + " FROM povrat_robe_stavke LEFT JOIN povrat_robe ON povrat_robe.broj=povrat_robe_stavke.broj" + " LEFT JOIN partners ON povrat_robe.id_partner=partners.id_partner" + " WHERE CAST(povrat_robe.datum AS date) BETWEEN '" + DatumOD.ToString("yyyy-MM-dd 00:00:00") + "' AND '" + DatumDO.ToString("yyyy-MM-dd 23:59:59") + "' AND povrat_robe.id_skladiste=" + sklID + " GROUP BY povrat_robe.broj,povrat_robe.datum,ime_tvrtke" + " ORDER BY povrat_robe.datum"; doc = obradiDokument(sql, false); ukupno += doc; DTrow = dSRliste.Tables[0].NewRow(); DTrow["naziv"] = sklName; DTrow["jmj"] = "Ukupno po povratima"; DTrow["cijena3"] = doc; dSRliste.Tables[0].Rows.Add(DTrow); // Prazan red DTrow = dSRliste.Tables[0].NewRow(); dSRliste.Tables[0].Rows.Add(DTrow); #endregion POVRAT #region OTPREMNICA sql = "SELECT" + " 'Otpremnica ' + CAST(otpremnice.broj_otpremnice AS varchar) + '/' + CAST(otpremnica_stavke.id_skladiste AS varchar) AS broj, otpremnice.datum, ime_tvrtke," + " __mpc" + " SUM(vpc*CAST(REPLACE(kolicina,',','.') AS numeric)) AS vpc," + " SUM(CAST(nbc AS numeric)*CAST(REPLACE(kolicina,',','.') AS numeric)) AS nbc" + " FROM otpremnica_stavke LEFT JOIN otpremnice ON otpremnice.broj_otpremnice=otpremnica_stavke.broj_otpremnice" + " LEFT JOIN partners ON otpremnice.osoba_partner=partners.id_partner" + " WHERE CAST(otpremnice.datum AS date) BETWEEN '" + DatumOD.ToString("yyyy-MM-dd 00:00:00") + "' AND '" + DatumDO.ToString("yyyy-MM-dd 23:59:59") + "' AND otpremnica_stavke.id_skladiste=" + sklID + " GROUP BY otpremnice.broj_otpremnice,otpremnice.datum,otpremnica_stavke.id_skladiste,ime_tvrtke" + " ORDER BY otpremnice.datum"; if (sa_rabatom) { string sss; //= "sum(racun_stavke.vpc*CAST(REPLACE(racun_stavke.kolicina,',','.') AS numeric)*(1 zbroj CAST(REPLACE(racun_stavke.porez,',','.') AS numeric)/100)) AS mpc,"; sss = "sum(CAST(REPLACE(otpremnica_stavke.kolicina,',','.') AS numeric)*(otpremnica_stavke.vpc*(1 zbroj CAST(REPLACE(otpremnica_stavke.porez,',','.') AS numeric)/100)-(((otpremnica_stavke.vpc*(1 zbroj CAST(REPLACE(otpremnica_stavke.porez,',','.') AS numeric)/100))*CAST(REPLACE(otpremnica_stavke.rabat,',','.') AS numeric))/100))) AS mpc,"; sql = sql.Replace("__mpc", sss); } else { sql = sql.Replace("__mpc", "SUM(vpc*(1 zbroj CAST(REPLACE(porez,',','.') AS numeric)/100)*CAST(REPLACE(kolicina,',','.') AS numeric)) AS mpc,"); } doc = obradiDokument(sql, false); ukupno += doc; DTrow = dSRliste.Tables[0].NewRow(); DTrow["naziv"] = sklName; DTrow["jmj"] = "Ukupno po otpremnicama"; DTrow["cijena3"] = doc; dSRliste.Tables[0].Rows.Add(DTrow); // Prazan red DTrow = dSRliste.Tables[0].NewRow(); dSRliste.Tables[0].Rows.Add(DTrow); #endregion OTPREMNICA #region OTPIS sql = "SELECT" + " 'Otpis ' + CAST(otpis_robe.broj AS varchar) AS broj, otpis_robe.datum,'' AS ime_tvrtke," + " SUM(CAST(mpc AS numeric)*CAST(REPLACE(kolicina,',','.') AS numeric)) AS mpc," + " SUM(vpc*CAST(REPLACE(kolicina,',','.') AS numeric)) AS vpc," + " SUM(CAST(nbc AS numeric)*CAST(REPLACE(kolicina,',','.') AS numeric)) AS nbc" + " FROM otpis_robe_stavke LEFT JOIN otpis_robe ON otpis_robe.broj=otpis_robe_stavke.broj" + " WHERE CAST(otpis_robe.datum AS date) BETWEEN '" + DatumOD.ToString("yyyy-MM-dd 00:00:00") + "' AND '" + DatumDO.ToString("yyyy-MM-dd 23:59:59") + "' AND otpis_robe.id_skladiste=" + sklID + " GROUP BY otpis_robe.broj,otpis_robe.datum" + " ORDER BY otpis_robe.datum"; doc = obradiDokument(sql, false); ukupno += doc; DTrow = dSRliste.Tables[0].NewRow(); DTrow["naziv"] = sklName; DTrow["jmj"] = "Ukupno po otpisima"; DTrow["cijena3"] = doc; dSRliste.Tables[0].Rows.Add(DTrow); // Prazan red DTrow = dSRliste.Tables[0].NewRow(); dSRliste.Tables[0].Rows.Add(DTrow); #endregion OTPIS #region UKUPNO DTrow = dSRliste.Tables[0].NewRow(); DTrow["jmj"] = "Ukupno za " + sklName; DTrow["cijena3"] = ukupno; dSRliste.Tables[0].Rows.Add(DTrow); // Prazan red DTrow = dSRliste.Tables[0].NewRow(); dSRliste.Tables[0].Rows.Add(DTrow); #endregion UKUPNO }
private void synkplacanje() { string zapisi = "\r\n"; DataTable DTsync1 = new DataTable(); string sync1 = "SELECT broj_racuna, datum_racuna, nacin_placanja, storno, id_kasa, id_blagajnik, id_ducan, ukupno, EXTRACT(YEAR FROM datum_racuna) as godina_racuna FROM racuni WHERE racuni.datum_racuna> '" + DatumOD.ToString("yyyy-MM-dd H:mm:ss") + "' AND racuni.datum_racuna<'" + DatumDO.ToString("yyyy-MM-dd H:mm:ss") + "' ORDER BY CAST(broj_racuna AS int) ASC "; DTsync1 = classSQL.select(sync1, "racuni").Tables[0]; DataTable DTsync = new DataTable(); for (int i = 0; DTsync1.Rows.Count > i; i++) { string broj = DTsync1.Rows[i]["broj_racuna"].ToString(); string sifra_value = "191"; string broj_kase = "01"; string redni_broj_naplate_po_rac = "1"; string broj_kartice = "0"; string nacin_placanja = DTsync1.Rows[i]["nacin_placanja"].ToString(); string datum_naplate = ""; string id_broj = ""; string id_broj2 = ""; decimal ukupno_racun = Convert.ToDecimal(DTsync1.Rows[i]["ukupno"].ToString()); string id_kasa = DTsync1.Rows[i]["id_kasa"].ToString(); string id_blagajnik = DTsync1.Rows[i]["id_blagajnik"].ToString(); string id_ducan = ducan; string godina_racuna = DTsync1.Rows[i]["godina_racuna"].ToString(); DateTime datum_racuna = Convert.ToDateTime(DTsync1.Rows[i]["datum_racuna"].ToString()); try { if (i == 0) { if (File.Exists(pathTxt + "\\" + id_ducan + "_KPLRACUNa_" + datum_racuna.ToString("dd-MM-yyyy").Replace("-", "") + ".txt")) { File.Delete(pathTxt + "\\" + id_ducan + "_KPLRACUNa_" + datum_racuna.ToString("dd-MM-yyyy").Replace("-", "") + ".txt"); } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } zapisi += "#" + sifra_value + "#;" + "#" + broj_kase + "#;" + "#" + nule(8, broj) + "#;" + "#" + godina_racuna + "#;" + "#" + nule(10, Convert.ToString(ukupno_racun.ToString("#0.00"))).Replace(",", ".") + "#;" + "#" + redni_broj_naplate_po_rac + "#;" + "#" + broj_kartice + "#;" + "#" + nacin_pl(nacin_placanja) + "#;" + "#" + datum_naplate + "#;" + "#" + id_broj + "#;" + "#" + id_broj2 + "#;" + "#" + id_ducan + "#"; //File.WriteAllText("D:\\" + id_ducan + "_KRACUNI" + Sistemski_datum.ToString("dd-MM-yyyy") + "", zapisi); using (System.IO.StreamWriter file = new System.IO.StreamWriter(pathTxt + "\\" + id_ducan + "_KPLRACUNa_" + datum_racuna.ToString("dd-MM-yyyy").Replace("-", "") + ".txt", true)) { file.WriteLine(zapisi); zapisi = ""; } } }
private void synkstavke() { string zapisi = "\r\n"; DataTable DTsync1 = new DataTable(); string sync1 = "SELECT broj_racuna,datum_racuna, storno, id_kasa, id_blagajnik, id_ducan, EXTRACT(YEAR FROM datum_racuna) as godina_racuna FROM racuni WHERE racuni.datum_racuna> '" + DatumOD.ToString("yyyy-MM-dd H:mm:ss") + "' AND racuni.datum_racuna<'" + DatumDO.ToString("yyyy-MM-dd H:mm:ss") + "' ORDER BY datum_racuna ASC "; DTsync1 = classSQL.select(sync1, "racuni").Tables[0]; DataTable DTsync = new DataTable(); int provjera_za_prvi = 0; for (int i = 0; DTsync1.Rows.Count > i; i++) { Int32 brojac = 0; string broj = DTsync1.Rows[i]["broj_racuna"].ToString(); string id_kasa = DTsync1.Rows[i]["id_kasa"].ToString(); string id_blagajnik = DTsync1.Rows[i]["id_blagajnik"].ToString(); string id_ducan = ducan; string godina_racuna = DTsync1.Rows[i]["godina_racuna"].ToString(); string sync = "SELECT " + "CURRENT_DATE as Sistemski_datum, " + " racun_stavke.id_stavka, racun_stavke.sifra_robe, " + " racun_stavke.mpc, racun_stavke.vpc, racun_stavke.kolicina, racun_stavke.porez_potrosnja, racun_stavke.rabat, racun_stavke.porez, " + " roba.naziv, roba.jm, roba.sifra " + "FROM racun_stavke LEFT JOIN roba ON roba.sifra = racun_stavke.sifra_robe WHERE broj_racuna = '" + broj + "' AND racun_stavke.id_ducan='" + Util.Korisno.idDucan + "' AND racun_stavke.id_blagajna='" + Util.Korisno.idKasa + "'" + ""; DTsync = classSQL.select(sync, "racuni_stavke").Tables[0]; DateTime Sistemski_datum = new DateTime(); for (int y = 0; DTsync.Rows.Count > y; y++) { brojac++; decimal popust = 0; decimal neto_bez_pdv = 0; decimal mpc = Convert.ToDecimal(DTsync.Rows[y]["mpc"].ToString()); decimal porez_potr = Convert.ToDecimal(DTsync.Rows[y]["porez_potrosnja"].ToString()); decimal vpc = Convert.ToDecimal(DTsync.Rows[y]["vpc"].ToString()); decimal kol = Convert.ToDecimal(DTsync.Rows[y]["kolicina"].ToString()); decimal rab = Convert.ToDecimal(DTsync.Rows[y]["rabat"].ToString()); decimal pdv = Convert.ToDecimal(DTsync.Rows[y]["porez"].ToString()); decimal pdv_grupa = pdv + porez_potr; Sistemski_datum = Convert.ToDateTime(DTsync.Rows[y]["Sistemski_datum"].ToString()); string naziv_artikla = DTsync.Rows[y]["naziv"].ToString(); string jedinicna_mjera = DTsync.Rows[y]["jm"].ToString(); if (jedinicna_mjera.Length > 4) { jedinicna_mjera = jedinicna_mjera.Remove(4); } string sifra_artikla = DTsync.Rows[y]["sifra"].ToString(); Int32 id_stavka = brojac; DateTime datum_racuna = Convert.ToDateTime(DTsync1.Rows[i]["datum_racuna"].ToString()); //popust = (mpc * rab / 100) * kol; //mpc = mpc - (mpc * rab / 100); decimal PreracunataStopaPDV = Convert.ToDecimal((100 * (pdv + porez_potr)) / (100 + pdv + porez_potr)); decimal porez_stavka = ((mpc - (mpc * rab / 100)) * kol) * (PreracunataStopaPDV / 100); neto_bez_pdv = vpc; string vrsta_popusta = ""; string Pop_broj = ""; string rekpr_broj = ""; string iznos_popusta_reklamno = "0"; string sifra_grupe_artikala = " "; string naziv_grupe_artikala = ""; string faktor_poreza = "0"; string nevazno = ""; try { if (provjera_za_prvi == 0) { if (File.Exists(pathTxt + "\\" + id_ducan + "_KSTRACUNa_" + datum_racuna.ToString("dd-MM-yyyy").Replace("-", "") + ".txt")) { File.Delete(pathTxt + "\\" + id_ducan + "_KSTRACUNa_" + datum_racuna.ToString("dd-MM-yyyy").Replace("-", "") + ".txt"); } provjera_za_prvi++; } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } zapisi += "#" + nule(3, Convert.ToString(id_stavka)) + "#;" + "#" + nule(10, Math.Round(kol, 3).ToString("#0.000")).Replace(",", ".") + "#;" + "#" + nule(10, Math.Round(mpc, 2).ToString("#0.00")).Replace(",", ".") + "#;" + "#" + nule(10, Convert.ToString(Math.Round(neto_bez_pdv, 3).ToString("#0.000"))).Replace(",", ".") + "#;" + "#" + nule(2, Convert.ToString(porezna_grupa(pdv_grupa))) + "#;" + "#" + nule(10, Math.Round(((mpc * rab / 100) * kol), 2).ToString("#0.00")).Replace(",", ".") + "#;" + "#" + vrsta_popusta + "#;" + "#" + nule(2, id_kasa) + "#;" + "#" + nule(8, broj) + "#;" + "#" + godina_racuna + "#;" + "#" + sifra_artikla + "#;" + "#" + Pop_broj + "#;" + "#" + rekpr_broj + "#;" + "#" + iznos_popusta_reklamno + "#;" + "#" + id_ducan + "#;" + "#" + sifra_grupe_artikala + "#;" + "#" + naziv_grupe_artikala + "#;" + "#" + razmaci(naziv_artikla) + "#;" + "#" + jedinicna_mjera + "#;" + "#" + faktor_poreza + "#;" + "#" + nule(10, Math.Round(porez_stavka, 2).ToString("#0.00")).Replace(",", ".") + "#;" + "#" + "00000000.00" + "#"; //File.WriteAllText("D:\\" + id_ducan + "_KRACUNI" + Sistemski_datum.ToString("dd-MM-yyyy") + "", zapisi); using (System.IO.StreamWriter file = new System.IO.StreamWriter(pathTxt + "\\" + id_ducan + "_KSTRACUNa_" + datum_racuna.ToString("dd-MM-yyyy").Replace("-", "") + ".txt", true)) { file.WriteLine(zapisi); zapisi = ""; } } } }
private void synkracuni() { string zapisi = "\r\n"; DataTable DTsync1 = new DataTable(); string sync1 = "SELECT broj_racuna, datum_racuna, storno, id_kasa, id_kupac, id_blagajnik, id_ducan FROM racuni WHERE racuni.datum_racuna> '" + DatumOD.ToString("yyyy-MM-dd H:mm:ss") + "' AND racuni.datum_racuna<'" + DatumDO.ToString("yyyy-MM-dd H:mm:ss") + "' ORDER BY CAST(broj_racuna AS int) ASC "; DTsync1 = classSQL.select(sync1, "racuni").Tables[0]; DataTable DTsync = new DataTable(); for (int i = 0; DTsync1.Rows.Count > i; i++) { string broj = DTsync1.Rows[i]["broj_racuna"].ToString(); string storno = "N"; string id_kasa = DTsync1.Rows[i]["id_kasa"].ToString(); string id_kupac = DTsync1.Rows[i]["id_kupac"].ToString(); string id_blagajnik = DTsync1.Rows[i]["id_blagajnik"].ToString(); string id_ducan = ducan; DateTime datum_racuna = Convert.ToDateTime(DTsync1.Rows[i]["datum_racuna"].ToString()); string sync = "SELECT " + " mpc, vpc, kolicina, porez_potrosnja, rabat, porez " + "FROM racun_stavke WHERE broj_racuna = '" + broj + "' AND racun_stavke.id_ducan='" + Util.Korisno.idDucan + "' AND racun_stavke.id_blagajna='" + Util.Korisno.idKasa + "'" + ""; DTsync = classSQL.select(sync, "racuni_stavke").Tables[0]; string godina = dtpOD.Value.Year.ToString(); DateTime Sistemski_datum = DateTime.Now; DateTime Administrativni_datum = dtpOD.Value; DateTime Sistemsko_vrijeme = DateTime.Now; decimal iznos = 0; decimal porez = 0; decimal popust = 0; decimal porez_stavka = 0; decimal povrat = 0; //godina = DTsync1.Rows[i]["godina"].ToString(); //Sistemski_datum = Convert.ToDateTime(DTsync1.Rows[i]["sistemski_datum"].ToString()); //Administrativni_datum = Convert.ToDateTime(DTsync1.Rows[i]["administrativni_datum"].ToString()); //Sistemsko_vrijeme = Convert.ToDateTime(DTsync1.Rows[i]["sistemsko_vrijeme"].ToString()); for (int y = 0; DTsync.Rows.Count > y; y++) { decimal vpc = Convert.ToDecimal(DTsync.Rows[y]["vpc"].ToString()); decimal mpc = Convert.ToDecimal(DTsync.Rows[y]["mpc"].ToString()); decimal porez_potr = Convert.ToDecimal(DTsync.Rows[y]["porez_potrosnja"].ToString()); decimal kol = Convert.ToDecimal(DTsync.Rows[y]["kolicina"].ToString()); decimal rab = Convert.ToDecimal(DTsync.Rows[y]["rabat"].ToString()); decimal pdv = Convert.ToDecimal(DTsync.Rows[y]["porez"].ToString()); decimal PreracunataStopaPDV = Convert.ToDecimal((100 * (pdv + porez_potr)) / (100 + pdv + porez_potr)); popust += (mpc * rab / 100) * kol; mpc = mpc - (mpc * rab / 100); porez_stavka = Math.Round(((mpc * kol) * (PreracunataStopaPDV / 100)), 3) + porez_stavka; iznos = Math.Round((mpc * kol), 3) + iznos; porez = (((mpc * kol) * PreracunataStopaPDV) / 100) + porez; } string status = "1"; string obracun_smjene = "N"; string izdanih_r_racuna = id_kupac; string izdanih_kopija_potvrda = "0"; string broj_izvrsenih_korekcija = "0"; string sifra_posl_partnera = ""; string popust_np = "0"; string broj_smjene = "0"; string ind_knj = "N"; //privremeno - blagajnik uvijek mora biti 001 string blagajnik = "001"; try { if (i == 0) { if (File.Exists(pathTxt + "\\" + id_ducan + "_KRACUNI_" + datum_racuna.ToString("dd-MM-yyyy").Replace("-", "") + ".txt")) { File.Delete(pathTxt + "\\" + id_ducan + "_KRACUNI_" + datum_racuna.ToString("dd-MM-yyyy").Replace("-", "") + ".txt"); } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } zapisi += "#" + nule(8, broj) + "#;" + "#" + godina + "#;" + "#" + Sistemsko_vrijeme.ToString("dd.MM.yyyy") + "#;" + "#" + Sistemsko_vrijeme.ToString("dd.MM.yyyy HH:mm") + "#;" + "#" + Administrativni_datum.ToString("dd.MM.yyyy") + "#;" + "#" + status + "#;" + "#" + nule(10, Math.Round(iznos, 2).ToString("#0.00")).Replace(",", ".") + "#;" + "#" + nule(10, Math.Round(porez, 2).ToString("#0.00")).Replace(",", ".") + "#;" + "#" + nule(10, Math.Round(popust, 2).ToString("#0.00")).Replace(",", ".") + "#;" + "#" + nule(10, Math.Round(povrat, 2).ToString("#0.00")).Replace(",", ".") + "#;" + "#" + obracun_smjene + "#;" + "#" + r_racun(izdanih_r_racuna) + "#;" + "#" + izdanih_kopija_potvrda + "#;" + "#" + broj_izvrsenih_korekcija + "#;" + "#" + nule(2, id_kasa) + "#;" + "#" + blagajnik + "#;" + "#" + sifra_posl_partnera + "#;" + "#" + popust_np + "#;" + "#" + id_ducan + "#;" + "#" + storno + "#;" + "#" + broj_smjene + "#;" + "#" + id_ducan + "#;" + "#" + ind_knj + "#"; using (System.IO.StreamWriter file = new System.IO.StreamWriter(pathTxt + "\\" + id_ducan + "_KRACUNI_" + datum_racuna.ToString("dd-MM-yyyy").Replace("-", "") + ".txt", true)) { file.WriteLine(zapisi); zapisi = ""; } } }