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 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 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 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 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 DataTable RetrieveTarifAll() { OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand(); cmd.Query = @"SELECT jenispajak jenpajak, tarif FROM user_tarif_pajak"; return(cmd.GetTable()); }
public static DataTable RetrieveAllUser() { OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand(); cmd.Query = @"SELECT USERNAME FROM USER_CLIENT"; 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 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 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 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 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 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 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 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 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 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 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 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 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 DataTable GetUserTempSetting(string teks) { OracleCmdBuilder cmd = DataBaseHelper.CreateOracleCommand(); cmd.Query = @"SELECT ID_SETTING,NAMA_SETTING,VALUE,KETERANGAN FROM USER_TEMP_SETTING WHERE UPPER(NAMA_SETTING) LIKE :teks"; cmd.AddParameter("teks", OracleCmdParameterDirection.Input, "%" + teks.ToUpper() + "%"); return(cmd.GetTable()); }
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()); }