/// <summary>
/// Обработчик события кода на погрузку
/// </summary>
/// <param name="l4MsgInfo">Модель таблицы L4L3Event для обработки кода</param>
/// <returns>Результат обработки</returns>
public TCheckResult ShippingMng(TL4MsgInfo l4MsgInfo)
{
TCheckResult result = new TCheckResult();
TForShipping forShipping = TForShipping.NOShipped;
L4L3InterfaceServiceGlobalCheck global = new L4L3InterfaceServiceGlobalCheck();
logger.Error($"ShippingMng - STARTED -> MsgId: {l4MsgInfo.msgCounter}");
L4L3ShippingRepo shippingRepo = new L4L3ShippingRepo();
List <L4L3Shipping> ship = shippingRepo.GetListData(l4MsgInfo);
if (ship == null)
{
result.isOK = false;
result.data = $"В таблице {l4l3unterfacetable} поле {l4MsgInfo.msgCounter} запись не найдена";
global.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_ERROR, result.data);
return(result);
}
L4L3ServiceCheckShipping checkship = new L4L3ServiceCheckShipping();
result = checkship.ShippingCheck(ship, l4MsgInfo);
foreach (L4L3Shipping sship in ship)
{
if (result.isOK)
{
if (l4MsgInfo.opCode == L4L3InterfaceServiceConst.OP_CODE_NEW && sship.bolStatus == L4L3InterfaceServiceConst.BOL_NOT_SENT)
{
CreateBolIfNotEx(sship.bolId);
forShipping = TForShipping.NOShipped;
}
else if (l4MsgInfo.opCode == L4L3InterfaceServiceConst.OP_CODE_UPD && sship.bolStatus == L4L3InterfaceServiceConst.BOL_NOT_SENT)
{
forShipping = TForShipping.NOShipped;
}
else if (l4MsgInfo.opCode == L4L3InterfaceServiceConst.OP_CODE_UPD && sship.bolStatus == L4L3InterfaceServiceConst.BOL_SENT)
{
forShipping = TForShipping.YESShipped;
}
else if (l4MsgInfo.opCode == L4L3InterfaceServiceConst.OP_CODE_NEW && sship.bolStatus == L4L3InterfaceServiceConst.BOL_SENT)
{
forShipping = TForShipping.YESShipped;
}
else if (l4MsgInfo.opCode == L4L3InterfaceServiceConst.OP_CODE_DEL && sship.bolStatus == L4L3InterfaceServiceConst.BOL_SENT)
{
forShipping = TForShipping.YESShipped;
}
result = LocalSetPiece(l4MsgInfo, sship, TPieceAction.paAssign, forShipping);
}
}
if (!result.isOK)
{
global.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_ERROR, result.data);
logger.Error(result.data);
}
else
{
global.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_SUCCESS, result.data);
}
logger.Error($"ShippingMng - STOPPED -> MsgId:{l4MsgInfo.msgCounter}");
return(result);
}
/// <summary>
/// Обновление статуса сообщения
/// </summary>
/// <param name="l4MsgInfo">Модель таблицы L4L3Event для обработка кода</param>
public void UpdateMsgStatus(TL4MsgInfo l4MsgInfo)
{
OracleDynamicParameters odp = new OracleDynamicParameters {
BindByName = true
};
odp.Add("P_MSG_STATUS", l4MsgInfo.msgReport.status);
odp.Add("P_MSG_REMARK", l4MsgInfo.msgReport.remark);
odp.Add("P_MSG_COUNTER", l4MsgInfo.msgCounter);
string fields = "MSG_STATUS = :P_MSG_STATUS, MSG_REMARK = :P_MSG_REMARK";
string where = "MSG_COUNTER = :P_MSG_COUNTER";
string stm = $"UPDATE L4_L3_SERVICE_EVENT SET {fields} WHERE {where}";
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
connection.Execute(stm, odp);
}
where = "MSG_COUNTER = ( SELECT MSG_COUNTER_SOURCE FROM L4_L3_SERVICE_EVENT WHERE MSG_COUNTER=:P_MSG_COUNTER AND ROWNUM=1)";
stm = $"UPDATE L4_L3_EVENT SET {fields} WHERE {where}";
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
connection.Execute(stm, odp);
}
}
/// <summary>
/// Конструктор для получения модели событий и данных
/// </summary>
/// <param name="data">Данные</param>
/// <param name="pL4MsgInfoPtr">Модель событий</param>
public TL4EngineInterfaceMngRepo(L4L3Customer data, TL4MsgInfo pL4MsgInfoPtr)
{
this.tL4Engine = new TL4EngineInterfaceMng();
this.l4MsgInfo = new TL4MsgInfo();
tL4Engine.m_QryData = new EngineSom(data);
tL4Engine.m_L4MsgInfoPtr = pL4MsgInfoPtr;
}
/// <summary>
/// Получение аттрибутов из L4_L3_SO_LINE
/// </summary>
/// <param name="qryData">Модель таблицы L4L3SoHeader</param>
/// <param name="l4MsgInfo">Модель таблицы L4L3Event для обработки кода</param>
/// <returns>Результат обработки</returns>
public TCheckResult AttributeCheck(L4L3SoHeader qryData, TL4MsgInfo l4MsgInfo)
{
TCheckResult result = new TCheckResult();
result.isOK = false;
L4L3SoLine l4L3SoLine;
string sqlstr = $"SELECT * FROM L4_L3_SO_LINE WHERE MSG_COUNTER = {qryData.msgCounter}";
using (OracleConnection connection = GetConnection())
{
l4L3SoLine = connection.QueryFirstOrDefault <L4L3SoLine>(sqlstr, null);
}
if (l4L3SoLine == null)
{
result.isOK = false;
result.data = "Заказ не имеет записей в таблице L4_L3_SO_LINE";
l4MsgInfo.msgReport.status = L4L3InterfaceServiceConst.MSG_STATUS_ERROR;
l4MsgInfo.msgReport.remark = result.data;
}
else
{
result.isOK = true;
}
if (!result.isOK)
{
check.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_ERROR, result.data);
}
else
{
check.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_SUCCESS, result.data);
}
return(result);
}
/// <summary>
/// Сообщение обработано успешно
/// </summary>
/// <param name="l4MsgInfo">Модель таблицы L4L3Event для обработки кодов</param>
/// <param name="result">Результат обработки</param>
/// <returns>Результат обработки</returns>
public TCheckResult MngSuccesed(TL4MsgInfo l4MsgInfo, TCheckResult result)
{
SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_SUCCESS, "");
result.isOK = true;
result.data = l4MsgInfo.msgReport.remark;
return(result);
}
/// <summary>
/// Создание нового заказа (Требуется дальнейшая реализация)
/// </summary>
/// <param name="QryData">Модель таблицы L4_L3_SO_HEADER для обработки</param>
/// <param name="l4MsgInfo">Модель таблицы L4L3Event для обработки кода</param>
public void CreateNewOrder(L4L3SoHeader QryData, TL4MsgInfo l4MsgInfo)
{
tsoHeader = new TSoHeader();
string sNumeration = auxConstant.GetStringAuxConstant("SO_NUMERATION");
TL4EngineInterfaceMngRepo eimOrderEntry = new TL4EngineInterfaceMngRepo(QryData, l4MsgInfo);
try
{
Int32.TryParse(eimOrderEntry.GetCreateUserId(), out int iUserId);
}
catch
{
eimOrderEntry.NotifyErrorMessage("Ошибка при возвращении ID пользователя");
}
int iShipToCode = ManageShipTo(l4MsgInfo, eimOrderEntry);
if (iShipToCode >= 0)
{
tsoHeader = tSo.Create(QryData, l4MsgInfo, iShipToCode, true);
eimOrderEntry.l4MsgInfo.msgReport = new TMessageResult();
eimOrderEntry.l4MsgInfo.msgReport.status = L4L3InterfaceServiceConst.MSG_STATUS_SUCCESS;
if (eimOrderEntry.l4MsgInfo.msgReport.status == L4L3InterfaceServiceConst.MSG_STATUS_SUCCESS)
{
logger.Info("Заказ создан");
}
}
}
/// <summary>
/// Обработка результата работы обработчика
/// </summary>
/// <param name="l4MsgInfo">Модель L4L3Event для обработки кода</param>
/// <param name="newStatus">Код статуса</param>
/// <param name="newRemark">Примечание по сообщению</param>
/// <param name="logMessage">Сообщение в лог файл</param>
public TL4MsgInfo SetMsgResult(TL4MsgInfo l4MsgInfo, int newStatus, string newRemark, string logMessage = "")
{
switch (newStatus)
{
case L4L3InterfaceServiceConst.MSG_STATUS_ERROR:
l4MsgInfo.msgReport.status = newStatus;
l4MsgInfo.msgReport.remark = "ERROR: " + newRemark;
if (logMessage.Length > 0)
{
logger.Error("MSG_COUNTER " + l4MsgInfo.msgCounter + "MSG ID" + l4MsgInfo.msgId + "ERROR: " + logMessage);
}
break;
case L4L3InterfaceServiceConst.MSG_STATUS_WARNING:
l4MsgInfo.msgReport.status = newStatus;
l4MsgInfo.msgReport.remark = "WARNING: " + newRemark;
if (logMessage.Length > 0)
{
logger.Error("MSG_COUNTER " + l4MsgInfo.msgCounter + "MSG ID" + l4MsgInfo.msgId + "WARNING: " + logMessage);
}
break;
case L4L3InterfaceServiceConst.MSG_STATUS_SUCCESS:
l4MsgInfo.msgReport.status = newStatus;
l4MsgInfo.msgReport.remark = newRemark;
break;
}
return(l4MsgInfo);
}
/// <summary>
/// Обновление заказа
/// </summary>
/// <param name="QryData">Модель таблицы L4_L3_SO_HEADER для обработки</param>
/// <param name="l4MsgInfo">Модель таблицы L4L3Event для обработки кода</param>
/// <param name="bIsDeletion"></param>
public void UpdateOrder(L4L3SoHeader QryData, TL4MsgInfo l4MsgInfo, bool bIsDeletion = false)
{
if (bIsDeletion)
{
logger.Info("Заказ обновлен");
}
else
{
logger.Info("Заказ удален");
}
}
/// <summary>
/// Получение детали (Требуется доработка)
/// </summary>
/// <param name="l4MsgInfo">Модель таблицы L4L3Event для обработки кода</param>
/// <param name="ship">Модель таблицы L4L3Shipping</param>
/// <param name="action">Тип перечисления действий</param>
/// <param name="forShipping">Тип перечисления отгрузки</param>
/// <returns>Результат обработки</returns>
public TCheckResult LocalSetPiece(TL4MsgInfo l4MsgInfo, L4L3Shipping ship, TPieceAction action, TForShipping forShipping)
{
TCheckResult checkResult = new TCheckResult();
OracleDynamicParameters odp = new OracleDynamicParameters();
List <int> pieceNum = new List <int>();
checkResult.isOK = false;
string str = "SELECT PIECE_NUM_ID FROM PIECE WHERE PIECE_ID = :PIECE_ID AND STATUS= :STATUS AND PRODUCTION_MACHINE_CODE = 'HSM' ";
if (forShipping == TForShipping.NOShipped)
{
str += "AND READY_TO_SHIP = :READY_TO_SHIP ";
odp.Add("PIECE_ID", ship.pieceId);
//odp.Add("STATUS",);// Узнать код PIECE_STATUS_EXIST
//odp.Add("READY_TO_SHIP",)//Узнать код PIECE_READY_TO_SHIP
}
else if (action == TPieceAction.paAssign && forShipping == TForShipping.YESShipped)
{
str += "AND READY_TO_SHIP= :READY_TO_SHIP";
odp.Add("PIECE_ID", ship.pieceId);
//odp.Add("STATUS",);// Узнать код PIECE_STATUS_EXIST
//odp.Add("READY_TO_SHIP",)//Узнать код PIECE_READY_TO_SHIP
}
else if (action == TPieceAction.paDeAssign && forShipping == TForShipping.YESShipped)
{
str += "AND PIECE_EXIT_TYPE= :PIECE_EXIT_TYPE";
odp.Add("PIECE_ID", ship.pieceId);
//odp.Add("STATUS",);// Узнать код PIECE_STATUS_EXIST
//odp.Add("PIECE_EXIT_TYPE",)//Узнать код PIECE_EXIT_TYPE
}
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
pieceNum = connection.Query <int>(str, odp).AsList();
}
if (forShipping == TForShipping.NOShipped)
{
foreach (int piece in pieceNum)
{
//SetPieceAssignExternalBOL(QryTemp.FieldByName('PIECE_NUM_ID').AsInteger, Action, Qry.FieldByName('BOL_ID').AsString);
}
}
else if (forShipping == TForShipping.YESShipped)
{
foreach (int piece in pieceNum)
{
//SetPieceShippedFromExternal(QryTemp.FieldByName('PIECE_NUM_ID').AsInteger, Action, Qry.FieldByName('BOL_ID').AsString);
}
}
checkResult.isOK = true;
checkResult.data = $"В таблице {l4l3unterfacetable} поле MSG_COUNTER:{l4MsgInfo.msgCounter} Ошибок нет.";
return(checkResult);
}
/// <summary>
/// Получение ИД заказчика
/// </summary>
/// <param name="bisValid"></param>
/// <param name="l4MsgInfo">Модель таблицы L4L3Event для обработки содержимого</param>
/// <returns>ИД заказчика</returns>
public int CheckValue(bool bisValid, TL4MsgInfo l4MsgInfo)
{
bisValid = false;
int res = 0;
List <int> values;
string str = "SELECT CUSTOMER_ID AS CUSTOMER_ID " +
"FROM L4_L3_SO_HEADER " +
"WHERE MSG_COUNTER=:MSG_COUNTER";
OracleDynamicParameters odp = new OracleDynamicParameters();
odp.Add("MSG_COUNTER", l4MsgInfo.msgCounter);
using (OracleConnection connection = GetConnection())
{
LogSqlWithParams(str, odp);
values = connection.Query <int>(str, odp).AsList();
}
if (values.Count > 0)
{
foreach (int val in values)
{
if (val != 0)
{
int count = -1;
str = "SELECT COUNT(*) AS COUNTER " +
"FROM CUSTOMER_CATALOG " +
"WHERE CUSTOMER_DESCR_ID=:CUSTOMER_DESCR_ID";
odp = new OracleDynamicParameters();
odp.Add("CUSTOMER_DESCR_ID", val);
using (OracleConnection connection = GetConnection())
{
LogSqlWithParams(str, odp);
count = connection.ExecuteScalar <int>(str, odp);
}
if (count > 0)
{
bisValid = true;
res = val;
}
else
{
bisValid = false;
break;
}
}
}
}
if (!bisValid)
{
logger.Error($"Value L4_L3_SO_HEADER.CUSTOMER_ID is not valid");
}
return(res);
}
/// <summary>
/// Обновление статуса сообщения
/// </summary>
/// <param name="l4MsgInfo">Модель таблицы L4L3Event для обработки кода</param>
private void UpdateMsgStatus(TL4MsgInfo l4MsgInfo, OracleTransaction transaction)
{
OracleDynamicParameters odp = new OracleDynamicParameters();
string str = "UPDATE L4_L3_EVENT SET MSG_STATUS = :P_MSG_STATUS, MSG_REMARK = :P_MSG_REMARK WHERE MSG_COUNTER = :P_MSG_COUNTER ";
odp.Add("P_MSG_STATUS", l4MsgInfo.msgReport.status);
odp.Add("P_MSG_REMARK", l4MsgInfo.msgReport.remark);
odp.Add("P_MSG_COUNTER", l4MsgInfo.msgCounter);
using (OracleConnection connection = GetConnection())
{
connection.Execute(str, odp, transaction);
}
}
/// <summary>
/// Обработчик кода перемещения материала
/// </summary>
/// <param name="l4MsgInfo">Модель L4L3Event для обработки кода</param>
/// <returns>Результат обработки</returns>
public TCheckResult L4L3MaterialMovement(TL4MsgInfo l4MsgInfo)
{
L4L3InterfaceServiceGlobalCheck global = new L4L3InterfaceServiceGlobalCheck();
TCheckResult checkResult = global.InitResultWithFalse();
List <L4L3RmAndMatCat> l4L3Rms = new List <L4L3RmAndMatCat>();
OracleDynamicParameters odp = new OracleDynamicParameters();
string str = "select l4.material_id as sap_code, " +
"M.MATERIAL_CODE," +
"m.material_name," +
"m.actual_qty l3_qty," +
"l4.material_amount*1000 as material_amount," +
"case" +
"when (m.actual_qty < l4.material_amount*1000) then" +
"(-1)*(m.actual_qty - l4.material_amount*1000)" +
"else" +
"(m.actual_qty - l4.material_amount*10000)" +
"end as movement_qty" +
"l4.movement_datetime" +
"from L4_L3_RAW_MATERIAL l4," +
"mat_catalog m " +
"where trim(L4.MATERIAL_ID) = trim(M.MATERIAL_CODE_L4) " +
"and l4.msg_counter = :Counter";
odp.Add("Counter", l4MsgInfo.msgCounter);
using (OracleConnection connection = GetConnection())
{
l4L3Rms = connection.Query <L4L3RmAndMatCat>(str, odp).AsList();
}
if (l4L3Rms.Count == 0)
{
l4MsgInfo.msgReport.status = L4L3InterfaceServiceConst.MSG_STATUS_ERROR;
l4MsgInfo.msgReport.remark = "Код материала не найдена в БД МЕТ2000";
}
else
{
foreach (L4L3RmAndMatCat l4L3Rm in l4L3Rms)
{
if (InsertNewMovement(l4L3Rm.materialCode, "MT_RECONCILIATION", l4L3Rm.movementdatetime, l4L3Rm.movmetnqty, 1123, 0, "Корректировка остатков от SAP"))
{
check.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_SUCCESS, "Материал перемещен");
}
else
{
check.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_ERROR, "Ошибка добавления материала");
}
}
}
return(checkResult);
}
/// <summary>
/// Обновление кода операции
/// </summary>
/// <param name="l4MsgInfo">Модель таблицы L4L3Event для обработки кода</param>
/// <returns>
/// Измененное сообщение для дальнейшей обработки
/// </returns>
public TL4MsgInfo CheckUpdateOPCODE(TL4MsgInfo l4MsgInfo)
{
TL4MsgInfo tl4 = l4MsgInfo;
int count;
string sqlstr = $"select count(*) as counters " +
$"from l4_l3_event lle_actual, " +
$"l4_l3_event lle_before, " +
$"l4_l3_so_header llsh_actual, " +
$"l4_l3_so_header llsh_before, " +
$"(select lle_actual.msg_counter, " +
$"count(*) as counters " +
$"from l4_l3_event lle_actual, " +
$"l4_l3_event lle_before, " +
$"l4_l3_so_header llsh_actual, " +
$"l4_l3_so_header llsh_before " +
$"where lle_actual.msg_counter = llsh_actual.msg_counter " +
$"and lle_before.msg_counter = llsh_before.msg_counter " +
$"and llsh_before.SO_ID = llsh_actual.so_id " +
$"and lle_before.op_code = 1 " +
$"and lle_actual.op_code = 2 " +
$"and lle_before.msg_status = 2 " +
$"and lle_actual.msg_counter = {tl4.msgCounter} " +
$"group by lle_actual.msg_counter) yetinserted " +
$"where lle_actual.msg_counter = llsh_actual.msg_counter " +
$"and lle_before.msg_counter = llsh_before.msg_counter " +
$"and llsh_before.SO_ID = llsh_actual.so_id " +
$"and lle_before.op_code = 1 " +
$"and lle_actual.op_code = 2 " +
$"and lle_before.msg_status = -1 " +
$"and lle_actual.msg_counter = {tl4.msgCounter} " +
$"and yetinserted.msg_counter = lle_actual.msg_counter " +
$"and yetinserted.counters = 0 ";
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
count = connection.ExecuteScalar <int>(sqlstr, null);
}
if (count > 0)
{
sqlstr = $"update l4_l3_event set op_code = 1 where msg_counter = {tl4.msgCounter}";
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
connection.Execute(sqlstr, null);
}
tl4.opCode = 1;
}
return(tl4);
}
/// <summary>
/// Получение списка данных из таблицы L4_L3_SHIPPING
/// </summary>
/// <param name="l4MsgInfo"></param>
/// <returns></returns>
public List <L4L3Shipping> GetListData(TL4MsgInfo l4MsgInfo)
{
List <L4L3Shipping> lship;
OracleDynamicParameters odp = new OracleDynamicParameters();
string str = "SELECT * FROM L4_L3_SHIPPING WHERE MSG_COUNTER = :P_MSG_COUNTER";
odp.Add("P_MSG_COUNTER", l4MsgInfo.msgCounter);
using (OracleConnection connection = GetConnection())
{
lship = connection.Query <L4L3Shipping>(str, odp).AsList();
}
if (lship == null)
{
logger.Error($"Нет данных в таблице L4_L3_SHIPPING, для Msg_Counter: {l4MsgInfo.msgCounter}");
}
return(lship);
}
/// <summary>
/// Получение таблицы на обработку
/// </summary>
/// <param name="l4MsgInfo">Модель таблицы L4L3Event для обработки кода</param>
/// <returns>Модель таблицы L4L3Customer</returns>
public L4L3Customer GetData(TL4MsgInfo l4MsgInfo)
{
L4L3Customer customer = new L4L3Customer();
OracleDynamicParameters odp = new OracleDynamicParameters();
string str = "SELECT * FROM L4_L3_CUSTOMER WHERE MSG_COUNTER = :P_MSG_COUNTER";
odp.Add("P_MSG_COUNTER", l4MsgInfo.msgCounter);
using (OracleConnection connection = GetConnection())
{
customer = connection.QueryFirstOrDefault <L4L3Customer>(str, odp);
}
if (customer == null)
{
logger.Error($"Нет данных в таблице L4_L3_CUSTOMER, для Msg_Counter: {l4MsgInfo.msgCounter}");
}
return(customer);
}
/// <summary>
/// Блокировка процесса обработки
/// </summary>
/// <param name="l4MsgInfo">Модель таблицы L4L3Event для обработки кода</param>
/// <param name="serRSer">Блокироват/Разблокировать</param>
public void BlockForProcess(TL4MsgInfo l4MsgInfo, bool serRSer)
{
string sqlstr = "";
OracleDynamicParameters odp = new OracleDynamicParameters();
if (serRSer)
{
sqlstr = "Select UPDATE_BLOCK_ORDER(1,:msg_counter) FROM DUAL";
}
else
{
sqlstr = "Select UPDATE_BLOCK_ORDER(0,:msg_counter) FROM DUAL";
}
odp.Add("msg_counter", l4MsgInfo.msgCounter);
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
connection.Execute(sqlstr, odp);
}
}
/// <summary>
/// Обработчик события для кода заказчиков
/// </summary>
/// <param name="l4MsgInfo">Модель таблицы L4L3Event для обработки кода</param>
/// <returns>Результат обработки</returns>
public TCheckResult CustomerMng(TL4MsgInfo l4MsgInfo)
{
L4L3Customer customer = customerRepo.GetData(l4MsgInfo);
TCheckResult checkres = new TCheckResult();
checkres.rejType = 0;
TL4EngineInterfaceMngRepo engInterf = new TL4EngineInterfaceMngRepo(customer, l4MsgInfo);
AddressEngine addressEngine = new AddressEngine(engInterf);
CCatalEngine catalEngine = new CCatalEngine(engInterf);
CCreditEngine creditEngine = new CCreditEngine(engInterf);
bool res = true;
float el4CustomerId;
string l4CustomerId = "", customerIdForL4m = "", sAddressIdBillTo = "", l4CreateUserId = "", l4ModUserId = "";
logger.Info("Init 'CustomerMng' function");
l4MsgInfo.msgReport.status = L4L3InterfaceServiceConst.MSG_STATUS_SUCCESS;
try
{
l4CustomerId = customer.customerId.ToString();
el4CustomerId = customer.customerId;
l4CreateUserId = engInterf.GetCreateUserId();
customerIdForL4m = l4CustomerId;
logger.Info("'CustomerIdForL4':" + customerIdForL4m + "'l4CustomerId':" + l4CustomerId);
if (l4MsgInfo.opCode == L4L3InterfaceServiceConst.OP_CODE_DEL)
{
if (catalEngine.LoadData(l4CustomerId) > 0)
{
if (catalEngine.IsCustomerDeletable(catalEngine.GetCustomerID()))
{
catalEngine.DeleteCustomer(catalEngine.GetCustomerID());
}
}
else
{
checkres.isOK = engInterf.NotifyErrorMessage($"Заказчик {l4CustomerId} не существует");
logger.Error($"Заказчик {l4CustomerId} не существует");
}
}
else
{
customerIdForL4m = engInterf.GetCreateUserId();
if (l4CustomerId == "")
{
res = false;
}
l4ModUserId = engInterf.GetModUserId();
if (l4ModUserId == "")
{
res = false;
}
if (res)
{
if (catalEngine.LoadData(l4CustomerId) > 0)
{
if (addressEngine.LoadData(catalEngine.GetAddressIdCatalog()) != 1)
{
checkres.isOK = engInterf.NotifyErrorMessage($"Ошибка при обработке заказчика - Фатальная ошибка - поле ADDRESS_ID={catalEngine.GetAddressIdCatalog().ToString() } не найдено");
logger.Error($"Ошибка при обработке заказчика - Фатальная ошибка - поле ADDRESS_ID ={ catalEngine.GetAddressIdCatalog().ToString() } не найдено");
res = false;
}
if (res)
{
catalEngine.SetCustomerDescrId(l4CustomerId);
}
}
if (creditEngine.LoadData(catalEngine.GetCustomerID()) > 0)
{
if (addressEngine.LoadData(creditEngine.GetAddressIdBillTo()) != 1)
{
try
{
sAddressIdBillTo = creditEngine.GetAddressIdBillTo().ToString();
}
catch
{
sAddressIdBillTo = "";
}
checkres.isOK = engInterf.NotifyErrorMessage($"Ошибка при обработке заказчика - Фатальная ошибка - поле ADDRESS_ID={sAddressIdBillTo} не найдено");
res = false;
}
}
}
if (res)
{
res = FillAddressEngine(customer, addressEngine, l4ModUserId);
}
logger.Info("'CustomerMng - Load Customer Catalog data'");
if (res)
{
catalEngine.SetCustomerDescrId(l4CustomerId);
catalEngine.SetAddressIdCatalog(addressEngine.GetAddressId().ToString());
catalEngine.SetInternalCustomerFlag(customer.internalCustomerFlag);
catalEngine.SetInquiryValidityDays(30);
catalEngine.SetCustomerCurrencyCode(customer.customerCurrencyCode);
catalEngine.SetClassificationType(CheckClassificationType(customer.customerClassificationType));
catalEngine.SetWeightUnit("<NULL>");
if (customer.customerName.Length > 80)
{
catalEngine.SetCustomerShortName(customer.customerName.Substring(0, 80));
}
else
{
catalEngine.SetCustomerShortName(customer.customerName);
}
catalEngine.SetCreationUserId(l4CreateUserId);
if (customer.inn.Length > 40)
{
catalEngine.SetInn(customer.inn.Substring(0, 40));
}
else
{
catalEngine.SetInn(customer.inn);
}
if (customer.kpp.Length > 40)
{
catalEngine.SetKpp(customer.kpp.Substring(0, 40));
}
else
{
catalEngine.SetKpp(customer.kpp);
}
if (customer.rwstationCode.Length > 40)
{
catalEngine.SetRwStationCode(customer.rwstationCode.Substring(0, 40));
}
else
{
catalEngine.SetRwStationCode(customer.rwstationCode);
}
if (customer.region.Length > 40)
{
catalEngine.SetRegion(customer.region.Substring(0, 40));
}
else
{
catalEngine.SetRegion(customer.region);
}
catalEngine.SetLevel4CustomerId(customerIdForL4m);
DateTime date = new DateTime();
if (customer.vailityFlag.ToString() == "Y")
{
if (catalEngine.GetExpirationDate() != date)
{
catalEngine.SetExpirationDate(date);
}
}
else
{
if (catalEngine.GetExpirationDate() == date)
{
catalEngine.SetExpirationDate(DateTime.Now);
}
}
catalEngine.ForceModUserDatetime(l4ModUserId);
res = catalEngine.SaveData();
}
logger.Info("'CustomerMng - Load Customer Catalog Credit Data'");
if (res)
{
creditEngine.SetCustomerID(GetCustIDFromDescr(l4CustomerId));
creditEngine.SetAddressIdBillTo(addressEngine.GetAddressId());
creditEngine.SetCreditStatus(1);
creditEngine.ForceModUserDatetime(l4ModUserId);
creditEngine.SetInvoiceType("Y");
creditEngine.SetDirectPaymnt("Y");
creditEngine.SetCustomerCode(Convert.ToInt32(customer.customerCurrencyCode));
res = creditEngine.SaveData();
}
if (!res && l4MsgInfo.msgReport.status == L4L3InterfaceServiceConst.MSG_STATUS_SUCCESS)
{
engInterf.NotifyErrorMessage("CistomerMng - Unknown fatal error.");
checkres.data = "CistomerMng - Unknown fatal error.";
checkres.isOK = false;
}
if (res && l4MsgInfo.msgReport.status == L4L3InterfaceServiceConst.MSG_STATUS_SUCCESS)
{
checkres.data = "CistomerMng - SUCCESS.";
checkres.isOK = true;
}
}
logger.Info("End ''CustomerMng'' function");
return(checkres);
}
catch { return(checkres); }
}
/// <summary>
/// Создание и заполнение модели таблицы SO_HEADER
/// </summary>
/// <param name="l4l3soheader">Модель таблицы L4L3SoHeader для обработки</param>
/// <param name="l4MsgInfo">Модель таблицы L4L3Event для обработки содержимого кода</param>
/// <param name="iShipToCode">Код отгрузки</param>
/// <param name="bVerifyData">Пометка об актуальности данных</param>
/// <param name="bisUpdate">Пометка об обновлении данных</param>
/// <returns></returns>
public TSoHeader Create(L4L3SoHeader l4l3soheader, TL4MsgInfo l4MsgInfo, int iShipToCode, bool bVerifyData, bool bisUpdate = false)
{
TL4EngineInterfaceMngRepo mngRepo = new TL4EngineInterfaceMngRepo(l4MsgInfo);
bool bIsValid = true;
try
{
soHeader.m_iLinesCount = 0;
if (bIsValid)
{
soHeader.m_iCustSoldDescrID = CheckValue(bIsValid, l4MsgInfo);
}
if (!ExistCustomer(soHeader.m_iCustSoldDescrID.ToString()))
{
mngRepo.NotifyErrorMessage($"Заказчик САП {l4l3soheader.customerId} неверен или срок валидности закончился");
bIsValid = false;
}
if (bIsValid)
{
if (oneToSeveralOrderFromSap == 'Y')
{
soHeader.m_sSoDescrID = l4l3soheader.soID + "_" + m_strSO_Line_Id_MET;
}
else
{
soHeader.m_sSoDescrID = l4l3soheader.soID;
}
if (l4l3soheader.customerPo.Length > 0)
{
soHeader.m_strCustPO = l4l3soheader.customerPo;
}
else
{
mngRepo.NotifyErrorMessage("'CUSTOMER_PO' поле не может быть пустым");
bIsValid = false;
}
if (l4l3soheader.customerPoDate.ToString().Length > 0)
{
soHeader.m_dCustPODate = l4l3soheader.customerPoDate;
}
else
{
soHeader.m_dCustPODate = l4l3soheader.insertDate;
}
if (l4l3soheader.inquiryRefNumber.Length > 0)
{
soHeader.m_strInquirtNumber = l4l3soheader.inquiryRefNumber;
}
else
{
soHeader.m_strInquirtNumber = soHeader.m_strCustPO;
}
if (l4l3soheader.inquiryRefDate.ToString().Length > 0)
{
soHeader.m_dInquiryDate = l4l3soheader.inquiryRefDate;
}
else
{
soHeader.m_dInquiryDate = soHeader.m_dCustPODate;
}
}
if (bIsValid)
{
soHeader.m_iInsertUser = mngRepo.GetCreateUserId();
if (soHeader.m_iInsertUser == "")
{
bIsValid = false;
}
}
if (bIsValid)
{
if (l4l3soheader.insertDate.ToString().Length > 0)
{
soHeader.m_dInsertDate = l4l3soheader.insertDate;
}
}
if (bIsValid)
{
soHeader.m_iUpdateUser = mngRepo.GetModUserId();
if (soHeader.m_iUpdateUser == "")
{
bIsValid = false;
}
}
if (bIsValid)
{
if (l4MsgInfo.opCode > 0)
{
soHeader.m_iOpCode = l4MsgInfo.opCode;
}
}
if (bIsValid)
{
switch (som.DecodeContractType(soHeader.m_sSoDescrID, l4l3soheader.customerId.ToString()))
{
case TContractType.coInternal:
if (som.IsCustomerInternal(soHeader.m_iCustSoldDescrID))
{
mngRepo.NotifyErrorMessage("Заказ обозначен как внутренний, но имеет внешнего заказчика");
bIsValid = false;
}
break;
}
}
if (bIsValid)
{
List <LinesCom> lines = new List <LinesCom>();
string str = "SELECT L4SOL.* " +
"L4EVE.MSG_STATUS " +
"L4EVE.MSG_REMARK, " +
"L4SOL.SO_LINE_ID/10 AS SO_LINE_MET, " +
"PTC.PRODUCT_TYPE AS PROD_MET, " +
"L4EVE.OP_CODE, " +
"L4SOL.LINE_NOTE " +
"FROM L4_L3_SO_LINE L4SOL, " +
"PRODUCT_TYPE_CATALOGUE PTC " +
"L4_L3_EVENT L4EVE " +
$"WHERE L4SOL.MSG_COUNTER = :{l4MsgInfo.msgCounter}" +
"AND L4SOL.MSG_COUNTER = L4EVE.MSG_COUNTER " +
"AND ptc.SAP_CODE = l4sol.PRODUCT_TYPE";
if (oneToSeveralOrderFromSap == 'Y')
{
str += $"AND L4SOL.SO_LINE_ID = '{m_strSO_Line_Id_Params}'";
}
str += "ORDER BY L4SOL.SO_LINE_ID ";
using (OracleConnection connection = GetConnection())
{
lines = connection.Query <LinesCom>(str, null).AsList();
}
foreach (LinesCom line in lines)
{
templine = lineRepo.Create(line, l4MsgInfo, soHeader.m_iCustSoldDescrID, iShipToCode, bisUpdate);
templine.m_strDescription = line.lineNote;
if (!bVerifyData)
{
mngRepo.NotifyErrorMessage($"Ошибка в строке {templine.m_iSoLineID} {templine.m_L4MsgInfoLine.tL4MsgInfo.msgReport.remark}");
templine.m_L4MsgInfoLine.tL4MsgInfo.msgReport.status = L4L3InterfaceServiceConst.MSG_STATUS_ERROR;
Add(templine);
break;
}
Add(templine);
}
}
}
catch (Exception e) { logger.Error($"Ошибка создания SoHeader: {e.Message}"); }
return(soHeader);
}
/// <summary>
/// Конструктор для получения модели событий
/// </summary>
/// <param name="l4MsgInfo">Модель таблицы L4L3Event для обработки кода</param>
public TL4EngineInterfaceMngRepo(TL4MsgInfo l4MsgInfo)
{
this.l4MsgInfo = l4MsgInfo;
}
/// <summary>
/// Основная проверка отгрузки
/// </summary>
/// <param name="ship">Модель таблицы L4_L3_SHIPPING для обработки</param>
/// <param name="l4MsgInfo">Модель таблицы L4L3Event</param>
/// <returns>Результат работы</returns>
public TCheckResult ShippingGeneralCheck(List <L4L3Shipping> ship, TL4MsgInfo l4MsgInfo)
{
TCheckResult result = check.InitResultWithFalse();
result.isOK = true;
bool firstloop = true;
string bol_idUsedToCheck = "", strPieceId = "", strError = "";
int bol_status = -1, bol_statusUserToCheck = -1;
if (ship == null)
{
check.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_ERROR, $"Запись не найдена Msg_counter={l4MsgInfo.msgCounter}");
return(result);
}
foreach (L4L3Shipping shp in ship)
{
if (firstloop)
{
bol_statusUserToCheck = shp.bolStatus;
bol_idUsedToCheck = shp.bolId;
}
else
{
if (bol_idUsedToCheck != shp.bolId)
{
strError = $"Таблица L4_L3_SHIPPING MSG_COUNTER: {l4MsgInfo.msgCounter} значение Накладной должно совпадать для каждой записи";
result.isOK = false;
result.data = strError;
check.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_ERROR, strError);
break;
}
}
bol_status = shp.bolStatus;
if (bol_statusUserToCheck != bol_status)
{
strError = $"Таблица L4_L3_SHIPPING MSG_COUNTER: {l4MsgInfo.msgCounter} значение поля BOL_STATUS должно быть одинаковым для каждой записи";
result.isOK = false;
result.data = strError;
check.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_ERROR, strError);
break;
}
if (l4MsgInfo.opCode == L4L3InterfaceServiceConst.OP_CODE_NEW)
{
if (!(bol_status == L4L3InterfaceServiceConst.BOL_NOT_SENT || bol_status == L4L3InterfaceServiceConst.BOL_SENT))
{
strPieceId = shp.pieceId;
strError = $"Таблица L4_L3_SHIPPING MSG_COUNTER: {l4MsgInfo.msgCounter} значение PIECE_ID: {shp.pieceId} для BOL_STATUS = {bol_status} неверно";
result.isOK = false;
result.data = strError;
check.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_ERROR, strError);
break;
}
}
else if (l4MsgInfo.opCode == L4L3InterfaceServiceConst.OP_CODE_DEL)
{
if (bol_status != L4L3InterfaceServiceConst.BOL_SENT)
{
strPieceId = shp.pieceId;
strError = $"Таблица L4_L3_SHIPPING MSG_COUNTER: {l4MsgInfo.msgCounter} значение PIECE_ID: {shp.pieceId} для BOL_STATUS = {bol_status} неверно";
result.isOK = false;
result.data = strError;
check.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_ERROR, strError);
break;
}
}
else if (l4MsgInfo.opCode == L4L3InterfaceServiceConst.OP_CODE_UPD)
{
if (!(bol_status == L4L3InterfaceServiceConst.BOL_NOT_SENT || bol_status == L4L3InterfaceServiceConst.BOL_SENT))
{
strPieceId = shp.pieceId;
strError = $"Таблица L4_L3_SHIPPING MSG_COUNTER: {l4MsgInfo.msgCounter} значение PIECE_ID: {shp.pieceId} для BOL_STATUS = {bol_status} неверно";
result.isOK = false;
result.data = strError;
check.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_ERROR, strError);
break;
}
}
firstloop = false;
}
return(result);
}
/// <summary>
/// Автозакрытие заказа
/// </summary>
public void CloseOrder()
{
OracleDynamicParameters odp = new OracleDynamicParameters();
TL4MsgInfo l4MsgInfo = new TL4MsgInfo();
List <AutoClose> close = new List <AutoClose>();
string str1 = "UPDATE SALES_ORDER_LINE " +
"SET SO_LINE_STATUS = 4 " +
" , MOD_DATETIME = sysdate" +
", MANUAL_CLOSE_ORDER = 'Y' " +
"WHERE SO_ID = :P_SO_ID " +
"AND SO_LINE_ID = :P_SO_LINE_ID";
try
{
string str2 = "SELECT l2.*, " +
"sol.so_id as met_so_id, " +
"sol.so_line_id as met_so_line_id " +
"FROM ( select l.MSG_DATETIME, l.MSG_COUNTER, sh5.so_id , sh5.so_line_id ,sh5.ORDER_STATUS, sh5.SO_TYPE_CODE " +
"FROM L4_L3_EVENT l join(select shm.MSG_COUNTER, shm.SO_ID , shm.so_line_id, shm.ORDER_STATUS, shm.SO_TYPE_CODE " +
"FROM ( SELECT MAX(sh.MSG_COUNTER) as MSG_COUNTER , sh.SO_ID , sl.so_line_id , sl.ORDER_STATUS, sl.SO_TYPE_CODE " +
"FROM L4_L3_SO_HEADER sh JOIN L4_L3_SO_LINE sl ON sh.MSG_COUNTER =sl.MSG_COUNTER " +
"WHERE sl.ORDER_STATUS =40 " +
"GROUP BY sh.SO_ID , sl.so_line_id, sl.ORDER_STATUS, sl.SO_TYPE_CODE) shm ) sh5 on sh5.MSG_COUNTER=l.MSG_COUNTER " +
" WHERE l.MSG_STATUS = 1 " +
"AND l.msg_id = 4301 ) l2 join SALES_ORDER_LINE sol on sol.SO_DESCR_ID = l2.so_id or sol.SO_DESCR_ID = l2.so_id||'_'||to_number(l2.so_line_id)/10 " +
"WHERE sol.SO_LINE_STATUS = 3 " +
"AND l2.MSG_DATETIME > SYSDATE - 7";
using (OracleConnection connection = GetConnection())
{
close = connection.Query <AutoClose>(str2, null).AsList();
}
if (close != null)
{
foreach (AutoClose auto in close)
{
using (OracleConnection connection = GetConnection())
{
using (var transaction = connection.BeginTransaction())
{
try
{
odp.Add("P_SO_ID", auto.metSoId);
odp.Add("P_SO_LINE_ID", auto.metSoLineId);
connection.Execute(str1, odp, transaction);
transaction.Commit();
l4MsgInfo.msgCounter = auto.msgCounter;
l4MsgInfo.msgReport.status = 2;
l4MsgInfo.msgReport.remark = $"Заказ {auto.soId}/{auto.soLineId} Закрыт автоматически";
UpdateMsgStatus(l4MsgInfo, transaction);
}
catch (Exception e)
{
transaction.Rollback();
l4MsgInfo.msgCounter = auto.msgCounter;
l4MsgInfo.msgReport.status = -1;
l4MsgInfo.msgReport.remark = $"Заказ {auto.soId}/{auto.soLineId} {e.Message}";
UpdateMsgStatus(l4MsgInfo, transaction);
}
}
}
}
}
}
catch
{
logger.Error("Ошибка выполнения автоматического закрытия заказа");
}
}
/// <summary>
/// Обработчик заказа
/// </summary>
/// <param name="l4MsgInfo">Модель таблицы L4L3Event для обработки кода</param>
/// <returns>Результат обработки</returns>
public TCheckResult SalesOrderMng(TL4MsgInfo l4MsgInfo)
{
TCheckResult checkResult = new TCheckResult();
checkResult.isOK = false;
tSo = new TSoHeaderRepo();
L4L3SoHeaderRepo header = new L4L3SoHeaderRepo();
L4L3SoHeader soHeader = header.GetData(l4MsgInfo);
BlockForProcess(l4MsgInfo, true);
string charval;
string sqlstr = "SELECT CHAR_VALUE FROM AUX_CONSTANT WHERE CONSTANT_ID='ONE_TO_SEVERAL_ORDER_FROM_SAP'";
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
charval = connection.QueryFirstOrDefault <string>(sqlstr, null);
}
if (charval != null)
{
onetoSeveralirderFromSap = charval;
}
else
{
onetoSeveralirderFromSap = "N";
}
if (onetoSeveralirderFromSap == "Y")
{
List <l4sol> l4s = new List <l4sol>();
sqlstr = "SELECT L4SOL.SO_LINE_ID, L4SOL.SO_LINE_ID/10 as MET_SO_LINE_ID " +
"FROM L4_L3_SO_LINE L4SOL WHERE L4SOL.MSG_COUNTER = :MSG_COUNTER";
if (m_strSo_Lines_For_Where != "")
{
sqlstr += $"AND L4SOL.SO_LINE_ID IN {m_strSo_Lines_For_Where}";
}
sqlstr += "ORDER BY L4SOL.SO_LINE_ID";
OracleDynamicParameters odp = new OracleDynamicParameters();
odp.Add("MSG_COUNTER", l4MsgInfo.msgCounter);
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
l4s = connection.Query <l4sol>(sqlstr, null).AsList();
}
if (l4s != null)
{
foreach (l4sol kSol in l4s)
{
if (!OrderCanBeProcess(soHeader.soID, l4MsgInfo.opCode, ""))
{
l4MsgInfo.msgReport.status = L4L3InterfaceServiceConst.MSG_STATUS_ERROR;
l4MsgInfo.msgReport.remark = "";
}
else if (!OrderCanBeProcess($"{soHeader.soID}_{kSol.metSoLineId}", l4MsgInfo.opCode, ""))
{
l4MsgInfo.msgReport.status = L4L3InterfaceServiceConst.MSG_STATUS_ERROR;
l4MsgInfo.msgReport.remark = "";
}
else
{
m_strSO_Line_Id_Params = kSol.soLineId;
m_strSO_Line_Id_MET = kSol.metSoLineId;
tSo.m_strSO_Line_Id_MET = kSol.metSoLineId;
tSo.m_strSO_Line_Id_Params = kSol.soLineId;
l4MsgInfo.msgReport.status = L4L3InterfaceServiceConst.MSG_STATUS_SUCCESS;
l4MsgInfo.msgReport.remark = "";
l4MsgInfo = CheckUpdateOPCODE(l4MsgInfo);
switch (l4MsgInfo.opCode)
{
case L4L3InterfaceServiceConst.OP_CODE_NEW:
CreateNewOrder(soHeader, l4MsgInfo);
break;
case L4L3InterfaceServiceConst.OP_CODE_DEL:
UpdateOrder(soHeader, l4MsgInfo, true);
break;
case L4L3InterfaceServiceConst.OP_CODE_UPD:
UpdateOrder(soHeader, l4MsgInfo);
break;
default:
l4MsgInfo.msgReport.status = L4L3InterfaceServiceConst.MSG_STATUS_ERROR;
l4MsgInfo.msgReport.remark = $"{l4MsgInfo.opCode} неверный код операции";
break;
}
}
}
}
checkResult.isOK = true;
return(checkResult);
}
else
{
if (!OrderCanBeProcess(soHeader.soID, l4MsgInfo.opCode, ""))
{
l4MsgInfo.msgReport.status = L4L3InterfaceServiceConst.MSG_STATUS_ERROR;
l4MsgInfo.msgReport.remark = "Заказ уже принимался по схеме с разбиением на несколько заказов.";
return(checkResult);
}
CheckUpdateOPCODE(l4MsgInfo);
switch (l4MsgInfo.opCode)
{
case L4L3InterfaceServiceConst.OP_CODE_NEW:
CreateNewOrder(soHeader, l4MsgInfo);
break;
case L4L3InterfaceServiceConst.OP_CODE_DEL:
UpdateOrder(soHeader, l4MsgInfo, true);
break;
case L4L3InterfaceServiceConst.OP_CODE_UPD:
UpdateOrder(soHeader, l4MsgInfo);
break;
default:
l4MsgInfo.msgReport.status = L4L3InterfaceServiceConst.MSG_STATUS_ERROR;
l4MsgInfo.msgReport.remark = $"{l4MsgInfo.opCode} неверный код операции";
break;
}
BlockForProcess(l4MsgInfo, false);
checkResult.isOK = true;
return(checkResult);
}
}
/// <summary>
/// Добавление деталей к заказу
/// </summary>
/// <param name="l4MsgInfo">Модель таблицы L4L3Event для обработки кода</param>
public void AddVsw_detailsToOrder(TL4MsgInfo l4MsgInfo)
{
string vsw_detail, soDescID, soIdv;
OracleDynamicParameters odp = new OracleDynamicParameters();
try
{
string sqlstr = "SELECT case when L4SHIPTO.VSW_DETAILS is not null and length(L4SHIPTO.VSW_DETAILS) =1 then 'ТЭСЦ-'||L4SHIPTO.VSW_DETAILS else 'не_ВМЗ' end AS VSW_DETAILS " +
"FROM L4_L3_SO_LINE_SHIPTO L4SHIPTO " +
"WHERE L4SHIPTO.MSG_COUNTER = :P_MSG_COUNTER";
odp.Add("P_MSG_COUNTER", l4MsgInfo.msgCounter);
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
vsw_detail = connection.QueryFirstOrDefault <string>(sqlstr, odp);
}
if (vsw_detail == null)
{
vsw_detail = "не_ВМЗ";
}
sqlstr = "SELECT SO_ID FROM L4_L3_SO_HEADER WHERE MSG_COUNTER = :P_MSG_COUNTER";
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
soDescID = connection.QueryFirstOrDefault <string>(sqlstr, odp);
}
if (soDescID == null)
{
soDescID = "-9999";
}
odp = null;
if (onetoSeveralirderFromSap == "Y")
{
sqlstr = $"SELECT SO_ID FROM SALES_ORDER_HEADER WHERE SO_DESCR_ID = '{soDescID}_{m_strSO_Line_Id_MET}' ";
}
else
{
sqlstr = $"SELECT SO_ID FROM SALES_ORDER_HEADER WHERE SO_DESCR_ID = '{soDescID}'";
}
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
soIdv = connection.QueryFirstOrDefault <string>(sqlstr, odp);
}
int soIDV;
if (soIdv != null)
{
Int32.TryParse(soIdv, out soIDV);
}
else
{
soIDV = -9999;
}
sqlstr = "SELECT SO_LINE_ID FROM SALES_ORDER_LINE WHERE SO_ID=:P_SO_ID";
odp.Add("P_SO_ID", soIDV);
List <string> lines = new List <string>();
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
lines = connection.Query <string>(sqlstr, odp).AsList();
}
odp = null;
if (lines != null)
{
foreach (string soLineID in lines)
{
string rcrt;
sqlstr = "SELECT SO_ID FROM CONFIG_COMM_ATTRB_OF_SO_LINE " +
"WHERE SO_ID = :P_SO_ID " +
"AND SO_LINE_ID = :P_SO_LINE_ID " +
"AND ATTRB_CODE = 'VSW_DETAILS'";
odp.Add("P_SO_ID", soIDV);
odp.Add("P_SO_LINE_ID", soLineID);
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
rcrt = connection.QueryFirstOrDefault <string>(sqlstr, odp);
}
if (rcrt == null)
{
sqlstr = "INSERT INTO CONFIG_COMM_ATTRB_OF_SO_LINE(SO_ID,SO_LINE_ID,ATTRB_CODE,ATTRB_AN_VALUE) " +
$"VALUES(:P_SO_ID,:P_SO_LINE_ID,'VSW_DETAILS','{vsw_detail}')";
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
connection.Execute(sqlstr, odp);
}
}
else
{
sqlstr = $"UPDATE CONFIG_COMM_ATTRB_OF_SO_LINE SET ATTRB_AN_VALUE = '{vsw_detail}' " +
"WHERE SO_ID = :P_SO_ID " +
"AND SO_LINE_ID = :P_SO_LINE_ID" +
"AND ATTRB_CODE = 'VSW_DETAILS'";
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
connection.Execute(sqlstr, odp);
}
}
}
}
}
catch (Exception e) { logger.Error($"Ошибка при обработке vsw_detail {e.Message}"); }
}
/// <summary>
/// Требуется дальнейшая реализация
/// </summary>
/// <param name="l4MsgInfo">Модель таблицы L4L3Event для обработки кода</param>
/// <param name="tL4Engine"></param>
/// <returns></returns>
public int ManageShipTo(TL4MsgInfo l4MsgInfo, TL4EngineInterfaceMngRepo tL4Engine)
{
return(1);
}
/// <summary>
/// Распределение работы по обработчикам
/// </summary>
public void NewMessage()
{
TL4MsgInfo l4MsgInfo = new TL4MsgInfo();
OracleDynamicParameters odp = new OracleDynamicParameters();
bool isOpCodeOk;
string acceptOrderConsts = "";
string sqlstr = "SELECT CHAR_VALUE " +
"FROM AUX_CONSTANT " +
"WHERE CONSTANT_ID='ACCEPT_ORDER_IN_SRV'";
try
{
using (OracleConnection connection = GetDBConnection())
{
acceptOrderConsts = connection.ExecuteScalar <string>(sqlstr, null);
}
if (acceptOrderConsts == "")
{
acceptOrderConsts = "N";
}
List <L4L3Event> events = new List <L4L3Event>();
sqlstr = "SELECT le.* FROM L4_L3_EVENT le WHERE MSG_STATUS = 1 AND msg_id IN (4301,4303, 4304, 4305) AND le.MSG_DATETIME > SYSDATE - 7 ORDER BY MSG_COUNTER";
using (OracleConnection connection = GetConnection())
{
events = connection.Query <L4L3Event>(sqlstr, null).AsList();
}
if (events == null)
{
logger.Error("SQL запрос из таблицы L4_L3_EVENT выполнен с ошибков в ServiceWorker.GetNewMessage.");
throw new Exception();
}
foreach (L4L3Event evnt in events)
{
l4MsgInfo.msgCounter = evnt.msgCounter;
l4MsgInfo.msgId = evnt.msgId;
l4MsgInfo.msgDatetime = evnt.msgDatetime;
l4MsgInfo.opCode = evnt.opCode;
l4MsgInfo.keyString1 = evnt.keyString1;
l4MsgInfo.keyString2 = evnt.keyString2;
l4MsgInfo.keyNumber1 = evnt.keyNumber1;
l4MsgInfo.keyNumber2 = evnt.keyNumber2;
l4MsgInfo.msgReport = new TMessageResult();
l4MsgInfo.msgReport.status = 1;
l4MsgInfo.msgReport.remark = "";
logger.Info($"STARTED Event -> Table: L4_L3_EVENT, MsgCounter:{l4MsgInfo.msgCounter}");
using (OracleConnection conn = GetConnection())
{
conn.Open();
using (var transaction = conn.BeginTransaction())
{
try
{
switch (l4MsgInfo.opCode)
{
case L4L3InterfaceServiceConst.OP_CODE_NEW:
isOpCodeOk = true;
break;
case L4L3InterfaceServiceConst.OP_CODE_DEL:
isOpCodeOk = true;
break;
case L4L3InterfaceServiceConst.OP_CODE_UPD:
isOpCodeOk = true;
break;
case L4L3InterfaceServiceConst.OP_CODE_INUP:
isOpCodeOk = true;
break;
default:
isOpCodeOk = false;
l4MsgInfo = check.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_ERROR, $"OP_CODE: {l4MsgInfo.opCode} is not valid");
UpdateMsgStatus(l4MsgInfo, transaction);
break;
}
if (isOpCodeOk)
{
switch (l4MsgInfo.msgId)
{
//Запуск задачи SALES_ORDER
case L4L3InterfaceServiceConst.L4_L3_SALES_ORDER:
if (IsBlocked(evnt.msgCounter))
{
//somtask=Task.Run(() => som.SalesOrderMng(l4MsgInfo));
//somtask.Start();
//somtask.Wait();
cheker = som.SalesOrderMng(l4MsgInfo);
}
break;
//Запуск задачи CUSTOMER_CATALOG
case L4L3InterfaceServiceConst.L4_L3_CUSTOMER_CATALOG:
//ccmtask = Task.Run(() => ccm.CustomerMng(l4MsgInfo));
//ccmtask.Start();
//ccmtask.Wait();
cheker = ccm.CustomerMng(l4MsgInfo);
break;
//Запуск задачи SHIPPING
case L4L3InterfaceServiceConst.L4_L3_SHIPPING:
//shiptask = Task.Run(() => sship.ShippingMng(l4MsgInfo));
//shiptask.Start();
//shiptask.Wait();
cheker = sship.ShippingMng(l4MsgInfo);
break;
//Запуск задачи MATERIAL
case L4L3InterfaceServiceConst.L4_L3_RAW_MATERIAL:
//mattask = Task.Run(() => mat.L4L3MaterialMovement(l4MsgInfo));
//mattask.Start();
//mattask.Wait();
cheker = mat.L4L3MaterialMovement(l4MsgInfo);
break;
}
if (l4MsgInfo.msgReport.status == L4L3InterfaceServiceConst.MSG_STATUS_INSERT)
{
UpdateMsgStatus(l4MsgInfo, transaction);
}
if (cheker.isOK)
{
l4MsgInfo.msgReport.status = L4L3InterfaceServiceConst.MSG_STATUS_SUCCESS;
l4MsgInfo.msgReport.remark = cheker.data;
}
else
{
l4MsgInfo.msgReport.status = L4L3InterfaceServiceConst.MSG_STATUS_ERROR;
l4MsgInfo.msgReport.remark = cheker.data;
}
UpdateMsgStatus(l4MsgInfo, transaction);
}
logger.Info($"STOPPED Event -> Table: L4_L3_EVENT, MsgCounter:{l4MsgInfo.msgCounter}");
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
if (IsBlocked(l4MsgInfo.msgCounter))
{
BlockForProcess(l4MsgInfo, false);
}
using (OracleConnection conn1 = GetConnection())
{
using (var transaction1 = conn.BeginTransaction())
{
try
{
l4MsgInfo = check.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_ERROR, e.Message.Substring(0, 4000));
logger.Info($"STOPPED Event -> Table: L4_L3_EVENT, MsgCounter:{l4MsgInfo.msgCounter} with error");
UpdateMsgStatus(l4MsgInfo, transaction1);
transaction1.Commit();
}
catch
{
transaction1.Rollback();
if (IsBlocked(l4MsgInfo.msgCounter))
{
BlockForProcess(l4MsgInfo, false);
}
}
}
}
}
}
}
}
}
catch {
logger.Error("Ошибка выполнения обработчика событий");
}
}
/// <summary>
/// Проверка отгрузки
/// </summary>
/// <param name="ship">Набор строк таблицы L4_L3_SHIPPING для обработки</param>
/// <param name="l4MsgInfo">Модель таблицы L4L3Event</param>
/// <returns>Результат работы</returns>
public TCheckResult ShippingCheck(List <L4L3Shipping> ship, TL4MsgInfo l4MsgInfo)
{
TCheckResult result = ShippingGeneralCheck(ship, l4MsgInfo);
if (!result.isOK)
{
return(result);
}
foreach (L4L3Shipping sinship in ship)
{
if (l4MsgInfo.opCode == L4L3InterfaceServiceConst.OP_CODE_NEW && sinship.bolStatus == L4L3InterfaceServiceConst.BOL_NOT_SENT)
{
if (!L4L3ShipPieceSOCheck(sinship))
{
result.isOK = false;
result.data = $"Таблица L4_L3_SHIPPING MSG_COUNTER {l4MsgInfo.msgCounter} одна из заготовок не может быть назначена в накладную BOL: {sinship.bolId}";
check.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_ERROR, result.data);
}
}
else if (l4MsgInfo.opCode == L4L3InterfaceServiceConst.OP_CODE_UPD && sinship.bolStatus == L4L3InterfaceServiceConst.BOL_NOT_SENT)
{
if (!CheckBolExistNotShip(sinship.bolId))
{
result.isOK = false;
result.data = $"Таблица L4_L3_SHIPPING MSG_COUNTER {l4MsgInfo.msgCounter} Накладная: {sinship.bolId} не существует или отгружена";
check.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_ERROR, result.data);
}
if (!ShippingIsPieceAssignedToBOL(sinship, TForShipping.NOShipped))
{
result.isOK = false;
result.data = $"Таблица L4_L3_SHIPPING MSG_COUNTER {l4MsgInfo.msgCounter} как минимум одна из заготовок не может быть назначена в накладную BOL: {sinship.bolId}";
check.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_ERROR, result.data);
}
}
else if (l4MsgInfo.opCode == L4L3InterfaceServiceConst.OP_CODE_UPD && sinship.bolStatus == L4L3InterfaceServiceConst.BOL_SENT)
{
if (!CheckBolExistIsShip(sinship.bolId))
{
result.isOK = false;
result.data = $"Таблица L4_L3_SHIPPING MSG_COUNTER {l4MsgInfo.msgCounter} Накладная: {sinship.bolId} не существует или отгружена";
check.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_ERROR, result.data);
}
if (!ShippingIsPieceAssignedToBOL(sinship, TForShipping.YESShipped))
{
result.isOK = false;
result.data = $"Таблица L4_L3_SHIPPING MSG_COUNTER {l4MsgInfo.msgCounter} как минимум одна из заготовок не может быть назначена в накладную BOL: {sinship.bolId}";
check.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_ERROR, result.data);
}
}
else if (l4MsgInfo.opCode == L4L3InterfaceServiceConst.OP_CODE_NEW && sinship.bolStatus == L4L3InterfaceServiceConst.BOL_SENT)
{
if (!CheckBolExistIsShip(sinship.bolId))
{
result.isOK = false;
result.data = $"Таблица L4_L3_SHIPPING MSG_COUNTER {l4MsgInfo.msgCounter} Накладная: {sinship.bolId} не существует или отгружена";
check.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_ERROR, result.data);
}
if (!L4L3ShipPieceSOCheck(sinship))
{
result.isOK = false;
result.data = $"Таблица L4_L3_SHIPPING MSG_COUNTER {l4MsgInfo.msgCounter} как минимум одна из заготовок не может быть назначена в накладную BOL: {sinship.bolId}";
check.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_ERROR, result.data);
}
}
else if (l4MsgInfo.opCode == L4L3InterfaceServiceConst.OP_CODE_DEL && sinship.bolStatus == L4L3InterfaceServiceConst.BOL_SENT)
{
if (!CheckBolExistNotShip(sinship.bolId))
{
result.isOK = false;
result.data = $"Таблица L4_L3_SHIPPING MSG_COUNTER {l4MsgInfo.msgCounter} Накладная: {sinship.bolId} не существует или отгружена";
check.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_ERROR, result.data);
}
if (L4L3ShipPieceSOCheck(sinship))
{
result.isOK = false;
result.data = $"Таблица L4_L3_SHIPPING MSG_COUNTER {l4MsgInfo.msgCounter} как минимум одна из заготовок не может быть назначена в накладную BOL: {sinship.bolId}";
check.SetMsgResult(l4MsgInfo, L4L3InterfaceServiceConst.MSG_STATUS_ERROR, result.data);
}
}
}
return(result);
}
/// <summary>
/// Создание и заполнение таблицы SO_LINE
/// </summary>
/// <param name="line">Модель таблицы</param>
/// <param name="l4MsgInfo">Модель таблицы событий для обработки сообщения </param>
/// <param name="iCustomerID">ИД заказчика</param>
/// <param name="iShipToCode">Код разгрузки</param>
/// <param name="lbIsUpdate">Пометка об обновлении</param>
/// <returns>Заполненая модель SO_LINE</returns>
public TSoLine Create(LinesCom line, TL4MsgInfo l4MsgInfo, int iCustomerID, int iShipToCode, bool lbIsUpdate)
{
L4L3InterfaceUtility inter = new L4L3InterfaceUtility();
bool bVerifyData = true;
int iSysDatePeriodNumID = 0;
try
{
TL4MsgInfoLine m_L4MsgInfoLine = new TL4MsgInfoLine();
m_L4MsgInfoLine.tL4MsgInfo.msgCounter = line.msgCounter;
m_L4MsgInfoLine.tL4MsgInfo.msgReport.status = line.msgStatus;
m_L4MsgInfoLine.tL4MsgInfo.msgReport.remark = "";
newData.m_iSoLineID = line.soLineMet;
TL4EngineInterfaceMngRepo m_eimOELineInterface = new TL4EngineInterfaceMngRepo(m_L4MsgInfoLine.tL4MsgInfo);
newData.m_sSoDescrID = line.soId;
if (lbIsUpdate)
{
newData.m_iSoID = inter.GetSoIdFromDescr(newData.m_sSoDescrID);
}
m_L4MsgInfoLine.sSoDescrIDInfoLine = newData.m_sSoDescrID;
switch (som.DecodeContractType(newData.m_sSoDescrID, iCustomerID.ToString()))
{
case TContractType.coInternal:
if (!som.IsCustomerInternal(iCustomerID))
{
m_eimOELineInterface.NotifyErrorMessage("Заказ обозначен как внутренний, но имеет внешнего заказчика");
bVerifyData = false;
}
break;
}
newData.m_iShipToCode = iShipToCode;
if (bVerifyData)
{
newData.m_iSoTypeCode = CheckValueType(bVerifyData, m_L4MsgInfoLine);
}
if (newData.m_iSoTypeCode == 2)
{
newData.m_iSoTypeCode = 4;
}
if (bVerifyData)
{
newData.m_iCreditStatus = CheckValueCredit(bVerifyData, m_L4MsgInfoLine);
}
if (bVerifyData)
{
newData.m_iOrderLineStatus = -1;
if (line.soLineStatus.ToString().Length > 0)
{
newData.m_iOrderLineStatus = line.soLineStatus;
}
if (!(newData.m_iOrderLineStatus > 0))
{
m_eimOELineInterface.NotifyErrorMessage("Неверный статус строки заказа");
bVerifyData = false;
}
if (line.dueDeliveryDate.ToString().Length > 0)
{
newData.m_dDueDelivery = line.dueDeliveryDate;
}
newData.m_iDeliveryPeriodNumID = som.RetrievePeriodNumID(newData.m_dDueDelivery, 1);
if (GetLineStatus(newData.m_iOrderLineStatus) != TLineStatus.IsClosed)
{
iSysDatePeriodNumID = som.RetrievePeriodNumID(DateTime.Now, 1);
}
if (bVerifyData)
{
if (line.productType.Length > 0)
{
newData.m_strProductCode = CheckValueProductType(bVerifyData, m_L4MsgInfoLine).ToString();
newData.m_strProductCode = ProductTypeCheck(newData.m_strProductCode);
}
}
if (bVerifyData)
{
newData.m_iInsertUser = m_eimOELineInterface.GetCreateUserId();
if (newData.m_iInsertUser == "")
{
bVerifyData = false;
}
}
if (bVerifyData)
{
newData.m_iUpdateUser = m_eimOELineInterface.GetModUserId();
if (newData.m_iUpdateUser == "")
{
bVerifyData = false;
}
}
if (l4MsgInfo.opCode > 0)
{
newData.m_iOpCode = l4MsgInfo.opCode;
}
}
if (bVerifyData)
{
som.LoadAttrb(newData.m_L4MsgInfoLine, newData.m_strProductCode, newData.m_iSoID, newData.m_iSoLineID, attributesOfLine, slArrayofAttributes);
}
return(newData);
}
catch (Exception e)
{
logger.Trace($"TSOLine.Create - Exception - Message:{e.Message}");
return(newData);
}
}
