Files

508 lines
23 KiB
C#
Executable File
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
using Microsoft.Extensions.DependencyInjection;
using Newtonsoft.Json.Linq;
using Opsoft.Data;
using Opsoft.NetCore.O3App.Domain;
using Opsoft.NetCore.O3App.Domain.Enums.ReportCodes;
using Opsoft.NetCore.O3App.Domain.Interface;
using Opsoft.NetCore.O3App.Domain.Models;
using Opsoft.NetCore.O3App.Domain.UnitOfWork.BaseData.IRepositories;
using Opsoft.NetCore.O3App.Domain.UnitOfWork.BaseData.Model;
using Opsoft.NetCore.O3App.Domain.UnitOfWork.IPadReport.Model;
using Opsoft.NetCore.O3App.Shared;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
namespace Opsoft.NetCore.O3App.Repositories.BaseData
{
[MapTo(typeof(ISysConfigDataRepository), ServiceLifetime.Transient)]
public class SysConfigDataRepository : RepositoryBase<BaseEntity>, ISysConfigDataRepository
{
public SysConfigDataRepository() : base()
{
}
#region 获取APK版本
/// <summary>
/// 获取APK版本
/// </summary>
/// <returns></returns>
public async Task<TBdAppversion> GetLatestAppInfo(int apkTypeD)
{
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
var appversions = await context.Database.SqlQueryAsync<TBdAppversion>($"{TBdAppversion.GetSelectSql()} WHERE FAPPTYPE={apkTypeD} ORDER BY FVersionCode DESC");
Guard.For<O3AppException>(() => appversions == null, "App版本信息获取失败");
return appversions.FirstOrDefault();
}
#endregion
#region 获取仓位信息
/// <summary>
/// 获取仓位信息
/// </summary>
/// <param name="paramaters"></param>
/// <returns></returns>
public async Task<List<BD_FLEXVALUES>> GetAllFlexValues(string finterid)
{
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
Hashtable PBCursor = context.PBValue("FLEXCHAR,C,");
string fLexChar = PBCursor["FLEXCHAR"].ToStringEx();
string sql = "";
DataSet dts = new DataSet();//用于存储仓库仓位集值
if (!finterid.IsNullOrEmpty())
{
sql = @" SELECT
FDETAILID
,T02.FNUMBER AS FSTOCKNUMBER
FROM T_BD_STOCKFLEXITEM T01
LEFT JOIN T_BD_STOCK T02 ON T01.FINTERID = T02.FINTERID
WHERE T01.FINTERID='" + finterid + "'";
}
DataTable dtDetailIDs = context.Database.SqlQuery(sql);//仓库仓位集
await Task.Run(() =>
{
foreach (DataRow row in dtDetailIDs.Rows)
{
sql = @$"SELECT
T01.FDETAILID
,T01.FINTERID
,'{row["FSTOCKNUMBER"]}' AS FSTOCKNUMBER
,T02.FNUMBER AS FNUMBER
FROM T_BD_STOCKFLEXDETAIL T01
LEFT JOIN T_BAS_FLEXVALUESENTRY T02 ON T01.FFLEXENTRYID = T02.FDETAILID
WHERE FBODYID = '" + row["FDETAILID"] + "'";
dts.Tables.Add(context.Database.SqlQuery(sql));
}
});
List<BD_FLEXVALUES> retdata = new List<BD_FLEXVALUES>();
StockFlex(dts, retdata, 0, string.Empty, fLexChar);
return retdata;
}
#endregion
#region 根据仓库的仓位配置生成仓位编号
/// <summary>
/// 根据仓库的仓位配置生成仓位编号
/// </summary>
/// <param name="dsFLEX"></param>
/// <param name="dtFlexValues"></param>
/// <param name="dtIndex"></param>
/// <param name="FLEXVALUENUMBER"></param>
/// <param name="fLexChar"></param>
public void StockFlex(DataSet dsFLEX, List<BD_FLEXVALUES> dtFlexValues, int dtIndex, string FLEXVALUENUMBER, string fLexChar)//用来递归组合仓位代码,
{
if (dsFLEX.Tables.Count > dtIndex + 1)//当dataset里面的数量大于索引,按顺序组合代码
{
foreach (DataRow item in dsFLEX.Tables[dtIndex].Rows)
{
StockFlex(dsFLEX, dtFlexValues, dtIndex + 1,
FLEXVALUENUMBER + item["FNUMBER"].ToStringEx() + fLexChar, fLexChar);
}
}
else
{
foreach (DataRow item in dsFLEX.Tables[dtIndex].Rows)//当结果为最后,结束递归,将结果传出去
{
BD_FLEXVALUES data = new BD_FLEXVALUES();
data.FSTOCKINTERID = item["FINTERID"].ToStringEx();
data.FNUMBER = FLEXVALUENUMBER + item["FNUMBER"].ToStringEx();//仓位代码
data.FSTOCKNUMBER = item["FSTOCKNUMBER"].ToStringEx();
dtFlexValues.Add(data);
}
}
}
#endregion
#region 根据仓库代码和组织ID获取仓库信息
/// <summary>
/// 根据仓库代码和组织ID获取仓库信息,如果组织为空使用ToKen中的组织ID
/// </summary>
/// <param name="data">仓库代码和组织ID</param>
/// <returns>retData——T_BD_STOCK(内码,代码,仓库属性,是否启用仓位管理)</returns>
public async Task<List<T_BD_STOCK>> GetAllStock(Dictionary<string, string> paramaters)
{
//定义变量
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
string stockNumber = paramaters["stockNumber"].ToStringEx();
string fCompanyID = paramaters["orgid"].ToStringEx();
string sql = string.Empty;
//定义SQL
if (fCompanyID.IsNullOrEmpty())
{
sql = @"select FINTERID,FNUMBER,FSTOCKPROPERTY,FISOPENLOCATION
FROM T_BD_STOCK
WHERE FNUMBER='" + stockNumber + "'";
}
else
{
sql = @"select FINTERID,FNUMBER,FSTOCKPROPERTY,FISOPENLOCATION
FROM T_BD_STOCK
WHERE FNUMBER='" + stockNumber + "' AND (FCOMPANYID='" + fCompanyID + "' OR FCOMPANYID='')";
}
//执行SQL
var retData = await context.Database.SqlQueryAsync<T_BD_STOCK>(sql);
return retData;
}
#endregion
#region 获取轮播图信息,最多6张
/// <summary>
/// 获取轮播图信息,最多6张
/// </summary>
/// <returns>retDatat-图片Base64String</returns>
public async Task<List<T_BAS_IMAGEPAGEVIEW>> GetAllImagePageView()
{
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
string sql = @$"SELECT TOP 6
FIMAGE
FROM T_BAS_IMAGEPAGEVIEW
WHERE(FSTATUS=40)
ORDER BY FNUMBER DESC";
DataTable dtResult = context.Database.SqlQuery(sql);
List<T_BAS_IMAGEPAGEVIEW> retData = new List<T_BAS_IMAGEPAGEVIEW>();
await Task.Run(() =>
{
foreach (DataRow dr in dtResult.Rows)
{
retData.Add(new T_BAS_IMAGEPAGEVIEW
{
FIMAGEBASE64 = Convert.ToBase64String((byte[])(dr["FIMAGE"]))
});
}
});
return retData;
}
#endregion
#region 获取用户备忘录
/// <summary>
/// 获取日志信息
/// </summary>
/// <param name="dateTime"></param>
/// <param name="finterID"></param>
/// <returns></returns>
public async Task<List<T_BD_PERSONDIARY>> GetAllPersonDiary(string dateTime, string finterID)
{
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
string sql = string.Empty;
sql = @"SELECT
FDATE,
FNAME,
FCONTENT,
FSTARTTIME,
FENDTIME,
FINTERID
FROM T_BD_PERSONDIARY
WHERE C_USER='" + LoginInfo.UserID + "'";
if (!dateTime.IsNullOrEmpty())
{
sql += " AND DATEDIFF(DAY,FDATE,'" + dateTime + "')=0";
}
if (!finterID.IsNullOrEmpty())
{
sql += " AND FINTERID='" + finterID + "'";
}
var retData = await context.Database.SqlQueryAsync<T_BD_PERSONDIARY>(sql);
return retData;
}
#endregion
#region 编辑用户备忘录
/// <summary>
/// 编辑日志信息
/// </summary>
/// <param name="paramaters"></param>
/// <returns></returns>
public async Task<List<T_BD_PERSONDIARY>> UpDatePersonDiary(T_BD_PERSONDIARY diary)
{
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
string sql = string.Empty;
sql = " SELECT * FROM T_BD_PERSONDIARY WHERE FINTERID='" + diary.FINTERID + "'";
DataTable tb = context.Database.SqlQuery(sql);
if (tb.Rows.Count > 0)
{
sql =
$"update T_BD_PERSONDIARY set FNAME ='{diary.FNAME}',FCONTENT='{diary.FCONTENT}',FSTARTTIME='{diary.FSTARTTIME}',FENDTIME='{diary.FENDTIME}' WHERE FINTERID='{diary.FINTERID}'";
}
else
{
sql =
$"insert into T_BD_PERSONDIARY (FDATE,C_USER,FNAME,FCONTENT,FSTARTTIME,FENDTIME,FINTERID,UID) values('{diary.FDATE}','{LoginInfo.UserID}','{diary.FNAME}','{diary.FCONTENT}','{diary.FSTARTTIME}','{diary.FENDTIME}','{Guid.NewGuid().ToStringEx()}','{Guid.NewGuid().ToStringEx()}')";
}
var retData = await context.Database.SqlQueryAsync<T_BD_PERSONDIARY>(sql);
return retData;
}
public async Task<string> DeletePersonDiary(string interId)
{
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
var sql = $"DELETE FROM T_BD_PERSONDIARY WHERE FINTERID='{interId}'";
await context.Database.SqlNonQueryAsync(sql);
return "删除成功!";
}
#endregion
#region 获取个人事项
/// <summary>
/// 获取个人事项
/// </summary>
/// <returns></returns>
public async Task<List<T_BD_PERSONALMATTERS>> GetAllPersonalMatters()
{
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
string reData = string.Empty;
reData = @" SELECT
FINFO,FPERSONALDATE
FROM T_BD_PERSONALMATTERS
ORDER BY FPERSONALDATE DESC";
var retData = await context.Database.SqlQueryAsync<T_BD_PERSONALMATTERS>(reData);
return retData;
}
#endregion
#region 编辑个人事项
/// <summary>
/// 编辑个人事项
/// </summary>
/// <param name="paramaters"></param>
/// <returns></returns>
public async Task<List<T_BD_PERSONALMATTERS>> UpdatePersonalMatters(JObject data)
{
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
string FINFO = data["FINFO"].ToStringEx();
string FPERSONALDATE = data["FPERSONALDATE"].ToStringEx();
string UID = Guid.NewGuid().ToString();
string sql = string.Empty;
//定义SQL
sql =
$" INSERT INTO T_BD_PERSONALMATTERS (FINFO,FPERSONALDATE,C_YMD,C_USER,UID,FINTERID) VALUES('{FINFO}','{FPERSONALDATE}',GETDATE(),'{LoginInfo.UserID}','{UID}','{UID}')";
//执行SQL
var retData = await context.Database.SqlQueryAsync<T_BD_PERSONALMATTERS>(sql);
return retData;
}
#endregion
#region 查询系统打印参数配置
public async Task<List<SysPrintData>> SysPrintIteam(SysPrintIteam data)
{
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
string sql = $@"SELECT
FIPIF,--打印机ip
FPORTNUMBER,--端口号
FPRINTNAME,--打印机名称
FMOULD,--模板
FEQUIPMENT--设备号
FROM T_BOS_PRINTSERVER
WHERE FMODELKEY='{data.PrgKey}'";
if (data.IsUsedMac)
{
sql += $" AND FEQUIPMENT='{data.PhoneMAC}'";
}
if (!data.OrgID.IsNullOrWhiteSpace())
{
sql += $" AND FCOMPANYID='{data.OrgID}'";
}
var retData = await context.Database.SqlQueryAsync<SysPrintData>(sql);
return retData;
}
#endregion
#region 查询蓝牙打印机设置
/// <summary>
/// 获取全部蓝牙打印机设置
/// </summary>
/// <returns>btPrinter打印机设置信息</returns>
public async Task<List<Basic>> SysPrintBluetooth(SysPrintIteam data)
{
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
string sql = @"SELECT
FINTERID AS FID
,FNUMBER AS FNO
,FNAME
,FCOMPANYID
FROM T_BAS_BTPRINTER
WHERE FDELETED=0";
if (!data.OrgID.IsNullOrWhiteSpace())
{
sql += $" AND FCOMPANYID='{data.OrgID}'";
}
var retData = await context.Database.SqlQueryAsync<Basic>(sql);
return retData;
}
#endregion
#region 查询基本信息底层接口
/// <summary>
/// 查询基本信息底层接口
/// </summary>
/// <param name="paramaters"></param>
/// <returns></returns>
public async Task<List<Basic>> QueryALLResource(Dictionary<string, string> paramaters)
{
#region 变量
//basicType:1=部门;2=供应商;3=客户;4=设备;5=工序 6=资源;7=工作中心;8=班组;9=班次;10=职员;11=不良原因;12=模具;13=工序暂停;14=安灯预警事件;15=产品
string queryNO = paramaters["basicNO"].ToStringEx();
int queryType = Convert.ToInt32(paramaters["basicType"]);
//组织内码
string orgId = string.Empty;
if (paramaters.ContainsKey("orgId"))
{
orgId = paramaters["orgId"].ToStringEx();
}
//查询sql
string sql = string.Empty;
//apserver服务
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
#endregion
string sqlWhere = "";
if (queryType != 2 &&queryType != 3)
{
sqlWhere = (string.IsNullOrEmpty(queryNO) ? "" : " AND FNUMBER LIKE '%" + queryNO + "%' ") + (string.IsNullOrEmpty(orgId) ? "" : " AND FCOMPANYID = '" + orgId + "' ");
}
switch (queryType)
{
case 1://部门
string strTYPE = paramaters["currentTranTypeq"].ToStringEx().ToUpper();
sql = string.Format(@"
SELECT
S.FINTERID AS FID
,S.FNUMBER AS FNO
,S.FNAME
,S.FCOMPANYID
FROM T_BD_DEPARTMENT S
LEFT JOIN T_BAS_ASSISTANTDATAENTRY S1 ON S.FDEPTPROPERTY=S1.FINTERID
WHERE S.FSTATUS=40 AND S.FDELETED = 0 {0} {1} {2}
ORDER BY S.FNUMBER ASC",
string.IsNullOrEmpty(queryNO) ? "" : " AND S.FNUMBER LIKE '%" + queryNO + "%'",
string.IsNullOrEmpty(orgId) ? "" : " AND S.FCOMPANYID = '" + orgId + "' ",
(strTYPE.Equals("PRD_RESTOCK") || strTYPE.Equals("PRD_PICKMTRL") || strTYPE.Equals("PRD_RETURNMTRL") ||
strTYPE.Equals("PRD_INSTOCK") || strTYPE.Equals("SP_PICKMTRL") || strTYPE.Equals("SP_RETURNMTRL") ||
strTYPE.Equals("SP_INSTOCK")) ? " AND S1.FNUMBER='DP01_SYS' " : "");
break;
case 14://安灯预警事件
sql = $@"
SELECT
S.FINTERID AS FID
,S.FNUMBER AS FNO
,S.FNAME
,B.FNUMBER AS FPHONEID
,S.FCOMPANYID
FROM T_AS_WARNINGEVENT S
LEFT JOIN T_BAS_ASSISTANTDATAENTRY B ON B.FINTERID = S.FTYPEID
WHERE S.FSTATUS=40 AND S.FDELETED = 0 {(string.IsNullOrEmpty(queryNO) ? "" : " AND S.FNUMBER LIKE '%" + queryNO + "%'")} {(string.IsNullOrEmpty(orgId) ? "" : " AND S.FCOMPANYID = '" + orgId + "' ")}
ORDER BY S.FNUMBER ASC";
break;
case 15://BOM
sql = $@"
SELECT
DISTINCT
S.FINTERID AS FID
,S.FNUMBER AS FNO
,S.FNUMBER +'=>'+ S.FNAME AS FNAME
,S.FCOMPANYID
FROM T_BD_MATERIAL S
--INNER JOIN T_BD_BOM S2 ON S.FINTERID=S2.FMATERIALID
--AND S2.FSTATUS=40 AND S2.FDELETED = 0
WHERE S.FSTATUS=40 AND S.FDELETED = 0 AND S.FERPCLSID=2 AND S.FSUITE=0 {(string.IsNullOrEmpty(queryNO) ? "" : " AND S.FNUMBER='" + queryNO + "'")} {(string.IsNullOrEmpty(orgId) ? "" : " AND S.FCOMPANYID='" + orgId + "' ")}
ORDER BY S.FNUMBER ASC";
break;
case 16://辅助资料
sql = string.Format($@"
SELECT
A.FINTERID AS FID
,A.FNUMBER AS FNO
,A.FDATAVALUE AS FNAME
,B.FCOMPANYID
FROM T_BAS_ASSISTANTDATAENTRY A
LEFT JOIN T_BAS_ASSISTANTDATA B ON B.FINTERID = A.FID
WHERE A.FDELETED = 0 AND B.FNUMBER = '{queryNO}'
ORDER BY A.FNUMBER ASC");
break;
case 11://不良原因
sql = string.Format($@"SELECT A.FBADREASONID AS FID,
C.FNUMBER AS FNO,
C.FNAME,
B.FNUMBER AS FPROCESSNO,
B.FCOMPANYID
FROM T_SFC_PROCESSENTRY A
LEFT JOIN T_SFC_PROCESS B ON A.FINTERID=B.FINTERID
LEFT JOIN T_QM_BADREASONS C ON A.FBADREASONID=C.FINTERID
WHERE B.FSTATUS=40 AND B.FDELETED = 0 {(string.IsNullOrEmpty(queryNO) ? "" : " AND B.FNUMBER LIKE '%" + queryNO + "%'")} {(string.IsNullOrEmpty(orgId) ? "" : " AND B.FCOMPANYID = '" + orgId + "' ")}
ORDER BY C.FNUMBER ASC");
break;
default:
sql = $@"
SELECT
FINTERID AS FID
,FNUMBER AS FNO
,FNAME
,FCOMPANYID
,C_YMD AS FTIME
FROM {(BasicTableName)queryType}
WHERE FSTATUS=40 AND FDELETED = 0 {sqlWhere}
ORDER BY FNUMBER ASC";
break;
}
return await context.Database.SqlQueryAsync<Basic>(sql);
}
#endregion
#region 获取用户信息
/// <summary>
/// 获取用户信息
/// </summary>
/// <param name="paramaters"></param>
/// <returns></returns>
public async Task<LoginRequest> QueryLoginUserInfo(string useNO)
{
try
{
using APPServerContext context = new APPServerContext(LoginInfo.DBAias, LoginInfo.UserID);
string reData = string.Empty;
reData = $@" SELECT
USER_ID AS UserID
,USER_NAME AS UserName
,PR_PASSWORD AS Password
,IS_PDA AS IsPDA
,PATYPE AS PAType
,EMAIL
,PA_ID AS PAID
,ERPID
,ISDEFAULT
,CARDID
,DOMAINUSER
,LASTLOGINTIME
,LASTCPTIME
,PHOTO
FROM SYS_LOGINUSER
WHERE USER_ID='{useNO.ToStringEx()}'";
var retData = await context.Database.SqlQueryAsync<LoginRequest>(reData);
if (retData != null && retData.Count > 0)
{
return retData[0];
}
else
{
return new LoginRequest();
}
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
}
}