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); } }