private static long GetPackCode()
{
lock (Locker) //lock 关键字可确保当一个线程位于代码的临界区时，另一个线程不会进入该临界区。
{
long lastpack = 0;
string date = DateTime.Now.ToString("yyyyMMdd") + "0001"; //得到当天第一个包裹号
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
busi_sendorder send = db.SqlQuery <busi_sendorder>("select * from busi_sendorder where order_code=(select MAX(order_code) from busi_sendorder)").FirstOrDefault();
if (send == null)
{
return(Convert.ToInt64(date));
}
if (Convert.ToInt64(send.order_code) >= Convert.ToInt64(date))
{
lastpack = Convert.ToInt64(send.order_code) + 1;
return(lastpack);
}
else
{
return(Convert.ToInt64(date));
}
}
}
}
/// <summary>
/// 获取商品信息
/// </summary>
/// <returns></returns>
public ShopResult GetShopList()
{
ShopResult result = new ShopResult();
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
try
{
var list = db.Queryable <base_shop>().Where(a => a.del_flag).OrderBy("shop_id DESC").ToList();
if (list.Count <= 0)
{
result.success = false;
result.Msg = "暂无店铺信息!";
return(result);
}
var list1 = "<option value=\"0\">请选择...</option>";
foreach (var item in list)
{
list1 += "<option value=\"" + item.shop_id + "\">" + item.shop_name + "</option>";
}
result.success = true;
result.Msg = list1;
return(result);
}
catch (Exception ex)
{
result.success = false;
result.Msg = "获取店铺信息失败!";
return(result);
}
}
}
/// <summary>
/// 使用快递单号
/// </summary>
/// <param name="expressid"></param>
/// <param name="csvtable"></param>
/// <returns></returns>
public int UseExpressCode(int expressid, System.Data.DataTable csvtable)
{
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
int mycount = 0;
db.CommandTimeOut = 3000;//设置超时时间
try
{
db.BeginTran();//开启事务
//string sql = string.Format(@"", expressid);
foreach (DataRow item in csvtable.Rows)
{
base_expresscode Eid = db.SqlQuery <base_expresscode>("select top 1 * from base_expresscode where isuse=0 and express_id=@expressid order by Eid", new { expressid = expressid }).FirstOrDefault();
string mypackge = item["包裹号"].ToString();
db.Update <base_expresscode>(new { isuse = true, Usetime = DateTime.Now, packgecode = mypackge }, it => it.Eid == Eid.Eid);
bool isok = db.Update <busi_sendorder>(new { exp_code = Eid.Expresscode }, it => it.order_code == mypackge);//如果包裹号不存在系统中也没事
if (isok)
{
mycount++;
}
}
db.CommitTran();//提交事务
return(mycount);
}
catch (Exception ex)
{
db.RollbackTran();//回滚事务
throw ex;
}
}
}
public GoodsReceivedResult ResetOrdercode(long?purch_id, string OrderCode)
{
bool isok = false;
GoodsReceivedResult result = new GoodsReceivedResult();
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
try
{
busi_purchase pur = db.Queryable <busi_purchase>().Where(s => s.purch_id == purch_id).FirstOrDefault();
pur.OrderCode = OrderCode;
isok = db.Update <busi_purchase>(pur);
if (isok)
{
result.success = true;
result.Msg = "操作成功";
return(result);
}
else
{
result.success = false;
result.Msg = "操作失败";
return(result);
}
}
catch (Exception ex)
{
result.success = false;
result.Msg = "操作失败" + ex.Message;
return(result);
}
}
}
/// <summary>
/// 根据库存id 获取库存详情
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <returns></returns>
public List <StockModel> GetStockEList(out string exmsg, Guid?stock_id)
{
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
if (!stock_id.HasValue)
{
exmsg = "参数错误!";
return(null);
}
try
{
var sql = db.Queryable <base_wh_stock>()
.JoinTable <base_wh_warehouse>((s1, s2) => s1.wh_id == s2.wh_id)
.JoinTable <base_prod_code>((s1, s3) => s1.code_id == s3.code_id)
.JoinTable <base_product>((s1, s4) => s4.prod_id == s1.prod_id)
.Where("s1.del_flag=1 and s2.del_flag=1 and s3.del_flag=1 and s4.del_flag=1 and s1.stock_id='" + stock_id + "'")
.OrderBy("s1.code_id DESC")
.Select <StockModel>("s1.stock_qty ,s2.wh_name,s3.sku_code,s4.prod_title").ToList();
exmsg = "";
return(sql.ToList());
}
catch (Exception ex)
{
exmsg = ex.ToString();
return(null);
}
}
}
/// <summary>
/// 得到所有产品分类
/// </summary>
/// <param name="pagenum"></param>
/// <param name="onepagecount"></param>
/// <param name="totil"></param>
/// <param name="totilpage"></param>
/// <param name="exmsg"></param>
/// <returns></returns>
public List <base_productclass> Getpages(int pagenum, int onepagecount, out int totil, out int totilpage, out string exmsg)
{
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
try
{
var comresult = db.Queryable <base_productclass>().Where(s1 => s1.del_flag == true);
List <base_productclass> productclass = comresult.OrderBy(s1 => s1.prod_class_id)
.Skip(onepagecount * (pagenum - 1))
.Take(onepagecount).ToList();
totil = comresult.Count();
totilpage = totil / onepagecount;
exmsg = "";
if (totil % onepagecount > 0)
{
totilpage++;
}
return(productclass);
}
catch (Exception ex)
{
exmsg = ex.ToString();
totil = 0;
totilpage = 0;
return(null);
}
}
}
/// <summary>
/// 获取店铺销售额信息
/// </summary>
/// <param name="platform_id"></param>
/// <param name="shop_id"></param>
/// <param name="start_time"></param>
/// <param name="end_time"></param>
/// <param name="exmsg"></param>
/// <returns></returns>
public List <StoreSalesModel> GetStoreSalesInfo(Int64?platform_id, Int64?shop_id, DateTime?start_time, DateTime?end_time, out string exmsg)
{
StoreSalesResult result = new StoreSalesResult();
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
try
{
var sql = "SELECT base_platform.platform_name, base_shop.shop_name, " +
" (SELECT SUM(order_summoney) AS Expr2 FROM busi_custorder WHERE (del_flag = 1) AND (shop_id = " + shop_id + ") GROUP BY shop_id) AS sumshopmoney, " +
" (SELECT SUM(order_summoney) AS Expr1 FROM busi_custorder AS busi_custorder_2 WHERE (del_flag = 1) AND (platform_id = " + platform_id + ") GROUP BY platform_id) AS sumplatformmoney" +
" FROM busi_custorder AS busi_custorder_1 INNER JOIN" +
" base_platform ON busi_custorder_1.platform_id = base_platform.platform_id INNER JOIN" +
" base_shop ON base_platform.platform_id = base_shop.platform_id" +
" WHERE (base_platform.del_flag = 1) AND (base_shop.del_flag = 1) AND (busi_custorder_1.del_flag = 1) AND " +
" (busi_custorder_1.platform_id = " + platform_id + ") AND (busi_custorder_1.shop_id = " + shop_id + ")" +
" AND (busi_custorder_1.create_time >= CONVERT(DATETIME, '" + start_time + "', 102)) " +
" AND (busi_custorder_1.create_time < CONVERT(DATETIME, '" + end_time + "', 102))" +
" GROUP BY busi_custorder_1.platform_id, busi_custorder_1.shop_id, base_platform.platform_name, base_shop.shop_name";
var list = db.SqlQuery <StoreSalesModel>(sql).ToList();
exmsg = "";
return(list.ToList());
}
catch (Exception ex)
{
exmsg = ex.ToString();
return(null);
}
}
}
/// <summary>
/// 根据ID获取收件人信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public List <busi_sendorder> GetInfoByID(Int64?id)
{
OrderQueryResult result = new OrderQueryResult();
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
if (id == 0)
{
result.success = false;
result.Msg = "参数错误";
return(null);
}
try
{
var list = db.Queryable <busi_sendorder>().Where("del_flag=1 and custorder_id=" + id + "").ToList();
if (list.Count == 0)
{
result.success = false;
result.DataResult = "";
return(null);
}
result.success = true;
result.DataResult = list;
return(list.ToList());
}
catch (Exception)
{
return(null);
}
}
}
/// <summary>
/// 添加用户权限信息
/// </summary>
/// <returns></returns>
public bool AddUserMenu(List <base_user_menu_rel> usermenulist)
{
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
//获取需要添加的用户
string[] strlist = new string[usermenulist.Count()];
var i = 0;
foreach (var item in usermenulist)
{
strlist[i] = item.user_id.ToString();
item.del_flag = true;
i++;
}
try
{
db.BeginTran();
//删除用户权限
db.Delete <base_user_menu_rel, string>(a => a.user_id, strlist);
//重新添加用户权限
db.InsertRange <base_user_menu_rel>(usermenulist);
db.CommitTran();
}
catch (Exception e)
{
db.RollbackTran();
return(false);
}
}
return(true);
}
/// <summary>
/// 获取快递类型信息
/// </summary>
/// <returns></returns>
public LogisticsQueryResult GetExpressList()
{
LogisticsQueryResult result = new LogisticsQueryResult();
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
try
{
var list = db.Queryable <base_exp_comp>().Where(a => a.del_flag).OrderBy("express_id DESC").ToList();
if (list.Count <= 0)
{
result.success = false;
result.Msg = "暂无快递类型信息!";
return(result);
}
var list1 = "<option value=\"0\">请选择...</option>";
foreach (var item in list)
{
list1 += "<option value=\"" + item.express_coded + "\">" + item.express_name + "</option>";
}
result.success = true;
result.Msg = list1;
return(result);
}
catch (Exception ex)
{
result.success = false;
result.Msg = "获取快递类型失败!";
return(result);
}
}
}
/// <summary>
/// 更新信息
/// </summary>
/// <param name="purch_id"></param>
/// <param name="express_id"></param>
/// <param name="express_code"></param>
/// <param name="express_name"></param>
/// <param name="OrderCode"></param>
/// <returns></returns>
public MaterialReceiptResult Modify(Int64?purch_id, Int64?express_id, string express_code, string express_name, string OrderCode)
{
bool rstNum = false;
MaterialReceiptResult result = new MaterialReceiptResult();
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
db.BeginTran();
var list = db.Queryable <busi_purchase>().Where(s => s.del_flag).InSingle(purch_id.Value);
if (list == null)
{
db.RollbackTran();
result.success = false;
result.Msg = "无效的采购信息,操作失败!";
return(result);
}
rstNum = db.Update <busi_purchase>(new { purch_status = 3, express_id = express_id, express_code = express_code, express_name = express_name, OrderCode = OrderCode }, a => a.purch_id == purch_id && a.purch_status == 3);
if (rstNum)
{
db.CommitTran();
result.success = true;
result.Msg = "操作成功";
return(result);
}
else
{
db.RollbackTran();
result.success = false;
result.Msg = "操作失败";
return(result);
}
}
}
/// <summary>
/// 获取平台列表信息
/// </summary>
/// <returns></returns>
public List <base_platform> GetPlatformList(int pagenum, int onepagecount, out int totil, out int totilpage, out string exmsg)
{
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
try
{
var getwhere = db.Queryable <base_platform>()
.Where(a => a.del_flag);
totil = getwhere.Count();
var list = getwhere.OrderBy("platform_id DESC")
.Skip(onepagecount * (pagenum - 1))
.Take(onepagecount).ToList();
totilpage = totil / onepagecount;
exmsg = "";
if (totil % onepagecount > 0)
{
totilpage++;
}
return(list);
}
catch (Exception ex)
{
exmsg = ex.ToString();
totil = 0;
totilpage = 0;
return(null);
}
}
}
public bool GetDeclarationDHLExcelUrl(string zhuanyuncode, string fullpath, long express, long shop)
{
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
try
{
DataTable list = db.Queryable <busi_sendorder>().JoinTable <busi_transfer>((s1, s2) => s1.tran_id == s2.tran_id)
.JoinTable <busi_sendorder, busi_custorder>((s1, s3) => s1.custorder_id == s3.order_id)
.JoinTable <busi_sendorder, base_exp_comp>((s1, s4) => s1.express_id == s4.express_id)
.JoinTable <busi_sendorder, busi_custorder, base_shop>((s1, s3, s5) => s3.shop_id == s5.shop_id)
.Where <busi_transfer>((s1, s2) => s2.tran_code == zhuanyuncode.Trim())
.Where <base_exp_comp>((s1, s4) => s4.express_id == express)
.Where <base_shop>((s1, s5) => s5.shop_id == shop)
.Select("s3.custorder_code,s4.express_name ,s1.exp_code").ToDataTable();
//将datetable放入excel中
string error = string.Empty;
bool isok = ExcelHelper.DataTableToExcel(list, fullpath, out error);
return(isok);
}
catch (Exception ex)
{
throw ex;
}
}
}
/// <summary>
/// 获取库存详情
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <param name="locat_id"></param>
/// <returns></returns>
public List <StorageBinModel> GetStorageBinEList(out string exmsg, long?wh_id, long?locat_id)
{
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
if (wh_id == 0 || locat_id == 0)
{
exmsg = "参数错误!";
return(null);
}
try
{
var sql = db.Queryable <base_wh_stock>()
.JoinTable <base_wh_warehouse>((s1, s2) => s1.wh_id == s2.wh_id)
.JoinTable <base_product>((s1, s3) => s3.prod_id == s1.prod_id)
.JoinTable <base_location>((s1, s5) => s1.location_id == s5.locat_id)
.JoinTable <base_prod_code>((s1, s6) => s6.code_id == s1.code_id)
.Where("s1.del_flag=1 and s2.del_flag=1 and s3.del_flag=1 and s5.del_flag=1 and s6.del_flag=1 and s1.wh_id=" + wh_id + " and s1.location_id=" + locat_id + "")
.GroupBy(" s1.code_id, s1.wh_id, s1.location_id, s6.sku_code, s1.stock_qty, s2.wh_name, s3.prod_title, s5.locat_code")
.Select <StorageBinModel>(" s6.sku_code, s1.stock_qty, s2.wh_name, s3.prod_title, s5.locat_code").ToList();
exmsg = "";
return(sql.ToList());
}
catch (Exception ex)
{
exmsg = ex.ToString();
return(null);
}
}
}
/// <summary>
/// 得到配置SKU列表
/// </summary>
/// <param name="pagenum"></param>
/// <param name="onepagecount"></param>
/// <param name="shopID"></param>
/// <param name="totil"></param>
/// <param name="totilpage"></param>
/// <returns></returns>
public static List <base_NPSKU> GetPSKUListByshopID(int pagenum, int onepagecount, int shopID, out int totil, out int totilpage)
{
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
try
{
var comresult = db.Queryable <base_PSKU>().JoinTable <base_shop>((s1, s2) => s1.ShopID == s2.shop_id).Where <base_PSKU>(s1 => s1.ShopID == shopID);
totil = comresult.Count();
totilpage = totil / onepagecount;
if (totil % onepagecount > 0)
{
totilpage++;
}
List <base_NPSKU> platlist = comresult.OrderBy(s1 => s1.ID)
.Skip(onepagecount * (pagenum - 1))
.Take(onepagecount)
.Select <base_shop, base_NPSKU>((s1, s2) => new base_NPSKU()
{
ID = s1.ID,
ShopName = s2.shop_name,
PSKU = s1.PSKU,
SSKU = s1.SSKU,
GSKU = s1.GSKU
}).ToList();
return(platlist);
}
catch (Exception ex)
{
throw ex;
}
}
}
/// <summary>
/// 获取信息
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <param name="wh_id"></param>
/// <param name="locat_code"></param>
/// <returns></returns>
public List <StorageBinModel> GetStorageBinList(int pagenum, int onepagecount, out int totil, out int totilpage, out string exmsg, long?wh_id, string locat_code)
{
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
try
{
var sql1 = db.Queryable <base_location>()
.JoinTable <base_wh_warehouse>((s1, s2) => s1.wh_id == s2.wh_id)
.Where("s1.del_flag=1 and s2.del_flag=1")
.OrderBy("s1.locat_id DESC");
if (!string.IsNullOrWhiteSpace(locat_code))
{
sql1 = sql1.Where(s1 => s1.locat_code.Contains(locat_code));
}
if (wh_id > 0)
{
sql1 = sql1.Where(s1 => s1.wh_id == wh_id);
}
totil = sql1.Count();
var jList = sql1.Skip(onepagecount * (pagenum - 1)).Take(onepagecount)
.Select <StorageBinModel>("s1.locat_id,s1.wh_id,s1.locat_code,s1.create_time,s2.wh_name")
.ToList();
if (jList.Count > 0)
{
foreach (var item in jList)
{
var sql = db.Queryable <base_wh_stock>()
.Where(" del_flag=1 and location_id=" + item.locat_id + " and wh_id=" + item.wh_id + "")
.GroupBy("location_id,wh_id")
.Select <StorageBinModel>("location_id,wh_id, SUM(stock_qty) AS stock_qty")
.SingleOrDefault();
if (sql != null)
{
item.stock_qty = sql.stock_qty;
}
else
{
item.stock_qty = 0;
}
item.create_timeE = item.create_time.ToString("yyyy-MM-dd");
}
}
totilpage = totil / onepagecount;
exmsg = "";
if (totil % onepagecount > 0)
{
totilpage++;
}
return(jList.ToList());
}
catch (Exception ex)
{
exmsg = ex.ToString();
totil = 0;
totilpage = 0;
return(null);
}
}
}
public bool AddSupplier(base_supplier supp)
{
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
try
{
//db.DisableInsertColumns = new string[] { "edit_time" };//edit_time列将不会插入值
supp.edit_time = DateTime.Now;
var id = db.Insert <base_supplier>(supp);
if (id.ObjToInt() > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
ex.ToString();
return(false);
}
}
}
/// <summary>
/// 根据产品ID获得产品的详细信息和SKU
/// </summary>
/// <param name="proid"></param>
/// <returns></returns>
public ProductDetail GetProductDetail(int proid)
{
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
try
{
base_product pro = db.Queryable <base_product>().Where(s => s.prod_id == proid).FirstOrDefault();
List <base_prod_code> proskus = db.Queryable <base_prod_code>().Where(s1 => s1.prod_id == proid).ToList();
List <img_model> proimgs = db.Queryable <base_product_imgs>().Where(s2 => s2.prod_id == proid).GroupBy(s2 => s2.prod_model)
.GroupBy(s2 => s2.pic_url)
.Select <img_model>("prod_model,pic_url").ToList();
List <SupplierModel> prosupp = db.Queryable <base_prod_supp_rel>().JoinTable <base_supplier>((s1, s2) => s1.supp_id == s2.supp_id)
.Where(s1 => s1.prod_id == proid)
.Select <base_supplier, SupplierModel>((s1, s2) => new SupplierModel()
{
rel_id = s1.rel_id,
supp_id = s1.supp_id,
suppliername = s2.supp_name,
prod_id = s1.prod_id,
lev_purch = s1.lev_purch
}).ToList();
//List<base_prod_supp_rel> prosupp = db.Queryable<base_prod_supp_rel>().Where(s3 => s3.prod_id == proid).ToList();
ProductDetail prodetail = new ProductDetail();
prodetail.pro = pro;
prodetail.proimgs = proimgs;
prodetail.proskus = proskus;
prodetail.prosupp = prosupp;
return(prodetail);
}
catch (Exception ex)
{
throw ex;
}
}
}
public List <base_supplier> SearchSupplier(int pagenum, int onepagecount, string suppliername, out int totil, out int totilpage)
{
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
try
{
//db.CommandTimeOut = 5000;//设置超时时间
//db.BeginTran();//开启事务
var comresult = db.Queryable <base_supplier>().Where(s1 => s1.del_flag == true);
if (!string.IsNullOrEmpty(suppliername))
{
comresult = comresult.Where(s1 => s1.supp_name == suppliername);
}
List <base_supplier> suppliers = comresult.OrderBy(s1 => s1.supp_id)
.Skip(onepagecount * (pagenum - 1))
.Take(onepagecount).ToList();
totil = comresult.Count();
totilpage = totil / onepagecount;
if (totil % onepagecount > 0)
{
totilpage++;
}
return(suppliers);
}
catch (Exception ex)
{
ex.ToString();
//db.RollbackTran();//回滚事务
totil = 0;
totilpage = 0;
return(null);
}
}
}
public int DeleteExpress(int id)
{
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
try
{
//1.先判断此快递类型是否有未使用的单号数据
var hascode = db.Queryable <base_expresscode>().Where(s => s.express_id == id).Where(s => s.isuse == false).ToList();
if (hascode.Count > 0)
{
return(2);//代表有单号不能删除
}
else
{
db.DisableInsertColumns = new string[] { "edit_time" };//edit_time列将不会插入值
var isok = db.Update <base_exp_comp>(new { del_flag = true }, it => it.express_id == id);
if (id.ObjToInt() > 0)
{
return(0);
}
else
{
return(1);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
}
public bool UpdateProdcut(string code, string proname, string bgname, string bgcode, string bgprice, string weight, string price, string remark, string profly, string promodel, string prosize, int proid, int prostatus, string purchase_url)
{
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
try
{
bool isok = db.Update <base_product>(new {
bgname = bgname,
bgcode = bgcode,
pre_weight = weight.ObjToInt(),
price_us = bgprice.ObjToDecimal(),
price_cn = price.ObjToDecimal(),
remark = remark,
prod_class_id = profly.ObjToInt(),
model = promodel,
size = prosize,
prod_style = code,
prod_title = proname,
prod_status = prostatus,
prod_url = purchase_url
}, s => s.prod_id == proid);
string [] modellist = promodel.Split(',');
foreach (string item in modellist)
{
base_product_imgs a = db.Queryable <base_product_imgs>().Where(s => s.prod_id == proid).Where(s => s.prod_model == item).FirstOrDefault();
if (null == a)
{
base_product_imgs proimgs = new base_product_imgs();
proimgs.code_id = 0;
proimgs.create_time = DateTime.Now;
proimgs.create_user_id = LoginUser.Current.user_id;
proimgs.del_flag = false;
proimgs.del_time = DateTime.Now;
proimgs.del_user_id = 0;
proimgs.edit_time = DateTime.Now;
proimgs.edit_user_id = 0;
proimgs.pic_describe = "";
proimgs.pic_url = "";
proimgs.prod_id = proid;
proimgs.prod_model = item;
proimgs.remark = "";
db.Insert <base_product_imgs>(proimgs);
}
}
if (isok)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
throw ex;
}
}
}
/// <summary>
/// 得到产品列表
/// </summary>
/// <param name="pagenum"></param>
/// <param name="onepagecount"></param>
/// <param name="totil"></param>
/// <param name="totilpage"></param>
/// <param name="status"></param>
/// <returns></returns>
public List <ProcudtViewModel> Getprolist(int pagenum, int onepagecount, out int totil, out int totilpage, int status, string prostyle, string proskucode, string prodevelpoer)
{
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
try
{
var comresult = db.Queryable <base_product>().JoinTable <base_productclass>((s1, s2) => s1.prod_class_id == s2.prod_class_id)
//.JoinTable<base_prod_code>((s1, s3) => s1.prod_id == s3.prod_id,JoinType.Inner)
.Where <base_product>(s1 => s1.del_flag == true).Where <base_product>(s1 => s1.prod_status == status);
if (!string.IsNullOrEmpty(proskucode)) //如果SKU不为空
{
long myproid = db.Queryable <base_prod_code>().Where(s3 => s3.sku_code == proskucode).FirstOrDefault().prod_id;
comresult = comresult.Where <base_product>(s1 => s1.prod_id == myproid);
}
if (!string.IsNullOrEmpty(prostyle))
{
comresult = comresult.Where <base_product>(s1 => s1.prod_style == prostyle);
}
if (!string.IsNullOrEmpty(prodevelpoer))
{
comresult = comresult.Where <base_product>(s1 => s1.prod_developer == prodevelpoer);
}
totil = comresult.Count();
totilpage = totil / onepagecount;
if (totil % onepagecount > 0)
{
totilpage++;
}
List <ProcudtViewModel> productclass = comresult.OrderBy(s1 => s1.prod_id, OrderByType.Desc)
.Skip(onepagecount * (pagenum - 1))
.Take(onepagecount).Select <base_productclass, ProcudtViewModel>((s1, s2) => new ProcudtViewModel
{
prod_id = s1.prod_id,
pic_url = s1.pic_url,
price_cn = s1.price_cn,
prod_property = s1.prod_property, //Enum.GetName(typeof(Productstyle), Convert.ToInt32(s1.prod_property)),
prod_status = s1.prod_status, //Enum.GetName(typeof(ProductStatus), Convert.ToInt32(s1.prod_status)), //
prod_title = s1.prod_title,
prod_style = s1.prod_style,
prod_developer = s1.prod_developer,
create_time = s1.create_time,
remark = s1.remark
}).ToList();
return(productclass);
}
catch (Exception ex)
{
totil = 0;
totilpage = 0;
throw ex;
}
}
}
/// <summary>
/// 获取所有菜单分组
/// </summary>
/// <returns></returns>
public List <base_menu_groups> GetAllUserMenuGroups()
{
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
var usermenulist = db.Queryable <base_menu_groups>().ToList();
return(usermenulist);
}
}
/// <summary>
/// 获取所有用户信息
/// </summary>
/// <returns></returns>
public List <base_users> GetUserList(int pageIndex, int pageSize, out int count)
{
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
var getwhere = db.Queryable <base_users>()
.Where(a => a.del_flag == true).OrderBy(a => a.user_id);
count = getwhere.Count();
return(getwhere.Skip(pageSize * (pageIndex - 1)).Take(pageSize).ToList());
}
}
/// <summary>
/// 添加SKU配置信息
/// </summary>
/// <param name="PSKU"></param>
/// <param name="SSKU"></param>
/// <param name="GSKU"></param>
/// <returns></returns>
public static bool AddPSKU(string PSKU, string SSKU, string GSKU, int shopID)
{
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
try
{
if (!string.IsNullOrEmpty(GSKU))
{
var GSKUList = db.Queryable <base_PSKU>().Where(c => c.GSKU == GSKU && c.ShopID == shopID).ToList();
if (GSKUList.Count >= 1)
{
throw new Exception("过滤SKU已经存在！");
}
}
if (!string.IsNullOrEmpty(SSKU) && !string.IsNullOrEmpty(PSKU))
{
var PSKUList = db.Queryable <base_PSKU>().Where(c => c.PSKU == PSKU).Where(c => c.SSKU == SSKU).ToList();
if (PSKUList.Count >= 1)
{
throw new Exception("系统SKU或者平台SKU已经存在！");
}
}
//1.判断SSKU在系统中是否存在
if (!string.IsNullOrEmpty(SSKU))
{
var sku = db.Queryable <base_prod_code>().Where(s => s.sku_code == SSKU).ToList();
if (sku.Count == 0)
{
throw new Exception("系统中不存在此SKU！，" + SSKU);
}
}
//2.所有条件全部满足之后添加
base_PSKU NPSKU = new base_PSKU();
NPSKU.PSKU = PSKU;
NPSKU.SSKU = SSKU;
NPSKU.ShopID = shopID;
NPSKU.GSKU = GSKU;
var id = db.Insert <base_PSKU>(NPSKU);
if (id.ObjToInt() > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
throw ex;
}
}
}
/// <summary>
/// 添加或者更新用户信息
/// </summary>
/// <param name="model"></param>
/// <param name="state">0代表修改密码1代表删除用户2代表禁用启用用户3代表添加</param>
/// <returns></returns>
public ComResult AddOrUpdateUserInfo(base_users model, int state)
{
ComResult result = new ComResult();
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
if (model.user_id > 0)
{ //判断用户名是否存在
var userModel = db.Queryable <base_users>().Where(a => a.user_id == model.user_id).ToList();
if (userModel == null || userModel.Count() <= 0)
{
result.State = 0;
result.Msg = "用户不存在";
return(result);
}
switch (state)
{
case 0:
db.Update <base_users>(new { user_pwd = model.user_pwd, real_name = model.real_name }, a => a.user_id == model.user_id);
break;
case 1:
db.Update <base_users>(new { del_flag = model.del_flag }, a => a.user_id == model.user_id);
break;
case 2:
db.Update <base_users>(new { user_status = model.user_status }, a => a.user_id == model.user_id);
break;;
}
result.State = 1;
return(result);
}
else
{
//判断用户名是否存在
var userModel = db.Queryable <base_users>().Where(a => a.user_name == model.user_name).ToList();
if (userModel != null && userModel.Count() > 0)
{
result.State = 0;
result.Msg = "用户已经存在";
return(result);
}
model.create_time = DateTime.Now;
model.edit_time = DateTime.Now;
model.del_flag = true;
model.user_status = true;
model.create_user_id = LoginUser.Current.user_id;
model.net_no = 1;
model.emp_no = 1;
result.State = 1;
result.DataResult = db.InsertOrUpdate <base_users>(model);
return(result);
}
}
}
/// <summary>
/// 获取国际物流信息
/// </summary>
/// <param name="pagenum"></param>
/// <param name="onepagecount"></param>
/// <param name="totil"></param>
/// <param name="totilpage"></param>
/// <param name="exmsg"></param>
/// <param name="express_id"></param>
/// <param name="express_code"></param>
/// <returns></returns>
public List <InternationalQuiryModel> GetInternationalQuiryList(int pagenum, int onepagecount, out int totil, out int totilpage, out string exmsg,
int?express_id, string express_code)
{
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
try
{
var getwhere = db.Queryable <busi_transfer>()
.JoinTable <base_exp_comp>((s1, s2) => s1.express_id == s2.express_id)
.Where("s1.del_flag=1 and s2.del_flag=1 ").OrderBy("s1.tran_id DESC");
if (express_id > 0)
{
getwhere = getwhere.Where(s1 => s1.express_id == express_id);
}
if (!string.IsNullOrWhiteSpace(express_code))
{
getwhere = getwhere.Where(s1 => s1.express_code.Contains(express_code));
}
totil = getwhere.Count();
var list = getwhere.Skip(onepagecount * (pagenum - 1)).Take(onepagecount)
.Select <base_exp_comp, InternationalQuiryModel>((s1, s2) => new InternationalQuiryModel
{
tran_code = s1.tran_code,
express_code = s1.express_code,
create_time = s1.create_time,
tran_id = s1.tran_id,
express_name = s2.express_name,
express_coded = s2.express_coded,
}).ToList();
if (list.Count > 0)
{
foreach (var item in list)
{
item.create_timeE = item.create_time.ToString("yyyy-MM-dd HH:MM:ss");
}
}
totilpage = totil / onepagecount;
exmsg = "";
if (totil % onepagecount > 0)
{
totilpage++;
}
return(list.ToList());
}
catch (Exception ex)
{
totilpage = 0;
totil = 0;
exmsg = ex.ToString();
return(null);
}
}
}
/// <summary>
/// 获取采购成本信息
/// </summary>
/// <param name="pagenum"></param>
/// <param name="onepagecount"></param>
/// <param name="totil"></param>
/// <param name="totilpage"></param>
/// <param name="exmsg"></param>
/// <param name="start_time"></param>
/// <param name="end_time"></param>
/// <returns></returns>
public List <ProcurementCostsModel> GetProcurementCostsList(int pagenum, int onepagecount, out int totil, out int totilpage, out string exmsg, DateTime?start_time, DateTime?end_time)
{
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
try
{
var getwhere = db.Queryable <busi_purchase>()
.JoinTable <base_users>((s1, s2) => s1.create_user_id == s2.user_id)
.Where("s1.del_flag=1 AND s1.create_time >= CONVERT(DATETIME, '" + start_time + "', 102) AND s1.create_time < CONVERT(DATETIME, '" + end_time + "', 102)")
.OrderBy("s1.purch_id DESC")
.Select <base_users, ProcurementCostsModel>((s1, s2) => new ProcurementCostsModel
{
purch_code = s1.purch_code,
sum_money = s1.sum_money,
create_time = s1.create_time,
emp_name = s2.user_name,
}).ToList();
totil = getwhere.Count();
if (totil > 0)
{
var sql = "SELECT SUM(sum_money) AS sum_money FROM busi_purchase " +
" WHERE (del_flag = 1) AND (create_time >= CONVERT(DATETIME, '" + start_time + "', 102)) AND " +
" (create_time < CONVERT(DATETIME, '" + end_time + "', 102))";
var sum_moneyE = db.SqlQuery <ProcurementCostsModel>(sql);
getwhere.ForEach(a =>
{
a.sum_moneyE = sum_moneyE.FirstOrDefault().sum_money;
});
foreach (var item in getwhere)
{
item.create_timeE = item.create_time.ToString("yyyy-MM-dd");
}
}
var list = getwhere.Skip(onepagecount * (pagenum - 1)).Take(onepagecount).ToList();
totilpage = totil / onepagecount;
exmsg = "";
if (totil % onepagecount > 0)
{
totilpage++;
}
return(list.ToList());
}
catch (Exception ex)
{
exmsg = ex.ToString();
totil = 0;
totilpage = 0;
return(null);
}
}
}
/// <summary>
/// 获取所有权限菜单
/// </summary>
/// <returns></returns>
public List <UserMenu> GetAllUserMenu()
{
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
var usermenulist = db.Queryable <base_menus>().GroupBy(s1 => s1.menu_group_id).GroupBy(s1 => s1.menu_id).GroupBy(s1 => s1.menu_name)
.Select <UserMenu>(s1 => new UserMenu()
{
menu_group_id = s1.menu_group_id, menu_id = s1.menu_id, menu_name = s1.menu_name
}).ToList();
return(usermenulist);
}
}
/// <summary>
/// 查询拣选信息
/// </summary>
/// <param name="packgecode"></param>
/// <param name="ispacked">是否已配完</param>
/// <returns></returns>
public List <PrintSelect> GetPrintSelectList(string packgecode, int ispacked, int pageSize, int pageIndex, out int packgecount, out int count)
{
using (var db = SugarDao.GetInstance(LoginUser.GetConstr()))
{
try
{
var comresult = db.Queryable <busi_workinfo>().JoinTable <busi_sendorder>((s1, s2) => s1.packid == s2.order_id)
.JoinTable <busi_custorder>((s1, s3) => s1.custorder_id == s3.order_id)
.JoinTable <base_shop>((s1, s4) => s1.shop_id == s4.shop_id)
.JoinTable <base_prod_code>((s1, s5) => s1.prod_code_id == s5.code_id)
.Where <busi_workinfo, busi_sendorder>((s1, s2) => s2.del_flag == true)
.Where <busi_workinfo, busi_sendorder>((s1, s2) => s2.is_print == false && s2.tran_id == 0)
.Where <busi_workinfo, busi_sendorder>((s1, s2) => s2.prod_num >= 2); //多件的显示，单件的不显示
if (!string.IsNullOrEmpty(packgecode))
{
comresult = comresult.Where <busi_sendorder>((s1, s2) => s2.order_code == packgecode);
}
if (0 == ispacked)//是否已配完,30 说明部分配货
{
comresult = comresult.Where <busi_sendorder>((s1, s2) => s2.order_tatus == 30);
}
else if (1 == ispacked)//查询已经配货的,但是未打印拣选单的
{
comresult = comresult.Where <busi_sendorder>((s1, s2) => s2.order_tatus == 40);
}
count = comresult.Count();
packgecount = count / pageSize;
if (count % pageSize > 0)
{
packgecount++;
}
List <PrintSelect> list = comresult.OrderBy(s1 => s1.work_id).Skip(pageSize * (pageIndex - 1)).Take(pageSize)
.Select <busi_sendorder, busi_custorder, base_shop, base_prod_code, PrintSelect>((s1, s2, s3, s4, s5) => new PrintSelect()
{
packgecode = s2.order_code,
skucode = s5.sku_code,
shopname = s4.shop_name,
depotname = "",
location = "",
workid = s1.work_id,
skuststus = s1.is_work,
ordercode = s3.order_code
}).ToList();
return(list);
}
catch (Exception ex)
{
throw ex;
}
}
}