|
|
using Dapper;
|
|
|
using Estsh.Core.Base;
|
|
|
using Estsh.Core.Dapper;
|
|
|
using Estsh.Core.IRepositories;
|
|
|
using Estsh.Core.Model.Result;
|
|
|
using Estsh.Core.Models;
|
|
|
using Estsh.Core.Repository.IRepositories;
|
|
|
using System.Collections;
|
|
|
using System.Data;
|
|
|
using System.Text;
|
|
|
|
|
|
/***************************************************************************************************
|
|
|
*
|
|
|
* 更新人：sitong.dong
|
|
|
* 描述：SAP指令导入模块数据库访问类
|
|
|
* 修改时间：2022.06.22
|
|
|
* 修改日志：系统迭代升级
|
|
|
*
|
|
|
**************************************************************************************************/
|
|
|
namespace Estsh.Core.Repositories
|
|
|
{
|
|
|
/// <summary>
|
|
|
/// 数据库访问类，处理SAP指令导入模块的数据
|
|
|
/// </summary>
|
|
|
public class SAPOrderImportRepository : BaseRepository<BaseEntity>, ISAPOrderImportRepository
|
|
|
{
|
|
|
public SAPOrderImportRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
|
|
|
{
|
|
|
}
|
|
|
|
|
|
#region 公共方法
|
|
|
|
|
|
/// <summary>
|
|
|
/// 根据零件号查询零件信息
|
|
|
/// </summary>
|
|
|
/// <param name="partNo">零件号</param>
|
|
|
/// <returns>零件信息</returns>
|
|
|
public List<SysPart> getPartInfo(string partNo)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string SqlString = "SELECT * FROM dbo.sys_part WHERE part_no=@partNo AND enabled='Y'";
|
|
|
DynamicParameters Params = new DynamicParameters();
|
|
|
Params.Add("@partNo", partNo);
|
|
|
List<SysPart> result = dbConn.Query<SysPart>(SqlString, Params).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 根据厂区代码查询厂区信息
|
|
|
/// </summary>
|
|
|
/// <param name="factoryCode">厂区代码</param>
|
|
|
/// <returns>厂区信息</returns>
|
|
|
public List<SysFactory> getFactoryInfo(string factoryCode)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string SqlString = "SELECT * FROM dbo.sys_factory WHERE factory_code = @factory_code AND enabled='Y'";
|
|
|
DynamicParameters Params = new DynamicParameters();
|
|
|
Params.Add("@factory_code", factoryCode);
|
|
|
List<SysFactory> result = dbConn.Query<SysFactory>(SqlString, Params).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取工厂代码
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public List<SysFactory> getFactoryCode()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sqlString = "SELECT DISTINCT factory_code from sys_factory where enabled = 'Y' ";
|
|
|
List<SysFactory> result = dbConn.Query<SysFactory>(sqlString).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 根据库存地查询库存地信息
|
|
|
/// </summary>
|
|
|
/// <param name="erpWarehouse">库存地</param>
|
|
|
/// <returns>库存地信息</returns>
|
|
|
public List<SysWarehouse> getErpWarehouseInfo(string erpWarehouse)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string SqlString = "SELECT * from sys_zone WHERE erp_warehouse = @erpWarehouse AND enabled = 'Y' ";
|
|
|
DynamicParameters Params = new DynamicParameters();
|
|
|
Params.Add("@erpWarehouse", erpWarehouse);
|
|
|
List<SysWarehouse> result = dbConn.Query<SysWarehouse>(SqlString, Params).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 根据供应商代码获取供应商信息
|
|
|
/// </summary>
|
|
|
/// <param name="vendorCode"></param>
|
|
|
/// <returns></returns>
|
|
|
public List<SysVendor> getVendorInfo(string vendorCode)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sqlString = "SELECT * from sys_vendor where enabled = 'Y' AND vendor_code = @vendor_code";
|
|
|
DynamicParameters Params = new DynamicParameters();
|
|
|
Params.Add("@vendor_code", vendorCode);
|
|
|
List<SysVendor> result = dbConn.Query<SysVendor>(sqlString, Params).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 根据客户编码获取客户信息
|
|
|
/// </summary>
|
|
|
/// <param name="customCode"></param>
|
|
|
/// <returns></returns>
|
|
|
public List<SysCustomer> getCustomerInfo(string customCode)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sqlString = "SELECT * from sys_customer where enabled = 'Y' AND customer_code = @customer_code";
|
|
|
DynamicParameters Params = new DynamicParameters();
|
|
|
Params.Add("@customer_code", customCode);
|
|
|
List<SysCustomer> result = dbConn.Query<SysCustomer>(sqlString, Params).ToList();
|
|
|
return result;
|
|
|
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 执行sql语句带事务
|
|
|
/// </summary>
|
|
|
/// <param name="SqlStrings"></param>
|
|
|
/// <param name="Parameters"></param>
|
|
|
/// <returns></returns>
|
|
|
public bool ExecuteSqlTransaction(List<string> SqlStrings)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
IDbTransaction transaction = dbConn.BeginTransaction();
|
|
|
try
|
|
|
{
|
|
|
for (int i = 0; i < SqlStrings.Count; i++)
|
|
|
{
|
|
|
dbConn.Execute(SqlStrings[i], null, transaction);
|
|
|
}
|
|
|
transaction.Commit();
|
|
|
return true;
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
transaction.Rollback();
|
|
|
return false;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 执行sql语句带事务
|
|
|
/// </summary>
|
|
|
/// <param name="SqlStrings"></param>
|
|
|
/// <param name="Parameters"></param>
|
|
|
/// <returns></returns>
|
|
|
public bool ExecuteSqlTransaction(List<string> SqlStrings, List<DynamicParameters> Parameters,ref string msg)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
IDbTransaction transaction = dbConn.BeginTransaction();
|
|
|
try
|
|
|
{
|
|
|
for (int i = 0; i < SqlStrings.Count; i++)
|
|
|
{
|
|
|
dbConn.Execute(SqlStrings[i], Parameters[i], transaction);
|
|
|
}
|
|
|
transaction.Commit();
|
|
|
return true;
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
transaction.Rollback();
|
|
|
msg = ex.Message;
|
|
|
return false;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
#region 采购计划
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取周计划编号
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public string GetWeekNo()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
DynamicParameters parameter = new DynamicParameters();
|
|
|
parameter.Add("@weekNo", null, DbType.String, ParameterDirection.Output, 255);
|
|
|
List<dynamic> depts = dbConn.Query<dynamic>("sys_create_sapo_wo", parameter, commandType: CommandType.StoredProcedure).ToList();
|
|
|
return parameter.Get<string>("@weekNo");
|
|
|
}
|
|
|
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取订单编号
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public string GetOrderNo()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
DynamicParameters parameter = new DynamicParameters();
|
|
|
parameter.Add("@orderNo", null, DbType.String, ParameterDirection.Output, 255);
|
|
|
List<dynamic> depts = dbConn.Query<dynamic>("sys_create_sapo_no", parameter, commandType: CommandType.StoredProcedure).ToList();
|
|
|
return parameter.Get<string>("@orderNo");
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取订单编号
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public string GetMonthOrderNo()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
DynamicParameters parameter = new DynamicParameters();
|
|
|
parameter.Add("@orderNo", null, DbType.String, ParameterDirection.Output, 255);
|
|
|
List<dynamic> depts = dbConn.Query<dynamic>("sys_create_sapo_mo", parameter, commandType: CommandType.StoredProcedure).ToList();
|
|
|
return parameter.Get<string>("@orderNo");
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取插入sap_dspo的语句
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public string InsertSAPOData()
|
|
|
{
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("INSERT INTO dbo.SAP_DSPO (EBELN ,enabled,REVNO ,EVRTP ,LIFNR ,MATNR ,ZDEV_NUM, ");
|
|
|
SqlStringBuilder.Append("UNIT ,ZDATE ,ZCJSJ ,WERKS , LGORT , ZPZPS ,ZSTAS ,BSTRF ,RECUSR ,WEEKNO ) ");
|
|
|
SqlStringBuilder.Append("VALUES(@EBELN ,'Y',@REVNO ,@EVRTP ,@LIFNR ,@MATNR ,@ZDEV_NUM, ");
|
|
|
SqlStringBuilder.Append("@UNIT ,@ZDATE ,@ZCJSJ ,@WERKS , @LGORT , @ZPZPS ,@ZSTAS,@BSTRF,@RECUSR ,@WEEKNO )");
|
|
|
return SqlStringBuilder.ToString();
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取插入sap_dspo的语句
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public string InsertMonthlyPlanData()
|
|
|
{
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("INSERT INTO SAP_IF_DF.[dbo].[SWEB_MONTHLY_PLAN] (EBELN ,REVNO ,EVRTP ,LIFNR ,MATNR ,ZDEV_NUM, ");
|
|
|
SqlStringBuilder.Append("UNIT ,ZDATE ,ZCJSJ ,WERKS , LGORT , ZPZPS ,ZSTAS ,BSTRF ,RECUSR ,WEEKNO ,MODEL_NO) ");
|
|
|
SqlStringBuilder.Append("VALUES(@EBELN ,@REVNO ,@EVRTP ,@LIFNR ,@MATNR ,@ZDEV_NUM, ");
|
|
|
SqlStringBuilder.Append("@UNIT ,@ZDATE ,@ZCJSJ ,@WERKS , @LGORT , @ZPZPS ,@ZSTAS,@BSTRF,@RECUSR ,@WEEKNO ,@MODEL_NO )");
|
|
|
return SqlStringBuilder.ToString();
|
|
|
}
|
|
|
|
|
|
public List<SapDspo> IfZDATE(string zdate, string lifnr, string zcjsj)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string SqlString = "SELECT * FROM dbo.SAP_DSPO WHERE zdate='" + zdate + "' AND lifnr='" + lifnr + "' AND zcjsj='" + zcjsj + "'";
|
|
|
List<SapDspo> result = dbConn.Query<SapDspo>(SqlString).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public bool delDSPO(string zdate, string lifnr)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string SqlString = "update SAP_DSPO set Enabled='N' WHERE zdate='" + zdate + "' AND lifnr='" + lifnr + "'";
|
|
|
int result = dbConn.Execute(SqlString);
|
|
|
return result > 0 ? true : false;
|
|
|
|
|
|
}
|
|
|
}
|
|
|
public string updateSapDspo(string EBELN, string REVNO, int EVRTP, string LIFNR, string MATNR,
|
|
|
string ZDEV_NUM, string UNIT, string PSTYP, string ZDATE, string ZCJSJ, string WERKS,
|
|
|
string LGORT, string ZPZPS, string ZSTAS, decimal BSTRF)
|
|
|
{
|
|
|
string sqlString = string.Format("UPDATE SAP_DSPO SET EBELN = '{0}', REVNO = '{1}', EVRTP = {2}," +
|
|
|
" LIFNR = '{3}', MATNR = '{4}', ZDEV_NUM = '{5}', UNIT = '{6}', PSTYP = '{7}', ZDATE = '{8}', " +
|
|
|
" ZCJSJ = '{9}', WERKS = '{10}', LGORT = '{11}', ZPZPS = '{12}', ZSTAS = '{13}', BSTRF = '{14}',RECYMD = {15},RECHMS = {16} " +
|
|
|
" WHERE EBELN = '{0}' AND EVRTP = {2} ", EBELN, REVNO, EVRTP, LIFNR, MATNR,
|
|
|
ZDEV_NUM, UNIT, PSTYP, ZDATE, ZCJSJ, WERKS, LGORT, ZPZPS, ZSTAS, BSTRF,
|
|
|
"CONVERT(VARCHAR(100),GETDATE(),23)", "CONVERT(VARCHAR(100),GETDATE(),108)");
|
|
|
return sqlString;
|
|
|
}
|
|
|
|
|
|
public string insertSapDspo(string EBELN, string REVNO, int EVRTP, string LIFNR, string MATNR,
|
|
|
string ZDEV_NUM, string UNIT, string PSTYP, string ZDATE, string ZCJSJ, string WERKS,
|
|
|
string LGORT, string ZPZPS, string ZSTAS, decimal BSTRF)
|
|
|
{
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("INSERT INTO SAP_DSPO (EBELN ,enabled,REVNO ,EVRTP ,LIFNR ,MATNR ,ZDEV_NUM, ");
|
|
|
SqlStringBuilder.Append("UNIT ,PSTYP ,ZDATE ,ZCJSJ ,WERKS , LGORT , ZPZPS ,ZSTAS,LOEKZ,BSTRF,PRNNUM) ");
|
|
|
SqlStringBuilder.Append(string.Format("VALUES('{0}','Y','{1}',{2},'{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}',{15},{16} )",
|
|
|
EBELN, REVNO, EVRTP, LIFNR, MATNR, ZDEV_NUM, UNIT, PSTYP, ZDATE, ZCJSJ, WERKS, LGORT, ZPZPS, ZSTAS, "", BSTRF, 0));
|
|
|
return SqlStringBuilder.ToString();
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 通过单号和行号获取订单数据
|
|
|
/// </summary>
|
|
|
/// <param name="ebeln"></param>
|
|
|
/// <param name="evrtp"></param>
|
|
|
/// <returns></returns>
|
|
|
public List<SapDspo> getSapDspoInfo(string ebeln, string evrtp)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string SqlString = "SELECT * FROM SAP_IF_DF.dbo.SAP_DSPO WHERE EBELN = @EBELN AND EVRTP = @EVRTP ";
|
|
|
DynamicParameters Params = new DynamicParameters();
|
|
|
Params.Add("@EBELN", ebeln);
|
|
|
Params.Add("@EVRTP", evrtp);
|
|
|
List<SapDspo> result = dbConn.Query<SapDspo>(SqlString, Params).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
#region 零星出入库
|
|
|
/// <summary>
|
|
|
/// 根据编号和行号获取零星出入库指令信息
|
|
|
/// </summary>
|
|
|
/// <param name="zinstNo"></param>
|
|
|
/// <param name="zeile"></param>
|
|
|
/// <returns></returns>
|
|
|
public List<SapMisco> getSapMiscoInfo(string zinstNo, string zeile)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sqlString = "SELECT * FROM SAP_MISCO WHERE ZINSTNO = @ZINSTNO AND ZEILE = @ZEILE ";
|
|
|
DynamicParameters Params = new DynamicParameters();
|
|
|
Params.Add("@ZINSTNO", zinstNo);
|
|
|
Params.Add("@ZEILE", zeile);
|
|
|
List<SapMisco> result = dbConn.Query<SapMisco>(sqlString, Params).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
/// <summary>
|
|
|
/// 获得更新SAP_MISCO表的sql语句
|
|
|
/// </summary>
|
|
|
/// <param name="ZINSTNO"></param>
|
|
|
/// <param name="ZEILE"></param>
|
|
|
/// <param name="WERKS"></param>
|
|
|
/// <param name="LGORT"></param>
|
|
|
/// <param name="MATNR"></param>
|
|
|
/// <param name="ERFMG"></param>
|
|
|
/// <param name="ERFME"></param>
|
|
|
/// <param name="SOBKZ"></param>
|
|
|
/// <param name="HTEXT"></param>
|
|
|
/// <param name="ZZLZT"></param>
|
|
|
/// <param name="ZACTION"></param>
|
|
|
/// <returns></returns>
|
|
|
public string getUpdateSapMisco(string ZINSTNO, string ZEILE, string WERKS, string LGORT, string MATNR,
|
|
|
string ERFMG, string ERFME, string SOBKZ, string HTEXT, string ZZLZT, string ZACTION)
|
|
|
{
|
|
|
string sqlString = string.Format(" UPDATE SAP_MISCO SET ZINSTNO = '{0}', ZEILE = {1}, WERKS = {2}," +
|
|
|
" LGORT = '{3}', MATNR = '{4}', ERFMG = '{5}', ERFME = '{6}', ZZLZT = '{7}', ZACTION = '{8}',SOBKZ='{9}',HTEXT='{10}', " +
|
|
|
" RECYMD = {11},RECHMS = {12} " +
|
|
|
" WHERE ZINSTNO = '{0}' AND ZEILE = {1} AND ZACTION='{13}' ", ZINSTNO, ZEILE, WERKS, LGORT, MATNR, ERFMG, ERFME, ZZLZT, ZACTION, SOBKZ, HTEXT,
|
|
|
" CONVERT(VARCHAR(100),GETDATE(),23)", "CONVERT(VARCHAR(100),GETDATE(),108)", ZACTION);
|
|
|
return sqlString;
|
|
|
}
|
|
|
/// <summary>
|
|
|
/// 获得插入SAP_MISCO表的sql语句
|
|
|
/// </summary>
|
|
|
/// <param name="ZINSTNO"></param>
|
|
|
/// <param name="ZEILE"></param>
|
|
|
/// <param name="WERKS"></param>
|
|
|
/// <param name="LGORT"></param>
|
|
|
/// <param name="MATNR"></param>
|
|
|
/// <param name="ERFMG"></param>
|
|
|
/// <param name="ERFME"></param>
|
|
|
/// <param name="SOBKZ"></param>
|
|
|
/// <param name="HTEXT"></param>
|
|
|
/// <param name="ZZLZT"></param>
|
|
|
/// <param name="ZACTION"></param>
|
|
|
/// <returns></returns>
|
|
|
public string getInsertSapMisco(string ZINSTNO, string ZEILE, string WERKS, string LGORT, string MATNR,
|
|
|
string ERFMG, string ERFME, string SOBKZ, string HTEXT, string ZZLZT, string ZACTION)
|
|
|
{
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("INSERT INTO SAP_MISCO (ZINSTNO ,ZEILE ,WERKS ,LGORT ,MATNR ,ERFMG,");
|
|
|
SqlStringBuilder.Append("ERFME,SOBKZ,HTEXT,ZZLZT,ZACTION,GUID) ");
|
|
|
SqlStringBuilder.Append(string.Format("VALUES('{0}',{1},{2},'{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}',NEWID())",
|
|
|
ZINSTNO, ZEILE, WERKS, LGORT, MATNR, ERFMG, ERFME, SOBKZ, HTEXT, ZZLZT, ZACTION));
|
|
|
return SqlStringBuilder.ToString();
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
#region 非JIS发运
|
|
|
/// <summary>
|
|
|
/// 根据编号和行号获取非JIS发运指令
|
|
|
/// </summary>
|
|
|
/// <param name="vbeln"></param>
|
|
|
/// <param name="postnr"></param>
|
|
|
/// <returns></returns>
|
|
|
public List<SapDn> getSapDnInfo(string vbeln, string postnr)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sqlString = "SELECT * FROM SAP_DN WHERE VBELN = @VBELN AND POSNR = @POSNR";
|
|
|
DynamicParameters Params = new DynamicParameters();
|
|
|
Params.Add("@VBELN", vbeln);
|
|
|
Params.Add("@POSNR", postnr);
|
|
|
List<SapDn> result = dbConn.Query<SapDn>(sqlString, Params).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取插入sap_dn表的语句
|
|
|
/// </summary>
|
|
|
/// <param name="VBELN"></param>
|
|
|
/// <param name="POSNR"></param>
|
|
|
/// <param name="WERKS"></param>
|
|
|
/// <param name="LGORT"></param>
|
|
|
/// <param name="LFDAT"></param>
|
|
|
/// <param name="KUNNR"></param>
|
|
|
/// <param name="MATNR"></param>
|
|
|
/// <param name="KDMAT"></param>
|
|
|
/// <param name="LFIMG"></param>
|
|
|
/// <param name="MEINS"></param>
|
|
|
/// <returns></returns>
|
|
|
public string getInsertSapDn(string VBELN, string POSNR, string WERKS, string LGORT, string LFDAT,
|
|
|
string KUNNR, string MATNR, string KDMAT, string LFIMG, string MEINS)
|
|
|
{
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("INSERT INTO SAP_DN ([VBELN],[POSNR],[WERKS],[LGORT],[LFDAT],[KUNNR],");
|
|
|
SqlStringBuilder.Append("[MATNR],[KDMAT],[LFIMG],[MEINS],GUID) ");
|
|
|
SqlStringBuilder.Append(string.Format("VALUES('{0}','{1}',{2},'{3}','{4}','{5}','{6}','{7}','{8}','{9}',NEWID())",
|
|
|
VBELN, POSNR, WERKS, LGORT, LFDAT, KUNNR, MATNR, KDMAT, LFIMG, MEINS));
|
|
|
return SqlStringBuilder.ToString();
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
#region SAP供应商退货指令（SMRR/DMR)
|
|
|
|
|
|
/// <summary>
|
|
|
/// 根据编号和行号获取供应商退货指令信息
|
|
|
/// </summary>
|
|
|
/// <param name="zinstno"></param>
|
|
|
/// <param name="zeile"></param>
|
|
|
/// <returns></returns>
|
|
|
public List<SapReto> getSapRetoInfo(string zinstno, string zeile)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sqlString = "SELECT * FROM SAP_RETO WHERE ZINSTNO = @ZINSTNO AND ZEILE = @ZEILE";
|
|
|
DynamicParameters Params = new DynamicParameters();
|
|
|
Params.Add("@ZINSTNO", zinstno);
|
|
|
Params.Add("@ZEILE", zeile);
|
|
|
List<SapReto> result = dbConn.Query<SapReto>(sqlString, Params).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public string getUpdateSapReto(string ZACTION, string ZINSTNO, string ZEILE, string LIFNR, string UMWRK, string MATNR,
|
|
|
string LGORT, string ERFMG, string ERFME, string BKTXT, string ZQMD, string ZZLZT)
|
|
|
{
|
|
|
string sqlString = string.Format(" UPDATE SAP_RETO SET ZACTION = '{0}', LIFNR = '{3}', UMWRK = {4}," +
|
|
|
" MATNR = '{5}', LGORT = '{6}', ERFMG = '{7}', ERFME = '{8}', BKTXT = '{9}', ZQMD = '{10}',ZZLZT='{11}', " +
|
|
|
" RECYMD = {12},RECHMS = {13} " +
|
|
|
" WHERE ZINSTNO = '{1}' AND ZEILE ='{2}' AND ZACTION='{14}' ", ZACTION, ZINSTNO, ZEILE, LIFNR, UMWRK, MATNR, LGORT, ERFMG, ERFME, BKTXT, ZQMD, ZZLZT,
|
|
|
" CONVERT(VARCHAR(100),GETDATE(),23)", "CONVERT(VARCHAR(100),GETDATE(),108)", ZACTION);
|
|
|
return sqlString;
|
|
|
}
|
|
|
|
|
|
public string getInsertSapReto(string ZACTION, string ZINSTNO, string ZEILE, string LIFNR, string UMWRK, string MATNR,
|
|
|
string LGORT, string ERFMG, string ERFME, string BKTXT, string ZQMD, string ZZLZT)
|
|
|
{
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("INSERT INTO SAP_RETO (ZACTION,ZINSTNO,ZEILE,");
|
|
|
SqlStringBuilder.Append("LIFNR,UMWRK,MATNR,LGORT,ERFMG,ERFME,BKTXT,ZQMD,ZZLZT,GUID) ");
|
|
|
SqlStringBuilder.Append(string.Format("VALUES('{0}','{1}',{2},'{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}',NEWID())",
|
|
|
ZACTION, ZINSTNO, ZEILE, LIFNR, UMWRK, MATNR, LGORT, ERFMG, ERFME, BKTXT, ZQMD, ZZLZT));
|
|
|
return SqlStringBuilder.ToString();
|
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
#region SAP NC入库指令
|
|
|
|
|
|
public List<SapMovo> getSapMovoInfo(string refid, string zeile)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sqlString = "SELECT * FROM SAP_MOVO WHERE REFID = @REFID AND ZEILE = @ZEILE";
|
|
|
|
|
|
DynamicParameters Params = new DynamicParameters();
|
|
|
Params.Add("@REFID", refid);
|
|
|
Params.Add("@ZEILE", zeile);
|
|
|
List<SapMovo> result = dbConn.Query<SapMovo>(sqlString, Params).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public string getUpdateSapMovo(string REFID, string ACTION, string ZEILE, string WERKS, string LGORT, string UMLGO,
|
|
|
string MATNR, string UMMAT, string ERFMG, string ERFME, string ZSTAT, string SOBKZ, string HTEXT, string ZZLZT, string SGTXT, string ORDTYP)
|
|
|
{
|
|
|
string sqlString = string.Format(" UPDATE SAP_MOVO SET WERKS = '{0}', LGORT = '{1}', UMLGO = {2}," +
|
|
|
" MATNR = '{3}', UMMAT = '{4}', ERFMG = '{5}', ERFME = '{6}', ZSTAT = '{7}', SOBKZ = '{8}',HTEXT='{9}',ZZLZT='{10}',SGTXT='{11}', " +
|
|
|
" RECYMD = {14},RECHMS = {15} ,ORDTYP='{17}'" +
|
|
|
" WHERE REFID = '{12}' AND ZEILE = {13} AND ACTION='{16}' ", WERKS, LGORT, UMLGO, MATNR, UMMAT, ERFMG, ERFME, ZSTAT, SOBKZ, HTEXT, ZZLZT, SGTXT, REFID, ZEILE,
|
|
|
" CONVERT(VARCHAR(100),GETDATE(),23)", "CONVERT(VARCHAR(100),GETDATE(),108)", ACTION, ORDTYP);
|
|
|
return sqlString;
|
|
|
}
|
|
|
|
|
|
public string getInsertSapMovo(string REFID, string ACTION, string ZEILE, string WERKS, string LGORT, string UMLGO,
|
|
|
string MATNR, string UMMAT, string ERFMG, string ERFME, string ZSTAT, string SOBKZ, string HTEXT, string ZZLZT, string SGTXT, string ORDTYP)
|
|
|
{
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("INSERT INTO SAP_MOVO (REFID,ACTION,ZEILE,WERKS,LGORT,UMLGO,MATNR,UMMAT,ERFMG,ERFME,ZSTAT,SOBKZ,");
|
|
|
SqlStringBuilder.Append("HTEXT,ZZLZT,SGTXT,ORDTYP,GUID) ");
|
|
|
SqlStringBuilder.Append(string.Format("VALUES('{0}','{1}',{2},'{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}',NEWID())",
|
|
|
REFID, ACTION, ZEILE, WERKS, LGORT, UMLGO, MATNR, UMMAT, ERFMG, ERFME, ZSTAT, SOBKZ, HTEXT, ZZLZT, SGTXT, ORDTYP));
|
|
|
return SqlStringBuilder.ToString();
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
#region SAP 盘点表
|
|
|
|
|
|
public List<SapCyco> getSapCycoInfo(string iblnr, string zeili)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sqlString = "SELECT * FROM SAP_CYCO WHERE IBLNR = @IBLNR AND ZEILI = @ZEILI ";
|
|
|
DynamicParameters Params = new DynamicParameters();
|
|
|
Params.Add("@IBLNR", iblnr);
|
|
|
Params.Add("@ZEILI", zeili);
|
|
|
List<SapCyco> result = dbConn.Query<SapCyco>(sqlString, Params).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public string getInsertSapCyco(string IBLNR, string ZEILI, string MATNR, string MEINS, string WERKS, string LGORT, string ZSTAT, string SOBKZ, string ZXM,
|
|
|
string ZDJBS, string XLOEK, string GJAHR)
|
|
|
{
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("INSERT INTO SAP_CYCO (IBLNR,ZEILI,MATNR,");
|
|
|
SqlStringBuilder.Append(" MEINS,WERKS,LGORT,ZSTAT,SOBKZ,ZXM,ZDJBS,XLOEK,GJAHR,GUID) ");
|
|
|
SqlStringBuilder.Append(string.Format("VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}',NEWID())",
|
|
|
IBLNR, ZEILI, MATNR, MEINS, WERKS, LGORT, ZSTAT, SOBKZ, ZXM, ZDJBS, XLOEK, GJAHR));
|
|
|
return SqlStringBuilder.ToString();
|
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
#region SAP 盘点调差指令
|
|
|
|
|
|
public List<SapDifo> getSapDifoInfo(string iblnr, string zeili)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sqlString = "SELECT * FROM SAP_DIFO WHERE IBLNR = @IBLNR AND ZEILI = @ZEILI ";
|
|
|
DynamicParameters Params = new DynamicParameters();
|
|
|
Params.Add("@IBLNR", iblnr);
|
|
|
Params.Add("@ZEILI", zeili);
|
|
|
List<SapDifo> result = dbConn.Query<SapDifo>(sqlString, Params).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public string getInsertSapDifo(string WERKS, string IBLNR, string ZEILI, string ZTZBS, string GJAHR)
|
|
|
{
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("INSERT INTO SAP_DIFO (WERKS,IBLNR,ZEILI,");
|
|
|
SqlStringBuilder.Append(" ZTZBS,GJAHR,GUID) ");
|
|
|
SqlStringBuilder.Append(string.Format("VALUES('{0}','{1}','{2}','{3}','{4}',NEWID())", WERKS, IBLNR, ZEILI, ZTZBS, GJAHR));
|
|
|
return SqlStringBuilder.ToString();
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取盘点单信息
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public List<WmsCheckStock> getCheckStockInfo(string orderNo)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sqlString = " select * from wms_check_stock where enabled = 'Y' AND status = 'C' AND order_no = @order_no ";
|
|
|
DynamicParameters Params = new DynamicParameters();
|
|
|
Params.Add("@order_no", orderNo);
|
|
|
List<WmsCheckStock> result = dbConn.Query<WmsCheckStock>(sqlString, Params).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
#region 销售退货收货指令
|
|
|
|
|
|
public List<SapRdn> getSapRdnInfo(string vbeln, string postnr)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sqlString = "SELECT * FROM SAP_RDN WHERE VBELN = @VBELN AND POSNR = @POSNR ";
|
|
|
DynamicParameters Params = new DynamicParameters();
|
|
|
Params.Add("@VBELN", vbeln);
|
|
|
Params.Add("@POSNR", postnr);
|
|
|
List<SapRdn> result = dbConn.Query<SapRdn>(sqlString, Params).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public string getInsertSapRdn(string VBELN, string POSNR, string WERKS, string LGORT, string LFDAT, string KUNNR,
|
|
|
string MATNR, string LFIMG, string MEINS)
|
|
|
{
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("INSERT INTO SAP_RDN (VBELN,POSNR,WERKS,LGORT,LFDAT,");
|
|
|
SqlStringBuilder.Append("KUNNR,MATNR,LFIMG,MEINS,GUID) ");
|
|
|
SqlStringBuilder.Append(string.Format("VALUES('{0}','{1}',{2},'{3}','{4}','{5}','{6}','{7}','{8}',NEWID())",
|
|
|
VBELN, POSNR, WERKS, LGORT, LFDAT, KUNNR, MATNR, LFIMG, MEINS));
|
|
|
return SqlStringBuilder.ToString();
|
|
|
}
|
|
|
#endregion
|
|
|
}
|
|
|
}
|