protected void Fill()
{
string Periode = "";
if (thnKontrak.SelectedIndex != 0)
{
string[] z = thnKontrak.SelectedValue.Split(',');
Periode = " AND YEAR(a.TglKontrak) = " + z[0]
+ " AND MONTH(a.TglKontrak) = " + z[1];
}
string Lokasi = "";
if (lokasi.SelectedIndex != 0)
{
Lokasi = " AND a.Lokasi = '" + Cf.Str(lokasi.SelectedValue) + "'";
}
//cek level si user dalam approval
DataTable rs2 = Db.Rs("SELECT Lvl FROM " + Mi.DbPrefix + "SECURITY..REF_APPROVAL a WHERE Tipe = 4 AND Project = '" + project.SelectedValue + "' AND UserID = '" + Act.UserID + "'");
if (rs2.Rows.Count != 0)
{
int lvl = (rs2.Rows.Count > 0 && Convert.ToInt16(rs2.Rows[0]["Lvl"]) == 1) ? Convert.ToInt16(rs2.Rows[0]["Lvl"]) : (Convert.ToInt16(rs2.Rows[0]["Lvl"]) - 1);
//kalo dia bukan level pertama, cek di level sebelum nya udah ada yang approve atau belum
string level = Convert.ToInt16(rs2.Rows[0]["Lvl"]) > 1 ? " AND (SELECT COUNT(*) FROM MS_APPROVAL_DETAIL WHERE NoApproval = e.NoApproval AND Lvl = " + lvl + " AND TglApproval IS NOT NULL) > 0" : "";
string nav = "'<a href=KontrakApproveDiskon2.aspx?NoKontrak=''' + A.NoKontrak + '''&NoApproval=''' + e.NoApproval + '''&Level=" + Convert.ToInt16(rs2.Rows[0]["Lvl"]) + ">Next</a><br>'";
string strSql = " SELECT "
+ nav
+ " AS Nav"
+ ",e.NoApproval AS Approval"
+ ",e.SumberID AS Kontrak"
+ ",CONVERT(VARCHAR,A.TglKontrak,106) AS Tgl, b.NoUnit AS Unit, c.Nama AS Customer, d.Nama AS Agent"
+ ",(SELECT Nama FROM " + Mi.DbPrefix + "SECURITY..REF_PROJECT WHERE Project = a.Project) AS Project"
+ " FROM MS_APPROVAL e"
+ " INNER JOIN MS_KONTRAK_APPROVAL a ON e.SumberID = a.NoKontrak"
+ " 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.Status <> 'B'"
+ " AND e.Sumber = '" + Str.Approval("4") + "'"
+ " AND e.Project = '" + project.SelectedValue + "'"
+ Periode
+ Lokasi
+ " AND e.Status <> 'DONE'"
+ " AND (SELECT COUNT(*) FROM MS_APPROVAL_DETAIL WHERE NoApproval = e.NoApproval AND Lvl = " + Convert.ToInt16(rs2.Rows[0]["Lvl"]) + " AND TglApproval IS NOT NULL) = 0"
+ level
;
DataTable rs = Db.Rs(strSql);
tb.DataSource = rs;
tb.DataBind();
}
}
protected void save_Click(object sender, System.EventArgs e)
{
if (datavalid())
{
string Project = Db.SingleString("SELECT Project FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'");
string c = Db.SingleString("SELECT Value FROM " + Mi.DbPrefix + "SECURITY..REF_PARAM WHERE ParamID = 'ApprovBatal" + Project + "'");
if (c == "True")
{
int nomor = Db.SingleInteger("SELECT COUNT(*) FROM MS_APPROVAL");
nomor++;
string NoApproval = nomor.ToString().PadLeft(7, '0');
string AlasanBatal = Cf.Str(alasan.SelectedItem.Text);
// Update Manual 1
decimal NilaiKlaim = Convert.ToDecimal(nilaiklaim.Text);
decimal NilaiPengembalian = Convert.ToDecimal(totalPengembalian.Text);
decimal Biaya = Convert.ToDecimal(nilaibiaya.Text);
decimal TotalLunas = Db.SingleDecimal("SELECT ISNULL(SUM(NilaiPelunasan), 0)"
+ " FROM MS_PELUNASAN"
+ " WHERE NoKontrak = '" + NoKontrak + "'"
+ " AND SudahCair = 1"
);
string ket;
if (ketalasan.Text != "")
{
ket = ketalasan.Text;
}
else
{
ket = "";
}
DateTime TglKembali = Convert.ToDateTime(tglkembali.Text);
int count = Db.SingleInteger("SELECT COUNT (*) FROM MS_APPROVAL_BATAL WHERE NoKontrak ='" + NoKontrak + "' AND NoApproval IN (SELECT NoApproval FROM MS_APPROVAL WHERE Sumber = '" + Str.Approval("3") + "' AND Status <> 'DONE')");
if (count > 0)
{
//nostockc.Text = "Unit Tidak Valid";
Js.Alert(
this
, "NoKontrak Tidak Valid.\\n\\n"
+ "Kemungkinan Sebab :\\n"
+ "1. Kontrak sudah dijual kepada customer lain.\\n"
, "document.getElementById('nokontrakl').focus();"
+ "document.getElementById('nokontrakl').select();"
);
}
else
{
//INSERT KE MS_APPROVAL
Db.Execute("EXEC spApproval"
+ "'" + NoApproval + "'"
+ ",'" + Str.Approval("3") + "'"//untuk batal kontrak
+ ",'" + NoKontrak + "'"
+ ",'" + Convert.ToDateTime(tgl.Text) + "'"
+ ",'" + Project + "'"
);
//insert siapa aja yang berhak approve ke ms_approval_detil
DataTable rs2 = Db.Rs("SELECT * FROM " + Mi.DbPrefix + "SECURITY..REF_APPROVAL WHERE Tipe = 3 AND Project = '" + Project + "'");
for (int i = 0; i < rs2.Rows.Count; i++)
{
Db.Execute("EXEC spApprovalDetil"
+ "'" + NoApproval + "'"
+ ",'" + (i + 1) + "'"
+ ",'" + rs2.Rows[i]["UserID"].ToString() + "'"//dari Textbox
+ "," + rs2.Rows[i]["Lvl"]
+ ",'" + Db.SingleString("SELECT Nama FROM " + Mi.DbPrefix + "SECURITY..USERNAME WHERE UserID = '" + rs2.Rows[i]["UserID"].ToString() + "'") + "'"
);
}
//insert perubahan batal kontrak ke ms_approval_batal
Db.Execute("EXEC spKontrakBatalTemp"
+ "'" + NoApproval + "'"
+ ",'" + NoKontrak + "'"
+ ",'" + Convert.ToDateTime(tgl.Text) + "'"//dari Textbox
+ ",'" + Convert.ToDateTime(tglkembali.Text) + "'"
+ ",'" + AlasanBatal + "'"
+ ",'" + TotalLunas + "'"
+ ",'" + NilaiPengembalian + "'"
+ ",'" + NilaiKlaim + "'"
+ ",'" + ket + "'"
);
DataTable rs = Db.Rs("SELECT"
+ " NoKontrak AS [NoKontrak]"
+ ",NoUnit AS [No. Unit]"
+ ",(SELECT Nama FROM MS_CUSTOMER WHERE NoCustomer = MS_KONTRAK.NoCustomer) AS [Customer]"
+ " FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'"
);
string Ket = Cf.LogCapture(rs)
+ "<br>Tgl Pengembalian : " + Cf.Day(tglkembali.Text)
+ "<br>Alasan Batal : " + AlasanBatal
+ "<br>Total Pelunasan : " + TotalLunas
+ "<br>Total Pengembalian : " + NilaiPengembalian
+ "<br>Nilai Klaim : " + NilaiKlaim
;
Db.Execute("EXEC spLogKontrak "
+ " 'BATAL'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Ket + "'"
+ ",'" + NoKontrak + "'"
);
decimal LogID = Db.SingleDecimal("SELECT TOP 1 LogID FROM MS_KONTRAK_LOG ORDER BY LogID DESC");
Db.Execute("UPDATE MS_KONTRAK_LOG SET Project = '" + Project + "' WHERE LogID = " + LogID);
//Push notif ke Approval selanjutnya
string DeptID = Db.SingleString("SELECT Project FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'");
DataTable rsNextApp = Db.Rs("SELECT * FROM ISC064_SECURITY..REF_APPROVAL WHERE Tipe = 3 "
+ " AND Lvl = 1 AND Project = '" + DeptID + "'");
for (int i = 0; i < rsNextApp.Rows.Count; i++)
{
string UserIDNextApp = rsNextApp.Rows[i]["UserID"].ToString();
LibApi.PushNotif("BATAL", "Permohonan Approval Pembatalan Kontrak " + NoKontrak, UserIDNextApp, NoKontrak, 1);
}
Response.Redirect("KontrakBatal.aspx?done=" + NoKontrak + "&klaim=" + NoKontrak);
//}
}
}
else
{
Db.Execute("EXEC ISC064_MARKETINGJUAL..spKontrakBatal "
+ " '" + NoKontrak + "'"
);
if (Db.SingleString("SELECT Status FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'") == "B")
{
DataTable rs = Db.Rs("SELECT"
+ " MS_KONTRAK.NoKontrak AS [No. Kontrak]"
+ ",MS_KONTRAK.NoUnit AS [Unit]"
+ ",MS_CUSTOMER.Nama AS [Customer]"
+ ",MS_AGENT.Nama AS [Agent]"
+ ",AlasanBatal AS [Alasan Pembatalan]"
+ " FROM ISC064_MARKETINGJUAL..MS_KONTRAK AS MS_KONTRAK INNER JOIN ISC064_MARKETINGJUAL..MS_CUSTOMER AS MS_CUSTOMER"
+ " ON MS_KONTRAK.NoCustomer = MS_CUSTOMER.NoCustomer"
+ " INNER JOIN ISC064_MARKETINGJUAL..MS_AGENT AS MS_AGENT"
+ " ON MS_KONTRAK.NoAgent = MS_AGENT.NoAgent"
+ " WHERE MS_KONTRAK.NoKontrak = '" + NoKontrak + "'");
decimal NilaiBiaya = Db.SingleDecimal("SELECT BiayaBatal FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak ='" + NoKontrak + "'");
if (NilaiBiaya != 0)
{
Db.Execute("EXEC ISC064_MARKETINGJUAL..spTagihanDaftar "
+ " '" + NoKontrak + "'"
+ ",'BIAYA ADM. PEMBATALAN'"
+ ",'" + Cf.Day(DateTime.Today) + "'"
+ ", " + NilaiBiaya
+ ",'ADM'"
);
}
decimal NilaiMasuk = Db.SingleDecimal(
"SELECT ISNULL(SUM(NilaiPelunasan),0) FROM ISC064_MARKETINGJUAL..MS_PELUNASAN WHERE NoKontrak = '" + NoKontrak + "'");
Db.Execute("UPDATE ISC064_MARKETINGJUAL..MS_KONTRAK SET BatalMasuk = "
+ NilaiMasuk + " WHERE NoKontrak = '" + NoKontrak + "'");
decimal NilaiKlaim = Db.SingleDecimal("SELECT NilaiKlaim FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak ='" + NoKontrak + "'");
decimal NilaiPengembalian = Db.SingleDecimal("SELECT NilaiPulang FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak ='" + NoKontrak + "'");
decimal TotalLunas = Db.SingleDecimal("SELECT TotalLunasBatal FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak ='" + NoKontrak + "'");
string acc = Db.SingleString("SELECT AccBatal FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak ='" + NoKontrak + "'");
string Ket = Cf.LogCapture(rs)
+ "<br>Biaya Administrasi : " + Cf.Num(NilaiBiaya)
+ "<br>Uang Masuk : " + Cf.Num(NilaiMasuk)
+ "<br>Nilai Klaim : " + Cf.Num(NilaiKlaim)
+ "<br>Tgl. Batal : " + Cf.Day(DateTime.Today)
+ "<br>Total Pelunasan : " + Cf.Num(TotalLunas)
+ "<br>Nilai Kembali : " + Cf.Num(NilaiPengembalian)
+ "<br>Rekening Pembatalan : " + acc
;
Func.CekKomisi(NoKontrak);
//floor plan
string Peta = Db.SingleString("SELECT Peta "
+ " FROM ISC064_MARKETINGJUAL..MS_UNIT AS MS_UNIT INNER JOIN ISC064_MARKETINGJUAL..MS_KONTRAK AS MS_KONTRAK ON MS_UNIT.NoStock = MS_KONTRAK.NoStock "
+ " WHERE NoKontrak = '" + NoKontrak + "'");
Func.GenerateFP(Peta);
//SA01
string CnnEsales = "Data Source=.;Initial Catalog=SA01;Persist Security Info=True;User ID=batavianet;Password=iNDigo100";
string ClosingID = Db.SingleString("SELECT ISNULL(ClosingID,'') FROM ISC064_MARKETINGJUAL..MS_Kontrak WHERE NoKontrak='" + NoKontrak + "'");
string AlasanBatal = Db.SingleString("SELECT ISNULL(AlasanBatal,'') FROM ISC064_MARKETINGJUAL..MS_Kontrak WHERE NoKontrak='" + NoKontrak + "'");
if (ClosingID != "")
{
Execute("UPDATE SalesClosing SET"
+ " Status=1"
+ ", AlasanCancel='" + AlasanBatal + "'"
+ ", TglCancel='" + Cf.Day(DateTime.Today) + "'"
+ " WHERE ClosingID='" + ClosingID + "'"
, CnnEsales);
StringBuilder x = new StringBuilder();
x.Append("Closing dengan kode " + ClosingID);
x.Append("<br/>");
x.Append("<i>Dibatalkan setelah kontrak terdaftar</i>");
string Closinger = "";
Closinger = SingleString("SELECT ISNULL(UserID,'') FROM SalesClosing WHERE ClosingID='" + ClosingID + "'", CnnEsales);
if (Closinger != "")
{
string Role = "";
Role = SingleString("SELECT ISNULL(RoleID,'') FROM SecUser WHERE UserID='" + Closinger + "'", CnnEsales);
if (Role == "SA.OPR")
{
Execute("EXEC InsertSecNotification "
+ "'Pembatalan Kontrak'"
+ ",'" + x.ToString() + "'"
+ ",'" + Closinger + "'"
+ ",'../Sales/ClosingFile.aspx?id=" + ClosingID + "'"
, CnnEsales);
}
else if (Role == "SA.MGR")
{
Execute("EXEC InsertSecNotification "
+ "'Pembatalan Kontrak'"
+ ",'" + x.ToString() + "'"
+ ",'" + Closinger + "'"
+ ",'../SM/ClosingFile.aspx?id=" + ClosingID + "'"
, CnnEsales);
}
}
}
Db.Execute("EXEC spLogKontrak "
+ " 'BATAL'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",''"
+ ",'" + NoKontrak + "'"
);
decimal LogID = Db.SingleDecimal("SELECT TOP 1 LogID FROM MS_KONTRAK_LOG ORDER BY LogID DESC");
Db.Execute("UPDATE MS_KONTRAK_LOG SET Project = '" + Project + "' WHERE LogID = " + LogID);
//Push notif ke Approval selanjutnya
DataTable rsNextApp = Db.Rs("SELECT * FROM ISC064_SECURITY..REF_APPROVAL WHERE Tipe = 3 "
+ " AND Lvl = 1");
for (int i = 0; i < rsNextApp.Rows.Count; i++)
{
string UserIDNextApp = rsNextApp.Rows[i]["UserID"].ToString();
LibApi.PushNotif("BATAL", "Permohonan Approval Pembatalan Kontrak " + NoKontrak, UserIDNextApp, NoKontrak, 1);
}
Response.Redirect("KontrakBatal.aspx?done=" + NoKontrak + "&klaim=" + NoKontrak);
}
}
}
}
protected void save_Click(object sender, System.EventArgs e)
{
if (datavalid())
{
string Project = Db.SingleString("SELECT Project FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'");
string c = Db.SingleString("SELECT Value FROM " + Mi.DbPrefix + "SECURITY..REF_PARAM WHERE ParamID = 'ApprovGantiNama" + Project + "'");
string Keterangan = baru.Text;
decimal NilaiBiaya = Convert.ToDecimal(nilaibiaya.Text);
int nomor = Db.SingleInteger("SELECT COUNT(*) FROM MS_APPROVAL");
nomor++;
string NoApproval = nomor.ToString().PadLeft(7, '0');
if (c == "True")
{
if (filevalid())
{
DataTable rsBef = Db.Rs("SELECT "
+ " MS_CUSTOMER.NoCustomer AS [No. Customer]"
+ ",MS_CUSTOMER.Nama AS [Nama Customer]"
+ " FROM MS_KONTRAK INNER JOIN MS_CUSTOMER "
+ " ON MS_KONTRAK.NoCustomer = MS_CUSTOMER.NoCustomer"
+ " WHERE MS_KONTRAK.NoKontrak = '" + NoKontrak + "'");
//INSERT KE MS_APPROVAL
Db.Execute("EXEC spApproval"
+ "'" + NoApproval + "'"
+ ",'" + Str.Approval("1") + "'"//untuk ganti unit
+ ",'" + NoKontrak + "'"
+ ",'" + Convert.ToDateTime(tglgn.Text) + "'"
+ ",'" + Project + "'"
);
//insert siapa aja yang berhak approve ke ms_approval_detil
DataTable rs2 = Db.Rs("SELECT * FROM " + Mi.DbPrefix + "SECURITY..REF_APPROVAL WHERE Tipe = 1 AND Project = '" + Project + "'");
for (int i = 0; i < rs2.Rows.Count; i++)
{
Db.Execute("EXEC spApprovalDetil"
+ "'" + NoApproval + "'"
+ ",'" + (i + 1) + "'"
+ ",'" + rs2.Rows[i]["UserID"].ToString() + "'"//dari Textbox
+ "," + rs2.Rows[i]["Lvl"]
+ ",'" + Db.SingleString("SELECT Nama FROM " + Mi.DbPrefix + "SECURITY..USERNAME WHERE UserID = '" + rs2.Rows[i]["UserID"].ToString() + "'") + "'"
);
}
//insert ke ms_approval ganti nama
Db.Execute("EXEC spKontrakGantiNamaTemp"
+ "'" + NoApproval + "'"
+ ",'" + NoKontrak + "'"
+ ",'" + NoCustomer + "'"
+ ",'" + NilaiBiaya + "'"
+ ",'" + Convert.ToDateTime(tglgn.Text) + "'"
+ ",'" + Keterangan + "'"
);
DataTable rsAft = Db.Rs("SELECT "
+ " MS_CUSTOMER.NoCustomer AS [No. Customer]"
+ ",MS_CUSTOMER.Nama AS [Nama Customer]"
+ " FROM MS_KONTRAK INNER JOIN MS_CUSTOMER "
+ " ON MS_KONTRAK.TempGN = MS_CUSTOMER.NoCustomer"
+ " WHERE MS_KONTRAK.NoKontrak = '" + NoKontrak + "'");
#region Default
//Db.Execute("EXEC spKontrakGantiNama "
// + " '" + NoKontrak + "'"
// + ", '" + NoCustomer + "'"
// + ", '" + Convert.ToDateTime(tglgn.Text) + "'"
// );
//decimal NilaiBiaya = Convert.ToDecimal(nilaibiaya.Text);
//if(NilaiBiaya!=0)
//{
// Db.Execute("EXEC spTagihanDaftar "
// + " '" + NoKontrak + "'"
// + ",'BIAYA ADM. Pengalihan Hak'"
// + ",'" + Cf.Day(DateTime.Today) + "'"
// + ", " + NilaiBiaya
// + ",'ADM'"
// );
//}
///*Pengalihan Hak customer di MS_TTS*/
//string strNamaCs = Cf.Str(Db.SingleString("SELECT Nama FROM MS_CUSTOMER WHERE NoCustomer = " + NoCustomer));
//string strSql = "UPDATE " + Mi.DbPrefix + "FINANCEAR..MS_TTS"
// + " SET Customer = '" + strNamaCs + "'"
// + " WHERE Ref = '" + NoKontrak + "'"
// + " AND Tipe = 'JUAL'"
// ;
//Db.Execute(strSql);
///*******************************/
///*Pengalihan Hak customer di MS_PJT*/
//strSql = "UPDATE " + Mi.DbPrefix + "FINANCEAR..MS_PJT"
// + " SET Customer = '" + strNamaCs + "'"
// + " WHERE Ref = '" + NoKontrak + "'"
// + " AND Tipe = 'JUAL'"
// ;
//Db.Execute(strSql);
///*******************************/
///*Pengalihan Hak customer di MS_TUNGGAKAN*/
//strSql = "UPDATE " + Mi.DbPrefix + "FINANCEAR..MS_TUNGGAKAN"
// + " SET Customer = '" + strNamaCs + "'"
// + " WHERE Ref = '" + NoKontrak + "'"
// + " AND Tipe = 'JUAL'"
// ;
//Db.Execute(strSql);
///*******************************/
#endregion
/*Insert jurnal kontrak*/
string strKetJurnal = "KONTRAK Pengalihan Hak<br />" + Cf.Str(baru.Text) + "<br>No. Approval :" + NoApproval;
Db.Execute("EXEC spJurnalKontrak "
+ " '" + Act.UserID + "'"
+ ",'" + NoKontrak + "'"
+ ",'" + strKetJurnal + "'"
);
if (file.PostedFile.FileName.Length != 0)
{
long JurnalID = Db.SingleLong("SELECT TOP 1 JurnalID FROM MS_KONTRAK_JURNAL ORDER BY JurnalID DESC");
string path = Request.PhysicalApplicationPath
+ "JurnalKontrak\\" + JurnalID + ".jpg";
Dfc.UploadFile(".jpg", path, file);
}
/***********************/
string Ket = Cf.LogCompare(rsBef, rsAft)
+ "<br>Biaya Administrasi : " + Cf.Num(NilaiBiaya)
+ "<br>Tgl Pengalihan Hak : " + Cf.Day(Convert.ToDateTime(tglgn.Text))
;
Db.Execute("EXEC spLogKontrak "
+ " 'GN'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Ket + "'"
+ ",'" + NoKontrak + "'"
);
decimal LogID = Db.SingleDecimal("SELECT TOP 1 LogID FROM MS_KONTRAK_LOG ORDER BY LogID DESC");
Db.Execute("UPDATE MS_KONTRAK_LOG SET Project = '" + Project + "' WHERE LogID = " + LogID);
Func.CekKomisi(NoKontrak);
//Push notif ke Approval selanjutnya
string DeptID = Db.SingleString("SELECT Project FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'");
DataTable rsNextApp = Db.Rs("SELECT * FROM ISC064_SECURITY..REF_APPROVAL WHERE Tipe = 1 "
+ " AND Lvl = 1 AND Project = '" + DeptID + "'");
for (int i = 0; i < rsNextApp.Rows.Count; i++)
{
string UserIDNextApp = rsNextApp.Rows[i]["UserID"].ToString();
LibApi.PushNotif("APR-GN", "Permohonan Approval Pengalihan Hak " + NoKontrak, UserIDNextApp, NoKontrak, 1);
}
Response.Redirect("KontrakGantiNama.aspx?done=" + NoKontrak);
}
}
else
{
//buat yang ga pake fitur approv
if (filevalid())
{
DataTable rsBef = Db.Rs("SELECT "
+ " MS_CUSTOMER.NoCustomer AS [No. Customer]"
+ ",MS_CUSTOMER.Nama AS [Nama Customer]"
+ " FROM ISC064_MARKETINGJUAL..MS_KONTRAK AS MS_KONTRAK INNER JOIN ISC064_MARKETINGJUAL..MS_CUSTOMER AS MS_CUSTOMER"
+ " ON MS_KONTRAK.NoCustomer = MS_CUSTOMER.NoCustomer"
+ " WHERE MS_KONTRAK.NoKontrak = '" + NoKontrak + "'");
//INSERT KE MS_APPROVAL tapi langsung done
Db.Execute("INSERT INTO MS_APPROVAL VALUES ("
+ "'" + NoApproval + "'"
+ ",'" + Str.Approval("1") + "'"//untuk ganti unit
+ ",'" + NoKontrak + "'"
+ ",'" + Convert.ToDateTime(tglgn.Text) + "'"
+ ",'DONE'"
+ ",'" + Convert.ToDateTime(tglgn.Text) + "'"
+ ",'" + Project + "')"
);
//insert ke ms_approval ganti nama
Db.Execute("EXEC spKontrakGantiNamaTemp"
+ "'" + NoApproval + "'"
+ ",'" + NoKontrak + "'"
+ ",'" + NoCustomer + "'"
+ ",'" + NilaiBiaya + "'"
+ ",'" + Convert.ToDateTime(tglgn.Text) + "'"
+ ",'" + Keterangan + "'"
);
Db.Execute("EXEC ISC064_MARKETINGJUAL..spKontrakGantiNama "
+ " '" + NoKontrak + "'"
+ ", '" + NoCustomer + "'"
);
/*Update Flag ApprovalGN*/
Db.Execute("UPDATE ISC064_MARKETINGJUAL..MS_KONTRAK "
+ " SET ApprovalGN = 0"
+ " ,Revisi = Revisi + 1"
+ " WHERE NoKontrak='" + NoKontrak + "'"
);
DataTable rsAft = Db.Rs("SELECT "
+ " MS_CUSTOMER.NoCustomer AS [No. Customer]"
+ ",MS_CUSTOMER.Nama AS [Nama Customer]"
+ " FROM ISC064_MARKETINGJUAL..MS_KONTRAK AS MS_KONTRAK INNER JOIN ISC064_MARKETINGJUAL..MS_CUSTOMER AS MS_CUSTOMER "
+ " ON MS_KONTRAK.NoCustomer = MS_CUSTOMER.NoCustomer"
+ " WHERE MS_KONTRAK.NoKontrak = '" + NoKontrak + "'");
if (NilaiBiaya != 0)
{
Db.Execute("EXEC " + Mi.DbPrefix + "MARKETINGJUAL..spTagihanDaftar "
+ " '" + NoKontrak + "'"
+ ",'BIAYA ADM. Pengalihan Hak'"
+ ",'" + Cf.Day(DateTime.Today) + "'"
+ ", " + NilaiBiaya
+ ",'ADM'"
);
int NoUrut = Db.SingleInteger("SELECT TOP 1 NoUrut FROM MS_TAGIHAN WHERE NoKontrak = '" + NoKontrak + "' ORDER BY NoUrut DESC");
Db.Execute("UPDATE " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN SET Jenis = 'Pengalihan Hak' WHERE NoKontrak = '" + NoKontrak + "' AND NoUrut = " + NoUrut);
}
/* Ambil Nilai PPH Pengalihan Hak */
decimal NilaiPPH = Convert.ToDecimal(nilaipph.Text);
if (NilaiPPH != 0)
{
Db.Execute("EXEC ISC064_MARKETINGJUAL..spTagihanDaftar "
+ " '" + NoKontrak + "'"
+ ",'BIAYA PPH PENGALIHAN HAK'"
+ ",'" + Cf.Day(DateTime.Today) + "'"
+ ", " + NilaiPPH
+ ",'ADM'"
);
}
/*Pengalihan Hak customer di MS_TTS*/
string strNamaCs = Cf.Str(Db.SingleString("SELECT Nama FROM ISC064_MARKETINGJUAL..MS_CUSTOMER WHERE NoCustomer = " + NoCustomer));
string strSql = "UPDATE " + Mi.DbPrefix + "FINANCEAR..MS_TTS"
+ " SET Customer = '" + strNamaCs + "'"
+ " WHERE Ref = '" + NoKontrak + "'"
+ " AND Tipe = 'JUAL'"
;
Db.Execute(strSql);
/*******************************/
/*Pengalihan Hak customer di MS_MEMO*/
strSql = "UPDATE " + Mi.DbPrefix + "FINANCEAR..MS_MEMO"
+ " SET Customer = '" + strNamaCs + "'"
+ " WHERE Ref = '" + NoKontrak + "'"
+ " AND Tipe = 'JUAL'"
;
Db.Execute(strSql);
/*******************************/
/*Pengalihan Hak customer di MS_PJT*/
strSql = "UPDATE " + Mi.DbPrefix + "FINANCEAR..MS_PJT"
+ " SET Customer = '" + strNamaCs + "'"
+ " WHERE Ref = '" + NoKontrak + "'"
+ " AND Tipe = 'JUAL'"
;
Db.Execute(strSql);
/*******************************/
/*Pengalihan Hak customer di MS_TUNGGAKAN*/
strSql = "UPDATE " + Mi.DbPrefix + "FINANCEAR..MS_TUNGGAKAN"
+ " SET Customer = '" + strNamaCs + "'"
+ " WHERE Ref = '" + NoKontrak + "'"
+ " AND Tipe = 'JUAL'"
;
Db.Execute(strSql);
/*******************************/
string Ket = Cf.LogCompare(rsBef, rsAft)
+ "<br>Biaya Administrasi : " + Cf.Num(NilaiBiaya)
+ "<br>Tgl Pengalihan Hak : " + Cf.Day(Convert.ToDateTime(tglgn.Text))
;
Db.Execute("EXEC spLogKontrak "
+ " 'GN'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Ket + "'"
+ ",'" + NoKontrak + "'"
);
decimal LogID = Db.SingleDecimal("SELECT TOP 1 LogID FROM MS_KONTRAK_LOG ORDER BY LogID DESC");
Db.Execute("UPDATE MS_KONTRAK_LOG SET Project = '" + Project + "' WHERE LogID = " + LogID);
Func.CekKomisi(NoKontrak);
//Push notif ke Approval selanjutnya
DataTable rsNextApp = Db.Rs("SELECT * FROM ISC064_SECURITY..REF_APPROVAL WHERE Tipe = 1 "
+ " AND Lvl = 1");
for (int i = 0; i < rsNextApp.Rows.Count; i++)
{
string UserIDNextApp = rsNextApp.Rows[i]["UserID"].ToString();
LibApi.PushNotif("GN", "Permohonan Pengalihan Hak " + NoKontrak, UserIDNextApp, NoKontrak, 1);
}
Response.Redirect("KontrakGantiNama.aspx?done=" + NoKontrak);
}
}
}
}
protected void next_Click(object sender, System.EventArgs e)
{
if (valid())
{
int count = Db.SingleInteger("SELECT COUNT(*) FROM MS_APPROVAL WHERE SumberID = '" + NoKontrak + "' AND Sumber = '" + Str.Approval("1") + "' AND Status <> 'DONE'");
if (count == 0)
{
pilih.Visible = false;
frm.Visible = true;
Js.Focus(this, nocustomer);
nocustomer.Attributes["ondblclick"] = "popDaftarCustomer('a');";
Fill();
Js.Confirm(this, "Jalankan prosedur Pengalihan Hak?\\nProses ini akan merubah data kepemilikan unit properti.");
}
else
{
pilih.Visible = true;
feed1.Text = "<img src='/Media/db.gif' align=absmiddle> "
+ "Proses Pengalihan Hak untuk Kontrak tersebut belum selesai.";
feed1.Attributes["style"] = "background-color:white;color:red;";
}
}
}
protected void insert_Click(object sender, System.EventArgs e)
{
string Project = Db.SingleString("SELECT Project FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'");
string c = Db.SingleString("SELECT Value FROM " + Mi.DbPrefix + "SECURITY..REF_PARAM WHERE ParamID = 'ApprovCustomTagihan" + Project + "'");
int nomor = Db.SingleInteger("SELECT COUNT(*) FROM MS_APPROVAL");
nomor++;
string NoApproval = nomor.ToString().PadLeft(7, '0');
string Ket = "";
if (c == "True")
{
for (int i = 1; i < rpt.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
string Tipe = rpt.Rows[i].Cells[1].Text;
string NamaTagihan = Cf.Str(rpt.Rows[i].Cells[2].Text);
DateTime TglJT = Convert.ToDateTime(rpt.Rows[i].Cells[3].Text);
decimal NilaiTagihan = Convert.ToDecimal(rpt.Rows[i].Cells[4].Text);
Db.Execute("EXEC spTagihanDaftarTEMP"
+ " '" + NoKontrak + "'"
+ ",'" + NamaTagihan + "'"
+ ",'" + TglJT + "'"
+ ", " + NilaiTagihan
+ ",'" + Tipe + "'"
);
}
//INSERT KE MS_APPROVAL
Db.Execute("EXEC spApproval"
+ "'" + NoApproval + "'"
+ ",'" + Str.Approval("7") + "'"//untuk customize
+ ",'" + NoKontrak + "'"
+ ",'" + Convert.ToDateTime(tgl.Text) + "'"
+ ",'" + Project + "'"
);
//insert siapa aja yang berhak approve ke ms_approval_detil
DataTable rs2 = Db.Rs("SELECT * FROM " + Mi.DbPrefix + "SECURITY..REF_APPROVAL WHERE Tipe = 7 AND Project = '" + Project + "'");
for (int i = 0; i < rs2.Rows.Count; i++)
{
Db.Execute("EXEC spApprovalDetil"
+ "'" + NoApproval + "'"
+ ",'" + (i + 1) + "'"
+ ",'" + rs2.Rows[i]["UserID"].ToString() + "'"//dari Textbox
+ "," + rs2.Rows[i]["Lvl"]
+ ",'" + Db.SingleString("SELECT Nama FROM " + Mi.DbPrefix + "SECURITY..USERNAME WHERE UserID = '" + rs2.Rows[i]["UserID"].ToString() + "'") + "'"
);
}
Db.Execute("EXEC spKontrakCustomTemp "
+ " '" + NoApproval + "'"
+ ",'" + NoKontrak + "'"
+ ",'" + Db.SingleString("SELECT Skema FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'") + "'"
+ ",'" + skema.Text + "'"
+ ",'" + Db.SingleString("SELECT CaraBayar FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'") + "'"
+ ",'" + carabayar2.SelectedValue + "'"
+ ",'" + Convert.ToDateTime(tgl.Text) + "'"
);
if (carabayar2.SelectedValue == "KPA")
{
int NoUrutMax = Db.SingleInteger("SELECT MAX(NoUrut) FROM MS_TAGIHAN_TEMP WHERE NoKontrak = '" + NoKontrak + "' ");
Db.Execute("UPDATE MS_TAGIHAN_TEMP SET KPR = " + 1 + " WHERE NoKontrak = '" + NoKontrak + "' AND NoUrut = '" + (NoUrutMax) + "'");
}
DataTable rsDetail = Db.Rs("SELECT"
+ " MS_KONTRAK.NoKontrak AS [No. Kontrak]"
+ ",MS_KONTRAK.NoUnit AS [Unit]"
+ ",MS_CUSTOMER.Nama AS [Customer]"
+ ",MS_KONTRAK.NilaiKontrak AS [Nilai Kontrak]"
+ ",MS_KONTRAK.Skema AS [Skema]"
+ " FROM MS_KONTRAK INNER JOIN MS_CUSTOMER"
+ " ON MS_KONTRAK.NoCustomer = MS_CUSTOMER.NoCustomer"
+ " WHERE MS_KONTRAK.NoKontrak = '" + NoKontrak + "'");
DataTable rsAft = Db.Rs("SELECT "
+ "CONVERT(VARCHAR,NoUrut) + '. ' + NamaTagihan + ' ('+Tipe+') ' + CONVERT(VARCHAR,TglJT,106) + ' ' + CONVERT(VARCHAR,NilaiTagihan,1) "
+ "FROM MS_TAGIHAN_TEMP WHERE NoKontrak = '" + NoKontrak + "' ORDER BY NoUrut");
Ket = Cf.LogCapture(rsDetail)
+ Cf.LogList(rsAft, "JADWAL TAGIHAN");
}
else
{
for (int i = 1; i < rpt.Rows.Count; i++)
{
if (!Response.IsClientConnected)
{
break;
}
string Tipe = rpt.Rows[i].Cells[1].Text;
string NamaTagihan = Cf.Str(rpt.Rows[i].Cells[2].Text);
DateTime TglJT = Convert.ToDateTime(rpt.Rows[i].Cells[3].Text);
decimal NilaiTagihan = Convert.ToDecimal(rpt.Rows[i].Cells[4].Text);
Db.Execute("EXEC spTagihanDaftar"
+ " '" + NoKontrak + "'"
+ ",'" + NamaTagihan + "'"
+ ",'" + TglJT + "'"
+ ", " + NilaiTagihan
+ ",'" + Tipe + "'"
);
}
DataTable rs2 = Db.Rs("SELECT * FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'");
if (rs2.Rows.Count > 0)
{
Db.Execute("UPDATE MS_KONTRAK "
+ " SET Skema = '" + skema.Text + "'"
+ ", CaraBayar = '" + carabayar2.SelectedValue + "'"
+ ", ApprovalCustomTagihan = 0"
+ " WHERE NoKontrak = '" + NoKontrak + "'"
);
}
if (carabayar2.SelectedValue == "KPR")
{
int NoUrutMax = Db.SingleInteger("SELECT MAX(NoUrut) FROM MS_TAGIHAN WHERE NoKontrak = '" + NoKontrak + "' ");
Db.Execute("UPDATE MS_TAGIHAN SET KPR = " + 1 + " WHERE NoKontrak = '" + NoKontrak + "' AND NoUrut = '" + (NoUrutMax) + "'");
}
decimal NilaiKontrak = Db.SingleDecimal("SELECT NilaiKontrak FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak ='" + NoKontrak + "'");
//string skema = Db.SingleString("SELECT Skema FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak ='" + NoKontrak + "'");
string carabayar = Db.SingleString("SELECT CaraBayar FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak ='" + NoKontrak + "'");
DataTable rsAft = Db.Rs("SELECT "
+ "CONVERT(VARCHAR,NoUrut) + '. ' + NamaTagihan + ' ('+Tipe+') ' + CONVERT(VARCHAR,TglJT,106) + ' ' + CONVERT(VARCHAR,NilaiTagihan,1) "
+ "FROM MS_TAGIHAN_TEMP WHERE NoKontrak = '" + NoKontrak + "' ORDER BY NoUrut");
Ket = Cf.LogCapture(rs2)
+ "<br>Nilai Kontrak : " + Cf.Num(NilaiKontrak)
+ "<br>Tgl. Batal : " + Cf.Day(DateTime.Today)
+ "<br>Skema : " + skema
+ "<br>Cara Bayar : " + carabayar
+ Cf.LogList(rsAft, "JADWAL TAGIHAN")
;
}
Db.Execute("EXEC spLogKontrak"
+ " 'CUSTOM'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Ket + "'"
+ ",'" + NoKontrak + "'"
);
decimal LogID = Db.SingleDecimal("SELECT TOP 1 LogID FROM MS_KONTRAK_LOG ORDER BY LogID DESC");
Db.Execute("UPDATE MS_KONTRAK_LOG SET Project = '" + Project + "' WHERE LogID = " + LogID);
Func.CekKomisi(NoKontrak);
if (alokasi.Checked)
{
if (dariDaftar.Checked)
{
Response.Redirect("Alokasi.aspx?custom=1&dd=1&NoKontrak=" + NoKontrak);
}
else
{
Response.Redirect("Alokasi.aspx?custom=1&NoKontrak=" + NoKontrak);
}
}
else
{
if (dariDaftar.Checked)
{
Response.Redirect("KontrakDaftar3.aspx?NoKontrak=" + NoKontrak + "&done=1");
}
else
{
Response.Redirect("TagihanCustom.aspx?done=" + NoKontrak);
}
}
}
protected void next_Click(object sender, System.EventArgs e)
{
if (valid())
{
int count = Db.SingleInteger("SELECT COUNT(*) FROM MS_APPROVAL WHERE SumberID = '" + NoKontrak + "' AND Sumber = '" + Str.Approval("7") + "' AND Status <> 'DONE'");
if (count == 0)
{
pilih.Visible = false;
frm.Visible = true;
//Bind();
Fill();
}
else
{
pilih.Visible = true;
feed1.Text = "<img src='/Media/db.gif' align=absmiddle> "
+ "Proses Customize tagihan untuk Kontrak tersebut belum selesai.";
feed1.Attributes["style"] = "background-color:white;color:red;";
}
}
}
protected void save_Click(object sender, System.EventArgs e)
{
if (datavalid())
{
string c = Db.SingleString("SELECT Value FROM " + Mi.DbPrefix + "SECURITY..REF_PARAM WHERE ParamID = 'ApprovGantiUnit" + Project + "'");
decimal NilaiBiaya = Convert.ToDecimal(nilaibiaya.Text);
int nomor = Db.SingleInteger("SELECT COUNT(*) FROM MS_APPROVAL");
nomor++;
string NoApproval = nomor.ToString().PadLeft(7, '0');
if (c == "True")
{
if (filevalid())
{
DataTable rsBef = Db.Rs("SELECT "
+ " NoStock AS [No. Stock]"
+ ",NoUnit AS [Unit]"
+ ",Luas AS [Luas]"
+ ",Gross AS [Nilai Gross]"
+ " FROM MS_KONTRAK"
+ " WHERE NoKontrak = '" + NoKontrak + "'");
string NoStockOld = Db.SingleString(
"SELECT NoStock FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'");
//Update TempGU, TempBiayaGU
int count = Db.SingleInteger("SELECT COUNT (*) FROM MS_APPROVAL_GU WHERE UnitBaru ='" + NoStock + "' AND NoApproval IN (SELECT NoApproval FROM MS_APPROVAL WHERE Sumber = '" + Str.Approval("2") + "' AND Status <> 'DONE')");
if (count > 0)
{
nostockc.Text = "Unit Tidak Valid";
Js.Alert(
this
, "Unit Tidak Valid.\\n\\n"
+ "Kemungkinan Sebab :\\n"
+ "1. Unit sudah dijual kepada customer lain.\\n"
, "document.getElementById('nostock').focus();"
+ "document.getElementById('nostock').select();"
);
}
else
{
//INSERT KE MS_APPROVAL
Db.Execute("EXEC spApproval"
+ "'" + NoApproval + "'"
+ ",'" + Str.Approval("2") + "'"//untuk ganti unit
+ ",'" + NoKontrak + "'"
+ ",'" + Convert.ToDateTime(tglgu.Text) + "'"
+ ",'" + Project + "'"
);
//insert siapa aja yang berhak approve ke ms_approval_detil
DataTable rs2 = Db.Rs("SELECT * FROM " + Mi.DbPrefix + "SECURITY..REF_APPROVAL WHERE Tipe = 2 AND Project = '" + Project + "'");
for (int i = 0; i < rs2.Rows.Count; i++)
{
Db.Execute("EXEC spApprovalDetil"
+ "'" + NoApproval + "'"
+ ",'" + (i + 1) + "'"
+ ",'" + rs2.Rows[i]["UserID"].ToString() + "'"//dari Textbox
+ "," + rs2.Rows[i]["Lvl"]
+ ",'" + Db.SingleString("SELECT Nama FROM " + Mi.DbPrefix + "SECURITY..USERNAME WHERE UserID = '" + rs2.Rows[i]["UserID"].ToString() + "'") + "'"
);
}
//insert perubahan unit nya ke ms_approval_gu
Db.Execute("EXEC spKontrakGantiUnitTemp"
+ "'" + NoApproval + "'"
+ ",'" + NoKontrak + "'"
+ ",'" + NoStock + "'"//dari Textbox
+ ",'" + NilaiBiaya + "'"
+ ",'" + Convert.ToDateTime(tglgu.Text) + "'"
+ ",'" + Cf.Str(baru.Text) + "'"
);
string NoStockNew = Db.SingleString(
"SELECT UnitBaru FROM MS_APPROVAL_GU WHERE NoApproval = '" + NoApproval + "'");
if (NoStockNew == NoStockOld)
{
nostockc.Text = "Unit Tidak Valid";
Js.Alert(
this
, "Unit Tidak Valid.\\n\\n"
+ "Kemungkinan Sebab :\\n"
+ "1. Unit sudah dijual kepada customer lain.\\n"
, "document.getElementById('nostock').focus();"
+ "document.getElementById('nostock').select();"
);
}
else
{
//Data Unit Baru dari MS_UNIT
DataTable rsAft = Db.Rs("SELECT "
+ " NoStock AS [No. Stock]"
+ ",NoUnit AS [Unit]"
+ ",Luas AS [Luas]"
+ ",PriceList AS [Nilai Gross]"
+ " FROM MS_UNIT"
+ " WHERE NoStock = '" + NoStockNew + "'");
/*Insert jurnal kontrak*/
string strKetJurnal = "KONTRAK Pindah Unit<br />" + Cf.Str(baru.Text);
Db.Execute("EXEC spJurnalKontrak "
+ " '" + Act.UserID + "'"
+ ",'" + NoKontrak + "'"
+ ",'" + strKetJurnal + "'"
);
if (file.PostedFile.FileName.Length != 0)
{
long JurnalID = Db.SingleLong("SELECT TOP 1 JurnalID FROM MS_KONTRAK_JURNAL ORDER BY JurnalID DESC");
string path = Request.PhysicalApplicationPath
+ "JurnalKontrak\\" + JurnalID + ".jpg";
Dfc.UploadFile(".jpg", path, file);
}
/***********************/
string Ket = Cf.LogCompare(rsBef, rsAft)
+ "<br>Biaya Administrasi : " + Cf.Num(NilaiBiaya)
+ "<br>Tgl Pindah Unit : " + Cf.Day(tglgu.Text)
;
Db.Execute("EXEC spLogKontrak "
+ " 'GU'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Ket + "'"
+ ",'" + NoKontrak + "'"
);
decimal LogID = Db.SingleDecimal("SELECT TOP 1 LogID FROM MS_KONTRAK_LOG ORDER BY LogID DESC");
Db.Execute("UPDATE MS_KONTRAK_LOG SET Project = '" + Project + "' WHERE LogID = " + LogID);
Func.CekKomisi(NoKontrak);
//Push notif ke Approval selanjutnya
string DeptID = Db.SingleString("SELECT Project FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'");
DataTable rsNextApp = Db.Rs("SELECT * FROM ISC064_SECURITY..REF_APPROVAL WHERE Tipe = 2 "
+ " AND Lvl = 1 AND Project = '" + DeptID + "'");
for (int i = 0; i < rsNextApp.Rows.Count; i++)
{
string UserIDNextApp = rsNextApp.Rows[i]["UserID"].ToString();
LibApi.PushNotif("APR-GU", "Permohonan Approval Pindah Unit " + NoKontrak, UserIDNextApp, NoKontrak, 1);
}
Response.Redirect("KontrakGantiUnit.aspx?done=" + NoKontrak);
}
}
}
}
else
{
if (filevalid())
{
DataTable rsBef = Db.Rs("SELECT "
+ " NoStock AS [No. Stock]"
+ ",NoUnit AS [Unit]"
+ ",Luas AS [Luas]"
+ ",Gross AS [Nilai Gross]"
+ ",NilaiKontrak AS [Nilai Kontrak]"
+ ",DiskonRupiah AS [Diskon dalam Rupiah]"
+ ",DiskonPersen AS [Diskon dalam Persen]"
+ ",NilaiPPN AS [Nilai PPN]"
+ " FROM ISC064_MARKETINGJUAL..MS_KONTRAK"
+ " WHERE NoKontrak = '" + NoKontrak + "'");
string NoStockOld = Db.SingleString(
"SELECT NoStock FROM ISC064_MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'");
decimal biaya = Convert.ToDecimal(nilaibiaya.Text);
//INSERT KE MS_APPROVAL
Db.Execute("INSERT INTO MS_APPROVAL VALUES ("
+ "'" + NoApproval + "'"
+ ",'" + Str.Approval("2") + "'"//untuk ganti unit
+ ",'" + NoKontrak + "'"
+ ",'" + Convert.ToDateTime(tglgu.Text) + "'"
+ ",'DONE'"
+ ",'" + Convert.ToDateTime(tglgu.Text) + "'"
+ ",'" + Project + "')"
);
//insert perubahan unit nya ke ms_approval_gu
Db.Execute("EXEC spKontrakGantiUnitTemp"
+ "'" + NoApproval + "'"
+ ",'" + NoKontrak + "'"
+ ",'" + NoStock + "'"//dari Textbox
+ ",'" + NilaiBiaya + "'"
+ ",'" + Convert.ToDateTime(tglgu.Text) + "'"
+ ",'" + Cf.Str(baru.Text) + "'"
);
//GANTI UNIT NYA
Db.Execute("EXEC ISC064_MARKETINGJUAL..spKontrakGantiUnit "
+ " '" + NoKontrak + "'"
+ ",'" + NoStock + "'"
// + ",'" + Tgl + "'"
);
string NoUnitBaru = Db.SingleString("SELECT NoUnit FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_UNIT WHERE NoStock = '" + NoStock + "'");
//update nounit di kontrak
Db.Execute("UPDATE " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK SET NoUnit = '" + NoUnitBaru + "' WHERE NoKontrak = '" + NoKontrak + "'");
//Insert tagihan
if (biaya != 0)
{
Db.Execute("EXEC ISC064_MARKETINGJUAL..spTagihanDaftar "
+ " '" + NoKontrak + "'"
+ ",'BIAYA ADM. Pindah Unit'"
+ ",'" + Cf.Day(DateTime.Today) + "'"
+ ", " + biaya
+ ",'ADM'"
);
int NoUrut = Db.SingleInteger("SELECT TOP 1 NoUrut FROM MS_TAGIHAN WHERE NoKontrak = '" + NoKontrak + "' ORDER BY NoUrut DESC");
Db.Execute("UPDATE " + Mi.DbPrefix + "MARKETINGJUAL..MS_TAGIHAN SET Jenis = 'Pindah Unit' WHERE NoKontrak = '" + NoKontrak + "' AND NoUrut = " + NoUrut);
}
//UPDATE Nilai PPN , Nilai Kontrak Terbaru, PPNPemerintah, ApprovalGU
decimal GrossBaru = Db.SingleDecimal("SELECT Pricelist FROM MS_UNIT WHERE NoUnit = '" + NoUnitBaru + "'");
decimal DiskonRupiah = Db.SingleDecimal("SELECT DiskonRupiah FROM MS_KONTRAK WHERE NoKOntrak = '" + NoKontrak + "'");
decimal DiskonTambahan = Db.SingleDecimal("SELECT DiskonTambahan FROM MS_KONTRAK WHERE NoKOntrak = '" + NoKontrak + "'");
decimal BungaRupiah = Db.SingleDecimal("SELECT BungaNominal FROM MS_KONTRAK WHERE NoKOntrak = '" + NoKontrak + "'");
string ParamID = "PLIncludePPN" + Project;
decimal DPP = 0, NilaiPPN = 0, NilaiKontrak = 0;
bool includeppn = Db.SingleString("SELECT Value FROM " + Mi.DbPrefix + "SECURITY..REF_PARAM WHERE ParamID = '" + ParamID + "'") == "True";
bool jenisppn = Db.SingleBool("SELECT PPN FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK WHERE NoKontrak='" + NoKontrak + "'");
decimal Netto = GrossBaru + BungaRupiah - DiskonRupiah - DiskonTambahan;
if (jenisppn)
{
if (includeppn)
{
DPP = Math.Round(Netto / (decimal)1.1);
NilaiPPN = Netto - DPP;
}
else
{
DPP = Netto;
NilaiPPN = (DPP * (decimal)0.1);
}
}
else
{
DPP = Netto;
}
Db.Execute("UPDATE " + Mi.DbPrefix + "MARKETINGJUAL..MS_KONTRAK "
+ " SET NilaiPPN='" + NilaiPPN + "'"
+ ", NilaiKontrak='" + NilaiKontrak + "'"
+ ", NilaiDPP='" + DPP + "'"
+ ", Gross='" + GrossBaru + "'"
+ ", ApprovalGU = '" + Convert.ToBoolean(0) + "'"
+ ", Revisi = Revisi + 1"
+ " WHERE NoKontrak='" + NoKontrak + "'"
);
decimal pl = Db.SingleDecimal("SELECT ISNULL(PriceList, 0) FROM MS_UNIT WHERE NoStock = '" + NoStock + "'");
//string RumusDiskon = Db.SingleString("SELECT Diskon FROM REF_SKEMA WHERE Nomor = " + crbyt.SelectedValue);
DataTable rsAft = Db.Rs("SELECT "
+ " NoStock AS [No. Stock]"
+ ",NoUnit AS [Unit]"
+ ",Luas AS [Luas]"
+ ",Gross AS [Nilai Gross]"
+ ",NilaiKontrak AS [Nilai Kontrak]"
+ ",DiskonRupiah AS [Diskon dalam Rupiah]"
+ ",DiskonPersen AS [Diskon dalam Persen]"
+ ",NilaiPPN AS [Nilai PPN]"
+ " FROM ISC064_MARKETINGJUAL..MS_KONTRAK"
+ " WHERE NoKontrak = '" + NoKontrak + "'");
/*Ganti nomor unit di MS_TTS*/
string strNoUnit = Cf.Str(Db.SingleString("SELECT NoUnit FROM " + Mi.DbPrefix + "MARKETINGJUAL..MS_UNIT WHERE NoStock = '" + NoStock + "'"));
string strSql = "UPDATE " + Mi.DbPrefix + "FINANCEAR..MS_TTS"
+ " SET Unit = '" + strNoUnit + "'"
+ " WHERE Ref = '" + NoKontrak + "'"
+ " AND Tipe = 'JUAL'"
;
Db.Execute(strSql);
/*******************************/
/*Ganti nomor unit di MS_MEMO*/
strSql = "UPDATE " + Mi.DbPrefix + "FINANCEAR..MS_MEMO"
+ " SET Unit = '" + strNoUnit + "'"
+ " WHERE Ref = '" + NoKontrak + "'"
+ " AND Tipe = 'JUAL'"
;
Db.Execute(strSql);
/*******************************/
/*Ganti nomor unit di MS_PJT*/
strSql = "UPDATE " + Mi.DbPrefix + "FINANCEAR..MS_PJT"
+ " SET Unit = '" + strNoUnit + "'"
+ " WHERE Ref = '" + NoKontrak + "'"
+ " AND Tipe = 'JUAL'"
;
Db.Execute(strSql);
/*******************************/
/*Ganti nomor unit di MS_TUNGGAKAN*/
strSql = "UPDATE " + Mi.DbPrefix + "FINANCEAR..MS_TUNGGAKAN"
+ " SET Unit = '" + strNoUnit + "'"
+ " WHERE Ref = '" + NoKontrak + "'"
+ " AND Tipe = 'JUAL'"
;
Db.Execute(strSql);
/*******************************/
string Ket = Cf.LogCompare(rsBef, rsAft)
+ "<br>Biaya Administrasi : " + Cf.Num(NilaiBiaya)
+ "<br>Tgl Pindah Unit : " + Cf.Day(tglgu.Text)
;
/*Insert jurnal kontrak*/
string strKetJurnal = "KONTRAK Pindah Unit<br />" + Cf.Str(baru.Text);
Db.Execute("EXEC spJurnalKontrak "
+ " '" + Act.UserID + "'"
+ ",'" + NoKontrak + "'"
+ ",'" + strKetJurnal + "'"
);
if (file.PostedFile.FileName.Length != 0)
{
long JurnalID = Db.SingleLong("SELECT TOP 1 JurnalID FROM MS_KONTRAK_JURNAL ORDER BY JurnalID DESC");
string path = Request.PhysicalApplicationPath
+ "JurnalKontrak\\" + JurnalID + ".jpg";
Dfc.UploadFile(".jpg", path, file);
}
/***********************/
Db.Execute("EXEC spLogKontrak "
+ " 'GU'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Ket + "'"
+ ",'" + NoKontrak + "'"
);
decimal LogID = Db.SingleDecimal("SELECT TOP 1 LogID FROM MS_KONTRAK_LOG ORDER BY LogID DESC");
Db.Execute("UPDATE MS_KONTRAK_LOG SET Project = '" + Project + "' WHERE LogID = " + LogID);
Func.CekKomisi(NoKontrak);
//Push notif ke Approval selanjutnya
DataTable rsNextApp = Db.Rs("SELECT * FROM ISC064_SECURITY..REF_APPROVAL WHERE Tipe = 2 "
+ " AND Lvl = 1");
for (int i = 0; i < rsNextApp.Rows.Count; i++)
{
string UserIDNextApp = rsNextApp.Rows[i]["UserID"].ToString();
LibApi.PushNotif("APR-GU", "Permohonan Approval Pindah Unit " + NoKontrak, UserIDNextApp, NoKontrak, 1);
}
Response.Redirect("KontrakGantiUnit.aspx?done=" + NoKontrak);
}
}
}
}
protected void save_Click(object sender, System.EventArgs e)
{
if (datavalid())
{
int nomor = Db.SingleInteger("SELECT COUNT(*) FROM MS_APPROVAL");
nomor++;
string NoApproval = nomor.ToString().PadLeft(7, '0');
string Project = Db.SingleString("SELECT Project FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'");
DataTable rsBef = Db.Rs("SELECT "
+ " DiskonRupiah AS [Diskon dalam Rupiah]"
+ ", NilaiPPN AS [PPN]"
+ ", BungaNominal AS [BungaNominal]"
+ ",NilaiKontrak AS [Nilai Kontrak]"
+ ",Project AS [Project]"
+ ",NamaProject AS [Nama Project]"
+ ",Pers AS [Perusahaan]"
+ ",NamaPers AS [Nama Perusahaan]"
+ " FROM MS_KONTRAK"
+ " WHERE NoKontrak = '" + NoKontrak + "'"
);
decimal Gross = Convert.ToDecimal(gross.Text);
decimal DiskonSkema = Convert.ToDecimal(discSkema.Text);
decimal DiskonTambahan = Convert.ToDecimal(disc.Text);
decimal bng = Convert.ToDecimal(bunga.Text);
decimal PPN = 0;
decimal Netto = Gross + bng;
decimal NilaiKontrak = 0;
string statusPPN = sifatppn.SelectedValue;
string valueSkema = skema.Text;
string NamaProject = Db.SingleString("SELECT Nama FROM " + Mi.DbPrefix + "SECURITY..REF_PROJECT WHERE Project = '" + project.SelectedValue + "'");
string Pers = Db.SingleString("SELECT Pers FROM " + Mi.DbPrefix + "SECURITY..REF_PROJECT WHERE Project = '" + project.SelectedValue + "'");
string NamaPers = Db.SingleString("SELECT Nama FROM " + Mi.DbPrefix + "SECURITY..REF_PERS WHERE Pers = '" + Pers + "'");
/* DISKON SKEMA CARA BAYAR */
//string RumusDiskon = "";//Convert.ToString(DiskonSkema);
//string RumusDiskon2 = "diskon";
//Netto = Func.SetelahDiskon(RumusDiskon, Gross);
Netto -= DiskonSkema;
/***************************/
/* DISKON TAMBAHAN SAAT CLOSING */
//Db.Execute("UPDATE MS_KONTRAK SET"
// + " DiskonTambahan = " + DiskonTambahan
// + " WHERE NoKontrak = '" + NoKontrak + "'");
Netto -= DiskonTambahan;
/***************************/
string ParamID = "PLIncludePPN" + project.SelectedValue;
bool includeppn = Db.SingleString("SELECT Value FROM " + Mi.DbPrefix + "SECURITY..REF_PARAM WHERE ParamID = '" + ParamID + "'") == "True";
decimal NilaiPPN = 0;
decimal DPP = 0;
if (statusPPN == "1")
{
if (includeppn)
{
DPP = Math.Round(Netto / (decimal)1.1);
NilaiPPN = Math.Round(Netto - DPP);
}
else
{
DPP = Math.Round(Netto / (decimal)1.1);
NilaiPPN = Math.Round(DPP * (decimal)0.1);
}
}
else
{
DPP = Netto;
}
NilaiKontrak = DPP + NilaiPPN;
PPN = Math.Round(NilaiKontrak - DPP);
string c = Db.SingleString("SELECT Value FROM " + Mi.DbPrefix + "SECURITY..REF_PARAM WHERE ParamID = 'ApprovAdjustment" + Project + "'");
if (c == "True")
{
//INSERT KE MS_APPROVAL
Db.Execute("EXEC spApproval"
+ "'" + NoApproval + "'"
+ ",'" + Str.Approval("5") + "'"//untuk adjusment
+ ",'" + NoKontrak + "'"
+ ",'" + DateTime.Today + "'"
+ ",'" + Project + "'"
);
//insert siapa aja yang berhak approve ke ms_approval_detil
DataTable rs2 = Db.Rs("SELECT * FROM " + Mi.DbPrefix + "SECURITY..REF_APPROVAL WHERE Tipe = 5 AND Project = '" + Project + "'");
for (int i = 0; i < rs2.Rows.Count; i++)
{
Db.Execute("EXEC spApprovalDetil"
+ "'" + NoApproval + "'"
+ ",'" + (i + 1) + "'"
+ ",'" + rs2.Rows[i]["UserID"].ToString() + "'"//dari Textbox
+ "," + rs2.Rows[i]["Lvl"]
+ ",'" + Db.SingleString("SELECT Nama FROM " + Mi.DbPrefix + "SECURITY..USERNAME WHERE UserID = '" + rs2.Rows[i]["UserID"].ToString() + "'") + "'"
);
}
//simpan before afternya ke ms_approval_adjusment
Db.Execute("EXEC spKontrakADJTemp"
+ " '" + NoApproval + "'"
+ ",'" + NoKontrak + "'"
+ "," + Gross
+ "," + DPP
+ ",'" + statusPPN + "'"
+ "," + PPN
+ ",'" + valueSkema + "'"
+ "," + bng
+ "," + DiskonSkema
+ "," + DiskonTambahan
+ "," + NilaiKontrak
+ ",'" + DateTime.Today + "'"
);
Func.CekKomisi(NoKontrak);
}
else
{
//INSERT KE MS_APPROVAL
Db.Execute("INSERT INTO MS_APPROVAL VALUES ("
+ "'" + NoApproval + "'"
+ ",'" + Str.Approval("5") + "'"//untuk adjusment
+ ",'" + NoKontrak + "'"
+ ",'" + DateTime.Today + "'"
+ ",'DONE'"
+ ",'" + DateTime.Today + "'"
+ ",'" + Project + "')"
);
//simpan before afternya ke ms_approval_adjusment
Db.Execute("EXEC spKontrakADJTemp"
+ " '" + NoApproval + "'"
+ ",'" + NoKontrak + "'"
+ "," + Gross
+ "," + DPP
+ ",'" + statusPPN + "'"
+ "," + PPN
+ ",'" + valueSkema + "'"
+ "," + bng
+ "," + DiskonSkema
+ "," + DiskonTambahan
+ "," + NilaiKontrak
+ ",'" + DateTime.Today + "'"
);
string strSql = "SELECT * FROM MS_KONTRAK WHERE NoKontrak = '" + NoKontrak + "'";
DataTable rs3 = Db.Rs(strSql);
if (rs3.Rows.Count != 0)
{
Db.Execute("UPDATE MS_KONTRAK "
+ " SET"
+ " NilaiKontrak = '" + NilaiKontrak + "'"
+ ", Gross = '" + Gross + "'"
+ ", DiskonRupiah = '" + DiskonSkema + "'"
+ ", DiskonTambahan = '" + DiskonTambahan + "'"
+ ", BungaNominal = '" + bng + "'"
+ ", NilaiDPP = '" + DPP + "'"
+ ", NilaiPPN = '" + PPN + "'"
+ ", PPN = '" + statusPPN + "'"
+ ", Skema = '" + valueSkema + "'"
+ ", FlagADJ = 0 "
+ " WHERE NoKontrak = '" + NoKontrak + "'");
Db.Execute("EXEC spKontrakDiskon"
+ " '" + NoKontrak + "'"
+ ",'" + Gross + "'"
+ ",'" + NilaiKontrak + "'"
+ ",'" + DiskonSkema + "'"
+ ",'" + rs3.Rows[0]["DiskonPersen"] + "'"
+ ",'" + rs3.Rows[0]["DiskonKet"] + "'"
);
}
}
DataTable rsAft = Db.Rs("SELECT "
+ " DiskonRupiah AS [Diskon dalam Rupiah]"
+ ", NilaiPPN AS [PPN]"
+ ", BungaNominal AS [BungaNominal]"
+ ",NilaiKontrak AS [Nilai Kontrak]"
+ ",Project AS [Project]"
+ ",NamaProject AS [Nama Project]"
+ ",Pers AS [Perusahaan]"
+ ",NamaPers AS [Nama Perusahaan]"
+ " FROM MS_KONTRAK"
+ " WHERE NoKontrak = '" + NoKontrak + "'"
);
DataTable rs = Db.Rs("SELECT "
+ " MS_KONTRAK.NoKontrak AS [No. Kontrak]"
+ ",MS_KONTRAK.NoUnit AS [Unit]"
+ ",MS_CUSTOMER.Nama AS [Customer]"
+ ",MS_KONTRAK.Gross AS [Nilai Gross]"
+ " FROM MS_KONTRAK INNER JOIN MS_CUSTOMER"
+ " ON MS_KONTRAK.NoCustomer = MS_CUSTOMER.NoCustomer"
+ " WHERE MS_KONTRAK.NoKontrak = '" + NoKontrak + "'");
string Ket = Cf.LogCapture(rs)
+ Cf.LogCompare(rsBef, rsAft)
;
Db.Execute("EXEC spLogKontrak"
+ " 'ADJ'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Ket + "'"
+ ",'" + NoKontrak + "'"
);
decimal LogID = Db.SingleDecimal("SELECT TOP 1 LogID FROM MS_KONTRAK_LOG ORDER BY LogID DESC");
Db.Execute("UPDATE MS_KONTRAK_LOG SET Project = '" + Project + "' WHERE LogID = " + LogID);
Response.Redirect("AdjustmentKontrak.aspx?done=" + NoKontrak);
}
}
protected void next_Click(object sender, System.EventArgs e)
{
if (valid())
{
int count = Db.SingleInteger("SELECT COUNT(*) FROM MS_APPROVAL WHERE SumberID = '" + NoKontrak + "' AND Sumber = '" + Str.Approval("5") + "' AND Status <> 'DONE'");
if (count == 0)
{
pilih.Visible = false;
frm.Visible = true;
InitForm();
Fill();
Js.Focus(this, disc);
}
else
{
pilih.Visible = true;
feed.Text = "<img src='/Media/db.gif' align=absmiddle> "
+ "Proses Adjustment untuk Kontrak tersebut belum selesai.";
feed.Attributes["style"] = "background-color:white;color:red;";
}
// if(frm.Visible) Js.Confirm(this, "Lanjutkan proses diskon nilai kontrak?");
}
}
