public frmLabReferFee()
{
InitializeComponent();
sqldb = new clsSQL();
grd.DataSource = sqldb.GetDataSet("select * from tbllabreferfee as rf inner join tbllabtest as lt on lt.labTestPK= rf.testPK").Tables[0];
lueLabTest.DataSource = sqldb.GetDataSet("Select * from tbllabtest").Tables[0];
}
public frmLabReferFee()
{
InitializeComponent();
sqldb = new clsSQL();
grd.DataSource = sqldb.GetDataSet("select * from tbllabreferfee as rf inner join tbllabtest as lt on lt.labTestPK= rf.testPK").Tables[0];
lueLabTest.DataSource = sqldb.GetDataSet("Select * from tbllabtest").Tables[0];
}
public frmLabSubTestDetail()
{
InitializeComponent();
cboLabTest.DataSource = SqlDb.GetDataSet("Select labTestPK,labTestName from tbllabTest").Tables[0];
cboLabTest.DisplayMember = "labTestName";
cboLabTest.ValueMember = "labTestPK";
}
private void BindingPosition()
{
DataSet dsPosition = SqlDb.GetDataSet("SELECT positionPK, position, isActive FROM tblPosition WHERE isDelete = 0");
grdPosition.DataSource = dsPosition.Tables[0];
grdViewPosition.FocusedRowHandle = curIndex;
}
private void BindingUserRole()
{
DataSet dsUserRole = SqlDb.GetDataSet("SELECT rolePK, roleName, isActive FROM sysroles WHERE isDelete = 0");
grdUserRole.DataSource = dsUserRole.Tables[0];
grdViewUserRole.FocusedRowHandle = curIndex;
}
private void Binding()
{
DataSet ds = SqlDb.GetDataSet("SELECT chemNamePK, chemName, isActive FROM tblChemName WHERE isDelete = 0");
grd.DataSource = ds.Tables[0];
grdView.FocusedRowHandle = curIndex;
}
private void BindingDiagnosis()
{
DataSet dsDiagnosis = SqlDb.GetDataSet("SELECT diagnosisPK, diagnosis, isActive FROM tblDiagnosis WHERE isDelete = 0");
grdDiagnosis.DataSource = dsDiagnosis.Tables[0];
grdViewDiagnosis.FocusedRowHandle = curIndex;
}
private void BindingLab()
{
DataSet dsLab = SqlDb.GetDataSet("SELECT labPackPK, labPackName, isActive FROM tblLabPackage WHERE isDelete = 0");
grdLaboratory.DataSource = dsLab.Tables[0];
grdViewLaboratory.FocusedRowHandle = curIndex;
}
private void BindingTitle()
{
DataSet dsTitle = SqlDb.GetDataSet("SELECT titlePK, TitleName, isActive FROM tblTitle WHERE isDelete = 0");
grdTitle.DataSource = dsTitle.Tables[0];
grdViewTitle.FocusedRowHandle = curIndex;
}
private void BindingUserLevel()
{
DataSet dsUserLevel = SqlDb.GetDataSet("SELECT levelPK, UserLevel, isActive FROM sysUserLevel WHERE isDeleted = 0");
grdUserLevel.DataSource = dsUserLevel.Tables[0];
grdViewUserLevel.FocusedRowHandle = curIndex;
}
private void Binding()
{
DataSet ds = SqlDb.GetDataSet("SELECT manuPK, manuName,manuContact,manuAddress,manuEmail,manuCountry, isActive FROM tblManufacturer WHERE isDelete = 0");
grd.DataSource = ds.Tables[0];
grdView.FocusedRowHandle = curIndex;
}
private void Binding()
{
DataSet ds = SqlDb.GetDataSet("SELECT * FROM tblAgent");
grd.DataSource = ds.Tables[0];
grdView.FocusedRowHandle = curIndex;
}
private void BindingPackListTemp()
{
DataSet dsPackListTemp = SqlDb.GetDataSet("SELECT packListPK, packListName, isActive FROM tblPackListTemp WHERE isDelete = 0");
grdPackListTemp.DataSource = dsPackListTemp.Tables[0];
grdViewPackListTemp.FocusedRowHandle = curIndex;
}
private void Binding()
{
DataSet ds = SqlDb.GetDataSet("SELECT labSubTestPK,labTestPK,labSubTestCode, labSubTestName,labSubTestDescription, isActive FROM tblLabSubTest WHERE isDelete = 0");
grd.DataSource = ds.Tables[0];
grdView.FocusedRowHandle = curIndex;
}
private void BindingSpecialize()
{
DataSet dsSpecialize = SqlDb.GetDataSet("SELECT specializePK, specialize, isActive FROM tblSpecialize WHERE isDelete = 0");
grdSpecialize.DataSource = dsSpecialize.Tables[0];
grdViewSpecialize.FocusedRowHandle = curIndex;
}
private void BindingFrequency()
{
DataSet dsDiagnosis = SqlDb.GetDataSet("SELECT tblFrequency.frequencyPK, tblFrequency.frequency, tblTreatmentType.treatmentType, tblFrequency.isActive, tblFrequency.treatmentPK " +
"FROM tblFrequency INNER JOIN tblTreatmentType ON tblFrequency.treatmentPK = tblTreatmentType.treatmentPK WHERE isDelete = 0");
grdFrequency.DataSource = dsDiagnosis.Tables[0];
grdViewFrequency.FocusedRowHandle = curIndex;
}
private void BindingPatient()
{
DataSet dsPatients = SqlDb.GetDataSet("SELECT tblPatient.RegNo, CONCAT(tblTitle.TitleName, ' ', tblPatient.Name) AS FullName, tblPatient.NRC, tblPatient.Gender, " +
"tblPatient.FatherName, tblPatient.DOB, tblPatient.Phone, tblPatient.Address, tblPatient.email, tblPatient.RegDate, tblPatient.Name, tblPatient.titlePK, tblPatient.isDelete " +
"FROM tblPatient INNER JOIN tblTitle ON tblPatient.titlePK = tblTitle.titlePK WHERE (tblPatient.isDelete = 0)");
grdPatient.DataSource = dsPatients.Tables[0];
grdViewPatient.FocusedRowHandle = curIndex;
}
private void frmPackListTempDetail_Load(object sender, EventArgs e)
{
dtGridSource = SqlDb.GetDataSet("SELECT fromUnitPK, fromValue, toUnitPK, 1 toValue FROM tblPackListTempDetail WHERE packListPK=" + txtPackListTempName.Tag.ToString()).Tables[0];
DataSet dsUnitFrom = SqlDb.GetDataSet("SELECT medUnitPK, medUnitName FROM tblMedUnit WHERE isDelete=0 ORDER BY medUnitName");
DataSet dsUnitTo = SqlDb.GetDataSet("SELECT medUnitPK, medUnitName FROM tblMedUnit WHERE isDelete=0 ORDER BY medUnitName");
gridPackList.DataSource = dtGridSource;
resLookupFromUnit.DataSource = dsUnitFrom.Tables[0];
resLookupToUnit.DataSource = dsUnitTo.Tables[0];
}
private void BindingBooking()
{
DateTime curDate = SqlDb.ExecuteScalar <DateTime>("SELECT GETDATE()");
DataSet dsBookging = SqlDb.GetDataSet("SELECT PK, patientName, RegNo, (tblTitle.TitleName + ' ' + tblDoctor.doctor) As doctorName, " +
"abdate, tokenNo, abTime, abType, isVisited, isNew, (tblBooking.doctorPK) As doctorPK FROM tblDoctor INNER JOIN tblBooking ON tblDoctor.doctorPK = tblBooking.doctorPK INNER JOIN tblTitle " +
"ON tblDoctor.titlePK = tblTitle.titlePK WHERE tblBooking.isVisited = 0 AND abdate > @abDate",
new MySqlParameter("@abDate", curDate.ToString("yyyy-MM-dd")));
grdBooking.DataSource = dsBookging.Tables[0];
grdViewBooking.FocusedRowHandle = curBookingIndex;
}
public frmLabTest()
{
InitializeComponent();
cboLab.DataSource = SqlDb.GetDataSet("Select labPK,labName from tblLaboratory ").Tables[0];
cboLab.ValueMember = "labPK";
cboLab.DisplayMember = "labName";
cboBodySystem.DataSource = SqlDb.GetDataSet("Select labCatPK,labCatName from tblLabCat").Tables[0];
cboBodySystem.ValueMember = "labCatPK";
cboBodySystem.DisplayMember = "labCatName";
cboType.DataSource = SqlDb.GetDataSet("Select labTypePK,labTypeName from tblLabType").Tables[0];
cboType.ValueMember = "labTypePK";
cboType.DisplayMember = "labTypeName";
}
private void BindingVisiting()
{
DateTime curDate = SqlDb.ExecuteScalar <DateTime>("SELECT GETDATE()");
int curDoctorPK = SqlDb.ExecuteScalar <int>("SELECT doctorPK FROM tblDoctor WHERE userPK = @UserPK",
new MySqlParameter("@UserPK", AppVariable.CURRENT_USER_PK));
DataSet dsVisiting = SqlDb.GetDataSet("SELECT PK, patientName, RegNo, (tblTitle.TitleName + ' ' + tblDoctor.doctor) As doctorName, " +
"abdate, tokenNo, abTime, abType, isVisited, isNew, (tblBooking.doctorPK) As doctorPK, visitPK FROM tblDoctor INNER JOIN tblBooking ON tblDoctor.doctorPK = tblBooking.doctorPK INNER JOIN tblTitle " +
"ON tblDoctor.titlePK = tblTitle.titlePK WHERE abdate = @abDate AND tblBooking.doctorPK=@DoctorPK",
new MySqlParameter("@abDate", curDate.ToString("yyyy-MM-dd")),
new MySqlParameter("@DoctorPK", curDoctorPK));
grdTodayVisitPatients.DataSource = dsVisiting.Tables[0];
}
private void BindingMedicine()
{
DataSet dsPatients = SqlDb.GetDataSet("SELECT m.medPK, m.medName, " +
"mtype.medTypeName,cname.chemName, bsys.systemName, " +
"manu.manuName, manu.manuCountry, m.isActive " +
"FROM tblMedicine m " +
"JOIN tblMedType mtype ON m.medTypePK = mtype.medTypePK " +
"JOIN tblChemName cname ON m.chemNamePK = cname.chemNamePK " +
"JOIN tblBodySystem bsys ON m.systemPK = bsys.systemPK " +
"JOIN tblManufacturer manu ON m.manuPK = manu.manuPK " +
"WHERE m.isDelete = 0");
grdMedicine.DataSource = dsPatients.Tables[0];
grdViewMedicine.FocusedRowHandle = curIndex;
}
private void BindingTitle()
{
DataSet dsTitle = SqlDb.GetDataSet("SELECT titlePK, TitleName FROM tblTitle WHERE isActive = 1");
try
{
cboTitle.DataSource = dsTitle.Tables[0];
cboTitle.DisplayMember = "TitleName";
cboTitle.ValueMember = "titlePK";
cboTitle.SelectedIndex = -1;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void frmLabTechReqSample_Load(object sender, EventArgs e)
{
dteSampleDate.EditValue = DateTime.Now;
if (intLabTechReqPK > 0)
{
DataRow drReq = SqlDb.GetDataSet("select req.labtechnicianrequestPK, req.labInvoiceNo, req.labTestPK,req.sampleID, req.hasSample, " +
"req.hasResult, req.resultDateTime, lab.labTestCode,lab.labTestName, lab.companyPK,p.RegNo, p.Name, " +
"p.FatherName, p.DOB from tbllabtechnicianrequest as req " +
"join tbllabtest as lab on lab.labTestPK = req.labTestPK " +
"join tbllabinvoice as inv on inv.labInvoiceNo = req.labInvoiceNo " +
"join tblpatient as p on p.RegNo = inv.patientPK " +
"where req.labtechnicianrequestPK = " + intLabTechReqPK.ToString()).Tables[0].Rows[0];
lblRegNo.Text = drReq["RegNo"].ToString();
lblPatient.Text = drReq["Name"].ToString();
lblLabTestCode.Text = drReq["labTestCode"].ToString();
lblLabTestName.Text = drReq["labTestName"].ToString();
int intHasSameple = int.Parse(drReq["hasSample"].ToString());
if (intHasSameple == 0)
{
chkHasSample.Checked = false;
}
else
{
chkHasSample.Checked = true;
}
}
}
private void BindingUser()
{
DataSet dsUser = SqlDb.GetDataSet("SELECT sysUser.PK, sysUser.name, sysUser.login, sysUser.passw, sysUserLevel.UserLevel, sysUser.levelPK, " +
"sysUser.createPK, sysUser.createDate, sysUser.updatePK, sysUser.updateDate, sysUser.isActive " +
"FROM sysUser INNER JOIN sysUserLevel ON sysUser.levelPK = sysUserLevel.levelPK WHERE isDelete = 0");
grdUser.DataSource = dsUser.Tables[0];
grdViewUser.FocusedRowHandle = curIndex;
}
private void BindingDoctor()
{
DataSet dsDoctor = SqlDb.GetDataSet("SELECT tblDoctor.doctorPK, CONCAT(tblTitle.TitleName, ' ', tblDoctor.doctor) AS doctorName, tblSpecialize.specialize, tblPosition.position, tblDoctor.gender, tblDoctor.samaNo, " +
"tblDoctor.address, tblDoctor.homePhone, tblDoctor.mobilePhone, tblDoctor.email, tblDoctor.isActive, tblDoctor.doctor, tblDoctor.titlePK, tblDoctor.specializePK, tblDoctor.positionPK " +
"FROM tblDoctor JOIN tblPosition ON tblDoctor.positionPK = tblPosition.positionPK JOIN tblSpecialize ON tblDoctor.specializePK = tblSpecialize.specializePK " +
"JOIN tblTitle ON tblDoctor.titlePK = tblTitle.titlePK WHERE tblPosition.isDelete = 0");
grdDoctor.DataSource = dsDoctor.Tables[0];
grdViewDoctor.FocusedRowHandle = curIndex;
}
private void cmdLogin_Click(object sender, EventArgs e)
{
if (loginCnt == 3)
{
Application.ExitThread();
}
else if (ValidateForm() == true)
{
//string Login = SqlDb.ExecuteScalar<string>("SELECT login FROM sysUser WHERE login=@Login AND passw=@PWD AND levelPK=@LevelPK AND isDelete=@isDelete AND isActive=@isActive",
// new MySqlParameter("@login", txtLogin.Text.Trim()),
// new MySqlParameter("@PWD", Crypto.Encrypt(txtPassword.Text.Trim())),
// new MySqlParameter("@LevelPK", cboUserLevel.SelectedValue),
// new MySqlParameter("@isDelete", false),
// new MySqlParameter("@isActive", true));
string Login = SqlDb.ExecuteScalar <string>("SELECT login FROM sysUser WHERE login=@Login AND passw=@PWD AND isDelete=@isDelete AND isActive=@isActive",
new MySqlParameter("@login", txtLogin.Text.Trim()),
new MySqlParameter("@PWD", Crypto.Encrypt(txtPassword.Text.Trim())),
new MySqlParameter("@isDelete", false),
new MySqlParameter("@isActive", true));
int AuthCnt = (Login == txtLogin.Text.Trim()) ? 1 : 0;
if (AuthCnt > 0)
{
DataSet dsUser = SqlDb.GetDataSet("SELECT PK, name, login, levelPK, branchPK FROM sysUser WHERE login=@Login AND passw=@PWD AND isDelete=@isDelete AND isActive=@isActive",
new MySqlParameter("@login", txtLogin.Text.Trim()),
new MySqlParameter("@PWD", Crypto.Encrypt(txtPassword.Text.Trim())),
new MySqlParameter("@isDelete", false),
new MySqlParameter("@isActive", true));
AppVariable.CURRENT_USER_PK = int.Parse(dsUser.Tables[0].Rows[0]["PK"].ToString());
AppVariable.CURRENT_USER_FULLNAME = dsUser.Tables[0].Rows[0]["name"].ToString();
AppVariable.CURRENT_USER_LEVEL_ID = int.Parse(dsUser.Tables[0].Rows[0]["levelPK"].ToString());
AppVariable.CURRENT_LOGIN_ID = dsUser.Tables[0].Rows[0]["login"].ToString();
AppVariable.CURRENT_BRANCH_PK = int.Parse(dsUser.Tables[0].Rows[0]["branchPK"].ToString());
sysLogs.logs_login();
//new frmLabInvoice().Show();
//return;
Main MainForm = new Main();
MainForm.Show();
this.Hide();
}
else
{
loginCnt = loginCnt++;
MessageBox.Show("Your Login Information is not valid. Please try again.", "MediPro :: Clinic System", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtLogin.Focus();
}
}
}
private void CTDBinding()
{
if (cboDoctor.Text.Length > 0)
{
grdClinicTime.Enabled = true;
DataSet dsCTD = SqlDb.GetDataSet("SELECT ctdPK, doctorPK, clinicDay, ctdOption, toTime, fromTime FROM tblClinicTimeByDoctor " +
"WHERE isDelete = 0 AND doctorPK = @DoctorPK AND clinicDay=@clinicDay",
new MySqlParameter("@clinicDay", cboDay.Text),
new MySqlParameter("@DoctorPK", cboDoctor.SelectedValue));
grdClinicTime.DataSource = dsCTD.Tables[0];
}
else
{
DataSet dsCTD = SqlDb.GetDataSet("SELECT ctdPK, doctorPK, clinicDay, ctdOption, toTime, fromTime FROM tblClinicTimeByDoctor " +
"WHERE isDelete = 0 AND DoctorPK = 0");
grdClinicTime.DataSource = dsCTD.Tables[0];
grdClinicTime.Enabled = false;
}
}
private void BindingTrementType()
{
DataSet dsTreatment = SqlDb.GetDataSet("SELECT treatmentPK, treatmentType FROM tblTreatmentType");
try
{
cboTreatmentType.DataSource = dsTreatment.Tables[0];
cboTreatmentType.DisplayMember = "treatmentType";
cboTreatmentType.ValueMember = "treatmentPK";
cboTreatmentType.SelectedIndex = -1;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void BindingUserLevel()
{
DataSet dsUserLevel = new DataSet();
dsUserLevel = SqlDb.GetDataSet("SELECT levelPK, UserLevel FROM sysUserLevel");
try
{
cboUserLevel.DataSource = dsUserLevel.Tables[0];
cboUserLevel.DisplayMember = "UserLevel";
cboUserLevel.ValueMember = "levelPK";
cboUserLevel.SelectedIndex = -1;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public frmLabPackageDetail()
{
InitializeComponent();
DataTable dt = SqlDb.GetDataSet("Select labTestPK,labTestName from tblLabTest").Tables[0];
foreach (DataRow dr in dt.Rows)
{
dgv.Rows.Add(dr.ItemArray);
}
cboLab.DataSource = SqlDb.GetDataSet("Select * from tblLaboratory").Tables[0];
cboLab.DisplayMember = "labName";
cboLab.ValueMember = "labPK";
}
public static DataTable GetAllLabTestPrice()
{
clsSQL sql = new clsSQL();
return sql.GetDataSet("Select labtest.labTestPK,labTest.labTestCode,labTest.labTestName, tbllabtestprice.* from tblLabTest labtest left join tblLabTestPrice on labtest.labTestPK=tblLabTestPrice.labTestPK").Tables[0];
}
public static DataTable GetAllLabTest()
{
clsSQL sql = new clsSQL();
return sql.GetDataSet("select * from tblLabTest").Tables[0];
}