private void Fill()
{
string nProject = "";
if (Project != "SEMUA")
{
nProject = " AND ISC064_MARKETINGJUAL..MS_KONTRAK.Project IN ('" + Project.Replace(",", "','") + "')";
}
string nPerusahaan = "";
if (Perusahaan != "SEMUA")
{
nPerusahaan = " AND ISC064_MARKETINGJUAL..MS_KONTRAK.Pers = '" + Perusahaan + "'";
}
string Status = "";
if (StatusA != "")
{
Status = " AND ISC064_MARKETINGJUAL..MS_KONTRAK.Status = 'A'";
}
if (StatusB != "")
{
Status = " AND ISC064_MARKETINGJUAL..MS_KONTRAK.Status = 'B'";
}
decimal total = 0;
decimal t2 = 0;
decimal t1 = 0;
decimal t3 = 0;
decimal t4 = 0;
decimal t5 = 0;
decimal t6 = 0;
DateTime Tanggal = Db.SingleTime("SELECT FilterDari FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
//DateTime tanggal = Convert.ToDateTime(AsOF);
string nLokasi = "";
if (Lokasi != "SEMUA")
{
nLokasi = " AND ISC064_MARKETINGJUAL..MS_KONTRAK.Lokasi = '" + Cf.Str(Lokasi) + "'";
}
string KPR = "";
if (KPAStatus == "kpa1")
{
KPR = " ";
}
else if (KPAStatus == "kpa2")
{
KPR = " AND a.KPR != '1' ";
}
string aa = "";
if (UserAgent() > 0)
{
aa = " AND " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK.NoAgent = " + UserAgent();
}
string strSql = "SELECT "
+ " ISC064_MARKETINGJUAL..MS_KONTRAK.TglKontrak"
+ ",ISC064_MARKETINGJUAL..MS_KONTRAK.NoKontrak"
+ ",ISC064_MARKETINGJUAL..MS_KONTRAK.NoUnit"
+ ",ISC064_MARKETINGJUAL..MS_KONTRAK.NilaiKontrak"
+ ",ISC064_MARKETINGJUAL..MS_CUSTOMER.Nama AS Cs"
+ ",ISC064_MARKETINGJUAL..MS_AGENT.Nama AS Agent"
+ ",(SELECT ISNULL(SUM(NilaiPelunasan),0) FROM ISC064_MARKETINGJUAL..MS_PELUNASAN a join ISC064_marketingjual..ms_tagihan b on a.NoKontrak = b.NoKontrak WHERE a.NoKontrak = ISC064_MARKETINGJUAL..MS_KONTRAK.NoKontrak AND a.TglPelunasan < '" + Cf.AwalBulan(Tanggal.Month, Tanggal.Year) + "' AND b.Tipe <> 'ADM' and a.NoTagihan = b.NoUrut) AS Lalu"
+ ",(SELECT ISNULL(SUM(NilaiPelunasan),0) FROM ISC064_MARKETINGJUAL..MS_PELUNASAN a join ISC064_marketingjual..ms_tagihan b on a.NoKontrak = b.NoKontrak WHERE a.NoKontrak = ISC064_MARKETINGJUAL..MS_KONTRAK.NoKontrak AND a.TglPelunasan >= '" + Cf.AwalBulan(Tanggal.Month, Tanggal.Year) + "' AND a.TglPelunasan <= '" + Cf.AwalBulan1(Tanggal.Month, Tanggal.Year, Tanggal.Day) + "' AND b.Tipe <> 'ADM' and a.NoTagihan = b.NoUrut) AS Berjalan"
+ " FROM ISC064_MARKETINGJUAL..MS_KONTRAK INNER JOIN ISC064_MARKETINGJUAL..MS_CUSTOMER ON ISC064_MARKETINGJUAL..MS_KONTRAK.NoCustomer = ISC064_MARKETINGJUAL..MS_CUSTOMER.NoCustomer"
+ " INNER JOIN ISC064_MARKETINGJUAL..MS_AGENT ON ISC064_MARKETINGJUAL..MS_KONTRAK.NoAgent = ISC064_MARKETINGJUAL..MS_AGENT.NoAgent"
+ " WHERE 1=1 "
+ nProject
+ nPerusahaan
+ nLokasi
+ Status
+ aa;
DataTable rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
int no = i + 1;
decimal KPALalu = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan),0) FROM "
+ " ISC064_MARKETINGJUAL..MS_PELUNASAN A INNER JOIN "
+ " ISC064_MARKETINGJUAL..MS_TAGIHAN B on A.NoTagihan = B.NoUrut AND A.NoKontrak = B.NoKontrak "
+ " WHERE TglPelunasan < '" + Cf.AwalBulan(Tanggal.Month, Tanggal.Year) + "' "
+ " AND B.KPR = '1' "
+ " AND B.TIPE <> 'ADM' "
+ " AND A.NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' ");
decimal BerjalanLalu = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan),0) FROM "
+ " ISC064_MARKETINGJUAL..MS_PELUNASAN A INNER JOIN "
+ " ISC064_MARKETINGJUAL..MS_TAGIHAN B on A.NoTagihan = B.NoUrut AND A.NoKontrak = B.NoKontrak "
+ " WHERE TglPelunasan >= '" + Cf.AwalBulan(Tanggal.Month, Tanggal.Year) + "' "
+ " AND TglPelunasan <= '" + Cf.AwalBulan1(Tanggal.Month, Tanggal.Year, Tanggal.Day) + "' "
+ " AND B.KPR = '1' "
+ " AND B.TIPE <> 'ADM' "
+ " AND A.NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' ");
decimal NilaiLalu = Convert.ToDecimal(rs.Rows[i]["Lalu"]);
decimal NilaiBerjalan = Convert.ToDecimal(rs.Rows[i]["Berjalan"]);
if (KPAStatus == "kpa2")
{
NilaiLalu -= KPALalu;
NilaiBerjalan -= BerjalanLalu;
}
decimal sekarang = NilaiLalu + NilaiBerjalan;
decimal saldo = (decimal)rs.Rows[i]["NilaiKontrak"] - sekarang;
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
c = new TableCell();
c.Text = Cf.Str(no);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglKontrak"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoKontrak"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Cs"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Agent"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoUnit"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["NilaiKontrak"]);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(NilaiLalu);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(NilaiBerjalan);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(sekarang);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(saldo);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
decimal adm = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiTagihan),0)FROM ISC064_MARKETINGJUAL..MS_TAGIHAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND Tipe = 'ADM' ");
c.Text = Cf.Num(adm.ToString());
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
decimal admterima = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan),0)FROM ISC064_MARKETINGJUAL..MS_PELUNASAN a join ISC064_MARKETINGJUAL..MS_TAGIHAN b ON a.NoKontrak = b.NoKontrak WHERE a.NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND Tipe = 'ADM' AND a.NoTagihan = b.NoUrut AND a.TglPelunasan <= '" + Cf.AwalBulan1(Tanggal.Month, Tanggal.Year, Tanggal.Day) + "'");
c.Text = Cf.Num(admterima.ToString());
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
rpt.Rows.Add(r);
total = total + (decimal)rs.Rows[i]["NilaiKontrak"];
t2 = t2 + (decimal)rs.Rows[i]["NilaiKontrak"];
t1 = t1 + NilaiLalu;
t3 = t3 + NilaiBerjalan;
t4 = t4 + sekarang;
t5 = t5 + adm;
t6 = t6 + admterima;
if (i == rs.Rows.Count - 1)
{
SubTotal("GRAND TOTAL", total, t2, t1, t3, t4, t5, t6);
}
}
}
private void Fill()
{
decimal total = 0;
decimal t2 = 0;
decimal t1 = 0;
decimal t3 = 0;
decimal t4 = 0;
decimal t5 = 0;
DateTime tanggal = Convert.ToDateTime(tgl.Text);
string Lokasi = "";
if (lokasi.SelectedIndex != 0)
{
Lokasi = " AND ISC064_MARKETINGJUAL..MS_KONTRAK.Lokasi = '" + Cf.Str(lokasi.SelectedValue) + "'";
}
string Project = " AND ISC064_MARKETINGJUAL..MS_KONTRAK.Project IN (" + Act.ProjectListSql + ")";
if (project.SelectedValue != "SEMUA")
{
Project = " AND ISC064_MARKETINGJUAL..MS_KONTRAK.Project = '" + project.SelectedValue + "'";
}
string Perusahaan = "";
if (pers.SelectedValue != "SEMUA")
{
Perusahaan = " AND ISC064_MARKETINGJUAL..MS_KONTRAK.Pers = '" + pers.SelectedValue + "'";
}
string strSql = "SELECT "
+ " ISC064_MARKETINGJUAL..MS_KONTRAK.NoKontrak"
+ ",ISC064_MARKETINGJUAL..MS_KONTRAK.NilaiKontrak"
+ ",ISC064_MARKETINGJUAL..MS_KONTRAK.NoUnit"
+ ",ISC064_MARKETINGJUAL..MS_CUSTOMER.Nama AS Cs"
+ ",DATEDIFF(day,convert(datetime,ISC064_MARKETINGJUAL..MS_TAGIHAN.TglJT,112),'" + Cf.Tgl112(tanggal) + "') as Telat"
+ ",ISC064_MARKETINGJUAL..MS_TAGIHAN.NilaiTagihan AS NilaiTagihan"
+ ",ISC064_MARKETINGJUAL..MS_TAGIHAN.Denda AS Denda"
+ ",ISC064_MARKETINGJUAL..MS_TAGIHAN.DendaReal AS DendaReal"
+ ",ISC064_MARKETINGJUAL..MS_TAGIHAN.NilaiPutihDenda AS PutihDenda"
+ ",ISC064_MARKETINGJUAL..MS_TAGIHAN.AlokasiBenefit AS AlokasiBenefit"
+ ",ISC064_MARKETINGJUAL..MS_TAGIHAN.Benefit AS Benefit"
+ ",ISC064_MARKETINGJUAL..MS_TAGIHAN.BenefitReal AS BenefitReal"
+ " FROM ISC064_MARKETINGJUAL..MS_KONTRAK INNER JOIN ISC064_MARKETINGJUAL..MS_CUSTOMER ON ISC064_MARKETINGJUAL..MS_KONTRAK.NoCustomer = ISC064_MARKETINGJUAL..MS_CUSTOMER.NoCustomer"
+ " INNER JOIN ISC064_MARKETINGJUAL..MS_TAGIHAN ON ISC064_MARKETINGJUAL..MS_KONTRAK.NoKontrak = ISC064_MARKETINGJUAL..MS_TAGIHAN.NoKontrak"
+ " WHERE 1=1 "
+ " AND ISC064_MARKETINGJUAL..MS_TAGIHAN.TglJT <= '" + Cf.AwalBulan1(tanggal.Month, tanggal.Year, tanggal.Day) + "'"
+ " AND (ISC064_MARKETINGJUAL..MS_TAGIHAN.Denda > 0 OR ISC064_MARKETINGJUAL..MS_TAGIHAN.Benefit > 0)"
+ Project
+ Perusahaan
+ Lokasi
+ " ORDER BY ISC064_MARKETINGJUAL..MS_CUSTOMER.Nama ASC";
DataTable rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
int no = i + 1;
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
c = new TableCell();
c.Text = Cf.Str(no);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoKontrak"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Cs"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoUnit"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["NilaiKontrak"]);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["NilaiTagihan"]);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Telat"].ToString();
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round(Convert.ToDecimal(rs.Rows[i]["Denda"])));
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round(Convert.ToDecimal(rs.Rows[i]["Benefit"])));
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round(Convert.ToDecimal(rs.Rows[i]["BenefitReal"])));
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round(Convert.ToDecimal(rs.Rows[i]["DendaReal"])));
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["PutihDenda"]);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
decimal SaldoDenda = Math.Round(Convert.ToDecimal(rs.Rows[i]["Denda"]) - Convert.ToDecimal(rs.Rows[i]["DendaReal"]) - Convert.ToDecimal(rs.Rows[i]["PutihDenda"]) - Convert.ToDecimal(rs.Rows[i]["AlokasiBenefit"]));
c.Text = Cf.Num(SaldoDenda);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
rpt.Rows.Add(r);
total = total + (decimal)rs.Rows[i]["Denda"];
t2 = t2 + (decimal)rs.Rows[i]["DendaReal"];
t1 = t1 + (decimal)rs.Rows[i]["PutihDenda"];
t3 = t3 + (decimal)rs.Rows[i]["Benefit"];
t4 = t4 + (decimal)rs.Rows[i]["BenefitReal"];
t5 = t5 + SaldoDenda;
if (i == rs.Rows.Count - 1)
{
SubTotal("GRAND TOTAL", total, t2, t1, t3, t4, t5);
}
}
}
