/// <summary>
/// 查询保养品分仓信息
/// </summary>
/// <param name="pid"></param>
/// <returns></returns>
public static List <Product_SalespredictData> SelectProductSalespredictData(string pid)
{
List <Product_SalespredictData> result = new List <Product_SalespredictData>();
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Tuhu_BI")))
{
var sql = @"SELECT pid ,
ISNULL(totalstock, 0) AS totalstock ,
ISNULL(num_threemonth, 0) AS num_threemonth ,
ISNULL(num_month, 0) AS num_month ,
ISNULL(num_week, 0) AS num_week
FROM Tuhu_bi..dm_Product_SalespredictData (NOLOCK)
WHERE pid = @pid;
SELECT pid ,
warehouseid ,
warehousename ,
ISNULL(stocknum, 0) AS totalstock ,
ISNULL(num_threemonth, 0) AS num_threemonth ,
ISNULL(num_month, 0) AS num_month ,
ISNULL(num_week, 0) AS num_week
FROM Tuhu_bi..dm_Product_Warehouse_SalespredictData (NOLOCK)
WHERE pid = @pid
AND warehouseid IS NOT NULL;";
var param = new SqlParameter("@Pid", pid);
var ds = dbHelper.ExecuteDataSet(sql, CommandType.Text, param);
var totalData = ds.Tables[0];
if (totalData != null && totalData.Rows.Count > 0)
{
var item = new Product_SalespredictData
{
WareHouseName = "总库存",
TotalStock = Convert.ToInt32(totalData.Rows[0]["totalstock"]),
Num_ThreeMonth = Convert.ToInt32(totalData.Rows[0]["num_threemonth"]),
Num_Month = Convert.ToInt32(totalData.Rows[0]["num_month"]),
Num_Week = Convert.ToInt32(totalData.Rows[0]["num_week"]),
};
result.Add(item);
}
var regionData = ds.Tables[1];
if (regionData != null && regionData.Rows.Count > 0)
{
foreach (DataRow row in regionData.Rows)
{
var item = new Product_SalespredictData
{
WareHouseName = row["warehousename"].ToString(),
TotalStock = Convert.ToInt32(row["totalstock"]),
Num_ThreeMonth = Convert.ToInt32(row["num_threemonth"]),
Num_Month = Convert.ToInt32(row["num_month"]),
Num_Week = Convert.ToInt32(row["num_week"]),
};
result.Add(item);
}
}
return(result);
}
}
/// <summary>
/// 查询保养品分仓信息
/// </summary>
/// <param name="pid"></param>
/// <returns></returns>
public static List <Product_SalespredictData> SelectProductSalespredictData(IEnumerable <string> pids)
{
List <Product_SalespredictData> result = new List <Product_SalespredictData>();
using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Tuhu_BI")))
{
var sql = @"SELECT pid ,
ISNULL(totalstock, 0) AS totalstock ,
ISNULL(num_threemonth, 0) AS num_threemonth ,
ISNULL(num_month, 0) AS num_month ,
ISNULL(num_week, 0) AS num_week
FROM Tuhu_bi..dm_Product_SalespredictData (NOLOCK)
WHERE pid = @pid;
SELECT pid ,
warehouseid ,
warehousename ,
WarehouseType ,
ISNULL(stocknum, 0) AS totalstock ,
ISNULL(num_threemonth, 0) AS num_threemonth ,
ISNULL(num_month, 0) AS num_month ,
ISNULL(num_week, 0) AS num_week
FROM Tuhu_bi..dm_Product_Warehouse_SalespredictData (NOLOCK)
WHERE EXISTS (
SELECT 1
FROM Tuhu_bi..SplitString(@pids, ',', 1) AS pids
WHERE pids.Item = pid
)
AND warehouseid IS NOT NULL;";
var parameters = new SqlParameter[]
{
new SqlParameter("@pids", string.Join(",", pids)),
new SqlParameter("@pid", pids.FirstOrDefault())
};
var ds = dbHelper.ExecuteDataSet(sql, CommandType.Text, parameters);
var totalData = ds.Tables[0];
if (totalData != null && totalData.Rows.Count > 0)
{
var item = new Product_SalespredictData
{
WareHouseName = "总库存",
TotalStock = Convert.ToInt32(totalData.Rows[0]["totalstock"]),
Num_ThreeMonth = Convert.ToInt32(totalData.Rows[0]["num_threemonth"]),
Num_Month = Convert.ToInt32(totalData.Rows[0]["num_month"]),
Num_Week = Convert.ToInt32(totalData.Rows[0]["num_week"]),
};
result.Add(item);
}
var regionData = ds.Tables[1];
if (regionData != null && regionData.Rows.Count > 0)
{
foreach (DataRow row in regionData.Rows)
{
var item = new Product_SalespredictData
{
Pid = row["pid"].ToString(),
WareHouseName = row["warehousename"].ToString(),
TotalStock = Convert.ToInt32(row["totalstock"]),
Num_ThreeMonth = Convert.ToInt32(row["num_threemonth"]),
Num_Month = Convert.ToInt32(row["num_month"]),
Num_Week = Convert.ToInt32(row["num_week"]),
WarehouseType = row["WarehouseType"]?.ToString() ?? string.Empty
};
result.Add(item);
}
}
return(result);
}
}
