protected void Page_Load(object sender, System.EventArgs e)
{
Act.Pass();
Act.NoCache();
if (!Page.IsPostBack)
{
string[] x = Cf.SplitByString(Acc, ";");
DataTable rs = Db.Rs("SELECT * FROM " + Mi.DbPrefix + "FINANCEAR..REF_ACC"
+ " WHERE Acc = '" + x[0] + "' AND SubID='" + x[1] + "'"
);
if (rs.Rows.Count == 0)
{
Response.Redirect("/CustomError/Deleted.html");
}
else if (!Act.AksesProject(rs.Rows[0]["Project"].ToString()))
{
Response.Redirect("/CustomError/SecLevel.html");
}
Js.Focus(this, ket);
Js.Confirm(this,
"Apakah anda ingin menghapus account : " + Acc + " ?\\n"
+ "Perhatian bahwa data akan dihapus secara PERMANEN."
);
}
}
private void Fill()
{
btnlog.Attributes["onclick"] = "location.href='LogPk.aspx?Tb=" + Mi.DbPrefix + "FINANCEAR..REF_ACC_LOG&Pk=" + Acc + "'";
btndel.Attributes["onclick"] = "location.href='AccDel.aspx?Acc=" + Acc + "'";
string[] x = Cf.SplitByString(Acc, ";");
DataTable rs = Db.Rs("SELECT * FROM " + Mi.DbPrefix + "FINANCEAR..REF_ACC WHERE Acc = '" + x[0] + "' AND SubID='" + x[1] + "'");
if (rs.Rows.Count == 0)
{
Response.Redirect("/CustomError/Deleted.html");
}
else if (!Act.AksesProject(rs.Rows[0]["Project"].ToString()))
{
Response.Redirect("/CustomError/SecLevel.html");
}
else
{
acc.Text = rs.Rows[0]["Acc"].ToString();
rekening.Text = rs.Rows[0]["Rekening"].ToString();
subid.Text = rs.Rows[0]["SubID"].ToString();
bank.Text = rs.Rows[0]["Bank"].ToString();
cabang1.Text = rs.Rows[0]["Cabang"].ToString();
atasnama.Text = rs.Rows[0]["AtasNama"].ToString();
saldoawal.Text = Cf.Num(rs.Rows[0]["SaldoAwal"]);
Cf.SelectedValue(project, rs.Rows[0]["Project"].ToString());
}
}
protected void delbtn_Click(object sender, System.EventArgs e)
{
string[] x = Cf.SplitByString(Acc, ";");
DataTable rs = Db.Rs("SELECT * FROM " + Mi.DbPrefix + "FINANCEAR..MS_TTS"
+ " WHERE Acc ='" + x[0] + "' AND SubID='" + x[1] + "'"
);
if (rs.Rows.Count != 0)
{
//Response.Redirect("/CustomError/Deleted.html");
frm.Visible = false;
nodel.Visible = true;
}
//else if (!Act.AksesProject(rs.Rows[0]["Project"].ToString()))
// Response.Redirect("/CustomError/SecLevel.html");
else
{
string Ket = "***Alasan Delete :<br>" + Cf.Str(ket.Text)
+ "<br><br>***Data Sebelum Delete :<br>"
+ Cf.LogCapture(rs);
Db.Execute("EXEC " + Mi.DbPrefix + "FINANCEAR..spAccDel '" + x[0] + "', '" + x[1] + "'");
int c = Db.SingleInteger(
"SELECT COUNT(*) FROM " + Mi.DbPrefix + "FINANCEAR..REF_ACC WHERE Acc = '" + x[0] + "' AND SubID='" + x[1] + "'");
if (c == 0)
{
//Log
Db.Execute("EXEC " + Mi.DbPrefix + "FINANCEAR..spLogAcc "
+ " 'DELETE'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Ket + "'"
+ ",'" + Acc + "'"
);
decimal LogID = Db.SingleDecimal("SELECT TOP 1 LogID FROM REF_ACC_LOG ORDER BY LogID DESC");
string Project = Db.SingleString("SELECT Project FROM REF_ACC WHERE Acc ='" + x[0] + "' AND SubID='" + x[1] + "'");
Db.Execute("UPDATE REF_ACC_LOG SET Project = '" + Project + "' WHERE LogID = " + LogID);
Js.Close(this);
}
else
{
//Tidak bisa dihapus
frm.Visible = false;
nodel.Visible = true;
}
}
}
protected void Save()
{
int index = 0;
foreach (Control r in list.Controls)
{
CheckBox cb = (CheckBox)list.FindControl("cb_" + index);
if (cb.Checked)
{
string[] a = Cf.SplitByString(cb.Attributes["title"].ToString(), ";");
int NoNota = Db.SingleInteger("SELECT ISNULL(MAX(NoNota), 0) FROM MS_KOMISI");
NoNota += 1;
DateTime TglBayar = DateTime.Today;
DataTable rsBef = Db.Rs("SELECT * FROM MS_KOMISI WHERE NoKontrak = '" + a[0] + "' AND NoUrut = " + a[1]);
string strSql = "UPDATE MS_KOMISI"
+ " SET NoNota = " + NoNota
+ ", TglBayar = '" + TglBayar + "'"
+ ", NilaiBayar = NilaiKomisi"
+ ", SudahBayar = 1"
+ " WHERE NoKontrak = '" + a[0] + "'"
+ " AND NoUrut = " + a[1]
;
Db.Execute(strSql);
DataTable rsAft = Db.Rs("SELECT * FROM MS_KOMISI WHERE NoKontrak = '" + a[0] + "' AND NoUrut = " + a[1]);
string Ket = "";
Ket = "---BAYAR KOMISI---<br>"
+ Cf.LogCapture(Db.Rs("SELECT * FROM MS_KOMISI WHERE NoKontrak = '" + a[0] + "' AND NoUrut = " + a[1]))
;
Db.Execute("EXEC spLogKontrak"
+ " 'EJK'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Ket + "'"
+ ",'" + a[0] + "'"
);
}
index++;
}
}
private void Fill()
{
btnlog.Attributes["onclick"] = "location.href='LogPk.aspx?Tb=REF_EMAIL_LOG&Pk=" + ID + "'";
btndel.Attributes["onclick"] = "location.href='AlamatEmailDel.aspx?ID=" + ID + "'";
string[] x = Cf.SplitByString(ID, ";");
DataTable rs = Db.Rs("SELECT * FROM REF_EMAIL WHERE ID = '" + x[0] + "'");
if (rs.Rows.Count == 0)
Response.Redirect("/CustomError/Deleted.html");
else if (!Act.AksesProject(rs.Rows[0]["Project"].ToString()))
Response.Redirect("/CustomError/SecLevel.html");
else
{
id.Text = rs.Rows[0]["ID"].ToString();
email.Text = rs.Rows[0]["Email"].ToString();
Cf.SelectedValue(project, rs.Rows[0]["Project"].ToString());
}
}
private bool unik()
{
bool x = true;
string[] y = Cf.SplitByString(Acc, ";");
int c = Db.SingleInteger("SELECT COUNT(*) FROM " + Mi.DbPrefix + "FINANCEAR..REF_ACC WHERE"
+ " Acc <> '" + y[0] + "'"
+ " AND SubID <> '" + y[1] + "'"
+ " AND Acc = '" + Cf.Pk(acc.Text) + "'"
+ " AND SubID = '" + subid.Text + "'"
);
if (c != 0)
{
x = false;
}
return(x);
}
protected void Save()
{
int index = 0;
foreach (Control r in list.Controls)
{
CheckBox cb = (CheckBox)list.FindControl("cb_" + index);
if (cb.Checked)
{
string[] a = Cf.SplitByString(cb.Attributes["title"].ToString(), ";");
DataTable rsBef = Db.Rs("SELECT * FROM MS_KOMISI WHERE NoKontrak = '" + a[0] + "' AND NoUrut = " + a[1]);
string strSql = "UPDATE MS_KOMISI"
+ " SET Realisasi = 1"
+ " WHERE NoKontrak = '" + a[0] + "'"
+ " AND NoUrut = " + a[1]
;
Db.Execute(strSql);
DataTable rsAft = Db.Rs("SELECT * FROM MS_KOMISI WHERE NoKontrak = '" + a[0] + "' AND NoUrut = " + a[1]);
string Ket = "";
Ket = "---REALISASI KOMISI---<br>"
+ Cf.LogCapture(Db.Rs("SELECT * FROM MS_KOMISI WHERE NoKontrak = '" + a[0] + "' AND NoUrut = " + a[1]))
;
Db.Execute("EXEC spLogKontrak"
+ " 'EJK'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Ket + "'"
+ ",'" + a[0] + "'"
);
}
index++;
}
}
private void Fill()
{
string aa = "";
if (UserAgent() > 0)
{
aa = " AND a.NoAgent = " + UserAgent();
}
string nLokasi = "";
if (Lokasi != "SEMUA")
{
nLokasi = " AND a.Lokasi = '" + Cf.Str(Lokasi) + "'";
}
string nTipe = "";
if (Tipe != "SEMUA")
{
nTipe = "AND a.Jenis = '" + Tipe + "'";
}
decimal t1 = 0, t2 = 0, t3 = 0, t4 = 0, t5 = 0, t6 = 0, t7 = 0;
string strSql = "SELECT "
+ " a.TglKontrak"
+ " ,a.NoKontrak"
+ " ,a.Gross"
+ " ,a.DiskonRupiah"
+ " ,a.BungaNominal"
+ " ,a.TglBatal"
+ " ,a.TotalLunasBatal"
+ " ,a.NilaiPulang"
+ " ,a.NilaiKontrak"
+ " ,a.NilaiKlaim, a.NilaiPPN, a.NilaiDPP"
+ " ,b.NoUnit, b.Jenis, b.LuasSG"
+ " ,c.Nama, d.Nama AS Ag"
+ " FROM ISC064_MARKETINGJUAL..MS_KONTRAK a"
+ " INNER JOIN ISC064_MARKETINGJUAL..MS_UNIT b ON a.NoStock= b.NoStock"
+ " INNER JOIN ISC064_MARKETINGJUAL..MS_CUSTOMER c ON a.NoCustomer = c.NoCustomer"
+ " INNER JOIN ISC064_MARKETINGJUAL..MS_AGENT d ON a.NoAgent = d.NoAgent"
+ " WHERE a.TglBatal >= '" + Dari + "' AND a.TglBatal <= '" + Sampai + "'"
+ " AND a.Status = 'B'"
+ " AND a.Project = '" + Project + "'"
+ aa
+ nTipe
+ nLokasi
;
//Response.Write(strSql);
DataTable rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
r.Attributes["ondblclick"] = "javascript:popEditKontrak('" + rs.Rows[i]["NoKontrak"] + "');";
TableCell c;
c = new TableCell();
c.Text = (i + 1).ToString();
c.HorizontalAlign = HorizontalAlign.Center;
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]["NoKontrak"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
string unit = rs.Rows[i]["NoUnit"].ToString();
string[] nounit = Cf.SplitByString(unit, "/");
//string Tower = unit.Substring(1, 1);
//string Lantai = unit.Substring(3, 2);
//string nounit = unit.Substring(6, 2);
c = new TableCell();
c.Text = unit;// "SP" + nounit[0].Substring(1) + nounit[1] + nounit[2];
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Nama"].ToString();//Db.SingleString("SELECT Nama FROM ISC064_MARKETINGJUAL..MS_CUSTOMER WHERE NoCustomer = " + rs.Rows[i]["NoCustomer"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Ag"].ToString();//Db.SingleString("SELECT Nama FROM ISC064_MARKETINGJUAL..MS_AGENT a INNER JOIN ISC064_MARKETINGJUAL..MS_KONTRAK b ON a.NoAgent=b.NoAgent WHERE b.NoCustomer = " + rs.Rows[i]["NoCustomer"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoUnit"].ToString();//Db.SingleString("SELECT NoUnit FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE NoCustomer = " + rs.Rows[i]["NoCustomer"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Jenis"].ToString();//Db.SingleString("SELECT Jenis FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE NoCustomer = " + rs.Rows[i]["NoCustomer"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["LuasSG"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["Gross"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round(Convert.ToDecimal(rs.Rows[i]["DiskonRupiah"])));
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round(Convert.ToDecimal(rs.Rows[i]["BungaNominal"])));
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglBatal"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["NilaiKontrak"]);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["TotalLunasBatal"]);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["NilaiPulang"]);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["NilaiKlaim"]);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
decimal DPP = Convert.ToDecimal(rs.Rows[i]["NilaiDPP"]);//(rs.Rows[i]["NilaiKontrak"]) - Convert.ToDecimal(rs.Rows[i]["NilaiPPN"]);//Math.Round(Convert.ToDecimal(rs.Rows[i]["NilaiKontrak"])/(decimal)1.1);
c.Text = Cf.Num(Math.Round(DPP));
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
decimal PPN = Convert.ToDecimal(rs.Rows[i]["NilaiPPN"]);//Math.Round(DPP * (decimal)0.1);
c.Text = Cf.Num(Math.Round(PPN));
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
rpt.Rows.Add(r);
t1 += Convert.ToDecimal(rs.Rows[i]["NilaiKontrak"]);
t2 += Convert.ToDecimal(rs.Rows[i]["NilaiKlaim"]);
t3 += Convert.ToDecimal(rs.Rows[i]["TotalLunasBatal"]);
t4 += Convert.ToDecimal(rs.Rows[i]["NilaiPulang"]);
t5 += DPP;
t6 += PPN;
t7 += Convert.ToDecimal(rs.Rows[i]["BungaNominal"]);
if (i == rs.Rows.Count - 1)
{
SubTotal(t1, t2, t3, t4, t5, t6, t7);
}
}
}
private bool Save()
{
if (valid())
{
DateTime TglTTS = Convert.ToDateTime(tgltts.Text);
string Ket = Cf.Str(ket.Text);
string Unit = Cf.Str(unit.Text);
string Customer = Cf.Str(customer.Text);
decimal admbank = Convert.ToDecimal(admBank.Text);
decimal lebihbayar = Convert.ToDecimal(lebihBayar.Text); //kurang bayar
decimal lbayar = Convert.ToDecimal(lb.Text); //lebih bayar
string ManualTTS = manualtts.Text;
string ManualBKM = manualbkm.Text != "" ? Cf.Str(manualbkm.Text).PadLeft(6, '0') : "";
string NoBG = "";
DateTime TglBG = DateTime.Today, TglJTBG = DateTime.Today;
string Titip = "";
if (carabayar.Text == "CEK GIRO")
{
NoBG = Cf.Pk(nobg.Text);
TglBG = Convert.ToDateTime(tglbg.Text);
TglJTBG = Convert.ToDateTime(tgljtbg.Text);
Titip = Cf.Str(titip.Text);
}
//khusus kartu kredit
string NoKK = "", BankKK = "";
if (carabayar.Text == "KARTU KREDIT")
{
NoKK = Cf.Pk(nokk.Text);
BankKK = Cf.Pk(bankkk.Text);
}
DataTable rs = Db.Rs("SELECT "
+ " NoTTS AS [No. TTS]"
+ ",Tipe"
+ ",Ref AS [Ref.]"
+ ",CaraBayar AS [Cara Bayar]"
+ ",Total AS [Nilai TTS]"
+ " FROM MS_TTS"
+ " WHERE NoTTS = " + NoTTS
);
DataTable rsBef = Db.Rs("SELECT "
+ " CONVERT(varchar, TglTTS, 106) AS [Tanggal TTS]"
+ ",CONVERT(varchar, TglBKM, 106) AS [Tanggal BKM]"
+ ",Ket AS [Keterangan]"
+ ",NoBG AS [No. BG]"
+ ",CONVERT(varchar, TglBG, 106) AS [Tanggal BG]"
+ ",CONVERT(varchar, TglJTBG, 106) AS [Tanggal Jatuh Tempo BG]"
+ ",Titip AS [Pengelola BG]"
+ ",Unit"
+ ",Customer"
+ ",Pph AS [PPH]"
+ ",ManualTTS AS [Manual TTS]"
+ ",ManualBKM AS [Manual BKM]"
+ ", Acc AS [Rekening Bank]"
+ ", NoFPS AS [No. Faktur Pajak]"
+ " FROM MS_TTS"
+ " WHERE NoTTS = " + NoTTS
);
Db.Execute("EXEC spTTSEdit"
+ " '" + NoTTS + "'"
+ ",'" + TglTTS + "'"
+ ",'" + Unit + "'"
+ ",'" + Customer + "'"
+ ",'" + Ket + "'"
+ ",'" + NoBG + "'"
+ ",'" + TglBG + "'"
+ ",'" + TglJTBG + "'"
+ ",'" + Titip + "'"
);
//manual update
Db.Execute("UPDATE MS_TTS SET "
+ " Pph = " + Cf.BoolToSql(pph.Checked)
+ ",ManualTTS = '" + ManualTTS + "'"
+ ",ManualBKM = '" + ManualBKM + "'"
+ ", NoFPS = '" + Cf.Str(tbNoFaktur.Text) + "'"
+ ",NoKK = '" + NoKK + "'"
+ ",BankKK = '" + BankKK + "'"
+ " WHERE NoTTS = " + NoTTS);
if (tglfp.Text != "")
{
Db.Execute("UPDATE MS_TTS SET "
+ " TglFP = '" + Convert.ToDateTime(tglfp.Text) + "'"
+ " WHERE NoTTS = " + NoTTS);
}
if (delfp.Checked)
{
Db.Execute("UPDATE REF_FP SET Status = 0 WHERE NoFPS = '" + tbNoFaktur.Text + "'");
Db.Execute("UPDATE MS_TTS SET NoFPS = '' WHERE NoTTS = " + NoTTS);
}
bool FOBO = Db.SingleBool("SELECT FOBO FROM MS_TTS WHERE NoTTS='" + NoTTS + "'");
if (FOBO != true)
{
decimal Total = Db.SingleDecimal("SELECT Total FROM MS_TTS WHERE NoTTS='" + NoTTS + "'");
decimal total2 = Total + lebihbayar + lbayar - admbank;
Db.Execute("UPDATE MS_TTS SET "
+ " AdminBank = '" + admbank + "'"
+ " , Total2 ='" + total2 + "'"
+ " , LebihBayar ='" + lebihbayar + "'"
+ " , LB ='" + lbayar + "'"
+ " WHERE NoTTS = " + NoTTS);
}
Db.Execute("UPDATE MS_TTS SET "
+ " TglBKM = '" + Convert.ToDateTime(tglbkm.Text) + "'"
+ " WHERE NoTTS = " + NoTTS);
Db.Execute("EXEC spSinkronisasi " + NoTTS);
string[] x = Cf.SplitByString(ddlAcc.SelectedValue, ";");
Db.Execute("UPDATE MS_TTS SET Acc = '" + x[0] + "' , SubID='" + x[1] + "' WHERE NoTTS = " + NoTTS);
DataTable rsAft = Db.Rs("SELECT "
+ " CONVERT(varchar, TglTTS, 106) AS [Tanggal TTS]"
+ ",CONVERT(varchar, TglBKM, 106) AS [Tanggal BKM]"
+ ",Ket AS [Keterangan]"
+ ",NoBG AS [No. BG]"
+ ",CONVERT(varchar, TglBG, 106) AS [Tanggal BG]"
+ ",CONVERT(varchar, TglJTBG, 106) AS [Tanggal Jatuh Tempo BG]"
+ ",Titip AS [Pengelola BG]"
+ ",Unit"
+ ",Customer"
+ ",Pph AS [PPH]"
+ ",ManualTTS AS [Manual TTS]"
+ ",ManualBKM AS [Manual BKM]"
+ ", Acc AS [Rekening Bank]"
+ ", NoFPS AS [No. Faktur Pajak]"
+ " FROM MS_TTS"
+ " WHERE NoTTS = " + NoTTS
);
/*Update status Akunting*/
int Akunting = Db.SingleInteger("SELECT Akunting FROM MS_TTS WHERE NoTTS = " + NoTTS);
if (Akunting == 1)
{
string NoVoucher = Db.SingleString("SELECT NoVoucher FROM MS_TTS WHERE NoTTS = " + NoTTS);
Akun.InsertAnomali("TTS", NoTTS, Cf.LogCapture(rsBef), Cf.LogCapture(rsAft), "EDIT TTS", "", NoVoucher);
}
/************************/
//Logfile
string ketlog = Cf.LogCapture(rs)
+ Cf.LogCompare(rsBef, rsAft);
Db.Execute("EXEC spLogTTS"
+ " 'EDIT'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + ketlog + "'"
+ ",'" + NoTTS.ToString().PadLeft(7, '0') + "'"
);
decimal LogID = Db.SingleDecimal("SELECT TOP 1 LogID FROM MS_TTS_LOG ORDER BY LogID DESC");
string Project = Db.SingleString("SELECT Project FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak = (SELECT Ref FROM MS_TTS WHERE NoTTS = '" + NoTTS + "')");
Db.Execute("UPDATE MS_TTS_LOG SET Project = '" + Project + "' WHERE LogID = " + LogID);
return(true);
}
else
{
return(false);
}
}
private void Fill()
{
DateTime Dari = Db.SingleTime("SELECT FilterDari FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
DateTime Sampai = Db.SingleTime("SELECT FilterSampai FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
if (Dari > Sampai)
{
DateTime x = Sampai;
Sampai = Dari;
Dari = x;
}
string nProject = "";
if (Project != "SEMUA")
{
nProject = " AND b.Project IN ('" + Project.Replace(",", "','") + "')";
}
string nPerusahaan = "";
if (Perusahaan != "SEMUA")
{
nPerusahaan = " AND b.Pers = '" + Perusahaan + "'";
}
string aa = "";
if (UserAgent() > 0)
{
aa = " AND b.NoAgent = " + UserAgent();
}
string strSql = "SELECT a.*"
+ " FROM MS_KONTRAK_LOG a"
+ " INNER JOIN MS_KONTRAK b ON a.Pk = b.NoKontrak"
+ " WHERE a.Tgl >= '" + Dari + "'"
+ " AND a.Tgl <= '" + Sampai + "'"
+ " AND a.Aktivitas = 'GU'"
+ " AND b.Status = 'A'"
+ nProject
+ nPerusahaan
+ aa
;
DataTable rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
string strBef = "", strAft = "", strTgl = "";
string[] strTemp = Cf.SplitByString(rs.Rows[i]["Ket"].ToString(), "<br>");
bool isNext = false;
for (int j = 0; j < strTemp.Length; j++)
{
if (!isNext)
{
if (strTemp[j].StartsWith("No. Stock"))
{
strBef = strTemp[j].ToString().Replace("No. Stock : ", "");
isNext = true;
}
}
else
{
if (strTemp[j].StartsWith("No. Stock"))
{
strAft = strTemp[j].ToString().Replace("No. Stock : ", "");
break;
}
}
}
TableRow r = new TableRow();
TableCell c;
c = new TableCell();
c.Text = (i + 1).ToString();
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(Db.SingleTime("SELECT TglKontrak FROM MS_KONTRAK WHERE NoKontrak = '" + rs.Rows[i]["Pk"] + "'"));
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Pk"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT b.Nama"
+ " FROM MS_KONTRAK a"
+ " INNER JOIN MS_CUSTOMER b"
+ " ON a.NoCustomer = b.NoCustomer"
+ " WHERE a.NoKontrak = '" + Cf.Pk(rs.Rows[i]["Pk"]) + "'"
;
c.Text = Db.SingleString(strSql);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT b.Nama"
+ " FROM MS_KONTRAK a"
+ " INNER JOIN MS_AGENT b"
+ " ON a.NoAgent = b.NoAgent"
+ " WHERE a.NoKontrak = '" + Cf.Pk(rs.Rows[i]["Pk"]) + "'"
;
c.Text = Db.SingleString(strSql);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = strBef;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT Jenis FROM MS_UNIT WHERE NoStock = '" + strBef + "'");
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT LuasSG"
+ " FROM MS_UNIT"
+ " WHERE NoStock = '" + strBef + "'"
;
c.Text = Cf.Num(Db.SingleDecimal(strSql));
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
decimal pl = Db.SingleDecimal("SELECT Pricelist FROM MS_UNIT WHERE NoStock = '" + strBef + "'");
c.Text = Cf.Num(pl);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
decimal discrup = Db.SingleDecimal("SELECT DiskonRupiah FROM MS_Kontrak WHERE NoStock = '" + strAft + "'");
decimal disctambah = Db.SingleDecimal("SELECT DiskonTambahan FROM MS_Kontrak WHERE NoStock = '" + strAft + "'");
decimal bunga = Db.SingleDecimal("SELECT BungaNominal FROM MS_Kontrak WHERE NoStock = '" + strAft + "'");
c.Text = Cf.Num(discrup + disctambah);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(pl + bunga - discrup - disctambah);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = strAft;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT Jenis FROM MS_UNIT WHERE NoStock = '" + strAft + "'");
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT LuasSG"
+ " FROM MS_UNIT"
+ " WHERE NoStock = '" + strAft + "'"
;
c.Text = Cf.Num(Db.SingleDecimal(strSql));
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Db.SingleDecimal("SELECT Gross FROM MS_Kontrak WHERE NoStock = '" + strAft + "'"));
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(discrup + disctambah);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Db.SingleDecimal("SELECT NilaiKontrak FROM MS_Kontrak WHERE NoStock = '" + strAft + "'"));
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
for (int k = 0; k < strTemp.Length; k++)
{
if (strTemp[k].StartsWith("Tgl Pindah Unit"))
{
strTgl = strTemp[k].ToString().Replace("Tgl Pindah Unit : ", "");
}
}
c = new TableCell();
c.Text = strTgl;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
rpt.Rows.Add(r);
c = new TableCell();
c.Text = Db.SingleString("SELECT Nama FROM " + Mi.DbPrefix + "SECURITY..REF_PROJECT WHERE Project = '" + rs.Rows[i]["Project"].ToString() + "'");
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
rpt.Rows.Add(r);
}
}
private bool valid()
{
bool x = true;
string s = "";
//acc
Cf.ValidMandatory(this, "Rekening", project.SelectedValue);
if (!unik())
{
x = false;
//if (s == "") s = acc.ID;
accc.Text = "Duplikat";
subidc.Text = "Duplikat";
}
else
{
accc.Text = "";
subidc.Text = "";
}
if (!Cf.isMoney(saldoawal))
{
x = false;
if (s == "")
{
s = saldoawal.ID;
}
saldoawalc.Text = "Angka";
}
else if (Cf.isEmpty(subid))
{
x = false;
if (s == "")
{
s = subid.ID;
}
subidc.Text = "Kosong";
}
else
{
saldoawalc.Text = "";
}
string[] y = Cf.SplitByString(Acc, ";");
string AccBaru = Cf.Pk(acc.Text);
string SubID = Cf.Str(subid.Text);
if (y[0] != AccBaru || y[1] != SubID)
{
decimal jum = Db.SingleDecimal("SELECT COUNT(*) FROM " + Mi.DbPrefix + "FINANCEAR..REF_ACC WHERE ACC='" + AccBaru + "' AND SubID='" + SubID + "'");
if (jum != 0)
{
x = false;
}
}
decimal Bank = Db.SingleDecimal("SELECT ISNULL(COUNT(*),0) FROM " + Mi.DbPrefix + "FINANCEAR..REF_ACC WHERE BANK='" + bank.Text + "'");
string BankBef = Db.SingleString("SELECT Bank FROM " + Mi.DbPrefix + "FINANCEAR..REF_ACC WHERE Acc='" + y[0] + "' and SUBID='" + y[1] + "'");
if (Bank > 0 && BankBef != bank.Text)
{
x = false;
bankc.Text = "Nama Bank sudah digunakan";
}
else
{
bankc.Text = "";
}
if (!x)
{
Js.Alert(
this
, "Input Tidak Valid.\\n\\n"
+ "Aturan Proses :\\n"
+ "1. No. Account tidak boleh kosong dan tidak boleh duplikat.\\n"
+ "2. Saldo Awal harus berupa angka.\\n"
+ "3. Sudah terdapat rekening yang memiliki subid serta account yang sama.\\n"
+ "4. Nama bank tersebut sudah digunakan.\\n"
, "document.getElementById('" + s + "').focus();"
+ "document.getElementById('" + s + "').select();"
);
}
return(x);
}
protected void save_Click(object sender, System.EventArgs e)
{
if (datavalid())
{
for (int j = 0; j < baris; j++)
{
TextBox va = (TextBox)ph.FindControl("va_" + j);
TextBox tgl = (TextBox)ph.FindControl("tgl_" + j);
TextBox nokontrak = (TextBox)ph.FindControl("ref_" + j);
TextBox cs = (TextBox)ph.FindControl("cs_" + j);
TextBox unit = (TextBox)ph.FindControl("unit_" + j);
TextBox ket = (TextBox)ph.FindControl("ket_" + j);
DateTime TglBKM = Convert.ToDateTime(tgl.Text);
DateTime TglTTS = Convert.ToDateTime(tgl.Text);
string KetTag = "";
# region NoTTS yg pake format
//NoTTS
string formatMonth = Cf.Roman(TglTTS.Month);
string formatTahun = TglTTS.Year.ToString().Substring(2, 2);
string NoTTS2 = "";
bool hasfound = false;
while (!hasfound)
{
if (!Response.IsClientConnected)
{
break;
}
int num = Db.SingleInteger("SELECT COUNT(NoTTS2) FROM MS_TTS WHERE MONTH(TglTTS)='" + TglTTS.Month + "' AND YEAR(TglTTS)='" + TglTTS.Year + "'");
if (num == 0)
{
//TTS Pertama
int increment = num + 1;
string no = increment.ToString().PadLeft(7, '0');
NoTTS2 = "TTS/" + formatTahun + "/" + formatMonth + "/" + no;
}
else
{
//TTS Berikutnya
string terakhir = Db.SingleString("SELECT TOP 1 NoTTS2 FROM MS_TTS WHERE MONTH(TglTTS)='" + TglTTS.Month + "' AND YEAR(TglTTS)='" + TglTTS.Year + "' ORDER BY NoTTS2 DESC");
string temp = terakhir.Substring(terakhir.Length - 7);
int temp2 = Convert.ToInt32(temp) + 1;
string no = temp2.ToString().PadLeft(7, '0');
NoTTS2 = "TTS/" + formatTahun + "/" + formatMonth + "/" + no;
}
if (isUniqueTTS(NoTTS2))
{
hasfound = true;
}
}
#endregion
Db.Execute("EXEC spTTSRegistrasi"
+ " '" + Convert.ToDateTime(tgl.Text) + "'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'JUAL'"
+ ",'" + nokontrak.Text + "'"
+ ",'" + unit.Text + "'"
+ ",'" + cs.Text + "'"
+ ",'TR'"
+ ",'VA: " + Cf.Str(va.Text) + " " + ket.Text + "'"
);
string[] x = Cf.SplitByString(Bank, ";");
int NoTTS = Db.SingleInteger("SELECT TOP 1 NoTTS FROM MS_TTS ORDER BY NoTTS DESC");
Db.Execute("UPDATE MS_TTS"
+ " SET Acc = '" + x[0] + "'"
+ ", SubID='" + x[1] + "'"
+ " WHERE NoTTS = '" + NoTTS + "'"
);
for (int i = 0; i < rsTagihan.Rows.Count; i++)
{
TextBox lunas = (TextBox)ph.FindControl("lunas_" + j + "_" + i);
TextBox tag = (TextBox)ph.FindControl("tag_" + j + "_" + i);
if (lunas != null && tag != null && lunas.Text != "")
{
string[] arr = tag.Text.Split(';');
Db.Execute("EXEC spTTSAlokasi "
+ " '" + NoTTS + "'"
+ ", " + arr[0]
+ ", " + Convert.ToDecimal(lunas.Text)
);
KetTag = "<br>***ALOKASI PEMBAYARAN:<br>"
+ arr[1] + " (" + arr[2] + ")";
}
}
//==========================================
//Update NoBKM di MS_TTS
# region no bkm2
//NoBK
string formatMonth2 = Cf.Roman(TglBKM.Month);
string formatTahun2 = TglBKM.Year.ToString().Substring(2, 2);
string NoBKM2 = "";
bool hasfound2 = false;
while (!hasfound2)
{
if (!Response.IsClientConnected)
{
break;
}
int num = Db.SingleInteger("SELECT COUNT(NoBKM2) FROM MS_TTS WHERE Status='POST' AND MONTH(TglBKM)='" + TglBKM.Month + "' AND YEAR(TglBKM)='" + TglBKM.Year + "'");
if (num == 0)
{
//BKM Pertama
int increment = num + 1;
string no = increment.ToString().PadLeft(7, '0');
NoBKM2 = "KW/" + formatTahun2 + "/" + formatMonth2 + "/" + no;
}
else
{
//NoBKM Terakhir
string terakhir = Db.SingleString("SELECT TOP 1 NoBKM2 FROM MS_TTS WHERE Status='POST' AND MONTH(TglBKM)='" + TglBKM.Month + "' AND YEAR(TglBKM)='" + TglBKM.Year + "' ORDER BY NoBKM2 DESC");
string temp = terakhir.Substring(terakhir.Length - 7);
int temp2 = Convert.ToInt32(temp) + 1;
string no = temp2.ToString().PadLeft(7, '0');
NoBKM2 = "KW/" + formatTahun2 + "/" + formatMonth2 + "/" + no;
}
if (isUniqueBKM(NoBKM2))
{
hasfound2 = true;
}
}
#endregion
//=======================================
Db.Execute("EXEC spPostingTTS " + NoTTS + ",'" + TglBKM + "'");
Db.Execute("UPDATE MS_TTS SET ManualBKM = ManualTTS, NoBKM2='" + NoBKM2 + "', TglFP = '" + TglBKM + "', NoTTS2='" + NoTTS2 + "' WHERE NoTTS = " + NoTTS);
Db.Execute("UPDATE ISC064_MARKETINGJUAL..MS_PELUNASAN SET NoBKM2='" + NoBKM2 + "' WHERE NoTTS = " + NoTTS);
DataTable rsLog = 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 MS_TTS WHERE NoTTS = '" + NoTTS + "'");
string KetLog = Cf.LogCapture(rsLog)
+ KetTag;
Db.Execute("EXEC spLogTTS"
+ " 'VA'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + KetLog + "'"
+ ",'" + NoTTS + "'"
);
decimal LogID = Db.SingleDecimal("SELECT TOP 1 LogID FROM MS_TTS_LOG ORDER BY LogID DESC");
string Project = Db.SingleString("SELECT Project FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak = (SELECT Ref FROM MS_TTS WHERE NoTTS = '" + NoTTS + "')");
Db.Execute("UPDATE MS_TTS_LOG SET Project = '" + Project + "' WHERE LogID = " + LogID);
//Result
TableRow r = new TableRow();
TableCell c;
c = new TableCell();
c.Text = va.Text;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(tgl.Text);
r.Cells.Add(c);
c = new TableCell();
c.Text = "<a href=\"javascript:call('" + NoTTS + "')\">"
+ NoTTS2 + "</a>"
+ "<br /><i>POST</i>"
+ "<br />BKM: " + NoBKM2;
r.Cells.Add(c);
c = new TableCell();
c.Text = "JUAL No. " + nokontrak.Text
+ "<br />" + unit.Text
+ "<br />" + cs.Text;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Db.SingleDecimal("SELECT ISNULL(Total,0) FROM MS_TTS WHERE NoTTS=" + NoTTS + " AND Ref='" + nokontrak.Text + "'"));
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
Rpt.Border(r);
rpt.Rows.Add(r);
}
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 Project = " AND a.Project IN (" + Act.ProjectListSql + ")";
if (project.SelectedValue != "SEMUA")
{
Project = " AND a.Project = '" + project.SelectedValue + "'";
}
string Perusahaan = "";
if (pers.SelectedValue != "SEMUA")
{
Perusahaan = " AND a.Pers = '" + pers.SelectedValue + "'";
}
string aa = "";
if (UserAgent() > 0)
{
aa = " AND a.NoAgent = " + UserAgent();
}
decimal t1 = 0, t2 = 0, t3 = 0, t4 = 0, t5 = 0, t6 = 0, t7 = 0;
string strSql = "SELECT "
+ " a.TglKontrak"
+ " ,a.NoKontrak"
+ " ,a.Gross"
+ " ,a.DiskonRupiah"
+ " ,a.DiskonTambahan"
+ " ,a.BungaNominal"
+ " ,a.TglBatal"
+ " ,a.TotalLunasBatal"
+ " ,a.NilaiPulang"
+ " ,a.NilaiKontrak"
+ " ,a.Project"
+ " ,a.NilaiKlaim, a.NilaiPPN, a.NilaiDPP"
+ " ,b.NoUnit, b.Jenis, b.LuasSG"
+ " ,c.Nama, d.Nama AS Ag"
+ " 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.TglBatal >= '" + Dari + "' AND a.TglBatal <= '" + Sampai + "'"
+ " AND a.Status = 'B'"
+ Perusahaan
+ Project
+ aa
;
//Response.Write(strSql);
DataTable rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
r.Attributes["ondblclick"] = "javascript:popEditKontrak('" + rs.Rows[i]["NoKontrak"] + "');";
TableCell c;
c = new TableCell();
c.Text = (i + 1).ToString();
c.HorizontalAlign = HorizontalAlign.Center;
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]["NoKontrak"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
string unit = rs.Rows[i]["NoUnit"].ToString();
string[] nounit = Cf.SplitByString(unit, "/");
//string Tower = unit.Substring(1, 1);
//string Lantai = unit.Substring(3, 2);
//string nounit = unit.Substring(6, 2);
c = new TableCell();
c.Text = unit;// "SP" + nounit[0].Substring(1) + nounit[1] + nounit[2];
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Nama"].ToString();//Db.SingleString("SELECT Nama FROM MS_CUSTOMER WHERE NoCustomer = " + rs.Rows[i]["NoCustomer"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Ag"].ToString();//Db.SingleString("SELECT Nama FROM MS_AGENT a INNER JOIN MS_KONTRAK b ON a.NoAgent=b.NoAgent WHERE b.NoCustomer = " + rs.Rows[i]["NoCustomer"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoUnit"].ToString();//Db.SingleString("SELECT NoUnit FROM MS_KONTRAK WHERE NoCustomer = " + rs.Rows[i]["NoCustomer"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Jenis"].ToString();//Db.SingleString("SELECT Jenis FROM MS_KONTRAK WHERE NoCustomer = " + rs.Rows[i]["NoCustomer"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["LuasSG"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["Gross"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round((Convert.ToDecimal(rs.Rows[i]["DiskonRupiah"]) + Convert.ToDecimal(rs.Rows[i]["DiskonTambahan"]))));
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Math.Round(Convert.ToDecimal(rs.Rows[i]["BungaNominal"])));
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglBatal"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT Nama FROM " + Mi.DbPrefix + "SECURITY..REF_PROJECT WHERE Project = '" + rs.Rows[i]["Project"].ToString() + "'");
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["NilaiKontrak"]);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["TotalLunasBatal"]);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["NilaiPulang"]);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["NilaiKlaim"]);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
decimal DPP = Convert.ToDecimal(rs.Rows[i]["NilaiDPP"]); //(rs.Rows[i]["NilaiKontrak"]) - Convert.ToDecimal(rs.Rows[i]["NilaiPPN"]);//Math.Round(Convert.ToDecimal(rs.Rows[i]["NilaiKontrak"])/(decimal)1.1);
c.Text = Cf.Num(Math.Round(DPP));
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
decimal PPN = Convert.ToDecimal(rs.Rows[i]["NilaiPPN"]); //Math.Round(DPP * (decimal)0.1);
c.Text = Cf.Num(Math.Round(PPN));
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
rpt.Rows.Add(r);
t1 += Convert.ToDecimal(rs.Rows[i]["NilaiKontrak"]);
t2 += Convert.ToDecimal(rs.Rows[i]["NilaiKlaim"]);
t3 += Convert.ToDecimal(rs.Rows[i]["TotalLunasBatal"]);
t4 += Convert.ToDecimal(rs.Rows[i]["NilaiPulang"]);
t5 += DPP;
t6 += PPN;
t7 += Convert.ToDecimal(rs.Rows[i]["BungaNominal"]);
if (i == rs.Rows.Count - 1)
{
SubTotal(t1, t2, t3, t4, t5, t6, t7);
}
}
}
private void fillGN(DateTime Dari, DateTime Sampai)
{
string aa = "";
if (UserAgent() > 0)
{
aa = " AND b.NoAgent = " + UserAgent();
}
string nProject = "";
if (Project != "SEMUA")
{
nProject = " AND b.Project IN ('" + Project.Replace(",", "','") + "')";
}
string nPerusahaan = "";
if (Perusahaan != "SEMUA")
{
nPerusahaan = " AND b.Pers = '" + Perusahaan + "'";
}
string strSql = "SELECT a.*"
+ " FROM MS_KONTRAK_LOG a"
+ " INNER JOIN MS_KONTRAK b ON a.Pk = b.NoKontrak"
+ " WHERE a.Tgl >= '" + Dari + "'"
+ " AND a.Tgl <= '" + Sampai + "'"
+ " AND a.Aktivitas = 'GN'"
+ nProject
+ nPerusahaan
+ aa
;
DataTable rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
c = new TableCell();
c.Text = (i + 1).ToString();
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["Tgl"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Pk"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT NoUnit"
+ " FROM MS_KONTRAK "
+ " WHERE NoKontrak = '" + Cf.Pk(rs.Rows[i]["Pk"]) + "'"
;
c.Text = Db.SingleString(strSql);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT a.LuasSG"
+ " FROM MS_UNIT a"
+ " INNER JOIN MS_KONTRAK b ON a.NoStock = b.NoStock"
+ " WHERE b.NoKontrak = '" + Cf.Pk(rs.Rows[i]["Pk"]) + "'"
;
c.Text = Cf.Num(Db.SingleDecimal(strSql));
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT b.Nama"
+ " FROM MS_KONTRAK a"
+ " INNER JOIN MS_AGENT b"
+ " ON a.NoAgent = b.NoAgent"
+ " WHERE a.NoKontrak = '" + Cf.Pk(rs.Rows[i]["Pk"]) + "'"
;
c.Text = Db.SingleString(strSql);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["Tgl"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
//Cek nama sebelum dan sesudah Pengalihan Hak
string strBef = "", strAft = "";
string[] strTemp = Cf.SplitByString(rs.Rows[i]["Ket"].ToString(), "<br>");
bool isNext = false;
for (int j = 0; j < strTemp.Length; j++)
{
if (!isNext)
{
if (strTemp[j].StartsWith("Nama Customer"))
{
strBef = strTemp[j].ToString().Replace("Nama Customer : ", "");
isNext = true;
}
}
else
{
if (strTemp[j].StartsWith("Nama Customer"))
{
strAft = strTemp[j].ToString().Replace("Nama Customer : ", "");
break;
}
}
}
c = new TableCell();
c.Text = strBef;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT b.Nama"
+ " FROM MS_KONTRAK a"
+ " INNER JOIN MS_CUSTOMER b"
+ " ON a.NoCustomer = b.NoCustomer"
+ " WHERE a.NoKontrak = '" + Cf.Pk(rs.Rows[i]["Pk"]) + "'"
;
c.Text = Db.SingleString(strSql);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT NilaiKontrak"
+ " FROM MS_KONTRAK"
+ " WHERE NoKontrak = '" + Cf.Pk(rs.Rows[i]["Pk"]) + "'"
;
c.Text = Cf.Num(Db.SingleDecimal(strSql));
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
string biaya = "";
string[] strTemp1 = Cf.SplitByString(rs.Rows[i]["Ket"].ToString(), "<br>");
for (int j = 0; j < strTemp1.Length; j++)
{
if (strTemp1[j].StartsWith("Biaya"))
{
biaya = strTemp1[j].ToString().Replace("Biaya Administrasi : ", "");
}
}
c = new TableCell();
c.Text = biaya;
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT USERNAME.Nama"
+ " FROM ISC064_SECURITY..USERNAME"
+ " WHERE UserID in (select UserID from MS_KONTRAK_LOG"
+ " WHERE Aktivitas = 'GN' AND "
+ " Pk ='" + Cf.Pk(rs.Rows[i]["Pk"]) + "')"
;
c.Text = Db.SingleString(strSql);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT Nama FROM " + Mi.DbPrefix + "SECURITY..REF_PROJECT WHERE Project = '" + rs.Rows[i]["Project"].ToString() + "'");
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
rptD.Rows.Add(r);
}
}
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;
}
fillReserv(Dari, Sampai);
fillBatal(Dari, Sampai);
fillGN(Dari, Sampai);
string aa = "";
if (UserAgent() > 0)
{
aa = " AND b.NoAgent = " + UserAgent();
}
string Project = " AND b.Project IN (" + Act.ProjectListSql + ")";
if (project.SelectedValue != "SEMUA")
{
Project = " AND b.Project = '" + project.SelectedValue + "'";
}
string Perusahaan = "";
if (pers.SelectedValue != "SEMUA")
{
Perusahaan = " AND b.Pers = '" + pers.SelectedValue + "'";
}
string strSql = "SELECT a.*"
+ " FROM MS_KONTRAK_LOG a"
+ " INNER JOIN MS_KONTRAK b ON a.Pk = b.NoKontrak"
+ " WHERE a.Tgl >= '" + Dari + "'"
+ " AND a.Tgl <= '" + Sampai + "'"
+ " AND a.Aktivitas = 'GU'"
+ Project
+ Perusahaan
+ aa
;
DataTable rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
string strBef = "", strAft = "", Reason = "";
string[] strTemp = Cf.SplitByString(rs.Rows[i]["Ket"].ToString(), "<br>");
bool isNext = false;
for (int j = 0; j < strTemp.Length; j++)
{
if (!isNext)
{
if (strTemp[j].StartsWith("Unit"))
{
strBef = strTemp[j].ToString().Replace("Unit : ", "");
isNext = true;
}
}
else
{
if (strTemp[j].StartsWith("Unit"))
{
strAft = strTemp[j].ToString().Replace("Unit : ", "");
break;
}
}
}
TableRow r = new TableRow();
TableCell c;
c = new TableCell();
c.Text = (i + 1).ToString();
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT *"
+ " FROM MS_KONTRAK"
+ " WHERE NoKontrak = '" + rs.Rows[i]["Pk"].ToString() + "'"
;
DataTable tglK = Db.Rs(strSql);
c.Text = Cf.Day(tglK.Rows[0]["TglKontrak"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Pk"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT b.Nama"
+ " FROM MS_KONTRAK a"
+ " INNER JOIN MS_CUSTOMER b"
+ " ON a.NoCustomer = b.NoCustomer"
+ " WHERE a.NoKontrak = '" + Cf.Pk(rs.Rows[i]["Pk"]) + "'"
;
c.Text = Db.SingleString(strSql);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT b.Nama"
+ " FROM MS_KONTRAK a"
+ " INNER JOIN MS_AGENT b"
+ " ON a.NoAgent = b.NoAgent"
+ " WHERE a.NoKontrak = '" + Cf.Pk(rs.Rows[i]["Pk"]) + "'"
;
c.Text = Db.SingleString(strSql);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["Tgl"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = strBef;
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT LuasSG"
+ " FROM MS_UNIT"
+ " WHERE NoUnit = '" + strBef + "'"
;
c.Text = Cf.Num(Db.SingleDecimal(strSql));
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
c.Text = strAft;
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT LuasSG"
+ " FROM MS_UNIT"
+ " WHERE NoUnit = '" + strAft + "'"
;
c.Text = Cf.Num(Db.SingleDecimal(strSql));
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT NilaiKontrak"
+ " FROM MS_KONTRAK"
+ " WHERE NoKontrak = '" + Cf.Pk(rs.Rows[i]["Pk"]) + "'"
;
c.Text = Cf.Num(Db.SingleDecimal(strSql));
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
string biaya = "";
string[] strTemp1 = Cf.SplitByString(rs.Rows[i]["Ket"].ToString(), "<br>");
for (int j = 0; j < strTemp1.Length; j++)
{
if (strTemp1[j].StartsWith("Biaya"))
{
biaya = strTemp1[j].ToString().Replace("Biaya Administrasi : ", "");
}
}
c = new TableCell();
c.Text = biaya;
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT USERNAME.Nama"
+ " FROM ISC064_SECURITY..USERNAME"
+ " WHERE UserID in (select UserID from MS_KONTRAK_LOG"
+ " WHERE Aktivitas = 'GU' AND "
+ " Pk ='" + Cf.Pk(rs.Rows[i]["Pk"]) + "')"
;
c.Text = Db.SingleString(strSql);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT Nama FROM " + Mi.DbPrefix + "SECURITY..REF_PROJECT WHERE Project = '" + rs.Rows[i]["Project"].ToString() + "'");
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
rpt.Rows.Add(r);
}
}
private void fillBatal(DateTime Dari, DateTime Sampai)
{
string aa = "";
if (UserAgent() > 0)
{
aa = " AND b.NoAgent = " + UserAgent();
}
string Project = "";
if (project.SelectedValue != "SEMUA")
{
Project = " AND b.Project = '" + project.SelectedValue + "'";
}
string Perusahaan = "";
if (pers.SelectedValue != "SEMUA")
{
Perusahaan = " AND b.Pers = '" + pers.SelectedValue + "'";
}
string strSql = "SELECT a.*"
+ " FROM MS_KONTRAK_LOG a"
+ " INNER JOIN MS_KONTRAK b ON a.Pk = b.NoKontrak"
+ " WHERE a.Tgl >= '" + Dari + "'"
+ " AND a.Tgl <= '" + Sampai + "'"
+ " AND a.Aktivitas = 'BATAL'"
+ Project
+ Perusahaan
+ aa;
;
DataTable rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
c = new TableCell();
c.Text = (i + 1).ToString();
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["Tgl"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Pk"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT b.Nama"
+ " FROM MS_KONTRAK a"
+ " INNER JOIN MS_CUSTOMER b"
+ " ON a.NoCustomer = b.NoCustomer"
+ " WHERE a.NoKontrak = '" + Cf.Pk(rs.Rows[i]["Pk"]) + "'"
;
c.Text = Db.SingleString(strSql);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT b.Nama"
+ " FROM MS_KONTRAK a"
+ " INNER JOIN MS_AGENT b"
+ " ON a.NoAgent = b.NoAgent"
+ " WHERE a.NoKontrak = '" + Cf.Pk(rs.Rows[i]["Pk"]) + "'"
;
c.Text = Db.SingleString(strSql);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT NoUnit"
+ " FROM MS_KONTRAK"
+ " WHERE NoKontrak = '" + Cf.Pk(rs.Rows[i]["Pk"]) + "'"
;
c.Text = Db.SingleString(strSql);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT b.LuasSG"
+ " FROM MS_KONTRAK a"
+ " INNER JOIN MS_UNIT b ON a.NoStock = b.NoStock"
+ " WHERE a.NoKontrak = '" + Cf.Pk(rs.Rows[i]["Pk"]) + "'"
;
c.Text = Cf.Num(Db.SingleDecimal(strSql));
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["Tgl"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
string[] strTemp = Cf.SplitByString(rs.Rows[i]["Ket"].ToString(), "<br>");
string Reason = "";
for (int j = 0; j < strTemp.Length; j++)
{
if (strTemp[j].StartsWith("Alasan"))
{
Reason = strTemp[j].ToString().Replace("Alasan Pembatalan : ", "");
}
}
c = new TableCell();
c.Text = Reason;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT NilaiKontrak"
+ " FROM MS_KONTRAK"
+ " WHERE NoKontrak = '" + Cf.Pk(rs.Rows[i]["Pk"]) + "'"
;
c.Text = Convert.ToString(Cf.Num(Db.SingleDecimal(strSql)));
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
string biaya = "";
string[] strTemp1 = Cf.SplitByString(rs.Rows[i]["Ket"].ToString(), "<br>");
for (int j = 0; j < strTemp1.Length; j++)
{
if (strTemp1[j].StartsWith("Biaya"))
{
biaya = strTemp1[j].ToString().Replace("Biaya Administrasi : ", "");
}
}
c = new TableCell();
c.Text = biaya;
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT USERNAME.Nama"
+ " FROM ISC064_SECURITY..USERNAME"
+ " WHERE UserID in (select UserID from MS_KONTRAK_LOG"
+ " WHERE Aktivitas = 'BATAL' AND "
+ " Pk ='" + Cf.Pk(rs.Rows[i]["Pk"]) + "')"
;
c.Text = Db.SingleString(strSql);
c.HorizontalAlign = HorizontalAlign.Right;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT Nama FROM " + Mi.DbPrefix + "SECURITY..REF_PROJECT WHERE Project = '" + rs.Rows[i]["Project"].ToString() + "'");
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
rptB.Rows.Add(r);
}
//return 0;
}
private void Fill()
{
rpt.Style["border-collapse"] = "collapse";
DateTime Dari = Convert.ToDateTime(dari.Text);
DateTime Sampai = Convert.ToDateTime(sampai.Text);
if (Dari > Sampai)
{
DateTime x = Sampai;
Sampai = Dari;
Dari = x;
}
string Project = " AND MS_KONTRAK.Project IN (" + Act.ProjectListSql + ")";
if (project.SelectedValue != "SEMUA")
{
Project = " AND MS_KONTRAK.Project = '" + project.SelectedValue + "'";
}
string Perusahaan = "";
if (pers.SelectedValue != "SEMUA")
{
Perusahaan = " AND MS_KONTRAK.Pers = '" + pers.SelectedValue + "'";
}
string strSql = "SELECT * FROM MS_KONTRAK"
+ " INNER JOIN " + Mi.DbPrefix + "MARKETINGJUAL..MS_CUSTOMER ON MS_CUSTOMER.NoCustomer = MS_KONTRAK.NoCustomer"
+ " INNER JOIN " + Mi.DbPrefix + "MARKETINGJUAL..MS_UNIT ON MS_UNIT.NoUnit = MS_KONTRAK.NoUnit"
+ " WHERE PPJB != 'B'"
+ " AND CONVERT(varchar,TglPPJB,112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(varchar,TglPPJB,112) <= '" + Cf.Tgl112(Sampai) + "'"
+ Project + Perusahaan
+ " ORDER BY MS_KONTRAK.NoPPJB";
DataTable rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
bool a = true;
bool b = true;
bool d = true;
bool e = true;
bool f = true;
bool g = true;
bool h = true;
bool j = true;
bool k = true;
r.Attributes["ondblclick"] = "popEditKontrak('" + rs.Rows[i]["NoKontrak"] + "')";
c = new TableCell();
c.Text = (i + 1).ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
c.Text = rs.Rows[i]["Nama"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
string[] x = Cf.SplitByString(rs.Rows[i]["NoUnit"].ToString(), "/");
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
c.Text = rs.Rows[i]["Nomor"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
c.Text = rs.Rows[i]["Lokasi"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
c.Text = rs.Rows[i]["Lantai"].ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
c.Text = Cf.Num(rs.Rows[i]["LuasSG"]) + "m<sup>2</sup>";
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
// B = BELUM
// D = SUDAH REGIS
// T = PROSES TTD
// S = SELESAI
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
if (rs.Rows[i]["PPJB"].ToString() == "D")
{
c.Text = "Teregister";
}
else if (rs.Rows[i]["PPJB"].ToString() == "T")
{
c.Text = "Proses Tanda Tangan";
}
else if (rs.Rows[i]["PPJB"].ToString() == "S")
{
c.Text = "Selesai";
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
if (rs.Rows[i]["TglPPJB"] != DBNull.Value)
{
c.Text = Cf.Day(Convert.ToDateTime(rs.Rows[i]["TglPPJB"]));
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
if (rs.Rows[i]["TglCetakPPJB"] != DBNull.Value)
{
c.Text = Cf.Day(Convert.ToDateTime(rs.Rows[i]["TglCetakPPJB"]));
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
if (rs.Rows[i]["TglTTDPPJB"] != DBNull.Value)
{
c.Text = Cf.Day(Convert.ToDateTime(rs.Rows[i]["TglTTDPPJB"]));
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
c.Text = rs.Rows[i]["NoPPJB"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
c.Text = rs.Rows[i]["NoPPJBm"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
if (rs.Rows[i]["PPJBu"].ToString() == "1")
{
c.Text = "Manual";
}
else
{
c.Text = "System";
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
c.Text = rs.Rows[i]["NoKTP"].ToString() + " ";
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
if (rs.Rows[i]["KTPMilik"].ToString() == "0")
{
c.Text = "Tidak Ada";
}
else
{
c.Text = "Ada";
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
if (rs.Rows[i]["KTPIstri"].ToString() == "1")
{
c.Text = "Ada";
}
else
{
c.Text = "Tidak Ada";
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
if (rs.Rows[i]["KK"].ToString() == "0")
{
c.Text = "Tidak Ada";
a = false;
}
else if (rs.Rows[i]["KK"].ToString() == "1")
{
c.Text = "Ada";
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
if (rs.Rows[i]["SNKH"].ToString() == "0")
{
c.Text = "Tidak Ada";
b = false;
}
else if (rs.Rows[i]["SNKH"].ToString() == "1")
{
c.Text = "Ada";
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
if (rs.Rows[i]["SKK"].ToString() == "0")
{
c.Text = "Tidak Ada";
k = false;
}
else if (rs.Rows[i]["SKK"].ToString() == "1")
{
c.Text = "Ada";
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
if (rs.Rows[i]["RK"].ToString() == "0")
{
c.Text = "Tidak Ada";
d = false;
}
else if (rs.Rows[i]["RK"].ToString() == "1")
{
c.Text = "Ada";
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
if (rs.Rows[i]["BT"].ToString() == "0")
{
c.Text = "Tidak Ada";
e = false;
}
else if (rs.Rows[i]["BT"].ToString() == "1")
{
c.Text = "Ada";
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
if (rs.Rows[i]["KW"].ToString() == "0")
{
c.Text = "Tidak Ada";
f = false;
}
else if (rs.Rows[i]["KW"].ToString() == "1")
{
c.Text = "Ada";
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
c.Text = rs.Rows[i]["NPWP"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
c.Text = rs.Rows[i]["NoKontrak"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
if (rs.Rows[i]["DU"].ToString() == "0")
{
c.Text = "Tidak Ada";
g = false;
}
else if (rs.Rows[i]["DU"].ToString() == "1")
{
c.Text = "Ada";
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
if (rs.Rows[i]["DL"].ToString() == "0")
{
c.Text = "Tidak Ada";
h = false;
}
else if (rs.Rows[i]["DL"].ToString() == "1")
{
c.Text = "Ada";
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
if (rs.Rows[i]["SM"].ToString() == "0")
{
c.Text = "Tidak Ada";
j = false;
}
else if (rs.Rows[i]["SM"].ToString() == "1")
{
c.Text = "Ada";
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
bool z = a & b & d & e & f & g & h & j & k;
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
if (z == false)
{
c.Text = "Tidak Lengkap";
}
else if (z == true)
{
c.Text = "Lengkap";
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
string ker = "";
if (a == false)
{
ker += "Kartu Keluarga,";
}
if (b == false)
{
ker += "Surat Nikah,";
}
if (k == false)
{
ker += "SKK,";
}
if (d == false)
{
ker += "RK,";
}
if (e == false)
{
ker += "BT,";
}
if (f == false)
{
ker += "KW,";
}
if (g == false)
{
ker += "Denah Unit,";
}
if (h == false)
{
ker += "Denah Lantai,";
}
if (j == false)
{
ker += "Spesifikasi Material";
}
c = new TableCell();
c.Text = ker;
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.VerticalAlign = VerticalAlign.Top;
if (rs.Rows[i]["TglLengkapPPJB"] != DBNull.Value)
{
c.Text = Cf.Day(Convert.ToDateTime(rs.Rows[i]["TglLengkapPPJB"]));
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT Nama FROM " + Mi.DbPrefix + "SECURITY..REF_PROJECT WHERE Project = '" + rs.Rows[i]["Project"].ToString() + "'");
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
rpt.Rows.Add(r);
}
}
private void Fill()
{
DateTime Dari = Db.SingleTime("SELECT FilterDari FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
DateTime Sampai = Db.SingleTime("SELECT FilterSampai FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
string UserID = "";
if (Kasir != "SEMUA")
{
UserID = " AND a.UserID = '" + Kasir + "'";
}
string agent = "";
if (UserAgent() > 0)
{
agent = " AND (SELECT NoAgent FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak = a.Ref) = " + UserAgent();
}
string nProject = "";
if (Project != "SEMUA")
{
nProject = " AND c.Project IN ('" + Project.Replace(",", "','") + "')";
}
string nPerusahaan = "";
if (Perusahaan != "SEMUA")
{
nPerusahaan = " AND c.Pers = '" + Perusahaan + "'";
}
decimal t = 0, t1 = 0, t2 = 0, t3 = 0;
string strSql = "SELECT a.NoTTS AS NoTTS,a.NoTTS2,a.Project, b.Tgl AS Tgl, a.TglTTS AS TglTTS, a.UserID AS UserID, a.ManualTTS, "
+ " a.IP as IP, a.Tipe as Tipe, a.Ref as Ref, a.Unit as Unit, a.Customer AS Customer, "
+ " a.CaraBayar as CaraBayar, a.Ket as Ket, a.NoBG as NoBG, a.TglBG as TglBG, "
+ " a.Titip as Titip, a.Tolak as Tolak, a.NoBKM as NoBKM, a.TglBKM as TglBKM, a.ManualBKM, "
+ " a.NoSlip as NoSlip, a.LebihBayar, a.Total2 ,a.Acc as Acc, a.NilaiKembali as NilaiKembali, b.Ket as Keterangan"
+ " FROM MS_TTS a"
+ " INNER JOIN MS_TTS_LOG b ON a.NoTTS = CONVERT(INT, b.Pk)"
+ " INNER JOIN " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK c ON a.Ref = c.NoKontrak"
+ " WHERE 1 = 1"
+ " AND CONVERT(VARCHAR, b.Tgl, 112) >= '" + Cf.Tgl112(Dari) + "'"
+ " AND CONVERT(VARCHAR, b.Tgl, 112) <= '" + Cf.Tgl112(Sampai) + "'"
+ " AND b.Aktivitas = 'VOID'"
+ nProject
+ nPerusahaan
+ UserID
+ agent
+ " ORDER BY b.Tgl";
DataTable rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
TableRow r = new TableRow();
TableCell c;
r.VerticalAlign = VerticalAlign.Top;
r.Attributes["ondblclick"] = "popEditTTS('" + rs.Rows[i]["NoTTS"] + "')";
c = new TableCell();
c.Text = rs.Rows[i]["NoTTS2"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["Tgl"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
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]["UserID"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = 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 = rs.Rows[i]["CaraBayar"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Ket"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["NoBG"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglBG"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
if ((int)rs.Rows[i]["NoBKM"] != 0)
{
c.Text = rs.Rows[i]["ManualBKM"].ToString().PadLeft(7, '0');
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglBKM"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Acc"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT NAMA FROM " + Mi.DbPrefix + "SECURITY..REF_PROJECT WHERE PROJECT='" + rs.Rows[i]["Project"].ToString() + "'");
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
string sTotal = "";
string[] sTemp = Cf.SplitByString(rs.Rows[i]["Keterangan"].ToString(), "<br>");
for (int j = 0; j < sTemp.Length; j++)
{
if (sTemp[j].StartsWith("Total :"))
{
sTotal = sTemp[j].ToString().Replace("Total :", "");
}
}
c = new TableCell();
c.Text = Cf.Num(sTotal);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["LebihBayar"]);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(rs.Rows[i]["Total2"]);
c.HorizontalAlign = HorizontalAlign.Right;
c.Wrap = false;
r.Cells.Add(c);
rpt.Rows.Add(r);
t = t + (decimal)rs.Rows[i]["NilaiKembali"];
t1 = t1 + (decimal)Convert.ToDecimal(sTotal);
t2 = t2 + (decimal)Convert.ToDecimal(rs.Rows[i]["LebihBayar"]);
t3 = t3 + (decimal)Convert.ToDecimal(rs.Rows[i]["Total2"]);
if (i == rs.Rows.Count - 1)
{
SubTotal("GRAND TOTAL", t, t1, t2, t3);
}
}
}
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);
}
}
private void Fill()
{
//DateTime Dari = Convert.ToDateTime(dari.Text);
string nProject = "";
if (Project != "SEMUA")
{
nProject = " AND a.Project IN('" + Project.Replace(",", "','") + "')";
}
string nPerusahaan = "";
if (Perusahaan != "SEMUA")
{
nPerusahaan = " AND a.Pers = '" + Perusahaan + "'";
}
decimal t1 = 0;
decimal t2 = 0;
decimal t3 = 0;
decimal t4 = 0;
decimal t5 = 0;
decimal t6 = 0;
string lantai = "";
string to = "";
if (Lt != "0")
{
lantai = " AND LEFT(c.NoUnit,7) like '%" + Lt + "%'";
}
if (Tower != "0")
{
to = " AND c.Lokasi ='" + Tower.Replace("%", " ") + "'";
}
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 "
+ nProject
+ nPerusahaan
+ lantai
+ to
+ " ORDER BY a.Status, a.TglKontrak";
DataTable rs = Db.Rs(strSql);
TableHeaderRow trow = new TableHeaderRow();
TableHeaderCell tc;
//trow.BackColor = Color.LightGray;
trow.HorizontalAlign = HorizontalAlign.Center;
tc = new TableHeaderCell();
tc.Text = "NO.";
tc.RowSpan = 2;
tc.Wrap = false; tc.Attributes["style"] = "margin:1px;";
trow.Cells.Add(tc);
tc = new TableHeaderCell();
tc.Text = "CUSTOMER";
tc.RowSpan = 2;
tc.Wrap = false;
trow.Cells.Add(tc);
tc = new TableHeaderCell();
tc.Text = "STATUS";
tc.RowSpan = 2;
tc.Wrap = false;
trow.Cells.Add(tc);
tc = new TableHeaderCell();
tc.Text = "TGL BF";
tc.RowSpan = 2;
tc.Wrap = false;
trow.Cells.Add(tc);
tc = new TableHeaderCell();
tc.Text = "TYPE";
tc.ColumnSpan = 4;
tc.Wrap = false;
trow.Cells.Add(tc);
tc = new TableHeaderCell();
tc.Text = "HARGA";
tc.RowSpan = 2;
tc.Wrap = false;
//tc.Attributes["style"] = "padding:100px; margin:100px";
trow.Cells.Add(tc);
tc = new TableHeaderCell();
tc.Text = "PPN";
tc.RowSpan = 2;
tc.Wrap = false;
trow.Cells.Add(tc);
tc = new TableHeaderCell();
tc.Text = "TOTAL HARGA";
tc.RowSpan = 2;
tc.Wrap = false;
trow.Cells.Add(tc);
tc = new TableHeaderCell();
tc.Text = "TYPE OF PAYMENT";
tc.RowSpan = 2;
tc.Wrap = false;
trow.Cells.Add(tc);
tc = new TableHeaderCell();
tc.Text = "Akumulasi Sebelum " + Cf.Monthname(Convert.ToInt32(BlnDari)) + " " + Convert.ToInt32(ThnDari);
tc.RowSpan = 2;
tc.Wrap = false;
trow.Cells.Add(tc);
DateTime awal = new DateTime(Convert.ToInt32(ThnDari), Convert.ToInt32(BlnDari), 1);
DateTime akhir = Cf.AkhirBulan(Convert.ToInt32(BlnSampai), Convert.ToInt32(ThnSampai));
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 TableHeaderCell();
tc.Text = r.ToString();
tc.ColumnSpan = 4;
tc.Wrap = false;
trow.Cells.Add(tc);
}
tc = new TableHeaderCell();
tc.Text = "Total Penerimaan Hingga " + Cf.Monthname(Convert.ToInt32(BlnSampai)) + " " + Convert.ToInt32(ThnSampai) + " (Angsuran dan Saldo Awal)";
tc.RowSpan = 2;
tc.Wrap = false;
trow.Cells.Add(tc);
tc = new TableHeaderCell();
tc.Text = "Total Penerimaan Lain-Lain Hingga " + Cf.Monthname(Convert.ToInt32(BlnSampai)) + " " + Convert.ToInt32(ThnSampai) + " (Admin dan Memo Selain Saldo Awal)";
tc.RowSpan = 2;
tc.Wrap = false;
trow.Cells.Add(tc);
tc = new TableHeaderCell();
tc.Text = "Persentase Penerimaan";
tc.RowSpan = 2;
tc.Wrap = false;
trow.Cells.Add(tc);
tc = new TableHeaderCell();
tc.Text = "Sisa Angsuran";
tc.RowSpan = 2;
tc.Wrap = false;
trow.Cells.Add(tc);
rpt.Rows.Add(trow);
TableHeaderRow trow1 = new TableHeaderRow();
TableHeaderCell tc1;
//trow1.BackColor = Color.LightGray;
trow1.HorizontalAlign = HorizontalAlign.Center;
tc1 = new TableHeaderCell();
tc1.Text = "NO UNIT";
trow1.Cells.Add(tc1);
tc1 = new TableHeaderCell();
tc1.Text = "TOWER";
trow1.Cells.Add(tc1);
tc1 = new TableHeaderCell();
tc1.Text = "LANTAI";
trow1.Cells.Add(tc1);
tc1 = new TableHeaderCell();
tc1.Text = "LUAS";
trow1.Cells.Add(tc1);
foreach (var r in list)
{
string[] a = Cf.SplitByString(r.ToString(), "-");
tc1 = new TableHeaderCell();
tc1.Text = "1 s/d 7 ";
trow1.Cells.Add(tc1);
tc1 = new TableHeaderCell();
tc1.Text = "8 s/d 14 ";
trow1.Cells.Add(tc1);
tc1 = new TableHeaderCell();
tc1.Text = "15 s/d 21 ";
trow1.Cells.Add(tc1);
tc1 = new TableHeaderCell();
tc1.Text = "22 s/d " + Cf.AkhirBulan(Convert.ToInt32(a[0]), Convert.ToInt32(a[1])).Day;
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;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
if (rs.Rows[i]["Status"].ToString() == "A")
{
c.Text = "<b>Aktif</b>";
}
else
{
c.Text = "Batal";
}
c.HorizontalAlign = HorizontalAlign.Left;
c.Wrap = false;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["TglKontrak"]);
c.Wrap = false;
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 mProject = Db.SingleString("SELECT Project FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_UNIT WHERE NoUnit = '" + rs.Rows[i]["NoUnit"] + "'");
string ParamID = "FormatUnit" + mProject;
string pemisah = Db.SingleString("SELECT Value FROM " + Mi.DbPrefix + "SECURITY..REF_PARAM WHERE ParamID = '" + ParamID + "'");
string[] x = Cf.SplitByString(rs.Rows[i]["NoUnit"].ToString(), pemisah);
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"]) + "m<sup>2</sup>";
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Convert.ToDecimal(rs.Rows[i]["NilaiDPP"])).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(Convert.ToDecimal(rs.Rows[i]["NilaiPPN"])).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(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(ThnDari), Convert.ToInt32(BlnDari), 1);
c = new TableCell();
c.Text = Cf.Num(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() + "' AND CaraBayar!='PPA'")).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 = Cf.Num(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() + "' AND CaraBayar!='PPA'")).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(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() + "' AND CaraBayar!='PPA'")).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(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() + "' AND CaraBayar!='PPA'")).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(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 = Cf.Num(Ang(rs.Rows[i]["NoKontrak"].ToString(), akhir));
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(LainLain(rs.Rows[i]["NoKontrak"].ToString(), akhir));
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Num(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() + "' AND CaraBayar!='PPA'");
decimal sisa = tagihan - pelunasan;
c = new TableCell();
c.Text = Cf.Num(sisa).ToString();
c.Wrap = false;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
rpt.Rows.Add(r);
}
}
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 strSql = "SELECT *"
+ " FROM MS_KONTRAK_LOG"
+ " WHERE Tgl >= '" + Dari + "'"
+ " AND Tgl <= '" + Sampai + "'"
+ " AND Aktivitas = 'BATAL'"
;
DataTable rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
string strUnit = "", strCustomer = "";
string Ag = "", Reason = "";
string[] strTemp = Cf.SplitByString(rs.Rows[i]["Ket"].ToString(), "<br>");
for (int j = 0; j < strTemp.Length; j++)
{
if (strTemp[j].StartsWith("Unit"))
{
strUnit = strTemp[j].ToString().Replace("Unit : ", "");
}
if (strTemp[j].StartsWith("Customer"))
{
strCustomer = strTemp[j].ToString().Replace("Customer : ", "");
}
if (strTemp[j].StartsWith("Agent"))
{
Ag = strTemp[j].ToString().Replace("Agent : ", "");
}
if (strTemp[j].StartsWith("Alasan"))
{
Reason = strTemp[j].ToString().Replace("Alasan Pembatalan : ", "");
}
}
TableRow r = new TableRow();
TableCell c;
c = new TableCell();
c.Text = (i + 1).ToString();
c.HorizontalAlign = HorizontalAlign.Center;
r.Cells.Add(c);
c = new TableCell();
c.Text = Cf.Day(rs.Rows[i]["Tgl"]);
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Pk"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = strCustomer;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = strUnit;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Ag;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
c = new TableCell();
c.Text = Reason;
c.HorizontalAlign = HorizontalAlign.Left;
r.Cells.Add(c);
rpt.Rows.Add(r);
}
}
private bool Save()
{
if (valid())
{
string[] x = Cf.SplitByString(Acc, ";");
string AccBaru = Cf.Pk(acc.Text);
string Rekening = Cf.Str(rekening.Text);
string SubID = Cf.Str(subid.Text);
string Bank = Cf.Str(bank.Text);
string Cabang = Cf.Str(cabang1.Text);
string AtasNama = Cf.Str(atasnama.Text);
decimal SaldoAwal = Convert.ToDecimal(saldoawal.Text);
string Project = Cf.Pk(project.SelectedValue);
DataTable rsBef = Db.Rs("SELECT "
+ " Acc AS [No. Account]"
+ ",Rekening"
+ ",SubID"
+ ",Project"
+ ",Bank"
+ ",Cabang"
+ ",AtasNama AS [Atas Nama]"
+ ",SaldoAwal AS [Saldo Awal]"
+ " FROM " + Mi.DbPrefix + "FINANCEAR..REF_ACC "
+ " WHERE Acc = '" + x[0] + "' AND SubID='" + x[1] + "'");
Db.Execute("EXEC " + Mi.DbPrefix + "FINANCEAR..spAccEdit"
+ " '" + x[0] + "'"
+ ",'" + AccBaru + "'"
+ ",'" + Rekening + "'"
+ ",'" + Bank + "'"
+ ",'" + Cabang + "'"
+ ",'" + AtasNama + "'"
+ ", " + SaldoAwal
+ ",'" + SubID + "'"
+ ",'" + x[1] + "'"
+ ",'" + Project + "'"
);
DataTable rsAft = Db.Rs("SELECT "
+ " Acc AS [No. Account]"
+ ",Rekening"
+ ",SubID"
+ ",Project"
+ ",Bank"
+ ",Cabang"
+ ",AtasNama AS [Atas Nama]"
+ ",SaldoAwal AS [Saldo Awal]"
+ " FROM " + Mi.DbPrefix + "FINANCEAR..REF_ACC "
+ " WHERE Acc = '" + AccBaru + "' AND SubID='" + SubID + "'");
string KetLog = Cf.LogCompare(rsBef, rsAft);
Db.Execute("EXEC " + Mi.DbPrefix + "FINANCEAR..spLogAcc"
+ " 'EDIT'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + KetLog + "'"
+ ",'" + AccBaru + ";" + SubID + "'"
);
decimal LogID = Db.SingleDecimal("SELECT TOP 1 LogID FROM REF_ACC_LOG ORDER BY LogID DESC");
Db.Execute("UPDATE REF_ACC_LOG SET Project = '" + Project + "' WHERE LogID = " + LogID);
return(true);
}
else
{
return(false);
}
}
private void Fill()
{
DateTime Dari = Db.SingleTime("SELECT FilterDari FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
DateTime Sampai = Db.SingleTime("SELECT FilterSampai FROM LapPDF WHERE AttachmentID='" + AttachmentID + "'");
string aa = "";
if (UserAgent() > 0)
{
aa = " AND b.NoAgent = " + UserAgent();
}
string nProject = "";
if (Project != "SEMUA")
{
nProject = " AND b.Project IN ('" + Project.Replace(",", "','") + "')";
}
string nPerusahaan = "";
if (Perusahaan != "SEMUA")
{
nPerusahaan = " AND b.Pers = '" + Perusahaan + "'";
}
string strSql = "SELECT a.*"
+ " FROM MS_KONTRAK_LOG a"
+ " INNER JOIN MS_KONTRAK b ON a.Pk = b.NoKontrak"
+ " WHERE a.Tgl >= '" + Dari + "'"
+ " AND a.Tgl <= '" + Sampai + "'"
+ " AND a.Aktivitas = 'GN'"
+ nProject
+ nPerusahaan
+ aa
;
DataTable rs = Db.Rs(strSql);
for (int i = 0; i < rs.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
string strBef = "", strAft = "", strTgl = "";
string[] strTemp = Cf.SplitByString(rs.Rows[i]["Ket"].ToString(), "<br>");
bool isNext = false;
for (int j = 0; j < strTemp.Length; j++)
{
if (!isNext)
{
if (strTemp[j].StartsWith("Nama Customer"))
{
strBef = strTemp[j].ToString().Replace("Nama Customer : ", "");
isNext = true;
}
}
else
{
if (strTemp[j].StartsWith("Nama Customer"))
{
strAft = strTemp[j].ToString().Replace("Nama Customer : ", "");
break;
}
}
}
strTgl = "";// strTemp[10].ToString().Replace("Tgl Pengalihan Hak : ", "");
for (int k = 0; k < strTemp.Length; k++)
{
if (strTemp[k].StartsWith("Tgl Pengalihan Hak"))
{
strTgl = strTemp[k].ToString().Replace("Tgl Pengalihan Hak : ", "");
}
}
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 = strTgl;//Cf.Day(rs.Rows[i]["Tgl"]);
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
c.Text = rs.Rows[i]["Pk"].ToString();
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT NoUnit"
+ " FROM MS_KONTRAK"
+ " WHERE NoKontrak = '" + Cf.Pk(rs.Rows[i]["Pk"]) + "'"
;
c.Text = Db.SingleString(strSql);
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT a.LuasSG"
+ " FROM MS_UNIT a"
+ " INNER JOIN MS_KONTRAK b ON a.NoStock = b.NoStock"
+ " WHERE b.NoKontrak = '" + Cf.Pk(rs.Rows[i]["Pk"]) + "'"
;
c.Text = Cf.Num(Db.SingleDecimal(strSql));
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
c.Text = strBef;
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
c.Text = strAft;
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
strSql = "SELECT b.Nama"
+ " FROM MS_KONTRAK a"
+ " INNER JOIN MS_AGENT b ON a.NoAgent = b.NoAgent"
+ " WHERE NoKontrak = '" + Cf.Pk(rs.Rows[i]["Pk"]) + "'"
;
c.Text = Db.SingleString(strSql);
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
c = new TableCell();
c.Text = Db.SingleString("SELECT Nama FROM " + Mi.DbPrefix + "SECURITY..REF_PROJECT WHERE Project = '" + rs.Rows[i]["Project"].ToString() + "'");
c.HorizontalAlign = HorizontalAlign.Left;
c.VerticalAlign = VerticalAlign.Top;
r.Cells.Add(c);
rpt.Rows.Add(r);
}
}
protected void save_Click(object sender, System.EventArgs e)
{
if (datavalid() && ValidNilai())
{
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;
string Project = Db.SingleString("SELECT Project FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak = '" + Ref + "'");
string NamaProject = Db.SingleString("SELECT Nama FROM " + Mi.DbPrefix + "SECURITY..REF_PROJECT WHERE Project = '" + Project + "'");
//Numerator
string NoTTS2 = Numerator.TTS(TglTTS.Month, TglTTS.Year, Project);
Db.Execute("EXEC spTTSRegistrasi"
+ " '" + TglTTS + "'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Tipe + "'"
+ ",'" + Ref + "'"
+ ",'" + Unit + "'"
+ ",'" + Customer + "'"
+ ",'" + CaraBayar + "'"
+ ",'" + Ket + "'"
);
string[] x = Cf.SplitByString(ddlAcc.SelectedValue, ";");
// string[] y = Cf.SplitByString(ddlpt.SelectedValue, ";");
int noTTS = Db.SingleInteger("SELECT TOP 1 NoTTS FROM MS_TTS ORDER BY NoTTS DESC");
Db.Execute("UPDATE MS_TTS SET NoTTS2 = '" + NoTTS2 + "',Project = '" + Project + "',NamaProject = '" + NamaProject + "' WHERE NoTTS ='" + noTTS + "'");
Db.Execute("UPDATE ISC064_MARKETINGJUAL..MS_PELUNASAN SET NoTTS2='" + NoTTS2 + "' WHERE NoTTS ='" + noTTS + "'");
decimal total2 = (Convert.ToDecimal(gt.Text) + LebihBayar) - AdminBank;
Db.Execute("UPDATE MS_TTS"
+ " SET Acc = '" + x[0] + "'"
+ ", SubID='" + x[1] + "'"
+ ", AdminBank='" + AdminBank + "' "
// + ", Total2 = '" + total2 + "'"
+ ", LebihBayar = '" + LebihBayar + "'" //kurang bayar
+ ", LB = '" + LB + "'" //lebih bayar
+ ", SumberBayar = " + sumberdana.SelectedValue
//+ ", NoPT='" + y[0] + "'"
+ " WHERE NoTTS = " + noTTS);
if (anonim.SelectedValue != "")
{
Db.Execute("UPDATE MS_TTS SET NoAnonim = '" + anonim.SelectedValue + "' WHERE NoTTS = '" + noTTS + "' ");
}
//khusus cek giro
if (carabayar.SelectedValue == "BG")
{
string NoBG = Cf.Pk(nobg.Text);
DateTime TglBG = Convert.ToDateTime(tglbg.Text);
Db.Execute("EXEC spTTSRegistrasiBG"
+ " '" + noTTS + "'"
+ ",'" + NoBG + "'"
+ ",'" + TglBG + "'"
);
Db.Execute("UPDATE MS_TTS SET "
+ " BankBG= '" + Cf.Str(bankbg.Text) + "'"
+ ",TglJTBG = '" + Convert.ToDateTime(tgljtbg.Text) + "'"
+ " WHERE NoTTS = '" + noTTS + "'"
);
}
if (anonim.SelectedIndex > 0)
{
Db.Execute("UPDATE 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);
decimal BiayaAdmin = Convert.ToDecimal(biayaadmin.Text);
int BebanBiayaAdmin = Convert.ToInt16(bebanbiayaadmin.SelectedValue);
Db.Execute("UPDATE MS_TTS SET "
+ " NoKK = '" + NoKK + "'"
+ ",BankKK = '" + BankKK + "'"
+ ",BebanBiayaAdmin = " + BebanBiayaAdmin
+ " WHERE NoTTS = '" + noTTS + "'"
);
}
if (LebihBayar > 0)
{
Db.Execute("EXEC spMEMORegistrasi"
+ " '" + TglTTS + "'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Tipe + "'"
+ ",'" + Ref + "'"
+ ",'" + Unit + "'"
+ ",'" + Customer + "'"
+ ",'PP'"
+ ",''"
+ "," + noTTS
);
}
int NoMEMO = 0;
if (Db.SingleInteger("SELECT COUNT(*) FROM MS_MEMO") > 0)
{
NoMEMO = Db.SingleInteger("SELECT TOP 1 NoMEMO FROM MS_MEMO ORDER BY NoMEMO DESC");
}
Db.Execute("UPDATE MS_MEMO SET Project = '" + Project + "',NamaProject = '" + NamaProject + "' WHERE NoMemo = " + NoMEMO);
System.Text.StringBuilder alokasiM = new System.Text.StringBuilder();
System.Text.StringBuilder alokasi = new System.Text.StringBuilder();
int b = 0;
for (int i = 0; i < rsTagihan.Rows.Count; i++)
{
b++;
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 spTTSAlokasi "
+ " " + noTTS
+ ", " + NoTagihan
+ ", " + Nilai
);
alokasi.Append(NamaTagihan + " " + Cf.Num(Nilai) + "<br>");
//Benefit
decimal benefit = Convert.ToDecimal(rsTagihan.Rows[i]["Benefit"]);
DateTime TglJT = Convert.ToDateTime(rsTagihan.Rows[i]["TglJT"]);
int beda = TglJT.Subtract(TglTTS).Days;
if (beda > 0)
{
benefit += (decimal)0.001 * Nilai * beda;
Db.Execute("UPDATE " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN SET Benefit = " + benefit
+ " WHERE NoKontrak = '" + rsTagihan.Rows[i]["NoKontrak"] + "'"
+ " AND NoUrut = " + NoTagihan
);
}
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 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 MS_MEMO SET Status='POST' WHERE NoMemo='" + NoMEMO + "'");
alokasiM.Append(NamaTagihan + " " + Cf.Num(Nilai) + "<br>");
LebihBayar -= n;
}
}
}
}
for (int a = 0; a < rsTagihan2.Rows.Count; a++)
{
TextBox lunas = (TextBox)list.FindControl("lunas_" + (a + b));
if (lunas.Text != "")
{
int NoTagihan = (int)rsTagihan2.Rows[a]["NoUrut"];
string NamaTagihan = Cf.Str(rsTagihan2.Rows[a]["NamaTagihan"])
+ " (" + rsTagihan2.Rows[a]["Tipe"] + ")";
decimal Nilai = Convert.ToDecimal(lunas.Text);
Db.Execute("EXEC spTTSAlokasi "
+ " " + noTTS
+ ", " + NoTagihan
+ ", " + Nilai
);
alokasi.Append(NamaTagihan + " " + Cf.Num(Nilai) + "<br>");
//Benefit
decimal benefit = Convert.ToDecimal(rsTagihan2.Rows[a]["Benefit"]);
DateTime TglJT = Convert.ToDateTime(rsTagihan2.Rows[a]["TglJT"]);
int beda = TglJT.Subtract(TglTTS).Days;
if (beda > 0)
{
benefit += (decimal)0.001 * Nilai * beda;
Db.Execute("UPDATE " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN SET Benefit = " + benefit
+ " WHERE NoKontrak = '" + rsTagihan2.Rows[a]["NoKontrak"] + "'"
+ " AND NoUrut = " + NoTagihan
);
}
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 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 MS_MEMO SET Status='POST' WHERE NoMemo='" + NoMEMO + "'");
alokasiM.Append(NamaTagihan + " " + Cf.Num(Nilai) + "<br>");
LebihBayar -= n;
}
}
}
}
decimal TotalSatu = Db.SingleDecimal("SELECT Total FROM MS_TTS WHERE NoTTS = '" + noTTS + "' ");
decimal TotalDua = (TotalSatu + LebihBayar + LB) - AdminBank;
Db.Execute("UPDATE 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]"
+ ",CONVERT(varchar, TglJTBG, 106) AS [Tanggal Jatuh Tempo BG]"
+ ", Acc AS [Rekening Bank]"
+ " FROM MS_TTS WHERE NoTTS = " + noTTS);
string KetLog = Cf.LogCapture(rs)
+ "<br>***ALOKASI PEMBAYARAN:<br>"
+ alokasi.ToString();
Db.Execute("EXEC spLogTTS"
+ " 'REGIS'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + KetLog + "'"
+ ",'" + noTTS.ToString().PadLeft(7, '0') + "'"
);
decimal LogID = Db.SingleDecimal("SELECT TOP 1 LogID FROM MS_TTS_LOG ORDER BY LogID DESC");
Db.Execute("UPDATE 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 MS_MEMO WHERE NoMEMO = " + NoMEMO);
string KetLogM = Cf.LogCapture(rsM)
+ "<br>***ALOKASI PEMBAYARAN:<br>"
+ alokasiM.ToString();
Db.Execute("EXEC 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 + "'");
}
Response.Redirect("TTSRegistrasi.aspx?done=" + noTTS);
}
}