//確認退款單---執行之前的歸檔邏輯但是除去更新庫存的操作
public bool CouldReturnMoney(OrderReturnStatusQuery query)
{
StringBuilder sql = new StringBuilder();
ArrayList arrList = new ArrayList();
try
{
arrList.Add(CouldReturn(query));
arrList.Add(UpOrderReturnMaster(query));
HgBatchAccumulateRefund hgBatch = new HgBatchAccumulateRefund();
HGLogin hgLogin = GetHGLoginData((uint)query.ors_order_id);
OrderMaster om = _ordermasterdao.GetOrderMasterByOrderId4Change(Convert.ToInt32(query.ors_order_id));
DataTable _returnDt = GetReturnId(query);
DataTable odli = _orderDetailDao.OrderDetailTable(Convert.ToUInt32(query.return_id),0);
OrderReturnMasterQuery omQuery = new OrderReturnMasterQuery();
SerialDao serialDao = new SerialDao(connStr);
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connStr);
if (odli != null)
{
#region hg個字段賦值
if (hgLogin != null)
{
#region hg_batch_accumulate_refund
hgBatch.order_id = (uint)query.ors_order_id;
hgBatch.head = "B";
hgBatch.card_no = "";
hgBatch.card_checksum = "";
hgBatch.category_id = "N0699999";
hgBatch.wallet = "991991";
hgBatch.enc_idno = hgLogin.enc_idno;
hgBatch.checksum = hgLogin.chk_sum;
hgBatch.transaction_time = hgLogin.transaction_time;
hgBatch.merchant_pos = hgLogin.merchant_pos;
hgBatch.terminal_pos = hgLogin.terminal_pos;
hgBatch.refund_point = om.Deduct_Happygo;
//hgBatch.order_note = "吉甲地台灣好市集訂單編號" + hgBatch.order_id + "返還" + hgBatch.refund_point + "點";
//吉甲地台灣好市集訂單編號131210039累點取消4點
hgBatch.batch_status = 0;
hgBatch.billing_checked = 0;
// hgBatch.batch_import_time = Convert.ToInt32(CommonFunction.GetPHPTime());
#endregion
}
#endregion
int paymoney = 0;
int returnmoney = 0;
int deductbonus = 0;
int deductcash = 0;
int accumulated_bonus = 0;
int accumulated_happygo = 0;
int deduct_happygo = 0;
int deduct_happygo_money = 0;
#region 退款金額 = 商品購買金額 - 購物金
foreach (DataRow od in odli.Rows)
{
// 退款金額 = 商品購買金額 - 購物金
paymoney += (Convert.ToInt32(od["single_money"]) * Convert.ToInt32(od["buy_num"]));
deductbonus += Convert.ToInt32(od["deduct_bonus"]);
deductcash += Convert.ToInt32(od["deduct_welfare"]);
accumulated_bonus += Convert.ToInt32(od["accumulated_bonus"]);
accumulated_happygo += Convert.ToInt32(od["accumulated_happygo"]);
deduct_happygo += Convert.ToInt32(od["deduct_happygo"]);
deduct_happygo_money += Convert.ToInt32(od["deduct_happygo_money"]);
}
returnmoney += paymoney - (deductbonus + deductcash + deduct_happygo_money);//計算應退還的money
#endregion
#region 判斷是否有付款
if (om.Money_Collect_Date > 0 && om.Order_Amount > 0 && returnmoney > 0)
{
#region 新增退款單
string serial_sql = serialDao.Update(46);//46 退款單流水號
DataTable _moneyDt = GetMoneyIDBySerial(serial_sql);
uint moneyId = Convert.ToUInt32(_moneyDt.Rows[0][0]);
OrderMoneyReturn omr = new OrderMoneyReturn();
omQuery.order_id = Convert.ToUInt32(query.ors_order_id);
omr.money_type = om.Order_Payment;
omr.money_total = Convert.ToUInt32(returnmoney);
omr.money_status = 0;
omr.money_source = "return_id:" + query.return_id;
omr.money_id = moneyId;
omQuery.return_ipfrom = "";
omQuery.bank_name = query.bank_name;
omr.bank_branch = query.bank_branch;
omr.bank_account = query.bank_account;
omr.account_name = query.account_name;
omr.bank_note = query.bank_note;
string insertSql = _orderMoneyReturnDao.InsertSql(omQuery, omr);
arrList.Add(insertSql);
#endregion
//取回給予的購物金與happygo點數
if (accumulated_bonus > 0)
{
Deduct_User_Bonus(accumulated_bonus, om);
}
//扣除給予會員的hg點數
if (accumulated_happygo > 0)
{
//插入hg_batch_accumulate_refund
arrList.Add(hg_batch_accumulate_refund(hgBatch));
}
}
#endregion
#region 黑貓例外處理
//黑貓例外處理
if (om.Money_Collect_Date == 0 && om.Order_Payment == 8)
{
//扣除給予會員的購物金
if (accumulated_bonus > 0)
{
Deduct_User_Bonus(accumulated_bonus, om);
}
//扣除給予會員的hg點數
if (accumulated_happygo > 0)
{
//插入hg_batch_accumulate_refund
arrList.Add(hg_batch_accumulate_refund(hgBatch));
}
}
#endregion
//寫入付款單退款金額
arrList.Add(_ordermasterdao.UpdateMoneyReturn(returnmoney, om.Order_Id));
//退回購買扣抵的hp點數
#region 判斷退回購買扣抵的hp點數 判斷是否要退回購物金
if (deduct_happygo > 0)
{
//插入hg_deduct_refund
arrList.Add(hg_batch_deduct_refund(hgBatch));
}
if (deductbonus > 0)
{
Deduct_Refund(om, deductbonus, 0, 0, omQuery);
}
if (deductcash > 0)
{
Deduct_Refund(om, 0, deductcash, 0, omQuery);
}
#endregion
DataTable _dt = GetTotalCount(om.Order_Id, 1);
if (Convert.ToInt32(_dt.Rows[0][0]) == 0)
{
if (om.Priority == 1)
{
arrList.Add(UpdatePriority(om.Order_Id));
arrList.Add(UpdateFirstTime(om.user_id));
}
}
DataTable _dt2 = GetTotalCount(om.Order_Id, 2);
#region 更改推薦
if (Convert.ToInt32(_dt2.Rows[0][0]) == 0)
{
List<UserRecommend> usRecommandLi = _userRecommendDao.QueryByOrderId(om.Order_Id);
string idStr = string.Empty;
if (usRecommandLi.Count != 0)
{
foreach (var record in usRecommandLi)
{
idStr += "," + record.id;
}
idStr= idStr.TrimStart(',');
arrList.Add(_userRecommendDao.UpdateIsCommend(idStr));
}
}
#endregion
if (_mySqlDao.ExcuteSqlsThrowException(arrList))
{
return true;
}
else {
return false;
}
}
}
catch (Exception ex)
{
throw new Exception("OrderReturnStatusDao-->CouldReturnMoney-->" + ex.Message, ex);
}
return true;
}
/// <summary>
/// 退貨單歸檔
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public int Save(OrderReturnMasterQuery query)
{
int id = 0;
SerialDao serialDao = new SerialDao(connString);
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connString);
StringBuilder sql = new StringBuilder();
try
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed)
{
mySqlConn.Open();
}
mySqlCmd.Connection = mySqlConn;
mySqlCmd.Transaction = mySqlConn.BeginTransaction();
mySqlCmd.CommandType = System.Data.CommandType.Text;
//修改退貨單
mySqlCmd.CommandText = UpdateSql(query);
sql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();
//若歸檔 判斷是否成立退款單
#region 歸檔
if (query.return_status == 1)
{
//查詢訂單內容
OrderMaster om = _ordermasterdao.GetOrderMasterByOrderId4Change(Convert.ToInt32(query.order_id));
// 查退貨單內容詳情
DataTable odli = _orderDetailDao.OrderDetailTable(query.return_id,0);
if (odli != null)
{
int paymoney = 0;
int returnmoney = 0;
int deductbonus = 0;
int deductcash = 0;
int accumulated_bonus = 0;
int accumulated_happygo = 0;
int deduct_happygo = 0;
int deduct_happygo_money = 0;
foreach (DataRow od in odli.Rows)
{
// 退款金額 = 商品購買金額 - 購物金
paymoney += (Convert.ToInt32(od["single_money"]) * Convert.ToInt32(od["buy_num"]));
deductbonus += Convert.ToInt32(od["deduct_bonus"]);
deductcash += Convert.ToInt32(od["deduct_welfare"]);
accumulated_bonus += Convert.ToInt32(od["accumulated_bonus"]);
accumulated_happygo += Convert.ToInt32(od["accumulated_happygo"]);
deduct_happygo += Convert.ToInt32(od["deduct_happygo"]);
deduct_happygo_money += Convert.ToInt32(od["deduct_happygo_money"]);
}
returnmoney += paymoney - (deductbonus + deductcash + deduct_happygo_money);//計算應退還的money
// 判斷是否有付款
if (om.Money_Collect_Date > 0 && om.Order_Amount > 0 && returnmoney > 0)
{
//退款方式
uint moneytype = 0;
//if (om.Order_Payment == 1 || om.Order_Payment == 2 || om.Order_Payment == 13 || om.Order_Payment == 16)
//{
// moneytype = om.Order_Payment;
//}
if (om.Order_Payment == 1 || om.Order_Payment == 22)
{
moneytype = 1;
}
else if (om.Order_Payment == 2)
{
moneytype = 2;
}
else if (om.Order_Payment == 13 || om.Order_Payment == 21)
{
moneytype = 13;
}
else if (om.Order_Payment == 16)
{
moneytype = 16;
}
#region 新增退款單
mySqlCmd.CommandText = serialDao.Update(46);//46 退款單流水號
sql.Append(mySqlCmd.CommandText);
uint moneyId = Convert.ToUInt32(mySqlCmd.ExecuteScalar());
OrderMoneyReturn omr = new OrderMoneyReturn();
omr.money_id = moneyId;
omr.money_type = moneytype;
omr.money_total = Convert.ToUInt32(returnmoney);
omr.money_status = 0;
omr.money_source = "return_id:" + query.return_id;
mySqlCmd.CommandText = _orderMoneyReturnDao.InsertSql(query, omr);
sql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();
#endregion
//取回給予的購物金與happygo點數
//扣除給予會員的購物金
if (accumulated_bonus > 0)
{
Deduct_User_Bonus(accumulated_bonus, om);
}
//扣除給予會員的hg點數
if (accumulated_happygo > 0)
{
Deduct_User_Happy_Go(accumulated_happygo, om.Order_Id);
}
}
//黑貓例外處理
if (om.Money_Collect_Date == 0 && om.Order_Payment == 8)
{
//扣除給予會員的購物金
if (accumulated_bonus > 0)
{
Deduct_User_Bonus(accumulated_bonus, om);
}
//扣除給予會員的hg點數
if (accumulated_happygo > 0)
{
Deduct_User_Happy_Go(accumulated_happygo, om.Order_Id);
}
}
#region 寫入付款單退款金額
if (returnmoney > 0)
{
mySqlCmd.CommandText = _ordermasterdao.UpdateMoneyReturn(returnmoney, om.Order_Id);
sql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();
sql.Clear();
}
#endregion
#region 判斷退回購買扣抵的hp點數 判斷是否要退回購物金
if (deduct_happygo > 0)
{
Deduct_Refund(om, 0, 0, deduct_happygo, query);
}
if (accumulated_happygo > 0)
{
Deduct_Refund(om, deductbonus, 0, 0, query);
}
#endregion
//商品數量補回
//foreach (var od in odli)
//{
// if (od.item_mode == 1)//组合商品
// {
// List<OrderDetail> childDetail = Get_Combined_Product(query.order_id, od.Parent_Id, od.pack_id);
// foreach (var child in childDetail)
// {
// _itemDao.UpdateItemStock(child.Item_Id, child.Buy_Num * child.parent_num);
// }
// }
// else
// {
// _itemDao.UpdateItemStock(od.Item_Id, od.Buy_Num);
// }
//}
//
mySqlCmd.CommandText = GetTotalCount(query.order_id);
sql.Append(mySqlCmd.CommandText);
int total = mySqlCmd.ExecuteNonQuery();
//比較訂單全部detail的筆數與（退貨單+取消單）的筆數
if (total == 0)
{
// 首購會員優惠，因取消訂單時要自動回覆功能
if (om.Priority == 1)
{
mySqlCmd.CommandText = _ordermasterdao.UpdatePriority(om.Order_Id);
sql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();
mySqlCmd.CommandText = _usersDao.UpdateFirstTime(om.User_Id);
sql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();
}
//2.0活動筆數
List<UserRecommend> usRecommandLi = _userRecommendDao.QueryByOrderId(om.Order_Id);
string idStr = string.Empty;
if (usRecommandLi != null)
{
foreach (var record in usRecommandLi)
{
if (string.IsNullOrEmpty(idStr))
{
idStr += record;
}
else
{
idStr += "," + record.id;
}
}
mySqlCmd.CommandText = _userRecommendDao.UpdateIsCommend(idStr);
}
sql.Append(mySqlCmd.CommandText);
mySqlCmd.ExecuteNonQuery();
}
}
}
#endregion
#region 取消退款
if (query.return_status == 2)
{
OrderMasterStatusQuery statusquery = new OrderMasterStatusQuery();
OrderDetailQuery detailquery = new OrderDetailQuery();
string description = "Writer:(" + query.user_id + ")" + query.user_username + ",return_id:" + query.return_id + ",取消退貨請協助通知營管貨品確實出貨";
string sqlSerial = serialDao.Update(29);//訂單主檔狀態流水號
DataTable _dt = _accessMySql.getDataTable(sqlSerial);
statusquery.serial_id = Convert.ToUInt64(_dt.Rows[0][0]);
statusquery.order_id = query.order_id;
statusquery.order_status = query.return_status;
statusquery.status_description = description;
statusquery.status_ipfrom = query.return_ipfrom;
mySqlCmd.CommandText += InsertOrderMasterS(statusquery);
List<OrderDetailQuery> odli = _orderDetailDao.OrderDetail(query.return_id);
foreach (var item in odli)
{
detailquery.Slave_Id = item.Slave_Id;
detailquery.Parent_Id = item.Parent_Id;
detailquery.pack_id = item.pack_id;
detailquery.Detail_Id = item.Detail_Id;
if (item.item_mode == 1)
{
mySqlCmd.CommandText = _orderDetailDao.UpdateOrderDetailSome(detailquery);
}
else
{
mySqlCmd.CommandText = _orderDetailDao.UpdateOrderDetail(detailquery);
}
}
mySqlCmd.ExecuteNonQuery();
}
#endregion
mySqlCmd.Transaction.Commit();
id = 1;
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("OrderReturnlMasterMgr-->Save-->" + ex.Message + sql.ToString(), ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
return 0;
}
