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 ShowMoneyReceipt(string cashMemoId, bool isEdit = false)
{
objData.OpenConnection("AatickPC-Office", ref strErr);
var sqlCashMemoInfo = "SELECT CAST(CM.CM_SL AS VARCHAR(10))+'/'+CAST(MONTH(CM.CreatedOn) AS VARCHAR(2))+'/'+CAST(YEAR(CM.CreatedOn) AS VARCHAR(4)) AS CashMemoNo";
sqlCashMemoInfo += " , CAST(AD.Admission_SL AS VARCHAR(10))+'/'+CAST(MONTH(AD.CreatedOn) AS VARCHAR(2))+'/'+CAST(YEAR(AD.CreatedOn) AS VARCHAR(4)) AS AdmissionSlNo";
sqlCashMemoInfo += " , AD.CreatedOn,PI.Name,PI.Year,PI.Month,PI.Day,PI.Gender";
sqlCashMemoInfo += " , CM.Pay_Amount,case when AD.DiscountTk is null then 0 else AD.DiscountTk end DiscountTk,case when Ad.DiscountPercentage is null then 0 else Ad.DiscountPercentage end DiscountPercentage,AD.DiscountByID,dbo.f_Get_Admission_PayableAmount(AD.Admission_Id) Payable_Amount, case when CM.Return_Amount is null then 0 else CM.Return_Amount end Return_Amount,U.Name UserName ";
sqlCashMemoInfo +=
" FROM dbo_CashMemo CM INNER JOIN dbo_Admission AD ON CM.Admission_ID=AD.Admission_Id ";
sqlCashMemoInfo += " INNER JOIN dbo_Patient_info PI ON PI.P_ID=AD.P_ID ";
sqlCashMemoInfo += " LEFT JOIN dbo_Users U ON U.User_Id=CM.UserId ";
sqlCashMemoInfo += " WHERE CM.CM_ID=" + cashMemoId;
var dtCashMemoInfo = objData.RetriveData(sqlCashMemoInfo, ref strErr);
objData.CloseConnection();
var rptpath = Utility.ReportPath + "rptMoneyReceipt.rpt";
if (isEdit)
{
var dataset = new DataSet();
dataset.Tables.Add(dtCashMemoInfo);
var reportviewer = new frmReport();
reportviewer.ReportPath = rptpath;
reportviewer.Reportds = dataset;
reportviewer.Show(this);
}
else
{
Utility.Print(dtCashMemoInfo, rptpath, 1);
}
}
private void ShowReport()
{
if (dgvDueCollection.SelectedCells.Count > 0)
{
var cashId = dgvDueCollection.Rows[dgvDueCollection.SelectedCells[0].RowIndex].Cells[14].Value;
objData.OpenConnection("AatickPC-Office", ref strErr);
var sqlCashMemoInfo = "SELECT CAST(CM.CM_SL AS VARCHAR(10))+'/'+CAST(MONTH(CM.CreatedOn) AS VARCHAR(2))+'/'+CAST(YEAR(CM.CreatedOn) AS VARCHAR(4)) AS CashMemoNo";
sqlCashMemoInfo += " , CAST(Inv.Invetigation_SL AS VARCHAR(10))+'/'+CAST(MONTH(Inv.CreatedOn) AS VARCHAR(2))+'/'+CAST(YEAR(Inv.CreatedOn) AS VARCHAR(4)) AS InvestigationSlNo";
sqlCashMemoInfo += " , Inv.CreatedOn,PI.Name,PI.Year,PI.Month,PI.Day,PI.Gender";
sqlCashMemoInfo +=
" , CM.Pay_Amount,Inv.DiscountTK,Inv.DiscountPasentance,Inv.DiscountByID,dbo.f_Get_Payable_Amount(Inv.InvestigationID) Payable_Amount,U.Name UserName";
sqlCashMemoInfo +=
" FROM dbo_CashMemo CM INNER JOIN dbo_Investigation Inv ON CM.InvetigationID=Inv.InvestigationID";
sqlCashMemoInfo += " INNER JOIN dbo_Patient_info PI ON PI.P_ID=Inv.P_ID ";
sqlCashMemoInfo += " LEFT JOIN dbo_Users U ON U.User_Id=CM.UserId ";
sqlCashMemoInfo += " WHERE CM.CM_ID=" + cashId;
var dtCashMemoInfo = objData.RetriveData(sqlCashMemoInfo, ref strErr);
objData.CloseConnection();
var dataset = new DataSet();
dataset.Tables.Add(dtCashMemoInfo);
var reportviewer = new frmReport();
var rptpath = Utility.ReportPath + "rptDueMoneyReceipt.rpt";
reportviewer.ReportPath = rptpath;
reportviewer.Reportds = dataset;
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 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)
{
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 ShowReport(string admissionSl, bool isAdmission)
{
strErr = "";
objData.OpenConnection("AatickPC-Office", ref strErr);
var strSQL = "";
if (isAdmission)
{
strSQL = "SELECT Top 1 CAST(Admission_SL AS VARCHAR(20))+'/'+CAST(Month(AD.CreatedOn) AS VARCHAR(20))+'/'+CAST(Year(AD.CreatedOn) AS VARCHAR(20)) Admission_SL";
strSQL += " ,BI.Name Bed_Name,PI.Name,AD.CreatedOn,PI.Year,PI.Month,PI.Day,PI.Gender";
strSQL += " ,DL.Title+' '+DL.Name+','+DL.Degree+','+DL.Specialist+',Phone: '+dl.Phone DoctorInfo";
strSQL += " ,PO.ForH_Name,PO.Mother_Name,PI.PresentAddress,PO.Parmanent_address,PO.Region,PO.Occupation";
strSQL += " ,PO.Guardian,PO.Guardian_Relation,po.Gurdian_Address,PI.Phone";
strSQL += " ,ADO.Provisional_Diagnosis,ADO.ListOfDiagnosis,PB.Booked_On,U.Name UserName,BI.BI_Number BedNo";
strSQL +=
" FROM dbo_Admission AD INNER JOIN dbo_Admission_other_info ADO ON ADO.Admission_Id=AD.Admission_Id ";
strSQL += " INNER JOIN dbo_Patient_info PI ON PI.P_ID=AD.P_ID ";
strSQL += " INNER JOIN dbo_Patient_other_info PO ON PO.P_ID=AD.P_ID ";
strSQL += " LEFT JOIN Patient_Bed PB ON PB.Admission_id=AD.Admission_Id";
strSQL += " LEFT JOIN Bed_Information BI ON BI.BI_ID=PB.BI_ID";
strSQL += " LEFT JOIN dbo_Users U ON U.User_Id=AD.UserId";
strSQL += " LEFT JOIN dbo_Doctor_List DL ON DL.DocID=AD.Under_Doc_ID WHERE AD.Admission_SL=" + admissionSl.Split('/')[0] + " AND MONTH(AD.CreatedOn)=" + admissionSl.Split('/')[1] + " AND YEAR(AD.CreatedOn)=" + admissionSl.Split('/')[2] + " order by PB.Booked_On desc";
}
else
{
strSQL = "USP_HospitalConsultantBill_Summery null,'" + admissionSl + "'";
}
var dtReport = objData.RetriveData(strSQL, ref strErr);
objData.CloseConnection();
if (strErr == "" && dtReport.Rows.Count > 0)
{
if (isAdmission)
{
var rptpath = Utility.ReportPath + "rptAdmissionForm.rpt";
var dataset = new DataSet();
dataset.Tables.Add(dtReport);
var reportviewer = new frmReport {
ReportPath = rptpath, Reportds = dataset
};
reportviewer.Show(this);
}
else
{
var rptpath = Utility.ReportPath + "rptHospitalBill.rpt";
var report = new ReportDocument();
report.Load(rptpath);
report.SetDataSource(dtReport);
report.SetParameterValue("UserName", Utility.UserName);
var reportviewer = new frmReport(report);
reportviewer.Show(this);
}
}
}
private void lstFirst_DoubleClick(object sender, EventArgs e)
{
if (lstFirst.SelectedIndex > -1)
{
objData.OpenConnection("AatickPC-Office", ref strErr);
var sql = "";
if (lstFirst.SelectedValue.ToString() == "2")
{
sql = "rpt_USP_inv_Monthly_Report '" + dateFrom.Value.ToShortDateString() + "','" + dateTo.Value.AddDays(1).ToShortDateString() + "'";
}
else if (lstFirst.SelectedValue.ToString() == "1")
{
sql = "rpt_USP_Hospital_Monthly_Collection_summery '" + dateFrom.Value.ToShortDateString() + "','" + dateTo.Value.AddDays(0).ToShortDateString() + "'";
}
else if (lstFirst.SelectedValue.ToString() == "3")
{
sql = "rpt_USP_Marketing_Total_sales_Commission '" + dateFrom.Value.ToShortDateString() + "','" + dateTo.Value.AddDays(1).ToShortDateString() + "'";
}
var dtReport = objData.RetriveData(sql, ref strErr);
objData.CloseConnection();
if (strErr == "" && dtReport.Rows.Count > 0)
{
var rptpath = Utility.ReportPath;
if (lstFirst.SelectedValue.ToString() == "2")
{
rptpath += "rptMonthlyCollectionDiagnostic.rpt";
}
else if (lstFirst.SelectedValue.ToString() == "1")
{
rptpath += "rptHospitalMonthlySummary.rpt";
}
else if (lstFirst.SelectedValue.ToString() == "3")
{
rptpath += "rptMonthlyTotalsalesComissionInvest.rpt";
}
var dataset = new DataSet();
dataset.Tables.Add(dtReport);
var reportviewer = new frmReport {
ReportPath = rptpath, Reportds = dataset
};
reportviewer.Show(this);
}
}
}
private void btnSpotCommission_Click(object sender, EventArgs e)
{
var dt = txtDatePicker.Value.ToShortDateString();
objData.OpenConnection("AatickPC-Office", ref strErr);
var sql = "USP_daywise_doctor_commission_paid_list '" + dt + "'";
var dtReport = objData.RetriveData(sql, ref strErr);
objData.CloseConnection();
if (strErr == "" && dtReport.Rows.Count > 0)
{
var reportDoc = new ReportDocument();
var rptpath = Utility.ReportPath + "rptPaidDoctorCommissionList.rpt";
reportDoc.Load(rptpath);
reportDoc.SetDataSource(dtReport);
reportDoc.SetParameterValue("printedBy", Utility.UserName);
var reportviewer = new frmReport(reportDoc);
reportviewer.Show(this);
}
}
private void btnSpotCommission_Click(object sender, EventArgs e)
{
objData.OpenConnection("AatickPC-Office", ref strErr);
var sql = "";
sql = "USP_Inv_SpotCommissionList '" + txtDatePicker.Value.ToShortDateString() + "'";
var dtReport = objData.RetriveData(sql, ref strErr);
objData.CloseConnection();
if (strErr == "" && dtReport.Rows.Count > 0)
{
var rptpath = Utility.ReportPath + "rptSpotCommissionList.rpt";
var dataset = new DataSet();
dataset.Tables.Add(dtReport);
var reportviewer = new frmReport {
ReportPath = rptpath, Reportds = dataset
};
reportviewer.Show(this);
}
}
private void btnSpotCommission_Click(object sender, EventArgs e)
{
objData.OpenConnection("AatickPC-Office", ref strErr);
var sql = "USP_rpt_paid_visit_charge_list '" + txtDatePicker.Value.ToShortDateString() + "'";
var dtReport = objData.RetriveData(sql, ref strErr);
objData.CloseConnection();
if (strErr == "" && dtReport.Rows.Count > 0)
{
var rptpath = Utility.ReportPath + "rptPaidSpotVisitList.rpt";
var report = new ReportDocument();
report.Load(rptpath);
report.SetDataSource(dtReport);
report.SetParameterValue("printedBy", Utility.UserName);
var reportviewer = new frmReport(report);
reportviewer.Show(this);
}
else
{
MessageBox.Show(@"No data found in this date.");
}
}
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 ShowAdmissionReport(string admission_id, bool isEdit = false)
{
var strErr = "";
objData.OpenConnection("AatickPC-Office", ref strErr);
var strSQL = "SELECT Top 1 CAST(Admission_SL AS VARCHAR(20))+'/'+CAST(Month(AD.CreatedOn) AS VARCHAR(20))+'/'+CAST(Year(AD.CreatedOn) AS VARCHAR(20)) Admission_SL";
strSQL += " ,BI.Name Bed_Name,PI.Name,AD.CreatedOn,PI.Year,PI.Month,PI.Day,PI.Gender";
strSQL += " ,DL.Title+' '+DL.Name+','+DL.Degree+','+DL.Specialist+',Phone: '+dl.Phone DoctorInfo";
strSQL += " ,PO.ForH_Name,PO.Mother_Name,PI.PresentAddress,PO.Parmanent_address,PO.Region,PO.Occupation";
strSQL += " ,PO.Guardian,PO.Guardian_Relation,po.Gurdian_Address,PI.Phone";
strSQL += " ,ADO.Provisional_Diagnosis,ADO.ListOfDiagnosis,PB.Booked_On,U.Name UserName,BI.BI_Number BedNo,AD.Reference_Doc_ID RefDoc";
strSQL +=
" FROM dbo_Admission AD INNER JOIN dbo_Admission_other_info ADO ON ADO.Admission_Id=AD.Admission_Id ";
strSQL += " INNER JOIN dbo_Patient_info PI ON PI.P_ID=AD.P_ID ";
strSQL += " INNER JOIN dbo_Patient_other_info PO ON PO.P_ID=AD.P_ID ";
strSQL += " LEFT JOIN Patient_Bed PB ON PB.Admission_id=AD.Admission_Id";
strSQL += " LEFT JOIN Bed_Information BI ON BI.BI_ID=PB.BI_ID";
strSQL += " LEFT JOIN dbo_Users U ON U.User_Id=AD.UserId";
strSQL += " LEFT JOIN dbo_Doctor_List DL ON DL.DocID=AD.Under_Doc_ID WHERE AD.Admission_Id=" + admission_id + " order by PB.Booked_On desc";
var patientInfo = objData.RetriveData(strSQL, ref strErr);
objData.CloseConnection();
var dataset = new DataSet();
dataset.Tables.Add(patientInfo);
var reportviewer = new frmReport();
var rptpath = Utility.ReportPath + "rptAdmissionForm.rpt";
reportviewer.ReportPath = rptpath;
reportviewer.Reportds = dataset;
reportviewer.Show(this);
if (!isEdit)
{
Utility.Print(patientInfo, rptpath, 2);
}
}
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_Admission_AdmitedList "; //'" + datePicker.Value.ToShortDateString() + "','" + datePicker.Value.AddDays(1).ToShortDateString() + "'";
}
else if (lstSecond.SelectedValue.ToString() == "2")
{
sql = "USP_Admission_DischageList '" + 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 += "rptAdmittedPatientList.rpt";
}
else if (lstSecond.SelectedValue.ToString() == "2")
{
rptpath += "rptDischargedPatientList.rpt";
}
var dataset = new DataSet();
dataset.Tables.Add(dtReport);
var reportviewer = new frmReport {
ReportPath = rptpath, Reportds = dataset
};
reportviewer.Show(this);
}
}
}
private void btndailyladger_Click(object sender, EventArgs e)
{
objData.OpenConnection("AatickPC-Office", ref strErr);
var intUserID = Utility.userID;
if (intUserID == 2 || intUserID == 1 || intUserID == 3 || intUserID == 7 || intUserID == 9)
{
intUserID = -1;
}
var sql = "USP_Inv_Daily_ledger " + intUserID;
var dtReport = objData.RetriveData(sql, ref strErr);
objData.CloseConnection();
var dataset = new DataSet();
dataset.Tables.Add(dtReport);
var reportviewer = new frmReport();
var rptpath = Utility.ReportPath + "rptDailyLedger.rpt";
reportviewer.ReportPath = rptpath;
reportviewer.Reportds = dataset;
reportviewer.Show(this);
}
private void lstFirst_DoubleClick(object sender, EventArgs e)
{
if (lstFirst.SelectedIndex > -1)
{
objData.OpenConnection("AatickPC-Office", ref strErr);
var sql = "";
if (lstFirst.SelectedValue.ToString() == "1")
{
sql = "USP_Admission_DischageList '" + dateFrom.Value.ToShortDateString() + "','" + dateTo.Value.AddDays(1).ToShortDateString() + "'";
}
else if (lstFirst.SelectedValue.ToString() == "2")
{
sql = "rpt_USP_Hospital_Monthly_Collection '" + dateFrom.Value.ToShortDateString() + "','" + dateTo.Value.AddDays(0).ToShortDateString() + "'";
}
else if (lstFirst.SelectedValue.ToString() == "3")
{
sql = "rpt_USP_Hospital_Marketing_Total_sales_Commission '" + dateFrom.Value.ToShortDateString() + "','" + dateTo.Value.AddDays(0).ToShortDateString() + "'";
}
var dtReport = objData.RetriveData(sql, ref strErr);
objData.CloseConnection();
if (strErr == "" && dtReport.Rows.Count > 0)
{
var rptpath = Utility.ReportPath;
if (lstFirst.SelectedValue.ToString() == "1")
{
rptpath += "rptDischargedPatientList.rpt";
}
else if (lstFirst.SelectedValue.ToString() == "2")
{
rptpath += "rptHospitalMonthlyCollection.rpt";
}
else if (lstFirst.SelectedValue.ToString() == "3")
{
rptpath += "rptAPTotalSalesCommistionMarketing.rpt";
}
var dataset = new DataSet();
dataset.Tables.Add(dtReport);
var reportviewer = new frmReport {
ReportPath = rptpath, Reportds = dataset
};
reportviewer.Show(this);
}
}
//if (lstFirst.SelectedIndex > -2)
//{
// objData.OpenConnection("AatickPC-Office", ref strErr);
// var sql = "";
// if (lstFirst.SelectedValue.ToString() == "2")
// sql = "rpt_USP_Hospital_Monthly_Collection '" + dateFrom.Value.ToShortDateString() + "','" + dateTo.Value.AddDays(1).ToShortDateString() + "'";
// var dtReport = objData.RetriveData(sql, ref strErr);
// objData.CloseConnection();
// if (strErr == "" && dtReport.Rows.Count > 0)
// {
// var rptpath = Utility.ReportPath;
// if (lstFirst.SelectedValue.ToString() == "2")
// rptpath += "rptHospitalMonthlyCollection.rpt";
// var dataset = new DataSet();
// dataset.Tables.Add(dtReport);
// var reportviewer = new frmReport { ReportPath = rptpath, Reportds = dataset };
// reportviewer.Show(this);
// }
//}
}