private void btFind_Click(object sender, EventArgs e) { System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); ExcData exc = new ExcData(); if (gvPatient.Rows.Count > 0) { gvPatient.DataSource = null; gvPatient.Rows.Clear(); } string DateFrom = dtpDateFrom.Value.ToString("yyyy-MM-dd") + " " + dtpTimeFrom.Value.ToString("HH:mm"); string DateTo = dtpDateTo.Value.ToString("yyyy-MM-dd") + " " + dtpTimeTo.Value.ToString("HH:mm"); string SQL = string.Empty; var strSQL = new StringBuilder(); var clsSQL = new clsSQLNative(); var clsTempData = new clsTempData(); /* SQL = "SELECT [NO] ,HN,Forename as Name , Surname as LastName , DOE , [ChildCompany],[STS],[SyncWhen],'0' IsConvertPreOrder FROM [tblPatientList] WHERE DOE BETWEEN '" + DateFrom + "' AND '" + DateTo + "' "; if (rbAll.Checked) { SQL += "AND STS in ('A','R') "; } else if (rbNotRegister.Checked) { SQL += "AND STS='A' "; } else if (rbRegister.Checked) { SQL += "AND STS='R' "; } if (ddlPayor.SelectedItem.Value.ToString() != "") { SQL += "AND Payor='"+ddlPayor.SelectedItem.Value.ToString()+"' "; } SQL += "ORDER BY SyncWhen,NO"; */ #region SQLQuery strSQL.Append("SELECT "); strSQL.Append("P.NO,"); strSQL.Append("P.HN,"); strSQL.Append("LTRIM(REPLACE(P.Name, P.PreName, ''))Name,"); strSQL.Append("P.LastName,"); strSQL.Append("P.DOE,"); strSQL.Append("PL.ChildCompany,"); strSQL.Append("P.StatusOnMobile STS,"); strSQL.Append("P.SyncWhen,'0' IsConvertPreOrder,"); strSQL.Append("P.rowguid PatientUID "); strSQL.Append("FROM "); strSQL.Append("Patient P "); strSQL.Append("LEFT JOIN tblPatientList PL ON P.rowguid = PL.PatientUID "); if (rbRegister.Checked) { strSQL.Append("INNER JOIN tblCheckList CL ON P.rowguid = CL.PatientUID AND CL.WFID=1 AND (CL.ProStatus='3' OR CL.ProStatus='2') "); } strSQL.Append("WHERE "); strSQL.Append("(P.DOE BETWEEN '"+DateFrom+"' AND '"+DateTo+"') "); strSQL.Append("AND PL.HNStatus='A' "); if (rbAll.Checked) { //SQL += "AND StatusOnMobile in ('A','R') "; } else if (rbNotRegister.Checked) { strSQL.Append("AND StatusOnMobile IS NULL "); } else if (rbRegister.Checked) { strSQL.Append("AND StatusOnMobile='R' "); } if (ddlPayor.SelectedItem.ToString() != "- ทั้งหมด -") { strSQL.Append("AND Payor='" + ddlPayor.SelectedItem.ToString() + "' "); } strSQL.Append("ORDER BY SyncWhen,NO;"); #endregion dtPatient = clsSQL.Bind(strSQL.ToString(), clsSQLNative.DBType.SQLServer, "MobieConnect"); strSQL.Length = 0;strSQL.Capacity = 0; clsTempData.dtIsConverted = null; if(dtPatient!=null && dtPatient.Rows.Count > 0) { try { ddlIsConverted.SelectedIndex = 0; } catch (Exception) { } #region Check IsConvertPreOrder for(int i = 0; i < dtPatient.Rows.Count; i++) { if (clsTempData.IsConverted( dtPatient.Rows[i]["Name"].ToString(), dtPatient.Rows[i]["LastName"].ToString(), dtPatient.Rows[i]["DOE"].ToString(), DateFrom,DateTo)) { dtPatient.Rows[i]["IsConvertPreOrder"] = "1"; } } dtPatient.AcceptChanges(); #endregion bs.DataSource = dtPatient; gvPatient.DataSource = bs; gvPatient.Columns["No"].Width = 20; gvPatient.Columns["HN"].Width = 100; gvPatient.Columns["Name"].Width = 100; gvPatient.Columns["LastName"].Width = 100; gvPatient.Columns["DOE"].Width = 130; gvPatient.Columns["NO"].Width = 40; gvPatient.Columns["ChildCompany"].Width = 170; gvPatient.Columns["STS"].Width = 40; gvPatient.Columns["SyncWhen"].Width = 130; gvPatient.Columns["IsConvertPreOrder"].IsVisible = false; gvPatient.Columns["PatientUID"].IsVisible = false; gvPatient.Refresh(); lblCountPT.Text = dtPatient.Rows.Count.ToString() + " Record."; lblIsConvertCount.Text = dtPatient.Rows.Count.ToString(); CheckAll(); } else { MessageBox.Show("ไม่พบข้อมูลตามเงื่อนไขที่ต้องการ", "No data.", MessageBoxButtons.OK, MessageBoxIcon.Information); lblCountPT.Text = "0 Record."; bs.DataSource = null; gvPatient.DataSource = bs; gvPatient.Refresh(); } }
private void Find() { //if (ddlIsConverted.InvokeRequired) //{ // ddlIsConverted.Invoke(new MethodInvoker(delegate // { // ddlIsConverted.SelectedIndex = 0; // })); //} //else //{ // ddlIsConverted.SelectedIndex = 0; //} setRadButton(btFind, false); setPictureBox(pbCountPT, true); setLabel(lblCountPT, ""); System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); ExcData exc = new ExcData(); if (gvPatient.InvokeRequired) { gvPatient.Invoke(new MethodInvoker(delegate { if (gvPatient.Rows.Count > 0) { gvPatient.DataSource = null; gvPatient.Rows.Clear(); } })); } else { if (gvPatient.Rows.Count > 0) { gvPatient.DataSource = null; gvPatient.Rows.Clear(); } } string DateFrom = dtpDateFrom.Value.ToString("yyyy-MM-dd") + " " + dtpTimeFrom.Value.ToString("HH:mm"); string DateTo = dtpDateTo.Value.ToString("yyyy-MM-dd") + " " + dtpTimeTo.Value.ToString("HH:mm"); string SQL = string.Empty; var strSQL = new StringBuilder(); var clsSQL = new clsSQLNative(); var clsTempData = new clsTempData(); var payor = ""; #region SQLQuery strSQL.Append("SELECT "); strSQL.Append("P.NO,"); strSQL.Append("P.HN,"); strSQL.Append("LTRIM(REPLACE(P.Name, P.PreName, ''))Name,"); strSQL.Append("P.LastName,"); strSQL.Append("P.DOE,"); strSQL.Append("P.Payor,"); strSQL.Append("PL.ChildCompany,"); strSQL.Append("P.StatusOnMobile STS,"); strSQL.Append("CL.RegDate RegisterDate,"); strSQL.Append("P.rowguid PatientUID,"); strSQL.Append("P.SyncWhen,'0' IsConvertPreOrder "); strSQL.Append("FROM "); strSQL.Append("Patient P "); strSQL.Append("INNER JOIN tblCheckList CL ON P.rowguid = CL.PatientUID AND WFID = 1 "); if (rbRegister.Checked) { strSQL.Append("AND (CL.ProStatus='2' OR CL.ProStatus='3') "); } strSQL.Append("LEFT JOIN tblPatientList PL ON P.rowguid = PL.PatientUID "); strSQL.Append("WHERE "); strSQL.Append("(CL.RegDate BETWEEN '" + DateFrom + "' AND '" + DateTo + "') "); if (rbAll.Checked) { //SQL += "AND StatusOnMobile in ('A','R') "; } else if (rbNotRegister.Checked) { strSQL.Append("AND StatusOnMobile IS NULL "); } else if (rbRegister.Checked) { strSQL.Append("AND StatusOnMobile='R' "); } if (ddlPayor.InvokeRequired) { ddlPayor.Invoke(new MethodInvoker(delegate { if (ddlPayor.SelectedItem.ToString() != "- ทั้งหมด -") { payor = ddlPayor.SelectedItem.ToString(); payorChoose = payor; strSQL.Append("AND Payor='" + payor + "' "); } })); } else { if (ddlPayor.SelectedItem.ToString() != "- ทั้งหมด -") { payor = ddlPayor.SelectedItem.ToString(); payorChoose = payor; strSQL.Append("AND Payor='" + payor + "' "); } } strSQL.Append("ORDER BY "); //strSQL.Append("CL.RegDate ASC;"); strSQL.Append("DOE DESC;"); #endregion dtPatient = clsSQL.Bind(strSQL.ToString(), clsSQLNative.DBType.SQLServer, "MobieConnect"); strSQL.Length = 0; strSQL.Capacity = 0; clsTempData.dtIsConverted = null; if (dtPatient != null && dtPatient.Rows.Count > 0) { #region Find Min&Max DOE var col = dtPatient.Columns["DOE"]; var minDOE = dtPatient.AsEnumerable() .Select(cols => cols.Field<DateTime>(col.ColumnName)) .OrderBy(p => p.Ticks) .FirstOrDefault(); var maxDOE = dtPatient.AsEnumerable() .Select(cols => cols.Field<DateTime>(col.ColumnName)) .OrderByDescending(p => p.Ticks) .FirstOrDefault(); #endregion try { //setComboBox(ddlIsConverted, 0); } catch (Exception) { } #region Check IsConvertPreOrder for (int i = 0; i < dtPatient.Rows.Count; i++) { if (clsTempData.IsConverted( dtPatient.Rows[i]["Name"].ToString(), dtPatient.Rows[i]["LastName"].ToString(), dtPatient.Rows[i]["DOE"].ToString(), minDOE.ToString("yyyy-MM-dd HH:mm"), maxDOE.ToString("yyyy-MM-dd HH:mm"))) { dtPatient.Rows[i]["IsConvertPreOrder"] = "1"; } } dtPatient.AcceptChanges(); #endregion #region SearchIsConvertFilter /* if (ddlIsConverted.InvokeRequired) { ddlIsConverted.Invoke(new MethodInvoker(delegate { if (ddlIsConverted.SelectedIndex > 0) { DataView dv = dtPatient.DefaultView; if (ddlIsConverted.SelectedItem.ToString() == "เฉพาะที่ยังไม่ Convert") { dv.RowFilter = "IsConvertPreOrder=0"; } else if(ddlIsConverted.SelectedItem.ToString() == "เฉพาะที่ Convert แล้ว") { dv.RowFilter = "IsConvertPreOrder=1"; } dtPatient = dv.ToTable(); } })); } */ #endregion if (gvPatient.InvokeRequired) { gvPatient.Invoke(new MethodInvoker(delegate { bs.DataSource = dtPatient; gvPatient.DataSource = bs; gvPatient.Columns["No"].Width = 20; gvPatient.Columns["HN"].Width = 100; gvPatient.Columns["Name"].Width = 100; gvPatient.Columns["LastName"].Width = 100; gvPatient.Columns["DOE"].Width = 130; gvPatient.Columns["NO"].Width = 40; gvPatient.Columns["Payor"].Width = 170; gvPatient.Columns["ChildCompany"].Width = 170; gvPatient.Columns["STS"].Width = 40; gvPatient.Columns["RegisterDate"].Width = 130; gvPatient.Columns["SyncWhen"].Width = 130; gvPatient.Columns["IsConvertPreOrder"].IsVisible = false; gvPatient.Columns["PatientUID"].IsVisible = false; gvPatient.Refresh(); })); } else { bs.DataSource = dtPatient; gvPatient.DataSource = bs; gvPatient.Columns["No"].Width = 20; gvPatient.Columns["HN"].Width = 100; gvPatient.Columns["Name"].Width = 100; gvPatient.Columns["LastName"].Width = 100; gvPatient.Columns["DOE"].Width = 130; gvPatient.Columns["NO"].Width = 40; gvPatient.Columns["Payor"].Width = 170; gvPatient.Columns["ChildCompany"].Width = 170; gvPatient.Columns["STS"].Width = 40; gvPatient.Columns["RegisterDate"].Width = 130; gvPatient.Columns["SyncWhen"].Width = 130; gvPatient.Columns["IsConvertPreOrder"].IsVisible = false; gvPatient.Columns["PatientUID"].IsVisible = false; gvPatient.Refresh(); } setLabel(lblCountPT, dtPatient.Rows.Count.ToString() + " Record."); setLabel(lblIsConvertCount, string.Format("พบข้อมูลตรงเงื่อนไข {0} รายการ", dtPatient.Rows.Count.ToString())); CheckAll(); IsConvertFilter(); } else { MessageBox.Show("ไม่พบข้อมูลตามเงื่อนไขที่ต้องการ", "No data.", MessageBoxButtons.OK, MessageBoxIcon.Information); setLabel(lblCountPT, "0 Record."); bs.DataSource = null; if (gvPatient.InvokeRequired) { gvPatient.Invoke(new MethodInvoker(delegate { gvPatient.DataSource = bs; gvPatient.Refresh(); })); } else { gvPatient.DataSource = bs; gvPatient.Refresh(); } } setPictureBox(pbCountPT, false); setRadButton(btFind, true); }
private void getPatient() { System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); #region Variable var dt = new DataTable(); var clsTempData = new clsTempData(); var mobileStatus = ""; #endregion #region Procedure if (rbAll.Checked) { mobileStatus = "All"; } else if (rbNotRegister.Checked) { mobileStatus = "NotRegister"; } else if (rbRegister.Checked) { mobileStatus = "Register"; } dt = clsTempData.getPatient(dtDOEFrom.Value,dtDOETo.Value,getPayor(),mobileStatus); clsTempData.dtIsConverted = null; if (dt!=null && dt.Rows.Count > 0) { #region Check IsConvertPreOrder for (int i = 0; i < dt.Rows.Count; i++) { if (clsTempData.IsConverted( dt.Rows[i]["Name"].ToString().Replace(dt.Rows[i]["PreName"].ToString(),""), dt.Rows[i]["LastName"].ToString(), dt.Rows[i]["DOE"].ToString(), dtDOEFrom.Value.ToString("yyyy-MM-dd HH:mm"), dtDOETo.Value.ToString("yyyy-MM-dd HH:mm"))) { dt.Rows[i]["IsConvertPreOrder"] = "1"; } } dt.AcceptChanges(); #endregion lblSearchResult.Text = string.Format("พบข้อมูลทั้งหมด {0} รายการ",dt.Rows.Count.ToString()); gvPatient.DataSource = dt; gvPatient.Columns["PreName"].Visible = false; gvPatient.Columns["IsConvertPreOrder"].Visible = false; } else { lblSearchResult.Text = string.Format("ไม่พบข้อมูลที่ต้องการ"); gvPatient.DataSource = null; } #endregion }