public OdbcDataReader ExecuteQuery(string queryStr)
{
try
{
OdbcCommand command = new OdbcCommand(queryStr, dbConn);
command.CommandType = System.Data.CommandType.Text;
return command.ExecuteReader();
}
catch (Exception ex)
{
exception = ex;
return null;
}
}
// 실시간 데이터 전송
// GET: api/data
public IEnumerable <RTimeDataModel> Get()
{
var dataList = new List <RTimeDataModel>();
var dataType = new string[]
{
"STRAIN", "TEMP", "DISP"
};
var endIdx = new int[]
{
CRUD.Strain_colName.Count + 1,
CRUD.Temp_colName.Count + 1,
CRUD.Disp_colName.Count + 1
};
using (OdbcConnection conn = new OdbcConnection(connectionString))
{
OdbcCommand cmd = new OdbcCommand();
cmd.Connection = conn;
conn.Open();
try
{
DateTime now = DateTime.Now;
// 5분 전까지 데이터가 하나 가져오기
for (int i = 0; i < 3; i++)
{
StringBuilder query = new StringBuilder(700);
query.Append("SELECT MEASURE_TIME");
for (int colNo = 1; colNo < endIdx[i]; colNo++)
{
query.Append($", RESULT{colNo}");
}
query.Append($" FROM {dataType[i]}_{now.Year} WHERE measure_time > subdate(now(), interval 5 minute)");
cmd.CommandText = query.ToString();
OdbcDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
RTimeDataModel data = new RTimeDataModel();
StringBuilder mData = new StringBuilder();
data.DataType = dataType[i];
data.MTime = (DateTime)reader[0];
for (int j = 1; j < reader.FieldCount; j++)
{
if (reader[j] == DBNull.Value)
{
mData.Append("NaN,");
}
else
{
mData.Append($"{reader[j]},");
}
}
data.MData = mData.ToString().TrimEnd(',');
dataList.Add(data);
}
reader.Close();
}
}
catch (Exception ex)
{
logger.Error("Error RunTime Data Request", ex);
}
finally
{
if (conn != null)
{
conn.Dispose();
}
}
}
return(dataList);
}
public override Stream GetSnapshot(string id, string contentType)
{
Logging.LogInfo("GetSnapshot for " + id);
var tmpFileName = ConfigurationReader.Configuration.HashValueStorageLocation + Path.DirectorySeparatorChar + Guid.NewGuid();
Logging.LogInfo("Writing to file " + tmpFileName);
try {
using (var fs = new FileStream(tmpFileName, FileMode.Create))
{
var sb = new StringBuilder();
foreach (var definition in _publishingDefinitions)
{
foreach (var generationDefinition in definition.FragmentGenerationDefinitions)
{
try
{
using (var connection = new OdbcConnection(_dataSourceConnectionString))
{
connection.Open();
var odbcCommand = new OdbcCommand(generationDefinition.SnapshotQuery)
{
Connection = connection,
CommandTimeout = 0
};
var dr = odbcCommand.ExecuteReader();
var schema = dr.GetSchemaTable();
var columnNames = (from DataRow row in schema.Rows select row[0].ToString().ToLower()).ToList();
var flushCount = 0;
var drAdaptor = new DbReaderDataRow(dr, columnNames);
while (dr.Read())
{
try
{
flushCount++;
foreach (var line in generationDefinition.RdfTemplateLines)
{
var pattern = new NTripleLinePattern(line);
pattern.GenerateNTriples(sb, drAdaptor, generationDefinition.GenericTemplateExcludeColumns, false);
}
}
catch (Exception dataex)
{
Logging.LogError(1, "Error Processing Data Line in " + generationDefinition.SnapshotQuery + " : " + dataex.Message);
}
if (flushCount >= 1000)
{
try
{
var sw = new StreamWriter(fs);
sw.Write(sb.ToString());
sw.Flush();
sb = new StringBuilder();
}
catch (Exception ex)
{
var msg = ex.Message;
Logging.LogError(1, "Error exporting triples " + msg + " " + ex.StackTrace);
}
}
}
if (flushCount >= 0)
{
try
{
var sw = new StreamWriter(fs);
sw.Write(sb.ToString());
sw.Flush();
sb = new StringBuilder();
}
catch (Exception ex)
{
var msg = ex.Message;
Logging.LogError(1, "Error exporting triples " + msg + " " + ex.StackTrace);
}
}
dr.Close();
connection.Close();
}
}
catch (Exception ext)
{
Logging.LogError(1, "Error processing definition {0} {1} {2} {3}", ext.Message, generationDefinition.SnapshotQuery, _dataSourceConnectionString, ext.StackTrace);
}
}
}
}
}
catch (Exception ex)
{
Logging.LogError(1, "Exception getting snapshot " + ex.Message + " " + ex.StackTrace);
}
return(new FileStream(tmpFileName, FileMode.Open));
}
public static int Main(string[] args)
{
int
Result = -1,
_ID_;
StreamWriter
fstr_out = null,
fstr_out_866 = null;
string
currentDirectory = System.Reflection.Assembly.GetExecutingAssembly().Location,
tmpString = "log.log",
DbfFileName,
asciiString,
cp866String;
if (currentDirectory.IndexOf("bin") != -1)
{
currentDirectory = currentDirectory.Substring(0, currentDirectory.LastIndexOf("bin", currentDirectory.Length - 1));
}
byte[]
asciiBytes,
cp866Bytes;
char[]
cp866Chars;
#if TEST_DBF_BY_ODBC
OdbcConnection
odbc_conn = null;
OdbcCommand
odbc_cmd = null;
OdbcDataReader
odbc_rdr = null;
OdbcDataAdapter
odbc_da = null;
#endif
OleDbConnection
conn = null;
OleDbCommand
cmd = null;
OleDbDataReader
rdr = null;
OleDbDataAdapter
da = null;
DataTable
tmpDataTable = null;
object
tmpObject;
try
{
try
{
fstr_out = new StreamWriter(tmpString, false, System.Text.Encoding.GetEncoding(1251));
fstr_out.AutoFlush = true;
fstr_out_866 = new StreamWriter(tmpString + "_866", false, System.Text.Encoding.GetEncoding(866));
fstr_out_866.AutoFlush = true;
#if TEST_DBF_BY_ODBC || TEST_DBF || TEST_VFP
string
PathToDbf = Path.Combine(currentDirectory, "dbf"),
CommonDbfTableName = "Common",
CommonDbfTableSQLCreate = @"
create table " + CommonDbfTableName + @"(
FInt integer,
FChar char(254),
FDate1 date,
FDate2 date
)";
#endif
#if TEST_DBF_BY_ODBC
if (PathToDbf.EndsWith(Path.DirectorySeparatorChar.ToString()))
{
PathToDbf = PathToDbf.Remove(PathToDbf.Length - 1, 1);
}
tmpString = "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=" + PathToDbf + ";";
odbc_conn = new OdbcConnection(tmpString);
odbc_conn.Open();
fstr_out.WriteLine("ConnectionString: " + odbc_conn.ConnectionString);
fstr_out.WriteLine("ConnectionTimeout: " + odbc_conn.ConnectionTimeout.ToString());
fstr_out.WriteLine("Database: " + odbc_conn.Database);
fstr_out.WriteLine("DataSource: " + odbc_conn.DataSource);
fstr_out.WriteLine("Driver: " + odbc_conn.Driver);
fstr_out.WriteLine("ServerVersion: " + odbc_conn.ServerVersion);
fstr_out.WriteLine("State: " + odbc_conn.State.ToString());
fstr_out.WriteLine();
tmpString = PathToDbf + Path.DirectorySeparatorChar + CommonDbfTableName + ".dbf";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
odbc_cmd = odbc_conn.CreateCommand();
odbc_cmd.CommandType = CommandType.Text;
odbc_cmd.CommandText = CommonDbfTableSQLCreate;
odbc_cmd.ExecuteNonQuery();
odbc_cmd.CommandText = "insert into " + CommonDbfTableName + " (FInt, FChar, FDate1, FDate2) values (1, 'Line# 1 Линия № 1 Лінія № 1', {d'2008-12-31'}, {d'2008-12-31'})";
odbc_cmd.ExecuteNonQuery();
odbc_cmd.CommandText = "insert into " + CommonDbfTableName + " (FInt, FChar) values (?, ?)";
odbc_cmd.Parameters.Clear();
odbc_cmd.Parameters.Add("FInt", OdbcType.Int).Value = 2;
odbc_cmd.Parameters.Add("FChar", OdbcType.VarChar).Value = "Line# 2 Линия № 2 Лінія № 2";
odbc_cmd.ExecuteNonQuery();
tmpString = "select * from " + CommonDbfTableName;
odbc_cmd = new OdbcCommand(tmpString, odbc_conn);
odbc_rdr = odbc_cmd.ExecuteReader();
while (odbc_rdr.Read())
{
for (int i = 0; i < odbc_rdr.FieldCount; ++i)
{
fstr_out.Write(odbc_rdr[odbc_rdr.GetName(i)]);
if (i < odbc_rdr.FieldCount)
{
fstr_out.Write("\t");
}
}
fstr_out.WriteLine();
}
odbc_rdr.Close();
odbc_da = new OdbcDataAdapter(tmpString, odbc_conn);
if (tmpDataTable == null)
{
tmpDataTable = new DataTable();
}
odbc_da.Fill(tmpDataTable);
ShowStru(tmpDataTable, fstr_out);
tmpDataTable.Reset();
odbc_cmd.CommandText = "select * from testtype_1";
fstr_out.WriteLine();
fstr_out.WriteLine("OdbcDataAdapter.FillSchema()");
odbc_da.SelectCommand = odbc_cmd;
odbc_da.FillSchema(tmpDataTable, SchemaType.Source);
foreach (DataColumn c in tmpDataTable.Columns)
{
fstr_out.WriteLine(c.ColumnName + ": \"" + c.DataType.ToString() + "\"");
}
tmpDataTable.Reset();
odbc_rdr = odbc_cmd.ExecuteReader(CommandBehavior.SchemaOnly);
fstr_out.WriteLine(Environment.NewLine + "OdbcDataReader.HasRows=" + odbc_rdr.HasRows.ToString());
for (int i = 0; i < odbc_rdr.FieldCount; ++i)
{
fstr_out.WriteLine(odbc_rdr.GetName(i) + " GetDataTypeName(): \"" + odbc_rdr.GetDataTypeName(i) + "\" GetFieldType(): \"" + odbc_rdr.GetFieldType(i) + "\"");
}
odbc_rdr.Close();
/*
* odbc_cmd.CommandText="insert into testtype_1 (FLogical) values (true)";
* odbc_cmd.ExecuteNonQuery();
*
* odbc_cmd.CommandText="update testtype_1 set FLogical=false";
* odbc_cmd.ExecuteNonQuery();
*/
try
{
tmpString = "select * from t"; // t is FoxPro (with cdx) table
odbc_cmd.CommandText = tmpString;
odbc_rdr = odbc_cmd.ExecuteReader();
odbc_rdr.Close();
}
catch (OdbcException eException)
{
tmpString = string.Empty;
for (int i = 0; i < eException.Errors.Count; ++i)
{
if (tmpString != string.Empty)
{
tmpString += Environment.NewLine;
}
tmpString += "Index #" + i + "\t" +
"Message: \"" + eException.Errors[i].Message + "\"\t" +
"Native: \"" + eException.Errors[i].NativeError.ToString() + "\"\t" +
"Source: \"" + eException.Errors[i].Source + "\"\t" +
"SQL: \"" + eException.Errors[i].SQLState + "\"";
}
fstr_out.WriteLine(Environment.NewLine + "OdbcException:" + Environment.NewLine + tmpString + Environment.NewLine);
}
odbc_conn.Close();
#endif
#if TEST_DBF
if (PathToDbf.EndsWith(Path.DirectorySeparatorChar.ToString()))
{
PathToDbf = PathToDbf.Remove(PathToDbf.Length - 1, 1);
}
tmpString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathToDbf + ";Extended Properties=dBASE IV;User ID=;Password="******"ConnectionString: " + conn.ConnectionString);
fstr_out.WriteLine("ConnectionTimeout: " + conn.ConnectionTimeout.ToString());
fstr_out.WriteLine("Database: " + conn.Database);
fstr_out.WriteLine("DataSource: " + conn.DataSource);
fstr_out.WriteLine("Provider: " + conn.Provider);
fstr_out.WriteLine("ServerVersion: " + conn.ServerVersion);
fstr_out.WriteLine("State: " + conn.State.ToString());
fstr_out.WriteLine();
tmpString = PathToDbf + Path.DirectorySeparatorChar + CommonDbfTableName + ".dbf";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = CommonDbfTableSQLCreate;
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into " + CommonDbfTableName + " (FInt, FChar, FDate1, FDate2) values (1, 'Line# 1 Линия № 1 Лінія № 1', #01/01/2008#, #01/01/2008#)";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into " + CommonDbfTableName + " (FInt, FChar) values (?, ?)";
cmd.Parameters.Clear();
cmd.Parameters.Add("FInt", OleDbType.Integer).Value = 2;
cmd.Parameters.Add("FChar", OleDbType.VarChar).Value = "Line# 2 Линия № 2 Лінія № 2";
cmd.ExecuteNonQuery();
tmpString = "select * from " + CommonDbfTableName;
cmd = new OleDbCommand(tmpString, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; ++i)
{
fstr_out.Write(rdr[rdr.GetName(i)]);
if (i < rdr.FieldCount)
{
fstr_out.Write("\t");
}
}
fstr_out.WriteLine();
}
rdr.Close();
tmpString = "select * from dbffile";
cmd = new OleDbCommand(tmpString, conn);
rdr = cmd.ExecuteReader();
fstr_out.WriteLine();
while (rdr.Read())
{
fstr_out.WriteLine(rdr.GetString(rdr.GetOrdinal("BANK")));
}
rdr.Close();
if (tmpDataTable == null)
{
tmpDataTable = new DataTable();
}
else
{
tmpDataTable.Reset();
}
if (da == null)
{
da = new OleDbDataAdapter(cmd);
}
da.Fill(tmpDataTable);
ShowStru(tmpDataTable, fstr_out);
tmpDataTable.Reset();
fstr_out.WriteLine();
cmd.CommandText = "select * from testtype_1";
fstr_out.WriteLine(cmd.CommandText);
fstr_out.WriteLine(new string('-', cmd.CommandText.Length));
fstr_out.WriteLine();
fstr_out.WriteLine("OleDbDataAdapter.FillSchema()");
da.SelectCommand = cmd;
da.FillSchema(tmpDataTable, SchemaType.Source);
foreach (DataColumn c in tmpDataTable.Columns)
{
fstr_out.WriteLine(c.ColumnName + ": \"" + c.DataType.ToString() + "\"");
}
tmpDataTable.Reset();
fstr_out.WriteLine();
fstr_out.WriteLine("OleDbCommand.ExecuteReader(CommandBehavior.SchemaOnly)");
rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
fstr_out.WriteLine("OleDbDataReader.HasRows=" + rdr.HasRows.ToString());
for (int i = 0; i < rdr.FieldCount; ++i)
{
fstr_out.WriteLine(rdr.GetName(i) + " " + rdr.GetDataTypeName(i) + " " + rdr.GetFieldType(i));
}
rdr.Close();
fstr_out.WriteLine();
cmd.CommandText = "select * from testtype_2";
fstr_out.WriteLine(cmd.CommandText);
fstr_out.WriteLine(new string('-', cmd.CommandText.Length));
fstr_out.WriteLine();
fstr_out.WriteLine("OleDbDataAdapter.FillSchema()");
da.SelectCommand = cmd;
da.FillSchema(tmpDataTable, SchemaType.Source);
foreach (DataColumn c in tmpDataTable.Columns)
{
fstr_out.WriteLine(c.ColumnName + ": \"" + c.DataType.ToString() + "\"");
}
tmpDataTable.Reset();
fstr_out.WriteLine();
fstr_out.WriteLine("OleDbCommand.ExecuteReader(CommandBehavior.SchemaOnly)");
rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
fstr_out.WriteLine("OleDbDataReader.HasRows=" + rdr.HasRows.ToString());
for (int i = 0; i < rdr.FieldCount; ++i)
{
fstr_out.WriteLine(rdr.GetName(i) + " " + rdr.GetDataTypeName(i) + " " + rdr.GetFieldType(i));
}
rdr.Close();
fstr_out.WriteLine();
try
{
tmpString = "select * from t"; // t is FoxPro (with cdx) table
cmd.CommandText = tmpString;
rdr = cmd.ExecuteReader();
rdr.Close();
}
catch (OleDbException eException)
{
tmpString = string.Empty;
for (int i = 0; i < eException.Errors.Count; ++i)
{
if (tmpString != string.Empty)
{
tmpString += Environment.NewLine;
}
tmpString += "Index #" + i + "\t" +
"Message: \"" + eException.Errors[i].Message + "\"\t" +
"Native: \"" + eException.Errors[i].NativeError.ToString() + "\"\t" +
"Source: \"" + eException.Errors[i].Source + "\"\t" +
"SQL: \"" + eException.Errors[i].SQLState + "\"";
}
fstr_out.WriteLine(Environment.NewLine + "OleDbException:" + Environment.NewLine + tmpString + Environment.NewLine);
}
cmd.CommandType = CommandType.Text;
DbfFileName = "test_ins";
cmd.CommandText = "delete from " + DbfFileName;
cmd.ExecuteNonQuery();
cmd.CommandText = "select max(ID) from " + DbfFileName;
tmpObject = cmd.ExecuteScalar();
_ID_ = !Convert.IsDBNull(tmpObject) ? Convert.ToInt32(tmpObject) + 1 : 1;
cmd.CommandText = "insert into " + DbfFileName + " (ID, FCHAR, DDATE, FNUMERIC) values (?, ?, ?, ?)";
cmd.Parameters.Clear();
cmd.Parameters.Add("ID", OleDbType.Numeric).Value = _ID_;
cmd.Parameters.Add("FCHAR", OleDbType.VarChar).Value = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
cmd.Parameters.Add("DDATE", OleDbType.Date).Value = DateTime.Now;
cmd.Parameters.Add("FNUMERIC", OleDbType.Numeric).Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "abcdefghijklmnopqrstuvwxyz";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "АБВГДЕЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "абвгдежзийклмнопрстуфхцчшщъыьэюя";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "ЁёҐґІіЇїЄє№";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
asciiString = "АБВГДЕЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ";
asciiBytes = System.Text.Encoding.GetEncoding(1251).GetBytes(asciiString);
cp866Bytes = System.Text.Encoding.Convert(System.Text.Encoding.GetEncoding(1251), System.Text.Encoding.GetEncoding(866), asciiBytes);
cp866Chars = new char[System.Text.Encoding.GetEncoding(866).GetCharCount(cp866Bytes, 0, cp866Bytes.Length)];
System.Text.Encoding.GetEncoding(866).GetChars(cp866Bytes, 0, cp866Bytes.Length, cp866Chars, 0);
cp866String = new string(cp866Chars);
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = cp866String;
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
asciiString = "абвгдежзийклмнопрстуфхцчшщъыьэюя";
asciiBytes = System.Text.Encoding.GetEncoding(1251).GetBytes(asciiString);
cp866Bytes = System.Text.Encoding.Convert(System.Text.Encoding.GetEncoding(1251), System.Text.Encoding.GetEncoding(866), asciiBytes);
cp866Chars = new char[System.Text.Encoding.GetEncoding(866).GetCharCount(cp866Bytes, 0, cp866Bytes.Length)];
System.Text.Encoding.GetEncoding(866).GetChars(cp866Bytes, 0, cp866Bytes.Length, cp866Chars, 0);
cp866String = new string(cp866Chars);
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = cp866String;
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
asciiString = "ЁёҐґІіЇїЄє№";
asciiBytes = System.Text.Encoding.GetEncoding(1251).GetBytes(asciiString);
asciiBytes[2] = 0x0c3; // 0x0a5 (165) -> 0x0c3 (195) Ґ -> Г
asciiBytes[3] = 0x0e3; // 0x0b4 (180) -> 0x0e3 (227) ґ -> г
asciiBytes[4] = 0x0a1; // 0x0b2 (178) -> 0x0a1 (161) І -> Ў
asciiBytes[5] = 0x0a2; // 0x0b3 (179) -> 0x0a2 (162) і -> ў
asciiBytes[6] = 0x0b0; // 0x0af (175) -> 0x0b0 (176) Ї -> °
asciiBytes[7] = 0x0b7; // 0x0bf (191) -> 0x0b7 (183) ї -> ·
asciiBytes[8] = 0x0af; // 0x0aa (170) -> 0x0af (175) Є -> Ї
asciiBytes[9] = 0x0bf; // 0x0ba (186) -> 0x0bf (191) є -> ї
cp866Bytes = System.Text.Encoding.Convert(System.Text.Encoding.GetEncoding(1251), System.Text.Encoding.GetEncoding(866), asciiBytes);
cp866Chars = new char[System.Text.Encoding.GetEncoding(866).GetCharCount(cp866Bytes, 0, cp866Bytes.Length)];
System.Text.Encoding.GetEncoding(866).GetChars(cp866Bytes, 0, cp866Bytes.Length, cp866Chars, 0);
cp866Chars[7] = (char)0x02219;
cp866String = new string(cp866Chars);
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = cp866String;
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = CP1251ToCP866("Line# 2 Линия № 2 Лінія № 2");
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
if (!PathToDbf.EndsWith(Path.DirectorySeparatorChar.ToString()))
{
PathToDbf += Path.DirectorySeparatorChar;
}
DbfFileName = "test_ADO.dbf";
if (File.Exists(PathToDbf + DbfFileName))
{
File.Delete(PathToDbf + DbfFileName);
}
DbfFileName = Path.GetFileNameWithoutExtension(DbfFileName);
cmd.CommandText = "create table " + DbfFileName + "(ID numeric(18,0), FCHAR varchar(254), DDATE date, FNUMERIC numeric(10,4))";
cmd.ExecuteNonQuery();
cmd.CommandText = "select max(ID) from " + DbfFileName;
tmpObject = cmd.ExecuteScalar();
_ID_ = !Convert.IsDBNull(tmpObject) ? Convert.ToInt32(tmpObject) + 1 : 1;
cmd.CommandText = "insert into " + DbfFileName + " (ID, FCHAR, DDATE, FNUMERIC) values (?, ?, ?, ?)";
cmd.Parameters.Clear();
cmd.Parameters.Add("ID", OleDbType.Numeric).Value = _ID_;
cmd.Parameters.Add("FCHAR", OleDbType.VarChar).Value = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
cmd.Parameters.Add("DDATE", OleDbType.Date).Value = DateTime.Now;
cmd.Parameters.Add("FNUMERIC", OleDbType.Numeric).Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "abcdefghijklmnopqrstuvwxyz";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "АБВГДЕЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "абвгдежзийклмнопрстуфхцчшщъыьэюя";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "ЁёҐґІіЇїЄє№";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
asciiString = "АБВГДЕЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ";
asciiBytes = System.Text.Encoding.GetEncoding(1251).GetBytes(asciiString);
cp866Bytes = System.Text.Encoding.Convert(System.Text.Encoding.GetEncoding(1251), System.Text.Encoding.GetEncoding(866), asciiBytes);
cp866Chars = new char[System.Text.Encoding.GetEncoding(866).GetCharCount(cp866Bytes, 0, cp866Bytes.Length)];
System.Text.Encoding.GetEncoding(866).GetChars(cp866Bytes, 0, cp866Bytes.Length, cp866Chars, 0);
cp866String = new string(cp866Chars);
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = cp866String;
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
asciiString = "абвгдежзийклмнопрстуфхцчшщъыьэюя";
asciiBytes = System.Text.Encoding.GetEncoding(1251).GetBytes(asciiString);
cp866Bytes = System.Text.Encoding.Convert(System.Text.Encoding.GetEncoding(1251), System.Text.Encoding.GetEncoding(866), asciiBytes);
cp866Chars = new char[System.Text.Encoding.GetEncoding(866).GetCharCount(cp866Bytes, 0, cp866Bytes.Length)];
System.Text.Encoding.GetEncoding(866).GetChars(cp866Bytes, 0, cp866Bytes.Length, cp866Chars, 0);
cp866String = new string(cp866Chars);
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = cp866String;
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
asciiString = "ЁёҐґІіЇїЄє№";
asciiBytes = System.Text.Encoding.GetEncoding(1251).GetBytes(asciiString);
asciiBytes[2] = 0x0c3; // 0x0a5 (165) -> 0x0c3 (195) Ґ -> Г
asciiBytes[3] = 0x0e3; // 0x0b4 (180) -> 0x0e3 (227) ґ -> г
asciiBytes[4] = 0x0a1; // 0x0b2 (178) -> 0x0a1 (161) І -> Ў
asciiBytes[5] = 0x0a2; // 0x0b3 (179) -> 0x0a2 (162) і -> ў
asciiBytes[6] = 0x0b0; // 0x0af (175) -> 0x0b0 (176) Ї -> °
asciiBytes[7] = 0x0b7; // 0x0bf (191) -> 0x0b7 (183) ї -> ·
asciiBytes[8] = 0x0af; // 0x0aa (170) -> 0x0af (175) Є -> Ї
asciiBytes[9] = 0x0bf; // 0x0ba (186) -> 0x0bf (191) є -> ї
cp866Bytes = System.Text.Encoding.Convert(System.Text.Encoding.GetEncoding(1251), System.Text.Encoding.GetEncoding(866), asciiBytes);
cp866Chars = new char[System.Text.Encoding.GetEncoding(866).GetCharCount(cp866Bytes, 0, cp866Bytes.Length)];
System.Text.Encoding.GetEncoding(866).GetChars(cp866Bytes, 0, cp866Bytes.Length, cp866Chars, 0);
cp866Chars[7] = (char)0x02219; // •
cp866String = new string(cp866Chars);
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = cp866String;
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = tmpString = CP1251ToCP866("Line# 2 Линия № 2 Лінія № 2");
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
fstr_out_866.WriteLine(tmpString);
#if TEST_DBF_JOIN
cmd.CommandType = CommandType.Text;
/*
* cmd.CommandText=@"
* select
* m.*,
* d_l_I.*,
* d_l_II.*
* from
* master m
* left outer join det_l_I d_l_I on (d_l_I.Master_ID=m.ID)
* left outer join det_l_II d_l_II on (d_l_II.Master_ID=m.ID) and (d_l_II.det_l_I_ID=d_l_I.ID)
* order by m.ID, d_l_I.ID, d_l_II.ID
* ";
*/
/*
* cmd.CommandText=@"
* select
* m.*,
* d_l_I.*,
* d_l_II.*
* from
* master m
* left outer join det_l_I d_l_I on (d_l_I.Master_ID=m.ID)
* left outer join det_l_II d_l_II on (d_l_II.Master_ID=d_l_I.Master_ID) and (d_l_II.det_l_I_ID=d_l_I.ID)
* order by m.ID, d_l_I.ID, d_l_II.ID
* ";
*/
cmd.CommandText = @"
select
m.*,
d_l_I.*,
d_l_II.*
from
master m
join det_l_I d_l_I on (d_l_I.Master_ID=m.ID)
join det_l_II d_l_II on (d_l_II.Master_ID=m.ID) and (d_l_II.det_l_I_ID=d_l_I.ID)
order by m.ID, d_l_I.ID, d_l_II.ID
";
/*
* cmd.CommandText=@"
* select
* m.*,
* d_l_I.*,
* d_l_II.*
* from
* master m
* join det_l_I d_l_I on (d_l_I.Master_ID=m.ID)
* join det_l_II d_l_II on (d_l_II.Master_ID=d_l_I.Master_ID) and (d_l_II.det_l_I_ID=d_l_I.ID)
* order by m.ID, d_l_I.ID, d_l_II.ID
* ";
*/
if (da == null)
{
da = new OleDbDataAdapter(cmd);
}
else
{
da.SelectCommand = cmd;
}
if (tmpDataTable == null)
{
tmpDataTable = new DataTable();
}
else
{
tmpDataTable.Reset();
}
da.Fill(tmpDataTable);
tmpDataTable.Reset();
#endif
conn.Close();
#endif
#if TEST_VFP
if (!PathToDbf.EndsWith(Path.DirectorySeparatorChar.ToString()))
{
PathToDbf += Path.DirectorySeparatorChar;
}
tmpString = "Provider=vfpoledb.1;Data Source=" + PathToDbf; //Collating Sequence=general";
conn = new OleDbConnection(tmpString);
conn.Open();
fstr_out.WriteLine("ConnectionString: " + conn.ConnectionString);
fstr_out.WriteLine("ConnectionTimeout: " + conn.ConnectionTimeout.ToString());
fstr_out.WriteLine("Database: " + conn.Database);
fstr_out.WriteLine("DataSource: " + conn.DataSource);
fstr_out.WriteLine("Provider: " + conn.Provider);
fstr_out.WriteLine("ServerVersion: " + conn.ServerVersion);
fstr_out.WriteLine("State: " + conn.State.ToString());
fstr_out.WriteLine();
tmpString = PathToDbf + CommonDbfTableName + ".dbf";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = CommonDbfTableSQLCreate;
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into " + CommonDbfTableName + " (FInt, FChar) values (1, 'Line# 1 Линия № 1 Лінія № 1')";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into " + CommonDbfTableName + " (FInt, FChar, FDate1, FDate2) values (?, ?, ?, ?)";
cmd.Parameters.Clear();
cmd.Parameters.Add("FInt", OleDbType.Integer).Value = 2;
cmd.Parameters.Add("FChar", OleDbType.VarChar).Value = "Line# 2 Линия № 2 Лінія № 2";
cmd.Parameters.Add("FDate1", OleDbType.DBDate).Value = DateTime.Now;
cmd.Parameters.Add("FDate2", OleDbType.Date).Value = DateTime.Now;
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into test_t3 (FDate1, FDate2, FChar1) values (?, ?, ?)";
cmd.Parameters.Clear();
cmd.Parameters.Add("FDate1", OleDbType.Date).Value = DateTime.Now;
cmd.Parameters.Add("FDate2", OleDbType.DBDate).Value = DateTime.Now;
cmd.Parameters.Add("FChar1", OleDbType.Char).Value = CP1251ToCP866("Line# 2 Линия № 2 Лінія № 2");
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into test_t3 (FDate1, FDate2, FChar1) values ({12/31/2008},{12/31/2008},'" + CP1251ToCP866("Line# 2 Линия № 2 Лінія № 2") + "')";
cmd.ExecuteNonQuery();
DbfFileName = "test_ins";
cmd.CommandText = "delete from " + DbfFileName;
cmd.ExecuteNonQuery();
cmd.CommandText = "select max(ID) from " + DbfFileName;
tmpObject = cmd.ExecuteScalar();
_ID_ = !Convert.IsDBNull(tmpObject) ? Convert.ToInt32(tmpObject) + 1 : 1;
cmd.CommandText = "insert into " + DbfFileName + " (ID, FCHAR, DDATE, FNUMERIC) values (?, ?, ?, ?)";
cmd.Parameters.Clear();
cmd.Parameters.Add("ID", OleDbType.Numeric).Value = _ID_;
cmd.Parameters.Add("FCHAR", OleDbType.VarChar).Value = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
cmd.Parameters.Add("DDATE", OleDbType.Date).Value = DateTime.Now;
cmd.Parameters.Add("FNUMERIC", OleDbType.Numeric).Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "abcdefghijklmnopqrstuvwxyz";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "АБВГДЕЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "абвгдежзийклмнопрстуфхцчшщъыьэюя";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = "ЁёҐґІіЇїЄє№";
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
asciiString = "АБВГДЕЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ";
asciiBytes = System.Text.Encoding.GetEncoding(1251).GetBytes(asciiString);
cp866Bytes = System.Text.Encoding.Convert(System.Text.Encoding.GetEncoding(1251), System.Text.Encoding.GetEncoding(866), asciiBytes);
cp866Chars = new char[System.Text.Encoding.GetEncoding(866).GetCharCount(cp866Bytes, 0, cp866Bytes.Length)];
System.Text.Encoding.GetEncoding(866).GetChars(cp866Bytes, 0, cp866Bytes.Length, cp866Chars, 0);
cp866String = new string(cp866Chars);
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = cp866String;
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
asciiString = "абвгдежзийклмнопрстуфхцчшщъыьэюя";
asciiBytes = System.Text.Encoding.GetEncoding(1251).GetBytes(asciiString);
cp866Bytes = System.Text.Encoding.Convert(System.Text.Encoding.GetEncoding(1251), System.Text.Encoding.GetEncoding(866), asciiBytes);
cp866Chars = new char[System.Text.Encoding.GetEncoding(866).GetCharCount(cp866Bytes, 0, cp866Bytes.Length)];
System.Text.Encoding.GetEncoding(866).GetChars(cp866Bytes, 0, cp866Bytes.Length, cp866Chars, 0);
cp866String = new string(cp866Chars);
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = cp866String;
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
asciiString = "ЁёҐґІіЇїЄє№";
asciiBytes = System.Text.Encoding.GetEncoding(1251).GetBytes(asciiString);
asciiBytes[2] = 0x0c3; // 0x0a5 (165) -> 0x0c3 (195) Ґ -> Г
asciiBytes[3] = 0x0e3; // 0x0b4 (180) -> 0x0e3 (227) ґ -> г
asciiBytes[4] = 0x0a1; // 0x0b2 (178) -> 0x0a1 (161) І -> Ў
asciiBytes[5] = 0x0a2; // 0x0b3 (179) -> 0x0a2 (162) і -> ў
asciiBytes[6] = 0x0b0; // 0x0af (175) -> 0x0b0 (176) Ї -> °
asciiBytes[7] = 0x0b7; // 0x0bf (191) -> 0x0b7 (183) ї -> ·
asciiBytes[8] = 0x0af; // 0x0aa (170) -> 0x0af (175) Є -> Ї
asciiBytes[9] = 0x0bf; // 0x0ba (186) -> 0x0bf (191) є -> ї
cp866Bytes = System.Text.Encoding.Convert(System.Text.Encoding.GetEncoding(1251), System.Text.Encoding.GetEncoding(866), asciiBytes);
cp866Chars = new char[System.Text.Encoding.GetEncoding(866).GetCharCount(cp866Bytes, 0, cp866Bytes.Length)];
System.Text.Encoding.GetEncoding(866).GetChars(cp866Bytes, 0, cp866Bytes.Length, cp866Chars, 0);
cp866Chars[7] = (char)0x02219;
cp866String = new string(cp866Chars);
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = cp866String;
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
cmd.Parameters["ID"].Value = ++_ID_;
cmd.Parameters["FCHAR"].Value = CP1251ToCP866("Line# 2 Линия № 2 Лінія № 2");
cmd.Parameters["DDATE"].Value = DateTime.Now;
cmd.Parameters["FNUMERIC"].Value = 99.999 * _ID_;
cmd.ExecuteNonQuery();
if (tmpDataTable == null)
{
tmpDataTable = new DataTable();
}
else
{
tmpDataTable.Reset();
}
cmd.CommandText = "select * from " + CommonDbfTableName;
if (da == null)
{
da = new OleDbDataAdapter(cmd);
}
else
{
da.SelectCommand = cmd;
}
da.Fill(tmpDataTable);
tmpString = "select * from baza";
cmd = new OleDbCommand(tmpString, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; ++i)
{
fstr_out.Write(rdr[rdr.GetName(i)]);
if (i < rdr.FieldCount)
{
fstr_out.Write("\t");
}
}
fstr_out.WriteLine();
}
rdr.Close();
fstr_out.WriteLine();
if (tmpDataTable == null)
{
tmpDataTable = new DataTable();
}
else
{
tmpDataTable.Reset();
}
if (da == null)
{
da = new OleDbDataAdapter(cmd);
}
else
{
da.SelectCommand = cmd;
}
da.Fill(tmpDataTable);
foreach (DataRow _r_ in tmpDataTable.Rows)
{
foreach (DataColumn _c_ in tmpDataTable.Columns)
{
fstr_out.Write(_r_[_c_.ColumnName] + "\t");
}
fstr_out.WriteLine();
}
fstr_out.WriteLine();
cmd.CommandText = "select * from test_ins";
da.SelectCommand = cmd;
tmpDataTable.Reset();
da.Fill(tmpDataTable);
foreach (DataRow _r_ in tmpDataTable.Rows)
{
foreach (DataColumn _c_ in tmpDataTable.Columns)
{
fstr_out.Write(_r_[_c_.ColumnName] + "\t");
}
fstr_out.WriteLine();
}
fstr_out.WriteLine();
tmpString = "select * from t";
cmd = new OleDbCommand(tmpString, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; ++i)
{
fstr_out.Write(rdr[rdr.GetName(i)]);
if (i < rdr.FieldCount)
{
fstr_out.Write("\t");
}
}
fstr_out.WriteLine();
}
rdr.Close();
fstr_out.WriteLine();
cmd.CommandType = CommandType.Text;
/*
* cmd.CommandText=@"
* select
* m.*,
* d_l_I.*,
* d_l_II.*
* from
* master m
* left outer join det_l_I d_l_I on (d_l_I.Master_ID=m.ID)
* left outer join det_l_II d_l_II on (d_l_II.Master_ID=m.ID) and (d_l_II.det_l_I_ID=d_l_I.ID)
* order by m.ID, d_l_I.ID, d_l_II.ID
* ";
*/
/*
* cmd.CommandText=@"
* select
* m.*,
* d_l_I.*,
* d_l_II.*
* from
* master m
* left outer join det_l_I d_l_I on (d_l_I.Master_ID=m.ID)
* left outer join det_l_II d_l_II on (d_l_II.Master_ID=d_l_I.Master_ID) and (d_l_II.det_l_I_ID=d_l_I.ID)
* order by m.ID, d_l_I.ID, d_l_II.ID
* ";
*/
cmd.CommandText = @"
select
m.*,
d_l_I.*,
d_l_II.*
from
master m
join det_l_I d_l_I on (d_l_I.Master_ID=m.ID)
join det_l_II d_l_II on (d_l_II.Master_ID=m.ID) and (d_l_II.det_l_I_ID=d_l_I.ID)
order by m.ID, d_l_I.ID, d_l_II.ID
";
/*
* cmd.CommandText=@"
* select
* m.*,
* d_l_I.*,
* d_l_II.*
* from
* master m
* join det_l_I d_l_I on (d_l_I.Master_ID=m.ID)
* join det_l_II d_l_II on (d_l_II.Master_ID=d_l_I.Master_ID) and (d_l_II.det_l_I_ID=d_l_I.ID)
* order by m.ID, d_l_I.ID, d_l_II.ID
* ";
*/
if (da == null)
{
da = new OleDbDataAdapter(cmd);
}
else
{
da.SelectCommand = cmd;
}
if (tmpDataTable == null)
{
tmpDataTable = new DataTable();
}
else
{
tmpDataTable.Reset();
}
da.Fill(tmpDataTable);
tmpDataTable.Reset();
conn.Close();
#endif
Result = 0;
}
catch (Exception eException)
{
Console.WriteLine(eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace);
}
}
finally
{
#if TEST_DBF_BY_ODBC
if (odbc_rdr != null && !odbc_rdr.IsClosed)
{
odbc_rdr.Close();
}
if (odbc_conn != null && odbc_conn.State == System.Data.ConnectionState.Open)
{
odbc_conn.Close();
}
#endif
if (rdr != null && !rdr.IsClosed)
{
rdr.Close();
}
if (conn != null && conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
if (fstr_out != null)
{
fstr_out.Close();
}
if (fstr_out_866 != null)
{
fstr_out_866.Close();
}
}
return(Result);
}
protected void b1_Click(object sender,
ToolBarButtonClickEventArgs e)
{
if (e.Button == b1)
{
//Calling the Connection method of the connect.cs file for establishing connection to the //database..
FrmConnect c = new FrmConnect();
c.ShowDialog();
connection = c.Connection();
statusBar.Text = "Connection established";
b1.Enabled = false;
b2.Enabled = true;
b3.Enabled = true;
b4.Enabled = true;
b5.Enabled = false;
}
if (e.Button == b2)
{
//Closing the connection...
connection.Close();
b1.Enabled = true;
b2.Enabled = false;
b3.Enabled = false;
b4.Enabled = false;
b5.Enabled = false;
t1.Text = "";
lv.Clear();
lowerlimit = 1;
upperlimit = 5;
dispint = 0;
}
if (e.Button == b3)
{
//Setting the lower limit and upperlimit for no of records to be displayed per page...
//Retrieving all the tables present in the database....
lowerlimit = 1;
upperlimit = 5;
lv.Clear();
flag = "usertables";
try
{
string query = "select * from (select a.* ,rownum rnum from (select * from tab) a where rownum<=" + upperlimit + " ) where rnum>=" + lowerlimit;
OdbcCommand myCommand = new OdbcCommand(query, connection);
myCommand.Connection.Open();
OdbcDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
int count = myReader.FieldCount - 1;
lv.Columns.Add("Row No.", 100, HorizontalAlignment.Center);
for (int i = 0; i < count; i++)
{
lv.Columns.Add(myReader.GetName(i), 100, HorizontalAlignment.Center);
}
dispint = 0;
while (myReader.Read())
{
if (!myReader.IsDBNull(0))
{
lv.Items.Add((dispint + 1).ToString());
}
for (int i = 0; i < count; i++)
{
if (!myReader.IsDBNull(i))
{
lv.Items[dispint].SubItems.Add(myReader.GetValue(i).ToString());
}
}
dispint++;
}
myReader.Close();
b5.Enabled = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
if (e.Button == b4)
{
lowerlimit = 1;
upperlimit = 5;
flag = "execute";
try
{
string a = t1.Text;
//For executing only the select query...
if (a.IndexOf("select") != -1)
{
lv.Clear();
string query = "select * from (select a.* ,rownum rnum from (" + t1.Text + ") a where rownum<=" + upperlimit + " ) where rnum>=" + lowerlimit;
OdbcCommand myCommand = new OdbcCommand(query, connection);
myCommand.Connection.Open();
OdbcDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
int count = myReader.FieldCount - 1;
lv.Columns.Add("Row No.", 100, HorizontalAlignment.Center);
for (int i = 0; i < count; i++)
{
lv.Columns.Add(myReader.GetName(i), 100, HorizontalAlignment.Center);
}
dispint = 0;
while (myReader.Read())
{
if (!myReader.IsDBNull(0))
{
lv.Items.Add((dispint + 1).ToString());
}
for (int i = 0; i < count; i++)
{
if (!myReader.IsDBNull(i))
{
lv.Items[dispint].SubItems.Add(myReader.GetValue(i).ToString());
}
}
dispint++;
}
myReader.Close();
b5.Enabled = true;
}
//For executing any query like insert,create etc...
else
{
string nonquery = t1.Text;
OdbcCommand mynonqueryCommand = new OdbcCommand(nonquery, connection);
mynonqueryCommand.Connection.Open();
mynonqueryCommand.ExecuteNonQuery();
mynonqueryCommand.Connection.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
if (e.Button == b5)
{
lv.Clear();
try
{
lowerlimit = upperlimit + 1;
upperlimit = upperlimit + increment;
//For displaying the records of the select query five records per page...
//When the last record is displayed the NextRowSet button is disabled...
if (flag == "execute")
{
string abc = t1.Text;
string xyz = abc.Substring(abc.IndexOf("from"));
string querycount = "select count(*) " + xyz;
OdbcCommand countcommand1 = new OdbcCommand(querycount, connection);
countcommand1.Connection.Open();
OdbcDataReader countreader1 = countcommand1.ExecuteReader(CommandBehavior.CloseConnection);
countreader1.Read();
int counttext = countreader1.GetInt32(0);
countreader1.Close();
countcommand1.Connection.Close();
if (counttext <= upperlimit)
{
upperlimit = counttext;
b5.Enabled = false;
}
string query = "select * from (select a.* ,rownum rnum from (" + t1.Text + ") a where rownum<=" + upperlimit + ") where rnum>=" + lowerlimit + "";
OdbcCommand myCommand = new OdbcCommand(query, connection);
myCommand.Connection.Open();
OdbcDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
int count = myReader.FieldCount - 1;
lv.Columns.Add("Row No.", 100, HorizontalAlignment.Center);
for (int i = 0; i < count; i++)
{
lv.Columns.Add(myReader.GetName(i), 100, HorizontalAlignment.Center);
}
dispint = 0;
int rowid = lowerlimit;
while (myReader.Read())
{
if (!myReader.IsDBNull(0))
{
lv.Items.Add((rowid++).ToString());
}
for (int i = 0; i < count; i++)
{
if (!myReader.IsDBNull(i))
{
lv.Items[dispint].SubItems.Add(myReader.GetValue(i).ToString());
}
}
dispint++;
}
myReader.Close();
}
//For displaying all the tables present in the database five per page....
//When the last table is displayed the NextRowSet button is disabled...
if (flag == "usertables")
{
string query1count = "select count(*) from tab";
OdbcCommand countcommand = new OdbcCommand(query1count, connection);
countcommand.Connection.Open();
OdbcDataReader countreader = countcommand.ExecuteReader(CommandBehavior.CloseConnection);
countreader.Read();
int counttab = countreader.GetInt32(0);
countreader.Close();
countcommand.Connection.Close();
if (counttab <= upperlimit)
{
upperlimit = counttab;
b5.Enabled = false;
}
string query1 = "select * from (select a.* ,rownum rnum from (select * from tab) a where rownum<=" + upperlimit + ") where rnum>=" + lowerlimit + "";
OdbcCommand myCommand1 = new OdbcCommand(query1, connection);
myCommand1.Connection.Open();
OdbcDataReader myReader1 = myCommand1.ExecuteReader(CommandBehavior.CloseConnection);
int count1 = myReader1.FieldCount - 1;
lv.Columns.Add("Row No.", 100, HorizontalAlignment.Center);
for (int i = 0; i < count1; i++)
{
lv.Columns.Add(myReader1.GetName(i), 100, HorizontalAlignment.Center);
}
dispint = 0;
int rowid1 = lowerlimit;
while (myReader1.Read())
{
if (!myReader1.IsDBNull(0))
{
lv.Items.Add((rowid1++).ToString());
}
for (int i = 0; i < count1; i++)
{
if (!myReader1.IsDBNull(i))
{
lv.Items[dispint].SubItems.Add(myReader1.GetValue(i).ToString());
}
}
dispint++;
}
myReader1.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
void F_comment()
{
EMPLOYEE_NO = Request.QueryString["EMPLOYEE_NO"];
DEPARTMENT_NO = Request.QueryString["DEP_NO"];
ICMANAGER = Request.QueryString["ICM"];
MANAGER = Request.QueryString["MNG"];
SINMANAGER = Request.QueryString["SMNG"];
if (ICMANAGER == EMPLOYEE_NO)
{
string connectionString = ConfigurationManager.ConnectionStrings["PetroneedsConnectionString"].ConnectionString;
EMPLOYEE_NO = Request.QueryString["EMPLOYEE_NO"];
DEPARTMENT_NO = Request.QueryString["DEP_NO"];
string sql = "SELECT ICT_MSTR.FORM_NO,ICT_MSTR.SMNG_COMMENT,ICT_MSTR.MNG_COMMENT FROM ICT_MSTR WHERE ICT_MSTR.FORM_NO='" + TextBox1.Text + "'";
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand(sql, conn);
cmd.Connection.Open();
OdbcDataReader read = cmd.ExecuteReader();
read.Read();
if (read.HasRows)
{
TextBox3.Text = read["MNG_COMMENT"].ToString();
TextBox4.Text = read["SMNG_COMMENT"].ToString();
TextBox3.Visible = true;
Label6.Visible = true;
TextBox4.Visible = true;
Label1.Visible = true;
}
cmd.Connection.Close();
}
else
if (MANAGER == EMPLOYEE_NO & SINMANAGER != EMPLOYEE_NO)
{
string connectionString = ConfigurationManager.ConnectionStrings["PetroneedsConnectionString"].ConnectionString;
EMPLOYEE_NO = Request.QueryString["EMPLOYEE_NO"];
DEPARTMENT_NO = Request.QueryString["DEP_NO"];
string sql = "SELECT ICT_MSTR.FORM_NO,ICT_MSTR.SMNG_COMMENT,ICT_MSTR.MNG_COMMENT FROM ICT_MSTR WHERE ICT_MSTR.FORM_NO='" + TextBox1.Text + "'";
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand(sql, conn);
cmd.Connection.Open();
OdbcDataReader read = cmd.ExecuteReader();
read.Read();
if (read.HasRows)
{
TextBox3.Text = read["MNG_COMMENT"].ToString();
TextBox4.Text = read["SMNG_COMMENT"].ToString();
TextBox3.Visible = false;
Label6.Visible = false;
TextBox4.Visible = false;
Label1.Visible = false;
}
cmd.Connection.Close();
}
/*else
* if (MANAGER == EMPLOYEE_NO & SINMANAGER == EMPLOYEE_NO)
* {
* string connectionString = ConfigurationManager.ConnectionStrings["PetroneedsConnectionString"].ConnectionString;
* EMPLOYEE_NO = Request.QueryString["EMPLOYEE_NO"];
* DEPARTMENT_NO = Request.QueryString["DEP_NO"];
* string sql = "SELECT ICT_MSTR.FORM_NO,ICT_MSTR.SMNG_COMMENT,ICT_MSTR.MNG_COMMENT FROM ICT_MSTR WHERE ICT_MSTR.FORM_NO='" + TextBox1.Text + "'";
*
* OdbcConnection conn = new OdbcConnection(connectionString);
* OdbcCommand cmd = new OdbcCommand(sql, conn);
*
* cmd.Connection.Open();
* OdbcDataReader read = cmd.ExecuteReader();
* read.Read();
*
* if (read.HasRows)
* {
* TextBox3.Text = read["MNG_COMMENT"].ToString();
* TextBox4.Text = read["SMNG_COMMENT"].ToString();
*
* TextBox3.Visible = false;
* Label6.Visible = false;
* TextBox4.Visible = false;
* Label1.Visible = false;
*
* }
* cmd.Connection.Close();
* }*/
else
if (MANAGER == EMPLOYEE_NO & SINMANAGER == EMPLOYEE_NO | SINMANAGER == EMPLOYEE_NO)
{
string connectionString = ConfigurationManager.ConnectionStrings["PetroneedsConnectionString"].ConnectionString;
EMPLOYEE_NO = Request.QueryString["EMPLOYEE_NO"];
DEPARTMENT_NO = Request.QueryString["DEP_NO"];
string sql = "SELECT ICT_MSTR.FORM_NO,ICT_MSTR.SMNG_COMMENT,ICT_MSTR.MNG_COMMENT FROM ICT_MSTR WHERE ICT_MSTR.FORM_NO='" + TextBox1.Text + "'";
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand(sql, conn);
cmd.Connection.Open();
OdbcDataReader read = cmd.ExecuteReader();
read.Read();
if (read.HasRows)
{
TextBox3.Text = read["MNG_COMMENT"].ToString();
//TextBox4.Text = read["SMNG_COMMENT"].ToString();
TextBox4.Visible = false;
Label1.Visible = false;
TextBox3.Visible = true;
Label6.Visible = true;
}
cmd.Connection.Close();
}
}
private void create_SearchedDgv(string ConnectionString, string identify_Num)
{
searchedDataView.Columns.Add("Model", "Model");
searchedDataView.Columns.Add("Tester", "Tester");
searchedDataView.Columns.Add("Start_time", "Start_time");
searchedDataView.Columns.Add("End_time", "End_time");
searchedDataView.Columns.Add("Serial_number", "Serial_number");
searchedDataView.Columns.Add("Barcode", "Barcode");
searchedDataView.Columns.Add("Total_result", "Total_result");
string queryString = "Select * from Test_Data";
string model_Name = "";
List <string> parsingData = new List <string>();
if (identify_Num != "")
{
queryString += " where Serial_number='" + identify_Num + "' or Barcode='" + identify_Num + "'";
}
OdbcCommand command = new OdbcCommand(queryString);
using (OdbcConnection connection = new OdbcConnection(ConnectionString))
{
command.Connection = connection;
connection.Open();
OdbcDataReader dr = command.ExecuteReader();
while (dr.Read())
{
model_Name = dr["Model_name"].ToString();
break;
}
}
if (identify_Num != "" && !model_Name.Equals(""))
{
queryString = "Select * from model where name='" + model_Name + "'";
}
else
{
MessageBox.Show("존재하지 않거나 잘못된 번호입니다.");
return;
}
command.CommandText = queryString;
using (OdbcConnection connection = new OdbcConnection(ConnectionString))
{
command.Connection = connection;
connection.Open();
OdbcDataReader dr = command.ExecuteReader();
while (dr.Read())
{
string[] dataHeader = dr["data_header"].ToString().Split(';');
for (int i = 0; i < dataHeader.Length; i++)
{
if (parsingData.Contains(dataHeader[i]))
{
continue;
}
else if (dataHeader[i].Equals(""))
{
break;
}
else
{
parsingData.Add(dataHeader[i]);
}
}
}
foreach (string parsingStr in parsingData)
{
searchedDataView.Columns.Add(parsingStr, parsingStr);
}
}
}
/// <summary>
/// Implementation of <see cref="IWorkflowScript.OnWorkflowScriptExecute" />.
/// <seealso cref="IWorkflowScript" />
/// </summary>
/// <param name="app"></param>
/// <param name="args"></param>
public void OnWorkflowScriptExecute(Hyland.Unity.Application app, Hyland.Unity.WorkflowEventArgs args)
{
try
{
// Initialize global settings
IntializeScript(ref app, ref args);
//get and clean LicenseType and Case # keywords for passing to LicEase database
KeywordType kwtCaseNum = _currentDocument.DocumentType.KeywordRecordTypes.FindKeywordType(gParamCaseNum);
string strCaseNum = "";
if (kwtCaseNum != null)
{
KeywordRecord keyRecFileNum = _currentDocument.KeywordRecords.Find(kwtCaseNum);
if (keyRecFileNum != null)
{
Keyword kwdFileNum = keyRecFileNum.Keywords.Find(kwtCaseNum);
if (kwdFileNum != null)
{
strCaseNum = CleanSeedKW(kwdFileNum.ToString());
}
}
}
KeywordType kwtLicenseType = _currentDocument.DocumentType.KeywordRecordTypes.FindKeywordType(gParamLicType);
string strLicenseType = "";
if (kwtLicenseType != null)
{
KeywordRecord keyRecLicenseType = _currentDocument.KeywordRecords.Find(kwtLicenseType);
if (keyRecLicenseType != null)
{
Keyword kwdLicenseType = keyRecLicenseType.Keywords.Find(kwtLicenseType);
if (kwdLicenseType != null)
{
strLicenseType = CleanSeedKW(kwdLicenseType.ToString());
}
}
}
if ((strCaseNum == "") || (strLicenseType == ""))
{
throw new Exception(string.Format("Either {0} or {1} is blank.", gParamCaseNum, gParamLicType));
}
//access Config Item for LicEase User
string gUSER = "";
if (app.Configuration.TryGetValue("LicEaseUser", out gUSER))
{
}
//access Config Item for LicEase Password
string gPASS = "";
if (app.Configuration.TryGetValue("LicEasePassword", out gPASS))
{
}
/* COMMENT THIS SECTION OUT WHEN MOVING TO PROD */
//access Config Item for LicEase UAT ODBC
string gODBC = "";
if (app.Configuration.TryGetValue("LicEaseUAT", out gODBC))
{
}
/* UNCOMMENT THIS SECTION WHEN MOVING TO PROD
* //access Config Item for LicEase PROD ODBC
* string gODBC = "";
* if (app.Configuration.TryGetValue("LicEasePROD", out gODBC))
* {
* }
*/
string connectionString = string.Format("DSN={0};Uid={1};Pwd={2};", gODBC, gUSER, gPASS);
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Connection string: {0}", connectionString));
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT ");
strSql.Append(@" (SELECT clnt.clnt_lng_nme ");
strSql.Append(@" FROM clnt clnt ");
strSql.Append(@" WHERE a.clnt_cde = clnt.clnt_cde) AS Profession, ");
strSql.Append(@" (SELECT clnt3.clnt_lng_nme ");
strSql.Append(@" FROM clnt clnt2, clnt clnt3 ");
strSql.Append(@" WHERE a.clnt_cde = clnt2.clnt_cde ");
strSql.Append(@" AND clnt2.clnt_cde_prnt = clnt3.clnt_cde) AS BOARD, ");
strSql.Append(@" (SELECT x.key_nme ");
strSql.Append(@" FROM invl_party y, link u, clnt_link_typ v, link_typ w, name x ");
strSql.Append(@" WHERE a.cmpln_id = y.cmpln_id ");
strSql.Append(@" AND y.xent_id = u.xent_id ");
strSql.Append(@" AND y.invl_party_id = u.prnt_id ");
strSql.Append(@" AND u.link_prnt_cde = 'I' ");
strSql.Append(@" AND u.curr_ind = 'Y' ");
strSql.Append(@" AND u.clnt_link_typ_id = v.clnt_link_typ_id ");
strSql.Append(@" AND v.link_typ_id = w.link_typ_id ");
strSql.Append(@" AND w.link_typ_cde = 'CM' ");
strSql.Append(@" AND u.nme_id = x.nme_id ");
strSql.Append(@" AND x.ent_nme_typ = 'R' ");
strSql.Append(@" AND x.cur_nme_ind = 'Y') as COMPLAINANT, ");
strSql.Append(@" (SELECT n.key_nme ");
strSql.Append(@" FROM rspn r, link l, clnt_link_typ clt, link_typ lt, name n ");
strSql.Append(@" WHERE a.cmpln_id = r.cmpln_id ");
strSql.Append(@" AND r.rspn_id = l.prnt_id ");
strSql.Append(@" AND l.link_prnt_cde = 'R' ");
strSql.Append(@" AND l.curr_ind = 'Y' ");
strSql.Append(@" AND l.clnt_link_typ_id = clt.clnt_link_typ_id ");
strSql.Append(@" AND clt.link_typ_id = lt.link_typ_id ");
strSql.Append(@" AND lt.link_typ_cde = 'RS' ");
strSql.Append(@" AND l.nme_id = n.nme_id ");
strSql.Append(@" AND n.ent_nme_typ = 'R' ");
strSql.Append(@" AND n.cur_nme_ind = 'Y') as SUBJECT ");
strSql.Append(@" FROM cmpln a ");
strSql.Append(@" WHERE a.clnt_cde = '");
strSql.Append(strLicenseType);
strSql.Append(@"' AND a.cmpln_nbr = '");
strSql.Append(strCaseNum);
strSql.Append(@"'");
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Sql Query: {0}", strSql.ToString()));
using (OdbcConnection con = new OdbcConnection(connectionString))
{
try
{
con.Open();
using (OdbcCommand command = new OdbcCommand(strSql.ToString(), con))
using (OdbcDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
string strBoard = "";
string strSubject = "";
string strComplainant = "";
reader.Read();
strBoard = reader["BOARD"].ToString();
strSubject = reader["SUBJECT"].ToString();
strComplainant = reader["COMPLAINANT"].ToString();
Keyword kwdBoard = null;
if (!String.IsNullOrEmpty(strBoard))
{
KeywordType kwtBoard = app.Core.KeywordTypes.Find(gSaveToBoard);
if (kwtBoard != null)
{
kwdBoard = CreateKeywordHelper(kwtBoard, strBoard);
}
}
Keyword kwdSubject = null;
if (!String.IsNullOrEmpty(strSubject))
{
KeywordType kwtSubject = app.Core.KeywordTypes.Find(gSaveToSubject);
if (kwtSubject != null)
{
kwdSubject = CreateKeywordHelper(kwtSubject, strSubject);
}
}
Keyword kwdComplainant = null;
if (!String.IsNullOrEmpty(strComplainant))
{
KeywordType kwtComplainant = app.Core.KeywordTypes.Find(gSaveToComplainant);
if (kwtComplainant != null)
{
kwdComplainant = CreateKeywordHelper(kwtComplainant, strComplainant);
}
}
using (DocumentLock documentLock = _currentDocument.LockDocument())
{
// Ensure lock was obtained
if (documentLock.Status != DocumentLockStatus.LockObtained)
{
throw new Exception("Document lock not obtained");
}
// Create keyword modifier object to hold keyword changes
KeywordModifier keyModifier = _currentDocument.CreateKeywordModifier();
// Add update keyword call to keyword modifier object
//Note Overloads available for use
//(I.E.): keyModifier.AddKeyword(keywordTypeName,keywordValue)
if (kwdBoard != null)
{
keyModifier.AddKeyword(kwdBoard);
}
if (kwdSubject != null)
{
keyModifier.AddKeyword(kwdSubject);
}
if (kwdComplainant != null)
{
keyModifier.AddKeyword(kwdComplainant);
}
// Apply keyword change to the document
keyModifier.ApplyChanges();
string output = String.Format("Keyword: '{0}' Value: '{1}', {7}Keyword: '{2}' Value: '{3}', {7}Keyword: '{4}' Value: '{5}', {7}added to Document {6}.",
gSaveToBoard, strBoard, gSaveToSubject, strSubject, gSaveToComplainant, strComplainant, _currentDocument.ID, Environment.NewLine);
//Output the results to the OnBase Diagnostics Console
app.Diagnostics.WriteIf(Hyland.Unity.Diagnostics.DiagnosticsLevel.Verbose, output);
}
}
else
{
throw new Exception(string.Format("No records found in database for {0}='{1}' and {4}{2}='{3}' ", gParamLicType, strLicenseType, gParamCaseNum, strCaseNum, Environment.NewLine));
}
}
}
catch (Exception ex)
{
throw new ApplicationException("Error during database operations!", ex);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
}
catch (Exception ex)
{
// Handle exceptions and log to Diagnostics Console and document history
HandleException(ex, ref app, ref args);
}
finally
{
// Log script execution end
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Info,
string.Format("End Script - [{0}]", ScriptName));
}
}
private void BindData()
{
string by = drp_by.SelectedItem.Text;
string val = txt_val.Text.Trim();
string drp_val = drp_type.SelectedValue;
if (by == "Asset Code")
{
OdbcCommand cmd = conn_asset.CreateCommand();
cmd.CommandText = "select custodian_p_no,fromDate,toDate from ast_cust_history where astCode='" + val + "' order by fromDate desc";
conn_asset.Open();
cmd.CommandType = CommandType.Text;
DataTable dt = new DataTable();
DataRow newRow;
OdbcDataReader dr = cmd.ExecuteReader();
dt.Columns.Add(new System.Data.DataColumn("custodian_p_no", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("fromDate", typeof(DateTime)));
dt.Columns.Add(new System.Data.DataColumn("toDate", typeof(String)));
while (dr.Read())
{
newRow = dt.NewRow();
newRow["custodian_p_no"] = Convert.ToString(dr["custodian_p_no"]);
newRow["fromDate"] = Convert.ToDateTime(dr["fromDate"]);
DateTime dat = Convert.ToDateTime(dr["toDate"]);
newRow["toDate"] = dat.ToString("yyyy/MM/dd");
dt.Rows.Add(newRow);
}
if (dt.Rows.Count > 0)
{
grid_astcode.Visible = true;
grid_pno.Visible = false;
grid_type.Visible = false;
grid_astcode.DataSource = dt;
grid_astcode.DataBind();
lbl_no_recs.Visible = false;
}
else
{
grid_astcode.Visible = false;
grid_pno.Visible = false;
grid_type.Visible = false;
lbl_no_recs.Visible = true;
}
conn_asset.Close();
}
//****************************************
if (by == "Personal No.")
{
OdbcCommand cmd = conn_asset.CreateCommand();
cmd.CommandText = "select astCode,description,issueDate from ast_master where custodian='" + val + "' order by issueDate desc ";
conn_asset.Open();
cmd.CommandType = CommandType.Text;
DataTable dt = new DataTable();
DataRow newRow;
OdbcDataReader dr = cmd.ExecuteReader();
dt.Columns.Add(new System.Data.DataColumn("astCode", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("description", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("issueDate", typeof(DateTime)));
while (dr.Read())
{
newRow = dt.NewRow();
newRow["astCode"] = Convert.ToString(dr["astCode"]);
newRow["description"] = Convert.ToString(dr["description"]);
newRow["issueDate"] = Convert.ToDateTime(dr["issueDate"]);
dt.Rows.Add(newRow);
}
if (dt.Rows.Count > 0)
{
grid_pno.Visible = true;
grid_astcode.Visible = false;
grid_type.Visible = false;
grid_pno.DataSource = dt;
grid_pno.DataBind();
lbl_no_recs.Visible = false;
}
else
{
grid_pno.Visible = false;
grid_astcode.Visible = false;
grid_type.Visible = false;
lbl_no_recs.Visible = true;
}
conn_asset.Close();
}
//****************************************
if (by == "Type")
{
OdbcCommand cmd = conn_asset.CreateCommand();
cmd.CommandText = "select astCode,description,custodian,issueDate from ast_master where category='" + drp_val + "' order by issueDate desc";
conn_asset.Open();
cmd.CommandType = CommandType.Text;
DataTable dt = new DataTable();
DataRow newRow;
OdbcDataReader dr = cmd.ExecuteReader();
dt.Columns.Add(new System.Data.DataColumn("astCode", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("description", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("custodian", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("issueDate", typeof(DateTime)));
while (dr.Read())
{
newRow = dt.NewRow();
newRow["astCode"] = Convert.ToString(dr["astCode"]);
newRow["description"] = Convert.ToString(dr["description"]);
newRow["custodian"] = Convert.ToString(dr["custodian"]);
newRow["issueDate"] = Convert.ToDateTime(dr["issueDate"]);
dt.Rows.Add(newRow);
}
if (dt.Rows.Count > 0)
{
grid_type.Visible = true;
grid_astcode.Visible = false;
grid_pno.Visible = false;
grid_type.DataSource = dt;
grid_type.DataBind();
lbl_no_recs.Visible = false;
}
else
{
grid_pno.Visible = false;
grid_astcode.Visible = false;
grid_type.Visible = false;
lbl_no_recs.Visible = true;
}
conn_asset.Close();
}
//*******************************
}
protected void drp_by_SelectedIndexChanged(object sender, EventArgs e)
{
if (drp_by.SelectedValue == "type")
{
drp_type.Visible = true;
txt_val.Visible = false;
}
else if (drp_by.SelectedValue == "all")
{
drp_type.Visible = false;
lbl_2.Visible = false;
txt_val.Visible = false;
string by = drp_by.SelectedItem.Text;
if (by == "All Assets")
{
OdbcCommand cmd = conn_asset.CreateCommand();
cmd.CommandText = "select astCode,description,custodian,issueDate from ast_master order by issueDate desc";
conn_asset.Open();
cmd.CommandType = CommandType.Text;
DataTable dt = new DataTable();
DataRow newRow;
OdbcDataReader dr = cmd.ExecuteReader();
dt.Columns.Add(new System.Data.DataColumn("astCode", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("description", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("custodian", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("issueDate", typeof(DateTime)));
while (dr.Read())
{
newRow = dt.NewRow();
newRow["astCode"] = Convert.ToString(dr["astCode"]);
newRow["description"] = Convert.ToString(dr["description"]);
newRow["custodian"] = Convert.ToString(dr["custodian"]);
newRow["issueDate"] = Convert.ToDateTime(dr["issueDate"]);
dt.Rows.Add(newRow);
}
if (dt.Rows.Count > 0)
{
grid_type.Visible = true;
grid_astcode.Visible = false;
grid_pno.Visible = false;
grid_type.DataSource = dt;
grid_type.DataBind();
lbl_no_recs.Visible = false;
}
else
{
grid_pno.Visible = false;
grid_astcode.Visible = false;
grid_type.Visible = false;
lbl_no_recs.Visible = true;
}
conn_asset.Close();
}
}
else
{
drp_type.Visible = false;
txt_val.Visible = true;
}
}
protected void btn_reg_Click(object sender, EventArgs e)
{
OdbcCommand cmd = conn_asset.CreateCommand();
cmd.CommandText = "update ast_master set amcParty = '" + drp_amcParty.SelectedValue + "' , amcStart = '" + txt_amcStart.Text + "' , amcEnd = '" + txt_amcEnd.Text + "', amcStat = 'Y' where po_no = '" + txt_po_no.Text.Trim() + "' or astCode = '" + txt_assetCode.Text.Trim().ToUpper() + "'";
int check1;
conn_asset.Open();
check1 = cmd.ExecuteNonQuery();
conn_asset.Close();
if (drp_sel.SelectedValue.Equals("po_no"))
{
/////
OdbcCommand cmd2 = conn_asset.CreateCommand();
cmd2.CommandText = "select astCode from ast_master where po_no = '" + txt_po_no.Text.Trim().ToUpper() + "'";
conn_asset.Open();
OdbcDataReader dr = cmd2.ExecuteReader();
DataTable dt = new DataTable();
DataRow newRow;
dt.Columns.Add(new System.Data.DataColumn("astCode", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("po_no", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("amcParty", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("startDate", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("endDate", typeof(String)));
while (dr.Read())
{
newRow = dt.NewRow();
newRow["astCode"] = Convert.ToString(dr["astCode"]);
newRow["po_no"] = txt_po_no.Text.Trim().ToUpper();
newRow["amcParty"] = drp_amcParty.SelectedValue;
newRow["startDate"] = txt_amcStart.Text;
newRow["endDate"] = txt_amcEnd.Text;
dt.Rows.Add(newRow);
}
conn_asset.Close();
foreach (DataRow it in dt.Rows)
{
OdbcCommand cmd1 = conn_asset.CreateCommand();
cmd1.CommandText = "insert into ast_amcMaster values('" + it["astCode"] + "','" + it["po_no"] + "','" + it["amcParty"] + "','" + it["startDate"] + "','" + it["endDate"] + "','','1900-01-01')";
int check2;
conn_asset.Open();
check2 = cmd1.ExecuteNonQuery();
conn_asset.Close();
}
/////
}
else
{
OdbcCommand cmd3 = conn_asset.CreateCommand();
cmd3.CommandText = "select po_no from ast_master where astCode = '" + txt_assetCode.Text.Trim().ToUpper() + "'";
conn_asset.Open();
OdbcDataReader dr3 = cmd3.ExecuteReader();
string po_no = "default";
if (dr3.Read())
{
po_no = dr3["po_no"].ToString();
}
OdbcCommand cmd2 = conn_asset.CreateCommand();
cmd2.CommandText = "insert into ast_amcMaster values('" + txt_assetCode.Text.Trim().ToUpper() + "','" + po_no + "','" + drp_amcParty.SelectedValue + "','" + txt_amcStart.Text + "','" + txt_amcEnd.Text + "','','1900-01-01')";
int check2;
conn_asset.Open();
check2 = cmd2.ExecuteNonQuery();
conn_asset.Close();
}
if (check1 >= 1)
{
lbl_error.ForeColor = System.Drawing.Color.Green;
conn_asset.Close();
lbl_error.Text = "Registered successfully...";
txt_assetCode.Text = "";
txt_po_no.Text = "";
}
else
{
lbl_error.ForeColor = System.Drawing.Color.Red;
lbl_error.Text = "Registration failed";
lbl_error.Visible = true;
txt_assetCode.Text = "";
txt_po_no.Text = "";
}
}
protected void txt_assetCode_TextChanged(object sender, EventArgs e)
{
txt_po_no.Text = "";
//searching for asset
lbl_poDate.Text = "PO Date : ";
lbl_poDate.Visible = false;
OdbcCommand cmd = conn_asset.CreateCommand();
cmd.CommandText = "select * from ast_master where astCode='" + txt_assetCode.Text.Trim().ToUpper() + "'";
conn_asset.Open();
OdbcDataReader dr = cmd.ExecuteReader();
string p_no = "default";
int flg = 0;
lbl_astpo.Text = "PO Date : ";
lbl_custPNo.Text = "Custodian's Personal No. : ";
lbl_custName.Text = "Custodian's Name : ";
lbl_dept.Text = "Department : ";
while (dr.Read())
{
DateTime po_dt = Convert.ToDateTime(dr["poDate"]);
lbl_astpo.Text += po_dt.ToString("yyyy/MM/dd");
lbl_astpo.Visible = true;
p_no = dr["custodian"].ToString();
lbl_custPNo.Text += p_no;
lbl_custPNo.Visible = true;
lbl_dept.Text += dr["dept"].ToString();
lbl_dept.Visible = true;
flg = 1;
img_p.ImageUrl = "~/Images/" + p_no + ".jpg";
img_p.Visible = true;
}
if (flg == 1)
{
conn_asset.Close();
OdbcCommand cmda = conn_asset.CreateCommand();
cmda.CommandText = "select * from ast_empMaster where p_no='" + p_no.Trim().ToUpper() + "'";
conn_asset.Open();
OdbcDataReader dr1 = cmda.ExecuteReader();
while (dr1.Read())
{
lbl_custName.Text += dr1["name"].ToString();
lbl_custName.Visible = true;
}
btn_reg.Enabled = true;
btn_reg.BackColor = System.Drawing.Color.LightSteelBlue;
btn_reg.ForeColor = System.Drawing.Color.Black;
lbl_invalid.Visible = false;
conn_asset.Close();
}
else
{
lbl_invalid.Text = "Invalid Asset Code";
lbl_invalid.Visible = true;
btn_reg.BackColor = System.Drawing.Color.Gray;
btn_reg.ForeColor = System.Drawing.Color.LightGray;
btn_reg.Enabled = false;
lbl_astpo.Visible = false;
lbl_custName.Visible = false;
lbl_custPNo.Visible = false;
lbl_dept.Visible = false;
img_p.Visible = false;
lbl_astpo.Text = "PO Date : ";
lbl_custPNo.Text = "Custodian's Personal No. : ";
lbl_custName.Text = "Custodian's Name : ";
lbl_dept.Text = "Department : ";
}
}
protected void txt_po_no_TextChanged(object sender, EventArgs e)
{
//searching by po no.
txt_assetCode.Text = "";
int total = 0;
lbl_poDate.Text = "PO Date : ";
OdbcCommand cmda = conn_asset.CreateCommand();
cmda.CommandText = "select COUNT(*) as quantity from ast_master where po_no='" + txt_po_no.Text.Trim().ToUpper() + "'";
conn_asset.Open();
OdbcDataReader dr1 = cmda.ExecuteReader();
while (dr1.Read())
{
total = Convert.ToInt32(dr1["quantity"]);
}
conn_asset.Close();
OdbcCommand cmd = conn_asset.CreateCommand();
cmd.CommandText = "select category,COUNT(*) as quantity from ast_master where po_no='" + txt_po_no.Text.Trim().ToUpper() + "' group by category";
conn_asset.Open();
OdbcDataReader dr = cmd.ExecuteReader();
DataTable dt = new DataTable();
DataRow newRow;
dt.Columns.Add(new System.Data.DataColumn("category", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("quantity", typeof(Int32)));
while (dr.Read())
{
newRow = dt.NewRow();
newRow["category"] = Convert.ToString(dr["category"]);
newRow["quantity"] = Convert.ToInt32(dr["quantity"]);
dt.Rows.Add(newRow);
}
if (dt.Rows.Count > 0)
{
conn_asset.Close();
DateTime podt = DateTime.Now;
OdbcCommand cmdb = conn_asset.CreateCommand();
cmdb.CommandText = "select poDate from ast_master where po_no='" + txt_po_no.Text.Trim().ToUpper() + "'";
conn_asset.Open();
OdbcDataReader dr2 = cmdb.ExecuteReader();
while (dr2.Read())
{
podt = Convert.ToDateTime(dr2["poDate"]);
break;
}
lbl_poDate.Text += podt.ToString("yyyy/MM/dd");
grid_display.Visible = true;
grid_display.DataSource = dt;
grid_display.DataBind();
lbl_no_recs.ForeColor = System.Drawing.Color.Green;
lbl_no_recs.Text = "Total Assets Found : " + total;
lbl_poDate.Visible = true;
lbl_no_recs.Visible = true;
btn_reg.Enabled = true;
btn_reg.BackColor = System.Drawing.Color.LightSteelBlue;
btn_reg.ForeColor = System.Drawing.Color.Black;
}
else
{
grid_display.Visible = false;
lbl_no_recs.ForeColor = System.Drawing.Color.Red;
lbl_no_recs.Text = "No Records Available";
lbl_no_recs.Visible = true;
btn_reg.BackColor = System.Drawing.Color.Gray;
btn_reg.ForeColor = System.Drawing.Color.LightGray;
btn_reg.Enabled = false;
}
conn_asset.Close();
}
protected void fetch(object sender, EventArgs e)
{
sem = semlist.SelectedValue;
year = yearlist.SelectedValue;
month = monthlist.SelectedValue;
String[] sub_array = new String[5];
table = "mca" + sem + "semattendance";
try
{
String strcon = System.Configuration.ConfigurationManager.ConnectionStrings["studentdbconnectionstring"].ConnectionString.ToString();
OdbcConnection con = new OdbcConnection(strcon);
OdbcCommand get_subjects = new OdbcCommand("Select * from " + table + " where rollno ='0' ", con);
//OdbcCommand cmd = new OdbcCommand("Select * from " + table + " where rollno ='" + rollno + "' and year='" + year + "' ", con);
con.Open();
OdbcDataReader subject = get_subjects.ExecuteReader();
// OdbcDataReader rd = cmd.ExecuteReader();
while (subject.Read())
{
sub_array[0] = subject["s1"].ToString();
sub_array[1] = subject["s2"].ToString();
sub_array[2] = subject["s3"].ToString();
sub_array[3] = subject["s4"].ToString();
sub_array[4] = subject["s5"].ToString();
//while (i < 5)
//{
// TableRow thr = new TableRow();
// TableHeaderCell sub = new TableHeaderCell();
// sub.Text = sub_array[i];
// thr.Cells.Add(sub);
// TableCell c2 = new TableCell();
// id=id+Convert.ToString(i);
// TextBox t1 = new TextBox();
// t1.ID = "s" + i;
// c2.Controls.Add(t1);
// thr.Cells.Add(c2);
// TableCell c3 = new TableCell();
// TextBox t2 = new TextBox();
// t2.ID = "t" + i;
// c3.Controls.Add(t2);
// thr.Cells.Add(c3);
// tbl_attend.Rows.Add(thr);
// i++;
//}
}
s1.Text = sub_array[0];
s2.Text = sub_array[1];
s3.Text = sub_array[2];
s4.Text = sub_array[3];
s5.Text = sub_array[4];
table1.Visible = false;
tbl_attend.Visible = true;
submit.Visible = true;
roll.Visible = true;
label.Visible = true;
con.Close();
}
catch (Exception ae)
{
Response.Write(ae);
}
}
/// <summary>
/// Implementation of <see cref="IWorkflowScript.OnWorkflowScriptExecute" />.
/// <seealso cref="IWorkflowScript" />
/// </summary>
/// <param name="app"></param>
/// <param name="args"></param>
public void OnWorkflowScriptExecute(Hyland.Unity.Application app, Hyland.Unity.WorkflowEventArgs args)
{
try
{
// Initialize global settings
IntializeScript(ref app, ref args);
//access Config Item for LicEase User
string gUSER = "";
if (app.Configuration.TryGetValue("OnBaseUser", out gUSER))
{
}
//access Config Item for LicEase Password
string gPASS = "";
if (app.Configuration.TryGetValue("OnBasePassword", out gPASS))
{
}
//access Config Item for LicEase PROD ODBC
string gODBC = "";
if (app.Configuration.TryGetValue("OnBaseDEV", out gODBC))
{
}
string strBatch = "";
if (!args.SessionPropertyBag.TryGetValue(gProp, out strBatch))
{
}
string connectionString = string.Format("DSN={0};Uid={1};Pwd={2};", gODBC, gUSER, gPASS);
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Connection string: {0}", connectionString));
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select hsi.keyitem114.keyvaluecurr AS VAL from hsi.keyitem114 join hsi.keyitem143 on hsi.keyitem114.itemnum = hsi.keyitem143.itemnum ");
strSql.Append(@" join hsi.itemdata on hsi.keyitem114.itemnum = hsi.itemdata.itemnum where hsi.itemdata.itemtypenum <> 146 and hsi.itemdata.status = 0 and hsi.keyitem143.keyvaluebig = ");
strSql.Append(strBatch);
strSql.Append(@"'");
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Verbose, string.Format("Sql Query: {0}", strSql.ToString()));
using (OdbcConnection con = new OdbcConnection(connectionString))
{
try
{
con.Open();
using (OdbcCommand command = new OdbcCommand(strSql.ToString(), con))
using (OdbcDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
lstProp.Add(reader["VAL"].ToString());
}
double total = 0;
foreach (string remit in lstProp)
{
total = total + double.Parse(remit);
}
Keyword kwdDesc = null;
if (!String.IsNullOrEmpty(total.ToString()))
{
KeywordType kwtDesc = app.Core.KeywordTypes.Find("Remittance Total");
if (kwtDesc != null)
{
kwdDesc = CreateKeywordHelper(kwtDesc, total.ToString());
}
}
using (DocumentLock documentLock = _currentDocument.LockDocument())
{
// Ensure lock was obtained
if (documentLock.Status != DocumentLockStatus.LockObtained)
{
throw new Exception("Document lock not obtained");
}
// Create keyword modifier object to hold keyword changes
KeywordModifier keyModifier = _currentDocument.CreateKeywordModifier();
// Add update keyword call to keyword modifier object
//Note Overloads available for use
//(I.E.): keyModifier.AddKeyword(keywordTypeName,keywordValue)
if (kwdDesc != null)
{
keyModifier.AddKeyword(kwdDesc);
}
// Apply keyword change to the document
keyModifier.ApplyChanges();
string output = String.Format("Keyword: '{0}' Value: '{1}', added to Document {2}.", "Remittance Total", total.ToString(), _currentDocument.ID);
//Output the results to the OnBase Diagnostics Console
app.Diagnostics.WriteIf(Hyland.Unity.Diagnostics.DiagnosticsLevel.Verbose, output);
documentLock.Release();
}
}
else
{
throw new Exception(string.Format("No records found in database"));
}
}
}
catch (Exception ex)
{
throw new ApplicationException("Error during database operations!", ex);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
}
catch (Exception ex)
{
// Handle exceptions and log to Diagnostics Console and document history
HandleException(ex, ref app, ref args);
}
finally
{
// Log script execution end
app.Diagnostics.WriteIf(Diagnostics.DiagnosticsLevel.Info,
string.Format("End Script - [{0}]", ScriptName));
}
}
protected void drp_quarter_SelectedIndexChanged(object sender, EventArgs e)
{
qDates();
lbl_error.Text = "";
lbl_error.Visible = false;
string m = drp_quarter.SelectedItem.Text;
string sDate = "";
string eDate = "";
for (int i = 0; i <= 9; i++)
{
sDate += m[i];
eDate += m[i + 14];
}
fromDate = Convert.ToDateTime(sDate);
toDate = Convert.ToDateTime(eDate);
//////Delete Existing entries
OdbcCommand cmd2 = conn_asset.CreateCommand();
cmd2.CommandText = "delete from ast_pmPenalty where quarterStartDate = '" + fromDate.ToString("yyyy/MM/dd") + "' ";
int check1;
conn_asset.Open();
check1 = cmd2.ExecuteNonQuery();
conn_asset.Close();
//////Add new Entries
OdbcCommand cmd3 = conn_asset.CreateCommand();
cmd3.CommandText = "select ap.astCode as astCode1,am.type,amcParty from ast_pm ap join ast_master am on ap.astCode = am.astCode where quarterStartDate = '" + fromDate.ToString("yyyy/MM/dd") + "' and compStat = 'N'";
conn_asset.Open();
OdbcDataReader dr3 = cmd3.ExecuteReader();
DataTable dt = new DataTable();
DataRow newRow;
dt.Columns.Add(new System.Data.DataColumn("amcParty", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("type", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("astCode", typeof(String)));
dt.Columns.Add(new System.Data.DataColumn("quarterStartDate", typeof(DateTime)));
dt.Columns.Add(new System.Data.DataColumn("quarterEndDate", typeof(DateTime)));
while (dr3.Read())
{
newRow = dt.NewRow();
newRow["astCode"] = Convert.ToString(dr3["astCode1"]);
newRow["type"] = Convert.ToString(dr3["type"]);
newRow["amcParty"] = Convert.ToString(dr3["amcParty"]);
newRow["quarterStartDate"] = fromDate;
newRow["quarterEndDate"] = toDate;
dt.Rows.Add(newRow);
}
conn_asset.Close();
foreach (DataRow it in dt.Rows)
{
OdbcCommand cmd1 = conn_asset.CreateCommand();
cmd1.CommandText = "insert into ast_pmPenalty values('" + it["quarterStartDate"] + "','" + it["quarterEndDate"] + "','" + it["astCode"] + "','" + it["type"] + "','" + it["amcParty"] + "')";
int check2;
conn_asset.Open();
check2 = cmd1.ExecuteNonQuery();
conn_asset.Close();
}
//////
OdbcCommand cmd = conn_asset.CreateCommand();
cmd.CommandText = "select * from ast_pm where billStat = 'Y' and quarterStartDate = '" + fromDate.ToString("yyyy/MM/dd") + "'";
conn_asset.Open();
OdbcDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
btn_compBill.Visible = true;
btn_genBill.Visible = false;
conn_asset.Close();
BindData();
grid_display.Visible = true;
}
else
{
conn_asset.Close();
OdbcCommand cmd1 = conn_asset.CreateCommand();
cmd1.CommandText = "select * from ast_pm where lockStat = 'R' and quarterStartDate = '" + fromDate.ToString("yyyy/MM/dd") + "'";
conn_asset.Open();
OdbcDataReader dr1 = cmd1.ExecuteReader();
if (dr1.Read())
{
lbl_error.ForeColor = System.Drawing.Color.Red;
lbl_error.Text = "Quarter is not locked yet";
lbl_error.Visible = true;
}
else
{
lbl_error.Visible = false;
btn_genBill.Visible = true;
btn_compBill.Visible = false;
grid_display.Visible = false;
}
}
}
void F_comment()
{
EMPLOYEE_NO = Request.QueryString["EMPLOYEE_NO"];
DEPARTMENT_NO = Request.QueryString["DEP_NO"];
ICMANAGER = Request.QueryString["ICM"];
MANAGER = Request.QueryString["MNG"];
//SINMANAGER = Request.QueryString["SMNG"];
if (ICMANAGER == EMPLOYEE_NO)
{
string connectionString = ConfigurationManager.ConnectionStrings["PetroneedsConnectionString"].ConnectionString;
EMPLOYEE_NO = Request.QueryString["EMPLOYEE_NO"];
DEPARTMENT_NO = Request.QueryString["DEP_NO"];
sql = "SELECT ICT_TELE.FORM_NO,ICT_TELE.MNG_COMMENT FROM ICT_TELE WHERE ICT_TELE.FORM_NO='" + TextBox1.Text + "'";
/// ICT_TELE , FORM_NO, FORM_DATE, USER_NAME, USER_ID, USER_DEP, SIM_TYPE, FORM_STATUS, SIM_CEIL, DID, JUSTIFICATION, MNG_COMMENT, GM_COMMENT, ICT_COMMENT, POSITION
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand(sql, conn);
cmd.Connection.Open();
OdbcDataReader read = cmd.ExecuteReader();
read.Read();
if (read.HasRows)
{
TextBox3.Text = read["MNG_COMMENT"].ToString();
// TextBox4.Text = read["SMNG_COMMENT"].ToString();
TextBox3.Visible = true;
Label6.Visible = true;
// TextBox4.Visible = true;
// Label1.Visible = true;
}
cmd.Connection.Close();
}
/*else
* if (MANAGER == EMPLOYEE_NO)
* {
*
* string connectionString = ConfigurationManager.ConnectionStrings["PetroneedsConnectionString"].ConnectionString;
* EMPLOYEE_NO = Request.QueryString["EMPLOYEE_NO"];
* DEPARTMENT_NO = Request.QueryString["DEP_NO"];
* string sql = "SELECT ICT_NEW_ACCOUNT.FORM_NO,ICT_NEW_ACCOUNT.MNG_COMMENT FROM ICT_NEW_ACCOUNT WHERE ICT_NEW_ACCOUNT.FORM_NO='" + TextBox1.Text + "'";
*
* OdbcConnection conn = new OdbcConnection(connectionString);
* OdbcCommand cmd = new OdbcCommand(sql, conn);
*
* cmd.Connection.Open();
* OdbcDataReader read = cmd.ExecuteReader();
* read.Read();
*
* if (read.HasRows)
* {
* TextBox3.Text = read["MNG_COMMENT"].ToString();
* // TextBox4.Text = read["SMNG_COMMENT"].ToString();
*
* TextBox3.Visible = false;
* Label6.Visible = false;
* // TextBox4.Visible = false;
* // Label1.Visible = false;
*
* }
* cmd.Connection.Close();
*
* }*/
/*else
* if (MANAGER == EMPLOYEE_NO & SINMANAGER == EMPLOYEE_NO)
* {
* string connectionString = ConfigurationManager.ConnectionStrings["PetroneedsConnectionString"].ConnectionString;
* EMPLOYEE_NO = Request.QueryString["EMPLOYEE_NO"];
* DEPARTMENT_NO = Request.QueryString["DEP_NO"];
* string sql = "SELECT ICT_MSTR.FORM_NO,ICT_MSTR.SMNG_COMMENT,ICT_MSTR.MNG_COMMENT FROM ICT_MSTR WHERE ICT_MSTR.FORM_NO='" + TextBox1.Text + "'";
*
* OdbcConnection conn = new OdbcConnection(connectionString);
* OdbcCommand cmd = new OdbcCommand(sql, conn);
*
* cmd.Connection.Open();
* OdbcDataReader read = cmd.ExecuteReader();
* read.Read();
*
* if (read.HasRows)
* {
* TextBox3.Text = read["MNG_COMMENT"].ToString();
* TextBox4.Text = read["SMNG_COMMENT"].ToString();
*
* TextBox3.Visible = false;
* Label6.Visible = false;
* TextBox4.Visible = false;
* Label1.Visible = false;
*
* }
* cmd.Connection.Close();
* }*/
/*else
* if (MANAGER == EMPLOYEE_NO & SINMANAGER == EMPLOYEE_NO | SINMANAGER == EMPLOYEE_NO)
* {
* string connectionString = ConfigurationManager.ConnectionStrings["PetroneedsConnectionString"].ConnectionString;
* EMPLOYEE_NO = Request.QueryString["EMPLOYEE_NO"];
* DEPARTMENT_NO = Request.QueryString["DEP_NO"];
* string sql = "SELECT ICT_MSTR.FORM_NO,ICT_MSTR.SMNG_COMMENT,ICT_MSTR.MNG_COMMENT FROM ICT_MSTR WHERE ICT_MSTR.FORM_NO='" + TextBox1.Text + "'";
* OdbcConnection conn = new OdbcConnection(connectionString);
* OdbcCommand cmd = new OdbcCommand(sql, conn);
*
* cmd.Connection.Open();
* OdbcDataReader read = cmd.ExecuteReader();
* read.Read();
*
* if (read.HasRows)
* {
* TextBox3.Text = read["MNG_COMMENT"].ToString();
* //TextBox4.Text = read["SMNG_COMMENT"].ToString();
* TextBox4.Visible = false;
* Label1.Visible = false;
*
* TextBox3.Visible = true;
* Label6.Visible = true;
*
*
* }
* cmd.Connection.Close();
* }*/
}
private void btn_SearchData_Click(object sender, EventArgs e)
{
string queryString = "select * from Test_Data where ";
string identify_Num = "";
// Enumerable.Any() => 요소가 있으면 true, 없으면 false
if (!txtbox_InputSerialNum.Text.Any() && !txtbox_InputBarcode.Text.Any())
{
MessageBox.Show("시리얼 넘버나 바코드를 입력하세요.");
return;
}
else if (txtbox_InputSerialNum.Text.Any() && txtbox_InputBarcode.Text.Any())
{
MessageBox.Show("하나만 입력하세요(임시)");
return;
}
else if (txtbox_InputSerialNum.Text.Any())
{
queryString += "Serial_number='" + txtbox_InputSerialNum.Text + "'";
identify_Num = txtbox_InputSerialNum.Text;
}
else if (txtbox_InputBarcode.Text.Any())
{
queryString += "Barcode='" + txtbox_InputBarcode.Text + "'";
identify_Num = txtbox_InputBarcode.Text;
}
string serialNum = txtbox_InputSerialNum.Text;
searchedDataView.Columns.Clear();
create_SearchedDgv(conString, identify_Num);
OdbcCommand command = new OdbcCommand(queryString);
using (OdbcConnection connection = new OdbcConnection(conString))
{
command.Connection = connection;
connection.Open();
OdbcDataReader dr = command.ExecuteReader();
while (dr.Read())
{
string[] row = { dr["Model_name"].ToString(), dr["Test_user"].ToString(), dr["Start_time"].ToString(), dr["End_time"].ToString(),
dr["Serial_number"].ToString(), dr["Barcode"].ToString(), dr["Total_result"].ToString() };
List <string> readRow = row.ToList();
List <string> parsedStrings = new List <string>();
parsedStrings.AddRange(dr["Test_Data"].ToString().Split(';'));
parsedStrings.Remove("");
foreach (string parsedStr in parsedStrings)
{
readRow.Add(parsedStr);
}
//Rows.Add() 인자 배열을 넣어야 원하는 결과가 나옴. 리스트 넣으면 값이 안들어감
searchedDataView.Rows.Add(readRow.ToArray());
//modifyData(conString, dr["Model_name"].ToString());
}
}
int rowNumber = 1;
foreach (DataGridViewRow row in searchedDataView.Rows)
{
if (row.IsNewRow)
{
continue;
}
row.HeaderCell.Value = rowNumber.ToString();
rowNumber = rowNumber + 1;
}
searchedDataView.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders);
}
private static void Main(string[] args)
{
if (args.Length != 5)
{
if ((args.Length != 5) && (args.Length != 0))
{
showUsage();
}
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new formMain());
}
else
{
OdbcDataReader reader2;
string dsn = args[0];
string username = args[1];
string password = args[2];
string path = args[3];
string str5 = args[4];
OdbcConnectionManager manager = new OdbcConnectionManager(dsn, username, password);
StreamReader reader = new StreamReader(path);
OdbcCommand command = new OdbcCommand(reader.ReadLine(), manager.cnn);
DataTable table = new DataTable();
try
{
reader2 = command.ExecuteReader();
}
catch (Exception exception)
{
Cursor.Current = Cursors.Default;
MessageBox.Show("Error executing command\n\n" + exception.Message);
return;
}
try
{
table.Load(reader2);
}
catch (OutOfMemoryException)
{
MessageBox.Show("Error: Out of memory", "NetTools: ODBCBrowse", MessageBoxButtons.OK, MessageBoxIcon.Hand);
return;
}
StreamWriter writer = new StreamWriter(str5);
string str7 = "";
foreach (DataColumn column in table.Columns)
{
writer.Write(str7);
if (column.ColumnName.Contains(",") ||
column.ColumnName.Contains("\"") ||
column.ColumnName.Contains("\n"))
{
writer.Write("\"");
writer.Write(column.ColumnName.Replace("\"", "\"\"").Replace("\r", " ").Replace("\n", " "));
writer.Write("\"");
}
else
{
writer.Write(column.ColumnName);
}
str7 = ",";
}
writer.WriteLine();
ExportProgress progress = new ExportProgress();
progress.updateProgressMin(1);
progress.updateProgressMax(table.Rows.Count);
progress.Show();
int num = 1;
foreach (DataRow row in table.Rows)
{
str7 = "";
foreach (object obj2 in row.ItemArray)
{
writer.Write(str7);
if (obj2.ToString().Contains(",") ||
obj2.ToString().Contains("\"") ||
obj2.ToString().Contains("\n"))
{
writer.Write("\"");
writer.Write(obj2.ToString().Trim().Replace("\"", "\"\"").Replace("\r", " ").Replace("\n", " "));
writer.Write("\"");
}
else
{
writer.Write(obj2.ToString().Trim());
}
str7 = ",";
}
writer.WriteLine();
progress.updateProgressBar(num);
num++;
}
writer.Close();
}
}
public List <IPTFRA> Get_IPTFRA()
{
List <IPTFRA> lstIPTFRA = new List <IPTFRA>();
OdbcConnection conn = new OdbcConnection(ODBCDataConnString);
try
{
conn.Open();
// Try to split with verbatim literal
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = @"select
fra_job_no,fra_wdth_1,fra_nbr_slit_1,fra_wdth_2,fra_nbr_slit_2,fra_wdth_3,fra_nbr_slit_3,fra_wdth_4,fra_nbr_slit_4,fra_wdth_5,fra_nbr_slit_5,
fra_wdth_6,fra_nbr_slit_6,fra_wdth_7,fra_nbr_slit_7,fra_wdth_8,fra_nbr_slit_8,fra_wdth_9,fra_nbr_slit_9,fra_wdth_10,fra_nbr_slit_10,
fra_wdth_11,fra_nbr_slit_11,fra_wdth_12,fra_nbr_slit_12,fra_wdth_13,fra_nbr_slit_13,fra_wdth_14,fra_nbr_slit_14,fra_wdth_15,fra_nbr_slit_15,
fra_wdth_16,fra_nbr_slit_16,fra_wdth_17,fra_nbr_slit_17,fra_wdth_18,fra_nbr_slit_18,fra_wdth_19,fra_nbr_slit_19,fra_wdth_20,fra_nbr_slit_20,
fra_wdth_21,fra_nbr_slit_21,fra_wdth_22,fra_nbr_slit_22,fra_wdth_23,fra_nbr_slit_23,fra_wdth_24,fra_nbr_slit_24,fra_wdth_25,fra_nbr_slit_25,
fra_wdth_26,fra_nbr_slit_26,fra_wdth_27,fra_nbr_slit_27,fra_wdth_28,fra_nbr_slit_28,fra_wdth_29,fra_nbr_slit_29,fra_wdth_30,fra_nbr_slit_30,
fra_wdth_31,fra_nbr_slit_31,fra_wdth_32,fra_nbr_slit_32,fra_wdth_33,fra_nbr_slit_33,fra_wdth_34,fra_nbr_slit_34,fra_wdth_35,fra_nbr_slit_35,
fra_wdth_36,fra_nbr_slit_36,fra_wdth_37,fra_nbr_slit_37,fra_wdth_38,fra_nbr_slit_38,fra_wdth_39,fra_nbr_slit_39,fra_wdth_40,fra_nbr_slit_40,
fra_wdth_41,fra_nbr_slit_41,fra_wdth_42,fra_nbr_slit_42,fra_wdth_43,fra_nbr_slit_43,fra_wdth_44,fra_nbr_slit_44,fra_wdth_45,fra_nbr_slit_45,
fra_wdth_46,fra_nbr_slit_46,fra_wdth_47,fra_nbr_slit_47,fra_wdth_48,fra_nbr_slit_48,fra_wdth_49,fra_nbr_slit_49,fra_wdth_50,fra_nbr_slit_50,
fra_wdth_51,fra_nbr_slit_51,fra_wdth_52,fra_nbr_slit_52,fra_wdth_53,fra_nbr_slit_53,fra_wdth_54,fra_nbr_slit_54,fra_wdth_55,fra_nbr_slit_55,
fra_wdth_56,fra_nbr_slit_56,fra_wdth_57,fra_nbr_slit_57,fra_wdth_58,fra_nbr_slit_58,fra_wdth_59,fra_nbr_slit_59,fra_wdth_60,fra_nbr_slit_60,
fra_tot_wdth
from iptfra_rec
where fra_job_no in
(select psh_job_no
from iptpsh_rec s
inner join iptjob_rec j
on j.job_job_no = s.psh_job_no
where s.psh_whs = 'SW'
and psh_sch_seq_no <> 99999999
and(job_job_sts = 0 or job_job_sts = 1)
and (job_prs_cl = 'SL' or job_prs_cl = 'CL' or job_prs_cl = 'MB'))";
OdbcDataReader rdr = cmd.ExecuteReader();
using (rdr)
{
while (rdr.Read())
{
IPTFRA b = new IPTFRA();
b.job_no = Convert.ToInt32(rdr["fra_job_no"]);
b.tot_wdth = Convert.ToDecimal(rdr["fra_tot_wdth"]);
/*
* Step through each pair of wdth_X and nbr_X properties in IPTFRA
* Use reflection to get the property name, then set the value using
* a variable for wdth_X and nbr_X
*/
PropertyInfo[] props = b.GetType().GetProperties();
for (int a = 1; a < 61; a++)
{
string w = "wdth_" + a.ToString();
string n = "nbr_" + a.ToString();
PropertyInfo propertyWdth = b.GetType().GetProperty(w);
propertyWdth.SetValue(b, Convert.ToDecimal(rdr["fra_wdth_" + a.ToString()]));
PropertyInfo propertyNbr = b.GetType().GetProperty(n);
propertyNbr.SetValue(b, Convert.ToInt32(rdr["fra_nbr_slit_" + a.ToString()]));
}
lstIPTFRA.Add(b);
}
}
}
catch (OdbcException)
{
throw;
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
conn.Dispose();
}
return(lstIPTFRA);
}
/// <summary>
///
/// select a row from table t_RBSR_AUFW_u_SAPRoleOwner.
/// </summary>
/// <param name="ID"></param>
/// <returns>returnGetSAPRoleOwner</returns>
public returnGetSAPRoleOwner GetSAPRoleOwner(int ID)
{
returnGetSAPRoleOwner rv = new returnGetSAPRoleOwner();
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "select \"c_id\",\"c_u_EID\",\"c_u_Geography\",\"c_u_Rank\",\"c_r_SAProle\" from \"t_RBSR_AUFW_u_SAPRoleOwner\" where \"c_id\"= ?";
cmd.Parameters.Add("c_id", OdbcType.Int);
cmd.Parameters["c_id"].Value = (object)ID;
cmd.Connection = _dbConnection;
OdbcDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (dr.IsDBNull(0))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'ID'");
}
else
{
rv.ID = dr.GetInt32(0);
}
if (dr.IsDBNull(1))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'EID'");
}
else
{
rv.EID = dr.GetString(1);
}
if (dr.IsDBNull(2))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'Geography'");
}
else
{
rv.Geography = dr.GetString(2);
}
if (dr.IsDBNull(3))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'Rank'");
}
else
{
rv.Rank = dr.GetString(3);
}
if (dr.IsDBNull(4))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'SAProleID'");
}
else
{
rv.SAProleID = dr.GetInt32(4);
}
}
dr.Close();
dr.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
public void SendEMail(string Status, int UserCheck)
{
string connectionString = ConfigurationManager.ConnectionStrings["PetroneedsConnectionString"].ConnectionString;
EMPLOYEE_NO = Request.QueryString["EMPLOYEE_NO"];
DEPARTMENT_NO = Request.QueryString["DEP_NO"];
ICMANAGER = Request.QueryString["ICM"];
MANAGER = Request.QueryString["MNG"];
//SINMANAGER = Request.QueryString["SMNG"];
EMSM = Request.QueryString["EMS"];
EmId = DropDownList1.SelectedValue.ToString();
string date;
date = DateTime.Now.Date.ToString("dd-MMMM-yyyy");
// Requester
string sql = "SELECT USERS_INFORMATIONS.EMAIL, USERS_INFORMATIONS.FULL_USER_NAME, DEPARTMENTS.DEP_NAME " +
"FROM USERS_INFORMATIONS, DEPARTMENTS WHERE DEPARTMENTS.DEP_NO = USERS_INFORMATIONS.DEP_NO AND " +
"USERS_INFORMATIONS.EMPLOYEE_NO = '" + TextBox2.Text + "'";
// ICT_MANAGE
//string sql2 = "SELECT FULL_USER_NAME FROM USERS_INFORMATIONS WHERE EMPLOYEE_NO = '" + EMPLOYEE_NO + "'"; //DEP_NO
string sql2 = "SELECT USERS_INFORMATIONS.EMAIL, USERS_INFORMATIONS.FULL_USER_NAME, DEPARTMENTS.DEP_NAME " +
"FROM USERS_INFORMATIONS, DEPARTMENTS WHERE DEPARTMENTS.DEP_NO = USERS_INFORMATIONS.DEP_NO AND " +
"USERS_INFORMATIONS.EMPLOYEE_NO = '" + ICMANAGER + "'";
//"SELECT EMPLOYEE_NO FROM REQUEST_ACTORS WHERE REQUEST_TYPE_ID='31'
// Approval (Manager)
string sql3 = "SELECT USERS_INFORMATIONS.EMAIL, USERS_INFORMATIONS.FULL_USER_NAME, DEPARTMENTS.DEP_NAME " +
"FROM USERS_INFORMATIONS, DEPARTMENTS WHERE DEPARTMENTS.DEP_NO = USERS_INFORMATIONS.DEP_NO AND " +
"USERS_INFORMATIONS.EMPLOYEE_NO = '" + MANAGER + "'";
//SENER MANAGER
string sql4 = "SELECT USERS_INFORMATIONS.EMAIL, USERS_INFORMATIONS.FULL_USER_NAME, DEPARTMENTS.DEP_NAME " +
"FROM USERS_INFORMATIONS, DEPARTMENTS WHERE DEPARTMENTS.DEP_NO = USERS_INFORMATIONS.DEP_NO AND " +
"USERS_INFORMATIONS.EMPLOYEE_NO = '" + EMSM + "'";
//SEND EMPLOYEE_IT
string sql5 = "SELECT USERS_INFORMATIONS.EMAIL, USERS_INFORMATIONS.FULL_USER_NAME, DEPARTMENTS.DEP_NAME " +
"FROM USERS_INFORMATIONS, DEPARTMENTS WHERE DEPARTMENTS.DEP_NO = USERS_INFORMATIONS.DEP_NO AND " +
"USERS_INFORMATIONS.EMPLOYEE_NO = '" + EmId + "'";
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand(sql, conn);
OdbcCommand cmd2 = new OdbcCommand(sql2, conn);
OdbcCommand cmd3 = new OdbcCommand(sql3, conn);
OdbcCommand cmd4 = new OdbcCommand(sql4, conn);
OdbcCommand cmd5 = new OdbcCommand(sql5, conn);
cmd.Connection.Open();
OdbcDataReader read = cmd.ExecuteReader();
OdbcDataReader read2 = cmd2.ExecuteReader();
OdbcDataReader read3 = cmd3.ExecuteReader();
OdbcDataReader read4 = cmd4.ExecuteReader();
OdbcDataReader read5 = cmd5.ExecuteReader();
read.Read();
read2.Read();
read3.Read();
read4.Read();
read5.Read();
if (read.HasRows) // Requester
{
EmpName = read["FULL_USER_NAME"].ToString();
EmpEmail = read["EMAIL"].ToString();
Depart = read["DEP_NAME"].ToString();
}
if (read2.HasRows) // Approval ( ICT_MNG)
{
EmpName2 = read2["FULL_USER_NAME"].ToString();
EmpEmail2 = read2["EMAIL"].ToString();
}
if (read3.HasRows) // Approval (Manager)
{
EmpName3 = read3["FULL_USER_NAME"].ToString();
EmpEmail3 = read3["EMAIL"].ToString();
}
if (read4.HasRows) // (SMNG)
{
EmpName4 = read4["FULL_USER_NAME"].ToString();
EmpEmail4 = read4["EMAIL"].ToString();
}
if (read5.HasRows)
{
ITEMP = read5["FULL_USER_NAME"].ToString();
ITEmail = read5["EMAIL"].ToString();
}
//if ICT MANAGER
if (UserCheck == 1)
{
Response.Redirect("~/UsersArea/E-mail.aspx?EMPLOYEE_NO=" + TextBox2.Text + "&DEP_NO=" + DEPARTMENT_NO + "&FULL_USER_NAME=" + EmpName + "&EMAIL=" + EmpEmail + "&DEP_NAME=" + Depart + "&REQSTATUS=" + Status + "&comment=" + TextBox5 + "&DATE=" + date + "" +
"&ApprovalName=" + ITEMP + "&ApprovalEmail=" + ITEmail + "&REQNAME=Equpment Request (ER)&AUTHORNAME=" + EmpName2 + "");
/*Response.Redirect("~/UsersArea/E-mail.aspx?EMPLOYEE_NO=" + TextBox2.Text + "&DEP_NO=" + DEPARTMENT_NO + "&FULL_USER_NAME=" + EmpName + "&EMAIL=" + EmpEmail + "&DEP_NAME=" + Depart + "&REQSTATUS=" + Status + "&DATE=" + date + "" +
* "&ApprovalName=" + EmpName + "&ApprovalEmail=" + EmpEmail + "&REQNAME=Equpment Request (TRF)&AUTHORNAME=" + EmpName + "");*/
}
if (UserCheck == 2) // if Manager
{
Response.Redirect("~/UsersArea/E-mail.aspx?EMPLOYEE_NO=" + TextBox2.Text + "&DEP_NO=" + DEPARTMENT_NO + "&FULL_USER_NAME=" + EmpName + "&EMAIL=" + EmpEmail + "&DEP_NAME=" + Depart + "&REQSTATUS=" + Status + "&DATE=" + date + "" +
"&ApprovalName=" + EmpName4 + "&ApprovalEmail=" + EmpEmail4 + "&REQNAME=Equipment Request (ER)&AUTHORNAME=" + EmpName3 + "");
}
else
//if SENIER = Manager , IF SMNG
if (UserCheck == 3 | UserCheck == 4)
{
//Response.Redirect("~/Login.aspx");
Response.Redirect("~/UsersArea/E-mail.aspx?EMPLOYEE_NO=" + TextBox2.Text + "&DEP_NO=" + DEPARTMENT_NO + "&FULL_USER_NAME=" + EmpName + "&EMAIL=" + EmpEmail + "&DEP_NAME=" + Depart + "&REQSTATUS=" + Status + "&DATE=" + date + "" +
"&ApprovalName=" + EmpName2 + "&ApprovalEmail=" + EmpEmail2 + "&REQNAME=Equipment Request (ER)&AUTHORNAME=" + EmpName4 + "");
}
/*else
* //if SMNG
* if (UserCheck == 4)
* {
* Response.Redirect("~/UsersArea/E-mail.aspx?EMPLOYEE_NO=" + TextBox2.Text + "&DEP_NO=" + DEPARTMENT_NO + "&FULL_USER_NAME=" + EmpName + "&EMAIL=" + EmpEmail + "&DEP_NAME=" + Depart + "&REQSTATUS=" + Status + "&DATE=" + date + "" +
* "&ApprovalName=" + EmpName2 + "&ApprovalEmail=" + EmpEmail2 + "&REQNAME=Equipment Request (ER)&AUTHORNAME=" + EmpName4 + "");
* }*/
cmd.Connection.Close();
//cmd2.Connection.Close();
}
/// <summary>
///
/// select a set of rows from table t_RBSR_AUFW_u_SAPRoleOwner.
/// </summary>
/// <param name="maxRowsToReturn">Max number of rows to return. If null or 0 all rows are returned.</param>
/// <param name="SAProleID"></param>
/// <returns>returnListSAPRoleOwnerBySAProle[]</returns>
public returnListSAPRoleOwnerBySAProle[] ListSAPRoleOwnerBySAProle(int?maxRowsToReturn, int SAProleID)
{
returnListSAPRoleOwnerBySAProle[] rv = null;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
if (maxRowsToReturn.HasValue && maxRowsToReturn.Value > 0)
{
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText = "SELECT TOP " + maxRowsToReturn.Value + " \"c_id\", \"c_u_EID\", \"c_u_Geography\", \"c_u_Rank\", \"c_r_SAProle\" FROM \"t_RBSR_AUFW_u_SAPRoleOwner\" WHERE \"c_r_SAProle\"=?";
}
else
{
cmd.CommandText = "SELECT \"c_id\", \"c_u_EID\", \"c_u_Geography\", \"c_u_Rank\", \"c_r_SAProle\" FROM \"t_RBSR_AUFW_u_SAPRoleOwner\" WHERE \"c_r_SAProle\"=?" + " LIMIT " + maxRowsToReturn.Value;
}
}
else
{
cmd.CommandText = "SELECT \"c_id\", \"c_u_EID\", \"c_u_Geography\", \"c_u_Rank\", \"c_r_SAProle\" FROM \"t_RBSR_AUFW_u_SAPRoleOwner\" WHERE \"c_r_SAProle\"=?";
}
cmd.Parameters.Add("1_SAProleID", OdbcType.Int);
cmd.Parameters["1_SAProleID"].Value = (object)SAProleID;
OdbcDataReader dr = cmd.ExecuteReader();
List <returnListSAPRoleOwnerBySAProle> rvl = new List <returnListSAPRoleOwnerBySAProle>();
while (dr.Read())
{
returnListSAPRoleOwnerBySAProle cr = new returnListSAPRoleOwnerBySAProle();
if (dr.IsDBNull(0))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'ID'");
}
else
{
cr.ID = dr.GetInt32(0);
}
if (dr.IsDBNull(1))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'EID'");
}
else
{
cr.EID = dr.GetString(1);
}
if (dr.IsDBNull(2))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'Geography'");
}
else
{
cr.Geography = dr.GetString(2);
}
if (dr.IsDBNull(3))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'Rank'");
}
else
{
cr.Rank = dr.GetString(3);
}
if (dr.IsDBNull(4))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'SAProleID'");
}
else
{
cr.SAProleID = dr.GetInt32(4);
}
rvl.Add(cr);
}
dr.Close();
dr.Dispose();
rv = rvl.ToArray();
cmd.Dispose();
DBClose();
return(rv);
}
void unique_card_view(object sender, KeyEventArgs e)
{
if (e.Key == Key.Tab | e.Key == Key.Enter)
{
string user = Properties.Settings.Default.User;
if (user == "ADMIN" || user == "MANAGER" || user == "USER")
{
if (!string.IsNullOrWhiteSpace(card_view.Text))
{
if (card_view.Text.Length <= 17)
{
try
{
double bal = 0;
double balance = 0;
Connection con1 = new Connection();
con1.open_connection();
OdbcCommand cmd2 = new OdbcCommand("select credit_amount from credit_details where card_id='" + card_view.Text + "'", con1.conn);
OdbcDataAdapter daa = new OdbcDataAdapter(cmd2);
DataTable dtt = new DataTable();
daa.Fill(dtt);
if (dtt.Rows.Count > 0)
{
OdbcCommand cmd = new OdbcCommand("select sum(credit_amount) from credit_details where card_id='" + card_view.Text + "'", con1.conn);
OdbcDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
bal = Convert.ToDouble(dr[0]);
}
}
else
{
MessageBox.Show("Records does not Exist");
}
OdbcCommand cmd4 = new OdbcCommand("select total_cost from diesel_balance_sheet where card_id='" + card_view.Text + "'", con1.conn);
OdbcDataAdapter daa1 = new OdbcDataAdapter(cmd2);
DataTable dtt1 = new DataTable();
daa1.Fill(dtt1);
if (dtt1.Rows.Count > 0)
{
OdbcCommand cmd3 = new OdbcCommand("select sum(total_cost) from diesel_balance_sheet where card_id='" + card_view.Text + "'", con1.conn);
OdbcDataReader dr1 = cmd3.ExecuteReader();
while (dr1.Read())
{
balance = Convert.ToDouble(dr1[0]);
}
}
else
{
MessageBox.Show("Records does not Exist");
}
double d = bal - balance;
diesel_balance.Content = Math.Round(d, 2);
con1.close_connection();
}
catch (OdbcException ex)
{
MessageBox.Show("Error :" + ex);
}
}
else
{
MessageBox.Show("Allow Combination of 17 Characters and Numbers");
}
}
else
{
MessageBox.Show("Please Select Card Id");
}
}
else
{
MessageBox.Show("Access Denied");
}
}
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
try
{
OdbcConnection conexion = ASG_DB.connectionResult();
string sql = string.Format("SELECT * FROM VISTA_MERCADERIA WHERE ID_MERCADERIA LIKE '%{0}%' AND NOMBRE_SUCURSAL = '{1}';", textBox1.Text, comboBox2.Text);
OdbcCommand cmd = new OdbcCommand(sql, conexion);
OdbcDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
dataGridView1.Rows.Clear();
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(2), reader.GetString(1), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(4)), string.Format("{0:#,###,###,###,###}", reader.GetDouble(5)));
while (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(2), reader.GetString(1), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(4)), string.Format("{0:#,###,###,###,###}", reader.GetDouble(5)));
//styleDV(this.dataGridView1);
}
}
else
{
conexion.Close();
conexion = ASG_DB.connectionResult();
sql = string.Format("SELECT * FROM VISTA_MERCADERIA WHERE DESCRIPCION LIKE '%{0}%' AND NOMBRE_SUCURSAL = '{1}';", textBox1.Text, comboBox2.Text);
cmd = new OdbcCommand(sql, conexion);
reader = cmd.ExecuteReader();
if (reader.Read())
{
dataGridView1.Rows.Clear();
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(2), reader.GetString(1), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(4)), string.Format("{0:#,###,###,###,###}", reader.GetDouble(5)));
while (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(2), reader.GetString(1), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(4)), string.Format("{0:#,###,###,###,###}", reader.GetDouble(5)));
//styleDV(this.dataGridView1);
}
}
else
{
conexion.Close();
conexion = ASG_DB.connectionResult();
sql = string.Format("SELECT * FROM VISTA_MERCADERIA WHERE NOMBRE_PROVEEDOR LIKE '%{0}%' AND NOMBRE_SUCURSAL = '{1}';", textBox1.Text, comboBox2.Text);
cmd = new OdbcCommand(sql, conexion);
reader = cmd.ExecuteReader();
if (reader.Read())
{
dataGridView1.Rows.Clear();
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(2), reader.GetString(1), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(4)), string.Format("{0:#,###,###,###,###}", reader.GetDouble(5)));
while (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(2), reader.GetString(1), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(4)), string.Format("{0:#,###,###,###,###}", reader.GetDouble(5)));
//styleDV(this.dataGridView1);
}
}
else
{
conexion.Close();
conexion = ASG_DB.connectionResult();
sql = string.Format("SELECT * FROM VISTA_MERCADERIA WHERE PRECIO_COMPRA LIKE '%{0}%' AND NOMBRE_SUCURSAL = '{1}';", textBox1.Text, comboBox2.Text);
cmd = new OdbcCommand(sql, conexion);
reader = cmd.ExecuteReader();
if (reader.Read())
{
dataGridView1.Rows.Clear();
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(2), reader.GetString(1), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(4)), string.Format("{0:#,###,###,###,###}", reader.GetDouble(5)));
while (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(2), reader.GetString(1), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(4)), string.Format("{0:#,###,###,###,###}", reader.GetDouble(5)));
//styleDV(this.dataGridView1);
}
}
else
{
conexion.Close();
conexion = ASG_DB.connectionResult();
sql = string.Format("SELECT * FROM VISTA_MERCADERIA WHERE TOTAL_UNIDADES LIKE '%{0}%' AND NOMBRE_SUCURSAL = '{1}';", textBox1.Text, comboBox2.Text);
cmd = new OdbcCommand(sql, conexion);
reader = cmd.ExecuteReader();
if (reader.Read())
{
dataGridView1.Rows.Clear();
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(2), reader.GetString(1), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(4)), string.Format("{0:#,###,###,###,###}", reader.GetDouble(5)));
while (reader.Read())
{
dataGridView1.Rows.Add(reader.GetString(0), reader.GetString(2), reader.GetString(1), string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(4)), string.Format("{0:#,###,###,###,###}", reader.GetDouble(5)));
///styleDV(this.dataGridView1);
}
}
}
}
}
}
}
catch (Exception es)
{
MessageBox.Show(es.ToString());
}
}
private const string _options = @"Trusted_Connection=Yes"; // Any additional connection options
static void Main(string[] args)
{
// Parse command line
bool show_help = false;
OptionSet p = new OptionSet()
.Add(
"s=|server|S|Server",
"The SQL Server instance to connect to.",
v => _server = v
)
.Add(
"d=|database|D|Database",
"The SQL Server database to connect to.",
v => _database = v
)
.Add(
"t=|table|T|Table",
"The SQL Server table whose columns will be returned.",
v => _table = v
)
.Add(
"v",
"Increase debug message verbosity.",
v => { if (v != null)
{
++_verbosity;
}
}
)
.Add(
"?|h|help",
"Display this help message.",
v => show_help = v != null
);
List <string> extra;
try
{
extra = p.Parse(args);
}
catch (OptionException e)
{
Console.Write("GetColumnNames: ");
Console.WriteLine(e.Message);
Console.WriteLine("Try 'GetColumnNames --help' for more information.");
return;
}
if (show_help || (_server == null || _database == null || _table == null))
{
ShowHelp(p);
return;
}
// Main processing
List <string> columns = new List <string>();
try
{
using (OdbcConnection connection = new OdbcConnection(GetConnectionString()))
{
// Connect to the database.
Debug("Connection String: {0}", connection.ConnectionString);
connection.Open();
using (OdbcCommand cmd = new OdbcCommand(GetQueryString(), connection))
{
Debug("Query String: {0}", cmd.CommandText);
using (OdbcDataReader reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
{
for (int col = 0; col < reader.FieldCount; col++)
{
columns.Add(reader.GetName(col).ToString()); // Gets the column name
}
reader.Close();
}
}
connection.Close();
}
PrintColumnNames(columns);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
private void Btn_consultaFactura_Click(object sender, EventArgs e)
{
Frm_consultaCotizacion consultaFactura = new Frm_consultaCotizacion();
consultaFactura.ShowDialog();
if (consultaFactura.DialogResult == DialogResult.OK)
{
Lbl_codFactura.Text =
consultaFactura.Dgv_consultaCotizacion.Rows[consultaFactura.Dgv_consultaCotizacion.CurrentRow.Index].
Cells[0].Value.ToString();
Lbl_idCliente.Text =
consultaFactura.Dgv_consultaCotizacion.Rows[consultaFactura.Dgv_consultaCotizacion.CurrentRow.Index].
Cells[1].Value.ToString();
Lbl_fechaEmision.Text =
consultaFactura.Dgv_consultaCotizacion.Rows[consultaFactura.Dgv_consultaCotizacion.CurrentRow.Index].
Cells[3].Value.ToString();
Cbo_Pasajero.Text =
consultaFactura.Dgv_consultaCotizacion.Rows[consultaFactura.Dgv_consultaCotizacion.CurrentRow.Index].
Cells[4].Value.ToString();
Lbl_idTipoPago.Text =
consultaFactura.Dgv_consultaCotizacion.Rows[consultaFactura.Dgv_consultaCotizacion.CurrentRow.Index].
Cells[5].Value.ToString();
Lbl_resultado.Text =
consultaFactura.Dgv_consultaCotizacion.Rows[consultaFactura.Dgv_consultaCotizacion.CurrentRow.Index].
Cells[6].Value.ToString();
Btn_generarReporte.Enabled = true;
try
{
string mostrarTipoPago = "SELECT T.NombreTipoPago FROM tbl_tipopagos T INNER JOIN " +
"tbl_facturaencabezado FE ON T.Pk_idTipoPago = " + Lbl_idTipoPago.Text;
OdbcCommand comm = new OdbcCommand(mostrarTipoPago, Conexion.nuevaConexion());
OdbcDataReader mostrarDatos = comm.ExecuteReader();
if (mostrarDatos.Read())
{
Cmbx_tipoPago.Text = mostrarDatos.GetString(0);
}
comm.Connection.Close();
mostrarDatos.Close();
string mostrarNombreCliente = "SELECT C.nombreCliente FROM tbl_cliente C INNER JOIN " +
"tbl_facturaencabezado FE ON C.Pk_idCliente = " + Lbl_idCliente.Text;
comm = new OdbcCommand(mostrarNombreCliente, Conexion.nuevaConexion());
mostrarDatos = comm.ExecuteReader();
if (mostrarDatos.Read())
{
Txt_cliente.Text = mostrarDatos.GetString(0);
}
comm.Connection.Close();
mostrarDatos.Close();
string mostrarDetalleFactura = "SELECT * FROM tbl_facturadetalle WHERE Pk_idFactura = " + Lbl_codFactura.Text;
comm = new OdbcCommand(mostrarDetalleFactura, Conexion.nuevaConexion());
mostrarDatos = comm.ExecuteReader();
Dgv_detalleFactura.Rows.Clear();
while (mostrarDatos.Read())
{
Dgv_detalleFactura.Rows.Add(mostrarDatos.GetString(0), mostrarDatos.GetString(1),
mostrarDatos.GetString(2), mostrarDatos.GetString(3), "xxxxx");
}
}
catch (Exception err)
{
Console.WriteLine(err.Message);
}
}
}
/// <summary>
/// Executes the service
/// </summary>
/// <param name="values">The values.</param>
/// <param name="theWorkspace">The workspace.</param>
/// <returns></returns>
public StringBuilder Execute(Dictionary <string, StringBuilder> values, IWorkspace theWorkspace)
{
if (values == null)
{
throw new InvalidDataContractException(ErrorResource.NoParameter);
}
string database = null;
string tableName = null;
string schema = null;
StringBuilder tmp;
values.TryGetValue("Database", out tmp);
if (tmp != null)
{
database = tmp.ToString();
}
values.TryGetValue("TableName", out tmp);
if (tmp != null)
{
tableName = tmp.ToString();
}
values.TryGetValue("Schema", out tmp);
if (tmp != null)
{
schema = tmp.ToString();
}
Dev2JsonSerializer serializer = new Dev2JsonSerializer();
if (string.IsNullOrEmpty(database))
{
var res = new DbColumnList("No database set.");
Dev2Logger.Debug("No database set.");
return(serializer.SerializeToBuilder(res));
}
if (string.IsNullOrEmpty(tableName))
{
var res = new DbColumnList("No table name set.");
Dev2Logger.Debug("No table name set.");
return(serializer.SerializeToBuilder(res));
}
Dev2Logger.Info($"Get Database Columns For Table. Database:{database} Schema:{schema} Table{tableName}");
try
{
var dbSource = serializer.Deserialize <DbSource>(database);
var runtTimedbSource = ResourceCatalog.Instance.GetResource <DbSource>(theWorkspace.ID, dbSource.ResourceID);
DataTable columnInfo;
switch (dbSource.ServerType)
{
case enSourceType.MySqlDatabase:
{
using (var connection = new MySqlConnection(runtTimedbSource.ConnectionString))
{
// Connect to the database then retrieve the schema information.
connection.Open();
var sql = @"select * from " + tableName.Trim('"').Replace("[", "").Replace("]", "") + " Limit 1 ";
using (var sqlcmd = new MySqlCommand(sql, connection))
{
// force it closed so we just get the proper schema ;)
using (var sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection))
{
columnInfo = sdr.GetSchemaTable();
}
}
}
break;
}
case enSourceType.Oracle:
{
using (var connection = new OracleConnection(runtTimedbSource.ConnectionString))
{
// Connect to the database then retrieve the schema information.
connection.Open();
var sql = @"select * from " + tableName.Trim('"').Replace("[", "").Replace("]", "") + " Limit 1 ";
using (var sqlcmd = new OracleCommand(sql, connection))
{
// force it closed so we just get the proper schema ;)
using (var sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection))
{
columnInfo = sdr.GetSchemaTable();
}
}
}
break;
}
case enSourceType.ODBC:
{
using (var connection = new OdbcConnection(runtTimedbSource.ConnectionString))
{
// Connect to the database then retrieve the schema information.
connection.Open();
var sql = @"select * from " + tableName.Trim('"').Replace("[", "").Replace("]", "") + " Limit 1 ";
using (var sqlcmd = new OdbcCommand(sql, connection))
{
// force it closed so we just get the proper schema ;)
using (var sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection))
{
columnInfo = sdr.GetSchemaTable();
}
}
}
break;
}
default:
{
using (var connection = new SqlConnection(runtTimedbSource.ConnectionString))
{
// Connect to the database then retrieve the schema information.
connection.Open();
// GUTTED TO RETURN ALL REQUIRED DATA ;)
if (schema == null)
{
schema = string.Empty;
}
var sql = @"select top 1 * from " + schema.Trim('"') + "." + tableName.Trim('"');
using (var sqlcmd = new SqlCommand(sql, connection))
{
// force it closed so we just get the proper schema ;)
using (var sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection))
{
columnInfo = sdr.GetSchemaTable();
}
}
}
break;
}
}
var dbColumns = new DbColumnList();
if (columnInfo != null)
{
foreach (DataRow row in columnInfo.Rows)
{
var columnName = row["ColumnName"] as string;
var isNullable = row["AllowDBNull"] is bool && (bool)row["AllowDBNull"];
var isIdentity = row["IsIdentity"] is bool && (bool)row["IsIdentity"];
var dbColumn = new DbColumn {
ColumnName = columnName, IsNullable = isNullable, IsAutoIncrement = isIdentity
};
SqlDbType sqlDataType;
var typeValue = dbSource.ServerType == enSourceType.SqlDatabase? row["DataTypeName"] as string:((Type)row["DataType"]).Name;
if (Enum.TryParse(typeValue, true, out sqlDataType))
{
dbColumn.SqlDataType = sqlDataType;
}
var columnLength = row["ColumnSize"] as int? ?? -1;
dbColumn.MaxLength = columnLength;
dbColumns.Items.Add(dbColumn);
}
}
return(serializer.SerializeToBuilder(dbColumns));
}
catch (Exception ex)
{
Dev2Logger.Error(ex);
var res = new DbColumnList(ex);
return(serializer.SerializeToBuilder(res));
}
}
static void Main(string[] args)
{
var con = new OdbcConnection("DSN=modemmysql");
OdbcDataReader dr;
OdbcCommand cmd;
con.Open();
//A select statement to get all rows that do not have code filled in as per said. Also These should contain the unused/ unchecked codes.
string strSQL = "SELECT * FROM `lnm`.`sms_in` WHERE `sms_text` LIKE '%Confirmed.%' AND `sender_number` IS NOT NULL AND (`code` = '' OR `code` IS NULL)";
OdbcDataAdapter da = new OdbcDataAdapter(strSQL, con);
cmd = new OdbcCommand(strSQL, con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
string strText = dr[1].ToString();
//Console.WriteLine(strText);
//Console.ReadLine();
string[] arrText = strText.Split(' ');
//AA64SF914 Confirmed.on 17/2/14 at 12:02 PMKsh33,950.00 received from 254727137226 Mark ADAMS.New Account balance is Ksh2,550.00
//These are the values that should be inserted to the NULL columns
/*
* Response.Write(arrText[0]); //TranCode
* Response.Write(brk);
* Response.Write(arrText[5]); //amount
* Response.Write(brk);
* Response.Write(arrText[9]); //fname
* Response.Write(brk);
* Response.Write(arrText[10]); //lname
* Response.Write(brk);
* Response.Write(arrText[8]); //msisdn
* Response.Write(brk);
*/
//Ignore. These are the other text fields to be used as tests
//Here I'll be getting the row ID and the update will occur per Id That fulfills the said condition
string ID1 = dr["id"].ToString();
//Response.Write(ID1);
//****************************************
var charsToRemove = new string[] { "\n", "PMKsh", "AMKsh", "Ksh", ",", ".New", "'" };
string str = arrText[5];
string strfn = arrText[9];
string strln = arrText[10];
foreach (var c in charsToRemove)
{
str = str.Replace(c, string.Empty);
strfn = strfn.Replace(c, string.Empty);
strln = strln.Replace(c, string.Empty);
}
arrText[5] = str;
arrText[9] = strfn;
arrText[10] = strln;
/*//****************************************
* Response.Write(brk);
* Response.Write(str);
* Response.Write(brk);
* Response.Write(strln);
* //****************************************
*/
strSQL = "Update `lnm`.`sms_in` SET `code` = '" + arrText[0] + "', `amount` = '" + arrText[5] + "', `firstname` = '" + arrText[9] + "', `lastname` = '" + arrText[10] + "', `msisdn` = '" + arrText[8] + "' WHERE `id` = " + ID1;
OdbcCommand update = new OdbcCommand(strSQL, con);
//Response.Write(brk);
//Response.Write(strSQL);
update.ExecuteNonQuery();
} //end of the while loop
}
protected void btnExportar_Click(object sender, EventArgs e)
{
string dtHoje = DateTime.Now.Date.Day.ToString().PadLeft(2, '0') + "/" + DateTime.Now.Date.Month.ToString().PadLeft(2, '0') + "/" + DateTime.Now.Date.Year.ToString().PadLeft(2, '0');
DataTable dt = new DataTable();
DateTime Data = DateTime.Now;
string nome = "";
string rh = "";
string clinica = "";
string microorganismo = "";
string material = "";
string dt_resultado = "";
string status = "";
string admissão = "Paciente não Internado";
string dataDaUltimaSaida = "";
string reinternacao = "";
string andarCompleto = "";
string andar = "";
string quarto = "";
bool isInternado = false;
string leito = "";
string anoMesInicial = ddlAnoInicial.SelectedItem.Text + ddlMesInicial.SelectedValue.ToString().PadLeft(2, '0');
string anoMesFinal = ddlAnoFinal.SelectedItem.Text + ddlMesFinal.SelectedValue.ToString().PadLeft(2, '0');
//lbDataHora.Text = Data.ToString("D") + " " + Data.ToString("T");
dt.Columns.Add("DATA RESULTADO", System.Type.GetType("System.String"));
dt.Columns.Add("NOME DO PACIENTE", System.Type.GetType("System.String"));
dt.Columns.Add("REG.HOSP", System.Type.GetType("System.String"));
dt.Columns.Add("ADM", System.Type.GetType("System.String"));
dt.Columns.Add("SAIDA", System.Type.GetType("System.String"));
dt.Columns.Add("CLINICA", System.Type.GetType("System.String"));
dt.Columns.Add("ANDAR", System.Type.GetType("System.String"));
dt.Columns.Add("QUARTO", System.Type.GetType("System.String"));
dt.Columns.Add("LEITO", System.Type.GetType("System.String"));
dt.Columns.Add("MICROORGANISMO", System.Type.GetType("System.String"));
dt.Columns.Add("MATERIAL", System.Type.GetType("System.String"));
dt.Columns.Add("STATUS", System.Type.GetType("System.String"));
dt.Columns.Add("REINTERNAÇÃO", System.Type.GetType("System.String"));
try
{
using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringIsolamento"].ToString()))
{
SqlCommand cmm = cnn.CreateCommand();
cmm.CommandText = "SELECT p.nome, e.rh ,e.clinica, mi.descricao as microrganismo ,ma.descricao as material,e.dt_resultado " +
"FROM [Isolamento].[dbo].[Exame] as e inner join [Isolamento].[dbo].[Paciente] as p " +
"on e.rh = p.rh " +
"inner join [Isolamento].[dbo].[tipos_materiais] as ma " +
"on e.material = ma.cod_material " +
"inner join [Isolamento].[dbo].[tipos_microorganismos] as mi " +
"on e.microorganismo = mi.cod_microorg " +
" WHERE (YEAR(dt_resultado) * 100 + MONTH(dt_resultado)) >= " + anoMesInicial +
" AND (YEAR(dt_resultado) * 100 + MONTH(dt_resultado)) <= " + anoMesFinal;
cnn.Open();
SqlDataReader dr = cmm.ExecuteReader();
while (dr.Read())
{
nome = dr.GetString(0);
rh = dr.GetInt32(1).ToString();
clinica = dr.GetString(2);
microorganismo = dr.GetString(3);
material = dr.GetString(4);
dt_resultado = dr.GetDateTime(5).ToShortDateString();
try
{
using (OdbcConnection cnn2 = new OdbcConnection(ConfigurationManager.ConnectionStrings["HospubConn"].ToString()))
{
OdbcCommand cmm2 = cnn2.CreateCommand();
cmm2.CommandText = "select c02codleito,d02inter,c14nomec from cen02,cen14 where c14codclin = c02codclin and i02pront =" + rh;
cnn2.Open();
OdbcDataReader dr2 = cmm2.ExecuteReader();
if (dr2.Read())
{
isInternado = true;
andarCompleto = dr2.GetString(0);
andar = andarCompleto.Substring(0, 2);
quarto = andarCompleto.Substring(3, 2);
leito = andarCompleto.Substring(5, 2);
admissão = dr2.GetDecimal(1).ToString();
admissão = dataFormatada(admissão);
status = "Internado";
if (andar == "99")
{
clinica = "Leito Extra";
andar = "";
quarto = "";
}
else
{
clinica = dr2.GetString(2);
}
}
using (OdbcConnection cnn3 = new OdbcConnection(ConfigurationManager.ConnectionStrings["HospubConn"].ToString()))
{
OdbcCommand cmm3 = cnn3.CreateCommand();
cmm3.CommandText = "select d15inter, d15apres,d15compos1,c14nomec,c15motivo,c15codleito from cen15,cen14 where c14codclin = c15codclin and i15pront = " + rh + " order by d15inter desc ";
cnn3.Open();
OdbcDataReader dr3 = cmm3.ExecuteReader();
if (dr3.Read())
{
if (isInternado)
{
reinternacao = "Reinternado";
}
else
{
clinica = dr3.GetString(3);
admissão = dr3.GetDecimal(0).ToString();
admissão = dataFormatada(admissão);
dataDaUltimaSaida = dr3.GetDecimal(1).ToString() + dr3.GetDecimal(2).ToString().PadLeft(2, '0');
dataDaUltimaSaida = dataFormatada(dataDaUltimaSaida);
status = dr3.GetString(4);
if (status == "1")
{
status = "Alta";
}
else if (status == "2")
{
status = "Remoção";
}
else if (status == "3")
{
status = "Óbito até 24 horas";
}
else
{
status = "Óbito após 24 horas";
}
andarCompleto = dr3.GetString(5);
andar = andarCompleto.Substring(0, 2);
quarto = andarCompleto.Substring(3, 2);
leito = andarCompleto.Substring(5, 2);
}
}
}
}
using (OdbcConnection cnn1 = new OdbcConnection(ConfigurationManager.ConnectionStrings["HospubConn"].ToString()))
{
OdbcCommand cmm1 = cnn1.CreateCommand();
cmm1.CommandText = "select * from intb6 where ((ib6compos like '%OBITO%') or (ib6dtobito != '' and ib6dtobito != '00000000')) and ib6regist =" + rh;
cnn1.Open();
OdbcDataReader dr1 = cmm1.ExecuteReader();
if (dr1.Read())
{
status = "Obito";
}
}
}
catch (Exception ex)
{
Console.WriteLine("{0} Exception caught.", ex);
}
dt.Rows.Add(new String[] { dt_resultado, nome, rh, admissão, dataDaUltimaSaida, clinica, andar, quarto, leito, microorganismo, material, status, reinternacao });
nome = "";
rh = "";
clinica = "";
microorganismo = "";
material = "";
dt_resultado = "";
status = "";
admissão = "Paciente não Internado";
dataDaUltimaSaida = "";
reinternacao = "";
andarCompleto = "";
andar = "";
quarto = "";
isInternado = false;
leito = "";
}//while
}
}
catch (Exception ex)
{
Console.WriteLine("{0} Exception caught.", ex);
}
GridInternado.DataSource = dt; // apresentação dos dados da lista
GridInternado.DataBind();
DateTime dtarq = DateTime.Now;
string dia = Convert.ToString(Convert.ToInt32(dtarq.Day));//dia atual + 1 = dia seguinte
if (dia.Length == 1)
{
dia = dia.PadLeft(2, '0');
}
string mes = Convert.ToString(dtarq.Month);
if (mes.Length == 1)
{
mes = mes.PadLeft(2, '0');
}
string data = Convert.ToString(dia) + Convert.ToString(mes) + Convert.ToString(dtarq.Year);
HttpResponse oResponse = System.Web.HttpContext.Current.Response;
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("ISO-8859-1");
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=Isolamento" + data + ".xls");
HttpContext.Current.Response.Charset = "UTF-8";
GridInternado.RenderControl(hw);
oResponse.Write(tw.ToString());
oResponse.End();
}
protected void btnLogin_Click(Object sender, EventArgs e)
{
// Initialize FormsAuthentication, for what it's worth
FormsAuthentication.Initialize();
// Create our connection and command objects
OdbcConnection conn = new OdbcConnection(Utilities.ObtenerCadenaConexion());
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT roles FROM users WHERE username=? AND password=?";
// Fill our parameters
cmd.Parameters.Add("@username", OdbcType.NVarChar, 200).Value = Username.Value;
cmd.Parameters.Add("@password", OdbcType.NVarChar, 200).Value = FormsAuthentication.HashPasswordForStoringInConfigFile(Password.Value, "SHA1");
// Execute the command
conn.Open();
OdbcDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
// Create a new ticket used for authentication
FormsAuthenticationTicket ticket = new FormsAuthenticationTicket(
1, // Ticket version
Username.Value, // Username associated with ticket
DateTime.Now, // Date/time issued
DateTime.Now.AddMinutes(30), // Date/time to expire
true, // "true" for a persistent user cookie
reader.GetString(0), // User-data, in this case the roles
FormsAuthentication.FormsCookiePath); // Path cookie valid for
// Encrypt the cookie using the machine key for secure transport
string hash = FormsAuthentication.Encrypt(ticket);
HttpCookie cookie = new HttpCookie(
FormsAuthentication.FormsCookieName, // Name of auth cookie
hash); // Hashed ticket
// Set the cookie's expiration time to the tickets expiration time
if (ticket.IsPersistent)
{
cookie.Expires = ticket.Expiration;
}
// Add the cookie to the list for outgoing response
Response.Cookies.Add(cookie);
// Redirect to requested URL, or homepage if no previous page
// requested
string returnUrl = Request.QueryString["ReturnUrl"];
if (returnUrl == null)
{
returnUrl = "/admin/frmInicio.aspx";
}
// Don't call FormsAuthentication.RedirectFromLoginPage since it
// could
// replace the authentication ticket (cookie) we just added
Response.Redirect(returnUrl);
}
else
{
// Never tell the user if just the username is password is incorrect.
// That just gives them a place to start, once they've found one or
// the other is correct!
ErrorLabel.Text = "Por favor revise sus datos ya que no fue posible validarlos.";
ErrorLabel.Visible = true;
}
reader.Close();
conn.Close();
}