188 lines
8.9 KiB
C#
188 lines
8.9 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Data;
|
|
using WMS.Model.Stock;
|
|
using WMS.IData;using Oracle.ManagedDataAccess.Client;
|
|
using WMS.IData.IReport;
|
|
|
|
namespace WMS.SqlServerData.ReportData
|
|
{
|
|
public class MIStockSearchData : IMIStockSearch
|
|
{
|
|
/// <summary>
|
|
/// 此查询 用于库存查看功能 查询
|
|
/// </summary>
|
|
/// <param name="model"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetMiStockForLookUp(MIStockModel model)
|
|
{
|
|
//StringBuilder strSql = new StringBuilder();
|
|
////T_VIEW_MISTOCKSEARCH 视图
|
|
//strSql.Append(@"select t.*,(case t.STS when '0' then '正常库存' when '1' then '冻结库存' end )as STS from t_mi_stock t where 1 = 1 ");
|
|
//strSql.AppendLine(" order by WARE_DATE desc");
|
|
|
|
|
|
string sqls = @"select t.stock_id,
|
|
t.putin_id,
|
|
t.storage_id,
|
|
t.area_id,
|
|
t.location_id,
|
|
t.goods_typeid,
|
|
t.goods_id,
|
|
t.shelves_num,
|
|
t.remark,
|
|
t.version,
|
|
t.storage_mode,
|
|
t.provider_id,
|
|
t.production_date,
|
|
t.ware_date,
|
|
t.docctnumber,
|
|
t.goodsvolume,
|
|
t.occupy_num,
|
|
t.notice_id,
|
|
t.ori_bill,
|
|
t.ctl,
|
|
t.barcode,
|
|
t.customer_id,
|
|
t.goods_sku,
|
|
t.locidname,
|
|
t.wgh,
|
|
t.box_type,
|
|
t.packing_num,
|
|
t.produclotid,
|
|
t.whseloc,
|
|
t.high,
|
|
|
|
t.price,
|
|
t.problem,
|
|
t.order_num,
|
|
t.customer,
|
|
t.mark,
|
|
t.net_weigh,
|
|
t.scale_unit,
|
|
t.goods_name,
|
|
(case t.STSEBS when 0 then '空箱不上报' when 1 then 'mes上报成功' when 2 then 'mes上报失败' when 9 then 'mes上报失败' end) as STSEBS,
|
|
(case t.STS when '0' then '正常' when '1' then '出库中' when '2' then '已下发堆垛机'
|
|
when '6' then '堆垛机完成' when '9' then '手持拣货完成' when '10' then '库存扣除处理中' end) as STS
|
|
from t_mi_stock t where 1 = 1 order by GOODS_SKU ASC";
|
|
|
|
|
|
return SystemDataObject.Instance.GetDataTable(sqls.ToString());
|
|
}
|
|
|
|
public DataTable GetMiStockForLookUp(MIStockModel model,string tasksts)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
//T_VIEW_MISTOCKSEARCH 视图
|
|
strSql.Append(@" select t.*,(case t.STS when '0' then '正常库存' when '1' then '冻结库存' end )as locsts ,(case t.status when '9' then '预出库库位' else '正常库位' end )as locstatus from T_VIEW_MISTOCKSEARCH t where storage_id='JXJWYCK'and 1 = 1 ");
|
|
strSql.AppendLine(" order by GOODS_SKU ASC");
|
|
|
|
return SystemDataObject.Instance.GetDataTable(strSql.ToString());
|
|
}
|
|
|
|
/// <summary>
|
|
/// 某年统计月库存报表 例2013年全年12个月 某商品库存变化
|
|
/// </summary>
|
|
/// <param name="goods_id"></param>
|
|
/// <returns></returns>
|
|
public DataTable MonthReportData(MIStockBackModel mo)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append(@"select operator_date Dat,sum(change_num) NumC from T_MI_STOCK_BACK where goods_id='" + mo.GOODS_ID +@"'
|
|
and operator_date between convert(varchar(100),'" + mo.TIMESTART.ToString("yyyy-MM-dd") + "','23') and convert(varchar(100),'" + mo.TIMEEND.ToString("yyyy-MM-dd") +@"','23')
|
|
group by operator_date ;");
|
|
return SystemDataObject.Instance.GetDataTable(strSql.ToString());
|
|
}
|
|
|
|
|
|
|
|
//某个月 每天详细库存
|
|
public DataTable DayReportData(MIStockBackModel mo)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
// strSql.Append(" select OPERATOR_DATE,change_num from T_MI_STOCK_BACK where goods_id='" + mo.GOODS_ID +@"'
|
|
// and operator_date between to_date('" + mo.TIMESTART.ToString("yyyy-MM-dd") + "','yyyy-MM-dd') and to_date('" + mo.TIMEEND.ToString("yyyy-MM-dd") +@"','yyyy-MM-dd') ");
|
|
// return SystemDataObject.Instance.GetDataTable(strSql.ToString());
|
|
|
|
//OPERATOR_DATE,change_num
|
|
strSql.Append(" select * from T_VIEW_MISTOCKBACKSEARCH where goods_id='" + mo.GOODS_ID +@"'
|
|
and operator_date between convert(varchar(100),'" + mo.TIMESTART + "',20) and convert(varchar(100),'" + mo.TIMEEND + "',20) ");
|
|
|
|
return SystemDataObject.Instance.GetDataTable(strSql.ToString());
|
|
}
|
|
public DataTable GetVendorsSite()
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append(@"select * from cux_wms_po_vendor_sites_itf_zh");
|
|
return SystemDataObject.Instance.GetDataTable(strSql.ToString());
|
|
}
|
|
public DataTable GetVendors()
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append(@"select * from cux_wms_po_vendors_intf_zh");
|
|
return SystemDataObject.Instance.GetDataTable(strSql.ToString());
|
|
}
|
|
public DataTable GetSubinventory()
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append(@"select * from cux_wms_subinventory_itf_zh");
|
|
return SystemDataObject.Instance.GetDataTable(strSql.ToString());
|
|
}
|
|
public DataTable GetItems()
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append(@"select * from cux_wms_system_items_intf_zh");
|
|
return SystemDataObject.Instance.GetDataTable(strSql.ToString());
|
|
}
|
|
public DataTable GetReason()
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append(@"select * from t_scrap_reason");
|
|
return SystemDataObject.Instance.GetDataTable(strSql.ToString());
|
|
}
|
|
public string DeleteReason(string id,string reason)
|
|
{
|
|
string errtext = string.Empty;
|
|
string strSQL = string.Empty;
|
|
try
|
|
{
|
|
strSQL = "delete from t_scrap_reason where id = '" + id + "' and reason='" + reason + "'";
|
|
SystemDataObject.Instance.ExecuteSqlCommit(strSQL);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
errtext= ex.ToString();
|
|
}
|
|
return errtext;
|
|
}
|
|
public void AddReason(string id,string reason)
|
|
{
|
|
string strSQL = string.Empty;
|
|
|
|
strSQL = "insert into t_scrap_reason (id,reason) values('"+id+"','"+reason+"')";
|
|
|
|
SystemDataObject.Instance.ExecuteSqlCommit(strSQL);
|
|
}
|
|
public DataTable GetMoveLot(string row, string cln, string layer, string area)
|
|
{
|
|
string sqlStr = @"select location_id,abs(cln-" + cln + ") as c,abs(remark-" + area + ") as a from T_BASE_STORAGE_AREA_LOCATION where cln!='" + cln + "' and layer1='" + layer + "' and status='0' and location_id not in (select location_id from t_mi_stock where location_id is not null) AND OUTSTAND1='0' order by a asc,c asc ,sort asc ";
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
|
|
}
|
|
public DataTable GetMoveLot2(string row, string cln, string layer, string area, string ABC_ID)
|
|
{
|
|
string sqlStr = "";
|
|
if (ABC_ID == "A")
|
|
{
|
|
sqlStr = @"select location_id,abs(cln-" + cln + ") as c,abs(remark-" + area + ") as a from T_BASE_STORAGE_AREA_LOCATION where remark>'" + area + "' and layer1='" + layer + "' and status='0' and location_id not in (select location_id from t_mi_stock where location_id is not null) AND OUTSTAND1='0' order by a asc,c asc ,sort asc ";
|
|
}
|
|
else
|
|
{
|
|
sqlStr = @"select location_id,abs(cln-" + cln + ") as c,abs(remark-" + area + ") as a from T_BASE_STORAGE_AREA_LOCATION where remark<'" + area + "' and layer1='" + layer + "' and status='0' and location_id not in (select location_id from t_mi_stock where location_id is not null) AND OUTSTAND1='0' order by a asc,c asc ,sort asc ";
|
|
}
|
|
return SystemDataObject.Instance.GetDataTable(sqlStr.ToString());
|
|
}
|
|
}
|
|
}
|