public static IList <DispatchFormEntity> GetDispatches(int formNo, string hospitalID)
{
var sql = string.Format("select {0} from dispatch_form where order_form_no=@p_form_no and hospital_id=@p_hospital_id", COLUMN_SQL);
var db = DatabaseFactory.CreateDatabase();
var dc = db.GetSqlStringCommand(sql);
db.AddInParameter(dc, "p_form_no", DbType.Int32, formNo);
db.AddInParameter(dc, "p_hospital_id", DbType.String, hospitalID);
var list = new List <DispatchFormEntity>();
using (var reader = db.ExecuteReader(dc))
{
while (reader.Read())
{
var entity = new DispatchFormEntity();
entity.Init(reader);
list.Add(entity);
}
}
return(list);
}
private static void Create(DispatchFormEntity form, Database db, DbTransaction trans)
{
var sql = string.Format(@"INSERT INTO dispatch_form
(
id,order_id,order_form_no,order_detail_id,apply_unit_id,product_id,
dispatched_count,hospital_id,vendor_id,status,changed_id,changed_time,created_id,created_time
)
VALUES(
@p_id,@p_order_id,@p_order_form_no,@p_order_detail_id,@p_apply_unit_id,@p_product_id,
@p_dispatched_count,@p_hospital_id,@p_vendor_id,@p_status,@p_changed_id,@p_changed_time,@p_created_id,@p_created_time
)", COLUMN_SQL);
var dc = db.GetSqlStringCommand(sql);
db.AddInParameter(dc, "p_id", DbType.String, form.Id);
db.AddInParameter(dc, "p_order_id", DbType.String, form.OrderId);
db.AddInParameter(dc, "p_order_form_no", DbType.Int32, form.OrderFormNo);
db.AddInParameter(dc, "p_order_detail_id", DbType.String, form.OrderDetailId);
db.AddInParameter(dc, "p_apply_unit_id", DbType.String, form.ApplyUnitId);
db.AddInParameter(dc, "p_product_id", DbType.String, form.ProductId);
db.AddInParameter(dc, "p_dispatched_count", DbType.Int32, form.DispatchedCount);
db.AddInParameter(dc, "p_hospital_id", DbType.String, form.HospitalId);
db.AddInParameter(dc, "p_vendor_id", DbType.String, form.VendorId);
db.AddInParameter(dc, "p_status", DbType.String, form.Status);
db.AddInParameter(dc, "p_changed_id", DbType.String, form.ChangedId);
db.AddInParameter(dc, "p_changed_time", DbType.DateTime, form.ChangedTime);
db.AddInParameter(dc, "p_created_id", DbType.String, form.CreatedId);
db.AddInParameter(dc, "p_created_time", DbType.DateTime, form.CreatedTime);
db.ExecuteNonQuery(dc, trans);
}
private static DispatchFormEntity Get(string id, Database db, DbTransaction trans)
{
var sql = string.Format("select {0} from dispatch_form {1} where id=@p_id", COLUMN_SQL, TransHelper.UpdateLock(trans));
if (db == null)
{
db = DatabaseFactory.CreateDatabase();
}
var dc = db.GetSqlStringCommand(sql);
db.AddInParameter(dc, "p_id", DbType.String, id);
DispatchFormEntity entity = null;
using (var reader = (trans == null ? db.ExecuteReader(dc) : db.ExecuteReader(dc, trans)))
{
while (reader.Read())
{
entity = new DispatchFormEntity();
entity.Init(reader);
break;
}
}
return(entity);
}
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 string DispatchGoods(DispatchFormEntity form, DispatchFormItemEntity formItem, Database db, DbTransaction trans)
{
var orderDetail = OrderFormRepository.GetItem(form.OrderDetailId);
var goodsSerial = new GoodsSerialEntity
{
ProductId = form.ProductId,
DispatchedCount = formItem.Count,
HospitalId = form.HospitalId,
VendorId = form.VendorId,
NeedAudit = orderDetail.NeedAudit,
NeedCheck = orderDetail.NeedCheck,
NeedSplit = orderDetail.NeedSplit,
SplitCopies = orderDetail.SplitCopies,
SplitUnit = orderDetail.SplitUnit,
SplitCapacity = orderDetail.SplitCapacity,
SplitPackageCount = orderDetail.SplitPackageCount,
ValidDays = orderDetail.ValidDays,
BatchNo = formItem.BatchNo,
ExpiredDate = formItem.ExpiredDate,
IsClosed = false,
CreatedId = form.CreatedId,
CreatedTime = DateTime.Now,
UpdatedId = form.CreatedId,
UpdatedTime = DateTime.Now
};
GoodsSerialRepository.Create(goodsSerial, db, trans);
GoodsSerialFormRepository.Create(new GoodsSerialFormEntity
{
SerialId = goodsSerial.Id,
FormId = form.Id,
FormKind = FormKind.DispatchItem,
CreatedId = form.CreatedId,
CreatedTime = DateTime.Now,
}, new GoodsSerialFormEntity
{
SerialId = goodsSerial.Id,
FormId = form.OrderDetailId,
FormKind = FormKind.OrderDetail,
CreatedId = form.CreatedId,
CreatedTime = DateTime.Now,
}, db, trans);
return(goodsSerial.Id);
}
private static void CreateReceiveForm(DispatchFormEntity form, DispatchFormItemEntity formItem, Database db, DbTransaction trans)
{
var receiveForm = new ReceiveFormEntity
{
OrderId = form.OrderId,
OrderFormNo = form.OrderFormNo,
OrderDetailId = form.OrderDetailId,
ApplyUnitId = form.ApplyUnitId,
SerialId = formItem.SerialId,
ProductId = form.ProductId,
ReceivedCount = formItem.Count,
HospitalId = form.HospitalId,
VendorId = form.VendorId,
CreatedId = form.ChangedId,
CreatedTime = form.ChangedTime
};
ReceiveFormRepository.Create(receiveForm, db, trans);
}
public static void CreateItem(DispatchFormEntity form, DispatchFormItemEntity formItem)
{
var db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
try
{
var count = SumItems(form.Id);
if (form.DispatchedCount == count + formItem.Count)
{
UpdateStatus(form.Id, DispatchFormStatus.Dispatching, formItem.CreatedId, formItem.CreatedTime, db, trans);
}
else if (count == 0)
{
UpdateStatus(form.Id, DispatchFormStatus.Dispatching, formItem.CreatedId, formItem.CreatedTime, db, trans);
}
else if (formItem.Count + count > form.DispatchedCount)
{
throw new Exception("The count of dispatch form is over.");
}
formItem.Id = Guid.NewGuid().ToString();
formItem.SerialId = DispatchGoods(form, formItem, db, trans);
CreateItem(formItem, db, trans);
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
}
}
public static DispatchFormEntity Get(string id)
{
var sql = string.Format("select {0} from dispatch_form where id=@p_id", COLUMN_SQL);
var db = DatabaseFactory.CreateDatabase();
var dc = db.GetSqlStringCommand(sql);
db.AddInParameter(dc, "p_id", DbType.String, id);
using (var reader = db.ExecuteReader(dc))
{
if (reader.Read())
{
var entity = new DispatchFormEntity();
entity.Init(reader);
return(entity);
}
}
return(null);
}
public static IList <DispatchFormEntity> Query(IList <string> orderDetailIds)
{
var list = new List <DispatchFormEntity>();
if (orderDetailIds == null || orderDetailIds.Count == 0)
{
return(list);
}
IList <string> paramNames;
string paramSql;
LIMS.Util.StringHelper.GenerInParameters("p_order_detail_id", orderDetailIds.Count, out paramNames, out paramSql);
var sql = string.Format(@"select {0} from dispatch_form where order_detail_id in ({1}) and status in ('{2}','{3}','{4}')", COLUMN_SQL, paramSql, DispatchFormStatus.Waiting, DispatchFormStatus.Confirmed, DispatchFormStatus.Dispatching);
var db = DatabaseFactory.CreateDatabase();
var dc = db.GetSqlStringCommand(sql);
for (var i = 0; i < orderDetailIds.Count; i++)
{
db.AddInParameter(dc, paramNames[i], DbType.String, orderDetailIds[i]);
}
using (var reader = db.ExecuteReader(dc))
{
while (reader.Read())
{
var entity = new DispatchFormEntity();
entity.Init(reader);
list.Add(entity);
}
}
return(list);
}
private static void UpdateOrderStatus(DispatchFormEntity form, Database db, DbTransaction trans)
{
OrderFormRepository.UpdateStatus(form.OrderId, form.OrderDetailId, OrderFormItemStatus.Dispatching, db, trans);
}
