public VWCGOrderEntity GetVWCGOrderByCode(long cgcode, int memid)
{
VWCGOrderEntity entity = new VWCGOrderEntity();
string where = " where Code=@Code ";
if (memid != -1)
{
where += "and CGMemId=@CGMemId ";
}
string sql = @" SELECT a.[Id],a.[Code],a.[Title],a.[SourceCode],a.[CreateTime],a.[TransFee],a.[Status], a.[SendTime],a.[Remark],
b.[ReceiptName]
,b.[ReceiptProvince]
,b.[ReceiptCity]
,b.[ReceiptTown]
,b.[ReceiptAddress]
,b.[ReceiptPhone]
FROM
dbo.[CGOrder] a WITH(NOLOCK) inner join dbo.[CGOrderAddress] b WITH(NOLOCK)
on a.Code=b.CGOrderCode " + where;
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@Code", DbType.Int64, cgcode);
if (memid != -1)
{
db.AddInParameter(cmd, "@CGMemId", DbType.Int32, memid);
}
using (IDataReader reader = db.ExecuteReader(cmd))
{
if (reader.Read())
{
entity.Id = StringUtils.GetDbInt(reader["Id"]);
entity.Code = StringUtils.GetDbLong(reader["Code"]);
entity.Title = StringUtils.GetDbString(reader["Title"]);
entity.SourceCode = StringUtils.GetDbLong(reader["SourceCode"]);
entity.CreateTime = StringUtils.GetDbDateTime(reader["CreateTime"]);
entity.TransFee = StringUtils.GetDbDecimal(reader["TransFee"]);
entity.Status = StringUtils.GetDbInt(reader["Status"]);
entity.SendTime = StringUtils.GetDbDateTime(reader["SendTime"]);
entity.Remark = StringUtils.GetDbString(reader["Remark"]);
entity.ReceiptName = StringUtils.GetDbString(reader["ReceiptName"]);
entity.ReceiptProvince = StringUtils.GetDbInt(reader["ReceiptProvince"]);
entity.ReceiptCity = StringUtils.GetDbInt(reader["ReceiptCity"]);
entity.ReceiptTown = StringUtils.GetDbInt(reader["ReceiptTown"]);
entity.ReceiptAddress = StringUtils.GetDbString(reader["ReceiptAddress"]);
entity.ReceiptPhone = StringUtils.GetDbString(reader["ReceiptPhone"]);
}
}
return(entity);
}
public IList <VWCGOrderEntity> GetVWCGOrderBySourceCode(long sourceCode)
{
string sql = @"SELECT a.[Id],a.[Code],a.ExpressCom,a.[Title],a.[SourceCode],a.[CreateTime], [TransFee],a.[Status], a.[SendTime],a.[Remark],
b.[ReceiptName]
,b.[ReceiptProvince]
,b.[ReceiptCity]
,b.[ReceiptTown]
,b.[ReceiptAddress]
,b.[ReceiptPhone]
FROM
dbo.[CGOrder] a WITH(NOLOCK) inner join dbo.[CGOrderAddress] b WITH(NOLOCK)
on a.Code=b.CGOrderCode
WHERE [SourceCode]=@SourceCode" ;
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@SourceCode", DbType.Int64, sourceCode);
IList <VWCGOrderEntity> entityList = new List <VWCGOrderEntity>();
using (IDataReader reader = db.ExecuteReader(cmd))
{
while (reader.Read())
{
VWCGOrderEntity entity = new VWCGOrderEntity();
entity.Id = StringUtils.GetDbInt(reader["Id"]);
entity.Code = StringUtils.GetDbLong(reader["Code"]);
entity.Title = StringUtils.GetDbString(reader["Title"]);
entity.SourceCode = StringUtils.GetDbLong(reader["SourceCode"]);
entity.CreateTime = StringUtils.GetDbDateTime(reader["CreateTime"]);
entity.TransFee = StringUtils.GetDbDecimal(reader["TransFee"]);
entity.Status = StringUtils.GetDbInt(reader["Status"]);
entity.SendTime = StringUtils.GetDbDateTime(reader["SendTime"]);
entity.Remark = StringUtils.GetDbString(reader["Remark"]);
entity.ReceiptName = StringUtils.GetDbString(reader["ReceiptName"]);
entity.ReceiptProvince = StringUtils.GetDbInt(reader["ReceiptProvince"]);
entity.ReceiptCity = StringUtils.GetDbInt(reader["ReceiptCity"]);
entity.ReceiptTown = StringUtils.GetDbInt(reader["ReceiptTown"]);
entity.ReceiptAddress = StringUtils.GetDbString(reader["ReceiptAddress"]);
entity.ReceiptPhone = StringUtils.GetDbString(reader["ReceiptPhone"]);
entityList.Add(entity);
}
}
return(entityList);
}
/// <summary>
/// 读取记录列表。
/// </summary>
/// <param name="db">数据库操作对象</param>
/// <param name="columns">需要返回的列，不提供任何列名时默认将返回所有列</param>
public IList <VWCGOrderEntity> GetCGOrderTakeList(int pagesize, int pageindex, ref int recordCount, string keyword, int memid)
{
string where = " where 1=1";
if (!string.IsNullOrEmpty(keyword))
{
where += " And a.Title like @keyword ";
}
if (memid != -1)
{
where += " And b.CGMemId=@CGMemId ";
}
string sql = @"SELECT [Id],[Code],[Title],[SourceCode],[CreateTime],[ReceiptName],[ReceiptProvince],[ReceiptCity],[ReceiptTown],[ReceiptAddress],[ReceiptPhone], [Status], [SendTime],[Remark]
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY a.Id desc) AS ROWNUMBER,
a.[Id],a.[Code],a.[Title],a.[SourceCode],a.[CreateTime],c.[ReceiptName],c.[ReceiptProvince],c.[ReceiptCity],c.[ReceiptTown],c.[ReceiptAddress],
c.[ReceiptPhone], a.[Status],a.[SendTime],a.[Remark] from dbo.[CGOrder] a WITH(NOLOCK) inner join dbo.[CGOrderAddress] c WITH(NOLOCK)
on a.Code=c.CGOrderCode
inner join dbo.[CGOrderTake] b WITH(NOLOCK) on a.Code=b.CGOrderCode
" + where + @") as temp
where rownumber BETWEEN ((@PageIndex - 1) * @PageSize + 1) AND @PageIndex * @PageSize" ;
string sql2 = @"Select count(1) from dbo.[CGOrder] a WITH(NOLOCK) inner join dbo.[CGOrderTake] b WITH(NOLOCK) on a.Code=b.CGOrderCode " + where;
IList <VWCGOrderEntity> entityList = new List <VWCGOrderEntity>();
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@PageIndex", DbType.Int32, pageindex);
db.AddInParameter(cmd, "@PageSize", DbType.Int32, pagesize);
if (!string.IsNullOrEmpty(keyword))
{
db.AddInParameter(cmd, "@keyword", DbType.String, "%" + keyword + "%");
}
if (memid != -1)
{
db.AddInParameter(cmd, "@CGMemId", DbType.Int32, memid);
}
using (IDataReader reader = db.ExecuteReader(cmd))
{
while (reader.Read())
{
VWCGOrderEntity entity = new VWCGOrderEntity();
entity.Id = StringUtils.GetDbInt(reader["Id"]);
entity.Code = StringUtils.GetDbLong(reader["Code"]);
entity.Title = StringUtils.GetDbString(reader["Title"]);
entity.SourceCode = StringUtils.GetDbLong(reader["SourceCode"]);
entity.CreateTime = StringUtils.GetDbDateTime(reader["CreateTime"]);
entity.ReceiptName = StringUtils.GetDbString(reader["ReceiptName"]);
entity.ReceiptProvince = StringUtils.GetDbInt(reader["ReceiptProvince"]);
entity.ReceiptCity = StringUtils.GetDbInt(reader["ReceiptCity"]);
entity.ReceiptTown = StringUtils.GetDbInt(reader["ReceiptTown"]);
entity.ReceiptAddress = StringUtils.GetDbString(reader["ReceiptAddress"]);
entity.ReceiptPhone = StringUtils.GetDbString(reader["ReceiptPhone"]);
entity.Status = StringUtils.GetDbInt(reader["Status"]);
entity.SendTime = StringUtils.GetDbDateTime(reader["SendTime"]);
entity.Remark = StringUtils.GetDbString(reader["Remark"]);
entityList.Add(entity);
}
}
cmd = db.GetSqlStringCommand(sql2);
if (!string.IsNullOrEmpty(keyword))
{
db.AddInParameter(cmd, "@keyword", DbType.String, "%" + keyword + "%");
}
if (memid != -1)
{
db.AddInParameter(cmd, "@CGMemId", DbType.Int32, memid);
}
using (IDataReader reader = db.ExecuteReader(cmd))
{
if (reader.Read())
{
recordCount = StringUtils.GetDbInt(reader[0]);
}
else
{
recordCount = 0;
}
}
return(entityList);
}
