public DataTable GetDetailsOfAllUsers()
{
SqlConnection con = null;
DataTable dtUsers = null;
try
{
dtUsers = new DataTable();
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand("ViewAllUsers", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dtUsers);
return dtUsers;
}
catch (Exception ex)
{
var page = HttpContext.Current.CurrentHandler as Page;
eObj.ErrorData(ex, page);
throw ex;
}
finally
{
if (con != null)
{
con.Dispose();
}
}
}
public void AddNewCategory()
{
dbConnection dcon = null;
try
{
dcon = new dbConnection();
dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = dcon.SQLCon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[InsertCategories]";
cmd.Parameters.Add("@CategoryID", SqlDbType.UniqueIdentifier).Value = CategoryID;
cmd.Parameters.Add("@ClinicID", SqlDbType.UniqueIdentifier).Value = ClinicID;
cmd.Parameters.Add("@CreatedBy", SqlDbType.NVarChar, 255).Value = CreatedBy;
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 255).Value = CategoryName;
cmd.Parameters.Add("@Status", SqlDbType.Int);
cmd.Parameters["@Status"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
int Outputval = (int)cmd.Parameters["@Status"].Value;
cmd.ExecuteNonQuery();
if (Outputval == 1)
{
//Success
}
else
{
if (Outputval == 0)
{
//Already exists!
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dcon.SQLCon != null)
{
dcon.DisconectDB();
}
}
}
public void InsertPrescriptionHeaderDetails()
{
dbConnection dcon = null;
try
{
Guid PrescID = new Guid();
DateTime now = DateTime.Now;
dcon = new dbConnection();
dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = dcon.SQLCon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[InsertPrescriptionHeaderDetails]";
cmd.Parameters.Add("@PrescID", SqlDbType.UniqueIdentifier).Value = PrescID;
cmd.Parameters.Add("@DoctorID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(DoctorID);
cmd.Parameters.Add("@VisitID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(VisitID);
cmd.Parameters.Add("@ClinicID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(ClinicID);
cmd.Parameters.Add("@Date", SqlDbType.NVarChar, 50).Value = now.ToString("yyyy-MM-dd");
cmd.Parameters.Add("@CreatedBy", SqlDbType.NVarChar, 255).Value = CreatedBy;
cmd.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = CreatedDate;
cmd.Parameters.Add("@CreatedBy", SqlDbType.NVarChar, 255).Value =UpdatedBy;
cmd.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = UpdatedDate;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dcon.SQLCon != null)
{
dcon.DisconectDB();
}
}
}
public DataSet PhramacyPatientDetails(Guid PatientID)
{
SqlConnection con = null;
DataSet ds = null;
SqlDataAdapter sda = null;
try
{
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[PharmacyPatientDetails]";
cmd.Parameters.Add("@PatientID", SqlDbType.UniqueIdentifier).Value = PatientID;
sda = new SqlDataAdapter();
cmd.ExecuteNonQuery();
sda.SelectCommand = cmd;
ds = new DataSet();
sda.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
{
con.Dispose();
}
}
}
public DataTable GetFileIDUSingPatientID()
{
SqlConnection con = null;
try
{
DateTime now = DateTime.Now;
DataTable DtFileID = new DataTable();
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand("GetFileID", con);
cmd.Parameters.Add("@PatientID", SqlDbType.UniqueIdentifier).Value = PatientIdForFile;
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
DtFileID = new DataTable();
adapter.Fill(DtFileID);
return DtFileID;
}
catch (Exception ex)
{
//var page = HttpContext.Current.CurrentHandler as Page;
//eObj.ErrorData(ex, page);
throw ex;
}
finally
{
if (con != null)
{
con.Dispose();
}
}
}
public string GetRoleIDOfDoctor()
{
string DoctorRoleID = string.Empty;
dbConnection dcon = null;
try
{
dcon = new dbConnection();
dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = dcon.SQLCon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[GetRoleIDOFDoctor]";
cmd.Parameters.Add("@ClinicID", SqlDbType.UniqueIdentifier).Value = ClinicID;
object ID = cmd.ExecuteScalar();
if (ID != null)
{
DoctorRoleID = ID.ToString();
}
}
catch (Exception ex)
{
var page = HttpContext.Current.CurrentHandler as Page;
//eObj.ErrorData(ex, page);
}
finally
{
if (dcon.SQLCon != null)
{
dcon.DisconectDB();
}
}
return DoctorRoleID;
}
public int CheckUserIsDoctor()
{
int count = 0;
dbConnection dcon = new dbConnection();
try
{
dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = dcon.SQLCon;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[CheckUserIsDoctor]";
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 255).Value = firstName;
cmd.Parameters.Add("@ClinicID", SqlDbType.UniqueIdentifier).Value = ClinicID;
object cnt = cmd.ExecuteScalar();
if (cnt != null)
{
count = Convert.ToInt32(cnt);
}
}
catch (Exception ex)
{
var page = HttpContext.Current.CurrentHandler as Page;
eObj.ErrorData(ex, page);
}
finally
{
if (dcon.SQLCon != null)
{
dcon.DisconectDB();
}
}
return count;
}
/// <summary>
/// VIEW MEDCINES IF SEARCH ITEM IS NULL ....OTHERWISE SEARCH
///
/// </summary>
/// <param name="String"></param>
/// <returns></returns>
public DataSet SearchMedicineStock(string String )//In case of view : Empty string has to be passed
{
dbConnection dcon = null;
DataSet ds = null;
SqlDataAdapter sda = null;
try
{
dcon = new dbConnection();
dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = dcon.SQLCon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[SearchMedicineStock]";
if (String == String.Empty)
{
cmd.Parameters.Add("@String", SqlDbType.NVarChar, 255).Value = DBNull.Value;
}
else
{
cmd.Parameters.Add("@String", SqlDbType.NVarChar, 255).Value = String;
}
cmd.Parameters.Add("@ClinicID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(ClinicID);
sda = new SqlDataAdapter();
cmd.ExecuteNonQuery();
sda.SelectCommand = cmd;
ds = new DataSet();
sda.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dcon.SQLCon != null)
{
dcon.DisconectDB();
}
}
}
public void UpdateuserByUserID()
{
dbConnection dcon = new dbConnection();
try
{
dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = dcon.SQLCon;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[UpdateUserByUserID]";
cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier).Value = UserID;
cmd.Parameters.Add("@LoginName", SqlDbType.NVarChar, 255).Value = loginName;
cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 255).Value = firstName;
cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 255).Value = lastName;
cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 40).Value = passWord;
cmd.Parameters.Add("@Active", SqlDbType.Bit).Value = isActive;
cmd.Parameters.Add("@ClinicID", SqlDbType.UniqueIdentifier).Value = ClinicID;
cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 255).Value = Email;
cmd.Parameters.Add("@PhoneNo", SqlDbType.NVarChar, 30).Value = PhoneNo;
cmd.Parameters.Add("@UpdatedBY", SqlDbType.NVarChar, 255).Value = updatedBy;
SqlParameter Output = new SqlParameter();
Output.DbType = DbType.Int32;
Output.ParameterName = "@Status";
Output.Direction = ParameterDirection.Output;
cmd.Parameters.Add(Output);
cmd.ExecuteNonQuery();
if (Output.Value.ToString() == "")
{
//not successfull
var page = HttpContext.Current.CurrentHandler as Page;
eObj.UpdationNotSuccessMessage(page);
}
else
{
//successfull
var page = HttpContext.Current.CurrentHandler as Page;
eObj.UpdationSuccessMessage(page);
}
}
catch (Exception ex)
{
var page = HttpContext.Current.CurrentHandler as Page;
eObj.ErrorData(ex, page);
}
finally
{
if (dcon.SQLCon != null)
{
dcon.DisconectDB();
}
}
}
public void UpdateIssueHeader(string IssueID)
{
dbConnection dcon = null;
try
{
dcon = new dbConnection();
dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = dcon.SQLCon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[UpdateIssueHeader]";
cmd.Parameters.Add("@IssueID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(IssueID);
cmd.Parameters.Add("@ClinicID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(ClinicID);
cmd.Parameters.Add("@IssuedTo", SqlDbType.NVarChar, 255).Value = IssuedTo;
cmd.Parameters.Add("@Date", SqlDbType.NVarChar, 50).Value = Date;
cmd.Parameters.Add("@UpdatedBy", SqlDbType.NVarChar, 255).Value = UpdatedBy;
//cmd.Parameters.Add("@IssueNO", SqlDbType.NVarChar, 50).Value = IssueNO;
cmd.Parameters.Add("@Status", SqlDbType.Int);
cmd.Parameters["@Status"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
int Outputval = (int)cmd.Parameters["@Status"].Value;
if (Outputval == 1)
{
//Success
var page = HttpContext.Current.CurrentHandler as Page;
//eObj.UpdationSuccessMessage(page);
}
else
{
if (Outputval == 0)
{
//---------* Already exists! *----------//
var page = HttpContext.Current.CurrentHandler as Page;
//eObj.AlreadyExistsMessage(page);
}
}
}
catch (Exception ex)
{
var page = HttpContext.Current.CurrentHandler as Page;
//eObj.ErrorData(ex, page);
}
finally
{
if (dcon.SQLCon != null)
{
dcon.DisconectDB();
}
}
}
public void InsertIssueDetails()
{
dbConnection dcon = null;
//string medID = GetMedcineIDByMedicineName(MedicineName);
try
{
dcon = new dbConnection();
dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = dcon.SQLCon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[InsertIssueDetails]";
cmd.Parameters.Add("@UniqueID", SqlDbType.UniqueIdentifier).Value = UniqueID;
cmd.Parameters.Add("@IssueID", SqlDbType.UniqueIdentifier).Value = IssueID;
cmd.Parameters.Add("@ClinicID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(ClinicID);
cmd.Parameters.Add("@MedicineName", SqlDbType.NVarChar, 255).Value = MedicineName;
//cmd.Parameters.Add("@MedicineID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(medID);
cmd.Parameters.Add("@Qty", SqlDbType.Real).Value = Qty;
cmd.Parameters.Add("@CreatedBy", SqlDbType.NVarChar, 255).Value = CreatedBy;
cmd.Parameters.Add("@Status", SqlDbType.Int);
cmd.Parameters["@Status"].Direction = ParameterDirection.Output;
//cmd.Parameters.Add("@QtyInStock", SqlDbType.Real);
//cmd.Parameters["@QtyInStock"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
int Outputval = (int)cmd.Parameters["@Status"].Value;
//int QtyInstock = Convert.ToInt32(cmd.Parameters["@QtyInStock"].Value);
if (Outputval == 1)
{
//Success
var page = HttpContext.Current.CurrentHandler as Page;
//eObj.InsertionSuccessMessage(page);
}
else
{
if (Outputval == 2)
{
//Out of stock
var page = HttpContext.Current.CurrentHandler as Page;
//eObj.OutOfStockMessage(page);
}
}
}
catch (Exception ex)
{
var page = HttpContext.Current.CurrentHandler as Page;
//eObj.ErrorData(ex, page);
}
finally
{
if (dcon.SQLCon != null)
{
dcon.DisconectDB();
}
}
}
public void InsertMedicines()
{
dbConnection dcon = null;
try
{
dcon = new dbConnection();
dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = dcon.SQLCon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[InsertMedicines]";
cmd.Parameters.Add("@MedicineID", SqlDbType.UniqueIdentifier).Value = MedicineID;
cmd.Parameters.Add("@ClinicID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(ClinicID);
cmd.Parameters.Add("@CategoryID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(CategoryID);
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 255).Value = Name;
cmd.Parameters.Add("@Unit", SqlDbType.NVarChar, 15).Value = Unit;
// cmd.Parameters.Add("@Qty", SqlDbType.Real).Value = Qty; INITIAL QUANTITY WILL BE ZERO
cmd.Parameters.Add("@CreatedBy", SqlDbType.NVarChar, 255).Value = CreatedBy;
cmd.Parameters.Add("@ReOrderQty", SqlDbType.Real).Value = Convert.ToInt32( ReOrderQty);
cmd.Parameters.Add("@MedCode", SqlDbType.NVarChar, 20).Value = MedCode;
cmd.Parameters.Add("@Status", SqlDbType.Int);
cmd.Parameters["@Status"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
int Outputval = (int)cmd.Parameters["@Status"].Value;
cmd.ExecuteNonQuery();
if (Outputval == 1)
{
//Success
var page = HttpContext.Current.CurrentHandler as Page;
//eObj.InsertionSuccessMessage(page);
}
else
{
if(Outputval == 0)
{
//Already exists!
}
}
}
catch (Exception ex)
{
var page = HttpContext.Current.CurrentHandler as Page;
eObj.ErrorData(ex, page);
//throw ex;
}
finally
{
if (dcon.SQLCon != null)
{
dcon.DisconectDB();
}
}
}
public void ResetPassword(Guid UserID)
{
dbConnection dcon = new dbConnection();
try
{
dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = dcon.SQLCon;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "ResetPassword";
cmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = UserID;
cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 40).Value = passWord;
SqlParameter Output = new SqlParameter();
Output.DbType = DbType.Int32;
Output.ParameterName = "@Status";
Output.Direction = ParameterDirection.Output;
cmd.Parameters.Add(Output);
cmd.ExecuteNonQuery();
if (Output.Value.ToString() == "")
{
//not successfull
//var page = HttpContext.Current.CurrentHandler as Page;
//eObj.UpdationNotSuccessMessage(page);
}
else
{
//successfull
//var page = HttpContext.Current.CurrentHandler as Page;
// eObj.UpdationSuccessMessage(page);
}
}
catch (Exception ex)
{
//var page = HttpContext.Current.CurrentHandler as Page;
//eObj.ErrorData(ex, page);
}
finally
{
if (dcon.SQLCon != null)
{
dcon.DisconectDB();
}
}
}
public DataTable SearchBoxMedicine()
{
DataTable dt = null;
SqlConnection con = null;
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand("SearchBoxMedicine", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
dt = new DataTable();
adapter.Fill(dt);
con.Close();
return dt;
}
public DataTable GetMedcineDetails(string SearchName)
{
DataTable dt = null;
SqlConnection con = null;
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand("SearchMedicineWithName", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 255).Value = SearchName;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
dt = new DataTable();
adapter.Fill(dt);
con.Close();
return dt;
}
public string GetRoleName(string LoginName)
{
string Role="";
SqlConnection con = null;
try
{
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand pud = new SqlCommand();
pud.Connection = con;
pud.CommandType = System.Data.CommandType.StoredProcedure;
pud.CommandText = "GetUserandRolesJoined";
pud.Parameters.Add("@LoginName", SqlDbType.NVarChar, 50).Value = LoginName;
SqlParameter OutparamId = pud.Parameters.Add("@OutRole", SqlDbType.NVarChar,255);
OutparamId.Direction = ParameterDirection.Output;
pud.ExecuteNonQuery();
if (OutparamId.Value != null)
{
Role=OutparamId.Value.ToString();
}
}
catch (Exception ex)
{
//var page = HttpContext.Current.CurrentHandler as Page;
//eObj.ErrorData(ex, page);
//throw ex;
}
finally
{
if (con != null)
{
con.Dispose();
}
}
return Role;
}
public DataTable GetLoginDetails(string LoginName)
{
SqlConnection con = null;
try
{
DataTable dt = new DataTable();
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand("GetLoginDetails", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@LoginName", SqlDbType.NVarChar, 50).Value = LoginName;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
dt = new DataTable();
adapter.Fill(dt);
con.Close();
return dt;
}
catch (Exception ex)
{
//var page = HttpContext.Current.CurrentHandler as Page;
//eObj.ErrorData(ex, page);
throw ex;
}
finally
{
if (con != null)
{
con.Dispose();
}
}
}
public void UpdateIssueDetails(string UniqueID)
{
dbConnection dcon = null;
try
{
DateTime now = DateTime.Now;
dcon = new dbConnection();
dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = dcon.SQLCon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[UpdateIssueDetails]";
cmd.Parameters.Add("@UniqueID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(UniqueID);
cmd.Parameters.Add("@ClinicID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(ClinicID);
//cmd.Parameters.Add("@MedicineID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(MedicineID);
cmd.Parameters.Add("@Qty", SqlDbType.Real).Value = Qty;
cmd.Parameters.Add("@UpdatedBy", SqlDbType.NVarChar, 255).Value = UpdatedBy;
cmd.Parameters.Add("@Status", SqlDbType.Int);
cmd.Parameters["@Status"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
int Outputval = (int)cmd.Parameters["@Status"].Value;
if (Outputval == 1)
{
//Success
var page = HttpContext.Current.CurrentHandler as Page;
//eObj.UpdationSuccessMessage(page);
}
else
{
if (Outputval == 2)
{
//Out of stock
var page = HttpContext.Current.CurrentHandler as Page;
//eObj.OutOfStockMessage(page);
}
}
}
catch (Exception ex)
{
var page = HttpContext.Current.CurrentHandler as Page;
//eObj.ErrorData(ex, page);
}
finally
{
if (dcon.SQLCon != null)
{
dcon.DisconectDB();
}
}
}
//------------*Methods Used For Forgot Password Implementation *------------//
#region Add verificationcode (Generated random number)
public void AddVerificationCode()
{
dbConnection dcon = new dbConnection();
try
{
dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = dcon.SQLCon;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[AddVerificationCode]";
cmd.Parameters.Add("@VerificationCode", SqlDbType.NVarChar, 20).Value = verificationCode;
cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 255).Value = Email;
SqlParameter Output = new SqlParameter();
Output.DbType = DbType.Int32;
Output.ParameterName = "@Status";
Output.Direction = ParameterDirection.Output;
cmd.Parameters.Add(Output);
cmd.ExecuteNonQuery();
//if (Output.Value.ToString() == "")
//{
// //not successfull
// var page = HttpContext.Current.CurrentHandler as Page;
// eObj.InsertionNotSuccessMessage(page);
//}
//else
//{
// //successfull
// var page = HttpContext.Current.CurrentHandler as Page;
// eObj.InsertionSuccessMessage(page);
//}
}
catch (Exception ex)
{
//var page = HttpContext.Current.CurrentHandler as Page;
//eObj.ErrorData(ex, page);
}
finally
{
if (dcon.SQLCon != null)
{
dcon.DisconectDB();
}
}
}
public void UpdateMedicines(string MedicineID )
{
dbConnection dcon = null;
try
{
dcon = new dbConnection();
dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = dcon.SQLCon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[UpdateMedicines]";
cmd.Parameters.Add("@MedicineID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(MedicineID);
cmd.Parameters.Add("@ClinicID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(ClinicID);
cmd.Parameters.Add("@CateoryID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(CategoryID);
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 255).Value = Name;
cmd.Parameters.Add("@Unit", SqlDbType.NVarChar, 15).Value = Unit;
cmd.Parameters.Add("@Qty", SqlDbType.Real).Value = Qty;
cmd.Parameters.Add("@UpdatedBy", SqlDbType.NVarChar, 255).Value = UpdatedBy;
cmd.Parameters.Add("@ReOrderQty", SqlDbType.Real).Value = ReOrderQty;
cmd.Parameters.Add("@MedCode", SqlDbType.NVarChar, 20).Value = MedCode;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dcon.SQLCon != null)
{
dcon.DisconectDB();
}
}
}
public DataTable GetUserVerificationCodeByEmailID()
{
SqlConnection con = null;
DataTable dtVerificationCode = null;
try
{
dtVerificationCode = new DataTable();
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand("GetVerificationCodeByEmailID", con);
cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 255).Value = Email;
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dtVerificationCode);
return dtVerificationCode;
}
catch (Exception ex)
{
var page = HttpContext.Current.CurrentHandler as Page;
eObj.ErrorData(ex, page);
throw ex;
}
finally
{
if (con != null)
{
con.Dispose();
}
}
}
public void DeleteAssignedRoleByUniqueID()
{
SqlConnection con = null;
try
{
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "DeleteUserInRolesByUniqueID";
cmd.Parameters.Add("@UniqueID", SqlDbType.UniqueIdentifier).Value = UniqueID;
SqlParameter Output = new SqlParameter();
Output.DbType = DbType.Int32;
Output.ParameterName = "@Status";
Output.Direction = ParameterDirection.Output;
cmd.Parameters.Add(Output);
cmd.ExecuteNonQuery();
if (Output.Value.ToString() == "")
{
//not successfull
var page = HttpContext.Current.CurrentHandler as Page;
eObj.WarningMessage(page);
}
else
{
//successfull
var page = HttpContext.Current.CurrentHandler as Page;
eObj.DeleteSuccessMessage(page);
}
}
catch (Exception ex)
{
var page = HttpContext.Current.CurrentHandler as Page;
eObj.ErrorData(ex, page);
}
finally
{
if (con != null)
{
con.Dispose();
}
}
}
public bool ValidateCategoryName(string CheckCategory)
{
bool flag;
SqlConnection con = null;
try
{
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand("CheckCategoryNameDuplication", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@CategoryName", SqlDbType.VarChar, 255).Value = CheckCategory;
SqlParameter outflag = cmd.Parameters.Add("@flag", SqlDbType.Bit);
outflag.Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
flag = (bool)outflag.Value;
if (flag == true)
{
return flag;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
{
con.Close();
}
}
return false;
}
public DataSet GetIssueDetailsByIssueNO(String IssueNo)
{
dbConnection dcon = null;
DataSet ds = null;
SqlDataAdapter sda = null;
try
{
dcon = new dbConnection();
dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = dcon.SQLCon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[GetIssueDetailsByIssueNo]";
cmd.Parameters.Add("@IssueNo", SqlDbType.NVarChar, 255).Value = IssueNo;
cmd.Parameters.Add("@ClinicID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(ClinicID);
sda = new SqlDataAdapter();
cmd.ExecuteNonQuery();
sda.SelectCommand = cmd;
ds = new DataSet();
sda.Fill(ds,"Medicines");
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dcon.SQLCon != null)
{
dcon.DisconectDB();
}
}
}
public void AssignRole()
{
dbConnection dcon = new dbConnection();
try
{
dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = dcon.SQLCon;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "[InsertUserInRoles]";
cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier).Value = UserID;
cmd.Parameters.Add("@RoleID", SqlDbType.UniqueIdentifier).Value = RoleID;
cmd.Parameters.Add("@ClinicID", SqlDbType.UniqueIdentifier).Value = ClinicID;
cmd.Parameters.Add("@Createdby", SqlDbType.NVarChar, 255).Value = CreatedBy;
SqlParameter Output = new SqlParameter();
Output.DbType = DbType.Int32;
Output.ParameterName = "@Status";
Output.Direction = ParameterDirection.Output;
cmd.Parameters.Add(Output);
cmd.ExecuteNonQuery();
if (Output.Value.ToString() == "")
{
//not successfull
var page = HttpContext.Current.CurrentHandler as Page;
eObj.InsertionNotSuccessMessage(page);
}
else
{
//successfull
var page = HttpContext.Current.CurrentHandler as Page;
eObj.InsertionSuccessMessage(page);
}
}
catch (Exception ex)
{
var page = HttpContext.Current.CurrentHandler as Page;
eObj.ErrorData(ex, page);
}
finally
{
if (dcon.SQLCon != null)
{
dcon.DisconectDB();
}
}
}
public string GetMedcineIDByMedicineName(string MedicineName)
{
string MedicineID = string.Empty;
dbConnection dcon = null;
try
{
dcon = new dbConnection();
dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = dcon.SQLCon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[GetMedcineIDByMedicineName]";
cmd.Parameters.Add("@MedicineName", SqlDbType.NVarChar, 255).Value = MedicineName;
object ID = cmd.ExecuteScalar();
if (ID != null)
{
MedicineID = ID.ToString();
}
}
catch (Exception ex)
{
var page = HttpContext.Current.CurrentHandler as Page;
//eObj.ErrorData(ex, page);
}
finally
{
if (dcon.SQLCon != null)
{
dcon.DisconectDB();
}
}
return MedicineID;
}
public void DeleteMedicines(string MedicineID)
{
dbConnection dcon = null;
try
{
dcon = new dbConnection();
dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = dcon.SQLCon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[DeleteMedicines]";
cmd.Parameters.Add("@ClinicID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(ClinicID);
cmd.Parameters.Add("@MedicineID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(MedicineID);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dcon.SQLCon != null)
{
dcon.DisconectDB();
}
}
}
/// <summary>
///
/// </summary>
/// <param name="IssueID"></param>
public void DeleteIssueHeader(string IssueID)
{
dbConnection dcon = null;
try
{
dcon = new dbConnection();
dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = dcon.SQLCon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[DeleteIssueHeader]";
cmd.Parameters.Add("@IssueID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(IssueID);
cmd.Parameters.Add("@ClinicID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(ClinicID);
cmd.Parameters.Add("@Status", SqlDbType.Int);
cmd.Parameters["@Status"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
int Outputval = (int)cmd.Parameters["@Status"].Value;
if (Outputval == 1)
{
//Success
var page = HttpContext.Current.CurrentHandler as Page;
//eObj.DeleteSuccessMessage(page);
}
else
{
if (Outputval == 0)
{
//Failure
var page = HttpContext.Current.CurrentHandler as Page;
//eObj.DeletionFailureMessage(page);
}
}
}
catch (Exception ex)
{
var page = HttpContext.Current.CurrentHandler as Page;
//eObj.ErrorData(ex, page);
}
finally
{
if (dcon.SQLCon != null)
{
dcon.DisconectDB();
}
}
}
public DataSet PrescriptionDetails(Guid PatientID)
{
SqlConnection con = null;
DataSet ds = null;
SqlDataAdapter sda = null;
try
{
dbConnection dcon = new dbConnection();
con = dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[PrescriptionDetails]";
cmd.Parameters.Add("@PatientID", SqlDbType.UniqueIdentifier).Value = PatientID;
// PrescriptionDT.PrescID,MedicineID,Qty,Unit,Dosage,Timing,Days will be diplayed
sda = new SqlDataAdapter();
cmd.ExecuteNonQuery();
sda.SelectCommand = cmd;
ds = new DataSet();
sda.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (con != null)
{
con.Dispose();
}
}
}
public DataSet ViewOutofStockMedicines()
{
dbConnection dcon = null;
DataSet ds = null;
SqlDataAdapter sda = null;
try
{
dcon = new dbConnection();
dcon.GetDBConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = dcon.SQLCon;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[ViewOutofStockMedicines]";
cmd.Parameters.Add("@ClinicID", SqlDbType.UniqueIdentifier).Value = Guid.Parse(ClinicID);
sda = new SqlDataAdapter();
cmd.ExecuteNonQuery();
sda.SelectCommand = cmd;
ds = new DataSet();
sda.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dcon.SQLCon != null)
{
dcon.DisconectDB();
}
}
}
