/**
*Add by chaojie1124j 2015/08/20
*實現訂單內容裡面的<取消整筆訂單>
*/
public int ReturnAllOrder(OrderMaster om)
{
_serialDao = new SerialDao(connString);
DataTable ordermaster = GetOrderMaster(om.Order_Id);
StringBuilder sql = new StringBuilder();
StringBuilder sqlstr = new StringBuilder();
MySqlCommand mySqlCmd = new MySqlCommand();
MySqlConnection mySqlConn = new MySqlConnection(connString);
om.Replace4MySQL();
int result = 0;
int accumulated_bonus = 0;
int accumulated_happygo = 0;
int deduct_happygo = 0;
if (ordermaster.Rows.Count <= 0)
{
return 0;//Order error;
}
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;
List<OrderSlave> orderSlaveList = new List<OrderSlave>();
sql.AppendFormat("select slave_id,slave_status from order_slave where order_id='{0}';", om.Order_Id);
Dictionary<uint, string> SendMail = new Dictionary<uint, string>();
orderSlaveList = _accessMySql.getDataTableForObj<OrderSlave>(sql.ToString());
sql.Clear();
List<OrderDetail> orderDetailList = new List<OrderDetail>();
sql.Append("select od.detail_id,od.item_id,od.buy_num,od.parent_num,od.item_mode,od.accumulated_bonus,od.accumulated_happygo,od.deduct_happygo");
sql.AppendFormat(" FROM order_detail od,order_slave os WHERE os.order_id ='{0}' AND os.slave_id = od.slave_id ;", om.Order_Id);
orderDetailList = _accessMySql.getDataTableForObj<OrderDetail>(sql.ToString());
sql.Clear();
accumulated_bonus =string.IsNullOrEmpty(ordermaster.Rows[0]["accumulated_bonus"].ToString())?0:Convert.ToInt32(ordermaster.Rows[0]["accumulated_bonus"]);
accumulated_happygo = string.IsNullOrEmpty(ordermaster.Rows[0]["accumulated_happygo"].ToString()) ? 0 : Convert.ToInt32(ordermaster.Rows[0]["accumulated_happygo"]);
deduct_happygo = string.IsNullOrEmpty(ordermaster.Rows[0]["deduct_happygo"].ToString()) ? 0 : Convert.ToInt32(ordermaster.Rows[0]["deduct_happygo"]);
//for (int i = 0; i < orderDetailList.Count; i++)
//{
// if (orderDetailList[i].item_mode == 2)
// continue;
// accumulated_bonus += orderDetailList[i].Accumulated_Bonus;
// accumulated_happygo += orderDetailList[i].Accumulated_Happygo;
// deduct_happygo += orderDetailList[i].Deduct_Happygo;
//}
//int user_bonus = GetUserBonus( ordermaster.Rows[0]["user_id"].ToString(), 1);
//if (accumulated_bonus > user_bonus)
//{
// return 1;//消費者購物金餘額不足，無法扣除給予購物金。
//}
if (!check_order_process(Convert.ToInt32(ordermaster.Rows[0]["order_status"]), 90) && Convert.ToInt32(ordermaster.Rows[0]["deduct_card_bonus"]) == 0)
{
return 2;//error master
}
for (int i = 0; i < orderSlaveList.Count; i++)
{
if (!check_order_process(Convert.ToInt32(orderSlaveList[i].Slave_Status), 90) && Convert.ToInt32(ordermaster.Rows[0]["deduct_card_bonus"]) == 0)
{
return 3;//error slave
}
}
sqlstr.Append(modify_order_master_status(om.Order_Id.ToString(), 90, om.Order_Ipfrom));
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
//'Writer : (' . $aUser_Data['user_id'] . ')' . $aUser_Data['user_username'] . "\r\n" . $sDescription;
om.Note_Order = "Writer : " + om.User_Id + " " + om.user_name + "\r\n" + om.Note_Order;
sqlstr.Clear();
sqlstr.Append(order_master_status_record(int.Parse(om.Order_Id.ToString()), 90, om.Note_Order, om.Order_Ipfrom));
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
for (int i = 0; i < orderSlaveList.Count; i++)
{
sqlstr.Append(modify_order_slave_status(orderSlaveList[i].Slave_Id, 90, om.Order_Ipfrom));
sqlstr.Append(order_slave_status_record(orderSlaveList[i].Slave_Id, 90, om.Order_Ipfrom, om.Note_Order));
if (Convert.ToInt32(ordermaster.Rows[0]["order_date_pay"]) > 0)//只要付款了就發郵件&& Convert.ToInt32(ordermaster.Rows[0]["order_amount"]) > 0
{
if (!SendMail.Keys.Contains(orderSlaveList[i].Slave_Id))
{
SendMail.Add(orderSlaveList[i].Slave_Id, " ");//發郵件給供應商
}
}
}
if (!string.IsNullOrEmpty(sqlstr.ToString()))
{
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
if (orderDetailList.Count > 0)
{
for (int i = 0; i < orderDetailList.Count; i++)
{
sqlstr.AppendFormat(" set sql_safe_updates=0;update order_detail SET detail_status ='{0}' WHERE detail_id ='{1}';set sql_safe_updates=1; ", 90, orderDetailList[i].Detail_Id);
}
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
// 回存庫存量
for (int i = 0; i < orderDetailList.Count; i++)
{
if (orderDetailList[i].item_mode == 1)
continue;
uint Buy_Num = orderDetailList[i].Buy_Num;
if (orderDetailList[i].item_mode == 2)
{
Buy_Num = orderDetailList[i].Buy_Num * orderDetailList[i].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;", Buy_Num, orderDetailList[i].Item_Id);
}
if (!string.IsNullOrEmpty(sqlstr.ToString()))
{
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
//取回減免數量
int[] nums = new int[] { 3, 4, 5, 99 };
bool or_status = nums.Contains(Convert.ToInt32(ordermaster.Rows[0]["order_status"]));
if (!or_status)
{
sqlstr.AppendFormat(" set sql_safe_updates=0;UPDATE promotions_amount_reduce_member SET order_status = 0 where order_id ='{0}' ;set sql_safe_updates=1;", om.Order_Id);
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
int collect_date = string.IsNullOrEmpty(ordermaster.Rows[0]["money_collect_date"].ToString()) ? 0 : Convert.ToInt32(ordermaster.Rows[0]["money_collect_date"]);//付款日期有null的判斷
if (collect_date > 0 && (Convert.ToInt32(ordermaster.Rows[0]["order_amount"]) > 0 || Convert.ToInt32(ordermaster.Rows[0]["deduct_card_bonus"]) > 0))
{
int Money_Type = 0;
if (Convert.ToInt32(ordermaster.Rows[0]["order_amount"]) == 0)
{
ordermaster.Rows[0]["order_amount"] = ordermaster.Rows[0]["deduct_card_bonus"];
}
Money_Type = Convert.ToInt32(ordermaster.Rows[0]["order_payment"]);
sqlstr.Append(_serialDao.Update(46));
sqlstr.Append(" select serial_value from serial where serial_id=31;");
sqlstr.Append(" insert into order_money_return(money_id,order_id,money_type,money_total,money_status,money_note,money_source,money_createdate,money_updatedate,money_ipfrom)value( ");
sqlstr.AppendFormat("(select serial_value from serial where serial_id=31),'{0}','{1}', ", om.Order_Id, Money_Type);
sqlstr.AppendFormat("'{0}','{1}','{2}', ", Convert.ToInt32(ordermaster.Rows[0]["order_amount"]), 0, "");
sqlstr.AppendFormat("'{0}','{1}','{2}', ", "order master cancel", CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")), CommonFunction.GetPHPTime(DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")));
sqlstr.AppendFormat("'{0}'); ", om.Order_Ipfrom);
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
if (accumulated_happygo > 0)
{
sql.AppendFormat(" select status,order_id from hg_accumulate where order_id='{0}';", om.Order_Id);
DataTable _dtHG = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
if (_dtHG.Rows.Count > 0 && Convert.ToInt32(_dtHG.Rows[0]["status"]) == 0)
{
sqlstr.AppendFormat(" set sql_safe_updates=0;update hg_accumulate set error_type='{0}',status='{1}' where order_id='{2}';set sql_safe_updates=1;", "", 2, om.Order_Id);
}
else
{
sql.AppendFormat(@"select * from hg_deduct where order_id={0} limit 0,1", om.Order_Id);
DataTable hg_deduct = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
if (hg_deduct.Rows.Count > 0)
{
sqlstr.Append(Deduct_User_Happy_Go(accumulated_happygo, om.Order_Id.ToString(), hg_deduct));
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
else
{
return 4;//取得身分證字號失敗
}
}
}
}
//回收購物金在外判斷
if (accumulated_bonus > 0)
{
sqlstr.Append(Deduct_User_Bonus(accumulated_bonus, om.Order_Id.ToString(), ordermaster.Rows[0]["user_id"].ToString()));
if (!string.IsNullOrEmpty(sqlstr.ToString()))
{
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
}
//if (Convert.ToInt32(ordermaster.Rows[0]["order_payment"]) == 8 && Convert.ToInt32(ordermaster.Rows[0]["money_collect_date"]) == 0)
//{
// if(accumulated_bonus > 0)
// {
// sqlstr.Append(Deduct_User_Bonus(accumulated_bonus, om.Order_Id.ToString(), ordermaster.Rows[0]["user_id"].ToString()));
// mySqlCmd.CommandText = sqlstr.ToString();
// result = mySqlCmd.ExecuteNonQuery();
// sqlstr.Clear();
// }
//}
if (Convert.ToInt32(ordermaster.Rows[0]["order_amount"]) > 0)
{
sqlstr.AppendFormat(" set sql_safe_updates=0;UPDATE order_master SET money_cancel = '{0}' where order_id = '{1}';set sql_safe_updates=1;", Convert.ToInt32(ordermaster.Rows[0]["order_amount"]), om.Order_Id);
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
if (Convert.ToInt32(ordermaster.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;", om.Order_Id);
sqlstr.AppendFormat(" set sql_safe_updates=0;UPDATE users SET first_time = 0 WHERE user_id ='{0}';set sql_safe_updates=1;", Convert.ToInt32(ordermaster.Rows[0]["user_id"]));
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
sql.AppendFormat(" select *from user_recommend where order_id='{0}';", om.Order_Id);
DataTable _dtrecom = _accessMySql.getDataTable(sql.ToString());
sql.Clear();
if (_dtrecom.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;", Convert.ToInt32(_dtrecom.Rows[0]["id"]));
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
if (deduct_happygo > 0)
{
sqlstr.Append(Deduct_Refund(om.Order_Id, 0, 0, deduct_happygo, om.Order_Ipfrom));
if (!string.IsNullOrEmpty(sqlstr.ToString().Trim()))
{
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
}
if (Convert.ToInt32(ordermaster.Rows[0]["deduct_bonus"]) > 0)
{
sqlstr.Append(Deduct_Refund(om.Order_Id, Convert.ToInt32(ordermaster.Rows[0]["deduct_bonus"]), 0, 0, om.Order_Ipfrom));
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
if (Convert.ToInt32(ordermaster.Rows[0]["deduct_welfare"]) > 0)
{
sqlstr.Append(Deduct_Refund(om.Order_Id, 0, Convert.ToInt32(ordermaster.Rows[0]["deduct_welfare"]), 0, om.Order_Ipfrom));
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
sqlstr.AppendFormat(" set sql_safe_updates=0;UPDATE deliver_master SET delivery_status = 6 WHERE order_id = '{0}' AND type in (1 , 2);set sql_safe_updates=1;", om.Order_Id);
if (Convert.ToInt32(ordermaster.Rows[0]["deduct_card_bonus"]) > 0)
{
sqlstr.AppendFormat(" set sql_safe_updates=0;UPDATE order_slave set slave_status=90,slave_date_delivery = 0 where order_id = '{0}';set sql_safe_updates=1;", om.Order_Id);
}
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
sqlstr.Append(check_and_modify_flag(om.Order_Id, 3, om.Order_Ipfrom));
if (!string.IsNullOrEmpty(sqlstr.ToString()))
{
mySqlCmd.CommandText = sqlstr.ToString();
result = mySqlCmd.ExecuteNonQuery();
sqlstr.Clear();
}
mySqlCmd.Transaction.Commit();
// mySqlCmd.Transaction.Rollback();
send_cancel_mail_for_vendor(SendMail);
return 100;//完成
}
catch (Exception ex)
{
mySqlCmd.Transaction.Rollback();
throw new Exception("OrderCancelMasterDao-->ReturnAllOrder-->" + ex.Message, ex);
}
finally
{
if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open)
{
mySqlConn.Close();
}
}
}
