void LoadGrid()
{
try {
KasaHareketDurumlari helper = GetKasaHareketDurumlari(checkedListBoxHareketTipleri);
int yil = int.Parse(maskedTextBoxYil.Text);
_source = _mngKasaHar.AyAyGelirGiderRaporu(helper, cmboxKasalar.Text, UserInfo.Sube.Id, yil);
_source.Replace("Ay", "1", "Ocak"); _source.Replace("Ay", "2", "Şubat");
_source.Replace("Ay", "3", "Mart"); _source.Replace("Ay", "4", "Nisan");
_source.Replace("Ay", "5", "Mayıs"); _source.Replace("Ay", "6", "Haziran");
_source.Replace("Ay", "7", "Temmuz"); _source.Replace("Ay", "8", "Ağustos");
_source.Replace("Ay", "9", "Eylül");
_source.Replace("Ay", "01", "Ocak"); _source.Replace("Ay", "02", "Şubat");
_source.Replace("Ay", "03", "Mart"); _source.Replace("Ay", "04", "Nisan");
_source.Replace("Ay", "05", "Mayıs"); _source.Replace("Ay", "06", "Haziran");
_source.Replace("Ay", "07", "Temmuz"); _source.Replace("Ay", "08", "Ağustos");
_source.Replace("Ay", "09", "Eylül");
_source.Replace("Ay", "10", "Ekim");
_source.Replace("Ay", "11", "Kasım"); _source.Replace("Ay", "12", "Aralık");
dataGridView1.DataSource = _source;
if (_source != null && _source.Rows.Count > 0)
{
tslabToplamGelir.Text = ToplamGelir().ToString("F2");
tslabToplamGider.Text = ToplamGider().ToString("F2");
tslabToplamBakiye.Text = ToplamBakiye().ToString("F2");
dataGridView1.Columns["Bakiye"].DefaultCellStyle.Format = "F2";
}
} catch (Exception exc) {
LogWrite.Write(exc);
MessageBox.Show(exc.Message);
}
}
void LoadGrid()
{
try {
KasaHareketDurumlari helper = GetKasaHareketDurumlari(checkedListBoxHareketTipleri);
helper.BeginDate = dtpStart.Value;
helper.EndDate = dtpFinish.Value;
_data = _mngKasaHar.GetGunlukGelirGider(helper, cmboxKasalar.Text, UserInfo.Sube.Id);
dataGridView1.DataSource = _data;
if (_data != null && _data.Count > 0)
{
tslabToplamGelir.Text = _data.Sum(x => x.Gelir).ToString("F2");
tslabToplamGider.Text = _data.Sum(x => x.Gider).ToString("F2");
tslabToplamBakiye.Text = _data.Sum(x => x.Bakiye).ToString("F2");
}
} catch (Exception exc) {
LogWrite.Write(exc);
MessageBox.Show(exc.Message);
}
}
public KasaHareketDurumlari GetKasaHareketDurumlari(CheckedListBox listBox)
{
KasaHareketDurumlari helper = new KasaHareketDurumlari();
foreach (object itemChecked in listBox.CheckedItems)
{
itemChecked.IfEqualToSetTrue(itemChecked.ToString(), "MalAlış", (s) => { helper.MalAlis = s; });
itemChecked.IfEqualToSetTrue(itemChecked.ToString(), "MalSatış", (s) => { helper.MalSatis = s; });
itemChecked.IfEqualToSetTrue(itemChecked.ToString(), "CaridenTahsilat", (s) => { helper.CaridenTahsilat = s; });
itemChecked.IfEqualToSetTrue(itemChecked.ToString(), "CariyeÖdeme", (s) => { helper.CariyeOdeme = s; });
itemChecked.IfEqualToSetTrue(itemChecked.ToString(), "BankayaYatan", (s) => { helper.BankayaYatan = s; });
itemChecked.IfEqualToSetTrue(itemChecked.ToString(), "BankadanÇekilen", (s) => { helper.BankadanCekilen = s; });
itemChecked.IfEqualToSetTrue(itemChecked.ToString(), "ÇekGelir", (s) => { helper.CekGelir = s; });
itemChecked.IfEqualToSetTrue(itemChecked.ToString(), "ÇekGider", (s) => { helper.CekGider = s; });
itemChecked.IfEqualToSetTrue(itemChecked.ToString(), "SenetGelir", (s) => { helper.SenetGelir = s; });
itemChecked.IfEqualToSetTrue(itemChecked.ToString(), "SenetGider", (s) => { helper.SenetGider = s; });
itemChecked.IfEqualToSetTrue(itemChecked.ToString(), "ÖzelGelir", (s) => { helper.OzelGelir = s; });
itemChecked.IfEqualToSetTrue(itemChecked.ToString(), "ÖzelGider", (s) => { helper.OzelGider = s; });
}
return(helper);
}
public DataTable AyAyGelirGiderRaporu(KasaHareketDurumlari durumlar, string kasaKodu, string subeKodu, int yil)
{
IDbConnection con = Session.Connection;
IDbCommand cmd = con.CreateCommand();
StringBuilder query = new StringBuilder();
query.AppendFormat(@"select {0} Ay,SUM(case when kh.GELIR_GIDER='G' then
kh.Tutar else 0 end) as GelirToplam
,sum(case when kh.GELIR_GIDER='C' then kh.Tutar else 0 end) as GiderToplam
,sum((case when kh.GELIR_GIDER='G' then kh.Tutar else 0 end)-
(case when kh.GELIR_GIDER='C' then kh.Tutar else 0 end)) as Bakiye
from KasaHareket kh where ", SqlTypeHelper.GetMonth("kh.Tarih"));
string or = "";
string and = "";
string beginParentez = "(";
query.ConditionAppend(durumlar.MalAlis,
string.Format("{0} (kh.TIP='F' and kh.GELIR_GIDER='C') ", beginParentez), () =>
{ or = "or"; and = "and"; beginParentez = ""; });
query.ConditionAppend(durumlar.MalSatis,
string.Format("{0} {1} (kh.TIP='F' and kh.GELIR_GIDER='G') ", beginParentez, or), () =>
{ or = "or"; and = "and"; beginParentez = ""; });
query.ConditionAppend(durumlar.CaridenTahsilat,
string.Format("{0} {1} (kh.TIP='C' and kh.GELIR_GIDER='G') ", beginParentez, or), () =>
{ or = "or"; and = "and"; beginParentez = ""; });
query.ConditionAppend(durumlar.CariyeOdeme,
string.Format("{0} {1} (kh.TIP='C' and kh.GELIR_GIDER='C') ", beginParentez, or), () =>
{ or = "or"; and = "and"; beginParentez = ""; });
query.ConditionAppend(durumlar.BankayaYatan,
string.Format("{0} {1} (kh.TIP='B' and kh.GELIR_GIDER='C') ", beginParentez, or), () =>
{ or = "or"; and = "and"; beginParentez = ""; });
query.ConditionAppend(durumlar.BankadanCekilen,
string.Format("{0} {1} (kh.TIP='B' and kh.GELIR_GIDER='G') ", beginParentez, or), () =>
{ or = "or"; and = "and"; beginParentez = ""; });
query.ConditionAppend(durumlar.CekGelir,
string.Format("{0} {1} (kh.TIP='E' and kh.GELIR_GIDER='G') ", beginParentez, or), () =>
{ or = "or"; and = "and"; beginParentez = ""; });
query.ConditionAppend(durumlar.CekGider,
string.Format("{0} {1} (kh.TIP='E' and kh.GELIR_GIDER='C') ", beginParentez, or), () =>
{ or = "or"; and = "and"; beginParentez = ""; });
query.ConditionAppend(durumlar.SenetGelir,
string.Format("{0} {1} (kh.TIP='S' and kh.GELIR_GIDER='G') ", beginParentez, or), () =>
{ or = "or"; and = "and"; beginParentez = ""; });
query.ConditionAppend(durumlar.SenetGider,
string.Format("{0} {1} (kh.TIP='S' and kh.GELIR_GIDER='C') ", beginParentez, or), () =>
{ or = "or"; and = "and"; beginParentez = ""; });
query.ConditionAppend(durumlar.OzelGelir,
string.Format("{0} {1} (kh.TIP='O' and kh.GELIR_GIDER='G') ", beginParentez, or), () =>
{ or = "or"; and = "and"; beginParentez = ""; });
query.ConditionAppend(durumlar.OzelGider,
string.Format("{0} {1} (kh.TIP='O' and kh.GELIR_GIDER='C') ", beginParentez, or), () =>
{ or = ""; and = "and"; beginParentez = ""; });
if (beginParentez == "")
{
query.Append(")");
}
//query.AppendFormat(" {0} kh.Tarih between '{1}' and '{2}' ", and, durum.BeginDate.ToString("yyyy-MM-dd"), durum.EndDate.ToString("yyyy-MM-dd"));
query.AppendFormat(" {0} {1}='{2}'", and, SqlTypeHelper.GetYear("kh.Tarih"), yil);
query.AppendFormat(" and kh.KASA_KOD='{0}' and kh.SUBE_KODU='{1}'", kasaKodu, subeKodu);
query.AppendFormat(" group by {0}", SqlTypeHelper.GetMonth("kh.Tarih"));
cmd.CommandText = query.ToString();
IDataReader dr = null;
DataTable dt = new DataTable();
try {
dr = cmd.ExecuteReader();
dt.Columns.AddRange(
new DataColumn[]
{
new DataColumn("Ay", typeof(string)),
new DataColumn("Gelir", typeof(double)),
new DataColumn("Gider", typeof(double)),
new DataColumn("Bakiye", typeof(double))
}
);
while (dr.Read())
{
DataRow drow = dt.NewRow();
drow[0] = dr[0].ToStringOrEmpty();
drow[1] = dr[1].ToStringOrEmpty("0");
drow[2] = dr[2].ToStringOrEmpty("0");
drow[3] = dr[3].ToStringOrEmpty("0");
dt.Rows.Add(drow);
}
} catch (Exception exc) {
throw exc;
} finally {
if (dr != null && !dr.IsClosed)
{
dr.Close();
}
}
return(dt);
}
public List <KasaGunlukGelirGider> GetGunlukGelirGider(KasaHareketDurumlari helper, string kasaKodu, string subeKodu)
{
IDbConnection con = Session.Connection;
IDbCommand cmd = con.CreateCommand();
StringBuilder query = new StringBuilder();
query.AppendFormat(@"select kh.Tarih,SUM(case when kh.GELIR_GIDER='G' then
kh.Tutar else 0 end) as GelirToplam
,sum(case when kh.GELIR_GIDER='C' then kh.Tutar else 0 end) as GiderToplam
,sum((case when kh.GELIR_GIDER='G' then kh.Tutar else 0 end)-
(case when kh.GELIR_GIDER='C' then kh.Tutar else 0 end)) as Bakiye
from KasaHareket kh where ");
string or = "";
string and = "";
string beginParentez = "(";
if (helper.MalAlis)
{
query.AppendFormat("{0} (kh.TIP='F' and kh.GELIR_GIDER='C') ", beginParentez);
or = "or";
and = "and";
beginParentez = "";
}
if (helper.MalSatis)
{
query.AppendFormat("{0} {1} (kh.TIP='F' and kh.GELIR_GIDER='G') ", beginParentez, or);
or = "or";
and = "and";
beginParentez = "";
}
if (helper.CaridenTahsilat)
{
query.AppendFormat("{0} {1} (kh.TIP='C' and kh.GELIR_GIDER='G') ", beginParentez, or);
or = "or";
and = "and";
beginParentez = "";
}
if (helper.CariyeOdeme)
{
query.AppendFormat("{0}{1} (kh.TIP='C' and kh.GELIR_GIDER='C') ", beginParentez, or);
or = "or";
and = "and";
beginParentez = "";
}
if (helper.BankayaYatan)
{
query.AppendFormat("{0}{1} (kh.TIP='B' and kh.GELIR_GIDER='C') ", beginParentez, or);
or = "or";
and = "and";
beginParentez = "";
}
if (helper.BankadanCekilen)
{
query.AppendFormat("{0}{1} (kh.TIP='B' and kh.GELIR_GIDER='G') ", beginParentez, or);
or = "or";
and = "and";
beginParentez = "";
}
if (helper.CekGider)
{
query.AppendFormat("{0}{1} (kh.TIP='E' and kh.GELIR_GIDER='C') ", beginParentez, or);
or = "or";
and = "and";
beginParentez = "";
}
if (helper.CekGelir)
{
query.AppendFormat("{0}{1} (kh.TIP='E' and kh.GELIR_GIDER='G') ", beginParentez, or);
or = "or";
and = "and";
beginParentez = "";
}
if (helper.SenetGider)
{
query.AppendFormat("{0}{1} (kh.TIP='S' and kh.GELIR_GIDER='C') ", beginParentez, or);
or = "or";
and = "and";
beginParentez = "";
}
if (helper.SenetGelir)
{
query.AppendFormat("{0}{1} (kh.TIP='S' and kh.GELIR_GIDER='G') ", beginParentez, or);
or = "or";
and = "and";
beginParentez = "";
}
if (helper.OzelGider)
{
query.AppendFormat("{0}{1} (kh.TIP='O' and kh.GELIR_GIDER='C') ", beginParentez, or);
or = "or";
and = "and";
beginParentez = "";
}
if (helper.OzelGelir)
{
query.AppendFormat("{0}{1} (kh.TIP='O' and kh.GELIR_GIDER='G') ", beginParentez, or);
or = "";
and = "and";
beginParentez = "";
}
if (beginParentez == "")
{
query.Append(")");
}
query.AppendFormat(" {0} {1} between '{2}' and '{3}' ", and, SqlTypeHelper.GetDate("kh.Tarih"), helper.BeginDate.JustDate().ToString("yyyy-MM-dd"), helper.EndDate.JustDate().ToString("yyyy-MM-dd"));
query.AppendFormat(" and kh.KASA_KOD='{0}' and kh.SUBE_KODU='{1}'", kasaKodu, subeKodu);
query.Append(" group by kh.Tarih");
cmd.CommandText = query.ToString();
List <KasaGunlukGelirGider> liste;
IDataReader dr = null;
try {
dr = cmd.ExecuteReader();
liste = new List <KasaGunlukGelirGider>();
while (dr.Read())
{
KasaGunlukGelirGider item = new KasaGunlukGelirGider();
item.Tarih = DateTime.Parse(dr["Tarih"].ToString());
item.Gelir = double.Parse(dr["GelirToplam"].ToString());
item.Gider = double.Parse(dr["GiderToplam"].ToString());
item.Bakiye = double.Parse(dr["Bakiye"].ToString());
liste.Add(item);
}
} catch (Exception exc) {
throw exc;
} finally {
if (dr != null && !dr.IsClosed)
{
dr.Close();
}
}
return(liste);
}
