public List <TestPaperInfo> NewReadList(TestPaperInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "TestPaper] ");
List <TestPaperInfo> TempList = new List <TestPaperInfo>();
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, Model);
if (mssqlCondition.ToString() != string.Empty)
{
sql.Append("where " + mssqlCondition.ToString());
}
if (Model.OrderByCondition == string.Empty)
{
sql.Append(" Order by TestPaperId desc");
}
else
{
sql.Append(" Order by " + Model.OrderByCondition);
}
using (SqlDataReader dr = DbSQLHelper.ExecuteReader(sql.ToString()))
{
this.PrepareModel(dr, TempList);
}
return(TempList);
}
public List <PostPassInfo> ReadPostPassList(PostPassInfo Model, int num)
{
string sqlNumString = "*";
if (num > 0)
{
sqlNumString = "top " + num + " " + sqlNumString;
}
StringBuilder sql = new StringBuilder();
sql.Append("select " + sqlNumString + " from [" + ShopMssqlHelper.TablePrefix + "PassPost] ");
List <PostPassInfo> TempList = new List <PostPassInfo>();
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, Model);
if (mssqlCondition.ToString() != string.Empty)
{
sql.Append("where " + mssqlCondition.ToString());
sql.Append(" Order By CreateDate desc");
using (SqlDataReader dr = DbSQLHelper.ExecuteReader(sql.ToString()))
{
this.PrepareModel(dr, TempList);
}
}
return(TempList);
}
public List <WorkingPostViewInfo> SearchWorkingPostViewList(WorkingPostSearchInfo workingPostSearch)
{
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, workingPostSearch);
List <WorkingPostViewInfo> workingPostViewList = new List <WorkingPostViewInfo>();
SqlParameter[] pt = new SqlParameter[] { new SqlParameter("@condition", SqlDbType.NVarChar) };
pt[0].Value = mssqlCondition.ToString();
using (SqlDataReader dr = ShopMssqlHelper.ExecuteReader(ShopMssqlHelper.TablePrefix + "SearchWorkingPostViewList", pt))
{
while (dr.Read())
{
WorkingPostViewInfo info = new WorkingPostViewInfo();
{
info.PostId = int.Parse(dr["PostId"].ToString());
info.PostName = dr["PostName"].ToString();
info.KPIContent = dr["KPIContent"].ToString();
info.CompanyId = int.Parse(dr["CompanyId"].ToString());
}
workingPostViewList.Add(info);
}
}
return(workingPostViewList);
}
public List <TestPaperInfo> ReadList(TestPaperInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("select count(*) as TestNum,sum(point) as scorse");
if (Model.GroupCondition != "")
{
sql.Append("," + Model.GroupCondition);
}
sql.Append(" from [" + ShopMssqlHelper.TablePrefix + "TestPaper] ");
List <TestPaperInfo> TempList = new List <TestPaperInfo>();
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, Model);
if (mssqlCondition.ToString() != string.Empty)
{
sql.Append("where " + mssqlCondition.ToString());
}
if (Model.GroupCondition != "")
{
sql.Append(" Group by " + Model.GroupCondition);
}
if (Model.GroupCondition == "cateid")
{
sql.Append(" Order by Cateid ");
}
using (SqlDataReader dr = DbSQLHelper.ExecuteReader(sql.ToString()))
{
//this.PrepareModel(reader, TempList);
while (dr.Read())
{
TestPaperInfo Model1 = new TestPaperInfo();
{
Model1.TestNum = dr.GetInt32(0);
Model1.Point = Convert.ToDecimal(dr[1].ToString());
Model1.UserId = dr.GetInt32(2);
Model1.CateId = dr.GetInt32(2);
}
TempList.Add(Model1);
}
}
return(TempList);
}
public DataTable StatisticsUserStatus(UserSearchInfo userSearch)
{
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, userSearch);
SqlParameter[] pt = new SqlParameter[] { new SqlParameter("@condition", SqlDbType.NVarChar) };
pt[0].Value = mssqlCondition.ToString();
return(ShopMssqlHelper.ExecuteDataTable(ShopMssqlHelper.TablePrefix + "StatisticsUserStatus", pt));
}
public DataTable StatisticsUserCount(UserSearchInfo userSearch, DateType dateType)
{
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, userSearch);
SqlParameter[] pt = new SqlParameter[] { new SqlParameter("@condition", SqlDbType.NVarChar), new SqlParameter("@dateType", SqlDbType.Int) };
pt[0].Value = mssqlCondition.ToString();
pt[1].Value = (int)dateType;
return(ShopMssqlHelper.ExecuteDataTable(ShopMssqlHelper.TablePrefix + "StatisticsUserCount", pt));
}
public List <UserInfo> SearchReportUserList(UserSearchInfo userSearch)
{
List <UserInfo> userList = new List <UserInfo>();
StringBuilder sql = new StringBuilder();
sql.Append("select [ID],[UserName],[UserPassword],[Email],[Sex],[Introduce],[Photo],[MSN],[QQ],[Tel],[Mobile],[RegionID],[Address],[Birthday],[RegisterIP],[RegisterDate],[LastLoginIP],[LastLoginDate],[LoginTimes],[SafeCode],[FindDate],[Status],[OpenID],[GroupID],[RealName],[CompanyID],[WorkingPostID],[StudyPostID],[WorkingPostName],[Department],[IDCard],[PostStartDate],[EntryDate] from [" + ShopMssqlHelper.TablePrefix + "User] ");
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, userSearch);
if (mssqlCondition.ToString() != string.Empty)
{
sql.Append("where " + mssqlCondition.ToString());
sql.Append(" Order by [CompanyID] Desc, [Department],[WorkingPostID],[WorkingPostName],[StudyPostID],[RegisterDate]");
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
this.PrepareUserModel(reader, userList);
}
}
return(userList);
}
/// <summary>
/// 读取所有实体的列表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
//public List<T> ReadList<T>() where T : BaseModel
public List <T> ReadList <S>(S s)
{
//Type type = typeof(T);
string sqlText = string.Format("select {0} from [{1}{2}]", this.GetFieldString(type), ShopMssqlHelper.TablePrefix, tableName);
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, s);
if (!string.IsNullOrEmpty(mssqlCondition.ToString()))
{
sqlText = string.Format("{0} where {1}", sqlText, mssqlCondition.ToString());
}
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sqlText))
{
return(this.PrepareList(reader));
}
}
public List <CourseCateInfo> ReadCourseCateAllList(CourseCateInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "CourseCate] ");
List <CourseCateInfo> CourseCateList = new List <CourseCateInfo>();
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, Model);
if (mssqlCondition.ToString() != string.Empty)
{
sql.Append("where " + mssqlCondition.ToString());
sql.Append(" Order by [OrderIndex] ASC,CateId ASC");
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
this.PrepareCourseCateModel(reader, CourseCateList);
}
}
return(CourseCateList);
}
public List <CompanyPostPlanInfo> CompanyPostPlanList(CompanyPostPlanInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "CompanyPostPlan] ");
List <CompanyPostPlanInfo> CompanyPostPlanList = new List <CompanyPostPlanInfo>();
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, Model);
if (mssqlCondition.ToString() != string.Empty)
{
sql.Append("where " + mssqlCondition.ToString());
sql.Append(" Order by StartDate");
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
this.PrepareModel(reader, CompanyPostPlanList);
}
}
return(CompanyPostPlanList);
}
public List<UserApplyInfo> SearchUserApplyList(UserApplySearchInfo userApplySearch)
{
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, userApplySearch);
List<UserApplyInfo> userApplyList = new List<UserApplyInfo>();
SqlParameter[] pt = new SqlParameter[] { new SqlParameter("@condition", SqlDbType.NVarChar) };
pt[0].Value = mssqlCondition.ToString();
using (SqlDataReader reader = ShopMssqlHelper.ExecuteReader(ShopMssqlHelper.TablePrefix + "SearchUserApplyList", pt))
{
this.PrepareUserApplyModel(reader, userApplyList);
}
return userApplyList;
}
public List <QuestionInfo> ReadList(QuestionInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("select " + Model.QuestionNum + " from [" + ShopMssqlHelper.TablePrefix + "Question] ");
List <QuestionInfo> TempList = new List <QuestionInfo>();
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, Model);
if (mssqlCondition.ToString() != string.Empty)
{
sql.Append("where " + mssqlCondition.ToString());
}
sql.Append(" Order by newid()");
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
this.PrepareModel(reader, TempList);
}
return(TempList);
}
public List<GiftInfo> SearchGiftList(GiftSearchInfo giftSearch)
{
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, giftSearch);
List<GiftInfo> giftList = new List<GiftInfo>();
SqlParameter[] pt = new SqlParameter[] { new SqlParameter("@condition", SqlDbType.NVarChar) };
pt[0].Value = mssqlCondition.ToString();
using (SqlDataReader reader = ShopMssqlHelper.ExecuteReader(ShopMssqlHelper.TablePrefix + "SearchGiftList", pt))
{
this.PrepareGiftModel(reader, giftList);
}
return giftList;
}
public List <TestSettingInfo> ReadList(TestSettingInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "TestSetting] ");
List <TestSettingInfo> TempList = new List <TestSettingInfo>();
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, Model);
if (mssqlCondition.ToString() != string.Empty)
{
sql.Append("where " + mssqlCondition.ToString());
sql.Append(" Order by [Id] desc");
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
this.PrepareModel(reader, TempList);
}
}
return(TempList);
}
public DataTable StatisticsProductSale(int currentPage, int pageSize, ProductSearchInfo productSearch, ref int count, DateTime startDate, DateTime endDate)
{
ShopMssqlPagerClass class2;
List <ProductInfo> list = new List <ProductInfo>();
string str = string.Empty;
MssqlCondition condition = new MssqlCondition();
condition.Add("[AddDate]", startDate, ConditionType.MoreOrEqual);
condition.Add("[AddDate]", endDate, ConditionType.Less);
str = condition.ToString();
if (str != string.Empty)
{
str = " AND" + str;
}
class2 = new ShopMssqlPagerClass();
class2.TableName = "(SELECT ID,Name,ClassID,BrandID,IsSale, ISNULL(SellCount, 0) AS SellCount, ISNULL(SellMoney,0) AS SellMoney FROM " + ShopMssqlHelper.TablePrefix + "Product ";
class2.TableName = class2.TableName + "LEFT OUTER JOIN (SELECT ProductID, SUM(BuyCount) AS SellCount, SUM(ProductPrice * BuyCount) AS SellMoney FROM ( ";
string tableName = class2.TableName;
class2.TableName = tableName + "SELECT " + ShopMssqlHelper.TablePrefix + "OrderDetail.ProductID," + ShopMssqlHelper.TablePrefix + "OrderDetail.ProductPrice, " + ShopMssqlHelper.TablePrefix + "OrderDetail.BuyCount FROM " + ShopMssqlHelper.TablePrefix + "OrderDetail ";
tableName = class2.TableName;
class2.TableName = tableName + " INNER JOIN [" + ShopMssqlHelper.TablePrefix + "Order] ON " + ShopMssqlHelper.TablePrefix + "OrderDetail.OrderID = [" + ShopMssqlHelper.TablePrefix + "Order].ID WHERE ([" + ShopMssqlHelper.TablePrefix + "Order].OrderStatus = 6 " + str + ")) AS TEMP1 GROUP BY ProductID) AS TEMP2 ON " + ShopMssqlHelper.TablePrefix + "Product.ID = TEMP2.ProductID) As PageTable";
class2.Fields = "[ID],[Name],[ClassID],[SellCount],[SellMoney]";
class2.CurrentPage = currentPage;
class2.PageSize = pageSize;
class2.OrderField = "[ID]";
if (productSearch.ProductOrderType != string.Empty)
{
string productOrderType = productSearch.ProductOrderType;
if (productOrderType != null)
{
if (!(productOrderType == "SellCount"))
{
if (productOrderType == "SellMoney")
{
class2.OrderField = "[SellMoney],[ID]";
}
}
else
{
class2.OrderField = "[SellCount],[ID]";
}
}
}
class2.OrderType = OrderType.Desc;
this.PrepareCondition(class2.MssqlCondition, productSearch);
class2.Count = count;
count = class2.Count;
return(class2.ExecuteDataTable());
}
public List <PostInfo> ReadPostList(PostInfo Model)
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from [" + ShopMssqlHelper.TablePrefix + "Post] ");
List <PostInfo> List = new List <PostInfo>();
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, Model);
if (mssqlCondition.ToString() != string.Empty)
{
sql.Append("where " + mssqlCondition.ToString());
}
sql.Append(" Order by [CompanyID],[OrderIndex],[PostName],[PostId] desc");
using (SqlDataReader reader = DbSQLHelper.ExecuteReader(sql.ToString()))
{
this.PrepareModel(reader, List);
}
return(List);
}
public List <ProductCommentInfo> SearchProductCommentList(ProductCommentSearchInfo productCommentSearch)
{
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, productCommentSearch);
List <ProductCommentInfo> productCommentList = new List <ProductCommentInfo>();
SqlParameter[] pt = new SqlParameter[] { new SqlParameter("@condition", SqlDbType.NVarChar) };
pt[0].Value = mssqlCondition.ToString();
using (SqlDataReader reader = ShopMssqlHelper.ExecuteReader(ShopMssqlHelper.TablePrefix + "SearchProductCommentList", pt))
{
this.PrepareProductCommentModel(reader, productCommentList);
}
return(productCommentList);
}
public List <KPITempletInfo> SearchKPITempletList(KPITempletInfo kpiTemplet)
{
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, kpiTemplet);
List <KPITempletInfo> kpiTempletList = new List <KPITempletInfo>();
SqlParameter[] pt = new SqlParameter[] { new SqlParameter("@condition", SqlDbType.NVarChar) };
pt[0].Value = mssqlCondition.ToString();
using (SqlDataReader dr = ShopMssqlHelper.ExecuteReader(ShopMssqlHelper.TablePrefix + "SearchKPITempletList", pt))
{
this.PrepareModel(dr, kpiTempletList);
}
return(kpiTempletList);
}
public List <ReceiveMessageInfo> SearchReceiveMessageList(ReceiveMessageSearchInfo receiveMessageSearch)
{
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, receiveMessageSearch);
List <ReceiveMessageInfo> receiveMessageList = new List <ReceiveMessageInfo>();
SqlParameter[] pt = new SqlParameter[] { new SqlParameter("@condition", SqlDbType.NVarChar) };
pt[0].Value = mssqlCondition.ToString();
using (SqlDataReader reader = ShopMssqlHelper.ExecuteReader(ShopMssqlHelper.TablePrefix + "SearchReceiveMessageList", pt))
{
this.PrepareReceiveMessageModel(reader, receiveMessageList);
}
return(receiveMessageList);
}
public List <EvaluateNameInfo> SearchEvaluateNameList(EvaluateNameInfo evaluateName)
{
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, evaluateName);
List <EvaluateNameInfo> evaluateNameList = new List <EvaluateNameInfo>();
SqlParameter[] pt = new SqlParameter[] { new SqlParameter("@condition", SqlDbType.NVarChar) };
pt[0].Value = mssqlCondition.ToString();
using (SqlDataReader dr = ShopMssqlHelper.ExecuteReader(ShopMssqlHelper.TablePrefix + "SearchEvaluateNameList", pt))
{
this.PrepareModel(dr, evaluateNameList);
}
return(evaluateNameList);
}
public List <CouponInfo> SearchCouponList(CouponSearchInfo couponSearch)
{
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, couponSearch);
List <CouponInfo> couponList = new List <CouponInfo>();
SqlParameter[] pt = new SqlParameter[] { new SqlParameter("@condition", SqlDbType.NVarChar) };
pt[0].Value = mssqlCondition.ToString();
using (SqlDataReader reader = ShopMssqlHelper.ExecuteReader(ShopMssqlHelper.TablePrefix + "SearchCouponList", pt))
{
this.PrepareCouponModel(reader, couponList);
}
return(couponList);
}
public List <EmailSendRecordInfo> SearchEmailSendRecordList(EmailSendRecordSearchInfo emailSendRecordSearch)
{
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, emailSendRecordSearch);
List <EmailSendRecordInfo> emailSendRecordList = new List <EmailSendRecordInfo>();
SqlParameter[] pt = new SqlParameter[] { new SqlParameter("@condition", SqlDbType.NVarChar) };
pt[0].Value = mssqlCondition.ToString();
using (SqlDataReader reader = ShopMssqlHelper.ExecuteReader(ShopMssqlHelper.TablePrefix + "SearchEmailSendRecordList", pt))
{
this.PrepareEmailSendRecordModel(reader, emailSendRecordList);
}
return(emailSendRecordList);
}
public List <PostKPISettingInfo> SearchPostKPISettingList(PostKPISettingInfo info)
{
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, info);
List <PostKPISettingInfo> postKPISettingList = new List <PostKPISettingInfo>();
SqlParameter[] pt = new SqlParameter[] { new SqlParameter("@condition", SqlDbType.NVarChar) };
pt[0].Value = mssqlCondition.ToString();
using (SqlDataReader dr = ShopMssqlHelper.ExecuteReader(ShopMssqlHelper.TablePrefix + "SearchPostKPISettingList", pt))
{
this.PrepareModel(dr, postKPISettingList);
}
return(postKPISettingList);
}
public List <WorkingPostInfo> SearchWorkingPostList(WorkingPostSearchInfo workingPostSearch)
{
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, workingPostSearch);
List <WorkingPostInfo> workingPostList = new List <WorkingPostInfo>();
SqlParameter[] pt = new SqlParameter[] { new SqlParameter("@condition", SqlDbType.NVarChar) };
pt[0].Value = mssqlCondition.ToString();
using (SqlDataReader dr = ShopMssqlHelper.ExecuteReader(ShopMssqlHelper.TablePrefix + "SearchWorkingPostList", pt))
{
this.PrepareModel(dr, workingPostList);
}
return(workingPostList);
}
public DataTable StatisticsProductSale(int currentPage, int pageSize, ProductSearchInfo productSearch, ref int count, DateTime startDate, DateTime endDate)
{
List <ProductInfo> productList = new List <ProductInfo>();
string orderCondition = string.Empty;
MssqlCondition orderCh = new MssqlCondition();
orderCh.Add("[AddDate]", startDate, ConditionType.MoreOrEqual);
orderCh.Add("[AddDate]", endDate, ConditionType.Less);
orderCondition = orderCh.ToString();
if (orderCondition != string.Empty)
{
orderCondition = " AND " + orderCondition;
}
ShopMssqlPagerClass pc = new ShopMssqlPagerClass();
pc.TableName = "(SELECT Id,Name,ClassId,BrandId,IsSale, ISNULL(SellCount, 0) AS SellCount, ISNULL(SellMoney,0) AS SellMoney FROM [Product] ";
pc.TableName += "LEFT OUTER JOIN (SELECT ProductId, SUM(BuyCount) AS SellCount, SUM(ProductPrice * BuyCount) AS SellMoney FROM ( ";
pc.TableName += "SELECT OrderDetail.ProductId,OrderDetail.ProductPrice, OrderDetail.BuyCount FROM [OrderDetail] ";
pc.TableName += " INNER JOIN [Order] ON [OrderDetail].OrderId = [Order].Id WHERE ([Order].OrderStatus = 6 " + orderCondition + ")) AS TEMP1 GROUP BY ProductId) AS TEMP2 ON [Product].Id = TEMP2.ProductId) As PageTable";
pc.Fields = "[Id],[Name],[ClassId],[SellCount],[SellMoney]";
pc.CurrentPage = currentPage;
pc.PageSize = pageSize;
pc.OrderField = "[Id]";
if (productSearch.ProductOrderType != string.Empty)
{
switch (productSearch.ProductOrderType)
{
case "SellCount":
pc.OrderField = "[SellCount],[Id]";
break;
case "SellMoney":
pc.OrderField = "[SellMoney],[Id]";
break;
default:
break;
}
}
pc.OrderType = OrderType.Desc;
pc.MssqlCondition = PrepareCondition(productSearch);
pc.Count = count;
count = pc.Count;
return(pc.ExecuteDataTable());
}
public List <TagsInfo> SearchTagsList(TagsSearchInfo tagsSearch)
{
MssqlCondition condition = new MssqlCondition();
condition.Add("[Word]", tagsSearch.Word, ConditionType.Like);
condition.Add("[IsTop]", tagsSearch.IsTop, ConditionType.Equal);
condition.Add("[ProductID]", tagsSearch.ProductID, ConditionType.Equal);
condition.Add("[UserID]", tagsSearch.UserID, ConditionType.Equal);
List <TagsInfo> tagsList = new List <TagsInfo>();
SqlParameter[] pt = new SqlParameter[] { new SqlParameter("@condition", SqlDbType.NVarChar) };
pt[0].Value = condition.ToString();
using (SqlDataReader reader = ShopMssqlHelper.ExecuteReader(ShopMssqlHelper.TablePrefix + "SearchTagsList", pt))
{
this.PrepareTagsModel(reader, tagsList);
}
return(tagsList);
}
public int SearchProductNum(ProductSearchInfo productSearch)
{
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, productSearch);
List <ProductInfo> productList = new List <ProductInfo>();
SqlParameter[] pt = new SqlParameter[] { new SqlParameter("@condition", SqlDbType.NVarChar) };
pt[0].Value = mssqlCondition.ToString();
using (SqlDataReader reader = ShopMssqlHelper.ExecuteReader(ShopMssqlHelper.TablePrefix + "SearchProductList", pt))
{
if (reader.Read())
{
return(reader.GetInt32(0));
}
}
return(0);
}
public List <VoteItemInfo> ReadVoteItemList(VoteItemSearchInfo voteItemSearch)
{
MssqlCondition mssqlCondition = new MssqlCondition();
PrepareCondition(mssqlCondition, voteItemSearch);
List <VoteItemInfo> VoteItemList = new List <VoteItemInfo>();
SqlParameter[] parameters =
{
new SqlParameter("@condition", SqlDbType.NVarChar)
};
parameters[0].Value = mssqlCondition.ToString() + " ORDER BY [OrderID] desc,[ID] desc";
using (SqlDataReader dr = ShopMssqlHelper.ExecuteReader(ShopMssqlHelper.TablePrefix + "SearchVoteItemList", parameters))
{
PrepareVoteItemModel(dr, VoteItemList);
}
return(VoteItemList);
}
public List <KPIEvaluateReportInfo> KPIEvaluateReportList(KPIEvaluateSearchInfo kpiEvaluateSearch)
{
List <KPIEvaluateReportInfo> reportList = new List <KPIEvaluateReportInfo>();
MssqlCondition mssqlCondition = new MssqlCondition();
this.PrepareCondition(mssqlCondition, kpiEvaluateSearch);
SqlParameter[] pt = new SqlParameter[] { new SqlParameter("@condition", SqlDbType.NVarChar) };
pt[0].Value = mssqlCondition.ToString();
using (SqlDataReader dr = ShopMssqlHelper.ExecuteReader(ShopMssqlHelper.TablePrefix + "NewKPIEvaluateReport", pt))
{
while (dr.Read())
{
KPIEvaluateReportInfo info = new KPIEvaluateReportInfo();
{
info.EvaluateNameId = int.Parse(dr["EvaluateNameId"].ToString());
//if (dr["UserId"].ToString() == null)
// info.EvaluationName = string.Empty;
//else
info.EvaluateName = dr["EvaluateName"].ToString();
info.UserId = int.Parse(dr["UserId"].ToString());
info.UserName = dr["UserName"].ToString();
info.PostId = int.Parse(dr["PostId"].ToString());
info.PostName = dr["PostName"].ToString();
info.CompleteNum = int.Parse(dr["CompleteNum"].ToString());
if (dr["WorkingPostKPINum"] == null)
{
info.WorkingPostKPINum = 0;
info.Rate = 0;
}
else
{
info.WorkingPostKPINum = int.Parse(dr["WorkingPostKPINum"].ToString());
info.Rate = info.CompleteNum * 100 / info.WorkingPostKPINum;
}
info.TempNum = int.Parse(dr["TempKPINum"].ToString());
info.FixedNum = int.Parse(dr["FixKPINum"].ToString());
info.TotalScore = int.Parse(dr["TotalScore"].ToString());
}
reportList.Add(info);
}
}
return(reportList);
}
/// <summary>
/// 用户消费分析
/// </summary>
public DataTable StatisticsUserConsume(int currentPage, int pageSize, UserSearchInfo userSearch, ref int count, string orderField, DateTime startDate, DateTime endDate)
{
List <UserInfo> userList = new List <UserInfo>();
string orderCondition = string.Empty;
MssqlCondition orderCh = new MssqlCondition();
orderCh.Add("[AddDate]", startDate, ConditionType.MoreOrEqual);
orderCh.Add("[AddDate]", endDate, ConditionType.Less);
orderCondition = orderCh.ToString();
if (!string.IsNullOrEmpty(orderCondition))
{
orderCondition = " AND " + orderCondition;
}
ShopMssqlPagerClass pc = new ShopMssqlPagerClass();
pc.TableName = "(SELECT Id,UserName,Sex,ISNULL(OrderCount,0) AS OrderCount,ISNULL(OrderMoney,0) AS OrderMoney ";
pc.TableName += "FROM Usr ";
pc.TableName += "LEFT OUTER JOIN (SELECT UserId, COUNT(*) AS OrderCount,Sum(ProductMoney-FavorableMoney+ShippingMoney+OtherMoney-CouponMoney) AS OrderMoney FROM [Order] WHERE OrderStatus=6 " + orderCondition + " GROUP BY UserId) AS TEMP3 ON Usr.Id = TEMP3.UserId where Usr.status = 2) AS PageTable";
pc.Fields = "[Id],[UserName],[Sex],[OrderCount],[OrderMoney]";
pc.CurrentPage = currentPage;
pc.PageSize = pageSize;
switch (orderField)
{
case "OrderCount":
pc.OrderField = "[OrderCount],[Id]";
break;
case "OrderMoney":
pc.OrderField = "[OrderMoney],[Id]";
break;
default:
pc.OrderField = "[OrderCount],[Id]";
break;
}
pc.OrderType = OrderType.Desc;
pc.MssqlCondition = PrepareCondition(userSearch);
pc.Count = count;
count = pc.Count;
return(pc.ExecuteDataTable());
}