public static void Save(CheckFormEntity form, IList <CheckFormCategoryEntity> categories)
{
var db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
try
{
if (string.IsNullOrEmpty(form.Id))
{
Create(form, db, trans);
}
else
{
Update(form, db, trans);
DeleteCategories(form.Id, db, trans);
}
CreateCategories(form.Id, categories, db, trans);
AnalyseProducts(form.Id, db, trans);
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
}
}
private static void Update(CheckFormEntity form, Database db, DbTransaction trans)
{
var sql = "update check_form set name=@p_name,storeroom_id=@p_storeroom_id where id=@p_id";
var cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "p_id", DbType.String, form.Id);
db.AddInParameter(cmd, "p_name", DbType.String, form.Name);
db.AddInParameter(cmd, "p_storeroom_id", DbType.String, form.StoreroomId);
db.ExecuteNonQuery(cmd, trans);
}
public static IList <CheckFormEntity> Query(CheckCondition condition, PagerInfo pager)
{
pager.ComputePageCount(QueryCount(condition));
var list = new List <CheckFormEntity>();
var orderSql = " ORDER BY ";
if (pager.OrderFields.Count > 0)
{
foreach (var field in pager.OrderFields)
{
orderSql += field.Field + (field.Desc ? " DESC" : "") + ",";
}
}
else
{
orderSql += "created_time DESC";
}
var sql = string.Format(@"SELECT {0} FROM check_form WHERE {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 CheckFormEntity();
entity.Init(reader);
list.Add(entity);
}
}
return(list);
}
private static void Create(CheckFormEntity form, Database db, DbTransaction trans)
{
var sql = @"insert into check_form(id,name,storeroom_id,hospital_id,status,created_id,created_time)
values(@p_id,@p_name,@p_storeroom_id,@p_hospital_id,@p_status,@p_created_id,@p_created_time)";
form.Id = Guid.NewGuid().ToString();
var cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "p_id", DbType.String, form.Id);
db.AddInParameter(cmd, "p_name", DbType.String, form.Name);
db.AddInParameter(cmd, "p_storeroom_id", DbType.String, form.StoreroomId);
db.AddInParameter(cmd, "p_hospital_id", DbType.String, form.HospitalId);
db.AddInParameter(cmd, "p_status", DbType.String, form.Status);
db.AddInParameter(cmd, "p_created_id", DbType.String, form.CreatedId);
db.AddInParameter(cmd, "p_created_time", DbType.DateTime, form.CreatedTime);
db.ExecuteNonQuery(cmd, trans);
}
public static CheckFormEntity Get(string id)
{
var sql = string.Format("select {0} from check_form where id=@p_id", COLUMN_SQL);
var db = DatabaseFactory.CreateDatabase();
var cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "p_id", DbType.String, id);
using (var reader = db.ExecuteReader(cmd))
{
if (reader.Read())
{
var entity = new CheckFormEntity();
entity.Init(reader);
return(entity);
}
}
return(null);
}
public void Save(CheckFormEntity form, IList <CheckFormCategoryEntity> categories)
{
CheckFormRepository.Save(form, categories);
}
