/// <summary>
/// Возвращает номер транзакции
/// </summary>
/// <returns></returns>
public string GetGorodSub()
{
string Sub_inner_tid = "";
using (OldiContext db = new OldiContext(Settings.GorodConnectionString))
{
IEnumerable <string> tids = db.ExecuteQuery <string>(@"
select [sub_inner_tid]
from [gorod].[dbo].payment
where tid = {0}
" , Tid);
// where not sub_inner_tid like 'card-%' and tid = {0}
try
{
Sub_inner_tid = tids.First <string>();
}
catch (Exception)
{
}
}
if (string.IsNullOrEmpty(Sub_inner_tid))
{
return("");
}
else
{
return(Sub_inner_tid);
}
}
/// <summary>
/// Добавляет платёж в таблицу Pays
/// </summary>
/// <param name="account"></param>
void AddPay(int account)
{
using (OldiContext db = new OldiContext(Settings.ConnectionString))
{
db.ExecuteCommand("insert into oldigw.oldigw.pays (tid, datepay, account, amount, balance) values({0}, {1}, {2}, {3}, {4})", Tid, DateTime.Now, account, Amount, 0M);
}
RootLog($"{Tid} [DLIM] Account {account} добавлен платёж {Amount.AsCF()}");
}
/// <summary>
/// Подсчитывает сумму платежей за сутки для счёта
/// </summary>
/// <param name="account">Номер счёта</param>
/// <returns>Сумма всех платежей</returns>
decimal PaysInTime(int account)
{
decimal? balance = 0;
DateTime start = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
DateTime finish = start.AddDays(1);
using (OldiContext db = new OldiContext(Settings.ConnectionString))
{
IEnumerable <decimal?> pays = db.ExecuteQuery <decimal?>("select sum(amount) from oldigw.oldigw.pays where datepay between {0} and {1} and account = {2}", start, finish, account);
// if (pays != null && pays.Count() > 0)
balance = pays.First();
}
RootLog($"{Tid} [DLIM] Account {account} за день выплачено {balance.AsCF()}");
return(balance == null ? 0M : balance.Value);
}
/// <summary>
/// Поиск задвоенных платежей
/// </summary>
/// <returns></returns>
public int GetDoubles()
{
int Doubles = 0;
// Искать задвоенные платежи по параметрам:
// Point_oid
// Template_tid
// User_id
// Account
// Amount, Commission, Summary_amount
/*
* select @Point_oid = p.Point_oid,
* @Template_tid = p.template_tid,
* @User_id = p.user_id,
* @Account = d.clientAccount,
* @datepay = p.datePay
* from [gorod].[dbo].payment p
* left join [gorod].[dbo].pd4 d on p.tid = d.tid
* where p.tid = @tid
*
* select count(p.tid)
* from [gorod].[dbo].payment p
* left join [gorod].[dbo].pd4 d on p.tid = d.tid
* where p.tid <> @tid and -- кроме самого себя
* @Point_oid = p.Point_oid and
* @Template_tid = p.template_tid and
* @User_id = p.user_id and
* @Account = d.clientAccount and
* abs(datediff(second, p.datePay, @datePay)) <= 180
*/
try
{
using (OldiContext db = new OldiContext(Settings.GorodConnectionString))
{
// извлечём информацию о платеже
IEnumerable <Pay> Pays = db.ExecuteQuery <Pay>(@"
select datePay,
Tid,
Point_oid,
template_tid,
user_id,
amount,
commission,
summary_amount
from [gorod].[dbo].payment
where tid = {0}
", Tid);
Pay current = Pays.First();
RootLog($"**** {Tid} [Check doubles] acc={ID()} amount={current.Amount.AsCF()} commissiion={current.Commission.AsCF()} summary={current.Summary_amount.AsCF()} date={current.DatePay.Value.AsCF()} point={current.Point_oid} tpl={current.Template_tid} usr={current.User_id}");
Doubles = db.ExecuteCommand(@"
select count(p.tid)
from [gorod].[dbo].payment p
left join [gorod].[dbo].pd4 d on p.tid = d.tid
where p.tid <> {0} and -- кроме самого себя
{1} = p.Point_oid and
{2} = p.template_tid and
{3} = p.user_id and
{4} = p.amount and
{5} = p.commission and
{6} = p.summary_amount and
{7} = d.clientAccount and
abs(datediff(minute, p.datePay, {8})) <= 600 -- 10 часов
" , Tid, current.Point_oid, current.Template_tid, current.User_id, current.Amount, current.Commission, current.Summary_amount,
ID(), current.DatePay);
}
// where not sub_inner_tid like 'card-%' and tid = {0}
}
catch (Exception ex)
{
RootLog($"{Tid} [Check doubles] acc={ID()}\r\n{ex.ToString()}");
}
RootLog($"**** {Tid} [Check doubles] acc={ID()} doubles={Doubles}");
return(Doubles);
}