public object ExecuteScale(string strSql, out string strErrMsg)
{
strErrMsg = null;
try
{
if (this._cmd == null)
{
return(null);
}
if (this._conn == null || this._conn.State == ConnectionState.Closed)
{
this.OpenConnection();
}
_cmd.CommandText = strSql;
return(_cmd.ExecuteOracleScalar());
}
catch (Exception ex)
{
strErrMsg = "获取数据库数据时发生错误！请尝试再次操作，或者重新刷新页面。若仍存在错误，请与管理员联系。详细信息：" + ex.Message;
log.ErrorFormat("oracle数据错误:{0}", ex.Message);
return(null);
}
finally
{
this.CloseConnection();
}
}
/// <summary>
/// 获取某段时间内所在的科室
/// </summary>
/// <param name="patNoofHis"></param>
/// <param name="starttime"></param>
/// <param name="endtime"></param>
/// <returns></returns>
public static string GetChangedDeptName(string patNoofHis, DateTime starttime, DateTime endtime, PatientInfo patientInfo)
{
string returnValue = string.Empty;
using (OracleConnection conn = new OracleConnection(DataAccessFactory.GetSqlDataAccess("HISDB").GetDbConnection().ConnectionString))
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
OracleCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(@"select OLD_DATA_NAME from changeddeptrecord where INPATIENT_NO='{0}' and OPER_DTIME >= '{1}' and OPER_DTIME <= '{2}' order by OPER_DTIME", patNoofHis, starttime.ToString("yyyy-MM-dd 00:00:00"), endtime.ToString("yyyy-MM-dd 23:59:59"));
try
{
Object obj = cmd.ExecuteOracleScalar();
if (null != obj)
{
returnValue = obj.ToString();
}
else
{
cmd.CommandText = string.Format(@"select new_data_name from changeddeptrecord where INPATIENT_NO='{0}' and OPER_DTIME < '{1}' order by OPER_DTIME desc", patNoofHis, starttime.ToString("yyyy-MM-dd 00:00:00"));
Object ob = cmd.ExecuteOracleScalar();
if (null != ob)
{
returnValue = ob.ToString();
}
else
{
cmd.CommandText = string.Format(@"select OLD_DATA_NAME from changeddeptrecord where INPATIENT_NO='{0}' and OPER_DTIME > '{1}' order by OPER_DTIME asc", patNoofHis, endtime.ToString("yyyy-MM-dd 23:59:59"));
Object oj = cmd.ExecuteOracleScalar();
if (null != oj)
{
returnValue = oj.ToString();
}
}
}
if (string.IsNullOrEmpty(returnValue))
{
returnValue = patientInfo.Section;
}
}
catch (Exception ex)
{
m_app.CustomMessageBox.MessageShow("调用HIS接口报错！" + ex.Message, CustomMessageBoxKind.ErrorOk);
}
}
return(returnValue);
}
/// <summary>
/// This function is used to execute select queires
/// </summary>
/// <param name="pQuery"></param>
/// <returns></returns>
public string FetchDataFromDBUsingExecuteScaler(string pQuery)
{
OracleConnection myConnection;
OracleCommand myCommand = null;
string lResult = string.Empty;
myConnection = new OracleConnection(ReadConfigFile("ConnectionString"));
try
{
myConnection.Open();
myCommand = new OracleCommand(pQuery, myConnection);
lResult = myCommand.ExecuteOracleScalar().ToString();
return(lResult);
}
catch (Exception ex)
{
return(string.Empty);
}
finally
{
if (myCommand != null)
{
myCommand.Connection.Close();
myCommand.Dispose();
}
}
}
/// <summary>
/// 获取NR标识
/// </summary>
/// <param name="dateString">日期yyyymmdd</param>
/// <param name="oraId">数据库名</param>
/// <param name="username"></param>
/// <param name="password"></param>
/// <returns></returns>
public string NrStatusCheck(string dateString, string oraId, string username, string password)
{
string oraStr = string.Format("Persist Security Info=False;Data Source={0};User ID={1};password={2}", oraId, username, password);
using (OracleConnection oraConn = new OracleConnection(oraStr))
{
string result = "n/a";
try
{
oraConn.Open();
string queryString = string.Format("select flag from nr_status where run_date=to_date('{0}','yyyymmdd')", dateString);
OracleCommand oraComm = new OracleCommand(queryString, oraConn);
var rd = oraComm.ExecuteOracleScalar();
result = rd.ToString();
//oraComm.Dispose();
//oraConn.Close();
}
catch (Exception ex)
{
result = oraId + " ";
result += ex.Message.ToString();
if ((ex.ToString()).IndexOf("NullReferenceException") != -1)
{
result = "no row selected";
}
if ((ex.ToString()).IndexOf("ORA-12535") != -1)
{
result = "TNS超时";
}
//throw;
}
return(result);
}
}
public Boolean BuscarAlumno(int clave)
{
try
{
conexion = new OracleConnection(cadena);
conexion.Open();
cmd = new OracleCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "Select * from Admin where Id= " + clave + "";
cmd.Connection = conexion;
resultado = new object();
resultado = cmd.ExecuteOracleScalar();
conexion.Close();
LlenarGridView();
if (resultado != null)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
Response.Write("" + ex.Message);
return(true);
}
}
public string ExecuteSQL(string sql)
{
string user_lang = Session["SESSION_LANG"].ToString();
string dbpass = Session["Ctl"].ToString();
string TCO_BSUSER_PK = Session["USER_PK"].ToString();
string sqlResult = "";
//$1 TCO_BSUSER_PK parameter
//$2 user language(ENG,VIE,KOR)
string queryString = sql.Replace("$1", TCO_BSUSER_PK);
queryString = sql.Replace("$2", user_lang);
try
{
string conString = ConfigurationManager.AppSettings["strConn"];
conString += "User id=Ctl;Password="******":" + queryString);
}
return(sqlResult);
}
public static object Scalar(string sql)
{
log.Debug(string.Format("\t{0}", sql));
sql = Regex.Replace(sql, @"[\n\r]", " ");
object result = null;
OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["oracle"].ConnectionString);
try
{
conn.Open();
OracleCommand cmd = new OracleCommand(sql, conn);
result = cmd.ExecuteOracleScalar();
}
catch (System.Exception ex)
{
log.Debug(string.Format("\t{0}", ex.ToString()));
throw ex;
}
finally
{
conn.Close();
}
return(result);
}
///<summary>
///连接数据库并执行SQL语句获得最大行数
///</summary>
public static object GetScalar(string sql_str, string con_str)
{
OracleConnection conn = new OracleConnection(con_str);
object r = null;
try
{
//OracleConnection conn = new OracleConnection(con_str);
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = sql_str;
r = cmd.ExecuteOracleScalar();
cmd.Dispose();
//conn.Close();
return(r);
}
catch (OracleException ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
conn.Close();
}
return(r);
}
/// <summary>
/// 用于测试数据库连接是否有效
/// </summary>
/// <returns></returns>
public static bool ExecuteTest()
{
bool bln = false;
try
{
using (OracleConnection con = new OracleConnection(DbConnectStr))
{
using (OracleCommand cmd = new OracleCommand("SELECT SYSDATE FROM DUAL", con))
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
cmd.CommandType = CommandType.Text;
object obj = cmd.ExecuteOracleScalar();
if (obj != null && obj != DBNull.Value)
{
bln = true;
}
}
}
}
catch (Exception e)
{
bln = false;
LogUtility.DataLog.WriteLog(LogLevel.Info, e.Message, new RunningPlace("OracleHelper", "ExecuteTest"), "DBErr");
}
return(bln);
}
//保存
protected void btnSaveData_Click(object sender, EventArgs e)
{
string strDate = ddlYear.Text.ToString() + "-" + ddlMonth.Text.ToString();
OracleConnection conn = DB.createConn();
conn.Open();
string strSQL = "select count(*) From TP_PLANT_DATA_M where LOGTIME = '" + strDate + "'";
OracleCommand cmd = new OracleCommand(strSQL, conn);
int iResult = Convert.ToInt32(cmd.ExecuteOracleScalar().ToString());
if (iResult > 0)
{
}
strSQL = "Delete From TP_PLANT_DATA_M where LOGTIME = '" + strDate + "'";
cmd = new OracleCommand(strSQL, conn);
cmd.ExecuteNonQuery();
strSQL = "insert into TP_PLANT_DATA_M(LOGTIME,F_STEEL,P_STEEL,J_STEEL,GREATETIME) "
+ "values ('" + strDate + "'," + txtFSteel.Text + "," + txtPSteel.Text + ","
+ txtJSteel.Text + ",'" + DateTime.Now.ToString() + "')";
cmd = new OracleCommand(strSQL, conn);
int rsCount = Convert.ToInt32(cmd.ExecuteNonQuery().ToString());
if (rsCount > 0)
{
this.Page.Visible = false;
}
else
{
Response.Write("保存失败!");
}
conn.Close();
}
/// <summary>
/// 创建NC预定单号
/// </summary>
/// <param name="cmd"></param>
/// <returns>判断当前物料是否已经存在于客商的填报计划当中</returns>
protected string createNCYDBH(OracleCommand cmd, string ydrq)
{
string strSql = "select count(*)+1 sl from tmp_nc_xsjh t where t.DBILLDATE='" + ydrq + "' and t.pk_corp='" + DplTHGC.Items[DplTHGC.SelectedIndex].Value + "' ";
cmd.CommandText = strSql;
return("JH" + ydrq.Replace("-", "") + cmd.ExecuteOracleScalar().ToString().PadLeft(4, '0'));
}
private void killSessionButton_Click(object sender, EventArgs e)
{
string sidStr = "", sidCount = "", msgError = "", msgSucc = "";
int returnCount = 0;
try
{
if (this.killSessionTextBox.Text == "")
{
MessageBox.Show("请输入会话ID");
}
else
{
OracleConnection conn = new OracleConnection(this.killSessionDsnTextBox.Text);
conn.Open();
foreach (string linestr in killSessionTextBox.Lines)
{
sidStr = linestr.Substring(0, linestr.IndexOf(","));
OracleCommand connCount = conn.CreateCommand();
connCount.CommandText = "select count(*) from v$session a where a.sid = '" + sidStr + "'";
sidCount = Convert.ToString(connCount.ExecuteOracleScalar()).Trim();
if (sidCount == "" || sidCount == "0")
{
MessageBox.Show("没有找到用户会话或用户已退出");
}
else if (sidCount == "1")
{
OracleCommand killSql = conn.CreateCommand();
killSql.CommandText = "alter system kill session '" + this.killSessionTextBox.Text + "'";
returnCount = killSql.ExecuteNonQuery();
if (returnCount == 0)
{
msgSucc += linestr + " KILL用户会话成功" + "\r\n";
//MessageBox.Show("KILL用户会话成功");
}
else
{
msgError += linestr + " KILL用户会话失败" + "\r\n";
//MessageBox.Show("KILL用户会话失败");
}
}
}
conn.Close();
if (msgSucc.Length > 0)
{
MessageBox.Show(msgSucc);
}
if (msgError.Length > 0)
{
MessageBox.Show(msgError);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static string GetReturnValue(string str, string sql)
{
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);
conn.Open();
OracleCommand command = conn.CreateCommand();
command.CommandText = sql + str;
string value = command.ExecuteOracleScalar().ToString();
conn.Close();
return(value);
}
/// <summary>
/// 创建销售计划表体ID
/// </summary>
/// <param name="cmd"></param>
/// <returns>判断当前物料是否已经存在于客商的填报计划当中</returns>
protected string createPK_XSJH_B(OracleCommand cmd, string chglda, string ydrq)
{
//string strSql = "select b.FREE1 from tmp_nc_xsjh t,tmp_nc_xsjh_b b where t.PK_TMP_NC_XSJH=b.PK_TMP_NC_XSJH and b.cinventoryid='" + chglda + "' and b.dr='0' and t.dr='0' and b.blargessflag='N' and t.DBILLDATE='" + ydrq + "' and t.pk_corp='" + log_corp + "' and t.FREE1='" + log_user + "' ";
//cmd.CommandText = strSql;
//object objPK_TMP_NC_XSJH = cmd.ExecuteOracleScalar();
//if (objPK_TMP_NC_XSJH == null)
//{
cmd.CommandText = "select tmp_nc_xsjh_b_seq.NextVal from dual ";
object objPK_TMP_NC_XSJH = cmd.ExecuteOracleScalar();
return(objPK_TMP_NC_XSJH.ToString());
//}
//else {
// return "exist"+objPK_TMP_NC_XSJH.ToString();
//}
}
/// <summary>
/// 执行命令，返回一个值
/// </summary>
/// <param name="commandText"></param>
/// <returns></returns>
public override object ExecuteScalar(string commandText)
{
OracleCommand command = new OracleCommand(commandText, this._connection, this._transaction);
object v = null;
try
{
v = command.ExecuteOracleScalar();
}
finally
{
command.Dispose();
command = null;
}
return(v);
}
/// <summary>
/// 返回第一行的第一列
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public object ExecScalar(string strSql)
{
try
{
using (var conn = new OracleConnection(Connstr))
{
conn.Open();
var cmd = new OracleCommand(strSql, conn);
var obj = cmd.ExecuteOracleScalar();
conn.Close();
return(obj);
}
}
catch
{
return(null);
}
}
public string ExecuteProcedureParam1(string Proc, OracleParameter[] param, string varOut, string result)
{
con = new OracleConnection(strOracleConn);
OracleCommand cmd = new OracleCommand();
try
{
con.Open();
if (con.State == ConnectionState.Open)
{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(param);
cmd.CommandText = Proc;
string str = Convert.ToString(cmd.ExecuteOracleScalar());
if (cmd.Parameters[result].Value.ToString() != "")
{
return(cmd.Parameters[result].Value.ToString());
}
else
{
return(string.Empty);
}
}
else
{
throw new Exception("database connection not found");
}
}
catch (Exception ex)
{
throw new Exception(ex.ToString() + "\n\n" + "Query:" + "\n" + Proc);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
cmd = null;
con = null;
}
}
/// <summary>
/// 带参数
/// </summary>
/// <param name="pDbConn"></param>
/// <param name="sSql"></param>
/// <param name="pParams"></param>
/// <returns></returns>
public object ExecuteOracleScalar(string sSql, OracleParameter[] opc)
{
try
{
pCommand.CommandText = sSql;
pCommand.Parameters.Clear();
if (opc != null)
{
for (int i = 0; i < opc.Length; i++)
{
pCommand.Parameters.Add(opc[i]);
}
}
return(pCommand.ExecuteOracleScalar());
}
catch (Exception ep)
{
LogBN.WriteLog(typeof(DbAPI), "ExecuteOracleScalar 程序段的异常" + ep + sSql);
return(-1);
}
}
protected void Delete_Command(object sender, CommandEventArgs e)
{
string strId = e.CommandArgument.ToString().Trim();
OracleConnection ConnectionStringJXC = new OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionStringJXC"].ConnectionString);
ConnectionStringJXC.Open(); //打开链接
OracleCommand cmd = new OracleCommand(); //创建执行类
cmd.Connection = ConnectionStringJXC;
OracleTransaction tx = ConnectionStringJXC.BeginTransaction();
cmd.Transaction = tx;//开启链接事务
try
{
cmd.CommandText = "update tmp_nc_xsjh_b set dr='1' where PK_TMP_NC_XSJH_B='" + strId + "'";
cmd.ExecuteNonQuery();
cmd.CommandText = "select t.PK_TMP_NC_XSJH from tmp_nc_xsjh_b t where t.dr=0 and t.PK_TMP_NC_XSJH in";
cmd.CommandText += "(select b.PK_TMP_NC_XSJH from tmp_nc_xsjh_b b where b.PK_TMP_NC_XSJH_B='" + strId + "') ";
//OracleDataAdapter cmdSQL = new OracleDataAdapter(strSql.ToString(), ConnectionStringJXC);
object ret = cmd.ExecuteOracleScalar();
if (ret == null)//如果计划表体已经删光则删除表体
{
cmd.CommandText = "update tmp_nc_xsjh t set t.dr='1' where t.PK_TMP_NC_XSJH in (select b.PK_TMP_NC_XSJH from tmp_nc_xsjh_b b where b.PK_TMP_NC_XSJH_B='" + strId + "' )";
cmd.ExecuteNonQuery();
}
}
catch (System.Data.OracleClient.OracleException E)
{
tx.Rollback();
ConnectionStringJXC.Close();
MessageBox.ShowAndRedirect(this, "删除销售计划信息失败，请联系系统管理员！", "SalePlanList.aspx");
BindPlanDataList();
}
tx.Commit();
ConnectionStringJXC.Close();
BindPlanDataList();
// MessageBox.ShowAndRedirect(this, "删除成功！", "SalePlanList.aspx");
}
public static string GetItemName(string pInventoryItemID)
{
string strReturn = "";
using (OracleConnection cn = new OracleConnection(clsSystemConfigurations.ConnectionStringOracle))
{
using (OracleCommand cmd = cn.CreateCommand())
{
cmd.CommandText = "SELECT DESCRIPTION FROM INV.MTL_SYSTEM_ITEMS_B WHERE INVENTORY_ITEM_ID=:INVENTORY_ITEM_ID AND ORGANIZATION_ID = '120'";
cmd.Parameters.Add(new OracleParameter("INVENTORY_ITEM_ID", pInventoryItemID));
cn.Open();
try
{
strReturn = cmd.ExecuteOracleScalar().ToString();
}
catch
{
strReturn = "None";
}
}
}
return(strReturn);
}
//public string readoraclereader(OracleCommand cmd)
//{
// try
// {
// strErrorMsg = "";
// OracleCommand cmd1 = new OracleCommand();
// System.Data.OracleClient.OracleDataReader oraReader;
// System.Data.OracleClient.OracleLob myClob;
// cmd1 = cmd;
// OpenConnection();
// cmd.Connection = oraConn;
// oraReader = cmd1.ExecuteReader();
// myClob = cmd1.Parameters["status"].Value;
// return myClob.Value.ToString;
// }
// catch (Exception ex)
// {
// return ex.Message.ToString();
// }
//}
/// <summary>
/// Execute the oracle command and return a single value.
///
/// </summary>
/// <param name="cmd">Oracle command to be executed.</param>
/// <returns></returns>
/// <remarks></remarks>
public bool ProcedureExecutescaler(OracleCommand cmd)
{
try
{
strErrorMsg = "";
OpenConnection();
cmd.Connection = oraConn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteOracleScalar();
return(true);
}
catch (Exception ex)
{
strErrorMsg = ex.Message.ToString();
return(false);
}
finally
{
cmd.Dispose();
oraConn.Close();
oraConn.Dispose();
}
}
public string ExecuteScalar(string sql, OracleParameterCollection opc)
{
string sRet = "";
if (null == _conn)
{
_conn = GetDBConnection();
}
try
{
OracleCommand cmd = new OracleCommand(sql, _conn);
foreach (OracleParameter op in opc)
{
cmd.Parameters.Add(op.ParameterName, op.OracleType, op.Size).Value = op.Value;
}
if (null != _st)
{
cmd.Transaction = _st;
}
sRet = cmd.ExecuteOracleScalar().ToString();
}
catch
{
throw;
}
finally
{
if (null == _st)
{
_conn.Close();
_conn.Dispose();
_conn = null;
}
}
return(sRet);
}
static void Main(string[] args)
{
StreamWriter
fstr_out = null;
OracleConnection
conn = null;
OracleCommand
cmd=null;
OracleDataAdapter
da=null;
OracleDataReader
rdr = null;
OracleTransaction
tr = null;
DataTable
tmpDataTable = null;
object
tmpObject;
string
FieldName,
tmpString;
long
tmpLong;
int
tmpInt,
i;
bool
tmpBool;
DateTime
tmpDateTime;
try
{
try
{
fstr_out = new StreamWriter("log.log", false, System.Text.Encoding.GetEncoding(1251));
fstr_out.AutoFlush = true;
string
ConnectionString = "User ID=corp_yk_user;Password=google;Data Source=sm";
//ConnectionString = "User ID=aspnetuser;Password=aspnet;Data Source=ASM";
//ConnectionString = "User ID=sqlpretension;Password=impression;Data Source=SM";
conn = new OracleConnection(ConnectionString);
#if TEST_DBMS_OUTPUT
conn.StateChange += new StateChangeEventHandler(conn_StateChange);
conn.InfoMessage += new OracleInfoMessageEventHandler(conn_InfoMessage);
#endif
conn.Open();
#if TEST_NULL
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from testtabletypes";
rdr=cmd.ExecuteReader();
do
{
if (rdr.HasRows)
{
int
idx = rdr.GetOrdinal(FieldName = "FINT");
while (rdr.Read())
{
tmpInt = !rdr.IsDBNull(idx) ? Convert.ToInt32(rdr[idx]) : int.MinValue;
tmpInt = rdr[idx] != DBNull.Value ? Convert.ToInt32(rdr[idx]) : int.MaxValue;
tmpInt = !rdr[idx].Equals(DBNull.Value) ? Convert.ToInt32(rdr[idx]) : int.MinValue;
tmpInt = !Convert.IsDBNull(rdr[idx]) ? Convert.ToInt32(rdr[idx]) : int.MaxValue;
}
}
} while (rdr.NextResult());
rdr.Close();
if (da == null)
da = new OracleDataAdapter();
da.SelectCommand = cmd;
if (tmpDataTable == null)
tmpDataTable = new DataTable();
else
tmpDataTable.Reset();
da.Fill(tmpDataTable);
foreach (DataRow r in tmpDataTable.Rows)
{
tmpInt = !r.IsNull(FieldName = "FINT") ? Convert.ToInt32(r[FieldName]) : int.MinValue;
tmpInt = r[FieldName] != DBNull.Value ? Convert.ToInt32(r[FieldName]) : int.MaxValue;
tmpInt = !r[FieldName].Equals(DBNull.Value) ? Convert.ToInt32(r[FieldName]) : int.MinValue;
tmpInt = !Convert.IsDBNull(r[FieldName]) ? Convert.ToInt32(r[FieldName]) : int.MaxValue;
}
#endif
#if TEST_NLS_NUMERIC_CHARACTERS
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select cast('0,123' as number(10,6)) from dual";
tmpObject = cmd.ExecuteScalar();
#endif
#if CAST_VS_CONVERT
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select count(*) from dual";
try
{
tmpInt = (int)cmd.ExecuteScalar();
}
catch (Exception eException)
{
Console.WriteLine(eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace);
}
tmpObject = cmd.ExecuteScalar(); // decimal
tmpInt = Convert.ToInt32(tmpObject);
tmpObject = cmd.ExecuteOracleScalar(); // OracleNumber
OracleNumber
tmpOracleNumber = (OracleNumber)tmpObject;
#endif
#if TEST_TYPES
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
tmpDateTime = DateTime.Now;
cmd.CommandText = @"
update
testtabletypes
set
ftimestamp=:ftimestamp_in,
ftimestamp_w_tz=:ftimestamp_w_tz_in,
ftimestamp_w_l_tz=:ftimestamp_w_l_tz_in
";
cmd.Parameters.Clear();
cmd.Parameters.Add("ftimestamp_in", OracleType.Timestamp).Value = tmpDateTime;
cmd.Parameters.Add("ftimestamp_w_tz_in", OracleType.TimestampWithTZ).Value = tmpDateTime;
cmd.Parameters.Add("ftimestamp_w_l_tz_in", OracleType.TimestampLocal).Value = tmpDateTime;
tmpInt=cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.CommandText = "select * from TestTableTypes";
if (da == null)
da = new OracleDataAdapter();
da.SelectCommand = cmd;
if (tmpDataTable == null)
tmpDataTable = new DataTable();
else
tmpDataTable.Reset();
da.Fill(tmpDataTable);
if (tmpDataTable.Rows.Count > 0)
{
tmpObject = tmpDataTable.Rows[0]["FNumber_Asterisk_0"];
if(!Convert.IsDBNull(tmpObject)) // System.InvalidCastException: Object cannot be cast from DBNull to other types.
tmpBool = Convert.ToBoolean(tmpObject);
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "UpdateTestTableTypes";
OracleCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["ID_IN"].Value = 1;
//cmd.Parameters["FCLOB_IN"].Value = new string('я', 2001); //"CLOB";
//cmd.Parameters["FNCLOB_IN"].Value = new string('я', 2001); //"NCLOB";
//cmd.Parameters["FSMALLINT_IN"].Value = false;
//cmd.Parameters["FINT_IN"].Value = Enum4TestTypes.Second;
cmd.ExecuteNonQuery();
#endif
#if TEST_DBMS_OUTPUT
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "TestProcedureWithDBMSOutput";
OracleCommandBuilder.DeriveParameters(cmd);
cmd.ExecuteNonQuery();
#endif
#if TEST_CLOB
if (cmd == null)
cmd = conn.CreateCommand();
//tmpString = "<P style=\"FONT-SIZE: large\" align=center>Спасибо, что пользуетесь нашей системой заказов!</P><P style=\"FONT-SIZE: large\" align=center>С 3 декабря изменился интерфейс \"онлайн заказов\" для клиентов компании.</P><P style=\"FONT-SIZE: large\" align=center>Просьба о всех пожеланиях и возможных проблемах сообщать на эл. адрес <A href=\"mailto:[email protected]\">[email protected]</A></P><P>Для создания заказа воспользуйтесь пунктом меню \"Каталог товаров\", либо загрузите заказ из <IMG src=\"http://corp.mtr.com.ua/OrdersApp/images/excel.gif\"> Excel через меню \"Загрузить заказ\"</P><P>Более подробно о том, как делать заказы и пользоваться системой вы можете почитать в <A href=\"http://corp.mtr.com.ua/OrdersApp/client/manual/help%20b2b.doc\">документации</A>.</P><P align=center><B>Уважаемые партнёры:</B></P><P align=center>Не упустите возможность закупить продукцию «Медиа Трейдинг» по очень хорошим ценам.</P><P align=center>Только раз в неделю, каждую пятницу, мы предлагаем специальные цены на несколько позиций из своего ассортимента. Размер партии может быть любой. Условие только одно: <B>Полная оплата закупки в тот же день*.</B><BR><BR>Способ оплаты может быть любой.<BR><SPAN style=\"COLOR: rgb(255,0,0); FONT-WEIGHT: bold\">ВНИМАНИЕ: АССОРТИМЕНТ ТОВАРОВ ОБНОВЛЯЕТСЯ КАЖДЫЙ ЧЕТВЕРГ В 16:00.</SPAN><BR></P><P><B>*Пояснение к условию:</B><BR>Вы можете получить товар по объявленной цене при условии оплаты в этот же день.<BR>Это значит, что нужно <B>«принести» (любым способом: безнал, предоплата) деньги в офис или представительство компании с 9:00 до 17:00 пятницы.</B> При этом <B>у Вас не должно быть вообще никаких задолженностей перед компанией, <U>независимо от размеров и срока давности</U>. Баланс либо нулевой, либо плюс.</B><BR><B><U>Без подтверждения оплаты товар отгружен не будет!</U></B><BR><BR>В случае, если товара нет на складе представительства, заказ будет доставлен с центрального склада ближайшей отгрузкой.</P><p align=\"center\"><b><br></b></p><p align=\"center\"><b><br></b></p><p align=\"center\"><b><br></b></p>";
//tmpString = new string('я', 2000);
tmpString = new string('я', 2001);
//tmpString = new string('я', 3000);
//tmpString = new string('я', 3999);
//tmpString = new string('я', 4000);
//tmpString = new string('я', 4001);
//tmpString = new string('я', 0x0ffff);
cmd.CommandType = CommandType.Text;
cmd.CommandText = "update TestTableTypes set FClob = :FClob, FNClob = :FNClob";
//cmd.Parameters.Add(":FClob", OracleType.Clob).Value = tmpString;
//cmd.Parameters.Add(":FNClob", OracleType.NClob).Value = tmpString;
//cmd.Parameters.AddWithValue(":FClob", tmpString);
//cmd.Parameters.AddWithValue(":FNClob", tmpString);
OracleParameter
p;
p = new OracleParameter();
p.ParameterName = ":FClob";
p.DbType = DbType.String;
p.Value = tmpString;
cmd.Parameters.Add(p);
p = new OracleParameter();
p.ParameterName = ":FNClob";
p.DbType = DbType.String;
p.Value = tmpString;
cmd.Parameters.Add(p);
tmpInt=cmd.ExecuteNonQuery();
#endif
#if TEST_EXECUTE_SCALAR
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select count(*) from user_objects";
tmpObject = cmd.ExecuteScalar();
// tmpInt=(int)tmpObject; // Wrong!!!
tmpInt = Convert.ToInt32(tmpObject);
#endif
#if TEST_FUNCTION
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "get_actions_count";
OracleCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["application_id_in"].Value = 128;
cmd.Parameters["date_actual"].Value = new DateTime(1900, 1, 1);
tmpObject = cmd.ExecuteNonQuery();
tmpInt = Convert.ToInt32(cmd.Parameters["return_value"].Value);
cmd.CommandText = "get_zonder_duration";
OracleCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["volume_in"].Value = 0.1;
tmpObject = cmd.ExecuteNonQuery();
tmpInt = Convert.ToInt32(cmd.Parameters["return_value"].Value);
#endif
#if GET_STORED_PROCEDURE_PARAMETERS
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "typhoon.utilfunctions.updategextra"; // "typhoon.Utilization_Util.ImportUtilization"; //"typhoon.pkg_claim.get_doc_status"; // "typhoon.movegoodstoresrevforinetf"; //"typhoon.setdocpropvalue"; // "typhoon.GET_URL_OPENDOC"; //"typhoon.HO_Utils.makeDocument";
OracleCommandBuilder.DeriveParameters(cmd);
Console.WriteLine(cmd.CommandText);
for (int ii = 0; ii < cmd.Parameters.Count; ++ii)
Console.WriteLine("ParameterName: " + cmd.Parameters[ii].ParameterName + " OracleType: " + cmd.Parameters[ii].OracleType.ToString() + " Direction: " + cmd.Parameters[ii].Direction.ToString());
Console.ReadLine();
#endif
#if TEST_SEND_E_MAIL
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "typhoon.mail_task_util.sendmail"; //"typhoon.mail_task_util.sendmail2";
OracleCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["M_SENDER"].Value = "*****@*****.**"; //"*****@*****.**"; // "*****@*****.**"
cmd.Parameters["M_RECIPIENT"].Value = "[email protected]; [email protected]"; // "*****@*****.**";
cmd.Parameters["M_SUBJECT"].Value = "Тест e-mail посредством оракула";
cmd.Parameters["M_MESSAGE"].Value = "<p><center><img src=\"http://b2b.mtr.com.ua/images/logo.gif\" /></center></p><p><center><b>Дистрибуция Медиа Продукции</b></center></p><p>Тест e-mail посредством оракула <a href=\"http://google.com/\">google.com</a></p>";
//cmd.Parameters["M_CC_RECIPIENT"].Value = DBNull.Value;
//cmd.Parameters["M_BCC_RECIPIENT"].Value = DBNull.Value;
cmd.Parameters["M_ADDRDELIM"].Value = ";";
cmd.ExecuteNonQuery();
#endif
#if TEST_STORED_PROCEDURE_IN_CIRCLE
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "TableWithSequenceSave";
OracleCommandBuilder.DeriveParameters(cmd);
for (i = 0; i < 10; ++i)
{
cmd.Parameters["id_cur"].Value = 0;
cmd.Parameters["val_new"].Value = i;
cmd.ExecuteNonQuery();
tmpInt = Convert.ToInt32(cmd.Parameters["id_new"].Value);
}
#endif
#if TEST_BLOB
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
FileStream
fs;
byte[]
Blob;
#if TEST_BLOB_SAVE
cmd.CommandText = "update TableWithBLOB set FBlob=:FBlob where Id=:Id";
cmd.Parameters.Clear();
cmd.Parameters.Add("Id", OracleType.Number).Value = 1;
cmd.Parameters.Add("FBlob", OracleType.Blob);
fs = new FileStream("xpfirewall.bmp", FileMode.Open, FileAccess.Read);
Blob = new byte[fs.Length];
fs.Read(Blob, 0, Blob.Length);
fs.Close();
cmd.Parameters["FBlob"].Value = Blob;
tmpInt = cmd.ExecuteNonQuery();
#endif
#if TEST_BLOB_SAVE_BY_SP
// http://support.microsoft.com/default.aspx?scid=kb;en-us;322796
fs = new FileStream("xpfirewall.bmp", FileMode.Open, FileAccess.Read);
Blob = new byte[fs.Length];
fs.Read(Blob, 0, Blob.Length);
fs.Close();
cmd.Transaction = conn.BeginTransaction();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;";
cmd.Parameters.Clear();
cmd.Parameters.Add(new OracleParameter("tempblob", OracleType.Blob)).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
OracleLob
tmpOracleLob;
tmpOracleLob = (OracleLob)cmd.Parameters["tempblob"].Value;
tmpOracleLob.BeginBatch(OracleLobOpenMode.ReadWrite);
tmpOracleLob.Write(Blob, 0, Blob.Length);
tmpOracleLob.EndBatch();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "TableWithBLOBSave";
OracleCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["ID_IN"].Value = 2;
cmd.Parameters["FBLOB_IN"].Value = tmpOracleLob;
tmpInt = cmd.ExecuteNonQuery();
cmd.Transaction.Commit();
cmd.CommandType = CommandType.Text;
#endif
cmd.CommandText = "select * from TableWithBLOB";
cmd.Parameters.Clear();
rdr = cmd.ExecuteReader();
do
{
if (rdr.HasRows)
{
for (int i = 0; i < rdr.FieldCount; ++i)
fstr_out.WriteLine(rdr.GetName(i) + " GetDataTypeName(): \"" + rdr.GetDataTypeName(i) + "\" GetFieldType(): \"" + rdr.GetFieldType(i) + "\"");
tmpInt = rdr.GetOrdinal("FBlob");
while (rdr.Read())
{
tmpString = "FromBlob.bmp";
if (File.Exists(tmpString))
File.Delete(tmpString);
Blob = (byte[])rdr["FBlob"];
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
tmpString = "FromBlob_1.bmp";
if (File.Exists(tmpString))
File.Delete(tmpString);
Blob = new byte[rdr.GetBytes(tmpInt, 0, null, 0, int.MaxValue)];
rdr.GetBytes(tmpInt, 0, Blob, 0, Blob.Length);
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
}
}
} while (rdr.NextResult());
rdr.Close();
#endif
#if TEST_SELECT
/* if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType=CommandType.Text;
cmd.CommandText = @"
select
ew.ew_id as ""Id"",
ew.ew_name as ""Description""
from
typhoon.tbl_extra_what ew
where
(ew.ew_fs=typhoon.get_parent_cfo(:cfo))
and (coalesce(ew.ew_is_closed,0)=0)
order by ew.ew_id
";
cmd.Parameters.Clear();
cmd.Parameters.Add("cfo", OracleType.Number).Value = 150140;
if (da == null)
da = new OracleDataAdapter();
da.SelectCommand = cmd;
if (tmpDataTable == null)
tmpDataTable = new DataTable();
else
tmpDataTable.Reset();
da.Fill(tmpDataTable);
*/ /*
tmpObject = cmd.ExecuteScalar();
if (tmpObject != null && !Convert.IsDBNull(tmpObject))
tmpLong = Convert.ToInt64(tmpObject);
*/
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText="select * from tmp_docs_badrows";
cmd.Parameters.Clear();
if (da == null)
da = new OracleDataAdapter();
da.SelectCommand = cmd;
if (tmpDataTable == null)
tmpDataTable = new DataTable();
else
tmpDataTable.Reset();
da.Fill(tmpDataTable);
if (tmpDataTable.PrimaryKey == null)
tmpDataTable.PrimaryKey = new DataColumn[] { tmpDataTable.Columns["doc_id"], tmpDataTable.Columns["g_id"] };
#endif
#if TEST_SELECTED_STORED_PROCEDURE
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "StoredProcedureForPaging";
OracleCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["start_in"].Value = 2;
cmd.Parameters["limit_in"].Value = 2;
if (da == null)
da = new OracleDataAdapter();
da.SelectCommand = cmd;
if (tmpDataTable == null)
tmpDataTable = new DataTable();
else
tmpDataTable.Reset();
da.Fill(tmpDataTable);
FieldName="id";
foreach(DataRow r in tmpDataTable.Rows)
{
Console.WriteLine(!r.IsNull(FieldName) ? Convert.ToInt32(r[FieldName]).ToString() : "null");
}
#endif
#if TEST_STORED_PROCEDURE
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "typhoon.GET_URL_OPENDOC";
OracleCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["P_DOCID"].Value = 33711025;
cmd.Parameters["P_FULLHTMLCODE"].Value = DBNull.Value;
cmd.ExecuteNonQuery();
tmpString = Convert.ToString(cmd.Parameters["RETURN_VALUE"].Value);
#endif
#if TEST_MULTI_STATEMENTS
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
//cmd.CommandText = "alter session set NLS_TERRITORY = 'CIS';alter session set NLS_SORT = 'RUSSIAN';alter session set NLS_NUMERIC_CHARACTERS = '.,'";
cmd.CommandText = "begin execute immediate 'alter session set NLS_TERRITORY = ''CIS'''; execute immediate 'alter session set NLS_SORT = ''RUSSIAN'''; execute immediate 'alter session set NLS_NUMERIC_CHARACTERS = ''.,'''; end;";
cmd.ExecuteNonQuery();
#endif
#if TEST_PARAMETERS
if (cmd == null)
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from dual where :a=:b";
cmd.Parameters.Add("a", OracleType.Int32).Value = 1;
cmd.Parameters.Add("b", OracleType.Int32).Value = 1;
//cmd.Parameters.Add("c", OracleType.Int32).Value = 2;
tmpObject = cmd.ExecuteScalar();
cmd.Parameters.Clear();
cmd.CommandText = "select k_id as id, k_name as val from typhoon.tbl_kontragents where k_name like :q order by k_name";
tmpString = "FOTO";
cmd.Parameters.Add("q", OracleType.NVarChar).Value = tmpString + "%";
if (da == null)
da = new OracleDataAdapter();
da.SelectCommand = cmd;
if (tmpDataTable == null)
tmpDataTable = new DataTable();
else
tmpDataTable.Reset();
da.Fill(tmpDataTable);
cmd.Parameters.Clear();
cmd.CommandText = @"
select
hoty_id,
param_name,
param_value
from
order_params
where
(hoty_id = :hoty_id)
";
cmd.Parameters.Add("hoty_id", OracleType.Number).Value = 2219;
if (da == null)
da = new OracleDataAdapter();
da.SelectCommand = cmd;
if (tmpDataTable == null)
tmpDataTable = new DataTable();
else
tmpDataTable.Reset();
da.Fill(tmpDataTable);
FieldName = "param_name";
foreach (DataRow r in tmpDataTable.Rows)
{
Console.WriteLine(!r.IsNull(FieldName) ? Convert.ToString(r[FieldName]).ToString() : "null");
}
#endif
}
catch (Exception eException)
{
Console.WriteLine(eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace);
}
}
finally
{
if (rdr != null && !rdr.IsClosed)
rdr.Close();
if (conn != null && conn.State == ConnectionState.Open)
conn.Close();
if (fstr_out != null)
fstr_out.Close();
}
}
public OrganizationModel OracleGetOrg(string UNP)
{
if (UNP == null)
{
MessageBox.Show("Заполните УПН!", "Ошибка УНП", MessageBoxButton.OK, MessageBoxImage.Error);
return(new OrganizationModel());
}
var orgFromOracle = new OrganizationModel();
OrganizationModel oracleOrg = null;
using (OracleConnection oracleConnection = new OracleConnection(ConnectionStringOracle))
{
oracleConnection.Open();
string oracleCheckUNP = "SELECT COUNT(*) FROM BUTB_SEC.mns_subject WHERE mns_subject.UNP =" + $"'{UNP}'";
OracleCommand oracleCheckUNPComm = new OracleCommand(oracleCheckUNP, oracleConnection);
if ((Convert.ToInt32((oracleCheckUNPComm.ExecuteOracleScalar()).ToString()) != 1))
{
MessageBox.Show("Организации с таким УПН не существует в базе МНС!", "Ошибка УНП", MessageBoxButton.OK, MessageBoxImage.Error);
return(new OrganizationModel());
}
string oracleFullNameOrgComm = "SELECT mns_subject.FULL_NAME FROM BUTB_SEC.mns_subject WHERE mns_subject.UNP = " + $"{UNP}";
OracleCommand oracleFullNameOrg = new OracleCommand(oracleFullNameOrgComm, oracleConnection);// Поллное название организации
string oracleSortNameOrgComm = "SELECT mns_subject.NAME FROM BUTB_SEC.mns_subject WHERE mns_subject.UNP = " + $"{UNP}";
OracleCommand oracleSortNameOrg = new OracleCommand(oracleSortNameOrgComm, oracleConnection); // Краткое название организации
string oracleDateRegistrationComm = "SELECT mns_subject.REG_DATE FROM BUTB_SEC.mns_subject WHERE mns_subject.UNP = " + $"{ UNP}";
OracleCommand oracleDateRegistration = new OracleCommand(oracleDateRegistrationComm, oracleConnection);// Дата регистрации
string checkUNPComm = "SELECT COUNT(*) FROM BUTB_SEC.mns_subject WHERE mns_subject.UNP =" + $"'{UNP}'";
OracleCommand CheckUNP = new OracleCommand(checkUNPComm, oracleConnection);// проверка на наличие организации с таким УНП
string createDateOrg = oracleDateRegistration.ExecuteOracleScalar().ToString();
string sqlORARESIDENT = "SELECT mns_constitutor.RESIDENT FROM BUTB_SEC.mns_constitutor join BUTB_SEC.mns_subject on mns_constitutor.MNS_SUBJECT_ID = mns_subject.ID WHERE mns_subject.UNP = " + $"{ UNP}";
OracleCommand comORARESIDENT = new OracleCommand(sqlORARESIDENT, oracleConnection);
int rez = Convert.ToInt32(comORARESIDENT.ExecuteOracleScalar().ToString());
var rezident = "";
if (rez == 1)
{
rezident = "Беларусь";
}
//string sqlNCOUNTRYID = "SELECT mns_constitutor.NCOUNTRY_ID FROM BUTB_SEC.mns_constitutor join BUTB_SEC.mns_subject on mns_constitutor.MNS_SUBJECT_ID = mns_subject.ID WHERE mns_subject.UNP = " + $"{ UNP}";
var orgModel = new OrganizationModel
{
CreatedateOrg = Convert.ToDateTime(createDateOrg), // Это не правильно делать через приведение к стрингу и назад, но инече я получаю System.InvalidCastException: "Не удалось привести тип объекта "System.Data.OracleClient.OracleDateTime" к типу "System.IConvertible"."
FullNameOrg = oracleFullNameOrg.ExecuteOracleScalar().ToString(),
ShortNameOrg = oracleSortNameOrg.ExecuteOracleScalar().ToString(),
CountyrOrg = rezident,
};
oracleConnection.Close();
oracleOrg = orgModel;
}
return(oracleOrg ?? new OrganizationModel());// если риск NULL возвращаем пустую модель(чтобы не свалилось в случае чего)
}
/// <summary>
/// 检查HIS系统中出院卡片是否已审核通过
/// 注：功能未启用、未配置、配置信息错误也视为未通过
/// </summary>
/// <auth>Yanqiao.Cai</auth>
/// <date>2013-03-04</date>
/// <param name="noofinpat"></param>
/// <returns></returns>
public static bool CheckOutHosCardCheckPassed(int noofinpat)
{
try
{
string config = DS_SqlService.GetConfigValueByKey("AuditOutHosCardInHIS");
if (string.IsNullOrEmpty(config))
{
return(false);
}
XmlDocument doc = new XmlDocument();
doc.LoadXml(config);
#region 1、出科检查按钮是否显示
XmlNodeList isOutHosCheckBtnOpenList = doc.GetElementsByTagName("IsOutHosCheckBtnOpen");
if (null == isOutHosCheckBtnOpenList || isOutHosCheckBtnOpenList.Count == 0)
{
return(false);
}
if (null != isOutHosCheckBtnOpenList[0].InnerText && isOutHosCheckBtnOpenList[0].InnerText.Trim() != "1")
{
return(false);
}
#endregion
#region 2、此功能是否启用
XmlNodeList isOpenList = doc.GetElementsByTagName("IsOpen");
if (null == isOpenList || isOpenList.Count == 0)
{
return(false);
}
if (null != isOpenList[0].InnerText && isOpenList[0].InnerText.Trim() != "1")
{
return(false);
}
#endregion
#region 3、出科检查是否已通过
///3.1、EMR中病人基本信息表
XmlNodeList emrBasicInfoTableNameList = doc.GetElementsByTagName("EmrBasicInfoTableName");
if (null == emrBasicInfoTableNameList || emrBasicInfoTableNameList.Count == 0)
{
return(false);
}
string emrBasicInfoTableName = null == emrBasicInfoTableNameList[0].InnerText ? string.Empty : emrBasicInfoTableNameList[0].InnerText.Trim();
if (string.IsNullOrEmpty(emrBasicInfoTableName))
{
return(false);
}
///3.2、EMR病人基本信息表中对应的出院检查标识字段
XmlNodeList emrOutHosCheckFlagFieldNameList = doc.GetElementsByTagName("EmrOutHosCheckFlagFieldName");
if (null == emrOutHosCheckFlagFieldNameList || emrOutHosCheckFlagFieldNameList.Count == 0)
{
return(false);
}
string emrOutHosCheckFlagFieldName = null == emrOutHosCheckFlagFieldNameList[0].InnerText ? string.Empty : emrOutHosCheckFlagFieldNameList[0].InnerText.Trim();
if (string.IsNullOrEmpty(emrOutHosCheckFlagFieldName))
{
return(false);
}
///3.3、EMR病人基本信息表中与病人表关联的字段
XmlNodeList relatedNoofinpatFieldNameList = doc.GetElementsByTagName("RelatedNoofinpatFieldName");
if (null == relatedNoofinpatFieldNameList || relatedNoofinpatFieldNameList.Count == 0)
{
return(false);
}
string relatedNoofinpatFieldName = null == relatedNoofinpatFieldNameList[0].InnerText ? string.Empty : relatedNoofinpatFieldNameList[0].InnerText.Trim();
if (string.IsNullOrEmpty(relatedNoofinpatFieldName))
{
return(false);
}
///3.4、EMR病人基本信息表中出院检查标识字段对应的检查通过的值
XmlNodeList emrOutHosCheckPassedValueList = doc.GetElementsByTagName("EmrOutHosCheckPassedValue");
if (null == emrOutHosCheckPassedValueList || emrOutHosCheckPassedValueList.Count == 0)
{
return(false);
}
string emrOutHosCheckPassedValue = null == emrOutHosCheckPassedValueList[0].InnerText ? string.Empty : emrOutHosCheckPassedValueList[0].InnerText.Trim();
if (string.IsNullOrEmpty(emrOutHosCheckPassedValue))
{
return(false);
}
string checkFlagValue = DS_SqlService.GetFieldContent(DS_Common.DeleteSpecialCharacter(emrBasicInfoTableName), DS_Common.DeleteSpecialCharacter(emrOutHosCheckFlagFieldName), DS_Common.DeleteSpecialCharacter(relatedNoofinpatFieldName), DS_Common.DeleteSpecialCharacter(noofinpat.ToString()).Trim());
///3.5、判断出科检查是否已通过
if (checkFlagValue != emrOutHosCheckPassedValue)
{
return(false);
}
#endregion
#region 4、EMR中病人表与HIS关联的字段
///4.1、EMR中病人表与HIS关联的字段
XmlNodeList emrRelatedFieldNameList = doc.GetElementsByTagName("EmrRelatedFieldName");
if (null == emrRelatedFieldNameList || emrRelatedFieldNameList.Count == 0)
{
return(false);
}
string emrRelatedFieldName = null == emrRelatedFieldNameList[0].InnerText ? string.Empty : emrRelatedFieldNameList[0].InnerText.Trim();
if (string.IsNullOrEmpty(emrRelatedFieldName))
{
return(false);
}
///4.2、EMR中病人表与HIS关联的字段的值
DataTable dt = DS_SqlService.GetInpatientByID(noofinpat, 1);
emrRelatedFieldName = DS_Common.DeleteSpecialCharacter(emrRelatedFieldName);
if (null == dt || dt.Rows.Count == 0 || !dt.Columns.Contains(emrRelatedFieldName.ToUpper()))
{
return(false);
}
string emrRelatedFieldValue = dt.Rows[0][emrRelatedFieldName].ToString();
if (string.IsNullOrEmpty(emrRelatedFieldValue))
{
return(false);
}
#endregion
#region 5、该病人的出院卡片在HIS系统中是否已经审核通过
///5.1、获取HIS审核状态表
XmlNodeList auditTableNameList = doc.GetElementsByTagName("AuditTableName");
if (null == auditTableNameList || auditTableNameList.Count == 0)
{
return(false);
}
string auditTableName = null == auditTableNameList[0].InnerText ? string.Empty : auditTableNameList[0].InnerText.Trim();
if (string.IsNullOrEmpty(auditTableName))
{
return(false);
}
///5.2、获取HIS审核状态字段
XmlNodeList auditFieldNameList = doc.GetElementsByTagName("AuditFieldName");
if (null == auditFieldNameList || auditFieldNameList.Count == 0)
{
return(false);
}
string auditFieldName = null == auditFieldNameList[0].InnerText ? string.Empty : auditFieldNameList[0].InnerText.Trim();
if (string.IsNullOrEmpty(auditFieldName))
{
return(false);
}
///5.3、获取HIS审核状态字段对应的审核通过的值(配置项)
XmlNodeList auditPassedValueList = doc.GetElementsByTagName("AuditPassedValue");
if (null == auditPassedValueList || auditPassedValueList.Count == 0)
{
return(false);
}
string auditPassedValue = null == auditPassedValueList[0].InnerText ? string.Empty : auditPassedValueList[0].InnerText.Trim();
if (string.IsNullOrEmpty(auditPassedValue))
{
return(false);
}
///5.4、获取HIS中与EMR病人表关联的字段
XmlNodeList hisRelatedFieldNameList = doc.GetElementsByTagName("HisRelatedFieldName");
if (null == hisRelatedFieldNameList || hisRelatedFieldNameList.Count == 0)
{
return(false);
}
string hisRelatedFieldName = null == hisRelatedFieldNameList[0].InnerText ? string.Empty : hisRelatedFieldNameList[0].InnerText.Trim();
if (string.IsNullOrEmpty(hisRelatedFieldName))
{
return(false);
}
using (OracleConnection conn = new OracleConnection(DataAccessFactory.GetSqlDataAccess("HISDB").GetDbConnection().ConnectionString))
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
OracleCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
///5.5、该病人的出院卡片在HIS系统中是否已经审核通过
auditTableName = DS_Common.DeleteSpecialCharacter(auditTableName);
auditFieldName = DS_Common.DeleteSpecialCharacter(auditFieldName);
auditPassedValue = DS_Common.DeleteSpecialCharacter(auditPassedValue);
hisRelatedFieldName = DS_Common.DeleteSpecialCharacter(hisRelatedFieldName);
cmd.CommandText = " select " + auditFieldName + " from " + auditTableName + " where " + hisRelatedFieldName + " = :emrRelatedFieldValue ";
cmd.Parameters.Clear();
OracleParameter para = new OracleParameter("emrRelatedFieldValue", OracleType.Char);
para.Value = emrRelatedFieldValue;
cmd.Parameters.Add(para);
object obj = cmd.ExecuteOracleScalar();
if (null != obj && obj.ToString().Trim() == auditPassedValue.Trim())
{
return(true);
}
}
#endregion
return(false);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public AutoFillKontragentViewModel AutoFilKontra(string UNP)
{
if (UNP == null)
{
MessageBox.Show("Заполните УПН!", "Ошибка УНП", MessageBoxButton.OK, MessageBoxImage.Error);
return(new AutoFillKontragentViewModel());
}
var orgFromOracle = new AutoFillKontragentViewModel();
AutoFillKontragentViewModel oracleOrg = null;
using (OracleConnection oracleConnection = new OracleConnection(ConnectionStringOracle))
{
oracleConnection.Open();
//-----------------------------------------------------------------------
string oracleCheckUNP = "SELECT COUNT(*) FROM BUTB_SEC.mns_subject WHERE mns_subject.UNP =" + $"'{UNP}'";
OracleCommand oracleCheckUNPComm = new OracleCommand(oracleCheckUNP, oracleConnection);
if ((Convert.ToInt32((oracleCheckUNPComm.ExecuteOracleScalar()).ToString()) != 1))
{
MessageBox.Show("Организации с таким УПН не существует в базе МНС!", "Ошибка УНП", MessageBoxButton.OK, MessageBoxImage.Error);
return(new AutoFillKontragentViewModel());
}
//=======================================================================
//-----------------------------------------------------------------------
string oracleFullNameOrgComm = "SELECT mns_subject.FULL_NAME FROM BUTB_SEC.mns_subject WHERE mns_subject.UNP = " + $"{UNP}";
OracleCommand oracleFullNameOrg = new OracleCommand(oracleFullNameOrgComm, oracleConnection);// Поллное название организации
string fullNameOrg = oracleFullNameOrg.ExecuteOracleScalar().ToString();
//=======================================================================
//-----------------------------------------------------------------------
string oracleSortNameOrgComm = "SELECT mns_subject.NAME FROM BUTB_SEC.mns_subject WHERE mns_subject.UNP = " + $"{UNP}";
OracleCommand oracleSortNameOrg = new OracleCommand(oracleSortNameOrgComm, oracleConnection); // Краткое название организации
string shortNameOrg = oracleSortNameOrg.ExecuteOracleScalar().ToString();
//======================================================================
//----------------------------------------------------------------------
string oracleDateRegistrationComm = "SELECT mns_subject.REG_DATE FROM BUTB_SEC.mns_subject WHERE mns_subject.UNP = " + $"{ UNP}";
OracleCommand oracleDateRegistration = new OracleCommand(oracleDateRegistrationComm, oracleConnection);// Дата регистрации
string createDateOrg = oracleDateRegistration.ExecuteOracleScalar().ToString();
//=====================================================================
//----------------------------------------------------------------------
string sqlHeadName = "SELECT mns_subject.FIO_CHIEF FROM BUTB_SEC.mns_subject WHERE mns_subject.UNP = " + $"{UNP}";
OracleCommand comHeadName = new OracleCommand(sqlHeadName, oracleConnection);// руководитель
string headName = comHeadName.ExecuteOracleScalar().ToString();
//=====================================================================
//---------------------------------------------------------------------
string sqlOwneerName = "SELECT mns_constitutor.FULL_NAME FROM BUTB_SEC.mns_constitutor join BUTB_SEC.mns_subject on mns_constitutor.MNS_SUBJECT_ID = mns_subject.ID WHERE mns_subject.UNP =" + $"{ UNP}";
OracleCommand OwneerNameComm = new OracleCommand(sqlOwneerName, oracleConnection);
OracleDataReader reader = OwneerNameComm.ExecuteReader();
string ownerName = null;
while (reader.Read())
{
ownerName = $"{ownerName}" + $"{ Convert.ToString(reader.GetString(0))}; ";
}
//---------------------------------------------------------------------
string sqlResident = "SELECT mns_constitutor.RESIDENT FROM BUTB_SEC.mns_constitutor join BUTB_SEC.mns_subject on mns_constitutor.MNS_SUBJECT_ID = mns_subject.ID WHERE mns_subject.UNP = " + $"{UNP}";
OracleCommand comResident = new OracleCommand(sqlResident, oracleConnection);
string countryPers = null;
string countryOrg = null;
int rez = 0;
try
{
rez = Convert.ToInt32(comResident.ExecuteOracleScalar().ToString());
}
catch (Exception)
{
countryOrg = "Беларусь";
//MessageBox.Show("В базе данных не содержится признака резидентства. Скорее всего организация очень старая или данные были внесены не корректно. Заполняем ручками из регистратора:)", "Предупреждение!", MessageBoxButton.OK, MessageBoxImage.Error);
}
if (rez != 0)
{
countryOrg = "Беларусь";
}
//=======================================================================
try
{
string ConnStrCountryID = "SELECT mns_constitutor.NCOUNTRY_ID FROM BUTB_SEC.mns_constitutor join BUTB_SEC.mns_subject on mns_constitutor.MNS_SUBJECT_ID = mns_subject.ID WHERE mns_subject.UNP = " + $"{UNP}";
OracleCommand com_ORA_NCOUNTRY_ID = new OracleCommand(ConnStrCountryID, oracleConnection);
string countryID = com_ORA_NCOUNTRY_ID.ExecuteOracleScalar().ToString();
using (SqlConnection sqlConnection = new SqlConnection(connectionStringSQL))
{
sqlConnection.Open();
SqlCommand Country_Select = new SqlCommand("Country_Select", sqlConnection);// получение старны
Country_Select.CommandType = CommandType.StoredProcedure;
Country_Select.Parameters.AddWithValue("Country_Code", countryID);
countryPers = Country_Select.ExecuteScalar().ToString();
if (countryPers == null)
{
countryPers = "Беларусь";
}
sqlConnection.Close();
}
}
catch (Exception)
{
countryPers = "Беларусь";
}
finally
{
if (countryPers == null)
{
countryPers = "(не выбрано)";
}
}
//-----------------------------------------------------------------------
//=======================================================================
var orgModel = new AutoFillKontragentViewModel();
orgModel.AutoFillViewModelOrg.CreateDateOrg = Convert.ToDateTime(createDateOrg); // Это не правильно делать через приведение к стрингу и назад, но инече я получаю System.InvalidCastException: "Не удалось привести тип объекта "System.Data.OracleClient.OracleDateTime" к типу "System.IConvertible"."
orgModel.AutoFillViewModelPers.HeadName = headName;
orgModel.AutoFillViewModelOrg.CountryOrg = countryOrg;
orgModel.AutoFillViewModelPers.CountryPers = countryPers;
if (string.IsNullOrEmpty(ownerName) || string.IsNullOrWhiteSpace(ownerName) || ownerName == "null" || ownerName == "Null")
{
orgModel.AutoFillViewModelPers.OwnerName = headName;
orgModel.AutoFillViewModelOrg.FullNameOrg = $"Индивидивидуальный предприниматель {fullNameOrg}";
orgModel.AutoFillViewModelOrg.ShortNameOrg = $"ИП {shortNameOrg}";
}
else
{
orgModel.AutoFillViewModelPers.OwnerName = ownerName;
orgModel.AutoFillViewModelOrg.FullNameOrg = fullNameOrg;
orgModel.AutoFillViewModelOrg.ShortNameOrg = shortNameOrg;
}
var check = new CheckUNP();
if (check.UNPchecher(UNP) == true)
{
MessageBox.Show("Организация с таким УНП уже внесена в базу", "Внимание!", MessageBoxButton.OK, MessageBoxImage.Information);
}
oracleConnection.Close();
oracleOrg = orgModel;
}
return(oracleOrg ?? new AutoFillKontragentViewModel());// если риск NULL возвращаем пустую модель(чтобы не свалилось в случае чего)
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
if (!Page.IsValid)
{
return;
}
string[] curupdateDate = hidNewDt.Value.Split(';');
if (curupdateDate.Length == 0 || hidNewDt.Value == "")
{
return;
}
using (OracleConnection ConnectionStringJXC = new OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionStringJXC"].ConnectionString))
{
ConnectionStringJXC.Open(); //打开链接
OracleCommand cmd = new OracleCommand(); //创建执行类
cmd.Connection = ConnectionStringJXC;
OracleTransaction tx = ConnectionStringJXC.BeginTransaction();
cmd.Transaction = tx;//开启链接事务
string strSql = "";
string strPK_TMP_NC_XSJH = "";
///插入销售计划表头信息
try
{
string[] curRowDate = curupdateDate[0].Split(',');
/* strSql = "select PK_TMP_NC_XSJH from tmp_nc_xsjh where dr='0' and DBILLDATE='" + curRowDate[9].ToString() + "' and pk_corp='" + log_corp + "' and FREE1='" + log_user + "' ";
* cmd.CommandText = strSql;
* object objPK_TMP_NC_XSJH = cmd.ExecuteOracleScalar();
* if (objPK_TMP_NC_XSJH == null)
* {*/
cmd.CommandText = "select tmp_nc_xsjh_seq.NextVal from dual ";
strPK_TMP_NC_XSJH = cmd.ExecuteOracleScalar().ToString();
////如果不存在插入
strSql = "insert into tmp_nc_xsjh (PK_TMP_NC_XSJH,DBILLDATE,CCUSTOMERID,PK_CORP,DR,VNOTE,";
strSql += " FREE1,FREE2,FREE3,FREE4,FREE5,TS,FLAG,ERRORMESSAGE,VRECEIPTCODE,CUSTCODE,CUSTNAME,CORPNAME)";
strSql += "values('" + strPK_TMP_NC_XSJH + "','" + TextTHRQ.Text + "','" + curRowDate[0].ToString() + "','" + DplTHGC.Items[DplTHGC.SelectedIndex].Value + "',0,'',";
strSql += "'','','','','',to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'),'0','','" + createNCYDBH(cmd, TextTHRQ.Text) + "',";
strSql += "'" + curRowDate[1].ToString() + "','" + curRowDate[2].ToString() + "','" + DplTHGC.Items[DplTHGC.SelectedIndex].Text + "')";
cmd.CommandText = strSql;
int exIn = cmd.ExecuteNonQuery();
/* }
* else //更新
* {
* strPK_TMP_NC_XSJH = objPK_TMP_NC_XSJH.ToString();
* strSql = "update tmp_nc_xsjh set ts=to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') where dr='0' and DBILLDATE='" + curRowDate[9].ToString() + "' and pk_corp='" + log_corp + "' and FREE1='" + log_user + "' ";
* cmd.CommandText = strSql;
* cmd.ExecuteOracleScalar();
* } */
}
catch (System.Data.OracleClient.OracleException E) {
tx.Rollback();
MessageBox.ShowAndRedirect(this, "操作销售计划表头信息失败，请联系系统管理员！", "SalesPlan.aspx");
return;
}
///插入销售计划表体信息
try {
for (int i = 0; i < curupdateDate.Length; i++)
{
string[] curRowDate_b = curupdateDate[i].Split(',');
string strPK_TMP_NC_XSJH_B = "";//销售计划表体ID
///校验物料是否存在，并创建销售计划表体ID
try
{
strPK_TMP_NC_XSJH_B = createPK_XSJH_B(cmd, curRowDate_b[4], TextTHRQ.Text);
//if (strPK_TMP_NC_XSJH_B.Substring(0, 4).Equals("exist"))
//{
// tx.Rollback();
// MessageBox.ShowAndRedirect(this, "当前物料:"+curRowDate_b[7].ToString()+"已经存在，请返回计划列表直接修改！", "SalesPlan.aspx");
// return ;
//}
}
catch (System.Data.OracleClient.OracleException E)
{
tx.Rollback();
MessageBox.ShowAndRedirect(this, "创建销售表体ID失败，请联系系统管理员！", "SalesPlan.aspx");
return;
}
///插入销售计划表体ID
strSql = " insert into tmp_nc_xsjh_b (PK_TMP_NC_XSJH_B,PK_TMP_NC_XSJH,BLARGESSFLAG,CINVBASDOCID,CINVENTORYID,FROWNOTE,DR";
strSql += " ,FREE1,FREE2,FREE3,FREE4,FREE5,TS,NUM,INVNAME,UNITWEIGHT)";
strSql += "values('" + strPK_TMP_NC_XSJH_B + "','" + strPK_TMP_NC_XSJH + "','N','" + curRowDate_b[3].ToString() + "','" + curRowDate_b[4].ToString() + "','','0',";
strSql += "'','','','','',to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')," + curRowDate_b[6].ToString() + ",'" + curRowDate_b[5].ToString() + "'," + curRowDate_b[7].ToString() + ")";
cmd.CommandText = strSql;
int exIn = cmd.ExecuteNonQuery();
}
tx.Commit();
}
catch (System.Data.OracleClient.OracleException E)
{
tx.Rollback();
MessageBox.ShowAndRedirect(this, "插入销售计划表头信息失败，请联系系统管理员！", "SalesPlan.aspx");
return;
}
ConnectionStringJXC.Close();
BindSalesList();
//MessageBox.ShowAndRedirect(this, "保存操作成功！", "SalesPlan.aspx");
}
}