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); } } }