|
|
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; }
}}
|
