public static List <TireCouponLogModel> FetchCouponLogByShopName(string ShopName)
{
const string sqlStr = @"SELECT ShopName ,
CouponName ,
CouponEndTime ,
Operator ,
OperateType ,
CreateDateTime
FROM Tuhu_log..TireCouponManageLog WITH ( NOLOCK )
WHERE ShopName = @shopname
ORDER BY CreateDateTime DESC; ";
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Tuhu_log")))
{
using (var cmd = new SqlCommand(sqlStr))
{
cmd.Parameters.AddWithValue("@shopname", ShopName);
return(dbHelper.ExecuteDataTable(cmd).ConvertTo <TireCouponLogModel>()?.ToList() ?? new List <TireCouponLogModel>());
}
}
}
public static IEnumerable <ActivityPageContentModel> FetchActivityPageContentByActivityId(string activityId)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir_AlwaysOnRead")))
{
using (var cmd = new SqlCommand(@" SELECT
apc.PKID ,
apc.Brand ,
apc.ProductType ,
apl.HashKey
FROM
Configuration..ActivePageContent
AS apc WITH ( NOLOCK )
JOIN Configuration..ActivePageList
AS apl WITH ( NOLOCK ) ON apc.FKActiveID = apl.PKID where apc.ActivityID=@Aid AND apc.Type=30"))
{
cmd.Parameters.AddWithValue("@Aid", activityId);
cmd.CommandType = CommandType.Text;
return(dbHelper.ExecuteDataTable(cmd).ConvertTo <ActivityPageContentModel>());
}
}
}
public static IEnumerable <ProductFaqConfigModel> SelectProductFaqConfigModelsPagination(Pagination pagination)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir_AlwaysOnRead")))
{
var sql1 =
@"SELECT PFC.QuestionDetail,PFC.LastUpdateDateTime,P.Pid,P.DisplayName AS ProductName FROM Configuration..ProductFaqConfig AS PFC WITH ( NOLOCK)
join Configuration..ProductFaqPidDetail AS PP on PFC.PKID =PP.FkFaqId
Join Tuhu_productcatalog..vw_Products AS P WITH(NOLOCK) on PP.PID =P.Pid
ORDER BY p.PID
OFFSET ( @PageIndex - 1 ) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY";
var sql2 = @"SELECT Count(0) FROM Configuration..ProductFaqConfig AS PFC WITH ( NOLOCK)
join Configuration..ProductFaqPidDetail AS PP on PFC.PKID =PP.FkFaqId
Join Tuhu_productcatalog..vw_Products AS P WITH(NOLOCK) on PP.PID =P.Pid";
pagination.records = (int)dbHelper.ExecuteScalar(sql2);
return(dbHelper.ExecuteDataTable(sql1, CommandType.Text, new SqlParameter[]
{
new SqlParameter("@PageSize", pagination.rows),
new SqlParameter("@PageIndex", pagination.page)
}).ConvertTo <ProductFaqConfigModel>());
}
}
public static DataTable FetchNeedExamQiangGouAndProducts(Guid aid)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir_AlwaysOnRead")))
{
return(dbHelper.ExecuteDataTable(@"SELECT FS.ActiveType,
FS.ActivityName,
FS.StartDateTime,
FS.EndDateTime,
FS.PlaceQuantity,
FS.IsNewUserFirstOrder,
VP.DisplayName,
VP.cy_list_price AS OriginalPrice,
FSP.*
FROM Activity..tbl_FlashSale_Temp AS FS WITH ( NOLOCK )
JOIN Activity..tbl_FlashSaleProducts_Temp AS FSP WITH ( NOLOCK )
ON FS.ActivityID = FSP.ActivityID
JOIN Tuhu_productcatalog..vw_Products AS VP
ON FSP.PID COLLATE Chinese_PRC_CI_AS = VP.PID
WHERE FS.ActivityID = @ActivityID ORDER BY FSP.PKID ;", CommandType.Text, new SqlParameter("@ActivityID", aid)));
}
}
public static int SaveSingle(IEnumerable <VehicleTireRecommend> list)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir")))
{
var result = -99;
dbHelper.BeginTransaction();
//删
DeleteDatasByVehicleAndTireSize(dbHelper, list.FirstOrDefault());
foreach (var item in list)
{
//插入
result = InsertDataToRecommend(dbHelper, item);
if (result <= 0)
{
dbHelper.Rollback();
}
}
dbHelper.Commit();
return(result);
}
}
public static int SaveQZTJSingle(QZTJModel model)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir")))
{
var result = -99;
dbHelper.BeginTransaction();
//删
DeleteQZTJByPID(model.PID);
foreach (var item in model.Products)
{
//插入
result = InsertQZTJ(dbHelper, item, model.PID);
if (result <= 0)
{
dbHelper.Rollback();
}
}
dbHelper.Commit();
return(result);
}
}
public static List <LowestLimitLogModel> GetLowestLimitLog(string PID, string type)
{
var SqlStr = @"SELECT PID ,
OldPrice ,
NewPrice ,
Operator ,type,
CreateDateTime
FROM Tuhu_log..ProductLowestLimitLog WITH ( NOLOCK )
WHERE PID = @pid and type=@type;";
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Tuhu_log")))
{
using (var cmd = new SqlCommand(SqlStr))
{
cmd.Parameters.AddWithValue("@pid", PID);
cmd.Parameters.AddWithValue("@type", type);
return(dbHelper.ExecuteDataTable(cmd).ConvertTo <LowestLimitLogModel>()?.ToList() ?? new List <LowestLimitLogModel>());
}
}
}
/// <summary>
/// 插入第三方订单渠道key
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static int AddThirdPartyOrderChannel(ThirdPartyOrderChannelModel model)
{
var sql = @"INSERT INTO Tuhu_thirdparty.[dbo].[tbl_ThirdPartyOrderChannel]
( OrderChannel
,CreateDatetime
,LastUpdateDateTime
)
VALUES ( @OrderChannel ,
GETDATE() ,
GETDATE()
)
SELECT SCOPE_IDENTITY();";
using (var conn = new SqlConnection(ConnectionHelper.GetDecryptConn("ThirdParty")))
{
var parameters = new[]
{
new SqlParameter("@OrderChannel", model.OrderChannel),
};
return(Convert.ToInt32(SqlHelper.ExecuteScalar(conn, CommandType.Text, sql, parameters)));
}
}
public static IEnumerable <ReleaseItemModel> SelectList(ReleaseItemModel model, PagerModel pager)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Tuhu_log")))
{
pager.TotalItem = GetListCount(dbHelper, model, pager);
var list = dbHelper.ExecuteDataTable(@"SELECT [PKID]
,[Name]
,[Content]
,[Developer]
,[Tester]
,[Checker]
,[ReleaseTime]
,[IsValid]
,[Status]
,[Reason]
FROM [dbo].[tbl_ReleaseItem](nolock)
where (Name like @Name or @Name is null)
and (Developer like @Developer or @Developer is null)
and (Tester like @Tester or @Tester is null)
and (Checker like @Checker or @Checker is null)
and (datediff(dd,ReleaseTime,@ReleaseTime)=0 or @ReleaseTime is null)
and (Status=@Status or @Status=-1)
order by PKID desc
OFFSET ( @PageIndex - 1 ) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;", CommandType.Text,
new SqlParameter[] {
new SqlParameter("@Name", String.IsNullOrWhiteSpace(model.Name)?null:"%" + model.Name.Replace("|", "%") + "%"),
new SqlParameter("@Developer", String.IsNullOrWhiteSpace(model.Developer)?null:"%" + model.Developer + "%"),
new SqlParameter("@Tester", String.IsNullOrWhiteSpace(model.Tester)?null:"%" + model.Tester + "%"),
new SqlParameter("@Checker", String.IsNullOrWhiteSpace(model.Checker)?null:"%" + model.Checker + "%"),
new SqlParameter("@ReleaseTime", model.ReleaseTime == null?null:model.ReleaseTime),
new SqlParameter("@Status", model.Status),
new SqlParameter("@PageIndex", pager.CurrentPage),
new SqlParameter("@PageSize", pager.PageSize),
}).ConvertTo <ReleaseItemModel>();
return(list);
}
}
/// <summary>
/// 批量删除
/// </summary>
/// <param name="model"></param>
/// <param name="Postions"></param>
/// <returns></returns>
public static int DeleteDatasByPostion(VehicleTireRecommend model, List <int> Postions)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir")))
{
return(dbHelper.ExecuteNonQuery(@"DELETE Tuhu_productcatalog..tbl_VehicleTireRecommend
WHERE TireSize = @TireSize
AND VehicleId COLLATE Chinese_PRC_CI_AS IN (
SELECT *
FROM Gungnir..Split(@VehicleIDS, ',') AS S )
AND Postion IN (
SELECT *
FROM Gungnir..Split(@Postions, ',') AS SS )",
CommandType.Text,
new SqlParameter[]
{
new SqlParameter("@TireSize", model.TireSize),
new SqlParameter("@VehicleIDS", string.Join(",", model.VehicleIDS)),
new SqlParameter("@Postions", string.Join(",", Postions))
}
));
}
}
public static AliPayBaoYangActivity GetAliPayBaoYangActivity()
{
// SupplierInfo result = null;
AliPayBaoYangActivity item = null;
List <AliPayBaoYangActivity> items;
using (var dbhelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("ThirdPartyReadOnly")))
{
string sqlstr = string.Empty;
sqlstr = @"SELECT [PKID],[Name],[BeginTime],[EndTime],[CreateTime],[UpdateTime] FROM [dbo].[AliPayBaoYangActivity]";
var cmd = new SqlCommand(sqlstr);
cmd.CommandType = CommandType.Text;
items = dbhelper.ExecuteDataTable(cmd).ConvertTo <AliPayBaoYangActivity>().ToList();
if (items != null && items.FirstOrDefault() != null)
{
item = items.First();
}
}
return(item);
}
public static DataTable FetchQiangGouAndProductsFromTemp(Guid aid)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir")))
{
return(dbHelper.ExecuteDataTable(@"SELECT FS.ActiveType,
FS.ActivityName,
FS.StartDateTime,
FS.EndDateTime,
FS.PlaceQuantity,
FS.IsNewUserFirstOrder,
CPZCC.DisplayName,
CPCP.cy_list_price AS OriginalPrice,
FSP.*
FROM Activity..tbl_FlashSale_Temp AS FS WITH ( NOLOCK )
JOIN Activity..tbl_FlashSaleProducts_Temp AS FSP WITH ( NOLOCK )
ON FS.ActivityID = FSP.ActivityID
JOIN Tuhu_productcatalog..CarPAR_CatalogProducts AS CPCP ON FSP.PID = CPCP.ProductID
+ '|'
+ CPCP.VariantID
JOIN Tuhu_productcatalog..[CarPAR_zh-CN_Catalog] AS CPZCC ON CPCP.oid = CPZCC.#Catalog_Lang_Oid
WHERE FS.ActivityID = @ActivityID ORDER BY FSP.Position", CommandType.Text, new SqlParameter("@ActivityID", aid)));
}
}
/// <summary>
/// 查询权重信息
/// </summary>
/// <param name="firstType"></param>
/// <param name="secondType"></param>
/// <param name="thirdType"></param>
/// <returns></returns>
public static DataTable SelectGuideParaByType(string firstType, string secondType, string thirdType)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir_AlwaysOnRead")))
{
var sql = @"SELECT DISTINCT
vc.DisplayName AS Item ,
vc.CategoryName AS Link ,
VP.CP_Brand ,
vc.oid ,
vc.ParentOid ,
vc.NodeNo
FROM Tuhu_productcatalog..vw_ProductCategories AS vc WITH ( NOLOCK )
LEFT JOIN Tuhu_productcatalog.dbo.vw_Products AS VP WITH ( NOLOCK ) ON VP.Category = vc.CategoryName
WHERE vc.NodeNo LIKE N'%' + @FirstType + N'%'
AND ( ( @SecondType IS NULL
OR @SecondType = ''
)
OR vc.NodeNo LIKE N'%' + @SecondType + N'%'
)
AND ( ( @ThirdType IS NULL
OR @ThirdType = ''
)
OR vc.NodeNo LIKE N'%' + @ThirdType + N'%'
)
AND vc.ParentOid != -1
AND ( vc.ParentOid != @FirstType
OR @FirstType = N'193649'
);";
DbParameter[] param =
{
new SqlParameter("@FirstType", firstType),
new SqlParameter("@SecondType", secondType),
new SqlParameter("@ThirdType", thirdType),
};
return(dbHelper.ExecuteDataTable(sql, CommandType.Text, param));
}
}
public static IEnumerable <LimitAreaSaleModel> GetLimitAreaSalePidList(int pageSize, int pageNum, string keyWord, int lsLimit)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir_AlwaysOnRead")))
{
var sql = @"
SELECT P.PID ,
P.DisplayName AS ProductName ,
LP.IsLimit ,
COUNT(1) OVER ( ) AS TotalCount
FROM Configuration..LimitAreaSaleProductConfig AS LP WITH ( NOLOCK )
JOIN Tuhu_productcatalog..vw_Products AS P WITH ( NOLOCK ) ON LP.Pid = P.PID
WHERE IsLimit = @IsLimit
ORDER BY Pid
OFFSET ( @PageIndex - 1 ) * @PageSize ROWS FETCH NEXT @PageSize
ROWS ONLY";
return(dbHelper.ExecuteDataTable(sql, CommandType.Text, new SqlParameter[]
{
//new SqlParameter("@Pid", keyWord),
new SqlParameter("@IsLimit", lsLimit),
new SqlParameter("@PageSize", pageSize),
new SqlParameter("@PageIndex", pageNum)
}).ConvertTo <LimitAreaSaleModel>());
}
}
/// <summary>
/// 获取值与orderChannelEngName相同的简称个数
/// </summary>
/// <param name="orderChannelEngName"></param>
/// <returns></returns>
public static int GetTPOrderChannelDiffPinYin(string orderChannelEngName)
{
string sql = @"SELECT COUNT(DISTINCT(equeEngName)) FROM (
SELECT threeEngName+'_'+twoEngName AS equeEngName ,* FROM (
SELECT PARSENAME(REPLACE(OrderChannelEngName,'_','.'),2) AS twoEngName,PARSENAME(REPLACE(OrderChannelEngName,'_','.'),3) AS threeEngName,
OrderChannelEngName,PKID FROM Tuhu_thirdparty.dbo.tbl_ThirdPartyOrderChannellink WITH(NOLOCK)
) a WHERE 1=1 ";
if (!string.IsNullOrWhiteSpace(orderChannelEngName))
{
sql += "and threeEngName=@orderChannelEngName ";
}
sql += ") b";
using (var conn = new SqlConnection(ConnectionHelper.GetDecryptConn("ThirdPartyReadOnly")))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
if (!string.IsNullOrWhiteSpace(orderChannelEngName))
{
cmd.Parameters.Add(new SqlParameter("@orderChannelEngName", orderChannelEngName));
}
return((int)cmd.ExecuteScalar());
}
}
public static ReleaseItemModel FetchReleaseItemModel(long pkid)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Tuhu_log")))
{
return(dbHelper.ExecuteDataTable(@"SELECT [PKID]
,[Name]
,[Content]
,[Developer]
,[Tester]
,[Checker]
,[ReleaseTime]
,[IsValid]
,[CreateDateTime]
,[LastUpdatedDateTime]
,[Reason]
,[Status]
FROM [dbo].[tbl_ReleaseItem] WITH(NOLOCK) WHERE PKID=@PKID
;", CommandType.Text,
new SqlParameter[]
{
new SqlParameter("@PKID", pkid)
}).ConvertTo <ReleaseItemModel>().FirstOrDefault());
}
}
public static List <string> GetModelIdList()
{
List <string> modelIdList = new List <string>();
using (var dbhelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("ThirdPartyReadOnly")))
{
string sqlstr = string.Empty;
sqlstr = @"SELECT ModelID FROM tbl_ThirdPartyVehicle";
var cmd = new SqlCommand(sqlstr);
cmd.CommandType = CommandType.Text;
var dt = dbhelper.ExecuteDataTable(sqlstr);
if (dt != null && dt.Rows != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
modelIdList.Add(dr["ModelID"].ToString());
}
}
}
return(modelIdList);
}
public static int UpdateSupplierInfo(SupplierInfo model)
{
using (var dbhelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("ThirdParty")))
{
var cmd = new SqlCommand(@"UPDATE [dbo].[WeiXinCardSupplier]
SET [brand_name] = @brand
,[logo_url] = @logo
,[LastUpdateDate] = @time
WHERE pkid=@pkid");
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@brand", model.brand_name);
cmd.Parameters.AddWithValue("@logo", model.logo_url);
cmd.Parameters.AddWithValue("@time", DateTime.Now);
cmd.Parameters.AddWithValue("@pkid", model.pkid);
//cmd.Parameters.AddWithValue("@use_all_locations", weixinCardbaseinfo.use_all_locations);
//cmd.Parameters.AddWithValue("@location_id_list", weixinCardbaseinfo.location_id_list);
//cmd.Parameters.AddWithValue("@source", weixinCardbaseinfo.source);
return(Convert.ToInt32(dbhelper.ExecuteNonQuery(cmd)));
}
}
/// <summary>
/// todo 按照晒许查询下一个
/// </summary>
/// <param name="phone"></param>
/// <param name="applicationName"></param>
/// <param name="createDateTime"></param>
/// <returns></returns>
public static RewardApplicationModel FetchRewardApplicationModel(string phone, string[] phones, string applicationName, DateTime?createDateTime)
{
var sql = $@"
SELECT TOP 1 * FROM Configuration..UserRewardApplication WITH(NOLOCK) WHERE Phone <> @Phone and Phone in (SELECT *
FROM Configuration.dbo.Split(@Phones, ',') ) AND ApplicationState=1 order by pkid";
//if (!string.IsNullOrEmpty(applicationName))
//{
// sql += $@"AND ApplicationName like N'%{applicationName}%'";
//}
//if (createDateTime.HasValue)
//{
// sql += $@"AND CreateDateTime >= CONVERT(VARCHAR(100), CONVERT(DATE, '{createDateTime.Value}'), 23) AND
// CreateDateTime <= CONVERT(VARCHAR(100), CONVERT(DATE, '{createDateTime.Value.AddDays(1)}'), 23)";
//}
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir")))
{
return(dbHelper.ExecuteDataTable(sql, CommandType.Text, new[]
{
new SqlParameter("@Phones", string.Join(",", phones)),
new SqlParameter("@Phone", phone)
}).ConvertTo <RewardApplicationModel>().FirstOrDefault());
}
}
public static int FetchProductListCount(ProductListRequest request)
{
#region SQL
var SqlStr = @"SELECT COUNT(1)
FROM Tuhu_productcatalog..vw_Products AS T WITH ( NOLOCK )
LEFT JOIN Tuhu_productcatalog..TireLowestPrice AS P WITH ( NOLOCK ) ON T.PID = P.PID
LEFT JOIN Tuhu_bi..dm_Product_SalespredictData AS S WITH ( NOLOCK ) ON T.PID = S.PID
WHERE T.IsShow = 1
AND ( ( T.PID LIKE '%' + @pid + '%'
OR @pid IS NULL
)
AND T.PID LIKE 'TR-%'
)
AND ( T.DisplayName LIKE '%' + @productname + '%'
OR @productname IS NULL
)
AND ( T.CP_Brand = @Brand
OR @Brand IS NULL
)
AND ( @OnSaleStatus = 0
OR @OnSaleStatus = 1
AND T.OnSale = 1
OR @OnSaleStatus = 2
AND T.OnSale = 0
)
AND ( {0}
);" ;
#endregion
List <string> ManyCustomPrice = request.ManyCustomPrice?.Split('|').ToList();
int Contrast = request.Contrast;
string SingleCustomPrice = GetSqlParameter(request.SingleCustomPrice);
double Proportion = request.Proportion;
var sqlPara = "1=1";
if (request.Type > 0 && ManyCustomPrice != null && !string.IsNullOrWhiteSpace(SingleCustomPrice) && Contrast != 0)
{
sqlPara = "1 <> 1";
var ContrastSig = Contrast == -1 ? "<=" : ">=";
foreach (var item in ManyCustomPrice)
{
var realPara = GetSqlParameter(item);
if (!string.IsNullOrWhiteSpace(realPara))
{
sqlPara += $@" OR ( ISNULL({realPara}, 0) > 0
AND {realPara} / IIF(ISNULL({SingleCustomPrice}, 0) = 0, 1, {SingleCustomPrice}) {ContrastSig} {Proportion})" ;
}
}
}
SqlStr = string.Format(SqlStr, sqlPara);
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Tuhu_Gungnir_BI")))
{
using (var cmd = new SqlCommand(SqlStr))
{
cmd.CommandTimeout = 3 * 60;
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@pid", request.PID);
cmd.Parameters.AddWithValue("@productname", request.ProductName);
cmd.Parameters.AddWithValue("@OnSaleStatus", request.OnSale);
cmd.Parameters.AddWithValue("@Brand", request.Brand);
var result = dbHelper.ExecuteScalar(cmd);
if (Int32.TryParse(result?.ToString(), out int value))
{
return(value);
}
return(0);
}
}
}
public static List <ProductPriceModel> FetchProductList(ProductListRequest request, PagerModel pager, bool isExport)
{
#region SQL
var SqlStr = @"SELECT T.CP_Brand AS Brand ,
T.PID AS PID ,
T.DisplayName AS ProductName ,
S.cost AS Cost ,
P.LowestPrice AS LowestLimit ,
T.cy_list_price AS Price ,
S.taobao_tuhuid AS TBPID ,
S.taobao_tuhuprice AS TBPrice ,
S.taobao2_tuhuid AS TB2PID ,
S.taobao2_tuhuprice AS TB2Price ,
S.tianmao1_tuhuprice AS TM1Price ,
S.tianmao1_tuhuid AS TM1PID ,
S.tianmao2_tuhuprice AS TM2Price ,
S.tianmao2_tuhuid AS TM2PID ,
S.tianmao3_tuhuprice AS TM3Price ,
S.tianmao3_tuhuid AS TM3PID ,
S.tianmao4_tuhuid AS TM4PID ,
S.tianmao4_tuhuprice AS TM4Price ,
S.jingdongflagship_tuhuprice AS JDFlagShipPrice ,
S.jingdongflagship_tuhuid AS JDFlagShipPID ,
S.jingdong_tuhuprice AS JDPrice ,
S.jingdong_tuhuid AS JDPID,
e.CanUseCoupon as CanUseCoupon,
s.num_threemonth
FROM Tuhu_productcatalog..vw_Products AS T WITH ( NOLOCK )
LEFT JOIN Tuhu_productcatalog..TireLowestPrice AS P WITH ( NOLOCK ) ON T.PID = P.PID
LEFT JOIN Tuhu_bi..dm_Product_SalespredictData AS S WITH ( NOLOCK ) ON T.PID = S.PID
left join Tuhu_productcatalog..tbl_ProductExtraProperties as e WITH ( NOLOCK ) on e.pid=t.pid
WHERE T.IsShow = 1
AND ( ( T.PID LIKE '%' + @pid + '%'
OR @pid IS NULL
)
AND T.PID LIKE 'TR-%'
)
AND ( T.DisplayName LIKE '%' + @productname + '%'
OR @productname IS NULL
)
AND ( T.CP_Brand = @Brand
OR @Brand IS NULL
)
AND ( @OnSaleStatus = 0
OR @OnSaleStatus = 1
AND T.OnSale = 1
OR @OnSaleStatus = 2
AND T.OnSale = 0
)
AND ( {0}
)" ;
SqlStr += isExport ? ";" : @"ORDER BY s.num_threemonth desc
OFFSET @start ROWS FETCH NEXT @step ROWS ONLY;" ;
#endregion +
pager.TotalItem = FetchProductListCount(request);
List <string> ManyCustomPrice = request.ManyCustomPrice?.Split('|').ToList();
int Contrast = request.Contrast;
string SingleCustomPrice = GetSqlParameter(request.SingleCustomPrice);
double Proportion = request.Proportion;
var sqlPara = "1 = 1";
if (request.Type > 0 && ManyCustomPrice != null && !string.IsNullOrWhiteSpace(SingleCustomPrice) && Contrast != 0)
{
sqlPara = "1 <> 1";
var ContrastSig = Contrast == -1 ? "<=" : ">=";
foreach (var item in ManyCustomPrice)
{
var realPara = GetSqlParameter(item);
if (!string.IsNullOrWhiteSpace(realPara))
{
sqlPara += $@" OR ( ISNULL({realPara}, 0) > 0
AND {realPara} / IIF(ISNULL({SingleCustomPrice}, 0) = 0, 1, {SingleCustomPrice}) {ContrastSig} {Proportion})" ;
}
}
}
//string.Format(SqlStr, sqlPara);
SqlStr = string.Format(SqlStr, sqlPara);
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Tuhu_Gungnir_BI")))
{
using (var cmd = new SqlCommand(SqlStr))
{
cmd.CommandTimeout = 3 * 60;
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@pid", request.PID);
cmd.Parameters.AddWithValue("@productname", request.ProductName);
cmd.Parameters.AddWithValue("@OnSaleStatus", request.OnSale);
cmd.Parameters.AddWithValue("@Brand", request.Brand);
cmd.Parameters.AddWithValue("@start", (request.PageIndex - 1) * request.PageSize);
cmd.Parameters.AddWithValue("@step", request.PageSize);
var result = dbHelper.ExecuteDataTable(cmd);
return(result.ConvertTo <ProductPriceModel>()?.ToList() ?? new List <ProductPriceModel>());
}
}
}
public static bool Insert(LuckyWheel model)
{
string sql = @"INSERT INTO Activity..LuckyWheel
( ID ,
Title ,
isNewUser ,
isStatus ,
CreateDate ,
UpdateDate ,
DataParames,
IsAddOne,
IsIntegral,
Integral,
CreatorUser,
UpdateUser,PreShareTimes,CompletedShareTimes
)
VALUES ( @ID , @Title , @isNewUser , @isStatus , GETDATE() , @UpdateDate , @DataParames ,@IsAddOne,@IsIntegral,@Integral,@CreatorUser,@UpdateUser ,@PreShareTimes, @CompletedShareTimes )";
bool result = false;
SqlConnection conn = new SqlConnection(ConnectionHelper.GetDecryptConn("Gungnir"));
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
try
{
//
string sqlDelete = @" DELETE FROM Activity..LuckyWheel WHERE ID = @ID DELETE FROM Activity..LuckyWheelDeatil WHERE FKLuckyWheelID = @ID";
SqlHelper.ExecuteNonQuery(trans, System.Data.CommandType.Text, sqlDelete, new SqlParameter("@ID", model.ID));
SqlParameter[] parameters = new SqlParameter[13];
parameters[0] = new SqlParameter("@ID", model.ID);
parameters[1] = new SqlParameter("@Title", model.Title);
parameters[2] = new SqlParameter("@isNewUser", model.isNewUser);
parameters[3] = new SqlParameter("@isStatus", model.isStatus);
parameters[4] = new SqlParameter("@UpdateDate", model.UpdateDate);
parameters[5] = new SqlParameter("@DataParames", model.DataParames);
parameters[6] = new SqlParameter("@IsAddOne", model.IsAddOne);
parameters[7] = new SqlParameter("@IsIntegral", model.IsIntegral);
parameters[8] = new SqlParameter("@Integral", model.Integral);
parameters[9] = new SqlParameter("@CreatorUser", model.CreatorUser);
parameters[10] = new SqlParameter("@UpdateUser", model.UpdateUser);
parameters[11] = new SqlParameter("@PreShareTimes", model.PreShareTimes);
parameters[12] = new SqlParameter("@CompletedShareTimes", model.CompletedShareTimes);
SqlHelper.ExecuteNonQuery(trans, System.Data.CommandType.Text, sql, parameters);
string sqlDeatil = @" INSERT INTO Activity..LuckyWheelDeatil
( FKLuckyWheelID ,
Type ,
CouponRuleID ,
MaxCoupon ,
BGImage ,
ContentImage ,
ChangeImage ,
GetDescription ,
GoDescription ,
APPUrl ,
WapUrl ,
WwwUrl ,
HandlerAndroid ,
SOAPAndroid ,
HandlerIOS ,
SOAPIOS ,
UserRank ,
OrderBy
)
VALUES ( @FKLuckyWheelID ,
@Type ,
@CouponRuleID ,
@MaxCoupon ,
@BGImage ,
@ContentImage ,
@ChangeImage ,
@GetDescription ,
@GoDescription ,
@APPUrl ,
@WapUrl ,
@WwwUrl,
@HandlerAndroid ,
@SOAPAndroid ,
@HandlerIOS ,
@SOAPIOS ,
@UserRank ,
@OrderBy
)";
foreach (var item in model.Items)
{
SqlParameter[] paras = new SqlParameter[18];
paras[0] = new SqlParameter("@FKLuckyWheelID", model.ID);
paras[1] = new SqlParameter("@Type", item.Type);
paras[2] = new SqlParameter("@CouponRuleID", item.CouponRuleID);
paras[3] = new SqlParameter("@MaxCoupon", string.IsNullOrWhiteSpace(item.MaxCoupon)?null:item.MaxCoupon);
paras[4] = new SqlParameter("@BGImage", item.BGImage);
paras[5] = new SqlParameter("@ContentImage", item.ContentImage);
paras[6] = new SqlParameter("@ChangeImage", item.ChangeImage);
paras[7] = new SqlParameter("@GetDescription", item.GetDescription);
paras[8] = new SqlParameter("@GoDescription", item.GoDescription);
paras[9] = new SqlParameter("@APPUrl", item.APPUrl);
paras[10] = new SqlParameter("@WapUrl", item.WapUrl);
paras[11] = new SqlParameter("@WwwUrl", item.WwwUrl);
paras[12] = new SqlParameter("@HandlerAndroid", item.HandlerAndroid);
paras[13] = new SqlParameter("@SOAPAndroid", item.SOAPAndroid);
paras[14] = new SqlParameter("@HandlerIOS", item.HandlerIOS);
paras[15] = new SqlParameter("@SOAPIOS", item.SOAPIOS);
paras[16] = new SqlParameter("@UserRank", item.UserRank);
paras[17] = new SqlParameter("@OrderBy", item.OrderBy);
SqlHelper.ExecuteNonQuery(trans, System.Data.CommandType.Text, sqlDeatil, paras);
}
trans.Commit();
result = true;
}
catch (Exception em)
{
trans.Rollback();
result = false;
}
finally
{
conn.Close();
conn.Dispose();
trans.Dispose();
}
return(result);
}
/// <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),
}));
}
}
/// <summary>
/// 查询保养品分仓信息
/// </summary>
/// <param name="pid"></param>
/// <returns></returns>
public static List <Product_SalespredictData> SelectProductSalespredictData(IEnumerable <string> pids)
{
List <Product_SalespredictData> result = new List <Product_SalespredictData>();
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Tuhu_BI")))
{
var sql = @"SELECT pid ,
ISNULL(totalstock, 0) AS totalstock ,
ISNULL(num_threemonth, 0) AS num_threemonth ,
ISNULL(num_month, 0) AS num_month ,
ISNULL(num_week, 0) AS num_week
FROM Tuhu_bi..dm_Product_SalespredictData (NOLOCK)
WHERE pid = @pid;
SELECT pid ,
warehouseid ,
warehousename ,
WarehouseType ,
ISNULL(stocknum, 0) AS totalstock ,
ISNULL(num_threemonth, 0) AS num_threemonth ,
ISNULL(num_month, 0) AS num_month ,
ISNULL(num_week, 0) AS num_week
FROM Tuhu_bi..dm_Product_Warehouse_SalespredictData (NOLOCK)
WHERE EXISTS (
SELECT 1
FROM Tuhu_bi..SplitString(@pids, ',', 1) AS pids
WHERE pids.Item = pid
)
AND warehouseid IS NOT NULL;";
var parameters = new SqlParameter[]
{
new SqlParameter("@pids", string.Join(",", pids)),
new SqlParameter("@pid", pids.FirstOrDefault())
};
var ds = dbHelper.ExecuteDataSet(sql, CommandType.Text, parameters);
var totalData = ds.Tables[0];
if (totalData != null && totalData.Rows.Count > 0)
{
var item = new Product_SalespredictData
{
WareHouseName = "总库存",
TotalStock = Convert.ToInt32(totalData.Rows[0]["totalstock"]),
Num_ThreeMonth = Convert.ToInt32(totalData.Rows[0]["num_threemonth"]),
Num_Month = Convert.ToInt32(totalData.Rows[0]["num_month"]),
Num_Week = Convert.ToInt32(totalData.Rows[0]["num_week"]),
};
result.Add(item);
}
var regionData = ds.Tables[1];
if (regionData != null && regionData.Rows.Count > 0)
{
foreach (DataRow row in regionData.Rows)
{
var item = new Product_SalespredictData
{
Pid = row["pid"].ToString(),
WareHouseName = row["warehousename"].ToString(),
TotalStock = Convert.ToInt32(row["totalstock"]),
Num_ThreeMonth = Convert.ToInt32(row["num_threemonth"]),
Num_Month = Convert.ToInt32(row["num_month"]),
Num_Week = Convert.ToInt32(row["num_week"]),
WarehouseType = row["WarehouseType"]?.ToString() ?? string.Empty
};
result.Add(item);
}
}
return(result);
}
}
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 ,
VP.cy_marketing_price AS MarketingPrice ,
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 IEnumerable <TirePatternModel> SelectList(TirePatternModel model, PagerModel pager)
{
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir")))
{
pager.TotalItem = GetListCount(dbHelper, model);
return(dbHelper.ExecuteDataTable(@"SELECT T.Brand,
T.Pattern,
PA.Image,
PA.Title,
PA.Describe,
PA.Author,
PA.Date,
PA.ArticleLink,
PA.IsShow,
PA.IsActive,
PA.PKID
FROM ( SELECT DISTINCT
VP.CP_Brand AS Brand,
VP.CP_Tire_Pattern AS Pattern
FROM Tuhu_productcatalog.dbo.vw_Products AS VP WITH ( NOLOCK )
WHERE VP.PID LIKE 'TR-%'
AND ISNULL(VP.CP_Brand, '') <> ''
AND ISNULL(VP.CP_Tire_Pattern, '') <> ''
AND VP.OnSale=1
AND VP.stockout=0
) AS T
LEFT JOIN Tuhu_productcatalog.dbo.PatternArticle AS PA WITH ( NOLOCK )
ON PA.Brand = T.Brand
AND PA.Pattern = T.Pattern
WHERE (
T.Brand = @Brand
OR @Brand IS NULL )
AND (
T.Pattern = @Pattern
OR @Pattern IS NULL )
AND (
PA.Title = @Title
OR @Title IS NULL )
AND (
PA.Author = @Author
OR @Author IS NULL )
AND (
@Count = 0
OR @Count = 1
AND PA.PKID > 0
OR @Count = 2
AND PA.PKID IS NULL )
AND (
@ShowCount = 0
OR @ShowCount = 1
AND T.Pattern IN ( SELECT PAA.Pattern
FROM Tuhu_productcatalog.dbo.PatternArticle AS PAA WITH ( NOLOCK )
WHERE PAA.IsShow = 1 )
OR @ShowCount = 2
AND T.Pattern NOT IN ( SELECT PAA.Pattern
FROM Tuhu_productcatalog.dbo.PatternArticle AS PAA WITH ( NOLOCK )
WHERE PAA.IsShow = 1 )
AND PA.PKID > 0 )
ORDER BY T.Pattern
OFFSET ( @PageIndex - 1 ) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;", CommandType.Text,
new SqlParameter[] {
new SqlParameter("@Brand", String.IsNullOrWhiteSpace(model.Brand)?null:model.Brand),
new SqlParameter("@Pattern", String.IsNullOrWhiteSpace(model.Pattern)?null:model.Pattern),
new SqlParameter("@Title", String.IsNullOrWhiteSpace(model.Title)?null:model.Title),
new SqlParameter("@Author", String.IsNullOrWhiteSpace(model.Author)?null:model.Author),
new SqlParameter("@Count", model.Count),
new SqlParameter("@ShowCount", model.ShowCount),
new SqlParameter("@PageIndex", pager.CurrentPage),
new SqlParameter("@PageSize", pager.PageSize),
}).ConvertTo <TirePatternModel>());
}
}
public bool UpdateQuestionWithOptionList(List <QuestionWithOption> questionWithOptionList)
{
var operationsuccess = true;
string conn = ConnectionHelper.GetDecryptConn("Gungnir");
using (var db = new SqlDbHelper(conn))
{
try
{
long questionaireId = 0;
db.BeginTransaction();
string questionUpdateSql = string.Empty;
foreach (var questionwithOption in questionWithOptionList)
{
questionUpdateSql = "update Activity..Question set QuestionTitle=" + "N\'" + questionwithOption.QuestionTitle + "\'" + ",StartTime=" + "N\'" + questionwithOption.StartTime + "\'" + ",EndTime=" + "N\'" + questionwithOption.EndTime + "\'" + ",DeadLineTime=" + "N\'" + questionwithOption.DeadLineTime + "\'" + ",LastUpdateDateTime=" + "N\'" + DateTime.Now + "\'" + " where PKID=" + questionwithOption.PKID;
int questionupdateresult = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, questionUpdateSql);
// long insertquestionid = 0;
if (questionupdateresult >= 0)
{
#region YesOptionUpdate
var optionAYescontent = string.Format("使用{0}积分，猜对赢{1}兑换券", questionwithOption.YesOptionAUseIntegral, questionwithOption.YesOptionAWinCouponCount);
var optionBYescontent = string.Format("使用{0}积分，猜对赢{1}兑换券", questionwithOption.YesOptionBUseIntegral, questionwithOption.YesOptionBWinCouponCount);
var optionCYescontent = string.Format("使用{0}积分，猜对赢{1}兑换券", questionwithOption.YesOptionCUseIntegral, questionwithOption.YesOptionCWinCouponCount);
string yesoptionUpdateSql = string.Empty;
yesoptionUpdateSql += "Update Activity..QuestionOption set OptionContent=" + "N\'" + optionAYescontent + "\'" + ",UseIntegral=" + questionwithOption.YesOptionAUseIntegral + ",WinCouponCount=" + questionwithOption.YesOptionAWinCouponCount + ",LastUpdateDateTime=" + "N\'" + DateTime.Now + "\'" + " where PKID=" + questionwithOption.YesOptionAPKID;
yesoptionUpdateSql += "\n";
yesoptionUpdateSql += "Update Activity..QuestionOption set OptionContent=" + "N\'" + optionBYescontent + "\'" + ",UseIntegral=" + questionwithOption.YesOptionBUseIntegral + ",WinCouponCount=" + questionwithOption.YesOptionBWinCouponCount + ",LastUpdateDateTime=" + "N\'" + DateTime.Now + "\'" + " where PKID=" + questionwithOption.YesOptionBPKID;
yesoptionUpdateSql += "\n";
yesoptionUpdateSql += "Update Activity..QuestionOption set OptionContent=" + "N\'" + optionCYescontent + "\'" + ",UseIntegral=" + questionwithOption.YesOptionCUseIntegral + ",WinCouponCount=" + questionwithOption.YesOptionCWinCouponCount + ",LastUpdateDateTime=" + "N\'" + DateTime.Now + "\'" + " where PKID=" + questionwithOption.YesOptionCPKID;
yesoptionUpdateSql += "\n";
int Yesaffectrows = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, yesoptionUpdateSql);
if (Yesaffectrows < 0)
{
db.Rollback();
operationsuccess = false;
return(false);
}
#endregion
#region NoOptionInsert
var optionANocontent = string.Format("使用{0}积分，猜对赢{1}兑换券", questionwithOption.NoOptionAUseIntegral, questionwithOption.NoOptionAWinCouponCount);
var optionBNocontent = string.Format("使用{0}积分，猜对赢{1}兑换券", questionwithOption.NoOptionBUseIntegral, questionwithOption.NoOptionBWinCouponCount);
var optionCNocontent = string.Format("使用{0}积分，猜对赢{1}兑换券", questionwithOption.NoOptionCUseIntegral, questionwithOption.NoOptionCWinCouponCount);
string nooptionUpdateSql = string.Empty;
nooptionUpdateSql += "Update Activity..QuestionOption set OptionContent=" + "N\'" + optionANocontent + "\'" + ",UseIntegral=" + questionwithOption.NoOptionAUseIntegral + ",WinCouponCount=" + questionwithOption.NoOptionAWinCouponCount + ",LastUpdateDateTime=" + "N\'" + DateTime.Now + "\'" + " where PKID=" + questionwithOption.NoOptionAPKID;
nooptionUpdateSql += "\n";
nooptionUpdateSql += "Update Activity..QuestionOption set OptionContent=" + "N\'" + optionBNocontent + "\'" + ",UseIntegral=" + questionwithOption.NoOptionBUseIntegral + ",WinCouponCount=" + questionwithOption.NoOptionBWinCouponCount + ",LastUpdateDateTime=" + "N\'" + DateTime.Now + "\'" + " where PKID=" + questionwithOption.NoOptionBPKID;
nooptionUpdateSql += "\n";
nooptionUpdateSql += "Update Activity..QuestionOption set OptionContent=" + "N\'" + optionCNocontent + "\'" + ",UseIntegral=" + questionwithOption.NoOptionCUseIntegral + ",WinCouponCount=" + questionwithOption.NoOptionCWinCouponCount + ",LastUpdateDateTime=" + "N\'" + DateTime.Now + "\'" + " where PKID=" + questionwithOption.NoOptionCPKID;
nooptionUpdateSql += "\n";
int Noaffectrows = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, nooptionUpdateSql);
if (Noaffectrows < 0)
{
db.Rollback();
operationsuccess = false;
return(false);
}
}
else
{
db.Rollback();
operationsuccess = false;
return(false);
}
#endregion
}
if (operationsuccess)
{
db.Commit();
return(true);
}
}
catch (Exception ex)
{
db.Rollback();
return(false);
}
}
return(operationsuccess);
}
public bool SaveQuestionWithOptionList(List <QuestionWithOption> questionWithOptionList)
{
var operationsuccess = true;
string conn = ConnectionHelper.GetDecryptConn("Gungnir");
using (var db = new SqlDbHelper(conn))
{
try
{
long questionaireId = 0;
db.BeginTransaction();
string sql = "select top 1 PKID from Activity..Questionnaire where QuestionnaireType=5";
object questionaireIdObj = SqlHelper.ExecuteScalar(conn, CommandType.Text, sql);
if (questionaireIdObj != null)
{
questionaireId = Convert.ToInt64(questionaireIdObj);
}
string questionInsertSql = string.Empty;
foreach (var questionwithOption in questionWithOptionList)
{
questionInsertSql = @"insert into Activity..Question(QuestionnaireID,QuestionTitle,QuestionType,IsRequired,IsValidateStartDate,StartTime,IsValidateEndDate,EndTime,DeadLineTime) values(" + questionaireId + "," + "N\'" + questionwithOption.QuestionTitle + "\'" + "," + questionwithOption.QuestionType + "," + "1" + "," + "1" + "," + "N\'" + questionwithOption.StartTime + "\'" + "," + "1" + "," + "N\'" + questionwithOption.EndTime + "\'" + "," + "N\'" + questionwithOption.DeadLineTime + "\'" + "); SELECT SCOPE_IDENTITY();";
var insertquestionidOjb = SqlHelper.ExecuteScalar(conn, CommandType.Text, questionInsertSql);
long insertquestionid = 0;
if (insertquestionidOjb != null && long.TryParse(insertquestionidOjb.ToString(), out insertquestionid) && insertquestionid > 0)
{
questionwithOption.PKID = insertquestionid;
#region YesOptionInsert
var yesquestionInsertSql = @"insert into Activity..QuestionOption(QuestionnaireID, QuestionID, OptionContent) values(" + questionaireId + "," + insertquestionid + "," + "N\'是\'" + "); SELECT SCOPE_IDENTITY();";
var yesinsertquestionidOjb = SqlHelper.ExecuteScalar(conn, CommandType.Text, yesquestionInsertSql);
long yesinsertquestionid = 0;
if (yesinsertquestionidOjb != null && long.TryParse(yesinsertquestionidOjb.ToString(), out yesinsertquestionid) && yesinsertquestionid > 0)
{
var optionAYescontent = string.Format("使用{0}积分，猜对赢{1}兑换券", questionwithOption.YesOptionAUseIntegral, questionwithOption.YesOptionAWinCouponCount);
var optionBYescontent = string.Format("使用{0}积分，猜对赢{1}兑换券", questionwithOption.YesOptionBUseIntegral, questionwithOption.YesOptionBWinCouponCount);
var optionCYescontent = string.Format("使用{0}积分，猜对赢{1}兑换券", questionwithOption.YesOptionCUseIntegral, questionwithOption.YesOptionCWinCouponCount);
string yesoptionInsertSql = string.Empty;
yesoptionInsertSql += @"insert into Activity..QuestionOption(QuestionnaireID,QuestionID,OptionContent,QuestionParentID,UseIntegral,WinCouponCount) values(" + questionaireId + "," + insertquestionid + "," + "N\'" + optionAYescontent + "\'" + "," + yesinsertquestionid + "," + questionwithOption.YesOptionAUseIntegral + "," + questionwithOption.YesOptionAWinCouponCount + ")";
yesoptionInsertSql += "\n";
yesoptionInsertSql += @"insert into Activity..QuestionOption(QuestionnaireID,QuestionID,OptionContent,QuestionParentID,UseIntegral,WinCouponCount) values(" + questionaireId + "," + insertquestionid + "," + "N\'" + optionBYescontent + "\'" + "," + yesinsertquestionid + "," + questionwithOption.YesOptionBUseIntegral + "," + questionwithOption.YesOptionBWinCouponCount + ")";
yesoptionInsertSql += "\n";
yesoptionInsertSql += @"insert into Activity..QuestionOption(QuestionnaireID,QuestionID,OptionContent,QuestionParentID,UseIntegral,WinCouponCount) values(" + questionaireId + "," + insertquestionid + "," + "N\'" + optionCYescontent + "\'" + "," + yesinsertquestionid + "," + questionwithOption.YesOptionCUseIntegral + "," + questionwithOption.YesOptionCWinCouponCount + ")";
yesoptionInsertSql += "\n";
int affectrows = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, yesoptionInsertSql);
if (affectrows < 0)
{
db.Rollback();
operationsuccess = false;
return(false);
}
}
else
{
db.Rollback();
operationsuccess = false;
return(false);
}
#endregion
#region NoOptionInsert
var noquestionInsertSql = @"insert into Activity..QuestionOption(QuestionnaireID, QuestionID, OptionContent) values(" + questionaireId + "," + insertquestionid + "," + "N\'否\'" + "); SELECT SCOPE_IDENTITY();";
var noinsertquestionidOjb = SqlHelper.ExecuteScalar(conn, CommandType.Text, noquestionInsertSql);
long noinsertquestionid = 0;
if (noinsertquestionidOjb != null && long.TryParse(noinsertquestionidOjb.ToString(), out noinsertquestionid) && noinsertquestionid > 0)
{
var optionANocontent = string.Format("使用{0}积分，猜对赢{1}兑换券", questionwithOption.NoOptionAUseIntegral, questionwithOption.NoOptionAWinCouponCount);
var optionBNocontent = string.Format("使用{0}积分，猜对赢{1}兑换券", questionwithOption.NoOptionBUseIntegral, questionwithOption.NoOptionBWinCouponCount);
var optionCNocontent = string.Format("使用{0}积分，猜对赢{1}兑换券", questionwithOption.NoOptionCUseIntegral, questionwithOption.NoOptionCWinCouponCount);
string nooptionInsertSql = string.Empty;
nooptionInsertSql += @"insert into Activity..QuestionOption(QuestionnaireID,QuestionID,OptionContent,QuestionParentID,UseIntegral,WinCouponCount) values(" + questionaireId + "," + insertquestionid + "," + "N\'" + optionANocontent + "\'" + "," + noinsertquestionid + "," + questionwithOption.YesOptionAUseIntegral + "," + questionwithOption.YesOptionAWinCouponCount + ")";
nooptionInsertSql += "\n";
nooptionInsertSql += @"insert into Activity..QuestionOption(QuestionnaireID,QuestionID,OptionContent,QuestionParentID,UseIntegral,WinCouponCount) values(" + questionaireId + "," + insertquestionid + "," + "N\'" + optionBNocontent + "\'" + "," + noinsertquestionid + "," + questionwithOption.YesOptionBUseIntegral + "," + questionwithOption.YesOptionBWinCouponCount + ")";
nooptionInsertSql += "\n";
nooptionInsertSql += @"insert into Activity..QuestionOption(QuestionnaireID,QuestionID,OptionContent,QuestionParentID,UseIntegral,WinCouponCount) values(" + questionaireId + "," + insertquestionid + "," + "N\'" + optionCNocontent + "\'" + "," + noinsertquestionid + "," + questionwithOption.YesOptionCUseIntegral + "," + questionwithOption.YesOptionCWinCouponCount + ")";
nooptionInsertSql += "\n";
int affectrows = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, nooptionInsertSql);
if (affectrows < 0)
{
db.Rollback();
operationsuccess = false;
return(false);
}
}
else
{
db.Rollback();
operationsuccess = false;
return(false);
}
#endregion
}
else
{
db.Rollback();
operationsuccess = false;
return(false);
}
}
if (operationsuccess)
{
db.Commit();
return(true);
}
}
catch (Exception ex)
{
db.Rollback();
return(false);
}
}
return(operationsuccess);
}
public static DataTable GetWeiXinCardList(int pkid = -1)
{
DataTable dt = null;
using (var dbhelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("ThirdPartyReadOnly")))
{
string sqlstr = string.Empty;
sqlstr = @"SELECT PKID,[supplierId],[card_id]
,[card_type]
,[deal_detail]
,[least_cost]
,[reduce_cost]
,[discount]
,[gift]
,[default_detail]
,[color]
,[title]
,[notice]
,[description]
,[quantity]
,[type]
,[begin_time]
,[end_time]
,[begin_timestamp]
,[end_timestamp]
,[fixed_term]
,[fixed_begin_term]
,[center_title]
,[center_sub_title]
,[center_url]
,[custom_url_name]
,[custom_url_sub_title]
,[custom_url]
,[promotion_url_name]
,[promotion_url_sub_title]
,[promotion_url]
,[service_phone]
,[get_limit]
,[use_limit]
,[can_share]
,[can_give_friend]
,[use_all_locations]
,[location_id_list]
,[source]
,[use_custom_code]
,[get_custom_code_mode]
,[bind_openid]
,[LastUpdateDate],PushedCount,[accept_category],
reject_category,
object_use_for,
use_condition_least_cost,
can_use_with_other_discount,
abstract,
icon_url_list,
text_image_list FROM WeiXinCard WHERE PKID=@pkid";
var cmd = new SqlCommand(sqlstr);
cmd.CommandType = CommandType.Text;
if (pkid > 0)
{
cmd.Parameters.AddWithValue("@pkid", pkid);
}
else
{
cmd.CommandText += " OR (1=1)";
cmd.Parameters.AddWithValue("@pkid", -1);
}
dt = dbhelper.ExecuteDataTable(cmd);
}
return(dt);
}
public static int SaveWeiXinCard(WeixinCardModel model)
{
using (var dbhelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("ThirdParty")))
{
var cmd = new SqlCommand(@"INSERT INTO [dbo].[WeiXinCard]([supplierId]
,[card_id]
,[card_type]
,[deal_detail]
,[least_cost]
,[reduce_cost]
,[discount]
,[gift]
,[default_detail]
,[color]
,[title]
,[notice]
,[description]
,[quantity]
,[type]
,[begin_time]
,[end_time]
,[begin_timestamp]
,[end_timestamp]
,[fixed_term]
,[fixed_begin_term]
,[center_title]
,[center_sub_title]
,[center_url]
,[custom_url_name]
,[custom_url_sub_title]
,[custom_url]
,[promotion_url_name]
,[promotion_url_sub_title]
,[promotion_url]
,[service_phone]
,[get_limit]
,[use_limit]
,[can_share]
,[can_give_friend]
,[use_all_locations]
,[location_id_list]
,[source]
,[use_custom_code]
,[get_custom_code_mode]
,[bind_openid]
,[LastUpdateDate],
[accept_category],
reject_category,
object_use_for,
use_condition_least_cost,
can_use_with_other_discount,
abstract,
icon_url_list,
text_image_list)
VALUES(@supplierId,@card_id,@card_type,@deal_detail,@least_cost,@reduce_cost,@discount,@gift,@default_detail,@color,@title,@notice,@description,@quantity,@type,@begin_time,@end_time,@begin_timestamp,@end_timestamp,@fixed_term,
@fixed_begin_term,@center_title,@center_sub_title,@center_url,@custom_url_name,@custom_url_sub_title,@custom_url,@promotion_url_name,
@promotion_url_sub_title,@promotion_url,@service_phone,@get_limit,@use_limit,@can_share,@can_give_friend,@use_all_locations,@location_id_list,@source,@use_custom_code,@get_custom_code_mode,@bind_openid,@LastUpdateDate,@accept_category,@reject_category,@object_use_for,@use_condition_least_cost,@can_use_with_other_discount,@abstract,@icon_url_list,@text_image_list) ");
cmd.CommandType = CommandType.Text;
WeixinCardBaseInfo weixinCardbaseinfo = model.total_info.base_info;
WeixinCardAdvancedInfo weixinCardadvancedinfo = model.total_info.advanced_info;
cmd.Parameters.AddWithValue("@supplierId", weixinCardbaseinfo.supplierId);
cmd.Parameters.AddWithValue("@card_id", model.card_id);
cmd.Parameters.AddWithValue("@card_type", model.card_type);
cmd.Parameters.AddWithValue("@deal_detail", model.total_info.deal_detail);
cmd.Parameters.AddWithValue("@least_cost", model.total_info.least_cost);
cmd.Parameters.AddWithValue("@reduce_cost", model.total_info.reduce_cost);
cmd.Parameters.AddWithValue("@discount", model.total_info.discount);
cmd.Parameters.AddWithValue("@gift", model.total_info.gift);
//@default_detail,@color,@title,@notice,@description,@quantity,@type,@begin_timestamp,@end_timestamp,@fixed_term,
cmd.Parameters.AddWithValue("@default_detail", model.total_info.default_detail);
cmd.Parameters.AddWithValue("@color", weixinCardbaseinfo.color);
cmd.Parameters.AddWithValue("@title", weixinCardbaseinfo.title);
cmd.Parameters.AddWithValue("@notice", weixinCardbaseinfo.notice);
cmd.Parameters.AddWithValue("@description", weixinCardbaseinfo.description);
cmd.Parameters.AddWithValue("@quantity", 0);
cmd.Parameters.AddWithValue("@type", weixinCardbaseinfo.date_info.type);
cmd.Parameters.AddWithValue("@begin_time", weixinCardbaseinfo.date_info.begin_time);
cmd.Parameters.AddWithValue("@end_time", weixinCardbaseinfo.date_info.end_time);
cmd.Parameters.AddWithValue("@begin_timestamp", (int)weixinCardbaseinfo.date_info.begin_timestamp);
cmd.Parameters.AddWithValue("@end_timestamp", (int)weixinCardbaseinfo.date_info.end_timestamp);
cmd.Parameters.AddWithValue("@fixed_term", weixinCardbaseinfo.date_info.fixed_term);
//@fixed_begin_term,@center_title,@center_sub_title,@center_url,@custom_url_name,@custom_url_sub_title,@custom_url,@promotion_url_name,
//@promotion_url_sub_title,@promotion_url,@service_phone,@get_limit
cmd.Parameters.AddWithValue("@fixed_begin_term", weixinCardbaseinfo.date_info.fixed_begin_term);
cmd.Parameters.AddWithValue("@center_title", weixinCardbaseinfo.center_title);
cmd.Parameters.AddWithValue("@center_sub_title", weixinCardbaseinfo.center_sub_title);
cmd.Parameters.AddWithValue("@center_url", weixinCardbaseinfo.center_url);
cmd.Parameters.AddWithValue("@custom_url_name", weixinCardbaseinfo.custom_url_name);
cmd.Parameters.AddWithValue("@custom_url_sub_title", weixinCardbaseinfo.custom_url_sub_title);
cmd.Parameters.AddWithValue("@custom_url", weixinCardbaseinfo.custom_url);
cmd.Parameters.AddWithValue("@promotion_url_name", weixinCardbaseinfo.promotion_url_name);
cmd.Parameters.AddWithValue("@promotion_url_sub_title", weixinCardbaseinfo.promotion_url_sub_title);
cmd.Parameters.AddWithValue("@promotion_url", weixinCardbaseinfo.promotion_url);
cmd.Parameters.AddWithValue("@service_phone", weixinCardbaseinfo.service_phone);
//@use_limit,@can_share,@can_give_friend,@use_all_locations,@location_id_list,@source,@use_custom_code,@get_custom_code_mode,@bind_openid,@LastUpdateDate
cmd.Parameters.AddWithValue("@get_limit", weixinCardbaseinfo.get_limit);
cmd.Parameters.AddWithValue("@use_limit", weixinCardbaseinfo.use_limit);
cmd.Parameters.AddWithValue("@can_share", weixinCardbaseinfo.can_share);
cmd.Parameters.AddWithValue("@can_give_friend", weixinCardbaseinfo.can_give_friend);
cmd.Parameters.AddWithValue("@use_all_locations", weixinCardbaseinfo.use_all_locations);
cmd.Parameters.AddWithValue("@location_id_list", weixinCardbaseinfo.location_id_list);
cmd.Parameters.AddWithValue("@source", weixinCardbaseinfo.source);
cmd.Parameters.AddWithValue("@use_custom_code", weixinCardbaseinfo.use_custom_code);
cmd.Parameters.AddWithValue("@get_custom_code_mode", weixinCardbaseinfo.get_custom_code_mode);
cmd.Parameters.AddWithValue("@bind_openid", weixinCardbaseinfo.bind_openid);
cmd.Parameters.AddWithValue("@LastUpdateDate", DateTime.Now);
//cmd.Parameters.AddWithValue("@use_all_locations", weixinCardbaseinfo.use_all_locations);
//cmd.Parameters.AddWithValue("@location_id_list", weixinCardbaseinfo.location_id_list);
//cmd.Parameters.AddWithValue("@source", weixinCardbaseinfo.source);
cmd.Parameters.AddWithValue("@accept_category", weixinCardadvancedinfo.use_condition.accept_category);
cmd.Parameters.AddWithValue("@reject_category", weixinCardadvancedinfo.use_condition.reject_category);
cmd.Parameters.AddWithValue("@object_use_for", weixinCardadvancedinfo.use_condition.object_use_for);
cmd.Parameters.AddWithValue("@use_condition_least_cost", weixinCardadvancedinfo.use_condition.least_cost);
cmd.Parameters.AddWithValue("@can_use_with_other_discount", weixinCardadvancedinfo.use_condition.can_use_with_other_discount);
cmd.Parameters.AddWithValue("@abstract", weixinCardadvancedinfo.abstractinfo.abstractstr);
cmd.Parameters.AddWithValue("@icon_url_list", weixinCardadvancedinfo.abstractinfo.icon_url_list?.Count() > 0?string.Join(";", weixinCardadvancedinfo.abstractinfo.icon_url_list):string.Empty);
cmd.Parameters.AddWithValue("@text_image_list", weixinCardadvancedinfo.text_image_list?.Count() > 0?JsonConvert.SerializeObject(weixinCardadvancedinfo.text_image_list):string.Empty);
return(Convert.ToInt32(dbhelper.ExecuteNonQuery(cmd)));
}
}