private string SaveTTS(string NoNUP, string cabar, string rekpers, string ketbayar, DateTime TglTransfer, int NoCustomer, decimal nilai, string Jenis)
{
int roman = Convert.ToInt32(Cf.Bulan(DateTime.Now));
DateTime TglTTS = TglTransfer;
string Customer = Cf.Str(Db.SingleString("SELECT Nama FROM MS_CUSTOMER WHERE NoCustomer = " + NoCustomer));
string CaraBayar = cabar;
Db.Execute("EXEC ISC064_FINANCEAR..spTTSRegistrasi"
+ " '" + TglTTS + "'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'JUAL'"
+ ",''" //Ref / NoKontrak
+ ",''" //Unit
+ ",'" + Customer + "'"
+ ",'" + CaraBayar + "'"
+ ",'PEMBAYARAN PLP'"
);
int NoTTS = Db.SingleInteger("SELECT TOP 1 NoTTS FROM ISC064_FINANCEAR..MS_TTS ORDER BY NoTTS DESC");
//di hard code untuk projectnya
string NoTTS2 = Numerator.TTS(TglTTS.Month, TglTTS.Year, "SVS");
//update nilai bayar di TTSnya
decimal nBayar = nilai;
Db.Execute("UPDATE ISC064_FINANCEAR..MS_TTS SET Total=" + nBayar
+ ", Acc = '" + rekpers + "'"
+ ", NoNUP = '" + NoNUP + "'"
+ ", Jenis = '" + Jenis + "'"
+ ", NoTTS2 = '" + NoTTS2 + "'"
//+ ", NoKK = '" + Cf.Str(noKK) + "'"
+ ", Catatan = '" + Cf.Str(ketbayar) + "'"
+ " 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]"
+ " FROM ISC064_FINANCEAR..MS_TTS WHERE NoTTS = " + NoTTS);
string KetLog =
"***PEMBAYARAN NUP PERTAMA: " + NoNUP + " Tipe = " + Jenis + "<br/><br/>"
+ Cf.LogCapture(rs);
Db.Execute("EXEC ISC064_FINANCEAR..spLogTTS"
+ " 'REGIS'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + KetLog + "'"
+ ",'" + NoTTS.ToString().PadLeft(7, '0') + "'"
);
return(NoTTS.ToString());
}
private bool Save(DataTable rs, int i)
{
string NoNUP = Cf.Str(rs.Rows[i][0].ToString().PadLeft(4, '0'));
string SumberData = Cf.Str(rs.Rows[i][1]);
string TipeCs = Cf.Str(rs.Rows[i][2]);
string NamaAgent = Cf.Str(rs.Rows[i][3]);
string Nama = Cf.Str(rs.Rows[i][4]);
string NoTelp = Cf.Str(rs.Rows[i][5]);
string NoHp = Cf.Str(rs.Rows[i][6]);
string Email = Cf.Str(rs.Rows[i][7]);
string NoKTP = Cf.Str(rs.Rows[i][8]);
string KTP1 = Cf.Str(rs.Rows[i][9]);
string KTP2 = Cf.Str(rs.Rows[i][10]);
string KTP3 = Cf.Str(rs.Rows[i][11]);
string KTP4 = Cf.Str(rs.Rows[i][12]);
string Alamat1 = Cf.Str(rs.Rows[i][13]);
string Alamat2 = Cf.Str(rs.Rows[i][14]);
string Alamat3 = Cf.Str(rs.Rows[i][15]);
string Alamat4 = Cf.Str(rs.Rows[i][16]);
string rekB = Cf.Str(rs.Rows[i][17]);
string rekC = Cf.Str(rs.Rows[i][18]);
string rekN = Cf.Str(rs.Rows[i][19]);
string rekNam = Cf.Str(rs.Rows[i][20]);
string NPWP = Cf.Str(rs.Rows[i][21]);
DateTime TglTransferNUP = Convert.ToDateTime(rs.Rows[i][22]);
string RekeningnUP = Cf.Str(rs.Rows[i][23]);
string CaraBayarNUP = Cf.Str(rs.Rows[i][24]);
decimal NilaiNUP = Convert.ToDecimal(rs.Rows[i][25]);
string JenisProperti = Cf.Str(rs.Rows[i][26]);
DateTime TglLahir = Convert.ToDateTime(rs.Rows[i][27]);
//Jika data NA maka diisi dengan string kosong saja di dalam database
//Check Jika NoNUP Ada Maka Tidak Akan di Upload Data Customernya
int AdaNUP = Db.SingleInteger("Select Count(*) From MS_NUP WHERE NoNup = " + NoNUP + " AND TIPE = '" + JenisProperti + "'");
if (AdaNUP == 0)
{
if (NoTelp == "0")
{
NoTelp = "";
}
if (NoHp == "0")
{
NoHp = "";
}
if (Email == "NA")
{
Email = "";
}
if (NoKTP == "NA")
{
NoKTP = "";
}
if (KTP1 == "NA")
{
KTP1 = "";
}
if (KTP2 == "NA")
{
KTP2 = "";
}
if (KTP3 == "NA")
{
KTP3 = "";
}
if (KTP4 == "NA")
{
KTP4 = "";
}
if (Alamat1 == "NA")
{
Alamat1 = "";
}
if (Alamat2 == "NA")
{
Alamat2 = "";
}
if (Alamat3 == "NA")
{
Alamat3 = "";
}
if (Alamat4 == "NA")
{
Alamat4 = "";
}
if (SumberData == "NA")
{
SumberData = "LAINNYA";
}
int NoAgent = Db.SingleInteger(
"SELECT TOP 1 NoAgent FROM MS_AGENT WHERE Nama = '" + NamaAgent + "'");
int NoCustomer = AutoID();
Db.Execute("EXEC spCustomerDaftarNUPUpload"
+ " '" + NoNUP + "'"
+ ", '" + NoCustomer + "'"
+ "," + NoAgent
+ ",'" + Nama + "'"
+ ",'" + NoHp + "'"
+ ",'" + NoTelp + "'"
+ ",'" + Email + "'"
+ ",'" + TglLahir + "'"
+ ",'" + NoKTP + "'"
+ ",'" + NPWP + "'"
+ ",'" + KTP1 + "'"
+ ",'" + KTP2 + "'"
+ ",'" + KTP3 + "'"
+ ",'" + KTP4 + "'"
+ ",'" + Alamat1 + "'"
+ ",'" + Alamat2 + "'"
+ ",'" + Alamat3 + "'"
+ ",'" + Alamat4 + "'"
+ ",'" + rekNam + "'"
+ ",'" + rekB + "'"
+ ",'" + rekC + "'"
+ ",'" + rekN + "'"
+ ",'" + JenisProperti + "'"
);
DateTime TglDaftar = DateTime.Now.Date;
Db.Execute("UPDATE MS_NUP SET "
+ " TglDaftar = '" + TglDaftar + "'"
+ ", NoCustomerBfr = '" + NoCustomer + "'"
+ ", NamaBfr = '" + Nama + "'"
+ ", UserInputID = '" + Act.UserID + "'"
+ ", Tipe = '" + JenisProperti + "'"
+ ", UserInputNama = '" + Db.SingleString("SELECT Nama FROM ISC064_SECURITY..USERNAME WHERE UserID = '" + Act.UserID + "'") + "'"
+ " WHERE NoNUP='" + NoNUP + "' AND Tipe = '" + JenisProperti + "'");
//Logfile
DataTable rsLog = Db.Rs("SELECT "
+ " MS_NUP.NoNUP AS [NUP]"
+ ",MS_CUSTOMER.Nama AS [Customer]"
+ ",(select top 1 Nama from ms_agent where MS_AGENT.NoAgent = MS_NUP.NoAgent) AS [Nama Agent]"
//+ ",MS_CUSTOMER.Nama AS [Customer]"
+ ",MS_NUP.UserInputNama AS [Diinput Oleh]"
+ " FROM MS_NUP INNER JOIN MS_CUSTOMER"
+ " ON MS_NUP.NoCustomer = MS_CUSTOMER.NoCustomer"
+ " INNER JOIN MS_AGENT ON MS_NUP.NoAgent = MS_AGENT.NoAgent"
+ " WHERE MS_NUP.NoNUP = '" + NoNUP + "' AND MS_NUP.Tipe = '" + JenisProperti + "'");
Db.Execute("EXEC spLogNUP"
+ " 'REGIS'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Cf.LogCapture(rsLog) + "'"
+ ",'" + NoNUP + "'"
+ ",'" + JenisProperti + "'"
);
DataTable log = Db.Rs("SELECT "
+ " NoCustomer AS [No. Customer]"
+ ",TipeCs AS [Tipe]"
+ ",Nama AS [Nama Lengkap]"
+ ",NamaBisnis AS [Nama Bisnis]"
+ ",JenisBisnis AS [Jenis Bisnis]"
+ ",MerekBisnis AS [Merek Bisnis]"
+ ",Agama AS [Agama]"
+ ",CONVERT(varchar, TglLahir, 106) AS [Tanggal Lahir]"
+ ",NoTelp AS [No. Telepon]"
+ ",NoHp AS [No. HP]"
+ ",NoKantor AS [No. Telepon Kantor]"
+ ",NoFax AS [No. Fax]"
+ ",Email AS [Alamat Email]"
+ ",Alamat1 AS [Alamat Surat Menyurat 1]"
+ ",Alamat2 AS [Alamat Surat Menyurat 2]"
+ ",Alamat3 AS [Alamat Surat Menyurat 3]"
+ ",Alamat4 AS [Alamat Surat Menyurat 4]"
+ ",Kantor1 AS [Alamat Kantor 1]"
+ ",Kantor2 AS [Alamat Kantor 2]"
+ ",Kantor3 AS [Alamat Kantor 3]"
+ ",Kantor4 AS [Alamat Kantor 4]"
+ ",NoKTP AS [No. KTP]"
+ ",KTP1 AS [KTP Alamat]"
+ ",KTP2 AS [KTP RT/RW]"
+ ",KTP3 AS [KTP Kecamatan]"
+ ",KTP4 AS [KTP Kotamadya]"
+ ",NoNUP AS [No.NUP]"
+ ",NilaiNUP AS [Nilai NUP]"
+ " FROM MS_CUSTOMER"
+ " WHERE NoCustomer = " + NoCustomer
);
Db.Execute("EXEC spLogCustomer"
+ " 'DAFTAR'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + Cf.LogCapture(log) + "'"
+ ",'" + NoCustomer.ToString().PadLeft(5, '0') + "'"
);
string cabar = Cf.Str(CaraBayarNUP);
string rekpers = Cf.Str(RekeningnUP);
string ketbayar = Cf.Str("PEMBAYARAN NUP PERTAMA");
decimal totalnup = (decimal)0;
decimal valuenup = 0;
valuenup = Convert.ToDecimal(NilaiNUP);
string NoTTS = "";
Db.Execute("EXEC spInsertNUPPelunasan"
+ " '" + NoNUP + "'"
+ ",'" + Cf.Tgl112(DateTime.Now) + "'"
+ "," + valuenup
+ ",'" + cabar + "'"
+ ",'" + ketbayar + "'"
+ ",'" + NoTTS.ToString() + "'"
+ "," + 1
+ ",'" + rekpers + "'"
+ ",'" + JenisProperti + "'"
); //Yang belakang diset 1, karena ini adalah pembayaran pertama NUP
//kode no tts
NoTTS = SaveTTS(NoNUP, cabar, rekpers, ketbayar, TglTransferNUP, Db.SingleInteger("SELECT NoCustomer FROM MS_NUP WHERE NoNUP='" + NoNUP + "' AND Tipe = '" + JenisProperti + "'"), NilaiNUP, JenisProperti);
int roman = Convert.ToInt32(Cf.Bulan(DateTime.Now));
string TTS = NoTTS.ToString();
Db.Execute("UPDATE MS_NUP_PELUNASAN SET "
+ " UserInputID = '" + Act.UserID + "'"
+ ", UserInputNama = '" + Db.SingleString("SELECT Nama FROM ISC064_SECURITY..USERNAME WHERE UserID = '" + Act.UserID + "'") + "'"
+ ", NoTTSNUP = '" + TTS + "'"
+ ", NoTTS = '" + TTS + "'"
+ " WHERE NoNUP='" + NoNUP + "' AND Tipe = '" + JenisProperti + "'");
if (JenisProperti == "RUSUNAMI")
{
totalnup = 2000000;
}
else
{
totalnup = 6000000;
}
if (valuenup == totalnup)
{
Db.Execute("UPDATE MS_NUP_PELUNASAN SET FlagUntukBayar=1 WHERE NoNUP='" + NoNUP + "' AND Tipe = '" + JenisProperti + "'");
}
Db.Execute("UPDATE MS_NUP SET NilaiBayar = '" + valuenup + "' WHERE NoNUP='" + NoNUP + "' AND Tipe = '" + JenisProperti + "'");
//Logfile
rsLog = Db.Rs("SELECT "
+ " A.NoNUP AS [NUP]"
+ ",C.Nama AS [Customer]"
+ ",B.CaraBayar AS [Cara Bayar]"
+ ",E.Bank+'/'+E.Rekening+'/'+E.AtasNama AS [Transfer ke]"
+ ",B.NilaiBayar AS [Nilai Bayar]"
+ ",B.Keterangan AS [Keterangan]"
+ ",B.UserInputNama AS [Diinput Oleh]"
+ " FROM MS_NUP A "
+ " INNER JOIN MS_NUP_PELUNASAN B ON A.NoNUP = B.NoNUP"
+ " INNER JOIN MS_CUSTOMER C ON A.NoCustomer = C.NoCustomer"
+ " INNER JOIN ISC064_FINANCEAR..REF_ACC E ON B.RekBank = E.Acc"
+ " WHERE A.NoNUP = '" + NoNUP + "' AND A.Tipe = '" + JenisProperti + "'");
string KetLog = Cf.LogCapture(rsLog)
+ "<br>***PEMBAYARAN NUP PERTAMA:<br>";
Db.Execute("EXEC spLogNUP"
+ " 'PY-NUP'"
+ ",'" + Act.UserID + "'"
+ ",'" + Act.IP + "'"
+ ",'" + KetLog + "'"
+ ",'" + NoNUP + "'"
+ ",'" + JenisProperti + "'"
);
Berhasil++;
}
else
{
Gagals++;
BarisGagal += "Baris Ke-" + (i + 1).ToString() + ", ";
}
return(true);
}
