public cari_kart GirisSuperAdmin(string giz_kullanici_adi, string giz_kullanici_sifre)
{
cari_kart cariKart = null;
#region Query
string query = @"
SELECT
carikart_id,
cari_unvan
FROM carikart
WHERE giz_kullanici_adi is not null
AND giz_kullanici_adi <> ''
AND isnull(kayit_silindi,0) = 0
AND giz_kullanici_adi = @giz_kullanici_adi AND giz_kullanici_sifre = @giz_kullanici_sifre e AND giz_yazilim_kodu = 9999
";
#endregion
#region Parameters
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@giz_kullanici_adi", giz_kullanici_adi),
new SqlParameter("@giz_kullanici_sifre", giz_kullanici_sifre)
};
#endregion
dt = SqlHelper.ExecuteDataset(ConnectionStrings.SqlConn, CommandType.Text, query, parameters).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
cariKart = new cari_kart();
cariKart.carikart_id = dt.Rows[0]["carikart_id"].acekaToLong();
cariKart.cari_unvan = dt.Rows[0]["cari_unvan"].acekaToString();
}
return(cariKart);
}
// Talimat Turu Tanımlama için Fasoncu Listesi
public List <cari_kart> TalimatFasoncuListesi()
{
List <cari_kart> fasoncular = null;
#region Query
string query = @"
SELECT
carikart_id,
cari_unvan
from carikart
where kayit_silindi=0 and carikart_tipi_id=14
";
#endregion
dt = SqlHelper.ExecuteDataset(ConnectionStrings.SqlConn, CommandType.Text, query).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
cari_kart fason = null;
fasoncular = new List <cari_kart>();
for (int i = 0; i < dt.Rows.Count; i++)
{
fason = new cari_kart();
fason.carikart_id = dt.Rows[i]["carikart_id"].acekaToLong();
fason.cari_unvan = dt.Rows[i]["cari_unvan"].acekaToString();
fasoncular.Add(fason);
fason = null;
}
return(fasoncular);
}
return(null);
}
/// <summary>
/// Select için Depo yerlerini getiren metod. carikart_turu_id =3 ve carikart_tipi_id=3 olanlar.
/// </summary>
/// <returns>List<carikart></returns>
public List <cari_kart> DepoListesi()
{
#region Query
string query = @"SELECT
carikart_id,cari_unvan
FROM carikart
WHERE kayit_silindi = 0 and carikart_turu_id = 3 and carikart_tipi_id = 3
ORDER BY cari_unvan";
#endregion
#region Parameters
#endregion
dt = SqlHelper.ExecuteDataset(ConnectionStrings.SqlConn, CommandType.Text, query).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
carikartlar = new List <cari_kart>();
for (int i = 0; i < dt.Rows.Count; i++)
{
carikart = new cari_kart();
carikart.carikart_id = dt.Rows[i]["carikart_id"].acekaToLong();
carikart.cari_unvan = dt.Rows[i]["cari_unvan"].ToString();
carikartlar.Add(carikart);
carikart = null;
}
}
return(carikartlar);
}
public cari_kart Getir(long carikart_id)
{
#region Query
string query = @"
--Table[0]
SELECT c.carikart_id,c.statu,c.cari_unvan,tur.carikart_turu_adi ,c.transfer_depo_id,c.kayit_silindi,c.ozel_kod,
c.giz_yazilim_kodu,ea.earsiv_seri,ef.efatura_seri,m.muh_kod,m.sirket_id,masraf.masraf_merkezi_adi,sy.acilis_tarihi,sy.kapanis_tarihi,sy.kapali,
c.ana_carikart_id,c.cari_unvan as baglistokyeri_unvan,
--ck.cari_unvan as baglistokyeri_unvan,tip.carikart_tipi_id,tur.carikart_turu_id,masraf.masraf_merkezi_id,
sy.transfer_depo_kullan --,c.ana_carikart_id
--CK.ana_carikart_id
,gs.sirket_adi,gs.sirket_id, tip.carikart_tipi_adi as stokyeri_tipi_adi,
--ana_carikart_id > 0 ise sorguya ana_cari_unvan adında bir alan ekleniyor
(CASE
WHEN c.ana_carikart_id > 0 THEN (Select cari_unvan from carikart where carikart_id = C.ana_carikart_id)
END) as 'ana_cari_unvan',c.carikart_turu_id,c.carikart_tipi_id,masraf.masraf_merkezi_id
FROM carikart c
--LEFT join carikart ck on ck.ana_carikart_id= c.carikart_id
LEFT join carikart_earsiv ea on ea.carikart_id=c.carikart_id
INNER join giz_sabit_carikart_tipi tip on tip.carikart_tipi_id=c.carikart_tipi_id --and tip.carikart_tipi_id in(2,3)
INNER join giz_sabit_carikart_turu tur on tur.carikart_turu_id=c.carikart_turu_id and tur.carikart_turu_id = 3
LEFT join carikart_efatura ef on ef.carikart_id=c.carikart_id
LEFT join carikart_muhasebe m on m.carikart_id=c.carikart_id
LEFT join muhasebe_tanim_masrafmerkezleri masraf on masraf.masraf_merkezi_id = m.masraf_merkezi_id
LEFT join giz_sirket gs on gs.sirket_id=m.sirket_id
LEFT join carikart_stokyeri sy on sy.carikart_id=c.carikart_id
WHERE c.carikart_id= @carikart_id --AND C.statu=1
";
#endregion
#region Parameters
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@carikart_id", carikart_id)
};
#endregion
ds = SqlHelper.ExecuteDataset(ConnectionStrings.SqlConn, CommandType.Text, query, parameters);
if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
{
carikart = new cari_kart();
carikart.carikart_id = ds.Tables[0].Rows[0]["carikart_id"].acekaToLong();
carikart.statu = ds.Tables[0].Rows[0]["statu"].acekaToBool();
carikart.cari_unvan = ds.Tables[0].Rows[0]["cari_unvan"].ToString();
carikart.ozel_kod = ds.Tables[0].Rows[0]["ozel_kod"].ToString();
carikart.ana_carikart_id = ds.Tables[0].Rows[0]["ana_carikart_id"].acekaToLong();
carikart.ana_cari_unvan = ds.Tables[0].Rows[0]["ana_cari_unvan"].ToString();
carikart.giz_sabit_carikart_turu = new giz_sabit_carikart_turu();
carikart.giz_sabit_carikart_turu.carikart_turu_adi = ds.Tables[0].Rows[0]["carikart_turu_adi"].ToString();
carikart.giz_sabit_carikart_turu.carikart_turu_id = ds.Tables[0].Rows[0]["carikart_turu_id"].acekaToByte();
carikart.giz_sabit_carikart_tipi = new giz_sabit_carikart_tipi();
carikart.giz_sabit_carikart_tipi.carikart_tipi_adi = ds.Tables[0].Rows[0]["stokyeri_tipi_adi"].ToString();
carikart.giz_sabit_carikart_tipi.carikart_tipi_id = ds.Tables[0].Rows[0]["carikart_tipi_id"].acekaToByte();
carikart.transfer_depo_id = ds.Tables[0].Rows[0]["transfer_depo_id"].acekaToLong();
carikart.giz_yazilim_kodu = ds.Tables[0].Rows[0]["giz_yazilim_kodu"].acekaToShort();
carikart.carikart_earsiv = new carikart_earsiv();
carikart.carikart_earsiv.earsiv_seri = ds.Tables[0].Rows[0]["earsiv_seri"].ToString();
carikart.carikart_efatura = new carikart_efatura();
carikart.carikart_efatura.efatura_seri = ds.Tables[0].Rows[0]["efatura_seri"].ToString();
carikart.carikart_muhasebe = new carikart_muhasebe();
carikart.carikart_muhasebe.muh_kod = ds.Tables[0].Rows[0]["muh_kod"].ToString();
carikart.carikart_muhasebe.sirket_id = ds.Tables[0].Rows[0]["sirket_id"].acekaToByte();
carikart.muhasebe_tanim_masrafmerkezleri = new muhasebe_tanim_masrafmerkezleri();
carikart.muhasebe_tanim_masrafmerkezleri.masraf_merkezi_adi = ds.Tables[0].Rows[0]["masraf_merkezi_adi"].ToString();
carikart.muhasebe_tanim_masrafmerkezleri.masraf_merkezi_id = ds.Tables[0].Rows[0]["masraf_merkezi_id"].acekaToInt();
carikart.carikart_stokyeri = new carikart_stokyeri();
carikart.carikart_stokyeri.acilis_tarihi = ds.Tables[0].Rows[0]["acilis_tarihi"].acekaToDateTimeWithNullable();
carikart.carikart_stokyeri.kapanis_tarihi = ds.Tables[0].Rows[0]["kapanis_tarihi"].acekaToDateTimeWithNullable();
carikart.carikart_stokyeri.kapali = ds.Tables[0].Rows[0]["kapali"].acekaToBoolWithNullable();
carikart.carikart_stokyeri.transfer_depo_kullan = ds.Tables[0].Rows[0]["transfer_depo_kullan"].acekaToBoolWithNullable();
carikart.giz_sirket = new giz_sirket();
carikart.giz_sirket.sirket_adi = ds.Tables[0].Rows[0]["sirket_adi"].ToString();
carikart.giz_sirket.sirket_id = ds.Tables[0].Rows[0]["sirket_id"].acekaToByte();
}
return(carikart);
}
/// <summary>
/// Cari bulma fonksiyonu
/// </summary>
/// <param name="cari"></param>
/// <returns></returns>
public List <cari_kart> DepoBul(long carikart_id = 0, string cari_unvan = "", string ozel_kod = "", byte carikart_tipi_id = 0)
{
short parameterControl = 0;
#region Query
string orStatement = "";
if (carikart_id > 0)
{
parameterControl++;
orStatement += "c.carikart_id = @carikart_id OR ";
}
if (!string.IsNullOrEmpty(cari_unvan.TrimEnd()))
{
parameterControl++;
orStatement += "c.cari_unvan like @unvan OR ";
}
if (!string.IsNullOrEmpty(ozel_kod.TrimEnd()))
{
parameterControl++;
orStatement += "c.ozel_kod like @ozelkod OR ";
}
if (carikart_tipi_id > 0)
{
parameterControl++;
orStatement += "c.carikart_tipi_id = @carikart_tipi_id OR ";
}
if (!string.IsNullOrEmpty(orStatement))
{
orStatement = "(" + orStatement.TrimEnd(new char[] { 'O', 'R', ' ' }) + ")";
}
orStatement += "";
string query = @"
SELECT c.carikart_id,cari_unvan,ozel_kod,giz_yazilim_kodu,c.statu,
ct.carikart_tipi_id,ct.carikart_tipi_adi,ctur.carikart_turu_id ,ctur.carikart_turu_adi,c.fiyattipi,
ca.adres,ca.tel1,ca.email,ca.websitesi,f.pb,ana_carikart_id,cf.odeme_tipi,
o.satin_alma_sorumlu_carikart_id,o.satis_sorumlu_carikart_id, cf.finans_sorumlu_carikart_id,
(CASE WHEN f.finans_sorumlu_carikart_id > 0 THEN (SELECT cari_unvan FROM carikart WHERE carikart_id = f.finans_sorumlu_carikart_id) END) as 'finans_sorumlu_cari_unvan',
(CASE WHEN f.ilgili_sube_carikart_id > 0 THEN (SELECT cari_unvan FROM carikart WHERE carikart_id = f.ilgili_sube_carikart_id) END) as 'ilgili_sube_cari_unvan',
(CASE WHEN o.satin_alma_sorumlu_carikart_id > 0 THEN (SELECT cari_unvan FROM carikart WHERE carikart_id = o.satin_alma_sorumlu_carikart_id) END) as 'satin_alma_sorumlu_cari_unvan',
(CASE WHEN o.satis_sorumlu_carikart_id > 0 THEN (SELECT cari_unvan FROM carikart WHERE carikart_id = o.satis_sorumlu_carikart_id) END) as 'satis_sorumlu_cari_unvan',
(CASE WHEN c.ana_carikart_id > 0 THEN (SELECT cari_unvan FROM carikart WHERE carikart_id = c.ana_carikart_id) END) as 'ana_cari_unvan'
--(CASE WHEN c.ana_carikart_id > 0 THEN (SELECT cari_unvan FROM carikart WHERE ana_carikart_id = c.carikart_id) END) as 'ilgili_sube'
FROM
carikart c
LEFT JOIN carikart_finans f on f.carikart_id=c.carikart_id
LEFT JOIN carikart_firma_ozel o on o.carikart_id=c.carikart_id
LEFT JOIN giz_sabit_carikart_turu ctur on ctur.carikart_turu_id = c.carikart_turu_id
INNER join giz_sabit_carikart_tipi ct on c.carikart_tipi_id=ct.carikart_tipi_id and ct.carikart_tipi_id in(2,3)
LEFT JOIN carikart_genel_adres ca on c.carikart_id=ca.carikart_id
LEFT JOIN carikart_finans cf on cf.carikart_id=c.carikart_id
WHERE " + orStatement + " ";
#endregion
#region Parameters
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@carikart_id", carikart_id),
new SqlParameter("@unvan", "%" + cari_unvan + "%"),
new SqlParameter("@ozelkod", "%" + ozel_kod + "%"),
new SqlParameter("@carikart_tipi_id", carikart_tipi_id)
};
#endregion
if (parameterControl > 0)
{
dt = SqlHelper.ExecuteDataset(ConnectionStrings.SqlConn, CommandType.Text, query, parameters).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
carikartlar = new List <cari_kart>();
for (int i = 0; i < dt.Rows.Count; i++)
{
carikart = new cari_kart();
carikart.carikart_id = dt.Rows[i]["carikart_id"].acekaToLong();
carikart.statu = dt.Rows[i]["statu"].acekaToBool();
carikart.carikart_turu_id = dt.Rows[i]["carikart_turu_id"].acekaToByte();
carikart.carikart_tipi_id = dt.Rows[i]["carikart_tipi_id"].acekaToByte();
carikart.cari_unvan = dt.Rows[i]["cari_unvan"].ToString();
carikart.ozel_kod = dt.Rows[i]["ozel_kod"].ToString();
carikart.fiyattipi = dt.Rows[i]["fiyattipi"].ToString();
carikart.giz_yazilim_kodu = dt.Rows[i]["giz_yazilim_kodu"].acekaToShort();
carikart.ana_cari_unvan = dt.Rows[i]["ana_cari_unvan"].ToString();
carikart.ana_carikart_id = dt.Rows[i]["ana_carikart_id"].acekaToLong();
carikart.giz_sabit_carikart_turu = new giz_sabit_carikart_turu();
carikart.giz_sabit_carikart_turu.carikart_turu_id = dt.Rows[i]["carikart_turu_id"].acekaToByte();
carikart.giz_sabit_carikart_turu.carikart_turu_adi = dt.Rows[i]["carikart_turu_adi"].ToString();
carikart.giz_sabit_carikart_tipi = new giz_sabit_carikart_tipi();
carikart.giz_sabit_carikart_tipi.carikart_tipi_id = dt.Rows[i]["carikart_tipi_id"].acekaToByte();
carikart.giz_sabit_carikart_tipi.carikart_tipi_adi = dt.Rows[i]["carikart_tipi_adi"].ToString();
carikart.carikart_genel_adres = new List <carikart_genel_adres>();
carikart.carikart_genel_adres.Add(new carikart_genel_adres
{
adres = dt.Rows[i]["adres"].ToString(),
tel1 = dt.Rows[i]["tel1"].ToString(),
email = dt.Rows[i]["email"].ToString(),
websitesi = dt.Rows[i]["websitesi"].ToString(),
});
//Cari finans
carikart.carikart_finans = new carikart_finans();
carikart.carikart_finans.finans_sorumlu_carikart_id = dt.Rows[i]["finans_sorumlu_carikart_id"].acekaToLong();
carikart.carikart_finans.finans_sorumlu_cari_unvan = dt.Rows[i]["finans_sorumlu_cari_unvan"].ToString();
carikart.carikart_finans.odeme_tipi = dt.Rows[i]["odeme_tipi"].acekaToByte();
//Cari Kart Firma Özel
carikart.carikart_firma_ozel = new carikart_firma_ozel();
carikart.carikart_firma_ozel.ozel = dt.Rows[i]["ozel_kod"].ToString();
carikart.carikart_firma_ozel.carikart_id = dt.Rows[i]["carikart_id"].acekaToLong();
carikart.carikart_firma_ozel.satin_alma_sorumlu_carikart_id = dt.Rows[i]["satin_alma_sorumlu_carikart_id"].acekaToLong();
carikart.carikart_firma_ozel.satin_alma_sorumlu_cari_unvan = dt.Rows[i]["satin_alma_sorumlu_cari_unvan"].ToString();
carikart.carikart_firma_ozel.satis_sorumlu_carikart_id = dt.Rows[i]["satis_sorumlu_carikart_id"].acekaToLong();
carikart.carikart_firma_ozel.satis_sorumlu_cari_unvan = dt.Rows[i]["satis_sorumlu_cari_unvan"].ToString();
// carikart.carikart_finans = new carikart_finans();
//carikart.carikart_finans.pb = dt.Rows[i]["pb"].ToString();
//carikart.carikart_finans.finans_sorumlu_carikart_id = dt.Rows[i]["finans_sorumlu_carikart_id"].acekaToLong();
//carikart.carikart_firma_ozel = new carikart_firma_ozel();
//carikart.carikart_firma_ozel.satin_alma_sorumlu_carikart_id = dt.Rows[i]["satin_alma_sorumlu_carikart_id"].acekaToLong();
//carikart.carikart_firma_ozel.satis_sorumlu_carikart_id = dt.Rows[i]["satis_sorumlu_carikart_id"].acekaToLong();
carikartlar.Add(carikart);
carikart = null;
//carikart = new cari_kart();
//carikart.carikart_id = dt.Rows[i]["carikart_id"].acekaToLong();
//carikart.statu = dt.Rows[i]["statu"].acekaToBool();
//carikart.carikart_turu_id = dt.Rows[i]["carikart_turu_id"].acekaToByte();
//carikart.carikart_tipi_id = dt.Rows[i]["carikart_tipi_id"].acekaToByte();
//carikart.cari_unvan = dt.Rows[i]["cari_unvan"].ToString();
//carikart.ozel_kod = dt.Rows[i]["ozel_kod"].ToString();
//carikart.fiyattipi = dt.Rows[i]["fiyattipi"].ToString();
//carikart.giz_yazilim_kodu = dt.Rows[i]["giz_yazilim_kodu"].acekaToShort();
//carikart.giz_sabit_carikart_turu = new giz_sabit_carikart_turu();
//carikart.giz_sabit_carikart_turu.carikart_turu_id = dt.Rows[i]["carikart_turu_id"].acekaToByte();
//carikart.giz_sabit_carikart_turu.carikart_turu_adi = dt.Rows[i]["carikart_turu_adi"].ToString();
//carikart.giz_sabit_carikart_tipi = new giz_sabit_carikart_tipi();
//carikart.giz_sabit_carikart_tipi.carikart_tipi_id = dt.Rows[i]["carikart_tipi_id"].acekaToByte();
//carikart.giz_sabit_carikart_tipi.carikart_tipi_adi = dt.Rows[i]["carikart_tipi_adi"].ToString();
//carikart.carikart_finans = new carikart_finans();
//carikart.carikart_finans.pb = dt.Rows[i]["pb"].ToString();
//carikartlar.Add(carikart);
//carikart = null;
}
}
}
return(carikartlar);
}
