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?"); } }