/// <summary>
/// DisplayRecords: This function displays the records in the view if any.
/// </summary>
static void DisplayRecords()
{
Console.Write("\tRecords in Prescirption list...");
try
{
reader = (OdbcDataReader)command.ExecuteReader();
// read the returned resultset
while (reader.Read())
{
/*
* This view consists the following fields:
* rxdef_id (TINYINT)
* drug_name (CHARACTER 50)
* description (CHARACTER 50)
* rx_date (DATE)
* For the purpose of keeping it simple, we are displaying the rxdef_id and drug_name on the console (the first two fields)
*/
Console.WriteLine("\n\t\t{0} {1} ", reader.GetInt16(0), reader.GetString(1));
}
// close the reader
reader.Close();
}
catch (Exception e)
{
ExceptionDisplay(e);
}
}
static List <etf_epf_record> GetListETF(string filter = "")
{
System.Data.Odbc.OdbcConnection l_objConnection = new System.Data.Odbc.OdbcConnection();
System.Data.Odbc.OdbcCommand l_objCmd = new System.Data.Odbc.OdbcCommand();
System.Data.Odbc.OdbcDataReader l_objRdr = null;
l_objConnection.ConnectionString = "DSN=PRODDSN_LIVE;PWD=INTERNET;UID=INTERNET";
l_objConnection.Open();
l_objCmd.Connection = l_objConnection;
l_objCmd.CommandType = CommandType.Text;
string sql = string.Empty;
if (string.IsNullOrEmpty(filter))
{
sql = "select etref,etreg,etamt,etpflg,etdate,ettime,etptyp,etfrpd,ettopd,etnomb,etbkcd,etbrcd,etacct,etdcd,etadat,etcopr,ettran from inetprddt1.etp1000 where etdate>='20170801' and etdate<='20170831' and etref like 'CBCETF%' order by etdate";
}
else
{
sql = "select etref,etreg,etamt,etpflg,etdate,ettime,etptyp,etfrpd,ettopd,etnomb,etbkcd,etbrcd,etacct,etdcd,etadat,etcopr,ettran from inetprddt1.etp1000 where etdate>='20170801' and etdate<='20170831' and etref like 'CBCETF%' and etref in " + filter + " order by etdate";
}
l_objCmd.CommandText = sql;
if (l_objRdr != null)
{
l_objRdr.Close();
}
l_objRdr = l_objCmd.ExecuteReader();
List <etf_epf_record> list = new List <etf_epf_record>();
if (l_objRdr.HasRows)
{
while (l_objRdr.Read())
{
etf_epf_record file = new etf_epf_record();
file.etref = l_objRdr["etref"].ToString().Trim();
file.etreg = l_objRdr["etreg"].ToString().Trim();
file.etamt = l_objRdr["etamt"].ToString().Trim();
file.etpflg = l_objRdr["etpflg"].ToString().Trim();
file.etdate = l_objRdr["etdate"].ToString().Trim();
file.ettime = l_objRdr["ettime"].ToString().Trim();
file.etptyp = l_objRdr["etptyp"].ToString().Trim();
file.etfrpd = l_objRdr["etfrpd"].ToString().Trim(); //From Period
file.ettopd = l_objRdr["ettopd"].ToString().Trim(); //To Period
file.etnomb = l_objRdr["etnomb"].ToString().Trim(); //EMployeed
file.etadat = l_objRdr["etadat"].ToString().Trim();
file.etcopr = l_objRdr["etcopr"].ToString().Trim();
file.ettran = l_objRdr["ettran"].ToString().Trim();
list.Add(file);
}
}
return(list);
}
public Hashtable Read_Values(string[] Tag_Name, DateTime Time_Stamp)
{
Hashtable Result = new Hashtable();
string Tag_cond = "(FALSE"; // Makes it easier to add OR conditions.
foreach (string Tag in Tag_Name)
{
if (Sanitize(Tag))
{
Tag_cond += "\n OR NAME = '" + Tag + "'";
}
}
Tag_cond += ")";
Cmdr.CommandText =
@"SELECT
NAME, VALUE, STATUS
FROM
History
WHERE
" + Tag_cond + @"
AND TS > CAST('" + Time_Stamp.AddSeconds(-1).ToString("yyyy-MM-dd HH:mm:ss") + @"' AS TIMESTAMP FORMAT 'YYYY-MM-DD HH:MI:SS')
AND TS < CAST('" + Time_Stamp.AddSeconds(1).ToString("yyyy-MM-dd HH:mm:ss") + @"' AS TIMESTAMP FORMAT 'YYYY-MM-DD HH:MI:SS')
AND PERIOD = 000:0:01.0;
";
#if DEBUG
DateTime Start_Time = DateTime.Now;
#endif
System.Data.Odbc.OdbcDataReader DR = Cmdr.ExecuteReader(System.Data.CommandBehavior.SingleResult);
while (DR.Read())
{
if (DR.GetValue(2).ToString() == "0") // if status is good
{
Result.Add(DR.GetValue(0).ToString(), DR.GetValue(1));
}
}
DR.Close();
#if DEBUG
double Run_Time = (DateTime.Now - Start_Time).TotalMilliseconds;
Console.WriteLine("Read {0} tags from IP21 in {1} ms", Tag_Name.Length, Run_Time);
foreach (DictionaryEntry p in Result)
{
Console.WriteLine("{0}: {1}", p.Key, p.Value);
}
#endif
return(Result);
}
// SQLGetRows
public List <string[]> SQLGetRows(string command)
{
List <string[]> rows = new List <string[]>();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = command;
sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
string[] row = new string[sqlDataReader.FieldCount];
for (int i = 0; i < row.Length; i++)
{
row[i] = sqlDataReader[i].ToString();
}
rows.Add(row);
}
sqlDataReader.Close();
sqlDataReader = null;
return(rows);
}
/// <summary>
/// DisplayRecords: This function displays the records in the view if any.
/// </summary>
static void DisplayRecords()
{
Console.Write("\tRecord in getpatientbalance stored procedure");
try
{
reader = (OdbcDataReader)command.ExecuteReader();
// read the returned resultset
while (reader.Read())
{
/*
* This view consists the following fields:
* pat_id
* pat_0_30_days
* pat_31_60_days
* pat_61_90_days
* pat_91_120_days
* pat_121_plus_days
* last_pmt_date
* last_pmt_amt
* last_pmt_type
* last_pmt_desc
*/
Console.WriteLine("\npat_id:{0}\npat_0_30_days: {1}\npat_31_60_days: {2} \npat_61_90_days: {3}\npat_91_120_plus_days: {4} \npat_121_plus_days: {5}\nlast_pmt_date: {6} \nlast_pmt_amt: {7}\nlast_pmt_type: {8}\nlast_pmt_desc: {9}",
reader.GetValue(0), reader.GetValue(1), reader.GetValue(2), reader.GetValue(3), reader.GetValue(4), reader.GetValue(5), reader.GetValue(6), reader.GetValue(7), reader.GetValue(8), reader.GetValue(9));
}
// close the reader
reader.Close();
}
catch (Exception e)
{
ExceptionDisplay(e);
}
}
public static void DisposeAll(OdbcCommand sqlCommand, OdbcDataReader dbReader)
{
sqlCommand.Dispose();
dbReader.Close();
}
public ArrayList getDGPContacts(string userId)
{
//Console.WriteLine("UserID: " + userId);
try
{
ArrayList friendList = new ArrayList();
itsCommand = new OdbcCommand("SELECT user FROM friends WHERE friend=\'" + userId + "\'",itsConnection);
itsDataReader = itsCommand.ExecuteReader();
for(int i = 0; itsDataReader.Read(); i++)
{
// Console.WriteLine("Add friend: " + itsDataReader.GetString(0));
friendList.Add(itsDataReader.GetString(0));
}
itsCommand.Dispose();
itsDataReader.Close();
return friendList;
}
catch(OdbcException MyOdbcException) //Catch any ODBC exception ..
{
this.handleException(MyOdbcException);
}
return null;
}
public bool testNameAndPassword(string name, string password, Client client)
{
try
{
int itsInloggId;
itsCommand = new OdbcCommand("",itsConnection);
itsCommand.CommandText = "SELECT userID FROM users WHERE email=\'"+ name + "\'";
itsDataReader = itsCommand.ExecuteReader();
if(itsDataReader.Read())
{
itsInloggId = itsDataReader.GetInt32(0);
itsDataReader.Close();
}
else
{
itsCommand.Dispose();
itsDataReader.Close();
return false;
}
itsCommand.CommandText = "SELECT password FROM users WHERE userID=\'"+ itsInloggId.ToString() + "\'";
itsDataReader = itsCommand.ExecuteReader();
itsDataReader.Read();
string databasePassword = itsDataReader.GetString(0);
if(string.Compare(databasePassword, password, true) == 0)
{
itsDataReader.Close();
itsCommand.CommandText = "SELECT * FROM users WHERE userID=\'"+ itsInloggId.ToString() + "\'";
itsDataReader = itsCommand.ExecuteReader();
itsDataReader.Read();
client.Id = itsDataReader.GetString(0);
client.Email = name;
//client.IpNumber = itsDataReader.GetString(3);
client.DisplayName = itsDataReader.GetString(4);
itsCommand.Dispose();
itsDataReader.Close();
return true;
}
else
{
itsCommand.Dispose();
itsDataReader.Close();
return false;
}
}
catch (OdbcException MyOdbcException)//Catch any ODBC exception ..
{
this.handleException(MyOdbcException);
}
return false;
}
public int readInt( string sqlCommand)
{
try
{
int returnInt = 0;
itsCommand = new OdbcCommand("", itsConnection);
itsCommand.CommandText = sqlCommand;
itsDataReader = itsCommand.ExecuteReader();
while(itsDataReader.Read())
{
returnInt = itsDataReader.GetInt32(0);
}
itsCommand.Dispose();
itsDataReader.Close();
return returnInt;
}
catch (OdbcException MyOdbcException)//Catch any ODBC exception ..
{
this.handleException(MyOdbcException);
}
return 0;
}
public string read( string sqlCommand)
{
try
{
string returnString = "";
itsCommand = new OdbcCommand("", itsConnection);
itsCommand.CommandText = sqlCommand;
itsDataReader = itsCommand.ExecuteReader();
while(itsDataReader.Read())
{
if(returnString != "")
{
returnString += "\n";
}
returnString += itsDataReader.GetString(0);
}
itsCommand.Dispose();
itsDataReader.Close();
return string.Copy(returnString);
}
catch (OdbcException MyOdbcException)//Catch any ODBC exception ..
{
this.handleException(MyOdbcException);
}
return "";
}
public char getUserOnlineStatus(string id)
{
try
{
char itsChar;
itsCommand = new OdbcCommand("",itsConnection);
itsCommand.CommandText = "SELECT status FROM users WHERE userID=\'" + id + "\'";;
itsDataReader = itsCommand.ExecuteReader();
if(itsDataReader.Read())
{
itsChar = itsDataReader.GetChar(0);
itsDataReader.Close();
itsCommand.Dispose();
return itsChar;
}
else
{
itsDataReader.Close();
itsCommand.Dispose();
return 'Z';
}
}
catch(OdbcException MyOdbcException) //Catch any ODBC exception ..
{
this.handleException(MyOdbcException);
}
return 'Z';
}
public bool getUserLogginStatus(int itsId)
{
try
{
char itsChar;
itsCommand = new OdbcCommand("",itsConnection);
itsCommand.CommandText = "SELECT status FROM users WHERE userID=\'" + itsId + "\'";
itsDataReader = itsCommand.ExecuteReader();
if(itsDataReader.Read())
{
itsChar = itsDataReader.GetChar(0);
if(itsChar == 'Y')
{
itsCommand.Dispose();
itsDataReader.Close();
return true;
}
else
{
itsCommand.Dispose();
itsDataReader.Close();
return false;
}
}
else
{
itsDataReader.Close();
itsCommand.Dispose();
return false;
}
}
catch(OdbcException MyOdbcException) //Catch any ODBC exception ..
{
this.handleException(MyOdbcException);
}
return false;
}
internal string[] loadNetQuantity(string p)
{
//assign variables
m_dataReader = null;
m_odbcCommand.CommandText = p;
//open the DB and read the data
OpenDB();
m_dataReader = m_odbcCommand.ExecuteReader(CommandBehavior.CloseConnection);
//intialize variables
string[] stringList = new string[m_dataReader.FieldCount];
//create single dimensional string array to hold DB values
if (m_dataReader.Read())
{
int i = 0;
while (i < m_dataReader.FieldCount)
{
stringList[i] = m_dataReader[i].ToString();
i++;
}
}
//close the connection and return the data
m_dataReader.Close();
return stringList;
}
public void Close(OdbcDataReader reader)
{
if (reader != null) { reader.Close(); }
Close();
}
