private static string GetConditionSql(DateRangeCondition condition) { var conditionSql = @" 1=1 and new_storeroom_id in ( select unit_id from user_privilege where user_id = @p_user_id and unit_root_id = @p_hospital_id and operate=1)"; int formNo; if (!string.IsNullOrEmpty(condition.Content) && int.TryParse(condition.Content, out formNo)) { conditionSql += " and form_no = @p_form_no"; } if (condition.BeginDate.HasValue) { conditionSql += " AND apply_time >= @p_begin_date"; } if (condition.EndDate.HasValue) { conditionSql += " AND apply_time <= @p_end_date"; } return(conditionSql); }
public static IList <GoodsInventoryEntity> Query(DateRangeCondition condition) { var sql = string.Format(GetBaseQuerySql(condition), "a.id,a.product_id,a.hospital_id,a.original_count,a.usable_count,a.granted_count,a.split_count,b.name,b.full_name"); sql += " order by name"; var list = new List <GoodsInventoryEntity>(); var db = DatabaseFactory.CreateDatabase(); var dc = db.GetSqlStringCommand(sql); AddParameter(dc, db, condition); using (var reader = db.ExecuteReader(dc)) { while (reader.Read()) { var entity = new GoodsInventoryEntity(); entity.Init(reader); //entity.ProductName = reader["name"].ToString(); //entity.ProductFullName = reader["full_name"].ToString(); list.Add(entity); } } return(list); }
private static void AddParameter(DbCommand dc, Database db, DateRangeCondition condition) { db.AddInParameter(dc, "p_hospital_id", DbType.String, condition.HospitalId); if (!string.IsNullOrEmpty(condition.Content)) { db.AddInParameter(dc, "p_name", DbType.String, "%" + condition.Content + "%"); } }
protected void InitCondition(DateRangeCondition condition) { condition.UserId = this.UserContext.UserId; condition.HospitalId = this.UserContext.CurrentHospital; if (condition.EndDate.HasValue) { condition.EndDate = condition.EndDate.Value.AddDays(1); } }
private static string GetConditionSql(DateRangeCondition condition) { var conditionSql = @" 1=1 and a.hospital_id = @p_hospital_id "; if (!string.IsNullOrEmpty(condition.Content)) { conditionSql += " and (b.name like @p_name or b.full_name like @p_name)"; } return(conditionSql); }
public static IList <MoveoutFormEntity> Query(DateRangeCondition condition, PagerInfo pager) { pager.ComputePageCount(QueryCount(condition)); var list = new List <MoveoutFormEntity>(); var orderSql = " ORDER BY "; if (pager.OrderFields.Count > 0) { foreach (var field in pager.OrderFields) { orderSql += field.Field + (field.Desc ? " DESC" : "") + ","; } } else { orderSql += "form_no DESC"; } var sql = string.Format(@"SELECT {0} FROM moveout_form WHERE is_active = 1 and {1}", COLUMN_SQL, GetConditionSql(condition)); sql = @"SELECT * FROM ( SELECT ROW_NUMBER() OVER(" + orderSql + @") pid," + COLUMN_SQL + @" FROM (" + sql + @") t ) t1 WHERE t1.pid BETWEEN @p_pageNo * @p_pageSize + 1 AND (@p_pageNo + 1) * @p_pageSize "; var db = DatabaseFactory.CreateDatabase(); var dc = db.GetSqlStringCommand(sql); AddParameter(dc, db, condition); db.AddInParameter(dc, "p_pageNo", DbType.Int32, pager.PageIndex); db.AddInParameter(dc, "p_pageSize", DbType.Int32, pager.PageSize); using (IDataReader reader = db.ExecuteReader(dc)) { while (reader.Read()) { var entity = new MoveoutFormEntity(); entity.Init(reader); list.Add(entity); } } return(list); }
public static IList <GoodsInventoryEntity> Query(DateRangeCondition condition, PagerInfo pager) { pager.ComputePageCount(QueryCount(condition)); var list = new List <GoodsInventoryEntity>(); var orderSql = " ORDER BY "; if (pager.OrderFields.Count > 0) { foreach (var field in pager.OrderFields) { orderSql += field.Field + (field.Desc ? " DESC" : "") + ","; } } else { orderSql += "name"; } var sql = string.Format(GetBaseQuerySql(condition), "a.id,a.product_id,a.hospital_id,a.original_count,a.usable_count,a.granted_count,a.split_count,b.name,b.full_name"); sql = @"SELECT * FROM ( SELECT ROW_NUMBER() OVER(" + orderSql + @") pid,* FROM (" + sql + @") t ) t1 WHERE t1.pid BETWEEN @p_pageNo * @p_pageSize + 1 AND (@p_pageNo + 1) * @p_pageSize"; var db = DatabaseFactory.CreateDatabase(); var dc = db.GetSqlStringCommand(sql); AddParameter(dc, db, condition); db.AddInParameter(dc, "p_pageNo", DbType.Int32, pager.PageIndex); db.AddInParameter(dc, "p_pageSize", DbType.Int32, pager.PageSize); using (var reader = db.ExecuteReader(dc)) { while (reader.Read()) { var entity = new GoodsInventoryEntity(); entity.Init(reader); //entity.ProductName = reader["name"].ToString(); //entity.ProductFullName = reader["full_name"].ToString(); list.Add(entity); } } return(list); }
public ActionResult LoadPrintInventory(DateRangeCondition condition) { try { condition.UserId = this.UserContext.UserId; condition.HospitalId = this.UserContext.CurrentHospital; var list = new GoodsInventoryService().Query(condition); return(JsonNet(new ResponseResult(true, list))); } catch (Exception e) { return(JsonNet(new ResponseResult(e))); } }
public ActionResult QueryChecking(DateRangeCondition condition, PagerInfo pager) { try { condition.UserId = this.UserContext.UserId; condition.HospitalId = this.UserContext.CurrentHospital; var list = new GoodsInventoryService().Query(condition, pager); return(JsonNet(new ResponseResult(true, list, pager))); } catch (Exception e) { return(JsonNet(new ResponseResult(e))); } }
private static int QueryCount(DateRangeCondition condition) { var sql = string.Format(GetBaseQuerySql(condition), "COUNT(a.id)"); var db = DatabaseFactory.CreateDatabase(); var dc = db.GetSqlStringCommand(sql); AddParameter(dc, db, condition); using (IDataReader reader = db.ExecuteReader(dc)) { reader.Read(); return(reader.GetInt32(0)); } }
private int QueryCount(DateRangeCondition condition) { var sql = "SELECT COUNT(id) FROM move_form WHERE status != 'deleted' and "; sql += GetConditionSql(condition); var dc = Db.GetSqlStringCommand(sql); AddParameter(dc, condition); using (IDataReader reader = Db.ExecuteReader(dc)) { reader.Read(); return(reader.GetInt32(0)); } }
private static int QueryCount(DateRangeCondition condition) { var sql = "SELECT COUNT(id) FROM moveout_form WHERE "; sql += GetConditionSql(condition); var db = DatabaseFactory.CreateDatabase(); var dc = db.GetSqlStringCommand(sql); AddParameter(dc, db, condition); using (IDataReader reader = db.ExecuteReader(dc)) { reader.Read(); return(reader.GetInt32(0)); } }
private static void AddParameter(DbCommand dc, Database db, DateRangeCondition condition) { db.AddInParameter(dc, "p_user_id", DbType.String, condition.UserId); db.AddInParameter(dc, "p_hospital_id", DbType.String, condition.HospitalId); int formNo; if (!string.IsNullOrEmpty(condition.Content) && int.TryParse(condition.Content, out formNo)) { db.AddInParameter(dc, "p_form_no", DbType.Int32, formNo); } if (condition.BeginDate.HasValue) { db.AddInParameter(dc, "p_begin_date", DbType.DateTime, condition.BeginDate); } if (condition.EndDate.HasValue) { db.AddInParameter(dc, "p_end_date", DbType.DateTime, condition.EndDate); } }
public IList <MoveoutFormEntity> Query(DateRangeCondition condition, PagerInfo pager) { return(MoveoutFormRepository.Query(condition, pager)); }
private static string GetBaseQuerySql(DateRangeCondition condition) { var sql = "SELECT {0} FROM goods_inventory a left join products b on a.product_id = b.id WHERE " + GetConditionSql(condition); return(sql); }
public IList <GoodsInventoryEntity> Query(DateRangeCondition condition, PagerInfo pageInfo) { return(GoodsInventoryRepository.Query(condition, pageInfo)); }
public IList <GoodsInventoryEntity> Query(DateRangeCondition condition) { return(GoodsInventoryRepository.Query(condition)); }