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