898 lines
40 KiB
C#
898 lines
40 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Linq;
|
||
using System.Text;
|
||
using System.Data;
|
||
|
||
using WMS.DBUtility;
|
||
using WMS.IData.ICK;
|
||
using WMS.IData;using Oracle.ManagedDataAccess.Client;
|
||
using WMS.Model.CK;
|
||
using WMS.Model.RK;
|
||
using System.Diagnostics;
|
||
|
||
namespace WMS.SqlServerData.CKData
|
||
{
|
||
public class PickingWaveGoodsData : IPickingWaveGoods
|
||
{
|
||
|
||
public bool getPickData(string locid)
|
||
{
|
||
|
||
string sqlstr = "select * from T_CK_PICKINGWAVEGOODS where LOC_ID='"+locid+ "' ";
|
||
DataTable dt= SystemDataObject.Instance.GetDataTable(sqlstr);
|
||
if (dt.Rows.Count > 0)
|
||
{
|
||
return true;
|
||
}
|
||
else
|
||
{
|
||
return false;
|
||
}
|
||
|
||
}
|
||
|
||
|
||
|
||
public bool GetPickData_BARCODE (string BARCODE)
|
||
{
|
||
|
||
string sqlstr = "select * from T_CK_PICKINGWAVEGOODS where BARCODE='" + BARCODE + "' ";
|
||
DataTable dt = SystemDataObject.Instance.GetDataTable(sqlstr);
|
||
if (dt.Rows.Count > 0)
|
||
{
|
||
return true;
|
||
}
|
||
else
|
||
{
|
||
return false;
|
||
}
|
||
|
||
}
|
||
|
||
public bool getPickDataCount()
|
||
{
|
||
|
||
//string sqlstr = "select count(*) from t_ck_pickingwavegoods where length(pickingid)<30 and length(outstand)<3";
|
||
string sqlstr = "select distinct CTL from t_ck_pickingwavegoods where STATUS in(0,1)";
|
||
DataTable dt = SystemDataObject.Instance.GetDataTable(sqlstr);
|
||
if (dt.Rows.Count >9)
|
||
{
|
||
return true;
|
||
}
|
||
else
|
||
{
|
||
return false;
|
||
}
|
||
|
||
}
|
||
|
||
|
||
public bool getPickDataCount(int num)
|
||
{
|
||
|
||
//string sqlstr = "select count(*) from t_ck_pickingwavegoods where length(pickingid)<30 and length(outstand)<3";
|
||
string sqlstr = "select distinct CTL from t_ck_pickingwavegoods where STATUS in(0,1)";
|
||
DataTable dt = SystemDataObject.Instance.GetDataTable(sqlstr);
|
||
if (dt.Rows.Count >= num)
|
||
{
|
||
return true;
|
||
}
|
||
else
|
||
{
|
||
return false;
|
||
}
|
||
|
||
}
|
||
public bool getLocS(string locid)
|
||
{
|
||
|
||
string sqlstr = "select * from t_base_storage_area_location where location_id='" + locid + "' and status='0'";
|
||
DataTable dt = SystemDataObject.Instance.GetDataTable(sqlstr);
|
||
if (dt.Rows.Count > 0)
|
||
{
|
||
return false;
|
||
}
|
||
else
|
||
{
|
||
return true;
|
||
}
|
||
|
||
}
|
||
|
||
public string getYckLoc(string layer)
|
||
{
|
||
|
||
string sqlstr = "select location_id from t_base_storage_area_location where storage_id='JXJWYCK' and status='0' and layer1='"+layer+ "' order by row1 desc ";
|
||
DataTable dt = SystemDataObject.Instance.GetDataTable(sqlstr);
|
||
if (dt.Rows.Count > 0)
|
||
{
|
||
return dt.Rows[0][0].ToString();
|
||
}
|
||
else
|
||
{
|
||
return "";
|
||
}
|
||
|
||
}
|
||
public DataTable getDecid(string decid)
|
||
{
|
||
|
||
string sqlstr = "select * from T_DECNAME where decid='" + decid + "'";
|
||
return SystemDataObject.Instance.GetDataTable(sqlstr);
|
||
|
||
}
|
||
public DataTable getRemark(string locid)
|
||
{
|
||
|
||
string sqlstr = "select * from t_base_storage_area_location where location_id='" + locid + "'";
|
||
return SystemDataObject.Instance.GetDataTable(sqlstr);
|
||
|
||
}
|
||
public DataTable getyck(string remark,string layer)
|
||
{
|
||
|
||
string sqlstr = "select DISTINCT D.LOCATION_ID ,D.STORAGE_ID,D.GOODS_ID,D.SHELVES_NUM,D.CTL,D.PROVIDER_ID,D.STORAGE_ID,D.SORT FROM (select a.*,B.SORT from t_mi_stock a left join t_base_storage_area_location b on a.location_id=b.location_id where b.remark='" + remark+"' and b.layer1='"+layer+ "' and b.storage_id='JXJWYCK' ) D ORDER BY SORT DESC";
|
||
return SystemDataObject.Instance.GetDataTable(sqlstr);
|
||
|
||
}
|
||
public DataTable getYK(string ctl)
|
||
{
|
||
|
||
string sqlstr = "select * from t_ck_pickingwavegoods where CTL='"+ ctl + "' and status in ('0','1','2') ";
|
||
return SystemDataObject.Instance.GetDataTable(sqlstr);
|
||
|
||
}
|
||
/// <summary>
|
||
/// 根据出库单查询拣选明细
|
||
/// </summary>
|
||
/// <param name="outId"></param>
|
||
/// <returns></returns>
|
||
public DataTable GetOutDataId(string outId)
|
||
{
|
||
string sqlStr = " select top 1 outStand from T_CK_PICKINGWAVEGOODS where out_id='" + outId + "'";
|
||
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
||
}
|
||
public DataTable GetTaskOutCtStand(string stand)
|
||
{
|
||
string sqlStr = "select count(*)a from T_CK_PICKINGWAVEGOODS where outStand='" + stand+"'";
|
||
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 根据托盘删除拣选任务
|
||
/// </summary>
|
||
/// <param name="ctl"></param>
|
||
public void DelCtlPickGoods(string ctl, string STORAGE_ID)
|
||
{
|
||
string sqlStr = " Delete T_CK_PICKINGWAVEGOODS where ctl='" + ctl + "' and STORAGE_ID='" + STORAGE_ID + "'";
|
||
SystemDataObject.Instance.ExecuteSql(sqlStr);
|
||
}
|
||
/// <summary>
|
||
/// 获取站台
|
||
/// </summary>
|
||
/// <returns></returns>
|
||
public DataSet GetStation(string STORAGE_ID)
|
||
{
|
||
string sqlstr = "select t.ConveyorID from T_ConveyorStatus t where" +
|
||
" t.optype = '2' and STORAGE_ID='" + STORAGE_ID + "' and t.ConveyorID not in " +
|
||
"(select outStand from T_CK_PICKINGWAVEGOODS where STORAGE_ID='"+ STORAGE_ID + "' group by outStand);" +
|
||
"select count(OUT_ID)a,outStand from T_CK_PICKINGWAVEGOODS where STORAGE_ID='"+ STORAGE_ID + "' and outStand is not null group by outStand order by a ";
|
||
return SystemDataObject.Instance.GetDataSet(sqlstr);
|
||
}
|
||
|
||
public DataTable GetLocaStationAllData(string locID)
|
||
{
|
||
string sqlStr =@" select outStand, LOCATION_ID,[row], cln, layer from[dbo].[T_BASE_STORAGE_AREA_LOCATION] where LOCATION_ID = '" + locID + "'"
|
||
+ " union ALL "
|
||
+ " select outStand1, LOCATION_ID,[row], cln, layer from[dbo].[T_BASE_STORAGE_AREA_LOCATION] where LOCATION_ID = '" + locID + "'"
|
||
+ " union ALL "
|
||
+ " select outStand3, LOCATION_ID,[row], cln, layer from[dbo].[T_BASE_STORAGE_AREA_LOCATION] where LOCATION_ID = '" + locID + "' and outStand3!=0 ";
|
||
|
||
|
||
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
||
}
|
||
public DataTable GetTaskdataHand()
|
||
{
|
||
string sqlStr = @" select pickingid, putin_id, docctnumber, good_id, loc_id, goods_num, specification,
|
||
production_date, ware_date, provider_id, unit, remark, out_id, scale_unit,
|
||
mistock_num, picking_num, price, storage_area_id, storage_id, ctl, goods_num_sj,
|
||
typests, packing_num, produclotid, barcode, goods_name, port, goods_measure_id,
|
||
datatype, case ck_type when '1' then '出库' when '2' then '移库' end ck_type,
|
||
customermemberid, plcid, towhseloc,
|
||
outstand, CASE WHEN STATUS = '0' THEN '未下发' WHEN STATUS = '1' THEN '正在执行' WHEN STATUS = '6'
|
||
THEN '堆垛机过账完成' WHEN STATUS = '9' THEN '手持拣货完成' END as STATUS from T_CK_PICKINGWAVEGOODS order by WARE_DATE";
|
||
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
||
}
|
||
|
||
public DataTable GetDataT_ck_orderDetall()
|
||
{
|
||
string sqlStr = "select * from T_CK_ORDERDETAIL where sts='8' order by ORDER_ID, endtime ";
|
||
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
||
}
|
||
|
||
public DataTable GetTaskOutStatus()
|
||
{
|
||
//PDA拣货完成状态9，EBS出库完成改成10， 等待EBS拣货完成才能扣减库存--目前改成手持完成后就ok
|
||
string sqlStr = "select * from T_CK_PICKINGWAVEGOODS where status='9' ";
|
||
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
||
}
|
||
|
||
public DataTable GetTaskOutStatus_0()
|
||
{
|
||
//查询当前未下发的出库任务
|
||
string sqlStr = "select * from T_CK_PICKINGWAVEGOODS where status='0' order by ware_date asc ";
|
||
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
||
}
|
||
|
||
public DataTable GetTaskOutStatus_Empty()
|
||
{
|
||
//空箱出库完成
|
||
string sqlStr = "select * from T_CK_PICKINGWAVEGOODS where status='6'and GOOD_ID='000000' ";
|
||
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
||
}
|
||
|
||
public DataTable GetTaskMoveStatus()
|
||
{
|
||
//移库完成
|
||
string sqlStr = "select * from T_CK_PICKINGWAVEGOODS where status='12' ";
|
||
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
||
}
|
||
|
||
public DataTable GetTaskLocOutStatus(string loc)
|
||
{
|
||
string sqlStr = "select * from T_CK_PICKINGWAVEGOODS where LOC_ID ='" + loc + "'";
|
||
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
||
}
|
||
|
||
|
||
|
||
|
||
|
||
public DataTable GetLocHand()
|
||
{
|
||
string sqlStr = "select * from T_BASE_STORAGE_AREA_LOCATION where STATUS='1' order by sort";
|
||
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
||
}
|
||
//public void InsertIoErp(Model.Pak_io_complete ioComplete)//Ymd,
|
||
//{
|
||
// string sqlStr =@"insert into pak_io_complete(Sheet_no,Pdc_no,Pdc_name,Io_type,
|
||
// PO,Qty,Wrk_no,status,Io_status,StoreBaseUom,SrcDoc_ID,SrcDocLine_ID,
|
||
// AllDoc_Type,Version,Org,ItemMaster,SPEC,Memo,Appe_user,
|
||
// Appe_time,Modi_user,Modi_time,Crtd_User,Lupd_User,
|
||
// CompanyID,SiteID,Batnbr,Inventory_Line_ID,UnitDesc,Whseloc,ToWhseloc,Tooling,Manufacturedate,
|
||
// PalletID,Location) values";
|
||
// sqlStr = sqlStr + " ('" + ioComplete.Sheet_no + "','" + ioComplete.Pdc_no + "','" + ioComplete.Pdc_name + "',"
|
||
// + "'"+ ioComplete .Io_type+ "','"+ ioComplete .Po+ "','"+ ioComplete .Qty+ "','"+ ioComplete .Wrk_no+ "',"
|
||
// + "'"+ ioComplete.Status + "','" + ioComplete.Io_status + "','" + ioComplete .StoreBaseUom+ "','"+ ioComplete .SrcDoc_ID+ "',"
|
||
// +" '"+ ioComplete .SrcDocLine_ID+ "','"+ ioComplete .AllDoc_Type + "','"+ ioComplete.Version+ "',"
|
||
// + " '"+ ioComplete.Org + "','"+ ioComplete.ItemMaster + "','"+ ioComplete .SPEC+ "','"+ ioComplete .Memo+ "',"
|
||
// +" '"+ ioComplete.Appe_user + "',convert(varchar(200),'" + ioComplete.Appe_time + "',103),'" + ioComplete .Modi_user+ "',"
|
||
// + " convert(varchar(200),'" + ioComplete.Modi_time + "',103),'" + ioComplete.Crtd_User + "',"
|
||
// + "'"+ ioComplete.Lupd_User + "', '" + ioComplete .CompanyID+ "',"
|
||
// +"'"+ ioComplete.SiteID + "','"+ ioComplete .Batnbr+ "','"+ ioComplete .Inventory_Line_ID+ "',"
|
||
// +"'"+ ioComplete.UnitDesc + "','"+ ioComplete .Whseloc+ "','"+ ioComplete .ToWhseloc+ "',"
|
||
// +"'"+ ioComplete.Tooling + "',getdate(),'"+ ioComplete .PalletID+ "','" +ioComplete.ASRS_Location + "')";
|
||
|
||
|
||
// SystemDataObject.Instance.ExecuteSql(sqlStr);
|
||
//}
|
||
|
||
/// <summary>
|
||
/// 任务下发到PLC写重量
|
||
/// </summary>
|
||
/// <param name="locId"></param>
|
||
public void UpDateWghMisBak(string barcode,string wgh)
|
||
{
|
||
string sqlStr = "update T_MI_STOCK_BACK set WGH='" + wgh + "' where BARCODE='" + barcode + "' ";
|
||
SystemDataObject.Instance.ExecuteSql(sqlStr);
|
||
}
|
||
|
||
public void UpDateSort(string locid, string sort)
|
||
{
|
||
string sqlStr = "update t_base_storage_area_location set sort='" + sort + "' where location_id='" + locid + "' ";
|
||
SystemDataObject.Instance.ExecuteSql(sqlStr);
|
||
}
|
||
/// <summary>
|
||
/// 任务下发到PLC将Status置为完成
|
||
/// </summary>
|
||
/// <param name="locId"></param>
|
||
public void UpdateStsPIckEnd(string ProDucLotId,string sts)
|
||
{
|
||
string sqlStr = "update T_CK_PICKINGWAVEGOODS set STS='"+sts+"' where ProDucLotId='" + ProDucLotId + "' ";
|
||
SystemDataObject.Instance.ExecuteSql(sqlStr);
|
||
}
|
||
/// <summary>
|
||
/// 更改拣选的数量
|
||
/// </summary>
|
||
/// <param name="pickId"></param>
|
||
/// <param name="goodsId"></param>
|
||
/// <param name="picnum"></param>
|
||
public void UpdaPickNum(string pickId, string goodsId, decimal picnum)
|
||
{
|
||
string sqlStr = "update T_CK_PICKINGWAVEGOODS set GOODS_NUM_SJ=GOODS_NUM_SJ+" + picnum + " " +
|
||
" where PICKINGID='" + pickId + "' and goodsId='"+goodsId+"' ";
|
||
SystemDataObject.Instance.ExecuteSql(sqlStr);
|
||
}
|
||
/// <summary>
|
||
/// 出库更新库位表为空闲
|
||
/// </summary>
|
||
/// <param name="locId"></param>
|
||
public void UpdateLocSts(string locId)
|
||
{
|
||
string sqlStr = "update T_BASE_STORAGE_AREA_LOCATION set HASVOLUME='0',STATUS='0' where LOCATION_ID='" + locId + "' ";
|
||
SystemDataObject.Instance.ExecuteSql(sqlStr);
|
||
}
|
||
/// <summary>
|
||
/// 获取捡货明细
|
||
/// </summary>
|
||
/// <returns>捡货明细</returns>
|
||
public DataTable GetPickingWaveGoodsDT(PickingWaveGoodsModel model)
|
||
{
|
||
StringBuilder strSql = new StringBuilder();
|
||
|
||
strSql.AppendLine("select ");
|
||
strSql.AppendLine(" a.PICKINGID, --拣货号 ");
|
||
strSql.AppendLine(" a.OUT_ID, --出库通知号 ");
|
||
strSql.AppendLine(" a.PUTIN_ID, --入库批次号 ");
|
||
strSql.AppendLine(" a.DOCCTNUMBER, --单据编号（调整填写调整号，入库填写入库号） ");
|
||
strSql.AppendLine(" a.GOOD_ID, --GoodsNumber ");
|
||
strSql.AppendLine(" a.LOC_ID, --库位编号 ");
|
||
strSql.AppendLine(" a.GOODS_NUM, --本波次拣货数量 ");
|
||
strSql.AppendLine(" a.SPECIFICATION, --规格 ");
|
||
strSql.AppendLine(" a.PRODUCTION_DATE,--生产日期 ");
|
||
strSql.AppendLine(" a.WARE_DATE, --上架时间 ");
|
||
strSql.AppendLine(" a.PROVIDER_ID, --供应商编号 ");
|
||
strSql.AppendLine(" a.REMARK, --备注 ");
|
||
strSql.AppendLine(" a.UNIT, --UNIT ");
|
||
strSql.AppendLine(" a.SCALE_UNIT, --重量UNIT ");
|
||
strSql.AppendLine(" b.goods_name, --商品名称 ");
|
||
strSql.AppendLine(" a.MISTOCK_NUM, --某库位某商品库存数 ");
|
||
strSql.AppendLine(" a.PICKING_NUM, --某商品要捡货总数 ");
|
||
strSql.AppendLine(" a.PRICE, --标准价格 ");
|
||
strSql.AppendLine(" c.provider_name, --供应商名称 ");
|
||
strSql.AppendLine(" a.ctl, --供应商名称 ");
|
||
strSql.AppendLine(" a.barcode, --供应商名称 ");
|
||
strSql.AppendLine(" a.ProDucLotId, --供应商名称 ");
|
||
strSql.AppendLine(" (case a.[status] when '0' then '未执行' when '1' then '下发成功' when '3' then '需要拣货' when '4' then '完成' end) as [status] ");
|
||
strSql.AppendLine("FROM ");
|
||
strSql.AppendLine(" T_CK_PICKINGWAVEGOODS a left join t_base_goods b ");
|
||
strSql.AppendLine(" on a.good_id = b.goods_id ");
|
||
strSql.AppendLine(" left join t_base_provider_dictionary c ");
|
||
strSql.AppendLine(" on a.provider_id = c.provider_id ");
|
||
strSql.AppendLine("where 1 = 1 ");
|
||
if (!string.IsNullOrEmpty(model.PICKINGID))
|
||
{
|
||
strSql.AppendLine(" and a.PICKINGID = '" + model.PICKINGID + "' ");
|
||
}
|
||
if (!string.IsNullOrEmpty(model.OUT_ID))
|
||
{
|
||
strSql.AppendLine(" and a.OUT_ID = '" + model.OUT_ID + "' ");
|
||
}
|
||
if (!string.IsNullOrEmpty(model.PUTIN_ID))
|
||
{
|
||
strSql.AppendLine(" and a.PUTIN_ID = '" + model.PUTIN_ID + "' ");
|
||
}
|
||
if (!string.IsNullOrEmpty(model.DOCCTNUMBER))
|
||
{
|
||
strSql.AppendLine(" and a.DOCCTNUMBER = '" + model.DOCCTNUMBER + "' ");
|
||
}
|
||
if (!string.IsNullOrEmpty(model.GOOD_ID))
|
||
{
|
||
strSql.AppendLine(" and a.GOOD_ID = '" + model.GOOD_ID + "' ");
|
||
}
|
||
if (!string.IsNullOrEmpty(model.LOC_ID))
|
||
{
|
||
strSql.AppendLine(" and a.LOC_ID = '" + model.LOC_ID + "' ");
|
||
}
|
||
if (!string.IsNullOrEmpty(model.LOC_ID))
|
||
{
|
||
strSql.AppendLine(" and a.LOC_ID = '" + model.LOC_ID + "' ");
|
||
}
|
||
if (!string.IsNullOrEmpty(model.status))
|
||
{
|
||
strSql.AppendLine(" and a.status = '" + model.status + "' ");
|
||
}
|
||
strSql.AppendLine(" order by a.PICKINGID");
|
||
|
||
return SystemDataObject.Instance.GetDataTable(strSql.ToString());
|
||
}
|
||
/// <summary>
|
||
/// 获取明细
|
||
/// </summary>
|
||
public DataTable LtkgetScanData(string localId, string goodsId)
|
||
{
|
||
|
||
string sqlStr = "select * from T_CK_PICKINGWAVEGOODS where LOC_ID='" + localId + "' and good_Id='" + goodsId + "' ";
|
||
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
|
||
|
||
}
|
||
///
|
||
/// <summary>
|
||
/// 获取拣货的明细 outStand='" + outStand + "' and
|
||
/// </summary>
|
||
public DataTable LtkgetPickData(string outStand)
|
||
{
|
||
|
||
string sqlStr = "select t.plcid,t.good_id,t.LOC_ID,t.GOODS_NUM,t.OUT_ID,t.REMARK,t.OUT_ID,t.ctl," +
|
||
"t.PICKING_NUM,t.PACKING_NUM,c.GOODS_NAME,b.ConveyorID as outStand, " +
|
||
" c.BARCODE from T_CK_PICKINGWAVEGOODS t left join T_BASE_GOODS c on t.GOOD_ID =c.GOODS_ID join " +
|
||
"T_ConveyorStatus b on t.plcid =b.PlcID where ConveyorID='"+outStand+"' and t.status = '3'";
|
||
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
|
||
|
||
}
|
||
/// <summary>
|
||
///
|
||
/// </summary>
|
||
/// <param name="goodsId"> 物料编号</param>
|
||
/// <param name="PACKING_NUM">出库数量</param>
|
||
/// <param name="STORAGE_ID">子库编号</param>
|
||
/// <returns></returns>
|
||
public DataTable GetLocDataNo(string goodsId, decimal PACKING_NUM, string STORAGE_ID)
|
||
{
|
||
|
||
string sqlStr = @"select a.LOCATION_ID , a.ctl, (a.SHELVES_NUM-a.OCCUPY_NUM) as SHELVES_NUM,b.HASVOLUME,b.outStand,a.PUTIN_ID,a.goods_sku,a.ware_date,a.barcode,a.STS
|
||
from T_MI_STOCK a
|
||
join T_BASE_STORAGE_AREA_LOCATION b on a.LOCATION_ID = b.LOCATION_ID
|
||
where a.goods_id='" + goodsId + "' and a.OCCUPY_NUM< a.SHELVES_NUM and a.storage_id='"+ STORAGE_ID + "' and a.sts='0' order by a.goods_sku,a.ware_date asc ";
|
||
|
||
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
||
}
|
||
public DataTable GetErpCm(string locid)
|
||
{
|
||
string sqlStr = "select * from pak_wait_out where rtrim(Location)='" + locid + "' ";
|
||
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
||
}
|
||
/// <summary>
|
||
/// 获取 ERP订单数据
|
||
/// </summary>
|
||
/// <returns></returns>
|
||
public DataTable GetDataErpData()
|
||
{
|
||
string sqlStr = "select * from pak_wait_out where status ='0' order by Location ";
|
||
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
||
}
|
||
|
||
public bool getPickDataProductionDate(string goodid, string production_date)
|
||
{
|
||
|
||
string sqlstr = "SELECT * FROM T_MI_STOCK where GOODS_ID='" + goodid + "' and GOODS_SKU<'"+ production_date + "' and sts='0' ";
|
||
DataTable dt = SystemDataObject.Instance.GetDataTable(sqlstr);
|
||
if (dt.Rows.Count > 0)
|
||
{
|
||
return true;
|
||
}
|
||
else
|
||
{
|
||
return false;
|
||
}
|
||
|
||
}
|
||
public bool GetDataErpData(string ORDER_ID)
|
||
{
|
||
bool sts = false;
|
||
string sqlStr = "select * from T_CK_ORDERS where ORDER_ID='" + ORDER_ID + "' ";
|
||
DataTable dt= SystemDataObject.Instance.GetDataTable(sqlStr);
|
||
if (dt != null)
|
||
{
|
||
if (dt.Rows.Count > 0)
|
||
{
|
||
sts= true;
|
||
}
|
||
}
|
||
return sts;
|
||
}
|
||
|
||
|
||
public bool DEL_T_CK_ORDERS(string loc,string goodid)
|
||
{
|
||
string sqlStr = "delete from T_MI_STOCK where LOCATION_ID='"+ loc + "'and GOODS_ID='"+goodid+"' ";
|
||
if (SystemDataObject.Instance.ExecuteSqlCommit(sqlStr) > 0)
|
||
{
|
||
return true;
|
||
}
|
||
else
|
||
{
|
||
return false;
|
||
}
|
||
}
|
||
|
||
public bool Insert_T_CK_ORDERS(string sqlStr)
|
||
{
|
||
SystemDataObject.Instance.ExecuteSql(sqlStr);
|
||
return true;
|
||
}
|
||
|
||
|
||
public bool insert_t_ck_ordersDetall(string sqlStr)
|
||
{
|
||
SystemDataObject.Instance.ExecuteSql(sqlStr);
|
||
return true;
|
||
}
|
||
|
||
public bool update_t_ck_ordersDetall(string ORDER_ID, string goodsID)
|
||
{
|
||
string sqlStr = " UPDATE T_CK_ORDERDETAIL SET sts='0' WHERE ORDER_ID='"+ ORDER_ID + "' AND GOOD_ID='"+ goodsID + "'";
|
||
SystemDataObject.Instance.ExecuteSql(sqlStr);
|
||
return true;
|
||
}
|
||
|
||
public bool delete_t_ckorder(string ORDER_ID)
|
||
{
|
||
string sqlStr = " DELETE FROM T_CK_ORDERDETAIL WHERE ORDER_ID='" + ORDER_ID + "' ; DELETE FROM T_CK_ORDERS WHERE ORDER_ID='" + ORDER_ID + "'";
|
||
SystemDataObject.Instance.ExecuteSql(sqlStr);
|
||
return true;
|
||
}
|
||
|
||
|
||
|
||
|
||
/// <summary>
|
||
/// 更新wms数据状态
|
||
/// </summary>
|
||
/// <param name="Sheet_no"></param>
|
||
/// <param name="Pdc_no"></param>
|
||
/// <param name="PalletID"></param>
|
||
public void SerLocWaitInSts(string ctl, string goodsid, string barcode,string GOODS_NUM_SJ,string out_id,string status,string GoodsMeaSure,string PACKING_NUM)
|
||
{
|
||
string sqlStr = " Update T_CK_PICKINGWAVEGOODS " +
|
||
" set Status = '"+ status + "',GOODS_NUM_SJ="+ GOODS_NUM_SJ + ", barcode='"+ barcode + "', ctl='" + ctl + "' ,PACKING_NUM='"+ PACKING_NUM + "',GOODS_MEASURE_ID='" + GoodsMeaSure +
|
||
" where GOOD_ID='" + goodsid + "' and OUT_ID='"+out_id+"'";
|
||
|
||
SystemDataObject.Instance.ExecuteSqlCommit(sqlStr);
|
||
}
|
||
/// <summary>
|
||
/// 获取拣货的明细
|
||
/// </summary>
|
||
public void UpdateErpData(string CK_ID,string goodsid)
|
||
{
|
||
|
||
string sqlStr = "update T_CK_ORDERDETAIL set sts='3',endtime=getdate() where ORDER_ID='" + CK_ID + "' and GOOD_ID='"+ goodsid + "'";
|
||
SystemDataObject.Instance.ExecuteSql(sqlStr);
|
||
|
||
}
|
||
/// <summary>
|
||
/// 获取拣货的明细
|
||
/// </summary>
|
||
public DataTable GetPickDataBarcode(string barcode)
|
||
{
|
||
|
||
string sqlStr = "select * from T_CK_PICKINGWAVEGOODS where BARCODE='" + barcode + "' ";
|
||
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
|
||
|
||
}
|
||
/// <summary>
|
||
/// 获取拣货单明细是否已经删除
|
||
/// </summary>
|
||
public DataTable GetPickGoodsData(string ctl, string STORAGE_ID)
|
||
{
|
||
|
||
string sqlStr = "select plcid,status from T_CK_PICKINGWAVEGOODS where ctl='" + ctl + "' and STORAGE_ID='"+ STORAGE_ID + "'";
|
||
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
|
||
|
||
}
|
||
|
||
public DataTable GetGoodsArea (string goodsId )
|
||
{
|
||
|
||
string sqlStr = "select a.shipping_length,a.shipping_width,a.pcs_number from cux_wms_system_items_intf_zh a where a.segment1='"+ goodsId + "'";
|
||
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
|
||
|
||
}
|
||
|
||
|
||
|
||
|
||
|
||
public void DeleteErpData(string pickingid)
|
||
{
|
||
string sqlStr = "delete T_CK_PICKINGWAVEGOODS where BARCODE='" + pickingid + "' ";
|
||
SystemDataObject.Instance.ExecuteSql(sqlStr.ToString());
|
||
}
|
||
/// <summary>
|
||
/// 获取拣货单明细是否已经删除
|
||
/// </summary>
|
||
public DataTable HandGoodsSts(string locid)
|
||
{
|
||
|
||
string sqlStr = "select STATUS from T_CK_PICKINGWAVEGOODS where LOC_ID='" + locid + "' ";
|
||
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
|
||
|
||
}
|
||
public void DelHandGoods(string locid)
|
||
{
|
||
string sqlStr = "delete T_CK_PICKINGWAVEGOODS where LOC_ID='"+ locid + "';update T_BASE_STORAGE_AREA_LOCATION set STATUS='1',HASVOLUME='1' where LOCATION_ID='" + locid + "'";
|
||
SystemDataObject.Instance.ExecuteSqlCommit(sqlStr);
|
||
}
|
||
public void InsertHandGoods(PickingWaveGoodsModel model)
|
||
{
|
||
string sqlStr = "insert into T_CK_PICKINGWAVEGOODS(PICKINGID,GOOD_ID,OUT_ID,LOC_ID,plcId,typests,SCALE_UNIT, PICKING_NUM,GOODS_NUM_SJ)" +
|
||
"values('hd001','','','" + model.LOC_ID + "','" + model.plcId + "','1','"+ model .SCALE_UNIT+ "','1','1') ";// ;update T_BASE_STORAGE_AREA_LOCATION set STATUS='0',HASVOLUME='0' where LOCATION_ID='" + model.LOC_ID + "'
|
||
SystemDataObject.Instance.ExecuteSql(sqlStr);
|
||
}
|
||
/// <summary>
|
||
/// 插入捡货明细信息
|
||
/// </summary>
|
||
/// <param name="PickingWaveGoodsModel">捡货明细Model</param>
|
||
/// <returns>捡货明细编号</returns>
|
||
public void InsertPickingWaveGoods(PickingWaveGoodsModel model)
|
||
{
|
||
//string orderstr = "CK" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
|
||
//PickingWaveGoodsModel or_Model = new PickingWaveGoodsModel();
|
||
//or_Model.PICKINGID = tb.Rows[0]["Docctnumber"].ToString(); //销售订单
|
||
//or_Model.PUTIN_ID = orderstr;
|
||
//or_Model.OUT_ID = orderstr;
|
||
//or_Model.GOOD_ID = tb.Rows[0]["GOODS_ID"].ToString();
|
||
//or_Model.LOC_ID = tb.Rows[0]["LOCATION_ID"].ToString();
|
||
//or_Model.WARE_DATE = Convert.ToDateTime(DateTime.Now.ToString());
|
||
//or_Model.PICKING_NUM = Convert.ToDecimal(tb.Rows[0]["SHELVES_NUM"].ToString());//SHELVES_NUM
|
||
//or_Model.BARCODE = tb.Rows[0]["BARCODE"].ToString();
|
||
//or_Model.CTL = tb.Rows[0]["CTL"].ToString();
|
||
//or_Model.status = "0";
|
||
////or_Model.outStand = this.comboBox1.Text.Trim();
|
||
//or_Model.typests = "1";
|
||
//or_Model.CUSTOMER_ID = tb.Rows[0]["CUSTOMER_ID"].ToString();
|
||
//or_Model.GOODS_NUM_SJ = Convert.ToDecimal(tb.Rows[0]["SHELVES_NUM"].ToString());
|
||
//or_Model.STORAGE_AREA_ID = tb.Rows[0]["Storage_Id"].ToString();//子库编码
|
||
|
||
|
||
StringBuilder strSql = new StringBuilder();
|
||
strSql.AppendLine("insert into T_CK_PICKINGWAVEGOODS(");
|
||
strSql.AppendLine("PICKINGID,OUT_ID,PUTIN_ID,DOCCTNUMBER,GOOD_ID,LOC_ID,");
|
||
strSql.AppendLine(" GOODS_NUM,SPECIFICATION,PROVIDER_ID,UNIT,REMARK,");
|
||
strSql.AppendLine(" SCALE_UNIT,MISTOCK_NUM,PICKING_NUM,PRICE,STORAGE_AREA_ID,STORAGE_ID,CTL," +
|
||
"GOODS_NUM_SJ,ProDucLotId,BARCODE,plcid,GOODS_NAME,outStand,typests,PACKING_NUM,status,ware_date,CUSTOMER_ID,PORT,CUSTOMERMEMBERID)");
|
||
strSql.AppendLine(" values (");
|
||
strSql.AppendLine("'" + model.PICKINGID + "','" + model.OUT_ID + "','" + model.PUTIN_ID + "','" + model.DOCCTNUMBER + "','" + model.GOOD_ID + "',");
|
||
strSql.AppendLine("'"+model.LOC_ID+"','"+model.GOODS_NUM+"','"+model.SPECIFICATION+"','"+model.PROVIDER_ID+"','"+model.UNIT+"','"+model.REMARK+"',");
|
||
strSql.AppendLine(" '"+model.SCALE_UNIT+"','"+model.MISTOCK_NUM+"','"+model.PICKING_NUM+"','"+model.PRICE+"','"+model.STORAGE_AREA_ID + "','"+model.STORAGE_ID+"',");
|
||
strSql.AppendLine(" '" + model.CTL + "','"+model.GOODS_NUM_SJ+"','"+model.ProDucLotId+"','"+model.BARCODE+"','"+model.plcId+"','"+model.GOODS_NAME+"'," +
|
||
"'"+model.outStand+"','"+model.typests+"','"+ model .PACKING_NUM+ "','"+model.status+ "',to_date('" + model.WARE_DATE+ "','yyyy/MM/dd hh24:mi:ss'),'"+ model.CUSTOMER_ID + "','"+ model.PORT+ "','"+ model.CUSTOMERMEMBERID + "')");
|
||
|
||
SystemDataObject.Instance.ExecuteSql(strSql.ToString());
|
||
|
||
}
|
||
public void InsertTMISTOCK(string loc_id)
|
||
{
|
||
|
||
|
||
string sqlString = "\n" +
|
||
"insert into t_mi_stock\n" +
|
||
" (storage_id,\n" +
|
||
" area_id,\n" +
|
||
" location_id,\n" +
|
||
" goods_id,\n" +
|
||
" shelves_num,\n" +
|
||
" production_date,\n" +
|
||
" ware_date,\n" +
|
||
" docctnumber,\n" +
|
||
" ctl,\n" +
|
||
" barcode,\n" +
|
||
" sts)\n" +
|
||
"values\n" +
|
||
" ('JXJW',\n" +
|
||
" '3001',\n" +
|
||
" '"+loc_id+"',\n" +
|
||
" '111111',\n" +
|
||
" '1',\n" +
|
||
" sysdate,\n" +
|
||
" sysdate,\n" +
|
||
" '111111',\n" +
|
||
" '111111',\n" +
|
||
" '111111',\n" +
|
||
" '0')";
|
||
|
||
|
||
SystemDataObject.Instance.ExecuteSql(sqlString);
|
||
|
||
}
|
||
public void InsertCK(string pickingid, string oldloc, string newloc,string plcid)
|
||
{
|
||
string sqlStr = "insert into T_CK_PICKINGWAVEGOODS (good_id,pickingid,loc_id,typests，status,outstand,plcid,ware_date,storage_id) values ('000','" + pickingid + "','" + oldloc + "','移库出库','1','" + newloc + "','" + plcid + "',to_date('" + DateTime.Now + "','YYYY-MM-DD HH24:MI:SS'),'JXJW')";
|
||
|
||
SystemDataObject.Instance.ExecuteSql(sqlStr);
|
||
}
|
||
/// <summary>
|
||
/// 删除一个捡货单的捡货商品明细
|
||
/// </summary>
|
||
/// <param name="pickingID">捡货单ID</param>
|
||
public void UpdatePickingWaveSlGoods(string localtion,string goodsId,string number ,string puid)
|
||
{
|
||
string sqlStr = "update T_CK_PICKINGWAVEGOODS set GOODS_NUM_SJ='" + number + "' where GOOD_ID='" + goodsId + "' and LOC_ID='" + localtion + "' and PUTIN_ID='" + puid + "'";
|
||
|
||
SystemDataObject.Instance.ExecuteSqlCommit(sqlStr.ToString());
|
||
}
|
||
///// <summary>
|
||
///// 删除一个捡货单的捡货商品明细
|
||
///// </summary>
|
||
///// <param name="pickingID">捡货单ID</param>
|
||
//public void DeletePickingWaveGoods(string pickingID)
|
||
//{
|
||
// StringBuilder strSql = new StringBuilder();
|
||
// strSql.AppendLine("delete from T_CK_PICKINGWAVEGOODS ");
|
||
// strSql.AppendLine(" where PICKINGID=:PICKINGID");
|
||
// OracleParameter[] parameters = {
|
||
// new OracleParameter(":PICKINGID",OracleDbType.Varchar2) };
|
||
// parameters[0].Value = pickingID;
|
||
|
||
// SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
|
||
//}
|
||
/// <summary>
|
||
/// 删除一个捡货单的捡货商品明细
|
||
/// </summary>
|
||
/// <param name="BARCODE"></param>
|
||
public void DeletePickingWaveGoods(string LOC_ID,string out_id)
|
||
{
|
||
StringBuilder strSql = new StringBuilder();
|
||
strSql.AppendLine("delete from T_CK_PICKINGWAVEGOODS ");
|
||
strSql.AppendLine(" where LOC_ID='"+LOC_ID+ "' ");//and OUT_ID='"+ out_id + "'
|
||
|
||
SystemDataObject.Instance.ExecuteSql(strSql.ToString());
|
||
}
|
||
public void DeletePickingWaveGoodsOut(string out_id)
|
||
{
|
||
StringBuilder strSql = new StringBuilder();
|
||
strSql.AppendLine("delete from T_CK_PICKINGWAVEGOODS ");
|
||
strSql.AppendLine(" where out_id=:PICKINGID ");
|
||
OracleParameter[] parameters = {
|
||
new OracleParameter(":PICKINGID",OracleDbType.Varchar2)
|
||
};
|
||
parameters[0].Value = out_id;
|
||
|
||
SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
|
||
}
|
||
/// <summary>
|
||
/// 出库时降库存锁住
|
||
/// </summary>
|
||
/// <param name="barcode"></param>
|
||
public void UpdateMisStsLock(string barcode)
|
||
{
|
||
string sqlstr = " update T_MI_STOCK set ABLE_NUM =1 where barcode='" + barcode + "'";
|
||
|
||
SystemDataObject.Instance.ExecuteSql(sqlstr);
|
||
}
|
||
/// <summary>
|
||
/// 删除一个捡货单的捡货商品明细
|
||
/// </summary>
|
||
/// <param name="pickingID">捡货单ID</param>
|
||
public void DeleteProDucLotIdGoods(string ProDucLotId, string BARCODE, string localId)
|
||
{
|
||
StringBuilder strSql = new StringBuilder();
|
||
strSql.AppendLine("delete from T_CK_PICKINGWAVEGOODS ");
|
||
strSql.AppendLine(" where ProDucLotId=:ProDucLotId and BARCODE='" + BARCODE + "' and localId='" + localId + "' ");
|
||
OracleParameter[] parameters = {
|
||
new OracleParameter(":ProDucLotId",OracleDbType.Varchar2) };
|
||
parameters[0].Value = ProDucLotId;
|
||
|
||
SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
|
||
}
|
||
|
||
|
||
/// <summary>
|
||
/// 获取该库位的是否已经满
|
||
/// </summary>
|
||
/// <param name="locId"></param>
|
||
/// <returns></returns>
|
||
public DataTable getLocIdVolume(string locId)
|
||
{
|
||
string sqlStr = "select HASVOLUME,VOLUME from T_BASE_STORAGE_AREA_LOCATION where LOCATION_ID='" + locId + "' ";
|
||
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
|
||
}
|
||
/// <summary>
|
||
/// 向删除批次表（巨化使用）
|
||
/// </summary>
|
||
/// <param name="pickingID">捡货单ID</param>
|
||
public void InsertProDucLotIdGoodsBack(string localId,string out_id)
|
||
{
|
||
|
||
string strsql =@"insert into T_CK_PICKINGWAVEGOODS_BACK
|
||
( PICKINGID,
|
||
OUT_ID,
|
||
PUTIN_ID,
|
||
DOCCTNUMBER,
|
||
GOOD_ID,
|
||
LOC_ID,
|
||
GOODS_NUM,
|
||
SPECIFICATION,
|
||
PRODUCTION_DATE,
|
||
WARE_DATE,
|
||
PROVIDER_ID,
|
||
UNIT,
|
||
REMARK,
|
||
SCALE_UNIT,
|
||
MISTOCK_NUM,
|
||
PICKING_NUM,
|
||
PRICE,
|
||
STORAGE_AREA_ID,
|
||
STORAGE_ID,
|
||
CTL,
|
||
GOODS_NUM_SJ,ProDucLotId,BARCODE,GOODS_NAME,ToWhseloc,plcId,outStand,typests)
|
||
(
|
||
select PICKINGID,
|
||
OUT_ID,
|
||
PUTIN_ID,
|
||
DOCCTNUMBER,
|
||
GOOD_ID,
|
||
LOC_ID,
|
||
GOODS_NUM,
|
||
SPECIFICATION,
|
||
PRODUCTION_DATE,
|
||
WARE_DATE,
|
||
PROVIDER_ID,
|
||
UNIT,
|
||
REMARK,
|
||
SCALE_UNIT,
|
||
MISTOCK_NUM,
|
||
PICKING_NUM,
|
||
PRICE,
|
||
STORAGE_AREA_ID,
|
||
STORAGE_ID,
|
||
CTL,
|
||
GOODS_NUM_SJ,ProDucLotId,BARCODE,GOODS_NAME,ToWhseloc,plcId,outStand,typests
|
||
from T_CK_PICKINGWAVEGOODS
|
||
where LOC_ID='" + localId + "' )";//and OUT_ID='" + out_id + "'
|
||
|
||
SystemDataObject.Instance.ExecuteSql(strsql);
|
||
|
||
}
|
||
/// <summary>
|
||
/// 向备份表中添加一个捡货单的捡货商品明细
|
||
/// </summary>
|
||
/// <param name="pickingID">捡货单ID</param>
|
||
public void InsertPickingWaveGoodsBack(string pickingID, string goodsId, string localId)
|
||
{
|
||
|
||
string strsql =@"insert into T_CK_PICKINGWAVEGOODS_BACK
|
||
( PICKINGID,
|
||
OUT_ID,
|
||
PUTIN_ID,
|
||
DOCCTNUMBER,
|
||
GOOD_ID,
|
||
LOC_ID,
|
||
GOODS_NUM,
|
||
SPECIFICATION,
|
||
PRODUCTION_DATE,
|
||
WARE_DATE,
|
||
PROVIDER_ID,
|
||
UNIT,
|
||
REMARK,
|
||
SCALE_UNIT,
|
||
MISTOCK_NUM,
|
||
PICKING_NUM,
|
||
PRICE,
|
||
STORAGE_AREA_ID,
|
||
STORAGE_ID,
|
||
CTL,
|
||
GOODS_NUM_SJ)
|
||
(
|
||
select PICKINGID,
|
||
OUT_ID,
|
||
PUTIN_ID,
|
||
DOCCTNUMBER,
|
||
GOOD_ID,
|
||
LOC_ID,
|
||
GOODS_NUM,
|
||
SPECIFICATION,
|
||
PRODUCTION_DATE,
|
||
WARE_DATE,
|
||
PROVIDER_ID,
|
||
UNIT,
|
||
REMARK,
|
||
SCALE_UNIT,
|
||
MISTOCK_NUM,
|
||
PICKING_NUM,
|
||
PRICE,
|
||
STORAGE_AREA_ID,
|
||
STORAGE_ID,
|
||
CTL,
|
||
GOODS_NUM_SJ
|
||
from T_CK_PICKINGWAVEGOODS
|
||
where PICKINGID = '" + pickingID + "'and GOOD_ID = '" + goodsId + "' and LOC_ID = '" + localId + "')";
|
||
|
||
|
||
SystemDataObject.Instance.ExecuteSql(strsql);
|
||
|
||
}
|
||
}
|
||
}
|