/// <summary>
///
/// </summary>
/// <param name="productId"></param>
/// <returns></returns>
public List <BProduct> GetProductProperties(int productId)
{
List <BProduct> props = null;
using (KuanMaiEntities db = new KuanMaiEntities())
{
Product product = (from p in db.Product where p.Product_ID == productId select p).FirstOrDefault <Product>();
if (product == null)
{
throw new KMJXCException("编号为:" + productId + " 的产品不存在");
}
var mall_id = from m in db.Mall_Product
where m.Outer_ID == productId
select m.Mall_ID;
var children_id = from sku in db.Mall_Product_Sku
where mall_id.Contains(sku.Mall_ID) && sku.Outer_ID > 0
select sku.Outer_ID;
props = (from p in db.Product
where p.Parent_ID == productId && !children_id.Contains(p.Product_ID)
select new BProduct
{
ID = p.Product_ID,
Title = p.Name
}).ToList <BProduct>();
if (props == null || props.Count <= 0)
{
throw new KMJXCException("编号为:" + productId + " 的产品不存在未关联的销售属性，请先编辑产品添加属性");
}
int[] tmpProducts = (from p in props select p.ID).ToArray <int>();
List <BProductProperty> properties = new List <BProductProperty>();
properties = (from pv in db.Product_Specifications
join prop in db.Product_Spec on pv.Product_Spec_ID equals prop.Product_Spec_ID into LProp
from l_prop in LProp.DefaultIfEmpty()
join propV in db.Product_Spec_Value on pv.Product_Spec_Value_ID equals propV.Product_Spec_Value_ID into LPropv
from l_propv in LPropv.DefaultIfEmpty()
where tmpProducts.Contains(pv.Product_ID)
select new BProductProperty
{
PID = pv.Product_Spec_ID,
PName = l_prop.Name,
ProductID = pv.Product_ID,
PValue = l_propv.Name,
PVID = pv.Product_Spec_Value_ID
}).OrderBy(p => p.PID).ToList <BProductProperty>();
foreach (BProduct prop in props)
{
prop.Properties = (from prp in properties where prp.ProductID == prop.ID select prp).ToList <BProductProperty>();
}
}
return(props);
}
/// <summary>
///
/// </summary>
/// <param name="title"></param>
/// <param name="description"></param>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="total"></param>
/// <returns></returns>
public List <BProduct> SearchProducts(int[] product_ids, int[] suppliers, string title, string description, int startTime, int endTime, int?category_id, int pageIndex, int pageSize, out int total, bool includeProps = false, bool paging = true, bool includeSupplier = false)
{
total = 0;
List <BProduct> products = new List <BProduct>();
if (pageIndex <= 0)
{
pageIndex = 1;
}
if (pageSize <= 0)
{
pageSize = 30;
}
using (KuanMaiEntities db = new KuanMaiEntities())
{
int[] childshop_ids = (from c in this.DBChildShops select c.Shop_ID).ToArray <int>();
var dbps = from product in db.Product
//join employee in db.Employee on product.User_ID equals employee.User_ID
where product.Parent_ID == 0
//&& (product.Shop_ID == this.Shop.Shop_ID || product.Shop_ID == this.Main_Shop.Shop_ID || childshop_ids.Contains(product.Shop_ID))
select new
{
Pdt = product,
//Emp=employee,
};
if (product_ids != null && product_ids.Length > 0)
{
dbps = dbps.Where(a => product_ids.Contains(a.Pdt.Product_ID));
}
if (childshop_ids != null && childshop_ids.Length > 0)
{
dbps = dbps.Where(a => a.Pdt.Shop_ID == this.Shop.Shop_ID || a.Pdt.Shop_ID == this.Main_Shop.Shop_ID || childshop_ids.Contains(a.Pdt.Shop_ID));
}
else
{
dbps = dbps.Where(a => a.Pdt.Shop_ID == this.Shop.Shop_ID || a.Pdt.Shop_ID == this.Main_Shop.Shop_ID);
}
if (!string.IsNullOrEmpty(title))
{
dbps = dbps.Where(a => a.Pdt.Name.Contains(title.Trim()));
}
if (!string.IsNullOrEmpty(description))
{
dbps = dbps.Where(a => a.Pdt.Description.Contains(description));
}
if (startTime > 0)
{
dbps = dbps.Where(a => a.Pdt.Create_Time >= startTime);
}
if (endTime > 0)
{
dbps = dbps.Where(a => a.Pdt.Create_Time <= endTime);
}
if (suppliers != null && suppliers.Length > 0)
{
//int[] pdtIds=(from ps in db.Product_Supplier where suppliers.Contains(ps.Supplier_ID) && ps.Enabled==true select ps.Product_ID).ToArray<int>();
var pdtIds = from ps in db.Product_Supplier where suppliers.Contains(ps.Supplier_ID) && ps.Enabled == true orderby ps.Created descending select ps.Product_ID;
dbps = dbps.Where(a => pdtIds.Contains(a.Pdt.Product_ID));
}
if (category_id != null)
{
Product_Class cate = (from ca in db.Product_Class where ca.Product_Class_ID == category_id select ca).FirstOrDefault <Product_Class>();
if (cate != null)
{
if (cate.Parent_ID == 0)
{
int[] ccids = (from c in db.Product_Class where c.Parent_ID == category_id select c.Product_Class_ID).ToArray <int>();
if (ccids != null && ccids.Length > 0)
{
dbps = dbps.Where(a => (ccids.Contains(a.Pdt.Product_Class_ID) || a.Pdt.Product_Class_ID == category_id));
}
else
{
dbps = dbps.Where(a => a.Pdt.Product_Class_ID == category_id);
}
}
else
{
dbps = dbps.Where(a => a.Pdt.Product_Class_ID == category_id);
}
}
}
dbps = dbps.OrderBy(a => a.Pdt.Shop_ID).OrderBy(b => b.Pdt.Create_Time);
total = dbps.Count();
if (total > 0)
{
var bps = from bpss in dbps
select new BProduct
{
Description = bpss.Pdt.Description,
Shop = (from sp in db.Shop where sp.Shop_ID == bpss.Pdt.Shop_ID select sp).FirstOrDefault <Shop>(),
Price = bpss.Pdt.Price,
ID = bpss.Pdt.Product_ID,
Title = bpss.Pdt.Name,
CreateTime = bpss.Pdt.Create_Time,
Code = bpss.Pdt.Code,
Unit = (from u in db.Product_Unit where u.Product_Unit_ID == bpss.Pdt.Product_Unit_ID select u).FirstOrDefault <Product_Unit>(),
Category = (from c in db.Product_Class
where bpss.Pdt.Product_Class_ID == c.Product_Class_ID
select new BCategory
{
Name = c.Name,
ID = c.Product_Class_ID,
}).FirstOrDefault <BCategory>(),
User = (from u in db.User
where u.User_ID == bpss.Pdt.User_ID
select new BUser
{
ID = u.User_ID,
Mall_Name = u.Mall_Name,
Mall_ID = u.Mall_ID,
}).FirstOrDefault <BUser>()
};
if (paging)
{
products = bps.OrderBy(a => a.ID).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList <BProduct>();
}
else
{
products = bps.OrderBy(a => a.ID).ToList <BProduct>();
}
}
List <BProduct> childProducts = null;
List <BProductProperty> properties = null;
if (includeProps)
{
int[] parent_ids = (from p in products select p.ID).ToArray <int>();
var tmpchildProducts = from p in db.Product
where parent_ids.Contains(p.Parent_ID)
select new BProduct
{
ID = p.Product_ID,
Title = p.Name,
ParentID = p.Parent_ID
};
childProducts = tmpchildProducts.ToList <BProduct>();
int[] child_product_ids = (from c in childProducts select c.ID).ToArray <int>();
properties = (from pv in db.Product_Specifications
join prop in db.Product_Spec on pv.Product_Spec_ID equals prop.Product_Spec_ID into LProp
from l_prop in LProp.DefaultIfEmpty()
join propV in db.Product_Spec_Value on pv.Product_Spec_Value_ID equals propV.Product_Spec_Value_ID into LPropv
from l_propv in LPropv.DefaultIfEmpty()
where child_product_ids.Contains(pv.Product_ID)
select new BProductProperty
{
PID = pv.Product_Spec_ID,
PName = l_prop.Name,
ProductID = pv.Product_ID,
PValue = l_propv.Name,
PVID = pv.Product_Spec_Value_ID
}).ToList <BProductProperty>();
}
List <BProductSupplier> bsuppliers = new List <BProductSupplier>();
if (includeSupplier)
{
int[] parent_ids = (from p in products select p.ID).ToArray <int>();
bsuppliers = (from s in db.Product_Supplier
join supplier in db.Supplier on s.Supplier_ID equals supplier.Supplier_ID into LSupplier
from l_supplier in LSupplier.DefaultIfEmpty()
where parent_ids.Contains(s.Product_ID)
select new BProductSupplier
{
Supplier = new BSupplier {
ID = s.Supplier_ID, Name = l_supplier.Name
},
Product = new BProduct {
ID = s.Product_ID
}
}).ToList <BProductSupplier>();
}
foreach (BProduct product in products)
{
if (product.Shop.Shop_ID == this.Main_Shop.Shop_ID)
{
product.FromMainShop = true;
}
else if (childshop_ids != null && childshop_ids.Length > 0 && childshop_ids.Contains(product.Shop.Shop_ID))
{
product.FromChildShop = true;
}
if (includeProps)
{
List <BProduct> children = (from c in childProducts where c.ParentID == product.ID select c).ToList <BProduct>();
if (children.Count > 0)
{
product.Children = new List <BProduct>();
foreach (BProduct child in children)
{
child.Properties = (from prop in properties where prop.ProductID == child.ID select prop).ToList <BProductProperty>();
product.Children.Add(child);
}
}
}
if (includeSupplier)
{
product.Suppliers = (from ps in bsuppliers where ps.Product.ID == product.ID select new Supplier {
Supplier_ID = ps.Supplier.ID, Name = ps.Supplier.Name
}).ToList <Supplier>();
}
}
}
return(products);
}
/// <summary>
/// Gets buy orders report in json format
/// </summary>
/// <param name="startDate">Buy order start date</param>
/// <param name="endDate">Buy order end date</param>
/// <param name="product_id">A array of product ids</param>
/// <param name="page">Page index</param>
/// <param name="pageSize">Page size</param>
/// <param name="totalProducts">Total records according to the conditions</param>
/// <param name="paging">Paging(true/false)</param>
/// <returns></returns>
public JToken[] GetBuyReport(long startDate, long endDate, int[] product_id, int page, int pageSize, out int totalProducts, bool paging = true)
{
string json = "";
totalProducts = 0;
if (this.CurrentUserPermission.VIEW_BUY_REPORT == 0)
{
throw new KMJXCException("没有权限查看采购报表");
}
if (page <= 0)
{
page = 1;
}
if (pageSize <= 0)
{
pageSize = 20;
}
using (KuanMaiEntities db = new KuanMaiEntities())
{
int[] childShops = (from c in this.DBChildShops select c.Shop_ID).ToArray <int>();
List <BProduct> products = null;
var tmpProducts = from p in db.Product
join shop in db.Shop on p.Shop_ID equals shop.Shop_ID into LShop
from l_shop in LShop.DefaultIfEmpty()
where p.Parent_ID == 0
select new BProduct
{
ID = p.Product_ID,
BShop = new BShop {
ID = p.Shop_ID, Title = l_shop.Name
},
Title = p.Name
};
if (this.Shop.Shop_ID == this.Main_Shop.Shop_ID)
{
tmpProducts = tmpProducts.Where(p => (p.BShop.ID == this.Shop.Shop_ID || childShops.Contains(p.BShop.ID)));
}
else
{
tmpProducts = tmpProducts.Where(p => p.BShop.ID == this.Shop.Shop_ID);
}
if (product_id != null && product_id.Length > 0)
{
tmpProducts = tmpProducts.Where(p => product_id.Contains(p.ID));
}
var tmpBuyOrders = from bo in db.Buy_Order
select bo;
if (this.Shop.Shop_ID == this.Main_Shop.Shop_ID)
{
tmpBuyOrders = tmpBuyOrders.Where(o => (o.Shop_ID == this.Shop.Shop_ID || childShops.Contains(o.Shop_ID)));
}
else
{
tmpBuyOrders = tmpBuyOrders.Where(o => o.Shop_ID == this.Shop.Shop_ID);
}
if (startDate > 0)
{
tmpBuyOrders = tmpBuyOrders.Where(o => o.Create_Date > startDate);
}
if (endDate > 0)
{
tmpBuyOrders = tmpBuyOrders.Where(o => o.Create_Date <= endDate);
}
var tmpBuyOrderIds = from o in tmpBuyOrders select o.Buy_Order_ID;
var tmpBuyOrderDetails = from bd in db.Buy_Order_Detail
where tmpBuyOrderIds.Contains(bd.Buy_Order_ID)
select bd;
var orderProductIds = from o in tmpBuyOrderDetails select o.Parent_Product_ID;
tmpProducts = tmpProducts.Where(p => orderProductIds.Contains(p.ID));
if (paging)
{
products = tmpProducts.OrderBy(p => p.ID).Skip((page - 1) * page).Take(pageSize).ToList <BProduct>();
}
else
{
products = tmpProducts.OrderBy(p => p.ID).ToList <BProduct>();
}
int[] parent_pIds = (from p in products select p.ID).ToArray <int>();
List <Product> childrenProducts = (from p in db.Product where parent_pIds.Contains(p.Parent_ID) select p).ToList <Product>();
int[] child_pIds = (from p in childrenProducts select p.Product_ID).ToArray <int>();
List <BProductProperty> properties = (from pv in db.Product_Specifications
join prop in db.Product_Spec on pv.Product_Spec_ID equals prop.Product_Spec_ID into LProp
from l_prop in LProp.DefaultIfEmpty()
join propV in db.Product_Spec_Value on pv.Product_Spec_Value_ID equals propV.Product_Spec_Value_ID into LPropv
from l_propv in LPropv.DefaultIfEmpty()
where child_pIds.Contains(pv.Product_ID)
select new BProductProperty
{
PID = pv.Product_Spec_ID,
PName = l_prop.Name,
ProductID = pv.Product_ID,
PValue = l_propv.Name,
PVID = pv.Product_Spec_Value_ID
}).OrderBy(p => p.PID).ToList <BProductProperty>();
List <Buy_Order> orders = (from o in tmpBuyOrders select o).ToList <Buy_Order>();
List <BBuyOrderDetail> details = (from detail in tmpBuyOrderDetails
join order in tmpBuyOrders on detail.Buy_Order_ID equals order.Buy_Order_ID into LOrder
from l_order in LOrder.DefaultIfEmpty()
select new BBuyOrderDetail
{
Product = new BProduct {
ID = detail.Product_ID
},
Parent_Product_ID = detail.Parent_Product_ID,
BuyDate = l_order.Insure_Date != null && l_order.Insure_Date > 0 ? (long)l_order.Insure_Date : l_order.Create_Date,
Quantity = detail.Quantity,
Price = detail.Price
}).OrderBy(d => d.BuyDate).ToList <BBuyOrderDetail>();
foreach (BProduct product in products)
{
string productName = product.ID + " " + product.Title;
string shopName = product.BShop.Title;
List <Product> children = (from c in childrenProducts where c.Parent_ID == product.ID select c).ToList <Product>();
if (children == null || children.Count <= 0)
{
var vdetails = from d in details where d.Parent_Product_ID == product.ID select d;
foreach (var detail in vdetails)
{
string month = DateTimeUtil.ConvertToDateTime(detail.BuyDate).ToString("yyyy-M");
if (json == "")
{
json = "[{\"product_name\":\"" + productName + "\",\"prop_name\":\"--\",\"shop_name\":\"" + shopName + "\",\"month\":\"" + month + "\",\"quantity\":\"" + detail.Quantity + "\",\"amount\":" + (detail.Quantity * detail.Price).ToString("0.00") + "}";
}
else
{
json += ",{\"product_name\":\"" + productName + "\",\"prop_name\":\"--\",\"shop_name\":\"" + shopName + "\",\"month\":\"" + month + "\",\"quantity\":\"" + detail.Quantity + "\",\"amount\":" + (detail.Quantity * detail.Price).ToString("0.00") + "}";
}
}
}
else
{
int[] childrenIds = (from c in children select c.Product_ID).ToArray <int>();
var vdetails = from d in details where childrenIds.Contains(d.Product.ID) select d;
foreach (var detail in vdetails)
{
string pNames = "";
List <BProductProperty> props = (from p in properties where p.ProductID == detail.Product.ID select p).ToList <BProductProperty>();
foreach (BProductProperty prop in props)
{
if (pNames == "")
{
pNames = prop.PName + ":" + prop.PValue;
}
else
{
pNames += "," + prop.PName + ":" + prop.PValue;
}
}
string month = DateTimeUtil.ConvertToDateTime(detail.BuyDate).ToString("yyyy-M");
if (json == "")
{
json = "[{\"product_name\":\"" + productName + "\",\"prop_name\":\"" + detail.Product.ID + " " + pNames + "\",\"shop_name\":\"" + shopName + "\",\"month\":\"" + month + "\",\"quantity\":\"" + detail.Quantity + "\",\"amount\":" + (detail.Quantity * detail.Price).ToString("0.00") + "}";
}
else
{
json += ",{\"product_name\":\"" + productName + "\",\"prop_name\":\"" + detail.Product.ID + " " + pNames + "\",\"shop_name\":\"" + shopName + "\",\"month\":\"" + month + "\",\"quantity\":\"" + detail.Quantity + "\",\"amount\":" + (detail.Quantity * detail.Price).ToString("0.00") + "}";
}
}
}
}
}
if (json != "")
{
json += "]";
}
JArray ja = JArray.Parse(json);
JToken[] jac = (from j in ja orderby j["month"] select j).ToArray <JToken>();
return(jac);
}
/// <summary>
/// Gets stock report in json format
/// </summary>
/// <param name="products">A array of products' id</param>
/// <param name="page">Page index</param>
/// <param name="pageSize">Page size</param>
/// <param name="total">Total records</param>
/// <param name="paging">Paging</param>
/// <returns>Json string</returns>
public string GetStockReport(int[] products, int page, int pageSize, out int total, bool paging = true)
{
if (this.CurrentUserPermission.VIEW_STOCK_REPORT == 0)
{
throw new KMJXCException("没有权限查看库存报表");
}
string json = "";
total = 0;
if (page <= 0)
{
page = 1;
}
if (pageSize <= 0)
{
pageSize = 30;
}
using (KuanMaiEntities db = new KuanMaiEntities())
{
var stocks = from stock in db.Stock_Pile
select stock;
var pdts = from product in db.Product
join shop in db.Shop on product.Shop_ID equals shop.Shop_ID into Lshop
from l_shop in Lshop.DefaultIfEmpty()
where product.Parent_ID == 0
select new BProduct
{
ID = product.Product_ID,
Title = product.Name,
BShop = l_shop != null ? new BShop {
ID = product.Shop_ID, Title = l_shop.Name
} : new BShop {
ID = 0, Title = ""
}
};
if (products != null && products.Length > 0)
{
pdts = pdts.Where(p => products.Contains(p.ID));
}
List <BProduct> dbproducts = null;
total = pdts.Count();
if (paging)
{
dbproducts = pdts.OrderBy(p => p.ID).Skip((page - 1) * pageSize).Take(pageSize).ToList <BProduct>();
}
else
{
dbproducts = pdts.ToList <BProduct>();
}
int[] pPIds = (from p in dbproducts select p.ID).ToArray <int>();
List <Product> dbChildrenProducts = (from p in db.Product where pPIds.Contains(p.Parent_ID) select p).ToList <Product>();
int[] cPIds = (from p in dbChildrenProducts select p.Product_ID).ToArray <int>();
List <Stock_Pile> sPiles = (from s in stocks where pPIds.Contains(s.Product_ID) || cPIds.Contains(s.Product_ID) select s).ToList <Stock_Pile>();
List <BProductProperty> properties = (from pv in db.Product_Specifications
join prop in db.Product_Spec on pv.Product_Spec_ID equals prop.Product_Spec_ID into LProp
from l_prop in LProp.DefaultIfEmpty()
join propV in db.Product_Spec_Value on pv.Product_Spec_Value_ID equals propV.Product_Spec_Value_ID into LPropv
from l_propv in LPropv.DefaultIfEmpty()
where cPIds.Contains(pv.Product_ID)
select new BProductProperty
{
PID = pv.Product_Spec_ID,
PName = l_prop.Name,
ProductID = pv.Product_ID,
PValue = l_propv.Name,
PVID = pv.Product_Spec_Value_ID
}).OrderBy(p => p.PID).ToList <BProductProperty>();
foreach (BProduct pdt in dbproducts)
{
string sJson = "";
string productName = pdt.ID + " " + pdt.Title;
string propName = "";
int quantity = 0;
string shopName = pdt.BShop.Title;
List <Product> children = (from p in dbChildrenProducts where p.Parent_ID == pdt.ID select p).ToList <Product>();
if (children == null || children.Count <= 0)
{
propName = "--";
quantity = (from s in sPiles where s.Product_ID == pdt.ID select s.Quantity).Sum();
sJson = "{\"product_name\":\"" + productName + "\",\"prop_name\":\"" + propName + "\",\"shop_name\":\"" + shopName + "\",\"quantity\":" + quantity.ToString() + ",\"pivot\":\"库存\"}";
}
else
{
foreach (Product child in children)
{
propName = "";
quantity = (from s in sPiles where s.Product_ID == child.Product_ID select s.Quantity).Sum();
List <BProductProperty> props = (from p in properties where p.ProductID == child.Product_ID select p).ToList <BProductProperty>();
foreach (BProductProperty prop in props)
{
if (propName == "")
{
propName = prop.PName + ":" + prop.PValue;
}
else
{
propName += ";" + prop.PName + ":" + prop.PValue;
}
}
if (sJson == "")
{
sJson = "{\"product_name\":\"" + productName + "\",\"prop_name\":\"" + child.Product_ID + " " + propName + "\",\"shop_name\":\"" + shopName + "\",\"quantity\":" + quantity.ToString() + ",\"pivot\":\"库存\"}";
}
else
{
sJson += ",{\"product_name\":\"" + productName + "\",\"prop_name\":\"" + child.Product_ID + " " + propName + "\",\"shop_name\":\"" + shopName + "\",\"quantity\":" + quantity.ToString() + ",\"pivot\":\"库存\"}";
}
}
}
if (!string.IsNullOrEmpty(sJson))
{
if (json == "")
{
json = "[" + sJson;
}
else
{
json += "," + sJson;
}
}
}
if (!string.IsNullOrEmpty(json))
{
json += "]";
}
}
return(json);
}