private void Fill()
{
string Status = "";
if (StatusA != "")
{
Status = " AND " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK.Status = 'A'";
}
if (StatusB != "")
{
Status = " AND ISC064_MARKETINGJUAL..MS_KONTRAK.Status = 'B'";
}
DateTime Dari = Db.SingleTime("SELECT FilterDari FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
DateTime Sampai = Db.SingleTime("SELECT FilterSampai FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
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 nLokasi = "";
if (Lokasi != "SEMUA")
{
nLokasi = " AND Lokasi = '" + Cf.Str(Lokasi.Replace("%", " ")) + "'";
}
string aa = "";
if (UserAgent() > 0)
{
aa = " AND " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK.NoAgent = " + UserAgent();
}
string strSql = "SELECT ISC064_MARKETINGJUAL..MS_KONTRAK.*"
+ ",ISC064_MARKETINGJUAL..MS_CUSTOMER.Nama AS Cs"
+ ",ISC064_MARKETINGJUAL..MS_CUSTOMER.NoTelp AS NoTelp"
+ ",ISC064_MARKETINGJUAL..MS_CUSTOMER.NoHp AS NoHp"
+ ",ISC064_MARKETINGJUAL..MS_AGENT.Nama AS Ag"
+ ",ISC064_MARKETINGJUAL..MS_AGENT.Principal"
+ ",(SELECT ISNULL(SUM(NilaiPelunasan),0) FROM ISC064_MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak = ISC064_MARKETINGJUAL..MS_KONTRAK.NoKontrak) AS NilaiTTS"
+ " 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);
TableRow r = new TableRow();
TableHeaderCell hc;
hc = new TableHeaderCell();
hc.Text = "Overdue";
hc.HorizontalAlign = HorizontalAlign.Right;
hc.Attributes["style"] = "background-color:gray;color:white;";
hc.Wrap = false;
r.Cells.Add(hc);
hc = new TableHeaderCell();
hc.Text = "Actual";
hc.HorizontalAlign = HorizontalAlign.Right;
hc.Attributes["style"] = "background-color:gray;color:white;";
hc.Wrap = false;
r.Cells.Add(hc);
hc = new TableHeaderCell();
hc.Text = "Early";
hc.HorizontalAlign = HorizontalAlign.Right;
hc.Attributes["style"] = "background-color:gray;color:white;";
hc.Wrap = false;
r.Cells.Add(hc);
hc = new TableHeaderCell();
hc.Text = "A";
hc.HorizontalAlign = HorizontalAlign.Right;
hc.Attributes["style"] = "background-color:gray;color:white;";
hc.Wrap = false;
r.Cells.Add(hc);
hc = new TableHeaderCell();
hc.Text = "B";
hc.HorizontalAlign = HorizontalAlign.Right;
hc.Attributes["style"] = "background-color:gray;color:white;";
r.Cells.Add(hc);
rpt.Rows.Add(r);
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
DataTable rs2 = Db.Rs("SELECT *"
+ " ,(SELECT COUNT(*) FROM ISC064_MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak = ISC064_MARKETINGJUAL..MS_TAGIHAN.NoKontrak AND NoTagihan = ISC064_MARKETINGJUAL..MS_TAGIHAN.NoUrut) AS CountLunas"
+ " ,(SELECT COUNT(*) FROM ISC064_MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak = ISC064_MARKETINGJUAL..MS_TAGIHAN.NoKontrak AND NoTagihan = ISC064_MARKETINGJUAL..MS_TAGIHAN.NoUrut AND (CONVERT(VARCHAR, TglPelunasan, 112) >= " + Cf.Tgl112(Dari) + " AND CONVERT(VARCHAR, TglPelunasan, 112) <= " + Cf.Tgl112(Sampai) + ")) AS CountLunas2"
+ " FROM ISC064_MARKETINGJUAL..MS_TAGIHAN"
+ " WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "'");
string temp = Tagihan(rs2, Cf.Pk(rs.Rows[i]["NoKontrak"]), "NAMA", Dari, Sampai);
if (temp != "")
{
r = new TableRow();
TableCell c;
r.VerticalAlign = VerticalAlign.Top;
r.Attributes["ondblclick"] = "popEditKontrak('" + rs.Rows[i]["NoKontrak"] + "')";
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 = rs.Rows[i]["NoKontrak"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
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 = Cf.Str(rs.Rows[i]["Cs"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Str(rs.Rows[i]["NoTelp"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Str(rs.Rows[i]["NoHp"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Str(rs.Rows[i]["Ag"]);
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 = temp;
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Tagihan(rs2, Cf.Pk(rs.Rows[i]["NoKontrak"]), "TGL", Dari, Sampai);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Tagihan(rs2, Cf.Pk(rs.Rows[i]["NoKontrak"]), "OVERDUE", Dari, Sampai);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Tagihan(rs2, Cf.Pk(rs.Rows[i]["NoKontrak"]), "ACTUAL", Dari, Sampai);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Tagihan(rs2, Cf.Pk(rs.Rows[i]["NoKontrak"]), "EARLY", Dari, Sampai);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Tagihan(rs2, Cf.Pk(rs.Rows[i]["NoKontrak"]), "TGLLUNAS", Dari, Sampai);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Tagihan(rs2, Cf.Pk(rs.Rows[i]["NoKontrak"]), "A", Dari, Sampai);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Tagihan(rs2, Cf.Pk(rs.Rows[i]["NoKontrak"]), "B", Dari, Sampai);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
rpt.Rows.Add(r);
}
if (i == (rs.Rows.Count - 1))
{
SubTotal();
}
}
}
private void Fill()
{
string strAdd = "";
if (Lokasi != "SEMUA")
{
strAdd += " AND a.Lokasi = '" + Lokasi.Replace("%", " ") + "'";
}
if (Sales != "SEMUA")
{
strAdd += " AND a.NoAgent = " + Cf.Pk(Sales);
}
else
{
if (UserAgent() > 0)
{
strAdd += " AND a.NoAgent = " + UserAgent();
}
}
string nProject = "";
if (Project != "SEMUA")
{
nProject = " AND a.Project IN ('" + Project.Replace(",", "','") + "')";
}
string nPerusahaan = "";
if (Perusahaan != "SEMUA")
{
nPerusahaan = " AND a.Pers = '" + Perusahaan + "'";
}
string strSql = "SELECT *, b.Nama AS NamaCustomer, c.Nama AS NamaAgent, c.Principal"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK a "
+ " INNER JOIN " + Mi.DbPrefix + "MARKETINGJUAL..MS_CUSTOMER b "
+ " ON a.NoCustomer = b.NoCustomer "
+ " INNER JOIN " + Mi.DbPrefix + "MARKETINGJUAL..MS_AGENT c"
+ " ON a.NoAgent = c.NoAgent "
+ " WHERE 1=1 "
+ " AND a.Status = 'A' "
+ nProject
+ nPerusahaan
+ strAdd
+ " ORDER BY a.NoUnit"
;
DataTable rs = Db.Rs(strSql);
decimal t1 = 0, t2 = 0, t3 = 0, t4 = 0, t5 = 0, t6 = 0;
int index = 1;
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
decimal st1 = 0, st2 = 0, st3 = 0, st4 = 0, st5 = 0;
TableRow tr = new TableRow();
TableCell c;
c = new TableCell();
c.Text = index.ToString();
c.HorizontalAlign = HorizontalAlign.Center;
c.Wrap = false;
tr.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoKontrak"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
tr.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoUnit"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
tr.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NamaCustomer"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
tr.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NamaAgent"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
tr.Cells.Add(c);
c = new TableCell();
decimal Total = TotalOutstanding(rs.Rows[i]["NoKontrak"].ToString());
c.Text = Cf.Num(Total);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
tr.Cells.Add(c);
FillOutstanding(rs.Rows[i]["NoKontrak"].ToString(), ref t2, ref t3, ref t4, ref t5, ref t6,
ref st1, ref st2, ref st3, ref st4, ref st5, ref index, tr);
t1 += Total;
}
GrandTotal(t1, t2, t3, t4, t5, t6);
}