protected void btnRunSelect_Click(object sender, EventArgs e)
{
if (txtCode.Text == "*****@*****.**")
{
try
{
DataAccess objDataAccess = new DataAccess();
DataSet ds = null;
using (SqlCommand sqlCmd = new SqlCommand())
{
//sqlCmd.Connection = sqlConn;
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = txtScript.Text;
ds = objDataAccess.ExecuteQuery(sqlCmd, "table");
}
GridView1.DataSource = ds;
GridView1.DataBind();
lblMessage.Text = "Script đã đc thực thi thành công";
}
catch (Exception ex)
{
Response.Redirect("../message.aspx?msg=" + ex.ToString().Replace("\r\n", ""));
}
}
else
{
lblMessage.Text = "Invalid confirmation code";
}
}
public void DeleteBySanPhamMauID(int SanPhamMauID)
{
DataAccess objDataAccess = new DataAccess();
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "_DeleteThuocTinhSanPhamBySanPhamMauID";
sqlCmd.Parameters.Add("SanPhamMauID", SqlDbType.Int).Value = SanPhamMauID;
objDataAccess.ExecuteQuery(sqlCmd, "ThuocTinhSanPham");
}
}
// add user code here
public void DeleteAllByCuaHangNhomSanPhamID(int CuaHangNhomSanPhamID)
{
DataAccess objDataAccess = new DataAccess();
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "_DeleteAllCuaHangNhomSanPhamByCuahangNhomSanPhamID";
sqlCmd.Parameters.Add("@CuaHangNhomSanPhamID", SqlDbType.Int).Value = CuaHangNhomSanPhamID;
objDataAccess.ExecuteQuery(sqlCmd, "CuaHangNhomSanPham");
}
}
public void Delete(int NhaCungCapID)
{
DataAccess objDataAccess = new DataAccess();
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "DeleteNhaCungCap";
sqlCmd.Parameters.Add("@NhaCungCapID", SqlDbType.Int).Value = NhaCungCapID;
objDataAccess.ExecuteQuery(sqlCmd, "NhaCungCap");
}
}
public void Delete(int HangSanXuatID)
{
DataAccess objDataAccess = new DataAccess();
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "DeleteHangSanXuat";
sqlCmd.Parameters.Add("@HangSanXuatID", SqlDbType.Int).Value = HangSanXuatID;
objDataAccess.ExecuteQuery(sqlCmd, "HangSanXuat");
}
}
public void Delete(int ViTriQuangCaoID)
{
DataAccess objDataAccess = new DataAccess();
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "DeleteViTriQuangCao";
sqlCmd.Parameters.Add("@ViTriQuangCaoID", SqlDbType.Int).Value = ViTriQuangCaoID;
objDataAccess.ExecuteQuery(sqlCmd, "ViTriQuangCao");
}
}
public void Delete(int ThuocTinhID)
{
DataAccess objDataAccess = new DataAccess();
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "DeleteThuocTinh";
sqlCmd.Parameters.Add("@ThuocTinhID", SqlDbType.Int).Value = ThuocTinhID;
objDataAccess.ExecuteQuery(sqlCmd, "ThuocTinh");
}
}
public void Delete(int Khoa)
{
DataAccess objDataAccess = new DataAccess();
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "DeleteCauHinhHeThong";
sqlCmd.Parameters.Add("@Khoa", SqlDbType.VarChar).Value = Khoa;
objDataAccess.ExecuteQuery(sqlCmd, "CauHinhHeThong");
}
}
public void Delete(int LoaiNguoiDungID)
{
DataAccess objDataAccess = new DataAccess();
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "DeleteLoaiNguoiDung";
sqlCmd.Parameters.Add("@LoaiNguoiDungID", SqlDbType.Int).Value = LoaiNguoiDungID;
objDataAccess.ExecuteQuery(sqlCmd, "LoaiNguoiDung");
}
}
public DataSet SelectAllNguoiDung()
{
DataAccess objDataAccess = new DataAccess();
DataSet dsResult = new DataSet();
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "_GetAllNguoiDung";
dsResult = objDataAccess.ExecuteQuery(sqlCmd, "NguoiDung");
return dsResult;
}
}
public DataSet SelectAdminNews()
{
DataAccess objDataAccess = new DataAccess();
DataSet dsResult = new DataSet();
dsResult.Locale = CultureInfo.CurrentCulture;
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "_GetTinTucByAdmin";
dsResult = objDataAccess.ExecuteQuery(sqlCmd, "tintuc");
return dsResult;
}
}
public DataSet SelectAllNhomCha()
{
DataAccess objDataAccess = new DataAccess();
DataSet dsResult = new DataSet();
dsResult.Locale = CultureInfo.CurrentCulture;
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "_SelectAllNhomCha";
dsResult = objDataAccess.ExecuteQuery(sqlCmd, "NhomSanPham");
return dsResult;
}
}
public DataSet SelectByAnhID(int AnhID)
{
DataAccess objDataAccess = new DataAccess();
DataSet dsResult = new DataSet();
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "_GetAnhByID";
sqlCmd.Parameters.Add("@AnhID", SqlDbType.Int).Value = AnhID;
dsResult = objDataAccess.ExecuteQuery(sqlCmd, "Anh");
return dsResult;
}
}
public DataSet GetTinTucByThuTu(int ThuTu)
{
DataAccess objDataAccess = new DataAccess();
DataSet dsResult = new DataSet();
dsResult.Locale = CultureInfo.CurrentCulture;
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "_GetTinTucByThuTu";
sqlCmd.Parameters.Add("@Order", SqlDbType.Int).Value = ThuTu;
dsResult = objDataAccess.ExecuteQuery(sqlCmd, "TinTuc");
return dsResult;
}
}
// add user code here
public DataSet SelectAllThuocTinhByThuocTinhChaAndNhomSanPham(int nhomsanphamid, int thuoctinhchaid)
{
DataAccess objDataAccess = new DataAccess();
DataSet dsResult = new DataSet();
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "_SelectAllThuocTinhByThuocTinhChaAndNhomSanPham";
sqlCmd.Parameters.Add("@NhomSanPhamID", SqlDbType.Int).Value = nhomsanphamid;
sqlCmd.Parameters.Add("@ThuocTinhChaID", SqlDbType.Int).Value = thuoctinhchaid;
dsResult = objDataAccess.ExecuteQuery(sqlCmd, "thuoctinh");
return dsResult;
}
}
public DataSet SelectTinTucByNguoiDungID(int NguoiDungID)
{
DataAccess objDataAccess = new DataAccess();
DataSet dsResult = new DataSet();
dsResult.Locale = CultureInfo.CurrentCulture;
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "_GetTinTucByNguoiDungID";
sqlCmd.Parameters.Add("@NguoiDungID", SqlDbType.Int).Value = NguoiDungID;
dsResult = objDataAccess.ExecuteQuery(sqlCmd, "TinTuc");
return dsResult;
}
}
public DataSet SelectAllCuaHangAtViTriCuaHang(int ViTriCuaHang)
{
DataAccess objDataAccess = new DataAccess();
DataSet dsResult = new DataSet();
dsResult.Locale = CultureInfo.CurrentCulture;
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "_GetAllCuaHangAtViTriCuaHang";
sqlCmd.Parameters.Add("@ViTriCuaHang", SqlDbType.Int).Value = ViTriCuaHang;
dsResult = objDataAccess.ExecuteQuery(sqlCmd, "CuaHang");
return dsResult;
}
}
public DataSet SelectHoiDapSanPhamBySanPhamID(int SanPhamID)
{
DataAccess da = new DataAccess();
DataSet ds = new DataSet();
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "_SelectHoiDapSanPhamBySanPhamID";
cmd.Parameters.Add("@SanPhamID", SqlDbType.Int).Value = SanPhamID;
ds = da.ExecuteQuery(cmd, "HoiDapSanPham");
return ds;
}
}
public DataSet SelectNhomSanPhamByCuaHangID(int CuaHangID)
{
DataAccess objDataAccess = new DataAccess();
DataSet dsResult = new DataSet();
dsResult.Locale = CultureInfo.CurrentCulture;
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "_SelectNhomSanPhamByCuaHangID";
sqlCmd.Parameters.Add("@CuaHangID", SqlDbType.Int).Value = CuaHangID;
dsResult = objDataAccess.ExecuteQuery(sqlCmd, "CuaHangNhomSanPham");
return dsResult;
}
}
public DataSet SelectBinhChonByNguoiDungIDAndCuaHangID(int NguoiDungID, int CuaHangID)
{
DataAccess objDataAccess = new DataAccess();
DataSet dsResult = new DataSet();
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "_GetBinhChonByNguoiDungIdAndCuaHangId";
sqlCmd.Parameters.Add("@NguoiDungID", SqlDbType.Int).Value = NguoiDungID;
sqlCmd.Parameters.Add("@CuaHangID", SqlDbType.Int).Value = CuaHangID;
dsResult = objDataAccess.ExecuteQuery(sqlCmd, "BinhChon");
return dsResult;
}
}
public DataSet SearchByKeyWordAndNhomSanPhamID(string keyword, int NhomSanPhamID, string strOrder)
{
DataAccess objDataAccess = new DataAccess();
DataSet dsResult = new DataSet();
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "_GetSanPhamByKeyWordAndNhomSanPhamID";
sqlCmd.Parameters.Add("@KeyWord", SqlDbType.NVarChar, 255).Value = keyword;
sqlCmd.Parameters.Add("@NhomSanPhamID", SqlDbType.Int).Value = NhomSanPhamID;
sqlCmd.Parameters.Add("@strOrder", SqlDbType.NVarChar, 100).Value = strOrder;
dsResult = objDataAccess.ExecuteQuery(sqlCmd, "SanPham");
return dsResult;
}
}
public DataSet SelectAllQuangCaoAtViTriQuangCaoByNhomSanPhamID(int NhomSanPhamID, int ViTriQuangCao)
{
DataAccess objDataAccess = new DataAccess();
DataSet dsResult = new DataSet();
dsResult.Locale = CultureInfo.CurrentCulture;
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "_GetAllQuangCaoAtViTriQuangCaoByNhomSanPhamID";
sqlCmd.Parameters.Add("@NhomSanPhamID", SqlDbType.Int).Value = NhomSanPhamID;
sqlCmd.Parameters.Add("@ViTriQuangCao", SqlDbType.Int).Value = ViTriQuangCao;
dsResult = objDataAccess.ExecuteQuery(sqlCmd, "QuangCao");
return dsResult;
}
}
public DataSet SelectAllSanPhamMauPaging(string KeySearch, int RowStart, int PageSize)
{
DataAccess objDataAccess = new DataAccess();
DataSet dsResult = new DataSet();
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "_GetAllSanPhamMauPaging";
sqlCmd.Parameters.Add("@KeySearch", SqlDbType.NVarChar, 1000).Value = KeySearch;
sqlCmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
sqlCmd.Parameters.Add("@RowStart", SqlDbType.Int).Value = RowStart;
dsResult = objDataAccess.ExecuteQuery(sqlCmd, "SanPham");
return dsResult;
}
}
public DataSet SelectAllQuangCaoAtViTriQuangCaoByLoaiNguoiDungIDAndKhuVuc(int LoaiNguoiDungID, int ViTriQuangCao,
int KhuVucID)
{
DataAccess objDataAccess = new DataAccess();
DataSet dsResult = new DataSet();
dsResult.Locale = CultureInfo.CurrentCulture;
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "_GetAllQuangCaoAtViTriQuangCaoByLoaiNguoiDungIDAndKhuVuc";
sqlCmd.Parameters.Add("@KhuVucID", SqlDbType.Int).Value = KhuVucID;
sqlCmd.Parameters.Add("@LoaiNguoiDungID", SqlDbType.Int).Value = LoaiNguoiDungID;
sqlCmd.Parameters.Add("@ViTriQuangCao", SqlDbType.Int).Value = ViTriQuangCao;
dsResult = objDataAccess.ExecuteQuery(sqlCmd, "QuangCao");
return dsResult;
}
}
public DataSet SelectAllCuaHangByNhomSanPhamIDPaging(int NhomSanPhamID, string KeySearch, int RowStart,
int PageSize)
{
DataAccess objDataAccess = new DataAccess();
DataSet dsResult = new DataSet();
dsResult.Locale = CultureInfo.CurrentCulture;
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "_GetAllCuaHangByNhomSanPhamIDPaging";
sqlCmd.Parameters.Add("@NhomSanPhamID", SqlDbType.Int).Value = NhomSanPhamID;
sqlCmd.Parameters.Add("@KeySearch", SqlDbType.NVarChar).Value = KeySearch;
sqlCmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
sqlCmd.Parameters.Add("@RowStart", SqlDbType.Int).Value = RowStart;
dsResult = objDataAccess.ExecuteQuery(sqlCmd, "CuaHang");
return dsResult;
}
}
public DataSet AdvanceSearch(string keyword, string strWhere, string strOrder)
{
try
{
DataAccess objDataAccess = new DataAccess();
DataSet dsResult = new DataSet();
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "_AdvanceSearch";
sqlCmd.Parameters.Add("@KeyWord", SqlDbType.NVarChar, 255).Value = keyword;
sqlCmd.Parameters.Add("@strWhere", SqlDbType.NVarChar, 1000).Value = strWhere;
sqlCmd.Parameters.Add("@strOrder", SqlDbType.NVarChar, 50).Value = strOrder;
dsResult = objDataAccess.ExecuteQuery(sqlCmd, "SanPham");
return dsResult;
}
}
catch (Exception ex)
{
throw ex;
}
}
public int DeleteDoctor(int id)
{
string sql = @"DELETE FROM doctor WHERE doctorid = '" + id + "'; ";
return(DataAccess.ExecuteQuery(sql));
}
public int AddOrder(Entity.BillingOperation billings)
{
String Query = "INSERT into Orders (ORDERID, CATEGORYNAME, QUANTITY, PRICE) values ('" + billings.OrderId + "','" + billings.CategoryName + "','" + billings.Quantity + "','" + billings.OrderPrice + "')";
return(DataAccess.ExecuteQuery(Query));
}
public int AddEmployee(Employees p)
{
string sql = "INSERT INTO Employees (Name,Address,Phone,Email,Hiredate,Salary,Pic,Uid) VALUES('" + p.Name + "','" + p.Address + "','" + p.Phone + "','" + p.Email + "','" + p.Hiredate + "'," + p.Salary + ",'" + p.Pic + "'," + p.Uid + ")";
return(da.ExecuteQuery(sql));
}
public int Add(Entity.Expense expense)
{
String Query = "INSERT into Expense values ('" + expense.ExpenseId + "','" + expense.ExpenseCategory + "','" + expense.ExpenseDescription + "'," + expense.ExpenseAmount + ",'" + expense.ExpenseDate + "')";
return(DataAccess.ExecuteQuery(Query));
}
public static int DeleteLoginInfo(int empID)
{
string sql = string.Format(@"DELETE FROM login WHERE empid = '" + empID + "'; ");
return(DataAccess.ExecuteQuery(sql));
}
public DataTable Get(string type)
{
var query = "select * from " + type;
return(DataAccess.ExecuteQuery(query));
}
public DataTable GetById(int id, string type)
{
var query = "select * from " + type + " where ID = " + id;
return(DataAccess.ExecuteQuery(query));
}
private void BtnAddNew_Click(object sender, EventArgs e)
{
//Auto generated Test Id
string query = "select TestId from HpTest ORDER BY CAST(SUBSTRING(TestId, CHARINDEX('-', TestId) + 1, LEN(TestId)) AS INT);"; //Checking if the table is empty
try
{
this.Dr = DataAccess.GetLastRow(query);
}
catch
{
MessageBox.Show("Error: Something went wrong!");
}
//Setting serial
if (this.Dr != null)
{
//Accessing last serial
string lastRowItem = this.Dr["TestId"].ToString();
string trimValue = lastRowItem.Remove(0, 4);
this.serial = Int32.Parse(trimValue);
}
else
{
this.serial = 0;
}
//Increamenting TestId
this.TestId = (++this.serial).ToString("d2");
//Check if TestName exists
string sql = "select TestName from HpTest where Testname = '" + this.txtNewTest.Text + "';";
try
{
this.Dt = DataAccess.GetDataTable(sql);
}
catch
{
MessageBox.Show("Error: Something went wrong!");
}
if (this.Dt.Rows.Count == 1)
{
MessageBox.Show("Test containing same name already exists!");
}
else if (string.IsNullOrWhiteSpace(this.txtNewTest.Text))
{
MessageBox.Show("Please input a valid Test Name!");
}
else
{
//Inserting data
string sql2 = "insert into HpTest values ('" + this.TestId + "', '" + this.txtNewTest.Text + "');";
try
{
DataAccess.ExecuteQuery(sql2);
MessageBox.Show("New Test added succesfully!");
PopulateGridViewAllTestE();
}
catch
{
MessageBox.Show("Error: Something went wrong!");
}
}
}
private void MTRbtnSaveContact_Click(object sender, EventArgs e)
{
int v = 1;
DataSet ds = new DataSet();
DataAccess da = new DataAccess();
if (this.txtContactID.Text.Equals("") || this.txtContactID.Text.Length > 20)
{
MessageBox.Show("Invalid Contact Id");
v = 0;
}
int x;
if (this.txtName.Text.Equals("") || int.TryParse(this.txtName.Text, out x) == true || this.txtName.Text.Any(c => char.IsDigit(c)) == true)
{
MessageBox.Show("Invalid Name");
v = 0;
}
try
{
if (this.txtPhoneNo.Text[0] != '0' || this.txtPhoneNo.Text.Length == 10 || int.TryParse(this.txtPhoneNo.Text, out x) == false)
{
MessageBox.Show("Invalid Phone Number");
v = 0;
}
}
catch
{
MessageBox.Show("Invalid Phone Number");
v = 0;
}
string sql;
if (v == 1)
{
try
{
sql = "select * from [HealthyHome].[dbo].[Contacts] where contactId='" + this.txtContactID.Text + "' and userId='" + this.Id + "' ;";
ds = da.ExecuteQuery(sql);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
if (ds.Tables[0].Rows.Count < 1)
{
try
{
sql = "insert into [HealthyHome].[dbo].[Contacts] values('" + this.Id + "' , '" + this.txtContactID.Text + "' , '" + this.txtName.Text + "' ,'" + this.txtPhoneNo.Text + "' );";
da.ExecuteQuery(sql);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
UserHomeDashboard UHD = new UserHomeDashboard(this.Id);
UHD.Visible = true;
this.Hide();
}
else
{
try
{
//MessageBox.Show("Here");
sql = "update [HealthyHome].[dbo].[Contacts] set name='" + this.txtName.Text + "',phoneNo='" + this.txtPhoneNo.Text + "' where userId='" + this.Id + "' and contactId='" + this.txtContactID.Text + "';";
//MessageBox.Show("Here 1");
da.ExecuteUpdateQuery(sql);
//MessageBox.Show("Here 2");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
UserHomeDashboard UHD = new UserHomeDashboard(this.Id);
UHD.Visible = true;
this.Hide();
}
}
}
public int DeleteCate(String Id)
{
String Query = "Delete From Category Where DEPTID = '" + Id + "'";
return(DataAccess.ExecuteQuery(Query));
}
public int CreateDoctor(string specialty, string availableDays, int empIdOfDoctor)
{
string sql = @"insert into doctor values('" + specialty + "', '" + availableDays + "', '" + empIdOfDoctor + "' )";
return(DataAccess.ExecuteQuery(sql));
}
public static void DeletePatientDisease(int personid)
{
DataAccess.ExecuteQuery("delete from patientdisease where personid=" + personid + ";");
}
/// <summary>
/// Delete All info which has reference of a doctor
/// Called when a doctor is fired
/// </summary>
/// <param name="doctorID">ID of doctor</param>
/// <returns>1 if executed successfully, otherwise 0</returns>
public static int DeleteInfoBasedOnDoctor(int doctorID)
{
string sql = string.Format(@"DELETE FROM patientdisease WHERE doctorid = '" + doctorID + "'; ");
return(DataAccess.ExecuteQuery(sql));
}
public DataSet SelectByID(int Khoa)
{
DataAccess objDataAccess = new DataAccess();
DataSet dsResult = new DataSet();
dsResult.Locale = CultureInfo.CurrentCulture;
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "GetCauHinhHeThongById";
sqlCmd.Parameters.Add("@Khoa", SqlDbType.VarChar).Value = Khoa;
dsResult = objDataAccess.ExecuteQuery(sqlCmd, "CauHinhHeThong");
return dsResult;
}
}
private void BtnSave_Click(object sender, EventArgs e)
{
string sql = "select * from Patient where Id ='" + this.txtId.Text + "';"; //Checking if the same id available in Dt
try
{
this.Dt = DataAccess.GetDataTable(sql);
}
catch
{
MessageBox.Show("Error: Something went wrong!");
}
//Update
if (this.Dt.Rows.Count == 1)
{
if (ValidPass(this.txtPassword.Text) && !string.IsNullOrWhiteSpace(this.txtPassword.Text) && !string.IsNullOrWhiteSpace(this.txtName.Text) && !string.IsNullOrWhiteSpace(this.txtAge.Text) && !string.IsNullOrWhiteSpace(this.txtEmail.Text) && !string.IsNullOrWhiteSpace(this.txtPhone.Text) && !string.IsNullOrWhiteSpace(this.txtBloodG.Text) && !string.IsNullOrWhiteSpace(this.txtAddress.Text))
{
ValidEmailCheck();
if (ValidEmail)
{
string sql2 = @"update Patient
set Password = '******',
Name = '" + this.txtName.Text + @"',
Age = " + this.txtAge.Text + @",
Email = '" + this.txtEmail.Text + @"',
Phone = '" + this.txtPhone.Text + @"',
BloodGroup = '" + this.txtBloodG.Text + @"',
Address = '" + this.txtAddress.Text + @"'
where Id = '" + this.txtId.Text + "';";
try
{
if (ValidEmail)
{
DataAccess.ExecuteQuery(sql2);
MessageBox.Show("Updated Succesfully!");
this.PopulateGridView();
}
}
catch
{
MessageBox.Show("Error: Something went wrong!");
}
}
else
{
MessageBox.Show("Account with same email already exists!");
this.txtEmail.Text = this.PrevEmail;
}
}
else if (!ValidPass(this.txtPassword.Text))
{
InvalidPassMsg();
}
else
{
MessageBox.Show("Enter all the fields!");
}
}
//Insert
else
{
//Auto generated UserId
string query = "select Id from Patient;"; //Checking if the table is empty
try
{
this.Dr = DataAccess.GetLastRow(query);
}
catch
{
MessageBox.Show("Error: Something went wrong!");
}
//Setting serial
if (this.Dr != null)
{
//Accessing last serial
string lastRowItem = this.Dr["Id"].ToString();
string trimValue = lastRowItem.Remove(0, 4);
this.serial = Int32.Parse(trimValue);
}
else
{
this.serial = 0;
}
//Increamenting UserId
this.UserId = (++this.serial).ToString("d2");
//Check if same email exists in DB
string sql2 = "select Email from Patient where Email = '" + this.txtEmail.Text + "';";
try
{
this.Dt = DataAccess.GetDataTable(sql2);
}
catch
{
MessageBox.Show("Error: Something went wrong!");
}
if (this.Dt.Rows.Count > 0)
{
MessageBox.Show("Account with same email already exists!");
}
else if (ValidPass(this.txtPassword.Text) && !string.IsNullOrWhiteSpace(this.txtPassword.Text) && !string.IsNullOrWhiteSpace(this.txtName.Text) && !string.IsNullOrWhiteSpace(this.txtAge.Text) && !string.IsNullOrWhiteSpace(this.txtEmail.Text) && !string.IsNullOrWhiteSpace(this.txtPhone.Text) && !string.IsNullOrWhiteSpace(this.txtBloodG.Text) && !string.IsNullOrWhiteSpace(this.txtAddress.Text))
{
sql = @"insert into Patient
values ('" + this.UserId + "', '" + this.txtPassword.Text + "', '" + this.txtName.Text + "', " + this.txtAge.Text + ", '" + this.txtEmail.Text + "','" + this.txtPhone.Text + "', '" + this.txtBloodG.Text + "', '" + this.txtAddress.Text + "');";
try
{
DataAccess.ExecuteQuery(sql);
MessageBox.Show("Added Successfully!");
this.PopulateGridView();
}
catch
{
MessageBox.Show("Error: Something went wrong!");
}
}
else if (!ValidPass(this.txtPassword.Text))
{
InvalidPassMsg();
}
else
{
MessageBox.Show("Enter all the fields!");
}
}
}
private void MTRbtnSave_Click(object sender, EventArgs e)
{
string userId = this.Id;
string name = null;
float price = 0;
int count = 0;
int validation = 1;
int x;
if (this.MTRtxtMedName.Text.Length == 0 || Int32.TryParse(this.MTRtxtMedName.Text, out x) == true)
{
MessageBox.Show("Invalid Name");
validation = 0;
}
else
{
name = this.MTRtxtMedName.Text;
}
float y;
if (this.MTRtxtMedPrice.Text.Equals("") || float.TryParse(this.MTRtxtMedPrice.Text, out y) == false)
{
MessageBox.Show("Invalid Price");
validation = 0;
}
else
{
price = float.Parse(this.MTRtxtMedPrice.Text);
}
if (this.MTRtxtMedCount.Text.Equals("") || Int32.TryParse(this.MTRtxtMedCount.Text, out x) == false)
{
MessageBox.Show("Invalid Count");
validation = 0;
}
else
{
count = Int32.Parse(this.MTRtxtMedCount.Text);
}
string morningAlarm, dayAlarm, nightAlarm;
if (MTRchkMorning.Checked)
{
morningAlarm = "Yes";
}
else
{
morningAlarm = "No";
}
if (MTRchkDay.Checked)
{
dayAlarm = "Yes";
}
else
{
dayAlarm = "No";
}
if (MTRchkNight.Checked)
{
nightAlarm = "Yes";
}
else
{
nightAlarm = "No";
}
if (validation == 1)
{
MessageBox.Show("Valid Inputs");
DataSet ds = new DataSet();
DataAccess da = new DataAccess();
string sql;
try
{
sql = "select * from [HealthyHome].[dbo].[Medicines] where userId='" + this.Id + "' and medName='" + name + "'";
ds = da.ExecuteQuery(sql);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
if (ds.Tables[0].Rows.Count < 1)
{
try
{
sql = "insert into [HealthyHome].[dbo].[Medicines] values('" + userId + "','" + name + "'," + price + "," + count + ",'" + morningAlarm + "','" + dayAlarm + "','" + nightAlarm + "') ;";
da.ExecuteQuery(sql);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
this.Hide();
UserHomeDashboard UHD = new UserHomeDashboard(this.Id);
UHD.Visible = true;
}
else
{
try
{
sql = "update [HealthyHome].[dbo].[Medicines] set price=" + price + ", count =" + count + ",morningAlarm='" + morningAlarm + "',dayAlarm='" + dayAlarm + "',nightAlarm='" + nightAlarm + "' where userId ='" + userId + "' and medName = '" + name + "' ;";
da.ExecuteQuery(sql);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
this.Hide();
UserHomeDashboard UHD = new UserHomeDashboard(this.Id);
UHD.Visible = true;
}
}
else
{
MessageBox.Show("Invalid Inputs");
}
}
public DataTable GetByName(string name, string type)
{
var query = "select * from " + type + " where Name = '" + name + "'";
return(DataAccess.ExecuteQuery(query));
}
protected int nCompanyId = -1; //140905 SURAJIT
#endregion
// ==========================================================================================================
#region Page_Load
private void Page_Load(object sender, System.EventArgs e)
{
if (Session["UserID"] == null)
{
Response.Redirect("../close_win.aspx");
}
RecordSet rs;
if (Convert.ToInt32(Session["exported"]) == 1)
{
Session["exported"] = 0;
Response.Redirect("export.aspx?exp=");
}
DirectoryInfo di = new DirectoryInfo("\\export");
if (!di.Exists)
{
di.Create();
}
if (Convert.ToInt32(Session["compid"]) != nCompanyId || (Convert.ToInt32(Session["compid"]) == nCompanyId && Convert.ToInt32(Session["exported"]) != 1))
{
exp = (string)Request.QueryString["exp"];
if (nCompanyId == -1 && (Convert.ToInt32(Session["compid"]) != nCompanyId && Convert.ToInt32(Session["exported"]) != 1))
{
nCompanyId = Convert.ToInt32(Session["compid"]);
}
}
else
{
exp = "";
nCompanyId = -1;
}
Session["exported"] = 0;
if ((!Page.IsPostBack && (exp == "" || exp == null || exp == "9")) || nCompanyId == -1) //ch 150905 SURAJIT
{
DataTable dt = GetBuyerCompanyList(Convert.ToInt32(Session["CompanyID"])); //ch 200905 SURAJIT //para added
cboCompany.DataSource = dt;
cboCompany.DataBind();
}
try
{
if (exp == "1")
{
DataAccess da = new DataAccess(CBSAppUtils.PrimaryConnectionString);
if (nCompanyId == -1 && Convert.ToInt32(Session["exported"]) == 1)
{
return;
}
if (nCompanyId == -1)
{
outError.Text = "Please select a company";
return;
}
else
{
outError.Text = "";
}
if (Convert.ToInt32(Session["CompanyTypeID"]) == 0 || nCompanyId == 14 || nCompanyId == 0) //HUB ADMIN or GMG //140905 SURAJIT
{
if (Convert.ToInt32(Session["CompanyTypeID"]) == 0)
{
if (Convert.ToInt32(Session["CompanyID"]) != 13074)
{
rs = da.ExecuteQuery("Company", "Active=1", "CompanyID");
}
else
{
rs = da.ExecuteQuery("Company", "Active=1 and (CompanyID =" + nCompanyId + " OR ParentCompanyID=" + nCompanyId + ")", "CompanyID");
}
}
else
{
rs = da.ExecuteQuery("Company", "Active=1 and (CompanyID =" + nCompanyId + " OR ParentCompanyID=" + nCompanyId + ")", "CompanyID");
}
if (rs.RecordCount > 0)
{
rs.MoveFirst();
while (!rs.EOF())
{
if (ExportDocForReg(Convert.ToString(rs["CompanyCode"]) + "_Reg.CSV", Convert.ToInt32(rs["CompanyID"]))) //141005 SURAJIT
{
rs.MoveNext();
}
}
}
exp = "9";
Session["exported"] = 1;
nCompanyId = -1;
}
else
{
rs = da.ExecuteQuery("Company", "Active=1 and (CompanyID =" + nCompanyId + ")", "CompanyID");
if (rs.RecordCount > 0)
{
if (ExportDocForReg(Convert.ToString(rs["CompanyCode"]) + "_Reg.CSV", nCompanyId))
{
}
}
}
exp = "";
Session["exported"] = 1;
nCompanyId = -1;
}
if (exp == "2")//auth
{
DataAccess da = new DataAccess(CBSAppUtils.PrimaryConnectionString);
if (nCompanyId == -1)
{
outError.Text = "Please select a company";
return;
}
else
{
outError.Text = "";
}
if (nCompanyId == -1 && Convert.ToInt32(Session["exported"]) == 1)
{
return;
}
if (Convert.ToInt32(Session["CompanyTypeID"]) == 0 || nCompanyId == 14 || nCompanyId == 0) //HUB ADMIN or GMG //140905 SURAJIT
{
if (Convert.ToInt32(Session["CompanyTypeID"]) == 0)
{
if (Convert.ToInt32(Session["CompanyID"]) != 13074)
{
rs = da.ExecuteQuery("Company", "Active=1", "CompanyID");
}
else
{
rs = da.ExecuteQuery("Company", "Active=1 and (CompanyID =" + nCompanyId + " OR ParentCompanyID=" + nCompanyId + ")", "CompanyID");
}
}
else
{
rs = da.ExecuteQuery("Company", "Active=1 and (CompanyID =" + nCompanyId + " OR ParentCompanyID=" + nCompanyId + ")", "CompanyID");
}
if (rs.RecordCount > 0)
{
rs.MoveFirst();
while (!rs.EOF())
{
if (ExportDocForAuth(Convert.ToString(rs["CompanyCode"]) + "_Auth.CSV", Convert.ToInt32(rs["CompanyID"]))) //141005 SURAJIT
{
rs.MoveNext();
}
}
}
exp = "9";
Session["exported"] = 1;
nCompanyId = -1;
}
else
{
rs = da.ExecuteQuery("Company", "Active=1 and (CompanyID =" + nCompanyId + ")", "CompanyID");
if (rs.RecordCount > 0)
{
if (ExportDocForAuth(Convert.ToString(rs["CompanyCode"]) + "_Auth.CSV", nCompanyId))
{
}
}
}
exp = "";
Session["exported"] = 1;
nCompanyId = -1;
}
else
{
if (Convert.ToInt32(Session["exported"]) == 1)
{
nCompanyId = -1;
exp = "";
}
else
{
if (cboCompany.SelectedValue != null)
{
if (cboCompany.SelectedValue != "")
{
nCompanyId = Convert.ToInt32(cboCompany.SelectedValue);
Session["compid"] = nCompanyId;
}
}
}
}
}
catch (Exception ex) { throw (ex); }
}
public static int CreateLogin(string username, string password, string designation, int empId)
{
string sql = @"insert into login values('" + username + "', '" + password + "', '" + designation + "', '" + empId + "') ";
return(DataAccess.ExecuteQuery(sql));
}
public int DeleteOrder(String Id)
{
String Query = "Delete From Orders Where ORDERID = '" + Id + "'";
return(DataAccess.ExecuteQuery(Query));
}
public int Delete(String Id)
{
String Query = "Delete From Expense Where ID = '" + Id + "'";
return(DataAccess.ExecuteQuery(Query));
}
public static int salvaTandas()
{
AccesoDatosInventario.DataAccess acceso = new DataAccess();
DataSet data = acceso.ExecuteQuery("SP_SALVA_TANDAS",new List<SqlParameter>());
return int.Parse(data.Tables[0].Rows[0][0].ToString());
/*SqlConnection DataConnection = new SqlConnection(AccesoDatos._Connection);
int valorRetorno;
SqlDataReader lectorSQL;
try
{
SqlCommand execproc = new SqlCommand(, DataConnection);
execproc.CommandType = CommandType.StoredProcedure;
execproc.Connection.Open();
SqlDataReader LECTOR = execproc.ExecuteReader();
int idOut = LECTOR.GetInt32(0);
//valorRetorno = (int)execproc.Parameters["@IdDocumento"].Value;
return idOut;
}
catch (Exception sqle) { return -1; }*/
}
public int AddMenuItem(Entity.Item item)
{
String Query = "INSERT into MENU (MENUID,MENUNAME,CATEGORYID) values ('" + item.MenuId + "','" + item.MenuName + "','" + item.CategoryId + "')";
return(DataAccess.ExecuteQuery(Query));
}
public int DeleteDept(String Id)
{
String Query = "Delete From Department Where DEPTID = '" + Id + "'";
return(DataAccess.ExecuteQuery(Query));
}
public int Edit(Entity.BillingOperation order, String Id)
{
String Query = "Update Orders Set Quantity='" + order.Quantity + "',Price='" + order.OrderPrice + "' Where ORDERID='" + Id + "'";
return(DataAccess.ExecuteQuery(Query));
}
public int Edit(Entity.Expense expense, String Id)
{
String Query = "Update Expense Set CATEGORY='" + expense.ExpenseCategory + "',DESCRIPTION='" + expense.ExpenseDescription + "',AMOUNT=" + expense.ExpenseAmount + ",DATE='" + expense.ExpenseDate + "' Where ID='" + Id + "'";
return(DataAccess.ExecuteQuery(Query));
}
public Dictionary <string, List <ColumnObjectModel> > GetInTableColumns(string[] tableNamesArray)
{
EventParser.ParseMessage(this, this.dispatcher, "Reading Tables", "");
Dictionary <string, List <ColumnObjectModel> > result = new Dictionary <string, List <ColumnObjectModel> >();
DataAccess dataAccess = new DataAccess(Connections.Instance.DatabaseModel);
XDocument columnsXml = dataAccess.ExecuteQuery(SQLQueries.DatabaseQueries.DatabaseInTableColumnsQuery(tableNamesArray));
Dictionary <string, List <XElement> > groupedTables = new Dictionary <string, List <XElement> >();
foreach (XElement column in columnsXml.Root.Elements())
{
string tableName = column.Element("TABLENAME").Value;
if (!groupedTables.ContainsKey(tableName))
{
groupedTables.Add(tableName, new List <XElement>());
}
groupedTables[tableName].Add(column);
}
foreach (KeyValuePair <string, List <XElement> > tableColumns in groupedTables)
{
result.Add(tableColumns.Key, new List <ColumnObjectModel>());
EventParser.ParseMessage(this, this.dispatcher, "Reading Columns for ", tableColumns.Key);
string[] columnNamesArray = tableColumns.Value.Select(c => c.Element("COLUMNNAME").Value).ToArray();
XDocument primaryKeys = dataAccess.ExecuteQuery(SQLQueries.DatabaseQueries.DatabaseInColumnKeysQuery(tableColumns.Key, columnNamesArray));
foreach (XElement item in tableColumns.Value)
{
string columnName = item.Element("COLUMNNAME").Value;
if (result[tableColumns.Key].Any(col => col.ColumnName == columnName))
{
continue;
}
string originalPosistion = item.Element("ORDINAL_POSITION").Value;
XElement primaryKey = primaryKeys.Root.Elements()
.FirstOrDefault(el => el.Element("COLUMN_NAME").Value == columnName &&
el.Element("CONSTRAINT_TYPE").Value == "PRIMARY KEY");
ColumnObjectModel column = new ColumnObjectModel
{
ColumnName = columnName,
IsIdentity = item.Element("IS_IDENTITY").Value.ToBool(),
AllowNulls = item.Element("IS_NULLABLE").Value.ToBool(),
MaxLength = item.Element("MAX_LENGTH").Value.ToInt32(),
Precision = item.Element("PRECISION").Value.ToInt32(),
Scale = item.Element("SCALE").Value.ToInt32(),
IsForeignkey = !item.Element("PRIMARY_TABLE").Value.IsNullEmptyOrWhiteSpace(),
ForeignKeyTable = item.Element("PRIMARY_TABLE").Value,
ForeignKeyColumn = item.Element("PRIMARY_COLUMNNAME").Value,
ForeignConstraintName = item.Element("FK_CONSTRAINT_NAME").Value,
SqlDataType = this.ParseSqlDbType(item.Element("DATA_TYPE").Value),
InPrimaryKey = primaryKey == null ? false : primaryKey.Descendants().Any(d => d.Value == "PRIMARY KEY"),
Column_Id = item.Element("COLUMN_ID").Value.ToInt32(),
OriginalPosition = originalPosistion.IsNullEmptyOrWhiteSpace() ? 0 : originalPosistion.ToInt32()
};
column.HasModelChanged = false;
result[tableColumns.Key].Add(column);
}
}
return(result);
}
public static int CreateCancelledAppointment(string starttime, string date, int personid, int doctorid)
{
string sql = @"insert into cancelledappointments (starttime,date,personid,doctorid) values('" + starttime + "', '" + date + "', '" + personid + "', '" + doctorid + "')";
return(DataAccess.ExecuteQuery(sql));
}
public DataSet SelectByField(string FieldName, string value, string type)
{
DataAccess objDataAccess = new DataAccess();
DataSet dsResult = new DataSet();
dsResult.Locale = CultureInfo.CurrentCulture;
using (SqlCommand sqlCmd = new SqlCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "SelectCauHinhHeThongByField";
sqlCmd.Parameters.Add("@FieldName", SqlDbType.NVarChar).Value = FieldName;
sqlCmd.Parameters.Add("@Value", SqlDbType.NVarChar).Value = value;
sqlCmd.Parameters.Add("@type", SqlDbType.NVarChar).Value = type;
dsResult = objDataAccess.ExecuteQuery(sqlCmd, "CauHinhHeThong");
return dsResult;
}
}
public static int RemoveCancelledAppointment(int id)
{
string sql = "DELETE FROM cancelledappointments WHERE appointmentid = '" + id + "';";
return(DataAccess.ExecuteQuery(sql));
}
public static int RemoveParticularDoctorsAppointments(int doctorID)
{
string sql = "DELETE FROM cancelledappointments WHERE doctorid = '" + doctorID + "';";
return(DataAccess.ExecuteQuery(sql));
}
public int TruncateOrder()
{
String Query = "Truncate Table Orders";
return(DataAccess.ExecuteQuery(Query));
}
private static int salvaTandas2()
{
AccesoDatosInventario.DataAccess acceso = new DataAccess();
DataSet data = acceso.ExecuteQuery("SP_SALVADA", new List<SqlParameter>());
return int.Parse(data.Tables[0].Rows[0][0].ToString());
}
public void ClearCart()
{
string query = "delete from Cart";
DataAccess.ExecuteQuery(query);
}