using Applications.FDC;
using Edge.Core.Database;
using Edge.Core.Processor;
using Edge.Core.IndustryStandardInterface.Pump;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Abstractions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using Wayne.FDCPOSLibrary;
using Edge.Core.Configuration;
namespace Applications.PumpInfoToSinoChemPosSqlServerOrMySql
{
public abstract class AppBase
{
protected ILogger logger = NullLogger.Instance;
protected FdcServerHostApp fdcServerHostApp;// = Configurator.Default.NozzleProductConfiguration.Mapping;
protected AppConfigV1 appConfig;
#region App config
public class AppConfigV1
{
public string DatabaseConnStr { get; set; }
public string JiaYouJiPeiZhi_TableName { get; set; }
public List<YouJiConfigV1> YouJiConfigs { get; set; }
}
/// <summary>
/// 对应一台物理油机
/// </summary>
public class YouJiConfigV1
{
/// <summary>
/// 油机编号,由1开始编号
/// 假设某个油站有四台油机, 则此号码分别为1、2、3、4
/// </summary>
public byte JiHao { get; set; }
public List<YouMianConfigV1> YouMianConfigs { get; set; }
}
/// <summary>
/// 每台物理油机有两个油面(A面或B面)
/// </summary>
public class YouMianConfigV1
{
/// <summary>
/// 0 or 1, 0=A面 1=B面
/// </summary>
public int YouMianBianHao { get; set; }
public List<YouQiangConfigV1> YouQiangConfigs { get; set; }
}
public class YouQiangConfigV1
{
/// <summary>
/// 每台油面下的枪号,由1开始编号，作用范围是一个面，即换面则重置，重新从1开始。
/// 如某个面上有2把枪，则此编号为1、2，另外一个面上有3把枪，则此编号为1、2、3.
/// </summary>
public int QiangHao { get; set; }
/// <summary>
/// 枪号,对应"加油流水表"和"油枪状态表"的枪号(jihao),整个油站的所有枪号由1开始编号
///
/// 应该与FDC SERVER中的site level nozzle id相匹配对应。
/// </summary>
public int LuoJiQiangHao { get; set; }
}
#endregion
protected AppBase(AppConfigV1 appConfig, IServiceProvider services)
{
this.appConfig = appConfig;
var loggerFactory = services.GetRequiredService<ILoggerFactory>();
this.logger = loggerFactory.CreateLogger("Application");
}
/// <summary>
/// Truncate table jyjpz and jy_info, and then re-insert site nozzles info to them.
/// </summary>
/// <param name="dbConnCreator"></param>
/// <param name="dbCommandCreator"></param>
/// <returns></returns>
protected bool ReCreatePumpLayoutToDatabase(Func<DbConnection> dbConnCreator,
Func<DbCommand> dbCommandCreator)
{
try
{
#region truncate table [jyjpz] and [jy_info], and then insert data into table [jyjpz]
// for now, always overwrite the whole table in POS database without further check for rows or columns level.
using (var posConn = dbConnCreator())
{
/* configInFcDb structure like below */
//" JiHao: " + oneRow["jiHao"] + ", sideId: " + oneRow["sideId"] +
//", nozzleLogicalId: " + oneRow["HoseLogicalId"] +
//", siteLevelNozzleId: " + oneRow["siteLevelNozzleId"]
string bulkInsertCmd = "";
const string sqlConcateAppendix = "Union all ";
foreach (var youJiConfig in this.appConfig.YouJiConfigs)
{
foreach (var youMianConfig in youJiConfig.YouMianConfigs)
{
foreach (var youQiangConfig in youMianConfig.YouQiangConfigs)
{
bulkInsertCmd +=
$"Select {youJiConfig.JiHao}, '{youMianConfig.YouMianBianHao}', {youQiangConfig.QiangHao}, {youQiangConfig.LuoJiQiangHao} {sqlConcateAppendix}";
}
}
}
var truncateAndInsertCmd = dbCommandCreator();
truncateAndInsertCmd.Connection = posConn;
truncateAndInsertCmd.CommandText =
"TRUNCATE table " + this.appConfig.JiaYouJiPeiZhi_TableName + "; TRUNCATE table jy_info; INSERT " + this.appConfig.JiaYouJiPeiZhi_TableName + " (jihao, abtype, qianghao, luojiqh) " + bulkInsertCmd.Substring(0, bulkInsertCmd.Length - sqlConcateAppendix.Length);
logger.LogInformation("truncateAndInsertSiteConfigCmd: " + truncateAndInsertCmd.CommandText);
posConn.Open();
truncateAndInsertCmd.ExecuteNonQuery();
//return true;
};
#endregion
#region insert data into table [jy_info]
//foreach (var nozzle in sortedSiteNozzlesDataRows)//.Select(n => n["siteLevelNozzleId"]))
//{
//var siteLevelNozzleId = nozzle["siteLevelNozzleId"];
foreach (var nozzle in fdcServerHostApp.FdcPumpControllers.SelectMany(c => c.Nozzles))
{
//var pumpId = int.Parse(nozzle["pumpid"].ToString());
//var pump = fdcServerHostApp.FdcPumpControllers.First(f => f.PumpId == pumpId);
//var nozzleLogicalId = int.Parse(nozzle["HoseLogicalId"].ToString());
using (var posSqlConnection = dbConnCreator())
{
var bindingNozzleExtraInfo = this.fdcServerHostApp.GetNozzleExtraInfos()?.FirstOrDefault(f => f.PumpId == nozzle.PumpId
&& f.NozzleLogicalId == nozzle.LogicalId);
try
{
//var gradeFriendlyName = rawProductNameToPosProductNameMapping[productNo];// Translator.GetFriendlyGradeName(SiteConfigUtility.Default.GetGradeName(siteLevelNozzleId));
//SqliteDbContext dbContext = new SqliteDbContext();
//var lastTrx = dbContext.PumpTransactionModels.OrderByDescending(f => f.SaleEndTime)
// .FirstOrDefault(f => f.PumpId == pumpId && f.LogicalNozzleId == nozzleLogicalId);
var lastTrx = fdcServerHostApp.GetAvailableFuelSaleTrxsWithDetailsAsync(nozzle.PumpId, nozzle.LogicalId, 1).Result.FirstOrDefault();
var totalizer = fdcServerHostApp.GetFuelPointTotalsAsync(nozzle.PumpId, nozzle.LogicalId).Result;
//new Tuple<float, float>((float)((lastTrx?.VolumeTotalizer / Math.Pow(10, pump.VolumeDecimalDigits) ?? 0)),
// (float)((lastTrx?.AmountTotalizer / Math.Pow(10, pump.AmountDecimalDigits) ?? 0) * Math.Pow(10, 2)));
var setPumpOnStartingCommand = dbCommandCreator();
setPumpOnStartingCommand.Connection = posSqlConnection;
setPumpOnStartingCommand.CommandText = this.InitTable_jy_info_SqlCommand(
(bindingNozzleExtraInfo?.SiteLevelNozzleId ?? -1).ToString(),
(bindingNozzleExtraInfo?.ProductName ?? "undefined"),
totalizer.Item2, totalizer.Item1);
logger.LogDebug("initTable_jy_info_Command: " + setPumpOnStartingCommand.CommandText);
posSqlConnection.Open();
setPumpOnStartingCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
logger.LogError("executing initTable_jy_info_Command failed for siteLevelNozzleId: " + (bindingNozzleExtraInfo?.SiteLevelNozzleId ?? -1) + ", exception detail: " + ex);
throw;
}
}
}
#endregion
}
catch (Exception ex)
{
logger.LogError("PerformCompareAndUpdate exceptioned, detail: " + ex);
return false;
}
return true;
}
protected virtual string InitTable_jy_info_SqlCommand(string siteLevelNozzleId, string gradeFriendlyName,
double totalizerVol, double totalizerAmt)
{
return string.Format("if not exists(select * from jy_info where jihao={0})" +
" BEGIN" +
" insert jy_info (jihao, status, youpin, qty, amount, fzqty, fzamount) values({0}, '{1}', N'{2}', 0, 0, '{3}', {4})" +
" END",
siteLevelNozzleId,
'F',
gradeFriendlyName,
totalizerVol, totalizerAmt);
}
/// <summary>
/// attach pump event for inserting correlated data to database tables.
/// </summary>
/// <param name="dbConnCreator"></param>
/// <param name="dbCommandCreator"></param>
/// <returns></returns>
protected bool HandlePumpStateChangeToDatabase(Func<DbConnection> dbConnCreator,
Func<DbCommand> dbCommandCreator)
{
var siteLevelNozzldIdsInAppConfig = this.appConfig.YouJiConfigs.SelectMany(youJiConfig => youJiConfig.YouMianConfigs.SelectMany(youMianConfig => youMianConfig.YouQiangConfigs))
.Select(youQiangConfig => youQiangConfig.LuoJiQiangHao);
var siteLevelNozzldIdsInFdcServerApp = this.fdcServerHostApp.GetNozzleExtraInfos().Where(ri => ri.SiteLevelNozzleId.HasValue).Select(ri => ri.SiteLevelNozzleId.Value);
var missedInFdcServerApp = siteLevelNozzldIdsInAppConfig.Except(siteLevelNozzldIdsInFdcServerApp);
var missedInAppConfig = siteLevelNozzldIdsInFdcServerApp.Except(siteLevelNozzldIdsInAppConfig);
if (missedInFdcServerApp.Any())
{
throw new ArgumentException("油枪(以此App中 全站枪号 为标识): "
+ missedInFdcServerApp.Select(n => n.ToString()).Aggregate((acc, n) => acc + ", " + n) + " 未配置于FdcServerApp中, 请配置后再试");
}
if (missedInAppConfig.Any())
{
throw new ArgumentException("油枪(以 FdcServerApp中的全站枪号 为标识): "
+ missedInAppConfig.Select(n => n.ToString()).Aggregate((acc, n) => acc + ", " + n) + " 未配置于此App中, 请配置后再试");
}
fdcServerHostApp.OnStateChange += (s, a) =>
{
var pump = s as IFdcPumpController;
try
{
if (a.NewPumpState == LogicalDeviceState.FDC_READY)
{
/* indicate for nozzle if replaced back */
var sizeLevelNozzleIdsOnPump = this.fdcServerHostApp.GetNozzleExtraInfos().Where(ri => ri.PumpId == pump.PumpId && ri.SiteLevelNozzleId.HasValue).Select(ri => ri.SiteLevelNozzleId.Value);
if (!sizeLevelNozzleIdsOnPump.Any())
{
logger.LogInformation("Could not found any site level nozzle id defined on pump: " + pump.PumpId);
return;
}
using (var posSqlConnection = dbConnCreator())
{
try
{
/* idle would not carry nozzle id, so here reset all nozzles on target pump.*/
var setPumpOnIdleCommand = dbCommandCreator();
setPumpOnIdleCommand.Connection = posSqlConnection;
setPumpOnIdleCommand.CommandText = sizeLevelNozzleIdsOnPump.Select(
siteLevelNozzleId =>
string.Format(
"Update jy_info set status = '{1}', qty=0, amount=0 where jihao = {0};", siteLevelNozzleId, 'F')
).Aggregate((acc, n) => acc + " " + n);
logger.LogDebug("setPumpOnIdleCommand(via Fdc): " + setPumpOnIdleCommand.CommandText);
posSqlConnection.Open();
setPumpOnIdleCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
logger.LogError("executing setPumpOnIdleCommand(via Fdc) failed, exception detail: " + ex);
}
}
}
else if (a.NewPumpState == LogicalDeviceState.FDC_CALLING)
{
using (var posSqlConnection = dbConnCreator())
{
try
{
var operatingNozzleLogicalId = a?.StateChangedNozzles?.FirstOrDefault()?.LogicalId ?? (byte)0;
var bindingNozzleExtraInfo = this.fdcServerHostApp.GetNozzleExtraInfos()?.FirstOrDefault(f => f.PumpId == pump.PumpId
&& f.NozzleLogicalId == operatingNozzleLogicalId);
//var updatePumpOnFuelingCommand =
// new SqlCommand(string.Format("Update jy_info set [status] = '{1}', youpin = N'{2}', qty= {3}, amount= {4} where jihao = '{0}'"
// , SiteConfigUtility.Default.GetSiteLevelNozzleIdByLogicalNozzleId(pump.PumpId, a?.Transaction?.Nozzle?.LogicalId ?? 0),
// 'B',
// gradeFriendlyName,
// a.Transaction.Volumn,
// a.Transaction.Amount),
// posSqlConnection);
var updatePumpOnFuelingCommand = dbCommandCreator();
updatePumpOnFuelingCommand.Connection = posSqlConnection;
updatePumpOnFuelingCommand.CommandText =
string.Format("Update jy_info set status = '{1}' where jihao = '{0}';"
, (bindingNozzleExtraInfo?.SiteLevelNozzleId ?? -1),
'B',
(bindingNozzleExtraInfo?.ProductName ?? "undefined"));
logger.LogDebug("updatePumpOnFuelingCommand: " + updatePumpOnFuelingCommand.CommandText);
posSqlConnection.Open();
updatePumpOnFuelingCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
logger.LogError("executing updatePumpOnFuelingCommand failed, exception detail: " + ex);
}
}
}
}
catch (Exception exx) { }
};
fdcServerHostApp.OnCurrentFuellingStatusChange += async (s, a) =>
{
try
{
var pump = s as IFdcPumpController;
if (!a.Transaction.Finished)
{
//add some random drops to reduce the operations in db, this is for performance improve.
if (DateTime.Now.Millisecond % 2 == 0 || DateTime.Now.Millisecond % 3 == 0) return;
using (var posSqlConnection = dbConnCreator())
{
try
{
var bindingNozzleExtraInfo = this.fdcServerHostApp.GetNozzleExtraInfos().FirstOrDefault(f => f.PumpId == pump.PumpId
&& f.NozzleLogicalId == a.Transaction.Nozzle.LogicalId);
//var updatePumpOnFuelingCommand =
// new SqlCommand(string.Format("Update jy_info set [status] = '{1}', youpin = N'{2}', qty= {3}, amount= {4} where jihao = '{0}'"
// , SiteConfigUtility.Default.GetSiteLevelNozzleIdByLogicalNozzleId(pump.PumpId, a?.Transaction?.Nozzle?.LogicalId ?? 0),
// 'B',
// gradeFriendlyName,
// a.Transaction.Volumn,
// a.Transaction.Amount),
// posSqlConnection);
var updatePumpOnFuelingCommand = dbCommandCreator();
updatePumpOnFuelingCommand.Connection = posSqlConnection;
updatePumpOnFuelingCommand.CommandText =
string.Format("Update jy_info set youpin = N'{2}', qty= {3}, amount= {4} where jihao = '{0}';"
, (bindingNozzleExtraInfo?.SiteLevelNozzleId ?? -1),
"",
(bindingNozzleExtraInfo?.ProductName ?? "undefined"),
a.Transaction.Volumn / Math.Pow(10, pump.VolumeDecimalDigits),
a.Transaction.Amount / Math.Pow(10, pump.AmountDecimalDigits));
logger.LogDebug("updatePumpOnFuelingCommand: " + updatePumpOnFuelingCommand.CommandText);
posSqlConnection.Open();
updatePumpOnFuelingCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
logger.LogError("executing updatePumpOnFuelingCommand failed, exception detail: " + ex);
}
}
}
else
{
#region totalizer value reading
double volumeTotalizerValue = 0, amountTotalizerValue = 0;
if (a.Transaction.VolumeTotalizer.HasValue)
{
/*indicates this kind of device will carry totalizer with trx, use it directly*/
volumeTotalizerValue = a.Transaction.VolumeTotalizer.Value / Math.Pow(10, pump.VolumeDecimalDigits);
amountTotalizerValue = a.Transaction.AmountTotalizer ?? -1;
}
else
{
/*indicates this kind of device will NOT carry totalizer with trx,
* have to query it*/
var queried = await fdcServerHostApp.GetFuelPointTotalsAsync(pump.PumpId, a.Transaction.Nozzle.LogicalId);
volumeTotalizerValue = queried.Item2;
amountTotalizerValue = queried.Item1;
}
#endregion
var bindingNozzleExtraInfo = this.fdcServerHostApp.GetNozzleExtraInfos()?.FirstOrDefault(f => f.PumpId == pump.PumpId
&& f.NozzleLogicalId == a.Transaction.Nozzle.LogicalId);
var posSqlConnection = dbConnCreator();
using (posSqlConnection)
{
try
{
//var rawProductNo = nozzleProductConfig.FirstOrDefault(f => f.PumpId == pump.PumpId
// && f.NozzleLogicalId == a.Transaction.Nozzle.LogicalId)?.ProductBarcode;
//var totalizer = SiteConfigUtility.Default.GetTotalizer(e.Fuelling.Pump.Id, e.Fuelling.Nozzle.Id);
var updateFuelingTrxDoneCommand = dbCommandCreator();
updateFuelingTrxDoneCommand.Connection = posSqlConnection;
updateFuelingTrxDoneCommand.CommandText =
string.Format(
"insert xiaofei2 (jihao, youpin, qty, danjia, amount, xf_date, xf_time, liushuino, fzqty, fzamount)" +
" values({0}, N'{1}', {2}, {3}, {4}, '{5}', '{6}', {7}, {8}, {9})",
(bindingNozzleExtraInfo?.SiteLevelNozzleId ?? -1),
(bindingNozzleExtraInfo?.ProductName ?? "undefined"),
a.Transaction.Volumn / Math.Pow(10, pump.VolumeDecimalDigits),
a.Transaction.Price / Math.Pow(10, pump.PriceDecimalDigits),
a.Transaction.Amount / Math.Pow(10, pump.AmountDecimalDigits),
DateTime.Now.Date.ToString("yyyy-MM-dd"),
DateTime.Now.ToString("HH:mm:ss"),
a.Transaction.SequenceNumberGeneratedOnPhysicalPump,
volumeTotalizerValue,
amountTotalizerValue);
logger.LogInformation("updateFuelingTrxDoneCommand: " + updateFuelingTrxDoneCommand.CommandText);
posSqlConnection.Open();
updateFuelingTrxDoneCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
logger.LogError("executing updateFuelingTrxDoneCommand failed, exception detail: " + ex);
}
}
using (var sqlConnection = dbConnCreator())
{
try
{
//var rawProductNo = nozzleProductConfig.First(f => f.PumpId == pump.PumpId
// && f.NozzleLogicalId == operatingNozzleLogicalId).ProductBarcode;
//var gradeFriendlyName = rawProductNameToPosProductNameMapping[rawProductNo.ToString()];
//var updatePumpOnFuelingCommand =
// new SqlCommand(string.Format("Update jy_info set [status] = '{1}', youpin = N'{2}', qty= {3}, amount= {4} where jihao = '{0}'"
// , SiteConfigUtility.Default.GetSiteLevelNozzleIdByLogicalNozzleId(pump.PumpId, a?.Transaction?.Nozzle?.LogicalId ?? 0),
// 'B',
// gradeFriendlyName,
// a.Transaction.Volumn,
// a.Transaction.Amount),
// posSqlConnection);
var updatePumpOnFuelingCommand = dbCommandCreator();
updatePumpOnFuelingCommand.Connection = sqlConnection;
updatePumpOnFuelingCommand.CommandText =
string.Format("Update jy_info set fzqty = '{1}', fzamount={2} where jihao = '{0}'",
(bindingNozzleExtraInfo?.SiteLevelNozzleId ?? -1),
volumeTotalizerValue,
amountTotalizerValue);
logger.LogDebug("updatePumpOnFuelingCommand: " + updatePumpOnFuelingCommand.CommandText);
sqlConnection.Open();
updatePumpOnFuelingCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
logger.LogError("executing updatePumpOnFuelingCommand failed, exception detail: " + ex);
}
}
}
}
catch (Exception exxx) { }
};
return true;
}
}
}