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

        }