public static System.Data.DataTable RetrieveUserTransactionByDateTransaction(string nop, DateTime tglTransaksi)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT username, tanggal, pajak_terutang, keterangan, is_adjusment, createdate, ip_sender,nop, file_adjustment
FROM user_transaction WHERE nop =:nop AND tanggal = :tgl";
cmd.AddParameter("tgl", OracleCmdParameterDirection.Input, tglTransaksi);
cmd.AddParameter("nop", OracleCmdParameterDirection.Input, nop);
return(cmd.GetTable());
}
public static bool DeleteUserDetailTransaction(string nop, DateTime tglTransaksi)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"DELETE FROM user_transaction_detail WHERE nop=:nop AND transaction_date=:tgl";
cmd.AddParameter("nop", OracleCmdParameterDirection.Input, nop);
cmd.AddParameter("tgl", OracleCmdParameterDirection.Input, tglTransaksi);
return(cmd.ExecuteNonQuery() > 0);
}
public static DataTable RetrieveColumnByUserNop(string username, string nop)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT USERNAME, NOP, COLUMN_NAME, COLUMN_TEXT
FROM USER_SETTING_COLUMN WHERE USERNAME=:usern AND NOP=:nop ";
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
cmd.AddParameter("nop", OracleCmdParameterDirection.Input, nop);
return(cmd.GetTable());
}
public static DataTable RetrieveUserClient(string username, string email)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT username, id_machine, password,web_username, serial_key, email, phone,
status_aktif,port_client
FROM USER_CLIENT WHERE USERNAME=:usern AND EMAIL = :email";
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
cmd.AddParameter("email", OracleCmdParameterDirection.Input, email);
return(cmd.GetTable());
}
public DataTable RetrieveUser(string uName, string pass)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT ID_ROLE, USERNAME, PASSWORD, CREATE_DATE,IS_ACTIVE,IS_DELETED, USER_APP
FROM M_USER
WHERE UPPER(USERNAME)=:uName AND PASSWORD=:pass AND IS_ACTIVE = 1 AND IS_DELETED = 0";
cmd.AddParameter("uName", OracleCmdParameterDirection.Input, uName.ToUpper());
cmd.AddParameter("pass", OracleCmdParameterDirection.Input, pass.ToUpper());
return(cmd.GetTable());
}
public static System.Data.DataTable RetrieveUserTransactionByNop(string nop, DateTime tglTransaction)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT username, tanggal, pajak_terutang, keterangan, is_adjusment, createdate, ip_sender, nop, file_adjustment
FROM user_transaction WHERE nop=:nop AND trunc(tanggal)=:dateTransaction and is_generate=1";
cmd.AddParameter("nop", OracleCmdParameterDirection.Input, nop);
cmd.AddParameter("dateTransaction", OracleCmdParameterDirection.Input, tglTransaction.ToString("dd MMM yyyy", new CultureInfo("en-US")));
return(cmd.GetTable());
}
public static bool InsertUserNop(string username, string nop, string jenisPajak)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"INSERT INTO USER_NOP (USERNAME, NOP, JENIS_PAJAK)
VALUES(:usern, :nop, :jnsPajak)";
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
cmd.AddParameter("nop", OracleCmdParameterDirection.Input, nop);
cmd.AddParameter("jnsPajak", OracleCmdParameterDirection.Input, jenisPajak);
return(cmd.ExecuteNonQuery() > 0);
}
public static bool isUserNopExist(string username, string nop)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT count(*) FROM USER_NOP WHERE username=:usern AND nop=:nop AND IS_DELETED = 0";
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
cmd.AddParameter("nop", OracleCmdParameterDirection.Input, nop);
return(Convert.ToInt32(cmd.ExecuteScalar()) > 0);
}
public static bool UpdateSerialKey(string username, string serialKey)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"UPDATE user_client SET serial_key=:ser
WHERE username=:usern";
cmd.AddParameter("ser", OracleCmdParameterDirection.Input, serialKey);
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
return(cmd.ExecuteNonQuery() > 0);
}
public bool InsertUser(string username, string idRole, string password, string userapp)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"INSERT INTO M_USER (USERNAME, ID_ROLE, PASSWORD, USER_APP)
VALUES(:usern, :idrole, :passw, :userapp)";
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
cmd.AddParameter("idrole", OracleCmdParameterDirection.Input, idRole);
cmd.AddParameter("passw", OracleCmdParameterDirection.Input, password);
cmd.AddParameter("userapp", OracleCmdParameterDirection.Input, userapp);
return(cmd.ExecuteNonQuery() > 0);
}
public bool ChangePassword(string username, string oldPassword, string newPassword)
{
string encNewPassword = POAdministrationTools.StringCipher.Encrypt(newPassword, HttpContext.Current.Session["Serial_key"].ToString());
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"UPDATE M_USER SET password =:password WHERE Username =:uName AND Password =:oldPass";
cmd.AddParameter("password", OracleCmdParameterDirection.Input, encNewPassword);
cmd.AddParameter("uName", OracleCmdParameterDirection.Input, username);
cmd.AddParameter("oldPass", OracleCmdParameterDirection.Input, oldPassword);
return(cmd.ExecuteNonQuery() > 0);
}
public static System.Data.DataTable RetrieveUserTransactionByMonth(string username, int monthTransaction, int year)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT username, tanggal, pajak_terutang, keterangan, is_adjusment, createdate, ip_sender ,nop, file_adjustment
FROM user_transaction WHERE username=:usern AND to_char(tanggal,'MM')=:monTrans AND TO_CHAR(tanggal,'YYYY')=:year";
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
cmd.AddParameter("monTrans", OracleCmdParameterDirection.Input, monthTransaction.ToString("00"));
cmd.AddParameter("year", OracleCmdParameterDirection.Input, year);
return(cmd.GetTable());
}
public static bool InsertUserSettingColumn(string username, string nop, string colName, string colText)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"INSERT INTO USER_SETTING_COLUMN (USERNAME, NOP, COLUMN_NAME, COLUMN_TEXT)
VALUES(:usern, :nop, :colname, :coltext)";
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
cmd.AddParameter("nop", OracleCmdParameterDirection.Input, nop);
cmd.AddParameter("colname", OracleCmdParameterDirection.Input, colName);
cmd.AddParameter("coltext", OracleCmdParameterDirection.Input, colText);
return(cmd.ExecuteNonQuery() > 0);
}
public static DataTable RetrieveUserSettingColumn(string username, string nop)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT username,nop,column_name,column_text FROM USER_SETTING_COLUMN
WHERE username=:usern AND nop=:nop";
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
cmd.AddParameter("nop", OracleCmdParameterDirection.Input, nop);
return(cmd.GetTable());
}
public static System.Data.DataTable RetrieveDataPayment(string username, string nop, int month, int year)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT USERNAME, TANGGAL, KETERANGAN, IS_ADJUSMENT, PAJAK_TERUTANG, NOP,MASAPAJAK, TAHUN
FROM VW_GENERATEPAYMENT
WHERE nop IN (" + nop + ") and masapajak=:monTrans AND tahun=:year";
//cmd.AddParameter("nop", OracleCmdParameterDirection.Input, nop);
cmd.AddParameter("monTrans", OracleCmdParameterDirection.Input, month.ToString("00"));
cmd.AddParameter("year", OracleCmdParameterDirection.Input, year);
return(cmd.GetTable());
}
public static bool UpdateIsGenerate(string nop, DateTime tanggal)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"UPDATE USER_TRANSACTION SET IS_GENERATE=1
WHERE NOP=:nop AND trunc(tanggal) = :tgl";
cmd.AddParameter("nop", OracleCmdParameterDirection.Input, nop);
cmd.AddParameter("tgl", OracleCmdParameterDirection.Input, tanggal.Date);
return(cmd.ExecuteNonQuery() > 0);
}
public static DataTable RetrieveQueryPajak(string username, string nop)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT NOP, QUERY_PAJAK querypajak, QUERY_DETAIL querylampiran
FROM USER_SETTING_DATABASE WHERE USERNAME=:usern AND NOP=:nop";
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
cmd.AddParameter("nop", OracleCmdParameterDirection.Input, nop);
return(cmd.GetTable());
}
public static System.Data.DataTable RetrieveUserTransactionBetweenDate(DateTime tglAwal, DateTime tglAkhir)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT a.username,a.tanggal,a.pajak_terutang,a.keterangan,a.is_adjusment,a.createdate,a.ip_sender,a.nop,a.file_adjustment,b.jenis_pajak
FROM user_transaction a INNER JOIN user_nop b ON (a.nop=b.nop)
WHERE tanggal BETWEEN :tglAwal AND :tglAkhir";
cmd.AddParameter("tglAwal", OracleCmdParameterDirection.Input, tglAwal.ToString("dd MMM yyyy"));
cmd.AddParameter("tglAkhir", OracleCmdParameterDirection.Input, tglAkhir.ToString("dd MMM yyyy"));
return(cmd.GetTable());
}
public static bool UpdateUserClient(string email, string phone, string kdBank, string username)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"UPDATE user_client
SET email =:email, phone =:phone, kode_bank =:kdbank
WHERE username =:username";
cmd.AddParameter("email", OracleCmdParameterDirection.Input, email);
cmd.AddParameter("phone", OracleCmdParameterDirection.Input, phone);
cmd.AddParameter("kdbank", OracleCmdParameterDirection.Input, kdBank);
cmd.AddParameter("username", OracleCmdParameterDirection.Input, username);
return(cmd.ExecuteNonQuery() > 0);
}
public static bool IsCodeExist2(string kode, string username, string cpuId)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT COUNT(*)
FROM USER_CLIENT
WHERE SERIAL_KEY=:kodeSerial AND USERNAME=:usern AND ID_MACHINE=:cpuid";
cmd.AddParameter("kodeSerial", OracleCmdParameterDirection.Input, kode);
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
cmd.AddParameter("cpuid", OracleCmdParameterDirection.Input, cpuId);
return(Convert.ToInt32(cmd.ExecuteScalar()) > 0);
}
public static DataTable RetrieveJatuhTempo(int masaPajak, int tahunPajak)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT bulan, tahun,tgl_jatuh_tempo
FROM jatuhtempo_pajak
WHERE bulan=:bln AND tahun=:thn";
cmd.AddParameter("bln", OracleCmdParameterDirection.Input, masaPajak);
cmd.AddParameter("thn", OracleCmdParameterDirection.Input, tahunPajak);
return(cmd.GetTable());
}
public static bool InsertUserActivity(string username, string ipAddress, DateTime activityDate, bool status, string keterangan)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"INSERT INTO user_activity(username, ip_address,activity_date, status_error, keterangan)
VALUES(:usern, :ipaddress,:actdate, :status, :ket)";
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
cmd.AddParameter("ipaddress", OracleCmdParameterDirection.Input, ipAddress);
cmd.AddParameter("actdate", OracleCmdParameterDirection.Input, activityDate);
cmd.AddParameter("status", OracleCmdParameterDirection.Input, status);
cmd.AddParameter("ket", OracleCmdParameterDirection.Input, keterangan);
return(cmd.ExecuteNonQuery() > 0);
}
public static bool UpdatePajakUserTransaction(string username, string nop, DateTime tanggal, double pajak)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"UPDATE USER_TRANSACTION SET PAJAK_TERUTANG=:nominal
WHERE USERNAME=:usern AND NOP=:nop AND trunc(TANGGAL)=:tgl AND is_adjusment=0";
cmd.AddParameter("nominal", OracleCmdParameterDirection.Input, pajak);
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
cmd.AddParameter("nop", OracleCmdParameterDirection.Input, nop);
cmd.AddParameter("tgl", OracleCmdParameterDirection.Input, tanggal.ToString("dd MMM yyyy"));
return(cmd.ExecuteNonQuery() > 0);
}
public static bool RegisterSerialKey(string user, string key, string serialKey)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"UPDATE USER_CLIENT
SET serial_key=:serialKey
WHERE username=:usern AND id_machine=:idMachine";
cmd.AddParameter("serialKey", OracleCmdParameterDirection.Input, serialKey);
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, user);
cmd.AddParameter("idMachine", OracleCmdParameterDirection.Input, key);
return(cmd.ExecuteNonQuery() > 0);
}
public static bool InsertUserSourceDatabase(string username, string nop, string xmlSettDB, string ip, string namaDb)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"INSERT INTO USER_SOURCE_DB (username,nop,setting_db,ip_sender,nama_db)
VALUES(:usern,:nop,:settDB,:ipAdd,:nmDB)";
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
cmd.AddParameter("nop", OracleCmdParameterDirection.Input, nop);
cmd.AddParameter("settDB", OracleCmdParameterDirection.Input, xmlSettDB);
cmd.AddParameter("ipAdd", OracleCmdParameterDirection.Input, ip);
cmd.AddParameter("nmDB", OracleCmdParameterDirection.Input, namaDb);
return(cmd.ExecuteNonQuery() > 0);
}
public static DataTable RetrieveDetailOrderByDate(DateTime tglAwal, DateTime tglAkhir)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT to_date(to_char(transaction_date,'MM/DD/YYYY'),'MM/DD/YYYY') transaction_date,bulan, tahun, nop, MAX(create_date) create_Date
FROM user_transaction_detail
WHERE trunc(transaction_date) between :tglAwal AND :tglAkhir
GROUP BY to_date(to_char(transaction_date,'MM/DD/YYYY'),'MM/DD/YYYY'),bulan, tahun, nop
ORDER BY create_date DESC";
cmd.AddParameter("tglAwal", OracleCmdParameterDirection.Input, tglAwal.ToString("dd MMM yyyy", new System.Globalization.CultureInfo("en-US")));
cmd.AddParameter("tglAkhir", OracleCmdParameterDirection.Input, tglAkhir.ToString("dd MMM yyyy", new System.Globalization.CultureInfo("en-US")));
return(cmd.GetTable());
}
public static DataTable RetrieveUserClient(string username, string idMachine, string password)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT username, id_machine, password,serial_key, email, phone,
status_aktif,port_client
FROM user_client
WHERE username=:usern
AND id_machine=:idmachine
AND password=:passw";
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
cmd.AddParameter("idmachine", OracleCmdParameterDirection.Input, idMachine);
cmd.AddParameter("passw", OracleCmdParameterDirection.Input, password);
return(cmd.GetTable());
}
public static DataTable RetrieveDataSptpd(string username, int masapajak, int tahun, bool isActive, double pajak)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"select ID_SPTPD, USERNAME, MASAPAJAK, TAHUN, PAJAK, SANKSI, TOTAL, ID_BAYAR, IS_ACTIVE
from sptpd
where username=:usern and masapajak=:masapajak and tahun =:tahun and is_active =:isactive and pajak=:pjk";
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
cmd.AddParameter("masapajak", OracleCmdParameterDirection.Input, masapajak);
cmd.AddParameter("tahun", OracleCmdParameterDirection.Input, tahun);
cmd.AddParameter("isactive", OracleCmdParameterDirection.Input, isActive);
cmd.AddParameter("pjk", OracleCmdParameterDirection.Input, pajak);
return(cmd.GetTable());
}
public static bool InsertXmlFile(string username, string filename, string xmlContent, string fileNote, string separator)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"INSERT INTO user_xml_file(username,filename,file_xml,file_note,separate)
VALUES(:usern,:filename,:xmlFile,:fileNote,:separate)";
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
cmd.AddParameter("filename", OracleCmdParameterDirection.Input, filename);
cmd.AddParameter("xmlFile", OracleCmdParameterDirection.Input, xmlContent);
cmd.AddParameter("fileNote", OracleCmdParameterDirection.Input, fileNote);
cmd.AddParameter("separate", OracleCmdParameterDirection.Input, separator);
return(cmd.ExecuteNonQuery() > 0);
}
public static bool InsertHistoryRegister(string username, string encryptKode)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"INSERT INTO USER_HISTORY_REGISTER (KODE, USERNAME,STATUS)
VALUES(:kode,:usern,(
select (nvl(max(status),0) + 1) status
from user_history_register
where username=:usern)
)";
cmd.AddParameter("kode", OracleCmdParameterDirection.Input, encryptKode);
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
return(cmd.ExecuteNonQuery() > 0);
}
