Jjie
/
HGSRM
1
0
Fork 0
Code Issues Pull Requests Projects Releases Wiki Activity
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1 Commit
1 Branch
0 Tags
399 MiB
 
 
 
 
 
Branch: master
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
HGSRM/WMS-BS/NFine.Application/WMS/IQC/IQCQualityApp.cs

7249 lines
280 KiB
Raw Permalink Blame History

using Dapper;
using Dapper.Contrib.Extensions;
using ICSSoft.SendMail;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NFine.Application.DHAY;
using NFine.Application.Entity;
using NFine.Application.Models;
using NFine.Code;
using NFine.Code.FTP;
using NFine.Data.Extensions;
using NFine.Domain._03_Entity.SRM;
using NFine.Repository;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace NFine.Application.WMS
{
public class IQCQualityApp : RepositoryFactory<ICSVendor>
{
public DataTable GetInvcode(string queryJson, ref Pagination jqgridparam)
{
var queryParam = queryJson.ToJObject();
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
List<DbParameter> parameter = new List<DbParameter>();
DataTable table = new DataTable();
if (string.IsNullOrEmpty(queryParam["IsAllInv"].ToString()))
{
//检验物料
//var result = GetMaterialInStorageCheckPageList(queryJson,ref jqgridparam);
//return result;
string sql = @"
select
distinct
a.InvCode
,a.InvName
,a.InvStd
from
(
select
distinct
c.InvCode
,inv.InvName
,inv.InvStd
,sum(b.DNQuantity) DNQuantity --到货数量
,case when max(g.CheckNo) is null then '否'else '是' end as IsCheck
from ICSASN a with(nolock)
left join ICSASNDetail b with(nolock) on a.ASNCode=b.ASNCode and a.WorkPoint=b.WorkPoint
left join ICSInventoryLot c with(nolock) on b.LotNo=c.LotNo and b.WorkPoint=b.WorkPoint
left join ICSInventoryLotDetail cc with(nolock) on b.lotno=cc.lotno and cc.WorkPoint=c.WorkPoint
left join ICSExtension c1 with(nolock) on c.ExtensionID=c1.ID
left join ICSInventory inv with(nolock) on c.InvCode=inv.InvCode and c.WorkPoint=inv.WorkPoint
left join ICSVendor e with(nolock) on a.VenCode=e.VenCode and a.WorkPoint=e.WorkPoint
left join IcsMatCheckResult g with(nolock) on c.InvCode=g.InvCode and c1.BatchCode=g.InvBatcgNo and a.ASNCode=g.AsnCode
left join (SELECT ASNCode,InvCode,ExtensionID,max(CreateDateTime) CreateDateTime,max( t.DNCode) DNCode,PODetailID
from ICSDeliveryNotice t with(nolock) GROUP BY ASNCode,InvCode,ExtensionID,PODetailID ) l
on a.ASNCode=l.ASNCode and c.InvCode=l.InvCode and l.PODetailID=cc.TransCode+'~'+cc.TransSequence
where 1=1
{0}
and inv.InvIQC=1
and b.lotno not in
( select a.LotNo from ICSASNDetail a
inner join ICSWareHouseLotInfo b on a.lotno=b.lotno
left join ICSInspection c on c.LotNo=a.LotNo
where c.lotno is null
)
group by c.InvCode,inv.InvName ,inv.InvStd ,c1.BatchCode,a.ASNCode
union all
select
distinct
c.InvCode
,d.InvName
,d.InvStd
,sum(b.ODNQuantity) DNQuantity --到货数量
,case when max(g.CheckNo) is null then '否'else '是' end as IsCheck
from ICSOASN a with(nolock)
left join ICSOASNDetail b with(nolock) on a.OASNCode=b.OASNCode
left join ICSInventoryLot c with(nolock) on b.LotNo=c.LotNo
left join ICSInspection wl with(nolock) on b.LotNo=wl.LotNo and b.WorkPoint=wl.WorkPoint
left join ICSExtension c1 with(nolock) on c.ExtensionID=c1.ID
left join ICSInventory d with(nolock) on c.InvCode=d.InvCode
left join ICSVendor e with(nolock) on a.VenCode=e.VenCode
left join IcsMatCheckResult g with(nolock) on c.InvCode=g.InvCode and c1.BatchCode=g.InvBatcgNo and a.OASNCode=g.AsnCode
left join (SELECT OASNCode,InvCode,ExtensionID,max(CreateDateTime) CreateDateTime,max( t.ODNCode) DNCode
from ICSODeliveryNotice t with(nolock) GROUP BY OASNCode,InvCode,ExtensionID )l on a.OASNCode=l.OASNCode and c.InvCode=l.InvCode and l.ExtensionID=c.ExtensionID
where 1=1
and d.InvIQC=1
and b.lotno not in
(select a.LotNo from ICSOASNDetail a with(nolock)
inner join ICSWareHouseLotInfo b with(nolock) on a.lotno=b.lotno
left join ICSInspection c with(nolock) on c.LotNo=a.LotNo
where c.lotno is null
)
{0}
group by c.InvCode,d.InvName ,d.InvStd ,c1.BatchCode,a.OASNCode
) a
where 1=1 AND a.DNQuantity>0
and a.IsCheck='否'
<1>
<2>
<3>
";
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToStringExt()))
{
sql = sql.Replace("<1>", " and a.InvCode like '%" + queryParam["InvCode"].ToStringExt() + "%' ");
}
else
{
sql = sql.Replace("<1>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToStringExt()))
{
sql = sql.Replace("<2>", " and a.InvName like '%" + queryParam["InvName"].ToStringExt() + "%' ");
}
else
{
sql = sql.Replace("<2>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["InvStd"].ToStringExt()))
{
sql = sql.Replace("<3>", " and a.InvStd like '%" + queryParam["InvStd"].ToStringExt() + "%' ");
}
else
{
sql = sql.Replace("<3>", " ");
}
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
}
else
{
//全部物料
string wherestr = "";
if (!string.IsNullOrEmpty(queryParam["InvCode"].ToString()))
{
wherestr += " and InvCode like '%" + queryParam["InvCode"].ToString() + "%'";
}
if (!string.IsNullOrEmpty(queryParam["InvName"].ToString()))
{
wherestr += " and InvName like '%" + queryParam["InvName"].ToString() + "%'";
}
if (!string.IsNullOrEmpty(queryParam["InvStd"].ToString()))
{
wherestr += " and InvStd like '%" + queryParam["InvStd"].ToString() + "%'";
}
string sql = @"select distinct InvCode,InvName,InvStd,InvUnit,InvDesc from dbo.ICSInventory with(nolock)
WHERE WorkPoint = '" + WorkPoint + "'" + wherestr;
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
}
}
public DataTable GetGridJsonChengPing(string queryJson, ref Pagination pagination)
{
DataTable dt = new DataTable();
var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
#region [SQL]
string sql = @"
select
case when a.Enable=1 then '是'
else '否' end as EnableText
,a.*
from ICSInspectionRulesGroup a with(nolock)
where 1=1
--and a.WorkPoint='UFDATA_106_2019'
{0}
<1>
<2>
<3>
";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
//sql = string.Format(sql, Figure);
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
if (!string.IsNullOrWhiteSpace(queryJson))
{
if (!string.IsNullOrWhiteSpace(queryParam["RulesCode"].ToString()))
{
sql = sql.Replace("<1>", " and a.RulesCode like '%" + queryParam["RulesCode"].ToString() + "%' ");
}
else
{
sql = sql.Replace("<1>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["RulesName"].ToString()))
{
sql = sql.Replace("<2>", " and a.RulesName like '%" + queryParam["RulesName"].ToString() + "%' ");
}
else
{
sql = sql.Replace("<2>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["Enable"].ToString()))
{
sql = sql.Replace("<3>", " and a.Enable =" + queryParam["Enable"].ToString() + " ");
}
else
{
sql = sql.Replace("<3>", " ");
}
}
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
{
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
// sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
}
else
{
sql = sql.Replace("{0}", "");
}
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
//{
// sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
//}
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref pagination);
}
public DataTable GetCheckItemDetailList(string id, ref Pagination pagination)
{
DataTable dt = new DataTable();
// var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
#region [SQL]
string sql = @"select
a.*
from ICSInspectionList a with(nolock)
where 1=1
{0}
<1>
order by a.ListCode asc
";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
sql = sql.Replace("<1>", "and a.GroupID='" + id + "'");
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
{
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
// sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
}
else
{
sql = sql.Replace("{0}", "");
}
return Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
}
public DataTable GetCheckItemGroupPageList(string queryJson, ref Pagination pagination)
{
DataTable dt = new DataTable();
var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
#region [SQL]
string sql = @"
select
case when a.Enable=1 then '是'
else '否' end as EnableText
,a.*
from ICSInspectionGroup a with(nolock)
where 1=1
{0}
<1>
<2>
--<3>
";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
//sql = string.Format(sql, Figure);
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
if (!string.IsNullOrWhiteSpace(queryJson))
{
if (!string.IsNullOrWhiteSpace(queryParam["GroupCode"].ToString()))
{
sql = sql.Replace("<1>", " and a.GroupCode like '%" + queryParam["GroupCode"].ToString() + "%' ");
}
else
{
sql = sql.Replace("<1>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["GroupName"].ToString()))
{
sql = sql.Replace("<2>", " and a.GroupName like '%" + queryParam["GroupName"].ToString() + "%' ");
}
else
{
sql = sql.Replace("<2>", " ");
}
//if (!string.IsNullOrWhiteSpace(queryParam["Enable"].ToString()))
//{
// sql = sql.Replace("<3>", " and a.Enable =" + queryParam["Enable"].ToString() + " ");
//}
//else
//{
// sql = sql.Replace("<3>", " ");
//}
}
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
{
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
// sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
}
else
{
sql = sql.Replace("{0}", "");
}
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
//{
// sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
//}
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref pagination);
}
public DataTable GetBadCodeGroupPageList(string queryJson, ref Pagination pagination)
{
DataTable dt = new DataTable();
var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
#region [SQL]
string sql = @"
select
a.*
from ICSBadCodeGroup a with(nolock)
where 1=1
{0}
<1>
<2>
";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
//sql = string.Format(sql, Figure);
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
if (!string.IsNullOrWhiteSpace(queryJson))
{
if (!string.IsNullOrWhiteSpace(queryParam["BCGCode"].ToString()))
{
sql = sql.Replace("<1>", " and a.BCGCode like '%" + queryParam["BCGCode"].ToString() + "%' ");
}
else
{
sql = sql.Replace("<1>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["BCGDesc"].ToString()))
{
sql = sql.Replace("<2>", " and a.BCGDesc like '%" + queryParam["BCGDesc"].ToString() + "%' ");
}
else
{
sql = sql.Replace("<2>", " ");
}
}
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
{
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
// sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
}
else
{
sql = sql.Replace("{0}", "");
}
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
//{
// sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
//}
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref pagination);
}
public DataTable GetBadCodeGroupDetailList(string id, ref Pagination pagination)
{
DataTable dt = new DataTable();
// var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
#region [SQL]
string sql = @"select
a.*
from ICSBadCode a with(nolock)
where 1=1
{0}
<1>
order by a.BadCode asc
";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
sql = sql.Replace("<1>", "and a.BCGroupID='" + id + "'");
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
{
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
// sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
}
else
{
sql = sql.Replace("{0}", "");
}
return Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
}
public List<ICSInspectionGroupEntity> GetAllCheckGroupList(string code)
{
var returnValue = new List<ICSInspectionGroupEntity>();
string sql = "select * from ICSInspectionGroup with(nolock) where GroupCode like '%" + code + "%' {0} ";
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
{
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
// sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
}
else
{
sql = sql.Replace("{0}", "");
}
returnValue = MsSqlData.Query<ICSInspectionGroupEntity>(sql).ToList();
return returnValue;
}
public string DeleteAql(string keyValue)
{
string returnValue = string.Empty;
try
{
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var idList = JsonConvert.DeserializeObject<List<string>>(keyValue);
string sql = @"select
a.*
from ICSInspectionRulesGroup a with(nolock)
where a.ID in ('" + string.Join("','", idList) + "') and a.workpoint=@WorkPoint ";
var para = new { WorkPoint = WorkPoint };
var entityList = MsSqlData.Query<ICSInspectionRulesGroupEntity>(sql, para).ToList();
foreach (var item in entityList)
{
//var count = MsSqlData.ExecuteScalar(" select count(1) from IcsCheckingFixtureTransfer with(nolock) where CheckFixtureNo='" + item.CheckFixtureNo + "'").ToInt();
//if (count > 0)
//{
// throw new Exception("当前检具已经存在履历,不能删除");
//}
}
var result = MsSqlData.Delete<ICSInspectionRulesGroupEntity>(entityList);
if (!result)
{
returnValue = "删除失败";
}
string sql2 = @"delete from ICSInspectionRulesList where RulesID in ('" + string.Join("','", idList) + "') and a.workpoint=@WorkPoint ";
List<SqlParameter> paras = new List<SqlParameter>();
paras.Add(new SqlParameter("@WorkPoint", SqlDbType.NVarChar) { Value = WorkPoint });
var line = Repository().ExecuteBySql(new StringBuilder(sql2), paras.ToArray());
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
public DataTable GetItem2RuleList(string queryJson, ref Pagination pagination)
{
DataTable dt = new DataTable();
var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
#region [SQL]
string sql = @"
select
a.*
from
(
select
a.ID
,b.InvCode
,b.InvName
,b.InvStd
,c.RulesCode
,c.RulesName
,c.RulesDesc
,a.MUSER
,a.MUSERName
,a.MTIME
,a.EATTRIBUTE1 as CheckAttr --检验属性
,( select t.label from V_QuerySysEnumItem t where t.value=a.EATTRIBUTE1 and t.F_EnCode='CheckAttr'
) as CheckAttrText
from ICSInventoryInspectionRulesGroup a with(nolock)
join ICSInventory b with(nolock) on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
join ICSInspectionRulesGroup c with(nolock) on a.RulesCode=c.RulesCode and a.WorkPoint=c.WorkPoint
where 1=1
{0}
) a
where 1=1
<1>
<2>
<3>
<4>
";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
//sql = string.Format(sql, Figure);
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
if (!string.IsNullOrWhiteSpace(queryJson))
{
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
{
sql = sql.Replace("<1>", " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ");
}
else
{
sql = sql.Replace("<1>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToString()))
{
sql = sql.Replace("<2>", " and a.InvName like '%" + queryParam["InvName"].ToString() + "%' ");
}
else
{
sql = sql.Replace("<2>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["RulesCode"].ToString()))
{
sql = sql.Replace("<3>", " and a.RulesCode like '%" + queryParam["RulesCode"].ToString() + "%' ");
}
else
{
sql = sql.Replace("<3>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["RulesName"].ToString()))
{
sql = sql.Replace("<4>", " and a.RulesName like '%" + queryParam["RulesName"].ToString() + "%' ");
}
else
{
sql = sql.Replace("<4>", " ");
}
}
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
{
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
// sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
}
else
{
sql = sql.Replace("{0}", "");
}
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
//{
// sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
//}
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref pagination);
}
public string SaveItem2AqlSingle(string keyValue)
{
string returnValue = string.Empty;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
try
{
var dtNow = DateTime.Now;
var para = new { WorkPoint = WorkPoint };
var model = JsonConvert.DeserializeObject<ICSInventoryInspectionRulesGroup>(keyValue);
if (model == null)
{
throw new Exception("传入参数错误");
}
//修改
var entity = MsSqlData.Get<ICSInventoryInspectionRulesGroup>(model.ID);
if (entity == null)
{
throw new Exception("当前数据不存在");
}
entity.RulesCode = model.RulesCode;
var sql1 = @"select top 1 * from ICSInspectionRulesGroup a with(nolock) where a.RulesCode = '" + entity.RulesCode + "' and a.WorkPoint=@WorkPoint ";
var aql = MsSqlData.Query<ICSInspectionRulesGroupEntity>(sql1, para).FirstOrDefault();
if (aql == null)
{
throw new Exception("当前Aql不存在");
}
entity.RulesName = aql.RulesName;
entity.RulesDesc = aql.RulesDesc;
entity.Type = aql.Type;
entity.Enable = model.Enable;
entity.MUSER = MUSER;
entity.MUSERName = MUSERNAME;
entity.MTIME = dtNow;
var result = MsSqlData.Update<ICSInventoryInspectionRulesGroup>(entity);
if (!result)
{
returnValue = "修改失败";
}
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
public int UpLoadFinalJudgeFile(string iD, string fileName)
{
var returnValue = 1;
var dtNow = DateTime.Now;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
SqlConnection conn = SqlHelper.GetDataCenterConn();
try
{
var entity = MsSqlData.Get<IcsMatCheckResult>(iD);
if (entity == null)
{
throw new Exception("当前数据不存在");
}
entity.SpecialFile = fileName;
entity.MUSER = MUSER;
entity.MUSERName = MUSERNAME;
entity.MTIME = dtNow;
var result = MsSqlData.Update<IcsMatCheckResult>(entity);
if (!result)
{
returnValue = 0;
}
}
catch (Exception ex)
{
returnValue = 0;
}
return returnValue;
}
public ICSInventoryInspectionRulesGroup GetItem2Aql(string keyValue)
{
var returnValue = new ICSInventoryInspectionRulesGroup();
try
{
returnValue = MsSqlData.Get<ICSInventoryInspectionRulesGroup>(keyValue);
}
catch (Exception ex)
{
returnValue = null;
}
return returnValue;
}
public string SetData_PR(string savePath)
{
string returnValue = string.Empty;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
SqlConnection conn = SqlHelper.GetDataCenterConn();
DataTable data = FileToExcel.ExcelToTable(savePath);
DateTime time = DateTime.Now;
//DataSet dsSave = new DataSet();
//dsSave.Tables.Add(data.Copy());
//dsSave.Tables[0].TableName = "IcsCheckingFixture";
var para = new { WorkPoint = WorkPoint };
string sql0 = @"
select *
from V_QuerySysEnumItem a
where a.F_EnCode='CheckAttr'
";
var enumList = MsSqlData.Query<SysEnumValueDto>(sql0, para).ToList();
//var chicun = enumList.Where(x => x.Ext1 == "尺寸").Select(x => x.value);
List<ICSInventoryInspectionRulesGroup> list = new List<ICSInventoryInspectionRulesGroup>();
for (int i = 0; i < data.Rows.Count; i++)
{
var item = data.Rows[i];
if (item["物料代码"].ToStringExt() == "")
{
throw new Exception("第" + (i + 1) + "行," + "物料代码为空");
}
if (item["Aql代码"].ToStringExt() == "")
{
throw new Exception("第" + (i + 1) + "行," + "Aql代码为空");
}
if (item["检验属性"].ToStringExt() == "")
{
throw new Exception("第" + (i + 1) + "行," + "检验属性为空");
}
//if (item["类型"].ToStringExt() == "")
//{
// throw new Exception("第" + (i + 1) + "行," + "类型为空");
//}
ICSInventoryInspectionRulesGroup entity = new ICSInventoryInspectionRulesGroup();
entity.ID = Guid.NewGuid().ToString();
entity.InvCode = item["物料代码"].ToStringExt();
entity.RulesCode = item["Aql代码"].ToStringExt();
entity.EATTRIBUTE1 = enumList.Where(x => x.label == item["检验属性"].ToStringExt()).FirstOrDefault()?.value;
var count = MsSqlData.ExecuteScalar
(
" select count(1) from ICSInventoryInspectionRulesGroup a with(nolock) where InvCode='" + entity.InvCode + "' and a.EATTRIBUTE1='" + entity.EATTRIBUTE1 + "' "
).ToInt();
if (count > 0)
{
throw new Exception("当前物料Aql关系已经存在");
}
var itemCode = MsSqlData.Query<ICSInventoryDto>(
@"select top 1 * from ICSInventory a with(nolock)
where a.InvCode = '" + entity.InvCode + "'")
.FirstOrDefault();
if (itemCode == null)
{
throw new Exception("当前物料不存在");
}
var aql = MsSqlData.Query<ICSInspectionRulesGroupEntity>(
@"select top 1 * from ICSInspectionRulesGroup a with(nolock)
where a.RulesCode = '" + entity.RulesCode + "'")
.FirstOrDefault();
if (aql == null)
{
throw new Exception("当前Aql不存在");
}
entity.RulesName = aql.RulesName;
entity.RulesDesc = aql.RulesDesc;
entity.Type = aql.Type;
entity.Enable = true;
entity.Editable = true;
entity.MUSER = MUSER;
entity.MUSERName = MUSERNAME;
entity.MTIME = time;
entity.WorkPoint = WorkPoint;
list.Add(entity);
}
if (list.Count == 0)
{
throw new Exception("没有导入数据");
}
var result = MsSqlData.Insert<ICSInventoryInspectionRulesGroup>(list);
if (!result)
{
throw new Exception("导入失败");
}
returnValue = "true";
return returnValue;
}
public DataTable GetMaterialCheckLog(string keyValue)
{
List<DbParameter> parameter = new List<DbParameter>();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
string sql = @"SELECT DISTINCT
a.WgNgFile,
c2.FileName OutReport,
c2.FilePath,
a.InvCode,
d.InvName,
d.InvStd,
REPLACE(d.ClassName, '编码规则', '') WLFL,
a.AsnCode,
a.InvBatcgNo,
a.InvBatcgQty,
a.VenCode,
c.VenName,
CASE WHEN e.EATTRIBUTE1='是' THEN '是' ELSE '否' END AS SFJL,
a.FirstCheckTime CHUJIANSHIJIAN,
a.Result,
a.IQCGroupHeaderResult ZZJG,
a.IQCGroupHeaderRemark ZZBZ,
a.IQCGroupHeaderUser,
a.IQCGroupHeaderTime,
BLXX.BRGDesc,
BLXX1.BadReasonDesc,
BLFL.BadDesc,
a.SQEEngineerResult SQEJG,
a.SQEEngineerRemark SQEBZ,
a.SQEEngineerUser,
a.SQEEngineerTime,
a.IQCHeaderResult ZGJG,
a.IQCHeaderRemark ZGBZ,
a.IQCHeaderUser,
a.IQCHeaderTime,
a.EATTRIBUTE1 SQEFA,
A.FinalResult ZZPD,
a.FinalUser,
a.FinalTime,
CASE WHEN a.Result='OK' OR (a.Result='NG' AND a.IsCheckComplete='是') THEN '是'
ELSE '否' END AS SFJA,
b.CheckAttr,
b.CheckAttrText,
b.CheckItemCode,
b.CheckItemName,
b.CheckWay,
b.CheckAqlName,
CONVERT(decimal(18, 2), b.SetValueMax) AS SetValueMax,
CONVERT(decimal(18, 2), b.SetValueMin) AS SetValueMin,
b.Unit,
CONVERT(decimal(18, 2), b.S1) AS S1,
CONVERT(decimal(18, 2), b.S2) AS S2,
CONVERT(decimal(18, 2), b.S3) AS S3,
CONVERT(decimal(18, 2), b.S4) AS S4,
CONVERT(decimal(18, 2), b.S5) AS S5,
CONVERT(decimal(18, 2), b.S6) AS S6,
CONVERT(decimal(18, 2), b.S7) AS S7,
CONVERT(decimal(18, 2), b.S8) AS S8,
CONVERT(decimal(18, 2), b.S9) AS S9,
CONVERT(decimal(18, 2), b.S10) AS S10,
B.AqlQty,B.GoodQty,B.NgQty,B.Result JYXJG,B.Remark JYXBZ,B.MUSER,B.MUSERName,B.MTIME
FROM IcsMatCheckResult a
left JOIN IcsMatCheckResultDetail b on a.CheckNo=b.CheckNo AND a.WorkPoint=b.WorkPoint
LEFT JOIN ICSVendor c on a.VenCode=c.VenCode AND a.WorkPoint=c.WorkPoint
LEFT JOIN ICSInventory d on a.InvCode=d.InvCode AND a.WorkPoint=d.WorkPoint
LEFT JOIN (
SELECT DISTINCT a.ASNCode,b.InvCode,a.EATTRIBUTE1,c.BatchCode,a.WorkPoint,bb.TransCode FROM ICSASNDetail a
LEFT JOIN ICSInventoryLot b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint
LEFT JOIN ICSInventoryLotDetail bb on a.LotNo=bb.LotNo and a.WorkPoint=bb.WorkPoint
LEFT JOIN ICSExtension c on b.ExtensionID=c.ID and c.WorkPoint=b.WorkPoint
)e ON a.AsnCode=e.ASNCode and a.InvCode=e.InvCode and a.InvBatcgNo=e.BatchCode and a.WorkPoint=e.WorkPoint
LEFT JOIN (
SELECT DISTINCT a.OASNCode,b.InvCode,a.EATTRIBUTE1,c.BatchCode,a.WorkPoint,bb.TransCode FROM ICSOASNDetail a
LEFT JOIN ICSInventoryLot b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint
LEFT JOIN ICSInventoryLotDetail bb on a.LotNo=bb.LotNo and a.WorkPoint=bb.WorkPoint
LEFT JOIN ICSExtension c on b.ExtensionID=c.ID and c.WorkPoint=b.WorkPoint
)f ON a.AsnCode=f.OASNCode and a.InvCode=f.InvCode and a.InvBatcgNo=e.BatchCode and a.WorkPoint=e.WorkPoint
LEFT JOIN ICSBadReasonGroup BLXX ON BLXX.BRGCode=A.BCGCode
LEFT JOIN ICSBadReason BLXX1 ON BLXX1.BadReasonCode=A.BadReasonCode
LEFT JOIN ICSBadCode BLFL ON BLFL.BadCode=A.BRGCode
left join (
select *
from
(
select t.*
,row_number() over(partition by t.ASNCode,t.BatchCode,t.invcode order by t.CerateTime desc) RowSeq
from ICSASNShippingReport t with(nolock)
) t where t.RowSeq=1
) c2 on a.invcode=c2.invcode AND a.WgNgFile=c2.FileName
WHERE 1=1
and a.InvCode='{0}' AND a.CheckNo IN
(select TOP 10 CheckNo from IcsMatCheckResult
where InvCode='{0}' order by MTIME desc)
order by b.MTIME desc
";
sql = string.Format(sql, keyValue, WorkPoint);
return Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
}
public DataTable GetBadReasonGroupPageList(string queryJson, ref Pagination pagination)
{
DataTable dt = new DataTable();
var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
#region [SQL]
string sql = @"
select
a.*
from ICSBadReasonGroup a with(nolock)
where 1=1
{0}
<1>
<2>
";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
//sql = string.Format(sql, Figure);
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
if (!string.IsNullOrWhiteSpace(queryJson))
{
if (!string.IsNullOrWhiteSpace(queryParam["BRGCode"].ToString()))
{
sql = sql.Replace("<1>", " and a.BRGCode like '%" + queryParam["BRGCode"].ToString() + "%' ");
}
else
{
sql = sql.Replace("<1>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["BRGDesc"].ToString()))
{
sql = sql.Replace("<2>", " and a.BRGDesc like '%" + queryParam["BRGDesc"].ToString() + "%' ");
}
else
{
sql = sql.Replace("<2>", " ");
}
}
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
{
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
// sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
}
else
{
sql = sql.Replace("{0}", "");
}
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
//{
// sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
//}
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref pagination);
}
public DataTable ExportAllCheckItemGroup(string groupCode, string GroupName)
{
DataTable dt = new DataTable();
// var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
#region [SQL]
string sql = @"
select
case when a.Enable=1 then '是'
else '否' end as EnableText
,a.*
from ICSInspectionGroup a with(nolock)
where 1=1
{0}
<1>
<2>
--<3>
";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
//sql = string.Format(sql, Figure);
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
if (!string.IsNullOrWhiteSpace(groupCode))
{
sql = sql.Replace("<1>", " and a.GroupCode like '%" + groupCode + "%' ");
}
else
{
sql = sql.Replace("<1>", " ");
}
if (!string.IsNullOrWhiteSpace(GroupName))
{
sql = sql.Replace("<2>", " and a.GroupName like '%" + GroupName + "%' ");
}
else
{
sql = sql.Replace("<2>", " ");
}
//if (!string.IsNullOrWhiteSpace(queryParam["Enable"].ToString()))
//{
// sql = sql.Replace("<3>", " and a.Enable =" + queryParam["Enable"].ToString() + " ");
//}
//else
//{
// sql = sql.Replace("<3>", " ");
//}
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
{
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
// sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
}
else
{
sql = sql.Replace("{0}", "");
}
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
//{
// sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
//}
return Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
}
public string ImportCheckItem(string savePath)
{
string returnValue = string.Empty;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
SqlConnection conn = SqlHelper.GetDataCenterConn();
DataTable data = FileToExcel.ExcelToTable(savePath);
DateTime time = DateTime.Now;
//DataSet dsSave = new DataSet();
//dsSave.Tables.Add(data.Copy());
DateTime dtNow = DateTime.Now;
//dsSave.Tables[0].TableName = "IcsCheckingFixture";
ICSInspectionGroupEntity entity = new ICSInspectionGroupEntity();
List<ICSInspectionListEntity> list = new List<ICSInspectionListEntity>();
for (int i = 0; i < data.Rows.Count; i++)
{
var item = data.Rows[i];
if (i == 0)
{
if (item["检验类型编码"].ToStringExt() == "")
{
throw new Exception("第1行," + "检验类型编码为空");
}
if (item["检验类型名称"].ToStringExt() == "")
{
throw new Exception("第1行," + "检验类型名称为空");
}
entity.ID = Guid.NewGuid().ToString();
entity.MUSER = MUSER;
entity.MUSERName = MUSERNAME;
entity.MTIME = dtNow;
entity.WorkPoint = WorkPoint;
entity.GroupCode = item["检验类型编码"].ToStringExt();
entity.GroupName = item["检验类型名称"].ToStringExt();
entity.Enable = true;
}
if (item["检验项目代码"].ToStringExt() == "")
{
throw new Exception("第" + (i + 1) + "行," + "检验项目代码为空");
}
if (item["检验项目名称"].ToStringExt() == "")
{
throw new Exception("第" + (i + 1) + "行," + "检验项目名称为空");
}
if (item["最大值"].ToDecimal() <= 0)
{
throw new Exception("第" + (i + 1) + "行," + "最大值必须大于0");
}
if (item["最小值"].ToDecimal() <= 0)
{
throw new Exception("第" + (i + 1) + "行," + "最小值必须大于0");
}
//if (item["单位"].ToStringExt() == "")
//{
// throw new Exception("第" + (i + 1) + "行," + "检验项目名称为空");
//}
ICSInspectionListEntity detail = new ICSInspectionListEntity();
detail.ID = Guid.NewGuid().ToString();
detail.GroupID = entity.ID;
detail.ListCode = item["检验项目代码"].ToStringExt();
detail.ListName = item["检验项目名称"].ToStringExt();
detail.Unit = item["单位"].ToStringExt();
detail.SetValueMax = item["最大值"].ToDecimal();
detail.SetValueMin = item["最小值"].ToDecimal();
detail.Enable = true;
detail.MUSER = MUSER;
detail.MUSERName = MUSERNAME;
detail.MTIME = dtNow;
detail.WorkPoint = WorkPoint;
if (detail.SetValueMax <= 0 || detail.SetValueMin <= 0)
{
throw new Exception("第" + (i + 1) + "行," + "值必须大于0");
}
if (detail.SetValueMax <= detail.SetValueMin)
{
throw new Exception("第" + (i + 1) + "行," + "最大值必须大于最小值");
}
list.Add(detail);
}
if (list.Count == 0)
{
throw new Exception("没有导入数据");
}
if (list != null && list.Count > 0)
{
if (list.Select(x => x.ListCode).Distinct().Count() != list.Count)
{
throw new Exception("检验项目代码重复");
}
}
var result = MsSqlData.Insert<ICSInspectionGroupEntity>(entity);
if (!result)
{
throw new Exception("导入失败");
}
result = MsSqlData.Insert<ICSInspectionListEntity>(list);
if (!result)
{
throw new Exception("导入失败");
}
returnValue = "true";
return returnValue;
}
public ICSInventoryInspectionListDto GetItem2CheckItem(string keyValue)
{
var returnValue = new ICSInventoryInspectionListDto();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var sql = @"select
a.*
from
(
select
a.ID
,c.InvCode
,c.InvName
,a.ListCode
,a.ListName
,b.EATTRIBUTE1 as CheckAttr --检验属性
,( select t.label from V_QuerySysEnumItem t where t.value=b.EATTRIBUTE1 and t.F_EnCode='CheckAttr'
) as CheckAttrText
,a.SetValueMax
,a.SetValueMin
,a.Unit
,a.Enable
,a.MUSER
,a.MUSERName
,a.MTIME
from ICSInventoryInspectionList a with(nolock)
left join ICSInventoryInspectionGroup b with(nolock) on a.InvGroupID=b.ID
left join ICSInventory c with(nolock) on b.InvCode=c.InvCode
where 1=1
and a.WorkPoint=@WorkPoint
) a
where 1=1
and a.ID=@Id";
var para = new
{
ID = keyValue,
WorkPoint = WorkPoint,
};
returnValue = MsSqlData.Query<ICSInventoryInspectionListDto>(sql, para).FirstOrDefault();
return returnValue;
}
public string SaveItem2CheckItemSingle(string keyValue)
{
string returnValue = string.Empty;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
try
{
var dtNow = DateTime.Now;
var model = JsonConvert.DeserializeObject<ICSInventoryInspectionListDto>(keyValue);
if (model == null)
{
throw new Exception("传入参数错误");
}
//修改
var entity = MsSqlData.Get<ICSInventoryInspectionListEntity>(model.ID);
if (entity == null)
{
throw new Exception("当前数据不存在");
}
entity.ListName = model.ListName;
entity.Unit = model.Unit;
entity.SetValueMax = model.SetValueMax;
entity.SetValueMin = model.SetValueMin;
entity.Enable = model.Enable;
entity.MUSER = MUSER;
entity.MUSERName = MUSERNAME;
entity.MTIME = dtNow;
var result = MsSqlData.Update<ICSInventoryInspectionListEntity>(entity);
if (!result)
{
returnValue = "修改失败";
}
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
public DataTable ExportAllMaterialInStorageCheck(string invCode, string invName, string isCheck, string StartDate, string EndDate,
string checkStartDate,string CheckEndDate)
{
DataTable dt = new DataTable();
//var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
#region [SQL]
string sql = @"
select a.InvCode as 物料编码
,a.InvName as 物料名称
,a.DNCode as 到货单号
,a.InvBatcgNo as 物料批号
,a.DNQuantity as 到货数量
,a.DHDate as 到货时间
,a.VenCode as 供应商编码
,a.VenName as 供应商名称
,a.ASNCode as 送货单号
,a.IsUrgentItem as 加急料
,a.IsCheck as 是否检验
,a.Result as 初判结果
,a.CheckDate as 初检时间
,a.WHCode as 仓库代码
from
(
select
distinct
max(b.ID) DetailId --子表主键
,Max(c.ID) LotNoId --条码ID
,c.InvCode
,inv.InvName
,c1.BatchCode as InvBatcgNo --物料批号
,sum(b.DNQuantity) DNQuantity --到货数量
,max(a.VenCode) VenCode --供应商编码
,max(e.VenName ) VenName --供应商名称
,a.ASNCode
,max(b.EATTRIBUTE1) as IsUrgentItem --加急料
,max(inv.InvUnit) InvUnit
,sum(c.Quantity) Quantity
,max(c2.FileName) OutReport
,max(c2.FilePath) FilePath
,max(g.id) CheckID
,case when max(g.CheckNo) is null then '否'else '是' end as IsCheck
,max(g.Result) Result
,max(g.FirstCheckTime) CheckDate
,max(g.WHCode) WHCode
,max(l.CreateDateTime) DHDate
,max(l.DNCode) DNCode
,'1' as DocType
from ICSASN a with(nolock)
left join ICSASNDetail b with(nolock) on a.ASNCode=b.ASNCode and a.WorkPoint=b.WorkPoint
left join ICSInventoryLot c with(nolock) on b.LotNo=c.LotNo and b.WorkPoint=b.WorkPoint
left join ICSInventoryLotDetail cc with(nolock) on b.lotno=cc.lotno and cc.WorkPoint=c.WorkPoint
left join ICSExtension c1 with(nolock) on c.ExtensionID=c1.ID
left join (
select *
from
(
select t.*
,row_number() over(partition by t.ASNCode,t.BatchCode,t.invcode order by t.CerateTime desc) RowSeq
from ICSASNShippingReport t with(nolock)
) t where t.RowSeq=1
) c2 on a.ASNCode=c2.ASNCode and c1.BatchCode=c2.BatchCode and c.invcode=c2.invcode
left join ICSInventory inv with(nolock) on c.InvCode=inv.InvCode and c.WorkPoint=inv.WorkPoint
left join ICSVendor e with(nolock) on a.VenCode=e.VenCode and a.WorkPoint=e.WorkPoint
left join IcsMatCheckResult g with(nolock) on c.InvCode=g.InvCode and c1.BatchCode=g.InvBatcgNo and a.ASNCode=g.AsnCode
left join (SELECT ASNCode,InvCode,ExtensionID,max(CreateDateTime) CreateDateTime,max( t.DNCode) DNCode,PODetailID
from ICSDeliveryNotice t with(nolock) GROUP BY ASNCode,InvCode,ExtensionID,PODetailID ) l
on a.ASNCode=l.ASNCode and c.InvCode=l.InvCode and l.PODetailID=cc.TransCode+'~'+cc.TransSequence
where 1=1
{0}
and inv.InvIQC=1
and b.lotno not in
( select a.LotNo from ICSASNDetail a
inner join ICSWareHouseLotInfo b on a.lotno=b.lotno
left join ICSInspection c on c.LotNo=a.LotNo
where c.lotno is null
)
group by c.InvCode,inv.InvName ,c1.BatchCode,a.ASNCode
union all
select
distinct
max(b.ID) DetailId --子表主键
,Max(c.ID) LotNoId --条码ID
,c.InvCode
,d.InvName
,c1.BatchCode as InvBatcgNo --物料批号
,sum(b.ODNQuantity) DNQuantity --到货数量
,max(a.VenCode) VenCode --供应商编码
,max(e.VenName ) VenName --供应商名称
,a.OASNCode as ASNCode
,max(b.EATTRIBUTE1) as IsUrgentItem --加急料
,max(d.InvUnit) InvUnit
,sum(c.Quantity) Quantity
,'' as OutReport
,'' as FilePath
,max(g.id) CheckID
,case when max(g.CheckNo) is null then '否'else '是' end as IsCheck
,max(g.Result) Result
,max(g.FirstCheckTime) CheckDate
,max(g.WHCode) WHCode
,max(l.CreateDateTime) DHDate
,max(l.DNCode) DNCode
,'2' as DocType
from ICSOASN a with(nolock)
left join ICSOASNDetail b with(nolock) on a.OASNCode=b.OASNCode
left join ICSInventoryLot c with(nolock) on b.LotNo=c.LotNo
left join ICSInspection wl with(nolock) on b.LotNo=wl.LotNo and b.WorkPoint=wl.WorkPoint
left join ICSExtension c1 with(nolock) on c.ExtensionID=c1.ID
left join ICSInventory d with(nolock) on c.InvCode=d.InvCode
left join ICSVendor e with(nolock) on a.VenCode=e.VenCode
left join IcsMatCheckResult g with(nolock) on c.InvCode=g.InvCode and c1.BatchCode=g.InvBatcgNo and a.OASNCode=g.AsnCode
left join (SELECT OASNCode,InvCode,ExtensionID,max(CreateDateTime) CreateDateTime,max( t.ODNCode) DNCode
from ICSODeliveryNotice t with(nolock) GROUP BY OASNCode,InvCode,ExtensionID )l on a.OASNCode=l.OASNCode and c.InvCode=l.InvCode and l.ExtensionID=c.ExtensionID
where 1=1
and d.InvIQC=1
and b.lotno not in
(select a.LotNo from ICSOASNDetail a
inner join ICSWareHouseLotInfo b on a.lotno=b.lotno
left join ICSInspection c on c.LotNo=a.LotNo
where c.lotno is null
)
{0}
group by c.InvCode,d.InvName ,c1.BatchCode,a.OASNCode
) a
where 1=1 AND a.DNQuantity>0
<1>
<2>
<3>
<4>
--<5>
--<6>
--<7>
--<8>
<9>
<10>
<11>
";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
//sql = string.Format(sql, Figure);
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
if (!string.IsNullOrWhiteSpace(invCode))
{
sql = sql.Replace("<1>", " and a.InvCode like '%" + invCode + "%' ");
}
else
{
sql = sql.Replace("<1>", " ");
}
if (!string.IsNullOrWhiteSpace(invName))
{
sql = sql.Replace("<2>", " and a.InvName like '%" + invName + "%' ");
}
else
{
sql = sql.Replace("<2>", " ");
}
if (!string.IsNullOrWhiteSpace(StartDate))
{
sql = sql.Replace("<3>", " and a.DHDate>='" + StartDate + " 00:00:00' ");
}
else
{
sql = sql.Replace("<3>", " ");
}
if (!string.IsNullOrWhiteSpace(EndDate))
{
sql = sql.Replace("<4>", " and a.DHDate<='" + EndDate + " 23:59:59' ");
}
else
{
sql = sql.Replace("<4>", " ");
}
//if (!string.IsNullOrWhiteSpace(queryParam["ASNCode"].ToString()))
//{
// sql = sql.Replace("<5>", " and a.ASNCode like '%" + queryParam["ASNCode"].ToString() + "%' ");
//}
//else
//{
// sql = sql.Replace("<5>", " ");
//}
//if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString()))
//{
// sql = sql.Replace("<6>", " and a.VenCode like '%" + queryParam["VenCode"].ToString() + "%' ");
//}
//else
//{
// sql = sql.Replace("<6>", " ");
//}
//if (!string.IsNullOrWhiteSpace(queryParam["VenName"].ToString()))
//{
// sql = sql.Replace("<7>", " and a.VenName like '%" + queryParam["VenName"].ToString() + "%' ");
//}
//else
//{
// sql = sql.Replace("<7>", " ");
//}
//if (!string.IsNullOrWhiteSpace(queryParam["InvBatcgNo"].ToString()))
//{
// sql = sql.Replace("<8>", " and a.InvBatcgNo like '%" + queryParam["InvBatcgNo"].ToString() + "%' ");
//}
//else
//{
// sql = sql.Replace("<8>", " ");
//}
if (!string.IsNullOrWhiteSpace(isCheck))
{
sql = sql.Replace("<9>", " and a.IsCheck='" + isCheck + "' ");
}
else
{
sql = sql.Replace("<9>", " ");
}
if (!string.IsNullOrWhiteSpace(checkStartDate))
{
sql = sql.Replace("<10>", " and a.CheckDate>='" + checkStartDate + " 00:00:00' ");
}
else
{
sql = sql.Replace("<10>", " ");
}
if (!string.IsNullOrWhiteSpace(CheckEndDate))
{
sql = sql.Replace("<11>", " and a.CheckDate<='" + CheckEndDate + " 23:59:59' ");
}
else
{
sql = sql.Replace("<11>", " ");
}
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
//{
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
// // sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
//}
//else
//{
// sql = sql.Replace("{0}", "");
//}
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
//{
// sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
//}
return Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
}
public string DeleteMatCheck(string keyValue)
{
string returnValue = string.Empty;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
try
{
var model = JsonConvert.DeserializeObject<IcsMatCheckResultDto>(keyValue);
if (model == null)
{
throw new Exception("数据获取失败");
}
var para = new
{
WorkPoint = WorkPoint,
InvCode = model.InvCode,
AsnCode = model.AsnCode,
InvBatcgNo = model.InvBatcgNo,
};
string sql = @" select * from [IcsMatCheckResult] a with(nolock)
where InvCode=@InvCode
and AsnCode=@AsnCode
and InvBatcgNo=@InvBatcgNo and a.WorkPoint=@WorkPoint ";
var entityList = MsSqlData.Query<IcsMatCheckResult>(sql, para).ToList();
if (entityList == null|| entityList.Count==0)
{
throw new Exception("当前选择数据不能查到对应检验记录");
}
foreach (var item in entityList)
{
//var count = MsSqlData.ExecuteScalar(" select count(1) from IcsCheckingFixtureTransfer with(nolock) where CheckFixtureNo='" + item.CheckFixtureNo + "'").ToInt();
//if (count > 0)
//{
// throw new Exception("当前检具已经存在履历,不能删除");
//}
Pagination pagination = null;
var invLotNos = GetMaterialInStorageCheckDetailList(item.AsnCode, item.InvBatcgNo, item.InvCode, ref pagination);
//删除检验主表记录
if (invLotNos != null && invLotNos.Rows.Count > 0)
{
var lotNos = invLotNos.Select("1=1").Select(x => x["LotNo"].ToString());
DeleteInspection(lotNos, para);
}
var result = MsSqlData.Delete<IcsMatCheckResult>(entityList);
if (!result)
{
throw new Exception("删除失败");
}
var sql2 = @" delete from IcsMatCheckResultDetail where CheckNo='"+item.CheckNo+"' and WorkPoint=@WorkPoint ";
var count= MsSqlData.Execute(sql2, para);
//if (count == 0)
//{
// throw new Exception("删除失败");
//}
}
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
public string IQC1Back(string keyValue)
{
string returnValue = string.Empty;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
try
{
var dtNow = DateTime.Now;
var model = JsonConvert.DeserializeObject<IcsMatCheckResultDto>(keyValue);
if (model == null)
{
throw new Exception("传入参数错误");
}
var entity = MsSqlData.Get<IcsMatCheckResult>(model.ID);
if (entity == null)
{
throw new Exception("当前数据不存在");
}
var para = new
{
WorkPoint = WorkPoint,
};
Pagination pagination = null;
var invLotNos = GetMaterialInStorageCheckDetailList(entity.AsnCode, entity.InvBatcgNo, entity.InvCode, ref pagination);
var result = true;
//return "";
switch (model.BackType)
{
case "IQC1Back":
if (!entity.SQEEngineerResult.IsNullOrEmpty())
{
throw new Exception("当前数据已经被SQE判定");
}
if (entity.IQCGroupHeaderResult.IsNullOrEmpty())
{
throw new Exception("当前数据还没有被判定");
}
//删除检验主表记录
if (invLotNos != null && invLotNos.Rows.Count > 0)
{
var lotNos = invLotNos.Select("1=1").Select(x => x["LotNo"].ToString());
DeleteInspection(lotNos, para);
}
//删除当前检验角色检验记录
entity.IQCGroupHeaderResult = null;
entity.IQCGroupHeaderRemark = null;
entity.IQCGroupHeaderUser = null;
entity.IQCGroupHeaderTime = null;
break;
case "SQEBack":
if (!entity.IQCHeaderResult.IsNullOrEmpty())
{
throw new Exception("当前数据已经被IQC主管判定");
}
if (!entity.FinalResult.IsNullOrEmpty())
{
throw new Exception("当前数据已经被追最终判定");
}
if (entity.SQEEngineerResult.IsNullOrEmpty())
{
throw new Exception("当前数据还没有被判定");
}
//删除检验主表记录
if (invLotNos != null && invLotNos.Rows.Count > 0)
{
var lotNos = invLotNos.Select("1=1").Select(x => x["LotNo"].ToString());
DeleteInspection(lotNos, para);
}
//删除当前检验角色检验记录
entity.SQEEngineerResult = null;
entity.SQEEngineerRemark = null;
entity.SQEEngineerUser = null;
entity.SQEEngineerTime = null;
entity.EATTRIBUTE1 = null;
break;
case "IQC2Back":
if (!entity.FinalResult.IsNullOrEmpty())
{
throw new Exception("当前数据已经被追最终判定");
}
if (entity.IQCHeaderResult.IsNullOrEmpty())
{
throw new Exception("当前数据还没有被判定");
}
//删除检验主表记录
if (invLotNos != null && invLotNos.Rows.Count > 0)
{
var lotNos = invLotNos.Select("1=1").Select(x => x["LotNo"].ToString());
DeleteInspection(lotNos, para);
}
//删除当前检验角色检验记录
entity.IQCHeaderResult = null;
entity.IQCHeaderRemark = null;
entity.IQCHeaderUser = null;
entity.IQCHeaderTime = null;
break;
case "FinalJudgeBack":
if (entity.FinalResult.IsNullOrEmpty())
{
throw new Exception("当前数据还没有被最终判定");
}
//删除检验主表记录
if (invLotNos != null && invLotNos.Rows.Count > 0)
{
var lotNos = invLotNos.Select("1=1").Select(x => x["LotNo"].ToString());
DeleteInspection(lotNos, para);
}
//删除当前检验角色检验记录
entity.FinalUser = null;
entity.FinalResult = null;
entity.FinalTime = null;
entity.GoodQty = 0;
entity.NgQty = 0;
break;
default:
break;
}
entity.MUSER = MUSER;
entity.MUSERName = MUSERNAME;
entity.MTIME = dtNow;
entity.IsCheckComplete = null;
result = MsSqlData.Update<IcsMatCheckResult>(entity);
if (!result)
{
returnValue = "修改失败";
}
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
private void DeleteInspection(IEnumerable<string> lotNos, object para)
{
var isInstorageSql = @"select count(1) from ICSWareHouseLotInfo a with(nolock) where a.LotNo in ('" + string.Join("','", lotNos) + "') and WorkPoint=@WorkPoint";
var count = MsSqlData.ExecuteScalar(isInstorageSql, para).ToInt();
if (count > 0)
{
throw new Exception("当前检验单号对应条码已经入库");
}
var sql = @"delete from ICSInspection where LotNo in ('" + string.Join("','", lotNos) + "') and WorkPoint=@WorkPoint";
var sql2 = @"delete FROM ICSDeliveryNotice WHERE EATTRIBUTE1 in ('" + string.Join("','", lotNos) + "') and WorkPoint=@WorkPoint";
var aa = MsSqlData.Execute(sql, para);
aa = MsSqlData.Execute(sql2, para);
}
public DataTable ExportMaterialInStorageFinalList(string invCode, string invName, string isCheckComplete, string startDate, string endDate, string aSNCode
, string venCode, string venName, string invBatcgNo, string juegeProgess, string bcgCode)
{
DataTable dt = new DataTable();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
#region [SQL]
string sql = @"
select
a.ASNCode 送货单号
,InvCode 物料编码
,InvName 物料名称
,InvBatcgNo 批次
,InvBatcgQty 批次数量
,VenCode 供应商
,VenName 供应商名称
,a.IQCGroupHeaderResult IQC组长判定
,a.IQCHeaderResult IQC主管判定
,a.SQEEngineerResult SQE工程师判定
,a.FinalResult 最终判定
,a.EATTRIBUTE1 SQE处理方案
,a.DHDate 到货时间
,a.IQCGroupHeaderUser IQC组长判定人员
,a.IQCGroupHeaderTime IQC组长判定时间
,a.IQCHeaderUser IQC主管判定人员
,a.IQCHeaderTime IQC主管判定时间
,a.SQEEngineerUser SQE工程师判定人员
,a.SQEEngineerTime SQE工程师判定时间
,a.FinalUser 最终判定人员
,a.FinalTime 最终判定时间
,a.BcgDesc 不良分类
,a.HasReturnItem 仓库已退实物
,a.Urgent 加急
,a.FirstCheckUser 初检人员
,a.FirstCheckTime 初检时间
from
(
select
distinct
a.ID
,a.CheckNo
,a.AsnCode ASNCode
,a.InvCode
,b.InvName
,a.InvBatcgNo
,a.InvBatcgQty
,a.VenCode
,c.VenName
,c2.FileName OutReport --出货报告
,c2.FilePath
,a.IQCGroupHeaderResult
,a.IQCHeaderResult
,a.SQEEngineerResult
,a.FinalResult
,a.IsCheckComplete
,a.EATTRIBUTE1
,a.EATTRIBUTE3 CheckDate
,case when l.CreateDateTime is not null then l.CreateDateTime
when l2.CreateDateTime is not null then l2.CreateDateTime
else null end as DHDate
,a.IQCGroupHeaderUser
,a.IQCGroupHeaderTime
,a.IQCHeaderUser
,a.IQCHeaderTime
,a.SQEEngineerUser
,a.SQEEngineerTime
,a.FinalUser
,a.FinalTime
,a.BRGCode
,bcg.BcgDesc
,a.HasReturnItem
,a.Urgent
,a.FirstCheckUser
,a.FirstCheckTime
,a.WgNgFile
from IcsMatCheckResult a with(nolock)
left join ICSBadCodeGroup bcg with(nolock) on a.BRGCode=bcg.bcgcode and a.WorkPoint=bcg.WorkPoint
left join ICSInventory b with(nolock) on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
left join ICSVendor c with(nolock) on a.VenCode=c.VenCode and a.WorkPoint=c.WorkPoint
left join (
select *
from
(
select t.*
,row_number() over(partition by t.ASNCode,t.BatchCode,t.invcode order by t.CerateTime desc) RowSeq
from ICSASNShippingReport t with(nolock)
) t where t.RowSeq=1
) c2 on a.ASNCode=c2.ASNCode and a.InvBatcgNo=c2.BatchCode and a.invcode=c2.invcode
left join ICSDeliveryNotice l with(nolock) on a.ASNCode=l.ASNCode
left join ICSODeliveryNotice l2 with(nolock) on a.ASNCode=l2.OASNCode
where 1=1
{0}
and a.Result='NG'
) a
where 1=1
--and a.AsnCode=''
--order by a.CheckNo
<1>
<2>
<3>
<4>
<5>
<6>
<7>
<8>
<9>
<10>
<11>
";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
//sql = string.Format(sql, Figure);
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
if (!string.IsNullOrWhiteSpace(invCode))
{
sql = sql.Replace("<1>", " and a.InvCode like '%" + invCode + "%' ");
}
else
{
sql = sql.Replace("<1>", " ");
}
if (!string.IsNullOrWhiteSpace(invName))
{
sql = sql.Replace("<2>", " and a.InvName like '%" + invName + "%' ");
}
else
{
sql = sql.Replace("<2>", " ");
}
if (!string.IsNullOrWhiteSpace(startDate))
{
sql = sql.Replace("<3>", " and a.DHDate>='" + startDate + " 00:00:00' ");
}
else
{
sql = sql.Replace("<3>", " ");
}
if (!string.IsNullOrWhiteSpace(endDate))
{
sql = sql.Replace("<4>", " and a.DHDate<='" + endDate + " 23:59:59' ");
}
else
{
sql = sql.Replace("<4>", " ");
}
if (!string.IsNullOrWhiteSpace(aSNCode))
{
sql = sql.Replace("<5>", " and a.ASNCode like '%" + aSNCode + "%' ");
}
else
{
sql = sql.Replace("<5>", " ");
}
if (!string.IsNullOrWhiteSpace(venCode))
{
sql = sql.Replace("<6>", " and a.VenCode like '%" + venCode + "%' ");
}
else
{
sql = sql.Replace("<6>", " ");
}
if (!string.IsNullOrWhiteSpace(venName))
{
sql = sql.Replace("<7>", " and a.VenName like '%" + venName + "%' ");
}
else
{
sql = sql.Replace("<7>", " ");
}
if (!string.IsNullOrWhiteSpace(invBatcgNo))
{
sql = sql.Replace("<8>", " and a.InvBatcgNo like '%" + invBatcgNo + "%' ");
}
else
{
sql = sql.Replace("<8>", " ");
}
if (!string.IsNullOrWhiteSpace(isCheckComplete))
{
if (isCheckComplete == "是")
sql = sql.Replace("<9>", " and a.IsCheckComplete='" + isCheckComplete + "' ");
else
sql = sql.Replace("<9>", " and a.IsCheckComplete is null ");
}
else
{
sql = sql.Replace("<9>", " ");
}
if (!string.IsNullOrWhiteSpace(juegeProgess))
{
var aa = juegeProgess;
if (aa == "1")
{
sql = sql.Replace("<10>", " and a.IQCGroupHeaderResult is null ");
}
if (aa == "2")
{
sql = sql.Replace("<10>", " and a.IQCGroupHeaderResult is not null ");
}
if (aa == "3")
{
sql = sql.Replace("<10>", " and a.SQEEngineerResult is null ");
}
if (aa == "4")
{
sql = sql.Replace("<10>", " and a.SQEEngineerResult is not null ");
}
if (aa == "5")
{
sql = sql.Replace("<10>", " and a.IQCHeaderResult is null ");
}
if (aa == "6")
{
sql = sql.Replace("<10>", " and a.IQCHeaderResult is not null ");
}
if (aa == "7")
{
sql = sql.Replace("<10>", " and a.FinalResult is null ");
}
}
else
{
sql = sql.Replace("<10>", " ");
}
if (!string.IsNullOrWhiteSpace(bcgCode))
{
sql = sql.Replace("<11>", " and a.BcgDesc like '%" + bcgCode + "%' ");
}
else
{
sql = sql.Replace("<11>", " ");
}
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
//{
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
// // sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
//}
//else
//{
// sql = sql.Replace("{0}", "");
//}
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
//{
// sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
//}
return Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
}
public DataTable ExportAllMaterialCheckLog(string invCode)
{
DataTable dt = new DataTable();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
string sql = @"SELECT DISTINCT
a.InvCode '料品编码',
d.InvName '料品名称',
d.InvStd '规格型号',
REPLACE(d.ClassName, '编码规则', '') '物料分类',
a.AsnCode '送货单号',
a.InvBatcgNo '批号',
a.InvBatcgQty '检验数量',
a.VenCode '供应商编码',
c.VenName '供应商名称',
CASE WHEN e.EATTRIBUTE1='是' THEN '是' ELSE '否' END AS '是否急料',
a.FirstCheckTime '初检时间',
a.Result '初检结果',
a.IQCGroupHeaderResult 'IQC组长复判结果',
a.IQCGroupHeaderRemark 'IQC组长备注',
a.SQEEngineerResult 'SQE复判结果',
a.SQEEngineerRemark 'SQE复判备注',
a.IQCHeaderResult 'IQC主管复判结果',
a.IQCHeaderRemark 'IQC主管复判备注',
a.EATTRIBUTE1 'SQE判定处理方案',
A.FinalResult '最终判定结果',
CASE WHEN a.Result='OK' OR (a.Result='NG' AND a.IsCheckComplete='是') THEN '是'
ELSE '否' END AS '是否结案',
b.CheckAttr '检验分类编码',
b.CheckAttrText '检验分类描述',
b.CheckItemCode '检验项编码',
b.CheckItemName '检验项描述',
b.CheckWay '检验方式',
b.CheckAqlName '检验属性',
CONVERT(decimal(18, 2), b.SetValueMax) AS '检验上限',
CONVERT(decimal(18, 2), b.SetValueMin) AS '检验下限',
b.Unit '检验单位',
CONVERT(decimal(18, 2), b.S1) AS S1,
CONVERT(decimal(18, 2), b.S2) AS S2,
CONVERT(decimal(18, 2), b.S3) AS S3,
CONVERT(decimal(18, 2), b.S4) AS S4,
CONVERT(decimal(18, 2), b.S5) AS S5,
CONVERT(decimal(18, 2), b.S6) AS S6,
CONVERT(decimal(18, 2), b.S7) AS S7,
CONVERT(decimal(18, 2), b.S8) AS S8,
CONVERT(decimal(18, 2), b.S9) AS S9,
CONVERT(decimal(18, 2), b.S10) AS S10,
B.AqlQty '外观抽检数量',
B.GoodQty '抽检合格数量',
B.NgQty '抽检不合格数量',
B.Result '检验项结果',
B.Remark '检验项备注',
B.MUSER '检验人编码',
B.MUSERName '检验人名称',
B.MTIME '检验时间',
'' as '订单号'
FROM IcsMatCheckResult a
left JOIN IcsMatCheckResultDetail b on a.CheckNo=b.CheckNo AND a.WorkPoint=b.WorkPoint
LEFT JOIN ICSVendor c on a.VenCode=c.VenCode AND a.WorkPoint=c.WorkPoint
LEFT JOIN ICSInventory d on a.InvCode=d.InvCode AND a.WorkPoint=d.WorkPoint
LEFT JOIN (
SELECT DISTINCT a.ASNCode,b.InvCode,a.EATTRIBUTE1,c.BatchCode,a.WorkPoint,bb.TransCode FROM ICSASNDetail a
LEFT JOIN ICSInventoryLot b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint
LEFT JOIN ICSInventoryLotDetail bb on a.LotNo=bb.LotNo and a.WorkPoint=bb.WorkPoint
LEFT JOIN ICSExtension c on b.ExtensionID=c.ID and c.WorkPoint=b.WorkPoint
)e ON a.AsnCode=e.ASNCode and a.InvCode=e.InvCode and a.InvBatcgNo=e.BatchCode and a.WorkPoint=e.WorkPoint
LEFT JOIN (
SELECT DISTINCT a.OASNCode,b.InvCode,a.EATTRIBUTE1,c.BatchCode,a.WorkPoint,bb.TransCode FROM ICSOASNDetail a
LEFT JOIN ICSInventoryLot b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint
LEFT JOIN ICSInventoryLotDetail bb on a.LotNo=bb.LotNo and a.WorkPoint=bb.WorkPoint
LEFT JOIN ICSExtension c on b.ExtensionID=c.ID and c.WorkPoint=b.WorkPoint
)f ON a.AsnCode=f.OASNCode and a.InvCode=f.InvCode and a.InvBatcgNo=e.BatchCode and a.WorkPoint=e.WorkPoint
LEFT JOIN ICSBadReasonGroup BLXX ON BLXX.BRGCode=A.BCGCode
LEFT JOIN ICSBadReason BLXX1 ON BLXX1.BadReasonCode=A.BadReasonCode
LEFT JOIN ICSBadCode BLFL ON BLFL.BadCode=A.BRGCode
left join (
select *
from
(
select t.*
,row_number() over(partition by t.ASNCode,t.BatchCode,t.invcode order by t.CerateTime desc) RowSeq
from ICSASNShippingReport t with(nolock)
) t where t.RowSeq=1
) c2 on a.invcode=c2.invcode AND a.WgNgFile=c2.FileName
WHERE 1=1
and a.InvCode='{0}' AND a.CheckNo IN
(select TOP 10 CheckNo from IcsMatCheckResult
where InvCode='{0}' order by MTIME desc)
order by b.MTIME desc
";
sql = string.Format(sql, invCode, WorkPoint);
return Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
}
public string UpdateMaterialReUrgent(string keyValue,string time)
{
string returnValue = string.Empty;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
try
{
var dtNow = DateTime.Now;
var models = keyValue.Substring(1, keyValue.Length-2).Split(',').ToList();
if (models == null)
{
throw new Exception("传入参数错误");
}
var sql = @"select * from IcsMatCheckResult a with(nolock) where a.id in ('" + string.Join("','", models) + "') and a.WorkPoint=@WorkPoint";
var para = new
{
WorkPoint = WorkPoint,
};
//查询已存在条码记录
var getList = MsSqlData.Query<IcsMatCheckResult>(sql, para).ToList();
foreach (var item in getList)
{
item.Urgent = "是";
item.MUSER = MUSER;
item.MUSERName = MUSERNAME;
item.MTIME = dtNow;
item.EATTRIBUTE6 = time;
var result = MsSqlData.Update<IcsMatCheckResult>(item);
if (!result)
{
returnValue = "修改失败";
}
}
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
public string UpdateMaterialHasReturnItem(string keyValue)
{
string returnValue = string.Empty;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
try
{
var dtNow = DateTime.Now;
var model = JsonConvert.DeserializeObject<IcsMatCheckResultDto>(keyValue);
if (model == null)
{
throw new Exception("传入参数错误");
}
var sql = @"select * from IcsMatCheckResult a with(nolock) where a.id in ('" + model.ID + "') and a.WorkPoint=@WorkPoint";
var para = new
{
WorkPoint = WorkPoint,
};
//查询已存在条码记录
var getList = MsSqlData.Query<IcsMatCheckResult>(sql, para).ToList();
//return "";
foreach (var item in getList)
{
if (item.NgQty > 0)
{
item.HasReturnItem = "是";
item.HasReturnTime = dtNow;
item.WHRemark = model.WHRemark;
}
else
{
continue;
}
item.MUSER = MUSER;
item.MUSERName = MUSERNAME;
item.MTIME = dtNow;
var result = MsSqlData.Update<IcsMatCheckResult>(item);
if (!result)
{
returnValue = "修改失败";
}
}
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
public string ImportItem2CheckGroup(string savePath)
{
string returnValue = string.Empty;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
SqlConnection conn = SqlHelper.GetDataCenterConn();
DataTable data = FileToExcel.ExcelToTable(savePath);
DateTime time = DateTime.Now;
//DataSet dsSave = new DataSet();
//dsSave.Tables.Add(data.Copy());
DateTime dtNow = DateTime.Now;
//dsSave.Tables[0].TableName = "IcsCheckingFixture";
//ICSInspectionGroupEntity entity = new ICSInspectionGroupEntity();
List<ICSInventoryInspectionListDto> dtolist = new List<ICSInventoryInspectionListDto>();
List<ICSInventoryInspectionGroupEntity> list = new List<ICSInventoryInspectionGroupEntity>();
List<ICSInventoryInspectionListEntity> detailList = new List<ICSInventoryInspectionListEntity>();
var para = new { WorkPoint = WorkPoint };
string sql0 = @"
select *
from V_QuerySysEnumItem a
where a.F_EnCode='CheckAttr'
";
var enumList = MsSqlData.Query<SysEnumValueDto>(sql0, para).ToList();
for (int i = 0; i < data.Rows.Count; i++)
{
var item = data.Rows[i];
if (item["物料代码"].ToStringExt() == "")
{
throw new Exception("第" + (i + 1) + "行," + "物料代码为空");
}
if (item["物料名称"].ToStringExt() == "")
{
throw new Exception("第" + (i + 1) + "行," + "物料名称为空");
}
if (item["检验项目编码"].ToStringExt() == "")
{
throw new Exception("第" + (i + 1) + "行," + "检验项目编码为空");
}
if (item["检验项目名称"].ToStringExt() == "")
{
throw new Exception("第" + (i + 1) + "行," + "检验项目名称为空");
}
if (item["检验属性"].ToStringExt() == "")
{
throw new Exception("第" + (i + 1) + "行," + "检验属性为空");
}
if (item["启用(是否)"].ToStringExt() == "")
{
throw new Exception("第" + (i + 1) + "行," + "启用(是否)为空");
}
var dto = new ICSInventoryInspectionListDto();
dto.InvCode = item["物料代码"].ToStringExt();
dto.InvName = item["物料名称"].ToStringExt();
dto.ListCode = item["检验项目编码"].ToStringExt();
dto.ListName = item["检验项目名称"].ToStringExt();
dto.EATTRIBUTE1 = item["检验方法"].ToStringExt();
dto.CheckAttr = item["检验属性"].ToStringExt();
var enumValue = enumList.Where(x => x.label == dto.CheckAttr).FirstOrDefault();
if (enumValue == null)
{
throw new Exception("第" + (i + 1) + "行," + "检验属性不能识别");
}
dto.CheckAttr = enumValue.value;
dto.SetValueMax = item["设计最大值"].ToDecimal();
dto.SetValueMin = item["设计最小值"].ToDecimal();
dto.Unit = item["单位"].ToStringExt();
dto.Enable = item["启用(是否)"].ToStringExt() == "是" ? true : false;
dtolist.Add(dto);
if (enumValue.Ext1 == "尺寸")
{
//尺寸
if (!dto.SetValueMax.HasValue || !dto.SetValueMin.HasValue || dto.Unit.IsEmpty())
{
throw new Exception("第" + (i + 1) + "行," + " 当属性为尺寸时,最大值,最小值,单位必填");
}
if (dto.SetValueMax.Value <= dto.SetValueMin.Value)
{
throw new Exception("第" + (i + 1) + "行," + " 最大值必须大于最小值");
}
}
string dtPre = DateTime.Now.ToString("yyyyMMdd");
string sql = @"EXEC Addins_GetSerialCode '" + WorkPoint + "','ICSInventoryInspectionGroup','GroupCode','" + "IGC" + dtPre + "',3";
var serialNo = MsSqlData.ExecuteScalar(sql).ToStringExt();
ICSInventoryInspectionGroupEntity entity = new ICSInventoryInspectionGroupEntity();
entity.ID = Guid.NewGuid().ToString();
entity.InvCode = dto.InvCode;
entity.GroupCode = serialNo;
entity.GroupName = serialNo;
entity.EATTRIBUTE1 = dto.CheckAttr;
entity.Enable = true;
entity.MUSER = MUSER;
entity.MUSERName = MUSERNAME;
entity.MTIME = dtNow;
entity.WorkPoint = WorkPoint;
var detail = new ICSInventoryInspectionListEntity();
detail = ConvertExt.Mapping<ICSInventoryInspectionListDto, ICSInventoryInspectionListEntity>(dto);
detail.InvGroupID = entity.ID;
detail.ID = Guid.NewGuid().ToString();
detail.MUSER = MUSER;
detail.MUSERName = MUSERNAME;
detail.MTIME = dtNow;
detail.WorkPoint = WorkPoint;
var sql4Count = @" select
count(1)
from
(
select
a.ID
, c.InvCode
, c.InvName
, a.ListCode
, a.ListName
, b.EATTRIBUTE1 as CheckAttr --检验属性
from ICSInventoryInspectionList a with(nolock)
left join ICSInventoryInspectionGroup b with(nolock) on a.InvGroupID = b.ID
left join ICSInventory c with(nolock) on b.InvCode = c.InvCode
) a
where 1 = 1
and a.InvCode = @InvCode and a.ListCode=@ListCode ";
var para2 = new { InvCode = dto.InvCode, ListCode = dto.ListCode, CheckAttr = dto.CheckAttr };
var count = MsSqlData.ExecuteScalar(sql4Count, para2).ToInt();
if (count > 0)
{
throw new Exception("第" + (i + 1) + "行," + "当前物料检验项目关系已经存在");
}
var itemCode = MsSqlData.Query<ICSInventoryDto>(
@"select top 1 * from ICSInventory a with(nolock)
where a.InvCode = '" + entity.InvCode + "'")
.FirstOrDefault();
if (itemCode == null)
{
throw new Exception("第" + (i + 1) + "行," + "当前物料不存在");
}
list.Add(entity);
detailList.Add(detail);
}
if (list.Count == 0 || detailList.Count == 0)
{
throw new Exception("没有导入数据");
}
//if (list != null && list.Count > 0)
//{
// if (list.Select(x => x.InvCode).Distinct().Count() != list.Count)
// {
// throw new Exception("一个物料只能绑定一个检验类型");
// }
//}
var groupList = dtolist.GroupBy(x => new { x.ListCode, x.InvCode });
if (groupList.Count() != dtolist.Count)
{
var codeError = groupList.Where(x => x.Count() > 1).FirstOrDefault();
// var conGroupList= groupList.Count()
throw new Exception("当前物料检验项目代码重复,对应物料和检验项目代码: " + codeError.Key.ListCode + "|" + codeError.Key.InvCode);
}
var result = MsSqlData.Insert<ICSInventoryInspectionGroupEntity>(list);
if (!result)
{
throw new Exception("导入失败");
}
result = MsSqlData.Insert<ICSInventoryInspectionListEntity>(detailList);
if (!result)
{
throw new Exception("导入失败");
}
returnValue = "true";
return returnValue;
}
public List<SelectItemDto> GetSelectBadReasonList(string keyValue)
{
var returnValue = new List<SelectItemDto>();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var sql = @"select
a.*
from ICSBadReason a with(nolock)
where 1=1
and a.WorkPoint=@WorkPoint
and a.BRGroupID=(
select
a.ID
from ICSBadReasonGroup a with(nolock)
where 1=1
and a.BRGCode=@BRGroupID
)
order by a.BadReasonCode asc ";
var para = new
{
WorkPoint = WorkPoint,
BRGroupID = keyValue
};
var query = MsSqlData.Query<ICSBadReasonEntity>(sql, para);
int index = 0;
returnValue = query.Select(x =>
{
++index;
SelectItemDto item = new SelectItemDto();
item.key = index;
item.value = x.BadReasonCode;
item.label = x.BadReasonDesc;
return item;
}).ToList();
return returnValue;
}
public List<SelectItemDto> GetSelectBadReasonGroupList()
{
var returnValue = new List<SelectItemDto>();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var sql = @"select
a.*
from ICSBadReasonGroup a with(nolock)
where 1=1
and a.WorkPoint=@WorkPoint
order by a.Mtime asc ";
var para = new
{
WorkPoint = WorkPoint,
};
var query = MsSqlData.Query<ICSBadReasonGroupEntity>(sql, para);
int index = 0;
returnValue = query.Select(x =>
{
++index;
SelectItemDto item = new SelectItemDto();
item.key = index;
item.value = x.BRGCode;
item.label = x.BRGDesc;
return item;
}).ToList();
return returnValue;
}
public List<SelectItemDto> GetSelectBadCodeGroupList()
{
var returnValue = new List<SelectItemDto>();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var sql = @"select
a.*
from ICSBadCodeGroup a with(nolock)
where 1=1
and a.WorkPoint=@WorkPoint
order by a.Mtime asc ";
var para = new
{
WorkPoint = WorkPoint,
};
var query = MsSqlData.Query<ICSBadCodeGroupEntity>(sql, para);
int index = 0;
returnValue = query.Select(x =>
{
++index;
SelectItemDto item = new SelectItemDto();
item.key = index;
item.value = x.BCGCode;
item.label = x.BCGDesc;
return item;
}).ToList();
return returnValue;
}
public IcsMatCheckResultDto GetMaterialReJudgeMain(string keyValue)
{
var returnValue = new IcsMatCheckResultDto();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
try
{
var para1 = new
{
WorkPoint = WorkPoint,
Id = keyValue
};
var sqlMain = @"select top 1
a.*
,b.InvName
,e.VenName
,case when l.CreateDateTime is not null then l.CreateDateTime
when l2.CreateDateTime is not null then l2.CreateDateTime
else null end as DHDate
from IcsMatCheckResult a with(nolock)
left join ICSInventory b with(nolock) on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
left join ICSVendor e with(nolock) on a.VenCode=e.VenCode and a.WorkPoint=e.WorkPoint
left join ICSDeliveryNotice l with(nolock) on a.ASNCode=l.ASNCode
left join ICSODeliveryNotice l2 with(nolock) on a.ASNCode=l2.OASNCode
where 1 = 1
and a.WorkPoint= @WorkPoint
--and a.CheckNo='CR202410300001'
and a.ID=@Id";
returnValue = MsSqlData.Query<IcsMatCheckResultDto>(sqlMain, para1).FirstOrDefault();
if (returnValue == null)
{
throw new Exception("当前检验单数据查询失败");
}
var para = new
{
WorkPoint = WorkPoint,
CheckNo = returnValue.CheckNo
};
var sql1 = @"select
a.*
from IcsMatCheckResultDetail a with(nolock)
where 1 = 1
and a.WorkPoint = @WorkPoint
and a.CheckNo=@CheckNo
order by a.CheckAttr ,a.CheckItemCode";
var detailList = MsSqlData.Query<IcsMatCheckResultDetailDto>(sql1, para);
// var para = new { WorkPoint = WorkPoint };
string sql0 = @"
select *
from V_QuerySysEnumItem a
where a.F_EnCode='CheckAttr'
";
var enumList = MsSqlData.Query<SysEnumValueDto>(sql0, para).ToList();
var chicun = enumList.Where(x => x.Ext1 == "尺寸").Select(x => x.value);
returnValue.CCList = detailList.Where(x => chicun.Contains(x.CheckAttr)).ToList();
chicun = enumList.Where(x => x.Ext1 == "外观").Select(x => x.value);
returnValue.WGList = detailList.Where(x => chicun.Contains(x.CheckAttr)).ToList();
chicun = enumList.Where(x => x.Ext1 == "性能").Select(x => x.value);
returnValue.XNList = detailList.Where(x => chicun.Contains(x.CheckAttr)).ToList();
}
catch (Exception ex)
{
returnValue = new IcsMatCheckResultDto();
returnValue.CCList = new List<IcsMatCheckResultDetailDto>();
returnValue.WGList = new List<IcsMatCheckResultDetailDto>();
returnValue.XNList = new List<IcsMatCheckResultDetailDto>();
}
return returnValue;
}
public DataTable GetMaterialInStorageFinalPageList(string queryJson, ref Pagination pagination)
{
DataTable dt = new DataTable();
var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
#region [SQL]
string sql = @"
select
a.*
from
(
select
distinct
a.ID
,a.CheckNo
,a.AsnCode ASNCode
,a.InvCode
,b.InvName
,a.InvBatcgNo
,a.InvBatcgQty
,a.VenCode
,c.VenName
,c2.FileName OutReport --出货报告
,c2.FilePath
,a.IQCGroupHeaderResult
,a.IQCHeaderResult
,a.SQEEngineerResult
,a.FinalResult
,a.IsCheckComplete
,a.EATTRIBUTE1
,a.EATTRIBUTE3 CheckDate
,a.EATTRIBUTE6
,case when l.CreateDateTime is not null then l.CreateDateTime
when l2.CreateDateTime is not null then l2.CreateDateTime
else null end as DHDate
,a.IQCGroupHeaderUser
,a.IQCGroupHeaderTime
,a.IQCHeaderUser
,a.IQCHeaderTime
,a.SQEEngineerUser
,a.SQEEngineerTime
,a.FinalUser
,a.FinalTime
,a.BRGCode
,bcg.BcgDesc
,a.HasReturnItem
,a.Urgent
,a.FirstCheckUser
,a.FirstCheckTime
,a.WgNgFile
,a.SQEFile
,a.GoodQty
,a.NgQty
,a.HasReturnTime
,a.WHRemark
from IcsMatCheckResult a with(nolock)
left join ICSBadCodeGroup bcg with(nolock) on a.BRGCode=bcg.bcgcode and a.WorkPoint=bcg.WorkPoint
left join ICSInventory b with(nolock) on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
left join ICSVendor c with(nolock) on a.VenCode=c.VenCode and a.WorkPoint=c.WorkPoint
left join (
select *
from
(
select t.*
,row_number() over(partition by t.ASNCode,t.BatchCode,t.invcode order by t.CerateTime desc) RowSeq
from ICSASNShippingReport t with(nolock)
) t where t.RowSeq=1
) c2 on a.ASNCode=c2.ASNCode and a.InvBatcgNo=c2.BatchCode and a.invcode=c2.invcode
left join ICSDeliveryNotice l with(nolock) on a.ASNCode=l.ASNCode
left join ICSODeliveryNotice l2 with(nolock) on a.ASNCode=l2.OASNCode
where 1=1
{0}
and a.Result='NG'
) a
where 1=1
--and a.AsnCode=''
--order by a.CheckNo
<1>
<2>
<3>
<4>
<5>
<6>
<7>
<8>
<9>
<10>
<11>
";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
//sql = string.Format(sql, Figure);
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
if (!string.IsNullOrWhiteSpace(queryJson))
{
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
{
sql = sql.Replace("<1>", " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ");
}
else
{
sql = sql.Replace("<1>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToString()))
{
sql = sql.Replace("<2>", " and a.InvName like '%" + queryParam["InvName"].ToString() + "%' ");
}
else
{
sql = sql.Replace("<2>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["StartDate"].ToString()))
{
sql = sql.Replace("<3>", " and a.DHDate>='" + queryParam["StartDate"].ToString() + " 00:00:00' ");
}
else
{
sql = sql.Replace("<3>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
{
sql = sql.Replace("<4>", " and a.DHDate<='" + queryParam["EndDate"].ToString() + " 23:59:59' ");
}
else
{
sql = sql.Replace("<4>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["ASNCode"].ToString()))
{
sql = sql.Replace("<5>", " and a.ASNCode like '%" + queryParam["ASNCode"].ToString() + "%' ");
}
else
{
sql = sql.Replace("<5>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString()))
{
sql = sql.Replace("<6>", " and a.VenCode like '%" + queryParam["VenCode"].ToString() + "%' ");
}
else
{
sql = sql.Replace("<6>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["VenName"].ToString()))
{
sql = sql.Replace("<7>", " and a.VenName like '%" + queryParam["VenName"].ToString() + "%' ");
}
else
{
sql = sql.Replace("<7>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["InvBatcgNo"].ToString()))
{
sql = sql.Replace("<8>", " and a.InvBatcgNo like '%" + queryParam["InvBatcgNo"].ToString() + "%' ");
}
else
{
sql = sql.Replace("<8>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["IsCheckComplete"].ToString()))
{
if (queryParam["IsCheckComplete"].ToString() == "是")
sql = sql.Replace("<9>", " and a.IsCheckComplete='" + queryParam["IsCheckComplete"].ToString() + "' ");
else
sql = sql.Replace("<9>", " and a.IsCheckComplete is null ");
}
else
{
sql = sql.Replace("<9>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["JuegeProgess"].ToString()))
{
var aa = queryParam["JuegeProgess"].ToString();
if (aa == "1")
{
sql = sql.Replace("<10>", " and a.IQCGroupHeaderResult is null ");
}
if (aa == "2")
{
sql = sql.Replace("<10>", " and a.IQCGroupHeaderResult is not null ");
}
if (aa == "3")
{
sql = sql.Replace("<10>", " and a.SQEEngineerResult is null ");
}
if (aa == "4")
{
sql = sql.Replace("<10>", " and a.SQEEngineerResult is not null ");
}
if (aa == "5")
{
sql = sql.Replace("<10>", " and a.IQCHeaderResult is null ");
}
if (aa == "6")
{
sql = sql.Replace("<10>", " and a.IQCHeaderResult is not null ");
}
if (aa == "7")
{
sql = sql.Replace("<10>", " and a.FinalResult is null ");
}
}
else
{
sql = sql.Replace("<10>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["BcgCode"].ToString()))
{
sql = sql.Replace("<11>", " and a.BcgDesc like '%" + queryParam["BcgCode"].ToString() + "%' ");
}
else
{
sql = sql.Replace("<11>", " ");
}
}
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
//{
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
// // sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
//}
//else
//{
// sql = sql.Replace("{0}", "");
//}
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
//{
// sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
//}
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref pagination);
}
public string SaveMaterialCheckResult(string keyValue)
{
string returnValue = string.Empty;
var otherApp = new DHICSRCVIQCsApp();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
try
{
var dtNow = DateTime.Now;
var model = JsonConvert.DeserializeObject<IcsMatCheckResultDto>(keyValue);
if (model == null)
{
throw new Exception("传入参数错误");
}
var getCountSql = @"select Count(1) from IcsMatCheckResult a with(nolock) where a.AsnCode=@AsnCode
and a.InvCode=@InvCode
and a.InvBatcgNo=@InvBatcgNo
and a.WorkPoint=@WorkPoint
";
var para = new
{
WorkPoint = WorkPoint,
AsnCode = model.AsnCode,
InvCode = model.InvCode,
InvBatcgNo = model.InvBatcgNo,
};
var getCount = MsSqlData.ExecuteScalar(getCountSql, para).ToInt();
if (getCount > 0)
{
throw new Exception("当前批次号已经存在检验记录");
}
string dtPre = DateTime.Now.ToString("yyyyMMdd");
string sql = @"EXEC Addins_GetSerialCode '" + WorkPoint + "','IcsMatCheckResult','CheckNo','" + "CR" + dtPre + "',4";
var serialNo = MsSqlData.ExecuteScalar(sql).ToStringExt();
var entity = new IcsMatCheckResult();
entity = ConvertExt.Mapping<IcsMatCheckResultDto, IcsMatCheckResult>(model);
entity.CheckNo = serialNo;
entity.ID = Guid.NewGuid().ToString();
entity.MUSER = MUSER;
entity.MUSERName = MUSERNAME;
entity.MTIME = dtNow;
entity.FirstCheckUser = MUSERNAME;
entity.FirstCheckTime = dtNow;
entity.WorkPoint = WorkPoint;
entity.WgNgFile = model.WgNgFile;
// entity.EATTRIBUTE3 = dtNow.ToString("yyyy-MM-dd HH:mm:ss");
MsSqlData.Insert<IcsMatCheckResult>(entity);
//合格给到仓库进行入库，不合格则由IQC组长进行判定；
#region 合格
if (model.Result.ToUpper() == "OK")
{
Pagination pagination = new Pagination();
var itemLotDt = GetMaterialInStorageCheckDetailList(model.AsnCode, model.InvBatcgNo, model.InvCode, ref pagination);
var itemLotList = JsonConvert.DeserializeObject<List<IcsItemLotInIQCCheckDto>>(itemLotDt.ToJson());
GoodInStorage(dtNow, model, itemLotList);
}
#endregion
#region 检验项目保存
List<IcsMatCheckResultDetail> detailList = new List<IcsMatCheckResultDetail>();
foreach (var item in model.CCList)
{
IcsMatCheckResultDetail detail = ConvertExt.Mapping<IcsMatCheckResultDetailDto, IcsMatCheckResultDetail>(item);
detail.CheckNo = serialNo;
detail.ID = Guid.NewGuid().ToString();
detail.MUSER = MUSER;
detail.MUSERName = MUSERNAME;
detail.MTIME = dtNow;
detail.WorkPoint = WorkPoint;
// detail.CheckItemCod
detailList.Add(detail);
}
foreach (var item in model.WGList)
{
IcsMatCheckResultDetail detail = ConvertExt.Mapping<IcsMatCheckResultDetailDto, IcsMatCheckResultDetail>(item);
detail.CheckNo = serialNo;
detail.ID = Guid.NewGuid().ToString();
detail.MUSER = MUSER;
detail.MUSERName = MUSERNAME;
detail.MTIME = dtNow;
detail.WorkPoint = WorkPoint;
detailList.Add(detail);
}
foreach (var item in model.XNList)
{
IcsMatCheckResultDetail detail = ConvertExt.Mapping<IcsMatCheckResultDetailDto, IcsMatCheckResultDetail>(item);
detail.CheckNo = serialNo;
detail.ID = Guid.NewGuid().ToString();
detail.MUSER = MUSER;
detail.MUSERName = MUSERNAME;
detail.MTIME = dtNow;
detail.WorkPoint = WorkPoint;
detailList.Add(detail);
}
if (detailList.Count > 0)
{
MsSqlData.Insert<IcsMatCheckResultDetail>(detailList);
}
#endregion
#region 实验抽选数量拒收单
if (model.FinalItemLotList != null && model.FinalItemLotList.Count > 0)
{
// var goodList = model.FinalItemLotList.Where(x => x.LeftQty.HasValue && x.GoodQty > 0).ToList();
var ngList = model.FinalItemLotList.Where(x => x.SampleQty.HasValue && x.SampleQty > 0).ToList();
//GoodInStorage(dtNow, model, goodList);
if (ngList.Count > 0)
{
var itemLotNos = ngList.Select(x => x.LotNo);
var lotNoStr = string.Empty;
foreach (var str in itemLotNos)
{
lotNoStr += "'" + str + "',";
}
//记录日志
InsertOperateLog(serialNo, JsonConvert.SerializeObject(itemLotNos), entity.GetType().Name, "实验");
if (model.EATTRIBUTE2 == "1")
{
var msg = otherApp.CreateRejection4KBS(lotNoStr);
if (!msg.IsNullOrEmpty())
{
throw new Exception(msg);
}
}
else if (model.EATTRIBUTE2 == "2")
{
var msg = otherApp.CreateWWRejection4KBS(lotNoStr);
if (!msg.IsNullOrEmpty())
{
throw new Exception(msg);
}
}
else
{
}
SendEmail("1",model);
}
}
#endregion
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
public string SaveMaterialCheckReJudgeResult(string keyValue)
{
string returnValue = string.Empty;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
try
{
var dtNow = DateTime.Now;
var model = JsonConvert.DeserializeObject<IcsMatCheckResultDto>(keyValue);
if (model == null)
{
throw new Exception("传入参数错误");
}
var entity = MsSqlData.Get<IcsMatCheckResult>(model.ID);
if (entity == null)
{
throw new Exception("当前数据不存在");
}
Pagination pagination = new Pagination();
var itemLotDt = GetMaterialInStorageCheckDetailList(model.AsnCode, model.InvBatcgNo, model.InvCode, ref pagination);
var itemLotList = JsonConvert.DeserializeObject<List<IcsItemLotInIQCCheckDto>>(itemLotDt.ToJson());
switch (model.JudgeRole)
{
case "IQCGroupHeader":
entity.IQCGroupHeaderResult = model.IQCGroupHeaderResult;
entity.IQCGroupHeaderRemark = model.IQCGroupHeaderRemark;
entity.IQCGroupHeaderUser = MUSERNAME;
entity.IQCGroupHeaderTime = dtNow;
entity.BRGCode = model.BRGCode;
//if (entity.IQCGroupHeaderResult == "")
//{
// entity
//}
//IQC组长 2.1.合格交给仓库进行入库，不合格由SQE工程师进行复判；
#region IQC组长
if (entity.IQCGroupHeaderResult == "OK")
{
entity.IsCheckComplete = "是";
GoodInStorage(dtNow, model, itemLotList);
}
#endregion
break;
case "IQCHeader":
entity.IQCHeaderResult = model.IQCHeaderResult;
entity.IQCHeaderRemark = model.IQCHeaderRemark;
entity.BRGCode = model.BRGCode;
entity.BadReasonCode = model.BadReasonCode;
entity.BCGCode = model.BCGCode;
entity.IQCHeaderUser = MUSERNAME;
entity.IQCHeaderTime = dtNow;
//IQC主管判定 合格后由仓库进行入库，不合格则进行最终判定,并发送邮件
if (entity.IQCHeaderResult == "OK")
{
entity.IsCheckComplete = "是";
GoodInStorage(dtNow, model, itemLotList);
}
else
{
SendEmail("3",model);
}
break;
case "SQEEngineer":
entity.SQEEngineerResult = model.SQEEngineerResult;
entity.SQEEngineerRemark = model.SQEEngineerRemark;
// entity.BRGCode = model.BRGCode;
entity.SQEEngineerUser = MUSERNAME;
entity.SQEEngineerTime = dtNow;
if (entity.SQEEngineerResult == "NG")
{
SendEmail("2", model);
}
break;
default:
break;
}
entity.EATTRIBUTE1 = model.EATTRIBUTE1;
entity.MUSER = MUSER;
entity.MUSERName = MUSERNAME;
entity.MTIME = dtNow;
entity.SQEFile = model.SQEFile;
var result = MsSqlData.Update<IcsMatCheckResult>(entity);
if (!result)
{
returnValue = "修改失败";
}
DataTable dt = new DataTable();
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
private void GoodInStorage(DateTime dtNow, IcsMatCheckResultDto model, List<IcsItemLotInIQCCheckDto> list)
{
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
if (list.Count == 0)
{
return;
}
//var para = new
//{
// WorkPoint = WorkPoint,
//};
//var lotNos = list.Select(x => x.LotNo);
//var sql = @"select * from ICSInspection a with(nolock) where a.LotNo in ('" + string.Join("','", lotNos) + "') and a.WorkPoint=@WorkPoint";
////查询已存在条码记录
//var getList = MsSqlData.Query<ICSInspection>(sql, para).ToList();
var lotNos = list.Select(x => x.LotNo);
var sql = @"select * from ICSInspection a with(nolock) where a.LotNo in ('" + string.Join("','", lotNos.ToArray()) + "') and a.WorkPoint='" + WorkPoint + "'";
//查询已存在条码记录
var getList = MsSqlData.Query<ICSInspection>(sql).ToList();
List<ICSInspection> checkMainList4Insert = new List<ICSInspection>();
List<ICSInspection> checkMainList4Update = new List<ICSInspection>();
foreach (var item in list)
{
// var isEdit = false;
var inspection = getList.Where(x => x.LotNo == item.LotNo).FirstOrDefault();
if (inspection == null)
{
inspection = new ICSInspection();
inspection.ID = Guid.NewGuid().ToString();
inspection.LotNo = item.LotNo;
inspection.InvCode = item.InvCode;
inspection.Quantity = item.Quantity;
inspection.BCCode = model.BCGCode;
inspection.BRCode = model.BadReasonCode;
inspection.Type = "1";
//inspection.Result = false;
inspection.Enable = true;
inspection.WaiveQuantity = 0;
inspection.SampleQuantity = item.SampleQty.ToDecimal();
inspection.UnqualifiedQuantity = item.NgQty.ToDecimal();
inspection.QualifiedQuantity = inspection.Quantity - inspection.SampleQuantity - inspection.UnqualifiedQuantity;
inspection.MUSER = MUSER;
inspection.MUSERName = MUSERNAME;
inspection.MTIME = dtNow;
inspection.WorkPoint = WorkPoint;
checkMainList4Insert.Add(inspection);
}
else
{
inspection.BCCode = model.BCGCode;
inspection.BRCode = model.BadReasonCode;
inspection.WaiveQuantity = 0;
inspection.SampleQuantity = item.SampleQty.ToDecimal();
inspection.UnqualifiedQuantity = item.NgQty.ToDecimal();
inspection.QualifiedQuantity = inspection.Quantity - inspection.SampleQuantity - inspection.UnqualifiedQuantity;
inspection.MUSER = MUSER;
inspection.MUSERName = MUSERNAME;
inspection.MTIME = dtNow;
checkMainList4Update.Add(inspection);
}
}
var line = true;
if (checkMainList4Insert.Count > 0)
line = MsSqlData.Insert<ICSInspection>(checkMainList4Insert);
if (checkMainList4Update.Count > 0)
line = MsSqlData.Update<ICSInspection>(checkMainList4Update);
}
public string SaveMaterialCheckFinalJudgeResult(string keyValue)
{
string returnValue = string.Empty;
// List<decimal> list2 = new List<decimal>();
// decimal maxaa = list2.Select(x => x).ToList().Max();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var otherApp = new DHICSRCVIQCsApp();
try
{
var dtNow = DateTime.Now;
var model = JsonConvert.DeserializeObject<IcsMatCheckResultDto>(keyValue);
if (model == null)
{
throw new Exception("传入参数错误");
}
var entity = MsSqlData.Get<IcsMatCheckResult>(model.ID);
if (entity == null)
{
throw new Exception("当前数据不存在");
}
if (model.FinalItemLotList == null || model.FinalItemLotList.Count == 0)
{
throw new Exception("当前检验报告不存在物料条码");
}
switch (model.FinalResult)
{
case "判退":
case "NG":
//③判退：所有的条码不合格，不能输入合格或不合格数量，点击确定即可；(条码拒收)
//使用当前基础班的拒收
//var goodList2 = model.FinalItemLotList.Where(x => x.GoodQty.HasValue && x.GoodQty > 0).ToList();
//var ngList2 = model.FinalItemLotList.Where(x => x.NgQty.HasValue && x.NgQty > 0).ToList();
#region //入库检验数据
GoodInStorage(dtNow, model, model.FinalItemLotList);
#endregion
var itemLotNos = model.FinalItemLotList.Select(x => x.LotNo);
if (itemLotNos.Count() == 0)
{
throw new Exception("不存在物料条码");
}
var lotNoStr = string.Empty;
foreach (var str in itemLotNos)
{
lotNoStr += "'" + str + "',";
}
//记录日志
InsertOperateLog(model.CheckNo, JsonConvert.SerializeObject(itemLotNos), entity.GetType().Name, "判退");
// return "";
if (entity.EATTRIBUTE2 == "1")
{
var msg = otherApp.CreateRejection4KBS(lotNoStr);
if (!msg.IsNullOrEmpty())
{
throw new Exception(msg);
}
}
else if (entity.EATTRIBUTE2 == "2")
{
var msg = otherApp.CreateWWRejection4KBS(lotNoStr);
if (!msg.IsNullOrEmpty())
{
throw new Exception(msg);
}
}
else
{
}
//发邮件
// 参考SRM邮件格式
//通过物料条码找采购订单/委外订单 采购人员 人员邮箱
//
SendEmail("1",model);
break;
case "返工":
//合格入库 新增 ICSInspection ；不合格拒收，先保存检验记录，在调用拒收单
var goodList = model.FinalItemLotList.Where(x => x.GoodQty.HasValue && x.GoodQty > 0).ToList();
var ngList = model.FinalItemLotList.Where(x => x.NgQty.HasValue && x.NgQty > 0).ToList();
#region //入库检验数据
GoodInStorage(dtNow, model, model.FinalItemLotList);
#endregion
#region //不合格拒收
if (ngList.Count > 0)
{
var itemLotNos2 = ngList.Select(x => x.LotNo);
var lotNoStr2 = string.Empty;
foreach (var str in itemLotNos2)
{
lotNoStr2 += "'" + str + "',";
}
//记录日志
InsertOperateLog(model.CheckNo, JsonConvert.SerializeObject(itemLotNos2), entity.GetType().Name, "返工");
if (entity.EATTRIBUTE2 == "1")
{
var msg = otherApp.CreateRejection4KBS(lotNoStr2);
if (!msg.IsNullOrEmpty())
{
throw new Exception(msg);
}
}
else if (entity.EATTRIBUTE2 == "2")
{
var msg = otherApp.CreateWWRejection4KBS(lotNoStr2);
if (!msg.IsNullOrEmpty())
{
throw new Exception(msg);
}
}
else
{
}
SendEmail("1",model);
}
#endregion
break;
case "让步接收":
case "合格":
entity.SpecialNo = model.SpecialNo;
entity.SpecialFile = model.SpecialFile;
var goodList3 = model.FinalItemLotList.Where(x => x.GoodQty.HasValue && x.GoodQty > 0).ToList();
var ngList3 = model.FinalItemLotList.Where(x => x.NgQty.HasValue && x.NgQty > 0).ToList();
#region //入库检验数据
GoodInStorage(dtNow, model, model.FinalItemLotList);
#endregion
#region //不合格拒收
if (ngList3.Count > 0)
{
var itemLotNos3 = ngList3.Select(x => x.LotNo);
var lotNoStr3 = string.Empty;
foreach (var str in itemLotNos3)
{
lotNoStr3 += "'" + str + "',";
}
//记录日志
InsertOperateLog(model.CheckNo, JsonConvert.SerializeObject(itemLotNos3), entity.GetType().Name, "让步接收");
if (entity.EATTRIBUTE2 == "1")
{
var msg = otherApp.CreateRejection4KBS(lotNoStr3);
if (!msg.IsNullOrEmpty())
{
throw new Exception(msg);
}
}
else if (entity.EATTRIBUTE2 == "2")
{
var msg = otherApp.CreateWWRejection4KBS(lotNoStr3);
if (!msg.IsNullOrEmpty())
{
throw new Exception(msg);
}
}
else
{
}
SendEmail("1",model);
}
#endregion
break;
default:
break;
}
entity.IsCheckComplete = "是";
entity.Remark = model.Remark;
entity.FinalResult = model.FinalResult;
entity.FinalUser = MUSERNAME;
entity.FinalTime = dtNow;
entity.MUSER = MUSER;
entity.MUSERName = MUSERNAME;
entity.MTIME = dtNow;
entity.GoodQty = model.FinalItemLotList.Sum(x => x.GoodQty ?? 0);
entity.NgQty = model.FinalItemLotList.Sum(x => x.NgQty ?? 0);
var result = MsSqlData.Update<IcsMatCheckResult>(entity);
if (!result)
{
returnValue = "修改失败";
}
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
public DataTable GetMaterialCheckMain4WG(string keyValue)
{
DataTable dt = new DataTable();
// var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
string sql0 = @"
select *
from V_QuerySysEnumItem a
where a.F_EnCode='CheckAttr'
";
var enumList = MsSqlData.Query<SysEnumValueDto>(sql0, para).ToList();
var chicun = enumList.Where(x => x.Ext1 == "外观").Select(x => x.value);
if (chicun.Count() == 0)
{
return new DataTable();
}
#region [SQL]
string sql = @"
select
a.ID
,c.InvCode
,c.InvName
,a.ListCode CheckItemCode
,a.ListName CheckItemName
,a.EATTRIBUTE1 as CheckWay --检验方法
,b.EATTRIBUTE1 as CheckAttr --检验属性
,( select t.label from V_QuerySysEnumItem t where t.value=b.EATTRIBUTE1 and t.F_EnCode='CheckAttr'
) as CheckAttrText
,a.SetValueMax
,a.SetValueMin
,a.Unit
,d.RulesCode
,d.RulesName CheckAqlName
,e.Type RulesType
,10 as AqlQty
,0 as GoodQty
,0 as NgQty
,'' as Result
,'' as Remark
from ICSInventoryInspectionList a with(nolock)
left join ICSInventoryInspectionGroup b with(nolock) on a.InvGroupID=b.ID and a.WorkPoint=b.WorkPoint
left join ICSInventory c with(nolock) on b.InvCode=c.InvCode and b.WorkPoint=c.WorkPoint
left join ICSInventoryInspectionRulesGroup d with(nolock) on b.InvCode=d.InvCode and b.EATTRIBUTE1=d.EATTRIBUTE1 and b.WorkPoint=d.WorkPoint
left join ICSInspectionRulesGroup e with(nolock) on d.rulescode=e.RulesCode
where 1=1
{0}
<1>
and b.EATTRIBUTE1 in ('" + string.Join("','", chicun) + "') and d.EATTRIBUTE1 in ('" + string.Join("','", chicun) + "') and a.Enable=1";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
sql = sql.Replace("<1>", " and b.InvCode='" + keyValue + "' ");
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
{
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
// sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
}
else
{
sql = sql.Replace("{0}", "");
}
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
return Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
}
public DataTable GetMaterialCheckMain4CC(string keyValue)
{
DataTable dt = new DataTable();
// var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
string sql0 = @"
select *
from V_QuerySysEnumItem a
where a.F_EnCode='CheckAttr'
";
var enumList = MsSqlData.Query<SysEnumValueDto>(sql0, para).ToList();
var chicun = enumList.Where(x => x.Ext1 == "尺寸").Select(x => x.value);
if (chicun.Count() == 0)
{
return new DataTable();
}
#region [SQL]
string sql = @"
select
a.ID
,c.InvCode
,c.InvName
,a.ListCode CheckItemCode
,a.ListName CheckItemName
,a.EATTRIBUTE1 as CheckWay --检验方法
,b.EATTRIBUTE1 as CheckAttr --检验属性
,( select t.label from V_QuerySysEnumItem t where t.value=b.EATTRIBUTE1 and t.F_EnCode='CheckAttr'
) as CheckAttrText
,a.SetValueMax
,a.SetValueMin
,a.Unit
,d.RulesCode
,d.RulesName CheckAqlName
,d.RulesDesc
,e.Type RulesType
,0 as S1
,0 as S2
,0 as S3
,0 as S4
,0 as S5
,0 as S6
,0 as S7
,0 as S8
,0 as S9
,0 as S10
,'' as Result
,'' as Remark
from ICSInventoryInspectionList a with(nolock)
left join ICSInventoryInspectionGroup b with(nolock) on a.InvGroupID=b.ID and a.WorkPoint=b.WorkPoint
left join ICSInventory c with(nolock) on b.InvCode=c.InvCode and b.WorkPoint=b.WorkPoint
left join ICSInventoryInspectionRulesGroup d with(nolock) on b.InvCode=d.InvCode and b.EATTRIBUTE1=d.EATTRIBUTE1 and b.WorkPoint=b.WorkPoint
left join ICSInspectionRulesGroup e with(nolock) on d.rulescode=e.RulesCode
where 1=1
{0}
<1>
and b.EATTRIBUTE1 in ('" + string.Join("','", chicun) + "') and d.EATTRIBUTE1 in ('" + string.Join("','", chicun) + "') and a.Enable=1";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
sql = sql.Replace("<1>", " and b.InvCode='" + keyValue + "' ");
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
{
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
// sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
}
else
{
sql = sql.Replace("{0}", "");
}
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
return Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
}
public DataTable GetMaterialCheckMain4XN(string keyValue)
{
DataTable dt = new DataTable();
// var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
string sql0 = @"
select *
from V_QuerySysEnumItem a
where a.F_EnCode='CheckAttr'
";
var enumList = MsSqlData.Query<SysEnumValueDto>(sql0, para).ToList();
var chicun = enumList.Where(x => x.Ext1 == "性能").Select(x => x.value);
if (chicun.Count() == 0)
{
return new DataTable();
}
#region [SQL]
string sql = @"
select
a.ID
,c.InvCode
,c.InvName
,a.ListCode CheckItemCode
,a.ListName CheckItemName
,a.EATTRIBUTE1 as CheckWay --检验方法
,b.EATTRIBUTE1 as CheckAttr --检验属性
,( select t.label from V_QuerySysEnumItem t where t.value=b.EATTRIBUTE1 and t.F_EnCode='CheckAttr'
) as CheckAttrText
,a.SetValueMax
,a.SetValueMin
,a.Unit
,d.RulesCode
,d.RulesName CheckAqlName
,e.Type RulesType
,0 as S1
,0 as S2
,0 as S3
,0 as S4
,0 as S5
,0 as S6
,0 as S7
,0 as S8
,0 as S9
,0 as S10
,'' as Result
,'' as Remark
from ICSInventoryInspectionList a with(nolock)
left join ICSInventoryInspectionGroup b with(nolock) on a.InvGroupID=b.ID and a.WorkPoint=b.WorkPoint
left join ICSInventory c with(nolock) on b.InvCode=c.InvCode and b.WorkPoint=b.WorkPoint
left join ICSInventoryInspectionRulesGroup d with(nolock) on b.InvCode=d.InvCode and b.EATTRIBUTE1=d.EATTRIBUTE1 and b.WorkPoint=b.WorkPoint
left join ICSInspectionRulesGroup e with(nolock) on d.rulescode=e.RulesCode
where 1=1
{0}
<1>
and b.EATTRIBUTE1 in ('" + string.Join("','", chicun) + "') and d.EATTRIBUTE1 in ('" + string.Join("','", chicun) + "') and a.Enable=1";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
sql = sql.Replace("<1>", " and b.InvCode='" + keyValue + "' ");
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
{
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
// sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
}
else
{
sql = sql.Replace("{0}", "");
}
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
return Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
}
public DataTable GetMaterialCheckMain(string keyValue)
{
List<DbParameter> parameter = new List<DbParameter>();
string sql = @"select
a.*
from
(
select
distinct
b.ID DetailId --子表主键
,c.ID LotNoId --条码ID
,c.InvCode
,d.InvName
,c1.BatchCode as InvBatcgNo --物料批号
,b.DNQuantity --到货数量
,a.VenCode --供应商编码
,e.VenName --供应商名称
,a.AsnCode
,b.EATTRIBUTE1 as IsUrgentitem --加急料
,d.InvUnit
,c.Quantity as InvBatcgQty --批次数量
,f.RulesCode
,f.Id as InvRuleId
,0 as AqlCheckQty --抽检数量
,0 as AllowNgQty --允许不良数(关键)
,'' as Result
from ICSASN a with(nolock)
left join ICSASNDetail b with(nolock) on a.ASNCode=b.ASNCode and a.WorkPoint=b.WorkPoint
left join ICSInventoryLot c with(nolock) on b.LotNo=c.LotNo and b.WorkPoint=b.WorkPoint
left join ICSExtension c1 with(nolock) on c.ExtensionID=c1.ID and c.WorkPoint=c1.WorkPoint
left join ICSInventory d with(nolock) on c.InvCode=d.InvCode and c.WorkPoint=c.WorkPoint
left join ICSVendor e with(nolock) on a.VenCode=e.VenCode and a.WorkPoint=e.WorkPoint
left join ICSInventoryInspectionRulesGroup f with(nolock) on c.InvCode=f.InvCode and c.WorkPoint=f.WorkPoint
where 1=1
and a.WorkPoint = '{1}'
union all
select
distinct
b.ID DetailId --子表主键
,c.ID LotNoId --条码ID
,c.InvCode
,d.InvName
,c1.BatchCode as InvBatcgNo --物料批号
,b.ODNQuantity --到货数量
,a.VenCode --供应商编码
,e.VenName --供应商名称
,a.OAsnCode
,b.EATTRIBUTE1 as IsUrgentitem --加急料
,d.InvUnit
,c.Quantity as InvBatcgQty --批次数量
,f.RulesCode
,f.Id as InvRuleId
,0 as AqlCheckQty --抽检数量
,0 as AllowNgQty --允许不良数(关键)
,'' as Result
from ICSOASN a with(nolock)
left join ICSOASNDetail b with(nolock) on a.OASNCode=b.OASNCode and a.WorkPoint=b.WorkPoint
left join ICSInventoryLot c with(nolock) on b.LotNo=c.LotNo and b.WorkPoint=b.WorkPoint
left join ICSExtension c1 with(nolock) on c.ExtensionID=c1.ID and c.WorkPoint=c1.WorkPoint
left join ICSInventory d with(nolock) on c.InvCode=d.InvCode and c.WorkPoint=c.WorkPoint
left join ICSVendor e with(nolock) on a.VenCode=e.VenCode and a.WorkPoint=e.WorkPoint
left join ICSInventoryInspectionRulesGroup f with(nolock) on c.InvCode=f.InvCode and c.WorkPoint=f.WorkPoint
where 1=1
and a.WorkPoint = '{1}'
) a
where 1=1
and a.DetailId='{0}'
";
sql = string.Format(sql, keyValue, NFine.Code.OperatorProvider.Provider.GetCurrent().Location);
// sql = sql.Replace("{1}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
var dt = Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
var sql2 = @"select
a.*
from ICSInspectionRulesList a with(nolock)
where 1=1
and a.WorkPoint = '{1}'
and a.RulesID=(
select
a.ID
from ICSInspectionRulesGroup a with(nolock)
where 1=1
and a.RulesCode='{0}'
)
order by a.SetValueMin asc
";
sql2 = string.Format(sql2, dt.Rows[0]["RulesCode"].ToString(), NFine.Code.OperatorProvider.Provider.GetCurrent().Location);
var dt2 = Repository().FindDataSetBySql(sql2.ToString(), parameter.ToArray()).Tables[0];
if (dt2 != null && dt2.Rows.Count > 0)
{
foreach (DataRow item in dt2.Rows)
{
if (item["SetValueMin"].ToDecimal() <= dt.Rows[0]["InvBatcgQty"].ToDecimal() &&
item["SetValueMax"].ToDecimal() >= dt.Rows[0]["InvBatcgQty"].ToDecimal())
{
dt.Rows[0]["AqlCheckQty"] = item["SampleQuantity"].ToDecimal();
dt.Rows[0]["AllowNgQty"] = item["EATTRIBUTE1"].ToDecimal();
}
}
}
}
return dt;
}
public DataTable GetMaterialCheckMain2(string keyValue, decimal qty)
{
List<DbParameter> parameter = new List<DbParameter>();
string sql = @"
select
a.*
from
(
select
distinct
b.ID DetailId --子表主键
,c.ID LotNoId --条码ID
,c.InvCode
,d.InvName
,d.InvStd
,c1.BatchCode as InvBatcgNo --物料批号
,b.DNQuantity --到货数量
,a.VenCode --供应商编码
,e.VenName --供应商名称
,a.AsnCode
,b.EATTRIBUTE1 as IsUrgentitem --加急料
,d.InvUnit
,c.Quantity as InvBatcgQty --批次数量
,f.RulesCode
,f.Id as InvRuleId
,0 as AqlCheckQty --抽检数量
,0 as AllowNgQty --允许不良数(关键)
,'' as Result
,'' as WgNgFile
from ICSASN a with(nolock)
left join ICSASNDetail b with(nolock) on a.ASNCode=b.ASNCode and a.WorkPoint=b.WorkPoint
left join ICSInventoryLot c with(nolock) on b.LotNo=c.LotNo and b.WorkPoint=b.WorkPoint
left join ICSExtension c1 with(nolock) on c.ExtensionID=c1.ID and c.WorkPoint=c1.WorkPoint
left join ICSInventory d with(nolock) on c.InvCode=d.InvCode and c.WorkPoint=c.WorkPoint
left join ICSVendor e with(nolock) on a.VenCode=e.VenCode and a.WorkPoint=e.WorkPoint
left join ICSInventoryInspectionRulesGroup f with(nolock) on c.InvCode=f.InvCode and c.WorkPoint=f.WorkPoint
where 1=1
and a.WorkPoint = '{1}'
union all
select
distinct
b.ID DetailId --子表主键
,c.ID LotNoId --条码ID
,c.InvCode
,d.InvName
,d.InvStd
,c1.BatchCode as InvBatcgNo --物料批号
,b.ODNQuantity --到货数量
,a.VenCode --供应商编码
,e.VenName --供应商名称
,a.OAsnCode
,b.EATTRIBUTE1 as IsUrgentitem --加急料
,d.InvUnit
,c.Quantity as InvBatcgQty --批次数量
,f.RulesCode
,f.Id as InvRuleId
,0 as AqlCheckQty --抽检数量
,0 as AllowNgQty --允许不良数(关键)
,'' as Result
,'' as WgNgFile
from ICSOASN a with(nolock)
left join ICSOASNDetail b with(nolock) on a.OASNCode=b.OASNCode and a.WorkPoint=b.WorkPoint
left join ICSInventoryLot c with(nolock) on b.LotNo=c.LotNo and b.WorkPoint=b.WorkPoint
left join ICSExtension c1 with(nolock) on c.ExtensionID=c1.ID and c.WorkPoint=c1.WorkPoint
left join ICSInventory d with(nolock) on c.InvCode=d.InvCode and c.WorkPoint=c.WorkPoint
left join ICSVendor e with(nolock) on a.VenCode=e.VenCode and a.WorkPoint=e.WorkPoint
left join ICSInventoryInspectionRulesGroup f with(nolock) on c.InvCode=f.InvCode and c.WorkPoint=f.WorkPoint
where 1=1
and a.WorkPoint = '{1}'
) a
where 1=1
and a.DetailId='{0}'
";
sql = string.Format(sql, keyValue, NFine.Code.OperatorProvider.Provider.GetCurrent().Location);
// sql = sql.Replace("{1}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
var dt = Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
var sql2 = @"select
a.*
from ICSInspectionRulesList a with(nolock)
where 1=1
and a.WorkPoint = '{1}'
and a.RulesID=(
select
a.ID
from ICSInspectionRulesGroup a with(nolock)
where 1=1
and a.RulesCode='{0}'
)
order by a.SetValueMin asc
";
sql2 = string.Format(sql2, dt.Rows[0]["RulesCode"].ToString(), NFine.Code.OperatorProvider.Provider.GetCurrent().Location);
var dt2 = Repository().FindDataSetBySql(sql2.ToString(), parameter.ToArray()).Tables[0];
if (dt2 != null && dt2.Rows.Count > 0)
{
foreach (DataRow item in dt2.Rows)
{
if (item["SetValueMin"].ToDecimal() <= qty &&
item["SetValueMax"].ToDecimal() >= qty)
{
dt.Rows[0]["AqlCheckQty"] = item["SampleQuantity"].ToDecimal();
dt.Rows[0]["AllowNgQty"] = item["EATTRIBUTE1"].ToDecimal();
}
}
}
}
return dt;
}
public string UpdateUrgent(string keyValue)
{
string returnValue = string.Empty;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
try
{
var dtNow = DateTime.Now;
var models = JsonConvert.DeserializeObject<List<IcsMatCheckResultDto>>(keyValue);
if (models == null)
{
throw new Exception("传入参数错误");
}
foreach (var model in models)
{
var para = new
{
AsnCode = model.AsnCode,
InvBatcgNo = model.InvBatcgNo,
InvCode = model.InvCode,
WorkPoint = WorkPoint,
};
#region 送货单
var sql = @"
select a.* from ICSASNDetail a with(nolock)
where a.id in (
select
distinct a.Id
from ICSASNDetail a with(nolock)
left join ICSASN b with(nolock) on a.ASNCode=b.ASNCode
left join ICSInventoryLot c with(nolock) on a.LotNo=c.LotNo
left join ICSExtension c1 with(nolock) on c.ExtensionID=c1.ID
where 1=1
and a.ASNCode=@AsnCode
and c1.BatchCode=@InvBatcgNo
and c.InvCode=@InvCode
and a.WorkPoint=@WorkPoint
)
";
var list = MsSqlData.Query<ICSASNDetailEntity>(sql, para).ToList();
foreach (var item in list)
{
if (item.EATTRIBUTE1 == "是")
{
continue;
}
item.EATTRIBUTE1 = "是";
item.MUSER = MUSER;
item.MUSERName = MUSERNAME;
item.MTIME = dtNow;
item.WorkPoint = WorkPoint;
var result2 = MsSqlData.Update<ICSASNDetailEntity>(item);
}
#endregion
#region 委外送货单
sql = @"select a.* from ICSOASNDetail a with(nolock)
where a.id in (
select
distinct a.Id
from ICSOASNDetail a with(nolock)
left join ICSOASN b with(nolock) on a.OASNCode=b.OASNCode
left join ICSInventoryLot c with(nolock) on a.LotNo=c.LotNo
left join ICSExtension c1 with(nolock) on c.ExtensionID=c1.ID
where 1=1
and a.OASNCode=@AsnCode
and c1.BatchCode=@InvBatcgNo
and c.InvCode=@InvCode
and a.WorkPoint=@WorkPoint
)
";
var list2 = MsSqlData.Query<ICSOASNDetailEntity>(sql, para).ToList();
foreach (var item in list2)
{
if (item.EATTRIBUTE1 == "是")
{
continue;
}
item.EATTRIBUTE1 = "是";
item.MUSER = MUSER;
item.MUSERName = MUSERNAME;
item.MTIME = dtNow;
item.WorkPoint = WorkPoint;
var result2 = MsSqlData.Update<ICSOASNDetailEntity>(item);
}
#endregion
}
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
public DataTable GetMaterialInStorageCheckDetailList(string id, string lotno, string invCode, ref Pagination pagination)
{
DataTable dt = new DataTable();
// var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
#region [SQL]
string sql = @"select
( Row_Number() over( ORDER BY a.ASNCode,a.InvBatcgNo,a.LotNo )) AS RowNo
,a.*
from
(
select
distinct
a.ASNCode
,d.TransCode
,d.TransSequence
,c.InvCode
,inv.InvName
,c1.BatchCode as InvBatcgNo --物料批号
,b.LotNo
,b.DNQuantity Quantity
,f.ContainerCode as XiangCode--箱号编码
,g.ContainerCode as ZhanCode --栈板编码
from ICSASN a with(nolock)
left join ICSASNDetail b with(nolock) on a.ASNCode=b.ASNCode and a.WorkPoint=b.WorkPoint and ISnull(b.DNQuantity,0)>0
left join ICSInventoryLot c with(nolock) on b.LotNo=c.LotNo and b.WorkPoint=c.WorkPoint
left join ICSExtension c1 with(nolock) on c.ExtensionID=c1.ID
left join ICSInventoryLotDetail d on b.LotNo=d.LotNo and b.WorkPoint=b.WorkPoint
left join ICSInventory inv with(nolock) on c.InvCode=inv.InvCode and c.WorkPoint=inv.WorkPoint
left join ICSContainerLot e with(nolock) on b.LotNo=e.LotNo and b.WorkPoint=e.WorkPoint
left join ICSContainer f with(nolock) on e.ContainerID =f.ID and e.WorkPoint=f.WorkPoint
left join ICSContainer g with(nolock) on f.ContainerID=g.ID and g.ContainerType='ContainerType04' and f.WorkPoint=g.WorkPoint
where c.Quantity>0 AND 1=1
{0}
union all
select
distinct
a.OASNCode
,d.TransCode
,d.TransSequence
,c.InvCode
,inv.InvName
,c1.BatchCode as InvBatcgNo --物料批号
,b.LotNo
,b.ODNQuantity Quantity
,f.ContainerCode as XiangCode--箱号编码
,g.ContainerCode as ZhanCode --栈板编码
from ICSOASN a with(nolock)
left join ICSOASNDetail b with(nolock) on a.OASNCode=b.OASNCode and a.WorkPoint=b.WorkPoint and isnull(b.ODNQuantity,0)>0
left join ICSInventoryLot c with(nolock) on b.LotNo=c.LotNo and b.WorkPoint=c.WorkPoint
left join ICSExtension c1 with(nolock) on c.ExtensionID=c1.ID
left join ICSInventoryLotDetail d on b.LotNo=d.LotNo and b.WorkPoint=b.WorkPoint
left join ICSInventory inv with(nolock) on c.InvCode=inv.InvCode and c.WorkPoint=inv.WorkPoint
left join ICSContainerLot e with(nolock) on b.LotNo=e.LotNo and b.WorkPoint=e.WorkPoint
left join ICSContainer f with(nolock) on e.ContainerID =f.ID and e.WorkPoint=f.WorkPoint
left join ICSContainer g with(nolock) on f.ContainerID=g.ID and g.ContainerType='ContainerType04' and f.WorkPoint=g.WorkPoint
where c.Quantity>0 AND 1=1
--and a.WorkPoint = 'UFDATA_106_2019'
-- and a.OASNCode='DN010000052200103' and c1.BatchCode=''
{0}
) a
where a.Quantity>0 AND a.InvCode='" + invCode + @"'
<1>
order by a.ASNCode,a.InvBatcgNo,a.LotNo
";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
sql = sql.Replace("<1>", " and a.ASNCode='" + id + "' and a.InvBatcgNo='" + lotno + "' ");
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
return Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
}
#region 20251107 （杨甜美修改咖博士来料检验要求注释）
// public DataTable GetMaterialInStorageCheckPageList(string queryJson, ref Pagination pagination)
// {
// DataTable dt = new DataTable();
// var queryParam = queryJson.ToJObject();
// List<DbParameter> parameter = new List<DbParameter>();
// //object Figure = GetDecimalDigits();
// #region [SQL]
// string sql = @"
//select
//a.*
//from
//(
//select
//distinct
//max(b.ID) DetailId --子表主键
//,Max(c.ID) LotNoId --条码ID
//,c.InvCode
//,inv.InvName
//,inv.InvStd
//,c1.BatchCode as InvBatcgNo --物料批号
//,sum(b.DNQuantity) DNQuantity --到货数量
//,max(a.VenCode) VenCode --供应商编码
//,max(e.VenName ) VenName --供应商名称
//,a.ASNCode
//,max(b.EATTRIBUTE1) as IsUrgentItem --加急料
//,max(inv.InvUnit) InvUnit
//,sum(c.Quantity) Quantity
//,max(c2.FileName) OutReport
//,max(c2.FilePath) FilePath
//,max(g.id) CheckID
//,case when max(g.CheckNo) is null then '否'else '是' end as IsCheck
//,max(g.Result) Result
//,max(g.FirstCheckTime) CheckDate
//,max(g.WHCode) WHCode
//,max(Isnull(g.FirstCheckUser,g.MUSERName)) FirstCheckUser
//,max(l.CreateDateTime) DHDate
//,max(l.DNCode) DNCode
//,'1' as DocType
//from ICSASN a with(nolock)
//left join ICSASNDetail b with(nolock) on a.ASNCode=b.ASNCode and a.WorkPoint=b.WorkPoint
//left join ICSInventoryLot c with(nolock) on b.LotNo=c.LotNo and b.WorkPoint=b.WorkPoint
//left join ICSInventoryLotDetail cc with(nolock) on b.lotno=cc.lotno and cc.WorkPoint=c.WorkPoint
//left join ICSExtension c1 with(nolock) on c.ExtensionID=c1.ID
//left join (
// select *
// from
// (
// select t.*
// ,row_number() over(partition by t.ASNCode,t.BatchCode,t.invcode order by t.CerateTime desc) RowSeq
// from ICSASNShippingReport t with(nolock)
// ) t where t.RowSeq=1
//) c2 on a.ASNCode=c2.ASNCode and c1.BatchCode=c2.BatchCode and c.invcode=c2.invcode
//left join ICSInventory inv with(nolock) on c.InvCode=inv.InvCode and c.WorkPoint=inv.WorkPoint
//left join ICSVendor e with(nolock) on a.VenCode=e.VenCode and a.WorkPoint=e.WorkPoint
//left join IcsMatCheckResult g with(nolock) on c.InvCode=g.InvCode and c1.BatchCode=g.InvBatcgNo and a.ASNCode=g.AsnCode
//left join (SELECT ASNCode,InvCode,ExtensionID,max(CreateDateTime) CreateDateTime,max( t.DNCode) DNCode,PODetailID
//from ICSDeliveryNotice t with(nolock) GROUP BY ASNCode,InvCode,ExtensionID,PODetailID ) l
//on a.ASNCode=l.ASNCode and c.InvCode=l.InvCode and l.PODetailID=cc.TransCode+'~'+cc.TransSequence
//where 1=1
//<1>
//{0}
//and inv.InvIQC=1
//and b.lotno not in
//( select a.LotNo from ICSASNDetail a
//inner join ICSWareHouseLotInfo b on a.lotno=b.lotno
//left join ICSInspection c on c.LotNo=a.LotNo
//where c.lotno is null
//)
//group by c.InvCode,inv.InvName ,inv.InvStd ,c1.BatchCode,a.ASNCode
//union all
//select
//distinct
//max(b.ID) DetailId --子表主键
//,Max(c.ID) LotNoId --条码ID
//,c.InvCode
//,d.InvName
//,d.InvStd
//,c1.BatchCode as InvBatcgNo --物料批号
//,sum(b.ODNQuantity) DNQuantity --到货数量
//,max(a.VenCode) VenCode --供应商编码
//,max(e.VenName ) VenName --供应商名称
//,a.OASNCode as ASNCode
//,max(b.EATTRIBUTE1) as IsUrgentItem --加急料
//,max(d.InvUnit) InvUnit
//,sum(c.Quantity) Quantity
//,max(c2.FileName) OutReport
//,max(c2.FilePath) FilePath
//--,'' as OutReport
//--,'' as FilePath
//,max(g.id) CheckID
//,case when max(g.CheckNo) is null then '否'else '是' end as IsCheck
//,max(g.Result) Result
//,max(g.FirstCheckTime) CheckDate
//,max(g.WHCode) WHCode
//,max(Isnull(g.FirstCheckUser,g.MUSERName)) FirstCheckUser
//,max(l.CreateDateTime) DHDate
//,max(l.DNCode) DNCode
//,'2' as DocType
//from ICSOASN a with(nolock)
//left join ICSOASNDetail b with(nolock) on a.OASNCode=b.OASNCode
//left join ICSInventoryLot c with(nolock) on b.LotNo=c.LotNo
//left join ICSInspection wl with(nolock) on b.LotNo=wl.LotNo and b.WorkPoint=wl.WorkPoint
//left join ICSExtension c1 with(nolock) on c.ExtensionID=c1.ID
//left join (
// select *
// from
// (
// select t.*
// ,row_number() over(partition by t.ASNCode,t.BatchCode,t.invcode order by t.CerateTime desc) RowSeq
// from ICSASNShippingReport t with(nolock)
// ) t where t.RowSeq=1
//) c2 on a.OASNCode=c2.ASNCode and c1.BatchCode=c2.BatchCode and c.invcode=c2.invcode
//left join ICSInventory d with(nolock) on c.InvCode=d.InvCode
//left join ICSVendor e with(nolock) on a.VenCode=e.VenCode
//left join IcsMatCheckResult g with(nolock) on c.InvCode=g.InvCode and c1.BatchCode=g.InvBatcgNo and a.OASNCode=g.AsnCode
//left join (SELECT OASNCode,InvCode,ExtensionID,max(CreateDateTime) CreateDateTime,max( t.ODNCode) DNCode
//from ICSODeliveryNotice t with(nolock) GROUP BY OASNCode,InvCode,ExtensionID )l on a.OASNCode=l.OASNCode and c.InvCode=l.InvCode and l.ExtensionID=c.ExtensionID
//where 1=1
//and d.InvIQC=1
//and b.lotno not in
//(select a.LotNo from ICSOASNDetail a
//inner join ICSWareHouseLotInfo b on a.lotno=b.lotno
//left join ICSInspection c on c.LotNo=a.LotNo
//where c.lotno is null
//)
//{0}
//<1>
//group by c.InvCode,d.InvName ,d.InvStd ,c1.BatchCode,a.OASNCode
//) a
//where 1=1 AND a.DNQuantity>0
//<2>
//<22>
//<3>
//<4>
//<5>
//<6>
//<7>
//<8>
//<9>
//<10>
//<11>
// ";
// // sql += " WHERE 1=1 and a.MOStatus<>'3' ";
// //sql = string.Format(sql, Figure);
// //sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
// #endregion
// if (!string.IsNullOrWhiteSpace(queryJson))
// {
// //if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToStringExt()))
// //{
// // var invCodes = queryParam["InvCode"].ToStringExt().Split(',');
// // sql = sql.Replace("<1>", " and c.InvCode in ('"+string.Join("','", invCodes) +"')");
// //}
// //else
// //{
// // sql = sql.Replace("<1>", " ");
// //}
// if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToStringExt()))
// {
// if (queryParam["InvCode"].ToStringExt().IndexOf(",") >= 0)
// {
// var invCodes = queryParam["InvCode"].ToStringExt().Split(',');
// sql = sql.Replace("<1>", " and c.InvCode in ('" + string.Join("','", invCodes) + "')");
// }
// else
// {
// sql = sql.Replace("<1>", " and c.InvCode like '%" + queryParam["InvCode"].ToStringExt() + "%'");
// }
// }
// else
// {
// sql = sql.Replace("<1>", " ");
// }
// if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToStringExt()))
// {
// sql = sql.Replace("<2>", " and a.InvName like '%" + queryParam["InvName"].ToStringExt() + "%' ");
// }
// else
// {
// sql = sql.Replace("<2>", " ");
// }
// if (!string.IsNullOrWhiteSpace(queryParam["InvStd"].ToStringExt()))
// {
// sql = sql.Replace("<22>", " and a.InvStd like '%" + queryParam["InvStd"].ToStringExt() + "%' ");
// }
// else
// {
// sql = sql.Replace("<22>", " ");
// }
// if (!string.IsNullOrWhiteSpace(queryParam["StartDate"].ToStringExt()))
// {
// sql = sql.Replace("<3>", " and a.DHDate>='" + queryParam["StartDate"].ToStringExt() + " 00:00:00' ");
// }
// else
// {
// sql = sql.Replace("<3>", " ");
// }
// if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToStringExt()))
// {
// sql = sql.Replace("<4>", " and a.DHDate<='" + queryParam["EndDate"].ToStringExt() + " 23:59:59' ");
// }
// else
// {
// sql = sql.Replace("<4>", " ");
// }
// if (!string.IsNullOrWhiteSpace(queryParam["ASNCode"].ToStringExt()))
// {
// sql = sql.Replace("<5>", " and a.ASNCode like '%" + queryParam["ASNCode"].ToStringExt() + "%' ");
// }
// else
// {
// sql = sql.Replace("<5>", " ");
// }
// if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToStringExt()))
// {
// sql = sql.Replace("<6>", " and a.VenCode like '%" + queryParam["VenCode"].ToStringExt() + "%' ");
// }
// else
// {
// sql = sql.Replace("<6>", " ");
// }
// if (!string.IsNullOrWhiteSpace(queryParam["VenName"].ToStringExt()))
// {
// sql = sql.Replace("<7>", " and a.VenName like '%" + queryParam["VenName"].ToStringExt() + "%' ");
// }
// else
// {
// sql = sql.Replace("<7>", " ");
// }
// if (!string.IsNullOrWhiteSpace(queryParam["InvBatcgNo"].ToStringExt()))
// {
// sql = sql.Replace("<8>", " and a.InvBatcgNo like '%" + queryParam["InvBatcgNo"].ToStringExt() + "%' ");
// }
// else
// {
// sql = sql.Replace("<8>", " ");
// }
// if (!string.IsNullOrWhiteSpace(queryParam["IsCheck"].ToStringExt()))
// {
// sql = sql.Replace("<9>", " and a.IsCheck='" + queryParam["IsCheck"].ToStringExt() + "' ");
// }
// else
// {
// sql = sql.Replace("<9>", " ");
// }
// if (!string.IsNullOrWhiteSpace(queryParam["CheckStartDate"].ToStringExt()))
// {
// sql = sql.Replace("<10>", " and a.CheckDate>='" + queryParam["CheckStartDate"].ToStringExt() + " 00:00:00' ");
// }
// else
// {
// sql = sql.Replace("<10>", " ");
// }
// if (!string.IsNullOrWhiteSpace(queryParam["CheckEndDate"].ToStringExt()))
// {
// sql = sql.Replace("<11>", " and a.CheckDate<='" + queryParam["CheckEndDate"].ToStringExt() + " 23:59:59' ");
// }
// else
// {
// sql = sql.Replace("<11>", " ");
// }
// }
// //if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
// //{
// sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
// // // sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
// //}
// //else
// //{
// // sql = sql.Replace("{0}", "");
// //}
// //if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
// //{
// // sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
// //}
// return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref pagination);
// }
#endregion
public DataTable GetMaterialInStorageCheckPageList(string queryJson, ref Pagination pagination)
{
DataTable dt = new DataTable();
var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
#region [SQL]
string sql = @"
WITH ShippingReport_CTE AS (
SELECT *
FROM (
SELECT t.*,
ROW_NUMBER() OVER(PARTITION BY t.ASNCode, t.BatchCode, t.invcode ORDER BY t.CerateTime DESC) RowSeq
FROM ICSASNShippingReport t WITH(NOLOCK)
) t
WHERE t.RowSeq = 1
),
DeliveryNotice_CTE AS (
SELECT ASNCode, InvCode, ExtensionID, PODetailID,
MAX(CreateDateTime) AS CreateDateTime,
MAX(DNCode) AS DNCode
FROM ICSDeliveryNotice WITH(NOLOCK)
GROUP BY ASNCode, InvCode, ExtensionID, PODetailID
),
ODeliveryNotice_CTE AS (
SELECT OASNCode, InvCode, ExtensionID,OODetailID,
MAX(CreateDateTime) AS CreateDateTime,
MAX(ODNCode) AS DNCode
FROM ICSODeliveryNotice WITH(NOLOCK)
GROUP BY OASNCode, InvCode, ExtensionID,OODetailID
),
RKLotNo_CTE AS (
SELECT a.LotNo
FROM ICSASNDetail a WITH(NOLOCK)
INNER JOIN ICSWareHouseLotInfo b WITH(NOLOCK) ON a.lotno = b.lotno
LEFT JOIN ICSInspection c WITH(NOLOCK) ON c.LotNo = a.LotNo
WHERE c.lotno IS NULL
),
O_RKLotNo_CTE AS (
SELECT a.LotNo
FROM ICSOASNDetail a WITH(NOLOCK)
INNER JOIN ICSWareHouseLotInfo b WITH(NOLOCK) ON a.lotno = b.lotno
LEFT JOIN ICSInspection c WITH(NOLOCK) ON c.LotNo = a.LotNo
WHERE c.lotno IS NULL
),
ASN_Data AS (
SELECT
c.InvCode,
inv.InvName,
inv.InvStd,
c1.BatchCode AS InvBatcgNo,
a.ASNCode,
MAX(b.ID) AS DetailId,
MAX(c.ID) AS LotNoId,
SUM(b.DNQuantity) AS DNQuantity,
MAX(a.VenCode) AS VenCode,
MAX(e.VenName) AS VenName,
MAX(b.EATTRIBUTE1) AS IsUrgentItem,
MAX(inv.InvUnit) AS InvUnit,
SUM(c.Quantity) AS Quantity,
MAX(c2.FileName) AS OutReport,
MAX(c2.FilePath) AS FilePath,
MAX(g.id) AS CheckID,
CASE WHEN MAX(g.CheckNo) IS NULL THEN '否' ELSE '是' END AS IsCheck,
MAX(g.Result) AS Result,
MAX(g.FirstCheckTime) AS CheckDate,
MAX(g.WHCode) AS WHCode,
MAX(ISNULL(g.FirstCheckUser, g.MUSERName)) AS FirstCheckUser,
MAX(l.CreateDateTime) AS DHDate,
MAX(l.DNCode) AS DNCode,
'1' AS DocType
FROM ICSASN a WITH(NOLOCK)
INNER JOIN ICSASNDetail b WITH(NOLOCK) ON a.ASNCode = b.ASNCode AND a.WorkPoint = b.WorkPoint
INNER JOIN ICSInventoryLot c WITH(NOLOCK) ON b.LotNo = c.LotNo AND b.WorkPoint = c.WorkPoint
INNER JOIN ICSInventoryLotDetail cc WITH(NOLOCK) ON b.lotno = cc.lotno AND cc.WorkPoint = c.WorkPoint
INNER JOIN ICSExtension c1 WITH(NOLOCK) ON c.ExtensionID = c1.ID
INNER JOIN ICSInventory inv WITH(NOLOCK) ON c.InvCode = inv.InvCode AND c.WorkPoint = inv.WorkPoint
INNER JOIN ICSVendor e WITH(NOLOCK) ON a.VenCode = e.VenCode AND a.WorkPoint = e.WorkPoint
LEFT JOIN ShippingReport_CTE c2 ON a.ASNCode = c2.ASNCode AND c1.BatchCode = c2.BatchCode AND c.invcode = c2.invcode
LEFT JOIN IcsMatCheckResult g WITH(NOLOCK) ON c.InvCode = g.InvCode AND c1.BatchCode = g.InvBatcgNo AND a.ASNCode = g.AsnCode
LEFT JOIN DeliveryNotice_CTE l ON a.ASNCode = l.ASNCode AND c.InvCode = l.InvCode AND l.PODetailID = cc.TransCode + '~' + cc.TransSequence
LEFT JOIN RKLotNo_CTE rklotno ON b.lotno = rklotno.LotNo
WHERE b.DNQuantity > 0
{0}
AND inv.InvIQC = 1
AND rklotno.LotNo IS NULL
<1>
<2>
<22>
<3>
<4>
<5>
<6>
<7>
<8>
<10>
<11>
<12>
GROUP BY c.InvCode, inv.InvName, inv.InvStd, c1.BatchCode, a.ASNCode
),
OASN_Data AS (
SELECT
c.InvCode,
inv.InvName,
inv.InvStd,
c1.BatchCode AS InvBatcgNo,
a.OASNCode AS ASNCode,
MAX(b.ID) AS DetailId,
MAX(c.ID) AS LotNoId,
SUM(b.ODNQuantity) AS DNQuantity,
MAX(a.VenCode) AS VenCode,
MAX(e.VenName) AS VenName,
MAX(b.EATTRIBUTE1) AS IsUrgentItem,
MAX(inv.InvUnit) AS InvUnit,
SUM(c.Quantity) AS Quantity,
MAX(c2.FileName) AS OutReport,
MAX(c2.FilePath) AS FilePath,
MAX(g.id) AS CheckID,
CASE WHEN MAX(g.CheckNo) IS NULL THEN '否' ELSE '是' END AS IsCheck,
MAX(g.Result) AS Result,
MAX(g.FirstCheckTime) AS CheckDate,
MAX(g.WHCode) AS WHCode,
MAX(ISNULL(g.FirstCheckUser, g.MUSERName)) AS FirstCheckUser,
MAX(l.CreateDateTime) AS DHDate,
MAX(l.DNCode) AS DNCode,
'2' AS DocType
FROM ICSOASN a WITH(NOLOCK)
INNER JOIN ICSOASNDetail b WITH(NOLOCK) ON a.OASNCode = b.OASNCode
INNER JOIN ICSInventoryLot c WITH(NOLOCK) ON b.LotNo = c.LotNo
INNER JOIN ICSInventoryLotDetail cc WITH(NOLOCK) ON b.lotno = cc.lotno AND cc.WorkPoint = c.WorkPoint
INNER JOIN ICSExtension c1 WITH(NOLOCK) ON c.ExtensionID = c1.ID
INNER JOIN ICSInventory inv WITH(NOLOCK) ON c.InvCode = inv.InvCode
INNER JOIN ICSVendor e WITH(NOLOCK) ON a.VenCode = e.VenCode
LEFT JOIN ShippingReport_CTE c2 ON a.OASNCode = c2.ASNCode AND c1.BatchCode = c2.BatchCode AND c.invcode = c2.invcode
LEFT JOIN IcsMatCheckResult g WITH(NOLOCK) ON c.InvCode = g.InvCode AND c1.BatchCode = g.InvBatcgNo AND a.OASNCode = g.AsnCode
LEFT JOIN ODeliveryNotice_CTE l ON a.OASNCode = l.OASNCode AND c.InvCode = l.InvCode AND l.OODetailID = cc.TransCode + '~' + cc.TransSequence--AND l.ExtensionID = c.ExtensionID
LEFT JOIN O_RKLotNo_CTE rklotno ON b.lotno = rklotno.LotNo
WHERE b.ODNQuantity > 0
AND inv.InvIQC = 1
AND rklotno.lotno IS NULL
<12>
{0}
<1>
<2>
<22>
<3>
<4>
<5-1>
<6>
<7>
<8>
<10>
<11>
GROUP BY c.InvCode, inv.InvName, inv.InvStd, c1.BatchCode, a.OASNCode
)
SELECT a.* into #LLJYSQL
FROM (
SELECT * FROM ASN_Data
UNION ALL
SELECT * FROM OASN_Data
) a
WHERE 1=1 <9>
-- AND a.DNQuantity > 0 -- 根据需求决定是否启用
";
#endregion
if (!string.IsNullOrWhiteSpace(queryJson))
{
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToStringExt()))
{
if (queryParam["InvCode"].ToStringExt().IndexOf(",") >= 0)
{
var invCodes = queryParam["InvCode"].ToStringExt().Split(',');
sql = sql.Replace("<1>", " and c.InvCode in ('" + string.Join("','", invCodes) + "')");
}
else
{
sql = sql.Replace("<1>", " and c.InvCode like '%" + queryParam["InvCode"].ToStringExt() + "%'");
}
}
else
{
sql = sql.Replace("<1>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToStringExt()))
{
sql = sql.Replace("<2>", " and inv.InvName like '%" + queryParam["InvName"].ToStringExt() + "%' ");
}
else
{
sql = sql.Replace("<2>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["InvStd"].ToStringExt()))
{
sql = sql.Replace("<22>", " and inv.InvStd like '%" + queryParam["InvStd"].ToStringExt() + "%' ");
}
else
{
sql = sql.Replace("<22>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["StartDate"].ToStringExt()))
{
sql = sql.Replace("<3>", " and l.CreateDateTime>='" + queryParam["StartDate"].ToStringExt() + " 00:00:00' ");
}
else
{
sql = sql.Replace("<3>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToStringExt()))
{
sql = sql.Replace("<4>", " and l.CreateDateTime<='" + queryParam["EndDate"].ToStringExt() + " 23:59:59' ");
}
else
{
sql = sql.Replace("<4>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["ASNCode"].ToStringExt()))
{
sql = sql.Replace("<5>", " and a.ASNCode like '%" + queryParam["ASNCode"].ToStringExt() + "%' ");
sql = sql.Replace("<5-1>", " and a.OASNCode like '%" + queryParam["ASNCode"].ToStringExt() + "%' ");
}
else
{
sql = sql.Replace("<5>", " ");
sql = sql.Replace("<5-1>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToStringExt()))
{
sql = sql.Replace("<6>", " and a.VenCode like '%" + queryParam["VenCode"].ToStringExt() + "%' ");
}
else
{
sql = sql.Replace("<6>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["VenName"].ToStringExt()))
{
sql = sql.Replace("<7>", " and e.VenName like '%" + queryParam["VenName"].ToStringExt() + "%' ");
}
else
{
sql = sql.Replace("<7>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["InvBatcgNo"].ToStringExt()))
{
sql = sql.Replace("<8>", " and c1.BatchCode like '%" + queryParam["InvBatcgNo"].ToStringExt() + "%' ");
}
else
{
sql = sql.Replace("<8>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["IsCheck"].ToStringExt()))
{
sql = sql.Replace("<9>", " and a.IsCheck='" + queryParam["IsCheck"].ToStringExt() + "' ");
if (queryParam["IsCheck"].ToStringExt() == "否")
{
sql = sql.Replace("<12>", " and g.CheckNo is null and a.MTIME >= '2025-01-01 00:00:00' ");
}
else if (queryParam["IsCheck"].ToStringExt() == "是")
{
sql = sql.Replace("<12>", " and g.CheckNo is not null ");
}
}
else
{
sql = sql.Replace("<9>", " ");
sql = sql.Replace("<12>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["CheckStartDate"].ToStringExt()))
{
sql = sql.Replace("<10>", " and g.FirstCheckTime>='" + queryParam["CheckStartDate"].ToStringExt() + " 00:00:00' ");
}
else
{
sql = sql.Replace("<10>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["CheckEndDate"].ToStringExt()))
{
sql = sql.Replace("<11>", " and g.FirstCheckTime<='" + queryParam["CheckEndDate"].ToStringExt() + " 23:59:59' ");
}
else
{
sql = sql.Replace("<11>", " ");
}
}
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
return Repository().FindTablePageBySql_OtherTemp(sql.ToString(), "#LLJYSQL"
, @" drop table #LLJYSQL ", "connstr", parameter.ToArray(), ref pagination);
}
// public DataTable GetMaterialInStorageCheckPageList(string queryJson, ref Pagination pagination)
// {
// DataTable dt = new DataTable();
// var queryParam = queryJson.ToJObject();
// List<DbParameter> parameter = new List<DbParameter>();
// //object Figure = GetDecimalDigits();
// #region [SQL]
// string sql = @" if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#TempLLData_1'))
// drop table #TempLLData_1
// if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#TempLLData_1_1'))
// drop table #TempLLData_1_1
// if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tempLot2'))
// drop table #tempLot2
// if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tempLot'))
// drop table #tempLot
// if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tempDn'))
// drop table #tempDn
// if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tempDn2'))
// drop table #tempDn2
//select a.LotNo into #tempLot from ICSASNDetail a
//inner join ICSWareHouseLotInfo b on a.lotno=b.lotno
//left join ICSInspection c on c.LotNo=a.LotNo
//where c.lotno is null
//select a.LotNo into #tempLot2 from ICSOASNDetail a
//inner join ICSWareHouseLotInfo b on a.lotno=b.lotno
//left join ICSInspection c on c.LotNo=a.LotNo
//where c.lotno is null
//SELECT ASNCode,InvCode,ExtensionID,max(CreateDateTime) CreateDateTime,max( t.DNCode) DNCode,PODetailID
// into #tempDn from ICSDeliveryNotice t with(nolock) GROUP BY ASNCode,InvCode,ExtensionID,PODetailID
// SELECT OASNCode,InvCode,ExtensionID,max(CreateDateTime) CreateDateTime,max( t.ODNCode) DNCode
// into #tempDn2 from ICSODeliveryNotice t with(nolock) GROUP BY OASNCode,InvCode,ExtensionID
//SELECT * into #TempLLData_1 FROM (
//select
//distinct
//max(b.ID) DetailId --子表主键
//,inv.InvCode
//,inv.InvName
//,b.[EATTRIBUTE2] as InvBatcgNo --物料批号
//,sum(b.DNQuantity) DNQuantity --到货数量
//,max(a.VenCode) VenCode --供应商编码
//,max(e.VenName ) VenName --供应商名称
//,a.ASNCode
//,max(b.EATTRIBUTE1) as IsUrgentItem --加急料
//,max(inv.InvUnit) InvUnit
//,sum(b.DNQuantity) Quantity
//,max(c2.FileName) OutReport
//,max(c2.FilePath) FilePath
//,max(g.id) CheckID
//,case when max(g.CheckNo) is null then '否'else '是' end as IsCheck
//,max(g.Result) Result
//,max(g.FirstCheckTime) CheckDate
//,max(g.WHCode) WHCode
//,max(Isnull(g.FirstCheckUser,g.MUSERName)) FirstCheckUser
//,max(l.CreateDateTime) DHDate
//,max(l.DNCode) DNCode
//,'1' as DocType
//from ICSASN a with(nolock)
//left join ICSASNDetail b with(nolock) on a.ASNCode=b.ASNCode and a.WorkPoint=b.WorkPoint
//left join (
// select *
// from
// (
// select t.*
// ,row_number() over(partition by t.ASNCode,t.BatchCode,t.invcode order by t.CerateTime desc) RowSeq
// from ICSASNShippingReport t with(nolock)
// ) t where t.RowSeq=1
//) c2 on a.ASNCode=c2.ASNCode and b.[EATTRIBUTE2] =c2.BatchCode and b.[EATTRIBUTE3]=c2.invcode
//left join ICSInventory inv with(nolock) on b.[EATTRIBUTE3]=inv.InvCode and b.WorkPoint=inv.WorkPoint
//left join ICSVendor e with(nolock) on a.VenCode=e.VenCode and a.WorkPoint=e.WorkPoint
//left join IcsMatCheckResult g with(nolock) on b.[EATTRIBUTE3]=g.InvCode and b.[EATTRIBUTE2]=g.InvBatcgNo and a.ASNCode=g.AsnCode
//left join #tempDn l
//on a.ASNCode=l.ASNCode and b.[EATTRIBUTE3]=l.InvCode --and l.PODetailID=cc.TransCode+'~'+cc.TransSequence
//left join #tempLot k on k.Lotno = b.LotNo
//where 1=1
//and inv.InvIQC=1
//and k.lotno is null
//{0}
//--and b.lotno not in ( select lotno from #tempLot )
//group by inv.InvCode,inv.InvName ,b.[EATTRIBUTE2],a.ASNCode
//union all
//select
//distinct
//max(b.ID) DetailId --子表主键
//,d.InvCode
//,d.InvName
//,b.[EATTRIBUTE2] as InvBatcgNo --物料批号
//,sum(b.ODNQuantity) DNQuantity --到货数量
//,max(a.VenCode) VenCode --供应商编码
//,max(e.VenName ) VenName --供应商名称
//,a.OASNCode as ASNCode
//,max(b.EATTRIBUTE1) as IsUrgentItem --加急料
//,max(d.InvUnit) InvUnit
//,sum(b.ODNQuantity) Quantity
//,max(c2.FileName) OutReport
//,max(c2.FilePath) FilePath
//--,'' as OutReport
//--,'' as FilePath
//,max(g.id) CheckID
//,case when max(g.CheckNo) is null then '否'else '是' end as IsCheck
//,max(g.Result) Result
//,max(g.FirstCheckTime) CheckDate
//,max(g.WHCode) WHCode
//,max(Isnull(g.FirstCheckUser,g.MUSERName)) FirstCheckUser
//,max(l.CreateDateTime) DHDate
//,max(l.DNCode) DNCode
//,'2' as DocType
//from ICSOASN a with(nolock)
//left join ICSOASNDetail b with(nolock) on a.OASNCode=b.OASNCode
//left join (
// select *
// from
// (
// select t.*
// ,row_number() over(partition by t.ASNCode,t.BatchCode,t.invcode order by t.CerateTime desc) RowSeq
// from ICSASNShippingReport t with(nolock)
// ) t where t.RowSeq=1
//) c2 on a.OASNCode=c2.ASNCode and b.[EATTRIBUTE2]=c2.BatchCode and b.[EATTRIBUTE3]=c2.invcode
//left join ICSInventory d with(nolock) on b.[EATTRIBUTE3]=d.InvCode
//left join ICSVendor e with(nolock) on a.VenCode=e.VenCode
//left join IcsMatCheckResult g with(nolock) on b.[EATTRIBUTE3]=g.InvCode and b.[EATTRIBUTE2]=g.InvBatcgNo and a.OASNCode=g.AsnCode
//left join #tempDn2 l on a.OASNCode=l.OASNCode and d.InvCode=l.InvCode
//left join #tempLot2 k on k.Lotno = b.LotNo
//where 1=1
//and d.InvIQC=1
//and k.lotno is null
//{0}
//group by d.InvCode,d.InvName ,b.[EATTRIBUTE2],a.OASNCode) a
// WHERE DNQuantity > 0
//<1>
//<2>
//<3>
//<4>
//<5>
//<6>
//<7>
//<8>
//<9>
//<10>
//<11>
//select * into #TempLLData_1_1 from #TempLLData_1
// ";
// // sql += " WHERE 1=1 and a.MOStatus<>'3' ";
// //sql = string.Format(sql, Figure);
// //sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
// #endregion
// if (!string.IsNullOrWhiteSpace(queryJson))
// {
// if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
// {
// sql = sql.Replace("<1>", " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ");
// }
// else
// {
// sql = sql.Replace("<1>", " ");
// }
// if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToString()))
// {
// sql = sql.Replace("<2>", " and a.InvName like '%" + queryParam["InvName"].ToString() + "%' ");
// }
// else
// {
// sql = sql.Replace("<2>", " ");
// }
// if (!string.IsNullOrWhiteSpace(queryParam["StartDate"].ToString()))
// {
// sql = sql.Replace("<3>", " and a.DHDate>='" + queryParam["StartDate"].ToString() + " 00:00:00' ");
// }
// else
// {
// sql = sql.Replace("<3>", " ");
// }
// if (!string.IsNullOrWhiteSpace(queryParam["EndDate"].ToString()))
// {
// sql = sql.Replace("<4>", " and a.DHDate<='" + queryParam["EndDate"].ToString() + " 23:59:59' ");
// }
// else
// {
// sql = sql.Replace("<4>", " ");
// }
// if (!string.IsNullOrWhiteSpace(queryParam["ASNCode"].ToString()))
// {
// sql = sql.Replace("<5>", " and a.ASNCode like '%" + queryParam["ASNCode"].ToString() + "%' ");
// }
// else
// {
// sql = sql.Replace("<5>", " ");
// }
// if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString()))
// {
// sql = sql.Replace("<6>", " and a.VenCode like '%" + queryParam["VenCode"].ToString() + "%' ");
// }
// else
// {
// sql = sql.Replace("<6>", " ");
// }
// if (!string.IsNullOrWhiteSpace(queryParam["VenName"].ToString()))
// {
// sql = sql.Replace("<7>", " and a.VenName like '%" + queryParam["VenName"].ToString() + "%' ");
// }
// else
// {
// sql = sql.Replace("<7>", " ");
// }
// if (!string.IsNullOrWhiteSpace(queryParam["InvBatcgNo"].ToString()))
// {
// sql = sql.Replace("<8>", " and a.InvBatcgNo like '%" + queryParam["InvBatcgNo"].ToString() + "%' ");
// }
// else
// {
// sql = sql.Replace("<8>", " ");
// }
// if (!string.IsNullOrWhiteSpace(queryParam["IsCheck"].ToString()))
// {
// sql = sql.Replace("<9>", " and a.IsCheck='" + queryParam["IsCheck"].ToString() + "' ");
// }
// else
// {
// sql = sql.Replace("<9>", " ");
// }
// if (!string.IsNullOrWhiteSpace(queryParam["CheckStartDate"].ToString()))
// {
// sql = sql.Replace("<10>", " and a.CheckDate>='" + queryParam["CheckStartDate"].ToString() + " 00:00:00' ");
// }
// else
// {
// sql = sql.Replace("<10>", " ");
// }
// if (!string.IsNullOrWhiteSpace(queryParam["CheckEndDate"].ToString()))
// {
// sql = sql.Replace("<11>", " and a.CheckDate<='" + queryParam["CheckEndDate"].ToString() + " 23:59:59' ");
// }
// else
// {
// sql = sql.Replace("<11>", " ");
// }
// }
// //if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
// //{
// sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
// // // sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
// //}
// //else
// //{
// // sql = sql.Replace("{0}", "");
// //}
// //if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
// //{
// // sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
// //}
// //return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref pagination);
// return Repository().FindTablePageBySql_OtherTemp(sql, "#TempLLData_1_1",
// @" if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#TempLLData_1'))
// drop table #TempLLData_1
// if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#TempLLData_1_1'))
// drop table #TempLLData_1_1
// if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tempLot2'))
// drop table #tempLot2
// if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tempLot'))
// drop table #tempLot
// if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tempDn'))
// drop table #tempDn
// if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tempDn2'))
// drop table #tempDn2 ",
// "connstr", parameter.ToArray(), ref pagination);
// }
public string DeleteItem2CheckItem(string keyValue)
{
string returnValue = string.Empty;
try
{
var idList = JsonConvert.DeserializeObject<List<string>>(keyValue);
string sql = @"select
a.*
from ICSInventoryInspectionList a with(nolock)
where a.ID in ('" + string.Join("','", idList) + "')";
var entityList = MsSqlData.Query<ICSInventoryInspectionListEntity>(sql).ToList();
foreach (var item in entityList)
{
var delSql = @" delete from ICSInventoryInspectionGroup where id='" + item.InvGroupID + "' " +
" delete from ICSInventoryInspectionList where InvGroupID='" + item.InvGroupID + "' ";
var result = MsSqlData.Execute(delSql);
}
//var result = MsSqlData.Delete<ICSInventoryInspectionGroupEntity>(entityList);
//if (!result)
//{
// returnValue = "删除失败";
//}
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
public string SaveItem2CheckItem(string keyValue)
{
string returnValue = string.Empty;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var connString = MsSqlData.FromMd5(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString);
try
{
var dtNow = DateTime.Now;
var model = JsonConvert.DeserializeObject<ICSInventoryInspectionGroupDto>(keyValue);
if (model == null)
{
throw new Exception("传入参数错误");
}
using (var connection = new SqlConnection())
{
connection.ConnectionString = connString;
connection.Open();
using (var trans = connection.BeginTransaction())
{
try
{
if (model.CheckItemList.GroupBy(x => new { x.ListCode, x.InvCode }).Count() != model.CheckItemList.Count)
{
throw new Exception("当前物料检验项目代码重复");
}
foreach (var item in model.CheckItemList)
{
string dtPre = DateTime.Now.ToString("yyyyMMdd");
string sql = @"EXEC Addins_GetSerialCode '" + WorkPoint + "','ICSInventoryInspectionGroup','GroupCode','" + "IGC" + dtPre + "',3";
var serialNo = MsSqlData.ExecuteScalar(sql).ToStringExt();
var entity = new ICSInventoryInspectionGroupEntity();
entity.InvCode = item.InvCode;
entity.GroupCode = serialNo;
entity.GroupName = serialNo;
entity.Enable = true;
entity.EATTRIBUTE1 = item.CheckAttr;
entity.ID = Guid.NewGuid().ToString();
entity.MUSER = MUSER;
entity.MUSERName = MUSERNAME;
entity.MTIME = dtNow;
entity.WorkPoint = WorkPoint;
connection.Insert<ICSInventoryInspectionGroupEntity>(entity, trans);
var detail = new ICSInventoryInspectionListEntity();
detail = ConvertExt.Mapping<ICSInventoryInspectionListDto, ICSInventoryInspectionListEntity>(item);
detail.InvGroupID = entity.ID;
detail.ID = Guid.NewGuid().ToString();
detail.MUSER = MUSER;
detail.MUSERName = MUSERNAME;
detail.MTIME = dtNow;
detail.WorkPoint = WorkPoint;
var sql4Count = @" select
count(1)
from
(
select
a.ID
, c.InvCode
, c.InvName
, a.ListCode
, a.ListName
, b.EATTRIBUTE1 as CheckAttr --检验属性
from ICSInventoryInspectionList a with(nolock)
left join ICSInventoryInspectionGroup b with(nolock) on a.InvGroupID = b.ID
left join ICSInventory c with(nolock) on b.InvCode = c.InvCode
) a
where 1 = 1
and a.InvCode = @InvCode and a.ListCode=@ListCode ";
var para2 = new { InvCode = item.InvCode, ListCode = item.ListCode };
var count = connection.ExecuteScalar<int>(sql4Count, para2, trans);
if (count > 0)
{
throw new Exception("当前物料检验项目关系已经存在");
}
connection.Insert<ICSInventoryInspectionListEntity>(detail, trans);
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
}
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
public DataTable ExportAllItem2CheckItem(string invCode, string invName, string ListCode, string ListName)
{
DataTable dt = new DataTable();
// var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
#region [SQL]
string sql = @"
select
a.*
from
(
select
a.ID
,c.InvCode
,c.InvName
,a.ListCode
,a.ListName
,b.EATTRIBUTE1 as CheckAttr --检验属性
,( select t.label from V_QuerySysEnumItem t where t.value=b.EATTRIBUTE1 and t.F_EnCode='CheckAttr'
) as CheckAttrText
,a.SetValueMax
,a.SetValueMin
,a.Unit
,a.Enable
,a.MUSER
,a.MUSERName
,a.MTIME
from ICSInventoryInspectionList a with(nolock)
left join ICSInventoryInspectionGroup b with(nolock) on a.InvGroupID=b.ID
left join ICSInventory c with(nolock) on b.InvCode=c.InvCode
where 1=1
{0}
) a
where 1=1
<1>
<2>
<3>
<4> ";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
//sql = string.Format(sql, Figure);
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
if (!string.IsNullOrWhiteSpace(invCode))
{
sql = sql.Replace("<1>", " and a.InvCode like '%" + invCode + "%' ");
}
else
{
sql = sql.Replace("<1>", " ");
}
if (!string.IsNullOrWhiteSpace(invName))
{
sql = sql.Replace("<2>", " and a.InvName like '%" + invName + "%' ");
}
else
{
sql = sql.Replace("<2>", " ");
}
if (!string.IsNullOrWhiteSpace(ListCode))
{
sql = sql.Replace("<3>", " and a.ListCode =" + ListCode + " ");
}
else
{
sql = sql.Replace("<3>", " ");
}
if (!string.IsNullOrWhiteSpace(ListName))
{
sql = sql.Replace("<4>", " and a.ListName =" + ListName + " ");
}
else
{
sql = sql.Replace("<4>", " ");
}
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
//{
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
// // sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
//}
//else
//{
// sql = sql.Replace("{0}", "");
//}
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
//{
// sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
//}
return Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
}
public DataTable GetItem2CheckItemPageList(string queryJson, ref Pagination pagination)
{
DataTable dt = new DataTable();
var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
#region [SQL]
string sql = @"
select
a.*
from
(
select
a.ID
,c.InvCode
,c.InvName
,a.ListCode
,a.ListName
,b.EATTRIBUTE1 as CheckAttr --检验属性
,( select t.label from V_QuerySysEnumItem t where t.value=b.EATTRIBUTE1 and t.F_EnCode='CheckAttr'
) as CheckAttrText
,a.SetValueMax
,a.SetValueMin
,a.Unit
,a.Enable
,a.MUSER
,a.MUSERName
,a.MTIME
from ICSInventoryInspectionList a with(nolock)
left join ICSInventoryInspectionGroup b with(nolock) on a.InvGroupID=b.ID
left join ICSInventory c with(nolock) on b.InvCode=c.InvCode
where 1=1
{0}
) a
where 1=1
<1>
<2>
<3>
<4>
";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
//sql = string.Format(sql, Figure);
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
if (!string.IsNullOrWhiteSpace(queryJson))
{
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
{
sql = sql.Replace("<1>", " and a.InvCode = '" + queryParam["InvCode"].ToString() + "' ");
}
else
{
sql = sql.Replace("<1>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToString()))
{
sql = sql.Replace("<2>", " and a.InvName like '%" + queryParam["InvName"].ToString() + "%' ");
}
else
{
sql = sql.Replace("<2>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["ListCode"].ToString()))
{
sql = sql.Replace("<3>", " and a.ListCode =" + queryParam["ListCode"].ToString() + " ");
}
else
{
sql = sql.Replace("<3>", " ");
}
if (!string.IsNullOrWhiteSpace(queryParam["ListName"].ToString()))
{
sql = sql.Replace("<4>", " and a.ListName =" + queryParam["ListName"].ToString() + " ");
}
else
{
sql = sql.Replace("<4>", " ");
}
}
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
//{
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
// // sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
//}
//else
//{
// sql = sql.Replace("{0}", "");
//}
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
//{
// sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
//}
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref pagination);
}
public string DeleteCheckItemGroup(string keyValue)
{
string returnValue = string.Empty;
try
{
var idList = JsonConvert.DeserializeObject<List<string>>(keyValue);
string sql = @"select
a.*
from ICSInspectionGroup a with(nolock)
where a.ID in ('" + string.Join("','", idList) + "')";
var entityList = MsSqlData.Query<ICSInspectionGroupEntity>(sql).ToList();
foreach (var item in entityList)
{
//var count = MsSqlData.ExecuteScalar(" select count(1) from IcsCheckingFixtureTransfer with(nolock) where CheckFixtureNo='" + item.CheckFixtureNo + "'").ToInt();
//if (count > 0)
//{
// throw new Exception("当前检具已经存在履历,不能删除");
//}
var count = MsSqlData.ExecuteScalar
(
" select count(1) from ICSInventoryInspectionGroup with(nolock) where GroupCode='" + item.GroupCode + "' "
).ToInt();
if (count > 0)
{
throw new Exception("当前物料检验类型关系已经存在,不能删除");
}
}
var result = MsSqlData.Delete<ICSInspectionGroupEntity>(entityList);
if (!result)
{
returnValue = "删除失败";
}
string sql2 = @"delete from ICSInspectionList where GroupID in ('" + string.Join("','", idList) + "')";
var line = Repository().ExecuteBySql(new StringBuilder(sql2));
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
public string SaveCheckItemGroup(string keyValue)
{
string returnValue = string.Empty;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
try
{
var dtNow = DateTime.Now;
var model = JsonConvert.DeserializeObject<ICSInspectionGroupDto>(keyValue);
if (model == null)
{
throw new Exception("传入参数错误");
}
if (model.DetailList != null && model.DetailList.Count > 0)
{
if (model.DetailList.Select(x => x.ListCode).Distinct().Count() != model.DetailList.Count)
{
throw new Exception("检验项目代码重复");
}
}
if (model.ID.IsNullOrEmpty())
{
//新增
var count = MsSqlData.ExecuteScalar(" select count(1) from ICSInspectionGroup with(nolock) where GroupCode='" + model.GroupCode + "'").ToInt();
if (count > 0)
{
throw new Exception("当前检验类型已经存在");
}
model.ID = Guid.NewGuid().ToString();
model.MUSER = MUSER;
model.MUSERName = MUSERNAME;
model.MTIME = dtNow;
model.WorkPoint = WorkPoint;
var result = MsSqlData.Insert<ICSInspectionGroupEntity>(model);
if (!result)
{
returnValue = "新增失败";
}
foreach (var item in model.DetailList)
{
var detailEntity = ConvertExt.Mapping<ICSInspectionListDto, ICSInspectionListEntity>(item);
detailEntity.ID = Guid.NewGuid().ToString();
detailEntity.GroupID = model.ID;
detailEntity.MUSER = MUSER;
detailEntity.MUSERName = MUSERNAME;
detailEntity.MTIME = dtNow;
detailEntity.WorkPoint = WorkPoint;
var result2 = MsSqlData.Insert<ICSInspectionListEntity>(detailEntity);
if (!result2)
{
returnValue = "新增失败";
}
}
}
else
{
//修改
var entity = MsSqlData.Get<ICSInspectionGroupEntity>(model.ID);
if (entity == null)
{
throw new Exception("当前数据不存在");
}
ConvertExt.Mapping<ICSInspectionGroupDto, ICSInspectionGroupEntity>(model, entity);
entity.MUSER = MUSER;
entity.MUSERName = MUSERNAME;
entity.MTIME = dtNow;
var result = MsSqlData.Update<ICSInspectionGroupEntity>(entity);
if (!result)
{
returnValue = "修改失败";
}
string sql = @"select
a.*
from ICSInspectionList a with(nolock)
where a.GroupID ='" + entity.ID + "' ";
var entityList = MsSqlData.Query<ICSInspectionListEntity>(sql).ToList();
MsSqlData.Delete<ICSInspectionListEntity>(entityList);
foreach (var item in model.DetailList)
{
var detailEntity = ConvertExt.Mapping<ICSInspectionListDto, ICSInspectionListEntity>(item);
detailEntity.ID = Guid.NewGuid().ToString();
detailEntity.GroupID = model.ID;
detailEntity.MUSER = MUSER;
detailEntity.MUSERName = MUSERNAME;
detailEntity.MTIME = dtNow;
detailEntity.WorkPoint = WorkPoint;
var result2 = MsSqlData.Insert<ICSInspectionListEntity>(detailEntity);
if (!result2)
{
returnValue = "修改失败";
}
}
}
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
public ICSInspectionGroupDto GetCheckItemGroup(string keyValue)
{
var returnValue = new ICSInspectionGroupDto();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
try
{
var entity = MsSqlData.Get<ICSInspectionGroupEntity>(keyValue);
if (entity == null)
{
throw new Exception("获取单个检验项目组失败");
}
returnValue = ConvertExt.Mapping<ICSInspectionGroupEntity, ICSInspectionGroupDto>(entity);
var para = new { WorkPoint = WorkPoint };
string sql = @"select
a.*
from ICSInspectionList a with(nolock)
where a.GroupID ='" + entity.ID + "' and a.WorkPoint=@WorkPoint " +
"order by a.ListCode asc ";
var entityList = MsSqlData.Query<ICSInspectionListDto>(sql, para).ToList();
//int i = 0;
for (int i = 0; i < entityList.Count; i++)
{
entityList[i].key = i + 1;
}
returnValue.DetailList = entityList;
}
catch (Exception ex)
{
returnValue = null;
}
return returnValue;
}
public DataTable ExportAllBadReasonGroup(string bRGCode, string bRGDesc)
{
DataTable dt = new DataTable();
// var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
#region [SQL]
string sql = @"
select
a.*
from ICSBadReasonGroup a with(nolock)
where 1=1
{0}
<1>
<2>
";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
//sql = string.Format(sql, Figure);
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
if (!string.IsNullOrWhiteSpace(bRGCode))
{
sql = sql.Replace("<1>", " and a.bRGCode like '%" + bRGCode + "%' ");
}
else
{
sql = sql.Replace("<1>", " ");
}
if (!string.IsNullOrWhiteSpace(bRGDesc))
{
sql = sql.Replace("<2>", " and a.bRGDesc like '%" + bRGDesc + "%' ");
}
else
{
sql = sql.Replace("<2>", " ");
}
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
//{
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
// // sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
//}
//else
//{
// sql = sql.Replace("{0}", "");
//}
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
//{
// sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
//}
return Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
}
public string DeleteBadReasonGroup(string keyValue)
{
string returnValue = string.Empty;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
try
{
var idList = JsonConvert.DeserializeObject<List<string>>(keyValue);
string sql = @"select
a.*
from ICSBadReasonGroup a with(nolock)
where a.ID in ('" + string.Join("','", idList) + "') and a.WorkPoint=@WorkPoint ";
var entityList = MsSqlData.Query<ICSBadReasonGroupEntity>(sql, para).ToList();
foreach (var item in entityList)
{
//var count = MsSqlData.ExecuteScalar(" select count(1) from IcsCheckingFixtureTransfer with(nolock) where CheckFixtureNo='" + item.CheckFixtureNo + "'").ToInt();
//if (count > 0)
//{
// throw new Exception("当前检具已经存在履历,不能删除");
//}
}
var result = MsSqlData.Delete<ICSBadReasonGroupEntity>(entityList);
if (!result)
{
returnValue = "删除失败";
}
string sql2 = @"delete from ICSBadReason where BRGroupID in ('" + string.Join("','", idList) + "') and WorkPoint=@WorkPoint";
var line = MsSqlData.Execute(sql2, para);
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
public string SaveBadReasonGroup(string keyValue)
{
string returnValue = string.Empty;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
try
{
var dtNow = DateTime.Now;
var model = JsonConvert.DeserializeObject<ICSBadReasonGroupDto>(keyValue);
if (model == null)
{
throw new Exception("传入参数错误");
}
if (model.ID.IsNullOrEmpty())
{
//新增
var sql1 = @" select count(1) from ICSBadReasonGroup a with(nolock) where BRGCode='" + model.BRGCode + "' and a.WorkPoint=@WorkPoint";
var count = MsSqlData.ExecuteScalar(sql1, para).ToInt();
if (count > 0)
{
throw new Exception("当前不良状态已经存在");
}
model.ID = Guid.NewGuid().ToString();
model.MUSER = MUSER;
model.MUSERName = MUSERNAME;
model.MTIME = dtNow;
model.WorkPoint = WorkPoint;
var result = MsSqlData.Insert<ICSBadReasonGroupEntity>(model);
if (!result)
{
returnValue = "新增失败";
}
foreach (var item in model.DetailList)
{
var detailEntity = ConvertExt.Mapping<ICSBadReasonDto, ICSBadReasonEntity>(item);
detailEntity.ID = Guid.NewGuid().ToString();
detailEntity.BRGroupID = model.ID;
detailEntity.MUSER = MUSER;
detailEntity.MUSERName = MUSERNAME;
detailEntity.MTIME = dtNow;
detailEntity.WorkPoint = WorkPoint;
var result2 = MsSqlData.Insert<ICSBadReasonEntity>(detailEntity);
if (!result2)
{
returnValue = "新增失败";
}
}
}
else
{
//修改
var entity = MsSqlData.Get<ICSBadReasonGroupEntity>(model.ID);
if (entity == null)
{
throw new Exception("当前数据不存在");
}
ConvertExt.Mapping<ICSBadReasonGroupDto, ICSBadReasonGroupEntity>(model, entity);
entity.MUSER = MUSER;
entity.MUSERName = MUSERNAME;
entity.MTIME = dtNow;
var result = MsSqlData.Update<ICSBadReasonGroupEntity>(entity);
if (!result)
{
returnValue = "修改失败";
}
string sql = @"select
a.*
from ICSBadReason a with(nolock)
where a.BRGroupID ='" + entity.ID + "' and a.WorkPoint=@WorkPoint ";
var entityList = MsSqlData.Query<ICSBadReasonEntity>(sql, para).ToList();
MsSqlData.Delete<ICSBadReasonEntity>(entityList);
foreach (var item in model.DetailList)
{
var detailEntity = ConvertExt.Mapping<ICSBadReasonDto, ICSBadReasonEntity>(item);
detailEntity.ID = Guid.NewGuid().ToString();
detailEntity.BRGroupID = model.ID;
detailEntity.MUSER = MUSER;
detailEntity.MUSERName = MUSERNAME;
detailEntity.MTIME = dtNow;
detailEntity.WorkPoint = WorkPoint;
var result2 = MsSqlData.Insert<ICSBadReasonEntity>(detailEntity);
if (!result2)
{
returnValue = "修改失败";
}
}
}
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
public ICSBadReasonGroupDto GetBadReasonGroup(string keyValue)
{
var returnValue = new ICSBadReasonGroupDto();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
try
{
var entity = MsSqlData.Get<ICSBadReasonGroupEntity>(keyValue);
if (entity == null)
{
throw new Exception("获取单个规则失败");
}
returnValue = ConvertExt.Mapping<ICSBadReasonGroupEntity, ICSBadReasonGroupDto>(entity);
string sql = @"select
a.*
from ICSBadReason a with(nolock)
where a.BRGroupID ='" + entity.ID + "' and a.WorkPoint=@WorkPoint order by a.BadReasonCode asc ";
var entityList = MsSqlData.Query<ICSBadReasonDto>(sql, para).ToList();
//int i = 0;
for (int i = 0; i < entityList.Count; i++)
{
entityList[i].key = i + 1;
}
returnValue.DetailList = entityList;
}
catch (Exception ex)
{
returnValue = null;
}
return returnValue;
}
public DataTable GetBadReasonDetailList(string id, ref Pagination pagination)
{
DataTable dt = new DataTable();
// var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
#region [SQL]
string sql = @"select
a.*
from ICSBadReason a with(nolock)
where 1=1
{0}
<1>
order by a.BadReasonCode asc
";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
sql = sql.Replace("<1>", "and a.BRGroupID='" + id + "'");
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
return Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
}
public DataTable ExportAllBadCodeGroup(string bCGCode, string bCGDesc)
{
DataTable dt = new DataTable();
// var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
#region [SQL]
string sql = @"
select
a.*
from ICSBadCodeGroup a with(nolock)
where 1=1
{0}
<1>
<2>
";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
//sql = string.Format(sql, Figure);
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
if (!string.IsNullOrWhiteSpace(bCGCode))
{
sql = sql.Replace("<1>", " and a.bCGCode like '%" + bCGCode + "%' ");
}
else
{
sql = sql.Replace("<1>", " ");
}
if (!string.IsNullOrWhiteSpace(bCGDesc))
{
sql = sql.Replace("<2>", " and a.bCGDesc like '%" + bCGDesc + "%' ");
}
else
{
sql = sql.Replace("<2>", " ");
}
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
//{
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
// // sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
//}
//else
//{
// sql = sql.Replace("{0}", "");
//}
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
//{
// sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
//}
return Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
}
public string DeleteBadCodeGroup(string keyValue)
{
string returnValue = string.Empty;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
try
{
var idList = JsonConvert.DeserializeObject<List<string>>(keyValue);
string sql = @"select
a.*
from ICSBadCodeGroup a with(nolock)
where a.ID in ('" + string.Join("','", idList) + "') and a.WorkPoint=@WorkPoint ";
var entityList = MsSqlData.Query<ICSBadCodeGroupEntity>(sql, para).ToList();
foreach (var item in entityList)
{
//var count = MsSqlData.ExecuteScalar(" select count(1) from IcsCheckingFixtureTransfer with(nolock) where CheckFixtureNo='" + item.CheckFixtureNo + "'").ToInt();
//if (count > 0)
//{
// throw new Exception("当前检具已经存在履历,不能删除");
//}
}
var result = MsSqlData.Delete<ICSBadCodeGroupEntity>(entityList);
if (!result)
{
returnValue = "删除失败";
}
string sql2 = @"delete from ICSBadCode where BCGroupID in ('" + string.Join("','", idList) + "') and WorkPoint=@WorkPoint";
var line = MsSqlData.Execute(sql2, para);
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
public string SaveBadCodeGroup(string keyValue)
{
string returnValue = string.Empty;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
try
{
var dtNow = DateTime.Now;
var model = JsonConvert.DeserializeObject<ICSBadCodeGroupDto>(keyValue);
if (model == null)
{
throw new Exception("传入参数错误");
}
if (model.ID.IsNullOrEmpty())
{
//新增
var sql1 = @" select count(1) from ICSBadCodeGroup with(nolock) where BCGCode='" + model.BCGCode + "' and WorkPoint=@WorkPoint";
var count = MsSqlData.ExecuteScalar(sql1, para).ToInt();
if (count > 0)
{
throw new Exception("当前不良状态已经存在");
}
model.ID = Guid.NewGuid().ToString();
model.MUSER = MUSER;
model.MUSERName = MUSERNAME;
model.MTIME = dtNow;
model.WorkPoint = WorkPoint;
var result = MsSqlData.Insert<ICSBadCodeGroupEntity>(model);
if (!result)
{
returnValue = "新增失败";
}
foreach (var item in model.DetailList)
{
var detailEntity = ConvertExt.Mapping<ICSBadCodeDto, ICSBadCodeEntity>(item);
detailEntity.ID = Guid.NewGuid().ToString();
detailEntity.BCGroupID = model.ID;
detailEntity.MUSER = MUSER;
detailEntity.MUSERName = MUSERNAME;
detailEntity.MTIME = dtNow;
detailEntity.WorkPoint = WorkPoint;
var result2 = MsSqlData.Insert<ICSBadCodeEntity>(detailEntity);
if (!result2)
{
returnValue = "新增失败";
}
}
}
else
{
//修改
var entity = MsSqlData.Get<ICSBadCodeGroupEntity>(model.ID);
if (entity == null)
{
throw new Exception("当前数据不存在");
}
ConvertExt.Mapping<ICSBadCodeGroupDto, ICSBadCodeGroupEntity>(model, entity);
entity.MUSER = MUSER;
entity.MUSERName = MUSERNAME;
entity.MTIME = dtNow;
var result = MsSqlData.Update<ICSBadCodeGroupEntity>(entity);
if (!result)
{
returnValue = "修改失败";
}
string sql = @"select
a.*
from ICSBadCode a with(nolock)
where a.BCGroupID ='" + entity.ID + "' and WorkPoint=@WorkPoint ";
var entityList = MsSqlData.Query<ICSBadCodeEntity>(sql, para).ToList();
MsSqlData.Delete<ICSBadCodeEntity>(entityList);
foreach (var item in model.DetailList)
{
var detailEntity = ConvertExt.Mapping<ICSBadCodeDto, ICSBadCodeEntity>(item);
detailEntity.ID = Guid.NewGuid().ToString();
detailEntity.BCGroupID = model.ID;
detailEntity.MUSER = MUSER;
detailEntity.MUSERName = MUSERNAME;
detailEntity.MTIME = dtNow;
detailEntity.WorkPoint = WorkPoint;
var result2 = MsSqlData.Insert<ICSBadCodeEntity>(detailEntity);
if (!result2)
{
returnValue = "修改失败";
}
}
}
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
public ICSBadCodeGroupDto GetBadCodeGroup(string keyValue)
{
var returnValue = new ICSBadCodeGroupDto();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
try
{
var entity = MsSqlData.Get<ICSBadCodeGroupEntity>(keyValue);
if (entity == null)
{
throw new Exception("获取单个规则失败");
}
returnValue = ConvertExt.Mapping<ICSBadCodeGroupEntity, ICSBadCodeGroupDto>(entity);
string sql = @"select
a.*
from ICSBadCode a with(nolock)
where a.BCGroupID ='" + entity.ID + "' and WorkPoint=@WorkPoint order by a.BadCode asc ";
var entityList = MsSqlData.Query<ICSBadCodeDto>(sql, para).ToList();
//int i = 0;
for (int i = 0; i < entityList.Count; i++)
{
entityList[i].key = i + 1;
}
returnValue.DetailList = entityList;
}
catch (Exception ex)
{
returnValue = new ICSBadCodeGroupDto();
returnValue.DetailList = new List<ICSBadCodeDto>();
}
return returnValue;
}
public string DeleteItem2AqlFile(string keyValue)
{
string returnValue = string.Empty;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
try
{
var idList = JsonConvert.DeserializeObject<List<string>>(keyValue);
string sql = @"select
a.*
from ICSInventoryInspectionRulesGroupFile a with(nolock)
where a.ID in ('" + string.Join("','", idList) + "') and WorkPoint=@WorkPoint ";
var entityList = MsSqlData.Query<ICSInventoryInspectionRulesGroupFile>(sql, para).ToList();
var result = MsSqlData.Delete<ICSInventoryInspectionRulesGroupFile>(entityList);
if (!result)
{
returnValue = "删除失败";
}
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
public DataTable GetItem2AqlFileList(string keyValue)
{
List<DbParameter> parameter = new List<DbParameter>();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
string sql = @"select
a.ID
,b.InvCode
,b.InvName
,b.InvStd
,a.MUSER
,a.MUSERName
,a.MTIME
,d.FileName
,d.ID as FileID
from ICSInventoryInspectionRulesGroup a with(nolock)
join ICSInventory b with(nolock) on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
join ICSInspectionRulesGroup c with(nolock) on a.RulesCode=c.RulesCode and a.WorkPoint=c.WorkPoint
join ICSInventoryInspectionRulesGroupFile d with(nolock)on a.ID=d.IIRGID and a.WorkPoint=d.WorkPoint
where 1=1
and a.workpoint='{1}'
and a.InvCode='{0}'
";
sql = string.Format(sql, keyValue, WorkPoint);
return Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
}
public int UpLoadItemRuleFile(string id, string fileName)
{
var returnValue = 1;
var dtNow = DateTime.Now;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
SqlConnection conn = SqlHelper.GetDataCenterConn();
var sql1 = @" select count(1) from ICSInventoryInspectionRulesGroupFile with(nolock) where IIRGID='" + id + "' and FileName='" + fileName + "' and WorkPoint=@WorkPoint";
var count = MsSqlData.ExecuteScalar(sql1, para).ToInt();
if (count > 0)
{
throw new Exception("当前物料规则文件已经存在");
}
var count2 = MsSqlData.ExecuteScalar
(" select count(1) from ICSInventoryInspectionRulesGroupFile with(nolock) where IIRGID='" + id + "' and WorkPoint=@WorkPoint ", para).ToInt();
if (count2 >= 3)
{
throw new Exception("当前物料规则文件已经超过3个,不能再次上传");
}
ICSInventoryInspectionRulesGroupFile entity = new ICSInventoryInspectionRulesGroupFile();
entity.ID = Guid.NewGuid().ToString();
entity.IIRGID = id;
entity.FileCode = fileName;
entity.FileName = fileName;
entity.MUSER = MUSER;
entity.MUSERName = MUSERNAME;
entity.MTIME = dtNow;
entity.WorkPoint = WorkPoint;
var result2 = MsSqlData.Insert<ICSInventoryInspectionRulesGroupFile>(entity);
if (!result2)
{
returnValue = 0;
}
return returnValue;
}
public string DeleteItem2Aql(string keyValue)
{
string returnValue = string.Empty;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
try
{
var idList = JsonConvert.DeserializeObject<List<string>>(keyValue);
string sql = @"select
a.*
from ICSInventoryInspectionRulesGroup a with(nolock)
where a.ID in ('" + string.Join("','", idList) + "') and WorkPoint=@WorkPoint ";
var entityList = MsSqlData.Query<ICSInventoryInspectionRulesGroup>(sql, para).ToList();
foreach (var item in entityList)
{
//var count = MsSqlData.ExecuteScalar(" select count(1) from IcsCheckingFixtureTransfer with(nolock) where CheckFixtureNo='" + item.CheckFixtureNo + "'").ToInt();
//if (count > 0)
//{
// throw new Exception("当前检具已经存在履历,不能删除");
//}
string sql2 = @"delete from ICSInventoryInspectionRulesGroupFile where IIRGID in ('" + string.Join("','", idList) + "') and WorkPoint=@WorkPoint ";
var line = MsSqlData.Execute(sql2, para);
}
var result = MsSqlData.Delete<ICSInventoryInspectionRulesGroup>(entityList);
if (!result)
{
returnValue = "删除失败";
}
//string sql2 = @"delete from ICSInspectionRulesList where RulesID in ('" + string.Join("','", idList) + "')";
//var line = Repository().ExecuteBySql(new StringBuilder(sql2));
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
public DataTable ExportAllItem2AqlList(string rulesCode, string rulesName, string invCode, string invName)
{
DataTable dt = new DataTable();
// var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
#region [SQL]
string sql = @"
select
a.*
from
(
select
b.InvCode
,b.InvName
,b.InvStd
,c.RulesCode
,c.RulesName
,c.RulesDesc
,a.MUSER
,a.MUSERName
,a.MTIME
from ICSInventoryInspectionRulesGroup a with(nolock)
join ICSInventory b with(nolock) on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
join ICSInspectionRulesGroup c with(nolock) on a.RulesCode=c.RulesCode and a.WorkPoint=c.WorkPoint
where 1=1
{0}
) a
where 1=1
--and a.wo
<1>
<2>
<3>
<4>
";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
//sql = string.Format(sql, Figure);
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
if (!string.IsNullOrWhiteSpace(rulesCode))
{
sql = sql.Replace("<1>", " and a.InvCode like '%" + rulesCode + "%' ");
}
else
{
sql = sql.Replace("<1>", " ");
}
if (!string.IsNullOrWhiteSpace(rulesName))
{
sql = sql.Replace("<2>", " and a.InvName like '%" + rulesName + "%' ");
}
else
{
sql = sql.Replace("<2>", " ");
}
if (!string.IsNullOrWhiteSpace(invCode))
{
sql = sql.Replace("<3>", " and a.RulesCode like '%" + invCode + "%' ");
}
else
{
sql = sql.Replace("<3>", " ");
}
if (!string.IsNullOrWhiteSpace(invName))
{
sql = sql.Replace("<4>", " and a.RulesName like '%" + invName + "%' ");
}
else
{
sql = sql.Replace("<4>", " ");
}
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
//{
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
// // sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
//}
//else
//{
// sql = sql.Replace("{0}", "");
//}
//if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
//{
// sql += " and a.VenCode='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "'";
//}
return Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
}
public string SaveItem2Aql(string keyValue)
{
string returnValue = string.Empty;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
try
{
var dtNow = DateTime.Now;
var model = JsonConvert.DeserializeObject<ICSInventoryInspectionRulesGroupDto>(keyValue);
if (model == null)
{
throw new Exception("传入参数错误");
}
foreach (var item in model.ItemList)
{
foreach (var item2 in model.AqlList)
{
var count = MsSqlData.ExecuteScalar
(
" select count(1) from ICSInventoryInspectionRulesGroup a with(nolock) where InvCode='" + item.InvCode + "' and a.EATTRIBUTE1='" + model.EATTRIBUTE1 + "' and WorkPoint=@WorkPoint "
, para).ToInt();
if (count > 0)
{
throw new Exception("当前物料Aql关系已经存在");
}
var entity = new ICSInventoryInspectionRulesGroup();
entity.InvCode = item.InvCode;
entity.RulesCode = item2.RulesCode;
entity.RulesName = item2.RulesName;
entity.RulesDesc = item2.RulesDesc;
entity.Type = item2.Type;
entity.EATTRIBUTE1 = model.EATTRIBUTE1;
entity.Enable = true;
entity.Editable = true;
entity.ID = Guid.NewGuid().ToString();
entity.MUSER = MUSER;
entity.MUSERName = MUSERNAME;
entity.MTIME = dtNow;
entity.WorkPoint = WorkPoint;
var result2 = MsSqlData.Insert<ICSInventoryInspectionRulesGroup>(entity);
if (!result2)
{
returnValue = "新增失败";
}
}
}
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
public List<ICSInspectionRulesGroupEntity> GetAllAqlList(string code)
{
var returnValue = new List<ICSInspectionRulesGroupEntity>();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
string sql = string.Empty;
if (code.IsNullOrEmpty())
{
sql = "select * from ICSInspectionRulesGroup with(nolock) where WorkPoint=@WorkPoint ";
}
else
{
sql = "select * from ICSInspectionRulesGroup with(nolock) where WorkPoint=@WorkPoint and RulesCode like '%" + code + "%'";
}
returnValue = MsSqlData.Query<ICSInspectionRulesGroupEntity>(sql, para).ToList();
return returnValue;
}
public List<ICSInventoryDto> GetAllItemList(string code)
{
var returnValue = new List<ICSInventoryDto>();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
string sql = "select * from ICSInventory with(nolock) where WorkPoint=@WorkPoint and InvCode like '%" + code + "%' ";
returnValue = MsSqlData.Query<ICSInventoryDto>(sql, para).ToList();
return returnValue;
}
public DataTable ExportAllAqlList(string rulesCode, string rulesName, string enable)
{
DataTable dt = new DataTable();
//var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
#region [SQL]
string sql = @"
select
case when a.Enable=1 then '是'
else '否' end as EnableText
,a.*
from ICSInspectionRulesGroup a with(nolock)
where 1=1
{0}
<1>
<2>
<3>
";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
//sql = string.Format(sql, Figure);
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
if (!string.IsNullOrWhiteSpace(rulesCode))
{
sql = sql.Replace("<1>", " and a.RulesCode like '%" + rulesCode + "%' ");
}
else
{
sql = sql.Replace("<1>", " ");
}
if (!string.IsNullOrWhiteSpace(rulesName))
{
sql = sql.Replace("<2>", " and a.RulesName like '%" + rulesName + "%' ");
}
else
{
sql = sql.Replace("<2>", " ");
}
if (!string.IsNullOrWhiteSpace(enable))
{
sql = sql.Replace("<3>", " and a.Enable =" + enable + " ");
}
else
{
sql = sql.Replace("<3>", " ");
}
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
return Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
}
public ICSInspectionRulesGroupDto GetAql(string keyValue)
{
var returnValue = new ICSInspectionRulesGroupDto();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
try
{
var entity = MsSqlData.Get<ICSInspectionRulesGroupEntity>(keyValue);
if (entity == null)
{
throw new Exception("获取单个规则失败");
}
returnValue = ConvertExt.Mapping<ICSInspectionRulesGroupEntity, ICSInspectionRulesGroupDto>(entity);
string sql = @"select
a.*
from ICSInspectionRulesList a with(nolock)
where a.RulesID ='" + entity.ID + "' and WorkPoint=@WorkPoint ";
var entityList = MsSqlData.Query<ICSInspectionRulesListDto>(sql, para).ToList();
//int i = 0;
for (int i = 0; i < entityList.Count; i++)
{
entityList[i].key = i + 1;
}
returnValue.DetailList = entityList;
}
catch (Exception ex)
{
returnValue = null;
}
return returnValue;
}
public string SaveAql(string keyValue)
{
string returnValue = string.Empty;
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
try
{
var dtNow = DateTime.Now;
var model = JsonConvert.DeserializeObject<ICSInspectionRulesGroupDto>(keyValue);
if (model == null)
{
throw new Exception("传入参数错误");
}
if (model.ID.IsNullOrEmpty())
{
//新增
var sql1 = @" select count(1) from ICSInspectionRulesGroup with(nolock) where RulesCode='" + model.RulesCode + "' and WorkPoint=@WorkPoint ";
var count = MsSqlData.ExecuteScalar(sql1, para).ToInt();
if (count > 0)
{
throw new Exception("当前规则代码已经存在");
}
model.ID = Guid.NewGuid().ToString();
model.MUSER = MUSER;
model.MUSERName = MUSERNAME;
model.MTIME = dtNow;
model.WorkPoint = WorkPoint;
var result = MsSqlData.Insert<ICSInspectionRulesGroupEntity>(model);
if (!result)
{
returnValue = "新增失败";
}
foreach (var item in model.DetailList)
{
var detailEntity = ConvertExt.Mapping<ICSInspectionRulesListDto, ICSInspectionRulesListEntity>(item);
detailEntity.ID = Guid.NewGuid().ToString();
detailEntity.RulesID = model.ID;
detailEntity.Enable = true;
detailEntity.MUSER = MUSER;
detailEntity.MUSERName = MUSERNAME;
detailEntity.MTIME = dtNow;
detailEntity.WorkPoint = WorkPoint;
var result2 = MsSqlData.Insert<ICSInspectionRulesListEntity>(detailEntity);
if (!result2)
{
returnValue = "新增失败";
}
}
}
else
{
//修改
var entity = MsSqlData.Get<ICSInspectionRulesGroupEntity>(model.ID);
if (entity == null)
{
throw new Exception("当前数据不存在");
}
ConvertExt.Mapping<ICSInspectionRulesGroupDto, ICSInspectionRulesGroupEntity>(model, entity);
entity.MUSER = MUSER;
entity.MUSERName = MUSERNAME;
entity.MTIME = dtNow;
var result = MsSqlData.Update<ICSInspectionRulesGroupEntity>(entity);
if (!result)
{
returnValue = "修改失败";
}
string sql = @"select
a.*
from ICSInspectionRulesList a with(nolock)
where a.RulesID ='" + entity.ID + "' and WorkPoint=@WorkPoint ";
var entityList = MsSqlData.Query<ICSInspectionRulesListEntity>(sql, para).ToList();
MsSqlData.Delete<ICSInspectionRulesListEntity>(entityList);
foreach (var item in model.DetailList)
{
var detailEntity = ConvertExt.Mapping<ICSInspectionRulesListDto, ICSInspectionRulesListEntity>(item);
detailEntity.ID = Guid.NewGuid().ToString();
detailEntity.RulesID = model.ID;
detailEntity.Enable = true;
detailEntity.MUSER = MUSER;
detailEntity.MUSERName = MUSERNAME;
detailEntity.MTIME = dtNow;
detailEntity.WorkPoint = WorkPoint;
var result2 = MsSqlData.Insert<ICSInspectionRulesListEntity>(detailEntity);
if (!result2)
{
returnValue = "修改失败";
}
}
}
}
catch (Exception ex)
{
returnValue = ex.Message;
}
return returnValue;
}
public DataTable GetAqlSubGridJsonList(string id, ref Pagination pagination)
{
DataTable dt = new DataTable();
// var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
//object Figure = GetDecimalDigits();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var para = new { WorkPoint = WorkPoint };
#region [SQL]
string sql = @"select
a.*
from ICSInspectionRulesList a with(nolock)
where 1=1
{0}
<1>
order by a.SetValueMin asc
";
// sql += " WHERE 1=1 and a.MOStatus<>'3' ";
sql = sql.Replace("<1>", "and a.RulesID='" + id + "'");
//sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
#endregion
sql = sql.Replace("{0}", "and a.WorkPoint = '" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'");
return Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
}
public List<SysEnumValueDto> GetSelectItemList(string key)
{
var returnValue = new List<SysEnumValueDto>();
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
string sql = @"select *
from V_QuerySysEnumItem a
where a.F_EnCode=@EnCode
";
var para = new
{
EnCode = key,
};
returnValue = MsSqlData.Query<SysEnumValueDto>(sql, para).ToList();
for (int i = 0; i < returnValue.Count; i++)
{
returnValue[i].key = i + 1;
}
return returnValue;
}
public void InsertOperateLog(string key, string data, string entityName = null, string sourceType = null)
{
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
var dtNow = DateTime.Now;
var entity = new SysModifyLog();
entity.BillCode = key;
entity.BillType = "Add";
entity.SourceType = sourceType;
entity.EntityName = entityName;
entity.JsonData = data;
entity.TenantId = WorkPoint;
entity.CreationTime = dtNow;
entity.CreatorUserId = MUSER;
entity.CreatorUserName = MUSERNAME;
entity.LastModificationTime = dtNow;
entity.LastModifierUserId = MUSER;
entity.LastModifierUserName = MUSERNAME;
var result2 = MsSqlData.Insert<SysModifyLog>(entity);
}
private void SendEmail(string type,IcsMatCheckResultDto model)
{
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
try
{
List<DbParameter> parameter = new List<DbParameter>();
List<DbParameter> parameter1 = new List<DbParameter>();
string sqls = @" select *
from V_QuerySysEnumItem a
where a.F_EnCode = 'SQEPD'";
DataTable dts = Repository().FindDataSetBySql(sqls.ToString(), parameter1.ToArray()).Tables[0];
if (dts == null || dts.Rows.Count == 0)
{
return;
}
string Email = string.Empty;
foreach (DataRow drs in dts.Rows)
{
Email += drs["Ext1"].ToString() + ",";
}
if (type == "1")
{
SendEmail(model, Email);
}
else if (type == "2")
{
SendEmailSQE(model, Email);
}
else
{
SendEmailIQC(model, Email);
}
}
catch (Exception ex)
{
InsertOperateLog(model.CheckNo, JsonConvert.SerializeObject(ex), new IcsMatCheckResult().GetType().Name, "拒收单邮件");
}
}
private void SendEmail(IcsMatCheckResultDto model, string email)
{
try
{
DateTime dtNow = DateTime.Now;
string MailOpen = ConfigurationManager.ConnectionStrings["MailOpen"].ConnectionString;
if (MailOpen == "true")
{
string SendHost = ConfigurationManager.ConnectionStrings["SendHost"].ConnectionString;
string StrSendPort = ConfigurationManager.ConnectionStrings["SendPort"].ConnectionString;
int SendPort = 25;
if (!string.IsNullOrEmpty(StrSendPort))
SendPort = Convert.ToInt32(ConfigurationManager.ConnectionStrings["SendPort"].ConnectionString);
string SendDisplayName = ConfigurationManager.ConnectionStrings["SendDisplayName"].ConnectionString;
string SendAddress = ConfigurationManager.ConnectionStrings["SendAddress"].ConnectionString;
string SendPassword = ConfigurationManager.ConnectionStrings["SendPassword"].ConnectionString;
string TOAddress = email;
string CCAddress = "";
string Subject = "您好！有来自检验拒收的订单";
bool isBodyHtml = false;
string body = "";
body += "\r\n";
body += "您好！有来自SQE拒收的信息：供应商：" + model.VenName + " ，到货时间:" + model.DHDate.Value.ToShortDateString() + " ,物料编码：" + model.InvCode + "，物料名称："
+ model.InvName + "，来料数量：" + model.FinalItemLotList.Sum(x => x.Quantity) + "，拒收数量:" + model.FinalItemLotList.Sum(x => x.NgQty) + "，可登陆咖博士WMS系统查看相关信息！";
body += "\r\n";
body += " ";
body += "\r\n";
body += " 苏州咖博士咖啡系统科技有限公司";
body += "\r\n";
body += " " + dtNow.ToString("yyyy-MM-dd");
string StrConn = MsSqlData.FromMd5(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString);
//try
//{
if (!string.IsNullOrEmpty(TOAddress))
{
//MailHelper.SendEmail(StrConn, SendHost, SendPort, SendDisplayName, SendAddress, SendPassword, TOAddress, CCAddress, Subject, isBodyHtml, body, POCode);
ICSSendMail.SendEmail(StrConn, SendHost, SendPort, SendDisplayName, SendAddress, SendPassword, TOAddress, CCAddress, Subject, isBodyHtml, body);
}
else
{
//throw new Exception("供应商邮箱地址未维护，邮件发送失败！");
// msg += "供应商:" + cVenCode + "邮箱地址未维护，邮件发送失败！";
}
//}
//catch (Exception ex)
//{
//InsertData(StrConn, SendHost, SendPort, SendDisplayName, SendAddress, SendPassword, TOAddress, CCAddress, Subject, isBodyHtml, body, "0", "");
//}
}
}
catch (Exception ex)
{
throw new Exception("供应商:" + model.VenName + "邮件发送失败！ \r\n" + ex.Message);
// msg += "供应商:" + cVenCode + "邮件发送失败！ \r\n" + ex.Message;
// InsertOperateLog("拒收单邮件", JsonConvert.SerializeObject(ex), new IcsMatCheckResult().GetType().Name, "判退");
}
}
private void SendEmailSQE(IcsMatCheckResultDto model, string email)
{
try
{
DateTime dtNow = DateTime.Now;
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
string MailOpen = ConfigurationManager.ConnectionStrings["MailOpen"].ConnectionString;
if (MailOpen == "true")
{
string SendHost = ConfigurationManager.ConnectionStrings["SendHost"].ConnectionString;
string StrSendPort = ConfigurationManager.ConnectionStrings["SendPort"].ConnectionString;
int SendPort = 25;
if (!string.IsNullOrEmpty(StrSendPort))
SendPort = Convert.ToInt32(ConfigurationManager.ConnectionStrings["SendPort"].ConnectionString);
string SendDisplayName = ConfigurationManager.ConnectionStrings["SendDisplayName"].ConnectionString;
string SendAddress = ConfigurationManager.ConnectionStrings["SendAddress"].ConnectionString;
string SendPassword = ConfigurationManager.ConnectionStrings["SendPassword"].ConnectionString;
//查询 不良分类
string sql = $" select BcgDesc from ICSBadCodeGroup where workpoint='{WorkPoint}' and bcgcode='{model.BRGCode}' ";
var dt = SqlHelper.CmdExecuteDataTable(sql);
string TOAddress = email;
string CCAddress = "";
string Subject = "您好！有来自SQE复判NG的信息";
bool isBodyHtml = false;
string body = "";
body += "\r\n";
body += "您好！有来自SQE复判NG的信息：供应商：" + model.VenName + " ，到货时间:" + model.DHDate.Value.ToShortDateString() + " ,物料编码：" + model.InvCode + "，物料名称："
+ model.InvName + "，来料数量：" + model.InvBatcgQty + "，不良分类：" + dt.Rows[0]["BcgDesc"] + "，SQE处理方案：" + model.EATTRIBUTE1 + "，可登陆咖博士WMS系统查看相关信息！";
body += "\r\n";
body += " ";
body += "\r\n";
body += " 苏州咖博士咖啡系统科技有限公司";
body += "\r\n";
body += " " + dtNow.ToString("yyyy-MM-dd");
string StrConn = MsSqlData.FromMd5(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString);
//try
//{
if (!string.IsNullOrEmpty(TOAddress))
{
//MailHelper.SendEmail(StrConn, SendHost, SendPort, SendDisplayName, SendAddress, SendPassword, TOAddress, CCAddress, Subject, isBodyHtml, body, POCode);
ICSSendMail.SendEmail(StrConn, SendHost, SendPort, SendDisplayName, SendAddress, SendPassword, TOAddress, CCAddress, Subject, isBodyHtml, body);
}
else
{
//throw new Exception("供应商邮箱地址未维护，邮件发送失败！");
// msg += "供应商:" + cVenCode + "邮箱地址未维护，邮件发送失败！";
}
//}
//catch (Exception ex)
//{
//InsertData(StrConn, SendHost, SendPort, SendDisplayName, SendAddress, SendPassword, TOAddress, CCAddress, Subject, isBodyHtml, body, "0", "");
//}
}
}
catch (Exception ex)
{
throw new Exception("供应商:" + model.VenName + "邮件发送失败！ \r\n" + ex.Message);
// msg += "供应商:" + cVenCode + "邮件发送失败！ \r\n" + ex.Message;
// InsertOperateLog("拒收单邮件", JsonConvert.SerializeObject(ex), new IcsMatCheckResult().GetType().Name, "判退");
}
}
private void SendEmailIQC(IcsMatCheckResultDto model, string email)
{
try
{
DateTime dtNow = DateTime.Now;
string MailOpen = ConfigurationManager.ConnectionStrings["MailOpen"].ConnectionString;
if (MailOpen == "true")
{
string SendHost = ConfigurationManager.ConnectionStrings["SendHost"].ConnectionString;
string StrSendPort = ConfigurationManager.ConnectionStrings["SendPort"].ConnectionString;
int SendPort = 25;
if (!string.IsNullOrEmpty(StrSendPort))
SendPort = Convert.ToInt32(ConfigurationManager.ConnectionStrings["SendPort"].ConnectionString);
string SendDisplayName = ConfigurationManager.ConnectionStrings["SendDisplayName"].ConnectionString;
string SendAddress = ConfigurationManager.ConnectionStrings["SendAddress"].ConnectionString;
string SendPassword = ConfigurationManager.ConnectionStrings["SendPassword"].ConnectionString;
string TOAddress = email;
string CCAddress = "";
string Subject = "您好！有来自IQC复判NG的信息";
bool isBodyHtml = false;
string body = "";
body += "\r\n";
body += "您好！有来自IQC复判NG的信息：供应商：" + model.VenName + " ，到货时间:" + model.DHDate.Value.ToShortDateString() + " ,物料编码：" + model.InvCode + "，物料名称："
+ model.InvName + "，来料数量：" + model.InvBatcgQty + "，可登陆咖博士WMS系统查看相关信息！";
body += "\r\n";
body += " ";
body += "\r\n";
body += " 苏州咖博士咖啡系统科技有限公司";
body += "\r\n";
body += " " + dtNow.ToString("yyyy-MM-dd");
string StrConn = MsSqlData.FromMd5(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString);
//try
//{
if (!string.IsNullOrEmpty(TOAddress))
{
//MailHelper.SendEmail(StrConn, SendHost, SendPort, SendDisplayName, SendAddress, SendPassword, TOAddress, CCAddress, Subject, isBodyHtml, body, POCode);
ICSSendMail.SendEmail(StrConn, SendHost, SendPort, SendDisplayName, SendAddress, SendPassword, TOAddress, CCAddress, Subject, isBodyHtml, body);
}
else
{
//throw new Exception("供应商邮箱地址未维护，邮件发送失败！");
// msg += "供应商:" + cVenCode + "邮箱地址未维护，邮件发送失败！";
}
//}
//catch (Exception ex)
//{
//InsertData(StrConn, SendHost, SendPort, SendDisplayName, SendAddress, SendPassword, TOAddress, CCAddress, Subject, isBodyHtml, body, "0", "");
//}
}
}
catch (Exception ex)
{
throw new Exception("供应商:" + model.VenName + "邮件发送失败！ \r\n" + ex.Message);
// msg += "供应商:" + cVenCode + "邮件发送失败！ \r\n" + ex.Message;
// InsertOperateLog("拒收单邮件", JsonConvert.SerializeObject(ex), new IcsMatCheckResult().GetType().Name, "判退");
}
}
//public object GetFTPFile(string keyValue)
//{
// var Header = new Dictionary<string, object>
// {
// ["msg"] = "",
// ["FileName"] = ""
// };
// keyValue = "DCC00001021";
// var PDMURL = ConfigurationManager.ConnectionStrings["PDMURL"].ConnectionString;
// string APIURLBidversion = PDMURL + keyValue+ "&version=all";
// var ResultFTPFileInfo = SqlHelper.httpGetByHeader(APIURLBidversion, "Authorization", "Basic d2NhZG1pbjp3Y2FkbWlu");
// JObject FTPFileInfo = (JObject)JsonConvert.DeserializeObject(ResultFTPFileInfo);
// string FTPTop = (string)FTPFileInfo["file_path"];//获取Tockn
// string FTPFileName = (string)FTPFileInfo["file_name"];
// if (FTPTop == null && FTPFileName == null)
// {
// Header["msg"] = (string)FTPFileInfo["message"];
// return Header;
// }
// var url = GetFTPToPath(FTPFileName, FTPTop, "FTP4", keyValue);
// Header["FileName"] = url;
// return Header;
//}
//public object GetFTPToPath(string pathName, string FTPTopPath, string Code)
//{
// try
// {
// Code = "DCC00001021";
// string returnpath = "";
// FtpWeb ftpWeb = new FtpWeb(FTPTopPath, "Administrator", "Pass@201609");
// string TempFile = System.Web.HttpContext.Current.Server.MapPath("~\\File\\PDMFile\\" + Code + "");
// if (Directory.Exists(TempFile) == false)//如果不存在就创建file文件夹
// {
// Directory.CreateDirectory(TempFile);
// }
// if (ftpWeb.FileExist(pathName))
// {
// ftpWeb.Download(TempFile + "\\", pathName);
// returnpath = pathName;
// }
// else
// {
// returnpath = "";
// }
// return returnpath;
// }
// catch (Exception ex)
// {
// throw ex;
// }
//}
public object GetFTPToPath(string pathName, string FTPTopPath, string materialCode, string version)
{
var Header = new Dictionary<string, object>
{
["msg"] = "",
["FileName"] = ""
};
try
{
// 移除硬编码，使用传入的Code参数
//Code = "DCC00001021";
string returnpath = "";
FtpWeb ftpWeb = new FtpWeb(FTPTopPath, "Administrator", "Pass@201609");
string TempFile = System.Web.HttpContext.Current.Server.MapPath($"~\\File\\PDMFile\\{materialCode}\\{version}\\");
if (!Directory.Exists(TempFile))
{
Directory.CreateDirectory(TempFile);
}
if (ftpWeb.FileExist(pathName))
{
ftpWeb.Download(TempFile, pathName);
returnpath = pathName;
}
Header["FileName"] = returnpath;
}
catch (Exception ex)
{
Header["msg"] = "下载失败: " + ex.Message;
}
return Header;
}
public object GetFTPFile(string keyValue)
{
// keyValue = "DCC00001021";
var PDMURL = ConfigurationManager.ConnectionStrings["PDMURL"].ConnectionString;
string APIURL = PDMURL + keyValue + "&version=all";
// 调用PDM接口
var pdmResponse = SqlHelper.httpGetByHeader(APIURL, "Authorization", "Basic d2NhZG1pbjp3Y2FkbWlu");
try
{
// 解析JSON响应
JObject responseObj = JObject.Parse(pdmResponse);
// 根据实际接口返回结构获取文件列表数组
// 假设文件列表在"data"字段中，需根据实际情况调整
JArray fileList = (JArray)responseObj["data"];
var processedFiles = new List<object>();
foreach (JObject file in fileList)
{
string ftpPath = file["file_path"]?.ToString();
string ftpFileName = file["file_name"]?.ToString();
string creator = file["creator"]?.ToString();
string createTime = file["create_time"]?.ToString();
string version = file["version"]?.ToString();
string code = file["material_code"]?.ToString();
if (string.IsNullOrEmpty(ftpPath) || string.IsNullOrEmpty(ftpFileName))
continue;
// 下载文件到本地
// string localFileName = GetFTPToPath(ftpFileName, ftpPath, "FTP4", keyValue);
//if (!string.IsNullOrEmpty(localFileName))
//{
processedFiles.Add(new
{
file_name = ftpFileName,
create_time = createTime,
ftpPath= ftpPath,
version = version,
creator = creator,
code= code
// local_path = localFileName
});
//}
}
return processedFiles;
}
catch (Exception ex)
{
// 记录详细错误日志
Console.WriteLine($"解析PDM接口响应失败: {ex.Message}");
Console.WriteLine($"原始响应: {pdmResponse}");
return new List<object>(); // 返回空列表
}
}
//public static string GetFTPToPath(string fileName, string ftpFullPath, string dbFtpKey, string code)
//{
// try
// {
// // 解析FTP地址（示例：ftp://172.66.9.27/DCC00001021T3.1/DOCType.pdf）
// Uri ftpUri = new Uri(ftpFullPath);
// string ftpServer = ftpUri.Host; // 172.66.9.27
// string ftpRemoteDir = ftpUri.LocalPath; // /DCC00001021T3.1/
// string ftpUser = "Administrator"; // PDM认证的用户名
// string ftpPwd = "Pass@201609"; // PDM认证的密码
// // 本地存储路径：~/File/PDMFile/{code}/
// string localDir = System.Web.HttpContext.Current.Server.MapPath($"~\\File\\PDMFile\\{code}\\");
// if (!Directory.Exists(localDir))
// Directory.CreateDirectory(localDir);
// // 初始化FTP客户端
// FtpWeb ftpClient = new FtpWeb(ftpServer, ftpUser, ftpPwd);
// // 不需要单独调用ChangeDirectory，现在在每个请求中自动使用当前目录
// if (ftpClient.FileExist(fileName))
// {
// ftpClient.Download(localDir, fileName); // 下载到本地
// return fileName; // 返回本地文件名
// }
// return "";
// }
// catch (Exception ex)
// {
// // 记录日志或抛出异常
// Console.WriteLine($"FTP下载失败: {ex.Message}");
// return "";
// }
//}
}
}