public RefundInfo GetByOrderIdAndProductID(string orderId, int productid, string skuid, int orderitemid)
{
RefundInfo info = null;
string str = string.Empty;
if (orderitemid > 0)
{
str = "select top 1 * from Hishop_OrderReturns where OrderId=@OrderId and OrderItemID=@OrderItemID";
}
else if (!string.IsNullOrEmpty(skuid))
{
str = "select top 1 * from Hishop_OrderReturns where OrderId=@OrderId and SkuID=@SkuID";
}
else if (productid > 0)
{
str = "select top 1 * from Hishop_OrderReturns where OrderId=@OrderId and ProductId=" + productid;
}
if (string.IsNullOrEmpty(str))
{
return(info);
}
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(str);
this.database.AddInParameter(sqlStringCommand, "OrderId", DbType.String, orderId);
this.database.AddInParameter(sqlStringCommand, "SkuID", DbType.String, skuid);
this.database.AddInParameter(sqlStringCommand, "OrderItemID", DbType.String, orderitemid);
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToModel <RefundInfo>(reader));
}
}
public ReturnsInfo GetReturnsInfo(int returnsId)
{
ReturnsInfo returnsInfo = null;
//Username,EmailAddress,RealName,ZipCode,CellPhone
DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT A.ReturnsId,A.OrderId,A.ApplyForTime,A.RefundType,A.RefundMoney,A.Comments,A.HandleStatus,A.HandleTime,A.AdminRemark,A.Operator,A.ReceiveTime,A1.Username,A1.EmailAddress,A1.RealName,A1.ZipCode,A1.CellPhone,A.LogisticsCompany,A.LogisticsId,A.ExpressFee,A.CustomsClearanceFee,A.FeeAffiliation FROM Ecshop_OrderReturns as A left join Ecshop_Orders as A1 on A.orderId=A1.orderId where returnsId=@returnsId;SELECT o.Id,o.AId,o.SkuId,o.ProductId,o.ApplyType,o.Quantity,o.ItemAdjustedPrice,o.ItemDescription,o.ThumbnailsUrl,o.Weight,o.SKUContent,o.PromotionId,o.PromotionName,o.TaxRate,o.TemplateId,o.storeId,o.SupplierId,o.DeductFee,s.SupplierName FROM Ecshop_OrderAppFormItems as o left JOIN Ecshop_Supplier as s ON o.SupplierId=s.SupplierId where o.AId=@returnsId and o.ApplyType=1");
this.database.AddInParameter(sqlStringCommand, "returnsId", DbType.Int32, returnsId);
using (IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
returnsInfo = ReaderConvert.ReaderToModel <ReturnsInfo>(dataReader);
if (returnsInfo != null)
{
returnsInfo.ReturnsLineItem = new List <OrderAppFormItems>();
}
dataReader.NextResult();
OrderAppFormItems appFormItem = null;
while (1 == 1)
{
appFormItem = ReaderConvert.ReaderToModel <OrderAppFormItems>(dataReader);
if (appFormItem != null && returnsInfo.ReturnsLineItem != null)
{
if (appFormItem.SupplierName == null)
{
appFormItem.SupplierName = "";
}
returnsInfo.ReturnsLineItem.Add(appFormItem);
}
if (appFormItem == null)
{
break;
}
}
}
return(returnsInfo);
}
public IEnumerable <GameInfo> GetLists(GameSearch search)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("SELECT * FROM [vw_Hishop_PromotionGame] where 1=1 ");
if (!string.IsNullOrEmpty(search.Status))
{
stringBuilder.AppendFormat(" and [Status]={0}", search.Status);
}
if (search.BeginTime.HasValue)
{
stringBuilder.AppendFormat(" and [BeginTime]>='{0}'", search.BeginTime);
}
if (search.EndTime.HasValue)
{
stringBuilder.AppendFormat(" and [EndTime]<'{0}'", search.EndTime);
}
System.Data.Common.DbCommand sqlStringCommand = this._database.GetSqlStringCommand(stringBuilder.ToString());
IEnumerable <GameInfo> result;
using (System.Data.IDataReader dataReader = this._database.ExecuteReader(sqlStringCommand))
{
result = ReaderConvert.ReaderToList <GameInfo>(dataReader);
}
return(result);
}
public string getOrderDeliveryState(string orderid)
{
string result = "配送";
string selectSql = string.Format(@"Select * From Store_DeliveryOrder Where orderid='{0}'", orderid);
DbCommand command = this.database.GetSqlStringCommand(selectSql);
DeliveryOrderInfo info = new DeliveryOrderInfo();
using (IDataReader reader = this.database.ExecuteReader(command))
{
info = ReaderConvert.ReaderToModel <DeliveryOrderInfo>(reader);
}
if (info == null)
{
return(result);
}
if (info.State == 1)
{
result = "送达";
}
else if (info.State == 2)
{
result = "结束";
}
return(result);
}
public IList <RecommendInfo> GetAllRecommendNormal(ClientType client)
{
// DbCommand sqlStringCommand = this.database.GetSqlStringCommand(string.Format(@"SELECT * FROM
// (
// select
// ROW_NUMBER() OVER(ORDER BY DisplaySequence) rid,*
// from Ecshop_Banner where type=12 AND Client=1
// ) a WHERE rid!=1", (int)client));
//若不是供应商，则supplierid=0 或 null
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(string.Format(@"SELECT * FROM
(
select
ROW_NUMBER() OVER(ORDER BY DisplaySequence) rid,*
from Ecshop_Banner where type=12 AND Client=1 and (SupplierId=0 or SupplierId is null)
) a WHERE rid!=1", (int)client));
IList <RecommendInfo> result;
using (IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
result = ReaderConvert.ReaderToList <RecommendInfo>(dataReader);
}
return(result);
}
public bool ChangeAppRunImg(List <AppRunImgInfo> appRunImgInfo)
{
List <AppRunImgInfo> list = null;
DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Ecshop_AppRunImg");
using (IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
list = ReaderConvert.ReaderToList <AppRunImgInfo>(dataReader) as List <AppRunImgInfo>;
}
System.Text.StringBuilder sql = new System.Text.StringBuilder();
int i = 0;
foreach (AppRunImgInfo r in appRunImgInfo)
{
if (list.Exists(a => a.phoneType == r.phoneType))
{
sql.AppendFormat("UPDATE Ecshop_AppRunImg SET imgSrc=@imgSrc{0} WHERE phoneType=@phoneType{0};", i);
}
else
{
sql.AppendFormat("INSERT INTO Ecshop_AppRunImg (imgSrc,phoneType) VALUES(@imgSrc{0},@phoneType{0});", i);
}
i++;
}
i = 0;
sqlStringCommand = this.database.GetSqlStringCommand(sql.ToString());
foreach (AppRunImgInfo r in appRunImgInfo)
{
this.database.AddInParameter(sqlStringCommand, "imgSrc" + i, DbType.String, r.imgSrc);
this.database.AddInParameter(sqlStringCommand, "phoneType" + i, DbType.String, r.phoneType);
i++;
}
return(this.database.ExecuteNonQuery(sqlStringCommand) > 0);
}
public IList <TplCfgInfo> GetTplCfgInfoList(ClientType client, int type, int pageSize, int pageIndex, ref int total)//分页
{
IList <TplCfgInfo> result = null;
// DbCommand sqlStringCommand = this.database.GetSqlStringCommand(@"SELECT COUNT(1) as total from Ecshop_Banner where [type]=@type AND Client=@client;SELECT * from (SELECT *,ROW_NUMBER() OVER(ORDER BY DisplaySequence ASC) as no
//from Ecshop_Banner where [type]=@type AND Client=@client) as AA WHERE AA.no >@pageIndex*@pageSize AND AA.no<=(@pageIndex+1)*@pageSize;");
//若不是供应商，则supplierid=0 或 null
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(@"SELECT COUNT(1) as total from Ecshop_Banner where [type]=@type AND Client=@client and (SupplierId=0 or SupplierId is null);SELECT * from (SELECT *,ROW_NUMBER() OVER(ORDER BY DisplaySequence ASC) as no
from Ecshop_Banner where [type]=@type AND Client=@client and (SupplierId=0 or SupplierId is null)) as AA WHERE AA.no >@pageIndex*@pageSize AND AA.no<=(@pageIndex+1)*@pageSize;");
this.database.AddInParameter(sqlStringCommand, "type", DbType.Int32, type);
this.database.AddInParameter(sqlStringCommand, "client", DbType.Int32, (int)client);
this.database.AddInParameter(sqlStringCommand, "pageIndex", DbType.Int32, pageIndex);
this.database.AddInParameter(sqlStringCommand, "pageSize", DbType.Int32, pageSize);
using (IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
while (dataReader.Read())
{
object obj = dataReader["total"];
if (obj != DBNull.Value)
{
total = Convert.ToInt32(dataReader["total"]);
}
}
dataReader.NextResult();
result = ReaderConvert.ReaderToList <TplCfgInfo>(dataReader);
}
return(result);
}
public OneyuanTaoParticipantInfo GetAddParticipant(int UserId, [Optional, DefaultParameterValue("")] string Pid, [Optional, DefaultParameterValue("")] string payNum)
{
string query = "";
if (UserId > 0)
{
query = "select top 1 * from Vshop_OneyuanTao_ParticipantMember where UserId=@UserId";
}
else if (Pid != "")
{
query = "select top 1 * from Vshop_OneyuanTao_ParticipantMember where Pid=@Pid";
}
else if (payNum != "")
{
query = "select top 1 * from Vshop_OneyuanTao_ParticipantMember where PayNum=@PayNum";
}
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(query);
this.database.AddInParameter(sqlStringCommand, "UserId", DbType.Int32, UserId);
this.database.AddInParameter(sqlStringCommand, "Pid", DbType.String, Pid);
this.database.AddInParameter(sqlStringCommand, "PayNum", DbType.String, payNum);
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToModel <OneyuanTaoParticipantInfo>(reader));
}
}
public IList <OneyuanTaoParticipantInfo> GetParticipantList(string ActivityId, [Optional] int[] UserIds, [Optional] string[] PIds)
{
string query = "";
if (!string.IsNullOrEmpty(ActivityId))
{
query = string.Format("select * from Vshop_OneyuanTao_ParticipantMember where ActivityId='{0}' ", ActivityId);
}
else if (UserIds != null)
{
query = "select * from Vshop_OneyuanTao_ParticipantMember where UserIds in(" + string.Join <int>(",", UserIds) + ")";
}
else if (PIds != null)
{
query = "select * from Vshop_OneyuanTao_ParticipantMember where Pid in(" + string.Join(",", PIds) + ")";
}
if (query == "")
{
return(null);
}
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(query);
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToList <OneyuanTaoParticipantInfo>(reader));
}
}
public RefundInfo GetByOrderIdAndProductID(string orderId, int productid, string skuid, int orderitemid)
{
RefundInfo result = null;
string text = string.Empty;
if (orderitemid > 0)
{
text = "select top 1 * from Hishop_OrderReturns where OrderId=@OrderId and OrderItemID=@OrderItemID";
}
else if (!string.IsNullOrEmpty(skuid))
{
text = "select top 1 * from Hishop_OrderReturns where OrderId=@OrderId and SkuID=@SkuID";
}
else if (productid > 0)
{
text = "select top 1 * from Hishop_OrderReturns where OrderId=@OrderId and ProductId=" + productid;
}
if (!string.IsNullOrEmpty(text))
{
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(text);
this.database.AddInParameter(sqlStringCommand, "OrderId", System.Data.DbType.String, orderId);
this.database.AddInParameter(sqlStringCommand, "SkuID", System.Data.DbType.String, skuid);
this.database.AddInParameter(sqlStringCommand, "OrderItemID", System.Data.DbType.String, orderitemid);
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
result = ReaderConvert.ReaderToModel <RefundInfo>(dataReader);
}
}
return(result);
}
public IList <NavigateInfo> GetAllNavigate()
{
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(new StringBuilder("select * from Hishop_Banner where type=2 ORDER BY DisplaySequence ASC").ToString());
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToList <NavigateInfo>(reader));
}
}
public IList <MemberInfo> GetMemdersByOpenIds(string openids)
{
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(string.Format("SELECT * FROM aspnet_Members where openid IN ({0})", openids));
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToList <MemberInfo>(reader));
}
}
public IList <MemberInfo> GetMemdersByCardNumbers(string cards)
{
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(string.Format("SELECT * FROM aspnet_Members WHERE VipCardNumber IN ({0})", cards));
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToList <MemberInfo>(reader));
}
}
public IList <RoleQx> GetRoleQx(int roleid)
{
DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM aspnet_RolesQx where RoleId = " + roleid);
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToList <RoleQx>(reader));
}
}
/// <summary>
/// 根据主键查询数据实体
/// </summary>
public static CardTypeInfoEntity LoadEntity(Guid ID)
{
string selectSql = string.Format(@"Select * From CardTypeInfo Where ID='{0}'", ID);
using (IDataReader reader = DataAccessFactory.GetDataProvider().GetReader(selectSql))
{
return(ReaderConvert.ReaderToModel <CardTypeInfoEntity>(reader));
}
}
public IList <ActivitiesInfo> GetActivitiesInfo(string ActivitiesId)
{
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(string.Format("SELECT * FROM Hishop_Activities WHERE ActivitiesId={0}", ActivitiesId));
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToList <ActivitiesInfo>(reader));
}
}
public IList <SpecifyRegionGroup> GetSpecifyRegionGroups(int templateId)
{
DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT g.*,r.RegionIds FROM Hishop_FreightTemplate_SpecifyRegionGroups g LEFT JOIN vw_Hishop_FreightTemplate_SpecifyRegions r on (g.GroupId=r.GroupId) where g.TemplateId =" + templateId);
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToList <SpecifyRegionGroup>(reader));
}
}
public Channel GetChannelListGrade(Guid ChannelId)
{
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(string.Format("SELECT * FROM Hishop_ChannelList where Id='{0}'", ChannelId));
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToModel <Channel>(reader));
}
}
public IList <MenuInfo> GetTopMenus()
{
DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM vshop_Menu WHERE ParentMenuId = 0 ORDER BY DisplaySequence ASC");
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToList <MenuInfo>(reader));
}
}
public IList <ProductTypeInfo> GetProductTypes()
{
DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_ProductTypes");
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToList <ProductTypeInfo>(reader));
}
}
public IList <TopicInfo> GetTopics()
{
DbCommand sqlStringCommand = this.database.GetSqlStringCommand("select * from Vshop_Topics order by DisplaySequence asc,topicid desc");
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToList <TopicInfo>(reader));
}
}
public IList <MenuInfo> GetTopMenus()
{
DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM VShop_NavMenu WHERE ParentMenuId = 0");
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToList <MenuInfo>(reader));
}
}
public List <GameWinningPool> GetWinningPoolList(int gameId)
{
DbCommand sqlStringCommand = this._database.GetSqlStringCommand(("SELECT * FROM Hishop_PromotionWinningPool where IsReceive=0 and GameId=" + gameId).ToString());
using (IDataReader reader = this._database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToList <GameWinningPool>(reader).ToList <GameWinningPool>());
}
}
/// <summary>
/// 根据主键查询数据实体
/// </summary>
public static aspnet_ManagersEntity LoadEntity(int ID, DbServers.DbServerName currDbName = DbServers.DbServerName.LatestDB)
{
string selectSql = string.Format(@"Select * From aspnet_Managers Where UserId={0}", ID);
using (IDataReader reader = DataAccessFactory.GetDataProvider(DbServers.GetCurrentDB(currDbName)).GetReader(selectSql))
{
return(ReaderConvert.ReaderToModel <aspnet_ManagersEntity>(reader));
}
}
public IList <DistributorGradeInfo> GetDistributorGradeInfos()
{
DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM aspnet_DistributorGrade");
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToList <DistributorGradeInfo>(reader));
}
}
public IList <FreightTemplate> GetFreightTemplates()
{
DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_FreightTemplate_Templates order by TemplateId desc");
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToList <FreightTemplate>(reader));
}
}
public ScanInfos GetScanInfosByScenceId(string Sceneid)
{
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(string.Format("SELECT * FROM Vshop_ScanSceneInfos WHERE Sceneid='{0}'", Sceneid));
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToModel <ScanInfos>(reader));
}
}
public IList <ReplyKeyInfo> GetTopReplyInfos(int Type)
{
DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Weibo_ReplyKeys where Type=" + Type + " ORDER BY id ASC");
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToList <ReplyKeyInfo>(reader));
}
}
public IList <ProductInfo> GetProductBaseInfo(IEnumerable <int> productIds)
{
string query = this.getProductBaseInfoSelectSQL(productIds);
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(query);
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToList <ProductInfo>(reader));
}
}
public RefundInfo GetByOrderId(string orderId)
{
string query = "select * from Hishop_OrderRefund where OrderId='" + orderId + "'";
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(query);
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(ReaderConvert.ReaderToModel <RefundInfo>(reader));
}
}
