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.
974 lines
36 KiB
974 lines
36 KiB
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;
|
|
}
|
|
}
|
|
}
|