hanlihui520/T-DAS
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
taitan_dengdai_songjiang
T-DAS/Epost.DAL/OrdersDAL.cs

2571 lines
90 KiB
C#
Raw Permalink Normal View History

添加项目文件。
2023-01-13 15:30:20 +08:00
using Epost.Common;
using Epost.Model;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Epost.DAL
{
public class OrdersDAL
{
//DB db = new DB();
DataBaseOpration.OprationSqlDAL db = DB_DLL.GetInstance();
HttpHelper http = new HttpHelper();
private static object uplock = new object();
#region 获取订单信息
public DataTable GetOrderList(string orderid)
{
try
{
string sql = string.Format("select ProdArea,LotNo,orderid,barcode,BatchId,DownDate,Tolocation,Sku,GoodsName,Discount,Quantity,GoodsType, TaskType, type, block, Area, Address, Way, Bkaddress, Addresstype, ControlID, ControlIP, shopid, wmsboxcode, slist,layer,corlorcode from v_orders where state = 0 and oprationstate=0 and orderid ='{0}' order by layer desc,slist asc ",
orderid);
return db.GetsqlForDT(sql);
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("获取订单信息异常：" + ex.Message);
return null;
}
}
#endregion
#region 获取订单信息
public DataTable GetOrderList_Car(string matchid, string sku, string area, string block)
{
try
{
string strwhere = string.Empty;
if (!string.IsNullOrEmpty(matchid))
{
strwhere = "and matchid= '" + matchid + "'";
}
string sql = string.Empty;
if (!string.IsNullOrEmpty(area))
{
2024-01-04 15:30:23 +08:00
sql = string.Format("select distinct tolocation,fromlocation,sku,block,area,address,controlip from v_orders where state = 0 and oprationstate <>3 and (sku = '{0}' or tolocation='{0}') and block = '{1}' and area='{2}' " + strwhere + "",
添加项目文件。
2023-01-13 15:30:20 +08:00
sku,
block, area);
}
else
{
2024-01-04 15:30:23 +08:00
sql = string.Format("select distinct tolocation,fromlocation,block,area ,address,controlip from v_orders where state = 0 and oprationstate <>3 (and sku = '{0}'or tolocation='{0}') and block = '{1}' " + strwhere + "",
添加项目文件。
2023-01-13 15:30:20 +08:00
sku,
block);
}
LogHelper.WriteLogInfo("获取订单信息location-" + sql);
return db.GetsqlForDT(sql);
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("获取订单信息异常：" + ex.Message);
return null;
}
}
#endregion
#region 获取回显订单信息
public DataTable GetOrderList_echo(string matchid, string sku, string area, string block)
{
try
{
string strwhere = string.Empty;
if (!string.IsNullOrEmpty(matchid))
{
strwhere = "and matchid= '" + matchid + "'";
}
string sql = string.Empty;
if (!string.IsNullOrEmpty(area))
{
sql = string.Format("select id,ProdArea, LotNo, Matchid, orderid, barcode, BatchId, DownDate, Tolocation, Sku, GoodsName, Discount, Quantity, GoodsType, corlorcode, address, ControlIP,area, block,boxcode,bkbarcode,isnull(checkquantity,0) as checkquantity,wholeunit,unit,shopid,wmsboxcode from v_orders where state = 0 and sku = '{0}' and block = '{1}' and area='{2}' " + strwhere + "",
sku,
block, area);
}
else
{
sql = string.Format("select id,ProdArea, LotNo, Matchid, orderid, barcode, BatchId, DownDate, Tolocation, Sku, GoodsName, Discount, Quantity, GoodsType, corlorcode, address, ControlIP, area , block,boxcode,bkbarcode,isnull(checkquantity,0) as checkquantity,wholeunit,unit,shopid,wmsboxcode from v_orders where state = 0 and sku = '{0}' and block = '{1}' " + strwhere + "",
sku,
block);
}
LogHelper.WriteLogInfo("获取订单信息location-" + sql);
return db.GetsqlForDT(sql);
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("获取订单信息异常：" + ex.Message);
return null;
}
}
#endregion
#region 获取工号信息
public DataTable GetOrderUseerXCode(string matchid, string sku, string area, string block)
{
try
{
string sql = string.Format("select usercode,lightcolor from ordersqueue where sku = '{0}' and taskblock = '{1}' and matchid='{2}' and taskarea='{3}' and state<>3",
sku,
block, matchid, area);
LogHelper.WriteLogInfo("获取工号信息-" + sql);
DataTable dt = db.GetsqlForDT(sql);
return dt;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("获取工号信息异常：" + ex.Message, LogHelper.Log_Type.ERROR);
return null;
}
}
#endregion
#region 中分
public DataTable GetSumList(string matchid, string sku, string block, string area = "")
{
try
{
string strwhere = string.Empty;
if (!string.IsNullOrEmpty(area))
{
strwhere += " and area = '" + area + "'";
}
if (!string.IsNullOrEmpty(matchid))
{
strwhere += " and matchid = '" + matchid + "'";
}
string sql = string.Format("select sum(Quantity) as SumQuantity, Sum(checkquantity) as SumCkQuantity,boxweight, Discount,unit,wholeunit,matchid ,goodscode,goodsname from v_orders where readtime= (select top 1 readtime from orders where state=0) and (oprationstate = 0 or oprationstate=1) and sku='{0}' and block='{1}' " + strwhere + " group by sku,boxweight, Discount,goodsname,unit,wholeunit,matchid ,goodscode",
sku,
block);
LogHelper.WriteLogInfo("获取订单信息-中分" + sql);
return db.GetsqlForDT(sql);
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("获取订单信息-中分异常：" + ex.Message, LogHelper.Log_Type.ERROR);
return null;
}
}
#endregion
#region 获取回显数据
public DataTable GetSumList_Echo(string matchid, string sku, string block, string area = "")
{
try
{
string strwhere = string.Empty;
if (!string.IsNullOrEmpty(area))
{
strwhere += " and area = '" + area + "'";
}
if (!string.IsNullOrEmpty(matchid))
{
strwhere += " and matchid = '" + matchid + "'";
}
string sql = string.Format("select sum(Quantity) as SumQuantity, Sum(checkquantity) as SumCkQuantity,boxweight, Discount,goodsname,unit,wholeunit,matchid ,goodscode,goodsname from v_orders where state = 0 and oprationstate =3 and sku='{0}' and block='{1}' " + strwhere + " group by sku ,boxweight, Discount,goodsname,unit,wholeunit,matchid ,goodscode,goodsname",
sku,
block);
LogHelper.WriteLogInfo("获取订单信息-中分" + sql);
return db.GetsqlForDT(sql);
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("获取订单信息-中分异常：" + ex.Message, LogHelper.Log_Type.ERROR);
return null;
}
}
#endregion
#region 中分波次信息
public DataTable GetMatchSumList(string matchid, string block)
{
try
{
string sql = string.Format("select a.incompleteCount,b.completeCount from (select count(DISTINCT sku) as incompleteCount from v_orders where state = 0 and oprationstate = 0 and matchid = '{0}') as a,(select count(DISTINCT sku) as completeCount from v_orders where state = 0 and matchid = '{1}') as b",
matchid,
matchid);
LogHelper.WriteLogInfo("中分波次信息-中分" + sql);
return db.GetsqlForDT(sql);
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("中分波次信息-中分异常:" + ex.Message, LogHelper.Log_Type.ERROR);
return null;
}
}
#endregion
#region 获取绑定波次的作业区
public DataTable GetMatchOrder()
{
string sql = string.Format("select workblock,matchid from orders where workblock=99 and state<>3 and oprationstate<>3 group by workblock,matchid");
return db.GetsqlForDT(sql);
}
#endregion
#region 获取绑定波次的作业区
public DataTable GetMatchOrderList()
{
string sql = string.Format("select workblock,matchid from orders where state=0 and oprationstate<>3 group by workblock,matchid");
return db.GetsqlForDT(sql);
}
#endregion
#region 获取是否存在退货单订单信息
public DataTable GetOrderList_Car(string matchid, string area)
{
try
{
string sql = string.Format("select matchid from v_orders where state = 0 and oprationstate=0 and matchid='{0}' and area='{1}' ",
matchid, area);
LogHelper.WriteLogInfo("获取是否存在退货单订单信息-" + sql);
return db.GetsqlForDT(sql);
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("获取是否存在退货单订单信息异常：" + ex.Message);
return null;
}
}
#endregion
#region 获取订单信息-拣货小车
public DataTable GetOrderListByCar()
{
try
{
string sql = string.Format("select DISTINCT sku ,matchid,layer, slist,shelves,tolocation,goodsname,area from v_orders where oprationstate<>3 and state<>3 ORDER BY shelves,layer,slist");
return db.GetsqlForDT(sql);
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("获取订单信息-拣货小车异常：" + ex.Message);
return null;
}
}
#endregion
#region 获取货位信息-拣货小车
public DataTable GetLocationInfo(string shelves)
{
try
{
string sql = string.Format("select DISTINCT tolocation,goodsname from v_orders where oprationstate=0 and state =0 and shelves='{0}' order by layer,slist",
shelves);
return db.GetsqlForDT(sql);
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("获取货位信息-拣货小车异常：" + ex.Message);
return null;
}
}
#endregion
#region 获取货位对应的sku-拣货小车
public DataTable GetSkuList(string location)
{
try
{
string sql = string.Format("select distinct sku,goodsname from orders where tolocation='{0}'",
location);
return db.GetsqlForDT(sql);
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("获取货位信息-拣货小车异常：" + ex.Message);
return null;
}
}
#endregion
#region 获取数据-拣货小车
public DataTable GetOrderListByMatchid(string matchid, string carid)
{
try
{
string sql = string.Format("select DISTINCT orderid,Matchid,fromlocation from orders where Matchid = '{0}' group by orderid, Matchid,fromlocation ",
matchid);
LogHelper.WriteLogInfo("获取queue-拣货小车" + sql);
return db.GetsqlForDT(sql);
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("获取货位信息-拣货小车异常：" + ex.Message);
return null;
}
}
#endregion
#region 获取小车设备地址-拣货小车
public DataTable GetAddress(string fromlocation)
{
try
{
string sql = string.Format(" select address,ControlIP from v_address where Location = '{0}' ",
fromlocation);
LogHelper.WriteLogInfo("获取小车设备地址-拣货小车" + sql);
return db.GetsqlForDT(sql);
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("获取货位信息-拣货小车异常：" + ex.Message);
return null;
}
}
#endregion
#region 获取数据-拣货小车
public DataTable GetOrders_Car(string matchid)
{
try
{
//string sql = string.Format("select * from orders where Matchid = '{0}' and fromlocation like '%{1}%' ",
// matchid,
// carid);
string sql = string.Format("select * from orders where Matchid = '{0}' ",
matchid
);
return db.GetsqlForDT(sql);
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("获取货位信息-拣货小车异常：" + ex.Message);
return null;
}
}
#endregion
#region 获取数据matchid-拣货小车
public DataTable GetOrdersMatchid_Car(string orderid)
{
try
{
string sql = string.Format("select matchid from orders where orderid = '{0}' and state=0 ",
orderid);
LogHelper.WriteLogInfo("获取数据matchid===" + sql);
return db.GetsqlForDT(sql);
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("获取数据matchid-拣货小车异常：" + ex.Message);
return null;
}
}
#endregion
#region 获取订单信息
public DataTable Gettask(string barcode)
{
try
{
string sql = string.Format("select block from v_orders where state = 0 and oprationstate=0 and orderid='{0}' group by block ",
barcode
);
return db.GetsqlForDT(sql);
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("条码：" + barcode + "获取订单信d息异常：" + ex.Message);
return null;
}
}
#endregion
#region 根据库位编码查询标签地址
public DataTable GetAddressByLocation(string Location)
{
string sql = string.Format("select * from V_Address where Location='{0}' and type =1",
Location);
return db.GetsqlForDT(sql);
}
#endregion
#region 修改订单为未作业
public bool UpOrderstate()
{
try
{
string sql = string.Format(" update orders set oprationstate=0,oprationtime='',truequantity=0 where oprationstate=1 and state=0");
long x = db.UpdateSql(sql);
if (x > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("修改订单为未开始" + ex.Message);
return false;
}
}
#endregion
#region 修改订单为已读
public bool UpOrder(string bathcid)
{
try
{
string sql = string.Format(" update orders set oprationstate=1,oprationtime='{0}' where batchid='{1}'",
DateTime.Now.ToString(),
bathcid);
long x = db.UpdateSql(sql);
if (x > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("修改订单为已读" + ex.Message);
return false;
}
}
#endregion
#region 修改订单为已读
public bool UpOrder_Car(string orderid, string sku, string id = "")
{
try
{
string sql = string.Empty;
if (!string.IsNullOrEmpty(id))
{
sql = string.Format(" update orders set oprationstate=1,oprationtime='{0}' where matchid='{1}' and id='{2}'",
DateTime.Now.ToString(),
orderid,
id);
}
else
{
sql = string.Format(" update orders set oprationstate=1,oprationtime='{0}' where matchid='{1}' and sku='{2}'",
DateTime.Now.ToString(),
orderid,
sku);
}
long x = db.UpdateSql(sql);
if (x > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("修改订单为已读" + ex.Message);
return false;
}
}
#endregion
#region 修改订单为未拣货订单--挂起使用
public bool UpOrderState(string orderid)
{
try
{
string sql = string.Format(" update orders set oprationstate=0,oprationtime='' where orderid='{0}' and state<>3",
orderid);
long x = db.UpdateSql(sql);
if (x > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("修改订单为未拣货" + ex.Message);
return false;
}
}
#endregion
#region 修改订单为未拣货订单--挂起使用
public bool UpOrderStateBybat(string batchid)
{
try
{
string sql = string.Format(" update orders set oprationstate=0,oprationtime='' where batchid='{0}' and state<>3",
batchid);
long x = db.UpdateSql(sql);
if (x > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("修改订单为未拣货" + ex.Message);
return false;
}
}
#endregion
#region 查询标签所在拣货区
public string getBlock(string address)
{
string sql = string.Format("select block from Addressstorage where address='{0}' ",
address);
// LogHelper.WriteLogInfo("查询标签所在区getArea:" + sql);
DataTable dt = db.GetsqlForDT(sql);
if (dt.Rows.Count > 0)
{
return dt.Rows[0]["block"].ToString();
}
else
{
return "";
}
}
#endregion
#region 修改当前标签拣货状态
public bool UpdateWorkState(string barcode, string address, int truequantity)
{
string sql = string.Format(" select batchid from Addressstorage as addr left join orders on addr.location = Orders.tolocation where address = '{0}' and type = 1 and barcode = '{1}' and orders.oprationstate = 1",
address,
barcode);
DataTable dt = db.GetsqlForDT(sql);
if (dt.Rows.Count > 0)
{
string upsql = string.Format("update orders set oprationstate = 3 ,oprationtime='{0}',truequantity='{1}' where batchid ='{2}'",
DateTime.Now.ToString(),
truequantity,
dt.Rows[0]["batchid"].ToString()
);
long x = db.UpdateSql(sql);
if (x > 0)
return true;
return false;
}
else { return false; }
}
#endregion
#region 修改当前标签拣货状态
public bool UpdateWorkState(string orderid, int truequantity, string tolocation, string batchid)
{
try
{
string upsql = string.Format("update orders set oprationstate = 3 ,oprationtime='{0}',truequantity='{1}' where orderid='{2}' and Tolocation='{3}' and batchid='{4}' ",
DateTime.Now.ToString(),
truequantity,
orderid,
tolocation,
batchid);
long x = db.UpdateSql(upsql);
if (x > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("修改当前标签拣货状态异常：" + ex.Message);
return false;
}
}
#endregion
#region 修改当前标签拣货状态
2024-01-04 15:30:23 +08:00
public bool UpdateWorkState_Car(string matchid,string tolocation, string oprationstate )
添加项目文件。
2023-01-13 15:30:20 +08:00
{
try
{
lock (uplock)
{
2024-01-04 15:30:23 +08:00
string upsql = string.Format("update orders set oprationstate = '{0}' ,oprationtime='{1}' ,truequantity=quantity where matchid='{2}' and tolocation='{3}' ",
2023-12-05 14:35:20 +08:00
oprationstate,
DateTime.Now.ToString(),
添加项目文件。
2023-01-13 15:30:20 +08:00
matchid,
2024-01-04 15:30:23 +08:00
tolocation
添加项目文件。
2023-01-13 15:30:20 +08:00
);
long x = db.UpdateSql(upsql);
if (x > 0)
return true;
return false;
}
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("修改当前标签拣货状态异常：" + ex.Message);
return false;
}
}
#endregion
#region 修改门店拣货状态
public bool UpdateWorkStateByShop(string orderid)
{
try
{
string upsql = string.Format("update orders set oprationstate=3, state=3 where orderid='{0}'",
orderid);
long x = db.UpdateSql(upsql);
if (x > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("修改当前标签拣货状态异常：" + ex.Message);
return false;
}
}
#endregion
#region 修改门店拣货状态
public bool UpdateStateByOrderID(string orderid)
{
try
{
string upsql = string.Format("update orders set oprationstate=3,truequantity=quantity, state=3 where orderid='{0}'",
orderid);
long x = db.UpdateSql(upsql);
if (x > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("修改当前标签拣货状态异常：" + ex.Message);
return false;
}
}
#endregion
#region 修改当前小车拣货状态
public bool UpdateStateByCar(string matchid, string block = "", string sku = "")
{
try
{
string strwhere = string.Empty;
if (!string.IsNullOrEmpty(block))
{
strwhere = " and workblock='" + block + "'";
}
if (!string.IsNullOrEmpty(sku))
{
strwhere += " and sku='" + sku + "'";
}
string upsql = string.Format("update orders set state=3 where matchid = '{0}'" + strwhere,
matchid);
LogHelper.WriteLogInfo("修改波次拣货状态：" + upsql);
long x = db.UpdateSql(upsql);
if (x > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("修改当前小车拣货状态：" + ex.Message);
return false;
}
}
#endregion
#region 回传小车任务数据到server
public void SendDataToServer(string matchid)
{
string ServerAPIURL = ConfigurationManager.AppSettings["ServerAPIURL"];
DataTable dt = OrdershisList(matchid);
if (dt != null && dt.Rows.Count > 0)
{
string poststr = JsonHelper.SerializeObject(dt);
string results = http.HttpPost_Old(ServerAPIURL, poststr);
if (!string.IsNullOrEmpty(results))
{
LogHelper.WriteLogInfo("回传请求Server成功-----" + results);
ResultModel resmodel = JsonHelper.DeserializeObject<ResultModel>(results);
if (resmodel.result == "0")
{
LogHelper.WriteLogInfo("回传返回成功--qq---");
}
}
}
}
public DataTable OrdershisList(string matchid)
{
string sql = string.Format("select * from ordershis where state=3 where matchid = '{0}'",
matchid);
return db.GetsqlForDT(sql);
}
#region 订单回传完成
public void UpLoad_OrderOver(string matchid, string orderid)
{
try
{
string sql = string.Format("update ordershis set state=66,returntime='{0}' where matchid='{1}' and orderid='{2}' and state =3",
DateTime.Now.ToString(),
matchid,
orderid
);
db.UpdateSql(sql);
}
catch (Exception ex)
{
LogHelper.WriteLog(GetType(), "UpLoad_OrderOver_Error:" + ex.Message);
//throw;
}
}
#endregion
#region 获取回传列表
public DataTable BackList()
{
try
{
string sql = string.Format("select matchid,orderid from ordershis where state=3 group by matchid,orderid");
return db.GetsqlForDT(sql);
}
catch (Exception ex)
{
LogHelper.WriteLog(GetType(), "BackOrderList_Error:" + ex.Message);
return null;
}
}
#endregion
#region 获取回传明细列表
public DataTable BackOrderList(string orderid, string matchid)
{
try
{
string sql = string.Format("select * from ordershis where orderid='{0}' and matchid='{1}' and state=3",
orderid, matchid);
return db.GetsqlForDT(sql);
}
catch (Exception ex)
{
LogHelper.WriteLog(GetType(), "BackOrderList_Error:" + ex.Message);
return null;
}
}
#endregion
#endregion
#region orders绑定箱号
public bool bindBox(string boxcode, string matchid, string shopid, string traycode)
{
try
{
string upsql = string.Format("update orders set boxcode='{0}',bkbarcode='{1}' where matchid='{2}' and shopid='{3}' and state=0 and oprationstate<>3",
boxcode,
traycode,
matchid,
shopid);
LogHelper.WriteLogInfo("orders绑定箱号：" + upsql);
long x = db.UpdateSql(upsql);
if (x > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("修改当前小车拣货状态：" + ex.Message);
return false;
}
}
#endregion
#region 查询订单是否已绑定通道
public bool GetWorkblock(string block, string area)
{
try
{
string upsql = string.Format("select * from orders where workblock='{0}' and state=0 and oprationstate<>3",
block
);
LogHelper.WriteLogInfo("查询订单是否已绑定通道：" + upsql);
DataTable dt = db.GetsqlForDT(upsql);
if (dt != null && dt.Rows.Count > 0)
{
return false;
}
else
{
return true;
}
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("查询订单是否已绑定通道：" + ex.Message, LogHelper.Log_Type.ERROR);
return false;
}
}
#endregion
#region 查询订单是否已绑定通道
public bool GetWork_Match(string matchid)
{
try
{
string upsql = string.Format(" select * from orders where matchid = '{0}' and workblock<>0 and state<>3",
matchid
);
LogHelper.WriteLogInfo("2查询订单是否已绑定通道：" + upsql);
DataTable dt = db.GetsqlForDT(upsql);
if (dt != null && dt.Rows.Count > 0)
{
return false;
}
else
{
return true;
}
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("查询订单是否已绑定通道：" + ex.Message, LogHelper.Log_Type.ERROR);
return false;
}
}
#endregion
#region 修改订单作业区
public bool UpWorkblock(string matchid, string block)
{
try
{
string upsql = string.Format("update orders set workblock='{0}' where matchid='{1}' and state=0 and oprationstate<>3",
block,
matchid
);
LogHelper.WriteLogInfo("修改订单作业区1：" + upsql);
long x = db.UpdateSql(upsql);
if (x > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("修改订单作业区1异常：" + ex.Message, LogHelper.Log_Type.ERROR);
return false;
}
}
#endregion
#region 查询波次有任务的作业区
public DataTable GetAreaByMatch(string matchid, string block)
{
try
{
string upsql = string.Format(" select DISTINCT area from v_orders where matchid = '{0}' and block='{1}'",
matchid, block
);
if (block == "99")
{
upsql = string.Format(" select DISTINCT block,area from v_orders");
}
LogHelper.WriteLogInfo("查询波次有任务的作业区：" + upsql);
return db.GetsqlForDT(upsql);
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("修改订单作业区1异常：" + ex.Message, LogHelper.Log_Type.ERROR);
return null;
}
}
#endregion
#region 判断订单状态为已完成
public bool IsFinishWork(string orderid, int endcount, string matchid = "")
{
string sql = string.Format("select * from orders where matchid='{0}' and oprationstate<>3",
orderid);
LogHelper.WriteLogInfo("判断订单状态为已完成" + sql);
DataTable dt = db.GetsqlForDT(sql);
if (dt == null || dt.Rows.Count == endcount)
{
LogHelper.WriteLogInfo("判断订单状态为已完成true");
return true;
}
else
{
LogHelper.WriteLogInfo("判断订单状态为已完成false");
return false;
}
}
#endregion
#region 判断当前波次是否为已完成
public bool IsFinishWorkByCar(int endcount, string matchid, string block)
{
string sql = "";
if (!string.IsNullOrEmpty(block))
{
sql = string.Format("select matchid,sku from V_orders where oprationstate<>3 and state<>9 and state<>99 and matchid = '{0}' and block='{1}'",
matchid,
block);
}
else
{
sql = string.Format("select matchid,sku from V_orders where oprationstate<>3 and state<>9 and state<>99 and matchid = '{0}' ",
matchid
);
}
LogHelper.WriteLogInfo("判断当前波次是否为已完成" + sql);
DataTable dt = db.GetsqlForDT(sql);
if (dt == null || dt.Rows.Count == 0)
{
LogHelper.WriteLogInfo("判断当前波次是否为已完成true");
return true;
}
else
{
LogHelper.WriteLogInfo("判断订单状态为已完成false");
return false;
}
}
#endregion
#region 判断当前sku是否为已完成
public bool IsFinishWork(int endcount, string block, string usercode = "", string area = "", string sku = "", string shopid = "", string matchid = "")
{
string strwheres = "";
if (!string.IsNullOrEmpty(usercode))
{
strwheres = " and sku in(select sku from ordersqueue where usercode = '" + usercode + "')";
}
if (!string.IsNullOrEmpty(sku))
{
strwheres += " and sku ='" + sku + "'";
}
if (!string.IsNullOrEmpty(area))
{
strwheres += " and area ='" + area + "'";
}
if (!string.IsNullOrEmpty(shopid))
{
strwheres += " and shopid ='" + shopid + "'";
}
if (!string.IsNullOrEmpty(matchid))
{
strwheres += " and matchid ='" + matchid + "'";
}
string sql = "select matchid, sku from V_orders where oprationstate <> 3 and state<>99 and block ='" + block + "' " + strwheres;
LogHelper.WriteLogInfo("判断当前sku是否为已完成" + sql);
DataTable dt = db.GetsqlForDT(sql);
if (dt == null || dt.Rows.Count == 0)
{
LogHelper.WriteLogInfo("判断当前sku是否为已完成true");
return true;
}
else
{
LogHelper.WriteLogInfo("判断订单状态为已完成false");
return false;
}
}
#endregion
#region 查询下一区段区号
public int getOrderarea(string usercode, string block, string area)
{
string sql = string.Format("select min(area) as area from v_orders where oprationstate<>3 and sku in(select sku from ordersqueue where usercode='{0}') and block='{1}' and area>'{2}'",
usercode, block, area);
DataTable dt = db.GetsqlForDT(sql);
LogHelper.WriteLogInfo("查询下一区段区号:" + sql);
if (dt != null && dt.Rows.Count > 0)
{
return Convert.ToInt32(dt.Rows[0]["area"].ToString());
}
else
{
return 1;
}
}
#endregion
#region 查询第一个区是否有任务
public bool isTask(string block, string area)
{
try
{
string upsql = string.Format(" select DISTINCT area from v_orders where block='{0}' and area='{1}'",
block, area
);
LogHelper.WriteLogInfo("查询第一个区是否有任务：" + upsql);
DataTable dt = db.GetsqlForDT(upsql);
if (dt != null && dt.Rows.Count > 0)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("查询第一个区是否有任务：" + ex.Message, LogHelper.Log_Type.ERROR);
return false;
}
}
#endregion
#region 删除订单信息
public bool DelOrder(string matchid, string orderid)
{
try
{
string sql = string.Format(" delete from orders where orderid ='{0}' and matchid='{1}' and oprationstate=3 and state =3 ",
orderid,
matchid);
LogHelper.WriteLogInfo("-删除订单信息:" + sql);
long x = db.DeleteSql(sql);
if (x > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("取消订单出错" + ex.Message);
return false;
}
}
#endregion
#region 回传完成删除订单信息
public bool DelOrder_matchid(string matchid)
{
try
{
string sql = string.Format(" delete from orders where matchid ='{0}' and oprationstate=3 and state=3 ",
matchid);
LogHelper.WriteLogInfo("-回传完成:" + sql);
long x = db.DeleteSql(sql);
if (x > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("回传完成删除订单信息出错" + ex.Message);
return false;
}
}
#endregion
#region 修改订单状态为已完成
public bool UpFinishWork(string barcode)
{
string sql = string.Format("update orders set state =3 where barcode ='{0}'",
barcode);
long x = db.UpdateSql(sql);
if (x > 0)
return true;
return false;
}
#endregion
#region 查询标签所在区
public DataTable getArea(string address, string ip)
{
string sql = string.Format("select area,block,way,type from v_address where address='{0}' and ControlIP='{1}' group by block,area,way,type",
address,
ip);
LogHelper.WriteLogInfo("查询标签所在区getArea:" + sql);
DataTable dt = db.GetsqlForDT(sql);
return dt;
}
#endregion
#region 查询第一个作业区
public string getOrderArea(string block, string sku)
{
string sql = string.Format("select area from v_orders where block = '{0}' and sku in ({1}) and state=0 and oprationstate=0 group by area order by area",
block,
sku);
LogHelper.WriteLogInfo("查询第一个作业区:" + sql);
DataTable dt = db.GetsqlForDT(sql);
if (dt != null && dt.Rows.Count > 0)
{
return dt.Rows[0]["area"].ToString();
}
else
{
return "";
}
}
#endregion
#region 查询sku所在区
public string getAreaBysku(string sku)
{
string sql = string.Format("select block,area,way from addressstorage where location=(select top 1 tolocation from v_orders where sku='{0}')",
sku);
// LogHelper.WriteLogInfo("查询标签所在区getArea:" + sql);
DataTable dt = db.GetsqlForDT(sql);
if (dt.Rows.Count > 0)
{
return dt.Rows[0]["block"].ToString();
}
else
{
return "";
}
}
#endregion
#region 查询sku对应的所有任务
public DataTable getOrderListBysku(string block, string sku)
{
string sql = string.Format("select * from v_orders where block = '{0}' and sku ='{1}' ",
block,
sku);
LogHelper.WriteLogInfo("查询sku对应的所有任务:" + sql);
DataTable dt = db.GetsqlForDT(sql);
return dt;
}
#endregion
#region 查询退货单号查询所在区
public string getAreaBymatchid(string matchid)
{
string sql = string.Format("select block,area,way from addressstorage where location=(select top 1 tolocation from v_orders where matchid='{0}')",
matchid);
// LogHelper.WriteLogInfo("查询标签所在区getArea:" + sql);
DataTable dt = db.GetsqlForDT(sql);
if (dt.Rows.Count > 0)
{
return dt.Rows[0]["block"].ToString();
}
else
{
return "";
}
}
#endregion
#region 查询所在区是否完成作业
public bool IsFinishByArea(string barcode, string area)
{
bool isfinish = false;
string sql = string.Format("select area from orders left join Addressstorage as addr on addr.location = Orders.tolocation where barcode='{0}' and oprationstate=1 and area='{1}'",
barcode,
area);
// LogHelper.WriteLogInfo("查询所在区是否完成作业IsFinishByArea:" + sql);
DataTable dt = db.GetsqlForDT(sql);
if (dt.Rows.Count > 0)
{
if (dt.Rows.Count == 1)
{
isfinish = true;
}
}
return isfinish;
}
#endregion
#region 查询是否完成作业
public bool IsFinish(string barcode, string address)
{
string sql = string.Format(" select location ,barcode from Addressstorage as addr left join orders on addr.location=Orders.tolocation where type=1 and barcode = '{0}' and orders.oprationstate < 3",
barcode);
DataTable dt = db.GetsqlForDT(sql);
if (dt.Rows.Count > 0)
return false;
return true;
}
#endregion
#region 查询订单所在区
public DataTable GetArea(string block, string area, string way)
{
string sql = string.Format("select * from v_Address where block ='{0}' and area='{1}' and way='{2}' and type = 1",
block,
area,
way);
// LogHelper.WriteLogInfo("查询订单所在区:"+sql);
return db.GetsqlForDT(sql);
}
#endregion
#region 根据区域查询通道灯地址
public DataTable GetLightAddress(string area)
{
string sql = string.Format(" select address,controlIp from Addressstorage left join controller on Addressstorage.controlId =controller.ID where area='{0}' and type=4",
area);
// LogHelper.WriteLogInfo("根据区域查询通道灯地址GetLightAddress:" + sql);
DataTable dt = db.GetsqlForDT(sql);
return dt;
}
#endregion
#region 修改订单状态为信号灯点亮
public bool updateOpration(string barcode, string location)
{
string sql = string.Format("update orders set oprationstate=2 where tolocation ='{0}' and barcode='{1}'",
location,
barcode);
long x = db.UpdateSql(sql);
if (x > 0)
return true;
return false;
}
#endregion
#region 查询控制器列表
public DataTable getControlList(int type)
{
string sql = string.Format(" select * from controller where ControlType='{0}'",
type);
DataTable dt = db.GetsqlForDT(sql);
return dt;
}
#endregion
#region 查询控制器下所有标签地址
public DataTable getLabelList(string controlid)
{
string sql = string.Format(" select address from Addressstorage where type =1 and Controlid='{0}' group by address ",
controlid);
DataTable dt = db.GetsqlForDT(sql);
return dt;
}
#endregion
#region 查询控制器下所有标签地址
public DataTable getLabelList()
{
string sql = string.Format(" select * from Addressstorage ");
DataTable dt = db.GetsqlForDT(sql);
return dt;
}
#endregion
#region 查询控制器下所有标签地址
public DataTable getLightList(string controlid)
{
string sql = string.Format(" select * from Addressstorage where Controlid='{0}' and type =4",
controlid);
DataTable dt = db.GetsqlForDT(sql);
return dt;
}
#endregion
#region 根据箱条码获取门店号
public string getShopID(string barcode)
{
try
{
string sql = string.Format(" select top 1 shopid from orders where wmsboxcode='{0}' and state=0 and oprationstate=0",
barcode);
DataTable dt = db.GetsqlForDT(sql);
if (dt != null && dt.Rows.Count > 0)
{
return dt.Rows[0]["shopid"].ToString();
}
else
{
return "";
}
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("根据箱条码获取门店号异常：" + ex.Message);
return "";
}
}
#endregion
#region 获取门店总箱数
public int getbBoxCount(string shopid)
{
try
{
string sql = string.Format(" select count(wmsboxcode) as boxcount from (select distinct wmsboxcode from orders where shopid = '{0}' group by wmsboxcode) as A",
shopid);
DataTable dt = db.GetsqlForDT(sql);
if (dt != null && dt.Rows.Count > 0)
{
return Convert.ToInt32(dt.Rows[0]["boxcount"].ToString());
}
else
{
return 0;
}
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("获取门店总箱数异常：" + ex.Message);
return 0;
}
}
#endregion
//////---------------------------------
public static int count_orders = 0;
public static int count_ordershis = 0;
public static int count_storageLocation = 0;
public static int count_shopId = 0;
//---
#region 取消订单
public bool DeleteOrder(string str)
{
bool t = false;
string sql = "delete from Orders where 1=1 " + str;
long dt = db.DeleteSql(sql);
if (dt > 0)
{
t = true;
}
else
{
t = false;
}
return t;
}
#endregion
#region 分页获取订单表列表
public List<OrdersModel> GetOrdersListByPage(string strWhere, string orderby, int startIndex, int endIndex, out int recordCount)
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from (SELECT *,ROW_NUMBER() over(");
if (!string.IsNullOrEmpty(orderby.Trim()))
{
strSql.Append("order by T." + orderby);
}
else
{
strSql.Append("order by matchid desc");
}
strSql.Append(") as rownumber FROM v_orders WITH(NOLOCK)");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.AppendFormat(" WHERE 1=1 {0}", strWhere);
}
strSql.Append(") t");
strSql.AppendFormat(" where t.rownumber between {0} and {1}", startIndex, endIndex);
List<OrdersModel> list = ModelConvertHelper<OrdersModel>.ConvertToList(db.GetsqlForDT(strSql.ToString()));
strSql.Remove(0, strSql.Length);
strSql.Append("SELECT COUNT(*) FROM v_Orders AS T ");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.AppendFormat(" WHERE 1=1 {0}", strWhere);
}
object obj = db.GetsqlForDT(strSql.ToString()).Rows[0][0];
if (obj != null)
recordCount = Convert.ToInt32(obj);
else
recordCount = 0;
return list;
}
catch (Exception ex)
{
LogHelper.WriteLog(GetType(), ex.Message);
recordCount = 0;
return new List<Model.OrdersModel>();
}
}
#endregion
#region 分页获取订单表列表
public List<OrderReportModel> GetDasReportListByPage(string strWhere, string orderby, int startIndex, int endIndex, out int recordCount)
{
try
{
StringBuilder strSql = new StringBuilder();
//strSql.Append("select * from (SELECT *,ROW_NUMBER() over(");
strSql.Append("select * from (SELECT t1.shopid,t2.shopname,t1.totalCount,ISNULL(t2.FinishCount,0) AS FinishCount,ISNULL(t3.TodoCount,0) AS TodoCount,ROW_NUMBER() over(");
if (!string.IsNullOrEmpty(orderby.Trim()))
{
strSql.Append("order by T." + orderby);
}
else
{
//strSql.Append("order by matchid desc");
strSql.Append("order by t2.shopid desc");
}
strSql.Append(") as rownumber FROM (SELECT shopid, SUM(quantity) AS totalCount FROM orders GROUP BY shopid,shopname) AS t1 LEFT OUTER JOIN (SELECT SUM(quantity)/10 AS FinishCount,orders.shopid,name as shopname FROM orders join store on orders.shopid=store.shopid WHERE (oprationstate = 3) GROUP BY orders.shopid,name) AS t2 ON t1.shopid = t2.shopid LEFT OUTER JOIN (SELECT SUM(quantity) AS TodoCount,shopid,shopname FROM orders WHERE (oprationstate <> 3) GROUP BY shopid,shopname) AS t3 ON t1.shopid = t3.shopid ");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.AppendFormat(" WHERE 1=1 {0}", strWhere);
}
strSql.AppendFormat(") t where t.rownumber between {0} and {1}", startIndex, endIndex);
List<OrderReportModel> list = ModelConvertHelper<OrderReportModel>.ConvertToList(db.GetsqlForDT(strSql.ToString()));
strSql.Remove(0, strSql.Length);
//strSql.Append("SELECT COUNT(*) FROM v_skureport AS T ");
strSql.Append("SELECT count(*) FROM (SELECT shopid, COUNT(DISTINCT sku) AS totalCount FROM orders GROUP BY shopid,shopname) AS t1 LEFT OUTER JOIN (SELECT COUNT(DISTINCT sku) AS FinishCount,orders.shopid,name as shopname FROM orders join store on orders.shopid=store.shopid WHERE (oprationstate = 3) GROUP BY orders.shopid,name) AS t2 ON t1.shopid = t2.shopid LEFT OUTER JOIN (SELECT COUNT(DISTINCT sku) AS TodoCount,shopid,shopname FROM orders WHERE (oprationstate <> 3) GROUP BY shopid,shopname) AS t3 ON t1.shopid = t3.shopid");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.AppendFormat(" WHERE 1=1 {0}", strWhere);
}
object obj = db.GetsqlForDT(strSql.ToString()).Rows[0][0];
if (obj != null)
recordCount = Convert.ToInt32(obj);
else
recordCount = 0;
return list;
}
catch (Exception ex)
{
LogHelper.WriteLog(GetType(), ex.Message);
recordCount = 0;
return new List<Model.OrderReportModel>();
}
}
public List<OrdersModel> GetDasReportListByPageDetail(string strWhere, string orderby, int startIndex, int endIndex, out int recordCount)
{
try
{
StringBuilder strSql = new StringBuilder();
//strSql.Append("select * from (SELECT *,ROW_NUMBER() over(");
strSql.Append("select * from (SELECT orders.sku,skuinfo.goodsname,warehouseid,quantity,truequantity,oprationtime,ROW_NUMBER() over(");
if (!string.IsNullOrEmpty(orderby.Trim()))
{
strSql.Append("order by T." + orderby);
}
else
{
strSql.Append("order by matchid asc");
}
strSql.Append(") as rownumber FROM orders join skuinfo on skuinfo.sku=orders.sku ");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.AppendFormat(" WHERE 1=1 {0}", strWhere);
}
strSql.AppendFormat(") t where t.rownumber between {0} and {1}", startIndex, endIndex);
List<OrdersModel> list = ModelConvertHelper<OrdersModel>.ConvertToList(db.GetsqlForDT(strSql.ToString()));
strSql.Remove(0, strSql.Length);
strSql.Append("SELECT COUNT(*) FROM orders join skuinfo on skuinfo.sku=orders.sku ");
//strSql.Append("select sku,goodsname,warehouseid,quantity,truequantity,oprationtime from orders ");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.AppendFormat(" WHERE 1=1 {0}", strWhere);
}
object obj = db.GetsqlForDT(strSql.ToString()).Rows[0][0];
if (obj != null)
recordCount = Convert.ToInt32(obj);
else
recordCount = 0;
return list;
}
catch (Exception ex)
{
LogHelper.WriteLog(GetType(), ex.Message);
recordCount = 0;
return new List<Model.OrdersModel>();
}
}
#region 总体数量统计
public List<OrderReportModel> GetDasReportListTotalQuantity()
{
try
{
string sql = string.Format("select count(*),sum(totalCount) as totalCount,sum(FinishCount) as FinishCount,sum(TodoCount) as TodoCount from (SELECT t1.shopid,t2.shopname,t1.totalCount,ISNULL(t2.FinishCount,0) AS FinishCount,ISNULL(t3.TodoCount,0) AS TodoCount,ROW_NUMBER() over(order by t2.shopid desc) as rownumber " +
"FROM (SELECT shopid, SUM(quantity) AS totalCount FROM orders GROUP BY shopid,shopname) AS t1 LEFT OUTER JOIN (SELECT SUM(quantity)/10 AS FinishCount,orders.shopid,name as shopname FROM orders join store on orders.shopid=store.shopid WHERE (oprationstate = 3) GROUP BY orders.shopid,name) AS t2 " +
"ON t1.shopid = t2.shopid LEFT OUTER JOIN (SELECT SUM(quantity) AS TodoCount,shopid,shopname FROM orders WHERE (oprationstate <> 3) GROUP BY shopid,shopname) AS t3 ON t1.shopid = t3.shopid ) t where shopname is not null");
DataTable dt = db.GetsqlForDT(sql);
List<OrderReportModel> list = ModelConvertHelper<OrderReportModel>.ConvertToList(dt);
return list;
}
catch (Exception ex)
{
LogHelper.WriteLog(GetType(), ex.Message);
return new List<Model.OrderReportModel>();
}
}
#endregion
#endregion
#region 分页获取订单表列表
public List<OrderReportModel> GetDpsReportListByPage(string strWhere, string orderby, int startIndex, int endIndex, out int recordCount)
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from (SELECT *,ROW_NUMBER() over(");
if (!string.IsNullOrEmpty(orderby.Trim()))
{
strSql.Append("order by T." + orderby);
}
else
{
strSql.Append("order by matchid desc");
}
strSql.Append(") as rownumber FROM v_orderreport WITH(NOLOCK)");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.AppendFormat(" WHERE 1=1 {0}", strWhere);
}
strSql.Append(") t");
strSql.AppendFormat(" where t.rownumber between {0} and {1}", startIndex, endIndex);
List<OrderReportModel> list = ModelConvertHelper<OrderReportModel>.ConvertToList(db.GetsqlForDT(strSql.ToString()));
strSql.Remove(0, strSql.Length);
strSql.Append("SELECT COUNT(*) FROM v_orderreport AS T ");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.AppendFormat(" WHERE 1=1 {0}", strWhere);
}
object obj = db.GetsqlForDT(strSql.ToString()).Rows[0][0];
if (obj != null)
recordCount = Convert.ToInt32(obj);
else
recordCount = 0;
return list;
}
catch (Exception ex)
{
LogHelper.WriteLog(GetType(), ex.Message);
recordCount = 0;
return new List<Model.OrderReportModel>();
}
}
#endregion
#region 分页获取订单表列表
public List<OrderRepModel> GetOrdersListByPage_Rep(string strWhere, string orderby, int startIndex, int endIndex, out int recordCount)
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select Matchid as Matchid,count(*) as Matchcount ,count(DISTINCT sku) as Skucount,count(DISTINCT shopid) as Shopcount from v_orders");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.Append(" WHERE 1=1 " + strWhere);
}
strSql.Append(" GROUP BY matchid");
if (!string.IsNullOrEmpty(orderby.Trim()))
{
strSql.Append(" order by " + orderby);
}
else
{
strSql.Append(" order by matchid asc");
}
strSql.AppendFormat(" limit {0},{1}", startIndex, endIndex);
DataTable dt = db.GetsqlForDT(strSql.ToString());
List<OrderRepModel> list = ModelConvertHelper<OrderRepModel>.ConvertToList(dt);
strSql.Remove(0, strSql.Length);
strSql.Append("select count(*) from (SELECT COUNT(matchid) FROM v_Orders AS T ");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.AppendFormat(" WHERE 1=1 {0}", strWhere);
}
strSql.Append("group by matchid) as a");
object obj = db.GetsqlForDT(strSql.ToString()).Rows[0][0];
if (obj != null)
recordCount = Convert.ToInt32(obj);
else
recordCount = 0;
return list;
}
catch (Exception ex)
{
LogHelper.WriteLog(GetType(), ex.Message);
recordCount = 0;
return new List<Model.OrderRepModel>();
}
}
#endregion
#region 获取已完成条数
/// <summary>
/// 分页获取数据列表
/// </summary>
public DataTable GetOverCount(string strWhere, string matchid)
{
string sql = string.Format("select count(sku) from v_orders where oprationstate=3 and Matchid='" + matchid + "' " + strWhere);
DataTable dt = db.GetsqlForDT(sql);
return dt;
}
#endregion
#region 获取已完成门店数
/// <summary>
/// 分页获取数据列表
/// </summary>
public DataTable GetOverShopCount(string strWhere, string matchid)
{
string sql = string.Format("select distinct shopid from v_orders where oprationstate=3 and shopid not in(select distinct shopid from v_orders where oprationstate<>3) and Matchid='" + matchid + "' " + strWhere);
DataTable dt = db.GetsqlForDT(sql);
return dt;
}
#endregion
#region 获取已完成sku数
/// <summary>
/// 分页获取数据列表
/// </summary>
public DataTable GetOverSkuCount(string strWhere, string matchid)
{
string sql = string.Format("select count(distinct sku) from v_orders where oprationstate=3 and sku not in(select distinct sku from v_orders where oprationstate<>3) and Matchid='" + matchid + "' " + strWhere);
DataTable dt = db.GetsqlForDT(sql);
return dt;
}
#endregion
#region 分页获取历史表列表
public List<OrdersModel> GetOrdershisListByPage(string strWhere, string orderby, int startIndex, int endIndex, out int recordCount)
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM ordershis");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.Append(" WHERE 1=1 " + strWhere);
}
if (!string.IsNullOrEmpty(orderby.Trim()))
{
strSql.Append(" order by " + orderby);
}
else
{
strSql.Append(" order by orderid asc");
}
strSql.AppendFormat(" limit {0},{1}", startIndex, endIndex);
List<OrdersModel> list = ModelConvertHelper<OrdersModel>.ConvertToList(db.GetsqlForDT(strSql.ToString()));
strSql.Remove(0, strSql.Length);
strSql.Append("SELECT COUNT(*) FROM ordershis AS T ");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.AppendFormat(" WHERE 1=1 {0}", strWhere);
}
object obj = db.GetsqlForDT(strSql.ToString()).Rows[0][0];
if (obj != null)
recordCount = Convert.ToInt32(obj);
else
recordCount = 0;
return list;
}
catch (Exception ex)
{
LogHelper.WriteLog(GetType(), ex.Message);
recordCount = 0;
return new List<Model.OrdersModel>();
}
}
#endregion
#region 分页获取取消订单列表
public List<OrdersModel> GetOrderDelListByPage(string strWhere, string orderby, int startIndex, int endIndex, out int recordCount)
{
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM ( ");
strSql.Append(" SELECT ROW_NUMBER() OVER (");
if (!string.IsNullOrEmpty(orderby.Trim()))
{
strSql.Append("order by T." + orderby);
}
else
{
strSql.Append("order by shopid asc");
}
strSql.Append(")AS Row, T.* from Scheduling T WITH(NOLOCK) ");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.Append(" WHERE 1=1 " + strWhere);
}
strSql.Append(" ) TT");
strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
List<OrdersModel> list = ModelConvertHelper<OrdersModel>.ConvertToList(db.GetsqlForDT(strSql.ToString()));
strSql.Remove(0, strSql.Length);
strSql.Append("SELECT COUNT(*) FROM Scheduling AS T ");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.AppendFormat(" WHERE 1=1 {0}", strWhere);
}
object obj = db.GetsqlForDT(strSql.ToString()).Rows[0][0];
if (obj != null)
recordCount = Convert.ToInt32(obj);
else
recordCount = 0;
return list;
}
catch (Exception ex)
{
LogHelper.WriteLog(GetType(), ex.Message);
recordCount = 0;
return new List<Model.OrdersModel>();
}
}
#endregion
#region 分页获取出库排序订单列表
public List<OrdersModel> GetOrderShipmentListByPage(string strWhere, string orderby, int startIndex, int endIndex, out int recordCount)
{
string qq = "";
string sq = "SELECT DISTINCT TOP(100) PERCENT dbo.Orders.orderid AS orderid, " +
" dbo.Orders.matchid AS matchid, " +
" CAST(dbo.Orders.workblock AS bigint) AS workblock," +
" dbo.Orders.shopid " +
" FROM dbo.Orders INNER JOIN dbo.Addressstorage ON dbo.Orders.tolocation = dbo.Addressstorage.location " +
" WHERE(dbo.Orders.state = 0) AND(dbo.Orders.boxcode IS NULL) " +
" GROUP BY dbo.Addressstorage.block, dbo.Orders.orderid, " +
" CAST(dbo.Orders.workblock AS bigint), dbo.Orders.matchid," +
" dbo.Orders.shopid ORDER BY matchid, workblock";
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT Row,orderid,matchid, CAST(workblock AS varchar) AS workblock,shopid FROM ( ");
strSql.Append(" SELECT ROW_NUMBER() OVER (");
if (!string.IsNullOrEmpty(orderby.Trim()))
{
strSql.Append("order by T." + orderby);
}
else
{
strSql.Append("order by matchid,workblock asc");
}
strSql.Append(")AS Row, T.* from (" + sq + ") T ");
//if (!string.IsNullOrEmpty(strWhere.Trim()))
//{
// strSql.Append(" WHERE 1=1 " + strWhere);
//}
strSql.Append(" ) TT");
string s = strSql.Append(" WHERE TT.Row between {0} and {1}").ToString();
string sv = string.Format(s, startIndex, endIndex);
qq = string.Format(s, 0, 10000);
List<OrdersModel> list = ModelConvertHelper<OrdersModel>.ConvertToList(db.GetsqlForDT(sv));
strSql.Remove(0, strSql.Length);
strSql.Append("SELECT COUNT(*) FROM(" + qq + ")AS T ");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.AppendFormat(" WHERE 1=1 {0}", strWhere);
}
object obj = db.GetsqlForDT(strSql.ToString()).Rows[0][0];
if (obj != null)
recordCount = Convert.ToInt32(obj);
else
recordCount = 0;
return list;
}
catch (Exception ex)
{
LogHelper.WriteLog(GetType(), ex.Message);
recordCount = 0;
return new List<Model.OrdersModel>();
}
}
#endregion
#region 获取波次列表
public List<MatchModel> getMatchList(string key, string strWhere, int startIndex, int endIndex, out int recordCount)
{
try
{//SELECT " + key + ",workblock as Channel,Oprationstate as Oprationstate,State as OrState FROM orders
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from (SELECT " + key + ",readtime as Channel,State as OrState,ROW_NUMBER() over(");
if (!string.IsNullOrEmpty(key.Trim()))
{
strSql.Append(" order by " + key);
}
else
{
strSql.Append(" order by matchid asc");
}
strSql.Append(") as rownumber FROM orders WITH(NOLOCK) ");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.Append(" WHERE state<>3 " + strWhere);
}
else
{
strSql.Append(" WHERE state<>3 ");
}
strSql.AppendFormat(" group by " + key + ",readtime,State )t where t.rownumber between {0} and {1}", startIndex, endIndex);
DataTable dt = db.GetsqlForDT(strSql.ToString());
List<MatchModel> list = ModelConvertHelper<MatchModel>.ConvertToList(dt);
strSql.Remove(0, strSql.Length);
strSql.Append("SELECT COUNT(*) from (SELECT " + key + ",readtime as Channel,State as OrState FROM orders where state<>3 " + strWhere + " group by " + key + ",readtime,State) as T ");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.AppendFormat(" WHERE 1=1 {0}", strWhere);
}
object obj = db.GetsqlForDT(strSql.ToString()).Rows[0][0];
if (obj != null)
recordCount = Convert.ToInt32(obj);
else
recordCount = 0;
return list;
}
catch (Exception ex)
{
LogHelper.WriteLog(GetType(), ex.Message);
recordCount = 0;
return new List<Model.MatchModel>();
}
}
#endregion
#region 修改订单信息
public bool UpdateOrstate(OrdersModel model)
{
try
{
string strwhere = string.Empty;
if (!string.IsNullOrEmpty(model.matchid))
{
strwhere += " and matchid ='" + model.matchid + "'";
}
if (!string.IsNullOrEmpty(model.readtime))
{
strwhere += " and readtime ='" + model.readtime + "'";
}
if (!string.IsNullOrEmpty(model.shopid))
{
strwhere += " and shopid ='" + model.shopid + "'";
}
if (!string.IsNullOrEmpty(model.orderid))
{
strwhere += " and orderid ='" + model.orderid + "'";
}
string sql = string.Format("update orders set orderstate = '0',state='99' where 1=1" + strwhere,
DateTime.Now.ToString());
LogHelper.WriteLogInfo(" ");
long x = db.UpdateSql(sql);
if (x > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("修改订单状态出错1" + ex.Message);
return false;
}
}
#endregion
#region 修改订单信息
public bool UpdateOrderstate(OrdersModel model, bool qty = true)
{
try
{
string strwhere = string.Empty;
if (!string.IsNullOrEmpty(model.matchid))
{
strwhere += " and matchid ='" + model.matchid + "'";
}
if (!string.IsNullOrEmpty(model.readtime))
{
strwhere += " and readtime ='" + model.readtime + "'";
}
if (!string.IsNullOrEmpty(model.shopid))
{
strwhere += " and shopid ='" + model.shopid + "'";
}
if (!string.IsNullOrEmpty(model.orderid))
{
strwhere += " and orderid ='" + model.orderid + "'";
}
string sql = string.Format("update orders set state = 3,oprationtime='{0}',orderstate='4' where 1=1" + strwhere,
DateTime.Now.ToString());
if (qty)
{
sql = string.Format("update orders set state = 3,truequantity=quantity, oprationstate=3,oprationtime='{0}',orderstate='4' where 1=1" + strwhere,
DateTime.Now.ToString());
}
long x = db.UpdateSql(sql);
if (x > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("修改订单状态出错1" + ex.Message);
return false;
}
}
#endregion
public DataTable GetModifyOrder(string sql)
{
string sql2 = "select distinct orderid from Orders where 1=1" + sql;
return db.GetsqlForDT(sql2);
}
public long UpdateModifyOrder(string sql)
{
return db.UpdateSql(sql);
}
#region 接口通用
#region 订单删除
public void Delete_Order(string backupOrderWaveNo)
{
try
{
string sql = string.Format("delete from orders where orderid='{0}' and (state=9 or state =0)",
backupOrderWaveNo);
db.UpdateSql(sql);
}
catch (Exception ex)
{
LogHelper.WriteLog(GetType(), "单号：" + backupOrderWaveNo + "Delete_Order_ERROR:" + ex.Message);
//throw;
}
}
#endregion
#region 根据货位获取设备地址
public bool GetLocationState(string location)
{
try
{
string sql = string.Format("select address from Addressstorage where location='{0}'",
location);
DataTable dt = db.GetsqlForDT(sql);
if (dt == null || dt.Rows.Count == 0)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
LogHelper.WriteLog(GetType(), "GetLocationState:" + ex.Message);
return true;
}
}
#endregion
#region 查看订单是否已存在
public void GetOrderByBatchid(string batchid)
{
string sql = string.Format("select BatchId from orders where batchid='{0}' and oprationstate=0 and (state=0 or state=9 or state=99)",
batchid);
DataTable dt = db.GetsqlForDT(sql);
LogHelper.WriteLogInfo("查看订单是否已存在:" + sql);
if (dt != null && dt.Rows.Count > 0)
{
DeleteOrderByBatchid(batchid);
}
}
#endregion
#region 删除订单
public bool DeleteOrderByBatchid(string batchid)
{
try
{
string sql = string.Format(" delete from orders where batchid='{0}'",
batchid);
long x = db.UpdateSql(sql);
LogHelper.WriteLogInfo("删除订单:" + sql);
if (x > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("修改订单状态出错" + ex.Message);
return false;
}
}
#endregion
#region 添加订单信息
public bool AddOrder(DetailModel item, string OrderID, string DownDate, string classes)
{
try
{
GetOrderByBatchid(item.BatchId);//删除已存在的订单
string sql = string.Format("insert into orders(orderid,BatchId,Tolocation,Sku,GoodsName,Discount,ProdArea,Quantity,lotno,goodstype,state,downdate,readtime,corlorcode,checkquantity,classes,sizecode) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}')",
OrderID,
item.BatchId,
item.Tolocation,
item.Sku,
item.GoodsName,
item.Discount,
item.Manufactor,
item.Quantity,
item.EffectivePeriod,
item.GoodsType,
9,
DownDate,
DateTime.Now.ToString(),
item.WindowNO,
item.Stock,
classes,
item.BgColor
);
long x = db.UpdateSql(sql);
if (x > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("添加订单出错" + ex.Message);
return false;
}
}
#endregion
#region 添加订单信息
public bool InsertOrder(OrdersModel item)
{
try
{
string Car_ID = ConfigurationManager.AppSettings["CARID"];
string sql = string.Format("insert into orders(orderid,BatchId,Tolocation,Quantity,Sku,GoodsName,LotNo,state,downdate,readtime,Shopid,Wmsboxcode,Prodarea,Discount,unit,Tasktype,fromlocation,matchid,CheckQuantity,BoxWeight,WholeUnit) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}')",
item.orderid,
item.batchid,
item.tolocation,
item.quantity,
item.sku,
item.goodsname,
item.lotno,
0,
DateTime.Now,
DateTime.Now.ToString(),
item.shopid,
item.wmsboxcode,
item.prodarea,
item.discount,
item.unit,
item.tasktype,
item.fromlocation,
item.matchid,
item.checkquantity,
item.boxweight,
item.wholeunit
);
LogHelper.WriteLogInfo("添加订单信息:--" + sql);
long x = db.UpdateSql(sql);
if (x > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("添加订单出错" + ex.Message);
return false;
}
}
#endregion
#region 获取订单信息
public List<OrdersModel> getorders(string batchid)
{
string sql = string.Format("select * from orders where batchid = '{0}'",
batchid);
DataTable dt = db.GetsqlForDT(sql);
return ModelConvertHelper<OrdersModel>.ConvertToList(dt);
}
#endregion
#region 获取订单信息
public DataTable getordersBatchid(string batchid)
{
string sql = string.Format("select batchid from orders where batchid like '%{0}%' order by batchid desc ",
batchid);
return db.GetsqlForDT(sql);
}
#endregion
#region 修改订单信息
public bool UpdateOrderstate(string matchid)
{
try
{
string sql = string.Format(" update orders set state = 3,oprationtime='{0}',orderstate='4' where matchid='{1}'",
DateTime.Now.ToString(),
matchid);
long x = db.UpdateSql(sql);
if (x > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("修改订单状态出错1" + ex.Message);
return false;
}
}
#endregion
#region 查看波次是否开始作业
public bool getMatchWork(string matchid)
{
try
{
string sql = string.Format(" select * from orders where matchid='{0}' and (oprationstate<>0 or state<>0)",
matchid);
DataTable dt = db.GetsqlForDT(sql);
if (dt != null && dt.Rows.Count > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("查看波次是否开始作业异常" + ex.Message);
return false;
}
}
#endregion
#region 修改订单信息-拣货小车
public bool UpdateOrderstate_Back(OrdersModel model)
{
try
{
string sql = string.Format(" update orders set state =3,oprationstate=3,truequantity='{0}',oprationtime='{1}',boxcode='{7}',workblock='{8}' where orderid='{2}' and matchid='{3}' and fromlocation='{4}' and sku='{5}' and tolocation='{6}'",
model.truequantity,
model.oprationtime,
model.orderid,
model.matchid,
model.fromlocation,
model.sku,
model.tolocation,
model.boxcode,
model.workblock);
LogHelper.WriteLogInfo("修改订单信息-拣货小车" + sql);
long x = db.UpdateSql(sql);
if (x > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("修改订单信息-拣货小车" + ex.Message);
return false;
}
}
#endregion
#region 修改订单信息
public bool Updatestate(string matchid, string readdate)
{
try
{
string sql = string.Format(" update orders set state =0 where matchid='{0}' and readtime='{1}' and state=99",
matchid,
readdate);
long x = db.UpdateSql(sql);
if (x > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("修改订单状态出错1" + ex.Message);
return false;
}
}
#endregion
#region 查询通道信息
public DataTable GetWayList(string orderid)
{
try
{
string sql = string.Format(" select distinct way from V_Orders where orderid='{0}'",
orderid);
return db.GetsqlForDT(sql);
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("查询通道信息异常：" + ex.Message);
return null;
}
}
#endregion
#region 查询通道灯设备信息
public DataTable GetWayLightList(string orderid)
{
try
{
string sql = string.Format(" select * from V_Address where way in(select distinct way from V_Orders where orderid='{0}') and type =4",
orderid);
return db.GetsqlForDT(sql);
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("查询通道灯设备信息异常：" + ex.Message);
return null;
}
}
#endregion
#region 查询通道灯设备信息
public DataTable GetLightWay(string way)
{
try
{
string sql = string.Format(" select * from V_Address where way ='{0}' and type =4",
way);
return db.GetsqlForDT(sql);
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("查询通道灯设备信息2：" + ex.Message);
return null;
}
}
#endregion
#endregion
#region 查询波次列表
public List<MatchModel> getMatchList(string strwhere)
{
string sql = string.Format("SELECT matchid as Matchid,readtime as Channel,State as OrState FROM orders where state<>3 " + strwhere + " group by matchid,readtime,state");
DataTable dt = db.GetsqlForDT(sql);
return ModelConvertHelper<MatchModel>.ConvertToList(dt);
}
#endregion
#region 查询波次列表
public DataTable getShopList(string matchid)
{
string sql = string.Format("select tolocation,shopid,address,ControlIP,matchid,block,area from v_orders where matchid='{0}' and state =9 group by tolocation,shopid",
matchid
);
return db.GetsqlForDT(sql);
}
#endregion
#region 查询波次通道是否被占用
public DataTable getShopBlockList(string matchid)
{
string sql = string.Format("select * from v_orders where state = 0",
matchid
);
return db.GetsqlForDT(sql);
}
#endregion
#region 获取商品名称
public string GetGoodsname(string sku)
{
string sql = string.Format("select top 1 goodsname from orders where sku='{0}'",
sku
);
DataTable dt = db.GetsqlForDT(sql);
if (dt != null && dt.Rows.Count > 0)
{
return dt.Rows[0]["goodsname"].ToString();
}
else
{
return "";
}
}
#endregion
增加小格口绑定
2024-01-15 17:37:47 +08:00
#region 修改小格口号
public bool UpdateSkuLoc(string sku,string smallloc)
{
try
{
2024-12-31 17:16:22 +08:00
string sql = string.Format(" update orders set partcode = '{0}' where sku='{1}' and (partcode is null or partcode='')",
增加小格口绑定
2024-01-15 17:37:47 +08:00
smallloc,
sku);
long x = db.UpdateSql(sql);
LogHelper.WriteLogInfo(x+"修改小格口信息"+sql);
if (x > 0)
return true;
return false;
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("修改小格口号出错1" + ex.Message);
return false;
}
}
#endregion
2025-01-13 13:47:28 +08:00
#region 查询小格口是否都已绑定
public bool getSmallLocList(string loc)
{
string sql = string.Format("select top 1 * from orders where (partcode is null or partcode='') and oprationstate=3 and fromlocation='{0}'", loc);
DataTable dt = db.GetsqlForDT(sql);
if (dt != null && dt.Rows.Count > 0)
{
return false;
}
else
{
return true;
}
}
#endregion
#region 生成箱号
public bool CreateBoxInfo(string boxcode, string loc, string type)
{
try
{
添加项目文件。
2023-01-13 15:30:20 +08:00
2025-01-13 13:47:28 +08:00
//添加打印信息
string printsql = string.Format("insert into print_sku(orderid,matchid,state,addtime,type) values('{0}','{1}','{2}','{3}','{4}')",
boxcode, loc, "0", DateTime.Now.ToString(), type);
int x = db.InsertSql(printsql);
if (x > 0)
{
//更新订单状态为已完成
string sql = string.Format("update orders set state =3,boxcode='{0}' where fromlocation='{1}' and state<>3 and oprationstate=3",
boxcode, loc);
long y = db.UpdateSql(sql);
LogHelper.WriteLogInfo(y + "更新订单完成" + sql);
//查询此货位是否存在未播商品
//string querysql = string.Format("select * from orders where oprationstate<>3 and fromlocation='{0}'",loc);
//DataTable querydt = db.GetsqlForDT(querysql);
//if (querydt != null && querydt.Rows.Count > 0)
//{
// LogHelper.WriteLogInfo(loc+"还有商品未分配完，不释放格口");
// //不释放格口
// return true;
//}
//else {
// //释放格口
// string upsql = string.Format("update addressstorage set bkaddress=0,addresstype='' where location='{0}'", loc);
// long b = db.UpdateSql(upsql);
// if (b > 0)
// {
// return true;
// }
// else
// {
// LogHelper.WriteLogInfo("释放格口失败" + upsql);
// return false;
// }
//}
return true;
}
else
{
LogHelper.WriteLogInfo("添加打印箱标签失败" + printsql);
return false;
}
}
catch (Exception ex)
{
LogHelper.WriteLogInfo("完成格口异常" + ex.ToString());
return false;
}
}
#endregion
添加项目文件。
2023-01-13 15:30:20 +08:00
}
}
Reference in New Issue Copy Permalink