public static System.Data.DataTable RetrieveDataPayment(string username, int?tahun)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"select username, nop,masapajak, tahun,is_generate,
sum(pajak_terutang) as pajak
from (
select * from vw_generatepayment
) ";
if (!string.IsNullOrEmpty(username))
{
cmd.Query += @"WHERE USERNAME IN (" + username + ") ";
}
if (tahun.HasValue)
{
if (!cmd.Query.Contains("WHERE"))
{
cmd.Query += @"WHERE tahun =:thn ";
cmd.AddParameter("thn", OracleCmdParameterDirection.Input, tahun.Value);
}
else
{
cmd.Query += @"AND tahun =:thn";
cmd.AddParameter("thn", OracleCmdParameterDirection.Input, tahun.Value);
}
}
cmd.Query += " group by username, nop,masapajak, tahun,is_generate ";
return(cmd.GetTable());
}
public static DataTable RetrievePortException()
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT PORT FROM EXCEPTION_PORT";
return(cmd.GetTable());
}
public static string GetXmlFileByNop(string nop, int bulan, int tahun)
{
DataTable dt = new DataTable();
string xml = string.Empty;
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT xml_file FROM user_transaction_detail
WHERE rownum=1 AND nop=:nop AND bulan=:bln AND tahun=:thn";
cmd.AddParameter("nop", OracleCmdParameterDirection.Input, nop);
cmd.AddParameter("bln", OracleCmdParameterDirection.Input, bulan);
cmd.AddParameter("thn", OracleCmdParameterDirection.Input, tahun);
dt = cmd.GetTable();
if (dt != null && dt.Rows.Count > 0)
{
xml = dt.Rows[0]["XML_FILE"].ToString();
}
else
{
xml = "Tidak ada data";
}
return(xml);
}
public static string RetrieveApiUrl()
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT URL_API from USER_API_URL";
return(cmd.ExecuteScalar().ToString());
}
public static System.Data.DataTable RetrieveUserInformationTransactionByMonth(string username, string nop, 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(
SELECT username, tanggal, pajak_terutang, keterangan, is_adjusment, createdate, ip_sender,nop, file_adjustment, ROW_NUMBER()OVER(PARTITION BY TRUNC(TANGGAL) ORDER BY CREATEDATE DESC) RN
FROM user_transaction
WHERE IS_ADJUSMENT = 0 AND to_char(tanggal,'MM')=:monTrans AND TO_CHAR(tanggal,'YYYY')=:year ";
cmd.AddParameter("monTrans", OracleCmdParameterDirection.Input, monthTransaction.ToString("00"));
cmd.AddParameter("year", OracleCmdParameterDirection.Input, year);
if (!string.IsNullOrEmpty(username))
{
cmd.Query += @" AND username=:usern ";
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
}
if (!string.IsNullOrEmpty(nop))
{
cmd.Query += @" AND nop=:nop ";
cmd.AddParameter("nop", OracleCmdParameterDirection.Input, nop);
}
cmd.Query += @" ) WHERE RN = 1";
return(cmd.GetTable());
}
public static DataTable RetrieveUserNop()
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = "SELECT DISTINCT(NOP) from USER_NOP WHERE IS_DELETED = 0";
return(cmd.GetTable());
}
public static DataTable RetrieveUserDetailTransactionByDate(string username, DateTime tgltransaksi)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
System.Text.StringBuilder sbQuery = new System.Text.StringBuilder();
sbQuery.Append(@"SELECT rownum, username, xml_path, transaction_date, ip_address, xml_file, bulan, tahun, nop, create_date
FROM(
SELECT username, xml_path, transaction_date, ip_address, xml_file, bulan, tahun, nop, create_date
FROM user_transaction_detail
WHERE TRUNC(transaction_date) = TO_DATE(:dateTrans, 'DD/MM/YYYY') "
);
//cmd.Query = @"SELECT rownum, username, xml_path, transaction_date, ip_address, xml_file, bulan, tahun, nop, create_date
// FROM (
// SELECT username, xml_path, transaction_date, ip_address, xml_file, bulan, tahun, nop, create_date
// FROM user_transaction_detail
// WHERE TRUNC(transaction_date) = TO_DATE(:dateTrans,'DD/MM/YYYY') and username = :usernm
// ORDER BY create_date DESC
// ) WHERE rownum = 1";
if (!string.IsNullOrEmpty(username))
{
sbQuery.Append("AND username = :usern ");
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
}
sbQuery.Append(" ORDER BY create_date DESC) WHERE rownum = 1");
cmd.Query = sbQuery.ToString();
cmd.AddParameter("dateTrans", OracleCmdParameterDirection.Input, tgltransaksi.ToString("dd/MM/yyyy"));
return(cmd.GetTable());
}
public static bool InsertTestimoni(string username, string commend)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = "";
return(cmd.ExecuteNonQuery() > 0);
}
public static System.Data.DataTable RetrieveAllNopByUsername(string username)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = "SELECT nop FROM user_nop WHERE username in (" + username + @")";
return(cmd.GetTable());
}
public static string GetUrlApi()
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT url_api FROM user_api_url";
return(cmd.ExecuteScalar().ToString());
}
public static DataTable RetrieveTarifAll()
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT jenispajak jenpajak, tarif FROM user_tarif_pajak";
return(cmd.GetTable());
}
public static DataTable RetrieveSettingColumn(string username)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT USERNAME, NOP, COLUMN_NAME, COLUMN_TEXT
FROM USER_SETTING_COLUMN WHERE USERNAME=:usern";
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
return(cmd.GetTable());
}
public static DataTable RetrieveAllUser()
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT USERNAME
FROM USER_CLIENT";
return(cmd.GetTable());
}
public static DataTable RetrieveTahunJatuhTempo()
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"Select distinct(tahun) from jatuhtempo_pajak
WHERE tahun between to_number(to_char(sysdate,'yyyy')) - 1 AND to_number(to_char(sysdate,'yyyy')) + 1
order by tahun";
return(cmd.GetTable());
}
public static DataTable RetrieveAllowMasaPajak()
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"select bulan, tahun,tgl_jatuh_tempo from jatuhtempo_pajak
where tgl_jatuh_tempo >= trunc(sysdate)
order by bulan,tahun";
return(cmd.GetTable());
}
public static bool UpdateWebUsername(string existUsername, string webUsername)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"UPDATE user_client SET web_username =:webUsern WHERE username =:exstUsern";
cmd.AddParameter("webUsern", OracleCmdParameterDirection.Input, webUsername);
cmd.AddParameter("exstUsern", OracleCmdParameterDirection.Input, existUsername);
return(cmd.ExecuteNonQuery() > 0);
}
public static DataTable RetrieveDetailSptpdByNop(string idSptpd, string nop)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"select ID_SPTPD, NOP, USERNAME, MASAPAJAK, TAHUN, ROUND(NOMINAL,0) NOMINAL from sptpd_detail
where id_sptpd=:idsptpd and nop=:nop";
cmd.AddParameter("idsptpd", OracleCmdParameterDirection.Input, idSptpd);
cmd.AddParameter("nop", OracleCmdParameterDirection.Input, nop);
return(cmd.GetTable());
}
public static DataTable RetrieveDataSptpdById(string idSptpd)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"select ID_SPTPD, USERNAME, MASAPAJAK, TAHUN, PAJAK, SANKSI, TOTAL, ID_BAYAR, IS_ACTIVE
from sptpd
where id_sptpd=:idsptpd";
cmd.AddParameter("idsptpd", OracleCmdParameterDirection.Input, idSptpd);
return(cmd.GetTable());
}
public static DataTable RetrieveUserActivity(string username)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT username,ip_address, activity_date, status_error, keterangan
FROM user_activity
WHERE username =:usern";
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
return(cmd.GetTable());
}
public static DataTable RetrieveUserClientByWebUsername(string webusername)
{
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 WEB_USERNAME=:usern AND STATUS_AKTIF=1";
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, webusername);
return(cmd.GetTable());
}
public static bool InsertUserTempError(string username, DateTime activityDate)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"INSERT INTO user_temp_error(username, tanggal_error)
VALUES(:usern, :actdate)";
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
cmd.AddParameter("actdate", OracleCmdParameterDirection.Input, activityDate);
return(cmd.ExecuteNonQuery() > 0);
}
public static DataTable RetrieveSourceDB(string username)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT NOP,SETTING_DB settingdb,NAMA_DB namadb FROM USER_SOURCE_DB WHERE USERNAME=:usern";
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
return(cmd.GetTable());
}
public static DataTable RetrieveAllTakenSerialNote()
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = "SELECT kode, taken_username, taken_hw_id, create_date, status, modidate " +
"FROM serial_note " +
"WHERE status !='" + DataBaseHelper.AvailableCommandNote + "'";
return(cmd.GetTable());
}
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 DataTable RetrieveMultiNopByUsername(string usern)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT UN.NOP, UN.NAMA_OBYEK FROM USER_NOP UN
INNER JOIN M_USER USR on USR.USER_APP = UN.USERNAME
WHERE USR.USERNAME = :usern AND USR.IS_DELETED=0";
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, usern);
return(cmd.GetTable());
}
public static DataTable GetVersion()
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT version,path_directory,is_important,create_date,islatestversion
FROM update_version WHERE is_important=1 AND islatestversion=1
ORDER BY create_date desc";
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 RetrieveNOPByUsername(string username)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT username, nop, jenis_pajak, nama_obyek
FROM user_nop
WHERE username =:usern";
cmd.AddParameter("usern", OracleCmdParameterDirection.Input, username);
return(cmd.GetTable());
}
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 GetSerialKey(string serialKey)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT COUNT(*)
FROM USER_CLIENT WHERE SERIAL_KEY=:serial";
cmd.AddParameter("serial", OracleCmdParameterDirection.Input, serialKey);
return(System.Convert.ToInt32(cmd.ExecuteScalar()) > 0);
}