/// <summary> /// 查询优惠券列表 /// </summary> /// <param name="name"></param> /// <param name="value"></param> /// <param name="issue_status"></param> /// <param name="startdate1"></param> /// <param name="startdate2"></param> /// <param name="enddate1"></param> /// <param name="enddate2"></param> /// <param name="orderby"></param> /// <param name="orderbyCol"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="record"></param> /// <param name="error"></param> /// <returns></returns> public List<promotion_coupons> GetPromotionCouponsList(string name, int value, int issue_status, DateTime? startdate1, DateTime? startdate2, DateTime? enddate1, DateTime? enddate2, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out int record, out string error) { error = string.Empty; record = 0; return new promotion_events_DA().GetPromotionCouponsList(name, value, issue_status, startdate1, startdate2, enddate1, enddate2, orderby, orderbyCol, pageIndex, pageSize, out record, out error); }
public List<md_cashdraw_app> SearchCashdrawList(int drid, DateTime? app_time1, DateTime? app_time2, string opuser, DateTime? optime1, DateTime? optime2, int opstatus, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out int record, out string error) { record = 0; error = string.Empty; try { md_cashdraw_app_DA da = new md_cashdraw_app_DA(); var resultList = da.SearchCashdrawList(drid, app_time1, app_time2, opuser, optime1, optime2, opstatus, orderby, orderbyCol, pageIndex, pageSize, out record,out error); return resultList; } catch (Exception ex) { error += BaseTool.FormatExceptionMessage(ex); return null; } }
/// <summary> /// 对服务订单表表各个字段查询 /// </summary> /// <param name="hospital_id"></param> /// <param name="drid"></param> /// <param name="visit_date1"></param> /// <param name="visit_date2"></param> /// <param name="visit_time"></param> /// <param name="service_type"></param> /// <param name="status"></param> /// <param name="createtime1"></param> /// <param name="createtime2"></param> /// <param name="orderby">顺序 降序</param> /// <param name="orderbyCol">排序字段,传入patient_order的字段,不需要前缀</param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="error"></param> /// <returns>null则出错</returns> public List<patient_order> SearchPatientOrderList(int patient_id, int drid, int service_type, DateTime? createtime, DateTime? createtime2, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out string error) { error = string.Empty; try { patient_order_DA da = new patient_order_DA(); var resultList = da.SearchPatientOrderList(patient_id, drid, service_type, createtime,createtime2, orderby, orderbyCol, pageIndex, pageSize, out error); return resultList; } catch (Exception ex) { error += BaseTool.FormatExceptionMessage(ex); return null; } }
/// <summary> /// 药品查询 /// </summary> /// <param name="drugname">药品名</param> /// <param name="standard">规格</param> /// <param name="producerid">厂家id</param> /// <param name="cust_name">科组</param> /// <param name="sub_name">病种</param> /// <param name="orderby"></param> /// <param name="orderbyCol"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="error"></param> /// <returns></returns> public List<md_druginfo> SearchDruginfoList(string drugname, string standard, int producerid, string cust_name, string sub_name, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out string error) { error = string.Empty; try { md_druginfo_DA da = new md_druginfo_DA(); var resultList = da.SearchDruginfoList(drugname, standard, producerid, cust_name, sub_name, orderby, orderbyCol, pageIndex, pageSize, out error); return resultList; } catch (Exception ex) { error += BaseTool.FormatExceptionMessage(ex); return null; } }
/// <summary> /// 对出诊表各个字段查询 /// </summary> /// <param name="hospital_id"></param> /// <param name="drid"></param> /// <param name="visit_date1"></param> /// <param name="visit_date2"></param> /// <param name="visit_time"></param> /// <param name="service_type"></param> /// <param name="status"></param> /// <param name="createtime1"></param> /// <param name="createtime2"></param> /// <param name="orderby">顺序 降序</param> /// <param name="orderbyCol">排序字段,传入dr_addvisit的字段,不需要前缀</param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="error"></param> /// <returns>null则出错</returns> public List<dr_addvisit> SearchAddVisitList(int hospital_id, int drid, DateTime? visit_date1, DateTime? visit_date2, string visit_time, int service_type, int status, DateTime? createtime1, DateTime? createtime2, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out string error) { error = string.Empty; try { dr_addvisit_DA da = new dr_addvisit_DA(); var resultList = da.SearchVisitList(hospital_id, drid, visit_date1, visit_date2, visit_time, service_type, status, createtime1, createtime2, orderby, orderbyCol, pageIndex, pageSize, out error); return resultList; } catch (Exception ex) { error += BaseTool.FormatExceptionMessage(ex); return null; } }
public List<patient_recipelist> SearchReciptList(int drid, int medical_rcd_id, int hospital_id, int patient_id, string cust_name, DateTime? issue_time1, DateTime? issue_time2, DateTime? createtime1, DateTime? createtime2, string check_result, int fileid, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out string error) { error = string.Empty; try { patient_recipelist_DA recipeDa = new patient_recipelist_DA(); var resultList = recipeDa.SearchReciptList(drid,medical_rcd_id, hospital_id, patient_id, cust_name, issue_time1, issue_time2, createtime1, createtime2, check_result, fileid, orderby, orderbyCol, pageIndex, pageSize, out error); return resultList; } catch (Exception ex) { error += BaseTool.FormatExceptionMessage(ex); return null; } }
public List<dr_pre_visit> SearchPreVisitList(int drid, int visit_id, int patient_id, DateTime? pre_date1, DateTime? pre_date2, string pre_time, int pre_type, DateTime? createtime1, DateTime? createtime2, int status, DateTime? visit_date1, DateTime? visit_date2, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out string error) { error = string.Empty; try { dr_visit_DA da = new dr_visit_DA(); var resultList = da.SearchPreVisitList(drid ,visit_id, patient_id, pre_date1, pre_date2, pre_time, pre_type, createtime1, createtime2, status , visit_date1, visit_date2, orderby, orderbyCol, pageIndex, pageSize, out error); return resultList; } catch (Exception ex) { error += BaseTool.FormatExceptionMessage(ex); return null; } }
public List<chk_demo> SearchChkTypeList( int typeid, string chk_item, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out string error) { error = string.Empty; try { chk_info_DA chkDa = new chk_info_DA(); var resultList = chkDa.SearchChkTypeList( typeid, chk_item, orderby, orderbyCol, pageIndex, pageSize, out error); return resultList; } catch (Exception ex) { error += BaseTool.FormatExceptionMessage(ex); return null; } }
public List<chk_info> SearchChkInfoList( int medical_id, int patient_id, int chk_type_id, int chk_demo_id, int fileid, int drid, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out string error) { error = string.Empty; try { chk_info_DA chkDa = new chk_info_DA(); var resultList = chkDa.SearchChkInfoList( medical_id, patient_id, chk_type_id, chk_demo_id, fileid,drid, orderby, orderbyCol, pageIndex, pageSize, out error); return resultList; } catch (Exception ex) { error += BaseTool.FormatExceptionMessage(ex); return null; } }
public List<patient_message> SearchMessgaeList(int hispital_id, int patientid, int drid, int status, string contents, DateTime? createtime1, DateTime? createtime2, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out string error) { error = string.Empty; try { patient_message_DA messageDa = new patient_message_DA(); var resultList = messageDa.SearchMessgaeList(hispital_id,patientid, drid, status, contents,createtime1, createtime2, orderby, orderbyCol, pageIndex, pageSize, out error); return resultList; } catch (Exception ex) { error += BaseTool.FormatExceptionMessage(ex); return null; } }
public List<patient_medical_rcd> SearchMedicalList(string code, int hospital_id, int drid, int patient_id, string cust_name, string rcd_result, DateTime? rcd_time1, DateTime? rcd_time2, DateTime? createtime1, DateTime? createtime2, int fileid, string next_chk_item, string next_visit_item, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out string error) { error = string.Empty; try { patient_medical_rcd_DA medicalDa = new patient_medical_rcd_DA(); var resultList = medicalDa.SearchMedicalList(code, hospital_id, drid, patient_id, cust_name, rcd_result, rcd_time1, rcd_time2, createtime1,createtime2, fileid, next_chk_item, next_visit_item, orderby, orderbyCol, pageIndex, pageSize, out error); return resultList; } catch (Exception ex) { error += BaseTool.FormatExceptionMessage(ex); return null; } }
public List<md_druginfo> SearchDruginfoList(string drugname, string standard, int producerid, string cust_name, string sub_name, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out string error) { error = string.Empty; try { #region Command string selectSql = string.Format("select * from md_druginfo WHERE TRUE "); StringBuilder conditionSb = new System.Text.StringBuilder(); List<MySqlParameter> paraList = new List<MySqlParameter>(); #endregion #region 搜索条件 if (!string.IsNullOrEmpty(drugname)) { conditionSb.Append(" AND drugname LIKE CONCAT('%', @drugname, '%') "); paraList.Add(new MySqlParameter("drugname", drugname)); } if (!string.IsNullOrEmpty(standard)) { conditionSb.Append(" AND standard LIKE CONCAT('%', @standard, '%') "); paraList.Add(new MySqlParameter("standard", standard)); } if (producerid > 0) { conditionSb.Append("AND producerid = @producerid"); paraList.Add(new MySqlParameter("producerid", producerid)); } if (!string.IsNullOrEmpty(cust_name)) { conditionSb.Append(" AND cust_name LIKE CONCAT('%', @cust_name, '%') "); paraList.Add(new MySqlParameter("cust_name", cust_name)); } if (!string.IsNullOrEmpty(sub_name)) { conditionSb.Append(" AND sub_name LIKE CONCAT('%', @sub_name, '%') "); paraList.Add(new MySqlParameter("sub_name", sub_name)); } #endregion #region 排序 分页 string orderbyStr = string.Empty; if (!string.IsNullOrEmpty(orderbyCol) && orderby.HasValue) orderbyStr = orderbyFormat.getSortStr(orderby.Value, orderbyCol); else orderbyStr = " order by pkid "; selectSql += conditionSb.ToString() + orderbyStr; if (pageIndex > 0) { selectSql += " LIMIT @pageIndex , @pageSize "; paraList.Add(new MySqlParameter("pageIndex", (pageIndex - 1) * pageSize)); paraList.Add(new MySqlParameter("pageSize", pageSize)); } #endregion #region 执行 var resultList = sqlHelper.ExecuteObjects<md_druginfo>(selectSql, paraList.ToArray()).ToList(); return resultList; #endregion } catch (Exception ex) { error += BaseTool.FormatExceptionMessage(ex); return null; } }
/// <summary> /// 对出诊表各个字段查询 /// </summary> /// <param name="hospital_id"></param> /// <param name="drid"></param> /// <param name="visit_date1"></param> /// <param name="visit_date2"></param> /// <param name="visit_time"></param> /// <param name="service_type"></param> /// <param name="status"></param> /// <param name="createtime1"></param> /// <param name="createtime2"></param> /// <param name="orderby">顺序 降序</param> /// <param name="orderbyCol">排序字段,传入dr_visit的字段,不需要前缀</param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="error"></param> /// <returns>null则出错</returns> public List<dr_addvisit> SearchVisitList(int hospital_id, int drid, DateTime? visit_date1, DateTime? visit_date2, string visit_time, int service_type, int status, DateTime? createtime1, DateTime? createtime2, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out string error) { error = string.Empty; try { #region Command string selectSql = string.Format("select * from dr_addvisit WHERE TRUE "); StringBuilder conditionSb = new System.Text.StringBuilder(); List<MySqlParameter> paraList = new List<MySqlParameter>(); #endregion #region 搜索条件 if (hospital_id > 0) { conditionSb.Append("AND hospital_id = @hospital_id"); paraList.Add(new MySqlParameter("hospital_id", hospital_id)); } if (drid > 0) { conditionSb.Append("AND drid = @drid"); paraList.Add(new MySqlParameter("drid", drid)); } if (visit_date1.HasValue) { conditionSb.Append(" AND DATEDIFF(visit_date,@visit_date1) >= 0 "); paraList.Add(new MySqlParameter("visit_date1", visit_date1.Value)); } if (visit_date2.HasValue) { conditionSb.Append(" AND DATEDIFF(visit_date,@visit_date2) <= 0 "); paraList.Add(new MySqlParameter("visit_date2", visit_date2.Value)); } if (!string.IsNullOrEmpty(visit_time)) { conditionSb.Append(" AND visit_time = @visit_time "); paraList.Add(new MySqlParameter("visit_time", visit_time)); } if (service_type > 0) { conditionSb.Append("AND service_type = @service_type"); paraList.Add(new MySqlParameter("service_type", service_type)); } if (status > 0) { conditionSb.Append("AND status = @status"); paraList.Add(new MySqlParameter("status", status)); } if (createtime1.HasValue) { conditionSb.Append(" AND DATEDIFF(createtime,@createtime1) >= 0 "); paraList.Add(new MySqlParameter("createtime1", createtime1.Value)); } if (createtime2.HasValue) { conditionSb.Append(" AND DATEDIFF(createtime,@createtime2) <= 0 "); paraList.Add(new MySqlParameter("createtime2", createtime2.Value)); } #endregion #region 排序 分页 string orderbyStr = string.Empty; if (!string.IsNullOrEmpty(orderbyCol) && orderby.HasValue) orderbyStr = orderbyFormat.getSortStr(orderby.Value, orderbyCol); else orderbyStr = " order by pkid "; selectSql += conditionSb.ToString() + orderbyStr; if (pageIndex > 0) { selectSql += " LIMIT @pageIndex , @pageSize "; paraList.Add(new MySqlParameter("pageIndex", (pageIndex - 1) * pageSize)); paraList.Add(new MySqlParameter("pageSize", pageSize)); } #endregion #region 执行 var resultList = db.Database.SqlQuery<dr_addvisit>(selectSql, paraList.ToArray()).ToList(); return resultList; #endregion } catch (Exception ex) { error += BaseTool.FormatExceptionMessage(ex); return null; } }
public List<chk_info> SearchChkInfoList( int medical_id, int patient_id, int chk_type_id, int chk_demo_id, int fileid, int drid, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out string error) { error = string.Empty; try { #region Command string selectSql = string.Format("select *,pat.name as patient_name,chk_type.name as chk_name,rcd.createtime as chk_time from chk_info chk left join patient_medical_rcd rcd on chk.medical_id=rcd.pkid left join patient_info pat on chk.patient_id=pat.pkid left join chk_type on chk.chk_type_id on chk_type.pkid WHERE TRUE "); StringBuilder conditionSb = new System.Text.StringBuilder(); List<MySqlParameter> paraList = new List<MySqlParameter>(); #endregion #region 搜索条件 if (medical_id > 0) { conditionSb.Append(" AND medical_id = @medical_id "); paraList.Add(new MySqlParameter("medical_id", medical_id)); } if (patient_id > 0) { conditionSb.Append(" AND chk.patient_id = @patient_id "); paraList.Add(new MySqlParameter("patient_id", patient_id)); } if (chk_type_id > 0) { conditionSb.Append(" AND chk_type_id = @chk_type_id "); paraList.Add(new MySqlParameter("chk_type_id", chk_type_id)); } if (chk_demo_id > 0) { conditionSb.Append(" AND chk_demo_id = @chk_demo_id "); paraList.Add(new MySqlParameter("chk_demo_id", chk_demo_id)); } if (fileid > 0) { conditionSb.Append(" AND fileid = @fileid "); paraList.Add(new MySqlParameter("fileid", fileid)); } if (drid > 0) { conditionSb.Append(" AND patient_medical_rcd.drid = @drid "); paraList.Add(new MySqlParameter("drid", drid)); } #endregion #region 排序 分页 string orderbyStr = string.Empty; if (!string.IsNullOrEmpty(orderbyCol) && orderby.HasValue) orderbyStr = orderbyFormat.getSortStr(orderby.Value, orderbyCol); else orderbyStr = " order by chk.pkid "; selectSql += conditionSb.ToString() + orderbyStr; if (pageIndex > 0) { selectSql += " LIMIT @pageIndex , @pageSize "; paraList.Add(new MySqlParameter("pageIndex", (pageIndex - 1) * pageSize)); paraList.Add(new MySqlParameter("pageSize", pageSize)); } #endregion #region 执行 var resultList = db.Database.SqlQuery<chk_info>(selectSql, paraList.ToArray()).ToList(); return resultList; #endregion } catch (Exception ex) { error += BaseTool.FormatExceptionMessage(ex); return null; } }
public List<chk_demo> SearchChkTypeList( int typeid, string chk_item, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out string error) { error = string.Empty; try { #region Command string selectSql = string.Format("select * from chk_demo WHERE TRUE "); StringBuilder conditionSb = new System.Text.StringBuilder(); List<MySqlParameter> paraList = new List<MySqlParameter>(); #endregion #region 搜索条件 if (typeid > 0) { conditionSb.Append(" AND typeid = @typeid "); paraList.Add(new MySqlParameter("typeid", typeid)); } if (!string.IsNullOrEmpty(chk_item)) { conditionSb.Append(" AND chk_item like CONCAT('%'@,chk_item,'%') "); paraList.Add(new MySqlParameter("chk_item", chk_item)); } #endregion #region 排序 分页 string orderbyStr = string.Empty; if (!string.IsNullOrEmpty(orderbyCol) && orderby.HasValue) orderbyStr = orderbyFormat.getSortStr(orderby.Value, orderbyCol); else orderbyStr = " order by pkid "; selectSql += conditionSb.ToString() + orderbyStr; if (pageIndex > 0) { selectSql += " LIMIT @pageIndex , @pageSize "; paraList.Add(new MySqlParameter("pageIndex", (pageIndex - 1) * pageSize)); paraList.Add(new MySqlParameter("pageSize", pageSize)); } #endregion #region 执行 var resultList = db.Database.SqlQuery<chk_demo>(selectSql, paraList.ToArray()).ToList(); return resultList; #endregion } catch (Exception ex) { error += BaseTool.FormatExceptionMessage(ex); return null; } }
public List<patient_message> SearchMessgaeList(int hispital_id, int patientid, int drid, int status, string contents, DateTime? createtime1, DateTime? createtime2, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out string error) { error = string.Empty; try { #region Command string selectSql = string.Format("select p.createtime,d.name as doctor_name,i.name as patient_name,p.status,contents from patient_message p left join patient_info i on p.patientid =i.pkid left join md_docter d on p.drid=d.pkid left join md_hospital h on d.hispital_id=h.pkid WHERE TRUE "); StringBuilder conditionSb = new System.Text.StringBuilder(); List<MySqlParameter> paraList = new List<MySqlParameter>(); #endregion #region 搜索条件 if (hispital_id > 0) { conditionSb.Append("AND h.pkid = @hispital_id"); paraList.Add(new MySqlParameter("hispital_id", drid)); } if (!string.IsNullOrEmpty(contents)) { conditionSb.Append(" AND p.contents like CONCAT('%', @contents , '%') "); paraList.Add(new MySqlParameter("contents", contents)); } if (drid > 0) { conditionSb.Append("AND p.drid = @drid"); paraList.Add(new MySqlParameter("drid", drid)); } if (patientid > 0) { conditionSb.Append("AND p.patientid = @patientid"); paraList.Add(new MySqlParameter("patientid", patientid)); } if (status > 0) { conditionSb.Append("AND p.status = @status"); paraList.Add(new MySqlParameter("status", status)); } if (createtime1.HasValue) { conditionSb.Append(" AND DATEDIFF(p.createtime,@createtime1) >= 0 "); paraList.Add(new MySqlParameter("createtime1", createtime1.Value)); } if (createtime2.HasValue) { conditionSb.Append(" AND DATEDIFF(p.createtime,@createtime2) <= 0 "); paraList.Add(new MySqlParameter("createtime2", createtime2.Value)); } #endregion #region 排序 分页 string orderbyStr = string.Empty; if (!string.IsNullOrEmpty(orderbyCol) && orderby.HasValue) orderbyStr = orderbyFormat.getSortStr(orderby.Value, orderbyCol); else orderbyStr = " order by p.pkid "; selectSql += conditionSb.ToString() + orderbyStr; if (pageIndex > 0) { selectSql += " LIMIT @pageIndex , @pageSize "; paraList.Add(new MySqlParameter("pageIndex", (pageIndex - 1) * pageSize)); paraList.Add(new MySqlParameter("pageSize", pageSize)); } #endregion #region 执行 var resultList = db.Database.SqlQuery<patient_message>(selectSql, paraList.ToArray()).ToList(); return resultList; #endregion } catch (Exception ex) { error += BaseTool.FormatExceptionMessage(ex); return null; } }
/// <summary> /// 返回排序SQL语句 /// </summary> /// <param name="srt">排序参数</param> /// <param name="cols">排序字段</param> /// <returns></returns> public static string getSortStr(orderbyEnum srt, string cols) { string result = string.Empty; switch (srt) { case orderbyEnum.降序: result = " order by " + cols + " desc "; break; case orderbyEnum.升序: result = " order by " + cols + " asc "; break; default: result = " order by " + cols + " desc "; break; } return result; }
public List<promotion_coupons_detail> SearchPromotionCouponsList(int coupons_id, int use_status, int business_type, int userid, int drid, DateTime? sendtime1, DateTime? sendtime2, DateTime? usetime1, DateTime? usetime2, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out int record, out string error) { error = string.Empty; record = 0; try { //string selectSql = "select c.values as valuess,p.name as patient_name,m.name as doctor_name,d.pkid as pkid,coupons_id,use_status,code,sendtime,usetime,business_type from promotion_coupons_detail d left join promotion_coupons c on d.coupons_id=c.pkid left join patient_info p on d.userid=p.pkid left join md_docter m on d.drid = m.pkid where true"; //string countSql = "selectc.values as valuess,p.name as patient_name,m.name as doctor_name,d.pkid as pkid,coupons_id,use_status,code,sendtime,usetime,business_type from promotion_coupons_detail d left join promotion_coupons c on d.coupons_id=c.pkid left join patient_info p on d.userid=p.pkid left join md_docter m on d.drid = m.pkid where true"; string selectSql = "select c.values as valuess,p.name as patient_name,m.name as doctor_name,d.pkid as pkid,coupons_id,use_status,code,sendtime,usetime,business_type from promotion_coupons_detail d left join promotion_coupons c on d.coupons_id=c.pkid left join patient_info p on d.userid=p.pkid left join md_docter m on d.drid = m.pkid where true"; string countSql = "select c.values as valuess,p.name as patient_name,m.name as doctor_name,d.pkid as pkid,coupons_id,use_status,code,sendtime,usetime,business_type from promotion_coupons_detail d left join promotion_coupons c on d.coupons_id=c.pkid left join patient_info p on d.userid=p.pkid left join md_docter m on d.drid = m.pkid where true"; StringBuilder conditionSb = new System.Text.StringBuilder(); List<MySqlParameter> paraList = new List<MySqlParameter>(); #region 条件 if (coupons_id > 0) { conditionSb.Append(" AND coupons_id = @coupons_id "); paraList.Add(new MySqlParameter("coupons_id", coupons_id)); } conditionSb.Append(" AND use_status = @use_status "); paraList.Add(new MySqlParameter("use_status", use_status)); conditionSb.Append(" AND business_type = @business_type "); paraList.Add(new MySqlParameter("business_type", business_type)); if (userid > 0) { conditionSb.Append(" AND userid = @userid "); paraList.Add(new MySqlParameter("userid", userid)); } if (drid > 0) { conditionSb.Append(" AND d.drid = @drid "); paraList.Add(new MySqlParameter("drid", drid)); } if (sendtime1.HasValue) { conditionSb.Append(" AND DATEDIFF(sendtime,@sendtime1) >= 0 "); paraList.Add(new MySqlParameter("sendtime1", sendtime1.Value)); } if (sendtime2.HasValue) { conditionSb.Append(" AND DATEDIFF(sendtime,@sendtime2) <= 0 "); paraList.Add(new MySqlParameter("sendtime2", sendtime2.Value)); } if (usetime1.HasValue) { conditionSb.Append(" AND DATEDIFF(usetime,@usetime1) >= 0 "); paraList.Add(new MySqlParameter("usetime1", usetime1.Value)); } if (usetime2.HasValue) { conditionSb.Append(" AND DATEDIFF(usetime,@usetime2) <= 0 "); paraList.Add(new MySqlParameter("usetime2", usetime2.Value)); } #endregion #region 排序 分页 string orderbyStr = string.Empty; if (!string.IsNullOrEmpty(orderbyCol) && orderby.HasValue) orderbyStr = orderbyFormat.getSortStr(orderby.Value, orderbyCol); else orderbyStr = " order by d.pkid desc"; countSql += conditionSb.ToString(); countSql = string.Format("SELECT COUNT(*) FROM ({0}) AS t", countSql); selectSql += conditionSb.ToString() + orderbyStr; if (pageIndex > 0) { selectSql += " LIMIT @pageIndex , @pageSize "; paraList.Add(new MySqlParameter("pageIndex", (pageIndex - 1) * pageSize)); paraList.Add(new MySqlParameter("pageSize", pageSize)); } #endregion var resultList = new List<promotion_coupons_detail>(); //共多少行 record = sqlHelper.ExecuteScalar<int>(countSql, paraList.ToArray()); if (record == 0) return resultList; resultList = sqlHelper.ExecuteObjects<promotion_coupons_detail>(selectSql, paraList.ToArray()).ToList(); return resultList; } catch (Exception ex) { error += BaseTool.FormatExceptionMessage(ex); return null; } }
public List<promotion_events> GetPromotionEventList(string name, int hospital_id, int face_type, DateTime? startdate1, DateTime? startdate2, DateTime? enddate1, DateTime? enddate2, DateTime? createtime1, DateTime? createtime2, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out int record, out string error) { error = string.Empty; record = 0; try { string selectSql = "select * from promotion_events where true"; string countSql = "select * from promotion_events where true"; StringBuilder conditionSb = new System.Text.StringBuilder(); List<MySqlParameter> paraList = new List<MySqlParameter>(); #region 条件 if (!string.IsNullOrEmpty(name)) { conditionSb.Append(" AND name LIKE CONCAT('%', @name, '%') "); paraList.Add(new MySqlParameter("name", name)); } if (face_type > 0) { conditionSb.Append(" AND face_type = @face_type "); paraList.Add(new MySqlParameter("face_type", face_type)); } if (startdate1.HasValue) { conditionSb.Append(" AND DATEDIFF(startdate,@startdate1) >= 0 "); paraList.Add(new MySqlParameter("startdate1", startdate1.Value)); } if (startdate2.HasValue) { conditionSb.Append(" AND DATEDIFF(startdate,@startdate2) <= 0 "); paraList.Add(new MySqlParameter("startdate2", startdate2.Value)); } if (enddate1.HasValue) { conditionSb.Append(" AND DATEDIFF(enddate,@enddate1) >= 0 "); paraList.Add(new MySqlParameter("enddate1", enddate1.Value)); } if (enddate2.HasValue) { conditionSb.Append(" AND DATEDIFF(enddate,@enddate2) <= 0 "); paraList.Add(new MySqlParameter("enddate2", enddate2.Value)); } if (createtime1.HasValue) { conditionSb.Append(" AND DATEDIFF(createtime,@createtime1) >= 0 "); paraList.Add(new MySqlParameter("createtime1", createtime1.Value)); } if (createtime2.HasValue) { conditionSb.Append(" AND DATEDIFF(createtime,@createtime2) <= 0 "); paraList.Add(new MySqlParameter("createtime2", createtime2.Value)); } #endregion #region 排序 分页 string orderbyStr = string.Empty; if (!string.IsNullOrEmpty(orderbyCol) && orderby.HasValue) orderbyStr = orderbyFormat.getSortStr(orderby.Value, orderbyCol); else orderbyStr = " order by pkid desc"; countSql += conditionSb.ToString(); countSql = string.Format("SELECT COUNT(*) FROM ({0}) AS t", countSql); selectSql += conditionSb.ToString() + orderbyStr; if (pageIndex > 0) { selectSql += " LIMIT @pageIndex , @pageSize "; paraList.Add(new MySqlParameter("pageIndex", (pageIndex - 1) * pageSize)); paraList.Add(new MySqlParameter("pageSize", pageSize)); } #endregion var resultList = new List<promotion_events>(); //共多少行 record = sqlHelper.ExecuteScalar<int>(countSql, paraList.ToArray()); if (record == 0) return resultList; resultList = sqlHelper.ExecuteObjects<promotion_events>(selectSql, paraList.ToArray()).ToList(); return resultList; } catch (Exception ex) { error += BaseTool.FormatExceptionMessage(ex); return null; } }
public List<promotion_coupons> GetPromotionCouponsList(string name, decimal value, int issue_status, DateTime? startdate1, DateTime? startdate2, DateTime? enddate1, DateTime? enddate2, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out int record, out string error) { error = string.Empty; record = 0; try { string selectSql = "select c.values as valuess,c.name as name,c.pkid as pkid,p.name as events,c.startdate as startdate,c.enddate as enddate,issue_num from promotion_coupons as c left join promotion_events as p on c.events_id = p.pkid where true "; string countSql = "select c.values as valuess,c.name as name,c.pkid as pkid,p.name as events,c.startdate as startdate,c.enddate as enddate,issue_num from promotion_coupons as c left join promotion_events as p on c.events_id = p.pkid where true "; StringBuilder conditionSb = new System.Text.StringBuilder(); List<MySqlParameter> paraList = new List<MySqlParameter>(); #region 条件 if (!string.IsNullOrEmpty(name)) { conditionSb.Append(" AND c.name LIKE CONCAT('%', @name, '%') "); paraList.Add(new MySqlParameter("name", name)); } if (value > 0) { conditionSb.Append(" AND `values` = @values "); paraList.Add(new MySqlParameter("values", value)); } conditionSb.Append(" AND issue_status = @issue_status "); paraList.Add(new MySqlParameter("issue_status", issue_status)); if (startdate1.HasValue) { conditionSb.Append(" AND DATEDIFF(startdate,@startdate1) >= 0 "); paraList.Add(new MySqlParameter("startdate1", startdate1.Value)); } if (startdate2.HasValue) { conditionSb.Append(" AND DATEDIFF(startdate,@startdate2) <= 0 "); paraList.Add(new MySqlParameter("startdate2", startdate2.Value)); } if (enddate1.HasValue) { conditionSb.Append(" AND DATEDIFF(enddate,@enddate1) >= 0 "); paraList.Add(new MySqlParameter("enddate1", enddate1.Value)); } if (enddate2.HasValue) { conditionSb.Append(" AND DATEDIFF(enddate,@enddate2) <= 0 "); paraList.Add(new MySqlParameter("enddate2", enddate2.Value)); } #endregion #region 排序 分页 string orderbyStr = string.Empty; if (!string.IsNullOrEmpty(orderbyCol) && orderby.HasValue) orderbyStr = orderbyFormat.getSortStr(orderby.Value, orderbyCol); else orderbyStr = " order by c.pkid desc"; countSql += conditionSb.ToString(); countSql = string.Format("SELECT COUNT(*) FROM ({0}) AS t", countSql); selectSql += conditionSb.ToString() + orderbyStr; if (pageIndex > 0) { selectSql += " LIMIT @pageIndex , @pageSize "; paraList.Add(new MySqlParameter("pageIndex", (pageIndex - 1) * pageSize)); paraList.Add(new MySqlParameter("pageSize", pageSize)); } #endregion var resultList = new List<promotion_coupons>(); //共多少行 record = sqlHelper.ExecuteScalar<int>(countSql, paraList.ToArray()); if (record == 0) return resultList; resultList = sqlHelper.ExecuteObjects<promotion_coupons>(selectSql, paraList.ToArray()).ToList(); return resultList; } catch (Exception ex) { error += BaseTool.FormatExceptionMessage(ex); return null; } }
public List<patient_order> SearchPatientOrderList(int patient_id, int drid, int service_type, DateTime? createtime1, DateTime? createtime2, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out string error) { error = string.Empty; try { #region Command string selectSql = string.Format("select *,patient_info.name as patient_name from patient_order left join patient_info on patient_order.patient_id = patient_info.pkid WHERE TRUE "); StringBuilder conditionSb = new System.Text.StringBuilder(); List<MySqlParameter> paraList = new List<MySqlParameter>(); #endregion #region 搜索条件 if (patient_id > 0) { conditionSb.Append("AND patient_id = @patient_id"); paraList.Add(new MySqlParameter("patient_id", patient_id)); } if (drid > 0) { conditionSb.Append("AND drid = @drid"); paraList.Add(new MySqlParameter("drid", drid)); } if (service_type > 0) { conditionSb.Append("AND service_type = @service_type"); paraList.Add(new MySqlParameter("service_type", service_type)); } if (createtime1.HasValue) { conditionSb.Append(" AND DATEDIFF(createtime,@createtime1) >= 0 "); paraList.Add(new MySqlParameter("createtime1", createtime1.Value)); } if (createtime2.HasValue) { conditionSb.Append(" AND DATEDIFF(createtime,@createtime2) <= 0 "); paraList.Add(new MySqlParameter("createtime2", createtime2.Value)); } #endregion #region 排序 分页 string orderbyStr = string.Empty; if (!string.IsNullOrEmpty(orderbyCol) && orderby.HasValue) orderbyStr = orderbyFormat.getSortStr(orderby.Value, orderbyCol); else orderbyStr = " order by patient_order.pkid "; selectSql += conditionSb.ToString() + orderbyStr; if (pageIndex > 0) { selectSql += " LIMIT @pageIndex , @pageSize "; paraList.Add(new MySqlParameter("pageIndex", (pageIndex - 1) * pageSize)); paraList.Add(new MySqlParameter("pageSize", pageSize)); } #endregion #region 执行 //var resultList = db.Database.SqlQuery<patient_order>(selectSql, paraList.ToArray()).ToList(); var resultList = sqlHelper.ExecuteObjects<patient_order>(selectSql, paraList.ToArray()).ToList(); return resultList; #endregion } catch (Exception ex) { error += BaseTool.FormatExceptionMessage(ex); return null; } }
public List<patient_recipelist> SearchReciptList(int drid,int medical_rcd_id ,int hospital_id ,int patient_id ,string cust_name , DateTime? issue_time1 ,DateTime? issue_time2 , DateTime? createtime1 ,DateTime? createtime2,string check_result ,int fileid, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out string error) { error = string.Empty; try { #region Command string selectSql = string.Format("select *,COALESCE(info.name,'') as patient_name,info.gender as gender from patient_recipelist rec left join patient_info info on rec.patient_id=info.pkid WHERE TRUE "); StringBuilder conditionSb = new System.Text.StringBuilder(); List<MySqlParameter> paraList = new List<MySqlParameter>(); #endregion #region 搜索条件 if (drid > 0) { conditionSb.Append("AND drid = @drid"); paraList.Add(new MySqlParameter("drid", drid)); } if (medical_rcd_id > 0) { conditionSb.Append("AND medical_rcd_id = @medical_rcd_id"); paraList.Add(new MySqlParameter("medical_rcd_id", medical_rcd_id)); } if (hospital_id > 0) { conditionSb.Append("AND hospital_id = @hospital_id"); paraList.Add(new MySqlParameter("hospital_id", hospital_id)); } if (patient_id > 0) { conditionSb.Append("AND patient_id = @patient_id"); paraList.Add(new MySqlParameter("patient_id", patient_id)); } if (!string.IsNullOrEmpty(cust_name)) { conditionSb.Append(" AND cust_name = @cust_name "); paraList.Add(new MySqlParameter("cust_name", cust_name)); } if (issue_time1.HasValue) { conditionSb.Append(" AND DATEDIFF(issue_time,@issue_time1) >= 0 "); paraList.Add(new MySqlParameter("issue_time1", issue_time1.Value)); } if (issue_time2.HasValue) { conditionSb.Append(" AND DATEDIFF(issue_time,@issue_time2) <= 0 "); paraList.Add(new MySqlParameter("issue_time2", issue_time2.Value)); } if (createtime1.HasValue) { conditionSb.Append(" AND DATEDIFF(rec.createtime,@createtime1) >= 0 "); paraList.Add(new MySqlParameter("createtime1", createtime1.Value)); } if (createtime2.HasValue) { conditionSb.Append(" AND DATEDIFF(rec.createtime,@createtime2) <= 0 "); paraList.Add(new MySqlParameter("createtime2", createtime2.Value)); } if (fileid > 0) { conditionSb.Append("AND fileid = @fileid"); paraList.Add(new MySqlParameter("fileid", fileid)); } if (!string.IsNullOrEmpty(check_result)) { conditionSb.Append(" AND check_result = @check_result "); paraList.Add(new MySqlParameter("check_result", check_result)); } #endregion #region 排序 分页 string orderbyStr = string.Empty; if (!string.IsNullOrEmpty(orderbyCol) && orderby.HasValue) orderbyStr = orderbyFormat.getSortStr(orderby.Value, orderbyCol); else orderbyStr = " order by rec.pkid "; selectSql += conditionSb.ToString() + orderbyStr; if (pageIndex > 0) { selectSql += " LIMIT @pageIndex , @pageSize "; paraList.Add(new MySqlParameter("pageIndex", (pageIndex - 1) * pageSize)); paraList.Add(new MySqlParameter("pageSize", pageSize)); } #endregion #region 执行 var resultList = db.Database.SqlQuery<patient_recipelist>(selectSql, paraList.ToArray()).ToList(); return resultList; #endregion } catch (Exception ex) { error += BaseTool.FormatExceptionMessage(ex); return null; } }
public List<dr_pre_visit> SearchPreVisitList(int drid, int visit_id, int patient_id, DateTime? pre_date1, DateTime? pre_date2, string pre_time, int pre_type, DateTime? createtime1, DateTime? createtime2, int status , DateTime? visit_date1, DateTime? visit_date2, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out string error) { error = string.Empty; try { #region Command string selectSql = string.Format("select *,COALESCE(info.name,'') as patient_name,info.gender as gender from dr_pre_visit pre left join dr_visit on pre.visit_id=dr_visit.pkid left join patient_info info on pre.patient_id=info.pkid WHERE TRUE "); StringBuilder conditionSb = new System.Text.StringBuilder(); List<MySqlParameter> paraList = new List<MySqlParameter>(); #endregion #region 搜索条件 if (drid > 0) { conditionSb.Append(" AND dr_visit.drid = @drid "); paraList.Add(new MySqlParameter("drid", drid)); } if (visit_id > 0) { conditionSb.Append(" AND pre.visit_id = @visit_id "); paraList.Add(new MySqlParameter("visit_id", visit_id)); } if (patient_id > 0) { conditionSb.Append(" AND pre.patient_id = @patient_id "); paraList.Add(new MySqlParameter("patient_id", patient_id)); } if (pre_date1.HasValue) { conditionSb.Append(" AND DATEDIFF(pre.pre_date,@pre_date1) >= 0 "); paraList.Add(new MySqlParameter("pre_date1", pre_date1.Value)); } if (pre_date2.HasValue) { conditionSb.Append(" AND DATEDIFF(pre.pre_date,@pre_date2) <= 0 "); paraList.Add(new MySqlParameter("pre_date2", pre_date2.Value)); } if (!string.IsNullOrEmpty(pre_time)) { conditionSb.Append(" AND pre.pre_time = @pre_time "); paraList.Add(new MySqlParameter("pre_time", pre_time)); } if (pre_type > 0) { conditionSb.Append(" AND pre.pre_type = @pre_type "); paraList.Add(new MySqlParameter("pre_type", pre_type)); } if (visit_date1.HasValue) { conditionSb.Append(" AND DATEDIFF(pre.createtime,@createtime1) >= 0 "); paraList.Add(new MySqlParameter("createtime1", createtime1.Value)); } if (createtime2.HasValue) { conditionSb.Append(" AND DATEDIFF(pre.createtime,@createtime2) <= 0 "); paraList.Add(new MySqlParameter("createtime2", createtime2.Value)); } if (status > 0) { conditionSb.Append(" AND dr_visit.status = @status "); paraList.Add(new MySqlParameter("status", status)); } if (visit_date1.HasValue) { conditionSb.Append(" AND DATEDIFF(dr_visit.visit_date,@visit_date1) >= 0 "); paraList.Add(new MySqlParameter("visit_date1", visit_date1.Value)); } if (visit_date2.HasValue) { conditionSb.Append(" AND DATEDIFF(dr_visit.visit_date,@visit_date2) <= 0 "); paraList.Add(new MySqlParameter("visit_date2", visit_date2.Value)); } #endregion #region 排序 分页 string orderbyStr = string.Empty; if (!string.IsNullOrEmpty(orderbyCol) && orderby.HasValue) orderbyStr = orderbyFormat.getSortStr(orderby.Value, orderbyCol); else orderbyStr = " order by pre.pkid "; selectSql += conditionSb.ToString() + orderbyStr; if (pageIndex > 0) { selectSql += " LIMIT @pageIndex , @pageSize "; paraList.Add(new MySqlParameter("pageIndex", (pageIndex - 1) * pageSize)); paraList.Add(new MySqlParameter("pageSize", pageSize)); } #endregion #region 执行 var resultList = db.Database.SqlQuery<dr_pre_visit>(selectSql, paraList.ToArray()).ToList(); return resultList; #endregion } catch (Exception ex) { error += BaseTool.FormatExceptionMessage(ex); return null; } }
/// <summary> /// 查询活动信息 /// </summary> /// <param name="face_type"></param> /// <param name="orderby"></param> /// <param name="orderbyCol"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="error"></param> /// <returns></returns> public List<promotion_events> GetPromotionEventList(string name, int hospital_id, int face_type, DateTime? startdate1, DateTime? startdate2, DateTime? enddate1, DateTime? enddate2, DateTime? createtime1, DateTime? createtime2, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out int record, out string error) { return new promotion_events_DA().GetPromotionEventList(name, hospital_id, face_type, startdate1, startdate2, enddate1, enddate2, createtime1, createtime2, orderby, orderbyCol, pageIndex, pageSize, out record, out error); }
public List<promotion_coupons_detail> SearchPromotionCouponsList(int coupons_id, int use_status, int business_type, int userid, int drid, DateTime? sendtime1, DateTime? sendtime2, DateTime? usetime1, DateTime? usetime2, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize, out int record, out string error) { error = string.Empty; record = 0; return new promotion_events_DA().SearchPromotionCouponsList(coupons_id, use_status, business_type, userid, drid, sendtime1, sendtime2, usetime1, usetime2, orderby, orderbyCol, pageIndex, pageSize, out record, out error); }
public List<md_cashdraw_app> SearchCashdrawList(int drid, DateTime? app_time1, DateTime? app_time2, string opuser, DateTime? optime1, DateTime? optime2, int opstatus, orderbyEnum? orderby, string orderbyCol, int pageIndex, int pageSize,out int record, out string error) { error = string.Empty; record = 0; try { #region Command //string selectSql = string.Format("select cash.drid,app_time,drawmoney,in_bank,opuser,optime,opstatus,opremark,cash.pkid as pkid from md_cashdraw_app cash WHERE TRUE "); //string selectSql = string.Format("select cash.drid,app_time,drawmoney,in_bank,opuser,optime,opstatus,opremark,cash.pkid as pkid,md_docter.name as doctor_name from md_cashdraw_app cash left join md_docter on cash.drid=md_docter.pkid WHERE TRUE "); string selectSql = string.Format("select cash.drid,app_time,drawmoney,in_bank,opuser,optime,opstatus,opremark,cash.pkid as pkid,md_hospital.name as hospital,md_docter.name as doctor_name, md_bank.bank as bank from md_cashdraw_app cash left join md_bank on cash.in_bank = md_bank.pkid left join md_docter on cash.drid=md_docter.pkid left join md_hospital on md_docter.hispital_id=md_hospital.pkid WHERE TRUE "); string countSql = string.Format("select cash.drid,app_time,drawmoney,in_bank,opuser,optime,opstatus,opremark,cash.pkid as pkid,md_hospital.name as hospital,md_docter.name as doctor_name, md_bank.bank as bank from md_cashdraw_app cash left join md_bank on cash.in_bank = md_bank.pkid left join md_docter on cash.drid=md_docter.pkid left join md_hospital on md_docter.hispital_id=md_hospital.pkid WHERE TRUE "); StringBuilder conditionSb = new System.Text.StringBuilder(); List<MySqlParameter> paraList = new List<MySqlParameter>(); List<md_cashdraw_app> resultList = new List<md_cashdraw_app>(); #endregion #region 搜索条件 if (drid > 0) { conditionSb.Append("AND cash.drid = @drid"); paraList.Add(new MySqlParameter("drid", drid)); } if (!string.IsNullOrEmpty(opuser)) { conditionSb.Append(" AND opuser LIKE CONCAT('%', @opuser, '%') "); paraList.Add(new MySqlParameter("opuser", opuser)); } conditionSb.Append(" AND opstatus = @opstatus "); paraList.Add(new MySqlParameter("opstatus", opstatus)); if (app_time1.HasValue) { conditionSb.Append(" AND DATEDIFF(app_time,@app_time1) >= 0 "); paraList.Add(new MySqlParameter("app_time1", app_time1.Value)); } if (app_time2.HasValue) { conditionSb.Append(" AND DATEDIFF(app_time,@app_time2) <= 0 "); paraList.Add(new MySqlParameter("app_time2", app_time2.Value)); } if (optime1.HasValue) { conditionSb.Append(" AND DATEDIFF(optime,@optime1) >= 0 "); paraList.Add(new MySqlParameter("optime1", optime1.Value)); } if (optime2.HasValue) { conditionSb.Append(" AND DATEDIFF(optime,@optime2) <= 0 "); paraList.Add(new MySqlParameter("optime2", optime2.Value)); } #endregion #region 排序 分页 string orderbyStr = string.Empty; if (!string.IsNullOrEmpty(orderbyCol) && orderby.HasValue) orderbyStr = orderbyFormat.getSortStr(orderby.Value, orderbyCol); else orderbyStr = " order by cash.pkid "; selectSql += conditionSb.ToString() + orderbyStr; countSql += conditionSb.ToString(); countSql = string.Format("SELECT COUNT(*) FROM ({0}) AS t", countSql); if (pageIndex > 0) { selectSql += " LIMIT @pageIndex , @pageSize "; paraList.Add(new MySqlParameter("pageIndex", (pageIndex - 1) * pageSize)); paraList.Add(new MySqlParameter("pageSize", pageSize)); } #endregion #region 执行 //共多少行 record = sqlHelper.ExecuteScalar<int>(countSql, paraList.ToArray()); if (record == 0) return resultList; resultList = sqlHelper.ExecuteObjects<md_cashdraw_app>(selectSql, paraList.ToArray()).ToList(); return resultList; #endregion } catch (Exception ex) { error += BaseTool.FormatExceptionMessage(ex); return null; } }