Files

493 lines
20 KiB
C#
Executable File

using Microsoft.Extensions.DependencyInjection;
using Opsoft.Data;
using Opsoft.NetCore.O3App.Domain;
using Opsoft.NetCore.O3App.Domain.Interface;
using Opsoft.NetCore.O3App.Domain.UnitOfWork.Energy.IRepositories;
using Opsoft.NetCore.O3App.Domain.UnitOfWork.WMS.Model;
using Opsoft.NetCore.O3App.Shared;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Serialization;
namespace Opsoft.NetCore.O3App.Repositories.Energy
{
[MapTo(typeof(IElectricityRepository), ServiceLifetime.Transient)]
public class ElectricityRepository : RepositoryBase<BaseEntity>, IElectricityRepository
{
public async Task<List<BarCodeMsg>> GetBarcodeMsg(string barcode, string orgId)
{
try
{
await using APPServerContext dbContext = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
barcode = GetAnalysisBarCode(dbContext, barcode, string.Empty);
var sqlStr = $@"SELECT
A.*,
B.FNUMBER AS FITEMNUMBER,
B.FNAME AS FITEMNAME,
B.FSPECIFICATION,
B.FKFPERIOD,
B.FKFUNIT,
(CASE WHEN ISNULL(A.FKFDATE,'')<> '' AND B.FISKFPERIOD = 1 AND B.FKFPERIOD > 0 THEN(CASE B.FKFUNIT WHEN 2 THEN DATEADD(YEAR, B.FKFPERIOD, A.FKFDATE) WHEN 1 THEN DATEADD(MONTH, B.FKFPERIOD, A.FKFDATE) ELSE DATEADD(DAY, B.FKFPERIOD, A.FKFDATE) END) ELSE NULL END) AS FEXPIRYDATE
,C.FNAME AS FSTOCKNAME
,D.FNAME AS FSTOCKLOCNAME
,E.FNAME AS FAUXPROPNAME
,F.FNAME AS FSUPPLYNAME
,G.FNAME AS FSTOCKSTATUSNAME
FROM T_BD_BARCODERS A
LEFT JOIN T_BD_MATERIAL B ON B.FINTERID = A.FMATERIALID
LEFT JOIN T_BD_STOCK C ON A.FSTOCKID=C.FINTERID
LEFT JOIN T_BAS_FLEXVALUESDETAIL D ON A.FSTOCKLOCID=D.FINTERID
LEFT JOIN T_BD_FLEXSITEMDETAILV E ON A.FAUXPROPID=E.FINTERID
LEFT JOIN SYS_SUPPLIER F ON A.FSUPPLYID=F.FINTERID
LEFT JOIN T_BD_STOCKSTATUS G ON A.FSTOCKSTATUSID=G.FNUMBER
WHERE A.FBARCODE ='{barcode}'";
if (!orgId.IsNullOrWhiteSpace())
{
sqlStr += $" AND (A.FCOMPANYID='{orgId}' OR A.FCOMPANYID='') ";
}
return await dbContext.Database.SqlQueryAsync<BarCodeMsg>(sqlStr);
}
catch (Exception e)
{
throw new O3AppException(e.Message);
}
}
public async Task<List<BarcodeCheckInfo>> GetBarcodeCheckInfoAsync(string barcode)
{
try
{
await using APPServerContext dbContext = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
var sqlStr = $@"SELECT * FROM T_BD_BARCODERS
WHERE FBARCODE ='{barcode}'";
return await dbContext.Database.SqlQueryAsync<BarcodeCheckInfo>(sqlStr);
}
catch (Exception e)
{
throw new O3AppException(e.Message);
}
}
public async Task<string> GetInfoAsync(string sql, int billType)
{
await using APPServerContext dbContext = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
try
{
switch (billType)
{
case 1:
return JsonOperator.JsonSerialize(
await dbContext.Database.SqlQueryAsync<EnergyInfoDto>(sql));
case 2:
return JsonOperator.JsonSerialize(
await dbContext.Database.SqlQueryAsync<BarcodeInfoDto2>(sql));
case 3:
return JsonOperator.JsonSerialize(
await dbContext.Database.SqlQueryAsync<EnergyInfoDto2>(sql));
case 4:
return JsonOperator.JsonSerialize(
await dbContext.Database.SqlQueryAsync<RealTimePower>(sql));
case 5:
return JsonOperator.JsonSerialize(
await dbContext.Database.SqlQueryAsync<DataList>(sql));
}
}
catch (Exception e)
{
throw new O3AppException(e.Message);
}
return "";
}
public async Task<DataTable> GetDataTale(string sql)
{
await using APPServerContext dbContext = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
try
{
DataTable dt = dbContext.Database.SqlQuery(sql);
return dt;
}
catch (Exception e)
{
throw new O3AppException(e.Message);
}
}
public async Task<List<BD_BARCODERSENTRY>> GetBoxedBarcodeAsync(string sql)
{
await using APPServerContext dbContext = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
try
{
return await dbContext.Database.SqlQueryAsync<BD_BARCODERSENTRY>(sql);
}
catch (Exception e)
{
throw new O3AppException(e.Message);
}
}
public async Task<List<SpMeBarcodeInfoDto>> GetSpMeBarcodeInfoAsync(string sql)
{
await using APPServerContext dbContext = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
try
{
return await dbContext.Database.SqlQueryAsync<SpMeBarcodeInfoDto>(sql);
}
catch (Exception e)
{
throw new O3AppException(e.Message);
}
}
public async Task<List<InBoxBarcodeInfoDto>> GetInBoxBarcodeInfoAsync(string sql)
{
await using APPServerContext dbContext = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
try
{
return await dbContext.Database.SqlQueryAsync<InBoxBarcodeInfoDto>(sql);
}
catch (Exception e)
{
throw new O3AppException(e.Message);
}
}
public async Task<string> GetBoxRootInfoXmlStrAsync(string sql)
{
await using APPServerContext dbContext = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
try
{
var rootInfo = await dbContext.Database.SqlQueryAsync<BD_BARCODERS4>(sql);
if (rootInfo.Count == 0)
{
throw new O3AppException("箱码底阶明细未找到,请重新扫描!");
}
else
{
var result = rootInfo.FirstOrDefault();
return result.FBOXINFO;
}
}
catch (Exception ex)
{
throw new O3AppException(ex.StackTrace, ex);
}
}
public async Task<string> GetUnit(string sql)
{
await using APPServerContext dbContext = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
try
{
string rootInfo = dbContext.Database.SqlQueryString(sql);
if (rootInfo == string.Empty)
{
throw new O3AppException("无单位数据。");
}
else
{
var result = rootInfo;
return result;
}
}
catch (Exception ex)
{
throw new O3AppException(ex.StackTrace, ex);
}
}
public async Task<decimal> GetBaseUnitQtyAsync(string materialId, string unitId, string baseUnitId, decimal qty)
{
await using APPServerContext dbContext = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
try
{
return GetMaterQty2(dbContext, materialId, unitId, baseUnitId, qty);
}
catch (Exception e)
{
throw new O3AppException(e.Message);
}
}
public async Task<int> UpdateBarcodeQtyAsync(string sql)
{
await using APPServerContext dbContext = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
try
{
await dbContext.Database.BeginTransactionAsync().ConfigureAwait(false);
var count = await dbContext.Database.SqlNonQueryAsync(sql);
await dbContext.Database.CommitTransactionAsync().ConfigureAwait(false);
if (count == 0)
{
throw new Exception("更新被拆分条码数量失败,条码已不存在!");
}
if (count > 1)
{
throw new Exception("更新被拆分条码数量失败,存在重复的条码!");
}
return count;
}
catch (Exception e)
{
await dbContext.Database.RollbackTransactionAsync().ConfigureAwait(false);
throw new O3AppException(e.Message);
}
}
public async Task<int> ExecuteSqlNoneQueryAsync(string sql)
{
await using APPServerContext dbContext = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
try
{
await dbContext.Database.BeginTransactionAsync().ConfigureAwait(false);
var count = await dbContext.Database.SqlNonQueryAsync(sql);
await dbContext.Database.CommitTransactionAsync().ConfigureAwait(false);
return count;
}
catch (Exception e)
{
await dbContext.Database.RollbackTransactionAsync().ConfigureAwait(false);
throw new O3AppException(e.Message);
}
}
public async Task<int> ExecuteSqlNoneQueryAsync(List<string> sqlStrList)
{
await using APPServerContext dbContext = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
try
{
var count = 0;
await dbContext.Database.BeginTransactionAsync().ConfigureAwait(false);
foreach (var sql in sqlStrList)
{
await dbContext.Database.SqlNonQueryAsync(sql);
}
await dbContext.Database.CommitTransactionAsync().ConfigureAwait(false);
return count;
}
catch (Exception e)
{
await dbContext.Database.RollbackTransactionAsync().ConfigureAwait(false);
throw new O3AppException(e.Message);
}
}
public async Task<string> CreateBillNo(string billInfoJson)
{
await using APPServerContext dbContext = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
try
{
return CreateBilllNO(dbContext, billInfoJson, "STK_BARCODESPLIT", "T_BD_BARCODERSCHANGE", "FBILLNO", null, null);
}
catch (Exception e)
{
throw new O3AppException(e.Message);
}
}
public async Task<string> CreateBarcode(string billDataJson, string prgKey)
{
await using APPServerContext dbContext = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
try
{
return CreateBarCodeNo(dbContext, billDataJson, prgKey, "T_BD_BARCODERS", "FBARCODE", null, null);
}
catch (Exception e)
{
throw new O3AppException(e.Message);
}
}
public async Task<Hashtable> GetSystemParameters(string key)
{
await using APPServerContext dbContext = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
try
{
return dbContext.PBValue(key);
}
catch (Exception e)
{
throw new O3AppException(e.Message);
}
}
/// <summary>
/// 拆箱进行的操作
/// </summary>
/// <param name="inputDto"></param>
/// <returns></returns>
public async Task SplitBox(OutBoxInputDto inputDto)
{
await using APPServerContext dbContext = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
try
{
await dbContext.Database.BeginTransactionAsync().ConfigureAwait(false);
var rootInfo = await GetBoxRootInfo(inputDto.BoxCode);
#region 更新装箱信息
List<string> sqlList = new List<string>();
var boxedBarcodeInfoSqlStr = $"SELECT * FROM T_BD_BARCODERSENTRY WHERE FBOXCODE='{inputDto.BoxCode}'";
var boxedBarcodeInfoList = await dbContext.Database.SqlQueryAsync<BD_BARCODERSENTRY>(boxedBarcodeInfoSqlStr);
if (inputDto.OutBoxInfo.Count > 0)
{
foreach (var entry in boxedBarcodeInfoList)
{
if (!inputDto.OutBoxInfo.Exists(o => o.DetailId == entry.FDETAILID))
{
sqlList.Add($"DELETE T FROM T_BD_BARCODERSENTRY T WHERE T.FDETAILID='{entry.FDETAILID}'");
sqlList.Add($"UPDATE T_BD_BARCODERS SET FINBOX=0 WHERE FBARCODE='{entry.FBARCODE}'");
var item = rootInfo.FirstOrDefault(o => o.FBARCODE == entry.FBARCODE);
rootInfo.Remove(item);
}
}
if (sqlList.Count > 0)
{
sqlList.Add(
$@"UPDATE T_BD_BARCODERS
SET FQTY={inputDto.OutBoxInfo.Sum(o => o.Qty)},
FBASEQTY={inputDto.OutBoxInfo.Sum(o => o.BaseQty)}
WHERE FBARCODE='{inputDto.BoxCode}'");
sqlList.Add(
$@"UPDATE T_BD_BARCODERS4
SET FBOXINFO='{XmlHelper.XmlSerialize(rootInfo, Encoding.UTF8, true)}'
WHERE FBARCODE='{inputDto.BoxCode}'");
}
//执行第一步sql
foreach (string itemSql in sqlList)
{
await dbContext.Database.SqlNonQueryAsync(itemSql);
}
sqlList.Clear();
int entryId = 1;
boxedBarcodeInfoList = await dbContext.Database.SqlQueryAsync<BD_BARCODERSENTRY>(boxedBarcodeInfoSqlStr);
boxedBarcodeInfoList = boxedBarcodeInfoList.OrderBy(o => o.FENTRYID).ToList();
foreach (var entry in boxedBarcodeInfoList)
{
sqlList.Add($"UPDATE T_BD_BARCODERSENTRY SET FENTRYID={entryId++} WHERE FBARCODE='{entry.FBARCODE}'");
}
//执行第二步sql:表体数据重新给序号
foreach (string itemSql in sqlList)
{
await dbContext.Database.SqlNonQueryAsync(itemSql);
}
}
#endregion
#region 清空装箱信息
else
{
sqlList.Add(
$@"UPDATE T1
SET T1.FINBOX=0
FROM T_BD_BARCODERS T1
INNER JOIN T_BD_BARCODERSENTRY T2 ON T2.FBARCODE=T1.FBARCODE
WHERE T2.FBOXCODE='{inputDto.BoxCode}'");
sqlList.Add($"DELETE FROM T_BD_BARCODERSENTRY WHERE FBOXCODE='{inputDto.BoxCode}'");
sqlList.Add($"DELETE FROM T_BD_BARCODERS4 WHERE FBARCODE='{inputDto.BoxCode}'");
sqlList.Add($"DELETE FROM T_BD_BARCODERS WHERE FBARCODE='{inputDto.BoxCode}'");
foreach (string itemSql in sqlList)
{
await dbContext.Database.SqlNonQueryAsync(itemSql);
}
}
#endregion
await dbContext.Database.CommitTransactionAsync().ConfigureAwait(false);
}
catch (Exception e)
{
await dbContext.Database.RollbackTransactionAsync().ConfigureAwait(false);
throw new O3AppException(e.Message);
}
}
/// <summary>
/// 获取最底阶条码信息
/// </summary>
/// <param name="barcode"></param>
/// <returns></returns>
private async Task<BARCODE> GetBoxRootInfo(string barcode)
{
try
{
var boxRootInfoXmlSqlStr = $"SELECT * FROM T_BD_BARCODERS4 WHERE FBARCODE='{barcode}'";
var boxRootInfoXml = await GetBoxRootInfoXmlStrAsync(boxRootInfoXmlSqlStr);
return XmlHelper.XmlDeserialize<BARCODE>(boxRootInfoXml, Encoding.UTF8);
}
catch (Exception e)
{
throw new O3AppException(e.Message);
}
}
/// <summary>
/// 查询DataTable
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public async Task<DataSet> GetDataSet(List<string> sqlList)
{
await using APPServerContext dbContext = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
try
{
DataSet dsData = new DataSet();
foreach (string itemSql in sqlList)
{
dsData.Tables.Add(dbContext.Database.SqlQuery(itemSql).Copy());
}
return dsData;
}
catch (Exception e)
{
throw new O3AppException(e.Message);
}
}
/// <summary>
/// <see cref="IBarcodeService.GetBarcodeCheckInfo(string)"/>
/// </summary>
/// <param name="barcode"></param>
/// <returns></returns>
public async Task<List<BarcodeCheckInfo>> GetBarcodeCheckInfo(string barcode, string orgId)
{
try
{
await using APPServerContext dbContext = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
var sqlStr = $@"SELECT * FROM T_BD_BARCODERS WHERE FBARCODE ='{barcode}'";
if (!orgId.IsNullOrWhiteSpace())
{
sqlStr += $" AND (FCOMPANYID='{orgId}' OR FCOMPANYID='')";
}
return await dbContext.Database.SqlQueryAsync<BarcodeCheckInfo>(sqlStr);
}
catch (Exception e)
{
throw new O3AppException(e.Message);
}
}
[Serializable]
[XmlRoot("BARCODE")]
public class BARCODE : List<DATA>
{
}
[Serializable]
[XmlRoot("DATA")]
public class DATA
{
[XmlAttribute("FBARCODE")]
public string FBARCODE { get; set; }
[XmlAttribute("FQTY")]
public decimal FQTY { get; set; }
}
}
}