public void VerifyConnectionStateChanged()
{
var connectionString = ConfigurationManager.ConnectionStrings["sqltools"].ConnectionString;
var helper = new SqlDbHelper(connectionString);
helper.ConnectionStateChanged += (x, y) => Console.WriteLine("State changed: {0}", y.State);
helper.ConnectionCreated += (x, y) => Console.WriteLine("Connection created!");
using (var cmd = new SqlCommand("select Code, Display from state"))
using (var reader = helper.ExecuteReader(cmd))
{
while (reader.Read())
{
}
}
}
public static int RemoveWhite(string pid)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir")))
return(dbHelper.ExecuteNonQuery(TireSql.StockoutStatusWhite.delete, CommandType.Text, new SqlParameter("@PID", pid)));
}
/// <summary>
/// 캐시를 지원하지 않는 <c>IDacHelper</c>를 구현하는 클래스의 새 인스턴스를 돌려줍니다.
/// </summary>
/// <param name="dbHelper">쿼리를 수행하는데 사용되는 <c>SqlDbHelper</c>입니다.</param>
/// <returns>캐시를 지원하지 않는 <c>IDacHelper</c>를 구현하는 클래스의 새 인스턴스입니다.</returns>
public static IDacHelper CreateDefault(SqlDbHelper dbHelper)
{
return new DacHelper(dbHelper);
}
//确认修改按钮
private void btn_edit_Click(object sender, EventArgs e)
{
if (text_book.Text.Trim() == "" || text_author.Text.Trim() == "" || text_press.Text.Trim() == "" || text_price.Text.Trim() == "" || text_isbn.Text.Trim() == "" || mtext_stocks.Text.Trim() == "" || mtext_year.Text.Trim() == "" || cmb_type.Text == "")
{
MessageBox.Show("内容不能为空！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
int year = Convert.ToInt16(mtext_year.Text);//获得年份
if (year > DateTime.Now.Year)
{
MessageBox.Show("输入的年份不符合要求", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
text_price.Text = string.Format("{0:#,##0.00}", Convert.ToDouble(text_price.Text)); //价格文本框必须显示2位小数点
DialogResult dialog = MessageBox.Show("确认修改为以下信息？\n图书名：" + text_book.Text + "\nISBN编号：" + text_isbn.Text + "\n图书类别：" + cmb_type.Text + "\n作者：" + text_author.Text + "\n出版社：" + text_press.Text + "\n出版年份：" + year + "\n价格：" + text_price.Text + "\n库存：" + mtext_stocks.Text, "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (DialogResult.OK == dialog)
{
//判断数量与之前相比是增加了还是减少了
int m = Convert.ToInt32(mtext_stocks.Text.Trim()) - Convert.ToInt32(Log.log.b_stocks);
if (m >= 0)
{
//插入语句
string sql_books = "insert into books values ('" + text_isbn.Text.Trim() + "',1)";
int n = 0; //定义个局部变量用于判断是否成功执行sql语句
con = dButil.SqlOpen(); //打开数据库
for (int i = 0; i < m; i++)
{
cmd = new SqlCommand(sql_books, con);
n = n + cmd.ExecuteNonQuery();
}
con.Close();//关闭数据库
if (n == m)
{
//更新语句，先更新book表中的isbn再更新books表
string sql = "update [book] set b_count=b_count+" + mtext_stocks.Text.Trim() + "-b_stocks,b_stocks='" + mtext_stocks.Text.Trim() + "',b_author='" + text_author.Text.Trim() + "',b_press='" + text_press.Text.Trim() + "',b_time='" + year + "',b_price='" + text_price.Text.Trim() + "',t_id=(select t_id from [type] where t_name='" + cmb_type.Text + "') where b_name='" + bookname + "'";
Console.WriteLine(sql);
con = dButil.SqlOpen(); //打开数据库
cmd = new SqlCommand(sql, con); //储存sql语句
n = cmd.ExecuteNonQuery(); //执行sql语句，返回影响行
con.Close(); //关闭数据库
if (n > 0) //判断是否执行成功
{
SqlDbHelper dbHelper = new SqlDbHelper(); //实例化SqlDbHelper类
dbHelper.Operation("成功图书：" + bookname + "的数据"); //插入操作记录
//成功提示
DialogResult result = MessageBox.Show("修改成功！点击确认按钮返回图书管理界面", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (DialogResult.OK == result)
{
//返回图书管理界面
btn_ret_Click(sender, e);
}
}
else
{
SqlDbHelper dbHelper = new SqlDbHelper(); //实例化SqlDbHelper类
dbHelper.Operation("修改图书名为：" + bookname + "的数据失败"); //插入操作记录
//失败提示
MessageBox.Show("修改失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
SqlDbHelper dbHelper = new SqlDbHelper(); //实例化SqlDbHelper类
dbHelper.Operation("修改图书：" + bookname + "的数据失败"); //插入操作记录
//失败提示
MessageBox.Show("修改失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
m = -m;//转为正数
//查询是否有存在m条未借出的数据
string sql = "select count(*) from books where b_isbn='" + b_isbn + "' and b_lend=1";
con = dButil.SqlOpen(); //打开数据库
cmd = new SqlCommand(sql, con); //储存sql语句
int n = Convert.ToInt16(cmd.ExecuteScalar()); //赋值进行对比，执行sql语句，返回第一行第一列的值
con.Close(); //关闭数据库
if (n >= m)
{
//更新语句，先更新book表中的isbn再更新books表
sql = "update [book] set b_count=b_count+" + mtext_stocks.Text.Trim() + "-b_stocks,b_stocks='" + mtext_stocks.Text.Trim() + "',b_author='" + text_author.Text.Trim() + "',b_press='" + text_press.Text.Trim() + "',b_time='" + year + "',b_price='" + text_price.Text.Trim() + "',t_id=(select t_id from [type] where t_name='" + cmb_type.Text + "') where b_isbn='" + b_isbn + "'";
Console.WriteLine(sql);
con = dButil.SqlOpen(); //打开数据库
cmd = new SqlCommand(sql, con); //储存语句
n = cmd.ExecuteNonQuery(); //，执行语句返回影响行
con.Close(); //关闭数据库
if (n > 0) //判断是否执行成功
{
string sql_books = "set rowcount " + m + " delete books where b_isbn='" + b_isbn + "' and b_lend=1";
con = dButil.SqlOpen(); //打开数据库
cmd = new SqlCommand(sql_books, con); //储存语句
n = cmd.ExecuteNonQuery(); //执行
con.Close(); //关闭数据库
if (n > 0)
{
SqlDbHelper dbHelper = new SqlDbHelper(); //实例化SqlDbHelper类
dbHelper.Operation("成功修改图书：" + bookname + "的数据"); //插入操作记录
//成功提示
DialogResult result = MessageBox.Show("修改成功！点击确认按钮返回图书管理界面", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (DialogResult.OK == result)
{
//按确认键关闭该窗体
btn_ret_Click(sender, e);
}
}
else
{
SqlDbHelper dbHelper = new SqlDbHelper(); //实例化SqlDbHelper类
dbHelper.Operation("修改图书名为：" + bookname + "的数据失败"); //插入操作记录
//失败提示
MessageBox.Show("修改失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
SqlDbHelper dbHelper = new SqlDbHelper(); //实例化SqlDbHelper类
dbHelper.Operation("修改图书名为：" + bookname + "的数据失败"); //插入操作记录
//失败提示
MessageBox.Show("修改失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
SqlDbHelper dbHelper = new SqlDbHelper(); //实例化SqlDbHelper类
dbHelper.Operation("修改图书名为：" + bookname + "的数据失败"); //插入操作记录
//失败提示
MessageBox.Show("修改失败！可能存在用户正在借用该书", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
}
}
public static List <BaoYangPriceGuideList> SelectBaoYangProductInfo(BaoYangPriceSelectModel model)
{
var result = new List <BaoYangPriceGuideList>();
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Tuhu_Gungnir_BI")))
{
#region sql
var sql = @"SELECT VP.CP_Brand AS Brand ,
VP.PID ,
VP.DisplayName AS ProductName ,
VP.stockout AS StockStatus,
VP.OnSale AS OnSaleStatus,
VP.Category,
DPSD.totalstock ,
DPSD.SelfStock ,
DPSD.num_week ,
DPSD.num_month ,
DPSD.num_threemonth ,
DPSD.cost ,
DPSD.PurchasePrice ,
VP.cy_list_price AS Price ,
DPSD.taobao_tuhuid AS TBPID ,
DPSD.taobao_tuhuprice AS TBPrice ,
DPSD.taobao2_tuhuid AS TB2PID ,
DPSD.taobao2_tuhuprice AS TB2Price ,
DPSD.tianmao1_tuhuprice AS TM1Price ,
DPSD.tianmao1_tuhuid AS TM1PID ,
DPSD.tianmao2_tuhuprice AS TM2Price ,
DPSD.tianmao2_tuhuid AS TM2PID ,
DPSD.tianmao3_tuhuprice AS TM3Price ,
DPSD.tianmao3_tuhuid AS TM3PID ,
DPSD.tianmao4_tuhuid AS TM4PID ,
DPSD.tianmao4_tuhuprice AS TM4Price ,
DPSD.jingdong_tuhuprice AS JDPrice ,
DPSD.jingdong_tuhuid AS JDPID ,
DPSD.jingdongflagship_tuhuprice AS JDFlagShipPrice ,
DPSD.jingdongflagship_tuhuid AS JDFlagShipPID ,
DPSD.jingdongself_price AS JDSelfPrice ,
DPSD.jingdongself_id AS JDSelfPID ,
DPSD.teweilun_tianmaoprice AS TWLTMPrice ,
DPSD.teweilun_tianmaoid AS TWLTMPID,
DPSD.qccr_retailprice AS QccrlPrice ,
DPSD.qccr_retailid AS QccrlId ,
DPSD.qccr_wholesaleprice AS QccrpPrice ,
DPSD.qccr_wholesaleid AS QccrpId,
DPSD.yangche51_id AS YcwyId,
DPSD.yangche51_price AS YcwyPrice,
DPSD.carzone_id AS KzId,
DPSD.carzone_price AS KzPrice
FROM Tuhu_productcatalog.dbo.vw_Products AS VP WITH ( NOLOCK )
LEFT JOIN Tuhu_bi.dbo.dm_Product_SalespredictData
AS DPSD WITH ( NOLOCK ) ON VP.PID = DPSD.pid
WHERE ( ( @PID IS NULL
OR @PID = ''
)
OR VP.PID = @PID
)
AND VP.Category IN (
SELECT CategoryName
FROM
Tuhu_productcatalog..vw_ProductCategories (NOLOCK)
WHERE NodeNo LIKE N'%' + @FirstType + N'%'
AND ( ( @SecondType IS NULL
OR @SecondType = ''
)
OR NodeNo LIKE N'%' + @SecondType + N'%'
)
AND ( ( @ThirdType IS NULL
OR @ThirdType = ''
)
OR NodeNo LIKE N'%' + @ThirdType + N'%'
))
AND ( VP.DisplayName LIKE '%' + @ProductName
+ '%'
OR @ProductName IS NULL
)
AND ( VP.CP_Brand = @Brand
OR @Brand IS NULL
)
AND ( @StockStatus = 0
OR @StockStatus = 1
AND VP.stockout = 0
OR @StockStatus = 2
AND VP.stockout = 1
)
AND ( @OnSaleStatus = 0
OR @OnSaleStatus = 1
AND VP.OnSale = 1
OR @OnSaleStatus = 2
AND VP.OnSale = 0
)";
#endregion
SqlParameter[] parameters =
{
new SqlParameter("@PID", string.IsNullOrWhiteSpace(model.PID)?null:model.PID),
new SqlParameter("@ProductName", string.IsNullOrWhiteSpace(model.ProductName)?null:model.ProductName),
new SqlParameter("@Brand", string.IsNullOrWhiteSpace(model.Brand)?null:model.Brand),
new SqlParameter("@StockStatus", model.StockStatus),
new SqlParameter("@OnSaleStatus", model.OnSaleStatus),
new SqlParameter("@FirstType", model.FirstType),
new SqlParameter("@SecondType", model.SecondType),
new SqlParameter("@ThirdType", model.ThirdType)
};
var dt = dbHelper.ExecuteDataTable(sql, CommandType.Text, parameters);
if (dt != null && dt.Rows.Count > 0)
{
result = dt.ConvertTo <BaoYangPriceGuideList>().ToList();
}
return(result);
}
}
public static bool UpdateBeautyYearCardConfig(BeautyYearCardModel model)
{
#region sql
const string sql = @"Update [Tuhu_groupon].[dbo].[ShopBeautyYearCardConfig] WITH(ROWLOCK)
SET
[CardName]=IsNULL(@CardName,CardName)
,[CardPrice]=IsNULL(@CardPrice,CardPrice)
,[PID]=IsNULL(@PID,PID)
,[CardImageUrl]=IsNULL(@CardImageUrl,CardImageUrl)
,[AdaptVehicle]=IsNULL(@AdaptVehicle,AdaptVehicle)
,[Remark]=IsNULL(@Remark,Remark)
,[Rule]=IsNULL(@Rule,[Rule])
,[SalesBeginTime]=IsNULL(@SalesBeginTime,SalesBeginTime)
,[SalesEndTime]=IsNULL(@SalesEndTime,SalesEndTime)
,[CardValidDays]=IsNULL(@CardValidDays,CardValidDays)
,[CardType]=IsNULL(@CardType,CardType)
WHERE PKID=@PKID";
const string sqlPro = @"UPDATE [Tuhu_groupon].[dbo].[ShopBeautyYearCardProductsConfig] WITH(ROWLOCK)
SET
[ProductId]=IsNULL(@ProductId,ProductId)
,[ProductName]=IsNULL(@ProductName,ProductName)
,[ProductNumber]=IsNULL(@ProductNumber,ProductNumber)
,[ProductPrice]=IsNULL(@ProductPrice,ProductPrice)
,[ProductDescription]=IsNULL(@ProductDescription,ProductDescription)
,[Commission]=IsNULL(@Commission,Commission)
,[UseCycle]=IsNULL(@UseCycle,UseCycle)
,[CycleType]=IsNULL(@CycleType,CycleType)
,[UpdateTime]=GETDATE()
WHERE PKID=@PKID AND CardId=@CardId";
const string regionDel = @"Delete from [Tuhu_groupon].[dbo].[ShopBeautyYearCardRegionConfig] where CardId=@CardId";
const string sqlRegion = @"Insert Into [Tuhu_groupon].[dbo].[ShopBeautyYearCardRegionConfig]
([CardId]
,[ProvinceId]
,[CityId]
,[IsAllCity]
,[CreateTime]
,[UpdateTime])
values(
@CardId,
@ProvinceId,
@CityId,
@IsAllCity,
GetDate(),
GetDate()
)
";
#endregion
using (var db = new SqlDbHelper(strConn))
{
db.BeginTransaction();
try
{
#region CardConfig
var cardId = Convert.ToInt32(db.ExecuteNonQuery(sql,
CommandType.Text,
new SqlParameter[] {
new SqlParameter("@PKID", model.PKID),
new SqlParameter("@CardName", model.CardName),
new SqlParameter("@CardPrice", model.CardPrice),
new SqlParameter("@PID", model.PID),
new SqlParameter("@CardImageUrl", model.CardImageUrl),
new SqlParameter("@AdaptVehicle", model.AdaptVehicle),
new SqlParameter("@Remark", model.Remark),
new SqlParameter("@Rule", model.Rule),
new SqlParameter("@SalesBeginTime", model.SalesBeginTime),
new SqlParameter("@SalesEndTime", model.SalesEndTime),
new SqlParameter("@CardType", model.CardType),
new SqlParameter("@CardValidDays", model.CardValidDays)
}));
#endregion
if (cardId > 0 && model.BeautyYearCardProducts.Any())
{
#region proConfig
var proUpdate = true;
foreach (var f in model.BeautyYearCardProducts)
{
var proResult = Convert.ToInt32(db.ExecuteNonQuery(sqlPro,
CommandType.Text,
new SqlParameter[] {
new SqlParameter("@PKID", f.PKID),
new SqlParameter("@CardId", model.PKID),
new SqlParameter("@ProductId", f.ProductId),
new SqlParameter("@ProductName", f.ProductName),
new SqlParameter("@ProductDescription", f.ProductDescription),
new SqlParameter("@ProductNumber", f.ProductNumber),
new SqlParameter("@ProductPrice", f.ProductPrice),
new SqlParameter("@Commission", f.Commission),
new SqlParameter("@UseCycle", f.UseCycle),
new SqlParameter("@CycleType", f.CycleType)
}));
if (proResult <= 0)
{
db.Rollback();
proUpdate = false;
break;
}
}
#endregion
if (proUpdate && model.BeautyYearCardRegions.Any())
{
#region regConfig
var regInsert = true;
var delResult = Convert.ToInt32(db.ExecuteNonQuery(regionDel,
CommandType.Text,
new SqlParameter[] {
new SqlParameter("@CardId", model.PKID)
})
);
if (delResult > 0)
{
foreach (var e in model.BeautyYearCardRegions)
{
var regResult = Convert.ToInt32(db.ExecuteNonQuery(sqlRegion,
CommandType.Text,
new SqlParameter[] { new SqlParameter("@CardId", model.PKID),
new SqlParameter("@ProvinceId", e.ProvinceId),
new SqlParameter("@CityId", (e.IsAllCity? null: e.CityId)),
new SqlParameter("@IsAllCity", e.IsAllCity) }));
if (regResult <= 0)
{
db.Rollback();
regInsert = false;
break;
}
}
if (regInsert)
{
db.Commit();
return(true);
}
}
#endregion
}
}
db.Rollback();
return(false);
}
catch (Exception ex)
{
db.Rollback();
throw ex;
}
}
}
public void VerifyExecuteScalar()
{
var localHelper = new SqlDbHelper(_helper.ConnectionString);
localHelper.ConnectionCreated += (x, y) => Console.WriteLine("Connection Created");
//var numberOfStates = localHelper.ExecuteScalar<int>("select count(*) from state");
//Assert.AreEqual(71, numberOfStates);
using (var cmd = new SqlCommand("select count(*) from state"))
{
localHelper.ExecuteScalar<int>(cmd);
}
}
/// <summary>
/// 更新广告信息
/// </summary>
/// <param name="AdvertiseModel"></param>
/// <returns></returns>
public static int UpdateAdvertise(AdvertiseModel AdvertiseModel, IEnumerable <AdProductModel> products, out int result)
{
var conn = ConfigurationManager.ConnectionStrings["Gungnir"].ConnectionString;
if (SecurityHelp.IsBase64Formatted(conn))
{
conn = SecurityHelp.DecryptAES(conn);
}
using (var dbhelper = new SqlDbHelper(conn))
{
dbhelper.BeginTransaction();
var cmd = new SqlCommand("Gungnir..[Advertise_UpdateAdvertise]");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@AdColumnID", AdvertiseModel.AdColumnID);
cmd.Parameters.AddWithValue("@AdName", AdvertiseModel.AdName);
cmd.Parameters.AddWithValue("@PKID", AdvertiseModel.PKID);
cmd.Parameters.AddWithValue("@BeginDateTime", AdvertiseModel.BeginDateTime);
cmd.Parameters.AddWithValue("@EndDateTime", AdvertiseModel.EndDateTime);
cmd.Parameters.AddWithValue("@Position", AdvertiseModel.Position);
cmd.Parameters.AddWithValue("@Image", AdvertiseModel.Image);
cmd.Parameters.AddWithValue("@State", AdvertiseModel.State);
cmd.Parameters.AddWithValue("@Url", AdvertiseModel.Url);
cmd.Parameters.AddWithValue("@bgColor", AdvertiseModel.bgColor);
cmd.Parameters.Add(new SqlParameter
{
ParameterName = "@Result",
DbType = DbType.Int32,
Direction = ParameterDirection.Output
});
dbhelper.ExecuteNonQuery(cmd);
result = Convert.ToInt32(cmd.Parameters["@Result"].Value);
if (result > 0)
{
if (products != null && products.Count() > 0)
{
if (DeleteProducts(AdvertiseModel.AdColumnID, AdvertiseModel.PKID.ToString()) > 0)
{
foreach (var p in products)
{
p.AdvertiseID = AdvertiseModel.PKID;
if (InsertProducts(dbhelper, p) <= 0)
{
dbhelper.Rollback();
return(-2);
}
}
}
else
{
dbhelper.Rollback();
return(-3);
}
}
else if (AdvertiseModel.AdColumnID.Contains("F_11"))
{
dbhelper.Rollback();
result = -98;
}
}
else
{
dbhelper.Rollback();
return(-1);
}
dbhelper.Commit();
return(result);
}
}
public static DataTable SelectQiangGouToCache(Guid activityID)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir")))
{
return(dbHelper.ExecuteDataTable(@"SELECT FS.ActivityID,
FS.ActivityName,
FS.StartDateTime,
FS.EndDateTime,
FS.CreateDateTime,
FS.UpdateDateTime,
FS.Area,
FS.BannerUrlAndroid,
FS.BannerUrlIOS,
FS.AppVlueAndroid,
FS.AppVlueIOS,
FS.BackgoundColor,
FS.TomorrowText,
FS.IsBannerIOS,
FS.IsBannerAndroid,
FS.ShowType,
FS.ShippType,
FS.IsTomorrowTextActive,
FS.CountDown,
FS.Status,
FS.WebBanner,
FS.WebCornerMark,
FS.WebBackground,
FS.IsNoActiveTime,
FS.EndImage,
FS.IsEndImage,
FS.WebOtherPart,
FS.ActiveType,
FS.PCodeIDS,
FS.ShoppingCart,
FS.IsNewUserFirstOrder,
FS.H5Url,
FS.PlaceQuantity,
FSP.PKID,
FSP.PID,
FSP.Position,
FSP.Price,
FSP.TotalQuantity,
FSP.MaxQuantity,
FSP.SaleOutQuantity,
ISNULL(FSP.ProductName, VP.DisplayName) AS ProductName,
ISNULL(VP.Image_filename, ISNULL(VP.Image_filename_2, ISNULL(VP.Image_filename_3, ISNULL(Image_filename_4, Image_filename_5)))) AS ProductImg,
VP.TireSize,
FSP.InstallAndPay,
FSP.Level,
FSP.ImgUrl,
FSP.IsUsePCode,
FSP.Channel,
FSP.FalseOriginalPrice,
FSP.IsJoinPlace,
FSP.IsShow
FROM Activity..tbl_FlashSale AS FS WITH(NOLOCK)
JOIN Activity..tbl_FlashSaleProducts AS FSP WITH (NOLOCK)
ON FS.ActivityID = FSP.ActivityID AND FS.ActivityID = @ActivityID
JOIN Tuhu_productcatalog..vw_Products AS VP WITH (NOLOCK) ON FSP.PID = VP.PID", CommandType.Text, new SqlParameter("@ActivityID", activityID)));
}
}
public static int InsertPromotionCode(SqlDbHelper dbhelper, PromotionCode PC)
{
return(DalPromotionCode.InsertPromotionCode(dbhelper, PC));
}
/// <summary>
/// <c>CacheableDacHelper</c>클래스의 새 인스턴스를 초기화합니다.
/// </summary>
/// <param name="dbHelper">Sql처리를 수행하는 <c>SqlDbHelper</c>입니다.</param>
/// <param name="connectionStringName">연결문자열 이름입니다.</param>
/// <param name="cacheManager">캐시 관리자 입니다.</param>
/// <param name="slidingExpiration">캐시 폐기 시간 입니다.</param>
public CacheableDacHelper(SqlDbHelper dbHelper, string connectionStringName, CacheManager cacheManager, TimeSpan slidingExpiration)
: base(dbHelper, connectionStringName)
{
this.cacheManager = cacheManager;
this.slidingExpiration = slidingExpiration;
}
/// <summary>
/// <c>CacheableDacHelper</c>클래스의 새 인스턴스를 초기화합니다.
/// </summary>
/// <param name="dbHelper">Sql처리를 수행하는 <c>SqlDbHelper</c>입니다.</param>
/// <param name="cacheManager">캐시 관리자 입니다.</param>
/// <param name="slidingExpiration">캐시 폐기 시간 입니다.</param>
public CacheableDacHelper(SqlDbHelper dbHelper, CacheManager cacheManager, TimeSpan slidingExpiration)
: base(dbHelper)
{
this.cacheManager = cacheManager;
this.slidingExpiration = slidingExpiration;
}
public void VerifyChangeConnectionChangesInternalConnectionString()
{
var helper = new SqlDbHelper("godzilla");
Assert.AreEqual("godzilla", helper.ConnectionString);
helper.ChangeConnection("mothra");
Assert.AreEqual("mothra", helper.ConnectionString);
}
public static bool FetchPidStatus(SqlDbHelper dbHelper, string pid)
{
var obj = dbHelper.ExecuteScalar(TireSql.StockoutStatusWhite.fetch_pidStatus, CommandType.Text, new SqlParameter("@PID", pid));
return(obj == null || obj == DBNull.Value ? false : true);
}
public static Dictionary <string, IEnumerable <QiangGouProductModel> > SelectDiffActivityProducts(Guid?aid, int atype, IEnumerable <QiangGouProductModel> products, SqlDbHelper dbHelper)
{
Dictionary <string, IEnumerable <QiangGouProductModel> > dic = new Dictionary <string, IEnumerable <QiangGouProductModel> >();
//return dic;
var list = dbHelper.ExecuteDataTable(@"SELECT FS.ActivityID ,FS.ActivityName,
ISNULL(FSP.ProductName, VP.DisplayName) AS ProductName ,
FSP.Price ,
FSP.FalseOriginalPrice ,
FSP.InstallAndPay ,
FSP.IsUsePCode,
FSP.PID
FROM Activity.dbo.tbl_FlashSale AS FS WITH ( NOLOCK )
JOIN Activity.dbo.tbl_FlashSaleProducts AS FSP WITH ( NOLOCK ) ON FSP.ActivityID = FS.ActivityID
JOIN Tuhu_productcatalog.dbo.vw_Products AS VP WITH ( NOLOCK ) ON VP.PID = FSP.PID COLLATE Chinese_PRC_CI_AS
WHERE FS.EndDateTime > GETDATE()
AND FS.ActiveType =@ActivityType and FS.ActiveType !=4
AND FSP.PID COLLATE Chinese_PRC_CI_AS IN ( SELECT *
FROM Gungnir..Split(@pids, ';') );", CommandType.Text, new SqlParameter[] {
new SqlParameter("@pids", string.Join(";", products.Select(_ => _.PID))),
new SqlParameter("@ActivityType", atype)
}).ConvertTo <QiangGouProductModel>();
if (aid != null)
{
list = list.Where(_ => _.ActivityID != aid);
}
if (list.Any())
{
foreach (var item in products)
{
var diff = list.Where(_ => _.PID == item.PID &&
(
_.ProductName != item.ProductName ||
_.Price != item.Price ||
_.FalseOriginalPrice != item.FalseOriginalPrice ||
(_.InstallAndPay != item.InstallAndPay && !(string.IsNullOrWhiteSpace(_.InstallAndPay) && string.IsNullOrWhiteSpace(item.InstallAndPay))) ||
_.IsUsePCode != item.IsUsePCode));
if (diff.Any())
{
dic.Add(item.PID, diff);
}
}
}
return(dic);
}
public bool IsExist()
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir_AlwaysOnRead")))
return((int)dbHelper.ExecuteScalar("SELECT COUNT(*) FROM Configuration.dbo.SE_ShareMakeMoneyConfig(NOLOCK)") > 0);
}
public static IEnumerable <QiangGouProductModel> SelectSelectedDiffActivityProducts(Guid?aid, int atype, IEnumerable <QiangGouProductModel> products, SqlDbHelper dbHelper)
{
var list = dbHelper.ExecuteDataTable(@"SELECT FS.ActivityID ,
ISNULL(FSP.ProductName, VP.DisplayName) AS ProductName ,
FSP.Price ,
FSP.FalseOriginalPrice ,
FSP.InstallAndPay ,
FSP.IsUsePCode,
FSP.PID
FROM Activity.dbo.tbl_FlashSale AS FS WITH ( NOLOCK )
JOIN Activity.dbo.tbl_FlashSaleProducts AS FSP WITH ( NOLOCK ) ON FSP.ActivityID = FS.ActivityID
JOIN Tuhu_productcatalog.dbo.vw_Products AS VP WITH ( NOLOCK ) ON VP.PID = FSP.PID COLLATE Chinese_PRC_CI_AS
WHERE FS.EndDateTime > GETDATE()
AND FS.ActiveType =@ActivityType
AND FS.ActivityID =@ActivityID
AND FSP.PID COLLATE Chinese_PRC_CI_AS IN ( SELECT *
FROM Gungnir..Split(@pids, ';') );", CommandType.Text, new SqlParameter[] {
new SqlParameter("@pids", string.Join(";", products.Select(_ => _.PID))),
new SqlParameter("@ActivityType", atype),
new SqlParameter("@ActivityID", aid)
}).ConvertTo <QiangGouProductModel>();
return(list);
}
public static bool InsertVipCardDetailsModel(List <VipCardDetailModel> details, int vipCardId, SqlDbHelper dbHelper)
{
var flag = true;
const string sql = @"
INSERT INTO Configuration..VipCardSaleConfigDetail
(
ClientId,
VipCardId,
ClientName,
BatchId,
CardName,
CardValue,
SalePrice,
UseRange,
StartDate,
EndDate,
Stock,
SaleOutQuantity,
Status
)
VALUES
(
@ClientId,
@VipCardId,
@ClientName,
@BatchId,
@CardName,
@CardValue,
@SalePrice,
@UseRange,
@StartDate,
@EndDate,
@Stock,
0,
1
) " ;
foreach (var model in details)
{
var singleResult = dbHelper.ExecuteNonQuery(sql, CommandType.Text, new DbParameter[]
{
new SqlParameter("@ClientId", model.ClientId),
new SqlParameter("@VipCardId", vipCardId),
new SqlParameter("@ClientName", model.ClientName),
new SqlParameter("@BatchId", model.BatchId),
new SqlParameter("@CardName", model.CardName),
new SqlParameter("@CardValue", model.CardValue),
new SqlParameter("@SalePrice", model.SalePrice),
new SqlParameter("@UseRange", model.UseRange),
new SqlParameter("@StartDate", model.StartDate),
new SqlParameter("@EndDate", model.EndDate),
new SqlParameter("@Stock", model.Stock),
}) > 0;
flag = flag && singleResult;
}
return(flag);
}
public static Tuple <int, Guid> CreateOrUpdateQianggou(SqlDbHelper dbHelper, QiangGouModel model)
{
Tuple <int, Guid> resultWithAct;
var result = -99;
//创建活动
if (model.ActivityID == null)
{
var cmd = new SqlCommand("Activity..Activity_QiangGou_CreateActivity");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ActivityName", model.ActivityName);
cmd.Parameters.AddWithValue("@StartDateTime", model.StartDateTime);
cmd.Parameters.AddWithValue("@EndDateTime", model.EndDateTime);
cmd.Parameters.AddWithValue("@ActiveType", model.ActiveType);
cmd.Parameters.AddWithValue("@PlaceQuantity", model.PlaceQuantity);
cmd.Parameters.AddWithValue("@NeedExam", model.NeedExam);
cmd.Parameters.AddWithValue("@IsDefault", model.IsDefault);
cmd.Parameters.AddWithValue("@IsNewUserFirstOrder", model.IsNewUserFirstOrder);
cmd.Parameters.Add(new SqlParameter()
{
DbType = DbType.Int32,
Direction = ParameterDirection.Output,
ParameterName = "@Result",
});
cmd.Parameters.Add(new SqlParameter()
{
DbType = DbType.Guid,
Direction = ParameterDirection.Output,
ParameterName = "@ActivityID",
});
dbHelper.ExecuteNonQuery(cmd);
result = Convert.ToInt32(cmd.Parameters["@Result"].Value);
model.ActivityID = Guid.Parse(cmd.Parameters["@ActivityID"].Value.ToString());
if (model.ActivityID != null && result >= 0)
{
result = CraateOrUpdateQiangGouProducts(dbHelper, model, false);
}
resultWithAct = new Tuple <int, Guid>(result, model.ActivityID.Value);
}
else//修改活动
{
var cmd = new SqlCommand("Activity..Activity_QiangGou_UpdateActivity");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ActivityName", model.ActivityName);
cmd.Parameters.AddWithValue("@ActivityID", model.ActivityID);
cmd.Parameters.AddWithValue("@StartDateTime", model.StartDateTime);
cmd.Parameters.AddWithValue("@EndDateTime", model.EndDateTime);
cmd.Parameters.AddWithValue("@ActiveType", model.ActiveType);
cmd.Parameters.AddWithValue("@PlaceQuantity", model.PlaceQuantity);
cmd.Parameters.AddWithValue("@NeedExam", model.NeedExam);
cmd.Parameters.AddWithValue("@IsDefault", model.IsDefault);
cmd.Parameters.AddWithValue("@IsNewUserFirstOrder", model.IsNewUserFirstOrder);
cmd.Parameters.AddWithValue("@PIDS", string.Join(",", model.Products.Select(p => p.PID)));
cmd.Parameters.Add(new SqlParameter()
{
DbType = DbType.Int32,
Direction = ParameterDirection.Output,
ParameterName = "@Result",
});
dbHelper.ExecuteNonQuery(cmd);
result = Convert.ToInt32(cmd.Parameters["@Result"].Value);
if (result > 0)
{
result = CraateOrUpdateQiangGouProducts(dbHelper, model, true);
}
resultWithAct = new Tuple <int, Guid>(result, model.ActivityID.Value);
}
return(resultWithAct);
}
public static int CreateBeautyYearCardConfig(BeautyYearCardModel model)
{
#region sql
const string sql = @"Insert Into [Tuhu_groupon].[dbo].[ShopBeautyYearCardConfig]
([CardName]
,[CardPrice]
,[PID]
,[CardImageUrl]
,[AdaptVehicle]
,[Remark]
,[Rule]
,[SalesBeginTime]
,[SalesEndTime]
,[CardValidDays]
,IsDeleted
,CreateTime
,UpdateTime
,CardType)
values(
@CardName,
@CardPrice,
@PID,
@CardImageUrl,
@AdaptVehicle,
@Remark,
@Rule,
@SalesBeginTime,
@SalesEndTime,
@CardValidDays,
0,
GetDate(),
GetDate(),
@CardType
);Select @@identity;";
const string sqlPro = @"Insert into [Tuhu_groupon].[dbo].[ShopBeautyYearCardProductsConfig]
([CardId]
,[ProductId]
,[ProductName]
,[ProductNumber]
,[ProductPrice]
,[ProductDescription]
,[Commission]
,[UseCycle]
,[CycleType]
,[IsDeleted]
,[CreateTime]
,[UpdateTime])
values(
@CardId,
@ProductId,
@ProductName,
@ProductNumber,
@ProductPrice,
@ProductDescription,
@Commission,
@UseCycle,
@CycleType,
0,
GetDate(),
GetDate()
) ";
const string sqlRegion = @"Insert Into [Tuhu_groupon].[dbo].[ShopBeautyYearCardRegionConfig]
([CardId]
,[ProvinceId]
,[CityId]
,[IsAllCity]
,[CreateTime]
,[UpdateTime])
values(
@CardId,
@ProvinceId,
@CityId,
@IsAllCity,
GetDate(),
GetDate()
)";
#endregion
using (var db = new SqlDbHelper(strConn))
{
db.BeginTransaction();
try
{
#region CardConfig
var cardId = Convert.ToInt32(db.ExecuteScalar(sql,
CommandType.Text,
new SqlParameter[] { new SqlParameter("@CardName", model.CardName),
new SqlParameter("@CardPrice", model.CardPrice),
new SqlParameter("@PID", model.PID),
new SqlParameter("@CardImageUrl", model.CardImageUrl),
new SqlParameter("@AdaptVehicle", model.AdaptVehicle),
new SqlParameter("@Remark", model.Remark),
new SqlParameter("@Rule", model.Rule),
new SqlParameter("@SalesBeginTime", model.SalesBeginTime),
new SqlParameter("@SalesEndTime", model.SalesEndTime),
new SqlParameter("@CardType", model.CardType),
new SqlParameter("@CardValidDays", model.CardValidDays) }
));
#endregion
if (cardId > 0 && model.BeautyYearCardProducts.Any())
{
#region proConfig
var proInsert = true;
foreach (var f in model.BeautyYearCardProducts)
{
var proResult = Convert.ToInt32(db.ExecuteNonQuery(sqlPro,
CommandType.Text,
new SqlParameter[] { new SqlParameter("@CardId", cardId),
new SqlParameter("@ProductId", f.ProductId),
new SqlParameter("@ProductName", f.ProductName),
new SqlParameter("@ProductDescription", f.ProductDescription),
new SqlParameter("@ProductNumber", f.ProductNumber),
new SqlParameter("@ProductPrice", f.ProductPrice),
new SqlParameter("@Commission", f.Commission),
new SqlParameter("@UseCycle", f.UseCycle),
new SqlParameter("@CycleType", f.CycleType) }));
if (proResult <= 0)
{
db.Rollback();
proInsert = false;
}
}
#endregion
if (proInsert && model.BeautyYearCardRegions.Any())
{
#region regConfig
var regInsert = true;
foreach (var e in model.BeautyYearCardRegions)
{
var regResult = Convert.ToInt32(db.ExecuteNonQuery(sqlRegion,
CommandType.Text,
new SqlParameter[] { new SqlParameter("@CardId", cardId),
new SqlParameter("@ProvinceId", e.ProvinceId),
new SqlParameter("@CityId", (e.IsAllCity? null:e.CityId)),
new SqlParameter("@IsAllCity", e.IsAllCity) }));
if (regResult <= 0)
{
db.Rollback();
regInsert = false;
}
}
if (regInsert)
{
db.Commit();
return(cardId);
}
#endregion
}
}
else
{
db.Rollback();
}
return(0);
}
catch (Exception ex)
{
db.Rollback();
throw ex;
}
}
}
public static int CraateOrUpdateQiangGouProducts(SqlDbHelper dbHelper, QiangGouModel model, bool isUpdate)
{
var result = -99;
string sql;
if (model.NeedExam)
{
sql = @" INSERT INTO Activity..tbl_FlashSaleProducts_Temp
(
ActivityID,
PID,
Position,
Price,
Label,
TotalQuantity,
MaxQuantity,
SaleOutQuantity,
ProductName,
InstallAndPay,
IsUsePCode,
Channel,
FalseOriginalPrice,
IsJoinPlace,
IsShow,
InstallService)
VALUES(
@ActivityID,
@PID,
@Position,
@Price,
@Label,
@TotalQuantity,
@MaxQuantity,
@SaleOutQuantity,
@ProductName,
@InstallAndPay,
@IsUsePCode,
@Channel,
@FalseOriginalPrice,
@IsJoinPlace,
@IsShow,
@InstallService); ";
}
else
{
sql = @" MERGE INTO Activity..tbl_FlashSaleProducts AS T
USING
( SELECT @Pid AS pid ,
@ActivityID AS activityID
) AS S
ON T.PID = S.pid
AND S.activityID = T.ActivityID
WHEN MATCHED THEN
UPDATE SET Price = @Price ,
Position = @Position ,
Label = @Label ,
TotalQuantity = @TotalQuantity ,
MaxQuantity = @MaxQuantity ,
ProductName = @ProductName ,
InstallAndPay = @InstallAndPay ,
IsUsePCode = @IsUsePCode ,
Channel = @Channel ,
FalseOriginalPrice = @FalseOriginalPrice ,
IsJoinPlace = @IsJoinPlace ,
IsShow = @IsShow ,
InstallService = @InstallService,
SaleOutQuantity = @SaleOutQuantity,
LastUpdateDateTime=GetDate()
WHEN NOT MATCHED THEN
INSERT ( ActivityID ,
PID ,
Position ,
Price ,
Label ,
TotalQuantity ,
MaxQuantity ,
ProductName ,
InstallAndPay ,
IsUsePCode ,
Channel ,
FalseOriginalPrice ,
IsJoinPlace ,
IsShow ,
InstallService,
SaleOutQuantity
)
VALUES ( @ActivityID ,
@PID ,
@Position ,
@Price ,
@Label ,
@TotalQuantity ,
@MaxQuantity ,
@ProductName ,
@InstallAndPay ,
@IsUsePCode ,
@Channel ,
@FalseOriginalPrice ,
@IsJoinPlace ,
@IsShow ,
@InstallService,
@SaleOutQuantity
);";
}
foreach (var item in model.Products)
{
using (var cmd = new SqlCommand(sql))
{
cmd.Parameters.AddWithValue("@ActivityID", model.ActivityID);
cmd.Parameters.AddWithValue("@PID", item.PID);
cmd.Parameters.AddWithValue("@Position", item.Position);
cmd.Parameters.AddWithValue("@Price", item.Price);
cmd.Parameters.AddWithValue("@Label", string.IsNullOrWhiteSpace(item.Label) ? null : item.Label);
cmd.Parameters.AddWithValue("@TotalQuantity", item.TotalQuantity);
cmd.Parameters.AddWithValue("@MaxQuantity", item.MaxQuantity);
cmd.Parameters.AddWithValue("@ProductName", item.ProductName);
cmd.Parameters.AddWithValue("@InstallAndPay", string.IsNullOrWhiteSpace(item.InstallAndPay) ? null : item.InstallAndPay);
cmd.Parameters.AddWithValue("@IsUsePCode", item.IsUsePCode);
cmd.Parameters.AddWithValue("@Channel", item.Channel);
cmd.Parameters.AddWithValue("@FalseOriginalPrice", item.FalseOriginalPrice);
cmd.Parameters.AddWithValue("@IsJoinPlace", item.IsJoinPlace);
cmd.Parameters.AddWithValue("@IsShow", item.IsShow);
cmd.Parameters.AddWithValue("@InstallService", item.InstallService);
cmd.Parameters.AddWithValue("@SaleOutQuantity", item.SaleOutQuantity);
result = dbHelper.ExecuteNonQuery(cmd);
if (result <= 0)
{
dbHelper.Rollback();
}
}
}
return(result);
}
public static bool UpdateOrInsertOrderTypeTousuConfig(OrderTypeTousuConfig model)
{
bool result = false;
IEnumerable <OrderTypeTousuConfig> ordertypeTousuConfigList;
if (model != null)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Shouhou")))
{
ordertypeTousuConfigList = dbHelper.ExecuteDataTable(@"SELECT [PKID]
,[OrderType]
,[TopLevelTousu]
,[SecondLevelTousu]
,[ThirdLevelTousu]
,[FourthLevelTousu]
,[LastLevelTousu]
,[IsNeedPhoto]
,[CautionText]
,[GroupName]
FROM [dbo].[OrderTypeTousuConfig] with(nolock)
where (OrderType=@OrderType)
and (LastLevelTousu=@LastLevelTousu)
order by PKID desc
;", CommandType.Text,
new SqlParameter[] {
new SqlParameter("@OrderType", model.OrderType),
new SqlParameter("@LastLevelTousu", model.LastLevelTousu)
}).ConvertTo <OrderTypeTousuConfig>();
}
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Shouhou")))
{
if (ordertypeTousuConfigList != null && ordertypeTousuConfigList.Any())
{
using (var cmd =
new SqlCommand(
@"UPDATE [OrderTypeTousuConfig] WITH(ROWLOCK) SET IsChecked=@IsChecked,IsNeedPhoto=@IsNeedPhoto,CautionText=@CautionText,GroupName=@GroupName,LastUpdateDateTime=@LastUpdateDateTime WHERE (OrderType=@OrderType)
and (LastLevelTousu=@LastLevelTousu)")
)
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@IsChecked", model.IsChecked);
cmd.Parameters.AddWithValue("@IsNeedPhoto", model.IsNeedPhoto);
cmd.Parameters.AddWithValue("@CautionText", model.CautionText);
cmd.Parameters.AddWithValue("@GroupName", model.GroupName ?? string.Empty);
cmd.Parameters.AddWithValue("@OrderType", model.OrderType);
cmd.Parameters.AddWithValue("@LastLevelTousu", model.LastLevelTousu);
cmd.Parameters.AddWithValue("@LastUpdateDateTime", DateTime.Now);
result = dbHelper.ExecuteNonQuery(cmd) > 0 ? true : false;
}
}
else
{
using (var cmd =
new SqlCommand(
@"INSERT INTO [dbo].[OrderTypeTousuConfig] ([OrderType],[TopLevelTousu],[SecondLevelTousu],[ThirdLevelTousu],[FourthLevelTousu],[LastLevelTousu],[IsNeedPhoto],[IsChecked],[CautionText],[GroupName],[CreateDateTime],[LastUpdateDateTime])
VALUES(@OrderType,@TopLevelTousu,@SecondLevelTousu,@ThirdLevelTousu,@FourthLevelTousu,@LastLevelTousu,@IsNeedPhoto,@IsChecked,@CautionText,@GroupName,@CreateDateTime,@LastUpdateDateTime)")
)
{
var datetimeNow = DateTime.Now;
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@OrderType", model.OrderType);
cmd.Parameters.AddWithValue("@TopLevelTousu", model.TopLevelTousu);
cmd.Parameters.AddWithValue("@SecondLevelTousu", model.SecondLevelTousu);
cmd.Parameters.AddWithValue("@ThirdLevelTousu", model.ThirdLevelTousu);
cmd.Parameters.AddWithValue("@FourthLevelTousu", model.FourthLevelTousu);
cmd.Parameters.AddWithValue("@LastLevelTousu", model.LastLevelTousu);
cmd.Parameters.AddWithValue("@IsNeedPhoto", model.IsNeedPhoto);
cmd.Parameters.AddWithValue("@IsChecked", model.IsChecked);
cmd.Parameters.AddWithValue("@CautionText", model.CautionText);
cmd.Parameters.AddWithValue("@GroupName", model.GroupName ?? string.Empty);
cmd.Parameters.AddWithValue("@CreateDateTime", datetimeNow);
cmd.Parameters.AddWithValue("@LastUpdateDateTime", datetimeNow);
result = dbHelper.ExecuteNonQuery(cmd) > 0 ? true : false;
}
}
}
}
return(result);
}
public static int DeleteQZTJByPIDAndPostion(SqlDbHelper dbHelper, string PID, int Postion)
{
return(dbHelper.ExecuteNonQuery(@"DELETE Tuhu_productcatalog.dbo.tbl_TireEnforceRecommend
WHERE PID = @PID AND Position=@Postion" , CommandType.Text, new SqlParameter[] { new SqlParameter("@PID", PID), new SqlParameter("@Postion", Postion) }));
}
/// <summary>
/// 审核保养品价格修改申请
/// </summary>
/// <param name="isAccess"></param>
/// <param name="auther"></param>
/// <param name="pid"></param>
/// <param name="cost"></param>
/// <param name="PurchasePrice"></param>
/// <param name="totalstock"></param>
/// <param name="num_week"></param>
/// <param name="num_month"></param>
/// <param name="guidePrice"></param>
/// <param name="nowPrice"></param>
/// <param name="maoliLv"></param>
/// <param name="chaochu"></param>
/// <param name="jdself"></param>
/// <param name="maolie"></param>
/// <returns></returns>
public static int GotoAudit(bool isAccess, string auther, string pid, decimal?cost, decimal?PurchasePrice, int?totalstock, int?num_week, int?num_month, decimal?guidePrice, decimal nowPrice, string maoliLv, string chaochu, decimal?jdself, decimal?maolie)
{
double?maoliLv_1;
if (maoliLv == null || !maoliLv.Contains("%"))
{
maoliLv_1 = null;
}
else
{
maoliLv_1 = Convert.ToDouble(maoliLv.Replace("%", string.Empty)) / 100;
}
double?chaochu_1;
if (chaochu == null || !chaochu.Contains("%"))
{
chaochu_1 = null;
}
else
{
chaochu_1 = Convert.ToDouble(chaochu.Replace("%", string.Empty)) / 100;
}
int temp = isAccess ? 1 : 2;
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir")))
{
return(dbHelper.ExecuteNonQuery(@"UPDATE Configuration.dbo.tbl_BaoYangPriceUpdateAudit
SET AuditPerson = @AuditPerson ,
AuditDateTime = GETDATE() ,
Cost = @cost ,
PurchasePrice = @PurchasePrice ,
TotalStock = @totalstock ,
Num_Week = @num_week ,
Num_Month = @num_month ,
GuidePrice = @guidePrice ,
NowPrice = @nowPrice ,
MaoLiLv = @maoliLv ,
OverFlow = @overflow ,
JdSelf = @jdself ,
MaoLiE = @maolie ,
AuditStatus = @AuditStatus
WHERE AuditStatus = 0
AND PID = @PID;", CommandType.Text,
new SqlParameter[] {
new SqlParameter("@AuditPerson", auther),
new SqlParameter("@AuditStatus", temp),
new SqlParameter("@PID", pid),
new SqlParameter("@cost", cost),
new SqlParameter("@PurchasePrice", PurchasePrice),
new SqlParameter("@totalstock", totalstock),
new SqlParameter("@num_week", num_week),
new SqlParameter("@num_month", num_month),
new SqlParameter("@guidePrice", guidePrice),
new SqlParameter("@nowPrice", nowPrice),
new SqlParameter("@maoliLv", maoliLv_1),
new SqlParameter("@overflow", chaochu_1),
new SqlParameter("@jdself", jdself),
new SqlParameter("@maolie", maolie),
}));
}
}
public static IEnumerable <SelectListModel> SelectListNew(string Departments, string VehicleIDS, string PriceRanges, string VehicleBodyTypes, string Specifications, string Brands, string PID, string Province, string City)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Tuhu_Gungnir_BI")))
{
#region SQL
var sql = @"SELECT TT.BrandCategory,
TT.Brand,
TT.Vehicle,
TT.Tires,
TT.MinPrice,
TT.TiresMatch,
TT.VehicleId,
TT.TireSize,
VTR.PID,
VTR.Postion,
BI.ProductId,
VTR.PKID,
VTR.Reason,
BI.RowNumber
FROM ( SELECT T.BrandCategory,
T.Brand,
T.Vehicle,
T.Tires,
T.MinPrice,
T.TiresMatch,
T.VehicleId,
SS.Item AS TireSize
FROM ( SELECT VT.BrandCategory,
VT.Brand,
VT.Vehicle,
VT.Tires,
VT.MinPrice,
VT.TiresMatch,
VT.ProductID AS VehicleId
FROM Gungnir.dbo.tbl_Vehicle_Type AS VT WITH ( NOLOCK )
WHERE (
VT.ProductID IN ( SELECT *
FROM Gungnir..Split(@VehicleIDS, ',') AS S )
OR @VehicleIDS = N'全部' )
AND (
VT.BrandCategory IN ( SELECT *
FROM Gungnir..Split(@Departments, ',') AS S )
OR @Departments = N'全部'
OR (
CHARINDEX(N'其它', @Departments) > 0
AND ISNULL(VT.BrandCategory, '') = '' ) )
AND (
@PriceRanges = N'全部'
OR (
CHARINDEX(N'高', @PriceRanges) > 0
AND VT.MinPrice >= 16 )
OR (
CHARINDEX(N'中', @PriceRanges) > 0
AND VT.MinPrice < 16
AND VT.MinPrice >= 8 )
OR (
CHARINDEX(N'低', @PriceRanges) > 0
AND VT.MinPrice < 8
AND ISNULL(VT.MinPrice, 0) > 0 ) )
AND (
@VehicleBodyTypes = N'全部'
OR VT.VehicleBodyType IN ( SELECT *
FROM Gungnir..Split(@VehicleBodyTypes, ',') AS S2 ) )
AND ISNULL(VT.Tires, '') <> ''
) AS T
CROSS APPLY Gungnir..SplitString(T.Tires, ';', 1) AS SS
WHERE (@Specifications =N'不限' OR SS.Item IN (SELECT * FROM Gungnir.dbo.Split(@Specifications,',') AS S3))
) AS TT
LEFT JOIN Tuhu_productcatalog.dbo.tbl_VehicleTireRecommend AS VTR WITH ( NOLOCK )
ON TT.VehicleId = VTR.VehicleId COLLATE Chinese_PRC_CI_AS
AND TT.TireSize = VTR.TireSize COLLATE Chinese_PRC_CI_AS" ;
if (string.IsNullOrWhiteSpace(Province) || string.IsNullOrWhiteSpace(City))
{
sql += @" LEFT JOIN ( SELECT T.VehicleId,
T.TireSize,
T.ProductId,
T.RowNumber
FROM (SELECT CTR.VehicleId,
CTR.TireSize,
CTR.ProductId,
ROW_NUMBER() OVER(PARTITION BY CTR.VehicleId, CTR.TireSize ORDER BY CTR.Grade DESC) AS RowNumber
FROM Tuhu_bi.dbo.tbl_CarTireRecommendation AS CTR WITH(NOLOCK)
) AS T
WHERE T.RowNumber <= 4
) AS BI
ON BI.VehicleId = TT.VehicleId COLLATE Chinese_PRC_CI_AS AND BI.TireSize COLLATE Chinese_PRC_CI_AS = TT.TireSize";
}
else
{
sql += @" LEFT JOIN ( SELECT T.VehicleId,
T.TireSize,
T.ProductId,
T.RowNumber
FROM (SELECT CTR.VehicleId,
CTR.TireSize,
CTR.ProductId,
ROW_NUMBER() OVER(PARTITION BY CTR.VehicleId, CTR.TireSize ORDER BY CTR.Grade DESC) AS RowNumber
FROM Tuhu_bi.dbo.tbl_CarTireRecommendation_Region AS CTR WITH(NOLOCK)
JOIN Gungnir.dbo.tbl_region AS R WITH(NOLOCK) ON CTR.CityID=R.PKID
WHERE R.RegionName=@City
) AS T
WHERE T.RowNumber <= 4
) AS BI
ON BI.VehicleId = TT.VehicleId COLLATE Chinese_PRC_CI_AS AND BI.TireSize COLLATE Chinese_PRC_CI_AS = TT.TireSize";
}
if ((string.IsNullOrWhiteSpace(Brands) || Brands == "不限") && string.IsNullOrWhiteSpace(PID))
{
//品牌不限PID不限
}
else
{
sql += @" INNER JOIN Tuhu_productcatalog.dbo.vw_Products AS VP
ON VP.PID = VTR.PID
WHERE (@Brands=N'不限' OR VP.CP_Brand COLLATE Chinese_PRC_CI_AS IN (SELECT * FROM Gungnir.dbo.Split(@Brands,',') AS S4))
AND (@PID IS NULL OR VTR.PID =@PID)";
}
sql += @" ORDER BY TT.VehicleId";
#endregion
return(dbHelper.ExecuteDataTable(sql, CommandType.Text, new SqlParameter[] {
new SqlParameter("@VehicleIDS", VehicleIDS.Contains("全部") || string.IsNullOrWhiteSpace(VehicleIDS) ? "全部" :VehicleIDS),
new SqlParameter("@Departments", Departments.Contains("全部") || string.IsNullOrWhiteSpace(Departments) ? "全部" :Departments),
new SqlParameter("@PriceRanges", PriceRanges.Contains("全部") || string.IsNullOrWhiteSpace(PriceRanges) ? "全部" :PriceRanges),
new SqlParameter("@VehicleBodyTypes", VehicleBodyTypes.Contains("全部") || string.IsNullOrWhiteSpace(VehicleBodyTypes) ? "全部" :VehicleBodyTypes),
new SqlParameter("@Specifications", Specifications.Contains("不限") || string.IsNullOrWhiteSpace(Specifications) ? "不限" :Specifications),
new SqlParameter("@Brands", Brands.Contains("不限") || string.IsNullOrWhiteSpace(Brands) ? "不限" :Brands),
//new SqlParameter("@Province",string.IsNullOrWhiteSpace(Province) ? null:Province),
new SqlParameter("@City", string.IsNullOrWhiteSpace(City) ? null:City),
new SqlParameter("@PID", string.IsNullOrWhiteSpace(PID) ? null:PID)
}).ConvertTo <SelectListModel>());
}
}
/// <summary>
/// 캐시를 지원하는 <c>IDacHelper</c>를 구현하는 클래스의 새 인스턴스를 돌려줍니다.
/// </summary>
/// <param name="dbHelper">쿼리를 수행하는데 사용되는 <c>SqlDbHelper</c>입니다.</param>
/// <param name="connectionStringName">연결문자열입니다.</param>
/// <param name="store">캐시 저장 메카니즘의 종류 입니다.</param>
/// <param name="slidingExpiration">캐시의 폐기 시간 입니다.</param>
/// <returns>캐시를 지원하는 <c>IDacHelper</c>를 구현하는 클래스의 새 인스턴스입니다.</returns>
public static IDacHelper CreateCacheable(SqlDbHelper dbHelper, string connectionStringName, PersistenceStore store, TimeSpan slidingExpiration)
{
return new CacheableDacHelper(dbHelper, connectionStringName, GetCacheManager(store), slidingExpiration);
}
//游戏进程的更新
public void processUpdate()
{
switch (step)
{
//游戏开始
case GameProcessEnum.GAME_START: {
//进入下一步的叫分环节
next_step = GameProcessEnum.CALL_SCORE;
//四名玩家同时进入叫分阶段
for (int i = 0; i < 4; i++)
{
//如果该玩家不是机器人(离线的玩家也要对其状态进行更新)
if (lobby.players[i].playerEnum != PlayerEnum.ROBOT && lobby.players[i].playerEnum != PlayerEnum.OFFLINE)
{
lobby.players[i].curServerDeal.next_step = ServerProcess.CALL_SCORE_PROCESS;
lobby.players[i].curServerDeal.initProcessUpdate();
}
}
}
break;
//进入叫分环节（叫分阶段结束）
case GameProcessEnum.CALL_SCORE: {
if (intergation >= 3 || curCallScore % 4 == firstCallScore || reCallScoreNum == MAX_RE_CALLSCORE_NUM - 1)
{
//停止计时
timeCount.Stop();
//已经连续三局没人叫地主了，强制要求一个人当地主
if (reCallScoreNum == MAX_RE_CALLSCORE_NUM - 1 && intergation == 0)
{
reCallScoreNum = 0;
//随机指定一个地主，叫分为1
intergation = 1;
whoIsLand = random.Next(4);
CallScoreProcess.setLandlord(whoIsLand, ref lobby);
}
//如果当前有人叫分
if (intergation > 0)
{
next_step = GameProcessEnum.OUT_CARD;
//四名玩家同时进入出牌阶段(离线的玩家也要对其状态进行更新)
for (int i = 0; i < 4; i++)
{
if (lobby.players[i].playerEnum != PlayerEnum.ROBOT && lobby.players[i].playerEnum != PlayerEnum.OFFLINE)
{
lobby.players[i].curServerDeal.next_step = ServerProcess.OUT_CARD_PROCESS;
lobby.players[i].curServerDeal.initProcessUpdate();
}
}
}
//如果当前没人叫分，则重新开始（三局之内）
else if (reCallScoreNum < MAX_RE_CALLSCORE_NUM - 1)
{
//当前叫分轮数加一
reCallScoreNum++;
next_step = GameProcessEnum.GAME_START;
}
}
}
break;
//进入出牌阶段（出牌阶段结束）
case GameProcessEnum.OUT_CARD: {
//停止计时
timeCount.Stop();
//进行积分结算
peopleOperator.settleScore(peoples, intergation);
//如果玩家不为机器人，将结算完的积分写入数据库
for (int i = 0; i < 4; i++)
{
if (lobby.players[i] != null && lobby.players[i].playerEnum != PlayerEnum.ROBOT)
{
string username = lobby.players[i].username;
int score = peoples[i].integration;
SqlDbHelper.ExecuteNonQuery
("update user_table set score = " + score + " where username = '******'");
}
}
//房间对剩余玩家进行处理，如果还剩玩家，将其放入大厅中，如果不剩，做销毁处理
if (lobby.dealTurnGame() == false)
{
destroyGameProcessDeal();
return;
}
//积分结算完等待下一局游戏开始
next_step = GameProcessEnum.GAME_START;
}
break;
}
//改变状态前的初始化
initProcessUpdate();
}
/// <summary>
/// 캐시를 지원하지 않는 <c>IDacHelper</c>를 구현하는 클래스의 새 인스턴스를 돌려줍니다.
/// </summary>
/// <param name="dbHelper">쿼리를 수행하는데 사용되는 <c>SqlDbHelper</c>입니다.</param>
/// <param name="connectionStringName">연결문자열입니다.</param>
/// <returns>캐시를 지원하지 않는 <c>IDacHelper</c>를 구현하는 클래스의 새 인스턴스입니다.</returns>
public static IDacHelper CreateDefault(SqlDbHelper dbHelper, string connectionStringName)
{
return new DacHelper(dbHelper, connectionStringName);
}
public int Del(object id)
{
string sql = ReflectHelper <T> .GetDelStr(id);
return(SqlDbHelper.ADU(sql));
}
public static int InsertWhite(SqlDbHelper dbHelper, string pid)
{
return(dbHelper.ExecuteNonQuery(TireSql.StockoutStatusWhite.insert, CommandType.Text, new SqlParameter("@PID", pid)));
}
public List <T> Get()
{
string sql = ReflectHelper <T> .GetStr();
return(ReflectHelper <T> .GetList(SqlDbHelper.Get(sql)));
}
private static int GetListCount(SqlDbHelper dbHelper, SqlParameter[] para)
{
return(Convert.ToInt32(dbHelper.ExecuteScalar(TireSql.StockoutWgite.select_count, CommandType.Text, para)));
}
public int Upd(T t)
{
string sql = ReflectHelper <T> .GetUpdStr(t);
return(SqlDbHelper.ADU(sql));
}
public IEnumerable <SE_ShareMakeImportProducts> GetProductEntities(string whereString, SqlParameter[] paramater)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir_AlwaysOnRead")))
return(dbHelper.ExecuteDataTable("SELECT * FROM Configuration.dbo.SE_ShareMakeImportProducts (NOLOCK) where 1=1 " + whereString, CommandType.Text, paramater).ConvertTo <SE_ShareMakeImportProducts>());
}
public ActionResult CreateOrUpdateActivityProduct(Guid?ActivityID, ActivityModel model, string Products)
{
JavaScriptSerializer Serializer = new JavaScriptSerializer();
model.Products = Serializer.Deserialize <List <ActivityProduct> >(Products);
//model.Products == JsonConvert.DeserializeObject<List<ActivityProduct>>(Products);
#region 优惠券ID的处理
var pcodeids = model.PCodeIDS.Split(';');
model.PCodeIDS = string.Empty;
var validateIDS = string.Empty;
var flag = true;
foreach (var id in pcodeids)
{
if (!string.IsNullOrEmpty(id))
{
Guid gid;
if (Guid.TryParse(id, out gid))
{
model.PCodeIDS += id + ";";
if (!validateIDS.Contains(id))
{
validateIDS += id + ";";
}
}
else
{
flag = false;
}
}
}
if (!flag)
{
return(Json(-101));//优惠券ID配置有误
}
if (validateIDS != model.PCodeIDS)
{
return(Json(-102));
}
if (model.PCodeIDS.Length > 0)
{
model.PCodeIDS = model.PCodeIDS.Substring(0, model.PCodeIDS.Length - 1);
}
#endregion
var conn = ConfigurationManager.ConnectionStrings["Gungnir"].ConnectionString;
if (SecurityHelp.IsBase64Formatted(conn))
{
conn = SecurityHelp.DecryptAES(conn);
}
using (var dbHelper = new SqlDbHelper(conn))
{
if (ActivityID == null)
{
//添加活动
var cmd = new SqlCommand("Activity..Action_CreateActivity");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ActivityName", model.ActivityName);
cmd.Parameters.AddWithValue("@StartDateTime", model.StartDateTime);
cmd.Parameters.AddWithValue("@EndDateTime", model.EndDateTime);
cmd.Parameters.AddWithValue("@Author", User.Identity.Name);
cmd.Parameters.AddWithValue("@WebBanner", model.WebBanner);
cmd.Parameters.AddWithValue("@WebOtherPart", model.WebOtherPart);
cmd.Parameters.AddWithValue("@WebBackground", model.WebBackground);
cmd.Parameters.AddWithValue("@ActiveType", model.ActiveType);
cmd.Parameters.AddWithValue("@PlaceQuantity", model.PlaceQuantity);
cmd.Parameters.AddWithValue("@PCodeIDS", string.IsNullOrEmpty(model.PCodeIDS) ? (object)null : model.PCodeIDS);
cmd.Parameters.Add("@Result", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters.Add(new SqlParameter()
{
ParameterName = "@ActivityID",
Direction = ParameterDirection.Output,
SqlDbType = SqlDbType.NVarChar,
Size = 50
});
dbHelper.ExecuteNonQuery(cmd);
int res = Convert.ToInt32(cmd.Parameters["@Result"].Value);
model.ActivityID = Guid.Parse(cmd.Parameters["@ActivityID"].Value.ToString());
if (res > 0)
{
var oprLog = new ThBiz.DataAccess.Entity.OprLog();
oprLog.Author = ThreadIdentity.Operator.Name;
oprLog.ChangeDatetime = DateTime.Now;
oprLog.AfterValue = model.ActivityID.ToString();
oprLog.ObjectType = "FlashSale";
oprLog.Operation = "创建活动";
new OprLogManager().AddOprLog(oprLog);
foreach (var item in model.Products)
{
//添加产品
using (var cmd2 = new SqlCommand(@"IF NOT EXISTS(SELECT 1 FROM Activity..tbl_FlashSaleProducts AS FSP WHERE FSP.PID=@PID AND FSP.ActivityID=@ActivityID) BEGIN INSERT INTO Activity..tbl_FlashSaleProducts ( PID ,Position ,ActivityID ,Price ,TotalQuantity ,MaxQuantity ,SaleOutQuantity,InstallAndPay,Level,ImgUrl,IsUsePCode,Channel,IsJoinPlace,FalseOriginalPrice,CreateDateTime ,LastUpdateDateTime) VALUES ( @PID , @Position ,@ActivityID , @Price , @TotalQuantity , @MaxQuantity ,0,@InstallAndPay,@Level,@ImgUrl,@IsUsePCode,@Channel,@IsJoinPlace,@FalseOriginalPrice, GETDATE() , GETDATE()) END"))
{
cmd2.CommandType = CommandType.Text;
#region AddParameters
cmd2.Parameters.AddWithValue("@PID", item.PID);
cmd2.Parameters.AddWithValue("@Position", item.Position);
cmd2.Parameters.AddWithValue("@ActivityID", model.ActivityID);
cmd2.Parameters.AddWithValue("@Price", item.Price);
cmd2.Parameters.AddWithValue("@FalseOriginalPrice", item.FalseOriginalPrice == 0 ? (object)null : item.FalseOriginalPrice);
cmd2.Parameters.AddWithValue("@TotalQuantity", item.TotalQuantity == 0 ? (object)null : item.TotalQuantity);
cmd2.Parameters.AddWithValue("@MaxQuantity", item.MaxQuantity == 0 ? (object)null : item.MaxQuantity);
cmd2.Parameters.AddWithValue("@Level", item.Level);
cmd2.Parameters.AddWithValue("@InstallAndPay", item.InstallAndPay == "不限" ? (object)null : item.InstallAndPay);
cmd2.Parameters.AddWithValue("@ImgUrl", item.ImgUrl);
cmd2.Parameters.AddWithValue("@IsUsePCode", item.IsUsePCode);
cmd2.Parameters.AddWithValue("@IsJoinPlace", item.IsJoinPlace);
cmd2.Parameters.AddWithValue("@Channel", item.Channel);
#endregion
res = dbHelper.ExecuteNonQuery(cmd2);
}
}
}
//-99已存在该活动名称,
return(Json(res));
}
else
{
//修改活动
var cmd = new SqlCommand("Activity..Action_UpdateActivityByActivityID");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ActivityName", model.ActivityName);
cmd.Parameters.AddWithValue("@ActivityID", model.ActivityID);
cmd.Parameters.AddWithValue("@StartDateTime", model.StartDateTime);
cmd.Parameters.AddWithValue("@EndDateTime", model.EndDateTime);
cmd.Parameters.AddWithValue("@Author", User.Identity.Name);
cmd.Parameters.AddWithValue("@WebBanner", model.WebBanner);
cmd.Parameters.AddWithValue("@WebOtherPart", model.WebOtherPart);
cmd.Parameters.AddWithValue("@WebBackground", model.WebBackground);
cmd.Parameters.AddWithValue("@ActiveType", model.ActiveType);
cmd.Parameters.AddWithValue("@PlaceQuantity", model.PlaceQuantity);
cmd.Parameters.AddWithValue("@PCodeIDS", string.IsNullOrEmpty(model.PCodeIDS) ? (object)null : model.PCodeIDS);
cmd.Parameters.Add("@Result", SqlDbType.Int).Direction = ParameterDirection.Output;
dbHelper.ExecuteNonQuery(cmd);
int res = Convert.ToInt32(cmd.Parameters["@Result"].Value);
if (res > 0)
{
var oprLog = new ThBiz.DataAccess.Entity.OprLog();
oprLog.Author = ThreadIdentity.Operator.Name;
oprLog.ChangeDatetime = DateTime.Now;
oprLog.AfterValue = model.ActivityID.ToString();
oprLog.ObjectType = "FlashSale";
oprLog.Operation = "修改活动";
new OprLogManager().AddOprLog(oprLog);
foreach (var item in model.Products)
{
if (item.PKID == 0)//新增产品
{
using (var cmd2 = new SqlCommand(@"IF NOT EXISTS(SELECT 1 FROM Activity..tbl_FlashSaleProducts AS FSP WHERE FSP.PID=@PID AND FSP.ActivityID=@ActivityID) BEGIN INSERT INTO Activity..tbl_FlashSaleProducts ( PID ,Position ,ActivityID ,Price ,TotalQuantity ,MaxQuantity ,SaleOutQuantity,Level,InstallAndPay,ImgUrl,IsUsePCode,Channel,IsJoinPlace,FalseOriginalPrice,CreateDateTime ,LastUpdateDateTime) VALUES ( @PID , @Position ,@ActivityID , @Price , @TotalQuantity , @MaxQuantity ,0,@Level,@InstallAndPay,@ImgUrl,@IsUsePCode,@Channel,@IsJoinPlace,@FalseOriginalPrice, GETDATE() , GETDATE()) END"))
{
cmd2.CommandType = CommandType.Text;
#region AddParameters
cmd2.Parameters.AddWithValue("@PID", item.PID);
cmd2.Parameters.AddWithValue("@Position", item.Position);
cmd2.Parameters.AddWithValue("@ActivityID", model.ActivityID);
cmd2.Parameters.AddWithValue("@Price", item.Price);
cmd2.Parameters.AddWithValue("@FalseOriginalPrice", item.FalseOriginalPrice == 0 ? (object)null : item.FalseOriginalPrice);
cmd2.Parameters.AddWithValue("@TotalQuantity", item.TotalQuantity == 0 ? (object)null : item.TotalQuantity);
cmd2.Parameters.AddWithValue("@MaxQuantity", item.MaxQuantity == 0 ? (object)null : item.MaxQuantity);
cmd2.Parameters.AddWithValue("@Level", item.Level);
cmd2.Parameters.AddWithValue("@InstallAndPay", item.InstallAndPay == "不限" ? (object)null : item.InstallAndPay);
cmd2.Parameters.AddWithValue("@ImgUrl", item.ImgUrl);
cmd2.Parameters.AddWithValue("@IsUsePCode", item.IsUsePCode);
cmd2.Parameters.AddWithValue("@IsJoinPlace", item.IsJoinPlace);
cmd2.Parameters.AddWithValue("@Channel", item.Channel);
#endregion
dbHelper.ExecuteNonQuery(cmd2);//不用res接收.否则最后一条PID重复的话res就会是-1
}
}
else
{
//修改产品
var cmd2 = new SqlCommand(@"UPDATE Activity..tbl_FlashSaleProducts SET PID=@PID, Position=@Position, Price=@Price, TotalQuantity=@TotalQuantity, MaxQuantity=@MaxQuantity,Level=@Level,InstallAndPay=@InstallAndPay,ImgUrl=@ImgUrl,IsUsePCode=@IsUsePCode,Channel=@Channel,IsJoinPlace=@IsJoinPlace,FalseOriginalPrice=@FalseOriginalPrice, LastUpdateDateTime=GETDATE() WHERE PKID=@PKID");
cmd2.CommandType = CommandType.Text;
#region AddParameters
cmd2.Parameters.AddWithValue("@PID", item.PID);
cmd2.Parameters.AddWithValue("@Position", item.Position);
cmd2.Parameters.AddWithValue("@Price", item.Price);
cmd2.Parameters.AddWithValue("@FalseOriginalPrice", item.FalseOriginalPrice == 0 ? (object)null : item.FalseOriginalPrice);
cmd2.Parameters.AddWithValue("@TotalQuantity", item.TotalQuantity == 0 ? (object)null : item.TotalQuantity);
cmd2.Parameters.AddWithValue("@MaxQuantity", item.MaxQuantity == 0 ? (object)null : item.MaxQuantity);
cmd2.Parameters.AddWithValue("@PKID", item.PKID);
cmd2.Parameters.AddWithValue("@Level", item.Level);
cmd2.Parameters.AddWithValue("@InstallAndPay", item.InstallAndPay == "不限" ? (object)null : item.InstallAndPay);
cmd2.Parameters.AddWithValue("@ImgUrl", item.ImgUrl);
cmd2.Parameters.AddWithValue("@IsUsePCode", item.IsUsePCode);
cmd2.Parameters.AddWithValue("@IsJoinPlace", item.IsJoinPlace);
cmd2.Parameters.AddWithValue("@Channel", item.Channel);
#endregion
res = dbHelper.ExecuteNonQuery(cmd2);
}
}
}
if (res == 0 && model.ActiveType == 1)
{
res = -98;
}
//-1已存在该活动名称,-2增加活动失败
return(Json(res));
}
}
}
private void FilterReport(string start_date, string end_date, string action)
{
string foldername = Server.MapPath("~/CS_REPORT/rpt/List-Of-Approved-Accounts.rpt");
string val_region = TextBox1.Text.ToUpper();
_User Ousr = new _User(Session["username"].ToString());
string[] No_Filter_Users = { "ceo", "vpbsm", "vptfi", "ssgm", "ssm", "brd", "admin" };
string[] User_more_roles = { "6", "14", "20", "21", "23", "31", "36", "52", "62", "64", "65", "71", "72", "77", "79", "90" };
string orig_ids = string.Join(",", User_more_roles);
string tmp_ids = "";
DataTable user_counterid = SqlDbHelper.getDataDT("SELECT counterid FROM userheader WHERE username='******'");
foreach (DataRow ids in user_counterid.Rows)
{
tmp_ids = ids["counterid"].ToString();
}
if (val_region != "")
{
ReportDocument ReportDoc = new ReportDocument();
ReportDoc.Load(foldername);
ReportDoc.SetDatabaseLogon("sa", "p@ssw0rd");
ParameterFields PFIELDS = ReportDoc.ParameterFields;
ParameterDiscreteValue[] param_values = new ParameterDiscreteValue[] { };
ParameterDiscreteValue region_default_val = new ParameterDiscreteValue();
region_default_val.Value = "C";
ParameterDiscreteValue channel_default_val = new ParameterDiscreteValue();
channel_default_val.Value = "All";
ParameterDiscreteValue area_default_val = new ParameterDiscreteValue();
area_default_val.Value = "All";
ParameterDiscreteValue so_default_val = new ParameterDiscreteValue();
so_default_val.Value = "All";
ParameterDiscreteValue start_date_default_val = new ParameterDiscreteValue();
start_date_default_val.Value = start_date;
ParameterDiscreteValue end_date_default_val = new ParameterDiscreteValue();
end_date_default_val.Value = end_date;
PFIELDS["Region"].CurrentValues.Clear();
PFIELDS["Area"].CurrentValues.Clear();
PFIELDS["Channel"].CurrentValues.Clear();
PFIELDS["SO"].CurrentValues.Clear();
PFIELDS["Start Date"].CurrentValues.Clear();
PFIELDS["End Date"].CurrentValues.Clear();
if (action == "button_click" && (Ousr.HasPositionsOf(No_Filter_Users) || orig_ids.IndexOf(tmp_ids) != -1))
{
if (val_region == "LUZON")
{
region_default_val.Value = "CL";
}
else if (val_region == "VISMIN")
{
region_default_val.Value = "CV";
}
else
{
region_default_val.Value = "C";
}
}
else
{
ReportHelper.GetRegions(Session["username"].ToString(), "Region", ref PFIELDS, ref param_values);
foreach (ParameterDiscreteValue pd in PFIELDS["Region"].CurrentValues)
{
if (pd.Value.ToString() == "LUZON")
{
pd.Value = "CL";
}
if (pd.Value.ToString() == "VISMIN")
{
pd.Value = "CV";
}
}
}
ReportHelper.GetChannels(Session["username"].ToString(), "Channel", ref PFIELDS, ref param_values);
ReportHelper.GetAreas(Session["username"].ToString(), "Area", ref PFIELDS, ref param_values);
if (Ousr.HasPositionOf("so") != -1)
{
so_default_val.Value = Ousr.SlpName;
}
bool has_region = false, has_channel = false, has_area = false, has_so = false;
foreach (ParameterField pd in PFIELDS)
{
bool has_val = pd.CurrentValues.Count > 0 ? true : false;
if (has_val)
{
switch (pd.Name.ToUpper())
{
case "REGION": has_region = true; break;
case "CHANNEL": has_channel = true; break;
case "AREA": has_area = true; break;
case "SO": has_so = true; break;
}
}
}
if (!has_region)
{
ReportDoc.ParameterFields["Region"].CurrentValues.Add(region_default_val);
}
if (!has_channel)
{
ReportDoc.ParameterFields["Channel"].CurrentValues.Add(channel_default_val);
}
if (!has_area)
{
ReportDoc.ParameterFields["Area"].CurrentValues.Add(area_default_val);
}
if (!has_so)
{
ReportDoc.ParameterFields["SO"].CurrentValues.Add(so_default_val);
}
ReportDoc.ParameterFields["Start Date"].CurrentValues.Add(start_date_default_val);
ReportDoc.ParameterFields["End Date"].CurrentValues.Add(end_date_default_val);
CrystalReportViewer1.ReportSource = ReportDoc;
}
else
{
CrystalReportViewer1.RefreshReport();
}
}
//添加按钮事件
private void btn_type_Click(object sender, EventArgs e)
{
SqlConnection con; //创建数据库连接对象
SqlCommand cmd; //创建sql执行对象
DButil dButil = new DButil();
con = dButil.SqlOpen();//打开数据库
if (Log.log.user_college == true)
{
string sql = "select c_college from college where c_college='" + text_type.Text.Trim() + "'";
cmd = new SqlCommand(sql, con); //执行sql语句
string name = Convert.ToString(cmd.ExecuteScalar()); //获得是否存在内容
con.Close(); //关闭数据库
if (text_type.Text.Trim() == "") //判断是否输入了类别
{
MessageBox.Show("学院名称不能为空！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else if (name != "")//判断是否存在该类别
{
//存在提示
MessageBox.Show("已存在该学院名称！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
DialogResult dialog = MessageBox.Show("确认添加" + text_type.Text.Trim() + "学院？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (dialog == DialogResult.OK)
{
con = dButil.SqlOpen(); //打开数据库
//sql插入语句
sql = "insert into college(c_college) values ('" + text_type.Text.Trim() + "')";
cmd = new SqlCommand(sql, con); //执行sql语句
int n = cmd.ExecuteNonQuery(); //返回影响行
con.Close(); //关闭数据库
if (n > 0)
{
SqlDbHelper dbHelper = new SqlDbHelper(); //实例化SqlDbHelper类
dbHelper.Operation("添加" + text_type.Text.Trim() + "学院成功！"); //插入操作记录
//成功提示
MessageBox.Show("添加" + text_type.Text.Trim() + "学院成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
//失败提示
MessageBox.Show("添加学院失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
else
{
string sql = "select t_name from type where t_name='" + text_type.Text.Trim() + "'";
cmd = new SqlCommand(sql, con); //执行sql语句
string name = Convert.ToString(cmd.ExecuteScalar()); //获得是否存在内容
con.Close(); //关闭数据库
if (text_type.Text.Trim() == "") //判断是否输入了类别
{
MessageBox.Show("类别不能为空！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else if (name != "")//判断是否存在该类别
{
//存在提示
MessageBox.Show("已存在该类别！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
DialogResult dialog = MessageBox.Show("确认添加" + text_type.Text.Trim() + "图书类别？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (dialog == DialogResult.OK)
{
con = dButil.SqlOpen(); //打开数据库
//sql插入语句
sql = "insert into type(t_name) values ('" + text_type.Text.Trim() + "')";
cmd = new SqlCommand(sql, con); //执行sql语句
int n = cmd.ExecuteNonQuery(); //返回影响行
con.Close(); //关闭数据库
if (n > 0)
{
SqlDbHelper dbHelper = new SqlDbHelper(); //实例化SqlDbHelper类
dbHelper.Operation("添加新图书类别：" + text_type.Text.Trim()); //插入操作记录
//成功提示
MessageBox.Show("添加类别:" + text_type.Text.Trim() + "成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
//失败提示
MessageBox.Show("添加类别失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
}
public static int SetVipCardModelDetailStatus(int cardId, List <string> batchIds, SqlDbHelper dbHelper)
{
string sql = $@"UPDATE Configuration..VipCardSaleConfigDetail
SET Status=0 WHERE VipCardId={cardId} AND BatchId NOT IN(N'{string.Join(",", batchIds)}')" ;
return(Convert.ToInt32(dbHelper.ExecuteNonQuery(sql, CommandType.Text)));
}
public void DefaultCommandTimeoutInSecondsShouldDefaultToSqlCommandCommandTimeout()
{
var helper = new SqlDbHelper("");
Assert.AreEqual(helper.DefaultCommandTimeoutInSeconds, new SqlCommand().CommandTimeout);
}