private void btnsave_Click(object sender, EventArgs e)
{
//MessageBox.Show(dgvRetrurn.Rows[0].Cells[0].Value.ToString());
//MessageBox.Show(dgvRetrurn.Rows[1].Cells[17].Value.ToString());
objData = new C_Data_Batch();
objData.OpenConnection("AzharPC-Home", ref strErr);
string strSQL = "";
objData.BeginTransaction(ref strErr);
for (int i = 0; i < dgvRetrurn.Rows.Count - 1; i++)
{
if (Convert.ToInt16(dgvRetrurn.Rows[i].Cells[3].Value.ToString()) > 0)
{
strSQL = "update dbo_InvertigationServiceItem set isreturn =1, return_on = getdate(), Return_Amount = " + dgvRetrurn.Rows[i].Cells[3].Value + ", ReturnUserID =" + Utility.userID + " , ReturnUserPC='" + Utility.PcName + "' where ISI_ID=" + dgvRetrurn.Rows[i].Cells[17].Value;
objData.ExecuteQuery(strSQL, ref strErr);
}
}
if (strErr == "")
{
string strSqlSpotCommission = "USP_Inv_Calculate_SpotCommission " + txtInvestigationID.Text.Split('/')[0] + "," + txtInvestigationID.Text.Split('/')[1] + "," + txtInvestigationID.Text.Split('/')[2];
objData.ExecuteQuery(strSqlSpotCommission, ref strErr);
MessageBox.Show("Investigation return succefully done.");
objData.CommitTransaction(ref strErr);
}
else
{
MessageBox.Show("Investigation not return.");
objData.Rollback(ref strErr);
}
}
private void btnSane_Click(object sender, EventArgs e)
{
var strSql = "";
//var strSqlInvItem = "";
DataTable arrData;
objData.OpenConnection("AzharPC-Office", ref strErr);
objData.BeginTransaction(ref strErr);
for (int i = 0; i < dgvPathologyLabProcess.Rows.Count - 1; i++)
{
if (strErr == "")
{
if (dgvPathologyLabProcess.Rows[i].Cells[4].Value.ToString() != "")
{
strSql = "select ID From InvertigationServiceItemwise_testResult Where SIT_ID =" + dgvPathologyLabProcess.Rows[i].Cells[8].Value + " and InvestigationID =" + dgvPathologyLabProcess.Rows[i].Cells[9].Value;
arrData = objData.RetriveData(strSql, ref strErr);
if (arrData.Rows.Count > 0)
{
strSql = "update InvertigationServiceItemwise_testResult set Result= '" + dgvPathologyLabProcess.Rows[i].Cells[4].Value + "' Where SIT_ID =" + dgvPathologyLabProcess.Rows[i].Cells[8].Value + " and InvestigationID =" + dgvPathologyLabProcess.Rows[i].Cells[9].Value;
//MessageBox.Show(strSql);
objData.ExecuteQuery(strSql, ref strErr);
}
else
{
strSql = "Insert into InvertigationServiceItemwise_testResult (SIT_ID,InvestigationID, Result) values(" + dgvPathologyLabProcess.Rows[i].Cells[8].Value + "," + dgvPathologyLabProcess.Rows[i].Cells[9].Value + ",'" + dgvPathologyLabProcess.Rows[i].Cells[4].Value + "')";
//MessageBox.Show(strSql);
objData.ExecuteQuery(strSql, ref strErr);
}
}
}
}
if (strErr == "")
{
objData.CommitTransaction(ref strErr);
}
else
{
objData.Rollback(ref strErr);
}
if (strErr == "")
{
fillGdv();
//loadReport();
}
objData.CloseConnection();
}
private void btnDeliver_Click(object sender, EventArgs e)
{
var tD = 0;
if (lbltotaldue.Text == "")
{
tD = 0;
}
else
{
tD = Convert.ToInt16(lbltotaldue.Text.ToString());
if (tD > 0)
{
MessageBox.Show("Please paid the due collection");
}
else
{
string strSqlUpdateItems = "";
string strSqlInsertHistory = "";
string strIDs = "";
if (Convert.ToInt32(dgvFontDeskReport.Rows.Count) - 1 > 0)
{
for (int i = 0; i < dgvFontDeskReport.Rows.Count - 1; i++)
{
if ((bool)dgvFontDeskReport.Rows[i].Cells[0].FormattedValue == true)
{
if (strIDs.ToString() == "")
{
strIDs = dgvFontDeskReport.Rows[i].Cells[8].Value.ToString();
strSqlInsertHistory = " (" + dgvFontDeskReport.Rows[i].Cells[8].Value.ToString() + ", 6," + hms.Include_Files.Utility.userID + ",'" + Utility.PcName + "')";
}
else
{
strIDs = strIDs + ", " + dgvFontDeskReport.Rows[i].Cells[8].Value.ToString();
strSqlInsertHistory = strSqlInsertHistory + ", (" + dgvFontDeskReport.Rows[i].Cells[8].Value.ToString() + ", 6," + hms.Include_Files.Utility.userID + ",'" + Utility.PcName + "')";
}
}
}
}
if (strIDs != "")
{
strSqlUpdateItems = "Update dbo_InvertigationServiceItem SET Status=6, Delivered= getdate() where [ISI_ID] in (" + strIDs + ")";
strSqlInsertHistory = "insert into Investigation_Serviceitem_History (ISI_ID, Status, UserID,userPC) values " + strSqlInsertHistory;
//objData = new C_Data_Batch();
objData.OpenConnection("AzharPC-Home", ref strErr);
objData.BeginTransaction(ref strErr);
if (strErr == "")
{
objData.ExecuteQuery(strSqlUpdateItems, ref strErr);
if (strErr == "")
{
objData.ExecuteQuery(strSqlInsertHistory, ref strErr);
if (strErr == "")
{
MessageBox.Show("Report Delivery succefully done.");
objData.CommitTransaction(ref strErr);
frmLoad();
}
else
{
objData.Rollback(ref strErr);
MessageBox.Show("System cannot deal at this moment.");
}
}
else
{
objData.Rollback(ref strErr);
MessageBox.Show("System cannot deal at this moment.");
}
}
}
}
}
}
private void SaveData()
{
var invid = cmbInvestigation.SelectedValue.ToString();
var DincountByID = cmbDiscount.SelectedIndex > -1 ? int.Parse(cmbDiscount.SelectedValue.ToString()) : 0;
var invSL = cmbInvestigation.Text;
string strSQLUpdateInvertigation = "";
string strSQLUpdateCashMemo = "select max(CM_SL) From dbo_CashMemo where month(CreatedOn) = month(getdate()) ";
DataTable arrCashSL = objData.RetriveData(strSQLUpdateCashMemo, ref strErr);
int cashSL = 0;
if (arrCashSL.Rows.Count > 0)
{
if (arrCashSL.Rows[0][0].ToString() == "")
{
cashSL = 1000;
}
else
{
cashSL = Convert.ToInt16(arrCashSL.Rows[0][0].ToString()) + 1;
}
}
else
{
cashSL = 1000;
}
string strSQLCASH = "";
int paidAmount = 0;
if (txtpaidamount.ToString() == "")
{
paidAmount = 0;
}
else
{
paidAmount = Convert.ToInt16(txtpaidamount.Text);
}
int totalDueAmount = 0;
if (txtremainingdue.Text == "")
{
totalDueAmount = 0;
}
else
{
totalDueAmount = Convert.ToInt16(txtremainingdue.Text);
}
var totalDiscountTake = 0;
if (txtdiscount.Text != "")
{
totalDiscountTake = Convert.ToInt32(txtdiscount.Text);
}
var totalDiscountParsentence = 0;
if (txtpersentance.Text != "")
{
totalDiscountParsentence = Convert.ToInt32(txtpersentance.Text);
}
objData.BeginTransaction(ref strErr);
strSQLUpdateInvertigation = "update dbo_Investigation SET TotalDue = " + txtremainingdue.Text + " ,DiscountTK=" + totalDiscountTake + ", DiscountPasentance=" + totalDiscountParsentence + ", DiscountByID =" + DincountByID + ", UpdatedOn = getdate() where InvestigationID =" + invid;
objData.ExecuteQuery(strSQLUpdateInvertigation, ref strErr);
if (strErr == "")
{
var strDueCollection = "insert into dbo_Inv_Due_Collection (investigationID, Paid_Amount, Paid_Type, UserId, UserPC) ";
strDueCollection += " Values (" + invid + ", " + paidAmount + ", 'DI'," + hms.Include_Files.Utility.userID + ", '" + hms.Include_Files.Utility.PcName + "' )";
objData.ExecuteQuery(strDueCollection, ref strErr);
if (strErr == "")
{
strSQLCASH = "insert into [dbo_CashMemo]([CM_SL], [InvetigationID], [Pay_Amount], [Due], USERID,UserPC) ";
strSQLCASH = strSQLCASH + "Values (" + cashSL + ", " + invid + ", " + paidAmount + ", " + totalDueAmount + ", " + hms.Include_Files.Utility.userID + ", '" + hms.Include_Files.Utility.PcName + "')";
objData.ExecuteQuery(strSQLCASH, ref strErr);
if (strErr == "")
{
string strSqlSpotCommission = "USP_Inv_Calculate_SpotCommission " + cmbInvestigation.Text.Split('/')[0] + "," + cmbInvestigation.Text.Split('/')[1] + "," + cmbInvestigation.Text.Split('/')[2];
objData.ExecuteQuery(strSqlSpotCommission, ref strErr);
MessageBox.Show("Due Collection succefullty done");
objData.CommitTransaction(ref strErr);
string strSQL = "";
strSQL = "USP_INV_DUE_Collection " + invid;
DataTable arrAllInfo = objData.RetriveData(strSQL, ref strErr);
DataSet ds = new DataSet();
ds.Tables.Add(arrAllInfo);
dgvDueCollection.DataSource = ds.Tables["Table1"];
dgvDueCollection.Columns["Cash Memo"].DisplayIndex = 0;
dgvDueCollection.Columns["Invertigation"].DisplayIndex = 1;
//dgvDueCollection.Columns["Pay_Amount"].DisplayIndex = 2;
var maxCashMemo = "select max(CM_ID) from dbo_CashMemo ";
var arrCashmemo = objData.RetriveData(maxCashMemo, ref strErr);
var cashId = 0;
if (arrCashmemo.Rows.Count > 0)
{
cashId = Convert.ToInt32(arrCashmemo.Rows[0][0].ToString());
}
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);
DataSet dscsh = new DataSet();
dscsh.Tables.Add(dtCashMemoInfo);
var rptpath = Utility.ReportPath + "rptDueMoneyReceipt.rpt";
var reportDoc = new ReportDocument();
reportDoc.Load(rptpath);
reportDoc.SetDataSource(dscsh.Tables[0]);
//ReportDoc.SetParameterValue("UserName", Utility.UserName);
Utility.Print(reportDoc, rptpath, 2);
}
else
{
MessageBox.Show("cash memo not creted.");
objData.Rollback(ref strErr);
}
}
else
{
MessageBox.Show("investigation not updated.");
objData.Rollback(ref strErr);
}
}
else
{
MessageBox.Show("investigation not updated.");
objData.Rollback(ref strErr);
}
}
private void insertData()
{
//throw new NotImplementedException();
objetdata = new C_Data_Batch();
string strErr = "";
hms.Include_Files.Utility.ComboboxItem Department = cmbDepartment.SelectedItem as hms.Include_Files.Utility.ComboboxItem;
hms.Include_Files.Utility.ComboboxItem Designation = cmbDesignation.SelectedItem as hms.Include_Files.Utility.ComboboxItem;
if (Department != null && Designation != null)
{
var ValDepartment = Department.Value.ToString();
var ValDesignation = Designation.Value.ToString();
//var DeptID = 0;
objetdata.OpenConnection("PolinPC-Office", ref strErr);
string strSQL = "";
var Gender = "M";
if (txtgender.Text.ToString() == "F")
{
Gender = "F";
}
var IsCommession = 0;
if (chkIsCommession.Checked == true)
{
IsCommession = 1;
}
var isDoc = 0;
if (chkDoctor.Checked == true)
{
isDoc = 1;
}
var valDocID = 0;
if (isDoc.ToString() == "1")
{
valDocID = Convert.ToInt32(txtDoctorId.Text.ToString());
}
var isparmanent = 0;
if (chkParmanent.Checked == true)
{
isparmanent = 1;
}
strSQL = "Insert into dbo_Employee_Profiles ([Name],[PresentAddress],[Gender],[BloodGroup],";
strSQL = strSQL + "[JoiningDate],[Phone],[DateOfBirth],[ParmanentAddress],[IsDoctor],[IsCommession],[IsEmployment],[FatherName],[MotherName], [DocID]) ";
strSQL = strSQL + " values ('" + txtEmployeeName.Text.ToString() + "','" + rtbPresentAddress.Text + "','" + Gender + "','" + txtBloodGroup.Text + "',";
strSQL = strSQL + "'" + dtpJoinDate.Text + "','" + txtMobileNo.Text + "','" + dtpDateOfBirth.Text + "',";
strSQL = strSQL + "'" + txtParmanentAddress.Text + "'," + isDoc + "," + IsCommession + "," + isparmanent + ",'" + txtFatherName.Text + "','" + txtMotherName.Text + "', " + valDocID + ")";
objetdata.BeginTransaction(ref strErr);
objetdata.ExecuteQuery(strSQL, ref strErr);
if (strErr == "")
{
strSQL = "Select Max(EmpID) From dbo_Employee_Profiles";
var arr = objetdata.RetriveData(strSQL, ref strErr);
var EmpId = 1;
if (arr.Rows[0][0].ToString() != "")
{
EmpId = Convert.ToInt32(arr.Rows[0][0].ToString());
strSQL = "Insert into dbo_Employee_Designation (EmpID, DegID) values (" + EmpId.ToString() + ", " + ValDesignation + ")";
objetdata.ExecuteQuery(strSQL, ref strErr);
if (strErr == "")
{
strSQL = "Insert into dbo_Employee_Salary(EmpID, Basic, HouseRent, Medical, Others, InActive) ";
strSQL = strSQL + " Values(" + EmpId.ToString() + ", " + txtBasic.Text + ", " + txthouse.Text + ", " + txtmedical.Text + ", " + txtother.Text + ", 1)";
objetdata.ExecuteQuery(strSQL, ref strErr);
if (strErr == "")
{
objetdata.CommitTransaction(ref strErr);
MessageBox.Show("Employe Insert succefully done.");
clearFields();
getEmployeeID();
fillDepartment();
ShowdgvEmpprofile();
}
else
{
objetdata.Rollback(ref strErr);
MessageBox.Show("Employe Not Insert");
}
}
else
{
objetdata.Rollback(ref strErr);
MessageBox.Show("Employe Not Insert");
}
}
else
{
objetdata.Rollback(ref strErr);
MessageBox.Show("Employe Not Insert");
}
}
else
{
objetdata.Rollback(ref strErr);
MessageBox.Show("Employe Not Insert");
}
}
else
{
MessageBox.Show("Please select Department or Designation");
}
}
private void SaveAdmissionInfo()
{
var userid = Utility.userID;
var patientName = txtPatientName.Text;
int d, m, y;
int.TryParse(txtdd.Text, out d);
int.TryParse(txtmm.Text, out m);
int.TryParse(txtyyy.Text, out y);
if (y == 0 && m == 0 && d == 0)
{
MessageBox.Show("Please enter age.");
txtyyy.Focus();
return;
}
var day = d.ToString();
var month = m.ToString();
var year = y.ToString();
var gender = txtgender.SelectedValue;
var isFathertemp = rbtnfathername.Checked;
var isHusbandtemp = rbtnhusbandname.Checked;
var txtFatherOrHusband = txtFatherOrHusbandname.Text;
var mothername = txtMotherName.Text;
var religion = txtReligion.SelectedValue;
var occupation = txtoccupation.Text;
var guardian = txtguardian.Text;
var guardianAddress = txtGuardianAddress.Text;
var patientPresentAddress = txtpresentaddress.Text;
var patientParmanentAddress = txtpermanetaddress.Text;
var patientPhone = txtphone.Text;
var dtbirthDate = DateTime.Now.AddYears(int.Parse("-" + year)).AddMonths(int.Parse("-" + month)).AddDays(int.Parse("-" + day));
var isFather = isHusbandtemp ? 0 : 1;
var hospitalFee = 0;
int.TryParse(txtFee.Text, out hospitalFee);
var advance = txtadvance.Text == "" ? "0" : txtadvance.Text;
var selectedBed = lstBedInfo.CurrentRow;
var strErr = "";
objData.OpenConnection("AatickPC-Office", ref strErr);
objData.BeginTransaction(ref strErr);
var p_id = 0;
//var sqlpatientSl = "SELECT '" + dtbirthDate.ToString("yyyyMMdd") + "'+ CASE WHEN MAX(P_ID) IS NULL THEN '1' ELSE CAST((MAX(P_ID)+1) AS VARCHAR(20)) END FROM dbo_patient_info WHERE CreatedOn>='" + new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).ToShortDateString() + "'";
//var dtpatientSl = objData.RetriveData(sqlpatientSl, ref strErr);
var insertPatientinfo = "Insert into dbo_patient_info(Name,PresentAddress,Phone,Year,Month,Day,Gender,BirthDate,UserID,UserPC) values";
insertPatientinfo += "('" + patientName + "','" + patientPresentAddress + "','" + patientPhone + "'," + year + "," + month + "," + day + ",'" + gender + "','" + dtbirthDate + "'," + userid + ",'" + Utility.PcName + "')";
objData.ExecuteQuery(insertPatientinfo, ref strErr);
var intAdmissionId = "";
var maxCashId = "";
if (strErr == "")
{
var sqlSelectPatientId = "select max(p_id) from dbo_patient_info";
var dtSelectPatientId = objData.RetriveData(sqlSelectPatientId, ref strErr);
if (strErr == "")
{
p_id = int.Parse(dtSelectPatientId.Rows[0][0].ToString());
var sqlInsertPatientOtherInfo = "Insert into dbo_Patient_other_info(P_ID,IsFather,ForH_Name,Region,Occupation,Guardian,Parmanent_address,Mother_Name,Gurdian_Address,Guardian_Relation) values";
sqlInsertPatientOtherInfo += "(" + p_id + "," + isFather + ",'" + txtFatherOrHusband + "','" +
religion + "','" + occupation + "','" + guardian + "','" +
patientParmanentAddress + "','" + mothername + "','" + guardianAddress + "','" + txtRelation.Text + "')";
objData.ExecuteQuery(sqlInsertPatientOtherInfo, ref strErr);
if (strErr == "")
{
var refDocid = txtdoctorid.Text;
var underDocid = txtadmissonunder.Text;
var sqladmissionSl = "SELECT CASE WHEN MAX(Admission_SL) IS NULL THEN 1001 ELSE MAX(Admission_SL)+1 END FROM dbo_Admission WHERE CreatedOn>='" + new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).ToShortDateString() + "'";
var dtAddmissionSl = objData.RetriveData(sqladmissionSl, ref strErr);
if (strErr == "")
{
var insertAddmissioninfosql = "INSERT INTO dbo_Admission(P_ID,Reference_Doc_ID,Under_Doc_ID,Admission_SL,UserID,UserPC,Hospital_Fee) VALUES(" + p_id + "," + refDocid + "," + underDocid + "," + dtAddmissionSl.Rows[0][0] + "," + Utility.userID + ",'" + Utility.PcName + "'," + hospitalFee + ")";
objData.ExecuteQuery(insertAddmissioninfosql, ref strErr);
if (strErr == "")
{
var sqlSelectAdmissionId = "select max(Admission_id) from dbo_Admission";
var dtSelectAdmissionId = objData.RetriveData(sqlSelectAdmissionId, ref strErr);
if (strErr == "")
{
intAdmissionId = dtSelectAdmissionId.Rows[0][0].ToString();
var insertAdmissionOtherinfosql =
"INSERT INTO dbo_Admission_other_info(Admission_Id,Provisional_Diagnosis,ListOfDiagnosis) VALUES(" +
dtSelectAdmissionId.Rows[0][0] + ",'" + txtprovisionsis.Text +
"','" + txtlistofdocument.Text + "')";
objData.ExecuteQuery(insertAdmissionOtherinfosql, ref strErr);
if (strErr == "")
{
var insertBedinfosql = "INSERT INTO Patient_Bed(BI_ID,Admission_id,Booked_On,isBooked,Note,UserID,UserPC) VALUES(" + selectedBed.Cells[0].Value + "," + dtSelectAdmissionId.Rows[0][0] + ",getdate(),1,'" + selectedBed.Cells[5].Value + "'," + Utility.userID + ",'" + Utility.PcName + "')";
objData.ExecuteQuery(insertBedinfosql, ref strErr);
if (strErr == "")
{
if (!string.IsNullOrEmpty(advance))
{
var insertDepositinfosql =
"INSERT INTO Hospital_Deposit(Admission_id,Deposit_amount,Deposit_On,UserID,UserPC,Deposite_Type) VALUES(" +
dtSelectAdmissionId.Rows[0][0] + ",'" + advance + "',getdate()," + Utility.userID + ",'" + Utility.PcName + "','A')";
objData.ExecuteQuery(insertDepositinfosql, ref strErr);
if (strErr == "")
{
var sqlcashSl =
"SELECT CASE WHEN MAX(CM_SL) IS NULL THEN 1001 ELSE MAX(CM_SL)+1 END FROM dbo_CashMemo WHERE CreatedOn>='" +
new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1)
.ToShortDateString() + "'";
var dtACashSl = objData.RetriveData(sqlcashSl, ref strErr);
if (strErr == "")
{
var insertCashMemosql =
"INSERT INTO dbo_CashMemo(Admission_ID,CM_SL,Pay_Amount,USERID,UserPC) VALUES(" +
dtSelectAdmissionId.Rows[0][0] + "," + dtACashSl.Rows[0][0] +
"," + advance + "," + userid + ",'" + Utility.PcName + "')";
objData.ExecuteQuery(insertCashMemosql, ref strErr);
var selectCashMaxId = "SELECT MAX(CM_ID) FROM dbo_CashMemo;";
var arrMaxCashId = objData.RetriveData(selectCashMaxId, ref strErr);
maxCashId = arrMaxCashId.Rows[0][0].ToString();
}
}
}
}
}
}
}
}
}
}
}
var msg = "";
if (strErr != "")
{
objData.Rollback(ref strErr);
msg = "An Error occured.\nTry again later.";
}
else
{
objData.CommitTransaction(ref strErr);
msg = @"Successfully admitted";
}
objData.CloseConnection();
if (strErr == "")
{
SetNewEnv();
}
MessageBox.Show(msg);
if (strErr == "")
{
ShowAdmissionReport(intAdmissionId);
PrintMoneyReceipt(maxCashId);
}
btnpreview.Enabled = true;
}
private void SaveData()
{
//throw new NotImplementedException();
string strSqlUpdateItems = "";
string strSqlInsertHistory = "";
string strIDs = "";
var strMsg = "";
hms.Include_Files.Utility.ComboboxItem LabData = lbPathlab.SelectedItem as hms.Include_Files.Utility.ComboboxItem;
var ReportId = 2;
if (LabData != null)
{
ReportId = Convert.ToInt16(LabData.Value.ToString()) + 1;
}
if (Convert.ToInt32(dgvPathLab.Rows.Count) - 1 > 0)
{
for (int i = 0; i < dgvPathLab.Rows.Count - 1; i++)
{
if ((bool)dgvPathLab.Rows[i].Cells[0].FormattedValue == true)
{
if (strIDs.ToString() == "")
{
strIDs = dgvPathLab.Rows[i].Cells[9].Value.ToString();
strSqlInsertHistory = " (" + dgvPathLab.Rows[i].Cells[9].Value.ToString() + ", " + ReportId + ", " + hms.Include_Files.Utility.userID + ",'" + Utility.PcName + "')";
}
else
{
strIDs = strIDs + ", " + dgvPathLab.Rows[i].Cells[9].Value.ToString();
strSqlInsertHistory = strSqlInsertHistory + ", (" + dgvPathLab.Rows[i].Cells[9].Value.ToString() + ", " + ReportId + ", " + hms.Include_Files.Utility.userID + ",'" + Utility.PcName + "')";
}
}
}
}
if (strIDs.ToString() != "")
{
strSqlUpdateItems = "Update dbo_InvertigationServiceItem SET Status=" + ReportId + " where [ISI_ID] in (" + strIDs.ToString() + ")";
strSqlInsertHistory = "insert into Investigation_Serviceitem_History (ISI_ID, Status, UserID,userPC) values " + strSqlInsertHistory;
objData = new C_Data_Batch();
objData.OpenConnection("AzharPC-Home", ref strErr);
objData.BeginTransaction(ref strErr);
if (strErr.ToString() == "")
{
objData.ExecuteQuery(strSqlUpdateItems, ref strErr);
if (strErr.ToString() == "")
{
objData.ExecuteQuery(strSqlInsertHistory, ref strErr);
if (strErr.ToString() == "")
{
//MessageBox.Show("sample collection saved succefully done.");
hms.Include_Files.Utility.ComboboxItem LabDataMsg = lbPathlab.SelectedItem as hms.Include_Files.Utility.ComboboxItem;
strMsg = "Test Complete";
if (LabDataMsg != null)
{
strMsg = LabDataMsg.Name;
}
strMsg = strMsg + " saved succefully done.";
MessageBox.Show(strMsg);
objData.CommitTransaction(ref strErr);
frmLoad();
}
else
{
objData.Rollback(ref strErr);
MessageBox.Show("System cannot deal at this moment.");
}
}
else
{
objData.Rollback(ref strErr);
MessageBox.Show("System cannot deal at this moment.");
}
}
}
}
private void btnsave_Click(object sender, EventArgs e)
{
var addmissionId = txtAdmissionId.Text;
var payAmount = 0;
int.TryParse(txtdepositamount.Text, out payAmount);
if (payAmount == 0)
{
MessageBox.Show(@"Please enter deposit amount.");
txtdepositamount.Focus();
return;
}
if (!string.IsNullOrEmpty(txtdiscount.Text) || !string.IsNullOrEmpty(txtpersentance.Text))
{
if (lstDiscountType.SelectedItems.Count == 0)
{
MessageBox.Show(@"Please select discount type.");
return;
}
if (lstdiscountby.SelectedItems.Count == 0)
{
MessageBox.Show(@"Please select discount by.");
return;
}
}
var dueAmount = txtremainingdue.Text;
var userId = Utility.userID;
var cashMemoSl = "";
var cashId = "";
strErr = "";
var msg = "";
objData.OpenConnection("AatickPC-Office", ref strErr);
var sqlCashMemoSl =
"SELECT CASE WHEN MAX(CM_SL) IS NULL THEN 1001 ELSE MAX(CM_SL)+1 END FROM dbo_CashMemo WHERE CreatedOn>='" +
new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).ToShortDateString() + "'";
var dtCashMemoSl = objData.RetriveData(sqlCashMemoSl, ref strErr);
cashMemoSl = dtCashMemoSl.Rows[0][0].ToString();
if (txtReturnAmount.Visible && !string.IsNullOrEmpty(txtReturnAmount.Text))
{
var insertReturnsql = "INSERT INTO dbo_CashMemo(CM_SL,Admission_ID,USERID,Return_Amount,UserPC) VALUES('" +
cashMemoSl + "'," + addmissionId + "," + userId + ",'" + txtReturnAmount.Text +
"','" + Utility.PcName + "')";
objData.ExecuteQuery(insertReturnsql, ref strErr);
if (strErr == "")
{
var maxCashMemoSql = "SELECT MAX(CM_ID) FROM dbo_CashMemo;";
var arrCashId = objData.RetriveData(maxCashMemoSql, ref strErr);
if (strErr == "")
{
cashId = arrCashId.Rows[0][0].ToString();
}
}
msg = strErr != "" ? "An error occured.\nPlease try again later." : "Successfully saved.";
}
else
{
objData.BeginTransaction(ref strErr);
var insertDepositinfosql =
"INSERT INTO Hospital_Deposit(Admission_id,Deposit_amount,Deposit_On,UserPC,Deposite_Type,UserID) VALUES(" + addmissionId + ",'" +
payAmount + "',getdate(),'" + Utility.PcName + "','AD'," + Utility.userID + ")";
objData.ExecuteQuery(insertDepositinfosql, ref strErr);
if (strErr == "")
{
var insertCashinfosql =
"INSERT INTO dbo_CashMemo(CM_SL,Admission_ID,Pay_Amount,Due,USERID,Remarks,UserPC) VALUES('" +
cashMemoSl + "'," + addmissionId + ",'" + payAmount + "','" + dueAmount + "'," + userId +
",'" + txtremarks.Text + "','" + Utility.PcName + "')";
objData.ExecuteQuery(insertCashinfosql, ref strErr);
if (strErr == "")
{
var maxCashMemoSql = "SELECT MAX(CM_ID) FROM dbo_CashMemo;";
var arrCashId = objData.RetriveData(maxCashMemoSql, ref strErr);
if (strErr == "")
{
cashId = arrCashId.Rows[0][0].ToString();
if (!string.IsNullOrEmpty(txtdiscount.Text) || !string.IsNullOrEmpty(txtpersentance.Text))
{
var sql =
"INSERT INTO dbo_Admission_Discount(Admission_Id,Discount_Amount,Discount_Percentage,Discount_Type,DiscountBy_Id,UserId,UserPc) VALUES(" +
addmissionId + ",'" + GetDiscountAmount() + "','" + (string.IsNullOrEmpty(txtpersentance.Text) ? "0" : txtpersentance.Text) + "','" + lstDiscountType.SelectedValue +
"','" + txtdiscountby.Text + "'," + Utility.userID + ",'" + Utility.PcName + "')";
objData.ExecuteQuery(sql, ref strErr);
var updateAdmissioninfosql = "UPDATE dbo_Admission SET DiscountByID='" +
txtdiscountby.Text +
"', DiscountTK=ISNULL(DiscountTK,0)+" + GetDiscountAmount() + ",DiscountPercentage='0',UserId=" + Utility.userID + ",UserPc='" + Utility.PcName + "' WHERE Admission_Id=" + addmissionId;
objData.ExecuteQuery(updateAdmissioninfosql, ref strErr);
}
}
}
}
if (strErr != "")
{
objData.Rollback(ref strErr);
msg = "An error occured.\nPlease try again later.";
}
else
{
objData.CommitTransaction(ref strErr);
msg = "Successfully saved.";
}
}
objData.CloseConnection();
MessageBox.Show(msg);
if (strErr == "")
{
LoadAddmissionInfo(addmissionId);
ShowMoneyReceipt(cashId);
}
}
private void saveData()
{
if (txtYear.Text.Trim() == "")
{
MessageBox.Show("Please enter year!");
return;
}
int year;
int.TryParse(txtYear.Text, out year);
int month;
int.TryParse(txtMonth.Text, out month);
DateTime thisDay = DateTime.Today;
Int64 DateDiff = Convert.ToInt64(((year * 12) + month) * 30);
DateTime totalDays = thisDay.AddDays(-DateDiff);
string birthDate = totalDays.Date.ToShortDateString();
string strYear = totalDays.Year.ToString();
string strmonth = "";
if (totalDays.Month < 10)
{
strmonth = "0" + totalDays.Month;;
}
else
{
strmonth = totalDays.Month.ToString();
}
string strDay = "";
if (totalDays.Day < 10)
{
strDay = "0" + totalDays.Day;
}
else
{
strDay = totalDays.Day.ToString();
}
int intInvestigationType = 0;
if (rbtngenaral.Checked == true)
{
intInvestigationType = 1;
}
else if (rbtnopd.Checked == true)
{
intInvestigationType = 2;
}
else
{
intInvestigationType = 1;
}
int intInvestigationDeptType = 0;
if (rbtnpathology.Checked == true)
{
intInvestigationDeptType = 1;
}
else if (rbtndental.Checked == true)
{
intInvestigationDeptType = 2;
}
else if (rbtnbloodbank.Checked == true)
{
intInvestigationDeptType = 3;
}
else if (rbtndialysis.Checked == true)
{
intInvestigationDeptType = 4;
}
else
{
intInvestigationDeptType = 1;
}
var isUpdate = (rbtnindoor.Checked && !string.IsNullOrEmpty(txtAdmissionSl.Text.Trim())) ? true : false;
objData.BeginTransaction(ref strErr);
string strPatSql = "";
strPatSql = "INSERT INTO [dbo_Patient_info]([Name],[PresentAddress],[Phone],[Year],[Month],[Day],[Gender],[BirthDate], UserID,UserPC) ";
strPatSql = strPatSql + "VALUES('" + txtname.Text + "','" + txtaddress.Text + "','" + txtphone.Text;
strPatSql = strPatSql + "'," + year + "," + month + ",0,'" + txtsex.SelectedValue + "','" + birthDate + "', " + Utility.userID + ",'" + Utility.PcName + "')";
if (isUpdate)
{
strPatSql = "UPDATE dbo_Patient_info SET Name='" + txtname.Text + "',PresentAddress='" + txtaddress.Text + "',Phone='" + txtphone.Text + "',Year=" + txtYear.Text + ",Month=" + txtMonth.Text + ",Gender='" + txtsex.SelectedValue + "',BirthDate='" + birthDate + "',UserID=" + Utility.userID + " WHERE P_ID=" + txtid.Text + ";";
}
objData.ExecuteQuery(strPatSql, ref strErr);
if (strErr == "")
{
string strInvFndSL = "";
strInvFndSL = "select case when max(Invetigation_SL) is null then 1001 else max(Invetigation_SL)+1 end From dbo_Investigation where month(CreatedON) = month(getdate()) AND YEAR(CreatedON) = YEAR(getdate()) ";
DataTable arrMaxInvSL = objData.RetriveData(strInvFndSL, ref strErr);
if (strErr == "")
{
int maxInvSlL = 0;
maxInvSlL = Convert.ToInt32(arrMaxInvSL.Rows[0][0].ToString());
string strP_ID = "select CASE WHEN max(p_ID) IS NULL THEN 0 ELSE max(p_ID) END From dbo_Patient_info ";
DataTable arrp_ID = objData.RetriveData(strP_ID, ref strErr);
int p_IDval = 0;
p_IDval = Convert.ToInt32(arrp_ID.Rows[0][0].ToString());
if (isUpdate)
{
p_IDval = Convert.ToInt32(txtid.Text);
}
var pc = txtpc.Text;
if (pc.Trim() == "")
{
pc = "0";
}
var disciuntBy = lstDiscount.SelectedIndex == -1 ? "0" : lstDiscount.SelectedValue.ToString();
string strInvSql = "";
strInvSql = "INSERT INTO [dbo_Investigation]([DocID],[DiscountTK],[DiscountPasentance],[TotalDue],[InvestigationType],[InvestigationDeptType]";
strInvSql = strInvSql + " ,[P_ID],[Invetigation_SL],[PC_CC], [userID],[UserPC], DiscountByID" + (isUpdate ? ",[Admission_id]" : "") + ")";
strInvSql = strInvSql + " Values (" + txtdoctorid.Text + ", " + txtdiscount.Text + ", " + txtdiscouuntpersentnance.Text + ", " + txttotaldue.Text;
strInvSql = strInvSql + ", " + intInvestigationType + ", " + intInvestigationDeptType + ", " + p_IDval + ", " + maxInvSlL + ", " + pc + "," + Utility.userID + ",'" + Utility.PcName + "'," + disciuntBy + (isUpdate ? "," + lblAdmissionId.Text : "") + " )";
objData.ExecuteQuery(strInvSql, ref strErr);
if (strErr == "")
{
string strMaxInvID = "";
strMaxInvID = "select max(Investigationid) from dbo_Investigation";
DataTable arrMaxInvID = objData.RetriveData(strMaxInvID, ref strErr);
if (strErr == "")
{
int maxInvID = 0;
if (arrMaxInvID.Rows.Count > 0)
{
if (arrMaxInvID.Rows[0][0].ToString() == "")
{
maxInvID = 0;
}
else
{
maxInvID = Convert.ToInt32(arrMaxInvID.Rows[0][0].ToString());
}
}
else
{
maxInvID = 0;
}
string strSqlInvItem = "";
for (int i = 0; i < dgvInvestigation.Rows.Count; i++)
{
if (strSqlInvItem == "")
{
strSqlInvItem = " (" + maxInvID + ", " + dgvInvestigation.Rows[i].Cells[1].Value + ",getdate(), 0," + Utility.userID + ", " + dgvInvestigation.Rows[i].Cells[3].Value + ",'" + dgvInvestigation.Rows[i].Cells[4].Value + "' ,'" + dgvInvestigation.Rows[i].Cells[5].Value + "','" + Utility.PcName + "' )";
}
else
{
strSqlInvItem = strSqlInvItem + ", (" + maxInvID + ", " + dgvInvestigation.Rows[i].Cells[1].Value + ",getdate(), 0," + Utility.userID + ", " + dgvInvestigation.Rows[i].Cells[3].Value + ",'" + dgvInvestigation.Rows[i].Cells[4].Value + "' ,'" + dgvInvestigation.Rows[i].Cells[5].Value + "','" + Utility.PcName + "' )";
}
}
if (strSqlInvItem != "")
{
string strSqlInvItemMain = "INSERT INTO [dbo_InvertigationServiceItem]([Investigationid],[Service_ID],[Date],[Status],[UserID],Amount, DeliveryOn, Remaks,UserPC) Values " + strSqlInvItem;
objData.ExecuteQuery(strSqlInvItemMain, ref strErr);
if (strErr == "")
{
string strSqlSpotCommission = "USP_Inv_Calculate_SpotCommission " + maxInvSlL + "," + DateTime.Today.Month + "," + DateTime.Today.Year;
objData.ExecuteQuery(strSqlSpotCommission, ref strErr);
getPrint(maxInvID.ToString());
objData.CommitTransaction(ref strErr);
frmload();
objData.CloseConnection();
}
else
{
objData.Rollback(ref strErr);
}
}
}
else
{
objData.Rollback(ref strErr);
}
}
else
{
objData.Rollback(ref strErr);
}
}
else
{
objData.Rollback(ref strErr);
}
}
else
{
objData.Rollback(ref strErr);
}
}
private void saveData()
{
//throw new NotImplementedException();
string strSqlUpdateItems = "";
string strSqlInsertHistory = "";
string strIDs = "";
if (Convert.ToInt32(dgvSampleCollection.Rows.Count) - 1 > 0)
{
for (int i = 0; i < dgvSampleCollection.Rows.Count - 1; i++)
{
if ((bool)dgvSampleCollection.Rows[i].Cells[0].FormattedValue == true)
{
if (strIDs.ToString() == "")
{
strIDs = dgvSampleCollection.Rows[i].Cells[9].Value.ToString();
strSqlInsertHistory = " (" + dgvSampleCollection.Rows[i].Cells[9].Value.ToString() + ", 1," + hms.Include_Files.Utility.userID + ",'" + Utility.PcName + "')";
}
else
{
strIDs = strIDs + ", " + dgvSampleCollection.Rows[i].Cells[9].Value.ToString();
strSqlInsertHistory = strSqlInsertHistory + ", (" + dgvSampleCollection.Rows[i].Cells[9].Value.ToString() + ", 1," + hms.Include_Files.Utility.userID + ",'" + Utility.PcName + "')";
}
}
}
}
if (strIDs.ToString() != "")
{
strSqlUpdateItems = "Update dbo_InvertigationServiceItem SET Status=1 where [ISI_ID] in (" + strIDs.ToString() + ")";
strSqlInsertHistory = "insert into Investigation_Serviceitem_History (ISI_ID, Status, UserID,userPC) values " + strSqlInsertHistory;
objData = new C_Data_Batch();
objData.OpenConnection("AzharPC-Home", ref strErr);
objData.BeginTransaction(ref strErr);
if (strErr.ToString() == "")
{
objData.ExecuteQuery(strSqlUpdateItems, ref strErr);
if (strErr.ToString() == "")
{
objData.ExecuteQuery(strSqlInsertHistory, ref strErr);
if (strErr.ToString() == "")
{
MessageBox.Show("sample collection saved succefully done.");
objData.CommitTransaction(ref strErr);
frmLoad();
}
else
{
objData.Rollback(ref strErr);
MessageBox.Show("System cannot deal at this moment.");
}
}
else
{
objData.Rollback(ref strErr);
MessageBox.Show("System cannot deal at this moment.");
}
}
}
}
