/// <summary>
/// 获取任务列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public PageOf <TaskModel> GetTaskList(int pageIndex, int pageSize)
{
var QUERY_SQL = @"( SELECT TaskID,TaskName,TaskParam,CronExpressionString,AssemblyName,ClassName,Status,IsDelete,CreatedTime,ModifyTime,RecentRunTime,NextFireTime,CronRemark,Remark
FROM p_Task(nolock)
WHERE IsDelete=0 ";
QUERY_SQL += ") pp ";
string SQL = string.Format(@" select * from (select ROW_NUMBER() OVER(order by pp.ModifyTime desc) AS RowNum,* from {0}
) as A where A.RowNum BETWEEN (@PageIndex-1)* @PageSize+1 AND @PageIndex*@PageSize ORDER BY RowNum;" ,
QUERY_SQL);
SQL += string.Format(@" SELECT COUNT(1) FROM {0};", QUERY_SQL);
object param = new { pageIndex = pageIndex, pageSize = pageSize };
DataSet ds = SQLHelper.FillDataSet(SQL, param);
return(new PageOf <TaskModel>()
{
PageIndex = pageIndex,
PageSize = pageSize,
Total = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
Items = DataMapHelper.DataSetToList <TaskModel>(ds)
});
}
/// <summary>
/// 读取错误日志列表
/// </summary>
/// <param name="pageNo"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public PageOf <ErrorLogModel> GetErrorLogList(int pageNo, int pageSize)
{
var QUERY_SQL = @"( select nId,dtDate,sThread,sLevel,sLogger,sMessage,sException,sName
from p_ErrorLog
where DateDiff(dd,dtDate,getdate())<=30";
QUERY_SQL += ") pp ";
string SQL = string.Format(@" select * from (select ROW_NUMBER() OVER(order by pp.dtDate desc) AS RowNum,* from {0}
) as A where A.RowNum BETWEEN (@PageIndex-1)* @PageSize+1 AND @PageIndex*@PageSize ORDER BY RowNum;" ,
QUERY_SQL);
SQL += string.Format(@" SELECT COUNT(1) FROM {0};", QUERY_SQL);
object param = new { pageIndex = pageNo, pageSize = pageSize };
DataSet ds = SQLHelper.FillDataSet(SQL, param);
return(new PageOf <ErrorLogModel>()
{
PageIndex = pageNo,
PageSize = pageSize,
Total = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
Items = DataMapHelper.DataSetToList <ErrorLogModel>(ds)
});
}
public IList <RefundDetailModel> GetRefundInfos(string refundCode, LanguageEnum languageEnum)
{
var sql = @"SELECT r.OrderCode,r.RefundCode,r.RefundStatus,r.RmbTotalAmount,r.RmbDutyAmount,r.TotalAmount,r.RefundType,r.RefundReason,r.ProductStatus,r.RefundDescription,r.CreateTime,r.AuditTime
,r.PickupTime,r.CompletionTime,r.ImagePath,c.UserName,o.Receiver,o.ReceiptAddress,o.Phone,rp.Spu,rp.Sku,p.Name,s.MainDicValue,s.BarCode
,s.SubDicValue,s.MainValue,s.SubValue,rp.UnitPrice,rp.RMBUnitPrice,rp.TaxRate,r.TotalAmount,o.ExchangeRate,rp.Quantity,pImg.ImagePath as ProductImagePath
,pro.ProvinceName,ci.CityName,a.AreaName
FROM RefundOrderInfo r(NOLOCK)
inner join RefundOrderProducts rp(NOLOCK) on r.RefundCode= rp.RefundCode
inner join SkuInfo s(NOLOCK) on s.Sku= rp.Sku
inner join ProductInfo p(NOLOCK) on p.Id=s.SpuId and p.LanguageVersion=@LanguageVersion
inner join OrderInfo o(NOLOCK) on r.OrderCode = o.OrderCode
inner join Province pro(NOLOCK) on pro.ProvinceId=o.ReceiptProvince and pro.LanguageVersion=@LanguageVersion
inner join City ci(NOLOCK) on ci.CityId= o.ReceiptCity and ci.LanguageVersion=@LanguageVersion
inner join Area a(NOLOCK) on a.AreaId= o.ReceiptRegion and a.LanguageVersion=@LanguageVersion
inner join Customer c(NOLOCK) on o.UserId=c.ID
left join ProductImage pImg(NOLOCK) ON pImg.Spu = p.Spu AND pImg.SortValue = 1 AND pImg.ImageType = 1
where r.RefundCode=@RefundCode ";
var db = DbSFO2OMain;
var parameters = db.CreateParameterCollection();
parameters.Append("LanguageVersion", (int)languageEnum);
parameters.Append("RefundCode", refundCode);
var ds = db.ExecuteSqlDataSet(sql, parameters);
return(DataMapHelper.DataSetToList <RefundDetailModel>(ds));
}
public Models.Order GetOrderModel(string orderCode, int supplierId, LanguageEnum languageEnum)
{
var sql = @"select o.OrderCode,o.UserId,c.UserName,c.Mobile,c.NickName,c.Email,o.Receiver,o.ReceiptAddress,o.PayTime,o.DeliveryTime,o.ArrivalTime,o.OrderCompletionTime,o.Phone,o.ShippingMethod,o.CreateTime,o.OrderStatus,o.ExchangeRate,
sum(op.PayAmount) as PaidAmount,sum(op.TaxAmount) as CustomsDuties,sum(op.PayUnitPrice*op.Quantity) as TotalAmount,opay.PayCode,(case when opay.PayPlatform = 1 then '易票联' else '' end) as PayPlatform
,pro.ProvinceName,ci.CityName,a.AreaName,(select top 1 expresslist from OrderExpress where OrderCode = o.OrderCode) as expresslist,(select top 1 ExpressCompany from OrderExpress where OrderCode = o.OrderCode) as ExpressCompany
from OrderInfo o(NOLOCK)
left join OrderPayment opay(NOLOCK) on o.OrderCode=opay.OrderCode and opay.PayStatus=2
inner join Province pro(NOLOCK) on pro.ProvinceId=o.ReceiptProvince and pro.LanguageVersion=@LanguageVersion
inner join City ci(NOLOCK) on ci.CityId= o.ReceiptCity and ci.LanguageVersion=@LanguageVersion
inner join Area a(NOLOCK) on a.AreaId= o.ReceiptRegion and a.LanguageVersion=@LanguageVersion
inner join OrderProducts op(NOLOCK) on o.OrderCode=op.OrderCode
inner join customer c(NOLOCK) on c.ID=o.UserId
inner join SkuInfo s(NOLOCK) on s.Sku= op.Sku
inner join ProductInfo p(NOLOCK) on p.Id=s.SpuId and p.LanguageVersion=@LanguageVersion
where o.OrderCode=@OrderCode and p.SupplierId=@SupplierId
group by o.OrderCode,o.UserId,c.UserName,c.Mobile,c.NickName,c.Email,o.Receiver,o.ReceiptAddress,o.PayTime,o.DeliveryTime,o.ArrivalTime,o.Phone,o.ShippingMethod,o.OrderCompletionTime,
o.CreateTime,o.OrderStatus,o.ExchangeRate,opay.PayCode,opay.PayPlatform,pro.ProvinceName,ci.CityName,a.AreaName";
var db = DbSFO2OMain;
var parameters = db.CreateParameterCollection();
parameters.Append("LanguageVersion", (int)languageEnum);
parameters.Append("SupplierId", supplierId);
parameters.Append("OrderCode", orderCode);
var ds = db.ExecuteSqlDataSet(sql, parameters);
return(DataMapHelper.DataSetToList <Models.Order>(ds)[0]);
}
public PageOf <SupplierUserInfo> GetSupplierUserBySupplierID(int supplierID, PageDTO page)
{
var db = DbSFO2ORead;
var parameters = db.CreateParameterCollection();
string QUERY_SQL = "(" + GETBYID_SQL + " where s.SupplierId=@SupplierID AND su.IsAdmin=0) AS t";
parameters.Append("SupplierID", supplierID);
string SQL = string.Format(@"select * from (select ROW_NUMBER() OVER(order by t.ID desc) AS RowNum,* from {0}
) as A where A.RowNum BETWEEN (@PageIndex-1)* @PageSize+1 AND @PageIndex*@PageSize ORDER BY RowNum;
SELECT COUNT(1) FROM {0};", QUERY_SQL);
parameters.Append("PageIndex", page.PageIndex);
parameters.Append("PageSize", page.PageSize);
DataSet ds = db.ExecuteSqlDataSet(SQL, parameters);
return(new PageOf <SupplierUserInfo>()
{
PageIndex = page.PageIndex,
PageSize = page.PageSize,
Total = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
Items = DataMapHelper.DataSetToList <SupplierUserInfo>(ds)
});
}
/// <summary>
/// 读取运行日志列表
/// </summary>
/// <param name="pageNo"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public PageOf <RunLogModel> GetRunLogList(int pageNo, int pageSize)
{
var QUERY_SQL = @"( select r.Id,r.Remark,r.Description,r.CreateTime,t.TaskName,t.ClassName
from p_RunningLog(nolock) r inner join p_task(nolock) t on r.TaskID = t.TaskID
where DateDiff(dd,r.CreateTime,getdate())<=30";
QUERY_SQL += ") pp ";
string SQL = string.Format(@" select * from (select ROW_NUMBER() OVER(order by pp.CreateTime desc) AS RowNum,* from {0}
) as A where A.RowNum BETWEEN (@PageIndex-1)* @PageSize+1 AND @PageIndex*@PageSize ORDER BY RowNum;" ,
QUERY_SQL);
SQL += string.Format(@" SELECT COUNT(1) FROM {0};", QUERY_SQL);
object param = new { pageIndex = pageNo, pageSize = pageSize };
DataSet ds = SQLHelper.FillDataSet(SQL, param);
return(new PageOf <RunLogModel>()
{
PageIndex = pageNo,
PageSize = pageSize,
Total = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
Items = DataMapHelper.DataSetToList <RunLogModel>(ds)
});
}
public PageOf <SupplierRoleInfo> GetSupplierRoleBySupplierID(int supplierID, PageDTO page)
{
var db = DbSFO2ORead;
var parameters = db.CreateParameterCollection();
string QUERY_SQL = @"(SELECT r.[RoleID],[RoleName],[Status],[SupplierID],COUNT(UserID) UserCount
FROM [SupplierRole] r (nolock) LEFT JOIN [SupplierUserRole] m ON m.RoleID = r.RoleID
WHERE r.[Status]=1 AND SupplierID=@SupplierID
GROUP BY r.[RoleID],[RoleName],[Status],[SupplierID]) AS t";
parameters.Append("SupplierID", supplierID);
string SQL = string.Format(@"select * from (select ROW_NUMBER() OVER(order by t.RoleID desc) AS RowNum,* from {0}
) as A where A.RowNum BETWEEN (@PageIndex-1)* @PageSize+1 AND @PageIndex*@PageSize ORDER BY RowNum;
SELECT COUNT(1) FROM {0};", QUERY_SQL);
parameters.Append("PageIndex", page.PageIndex);
parameters.Append("PageSize", page.PageSize);
DataSet ds = db.ExecuteSqlDataSet(SQL, parameters);
return(new PageOf <SupplierRoleInfo>()
{
PageIndex = page.PageIndex,
PageSize = page.PageSize,
Total = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
Items = DataMapHelper.DataSetToList <SupplierRoleInfo>(ds)
});
}
public PageOf <PromotionSkuListModel> GetSpuulierSkus(int supplierId, string productName, PageDTO page)
{
var querySql = @" WITH pSku
AS
(
SELECT DISTINCT p.Id,ps.sku,1 AS IsInPromotion,ps.DiscountPrice,ps.DiscountRate FROM Promotions(NOLOCK)p
INNER JOIN PromotionSku(NOLOCK) AS PS
ON p.Id = ps.PromotionId
WHERE p.PromotionStatus NOT IN(3,4) AND p.SupplierId=@SupplierId
)
{0}
SELECT ROW_NUMBER() OVER(ORDER BY p.ModifyTime DESC, s.spu,s.sku) AS RowNum,s.spu,s.Sku,p.Name,s.MainValue,s.SubValue,s.Price,CASE psku.IsInPromotion WHEN 1 THEN 'true' ELSE 'false' END AS skuProStatus
,pSku.Id AS PromotionId,pSku.DiscountPrice AS PromotionPrice,pSku.DiscountRate AS PromotionRate,pImg.ImagePath
FROM ProductInfo(NOLOCK) p
INNER JOIN SkuInfo(NOLOCK) s
ON p.Id=s.SpuId AND p.LanguageVersion=2 AND p.SupplierId=@SupplierId AND s.Status=3
LEFT JOIN ProductImage(NOLOCK) pImg
ON pImg.Spu = p.Spu AND pImg.SortValue=1
LEFT JOIN pSku
ON pSku.Sku = s.Sku
WHERE 1=1 ";
var db = DbSFO2ORead;
var parameters = db.CreateParameterCollection();
parameters.Append("SupplierId", supplierId);
if (!String.IsNullOrWhiteSpace(productName))
{
querySql += " AND name LIKE @Name";
parameters.Append("Name", "%" + productName + "%");
}
var sql = String.Empty;
sql = String.Format(querySql, "SELECT * FROM (") + ")a WHERE a.RowNum BETWEEN (@PageIndex-1)* @PageSize+1 AND @PageIndex*@PageSize ORDER BY RowNum;";
parameters.Append("PageIndex", page.PageIndex);
parameters.Append("PageSize", page.PageSize);
sql += String.Format(querySql, "SELECT COUNT(1) FROM (") + ") b";
DataSet ds = db.ExecuteSqlDataSet(sql, parameters);
return(new PageOf <PromotionSkuListModel>()
{
PageIndex = page.PageIndex,
PageSize = page.PageSize,
Total = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
RowCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
Items = DataMapHelper.DataSetToList <PromotionSkuListModel>(ds)
});
}
public Models.PageOf <Models.OrderListInfoModel> GetOrderList(Models.OrderQueryInfo queryInfo, Models.PageDTO page, LanguageEnum languageVersion, int receiptCountry)
{
var db = DbSFO2OMain;
var parameters = db.CreateParameterCollection();
var sql = @"( select o.OrderCode,s.MainDicValue,s.SubDicValue,s.MainValue,s.SubValue,o.CreateTime,p.Name,op.UnitPrice,op.PayUnitPrice,op.Quantity,o.TotalAmount,
o.OrderStatus,o.ExchangeRate,p.Spu,pImg.ImagePath as ProductImagePath,op.Sku,op.RefundQuantity,
(select top 1 expresslist from OrderExpress where OrderCode = o.OrderCode) as expresslist,
(select top 1 ExpressCompany from OrderExpress where OrderCode = o.OrderCode) as ExpressCompany,pr.ProvinceName+' | '+ci.CityName+' | '+ ar.AreaName+' | '+ o.ReceiptAddress AS ReceiptAddress,o.Phone,o.Receiver
from OrderInfo o(NOLOCK)
inner join OrderProducts op(NOLOCK) on o.OrderCode= op.OrderCode
inner join SkuInfo s(NOLOCK) on s.Sku= op.Sku
inner join ProductInfo p(NOLOCK) on p.Id=s.SpuId and p.LanguageVersion=@LanguageVersion
left join ProductImage pImg(NOLOCK) ON pImg.Spu = p.Spu AND pImg.SortValue = 1 AND pImg.ImageType = 1
LEFT JOIN city(NOLOCK) AS ci ON ci.CityId=o.ReceiptCity AND ci.LanguageVersion = 1
LEFT JOIN Province(NOLOCK) AS pr ON pr.ProvinceId = o.ReceiptProvince AND pr.LanguageVersion = 1
LEFT JOIN Area AS ar ON ar.areaid=o.ReceiptRegion AND ar.LanguageVersion = 1
where p.SupplierId= @SupplierId ";
if (receiptCountry != 0)
{
sql = sql + " and o.ReceiptCountry=@ReceiptCountry";
}
var query = BindQuery(queryInfo, parameters);
sql = sql + query;
sql += ") pp ";
string SQL = string.Format(@" select * from (select ROW_NUMBER() OVER(order by pp.CreateTime desc) AS RowNum,* from {0}
) as A where A.RowNum BETWEEN (@PageIndex-1)* @PageSize+1 AND @PageIndex*@PageSize ORDER BY RowNum;" ,
sql);
SQL += string.Format(@" SELECT COUNT(1),COUNT(distinct pp.OrderCode) FROM {0};", sql);
parameters.Append("LanguageVersion", (int)languageVersion);
parameters.Append("SupplierId", queryInfo.SupplierId);
parameters.Append("ReceiptCountry", receiptCountry);
parameters.Append("PageIndex", page.PageIndex);
parameters.Append("PageSize", page.PageSize);
DataSet ds = db.ExecuteSqlDataSet(SQL, parameters);
return(new PageOf <OrderListInfoModel>()
{
PageIndex = page.PageIndex,
PageSize = page.PageSize,
Total = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
RowCount = Convert.ToInt32(ds.Tables[1].Rows[0][1]),
Items = DataMapHelper.DataSetToList <OrderListInfoModel>(ds)
});
}
public PageOf <SkuInfo> GetProductInventoryList(InventoryListViewModel queryModel, LanguageEnum languageEnum, Models.PagingModel page)
{
var ds = productDAL.GetProductInventoryList(queryModel, languageEnum, page);
return(new PageOf <SkuInfo>()
{
PageIndex = page.PageIndex,
PageSize = page.PageSize,
Total = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
RowCount = Convert.ToInt32(ds.Tables[1].Rows[0][1]),
Items = DataMapHelper.DataSetToList <SkuInfo>(ds)
});
}
public SupplierCounter GetSupplierCounter(int objectID, int countType, DateTime countDate)
{
String sql = "SELECT * FROM [SupplierCounter](NOLOCK) WHERE ObjectID=@ObjectID AND CountType=@CountType AND CountDate=@CountDate";
var db = DbSFO2ORead;
var parameters = db.CreateParameterCollection();
parameters.Append("ObjectID", objectID);
parameters.Append("CountType", countType);
parameters.Append("CountDate", countDate);
var ds = db.ExecuteSqlDataSet(sql, parameters);
return(DataMapHelper.DataSetToObject <SupplierCounter>(ds));;
}
public FindPasswordToken GetFindPasswordToken(int supplierID)
{
var db = DbSFO2ORead;
var parameters = db.CreateParameterCollection();
string strsql = @"SELECT [Token],[ExpiredTime]
FROM [SupplierToken](NOLOCK) WHERE ObjectID=@SupplierID AND [TokenType]=1 AND [Status]=1 AND [ExpiredTime]>GetDate()";
parameters.Append("SupplierID", supplierID);
DataSet ds2 = db.ExecuteSqlDataSet(strsql, parameters);
var token = DataMapHelper.DataSetToObject <FindPasswordToken>(ds2);
return(token);
}
public PageOf <RefundInfoModel> GetRefundOrderList(RefundQueryInfo queryInfo, PageDTO page, LanguageEnum languageVersion, int receiptCountry)
{
var db = DbSFO2OMain;
var parameters = db.CreateParameterCollection();
var sql = @"( select r.OrderCode,r.RefundCode,r.UserId,c.UserName,r.RefundStatus,r.CreateTime,r.RefundType,rp.Spu,rp.Sku,p.Name,s.MainDicValue,
s.SubDicValue,s.MainValue,s.SubValue,rp.UnitPrice,rp.RMBUnitPrice,rp.TaxRate,r.TotalAmount,o.ExchangeRate,rp.Quantity,pImg.ImagePath as ProductImagePath
FROM RefundOrderInfo r(NOLOCK)
inner join RefundOrderProducts rp(NOLOCK) on r.RefundCode= rp.RefundCode
inner join SkuInfo s(NOLOCK) on s.Sku= rp.Sku
inner join ProductInfo p(NOLOCK) on p.Id=s.SpuId and p.LanguageVersion=@LanguageVersion
left join ProductImage pImg(NOLOCK) ON pImg.Spu = p.Spu AND pImg.SortValue = 1 AND pImg.ImageType = 1
inner join OrderInfo o(NOLOCK) on r.OrderCode = o.OrderCode
inner join customer c(NOLOCK) on c.ID=r.UserId
where p.SupplierId= @SupplierId ";
if (receiptCountry != 0)
{
sql = sql + " and o.ReceiptCountry=@ReceiptCountry";
}
var query = BindQuery(queryInfo, parameters);
sql = sql + query;
sql += ") pp ";
string SQL = string.Format(@" select * from (select ROW_NUMBER() OVER(order by pp.CreateTime desc) AS RowNum,* from {0}
) as A where A.RowNum BETWEEN (@PageIndex-1)* @PageSize+1 AND @PageIndex*@PageSize ORDER BY RowNum;" ,
sql);
SQL += string.Format(@" SELECT COUNT(1) FROM {0};", sql);
parameters.Append("LanguageVersion", (int)languageVersion);
parameters.Append("SupplierId", queryInfo.SupplierId);
parameters.Append("ReceiptCountry", receiptCountry);
parameters.Append("PageIndex", page.PageIndex);
parameters.Append("PageSize", page.PageSize);
DataSet ds = db.ExecuteSqlDataSet(SQL, parameters);
return(new PageOf <RefundInfoModel>()
{
PageIndex = page.PageIndex,
PageSize = page.PageSize,
Total = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
Items = DataMapHelper.DataSetToList <RefundInfoModel>(ds)
});
}
public FindPasswordToken VerifyFindPasswordToken(string token)
{
var db = DbSFO2ORead;
var parameters = db.CreateParameterCollection();
string strsql = @"SELECT s.[SupplierID],[UserName],[CompanyName],st.[Token],st.[ExpiredTime]
FROM [Supplier](NOLOCK) s
INNER JOIN [SupplierToken](NOLOCK) st ON s.SupplierID = st.ObjectID AND st.[TokenType]=1
WHERE st.[ExpiredTime]>GetDate() AND st.[Status]=1 AND st.[Token]=@Token";
parameters.Append("Token", token);
DataSet ds = db.ExecuteSqlDataSet(strsql, parameters);
return(DataMapHelper.DataSetToObject <FindPasswordToken>(ds));
}
public IList <OrderLogistics> GetOrderLogistics(string orderCode, int supplierId)
{
var sql = @"select ol.OrderCode,oe.ExpressCompany,oe.ExpressList,ol.Id,ol.LogisticsTime,ol.Remark,ol.Status,ol.CreateTime from
OrderExpress oe(NOLOCK) inner join OrderLogistics ol(NOLOCK) on oe.OrderCode=ol.OrderCode
where oe.OrderCode=@OrderCode
order by ol.CreateTime desc ";
var db = DbSFO2OMain;
var parameters = db.CreateParameterCollection();
parameters.Append("OrderCode", orderCode);
var ds = db.ExecuteSqlDataSet(sql, parameters);
return(DataMapHelper.DataSetToList <OrderLogistics>(ds));
}
public PageOf <PromotionListModel> GetPromotionList(int supplierId, PromotionQuery query, PageDTO page)
{
var querySql = @" SELECT ROW_NUMBER() OVER(ORDER BY createtime DESC) AS RowNum,Id,PromotionName,PromotionStatus,StartTime,EndTime FROM Promotions(NOLOCK)
WHERE SupplierId=@SupplierId AND StartTime >=@StartTime AND StartTime <=@EndTime ";
var db = DbSFO2ORead;
var parameters = db.CreateParameterCollection();
parameters.Append("SupplierId", supplierId);
parameters.Append("StartTime", query.StartTime);
parameters.Append("EndTime", query.EndTime);
if (!String.IsNullOrWhiteSpace(query.PromotionName))
{
querySql += " AND PromotionName LIKE @PromotionName";
parameters.Append("PromotionName", "%" + query.PromotionName + "%");
}
if (query.PromotionStatus >= 0)
{
querySql += " AND PromotionStatus=@PromotionStatus";
parameters.Append("PromotionStatus", query.PromotionStatus);
}
var sql = String.Empty;
sql += "SELECT * FROM (" + querySql + ")a" + " WHERE a.RowNum BETWEEN (@PageIndex-1)* @PageSize+1 AND @PageIndex*@PageSize ORDER BY RowNum";
parameters.Append("PageIndex", page.PageIndex);
parameters.Append("PageSize", page.PageSize);
sql += string.Format(@" SELECT COUNT(1) FROM ({0}) b;", querySql);
DataSet ds = db.ExecuteSqlDataSet(sql, parameters);
return(new PageOf <PromotionListModel>()
{
PageIndex = page.PageIndex,
PageSize = page.PageSize,
Total = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
RowCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
Items = DataMapHelper.DataSetToList <PromotionListModel>(ds)
});
}
public PageOf <Models.StoreModel> GetStoreListByBrandId(int brandId, int areaId, Models.Enums.LanguageEnum languageEnum, int pageSize, int pageIndex)
{
var query = @" (SELECT s.Id
,s.BrandId
,s.AreaId
,p.ProvinceName as AreaName
,s.AddressCN
,s.AddressEN
,s.AddressHK
,s.Status
FROM Store s(nolock) inner join Province p(nolock) on s.AreaId = p.ProvinceId and p.LanguageVersion=@LanguageVersion
WHERE s.BrandId=@BrandId and s.Status=1";
if (areaId > 0)
{
query += " and s.AreaId=@AreaId";
}
query += ") pp ";
string sql = string.Format(@" select * from (select ROW_NUMBER() OVER(order by pp.Id desc) AS RowNum,* from {0}
) as A where A.RowNum BETWEEN (@PageIndex-1)* @PageSize+1 AND @PageIndex*@PageSize ORDER BY RowNum;" ,
query);
sql += string.Format(@" SELECT COUNT(1) FROM {0};", query);
var db = DbSFO2OMain;
var parameters = db.CreateParameterCollection();
parameters.Append("BrandId", brandId);
parameters.Append("AreaId", areaId);
parameters.Append("LanguageVersion", (int)languageEnum);
parameters.Append("PageIndex", pageIndex);
parameters.Append("PageSize", pageSize);
DataSet ds = db.ExecuteSqlDataSet(sql, parameters);
return(new PageOf <StoreModel>()
{
PageIndex = pageIndex,
PageSize = pageSize,
Total = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
Items = DataMapHelper.DataSetToList <StoreModel>(ds)
});
}
public PageOf <SupplierBrandModel> GetBrandBySupplierId(int supplierId, PageDTO page)
{
var querySql = @" with cte(BrandId,Spu,sku)
as
(
select distinct p.BrandId,p.Spu,s.sku
from ProductInfo p(nolock)
inner join SkuInfo(nolock) s on p.Id=s.SpuId and s.Status=3 and p.LanguageVersion=1
inner join Stock st(nolock) on s.Sku=st.Sku
group by p.BrandId,p.Spu,s.sku,p.MinForOrder
having sum(st.ForOrderQty) >= p.MinForOrder
)
{0}
SELECT ROW_NUMBER() OVER(ORDER BY sb.CreateTime desc) AS RowNum,Id,SupplierId,NameCN,NameHK,NameEN,CreateTime,Status,
(select count(1) from cte where BrandId = sb.Id) as OnSaleCount
FROM SupplierBrand sb(nolock)
WHERE 1=1 and SupplierId=@SupplierId";
var sql = String.Empty;
sql = String.Format(querySql, "SELECT * FROM (") + ")a WHERE a.RowNum BETWEEN (@PageIndex-1)* @PageSize+1 AND @PageIndex*@PageSize ORDER BY RowNum;";
var db = DbSFO2OMain;
var parameters = db.CreateParameterCollection();
parameters.Append("SupplierId", supplierId);
parameters.Append("PageIndex", page.PageIndex);
parameters.Append("PageSize", page.PageSize);
sql += String.Format(querySql, "SELECT COUNT(1) FROM (") + ") b";
DataSet ds = db.ExecuteSqlDataSet(sql, parameters);
return(new PageOf <SupplierBrandModel>()
{
PageIndex = page.PageIndex,
PageSize = page.PageSize,
Total = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
RowCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
Items = DataMapHelper.DataSetToList <SupplierBrandModel>(ds)
});
}
public SupplierDetailModel GetSupplierDetailInfo(int supplierId)
{
const string sql = @"
WITH SN AS
(
SELECT pin.SupplierId,COUNT(1) AS SkuNumber FROM SkuInfo(NOLOCK) si
INNER JOIN ProductInfo(NOLOCK) pin
ON si.SpuId = pin.id AND pin.LanguageVersion=2
LEFT JOIN Stock(NOLOCK) t ON t.Sku=si.Sku
WHERE pin.SupplierId=@SupplierID AND t.ForOrderQty >0 and [Status]=3
GROUP BY pin.SupplierId
)
SELECT s.*,sn.SkuNumber,ss.StoreLogoPath FROM Supplier(NOLOCK) AS s
LEFT JOIN SN
ON sn.SupplierId = s.SupplierID
INNER JOIN SupplierStore(NOLOCK) AS ss
ON ss.SupplierID = s.SupplierID
WHERE s.SupplierID=@SupplierID
SELECT * FROM SupplierBrand(NOLOCK) WHERE SupplierID=@SupplierID";
var db = DbSFO2ORead;
var parameters = db.CreateParameterCollection();
parameters.Append("@SupplierID", supplierId);
var ds = db.ExecuteDataSet(CommandType.Text, sql, parameters);
var sd = new SupplierDetailModel();
if (ds != null && ds.Tables != null && ds.Tables.Count > 0)
{
sd.Supplier = DataMapHelper.DataTableToObject <SupplierModel>(ds.Tables[0]);
if (ds.Tables[1] != null && ds.Tables[1].Rows != null && ds.Tables[1].Rows.Count > 0)
{
sd.Brands = DataMapHelper.DataTableToList <BrandModel>(ds.Tables[1]).ToList();
}
}
return(sd);
}
public IList <Models.OrderProduct> GetOrderProducts(string orderCode, int supplierId, LanguageEnum languageEnum)
{
var sql = @"select o.OrderCode,s.Sku,p.Spu,s.MainDicValue,s.SubDicValue,s.MainValue,s.SubValue,p.Name,s.BarCode,op.PayUnitPrice,op.TaxAmount,op.Quantity,op.UnitPrice,
o.OrderStatus,o.ExchangeRate,pImg.ImagePath as ProductImagePath,op.RefundQuantity,ISNULL(opr.OriginalPrice - opr.PromotionPrice,0) PromotionPrice
from OrderInfo o(NOLOCK)
inner join OrderProducts op(NOLOCK) on o.OrderCode= op.OrderCode
inner join SkuInfo s(NOLOCK) on s.Sku= op.Sku
inner join ProductInfo p(NOLOCK) on p.Id=s.SpuId and p.LanguageVersion=@LanguageVersion
inner join customer c(NOLOCK) on c.ID=o.UserId
left join ProductImage pImg(NOLOCK) ON pImg.Spu = p.Spu AND pImg.SortValue = 1 AND pImg.ImageType = 1
LEFT JOIN OrderPromotions opr ON opr.OrderCode=op.OrderCode AND opr.Sku=s.Sku
where o.OrderCode=@OrderCode and p.SupplierId= @SupplierId";
var db = DbSFO2OMain;
var parameters = db.CreateParameterCollection();
parameters.Append("LanguageVersion", (int)languageEnum);
parameters.Append("SupplierId", supplierId);
parameters.Append("OrderCode", orderCode);
var ds = db.ExecuteSqlDataSet(sql, parameters);
return(DataMapHelper.DataSetToList <OrderProduct>(ds));
}
public PageOf <AdminUserInfo> GetAdminUserList(PagingModel page)
{
var db = DbSFO2ORead;
var parameters = db.CreateParameterCollection();
string QUERY_SQL = "(SELECT * FROM AdminUser(NOLOCK) WHERE IsAdmin=0) AS t";
string SQL = string.Format(@"select * from (select ROW_NUMBER() OVER(order by t.ID desc) AS RowNum,* from {0}
) as A where A.RowNum BETWEEN (@PageIndex-1)* @PageSize+1 AND @PageIndex*@PageSize ORDER BY RowNum;
SELECT COUNT(1) FROM {0};", QUERY_SQL);
parameters.Append("PageIndex", page.PageIndex);
parameters.Append("PageSize", page.PageSize);
DataSet ds = db.ExecuteSqlDataSet(SQL, parameters);
return(new PageOf <AdminUserInfo>()
{
PageIndex = page.PageIndex,
PageSize = page.PageSize,
Total = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
Items = DataMapHelper.DataSetToList <AdminUserInfo>(ds)
});
}
public PageOf <UserModel> GetUserList(int pageNo, int pageSize)
{
var QUERY_SQL = @"( select UserId,UserName,PassWord,TrueName,UserEmail,PhoneNum,IsAdmin,Status,CreateTime,LastLoginTime
from p_User";
QUERY_SQL += ") pp ";
string SQL = string.Format(@" select * from (select ROW_NUMBER() OVER(order by pp.CreateTime desc) AS RowNum,* from {0}
) as A where A.RowNum BETWEEN (@PageIndex-1)* @PageSize+1 AND @PageIndex*@PageSize ORDER BY RowNum;" ,
QUERY_SQL);
SQL += string.Format(@" SELECT COUNT(1) FROM {0};", QUERY_SQL);
object param = new { pageIndex = pageNo, pageSize = pageSize };
DataSet ds = SQLHelper.FillDataSet(SQL, param);
return(new PageOf <UserModel>()
{
PageIndex = pageNo,
PageSize = pageSize,
Total = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
Items = DataMapHelper.DataSetToList <UserModel>(ds)
});
}
public PageOf <OrderListModel> GetOrderList(OrderListQueryModel query, int pageSize, int pageIndex)
{
const string sql = @"
WITH IMGS AS
(
SELECT spu,ImagePath
FROM (
SELECT rownum = ROW_NUMBER() OVER (PARTITION BY spu ORDER BY sortValue), spu,ImagePath
FROM ProductImage
WHERE ImageType = 1
)b
WHERE rownum<2
),
orderMain AS
(
SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY a.Createtime desc) AS RowNumber,* FROM (
SELECT distinct o.OrderCode,o.CreateTime,c.UserName,o.OrderStatus,o.ExchangeRate,SUM(op.RefundQuantity) AS RefundQuantity,CustomsDuties
FROM OrderInfo o(NOLOCK)
INNER JOIN OrderProducts op(NOLOCK) on o.OrderCode=op.OrderCode
INNER JOIN customer c(NOLOCK) on c.ID=o.UserId
INNER JOIN SkuInfo s(NOLOCK) on s.Sku= op.Sku
INNER JOIN ProductInfo p(NOLOCK) on p.Id=s.SpuId and p.LanguageVersion=1
WHERE 1=1 {0} {1}
GROUP BY o.OrderCode,o.CreateTime,c.UserName,o.OrderStatus,o.ExchangeRate,o.CustomsDuties
) a)b WHERE b.RowNumber>(@PageIndex-1)*@PageSize AND b.RowNumber <= @PageIndex*@PageSize
)
SELECT om.OrderCode,om.CreateTime,om.UserName,p.Name,si.MainDicValue,si.MainValue,si.SubDicValue,si.SubValue,om.OrderStatus,op.Sku,op.Spu,op.PayUnitPrice,op.Quantity,op.IsBearDuty,s.CompanyName,op.TaxAmount,op.IsBearDuty,om.CustomsDuties
,SUM(op.PayUnitPrice*op.Quantity) AS TotalAmount,SUM(op.UnitPrice*op.Quantity) AS TotalAmount1,om.ExchangeRate,IMGS.ImagePath
FROM OrderProducts(NOLOCK) AS op
INNER JOIN orderMain AS om ON op.OrderCode = om.OrderCode
INNER JOIN SkuInfo si(NOLOCK) ON si.Sku= op.Sku
INNER JOIN ProductInfo p(NOLOCK) ON p.Id=si.SpuId and p.LanguageVersion=1
INNER JOIN Supplier(NOLOCK) AS s ON s.SupplierID = p.SupplierId
LEFT JOIN IMGS ON IMGS.Spu=op.Spu
WHERE 1=1 {2}
GROUP BY om.OrderCode,om.CreateTime,om.UserName,op.Sku,op.Spu,op.PayUnitPrice,op.Quantity,s.CompanyName,p.Name,si.MainDicValue,si.MainValue,si.SubDicValue,si.SubValue,om.OrderStatus,om.ExchangeRate,IMGS.ImagePath,op.TaxAmount,op.IsBearDuty,om.CustomsDuties
ORDER BY om.CreateTime DESC
SELECT COUNT(DISTINCT o.ordercode)
FROM OrderInfo o(NOLOCK)
INNER JOIN OrderProducts op(NOLOCK) on o.OrderCode=op.OrderCode
INNER JOIN customer c(NOLOCK) on c.ID=o.UserId
INNER JOIN SkuInfo s(NOLOCK) on s.Sku= op.Sku
INNER JOIN ProductInfo p(NOLOCK) on p.Id=s.SpuId and p.LanguageVersion=1
WHERE 1=1 {0} {1}
";
var db = DbSFO2ORead;
var parameters = db.CreateParameterCollection();
parameters.Append("PageSize", pageSize);
parameters.Append("PageIndex", pageIndex);
var recCountry = "";
if (query.CountryCode > 0)
{
parameters.Append("ReceiptCountry", query.CountryCode);
recCountry = "AND ReceiptCountry=@ReceiptCountry";
}
var conditions = OrderListConditions(query, ref parameters);
var conditions1 = "";
if (query.SellerId > 0)
{
conditions1 = " AND p.SupplierId=@SupplierId1";
parameters.Append("SupplierId1", query.SellerId);
}
if (!String.IsNullOrWhiteSpace(query.SKU))
{
conditions1 += " AND op.Sku=@SKU1";
parameters.Append("SKU1", query.SKU);
}
var finalSql = String.Format(sql, recCountry, conditions, conditions1);
var ds = db.ExecuteSqlDataSet(finalSql, parameters);
return(new PageOf <OrderListModel>()
{
PageIndex = pageIndex,
PageSize = pageSize,
RowCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
Items = DataMapHelper.DataSetToList <OrderListModel>(ds)
});
}
