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 = "";
}
}
}