/// <summary>
/// 插入信息(单表)
/// </summary>
/// <param name="">信息</param>
/// <returns>插入行数</returns>
public int Insert(ProcessInfo model)
{
int count = 0;
List <DataParameter> parameters = new List <DataParameter>();
try
{
using (IDataSession session = AppDataFactory.CreateMainSession())
{
//插入基本信息
count = session.Insert <ProcessInfo>(model);
string sql = "DELETE FROM T_FP_EQUIPMENTREF WHERE PRID = @PRID";
parameters.Add(new DataParameter {
ParameterName = "PRID", DataType = DbType.String, Value = model.PID
});
session.ExecuteSqlScalar(sql, parameters.ToArray());
if (model.Details != null)
{
foreach (EquipmentRef detail in model.Details)
{
EquipmentRef eref = new EquipmentRef();
eref.PID = Guid.NewGuid().ToString();
eref.EQID = detail.EQID;
eref.PRID = model.PID;
session.Insert <EquipmentRef>(eref);
}
}
string sql2 = "DELETE FROM T_FP_STATIONREF WHERE PRID = @PRID";
session.ExecuteSqlScalar(sql2, parameters.ToArray());
if (model.Details2 != null)
{
foreach (StationRef detail in model.Details2)
{
StationRef eref = new StationRef();
eref.PID = Guid.NewGuid().ToString();
eref.STID = detail.STID;
eref.PRID = model.PID;
session.Insert <StationRef>(eref);
}
}
}
return(count);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 更新信息
/// </summary>
/// <param name=""></param>
/// <returns>更新行数</returns>
public int Update(WorkGroup model)
{
int count = 0;
List <DataParameter> parameters = new List <DataParameter>();
try
{
using (IDataSession session = AppDataFactory.CreateMainSession())
{
//更新基本信息
count = session.Update <WorkGroup>(model);
string sql = "DELETE FROM T_FP_WORKGROUPREF WHERE WOID = @WOID";
parameters.Add(new DataParameter {
ParameterName = "WOID", DataType = DbType.String, Value = model.PID
});
session.ExecuteSqlScalar(sql, parameters.ToArray());
foreach (WorkGroupRef detail in model.Details)
{
WorkGroupRef workGroupRef = new WorkGroupRef();
workGroupRef.PID = Guid.NewGuid().ToString();
workGroupRef.PEID = detail.PEID;
workGroupRef.WOID = model.PID;
session.Insert <WorkGroupRef>(workGroupRef);
}
}
return(count);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 判断名称是否存在
/// </summary>
/// <param name="info"></param>
/// <returns>true:已存在；fasel:不存在。</returns>
public bool ExistsWHSite(WHSite model)
{
StringBuilder sqlBuilder = new StringBuilder();
StringBuilder whereBuilder = new StringBuilder();
List <DataParameter> parameters = new List <DataParameter>();
int count = 0;
try
{
sqlBuilder.Append("SELECT COUNT(0) FROM T_WH_Site");
sqlBuilder.Append(" WHERE ID <> @ID AND WHID = @WHID AND (Code = @Code or Description = @Description)");
parameters.Add(new DataParameter {
ParameterName = "ID", DataType = DbType.String, Value = model.ID
});
parameters.Add(new DataParameter {
ParameterName = "WHID", DataType = DbType.String, Value = model.WHID
});
parameters.Add(new DataParameter {
ParameterName = "Code", DataType = DbType.String, Value = model.Code
});
parameters.Add(new DataParameter {
ParameterName = "Description", DataType = DbType.String, Value = model.Description
});
using (IDataSession session = AppDataFactory.CreateMainSession())
{
count = Convert.ToInt32(session.ExecuteSqlScalar(sqlBuilder.ToString(), parameters.ToArray()));
}
return(count > 0);
}
catch (Exception ex)
{
throw ex;
}
}
public string GetBatchNumberByBarCode(string barCode)
{
object value = null;
string batchNumber = null;
string sql = "select BatchNumber from T_FP_GoodPackingForm where DetailBarCode = @BarCode";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
value = session.ExecuteSqlScalar(sql, new DataParameter("BarCode", barCode));
}
if (value != null)
{
batchNumber = value.ToString();
}
return(batchNumber);
}
/// <summary>
/// 获取货品最新唯一识别码
/// </summary>
/// <param name="info">货品信息</param>
public string GetNewIDCodeSeq(WHMat info)
{
string no = null;
object value = null;
string sql = "select max(Seq) from T_WH_MatIDCode where MatID = @MatID and BuildDate = @BuildDate";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
sql = this.ChangeSqlByDB(sql, session);
value = session.ExecuteSqlScalar(sql, new DataParameter("MatID", info.ID)
, new DataParameter("BuildDate", DateTime.Now.ToString("yyyyMMdd")));
}
if (value != null && value != System.DBNull.Value)
{
no = value.ToString();
}
return(no);
}
/// <summary>
/// 判断条码是否已入库
/// </summary>
/// <param name="matBarCode"></param>
/// <returns></returns>
public bool ExistsMatBarCode(string matBarCode)
{
bool exists = false;
string sql = null;
int count = 0;
sql = "select count(*) from T_WH_MatAmount where MatBarCode = @MatBarCode";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
count = int.Parse(session.ExecuteSqlScalar(sql, new DataParameter("MatBarCode", matBarCode)).ToString());
}
if (count > 0)
{
exists = true;
}
return(exists);
}
/// <summary>
/// 获取最大入库单号
/// </summary>
/// <returns></returns>
public string GetMaxBillNO()
{
string no = null;
object value = null;
string sql = "select max(BillNO) from T_WH_MoveStockBill Where BillDate >= @StartDate and BillDate <=@EndDate";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
value = session.ExecuteSqlScalar(sql
, new DataParameter("StartDate", DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd")))
, new DataParameter("EndDate", DateTime.Now));
}
if (value != null && value != System.DBNull.Value)
{
no = value.ToString();
}
return(no);
}
/// <summary>
/// 获取数据库时间
/// </summary>
/// <returns>数据库时间</returns>
public DateTime GetDbNowTime()
{
DateTime nowTime;
string sql = null;
try
{
sql = "SELECT GETDATE()";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
nowTime = (DateTime)session.ExecuteSqlScalar(sql);
}
return(nowTime);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 判断入库单号是否存在
/// </summary>
/// <param name="billNO">入库单号</param>
/// <returns>true:存在;false:不存在</returns>
public bool ExistsBillNO(string billNO)
{
string sql = null;
object value = null;
sql = "select BillNO from T_WH_MoveStockBill where BillNO = @BillNO";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
value = session.ExecuteSqlScalar(sql, new DataParameter("BillNO", billNO));
}
if (value == null || value == System.DBNull.Value)
{
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// 判断是否被使用
/// </summary>
/// <param name="info">仓位</param>
/// <returns>true:已使用;false:未使用</returns>
public bool IsUse(WHSite info)
{
int count = 0;
string sql = null;
sql = "select count(ID) from T_WH_MatAmount where SaveSite = @SaveSite";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
count = int.Parse(session.ExecuteSqlScalar(sql, new DataParameter("SaveSite", info.ID)).ToString());
}
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 判断是否被使用
/// </summary>
/// <param name="info">货品</param>
/// <returns>true:已使用;false:未使用</returns>
public bool IsUse(WHMat info)
{
int count = 0;
string sql = null;
sql = "select count(ID) from T_WH_InStockDetail where MatID = @MatID";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
count = int.Parse(session.ExecuteSqlScalar(sql, new DataParameter("MatID", info.ID)).ToString());
}
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 判断角色名称是否存在
/// </summary>
/// <param name="role">角色信息</param>
/// <returns>true:已存在；fasel:不存在。</returns>
public bool ExistsRole(RoleInfo role)
{
string roleID = "";
int count = 0;
string sql = null;
try
{
if (string.IsNullOrEmpty(role.RoleID) == false)
{
roleID = role.RoleID;
}
sql = "SELECT COUNT(*) FROM T_ROLE WHERE ROLEID <> @ROLEID AND ROLEDESC=@ROLEDESC";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
sql = this.ChangeSqlByDB(sql, session);
count = (int)session.ExecuteSqlScalar(sql, new DataParameter {
ParameterName = "ROLEID", Value = roleID
}
, new DataParameter {
ParameterName = "ROLEDESC", Value = role.RoleDESC
});
}
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// 判断组织机构编号是否重复
/// </summary>
/// <param name="orga">组织机构信息</param>
/// <returns>true:已存在；fasel:不存在。</returns>
public bool ExistsOrga(Orgaization orga)
{
string organID = "";
int count = 0;
string sql = null;
try
{
if (string.IsNullOrEmpty(orga.OrganID) == false)
{
organID = orga.OrganID;
}
sql = "SELECT COUNT(*) FROM T_ORGANIZATION WHERE OrganID <> @OrganID OR ORGANDESC=@ORGANDESC";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
count = (int)session.ExecuteSqlScalar(sql, new DataParameter {
ParameterName = "OrganID", Value = organID
}
, new DataParameter {
ParameterName = "ORGANDESC", Value = orga.OrganDESC
});
}
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// 判断名称或者编号是否存在
/// </summary>
/// <param name="info"></param>
/// <returns>true:已存在；fasel:不存在。</returns>
public bool ExistsProcessInfo(ProcessInfo model)
{
StringBuilder sqlBuilder = new StringBuilder();
StringBuilder whereBuilder = new StringBuilder();
List <DataParameter> parameters = new List <DataParameter>();
int count = 0;
try
{
sqlBuilder.Append("SELECT COUNT(0) FROM T_FP_PROCESSINFO");
whereBuilder.Append(" AND PID <> @PID ");
parameters.Add(new DataParameter {
ParameterName = "PID", DataType = DbType.String, Value = model.PID
});
if (!string.IsNullOrEmpty(model.PCODE))
{
whereBuilder.Append(" AND (PCODE = @PCODE OR PNAME = @PNAME)");
parameters.Add(new DataParameter {
ParameterName = "PCODE", DataType = DbType.String, Value = model.PCODE
});
parameters.Add(new DataParameter {
ParameterName = "PNAME", DataType = DbType.String, Value = model.PNAME
});
}
if (whereBuilder.Length > 0)
{
sqlBuilder.Append(" WHERE " + whereBuilder.ToString().Substring(4));
}
using (IDataSession session = AppDataFactory.CreateMainSession())
{
count = Convert.ToInt32(session.ExecuteSqlScalar(sqlBuilder.ToString(), parameters.ToArray()));
}
return(count > 0);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 产品删除时校验是否在产品计划使用过
/// </summary>
/// <param name="pid"></param>
/// <returns>true:已存在；fasel:不存在。</returns>
public bool HasProducePlan(string pid)
{
StringBuilder sqlBuilder = new StringBuilder();
List <DataParameter> parameters = new List <DataParameter>();
int count = 0;
try
{
sqlBuilder.Append("SELECT COUNT(0) FROM T_FP_PRODUCEPLAN WHERE PRODUCTIONID=@PRODUCTIONID");
parameters.Add(new DataParameter {
ParameterName = "PRODUCTIONID", DataType = DbType.String, Value = pid
});
using (IDataSession session = AppDataFactory.CreateMainSession())
{
count = Convert.ToInt32(session.ExecuteSqlScalar(sqlBuilder.ToString(), parameters.ToArray()));
}
return(count > 0);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 判断名称是否存在
/// </summary>
/// <param name="info"></param>
/// <returns>true:已存在；fasel:不存在。</returns>
public bool ExistsWHOutMode(WHOutMode model)
{
StringBuilder sqlBuilder = new StringBuilder();
StringBuilder whereBuilder = new StringBuilder();
List <DataParameter> parameters = new List <DataParameter>();
int count = 0;
try
{
sqlBuilder.Append("SELECT COUNT(0) FROM T_WH_OutMode");
whereBuilder.Append(" AND ID <> @ID ");
parameters.Add(new DataParameter {
ParameterName = "ID", DataType = DbType.String, Value = model.ID
});
if (!string.IsNullOrEmpty(model.Description))
{
whereBuilder.Append(" AND Description = @Description ");
parameters.Add(new DataParameter {
ParameterName = "Description", DataType = DbType.String, Value = model.Description
});
}
if (whereBuilder.Length > 0)
{
sqlBuilder.Append(" WHERE " + whereBuilder.ToString().Substring(4));
}
using (IDataSession session = AppDataFactory.CreateMainSession())
{
string sql = this.ChangeSqlByDB(sqlBuilder.ToString(), session);
count = Convert.ToInt32(session.ExecuteSqlScalar(sql, parameters.ToArray()));
}
return(count > 0);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 判断编号是否存在
/// </summary>
/// <param name="info"></param>
/// <returns>true:已存在；fasel:不存在。</returns>
public bool Exists(VDPosition model)
{
StringBuilder sqlBuilder = new StringBuilder();
StringBuilder whereBuilder = new StringBuilder();
List <DataParameter> parameters = new List <DataParameter>();
int count = 0;
try
{
sqlBuilder.Append("SELECT COUNT(0) FROM T_VD_Position");
whereBuilder.Append(" AND ID <> @ID ");
parameters.Add(new DataParameter {
ParameterName = "ID", DataType = DbType.String, Value = model.ID
});
if (!string.IsNullOrEmpty(model.PositionCode))
{
whereBuilder.Append(" AND PositionCode = @PositionCode ");
parameters.Add(new DataParameter {
ParameterName = "PositionCode", DataType = DbType.String, Value = model.PositionCode
});
}
if (whereBuilder.Length > 0)
{
sqlBuilder.Append(" WHERE " + whereBuilder.ToString().Substring(4));
}
using (IDataSession session = AppDataFactory.CreateMainSession())
{
count = Convert.ToInt32(session.ExecuteSqlScalar(sqlBuilder.ToString(), parameters.ToArray()));
}
return(count > 0);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 判断公告标题是否存在
/// </summary>
/// <param name="info">信息</param>
/// <returns>true@已存在；fasel@不存在。</returns>
public bool ExistsNotice(NoticeInfo notice)
{
string noticeID = "";
int count = 0;
string sql = null;
try
{
if (string.IsNullOrEmpty(notice.NoticeID) == false)
{
noticeID = notice.NoticeID;
}
sql = "SELECT COUNT(*) FROM T_QM_NOTICE WHERE NOTICEID <> @NOTICEID AND NOTICETITLE=@NOTICETITLE";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
count = Convert.ToInt32(session.ExecuteSqlScalar(sql, new DataParameter("NOTICEID", noticeID), new DataParameter {
ParameterName = "NOTICETITLE", Value = notice.NoticeTitle
}));
}
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// 保存采集数据
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
public bool saveItemAndCalResultData(string dataStr, string resDataStr)
{
int count = 0;
List <InspectItemData> itemList = JsonConvertHelper.DeserializeObject <List <InspectItemData> >(dataStr);
List <InspectRealTimeItemData> realTimeItemList = JsonConvertHelper.DeserializeObject <List <InspectRealTimeItemData> >(dataStr);
List <InspectResultData> culResultList = JsonConvertHelper.DeserializeObject <List <InspectResultData> >(resDataStr);
//用于事务处理
using (IDataSession session = AppDataFactory.CreateMainSession())
{
try
{
//插入基本信息
count = session.Insert <InspectItemData>(itemList);
foreach (InspectRealTimeItemData irti in realTimeItemList)
{
List <DataParameter> dataParameter = new List <DataParameter>();
dataParameter.Add(new DataParameter {
ParameterName = "DeviceSN", DataType = DbType.String, Value = irti.DeviceSN
});
dataParameter.Add(new DataParameter {
ParameterName = "ItemCode", DataType = DbType.String, Value = irti.ItemCode
});
//更新实时记录表
int updCount = Convert.ToInt32(session.ExecuteSqlScalar("select count(0) from inspectrealtimedata where DeviceCode = @DeviceSN and ItemCode = @ItemCode ", dataParameter.ToArray()));
//如果返回数据为0说明记录不存在则插入一条新的记录
if (updCount == 0)
{
session.Insert(irti);
}
else
{
//更新值
dataParameter.Add(new DataParameter {
ParameterName = "InspectTime", DataType = DbType.DateTime, Value = irti.InspectTime
});
dataParameter.Add(new DataParameter {
ParameterName = "InspectData", DataType = DbType.String, Value = irti.InspectData
});
dataParameter.Add(new DataParameter {
ParameterName = "UpdateTime", DataType = DbType.DateTime, Value = irti.UpdateTime
});
session.ExecuteSqlScalar("update inspectrealtimedata set InspectTime = @InspectTime, InspectData = @InspectData, UpdateTime = @UpdateTime where DeviceCode = @DeviceSN and ItemCode = @ItemCode ", dataParameter.ToArray());
}
}
//测试时使用否则插入同一张表中数据会主键冲突
foreach (InspectResultData ird in culResultList)
{
List <DataParameter> dataParameter = new List <DataParameter>();
dataParameter.Add(new DataParameter {
ParameterName = "ID", DataType = DbType.String, Value = ird.ID
});
int dataCount = Convert.ToInt32(session.ExecuteSqlScalar("select count(0) from inspectcalcresult where ID = @ID ", dataParameter.ToArray()));
//如果返回数据为0说明记录不存在则插入一条新的记录
if (dataCount == 0)
{
count += session.Insert(ird);
}
else
{
count += session.Update(ird);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
//保存出错时回滚数据
session.RollbackTs();
return(false);
}
}
return(true);
}
