private DataTable GetDtAccess(string ybh, string yljgbm, string sblx)
{
DBAccess dbjkdah = new DBAccess();
if (sblx == "1") //条码
{
//获取以对应的人员的信息
return(dbjkdah.ExecuteQueryBySql(@"select jkdah,sfzh from T_JK_TJRY_TXM where txmbh='" + ybh + "' and yljgbm='" + yljgbm + "'"));
}
else
{
return
(dbjkdah.ExecuteQueryBySql(@"select jkdah,sfzh from t_jk_tjryxx where sfzh='" + ybh + "' and yljgbm='" + yljgbm + "'"));
}
}
/// <summary>
/// 取得Guid
/// </summary>
/// <returns></returns>
private bool getNewGuid(string jkdah, string jyrq, out string guid)
{
guid = Guid.NewGuid().ToString();
DBAccess dBAccess = new DBAccess();
string sql = "";
ArrayList sqlList = new ArrayList();
sql = "select guid from T_JK_JKTJ where d_grdabh='{d_grdabh}' and happentime='{happentime}'";
//健康档案编号
sql = sql.Replace("{d_grdabh}", jkdah);
//体检日期
sql = sql.Replace("{happentime}", Convert.ToDateTime(jyrq).ToString("yyyy-MM-dd"));
DataTable dt = dBAccess.ExecuteQueryBySql(sql);
if (dt != null && dt.Rows.Count > 0)
{
guid = dt.Rows[0]["guid"].ToString();
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// 窗体加载事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void AddForm_Load(object sender, EventArgs e)
{
DBAccess dBAccess = new DBAccess();
//取得村庄
string sqlCunzhaung = @"SELECT T_BS_CUNZHUANG.B_RGID, T_BS_CUNZHUANG.B_NAME
FROM T_TJ_YLJG_XIANGZHEN INNER JOIN T_BS_CUNZHUANG ON T_TJ_YLJG_XIANGZHEN.XZBM = left(T_BS_CUNZHUANG.B_RGID,len(T_TJ_YLJG_XIANGZHEN.XZBM))
where T_TJ_YLJG_XIANGZHEN.YLJGBM='{YLJGBM}'
order by T_BS_CUNZHUANG.B_RGID;";
DataTable dtCunzhuang = dBAccess.ExecuteQueryBySql(sqlCunzhaung.Replace("{YLJGBM}", UserInfo.Yybm));
//绑定村庄
DataRow dtRow = dtCunzhuang.NewRow();
dtRow["B_RGID"] = "";
dtRow["B_NAME"] = "--请选择--";
dtCunzhuang.Rows.InsertAt(dtRow, 0);
comboBox_cunzhuang.DataSource = dtCunzhuang;
comboBox_cunzhuang.DisplayMember = "B_NAME";
comboBox_cunzhuang.ValueMember = "B_RGID";
comboBox_cunzhuang.SelectedValue = czbm;
comboBox_cunzhuang.Focus();
}
/// <summary>
/// 窗体事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void menu_Load(object sender, EventArgs e)
{
DBAccess dBAccess = new DBAccess();
//取得医疗机构编码
DataTable dt = dBAccess.ExecuteQueryBySql(" SELECT Xt_gg_sydw.bm, Xt_gg_sydw.mc FROM Xt_gg_sydw ");
//绑定医疗机构
comFz.DataSource = dt;
comFz.DisplayMember = "mc";
comFz.ValueMember = "bm";
string yljgbm = ConfigurationSettings.AppSettings["YLJGBM"].ToString();
if (yljgbm.Length > 0)
{
comFz.SelectedValue = yljgbm;
comFz.Enabled = false;
}
//设定默认的用户名
string defaut_username = System.Configuration.ConfigurationManager.AppSettings["defaut_username"];
txtCzy.Text = defaut_username;
txtCzy.Focus();
}
private DataTable GetHyxm(DataTable dtAll, DataRow drs)
{
string sql = string.Format("select b.HIS_DB,b.JKDA_DB,b.KJID,b.XMMC,a.xmbm_lis from T_JK_LIS_XM a,t_jk_tjxm b where a.xmbm=b.xmbm and yljgbm='{0}'", UserInfo.Yybm);
DBAccess access = new DBAccess();
DataTable dt = access.ExecuteQueryBySql(sql);
string where = string.Format("ybh='{0}' and jyrq='{1}' and yq='{2}' and yybm='{3}'", drs["ybh"],
drs["jyrq"], drs["yq"], drs["yybm"]);
DataTable reDt = dtAll.Select(where).CopyToDataTable();
var results = from a in reDt.AsEnumerable()
join b in dt.AsEnumerable() on a.Field <string>("xmdh") equals b.Field <String>("XMBM_LIS")
select new
{
yybm = a["yybm"],
yq = a["yq"],
jyrq = a["jyrq"],
ybh = a["ybh"],
xmdh = a["xmdh"],
xmmc = a["xmmc"],
result = a["result"],
//result1 = a["result1"],
lowerValue = a["lowerValue"],
heightValue = a["heightValue"],
HIS_DB = b["HIS_DB"],
JKDA_DB = b["JKDA_DB"],
KJID = b["KJID"],
XMMC = b["XMMC"]
};
return(LinQHelper.LINQResultToDataTable(results));
}
private DataTable GetDtAccess(DataRow drs)
{
DBAccess dbjkdah = new DBAccess();
string sql = string.Format("select txmbh,jkdah,sfzh from t_jk_tjry_txm where txmbh='{0}' and yljgbm='{1}'", drs["ybh"], drs["yybm"]);
return
(dbjkdah.ExecuteQueryBySql(sql));
}
/// <summary>
/// 处理数据到数据库中
/// </summary>
/// <param name="dt_all"></param>
/// <returns></returns>
public string Upd_all(DataTable dt_all)
{
if (dt_all.Rows.Count > 0)
{
//过滤数据 得到数据行 身份证号
DataRow[] drs = dt_all.Select("xmdh='NAME'");
for (int i = 0; i < drs.Length; i++)
{
string ybh = drs[i]["ybh"].ToString();
DBAccess dbjkdah = new DBAccess();
DataTable dt_access = dbjkdah.ExecuteQueryBySql(@"select * from T_JK_lis_result_re where ybh='" + ybh + "' ");
if (dt_access != null && dt_access.Rows.Count > 0)
{
for (int j = 0; j < dt_access.Rows.Count; j++)
{
DataTable dt_tem = new DataTable();
dt_tem.Columns.Add("yybm");
dt_tem.Columns["yybm"].DefaultValue = dt_access.Rows[j]["yybm"];
dt_tem.Columns.Add("yq");
dt_tem.Columns["yq"].DefaultValue = dt_access.Rows[j]["yq"];
dt_tem.Columns.Add("jyrq");
dt_tem.Columns["jyrq"].DefaultValue = dt_access.Rows[j]["jyrq"];
dt_tem.Columns.Add("ybh");
dt_tem.Columns["ybh"].DefaultValue = dt_access.Rows[j]["ybh"];
dt_tem.Columns.Add("xmdh");
dt_tem.Columns.Add("result");
DataRow[] drss = dt_all.Select("jyrq='" + drs[i]["jyrq"].ToString() + "' and ybh='" + drs[i]["ybh"].ToString() + "'");
for (int k = 0; k < drss.Length; k++)
{
dt_tem.Rows.Add();
dt_tem.Rows[k]["xmdh"] = drss[k]["xmdh"];
dt_tem.Rows[k]["result"] = drss[k]["result"];
}
Form_lisBll form_lisbll = new Form_lisBll();
form_lisbll.Upd_all(dt_tem, "sql042_update");
}
}
else
{
DataTable dt_insert_tem = dt_all.Clone();
DataRow[] drss = dt_all.Select("jyrq='" + drs[i]["jyrq"].ToString() + "' and ybh='" + drs[i]["ybh"].ToString() + "'");
if (drss != null && drss.Length > 0)
{
for (int k = 0; k < drss.Length; k++)
{
dt_insert_tem.ImportRow(drss[k]);
}
Form_lisBll form_lisbll = new Form_lisBll();
form_lisbll.Add(dt_insert_tem, "sql042");
}
}
}
}
return("");
}
/// <summary>
/// 按照维护的基础数据匹配心电图的结果
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public DataTable dt_pp(DataTable dt)
{
//将数据进行排序
//DataView dtview_pp = dt.DefaultView;
//dtview_pp.Sort = " ybh,xmdh ";
//dt = dtview_pp.ToTable();
DataRow[] dtRows = dt.Select(" xmdh='CONCLUSION_QT'");
if (dtRows.Length > 0)
{
DBAccess dbaccess = new DBAccess();
DataTable dt_pp_base = dbaccess.ExecuteQueryBySql(string.Format("select * from t_jk_xdtjgppb where yljgbm is null or yljgbm='' or yljgbm ='{0}' order by value ", UserInfo.Yybm));
for (int i = 0; i < dtRows.Length; i++)
{
DataRow[] dtrow_qt = dt.Select(string.Format(" xmdh='TJPPZT' and ybh='{0}' and jyrq='{1}' ", dtRows[i]["ybh"].ToString(), dtRows[i]["jyrq"].ToString()));
if (dtrow_qt != null && dtrow_qt.Length > 0)
{
if (dt_pp_base != null && dt_pp_base.Rows.Count > 0)
{
string CONCLUSION_QT = dtRows[i]["result"].ToString();
string tjztqt = "";
for (int j = 0; j < dt_pp_base.Rows.Count; j++)
{
if (CONCLUSION_QT.IndexOf(dt_pp_base.Rows[j]["text"].ToString()) > -1)
{
CONCLUSION_QT = CONCLUSION_QT.Replace(dt_pp_base.Rows[j]["text"].ToString(), "");
string strvalue_tem = dt_pp_base.Rows[j]["value"].ToString();
if (strvalue_tem.IndexOf((tjztqt + ",")) == -1)
{
tjztqt = tjztqt + "," + strvalue_tem;
}
}
}
if (tjztqt.Replace(",1", "").Length > 0)
{
tjztqt = tjztqt.Replace(",1", "");
}
if (CONCLUSION_QT.Trim().Length > 0)
{
tjztqt = tjztqt + "," + "99";
}
dtRows[i]["result"] = CONCLUSION_QT;
dtrow_qt[0]["result"] = tjztqt.Length > 1 ? tjztqt.Substring(1) : tjztqt;
}
}
}
}
return(dt);
}
private DataTable GetDtAccess(string ybh, string yljgbm, ref string testno)
{
DBAccess dbjkdah = new DBAccess();
DataTable dt_access_no = dbjkdah.ExecuteQueryBySql(@"select testno from T_JK_lis_result_re where ybh='" + ybh + "'");
if (dt_access_no != null && dt_access_no.Rows.Count > 0 && !string.IsNullOrEmpty(dt_access_no.Rows[0]["testno"].ToString().Trim()))
{
//获取以对应的人员的信息
testno = dt_access_no.Rows[0]["testno"].ToString().Trim();
return
(dbjkdah.ExecuteQueryBySql(@"select jkdah,sfzh from T_JK_TJRY_TXM where (txmbh='" +
dt_access_no.Rows[0]["testno"]
+ "' or jkdah ='" + dt_access_no.Rows[0]["testno"] + "' or sfzh='" + dt_access_no.Rows[0]["testno"] +
"') and yljgbm='" + yljgbm + "'"));
}
else
{
testno = ybh;
return(dbjkdah.ExecuteQueryBySql(@"select jkdah,sfzh from T_JK_TJRY_TXM where (txmbh='" + ybh + "' or sfzh='" + ybh + "') and yljgbm='" + yljgbm + "'"));
}
}
private bool LisResultReDataIsExist(string ybh, string jyrq, string yq, string yybm)
{
string sql = string.Format("select id from T_JK_lis_result_re where ybh='{0}' and jyrq='{1}' and yq='{2}' and yybm='{3}'", ybh, jyrq, yq, yybm);
DBAccess db = new DBAccess();
DataTable dt = db.ExecuteQueryBySql(sql);
if (dt != null && dt.Rows.Count > 0)
{
return(true);
}
return(false);
}
private string getXmBySfzh(string sfzh, string jkdah, string yybm)
{
string result = "";
DBAccess db = new DBAccess();
DataTable dt_accessXM = db.ExecuteQueryBySql(@"select top 1 XM from T_JK_TJRYXX where sfzh='" + sfzh + "' and jkdah='"
+ jkdah + "' and yljgbm='" + yybm + "'");
if (dt_accessXM.Rows.Count > 0)
{
result = dt_accessXM.Rows[0]["xm"] as string;
}
return(result);
}
/// <summary>
/// 检索处理
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button_select_Click(object sender, EventArgs e)
{
//string sqlWhere = " and (D_GRDABH like '%{para}%' or D_XM like '%{para}%' or D_SFZH like '%{para}%' or D_LXDH like '%{para}%' or CREATEUSER like '%{para}%') ".Replace("{para}", textBox_Select.Text.Trim());
string sqlWhere = string.Format(" and importDate >='{0}' and importDate <='{1}' ", dateTimePicker1.Value.ToString("yyyy-MM-dd"), dateTimePicker2.Value.ToString("yyyy-MM-dd"));
DataRow dt_row = dt_fileType.Select(string.Format("iid={0}", comboBox_importDataSet.SelectedValue.ToString()))[0];
string tableName = dt_row["dbTableName_from"].ToString();
DBAccess dBAccess = new DBAccess();
//Form_downLoadBll form_download = new Form_downLoadBll();
DataTable dt = dBAccess.ExecuteQueryBySql(string.Format("select * from {0} where 1=1 {1}", tableName, sqlWhere)); // form_download.GetMoHuList(sqlWhere, "sql040");
dataGridView_list.DataSource = dt;
}
/// <summary>
/// 取得数据字典中的项目
/// </summary>
/// <param name="Sjzdbm"></param>
/// <returns></returns>
private DataTable getSjzdList(string Sjzdbm)
{
//获取该工作组对应的体检项目
string sql = @"select * from T_JK_SJZD where ZDLXBM='{ZDLXBM}' order by id ";
DataTable checkList = new DataTable();
DBAccess dBAccess = new DBAccess();
checkList = dBAccess.ExecuteQueryBySql(sql.Replace("{ZDLXBM}", Sjzdbm));
if (checkList != null && checkList.Rows.Count > 0)
{
return(checkList);
}
return(null);
}
/// <summary>
/// 获取工作组的相关信息
/// </summary>
public void SignNameGroupInit()
{
//获取该工作组对应的体检项目
string sql = @"SELECT T_JK_TJXM.SIGNNAMEGROUP,T_JK_GZZ_XM.YLJGBM, T_JK_GZZ_XM.GZZBM, T_JK_TJXM.XMFLBM, T_JK_TJXM.XMBM, T_JK_TJXM.XMMC, T_JK_TJXM.KJXSMC, T_JK_TJXM.KJLX, T_JK_TJXM.SJZDBM, T_JK_TJXM.KJID, T_JK_TJXM.KJKD, T_JK_TJXM.KJGD, T_JK_TJXM.KJMRZ, T_JK_TJXM.JKDA_DB, T_JK_TJXM.HIS_DB, T_JK_TJXM.SL, T_JK_TJXM.DJ, T_JK_TJXM.parentxm, T_JK_TJXM.parentxmvalue, T_JK_TJXM.maxcount, T_JK_TJXM.dw, T_JK_XMFL.XMFLMC,T_JK_TJXM.rowNo,T_JK_TJXM.jj,T_JK_TJXM.valueHeigh,T_JK_TJXM.valueLower,T_JK_TJXM.isNotNull,T_JK_TJXM.fzlritem
FROM (T_JK_GZZ_XM INNER JOIN T_JK_TJXM ON T_JK_GZZ_XM.XMBM = T_JK_TJXM.XMBM) INNER JOIN T_JK_XMFL ON T_JK_TJXM.XMFLBM = T_JK_XMFL.XMFLBM
WHERE (((T_JK_GZZ_XM.YLJGBM)='{YLJGBM}') AND ((T_JK_GZZ_XM.GZZBM)='{GZZBM}')) order by T_JK_TJXM.XMFLBM, T_JK_TJXM.rowNo,T_JK_TJXM.ORDERBY ";
//从数据库中取值
DBAccess dBAccess = new DBAccess();
dtResult = dBAccess.ExecuteQueryBySql(sql.Replace("{YLJGBM}", UserInfo.Yybm).Replace("{GZZBM}", UserInfo.gzz));
//初始化签名列表
TJClient.Signname.DataOperation dataoperation = new TJClient.Signname.DataOperation();
dtSignname = dataoperation.GetSignname(UserInfo.userId);
}
/// <summary>
/// 健康体检中的lis信息直接上传到健康档案系统中
/// </summary>
/// <param name="D_SFZH"></param>
/// <param name="D_GRDABH"></param>
/// <param name="P_RGID"></param>
/// <returns></returns>
public bool lisUpLoadToJkda(string D_SFZH, string D_GRDABH, string HAPPENTIME, string P_RGID, out string errMsg)
{
errMsg = "";
try
{
//sql
string sql = string.Format("select * from T_JK_JKTJ where D_GRDABH='{0}' and HAPPENTIME='{1}'", D_GRDABH, HAPPENTIME);
//取得数据
DBAccess dbaccess = new DBAccess();
DataTable dt_jkdaLis = dbaccess.ExecuteQueryBySql(sql);
dt_jkdaLis.TableName = gwtjUpload.commSys.tableName.检验信息;
if (dt_jkdaLis != null && dt_jkdaLis.Rows.Count > 0)
{
gwtjUpload.gwtjUpload gwtjupload = new gwtjUpload.gwtjUpload();
string result = gwtjupload.gwtjDoService(dt_jkdaLis, D_SFZH, D_GRDABH, P_RGID, "", "");
string[] resultList = result.Split(new char[] { '-' });
//正常情况下
if (resultList.Length > 0 && resultList[0].Equals("0"))
{
return(true);
}
else
{
errMsg = result;
return(false);
}
}
else
{
return(false);
}
}
catch (Exception ex)
{
errMsg = ex.Message;
return(false);
}
return(true);
}
/// <summary>
/// 取得Guid
/// </summary>
/// <returns></returns>
private string getGuid_xlzp()
{
string guid = System.Guid.NewGuid().ToString();
DBAccess dBAccess = new DBAccess();
string sql = "";
ArrayList sqlList = new ArrayList();
sql = "select guid from T_JG_LNRSF where czy='{czy}' and gzz='{gzz}' and D_GRDABH='{d_grdabh}' and HAPPENTIME ='{happentime}'";
//操作员
sql = sql.Replace("{czy}", userId);
//工作组
sql = sql.Replace("{gzz}", yhfz);
//健康档案编号
sql = sql.Replace("{d_grdabh}", textBox_head_JKDAH.Text);
//体检日期
sql = sql.Replace("{happentime}", dateTimePicker_SFRQ_lkzp.Value.ToString("yyyy-MM-dd"));
DataTable dt = dBAccess.ExecuteQueryBySql(sql);
if (dt != null && dt.Rows.Count > 0)
{
guid = dt.Rows[0]["guid"].ToString();
}
return(guid);
}
/// <summary>
/// 检索当前数据数据
/// </summary>
private void select_xlzp()
{
string select_sql = @"select D_GRDABH,
G_XCSFMB,
G_XCSFRQ,
G_SFYS,
CREATREGION,
CREATEUSER,
P_RGID,
CREATETIME,
UPDATETIME,
HAPPENTIME,
G_LRRQ,
QDQXZ,
JCPF,
SXPF,
RCPF,
HDPF,
ZPF,
CYPF
from T_JG_LNRSF
where
D_GRDABH='{D_GRDABH}'
and
HAPPENTIME='{HAPPENTIME}'";
select_sql = select_sql.Replace("{D_GRDABH}", textBox_head_JKDAH.Text).Replace("{HAPPENTIME}", getValueFromDt(dt_paraFromParent, 0, "TJSJ"));
DBAccess access = new DBAccess();
DataTable dt_xlzp = new DataTable();
dt_xlzp = access.ExecuteQueryBySql(select_sql);
if (dt_xlzp != null && dt_xlzp.Rows.Count > 0)
{
//进餐
setValueToControl(controlType_radioButton + "01_" + dt_xlzp.Rows[0]["JCPF"].ToString(), dt_xlzp.Rows[0]["JCPF"].ToString());
//梳洗
setValueToControl(controlType_radioButton + "02_" + dt_xlzp.Rows[0]["SXPF"].ToString(), dt_xlzp.Rows[0]["SXPF"].ToString());
//穿衣
setValueToControl(controlType_radioButton + "03_" + dt_xlzp.Rows[0]["CYPF"].ToString(), dt_xlzp.Rows[0]["CYPF"].ToString());
//如厕
setValueToControl(controlType_radioButton + "04_" + dt_xlzp.Rows[0]["RCPF"].ToString(), dt_xlzp.Rows[0]["RCPF"].ToString());
//活动
setValueToControl(controlType_radioButton + "05_" + dt_xlzp.Rows[0]["HDPF"].ToString(), dt_xlzp.Rows[0]["HDPF"].ToString());
//下次随访目标
richTextBox_XCSFMB_lkzp.Text = dt_xlzp.Rows[0]["G_XCSFMB"].ToString();
//下次随访日期
dateTimePicker_XCSFRQ_lkzp.Value = getDateFromString(dt_xlzp.Rows[0]["G_XCSFRQ"].ToString());
// 随访医生签名
if (dt_xlzp.Rows[0]["G_SFYS"] != null)
{
lTextBox_SFYS_lkzp.Text = dt_xlzp.Rows[0]["G_SFYS"].ToString();
}
else
{
lTextBox_SFYS_lkzp.Text = CommomSysInfo.TJFZR_MC;
}
//随访日期
dateTimePicker_SFRQ_lkzp.Value = getDateFromString(dt_xlzp.Rows[0]["HAPPENTIME"].ToString());
}
else
{
clear_xlzp();
lTextBox_SFYS_lkzp.Text = CommomSysInfo.TJFZR_MC;
}
}
/// <summary>
/// 再次处理
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button_save_sys_again(DataTable dt_all, string sqlcode)
{
try
{
if (dt_all.Rows.Count > 0)
{
DataRow[] drs = dt_all.Select("xmdh='NAME'");
for (int i = 0; i < drs.Length; i++)
{
DBAccess dbjkdah = new DBAccess();
//获取以对应的人的信息 不一定与心电工作站中的条码号对应
DataTable dt_access_no = dbjkdah.ExecuteQueryBySql(@"select testno from T_JK_lis_result_re where ybh='" + drs[i]["ybh"].ToString() + "'");
if (dt_access_no == null || dt_access_no.Rows.Count <= 0)
{
continue;
}
//获取以对应的人员的信息
DataTable dt_access = dbjkdah.ExecuteQueryBySql(@"select jkdah,sfzh from T_JK_TJRY_TXM where (txmbh='" + dt_access_no.Rows[0]["testno"].ToString()
+ "' or jkdah ='" + dt_access_no.Rows[0]["testno"].ToString() + "') and yljgbm='" + drs[i]["yybm"].ToString() + "'");
//没有对应的人员信息时直接退出
if (dt_access == null || dt_access.Rows.Count <= 0)
{
continue;
}
////更新化验结果表(T_JK_lis_result_re），确定化验结果与人员关系
//DataTable dt_update = new DataTable();
////医院编码
//dt_update.Columns.Add("yybm");
////仪器编码
//dt_update.Columns.Add("yq");
////样本号
//dt_update.Columns.Add("ybh");
////体检日期
//dt_update.Columns.Add("jyrq");
////健康档案号
//dt_update.Columns.Add("testno");
////年度
//dt_update.Columns.Add("nd");
//dt_update.Rows.Add();
//dt_update.AcceptChanges();
//dt_update.Rows[0]["yybm"] = UserInfo.Yybm;
//dt_update.Rows[0]["yq"] = "MECG200";
//dt_update.Rows[0]["ybh"] = drs[i]["ybh"].ToString();
//dt_update.Rows[0]["jyrq"] = Convert.ToDateTime(drs[i]["jyrq"].ToString()).ToString("yyyy-MM-dd");
//dt_update.Rows[0]["testno"] = dt_access.Rows[0]["jkdah"].ToString();
////dt_update.Rows[0]["nd"] = Convert.ToDateTime(jyrq_tem).Year .ToString();
//dt_update.Rows[0]["nd"] = DateTime.Now.Year.ToString();
Form_lisBll form_lisbll = new Form_lisBll();
////标本与人员的对应关系保存
//form_lisbll.Upd(dt_update, "sql_lis_result_ryxx_update");
//更新体检人员状态表
//form_lisbll.Upd(dt_update, "sql077");
//体检状态信息
ArrayList TJRYXXList = save_T_JK_TJZT(dt_access.Rows[0]["jkdah"].ToString(), dt_access.Rows[0]["sfzh"].ToString(), drs[i]["xmmc"].ToString(), drs[i]["jyrq"].ToString(), Common.TJTYPE.健康体检表, Common.ZT.确定状态);
if (TJRYXXList != null && TJRYXXList.Count > 0)
{
DBAccess dbaccess = new DBAccess();
dbaccess.ExecuteNonQueryBySql(TJRYXXList);
}
//保存体检结果到体检结果表
string sqlWhere = "";
//医院编码
if (UserInfo.Yybm.Length > 0)
{
sqlWhere = sqlWhere + string.Format(" and T_JK_lis_result_re.yybm='{0}'", UserInfo.Yybm);
}
//仪器
if (UserInfo.Yybm.Length > 0)
{
sqlWhere = sqlWhere + string.Format(" and T_JK_lis_result_re.yq='{0}'", drs[i]["yq"].ToString());
}
//检验日期
if (UserInfo.Yybm.Length > 0)
{
sqlWhere = sqlWhere + string.Format(" and T_JK_lis_result_re.jyrq='{0}'", drs[i]["jyrq"].ToString());
}
//样本号
if (UserInfo.Yybm.Length > 0)
{
sqlWhere = sqlWhere + string.Format(" and T_JK_lis_result_re.ybh='{0}'", drs[i]["ybh"].ToString());
}
//取得化验结果
DataTable dt_dyxm = form_lisbll.GetMoHuList(sqlWhere, "sql045");
//更新健康体检表
DataTable dt_tjjgUpdate = new DataTable();
dt_tjjgUpdate.Rows.Add();
for (int j = 0; j < dt_dyxm.Rows.Count; j++)
{
if (dt_tjjgUpdate.Columns.Contains(dt_dyxm.Rows[j]["HIS_DB"].ToString()) == false)
{
DataColumn dtColumn = new DataColumn(dt_dyxm.Rows[j]["HIS_DB"].ToString());
dtColumn.DefaultValue = dt_dyxm.Rows[j]["result"].ToString();
dt_tjjgUpdate.Columns.Add(dtColumn);
}
}
if (dt_tjjgUpdate.Columns.Contains("D_GRDABH") == false)
{
DataColumn dtColumn = new DataColumn("D_GRDABH");
dtColumn.DefaultValue = dt_access.Rows[0]["jkdah"].ToString();
dt_tjjgUpdate.Columns.Add(dtColumn);
}
if (dt_tjjgUpdate.Columns.Contains("HAPPENTIME") == false)
{
DataColumn dtColumn = new DataColumn("HAPPENTIME");
dtColumn.DefaultValue = Convert.ToDateTime(drs[i]["jyrq"].ToString()).ToString("yyyy-MM-dd");
dt_tjjgUpdate.Columns.Add(dtColumn);
}
if (dt_tjjgUpdate.Columns.Contains("czy") == false)
{
DataColumn dtColumn = new DataColumn("czy");
dtColumn.DefaultValue = UserInfo.userId;
dt_tjjgUpdate.Columns.Add(dtColumn);
}
if (dt_tjjgUpdate.Columns.Contains("gzz") == false)
{
DataColumn dtColumn = new DataColumn("gzz");
dtColumn.DefaultValue = UserInfo.gzz;
dt_tjjgUpdate.Columns.Add(dtColumn);
}
//体检结果是否已经存在
string Guid = "";
//true:新的Guid false:已经存在的Guid
bool GuidResult = true;
GuidResult = getNewGuid(dt_access.Rows[0]["jkdah"].ToString(), drs[i]["jyrq"].ToString(), out Guid);
if (dt_tjjgUpdate.Columns.Contains("guid") == false)
{
DataColumn dtColumn = new DataColumn("guid");
dtColumn.DefaultValue = Guid;
dt_tjjgUpdate.Columns.Add(dtColumn);
}
string[] sqllist = sqlcode.Split(new char[] { '|' });
if (sqlcode.Length == 0 || sqllist.Length < 2)
{
MessageBox.Show("仪器sql设定错误！");
return;
}
//体检结果
if (GuidResult == true)
{
//体检结果插入
form_lisbll.Add(dt_tjjgUpdate, sqllist[0]);
}
else
{
//体检结果更新
dt_tjjgUpdate.AcceptChanges();
dt_tjjgUpdate.Rows[0]["guid"] = Guid;
form_lisbll.Upd(dt_tjjgUpdate, sqllist[1]);
}
//签名
SaveJktjSignname(drs[i]["jyrq"].ToString(), dt_access.Rows[0]["jkdah"].ToString());
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// 把指定的文件导入到数据库中
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
private bool updateDataToTable()
{
////excel转换的数据集合
//ds_result = new DataSet();
string tablename = "";
try
{
if (ds_result != null && ds_result.Tables.Count > 0)
{
//计算数据总条数
dataCountAll = 0;
//遍历数据库表,计算数据总条数
for (int tableIndex = 0; tableIndex < ds_result.Tables.Count; tableIndex++)
{
dataCountAll = dataCountAll + ds_result.Tables[tableIndex].Rows.Count;
}
// 进度条控制
this.msgQf = "2";
//dataCountAll = dataCountAll;
dataCountCrrent = 0;
e_load.Value = 1;
this.OnValueChanged(e_load);
//int commitCount = 1000;
DataTable dt_result = ds_result.Tables[0].Clone();
//设定信息正确
if (dtRow != null && dtRow["dbTableName_to"] != null)
{
//数据库表名称
tablename = dtRow["dbTableName_to"].ToString();
}
//string columnFormat = "";
DataTable dt_column_value = new DataTable();
dt_column_value.Rows.Add();
//DataTable dt_column_where = new DataTable();
//dt_column_where.Rows.Add();
string sqlInsert = "";
string sqlInsertText = "";
string sqlInsertvalue = "";
string sqlUpdate = "";
//dt_column_value 同步字段对应
if (dtRow != null && dtRow["columnSet"] != null)
{
//值转换
string[] columnvalueList = dtRow["columnSet"].ToString().Split(new char[] { '|' });
for (int i = 0; i < columnvalueList.Length; i++)
{
string[] columnvalueListName = columnvalueList[i].Split(new char[] { ':' });
dt_column_value.Columns.Add(columnvalueListName[0]);
dt_column_value.Rows[0][columnvalueListName[0]] = columnvalueListName[1];
sqlInsertText = sqlInsertText + "," + columnvalueListName[1];
sqlInsertvalue = sqlInsertvalue + ",'[" + columnvalueListName[0] + "]'";
sqlUpdate = string.Format("{0}, {1}='[{2}]' ", sqlUpdate, columnvalueListName[1], columnvalueListName[0]);
}
}
string sqlWhere = "";
//dt_column_where updateToColumn
if (dtRow != null && dtRow["updateToColumn"] != null)
{
//值转换
string[] columnvalueList = dtRow["updateToColumn"].ToString().Split(new char[] { '|' });
for (int i = 0; i < columnvalueList.Length; i++)
{
string[] columnvalueListName = columnvalueList[i].Split(new char[] { ':' });
dt_column_value.Columns.Add(columnvalueListName[0]);
dt_column_value.Rows[0][columnvalueListName[0]] = columnvalueListName[1];
sqlWhere = string.Format(" {0} and {1}='[{2}]' ", sqlWhere, columnvalueListName[1], columnvalueListName[0]);
sqlInsertText = sqlInsertText + "," + columnvalueListName[1];
sqlInsertvalue = sqlInsertvalue + ",'[" + columnvalueListName[0] + "]'";
}
}
//插入语句
sqlInsert = string.Format(" insert into {0} ( {1},czy,gzz) values ({2},'{3}','{4}') ", tablename, sqlInsertText.Substring(1), sqlInsertvalue.Substring(1), UserInfo.userId, UserInfo.gzz);
//更新语句
sqlUpdate = string.Format(" update {0} set {1},czy='{3}',gzz='{4}' where 1=1 {2} ", tablename, sqlUpdate.Substring(1), sqlWhere, UserInfo.userId, UserInfo.gzz);
//检索语句
string sqlSelect = string.Format(" select * from {0} where 1=1 {1} ", tablename, sqlWhere);
//体检状态
string delete_tjzt = " delete from T_JK_TJZT where YLJGBM='" + UserInfo.Yybm + "' and JKDAH='[jkdabh]' and TJTYPE='1'";
string insert_tjzt = " insert into T_JK_TJZT (YLJGBM,JKDAH,ND,GZZBM,TJSJ,CZY,TJTYPE,ZT) values ('" + UserInfo.Yybm + "','[jkdabh]','" + DateTime.Now.Year.ToString() + "','" + UserInfo.gzz + "','[检查日期]','" + UserInfo.userId + "','1','1')";
ArrayList sqlArrayList_tem = new ArrayList();
DBAccess access = new DBAccess();
//遍历数据库表
for (int tableIndex = 0; tableIndex < ds_result.Tables.Count; tableIndex++)
{
//dt_result.TableName = tablename;
for (int rowIndex = 0; rowIndex < ds_result.Tables[tableIndex].Rows.Count; rowIndex++)
{
dataCountCrrent++;
string sqlInsert_tem = sqlInsert;
string sqlUpdate_tem = sqlUpdate;
string sqlSelect_tem = sqlSelect;
string delete_tjzt_tem = delete_tjzt;
string insert_tjzt_tem = insert_tjzt;
for (int i = 0; i < dt_column_value.Columns.Count; i++)
{
if ("检查日期".Equals(dt_column_value.Columns[i].ColumnName) == false)
{
string replaceStr = "[" + dt_column_value.Columns[i].ColumnName + "]";
sqlInsert_tem = sqlInsert_tem.Replace(replaceStr, ds_result.Tables[tableIndex].Rows[rowIndex][dt_column_value.Columns[i].ColumnName].ToString());
sqlUpdate_tem = sqlUpdate_tem.Replace(replaceStr, ds_result.Tables[tableIndex].Rows[rowIndex][dt_column_value.Columns[i].ColumnName].ToString());
sqlSelect_tem = sqlSelect_tem.Replace(replaceStr, ds_result.Tables[tableIndex].Rows[rowIndex][dt_column_value.Columns[i].ColumnName].ToString());
delete_tjzt_tem = delete_tjzt_tem.Replace(replaceStr, ds_result.Tables[tableIndex].Rows[rowIndex][dt_column_value.Columns[i].ColumnName].ToString());
insert_tjzt_tem = insert_tjzt_tem.Replace(replaceStr, ds_result.Tables[tableIndex].Rows[rowIndex][dt_column_value.Columns[i].ColumnName].ToString());
}
else
{
string replaceStr = "[" + dt_column_value.Columns[i].ColumnName + "]";
string replaceValue = ds_result.Tables[tableIndex].Rows[rowIndex][dt_column_value.Columns[i].ColumnName].ToString();
if (replaceValue.Length == 0)
{
replaceValue = DateTime.Now.ToString("yyyy-MM-dd");
}
else if (replaceValue.Length == 8)
{
replaceValue = replaceValue.Insert(6, "-").Insert(4, "-");
}
sqlInsert_tem = sqlInsert_tem.Replace(replaceStr, replaceValue);
sqlUpdate_tem = sqlUpdate_tem.Replace(replaceStr, replaceValue);
sqlSelect_tem = sqlSelect_tem.Replace(replaceStr, replaceValue);
delete_tjzt_tem = delete_tjzt_tem.Replace(replaceStr, replaceValue);
insert_tjzt_tem = insert_tjzt_tem.Replace(replaceStr, replaceValue);
}
}
DataTable dt_exist = access.ExecuteQueryBySql(sqlSelect_tem);
if (dt_exist != null && dt_exist.Rows.Count > 0)
{
sqlArrayList_tem.Add(sqlUpdate_tem);
}
else
{
sqlArrayList_tem.Add(sqlInsert_tem);
sqlArrayList_tem.Add(delete_tjzt_tem);
sqlArrayList_tem.Add(insert_tjzt_tem);
}
}
}
if (sqlArrayList_tem.Count > 0)
{
access.ExecuteNonQueryBySqlList(sqlArrayList_tem);
}
}
}
catch (Exception ex)
{
//progressBar_xz.Visible = false;
//MessageBox.Show(string.Format("表[{0}]{1}", tablename, ex.Message));
//throw ex;
ResultStatus = "4";
ResultStr = ex.Message;
return(false);
}
return(true);
}
/// <summary>
/// 登录处理
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void buttonLogin_Click(object sender, EventArgs e)
{
try
{
//是否初次登陆
string ifinitLogin = ConfigurationSettings.AppSettings["ifinitLogin"].ToString();
if (ifinitLogin.Equals("1") || (UserInfo.logInMode != null && UserInfo.logInMode.Equals("1")))
{
string yljgInit = comFz.Text;
if (txtCzy.Text.Equals("admin"))
{
DataTable dt = new DataTable();
dt.Columns.Add("userId");
dt.Columns.Add("jkda_czy");
dt.Columns.Add("gzz");
dt.Columns.Add("yljg");
dt.Rows.Add();
dt.Rows[0]["userId"] = "admin";
dt.Rows[0]["jkda_czy"] = "admin";
dt.Rows[0]["yljg"] = yljgInit;
dt.Rows[0]["gzz"] = "admin";
this.Tag = dt;
//登录信息
UserInfo.Yybm = yljgInit;
UserInfo.Username = "******";
UserInfo.userId = "admin";
UserInfo.gzz = "";
Main_Form formJktj = new Main_Form();
formJktj.Owner = this;
this.Hide();
formJktj.Show();
return;
}
else
{
MessageBox.Show("登录信息不正确，请确认");
return;
}
}
DBAccess dBAccess = new DBAccess();
//个人信息
string sql = "SELECT Xt_gg_czy.bm, Xt_gg_czy.pym, Xt_gg_czy.xm, Xt_gg_czy.yybh FROM Xt_gg_czy where bm='{bm}' and yybh='{yybm}' and (kl='{kl}' or kl is null )";
DataTable dtResult = dBAccess.ExecuteQueryBySql(sql.Replace("{bm}", txtCzy.Text).Replace("{yybm}", comFz.SelectedValue.ToString()).Replace("{kl}", txtMm.Text));
if (dtResult != null && dtResult.Rows.Count > 0)
{
//工作组
string sqlGzz = "SELECT YLJGBM, GZZBM, CZY, JKDA_CZY FROM T_JK_GZZ_CZY where CZY='{CZY}' and YLJGBM='{YLJGBM}' ";
DataTable dtGzz = dBAccess.ExecuteQueryBySql(sqlGzz.Replace("{CZY}", txtCzy.Text).Replace("{YLJGBM}", comFz.SelectedValue.ToString()));
DataTable dt = new DataTable();
if (dtGzz != null && dtGzz.Rows.Count > 0)
{
//DataTable dt = new DataTable();
dt.Columns.Add("userId");
dt.Columns.Add("jkda_czy");
dt.Columns.Add("gzz");
dt.Columns.Add("yljg");
dt.Rows.Add();
dt.Rows[0]["userId"] = txtCzy.Text;
dt.Rows[0]["jkda_czy"] = dtGzz.Rows[0]["JKDA_CZY"].ToString();
dt.Rows[0]["yljg"] = comFz.SelectedValue.ToString();
dt.Rows[0]["gzz"] = dtGzz.Rows[0]["GZZBM"].ToString();
//登录信息
UserInfo.Yybm = comFz.SelectedValue.ToString();
UserInfo.Yymc = comFz.Text.ToString();
UserInfo.Username = dtResult.Rows[0]["xm"].ToString();
UserInfo.userId = txtCzy.Text;
UserInfo.gzz = dtGzz.Rows[0]["GZZBM"].ToString();
}
else
{
//用户没有分配工作组
dt.Columns.Add("userId");
dt.Columns.Add("jkda_czy");
dt.Columns.Add("gzz");
dt.Columns.Add("yljg");
dt.Rows.Add();
dt.Rows[0]["userId"] = txtCzy.Text;
dt.Rows[0]["jkda_czy"] = "";
dt.Rows[0]["yljg"] = comFz.SelectedValue.ToString();
dt.Rows[0]["gzz"] = "";
//登录信息
UserInfo.Yybm = comFz.SelectedValue.ToString();
UserInfo.Yymc = comFz.Text.ToString();
UserInfo.Username = dtResult.Rows[0]["xm"].ToString();
UserInfo.userId = txtCzy.Text;
UserInfo.gzz = "";
MessageBox.Show("该用户没有分配工作组！");
}
this.Tag = dt;
Main_Form formJktj = new Main_Form();
formJktj.Owner = this;
this.Hide();
formJktj.Show();
////测试lis
//jktj formJktj = new jktj();
//formJktj.Owner = this;
//this.Hide();
//formJktj.Show();
//formJktj.Focus();
}
else
{
MessageBox.Show("登录信息不正确，请确认");
txtCzy.Focus();
}
}
catch (Exception ex)
{
MessageBox.Show("登录信息不正确，请确认！" + ex.Message);
}
}
