private void Fill() { string Status = ""; if (statusA.Checked) { Status = " AND a.Status = 'A'"; } if (statusB.Checked) { Status = " AND a.Status = 'B'"; } string tgl = ""; string order = ""; if (tglkontrak.Checked) { tgl = "TglKontrak"; order = "NoKontrak"; } DateTime Dari = Convert.ToDateTime(dari.Text); DateTime Sampai = Convert.ToDateTime(sampai.Text); if (Dari > Sampai) { DateTime x = Sampai; Sampai = Dari; Dari = x; } string Lokasi = ""; if (lokasi.SelectedIndex != 0) { Lokasi = " AND Lokasi = '" + Cf.Str(lokasi.SelectedValue) + "'"; } string Agent = ""; if (agent.SelectedIndex != 0) { Agent = " AND Principal = '" + Cf.Str(agent.SelectedValue) + "'"; } decimal t1 = 0, t2 = 0; string strSql = "SELECT a.*" + ", b.Nama AS Cs" + ", b.NoKTP" + ", b.NoTelp" + ", b.NoHP" + ", c.Nama AS Ag" + ", c.Principal" + ", (SELECT ISNULL(SUM(NilaiPelunasan), 0) FROM MS_PELUNASAN WHERE NoKontrak = a.NoKontrak) AS TotalBFR" + " FROM MS_KONTRAK a" + " INNER JOIN MS_CUSTOMER b ON a.NoCustomer = b.NoCustomer" + " INNER JOIN MS_AGENT c ON a.NoAgent = c.NoAgent" + " WHERE a.NoKontrak NOT IN" + " (" + " SELECT NoKontrak" + " FROM" + " (" + " SELECT NoKontrak, SUM(NilaiPelunasan) AS Total" + " FROM MS_PELUNASAN" + " GROUP BY NoKontrak" + " )" + " AS table1 WHERE Total >= 10000000" + " )" + " AND CONVERT(varchar, " + tgl + " ,112) >= '" + Cf.Tgl112(Dari) + "'" + " AND CONVERT(varchar, " + tgl + ", 112) <= '" + Cf.Tgl112(Sampai) + "'" + " AND Jenis IN (" + Rpt.inSql(jenis) + ")" + Lokasi + Status + Agent + " ORDER BY " + order ; DataTable rs = Db.Rs(strSql); for (int i = 0; i < rs.Rows.Count; i++) { if (!Response.IsClientConnected) { break; } TableRow r = new TableRow(); TableCell c; r.VerticalAlign = VerticalAlign.Top; r.Attributes["ondblclick"] = "popEditKontrak('" + rs.Rows[i]["NoKontrak"] + "')"; c = new TableCell(); c.Text = Cf.Pk(rs.Rows[i]["NoKontrak"]); c.HorizontalAlign = HorizontalAlign.Left; r.Cells.Add(c); c = new TableCell(); c.Text = Cf.IndoWeek(Convert.ToDateTime(rs.Rows[i]["TglKontrak"])) + "<br />" + Cf.Day(rs.Rows[i]["TglKontrak"]); c.HorizontalAlign = HorizontalAlign.Left; r.Cells.Add(c); c = new TableCell(); c.Text = Cf.Str(rs.Rows[i]["Cs"]) + "<br />" + "KTP: " + rs.Rows[i]["NoKTP"].ToString() + "<br />" + rs.Rows[i]["NoTelp"].ToString() + "<br />" + rs.Rows[i]["NoHP"].ToString() ; c.HorizontalAlign = HorizontalAlign.Left; r.Cells.Add(c); c = new TableCell(); c.Text = Str.Lantai(Cf.Pk(rs.Rows[i]["NoUnit"])); c.HorizontalAlign = HorizontalAlign.Left; r.Cells.Add(c); c = new TableCell(); c.Text = Str.Blok(Cf.Pk(rs.Rows[i]["NoUnit"])); c.HorizontalAlign = HorizontalAlign.Left; r.Cells.Add(c); c = new TableCell(); c.Text = Str.NoUnit(Cf.Pk(rs.Rows[i]["NoUnit"])); c.HorizontalAlign = HorizontalAlign.Left; r.Cells.Add(c); c = new TableCell(); c.Text = Cf.Str(rs.Rows[i]["Jenis"]); c.HorizontalAlign = HorizontalAlign.Left; r.Cells.Add(c); c = new TableCell(); c.Text = Cf.Num(rs.Rows[i]["NilaiKontrak"]); c.HorizontalAlign = HorizontalAlign.Right; r.Cells.Add(c); c = new TableCell(); c.Text = Cf.Str(rs.Rows[i]["Skema"]); c.HorizontalAlign = HorizontalAlign.Left; r.Cells.Add(c); c = new TableCell(); c.Text = NoTTS(Cf.Pk(rs.Rows[i]["NoKontrak"])); c.HorizontalAlign = HorizontalAlign.Left; r.Cells.Add(c); c = new TableCell(); c.Text = CaraBayar(Cf.Pk(rs.Rows[i]["NoKontrak"])); c.HorizontalAlign = HorizontalAlign.Left; r.Cells.Add(c); c = new TableCell(); c.Text = Cf.Num(rs.Rows[i]["TotalBFR"]); c.HorizontalAlign = HorizontalAlign.Right; r.Cells.Add(c); c = new TableCell(); c.Text = Cf.Str(rs.Rows[i]["Ag"]); c.HorizontalAlign = HorizontalAlign.Left; r.Cells.Add(c); rpt.Rows.Add(r); t1 = t1 + (decimal)rs.Rows[i]["NilaiKontrak"]; t2 = t2 + (decimal)rs.Rows[i]["TotalBFR"]; if (i == (rs.Rows.Count - 1)) { SubTotal(t1, t2); } } }