private void GetAdmissionOrInvestigationInfo(string sl, string mm, string yy, int docId)
{
strErr = "";
if (rbtnAdmission.Checked || rbtnInvestigation.Checked)
{
var sql = "USP_Get_Admission_Investigation_Info " + sl + "," + mm + "," + yy + "," + (rbtnAdmission.Checked ? "1" : "2") + "," + docId + "," + Utility.DCP;
objData.OpenConnection("AatickPC-Office", ref strErr);
var dtInfo = objData.RetriveData(sql, ref strErr);
objData.CloseConnection();
if (strErr != "")
{
MessageBox.Show("An error occured.\n\nTry again later.");
}
else
{
if (dtInfo.Rows.Count > 0)
{
grdInfo.DataSource = dtInfo;
grdInfo.Columns[0].Visible = false;
}
else
{
MessageBox.Show("No " + (rbtnAdmission.Checked ? "Admission" : "Investigation") + " found.");
grdInfo.DataSource = null;
}
grdDetails.DataSource = null;
}
}
else
{
MessageBox.Show("Please select Admission or Investigation radio button.");
}
}
private void frmLoad()
{
clearGrid();
lblPName.Text = "";
lblPAddress.Text = "";
lblDoc.Text = "";
lbltotalamount.Text = "";
lbltotalpayamount.Text = "";
lbltotaldiscount.Text = "";
lblvat.Text = "";
lbltotalpayamount.Text = "";
lbltotalpaid.Text = "";
lbltotalreturn.Text = "";
lbltotaldue.Text = "";
//throw new NotImplementedException();
var strSql = "";
strSql = "select ROW_NUMBER() over( order by inv.InvestigationID) SL,cast(inv.Invetigation_SL as varchar(6))+'/'+cast( MONTH(inv.CreatedON) as varchar(2))+'/'+cast(year(inv.CreatedON) as varchar(4)) investigation, S.Service_Name TestName,invItem.ReceivedOn, invItem.Delivered, invItem.DeliveryOn, inv.Investigationid, invItem.ISI_ID ";
strSql = strSql + "from dbo_Investigation inv inner join dbo_InvertigationServiceItem invItem on inv.InvestigationID =invItem.Investigationid ";
strSql = strSql + "inner join Dbo_Services S On invItem.Service_ID = s.Service_ID ";
strSql = strSql + "left join Dbo_service_Itemwise_Test ST On s.Service_ID = st.Service_ID ";
//strSql = strSql +"where invItem.Status in (4)";
DataTable arrData = objData.RetriveData(strSql, ref strErr);
DataSet ds = new DataSet();
ds.Tables.Add(arrData);
dgvFontDeskReport.DataSource = ds.Tables["Table1"];
dgvFontDeskReport.Columns["Investigationid"].Visible = false;
dgvFontDeskReport.Columns["ISI_ID"].Visible = false;
btnDeliver.Enabled = false;
btnReceive.Enabled = true;
}
private void getMaxID()
{
//throw new NotImplementedException();
string strSQL = "";
strSQL = "select max(SIT_ID) from Dbo_service_Itemwise_Test";
DataTable arrID = objData.RetriveData(strSQL, ref strErr);
int Id = 0;
if (arrID.Rows.Count > 0)
{
if (arrID.Rows[0][0].ToString() == "")
{
Id = 1;
}
else
{
Id = Convert.ToInt32(arrID.Rows[0][0].ToString()) + 1;
}
}
else
{
Id = 1;
}
txtSIT_ID.Text = Id.ToString();
}
private void loadReport()
{
var strSql = "usp_Inv_LabReport " + txtInvestigstionID.Text;
var dtResult = objData.RetriveData(strSql, ref strErr);
var strSqlDocReportComplete = "select top 1 U.Name From dbo_Investigation I Inner join dbo_InvertigationServiceItem items on i.InvestigationID = items.Investigationid Inner join Investigation_Serviceitem_History H on items.ISI_ID = H.ISI_ID inner join dbo_Users U On H.UserID = U.User_ID Where i.InvestigationID = " + txtInvestigstionID.Text + " and H.Status = 3";
var strSqlDocReportApprove = "select top 1 D.Title ,D.Name, D.Degree, D.Specialist From dbo_Investigation I Inner join dbo_InvertigationServiceItem items on i.InvestigationID = items.Investigationid Inner join Investigation_Serviceitem_History H on items.ISI_ID = H.ISI_ID inner join dbo_Users U On H.UserID = U.User_ID inner join dbo_Doctor_List D On U.DocID = D.DocID Where i.InvestigationID = "+ txtInvestigstionID.Text + " and H.Status = 4";
var strDocReportCompleteName = "";
var dtDocReportComplete = objData.RetriveData(strSqlDocReportComplete, ref strErr);
if (strErr == "")
{
if (dtDocReportComplete.Rows.Count > 0)
{
strDocReportCompleteName = dtDocReportComplete.Rows[0][0].ToString();
}
}
var strDocReportApproveName = "";
var strDocReportApproveTitle = "";
var strDocReportApproveDegree = "";
var strDocReportApproveSpecialst = "";
var dtDocReportApprove = objData.RetriveData(strSqlDocReportApprove, ref strErr);
if (strErr == "")
{
if (dtDocReportApprove.Rows.Count > 0)
{
strDocReportApproveName = dtDocReportApprove.Rows[0][1].ToString();
strDocReportApproveTitle = dtDocReportApprove.Rows[0][0].ToString();
strDocReportApproveDegree = dtDocReportApprove.Rows[0][2].ToString();
strDocReportApproveSpecialst = dtDocReportApprove.Rows[0][3].ToString();
}
}
var reportDoc = new ReportDocument();
var dataset = new DataSet();
dataset.Tables.Add(dtResult);
var rptpath = Utility.ReportPath + "rptLabReportPath.rpt";
reportDoc.Load(rptpath);
reportDoc.SetDataSource(dataset.Tables[0]);
reportDoc.SetParameterValue("ReportCompleteName", strDocReportCompleteName);
reportDoc.SetParameterValue("DocReportApproveName", strDocReportApproveName);
reportDoc.SetParameterValue("DocReportApproveTitle", strDocReportApproveTitle);
reportDoc.SetParameterValue("DocReportApproveDegree", strDocReportApproveDegree);
reportDoc.SetParameterValue("DocReportApproveSpecialst", strDocReportApproveSpecialst);
reportDoc.SetParameterValue("UserName", Utility.UserName);
crViewer.ReportSource = reportDoc;
crViewer.ToolPanelView = ToolPanelViewType.None;
}
private void txtinvestigationid_KeyPress(object sender, KeyPressEventArgs e)
{
if (e.KeyChar == (char)Keys.Enter)
{
var arrInvSl = txtinvestigationid.Text.Trim().Split('/');
if (arrInvSl.Length == 3)
{
objData.OpenConnection("AatickPC-Office", ref strErr);
var strSql =
"SELECT PT.Name,PT.PresentAddress,DL.Title+' '+DL.Name+CHAR(13)+DL.Degree+CHAR(13)+DL.Specialist AS DocInfo";
strSql += " ,dbo.f_Get_Payable_Amount(INV.InvestigationID),dbo.f_get_inv_DiscountAmont(INV.InvestigationID),dbo.f_Get_Inv_PaidAmount(INV.InvestigationID),dbo.f_Get_Inv_ReturnAmount(INV.InvestigationID),INV.InvestigationID,CASE WHEN SP.Paid_Amount>0 THEN 1 ELSE CASE WHEN SP.InvestigationID IS NULL THEN 2 ELSE 0 END END IsPaid,INV.DiscountById,SP.Receive_By,INV.CreatedON ";
strSql += " FROM dbo_Investigation INV ";
strSql += " INNER JOIN dbo_Patient_info PT ON PT.P_ID=INV.P_ID";
strSql += " INNER JOIN dbo_Doctor_List DL ON DL.DocID=INV.DocID";
strSql += " LEFT JOIN Investigation_SpootCommission SP ON SP.InvestigationID=INV.InvestigationID";
strSql += " WHERE INV.Invetigation_SL=" + arrInvSl[0] + " AND MONTH(INV.CreatedON)=" + arrInvSl[1] + " AND YEAR(INV.CreatedON)=" + arrInvSl[2];
var spotCommissionInfo = objData.RetriveData(strSql, ref strErr);
objData.CloseConnection();
if (strErr == "" && spotCommissionInfo.Rows.Count > 0)
{
lblName.Text = spotCommissionInfo.Rows[0][0].ToString();
lblAddress.Text = spotCommissionInfo.Rows[0][1].ToString();
lblDoctor.Text = spotCommissionInfo.Rows[0][2].ToString();
lbltotalamount.Text = spotCommissionInfo.Rows[0][3].ToString();
lbltotaldiscount.Text = spotCommissionInfo.Rows[0][4].ToString();
lbltotalpayamount.Text =
(double.Parse(spotCommissionInfo.Rows[0][3].ToString()) -
double.Parse(spotCommissionInfo.Rows[0][4].ToString())).ToString();
lbltotalpaid.Text = spotCommissionInfo.Rows[0][5].ToString();
lbltotalreturn.Text = spotCommissionInfo.Rows[0][6].ToString();
lbltotaldue.Text =
(double.Parse(lbltotalpayamount.Text) -
double.Parse(spotCommissionInfo.Rows[0][5].ToString()) -
double.Parse(spotCommissionInfo.Rows[0][6].ToString())).ToString();
int.TryParse(spotCommissionInfo.Rows[0][7].ToString(), out investigationId);
InvestigationCreationDate = spotCommissionInfo.Rows[0][11].ToString();
btnsave.Enabled = spotCommissionInfo.Rows[0][8].ToString() != "1";
if (spotCommissionInfo.Rows[0][8].ToString() == "1")
{
MessageBox.Show("Already paid spot commission.");
txtreceiveby.Text = spotCommissionInfo.Rows[0][10].ToString();
}
ShowReport(spotCommissionInfo.Rows[0][8].ToString() == "1");
}
else
{
MessageBox.Show(@"Invalid investigation id.");
ClearFields();
}
}
else
{
MessageBox.Show(@"Invalid investigation id.");
ClearFields();
}
}
}
private void fillGrdView()
{
//throw new NotImplementedException();
string strSql = "";
strSql = "select Deg.DegId,dept.Name DeptName, deg.Designation DegName From dbo_Designation Deg Inner Join dbo_Department dept On deg.DeptID = dept.DeptID order by deg.DegId Desc";
DataTable dtDeptInfo = objData.RetriveData(strSql, ref strErr);
DataSet ds = new DataSet();
ds.Tables.Add(dtDeptInfo);
dgvDensignation.DataSource = ds.Tables["Table1"];
}
private void frmLoad()
{
//throw new NotImplementedException();
lbPathlab.Items.Clear();
lbPathlab.Items.Add(new hms.Include_Files.Utility.ComboboxItem("Test Complete", 1));
lbPathlab.Items.Add(new hms.Include_Files.Utility.ComboboxItem("Report Complete", 2));
lbPathlab.Items.Add(new hms.Include_Files.Utility.ComboboxItem("Report Aprove", 3));
txtinvestigationid.Text = "/" + DateTime.Today.Month + "/" + DateTime.Today.Year;
//lbPathlab.SelectedIndex = lbPathlab.FindStringExact("Test Complete");
//to select the selected item
//lbPathlab.SelectedIndex = lbPathlab.FindStringExact("Test Complete");
var strSql = "SELECT Category_ID,Category_Name FROM Dbo_Service_Category where cat_type=1 ORDER BY Category_Name";
var categoryList = objData.RetriveData(strSql, ref strErr);
var x = 20;
var y = 20;
var checkBoxPerRow = 4;
var eachWidth = gbtestcatagory.Width / checkBoxPerRow;
for (var i = 0; i < categoryList.Rows.Count; i++)
{
var checkbox = new CheckBox();
checkbox.Text = categoryList.Rows[i][1].ToString();
checkbox.Tag = categoryList.Rows[i][0].ToString();
checkbox.Checked = false;
checkbox.Location = new Point(x, y);
checkbox.BringToFront();
checkbox.CheckedChanged += checkbox_CheckedChanged;
gbtestcatagory.Controls.Add(checkbox);
y = (i + 1) % checkBoxPerRow == 0 ? (y + 30) : (y);
x = (i + 1) % checkBoxPerRow == 0 ? (20) : (x + eachWidth);
}
//var list = new string[] { "sdf", "sdf", "sdf", "sdf", "sdf", "sdf", "sdf", "sdf" };
//for (var i = 0; i < list.Length; i++)
//{
// var checkbox = new CheckBox();
// checkbox.Text = list[i];//categoryList.Rows[i][1].ToString();
// checkbox.Tag = categoryList.Rows[i][0].ToString();
// checkbox.Checked = false;
// checkbox.Location = new Point(x, y);
// checkbox.BringToFront();
// checkbox.CheckedChanged += checkbox_CheckedChanged;
// gbtestcatagory.Controls.Add(checkbox);
// y = (i + 1) % checkBoxPerRow == 0 ? (y + 30) : (20);
// x = (i + 1) % checkBoxPerRow == 0 ? (20) : (x + eachWidth);
//}
formateGrid();
fillGridView();
}
private void txtDoctorId_KeyPress(object sender, KeyPressEventArgs e)
{
if (e.KeyChar == (char)Keys.Enter)
{
btnsave.Enabled = false;
var docId = txtDoctorId.Text.Trim();
if (docId == "")
{
MessageBox.Show("Please enter doctor id.");
return;
}
objData.OpenConnection("AatickPC-Home", ref strErr);
var sql = "USP_Doctor_Payment_Due_Amount " + docId + ",'I'," + Utility.DCP;
var dtDoctorPayment = objData.RetriveData(sql, ref strErr);
if (strErr == "" && dtDoctorPayment.Rows.Count > 0)
{
txtpayableamount.Text = dtDoctorPayment.Rows[0][0].ToString();
if (double.Parse(txtpayableamount.Text.Trim()) > 0)
{
btnsave.Enabled = true;
}
}
else
{
txtpayableamount.Text = "0";
}
strErr = "";
sql = "rpt_Doctor_Ledger " + docId + ",'I'," + Utility.DCP;
var dtDoctorLedger = objData.RetriveData(sql, ref strErr);
objData.CloseConnection();
if (strErr == "" && dtDoctorLedger.Rows.Count > 0)
{
var reportDoc = new ReportDocument();
var rptpath = Utility.ReportPath + "rptDoctorLedger.rpt";
reportDoc.Load(rptpath);
reportDoc.SetDataSource(dtDoctorLedger);
crViewer.ReportSource = reportDoc;
crViewer.ToolPanelView = ToolPanelViewType.None;
}
else
{
crViewer.ReportSource = null;
MessageBox.Show("No ledger found.");
}
}
else
{
e.Handled = !char.IsDigit(e.KeyChar) && !char.IsControl(e.KeyChar);
}
}
public frmMarketingTargetSetup()
{
InitializeComponent();
objData.OpenConnection("AatickPC-Office", ref strErr);
var sql = "SELECT EmpID,Name FROM dbo_Employee_Profiles";
var employeeList = objData.RetriveData(sql, ref strErr);
objData.CloseConnection();
cmbEmployee.DisplayMember = "Name";
cmbEmployee.ValueMember = "EmpId";
cmbEmployee.DataSource = employeeList;
cmbEmployee.SelectedIndex = -1;
cmbEmployee.SelectedIndexChanged += cmbEmployee_SelectedIndexChanged;
}
private void lsttestname_SelectedIndexChanged(object sender, EventArgs e)
{
if (isFirstTime != "0")
{
return;
}
var sql = "select Amount from Dbo_Services where service_id = " + lsttestname.SelectedValue;
//C_Data_Batch objData;
strErr = "";
objData = new C_Data_Batch();
objData.OpenConnection("AzharPC-Office", ref strErr);
var arrServicePrice = objData.RetriveData(sql, ref strErr);
if (strErr == "")
{
if (arrServicePrice.Rows.Count > 0)
{
lblServicePrice.Text = arrServicePrice.Rows[0][0].ToString();
}
else
{
lblServicePrice.Text = "";
}
}
else
{
lblServicePrice.Text = "";
}
objData.CloseConnection();
}
private void addService()
{
if (lsttestname.SelectedIndex > -1)
{
var isVal = false;
for (int i = 0; i < dgvInvestigation.Rows.Count; i++)
{
if (dgvInvestigation.Rows[i].Cells[1].Value.ToString() == lsttestname.SelectedValue.ToString())
{
isVal = true;
break;
}
}
if (!isVal)
{
objData = new C_Data_Batch();
objData.OpenConnection("AzharPC-Home", ref strErr);
string strSQL = "";
strSQL = "Select s.Service_ID, s.Service_Name, s.Amount ,(case when Duraion_Type = 1 then dateadd(hh, Dutaion, getdate()) else dateadd(day,Dutaion, getdate() ) end ) 'Delivery Date' , '' Remarks from Dbo_Services S where service_id in (" + lsttestname.SelectedValue + ")";
DataTable arrservice = objData.RetriveData(strSQL, ref strErr);
dgvInvestigation.Rows.Add(false, arrservice.Rows[0][0].ToString(), arrservice.Rows[0][1].ToString(), arrservice.Rows[0][2].ToString(), arrservice.Rows[0][3].ToString(), arrservice.Rows[0][4].ToString());
dgvInvestigation.AutoResizeColumn(0, DataGridViewAutoSizeColumnMode.ColumnHeader);
getTotalCalculation();
objData.CloseConnection();
}
}
}
private void btnpreview_Click(object sender, EventArgs e)
{
objData = new C_Data_Batch();
string strErr = "";
objData.OpenConnection("PolinPC-Office", ref strErr);
//txtCategoryID
string strSQL = "";
strSQL = "select Category_ID, Category_Name from Dbo_Service_Category where Category_ID = 2 order by Category_ID Desc";
DataTable arrCatList = objData.RetriveData(strSQL, ref strErr);
objData.CloseConnection();
DataSet ds = new DataSet();
ds.Tables.Add(arrCatList);
frmReport reportviewer = new frmReport();
var rptpath = Utility.ReportPath + "rptCategory.rpt";
reportviewer.ReportPath = rptpath;
reportviewer.Reportds = ds;
reportviewer.Show(this);
}
private void btnpreview_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtAdmissionId.Text.Trim()))
{
MessageBox.Show(@"Please select a patient.");
return;
}
var isSummary = rbtnPatientSummary.Checked;
if (rbtnPatientDetails.Checked || rbtnPatientSummary.Checked)
{
if (isSummary)
{
objData.OpenConnection("AatickPC-Office", ref strErr);
var sqlBill = "USP_HospitalConsultantBill_Summery " + txtAdmissionId.Text;
var dtBillInfo = objData.RetriveData(sqlBill, ref strErr);
objData.CloseConnection();
var rptpath = Utility.ReportPath + "rptHospitalBill.rpt";
var report = new ReportDocument();
report.Load(rptpath);
report.SetDataSource(dtBillInfo);
report.SetParameterValue("UserName", Utility.UserName);
var reportviewer = new frmReport(report);
reportviewer.Show(this);
}
}
else
{
MessageBox.Show(@"Please select any one report option.");
}
}
private void getServiceID()
{
//throw new NotImplementedException();
objData = new C_Data_Batch();
string strErr = "";
objData.OpenConnection("PolinPC-Office", ref strErr);
//txtCategoryID
string strSQL = "";
strSQL = "select max(Service_ID) from Dbo_Services";
DataTable arrID = objData.RetriveData(strSQL, ref strErr);
objData.CloseConnection();
int Id = 0;
if (arrID.Rows.Count > 0)
{
if (arrID.Rows[0][0].ToString() == "")
{
Id = 1;
}
else
{
Id = Convert.ToInt32(arrID.Rows[0][0].ToString()) + 1;
}
}
else
{
Id = 1;
}
txtServiceID.Text = Id.ToString();
}
public int getDoctorId()
{
int doctorid = 0;
string strErr = "";
string strSQL = "select max(DocID) from dbo_Doctor_list";
objData = new C_Data_Batch();
objData.OpenConnection("PolinPC-Office", ref strErr);
DataTable dtMaxDictorID = objData.RetriveData(strSQL, ref strErr);
//string[,] arrMaxDictorID = objData.RetriveDataArray(strSQL, ref strErr);
objData.CloseConnection();
if (dtMaxDictorID.Rows.Count > 0)
{
if (dtMaxDictorID.Rows[0][0].ToString() != "")
{
doctorid = Convert.ToInt32(dtMaxDictorID.Rows[0][0].ToString()) + 1;
}
else
{
doctorid = 1;
}
}
else
{
doctorid = 1;
}
return(doctorid);
}
private void getEmployeeID()
{
//throw new NotImplementedException();
string strErr = "";
objetdata = new C_Data_Batch();
string strSQL = "";
strSQL = "select max(EmpID) from dbo_Employee_Profiles";
objetdata.OpenConnection("PolinPC-Office", ref strErr);
Int32 EmpID = 0;
DataTable arrEmpID = objetdata.RetriveData(strSQL, ref strErr);
if (arrEmpID.Rows.Count > 0)
{
if (arrEmpID.Rows[0][0].ToString() != "")
{
EmpID = Convert.ToInt32(arrEmpID.Rows[0][0].ToString()) + 1;
}
else
{
EmpID = 1;
}
}
else
{
EmpID = 1;
}
txtEmpployeeID.Text = EmpID.ToString();
}
private void btnpreview_Click(object sender, EventArgs e)
{
objData = new C_Data_Batch();
string strErr = "";
objData.OpenConnection("PolinPC-Office", ref strErr);
//txtCategoryID
string strSQL = "";
strSQL = "SELECT st.[SIT_ID],s.[Service_Name],st.[OGroup],st.[Entity],st.[Unit],st.[RefRenge],st.[Result_B] FROM [Dbo_service_Itemwise_Test] ST inner Join Dbo_Services S On St.Service_ID=s.Service_ID order by st.[SIT_ID] desc";
DataTable arrCatList = objData.RetriveData(strSQL, ref strErr);
objData.CloseConnection();
DataSet ds = new DataSet();
ds.Tables.Add(arrCatList);
frmReport reportviewer = new frmReport();
var rptpath = Utility.ReportPath + "rptServicesWiseTest.rpt";
reportviewer.ReportPath = rptpath;
reportviewer.Reportds = ds;
reportviewer.Show(this);
}
private void fillDesignation()
{
//throw new NotImplementedException();
objetdata = new C_Data_Batch();
string strErr = "";
objetdata.OpenConnection("PolinPC-Office", ref strErr);
string strSQL = "";
hms.Include_Files.Utility.ComboboxItem itms = cmbDepartment.SelectedItem as hms.Include_Files.Utility.ComboboxItem;
var DeptID = 0;
if (itms != null)
{
DeptID = itms.Value;
strSQL = "select DegId, Designation from dbo_Designation where DeptID= " + DeptID + " order by DeptID Desc ";
DataTable arrDept = objetdata.RetriveData(strSQL, ref strErr);
objetdata.CloseConnection();
//DataSet ds = new DataSet();
//ds.Tables.Add(arrDept);
//dgvEmpprofile.DataSource = ds.Tables["Table1"];
cmbDesignation.Items.Clear();
if (arrDept.Rows.Count > 0)
{
for (int i = 0; i < arrDept.Rows.Count; i++)
{
cmbDesignation.Items.Add(new hms.Include_Files.Utility.ComboboxItem(arrDept.Rows[i][1].ToString(), Convert.ToInt32(arrDept.Rows[i][0].ToString())));
}
}
}
else
{
MessageBox.Show("Please select the Department");
}
}
private void getcategorid()
{
objData = new C_Data_Batch();
string strErr = "";
objData.OpenConnection("PolinPC-Office", ref strErr);
//txtCategoryID
string strSQL = "";
strSQL = "select max(Category_ID) from Dbo_Service_Category";
DataTable arrCatID = objData.RetriveData(strSQL, ref strErr);
objData.CloseConnection();
int CatId = 0;
if (arrCatID.Rows.Count > 0)
{
if (arrCatID.Rows[0][0].ToString() == "")
{
CatId = 1;
}
else
{
CatId = Convert.ToInt32(arrCatID.Rows[0][0].ToString()) + 1;
}
}
else
{
CatId = 1;
}
txtCategoryID.Text = CatId.ToString();
}
private void btnpreview_Click(object sender, EventArgs e)
{
objData = new C_Data_Batch();
string strErr = "";
objData.OpenConnection("PolinPC-Office", ref strErr);
//txtCategoryID
string strSQL = "";
strSQL = "SELECT [Service_ID],cat.Category_Name,[Service_Name],[Amount],[Commission],case when [Commision_Type] = 1 then '%' else 'Tk' end 'Commision Type',[Dutaion] ,case when [Duraion_Type] = 1 then 'Hour' else 'day' end 'Duraion Type' ,[CC_PC],[Sample_Type],[isSample] FROM [Dbo_Services] S Inner Join dbo_Service_category Cat On S.Category_ID = CAt.Category_ID order by Service_id Desc";
DataTable arrCatList = objData.RetriveData(strSQL, ref strErr);
objData.CloseConnection();
DataSet ds = new DataSet();
ds.Tables.Add(arrCatList);
frmReport reportviewer = new frmReport();
var rptpath = Utility.ReportPath + "rptServices.rpt";
reportviewer.ReportPath = rptpath;
reportviewer.Reportds = ds;
reportviewer.Show(this);
}
private void LoadVisitType()
{
var strSQL = "";
var strErr = "";
objData.OpenConnection("AatickPC-Office", ref strErr);
strSQL = "select VisitID,VisitType from dbo_VisitType order by VisitType";
var visitTypes = objData.RetriveData(strSQL, ref strErr);
if (strErr == "")
{
cmbVisitType.DisplayMember = "VisitType";
cmbVisitType.ValueMember = "VisitID";
cmbVisitType.DataSource = visitTypes;
cmbVisitType.SelectedIndex = -1;
}
objData.CloseConnection();
}
private void GetOtherServices(string searchText = "")
{
objdata.OpenConnection("AatickPC-Home", ref strErr);
var strSql = "select OSL_ID, Name from Dbo_Other_Service_List Where Name like '%" + searchText + "%' order by Name";
var otherServiceList = objdata.RetriveData(strSql, ref strErr);
objdata.CloseConnection();
lstserviceinfo.DisplayMember = "Name";
lstserviceinfo.ValueMember = "OSL_ID";
lstserviceinfo.DataSource = otherServiceList;
lstserviceinfo.SelectedIndex = -1;
var textBox = new ArrayList {
txtadmissionid.Name, txtServiceInfo.Name
};
Utility.ClearAllControls(this, textBox);
btnGridDelete.Enabled = false;
}
private void LoadDoctorList(string searchText = "")
{
objData.OpenConnection("AatickPC-Home", ref strErr);
var sql = "USP_Doctor_Search '" + searchText + "'";
var dtDoctors = objData.RetriveData(sql, ref strErr);
objData.CloseConnection();
grdDoctorList.DataSource = dtDoctors;
}
private void getMaxID()
{
//throw new NotImplementedException();
string strSQL = "";
strSQL = "select max(DeptID) from dbo_Department";
DataTable dtMaxID = objData.RetriveData(strSQL, ref strErr);
int DptId = 1;
if (dtMaxID.Rows.Count > 0)
{
if (dtMaxID.Rows[0][0].ToString() != "")
{
DptId = Convert.ToInt16(dtMaxID.Rows[0][0].ToString()) + 1;
}
}
txtDeptID.Text = DptId.ToString();
}
private void LoadList()
{
var strErr = "";
objData.OpenConnection("AatickPC-Office", ref strErr);
var strSql = "SELECT BI_ID,Name,BI_Number BedNumber,Rate FROM Bed_Information ORDER BY Name";
var bedList = objData.RetriveData(strSql, ref strErr);
lstBedInfo.DataSource = bedList;
objData.CloseConnection();
}
private void fillMarketingOfficer()
{
//throw new NotImplementedException();
string strSQL = "";
strSQL = "select E.EmpID, E.Name from dbo_Employee_Profiles E inner join dbo_Employee_Designation D On e.EmpID= D.EmpID inner join dbo_Designation DD On DD.DegId = D.DegID where DD.DeptID = 2 order by E.Name ";
DataTable arrcatList = objData.RetriveData(strSQL, ref strErr);
objData.CloseConnection();
cmbMarktingOfficer.Items.Clear();
if (arrcatList.Rows.Count > 0)
{
for (int i = 0; i < arrcatList.Rows.Count; i++)
{
cmbMarktingOfficer.Items.Add(new hms.Include_Files.Utility.ComboboxItem(arrcatList.Rows[i][1].ToString(), Convert.ToInt32(arrcatList.Rows[i][0].ToString())));
}
}
}
private void LoadBedInfo(string searchText = "")
{
lstBedInfo.Items.Clear();
objData.OpenConnection("AatickPC-Office", ref strErr);
var sqlBedInfo = "SELECT BI.BI_Number Serial,BI.Name Bed,BI.Rate Rent ,'P. Status: '+CASE WHEN (SELECT COUNT(1) FROM Patient_Bed WHERE BI_ID=BI.BI_ID AND isBooked=1)>0 THEN 'Patient Admitted' ELSE 'Free' END Note,CASE WHEN (SELECT COUNT(1) FROM Patient_Bed WHERE BI_ID=BI.BI_ID AND isBooked=1)>0 THEN 1 ELSE 0 END Booked,BI.BI_ID FROM Bed_Information BI"+ (searchText == "" ? "" : " WHERE BI.Name LIKE '%" + searchText + "%'");
var dtBedInfo = objData.RetriveData(sqlBedInfo, ref strErr);
objData.CloseConnection();
for (var i = 0; i < dtBedInfo.Rows.Count; i++)
{
var listitem = new ListViewItem(dtBedInfo.Rows[i][0].ToString());
listitem.SubItems.Add(dtBedInfo.Rows[i][1].ToString());
listitem.SubItems.Add(dtBedInfo.Rows[i][2].ToString());
listitem.SubItems.Add(dtBedInfo.Rows[i][3].ToString());
listitem.ForeColor = dtBedInfo.Rows[i][4].ToString() == "1" ? Color.Red : Color.Blue;
listitem.Tag = dtBedInfo.Rows[i][4] + "#" + dtBedInfo.Rows[i][5];
lstBedInfo.Items.Add(listitem);
}
Utility.AutoResizeColumns(lstBedInfo);
}
private void lstSecond_DoubleClick(object sender, EventArgs e)
{
if (lstSecond.SelectedIndex > -1)
{
objData.OpenConnection("AatickPC-Office", ref strErr);
var sql = "";
if (lstSecond.SelectedValue.ToString() == "1")
{
sql = "USP_Inv_SpotCommissionReport '" + datePicker.Value.ToShortDateString() + "'";
}
else if (lstSecond.SelectedValue.ToString() == "2")
{
sql = "USP_Get_DailyUserTransactionReport '" + datePicker.Value.ToShortDateString() + "','" + datePicker.Value.AddDays(1).ToShortDateString() + "'";
}
var dtReport = objData.RetriveData(sql, ref strErr);
objData.CloseConnection();
if (strErr == "" && dtReport.Rows.Count > 0)
{
var rptpath = Utility.ReportPath;
if (lstSecond.SelectedValue.ToString() == "1")
{
rptpath += "rptDailySpotCommission.rpt";
}
else if (lstSecond.SelectedValue.ToString() == "2")
{
rptpath += "rptHospitalCollection.rpt";
}
var dataset = new DataSet();
dataset.Tables.Add(dtReport);
var reportviewer = new frmReport {
ReportPath = rptpath, Reportds = dataset
};
reportviewer.Show(this);
}
}
}
private void investigationlist()
{
string strSQL = "";
strSQL = "select InvestigationID, cast((Invetigation_SL) as varchar(10)) +'/'+ cast(month(CreatedON) as varchar(2))+'/'+ cast(year(CreatedON) as varchar(4)) as 'Name' from dbo_Investigation order by InvestigationID desc";
var arrInvList = objData.RetriveData(strSQL, ref strErr);
cmbInvestigation.DataSource = arrInvList;
cmbInvestigation.DisplayMember = "Name";
cmbInvestigation.ValueMember = "InvestigationID";
cmbInvestigation.SelectedIndex = -1;
cmbInvestigation.SelectedIndexChanged += cmbInvestigation_SelectedIndexChanged;
}
private void GetDocInfoWithCommission(string admissionSl, int docId = 0, int visitId = 0)
{
var sql = "SELECT CAST(DL.DocID AS VARCHAR)+'@'+CAST(VC.VisitID AS VARCHAR) DocID,DL.Name+' ('+VT.VisitType+')' Name,SUM(VC.Amount*VC.VisitNo) 'Total Amount',AD.Admission_Id,AD.CreatedOn";
sql += " FROM dbo_Admission AD INNER JOIN dbo_Admission_Consultant_Visit_Charge VC ON VC.Admission_ID=AD.Admission_Id";
sql += " INNER JOIN dbo_Doctor_List DL ON DL.DocID=VC.Doc_ID";
sql += " INNER JOIN dbo_VisitType VT ON VT.VisitID=VC.VisitID";
sql += " WHERE VC.PaidOn IS NULL AND AD.Admission_SL=" + admissionSl.Split('/')[0] + " AND MONTH(AD.CreatedOn)=" + admissionSl.Split('/')[1] + " AND YEAR(AD.CreatedOn)=" + admissionSl.Split('/')[2];
if (docId > 0)
{
sql += " AND VC.Doc_ID=" + docId + " AND VC.VisitID=" + visitId;
}
sql += " GROUP BY AD.Admission_Id,DL.DocID,DL.Name,VC.VisitID,VT.VisitType";
var dtDoctorInfo = objData.RetriveData(sql, ref strErr);
if (strErr == "" && dtDoctorInfo.Rows.Count > 0)
{
if (docId == 0)
{
cmbDoctorList.DisplayMember = "Name";
cmbDoctorList.ValueMember = "DocId";
cmbDoctorList.DataSource = dtDoctorInfo;
cmbDoctorList.SelectedIndex = -1;
btnsave.Enabled = false;
txtPayableAmount.ResetText();
int.TryParse(dtDoctorInfo.Rows[0][3].ToString(), out ADMISSION_ID);
}
else
{
txtPayableAmount.Text = dtDoctorInfo.Rows[0][2].ToString();
btnsave.Enabled = true;
}
AdmissionCreationDate = dtDoctorInfo.Rows[0][4].ToString();
}
}
