/// <summary>
/// 添加类型
/// </summary>
/// <param name="pt">商品类别对象</param>
/// <returns></returns>
public static int AddProType(ProductTypes pt)
{
PSSEntities db = new PSSEntities();
db.ProductTypes.Add(pt);
return(db.SaveChanges());
}
/// <summary>
/// 条件查询
/// </summary>
/// <param name="PIDID"></param>
/// <param name="DepotID"></param>
/// <param name="PIDDate"></param>
/// <param name="UsersName"></param>
/// <param name="PIDState"></param>
/// <param name="PageIndex"></param>
/// <param name="PageSize"></param>
/// <param name="count"></param>
/// <returns></returns>
public static List <CheckDepot> Find(string CDID, string DepotID, string UsersName, string CDDate, int CDState, int PageIndex, int PageSize, out int count)
{
PSSEntities db = new PSSEntities();
var list = from s in db.CheckDepot select s;
if (CDID != null && CDID.Trim().Length > 0)//入库单编号
{
list = list.Where(s => s.CDID.Contains(CDID));
}
if (DepotID != null && DepotID.Trim().Length > 0)//盘点仓库
{
list = list.Where(s => s.DepotID.Equals(DepotID));
}
if (CDDate != null && CDDate.Trim().Length > 0)//入库时间
{
DateTime dt = Convert.ToDateTime(CDDate);
list = list.Where(s => s.CDDate >= dt);
}
if (UsersName != null && UsersName.Trim().Length > 0)//创建人
{
list = list.Where(s => s.Users.UsersName.Contains(UsersName));
}
if (CDState != -1)//订单状态
{
list = list.Where(s => s.CDState == CDState);
}
count = list.Count();
return(list.OrderBy(s => s.CDID).Skip((PageIndex - 1) * PageSize).Take(PageSize).ToList());
}
/// <summary>
/// 查询所有商品类别信息
/// </summary>
/// <returns></returns>
public static List <ProductTypes> GetAll()
{
PSSEntities db = new PSSEntities();
var linqlist = db.ProductTypes.Select(p => p).ToList();
return(linqlist as List <ProductTypes>);
}
/// <summary>
/// 添加客户等级
/// </summary>
/// <param name="cusl"></param>
/// <returns></returns>
public static int Add(CustomerLevel cusl)
{
PSSEntities db = new PSSEntities();
db.CustomerLevel.Add(cusl);
return(db.SaveChanges());
}
/// <summary>
/// 修改【---事务---】
/// </summary>
/// <param name="dp"></param>
/// <returns></returns>
public static int EdiStocks(Splits dep, List <SplitDetail> list)
{
PSSEntities db = new PSSEntities();
int fg = 1;
///以下开始事务操作
using (var tx = db.Database.BeginTransaction())
{
try
{
//将要修改的创建人找回来，避免修改时没有创建人
Splits st = db.Splits.FirstOrDefault(p => p.SplitID == dep.SplitID);
st.DepotID = dep.DepotID; //商品仓库
st.ProID = dep.ProID; //拆分商品
st.SplitDate = dep.SplitDate; //拆分时间
st.SplitDesc = dep.SplitDesc; //备注
st.SplitAmount = dep.SplitAmount; //拆分数量
st.SplitPrice = dep.SplitPrice; //价格
db.SplitDetail.RemoveRange(db.SplitDetail.Where(p => p.SplitID == dep.SplitID)); //删除详单
if (list != null)
{
db.SplitDetail.AddRange(list); //如果添加的详单不为空的话，那么就添加详单
}
db.SaveChanges();
tx.Commit();
}
catch (Exception e)
{
fg = 0;
tx.Rollback();
throw new Exception(e.Message);
}
}
return(fg);
}
/// <summary>
/// 修改【---事务---】
/// </summary>
/// <param name="dp"></param>
/// <returns></returns>
public static int EdiStocks(CheckDepot dep, List <CheckDepotDetail> list)
{
PSSEntities db = new PSSEntities();
int fg = 1;
///以下开始事务操作
using (var tx = db.Database.BeginTransaction())
{
try
{
//将要修改的创建人找回来，避免修改时没有创建人
CheckDepot st = db.CheckDepot.FirstOrDefault(p => p.CDID == dep.CDID);
st.DepotID = dep.DepotID; //盘点仓库
st.CDDate = dep.CDDate; //盘点时间
st.CDDesc = dep.CDDesc; //备注
db.CheckDepotDetail.RemoveRange(db.CheckDepotDetail.Where(p => p.CDID == dep.CDID)); //删除详单
if (list != null)
{
db.CheckDepotDetail.AddRange(list); //如果添加的详单不为空的话，那么就添加详单
}
db.SaveChanges();
tx.Commit();
}
catch (Exception e)
{
fg = 0;
tx.Rollback();
throw new Exception(e.Message);
}
}
return(fg);
}
/// <summary>
/// 查询出入库记录
/// </summary>
/// <param name="ProID">商品ID</param>
/// <param name="BDate">开始时间</param>
/// <param name="EDate">结束时间</param>
/// <param name="DepotID">仓库编号</param>
/// <param name="count">查询个数</param>
/// <returns></returns>
public static List <InOutDepotDetail> Find(string ProID, string BDate, string EDate, string DepotID, out int count, int PageIndex)
{
PSSEntities db = new PSSEntities();
var list = from s in db.InOutDepotDetail
select s;
if (ProID != null && ProID.Trim().Length > 0)
{
int id = Convert.ToInt32(ProID);
list = list.Where(c => c.ProID == id);
}
if (BDate != null && BDate.Trim().Length > 0)
{
DateTime dt = Convert.ToDateTime(BDate);
list = list.Where(c => c.InOutDepot.IODDate > dt);
}
if (EDate != null && EDate.Trim().Length > 0)
{
DateTime dt = Convert.ToDateTime(EDate);
list = list.Where(c => c.InOutDepot.IODDate < dt);
}
if (DepotID != null && DepotID.Trim().Length > 0)
{
list = list.Where(c => c.InOutDepot.DepotID.Equals(DepotID));
}
count = list.Count();
return(list.OrderBy(p => p.IODDID).Skip((PageIndex - 1) * 10).Take(10).ToList());
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="index">页数</param>
/// <returns></returns>
public static List <ProductTypes> GetAll(int index)
{
PSSEntities db = new PSSEntities();
var linqlist = db.ProductTypes.Select(p => p).OrderBy(p => p.PTID).Skip(10 * (index - 1)).Take(10).ToList();
return(linqlist as List <ProductTypes>);
}
/// <summary>
/// 多条件查询商品
/// </summary>
/// <param name="ProName">商品名称</param>
/// <param name="PTID">商品类别ID</param>
/// <param name="PUID">商品规格ID</param>
/// <param name="PCID">商品颜色ID</param>
/// <param name="PSID">商品单位ID</param>
/// <param name="count">总条数</param>
/// <param name="PageIndex">当前页</param>
/// <param name="PageSize">页大小</param>
/// <returns></returns>
public static List <Products> Find(string ProName, int PTID, int PUID, int PCID, int PSID, out int count, int PageIndex, int PageSize)
{
PSSEntities db = new PSSEntities();
var list = db.Products.Select(s => s);
if (ProName != null && ProName.Trim().Length > 0)
{
list = list.Where(s => s.ProName.Contains(ProName));
}
if (PTID != -1 && PTID > 0)
{
List <int?> ids = new List <int?>();
ids.Add(PTID);
GetPTID(PTID, ids);
list = list.Where(s => ids.Contains(s.PTID));
}
if (PUID != -1 && PUID > 0)
{
list = list.Where(s => s.PUID == PUID);
}
if (PCID != -1 && PCID > 0)
{
list = list.Where(s => s.PCID == PCID);
}
if (PSID != -1 && PSID > 0)
{
list = list.Where(s => s.PSID == PSID);
}
count = list.Count();
return(list.OrderBy(s => s.ProID).Skip((PageIndex - 1) * PageSize).Take(PageSize).ToList());
}
/// <summary>
/// 修改
/// </summary>
/// <param name="pro"></param>
/// <returns></returns>
public static int Edit(Products pro)
{
PSSEntities db = new PSSEntities();
db.Entry <Products>(pro).State = System.Data.Entity.EntityState.Modified;
return(db.SaveChanges());
}
/// <summary>
/// 审核销售出库单
/// </summary>
/// <param name="id"></param>
/// <param name="userid"></param>
/// <returns></returns>
internal static int CKInDepot(string id, int userid)
{
PSSEntities db = new PSSEntities();
int fg = 1;
using (var tx = db.Database.BeginTransaction())
{
try
{
SaleDepot co = db.SaleDepot.FirstOrDefault(p => p.SDID.Equals(id));
co.SDState = 1;
ObjectParameter para = new ObjectParameter("DD", "");
db.pro_order("InOutDepot", "IODNum", "RK", para);
string IODNum = para.Value.ToString();
//添加入库记录
db.InOutDepot.Add(new InOutDepot()
{
DepotID = co.DepotID, IODType = 2, IODNum = IODNum, IODDate = DateTime.Now, IODUser = userid, IODDesc = co.SDDesc
});
db.SaveChanges();
int inod = db.InOutDepot.Max(i => i.IODID);
List <SaleDepotDetail> list = db.SaleDepotDetail.Where(p => p.SDID.Equals(id)).ToList(); //销售出库详单
List <CustomerOrderDetail> cuslist = db.CustomerOrderDetail.Where(p => p.COID.Equals(co.COID)).ToList(); //客户订单详单
foreach (SaleDepotDetail item in list)
{
//修改客户订单已销数量
foreach (CustomerOrderDetail cus in cuslist)
{
if (cus.ProID == item.ProID)
{
cus.CODSale = item.SDDAmount;
}
}
if (db.DepotStock.FirstOrDefault(p => p.DepotID.Equals(co.DepotID) && p.ProID == item.ProID) == null)
{//如果指定的仓库中不存在该商品
throw new Exception("不存在该商品，操作失败！");
}
else
{//如果指定仓库存在这个商品，修改这个商品的库存
DepotStock ds = db.DepotStock.FirstOrDefault(d => d.ProID == item.ProID && d.DepotID == co.DepotID);
ds.DSAmount = ds.DSAmount - item.SDDAmount;
}
//添加入库记录详情
db.InOutDepotDetail.Add(new InOutDepotDetail()
{
IODID = inod, ProID = item.ProID, IODDAmount = item.SDDAmount, IODDPrice = item.SDDPrice
});
}
db.SaveChanges();
tx.Commit();
}
catch (Exception ex)
{
fg = 0;
tx.Rollback();
throw new Exception(ex.Message);
}
}
return(fg);
}
/// <summary>
/// 条件查询
/// </summary>
/// <param name="COID"></param>
/// <param name="CusID"></param>
/// <param name="CODate"></param>
/// <param name="CORefDate"></param>
/// <param name="UsersName"></param>
/// <param name="COState"></param>
/// <param name="PageIndex"></param>
/// <param name="PageSize"></param>
/// <param name="count"></param>
/// <returns></returns>
internal static List <SaleDepot> Find(string SDID, string CusID, string DepotID, string SDDate, string UsersName, int SDState, int PageIndex, int PageSize, out int count)
{
PSSEntities db = new PSSEntities();
var list = from c in db.SaleDepot select c;
if (SDID != null && SDID.Trim().Length > 0)
{
list = list.Where(p => p.SDID.Contains(SDID));
}
if (CusID != null && CusID.Trim().Length > 0)
{
list = list.Where(p => p.CusID.Equals(CusID));
}
if (DepotID != null && DepotID.Trim().Length > 0)
{
list = list.Where(p => p.DepotID.Equals(DepotID));
}
if (SDDate != null && SDDate.Trim().Length > 0)
{
DateTime dt = Convert.ToDateTime(SDDate);
list = list.Where(p => p.SDDate >= dt);
}
if (UsersName != null && UsersName.Trim().Length > 0)
{
list = list.Where(p => p.Users.UsersName.Contains(UsersName));
}
if (SDState != -1)
{
list = list.Where(p => p.SDState == SDState);
}
count = list.Count();
return(list.OrderBy(p => p.SDID).Skip((PageIndex - 1) * PageSize).Take(PageSize).ToList());
}
/// <summary>
/// 修改【---事务---】
/// </summary>
/// <param name="dp"></param>
/// <returns></returns>
public static int EdiStocks(ProduceOutDepot dep, List <ProduceOutDepotDetail> list)
{
PSSEntities db = new PSSEntities();
int fg = 1;
///以下开始事务操作
using (var tx = db.Database.BeginTransaction())
{
try
{
//将要修改的创建人找回来，避免修改时没有创建人
ProduceOutDepot st = db.ProduceOutDepot.FirstOrDefault(p => p.PODID == dep.PODID);
st.DepotID = dep.DepotID;
st.PODDate = dep.PODDate;
st.PODDesc = dep.PODDesc;
db.ProduceOutDepotDetail.RemoveRange(db.ProduceOutDepotDetail.Where(p => p.PODID == dep.PODID));
if (list != null)
{
db.ProduceOutDepotDetail.AddRange(list);
}
db.SaveChanges();
tx.Commit();
}
catch (Exception e)
{
fg = 0;
tx.Rollback();
throw new Exception(e.Message);
}
}
return(fg);
}
/// <summary>
/// 修改销售出库单
/// </summary>
/// <param name="qp"></param>
/// <param name="list"></param>
/// <returns></returns>
internal static int EditStocks(SaleDepot qp, List <SaleDepotDetail> list)
{
PSSEntities db = new PSSEntities();
int fg = 1;
using (var tx = db.Database.BeginTransaction())
{
try
{
SaleDepot c = db.SaleDepot.FirstOrDefault(p => p.SDID.Equals(qp.SDID));
c.CusID = qp.CusID;
c.DepotID = qp.DepotID;
c.SDState = qp.SDState;
c.SDDesc = qp.SDDesc;
db.SaleDepotDetail.RemoveRange(db.SaleDepotDetail.Where(s => s.SDID.Equals(qp.SDID)));
if (list != null)
{
db.SaleDepotDetail.AddRange(list);
}
db.SaveChanges();
tx.Commit();
}
catch (Exception ex)
{
fg = 0;
tx.Rollback();
throw new Exception(ex.Message);
}
}
return(fg);
}
/// <summary>
/// 添加
/// </summary>
/// <param name="dep"></param>
/// <returns></returns>
public static int AddStockDetail(ProduceInDepotDeteil dep)
{
PSSEntities db = new PSSEntities();
db.ProduceInDepotDeteil.Add(dep);
return(db.SaveChanges());
}
/// <summary>
/// 删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static int Delete(int id)
{
PSSEntities db = new PSSEntities();
db.ProductSpec.Remove(db.ProductSpec.FirstOrDefault(p => p.PSID == id));
return(db.SaveChanges());
}
/// <summary>
/// 添加采购退货单
/// </summary>
/// <param name="qp"></param>
/// <param name="list"></param>
/// <returns></returns>
internal static int AddStocks(StockReturn qp, List <StockReturnDetail> list)
{
PSSEntities db = new PSSEntities();
int fg = 1;
using (var tx = db.Database.BeginTransaction())
{
try
{
db.StockReturn.Add(qp);
if (list != null)
{
db.StockReturnDetail.AddRange(list);
}
db.SaveChanges();
tx.Commit();
}
catch (Exception ex)
{
fg = 0;
tx.Rollback();
throw new Exception(ex.Message);
}
}
return(fg);
}
/// <summary>
/// 修改客户等级
/// </summary>
/// <param name="cusl"></param>
/// <returns></returns>
public static int Edit(CustomerLevel cusl)
{
PSSEntities db = new PSSEntities();
db.Entry <CustomerLevel>(cusl).State = System.Data.Entity.EntityState.Modified;
return(db.SaveChanges());
}
/// <summary>
/// 添加供货商
/// </summary>
/// <param name="pd"></param>
/// <returns></returns>
public static int Add(ProductLend pd)
{
PSSEntities db = new PSSEntities();
db.ProductLend.Add(pd);
return(db.SaveChanges());
}
/// <summary>
/// 删除客户等级
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static int Delete(int id)
{
PSSEntities db = new PSSEntities();
db.CustomerLevel.Remove(db.CustomerLevel.FirstOrDefault(c => c.CLID == id));
return(db.SaveChanges());
}
/// <summary>
/// 删除供应商
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static int Delete(string id)
{
PSSEntities db = new PSSEntities();
db.ProductLend.Remove(db.ProductLend.FirstOrDefault(p => p.PPID == id));
return(db.SaveChanges());
}
/// <summary>
/// 添加
/// </summary>
/// <param name="pro"></param>
/// <returns></returns>
public static int Add(Products pro)
{
PSSEntities db = new PSSEntities();
db.Products.Add(pro);
return(db.SaveChanges());
}
/// <summary>
/// 添加【---事务---】
/// </summary>
/// <param name="dep"></param>
/// <returns></returns>
public static int AddStocks(CheckDepot dep, List <CheckDepotDetail> list)
{
PSSEntities db = new PSSEntities();
int fg = 1;
using (var tx = db.Database.BeginTransaction())
{
try
{
db.CheckDepot.Add(dep);
if (list != null)
{
db.CheckDepotDetail.AddRange(list);
}
db.SaveChanges();
tx.Commit();
}
catch (Exception e)
{
fg = 0;
tx.Rollback();
throw new Exception(e.Message);
}
}
return(fg);
}
/// <summary>
/// 审核订单【修改后不可修改，直接影响库存数据】
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static int CKDepot(string id, int userid)
{
PSSEntities db = new PSSEntities();
int fg = 1;
///以下开始事务操作
using (var tx = db.Database.BeginTransaction())
{
try
{
/*
* --------------------------------------- 【库存盘点】-------------------------------------------------
* 1、将盘点状态改为1
* 2、直接修改对应仓库中的商品库存为拆分商品后的实际库存
*/
Splits st = db.Splits.FirstOrDefault(s => s.SplitID.Equals(id));
st.SplitState = 1;
List <SplitDetail> list = db.SplitDetail.Where(p => p.SplitID.Equals(id)).ToList();//销售出库详单
DepotStock ds = db.DepotStock.FirstOrDefault(s => s.DepotID == st.DepotID && s.ProID == st.ProID);
if (ds == null)
{
throw new Exception("拆分商品未在当前仓库中！！！");
}
if (ds.DSAmount < st.SplitAmount)//判断库存是否充足
{
throw new Exception("拆分数量超出当前商品的库存数量！！");
}
else
{
ds.DSAmount -= st.SplitAmount;//减少被拆分商品的库存
}
foreach (SplitDetail item in list)
{
//如果拆分后的商品在当前仓库中不存在
if (db.DepotStock.FirstOrDefault(p => p.DepotID.Equals(st.DepotID) && p.ProID == item.ProID) == null)
{
//添加新的商品库存
db.DepotStock.Add(new DepotStock()
{
DepotID = st.DepotID, ProID = item.ProID, DSAmount = item.SDAmount, DSPrice = item.SDPrice
});
}
else
{//如果指定仓库存在这个商品，修改这个商品的库存
DepotStock dss = db.DepotStock.FirstOrDefault(d => d.ProID == item.ProID && d.DepotID == st.DepotID);
dss.DSAmount += item.SDAmount;
}
}
db.SaveChanges();
tx.Commit();
}
catch (Exception e)
{
tx.Rollback();
throw new Exception(e.Message);
}
}
return(fg);
}
/// <summary>
/// 获取采购退货单编号
/// </summary>
/// <returns></returns>
public ActionResult GetStockReturnID()
{
PSSEntities db = new PSSEntities();
ObjectParameter para = new ObjectParameter("DD", "");
db.pro_order("StockReturn", "KRID", "CT", para);
return(Content(para.Value.ToString()));
}
/// <summary>
/// 获取编号
/// </summary>
/// <returns></returns>
public ActionResult GetID()
{
PSSEntities db = new PSSEntities();
ObjectParameter para = new ObjectParameter("DD", "");
db.pro_order("Splits", "SplitID", "CF", para);
return(Content(para.Value.ToString()));
}
/// <summary>
/// 获取采购订单编号
/// </summary>
/// <returns></returns>
public ActionResult GetStockInID()
{
ObjectParameter para = new ObjectParameter("DD", "");
PSSEntities db = new PSSEntities();
db.pro_order("StockInDepot", "SIDID", "CR", para);
return(Content(para.Value.ToString()));
}
/// <summary>
///查询审核订单和未审核订单数
/// </summary>
/// <param name="dt"></param>
/// <param name="state"></param>
/// <returns></returns>
public static int FindStateCount(int state)
{
string sql = "select * from SaleReturn where SRState = " + state;
PSSEntities db = new PSSEntities();
int count = db.Database.SqlQuery <SaleReturn>(sql).ToList().Count;
return(count);
}
/// <summary>
/// 根据时间查询当前月订单个数
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static int FindByYCount(DateTime dt, int state)
{
PSSEntities db = new PSSEntities();
string sql = "select * from SaleReturn where datepart(MM,SRDate)=" + dt.Month + " and datepart(YYYY,SRDate) = " + dt.Year + " and SRState =" + state;
List <SaleReturn> list = db.Database.SqlQuery <SaleReturn>(sql).ToList();
return(list.Count());
}
/// <summary>
/// 根据时间查询当前月订单个数
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static int FindByYCount(DateTime dt)
{
PSSEntities db = new PSSEntities();
string sql = "select * from Devolves where datepart(MM,DevDate)=" + dt.Month + " and datepart(YYYY,DevDate) = " + dt.Year;
List <Devolves> list = db.Database.SqlQuery <Devolves>(sql).ToList();
return(list.Count());
}
