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 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 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 RetrieveApiUrl()
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT URL_API from USER_API_URL";
return(cmd.ExecuteScalar().ToString());
}
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 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 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 bool InsertTestimoni(string username, string commend)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = "";
return(cmd.ExecuteNonQuery() > 0);
}
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 RetrieveVirtualAccount(string idSptpd)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommandBonang();
cmd.Query = @"SELECT * FROM virtualaccount_bank WHERE trx_id=:idSpt";
cmd.AddParameter("idSpt", OracleCmdParameterDirection.Input, idSptpd);
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 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 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 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 bool DeleteJadwal(string id)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommandElang();
cmd.Query = @"DELETE FROM JADWAL_ONLINE WHERE IDOP=@id";
cmd.AddParameter("id", OracleCmdParameterDirection.Input, id);
return(cmd.ExecuteNonQuery() > 0);
}
public static DataTable GetJadwal()
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommandElang();
cmd.Query = @"SELECT tanggal, obyek_pajak, alamat, nama_vendor, jam, kegiatan, status, petugas from JADWAL_ONLINE
where status1 not in '0'";
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 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 DataTable RetrieveRoleByName(string nameRole)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand();
cmd.Query = @"SELECT *
FROM M_ROLE
WHERE NAME_ROLE=:nameRole";
cmd.AddParameter("nameRole", OracleCmdParameterDirection.Input, nameRole);
return(cmd.GetTable());
}
public static DataTable RetrieveSptpdPayment(string kdValidasi)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommandBonang();
cmd.Query = @"SELECT * FROM sptpd_payment WHERE ref_bill=:ids";
cmd.AddParameter("ids", OracleCmdParameterDirection.Input, kdValidasi);
return(cmd.GetTable());
}
public static string GetNamaOPSptpd(string kdValidasi)
{
OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommandBonang();
cmd.Query = @"SELECT namaop FROM sptpd_baru WHERE id_sptpd=:ids";
cmd.AddParameter("ids", OracleCmdParameterDirection.Input, kdValidasi);
return(cmd.ExecuteScalar().ToString());
}
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 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 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 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());
}
