protected void Save()
{
if (valid())
{
DataTable rs = Db.Rs("SELECT * FROM SmsFormat WHERE Project = '" + project.SelectedValue + "' AND Tipe ='" + Tipe + "'");
if (rs.Rows.Count == 0)
{
string Project = Cf.Str(project.SelectedValue);
string Tipe2 = Cf.Str(tipe.Text);
string SatuanWaktu = satuan.SelectedValue;
int NilaiWaktu = Convert.ToInt32(waktu.Text);
string Format = Cf.Str(format.Text);
bool Inaktif = inaktif.SelectedIndex == 0 ? false : true;
Db.Execute("EXEC spSmsFormatDaftar"
+ " '" + Project + "'"
+ ",'" + Tipe2 + "'"
+ ",'" + SatuanWaktu + "'"
+ ",'" + NilaiWaktu + "'"
+ ",'" + Format + "'"
+ ",'" + Inaktif + "'"
);
DataTable rsHeader = Db.Rs("SELECT "
+ " Project"
+ ",Tipe"
+ ",SatuanWaktu"
+ ",NilaiWaktu"
+ ",Format"
+ ",Inaktif"
+ " FROM " + Mi.DbPrefix + "SECURITY..SmsFormat "
+ " WHERE Project = '" + project.SelectedValue + "'");
string Ket = Cf.LogCapture(rsHeader);
Db.Execute("EXEC spLogSmsFormat"
+ " 'DAFTAR'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Ket + "'"
+ ",'" + project.SelectedValue + "'"
);
}
else
{
string Tipe2 = Cf.Str(tipe.Text);
string SatuanWaktu = satuan.SelectedValue;
int NilaiWaktu = Convert.ToInt32(waktu.Text);
string Format = Cf.Str(format.Text);
bool Inaktif = inaktif.SelectedIndex == 0 ? false : true;
Db.Execute("EXEC spSmsFormatEdit"
+ " '" + Tipe2 + "'"
+ ",'" + SatuanWaktu + "'"
+ ",'" + NilaiWaktu + "'"
+ ",'" + Format + "'"
+ ",'" + Inaktif + "'"
);
//Db.Execute("UPDATE SmsSatuTitik SET Username = '******', Pass = '******', Masking = '" + Masking + "', Divisi = '" + Divisi +"' WHERE Username = '******'");
DataTable rsHeader = Db.Rs("SELECT "
+ " Project"
+ ",Tipe"
+ ",SatuanWaktu"
+ ",NilaiWaktu"
+ ",Format"
+ ",Inaktif"
+ " FROM " + Mi.DbPrefix + "SECURITY..SmsFormat "
+ " WHERE Project = '" + project.SelectedValue + "'");
string Ket = Cf.LogCapture(rsHeader);
Db.Execute("EXEC spLogSmsFormat"
+ " 'EDIT'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Ket + "'"
+ ",'" + project.SelectedValue + "'"
);
}
Response.Redirect("SmsFormat2.aspx?d=1&id=" + Tipe + "&project=" + project.SelectedValue);
}
}
private bool datavalid()
{
string s = "";
bool x = true;
if (!Cf.isTgl(tglajb))
{
x = false;
if (s == "")
{
s = tglajb.ID;
}
tglajbc.Text = "Tanggal";
}
else
{
tglajbc.Text = "";
}
if (!Cf.isMoney(nilaibiaya))
{
x = false;
if (s == "")
{
s = nilaibiaya.ID;
}
nilaibiayac.Text = "Angka";
}
else
{
nilaibiayac.Text = "";
}
decimal lunas = Db.SingleDecimal("SELECT PersenLunas FROM MS_KONTRAK WHERE NoKontrak ='" + NoKontrak + "'");
string Project = Db.SingleString("SELECT Project FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'");
string ParamID = "FormatAJB" + Project;
string a = Db.SingleString("SELECT Value FROM [ISC064_SECURITY].[dbo].[REF_PARAM] WHERE ParamID = '" + ParamID + "'");
decimal minajb = Convert.ToDecimal(a);
if (lunas < minajb)
{
x = false;
lunasinfo.Text = "PELUNASAN BELUM MENCAPAI " + minajb + "%";
}
else
{
lunasinfo.Text = "";
}
if (!x)
{
Js.Alert(
this
, "Input Tidak Valid.\\n\\n"
+ "Aturan Proses :\\n"
+ "1. Format Tanggal : Bulan / Tanggal / Tahun.\\n"
+ "2. Biaya Administrasi harus berupa angka.\\n"
+ "3. Pelunasan Belum Mencapai " + minajb + "%"
, "document.getElementById('" + s + "').focus();"
+ "document.getElementById('" + s + "').select();"
);
}
return(x);
}
private void Fill()
{
string strAdd = "";
if (lokasi.SelectedIndex > 0)
{
strAdd += " AND a.Lokasi = '" + lokasi.SelectedValue + "'";
}
string Project = " AND a.Project IN (" + Act.ProjectListSql + ")";
if (project.SelectedValue != "SEMUA")
{
Project = " AND a.Project = '" + project.SelectedValue + "'";
}
string Perusahaan = "";
if (pers.SelectedValue != "SEMUA")
{
Perusahaan = " AND a.Pers = '" + pers.SelectedValue + "'";
}
string strSql = "SELECT *, b.Nama AS NamaCustomer"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK a "
+ " INNER JOIN " + Mi.DbPrefix + "MARKETINGJUAL..MS_CUSTOMER b "
+ " ON a.NoCustomer = b.NoCustomer "
+ " WHERE 1=1 "
+ " AND a.Status = 'A' "
+ " AND CONVERT(varchar,TglKontrak,112) <= '" + Cf.Tgl112(Convert.ToDateTime(tgl.Text)) + "'"
+ Project
+ Perusahaan
+ strAdd
+ " ORDER BY a.NoUnit"
;
DataTable rs = Db.Rs(strSql);
decimal t1 = 0, t2 = 0, t3 = 0, t4 = 0, t5 = 0, t6 = 0, t7 = 0, t8 = 0, t9 = 0, t10 = 0, t11 = 0, t12 = 0, t13 = 0, t14 = 0;
decimal tHutangJangkaPendek = 0;
decimal tHutangJangkaPanjang = 0;
int index = 1;
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow tr = new TableRow();
TableCell c;
rpt.Rows.Add(tr);
c = new TableCell();
c.Text = (i + 1).ToString() + ".";
c.HorizontalAlign = HorizontalAlign.Center;
tr.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NamaCustomer"].ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
tr.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoUnit"].ToString();
t1++;
c.HorizontalAlign = HorizontalAlign.Left;
tr.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Convert.ToDecimal(rs.Rows[i]["NilaiDPP"]));
t2 += Convert.ToDecimal(rs.Rows[i]["NilaiDPP"]);
c.HorizontalAlign = HorizontalAlign.Right;
tr.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Convert.ToDecimal(rs.Rows[i]["NilaiPPN"]));
t3 += Convert.ToDecimal(rs.Rows[i]["NilaiPPN"]);
c.HorizontalAlign = HorizontalAlign.Right;
tr.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Convert.ToDecimal(rs.Rows[i]["NilaiKontrak"]));
t4 += Convert.ToDecimal(rs.Rows[i]["NilaiKontrak"]);
c.HorizontalAlign = HorizontalAlign.Right;
tr.Cells.Add(c);
decimal Pembayaran = 0;
if (include.Checked)
{
Pembayaran = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan),0) FROM "
+ Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN"
+ " WHERE CONVERT(varchar,TglPelunasan,112) <= '" + Cf.Tgl112(Convert.ToDateTime(tgl.Text)) + "'"
+ " AND NoKontrak='" + rs.Rows[i]["NoKontrak"] + "'");
}
if (exclude.Checked)
{
Pembayaran = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan),0) FROM "
+ Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN a"
+ " JOIN " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN b ON a.NoKontrak = b.NoKontrak AND a.NoTagihan = b.NoUrut "
+ " WHERE CONVERT(varchar,TglPelunasan,112) <= '" + Cf.Tgl112(Convert.ToDateTime(tgl.Text)) + "'"
+ " AND b.Tipe != 'ADM'"
+ " AND a.NoKontrak='" + rs.Rows[i]["NoKontrak"] + "'");
}
c = new TableCell();
c.Text = Cf.Num(Pembayaran);
t5 += Pembayaran;
c.HorizontalAlign = HorizontalAlign.Right;
tr.Cells.Add(c);
c = new TableCell();
decimal Piutang = 0;
if (include.Checked)
{
DataTable rs2 = Db.Rs("SELECT (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) "
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut "
+ " AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "')) As SisaTagihan"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN AS MS_TAGIHAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "'"
+ " AND (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut "
+ " AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) > 0 ");
for (int a = 0; a < rs2.Rows.Count; a++)
{
Piutang += Convert.ToDecimal(rs2.Rows[a]["SisaTagihan"]);
}
}
else
{
DataTable rs2 = Db.Rs("SELECT (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) "
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut "
+ " AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "')) As SisaTagihan"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN AS MS_TAGIHAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "'"
+ " AND Tipe != 'ADM'"
+ " AND (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut "
+ " AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) > 0 ");
for (int a = 0; a < rs2.Rows.Count; a++)
{
Piutang += Convert.ToDecimal(rs2.Rows[a]["SisaTagihan"]);
}
}
c.Text = Cf.Num(Piutang);
t6 += Piutang;
c.HorizontalAlign = HorizontalAlign.Right;
tr.Cells.Add(c);
decimal BiayaAdmin = 0;
if (include.Checked)
{
tradmin.Visible = true;
BiayaAdmin = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiTagihan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND Tipe = 'ADM'");
c = new TableCell();
c.Text = Cf.Num(BiayaAdmin);
c.HorizontalAlign = HorizontalAlign.Right;
tr.Cells.Add(c);
}
t14 += BiayaAdmin;
c = new TableCell();
decimal BelumJatuhTempo = 0;
if (include.Checked)
{
DataTable rs3 = Db.Rs("SELECT (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) AS SisaTagihan"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN AS MS_TAGIHAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "'"
+ " AND (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) > 0 "
+ " AND TglJT> '" + Cf.Tgl112(Convert.ToDateTime(tgl.Text)) + "' ORDER BY TglJT, NoUrut");
for (int a = 0; a < rs3.Rows.Count; a++)
{
BelumJatuhTempo += Convert.ToDecimal(rs3.Rows[a]["SisaTagihan"]);
}
}
else
{
DataTable rs3 = Db.Rs("SELECT (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) AS SisaTagihan"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN AS MS_TAGIHAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND Tipe != 'ADM'"
+ " AND (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) > 0 "
+ " AND TglJT> '" + Cf.Tgl112(Convert.ToDateTime(tgl.Text)) + "' ORDER BY TglJT, NoUrut");
for (int a = 0; a < rs3.Rows.Count; a++)
{
BelumJatuhTempo += Convert.ToDecimal(rs3.Rows[a]["SisaTagihan"]);
}
}
t7 += BelumJatuhTempo;
c.Text = Cf.Num(Math.Round(BelumJatuhTempo, 0));
c.HorizontalAlign = HorizontalAlign.Right;
tr.Cells.Add(c);
c = new TableCell();
decimal satu = 0;
if (include.Checked)
{
DataTable rs4 = Db.Rs("SELECT (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) AS SisaTagihan"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN AS MS_TAGIHAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) > 0"
+ " AND DATEDIFF(day,TglJT,'" + Cf.Tgl112(Convert.ToDateTime(tgl.Text)) + "') between 0 and 30 ORDER BY TglJT, NoUrut");
for (int a = 0; a < rs4.Rows.Count; a++)
{
satu += Convert.ToDecimal(rs4.Rows[a]["SisaTagihan"]);
}
}
else
{
DataTable rs4 = Db.Rs("SELECT (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) AS SisaTagihan"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN AS MS_TAGIHAN WHERE Tipe != 'ADM' AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) > 0"
+ " AND DATEDIFF(day,TglJT,'" + Cf.Tgl112(Convert.ToDateTime(tgl.Text)) + "') between 0 and 30 ORDER BY TglJT, NoUrut");
for (int a = 0; a < rs4.Rows.Count; a++)
{
satu += Convert.ToDecimal(rs4.Rows[a]["SisaTagihan"]);
}
}
t8 += satu;
c.Text = Cf.Num(Math.Round(satu, 0));
c.HorizontalAlign = HorizontalAlign.Right;
tr.Cells.Add(c);
c = new TableCell();
decimal dua = 0;
if (include.Checked)
{
DataTable rs5 = Db.Rs("SELECT (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) AS SisaTagihan"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN AS MS_TAGIHAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) > 0"
+ " AND DATEDIFF(day,TglJT,'" + Cf.Tgl112(Convert.ToDateTime(tgl.Text)) + "') between 31 and 60 ORDER BY TglJT, NoUrut");
for (int a = 0; a < rs5.Rows.Count; a++)
{
dua += Convert.ToDecimal(rs5.Rows[a]["SisaTagihan"]);
}
}
else
{
DataTable rs5 = Db.Rs("SELECT (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) AS SisaTagihan"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN AS MS_TAGIHAN WHERE Tipe != 'ADM' AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) > 0"
+ " AND DATEDIFF(day,TglJT,'" + Cf.Tgl112(Convert.ToDateTime(tgl.Text)) + "') between 31 and 60 ORDER BY TglJT, NoUrut");
for (int a = 0; a < rs5.Rows.Count; a++)
{
dua += Convert.ToDecimal(rs5.Rows[a]["SisaTagihan"]);
}
}
t9 += dua;
c.Text = Cf.Num(Math.Round(dua, 0));
c.HorizontalAlign = HorizontalAlign.Right;
tr.Cells.Add(c);
c = new TableCell();
decimal tiga = 0;
if (include.Checked)
{
DataTable rs6 = Db.Rs("SELECT (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) AS SisaTagihan"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN AS MS_TAGIHAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) > 0"
+ " AND DATEDIFF(day,TglJT,'" + Cf.Tgl112(Convert.ToDateTime(tgl.Text)) + "') between 61 and 90 ORDER BY TglJT, NoUrut");
for (int a = 0; a < rs6.Rows.Count; a++)
{
tiga += Convert.ToDecimal(rs6.Rows[a]["SisaTagihan"]);
}
}
else
{
DataTable rs6 = Db.Rs("SELECT (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) AS SisaTagihan"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN AS MS_TAGIHAN WHERE Tipe != 'ADM' AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) > 0"
+ " AND DATEDIFF(day,TglJT,'" + Cf.Tgl112(Convert.ToDateTime(tgl.Text)) + "') between 61 and 90 ORDER BY TglJT, NoUrut");
for (int a = 0; a < rs6.Rows.Count; a++)
{
tiga += Convert.ToDecimal(rs6.Rows[a]["SisaTagihan"]);
}
}
t10 += tiga;
c.Text = Cf.Num(Math.Round(tiga, 0));
c.HorizontalAlign = HorizontalAlign.Right;
tr.Cells.Add(c);
c = new TableCell();
decimal empat = 0;
if (include.Checked)
{
DataTable rs7 = Db.Rs("SELECT (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) AS SisaTagihan"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN AS MS_TAGIHAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) > 0"
+ " AND DATEDIFF(day,TglJT,'" + Cf.Tgl112(Convert.ToDateTime(tgl.Text)) + "') between 91 and 120 ORDER BY TglJT, NoUrut");
for (int a = 0; a < rs7.Rows.Count; a++)
{
empat += Convert.ToDecimal(rs7.Rows[a]["SisaTagihan"]);
}
}
else
{
DataTable rs7 = Db.Rs("SELECT (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) AS SisaTagihan"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN AS MS_TAGIHAN WHERE Tipe != 'ADM' AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) > 0"
+ " AND DATEDIFF(day,TglJT,'" + Cf.Tgl112(Convert.ToDateTime(tgl.Text)) + "') between 91 and 120 ORDER BY TglJT, NoUrut");
for (int a = 0; a < rs7.Rows.Count; a++)
{
empat += Convert.ToDecimal(rs7.Rows[a]["SisaTagihan"]);
}
}
t11 += empat;
c.Text = Cf.Num(Math.Round(empat, 0));
c.HorizontalAlign = HorizontalAlign.Right;
tr.Cells.Add(c);
c = new TableCell();
decimal lima = 0;
if (include.Checked)
{
DataTable rs8 = Db.Rs("SELECT (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) AS SisaTagihan"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN AS MS_TAGIHAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) > 0"
+ " AND DATEDIFF(day,TglJT,'" + Cf.Tgl112(Convert.ToDateTime(tgl.Text)) + "') between 121 and 180 ORDER BY TglJT, NoUrut");
for (int a = 0; a < rs8.Rows.Count; a++)
{
lima += Convert.ToDecimal(rs8.Rows[a]["SisaTagihan"]);
}
}
else
{
DataTable rs8 = Db.Rs("SELECT (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) AS SisaTagihan"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN AS MS_TAGIHAN WHERE Tipe != 'ADM' AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) > 0"
+ " AND DATEDIFF(day,TglJT,'" + Cf.Tgl112(Convert.ToDateTime(tgl.Text)) + "') between 121 and 180 ORDER BY TglJT, NoUrut");
for (int a = 0; a < rs8.Rows.Count; a++)
{
lima += Convert.ToDecimal(rs8.Rows[a]["SisaTagihan"]);
}
}
t12 += lima;
c.Text = Cf.Num(Math.Round(lima, 0));
c.HorizontalAlign = HorizontalAlign.Right;
tr.Cells.Add(c);
c = new TableCell();
decimal enam = 0;
if (include.Checked)
{
DataTable rs9 = Db.Rs("SELECT (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) AS SisaTagihan"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN AS MS_TAGIHAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) > 0"
+ " AND DATEDIFF(day,TglJT,'" + Cf.Tgl112(Convert.ToDateTime(tgl.Text)) + "')>=181 ORDER BY TglJT, NoUrut");
for (int a = 0; a < rs9.Rows.Count; a++)
{
enam += Convert.ToDecimal(rs9.Rows[a]["SisaTagihan"]);
}
}
else
{
DataTable rs9 = Db.Rs("SELECT (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) AS SisaTagihan"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN AS MS_TAGIHAN WHERE Tipe != 'ADM' AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "') ) > 0"
+ " AND DATEDIFF(day,TglJT,'" + Cf.Tgl112(Convert.ToDateTime(tgl.Text)) + "')>=181 ORDER BY TglJT, NoUrut");
for (int a = 0; a < rs9.Rows.Count; a++)
{
enam += Convert.ToDecimal(rs9.Rows[a]["SisaTagihan"]);
}
}
t13 += enam;
c.Text = Cf.Num(Math.Round(enam, 0));
c.HorizontalAlign = HorizontalAlign.Right;
tr.Cells.Add(c);
decimal HutangJangkaPendek = satu + dua + tiga + empat + lima + enam;
tHutangJangkaPendek += HutangJangkaPendek;
c = new TableCell();
c.Text = Cf.Num(HutangJangkaPendek);
c.HorizontalAlign = HorizontalAlign.Right;
tr.Cells.Add(c);
decimal HutangJangkaPanjang = BelumJatuhTempo;
tHutangJangkaPanjang += HutangJangkaPanjang;
c = new TableCell();
c.Text = Cf.Num(Math.Round(HutangJangkaPanjang, 0));
c.HorizontalAlign = HorizontalAlign.Right;
tr.Cells.Add(c);
}
Total(t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14, tHutangJangkaPendek, tHutangJangkaPanjang);
}
private bool Save()
{
DataTable rsBef = Db.Rs("SELECT "
+ " CONVERT(VARCHAR,NoUrut) + '. ' + CaraBayar + ' ' + Ket + ' ' + CONVERT(VARCHAR,TglPelunasan,106) + ' ' + CONVERT(VARCHAR,NilaiPelunasan,1) + ' Tagihan : ' + CONVERT(VARCHAR,NoTagihan)"
+ " FROM MS_PELUNASAN WHERE NoKontrak = '" + NoKontrak + "' ORDER BY NoUrut");
for (int i = 0; i < rs.Rows.Count; i++)
{
DropDownList notagihan = (DropDownList)list.FindControl("notagihan_" + i);
int NoUrut = Convert.ToInt32(rs.Rows[i]["NoUrut"]);
int NoTagihan = Convert.ToInt32(notagihan.SelectedValue);
string strSql = "SELECT"
+ " CaraBayar AS [Cara Bayar]"
+ ", Ket AS [Keterangan]"
+ ", TglPelunasan AS [Tgl. Pelunasan]"
+ ", NilaiPelunasan AS [Nilai]"
+ ", NoTagihan AS [Tagihan]"
+ " FROM MS_PELUNASAN"
+ " WHERE NoKontrak = '" + NoKontrak + "'"
+ " AND NoUrut = " + NoUrut
;
DataTable AnomaliBef = Db.Rs(strSql);
Db.Execute("EXEC spPelunasanEdit "
+ " '" + NoKontrak + "'"
+ ", " + NoUrut
+ ", " + NoTagihan
);
if (NoTagihan != Convert.ToInt32(rs.Rows[i]["NoTagihan"]))
{
int Akunting = Db.SingleInteger("SELECT Akunting FROM " + Mi.DbPrefix + "FINANCEAR..MS_TTS WHERE NoTTS = " + Cf.Pk(rs.Rows[i]["NoTTS"]));
if (Akunting == 1)
{
DataTable AnomaliAft = Db.Rs(strSql);
string NoVoucher = Db.SingleString("SELECT NoVoucher FROM " + Mi.DbPrefix + "FINANCEAR..MS_TTS WHERE NoTTS = '" + Cf.Pk(rs.Rows[i]["NoTTS"]) + "'");
Akun.InsertAnomali("TTS", Cf.Pk(rs.Rows[i]["NoTTS"]), Cf.LogCapture(AnomaliBef), Cf.LogCapture(AnomaliAft), "EDIT ALOKASI PELUNASAN", "", NoVoucher);
}
}
}
DataTable rsAft = Db.Rs("SELECT "
+ " CONVERT(VARCHAR,NoUrut) + '. ' + CaraBayar + ' ' + Ket + ' ' + CONVERT(VARCHAR,TglPelunasan,106) + ' ' + CONVERT(VARCHAR,NilaiPelunasan,1) + ' Tagihan : ' + CONVERT(VARCHAR,NoTagihan)"
+ " FROM MS_PELUNASAN WHERE NoKontrak = '" + NoKontrak + "' ORDER BY NoUrut");
DataTable rsDetail = Db.Rs("SELECT"
+ " MS_KONTRAK.NoKontrak AS [No. Kontrak]"
+ ",MS_KONTRAK.NoUnit AS [Unit]"
+ ",MS_CUSTOMER.Nama AS [Customer]"
+ " FROM MS_KONTRAK AS MS_KONTRAK INNER JOIN MS_CUSTOMER AS MS_CUSTOMER"
+ " ON MS_KONTRAK.NoCustomer = MS_CUSTOMER.NoCustomer"
+ " WHERE MS_KONTRAK.NoKontrak = '" + NoKontrak + "'");
string KetLog = Cf.LogCapture(rsDetail)
+ Cf.LogList(rsBef, rsAft, "ALOKASI PELUNASAN");
Db.Execute("EXEC spLogKontrak"
+ " 'EAP'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + KetLog + "'"
+ ",'" + NoKontrak + "'"
);
decimal LogID = Db.SingleDecimal("SELECT TOP 1 LogID FROM MS_KONTRAK_LOG ORDER BY LogID DESC");
string Project = Db.SingleString("SELECT Project FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'");
Db.Execute("UPDATE MS_KONTRAK_LOG SET Project = '" + Project + "' WHERE LogID = " + LogID);
return(true);
}
private void Fill()
{
string strSql = "SELECT"
+ " NoKontrak"
+ ",NoUnit"
+ ",MS_CUSTOMER.Nama AS Cs"
+ ",MS_AGENT.Nama + ' ' + MS_AGENT.Principal AS Ag"
+ ",TargetST"
+ ",PersenLunas"
+ " FROM MS_KONTRAK INNER JOIN MS_CUSTOMER"
+ " ON MS_KONTRAK.NoCustomer = MS_CUSTOMER.NoCustomer"
+ " INNER JOIN MS_AGENT"
+ " ON MS_KONTRAK.NoAgent = MS_AGENT.NoAgent"
+ " WHERE 1=1"
+ " AND MS_KONTRAK.ST = 'T' AND MS_KONTRAK.Status = 'A'"
+ " ORDER BY MS_KONTRAK.NoKontrak";
DataTable rs = Db.Rs(strSql);
Rpt.NoData(rpt, rs, "Reminder untuk topik diatas masih kosong.");
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
c = new TableCell();
c.Text = "<a href=\"KontrakST.aspx?NoKontrak=" + rs.Rows[i]["NoKontrak"] + "\">"
+ rs.Rows[i]["NoKontrak"].ToString() + "</a>";
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoUnit"].ToString();
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Cs"].ToString();
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Ag"].ToString();
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TargetST"]);
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["PersenLunas"]) + "%";
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
Rpt.Border(r);
rpt.Rows.Add(r);
}
}
protected void GrandTotal(decimal t1, decimal t2, decimal t3, decimal t4, decimal t5, decimal t6)
{
TableRow r = new TableRow();
TableCell c;
c = Rpt.Foot();
c.Text = "GRAND TOTAL";
c.ColumnSpan = 3;
r.Cells.Add(c);
c = Rpt.Foot();
c.Text = Cf.Num(t1);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = Rpt.Foot();
c.Text = " ";
r.Cells.Add(c);
c = Rpt.Foot();
c.Text = Cf.Num(t2);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = Rpt.Foot();
c.Text = " ";
r.Cells.Add(c);
c = Rpt.Foot();
c.Text = Cf.Num(t3);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = Rpt.Foot();
c.Text = " ";
r.Cells.Add(c);
c = Rpt.Foot();
c.Text = Cf.Num(t4);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = Rpt.Foot();
c.Text = " ";
r.Cells.Add(c);
c = Rpt.Foot();
c.Text = Cf.Num(t5);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = Rpt.Foot();
c.Text = " ";
r.Cells.Add(c);
c = Rpt.Foot();
c.Text = Cf.Num(t6);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
rpt.Rows.Add(r);
}
protected void display_Click(object sender, System.EventArgs e)
{
Cf.SetGrid(tb);
Fill();
}
private bool valid()
{
string s = "";
bool x = true;
//nama
if (Cf.isEmpty(nama))
{
x = false;
if (s == "")
{
s = nama.ID;
}
namac.Text = "Kosong";
}
else
{
namac.Text = "";
}
if (jenis.SelectedIndex == -1)
{
x = false;
if (s == "")
{
s = jenis.ID;
}
carabayarc.Text = "Pilih salah satu jenis";
}
else
{
carabayarc.Text = "";
}
//bf nominal
if (!Cf.isInt(bfkali))
{
x = false;
if (s == "")
{
s = bfkali.ID;
}
bfc.Text = "Angka";
}
else if (Convert.ToInt32(bfkali.Text) < 0)
{
x = false;
if (s == "")
{
s = bfkali.ID;
}
bfc.Text = "Positif";
}
else if (!Cf.isMoney(bfjumlah))
{
x = false;
if (s == "")
{
s = bfjumlah.ID;
}
bfc.Text = "Angka";
}
else
{
bfc.Text = "";
}
//dp nominal
if (!Cf.isInt(dpkali))
{
x = false;
if (s == "")
{
s = dpkali.ID;
}
dpc.Text = "Angka";
}
else if (Convert.ToInt32(dpkali.Text) < 0)
{
x = false;
if (s == "")
{
s = dpkali.ID;
}
dpc.Text = "Positif";
}
else if (!Cf.isMoney(dpjumlah))
{
x = false;
if (s == "")
{
s = dpjumlah.ID;
}
dpc.Text = "Angka";
}
else
{
dpc.Text = "";
}
//ang nominal
if (!Cf.isInt(angkali))
{
x = false;
if (s == "")
{
s = angkali.ID;
}
angc.Text = "Angka";
}
else if (Convert.ToInt32(angkali.Text) < 0)
{
x = false;
if (s == "")
{
s = angkali.ID;
}
angc.Text = "Positif";
}
else if (!Cf.isMoney(angjumlah))
{
x = false;
if (s == "")
{
s = angjumlah.ID;
}
angc.Text = "Angka";
}
else
{
angc.Text = "";
}
//bf jadwal
if (!Cf.isInt(bflama1))
{
x = false;
if (s == "")
{
s = bflama1.ID;
}
bf2c.Text = "Angka";
}
else if (Convert.ToInt32(bflama1.Text) < 0)
{
x = false;
if (s == "")
{
s = bflama1.ID;
}
bf2c.Text = "Positif";
}
else if (!Cf.isInt(bflama2))
{
x = false;
if (s == "")
{
s = bflama2.ID;
}
bf2c.Text = "Angka";
}
else if (Convert.ToInt32(bflama2.Text) < 0)
{
x = false;
if (s == "")
{
s = bflama2.ID;
}
bf2c.Text = "Positif";
}
else
{
bf2c.Text = "";
}
//dp jadwal
if (!Cf.isInt(dplama1))
{
x = false;
if (s == "")
{
s = dplama1.ID;
}
dp2c.Text = "Angka";
}
else if (Convert.ToInt32(dplama1.Text) < 0)
{
x = false;
if (s == "")
{
s = dplama1.ID;
}
dp2c.Text = "Positif";
}
else if (!Cf.isInt(dplama2))
{
x = false;
if (s == "")
{
s = dplama2.ID;
}
dp2c.Text = "Angka";
}
else if (Convert.ToInt32(dplama2.Text) < 0)
{
x = false;
if (s == "")
{
s = dplama2.ID;
}
dp2c.Text = "Positif";
}
else
{
dp2c.Text = "";
}
//ang jadwal
if (!Cf.isInt(anglama1))
{
x = false;
if (s == "")
{
s = anglama1.ID;
}
ang2c.Text = "Angka";
}
else if (Convert.ToInt32(anglama1.Text) < 0)
{
x = false;
if (s == "")
{
s = anglama1.ID;
}
ang2c.Text = "Positif";
}
else if (!Cf.isInt(anglama2))
{
x = false;
if (s == "")
{
s = anglama2.ID;
}
ang2c.Text = "Angka";
}
else if (Convert.ToInt32(anglama2.Text) < 0)
{
x = false;
if (s == "")
{
s = anglama2.ID;
}
ang2c.Text = "Positif";
}
else
{
ang2c.Text = "";
}
if (x)
{
if ((dp1potong.Checked || dpspotong.Checked) && (Convert.ToInt32(dpkali.Text) == 0))
{
x = false;
if (s == "")
{
s = dpkali.ID;
}
cc.Text = "DP 0 kali";
}
else if ((ang1potong.Checked || angspotong.Checked) && (Convert.ToInt32(angkali.Text) == 0))
{
x = false;
if (s == "")
{
s = angkali.ID;
}
cc.Text = "Angsuran 0 kali";
}
else
{
cc.Text = "";
}
}
if (!x)
{
ClientScript.RegisterStartupScript(
GetType()
, "focusScript"
, "<script type='text/javascript'>"
+ " document.getElementById('" + s + "').focus();"
+ " document.getElementById('" + s + "').select();"
+ "</script>"
);
}
return(x);
}
private void SaveBaris(int Nomor, string Tipe, string Nama, TextBox kali, CheckBox rp, TextBox nom, CheckBox bln1, CheckBox bln2, TextBox interval1, TextBox interval2)
{
int count = Convert.ToInt32(kali.Text);
int index = Db.SingleInteger("SELECT ISNULL(MAX(Baris),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..REF_SKEMA_DETAIL"
+ " WHERE Nomor = " + Nomor);
int topref = index;
for (int i = 0; i < count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
index++;
decimal Nominal = Convert.ToDecimal(nom.Text) / count;
string TipeNominal = "%";
if (rp.Checked)
{
TipeNominal = "F";
}
string TipeJadwal = "";
int IntJadwal = 0;
int RefJadwal = 0;
if (i != 0)
{
//interval
if (bln1.Checked)
{
TipeJadwal = "M";
}
else
{
TipeJadwal = "D";
}
IntJadwal = Convert.ToInt32(interval1.Text) * i;
RefJadwal = topref + 1;
}
else
{
//pertama
if (bln2.Checked)
{
TipeJadwal = "M";
}
else
{
TipeJadwal = "D";
}
IntJadwal = Convert.ToInt32(interval2.Text);
RefJadwal = topref;
}
bool BF = false;
if (Tipe == "DP")
{
if (dp1potong.Checked && i == 0)
{
BF = true;
}
if (dpspotong.Checked)
{
BF = true;
}
}
if (Tipe == "ANG")
{
if (ang1potong.Checked && i == 0)
{
BF = true;
}
if (angspotong.Checked)
{
BF = true;
}
}
if ((Tipe == "ANG") && (i == count - 1))
{
Db.Execute("EXEC " + Mi.DbPrefix + "MARKETINGJUAL..spSkemaTambah "
+ Nomor
+ ",'" + Tipe + "'"
+ ",'PELUNASAN'"
+ ", " + Nominal
+ ",'" + TipeNominal + "'"
+ ",NULL"
+ ",'" + TipeJadwal + "'"
+ ", " + IntJadwal
+ ", " + RefJadwal
+ ", " + Cf.BoolToSql(BF)
);
string jenis = Db.SingleString("SELECT JENIS FROM " + Mi.DbPrefix + "MARKETINGJUAL..REF_SKEMA WHERE NOMOR=" + Nomor);
if (jenis == "KPR")
{
Db.Execute("UPDATE " + Mi.DbPrefix + "MARKETINGJUAL..REF_SKEMA_DETAIL SET KPR = 1 WHERE NOMOR=" + Nomor + " AND NAMA='PELUNASAN'");
}
}
else
{
Db.Execute("EXEC " + Mi.DbPrefix + "MARKETINGJUAL..spSkemaTambah "
+ Nomor
+ ",'" + Tipe + "'"
+ ",'" + Nama + " " + (i + 1) + "'"
+ ", " + Nominal
+ ",'" + TipeNominal + "'"
+ ",NULL"
+ ",'" + TipeJadwal + "'"
+ ", " + IntJadwal
+ ", " + RefJadwal
+ ", " + Cf.BoolToSql(BF)
);
}
}
}
private void Fill()
{
DateTime Dari = Convert.ToDateTime(dari.Text);
DateTime Sampai = Convert.ToDateTime(sampai.Text);
if (Dari > Sampai)
{
DateTime x = Sampai;
Sampai = Dari;
Dari = x;
}
string UserID = "";
if (user.SelectedIndex != 0)
{
UserID = " AND UserID = '" + user.SelectedValue + "'";
}
string SecLevel = "";
if (seclevel.SelectedIndex != 0)
{
SecLevel = " AND SecLevel = '" + seclevel.SelectedValue + "'";
}
string IP = "";
if (ip.SelectedIndex != 0)
{
IP = " AND IP = '" + ip.SelectedValue + "'";
}
string strSql = "SELECT "
+ " LogID"
+ ",TglLogin"
+ ",TglLogout"
+ ",UserID"
+ ",Nama"
+ ",SecLevel"
+ ",IP"
+ " FROM LOGIN"
+ " WHERE 1=1 "
+ " AND CONVERT(varchar,TglLogin,112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(varchar,TglLogin,112) <= '" + Cf.Tgl112(Sampai) + "'"
+ UserID
+ SecLevel
+ IP
+ " ORDER BY LogID";
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;
c = new TableCell();
c.Text = rs.Rows[i]["LogID"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Date(rs.Rows[i]["TglLogin"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Date(rs.Rows[i]["TglLogout"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["UserID"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Nama"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["SecLevel"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["IP"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
rpt.Rows.Add(r);
}
}
private void FillTb()
{
string strSql = "SELECT "
+ " NamaTagihan"
+ ",TglJT"
+ ",NilaiTagihan"
+ ",NoUrut"
+ ",Tipe"
+ " FROM ISC064_MARKETINGJUAL..MS_RESERVASI_TAGIHAN"
+ " WHERE NoReservasi = '" + NoReservasi + "'"
+ " ORDER BY NoUrut";
DataTable rs = Db.Rs(strSql);
Rpt.NoData(rpt, rs, "Daftar tagihan untuk kontrak tersebut masih kosong.");
decimal t1 = 0;
decimal t2 = 0;
decimal t3 = 0;
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
t1 = t1 + (decimal)rs.Rows[i]["NilaiTagihan"];
//t2 = t2 + (decimal)rs.Rows[i]["SisaTagihan"];
c = new TableCell();
c.Text = NoReservasi.PadLeft(5, '0') + "." + rs.Rows[i]["NoUrut"];
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Tipe"].ToString();
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NamaTagihan"].ToString();
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglJT"]);
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["NilaiTagihan"]);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = "";
r.Cells.Add(c);
//c = new TableCell();
//c.Text = Cf.Num(rs.Rows[i]["SisaTagihan"]);
//c.HorizontalAlign = HorizontalAlign.Right;
//r.Cells.Add(c);
// c = new TableCell();
// c.Text = "<a href='PrintINV.aspx?NoKontrak=" + NoKontrak + "&NoUrut=" + rs.Rows[i]["NoUrut"] + "'>Invoice...</a>";
// r.Cells.Add(c);
Rpt.Border(r);
rpt.Rows.Add(r);
//t3 = t3 + Lunas((int)rs.Rows[i]["NoUrut"]);
if (i == rs.Rows.Count - 1)
{
SubTotal(t1, t2, t3);
}
}
// Lunas(0);
}
protected void pdf_Click(object sender, System.EventArgs e)
{
Process p = new System.Diagnostics.Process();
string Nama = "Laporan Tabel Absensi";
string Link = "";
DateTime TglGenerate = DateTime.Now;
string FileName = "";
string FileType = "application/pdf";
string UserID = Act.UserID;
string IP = Act.IP;
Db.Execute("EXEC spLapPDFDaftar"
// + " '" + Convert.ToString(007) + "'"
+ " '" + Nama + "'"
+ ",'" + Link + "'"
+ ",'" + TglGenerate + "'"
+ ",'" + IP + "'"
+ ",'" + UserID + "'"
+ ",'" + FileName + "'"
+ ",'" + FileType + "'"
+ ",'" + Cf.Date(dari.Text) + "'"
+ ",'" + Cf.Date(sampai.Text) + "'"
);
//get nomor customer terbaru
int NoAttachment = Db.SingleInteger(
"SELECT TOP 1 AttachmentID FROM LapPDF ORDER BY AttachmentID DESC");
string strSql = "SELECT * FROM ISC064_SECURITY..LapPDF WHERE AttachmentID = '" + NoAttachment + "'";
DataTable rs = Db.Rs(strSql);
string nfilename = "LapTabelAbsensi" + NoAttachment + ".pdf";
//update filename
Db.Execute("UPDATE ISC064_SECURITY..LapPDF SET FileName= '" + nfilename + "' WHERE AttachmentID = " + NoAttachment);
//folder untuk menyimpan file pdf
string save = Mi.PathFilePDFReport + "LapTabelAbsensi" + rs.Rows[0]["AttachmentID"] + ".pdf";
//declare parameter
string SecLevel = seclevel.SelectedValue;
//link untuk download pdf
string link = Mi.PathAlamatWeb + "security/LaporanPDF/PDFTabelAbsensi.aspx?id=" + rs.Rows[0]["AttachmentID"] + "&user="******"&ip=" + ip.SelectedValue + "&seclevel=" + SecLevel + "";
//update link
Db.Execute("UPDATE ISC064_SECURITY..LapPDF SET Link= '" + link + "' WHERE AttachmentID = " + NoAttachment);
//format page
p.StartInfo.Arguments = "--orientation landscape --page-width 8.5in --page-height 11in --margin-left 0 --margin-right 0 --margin-top 0.25cm --margin-bottom 0 " + link + " " + save;
//panggil aplikasi untuk mengconvert pdf
p.StartInfo.FileName = Mi.PathWkhtmlPDFReport;
p.Start();
//60000 -> waktu jeda lama convert pdf
p.WaitForExit(60000);
string Src = Mi.PathFilePDFReport + nfilename;
Mi.DownloadPDF(this, Src, (rs.Rows[0]["FileName"]).ToString(), rs.Rows[0]["FileType"].ToString());
}
private void Fill()
{
if (validPrint())
{
divPrint.Visible = true;
divInfo.Visible = false;
string strSql = "SELECT * FROM MS_NUP WHERE MS_NUP.NoNUP= '" + nomor + "'";
DataTable rs = Db.Rs(strSql);
if (rs.Rows.Count != 0)
{
decimal bayaran = 0;
string strSql2 = "SELECT * FROM MS_NUP_PELUNASAN WHERE NoNUP= '" + nomor + "'";
DataTable rs2 = Db.Rs(strSql2);
if (rs2.Rows.Count != 0)
{
bayaran = Convert.ToDecimal(rs2.Rows[0]["Total"]);
switch (rs2.Rows[0]["CaraBayar"].ToString())
{
case "TR":
cabarTrf.Text = "X";
cabarTunai.Text = cabarCC.Text = cabarLainnya.Text = "";
break;
case "TN":
cabarTunai.Text = "X";
cabarCC.Text = cabarTrf.Text = cabarLainnya.Text = "";
break;
case "KK":
cabarCC.Text = "X";
cabarTunai.Text = cabarTrf.Text = cabarLainnya.Text = "";
break;
default:
cabarLainnya.Text = "X";
cabarTunai.Text = cabarCC.Text = cabarTrf.Text = "";
break;
}
}
QRImage.Src = "QR\\" + nomor + ".jpg";
QRImage.Width = 100;
QRImage.Height = 100;
QRImage.Attributes["style"] = "left:243";
nbayar.Text = Cf.Num(bayaran) + " ";
nbayar.Attributes["style"] = "border-bottom:1px solid black";
if (bayaran != 0)
{
nterbilang.Text = Money.Str(bayaran) + " RUPIAH";
}
nterbilang.Attributes["style"] = "border-bottom:1px solid black";
pemesan.Text = rs.Rows[0]["NamaCustomer"].ToString();
noktp.Text = rs.Rows[0]["NoKTP"].ToString();
npwp.Text = rs.Rows[0]["NoNPWP"].ToString();
email.Text = rs.Rows[0]["Email"].ToString();
korespon1.Text = rs.Rows[0]["Alamat1"].ToString() + " " + rs.Rows[0]["Alamat2"].ToString();
korespon2.Text = rs.Rows[0]["Alamat3"].ToString() + " " + rs.Rows[0]["Alamat4"].ToString();
notelp.Text = rs.Rows[0]["NoTelp"].ToString();
nohp.Text = rs.Rows[0]["NoHP"].ToString();
bank.Text = rs.Rows[0]["RekBank"].ToString();
norek.Text = rs.Rows[0]["RekNomor"].ToString();
string refEv = Db.SingleString("SELECT Event FROM REF_EVENT WHERE SN = " + rs.Rows[0]["SumberData"].ToString());
string nNup = refEv + "-" + rs.Rows[0]["NoNUP"].ToString();
nonup.Text = nNup;
Db.Execute("UPDATE MS_NUP SET PrintNUP = PrintNUP + 1, TglPrint = '" + Cf.Tgl112(DateTime.Now) + "' WHERE NoNUP = '" + nomor + "'");
}
}
else
{
divPrint.Visible = false;
divInfo.Visible = true;
}
}
private void Fill()
{
string Lokasi = "";
if (lokasi.SelectedIndex > 0)
{
Lokasi += " AND Lokasi = '" + lokasi.SelectedValue + "'";
}
string tgl = "";
if (tbBerkas.Checked)
{
tgl = "TglSelesaiBerkas";
}
if (tbKontrak.Checked)
{
tgl = "TglKontrak";
}
DateTime Dari = Convert.ToDateTime(dari.Text);
DateTime Sampai = Convert.ToDateTime(sampai.Text);
if (Dari > Sampai)
{
DateTime x = Sampai;
Sampai = Dari;
Dari = x;
}
string Status = "";
if (ddlStatus.SelectedIndex != 0)
{
Status = " AND StatusBerkas = '" + ddlStatus.SelectedValue + "'";
}
string Tanggal = " AND CONVERT(varchar," + tgl + ",112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(varchar," + tgl + ",112) <= '" + Cf.Tgl112(Sampai) + "'";
string Project = " AND a.Project IN (" + Act.ProjectListSql + ")";
if (project.SelectedValue != "SEMUA")
{
Project = " AND a.Project = '" + project.SelectedValue + "'";
}
string Perusahaan = "";
if (pers.SelectedValue != "SEMUA")
{
Perusahaan = " AND a.Pers = '" + pers.SelectedValue + "'";
}
string strSql = "SELECT a.*, b.Nama AS NamaCustomer"
+ " FROM MS_KONTRAK a"
+ " INNER JOIN MS_CUSTOMER b ON a.NoCustomer = b.NoCustomer"
+ " WHERE a.Status = 'A'"
+ " AND CaraBayar = 'KPR'"
+ Project
+ Perusahaan
+ Lokasi
+ Tanggal
+ Status
;
DataTable rs = Db.Rs(strSql);
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.VerticalAlign = VerticalAlign.Top;
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoKontrak"].ToString();
c.VerticalAlign = VerticalAlign.Top;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NamaCustomer"].ToString();
c.VerticalAlign = VerticalAlign.Top;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoUnit"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["BankKPR"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
string Berkas = "";
if (Convert.ToBoolean(rs.Rows[i]["StatusBerkas"]) == false)
{
Berkas = "BELUM LENGKAP";
}
else
{
Berkas = "SUDAH LENGKAP";
}
c.Text = Berkas;
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglSelesaiBerkas"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["CheckListDokumen"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
rpt.Rows.Add(r);
}
}
private void Fill()
{
string strAdd = "";
if (lokasi.SelectedIndex > 0)
{
strAdd += " AND a.Lokasi = '" + lokasi.SelectedValue + "'";
}
if (ddlAgent.SelectedIndex != 0)
{
strAdd = " AND a.NoAgent = " + Cf.Pk(ddlAgent.SelectedValue);
}
System.Text.StringBuilder z = new System.Text.StringBuilder();
bool isFirst = true;
for (int i = 0; i < cblPrincipal.Items.Count; i++)
{
if (cblPrincipal.Items[i].Selected)
{
if (isFirst)
{
z.Append("'" + Cf.Str(cblPrincipal.Items[i].Text) + "'");
isFirst = false;
}
else
{
z.Append(",'" + Cf.Str(cblPrincipal.Items[i].Text) + "'");
}
}
}
if (z.ToString() != "")
{
strAdd = " AND c.Principal IN (" + z.ToString() + ")";
}
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 "
+ strAdd
;
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;
tr.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoKontrak"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
tr.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NamaCustomer"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
tr.Cells.Add(c);
c = new TableCell();
decimal Total = TotalOutstanding(rs.Rows[i]["NoKontrak"].ToString());
c.Text = Cf.Num(Total);
c.HorizontalAlign = HorizontalAlign.Right;
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);
}
protected void save_Click(object sender, System.EventArgs e)
{
if (valid())
{
int No = 0;
No = Db.SingleInteger("SELECT ISNULL(MAX(No), 0) FROM REF_DATA WHERE Project = '" + project.SelectedValue + "'");
if (No > 0)
{
DataTable bef = Log();
//update
Db.Execute("UPDATE REF_DATA"
+ " SET NPWP = '" + npwp.Text + "'"
+ " ,NPWPNama = '" + npwpnama.Text + "'"
+ " ,AlamatNPWP = '" + npwpalamat.Text + "'"
+ " ,NoTelp = '" + notelp.Text + "'"
+ " ,NoFax = '" + nofax.Text + "'"
+ " ,AlamatPers = '" + alamat1.Text + "'"
+ " ,AlamatProject = '" + alamat2.Text + "'"
+ " ,RekBank = '" + namabank.Text + "'"
+ " ,RekCabang = '" + cabang.Text + "'"
+ " ,RekNo = '" + norek.Text + "'"
+ " ,RekAN = '" + an.Text + "'"
+ " ,RekAlamat = '" + alamatbank.Text + "'"
+ " ,BlokNPWP = '" + BlokNPWP.Text + "'"
+ " ,NomorNPWP = '" + NomorNPWP.Text + "'"
+ " ,RTNPWP = '" + RTNPWP.Text + "'"
+ " ,RWNPWP = '" + RWNPWP.Text + "'"
+ " ,KecamatanNPWP = '" + KecamatanNPWP.Text + "'"
+ " ,KabupatenNPWP = '" + KabupatenNPWP.Text + "'"
+ " ,PropinsiNPWP = '" + PropinsiNPWP.Text + "'"
+ " ,KodePosNPWP = '" + KodePosNPWP.Text + "'"
+ " WHERE Project = '" + project.SelectedValue + "'"
);
string KetLog = Cf.LogCompare(bef, Log());
Db.Execute("EXEC spLogRefData"
+ " 'EDIT'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + KetLog + "'"
+ ",'" + No + "'"
);
}
else
{
No = Db.SingleInteger("SELECT COUNT(No) + 1 FROM REF_DATA");
Db.Execute(
"INSERT INTO REF_DATA "
+ "(No, NPWP, NPWPNama, AlamatNPWP, NoTelp, NoFax, AlamatPers, AlamatProject, RekBank, RekCabang, RekNo"
+ ", RekAN, RekAlamat, BlokNPWP, NomorNPWP, RTNPWP, RWNPWP, KecamatanNPWP, KabupatenNPWP, PropinsiNPWP, KodePosNPWP, Project)"
+ " VALUES(" + No + ",'" + npwp.Text + "', '" + npwpnama.Text + "', '" + npwpalamat.Text + "', '" + notelp.Text + "', '" + nofax.Text + "', '" + alamat1.Text + "'"
+ ", '" + alamat2.Text + "', '" + namabank.Text + "', '" + cabang.Text + "', '" + norek.Text + "', '" + an.Text + "', '" + alamatbank.Text + "'"
+ ", '" + BlokNPWP.Text + "', '" + NomorNPWP.Text + "', '" + RTNPWP.Text + "', '" + RWNPWP.Text + "', '" + KecamatanNPWP.Text + "', '" + KabupatenNPWP.Text + "'"
+ ", '" + PropinsiNPWP.Text + "', '" + KodePosNPWP.Text + "', '" + project.SelectedValue + "'"
+ ")"
);
string KetLog = Cf.LogCapture(Log());
Db.Execute("EXEC spLogRefData"
+ " 'DAFTAR'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + KetLog + "'"
+ ",'" + No + "'"
);
}
Response.Redirect("MasterData.aspx?done=1&project=" + project.SelectedValue);
}
}
protected void FillOutstanding(string NoKontrak, ref decimal t2, ref decimal t3, ref decimal t4, ref decimal t5, ref decimal t6,
ref decimal st1, ref decimal st2, ref decimal st3, ref decimal st4, ref decimal st5, ref int index, TableRow tr)
{
DateTime AsOf = Convert.ToDateTime(tgl.Text);
DataTable rs = Db.Rs("SELECT *"
+ ", DATEDIFF(DAY, TglJT, '" + Cf.Tgl112(AsOf) + "') AS Telat"
+ ", NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan), 0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN"
+ " WHERE NoKontrak = '" + NoKontrak + "' AND NoTagihan = a.NoUrut"
+ ") AS Sisa"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN a"
+ " WHERE NoKontrak = '" + NoKontrak + "'"
+ " AND DATEDIFF(DAY, TglJT, '" + Cf.Tgl112(AsOf) + "') >= 0"
+ " AND (SELECT ISNULL(SUM(NilaiPelunasan), 0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN"
+ " WHERE NoKontrak = '" + NoKontrak + "' AND NoTagihan = a.NoUrut"
+ ") < NilaiTagihan"
+ " ORDER BY NoUrut"
);
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
int Telat = Convert.ToInt32(rs.Rows[i]["Telat"]);
decimal Sisa = Convert.ToDecimal(rs.Rows[i]["Sisa"]);
TableCell c;
if (i > 0)
{
tr = new TableRow();
c = new TableCell();
c.ColumnSpan = 4;
tr.Cells.Add(c);
}
c = new TableCell();
c.Text = rs.Rows[i]["NoKontrak"] + "." + rs.Rows[i]["NoUrut"] + " " + rs.Rows[i]["NamaTagihan"].ToString();
tr.Cells.Add(c);
if (Telat >= 0 && Telat <= 30)
{
t2 += Sisa;
st1 += Sisa;
c = new TableCell();
c.Text = Cf.Num(Sisa);
c.HorizontalAlign = HorizontalAlign.Right;
tr.Cells.Add(c);
c = new TableCell();
c.Text = Telat.ToString();
c.HorizontalAlign = HorizontalAlign.Center;
tr.Cells.Add(c);
}
else
{
c = new TableCell();
c.ColumnSpan = 2;
tr.Cells.Add(c);
}
if (Telat >= 31 && Telat <= 60)
{
t3 += Sisa;
st2 += Sisa;
c = new TableCell();
c.Text = Cf.Num(Sisa);
c.HorizontalAlign = HorizontalAlign.Right;
tr.Cells.Add(c);
c = new TableCell();
c.Text = Telat.ToString();
c.HorizontalAlign = HorizontalAlign.Center;
tr.Cells.Add(c);
}
else
{
c = new TableCell();
c.ColumnSpan = 2;
tr.Cells.Add(c);
}
if (Telat >= 61 && Telat <= 90)
{
t4 += Sisa;
st3 += Sisa;
c = new TableCell();
c.Text = Cf.Num(Sisa);
c.HorizontalAlign = HorizontalAlign.Right;
tr.Cells.Add(c);
c = new TableCell();
c.Text = Telat.ToString();
c.HorizontalAlign = HorizontalAlign.Center;
tr.Cells.Add(c);
}
else
{
c = new TableCell();
c.ColumnSpan = 2;
tr.Cells.Add(c);
}
if (Telat > 90)
{
t5 += Sisa;
st4 += Sisa;
c = new TableCell();
c.Text = Cf.Num(Sisa);
c.HorizontalAlign = HorizontalAlign.Right;
tr.Cells.Add(c);
c = new TableCell();
c.Text = Telat.ToString();
c.HorizontalAlign = HorizontalAlign.Center;
tr.Cells.Add(c);
}
else
{
c = new TableCell();
c.ColumnSpan = 2;
tr.Cells.Add(c);
}
decimal den = Db.SingleDecimal("SELECT Denda FROM MS_TAGIHAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND KPR != 1 AND NoUrut = '" + rs.Rows[i]["NoUrut"] + "' ORDER BY NoUrut");
c = new TableCell();
c.Text = Cf.Num(den);
c.HorizontalAlign = HorizontalAlign.Right;
tr.Cells.Add(c);
rpt.Rows.Add(tr);
t6 += Convert.ToDecimal(den);
st5 += Convert.ToDecimal(den);
}
if (rs.Rows.Count > 0)
{
index++;
SubTotal(st1, st2, st3, st4, st5);
}
}
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 = "A.TglKontrak";
order = ",A.NoKontrak";
}
DateTime Dari = Convert.ToDateTime(dari.Text);
DateTime Sampai = Convert.ToDateTime(sampai.Text);
if (Dari > Sampai)
{
DateTime x = Sampai;
Sampai = Dari;
Dari = x;
}
string Project = "";
if (project.SelectedValue != "SEMUA")
{
Project = " AND A.Project = '" + project.SelectedValue + "'";
}
string Perusahaan = "";
if (pers.SelectedValue != "SEMUA")
{
Perusahaan = " AND A.Pers = '" + pers.SelectedValue + "'";
}
int index = 1;
int no = 1;
//string sql = "SELECT DISTINCT (NoAgent) from MS_KONTRAK A"
// + " where (select ISNULL(count(*),0) from MS_KOMISI where NoKontrak = A.NoKontrak) > 0" +Project + Perusahaan;
//DataTable sr = Db.Rs(sql);
//decimal t1 = 0, t2 = 0;
//for (int g = 0; g < sr.Rows.Count; g++)
//{
//if (!Response.IsClientConnected) break;
//string strSql = "SELECT "
// + "A.NoKontrak"
// + ",A.TglKontrak"
// + ",A.NilaiDPP"
// + ",A.NoUnit"
// + ",A.Skema"
// + ",A.NilaiKontrak"
// + ",B.Nama AS Ag"
// + ",B.Principal"
// + ",B.NPWP"
// + ",B.Rekening"
// + ",A.Status"
// + ",A.PersenLunas"
// + ",C.Nama as Customer"
// + ",A.NoAgent"
// + ",A.NoStock"
// + " 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 A.NoAgent= '" + sr.Rows[g]["NoAgent"] + "'"
// + " AND A.FlagKomisi = '1'"
// + Status
// + " AND CONVERT(varchar,A.TglKontrak,112) >= '" + Cf.Tgl112(Dari) + "'"
// + " AND CONVERT(varchar,A.TglKontrak,112) <= '" + Cf.Tgl112(Sampai) + "'"
// //+ Agent
// + Project
// + Perusahaan
// + " ORDER BY B.Nama"
// + order;
string strSql = "SELECT "
+ "A.NoKontrak"
+ ",A.NoUnit"
+ ",C.Nilai"
+ ",B.NamaAgent"
+ ",A.Status"
+ ",A.PersenLunas"
+ ",A.NoAgent"
+ ",C.NoCF"
+ ",C.SN"
+ " FROM MS_KONTRAK A INNER JOIN MS_KOMISI_CF B ON A.NoKontrak = B.NoKontrak"
+ " INNER JOIN MS_KOMISI_CF_DETAIL C ON B.NoCF = C.NoCF"
//+ " WHERE A.NoAgent= '" + sr.Rows[g]["NoAgent"] + "'"
//+ " AND A.FlagKomisi = '1'"
+ Status
+ " AND CONVERT(varchar,A.TglKontrak,112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(varchar,A.TglKontrak,112) <= '" + Cf.Tgl112(Sampai) + "'"
+ Project
+ Perusahaan
+ " ORDER BY B.NamaAgent"
+ 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;
TableRow r2a;
TableHeaderCell th2;
Table tb;
r.VerticalAlign = VerticalAlign.Top;
//r.Attributes["ondblclick"] = "popJadwalKomisi('" + rs.Rows[i]["NoKontrak"] + "')";
//nambah no default
//c = new TableCell();
//c.Text = (no).ToString();
////c.RowSpan = 4;
//c.HorizontalAlign = HorizontalAlign.Left;
//r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoKontrak"].ToString();
string NoKontrak = rs.Rows[i]["NoKontrak"].ToString();
//c.RowSpan = 4;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoUnit"].ToString();
//c.RowSpan = 4;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NamaAgent"].ToString();
//c.RowSpan = 4;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
//string agen = Db.SingleString("Select Nama from ms_agent where NoAgent ='" + rs.Rows[i]["NoAgent"]+ "'");
//c = new TableCell();
//c.Text = agen;
//c.RowSpan = 4;
//c.HorizontalAlign = HorizontalAlign.Left;
//r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Convert.ToDecimal(rs.Rows[i]["Nilai"]));
//c.RowSpan = 4;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
string StatusCF = "<label style='color:red;'>Belum Pengajuan</label>", NoRef = "";
DataTable cfp = Db.Rs("SELECT * FROM MS_KOMISI_CFP_DETAIL WHERE NoCF = '" + rs.Rows[i]["NoCF"].ToString() + "' AND SN_NoCF = " + Convert.ToInt32(rs.Rows[i]["SN"]));
if (cfp.Rows.Count > 0)
{
NoRef = cfp.Rows[0]["NoCFP"].ToString();
StatusCF = "<label style='color:green;'>Pengajuan Pencairan</label>";
DataTable cfr = Db.Rs("SELECT * FROM MS_KOMISI_CFR_DETAIL WHERE NoCF = '" + rs.Rows[i]["NoCF"].ToString() + "' AND SN_NoCF = " + Convert.ToInt32(rs.Rows[i]["SN"]));
if (cfr.Rows.Count > 0)
{
NoRef = cfr.Rows[0]["NoCFR"].ToString();
StatusCF = "<label style='color:blue;'>Realisasi Pencairan</label>";
}
}
c = new TableCell();
c.Text = StatusCF;
r.Cells.Add(c);
rpt.Rows.Add(r);
no++;
//t1 += NKom;
//termin(NoKontrak);
}
//}
//SubTotal("GRAND TOTAL", t1);
}
private void Report()
{
param.Visible = false;
rpt.Visible = true;
lblHeader.Text = Mi.Pt
+ "<br />"
+ "LAPORAN AGING PIUTANG"
+ "<br />"
+ "PER " + Cf.Day(tgl.Text)
;
System.Text.StringBuilder x = new System.Text.StringBuilder();
if (lokasi.SelectedIndex > 0)
{
x.Append("Lokasi: " + lokasi.SelectedItem.Text + "<br />");
}
if (ddlAgent.SelectedIndex != 0)
{
x.Append("Sales: " + ddlAgent.SelectedValue);
}
else
{
x.Append("Sales: SEMUA");
}
string strPrincipal = "SEMUA";
System.Text.StringBuilder z = new System.Text.StringBuilder();
bool isFirst = true;
for (int i = 0; i < cblPrincipal.Items.Count; i++)
{
if (cblPrincipal.Items[i].Selected)
{
if (isFirst)
{
z.Append(cblPrincipal.Items[i].Text);
isFirst = false;
}
else
{
z.Append("," + cblPrincipal.Items[i].Text);
}
}
}
if (z.ToString() != "")
{
strPrincipal = z.ToString();
}
x.Append("<br />Principal: " + strPrincipal);
x.Append("<br /><span style='font-weight: normal;'>Laporan dihasilkan pada hari : " + Cf.IndoWeek(DateTime.Today)
+ ", " + Cf.Date(DateTime.Now)
+ " dari workstation : " + Act.IP
+ " dan username : "******"</span>"
);
lblSubHeader.Text = x.ToString();
Fill();
}
private void Fill()
{
string addq = "";
if (metode.SelectedIndex == 1)
{
addq = " AND ISC064_MARKETINGJUAL..MS_KONTRAK.Status = 'A'";
}
else if (metode.SelectedIndex == 2)
{
addq = " AND ISC064_MARKETINGJUAL..MS_KONTRAK.Status = 'B'";
}
if (Request.QueryString["tag"] != null)
{
addq = addq + " AND (SELECT COUNT(NoUrut) FROM ISC064_MARKETINGJUAL..MS_TAGIHAN WHERE NoKontrak = ISC064_MARKETINGJUAL..MS_KONTRAK.NoKontrak) = 0";
info.Text = "Kontrak dengan kondisi jadwal tagihan sudah dikeluarkan, tidak ditampilkan.";
}
if (Request.QueryString["kom"] != null)
{
addq = addq + " AND FlagKomisi = 0";
info.Text = "Kontrak dengan kondisi jadwal komisi sudah dikeluarkan, tidak ditampilkan.";
}
if (Request.QueryString["st"] != null)
{
addq = addq + " AND ST <> 'D'";
info.Text = "Kontrak dengan kondisi serah terima sudah dijalankan, tidak ditampilkan.";
}
if (Request.QueryString["ppjb"] != null)
{
addq = addq + " AND PPJB <> 'D'";
info.Text = "Kontrak dengan kondisi PPJB sudah dijalankan, tidak ditampilkan.";
}
if (Request.QueryString["ajb"] != null)
{
addq = addq + " AND AJB <> 'D'";
info.Text = "Kontrak dengan kondisi AJB sudah dijalankan, tidak ditampilkan.";
}
if (Request.QueryString["dd"] != null)
{
addq = " AND (SELECT COUNT(*) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN WHERE NoKontrak = " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK.NoKontrak AND Denda > 0) > 0";
info.Text = "Kontrak dengan kondisi tidak ada denda, tidak ditampilkan.";
}
string NoKontrak = "";
if (Request.QueryString["status"] == "dari" || Request.QueryString["status"] == "sampai")
{
NoKontrak = "'<a href=\"javascript:callSource(''' + NoKontrak + ''', ''" + Request.QueryString["status"] + "'')\">'"
+ "+ NoKontrak + '</a><br>";
}
else
{
NoKontrak = "'<a href=\"javascript:call(''' + NoKontrak + ''')\" ' + CASE MS_KONTRAK.Status WHEN 'B' THEN 'style = ''text-decoration:line-through''' ELSE '' END + '>'"
+ "+ NoKontrak + '</a><br>";
}
NoKontrak += " <font style=''font:8pt;color:' + CASE WHEN(SELECT COUNT(NoUrut) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN WHERE NoKontrak = MS_KONTRAK.NoKontrak) <> 0 THEN 'Black' ELSE 'Silver' END + ' ''>TAGIH</font> "
+ "<font style=''font:8pt;color:' + CASE WHEN FlagKomisi = 1 THEN 'Black' ELSE 'Silver' END + '''>KOM</font> "
+ "<font style=''font:8pt;color:' + CASE WHEN NoPPJB <> '' THEN 'Black' ELSE 'Silver' END + '''>PPJB</font> "
+ "<font style=''font:8pt;color:' + CASE WHEN NoAJB <> '' THEN 'Black' ELSE 'Silver' END + '''>AJB</font> "
+ "<font style=''font:8pt;color:' + CASE WHEN ST = 'D' THEN 'Black' ELSE 'Silver' END + '''>BAST</font>"
+ "' AS NoKontrak"
;
string strSql = "SELECT "
+ NoKontrak
+ ",NoUnit AS Unit"
+ ",CONVERT(VARCHAR,TglKontrak,106) AS Tanggal"
+ ",ISC064_MARKETINGJUAL..MS_CUSTOMER.Nama + '<br>' + ISC064_MARKETINGJUAL..MS_AGENT.Nama + ' ' + ISC064_MARKETINGJUAL..MS_AGENT.Principal AS Customer"
+ ",'' AS Keterangan"
+ ",ISC064_MARKETINGJUAL..MS_KONTRAK.NamaProject AS Project"
+ " 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 NoKontrak + NoUnit + ISC064_MARKETINGJUAL..MS_CUSTOMER.Nama + ISC064_MARKETINGJUAL..MS_AGENT.Nama + ISC064_MARKETINGJUAL..MS_AGENT.Principal "
+ " LIKE '%" + Cf.Str(keyword.Text) + "%'"
+ " AND MS_KONTRAK.Project = '" + project.SelectedValue + "'"
+ addq
+ " ORDER BY NoKontrak";
DataTable rs = new DataTable();
Db.Fill(rs, strSql);
tb.DataSource = rs;
tb.DataBind();
}
private void Fill(bool excel)
{
string Status = "";
if (statusA.Checked)
{
Status = " AND a.Status = 'A'";
}
if (statusB.Checked)
{
Status = " AND a.Status = 'B'";
}
string KPR = "";
if (kpa1.Checked)
{
KPR = " ";
}
else if (kpa2.Checked)
{
KPR = " AND b.KPR != '1' ";
}
DateTime Dari = Convert.ToDateTime(dari.Text);
string Lokasi = "";
if (lokasi.SelectedIndex != 0)
{
Lokasi = " AND a.Lokasi = '" + Cf.Str(lokasi.SelectedValue) + "'";
}
string Project = " AND a.Project IN (" + Act.ProjectListSql + ")";
if (project.SelectedValue != "SEMUA")
{
Project = " AND a.Project = '" + project.SelectedValue + "'";
}
string Perusahaan = "";
if (pers.SelectedValue != "SEMUA")
{
Perusahaan = " AND a.Pers = '" + pers.SelectedValue + "'";
}
string aa = "";
if (UserAgent() > 0)
{
aa = " AND a.NoAgent = " + UserAgent();
}
string strSql = "SELECT"
+ " a.NoKontrak"
+ ",a.CaraBayar"
+ ",NamaTagihan"
+ ",TglJT"
+ ",NilaiTagihan"
+ ",NoUrut"
+ ",datediff(day,convert(datetime,TglJT,112),'" + Cf.Tgl112(Dari) + "') as telat"
+ ", a.NoCustomer"
+ ", a.NoUnit"
+ ", b.KPR as KPR"
+ " FROM ISC064_MARKETINGJUAL..MS_KONTRAK a INNER JOIN ISC064_MARKETINGJUAL..MS_TAGIHAN b"
+ " ON a.NoKontrak = b.NoKontrak"
+ " WHERE 1=1"
+ " AND ((SELECT ISNULL(SUM(NilaiPelunasan),0) as pelunasan FROM ISC064_MARKETINGJUAL..MS_PELUNASAN"
+ " WHERE NoKontrak = a.NoKontrak AND NoTagihan = b.NoUrut) < NilaiTagihan)"
+ " AND TglJT < '" + Dari + "' "
+ " AND a.CaraBayar IN(" + Rpt.inSql(carabayar) + ")"
+ Project
+ Perusahaan
+ Lokasi
+ KPR
+ Status
+ aa
+ " ORDER BY a.NoKontrak ASC";
decimal a1 = 0;
decimal a2 = 0;
decimal a3 = 0;
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;
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]["NoKontrak"].ToString() + "." + rs.Rows[i]["NoUrut"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT Nama FROM ISC064_MARKETINGJUAL..MS_CUSTOMER WHERE NoCustomer = " + rs.Rows[i]["NoCustomer"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
if (excel == false)
{
c.Text = Db.SingleString("SELECT NoTelp FROM ISC064_MARKETINGJUAL..MS_CUSTOMER WHERE NoCustomer = " + rs.Rows[i]["NoCustomer"]);
}
else
{
string petik = "'";
c.Text = petik + Db.SingleString("SELECT NoTelp FROM ISC064_MARKETINGJUAL..MS_CUSTOMER WHERE NoCustomer = " + rs.Rows[i]["NoCustomer"]);
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
if (excel == false)
{
c.Text = Db.SingleString("SELECT NoHP FROM ISC064_MARKETINGJUAL..MS_CUSTOMER WHERE NoCustomer = " + rs.Rows[i]["NoCustomer"]);
}
else
{
string petik = "'";
c.Text = petik + Db.SingleString("SELECT NoHP FROM ISC064_MARKETINGJUAL..MS_CUSTOMER WHERE NoCustomer = " + rs.Rows[i]["NoCustomer"]);
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
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]["NamaTagihan"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglJT"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["telat"].ToString();
c.HorizontalAlign = HorizontalAlign.Center;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["NilaiTagihan"].ToString());
a1 = a1 + Convert.ToDecimal(c.Text);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan),0) FROM ISC064_MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND NoTagihan = '" + rs.Rows[i]["NoUrut"].ToString() + "'"));
a2 = a2 + Convert.ToDecimal(c.Text);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
decimal sisa = 0;
decimal n1 = (decimal)rs.Rows[i]["NilaiTagihan"];
decimal n2 = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan),0) FROM ISC064_MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND NoTagihan = '" + rs.Rows[i]["NoUrut"].ToString() + "'");
string txt = "";
sisa = n1 - n2;
c = new TableCell();
c.Text = Cf.Num(sisa);
a3 = a3 + Convert.ToDecimal(c.Text);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
rpt.Rows.Add(r);
if (i == rs.Rows.Count - 1)
{
SubTotal(txt, a1, a2, a3);
}
}
}
private void Fill()
{
string Status = "";
if (statusA.Checked)
{
Status = " AND ISC064_MARKETINGJUAL..MS_KONTRAK.Status = 'A'";
}
if (statusB.Checked)
{
Status = " AND ISC064_MARKETINGJUAL..MS_KONTRAK.Status = 'B'";
}
DateTime Dari = Convert.ToDateTime(dari.Text);
DateTime Sampai = Convert.ToDateTime(sampai.Text);
if (Dari > Sampai)
{
DateTime x = Sampai;
Sampai = Dari;
Dari = x;
}
string Project = " AND ISC064_MARKETINGJUAL..MS_KONTRAK.Project IN (" + Act.ProjectListSql + ")";
if (project.SelectedValue != "SEMUA")
{
Project = " AND ISC064_MARKETINGJUAL..MS_KONTRAK.Project = '" + project.SelectedValue + "'";
}
string Perusahaan = "";
if (pers.SelectedValue != "SEMUA")
{
Perusahaan = " AND ISC064_MARKETINGJUAL..MS_KONTRAK.Pers = '" + pers.SelectedValue + "'";
}
string Lokasi = "";
if (lokasi.SelectedIndex != 0)
{
Lokasi = " AND Lokasi = '" + Cf.Str(lokasi.SelectedValue) + "'";
}
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 "
+ Perusahaan
+ Project
+ Lokasi
+ Status
+ aa
;
DataTable rs = Db.Rs(strSql);
TableRow r = new TableRow();
TableHeaderCell hc;
hc = new TableHeaderCell();
hc.Text = "Overdue";
hc.HorizontalAlign = HorizontalAlign.Center;
//hc.Attributes["style"] = "background-color:gray;color:white;";
hc.Wrap = false;
r.Cells.Add(hc);
hc = new TableHeaderCell();
hc.Text = "Actual";
hc.HorizontalAlign = HorizontalAlign.Center;
//hc.Attributes["style"] = "background-color:gray;color:white;";
hc.Wrap = false;
r.Cells.Add(hc);
hc = new TableHeaderCell();
hc.Text = "Early";
hc.HorizontalAlign = HorizontalAlign.Center;
//hc.Attributes["style"] = "background-color:gray;color:white;";
hc.Wrap = false;
r.Cells.Add(hc);
hc = new TableHeaderCell();
hc.Text = "A";
hc.HorizontalAlign = HorizontalAlign.Center;
//hc.Attributes["style"] = "background-color:gray;color:white;";
hc.Wrap = false;
r.Cells.Add(hc);
hc = new TableHeaderCell();
hc.Text = "B";
hc.HorizontalAlign = HorizontalAlign.Center;
//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()
{
DateTime Dari = Convert.ToDateTime(dari.Text);
DateTime Sampai = Convert.ToDateTime(sampai.Text);
if (Dari > Sampai)
{
DateTime x = Sampai;
Sampai = Dari;
Dari = x;
}
string UserID = "";
if (user.SelectedIndex != 0)
{
UserID = " AND UserID = '" + user.SelectedValue + "'";
}
string CaraBayar = "";
if (carabayar.SelectedIndex != 0)
{
CaraBayar = " AND CaraBayar = '" + carabayar.SelectedValue + "'";
}
string Project = "";
if (project.SelectedValue != "Project :")
{
Project = " AND MS_TTS.Project = '" + project.SelectedValue + "'";
}
string Tipe = "";
if (tipe.SelectedIndex != 0)
{
Tipe = " AND Tipe = '" + Cf.Str(tipe.SelectedValue) + "'";
}
string Status = "";
if (statusB.Checked)
{
Status = " AND MS_TTS.Status = 'BARU'";
}
if (statusP.Checked)
{
Status = " AND MS_TTS.Status = 'POST'";
}
if (statusV.Checked)
{
Status = " AND MS_TTS.Status = 'VOID'";
}
string nav = "'<a href=\"javascript:call(''' + CONVERT(varchar(50),NoTTS) + ''')\" ' + CASE MS_TTS.Status WHEN 'VOID' THEN 'style = ''text-decoration:line-through''' ELSE '' END + '>'"
+ "+ CONVERT(varchar(50),NoTTS2) + '</a><br>TTS Manual : ' + MS_TTS.ManualTTS +'<br><i>'+ MS_TTS.Status + '</i>' + CASE MS_TTS.Status WHEN 'POST' THEN '<br>KWT Manual:' +MS_TTS.ManualBKM ELSE '' END";
string strSql = "SELECT"
+ nav
+ " AS TTS"
+ ",CONVERT(VARCHAR,MS_TTS.TglTTS,106) + '<br>' + (SELECT Nama FROM " + Mi.DbPrefix + "SECURITY..USERNAME WHERE UserID = MS_TTS.UserID) AS Tgl"
+ ",MS_TTS.Tipe + ' No : ' + MS_TTS.Ref + '<br>' + MS_TTS.Unit + '<br>' + MS_TTS.Customer AS Customer"
+ ",MS_TTS.Ket + CASE WHEN MS_TTS.Titip != '' THEN '<br>Pengelola : ' + MS_TTS.Titip ELSE '' END + CASE WHEN MS_TTS.Tolak != '' THEN '<br>Tolakan : ' + MS_TTS.Tolak ELSE '' END AS Keterangan"
+ ",CASE MS_TTS.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<br>' + MS_TTS.NoBG + '<br><font style=white-space:nowrap>Tgl. BG : ' + CONVERT(VARCHAR,MS_TTS.TglBG,106) + '</font>'"
+ " WHEN 'UJ' THEN 'UANG JAMINAN'"
+ " WHEN 'DN' THEN 'DISKON'"
+ " ELSE '' END AS CaraBayar"
+ ",CASE WHEN MS_TTS.Pph = '1' THEN + FORMAT(MS_TTS.Total,'#,###') + '<br>PPH' ELSE FORMAT(MS_TTS.Total,'#,###') END AS Total"
+ ",MS_TTS.Project"
+ " FROM MS_TTS "
+ " WHERE 1=1 "
+ " AND CONVERT(varchar,TglTTS,112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(varchar,TglTTS,112) <= '" + Cf.Tgl112(Sampai) + "'"
+ Project
+ UserID
+ CaraBayar
+ Tipe
+ Status
+ " ORDER BY NoTTS";
DataTable rs = Db.Rs(strSql);
tb.DataSource = rs;
tb.DataBind();
}
private string Tagihan(DataTable rs, string NoKontrak, string Type, DateTime Dari, DateTime Sampai)
{
string strSql = "";
bool s = false, Overdue = false, Actual = false, Early = false;
System.Text.StringBuilder x = new System.Text.StringBuilder();
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
if ((Convert.ToDateTime(rs.Rows[i]["TglJT"]) >= Dari) && (Convert.ToDateTime(rs.Rows[i]["TglJT"]) <= Sampai)) // 1
{
s = true;
Overdue = false;
Actual = true;
Early = false;
}
else if (Convert.ToDateTime(rs.Rows[i]["TglJT"]) <= Dari)
{
if (Convert.ToInt32(rs.Rows[i]["CountLunas"]) == 0) // 2
{
s = true;
}
else if (Convert.ToInt32(rs.Rows[i]["CountLunas2"]) > 0) // 3
{
s = true;
}
else
{
s = false;
}
Overdue = true;
Actual = false;
Early = false;
}
else if (Convert.ToDateTime(rs.Rows[i]["TglJT"]) > Sampai)
{
if (Convert.ToInt32(rs.Rows[i]["CountLunas2"]) > 0) // 4
{
s = true;
}
else
{
s = false;
}
Overdue = false;
Actual = false;
Early = true;
}
else
{
s = false;
Overdue = false;
Actual = false;
Early = false;
}
if (s)
{
if (Type == "NAMA")
{
x.Append(rs.Rows[i]["NamaTagihan"].ToString() + "<br />");
}
else if (Type == "TGL")
{
x.Append(Cf.Day(rs.Rows[i]["TglJT"]) + "<br />");
}
else if (Type == "OVERDUE")
{
if (Overdue)
{
x.Append(Cf.Num(rs.Rows[i]["NilaiTagihan"]) + "<br />");
total1.Text = Cf.Num(Convert.ToDecimal(rs.Rows[i]["NilaiTagihan"])
+ Convert.ToDecimal(total1.Text));
}
else
{
x.Append(" <br />");
}
}
else if (Type == "ACTUAL")
{
if (Actual)
{
x.Append(Cf.Num(rs.Rows[i]["NilaiTagihan"]) + "<br />");
total2.Text = Cf.Num(Convert.ToDecimal(rs.Rows[i]["NilaiTagihan"])
+ Convert.ToDecimal(total2.Text));
}
else
{
x.Append(" <br />");
}
}
else if (Type == "EARLY")
{
if (Early)
{
x.Append(Cf.Num(rs.Rows[i]["NilaiTagihan"]) + "<br />");
total3.Text = Cf.Num(Convert.ToDecimal(rs.Rows[i]["NilaiTagihan"])
+ Convert.ToDecimal(total3.Text));
}
else
{
x.Append(" <br />");
}
}
else if (Type == "TGLLUNAS")
{
strSql = "SELECT TOP 1 TglPelunasan"
+ " FROM ISC064_MARKETINGJUAL..MS_PELUNASAN"
+ " WHERE NoTagihan = " + Cf.Pk(rs.Rows[i]["NoUrut"])
+ " AND NoKontrak = '" + NoKontrak + "'"
+ " ORDER BY TglPelunasan DESC"
;
DataTable rs2 = Db.Rs(strSql);
if (rs2.Rows.Count > 0)
{
x.Append(Cf.Day(rs2.Rows[0]["TglPelunasan"]) + "<br />");
}
else
{
x.Append(" <br />");
}
}
else if (Type == "A")
{
decimal t = 0;
strSql = "SELECT ISNULL(SUM(NilaiPelunasan), 0)"
+ " FROM ISC064_MARKETINGJUAL..MS_PELUNASAN"
+ " WHERE NoTagihan = " + Cf.Pk(rs.Rows[i]["NoUrut"])
+ " AND (CONVERT(varchar, TglPelunasan, 112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(varchar, TglPelunasan, 112) <= '" + Cf.Tgl112(Sampai) + "')"
+ " AND NoKontrak = '" + NoKontrak + "'"
;
t = Db.SingleDecimal(strSql);
total4.Text = Cf.Num(t + Convert.ToDecimal(total4.Text));
if (t != 0)
{
x.Append(Cf.Num(t) + "<br />");
}
else
{
x.Append(" <br />");
}
}
else if (Type == "B")
{
decimal t = 0;
strSql = "SELECT ISNULL(SUM(NilaiPelunasan), 0)"
+ " FROM ISC064_MARKETINGJUAL..MS_PELUNASAN"
+ " WHERE NoTagihan = " + Cf.Pk(rs.Rows[i]["NoUrut"])
+ " AND (CONVERT(varchar, TglPelunasan, 112) < '" + Cf.Tgl112(Dari) + "'"
+ " OR CONVERT(varchar, TglPelunasan, 112) > '" + Cf.Tgl112(Sampai) + "')"
+ " AND NoKontrak = '" + NoKontrak + "'"
;
t = Db.SingleDecimal(strSql);
total5.Text = Cf.Num(t + Convert.ToDecimal(total5.Text));
if (t != 0)
{
x.Append(Cf.Num(t) + "<br />");
}
else
{
x.Append(" <br />");
}
}
}
}
return(x.ToString());
}
protected void Save(bool close)
{
int ada = Db.SingleInteger("SELECT COUNT(*) FROM " + Mi.DbPrefix + "MARKETINGJUAL..REF_AGENT_LEVEL WHERE Nama='" + nama.Text + "' AND LevelID != " + NoLevel);
if (ada > 0)
{
string s = "";
Js.Alert(
this
, "Input Tidak Valid.\\n\\n"
+ "Aturan Proses :\\n"
+ "1. Nama tidak boleh kosong.\\n"
+ "2. Tipe Sales tidak boleh kosong.\\n"
+ "3. Nama Duplikat.\\n"
+ "4. Tipe Sales Duplikat.\\n"
, "document.getElementById('" + s + "').focus();"
+ "document.getElementById('" + s + "').select();"
);
}
else
{
string strSql = "SELECT * FROM " + Mi.DbPrefix + "MARKETINGJUAL..REF_AGENT_LEVEL WHERE LevelID = '" + NoLevel + "'";
DataTable rs = Db.Rs(strSql);
DataTable rsBef = Db.Rs("SELECT "
+ " Nama"
+ ",Project"
+ ",Tipe"
+ ",LevelID"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..REF_AGENT_LEVEL "
+ " WHERE LevelID = '" + NoLevel + "'");
Db.Execute("UPDATE " + Mi.DbPrefix + "MARKETINGJUAL..REF_AGENT_LEVEL SET Nama='" + nama.Text + "',Tipe='" + tipe.SelectedValue + "',Project='" + project.SelectedValue + "' WHERE LevelID = '" + NoLevel + "'");
DataTable rsAft = Db.Rs("SELECT "
+ " Nama"
+ ",Project"
+ ",Tipe"
+ ",LevelID"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..REF_AGENT_LEVEL "
+ " WHERE LevelID = '" + NoLevel + "'");
//Logfile
string Ket = "Tipe: " + tipe.Text + "<br>"
+ Cf.LogCompare(rsBef, rsAft);
Db.Execute("EXEC " + Mi.DbPrefix + "MARKETINGJUAL..spLogLevelSales"
+ " 'EDIT'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Ket + "'"
+ ",'" + NoLevel + "'"
);
decimal LogID = Db.SingleDecimal("SELECT TOP 1 LogID FROM " + Mi.DbPrefix + "MARKETINGJUAL..REF_AGENT_LEVEL_LOG ORDER BY LogID DESC");
Db.Execute("UPDATE " + Mi.DbPrefix + "MARKETINGJUAL..REF_AGENT_LEVEL_LOG SET Project = '" + project.SelectedValue + "' WHERE LogID = " + LogID);
if (close)
{
Js.Close(this);
}
else
{
Response.Redirect("LevelSalesEdit.aspx?done=1&NoLevel=" + NoLevel);
}
}
}
private void GenerateAcc()
{
if (penjualan.SelectedIndex == 0)
{
xls = "MappingCOA1.xls";
JenisPenjualan = " AND JenisPenjualan = 0";
}
else if (penjualan.SelectedIndex == 1)
{
xls = "MappingCOA2.xls";
JenisPenjualan = " AND JenisPenjualan = 1";
}
string strSql = "SELECT *"
+ " FROM MS_KONTRAK"
+ " WHERE PersenLunas >=20 AND Akunting = 0 AND Status = 'A'"
+ JenisPenjualan
;
DataTable rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
string JournalID = Akun.NewJournalID();
string JournalMemo = rs.Rows[i]["NoUnit"].ToString()
+ " "
+ Db.SingleString("SELECT Nama FROM MS_CUSTOMER WHERE NoCustomer = " + Cf.Pk(rs.Rows[i]["NoCustomer"]))
;
string SourceID = Cf.Pk(rs.Rows[i]["NoKontrak"]);
DateTime TglKontrak = Convert.ToDateTime(rs.Rows[i]["TglKontrak"]);
decimal NilaiKontrak = Convert.ToDecimal(rs.Rows[i]["NilaiKontrak"]);
//Insert Journal
Akun.Journal(
JournalID
, "IDR"
, 1
, TglKontrak
, JournalMemo
, Source
, SourceID
);
InsertJournalDetail(NilaiKontrak, JournalID, SourceID, TglKontrak);
//Update Status Akunting + NoVoucher di MS_KONTRAK; NoKontrak di ISC064_ACC..Journal
if (x.ToString() == "")
{
Db.Execute("UPDATE MS_KONTRAK SET Akunting2 = 1, NoVoucher = '" + JournalID + "' WHERE NoKontrak = '" + Cf.Pk(rs.Rows[i]["NoKontrak"]) + "'");
//Logfile
DataTable rsDetail = Db.Rs("SELECT * FROM MS_KONTRAK WHERE NoKontrak = '" + Cf.Pk(rs.Rows[i]["NoKontrak"]) + "'");
string Ket = Cf.LogCapture(rsDetail);
Db.Execute("EXEC spLogKontrak"
+ " 'ACC'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Ket + "'"
+ ",'" + Cf.Pk(rs.Rows[i]["NoKontrak"]) + "'"
);
}
}
if (x.ToString() != "")
{
err.Text = "<h2 style='border-top:1px dashed gray; padding-top:10'>Gagal posting:</h2>"
+ "<div style='margin-left: 40px; padding-top: 10px;'>"
+ x.ToString()
+ "</div>"
;
}
else
{
Response.Redirect("AccountingPiutang.aspx?done=1");
}
}
protected void save_Click(object sender, System.EventArgs e)
{
if (datavalid())
{
int c = Db.SingleInteger("SELECT COUNT(*) FROM MS_AJB WHERE NoKontrak = '" + NoKontrak + "'");
DateTime TglAJB = Convert.ToDateTime(tglajb.Text);
string Project = Db.SingleString("SELECT Project FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'");
string status = "D"; // B = Belum, S = Target, D = AJB, T = Tanda Tangan
if (c == 0)
{
string NoAJB = Db.SingleString("SELECT NoAJB FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'");
if (NoAJB == "")
{
NoAJB = Numerator.AJB(TglAJB.Month, TglAJB.Year, Project);
}
Db.Execute("EXEC spAJB "
+ " '" + NoKontrak + "'"
+ ",'" + NoAJB + "'"
+ ",'" + TglAJB + "'"
);
Db.Execute("UPDATE MS_AJB SET Project = '" + Project + "' WHERE NoAJB = '" + NoAJB + "'");
Db.Execute("UPDATE MS_AJB SET"
+ " AJBu=" + ajbused.SelectedValue
+ " ,NoAJBm='" + noajbm.Text + "'"
+ " ,AJB='" + status + "'"
+ ",TglAJB='" + tglajb.Text + "'"
+ ",NamaNotaris='" + notaris.Text + "'"
+ ",KetAJB ='" + keterangan.Text + "'"
+ ",Biaya ='" + nilaibiaya.Text + "'"
+ " WHERE NoKontrak = '" + NoKontrak + "'"
);
Db.Execute("UPDATE MS_KONTRAK SET AJB = 'D',TglAJB='" + tglajb.Text + "' WHERE NoKontrak = '" + NoKontrak + "'");
decimal NilaiBiaya = Convert.ToDecimal(nilaibiaya.Text);
if (NilaiBiaya != 0)
{
Db.Execute("EXEC spTagihanDaftar "
+ " '" + NoKontrak + "'"
+ ",'BIAYA ADM. AJB'"
+ ",'" + TglAJB + "'"
+ ", " + NilaiBiaya
+ ",'ADM'"
);
int NoUrut = Db.SingleInteger("SELECT TOP 1 NoUrut FROM MS_TAGIHAN WHERE NoKontrak = '" + NoKontrak + "' ORDER BY NoUrut DESC");
Db.Execute("UPDATE MS_TAGIHAN SET Jenis = 'AJB' WHERE NoKontrak = '" + NoKontrak + "' AND NoUrut = " + NoUrut);
}
DataTable rs = Db.Rs("SELECT"
+ " A.NoKontrak AS [No. Kontrak]"
+ ",B.NoUnit AS [Unit]"
+ ",C.Nama AS [Customer]"
+ ",CONVERT(varchar, A.TglTargetAJB, 106) AS [Tanggal Target AJB]"
+ ",CONVERT(varchar, A.TglAJB, 106) AS [Tanggal AJB]"
+ ",PersenLunas AS [Prosentase Pelunasan]"
+ ",A.Biaya"
+ ",A.KetAJB"
+ ", case when A.AJB='S' then 'Target AJB' when A.AJB='D' then 'AJB' when A.AJB='B' then 'Belum AJB' else 'Tanda Tangan AJB' end as [Status AJB]"
+ " FROM MS_AJB A INNER JOIN MS_KONTRAK B"
+ " ON A.NoKontrak = B.NoKontrak"
+ " INNER JOIN MS_CUSTOMER C ON B.NoCustomer = C.NoCustomer"
+ " WHERE A.NoKontrak = '" + NoKontrak + "'");
string ket = Cf.LogCapture(rs)
+ "<br>Biaya Administrasi : " + Cf.Num(NilaiBiaya)
;
Db.Execute("EXEC spLogKontrak "
+ " 'AJB'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + ket + "'"
+ ",'" + NoKontrak + "'"
);
decimal LogID = Db.SingleDecimal("SELECT TOP 1 LogID FROM MS_KONTRAK_LOG ORDER BY LogID DESC");
Db.Execute("UPDATE MS_KONTRAK_LOG SET Project = '" + Project + "' WHERE LogID = " + LogID);
}
else
{
string NoAJB = Db.SingleString("SELECT NoAJB FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'");
if (NoAJB == "")
{
NoAJB = Numerator.AJB(TglAJB.Month, TglAJB.Year, Project);
}
Db.Execute("UPDATE MS_AJB SET"
+ " AJBu=" + ajbused.SelectedValue
+ ",NoAJB= '" + NoAJB + "'"
+ ",NoAJBm='" + noajbm.Text + "'"
+ ",TglAJB='" + tglajb.Text + "'"
+ ",AJB='" + status + "'"
+ ",NamaNotaris='" + notaris.Text + "'"
+ ",KetAJB ='" + keterangan.Text + "'"
+ ",Biaya =" + Convert.ToDecimal(nilaibiaya.Text)
+ ",Project = '" + Project + "'"
+ " WHERE NoKontrak = '" + NoKontrak + "'"
);
decimal NilaiBiaya = Convert.ToDecimal(nilaibiaya.Text);
if (NilaiBiaya != 0)
{
Db.Execute("EXEC spTagihanDaftar "
+ " '" + NoKontrak + "'"
+ ",'BIAYA ADM. AJB'"
+ ",'" + TglAJB + "'"
+ ", " + NilaiBiaya
+ ",'ADM'"
);
int NoUrut = Db.SingleInteger("SELECT TOP 1 NoUrut FROM MS_TAGIHAN WHERE NoKontrak = '" + NoKontrak + "' ORDER BY NoUrut DESC");
Db.Execute("UPDATE MS_TAGIHAN SET Jenis = 'AJB' WHERE NoKontrak = '" + NoKontrak + "' AND NoUrut = " + NoUrut);
}
Db.Execute("UPDATE MS_KONTRAK SET AJB = 'D',TglAJB='" + tglajb.Text + "' WHERE NoKontrak = '" + NoKontrak + "'");
DataTable rs = Db.Rs("SELECT"
+ " A.NoKontrak AS [No. Kontrak]"
+ ",B.NoUnit AS [Unit]"
+ ",C.Nama AS [Customer]"
+ ",CONVERT(varchar, A.TglTargetAJB, 106) AS [Tanggal Target AJB]"
+ ",CONVERT(varchar, A.TglAJB, 106) AS [Tanggal AJB]"
+ ",PersenLunas AS [Prosentase Pelunasan]"
+ ",A.Biaya"
+ ",A.KetAJB"
+ ", case when A.AJB='S' then 'Target AJB' when A.AJB='D' then 'AJB' when A.AJB='B' then 'Belum AJB' else 'Tanda Tangan AJB' end as [Status AJB]"
+ " FROM MS_AJB A INNER JOIN MS_KONTRAK B"
+ " ON A.NoKontrak = B.NoKontrak"
+ " INNER JOIN MS_CUSTOMER C ON B.NoCustomer = C.NoCustomer"
+ " WHERE A.NoKontrak = '" + NoKontrak + "'");
string ket = Cf.LogCapture(rs)
+ "<br>Biaya Administrasi : " + Cf.Num(NilaiBiaya)
;
Db.Execute("EXEC spLogKontrak "
+ " 'AJB'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + ket + "'"
+ ",'" + NoKontrak + "'"
);
decimal LogID = Db.SingleDecimal("SELECT TOP 1 LogID FROM MS_KONTRAK_LOG ORDER BY LogID DESC");
Db.Execute("UPDATE MS_KONTRAK_LOG SET Project = '" + Project + "' WHERE LogID = " + LogID);
}
if (dariReminder.Checked)
{
Response.Redirect("ReminderAJB.aspx?done=" + NoKontrak + "&project=" + Project);
}
else
{
Response.Redirect("KontrakAJB.aspx?done=" + NoKontrak);
}
}
}
protected void Fill()
{
if (penjualan.SelectedIndex == 0)
{
JenisPenjualan = " AND JenisPenjualan = 0";
}
else if (penjualan.SelectedIndex == 1)
{
JenisPenjualan = " AND JenisPenjualan = 1";
}
string strSql = "SELECT *"
+ " FROM MS_KONTRAK"
+ " WHERE PersenLunas >= 20 AND Akunting = 0 AND Status = 'A'"
+ JenisPenjualan
;
DataTable rs = Db.Rs(strSql);
Rpt.NoData(list, rs, "Tidak ditemukan data yang siap untuk di-posting.");
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
c = new TableCell();
c.Text = "<a href=\"javascript:popEditKontrak('" + Cf.Pk(rs.Rows[i]["NoKontrak"]) + "');\">"
+ rs.Rows[i]["NoKontrak"].ToString()
+ "</a>"
;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT Nama FROM MS_CUSTOMER WHERE NoCustomer = " + rs.Rows[i]["NoCustomer"].ToString());
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoUnit"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
// c = new TableCell();
// c.Text = Cf.Num(rs.Rows[i]["TotalPelunasan"]);
// c.HorizontalAlign = HorizontalAlign.Right;
// r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["PersenLunas"]);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
list.Controls.Add(r);
Rpt.Border(r);
}
}
protected void Total(decimal t1, decimal t2, decimal t3, decimal t4, decimal t5, decimal t6, decimal t7, decimal t8, decimal t9, decimal t10, decimal t11, decimal t12, decimal t13, decimal t14, decimal JangkaPendek, decimal JangkaPanjang)
{
TableRow r = new TableRow();
TableCell c;
c = new TableCell();
c.Text = "Total";
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
c.ColumnSpan = 2;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round(t1, 0));
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round(t2, 0));
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round(t3, 0));
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round(t4, 0));
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round(t5, 0));
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round(t6, 0));
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
if (include.Checked)
{
c = new TableCell();
c.Text = Cf.Num(Math.Round(t14, 0));
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
}
c = new TableCell();
c.Text = Cf.Num(Math.Round(t7, 0));
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round(t8, 0));
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round(t9, 0));
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round(t10, 0));
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round(t11, 0));
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round(t12, 0));
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round(t13, 0));
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round(JangkaPendek, 0));
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round(JangkaPanjang, 0));
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
rpt.Rows.Add(r);
}
public override int GetHashCode()
{
int hash = 1;
if (Ts != 0D)
{
hash ^= Ts.GetHashCode();
}
if (Cf != 0D)
{
hash ^= Cf.GetHashCode();
}
if (Cr != 0D)
{
hash ^= Cr.GetHashCode();
}
if (MassFl != 0)
{
hash ^= MassFl.GetHashCode();
}
if (MassFr != 0)
{
hash ^= MassFr.GetHashCode();
}
if (MassRl != 0)
{
hash ^= MassRl.GetHashCode();
}
if (MassRr != 0)
{
hash ^= MassRr.GetHashCode();
}
if (Eps != 0D)
{
hash ^= Eps.GetHashCode();
}
hash ^= matrixQ_.GetHashCode();
hash ^= matrixR_.GetHashCode();
if (CutoffFreq != 0)
{
hash ^= CutoffFreq.GetHashCode();
}
if (MeanFilterWindowSize != 0)
{
hash ^= MeanFilterWindowSize.GetHashCode();
}
if (MaxIteration != 0)
{
hash ^= MaxIteration.GetHashCode();
}
if (MaxLateralAcceleration != 0D)
{
hash ^= MaxLateralAcceleration.GetHashCode();
}
if (StandstillAcceleration != 0D)
{
hash ^= StandstillAcceleration.GetHashCode();
}
if (ThrottleDeadzone != 0D)
{
hash ^= ThrottleDeadzone.GetHashCode();
}
if (BrakeDeadzone != 0D)
{
hash ^= BrakeDeadzone.GetHashCode();
}
if (latErrGainScheduler_ != null)
{
hash ^= LatErrGainScheduler.GetHashCode();
}
if (headingErrGainScheduler_ != null)
{
hash ^= HeadingErrGainScheduler.GetHashCode();
}
if (steerWeightGainScheduler_ != null)
{
hash ^= SteerWeightGainScheduler.GetHashCode();
}
if (feedforwardtermGainScheduler_ != null)
{
hash ^= FeedforwardtermGainScheduler.GetHashCode();
}
if (calibrationTable_ != null)
{
hash ^= CalibrationTable.GetHashCode();
}
return(hash);
}