private static string GetConditionSql(DispatchQueryCondition condition)
{
var sql = " and vendor_id=@p_vendor_id and hospital_id=@p_hospital_id ";
sql += @" and product_id in (select b.product_id from user_privilege a join vendor_products b on a.unit_id=b.unit_id where user_id=@p_user_id and operate=1) ";
if (condition.FormNo.HasValue)
{
sql += " and order_form_no=@p_form_no";
}
if (!string.IsNullOrEmpty(condition.Status))
{
if (string.Compare(condition.Status, DispatchFormStatus.QueryDispatchable, true) == 0)
{
sql += " and (status=@p_waiting or status=@p_dispatching)";
}
else
{
sql += " and status=@p_status";
}
}
return(sql);
}
public static IList <DispatchFormEntity> Query(DispatchQueryCondition condition, PagerInfo pager)
{
var baseSql = @"select {0} from dispatch_form where 1=1 {1}";
var conditionSql = GetConditionSql(condition);
var db = DatabaseFactory.CreateDatabase();
pager.ComputePageCount(QueryCount(baseSql, conditionSql, condition, db));
var orderSql = " order by ";
if (pager.OrderFields.Count > 0)
{
foreach (var field in pager.OrderFields)
{
orderSql += field.Field + (field.Desc ? " DESC" : "") + ",";
}
}
else
{
orderSql += " order_form_no desc,product_id,created_time desc,status ";
}
var sql = string.Format(baseSql, COLUMN_SQL, conditionSql);
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 cmd = db.GetSqlStringCommand(sql);
AddParameters(condition, db, cmd);
db.AddInParameter(cmd, "p_pageNo", DbType.Int32, pager.PageIndex);
db.AddInParameter(cmd, "p_pageSize", DbType.Int32, pager.PageSize);
var list = new List <DispatchFormEntity>();
using (var reader = db.ExecuteReader(cmd))
{
while (reader.Read())
{
var entity = new DispatchFormEntity();
entity.Init(reader);
list.Add(entity);
}
}
return(list);
}
private static int QueryCount(string baseSql, string conditionSql, DispatchQueryCondition condition, Database db)
{
var sql = string.Format(baseSql, "count(*)", conditionSql);
var cmd = db.GetSqlStringCommand(sql);
AddParameters(condition, db, cmd);
using (IDataReader reader = db.ExecuteReader(cmd))
{
reader.Read();
return(reader.GetInt32(0));
}
}
private static void AddParameters(DispatchQueryCondition condition, Database db, DbCommand cmd)
{
db.AddInParameter(cmd, "p_vendor_id", DbType.String, condition.VendorId);
db.AddInParameter(cmd, "p_hospital_id", DbType.String, condition.HospitalId);
db.AddInParameter(cmd, "p_user_id", DbType.String, condition.UserId);
if (condition.FormNo.HasValue)
{
db.AddInParameter(cmd, "p_form_no", DbType.String, condition.FormNo);
}
if (!string.IsNullOrEmpty(condition.Status))
{
if (string.Compare(condition.Status, DispatchFormStatus.QueryDispatchable, true) == 0)
{
db.AddInParameter(cmd, "p_waiting", DbType.String, DispatchFormStatus.Waiting);
db.AddInParameter(cmd, "p_dispatching", DbType.String, DispatchFormStatus.Dispatching);
}
else
{
db.AddInParameter(cmd, "p_status", DbType.String, condition.Status);
}
}
}
