public ActionResult GetInventoryListJson(InventoryReq inventoryReq)
{
//sql
string strSql = @"
select
a.Id,
WareName,
OrderNo,
c.ProductName as ProductType,
c.ImgContent as ProductImg,
(select F_ItemName from View_ItemType_ItemDetail where F_EnCode='0202' and F_ItemCode=a.Grade) as Grade,
Strength,
(select F_ItemName from View_ItemType_ItemDetail where F_EnCode='0208' and F_ItemCode=a.Length) as Length,
(select F_ItemName from View_ItemType_ItemDetail where F_EnCode='0203' and F_ItemCode=a.HorseValue) as HorseValue,
(select F_ItemName from View_ItemType_ItemDetail where F_EnCode='0207' and F_ItemCode=a.Status) as Status,
(select F_ItemName from View_ItemType_ItemDetail where F_EnCode='0206' and F_ItemCode=a.QuoteType) as QuoteType,
(case when a.QuoteType<>'Futures' then a.Price else null end) as Price,
(case when a.QuoteType<>'Futures' then null else a.Contract end) as Contract,
(case when a.QuoteType<>'Futures' then null else cast(b.Price as decimal(18,2)) end) as ContractPrice,
(case when a.QuoteType<>'Futures' then null else cast(a.Basis+b.Price as decimal(18,2)) end) as TotalPrice,
(case when a.QuoteType<>'Futures' then null else Basis end) as Basis,
Year,
SailingSchedule,
(Weight-isnull((select sum(Weight) from InventoryOut where InventoryId=a.Id),0)) as Weight,
IsRecommend,
b.FS,b.M,b.S,b.C,b.V
from Inventory a
left join Contract b on a.Contract=b.ContractCode
left join Product c on c.ProductCode=a.ProductType
where 1=1 ";
//where
string strQry = "";
if (inventoryReq.inventoryQry != null)
{
InventoryQry[] inventoryQrys = inventoryReq.inventoryQry;
foreach (InventoryQry inventoryQry in inventoryQrys)
{
if (inventoryQry.code.ToLower() == "strength")//强力是范围，其他都是选项
{
foreach (List selected in inventoryQry.list)
{
if (selected.name.ToLower() == "min")
{
strQry += " and cast(Strength as float)>=" + selected.code + " ";
}
else if (selected.name.ToLower() == "max")
{
strQry += " and cast(Strength as float)<=" + selected.code + " ";
}
}
}
else if (inventoryQry.code.ToLower() == "producttype")//分类中，其他是除美棉/澳棉/巴西棉/印度棉以外的所有
{
string condition = "";
foreach (List selected in inventoryQry.list)
{
if (selected.code.ToLower() != "qt")
{
if (selected.selected)
{
condition += "'" + selected.code + "',";
}
}
else
{
if (selected.selected)
{
DataView dvQT = DbHelper.ExecuteToDataView("select ProductCode from Product where ProductCode not in ('mm','bxm','ydm','om')");
foreach (DataRowView drv in dvQT)
{
condition += "'" + drv["ProductCode"] + "',";
}
}
}
}
if (!string.IsNullOrEmpty(condition))
{
strQry += " and " + inventoryQry.code + " in (" + condition.Trim(',') + ") ";
}
}
else
{
string condition = "";
foreach (List selected in inventoryQry.list)
{
if (selected.selected)
{
condition += "'" + selected.code + "',";
}
}
if (!string.IsNullOrEmpty(condition))
{
strQry += " and " + inventoryQry.code + " in (" + condition.Trim(',') + ") ";
}
}
}
}
//order
string strOrder = " order by IsRecommend desc,a.CreatorTime desc ";
//paging
Pagination pagination = inventoryReq.pagination;
string strPaging = " offset " + (pagination.page - 1) * pagination.rows + " rows fetch next " + pagination.rows + " rows only";
DataView dvInventory = DbHelper.ExecuteToDataView(strSql + strQry + strOrder + strPaging);
List <object> inventorys = new List <object>();
foreach (DataRowView drv in dvInventory)
{
FileApp fileApp = new FileApp();
var files = fileApp.GetList(Convert.ToString(drv["Id"]));
List <object> img = new List <object>();
foreach (FileEntity file in files)
{
//baer64改成url，参数名不变了
//img.Add(new { base64 = file.FileContent });
if (!string.IsNullOrEmpty(file.FilePath))
{
img.Add(new { base64 = file.FilePath.Replace("\\", "/") });
}
}
inventorys.Add(new
{
f_Id = Convert.ToString(drv["Id"]),
wareName = Convert.ToString(drv["WareName"]),
orderNo = Convert.ToString(drv["OrderNo"]),
productType = Convert.ToString(drv["ProductType"]),
productImg = Convert.ToString(drv["ProductImg"]),
grade = Convert.ToString(drv["Grade"]),
strength = Convert.ToString(drv["Strength"]),
length = Convert.ToString(drv["Length"]),
horseValue = Convert.ToString(drv["HorseValue"]),
status = Convert.ToString(drv["Status"]),
quoteType = Convert.ToString(drv["QuoteType"]),
price = Convert.ToString(drv["Price"]),
contract = Convert.ToString(drv["Contract"]),
contractPrice = Convert.ToString(drv["ContractPrice"]),
basis = Convert.ToString(drv["Basis"]),
totalPrice = Convert.ToString(drv["TotalPrice"]),
year = Convert.ToString(drv["Year"]),
sailingSchedule = Convert.ToString(drv["SailingSchedule"]),
weight = Convert.ToString(drv["Weight"]),
isRecommend = Convert.ToString(drv["IsRecommend"]),
fs = Convert.ToString(drv["FS"]),
m = Convert.ToString(drv["M"]),
s = Convert.ToString(drv["S"]),
c = Convert.ToString(drv["C"]),
v = Convert.ToString(drv["V"]),
imgList = img
});
}
return(Content(inventorys.ToJson()));
}
public ActionResult GetInventoryListJson(InventoryReq inventoryReq)
{
Pagination pagination = inventoryReq.pagination;
InventoryQry[] inventoryQrys = inventoryReq.inventoryQry;
//sql
string strSql = @"
select
WareName,
OrderNo,
(select ProductName from Product where ProductCode=a.ProductType) as ProductType,
(select F_ItemName from View_ItemType_ItemDetail where F_EnCode='0202' and F_ItemCode=a.Grade) as Grade,
Strength,
(select F_ItemName from View_ItemType_ItemDetail where F_EnCode='0208' and F_ItemCode=a.Grade) as Length,
(select F_ItemName from View_ItemType_ItemDetail where F_EnCode='0203' and F_ItemCode=a.HorseValue) as HorseValue,
(select F_ItemName from View_ItemType_ItemDetail where F_EnCode='0207' and F_ItemCode=a.Status) as Status,
(select F_ItemName from View_ItemType_ItemDetail where F_EnCode='0206' and F_ItemCode=a.QuoteType) as QuoteType,
Price,
(select F_ItemName from View_ItemType_ItemDetail where F_EnCode='0206' and F_ItemCode=a.Contract) as Contract,
Basis,
Year,
SailingSchedule,
Weight,
IsRecommend
from Inventory a
where 1=1 ";
//where
string strQry = "";
foreach (InventoryQry inventoryQry in inventoryQrys)
{
if (inventoryQry.code.ToLower() == "strength")//强力是范围，其他都是选项
{
foreach (SelectedList selected in inventoryQry.selectedList)
{
if (selected.name.ToLower() == "min")
{
strQry += " and cast(Strength as float)>=" + selected.code + " ";
}
else if (selected.name.ToLower() == "max")
{
strQry += " and cast(Strength as float)<=" + selected.code + " ";
}
}
}
else
{
string condition = "";
foreach (SelectedList selected in inventoryQry.selectedList)
{
if (selected.selected)
{
condition += "'" + selected.code + "',";
}
}
if (!string.IsNullOrEmpty(condition))
{
strQry += " and " + inventoryQry.code + " in (" + condition.Trim(',') + ") ";
}
}
}
//order
string strOrder = " order by IsRecommend desc,F_CreatorTime desc ";
//paging
string strPaging = " offset " + (pagination.page - 1) * pagination.rows + " rows fetch next " + pagination.rows + " rows only";
DataView dvInventory = DbHelper.ExecuteToDataView(strSql + strQry + strOrder + strPaging);
List <object> inventorys = new List <object>();
foreach (DataRowView drv in dvInventory)
{
inventorys.Add(new
{
wareName = Convert.ToString(drv["WareName"]),
orderNo = Convert.ToString(drv["OrderNo"]),
productType = Convert.ToString(drv["ProductType"]),
grade = Convert.ToString(drv["Grade"]),
strength = Convert.ToString(drv["Strength"]),
length = Convert.ToString(drv["Length"]),
horseValue = Convert.ToString(drv["HorseValue"]),
status = Convert.ToString(drv["Status"]),
quoteType = Convert.ToString(drv["QuoteType"]),
price = Convert.ToString(drv["Price"]),
contract = Convert.ToString(drv["Contract"]),
basis = Convert.ToString(drv["Basis"]),
year = Convert.ToString(drv["Year"]),
sailingSchedule = Convert.ToString(drv["SailingSchedule"]),
weight = Convert.ToString(drv["Weight"]),
isRecommend = Convert.ToString(drv["IsRecommend"])
});
}
return(Content(inventorys.ToJson()));
}
