835 lines
37 KiB
C#
835 lines
37 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using WMS.IData;using Oracle.ManagedDataAccess.Client;
|
|
using System.Data;
|
|
|
|
using WMS.DBUtility;
|
|
using WMS.Model.Stock;
|
|
using WMS.IData.IStock;
|
|
using System.Security.Cryptography;
|
|
|
|
namespace WMS.SqlServerData.StockData
|
|
{
|
|
public partial class MIStockData : IMIStock
|
|
{
|
|
public void UpdateMisLocSts(string ctl, string location, string sts)
|
|
{
|
|
string sqlStr = "update T_MI_STOCK set LOCATION_ID='" + location + "',STS='" + sts + "' where LOCATION_ID='" + ctl + "'";
|
|
SystemDataObject.Instance.ExecuteSqlCommit(sqlStr);
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
public void DeleteMIStock(string ctl, string locid, string goodid)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.AppendLine("delete from T_MI_STOCK ");
|
|
strSql.AppendLine(" where CTL = '" + ctl + "' and LOCATION_ID='" + locid + "'and GOODS_ID='" + goodid + "'");
|
|
SystemDataObject.Instance.ExecuteSqlCommit(strSql.ToString());
|
|
|
|
|
|
}
|
|
|
|
public bool UpdateMis(string shelfnum, string locId, string STORAGE_ID, string goodid)
|
|
{
|
|
string sqlStr = " update T_MI_STOCK set SHELVES_NUM='" + shelfnum + "'where LOCATION_ID='" + locId + "' and STORAGE_ID='" + STORAGE_ID + "'and GOODS_ID='" + goodid + "'";
|
|
if (SystemDataObject.Instance.ExecuteSqlCommit(sqlStr) > 0)
|
|
{ return true; }
|
|
else
|
|
{
|
|
return false;
|
|
}
|
|
|
|
}
|
|
|
|
|
|
public bool UpdateMisSTS (string STS, string locId, string STORAGE_ID, string goodid,string BARCODE, decimal occupy_num)
|
|
{
|
|
string sqlStr = " update T_MI_STOCK set STS='" + STS + "',occupy_num="+ occupy_num + " where LOCATION_ID='" + locId + "' and STORAGE_ID='" + STORAGE_ID + "'and GOODS_ID='" + goodid + "',and BARCODE='"+ BARCODE + "'";
|
|
if (SystemDataObject.Instance.ExecuteSqlCommit(sqlStr) > 0)
|
|
{ return true; }
|
|
else
|
|
{
|
|
return false;
|
|
}
|
|
|
|
}
|
|
|
|
public bool UpdateMis(string locId, string STORAGE_ID)
|
|
{
|
|
string sqlStr = " update T_MI_STOCK set sts='0'where LOCATION_ID='" + locId + "' and STORAGE_ID='" + STORAGE_ID + "'";
|
|
if (SystemDataObject.Instance.ExecuteSqlCommit(sqlStr) > 0)
|
|
{ return true; }
|
|
else
|
|
{
|
|
return false;
|
|
}
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// 移动库位
|
|
/// </summary>
|
|
/// <param name="locId"></param>
|
|
/// <param name="ctl"></param>
|
|
public void UpdateMisLocData(string locId, string oldLoc,string STORAGE_ID,string ctl)
|
|
{
|
|
string sqlStr = " update T_MI_STOCK set sts='0', LOCATION_ID='"+ locId + "' where LOCATION_ID='" + oldLoc + "' ";
|
|
if(ctl.Trim().Length>0)
|
|
{
|
|
sqlStr = string.Format(sqlStr, ",ctl='" + ctl + "'");
|
|
}
|
|
else
|
|
{
|
|
sqlStr = string.Format(sqlStr, "");
|
|
}
|
|
|
|
SystemDataObject.Instance.ExecuteSql(sqlStr);
|
|
|
|
}
|
|
/// <summary>
|
|
/// 冻结/解冻库位
|
|
/// </summary>
|
|
/// <param name="locId"></param>
|
|
public void UpdateMisLoc(string locId, string sts)
|
|
{
|
|
|
|
string sqlStr = " update T_MI_STOCK set sts='" + sts + "' where LOCATION_ID='" + locId + "'";
|
|
|
|
|
|
SystemDataObject.Instance.ExecuteSqlCommit(sqlStr);
|
|
}
|
|
|
|
|
|
public void UpdateMisLocBarCode (string CTL,string LOC, string sts)
|
|
{
|
|
|
|
string sqlStr = " update T_MI_STOCK set sts='" + sts + "' where CTL='" + CTL + "' and LOCATION_ID='"+ LOC + "'";
|
|
|
|
SystemDataObject.Instance.ExecuteSql(sqlStr.ToString());
|
|
}
|
|
|
|
|
|
|
|
|
|
public void UpdateYKLoc( string location_id)
|
|
{
|
|
|
|
string sqlStr = " update T_BASE_STORAGE_AREA_LOCATION set status='" + 1 + "' where LOCATION_ID='" + location_id + "'";
|
|
|
|
|
|
SystemDataObject.Instance.ExecuteSqlCommit(sqlStr);
|
|
}
|
|
/// <summary>
|
|
/// 查询空托盘
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable GetMisDataNullOut(string size)
|
|
{
|
|
string sqlStr =@" select top 1 t.location_id,a.outStand1 from T_MI_STOCK t
|
|
left join t_base_storage_area_location a on t.LOCATION_ID=a.LOCATION_ID
|
|
|
|
where goods_id='00000' and HIGH='"+ size + "' and STS='0' order by PRODUCTION_DATE ";
|
|
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
|
|
}
|
|
|
|
|
|
public DataTable GetMisDataempty()
|
|
{
|
|
string sqlStr = @" select * from T_MI_STOCK where GOODS_ID='000000' and STS=0";
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
|
|
}
|
|
public DataTable GetMisDataCKCTL()
|
|
{
|
|
string sqlStr = @" select * from T_MI_STOCK where GOODS_ID='000000' and STS=0 ";
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
|
|
}
|
|
|
|
/// <summary>
|
|
/// 查询当前库存是否已出库
|
|
/// </summary>
|
|
/// <param name="CTL"></param>
|
|
/// <returns></returns>
|
|
public DataSet GET_T_MISTOCK_STATUS(string CTL)
|
|
{
|
|
DataSet dt=new DataSet();
|
|
string sqlStr = @" select * from T_MI_STOCK t where t.CTL='" + CTL + "' and t.sts=1 ";
|
|
return SystemDataObject.Instance.GetDataSet(sqlStr);
|
|
}
|
|
|
|
public DataSet GET_T_MI_STOCK_STATUS_MES()
|
|
{
|
|
DataSet dt = new DataSet();
|
|
string sqlStr = @" select * from T_MI_STOCK where STSEBS= ";
|
|
return SystemDataObject.Instance.GetDataSet(sqlStr);
|
|
}
|
|
|
|
public DataTable GetMisDataCK (string barcode)
|
|
{
|
|
string sqlStr = @" select * from T_MI_STOCK t where t.barcode='"+ barcode + "' and t.sts='0' ";
|
|
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
|
|
}
|
|
|
|
public DataTable GetMisDataCK_empty(string barcode)
|
|
{
|
|
string sqlStr = @" select * from T_MI_STOCK t where t.goods_id='" + barcode + "' and t.sts='0' ";
|
|
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
|
|
}
|
|
|
|
public DataTable GetMisDataCKCTL(string CTL )
|
|
{
|
|
string sqlStr = @" select * from T_MI_STOCK t where t.CTL='" + CTL + "' and t.sts='0' ";
|
|
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
|
|
}
|
|
|
|
|
|
//select * from T_MI_STOCK t where t.barcode='' and t.sts='0'
|
|
|
|
public DataTable GetMistockLocData(string devid)
|
|
{
|
|
string sqlStr = string.Empty;
|
|
if (devid == "")
|
|
{
|
|
sqlStr =@" select g.GOODS_NAME, * from T_MI_STOCK k,T_BASE_GOODS g
|
|
where k.GOODS_ID = g.GOODS_ID order by k.WARE_DATE desc";
|
|
}
|
|
else
|
|
{
|
|
sqlStr = "select m.ProDucLotId,m.BARCODE,m.CTL,m.GOODS_ID,g.GOODS_NAME,t.* from T_MI_STOCK m join T_BASE_STORAGE_AREA_LOCATION t on m.LOCATION_ID =t.LOCATION_ID left join T_BASE_GOODS g on m.BARCODE = g.BARCODE where t.devid='" + devid + "' order by t.SORT";
|
|
|
|
}
|
|
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
|
}
|
|
|
|
|
|
public DataTable GetCtMisTable(string locId)
|
|
{
|
|
string sqlStr =@"select count(*)a from T_MI_STOCK where LOCATION_ID = '" + locId + "' ";
|
|
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
|
|
}
|
|
/// <summary>
|
|
/// 查询根据条件库存
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable GetMisDataOut(string locId,string goodsiD,string BARCODE)
|
|
{
|
|
string sqlStr =@"select * from T_MI_STOCK where LOCATION_ID = '" + locId + "'";
|
|
if(goodsiD.Trim().Length>0)
|
|
{
|
|
sqlStr = sqlStr + " and goods_id='" + goodsiD + "'";
|
|
}
|
|
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
|
|
}
|
|
|
|
|
|
|
|
public DataTable GetCtlCount (string locId)
|
|
{
|
|
string sqlStr = @"select * from T_MI_STOCK where LOCATION_ID = '" + locId + "' and sts='0' ";
|
|
|
|
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
|
|
}
|
|
|
|
|
|
|
|
|
|
/// <summary>
|
|
/// 删除库存表信息
|
|
/// </summary>
|
|
/// <param name="barcode">BarCode</param>
|
|
/// <param name="locId">库位</param>
|
|
/// <param name="productId">生产批号</param>
|
|
public void DeleteMIStock(string locId)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.AppendLine("delete from T_MI_STOCK ");
|
|
strSql.AppendLine(" where LOCATION_ID = '" + locId + "'");
|
|
|
|
|
|
SystemDataObject.Instance.ExecuteSql(strSql.ToString());
|
|
|
|
|
|
}
|
|
|
|
}
|
|
public partial class MIStockData : IMIStock
|
|
{
|
|
|
|
public DataTable GetLoctionGoodLtk(string localId)
|
|
{
|
|
string sqlStr = @" select * from T_MI_STOCK t WHERE T.GOODS_ID='"+ localId + "' order by T.STS desc";
|
|
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
|
|
|
|
}
|
|
public DataTable GetLoctionGoods(string localId)
|
|
{
|
|
|
|
string sqlStr =@"select isnull(min(ctl),'')ctl,
|
|
sum(shelves_num) shelves_num from T_MI_STOCK where LOCATION_ID='" + localId + "' group by goods_id";
|
|
|
|
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
|
|
|
|
}
|
|
public DataTable GetCTlGoods(string ctl)
|
|
{
|
|
|
|
string sqlStr = "select min(LOCATION_ID)localId from T_MI_STOCK where ctl='" + ctl + "'";
|
|
|
|
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取库存表
|
|
/// </summary>
|
|
/// <returns>库存表</returns>
|
|
public DataTable GetMIStockDT(MIStockModel model)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
|
|
strSql.AppendLine(@"select t.STOCK_ID,
|
|
t.PUTIN_ID,
|
|
t.STORAGE_ID,
|
|
a.STORAGE_NAME,
|
|
t.AREA_ID,
|
|
b.STORAGE_AREA_NAME AREA_NAME,
|
|
t.LOCATION_ID,
|
|
t.GOODS_TYPEID,
|
|
t.GOODS_ID,
|
|
g.goods_name GOODS_NAME,
|
|
g.COST_PRICE,
|
|
(g.COST_PRICE*t.SHELVES_NUM) SUM_COST_PRICE,
|
|
tab.VC_DICTIONARY_NAME UNIT_NAME,
|
|
t.SHELVES_NUM,
|
|
t.REMARK,
|
|
t.VERSION,
|
|
t.STORAGE_MODE,
|
|
t.PRODUCTION_DATE,
|
|
t.WARE_DATE,
|
|
t.DOCCTNUMBER,
|
|
t.OCCUPY_NUM,
|
|
t.NOTICE_ID,
|
|
t.ORI_BILL,
|
|
t.SHELVES_NUM-t.OCCUPY_NUM ABLE_NUM,
|
|
t.ctl,
|
|
t.barcode,t.CUSTOMER_ID,t.ProDucLotId,t.sts
|
|
FROM T_MI_STOCK t
|
|
left join T_BASE_GOODS g
|
|
on t.goods_id = g.goods_id
|
|
left join T_BASE_STORAGEINFO a
|
|
on t.STORAGE_ID = a.STORAGE_ID
|
|
left join T_BASE_STORAGE_AREA b
|
|
on t.AREA_ID = b.STORAGE_AREA_ID
|
|
left join T_SYS_DICTIONARY_TAB tab
|
|
on g.UNIT = tab.VC_CHAR_VALUE and tab.VC_DICTIONARY_TYPE='vc_uom'
|
|
");
|
|
strSql.AppendLine(" where 1 = 1 ");
|
|
if (!string.IsNullOrEmpty(model.STOCK_ID))
|
|
{
|
|
strSql.AppendLine(" and t.STOCK_ID = '" + model.STOCK_ID + "' ");
|
|
}
|
|
if (!string.IsNullOrEmpty(model.PUTIN_ID))
|
|
{
|
|
strSql.AppendLine(" and t.PUTIN_ID = '" + model.PUTIN_ID + "' ");
|
|
}
|
|
if (!string.IsNullOrEmpty(model.LOCATION_ID))
|
|
{
|
|
strSql.AppendLine(" and t.LOCATION_ID = '" + model.LOCATION_ID + "' ");
|
|
}
|
|
if (!string.IsNullOrEmpty(model.GOODS_ID))
|
|
{
|
|
strSql.AppendLine(" and t.GOODS_ID = '" + model.GOODS_ID + "'");
|
|
}
|
|
//if (!string.IsNullOrEmpty(model.STORAGE_ID))
|
|
//{
|
|
// strSql.AppendLine(" and t.STORAGE_ID = '" + model.STORAGE_ID + "' ");
|
|
//}
|
|
if (!string.IsNullOrEmpty(model.AREA_ID))
|
|
{
|
|
strSql.AppendLine(" and t.AREA_ID = '" + model.AREA_ID + "' ");
|
|
}
|
|
if (!string.IsNullOrEmpty(model.PROVIDER_ID))
|
|
{
|
|
strSql.AppendLine(" and t.PROVIDER_ID = '" + model.PROVIDER_ID + "' ");
|
|
}
|
|
if (!string.IsNullOrEmpty(model.LOCATION_END) && !string.IsNullOrEmpty(model.LOCATION_STAR))
|
|
{
|
|
strSql.AppendLine(" and t.LOCATION_ID between '" + model.LOCATION_STAR + "' and '" + model.LOCATION_END + "'");
|
|
}
|
|
//if (model.ABLE_NUM==0)
|
|
//{
|
|
// strSql.AppendLine(" and t.ABLE_NUM =0 ");
|
|
//}
|
|
strSql.AppendLine(" order by t.STOCK_ID");
|
|
|
|
return SystemDataObject.Instance.GetDataTable(strSql.ToString());
|
|
}
|
|
|
|
public DataTable GetMistockLocData()
|
|
{
|
|
string sqlStr = "select t.* from T_MI_STOCK m join T_BASE_STORAGE_AREA_LOCATION t on m.LOCATION_ID =t.LOCATION_ID order by t.SORT";
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 此查询 用于库存查看功能 查询
|
|
/// </summary>
|
|
/// <param name="model"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetMiStockForLookUp(MIStockModel model)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append(@" select * from T_VIEW_MISTOCKSEARCH
|
|
where 1 = 1 ");
|
|
|
|
|
|
if (!string.IsNullOrEmpty(model.STORAGE_ID))
|
|
{
|
|
strSql.AppendLine(" and STORAGE_ID = '" + model.STORAGE_ID + "' ");
|
|
}
|
|
if (!string.IsNullOrEmpty(model.AREA_ID))
|
|
{
|
|
strSql.AppendLine(" and AREA_ID = '" + model.AREA_ID + "' ");
|
|
}
|
|
if (!string.IsNullOrEmpty(model.LOCATION_ID))
|
|
{
|
|
strSql.AppendLine(" and LOCATION_ID = '" + model.LOCATION_ID + "' ");
|
|
}
|
|
if (!string.IsNullOrEmpty(model.GOODS_ID))
|
|
{
|
|
strSql.AppendLine(" and GOODS_ID = '" + model.GOODS_ID + "'");
|
|
}
|
|
if (!string.IsNullOrEmpty(model.GOODS_NAME))
|
|
{
|
|
strSql.AppendLine(" and GOODS_NAME like'%" + model.GOODS_NAME + "%'");
|
|
}
|
|
if (!string.IsNullOrEmpty(model.BARCODE))
|
|
{
|
|
strSql.AppendLine(" and BARCODE ='" + model.BARCODE + "'");
|
|
}
|
|
strSql.AppendLine(" order by PRODUCTION_DATE ");
|
|
|
|
return SystemDataObject.Instance.GetDataTable(strSql.ToString());
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
/// <summary>
|
|
/// 插入库存表信息
|
|
/// </summary>
|
|
/// <param name="MIStockModel">库存表Model</param>
|
|
public string InsertMIStock(MIStockModel model)
|
|
{
|
|
//string str = "select SEQ_MI_STOCK.nextval from dual ";
|
|
//DataSet ds = SystemDataObject.Instance.GetDataSet( str);
|
|
//model.STOCK_ID = "KC" + ds.Tables[0].Rows[0][0].ToString();
|
|
|
|
//if (string.IsNullOrEmpty(model.PUTIN_ID)) {
|
|
// string str2 = "select to_char(getdate(),'yyyyMMdd')||seq_rk_upgoods.nextval from dual ";
|
|
// DataSet ds2 = SystemDataObject.Instance.GetDataSet( str2);
|
|
// model.PUTIN_ID = "SJ" + ds2.Tables[0].Rows[0][0].ToString();
|
|
//}
|
|
|
|
|
|
// strSql.AppendLine(@":,:,
|
|
//:,:,:,:,:,:,:,:,
|
|
//:,:,:,:
|
|
// ,:,:,:,:,:,:,:,:,:,:");
|
|
|
|
|
|
string sqlStr = "insert into T_MI_STOCK(STOCK_ID,PUTIN_ID,STORAGE_ID,AREA_ID,LOCATION_ID,GOODS_TYPEID,"
|
|
+ " GOODS_ID,SHELVES_NUM,REMARK,VERSION,STORAGE_MODE,PRODUCTION_DATE,DOCCTNUMBER,PROVIDER_ID,"
|
|
+ " OCCUPY_NUM,NOTICE_ID,ORI_BILL,CTL,BARCODE,ProDucLotId,Whseloc,HIGH,wgh,Customer_Id,GOODS_NAME,WARE_DATE,GOODS_SKU,PACKING_NUM,GOODSVOLUME) values('" + model.STOCK_ID + "','" + model.PUTIN_ID + "',"
|
|
+ "'" + model.STORAGE_ID + "','" + model.AREA_ID + "','" + model.LOCATION_ID + "','" + model.GOODS_TYPEID + "',"
|
|
+ "'" + model.GOODS_ID + "','" + model.SHELVES_NUM + "','" + model.REMARK + "',"
|
|
+ "'" + model.VERSION + "','" + model.STORAGE_MODE + "',to_date('" + model.PRODUCTION_DATE + "','YYYY-MM-DD HH24:MI:SS'),'" + model.DOCCTNUMBER + "'," +
|
|
"'" + model.PROVIDER_ID + "','" + model.OCCUPY_NUM + "','" + model.NOTICE_ID + "',"
|
|
+ " '" + model.ORI_BILL + "','" + model.CTL + "','" + model.BARCODE + "'," +
|
|
"'" + model.ProDucLotId + "','" + model.Whseloc + "','" + model.High + "','" + model.WGH + "'," +
|
|
"'" + model.Customer_Id + "','" + model.GOODS_NAME + "',to_date('" + model.WARE_DATE + "','YYYY-MM-DD HH24:MI:SS'),'"+ model.GOODS_SKU + "',"+model.PACKING_NUM + ","+ model.GOODSVOLUME + ")";
|
|
|
|
//string sqlStr = "insert into T_MI_STOCK(STOCK_ID,PUTIN_ID,STORAGE_ID,AREA_ID,LOCATION_ID,GOODS_TYPEID,"
|
|
// +" GOODS_ID,SHELVES_NUM,REMARK,VERSION,STORAGE_MODE,PRODUCTION_DATE,DOCCTNUMBER,PROVIDER_ID,"
|
|
// +" OCCUPY_NUM,NOTICE_ID,ORI_BILL,CTL,BARCODE,ProDucLotId,Whseloc,GOODS_NAME,HIGH,wgh) values('" + model.STOCK_ID + "','" + model.PUTIN_ID + "',"
|
|
// + "'" + model.STORAGE_ID + "','" + model.AREA_ID + "','" + model.LOCATION_ID + "','" + model.GOODS_TYPEID + "',"
|
|
// + "'" + model.GOODS_ID + "','" + model.SHELVES_NUM + "','" + model.REMARK + "',"
|
|
// + "'" + model.VERSION + "','" + model.STORAGE_MODE + "',sysdate," +
|
|
// "'" + model.DOCCTNUMBER + "','" + model.PROVIDER_ID + "','" + model.OCCUPY_NUM + "','" + model.NOTICE_ID + "',"
|
|
// + " '"+ model .ORI_BILL + "','"+ model .CTL + "','"+ model .BARCODE + "'," +
|
|
// "'"+ model .ProDucLotId + "','" + model.Whseloc + "','" + model.GOODS_NAME + "','" + model.High + "','" + model.WGH + "')";
|
|
// OracleParameter[] parameters = {
|
|
//new OracleParameter(":STOCK_ID",OracleDbType.Varchar2),
|
|
//new OracleParameter(":PUTIN_ID",OracleDbType.Varchar2),
|
|
//new OracleParameter(":STORAGE_ID",OracleDbType.Varchar2),
|
|
//new OracleParameter(":AREA_ID",OracleDbType.Varchar2),
|
|
//new OracleParameter(":LOCATION_ID",OracleDbType.Varchar2),
|
|
//new OracleParameter(":GOODS_TYPEID",OracleDbType.Varchar2),
|
|
//new OracleParameter(":GOODS_ID",OracleDbType.Varchar2),
|
|
//new OracleParameter(":SHELVES_NUM", OracleDbType.Decimal),
|
|
//new OracleParameter(":REMARK",OracleDbType.Varchar2),
|
|
//new OracleParameter(":VERSION",OracleDbType.Varchar2),
|
|
//new OracleParameter(":STORAGE_MODE", OracleDbType.Char),
|
|
//new OracleParameter(":PRODUCTION_DATE", OracleDbType.DateTime),
|
|
// //new OracleParameter(":WARE_DATE", OracleDbType.Date),
|
|
// new OracleParameter(":DOCCTNUMBER",OracleDbType.Varchar2),
|
|
//new OracleParameter(":PROVIDER_ID",OracleDbType.Varchar2),
|
|
|
|
// new OracleParameter(":OCCUPY_NUM", OracleDbType.Decimal),
|
|
|
|
// new OracleParameter(":NOTICE_ID",OracleDbType.Varchar2),
|
|
|
|
// new OracleParameter(":ORI_BILL",OracleDbType.Varchar2),
|
|
|
|
// new OracleParameter(":CTL",OracleDbType.Varchar2),
|
|
|
|
// new OracleParameter(":BARCODE",OracleDbType.Varchar2),
|
|
|
|
// new OracleParameter(":ProDucLotId",OracleDbType.Varchar2),
|
|
|
|
// new OracleParameter(":Whseloc",OracleDbType.Varchar2),
|
|
|
|
// new OracleParameter(":GOODS_NAME",OracleDbType.Varchar2),
|
|
|
|
// new OracleParameter(":HIGH",OracleDbType.Varchar2),
|
|
|
|
// new OracleParameter(":wgh",OracleDbType.Varchar2)
|
|
// };
|
|
// parameters[0].Value = model.STOCK_ID;
|
|
// parameters[1].Value = model.PUTIN_ID;
|
|
// parameters[2].Value = model.STORAGE_ID;
|
|
// parameters[3].Value = model.AREA_ID;
|
|
// parameters[4].Value = model.LOCATION_ID;
|
|
// parameters[5].Value = model.GOODS_TYPEID;
|
|
// parameters[6].Value = model.GOODS_ID;
|
|
// parameters[7].Value = model.SHELVES_NUM;
|
|
// parameters[8].Value = model.REMARK;
|
|
// parameters[9].Value = model.VERSION;
|
|
// parameters[10].Value = model.STORAGE_MODE;
|
|
// parameters[11].Value = model.PRODUCTION_DATE;
|
|
// // parameters[12].Value = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
|
|
// parameters[12].Value = model.DOCCTNUMBER;
|
|
// parameters[13].Value = model.PROVIDER_ID;
|
|
// parameters[14].Value = model.OCCUPY_NUM;
|
|
// parameters[15].Value = model.NOTICE_ID;
|
|
// parameters[16].Value = model.ORI_BILL;
|
|
// parameters[17].Value = model.CTL;
|
|
// parameters[18].Value = model.BARCODE;
|
|
// parameters[19].Value = model.ProDucLotId;
|
|
// parameters[20].Value = model.Whseloc;
|
|
// parameters[21].Value = model.GOODS_NAME;
|
|
// parameters[22].Value = model.High;
|
|
// parameters[23].Value = model.WGH;
|
|
SystemDataObject.Instance.ExecuteSql(sqlStr.ToString());
|
|
//SystemDataObject.Instance.ExecuteSqlCommit(sqlStr.ToString());
|
|
return model.STOCK_ID;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 修改库存表信息
|
|
/// </summary>
|
|
/// <param name="MIStockModel">库存表Model</param>
|
|
public void UpdateMIStock(MIStockModel model)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
|
|
strSql.AppendLine("update T_MI_STOCK set ");
|
|
strSql.AppendLine("PUTIN_ID=:PUTIN_ID,");
|
|
strSql.AppendLine("STORAGE_ID=:STORAGE_ID,");
|
|
strSql.AppendLine("AREA_ID=:AREA_ID,");
|
|
strSql.AppendLine("LOCATION_ID=:LOCATION_ID,");
|
|
strSql.AppendLine("GOODS_TYPEID=:GOODS_TYPEID,");
|
|
strSql.AppendLine("GOODS_ID=:GOODS_ID,");
|
|
strSql.AppendLine("SHELVES_NUM=:SHELVES_NUM,");
|
|
strSql.AppendLine("REMARK=:REMARK,");
|
|
strSql.AppendLine("VERSION=:VERSION,");
|
|
strSql.AppendLine("STORAGE_MODE=:STORAGE_MODE,");
|
|
strSql.AppendLine("PRODUCTION_DATE=:PRODUCTION_DATE,");
|
|
strSql.AppendLine("WARE_DATE=:WARE_DATE,");
|
|
strSql.AppendLine("DOCCTNUMBER=:DOCCTNUMBER,");
|
|
//strSql.AppendLine("STOCK_ID=:STOCK_ID,");
|
|
strSql.AppendLine("OCCUPY_NUM=:OCCUPY_NUM,");
|
|
strSql.AppendLine("NOTICE_ID=:NOTICE_ID,");
|
|
strSql.AppendLine("ORI_BILL=:ORI_BILL");
|
|
strSql.AppendLine(" where PUTIN_ID=:PUTIN_ID and GOODS_ID=:GOODS_ID and LOCATION_ID=:LOCATION_ID");
|
|
OracleParameter[] parameters = {
|
|
new OracleParameter(":PUTIN_ID",OracleDbType.Varchar2),
|
|
new OracleParameter(":STORAGE_ID",OracleDbType.Varchar2),
|
|
new OracleParameter(":AREA_ID",OracleDbType.Varchar2),
|
|
new OracleParameter(":LOCATION_ID",OracleDbType.Varchar2),
|
|
new OracleParameter(":GOODS_TYPEID",OracleDbType.Varchar2),
|
|
new OracleParameter(":GOODS_ID",OracleDbType.Varchar2),
|
|
new OracleParameter(":SHELVES_NUM", OracleDbType.Decimal),
|
|
new OracleParameter(":REMARK",OracleDbType.Varchar2),
|
|
new OracleParameter(":VERSION",OracleDbType.Varchar2),
|
|
new OracleParameter(":STORAGE_MODE", OracleDbType.Char),
|
|
new OracleParameter(":PRODUCTION_DATE", OracleDbType.Date),
|
|
new OracleParameter(":WARE_DATE", OracleDbType.Date),
|
|
new OracleParameter(":DOCCTNUMBER",OracleDbType.Varchar2),
|
|
//new OracleParameter(":STOCK_ID",OracleDbType.Varchar2),
|
|
new OracleParameter(":OCCUPY_NUM", OracleDbType.Decimal),
|
|
new OracleParameter(":NOTICE_ID",OracleDbType.Varchar2),
|
|
new OracleParameter(":ORI_BILL",OracleDbType.Varchar2)};
|
|
parameters[0].Value = model.PUTIN_ID;
|
|
parameters[1].Value = model.STORAGE_ID;
|
|
parameters[2].Value = model.AREA_ID;
|
|
parameters[3].Value = model.LOCATION_ID;
|
|
parameters[4].Value = model.GOODS_TYPEID;
|
|
parameters[5].Value = model.GOODS_ID;
|
|
parameters[6].Value = model.SHELVES_NUM;
|
|
parameters[7].Value = model.REMARK;
|
|
parameters[8].Value = model.VERSION;
|
|
parameters[9].Value = model.STORAGE_MODE;
|
|
parameters[10].Value = model.PRODUCTION_DATE;
|
|
parameters[11].Value = model.WARE_DATE;
|
|
parameters[12].Value = model.DOCCTNUMBER;
|
|
//parameters[13].Value = model.STOCK_ID;
|
|
parameters[13].Value = model.OCCUPY_NUM;
|
|
parameters[14].Value = model.NOTICE_ID;
|
|
parameters[15].Value = model.ORI_BILL;
|
|
|
|
SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 修改库存表数量
|
|
/// </summary>
|
|
/// <param name="MIStockModel">库存表Model</param>
|
|
public void UpdateMIStockNum(MIStockModel model)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
|
|
strSql.AppendLine("update T_MI_STOCK set ");
|
|
strSql.AppendLine("SHELVES_NUM= '" + model.SHELVES_NUM + "'");
|
|
strSql.AppendLine(" where PUTIN_ID='" + model.PUTIN_ID + "' and GOODS_ID='" + model.GOODS_ID + "' and LOCATION_ID='" + model.LOCATION_ID + "' and STORAGE_ID='"+ model .STORAGE_ID+ "'");
|
|
|
|
|
|
SystemDataObject.Instance.ExecuteSql(strSql.ToString());
|
|
}
|
|
/// <summary>
|
|
/// 查询空库位
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable GetNullLocal(string STORAGE_ID)
|
|
{
|
|
string sqlStr =@" select location_id,location_long,location_width,location_height,volume,hasvolume
|
|
from t_base_storage_area_location where location_id
|
|
not in (select min(location_id) from t_mi_stock group by location_id) and STATUS='0' and STORAGE_ID='"+ STORAGE_ID + "' ";
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
|
|
}
|
|
public DataSet GetStockName(string name)
|
|
{
|
|
string sqlStr =@"select
|
|
t.area_id,
|
|
t.location_id,
|
|
t.storage_id,
|
|
a.goods_id,
|
|
a.barcode,
|
|
min(t.PUTIN_ID)PUTIN_ID,
|
|
min(a.goods_name) goodsname,
|
|
min(t.ware_date)ware_date,
|
|
min( t.ctl)ctl,
|
|
sum(t.shelves_num)shelves_num
|
|
from t_mi_stock t
|
|
right join t_base_goods a
|
|
on t.goods_id = a.goods_id
|
|
where a.goods_name like '%" + name + "%' group by location_id,a.goods_id,area_id,storage_id,a.barcode";
|
|
return SystemDataObject.Instance.GetDataSet(sqlStr.ToString());
|
|
|
|
}
|
|
/// <summary>
|
|
/// 返回 根据数据
|
|
/// </summary>
|
|
/// <param name="name"></param>
|
|
/// <returns></returns>
|
|
public DataSet GetStockUserName(string name)
|
|
{
|
|
string sqlStr =@"select
|
|
t.area_id,
|
|
t.location_id,
|
|
t.storage_id,
|
|
a.goods_id,
|
|
a.barcode,t.customer_id,
|
|
min(a.goods_name) goodsname,
|
|
min(t.ware_date)ware_date,
|
|
min( t.ctl)ctl,
|
|
sum(t.shelves_num)shelves_num
|
|
from t_mi_stock t
|
|
left join t_base_goods a
|
|
on t.goods_id = a.goods_id
|
|
|
|
where t.REMARK like '%" + name + "%' group by location_id,a.goods_id,area_id,storage_id,a.barcode,t.CUSTOMER_ID";
|
|
return SystemDataObject.Instance.GetDataSet(sqlStr.ToString());
|
|
|
|
}
|
|
/// <summary>
|
|
/// 修改库存表占用数量
|
|
/// </summary>
|
|
/// <param name="MIStockModel">库存表Model</param>
|
|
public void UpdateMIStockOccupyNum(MIStockModel model)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
|
|
strSql.AppendLine("update T_MI_STOCK set ");
|
|
strSql.AppendLine("OCCUPY_NUM=OCCUPY_NUM + :OCCUPY_NUM");
|
|
strSql.AppendLine(" where PUTIN_ID=:PUTIN_ID and GOODS_ID=:GOODS_ID and LOCATION_ID=:LOCATION_ID and STORAGE_ID=:STORAGE_ID");
|
|
|
|
OracleParameter[] parameters = {
|
|
new OracleParameter(":OCCUPY_NUM", OracleDbType.Decimal),
|
|
new OracleParameter(":PUTIN_ID",OracleDbType.Varchar2),
|
|
new OracleParameter(":GOODS_ID",OracleDbType.Varchar2),
|
|
new OracleParameter(":LOCATION_ID",OracleDbType.Varchar2),
|
|
new OracleParameter(":STORAGE_ID",OracleDbType.Varchar2)
|
|
};
|
|
parameters[0].Value = model.OCCUPY_NUM;
|
|
parameters[1].Value = model.PUTIN_ID;
|
|
parameters[2].Value = model.GOODS_ID;
|
|
parameters[3].Value = model.LOCATION_ID;
|
|
parameters[4].Value = model.STORAGE_ID;
|
|
SystemDataObject.Instance.ExecuteSql(strSql.ToString(), parameters);
|
|
}
|
|
public DataTable LtkDataMis(string goodsId, string STORAGE_ID, string AREA_ID)
|
|
{
|
|
string sqlStr =@"select min(location_id) location_id,sum(shelves_num)shelves_num ,min(ctl)ctl ,min(PUTIN_ID)PUTIN_ID from T_MI_STOCK
|
|
where BARCODE='" + goodsId + "' and STORAGE_ID='" + STORAGE_ID + "' and AREA_ID='" + AREA_ID + "' group by location_id ";
|
|
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());;
|
|
}
|
|
/// <summary>
|
|
/// 删除库存表信息
|
|
/// </summary>
|
|
/// <param name="MIStock"></param>
|
|
public void DeleteMIStock(MIStockModel model)
|
|
{
|
|
DataSet data = new DataSet();
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.AppendLine("delete T_MI_STOCK ");
|
|
strSql.AppendLine(" where 1 = 1 ");
|
|
if (!string.IsNullOrEmpty(model.STOCK_ID))
|
|
{
|
|
strSql.AppendLine(" and STOCK_ID = '" + model.STOCK_ID + "' ");
|
|
}
|
|
if (!string.IsNullOrEmpty(model.PUTIN_ID))
|
|
{
|
|
strSql.AppendLine(" and PUTIN_ID = '" + model.PUTIN_ID + "' ");
|
|
}
|
|
if (!string.IsNullOrEmpty(model.LOCATION_ID))
|
|
{
|
|
strSql.AppendLine(" and LOCATION_ID = '" + model.LOCATION_ID + "' ");
|
|
}
|
|
if (!string.IsNullOrEmpty(model.CTL))
|
|
{
|
|
strSql.AppendLine(" and CTL = '" + model.CTL + "' ");
|
|
}
|
|
try
|
|
{
|
|
SystemDataObject.Instance.GetDataSet(strSql.ToString());
|
|
data = SystemDataObject.Instance.GetDataSet(@"select * from T_MI_STOCK where CTL = '" + model.CTL + "'");
|
|
if (data.Tables[0].Rows.Count > 0) { return; }
|
|
//SystemDataObject.Instance.ExecuteSqlCommit(strSql.ToString());
|
|
//SystemDataObject.Instance.GetDataSet(strSql.ToString());
|
|
}
|
|
catch
|
|
{
|
|
|
|
}
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取上架区为空库位的
|
|
/// </summary>
|
|
public DataTable GetMiStockNullData(string storage_id, string area_id)
|
|
{
|
|
|
|
string sqlStr =@"select t.location_id,t.sort, t.VOLUME,t.HASVOLUME ,t.storage_id ,m.storage_area_id
|
|
from T_BASE_STORAGE_AREA_LOCATION t left join t_base_loc_area m on t.location_id =m.location_id
|
|
where t.hasvolume=0 and t.storage_id =
|
|
'" + storage_id + "' and t.storage_area_id = '" + area_id + "' ";
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
|
}
|
|
/// <summary>
|
|
/// 根据商品的编号，获取商品所在库位
|
|
/// </summary>
|
|
/// <param name="goodsId"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetMiStockData(string goodsId, string storage_id, string area_id)
|
|
{
|
|
string sqlStr = "select t.location_id,a.sort, a.VOLUME,a.HASVOLUME,t.storage_id, m.STORAGE_AREA_ID from t_mi_stock t"
|
|
+ " left join T_BASE_STORAGE_AREA_LOCATION a on t.location_id=a.location_id "
|
|
+ " left join t_base_loc_area m on t.location_id =m.location_id"
|
|
+ " where t.goods_id='" + goodsId + "'and t.storage_id = '" + storage_id + "' and m.STORAGE_AREA_ID = '" + area_id + "' order by sort";
|
|
|
|
|
|
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
|
}
|
|
/// <summary>
|
|
/// 商品不在的，未满的库位
|
|
/// </summary>
|
|
/// <param name="goodsId"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetMiStockNotData(string goodsId, string storage_id, string area_id)
|
|
{
|
|
string sqlStr =@" select t.location_id,t.sort, t.VOLUME,t.HASVOLUME,t.storage_id, m.STORAGE_AREA_ID from T_BASE_STORAGE_AREA_LOCATION t
|
|
left join t_base_loc_area m on t.location_id =m.location_id where
|
|
not exists (select location_id from T_MI_Stock a where t.storage_id=a.storage_id and m.storage_area_id = a.area_id
|
|
and a.location_id=t.location_id and a.goods_id =
|
|
'" + goodsId + "' ) and t.storage_id = '" + storage_id + "' and m.STORAGE_AREA_ID = '" + area_id + "'and t.hasvolume=0 ";
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
|
}
|
|
|
|
public DataTable GetStockTaskLtk(string ck, string kq)
|
|
{
|
|
string sqlStr = "select location_id,'未执行' as sts from T_MI_Stock where STORAGE_ID = '" + ck + "' and AREA_ID = '" + kq + "' group by storage_id,area_id,location_id order by max(ware_date) asc";
|
|
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
|
|
|
}
|
|
/// <summary>
|
|
/// 获取商品相邻的库位
|
|
/// </summary>
|
|
/// <param name="goodsId"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetStockData(string goodsId, string storage_id, string area_id)
|
|
{
|
|
string sqlStr = " select d.location_id, d.sort, d.VOLUME, d.HASVOLUME,d.storage_id, z.STORAGE_AREA_ID"
|
|
+ " from (select a.sort, t.location_id"
|
|
+ " from T_MI_Stock t"
|
|
+ " left join T_BASE_STORAGE_AREA_LOCATION a"
|
|
+ " on t.location_id = a.location_id "
|
|
+ " where goods_id = '" + goodsId + "'"
|
|
+ " and a.storage_id = '" + storage_id + "'"
|
|
+ " and t.area_id = '" + area_id + "') m,"
|
|
+ " T_BASE_STORAGE_AREA_LOCATION d"
|
|
+ " left join t_base_loc_area z"
|
|
+ " on d.location_id = z.location_id "
|
|
+ " where d.storage_id = '" + storage_id + "'"
|
|
+ " and z.STORAGE_AREA_ID = '" + area_id + "' "
|
|
+ " and (d.sort < m.sort - 1 or d.sort > m.sort + 1)"
|
|
+ " and d.hasvolume != d.volume";
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr);
|
|
|
|
}
|
|
}
|
|
|
|
}
|