private void GridUploadshow() { string mono = Convert.ToString(Session["MobileNumber"]); string sql = "select id, upload_date,actual_filename,url_link from uploaddetails inner join UserMaster on folderuserid =usrUserId where usrMobileNo='" + mono + "' order by id desc"; //string sql = "select upload_date,actual_filename from uploaddetails where folderuserid='"+userid.ToString()+"'"; DataSet dt = cc.ExecuteDataset(sql); GridUpload.DataSource = dt.Tables[0]; GridUpload.DataBind(); }
//导入 protected void btnImport_Click(object sender, EventArgs e) { if (DropDictLab.SelectedValue == null) { MessageBoxShow("请先选择分点!"); return; } if (DropCustomer.SelectedIndex == 0) { MessageBoxShow("请先选择单位!"); return; } if (dpProvince.SelectedValue == "-1" || dpCity.SelectedValue == "-1") { MessageBoxShow("请选择省、市"); return; } string fileType = fileExcel.PostedFile.ContentType; if (fileType != "application/octet-stream" && fileType != "application/vnd.ms-excel" && fileType != "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" && fileType != "application/kset") { MessageBoxShow("请上传指定格式的Excel文件"); return; } if (ck1.Checked) { if (string.IsNullOrEmpty(txtAddress.Text.Trim()) || string.IsNullOrEmpty(txtRecName.Text.Trim()) || string.IsNullOrEmpty(txtTelphone.Text.Trim())) { MessageBoxShow("邮寄地址、收件人、联系电话不能为空"); return; } } string fileName = fileExcel.ShortFileName; fileName = fileName.Replace(":", "_").Replace(" ", "_").Replace("\\", "_").Replace("/", "_"); fileName = String.Format("{0}_{1}", DateTime.Now.Ticks, fileName); string savePath = Server.MapPath("~/upload/ExcelFiles/" + fileName); if (fileExcel.HasFile) { if (!Directory.Exists(Server.MapPath("~/upload/ExcelFiles"))) { Directory.CreateDirectory(Server.MapPath("~/upload/ExcelFiles")); } try { fileExcel.SaveAs(savePath); } catch (Exception ex) { MessageBoxShow("上传错误:" + ex.Message); return; } } DataTable dt; try { dt = RenderDataTableFromExcel(savePath); if (dt == null || dt.Rows.Count == 0) { MessageBoxShow("上传的文件数据不能为空!"); return; } //验证上传的excel是否符合模板标准,否则不让上传订单并删除上传的文件 string str = CheckDate(dt, fileName); if (!string.IsNullOrEmpty(str)) { MessageBoxShow(str); File.Delete(savePath);//删除文件 return; } } catch (Exception ex) { MessageBoxShow("请上传指定格式的Excel文件!\r\n提示信息:" + ex.Message); return; } List <Dicttestitem> TestItemList = loginservice.GetLoginDicttestitemList(); //项目字典表 List <Dictproductdetail> ProductDetail = loginservice.GetLoginDictproductdetail(); //套餐组合字典 //查询分点+公用套餐 List <Dicttestitem> productlistTemp = new DicttestitemService().GetProduct(TypeParse.StrToDouble(DropCustomer.SelectedValue, 0)); string _productname = string.Empty; #region 读取excel数据并上传订单 try { for (int i = (dt.Rows.Count - 1); i >= 0; i--) { bool b = false;//添加是否成功 string errstr = ""; DataRow dr = dt.Rows[i]; string productTestCode = dr["套餐代码"].ToString().Replace('_', ' ').Trim(); string sex = dr["性别"].ToString().Replace('_', ' ').Trim() == "女" ? "F" : (dr["性别"].ToString().Replace('_', ' ').Trim() == "男" ? "M" : "U"); List <Dicttestitem> productList = productlistTemp.Where <Dicttestitem>(c => c.Testcode == productTestCode && (c.Forsex == sex || c.Forsex == "B")).ToList <Dicttestitem>(); List <Dicttestitem> grouptestList = new List <Dicttestitem>(); Dicttestitem productinfo = null; if (productList.Count == 0) { errstr = String.Format("套餐代码[{0}]无匹配项,请查看性别是否匹配或者是否有该套餐。", productTestCode); SetTableValue(false, errstr, dr); continue; } else if (productList.Count > 1) { errstr = String.Format("存在多个套餐代码为[{0}]的套餐", productTestCode); SetTableValue(false, errstr, dr); continue; } else { productinfo = productList.First <Dicttestitem>(); List <OrderRegister> _gridtestList = null; string msg = registerservice.AddProduct(ref _gridtestList, sex, productinfo.Dicttestitemid, false, Userinfo, ref _productname, null); if (msg != string.Empty) { //错误 SetTableValue(false, msg, dr); continue; } string barcode = dr["条码号"].ToString().Replace('_', ' ').Trim(); if (barcode != string.Empty && barcode.Length != 12) { errstr = string.Format("条码号[{0}]必须为12位数字,可以为空!", barcode); SetTableValue(false, errstr, dr); continue; } if (barcode != string.Empty && barcode.Substring(barcode.Length - 2) != "00") { errstr = string.Format("条码号[{0}]必须以00结尾!", barcode); SetTableValue(false, errstr, dr); continue; } if (barcodeservice.CheckBarCode(barcode))//条码号存在 { errstr = string.Format("此条码号[{0}]已在本系统内生成,请更改条码号!", barcode); SetTableValue(false, errstr, dr); continue; } //套餐下组合项目 IEnumerable <Dictproductdetail> IEgroup = ProductDetail.Where <Dictproductdetail>(c => c.Productid == productinfo.Dicttestitemid); bool iscontinue = true; foreach (Dictproductdetail item in IEgroup) { IEnumerable <Dicttestitem> IEgruptest = TestItemList.Where <Dicttestitem>(c => c.Dicttestitemid == item.Testgroupid); if (IEgruptest.Count() <= 0) { errstr = string.Format("没有找到套餐[{0}]下ID为[{1}]的组合!", _productname, item.Testgroupid); SetTableValue(false, errstr, dr); continue; } Dicttestitem groupinfo = IEgruptest.First <Dicttestitem>(); //校验性别是否符合 string str = registerservice.checkSex(groupinfo.Dicttestitemid, sex); if (str != string.Empty) { SetTableValue(false, str, dr); iscontinue = false; break; } groupinfo.Productid = productinfo.Dicttestitemid; groupinfo.Productname = productinfo.Testname; ///套餐名 groupinfo.IsActive = "1"; //是否停止测试 groupinfo.Isadd = "0"; ///是否追加 groupinfo.Billed = "0"; groupinfo.Sendbilled = "0"; groupinfo.Adduserid = null;//追加人ID if (barcode == string.Empty) { IEnumerable <Dicttestitem> IEtempbarcodeList = grouptestList.Where <Dicttestitem>(c => c.Tubegroup == groupinfo.Tubegroup); if (IEtempbarcodeList.Count() > 0) { groupinfo.Barcode = IEtempbarcodeList.First <Dicttestitem>().Barcode; } else { groupinfo.Barcode = registerservice.GetBarCode(); } } else { groupinfo.Barcode = barcode; } //获取外包客户 Dictproductdetail detail = ProductDetail.Where <Dictproductdetail>(c => c.Productid == productinfo.Dicttestitemid && c.Testgroupid == groupinfo.Dicttestitemid).First <Dictproductdetail>(); groupinfo.Sendoutcustomerid = detail.Sendoutcustomerid; grouptestList.Add(groupinfo); } if (!iscontinue) { continue; } } #region >>>> zhouy 不存在此会员添加会员 string realname = dr["姓名"].ToString().Trim(); Dictmember member = new Dictmember() { Realname = realname }; string idnumber = dr["身份证"].ToString().Trim(); member.Idnumber = idnumber; //检查会员 errstr = registerservice.checkmember(null, ref member); if (errstr != string.Empty) { SetTableValue(false, errstr, dr); continue; } //List<Dictmember> memberList = memberservice.GetDictmemberList(member); //if (memberList.Count == 1)//存在此会员记录且只有一条 //{ // member = memberList[0]; // member.isAdd = false;//标识是否添加 //} //else //{ // member.Dictmemberid = loginservice.getSeqID("SEQ_DICTMEMBER"); // member.Islock = "F";//是否锁定 // member.isAdd = true; //} member.Nickname = member.Realname; member.Sex = sex; DateTime datebirthday; bool dateb = DateTime.TryParse(dr["出生日期"].ToString(), out datebirthday); if (dateb) { member.Birthday = datebirthday; } member.Addres = dr["住址"].ToString().Trim(); member.Phone = dr["电话"].ToString().Trim(); if (!string.IsNullOrWhiteSpace(dr["手机"].ToString().Trim())) { double a; bool mobileb = double.TryParse(dr["手机"].ToString().Trim(), out a); if (!mobileb) { SetTableValue(false, "手机号码填写不正确,不要有特殊字符,[-]也不能包含", dr); continue; } } member.Mobile = dr["手机"].ToString().Trim(); member.Email = dr["邮箱"].ToString().Trim(); #endregion double year = 0, month = 0, day = 0; double hours = 0;//小时 double age = 0; string agestr = dr["年龄"].ToString().Replace('_', ' ').Trim(); bool ageb = double.TryParse(agestr, out age); if (member.Birthday == null) { if (agestr != string.Empty && ageb) { year = age; day = age * 365; member.Birthday = datebirthday = DateTime.Now.AddDays((0 - day)); } else { SetTableValue(false, "生日和年龄必须填写一项或者两项均填写错误", dr); continue; } } TimeSpan ts = DateTime.Now - Convert.ToDateTime(member.Birthday);//时间差 year = Math.Truncate((double)(ts.Days / 365)); month = (ts.Days % 365) / 30; day = (ts.Days % 365) % 30; hours = ts.TotalHours; Orders _orders = new Orders(); _orders.Ordernum = new ProRegisterService().GetOrderNum();; //体检流水号 _orders.Remarks = dr["备注"].ToString().Trim(); //备注 _orders.Dictmemberid = member.Dictmemberid; //会员ID _orders.Dictcustomerid = Convert.ToDouble(DropCustomer.SelectedValue); //所属客户ID 界面选择 _orders.Realname = member.Realname; _orders.Sex = member.Sex; //性别 对应INITBASIC表 _orders.Caculatedage = hours; //计算后的年龄(小时为单位) _orders.Age = string.Format("{0}岁{1}月{2}日{3}时", year, month, day, 0); //年龄字符串拼接 岁月日时 _orders.Enterby = Userinfo.userName; //录入人 _orders.Ordertestlst = _productname + ","; //项目清单(冗余字段) _orders.Dictlabid = Convert.ToDouble(DropDictLab.SelectedValue); //实验室分点 _orders.Ordersource = "1"; //单位上传 全是单位来源 _orders.Ismarried = dr["婚否"].ToString() == "未婚" ? "0" : (dr["婚否"].ToString() == "已婚" ? "1" : "2"); _orders.Section = dr["部门"].ToString().Trim(); _orders.Status = ((int)ParamStatus.OrdersStatus.BarCodePrint).ToString(); DateTime samplingdate; bool s = DateTime.TryParse(dr["采样日期"].ToString(), out samplingdate); if (s) { TimeSpan timespan = DateTime.Now - samplingdate; if (Math.Abs(timespan.Days) > 30) { SetTableValue(false, "采样时间与当前时间相差不能超过一个月", dr); continue; } _orders.SamplingDate = samplingdate; } _orders.Province = dpProvince.SelectedValue == "-1" ? "" : dpProvince.SelectedText; _orders.City = dpCity.SelectedValue == "-1" ? "" : dpCity.SelectedText; _orders.County = dpCounty.SelectedValue == "-1" ? "" : dpCounty.SelectedText; if (ck1.Checked) { _orders.PostAddress = Regex.Replace(txtAddress.Text, @"\s", ""); _orders.ContactNumber = Regex.Replace(txtTelphone.Text, @"\s", ""); _orders.Recipient = Regex.Replace(txtRecName.Text, @"\s", ""); } else { _orders.PostAddress = dr["住址"].ToString().Trim(); _orders.Recipient = realname; _orders.ContactNumber = dr["手机"].ToString().Trim(); } //add 20160421 增加营业区、场次号 _orders.Area = dr["营业区"].ToString().Replace('_', ' ').Trim(); _orders.BatchNumber = dr["场次号"].ToString().Replace('_', ' ').Trim(); //add 20160530 增加客户经理字段 if (dt.Columns.Contains("客户经理")) { _orders.AccountManager = dr["客户经理"].ToString().Replace('_', ' ').Trim(); } //add 20160612 增加本批标本总数字段 if (dt.Columns.Contains("本批标本总数")) { _orders.SpecimenCount = dr["本批标本总数"].ToString().Replace('_', ' ').Trim(); } b = registerservice.insertUpdateOrders("单位批量上传", "", true, productList, grouptestList, member, _orders, "", ref errstr); SetTableValue(b, errstr, dr); } GridUpload.DataSource = dt; GridUpload.DataBind(); } catch (Exception ee) { string str = string.Format("导入失败,异常信息:{0}【Excel格式请严格参照导入模版说明】\n\n导入过程中如有疑问,请将异常信息截图后发与系统管理员。", ee.Message); MessageBoxShow(str); } #endregion }