public ActionResult Query(VipCardModel m)
{
try
{
string sql = @"select *
,case when lbalance<=200 then '普通卡' when lbalance<=500 then '黄金卡' when lbalance<=1000 then '白金卡' else '钻石卡' end 'level'
from
(
select vc.cardno,vc.username,vc.phone,vc.info,
case vc.sex when 'm' then '男' when 'f' then '女' else '保密' end 'sex',
(select SUM(amount*rtype) from TbVipCardRecord where vcid=vc.vcid) 'balance',
(select SUM(amount*rtype) from TbVipCardRecord where vcid=vc.vcid and rtype=1) 'lbalance',
CONVERT(int,(select SUM(amount*rtype*-1) from TbVipCardRecord where vcid=vc.vcid and rtype=-1)) 'integral',
CONVERT(varchar,createdDate,120) 'createdDate'
from TbVipCard vc
) a";
IList <IDictionary <string, object> > data = DBHelper.QueryDicRows(sql);
m.Datas.Add("list", data);
m.Ok("查询完成");
}
catch (Exception ex)
{
m.Fail(ex);
}
return(Json(m));
}
public static bool InsertVipCardModelAndDetails(List <VipCardDetailModel> details, string activityName, int clientId)
{
bool result;
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir")))
{
try
{
dbHelper.BeginTransaction();
var clientName = GetBatchesByClientId(clientId).Select(r => r.ClientName).FirstOrDefault();
var activityId = Guid.NewGuid();
var vipCardModel = new VipCardModel()
{
ActivityId = activityId,
ActivityName = activityName,
Url = UrlPrfex + activityId
};
var insertResult = DalVipCard.InsertVipCardModel(vipCardModel, dbHelper);
result = insertResult > 0;
if (insertResult <= 0)
{
dbHelper.Rollback();
}
else
{
var list = new List <VipCardDetailModel>();
if (details != null)
{
list = details.Select(r =>
{
r.ClientName = clientName;
r.ClientId = clientId;
return(r);
}).ToList();
}
var insertDetail = DalVipCard.InsertVipCardDetailsModel(list, insertResult, dbHelper);
result = result && insertDetail;
if (!insertDetail)
{
dbHelper.Rollback();
}
else
{
OpertionLogs(OpertionType.Add, "", "", activityId.ToString());
dbHelper.Commit();
}
}
}
catch (Exception ex)
{
dbHelper.Rollback();
result = false;
logger.Error(ex.Message, ex);
}
}
return(result);
}
public ActionResult Change(VipCardModel m)
{
try
{
if (String.IsNullOrWhiteSpace(m.Cardno))
{
m.Cardno = Guid.NewGuid().ToString();
}
string sql = @"update TbVipCard set cardno=@p0 where cardno=@p1";
DBHelper.Update(sql, m.Cardno, m.OldCardno);
m.Ok("换卡成功");
}
catch (Exception ex)
{
m.Fail(ex);
}
return(Json(m));
}
public ActionResult Add(VipCardModel m)
{
try
{
if (String.IsNullOrWhiteSpace(m.Cardno))
{
m.Cardno = Guid.NewGuid().ToString();
}
string sql = @"insert into TbVipCard(cardno,username,password,phone,info,sex) values(@p0,@p1,@p2,@p3,@p4,@p5)";
DBHelper.Update(sql, m.Cardno, m.Username, m.Password, m.Phone, m.Info, m.Sex);
m.Ok("开卡成功");
}
catch (Exception ex)
{
m.Fail(ex);
}
return(Json(m));
}
public ActionResult QueryRecord(VipCardModel m)
{
try
{
string sql = @"select vcr.info,vcr.amount,
CONVERT(varchar,vcr.rtime,120) 'rtime',
vc.username,vc.cardno
from TbVipCardRecord vcr
inner join TbVipCard vc on vcr.vcid=vc.vcid";
IList <IDictionary <string, object> > data = DBHelper.QueryDicRows(sql);
m.Datas.Add("list", data);
m.Ok("查询完成");
}
catch (Exception ex)
{
m.Fail(ex);
}
return(Json(m));
}
public ActionResult Charge(VipCardModel m)
{
try
{
string sql = @"select vcid from TbVipCard where cardno=@p0";
object vcid = DBHelper.QueryOne(sql, m.Cardno);
if (vcid == null)
{
throw new Exception("会员卡不存在");
}
sql = @"insert into TbVipCardRecord(vcid,rtype,amount,info) values(@p0,@p1,@p2,@p3)";
DBHelper.Update(sql, vcid, 1, m.Amount, "会员充值");
m.Ok("充值成功");
}
catch (Exception ex)
{
m.Fail(ex);
}
return(Json(m));
}
public static int InsertVipCardModel(VipCardModel model, SqlDbHelper dbHelper)
{
const string sql = @"
INSERT INTO Configuration..VipCardSaleConfig
(ActivityId,
ActivityName,
Url
)
VALUES(
@ActivityId,
@ActivityName,
@Url
) SELECT @@IDENTITY" ;
return(Convert.ToInt32(dbHelper.ExecuteScalar(sql, CommandType.Text, new DbParameter[]
{
new SqlParameter("@ActivityId", model.ActivityId),
new SqlParameter("@ActivityName", model.ActivityName),
new SqlParameter("@Url", model.Url),
})));
}
