public ActionResult ExportSupplierQueryList(DateTime startTime, DateTime endTime, string accountName, string companyName, int supplierStatus)
{
var dtNow = DateTime.Now;
SupplierQueryModel qm = new SupplierQueryModel()
{
AccountName = accountName,
CompanyName = companyName,
CreateTimeEnd = endTime,
CreateTimeStart = startTime,
SupplierStatus = supplierStatus,
};
PageOf <SupplierAbstractModel> suppliers = null;
try
{
suppliers = supplierBLL.GetSupplierList(qm, int.MaxValue, 1);
Response.Clear();
Response.Buffer = true;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=supplierlist" + System.DateTime.Now.ToString("yyyyMMdd") + ".xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); //设置输出流为简体中文
Response.ContentType = "application/ms-excel"; //设置输出文件类型为excel文件。
SupplierQueryListToExcel(suppliers.Items);
Response.End();
}
catch (Exception ext)
{
LogHelper.Error(ext);
}
return(View());
}
public ActionResult SupplierQueryList(SupplierQueryModel qm)
{
PageOf <SupplierAbstractModel> suppliers = null;
try
{
suppliers = supplierBLL.GetSupplierList(qm, this.PageSize, this.PageNo);
}
catch (Exception ext)
{
LogHelper.Error(ext);
}
return(View(suppliers));
}
//
// GET: /Supplier/
public ActionResult SupplierQuery()
{
var dtNow = DateTime.Now;
var qm = new SupplierQueryModel()
{
AccountName = Request["AccountName"] == null ? "" : Request["AccountName"].ToString(),
CompanyName = Request["CompanyName"] == null ? "" : Request["CompanyName"].ToString(),
CreateTimeEnd = Request["EndTime"] == null ? dtNow : Convert.ToDateTime(Request["EndTime"].ToString()),
CreateTimeStart = Request["StartTime"] == null?dtNow.AddMonths(-6) : Convert.ToDateTime(Request["StartTime"].ToString()),
SupplierStatus = Request["SupplierStatus"] == null ? 0 : Convert.ToInt32(Request["SupplierStatus"].ToString()),
};
ViewBag.PageNo = Request["PageNo"] == null ? 1 : Convert.ToInt32(Request["PageNo"].ToString());
ViewBag.PageSize = Request["PageSize"] == null ? 50 : Convert.ToInt32(Request["PageSize"].ToString());
return(View(qm));
}
public PageOf <SupplierAbstractModel> GetSupplierList(SupplierQueryModel query, int pageSize, int pageNo)
{
const string sql = @"
WITH SN AS
(
SELECT a.SupplierId,SUM(a.SkuNumber) AS SkuNumber FROM (
SELECT pin.SupplierId,COUNT(distinct si.sku) 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 t.ForOrderQty >0 and [Status]=3
GROUP BY pin.SupplierId,pin.MinForOrder
HAVING SUm(ISNULL(t.ForOrderQty,0)) - pin.MinForOrder >0)a
GROUP BY a.SupplierId
)
SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY s.Createtime DESC) AS RowNumber,s.SupplierID, su.UserName,s.CompanyName,s.CreateTime,Case s.status WHEN 1 THEN '正常' WHEN 2 THEN '凍結' END AS supplierStatus,sn.SkuNumber
FROM SupplierUser(NOLOCK) su
INNER JOIN Supplier(NOLOCK) s
ON s.SupplierID=su.SupplierID AND su.IsAdmin=1
LEFT JOIN SN
ON sn.SupplierId=su.SupplierID
WHERE s.Createtime>=@StartTime AND s.CreateTime<@EndTime {0}) a
WHERE a.RowNumber > (@pageNo-1)*@pageSize AND a.RowNumber <=@pageNo*@pageSize";
var db = DbSFO2OMain;
var parameters = db.CreateParameterCollection();
var countParas = db.CreateParameterCollection();
var startTime = new DateTime(query.CreateTimeStart.Year, query.CreateTimeStart.Month, query.CreateTimeStart.Day);
var endTime = new DateTime(query.CreateTimeEnd.Year, query.CreateTimeEnd.Month, query.CreateTimeEnd.Day, 23, 59, 59, 999);
parameters.Append("StartTime", startTime);
parameters.Append("EndTime", endTime);
parameters.Append("pageSize", pageSize);
parameters.Append("pageNo", pageNo);
countParas.Append("StartTime", startTime);
countParas.Append("EndTime", endTime);
countParas.Append("pageSize", pageSize);
countParas.Append("pageNo", pageNo);
StringBuilder sb = new StringBuilder();
if (!String.IsNullOrWhiteSpace(query.AccountName))
{
sb.Append(" AND su.UserName like @UserName");
parameters.Append("UserName", "%" + query.AccountName + "%");
countParas.Append("UserName", "%" + query.AccountName + "%");
}
if (!String.IsNullOrWhiteSpace(query.CompanyName))
{
sb.Append(" AND s.CompanyName like @CompanyName");
parameters.Append("CompanyName", "%" + query.CompanyName + "%");
countParas.Append("CompanyName", "%" + query.CompanyName + "%");
}
if (query.SupplierStatus > 0)
{
sb.Append(" AND s.Status=@Status");
parameters.Append("Status", query.SupplierStatus);
countParas.Append("Status", query.SupplierStatus);
}
var reslut = db.ExecuteSqlList <SupplierAbstractModel>(String.Format(sql, sb.ToString()), parameters);
const string countSql = @"
SELECT COUNT(1)
FROM SupplierUser(NOLOCK) su
INNER JOIN Supplier(NOLOCK) s
ON s.SupplierID=su.SupplierID AND su.IsAdmin=1
WHERE s.Createtime>=@StartTime AND s.CreateTime<@EndTime {0}";
var dataCount = db.ExecuteSqlScalar <int>(String.Format(countSql, sb.ToString()), countParas);
return(new PageOf <SupplierAbstractModel>()
{
Items = reslut,
PageIndex = pageNo,
PageSize = pageSize,
Total = dataCount
});
}
public PageOf <SupplierAbstractModel> GetSupplierList(SupplierQueryModel query, int pageSize, int pageNo)
{
return(supplierInfoDAL.GetSupplierList(query, pageSize, pageNo));
}
