public int Update(OrderCancelMaster ocm)
{
try
{
return _orderCancelMaster.Update(ocm);
}
catch (Exception ex)
{
throw new Exception("OrderCancelMasterMgr.Update-->" + ex.Message, ex);
}
}
public List<OrderCancelMaster> GetOrderCancelMasterList(OrderCancelMaster ocm, out int totalCount)
{
try
{
return _orderCancelMaster.GetOrderCancelMasterList(ocm, out totalCount);
}
catch (Exception ex)
{
throw new Exception("OrderCancelMasterMgr.GetOrderCancelMasterList-->" + ex.Message, ex);
}
}
/// <summary>
/// 獲取取消單列表
/// </summary>
/// <param name="ocm"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List<OrderCancelMaster> GetOrderCancelMasterList(OrderCancelMaster ocm, out int totalCount)
{
StringBuilder sql = new StringBuilder();
try
{
sql.AppendFormat("select cancel_id,order_id,cancel_status,cancel_note,bank_note,FROM_UNIXTIME(cancel_createdate)as cancel_createdate,FROM_UNIXTIME(cancel_updatedate)as cancel_updatedate,cancel_ipfrom from order_cancel_master order by cancel_createdate desc");
//分頁
totalCount = 0;
if (ocm.IsPage)
{
System.Data.DataTable _dt = _accessMySql.getDataTable(sql.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = _dt.Rows.Count;
}
sql.AppendFormat(" limit {0},{1}", ocm.Start, ocm.Limit);
}
return _accessMySql.getDataTableForObj<OrderCancelMaster>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("OrderReturnlMasterDao-->GetOrderCancelMasterList-->" + sql.ToString() + ex.Message, ex);
}
}
public HttpResponseBase ModifyOrderCancelMasterList()
{
List<OrderCancelMaster> stores = new List<OrderCancelMaster>();
string json = string.Empty;
try
{
OrderCancelMaster query = new OrderCancelMaster();
query.order_id = Convert.ToUInt32(Request.Form["order_id"]);
query.cancel_id = Convert.ToUInt32(Request.Form["cancel_id"]);
query.cancel_status = Convert.ToUInt32(Request.Form["cancel_status"]);
query.cancel_note = Request.Params["cancel_note"];
query.bank_note = Request.Params["bank_note"];
query.cancel_createdate = Convert.ToDateTime(Request.Params["cancel_createdate"]);
query.cancel_updatedate = Convert.ToDateTime(Request.Params["cancel_updatedate"]);
System.Net.IPAddress[] ips = Dns.GetHostByName(Dns.GetHostName()).AddressList;
if (ips.Length > 0)
{
query.cancel_ipfrom = ips[0].ToString();
}
_orderCancelMgr = new OrderCancelMasterMgr(mySqlConnectionString);
int result = _orderCancelMgr.Update(query);
string msg = "";
switch (result)
{
case 1:
msg = "bonus not enough!";
break;
case 2:
msg = "bonus type error!";
break;
case 3:
msg = "取得身分證字號失敗!";
break;
case 4:
msg = "扣除HappyGo點數失敗!";
break;
case 5:
msg = "bonus type error !";
break;
case 100:
msg = "";//操作成功!
break;
default:
break;
}
if (string.IsNullOrEmpty(msg))
{
json = "{success:true}";//返回json數據
}
else
{
json = "{success:true,msg:\"" + msg + "\"}";
}
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:false,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase GetOrderCancelMasterList()
{
List<OrderCancelMaster> stores = new List<OrderCancelMaster>();
string json = string.Empty;
try
{
int totalCount = 0;
OrderCancelMaster query = new OrderCancelMaster();
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "20");//
_orderCancelMgr = new OrderCancelMasterMgr(mySqlConnectionString);
stores = _orderCancelMgr.GetOrderCancelMasterList(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
//listUser是准备转换的对象
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(stores, Formatting.Indented, timeConverter) + "}";//返回json數據
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:true,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 取消單歸檔修改
/// </summary>
/// <param name="ocm"></param>
/// <returns></returns>
public int Update(OrderCancelMaster ocm)
{
_serialDao = new SerialDao(connString);
DataTable ordermaster = GetOrderMaster(ocm.order_id);
string user_id = string.Empty;
uint order_date_pay = 0;
uint order_amount = 0;
uint order_payment = 0;
int total_product_pay_money = 0;
int total_product_deduct_bonus = 0;
int total_product_deduct_cash = 0;
int accumulated_bonus = 0;
int accumulated_happygo = 0;
int deduct_happygo = 0;
int deduct_happygo_money = 0;
int cancel_money = 0;
if (ordermaster.Rows.Count > 0)
{
user_id = ordermaster.Rows[0]["user_id"].ToString();
if (!string.IsNullOrEmpty(ordermaster.Rows[0]["order_date_pay"].ToString()))
{
order_date_pay = (uint)ordermaster.Rows[0]["order_date_pay"];
}
if (!string.IsNullOrEmpty(ordermaster.Rows[0]["order_amount"].ToString()))
{
order_amount = (uint)ordermaster.Rows[0]["order_amount"];
}
if (!string.IsNullOrEmpty(ordermaster.Rows[0]["order_payment"].ToString()))
{
order_payment = Convert.ToUInt32(ordermaster.Rows[0]["order_payment"]);
}
}
StringBuilder sql = new StringBuilder();
StringBuilder sqlstr = new StringBuilder();
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connString);
int result = 0;
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;
sqlstr.AppendFormat(@"set sql_safe_updates=0;update order_cancel_master set cancel_status='{0}',cancel_note='{1}',bank_note='{2}',", ocm.cancel_status, ocm.cancel_note, ocm.bank_note);
sqlstr.AppendFormat(@"cancel_createdate='{0}',cancel_updatedate='{1}',cancel_ipfrom='{2}'", CommonFunction.GetPHPTime(ocm.cancel_createdate.ToString("yyyy-MM-dd 0:0:0")), CommonFunction.GetPHPTime(ocm.cancel_updatedate.ToString("yyyy-MM-dd 0:0:0")), ocm.cancel_ipfrom);
sqlstr.AppendFormat(@" where cancel_id='{0}';set sql_safe_updates=1;", ocm.cancel_id);
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
#region 若歸檔,判斷是否成立退款單
if (ocm.cancel_status == 1)
{
sql.AppendFormat(@"select od.detail_id,od.item_id,od.item_mode,od.parent_id,od.pack_id,od.single_money,");
sql.AppendFormat(@"od.deduct_bonus,od.buy_num,od.deduct_welfare,od.accumulated_bonus,od.accumulated_happygo,");
sql.AppendFormat(@"od.deduct_happygo,od.deduct_happygo_money");
sql.AppendFormat(@" from order_detail od,order_cancel_detail ocd");
sql.AppendFormat(@" where 1=1 and od.item_mode in (0 , 1) and ocd.detail_id = od.detail_id and ocd.cancel_id ={0}", ocm.cancel_id);
DataTable dt = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
sql.Append("select parameterCode,parameterName,remark from t_parametersrc where parameterType='refundment'");
DataTable _dtpayment = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
Dictionary<uint, string> payment = new Dictionary<uint, string>();
foreach (DataRow item in _dtpayment.Rows)
{
if (!payment.Keys.Contains(uint.Parse(item["parameterCode"].ToString())))
{
payment.Add(uint.Parse(item["parameterCode"].ToString()), item["remark"].ToString());
}
}
foreach (DataRow dr in dt.Rows)
{
if (!string.IsNullOrEmpty(dr["single_money"].ToString()) && !string.IsNullOrEmpty(dr["buy_num"].ToString()))
{
total_product_pay_money += Convert.ToInt32(dr["single_money"]) * Convert.ToInt32(dr["buy_num"]);
}
if (!string.IsNullOrEmpty(dr["deduct_bonus"].ToString()))
{
total_product_deduct_bonus += Convert.ToInt32(dr["deduct_bonus"]);
}
if (!string.IsNullOrEmpty(dr["deduct_welfare"].ToString()))
{
total_product_deduct_cash += Convert.ToInt32(dr["deduct_welfare"]);
}
if (!string.IsNullOrEmpty(dr["accumulated_bonus"].ToString()))
{
accumulated_bonus += Convert.ToInt32(dr["accumulated_bonus"]);
}
if (!string.IsNullOrEmpty(dr["accumulated_happygo"].ToString()))
{
accumulated_happygo += Convert.ToInt32(dr["accumulated_happygo"]);
}
if (!string.IsNullOrEmpty(dr["deduct_happygo"].ToString()))
{
deduct_happygo += Convert.ToInt32(dr["deduct_happygo"]);
}
if (!string.IsNullOrEmpty(dr["deduct_happygo_money"].ToString()))
{
deduct_happygo_money += Convert.ToInt32(dr["deduct_happygo_money"]);
}
}
// 退款金額 = 商品購買金額 - 購物金+抵用券
cancel_money = total_product_pay_money - (total_product_deduct_bonus + total_product_deduct_cash + deduct_happygo_money);
if (!string.IsNullOrEmpty(ordermaster.Rows[0]["money_collect_date"].ToString()))
{
#region 判斷是否有付款
if (Convert.ToInt32(ordermaster.Rows[0]["money_collect_date"]) > 0 && order_amount > 0 && cancel_money > 0)
{
// 退款方式
int money_type = 0;
if (payment.Keys.Contains(order_payment))
{
money_type = int.Parse(payment[order_payment]);
}
sqlstr.Append(_serialDao.Update(46));
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
//新增退款單
sqlstr.AppendFormat(@"insert into order_money_return (money_id,order_id,money_type,money_total,");
sqlstr.AppendFormat(@"money_status,money_note,bank_note,money_source,money_createdate,money_updatedate,money_ipfrom)");
sqlstr.AppendFormat(@" values((select serial_value from serial where serial_id=46),'{0}','{1}','{2}',", ocm.order_id, money_type, cancel_money);
sqlstr.AppendFormat(@"'{0}','{1}','{2}','{3}',", 0, " ", ocm.bank_note, "cancel_id:" + ocm.cancel_id);
sqlstr.AppendFormat(@"'{0}','{1}','{2}');", CommonFunction.GetPHPTime(CommonFunction.DateTimeToString(ocm.cancel_updatedate)), CommonFunction.GetPHPTime(CommonFunction.DateTimeToString(ocm.cancel_updatedate)), ocm.cancel_ipfrom);
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
int bonus_num = accumulated_bonus;
int user_bonus = GetUserBonus(user_id, 1);
// 有付款才去扣給予的購物金
if (accumulated_bonus > 0)
{
sql.AppendFormat("select count(type_id) as num from bonus_type where type_id={0}", 32);
DataTable _dtType = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
if (accumulated_bonus > bonus_num)
{
mySqlCmd.Transaction.Rollback();
return 1;//bonus not enough;
}
if (Convert.ToInt32(_dtType.Rows[0][0]) <= 0)
{
mySqlCmd.Transaction.Rollback();
return 2;//bonus type error
}
else
{
sqlstr.Append(Deduct_User_Bonus(accumulated_bonus, ocm.order_id.ToString(), user_id));
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
}
// 有付款才去扣給予的HG點數
if (accumulated_happygo > 0)
{
if (accumulated_happygo != 0 && !string.IsNullOrEmpty(ocm.order_id.ToString()))
{
sql.AppendFormat(@"select * from hg_deduct where order_id={0} limit 0,1", ocm.order_id.ToString());
DataTable hg_deduct = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
if (hg_deduct.Rows.Count > 0)
{
sqlstr.Append(Deduct_User_Happy_Go(accumulated_happygo, ocm.order_id.ToString(), hg_deduct));
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
else
{
mySqlCmd.Transaction.Rollback();
return 3;//取得身分證字號失敗;
}
}
else
{
mySqlCmd.Transaction.Rollback();
return 4;//扣除HappyGo點數失敗;
}
}
}
#endregion
}
// 寫入付款單退款金額
if (cancel_money > 0)
{
sqlstr.AppendFormat(@"set sql_safe_updates=0;update order_master set money_cancel = money_cancel +{0} where order_id ='{1}';set sql_safe_updates=1;", cancel_money, ocm.order_id);
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
sql.AppendFormat(" select *from bonus_type where type_id='{0}';", 4);
DataTable _dtbonus = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
//判斷是否要退回購物金
if (total_product_deduct_bonus > 0)
{
if (_dtbonus.Rows.Count <= 0)
{
mySqlCmd.Transaction.Rollback();
return 2;//bonus type error !
}
sqlstr.Append(Deduct_Refund(ocm.order_id, total_product_deduct_bonus, 0, 0, ocm.cancel_ipfrom));
if (!string.IsNullOrEmpty(sqlstr.ToString()))
{
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
}
// 退回購買扣抵的hp點數
if (deduct_happygo > 0)
{
if (ocm.order_id != 0 && deduct_happygo != 0)
{
sqlstr.Append(Deduct_Refund(ocm.order_id, 0, 0, deduct_happygo, ocm.cancel_ipfrom));
if (!string.IsNullOrEmpty(sqlstr.ToString().Trim()))
{
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
}
else
{
return 4;//扣除HappyGo點數失敗;
}
}
#region 商品數量補回
foreach (DataRow dr in dt.Rows)
{
if (dr["item_mode"].ToString() == "1")
{
DataTable p = Get_Combined_Product(ocm.order_id, dr["parent_id"].ToString(), dr["pack_id"].ToString());
if (p.Rows.Count > 0)
{
foreach (DataRow drp in p.Rows)
{
//modify_item_stock((int)drp["item_id"], (int)drp["buy_num"] * (int)drp["parent_num"]);
sqlstr.AppendFormat(@"set sql_safe_updates=0;update product_item set item_stock = item_stock +{0} where item_id ='{1}';set sql_safe_updates=1;", Convert.ToInt32(drp["buy_num"]) * Convert.ToInt32(drp["parent_num"]), drp["item_id"].ToString());
}
}
}
else
{
//modify_item_stock((int)dr["item_id"], (int)dr["buy_num"]);
sqlstr.AppendFormat(@"set sql_safe_updates=0;update product_item set item_stock = item_stock +{0} where item_id ='{1}';set sql_safe_updates=1;", dr["buy_num"], dr["item_id"]);
}
}
#endregion
}
if (!string.IsNullOrEmpty(sqlstr.ToString()))
{
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
sql.AppendFormat("SELECT count(*) total FROM order_detail INNER JOIN order_slave on order_detail.slave_id = order_slave.slave_id WHERE order_detail.detail_status not in (89,90,91) and order_slave.order_id ='{0}'", ocm.order_id);
DataTable _dtrow = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
if (Convert.ToInt32(_dtrow.Rows[0][0]) == 0)
{
sql.AppendFormat("select priority FROM order_master WHERE order_id ='{0}';", ocm.order_id);
DataTable _dtpriority = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
if (Convert.ToInt32(_dtpriority.Rows[0]["priority"]) == 1)
{
sqlstr.AppendFormat("set sql_safe_updates=0;update order_master set priority = 0 where order_id='{0}';set sql_safe_updates=1;", ocm.order_id);
sqlstr.AppendFormat("set sql_safe_updates=0; update users set first_time = 0 where user_id ='{0}';set sql_safe_updates=1;", user_id);
}
}
if (!string.IsNullOrEmpty(sqlstr.ToString()))
{
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
sql.AppendFormat("SELECT count(*) total FROM order_detail INNER JOIN order_slave on order_detail.slave_id = order_slave.slave_id WHERE order_detail.detail_status not in (89,90,91)and order_detail.site_id = 7 and order_slave.order_id ='{0}';", ocm.order_id);
DataTable _dtodRow = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
if (Convert.ToInt32(_dtodRow.Rows[0][0]) == 0)
{
sql.AppendFormat(" select *from user_recommend where order_id='{0}';", ocm.order_id);
_dtodRow = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
if (_dtodRow.Rows.Count > 0)
{
sqlstr.AppendFormat(" set sql_safe_updates=0;update user_recommend set is_recommend = 0 where id ='{0}';set sql_safe_updates=1;", _dtodRow.Rows[0]["id"]);
}
}
#endregion
if (!string.IsNullOrEmpty(sqlstr.ToString()))
{
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
mySqlCmd.Transaction.Commit();
return 100;//完成!
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("OrderCancelMasterDao-->Update-->" + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
}