public List<BonusMasterQuery> GetBonusMasterList(BonusMasterQuery query, ref int totalCount) { StringBuilder sql = new StringBuilder(); StringBuilder sqlCondi = new StringBuilder(); try { sql.Append(@"select bm.master_id,bm.user_id,bm.type_id,bm.master_total,bm.master_balance"); sql.Append(@",bm.master_note,bm.master_writer, FROM_UNIXTIME(bm.master_start) as smaster_start,FROM_UNIXTIME(bm.master_end) as smaster_end,bm.bonus_type"); sql.Append(@",bt.type_admin_link,bt.type_description,FROM_UNIXTIME(bm.master_createdate) as smaster_createtime "); sqlCondi.Append(@" from bonus_master bm "); sqlCondi.Append(@" left join bonus_type bt on bm.type_id=bt.type_id "); sqlCondi.Append(@" where 1=1"); if (query.user_id != 0) { sqlCondi.AppendFormat(@" and bm.user_id='{0}'", query.user_id); } sqlCondi.Append(@" order by bm.master_end desc,bm.master_start desc"); totalCount = 0; if (query.IsPage) { DataTable _dt = _access.getDataTable("select count(1) as totalCount " + sqlCondi.ToString()); if (_dt.Rows.Count > 0) { totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"].ToString()); } sqlCondi.AppendFormat(" limit {0},{1}", query.Start, query.Limit); } sql.Append(sqlCondi.ToString()); return _access.getDataTableForObj<BonusMasterQuery>(sql.ToString()); } catch (Exception ex) { throw new Exception("BonusMasterDao-->GetBonusMasterList-->" + sql.ToString() + ex.Message, ex); } }
public DataTable GetBonusMasterList(BonusMasterQuery query) { try { return _IBonusMasterDao.GetBonusMasterList(query); } catch (Exception ex) { throw new Exception("BonusMasterMgr-->GetBonusMasterList-->" + ex.Message, ex); } }
public DataTable GetBonusMasterList(BonusMasterQuery query) { StringBuilder sql = new StringBuilder(); try { sql.AppendFormat(@"SELECT SUM(master_total) AS master_total ,SUM(master_balance)AS master_balance FROM bonus_master WHERE user_id={0} AND bonus_type={1} and ('{2}' between master_start and master_end);", query.user_id, query.bonus_type,CommonFunction.GetPHPTime()); return _access.getDataTable(sql.ToString()); } catch (Exception ex) { throw new Exception("BonusMasterDao-->GetBonusMasterList-->" + sql.ToString() + ex.Message, ex); } }
public List<BonusMasterQuery> GetBonusMasterList(BonusMasterQuery query, ref int totalCount) { try { List<BonusMasterQuery> store = _IBonusMasterDao.GetBonusMasterList(query, ref totalCount); DateTime now = DateTime.Now; foreach (BonusMasterQuery item in store) { item.master_status = _IBonusMasterDao.CheckBonusMasterStatus(item, now); } return store; } catch (Exception ex) { throw new Exception("BonusMasterMgr-->GetBonusMasterList-->" + ex.Message, ex); } }
public string InsertBonusMaster(BonusMasterQuery store) { StringBuilder sql = new StringBuilder(); sql.Append("insert into bonus_master(`master_id`,`user_id`,`type_id`,`master_total`,`master_balance`,`master_note` "); sql.Append(" ,`master_writer`,`master_start`,`master_end`,`master_createdate`,`master_updatedate`,`master_ipfrom`,`bonus_type`)"); sql.AppendFormat("values({0},{1},{2},{3},{4},'{5}'", store.master_id, store.user_id, store.type_id, store.master_total, store.master_balance, store.master_note); sql.AppendFormat(",'{0}',{1},{2},{3},{4},'{5}',{6});", store.master_writer, CommonFunction.GetPHPTime(store.smaster_start.ToString()), CommonFunction.GetPHPTime(store.smaster_end.ToString()), CommonFunction.GetPHPTime(store.smaster_createtime.ToString()), CommonFunction.GetPHPTime(store.smaster_updatedate.ToString()), store.master_ipfrom, store.bonus_type); return sql.ToString(); }
/// <summary> /// 計算購物金每條詳情的狀態 /// // 購物金 ///$aLang['Bonus_Status'][1] = '尚未開通'; ///$aLang['Bonus_Status'][2] = '已過期'; ///$aLang['Bonus_Status'][3] = '未使用'; ///$aLang['Bonus_Status'][4] = '尚餘點數'; ///$aLang['Bonus_Status'][5] = '已用完'; ///$aLang['Bonus_Status'][6] = '已取消'; /// </summary> /// <param name="query"></param> /// <param name="now"></param> /// <param name="master_id"></param> /// <returns></returns> public int CheckBonusMasterStatus(BonusMasterQuery query, DateTime now, int master_id = 1) { StringBuilder sql = new StringBuilder(); StringBuilder sqlCondi = new StringBuilder(); try { sql.Append(@"select count(1) as search_total"); sqlCondi.Append(@" from bonus_record "); sqlCondi.AppendFormat(@" where master_id={0}", master_id); sqlCondi.Append(@" and type_id = 63"); sql.Append(sqlCondi.ToString()); DataTable _dt = _access.getDataTable(sql.ToString()); if (_dt.Rows.Count > 0) { int search_total = Convert.ToInt32(_dt.Rows[0]["search_total"].ToString()); if (search_total > 0) { return 6; } } if (now < query.smaster_start) { return 1; } else if (now > query.smaster_end) { return 2; } else if (query.master_total <= query.master_balance) { return 3; } else if (query.master_balance > 0) { return 4; } else { return 5; } } catch (Exception ex) { throw new Exception("BonusMasterDao-->GetBonusMasterList-->" + sql.ToString() + ex.Message, ex); } }
public string Deduct_User_Bonus(int deduct_bonus, string order_id, string user_id) { BonusMasterQuery b = new BonusMasterQuery(); BonusRecord br = new BonusRecord(); Serial s = new Serial(); _serial = new SerialDao(connString); _bonus = new BonusMasterDao(connString); List<BonusMaster> store = new List<BonusMaster>(); List<BonusMaster> store2 = new List<BonusMaster>(); System.Net.IPAddress[] ips = System.Net.Dns.GetHostByName(System.Net.Dns.GetHostName()).AddressList; if (ips.Count() > 0) { b.master_ipfrom = ips[0].ToString(); } StringBuilder sql = new StringBuilder(); StringBuilder sqlstr = new StringBuilder(); try { int bonus_num = deduct_bonus; //是否發放購物金 b.master_note = order_id; b.master_total = uint.Parse(deduct_bonus.ToString()); b.bonus_type = 1; store = _bonus.GetBonus(b); if (store.Count == 1) { bool user = false; foreach (var item in store) { if (item.master_total == item.master_balance) {//判斷發放的購物金是否使用 user = true; bonus_num = 0; } else { bonus_num = bonus_num - item.master_balance; } b.master_id = item.master_id; b.masterid = item.master_id.ToString() + ","; } s = _serial.GetSerialById(28); uint a = 1; #region 發放購物金扣除 if (deduct_bonus - bonus_num > 0) { //先清除bonus_master表裏面的發放的購物金 b.master_balance = deduct_bonus - bonus_num; b.master_writer = "訂單取消"; sqlstr.Append(_bonus.UpBonusMaster(b)); //并記錄到購物金記錄表中 sqlstr.Append(_serial.Update(28)); br.record_id = uint.Parse(s.Serial_Value.ToString()) + a; a++; br.master_id = b.master_id; br.type_id = 32; br.order_id = uint.Parse(b.master_note); br.record_use = uint.Parse(b.master_balance.ToString()); br.record_note = order_id; br.record_writer = "訂單整筆取消"; br.record_ipfrom = b.master_ipfrom; sqlstr.Append(_bonus.InsertBonusRecord(br)); } if (!user) {//發放購物金被使用 b.user_id = uint.Parse(user_id.ToString()); b.masterid = b.masterid.TrimEnd(','); uint order = uint.Parse(b.master_note.ToString()); b.master_note = null; b.master_total = 0; b.usebonus = "K"; //該用戶剩餘可用購物金 store2 = _bonus.GetBonus(b); foreach (var item in store2) { if (bonus_num > item.master_balance) { b.master_balance = item.master_balance; bonus_num = bonus_num - item.master_balance; } else { b.master_balance = bonus_num; bonus_num = 0; } //變更bonus_master表裏面的發放的購物金 b.master_id = item.master_id; sqlstr.Append(_bonus.UpBonusMaster(b)); //記錄到購物金記錄表中 sqlstr.Append(_serial.Update(28)); br.record_id = uint.Parse(s.Serial_Value.ToString()) + a; a++; br.master_id = b.master_id; br.type_id = 32; br.order_id = order; br.record_use = uint.Parse(b.master_balance.ToString()); br.record_note = order_id; br.record_writer = "訂單取消被使用,額外扣除購物金"; br.record_ipfrom = b.master_ipfrom; sqlstr.Append(_bonus.InsertBonusRecord(br)); if (bonus_num == 0) { break; } } } if (bonus_num > 0) {//該用戶剩餘的購物金不夠扣剩下記錄到表中 sqlstr.AppendFormat(@"insert into users_deduct_bonus (deduct_bonus,user_id,createdate,order_id)"); sqlstr.AppendFormat(@" values('{0}','{1}','{2}','{3}');", bonus_num, user_id, CommonFunction.GetPHPTime(DateTime.Now.ToString()), order_id); } #endregion } return sqlstr.ToString(); } catch (Exception ex) { throw new Exception("OrderReturnlMasterDao-->Deduct_User_Bonus-->" + ex.Message, ex); } }
public ArrayList regainBonus(BonusMasterQuery bm, BonusRecord br) { ArrayList arrList = new ArrayList(); try { arrList.Add(_IBonusMasterDao.UpdateBonusMaster(bm)); arrList.Add(_IBonusMasterDao.InsertBonusRecord(br)); return arrList; } catch (Exception ex) { throw new Exception("BonusMasterMgr-->regainBonus-->" + ex.Message, ex); } }
/// <summary> /// 更新bonus_master表,收回購物金 /// </summary> /// <param name="query"></param> /// <returns></returns> public string UpBonusMaster(BonusMasterQuery query) { StringBuilder sql = new StringBuilder(); try { sql.AppendFormat("update bonus_master set master_balance=master_balance-{3},master_updatedate='{0}',master_ipfrom='{1}',master_writer='{4}' where master_id in ({2});", CommonFunction.GetPHPTime(), query.master_ipfrom, query.master_id, query.master_balance, query.master_writer); return sql.ToString(); } catch (Exception ex) { throw new Exception("BonusMasterDao-->UpBonusMaster-->" + sql.ToString() + ex.Message, ex); } }
/// <summary> /// 獲取使用購物金總數(單一商品,組合商品) /// </summary> /// <param name="query"></param> /// <returns></returns> public int BonusAmount(BonusMasterQuery query) { StringBuilder sql = new StringBuilder(); try { sql.AppendFormat("select os.order_id,sum(od.accumulated_bonus) as 'sumBonus' from order_detail od LEFT JOIN order_slave os on os.slave_id=od.slave_id where os.order_id='{0}' and item_mode in (0,1);",query.master_note); return int.Parse(_access.getDataTable(sql.ToString()).Rows[0]["sumBonus"].ToString()); } catch (Exception ex) { throw new Exception("BonusMasterDao-->BonusAmount-->" + sql.ToString() + ex.Message, ex); } }
public HttpResponseBase BonusSearchList() { List<BonusMasterQuery> stores = new List<BonusMasterQuery>(); string json = string.Empty; try { BonusMasterQuery query = new BonusMasterQuery(); UInt32 uint32 = 0; query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量 query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "20");//用於分頁的變量 if (UInt32.TryParse(Request.Params["uid"], out uint32)) { query.user_id = uint32; } bool status = true; if (!string.IsNullOrEmpty(Request.Params["relation_id"]))//待回覆 { query.master_id = Convert.ToUInt32(Request.Params["relation_id"]); } if (bool.TryParse(Request.Params["use"], out status)) { query.use = status; } if (bool.TryParse(Request.Params["using"], out status)) { query.useing = status; } if (bool.TryParse(Request.Params["used"], out status)) { query.used = status; } if (bool.TryParse(Request.Params["usings"], out status)) { query.useings = status; } if (bool.TryParse(Request.Params["useds"], out status)) { query.useds = status; } if (!string.IsNullOrEmpty(Request.Params["userNameMail"])) { query.user_name = Request.Params["userNameMail"]; query.user_email = query.user_name; } DateTime dt; if (DateTime.TryParse(Request.Params["timestart"], out dt)) { query.smaster_start = Convert.ToDateTime(dt.ToString("yyyy-MM-dd HH:mm:ss")); } if (DateTime.TryParse(Request.Params["timeend"], out dt)) { query.smaster_end = Convert.ToDateTime(dt.ToString("yyyy-MM-dd HH:mm:ss")); ; } if (UInt32.TryParse(Request.Params["bonus_type"], out uint32)) { query.bonus_type = uint32; } if (UInt32.TryParse(Request.Params["type_id"], out uint32)) { query.type_id = uint32; } _uslmpgr = new UsersListMgr(mySqlConnectionString); int totalCount = 0; stores = _uslmpgr.bQuery(query, out totalCount); foreach (var item in stores) { //獲取時間 item.smaster_start = CommonFunction.GetNetTime(item.master_start); item.smaster_end = CommonFunction.GetNetTime(item.master_end); item.smaster_createtime = CommonFunction.GetNetTime(item.master_createdate); item.now_time = Convert.ToInt32(CommonFunction.GetPHPTime()); if (Convert.ToBoolean(Request.Params["isSecret"])) { if (!string.IsNullOrEmpty(item.user_name)) { item.user_name = item.user_name.Substring(0, 1) + "**"; } item.user_email = item.user_email.Split('@')[0] + "@***"; } } 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; }
public HttpResponseBase UsersList() { List<UsersListQuery> stores = new List<UsersListQuery>(); List<SiteConfig> configs = new List<SiteConfig>(); SiteConfig con = new SiteConfig(); string json = string.Empty; try { string path = Server.MapPath(xmlPath); if (System.IO.File.Exists(path)) { siteConfigMgr = new SiteConfigMgr(path); configs = siteConfigMgr.Query(); } foreach (SiteConfig site in configs) { if (site.Name == "DoMain_Name") { con = site; break; } } UsersListQuery query = new UsersListQuery(); query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量 query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "20");//用於分頁的變量 //todo:分页汇出会员信息,由于不能确定是按分页汇出还是汇出全部会员信息,暂且保留汇出全部会员信息 //start = query.Start; //limit = query.Limit; if (!string.IsNullOrEmpty(Request.Params["serchs"])) { query.serchtype = Request.Params["serchs"]; } if (!string.IsNullOrEmpty(Request.Params["timestart"])) { query.serchstart = Convert.ToDateTime(Request.Params["timestart"]); query.serchstart = Convert.ToDateTime(query.serchstart.ToString("yyyy-MM-dd HH:mm:ss")); } if (!string.IsNullOrEmpty(Request.Params["timeend"])) { query.serchend = Convert.ToDateTime(Request.Params["timeend"]); query.serchend = Convert.ToDateTime(query.serchend.ToString("yyyy-MM-dd HH:mm:ss")); } query.content = Request.Params["serchcontent"]; query.types = Request.Params["bonus_type"]; query.checks = Request.Params["checkbox1"]; if (!string.IsNullOrEmpty(Request.Params["relation_id"]))//待回覆 { query.user_id = Convert.ToUInt32(Request.Params["relation_id"]); } _uslmpgr = new UsersListMgr(mySqlConnectionString); _paraMgr = new ParameterMgr(mySqlConnectionString); int totalCount = 0; stores = _uslmpgr.Query(query, out totalCount); Parametersrc pa = new Parametersrc(); foreach (var item in stores) { string[] url = con.Value.Split('/'); item.user_url = "http://" + url[0] + "/ecservice_jump.php";//?uid=" + item.user_id; if (Convert.ToBoolean(Request.Params["isSecret"])) { if (!string.IsNullOrEmpty(item.user_name)) { item.user_name = item.user_name.Substring(0, 1) + "**"; } item.user_email = item.user_email.Split('@')[0] + "@***"; //if (!string.IsNullOrEmpty(item.user_mobile)) //{ // if (item.user_phone.ToString().Length > 3) // { // item.user_phone = item.user_phone.Substring(0, 3) + "***"; // } // else // { // item.user_phone = item.user_phone + "***"; // } //} if (!string.IsNullOrEmpty(item.user_mobile)) { item.user_mobile = UnSecretMobile(item); if (item.user_mobile.ToString().Length > 3) { item.user_mobile = item.user_mobile.Substring(0, 3) + "***"; } else { item.user_mobile = item.user_mobile + "***"; } } if (item.user_address.ToString().Length > 3) { item.user_address = item.user_address.Substring(0, 3) + "***"; } else { item.user_address = item.user_address + "***"; } } else { if (item.user_mobile.Length == 48) { item.user_mobile = EncryptComputer.EncryptDecryptTextByApi(item.user_mobile, false); } } //獲取時間 item.reg_date = CommonFunction.GetNetTime(item.user_reg_date); item.sfirst_time = CommonFunction.GetNetTime(item.first_time); item.slast_time = CommonFunction.GetNetTime(item.last_time); item.sbe4_last_time = CommonFunction.GetNetTime(item.be4_last_time); pa = _paraMgr.QueryUsed(new Parametersrc { Used = 1, ParameterCode = item.user_level.ToString(), ParameterType = "UserLevel" }).FirstOrDefault(); if (pa != null) { item.userLevel = pa.parameterName; } #region 購物金欄位修改 add by yafeng0715j 20150924 BonusMasterMgr bmMgr = new BonusMasterMgr(mySqlConnectionString); BonusMasterQuery bmQuery = new BonusMasterQuery(); bmQuery.user_id = item.user_id; bmQuery.bonus_type = 1; DataTable table = bmMgr.GetBonusMasterList(bmQuery); uint master_total = 0; int master_balance = 0; if (table.Rows[0][0].ToString() != "") { master_total = Convert.ToUInt32(table.Rows[0][0]); master_balance = Convert.ToInt32(table.Rows[0][1]); } item.bonus_type = 1; item.bonus_typename = string.Format("購物金(剩餘{0}/總{1})", master_balance, master_total); master_total = 0; master_balance = 0; bmQuery.bonus_type = 2; table = bmMgr.GetBonusMasterList(bmQuery); if (table.Rows[0][0].ToString() != "") { master_total = Convert.ToUInt32(table.Rows[0][0]); master_balance = Convert.ToInt32(table.Rows[0][1]); } item.bonus_type1 = 2; item.bonus_typenamequan = string.Format("抵用券(剩餘{0}/總{1})", master_balance, master_total); #endregion } IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; //listUser是准备转换的对象 json = "{success:true,'msg':'user',totalCount:" + totalCount + ",start:" + query.Start + ",limit:" + query.Limit + ",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 bool ModifyOrderMsaterForDeliver(OrderModifyModel order) { try { OrderMaster omModel = _orderMasterDao.GetOrderMasterByOrderId4Change(order.order_id); StringBuilder sbSql = new StringBuilder(); sbSql.Append(@" om.order_status = 2, os.slave_status=2, od.detail_status=2, om.order_date_cancel=0, om.money_cancel=0, om.export_flag=1,"); sbSql.AppendFormat("om.order_date_pay ={0}", Common.CommonFunction.GetPHPTime()); if (!isCanModifyForDeliver(order.order_id)) { return false; } //1、是否寫入對帳 if (order.isBilling_checked) { sbSql.Append(",om.billing_checked=1,"); sbSql.AppendFormat("om.money_collect_date={0}", Common.CommonFunction.GetPHPTime()); } //2、紅利折抵 if (order.deduct_card_bonus != 0) { if (omModel.Order_Amount < order.deduct_card_bonus) { return false; } sbSql.AppendFormat(",om.deduct_card_bonus={0},", order.deduct_card_bonus); sbSql.AppendFormat("om.order_amount={0}", omModel.Order_Amount - order.deduct_card_bonus); } //3、要不要扣除消費者抵用購物金 if (order.isCash_record_bonus) { order.user_id = Convert.ToInt32(omModel.user_id); //order.bonus_num = order.bonus_num = Convert.ToInt32(omModel.Deduct_Bonus); order.record_note = "強制轉單扣點"; order.record_writer = "server"; AddBonusRecord(order);//扣除 } #region //4、要不要給hg點或購物金 if (order.isHGBonus) { #region 購物金 if (omModel.Accumulated_Bonus > 0) { //訂單取消後,回撥購物金的使用日期限制 int nExpire_Day = 90; DateTime nMaster_Start = DateTime.Now.Date; DateTime nMaster_End = DateTime.Now.AddDays(omModel.BonusExpireDay).Date.AddSeconds(-1); // DateTime nMaster_End = DateTime.Now.AddDays(1).Date.AddSeconds(-1); //OrderModifyModel orderModifyModel = new OrderModifyModel(); ////$amego_bonus->bonus_master_add($aOrder['user_id'], 30, $aOrder['accumulated_bonus'], $nMaster_Start, $nMaster_End, $aOrder['order_id'], '商品回饋購物金' , 1); //orderModifyModel.user_id =Convert.ToInt32( omModel.User_Id); //orderModifyModel.order_id = Convert.ToInt32(omModel.Order_Id); //orderModifyModel.bonus_num = Convert.ToInt32(omModel.Deduct_Bonus); //orderModifyModel.use_note ="強制轉單扣點"; //orderModifyModel.use_writer = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id; // 會員目前可用購物金 BonusMasterQuery query = new BonusMasterQuery(); query.user_id = omModel.User_Id; query.type_id = 30; query.master_total = Convert.ToUInt32(omModel.Accumulated_Bonus); query.master_balance = omModel.Accumulated_Bonus; query.smaster_start = nMaster_Start; query.smaster_end = nMaster_End; query.smaster_createtime = DateTime.Now; query.smaster_updatedate = DateTime.Now; query.master_writer = string.Format("Writer:{0}", (System.Web.HttpContext.Current.Session["caller"] as Caller).user_username); query.bonus_type = 1; query.master_note = "商品回饋購物金"; //Serial ser = _serialDao.GetSerialById(27); //ser.Serial_Value = ser.Serial_Value+1; //_serialDao.Update(ser); //query.master_id = Convert.ToUInt32(ser.Serial_Value); query.master_ipfrom = order.ip_from; List<BonusMasterQuery> queryList = new List<BonusMasterQuery>(); queryList.Add(query); _bonusMasterMgr.BonusMasterAdd(queryList); //bonusMasterDao.InsertBonusMaster(query); } #endregion #region hg點 if (omModel.Accumulated_Happygo > 0) { if (_happyGoMgr.GetHGDeductList(omModel.Order_Id).Count > 0) { //有則進行點數累積,無則發信通知 } else { MailHelper mailHelper = new MailHelper(); string MailTitle = "HG累點失敗"; string MailBody = string.Format("{0}無HG資料,無法累點。", omModel.Order_Id); mailHelper.SendToGroup("BonusFailure", MailTitle, MailBody); } } #endregion } #endregion //更新條件 StringBuilder sql = new StringBuilder(); sql.Append(@"update order_master om,order_slave os,order_detail od set "); sql.Append(sbSql); sql.AppendFormat(@" where om.order_id={0} AND om.order_id = os.order_id AND os.slave_id = od.slave_id ", order.order_id); try { _orderMasterDao.UpdateOrderMaster(sql.ToString()); return true; } catch (Exception ex) { throw new Exception("OrderMasterMgr-->ModifyOrderMsaterForDeliver-->" + ex.Message, ex); } } catch (Exception ex) { throw new Exception("OrderMasterMgr-->ModifyOrderMsaterForDeliver-->" + ex.Message, ex); } }
public bool BonusMasterUpdate(BonusMasterQuery store) { try { return _IBonusMasterDao.UpdateBonusMaster(store); } catch (Exception ex) { throw new Exception("BonusMasterMgr-->BonusMasterAdd-->" + ex.Message, ex); } }
public bool UpdateBonusMaster(BonusMasterQuery store) { store.Replace4MySQL(); StringBuilder sql = new StringBuilder(); try { sql.AppendFormat("update bonus_master set `user_id`='{0}',`type_id`='{1}',`master_total`='{2}',`master_balance`='{3}',`master_note`='{4}' ", store.user_id, store.type_id, store.master_total, store.master_balance, store.master_note); sql.AppendFormat(" ,`master_writer`='{0}',`master_start`='{1}',`master_end`='{2}',`master_updatedate`='{3}',`master_ipfrom`='{4}',`bonus_type`='{5}')", store.master_writer, store.master_start, store.master_end, store.master_updatedate, store.master_ipfrom, store.bonus_type); if (_access.execCommand(sql.ToString()) > 0) { return true; } else { return false; } } catch (Exception ex) { throw new Exception("BonusMasterDao-->UpdateBonusMaster-->" + sql.ToString() + ex.Message, ex); } }
public bool UpdateBonusMasterMasterBalance(BonusMasterQuery query) { string sql = String.Empty; try { sql = string.Format("update bonus_master SET master_balance = {0} where master_id={1} ", query.master_balance, query.master_id); return _access.execCommand(sql) > 0; } catch (Exception ex) { throw new Exception("BonusMasterDao-->UpdateBonusMasterMasterBalance-->" + sql.ToString() + ex.Message, ex); } }
public HttpResponseBase updateuser_master() { string json = string.Empty; BonusMasterQuery bmq = new BonusMasterQuery(); try { _uslmpgr = new UsersListMgr(mySqlConnectionString); bmq.master_id = Convert.ToUInt32(Request.Params["master_id"]); bmq.user_id = Convert.ToUInt32(Request.Params["user_id"]); bmq.master_total = Convert.ToUInt32(Request.Params["master_total"]); bmq.master_note = Convert.ToString(Request.Params["master_note"]); int already_use_bonus = 0; if (!string.IsNullOrEmpty(Request.Params["already_use_bonus"])) { already_use_bonus = int.Parse(Request.Params["already_use_bonus"]); } bmq.master_balance = int.Parse(bmq.master_total.ToString()) - already_use_bonus; bmq.master_end = Convert.ToUInt32(CommonFunction.GetPHPTime(Request.Params["smaster_end"])); bmq.master_start = Convert.ToUInt32(CommonFunction.GetPHPTime(Request.Params["smaster_start"])); _uslmpgr.updateuser_master(bmq); json = "{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:false,msg:0}"; } this.Response.Clear(); this.Response.Write(json.ToString()); this.Response.End(); return this.Response; }
//bonus_master 是否發放了購物金 public List<BonusMasterQuery> IsExtendBonus(BonusMasterQuery query) { StringBuilder sql=new StringBuilder (); try { sql.AppendFormat("select master_id,master_total,master_balance from bonus_master where master_note='{0}' and bonus_type='{1}';", query.master_note, query.bonus_type); return _access.getDataTableForObj<BonusMasterQuery>(sql.ToString()); } catch(Exception ex) { throw new Exception("BonusMasterDao-->IsExtendBonus-->" + sql.ToString() + ex.Message, ex); } }
public List<BonusMasterQuery> bQuery(BonusMasterQuery query, out int totalCount) { StringBuilder sb = new StringBuilder(); try { StringBuilder sql = new StringBuilder(); StringBuilder sqlcount = new StringBuilder(); StringBuilder sqlfrom = new StringBuilder(); StringBuilder sqlStatus = new StringBuilder(); sql.Append(@"SELECT bm.master_id,bm.user_id,bm.master_total,bm.master_balance,bm.master_start,bm.master_end,bm.master_createdate,bm.bonus_type,bm.master_note,us.user_name,us.user_email,bt.type_admin_link,bt.type_description"); sqlcount.Append("SELECT count(bm.master_id) AS search_total "); sqlfrom.Append(" FROM bonus_master bm left join bonus_type bt on bm.type_id=bt.type_id left join users us on bm.user_id=us.user_id where 1=1 "); if (query.master_id != 0) { sqlfrom.AppendFormat(" and bm.master_id='{0}' ", query.master_id); } if (query.user_id != 0) { sqlfrom.AppendFormat(" and bm.user_id='{0}' ", query.user_id); } if (query.user_email != string.Empty) { sqlfrom.AppendFormat(" and (us.user_email like '%{0}%' or us.user_name like '%{0}%' escape '/')", query.user_email); } //if (query.user_name != string.Empty) //{ // sqlfrom.AppendFormat(" and u.user_name like '%{0}%'ESCAPE'/'", query.user_name); //} if (query.smaster_start != DateTime.MinValue && query.smaster_end != DateTime.MinValue) { sqlfrom.AppendFormat(" and bm.master_createdate between {0} and {1}", CommonFunction.GetPHPTime(query.smaster_start.ToString()), CommonFunction.GetPHPTime(query.smaster_end.ToString())); } if (query.bonus_type != 0) { sqlfrom.AppendFormat(" and bm.bonus_type={0}", query.bonus_type); } if (query.type_id != 0) { sqlfrom.AppendFormat(" and bm.type_id={0}", query.type_id); } if (query.use || query.useing || query.used || query.useings || query.useds) { sqlStatus.Append(" and ("); if (query.use) { sqlStatus.AppendFormat("bm.master_start>'{0}'", CommonFunction.GetPHPTime()); } if (query.useing) { sqlStatus.AppendFormat(" or (bm.master_balance>=bm.master_total and '{0}' between bm.master_start and bm.master_end)", CommonFunction.GetPHPTime()); } if (query.used) { sqlStatus.AppendFormat(" or (bm.master_end<'{0}' and bm.master_balance>0)", CommonFunction.GetPHPTime()); } if (query.useings) {//不包含未開始的,也不包含過期的 sqlStatus.AppendFormat(" or (0<bm.master_balance and bm.master_balance<bm.master_total and '{0}' between bm.master_start and bm.master_end)", CommonFunction.GetPHPTime()); } if (query.useds) { sqlStatus.Append(" or bm.master_balance=0"); } sqlStatus.Append(")"); sqlStatus.Replace("( or", "("); } sqlfrom.Append(sqlStatus); sqlfrom.AppendFormat(" order by master_createdate DESC,master_balance DESC , master_end DESC, master_start DESC "); totalCount = 0; if (query.IsPage) { sb.Append(sqlcount.ToString() + sqlfrom.ToString() + ";"); System.Data.DataTable _dt = _access.getDataTable(sqlcount.ToString() + sqlfrom.ToString()); if (_dt != null && _dt.Rows.Count > 0) { totalCount = Convert.ToInt32(_dt.Rows[0]["search_total"]); } sqlfrom.AppendFormat(" limit {0},{1}", query.Start, query.Limit); } sb.Append(sql.ToString() + sqlfrom.ToString()); return _access.getDataTableForObj<BonusMasterQuery>(sql.ToString() + sqlfrom.ToString()); } catch (Exception ex) { throw new Exception("UsersListDao-->bQuery-->" + ex.Message + sb.ToString(), ex); } }
/// <summary> /// 該訂單是否已經發放購物金/該用戶剩餘購物金(除本訂單) /// </summary> /// <param name="query"></param> /// <returns></returns> public List<BonusMaster> GetBonus(BonusMasterQuery query) { StringBuilder sql = new StringBuilder(); try { sql.Append(@"SELECT master_id,user_id,master_total,master_balance,master_note from bonus_master bm where 1=1 "); if(!string.IsNullOrEmpty(query.master_note)) { sql.AppendFormat(" and bm.master_note='{0}' ",query.master_note); } if(query.bonus_type>0) { sql.AppendFormat(" and bm.bonus_type='{0}' ",query.bonus_type); } if(query.master_total>0) { sql.AppendFormat(" and bm.master_total='{0}' ",query.master_total); } //排除要扣除的數據 if (query.user_id > 0) { sql.AppendFormat(" and bm.user_id='{0}' ", query.user_id); } if (!string.IsNullOrEmpty(query.masterid)) { sql.AppendFormat(" and bm.master_id not in ({0})", query.masterid); } if (query.usebonus == "K") { sql.AppendFormat(" and master_end>'{0}' and master_balance>0", CommonFunction.GetPHPTime()); } return _access.getDataTableForObj<BonusMaster>(sql.ToString()); } catch (Exception ex) { throw new Exception("BonusMasterDao-->GetBonus-->" + sql.ToString() + ex.Message, ex); } }
public int updateuser_master(BonusMasterQuery store) { StringBuilder sb = new StringBuilder(); try { store.Replace4MySQL(); strSql = string.Format(@"update bonus_master set master_total='{0}',master_balance='{1}', master_start='{2}',master_end='{3}',master_note='{4}' where user_id='{5}' and master_id='{6}'", store.master_total, store.master_balance, store.master_start, store.master_end, store.master_note, store.user_id, store.master_id); sb.Append(strSql.ToString()); return _access.execCommand(strSql.ToString()); } catch (Exception ex) { throw new Exception("UsersListDao-->updateuser_master-->" + ex.Message + sb.ToString(), ex); } }
/***判斷用戶的購物金是否夠扣除*/ public int returnMsg(OrderMaster om) { StringBuilder sb = new StringBuilder(); try { _bonus = new BonusMasterDao(connString); BonusMasterQuery query = new BonusMasterQuery(); query.master_note = om.Order_Id.ToString(); query.bonus_type = 1; List<BonusMasterQuery> bonusMasterStore = new List<BonusMasterQuery>(); bonusMasterStore = _bonus.IsExtendBonus(query); if (bonusMasterStore.Count > 0) { ///用戶購物金 sb.AppendFormat("SELECT sum(od.accumulated_bonus) as accumulated_bonus FROM order_detail od,order_slave os WHERE os.order_id = '{0}'AND os.slave_id = od.slave_id and od.item_mode<>2; ", om.Order_Id); DataTable _dtbonus = _accessMySql.getDataTable(sb.ToString()); sb.Clear(); //訂單購物金 sb.AppendFormat("select user_id from order_master where order_id='{0}';", om.Order_Id); DataTable _dtUser = _accessMySql.getDataTable(sb.ToString()); int deductuser = 0; if (!string.IsNullOrEmpty(_dtUser.Rows[0]["user_id"].ToString())) { deductuser = GetUserBonus(_dtUser.Rows[0]["user_id"].ToString(), 1); } else { return 1;//訂單錯誤! } int orderbonus = string.IsNullOrEmpty(_dtbonus.Rows[0]["accumulated_bonus"].ToString()) ? 0 : Convert.ToInt32(_dtbonus.Rows[0]["accumulated_bonus"]); if (orderbonus > deductuser) { return 99;//消費者購物金餘額不足,無法扣除給予購物金 } else { return 100;//不顯示那個信息 } } else { return 100;//不顯示那個信息 } } catch (Exception ex) { throw new Exception("OrderCancelMasterDao-->returnMsg" + ex.Message, ex); } }
//是否有發放購物金 write for cj public List<BonusMasterQuery> IsExtendBonus(BonusMasterQuery query) { try { return _IBonusMasterDao.IsExtendBonus(query); } catch (Exception ex) { throw new Exception("BonusMasterMgr-->UpdateBonusMasterBalance(BonusMaster bm)-->" + ex.Message, ex); } }