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