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.
269 lines
13 KiB
269 lines
13 KiB
using Newtonsoft.Json;
|
|
using Newtonsoft.Json.Linq;
|
|
using NFine.Code;
|
|
using NFine.Data.Extensions;
|
|
using NFine.Domain._03_Entity.SRM;
|
|
using NFine.Domain.Entity.ProductManage;
|
|
using NFine.Domain.IRepository.ProductManage;
|
|
using NFine.Repository;
|
|
using NFine.Repository.ProductManage;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Configuration;
|
|
using System.Data;
|
|
using System.Data.Common;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using System.Net;
|
|
using System.Text;
|
|
|
|
namespace NFine.Application.SRM
|
|
{
|
|
public class VerificationApp : RepositoryFactory<ICSVendor>
|
|
{
|
|
public DataTable GetGridJson(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select a.ID, b.GGUID,a.VENDORCODE,c.VenName cVenName,a.AUDITRESULT,a.AUDITRESULT as AUDITRESULTS,a.CERTIFICATETPYECODE,a.CERTIFICATETYPENAME,a.CERTIFICATEITEMCODE,a.CERTIFICATEITEMNAME,
|
|
a.ISKEYTASK,a.NEEDUPLOADFILE,b.BEGINDATE,b.ENDDATE,b.CERTIFICATETYPENFilePath,b.CERTIFICATETYPENFileName,a.CREATEDATE as MODIFIEDTIME,a.WorkPoint,
|
|
b.CERTIFICATIONINSTITUTIONS,b.CERTIFICATENO,b.CERTIFICATETYPENAME as BCERTIFICATETYPENAME,a.Remark as Remark,b.Type,b.ADDITION1,
|
|
case when isnull(e.TEMPVENDORCODE,'')='' then f.TEMPVENDORCODE else d.F_Account end as TEMPVENDORCODE,a.VenType,a.Supplierlevel
|
|
from ICSCERTIFICATEITEM a
|
|
inner join ICSCERTIFICATEITEMDETAIL b on a.ID=b.GGUID and a.WorkPoint=b.WorkPoint and b.STATUS='有效'
|
|
left join ICSVendor c on a.VENDORCODE=c.VenCode and b.WorkPoint=c.WorkPoint
|
|
left join Sys_SRM_User d on c.VenCode=d.F_VenCode and c.WorkPoint=d.F_Location
|
|
left join ICSPREVENDOR e on d.F_Account=e.TEMPVENDORCODE and a.WorkPoint=e.WorkPoint
|
|
left join ICSPREVENDOR f on c.VenCode=f.VenCode and a.WorkPoint=c.WorkPoint
|
|
where a.AUDITRESULT<>'New'
|
|
";
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["CERTIFICATETYPENAME"].ToString()))
|
|
{
|
|
sql += " and a.CERTIFICATETYPENAME like '%" + queryParam["CERTIFICATETYPENAME"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["CERTIFICATEITEMNAME"].ToString()))
|
|
{
|
|
sql += " and a.CERTIFICATEITEMNAME like '%" + queryParam["CERTIFICATEITEMNAME"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["VENDORCODE"].ToString()))
|
|
{
|
|
sql += " and a.VENDORCODE like '%" + queryParam["VENDORCODE"].ToString() + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["VENDORCODEName"].ToString()))
|
|
{
|
|
sql += " and c.VenName like '%" + queryParam["VENDORCODEName"].ToString() + "%' ";
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
|
|
{
|
|
sql += " and a.MODIFIEDDATE >= '" + queryParam["TimeFrom"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["TimeTo"].ToString()))
|
|
{
|
|
sql += " and a.MODIFIEDDATE <= '" + queryParam["TimeTo"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["status"].ToString()))
|
|
{
|
|
sql += " and a.AUDITRESULT in( " + queryParam["status"].ToString().TrimEnd(',') + ") ";
|
|
}
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
|
|
{
|
|
sql += " and a.WorkPoint in (" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',') + ")";
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Vendor"))
|
|
{
|
|
sql += " and c.cVenCode in (SELECT cVenCode FROM ICSVendor where cVenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "')";
|
|
}
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 采购审核
|
|
/// </summary>
|
|
/// <param name="keyValue"></param>
|
|
/// <param name="WorkPoint"></param>
|
|
/// <returns></returns>
|
|
public string Audit(string keyValue)
|
|
{
|
|
string msg = "";
|
|
string sql = string.Empty;
|
|
try
|
|
{
|
|
JArray res = (JArray)JsonConvert.DeserializeObject(keyValue);
|
|
foreach (var item in res)
|
|
{
|
|
JObject jo = (JObject)item;
|
|
sql += @"UPDATE ICSCERTIFICATEITEM SET AUDITRESULT = 'PurchasingPass',VenType='{2}',Supplierlevel='{3}' WHERE ID ='{0}' and WorkPoint='{1}' ";
|
|
sql = string.Format(sql, jo["ID"].ToString(), jo["WorkPoint"].ToString(), jo["VenType"].ToString(), jo["Supplierlevel"].ToString());
|
|
|
|
}
|
|
SqlHelper.CmdExecuteNonQueryLi(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
|
|
msg = ex.Message;
|
|
}
|
|
return msg;
|
|
}
|
|
/// <summary>
|
|
/// 经理审核
|
|
/// </summary>
|
|
/// <param name="keyValue"></param>
|
|
/// <param name="WorkPoint"></param>
|
|
/// <returns></returns>
|
|
public string AuditJL(string keyValue, string WorkPoint)
|
|
{
|
|
string msg = "";
|
|
try
|
|
{
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
WorkPoint = WorkPoint.Substring(1, WorkPoint.Length - 2);
|
|
string sql = string.Empty;
|
|
sql = @"UPDATE ICSCERTIFICATEITEM SET AUDITRESULT = 'Pass' WHERE ID IN ({0}) and WorkPoint='{1}' ";
|
|
sql = string.Format(sql, keyValue.TrimEnd(','), WorkPoint);
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
|
|
msg = ex.Message;
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
|
|
public string CancelAudit(string keyValue, string WorkPoint, string VENDORCODE, string Type, string Remark)
|
|
{
|
|
string msg = "";
|
|
string IsStop = "";
|
|
string sql = string.Empty;
|
|
string sqlU9 = "SELECT DBIpAddress,DBName FROM dbo.Sys_DataBase WHERE DBSourceName='ERP'";
|
|
DataTable dtU9 = SqlHelper.GetDataTableBySql(sqlU9);
|
|
string U9IP = dtU9.Rows[0]["DBIpAddress"].ToString();
|
|
string DBName = dtU9.Rows[0]["DBName"].ToString();
|
|
try
|
|
{
|
|
string sqlItem = @"select F_ItemCode from [dbo].[Sys_SRM_Items] a
|
|
LEFT JOIN [dbo].[Sys_SRM_ItemsDetail] b on a.F_Id=b.F_itemID
|
|
where F_EnCode='Vendor001'";
|
|
DataTable dts = SqlHelper.GetDataTableBySql(sqlItem);
|
|
if (dts.Rows.Count > 0)
|
|
{
|
|
IsStop = dts.Rows[0]["F_ItemCode"].ToString();
|
|
}
|
|
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
keyValue = keyValue.Substring(1, keyValue.Length - 2);
|
|
WorkPoint = WorkPoint.Substring(1, WorkPoint.Length - 2);
|
|
VENDORCODE = VENDORCODE.Substring(1, VENDORCODE.Length - 2);
|
|
Type = Type.Substring(1, Type.Length - 2);
|
|
Remark = Remark.Substring(1, Remark.Length - 2);
|
|
sql += string.Format(@"UPDATE ICSCERTIFICATEITEM SET AUDITRESULT = 'REJECT',Remark='{2}' WHERE ID IN ({0}) and WorkPoint='{1}' ", keyValue.TrimEnd(','), WorkPoint, Remark);
|
|
if (Type == "1") {
|
|
sql += string.Format(@"update b set b.STATUS='失效',b.MODIFIEDDATE=getdate(),b.MODIFIEDTIME=getdate(),b.MODIFIEDUSER='{0}',b.AUDITRESULT='REJECT' from (select top 1 * from ICSCERTIFICATEITEMDETAIL a where a.gguid={1} order by a.CREATETIME desc ) b", MUSER, keyValue.TrimEnd(','));
|
|
}
|
|
SqlHelper.CmdExecuteNonQueryLi(sql);
|
|
if (IsStop == "1")
|
|
{
|
|
string sqlU8 = string.Format(@"UPDATE [{0}].{1}.dbo.Vendor set dEndDate=GetDate() where cVenCode='{2}'", U9IP, DBName, VENDORCODE);
|
|
SqlHelper.ExecuteNonQuery(sqlU8);
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
|
|
msg = ex.Message;
|
|
}
|
|
return msg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 导出
|
|
/// </summary>
|
|
/// <param name="ORDERNO"></param>
|
|
/// <param name="STNO"></param>
|
|
/// <param name="BeginDate"></param>
|
|
/// <param name="EndDate"></param>
|
|
/// <param name="VenCode"></param>
|
|
/// <param name="VenName"></param>
|
|
/// <param name="InvCode"></param>
|
|
/// <param name="PersonName"></param>
|
|
/// <param name="POStatus"></param>
|
|
/// <param name="IsAll"></param>
|
|
/// <returns></returns>
|
|
public DataTable ExportAll(string CERTIFICATETYPENAME, string CERTIFICATEITEMNAME, string VENDORCODE, string VENDORCODEName, string TimeFrom, string TimeTo, string status)
|
|
{
|
|
string ParentId = SqlHelper.Organize_F_ParentId(NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode);
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"select
|
|
a.VenType 供应商类型,
|
|
a.Supplierlevel 供应商等级,
|
|
a.CERTIFICATETYPENAME 认证类型名称,
|
|
a.CERTIFICATEITEMNAME 认证项目名称,
|
|
a.VENDORCODE 供应商代码,
|
|
c.VenName 供应商名称,
|
|
a.AUDITRESULT as 状态,
|
|
a.Supplierlevel 供应商等级,
|
|
CASE WHEN a.ISKEYTASK='0' THEN '是' WHEN a.ISKEYTASK='1' THEN '否' END 是否关键项,
|
|
CASE WHEN a.NEEDUPLOADFILE='0' THEN '是' WHEN a.NEEDUPLOADFILE='1' THEN '否' END 是否传附件,
|
|
b.BEGINDATE 生效日期,
|
|
b.ENDDATE 失效日期,
|
|
a.CREATEDATE as 维护日期,
|
|
a.Remark as 审核备注,
|
|
b.ADDITION1 是否长期
|
|
from ICSCERTIFICATEITEM a
|
|
inner join ICSCERTIFICATEITEMDETAIL b on a.ID=b.GGUID and a.WorkPoint=b.WorkPoint and b.STATUS='有效'
|
|
left join ICSVendor c on a.VENDORCODE=c.VenCode and b.WorkPoint=c.WorkPoint
|
|
left join Sys_SRM_User d on c.VenCode=d.F_VenCode and c.WorkPoint=d.F_Location
|
|
left join ICSPREVENDOR e on d.F_Account=e.TEMPVENDORCODE and a.WorkPoint=e.WorkPoint
|
|
left join ICSPREVENDOR f on c.VenCode=f.VenCode and a.WorkPoint=c.WorkPoint
|
|
where a.AUDITRESULT<>'New'
|
|
";
|
|
if (!string.IsNullOrWhiteSpace(CERTIFICATETYPENAME))
|
|
{
|
|
sql += " and a.CERTIFICATETYPENAME like '%" + CERTIFICATETYPENAME + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(CERTIFICATEITEMNAME))
|
|
{
|
|
sql += " and a.CERTIFICATEITEMNAME like '%" + CERTIFICATEITEMNAME + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(VENDORCODE))
|
|
{
|
|
sql += " and a.VENDORCODE like '%" + VENDORCODE + "%' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(VENDORCODEName))
|
|
{
|
|
sql += " and c.VenName like '%" + VENDORCODEName + "%' ";
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(TimeFrom))
|
|
{
|
|
sql += " and a.MODIFIEDDATE >= '" + TimeFrom + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(TimeTo))
|
|
{
|
|
sql += " and a.MODIFIEDDATE <= '" + TimeTo + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(status))
|
|
{
|
|
sql += " and a.AUDITRESULT in( " + status + ") ";
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
|
|
{
|
|
sql += " and a.WorkPoint in (" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',') + ")";
|
|
}
|
|
if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode.Contains("Vendor"))
|
|
{
|
|
sql += " and c.cVenCode in (SELECT cVenCode FROM ICSVendor where cVenName ='" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserName + "')";
|
|
}
|
|
DataTable dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
|
|
}
|
|
}
|