using IWareDataAccess.Entity.Base;
|
using IWareDataAccess.Entity.Order;
|
using iWareSda_QQJF.WCSNEW.EDM;
|
using iWareSda_QQJF.WEBAPI.JSONEntity;
|
using Oracle.ManagedDataAccess.Client;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Globalization;
|
using System.Linq;
|
using System.Text;
|
using System.Threading;
|
using System.Threading.Tasks;
|
|
namespace iWareSda_QQJF.WEBAPI
|
{
|
public class OracleWord
|
{
|
public static long oldidOfItem = 0;//指针
|
public static long oldidOfKanban = 0;//指针
|
static decimal numSum = 0;//出库总零件数
|
|
|
static string connStr = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.69.136.2)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.69.136.4)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=erpdb)));Persist Security Info=True;User ID=cywms;Password=wmscy;";
|
|
/// <summary>
|
/// 语句调用方法
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <param name="parameters"></param>
|
/// <returns></returns>
|
public static DataTable ExecuteDataTable(string sql, params OracleParameter[] parameters)
|
{
|
using (OracleConnection conn = new OracleConnection(connStr))
|
{
|
conn.Open();
|
using (OracleCommand cmd = conn.CreateCommand())
|
{
|
cmd.CommandText = sql;
|
//cmd.Parameters.AddRange(parameters);
|
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
|
DataTable datatable = new DataTable();
|
adapter.Fill(datatable);
|
return datatable;
|
}
|
}
|
}
|
|
/// <summary>
|
/// 更新任务信息
|
/// </summary>
|
public static void SearchTask()
|
{
|
|
try
|
{
|
string sqlString = "select * from (select * from QDERP.DI_T_KANBANHEADER where getgodscen ='V3012' and DI_T_KANBANHEADERID > " + oldidOfKanban + " order by DI_T_KANBANHEADERID) where rownum < 2";
|
DataTable dt = ExecuteDataTable(sqlString, new OracleParameter(":id", 1));
|
DataRow row = dt.AsEnumerable().FirstOrDefault();
|
if (row != null)
|
{
|
//调用接口
|
if (row[41].ToString() == "I" || row[41].ToString() == "U")//插入
|
{
|
OutOrderWebEntity order = GetKANBAN(row);
|
|
if (order==null)
|
{
|
//记录读取指针是
|
oldidOfKanban = long.Parse(row[0].ToString());
|
return;
|
}
|
|
//GetJsonOfItem getInfo = ReadErpSendToWMS.AddTaskMethod(order);
|
//if (getInfo.result == false)
|
//{
|
// if (getInfo.data != null)
|
// {
|
// //WZ.Useful.Commons.LogTextHelper.WriteLine("OracleWord", "SearchADDTask", getInfo.data.orderPanCode.ToString());
|
// }
|
// //WZ.Useful.Commons.LogTextHelper.WriteLine("OracleWord", "SearchADDTask", getInfo.msg);
|
//}
|
string msg = "";
|
IWareDataAccess.Order.OUTORDER.OutOrderSqlFunc.AddOrUpdate(order,out msg);
|
}
|
//else if (row[41].ToString() == "U")//修改
|
//{
|
// ERPTask task = GetKANBAN(row);
|
|
// if (task.details.Count == 0)
|
// {
|
// //记录读取指针
|
// oldidOfKanban = long.Parse(row[0].ToString());
|
// return;
|
// }
|
|
|
// GetJsonOfItem getInfo = ReadErpSendToWMS.UpdateTaskMethod(task);
|
// if (getInfo.result == false)
|
// {
|
// if (getInfo.data != null)
|
// {
|
// WZ.Useful.Commons.LogTextHelper.WriteLine("OracleWord", "SearchUpdateTask", getInfo.data.orderPanCode.ToString());
|
// }
|
// WZ.Useful.Commons.LogTextHelper.WriteLine("OracleWord", "SearchUpdateTask", getInfo.msg);
|
// }
|
//}
|
else if (row[41].ToString() == "D")//删除
|
{
|
OutOrderWebEntity order = new OutOrderWebEntity();
|
//task.orderPlanCode = row[6].ToString();
|
order.productionLineName = row[6].ToString();
|
order.outOrderCode = row[6].ToString();
|
//GetJsonOfItem getInfo = ReadErpSendToWMS.DeleteTaskMethod(order);
|
//if (getInfo.result == false)
|
//{
|
// // WZ.Useful.Commons.LogTextHelper.WriteLine("OracleWord", "SearchDeleteTask", getInfo.msg);
|
//}
|
string msg = "";
|
IWareDataAccess.Order.OUTORDER.OutOrderSqlFunc.Delete(order, out msg);
|
}
|
//记录读取指针
|
oldidOfKanban = long.Parse(row[0].ToString());
|
}
|
else
|
{
|
//已经最新数据，等待一分钟
|
Thread.Sleep(1000 * 60);
|
}
|
}
|
catch (Exception e)
|
{
|
//WZ.Useful.Commons.LogTextHelper.WriteLine("OracleWord", "SearchTask", e.ToString());
|
//等待
|
Thread.Sleep(1000);
|
}
|
}
|
|
|
/// <summary>
|
/// 获取看板信息
|
/// </summary>
|
/// <param name="row"></param>
|
/// <returns></returns>
|
private static OutOrderWebEntity GetKANBAN(DataRow row)
|
{
|
//ERPTask task = new ERPTask();
|
////必填
|
////task.orderPlanCode = row[6].ToString();
|
//task.sourceCode = row[6].ToString();
|
//task.chanceDate = row[25].ToString();
|
//task.orderType = "焊装出库";
|
//task.creator = "超级管理员";
|
//task.createDate = DateTime.Now.ToString();
|
//////关联数据
|
////DataTable data = GetDesTask(row[6].ToString());
|
////List<DataRow> dtList = data.AsEnumerable().ToList();
|
////numSum = 0;
|
|
////for (int i = 0; i < dtList.Count(); i++)
|
////{
|
//// Details details = new Details();
|
|
//// details.productCode = dtList[i].ItemArray[16].ToString();
|
//// AddItemByNo(dtList[i].ItemArray[16].ToString());
|
//// //details.productName = SearchItemByNo(dtList[i].ItemArray[16].ToString());
|
//// //details.totalWeight = 0;
|
//// details.makeRoute = 0;
|
//// details.quantity = decimal.Parse(dtList[i].ItemArray[9].ToString());
|
//// details.destinationName = dtList[i].ItemArray[25].ToString();
|
//// details.batchNumber = dtList[i].ItemArray[5].ToString();
|
//// numSum = numSum + details.quantity;
|
|
//// task.details.Add(details);
|
|
|
////}
|
|
//task.totalQuantity = numSum;
|
//task.totalWeight = numSum * 0;
|
////貌似要填
|
//task.consignorCode = "V30";
|
//task.storageName = "立体库";
|
|
OutOrderWebEntity order = new OutOrderWebEntity();
|
try
|
{
|
order.doTime = DateTime.Parse(row[25].ToString());
|
}
|
catch
|
{
|
|
}
|
order.orderType = "焊装出库";
|
order.outOrderCode = row[6].ToString();
|
//order.UPDATETIME = DateTime.Now;
|
string msg="";
|
//ProductionLineWebEntity line=new ProductionLineWebEntity();
|
//line.productionName = row[6].ToString();
|
//var lineList = IWareDataAccess.Base.PRODUCTIONLINE.ProductionLineSqlFunc.SearchView(line,20,1, out msg);
|
//if(lineList.Count()>0)
|
//{
|
// order.productionLineCode = lineList[0].productionCode;
|
// order.productionLineName = lineList[0].productionName;
|
|
//}
|
//关联数据
|
DataTable data = GetDesTask(row[6].ToString());
|
List<DataRow> dtList = data.AsEnumerable().ToList();
|
numSum = 0;
|
|
for (int i = 0; i < dtList.Count(); i++)
|
{
|
//Details details = new Details();
|
|
//details.productCode = dtList[i].ItemArray[16].ToString();
|
AddItemByNo(dtList[i].ItemArray[16].ToString());
|
////details.productName = SearchItemByNo(dtList[i].ItemArray[16].ToString());
|
////details.totalWeight = 0;
|
//details.makeRoute = 0;
|
//details.quantity = decimal.Parse(dtList[i].ItemArray[9].ToString());
|
//details.destinationName = dtList[i].ItemArray[25].ToString();
|
//details.batchNumber = dtList[i].ItemArray[5].ToString();
|
numSum = numSum + decimal.Parse(dtList[i].ItemArray[9].ToString());
|
|
//task.details.Add(details);
|
|
|
}
|
order.totalOutNum = (int)numSum;
|
order.isErp = 1;
|
return order;
|
}
|
|
|
/// <summary>
|
/// 根据物料编号搜索物料
|
/// </summary>
|
/// <param name="no"></param>
|
public static string SearchItemByNo(string no)
|
{
|
string sqlString = "select * from QDERP.DI_T_MMRGENMTL where MTLNO = '"+ no +"'";
|
DataTable dt = ExecuteDataTable(sqlString, new OracleParameter(":id", 1));
|
DataRow row = dt.AsEnumerable().FirstOrDefault();
|
long id = long.Parse(row[5].ToString());
|
SearchItem(id);//添加此物料
|
string des = GetDes(int.Parse(row[5].ToString()));
|
return des;
|
}
|
|
/// <summary>
|
/// 根据物料编号插入物料
|
/// </summary>
|
/// <param name="no"></param>
|
public static void AddItemByNo(string no)
|
{
|
string sqlString = "select * from QDERP.DI_T_MMRGENMTL where MTLNO = '" + no + "'";
|
DataTable dt = ExecuteDataTable(sqlString, new OracleParameter(":id", 1));
|
DataRow row = dt.AsEnumerable().FirstOrDefault();
|
long id = long.Parse(row[5].ToString());
|
SearchItem(id);//添加此物料
|
}
|
|
/// <summary>
|
/// 搜索新物料(指针指定)
|
/// </summary>
|
public static void SearchItem(long id)
|
{
|
try
|
{
|
string sqlString = "select * from (select * from QDERP.DI_T_MMRGENMTL where MMRGENMTLID = " + id + " order by MMRGENMTLID) where rownum < 2";
|
DataTable dt = ExecuteDataTable(sqlString, new OracleParameter(":id", 1));
|
DataRow row = dt.AsEnumerable().FirstOrDefault();
|
if (row != null)
|
{
|
//AddItem item = new AddItem();
|
////必填
|
//item.productCode = row[6].ToString();
|
//item.productName = GetDes(int.Parse(row[5].ToString()));
|
//item.productModel = row[5].ToString();
|
//item.consignorCode = "";
|
//item.providerCode = "";
|
//item.providerShortName = "默认供应商";
|
//item.enable = 1;
|
////ERP存在数据
|
//item.productSpec = row[0].ToString();
|
//item.smallUnit = row[4].ToString();
|
////貌似必填
|
//item.consignorName = "一汽解放青岛";
|
//item.consignorCode = "V30";
|
//item.consignor_Id = 323;
|
////调用接口
|
//GetJsonOfItem getInfo = ReadErpSendToWMS.AdditemMethod(item);
|
//if (getInfo.result == false)
|
//{
|
// if (getInfo.data != null)
|
// {
|
// //WZ.Useful.Commons.LogTextHelper.WriteLine("OracleWord", "SearchItem", getInfo.data.productModel.ToString());
|
// }
|
// // WZ.Useful.Commons.LogTextHelper.WriteLine("OracleWord", "SearchItem", getInfo.msg);
|
//}
|
|
string msg = "";
|
ItemWebEntity item = new ItemWebEntity();
|
item.itemName = row[6].ToString();
|
item.itemDes = GetDes(int.Parse(row[5].ToString()));
|
//item.productModel = row[5].ToString();
|
item.enable = 1;
|
//ERP存在数据
|
//item.productSpec = row[0].ToString();
|
item.unit = row[4].ToString();
|
//貌似必填
|
IWareDataAccess.Base.ITEM.ItemSqlFunc.AddOrUpdate(item, out msg);
|
|
//保存到数据库
|
using (Model edm = new Model())
|
{
|
BASE_ITEM sqlItem = new BASE_ITEM();
|
|
}
|
|
//记录读取指针
|
oldidOfItem = int.Parse(row[5].ToString());
|
|
}
|
else
|
{
|
int a = 0;
|
}
|
}
|
catch (Exception e)
|
{
|
// WZ.Useful.Commons.LogTextHelper.WriteLine("OracleWord", "SearchItem", e.ToString());
|
//等待
|
Thread.Sleep(1000);
|
}
|
}
|
|
/// <summary>
|
/// 搜索新物料
|
/// </summary>
|
public static void SearchItem()
|
{
|
try
|
{
|
string sqlString = "select * from (select * from QDERP.DI_T_MMRGENMTL where MMRGENMTLID > " + oldidOfItem + " order by MMRGENMTLID) where rownum < 2";
|
DataTable dt = ExecuteDataTable(sqlString, new OracleParameter(":id", 1));
|
DataRow row = dt.AsEnumerable().FirstOrDefault();
|
if (row != null)
|
{
|
//AddItem item = new AddItem();
|
////必填
|
//item.productCode = row[6].ToString();
|
//item.productName = GetDes(int.Parse(row[5].ToString()));
|
//item.productModel = row[5].ToString();
|
//item.consignorCode = "";
|
//item.providerCode = "";
|
//item.providerShortName = "默认供应商";
|
//item.enable = 1;
|
////ERP存在数据
|
//item.productSpec = row[0].ToString();
|
//item.smallUnit = row[4].ToString();
|
////貌似必填
|
//item.consignorName = "一汽解放青岛";
|
//item.consignorCode = "V30";
|
//item.consignor_Id = 323;
|
////调用接口
|
//GetJsonOfItem getInfo = ReadErpSendToWMS.AdditemMethod(item);
|
//if (getInfo.result == false)
|
//{
|
// if (getInfo.data != null)
|
// {
|
// // WZ.Useful.Commons.LogTextHelper.WriteLine("OracleWord", "SearchItem", getInfo.data.productModel.ToString());
|
// }
|
// //WZ.Useful.Commons.LogTextHelper.WriteLine("OracleWord", "SearchItem", getInfo.msg);
|
//}
|
string msg = "";
|
ItemWebEntity item = new ItemWebEntity();
|
item.itemName = row[6].ToString();
|
item.itemDes = GetDes(int.Parse(row[5].ToString()));
|
//item.productModel = row[5].ToString();
|
item.enable = 1;
|
//ERP存在数据
|
//item.productSpec = row[0].ToString();
|
item.unit = row[4].ToString();
|
//貌似必填
|
IWareDataAccess.Base.ITEM.ItemSqlFunc.AddOrUpdate(item, out msg);
|
|
//保存到数据库
|
using (Model edm = new Model())
|
{
|
BASE_ITEM sqlItem = new BASE_ITEM();
|
|
}
|
//记录读取指针
|
oldidOfItem = int.Parse(row[5].ToString());
|
|
}
|
|
}
|
catch(Exception e)
|
{
|
// WZ.Useful.Commons.LogTextHelper.WriteLine("OracleWord", "SearchItem", e.ToString());
|
//等待
|
Thread.Sleep(1000);
|
}
|
}
|
|
|
/// <summary>
|
/// 获取物料描述
|
/// </summary>
|
/// <param name="id"></param>
|
/// <returns></returns>
|
public static string GetDes(int id)
|
{
|
string sqlString = "select * from QDERP.DI_T_MMRGENMTLTRL where MMRGENMTLID = " + id;
|
DataTable dt = ExecuteDataTable(sqlString, new OracleParameter(":id", 1));
|
DataRow row = dt.AsEnumerable().FirstOrDefault();
|
return row[2].ToString();
|
}
|
|
/// <summary>
|
/// 获取关联任务描述
|
/// </summary>
|
/// <param name="id"></param>
|
/// <returns></returns>
|
public static DataTable GetDesTask(string id)
|
{
|
string sqlString = "select * from QDERP.DI_T_KANBANITEM where shopKBNO = '" + id + "' and DI_OPERTYPE != 'D'";
|
DataTable dt = ExecuteDataTable(sqlString, new OracleParameter(":id", 1));
|
return dt;
|
}
|
}
|
}
|
