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 Tipe = "";
if (tipe.SelectedIndex != 0)
{
Tipe = " AND Tipe = '" + Cf.Str(tipe.SelectedValue) + "'";
}
string Status = "";
if (statusB.Checked)
{
Status = " AND Status = 'BARU'";
}
if (statusP.Checked)
{
Status = " AND Status = 'POST'";
}
if (statusV.Checked)
{
Status = " AND Status = 'VOID'";
}
string strSql = "SELECT * "
+ ",(SELECT Nama FROM " + Mi.DbPrefix + "SECURITY..USERNAME WHERE UserID = ISC064_FINANCEAR..MS_TTS.UserID) AS Nama"
+ ",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 1=1 "
+ " AND CONVERT(varchar,TglTTS,112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(varchar,TglTTS,112) <= '" + Cf.Tgl112(Sampai) + "'"
+ UserID
+ CaraBayar
+ Tipe
+ Status
+ " ORDER BY NoTTS";
DataTable rs = Db.Rs(strSql);
Rpt.NoData(rpt, rs, "Tidak ada TTS dengan kriteria seperti tersebut diatas.");
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
string status = rs.Rows[i]["Status"].ToString();
string strike = "";
if (status == "VOID")
{
r.ForeColor = Color.Silver;
strike = "style='text-decoration:line-through'";
}
string bkm = "";
if (status == "POST")
{
//bkm = "<br>BKM:" + rs.Rows[i]["NoBKM"].ToString().PadLeft(7,'0')
bkm = "<br>KWT Manual:" + rs.Rows[i]["ManualBKM"].ToString();
}
c = new TableCell();
c.Text = "<a href=\"javascript:call('" + rs.Rows[i]["NoTTS"] + "')\" " + strike + ">"
+ rs.Rows[i]["NoTTS"].ToString().PadLeft(7, '0') + "</a>"
+ "<br>TTS Manual : " + rs.Rows[i]["ManualTTS"].ToString()
+ "<br><i>" + status + "</i>"
+ bkm;
r.Cells.Add(c);
string userid = "";
if (rs.Rows[i]["Nama"].ToString() == "")
{
userid = rs.Rows[i]["UserID"].ToString();
}
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglTTS"])
+ "<br>" + rs.Rows[i]["Nama"] + userid;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Tipe"] + " No. " + rs.Rows[i]["Ref"]
+ "<br>" + rs.Rows[i]["Unit"]
+ "<br>" + rs.Rows[i]["Customer"];
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Ket"].ToString();
if (rs.Rows[i]["Titip"].ToString() != "")
{
c.Text = c.Text + "<br>Pengelola : " + rs.Rows[i]["Titip"];
}
if (rs.Rows[i]["Tolak"].ToString() != "")
{
c.Text = c.Text + "<br>Tolakan : " + rs.Rows[i]["Tolak"];
}
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["CaraBayar2"].ToString();
if (rs.Rows[i]["CaraBayar"].ToString() == "BG")
{
c.Text = c.Text
+ "<br>" + rs.Rows[i]["NoBG"]
+ "<br><font style='white-space:nowrap'>Tgl. BG : " + Cf.Day(rs.Rows[i]["TglBG"]) + "</font>";
}
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["Total"]);
if ((bool)rs.Rows[i]["Pph"])
{
c.Text = c.Text + "<br>PPH";
}
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
Rpt.Border(r);
rpt.Rows.Add(r);
}
}
private void FillTable()
{
//Func.KontrakHeader(NoKontrak, nokontrakl, unit, customer, agent);
DataTable rsk = Db.Rs("SELECT * FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'");
nokontrakl.Text = rsk.Rows[0]["NoKontrak"].ToString();
unit.Text = rsk.Rows[0]["NoUnit"].ToString();
customer.Text = Db.SingleString("SELECT Nama FROM ISC064_MARKETINGJUAL..MS_CUSTOMER WHERE NoCustomer = '" + rsk.Rows[0]["NoCustomer"].ToString() + "'");
agent.Text = Db.SingleString("SELECT Nama FROM ISC064_MARKETINGJUAL..MS_AGENT WHERE NoaGENT = '" + rsk.Rows[0]["NoAgent"].ToString() + "'");
benefit.Text = Cf.Num(RoundUp(Db.SingleDecimal("SELECT ISNULL(SUM(Benefit-BenefitReal),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN WHERE NoKontrak = '" + NoKontrak + "'")));
cek.Enabled = Convert.ToDecimal(benefit.Text) > 0 ? true : false;
list.Controls.Clear();
rsTagihan = Db.Rs("SELECT * FROM ISC064_MARKETINGJUAL..MS_TAGIHAN WHERE NoKontrak = '" + NoKontrak + "' "
+ " AND Denda > 0 AND (Denda - DendaReal) !=0 AND KPR<>1 AND Tipe <> 'ADM'");
// Rpt.NoData(list, rs, "Tidak ada tagihan untuk kontrak tersebut.");
int nomer = 0;
for (int i = 0; i < rsTagihan.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
//No
Label l;
TextBox bx;
CheckBox cb;
nomer++;
l = new Label();
l.Text = "<tr>"
+ "<td>" + nomer + ".</td>";
list.Controls.Add(l);
l = new Label();
l.Text = ""
+ "<td>" + rsTagihan.Rows[i]["NamaTagihan"] + "</td>";
list.Controls.Add(l);
l = new Label();
l.Text = ""
+ "<td>" + rsTagihan.Rows[i]["Tipe"] + "</td>";
list.Controls.Add(l);
l = new Label();
l.Text = ""
+ "<td>" + Cf.Day(rsTagihan.Rows[i]["TglJT"]) + "</td>";
list.Controls.Add(l);
l = new Label();
l.Text = ""
+ "<td>" + Cf.Num(rsTagihan.Rows[i]["NilaiTagihan"]) + "</td>";
list.Controls.Add(l);
l = new Label();
l.Text = ""
+ "<td>" + Cf.Num(RoundUp(Convert.ToDecimal(rsTagihan.Rows[i]["Denda"]))) + "</td>";
list.Controls.Add(l);
l = new Label();
l.Text = "<td>";
list.Controls.Add(l);
//Nilai
bx = new TextBox();
bx.ID = "real_" + i;
bx.CssClass = "txt_num";
bx.Text = Cf.Num(0);
bx.Attributes["onfocus"] = "tempnum=CalcFocus(this);";
bx.Attributes["onkeyup"] = "CalcType(this,tempnum);";
bx.Attributes["onblur"] = "CalcBlur(this);";
bx.Width = 90;
bx.Attributes["style"] = "font:8pt";
list.Controls.Add(bx);
l = new Label();
l.Text = "</td>";
list.Controls.Add(l);
l = new Label();
decimal Sisa = Convert.ToDecimal(rsTagihan.Rows[i]["Denda"]) - Convert.ToDecimal(rsTagihan.Rows[i]["DendaReal"]) - Convert.ToDecimal(rsTagihan.Rows[i]["NilaiPutihDenda"]) - Convert.ToDecimal(rsTagihan.Rows[i]["AlokasiBenefit"]);
l.Text = ""
+ "<td>" + Cf.Num(RoundUp(Sisa)) + "</td>";
list.Controls.Add(l);
cb = new CheckBox();
cb.ID = "ben_" + i;
cb.Visible = false;
list.Controls.Add(cb);
l = new Label();
l.Text = "</td>";
list.Controls.Add(l);
l = new Label();
l.Text = "</tr>";
list.Controls.Add(l);
}
}
private void FillTb()
{
string strSql = "SELECT * "
+ ",(NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Tb + "..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + Ref + "') ) AS SisaTagihan"
+ " FROM " + Tb + "..MS_TAGIHAN AS MS_TAGIHAN WHERE NoKontrak = '" + Ref + "'"
+ " AND KPR != '1' "
+ " AND (NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Tb + "..MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + Ref + "') ) > 0"
+ " ORDER BY NoUrut, TglJT";
rsTagihan = Db.Rs(strSql);
for (int i = 0; i < rsTagihan.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
Label l;
TextBox t;
l = new Label();
l.Text = "<tr valign=top>"
+ "<td>" + rsTagihan.Rows[i]["NoKontrak"] + "." + rsTagihan.Rows[i]["NoUrut"] + "</td>"
+ "<td>" + rsTagihan.Rows[i]["NamaTagihan"] + "</td>"
+ "<td>" + rsTagihan.Rows[i]["Tipe"] + "</td>"
+ "<td style='white-space:nowrap'>" + Cf.Day(rsTagihan.Rows[i]["TglJT"]) + "</td>"
+ "<td align=right>" + Cf.Num(rsTagihan.Rows[i]["SisaTagihan"]) + "</td>"
+ "<td>"
;
list.Controls.Add(l);
t = new TextBox();
t.ID = "lunas_" + i;
t.Width = 100;
t.CssClass = "txt_num";
t.Attributes["onfocus"] = "tempnum=CalcFocus(this);";
t.Attributes["onkeyup"] = "CalcType(this,tempnum);";
t.Attributes["onblur"] = "CalcBlur(this);hitunggt();";
list.Controls.Add(t);
l = new Label();
l.Text = "</td>"
+ "<td><input type='checkbox' onclick=\"tagihan('" + i + "','" + Cf.Num(rsTagihan.Rows[i]["SisaTagihan"]) + "',this)\"></td>"
+ "</tr>";
list.Controls.Add(l);
}
}
protected void save_Click(object sender, System.EventArgs e)
{
if (valid())
{
System.Text.StringBuilder log = new System.Text.StringBuilder();
int c = 0;
for (int i = 0; i < rs.Rows.Count; i++)
{
TextBox min = (TextBox)list.FindControl("min_" + i);
TextBox t = (TextBox)list.FindControl("pl_" + i);
TextBox tgl = (TextBox)list.FindControl("tgl_" + i);
TextBox gimmick = (TextBox)list.FindControl("gimmick_" + i);
TextBox kavling = (TextBox)list.FindControl("plkav_" + i);
TextBox bphtb = (TextBox)list.FindControl("bphtb_" + i);
TextBox bsurat = (TextBox)list.FindControl("bsurat_" + i);
TextBox bproses = (TextBox)list.FindControl("bproses_" + i);
TextBox blain = (TextBox)list.FindControl("blain_" + i);
TextBox lainlain = (TextBox)list.FindControl("lainlain_" + i);
CheckBox cbrumah = (CheckBox)list.FindControl("cbrumah_" + i);
CheckBox cbkavling = (CheckBox)list.FindControl("cbkavling_" + i);
decimal plmin = Convert.ToDecimal(min.Text);
decimal plkav = Convert.ToDecimal(kavling.Text);
decimal BPHTB = Convert.ToDecimal(bphtb.Text);
decimal BiayaSurat = Convert.ToDecimal(bsurat.Text);
decimal BiayaProses = Convert.ToDecimal(bproses.Text);
decimal BiayaLain = Convert.ToDecimal(blain.Text);
decimal pl = Convert.ToDecimal(t.Text);
DateTime Tgl = Convert.ToDateTime(tgl.Text);
decimal BiayaTambahanGimmick = 0;
decimal BiayaTambahanLainLain = 0;
int defaultpl = 0;
if (cbrumah.Checked)
{
defaultpl = 1;
}
else if (cbkavling.Checked)
{
defaultpl = 2;
}
if (pl != 0)
{
Db.Execute("EXEC spUnitPriceList "
+ "'" + rs.Rows[i]["NoStock"] + "'"
+ "," + plmin
+ "," + pl
);
}
Db.Execute("UPDATE MS_UNIT SET TambahanHargaGimmick = '" + BiayaTambahanGimmick + "'"
+ ", TambahanHargaLainLain = '" + BiayaTambahanLainLain + "'"
+ ", TglPriceList = '" + Tgl + "'"
+ ", PricelistKavling = " + plkav
+ ", BiayaBPHTB = " + BPHTB
+ ", BiayaSurat = " + BiayaSurat
+ ", BiayaProses = " + BiayaProses
+ ", BiayaLainLain = " + BiayaLain
+ ", DefaultPL = " + defaultpl
+ " WHERE NoStock = '" + rs.Rows[i]["NoStock"] + "'");
if (Convert.ToDecimal(rs.Rows[i]["PriceListMin"]) != plmin ||
Convert.ToDecimal(rs.Rows[i]["PriceList"]) != pl
)
{
Db.Execute("EXEC spPriceListHistory"
+ " '" + rs.Rows[i]["NoStock"] + "'"
+ ", " + plmin
+ ", " + pl
+ ", " + plkav
+ ", '" + Tgl + "'"
);
c++;
log.Append(Cf.Str(rs.Rows[i]["NoStock"]) + " (" + Cf.Str(rs.Rows[i]["NoUnit"]) + ") "
+ " " + Cf.Num(rs.Rows[i]["PriceListMin"]) + " / " + Cf.Num(rs.Rows[i]["PriceList"])
+ " --> " + Cf.Num(plmin) + " / " + Cf.Num(pl)
+ "<br>"
);
}
}
if (c != 0)
{
string Ket = "Jumlah Unit : " + c
+ "<br><br>=============DATA :<br>"
+ log.ToString()
;
Db.Execute("EXEC spLogUnit "
+ " 'SPL'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Ket + "'"
+ ",''"
);
}
Response.Redirect("PL.aspx?done=" + c + "&project=" + Project);
}
}
private void Fill()
{
string nProject = "";
if (Project != "SEMUA")
{
nProject = " AND a.Project IN ('" + Project.Replace(",", "','") + "')";
}
string nPerusahaan = "";
if (Perusahaan != "SEMUA")
{
nPerusahaan = " AND a.Pers = '" + Perusahaan + "'";
}
DateTime Dari = Db.SingleTime("SELECT FilterDari FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
DateTime Sampai = Db.SingleTime("SELECT FilterSampai FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
string nStatus = "";
if (StatusA != "")
{
nStatus = " AND (a.FOBO1 = '1' OR a.FOBO2 = '1')";
}
if (StatusB != "")
{
nStatus = " AND (a.FOBO1 = '0' AND a.FOBO2 = '0')";
}
string nAkad = "";
string B = "";
if (B != "")
{
nAkad = " AND a.StatusAkad != 'SELESAI'";
}
string D = "";
if (D != "")
{
nAkad = " AND a.StatusAkad = 'SELESAI'";
}
string tgl = "";
if (Tanggal2 == "TglAkad")
{
tgl = "TglAkad";
}
string Tanggal = "";
if (D != "")
{
Tanggal = " AND CONVERT(varchar," + tgl + ",112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(varchar," + tgl + ",112) <= '" + Cf.Tgl112(Sampai) + "'";
}
string nLokasi = "";
if (Lokasi != "SEMUA")
{
nLokasi = " AND MS_UNIT.Lokasi = '" + Cf.Str(Lokasi) + "'";
}
string BankKPR = "";
if (Rekening != "SEMUA")
{
BankKPR = " AND BankKPR = '" + Rekening + "'";
}
string strSql = "SELECT a.*, b.*"
+ " FROM MS_KONTRAK a"
+ " INNER JOIN MS_CUSTOMER b ON a.NoCustomer = b.NoCustomer"
+ " WHERE a.Status = 'A'"
+ " AND a.CaraBayar = 'KPR'"
+ nProject
+ nPerusahaan
//+ " AND StatusAkad = 'SELESAI'"
+ nLokasi
+ Tanggal
+ BankKPR
+ nStatus
+ nAkad
+ "ORDER BY TglAkad"
;
DataTable rs = Db.Rs(strSql);
decimal t1 = 0, t2 = 0, t3 = 0, t4 = 0, t5 = 0, t6 = 0;
decimal PotensiKPR = 0;
decimal RealisasiAkad = 0;
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
c = new TableCell();
c.Text = (i + 1).ToString();
c.HorizontalAlign = HorizontalAlign.Center;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoAkad"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglAkad"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoKontrak"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Nama"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NPWP"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["KTP1"].ToString()
+ "</br>" + rs.Rows[i]["KTP2"].ToString()
+ "</br>" + rs.Rows[i]["KTP3"].ToString()
+ "</br>" + rs.Rows[i]["KTP4"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT Nama FROM MS_AGENT WHERE NoAgent = '" + rs.Rows[i]["NoAgent"].ToString() + "' ");
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
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 = Cf.Num(Math.Round(Convert.ToDecimal(rs.Rows[i]["Luas"]), 2));
c.HorizontalAlign = HorizontalAlign.Right;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Jenis"].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();
c.Text = Cf.Num(rs.Rows[i]["Gross"]);
c.HorizontalAlign = HorizontalAlign.Right;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round(Convert.ToDecimal(rs.Rows[i]["DiskonRupiah"]), 0));
c.HorizontalAlign = HorizontalAlign.Right;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["NilaiKontrak"]);
c.HorizontalAlign = HorizontalAlign.Right;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
PotensiKPR = Convert.ToDecimal(rs.Rows[i]["NilaiPengajuan"]);
c.Text = Cf.Num(PotensiKPR);
c.HorizontalAlign = HorizontalAlign.Right;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
RealisasiAkad = Convert.ToDecimal(rs.Rows[i]["ApprovalKPR"]);
c.Text = Cf.Num(RealisasiAkad);
c.HorizontalAlign = HorizontalAlign.Right;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
rpt.Rows.Add(r);
t1 += Convert.ToDecimal(rs.Rows[i]["Gross"]);
t2 += Convert.ToDecimal(rs.Rows[i]["DiskonRupiah"]);
t3 += Convert.ToDecimal(rs.Rows[i]["NilaiKontrak"]);
t4 += PotensiKPR;
t5 += RealisasiAkad;
t6 += Convert.ToDecimal(rs.Rows[i]["Luas"]);
}
SubTotal(t1, t2, t3, t4, t5, t6);
}
private void Fill()
{
string NoKontrak = Convert.ToString(Request.QueryString["NoKontrak"]);
string aa = "";
if (UserAgent() > 0)
{
aa = " AND a.NoAgent = " + UserAgent();
}
string strSql = "SELECT a.*, b.*, c.Nama AS NamaAgent, d.LuasSG"
+ " FROM ISC064_MARKETINGJUAL..MS_KONTRAK a"
+ " INNER JOIN ISC064_MARKETINGJUAL..MS_CUSTOMER b ON a.NoCustomer = b.NoCustomer"
+ " INNER JOIN ISC064_MARKETINGJUAL..MS_AGENT c ON a.NoAgent = c.NoAgent"
+ " INNER JOIN ISC064_MARKETINGJUAL..MS_UNIT d ON a.NoStock = d.NoStock"
+ " WHERE NoKontrak = '" + NoKontrak + "' "
+ aa
;
DataTable rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
Label l;
l = new Label();
l.Text = "<table width='500px' class=''>";
report.Controls.Add(l);
//HEADER
l = new Label();
l.Text = "<tr><td colspan='8'>" + Mi.Pt + "<h1 class='title'>KARTU PIUTANG KPR<br /></h1></td></tr>";
report.Controls.Add(l);
//TYPE
l = new Label();
l.Text = "<tr><td width='80px'>TYPE</td><td>:</td><td>" + rs.Rows[i]["Jenis"].ToString() + "</td>";
report.Controls.Add(l);
//LUAS
l = new Label();
l.Text = "<td width='150px'>LUAS UNIT SG</td><td>:</td><td>" + Cf.Num(rs.Rows[i]["LuasSG"]) + " m<sup>2</sup></td></tr>";
report.Controls.Add(l);
//UNIT
l = new Label();
l.Text = "<tr><td style='color: red; font-weight: bold;'>NO UNIT</td><td style='color: red; font-weight: bold;'>:</td><td style='color: red; font-weight: bold;'>" + Cf.Pk(rs.Rows[i]["NoUnit"]) + "</td>";
report.Controls.Add(l);
//HARGA JUAL
l = new Label();
l.Text = "<td style='color: red; font-weight: bold;' width='150px'>NILAI KONTRAK</td><td style='color: red; font-weight: bold;'>:</td><td style='color: red; font-weight: bold;' align='right'>" + Cf.Num(rs.Rows[i]["NilaiKontrak"]) + "</td></tr>";
report.Controls.Add(l);
//NAMA
l = new Label();
l.Text = "<tr><td>NAMA</td><td>:</td><td>" + Cf.Str(rs.Rows[0]["Nama"]) + "</td>";
report.Controls.Add(l);
//TAGIHAN
l = new Label();
decimal Tagihan = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiTagihan),0) FROM ISC064_MARKETINGJUAL..MS_TAGIHAN_KPA WHERE NoKontrak = '" + Cf.Str(NoKontrak) + "' AND Tipe IN ('BF','DP','ANG')");
l.Text = "<td>TAGIHAN</td><td>:</td><td align='right'>" + Cf.Num(Tagihan) + "</td>";
report.Controls.Add(l);
//ALAMAT
l = new Label();
l.Text = "<tr><td valign='top'>ALAMAT</td><td valign='top'>:</td><td>" + Cf.Str(rs.Rows[i]["KTP1"]) + " " + Cf.Str(rs.Rows[i]["KTP2"]) + "<br />" + Cf.Str(rs.Rows[i]["KTP3"]) + "</td>";
report.Controls.Add(l);
//BIAYA
l = new Label();
decimal Biaya = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiTagihan),0) FROM ISC064_MARKETINGJUAL..MS_TAGIHAN_KPA WHERE NoKontrak = '" + Cf.Str(NoKontrak) + "' AND Tipe IN ('ADM')");
l.Text = "<td>BIAYA</td><td>:</td><td align='right'>" + Cf.Num(Biaya) + "</td></tr>";
report.Controls.Add(l);
//MARKETING
l = new Label();
l.Text = "<tr><td>MARKETING</td><td>:</td><td>" + Cf.Str(rs.Rows[i]["NamaAgent"]) + "</td>";
report.Controls.Add(l);
//TAGIHAN BIAYA
l = new Label();
decimal TagihanBiaya = Tagihan + Biaya;
l.Text = "<td>TAGIHAN + BIAYA</td><td>:</td><td align='right'>" + Cf.Num(TagihanBiaya) + "</td></tr>";
report.Controls.Add(l);
l = new Label();
l.Text = "<tr><td colspan='3'>";
report.Controls.Add(l);
//Cara Bayar
l = new Label();
l.Text = "<tr><td>CARA BAYAR</td><td>:</td><td>" + Cf.Str(rs.Rows[i]["CaraBayar"]) + "</td>";
report.Controls.Add(l);
//PEMBAYARAN
l = new Label();
decimal Pembayaran = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan),0) FROM ISC064_MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak = '" + Cf.Str(NoKontrak) + "' AND NoTagihan <> 0");
l.Text = "<td>PEMBAYARAN</td><td>:</td><td align='right'>" + Cf.Num(Pembayaran) + "</td></tr>";
report.Controls.Add(l);
//DPP
l = new Label();
l.Text = "<tr><td>DPP</td><td>:</td><td>" + Cf.Num(rs.Rows[i]["NilaiDPP"]) + "</td>";
report.Controls.Add(l);
//PELUNASAN
l = new Label();
decimal Pelunasan = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan),0) FROM ISC064_MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak = '" + Cf.Str(NoKontrak) + "' AND NoTagihan <> 0 AND SudahCair = 1");
decimal PersenLunas = Db.SingleDecimal("SELECT PersenLunas FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak = '" + Cf.Str(NoKontrak) + "'");
l.Text = "<td>PERSENTASE</td><td>:</td><td align='right'>" + Cf.Num(PersenLunas) + "%</td></tr>";
report.Controls.Add(l);
//PPN
l = new Label();
l.Text = "<tr><td>PPN</td><td>:</td><td>" + Cf.Num(rs.Rows[i]["NilaiPPN"]) + "</td>";
report.Controls.Add(l);
//Diskon
l = new Label();
l.Text = "<td>Diskon</td><td>:</td><td align='right'>" + Cf.Num(rs.Rows[i]["DiskonRupiah"]) + "</td></tr>";
report.Controls.Add(l);
//Status
l = new Label();
string statdb = Db.SingleString("SELECT status FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak = '" + Cf.Str(NoKontrak) + "'");
string status = "";
if (statdb == "A")
{
status = "Aktif";
}
else
{
status = "Batal";
}
l.Text = "<tr><td>Status</td><td>:</td><td>" + status + "</td>";
report.Controls.Add(l);
l = new Label();
l.Text = "</table><br />";
report.Controls.Add(l);
FillTagihan(Cf.Pk(rs.Rows[i]["NoKontrak"]));
FillBottom(Cf.Pk(rs.Rows[i]["NoKontrak"]));
}
}
private void SetBaris(TextBox Kali, string Tipe, string Nama
, TextBox interval1, TextBox interval2
, CheckBox hari1, CheckBox hari2
, TextBox nominal, CheckBox persen
)
{
int count = Convert.ToInt32(Kali.Text);
int index = rpt.Rows.Count - 1;
DateTime Tgl = Convert.ToDateTime(tgl.Text); //tanggal kontrak
try
{
if (Tipe == "BF" && bftgl.Text != "")
{
Tgl = Convert.ToDateTime(bftgl.Text);
}
else if (Tipe == "DP" && dptgl.Text != "")
{
Tgl = Convert.ToDateTime(dptgl.Text);
}
else if (Tipe == "ANG" && angtgl.Text != "")
{
Tgl = Convert.ToDateTime(angtgl.Text);
}
else
{
Tgl = Convert.ToDateTime(rpt.Rows[rpt.Rows.Count - 1].Cells[3].Text);
}
}
catch { }
for (int i = 0; i < count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
index++;
TableRow r = new TableRow();
TableCell c;
c = new TableCell();
c.Text = index.ToString() + ".";
r.Cells.Add(c);
c = new TableCell();
c.Text = Tipe;
r.Cells.Add(c);
c = new TableCell();
decimal cc = Db.SingleDecimal("SELECT COUNT(*) FROM MS_TAGIHAN WHERE tipe='" + Tipe + "' AND nourut in (select notagihan from ms_pelunasan where NoKontrak = '" + NoKontrak + "' AND NilaiPelunasan > 0) AND Nokontrak = '" + NoKontrak + "'");
if (carabayar2.SelectedValue == "KPR")
{
if (Tipe == "ANG")
{
if (i == count - 1)
{
c.Text = "PENCAIRAN KPR";
}
else
{
c.Text = Nama + " " + (cc + i + 1);
}
}
else
{
c.Text = Nama + " " + (cc + i + 1);
}
}
else
{
c.Text = Nama + " " + (cc + i + 1);
}
//c.Text = Nama + " " + (cc + i + 1);
r.Cells.Add(c);
//c = new TableCell();
//c.Text = Nama + " " + (i + 1);
//r.Cells.Add(c);
if (i == 0)
{
int interval = Convert.ToInt32(interval2.Text);
if (Tipe == "BF" && bftgl.Text != "")
{
interval = 0;
}
else if (Tipe == "DP" && dptgl.Text != "")
{
interval = 0;
}
else if (Tipe == "ANG" && angtgl.Text != "")
{
interval = 0;
}
else
{
//pertama
if (hari2.Checked)
{
Tgl = Tgl.AddDays(interval);
}
else
{
Tgl = Tgl.AddMonths(interval);
}
}
}
else
{
int interval = Convert.ToInt32(interval1.Text);
//pertama
if (hari1.Checked)
{
Tgl = Tgl.AddDays(interval);
}
else
{
int h = Tgl.Day;
if (Tipe == "BF" && bftgl.Text != "")
{
h = Convert.ToDateTime(bftgl.Text).Day;
}
else if (Tipe == "DP" && dptgl.Text != "")
{
h = Convert.ToDateTime(dptgl.Text).Day;
}
else if (Tipe == "ANG" && angtgl.Text != "")
{
h = Convert.ToDateTime(angtgl.Text).Day;
}
Tgl = Tgl.AddMonths(interval);
if (h != Tgl.Day && h <= DateTime.DaysInMonth(Tgl.Year, Tgl.Month))
{
Tgl = new DateTime(Tgl.Year, Tgl.Month, h);
}
}
}
c = new TableCell();
c.Text = Cf.Day(Tgl);
r.Cells.Add(c);
decimal Nominal = Convert.ToDecimal(nominal.Text);
decimal Netto = Convert.ToDecimal(netto.Text);
if (persen.Checked)
{
Nominal = Netto * (Nominal / 100);
}
if (Tipe == "ANG" && i == (count - 1))
{
decimal aa = Convert.ToDecimal(netto.Text);
decimal nilaiDP = 0, nilaiBF = 0, nilaiANG = 0;
for (int j = 1; j < rpt.Rows.Count; j++)
{
if (!Response.IsClientConnected)
{
break;
}
if (rpt.Rows[j].Cells[1].Text == "BF")
{
nilaiBF += Convert.ToDecimal(rpt.Rows[j].Cells[4].Text);
}
else if (rpt.Rows[j].Cells[1].Text == "DP")
{
nilaiDP += Convert.ToDecimal(rpt.Rows[j].Cells[4].Text);
}
else if (rpt.Rows[j].Cells[1].Text == "ANG")
{
nilaiANG += Convert.ToDecimal(rpt.Rows[j].Cells[4].Text);
}
}
Nominal = RoundThousand(aa - RoundThousand((nilaiDP + nilaiANG)));
}
else
{
if (rounding.Checked)
{
decimal rounded = 0;
Nominal = Math.Round(Nominal / count);
}
else
{
Nominal = Nominal / count;
}
}
c = new TableCell();
c.Text = Cf.Num(Nominal);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = "";
if (dp1potong.Checked && i == 0 && Tipe == "DP")
{
c.Text = "MIN";
}
if (ang1potong.Checked && i == 0 && Tipe == "ANG")
{
c.Text = "MIN";
}
if (dpspotong.Checked && Tipe == "DP")
{
c.Text = "MIN";
}
if (angspotong.Checked && Tipe == "ANG")
{
c.Text = "MIN";
}
r.Cells.Add(c);
Rpt.Border(r);
rpt.Rows.Add(r);
}
}
private void fill()
{
string[] id = NoID.Split(';', ' ');
int count = id.Length;
for (int i = 0; i < count; i++)
{
if (id[i] != "")
{
string strSql = "SELECT * FROM MS_TTS WHERE NOTTS = " + id[i];
DataTable rs = Db.Rs(strSql);
//Result
HtmlTableRow r = new HtmlTableRow();
HtmlTableCell c;
TextBox t = new TextBox();
c = new HtmlTableCell();
c.InnerHtml = rs.Rows[0]["Ket"].ToString();
r.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = Cf.Day(rs.Rows[0]["TglTTS"]);
r.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = "<a href=\"javascript:call('" + id[i] + "')\">"
+ id[i].ToString().PadLeft(7, '0') + "</a>"
+ "<br /><i>POST</i>";
//+ "<br />BKM: " + rs.Rows[0]["NoBKM"];
c.ID = id[i].ToString();
r.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = "JUAL No. " + rs.Rows[0]["Ref"]
+ "<br />" + rs.Rows[0]["Unit"]
+ "<br />" + rs.Rows[0]["Customer"];
r.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = Cf.Num(rs.Rows[0]["Total"]);
//c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
t = new TextBox();
t.ID = "no_" + i;
t.Width = 60;
c = new HtmlTableCell();
c.Controls.Add(t);
r.Cells.Add(c);
t = new TextBox();
t.ID = "tts_" + i;
t.Visible = false;
t.Text = id[i];
c.Controls.Add(t);
r.Cells.Add(c);
ph.Controls.Add(r);
}
}
}
private void Fill()
{
//DateTime Dari = Convert.ToDateTime(dari.Text);
decimal t1 = 0;
decimal t2 = 0;
decimal t3 = 0;
decimal t4 = 0;
decimal t5 = 0;
decimal t6 = 0;
string strSql = "SELECT a.*, a.Status, b.Nama AS Cust"
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK a"
+ " INNER JOIN " + Mi.DbPrefix + "MARKETINGJUAL..MS_CUSTOMER b ON a.NoCustomer = b.NoCustomer"
+ " INNER JOIN " + Mi.DbPrefix + "MARKETINGJUAL..MS_UNIT c ON a.NoUnit = c.NoUnit"
+ " WHERE 1=1 "
+ " ORDER BY a.TGLKONTRAK";
DataTable rs = Db.Rs(strSql);
TableRow trow = new TableRow();
TableCell tc;
trow.BackColor = Color.Gray;
trow.HorizontalAlign = HorizontalAlign.Center;
tc = new TableCell();
tc.Text = "NO.";
tc.RowSpan = 2;
trow.Cells.Add(tc);
tc = new TableCell();
tc.Text = "CUSTOMER";
tc.RowSpan = 2;
trow.Cells.Add(tc);
tc = new TableCell();
tc.Text = "TGL BF";
tc.RowSpan = 2;
trow.Cells.Add(tc);
tc = new TableCell();
tc.Text = "TYPE";
tc.ColumnSpan = 4;
trow.Cells.Add(tc);
tc = new TableCell();
tc.Text = "HARGA";
tc.RowSpan = 2;
trow.Cells.Add(tc);
tc = new TableCell();
tc.Text = "PPN";
tc.RowSpan = 2;
trow.Cells.Add(tc);
tc = new TableCell();
tc.Text = "TOTAL HARGA";
tc.RowSpan = 2;
trow.Cells.Add(tc);
tc = new TableCell();
tc.Text = "TYPE OF PAYMENT";
tc.RowSpan = 2;
trow.Cells.Add(tc);
tc = new TableCell();
tc.Text = "Akumulasi Sebelum " + Cf.Monthname(Convert.ToInt32(bulandari.SelectedValue)) + " " + Convert.ToInt32(tahundari.Text);
tc.RowSpan = 2;
trow.Cells.Add(tc);
DateTime awal = new DateTime(Convert.ToInt32(tahundari.Text), Convert.ToInt32(bulandari.SelectedValue), 1);
DateTime akhir = Cf.AkhirBulan(Convert.ToInt32(bulansampai.SelectedValue), Convert.ToInt32(tahunsampai.Text));
var listOfMonths = new List <string>();
var list = new List <string>();
while (awal <= akhir)
{
listOfMonths.Add(Cf.Monthname(awal.ToString("MMMM")) + " " + awal.ToString("yyyy"));
list.Add(awal.ToString("MM-yyyy"));
awal = awal.AddMonths(1);
}
foreach (var r in listOfMonths)
{
tc = new TableCell();
tc.Text = r.ToString();
tc.ColumnSpan = 4;
trow.Cells.Add(tc);
}
tc = new TableCell();
tc.Text = "Total Penerimaan Hingga " + Cf.Monthname(Convert.ToInt32(bulansampai.SelectedValue)) + " " + Convert.ToInt32(tahunsampai.Text);
tc.RowSpan = 2;
trow.Cells.Add(tc);
tc = new TableCell();
tc.Text = "";
tc.RowSpan = 2;
trow.Cells.Add(tc);
tc = new TableCell();
tc.Text = "Persentase Pelunasan";
tc.RowSpan = 2;
trow.Cells.Add(tc);
tc = new TableCell();
tc.Text = "Sisa Angsuran";
tc.RowSpan = 2;
trow.Cells.Add(tc);
rpt.Rows.Add(trow);
TableRow trow1 = new TableRow();
TableCell tc1;
trow1.BackColor = Color.Gray;
trow1.HorizontalAlign = HorizontalAlign.Center;
tc1 = new TableCell();
tc1.Text = "NO UNIT";
trow1.Cells.Add(tc1);
tc1 = new TableCell();
tc1.Text = "TOWER";
trow1.Cells.Add(tc1);
tc1 = new TableCell();
tc1.Text = "LANTAI";
trow1.Cells.Add(tc1);
tc1 = new TableCell();
tc1.Text = "LUAS";
trow1.Cells.Add(tc1);
foreach (var r in listOfMonths)
{
tc1 = new TableCell();
tc1.Text = "1 - 7";
trow1.Cells.Add(tc1);
tc1 = new TableCell();
tc1.Text = "8 - 14";
trow1.Cells.Add(tc1);
tc1 = new TableCell();
tc1.Text = "15 - 21";
trow1.Cells.Add(tc1);
tc1 = new TableCell();
tc1.Text = " >= 22";
trow1.Cells.Add(tc1);
}
rpt.Rows.Add(trow1);
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
c = new TableCell();
c.Text = (i + 1).ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Cust"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglKontrak"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoUnit"].ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Lokasi"].ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
string[] x = Cf.SplitByString(rs.Rows[i]["NoUnit"].ToString(), "/");
c = new TableCell();
c.Text = x[1];
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["Luas"]);
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Math.Round(Convert.ToDecimal(rs.Rows[i]["NilaiDPP"])).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Math.Round(Convert.ToDecimal(rs.Rows[i]["NilaiPPN"])).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Math.Round(Convert.ToDecimal(rs.Rows[i]["NilaiKontrak"])).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Skema"].ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
DateTime awala = new DateTime(Convert.ToInt32(tahundari.Text), Convert.ToInt32(bulandari.SelectedValue), 1);
c = new TableCell();
c.Text = Math.Round(Db.SingleDecimal("SELECT ISNULL(SUM(NILAIPELUNASAN),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE TglPelunasan < '" + Cf.Tgl112(awala) + "' AND NoKontrak='" + rs.Rows[i]["NoKontrak"].ToString() + "'")).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
foreach (var u in list)
{
string[] a = Cf.SplitByString(u.ToString(), "-");
DateTime week1a = new DateTime(Convert.ToInt32(a[1]), Convert.ToInt32(a[0]), 1);
DateTime week1b = new DateTime(Convert.ToInt32(a[1]), Convert.ToInt32(a[0]), 7);
DateTime week2a = new DateTime(Convert.ToInt32(a[1]), Convert.ToInt32(a[0]), 8);
DateTime week2b = new DateTime(Convert.ToInt32(a[1]), Convert.ToInt32(a[0]), 14);
DateTime week3a = new DateTime(Convert.ToInt32(a[1]), Convert.ToInt32(a[0]), 15);
DateTime week3b = new DateTime(Convert.ToInt32(a[1]), Convert.ToInt32(a[0]), 21);
DateTime week4a = new DateTime(Convert.ToInt32(a[1]), Convert.ToInt32(a[0]), 22);
DateTime week4b = Cf.AkhirBulan(Convert.ToInt32(a[0]), Convert.ToInt32(a[1]));
c = new TableCell();
c.Text = Math.Round(Db.SingleDecimal("SELECT ISNULL(SUM(NILAIPELUNASAN),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE TglPelunasan <= '" + Cf.Tgl112(week1b) + "' AND TglPelunasan >= '" + Cf.Tgl112(week1a) + "' AND NoKontrak='" + rs.Rows[i]["NoKontrak"].ToString() + "'")).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Math.Round(Db.SingleDecimal("SELECT ISNULL(SUM(NILAIPELUNASAN),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE TglPelunasan <= '" + Cf.Tgl112(week2b) + "' AND TglPelunasan >= '" + Cf.Tgl112(week2a) + "' AND NoKontrak='" + rs.Rows[i]["NoKontrak"].ToString() + "'")).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Math.Round(Db.SingleDecimal("SELECT ISNULL(SUM(NILAIPELUNASAN),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE TglPelunasan <= '" + Cf.Tgl112(week3b) + "' AND TglPelunasan >= '" + Cf.Tgl112(week3a) + "' AND NoKontrak='" + rs.Rows[i]["NoKontrak"].ToString() + "'")).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Math.Round(Db.SingleDecimal("SELECT ISNULL(SUM(NILAIPELUNASAN),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE TglPelunasan <= '" + Cf.Tgl112(week4b) + "' AND TglPelunasan >= '" + Cf.Tgl112(week4a) + "' AND NoKontrak='" + rs.Rows[i]["NoKontrak"].ToString() + "'")).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
}
c = new TableCell();
c.Text = Math.Round(Db.SingleDecimal("SELECT ISNULL(SUM(NILAIPELUNASAN),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE TglPelunasan < '" + Cf.Tgl112(akhir) + "' AND NoKontrak='" + rs.Rows[i]["NoKontrak"].ToString() + "'")).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = "";
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Math.Round(Convert.ToDecimal(rs.Rows[i]["PersenLunas"])).ToString() + "%";
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
decimal tagihan = Db.SingleDecimal("SELECT ISNULL(SUM(NILAITAGIHAN),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN WHERE NoKontrak='" + rs.Rows[i]["NoKontrak"].ToString() + "'");
decimal pelunasan = Db.SingleDecimal("SELECT ISNULL(SUM(NILAIPELUNASAN),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak='" + rs.Rows[i]["NoKontrak"].ToString() + "'");
decimal sisa = tagihan - pelunasan;
c = new TableCell();
c.Text = Math.Round(sisa).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
rpt.Rows.Add(r);
}
}
protected void SaveAlokasi_Click(object sender, EventArgs e)
{
if (validBayar && ValidNilai())
{
Table x = new Table();
string query = " SELECT (a.Nokontrak + '.' + Cast(a.NoUrut AS VARCHAR)) AS NoTagihan, "
+ " a.NamaTagihan, "
+ " a.Tipe, "
+ " a.TglJT, "
+ " a.NoUrut, "
+ " (a.NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak = a.NoKontrak AND NoTagihan = a.NoUrut)) AS SisaTagihan "
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN a "
+ " WHERE 1=1 "
+ " AND a.Nokontrak = '" + NoKontrak + "'"
+ " AND (a.NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak = a.NoKontrak AND NoTagihan = a.NoUrut)) > 0 ";
DataTable rs = Db.Rs(query);
DateTime TglAlokasi = DateTime.Now;
string Project = Db.SingleString("SELECT Project FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'");
string NamaProject = Db.SingleString("SELECT Nama FROM " + Mi.DbPrefix + "SECURITY..REF_PROJECT WHERE Project = '" + Project + "'");
//Numerator
string NoMEMO2 = Numerator.MEMO(TglAlokasi.Month, TglAlokasi.Year, Project);
Db.Execute("EXEC spMEMORegistrasi"
+ " '" + TglAlokasi + "'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'JUAL'"
+ ",'" + NoKontrak + "'"
+ ",'" + unit.Text + "'"
+ ",'" + customer.Text + "'"
+ ",'AL'"
+ ",''"
+ ",0"
);
int noAlokasi = Db.SingleInteger("SELECT TOP 1 NoMEMO FROM MS_MEMO ORDER BY NoMEMO DESC");
Db.Execute("UPDATE MS_MEMO SET NoMEMO2 = '" + NoMEMO2 + "', Project = '" + Project + "', NamaProject = '" + NamaProject + "' WHERE NoMEMO ='" + noAlokasi + "'");
System.Text.StringBuilder alokasi = new System.Text.StringBuilder();
for (int i = 0; i < rs.Rows.Count; i++)
{
TextBox lunas = (TextBox)list.FindControl("bayar_" + i);
TextBox tgl = (TextBox)list.FindControl("tgl_" + i);
HtmlTableCell nourut = (HtmlTableCell)list.FindControl("NoTagihan_" + i);
if (lunas != null && Cf.isMoney(lunas))
{
int NoTagihan = Convert.ToInt16(nourut.InnerHtml);
string NamaTagihan = Cf.Str(rs.Rows[i]["NamaTagihan"])
+ " (" + rs.Rows[i]["Tipe"] + ")";
decimal Nilai = Convert.ToDecimal(lunas.Text);
DateTime Tgl = Convert.ToDateTime(tgl.Text);
Db.Execute("EXEC spMEMOAlokasi "
+ " '" + noAlokasi + "'"
+ ", " + NoTagihan
+ ", " + Nilai
);
alokasi.Append(NamaTagihan + " " + Cf.Num(Nilai) + "<br>");
}
}
for (int i = 0; i < rs.Rows.Count; i++)
{
TextBox lunas = (TextBox)list.FindControl("bayar_" + i);
TextBox tgl = (TextBox)list.FindControl("tgl_" + i);
if (lunas != null && Cf.isMoney(lunas))
{
int NoTagihan = (int)rs.Rows[i]["NoUrut"];
string NamaTagihan = Cf.Str(rs.Rows[i]["NamaTagihan"])
+ " (" + rs.Rows[i]["Tipe"] + ")";
decimal Nilai = Convert.ToDecimal(lunas.Text);
DateTime Tgl = Convert.ToDateTime(tgl.Text);
Db.Execute("UPDATE ISC064_MARKETINGJUAL..MS_PELUNASAN"
+ " SET"
+ " TglPelunasan ='" + Tgl + "'"
+ " ,SudahCair = 1"
+ " WHERE NoKontrak='" + NoKontrak + "' AND NoMemo='" + noAlokasi + "' AND NoTagihan='" + NoTagihan + "'"
);
Db.Execute("UPDATE MS_MEMO SET Status='POST', TglBKM=TglMemo WHERE NoMemo='" + noAlokasi + "'");
}
}
DataTable rsLog = Db.Rs("SELECT "
+ " CONVERT(varchar, TglMEMO, 106) AS [Tanggal]"
+ ",Tipe"
+ ",Ref AS [Ref.]"
+ ",Unit"
+ ",Customer"
+ ",CaraBayar AS [Cara Bayar]"
+ ",Ket AS [Keterangan]"
+ ",Total"
+ ",NoBG AS [No. BG]"
+ ",CONVERT(varchar, TglBG, 106) AS [Tanggal BG]"
+ ", Acc AS [Rekening Bank]"
+ " FROM MS_MEMO WHERE NoMEMO = " + noAlokasi);
string KetLog = Cf.LogCapture(rsLog)
+ "<br>***ALOKASI PEMBAYARAN:<br>"
+ alokasi.ToString();
Db.Execute("EXEC spLogMEMO"
+ " 'REGIS'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + KetLog + "'"
+ ",'" + noAlokasi.ToString().PadLeft(7, '0') + "'"
);
decimal LogID = Db.SingleDecimal("SELECT TOP 1 LogID FROM MS_MEMO_LOG ORDER BY LogID DESC");
Db.Execute("UPDATE MS_MEMO_LOG SET Project = '" + Project + "' WHERE LogID = " + LogID);
Db.Execute("EXEC ISC064_MARKETINGJUAL..spProsentasePelunasan '" + NoKontrak + "'");
Db.Execute("UPDATE ISC064_MARKETINGJUAL..MS_KONTRAK SET FlagMemo=1 WHERE NoKontrak='" + NoKontrak + "'");
Response.Redirect("CbRegistrasi1.aspx?done=" + noAlokasi + "&memo=1");
}
}
private void Fill()
{
Js.NumberFormat(sisa);
Js.NumberFormat(lb);
Func.KontrakHeader(NoKontrak, nokontrakl, unit, customer, agent);
string strSql = "SELECT * FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'";
DataTable rs = Db.Rs(strSql);
unit.Text = Db.SingleString("SELECT NoUnit "
+ " FROM ISC064_MARKETINGJUAL..MS_KONTRAK "
+ " WHERE NoKontrak = '" + NoKontrak + "'");
customer.Text = Db.SingleString("SELECT Nama "
+ " FROM ISC064_MARKETINGJUAL..MS_KONTRAK AS MS_KONTRAK "
+ " INNER JOIN ISC064_MARKETINGJUAL..MS_CUSTOMER AS MS_CUSTOMER "
+ " ON MS_KONTRAK.NoCustomer = MS_CUSTOMER.NoCustomer"
+ " WHERE NoKontrak = '" + NoKontrak + "'");
if (rs.Rows.Count == 0)
{
Response.Redirect("/CustomError/Deleted.html");
}
else
{
decimal nilaitagihan = 0;
decimal nilaipelunasan1 = 0;
decimal nilaipelunasan2 = 0;
decimal sisatagihan = 0;
decimal lebihbayar = 0;
decimal bankkeluar = 0;
DataTable rs1 = Db.Rs("SELECT * "
+ " FROM ISC064_MARKETINGJUAL..MS_TAGIHAN "
+ " WHERE NoKontrak = '" + NoKontrak + "'");
for (int i = 0; i < rs1.Rows.Count; i++)
{
decimal x = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan),0) FROM ISC064_MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak = '" + NoKontrak + "' AND NoTagihan = '" + rs1.Rows[i]["Nourut"] + "'");// AND CaraBayar!='AL'
nilaitagihan += Convert.ToDecimal(rs1.Rows[i]["NilaiTagihan"]);
if (x > Convert.ToDecimal(rs1.Rows[i]["NilaiTagihan"]))
{
nilaipelunasan1 += Convert.ToDecimal(rs1.Rows[i]["NilaiTagihan"]);
}
else
{
nilaipelunasan1 += x;
}
nilaipelunasan2 += x;
}
decimal sumAlokasi = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak = '" + NoKontrak + "' AND CaraBayar='AL'");
decimal MemoCB = 0;// Db.SingleDecimal("SELECT ISNULL(SUM(LebihBayar),0) FROM MS_CASHBACK_MEMO where nokontrak='" + NoKontrak + "'");
sisatagihan = nilaitagihan - nilaipelunasan1;
bankkeluar = nilaitagihan - nilaipelunasan2;
lebihbayar = Db.SingleDecimal("SELECT ISNULL(SUM(LB),0) FROM MS_TTS WHERE Ref = '" + NoKontrak + "' AND Status <> 'VOID'");
decimal BK = Db.SingleDecimal("SELECT ISNULL(SUM(BankKeluar),0) FROM MS_CASHBACK WHERE NoKontrak = '" + NoKontrak + "'");
sisa.Text = Cf.Num(sisatagihan);
lb.Text = Cf.Num(lebihbayar - BK - sumAlokasi - MemoCB);
LebihBayar1 = lebihbayar - BK;
}
}
void fillTagihan()
{
decimal LB = Convert.ToDecimal(lb.Text);;
decimal total = 0;
string query = "SELECT (a.Nokontrak + '.' + Cast(a.NoUrut AS VARCHAR)) AS NoTagihan, "
+ " a.NamaTagihan, "
+ " a.Tipe, "
+ " a.TglJT, "
+ " a.NoUrut, "
+ " (a.NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak = a.NoKontrak and NoTagihan = a.NoUrut)) AS SisaTagihan "
+ " FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN A "
+ " WHERE 1=1 "
+ " AND A.Nokontrak = '" + NoKontrak + "'"
+ " AND (A.NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak = a.NoKontrak AND NoTagihan = a.NoUrut)) >0 ";
rs = Db.Rs(query);
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
HtmlTableRow r = new HtmlTableRow();
HtmlTableCell c = new HtmlTableCell();
TextBox t;
HtmlButton bt;
Label l;
c.InnerHtml = rs.Rows[i]["NoTagihan"].ToString();
r.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = rs.Rows[i]["NamaTagihan"].ToString();
r.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = rs.Rows[i]["Tipe"].ToString();
r.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = Cf.Day(rs.Rows[i]["TglJT"]);
r.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = Cf.Num(Convert.ToDecimal(rs.Rows[i]["SisaTagihan"]));
c.Align = "Right";
r.Cells.Add(c);
c = new HtmlTableCell();
t = new TextBox();
t.ID = "bayar_" + i;
t.Attributes.Add("style", "text-align:right;");
if (Convert.ToDecimal(rs.Rows[i]["SisaTagihan"]) <= LB)
{
t.Text = Cf.Num(rs.Rows[i]["SisaTagihan"]);
total += Convert.ToDecimal(rs.Rows[i]["SisaTagihan"]);
totalBayar.Text = Cf.Num(total);
}
else
{
if (LB > 0)
{
t.Text = Cf.Num(LB);
total += LB;
LB -= Convert.ToDecimal(rs.Rows[i]["SisaTagihan"]);
totalBayar.Text = Cf.Num(total);
}
}
Js.NumberFormat(t);
t.Attributes["onblur"] += "hitunggt();";
c.Controls.Add(t);
r.Cells.Add(c);
// Tanggal
c = new HtmlTableCell();
t = new TextBox();
t.ID = "tgl_" + Convert.ToString(i);
t.Width = 75;
t.CssClass = "txt_center";
t.Text = Cf.Day(rs.Rows[i]["TglJT"]);
t.Attributes["style"] = "font:8pt";
c.Controls.Add(t);
l = new Label();
l.Text = " ";
c.Controls.Add(l);
bt = new HtmlButton();
bt.InnerHtml = "";
bt.Attributes["onclick"] = "openCalendar('" + t.ID + "')";
bt.Attributes["class"] = "btn";
bt.Attributes["style"] = "font-family: 'fontawesome'";
c.Controls.Add(bt);
r.Cells.Add(c);
c = new HtmlTableCell();
CheckBox cb = new CheckBox();
cb.Attributes.Add("onclick", "tagihan('bayar_" + i + "','" + Cf.Num(rs.Rows[i]["SisaTagihan"]) + "',this)");
c.Controls.Add(cb);
r.Cells.Add(c);
c = new HtmlTableCell();
c.Attributes.Add("style", "display:none;");
c.InnerHtml = rs.Rows[i]["NoUrut"].ToString();
c.ID = "NoTagihan_" + i;
r.Cells.Add(c);
list.Controls.Add(r);
}
//HtmlTableRow tr = new HtmlTableRow();
//HtmlTableCell td = new HtmlTableCell();
//td.InnerHtml = "<b>Grand Total</b>";
//td.ColSpan = 5;
//tr.Cells.Add(td);
//td = new HtmlTableCell();
//TextBox te = new TextBox();
//te.ID = "totalBayar";
//te.Attributes.Add("style", "text-align:right;");
//te.Text = Cf.Num(total);
//td.Controls.Add(te);
//tr.Cells.Add(td);
//td = new HtmlTableCell();
//td.ColSpan = 2;
//tr.Cells.Add(td);
//list.Controls.Add(tr);
}
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;
}
string strSql = "SELECT a.NoKomisiP, a.SN, a.Nilai, c.NoKontrak, c.NoUnit, c.NamaAgent, c.NamaCust, d.*"
+ " FROM MS_KOMISIP_DETAIL a"
+ " INNER JOIN MS_KOMISIP b ON a.NoKomisiP = b.NoKomisiP"
+ " INNER JOIN MS_KOMISI c ON a.NoKomisi = c.NoKomisi"
+ " INNER JOIN MS_KOMISI_TERM d ON a.NoKomisi = d.NoKomisi"
+ " 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_KOMISIP_DETAIL WHERE NoKomisiP = a.NoKomisiP AND SN = a.SN) = 0"
+ " AND b.Project ='" + project.SelectedValue + "'"
+ " ORDER BY a.NoKomisiP";
DataTable rs = Db.Rs(strSql);
Rpt.NoData(list, rs, "Tidak terdapat data dengan kriteria seperti tersebut diatas.");
del.Enabled = false;
int index = 0;
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
//cek syarat cair
//cek syarat cair=================================================================
string bf = "SELECT ISNULL(SUM(NilaiTagihan),0) FROM MS_TAGIHAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"].ToString() + "' AND Tipe = 'BF'";
decimal NilaiBF = Db.SingleDecimal(bf);
string bbf = "SELECT ISNULL(SUM(NilaiPelunasan),0) FROM MS_PELUNASAN a INNER JOIN MS_TAGIHAN b ON a.NoKontrak = b.NoKontrak WHERE a.NoKontrak = '" + rs.Rows[i]["NoKontrak"].ToString() + "' AND a.NoTagihan = b.NoUrut AND b.Tipe = 'BF'";
decimal BayarBF = Db.SingleDecimal(bbf);
decimal PersenBF = NilaiBF != 0 ? BayarBF / NilaiBF * 100 : 0;
string dp = "SELECT ISNULL(SUM(NilaiTagihan),0) FROM MS_TAGIHAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"].ToString() + "' AND Tipe = 'DP'";
decimal NilaiDP = Db.SingleDecimal(dp);
string bdp = "SELECT ISNULL(SUM(NilaiPelunasan),0) FROM MS_PELUNASAN a INNER JOIN MS_TAGIHAN b ON a.NoKontrak = b.NoKontrak WHERE a.NoKontrak = '" + rs.Rows[i]["NoKontrak"].ToString() + "' AND a.NoTagihan = b.NoUrut AND b.Tipe = 'DP'";
decimal BayarDP = Db.SingleDecimal(bdp);
decimal PersenDP = NilaiDP != 0 ? BayarDP / NilaiDP * 100 : 0;
string ang = "SELECT ISNULL(SUM(NilaiTagihan),0) FROM MS_TAGIHAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"].ToString() + "' AND Tipe = 'ANG'";
decimal NilaiANG = Db.SingleDecimal(ang);
string bang = "SELECT ISNULL(SUM(NilaiPelunasan),0) FROM MS_PELUNASAN a INNER JOIN MS_TAGIHAN b ON a.NoKontrak = b.NoKontrak WHERE a.NoKontrak = '" + rs.Rows[i]["NoKontrak"].ToString() + "' AND a.NoTagihan = b.NoUrut AND b.Tipe = 'ANG'";
decimal BayarANG = Db.SingleDecimal(bang);
decimal PersenANG = NilaiANG != 0 ? BayarANG / NilaiANG * 100 : 0;
decimal PersenLunas = 0;
bool PPJB = false, AJB = false, AKAD = false;
string kon = "SELECT PersenLunas, PPJB, AJB, StatusAkad FROM MS_KONTRAK WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"].ToString() + "'";
DataTable rkon = Db.Rs(kon);
if (kon != null)
{
PersenLunas = Convert.ToDecimal(rkon.Rows[0]["PersenLunas"]);
PPJB = rkon.Rows[0]["PPJB"].ToString() != "B" ? true : false;
AJB = rkon.Rows[0]["AJB"].ToString() == "D" ? true : false;
AKAD = rkon.Rows[0]["StatusAkad"].ToString() == "SELESAI" ? true : false;
}
bool pengajuan = false;
bool Lunas = Convert.ToBoolean(rs.Rows[i]["Lunas"]);
bool BF = Convert.ToBoolean(rs.Rows[i]["BF"]);
bool DP = Convert.ToBoolean(rs.Rows[i]["DP"]);
bool ANG = Convert.ToBoolean(rs.Rows[i]["ANG"]);
bool PPJB_ = Convert.ToBoolean(rs.Rows[i]["PPJB"]);
bool AJB_ = Convert.ToBoolean(rs.Rows[i]["AJB"]);
bool AKAD_ = Convert.ToBoolean(rs.Rows[i]["AKAD"]);
int a = 0, b = 0;
if (!Lunas && !BF && !DP && !ANG && !PPJB_ && !AJB_ && !AKAD_)
{
pengajuan = true;
}
else
{
//Salah satu
if (Convert.ToInt32(rs.Rows[i]["TipeCair"]) == 1)
{
if ((Lunas && PersenLunas >= Convert.ToDecimal(rs.Rows[i]["PersenLunas"])) || (BF && PersenBF >= Convert.ToDecimal(rs.Rows[i]["PersenBF"])) || (DP && PersenDP >= Convert.ToDecimal(rs.Rows[i]["PersenDP"])) || (ANG && PersenANG >= Convert.ToDecimal(rs.Rows[i]["PersenANG"])) || (PPJB_ && PPJB) || (AJB_ && AJB) || (AKAD_ && AKAD))
{
pengajuan = true;
}
}
//Semua
else
{
if (Lunas)
{
a++;
if (PersenLunas >= Convert.ToDecimal(rs.Rows[i]["PersenLunas"]))
{
b++;
}
}
if (BF)
{
a++;
if (PersenBF >= Convert.ToDecimal(rs.Rows[i]["PersenBF"]))
{
b++;
}
}
if (DP)
{
a++;
if (PersenDP >= Convert.ToDecimal(rs.Rows[i]["PersenDP"]))
{
b++;
}
}
if (ANG)
{
a++;
if (PersenANG >= Convert.ToDecimal(rs.Rows[i]["PersenANG"]))
{
b++;
}
}
if (PPJB_)
{
a++;
if (PPJB)
{
b++;
}
}
if (AJB_)
{
a++;
if (AJB)
{
b++;
}
}
if (AKAD_)
{
a++;
if (AKAD)
{
b++;
}
}
if (a == b)
{
pengajuan = true;
}
}
}
HtmlTableRow r = new HtmlTableRow();
HtmlTableCell c;
CheckBox cb;
//cb = new CheckBox();
//cb.ID = "cb_" + index;
//cb.Attributes["title"] = rs.Rows[i]["NoKomisi"] + ";" + rs.Rows[i]["SN"];
////cb.Enabled = pengajuan ? true : false;
//c = new HtmlTableCell();
//c.Controls.Add(cb);
//r.Cells.Add(c);
cb = new CheckBox();
cb.ID = "cb_" + index;
cb.Attributes["title"] = rs.Rows[i]["NoKomisiP"].ToString();
c = new HtmlTableCell();
c.Controls.Add(cb);
r.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = rs.Rows[i]["NoKomisi"].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]["NilaiCair"]));
c.Attributes["class"] = "right";
r.Cells.Add(c);
list.Controls.Add(r);
index++;
del.Enabled = true;
}
}
private void Fill()
{
decimal GrandTotal = 0;
for (int bln = 1; bln <= 12; bln++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
r.VerticalAlign = VerticalAlign.Top;
c = new TableCell();
c.Text = Cf.Monthname(bln);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
int t = 0;
int gt = 0;
decimal rp = 0;
decimal grp = 0;
c = new TableCell();
c.HorizontalAlign = HorizontalAlign.Right;
c.Font.Bold = true;
r.Cells.Add(c);
int akhirbulan = DateTime.DaysInMonth(
Convert.ToInt32(tahun.SelectedValue)
, bln
);
for (int hari = 1; hari <= 31; hari++)
{
if (hari <= akhirbulan)
{
c = new TableCell();
if (kuantitas.Checked)
{
t = sum1(hari, bln);
gt = gt + t;
if (t != 0)
{
c.Text = t.ToString();
}
}
if (rupiah.Checked)
{
rp = sum2(hari, bln);
grp = grp + rp;
if (rp != 0)
{
c.Text = Cf.Num(rp);
}
}
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
}
}
c = r.Cells[1];
if (kuantitas.Checked)
{
c.Text = gt.ToString();
}
if (rupiah.Checked)
{
c.Text = Cf.Num(grp);
}
rpt.Rows.Add(r);
if (kuantitas.Checked)
{
GrandTotal += Convert.ToInt32(gt);
}
else if (rupiah.Checked)
{
GrandTotal += grp;
}
if (bln == 12)
{
SubTotal(GrandTotal);
}
}
}
private void FillTable()
{
string strSql = "";
if (Tipe != "TENANT")
{
strSql = "SELECT *"
+ ",(NilaiTagihan - (SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Tb + "..MS_PELUNASAN AS MS_PELUNASAN WHERE NoTagihan = MS_TAGIHAN.NoUrut AND NoKontrak = '" + Ref + "') ) AS SisaTagihan"
+ " FROM " + Tb + "..MS_TAGIHAN AS MS_TAGIHAN"
+ " WHERE NoKontrak = '" + Ref + "'"
+ " ORDER BY NoUrut";
}
else
{
strSql = "SELECT *"
+ ",CASE CaraBayar WHEN '' THEN NilaiTagihan ELSE LebihBayar*-1 END AS SisaTagihan"
+ ",CASE CaraBayar WHEN '' THEN 0 ELSE NilaiTagihan+LebihBayar END AS Pelunasan"
+ " FROM " + Tb + "..MS_TAGIHAN"
+ " WHERE NoPenghuni = '" + Ref + "'"
+ " ORDER BY Tipe,NoUrut";
}
DataTable rs = Db.Rs(strSql);
decimal t1 = 0;
decimal t2 = 0;
decimal t3 = 0;
decimal t4 = 0;
decimal t5 = 0;
decimal t6 = 0;
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
r = new TableRow();
c = new TableCell();
c.Text = rs.Rows[i]["NoUrut"] + ".";
if (Tipe == "TENANT")
{
c.Text = rs.Rows[i]["Tipe"] + "." + rs.Rows[i]["NoUrut"];
}
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NamaTagihan"].ToString();
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglJT"]);
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["NilaiTagihan"]);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = "";
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["SisaTagihan"]);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["Denda"]);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["DendaReal"]);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["NilaiPutihDenda"]);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
Rpt.Border(r);
rpt.Rows.Add(r);
t1 = t1 + (decimal)rs.Rows[i]["NilaiTagihan"];
if (Tipe != "TENANT")
{
t2 = t2 + Lunas((int)rs.Rows[i]["NoUrut"], Convert.ToDateTime(rs.Rows[i]["TglJT"]), Convert.ToDecimal(rs.Rows[i]["SisaTagihan"]), Convert.ToDecimal(rs.Rows[i]["Denda"]), Convert.ToDecimal(rs.Rows[i]["DendaReal"]), Convert.ToDecimal(rs.Rows[i]["NilaiPutihDenda"]));
}
else
{
t2 = t2 + (decimal)rs.Rows[i]["Pelunasan"];
}
t3 = t3 + (decimal)rs.Rows[i]["SisaTagihan"];
t4 = t4 + (decimal)rs.Rows[i]["Denda"];
t5 += Convert.ToDecimal(rs.Rows[i]["Dendareal"]);
t6 += Convert.ToDecimal(rs.Rows[i]["NilaiPutihDenda"]);
if (Tipe == "TENANT")
{
Lunas(rs, i);
}
if (i == rs.Rows.Count - 1)
{
SubTotal(t1, t2, t3, t4, t5, t6);
}
}
if (Tipe != "TENANT")
{
Lunas(0, DateTime.Today, 0, 0, 0, 0);
}
}
protected void Fill()
{
cancel.Attributes["onclick"] = "location.href='KontrakSertifikatEdit.aspx'";
string strSql = "SELECT "
+ " MS_KONTRAK.*"
+ ",MS_CUSTOMER.Nama AS Cs"
+ " FROM MS_KONTRAK INNER JOIN MS_CUSTOMER ON MS_KONTRAK.NoCustomer = MS_CUSTOMER.NoCustomer"
+ " WHERE MS_KONTRAK.NoKontrak = '" + NoKontrak + "'";
DataTable rsHeader = Db.Rs(strSql);
if (rsHeader.Rows.Count == 0)
{
Response.Redirect("/CustomError/Deleted.html");
}
else
{
kontrakno.Text = rsHeader.Rows[0]["NoKontrak"].ToString();
unit.Text = rsHeader.Rows[0]["NoUnit"].ToString();
customer.Text = rsHeader.Rows[0]["Cs"].ToString();
namaperusahaan.Text = rsHeader.Rows[0]["NamaPerusahaan"].ToString();
tbTgl.Text = Cf.Day(rsHeader.Rows[0]["TglSertifikat"]);
tbNoSertifikat.Text = rsHeader.Rows[0]["NoSertifikat"].ToString();
tglakhir.Text = Cf.Day(rsHeader.Rows[0]["TglAkhir"]);
jangkawaktu.Text = Cf.Num(rsHeader.Rows[0]["JangkaWaktu"]).ToString();
statussertifikat.SelectedIndex = Convert.ToInt32(rsHeader.Rows[0]["StatusHak"]);
nomorukur.Text = rsHeader.Rows[0]["NoPengukuranBidang"].ToString();
tbTgl1.Text = Cf.Day(rsHeader.Rows[0]["TglPengukuranBidang"]);
nomorsuratukur.Text = rsHeader.Rows[0]["NoSuratUkur"].ToString();
tbTgl2.Text = Cf.Day(rsHeader.Rows[0]["TglSuratUkur"]);
jumlahbidang.Text = Cf.Num(rsHeader.Rows[0]["JumlahBidang"].ToString());
if (Convert.ToInt32(rsHeader.Rows[0]["StatusSertifikat"]) == 0)
{
rblStatus.SelectedIndex = 0;
selesai.Visible = false;
sedangproses.Visible = false;
}
else if (Convert.ToInt32(rsHeader.Rows[0]["StatusSertifikat"]) == 1)
{
rblStatus.SelectedIndex = 1;
selesai.Visible = false;
sedangproses.Visible = true;
atasnama.Visible = false;
}
else if (Convert.ToInt32(rsHeader.Rows[0]["StatusSertifikat"]) == 2)
{
rblStatus.SelectedIndex = 2;
selesai.Visible = true;
sedangproses.Visible = false;
atasnama.Visible = true;
}
else if (Convert.ToInt32(rsHeader.Rows[0]["StatusSertifikat"]) == 3)
{
rblStatus.SelectedIndex = 3;
selesai.Visible = true;
atasnama.Visible = false;
sedangproses.Visible = false;
}
if (statussertifikat.SelectedIndex == 0)
{
sertifikat1.Visible = true;
sertifikat2.Visible = true;
}
else
{
sertifikat1.Visible = false;
sertifikat2.Visible = false;
}
}
}
private decimal Lunas(int NoTagihan, DateTime TglJT, decimal SisaTagihan, decimal NilaiDenda, decimal DendaReal, decimal PutihDenda)
{
string strSql = "SELECT *"
+ " FROM " + Tb + "..MS_PELUNASAN"
+ " WHERE NoKontrak = '" + Ref + "' AND NoTagihan = " + NoTagihan
+ " ORDER BY NoUrut";
decimal t = 0, Denda = 0, SubTotalDenda = 0;
decimal SubTotalPutihDenda = 0;
decimal TotalPutihDenda = 0;
decimal SubTotalDendaReal = 0;
decimal TotalDendaReal = 0;
DataTable rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
if (NoTagihan == 0 && i == 0)
{
TableRow r1 = new TableRow();
TableCell c1 = new TableCell();
c1.Text = "<b>PELUNASAN TIDAK TERALOKASI</b>";
c1.ForeColor = Color.Red;
c1.ColumnSpan = 7;
r1.Cells.Add(c1);
rpt.Rows.Add(r1);
}
TableRow r = new TableRow();
TableCell c;
if (rs.Rows[i]["NoTTS"].ToString() == "")
{
string dok = "";
//if ((bool)rs.Rows[i]["SudahCair"])
dok = "MEMO : " + rs.Rows[i]["NoMEMO"];
c = new TableCell();
c.Text = dok;
c.Wrap = false;
r.Cells.Add(c);
}
else
{
string dok = "";
if (!(bool)rs.Rows[i]["SudahCair"])
{
dok = "TTS : " + rs.Rows[i]["NoTTS2"].ToString();
}
else
{
dok = "BKM : " + rs.Rows[i]["NoBKM2"].ToString();
}
c = new TableCell();
c.Text = dok;
c.Wrap = false;
r.Cells.Add(c);
}
c = new TableCell();
c.Text = rs.Rows[i]["CaraBayar"].ToString();
if (rs.Rows[i]["Ket"].ToString().Trim() != "")
{
c.Text = c.Text + " / " + rs.Rows[i]["Ket"];
}
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglPelunasan"]);
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = "";
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["NilaiPelunasan"]);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = "";
r.Cells.Add(c);
c = new TableCell();
c.Text = ""; //Cf.Num(Math.Round(NilaiDenda, 0));
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = "";//Cf.Num(Math.Round(DendaReal, 0));
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = "";//Cf.Num(Math.Round(PutihDenda, 0));
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
Rpt.Border(r);
r.Cells[0].Attributes["style"] = r.Cells[0].Attributes["style"] + ";padding-left:15";
r.Cells[1].Attributes["style"] = r.Cells[1].Attributes["style"] + ";padding-left:20";
r.Cells[2].Attributes["style"] = r.Cells[2].Attributes["style"] + ";padding-left:20";
rpt.Rows.Add(r);
t = t + (decimal)rs.Rows[i]["NilaiPelunasan"];
SubTotalPutihDenda = PutihDenda;
TotalPutihDenda += PutihDenda;
SubTotalDendaReal = DendaReal;
TotalDendaReal += DendaReal;
SubTotalDenda = NilaiDenda;
TotalDenda = NilaiDenda;
if (i == (rs.Rows.Count - 1))
{
SubDenda(SubTotalDenda, SubTotalDendaReal, SubTotalPutihDenda);
}
}
return(t);
}
private void Fill()
{
btnlog.Attributes["onclick"] = "location.href='LogPk.aspx?Tb=MS_MEMO_LOG&Pk=" + NoMEMO.PadLeft(7, '0') + "'";
btnvoid.Attributes["onclick"] = "if(confirm('"
+ "Apakah anda ingin membatalkan MEMO nomor : " + NoMEMO + " ?\\n"
+ "Perhatian bahwa proses ini TIDAK bisa dibalik."
+ "'))"
+ "{location.href='MEMOVoid.aspx?NoMEMO=" + NoMEMO + "'}";
string Project = Db.SingleString("SELECT Project FROM MS_MEMO WHERE NoMemo = '" + NoMEMO + "'");
printMEMO.HRef = "PrintMEMO.aspx?NoMEMO=" + NoMEMO + "&project=" + Project;
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'"//jangan diganti!!!!!!! bisa merubah flow program dibawah
+ " WHEN 'UJ' THEN 'UANG JAMINAN'"
+ " WHEN 'DN' THEN 'DISKON'"
+ " WHEN 'MB' THEN 'MERCHANT BANKING'"
+ " WHEN 'PP' THEN 'PENGHAPUSAN PIUTANG'"
+ " WHEN 'TG' THEN 'TUKAR GULING'"
+ " WHEN 'AL' THEN 'ALOKASI LEBIH BAYAR'"
+ " END AS CaraBayar2"
+ " FROM MS_MEMO WHERE NoMEMO = " + NoMEMO;
DataTable rs = Db.Rs(strSql);
if (rs.Rows.Count == 0)
{
Response.Redirect("/CustomError/Deleted.html");
}
else
{
System.Configuration.AppSettingsReader s = new System.Configuration.AppSettingsReader();
string JenisPPN = Db.SingleString("SELECT JenisPPN FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak = '" + Cf.Pk(rs.Rows[0]["Ref"]) + "'");
string HeaderPajak = "";
if (JenisPPN == "PEMERINTAH")
{
HeaderPajak = (string)s.GetValue("NoFPSPemerintah", typeof(string));
}
else if (JenisPPN == "KONSUMEN")
{
HeaderPajak = (string)s.GetValue("NoFPSKonsumen", typeof(string));
}
tglmemo.Text = Cf.Day(rs.Rows[0]["TglMEMO"]);
ket.Text = rs.Rows[0]["Ket"].ToString();
tipememo.Text = rs.Rows[0]["CaraBayar2"].ToString();
unit.Text = rs.Rows[0]["Unit"].ToString();
customer.Text = rs.Rows[0]["Customer"].ToString();
printMEMO.InnerHtml = printMEMO.InnerHtml + " (" + rs.Rows[0]["PrintMEMO"] + ")";
kasir.Text = rs.Rows[0]["UserID"].ToString();
ip.Text = rs.Rows[0]["IP"].ToString();
tglInput.Text = Cf.Date(rs.Rows[0]["TglInput"]);
nilai.Text = Cf.Num(rs.Rows[0]["Total"]);
pph.Checked = (bool)rs.Rows[0]["Pph"];
memoinfo.Text = rs.Rows[0]["NoMEMO2"].ToString();
string stat = rs.Rows[0]["Status"].ToString();
status.Text = stat;
if (stat == "VOID")
{
status.ForeColor = Color.Red;
btnvoid.Disabled = true;
}
else if (stat == "POST")
{
status.ForeColor = Color.Blue;
}
FillTb(rs.Rows[0]["Tipe"].ToString());
if (rs.Rows[0]["Tipe"].ToString() != "TENANT")
{
alokasi.InnerHtml = "<a href='CustomerLunas.aspx?Tipe=" + rs.Rows[0]["Tipe"] + "&Ref=" + rs.Rows[0]["Ref"] + "'>"
+ "<b>Alokasi Pelunasan</b></a>";
}
}
}
protected void FillTagihan(string NoKontrak)
{
string strSql = "("
+ "SELECT *"
+ " FROM ISC064_MARKETINGJUAL..MS_TAGIHAN_KPA"
+ " WHERE NoKontrak = '" + Cf.Pk(NoKontrak) + "'"
+ " AND NilaiTagihan > 0"
+ ")"
;
strSql += " UNION "
+ "("
+ "SELECT * FROM ISC064_MARKETINGJUAL..MS_TAGIHAN_KPA a"
+ " WHERE NoKontrak = '" + Cf.Pk(NoKontrak) + "'"
+ " AND NilaiTagihan < 0"
+ " AND (SELECT ISNULL(SUM(NilaiPelunasan), 0) FROM ISC064_MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak = '" + Cf.Pk(NoKontrak) + "'"
+ " AND NoTagihan = a.NoUrut) < 0"
+ ")"
;
DataTable rs = Db.Rs(strSql);
decimal nilaiPelunasan;
decimal Total = Db.SingleDecimal("SELECT NilaiKontrak FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak = '" + Cf.Pk(NoKontrak) + "'");
decimal Sisa = Total;
Label l;
l = new Label();
System.Text.StringBuilder x = new System.Text.StringBuilder();
x.Append("<table cellspacing='1' style='border: 1px;' class='tb blue-skin'>");
x.Append("<tr>");
x.Append("<th rowspan='2' style='border-right: 1px ; border-bottom: 1px; background-color:#1E90FF; color:white'>NO.</th>");
x.Append("<th rowspan='2' style='border-right: 1px ; border-bottom: 1px; background-color:#1E90FF; color:white'>KETERANGAN</th>");
x.Append("<th colspan='2' style='border-right: 1px ; border-bottom: 1px; background-color:#1E90FF; color:white'>PIUTANG</th>");
x.Append("<th colspan='3' style='border-right: 1px ; border-bottom: 1px; background-color:#1E90FF; color:white'>PEMBAYARAN</th>");
x.Append("<th colspan='2' style='border-bottom: 1px ; background-color:#1E90FF; color:white'>DENDA</th>");
x.Append("</tr>");
x.Append("<tr>");
x.Append("<th style='border-right: 1px ; border-bottom: 1px; background-color:#1E90FF; color:white'>TGL.JT</th>");
x.Append("<th style='border-right: 1px ; border-bottom: 1px; background-color:#1E90FF; color:white'>NILAI</th>");
x.Append("<th style='border-right: 1px ; border-bottom: 1px; background-color:#1E90FF; color:white'>TGL.BAYAR</th>");
x.Append("<th style='border-right: 1px ; border-bottom: 1px; background-color:#1E90FF; color:white'>NO.KUITANSI</th>");
x.Append("<th style='border-right: 1px ; border-bottom: 1px; background-color:#1E90FF; color:white'>NILAI</th>");
x.Append("<th style='border-right: 1px ; border-bottom: 1px; background-color:#1E90FF; color:white'>HARI</th>");
x.Append("<th style='border-bottom: 1px ; background-color:#1E90FF; color:white'>NILAI</th>");
x.Append("</tr>");
decimal t1 = 0, t2 = 0;
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
x.Append("<tr>");
x.Append("<td align='center' style='border-right: 1px ;' nowrap='true'>" + (i + 1) + "</td>");
x.Append("<td style='border-right: 1px ;' nowrap='true'>" + rs.Rows[i]["NamaTagihan"] + "</td>");
x.Append("<td style='border-right: 1px ;' nowrap='true'>"
+ ((Convert.ToDecimal(rs.Rows[i]["NilaiTagihan"]) > 0) ? Cf.Day(rs.Rows[i]["TglJT"]) : " ")
+ "</td>");
x.Append("<td align='right' style='border-right: 1px ;' nowrap='true'>"
+ ((Convert.ToDecimal(rs.Rows[i]["NilaiTagihan"]) > 0) ? Cf.Num(rs.Rows[i]["NilaiTagihan"]) : " ")
+ "</td>");
x.Append("<td style='border-right: 1px ;' nowrap='true'>" + Lunas(NoKontrak, rs.Rows[i]["NoUrut"].ToString(), "b.TglBKM", "b.TglMEMO") + "</td>");
x.Append("<td style='border-right: 1px ;' align='center' nowrap='true'>" + Lunas(NoKontrak, rs.Rows[i]["NoUrut"].ToString(), "b.ManualBKM", "a.NoMEMO") + "</td>");
x.Append("<td align='right' style='border-right: 1px ;' nowrap='true'>" + Lunas(NoKontrak, rs.Rows[i]["NoUrut"].ToString(), "ISNULL(a.NilaiPelunasan,0)", "ISNULL(a.NilaiPelunasan,0)") + "</td>");
x.Append("<td align='right' style='border-right: 1px ;' nowrap='true'>" + Denda(NoKontrak, rs.Rows[i]["NoUrut"].ToString(), "HARI") + "</td>");
x.Append("<td nowrap='true'>" + Denda(NoKontrak, rs.Rows[i]["NoUrut"].ToString(), "NILAI") + "</td>");
x.Append("</tr>");
if (i == (rs.Rows.Count - 1))
{
decimal JumlahKPR = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiTagihan), 0) FROM ISC064_MARKETINGJUAL..MS_TAGIHAN_KPA WHERE NoKontrak = '" + NoKontrak + "' AND KPR = 1");
decimal PelunasanKPR = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan), 0) FROM ISC064_MARKETINGJUAL..MS_PELUNASAN a"
+ " INNER JOIN ISC064_MARKETINGJUAL..MS_TAGIHAN_KPA b ON a.NoTagihan = b.NoUrut AND a.NoKontrak = b.NoKontrak"
+ " WHERE a.NoKontrak = '" + NoKontrak + "' AND (a.NoTTS <> '0' OR a.NoMemo <> '0') "
+ " AND b.KPR = 1"
);
t1 = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiTagihan), 0) FROM ISC064_MARKETINGJUAL..MS_TAGIHAN_KPA"
+ " WHERE NoKontrak = '" + NoKontrak + "'"
+ " AND KPR = 0 AND NilaiTagihan > 0"
);
t2 = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan), 0) FROM ISC064_MARKETINGJUAL..MS_PELUNASAN a"
+ " INNER JOIN ISC064_MARKETINGJUAL..MS_TAGIHAN_KPA b ON a.NoTagihan = b.NoUrut AND a.NoKontrak = b.NoKontrak"
+ " WHERE a.NoKontrak = '" + NoKontrak + "' AND (a.NoTTS <> '0' OR a.NoMemo <> '0') "
+ " AND b.KPR = 0"
);
x.Append("<tr>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td style='border-right: 1px ;'><b>JUMLAH PIUTANG UM</b></td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td align='right' style='border-top: 1px ; border-right: 1px ;'><b>" + Cf.Num(t1) + "</b></td>");
x.Append("<td style='border-right: 1px ;' align='center'> </td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td style='border-top: 1px ; border-right: 1px ;'> </td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td> </td>");
x.Append("</tr>");
x.Append("<tr>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td style='border-right: 1px ;'><b>JUMLAH PEMBAYARAN UM</b></td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td align='right' style='border-right: 1px ;'><b>" + Cf.Num(t2) + "</b></td>");
x.Append("<td style='border-right: 1px ;' align='center'> </td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td style='border-top: 1px ; border-right: 1px ;'> </td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td> </td>");
x.Append("</tr>");
decimal t = (t1 - t2);
if (t < 0)
{
t = 0;
}
x.Append("<tr>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td style='border-right: 1px ;'><b>SALDO PIUTANG UM</b></td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td align='right' style='border-top: 1px ; border-right: 1px ;'><b>" + Cf.Num(Math.Round(t, 0)) + "</b></td>");
x.Append("<td style='border-right: 1px ;' align='center'> </td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td> </td>");
x.Append("</tr>");
x.Append("<tr>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td style='border-right: 1px ;'><b>JUMLAH PIUTANG KPR</b></td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td align='right' style='border-top: 1px ; border-right: 1px ;'><b>" + Cf.Num(JumlahKPR) + "</b></td>");
x.Append("<td style='border-right: 1px ;' align='center'> </td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td style='border-top: 1px ; border-right: 1px ;'> </td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td> </td>");
x.Append("</tr>");
x.Append("<tr>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td style='border-right: 1px ;'><b>JUMLAH PEMBAYARAN KPR</b></td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td align='right' style='border-right: 1px ;'><b>" + Cf.Num(PelunasanKPR) + "</b></td>");
x.Append("<td style='border-right: 1px ;' align='center'> </td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td style='border-top: 1px ; border-right: 1px ;'> </td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td> </td>");
x.Append("</tr>");
decimal SisaKPR = JumlahKPR - PelunasanKPR;
if (SisaKPR < 0)
{
SisaKPR = 0;
}
x.Append("<tr>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td style='border-right: 1px ;'><b>SALDO PIUTANG KPR</b></td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td align='right' style='border-top: 1px ; border-right: 1px ;'><b>" + Cf.Num(Math.Round(SisaKPR, 0)) + "</b></td>");
x.Append("<td style='border-right: 1px ;' align='center'> </td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td> </td>");
x.Append("</tr>");
x.Append("<tr>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td style='border-right: 1px ;'><b>SALDO PIUTANG UM + KPR</b></td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td align='right' style='border-bottom: 1px ; border-top: 1px ; border-right: 1px ;'><b>" + Cf.Num(t + SisaKPR) + "</b></td>");
x.Append("<td style='border-right: 1px ;' align='center'> </td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td style='border-top: 1px ; border-right: 1px ;'> </td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td> </td>");
x.Append("</tr>");
x.Append("<tr>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td style='border-right: 1px ;' align='center'> </td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td style='border-right: 1px ;'> </td>");
x.Append("<td> </td>");
x.Append("</tr>");
}
}
x.Append("</table>");
Label ll;
ll = new Label();
ll.Text = x.ToString();
list.Controls.Add(ll);
}
protected void save_Click(object sender, System.EventArgs e)
{
if (datavalid())
{
DateTime TglST = Convert.ToDateTime(tglst.Text);
decimal Luas = Convert.ToDecimal(luas.Text);
decimal LuasNett = Convert.ToDecimal(luasnett.Text);
decimal Biaya = Convert.ToDecimal(nilaibiaya.Text);
decimal LebihBayar = Convert.ToDecimal(lebihbayar.Text);
string status = "T";
int c = Db.SingleInteger("SELECT COUNT(*) FROM MS_BAST WHERE NoKontrak = '" + NoKontrak + "'");
if (c == 0)
{
string NoST = Db.SingleString("SELECT NoST FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'");
if (NoST == "")
{
NoST = AutoID();
}
Db.Execute("EXEC spST "
+ " '" + NoKontrak + "'"
+ ",'" + NoST + "'"
+ ",'" + TglST + "'"
);
Db.Execute("UPDATE MS_BAST SET"
+ " STu=" + stused.SelectedValue
+ " ,NoSTm='" + nostm.Text + "'"
+ " ,ST='" + status + "'"
+ " ,LuasGross= '" + Luas + "'"
+ " ,LuasNett= '" + LuasNett + "'"
+ " ,Biaya= '" + Biaya + "'"
+ " ,LebihBayar= '" + LebihBayar + "'"
+ " ,KetST ='" + keterangan.Text + "'"
+ " WHERE NoKontrak = '" + NoKontrak + "'"
);
if (tglst.Text != "")
{
Db.Execute("UPDATE MS_BAST SET TglST='" + Convert.ToDateTime(tglst.Text) + "' WHERE NoKontrak = '" + NoKontrak + "'");
}
if (tglttd.Text != "")
{
Db.Execute("UPDATE MS_BAST SET TglTTDST='" + Convert.ToDateTime(tglttd.Text) + "' WHERE NoKontrak = '" + NoKontrak + "'");
}
decimal NilaiBiaya = Convert.ToDecimal(nilaibiaya.Text);
if (NilaiBiaya != 0)
{
Db.Execute("EXEC spTagihanDaftar "
+ " '" + NoKontrak + "'"
+ ",'BIAYA ADM. SERAH TERIMA'"
+ ",'" + TglST + "'"
+ ", " + NilaiBiaya
+ ",'ADM'"
);
}
DataTable rs = Db.Rs("SELECT"
+ " A.NoKontrak AS [No. Kontrak]"
+ ",B.NoUnit AS [Unit]"
+ ",C.Nama AS [Customer]"
+ ",A.NoST AS [No. BAST]"
+ ",A.LuasGross AS [Luas Tanah]"
+ ",A.LuasNett AS [Luas Bangunan]"
+ ",CONVERT(varchar, A.TargetST, 106) AS [Tanggal Target BAST]"
+ ",CONVERT(varchar, A.TglST, 106) AS [Tanggal BAST]"
+ ",CONVERT(varchar, A.TglTTDST, 106) AS [Tanggal TTD BAST]"
+ ",PersenLunas AS [Prosentase Pelunasan]"
+ ",A.MasaGaransi"
+ ", case when A.ST='S' then 'Target BAST' when A.ST='D' then 'BAST' when A.ST='B' then 'Belum BAST' else 'Tanda Tangan BAST' end as [Status BAST]"
+ " FROM MS_BAST 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)
//+ Cf.LogCompare(rsBef,rsAft)
+ "<br>Biaya Administrasi : " + Cf.Num(NilaiBiaya)
;
Db.Execute("EXEC spLogKontrak "
+ " 'TTD-BAST'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + ket + "'"
+ ",'" + 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);
}
else
{
Db.Execute("UPDATE MS_BAST SET"
+ " STu=" + stused.SelectedValue
+ " ,NoSTm='" + nostm.Text + "'"
+ " ,ST='" + status + "'"
+ " ,LuasGross= '" + Luas + "'"
+ " ,LuasNett= '" + LuasNett + "'"
+ " ,Biaya= '" + Biaya + "'"
+ " ,LebihBayar= '" + LebihBayar + "'"
+ " ,KetST ='" + keterangan.Text + "'"
+ " WHERE NoKontrak = '" + NoKontrak + "'"
);
if (tglst.Text != "")
{
Db.Execute("UPDATE MS_BAST SET TglST='" + Convert.ToDateTime(tglst.Text) + "' WHERE NoKontrak = '" + NoKontrak + "'");
}
if (tglttd.Text != "")
{
Db.Execute("UPDATE MS_BAST SET TglTTDST='" + Convert.ToDateTime(tglttd.Text) + "' WHERE NoKontrak = '" + NoKontrak + "'");
}
decimal NilaiBiaya = Convert.ToDecimal(nilaibiaya.Text);
if (NilaiBiaya != 0)
{
Db.Execute("EXEC spTagihanDaftar "
+ " '" + NoKontrak + "'"
+ ",'BIAYA ADM. SERAH TERIMA'"
+ ",'" + TglST + "'"
+ ", " + NilaiBiaya
+ ",'ADM'"
);
}
DataTable rs = Db.Rs("SELECT"
+ " A.NoKontrak AS [No. Kontrak]"
+ ",B.NoUnit AS [Unit]"
+ ",C.Nama AS [Customer]"
+ ",A.NoST AS [No. BAST]"
+ ",A.LuasGross AS [Luas Tanah]"
+ ",A.LuasNett AS [Luas Bangunan]"
+ ",CONVERT(varchar, A.TargetST, 106) AS [Tanggal Target BAST]"
+ ",CONVERT(varchar, A.TglST, 106) AS [Tanggal BAST]"
+ ",CONVERT(varchar, A.TglTTDST, 106) AS [Tanggal TTD BAST]"
+ ",PersenLunas AS [Prosentase Pelunasan]"
+ ",A.MasaGaransi"
+ ", case when A.ST='S' then 'Target BAST' when A.ST='D' then 'BAST' when A.ST='B' then 'Belum BAST' else 'Tanda Tangan BAST' end as [Status BAST]"
+ " FROM MS_BAST 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)
//+ Cf.LogCompare(rsBef,rsAft)
+ "<br>Biaya Administrasi : " + Cf.Num(NilaiBiaya)
;
Db.Execute("EXEC spLogKontrak "
+ " 'TTD-BAST'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + ket + "'"
+ ",'" + 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);
}
if (dariReminder.Checked)
{
Response.Redirect("ReminderST.aspx?done=" + NoKontrak);
}
else
{
Response.Redirect("KontrakSTTTD.aspx?done=" + NoKontrak);
}
}
}
protected void insert_Click(object sender, System.EventArgs e)
{
string Project = Db.SingleString("SELECT Project FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'");
string c = Db.SingleString("SELECT Value FROM " + Mi.DbPrefix + "SECURITY..REF_PARAM WHERE ParamID = 'ApprovCustomTagihan" + Project + "'");
int nomor = Db.SingleInteger("SELECT COUNT(*) FROM MS_APPROVAL");
nomor++;
string NoApproval = nomor.ToString().PadLeft(7, '0');
string Ket = "";
if (c == "True")
{
for (int i = 1; i < rpt.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
string Tipe = rpt.Rows[i].Cells[1].Text;
string NamaTagihan = Cf.Str(rpt.Rows[i].Cells[2].Text);
DateTime TglJT = Convert.ToDateTime(rpt.Rows[i].Cells[3].Text);
decimal NilaiTagihan = Convert.ToDecimal(rpt.Rows[i].Cells[4].Text);
Db.Execute("EXEC spTagihanDaftarTEMP"
+ " '" + NoKontrak + "'"
+ ",'" + NamaTagihan + "'"
+ ",'" + TglJT + "'"
+ ", " + NilaiTagihan
+ ",'" + Tipe + "'"
);
}
//INSERT KE MS_APPROVAL
Db.Execute("EXEC spApproval"
+ "'" + NoApproval + "'"
+ ",'" + Str.Approval("7") + "'"//untuk customize
+ ",'" + NoKontrak + "'"
+ ",'" + Convert.ToDateTime(tgl.Text) + "'"
+ ",'" + Project + "'"
);
//insert siapa aja yang berhak approve ke ms_approval_detil
DataTable rs2 = Db.Rs("SELECT * FROM " + Mi.DbPrefix + "SECURITY..REF_APPROVAL WHERE Tipe = 7 AND Project = '" + Project + "'");
for (int i = 0; i < rs2.Rows.Count; i++)
{
Db.Execute("EXEC spApprovalDetil"
+ "'" + NoApproval + "'"
+ ",'" + (i + 1) + "'"
+ ",'" + rs2.Rows[i]["UserID"].ToString() + "'"//dari Textbox
+ "," + rs2.Rows[i]["Lvl"]
+ ",'" + Db.SingleString("SELECT Nama FROM " + Mi.DbPrefix + "SECURITY..USERNAME WHERE UserID = '" + rs2.Rows[i]["UserID"].ToString() + "'") + "'"
);
}
Db.Execute("EXEC spKontrakCustomTemp "
+ " '" + NoApproval + "'"
+ ",'" + NoKontrak + "'"
+ ",'" + Db.SingleString("SELECT Skema FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'") + "'"
+ ",'" + skema.Text + "'"
+ ",'" + Db.SingleString("SELECT CaraBayar FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'") + "'"
+ ",'" + carabayar2.SelectedValue + "'"
+ ",'" + Convert.ToDateTime(tgl.Text) + "'"
);
if (carabayar2.SelectedValue == "KPA")
{
int NoUrutMax = Db.SingleInteger("SELECT MAX(NoUrut) FROM MS_TAGIHAN_TEMP WHERE NoKontrak = '" + NoKontrak + "' ");
Db.Execute("UPDATE MS_TAGIHAN_TEMP SET KPR = " + 1 + " WHERE NoKontrak = '" + NoKontrak + "' AND NoUrut = '" + (NoUrutMax) + "'");
}
DataTable rsDetail = Db.Rs("SELECT"
+ " MS_KONTRAK.NoKontrak AS [No. Kontrak]"
+ ",MS_KONTRAK.NoUnit AS [Unit]"
+ ",MS_CUSTOMER.Nama AS [Customer]"
+ ",MS_KONTRAK.NilaiKontrak AS [Nilai Kontrak]"
+ ",MS_KONTRAK.Skema AS [Skema]"
+ " FROM MS_KONTRAK INNER JOIN MS_CUSTOMER"
+ " ON MS_KONTRAK.NoCustomer = MS_CUSTOMER.NoCustomer"
+ " WHERE MS_KONTRAK.NoKontrak = '" + NoKontrak + "'");
DataTable rsAft = Db.Rs("SELECT "
+ "CONVERT(VARCHAR,NoUrut) + '. ' + NamaTagihan + ' ('+Tipe+') ' + CONVERT(VARCHAR,TglJT,106) + ' ' + CONVERT(VARCHAR,NilaiTagihan,1) "
+ "FROM MS_TAGIHAN_TEMP WHERE NoKontrak = '" + NoKontrak + "' ORDER BY NoUrut");
Ket = Cf.LogCapture(rsDetail)
+ Cf.LogList(rsAft, "JADWAL TAGIHAN");
}
else
{
for (int i = 1; i < rpt.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
string Tipe = rpt.Rows[i].Cells[1].Text;
string NamaTagihan = Cf.Str(rpt.Rows[i].Cells[2].Text);
DateTime TglJT = Convert.ToDateTime(rpt.Rows[i].Cells[3].Text);
decimal NilaiTagihan = Convert.ToDecimal(rpt.Rows[i].Cells[4].Text);
Db.Execute("EXEC spTagihanDaftar"
+ " '" + NoKontrak + "'"
+ ",'" + NamaTagihan + "'"
+ ",'" + TglJT + "'"
+ ", " + NilaiTagihan
+ ",'" + Tipe + "'"
);
}
DataTable rs2 = Db.Rs("SELECT * FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'");
if (rs2.Rows.Count > 0)
{
Db.Execute("UPDATE MS_KONTRAK "
+ " SET Skema = '" + skema.Text + "'"
+ ", CaraBayar = '" + carabayar2.SelectedValue + "'"
+ ", ApprovalCustomTagihan = 0"
+ " WHERE NoKontrak = '" + NoKontrak + "'"
);
}
if (carabayar2.SelectedValue == "KPR")
{
int NoUrutMax = Db.SingleInteger("SELECT MAX(NoUrut) FROM MS_TAGIHAN WHERE NoKontrak = '" + NoKontrak + "' ");
Db.Execute("UPDATE MS_TAGIHAN SET KPR = " + 1 + " WHERE NoKontrak = '" + NoKontrak + "' AND NoUrut = '" + (NoUrutMax) + "'");
}
decimal NilaiKontrak = Db.SingleDecimal("SELECT NilaiKontrak FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak ='" + NoKontrak + "'");
//string skema = Db.SingleString("SELECT Skema FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak ='" + NoKontrak + "'");
string carabayar = Db.SingleString("SELECT CaraBayar FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak ='" + NoKontrak + "'");
DataTable rsAft = Db.Rs("SELECT "
+ "CONVERT(VARCHAR,NoUrut) + '. ' + NamaTagihan + ' ('+Tipe+') ' + CONVERT(VARCHAR,TglJT,106) + ' ' + CONVERT(VARCHAR,NilaiTagihan,1) "
+ "FROM MS_TAGIHAN_TEMP WHERE NoKontrak = '" + NoKontrak + "' ORDER BY NoUrut");
Ket = Cf.LogCapture(rs2)
+ "<br>Nilai Kontrak : " + Cf.Num(NilaiKontrak)
+ "<br>Tgl. Batal : " + Cf.Day(DateTime.Today)
+ "<br>Skema : " + skema
+ "<br>Cara Bayar : " + carabayar
+ Cf.LogList(rsAft, "JADWAL TAGIHAN")
;
}
Db.Execute("EXEC spLogKontrak"
+ " 'CUSTOM'"
+ ",'" + 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);
Func.CekKomisi(NoKontrak);
if (alokasi.Checked)
{
if (dariDaftar.Checked)
{
Response.Redirect("Alokasi.aspx?custom=1&dd=1&NoKontrak=" + NoKontrak);
}
else
{
Response.Redirect("Alokasi.aspx?custom=1&NoKontrak=" + NoKontrak);
}
}
else
{
if (dariDaftar.Checked)
{
Response.Redirect("KontrakDaftar3.aspx?NoKontrak=" + NoKontrak + "&done=1");
}
else
{
Response.Redirect("TagihanCustom.aspx?done=" + NoKontrak);
}
}
}
private void SaveApproval(string NoKontrak, CheckBox cb, DateTime Tgl)
{
if (cb.Checked)
{
Db.Execute("EXEC spKontrakBatal "
+ " '" + NoKontrak + "'"
);
if (Db.SingleString("SELECT Status FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'") == "B")
{
DataTable rs = Db.Rs("SELECT"
+ " MS_KONTRAK.NoKontrak AS [No. Kontrak]"
+ ",MS_KONTRAK.NoUnit AS [Unit]"
+ ",MS_CUSTOMER.Nama AS [Customer]"
+ ",MS_AGENT.Nama AS [Agent]"
+ ",AlasanBatal AS [Alasan Pembatalan]"
+ " 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 MS_KONTRAK.NoKontrak = '" + NoKontrak + "'");
decimal NilaiBiaya = Db.SingleDecimal("SELECT BiayaBatal FROM MS_KONTRAK WHERE NoKontrak ='" + NoKontrak + "'");
if (NilaiBiaya != 0)
{
Db.Execute("EXEC spTagihanDaftar "
+ " '" + NoKontrak + "'"
+ ",'BIAYA ADM. PEMBATALAN'"
+ ",'" + Cf.Day(DateTime.Today) + "'"
+ ", " + NilaiBiaya
+ ",'ADM'"
);
}
// decimal NomorPembatalan = Db.SingleDecimal("SELECT ISNULL(MAX(NomorPembatalan),0) + 1 FROM MS_KONTRAK");
// Db.Execute(" UPDATE MS_KONTRAK "
// + " SET NomorPembatalan = STUFF( " + NomorPembatalan + ", 1, 0, REPLICATE('0', 7 - LEN(" + NomorPembatalan + ")))"
// + ", TglKuasaPembatalan = '" + DateTime.Today + "'" //tglApprov batal
// + " WHERE NoKontrak = '" + NoKontrak + "'"
// );
decimal NilaiMasuk = Db.SingleDecimal(
"SELECT ISNULL(SUM(NilaiPelunasan),0) FROM MS_PELUNASAN WHERE NoKontrak = '" + NoKontrak + "'");
Db.Execute("UPDATE MS_KONTRAK SET BatalMasuk = "
+ NilaiMasuk + " WHERE NoKontrak = '" + NoKontrak + "'");
decimal NilaiKlaim = Db.SingleDecimal("SELECT NilaiKlaim FROM MS_KONTRAK WHERE NoKontrak ='" + NoKontrak + "'");
decimal NilaiPengembalian = Db.SingleDecimal("SELECT NilaiPulang FROM MS_KONTRAK WHERE NoKontrak ='" + NoKontrak + "'");
decimal TotalLunas = Db.SingleDecimal("SELECT TotalLunasBatal FROM MS_KONTRAK WHERE NoKontrak ='" + NoKontrak + "'");
string acc = Db.SingleString("SELECT AccBatal FROM MS_KONTRAK WHERE NoKontrak ='" + NoKontrak + "'");
string Ket = Cf.LogCapture(rs)
+ "<br>Biaya Administrasi : " + Cf.Num(NilaiBiaya)
+ "<br>Uang Masuk : " + Cf.Num(NilaiMasuk)
+ "<br>Nilai Klaim : " + Cf.Num(NilaiKlaim)
+ "<br>Tgl. Batal : " + Cf.Day(DateTime.Today)
+ "<br>Total Pelunasan : " + Cf.Num(TotalLunas)
+ "<br>Nilai Kembali : " + Cf.Num(NilaiPengembalian)
+ "<br>Rekening Pembatalan : " + acc
;
Db.Execute("EXEC spLogKontrak "
+ " 'APR-BA'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Ket + "'"
+ ",'" + NoKontrak + "'"
);
Func.CekKomisi(NoKontrak);
//floor plan
string Peta = Db.SingleString("SELECT Peta "
+ " FROM MS_UNIT INNER JOIN MS_KONTRAK ON MS_UNIT.NoStock = MS_KONTRAK.NoStock "
+ " WHERE NoKontrak = '" + NoKontrak + "'");
Func.GenerateFP(Peta);
//SA01
string CnnEsales = "Data Source=.;Initial Catalog=SA01;Persist Security Info=True;User ID=batavianet;Password=iNDigo100";
string ClosingID = Db.SingleString("SELECT ISNULL(ClosingID,'') FROM MS_Kontrak WHERE NoKontrak='" + NoKontrak + "'");
string AlasanBatal = Db.SingleString("SELECT ISNULL(AlasanBatal,'') FROM MS_Kontrak WHERE NoKontrak='" + NoKontrak + "'");
if (ClosingID != "")
{
Execute("UPDATE SalesClosing SET"
+ " Status=1"
+ ", AlasanCancel='" + AlasanBatal + "'"
+ ", TglCancel='" + Tgl + "'"
+ " WHERE ClosingID='" + ClosingID + "'"
, CnnEsales);
StringBuilder x = new StringBuilder();
x.Append("Closing dengan kode " + ClosingID);
x.Append("<br/>");
x.Append("<i>Dibatalkan setelah kontrak terdaftar</i>");
string Closinger = "";
Closinger = SingleString("SELECT ISNULL(UserID,'') FROM SalesClosing WHERE ClosingID='" + ClosingID + "'", CnnEsales);
if (Closinger != "")
{
string Role = "";
Role = SingleString("SELECT ISNULL(RoleID,'') FROM SecUser WHERE UserID='" + Closinger + "'", CnnEsales);
if (Role == "SA.OPR")
{
Execute("EXEC InsertSecNotification "
+ "'Pembatalan Kontrak'"
+ ",'" + x.ToString() + "'"
+ ",'" + Closinger + "'"
+ ",'../Sales/ClosingFile.aspx?id=" + ClosingID + "'"
, CnnEsales);
}
else if (Role == "SA.MGR")
{
Execute("EXEC InsertSecNotification "
+ "'Pembatalan Kontrak'"
+ ",'" + x.ToString() + "'"
+ ",'" + Closinger + "'"
+ ",'../SM/ClosingFile.aspx?id=" + ClosingID + "'"
, CnnEsales);
}
}
}
}
}
}
private void Fill()
{
if (Request.QueryString["f"] == "0")
{
//judul.InnerHtml = f0.InnerHtml;
judul.InnerText = "Price List - Pending " + Project;
}
if (Request.QueryString["f"] == "1")
{
judul.InnerText = "Price List - Approved " + Project;
}
if (Request.QueryString["f"] == "2")
{
judul.InnerText = "Price List - Edit Unit " + Project;
}
string flag = " AND FlagSPL = " + Request.QueryString["f"];
string strSql = "SELECT"
+ " MS_UNIT.NoStock"
+ ",NoUnit"
+ ",Luas"
+ ",LuasSG"
+ ",LuasNett"
+ ",LuasLebih"
+ ",MS_UNIT.PriceListMin"
+ ",MS_UNIT.PriceList"
+ ",MS_UNIT.PricelistKavling"
+ ",MS_PRICELIST_HISTORY.Periode"
+ ",BiayaBPHTB"
+ ",BiayaSurat"
+ ",BiayaProses"
+ ",BiayaLainLain"
+ ",TglInput"
+ ",TglPriceList"
+ ",TambahanHargaGimmick "
+ ",TambahanHargaLainLain "
+ " FROM MS_UNIT"
+ " INNER JOIN MS_PRICELIST_HISTORY ON MS_UNIT.NoStock = MS_PRICELIST_HISTORY.NoStock"
+ " WHERE"
+ " Status = 'A'"
+ " AND Project = '" + Project + "'"
+ flag
+ " ORDER BY NoStock";
rs = Db.Rs(strSql);
Rpt.NoData(list, rs, "Daftar unit untuk kondisi price list yang dipilih tidak ada.");
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
Label l;
TextBox t;
TextBox tgl;
CheckBox cb;
HtmlInputButton btn;
l = new Label();
l.Text = "<tr>"
+ "<td><a show-modal='#ModalPopUp' modal-title='Edit Unit' modal-url='UnitEdit.aspx?NoStock=" + rs.Rows[i]["NoStock"] + "'>" + rs.Rows[i]["NoStock"] + "</a></td>"
+ "<td>" + rs.Rows[i]["NoUnit"] + "</td>"
+ "<td align='right'>" + Cf.Num(rs.Rows[i]["Luas"]) + "</td>"
+ "<td align='right'>" + Cf.Num(rs.Rows[i]["LuasSG"]) + "</td>"
+ "<td align='right'>" + Cf.Num(rs.Rows[i]["LuasLebih"]) + "</td>"
+ "<td align='right'>" + Cf.Num(rs.Rows[i]["LuasNett"]) + "</td>"
+ "<td>";
list.Controls.Add(l);
t = new TextBox();
t.ID = "min_" + i;
t.Width = 100;
t.CssClass = "txt_num";
t.Text = Cf.Num(rs.Rows[i]["PriceListMin"]);
t.Attributes["onfocus"] = "tempnum=CalcFocus(this);";
t.Attributes["onkeyup"] = "CalcType(this,tempnum);";
t.Attributes["onblur"] = "CalcBlur(this);";
t.TabIndex = 1000;
list.Controls.Add(t);
l = new Label();
l.Text = "</td><td>";
list.Controls.Add(l);
t = new TextBox();
t.ID = "pl_" + i;
t.Width = 100;
t.CssClass = "txt_num";
t.Text = Cf.Num(rs.Rows[i]["PriceList"]);
t.Attributes["onfocus"] = "tempnum=CalcFocus(this);";
t.Attributes["onkeyup"] = "CalcType(this,tempnum);";
t.Attributes["onblur"] = "CalcBlur(this);";
t.TabIndex = 1000;
list.Controls.Add(t);
l = new Label();
l.Text = "</td><td>";
list.Controls.Add(l);
cb = new CheckBox();
cb.ID = "cbrumah_" + i;
cb.Text = " ";
cb.Attributes["onclick"] = "nonaktif(cbrumah_" + i + ",cbkavling_" + i + ")";
list.Controls.Add(cb);
l = new Label();
l.Text = "</td><td>";
list.Controls.Add(l);
t = new TextBox();
t.ID = "plkav_" + i;
t.Width = 100;
t.CssClass = "txt_num";
t.Text = Cf.Num(rs.Rows[i]["PricelistKavling"]);
t.Attributes["onfocus"] = "tempnum=CalcFocus(this);";
t.Attributes["onkeyup"] = "CalcType(this,tempnum);";
t.Attributes["onblur"] = "CalcBlur(this);";
t.TabIndex = 1000;
list.Controls.Add(t);
l = new Label();
l.Text = "</td><td>";
list.Controls.Add(l);
cb = new CheckBox();
cb.ID = "cbkavling_" + i;
cb.Attributes["onclick"] = "nonaktif(cbrumah_" + i + ",cbkavling_" + i + ")";
cb.Text = " ";
list.Controls.Add(cb);
l = new Label();
l.Text = "</td><td>";
list.Controls.Add(l);
t = new TextBox();
t.ID = "bphtb_" + i;
t.Width = 100;
t.CssClass = "txt_num";
t.Text = Cf.Num(rs.Rows[i]["BiayaBPHTB"]);
t.Attributes["onfocus"] = "tempnum=CalcFocus(this);";
t.Attributes["onkeyup"] = "CalcType(this,tempnum);";
t.Attributes["onblur"] = "CalcBlur(this);";
t.TabIndex = 1000;
list.Controls.Add(t);
l = new Label();
l.Text = "</td><td>";
list.Controls.Add(l);
t = new TextBox();
t.ID = "bsurat_" + i;
t.Width = 100;
t.CssClass = "txt_num";
t.Text = Cf.Num(rs.Rows[i]["BiayaSurat"]);
t.Attributes["onfocus"] = "tempnum=CalcFocus(this);";
t.Attributes["onkeyup"] = "CalcType(this,tempnum);";
t.Attributes["onblur"] = "CalcBlur(this);";
t.TabIndex = 1000;
list.Controls.Add(t);
l = new Label();
l.Text = "</td><td>";
list.Controls.Add(l);
t = new TextBox();
t.ID = "bproses_" + i;
t.Width = 100;
t.CssClass = "txt_num";
t.Text = Cf.Num(rs.Rows[i]["BiayaProses"]);
t.Attributes["onfocus"] = "tempnum=CalcFocus(this);";
t.Attributes["onkeyup"] = "CalcType(this,tempnum);";
t.Attributes["onblur"] = "CalcBlur(this);";
t.TabIndex = 1000;
list.Controls.Add(t);
l = new Label();
l.Text = "</td><td>";
list.Controls.Add(l);
t = new TextBox();
t.ID = "blain_" + i;
t.Width = 100;
t.CssClass = "txt_num";
t.Text = Cf.Num(rs.Rows[i]["BiayaLainLain"]);
t.Attributes["onfocus"] = "tempnum=CalcFocus(this);";
t.Attributes["onkeyup"] = "CalcType(this,tempnum);";
t.Attributes["onblur"] = "CalcBlur(this);";
t.TabIndex = 1000;
list.Controls.Add(t);
l = new Label();
l.Text = "</td>";
list.Controls.Add(l);
l = new Label();
l.Text = "<td>";
list.Controls.Add(l);
l = new Label();
//l.ID = "tgl_" + i;
l.Width = 75;
//tgl.CssClass = "tgl txt_center";
l.Text = Cf.Day(Convert.ToDateTime(rs.Rows[i]["Periode"]));
list.Controls.Add(l);
//if (rs.Rows[i]["TglPriceList"] == DBNull.Value)
// tgl.Text = Cf.Day(DateTime.Now);
//else
//tgl.Text = Cf.Day(Convert.ToDateTime(rs.Rows[i]["Periode"]));
//tgl.Attributes["style"] = "font:8pt; readonly:true";
//list.Controls.Add(tgl);
//l = new Label();
//l.Text = "<i class='fa fa-calendar'></i>";
//l.CssClass = "btn btn-cal";
//l.Attributes.Add("for", "tgl_" + i);
//list.Controls.Add(l);
//t = new TextBox();
//t.ID = "gimmick_" + i;
//t.Width = 100;
//t.CssClass = "txt_num";
//t.Text = Cf.Num(rs.Rows[i]["TambahanHargaGimmick"]);
//t.Attributes["onfocus"] = "tempnum=CalcFocus(this);";
//t.Attributes["onkeyup"] = "CalcType(this,tempnum);";
//t.Attributes["onblur"] = "CalcBlur(this);";
//t.TabIndex = 1000;
//list.Controls.Add(t);
//l = new Label();
//l.Text = "</td>";
//list.Controls.Add(l);
//l = new Label();
//l.Text = "<td>";
//list.Controls.Add(l);
//t = new TextBox();
//t.ID = "lainlain_" + i;
//t.Width = 100;
//t.CssClass = "txt_num";
//t.Text = Cf.Num(rs.Rows[i]["TambahanHargaLainLain"]);
//t.Attributes["onfocus"] = "tempnum=CalcFocus(this);";
//t.Attributes["onkeyup"] = "CalcType(this,tempnum);";
//t.Attributes["onblur"] = "CalcBlur(this);";
//t.TabIndex = 1000;
//list.Controls.Add(t);
l = new Label();
l.Text = "</td>";
l = new Label();
l.Text = "<td>";
list.Controls.Add(l);
tgl = new TextBox();
tgl.ID = "tgl_" + i;
tgl.Width = 75;
tgl.CssClass = "tgl txt_center";
//if (rs.Rows[i]["Periode"] == DBNull.Value)
// tgl.Text = Cf.Day(DateTime.Now);
//else
//tgl.Text = Cf.Day(Convert.ToDateTime(rs.Rows[i]["TglPriceList"]))
tgl.Text = Cf.Day(DateTime.Now);;
tgl.Attributes["style"] = "font:8pt";
list.Controls.Add(tgl);
l = new Label();
l.Text = "<i class='fa fa-calendar'></i>";
l.CssClass = "btn btn-cal";
l.Attributes.Add("for", "tgl_" + i);
list.Controls.Add(l);
l = new Label();
l.Text = "</td></tr>";
list.Controls.Add(l);
}
}
private void Fill()
{
string strSql = " SELECT a.*, b.NoUnit, c.Nama, d.Nama AS Agent FROM MS_KONTRAK a"
+ " INNER JOIN MS_UNIT b ON a.NoStock = b.NoStock"
+ " INNER JOIN MS_CUSTOMER c ON a.NoCustomer = c.NoCustomer"
+ " INNER JOIN MS_AGENT d ON a.NoAgent = d.NoAgent"
+ " WHERE a.ApprovalBatal = 1"
+ " AND a.Status <> 'B'"
;
DataTable rs = Db.Rs(strSql);
if (rs.Rows.Count == 0)
{
save.Enabled = false;
}
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
HtmlTableRow tr;
HtmlTableCell c;
CheckBox cb;
tr = new HtmlTableRow();
list.Controls.Add(tr);
cb = new CheckBox();
cb.ID = "nokontrak_" + i;
c = new HtmlTableCell();
c.ID = "pk_" + i;
c.Attributes["title"] = rs.Rows[i]["NoKontrak"].ToString();
c.Controls.Add(cb);
c.NoWrap = true;
tr.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = "<a href=\"javascript:popEditKontrak('" + rs.Rows[i]["NoKontrak"] + "')\">"
+ rs.Rows[i]["NoKontrak"].ToString()
+ "</a>";
c.NoWrap = true;
tr.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = rs.Rows[i]["NoUnit"].ToString();
c.NoWrap = true;
tr.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = rs.Rows[i]["Nama"].ToString();
c.NoWrap = true;
tr.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = rs.Rows[i]["Agent"].ToString();
c.NoWrap = true;
tr.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = rs.Rows[i]["AlasanBatal"].ToString();
c.NoWrap = true;
tr.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = rs.Rows[i]["KetAlasanBatal"].ToString();
c.NoWrap = true;
tr.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = Cf.Num(Convert.ToDecimal(rs.Rows[i]["BiayaBatal"]));
c.Align = "right";
c.NoWrap = true;
tr.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = Cf.Num(Convert.ToDecimal(rs.Rows[i]["TotalLunasBatal"]));
c.Align = "right";
c.NoWrap = true;
tr.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = Cf.Day(rs.Rows[i]["TglKembali"]);
c.Align = "right";
c.NoWrap = true;
tr.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = Cf.Num(Convert.ToDecimal(rs.Rows[i]["NilaiPulang"]));
c.Align = "right";
c.NoWrap = true;
tr.Cells.Add(c);
c = new HtmlTableCell();
c.InnerHtml = Cf.Num(Convert.ToDecimal(rs.Rows[i]["NilaiKlaim"]));
c.Align = "right";
c.NoWrap = true;
tr.Cells.Add(c);
}
}
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'";
}
decimal total = 0;
decimal t2 = 0;
decimal t1 = 0;
decimal t3 = 0;
decimal t4 = 0;
decimal t5 = 0;
decimal t6 = 0;
DateTime tanggal = Convert.ToDateTime(tgl.Text);
string Lokasi = "";
if (lokasi.SelectedIndex != 0)
{
Lokasi = " AND ISC064_MARKETINGJUAL..MS_KONTRAK.Lokasi = '" + Cf.Str(lokasi.SelectedValue) + "'";
}
string KPR = "";
if (kpa1.Checked)
{
KPR = " ";
}
else if (kpa2.Checked)
{
KPR = " AND MS_KONTRAK.NoKontrak IN (SELECT NoKontrak FROM ISC064_MARKETINGJUAL..MS_TAGIHAN WHERE Nokontrak = MS_KONTRAK.NoKontrak AND KPR != 1)";//" AND ISC064_MARKETINGJUAL..MS_TAGIHAN.KPR = '1' ";
}
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 aa = "";
if (UserAgent() > 0)
{
aa = " AND " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK.NoAgent = " + UserAgent();
}
string strSql = "SELECT "
+ " ISC064_MARKETINGJUAL..MS_KONTRAK.TglKontrak"
+ ",ISC064_MARKETINGJUAL..MS_KONTRAK.NoKontrak"
+ ",ISC064_MARKETINGJUAL..MS_KONTRAK.NoUnit"
+ ",ISC064_MARKETINGJUAL..MS_KONTRAK.NilaiKontrak"
+ ",ISC064_MARKETINGJUAL..MS_CUSTOMER.Nama AS Cs"
+ ",ISC064_MARKETINGJUAL..MS_AGENT.Nama AS Agent"
+ ",(SELECT ISNULL(SUM(NilaiPelunasan),0) FROM ISC064_MARKETINGJUAL..MS_PELUNASAN a join ISC064_marketingjual..ms_tagihan b on a.NoKontrak = b.NoKontrak WHERE a.NoKontrak = ISC064_MARKETINGJUAL..MS_KONTRAK.NoKontrak AND a.TglPelunasan < '" + Cf.AwalBulan(tanggal.Month, tanggal.Year) + "' AND b.Tipe <> 'ADM' and a.NoTagihan = b.NoUrut) AS Lalu"
+ ",(SELECT ISNULL(SUM(NilaiPelunasan),0) FROM ISC064_MARKETINGJUAL..MS_PELUNASAN a join ISC064_marketingjual..ms_tagihan b on a.NoKontrak = b.NoKontrak WHERE a.NoKontrak = ISC064_MARKETINGJUAL..MS_KONTRAK.NoKontrak AND a.TglPelunasan >= '" + Cf.AwalBulan(tanggal.Month, tanggal.Year) + "' AND a.TglPelunasan <= '" + Cf.AwalBulan1(tanggal.Month, tanggal.Year, tanggal.Day) + "' AND b.Tipe <> 'ADM' and a.NoTagihan = b.NoUrut) AS Berjalan"
+ " 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
+ KPR
+ Lokasi
+ Status
+ aa;
DataTable rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
int no = i + 1;
decimal KPALalu = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan),0) FROM "
+ " ISC064_MARKETINGJUAL..MS_PELUNASAN A INNER JOIN "
+ " ISC064_MARKETINGJUAL..MS_TAGIHAN B on A.NoTagihan = B.NoUrut AND A.NoKontrak = B.NoKontrak "
+ " WHERE TglPelunasan < '" + Cf.AwalBulan(tanggal.Month, tanggal.Year) + "' "
+ " AND B.KPR = '1' "
+ " AND B.TIPE <> 'ADM' "
+ " AND A.NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' ");
decimal BerjalanLalu = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan),0) FROM "
+ " ISC064_MARKETINGJUAL..MS_PELUNASAN A INNER JOIN "
+ " ISC064_MARKETINGJUAL..MS_TAGIHAN B on A.NoTagihan = B.NoUrut AND A.NoKontrak = B.NoKontrak "
+ " WHERE TglPelunasan >= '" + Cf.AwalBulan(tanggal.Month, tanggal.Year) + "' "
+ " AND TglPelunasan <= '" + Cf.AwalBulan1(tanggal.Month, tanggal.Year, tanggal.Day) + "' "
+ " AND B.KPR = '1' "
+ " AND B.TIPE <> 'ADM' "
+ " AND A.NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' ");
decimal NilaiLalu = Convert.ToDecimal(rs.Rows[i]["Lalu"]);
decimal NilaiBerjalan = Convert.ToDecimal(rs.Rows[i]["Berjalan"]);
if (kpa2.Checked)
{
NilaiLalu -= KPALalu;
NilaiBerjalan -= BerjalanLalu;
}
decimal sekarang = NilaiLalu + NilaiBerjalan;
decimal saldo = (decimal)rs.Rows[i]["NilaiKontrak"] - sekarang;
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
c = new TableCell();
c.Text = Cf.Str(no);
c.HorizontalAlign = HorizontalAlign.Right;
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 = rs.Rows[i]["NoKontrak"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Cs"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Agent"].ToString();
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 = Cf.Num(rs.Rows[i]["NilaiKontrak"]);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(NilaiLalu);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(NilaiBerjalan);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(sekarang);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(saldo);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
decimal adm = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiTagihan),0)FROM ISC064_MARKETINGJUAL..MS_TAGIHAN WHERE NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND Tipe = 'ADM' ");
c.Text = Cf.Num(adm.ToString());
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
decimal admterima = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan),0)FROM ISC064_MARKETINGJUAL..MS_PELUNASAN a join ISC064_MARKETINGJUAL..MS_TAGIHAN b ON a.NoKontrak = b.NoKontrak WHERE a.NoKontrak = '" + rs.Rows[i]["NoKontrak"] + "' AND Tipe = 'ADM' AND a.NoTagihan = b.NoUrut AND a.TglPelunasan <= '" + Cf.AwalBulan1(tanggal.Month, tanggal.Year, tanggal.Day) + "'");
c.Text = Cf.Num(admterima.ToString());
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
rpt.Rows.Add(r);
total = total + (decimal)rs.Rows[i]["NilaiKontrak"];
t2 = t2 + (decimal)rs.Rows[i]["NilaiKontrak"];
t1 = t1 + NilaiLalu;
t3 = t3 + NilaiBerjalan;
t4 = t4 + sekarang;
t5 = t5 + adm;
t6 = t6 + admterima;
if (i == rs.Rows.Count - 1)
{
SubTotal("GRAND TOTAL", total, t2, t1, t3, t4, t5, t6);
}
}
}
protected void crbyt_SelectedIndexChanged(object sender, EventArgs e)
{
if (crbyt.SelectedIndex == 0)
{
btncek.Text = "Silahkan Pilih Cara Bayar";
nml.Text = Cf.Num(0);
}
else
{
btncek.Text = "<a href=\"javascript:call('" + NoStock + "','" + NoNUP + "','" + Jenis + "','" + crbyt.SelectedValue + "','" + Project + "')\">Cek Harga...</a>";
Label l = new Label();
decimal pl = Db.SingleDecimal("SELECT ISNULL(PriceList, 0) FROM MS_UNIT"
+ " WHERE NoStock = '" + NoStock + "'");
string RumusDiskon = "";
RumusDiskon = Db.SingleString(
"SELECT Diskon FROM REF_SKEMA WHERE Nomor = " + crbyt.SelectedValue);
string[] x = RumusDiskon.Split('+');
System.Text.StringBuilder sb = new System.Text.StringBuilder();
for (int i = 0; i < x.Length; i++)
{
if (x[i] != "")
{
decimal y = Convert.ToDecimal(x[i]) * (decimal) - 1;
if (i < (x.Length - 1))
{
sb.Append(y.ToString() + "+");
}
else
{
sb.Append(y.ToString());
}
}
}
string RumusBunga = Db.SingleString(
"SELECT Bunga FROM REF_SKEMA WHERE Nomor = " + crbyt.SelectedValue);
string[] x2 = RumusBunga.Split('+');
System.Text.StringBuilder sb2 = new System.Text.StringBuilder();
for (int i = 0; i < x2.Length; i++)
{
if (x2[i] != "")
{
decimal y = Convert.ToDecimal(x2[i]) * (decimal) - 1;
if (i < (x2.Length - 1))
{
sb2.Append(y.ToString() + "+");
}
else
{
sb2.Append(y.ToString());
}
}
}
//persenbunga.Text = sb2.ToString();
//decimal bunga = Func.NominalDiskon(RumusBunga, pl);
//if (bunga == 0)
//{
// nilaiBunga.Text = "0";
//}
//else
//{
// nilaiBunga.Text = Cf.Num(Math.Round(bunga, 0).ToString());
//}
//persenbunga.Text = sb2.ToString();
decimal bunga = Func.NominalDiskon(RumusBunga, pl);
//disc.Text = sb.ToString();
decimal diskon = Func.NominalDiskon(RumusDiskon, pl + bunga);
decimal ndpp = 0, nppn = 0;
string ParamID = "PLIncludePPN" + Project;
bool includeppn = Db.SingleString("SELECT Value FROM " + Mi.DbPrefix + "SECURITY..REF_PARAM WHERE ParamID = '" + ParamID + "'") == "True";
if (includeppn)
{
ndpp = (pl - diskon + (bunga * (decimal) - 1)) / (decimal)1.1;
}
else
{
ndpp = (pl - diskon + (bunga * (decimal) - 1));
}
if (includeppn)
{
nppn = (pl - diskon + (bunga * (decimal) - 1)) - ndpp;
}
else
{
nppn = (ndpp * (decimal)0.1);
}
nml.Text = Cf.Num(Math.Round(ndpp + nppn));
btncek.Text = "<a href=\"javascript:call('" + NoNUP + "','" + NoStock + "','" + Jenis + "','" + crbyt.SelectedValue + "','" + Project + "')\">Cek Harga...</a>";
}
}
protected void Fill()
{
cancel.Attributes["onclick"] = "location.href='KontrakProses.aspx?NoKontrak=" + NoKontrak + "'";
string strSql = "SELECT "
+ " MS_KONTRAK.*"
+ ",MS_CUSTOMER.Nama AS Cs"
+ " FROM MS_KONTRAK INNER JOIN MS_CUSTOMER ON MS_KONTRAK.NoCustomer = MS_CUSTOMER.NoCustomer"
+ " WHERE MS_KONTRAK.NoKontrak = '" + NoKontrak + "'";
DataTable rsHeader = Db.Rs(strSql);
if (rsHeader.Rows.Count == 0)
{
Response.Redirect("/CustomError/Deleted.html");
}
else
{
nokontrak.Text = rsHeader.Rows[0]["NoKontrak"].ToString();
unit.Text = rsHeader.Rows[0]["NoUnit"].ToString();
customer.Text = rsHeader.Rows[0]["Cs"].ToString();
tbTgl.Text = Cf.Day(rsHeader.Rows[0]["TglHasilSP3K"]);
tbTarget.Text = Cf.Day(rsHeader.Rows[0]["TargetSP3K"]);
tbPengajuan.Text = Cf.Day(rsHeader.Rows[0]["TglPengajuanSP3K"]);
tbNoSP3K.Text = rsHeader.Rows[0]["NoSP3K"].ToString();
tbKet.Text = rsHeader.Rows[0]["KetSP3K"].ToString();
nilai.Text = Cf.Num(rsHeader.Rows[0]["ApprovalKPR"]);
if (rsHeader.Rows[0]["HasilSP3K"].ToString() == "TOLAK")
{
rblHasil.SelectedIndex = 0;
}
else if (rsHeader.Rows[0]["HasilSP3K"].ToString() == "SETUJU")
{
rblHasil.SelectedIndex = 1;
}
else if (rsHeader.Rows[0]["HasilSP3K"].ToString() == "SETUJU SEBAGIAN")
{
rblHasil.SelectedIndex = 2;
}
if (rsHeader.Rows[0]["StatusSP3K"].ToString() == "")
{
rblStatus.SelectedIndex = 0;
dijadwalkan.Visible = false;
diajukan.Visible = false;
selesai.Visible = false;
}
else if (rsHeader.Rows[0]["StatusSP3K"].ToString() == "TIDAK PERLU")
{
rblStatus.SelectedIndex = 1;
dijadwalkan.Visible = false;
diajukan.Visible = false;
selesai.Visible = false;
}
else if (rsHeader.Rows[0]["StatusSP3K"].ToString() == "DIJADWALKAN")
{
rblStatus.SelectedIndex = 2;
dijadwalkan.Visible = true;
diajukan.Visible = false;
selesai.Visible = false;
}
else if (rsHeader.Rows[0]["StatusSP3K"].ToString() == "DIAJUKAN")
{
rblStatus.SelectedIndex = 3;
dijadwalkan.Visible = true;
diajukan.Visible = true;
selesai.Visible = false;
}
else if (rsHeader.Rows[0]["StatusSP3K"].ToString() == "SELESAI")
{
rblStatus.SelectedIndex = 4;
dijadwalkan.Visible = true;
diajukan.Visible = true;
selesai.Visible = true;
}
decimal TambahanUM = Convert.ToDecimal(rsHeader.Rows[0]["NilaiPengajuan"]) - Convert.ToDecimal(rsHeader.Rows[0]["ApprovalKPR"]);
tambahum.Text = Cf.Num(TambahanUM);
}
}
private void Lunas(DataTable rs, int i)
{
if (rs.Rows[i]["CaraBayar"].ToString() != "")
{
TableRow r = new TableRow();
TableCell c;
string dok = "";
if (!(bool)rs.Rows[i]["SudahCair"])
{
if (rs.Rows[i]["NoTTS"].ToString() == "")
{
dok = "MEMO : " + rs.Rows[i]["NoMEMO"];
}
else
{
dok = "TTS : " + rs.Rows[i]["NoTTS2"];
}
}
else
if (rs.Rows[i]["NoTTS"].ToString() != "")
{
dok = "BKM : " + rs.Rows[i]["NoBKM2"] + "";
}
c = new TableCell();
c.Text = dok;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["CaraBayar"].ToString();
if (rs.Rows[i]["KetBayar"].ToString().Trim() != "")
{
c.Text = c.Text + " / " + rs.Rows[i]["KetBayar"];
}
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglBayar"]);
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = "";
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["Pelunasan"]);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = "";
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = "";
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = "";
c.Wrap = false;
r.Cells.Add(c);
Rpt.Border(r);
r.Cells[0].Attributes["style"] = r.Cells[0].Attributes["style"] + ";padding-left:15";
r.Cells[1].Attributes["style"] = r.Cells[1].Attributes["style"] + ";padding-left:20";
r.Cells[2].Attributes["style"] = r.Cells[2].Attributes["style"] + ";padding-left:20";
rpt.Rows.Add(r);
}
}
protected void save_Click(object sender, System.EventArgs e)
{
if (datavalid())
{
DateTime TglTTS = Convert.ToDateTime(tgl.Text);
string Unit = Cf.Str(unit.Text);
string Customer = Cf.Str(customer.Text);
string CaraBayar = carabayar.SelectedValue;
string Ket = Cf.Str(ket.Text);
decimal AdminBank = Convert.ToDecimal(admBank.Text);
decimal LebihBayar = Convert.ToDecimal(lebihBayar.Text); //kurang bayar
decimal LB = Convert.ToDecimal(lb.Text); //lebih bayar
decimal NilaiMemo = LebihBayar;
Db.Execute("EXEC ISC064_FINANCEAR..spTTSRegistrasi"
+ " '" + TglTTS + "'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Tipe + "'"
+ ",'" + Ref + "'"
+ ",'" + Unit + "'"
+ ",'" + Customer + "'"
+ ",'" + CaraBayar + "'"
+ ",'" + Ket + "'"
);
int noTTS = Db.SingleInteger("SELECT TOP 1 NoTTS FROM ISC064_FINANCEAR..MS_TTS ORDER BY NoTTS DESC");
decimal total2 = (Convert.ToDecimal(gt.Text) + LebihBayar) - AdminBank;
Db.Execute("UPDATE ISC064_FINANCEAR..MS_TTS"
+ " SET Acc = '" + ddlAcc.SelectedValue + "'"
+ ", AdminBank='" + AdminBank + "' "
// + ", Total2 = '" + total2 + "'"
+ ", LebihBayar = '" + LebihBayar + "'" //kurang bayar
+ ", LB = '" + LB + "'" //lebih bayar
+ ", SumberBayar = " + sumberdana.SelectedValue
+ " WHERE NoTTS = " + noTTS);
if (anonim.SelectedValue != "")
{
Db.Execute("UPDATE ISC064_FINANCEAR..MS_TTS SET NoAnonim = '" + anonim.SelectedValue + "' WHERE NoTTS = '" + noTTS + "' ");
}
// add by viana 23/03/09
// string sSQL = "UPDATE MS_TTS SET a.TipePosting = 1 FROM MS_TTS a, MS_KONTRAK b"
// + " WHERE a.Ref = b.NoKontrak AND b.Akunting2 = 1 AND NoTTS = " + NoTTS + "";
// Db.Execute(sSQL);
//khusus cek giro
if (carabayar.SelectedValue == "BG")
{
string NoBG = Cf.Pk(nobg.Text);
DateTime TglBG = Convert.ToDateTime(tglbg.Text);
Db.Execute("EXEC ISC064_FINANCEAR..spTTSRegistrasiBG"
+ " '" + noTTS + "'"
+ ",'" + NoBG + "'"
+ ",'" + TglBG + "'"
);
}
if (anonim.SelectedIndex > 0)
{
Db.Execute("UPDATE ISC064_FINANCEAR..MS_ANONIM SET Status = 'S' WHERE NoAnonim = "
+ anonim.SelectedValue);
}
//khusus kartu kredit
if (carabayar.SelectedValue == "KK")
{
string NoKK = Cf.Pk(nokk.Text);
string BankKK = Cf.Pk(bankkk.Text);
Db.Execute("UPDATE ISC064_FINANCEAR..MS_TTS SET "
+ " NoKK = '" + NoKK + "'"
+ ",BankKK = '" + BankKK + "'"
+ " WHERE NoTTS = '" + noTTS + "'"
);
}
if (LebihBayar > 0)
{
Db.Execute("EXEC ISC064_FINANCEAR..spMEMORegistrasi"
+ " '" + TglTTS + "'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Tipe + "'"
+ ",'" + Ref + "'"
+ ",'" + Unit + "'"
+ ",'" + Customer + "'"
+ ",'PP'"
+ ",''"
+ "," + noTTS
);
}
int NoMEMO = 0;
if (Db.SingleInteger("SELECT COUNT(*) FROM ISC064_FINANCEAR..MS_MEMO") > 0)
{
NoMEMO = Db.SingleInteger("SELECT TOP 1 NoMEMO FROM ISC064_FINANCEAR..MS_MEMO ORDER BY NoMEMO DESC");
}
System.Text.StringBuilder alokasiM = new System.Text.StringBuilder();
System.Text.StringBuilder alokasi = new System.Text.StringBuilder();
for (int i = 0; i < rsTagihan.Rows.Count; i++)
{
TextBox lunas = (TextBox)list.FindControl("lunas_" + i);
if (lunas.Text != "")
{
int NoTagihan = (int)rsTagihan.Rows[i]["NoUrut"];
string NamaTagihan = Cf.Str(rsTagihan.Rows[i]["NamaTagihan"])
+ " (" + rsTagihan.Rows[i]["Tipe"] + ")";
decimal Nilai = Convert.ToDecimal(lunas.Text);
Db.Execute("EXEC ISC064_FINANCEAR..spTTSAlokasi "
+ " " + noTTS
+ ", " + NoTagihan
+ ", " + Nilai
);
alokasi.Append(NamaTagihan + " " + Cf.Num(Nilai) + "<br>");
if (LebihBayar > 0)
{
decimal NilaiTagihan = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiTagihan),0) FROM " + Tb + "..MS_TAGIHAN WHERE NoUrut = " + NoTagihan + " AND NoKontrak = '" + Ref + "'");
decimal Pelunasan = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan),0) FROM " + Tb + "..MS_PELUNASAN WHERE NoTagihan = " + NoTagihan + " AND NoKontrak = '" + Ref + "'");
decimal SisaTag = NilaiTagihan - Pelunasan;
decimal n = 0;
if (SisaTag > 0)
{
n = SisaTag < LebihBayar ? SisaTag : LebihBayar;
Db.Execute("EXEC ISC064_FINANCEAR..spMEMOAlokasi "
+ " " + NoMEMO
+ ", " + NoTagihan
+ ", " + n
);
Db.Execute("UPDATE ISC064_MARKETINGJUAL..MS_PELUNASAN"
+ " SET"
+ " TglPelunasan ='" + TglTTS + "'"
+ ", SudahCair='1'"
+ " WHERE NoKontrak='" + referensi.Text + "' AND NoMemo='" + NoMEMO + "' AND NoTagihan='" + NoTagihan + "'"
);
Db.Execute("UPDATE ISC064_FINANCEAR..MS_MEMO SET Status='POST' WHERE NoMemo='" + NoMEMO + "'");
alokasiM.Append(NamaTagihan + " " + Cf.Num(Nilai) + "<br>");
LebihBayar -= n;
}
}
}
}
decimal TotalSatu = Db.SingleDecimal("SELECT Total FROM ISC064_FINANCEAR..MS_TTS WHERE NoTTS = '" + noTTS + "' ");
decimal TotalDua = (TotalSatu + LebihBayar + LB) - AdminBank;
Db.Execute("UPDATE ISC064_FINANCEAR..MS_TTS SET Total2 = '" + TotalDua + "' WHERE NoTTS = '" + noTTS + "' ");
DataTable rs = Db.Rs("SELECT "
+ " CONVERT(varchar, TglTTS, 106) AS [Tanggal]"
+ ",Tipe"
+ ",Ref AS [Ref.]"
+ ",Unit"
+ ",Customer"
+ ",CaraBayar AS [Cara Bayar]"
+ ",Ket AS [Keterangan]"
+ ",Total"
+ ",NoBG AS [No. BG]"
+ ",CONVERT(varchar, TglBG, 106) AS [Tanggal BG]"
+ ", Acc AS [Rekening Bank]"
+ " FROM ISC064_FINANCEAR..MS_TTS WHERE NoTTS = " + noTTS);
string KetLog = Cf.LogCapture(rs)
+ "<br>***ALOKASI PEMBAYARAN:<br>"
+ alokasi.ToString();
Db.Execute("EXEC ISC064_FINANCEAR..spLogTTS"
+ " 'REGIS'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + KetLog + "'"
+ ",'" + noTTS.ToString().PadLeft(7, '0') + "'"
);
decimal LogID = Db.SingleDecimal("SELECT TOP 1 LogID FROM " + Mi.DbPrefix + "FINANCEAR..MS_TTS_LOG ORDER BY LogID DESC");
string Project = Db.SingleString("SELECT Project FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak = (SELECT Ref FROM " + Mi.DbPrefix + "FINANCEAR..MS_TTS WHERE NoTTS = '" + noTTS + "')");
Db.Execute("UPDATE " + Mi.DbPrefix + "FINANCEAR..MS_TTS_LOG SET Project = '" + Project + "' WHERE LogID = " + LogID);
if (NilaiMemo > 0)
{
DataTable rsM = Db.Rs("SELECT "
+ " CONVERT(varchar, TglMEMO, 106) AS [Tanggal]"
+ ",Tipe"
+ ",Ref AS [Ref.]"
+ ",Unit"
+ ",Customer"
+ ",CaraBayar AS [Cara Bayar]"
+ ",Ket AS [Keterangan]"
+ ",Total"
+ ",NoBG AS [No. BG]"
+ ",CONVERT(varchar, TglBG, 106) AS [Tanggal BG]"
+ ", Acc AS [Rekening Bank]"
+ " FROM ISC064_FINANCEAR..MS_MEMO WHERE NoMEMO = " + NoMEMO);
string KetLogM = Cf.LogCapture(rsM)
+ "<br>***ALOKASI PEMBAYARAN:<br>"
+ alokasiM.ToString();
Db.Execute("EXEC ISC064_FINANCEAR..spLogMEMO"
+ " 'REGIS'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + KetLogM + "'"
+ ",'" + NoMEMO.ToString().PadLeft(7, '0') + "'"
);
decimal LogID2 = Db.SingleDecimal("SELECT TOP 1 LogID FROM MS_MEMO_LOG ORDER BY LogID DESC");
string Project2 = Db.SingleString("SELECT Project FROM MS_MEMO WHERE NoMEMO = '" + NoMEMO + "'");
Db.Execute("UPDATE MS_MEMO_LOG SET Project = '" + Project2 + "' WHERE LogID = " + LogID2);
Db.Execute("EXEC ISC064_MARKETINGJUAL..spProsentasePelunasan '" + referensi.Text + "'");
Db.Execute("UPDATE ISC064_MARKETINGJUAL..MS_KONTRAK SET FlagMemo=1 WHERE NoKontrak='" + referensi.Text + "'");
}
string NoTTS2 = AutoID;
Db.Execute("UPDATE ISC064_FINANCEAR..MS_TTS SET NoTTS2='" + NoTTS2 + "' WHERE NoTTS=" + noTTS);
Response.Redirect("TTSRegistrasi.aspx?done=" + noTTS);
}
}
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;
}
decimal t1 = 0;
decimal t2 = 0;
decimal t3 = 0;
string UserID = "";
if (kasir.SelectedIndex != 0)
{
UserID = " AND UserID = '" + kasir.SelectedValue + "'";
}
string tgl = "";
if (tglinput.Checked)
{
tgl = "TglInput";
}
string agent = "";
if (UserAgent() > 0)
{
agent = " AND (SELECT NoAgent FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak = MS_TTS.Ref) = " + UserAgent();
}
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 1=1 "
+ " AND CONVERT(varchar," + tgl + ",112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(varchar," + tgl + ",112) <= '" + Cf.Tgl112(Sampai) + "'"
+ " AND NoNUP != ''"
+ UserID
+ agent
+ " ORDER BY NoTTS";
DataTable rs = Db.Rs(strSql);
DataTable rsGiro = Db.Rs(
"SELECT DISTINCT NoBG"
+ " FROM " + Mi.DbPrefix + "FINANCEAR..MS_TTS"
+ " WHERE 1=1 "
+ " AND CONVERT(varchar," + tgl + ",112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(varchar," + tgl + ",112) <= '" + Cf.Tgl112(Sampai) + "'"
+ " AND NoNUP != ''"
+ UserID
+ agent
+ " AND NoBG != ''"
);
int LembarGiro = rsGiro.Rows.Count;
decimal TN = 0, KD = 0, KK = 0, TR = 0, BG = 0, UJ = 0, DN = 0;
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
r.VerticalAlign = VerticalAlign.Top;
r.Attributes["ondblclick"] = "popEditTTS('" + rs.Rows[i]["NoTTS"] + "')";
c = new TableCell();
c.Text = (i + 1).ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglTTS"]);
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoNUP"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
//c.Text = rs.Rows[i]["ManualTTS"].ToString();
c.Text = rs.Rows[i]["NoTTS"].ToString().PadLeft(7, '0');
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
//Unit Customer
c = new TableCell();
c.Text = Cf.Str(rs.Rows[i]["Unit"].ToString());
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Customer"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["Total"]);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["CaraBayar2"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
string CaraBayarr = rs.Rows[i]["CaraBayar"].ToString();
if (CaraBayarr == "TN")
{
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["Total"]);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = " ";
c.ColumnSpan = 3;
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
}
else if (CaraBayarr == "KD")
{
c = new TableCell();
c.Text = " ";
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["Total"]);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = " ";
c.ColumnSpan = 2;
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
}
else if (CaraBayarr == "TR")
{
c = new TableCell();
c.Text = " ";
c.ColumnSpan = 2;
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["Total"]);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = " ";
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
}
else if (CaraBayarr == "KK")
{
c = new TableCell();
c.Text = " ";
c.ColumnSpan = 3;
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["Total"]);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
}
string Bank = Db.SingleString("SELECT Bank FROM " + Mi.DbPrefix + "FINANCEAR..REF_ACC WHERE ACC = '" + rs.Rows[i]["Acc"] + "' ");
c = new TableCell();
c.Text = Bank;
c.Width = 1000;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT Nama FROM " + Mi.DbPrefix + "SECURITY..USERNAME WHERE UserID = '" + rs.Rows[i]["UserID"] + "'");
c.Width = 1000;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Catatan"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
rpt.Rows.Add(r);
t1 = t1 + (decimal)rs.Rows[i]["Total"];
t2 = t2 + (decimal)rs.Rows[i]["LebihBayar"];
t3 = t3 + (decimal)rs.Rows[i]["Total2"];
if (rs.Rows[i]["CaraBayar"].ToString() == "TN")
{
TN += Convert.ToDecimal(rs.Rows[i]["Total"]);
}
else if (rs.Rows[i]["CaraBayar"].ToString() == "KD")
{
KD += Convert.ToDecimal(rs.Rows[i]["Total"]);
}
else if (rs.Rows[i]["CaraBayar"].ToString() == "KK")
{
KK += Convert.ToDecimal(rs.Rows[i]["Total"]);
}
else if (rs.Rows[i]["CaraBayar"].ToString() == "TR")
{
TR += Convert.ToDecimal(rs.Rows[i]["Total"]);
}
else if (rs.Rows[i]["CaraBayar"].ToString() == "BG")
{
BG += Convert.ToDecimal(rs.Rows[i]["Total"]);
}
else if (rs.Rows[i]["CaraBayar"].ToString() == "UJ")
{
UJ += Convert.ToDecimal(rs.Rows[i]["Total"]);
}
else if (rs.Rows[i]["CaraBayar"].ToString() == "DN")
{
DN += Convert.ToDecimal(rs.Rows[i]["Total"]);
}
if (i == rs.Rows.Count - 1)
{
SubTotal("TOTAL", t1, t2, t3, TN, KD, KK, TR, BG, UJ, DN);
Giro(LembarGiro);
Detail(TN, KD, KK, TR, BG, UJ, DN);
}
}
}
