/// <summary>
/// 获取平台配置的sql
/// </summary>
/// <returns>返回sql列表</returns>
public void GetConfigSQL()
{
string strSQL = string.Format("select * from pt_sql a,pt_tables_dict b where a.table_id = b.id and b.exportflag ='TRUE' ");
m_dsSQL = DALUse.Query(strSQL);
RemoteMessage.SendMessage("===加载配置语句\nSQL：" + strSQL);
}
public void FindCompare()
{
sb.Length = 0;
sb.Append('%');
string sql = "";
if (rb_field.Checked && rb_mohu.Checked)
{
foreach (char c in gridView3.GetDataRow(gridView3.FocusedRowHandle)["field"].ToString().ToCharArray())
{
sb.Append(c);
sb.Append('%');
}
sql = string.Format("select * from PT_LOCAL_DICT where pt_id = '{0}' and type_name = '{1}'and field like'{2}'", cmb_pt_name.SelectedValue.ToString(), cmb_local.SelectedValue.ToString(), sb.ToString());
}
else if (rb_field.Checked && rb_yange.Checked)
{
sql = string.Format("select * from PT_LOCAL_DICT where pt_id = '{0}' and type_name = '{1}'and field ='{2}'", cmb_pt_name.SelectedValue.ToString(), cmb_local.SelectedValue.ToString(), gridView3.GetDataRow(gridView3.FocusedRowHandle)["field"].ToString());
}
else if (rb_code.Checked && rb_mohu.Checked)
{
foreach (char c in gridView3.GetDataRow(gridView3.FocusedRowHandle)["field_code"].ToString().ToCharArray())
{
sb.Append(c);
sb.Append('%');
}
sql = string.Format("select * from PT_LOCAL_DICT where pt_id = '{0}' and type_name = '{1}'and field_code like'{2}'", cmb_pt_name.SelectedValue.ToString(), cmb_local.SelectedValue.ToString(), sb.ToString());
}
else if (rb_code.Checked && rb_yange.Checked)
{
sql = string.Format("select * from PT_LOCAL_DICT where pt_id = '{0}' and type_name = '{1}'and field_code = '{2}'", cmb_pt_name.SelectedValue.ToString(), cmb_local.SelectedValue.ToString(), gridView3.GetDataRow(gridView3.FocusedRowHandle)["field_code"].ToString());
}
gc_local.DataSource = DALUse.Query(sql).Tables[0];
}
/// <summary>
/// 通过pt_id获取需要转换的字典
/// </summary>
/// <param name="pt_id">平台id</param>
/// <returns>字典集信息</returns>
public static void GetPtCompareDictDetail()
{
PublicVar.CompareDict.Tables.Clear();
DataSet _dsType = GetPtCompareDict(PublicVar.Pt_Id);
try
{
foreach (DataRow _dr in _dsType.Tables[0].Rows)
{
DataTable _dt = new DataTable(_dr["type_name"].ToString());
string strSQL = string.Format("select * from pt_comparison_detail_dict t where pt_id ='{0}' and type_name = '{1}'", PublicVar.Pt_Id, _dr["type_name"].ToString());
DataSet _ds = DALUse.Query(strSQL);
if (_ds.Tables.Count > 0)
{
_dt = _ds.Tables[0].Copy();
_dt.TableName = _dr["type_name"].ToString();
}
PublicVar.CompareDict.Tables.Add(_dt);
}
}
catch (Exception exp1)
{
CommonFunction.WriteError(exp1.ToString());
}
}
/// <summary>
/// 每当修改，删除，保存操作后进行刷新操作，跟新实时数据。
/// </summary>
public void RefreshContent()
{
string sql = string.Format("select * from pt_target_field where table_name ='{0}' and pt_id ='{1}'", gridView1.GetDataRow(gridView1.FocusedRowHandle)["TABLE_NAME"].ToString(), ((DataRowView)cmb_ptname.SelectedItem).Row["pt_id"].ToString());
dttemp = DALUse.Query(sql).Tables[0];
gc_pt_target.DataSource = dttemp;
}
private void gridView1_Click(object sender, EventArgs e)
{
try
{
needfix = false;
if (dttabledetail.Rows.Count != 0)
{
DataSet dsconnstr = DALUse.Query(string.Format("select * from PT_Setting where pt_id ='{0}'", cmb_ptname.SelectedValue.ToString()));
if (dsconnstr.Tables.Count > 0 && dsconnstr.Tables[0].Rows.Count > 0)
{
connstr = dsconnstr.Tables[0].Rows[0]["connstr"].ToString();
}
string sqltemp = string.Format("select * from pt_target_field where table_name ='{0}' and pt_id = '{1}'", gridView1.GetDataRow(gridView1.FocusedRowHandle)["TABLE_NAME"].ToString(), ((DataRowView)cmb_ptname.SelectedItem).Row["pt_id"].ToString());
string sqltarget = string.Format("select * from {0} where 1=0 ", gridView1.GetDataRow(gridView1.FocusedRowHandle)["TABLE_NAME"].ToString());
DataSet dstarget = DALUseSpecial.Query(sqltarget, connstr);
if (dstarget.Tables.Count > 0)
{
dttarget = dstarget.Tables[0];
}
DataSet dstemp = DALUse.Query(sqltemp);
if (dstemp.Tables.Count > 0)
{
dttemp = dstemp.Tables[0];
}
CheckTargetDict(dttemp, dttarget);
gc_pt_target.DataSource = dttemp;
}
}
catch (Exception ex)
{
//MessageBox.Show(ex.ToString());
}
}
private void btn_sqlconn_Click(object sender, EventArgs e)
{
try
{
if (cmb_pt.SelectedItem == null)
{
return;
}
string sqlgetdata = string.Format("select * from PT_Up_DataBase_Setting where pt_id = '{0}'", ((DataRowView)cmb_pt.SelectedItem).Row["PT_ID"].ToString());
DataTable connstr = DALUse.Query(sqlgetdata).Tables[0];
if (connstr.Rows.Count == 1)
{
string sqlupdate = string.Format("update PT_Up_DataBase_Setting set joins = '{0}',PT_DataBase ='{2}' where pt_id = '{1}'", txt_connstr.Text, cmb_pt.SelectedValue.ToString(), txt_database.Text);
if (DALUse.ExecuteSql(sqlupdate) == 1)
{
MessageBox.Show("修改成功！");
}
}
else if (connstr.Rows.Count == 0)
{
string sqlinsert = string.Format("insert into PT_Up_DataBase_Setting (pt_id,Joins,PT_DataBase) values('{0}','{1}','{2}')",
cmb_pt.SelectedValue.ToString(),
txt_connstr.Text,
txt_database.Text);
if (DALUse.ExecuteSql(sqlinsert) == 1)
{
MessageBox.Show("添加成功！");
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public void GetPats()
{
m_dsPtas = new DataSet();
string sql = string.Format("select m.PATIENT_ID,m.VISIT_ID ,n.NAME from pat_visit m ,pat_master_index n where {0} >'{1}' and {0}< '{2}' and m.patient_id = n.patient_id", "DISCHARGE_DATE_TIME", DateTime.Today.AddDays(-1), DateTime.Today);
m_dsPtas = DALUse.Query(sql);
ExportDB.m_strTimeRange = "[时间]:" + DateTime.Today.AddDays(-1).ToString() + "至" + DateTime.Today;
RemoteMessage.SendMessage("===加载病人列表\nSQL：" + sql + "\n===共" + m_dsPtas.Tables[0].Rows.Count.ToString() + "人");
}
private void gridView2_Click(object sender, EventArgs e)
{
if (gridView2.FocusedRowHandle < 0)
{
return;
}
string sql = string.Format("select * from pt_sql where id = {0}", gridView2.GetDataRow(gridView2.FocusedRowHandle)["ID"].ToString());
rtb_sql.Text = DALUse.Query(sql).Tables[0].Rows[0]["SQL"].ToString();
}
public void GetField()
{
if (gridView1.FocusedRowHandle < 0 || cmb_pt.SelectedItem == null)
{
return;
}
string sql = string.Format("select CENGCI_CODE,FIELD_NAME from pt_temp_field where pt_name = '{0}' and item_name='{1}'", ((DataRowView)cmb_pt.SelectedItem).Row["pt_name"].ToString(), gridView1.GetDataRow(gridView1.FocusedRowHandle)["item_name"].ToString());
gc_field.DataSource = DALUse.Query(sql).Tables[0];
}
public void getLayoutData()
{
try
{
gc_layout.DataSource = DALUse.Query(string.Format("select distinct LAYOUT_NAME from pt_xml_config where pt_id = '{0}'", cmb_pt.SelectedValue)).Tables[0];
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
/// <summary>
/// 取出对比字段
/// </summary>
/// <param name="pt_id">平台名</param>
/// <param name="table_name">表名</param>
/// <param name="compare_name">字段名</param>
/// <returns></returns>
public static string getFieldName(string pt_id, string table_name, string compare_name)
{
string result = "";
DataSet ds = DALUse.Query(string.Format("select * from PT_COMPARISON where pt_id = '{0}' and table_name = '{1}' and compare_name = '{2}'", pt_id, table_name, compare_name));
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
result = ds.Tables[0].Rows[0]["field"].ToString();
}
return(result);
}
public void getData()
{
try
{
string sqlgetdata = string.Format("select * from PT_Up_DataBase_Table where pt_id = '{0}'", ((DataRowView)cmb_pt.SelectedItem).Row["pt_id"].ToString());
gc_upload.DataSource = DALUse.Query(sqlgetdata).Tables[0];
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void cmb_local_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
string sqllocal = string.Format("select * from PT_LOCAL_DICT WHERE type_name='{0}'", ((DataRowView)cmb_local.SelectedItem).Row["type_name"].ToString());
dtlocal = DALUse.Query(sqllocal).Tables[0];
gc_local.DataSource = dtlocal;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void cmb_target_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
string sqltarget = string.Format("select distinct 0 NEWROWFLAG, T.* from PT_COMPARISON_DETAIL_DICT T WHERE pt_id='{0}' and type_name='{1}'", ((DataRowView)cmb_pt_name.SelectedItem).Row["pt_id"].ToString(), ((DataRowView)cmb_target.SelectedItem).Row["type_name"].ToString());
dtcompare = DALUse.Query(sqltarget).Tables[0];
gc_comparison.DataSource = dtcompare;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public void InitComboxDatasource()
{
try
{
cmb_pt.DataSource = DALUse.Query(PublicProperty.SqlGetPT).Tables[0];
cmb_pt.ValueMember = "pt_id";
cmb_pt.DisplayMember = "pt_name";
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void txt_itemname_TextChanged(object sender, EventArgs e)
{
sb.Length = 0;
sb.Append('%');
foreach (char c in txt_itemname.Text.ToCharArray())
{
sb.Append(c);
sb.Append('%');
}
string sql = string.Format("select * from PT_LOCAL_DICT where field like'{0}'", sb.ToString());
gc_local.DataSource = DALUse.Query(sql).Tables[0];
}
private void btn_export_Click(object sender, EventArgs e)
{
try
{
if (sfd_save.ShowDialog(this) == DialogResult.OK)
{
cf.WaitingThreadStart();
if (rb_excel.Checked)//excel导出
{
gridView1.ExportToXls(sfd_save.FileName + ".xls");
}
else if (rb_pdf.Checked)//pdf导出
{
MessageBox.Show("未完成");
}
else if (rb_xml.Checked)//xml导出
{
XmlDocument doc = new XmlDocument();
string sqlgetlayout = string.Format("select * from pt_xml_config where layout_name = '{0}'", cmb_xml.Text);
DataTable dtlayout = DALUse.Query(sqlgetlayout).Tables[0];
DataRow[] drroot = dtlayout.Select("parient_id is null");
XmlNode root = doc.CreateElement(drroot[0]["field_name"].ToString());
root.InnerText = "";
doc.AppendChild(root);
DataRow[] drsecondes = dtlayout.Select(string.Format("parient_id ='{0}'", drroot[0]["id"].ToString()));
foreach (DataRow drdata in source.Rows)
{
XmlElement xn = doc.CreateElement(drsecondes[0]["field_name"].ToString());
root.AppendChild(xn);
foreach (DataRow drfield in dtlayout.Rows)
{
if (source.Columns.Contains(drfield["field"].ToString()))
{
XmlNode xnleaf = doc.CreateElement(drfield["field_name"].ToString());
xnleaf.InnerText = drdata[drfield["field"].ToString()].ToString();
xn.AppendChild(xnleaf);
}
}
}
CommonFunction.ConverDataSetToXMLFile(doc.InnerXml, sfd_save.FileName + ".xml");
}
cf.WaitingThreadStop();
uctlMessageBox.frmDisappearShow("数据导出成功！");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
/// <summary>
/// 同过平台id获取所有需要转化的列集合
/// </summary>
/// <param name="_id">平台id</param>
/// <returns>需要转化的列集合</returns>
private static DataSet GetPtCompareDict(string _id)
{
DataSet _ds = new DataSet();
string strSQL = string.Format("select distinct type_name from pt_comparison_detail_dict t where pt_id ='{0}'", _id);
try
{
_ds = DALUse.Query(strSQL);
}
catch (Exception exp)
{
CommonFunction.WriteError(exp.ToString());
}
return(_ds);
}
private void InitData()
{
if (cmb_ptname.SelectedValue == null || gridView1.FocusedRowHandle < 0)
{
return;
}
string sql = string.Format("select * from PT_TABLES_DICT where pt_id = '{0}'", cmb_ptname.SelectedValue.ToString());
dttabledetail = DALUse.Query(sql).Tables[0];
gc_table_detail.DataSource = dttabledetail;
string sqltrget = string.Format("select * from pt_target_field where table_name ='{0}' and pt_id = '{1}'", gridView1.GetDataRow(gridView1.FocusedRowHandle)["TABLE_NAME"].ToString(), cmb_ptname.SelectedValue.ToString());
dttemp = DALUse.Query(sqltrget).Tables[0];
gc_pt_target.DataSource = dttemp;
}
/// <summary>
/// 获取插入字典的内容
/// </summary>
public void GetDictDetail()
{
DataTable dt = null;
string sqlgetinsertdata = (string.Format("select TYPE_NAME,FIELD_CODE,FIELD from {0} where pt_id = '{1}' and type_name = '{2}'", tablename, cmb_pt.SelectedValue.ToString(), txt_dict_name.Text.ToString()));
try
{
dt = DALUse.Query(sqlgetinsertdata).Tables[0];
}
catch (Exception ex)
{
CommonFunction.WriteErrorLog(ex.ToString());
}
gc_database.DataSource = dt;
}
public void InitDataTableColumns()
{
try
{
if (cmb_pt_name.SelectedItem == null | cmb_target.SelectedItem == null)
{
return;
}
string getstyle = string.Format("select distinct 0 NEWROWFLAG, T.* from PT_COMPARISON_DETAIL_DICT T WHERE 1=0", ((DataRowView)cmb_pt_name.SelectedItem).Row["pt_id"].ToString(), ((DataRowView)cmb_target.SelectedItem).Row["type_name"].ToString());
dtcompare = DALUse.Query(getstyle).Tables[0];
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public void Initgc_nodeData()
{
try
{
if (gridView2.FocusedRowHandle < 0)
{
return;
}
DataTable dtgc_node = DALUse.Query(string.Format("select * from pt_xml_config where layout_name = '{0}'", gridView2.GetDataRow(gridView2.FocusedRowHandle)["layout_name"].ToString())).Tables[0];
gc_node.DataSource = dtgc_node;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public void InitDictData()
{
try
{
cmb_target.DataSource = DALUse.Query(string.Format("SELECT DISTINCT TYPE_NAME FROM PT_COMPARISON_DETAIL_DICT where PT_ID ='{0}'", ((DataRowView)cmb_pt_name.SelectedItem).Row["pt_id"].ToString())).Tables[0];
cmb_target.DisplayMember = "TYPE_NAME";
cmb_target.ValueMember = "TYPE_NAME";
cmb_local.DataSource = DALUse.Query(string.Format("SELECT DISTINCT TYPE_NAME FROM PT_LOCAL_DICT where pt_id = '{0}'", ((DataRowView)cmb_pt_name.SelectedItem).Row["pt_id"].ToString())).Tables[0];
cmb_local.DisplayMember = "TYPE_NAME";
cmb_local.ValueMember = "TYPE_NAME";
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
//将目标表的字段同步到本地
public void SynTargetField()
{
try
{
string sqltarget = string.Format("select * from {0} where 1=0 ", gridView1.GetDataRow(gridView1.FocusedRowHandle)["TABLE_NAME"].ToString());
DataSet dstarget = DALUseSpecial.Query(sqltarget, connstr);
if (dstarget.Tables.Count > 0)
{
foreach (DataColumn dc in dstarget.Tables[0].Columns)
{
string columntype = "";
if (dc.DataType.Name.ToString().Contains("String"))
{
columntype = "STRING";
}
else if (dc.DataType.Name.ToString().Contains("Decimal") || dc.DataType.Name.ToString().Contains("Int"))
{
columntype = "NUMBER";
}
else if (dc.DataType.Name.ToString().Contains("DateTime"))
{
columntype = "DATE";
}
else
{
MessageBox.Show(dc.DataType.Name.ToString());
}
string sql = string.Format("insert into PT_TARGET_FIELD(compare_id,pt_name,table_name,field_name,field,field_type,compare_name,pt_id) values('{7}','{0}','{1}','{2}','{3}','{4}','{5}','{6}')", cmb_ptname.Text, gridView1.GetDataRow(gridView1.FocusedRowHandle)["TABLE_NAME"].ToString(), dc.ColumnName, dc.ColumnName, columntype, "", cmb_ptname.SelectedValue.ToString(), Guid.NewGuid());
string sql1 = string.Format("select * from PT_TARGET_FIELD where pt_id = '{0}' and table_name = '{1}' and field = '{2}'", cmb_ptname.SelectedValue.ToString(), gridView1.GetDataRow(gridView1.FocusedRowHandle)["TABLE_NAME"].ToString(), dc.ColumnName);
DataSet ds = DALUse.Query(sql1);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count == 0)
{
DALUse.ExecuteSql(sql);
}
}
}
else
{
MessageBox.Show("目标库不存在表：" + gridView1.GetDataRow(gridView1.FocusedRowHandle)["TABLE_NAME"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public void InitComBoxDataSource()
{
try
{
cmb_ptname.DataSource = DALUse.Query(PublicProperty.SqlGetPT).Tables[0];
cmb_ptname.DisplayMember = "PT_NAME";
cmb_ptname.ValueMember = "PT_ID";
//string sql = "select * from pt_dict";
//cmb_ptname.DataSource = DALUse.Query(sql).Tables[0];
//cmb_ptname.DisplayMember = "PT_NAME";
//cmb_ptname.ValueMember = "PT_ID";
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 取出对比字段
/// </summary>
/// <param name="pt_id">平台名</param>
/// <param name="table_name">表名</param>
/// <param name="compare_name">字段名</param>
/// <returns></returns>
public static string getFieldName(string pt_id, string table_name, string compare_name)
{
string result = "";
try
{
DataSet ds = DALUse.Query(string.Format("select * from PT_COMPARISON where pt_id = '{0}' and table_name = '{1}' and compare_name = '{2}'", pt_id, table_name, compare_name));
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
result = ds.Tables[0].Rows[0]["field"].ToString();
}
}
catch (Exception ex)
{
CommonFunction.WriteErrotLog(ex.ToString());
}
return(result);
}
/// <summary>
/// 获取由sql查询到的病人信息
/// </summary>
/// <param name="dql">sql集</param>
/// <returns>病人信息</returns>
public void GetPatientData()
{
m_dsAllPatsInfo = new DataSet();
foreach (DataRow dr in m_dsSQL.Tables[0].Rows)
{
DataTable _dtPatsInfo = new DataTable();
foreach (DataRow drpat in m_dsPtas.Tables[0].Rows)
{
string _strPatientId = drpat["PATIENT_ID"].ToString();
string _strVisitId = drpat["VISIT_ID"].ToString();
string _strSql = string.Format(dr["sql"].ToString().Replace("@PATIENT_ID", _strPatientId).Replace("@VISIT_ID", _strVisitId));
_dtPatsInfo.Merge(DALUse.Query(_strSql).Tables[0]);
RemoteMessage.SendMessage("===查询病人信息\nSQL：" + _strSql);
}
DataTable _dt = _dtPatsInfo.Copy();
_dt.TableName = "@" + dr["SQL_NAME"].ToString() + "@" + dr["table_id"].ToString();
m_dsAllPatsInfo.Tables.Add(_dt);
}
}
private void btn_query_Click(object sender, EventArgs e)
{
try
{
if (rtb_sql.Text.Length == 0)
{
MessageBox.Show("无效SQL语句！");
return;
}
gridView1.Columns.Clear();
gc_querycontent.DataMember = null;
source = DALUse.Query(string.Format(rtb_sql.Text)).Tables[0];
DoExchange(source.DataSet, cmb_pt.SelectedValue.ToString());
gc_querycontent.DataSource = source;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
/// <summary>
/// 刷新txt_sql gc_sql
/// </summary>
public void FreshSQL()
{
try
{
rtb_sql.Text = "";
string sql = string.Format("select * from pt_sql where pt_id = {0}", ((DataRowView)cmb_pt.SelectedItem).Row["PT_ID"].ToString());
gc_sql.DataSource = DALUse.Query(sql).Tables[0];
if (gridView2.FocusedRowHandle < 0)
{
return;
}
string sqlgettxt = string.Format("select * from pt_sql where id = {0}", gridView2.GetDataRow(gridView2.FocusedRowHandle)["ID"].ToString());
rtb_sql.Text = DALUse.Query(sql).Tables[0].Rows[0]["SQL"].ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void cmb_ptname_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
gc_pt_target.DataSource = null;
string sql = string.Format("select * from PT_TABLES_DICT where pt_id = '{0}'", ((DataRowView)cmb_ptname.SelectedItem).Row["pt_id"].ToString());
dttabledetail = DALUse.Query(sql).Tables[0];
gc_table_detail.DataSource = dttabledetail;
if (dttabledetail.Rows.Count != 0)
{
string sqltrget = string.Format("select * from pt_target_field where table_name ='{0}' and pt_id = '{1}'", gridView1.GetDataRow(gridView1.FocusedRowHandle)["TABLE_NAME"].ToString(), ((DataRowView)cmb_ptname.SelectedItem).Row["pt_id"].ToString());
dttemp = DALUse.Query(sqltrget).Tables[0];
gc_pt_target.DataSource = dttemp;
}
}
catch (Exception)
{
throw;
}
}
