public static Dictionary <string, dynamic> LaporanLabaRugi(bool _excel, PenggunaLogin pengguna, string tipeLaporan, string _tgl1, string _tgl2)
{
Dictionary <string, dynamic> Result = new Dictionary <string, dynamic>();
using (DataClassesDatabaseDataContext db = new DataClassesDatabaseDataContext())
{
DateTime Tanggal1, Tanggal2;
Tanggal1 = DateTime.Parse(_tgl1).Date;
Tanggal2 = DateTime.Parse(_tgl2).Date;
var Pemasukan = db.TBAkuns
.Where(item => item.TBAkunGrup.IDAkunGrupParent == 4 && item.IDTempat == pengguna.IDTempat)
.Select(item => new
{
item.IDAkun,
item.Nama,
Saldo = HitungSaldo(item.TBJurnalDetails
.Where(item2 =>
item2.TBJurnal.Tanggal.Value.Date >= DateTime.Parse(_tgl1) &&
item2.TBJurnal.Tanggal.Value.Date <= DateTime.Parse(_tgl2) &&
item2.TBJurnal.IDTempat == pengguna.IDTempat).ToArray(), true)
}).ToArray();
var Pengeluaran = db.TBAkuns
.Where(item => item.TBAkunGrup.IDAkunGrupParent == 5 && item.IDTempat == pengguna.IDTempat)
.Select(item => new
{
TBAkunGrup = item.TBAkunGrup,
item.Nama,
item.IDAkun,
Saldo = HitungSaldo(item.TBJurnalDetails
.Where(item2 =>
item2.TBJurnal.Tanggal.Value.Date >= DateTime.Parse(_tgl1) &&
item2.TBJurnal.Tanggal.Value.Date <= DateTime.Parse(_tgl2) &&
item2.TBJurnal.IDTempat == pengguna.IDTempat).ToArray(), true)
}).ToArray();
var OPEX = Pengeluaran.Where(item => item.TBAkunGrup.Nama != "Taxation" && item.IDAkun != 404 && item.Nama != "Beban Bunga");
var PengeluaranTaxInterest = Pengeluaran.Where(item => item.TBAkunGrup.Nama == "Taxation" || item.Nama == "Beban Bunga");
decimal TotalPemasukan = Pemasukan.Sum(item => item.Saldo);
decimal TotalPengeluaran = Pengeluaran.Sum(item => item.Saldo);
decimal TotalLabaRugi = TotalPemasukan - TotalPengeluaran;
#region MOD TEST
string NamaAkunPenjualan = Pemasukan.FirstOrDefault(item => item.Nama.ToUpper() == "PENJUALAN").Nama;
decimal NominalAkunPenjualan = Pemasukan.FirstOrDefault(item => item.Nama.ToUpper() == "PENJUALAN").Saldo;
string NamaAkunCOGS = Pengeluaran.FirstOrDefault(item => item.Nama == "Harga Pokok Penjualan").Nama;
decimal NominalCOGS = Pengeluaran.FirstOrDefault(item => item.Nama == "Harga Pokok Penjualan").Saldo;
decimal NominalGrossProfit = NominalAkunPenjualan - NominalCOGS;
decimal NominalOPEX = OPEX.Sum(item2 => item2.Saldo);
decimal NominalEBIT = NominalGrossProfit - NominalOPEX + Pemasukan.Where(item => item.IDAkun != 388).Sum(item2 => item2.Saldo);
decimal NominalNetIncome = NominalEBIT - PengeluaranTaxInterest.Sum(item => item.Saldo);
Result.Add("NamaAkunPenjualan", NamaAkunPenjualan);
Result.Add("NominalAkunPenjualan", NominalAkunPenjualan);
Result.Add("NamaAkunCOGS", NamaAkunCOGS);
Result.Add("NominalOPEX", NominalOPEX);
Result.Add("NominalCOGS", NominalCOGS);
Result.Add("NominalGrossProfit", NominalGrossProfit);
Result.Add("NominalEBIT", NominalEBIT);
Result.Add("NominalNetIncome", NominalNetIncome);
#endregion
Result.Add("Pemasukan", Pemasukan.Where(item => item.IDAkun != 388));
Result.Add("Pengeluaran", OPEX);
Result.Add("PengeluaranTax", PengeluaranTaxInterest);
Result.Add("TotalPemasukan", TotalPemasukan);
Result.Add("TotalPengeluaran", TotalPengeluaran);
Result.Add("TotalLabaRugi", TotalLabaRugi);
if (_excel)
{
string periode = Tanggal1 + " - " + Tanggal2;
if (tipeLaporan == "LabaRugi")
{
#region EXCEL LABA RUGI
Excel_Class Excel_Class = new Excel_Class(pengguna, "Laba Rugi", periode, 5);
ExcelWorksheet Worksheet = Excel_Class.Worksheet;
using (var range = Worksheet.Cells[1, 1, 2, 2])
{
range.Style.Font.Bold = true;
range.Merge = true;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
Excel_Class.Content(1, 1, "Pemasukan");
using (var range = Worksheet.Cells[1, 3, 2, 4])
{
range.Style.Font.Bold = true;
range.Merge = true;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
Excel_Class.Content(1, 3, "Pengeluaran");
Worksheet.Cells[3, 1].Value = "Akun";
Worksheet.Cells[3, 2].Value = "Saldo";
Worksheet.Cells[3, 3].Value = "Akun";
Worksheet.Cells[3, 4].Value = "Saldo";
int index = 4;
foreach (var item in Pemasukan)
{
Excel_Class.Content(index, 1, item.Nama);
Excel_Class.Content(index, 2, item.Saldo);
index++;
}
index = 4;
foreach (var item in Pengeluaran)
{
Excel_Class.Content(index, 3, item.Nama);
Excel_Class.Content(index, 4, item.Saldo);
index++;
}
Excel_Class.Save();
linkDownload = Excel_Class.LinkDownload;
#endregion
}
}
}
return(Result);
}
public static Dictionary <string, dynamic> LaporanBukuBesar(string periode1, string periode2, int _IDakun, bool _excel, PenggunaLogin pengguna)
{
Dictionary <string, dynamic> Result = new Dictionary <string, dynamic>();
using (DataClassesDatabaseDataContext db = new DataClassesDatabaseDataContext())
{
var BukuBesar = db.TBJurnalDetails.OrderBy(item => item.TBJurnal.Tanggal).ToArray()
.Where(item =>
item.TBJurnal.Tanggal.Value.Date >= periode1.ToDateTime() &&
item.TBJurnal.Tanggal.Value.Date <= periode2.ToDateTime() &&
item.IDAkun == _IDakun &&
item.TBJurnal.IDTempat == pengguna.IDTempat)
.Select(item => new
{
item.IDJurnal,
item.TBJurnal.Tanggal,
item.TBJurnal.Referensi,
item.TBJurnal.Keterangan,
Debit = item.Debit == 0 ? "-" : item.Debit.ToFormatHarga(),
Kredit = item.Kredit == 0 ? "-" : item.Kredit.ToFormatHarga(),
item.TBAkun.TBAkunGrup.EnumSaldoNormal,
Saldo = (HitungSaldoBukuBesar((decimal)item.Debit, (decimal)item.Kredit) < 0) && item.TBAkun.TBAkunGrup.EnumSaldoNormal == (int)PilihanDebitKredit.Kredit ? Math.Abs(Saldo) : Saldo
})
.OrderBy(item => item.Tanggal)
.ThenBy(item => item.IDJurnal);
if (_excel)
{
#region EXCEL BUKU BESAR
Excel_Class Excel_Class = new Excel_Class(pengguna, "Buku Besar", periode1 + " - " + periode2 + " - ", 6);
ExcelWorksheet Worksheet = Excel_Class.Worksheet;
Worksheet.Cells[1, 1].Value = "Tanggal";
Worksheet.Cells[1, 2].Value = "No. Referensi";
Worksheet.Cells[1, 3].Value = "Keterangan";
Worksheet.Cells[1, 4].Value = "Debit";
Worksheet.Cells[1, 5].Value = "Kredit";
Worksheet.Cells[1, 6].Value = "Balance";
int index = 2;
foreach (var item in BukuBesar)
{
Excel_Class.Content(index, 1, item.Tanggal.Value);
Excel_Class.Content(index, 2, item.Referensi);
Excel_Class.Content(index, 3, item.Keterangan);
Excel_Class.Content(index, 4, item.Debit);
Excel_Class.Content(index, 5, item.Kredit);
Excel_Class.Content(index, 6, item.Saldo);
index++;
}
Excel_Class.Save();
linkDownload = Excel_Class.LinkDownload;
#endregion
}
}
return(Result);
}
public static Dictionary <string, dynamic> LaporanLabaRugi(string bulan, string tahun, bool _excel, PenggunaLogin pengguna, string tipeLaporan)
{
Dictionary <string, dynamic> Result = new Dictionary <string, dynamic>();
using (DataClassesDatabaseDataContext db = new DataClassesDatabaseDataContext())
{
var Pemasukan = db.TBAkuns
.Where(item => item.TBAkunGrup.IDAkunGrupParent == 4)
.Select(item => new
{
item.IDAkun,
item.Nama,
Saldo = HitungSaldo(item.TBJurnalDetails
.Where(item2 =>
item2.TBJurnal.Tanggal.Value.Year == tahun.ToInt() &&
item2.TBJurnal.Tanggal.Value.Month == bulan.ToInt() &&
item2.TBJurnal.IDTempat == pengguna.IDTempat).ToArray(), true)
}).ToArray();
var Pengeluaran = db.TBAkuns
.Where(item => item.TBAkunGrup.IDAkunGrupParent == 5)
.Select(item => new
{
TBAkunGrup = item.TBAkunGrup,
item.Nama,
item.IDAkun,
Saldo = HitungSaldo(item.TBJurnalDetails
.Where(item2 =>
item2.TBJurnal.Tanggal.Value.Year == tahun.ToInt() &&
item2.TBJurnal.Tanggal.Value.Month == bulan.ToInt() &&
item2.TBJurnal.IDTempat == pengguna.IDTempat).ToArray(), true)
}).ToArray();
var OPEX = Pengeluaran.Where(item => item.TBAkunGrup.Nama != "Taxation" && item.IDAkun != 404 && item.Nama != "Beban Bunga");
var PengeluaranTaxInterest = Pengeluaran.Where(item => item.TBAkunGrup.Nama == "Taxation" || item.Nama == "Beban Bunga");
decimal TotalPemasukan = Pemasukan.Sum(item => item.Saldo);
decimal TotalPengeluaran = Pengeluaran.Sum(item => item.Saldo);
decimal TotalLabaRugi = TotalPemasukan - TotalPengeluaran;
#region MOD TEST
string NamaAkunPenjualan = Pemasukan.FirstOrDefault(item => item.Nama.ToUpper() == "PENJUALAN").Nama;
decimal NominalAkunPenjualan = Pemasukan.FirstOrDefault(item => item.Nama.ToUpper() == "PENJUALAN").Saldo;
string NamaAkunCOGS = Pengeluaran.FirstOrDefault(item => item.Nama == "Harga Pokok Penjualan").Nama;
decimal NominalCOGS = Pengeluaran.FirstOrDefault(item => item.Nama == "Harga Pokok Penjualan").Saldo;
decimal NominalGrossProfit = NominalAkunPenjualan - NominalCOGS;
decimal NominalOPEX = OPEX.Sum(item2 => item2.Saldo);
decimal NominalEBIT = NominalGrossProfit - NominalOPEX + Pemasukan.Where(item => item.IDAkun != 388).Sum(item2 => item2.Saldo);
decimal NominalNetIncome = NominalEBIT - PengeluaranTaxInterest.Sum(item => item.Saldo);
Result.Add("NamaAkunPenjualan", NamaAkunPenjualan);
Result.Add("NominalAkunPenjualan", NominalAkunPenjualan);
Result.Add("NamaAkunCOGS", NamaAkunCOGS);
Result.Add("NominalOPEX", NominalOPEX);
Result.Add("NominalCOGS", NominalCOGS);
Result.Add("NominalGrossProfit", NominalGrossProfit);
Result.Add("NominalEBIT", NominalEBIT);
Result.Add("NominalNetIncome", NominalNetIncome);
#endregion
Result.Add("Pemasukan", Pemasukan.Where(item => item.IDAkun != 388));
Result.Add("Pengeluaran", OPEX);
Result.Add("PengeluaranTax", PengeluaranTaxInterest);
Result.Add("TotalPemasukan", TotalPemasukan);
Result.Add("TotalPengeluaran", TotalPengeluaran);
Result.Add("TotalLabaRugi", TotalLabaRugi);
if (_excel)
{
string _bulan = DateTime.Parse(int.Parse(bulan) + "/" + "01" + "/" + "2016").ToString("MMMM", new CultureInfo("id-ID"));
if (tipeLaporan == "LabaRugi")
{
#region EXCEL LABA RUGI
Excel_Class Excel_Class = new Excel_Class(pengguna, "Laba Rugi", _bulan + " - " + tahun, 5);
ExcelWorksheet Worksheet = Excel_Class.Worksheet;
using (var range = Worksheet.Cells[1, 1, 2, 2])
{
range.Style.Font.Bold = true;
range.Merge = true;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
Excel_Class.Content(1, 1, "Pemasukan");
using (var range = Worksheet.Cells[1, 3, 2, 4])
{
range.Style.Font.Bold = true;
range.Merge = true;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
Excel_Class.Content(1, 3, "Pengeluaran");
Worksheet.Cells[3, 1].Value = "Akun";
Worksheet.Cells[3, 2].Value = "Saldo";
Worksheet.Cells[3, 3].Value = "Akun";
Worksheet.Cells[3, 4].Value = "Saldo";
int index = 4;
foreach (var item in Pemasukan)
{
Excel_Class.Content(index, 1, item.Nama);
Excel_Class.Content(index, 2, item.Saldo);
index++;
}
index = 4;
foreach (var item in Pengeluaran)
{
Excel_Class.Content(index, 3, item.Nama);
Excel_Class.Content(index, 4, item.Saldo);
index++;
}
Excel_Class.Save();
linkDownload = Excel_Class.LinkDownload;
#endregion
}
else
{
var AktivaPasiva = db.TBAkuns.Where(item =>
item.TBAkunGrup.IDAkunGrupParent != 4 &&
item.TBAkunGrup.IDAkunGrupParent != 5)
.ToArray()
.Select(item => new
{
item.TBAkunGrup.EnumJenisAkunGrup,
item.TBAkunGrup.EnumSaldoNormal,
Grup = item.TBAkunGrup.Nama,
item.Nama,
Saldo = Akuntansi_Class.HitungSaldo(item.TBJurnalDetails
.Where(item2 =>
item2.TBJurnal.Tanggal.Value.Month == bulan.ToInt() &&
item2.TBJurnal.Tanggal.Value.Year == tahun.ToInt() &&
item2.TBJurnal.IDTempat == pengguna.IDTempat).ToArray(), false)
})
.Select(item => new
{
item.EnumJenisAkunGrup,
item.EnumSaldoNormal,
item.Grup,
item.Nama,
Saldo = (item.Saldo < 0) && item.EnumSaldoNormal == (int)PilihanDebitKredit.Kredit ? Math.Abs(item.Saldo) : item.Saldo
});
var _aktiva = AktivaPasiva.Where(item => item.EnumJenisAkunGrup == (int)PilihanJenisAkunGrup.Aktiva);
var _pasiva = AktivaPasiva.Where(item => item.EnumJenisAkunGrup == (int)PilihanJenisAkunGrup.Pasiva);
#region EXCEL NERACA
Excel_Class Excel_Class = new Excel_Class(pengguna, "Laba Rugi", _bulan + " - " + tahun, 5);
ExcelWorksheet Worksheet = Excel_Class.Worksheet;
using (var range = Worksheet.Cells[1, 1, 2, 3])
{
range.Style.Font.Bold = true;
range.Merge = true;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
Excel_Class.Content(1, 1, "Aktiva");
using (var range = Worksheet.Cells[1, 4, 2, 6])
{
range.Style.Font.Bold = true;
range.Merge = true;
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
Excel_Class.Content(1, 4, "Pasiva");
Worksheet.Cells[3, 1].Value = "Tipe";
Worksheet.Cells[3, 2].Value = "Akun";
Worksheet.Cells[3, 3].Value = "Saldo";
Worksheet.Cells[3, 4].Value = "Tipe";
Worksheet.Cells[3, 5].Value = "Akun";
Worksheet.Cells[3, 6].Value = "Saldo";
int index = 4;
foreach (var item in _aktiva)
{
Excel_Class.Content(index, 1, item.Grup);
Excel_Class.Content(index, 2, item.Nama);
Excel_Class.Content(index, 3, item.Saldo);
index++;
}
index = 4;
foreach (var item in _pasiva)
{
Excel_Class.Content(index, 4, item.Grup);
Excel_Class.Content(index, 5, item.Nama);
Excel_Class.Content(index, 6, item.Saldo);
index++;
}
Excel_Class.Content(index + 1, 4, "Laba/Rugi Bulan Berjalan");
Excel_Class.Content(index + 1, 5, "");
Excel_Class.Content(index + 1, 6, TotalLabaRugi);
Excel_Class.Save();
linkDownload = Excel_Class.LinkDownload;
#endregion
}
}
}
return(Result);
}
protected void ButtonExcel_Click(object sender, EventArgs e)
{
using (DataClassesDatabaseDataContext db = new DataClassesDatabaseDataContext())
{
PenggunaLogin pengguna = (PenggunaLogin)Session["PenggunaLogin"];
Excel_Class Excel_Class = new Excel_Class(pengguna, "Neraca", DropDownListBulan.SelectedItem.Text + " " + DropDownListTahun.SelectedItem.Text, 4);
var result = new Excel_Class(pengguna, "Neraca", DropDownListBulan.SelectedItem.Text + " " + DropDownListTahun.SelectedItem.Text, 4);
ExcelWorksheet Worksheet = Excel_Class.Worksheet;
Excel_Class.Header(1, "No");
Excel_Class.Header(2, "Kode");
Excel_Class.Header(3, "Nama");
Excel_Class.Header(4, "Nominal");
Excel_Class.SetBackground(1, 1, Color.Black);
Excel_Class.SetBackground(1, 2, Color.Black);
Excel_Class.SetBackground(1, 3, Color.Black);
Excel_Class.SetBackground(1, 4, Color.Black);
Excel_Class.SetColor(1, 1, Color.White);
Excel_Class.SetColor(1, 2, Color.White);
Excel_Class.SetColor(1, 3, Color.White);
Excel_Class.SetColor(1, 4, Color.White);
int index = 2;
foreach (var item in LoadNeraca(db))
{
if (item.TBAkunGrup == null)
{
Excel_Class.SetBackground(index, 1, Color.LightGreen);
Excel_Class.SetBackground(index, 2, Color.LightGreen);
Excel_Class.SetBackground(index, 3, Color.LightGreen);
Excel_Class.SetBackground(index, 4, Color.LightGreen);
Excel_Class.SetColor(index, 1, Color.Black);
Excel_Class.SetColor(index, 2, Color.Black);
Excel_Class.SetColor(index, 3, Color.Black);
Excel_Class.SetColor(index, 4, Color.Black);
}
else
{
if (item.Grup == true)
{
Excel_Class.SetBackground(index, 1, Color.LightYellow);
Excel_Class.SetBackground(index, 2, Color.LightYellow);
Excel_Class.SetBackground(index, 3, Color.LightYellow);
Excel_Class.SetBackground(index, 4, Color.LightYellow);
Excel_Class.SetColor(index, 1, Color.Black);
Excel_Class.SetColor(index, 2, Color.Black);
Excel_Class.SetColor(index, 3, Color.Black);
Excel_Class.SetColor(index, 4, Color.Black);
}
}
Excel_Class.Content(index, 1, item.Nomor.Replace(" ", ""));
Excel_Class.Content(index, 2, item.Grup == true ? string.Empty : item.Kode);
Excel_Class.Content(index, 3, item.Nama);
Excel_Class.Content(index, 4, item.Grup == true ? string.Empty : item.Nominal.ToString());
index++;
}
List <ListAkun> listAkun = new List <ListAkun>();
var result2 = db.TBAkunGrups.Where(item => item.IDAkunGrupParent == null &&
(item.IDAkunGrup != 4 && item.IDAkunGrup != 5)).ToArray();
//CARI AKUN GRUP
CariAkunGrup("1", result2, listAkun);
var dataBulanIni = Akuntansi_Class.LaporanLabaRugi(DropDownListBulan.SelectedItem.Value, DropDownListTahun.SelectedItem.Value, false, pengguna, "");
var dataBulanSebelumnya = Akuntansi_Class.LaporanLabaRugi(((DropDownListBulan.SelectedItem.Value).ToInt() - 1).ToString(), DropDownListTahun.SelectedItem.Value, false, pengguna, "");
decimal LabaRugiBerjalan = dataBulanIni["TotalLabaRugi"];
decimal LabaRugiBulanSebelumnya = dataBulanSebelumnya["TotalLabaRugi"];
Excel_Class.Content(index, 1, "Laba Rugi Bulan Berjalan");
Excel_Class.Content(index, 2, "");
Excel_Class.Content(index, 3, "");
Excel_Class.Content(index, 4, LabaRugiBerjalan);
Excel_Class.Content(index + 1, 1, "Laba Rugi Bulan Sebelumnya");
Excel_Class.Content(index + 1, 2, "");
Excel_Class.Content(index + 1, 3, "");
Excel_Class.Content(index + 1, 4, LabaRugiBulanSebelumnya);
Excel_Class.Save();
LinkDownload.Visible = true;
ButtonExcel.Visible = false;
if (LinkDownload.Visible)
{
LinkDownload.HRef = Excel_Class.LinkDownload;
}
}
}
