/// <summary>
/// 插入账单客户
/// </summary>
/// <returns>The bill customer.</returns>
/// <param name="item">Item.</param>
public int AddBillCustomer(BillCustomer item)
{
item.Id = TimeParser.GetTimeRandom();
string sql = GenerateSql.ReturnInsertBillCustomerSql(item);
return(DbHelper.ExecuteSql(sql));
}
/// <summary>
/// 存在返回id，不存在插入新纪录并返回id
/// </summary>
/// <returns>The add.</returns>
/// <param name="item">Item.</param>
public string Add(Passport passport, BillCustomer item)
{
//新客户，插入客户，再插入账单
if (item.CustomerId == "")
{
item.Id = TimeParser.GetTimeRandom();
item.CustomerId = TimeParser.GetTimeRandom();
dal.AddBillCustomerAndCustomer(passport.EnterpriseId, item);
return(item.Id);
}
else
{
object result = dal.GetSingleBillCustomer(item);
if (result == null)
{
item.Id = TimeParser.GetTimeRandom();
dal.AddBillCustomer(item);
return(item.Id);
}
else
{
return(result.ToString());
}
}
}
public static string ReturnInsertBillCustomerSql(BillCustomer item)
{
string sql = string.Format(@"INSERT INTO `bill_customer` (`id`, `bill_id`, `customer_id`, `is_paid`, `memo`, `customer_nick_name`) VALUES ('{0}', '{1}', '{2}', {3}, '{4}', '{5}');",
item.Id, item.BillId, item.CustomerId, item.IsPaid, item.Memo, item.CustomerNickName);
return(sql);
}
private List <string> UpdateBillCustomer(BillCustomer item, Passport passport)
{
List <string> sqlList = new List <string>();
// 没有对应客户ID且输入的客户名称不为空时，插入客户表
//if (item.CustomerId == "" && item.CustomerNickName != "")
//{
// item.CustomerId = TimeParser.GetTimeRandom();
// sqlList.Add(GenerateSql.ReturnAddCustomerSql(item.CustomerId, item.CustomerNickName));
//}
//if (item.IsNewCustomer && item.CustomerNickName != "")
//{
// sqlList.Add(GenerateSql.ReturnAddCustomerSql(item.CustomerId, item.CustomerNickName));
//}
if (item.IsAdd)
{
sqlList.Add(GenerateSql.ReturnInsertBillCustomerSql(item));
}
else
{
sqlList.Add(GenerateSql.ReturnUpdateBillCustomerSql(item));
}
if (item.GoodsList != null && item.GoodsList.Count > 0)
{
item.GoodsList.ForEach(goods =>
{
sqlList.AddRange(UpdateBillGoods(goods, passport));
});
}
return(sqlList);
}
public async Task Handle(BillCustomer message, IMessageHandlerContext context)
{
Data.OrderId = message.OrderId;
Data.BillCustomerReceived = true;
await CheckForAllMessagesReceivedAndComplete(context);
}
public ActionResult AddorEdit(BillCustomer emp)
{
try
{
using (DBModel db = new DBModel())
{
if (emp.BillCustomerID == 0)
{
db.BillCustomers.Add(emp);
db.SaveChanges();
}
else
{
db.Entry(emp).State = EntityState.Modified;
db.SaveChanges();
}
}
return(RedirectToAction(""));
//return Json(new { success = true, html = GlobalClass.RenderRazorViewToString(this, "ViewAll", GetAllBillCustomer()), message = "Save Successfully" }, JsonRequestBehavior.AllowGet);
}
catch (Exception ex)
{
return(Json(new { success = false, message = ex.Message }, JsonRequestBehavior.AllowGet));
}
}
public static string ReturnUpdateBillCustomerSql(BillCustomer item)
{
string sql = string.Format(@"UPDATE bill_customer SET customer_id = '{0}',is_paid = {1}, memo='{2}', customer_nick_name='{3}' WHERE id = '{4}';",
item.CustomerId, item.IsPaid, item.Memo, item.CustomerNickName, item.Id);
return(sql);
}
/// <summary>
/// 是否存在客户
/// </summary>
/// <returns><c>true</c>, if bill customer was existed, <c>false</c> otherwise.</returns>
/// <param name="item">Item.</param>
public Object GetSingleBillCustomer(BillCustomer item)
{
string sql = string.Format(@"select id from bill_customer where doc_no='{0}' and customer_id='{1}'",
item.DocNo, item.CustomerId);
return(DbHelper.GetSingle(sql));
}
/// <summary>
/// 插入新客户和账单客户
/// </summary>
/// <returns>The bill customer and customer.</returns>
/// <param name="item">Item.</param>
public int AddBillCustomerAndCustomer(string enterpriseId, BillCustomer item)
{
List <string> sqlList = new List <string>();
sqlList.Add(GenerateSql.ReturnAddCustomerSql(item.CustomerId, item.CustomerNickName, enterpriseId));
sqlList.Add(GenerateSql.ReturnInsertBillCustomerSql(item));
return(DbHelper.ExecuteSqlTran(sqlList));
}
public static string ReturnUpdateBillCustomerSql(BillCustomer item)
{
string sql = string.Format(@"UPDATE bill_customer SET customer_id = '{0}',is_paid = {1}, memo='{2}', customer_nick_name='{3}',
`quantity`={4},`in_total_price`={5}, `out_total_price`={6}, `profit`={7} WHERE id = '{8}';",
item.CustomerId, item.IsPaid, item.Memo, item.CustomerNickName,
item.Quantity, item.InTotalPrice, item.OutTotalPrice, item.Profit, item.Id);
return(sql);
}
public IActionResult UpdateAddressId([FromBody] BillCustomer item)
{
passport = CommonMethod.GetPassport(Request);
if (item == null)
{
return(BadRequest());
}
return(new ObjectResult(service.UpdateAddressId(passport, item)));
}
public MySqlDataReader GetBillCustomerByBillIdAndCustomerId(BillCustomer item)
{
string sql = string.Format(@"SELECT a.id unit_bill_customer_id,a.doc_no unit_doc_no,a.customer_id,a.customer_nick_name,a.is_paid,b.*,c.name position_name FROM bill_customer a
left join bill_goods b on a.id=b.bill_customer_id
left join position c on c.id=b.position_id
where a.bill_id='{0}' and a.customer_id='{1}';",
item.BillId, item.CustomerId);
return(DbHelper.ExecuteReader(sql));
}
public static string ReturnInsertBillCustomerSql(BillCustomer item)
{
string sql = string.Format(@"INSERT INTO `bill_customer` (`id`, `bill_id`, `customer_id`, `is_paid`, `memo`, `customer_nick_name`,
`quantity`, `in_total_price`, `out_total_price`, `profit` )
VALUES ('{0}', '{1}', '{2}', {3}, '{4}', '{5}', {6}, {7}, {8}, {9});",
item.Id, item.BillId, item.CustomerId, item.IsPaid, item.Memo, item.CustomerNickName,
item.Quantity, item.InTotalPrice, item.OutTotalPrice, item.Profit);
return(sql);
}
public IActionResult Create([FromBody] BillCustomer item)
{
passport = CommonMethod.GetPassport(Request);
if (item == null)
{
return(BadRequest());
}
ResultModel result = new ResultModel(true, service.Add(passport, item));
return(new ObjectResult(result));
}
public IActionResult GetBillCustomerByDocNoAndCustomerId([FromBody] BillCustomer customer)
{
passport = CommonMethod.GetPassport(Request);
if (customer == null || customer.CustomerId == "" || customer.BillId == "")
{
return(BadRequest());
}
var item = service.GetBillCustomerByBillIdAndCustomerId(passport, customer);
ResultModel result = new ResultModel(true, item);
return(new ObjectResult(result));
}
//[HttpGet] iterator will be by default
public ActionResult AddorEdit(int id = 0)
{
BillCustomer emp = new BillCustomer();
if (id != 0)
{
using (DBModel db = new DBModel())
{
emp = db.BillCustomers.Where(x => x.BillCustomerID == id).FirstOrDefault <BillCustomer>();
}
}
return(View(emp));
}
private static BillCustomer RowToBillCustomer(MySqlDataReader row)
{
BillCustomer obj = new BillCustomer();
obj.Id = row["id"].ToString();
obj.BillId = row["bill_id"].ToString();
obj.DocNo = row["doc_no"].ToString();
obj.CustomerNickName = row["customer_nick_name"].ToString();
obj.AddressId = row["address_id"].ToString();
obj.IsPaid = Convert.ToBoolean(row["is_paid"]);
obj.Memo = row["memo"].ToString();
obj.Quantity = Convert.ToInt32(row["quantity"]);
obj.InTotalPrice = Convert.ToDecimal(row["in_total_price"]);
obj.OutTotalPrice = Convert.ToDecimal(row["out_total_price"]);
obj.Profit = Convert.ToDecimal(row["profit"]);
return(obj);
}
/// <summary>
/// 计算客户利润
/// </summary>
/// <param name="customer">Customer.</param>
private void CalculateProfit(ref BillCustomer customer)
{
if (customer.GoodsList != null)
{
customer.Quantity = 0;
customer.InTotalPrice = 0;
customer.OutTotalPrice = 0;
customer.Profit = 0;
foreach (BillGoods goods in customer.GoodsList)
{
customer.Quantity += goods.Quantity;
customer.InTotalPrice += goods.InTotalPrice;
customer.OutTotalPrice += goods.OutTotalPrice;
customer.Profit += goods.Profit;
}
}
}
public ActionResult Delete(int id)
{
try
{
using (DBModel db = new DBModel())
{
BillCustomer emp = db.BillCustomers.Where(x => x.BillCustomerID == id).FirstOrDefault <BillCustomer>();
db.BillCustomers.Remove(emp);
db.SaveChanges();
}
return(Json(new { success = true, html = GlobalClass.RenderRazorViewToString(this, "ViewAll", GetAllBillCustomer()), message = "Deleted Successfully" }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(new { success = false, message = ex.Message }, JsonRequestBehavior.AllowGet));
}
}
public async Task Given()
{
saga = new BillingSaga {
Data = new SagaData()
};
context = new TestableMessageHandlerContext();
var billCustomer = new BillCustomer {
OrderId = orderId
};
var orderDelivered = new OrderDelivered {
OrderId = orderId
};
await saga.Handle(billCustomer, context);
await saga.Handle(orderDelivered, context);
}
public static BillCustomer RowToBillCustomerWithGoods(MySqlDataReader dr)
{
BillCustomer customer = new BillCustomer();
customer.Id = "";
customer.BillId = "";
customer.DocNo = "";
customer.IsPaid = false;
customer.GoodsList = new List <BillGoods>();
while (dr.Read())
{
customer.Id = dr["unit_bill_customer_id"].ToString();
customer.BillId = dr["bill_id"].ToString();
customer.DocNo = dr["unit_doc_no"].ToString();
customer.IsPaid = Convert.ToBoolean(dr["is_paid"]);
customer.GoodsList.Add(RowToBillGoods(dr));
}
dr.Close();
return(customer);
}
/// <summary>
/// 更新账单下的客户和商品(会存在新增和更新二种状态的数据，通过ID来区分)
/// </summary>
/// <returns>The bill.</returns>
/// <param name="item">Item.</param>
public int UpdateBillCustomerAndGoods(BillCustomer item)
{
List <string> sqlList = new List <string>();
//if (item.IsAdd)
//{
// sqlList.Add(GenerateSql.ReturnInsertBillCustomerSql(item));
//}
//else
//{
// sqlList.Add(GenerateSql.ReturnUpdateBillCustomerSql(item));
//}
//// 没有对应客户ID且输入的客户名称不为空时，插入客户表
//if (item.CustomerId == "" && item.CustomerNickName != "")
//{
// sqlList.Add(GenerateSql.ReturnAddCustomerSql(item.CustomerNickName));
//}
//if (item.GoodsList != null && item.GoodsList.Count > 0)
//{
// item.GoodsList.ForEach(goods =>
// {
// if (goods.IsAdd)
// {
// sqlList.Add(GenerateSql.ReturnInsertBillGoodsSql(goods));
// }
// else
// {
// sqlList.Add(GenerateSql.ReturnUpdateBillGoodsSql(goods));
// }
// // 没有对应商品ID且输入的商品名称不为空时，插入商品表
// if (goods.GoodsId == "" && goods.GoodsName != "")
// {
// sqlList.Add(GenerateSql.ReturnAddGoodsSql(goods.GoodsName));
// }
// });
//}
return(DbHelper.ExecuteSqlTran(sqlList));
}
public string UpdateAddressId(Passport passport, BillCustomer item)
{
return(dal.UpdateAddressId(item) > 0 ? "" : ErrorMsg.UpdateFailMsg());
}
/// <summary>
/// 转化为账单明细对象
/// </summary>
/// <returns>The list to object.</returns>
/// <param name="dr">Dr.</param>
private Bill ConvertListToObj(MySqlDataReader dr)
{
Bill bill = new Bill();
BillCustomer customer = null;
string billCustomerId = "";
int index = 0;
while (dr.Read())
{
if (index == 0)
{
bill.Id = dr["id"].ToString();
bill.DocNo = dr["doc_no"].ToString();
bill.TaxRate = Convert.ToDecimal(dr["tax_rate"]);
bill.Memo = dr["bill_memo"].ToString();
bill.IsClose = Convert.ToBoolean(dr["is_close"]);
bill.Quantity = Convert.ToInt32(dr["bill_goods_quantity"]);
bill.InTotalPrice = Convert.ToDecimal(dr["bill_goods_in_total_price"]);
bill.OutTotalPrice = Convert.ToDecimal(dr["bill_goods_out_total_price"]);
bill.Profit = Convert.ToDecimal(dr["bill_goods_profit"]);
bill.CustomerList = new List <BillCustomer>();
}
//没有关联数据，退出
if (dr["bill_customer_id"].GetType() == typeof(System.DBNull))
{
break;
}
if (dr["bill_customer_id"].ToString() != billCustomerId)
{
if (customer != null)
{
//CalculateProfit(ref customer);
bill.CustomerList.Add(customer);
}
billCustomerId = dr["bill_customer_id"].ToString();
customer = new BillCustomer();
customer.Id = dr["bill_customer_id"].ToString();
customer.BillId = dr["bill_id"].ToString();
customer.CustomerId = dr["customer_id"].ToString();
customer.CustomerNickName = dr["customer_nick_name"].ToString();
customer.AddressId = dr["address_id"].ToString();
customer.IsPaid = Convert.ToBoolean(dr["is_paid"]);
customer.Memo = dr["memo"].ToString();
customer.Quantity = Convert.ToInt32(dr["customer_goods_quantity"]);
customer.InTotalPrice = Convert.ToDecimal(dr["customer_goods_in_total_price"]);
customer.OutTotalPrice = Convert.ToDecimal(dr["customer_goods_out_total_price"]);
customer.Profit = Convert.ToDecimal(dr["customer_goods_profit"]);
customer.GoodsList = new List <BillGoods>();
if (dr["bill_goods_id"].GetType() == typeof(System.DBNull))
{
continue;
}
customer.GoodsList.Add(RowToBillGoods(dr));
}
else
{
customer.GoodsList.Add(RowToBillGoods(dr));
}
index++;
}
dr.Close();
if (customer != null)
{
CalculateProfit(ref customer);
bill.CustomerList.Add(customer);
}
return(bill);
}
public int UpdateAddressId(BillCustomer item)
{
string sql = string.Format(@"update bill_customer set address_id='{0}' where id='{1}'", item.AddressId, item.Id);
return(DbHelper.ExecuteSql(sql));
}
/// <summary>
/// 更新备注
/// </summary>
/// <returns>The memo.</returns>
/// <param name="item">Item.</param>
public int UpdateMemo(BillCustomer item)
{
string sql = string.Format(@"UPDATE bill_customer SET memo = '{0}' WHERE id = '{1}';", item.Memo, item.Id);
return(DbHelper.ExecuteSql(sql));
}
/// <summary>
/// 更新是否付款
/// </summary>
/// <returns>The is paid.</returns>
/// <param name="item">Item.</param>
public int UpdateIsPaid(BillCustomer item)
{
string sql = string.Format(@"UPDATE bill_customer SET is_paid = {0} WHERE id = '{1}';", item.IsPaid, item.Id);
return(DbHelper.ExecuteSql(sql));
}
public BillCustomer GetBillCustomerByBillIdAndCustomerId(Passport passport, BillCustomer item)
{
return(CustomerDataConverter.RowToBillCustomerWithGoods(dal.GetBillCustomerByBillIdAndCustomerId(item)));
}