public static string ReadConfigParam(string Progname, string SectionStr, string Keyname, string DefaultValue = "")
{
string result = "";
_sqlProvider = new SqlProvider("SELECT VALUESTR FROM CONFIG WHERE PROGNAME=@PROGNAME AND SECTIONSTR=@SECTIONSTR AND KEYNAME=@KEYNAME ", false);
_sqlProvider.AddParameter("PROGNAME", Progname);
_sqlProvider.AddParameter("SECTIONSTR", SectionStr);
_sqlProvider.AddParameter("KEYNAME", Keyname);
using (var dt = new DataTable())
{
dt.Load(_sqlProvider.ExecuteReader());
if (dt.Rows.Count >= 1)
{
result = dt.Rows[0]["VALUESTR"].ToString();
}
else
{
result = DefaultValue;
_sqlProvider =
new SqlProvider(
"INSERT INTO CONFIG (PROGNAME,SECTIONSTR,KEYNAME,VALUESTR) VALUES(@PROGNAME,@SECTIONSTR,@KEYNAME,@VALUESTR) ",
false);
_sqlProvider.AddParameter("PROGNAME", Progname);
_sqlProvider.AddParameter("SECTIONSTR", SectionStr);
_sqlProvider.AddParameter("KEYNAME", Keyname);
_sqlProvider.AddParameter("VALUESTR", result);
_sqlProvider.ExecuteNonQuery();
}
}
return(result);
}
public static DataTable SubeListesi()
{
var _sqlProvider = new SqlProvider("SELECT DISTINCT SUBEKODU FROM (SELECT TOP 2000 SUBEKODU FROM VFR_KASA_ANALIZ ORDER BY TARIH DESC) TBL UNION ALL SELECT ' ' ORDER BY SUBEKODU", false);
var dt = new DataTable();
dt.Load(_sqlProvider.ExecuteReader());
return(dt);
}
private void frmFaturaDetay_Load(object sender, EventArgs e)
{
_sqlProvider = new SqlProvider("SELECT S.KODU,ADI,FI.FADET,FBFIYAT,ARATOPLAM FROM FATISL FI INNER JOIN STOK S ON S.STOKID=FI.STOKID WHERE FATID=@FATID", false);
_sqlProvider.AddParameter("FATID", _fatid);
DataTable dtFatIsl = new DataTable();
dtFatIsl.Load(_sqlProvider.ExecuteReader());
dataGridView1.DataSource = dtFatIsl;
}
public static bool TekrarEtiketYazdirilacakKaydiBul(string partino, ref int stokid, ref double miktar)
{
var _sqlProvider = new SqlProvider("SELECT TOP 1 * FROM PARTI_NO_TERAZI WHERE PARTINO = @PARTINO ", false);
_sqlProvider.AddParameter("PARTINO", partino);
var dt = new DataTable();
dt.Load(_sqlProvider.ExecuteReader());
if (dt.Rows.Count == 1)
{
stokid = Convert.ToInt32(dt.Rows[0]["STOKID"].ToString());
miktar = Convert.ToDouble(dt.Rows[0]["MIKTAR"].ToString());
}
return(dt.Rows.Count == 1);
}
public static DataTable PartiNoUretilecekALISFaturalariListesi(DateTime tarih1, DateTime tarih2, string DepoID)
{
//SELECT DISTINCT TARIH, FISTIPI, ISLEM, FATNO, FATID, DEPOID, DEPOADI, KODU, CARIUNVANI, TOPLAM
//FROM QMUH_STOKISL
//WHERE TARIH BETWEEN '2016-04-01' AND '2016-04-03' AND FISTIPI = 'FATURA' AND ISLEM = 'ALIS' AND BARKOD IS NULL
_sqlProvider = new SqlProvider(
" SELECT DISTINCT TARIH, FISTIPI, ISLEM, FATNO, FATID, DEPOID, DEPOADI, KODU, CARIUNVANI, round(SUM(TOPLAM),2) TOPLAM" +
" FROM QMUH_STOKISL" +
" WHERE TARIH BETWEEN @TARIH1 AND @TARIH2 AND FISTIPI = 'FATURA' AND ISLEM = 'ALIS' AND BARKOD IS NULL AND DEPOID = @DEPOID " +
" GROUP BY TARIH, FISTIPI, ISLEM, FATNO, FATID, DEPOID, DEPOADI, KODU, CARIUNVANI "
);
_sqlProvider.AddParameter("TARIH1", tarih1);
_sqlProvider.AddParameter("TARIH2", tarih2);
_sqlProvider.AddParameter("DEPOID", DepoID);
var d = new DataTable();
d.Load(_sqlProvider.ExecuteReader());
return(d);
}
public static bool ALISFaturaSatirlarinaPartiNoUret(int fatid, DateTime faturatarihi)
{
_sqlProvider = new SqlProvider("SELECT * FROM FATISL WHERE FATID=@FATID AND ISNULL(BARKOD,'')='' ", false);
_sqlProvider.AddParameter("FATID", fatid);
DataTable dtFatIsl = new DataTable();
dtFatIsl.Load(_sqlProvider.ExecuteReader());
foreach (DataRow rowFatIsl in dtFatIsl.Rows)
{
string pNo = PartiNo.PartiNoUret(faturatarihi, "HAMMADDEPARTINO");
_sqlProvider = new SqlProvider("UPDATE FATISL SET BARKOD=@PARTINO WHERE FATISLID=@FATISLID", false);
_sqlProvider.AddParameter("PARTINO", pNo);
_sqlProvider.AddParameter("FATISLID", rowFatIsl["FATISLID"]);
_sqlProvider.ExecuteNonQuery();
dbOperation.PartiNoOlusumuKaydet(pNo, Convert.ToInt32(rowFatIsl["STOKID"]), Convert.ToDouble(rowFatIsl["ADET"]), 0, faturatarihi, faturatarihi);
}
return(true);
}
public static DataTable UrunKodunaGoreReceteDetayiGetir(string urunkodu)
{
// SELECT URUNID, U.KODU URUNKODU, U.ADI URUNADI, RECETE.STOKID, S.KODU, S.ADI, RECETE.ORAN, RECETE.RECETEKATSAYISI
// FROM RECETE
// INNER JOIN STOK U ON RECETE.URUNID = U.STOKID
// INNER JOIN STOK S ON RECETE.STOKID = S.STOKID
// WHERE URUNID = 3760
_sqlProvider = new SqlProvider(
" SELECT URUNID, U.KODU URUNKODU, U.ADI URUNADI, RECETE.STOKID, S.KODU, S.ADI, RECETE.ORAN, RECETE.RECETEKATSAYISI, RECETE.FIRE, RECETE.XBIRIM " +
" FROM RECETE " +
" INNER JOIN STOK U ON RECETE.URUNID = U.STOKID " +
" INNER JOIN STOK S ON RECETE.STOKID = S.STOKID " +
" WHERE U.KODU = @URUNKODU ", false);
_sqlProvider.AddParameter("URUNKODU", urunkodu);
var dt = new DataTable();
dt.Load(_sqlProvider.ExecuteReader());
return(dt);
}
public static DataTable ParcalanacakHammaddeListesi(DateTime tar1, DateTime tar2, string DepoID)
{
//SELECT SI.TARIH,FATNO,CARIUNVANI,BARKOD,STOKKODU,ADI,SBIRIMI BIRIM, GCADET MIKTAR,ROUND(NFIYAT, 2) FIYAT, ROUND(GCADET * NFIYAT, 2) TUTAR,*
//FROM QMUH_STOKISL SI
// LEFT OUTER JOIN PARCALAMA P ON P.STOKID = SI.STOKID AND P.TARIH = SI.TARIH AND P.ACIKLAMA = SI.BARKOD
//WHERE SI.TARIH BETWEEN '2016-04-01' AND '2016-04-03' AND FISTIPI = 'FATURA' AND ISLEM = 'ALIS' AND SI.DEPOID = 1 AND BARKOD IS NOT NULL
_sqlProvider = new SqlProvider(
" SELECT SI.TARIH,FATNO,CARIUNVANI,BARKOD,SI.STOKID,STOKKODU,ADI,SBIRIMI BIRIM, GCADET MIKTAR,ROUND(NFIYAT, 2) FIYAT, ROUND(GCADET * NFIYAT, 2) TUTAR " +
" FROM QMUH_STOKISL SI " +
" LEFT OUTER JOIN PARCALAMA P ON P.STOKID = SI.STOKID AND P.TARIH = SI.TARIH AND P.ACIKLAMA = SI.BARKOD " +
" WHERE SI.TARIH BETWEEN @TARIH1 AND @TARIH2 AND FISTIPI = 'FATURA' AND ISLEM = 'ALIS' AND SI.DEPOID = @DEPOID AND BARKOD IS NOT NULL AND ADI LIKE '%(PAR_ALANACAK)%' AND P.ACIKLAMA IS NULL " +
" ORDER BY SI.TARIH, SI.ADI ", false);
_sqlProvider.AddParameter("TARIH1", tar1);
_sqlProvider.AddParameter("TARIH2", tar2);
_sqlProvider.AddParameter("DEPOID", DepoID);
var dt = new DataTable();
dt.Load(_sqlProvider.ExecuteReader());
return(dt); //dataGridView2.DataSource = dt;
}
public void StokDoldur()
{
_sqlProvider = new SqlProvider("SELECT S.STOKID, S.KODU, S.ADI, S.BIRIM, S.EK3 ETIKETTURU ,A.EK1,A.EK2,A.EK3,A.EK4,A.EK5,A.EK6,A.EK7,A.EK8,A.EK9,A.EK10,A.ADET1,A.ADET2,A.ADET3,A.ADET4,A.ADET5,A.ADET6,A.ADET7,A.ADET8,A.ADET9,A.DARA1,A.DARA2,A.DARA3,A.DARA4,A.DARA5,A.DARA6,A.DARA7,A.DARA8,A.DARA9 FROM STOK AS S LEFT OUTER JOIN ARTETIKETDETAY AS A ON A.STOKID = S.STOKID WHERE KODU LIKE 'S91%' AND HLISTE=1 AND (ISNULL(S.KKALDIR, 0) = 0) AND (S.TURU = 'Hammadde') ORDER BY S.KODU", false);
_dtStok = new DataTable();
_dtStok.Load(_sqlProvider.ExecuteReader());
}