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

    }
}