/// <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); } }