private void Fill()
{
string strSql = "SELECT * FROM MS_NUP WHERE NoNUP = '" + nomor + "' AND Tipe = '" + Tipe + "'";
DataTable rs = Db.Rs(strSql);
if (rs.Rows.Count != 0)
{
DateTime Tgl = Db.SingleTime("Select TglBayar From MS_NUP_PELUNASAN Where NoNUP = '" + nomor + "' AND Tipe = '" + Tipe + "'");
string RB = Db.SingleString("Select RekBank From MS_NUP_PELUNASAN Where NoNUP = '" + nomor + "' AND Tipe = '" + Tipe + "'");
no.Text = nup.Text = nomor;
tglp.Text = Cf.DayIndo(rs.Rows[0]["TglDaftar"]);
nama.Text = Cf.Str(rs.Rows[0]["NamaBfr"]);
tglbf.Text = Cf.Day(Tgl); //Cf.DayIndo(rs.Rows[0]["TglBayar"]);
jumlah.Text = "Rp. 1.000.000,00"; //Cf.Num(rs.Rows[0]["NilaiBayar"]);
string strSql2 = "SELECT * FROM MS_CUSTOMER WHERE NoCustomer = '" + rs.Rows[0]["NoCustomer"].ToString() + "'";
DataTable rs2 = Db.Rs(strSql2);
norek.Text = Cf.Str(rs2.Rows[0]["RekNo"]);
narek.Text = Cf.Str(rs2.Rows[0]["RekNama"]);
bank.Text = Cf.Str(rs2.Rows[0]["RekBank"]);
tglnow.Text = Cf.Day(DateTime.Today);
}
}
private void Fill()
{
string strSql = "SELECT *"
+ ",CASE CaraBayar"
+ " WHEN 'TN' THEN 'TUNAI'"
+ " WHEN 'KK' THEN 'KARTU KREDIT'"
+ " WHEN 'KD' THEN 'KARTU DEBIT'"
+ " WHEN 'TR' THEN 'TRANSFER BANK'"
+ " WHEN 'BG' THEN 'CEK GIRO'"
+ " WHEN 'UJ' THEN 'UANG JAMINAN'"
+ " WHEN 'DN' THEN 'DISKON'"
+ " END AS CaraBayar2"
+ " FROM MS_TTS WHERE NoTTS = " + nomor;
DataTable rs = Db.Rs(strSql);
if (rs.Rows.Count != 0)
{
nobkm.Text = rs.Rows[0]["Nobkm2"].ToString();
cs.Text = rs.Rows[0]["Customer"].ToString();
jumlah.Text = Cf.Num(rs.Rows[0]["Total"]);//
//jumlahbayar.Text = Cf.Num(rs.Rows[0]["Total"]);
tglcetak.Text = Cf.DayIndo(Convert.ToDateTime(rs.Rows[0]["TglBKM"]));
//pt.Text = Mi.Pt;
//alamatpt.Text = Db.SingleString("Select AlamatPers From ISC064_SECURITY..REF_DATA WHERE Project = '" + pro + "'");
//notelp.Text = Db.SingleString("Select NoTelp From ISC064_SECURITY..REF_DATA WHERE Project = '" + pro + "'");
string Tipe = rs.Rows[0]["Tipe"].ToString();
FillTable(Tipe, rs.Rows[0]["Unit"].ToString());
//if (rs.Rows[0]["CaraBayar"].ToString() == "BG")
//{
// gr.Text = " <b>X</b> ";
// cc.Text = " ";
// tr.Text = "";
//}
//else if (rs.Rows[0]["CaraBayar"].ToString() == "KK")
//{
// gr.Text = " ";
// cc.Text = " <b>X</b> ";
// tr.Text = " ";
//}
//else if (rs.Rows[0]["CaraBayar"].ToString() == "KD")
//{
// gr.Text = " ";
// cc.Text = " <b>X</b> ";
// tr.Text = " ";
//}
//else
//{
// gr.Text = " ";
// cc.Text = " ";
// tr.Text = " <b>X</b> ";
//}
}
}
protected void Fill()
{
string strSql = "SELECT a.*, b.Nama AS Cs"
+ " FROM MS_KONTRAK a"
+ " INNER JOIN MS_CUSTOMER b ON a.NoCustomer = b.NoCustomer"
+ " WHERE a.NoKontrak = '" + nomor + "'"
;
DataTable rs = Db.Rs(strSql);
if (rs.Rows.Count > 0)
{
nokontrak.Text = rs.Rows[0]["NoKontrak"].ToString();
namacs.Text = namacs2.Text = rs.Rows[0]["Cs"].ToString();
carabayar.Text = Db.SingleString("select ISNULL(Nama, '') from REF_SKEMA where Nomor = '" + rs.Rows[0]["Refskema"] + "'");
dpp.Text = "Rp. " + Cf.Num(rs.Rows[0]["NilaiKontrak"]) + ",-";
if (Convert.ToDecimal(rs.Rows[0]["DiskonRupiah"]) != 0)
{
trdiskon.Visible = true;
}
else
{
trdiskon.Visible = false;
}
diskon.Text = Cf.Num(rs.Rows[0]["DiskonRupiah"]);
total.Text = Cf.Num((decimal)rs.Rows[0]["NilaiKontrak"] - (decimal)rs.Rows[0]["DiskonRupiah"]);
bank.Text = "Bank Sinarmas";
atasnama.Text = "PT Panahome Deltamas Indonesia";
nova.Text = rs.Rows[0]["Nova"].ToString();
tglkontrak.Text = Cf.DayIndo(rs.Rows[0]["TglKontrak"]);
//fill data unit
int CountUnit = Db.SingleInteger("select count(*) from MS_UNIT where NoStock = '" + rs.Rows[0]["NoStock"] + "'");
if (CountUnit != 0)
{
string strSqlUnit = "";
strSqlUnit = "SELECT * "
+ " FROM MS_UNIT WHERE NoStock = '" + rs.Rows[0]["NoStock"] + "'";
DataTable rsUnit = Db.Rs(strSqlUnit);
for (int k = 0; k < rsUnit.Rows.Count; k++)
{
namajalan.Text = rsUnit.Rows[k]["NamaJalan"].ToString();
nounit.Text = rsUnit.Rows[k]["Nomor"].ToString().PadLeft(2, '0');
}
}
FillTb();
}
}
private void Fill()
{
string strSql = "SELECT *"
+ ",CASE CaraBayar"
+ " WHEN 'TN' THEN 'TUNAI'"
+ " WHEN 'KK' THEN 'KARTU KREDIT'"
+ " WHEN 'KD' THEN 'KARTU DEBIT'"
+ " WHEN 'TR' THEN 'TRANSFER BANK'"
+ " WHEN 'BG' THEN 'CEK GIRO'"
+ " WHEN 'UJ' THEN 'UANG JAMINAN'"
+ " WHEN 'DN' THEN 'DISKON'"
+ " END AS CaraBayar2"
+ " FROM " + Mi.DbPrefix + "FINANCEAR..MS_TTS WHERE NoTTS = " + nomor;
DataTable rs = Db.Rs(strSql);
if (rs.Rows.Count != 0)
{
namacs.Text = Cf.Str(rs.Rows[0]["Customer"]);
Customer.Text = Cf.Str(rs.Rows[0]["Customer"]);
noAcc.Text = Cf.Str(rs.Rows[0]["Acc"]);
//req nomor panahome (reset perbulan)
nobkm.Text = rs.Rows[0]["NoTTS2"].ToString();
tglbkm.Text = Cf.DayIndo(rs.Rows[0]["Tgltts"]);
nosp.Text = rs.Rows[0]["Ref"].ToString();
nilainup.Text = nominal.Text = Cf.Num(rs.Rows[0]["Total"]);
//int temp = Convert.ToInt16(Cf.Num(rs.Rows[0]["Total"]));
jumlahUnit.Text = rs.Rows[0]["Unit"].ToString();
terbilangnilainup.Text = Money.Str(Convert.ToDecimal(rs.Rows[0]["Total"])) + " RUPIAH";
if (rs.Rows[0]["Acc"].ToString() != "-")
{
bankacc.Text = Db.SingleString("select ISNULL(Bank, '') from REF_ACC where Acc = '" + rs.Rows[0]["Acc"].ToString() + "'") + " (" + rs.Rows[0]["CaraBayar2"].ToString() + ")";
}
else
{
bankacc.Text = "Cash";
}
tglttd.Text = Cf.DayIndo(rs.Rows[0]["TglTTS"]);
tglbankacc.Text = Cf.DayIndo(rs.Rows[0]["TglTTS"]);
decimal PpnNUP = Convert.ToDecimal(rs.Rows[0]["Total"]) / (decimal)1.1;
decimal DppNUP = Convert.ToDecimal(rs.Rows[0]["Total"]) - PpnNUP;
dppnup.Text = Cf.NumBulat(PpnNUP);
ppnnup.Text = Cf.NumBulat(DppNUP);
baya.Text = rs.Rows[0]["ket"].ToString();
//fill data customer
int CountNUP = Db.SingleInteger("select count(*) from " + Mi.DbPrefix + "MARKETINGJUAL..MS_NUP where NoNUP = '" + rs.Rows[0]["NoNUP"] + "'");
if (CountNUP != 0)
{
string strSqlNUP = "";
strSqlNUP = "SELECT "
+ " b.KTP1"
+ " ,b.KTP2"
+ " ,b.KTP3"
+ " ,b.KTP4"
+ " ,b.KTP5"
+ " ,b.Kodepos"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_NUP a INNER JOIN " + Mi.DbPrefix + "MARKETINGJUAL..MS_CUSTOMER b ON a.NoCustomer = b.NoCustomer WHERE a.NoNUP = '" + rs.Rows[0]["NoNUP"] + "'";
DataTable rsNUP = Db.Rs(strSqlNUP);
for (int j = 0; j < rsNUP.Rows.Count; j++)
{
alamat1.Text = rsNUP.Rows[j]["KTP1"].ToString();
alamat2.Text = rsNUP.Rows[j]["KTP2"].ToString() + " " + rsNUP.Rows[j]["KTP3"].ToString();
alamat3.Text = rsNUP.Rows[j]["KTP4"].ToString() + " " + rsNUP.Rows[j]["KTP5"].ToString() + " " + rsNUP.Rows[j]["Kodepos"].ToString();
}
}
else
{
string strSqlKontrak = "SELECT "
+ " b.KTP1"
+ " ,b.KTP2"
+ " ,b.KTP3"
+ " ,b.KTP4"
+ " ,b.KTP5"
+ " ,b.Kodepos"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK a INNER JOIN " + Mi.DbPrefix + "MARKETINGJUAL..MS_CUSTOMER b ON a.NoCustomer = b.NoCustomer WHERE a.NoKontrak = '" + rs.Rows[0]["Ref"] + "'";
DataTable rsKon = Db.Rs(strSqlKontrak);
for (int j = 0; j < rsKon.Rows.Count; j++)
{
alamat1.Text = rsKon.Rows[j]["KTP1"].ToString();
alamat2.Text = rsKon.Rows[j]["KTP2"].ToString() + " " + rsKon.Rows[j]["KTP3"].ToString();
alamat3.Text = rsKon.Rows[j]["KTP4"].ToString() + " " + rsKon.Rows[j]["KTP5"].ToString() + " " + rsKon.Rows[j]["Kodepos"].ToString();
}
}
//fill data unit
int CountUnit = Db.SingleInteger("select count(*) from " + Mi.DbPrefix + "MARKETINGJUAL..MS_UNIT where NoUnit = '" + rs.Rows[0]["Unit"] + "'");
if (CountUnit != 0)
{
string strSqlUnit = "";
strSqlUnit = "SELECT * "
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_UNIT WHERE NoUnit = '" + rs.Rows[0]["Unit"] + "'";
DataTable rsUnit = Db.Rs(strSqlUnit);
for (int k = 0; k < rsUnit.Rows.Count; k++)
{
nounit.Text = rsUnit.Rows[k]["Nomor"].ToString();
jalan.Text = rsUnit.Rows[k]["NamaJalan"].ToString();
cluster.Text = rsUnit.Rows[k]["Lokasi"].ToString();
}
}
else
{
hide1.Visible = false;
hide2.Visible = false;
hide3.Visible = false;
}
}
}
private void Fill()
{
string strSql = "SELECT *"
+ ",CASE CaraBayar"
+ " WHEN 'TN' THEN 'TUNAI'"
+ " WHEN 'KK' THEN 'KARTU KREDIT'"
+ " WHEN 'KD' THEN 'KARTU DEBIT'"
+ " WHEN 'TR' THEN 'TRANSFER BANK'"
+ " WHEN 'BG' THEN 'CEK GIRO'"
+ " WHEN 'UJ' THEN 'UANG JAMINAN'"
+ " WHEN 'DN' THEN 'DISKON'"
+ " END AS CaraBayar2"
+ " FROM ISC064_FINANCEAR..MS_TTS WHERE NoTTS = " + nomor;
DataTable rs = Db.Rs(strSql);
if (rs.Rows.Count != 0)
{
string Tipe = "";
if (rs.Rows[0]["NoNUP"].ToString() == "")
{
Tipe = Db.SingleString("Select Jenis From ISC064_MARKETINGJUAL..MS_KONTRAK Where NoKontrak = '" + rs.Rows[0]["Ref"].ToString() + "'");
}
else
{
string NoStock = Db.SingleString("SELECT NoStock FROM ISC064_MARKETINGJUAL..MS_NUP_PRIORITY WHERE NoNUP = '" + rs.Rows[0]["NoNUP"].ToString() + "'");
Tipe = Db.SingleString("SELECT Jenis FROM ISC064_MARKETINGJUAL..MS_UNIT WHERE NoStock = '" + NoStock + "'");
}
string KTP1 = Db.SingleString("Select KTP1 From ISC064_MARKETINGJUAL..MS_CUSTOMER Where Nama = '" + rs.Rows[0]["Customer"].ToString() + "'");
string KTP2 = Db.SingleString("Select KTP2 From ISC064_MARKETINGJUAL..MS_CUSTOMER Where Nama = '" + rs.Rows[0]["Customer"].ToString() + "'");
string KTP3 = Db.SingleString("Select KTP3 From ISC064_MARKETINGJUAL..MS_CUSTOMER Where Nama = '" + rs.Rows[0]["Customer"].ToString() + "'");
string KTP4 = Db.SingleString("Select KTP4 From ISC064_MARKETINGJUAL..MS_CUSTOMER Where Nama = '" + rs.Rows[0]["Customer"].ToString() + "'");
string Email = Db.SingleString("Select Email From ISC064_MARKETINGJUAL..MS_CUSTOMER Where Nama = '" + rs.Rows[0]["Customer"].ToString() + "'");
string NoHP = Db.SingleString("Select NoHp From ISC064_MARKETINGJUAL..MS_CUSTOMER Where Nama = '" + rs.Rows[0]["Customer"].ToString() + "'");
typ.Text = Tipe;
alamat.Text = KTP1 + "," + KTP2 + "," + KTP3 + "," + KTP4;
email.Text = Email;
nohp.Text = NoHP;
unit.Text = rs.Rows[0]["Unit"].ToString();
typ.Text = rs.Rows[0]["Jenis"].ToString();
nokwi.Text = rs.Rows[0]["NoTTS"].ToString().PadLeft(6, '0');
tglreal.Text = Cf.DayIndo(rs.Rows[0]["TglTTS"]);
kasir.Text = Db.SingleString("SELECT Nama FROM ISC064_SECURITY..USERNAME WHERE UserID = '" + rs.Rows[0]["UserID"].ToString() + "'");
tanggal.Text = Cf.DayIndo(rs.Rows[0]["TglTTS"]);
namacustomer.Text = Cf.Str(rs.Rows[0]["Customer"]);
total.Text = Money.Str(Convert.ToDecimal(rs.Rows[0]["Total"])) + " RUPIAH";
nilaitagihan.Text = NTot.Text = Cf.Num(rs.Rows[0]["Total"]);
if (rs.Rows[0]["Ket"].ToString() == "PEMBAYARAN NUP KEDUA" && rs.Rows[0]["NoNUP"].ToString() != "")
{
NBF.Text = Cf.Num(rs.Rows[0]["Total"]);
}
else if (rs.Rows[0]["Ket"].ToString() == "PEMBAYARAN NUP" && rs.Rows[0]["NoNUP"].ToString() != "")
{
NNUP.Text = Cf.Num(rs.Rows[0]["Total"]);
}
}
}
private void Fill()
{
DateTime Tanggal1 = Convert.ToDateTime(tglawal.Text);
DateTime Tanggal2 = Convert.ToDateTime(tglakhir.Text);
string Aktivasi = "";
string Status = "";
string Tipe = "";
decimal t1 = 0;
decimal t2 = 0;
decimal t3 = 0;
decimal t4 = 0;
decimal t5 = 0;
//status
if (status.SelectedValue == "PilihUnit")
{
Status = " AND Status = '3'";
}
else if (status.SelectedValue == "SudahClosing")
{
Status = " AND Status = '4' AND NoNUP IN (select NoNUP from MS_NUP_PRIORITY where NoKontrak != '')";
}
else if (status.SelectedValue == "SudahBayar")
{
Status = " AND Status = '4' AND NoNUP IN (select NoNUP from MS_NUP_PRIORITY where NoKontrak = '')";
}
//Aktivasi
if (aktivasi.SelectedIndex == 1)
{
Aktivasi = " AND TglAktivasi is not null";
}
if (aktivasi.SelectedIndex == 2)
{
Aktivasi = " AND TglAktivasi is null";
}
//Tipe Properti
if (tipepro.SelectedIndex > 0)
{
Tipe = " AND Tipe = '" + tipepro.SelectedValue + "'";
}
//Fill Table Total
TableRow r2 = new TableRow();
TableCell c2;
r2.VerticalAlign = VerticalAlign.Top;
//Nama Project
c2 = new TableCell();
c2.Text = Db.SingleString("select ISNULL(Nama, '') from " + Mi.DbPrefix + "SECURITY..REF_PROJECT where Project = 'SVS'"); //hardcode project -- Cf.Str(Mi.Pt);
c2.HorizontalAlign = HorizontalAlign.Left;
c2.Wrap = false;
r2.Cells.Add(c2);
//jumlah NUP
c2 = new TableCell();
c2.Text = Cf.Num(Db.SingleInteger("select count(*) from MS_NUP"));
c2.HorizontalAlign = HorizontalAlign.Left;
c2.Wrap = false;
r2.Cells.Add(c2);
//Nup Tidak Aktif
c2 = new TableCell();
c2.Text = Cf.Num(Db.SingleInteger("select count(*) from MS_NUP where Status = 0"));
c2.HorizontalAlign = HorizontalAlign.Left;
c2.Wrap = false;
r2.Cells.Add(c2);
//NUP Aktif
c2 = new TableCell();
c2.Text = Cf.Num(Db.SingleInteger("select count(*) from MS_NUP where Status != 0"));
c2.HorizontalAlign = HorizontalAlign.Left;
c2.Wrap = false;
r2.Cells.Add(c2);
//Tidak Pilih Unit
c2 = new TableCell();
c2.Text = Cf.Num(Db.SingleInteger("select COUNT(*) from MS_NUP where NoNUP NOT IN (SELECT NoNUP FROM MS_NUP_PRIORITY)"));
c2.HorizontalAlign = HorizontalAlign.Left;
c2.Wrap = false;
r2.Cells.Add(c2);
//Pilih Unit
c2 = new TableCell();
c2.Text = Cf.Num(Db.SingleInteger("select count(*) from MS_NUP_PRIORITY where NoStock != ''"));
c2.HorizontalAlign = HorizontalAlign.Left;
c2.Wrap = false;
r2.Cells.Add(c2);
//Nilai Pembayaran
c2 = new TableCell();
decimal nBayar2 = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiBayar),0) FROM MS_NUP_PELUNASAN");
c2.Text = Cf.Num(nBayar2);
c2.HorizontalAlign = HorizontalAlign.Right;
c2.Wrap = false;
r2.Cells.Add(c2);
rptTotal.Rows.Add(r2);
//fill table NUP lengkap
string strSql = "SELECT * FROM MS_NUP WHERE 1=1"
+ " AND CONVERT(DATETIME,TglDaftar,112) BETWEEN '" + Cf.Tgl112(Tanggal1) + "' AND '" + Cf.Tgl112(Tanggal2) + "'"
+ Aktivasi
+ Status
+ Tipe
+ " ORDER BY Tipe DESC, NoNUP ASC";
DataTable dtNUP = Db.Rs(strSql);
Rpt.NoData(rpt, dtNUP, "Tidak ada data NUP yang terdaftar.");
for (int i = 0; i < dtNUP.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
r.VerticalAlign = VerticalAlign.Top;
//NoNUP
c = new TableCell();
string cetakn = dtNUP.Rows[i]["NoNUP"].ToString();
if (Convert.ToInt32(dtNUP.Rows[i]["Revisi"].ToString()) > 0)
{
cetakn = cetakn + "R";
}
c.Text = Cf.Str(cetakn);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
//Nama Customer
c = new TableCell();
c.Text = Db.SingleString("select ISNULL(Nama,' ') from ms_customer where NoCustomer = '" + Convert.ToInt32(dtNUP.Rows[i]["NoCustomer"]) + "'");
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
//Tipe Agent
int kodeTipeAgent = Db.SingleInteger("select ISNULL(SalesTipe, 0) from MS_AGENT WHERE NoAgent = '" + Convert.ToInt32(dtNUP.Rows[i]["NoAgent"]) + "'");
c = new TableCell();
c.Text = Db.SingleString("select ISNULL(Tipe, '') from REF_AGENT_TIPE WHERE ID = '" + kodeTipeAgent + "'");
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
//Nama Agent
c = new TableCell();
c.Text = Db.SingleString("select ISNULL(Nama,' ') from MS_AGENT WHERE NoAgent = '" + Convert.ToInt32(dtNUP.Rows[i]["NoAgent"]) + "'");
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
//Tipe
c = new TableCell();
c.Text = Cf.Str(dtNUP.Rows[i]["Tipe"].ToString());
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
//Tgl Aktivasi
c = new TableCell();
c.Text = Cf.DayIndo(dtNUP.Rows[i]["TglAktivasi"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
//// cek apakah ada si nup priority
int adapriority = Db.SingleInteger("SELECT COUNT(*) FROM MS_NUP_PRIORITY WHERE NoNUP='" + dtNUP.Rows[i]["NoNUP"].ToString() + "' AND Tipe = '" + dtNUP.Rows[i]["Tipe"].ToString() + "'");
//Unit
c = new TableCell();
string noUnit = Db.SingleString("SELECT ISNULL(NoStock,' ') FROM MS_NUP_PRIORITY WHERE NoNUP='" + dtNUP.Rows[i]["NoNUP"].ToString() + "' AND Tipe = '" + dtNUP.Rows[i]["Tipe"].ToString() + "'");
c.Text = Db.SingleString("select NoUnit from MS_UNIT where NoStock = '" + noUnit + "'");
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
//No. Kontrak
c = new TableCell();
string noKontrak = Db.SingleString("SELECT ISNULL(NoKontrak,' ') FROM MS_NUP_PRIORITY WHERE NoNUP='" + dtNUP.Rows[i]["NoNUP"].ToString() + "' AND Tipe = '" + dtNUP.Rows[i]["Tipe"].ToString() + "'");
c.Text = noKontrak;
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
//Cara Bayar
c = new TableCell();
string cb = "";
if (adapriority > 0)
{
int nomorskema = Db.SingleInteger("SELECT nomorskema FROM MS_NUP_PRIORITY WHERE NoNUP='" + dtNUP.Rows[i]["NoNUP"].ToString() + "' AND Tipe = '" + dtNUP.Rows[i]["Tipe"].ToString() + "'");
cb = Db.SingleString("SELECT NAMA FROM REF_SKEMA WHERE NOMOR = " + nomorskema);
}
c.Text = cb;
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
//Pricelist
c = new TableCell();
string pl = "";
if (adapriority > 0)
{
decimal pls = Db.SingleDecimal("SELECT ISNULL(Harga,' ') FROM MS_NUP_PRIORITY WHERE NoNUP='" + dtNUP.Rows[i]["NoNUP"].ToString() + "' AND Tipe = '" + dtNUP.Rows[i]["Tipe"].ToString() + "'");
pl = Cf.Num(pls);
}
c.Text = pl;
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
//Nilai Pembayaran
c = new TableCell();
decimal nBayar = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiBayar),0) FROM MS_NUP_PELUNASAN WHERE NoNUP='" + dtNUP.Rows[i]["NoNUP"].ToString() + "' AND Tipe = '" + dtNUP.Rows[i]["Tipe"].ToString() + "'");
c.Text = Cf.Num(nBayar);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
//Status
c = new TableCell();
if (Convert.ToInt32(dtNUP.Rows[i]["Status"]) == 0)
{
c.Text = "Belum Aktivasi";
}
else if (Convert.ToInt32(dtNUP.Rows[i]["Status"]) == 1)
{
c.Text = "Aktivasi";
}
else if (Convert.ToInt32(dtNUP.Rows[i]["Status"]) == 3)
{
c.Text = "Pilih Unit";
}
else if (Convert.ToInt32(dtNUP.Rows[i]["Status"]) == 4)
{
int countKontrak = Db.SingleInteger("select count(*) from ms_nup_priority where NoNUP = '" + Cf.Str(dtNUP.Rows[i]["NoNUP"]) + "' and NoKontrak != ''");
if (countKontrak != 0)
{
c.Text = "Sudah Closing";
}
else
{
c.Text = "Sudah Bayar";
}
}
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
decimal totalNUP = Db.SingleInteger("select count(*) from ms_nup where NoNUP = '" + Cf.Str(dtNUP.Rows[i]["NoNUP"]) + "' AND Tipe = '" + Cf.Str(dtNUP.Rows[i]["Tipe"]) + "'");
decimal totalAktivasi = Db.SingleInteger("select count(*) from ms_nup where NoNUP = '" + Cf.Str(dtNUP.Rows[i]["NoNUP"]) + "' AND Tipe = '" + Cf.Str(dtNUP.Rows[i]["Tipe"]) + "' and TglAktivasi is not null");
decimal totalPilihUnit = Db.SingleInteger("select count(*) from ms_nup where NoNUP = '" + Cf.Str(dtNUP.Rows[i]["NoNUP"]) + "' AND Tipe = '" + Cf.Str(dtNUP.Rows[i]["Tipe"]) + "' and NoNUP IN (select NoNUP from MS_NUP_PRIORITY where NoKontrak = '')");
decimal totalClosing = Db.SingleInteger("select count(*) from ms_nup where NoNUP = '" + Cf.Str(dtNUP.Rows[i]["NoNUP"]) + "' AND Tipe = '" + Cf.Str(dtNUP.Rows[i]["Tipe"]) + "' and NoNUP IN (select NoNUP from MS_NUP_PRIORITY where NoKontrak != '')");
t1 = t1 + nBayar;
t2 += totalNUP;
t3 += totalAktivasi;
t4 += totalPilihUnit;
t5 += totalClosing;
rpt.Rows.Add(r);
if (i == dtNUP.Rows.Count - 1)
{
SubTotal("TOTAL", t1, t2, t3, t4, t5);
}
}
}
protected void Fill()
{
list.Controls.Clear();
DateTime Dari = Convert.ToDateTime(dari.Text);
DateTime Sampai = Convert.ToDateTime(sampai.Text);
if (Dari > Sampai)
{
DateTime x = Sampai;
Sampai = Dari;
Dari = x;
}
//fill tittle
headperiode.Text = Cf.DayIndo(Dari) + " s/d " + Cf.DayIndo(Sampai);
string w = "";
if (tipesales.SelectedIndex != 0)
{
trTipeSales.Visible = true;
headtipe.Text = Db.SingleString("select Tipe from REF_AGENT_TIPE where ID = '" + tipesales.SelectedValue + "'");
w = " AND b.SalesTipe = '" + tipesales.SelectedValue + "'";
}
else
{
trTipeSales.Visible = false;
}
string v = "";
if (sales.SelectedIndex != 0)
{
trNama.Visible = true;
headnama.Text = Db.SingleString("select Nama from MS_AGENT where NoAgent = '" + sales.SelectedValue + "'");
v = " AND a.NoAgent = '" + sales.SelectedValue + "'";
}
else
{
trNama.Visible = false;
}
string strSql = "SELECT a.NoCF, a.SN, a.Nilai, b.NoKontrak, b.NoUnit, a.NamaAgent, b.NamaCust"
+ " FROM MS_KOMISI_CF_DETAIL a"
+ " INNER JOIN MS_KOMISI_CF b ON a.NoCF = b.NoCF"
+ " WHERE 1=1 "
+ " AND CONVERT(varchar,b.Tgl,112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(varchar,b.Tgl,112) <= '" + Cf.Tgl112(Sampai) + "'"
+ " AND (SELECT COUNT(*) FROM MS_KOMISI_CFP_DETAIL WHERE NoCF = a.NoCF AND SN_NoCF = a.SN) = 0"
+ w
+ v
+ " AND b.Project = '" + project.SelectedValue + "'"
+ " ORDER BY b.NoCF";
DataTable rs = Db.Rs(strSql);
Rpt.NoData(list, rs, "Tidak terdapat data dengan kriteria seperti tersebut diatas.");
int index = 0;
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
HtmlTableRow r = new HtmlTableRow();
HtmlTableCell c;
CheckBox cb;
cb = new CheckBox();
cb.ID = "cb_" + index;
cb.Attributes["title"] = rs.Rows[i]["NoCF"] + ";" + rs.Rows[i]["SN"];
c = new HtmlTableCell();
c.Controls.Add(cb);
r.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = rs.Rows[i]["NoCF"].ToString();
r.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = rs.Rows[i]["NoKontrak"].ToString();
r.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = rs.Rows[i]["NoUnit"].ToString();
r.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = rs.Rows[i]["NamaCust"].ToString();
r.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = rs.Rows[i]["NamaAgent"].ToString();
r.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = Cf.Num(Convert.ToDecimal(rs.Rows[i]["Nilai"]));
c.Attributes["class"] = "right";
r.Cells.Add(c);
list.Controls.Add(r);
index++;
}
}
private void Fill()
{
string strSql = "SELECT * FROM MS_KONTRAK "
+ " WHERE NoKontrak = '" + nomor + "' and Project = '" + proj + "'"
;
DataTable rs = Db.Rs(strSql);
if (rs.Rows.Count > 0)
{
nokontrak.Text = rs.Rows[0]["NoKontrak"].ToString();
tglkontrak.Text = Cf.DayIndo(rs.Rows[0]["TglKontrak"]);
// namaproject.Text = rs.Rows[0]["NamaProject"].ToString();
namapers.Text = rs.Rows[0]["NamaPers"].ToString();
nilaikontrak.Text = Cf.NumBulat(rs.Rows[0]["NilaiKontrak"]);
skema.Text = Db.SingleString("select ISNULL(Nama, '') from REF_SKEMA where Nomor = '" + rs.Rows[0]["Refskema"] + "'");
//ag.Text = Db.SingleString("SELECT NAMA FROM MS_AGENT WHERE NoAgent = " + rs.Rows[0]["NoAgent"].ToString());
//fill data customer
int CountCus = Db.SingleInteger("select count(*) from MS_CUSTOMER where NoCustomer = '" + rs.Rows[0]["NoCustomer"] + "'");
if (CountCus != 0)
{
string strSqlCus = "";
strSqlCus = "SELECT * FROM MS_CUSTOMER WHERE NoCustomer = '" + rs.Rows[0]["NoCustomer"] + "'";
DataTable rsCus = Db.Rs(strSqlCus);
for (int j = 0; j < rsCus.Rows.Count; j++)
{
namacs.Text = namacs2.Text = rsCus.Rows[j]["Nama"].ToString();
noktp.Text = rsCus.Rows[j]["NoKTP"].ToString();
npwp.Text = rsCus.Rows[j]["NPWP"].ToString();
alamatktp1.Text = rsCus.Rows[j]["KTP1"].ToString() + " " + rsCus.Rows[j]["KTP2"].ToString() + " " + rsCus.Rows[j]["KTP3"].ToString();
alamatktp2.Text = rsCus.Rows[j]["KTP4"].ToString() + " " + rsCus.Rows[j]["KTP5"].ToString();
alamatsekarang1.Text = rsCus.Rows[j]["Alamat1"].ToString() + " " + rsCus.Rows[j]["Alamat2"].ToString() + " " + rsCus.Rows[j]["Alamat3"].ToString();
alamatsekarang2.Text = rsCus.Rows[j]["Alamat4"].ToString() + " " + rsCus.Rows[j]["Alamat5"].ToString();
hp1.Text = rsCus.Rows[j]["NoHP"].ToString();
hp2.Text = rsCus.Rows[j]["NoHP2"].ToString();
// email.Text = rsCus.Rows[j]["Email"].ToString();
}
}
//fill data unit
int CountUnit = Db.SingleInteger("select count(*) from MS_UNIT where NoStock = '" + rs.Rows[0]["NoStock"] + "'");
if (CountUnit != 0)
{
string strSqlUnit = "";
strSqlUnit = "SELECT * FROM MS_UNIT WHERE NoStock = '" + rs.Rows[0]["NoStock"] + "'";
DataTable rsUnit = Db.Rs(strSqlUnit);
for (int k = 0; k < rsUnit.Rows.Count; k++)
{
// jenisproperti.Text = rsUnit.Rows[k]["JenisProperti"].ToString();
namajalan.Text = rsUnit.Rows[k]["NamaJalan"].ToString();
nounit.Text = rsUnit.Rows[k]["Nomor"].ToString().PadLeft(2, '0');
jenis.Text = rsUnit.Rows[k]["Jenis"].ToString();
luasnett.Text = Cf.Num(rsUnit.Rows[k]["LuasNett"]);
luassg.Text = Cf.Num(rsUnit.Rows[k]["LuasSG"]);
lokasi.Text = rsUnit.Rows[k]["Lokasi"].ToString();
}
}
//gimmick
int CountGimmick = Db.SingleInteger("select count(*) from MS_KONTRAK_GIMMICK where NoKontrak = '" + nomor + "'");
if (CountGimmick != 0)
{
// gimmicktr.Visible = true;
FillTb();
}
else
{
//gimmicktr.Visible = false;
}
}
}
protected void fill()
{
list.Controls.Clear();
//mengaktifkan tittle
tbHead.Visible = true;
DateTime Dari = Convert.ToDateTime(dari.Text);
DateTime Sampai = Convert.ToDateTime(sampai.Text);
if (Dari > Sampai)
{
DateTime x = Sampai;
Sampai = Dari;
Dari = x;
}
//fill tittle
headperiode.Text = Cf.DayIndo(Dari) + " s/d " + Cf.DayIndo(Sampai);
string w = "";
if (tipesales.SelectedIndex != 0)
{
trTipeSales.Visible = true;
headtipe.Text = Db.SingleString("select Tipe from REF_AGENT_TIPE where ID = '" + tipesales.SelectedValue + "'");
w = " AND SalesTipe = '" + tipesales.SelectedValue + "'";
}
else
{
trTipeSales.Visible = false;
}
string v = "";
if (sales.SelectedIndex != 0)
{
trNama.Visible = true;
headnama.Text = Db.SingleString("select Nama from MS_AGENT where NoAgent = '" + sales.SelectedValue + "'");
v = " AND (select count(*) from MS_KOMISI_CFP_DETAIL where NoAgent = '" + sales.SelectedValue + "') != 0";
}
else
{
trNama.Visible = false;
}
string strSql = "SELECT * FROM MS_KOMISI_CFR"
+ " WHERE 1=1 "
+ " AND CONVERT(varchar,Tgl,112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(varchar,Tgl,112) <= '" + Cf.Tgl112(Sampai) + "'"
//+ " AND (SELECT COUNT(*) FROM MS_KOMISI_CFR WHERE NoCFP = MS_KOMISI_CFP.NoCFP) = 0" //jika sudah realisasi..gak nongol
+ w
+ " AND Project IN (" + Act.ProjectListSql + ")"
+ " ORDER BY NoCFR";
DataTable rs = Db.Rs(strSql);
Rpt.NoData(list, rs, "Tidak terdapat data dengan kriteria seperti tersebut diatas.");
int index = 0;
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
//fill tittle
headperiode.Text = Cf.DayIndo(Dari) + " s/d " + Cf.DayIndo(Sampai);
headtipe.Text = Db.SingleString("select Tipe from REF_AGENT_TIPE where ID = '" + tipesales.SelectedValue + "'");
if (sales.SelectedIndex != 0)
{
headnama.Text = rs.Rows[i]["NamaAgent"].ToString();
}
HtmlTableRow r = new HtmlTableRow();
HtmlTableCell c;
CheckBox cb;
cb = new CheckBox();
cb.ID = "cb_" + index;
cb.Attributes["title"] = rs.Rows[i]["NoCFR"].ToString();
c = new HtmlTableCell();
c.Controls.Add(cb);
r.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = rs.Rows[i]["NoCFR"].ToString();
r.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = Cf.Day(rs.Rows[i]["Tgl"]);
r.Cells.Add(c);
string Sales = Db.SingleString("SELECT TOP 1 STUFF((SELECT distinct ', ' + NamaAgent FROM MS_KOMISI_CFR_DETAIL AS T1"
+ " where NoCFR = '" + rs.Rows[i]["NoCFR"].ToString() + "'"
+ " FOR XML PATH('')), 1, 1, '') As Nama "
+ " FROM MS_KOMISI_CFR_DETAIL AS T2 where NoCFR = '" + rs.Rows[i]["NoCFR"].ToString() + "'"
);
c = new HtmlTableCell();
c.InnerHtml = Sales;
r.Cells.Add(c);
list.Controls.Add(r);
index++;
}
}
protected void fill()
{
tgl.Text = Cf.Day(DateTime.Today);
DateTime Dari = DateTime.Today;
DateTime Sampai = DateTime.Today;
if (Cf.isTgl(TglDari))
{
Dari = Convert.ToDateTime(TglDari);
}
if (Cf.isTgl(TglSampai))
{
Sampai = Convert.ToDateTime(TglSampai);
}
if (Dari > Sampai)
{
DateTime x = Sampai;
Sampai = Dari;
Dari = x;
}
//fill judul dan query search
project_txt.Text = Project.ToString();
periode_txt.Text = Cf.DayIndo(Dari) + " s/d " + Cf.DayIndo(Sampai);
if (Skema != "0")
{
trskema.Visible = true;
skema_txt.Text = Db.SingleString("select Nama from REF_SKOM_CF where NoSkema = '" + Skema + "' and Project = '" + Project + "'");
}
string w = "";
if (SalesID != "0")
{
trsales.Visible = true;
sales_txt.Text = Db.SingleString("select Nama from MS_AGENT where NoAgent = '" + SalesID + "' and Project = '" + Project + "'");
w = " AND a.NoAgent = '" + SalesID + "'";
}
string w2 = "";
if (SalesTipe != "0")
{
trtipe.Visible = true;
tipesales_txt.Text = Db.SingleString("select Tipe from REF_AGENT_TIPE where ID = '" + SalesTipe + "' and Project = '" + Project + "'");
w2 = " AND b.SalesTipe = '" + SalesTipe + "'";
}
string strSql = "SELECT a.*, b.SalesTipe, c.Nama AS NamaCust"
+ " FROM MS_KONTRAK a"
+ " INNER JOIN MS_AGENT b ON a.NoAgent = b.NoAgent"
+ " INNER JOIN MS_CUSTOMER c ON a.NoCustomer = c.NoCustomer"
+ " WHERE 1=1 "
+ " AND CONVERT(varchar,a.TglKontrak,112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(varchar,a.TglKontrak,112) <= '" + Cf.Tgl112(Sampai) + "'"
+ " AND a.Status = 'A' AND a.CFID = ''"
+ w
+ w2
+ " AND a.Project = '" + Project + "'"
+ " ORDER BY NoKontrak";
DataTable rs = Db.Rs(strSql);
int index = 0;
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
DateTime TglKontrak = Convert.ToDateTime(rs.Rows[i]["TglKontrak"]);
TableRow r = new TableRow();
TableCell c;
r.VerticalAlign = VerticalAlign.Top;
c = new TableCell();
c.Text = rs.Rows[i]["NoKontrak"].ToString();
c.ID = "nokontrak_" + index;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoUnit"].ToString();
c.ID = "nounit_" + index;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NamaCust"].ToString();
c.ID = "cust_" + index;
c.Attributes["title"] = rs.Rows[i]["NoCustomer"] + ";" + rs.Rows[i]["NamaCust"];
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
string strSql_u = "SELECT COUNT(*) FROM MS_KONTRAK WHERE NoAgent = '" + rs.Rows[i]["NoAgent"] + "' AND Status = 'A' AND CONVERT(VARCHAR,TglKontrak,112) <= '" + Cf.Tgl112(TglKontrak) + "' and Project = '" + Project + "'";
int unit = Db.SingleInteger(strSql_u);
string strSql_n = "SELECT ISNULL(SUM(NilaiKontrak),0) FROM MS_KONTRAK WHERE NoAgent = '" + rs.Rows[i]["NoAgent"] + "' AND Status = 'A' AND CONVERT(VARCHAR,TglKontrak,112) <= '" + Cf.Tgl112(TglKontrak) + "' and Project = '" + Project + "'";
decimal nilaikontrak = Db.SingleDecimal(strSql_n);
string w3 = "", SkemaID = "", SkemaNama = "";
if (SalesTipe != "0")
{
w3 = " AND SalesTipe = '" + SalesTipe + "'";
}
DataTable rsSkema = Db.Rs("SELECT * FROM REF_SKOM_CF WHERE " + Cf.Tgl112(TglKontrak) + " >= CONVERT(VARCHAR,Dari,112) AND " + Cf.Tgl112(TglKontrak) + " <= CONVERT(VARCHAR,Sampai,112)"
+ w3
+ " AND Inaktif = 0 AND Project = '" + rs.Rows[i]["Project"] + "'");
if (rsSkema.Rows.Count > 0)
{
decimal NilaiDasar = 0;
if (rsSkema.Rows[0]["DasarHitung"].ToString() != "")
{
SkemaID = rsSkema.Rows[0]["NoSkema"].ToString();
SkemaNama = rsSkema.Rows[0]["Nama"].ToString();
NilaiDasar = rsSkema.Rows[0]["DasarHitung"].ToString() == "DPP" ? Convert.ToDecimal(rs.Rows[i]["NilaiDPP"]) : Convert.ToDecimal(rs.Rows[i]["NilaiKontrak"]);
}
string strSql2 = "SELECT a.*, b.Nama FROM MS_KONTRAK_AGENT a"
+ " INNER JOIN MS_AGENT b ON a.NoAgent = b.NoAgent"
+ " WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' and Project = '" + Project + "'";
DataTable rsa = Db.Rs(strSql2);
for (int j = 0; j < rsa.Rows.Count; j++)
{
decimal NilaiKomisi = 0;
string PotongKomisi = "";
if (rsSkema.Rows[0]["Rumus"].ToString() == "UNIT")
{
DataTable rsDetil = Db.Rs("SELECT * FROM REF_SKOM_CF_DETAIL WHERE NoSkema = '" + rsSkema.Rows[0]["NoSkema"].ToString() + "' AND SalesLevel = " + Convert.ToInt32(rsa.Rows[j]["SalesLevel"]));
if (rsDetil.Rows.Count > 0)
{
if (rsDetil.Rows[0]["TipeTarif"].ToString() == "RP")
{
NilaiKomisi = Convert.ToDecimal(rsDetil.Rows[0]["Nilai"]);
}
else
{
NilaiKomisi = Math.Round((Convert.ToDecimal(rsDetil.Rows[0]["Nilai"]) / 100) * NilaiDasar);
}
PotongKomisi = Convert.ToBoolean(rsDetil.Rows[0]["PotongKomisi"]) ? "YA" : "TIDAK";
}
}
else
{
//Kumulatif
//unit
decimal a = Db.SingleDecimal("SELECT TargetAtas FROM REF_SKOM_CF_DETAIL2 WHERE NoSkema = '" + rsSkema.Rows[0]["NoSkema"].ToString() + "' AND SalesLevel = " + Convert.ToInt32(rsa.Rows[j]["SalesLevel"]) + " AND TipeTarget = 'UNIT' ORDER BY SN DESC");
string b = (a > 0) ? "AND " + unit + " <= TargetAtas" : "";
DataTable rsDetil = Db.Rs("SELECT * FROM REF_SKOM_CF_DETAIL2 WHERE NoSkema = '" + rsSkema.Rows[0]["NoSkema"].ToString() + "' AND SalesLevel = " + Convert.ToInt32(rsa.Rows[j]["SalesLevel"]) + " AND " + unit + " >= TargetBawah " + b + " AND TipeTarget = 'UNIT' ORDER BY SN DESC");
if (rsDetil.Rows.Count > 0)
{
if (rsDetil.Rows[0]["TipeTarif"].ToString() == "RP")
{
NilaiKomisi = Convert.ToDecimal(rsDetil.Rows[0]["Nilai"]);
}
else
{
NilaiKomisi = Math.Round((Convert.ToDecimal(rsDetil.Rows[0]["Nilai"]) / 100) * NilaiDasar);
}
PotongKomisi = Convert.ToBoolean(rsDetil.Rows[0]["PotongKomisi"]) ? "YA" : "TIDAK";
}
//nilai kontrak
decimal d = Db.SingleDecimal("SELECT TargetAtas FROM REF_SKOM_CF_DETAIL2 WHERE NoSkema = '" + rsSkema.Rows[0]["NoSkema"].ToString() + "' AND SalesLevel = " + Convert.ToInt32(rsa.Rows[j]["SalesLevel"]) + " AND TipeTarget = 'KONTRAK' ORDER BY SN DESC");
string e = (d > 0) ? "AND " + nilaikontrak + " <= TargetAtas" : "";
DataTable rsDetil2 = Db.Rs("SELECT * FROM REF_SKOM_CF_DETAIL2 WHERE NoSkema = '" + rsSkema.Rows[0]["NoSkema"].ToString() + "' AND SalesLevel = " + Convert.ToInt32(rsa.Rows[j]["SalesLevel"]) + " AND " + nilaikontrak + " >= TargetBawah " + e + " AND TipeTarget = 'KONTRAK' ORDER BY SN DESC");
if (rsDetil2.Rows.Count > 0)
{
if (rsDetil2.Rows[0]["TipeTarif"].ToString() == "RP")
{
NilaiKomisi = Convert.ToDecimal(rsDetil2.Rows[0]["Nilai"]);
}
else
{
NilaiKomisi = Math.Round((Convert.ToDecimal(rsDetil2.Rows[0]["Nilai"]) / 100) * NilaiDasar);
}
PotongKomisi = Convert.ToBoolean(rsDetil2.Rows[0]["PotongKomisi"]) ? "YA" : "TIDAK";
}
}
if (j > 0)
{
//index++;
r = new TableRow();
r.VerticalAlign = VerticalAlign.Top;
c = new TableCell();
c.ID = "nokontrak_" + index;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.ID = "nounit_" + index;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.ID = "cust_" + index;
c.Attributes["title"] = "-";
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
}
bool terakhir = false;
if (j == rsa.Rows.Count - 1)
{
terakhir = true;
}
c = new TableCell();
c.Text = rsa.Rows[j]["Nama"].ToString();
c.Attributes["title"] = rsa.Rows[j]["NoAgent"] + ";" + rsa.Rows[j]["Nama"] + ";" + SkemaID + ";" + SkemaNama + ";" + terakhir;
c.ID = "skema_agent_" + index;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(NilaiKomisi);
c.ID = "nilaikomisi_" + index;
c.CssClass = "right";
r.Cells.Add(c);
c = new TableCell();
c.Text = PotongKomisi;
c.ID = "potongkomisi_" + index;
r.Cells.Add(c);
Rpt.Border(r);
list.Controls.Add(r);
index++;
}
}
else
{
//Rpt.Border(r);
//list.Controls.Add(r);
//save.Enabled = false;
}
}
}
protected void Fill()
{
list.Controls.Clear();
DateTime Dari = Convert.ToDateTime(dari.Text);
DateTime Sampai = Convert.ToDateTime(sampai.Text);
if (Dari > Sampai)
{
DateTime x = Sampai;
Sampai = Dari;
Dari = x;
}
//fill tittle
headperiode.Text = Cf.DayIndo(Dari) + " s/d " + Cf.DayIndo(Sampai);
string w = "";
if (tipesales.SelectedIndex != 0)
{
trTipeSales.Visible = true;
headtipe.Text = Db.SingleString("select Tipe from REF_AGENT_TIPE where ID = '" + tipesales.SelectedValue + "'");
w = " AND b.SalesTipe = '" + tipesales.SelectedValue + "'";
}
else
{
trTipeSales.Visible = false;
}
string v = "";
if (sales.SelectedIndex != 0)
{
trNama.Visible = true;
headnama.Text = Db.SingleString("select Nama from MS_AGENT where NoAgent = '" + sales.SelectedValue + "'");
v = " AND a.NoAgent = '" + sales.SelectedValue + "'";
}
else
{
trNama.Visible = false;
}
string strSql = "SELECT a.*, b.Nama AS NamaAgent, b.SalesTipe, c.Nama AS NamaCust"
+ " FROM MS_KONTRAK a"
+ " INNER JOIN MS_AGENT b ON a.NoAgent = b.NoAgent"
+ " INNER JOIN MS_CUSTOMER c ON a.NoCustomer = c.NoCustomer"
+ " WHERE 1=1 "
+ " AND CONVERT(varchar,a.TglKontrak,112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(varchar,a.TglKontrak,112) <= '" + Cf.Tgl112(Sampai) + "'"
+ " AND a.Status = 'A' AND a.CFID != ''"
+ w
+ v
+ " AND a.Project = '" + project.SelectedValue + "'"
+ " ORDER BY NoKontrak";
DataTable rs = Db.Rs(strSql);
del.Enabled = false;
Rpt.NoData(list, rs, "Tidak terdapat kontrak dengan kriteria seperti tersebut diatas.");
int index = 0;
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
DateTime TglKontrak = Convert.ToDateTime(rs.Rows[i]["TglKontrak"]);
bool tampil = true;
int cfp = Db.SingleInteger("SELECT COUNT(*) FROM MS_KOMISI_CFP_DETAIL WHERE NoCF = '" + rs.Rows[i]["CFID"].ToString() + "'");
if (cfp != 0)
{
tampil = false;
}
if (tampil)
{
TableRow r = new TableRow();
TableCell c;
r.VerticalAlign = VerticalAlign.Top;
c = new TableCell();
c.Text = rs.Rows[i]["NamaAgent"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoKontrak"].ToString();
c.ID = "cfid_" + index;
c.Attributes["title"] = rs.Rows[i]["CFID"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
//Response.Write(rs.Rows[i]["CFID"].ToString());
c = new TableCell();
c.Text = rs.Rows[i]["NoUnit"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NamaCust"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
//nge-fill ref_skema_closing fee
string w2 = "";
if (tipesales.SelectedIndex != 0)
{
w2 = " AND SalesTipe = '" + tipesales.SelectedValue + "'";
}
string Skema = "", DasarHitung = "";
DataTable rsSkema = Db.Rs("SELECT * FROM REF_SKOM_CF WHERE " + Cf.Tgl112(TglKontrak) + " >= CONVERT(VARCHAR,Dari,112) AND " + Cf.Tgl112(TglKontrak) + " <= CONVERT(VARCHAR,Sampai,112)"
+ " AND Inaktif = 0"
+ w2
);
if (rsSkema.Rows.Count > 0)
{
Skema = rsSkema.Rows[0]["Nama"].ToString();
DasarHitung = rsSkema.Rows[0]["DasarHitung"].ToString();
}
else
{
del.Enabled = false;
alert.Text = "Skema belum Tersedia";
}
c = new TableCell();
c.Text = Skema;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
decimal Nilai = 0;
if (DasarHitung != "")
{
Nilai = DasarHitung == "DPP" ? Convert.ToDecimal(rs.Rows[i]["NilaiDPP"]) : Convert.ToDecimal(rs.Rows[i]["NilaiKontrak"]);
}
c = new TableCell();
c.Text = Cf.Num(Nilai);
c.CssClass = "right";
r.Cells.Add(c);
Rpt.Border(r);
list.Controls.Add(r);
index++;
del.Enabled = true;
}
}
}
