private List<ILogItem> BuildObjects(OracleDataReader rdr)
{
var output = new List<ILogItem>();
while(rdr.Read())
{
CSELogItem temp = new CSELogItem();
temp.ID = rdr[0].ToString();
temp.UserID = rdr[1].ToString();
temp.Date = rdr[2].ToString();
temp.ProcedureName = rdr[3].ToString();
temp.Message = rdr[4].ToString();
output.Add(temp);
}
rdr.Dispose();
return output;
}
/// <summary>
/// Opens the data stored under the given ID for reading
/// </summary>
public Task <Stream> Read(string id)
{
try
{
// update last read time quickly
UpdateLastReadTime(id);
OracleDbConnection connection = null;
OracleCommand command = null;
OracleDataReader reader = null;
try
{
connection = _connectionHelper.GetConnection();
command = connection.CreateCommand();
command.CommandText = $"SELECT data FROM {_table} WHERE id = :id";
command.Parameters.Add("id", id);
command.InitialLOBFetchSize = 4000;
reader = command.ExecuteReader(CommandBehavior.SingleRow);
if (!reader.Read())
{
throw new ArgumentException($"DataBus row with ID {id} not found");
}
var blob = reader.GetOracleBlob(0);
return(Task.FromResult <Stream>(new StreamWrapper(blob, /* dispose with stream: */ reader, command, connection)));
}
catch
{
// if something of the above fails, we did not pass ownership to someone who can dispose it... therefore:
reader?.Dispose();
command?.Dispose();
connection?.Dispose();
throw;
}
}
catch (Exception exception)
{
// Wrap in AggregateException to comply with Rebus contract. Tests do look for this specific exception type.
throw new AggregateException(exception);
}
}
public bool Fill()
{
try {
conn = new OracleConnection(GetConnectionString());
conn.Open();
transaction = conn.BeginTransaction();
} catch (Exception e) {
MessageBox.Show("При подключении к базе данных произошла ошибка:\n\n" + e.Message);
conn.Close();
conn.Dispose();
return false;
}
cmd = conn.CreateCommand();
cmd.CommandText = "SELECT TO_CHAR(id), l_name from akriko.cat_executors WHERE id in (SELECT distinct(ispolnitel_cik_id) FROM akriko.appeal) ORDER BY UPPER(l_name)";
try {
dr = cmd.ExecuteReader();
while (dr.Read()) {
if (dr.IsDBNull(0) || dr.IsDBNull(1)) {
MessageBox.Show("При чтении данных из базы данных произошла ошибка:\n\n");
break;
} else {
UsersGrid.Rows.Add();
UsersGrid.Rows[UsersGrid.Rows.Count - 1].Cells["executor_id"].Value = dr.GetString(0);
UsersGrid.Rows[UsersGrid.Rows.Count - 1].Cells["akriko"].Value = dr.GetString(1);
}
}
dr.Close();
dr.Dispose();
} catch (Exception e) {
MessageBox.Show("При чтении данных из базы данных произошла ошибка:\n\n" + e.Message);
}
if (UsersGrid.Rows.Count == 0) {
MessageBox.Show("Отсутствуют неотконвертированные исполнители", "Конвертация исполнителей", MessageBoxButtons.OK, MessageBoxIcon.Information);
ConvertButton.Enabled = false;
}
FillDeloDepartmentsColumn();
return true;
}
void Backup(string executor_id)
{
cmd = conn.CreateCommand();
cmd.CommandText = "select TO_CHAR(id) from akriko.appeal WHERE ISPOLNITEL_CIK_ID=" + executor_id;
try {
dr = cmd.ExecuteReader();
while (dr.Read()) {
if (!dr.IsDBNull(0)) {
this.Log(dr.GetString(0), executor_id);
}
}
} catch (Exception e) {
MessageBox.Show("При чтении данных о подразделениях из базы данных произошла ошибка:\n\n" + e.Message);
}
if (dr != null) {
dr.Close();
dr.Dispose();
}
if (cmd != null) {
cmd.Dispose();
}
}
private void FillDeloDepartmentsColumn()
{
((DataGridViewComboBoxColumn)UsersGrid.Columns["delo"]).Items.Add("");
cmd = conn.CreateCommand();
cmd.CommandText = "select SUBSTR(p.due, 0, INSTR(p.due, '.', 3 )) departmentCode, p.due personCode, (SELECT d.classif_name FROM department d WHERE d.due = SUBSTR(p.due, 0, INSTR(p.due, '.', 3 ))) departmentName, classif_name from department p where DELETED=0 and due != '0.' order by due";
try {
dr = cmd.ExecuteReader();
while (dr.Read()) {
if (!dr.IsDBNull(0)) {
((DataGridViewComboBoxColumn)UsersGrid.Columns["delo"]).Items.Add(dr.GetString(3));
DeloPersons[dr.GetString(3)] = new DeloPerson(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetString(3));
}
}
} catch (Exception e) {
MessageBox.Show("При чтении данных о подразделениях из базы данных произошла ошибка:\n\n" + e.Message);
}
if (dr != null) {
dr.Close();
dr.Dispose();
}
if (cmd != null) {
cmd.Dispose();
}
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
else
{
// The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
app.UseHsts();
}
app.Run(async(context) =>
{
//Demo: Basic ODP.NET Core application for ASP.NET Core
// to connect, query, and return results to a web page
//Create a connection to Oracle
string conString = "User Id=hr;Password=<password>;" +
//How to connect to an Oracle DB without SQL*Net configuration file
// also known as tnsnames.ora.
"Data Source=<ip or hostname>:1521/<service name>;";
//How to connect to an Oracle DB with a DB alias.
//Uncomment below and comment above.
//"Data Source=<service name alias>;";
using (OracleConnection con = new OracleConnection(conString))
{
using (OracleCommand cmd = con.CreateCommand())
{
try
{
con.Open();
cmd.BindByName = true;
//Use the command to display employee names from
// the EMPLOYEES table
cmd.CommandText = "select first_name from employees where department_id = :id";
// Assign id to the department number 50
OracleParameter id = new OracleParameter("id", 50);
cmd.Parameters.Add(id);
//Execute the command and use DataReader to display the data
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
await context.Response.WriteAsync("Employee First Name: " + reader.GetString(0) + "\n");
}
reader.Dispose();
}
catch (Exception ex)
{
await context.Response.WriteAsync(ex.Message);
}
}
}
});
app.UseHttpsRedirection();
app.UseMvc();
}
/// <summary>
/// 打印骨灰安放卡
/// </summary>
/// <param name="rc001"></param>
public static void Print_RegSettle(string rc001)
{
StringBuilder sb_1 = new StringBuilder(200);
OracleCommand oc_command = new OracleCommand("select * from rc01 where rc001 = :rc001", SqlHelper.conn);
OracleParameter op_rc001 = new OracleParameter("rc001", OracleDbType.Varchar2, 10);
op_rc001.Direction = ParameterDirection.Input;
op_rc001.Value = rc001;
oc_command.Parameters.Add(op_rc001);
OracleDataReader reader = oc_command.ExecuteReader();
if (reader.HasRows && reader.Read())
{
sb_1.Append(reader["RC003"].ToString() + PADSTR); //逝者姓名
sb_1.Append(reader["RC109"].ToString() + PADSTR); //寄存证号
if (reader["RC002"] == null || reader["RC002"] is DBNull)
{
sb_1.Append("" + PADSTR); //性别
}
else
{
sb_1.Append(reader["RC002"].ToString() + PADSTR);
}
if (reader["RC004"] == null || reader["RC004"] is DBNull)
{
sb_1.Append("" + PADSTR); //年龄
}
else
{
sb_1.Append(reader["RC004"].ToString() + PADSTR);
}
//寄存位置
string s_position = RegAction.GetRegPosition(reader["RC001"].ToString());
if (string.IsNullOrEmpty(s_position))
{
sb_1.Append("" + PADSTR);
}
else
{
sb_1.Append(s_position + PADSTR);
}
if (reader["RC050"] == null || reader["RC050"] is DBNull)
{
sb_1.Append("" + PADSTR); //联系人
}
else
{
sb_1.Append(reader["RC050"].ToString() + PADSTR);
}
if (reader["RC051"] == null || reader["RC051"] is DBNull)
{
sb_1.Append("" + PADSTR); //联系电话
}
else
{
sb_1.Append(reader["RC051"].ToString() + PADSTR);
}
if (reader["RC052"] == null || reader["RC052"] is DBNull)
{
sb_1.Append("" + PADSTR); //与逝者关系
}
else
{
sb_1.Append(reader["RC052"].ToString() + PADSTR);
}
if (reader["RC200"] == null || reader["RC200"] is DBNull)
{
sb_1.Append("" + PADSTR); //经办日期
}
else
{
sb_1.Append(string.Format("{0:yyyy年MM月dd日}", reader["RC200"]) + PADSTR);
}
if (reader["RC055"] == null || reader["RC055"] is DBNull)
{
sb_1.Append("" + PADSTR); //联系地址
}
else
{
sb_1.Append(reader["RC055"].ToString() + PADSTR);
}
Send_PrintData printData = new Send_PrintData();
printData.command = "RegSettleCard";
printData.data = sb_1.ToString();
Frm_main.socket.sendMsg(Tool.ConvertObjectToJson(printData));
}
reader.Dispose();
oc_command.Dispose();
}
/// <summary>
/// 打印寄存证(基础)
/// </summary>
/// <param name="rc001"></param>
public static void Print_RegCardBase(string rc001)
{
StringBuilder sb_1 = new StringBuilder(200);
OracleCommand oc_command = new OracleCommand("select * from rc01 where rc001 = :rc001", SqlHelper.conn);
OracleParameter op_rc001 = new OracleParameter("rc001", OracleDbType.Varchar2, 10);
op_rc001.Direction = ParameterDirection.Input;
op_rc001.Value = rc001;
oc_command.Parameters.Add(op_rc001);
OracleDataReader reader = oc_command.ExecuteReader();
if (reader.HasRows && reader.Read())
{
sb_1.Append(reader["RC003"].ToString() + PADSTR); //逝者姓名
sb_1.Append(reader["RC109"].ToString() + PADSTR); //寄存证号
if (reader["RC002"] == null || reader["RC002"] is DBNull)
{
sb_1.Append("" + PADSTR); //性别
}
else
{
sb_1.Append(reader["RC002"].ToString() + PADSTR);
}
if (reader["RC004"] == null || reader["RC004"] is DBNull)
{
sb_1.Append("" + PADSTR); //年龄
}
else
{
sb_1.Append(reader["RC004"].ToString() + PADSTR);
}
//寄存位置
string s_position = RegAction.GetRegPosition(reader["RC001"].ToString());
if (string.IsNullOrEmpty(s_position))
{
sb_1.Append("" + PADSTR);
}
else
{
sb_1.Append(s_position + PADSTR);
}
if (reader["RC050"] == null || reader["RC050"] is DBNull)
{
sb_1.Append("" + PADSTR); //联系人
}
else
{
sb_1.Append(reader["RC050"].ToString() + PADSTR);
}
if (reader["RC051"] == null || reader["RC051"] is DBNull)
{
sb_1.Append("" + PADSTR); //联系电话
}
else
{
sb_1.Append(reader["RC051"].ToString() + PADSTR);
}
if (reader["RC200"] == null || reader["RC200"] is DBNull)
{
sb_1.Append("" + PADSTR); //经办日期
}
else
{
sb_1.Append(string.Format("{0:yyyy年MM月dd日}", reader["RC200"]) + PADSTR);
}
if (reader["RC055"] == null || reader["RC055"] is DBNull)
{
sb_1.Append("" + PADSTR); //联系地址
}
else
{
sb_1.Append(reader["RC055"].ToString() + PADSTR);
}
if (reader["RC140"] == null || reader["RC140"] is DBNull)
{
sb_1.Append("" + PADSTR); //寄存日期
}
else
{
sb_1.Append(string.Format("{0:yyyy-MM-dd}", reader["RC140"]) + PADSTR);
}
DateTime d_end = Convert.ToDateTime(SqlHelper.ExecuteScalar("select min(rc022) from rc04 where status = '1' and rc001 = '" + rc001 + "'"));
if (d_end == null)
{
sb_1.Append("" + PADSTR); //截至日期
}
else
{
sb_1.Append(string.Format("{0:yyyy-MM-dd}", d_end) + PADSTR);
}
decimal price = RegAction.GetRegPrice(rc001);
sb_1.Append(price.ToString() + PADSTR);
Send_PrintData printData = new Send_PrintData();
printData.command = "RegCardBase";
printData.data = sb_1.ToString();
Frm_main.socket.sendMsg(Tool.ConvertObjectToJson(printData));
}
reader.Dispose();
oc_command.Dispose();
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
string v_barcode1 = "";
if ((Request.Params["id"] != null) & (Request.Params["pw"] != null))
{
v_id = Request.Params["id"].ToString();
v_pw = Request.Params["pw"].ToString();
Sql_str = " SELECT S.USER_ID, S.USERNAME, S.PASSWORD, ";
Sql_str += " S.LOGIN_NAME, S.EMP_NO, S.EMP_NAME, ";
Sql_str += " S.DEPT_NO, S.DEPT_NAME ";
Sql_str += " FROM EAGLE.SYS_USERS S ";
Sql_str += " WHERE USERNAME = :v_ID ";
Sql_str += " AND PASSWORD = :v_PW ";
//建立連線
//使用web.config conn string
OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["jh815"].ConnectionString);
conn.Open();
OracleCommand cmd = new OracleCommand(Sql_str, conn);
cmd.Parameters.Add(new OracleParameter("v_ID", OracleType.VarChar, 100));
cmd.Parameters["v_ID"].Value = v_id;
cmd.Parameters.Add(new OracleParameter("v_PW", OracleType.VarChar, 100));
cmd.Parameters["v_PW"].Value = v_pw;
OracleDataReader dr = cmd.ExecuteReader();
try
{
//while (dr.Read())
//{
//組成JSON字串
if (dr.HasRows)
{
var dataQuery = from d in dr.Cast <DbDataRecord>()
select new
{
STATUS = "PASS",
USERNAME = (String)dr["USERNAME"],
EMP_NAME = (String)dr["EMP_NAME"]
};
string v_json = (JsonConvert.SerializeObject(dataQuery));
tsconn.save_log("guest", "APP Login", "192.168.0.19", v_json);
Response.Write(v_json);
//}
}
else
{
var dataQuery = new
{
STATUS = "REJECT",
USERNAME = v_id
};
string v_json = (JsonConvert.SerializeObject(dataQuery));
tsconn.save_log("guest", "APP Login", "192.168.0.19", v_json);
Response.Write(v_json);
}
}
catch (Exception ex)
{
var dataQuery = new
{
STATUS = "REJECT",
USERNAME = v_id
};
string v_json = (JsonConvert.SerializeObject(dataQuery));
tsconn.save_log("guest", "APP Login", "192.168.0.19", v_json);
Response.Write(v_json);
}
finally
{
cmd.Dispose();
dr.Close();
dr.Dispose();
conn.Close();
conn.Dispose();
}
//tsconn.trans_oracle815(Sql_str, "insert");
//Response.Write("Insert success!");
}
//else
//{
// Response.Write("No data insert!");
//}
}
catch (Exception ex)
{
var dataQuery = new
{
STATUS = "REJECT",
USERNAME = v_id
};
string v_json = (JsonConvert.SerializeObject(dataQuery));
tsconn.save_log("guest", "APP Login", "192.168.0.19", v_json);
Response.Write(v_json);
}
}
public void ExportToCSV(SqlDataSource dataSrc, string fileName)
{
//Add Response header
Response.Clear();
Response.AddHeader("content-disposition",
string.Format("attachment;filename={0}.csv", fileName));
Response.Charset = "";
Response.ContentType = "application/vnd.xls";
//GET Data From Database
OracleConnection cn = new OracleConnection(dataSrc.ConnectionString);
string query =
dataSrc.SelectCommand.Replace("\r\n", " ").Replace("\t", " ");
OracleCommand cmd = new OracleCommand(query, cn);
cmd.CommandTimeout = 999999;
cmd.CommandType = CommandType.Text;
try
{
cn.Open();
OracleDataReader dr = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
//CSV Header
for (int count = 0; count < dr.FieldCount; count++)
{
if (dr.GetName(count) != null)
{
sb.Append(dr.GetName(count));
}
if (count < dr.FieldCount - 1)
{
sb.Append(",");
}
}
Response.Write(sb.ToString() + "\n");
Response.Flush();
//CSV Body
while (dr.Read())
{
sb = new StringBuilder();
for (int col = 0; col < dr.FieldCount - 1; col++)
{
if (!dr.IsDBNull(col))
{
if (dr.GetDataTypeName(col).ToUpper() == "DATE")
{
sb.Append(DateTime.Parse(dr.GetValue(col).ToString()).ToString("dd-MMM-yyyy"));
}
else
{
sb.Append(dr.GetValue(col).ToString().Replace(",", " "));
}
}
sb.Append(",");
}
if (!dr.IsDBNull(dr.FieldCount - 1))
{
if (!dr.IsDBNull(dr.FieldCount - 1))
{
if (dr.GetDataTypeName(dr.FieldCount - 1).ToUpper() == "DATE")
{
sb.Append(DateTime.Parse(dr.GetValue(dr.FieldCount - 1).ToString()).ToString("dd-MMM-yyyy"));
}
else
{
sb.Append(dr.GetValue(dr.FieldCount - 1).ToString().Replace(",", " "));
}
}
}
Response.Write(sb.ToString() + "\n");
Response.Flush();
}
dr.Dispose();
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
finally
{
cmd.Connection.Close();
cn.Close();
}
Response.End();
}
public List <STEmitent> GetData(int trcode)
{
List <STEmitent> list_data = new List <STEmitent>();
STEmitent item;
OracleConnection connet = new OracleConnection();
try
{
connet = new OracleConnection(config.connectionstring);
connet.Open();
if (connet.State != ConnectionState.Open)
{
log.LogLine(string.Format("No connection to DB Oracle. CS: {0}", config.connectionstring)); return(list_data);
}
string query = string.Format("SELECT EmtCode,EmtName,Address,Phone,PostIndex,E_mail,BankCode,Account, " +
"DeptCode,Shifr_OKPO,Shifr_UNN,Shifr_OKED,TransCode,PeriodBeg,PeriodEnd,DirectName," +
"BuchName,CBSCode,ConnectionString,EmtShortName FROM {0}.Emitent WHERE TransCode={1} ORDER BY EmtCode",
"RCD", trcode);
OracleCommand cmd = new OracleCommand(query, connet);
OracleDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
item = new STEmitent();
item.code = reader.GetInt32(0);
item.name = reader["EmtName"].ToString().Trim();
item.address = reader["Address"].ToString().Trim();
item.phone = reader["Phone"].ToString().Trim();
item.postindex = reader["PostIndex"].ToString().Trim();
item.email = reader["E_mail"].ToString().Trim();
if (!reader.IsDBNull(6))
{
item.bankcode = reader.GetInt32(6);
}
item.account = reader["Account"].ToString().Trim();
if (!reader.IsDBNull(8))
{
item.deptcode = reader.GetInt32(8);
}
item.OKPO = reader["Shifr_OKPO"].ToString().Trim();
item.UNN = reader["Shifr_UNN"].ToString().Trim();
item.OKED = reader["Shifr_OKED"].ToString().Trim();
if (!reader.IsDBNull(12))
{
item.transcode = reader.GetInt32(12);
}
if (!reader.IsDBNull(13))
{
item.periodbegin = reader.GetDateTime(13);
}
if (!reader.IsDBNull(14))
{
item.periodend = reader.GetDateTime(14);
}
item.directname = reader["DirectName"].ToString().Trim();
item.buchname = reader["BuchName"].ToString().Trim();
if (!reader.IsDBNull(17))
{
item.CBScode = reader.GetInt32(17);
}
if (!reader.IsDBNull(18))
{
item.c_string = reader.GetString(18);
}
else
{
item.c_string = null;
}
if (!reader.IsDBNull(19))
{
item.shortname = reader.GetString(19);
}
else
{
item.shortname = null;
}
list_data.Add(item);
}
}
reader.Dispose();
}
catch (Exception ex) { log.LogLine(ex.Message); if (connet.State == ConnectionState.Open)
{
connet.Close();
}
}
return(list_data);
}
//========================================================================================
// Edit()
//========================================================================================
internal override void Edit(River.Orqa.Query.QueryWindow window)
{
Statusbar.Message = "Reading text...";
OracleCommand cmd = new OracleCommand(
"SELECT text"
+ " FROM dba_source"
+ " WHERE owner='" + schemaName
+ "' AND name='" + Text
+ "' AND type='FUNCTION'"
+ " ORDER BY line",
dbase.OraConnection
);
try
{
OracleDataReader reader = cmd.ExecuteReader();
if (reader.FieldCount > 0)
{
StringBuilder text = new StringBuilder();
if (reader.Read())
{
// Modify first line to insert cmd and qualify name
// We purposefully do not trim the end to preserve the space
string preamble = reader.GetString(0).Substring("FUNCTION".Length).TrimStart();
if (preamble.IndexOf("wrapped") > 0)
{
MessageBox.Show(
"Unable to display function; contents are wrapped.",
"Wrapped Content",
MessageBoxButtons.OK,
MessageBoxIcon.Information
);
}
else
{
text.Append("CREATE OR REPLACE FUNCTION "
+ schemaName + "." + preamble
);
// append rest of content
while (reader.Read())
{
text.Append(reader.GetString(0));
}
window.InsertText(text.ToString());
window.IsSaved = true;
window.SetTitle(schemaName + "." + this.Text);
}
}
}
reader.Close();
reader.Dispose(); reader = null;
Statusbar.Message = String.Empty;
}
catch (Exception exc)
{
River.Orqa.Dialogs.ExceptionDialog.ShowException(exc);
}
cmd.Dispose();
cmd = null;
}
/// <summary>
/// Recherche les informations sur les comptes en mode droits restreints
/// </summary>
/// <param name="userConnected"></param>
/// <returns></returns>
private bool SelectArchiConnectedUser(AccountMgmt.DataAccess.ConnectedUser userConnected)
{
OracleCommand sqlCommand = null;
OracleDataReader sqlReader = null;
string sql = "SELECT proj.id_project,proj.project, proj.date_creation, proj.date_modification, proj.commentary, proj.activation, " +
" app.id_application, app.application, app.id_project, app.default_tablespace, app.date_creation, app.date_modification, app.commentary, app.activation, " +
" mod.id_module, mod.module, mod.id_application, mod.date_creation, mod.date_modification, mod.commentary, mod.activation, " +
//" prof.id_profile, prof.profile, prof.id_module, prof.date_creation, prof.date_modification, prof.commentary, prof.activation, prof.Profile_oracle, Rsrc_conso_group " +
" prof.id_profile, prof.profile, prof.id_module, prof.date_creation, prof.date_modification, prof.commentary, prof.activation, prof.Profile_oracle " +
"FROM MOU01.module mod, MOU01.profile prof, MOU01.affectation aff, MOU01.application app, MOU01.project proj " +
"WHERE aff.id_user_ = " + userConnected.UserConnected.Id.ToString("0") +
" AND aff.activation < " + AccountMgmt.Common.Constants.DesactivationLevel.ToString("0") +
" AND aff.admin>=" + AccountMgmt.Common.Constants.AdminLevel.ToString("0") +
" AND aff.id_profile = prof.id_profile " +
" AND prof.activation < " + AccountMgmt.Common.Constants.DesactivationLevel.ToString("0") +
" AND prof.id_module = mod.id_module " +
" AND mod.activation < " + AccountMgmt.Common.Constants.DesactivationLevel.ToString("0") +
" AND app.id_application = mod.id_application " +
" AND app.activation < " + AccountMgmt.Common.Constants.DesactivationLevel.ToString("0") +
" AND app.id_project = proj.id_project " +
" AND proj.activation < " + AccountMgmt.Common.Constants.DesactivationLevel.ToString("0") +
" ORDER BY proj.project, app.application, mod.module, prof.profile";
bool bResult = false;
try
{
sqlCommand = new OracleCommand(sql, userConnected.Connection);
sqlReader = sqlCommand.ExecuteReader();
long oldProject = -1, oldApplication = -1, oldModule = -1, oldProfile = -1;
//lecture des données
while (sqlReader.Read())
{
//recupère le projet
if (oldProject != sqlReader.GetInt64(0))
{
Project newProject = new Project();
newProject.Id = sqlReader.GetInt64(0);
newProject.Name = sqlReader.GetString(1);
newProject.DateCreation = sqlReader.GetDateTime(2);
if (!sqlReader.IsDBNull(3))
{
newProject.DateModification = sqlReader.GetDateTime(3);
}
if (!sqlReader.IsDBNull(4))
{
newProject.Commentary = sqlReader.GetString(4);
}
if (sqlReader.GetInt16(5) == AccountMgmt.Common.Constants.ActivationLevel)
{
newProject.Activation = true;
}
else
{
newProject.Activation = false;
}
m_listProject.Add(newProject);
oldProject = sqlReader.GetInt64(0);
}
//recupère l'application
if (oldApplication != sqlReader.GetInt64(6))
{
Application newApplication = new Application();
newApplication.Id = sqlReader.GetInt64(6);
newApplication.Name = sqlReader.GetString(7);
newApplication.IdProject = sqlReader.GetInt64(8);
newApplication.DateCreation = sqlReader.GetDateTime(10);
if (!sqlReader.IsDBNull(11))
{
newApplication.DateModification = sqlReader.GetDateTime(11);
}
if (!sqlReader.IsDBNull(12))
{
newApplication.Commentary = sqlReader.GetString(12);
}
if (sqlReader.GetInt16(13) == AccountMgmt.Common.Constants.ActivationLevel)
{
newApplication.Activation = true;
}
else
{
newApplication.Activation = false;
}
m_listApplication.Add(newApplication);
oldApplication = sqlReader.GetInt64(6);
}
//recupère le module
if (oldModule != sqlReader.GetInt64(14))
{
Module newModule = new Module();
newModule.Id = sqlReader.GetInt64(14);
newModule.Name = sqlReader.GetString(15);
newModule.IdApplication = sqlReader.GetInt64(16);
newModule.DateCreation = sqlReader.GetDateTime(17);
if (!sqlReader.IsDBNull(18))
{
newModule.DateModification = sqlReader.GetDateTime(18);
}
if (!sqlReader.IsDBNull(19))
{
newModule.Commentary = sqlReader.GetString(19);
}
if (sqlReader.GetInt16(20) == AccountMgmt.Common.Constants.ActivationLevel)
{
newModule.Activation = true;
}
else
{
newModule.Activation = false;
}
m_listModule.Add(newModule);
oldModule = sqlReader.GetInt64(14);
}
//recupère le profile
if (oldProfile != sqlReader.GetInt64(21))
{
Profile newProfile = new Profile();
newProfile.Id = sqlReader.GetInt64(21);
newProfile.Name = sqlReader.GetString(22);
newProfile.IdModule = sqlReader.GetInt64(23);
newProfile.DateCreation = sqlReader.GetDateTime(24);
if (!sqlReader.IsDBNull(25))
{
newProfile.DateModification = sqlReader.GetDateTime(25);
}
if (!sqlReader.IsDBNull(26))
{
newProfile.Commentary = sqlReader.GetString(26);
}
if (sqlReader.GetInt16(27) == AccountMgmt.Common.Constants.ActivationLevel)
{
newProfile.Activation = true;
}
else
{
newProfile.Activation = false;
}
if (!sqlReader.IsDBNull(28))
{
newProfile.OracleProfile = sqlReader.GetString(28);
}
/*if(!sqlReader.IsDBNull(29))
* newProfile.RsrcConsoGroup = sqlReader.GetString(29);*/
m_listProfile.Add(newProfile);
oldProfile = sqlReader.GetInt64(21);
//récupère les roles associés au profile
AccessRole listRole = new AccessRole();
if (!listRole.SelectRight(userConnected.Connection, "ro.id_role = ri.id_role AND ri.id_profile=" + newProfile.Id.ToString(), "role"))
{
return(false);
}
m_listRole.AddRange((ArrayList)listRole.ListRole.Clone());
listRole.Dispose();
}
}
bResult = true;
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
finally
{
try
{
// Fermeture de la base de données
if (sqlReader != null)
{
sqlReader.Close();
sqlReader.Dispose();
}
if (sqlCommand != null)
{
sqlCommand.Dispose();
}
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
}
return(bResult);
}
/// <summary>
/// Supprime l'affectation entre les rôles du profile et l'utilisateur, revoke aussi les rôles à l'utilisateur
/// </summary>
/// <param name="connection"></param>
/// <param name="strUserOracle">UserOrcale</param>
/// <returns></returns>
public bool Delete(OracleConnection connection, string strUserOracle)
{
bool bResult = false;
// Start a local transaction
OracleCommand command = new OracleCommand();
command.Connection = connection;
OracleDataReader sqlReader = null;
OracleTransaction transaction = null;
string sql = "", strRoleList = "";
try
{
//lock de la table affectation
sql = "LOCK TABLE MOU01.affectation IN EXCLUSIVE MODE";
command.CommandText = sql;
command.ExecuteNonQuery();
try
{
transaction = connection.BeginTransaction();
//Recherche des rôles à REVOKER
sql = "SELECT nom, SUM(nb) FROM(" +
"(SELECT role.role nom, '+'||COUNT(role.role) nb FROM MOU01.role, MOU01.right, MOU01.profile, MOU01.affectation " +
"WHERE Role.id_role = Right.id_role " +
" AND right.id_profile = profile.id_profile " +
" AND affectation.id_profile = profile.id_profile " +
" AND affectation.id_user_=" + this.m_dIdUser.ToString("0") + " " +
"GROUP BY role.role) " +
"UNION ALL " +
"(SELECT role.role nom, '-'||COUNT(role.role) nb FROM MOU01.role, MOU01.right " +
"WHERE Role.id_role = Right.id_role" +
" AND right.id_profile=" + this.m_dIdProfile.ToString("0") + " " +
"GROUP BY role.role)) GROUP BY nom HAVING SUM(nb)=0";
command.CommandText = sql;
sqlReader = command.ExecuteReader();
//lecture des données
while (sqlReader.Read())
{
if (strRoleList != "")
{
strRoleList += ", ";
}
strRoleList = strRoleList + sqlReader.GetString(0);
}
//revoke les roles du profile à l'utilisateur
if (strRoleList != "")
{
sql = "REVOKE " + strRoleList + " FROM " + strUserOracle;
command.CommandText = sql;
command.ExecuteNonQuery();
}
//mise à jour de la table affectation
sql = "DELETE FROM MOU01.affectation WHERE id_user_=" + this.m_dIdUser.ToString("0") + " AND id_profile=" + this.m_dIdProfile.ToString("0");
command.CommandText = sql;
command.ExecuteNonQuery();
transaction.Commit();
sql = "COMMIT";
command.CommandText = sql;
command.ExecuteNonQuery();
bResult = true;
}
catch (Exception error)
{
transaction.Rollback();
sql = "ROLLBACK";
command.CommandText = sql;
command.ExecuteNonQuery();
MessageBox.Show("Problème lors de la suppression de l'affectation de l'utilisateur au profile avec l'erreur : " + error.Message);
}
}
catch (Exception error)
{
sql = "ROLLBACK";
command.CommandText = sql;
command.ExecuteNonQuery();
MessageBox.Show("Problème lors du lock de la table Affectation avec l'erreur : " + error.Message);
}
finally
{
//deconnexion
try
{
// Fermeture de la base de données
if (sqlReader != null)
{
sqlReader.Close();
sqlReader.Dispose();
}
if (command != null)
{
command.Dispose();
}
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
}
return(bResult);
}
/// <summary>
/// Permet de sélectionner des enregistrements suivant un critère de recherche et de trier ces résultats
/// </summary>
/// <param name="connection">Objet de connexion à la base de données</param>
/// <param name="idProfile">Identificateur du profile affecté</param>
/// <param name="strSort">Critère de trie</param>
/// <returns></returns>
public bool Select(OracleConnection connection, long idProfile, string strSort)
{
OracleCommand sqlCommand = null;
OracleDataReader sqlReader = null;
string sql = "SELECT u.id_user_, u.id_language_data_i, u.id_region_data, u.user_, u.user_oracle, u.service, u.date_creation, u.date_modification, u.commentary, u.activation, u.id_language_user_, u.pwd, u.date_beginning, u.date_end, u.id_user_modification, u.id_temporary_tablespace, u.dba_status, id_source," +
" a.admin, a.date_creation, a.date_modification, a.commentary, a.activation, a.date_beginning, a.date_end FROM MOU01.list_user u, MOU01.AFFECTATION a " +
" WHERE u.id_user_ = a.id_user_ AND a.id_profile = " + idProfile.ToString("0");
if (strSort != "")
{
sql = sql + " ORDER BY " + strSort;
}
bool bResult = false;
try
{
sqlCommand = new OracleCommand(sql, connection);
sqlReader = sqlCommand.ExecuteReader();
//lecture des données
while (sqlReader.Read())
{
User newUser = new User();
newUser.Id = long.Parse(sqlReader.GetValue(0).ToString()); //sqlReader.GetInt64(0);
newUser.IdLanguageData = long.Parse(sqlReader.GetValue(1).ToString()); //sqlReader.GetInt64(1);
newUser.IdRegionData = long.Parse(sqlReader.GetValue(2).ToString()); //sqlReader.GetInt64(2);
newUser.Name = sqlReader.GetString(3);
newUser.UserOracle = sqlReader.GetString(4);
newUser.Service = sqlReader.GetString(5);
newUser.DateCreation = sqlReader.GetDateTime(6);
if (!sqlReader.IsDBNull(7))
{
newUser.DateModification = sqlReader.GetDateTime(7);
}
if (!sqlReader.IsDBNull(8))
{
newUser.Commentary = sqlReader.GetString(8);
}
if (/*sqlReader.GetInt16(9)*/ short.Parse(sqlReader.GetValue(9).ToString()) == AccountMgmt.Common.Constants.ActivationLevel)
{
newUser.Activation = true;
}
else
{
newUser.Activation = false;
}
newUser.IdLanguageUser = long.Parse(sqlReader.GetValue(10).ToString()); //sqlReader.GetInt64(10);
if (!sqlReader.IsDBNull(11))
{
newUser.Password = sqlReader.GetString(11);
}
if (!sqlReader.IsDBNull(12))
{
newUser.DateBeginning = sqlReader.GetDateTime(12);
}
if (!sqlReader.IsDBNull(13))
{
newUser.DateEnd = sqlReader.GetDateTime(13);
}
if (!sqlReader.IsDBNull(14))
{
newUser.IdUserModification = long.Parse(sqlReader.GetValue(14).ToString()); //sqlReader.GetInt64(14);
}
newUser.IdTemporaryTableSpace = long.Parse(sqlReader.GetValue(15).ToString()); //sqlReader.GetInt64(15);
if (!sqlReader.IsDBNull(16))
{
newUser.Admin = short.Parse(sqlReader.GetValue(16).ToString());
}
else
{
newUser.Admin = AccountMgmt.Common.Constants.NoAdminLevel;
}
if (!sqlReader.IsDBNull(17))
{
newUser.IdSource = long.Parse(sqlReader.GetValue(17).ToString());
}
Affectation newAffectation = new Affectation();
newAffectation.IdUser = long.Parse(sqlReader.GetValue(0).ToString()); //sqlReader.GetInt64(0);
newAffectation.IdProfile = idProfile;
if (sqlReader.GetInt16(18) == AccountMgmt.Common.Constants.AdminLevel)
{
newAffectation.Admin = true;
}
else
{
newAffectation.Admin = false;
}
newAffectation.DateCreation = sqlReader.GetDateTime(19);
if (!sqlReader.IsDBNull(20))
{
newAffectation.DateModification = sqlReader.GetDateTime(20);
}
if (!sqlReader.IsDBNull(21))
{
newAffectation.Commentary = sqlReader.GetString(21);
}
if (sqlReader.GetInt16(22) == AccountMgmt.Common.Constants.ActivationLevel)
{
newAffectation.Activation = true;
}
else
{
newAffectation.Activation = false;
}
if (!sqlReader.IsDBNull(23))
{
newAffectation.DateBeginning = sqlReader.GetDateTime(23);
}
if (!sqlReader.IsDBNull(24))
{
newAffectation.DateEnd = sqlReader.GetDateTime(24);
}
newAffectation.AffectedUser = newUser;
m_listAffectedUser.Add(newAffectation);
}
bResult = true;
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
finally
{
try
{
// Fermeture de la base de données
if (sqlReader != null)
{
sqlReader.Close();
sqlReader.Dispose();
}
if (sqlCommand != null)
{
sqlCommand.Dispose();
}
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
}
return(bResult);
}
protected void categoryddn_SelectedIndexChanged(object sender, EventArgs e)
{
string category = categoryddn.SelectedItem.Text;
OracleConnection con = new OracleConnection();
con.ConnectionString = "Data Source=orcl;User Id=aadbase;Password=GvI.@HYnP;Integrated Security = no;";
con.Open();
OracleCommand cmd = new OracleCommand(@"SELECT Discount FROM Category where CategoryNAME='" + category + "'", con);
OracleCommand catidcmd = new OracleCommand(@"SELECT CategoryID FROM Category where CategoryNAME='" + category + "'", con);
//OracleDataAdapter da = new OracleDataAdapter(cmd);
//OracleDataAdapter catidda = new OracleDataAdapter(catidcmd);
// SqlDataAdapter da = new SqlDataAdapter(cmd);
double discount;
string categoryid;
OracleDataReader cmdreader = cmd.ExecuteReader();
while (cmdreader.Read())
{
discount = cmdreader.GetDouble(0);
Session["catdiscount"] = discount;
}
OracleDataReader catidreader = catidcmd.ExecuteReader();
while (catidreader.Read())
{
categoryid = catidreader.GetString(0);
Session["CategoryID"] = categoryid;
}
//DataTable dt = new DataTable();
//da.Fill(dt);
//int discount = Convert.ToInt32(da);
//Session["Discount"] = discount;
//DataTable catiddt = new DataTable();
//catidda.Fill(catiddt);
//string categoryid = Convert.ToString(catidda);
//Session["CategoryID"] = categoryid;
cmdreader.Dispose();
catidreader.Dispose();
con.Close();
int finalcardfare = Convert.ToInt32(Session["cardfareamount"]);
double finaldiscount = (Convert.ToDouble(Session["catdiscount"]));
double cardfinalamount = finalcardfare - (finaldiscount * finalcardfare);
//int cardfinalamount = Convert.ToInt32(Session["cardfareamount"]) - (Convert.ToInt32(Session["Discount"]) * Convert.ToInt32(Session["cardfareamount"]));
finalamountid.Visible = true;
finalamountid.Text = "YOUR FINAL AMOUNT W/ DISCOUNT: $" + cardfinalamount.ToString();
buynow.Visible = true;
Session["CardFinalAmount"] = cardfinalamount;
//if (category == "Student")
//{
//discountlbl.Text = "Congratulations! Your Discount of " + (discount * 100) + "% will be applied";
//}
}
/// <summary>
/// Processes the m52 message.
/// </summary>
/// <returns>A string collection with the data to be returned</returns>
public System.Collections.Specialized.StringCollection Process()
{
StringCollection res = null;
OracleConnection oraDBConn = null;
OracleCommand oraCmd = null;
OracleDataReader dr = null;
ILogger logger = null;
bool bRes = false;
bool bExist = false;
try
{
Database d = OPS.Components.Data.DatabaseFactory.GetDatabase();
logger = DatabaseFactory.Logger;
System.Data.IDbConnection DBCon = d.GetNewConnection();
oraDBConn = (OracleConnection)DBCon;
oraDBConn.Open();
if (oraDBConn.State == System.Data.ConnectionState.Open)
{
oraCmd = new OracleCommand();
oraCmd.Connection = (OracleConnection)oraDBConn;
if (!ExistWork(ref bExist, ref oraDBConn, ref oraCmd))
{
bRes = false;
}
else
{
bRes = true;
}
string strEndDate;
if (_EndDate == "")
{
strEndDate = "NULL";
}
else
{
strEndDate = string.Format("to_date('{0}', 'HH24MISSDDMMYY')", _EndDate);
}
if ((bRes) && (!bExist))
{
bRes = false;
oraCmd.CommandText = string.Format("insert into WORKS (WORK_SS_ID, WORK_PDA_ID, WORK_USR_ID, WORK_UNI_ID, WORK_NUM_PARK_SPACES, WORK_REMARKS, WORK_INI_DATE, WORK_END_DATE, WORK_LICEN_NUMBER, WORK_LICEN_CORP) " +
"values ({0}, '{1}', {2} , {3}, {4}, '{5}', to_date('{6}', 'HH24MISSDDMMYY'), {7}, '{8}', '{9}')",
_streetStretch,
_PDAID,
_userId,
_unitId,
_NumPlaces,
_Remarks,
_StartDate,
strEndDate,
_LicenseNumber,
_LicenseCompany);
oraCmd.ExecuteNonQuery();
bRes = true;
}
else if ((bRes) && (bExist))
{
bRes = false;
oraCmd.CommandText = string.Format("update WORKS set WORK_SS_ID={0}, WORK_USR_ID={2}, WORK_UNI_ID={3}, WORK_NUM_PARK_SPACES={4}, WORK_REMARKS='{5}', WORK_INI_DATE= to_date('{6}', 'HH24MISSDDMMYY'), WORK_END_DATE={7}, WORK_LICEN_NUMBER='{8}', WORK_LICEN_CORP='{9}' " +
"where WORK_PDA_ID='{1}'",
_streetStretch,
_PDAID,
_userId,
_unitId,
_NumPlaces,
_Remarks,
_StartDate,
strEndDate,
_LicenseNumber,
_LicenseCompany);
oraCmd.ExecuteNonQuery();
bRes = true;
}
}
if (bRes)
{
res = ReturnAck(AckMessage.AckTypes.ACK_PROCESSED);
}
else
{
if (logger != null)
{
logger.AddLog("[Msg56:Process]: Error.", LoggerSeverities.Error);
}
res = ReturnNack(NackMessage.NackTypes.NACK_ERROR_BECS);
}
}
catch (Exception e)
{
if (logger != null)
{
logger.AddLog("[Msg56:Process]: Error: " + e.Message, LoggerSeverities.Error);
}
res = ReturnNack(NackMessage.NackTypes.NACK_ERROR_BECS);
}
finally
{
if (dr != null)
{
dr.Close();
dr.Dispose();
dr = null;
}
if (oraCmd != null)
{
oraCmd.Dispose();
oraCmd = null;
}
if (oraDBConn != null)
{
oraDBConn.Close();
oraDBConn.Dispose();
oraDBConn = null;
}
}
return(res);
}
static void Main(string[] args)
{
// This sample demonstrates how to use ODP.NET Core Configuration API
// Add connect descriptors and net service names entries.
OracleConfiguration.OracleDataSources.Add("orclpdb", "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname or IP>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<service name>)(SERVER=dedicated)))");
OracleConfiguration.OracleDataSources.Add("orcl", "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname or IP>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<service name>)(SERVER=dedicated)))");
// Set default statement cache size to be used by all connections.
OracleConfiguration.StatementCacheSize = 25;
// Disable self tuning by default.
OracleConfiguration.SelfTuning = false;
// Bind all parameters by name.
OracleConfiguration.BindByName = true;
// Set default timeout to 60 seconds.
OracleConfiguration.CommandTimeout = 60;
// Set default fetch size as 1 MB.
OracleConfiguration.FetchSize = 1024 * 1024;
// Set tracing options
OracleConfiguration.TraceOption = 1;
OracleConfiguration.TraceFileLocation = @"D:\traces";
// Uncomment below to generate trace files
//OracleConfiguration.TraceLevel = 7;
// Set network properties
OracleConfiguration.SendBufferSize = 8192;
OracleConfiguration.ReceiveBufferSize = 8192;
OracleConfiguration.DisableOOB = true;
OracleConnection orclCon = null;
try
{
// Open a connection
orclCon = new OracleConnection("user id=hr; password=<password>; data source=orclpdb");
orclCon.Open();
// Execute simple select statement that returns first 10 names from EMPLOYEES table
OracleCommand orclCmd = orclCon.CreateCommand();
orclCmd.CommandText = "select first_name from employees where rownum <= 10 ";
OracleDataReader rdr = orclCmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine("Employee Name: " + rdr.GetString(0));
}
Console.ReadLine();
rdr.Dispose();
orclCmd.Dispose();
}
finally
{
// Close the connection
if (null != orclCon)
{
orclCon.Close();
}
}
}
/// <summary>
/// Recherche des utilisateurs affectés à un profile
/// </summary>
/// <param name="connection"></param>
/// <param name="OracleProfile"></param>
/// <param name="IdProfile"></param>
/// <param name="bAllUsers"></param>
/// <returns></returns>
public bool Select(OracleConnection connection, string OracleProfile, long IdProfile, bool bAllUsers)
{
OracleCommand sqlCommand = null;
OracleDataReader sqlReader = null;
string sql = "";
string strOracleProfileNull = "";
if (OracleProfile == null)
{
strOracleProfileNull = " is null ";
}
else
{
strOracleProfileNull = " = '" + OracleProfile + "'";
}
if (!bAllUsers) //Etude de profil sur l'ensemble des utilisateurs affectés au profil MOU
{
sql = "select distinct f.username,f.user_,f.profile,f.profile_oracle,f.color from " +
"( " +
//-- Vert ( Affecté - Pas autre profile possible )
"select du.username,u.user_, du.profile,p.profile_oracle,'vert' as color from dba_users du,mou01.affectation a, mou01.profile p, mou01.user_ u " +
"where p.id_profile= " + IdProfile +
" and p.id_profile=a.id_profile " +
"and u.id_user_=a.id_user_ " +
"and u.user_oracle=du.username " +
"and du.profile=nvl(p.profile_oracle,'DEFAULT') " +
"and du.username not in " +
"(select u1.user_oracle from mou01.user_ u1,mou01.affectation a1, mou01.profile p1 " +
"where p1.profile_oracle <> 'DEFAULT' " +
"and p1.profile_oracle is not null " +
"and p1.profile_oracle<> '" + OracleProfile + "' " +
"and a1.id_profile=p1.id_profile " +
"and u1.id_user_=a1.id_user_ " +
") " +
"union " +
//-- Jaune ( Affecté - Autre profile possible )
"select du.username,u.user_, du.profile,p.profile_oracle,'jaune' as color " +
"from dba_users du,mou01.affectation a, mou01.profile p, mou01.user_ u " +
"where p.id_profile = " + IdProfile +
" and p.id_profile=a.id_profile " +
"and u.id_user_=a.id_user_ " +
"and u.user_oracle=du.username " +
"and du.profile=nvl(p.profile_oracle,'DEFAULT') " +
"and du.username in " +
"(select u1.user_oracle from mou01.user_ u1,mou01.affectation a1, mou01.profile p1 " +
"where p1.profile_oracle <> 'DEFAULT' " +
"and p1.profile_oracle is not null " +
"and p1.profile_oracle <> '" + OracleProfile + "' " +
"and a1.id_profile=p1.id_profile " +
"and u1.id_user_=a1.id_user_ " +
") " +
"union " +
//-- Orange ( Pas affecté - Pas autre profile possible )
"select du.username,u.user_, du.profile,p.profile_oracle,'orange' as color from dba_users du,mou01.affectation a, mou01.profile p, mou01.user_ u " +
"where p.id_profile= " + IdProfile +
" and p.id_profile=a.id_profile " +
"and u.id_user_=a.id_user_ " +
"and u.user_oracle=du.username " +
"and du.profile<>nvl(p.profile_oracle,'DEFAULT') " +
"and du.username not in " +
"(select u1.user_oracle from mou01.user_ u1,mou01.affectation a1, mou01.profile p1 " +
"where p1.profile_oracle <> 'DEFAULT' " +
"and p1.profile_oracle is not null " +
"and p1.profile_oracle<> '" + OracleProfile + "' " +
"and a1.id_profile=p1.id_profile " +
"and u1.id_user_=a1.id_user_ " +
") " +
"union " +
//-- Rouge ( Pas affecté - Autre profile possible )
"select du.username,u.user_, du.profile,p.profile_oracle,'rouge' as color from dba_users du,mou01.affectation a, mou01.profile p, mou01.user_ u " +
"where p.id_profile= " + IdProfile +
" and p.id_profile=a.id_profile " +
"and u.id_user_=a.id_user_ " +
"and u.user_oracle=du.username " +
"and du.profile<>nvl(p.profile_oracle,'DEFAULT') " +
"and du.username in " +
"(select u1.user_oracle from mou01.user_ u1,mou01.affectation a1, mou01.profile p1 " +
"where p1.profile_oracle <> 'DEFAULT' " +
"and p1.profile_oracle is not null " +
"and p1.profile_oracle <> '" + OracleProfile + "' " +
"and a1.id_profile=p1.id_profile " +
"and u1.id_user_=a1.id_user_ " +
") " +
") f " +
"order by f.username";
}
else //Etude de profil sur l'ensemble des utilisateurs affectés au profil ORACLE
{
sql = "select distinct f.username,f.user_,f.profile,f.profile_oracle,f.color from " +
"( " +
//-- Vert ( Affecté - Pas autre profile possible )
"select du.username,u.user_, du.profile,p.profile_oracle,'vert' as color from dba_users du,mou01.affectation a, mou01.profile p, mou01.user_ u " +
"where p.id_profile=a.id_profile " +
"and u.id_user_=a.id_user_ " +
"and u.user_oracle=du.username " +
"and du.profile=nvl(p.profile_oracle,'DEFAULT') " +
"and du.username not in " +
"(select u1.user_oracle from mou01.user_ u1,mou01.affectation a1, mou01.profile p1 " +
"where p1.profile_oracle <> 'DEFAULT' " +
"and p1.profile_oracle is not null " +
"and p1.profile_oracle<> '" + OracleProfile + "' " +
"and a1.id_profile=p1.id_profile " +
"and u1.id_user_=a1.id_user_ " +
") " +
"and p.profile_oracle " + strOracleProfileNull +
"union " +
//-- Jaune ( Affecté - Autre profile possible )
"select du.username,u.user_, du.profile,p.profile_oracle,'jaune' as color from dba_users du,mou01.affectation a, mou01.profile p, mou01.user_ u " +
"where p.id_profile=a.id_profile " +
"and u.id_user_=a.id_user_ " +
"and u.user_oracle=du.username " +
"and du.profile=nvl(p.profile_oracle,'DEFAULT') " +
"and du.username in " +
"(select u1.user_oracle from mou01.user_ u1,mou01.affectation a1, mou01.profile p1 " +
"where p1.profile_oracle <> 'DEFAULT' " +
"and p1.profile_oracle is not null " +
"and p1.profile_oracle <> '" + OracleProfile + "' " +
"and a1.id_profile=p1.id_profile " +
"and u1.id_user_=a1.id_user_ " +
") " +
"and p.profile_oracle " + strOracleProfileNull +
"union " +
//-- Orange ( Pas affecté - Pas autre profile possible )
"select du.username,u.user_, du.profile,p.profile_oracle,'orange' as color from dba_users du,mou01.affectation a, mou01.profile p, mou01.user_ u " +
"where p.id_profile=a.id_profile " +
"and u.id_user_=a.id_user_ " +
"and u.user_oracle=du.username " +
"and du.profile<>nvl(p.profile_oracle,'DEFAULT') " +
"and du.username not in " +
"(select u1.user_oracle from mou01.user_ u1,mou01.affectation a1, mou01.profile p1 " +
"where p1.profile_oracle <> 'DEFAULT' " +
"and p1.profile_oracle is not null " +
"and p1.profile_oracle<> '" + OracleProfile + "' " +
"and a1.id_profile=p1.id_profile " +
"and u1.id_user_=a1.id_user_ " +
") " +
"and p.profile_oracle " + strOracleProfileNull +
"union " +
//-- Rouge ( Pas affecté - Autre profile possible )
"select du.username,u.user_, du.profile,p.profile_oracle,'rouge' as color from dba_users du,mou01.affectation a, mou01.profile p, mou01.user_ u " +
"where p.id_profile=a.id_profile " +
"and u.id_user_=a.id_user_ " +
"and u.user_oracle=du.username " +
"and du.profile<>nvl(p.profile_oracle,'DEFAULT') " +
"and du.username in " +
"(select u1.user_oracle from mou01.user_ u1,mou01.affectation a1, mou01.profile p1 " +
"where p1.profile_oracle <> 'DEFAULT' " +
"and p1.profile_oracle is not null " +
"and p1.profile_oracle <> '" + OracleProfile + "' " +
"and a1.id_profile=p1.id_profile " +
"and u1.id_user_=a1.id_user_ " +
") " +
"and p.profile_oracle " + strOracleProfileNull +
") f " +
"order by f.username";
}
bool bResult = false;
try
{
sqlCommand = new OracleCommand(sql, connection);
sqlReader = sqlCommand.ExecuteReader();
//lecture des données
while (sqlReader.Read())
{
OracleProfile newOracleProfile = new OracleProfile();
newOracleProfile.UserOracle = sqlReader.GetValue(0).ToString();
newOracleProfile.UserName = sqlReader.GetValue(1).ToString();
newOracleProfile.OracleProfileAffected = sqlReader.GetValue(2).ToString();
newOracleProfile.OracleProfileSel = sqlReader.GetValue(3).ToString();
newOracleProfile.Color = sqlReader.GetValue(4).ToString();
m_listOracleProfile.Add(newOracleProfile);
}
bResult = true;
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
finally
{
try
{
// Fermeture de la base de données
if (sqlReader != null)
{
sqlReader.Close();
sqlReader.Dispose();
}
if (sqlCommand != null)
{
sqlCommand.Dispose();
}
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
}
return(bResult);
}
public object GetObject(string sql, string _ObjeclClassName, string _NameSpaceName)
{
if (sql == null || sql.Trim().Equals(""))
{
return(null);
}
OracleDataReader reader = GetTableData(sql);
try {
if (reader.Read())
{
Assembly assembly = Assembly.Load(_NameSpaceName);
object obj = assembly.CreateInstance(_ObjeclClassName);
if (null == obj)
{
return(null);
}
Type type = obj.GetType();
PropertyInfo[] ps = type.GetProperties();
foreach (PropertyInfo pi in ps)
{
string _fieldName = pi.Name;
string _typeName = pi.PropertyType.Name;
if (IsContainColumn(reader, _fieldName)) //查询的结果包含列
{
if (_typeName.ToLower() == "string") //赋字符串
{
pi.SetValue(obj, reader[_fieldName].ToString().Trim(), null);
}
else if (_typeName.ToLower().Contains("int")) //赋整数
{
pi.SetValue(obj, ConvertToInt32(reader[_fieldName].ToString().Trim()), null);
}
else if (_typeName.ToLower().Contains("datetime")) //赋整数
{
pi.SetValue(obj, ConvertToDatetime(reader[_fieldName]), null);
}
else if (_typeName.ToLower().Contains("date")) //赋整数
{
pi.SetValue(obj, ConvertToDatetime(reader[_fieldName]), null);
}
else if (_typeName.ToLower().Contains("byte")) //赋byte
{
pi.SetValue(obj, reader[_fieldName], null);
}
else //赋double
{
pi.SetValue(obj, ConvertToDouble(reader[_fieldName].ToString().Trim()), null);
}
}
}
return(obj);
}
} catch (Exception e) {
throw e;
} finally {
reader.Close();
reader.Dispose();
CloseConnection();
}
return(null);
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
string v_ver = "";
if ((Request.Params["version"] != null))
{
v_ver = Request.Params["version"].ToString();
//Y = 啟動 , N = 停用
Sql_str = " SELECT TITTLE, CONTENT,VERSION FROM PDA_NOTIFICATION WHERE STATUS = 'Y' AND VERSION > '" + v_ver + "' ";
//建立連線
//使用web.config conn string
OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["jheip"].ConnectionString);
conn.Open();
OracleCommand cmd = new OracleCommand(Sql_str, conn);
OracleDataReader dr = cmd.ExecuteReader();
try
{
//while (dr.Read())
//{
//組成JSON字串
if (dr.HasRows)
{
var dataQuery = from d in dr.Cast <DbDataRecord>()
select new
{
STATUS = "PASS",
TITTLE = (String)dr["TITTLE"],
CONTENT = (String)dr["CONTENT"],
VERSION = (String)dr["VERSION"]
};
string v_json = (JsonConvert.SerializeObject(dataQuery));
//tsconn.save_log("guest", "NOTFICATION PASS", "192.168.0.19", v_json);
Response.Write(v_json);
//}
}
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
finally
{
cmd.Dispose();
dr.Close();
dr.Dispose();
conn.Close();
conn.Dispose();
}
}
}
catch (Exception ex)
{
var dataQuery = new
{
STATUS = "REJECT"
};
string v_json = (JsonConvert.SerializeObject(dataQuery));
//tsconn.save_log("guest", "NOTFICATION REJECT", "192.168.0.19", v_json);
Response.Write(v_json);
}
}
/// <summary>
/// Executes the query using the information in the passed <paramref name="binder"/>. When no rows are found, an empty list is returned.
/// </summary>
/// <typeparam name="T">The type of each row in the list of results</typeparam>
/// <param name="xmlQuery"></param>
/// <param name="binder">Information which describes the query to execute</param>
/// <param name="maxRows">Maximum number of rows to retrieve. 0 means all.</param>
/// <returns>A list of rows returned by the query</returns>
/// <exception cref="ArgumentNullException"><paramref name="binder"/> is null</exception>
/// <exception cref="OperationCanceledException">Number of rows retturned by query exceeded <see cref="DefaultMaxRows"/></exception>
/// <exception cref="OracleException">Query execution error</exception>
/// <remarks>
/// <para>
/// The connection is opened if it is not already open.
/// </para>
/// <para>
/// Sharad 17 Nov 2011: Added new parameter <paramref name="maxRows"/>
/// </para>
/// </remarks>
public IList <T> ExecuteReader <T>(string xmlQuery, SqlBinder <T> binder, int maxRows)
{
if (binder == null)
{
throw new ArgumentNullException("binder");
}
OracleCommand cmd = null;
OracleDataReader reader = null;
try
{
cmd = CreateCommand(xmlQuery, binder.GetParameter, binder.ActionName);
//PrepareConnection(binder.ActionName);
reader = cmd.ExecuteReader(maxRows == 1 ? CommandBehavior.SingleRow : CommandBehavior.Default);
if (reader.RowSize > 0 && maxRows > 0)
{
// Safety check. FetchSize optimization is applied only if RowSize is known and maxRows is specified
// Treat default Fetch Size specified in configuration as the maximum allowed.
// See whether we can reduce the fetch size to save memory
reader.FetchSize = Math.Min(reader.RowSize * maxRows, reader.FetchSize);
}
// Lazy loading will not work because the map context will not be available for mapping
//var rowcount = 0;
//foreach (var result in from object row in reader select binder.Mapper.Engine.Map<IOracleDataRow, T>(dict))
var results = binder.MapRows(reader).Take(maxRows > 0 ? maxRows : this.DefaultMaxRows).ToList();
if (maxRows == 0 && reader.Read())
{
// User did not specify maxRows and we did not read all of them. Generate error.
var msg = string.Format("Query aborted because more than {0} rows were retrieved",
this.DefaultMaxRows);
if (_traceContext != null)
{
_traceContext.Warn(msg);
}
throw new OperationCanceledException(msg);
//}
}
if (_traceContext != null)
{
_traceContext.Write(string.Format("ExecuteReader returned {0} rows", results.Count));
}
var b = reader.NextResult();
return(results);
}
catch (OracleException ex)
{
if (_traceContext != null)
{
this._traceContext.Warn("Exception", "", ex);
}
if (ex.Number == 28150)
{
var userId = _builder.UserID;
var proxyUser = _builder.ProxyUserId;
var msg = string.Format("User '{0}' was not authenticated to connect as proxy user", userId) + "\n\n Excute following script to get rid of this error: \n\n" +
string.Format("ALTER USER {0} GRANT CONNECT THROUGH {1};", userId, proxyUser);
throw new AuthenticationException(msg);
}
throw new OracleDataStoreException(ex, cmd);
}
catch (Exception ex)
{
if (cmd == null)
{
throw;
}
// Include the query as part of the exception
throw new Exception(cmd.CommandText, ex);
}
finally
{
if (reader != null)
{
reader.Dispose();
}
if (cmd != null)
{
foreach (OracleParameter parameter in cmd.Parameters)
{
parameter.Dispose();
}
cmd.Dispose();
}
QueryLogging.TraceQueryEnd(_traceContext);
}
}
/// <summary>
/// 打印收款凭证（寄存收费）
/// </summary>
/// <param name="fa001"></param>
public static void Print_Skpz1(string fa001)
{
//结算数据
OracleCommand oc_jsd = new OracleCommand("select * from v_fa01 where fa001= :fa001", SqlHelper.conn);
OracleParameter op_fa001 = new OracleParameter("fa001", OracleDbType.Varchar2, 10);
op_fa001.Direction = ParameterDirection.Input;
op_fa001.Value = fa001;
OracleParameter op_sa010 = new OracleParameter("sa010", OracleDbType.Varchar2, 10);
op_sa010.Direction = ParameterDirection.Input;
op_sa010.Value = fa001;
oc_jsd.Parameters.Add(op_fa001);
//结算明细数据
OracleCommand oc_detail = new OracleCommand("select * from v_sa01 where sa010= :sa010", SqlHelper.conn);
oc_detail.Parameters.Add(op_sa010);
OracleDataReader reader = oc_jsd.ExecuteReader();
OracleDataReader reader2 = oc_detail.ExecuteReader();
string s_skr = string.Empty;
string s_skrq = string.Empty;
string s_cuname = string.Empty;
string s_memo = string.Empty;
int i_count = 0;
if (reader.HasRows && reader.Read())
{
StringBuilder sb_1 = new StringBuilder(100);
while (reader2.Read())
{
sb_1.Append(reader2["SA002"].ToString() + PADSTR); // 服务商品类别
sb_1.Append(reader2["SA003"].ToString() + PADSTR); // 服务或商品名
sb_1.Append(reader2["PRICE"].ToString() + PADSTR); // 单价
sb_1.Append(reader2["NUMS"].ToString() + PADSTR); // 数量
sb_1.Append(reader2["SA007"].ToString() + PADSTR); // 销售金额
sb_1.Append(PADSTR2);
i_count++;
}
s_skr = MiscAction.Mapper_Operator(reader["FA100"].ToString());
s_skrq = string.Format("{0:yyyyMMdd}", reader["FA200"]);
s_cuname = reader["FA003"].ToString();
Send_PrintData printData = new Send_PrintData();
printData.command = "print_skpz1";
printData.data = sb_1.ToString();
printData.extra1 = s_cuname;
printData.extra2 = s_skr;
printData.extra3 = s_skrq;
printData.extra4 = RegAction.GetRegFinMemo(fa001);
//i_pages = (int)Math.Ceiling(i_count / (App_Const.BILL_COUNT * 1.0));
//XtraMessageBox.Show("现在开始打印【结算单】，共需要" + i_pages.ToString() + "张!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
Frm_main.socket.sendMsg(Tool.ConvertObjectToJson(printData));
}
else
{
MessageBox.Show("未找到结算数据!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
reader.Dispose();
reader2.Dispose();
oc_jsd.Dispose();
}
protected override void DoDiscoverWork(object sender, DoWorkEventArgs e)
{
string sql =
"SELECT O.object_name, O.created, O.last_ddl_time, O.status, S.text,"
+ " COALESCE(E.IsError, 0) AS IsError"
+ " FROM dba_objects O"
+ " JOIN dba_source S"
+ " ON S.owner = O.owner"
+ " AND S.name = O.object_name"
+ " AND S.type = O.object_type"
+ " AND S.line = 1"
+ " LEFT OUTER JOIN"
+ " (SELECT DISTINCT E.name, 1 AS IsError"
+ " FROM User_Errors E"
+ " WHERE E.type = 'PACKAGE') E"
+ " ON E.name = O.object_name"
+ " WHERE O.owner = '" + schemaName + "'"
+ " AND O.object_type = 'PACKAGE'"
+ " ORDER BY O.object_name";
OracleCommand cmd = new OracleCommand(sql, dbase.OraConnection);
try
{
SchemataPackage package;
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (Logger.IsEnabled)
{
Logger.WriteLine(schemaName + "." + reader.GetString(0));
}
package = new SchemataPackage(dbase, srvnode, reader.GetString(0));
package.AddProperty("Name", reader.GetString(0));
package.AddProperty("Created", reader.GetDateTime(1).ToString());
package.AddProperty("Last DDL Time", reader.GetDateTime(2).ToString());
package.AddProperty("Status", reader.GetString(3));
string preamble = reader.GetString(4).ToString().Trim().ToLower();
package.IsWrapped = preamble.Contains("wrapped");
package.AddProperty("Wrapped", package.IsWrapped ? "Yes" : "No");
if ((int)reader.GetDecimal(5) == 1)
{
package.ForeColor = System.Drawing.Color.Red;
}
else if (reader.GetString(3) == "INVALID")
{
package.ForeColor = System.Drawing.Color.Green;
}
discoveries.Add(package);
}
reader.Close();
reader.Dispose();
reader = null;
}
catch (Exception exc)
{
River.Orqa.Dialogs.ExceptionDialog.ShowException(exc);
}
cmd.Dispose();
cmd = null;
}
/// <summary>
/// 打印火化证明
/// </summary>
/// <param name="ac001"></param>
public static void Print_HHZM(string ac001)
{
StringBuilder sb_1 = new StringBuilder(200);
OracleCommand oc_command = new OracleCommand("select * from V_PRINT_HHZM where ac001 = :ac001", SqlHelper.conn);
OracleParameter op_ac001 = new OracleParameter("ac001", OracleDbType.Varchar2, 10);
op_ac001.Direction = ParameterDirection.Input;
op_ac001.Value = ac001;
oc_command.Parameters.Add(op_ac001);
OracleDataReader reader = oc_command.ExecuteReader();
if (reader.HasRows && reader.Read())
{
if (reader["MM"] == null || reader["MM"] is DBNull)
{
sb_1.Append("" + PADSTR); //密码
}
else
{
sb_1.Append(reader["MM"].ToString() + PADSTR);
}
if (reader["AC003"] == null || reader["AC003"] is DBNull)
{
sb_1.Append("" + PADSTR); //逝者姓名
}
else
{
sb_1.Append(reader["AC003"].ToString() + PADSTR);
}
if (reader["AC002"] == null || reader["AC002"] is DBNull)
{
sb_1.Append("" + PADSTR); //逝者性别
}
else
{
sb_1.Append(reader["AC002"].ToString() + PADSTR);
}
if (reader["AC004"] == null || reader["AC004"] is DBNull)
{
sb_1.Append("" + PADSTR); //逝者年龄
}
else
{
sb_1.Append(reader["AC004"].ToString() + PADSTR);
}
if (reader["AC008"] == null || reader["AC008"] is DBNull)
{
sb_1.Append("" + PADSTR); //住址
}
else
{
sb_1.Append(reader["AC008"].ToString() + PADSTR);
}
if (reader["AC005"] == null || reader["AC005"] is DBNull)
{
sb_1.Append("" + PADSTR); //死因
}
else
{
sb_1.Append(reader["AC005"].ToString() + PADSTR);
}
if (reader["FIRETIME"] == null || reader["FIRETIME"] is DBNull)
{
sb_1.Append("" + PADSTR); //火化时间
}
else
{
sb_1.Append(reader["FIRETIME"].ToString() + PADSTR);
}
sb_1.Append(Envior.cur_user.UC003 + PADSTR); //经办人
if (reader["FIRETIME"] == null || reader["FIRETIME"] is DBNull)
{
sb_1.Append("" + PADSTR); //经办时间
}
else
{
sb_1.Append(reader["FIRETIME"].ToString() + PADSTR);
}
sb_1.Append(reader["UNITNAME"].ToString() + PADSTR); //单位
Send_PrintData printData = new Send_PrintData();
printData.command = "hhzm";
printData.data = sb_1.ToString();
Frm_main.socket.sendMsg(Tool.ConvertObjectToJson(printData));
}
else
{
XtraMessageBox.Show("未找到数据!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
reader.Dispose();
oc_command.Dispose();
}
public CargaDados Consultar(string carga)
{
CargaDados objCarga = new CargaDados();
ConexaoOracle objConexao = new ConexaoOracle();
try
{
objConexao.AdicionarParametro("P_CARGA", carga);
objConexao.AdicionarParametroCursor("DADOS");
OracleDataReader leitor = objConexao.ObterLeitor("FUN_PKG_CARGA_DADOS.CONSULTAR");
objCarga.de_para = new List <CargaDadosDePara>();
while (leitor.Read())
{
CargaDadosDePara objDePara = new CargaDadosDePara();
objCarga.id_carga = int.Parse(leitor["ID_CARGA"].ToString());
objCarga.carga = leitor["NM_CARGA"].ToString();
objCarga.titulo = leitor["TITULO"].ToString();
objCarga.carga_extensao = leitor["EXTENSAO"].ToString();
objCarga.pkg_listar = leitor["NM_PKG_LISTAR"].ToString();
objCarga.pkg_deletar = leitor["NM_PKG_DELETAR"].ToString();
if (leitor["ID_CARGA_TIPO"] != null)
{
objCarga.tipo = Convert.ToInt32(leitor["ID_CARGA_TIPO"]);
}
if (!leitor["TABELA_DESTINO"].ToString().Equals(""))
{
objDePara.tabela_destino = leitor["TABELA_DESTINO"].ToString();
objDePara.origem_campo = leitor["ORIGEM_CAMPO"].ToString();
objDePara.destino_campo = leitor["DESTINO_CAMPO"].ToString();
if (leitor["ORIGEM_TIPO"] != null)
{
objDePara.origem_tipo = int.Parse(leitor["ORIGEM_TIPO"].ToString());
}
if (leitor["DESTINO_TIPO"] != null)
{
objDePara.destino_tipo = int.Parse(leitor["DESTINO_TIPO"].ToString());
}
if (leitor["VALOR_PADRAO"] != null)
{
objDePara.destino_valor_padrao = leitor["VALOR_PADRAO"].ToString();
}
if (leitor["NUM_ORDEM"] != null)
{
objDePara.ordem = int.Parse(leitor["NUM_ORDEM"].ToString());
}
objCarga.de_para.Add(objDePara);
}
}
leitor.NextResult();
leitor.Dispose();
}
catch (Exception ex)
{
throw new Exception("Problemas contate o administrador do sistema [CargaDadosDAL.Consultar]: //n" + ex.Message);
}
finally
{
objConexao.Dispose();
}
return(objCarga);
}
/// <summary>
/// 打印 迁出通知单
/// </summary>
/// <param name="rc001"></param>
public static void Print_OutCard(string rc001)
{
StringBuilder sb_1 = new StringBuilder(200);
OracleCommand oc_command = new OracleCommand("select * from v_outcard where rc001 = :rc001", SqlHelper.conn);
OracleParameter op_rc001 = new OracleParameter("rc001", OracleDbType.Varchar2, 10);
op_rc001.Direction = ParameterDirection.Input;
op_rc001.Value = rc001;
oc_command.Parameters.Add(op_rc001);
OracleDataReader reader = oc_command.ExecuteReader();
if (reader.HasRows && reader.Read())
{
sb_1.Append(reader["RC003"].ToString().Trim() + PADSTR); //逝者姓名
sb_1.Append(reader["RC109"].ToString().Trim() + PADSTR); //寄存证号
sb_1.Append(reader["POSITION"].ToString() + PADSTR); //寄存位置
if (reader["RC050"] == null || reader["RC050"] is DBNull)
{
sb_1.Append("" + PADSTR); //家属姓名
}
else
{
sb_1.Append(reader["RC050"].ToString().Trim() + PADSTR);
}
if (reader["RC051"] == null || reader["RC051"] is DBNull)
{
sb_1.Append("" + PADSTR); //联系电话
}
else
{
sb_1.Append(reader["RC051"].ToString().Trim() + PADSTR);
}
if (reader["OC002"] == null || reader["OC002"] is DBNull)
{
sb_1.Append("" + PADSTR); //迁出日期
}
else
{
sb_1.Append(string.Format("{0:yyyy-MM-dd}", reader["OC002"]) + PADSTR);
}
if (reader["RC055"] == null || reader["RC055"] is DBNull)
{
sb_1.Append("" + PADSTR); //联系地址
}
else
{
sb_1.Append(reader["RC055"].ToString().Trim() + PADSTR);
}
if (reader["OC005"] == null || reader["OC005"] is DBNull)
{
sb_1.Append("" + PADSTR); //迁出原因
}
else
{
sb_1.Append(reader["OC005"].ToString().Trim() + PADSTR);
}
Send_PrintData printData = new Send_PrintData();
printData.command = "OutCard";
printData.data = sb_1.ToString();
Frm_main.socket.sendMsg(Tool.ConvertObjectToJson(printData));
}
reader.Dispose();
oc_command.Dispose();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
Session["pozaAngajat"] = null;
Session["postedFile"] = null;
salarBazaTB.Text = 0.ToString();
sporProcentTB.Text = 0.ToString();
premiiBruteTB.Text = 0.ToString();
totalBrutTB.Text = 0.ToString();
casTB.Text = 0.ToString();
cassTB.Text = 0.ToString();
brutImpozabilTB.Text = 0.ToString();
impozitTB.Text = 0.ToString();
retineriTB.Text = 0.ToString();
viratCardTB.Text = 0.ToString();
try
{
con.Open();
string cmd = "select casprocent, cassprocent, impozitprocent from procente where id=1";
oCmd = new OracleCommand(cmd, con);
oDr = oCmd.ExecuteReader();
if (oDr.HasRows)
{
oDr.Read();
cas = Int32.Parse(oDr["casprocent"].ToString().Trim());
cass = Int32.Parse(oDr["cassprocent"].ToString().Trim());
impozit = Int32.Parse(oDr["impozitprocent"].ToString().Trim());
}
oDr.Dispose();
oCmd.Dispose();
LabelPrincipalErr.Text = "";
LabelCAS.Text = "CAS (" + cas + "%)";
LabelCASS.Text = "CASS (" + cass + "%)";
LabelImpozit.Text = "Impozit (" + impozit + "%)";
}
catch (Exception ex)
{
LabelPrincipalErr.ForeColor = Color.Red;
LabelPrincipalErr.Text = "Nu se poate realiza conexiunea la baza de date: " + ex.Message;
}
finally
{
con.Close();
}
}
//If first time page is submitted and we have file in FileUpload control but not in session
// Store the values to SEssion Object
if (Session["pozaAngajat"] == null && pozaAngajat.HasFile != false && Session["postedFile"] == null)
{
var img = Server.MapPath(ClientID + ".jpg");
pozaAngajat.PostedFile.SaveAs(img);
Session["pozaAngajat"] = ClientID + ".jpg";
Session["postedFile"] = pozaAngajat.PostedFile;
pozaAngajatImg.ImageUrl = ClientID + ".jpg";
pozaAngajatImg.Attributes.Add("style", "visibility:visible;");
//pfile = (HttpPostedFile)Session["postedFile"];
//pfile.SaveAs(Server.MapPath("test.jpg"));
//pozaAngajatImg.ImageUrl = "test.jpg";
}
// Next time submit and Session has values but FileUpload is Blank
// Return the values from session to FileUpload
else if (Session["pozaAngajat"] != null && pozaAngajat.HasFile == false && Session["postedFile"] != null)
{
pozaAngajatImg.ImageUrl = Session["pozaAngajat"].ToString();
pozaAngajatImg.Attributes.Add("style", "visibility:visible;");
//pfile = (HttpPostedFile)Session["postedFile"];
//pfile.SaveAs(Server.MapPath("test.jpg"));
//pozaAngajatImg.ImageUrl = "test.jpg";
}
// Now there could be another sictution when Session has File but user want to change the file
// In this case we have to change the file in session object
else if (pozaAngajat.HasFile != false)
{
var img = Server.MapPath(ClientID + ".jpg");
pozaAngajat.PostedFile.SaveAs(img);
Session["pozaAngajat"] = ClientID + ".jpg";
Session["postedFile"] = pozaAngajat.PostedFile;
pozaAngajatImg.ImageUrl = ClientID + ".jpg";
pozaAngajatImg.Attributes.Add("style", "visibility:visible;");
//pfile = (HttpPostedFile)Session["postedFile"];
//pfile.SaveAs(Server.MapPath("test.jpg"));
//pozaAngajatImg.ImageUrl = "test.jpg";
}
}
public static List <List <string> > ToTableList(this OracleDataReader reader)
{
//所有行
var rows = new List <List <string> >();
if (reader.HasRows)
{
while (reader.Read())
{
//逐行添加内容
var cols = new List <string>();
for (var i = 0; i < reader.FieldCount; i++)
{
var value = "";
#region 根据格式取值
var type = reader.GetDataTypeName(i).ToLower();
if (reader.IsDBNull(i))
{
value = "";
}
else if (type == ("smallint"))
{
value = reader.GetInt16(i) + "";
}
else if (type == ("int64"))
{
value = reader.GetInt64(i) + "";
}
else if (type.Contains("int"))
{
value = reader.GetInt32(i) + "";
//
//var v = reader.GetInt32(i);
//if (v > 1000000000)
//{
// value = reader.GetInt32(i).ToDateTime().ToStr();
//}
//else
//{
// value = reader.GetInt32(i) + "";
//}
}
else if (type.Contains("clob"))
{
value = reader.GetOracleClob(i).Value + "";
}
else if (type.Contains("float"))
{
value = reader.GetDouble(i) + "";
}
else if (type.Contains("datetime") || type.Contains("date"))
{
value = reader.GetDateTime(i).ToString("yyyy-MM-dd HH:mm") + "";
}
else if (type.Contains("char") || type.Contains("text") || type.Contains("varchar"))
{
value = reader.GetString(i) + "";
}
else if (type.Contains("decimal"))
{
value = reader.GetOracleDecimal(i) + "";
}
else if (type.Contains("date"))
{
var temp = reader.GetOracleDate(i);
if (!temp.IsNull)
{
value = temp.Value.FormatTime();
}
}
else
{
value = reader.GetString(i) + "";
// Replace("%", "");//2016-09-05 配合报表转义规则 2016-09-10 更新规则
//if (value.Contains("%"))
// throw new Exception("替换不完全！");
}
#endregion
cols.Add(value);
}
rows.Add(cols);
}
}
reader.Close();
reader.Dispose();
return(rows);
}
private void button2_Click(object sender, EventArgs e)
{
string inv = this.textBox1.Text;
string cust = this.textBox3.Text;
string mob = this.textBox14.Text;
string add = this.richTextBox1.Text;
string amount = this.textBox12.Text;
string tax = this.textBox11.Text;
string dis = this.textBox10.Text;
if (string.IsNullOrWhiteSpace(textBox10.Text))
{
dis = "0";
}
string gt = this.textBox13.Text;
/// pinvoice database insert
try
{
conn.Open();
string sqlquery = "INSERT INTO SALESINVOICE(SALE_ID,SDATE,CUST_NAME,CUST_MOBILE,CUST_ADD,AMOUNT,TAX,DISCOUNT,GTOTAL) VALUES('" + inv + "',TO_DATE('" + tdate + "', 'yyyymmdd'),'" + cust + "','" + mob + "','" + add + "','" + amount + "','" + tax + "','" + dis + "','" + gt + "')";
OracleCommand cmd = new OracleCommand(sqlquery, conn);
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
cmd.Dispose();
MessageBox.Show("Done");
}
conn.Close();
}
catch (Exception exe)
{
MessageBox.Show(exe.Message);
}
/////////////////////////////////////////
string StrQuery;
// int j=0;
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
StrQuery = "INSERT INTO SALES_DETAILS(SALE_ID,MED_NAME,UPRICE,QTY,TOTAL) VALUES ('"
+ this.textBox1.Text + "','"
+ dataGridView1.Rows[i].Cells[1].Value + "','"
+ dataGridView1.Rows[i].Cells[2].Value + "','"
+ dataGridView1.Rows[i].Cells[3].Value + "','"
+ dataGridView1.Rows[i].Cells[4].Value + "')";
cmd.CommandText = StrQuery;
cmd.ExecuteNonQuery();
}
//int i = cmd.ExecuteNonQuery();
cmd.Dispose();
MessageBox.Show("Done");
// this.Hide();
conn.Close();
}
catch (Exception exe)
{
MessageBox.Show(exe.Message);
}
///////////////////////////////////////////////
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
string sid = dataGridView1.Rows[i].Cells[1].Value.ToString();
string sqlquery = "SELECT * FROM MED_STORE WHERE MED_NAME='" + sid + "'";
OracleCommand cmd = new OracleCommand(sqlquery, conn);
OracleDataReader r = cmd.ExecuteReader();
r.Read();
// textBox5.Text = r.GetValue(3).ToString();
// MessageBox.Show(r.GetValue(2).ToString());
int qty = int.Parse(r.GetValue(2).ToString()) - int.Parse(dataGridView1.Rows[i].Cells[3].Value.ToString());
r.Dispose();
cmd = new OracleCommand("UPDATE MED_STORE SET MED_QNTY='" + qty + "' WHERE MED_NAME='" + sid + "'", conn);
cmd.ExecuteNonQuery();
}
conn.Close();
}
catch (Exception exe)
{
MessageBox.Show(exe.Message);
}
}
/// <summary>
/// Executes a search for Providers matching the search keyword.
/// </summary>
/// <param name="providerSearchKeyword">Keyword for Provider search.</param>
/// <returns>A collection of matching Providers.</returns>
public List <Provider> GetProviders(string providerSearchKeyword)
{
OracleConnection connection = null;
OracleCommand command = null;
OracleParameter providerKeywordIn = null;
OracleParameter usernameIn = null;
OracleParameter errorMessageOut = null;
OracleParameter resultsOut = null;
OracleDataReader dr = null;
List <Provider> providers = new List <Provider>();
try
{
// Create Oracle connection
connection = new OracleConnection(Utilities.GetDatabaseConnection());
connection.Open();
// Create Oracle command
command = new OracleCommand(Constants.PROVIDER_SEARCH_SP, connection);
command.CommandType = CommandType.StoredProcedure;
// Input parameters
providerKeywordIn = new OracleParameter(
Constants.ProviderSearchParameters.PROVIDER_KEYWORD, OracleDbType.Varchar2,
providerSearchKeyword, ParameterDirection.Input);
command.Parameters.Add(providerKeywordIn);
usernameIn = new OracleParameter(
Constants.ProviderSearchParameters.EMAIL, OracleDbType.Varchar2, "CourseSearchSvc",
ParameterDirection.Input);
command.Parameters.Add(usernameIn);
// Output parameters
errorMessageOut = new OracleParameter(
Constants.ProviderSearchParameters.ERROR_MSG, OracleDbType.Varchar2, ParameterDirection.Output);
command.Parameters.Add(errorMessageOut);
resultsOut = new OracleParameter(
Constants.ProviderSearchParameters.RESULTS_CURSOR, OracleDbType.RefCursor, ParameterDirection.Output);
command.Parameters.Add(resultsOut);
dr = command.ExecuteReader();
// get error message
string errorMessage = null;
if (!((OracleString)(command.Parameters[Constants.ProviderSearchParameters.ERROR_MSG].Value)).IsNull)
{
errorMessage = command.Parameters[Constants.ProviderSearchParameters.ERROR_MSG].Value.ToString();
}
if ((errorMessage == null || errorMessage.Length == 0) && dr.HasRows)
{
while (dr.Read())
{
Provider provider = new Provider();
provider.ProviderId = dr[Constants.ProviderSearchColumns.PROVIDER_ID].ToString();
provider.ProviderName = dr[Constants.ProviderSearchColumns.PROVIDER_NAME].ToString();
provider.AddressLine1 = dr[Constants.ProviderSearchColumns.ADDRESS_LINE1].ToString();
provider.AddressLine2 = dr[Constants.ProviderSearchColumns.ADDRESS_LINE2].ToString();
provider.Town = dr[Constants.ProviderSearchColumns.TOWN].ToString();
provider.County = dr[Constants.ProviderSearchColumns.COUNTY].ToString();
provider.Postcode = dr[Constants.ProviderSearchColumns.POSTCODE].ToString();
provider.Phone = dr[Constants.ProviderSearchColumns.PHONE].ToString();
provider.Email = dr[Constants.ProviderSearchColumns.EMAIL].ToString();
provider.Fax = dr[Constants.ProviderSearchColumns.FAX].ToString();
provider.Website = dr[Constants.ProviderSearchColumns.WEBSITE].ToString();
provider.Ukprn = dr[Constants.ProviderSearchColumns.UKPRN].ToString();
provider.Upin = dr[Constants.ProviderSearchColumns.UPIN].ToString();
provider.TFPlusLoans =
Convert.ToBoolean(dr[Constants.ProviderSearchColumns.TFPLUSLOANS].ToString());
providers.Add(provider);
}
}
else
{
// throw error?
}
}
catch (Exception e)
{
}
finally
{
// clean up after call ...
dr.Dispose();
resultsOut.Dispose();
errorMessageOut.Dispose();
usernameIn.Dispose();
providerKeywordIn.Dispose();
command.Dispose();
connection.Dispose();
}
return(providers);
}
/// <summary>
/// Get Store Name, Indicator
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public List <Dictionary <string, object> > GetExcelConfigData(ExcelXmlModel model)
{
//Dictionary<string, object> dicParas = new Dictionary<string, object>();
//Dictionary<string, object> dicParaOuts = new Dictionary<string, object>();
List <Dictionary <string, object> > lst_store = new List <Dictionary <string, object> >();
List <Dictionary <string, object> > lst_kpi = new List <Dictionary <string, object> >();
List <Dictionary <string, object> > ouput = new List <Dictionary <string, object> >();
try
{
string procedureName = null;
if (model.ExcelXmlCommon.exportDirectory.Equals("PL_CongTy"))
{
procedureName = "pws_getreporsetupcompany_excel";
}
else
{
procedureName = "pws_getreporsetupstore_excel";
}
this.ConnectOracleDB.command.Parameters.Clear();
var op = new OracleParameter("PK_SRPTNBR_para", OracleDbType.Varchar2);
op.Direction = ParameterDirection.Input;
op.Value = model.ExcelXmlCommon.ReportName;
this.ConnectOracleDB.command.Parameters.Add(op);
this.ConnectOracleDB.command.Parameters.Add("p_recordset", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
this.ConnectOracleDB.command.CommandText = procedureName;
this.ConnectOracleDB.command.CommandType = CommandType.StoredProcedure;
OracleDataReader objReader = this.ConnectOracleDB.command.ExecuteReader();
while (objReader.Read())
{
var columns = Enumerable.Range(0, objReader.FieldCount).Select(objReader.GetName).ToList();
Dictionary <string, object> dic = new Dictionary <string, object>();
for (int i = 0; i < columns.Count; i++)
{
foreach (ExcelXMLColumn xmlColumn in model.LstColumn)
{
if (!xmlColumn.IsGetData)
{
continue;
}
if (xmlColumn.Name != columns[i].ToLower())
{
continue;
}
if ((objReader[xmlColumn.Name] != null) && (xmlColumn.IsGetData))
{
dic.Add(xmlColumn.Name, objReader[xmlColumn.Name]);
}
}
}
ouput.Add(dic);
//objReader.NextResult();
}
objReader.Close();
objReader.Dispose();
}
catch (Exception ex)
{
}
finally
{
this.ConnectOracleDB.CloseDBConnect();
}
return(ouput);
}
public List <Dictionary <string, object> > GetStoreDataInventory(ExcelXmlModel model, int month, int year)
{
//Dictionary<string, object> dicParas = new Dictionary<string, object>();
//Dictionary<string, object> dicParaOuts = new Dictionary<string, object>();
List <Dictionary <string, object> > lst_store = new List <Dictionary <string, object> >();
List <Dictionary <string, object> > lst_kpi = new List <Dictionary <string, object> >();
List <Dictionary <string, object> > ouput = new List <Dictionary <string, object> >();
try
{
this.ConnectOracleDB.command.Parameters.Clear();
var time = month.ToString() + "-" + year.ToString().Substring(2, 2);
if (month < 10)
{
time = '0' + time;
}
var dateTime = new OracleParameter("dateTime", OracleDbType.Varchar2);
dateTime.Direction = ParameterDirection.Input;
dateTime.Value = time;
this.ConnectOracleDB.command.Parameters.Add(dateTime);
this.ConnectOracleDB.command.Parameters.Add("p_recordset", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
this.ConnectOracleDB.command.CommandText = "PWS_GETINVENTORYDATA_EXCEL";
this.ConnectOracleDB.command.CommandType = CommandType.StoredProcedure;
OracleDataReader objReader = this.ConnectOracleDB.command.ExecuteReader();
while (objReader.Read())
{
var columns = Enumerable.Range(0, objReader.FieldCount).Select(objReader.GetName).ToList();
Dictionary <string, object> dic = new Dictionary <string, object>();
for (int i = 0; i < columns.Count; i++)
{
foreach (ExcelXMLColumn xmlColumn in model.LstColumn)
{
if (!xmlColumn.IsGetData)
{
continue;
}
if (xmlColumn.Name.ToLower() != columns[i].ToLower())
{
continue;
}
if ((objReader[xmlColumn.Name] != null) && (xmlColumn.IsGetData))
{
dic.Add(xmlColumn.Name, objReader[xmlColumn.Name]);
}
}
}
ouput.Add(dic);
//objReader.NextResult();
}
objReader.Close();
objReader.Dispose();
}
catch (Exception ex)
{
}
finally
{
this.ConnectOracleDB.CloseDBConnect();
}
return(ouput);
}
/// <summary>
/// 读取发票信息
/// </summary>
private void ReadInvoiceBaseInfo()
{
OracleDataReader reader = SqlAssist.ExecuteReader("select * from sp01 where sp001 < '0000000100' ");
if (reader.HasRows)
{
while (reader.Read())
{
if (reader["SP002"].ToString() == "tax_no") //纳税人识别号
{
Envior.TAX_ID = reader["SP005"].ToString();
}
else if (reader["SP002"].ToString() == "tax_addrtele") //税票-销方地址电话
{
Envior.TAX_ADDR_TELE = reader["SP005"].ToString();
}
else if (reader["SP002"].ToString() == "tax_bankaccount") //税票-销方银行账号
{
Envior.TAX_BANK_ACCOUNT = reader["SP005"].ToString();
}
else if (reader["SP002"].ToString() == "tax_appid") //税票-应用账号
{
Envior.TAX_APPID = reader["SP005"].ToString();
}
else if (reader["SP002"].ToString() == "tax_invoicetype") //税票-发票类型
{
Envior.TAX_INVOICE_TYPE = reader["SP005"].ToString();
}
else if (reader["SP002"].ToString() == "tax_server_url") //税票-服务请求URL
{
Envior.TAX_SERVER_URL = reader["SP005"].ToString();
}
else if (reader["SP002"].ToString() == "tax_publickey") //税票-公钥
{
Envior.TAX_PUBLIC_KEY = reader["SP005"].ToString();
}
else if (reader["SP002"].ToString() == "tax_privatekey") //税票-私钥
{
Envior.TAX_PRIVATE_KEY = reader["SP005"].ToString();
}
else if (reader["SP002"].ToString() == "tax_cashier") //税票-收款人
{
Envior.TAX_CASHIER = reader["SP005"].ToString();
}
else if (reader["SP002"].ToString() == "tax_checker") //税票-复核人
{
Envior.TAX_CHECKER = reader["SP005"].ToString();
}
else if (reader["SP002"].ToString() == "tax_max_fee")
{
decimal dec_max;
if (decimal.TryParse(reader["SP006"].ToString(), out dec_max))
{
Envior.TAX_MAX_FEE = dec_max;
}
else
{
Envior.TAX_MAX_FEE = 0;
}
}
}
}
reader.Dispose();
}
