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