HGSRM/WMS-BS/NFine.Application/WMS/CheckingFixtureApp.cs

using Newtonsoft.Json;
using NFine.Application.Entity;
using NFine.Application.Models;
using NFine.Code;
using NFine.Data.Extensions;
using NFine.Domain._03_Entity.SRM;
using NFine.Repository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;
namespace NFine.Application.WMS
{
    public class CheckingFixtureApp : RepositoryFactory<ICSVendor>
    {

        public DataTable GetGridJsonChengPing(string queryJson,  string type,ref Pagination pagination)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            //object Figure = GetDecimalDigits();

            string sql = string.Empty;
            #region [SQL]
            if (type=="1") 
            {
                                   sql = @"	
                  select 0 Code
                  
                  ,case when a.Status='领用' then (select  top 1 TransferUserName from                  IcsCheckingFixtureTransfer  t   with(nolock)
                  where t.CheckFixtureNo=a.CheckFixtureNo and t.TransferType='领用' 
                  order by t.MTIME desc)
                  else '' end as TransferUserName
                  ,(select  top 1 MTIME from IcsCheckingFixtureTransfer t  with(nolock)
                  where t.CheckFixtureNo=a.CheckFixtureNo  and t.TransferType='领用'
                  order by t.MTIME desc) TransferTime
                  ,a.*,case when DATEDIFF(day, GETDATE(),a.VerifyDate)+a.VerifyPeriodDay<0 then 'red' WHEN  DATEDIFF     (day,GETDATE(),a.VerifyDate)+a.VerifyPeriodDay>=0 AND DATEDIFF(day,  GETDATE(),a.VerifyDate)+a.VerifyPeriodDay <=10   THEN      'yellow' ELSE ''   END   AS  DiffDate
                  from IcsCheckingFixture  a
                  where a.Status !='报废' and a.Status !='封存'
                  --and a.CheckFixtureNo='CS001'
                  --order by a.MTIME desc
                  <1>
                  <2>
                  <3>
                  <4>
                  <5>
                  <6>
                  <7>
                  	";

                if (!string.IsNullOrWhiteSpace(queryParam["JJStatus"].ToString()))
                {
                    if (queryParam["JJStatus"].ToString() == "临近过期")
                    {
                        sql = sql.Replace("<7>", "and DATEDIFF(day,GETDATE(),a.VerifyDate)+a.VerifyPeriodDay>=0 AND DATEDIFF(day,  GETDATE(),a.VerifyDate)+a.VerifyPeriodDay <=10 ");
                    }
                    else if (queryParam["JJStatus"].ToString() == "已过期")
                    {
                        sql = sql.Replace("<7>", " and DATEDIFF(day, GETDATE(),a.VerifyDate)+a.VerifyPeriodDay<0 ");
                    }
                    else if (queryParam["JJStatus"].ToString() == "正常")
                    {
                        sql = sql.Replace("<7>", " and DATEDIFF(day, GETDATE(),a.VerifyDate)+a.VerifyPeriodDay>10 ");
                    }
                    else 
                    {
                        sql = sql.Replace("<7>", " ");
                    }

                    
                }
                else
                {
                    sql = sql.Replace("<7>", " ");
                }

            } 
            else 
            {
                                 sql = @"	
                select 0 Code
                
                ,case when a.Status='领用' then (select  top 1 TransferUserName from          IcsCheckingFixtureTransfer    t   with(nolock)
                where t.CheckFixtureNo=a.CheckFixtureNo and t.TransferType='领用' 
                order by t.MTIME desc)
                else '' end as TransferUserName
                ,(select  top 1 MTIME from IcsCheckingFixtureTransfer t  with(nolock)
                where t.CheckFixtureNo=a.CheckFixtureNo  and t.TransferType='领用'
                order by t.MTIME desc) TransferTime
                ,a.*,case when DATEDIFF(day, GETDATE(),a.VerifyDate)<0 then 'red' WHEN DATEDIFF (day,     GETDATE    (),a.VerifyDate)>=0 AND DATEDIFF(day, GETDATE   (),a.VerifyDate)<=10 THEN   'yellow' ELSE '' END     AS DiffDate
                from IcsCheckingFixture  a
                where  a.Status ='封存'
                --and a.CheckFixtureNo='CS001'
                --order by a.MTIME desc
                <1>
                <2>
                <3>
                <4>
                <5>
                <6>
                	";

            }
           
            // 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["CheckCode"].ToString()))
                {
                    sql = sql.Replace("<1>", " and a.CheckFixtureNo like '%" + queryParam["CheckCode"].ToString() + "%' ");
                }
                else
                {
                    sql = sql.Replace("<1>", " ");
                }

                if (!string.IsNullOrWhiteSpace(queryParam["CheckFixtureName"].ToString()))
                {
                    sql = sql.Replace("<2>", " and a.CheckFixtureName like '%" + queryParam["CheckFixtureName"].ToString() + "%' ");
                }
                else
                {
                    sql = sql.Replace("<2>", " ");
                }

                if (!string.IsNullOrWhiteSpace(queryParam["Status"].ToString()))
                {

                    sql = sql.Replace("<3>", " and a.Status ='" + queryParam["Status"].ToString() + "' ");
                }
                else
                {
                    sql = sql.Replace("<3>", " ");
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CustomerItemCode"].ToString()))
                {

                    sql = sql.Replace("<4>", " and a.CustomerItemCode like '%" + queryParam["CustomerItemCode"].ToString() + "%' ");
                }
                else
                {
                    sql = sql.Replace("<4>", " ");
                }

                if (!string.IsNullOrWhiteSpace(queryParam["VerifyDateBegin"].ToString()))
                {

                    sql = sql.Replace("<5>", " and a.VerifyDate>='" + queryParam["VerifyDateBegin"].ToString() + " 00:00:00' ");
                }
                else
                {
                    sql = sql.Replace("<5>", " ");
                }

                if (!string.IsNullOrWhiteSpace(queryParam["VerifyDateEnd"].ToString()))
                {

                    sql = sql.Replace("<6>", " and a.VerifyDate<='" + queryParam["VerifyDateEnd"].ToString() + " 23:59:59' ");
                }
                else
                {
                    sql = sql.Replace("<6>", " ");
                }
            }
            //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 List<SysEnumValueDto> GetLocationList(string keyValue)
        {
            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;

            object para = new {WorkPoint=WorkPoint };

            var sql = @" select
a.LocationCode as value
,a.LocationName as  label
from ICSLocation a with(nolock)
where 1=1
--and a.F_Account='CS001'
and a.WorkPoint=@WorkPoint
order by a.MTIME desc";

            returnValue = MsSqlData.Query<SysEnumValueDto>(sql, para).ToList();

            for (int i = 0; i < returnValue.Count; i++)
            {
                returnValue[i].key = i + 1;
            }

            return returnValue;
        }

        public IcsCheckingFixture GetCheckingFixture(string keyValue)
        {
            var entity = new IcsCheckingFixture();
            try
            {
                entity = MsSqlData.Get<IcsCheckingFixture>(keyValue);
            }
            catch (Exception ex)
            {
                //entity = null;
            }
            return entity;
        }

        public string DeleteCheckingFixture(string keyValue)
        {
            string returnValue = string.Empty;
            try
            {
                var idList = JsonConvert.DeserializeObject<List<string>>(keyValue);

                string sql = @"select 
a.*
from IcsCheckingFixture a with(nolock)
where  a.ID in ('" + string.Join("','", idList) + "')";
                var entityList = MsSqlData.Query<IcsCheckingFixture>(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 result = MsSqlData.Delete<IcsCheckingFixture>(entityList);
                if (!result)
                {
                    returnValue = "删除失败";
                }
            }
            catch (Exception ex)
            {
                returnValue = ex.Message;
            }
            return returnValue;
        }

        public string PostSealed(string type, string keyValue,string time)
        {
            string returnValue = string.Empty;
            try
            {
                List<IcsCheckingFixture> entityList=new List<IcsCheckingFixture>();

                if (type=="封存") 
                {
                    var idList = JsonConvert.DeserializeObject<List<string>>(keyValue);

                    string sql = @"select 
a.*
from IcsCheckingFixture a with(nolock)
where  a.ID in ('" + string.Join("','", idList) + "')";
                     entityList = MsSqlData.Query<IcsCheckingFixture>(sql).ToList();
                    for (int i = 0; i < entityList.Count; i++)
                    {
                        if (entityList[i].Status != "在库")
                        {
                            throw new Exception("当前检具状态不是在库状态，不能封存！");
                        }
                        entityList[i].Status = "封存";
                        entityList[i].VerifyDate = null;
                    }
                } else 
                {
                    var idList = keyValue.Substring(1, keyValue.Length-2).Split(',').ToList();

                    string sql = @"select 
a.*
from IcsCheckingFixture a with(nolock)
where  a.ID in ('" + string.Join("','", idList) + "')";
                     entityList = MsSqlData.Query<IcsCheckingFixture>(sql).ToList();
                    for (int i = 0; i < entityList.Count; i++)
                    {
                        if (entityList[i].Status != "封存")
                        {
                            throw new Exception("当前检具状态不是在封存态，不能解封！");
                        }
                        entityList[i].Status = "在库";
                        entityList[i].VerifyDate = time.ToDate();
                    }
                }
               


                var result = MsSqlData.Update<IcsCheckingFixture>(entityList);
                if (!result)
                {
                    returnValue = "修改失败";
                }
            }
            catch (Exception ex)
            {
                returnValue = ex.Message;
            }
            return returnValue;
        }

        public DataTable ExportAll(string id, string checkFixtureName, string status)
        {
            DataTable dt = new DataTable();
            // var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            //object Figure = GetDecimalDigits();

            #region [SQL]
            string sql = @"	
select 
a.*
from IcsCheckingFixture  a
where 1=1
--and a.CheckFixtureNo='CS001'
--order by a.MTIME desc
<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(id))
            {
                sql = sql.Replace("<1>", " and a.CheckFixtureNo like '%" + id + "%' ");
            }
            else
            {
                sql = sql.Replace("<1>", " ");
            }

            if (!string.IsNullOrWhiteSpace(checkFixtureName))
            {
                sql = sql.Replace("<2>", " and a.CheckFixtureName like '%" + checkFixtureName + "%' ");
            }
            else
            {
                sql = sql.Replace("<2>", " ");
            }

            if (!string.IsNullOrWhiteSpace(status))
            {

                sql = sql.Replace("<3>", " and a.Status ='" + status + "' ");
            }
            else
            {
                sql = sql.Replace("<3>", " ");
            }
            return Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
        }

        //public string PrintLabel(string keyValue)
        //{
        //    string strPrintFileName = Server.MapPath("/ReportFile/") + "lot. fr3";
        //        PrintIson pJson = new PrintIson(Server, MapPath("./PrintTemp"), strPrintFileName).
        //        pJson.CheckRegister("苏州智合诚信息科技有限公司", "56D3B4C172D0D140841CAC98A58A819F4E2SEDA5D6E45711DDD64F6A439F68B6A7870CD7DAFD69A919CBS70207FE4BB206F92BE3D53C221B019E0797E73? 9EBA4"): //注册信息
        //        pJson.MasterOptions(1, "ASNCode".
        //        //主从关系
        //        false):
        //        SetCookieAndURL(pJson);// 设置控件调用的Cookie值，判断是否安装了打印控件


        //}

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

            List<IcsCheckingFixture> list = new List<IcsCheckingFixture>();
            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) + "行," + "客户名称为空");
                }

                IcsCheckingFixture entity = new IcsCheckingFixture();
                entity.ID = Guid.NewGuid().ToString();
                entity.CheckFixtureNo = item["检具编码"].ToStringExt();
                entity.CheckFixtureName = item["客户名称 "].ToStringExt();
                entity.CustomerItemCode = item["客户料号"].ToStringExt();
                entity.Useage = item["用途"].ToStringExt();
                entity.Std = item["规格"].ToStringExt();
                entity.Unit = item["单位"].ToStringExt();
                entity.Remark = item["备注"].ToStringExt();
                entity.VerifyDate = item["校验日期"].ToDate();
                entity.VerifyPeriodDay = item["校验周期"].ToInt();
                entity.Status = "在库";
                entity.MUSER = MUSER;
                entity.MUSERName = MUSERNAME;
                entity.MTIME = time;
                entity.WorkPoint = WorkPoint;

                entity.VerifyPeriodDay = entity.VerifyPeriodDay == 0 ? null : entity.VerifyPeriodDay;
                list.Add(entity);
            }
            if (list.Count == 0)
            {
                throw new Exception("没有导入数据");
            }
            var result = MsSqlData.Insert<IcsCheckingFixture>(list);
            if (!result)
            {

                throw new Exception("导入失败");
            }
            returnValue = "true";

            return returnValue;
        }

        public string DeleteTransfer(string keyValue)
        {
            string returnValue = string.Empty;
            try
            {
                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 transfer = MsSqlData
                    .Query<IcsCheckingFixtureTransfer>("select top 1 * from IcsCheckingFixtureTransfer with(nolock) where id='" + keyValue + "'")
                    .FirstOrDefault();
                if (transfer == null)
                {
                    throw new Exception("当前检具流转记录不存在");
                }


                var checkFixyure = MsSqlData
                    .Query<IcsCheckingFixture>("select top 1 * from IcsCheckingFixture with(nolock) where CheckFixtureNo='" + transfer.CheckFixtureNo + "'")
                    .FirstOrDefault();
                if (checkFixyure == null)
                {
                    throw new Exception("当前检具档案不存在");
                }


                // //当前检具的最新流转单号
                var transferNew = MsSqlData
                    .Query<IcsCheckingFixtureTransfer>(
                    "select top 1 * from IcsCheckingFixtureTransfer a with(nolock)  where  CheckFixtureNo='" + transfer.CheckFixtureNo + "' and a.IsDelete='N' order by a.MTIME desc")
                    .FirstOrDefault();
                if (transferNew == null)
                {
                    throw new Exception("当前检具流转记录不存在");
                }
                if (transferNew.TransferNo != transfer.TransferNo)
                {
                    throw new Exception("当前检具已经存在最新的流转记录,不能删除之前的记录");
                }

                //软删除
                transfer.IsDelete = "Y";
                transfer.DeleteUser = MUSER;
                transfer.DeleteUserName = MUSERNAME;
                transfer.DeleteTime = dtNow;

                var result = MsSqlData.Update<IcsCheckingFixtureTransfer>(transfer);
                if (!result)
                {
                    returnValue = "删除失败";
                }
                checkFixyure.LastTransferDate = dtNow;
                checkFixyure.Status = transfer.OldStatus;
                checkFixyure.LocationCode = checkFixyure.EATTRIBUTE1;
                result = MsSqlData.Update<IcsCheckingFixture>(checkFixyure);
                if (!result)
                {
                    returnValue = "删除失败";
                }
            }
            catch (Exception ex)
            {
                returnValue = ex.Message;
            }
            return returnValue;
        }

        public DataTable ExportAllTransfer(string CheckFixtureNo, string checkFixtureName, string status, string transferType)
        {
            DataTable dt = new DataTable();
            // var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            //object Figure = GetDecimalDigits();
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;


            #region [SQL]
            string sql = @"	
select
a.*
from
(
select
a.ID
,a.TransferType
,a.TransferNo
,a.TransferUser
,a.TransferUserName
,a.CheckFixtureNo
,b.CheckFixtureName
,b.CustomerItemCode
,b.Useage
,b.Std
,b.Unit
,a.Remark
,a.MUSERName
,a.MTIME
from IcsCheckingFixtureTransfer  a with(nolock)
left join IcsCheckingFixture  b with(nolock) on a.CheckFixtureNo=b.CheckFixtureNo and a.WorkPoint=b.WorkPoint
<5>
--and a.CheckFixtureNo='JY001'
--order by a.MTIME desc
) 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(CheckFixtureNo))
            {
                sql = sql.Replace("<1>", " and a.CheckFixtureNo like '%" + CheckFixtureNo + "%' ");
            }
            else
            {
                sql = sql.Replace("<1>", " ");
            }

            if (!string.IsNullOrWhiteSpace(checkFixtureName))
            {
                sql = sql.Replace("<2>", " and a.CheckFixtureName like '%" + checkFixtureName + "%' ");
            }
            else
            {
                sql = sql.Replace("<2>", " ");
            }

            if (!string.IsNullOrWhiteSpace(status))
            {

                sql = sql.Replace("<3>", " and a.TransferNo ='" + status + "' ");
            }
            else
            {
                sql = sql.Replace("<3>", " ");
            }
            if (!string.IsNullOrWhiteSpace(transferType))
            {

                sql = sql.Replace("<4>", " and a.TransferType ='" + transferType + "' ");
            }
            else
            {
                sql = sql.Replace("<4>", " ");
            }

            sql = sql.Replace("<5>", " where  a.WorkPoint ='" + WorkPoint + "' ");
            return Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
        }

        public IcsCheckingFixture GetCheckingFixtureByNo(string keyValue)
        {
            var returnValue = new IcsCheckingFixture();
            try
            {
                var checkFixyure = MsSqlData
                      .Query<IcsCheckingFixture>("select top 1 * from IcsCheckingFixture with(nolock) where CheckFixtureNo='" + keyValue + "'")
                      .FirstOrDefault();
                if (checkFixyure == null)
                {
                    throw new Exception("当前检具档案不存在");
                }
                returnValue = checkFixyure;
            }
            catch (Exception ex)
            {
                returnValue = new IcsCheckingFixture();
            }
            return returnValue;
        }

        public DataTable GetUserInfo(string keyValue)
        {
            List<DbParameter> parameter = new List<DbParameter>();
            var sql = @"select
a.*
from sys_srm_user a with(nolock)
where 1 = 1
and a.F_Account = '" + keyValue + "' order by a.F_CreatorTime desc ";

            return Repository().FindDataSetBySql(sql.ToString(), parameter.ToArray()).Tables[0];
        }

        public string SaveTransfer(string keyValue)
        {
            string returnValue = string.Empty;

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

            string newStatus = string.Empty;
            try
            {

                string dtPre = DateTime.Now.ToString("yyyyMMdd");
                string sql = @"EXEC Addins_GetSerialCode '" + WorkPoint + "','IcsCheckingFixtureTransfer','TransferNo','" + "LZ" + dtPre + "',3";
                var serialNo = MsSqlData.ExecuteScalar(sql).ToStringExt();

                var model = JsonConvert.DeserializeObject<IcsCheckingFixtureTransferDto>(keyValue);

                var count = MsSqlData.ExecuteScalar(" select count(1) from IcsCheckingFixtureTransfer  with(nolock) where TransferNo='" + serialNo + "'").ToInt();
                if (count > 0)
                {
                    throw new Exception("当前单据已经存在");
                }


              
                //count = MsSqlData.ExecuteScalar(" select count(1) from ICSLocation  with(nolock) where LocationCode='" + model.LocationCode + "'").ToInt();
                //if (count == 0)
                //{
                //    throw new Exception("当前库位不存在");
                //}

                var checkFixyure = MsSqlData
                    .Query<IcsCheckingFixture>("select top 1 * from IcsCheckingFixture with(nolock) where CheckFixtureNo='" + model.CheckFixtureNo + "'")
                    .FirstOrDefault();
                if (checkFixyure == null)
                {
                    throw new Exception("当前检具档案不存在");
                }
                if (checkFixyure.Status == "报废")
                {
                    throw new Exception("当前检具档案已经报废,不能再次操作");
                }

                if (model.TransferType == "领用")
                {
                    if (checkFixyure.Status == "领用")
                    {
                        throw new Exception("当前检具已经被领用,不能再次领用");
                    }
                    if (checkFixyure.VerifyDate.ToDate().AddDays(checkFixyure.VerifyPeriodDay.ToInt()) <DateTime.Now) 
                    {
                        throw new Exception("检验过期，禁止领用！");
                    }
                    checkFixyure.ReceiveUser = model.TransferUserName;
                    newStatus = "领用";
                    checkFixyure.EATTRIBUTE1 = model.EATTRIBUTE1;
                   // checkFixyure.LocationCode = "";
                  
                }
                else if (model.TransferType == "归还")
                {
                    if (checkFixyure.Status == "在库")
                    {
                        throw new Exception("当前检具已经在库,不能归还");
                    }
                    checkFixyure.ReceiveUser = null;
                    newStatus = "在库";
                    checkFixyure.EATTRIBUTE1 = "";
                   // checkFixyure.LocationCode = model.LocationCode;
                  
                }
                else if (model.TransferType == "报废")
                {
                    if (checkFixyure.Status != "在库")
                    {
                        throw new Exception("当前检具不是在库,不能报废");
                    }
                    checkFixyure.ReceiveUser = null;
                    newStatus = "报废";
                   // checkFixyure.EATTRIBUTE1 = checkFixyure.LocationCode;
                  //  checkFixyure.LocationCode = "";
                }
                else
                {
                    newStatus = "在库";
                }
                var entity = ConvertExt.Mapping<IcsCheckingFixtureTransferDto, IcsCheckingFixtureTransfer>(model);
                entity.ID = Guid.NewGuid().ToString();
                entity.TransferNo = serialNo;//单据编码
                entity.OldStatus = checkFixyure.Status;
                entity.NewStatus = newStatus;
                entity.MUSER = MUSER;
                entity.MUSERName = MUSERNAME;
                entity.MTIME = dtNow;
                entity.WorkPoint = WorkPoint;
                entity.IsDelete = "N";
                var result = MsSqlData.Insert<IcsCheckingFixtureTransfer>(entity);
                if (!result)
                {
                    returnValue = "新增失败";
                }

                //更新档案最新流转日期
                checkFixyure.LastTransferDate = dtNow;
                checkFixyure.Status = newStatus;
                result = MsSqlData.Update<IcsCheckingFixture>(checkFixyure);
                if (!result)
                {
                    returnValue = "新增失败";
                }
            }
            catch (Exception ex)
            {
                returnValue = ex.Message;
            }

            return returnValue;
        }

        public DataTable QueryTransferList(string queryJson, ref Pagination pagination)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            //object Figure = GetDecimalDigits();
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;


            #region [SQL]
            string sql = @"	
select
a.*
from
(
select
a.ID
,a.TransferType
,a.TransferNo
,a.TransferUser
,a.TransferUserName
,a.CheckFixtureNo
,b.CheckFixtureName
,b.CustomerItemCode
,b.Useage
,b.Std
,b.Unit
,a.Remark
,a.MUSERName
,a.MTIME
from IcsCheckingFixtureTransfer  a with(nolock)
left join IcsCheckingFixture  b with(nolock) on a.CheckFixtureNo=b.CheckFixtureNo and a.WorkPoint=b.WorkPoint
<10>
and a.IsDelete='N'
--and a.CheckFixtureNo='JY001'
--order by a.MTIME desc
) a
where 1=1

<1>
<2>
<3>
<4>
<5>
<6>
	";
            // 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["CheckFixtureNo"].ToString()))
                {
                    sql = sql.Replace("<1>", " and a.CheckFixtureNo like '%" + queryParam["CheckFixtureNo"].ToString() + "%' ");
                }
                else
                {
                    sql = sql.Replace("<1>", " ");
                }

                if (!string.IsNullOrWhiteSpace(queryParam["CheckFixtureName"].ToString()))
                {
                    sql = sql.Replace("<2>", " and a.CheckFixtureName like '%" + queryParam["CheckFixtureName"].ToString() + "%' ");
                }
                else
                {
                    sql = sql.Replace("<2>", " ");
                }

                if (!string.IsNullOrWhiteSpace(queryParam["TransferNo"].ToString()))
                {

                    sql = sql.Replace("<3>", " and a.TransferNo like '%" + queryParam["TransferNo"].ToString() + "%' ");
                }
                else
                {
                    sql = sql.Replace("<3>", " ");
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TransferType"].ToString()))
                {

                    sql = sql.Replace("<4>", " and a.TransferType ='" + queryParam["TransferType"].ToString() + "' ");
                }
                else
                {
                    sql = sql.Replace("<4>", " ");
                }

                if (!string.IsNullOrWhiteSpace(queryParam["VerifyDateBegin"].ToString()))
                {

                    sql = sql.Replace("<5>", " and a.MTIME>='" + queryParam["VerifyDateBegin"].ToString() + " 00:00:00' ");
                }
                else
                {
                    sql = sql.Replace("<5>", " ");
                }

                if (!string.IsNullOrWhiteSpace(queryParam["VerifyDateEnd"].ToString()))
                {

                    sql = sql.Replace("<6>", " and a.MTIME<='" + queryParam["VerifyDateEnd"].ToString() + " 23:59:59' ");
                }
                else
                {
                    sql = sql.Replace("<6>", " ");
                }
            }
            //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 + "'";
            //}
            sql = sql.Replace("<10>", " where  a.WorkPoint ='" + WorkPoint + "' ");
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref pagination);
        }

        public string SaveCheckingFixture(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<IcsCheckingFixtureDto>(keyValue);

                int count = 0;
                count = MsSqlData.ExecuteScalar(" select count(1) from ICSLocation  with(nolock) where LocationCode='" + model.LocationCode + "'").ToInt();
                if (count == 0)
                {
                    throw new Exception("当前库位不存在");
                }

                if (string.IsNullOrEmpty(model.ID))
                {
                    //新增
                     count = MsSqlData.ExecuteScalar(" select count(1) from IcsCheckingFixture  with(nolock) where CheckFixtureNo='" + model.CheckFixtureNo + "'").ToInt();
                    if (count > 0)
                    {
                        throw new Exception("当前代码已经存在");
                    }
                    var entity = ConvertExt.Mapping<IcsCheckingFixtureDto, IcsCheckingFixture>(model);

                    entity.ID = Guid.NewGuid().ToString();
                    entity.Status = "在库";
                    entity.MUSER = MUSER;
                    entity.MUSERName = MUSERNAME;
                    entity.MTIME = DateTime.Now;
                    entity.WorkPoint = WorkPoint;

                    var result = MsSqlData.Insert<IcsCheckingFixture>(entity);
                    if (!result)
                    {
                        returnValue = "新增失败";
                    }
                }
                else
                { //修改

                    var entity = MsSqlData.Get<IcsCheckingFixture>(model.ID);
                    if (entity == null)
                    {
                        throw new Exception("当前数据不存在");
                    }
                    ConvertExt.Mapping<IcsCheckingFixtureDto, IcsCheckingFixture>(model, entity);
                    //entity.MUSER = MUSER;
                    //entity.MUSERName = MUSERNAME;
                    //  entity.MTIME = DateTime.Now;
                    var result = MsSqlData.Update<IcsCheckingFixture>(entity);
                    if (!result)
                    {
                        returnValue = "修改失败";
                    }
                }

            }
            catch (Exception ex)
            {
                returnValue = ex.Message;
            }

            return returnValue;
        }
    }
}