/// <summary>
/// 新增到order_slave_status表sql語句
/// </summary>
/// <param name="oms"></param>
/// <returns></returns>
public string InsertSlave(OrderMasterStatusQuery oms)
{
StringBuilder sql = new StringBuilder();
try
{
sql.Append(@"insert into order_slave_status (serial_id,slave_id,order_status,status_description,status_ipfrom,status_createdate) Values (");
sql.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}')", oms.serial_id, oms.slave_id, oms.order_status, oms.status_description, oms.status_ipfrom, CommonFunction.GetPHPTime(DateTime.Now.ToString()));
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("OrderMasterStatusDao.InsertSlave -->" + ex.Message + sql.ToString(), ex);
}
}
public HttpResponseBase GetStatus()
{
List<OrderMasterStatusQuery> stores = new List<OrderMasterStatusQuery>();
string json = string.Empty;
try
{
OrderMasterStatusQuery query = new OrderMasterStatusQuery();
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "20");//用於分頁的變量
query.order_id = Convert.ToUInt32(Request.Params["Order_Id"].ToString());
_tabshow = new TabShowMgr(mySqlConnectionString);
int totalCount = 0;
stores = _tabshow.GetStatus(query, out totalCount);
ulong Temp_Count = 0;
foreach (var item in stores)
{
Temp_Count++;//參考自 admin.gigade100.com/order/ajax_order.php第46行
item.serial_id = Temp_Count;
}
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,'msg':'user',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;
}
public HttpResponseBase OrderWaitClick()
{
string json = string.Empty;
OrderMasterStatusQuery query = new OrderMasterStatusQuery();
_orderDetailMgr = new OrderDetailMgr(mySqlConnectionString);
uint id;
try
{
if (uint.TryParse(Request.Params["order_id"].ToString(), out id))
{
query.order_id = id;
}
if (!string.IsNullOrEmpty(Request.Params["remark"].ToString()))
{
query.status_description = "Writer : (" + (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id + ")" + (System.Web.HttpContext.Current.Session["caller"] as Caller).user_username + ":" + Request.Params["remark"];
}
query.order_status=0;//等待付款
System.Net.IPAddress[] addlist = Dns.GetHostByName(Dns.GetHostName()).AddressList;
query.status_ipfrom = addlist[0].ToString();
json = _orderDetailMgr.OrderWaitClick(query);//"{success:true}";//返回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:flase,msg:1}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public string OrderWaitClick(OrderMasterStatusQuery query)
{
string json = "";
Serial s = new Serial();//流水號
ArrayList sql = new ArrayList();
OrderMaster om = new OrderMaster();
OrderSlave os = new OrderSlave();
OrderMasterStatusQuery oms = new OrderMasterStatusQuery();
List<OrderSlaveQuery> vendor = new List<OrderSlaveQuery>();
try
{
vendor = _orderSlaveDao.GetVendor(query.order_id);
if (vendor.Count > 0)
{
uint a = 1;
om.Order_Id = query.order_id;
om.Order_Status = query.order_status;
om.Order_Ipfrom = query.status_ipfrom;
//獲取變更order_master.status和付款money的sql
sql.Add(_orderMaterDao.UpdateOrderMasterStatus(om));
s = _serial.GetSerialById(29);//獲取訂單主檔狀態流水號
sql.Add(_serial.Update(29));//變更流水號+1
query.serial_id = s.Serial_Value+a;
//往訂單記錄表插入一條等待付款的狀態數據
sql.Add(_orderMaterStatusDao.Insert(query));
//
foreach (var item in vendor)
{
os.Slave_Id = item.Slave_Id;
os.Slave_Ipfrom = query.status_ipfrom;
sql.Add(_orderSlaveDao.UpdOrderSlaveStatus(os));
//往order_slave_status表插入數據
s = _serial.GetSerialById(31);//獲取slaver狀態表流水號
sql.Add(_serial.Update(31));//變更流水號+1
oms.serial_id = s.Serial_Value + a;
oms.slave_id = item.Slave_Id;
oms.order_status = query.order_status;
oms.status_description = query.status_description;
oms.status_ipfrom = query.status_ipfrom;
sql.Add(_orderMaterStatusDao.InsertSlave(oms));
a++;
}
if (_mySqlDao.ExcuteSqlsThrowException(sql))
{
json = "{success:true}";
}
else
{
json = "{success:true,msg:2}";//執行sql報錯
}
}
else
{
json = "{success:flase,msg:3}";//slave沒有數據
}
return json;
}
catch (Exception ex)
{
throw new Exception("OrderDetailMgr-->OrderWaitClick-->sql:" + sql + ",Message:" + ex.Message, ex);
}
}
public string OMSRecord(OrderMasterStatusQuery query)
{
StringBuilder sql = new StringBuilder();
try
{
sql.Append("insert into order_master_status(serial_id,order_id,order_status,status_description,status_ipfrom,status_createdate) values( ");
sql.AppendFormat("'{0}','{1}','{2}','{3}','{4}','{5}');", query.serial_id, query.order_id, query.order_status, query.status_description, query.status_ipfrom, CommonFunction.GetPHPTime());
return sql.ToString();
}
catch (Exception ex)
{
throw new Exception("OrderMasterDao-->OMSRecord-->" + sql.ToString() + ex.Message, ex);
}
}
/// <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;
}
public ArrayList ModifyOrderStatus(OrderMasterQuery query, OrderMasterStatusQuery omsQuery)
{
ArrayList arrList = new ArrayList();
try
{
arrList.Add(_orderMasterDao.ModifyOrderStatus(query));
omsQuery.serial_id = Convert.ToUInt64(_orderMasterDao.GetNextSerial(new Serial { Serial_id = 29 }).Rows[0][0]);
omsQuery.order_id = query.Order_Id;
omsQuery.status_description = query.status_description;
omsQuery.order_status = query.Order_Status;
arrList.Add(_orderMasterDao.OMSRecord(omsQuery));
return arrList;
}
catch (Exception ex)
{
throw new Exception("OrderMasterMgr-->ModifyOrderStatus-->" + ex.Message, ex);
}
}
public string ChangePayMent(OrderMasterQuery query)
{
string json = string.Empty;
ArrayList arrList = new ArrayList();
OrderMasterStatusQuery omsQuery = new OrderMasterStatusQuery();
OrderShowMasterQuery osmQuery = new OrderShowMasterQuery();
osmQuery = _orderMasterDao.GetData(query.Order_Id);
try
{
if (query.payment == "T_CAT")
{
query.Order_Payment = 8;
query.Order_Date_Pay = (uint)CommonFunction.GetPHPTime();
query.status_description = query.username + "轉黑貓貨到付款";
query.Delivery_Store = (uint)query.delivery;
#region modify_order_status
arrList.Add(_orderMasterDao.ModifyOrderStatus(query));
omsQuery.serial_id = Convert.ToUInt64(_orderMasterDao.GetNextSerial(new Serial { Serial_id = 29 }).Rows[0][0]);
omsQuery.order_id = query.Order_Id;
omsQuery.status_description = query.status_description;
omsQuery.order_status = 2;
omsQuery.status_ipfrom = query.Order_Ipfrom;
query.user_id = osmQuery.user_id;
arrList.Add(_orderMasterDao.OMSRecord(omsQuery));
DataTable _dt = _orderMasterDao.IsFirstTime(query);
if (_dt != null && _dt.Rows.Count > 0)
{
arrList.Add(_orderMasterDao.UpFirstTime(query));
}
#endregion
//加30運費
if (osmQuery.order_freight_normal != 0)
{
query.Order_Freight_Normal = osmQuery.order_freight_normal += 30;
query.t_cat_amount += 30;
}
if (osmQuery.order_freight_low != 0)
{
query.Order_Freight_Low = osmQuery.order_freight_low += 30;
query.t_cat_amount += 30;
}
if (query.t_cat_amount != 0)
{
query.Order_Amount = osmQuery.order_amount += query.t_cat_amount;
}
}
if (query.delivery == 12)
{
if (osmQuery.order_status == 0)
{
query.Order_Amount = osmQuery.order_amount - (osmQuery.order_freight_normal + osmQuery.order_freight_low);
query.Order_Freight_Normal = 0;
query.Order_Freight_Low = 0;
query.Order_Payment = 9;
query.Order_Date_Pay = (uint)CommonFunction.GetPHPTime();
query.status_description = query.username + "轉自取，現金";
#region modify_order_status
arrList.Add(_orderMasterDao.ModifyOrderStatus(query));
omsQuery.serial_id = Convert.ToUInt64(_orderMasterDao.GetNextSerial(new Serial { Serial_id = 29 }).Rows[0][0]);
omsQuery.order_id = query.Order_Id;
omsQuery.status_description = query.status_description;
omsQuery.order_status = 2;
omsQuery.status_ipfrom = query.Order_Ipfrom;
arrList.Add(_orderMasterDao.OMSRecord(omsQuery));
query.user_id = osmQuery.user_id;
DataTable _dt = _orderMasterDao.IsFirstTime(query);
if (_dt != null && _dt.Rows.Count > 0)
{
arrList.Add(_orderMasterDao.UpFirstTime(query));
}
#endregion
int master_total = osmQuery.accumulated_bonus;
if (master_total > 0)
{
BonusMaster bm = new BonusMaster();
bm.master_start = (uint)CommonFunction.GetPHPTime();
bm.master_end = bm.master_start + (86400 * 90);
bm.master_note = query.Order_Id.ToString();
bm.master_writer = "Writer : 轉自取發放購物金";
bm.type_id = 30;
bm.master_total = (uint)master_total;
bm.master_balance = master_total;
bm.user_id = osmQuery.user_id;
bm.master_createdate = (uint)CommonFunction.GetPHPTime();
bm.master_updatedate = bm.master_createdate;
bm.master_ipfrom = query.Order_Ipfrom;
_orsDao.Bonus_Master_Add(bm);
}
}
else
{
DataTable _serDt = _orderMasterDao.GetNextSerial(new Serial { Serial_id = 29 });
omsQuery.serial_id = Convert.ToUInt64(_serDt.Rows[0][0]);
omsQuery.order_id = query.Order_Id;
omsQuery.status_description = query.username + "轉自取";
omsQuery.order_status = osmQuery.order_status;
omsQuery.status_ipfrom = query.Order_Ipfrom;
arrList.Add(_orderMasterDao.OMSRecord(omsQuery));
}
arrList.Add(_orderMasterDao.UpDeliveryMaster(query.Order_Id, query.Delivery_Store));
}
//更新order_master
arrList.Add(_orderMasterDao.UpdateOrderMaster(query, osmQuery));
if (_mysqlDao.ExcuteSqlsThrowException(arrList))
{
json = "{success:true}";
}
else
{
json = "{success:false}";
}
}
catch (Exception ex)
{
throw new Exception("OrderMasterMgr-->ChangePayMent-->" + ex.Message, ex);
}
return json;
}
public string CancelReturnPurchaes(OrderReturnMasterQuery query)
{
string json = string.Empty;
try
{
int ors_status=0;
DataTable _dtStatus = _orderReturnMaster.OrsStatus(query.order_id);
if (_dtStatus.Rows.Count == 0)
{
ors_status = 0;
}
else
{
if (_dtStatus.Rows[0][0].ToString() == "")
{
ors_status = 0;
}
else
{
ors_status = Convert.ToInt32(_dtStatus.Rows[0][0]);
}
}
if (ors_status == 0 && query.return_status == 0)
{
OrderMasterStatusQuery statusquery = new OrderMasterStatusQuery();
OrderDetailQuery detailquery = new OrderDetailQuery();
ArrayList arrList = new ArrayList();
query.return_status = 2;
arrList.Add(_orderReturnMaster.UpOrderReturnMaster(query));
DataTable _dt = _orderReturnMaster.GetSerialID(29);
statusquery.serial_id = Convert.ToUInt64(_dt.Rows[0][0]);
statusquery.order_id = query.order_id;
statusquery.order_status = query.return_status;
statusquery.status_description = "Writer:(" + query.user_id + ")" + query.user_username + ",return_id:" + query.return_id + ",取消退貨請協助通知營管貨品確實出貨";
statusquery.status_ipfrom = query.return_ipfrom;
arrList.Add(_orderReturnMaster.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)
{
arrList.Add(_orderDetailDao.UpdateOrderDetailSome(detailquery));
}
else
{
arrList.Add(_orderDetailDao.UpdateOrderDetail(detailquery));
}
}
if (_mySqlDao.ExcuteSqlsThrowException(arrList))
{
json = "{success:true}";
}
else
{
json = "{success:false}";
}
}
else
{
json = "{success:false,msg:'1'}";
}
return json;
}
catch (Exception ex)
{
throw new Exception("OrderReturnMasterMgr.CancelReturnPurchaes-->" + ex.Message, ex);
}
}