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
}