public void RefreshToplam()
{
IEnumerable <FF> ffs = FF.View(PPId, HHId, TTId, BasTrhX, BitTrhX, TrhTur);
decimal GlrTop = 0, GdrTop = 0;
decimal BklGlrTop = 0, BklGdrTop = 0;
int cnt = 0;
foreach (var ff in ffs)
{
GlrTop += ff.Glr;
GdrTop += ff.Gdr;
BklGlrTop += ff.BklGlr;
BklGdrTop += ff.BklGdr;
cnt++;
}
NORX = $"Kayıt Sayısı: {cnt:n0}";
GlrTopX = $"{GlrTop:#,#.##;-#,#.##;#}";
GdrTopX = $"{GdrTop:#,#.##;-#,#.##;#}";
BklGlrTopX = $"{BklGlrTop:#,#.##;-#,#.##;#}";
BklGdrTopX = $"{BklGdrTop:#,#.##;-#,#.##;#}";
}
public static Response FFsXlsx(long PPId, long HHId, long TTId, string BasTrhX, string BitTrhX)
{
using (ExcelPackage pck = new ExcelPackage())
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("FFsRpr");
// Header (first row)
ws.Cells[1, 1].Value = "Tarih";
ws.Cells["A1:A2"].Merge = true;
ws.Cells[1, 2].Value = "Hesap";
ws.Cells["B1:B2"].Merge = true;
ws.Cells[1, 3].Value = "Etiket";
ws.Cells["C1:C2"].Merge = true;
ws.Cells[1, 8].Value = "Not";
ws.Cells["H1:H2"].Merge = true;
ws.Cells[1, 4].Value = "Gercek";
ws.Cells["D1:E1"].Merge = true;
ws.Cells[1, 6].Value = "Beklenen";
ws.Cells["F1:G1"].Merge = true;
ws.Cells[2, 4].Value = "Gelir";
ws.Cells[2, 5].Value = "Gider";
ws.Cells[2, 6].Value = "Gelir";
ws.Cells[2, 7].Value = "Gider";
ws.Row(1).Style.Font.Bold = true;
ws.Column(1).Style.Numberformat.Format = "dd.mm.yy";
ws.Column(4).Style.Numberformat.Format = "#,###";
ws.Column(5).Style.Numberformat.Format = "#,###";
ws.Column(6).Style.Numberformat.Format = "#,###";
ws.Column(7).Style.Numberformat.Format = "#,###";
if (Db.FromId((ulong)PPId) is PP pp)
{
string Hdr = $"{pp.CC.Ad}►{pp.Ad}";
if (!string.IsNullOrEmpty(BasTrhX))
{
DateTime basTrh = Convert.ToDateTime(BasTrhX);
DateTime bitTrh = Convert.ToDateTime(BitTrhX);
if (basTrh == bitTrh)
{
Hdr = $"{pp.CC.Ad}►{pp.Ad}►{basTrh:dd.MM.yy}";
}
else
{
Hdr = $"{pp.CC.Ad}►{pp.Ad}►{basTrh:dd.MM.yy} >=< {bitTrh:dd.MM.yy}";
}
}
if (Db.FromId((ulong)HHId) is HH hh)
{
Hdr = $"{Hdr}►{HH.FullParentAd(hh)}";
}
if (Db.FromId((ulong)TTId) is TT tt)
{
Hdr = $"{Hdr}►{tt.Ad}";
}
ws.HeaderFooter.OddHeader.CenteredText = Hdr;
int cr = 3;
IEnumerable <FF> ffs = FF.View(PPId, HHId, TTId, BasTrhX, BitTrhX);
foreach (var ff in ffs.OrderByDescending((x) => x.Trh))
{
ws.Cells[cr, 1].Value = ff.Trh;
ws.Cells[cr, 2].Value = ff.HHAdFull;
ws.Cells[cr, 3].Value = ff.TTAd;
ws.Cells[cr, 4].Value = ff.Glr;
ws.Cells[cr, 5].Value = ff.Gdr;
ws.Cells[cr, 6].Value = ff.BklGlr;
ws.Cells[cr, 7].Value = ff.BklGdr;
ws.Cells[cr, 8].Value = ff.Ad;
cr++;
}
using (var range = ws.Cells["A2:H2"]) {
range.AutoFilter = true;
//range.Style.Fill.PatternType = ExcelFillStyle.Solid;
//range.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
}
ws.Row(1).Height = 20;
ws.Row(1).Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
ws.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Column(1).Width = 12;
ws.Column(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Column(2).AutoFit();
ws.Column(3).AutoFit();
ws.Column(4).Width = 12;
ws.Column(5).Width = 12;
ws.Column(6).Width = 12;
ws.Column(7).Width = 12;
ws.Column(8).AutoFit();
ws.Cells[cr, 4].Formula = $"SUM(D2:D{cr-1})";
ws.Cells[cr, 5].Formula = $"SUM(E2:E{cr-1})";
ws.Cells[cr, 6].Formula = $"SUM(F2:F{cr-1})";
ws.Cells[cr, 7].Formula = $"SUM(G2:G{cr-1})";
using (var range = ws.Cells[$"D{cr}:G{cr}"])
{
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
}
}
Response r = new Response();
//r.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
r.ContentType = "application/octet-stream";
r.Headers["Content-Disposition"] = "attachment; filename=\"FFsRpr.xlsx\"";
var oms = new MemoryStream();
pck.SaveAs(oms);
oms.Seek(0, SeekOrigin.Begin);
r.StreamedBody = oms;
return(r);
}
}
protected override void OnData()
{
base.OnData();
var r = this.Root as MasterPage;
CUId = r.CUId;
//DlgFlt.BasTrhX = r.BasTrhX;
//DlgFlt.BitTrhX = r.BitTrhX;
DateTime basTrh = new DateTime(), bitTrh = new DateTime();
if (Db.FromId((ulong)PPId) is PP pp)
{
Hdr = $"{pp.CC.Ad}►{pp.Ad}";
if (Org == "PP") // Proje
{
TrhTur = "R";
basTrh = DateTime.Today;
bitTrh = basTrh.AddDays(1);
}
else if (Org == "HH") // Hesap
{
TrhTur = "R";
basTrh = DateTime.Today;
bitTrh = basTrh.AddDays(1);
}
else if (Org == "TT") // Tag
{
TrhTur = "R";
basTrh = DateTime.MinValue;
bitTrh = DateTime.MaxValue;
}
else if (Org == "FS") // FF Search/Filter
{
//TrhTur = "?";
basTrh = string.IsNullOrEmpty(BasTrhX) ? DateTime.MinValue : Convert.ToDateTime(BasTrhX);
bitTrh = string.IsNullOrEmpty(BitTrhX) ? DateTime.MaxValue : Convert.ToDateTime(BitTrhX).AddDays(1);
}
else if (Org == "FT") // FF Tarih
{
TrhTur = "R";
DateTime d = Convert.ToDateTime(BasTrhX);
basTrh = d.Date;
bitTrh = basTrh.AddDays(1);
}
else if (Org == "FH") // FF Hesap
{
TrhTur = "R";
//basTrh = DateTime.Today;
//bitTrh = basTrh.AddDays(1);
basTrh = DateTime.MinValue;
bitTrh = DateTime.MaxValue;
}
else
{
TrhTur = "R";
basTrh = Convert.ToDateTime(BasTrhX);
bitTrh = Convert.ToDateTime(BitTrhX);
}
BasTrhX = $"{basTrh:yyyy-MM-dd}";
BitTrhX = $"{bitTrh:yyyy-MM-dd}";
string TrhTurX = "İşlem";
switch (TrhTur)
{
case "I":
TrhTurX = "Giriş";
break;
case "U":
TrhTurX = "Edit";
break;
}
if (basTrh.AddDays(1) == bitTrh)
{
Hdr = $"{pp.CC.Ad}►{pp.Ad}►{TrhTurX}►{basTrh:dd.MM.yy}";
}
else if (basTrh == DateTime.MinValue && bitTrh == DateTime.MaxValue)
{
Hdr = $"{pp.CC.Ad}►{pp.Ad}►{TrhTurX}";
}
else
{
Hdr = $"{pp.CC.Ad}►{pp.Ad}►{TrhTurX}►{basTrh:dd.MM.yy} >=< {bitTrh:dd.MM.yy}";
}
}
if (Db.FromId((ulong)HHId) is HH hh)
{
Hdr = $"{Hdr}►{hh.AdFull}";
}
//Hdr = $"{Hdr}►{HH.FullParentAd(hh)}";
if (Db.FromId((ulong)TTId) is TT tt)
{
Hdr = $"{Hdr}►{tt.Ad}";
}
IEnumerable <FF> ffs = FF.View(PPId, HHId, TTId, BasTrhX, BitTrhX, TrhTur);
//IEnumerable<FF> ffs = FF.View(PPId, HHId, TTId, basTrh, bitTrh, TrhTur);
FFs.Data = ffs; //.OrderByDescending((x) => x.Trh);
NORX = $"{FFs.Count:n0} Kayıt";
decimal GlrTop = 0, GdrTop = 0;
decimal BklGlrTop = 0, BklGdrTop = 0;
foreach (var ff in ffs)
{
GlrTop += ff.Glr;
GdrTop += ff.Gdr;
BklGlrTop += ff.BklGlr;
BklGdrTop += ff.BklGdr;
}
GlrTopX = $"{GlrTop:#,#.##;-#,#.##;#}";
GdrTopX = $"{GdrTop:#,#.##;-#,#.##;#}";
BklGlrTopX = $"{BklGlrTop:#,#.##;-#,#.##;#}";
BklGdrTopX = $"{BklGdrTop:#,#.##;-#,#.##;#}";
if (Db.FromId((ulong)PPId) is PP pp2)
{
HHs.Data = Db.SQL <HH>("select r from HH r where r.PP = ? and r.Skl = ? order by r.AdFull", pp2, 99); // Sadece Leafs
HFs.Data = Db.SQL <HH>("select r from HH r where r.PP = ? order by r.AdFull", pp2); // Hepsi
TTs.Data = Db.SQL <TT>("select r from TT r where r.PP = ? order by r.Ad", pp2);
}
}
