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