private void Bind()
{
Js.Focus(this, nama);
dari.Text = Cf.Day(Cf.AwalBulan(DateTime.Now.Month, DateTime.Now.Year));
sampai.Text = Cf.Day(Cf.AkhirBulan(DateTime.Now.Month, DateTime.Now.Year));
LibMkt.ListTipeSales(tipe, project.SelectedValue);
}
private void init()
{
dari.Text = Cf.Day(Cf.AwalBulan());
sampai.Text = Cf.Day(Cf.AkhirBulan());
string Project = (project.SelectedValue != "SEMUA") ? "= '" + project.SelectedValue + "'" : "IN (" + Act.ProjectListSql + ")";
DataTable rs = Db.Rs("SELECT DISTINCT(Kode) FROM REF_RETENSI WHERE Project " + Project);
for (int i = 0; i < rs.Rows.Count; i++)
{
tipe.Items.Add(new ListItem(rs.Rows[i]["Kode"].ToString()));
}
}
protected void skema_SelectedIndexChanged(object sender, EventArgs e)
{
if (skema.SelectedIndex != 0)
{
string strSql = "SELECT * FROM REF_SKOM_CF WHERE NoSkema = '" + skema.SelectedValue + "' and Inaktif = 0";
DataTable rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
dari.Text = Cf.Day(rs.Rows[i]["Dari"]);
sampai.Text = Cf.Day(rs.Rows[i]["Sampai"]);
}
}
else
{
dari.Text = Cf.Day(Cf.AwalBulan(DateTime.Now.Month, DateTime.Now.Year));
sampai.Text = Cf.Day(Cf.AkhirBulan(DateTime.Now.Month, DateTime.Now.Year));
}
}
private void Bind()
{
dari.Text = Cf.Day(Cf.AwalBulan(DateTime.Now.Month, DateTime.Now.Year));
sampai.Text = Cf.Day(Cf.AkhirBulan(DateTime.Now.Month, DateTime.Now.Year));
string strSql = "SELECT * FROM REF_AGENT_TIPE WHERE Project= '" + project.SelectedValue + "'";
DataTable rs = Db.Rs(strSql);
tipesales.Items.Add(new ListItem {
Text = "Tipe Marketing:", Value = "0"
});
for (int i = 0; i < rs.Rows.Count; i++)
{
string v = rs.Rows[i]["ID"].ToString();
string t = rs.Rows[i]["Tipe"].ToString();
tipesales.Items.Add(new ListItem(t, v));
}
}
private void init()
{
dari.Text = Cf.Day(Cf.AwalBulan());
sampai.Text = Cf.Day(Cf.AkhirBulan());
DataTable rs;
string Project = project.SelectedIndex == 0 ? "Project IN (" + Act.ProjectListSql + ")" : "Project = '" + project.SelectedValue + "'";
rs = Db.Rs("SELECT * FROM REF_JENIS WHERE " + Project + " ORDER BY SN");
for (int i = 0; i < rs.Rows.Count; i++)
{
string v = rs.Rows[i]["Jenis"].ToString();
string t = v + " - " + rs.Rows[i]["Nama"].ToString();
jenis.Items.Add(new ListItem(t, v));
jenis.Items[i].Selected = true;
}
rs = Db.Rs("SELECT * FROM REF_LOKASI WHERE " + Project + " ORDER BY Lokasi");
for (int i = 0; i < rs.Rows.Count; i++)
{
lokasi.Items.Add(new ListItem(rs.Rows[i][0].ToString()));
}
string aa = "";
if (UserAgent() > 0)
{
aa = " AND NoAgent = " + UserAgent();
}
rs = Db.Rs("SELECT Nama, NoAgent FROM MS_AGENT WHERE Status = 'A' AND " + Project + aa);
for (int i = 0; i < rs.Rows.Count; i++)
{
agent.Items.Add(new ListItem(rs.Rows[i]["Nama"].ToString(), rs.Rows[i]["NoAgent"].ToString()));
}
//project.Items.Clear();
//project.Items.Add("SEMUA");
//Act.ProjectList(project);
jenis.SelectedIndex = 0;
lokasi.SelectedIndex = 0;
agent.SelectedIndex = 0;
}
private void Bind()
{
dari.Text = Cf.Day(Cf.AwalBulan());
sampai.Text = Cf.Day(Cf.AkhirBulan());
string strSql = "SELECT NoAgent, Nama FROM MS_AGENT WHERE Status = 'A' ORDER BY Nama";
DataTable rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
ddlAgent.Items.Add(new ListItem(rs.Rows[i]["Nama"].ToString(), Cf.Pk(rs.Rows[i]["NoAgent"])));
}
strSql = "SELECT DISTINCT Principal FROM MS_AGENT";
rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
cblPrincipal.Items.Add(new ListItem(rs.Rows[i]["Principal"].ToString()));
cblPrincipal.Items[i].Selected = true;
}
strSql = "SELECT Nomor, Nama FROM REF_SKEMA WHERE Status = 'A'";
rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
cblSkema.Items.Add(new ListItem(rs.Rows[i]["Nama"].ToString() + " (" + Cf.Pk(rs.Rows[i]["Nomor"]).PadLeft(3, '0') + ")", Cf.Pk(rs.Rows[i]["Nomor"])));
cblSkema.Items[i].Selected = true;
}
strSql = "SELECT * FROM REF_JENIS ORDER BY SN";
rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
cblTipe.Items.Add(new ListItem(rs.Rows[i]["Jenis"].ToString() + " - " + rs.Rows[i]["Nama"].ToString(), Cf.Pk(rs.Rows[i]["Jenis"].ToString())));
cblTipe.Items[i].Selected = true;
}
}
private void init()
{
dari.Text = Cf.Day(Cf.AwalBulan());
sampai.Text = Cf.Day(Cf.AkhirBulan());
DataTable rs = Db.Rs("SELECT DISTINCT BankKPR FROM MS_KONTRAK");
for (int i = 0; i < rs.Rows.Count; i++)
{
rekening.Items.Add(new ListItem(rs.Rows[i]["BankKPR"].ToString()));
}
//rs = Db.Rs("SELECT DISTINCT MS_KONTRAK.Lokasi FROM MS_KONTRAK INNER JOIN MS_UNIT "
// + "ON MS_KONTRAK.NoUnit = MS_UNIT.NoUnit "
// + "WHERE MS_UNIT.PT in " + Act.PT + " "
// + "ORDER BY MS_KONTRAK.Lokasi");
//if (rs.Rows.Count == 4)
//{
// lokasi.Items.Add(new ListItem("SEMUA"));
//}
rs = Db.Rs("SELECT DISTINCT Lokasi FROM MS_KONTRAK ORDER BY Lokasi");
for (int i = 0; i < rs.Rows.Count; i++)
{
lokasi.Items.Add(new ListItem(rs.Rows[i][0].ToString()));
}
//for (int i = 0; i < rs.Rows.Count; i++)
// lokasi.Items.Add(new ListItem(rs.Rows[i][0].ToString()));
//lokasi.SelectedIndex = 0;
//rs = Db.Rs("SELECT PT, Nama FROM REF_PT ORDER BY PT");
//for (int i = 0; i < rs.Rows.Count; i++)
// pt.Items.Add(new ListItem(rs.Rows[i]["Nama"].ToString(), rs.Rows[i]["PT"].ToString()));
//prj.Visible = false;
//clsr.Visible = false;
}
private void Fill()
{
//DateTime Dari = Convert.ToDateTime(dari.Text);
decimal t1 = 0;
decimal t2 = 0;
decimal t3 = 0;
decimal t4 = 0;
decimal t5 = 0;
decimal t6 = 0;
string strSql = "SELECT a.*, a.Status, b.Nama AS Cust"
+ " 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_UNIT c ON a.NoUnit = c.NoUnit"
+ " WHERE 1=1 "
+ " ORDER BY a.TGLKONTRAK";
DataTable rs = Db.Rs(strSql);
TableRow trow = new TableRow();
TableCell tc;
trow.BackColor = Color.Gray;
trow.HorizontalAlign = HorizontalAlign.Center;
tc = new TableCell();
tc.Text = "NO.";
tc.RowSpan = 2;
trow.Cells.Add(tc);
tc = new TableCell();
tc.Text = "CUSTOMER";
tc.RowSpan = 2;
trow.Cells.Add(tc);
tc = new TableCell();
tc.Text = "TGL BF";
tc.RowSpan = 2;
trow.Cells.Add(tc);
tc = new TableCell();
tc.Text = "TYPE";
tc.ColumnSpan = 4;
trow.Cells.Add(tc);
tc = new TableCell();
tc.Text = "HARGA";
tc.RowSpan = 2;
trow.Cells.Add(tc);
tc = new TableCell();
tc.Text = "PPN";
tc.RowSpan = 2;
trow.Cells.Add(tc);
tc = new TableCell();
tc.Text = "TOTAL HARGA";
tc.RowSpan = 2;
trow.Cells.Add(tc);
tc = new TableCell();
tc.Text = "TYPE OF PAYMENT";
tc.RowSpan = 2;
trow.Cells.Add(tc);
tc = new TableCell();
tc.Text = "Akumulasi Sebelum " + Cf.Monthname(Convert.ToInt32(bulandari.SelectedValue)) + " " + Convert.ToInt32(tahundari.Text);
tc.RowSpan = 2;
trow.Cells.Add(tc);
DateTime awal = new DateTime(Convert.ToInt32(tahundari.Text), Convert.ToInt32(bulandari.SelectedValue), 1);
DateTime akhir = Cf.AkhirBulan(Convert.ToInt32(bulansampai.SelectedValue), Convert.ToInt32(tahunsampai.Text));
var listOfMonths = new List <string>();
var list = new List <string>();
while (awal <= akhir)
{
listOfMonths.Add(Cf.Monthname(awal.ToString("MMMM")) + " " + awal.ToString("yyyy"));
list.Add(awal.ToString("MM-yyyy"));
awal = awal.AddMonths(1);
}
foreach (var r in listOfMonths)
{
tc = new TableCell();
tc.Text = r.ToString();
tc.ColumnSpan = 4;
trow.Cells.Add(tc);
}
tc = new TableCell();
tc.Text = "Total Penerimaan Hingga " + Cf.Monthname(Convert.ToInt32(bulansampai.SelectedValue)) + " " + Convert.ToInt32(tahunsampai.Text);
tc.RowSpan = 2;
trow.Cells.Add(tc);
tc = new TableCell();
tc.Text = "";
tc.RowSpan = 2;
trow.Cells.Add(tc);
tc = new TableCell();
tc.Text = "Persentase Pelunasan";
tc.RowSpan = 2;
trow.Cells.Add(tc);
tc = new TableCell();
tc.Text = "Sisa Angsuran";
tc.RowSpan = 2;
trow.Cells.Add(tc);
rpt.Rows.Add(trow);
TableRow trow1 = new TableRow();
TableCell tc1;
trow1.BackColor = Color.Gray;
trow1.HorizontalAlign = HorizontalAlign.Center;
tc1 = new TableCell();
tc1.Text = "NO UNIT";
trow1.Cells.Add(tc1);
tc1 = new TableCell();
tc1.Text = "TOWER";
trow1.Cells.Add(tc1);
tc1 = new TableCell();
tc1.Text = "LANTAI";
trow1.Cells.Add(tc1);
tc1 = new TableCell();
tc1.Text = "LUAS";
trow1.Cells.Add(tc1);
foreach (var r in listOfMonths)
{
tc1 = new TableCell();
tc1.Text = "1 - 7";
trow1.Cells.Add(tc1);
tc1 = new TableCell();
tc1.Text = "8 - 14";
trow1.Cells.Add(tc1);
tc1 = new TableCell();
tc1.Text = "15 - 21";
trow1.Cells.Add(tc1);
tc1 = new TableCell();
tc1.Text = " >= 22";
trow1.Cells.Add(tc1);
}
rpt.Rows.Add(trow1);
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
c = new TableCell();
c.Text = (i + 1).ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Cust"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglKontrak"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoUnit"].ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Lokasi"].ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
string[] x = Cf.SplitByString(rs.Rows[i]["NoUnit"].ToString(), "/");
c = new TableCell();
c.Text = x[1];
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["Luas"]);
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Math.Round(Convert.ToDecimal(rs.Rows[i]["NilaiDPP"])).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Math.Round(Convert.ToDecimal(rs.Rows[i]["NilaiPPN"])).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Math.Round(Convert.ToDecimal(rs.Rows[i]["NilaiKontrak"])).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Skema"].ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
DateTime awala = new DateTime(Convert.ToInt32(tahundari.Text), Convert.ToInt32(bulandari.SelectedValue), 1);
c = new TableCell();
c.Text = Math.Round(Db.SingleDecimal("SELECT ISNULL(SUM(NILAIPELUNASAN),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE TglPelunasan < '" + Cf.Tgl112(awala) + "' AND NoKontrak='" + rs.Rows[i]["NoKontrak"].ToString() + "'")).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
foreach (var u in list)
{
string[] a = Cf.SplitByString(u.ToString(), "-");
DateTime week1a = new DateTime(Convert.ToInt32(a[1]), Convert.ToInt32(a[0]), 1);
DateTime week1b = new DateTime(Convert.ToInt32(a[1]), Convert.ToInt32(a[0]), 7);
DateTime week2a = new DateTime(Convert.ToInt32(a[1]), Convert.ToInt32(a[0]), 8);
DateTime week2b = new DateTime(Convert.ToInt32(a[1]), Convert.ToInt32(a[0]), 14);
DateTime week3a = new DateTime(Convert.ToInt32(a[1]), Convert.ToInt32(a[0]), 15);
DateTime week3b = new DateTime(Convert.ToInt32(a[1]), Convert.ToInt32(a[0]), 21);
DateTime week4a = new DateTime(Convert.ToInt32(a[1]), Convert.ToInt32(a[0]), 22);
DateTime week4b = Cf.AkhirBulan(Convert.ToInt32(a[0]), Convert.ToInt32(a[1]));
c = new TableCell();
c.Text = Math.Round(Db.SingleDecimal("SELECT ISNULL(SUM(NILAIPELUNASAN),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE TglPelunasan <= '" + Cf.Tgl112(week1b) + "' AND TglPelunasan >= '" + Cf.Tgl112(week1a) + "' AND NoKontrak='" + rs.Rows[i]["NoKontrak"].ToString() + "'")).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Math.Round(Db.SingleDecimal("SELECT ISNULL(SUM(NILAIPELUNASAN),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE TglPelunasan <= '" + Cf.Tgl112(week2b) + "' AND TglPelunasan >= '" + Cf.Tgl112(week2a) + "' AND NoKontrak='" + rs.Rows[i]["NoKontrak"].ToString() + "'")).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Math.Round(Db.SingleDecimal("SELECT ISNULL(SUM(NILAIPELUNASAN),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE TglPelunasan <= '" + Cf.Tgl112(week3b) + "' AND TglPelunasan >= '" + Cf.Tgl112(week3a) + "' AND NoKontrak='" + rs.Rows[i]["NoKontrak"].ToString() + "'")).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Math.Round(Db.SingleDecimal("SELECT ISNULL(SUM(NILAIPELUNASAN),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE TglPelunasan <= '" + Cf.Tgl112(week4b) + "' AND TglPelunasan >= '" + Cf.Tgl112(week4a) + "' AND NoKontrak='" + rs.Rows[i]["NoKontrak"].ToString() + "'")).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
}
c = new TableCell();
c.Text = Math.Round(Db.SingleDecimal("SELECT ISNULL(SUM(NILAIPELUNASAN),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE TglPelunasan < '" + Cf.Tgl112(akhir) + "' AND NoKontrak='" + rs.Rows[i]["NoKontrak"].ToString() + "'")).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = "";
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Math.Round(Convert.ToDecimal(rs.Rows[i]["PersenLunas"])).ToString() + "%";
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
decimal tagihan = Db.SingleDecimal("SELECT ISNULL(SUM(NILAITAGIHAN),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN WHERE NoKontrak='" + rs.Rows[i]["NoKontrak"].ToString() + "'");
decimal pelunasan = Db.SingleDecimal("SELECT ISNULL(SUM(NILAIPELUNASAN),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak='" + rs.Rows[i]["NoKontrak"].ToString() + "'");
decimal sisa = tagihan - pelunasan;
c = new TableCell();
c.Text = Math.Round(sisa).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
rpt.Rows.Add(r);
}
}
