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