private void AddOrUpdateCard(CardInfo card)
{
var repo = new BaseRepo <CardInfo>(dbCtx);
CardInfo existsCard = null;
if (!string.IsNullOrEmpty(card.PkId))
{
existsCard = repo.Find(card.PkId);
}
else if (!string.IsNullOrEmpty(card.CardNo))
{
existsCard = repo.Find(t => t.CardNo == card.CardNo).FirstOrDefault();
}
card.UpdateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
if (existsCard == null)
{
card.NewId();
repo.Add(card);
}
else
{
existsCard.UpdateTime = card.UpdateTime;
existsCard.User = card.User;
existsCard.SerialNo = card.SerialNo;
existsCard.CardType = card.CardType;
existsCard.InvalidDate = card.InvalidDate;
repo.Update(existsCard);
card.PkId = existsCard.PkId;
}
}
private void UpdateAuth(string cardPkId, IList <Device> devices)
{
var repo = new BaseRepo <CardAuth>(dbCtx);
var existsList = repo.Find(t => t.Card == cardPkId);
foreach (var e in existsList)
{
repo.Delete(e);
}
if (devices != null && devices.Count > 0)
{
var updateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
foreach (var dev in devices)
{
var entity = new CardAuth()
{
Card = cardPkId,
Device = dev.PkId,
UpdateTime = updateTime
};
entity.NewId();
repo.Add(entity);
}
}
}
private void AddOrUpdateUser(UserInfo user)
{
var repo = new BaseRepo <UserInfo>(dbCtx);
UserInfo existsUser = null;
if (!string.IsNullOrEmpty(user.PkId))
{
existsUser = repo.Find(user.PkId);
}
else if (!string.IsNullOrEmpty(user.IdentityNumber))
{
existsUser = repo.Find(t => t.IdentityNumber == user.IdentityNumber).FirstOrDefault();
}
if (existsUser == null)
{
user.NewId();
repo.Add(user);
}
else
{
existsUser.Name = user.Name;
existsUser.Phone = user.Phone;
existsUser.IdentityNumber = user.IdentityNumber;
//todo: other fields
user.PkId = existsUser.PkId;
repo.Update(existsUser);
}
}
/// <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);
}
}
public UserCardAuthDto GetByCardNo(string cardNo)
{
var dto = new UserCardAuthDto();
dto.Card = new BaseRepo <CardInfo>(dbCtx)
.Find(t => t.CardNo == cardNo).FirstOrDefault();
if (dto.Card == null)
{
dto.Card = new CardInfo()
{
CardNo = cardNo
};
}
else
{
dto.User = new BaseRepo <UserInfo>(dbCtx).Find(dto.Card.User);
var deviceIds = new BaseRepo <CardAuth>(dbCtx)
.Find(t => t.Card == dto.Card.PkId)
.Select(t => t.Device)
.ToArray();
dto.Devices = new BaseRepo <Device>(dbCtx)
.Find(t => deviceIds.Contains(t.PkId))
.ToList();
}
return(dto);
}
/// <summary>
/// Удаление накладной
/// </summary>
/// <param name="msgCounter">Счетчик сообщений</param>
/// <param name="bolId">Номер накладной</param>
/// <param name="posNumId">Позиция в накладной</param>
/// <param name="transaction">Транзакция БД</param>
private void DeleteBol(int msgCounter, string bolId, int posNumId, OracleTransaction transaction)
{
if (bolId != "" && posNumId > 0)
{
OracleDynamicParameters odp = new OracleDynamicParameters();
string str = "DELETE FROM EXT_BOL_POSITION WHERE POS_NUM_ID = :POS_NUM_ID ";
odp.Add("POS_NUM_ID", posNumId);
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
connection.Execute(str, odp, transaction);
}
OracleDynamicParameters odp1 = new OracleDynamicParameters();
str = "SELECT count(1) as cnt FROM EXT_BOL_POSITION WHERE BOL_ID = :BOL_ID";
odp1.Add("BOL_ID", bolId);
int res;
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
res = connection.QueryFirstOrDefault <int>(str, odp, transaction);
}
if (res == 0)
{
OracleDynamicParameters odp2 = new OracleDynamicParameters();
str = "DELETE FROM EXT_BOL_HEADER WHERE BOL_ID = :BOL_ID ";
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
connection.Execute(str, odp, transaction);
}
}
UpdateStatusMessage(msgCounter, bol_created, "");
}
else
{
UpdateStatusMessage(msgCounter, bol_error, "bolId или posNumId равны null");
}
}
/// <summary>
/// Создание позиции если не существует
/// </summary>
/// <param name="strBolId">ИД позиции</param>
public void CreateBolIfNotEx(string strBolId)
{
OracleDynamicParameters odp = new OracleDynamicParameters();
string bolId = "";
string str = "SELECT BOL_ID FROM EXT_BOL_HEADER WHERE BOL_ID = :BOL_ID";
odp.Add("BOL_ID", strBolId);
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
bolId = connection.ExecuteScalar <string>(str, odp);
}
if (bolId == "")
{
odp = new OracleDynamicParameters();
str = "INSERT INTO EXT_BOL_HEADER " +
"(MOD_USER_ID,MOD_DATETIME, BOL_ID,CREATION_DATETIME,STATUS) " +
"VALUES (:MOD_USER_ID ,SYSDATE, :BOL_ID, SYSDATE, :STATUS)";
//odp.Add("MOD_USER_ID",); //Узнать про g_oUser.Userid
odp.Add("BOL_ID", strBolId);
odp.Add("STATUS", L4L3InterfaceServiceConst.BOL_NOT_SENT);
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
connection.Execute(str, odp);
}
}
}
/// <summary>
/// runs the RegisterUser, SignInUser methods based on the console print statements from startmenu
/// </summary>
/// <param name="baseRepo">DB context query object</param>
/// <returns> takes username from SignInUser and returns to runUI </returns>
public string StartMenuInput(BaseRepo baseRepo)
{
string menuInput = Console.ReadLine();
while (menuInput.Any(char.IsLetter))
{
Console.WriteLine("Please insert a valid number choice");
menuInput = Console.ReadLine();
}
switch (int.Parse(menuInput))
{
case 1:
RegisterUser(baseRepo);
Console.WriteLine("\nRegistration successful");
goto case 2;
case 2:
string username = SignInUser(baseRepo);
Console.WriteLine("\n{0} Signed in.", username);
return(username);
case 9:
Console.WriteLine("\nShutting Down");
System.Environment.Exit(0);
return("");
default:
Console.WriteLine("Not a valid choice. program ending.");
System.Environment.Exit(1);
return("");
}
}
/// <summary>
/// Check if customer name and password exist and match
/// </summary>
/// <param name="baseRepo"> db context object</param>
/// <returns> the user's username </returns>
public string SignInUser(BaseRepo baseRepo)
{
Console.WriteLine("\nTo sign in, Please enter your full name");
string customerName = Console.ReadLine();
while (baseRepo.checkCustomerNameExists(customerName) == false)
{
Console.WriteLine("\nname not found, please enter a valid name or escape with 9");
customerName = Console.ReadLine();
if (customerName.StartsWith("9"))
{
System.Environment.Exit(1);
}
}
Console.WriteLine("\nPlease enter your password");
string customerPassword = Console.ReadLine();
while (baseRepo.checkCustomerPasswordExists(customerName, customerPassword) == false)
{
Console.WriteLine("invalid password, please enter a valid name or escape with 9");
customerPassword = Console.ReadLine();
if (customerPassword.StartsWith("9"))
{
System.Environment.Exit(1);
}
}
return(customerName);
}
/// <summary>
/// enter a full name/username and a password and save to DB
/// </summary>
/// <param name="baseRepo">DB context query object</param>
public void RegisterUser(BaseRepo baseRepo)
{
bool checkRegistering = false;
int fallBack = 5;
int count = 0;
Customer newCustomer = new Customer();
Console.WriteLine("\nPlease enter your full name.");
while ((checkRegistering == false) && (count <= fallBack))
{
newCustomer.FullName = Console.ReadLine();
checkRegistering = newCustomer.ValidateName(newCustomer);
count++;
}
Console.WriteLine("\nPlease enter a password at least 8 characters long");
count = 0;
checkRegistering = false;
while ((checkRegistering == false) && (count <= fallBack))
{
newCustomer.CustPassword = Console.ReadLine();
checkRegistering = newCustomer.ValidatePass(newCustomer);
count++;
}
baseRepo.RegisterCustomer(newCustomer);
baseRepo.Save();
}
public List <Order> GetAllOrders()
{
if (!File.Exists(BaseRepo.DbFIle))
{
BaseRepo.CreateDatabase();
}
var orders = new List <Order>();
using (var cnn = BaseRepo.DbConnection())
{
cnn.Open();
var ordersWithoutProducts = cnn.Query <Order>("SELECT * FROM PlaceOrder").ToList();
foreach (Order order in ordersWithoutProducts)
{
order.Objects = cnn.Query <OrderObject>("SELECT * FROM OrderObject WHERE OrderId = @OrderId",
new { OrderId = order.Id })
.ToList();
orders.Add(order);
}
}
return(orders);
}
public void TestMethod()
{
BaseRepo <BaseModel> t1 = new BaseRepo <BaseModel>();
BaseRepo <NewModel> t2 = new NewRepo();
ICovariant <BaseModel> t3 = new BaseRepo <NewModel>();
ICovariant <BaseModel> t4 = new NewRepo();
}
/// <summary>
/// Проверка статуса вложенных линий заказа
/// </summary>
/// <param name="iMsgCounter">Счетчик сообщений</param>
/// <returns>
/// true - есть вложенные линии заказа
/// false - вложенные линии заказа отсутствуют
/// </returns>
public bool CheckInquiryLinesStatus(int iMsgCounter)
{
int iCounterSale, iCounterLine;
bool result = false;
string sqlstr = $"select count(*) as counter from sales_order_line where so_id = (select inquiry_id from L4_L3_SO_HEADER where msg_counter = {iMsgCounter} ) " +
$"and so_line_id in (select (so_line_id / 10) from L4_L3_SO_LINE where msg_counter = {iMsgCounter} ) and so_line_status = 8";
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
iCounterSale = connection.ExecuteScalar <int>(sqlstr, null);
}
sqlstr = $"select count(*) as counter from L4_L3_SO_LINE where msg_counter = {iMsgCounter}";
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
iCounterLine = connection.ExecuteScalar <int>(sqlstr, null);
}
if (iCounterLine != 0 && iCounterSale != 0)
{
if (iCounterLine == iCounterSale)
{
result = true;
}
}
return(result);
}
/// <summary>
/// Проверка существования накладной
/// </summary>
/// <param name="bolId">Номер накладной</param>
/// <param name="selChild"></param>
/// <returns>
/// true - существует
/// false - не существует
/// </returns>
private bool ExistsBol(string bolId, bool selChild)
{
OracleDynamicParameters odp = new OracleDynamicParameters();
if (bolId != null)
{
string str, result = "";
if (selChild)
{
str = "SELECT ebh.BOL_ID FROM EXT_BOL_HEADER ebh left join V_PIECE vp on ebh.BOL_ID = vp.BOL_ID WHERE upper(ebh.BOL_ID) = upper(:BOL_ID) AND vp.PIECE_NUM_ID IS NULL AND ebh.STATUS = 1";
}
else
{
str = "SELECT BOL_ID FROM EXT_BOL_HEADER WHERE upper(BOL_ID) = upper(:BOL_ID) ";
}
odp.Add("BOL_ID", bolId);
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
result = connection.QueryFirstOrDefault <string>(str, odp);
}
if (result == "")
{
return(false);
}
}
return(true);
}
public virtual bool Delete(IEnumerable <TEntity> entities)
{
using (var cxt = DbContext(DbOperation.Write))
{
try
{
cxt.BeginTransaction();
var repo = new BaseRepo <TEntity>(cxt);
var isSuccess = repo.Delete(entities);
if (isSuccess)
{
cxt.Commit();
}
else
{
cxt.Rollback();
}
return(isSuccess);
}
catch (Exception ex)
{
cxt.Rollback();
return(false);
}
}
}
public virtual long Insert(IEnumerable <TEntity> entities)
{
using (var cxt = DbContext(DbOperation.Write))
{
try
{
cxt.BeginTransaction();
var repo = new BaseRepo <TEntity>(cxt);
long id = repo.Insert(entities);
if (id > 0)
{
cxt.Commit();
}
else
{
cxt.Rollback();
}
return(id);
}
catch (Exception ex)
{
cxt.Rollback();
return(0);
}
}
}
public virtual bool Update(IEnumerable <TEntity> entities, Func <PropertyInfo, bool> propertyFilter)
{
using (var cxt = DbContext(DbOperation.Write))
{
try
{
cxt.BeginTransaction();
var repo = new BaseRepo <TEntity>(cxt);
bool isSuccess = repo.Update(entities, propertyFilter);
if (isSuccess)
{
cxt.Commit();
}
else
{
cxt.Rollback();
}
return(isSuccess);
}
catch (Exception ex)
{
cxt.Rollback();
return(false);
}
}
}
internal static void OverrideConnectionString(BaseRepo repo, string connectionString)
{
var t = repo.GetType();
const string propName = "ConnectionStringOrName";
t.InvokeMember(propName, BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.SetProperty | BindingFlags.Instance, null, repo, new object[] { connectionString });
}
/// <summary>
/// Сохранение листа примечаний
/// </summary>
public void SaveNote()
{
if (linenote.Count > 0)
{
foreach (TLineNote line in linenote)
{
if (line.lineNote != "")
{
OracleDynamicParameters odp = new OracleDynamicParameters();
string sqlstr = "INSERT INTO SO_LINE_NOTE " +
"(SO_NOTE_DUMMY_KEY, SO_ID, SO_LINE_ID, " +
"NOTE_TEXT, INSERTION_DATETIME, INSERTED_BY, MOD_DATETIME, MOD_USER_ID) " +
"SELECT SEQ_SO_LINE_NOTE_DUMMY_KEY.NEXTVAL AS SO_NOTE_DUMMY_KEY, " +
":P_SO_ID, :P_SO_LINE_ID,:P_LINENOTE,SYSDATE AS INSERTION_DATETIME," +
":P_INSERTED_BY AS INSERTED_BY, SYSDATE AS MODE_DATETIME," +
":P_MOD_USER_ID AS MOD_USER_ID FROM DUAL";
odp.Add("P_SO_ID", line.soId);
odp.Add("P_SO_LINE_ID", line.soLineId);
odp.Add("P_LINENOTE", line.lineNote);
odp.Add("P_INSERTED_BY", line.sSAPUser);
odp.Add("P_MOD_USER_ID", line.sSAPUser);
using (OracleConnection conn = BaseRepo.GetDBConnection())
{
conn.Execute(sqlstr, odp);
}
}
}
}
}
public PatientAddViewModel()
{
ButtonClick = new DelegateCommand(Add);
repo = new BaseRepo <Patient>(new ClinicAppDAL.EF.ClinicAppClinicContext());
SelectedPatient = new Patient();
SelectedPatient.PatientBirthdate = DateTime.Now;
}
internal static void OverrideConnectionString(BaseRepo repo, string connectionString)
{
var t = repo.GetType();
const string propName = "ConnectionStringOrName";
t.InvokeMember(propName, BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.SetProperty | BindingFlags.Instance, null, repo, new object[] { connectionString });
}
/// <summary>
/// Проверка существования заказчика в таблице
/// </summary>
/// <param name="m_iCustSoldDescrID">ИД заказчика</param>
/// <param name="odp"></param>
/// <returns>Существует/Не существует</returns>
public bool ExistCustomer(string m_iCustSoldDescrID, OracleDynamicParameters odp = null)
{
string cust;
StringBuilder stm = new StringBuilder(@"SELECT EXPIRATION_DATE FROM CUSTOMER_CATALOG WHERE CUSTOMER_DESCR_ID = " + m_iCustSoldDescrID + "");
using (OracleConnection conn = BaseRepo.GetDBConnection())
{
cust = conn.ExecuteScalar <string>(stm.ToString(), odp);
}
if (cust != null)
{
if (Convert.ToDateTime(cust) <= DateTime.Now)
{
return(true);
}
else
{
return(false);
}
}
else
{
return(false);
}
}
public void Setup()
{
_userRepo = new BaseRepo<User>(dbName);
_orgRepo = new BaseRepo<Organization>(dbName);
_organizationService = new OrganizationService(_orgRepo, _userRepo);
}
public void AddOrder(InternetOrder order)
{
if (!File.Exists(BaseRepo.DbFIle))
{
BaseRepo.CreateDatabase();
}
using (var cnn = BaseRepo.DbConnection())
{
cnn.Open();
string sql =
"INSERT INTO Order (ClientId, Completed, IpAddress) Values (@ClientId, @Completed, @IpAddress);";
cnn.Execute(sql,
new { ClientId = order.ClientId, Completed = 0, IpAddress = order.IpAddress });
SQLiteCommand Command = new SQLiteCommand("select last_insert_rowid()", cnn);
Int64 LastRowID64 = (Int64)Command.ExecuteScalar();
int orderId = (int)LastRowID64;
sql = "INSERT INTO OrderObject (Amount, ObjectId, OrderId) Values (@Amount, @ObjectId, @OrderId);";
foreach (var orderObj in order.Objects)
{
cnn.Execute(sql, new { Amount = orderObj.Amount, ObjectId = orderObj.ObjectId, OrderId = orderId });
}
}
}
/// <summary>
/// Сохранение листа примечаний
/// </summary>
public void SaveNote()
{
if (headernote.Count > 0)
{
foreach (THeaderNote header in headernote)
{
if (header.headerNote != "")
{
OracleDynamicParameters odp = new OracleDynamicParameters();
string sqlstr = "INSERT INTO SO_HEADER_NOTE " +
"(SO_NOTE_DUMMY_KEY, SO_ID, " +
"NOTE_TEXT, INSERTION_DATETIME, INSERTED_BY, MOD_DATETIME, MOD_USER_ID) " +
" SELECT SEQ_SO_HEADER_NOTE_DUMMY_KEY.NEXTVAL AS SO_NOTE_DUMMY_KEY, " +
":P_SO_ID, :P_HEADERNOTE,SYSDATE AS INSERTION_DATETIME," +
"-555 AS INSERTED_BY, SYSDATE AS MODE_DATETIME," +
"-555 AS MOD_USER_ID FROM DUAL";
odp.Add("P_SO_ID", header.so_id);
odp.Add("P_HEADERNOTE", header.headerNote);
using (OracleConnection conn = BaseRepo.GetDBConnection())
{
conn.Execute(sqlstr, odp);
}
}
}
}
}
public UserCardAuthDto GetByCardIdOrUserId(string cardId, string userId)
{
var dto = new UserCardAuthDto();
if (!string.IsNullOrEmpty(cardId))
{
dto.Card = new BaseRepo <CardInfo>(dbCtx).Find(cardId);
if (dto.Card != null)
{
dto.User = new BaseRepo <UserInfo>(dbCtx).Find(dto.Card.User);
var deviceIds = new BaseRepo <CardAuth>(dbCtx)
.Find(t => t.Card == dto.Card.PkId)
.Select(t => t.Device)
.ToArray();
dto.Devices = new BaseRepo <Device>(dbCtx)
.Find(t => deviceIds.Contains(t.PkId))
.ToList();
}
}
else if (!string.IsNullOrEmpty(userId))
{
dto.User = new BaseRepo <UserInfo>(dbCtx).Find(userId);
}
return(dto);
}
internal static string FindConnectionString(BaseRepo repo, string configFilePath)
{
//if the repo already has the full connection string test this first.
if (CanConnectToDatabaseServer(repo.ConnectionStringOrName))
{
return repo.ConnectionStringOrName;
}
if (!File.Exists(configFilePath)) throw new FileNotFoundException("Could not find configuration file");
var str = File.ReadAllText(configFilePath);
var xmlRoot = XElement.Parse(str);
var connectionStringElement = xmlRoot.Elements("connectionStrings").FirstOrDefault();
if(connectionStringElement == null) throw new ArgumentOutOfRangeException("configFilePath", "Could not find connection string section in config file");
var connections = connectionStringElement.Elements("add").ToArray();
if(connections == null || !connections.Any()) throw new ArgumentOutOfRangeException("configFilePath", "Could not find any connections within connection string section of config file");
XElement connectionElement = null;
connectionElement = !string.IsNullOrWhiteSpace(repo.ConnectionStringOrName)
? connections.FirstOrDefault(c => c.Attribute("name").Value == repo.ConnectionStringOrName)
: connections.FirstOrDefault();
if (connectionElement == null) throw new ArgumentOutOfRangeException("configFilePath", "Could not find connection string that matches repo connection: " + repo.ConnectionStringOrName);
var connString = connectionElement.Attribute("connectionString").Value;
if (string.IsNullOrWhiteSpace(connString)) throw new ArgumentNullException("configFilePath", "Connection string is empty for: " + repo.ConnectionStringOrName);
return connString;
}
public virtual TEntity Get <TKey>(TKey id)
{
using (var cxt = DbContext(DbOperation.Read))
{
var repo = new BaseRepo <TEntity>(cxt);
return(repo.Get(id));
}
}
public IEnumerable <TEntity> GetAll()
{
using (var cxt = DbContext(DbOperation.Read))
{
var repo = new BaseRepo <TEntity>(cxt);
return(repo.GetAll());
}
}
/// <summary>
/// Получение данных по ИД заказчика
/// </summary>
/// <param name="id">ИД заказчика</param>
public void GetData(string id)
{
string sqlstr = $"SELECT * FROM CUSTOMER_CATALOG WHERE CUSTOMER_ID = {id}";
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
customerCat = connection.QueryFirstOrDefault <CustomerCat>(sqlstr, null);
}
}
/// <summary>
/// Получение ИД заказа
/// </summary>
/// <param name="sSoDescrID">Текстовый ИД заказа</param>
/// <param name="odp"></param>
/// <returns>ИД заказа</returns>
public int GetSoIdFromDescr(string sSoDescrID, OracleDynamicParameters odp = null)
{
string stm = @"SELECT SO_ID FROM SALES_ORDER_HEADER WHERE SO_DESCR_ID = " + sSoDescrID + "";
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
return(connection.ExecuteScalar <int>(stm, odp));
}
}
/// <summary>
/// Получение значения типа продукта
/// </summary>
/// <param name="bisValid"></param>
/// <param name="l4MsgInfo">Обработчик сообщений по линиям заказа</param>
/// <returns>Значение типа продукта</returns>
public int CheckValueProductType(bool bisValid, TL4MsgInfoLine l4MsgInfo)
{
bisValid = false;
int res = 0;
List <int> values = new List <int>();
string str = "SELECT PRODUCT_TYPE AS PRODUCT_CODE" +
"FROM L4_L3_SO_LINE " +
"WHERE MSG_COUNTER=:MSG_COUNTER" +
"AND SO_ID =:SO_ID " +
"AND (SO_LINE_ID/10)= :SO_LINE_ID";
OracleDynamicParameters odp = new OracleDynamicParameters();
odp.Add("MSG_COUNTER", l4MsgInfo.tL4MsgInfo.msgCounter);
odp.Add("SO_ID", l4MsgInfo.sSoDescrIDInfoLine);
odp.Add("SO_LINE_ID", l4MsgInfo.iSOLineID);
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
values = connection.Query <int>(str, odp).AsList();
}
if (values.Count > 0)
{
foreach (int val in values)
{
if (val != 0)
{
int count = 0;
str = "SELECT COUNT(*) AS COUNTER " +
"FROM PRODUCT_TYPE_CATALOGUE " +
"WHERE SAP_CODE=:SAP_CODE" +
"AND FINISHED_PRODUCT_FLAG = 'Y' AND (EXPIRATION_DATE IS NULL OR TO_CHAR(EXPIRATION_DATE, 'DD/MM/YYYY')" +
"= '01/01/1970' OR EXPIRATION_DATE > sysdate) AND VALIDITY_DATE < sysdate ";
odp = new OracleDynamicParameters();
odp.Add("SAP_CODE", val);
using (OracleConnection connection = BaseRepo.GetDBConnection())
{
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_LINE.SO_TYPE_CODE is not valid");
}
return(res);
}
static void RetrieveOneByDate()
{
BaseRepo <Address> data = new BaseRepo <Address>();
Address addr = new Address();
DateTime date = new DateTime(2018, 12, 01);
addr = data.GetAddress(date);
Console.WriteLine($"On {date.ToString("MM/dd/yyyy")}, you lived at");
DisplayAddress(addr);
}
