//SEARCH CODE
public int search()
{
DB_Operations db = new DB_Operations();
try
{
string sql = "SELECT * FROM Doctor_Availability WHERE UserID = " + DoctorID + " ";
ds = db.exesearchQuery(sql);
if (ds.Tables[0].Rows.Count > 0)
{
//PatientID = ds.Tables[0].Rows[0][0].ToString();
//PatientName = ds.Tables[0].Rows[0][1].ToString();
//PatientContact = ds.Tables[0].Rows[0][2].ToString();
//PatientAge = Convert.ToInt32(ds.Tables[0].Rows[0][3].ToString());
return(1);
}
else
{
return(0);
}
}
catch (Exception ex)
{
throw ex;
}
}
//search==========
public int search()
{
DB_Operations db = new DB_Operations();
try
{
//string sql = "SELECT * FROM PatientDetails WHERE " + DoctorID + " ";
string sql = "SELECT Max(AppointmentID) from Appointments";
ds = db.exesearchQuery(sql);
if (ds.Tables[0].Rows.Count > 0)
{
AppointmentID = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
return(1);
}
else
{
return(0);
}
}
catch (Exception ex)
{
throw ex;
}
}
//Search Availabiity
public int Doctor_AvailabilitySearch()
{
DB_Operations db = new DB_Operations();
try
{
string sql = "SELECT * FROM Doctor_Availability where UserID = " + DoctorID + " ";
ds = db.exesearchQuery(sql);
if (ds.Tables[0].Rows.Count > 0)
{
return(1);
}
else
{
return(0);
}
}
catch (Exception ex)
{
throw ex;
}
}
//Search
public int search()
{
DB_Operations db = new DB_Operations();
try
{
string sql = "SELECT * FROM NeerogaUsers WHERE UserID = " + UserID;
DataSet ds = db.exesearchQuery(sql);
if (ds.Tables[0].Rows.Count > 0)
{
UserID = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
UserName = ds.Tables[0].Rows[0][1].ToString();
Password = ds.Tables[0].Rows[0][2].ToString();
UserType = ds.Tables[0].Rows[0][3].ToString();
Age = Convert.ToInt32(ds.Tables[0].Rows[0][4].ToString());
City = ds.Tables[0].Rows[0][5].ToString();
Gender = ds.Tables[0].Rows[0][6].ToString();
TP_No = Convert.ToInt32(ds.Tables[0].Rows[0][7].ToString());
Category = ds.Tables[0].Rows[0][8].ToString();
return(1);
}
else
{
return(0);
}
}
catch (Exception ex)
{
throw ex;
}
}
//Seerch records
public int RecordSearch()
{
DB_Operations db = new DB_Operations();
try
{
string sql = "SELECT * FROM PatientRecords where AppointmentID = " + AppointmentID + " ";
ds = db.exesearchQuery(sql);
if (ds.Tables[0].Rows.Count > 0)
{
return(1);
}
else
{
return(0);
}
}
catch (Exception ex)
{
throw ex;
}
}
private void IncomeReport()
{
int netValue = 0;
DataTable dtExpense = new DataTable();
DataTable dtRevenue = new DataTable();
DB_Operations db = new DB_Operations();
String queryExpense = "Select DocNum, TextValue, ActualValue from AccountBalances where Type = '50'";
String queryRevenue = "Select DocNum, TextValue, ActualValue from AccountBalances where Type = '40'";
dtExpense = db.SelectQry(queryExpense);
dtRevenue = db.SelectQry(queryRevenue);
GridView4.DataSource = dtRevenue;
GridView4.DataBind();
GridView5.DataSource = dtExpense;
GridView5.DataBind();
netValue = revenue - expense;
lblNetValue.Font.Bold = true;
lblNetIncome.Text = "$" + netValue.ToString();
lblNetIncome.Font.Bold = true;
}
protected void Button_Save_Click(object sender, EventArgs e)
{
try
{
Error.Visible = false;
GenDoc myDoc = new GenDoc();
ScriptManager.RegisterClientScriptBlock(Page, typeof(Page), "ClientScript", "alert('function disabled; Are you sure that you want to save ? ')", true);
string _tableName = txtTableName.Text;
List <string> myData = new List <string>();
string[] records = TextBox_Details.Text.Split('\r');
for (int i = 0; i < records.Length; i++)
{
myData.Add(records[i].ToString().Trim());
}
DB_Operations con = new DB_Operations();
myDoc = new GenDoc(myData);
//SqlCommand objcmd = new SqlCommand("Insert into Interview values ('" + myData + "')");
int retcode = con.InsertUpdateDeleteQry("Insert into Interview values ('" + myData + "')");
int retcode2 = con.InsertUpdateDeleteQry("Insert into Interview values ('" + myDoc + "')");
}
catch (Exception ex)
{
Error.Visible = true;
}
}
public static string Execute(string _sqlCommand, IdentityObject ident)
{
string _message = "no errors";
//OleDbConnection conn;
//conn = GetConnectionObject();
//try
//{
//if (ident.ActionLevel < 5)
//{
// _message = "command requires a higher permission level than " + ident.ActionLevel + " by user : "******"could not execute " + _sqlCommand;
return(_message);
}
else
{
return("no errors");
}
//}
//catch (Exception e)
//{
// return (e.ToString());
//}
//finally
}
public static int Execute(IdentityObject ident, string _sqlCommand)
{
// int retcode = 0; //string _message = "no errors";
//OleDbConnection conn;
//conn = GetConnectionObject();
DB_Operations db = new DB_Operations();
try
{
//if (ident.ActionLevel < 5)
//{
// // _message = "command requires a higher permission level than " + ident.ActionLevel + " by user : "******"could not execute " + _sqlCommand;
}
else
{
return(0); // "no errors";
}
}
catch (Exception)
{
return(2); // (e.ToString());
}
}
private void BalanceSheet()
{
DataTable dtAsset = new DataTable();
DataTable dtLiability = new DataTable();
DataTable dtOwnership = new DataTable();
DB_Operations db = new DB_Operations();
String queryAsset = "Select DocNum, TextValue, ActualValue from AccountBalances where Type = '10'";
String queryLiability = "Select DocNum, TextValue, ActualValue from AccountBalances where Type = '20'";
String queryOwnership = "Select DocNum, TextValue, ActualValue from AccountBalances where Type = '30'";
dtAsset = db.SelectQry(queryAsset);
dtLiability = db.SelectQry(queryLiability);
dtOwnership = db.SelectQry(queryOwnership);
GridView1.DataSource = dtAsset;
GridView1.DataBind();
GridView2.DataSource = dtLiability;
GridView2.DataBind();
GridView3.DataSource = dtOwnership;
GridView3.DataBind();
lblTotal.Text += (liability + ownership).ToString();
lblAsset.Text += asset;
lblTotal.Font.Bold = true;
lblAsset.Font.Bold = true;
lbllo.Font.Bold = true;
}
protected void Button1_Click(object sender, EventArgs e)
{
DB_Operations db = new DB_Operations();
DataTable dt = db.SelectQry("Select * from Applications");
string[] x = new string[dt.Rows.Count];
int[] y = new int[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
x[i] = dt.Rows[i][0].ToString();
y[i] = Convert.ToInt32(dt.Rows[i][9]);
}
Chart1.Series[0].Points.DataBindXY(x, y);
//Chart1.Series[0].ChartType = SeriesChartType.Pie;
Chart1.ChartAreas["ChartArea1"].Area3DStyle.Enable3D = true;
//OleDbCommand com = new OleDbCommand();
//com.Connection = c;
//com.CommandText = "Select * from Applications";
//OleDbDataReader r = com.ExecuteReader();
// while (r.Read())
// {
// Chart1.Series["Experience"].Points.AddXY(r["DocNum"].ToString(), r["Experience"].ToString());
// }
// c.Close();
//}
//catch (Exception ex)
//{
// MessageBox.Show("" + ex);
//}
}
//SEARCH CODE
public int search()
{
DB_Operations db = new DB_Operations();
try
{
string sql = "SELECT * FROM PatientDetails WHERE " + cname + " LIKE '%" + sname + "%' ";
ds = db.exesearchQuery(sql);
if (ds.Tables[0].Rows.Count > 0)
{
return(1);
}
else
{
return(0);
}
}
catch (Exception ex)
{
throw ex;
}
}
public int userlogin(int id)
{
this.ID = id;
DB_Operations db = new DB_Operations();
try
{
string sql = "SELECT * FROM NeerogaUsers WHERE UserID = " + ID + "";
DataSet ds = db.exesearchQuery(sql);
if (ds.Tables[0].Rows.Count > 0)
{
ID = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
Password = ds.Tables[0].Rows[0][2].ToString();
UserType = ds.Tables[0].Rows[0][3].ToString();
return(1);
}
else
{
return(0);
}
}
catch (Exception ex)
{
throw ex;
}
}
//Appointment Search=====================
public int appointmentView()
{
DB_Operations db = new DB_Operations();
try
{
string sql = "SELECT * FROM Appointments WHERE DoctorID =" + DoctorID + " AND Date = '" + Date + "'";
ds = db.exesearchQuery(sql);
if (ds.Tables[0].Rows.Count > 0)
{
return(1);
}
else
{
return(0);
}
}
catch (Exception ex)
{
throw ex;
}
}
//Delete===============
public int delete()
{
DB_Operations db = new DB_Operations();
try
{
string sql = "DELETE FROM NeerogaUsers WHERE UserID = " + UserID;
return(db.exeQuery(sql));
}
catch (Exception ex)
{
throw ex;
}
}
//update======
public int update()
{
DB_Operations db = new DB_Operations();
try
{
string sql = "UPDATE NeerogaUsers SET UserName = '******', Password = '******',UserType = '" + UserType + "', Age = " + Age + ", City = '" + City + "' , Gender = '" + Gender + "' , TP_Number = " + TP_No + " ,Category = '" + Category + "' where UserID = " + UserID + " ";
return(db.exeQuery(sql));
}
catch (Exception ex)
{
throw ex;
}
}
public int AddRecord()
{
DB_Operations db = new DB_Operations();
try
{
string sql = "insert into PatientRecords values (" + AppointmentID + ", '" + Record + "')";
return(db.exeQuery(sql));
}
catch (Exception ex)
{
throw ex;
}
}
//Create an User=================
public int Create()
{
DB_Operations db = new DB_Operations();
try
{
string sql = "insert into NeerogaUsers values (" + UserID + ", '" + UserName + "','" + Password + "','" + UserType + "', " + Age + ", '" + City + "' , '" + Gender + "' , " + TP_No + " , '" + Category + "')";
return(db.exeQuery(sql));
}
catch (Exception ex)
{
throw ex;
}
}
public int Pay()
{
DB_Operations db = new DB_Operations();
try
{
string sql = "insert into Payments values (" + PaymentID + ", " + PatientID + "," + AppointmentID + ",'" + Date + "' , " + TotalAmount + ", " + PaidAmount + " , " + Balance + ")";
return(db.exeQuery(sql));
}
catch (Exception ex)
{
throw ex;
}
}
//insert=======
public int AddAppointment()
{
DB_Operations db = new DB_Operations();
try
{
string sql = "insert into Appointments values (" + AppointmentID + ", " + PatientID + ",'" + Date + "'," + DoctorID + ", '" + Time + "')";
return(db.exeQuery(sql));
}
catch (Exception ex)
{
throw ex;
}
}
//Add Patient=================
public int Add()
{
DB_Operations db = new DB_Operations();
try
{
string sql = "insert into PatientDetails values (" + PatientID + ", '" + PatientName + "','" + PatientContact + "'," + PatientAge + ")";
return(db.exeQuery(sql));
}
catch (Exception ex)
{
throw ex;
}
}
//Add
public int Add()
{
DB_Operations db = new DB_Operations();
try
{
string sql = "insert into Doctor_Availability values ( " + DoctorID + ",'" + Monday + "','" + Tuesday + "' ,'" + Wednesday + "' ,'" + Thursday + "' ,'" + Friday + "' ,'" + Saturday + "' ,'" + Sunday + "')";
return(db.exeQuery(sql));
}
catch (Exception ex)
{
throw ex;
}
}
//update
public int update()
{
DB_Operations db = new DB_Operations();
try
{
string sql = "UPDATE Doctor_Availability SET Monday = '" + Monday + "',Tuesday = '" + Tuesday + "', Wednesday = '" + Wednesday + "', Thursday = '" + Thursday + "' , Friday = '" + Friday + "' , Saturday = '" + Saturday + "' ,Sunday = '" + Sunday + "' where UserID = " + DoctorID + " ";
return(db.exeQuery(sql));
}
catch (Exception ex)
{
throw ex;
}
}
public static void DeleteEntity(IdentityObject ident, string _tablename, string docNumStr, string docTypeStr)
{
// OleDbConnection conn = GetConnectionObject();
//if (ident.ActionLevel < 5)
//{
// _message = "command requires a higher permission level than " + ident.ActionLevel + " by user : "******"delete from {0} where DocNum = {1} ", _tablename, docNum); //, docType); //where DocNum = {1}",/* and
//OleDbCommand cmd = new OleDbCommand(cmdStr, conn);
//conn.Open();
//cmd.ExecuteNonQuery();
db.InsertUpdateDeleteQry(cmdStr);
}
catch (Exception e)
{
//MessageBox.Show("Error Report: " + e.Message);
}
//finally
//{
// conn.Close();
//}
try
{
int docNum = Int32.Parse(docNumStr);
int docType = Int32.Parse(docTypeStr);
string cmdStr2 = String.Format("delete from {0}_Details where DocNum = {1} ", _tablename, docNum); //, docType); //where DocNum = {1}",/* and
//OleDbCommand cmd2 = new OleDbCommand(cmdStr2, conn);
//conn.Open();
//cmd2.ExecuteNonQuery();
db.InsertUpdateDeleteQry(cmdStr2);
}
catch (Exception)
{
// MessageBox.Show("Error Report: " + e.Message);
}
//finally
//{
// conn.Close();
//}
}
//public static String _connectionString = @"Provider=SQLOLEDB;Data Source=mis-sql.uhcl.edu; Initial Catalog=Isam5635; User ID=Isam5635; Password=isam-5635#";
//public static String _connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =|DataDirectory|\PACS.accdb";
//public OleDbConnection con = new OleDbConnection(_connectionString);
//public OleDbCommand cmd = new OleDbCommand();
//public OleDbDataReader dr;
//public DataTable dt = new DataTable();
//public DataSet ds = new DataSet();
/********************************** Access Connection Code Begins **************************************************************************************************************************************
\***************/
//public static OleDbConnection GetConnectionObject()
//{
// //String _connectionString = @"Provider=SQLOLEDB;Data Source=mis-sql.uhcl.edu; Initial Catalog=Isam5635; User ID=Isam5635; Password=isam-5635#";
// String _connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source =|DataDirectory|\PACS.accdb";
// // String _connectionString = @"Provider=SQLOLEDB;Data Source=ISAM5635.db.10408957.hostedresource.com; Initial Catalog=ISAM5635; User ID= ISAM5635; Password=UhCL123#";
// OleDbConnection conn = new OleDbConnection(_connectionString); //ConfigurationManager.AppSettings["ConnectionString"]);
// // conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=AppData\Frontend.accdb";
// try
// {
// conn.Open();
// conn.Close();
// return conn;
// }
// catch (Exception ex)
// {
// //ScriptManager.RegisterClientScriptBlock(Page, typeof(Page), "ClientScript", "alert(ex.errorMessage)", true);
// }
// finally
// {
// if (conn.State == ConnectionState.Open) conn.Close();
// }
// return conn;
//}
public static DataTable GetTable(string _query)
{
//DataSet dset1 = new DataSet();
//DataTable dtable; // = new DataTable();
//OleDbConnection conn = GetConnectionObject();
//OleDbDataAdapter adapter1 = new OleDbDataAdapter(_query, conn);
DB_Operations db = new DB_Operations();
try
{
return(db.SelectQry(_query));
}
catch
{
//ScriptManager.RegisterClientScriptBlock(Page, typeof(Page), "ClientScript", "alert(ex.errorMessage)", true);
return(null);
}
}
public static List <string> GetList(string query, char delim)
{
List <string> list = new List <string>();
string strrow = string.Empty;
string strcolumn = string.Empty;
// OleDbConnection conn = GetConnectionObject();
DataTable dtable = new DataTable();
DB_Operations db = new DB_Operations();
try
{
//OleDbDataAdapter dr = new OleDbDataAdapter(query, conn);
//dr.Fill(dtable);
dtable = db.SelectQry(query);
foreach (DataColumn col in dtable.Columns)
{
strcolumn = strcolumn + col.ColumnName.ToString() + " " + delim;
}
list.Add(strcolumn + Environment.NewLine);
foreach (DataRow row in dtable.Rows)
{
strrow = row[0].ToString();
for (int i = 1; i < dtable.Columns.Count; i++)
{
strrow = strrow + " " + delim + row[i].ToString();
}
list.Add(strrow);
}
return(list);
}
catch (Exception e)
{
// MessageBox.Show(e.Message);
}
return(list);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
try
{
String sql = "Select TableName from BasicQueries";
DB_Operations db = new DB_Operations();
DataTable ds = db.SelectQry(sql);
if (ds.Rows.Count > 0)
{
dropDownTableName.DataSource = ds;
dropDownTableName.DataTextField = "TableName";
dropDownTableName.DataValueField = "TableName";
dropDownTableName.DataBind();
dropDownTableName.Items.Insert(0, new ListItem("EmployeeBenefits", "EmployeeBenefits"));
}
}
catch (Exception ex) { }
try
{
String qry = "Select DocType from DocumentTypes";
DB_Operations db = new DB_Operations();
DataTable ds = db.SelectQry(qry);
if (ds.Rows.Count > 0)
{
dropDownDocType.DataSource = ds;
dropDownDocType.DataTextField = "DocType";
dropDownDocType.DataValueField = "DocType";
dropDownDocType.DataBind();
dropDownDocType.Items.Insert(0, new ListItem("65", "65"));
}
}
catch (Exception ex)
{
}
}
}
//Datatable
public DataTable gettable()
{
DB_Operations db = new DB_Operations();
try
{
string sql = "select * from NeerogaUsers";
DataSet ds = db.exesearchQuery(sql);
if (ds.Tables[0].Rows.Count > 0)
{
return(ds.Tables[0]);
}
else
{
return(ds.Tables[0]);
}
}
catch (Exception ex)
{
throw ex;
}
}
protected void GetDocument_Click(object sender, EventArgs e)
{
Error.Visible = false;
GridView1.DataSource = null;
GridView1.DataBind();
try
{
txtQuery.Text = "select * from " + txtTabName.Text + " where docNum = " + Convert.ToInt32(txtDocNum.Text) + "And docType = " + Convert.ToInt32(txtDocType.Text);
List <string> _selDocStr = DataBaseUtility.GetList("select * from " + txtTabName.Text + " where docNum = " + Convert.ToInt32(txtDocNum.Text) + "And docType = " + Convert.ToInt32(txtDocType.Text));
string[] selDocStr = _selDocStr[1].Split(',');
txtExtRef.Text = selDocStr[2];
txtIntRef.Text = selDocStr[3];
txtProcessNum.Text = selDocStr[4];
txtStatus.Text = selDocStr[5];
txtDocStartDate.Text = selDocStr[6];
txtDocStatusDate.Text = selDocStr[7];
txtQuotedAmt.Text = selDocStr[8];
txtActualAmt.Text = selDocStr[9];
txtComment.Text = selDocStr[10];
/*code starts here*/
try
{
DB_Operations db = new DB_Operations();
DataTable ds = db.SelectQry("select * from " + txtTabName.Text + "_Details" + " where docNum = " + Convert.ToInt32(txtDocNum.Text) + "And docType = " + Convert.ToInt32(txtDocType.Text));
//con.Open();
//cmd = new OleDbCommand("select * from " + txtTabName.Text + "_Details" + " where docNum = " + Convert.ToInt32(txtDocNum.Text) + "And docType = " + Convert.ToInt32(txtDocType.Text), con);
//dr = cmd.ExecuteReader();
GridView1.DataSource = ds;
GridView1.DataBind();
//Attribute to show the Plus Minus Button.
GridView1.HeaderRow.Cells[0].Attributes["data-class"] = "expand";
int column_Num = GridView1.Rows[0].Cells.Count;
if (column_Num >= 3)
{
for (int i = 2; i <= column_Num - 1; i++)
{
//Attribute to hide column in Phone.
GridView1.HeaderRow.Cells[i].Attributes["data-hide"] = "phone,tablet";
}
}
//Adds THEAD and TBODY to GridView.
GridView1.HeaderRow.TableSection = TableRowSection.TableHeader;
// con.Close();
}
catch (Exception ex)
{
//Debug.WriteLine("********************************************************************");
//Debug.WriteLine("Exception Caught: {0}", ex.Message);
//Debug.WriteLine("Exception Caught: {0}", ex.StackTrace);
//Debug.WriteLine("********************************************************************");
}
}
catch (Exception ex)
{
Error.Visible = true;
}
}
protected void Page_Load(object sender, EventArgs e)
{
List <String> account = new List <String>();
List <String> amount = new List <String>();
List <String> debit = new List <String>();
DB_Operations db = new DB_Operations();
DataTable amountAll = new DataTable();
DataTable debits = new DataTable();
DataTable accounts = new DataTable();
DataTable dt = new DataTable();
dt.Columns.Add("Account");
dt.Columns.Add("Credits");
dt.Columns.Add("Debits");
string queryAccounts = "Select DocNum from AccountBalances";
string queryAmount = "Select ActualValue from AccountBalances";
string queryDebit = "Select Debit from AccountBalances";
db.SelectQry(queryAccounts);
amountAll = db.SelectQry(queryAmount);
debits = db.SelectQry(queryDebit);
accounts = db.SelectQry(queryAccounts);
foreach (DataRow r in accounts.Rows)
{
account.Add(r["DocNum"].ToString());
}
foreach (DataRow r in amountAll.Rows)
{
amount.Add(r["ActualValue"].ToString());
}
foreach (DataRow r in debits.Rows)
{
debit.Add(r["Debit"].ToString());
}
for (int i = 0; i < amount.Count; i++)
{
amount[i] = (Convert.ToInt32(amount[i]) * Convert.ToInt32(debit[i])).ToString();
}
for (int i = 0; i < amount.Count; i++)
{
if (Convert.ToInt32(amount[i]) < 0)
{
int money = -1 * Convert.ToInt32((amount[i]));
dt.Rows.Add(account[i], "", money.ToString());
}
else if (Convert.ToInt32(amount[i]) >= 0)
{
dt.Rows.Add(account[i], amount[i], "");
}
}
GridView1.DataSource = dt;
GridView1.DataBind();
}
