public static void CreateTable(string prefixTable)
{
using (BaseAccess access = new BaseAccess(true))
{
try
{
access.Open();
access.BeginTransaction();
//创建缓存托盘表
access.ExecuteNonQuery(GetCacheStackTraySql(prefixTable));
//创建托盘详情表
access.ExecuteNonQuery(GetStackTrayFlowSql(prefixTable));
//创建托盘表
access.ExecuteNonQuery(GetStackTraysSql(prefixTable));
//创建注册托盘表
//access.ExecuteNonQuery(GetRegisterTraySql(prefixTable));
//创建用户事件日志表
access.ExecuteNonQuery(GetUserEventLogSql(prefixTable));
access.Commit();
}
catch (Exception ex)
{
access.Rollback();
throw new Exception("创建表失败：" + ex.Message);
}
finally
{
access.Close();
}
}
}
/// <summary>
/// 更新缓存InStack托盘码（已叠盘）
/// </summary>
/// <param name="trayCode">托盘条码</param>
/// <param name="access">数据库实例对象</param>
/// <returns>执行结果。1：未获取到托盘批次。0：缓存成功</returns>
//public void UpdateCacheTable(BaseAccess access, string trayCode, string oldTrayCode)
//{
// BaseCondition condition = new Column(CacheStackTray.BARCODE).Equal(oldTrayCode);
// CacheStackTray trayData = access.Select<CacheStackTray>(condition).First();
// trayData.Barcode = trayCode;
// trayData.UpdateTime = DateTime.Now;
// //trayData.Flag = this.stackNumber;
// string updateSql = string.Format("UPDATE {0} SET Barcode='{1}',UpdateTime='{2}' WHERE Id='{3}'", lineDevice.PrefixTable + "CacheStackTray", trayData.Barcode, trayData.UpdateTime, trayData.Id);
// access.ExecuteNonQuery(updateSql);
// //access.Update(trayData, new string[] { CacheStackTray.ID }, CacheStackTray.BARCODE, CacheStackTray.UPDATE_TIME);
//}
/// <summary>
/// 删除缓存托盘
/// </summary>
/// <param name="trayCode"></param>
public void DeleteCacheTable(string trayCode)
{
using (BaseAccess access = new BaseAccess())
{
try
{
string deleteSql = string.Format("DELETE FROM {0} WHERE Barcode='{1}'", lineDevice.PrefixTable + "CacheStackTray", trayCode);
access.ExecuteNonQuery(deleteSql);
//BaseCondition condition = new Column(CacheStackTray.BARCODE).Equal(trayCode);
//access.Delete<CacheStackTray>(condition);
int count = trayCodeList.Count;
for (int i = count - 1; i > -1; i--)
{
if (trayCodeList[i].Barcode == trayCode)
{
trayCodeList.RemoveAt(i);
}
}
}
catch (Exception ex)
{
PrintInfo.I(ex.Message);
Console.WriteLine(ex.Message);
}
}
}
/// <summary>
/// 修改缓存托盘集合
/// </summary>
/// <param name="cacheTrayList"></param>
public void UpdateCacheTableList(List <CacheStackTray> cacheTrayList)
{
using (BaseAccess access = new BaseAccess(true))
{
try
{
access.Open();
access.BeginTransaction();
foreach (CacheStackTray entity in cacheTrayList)
{
string updateSql = string.Format("UPDATE {0} SET Barcode='{1}',Batch={2} WHERE Id ={3}", lineDevice.PrefixTable + "CacheStackTray", entity.Barcode, entity.Batch, entity.Id);
access.ExecuteNonQuery(updateSql);
//access.Update(entity, new string[] { CacheStackTray.ID }, CacheStackTray.BARCODE, CacheStackTray.BATCH);
}
access.Commit();
}
catch (Exception ex)
{
access.Rollback();
PrintInfo.I(ex.Message);
Console.WriteLine(ex.Message);
}
finally
{
access.Close();
}
}
}
/// <summary>
/// 清除缓存数据
/// </summary>
public void ClearCacheData()
{
string strSQL = string.Format("DELETE FROM [" + this.lineDevice.PrefixTable + "CacheStackTray] WHERE [Flag]={0}", this.stackNumber);
bool success = false;
while (!success)
{
using (BaseAccess access = new BaseAccess())
{
try
{
access.ExecuteNonQuery(strSQL);
trayCodeList.Clear();
this.mixBatch = false;
success = true;
}
catch (Exception ex)
{
PrintInfo.I(ex.Message);
}
}
if (!success)
{
Thread.Sleep(2000);
}
}
}
/// <summary>
/// 将托盘存入缓存库
/// </summary>
/// <param name="trayCode">托盘条码</param>
/// <param name="access">数据库实例对象</param>
/// <returns>执行结果。1：未获取到托盘批次。0：缓存成功</returns>
public int InsertCacheTable(BaseAccess access, string[] trayCode, int inStack)
{
int fls = 0;
for (int i = 0; i < trayCode.Length; i++)
{
string batch = businessControl.GetTrayBatch(trayCode[i]);
ProductProcessDataEntity entity;
if (string.IsNullOrEmpty(batch))
{
// 未获取到托盘的批次
entity = new ProductProcessDataEntity()
{
TrayCode = trayCode[i],
CreateTime = DateTime.Now,
State = "托盘到达",
MaterialData = "未获取到批次",
ExceptionInfo = "未获取到批次"
};
//WriteProductDateBase(Writers["scanCodeFeedbackAddr"], entity);
WriteProductDateBase(device, entity);
fls = 1;
}
else
{
entity = new ProductProcessDataEntity()
{
TrayCode = trayCode[i],
CreateTime = DateTime.Now,
State = "托盘批次",
MaterialData = batch
};
//WriteProductDateBase(Writers["scanCodeFeedbackAddr"], entity);
WriteProductDateBase(device, entity);
CacheStackTray trayData = new CacheStackTray();
trayData.Barcode = trayCode[i];
trayData.Batch = batch;
trayData.InStack = inStack;
trayData.UpdateTime = DateTime.Now;
trayData.Flag = this.stackNumber;
string insertSql = string.Format("INSERT INTO {0} (Barcode,Batch,InStack,Flag,UpdateTime) OUTPUT INSERTED.Id VALUES ('{1}','{2}','{3}','{4}','{5}')",
lineDevice.PrefixTable + "CacheStackTray",
trayData.Barcode,
trayData.Batch,
trayData.InStack,
trayData.Flag,
trayData.UpdateTime);
access.ExecuteNonQuery(insertSql);
//access.Insert(trayData, CacheStackTray.BARCODE, CacheStackTray.BATCH, CacheStackTray.IN_STACK, CacheStackTray.UPDATE_TIME, CacheStackTray.FLAG);
// 将托盘加入集合
trayCodeList.Add(trayData);
fls = 0;
}
}
return(fls);
}
/// <summary>
/// 更新上一缓存InStack为2（已叠盘）
/// </summary>
/// <param name="trayCode">托盘条码</param>
/// <param name="access">数据库实例对象</param>
/// <returns>执行结果。1：未获取到托盘批次。0：缓存成功</returns>
private void UpdateCacheTable(BaseAccess access, string trayCode, int inStack)
{
CacheStackTray trayData = new CacheStackTray();
trayData.Barcode = trayCode;
trayData.InStack = inStack;
trayData.UpdateTime = DateTime.Now;
trayData.Flag = this.stackNumber;
string updateSql = string.Format("UPDATE {0} SET InStack={1},UpdateTime='{2}' WHERE Barcode='{3}' AND Flag={4}", lineDevice.PrefixTable + "CacheStackTray", trayData.InStack, trayData.UpdateTime, trayData.Barcode, trayData.Flag);
access.ExecuteNonQuery(updateSql);
//access.Update(trayData, new string[] { CacheStackTray.BARCODE, CacheStackTray.FLAG }, CacheStackTray.IN_STACK, CacheStackTray.UPDATE_TIME);
}
/// <summary>
/// 删除缓存托盘数据
/// </summary>
/// <param name="access">数据库对象实例</param>
private void DeleteCacheData(BaseAccess access, string delFlag)
{
string whereStr = string.Empty;
if (delFlag == "1")
{
whereStr = string.Format(" WHERE [Flag]={0}", this.stackNumber);
}
else if (delFlag == "2")
{
whereStr = string.Format(" WHERE [InStack] = '2' AND [Flag]={0}", this.stackNumber);
}
string strSQL = "DELETE FROM [" + lineDevice.PrefixTable + "CacheStackTray]" + whereStr;
int count = access.ExecuteNonQuery(strSQL);
}
private static void RecordUserLog(string message)
{
string unKnownUser = "******";
using (BaseAccess access = new BaseAccess())
{
try
{
string strSQL = string.Format("INSERT INTO [{0}] ([Account],[HandleEvent]) VALUES('{1}','{2}')", tableName, unKnownUser, message);
access.ExecuteNonQuery(strSQL);
}
catch (System.Data.Common.DbException ex)
{
Log.Error(ex.StackTrace);
}
}
}
/// <summary>
/// 保存堆叠的托盘
/// </summary>
/// <param name="trayCodeList">托盘条码集合</param>
/// <param name="access">数据库对象实例</param>
private void SaveStackTrays(List <CacheStackTray> trayCodeList, BaseAccess access, string flag)
{
// 按照允许叠盘数量依次增加，根据实际情况而定，其中1盘在最上面,就不需要翻转
// 翻转保存
// trayCodeList.Reverse();
long traysId = SelectTraysId(trayCodeList[trayCodeList.Count - int.Parse(flag)].Barcode, access);
int trayIndex = 0;
foreach (var item in trayCodeList)
{
if (flag == "2" && item.InStack == 1)
{
continue;
}
StackTrays tray = new StackTrays();
//tray.Id = Guid.NewGuid();
tray.TraysId = traysId;
tray.Barcode = item.Barcode;
tray.Batch = item.Batch;
tray.TrayIndex = ++trayIndex;
tray.UpdateTime = DateTime.Now;
string insertSql = string.Format("INSERT INTO {0} (TraysId,Barcode,Batch,TrayIndex,UpdateTime) OUTPUT INSERTED.Id VALUES ('{1}','{2}','{3}','{4}','{5}')",
lineDevice.PrefixTable + "StackTrays",
tray.TraysId,
tray.Barcode,
tray.Batch,
tray.TrayIndex,
tray.UpdateTime);
access.ExecuteNonQuery(insertSql);
//int count = access.Insert(tray, StackTrays.TRAYS_ID, StackTrays.BARCODE, StackTrayFlow.BATCH, StackTrays.TRAY_INDEX, StackTrayFlow.UPDATE_TIME);
ProductProcessDataEntity entity = new ProductProcessDataEntity()
{
TrayCode = item.Barcode,
CreateTime = DateTime.Now,
State = "叠盘完成",
MaterialData = item.Batch
};
//WriteProductDateBase(Writers["scanCodeFeedbackAddr"], entity);
WriteProductDateBase(device, entity);
}
}
/// <summary>
/// 删除历史托盘数据
/// </summary>
/// <param name="trayId"></param>
/// <returns></returns>
public bool DeleteStackTray(int trayId)
{
using (BaseAccess access = new BaseAccess())
{
try
{
string deleteSql = string.Format("DELETE FROM {0} WHERE Id='{1}'", lineDevice.PrefixTable + "StackTrays", trayId);
access.ExecuteNonQuery(deleteSql);
//BaseCondition condition = new Column(StackTrays.ID).Equal(trayId);
//access.Delete<StackTrays>(condition);
}
catch (Exception ex)
{
PrintInfo.I(ex.Message);
Console.WriteLine(ex.Message);
}
}
return(true);
}
public bool UpdateStackTrays(StackTrays stackTrays)
{
using (BaseAccess access = new BaseAccess())
{
try
{
string updateSql = string.Format("UPDATE {0} SET Barcode='{1}',BATCH='{2}' WHERE Id='{3}'", lineDevice.PrefixTable + "StackTrays", stackTrays.Barcode, stackTrays.Batch, stackTrays.Id);
access.ExecuteNonQuery(updateSql);
//access.Update(stackTrays, new string[] { StackTrays.ID }, StackTrays.BARCODE, StackTrays.BATCH);
return(true);
}
catch (Exception ex)
{
PrintInfo.I(ex.Message);
Console.WriteLine(ex.Message);
return(false);
}
}
}
/// <summary>
/// 保存最下面托盘的信息
/// </summary>
/// <param name="flowTray">最下面托盘信息</param>
/// <param name="access">数据库对象</param>
private void SaveStackFlowTray(CacheStackTray flowTray, BaseAccess access)
{
UpdateUsed(flowTray.Barcode, access);
StackTrayFlow _flowTray = new StackTrayFlow();
_flowTray.Barcode = flowTray.Barcode;
// 通过托盘条码查询得到
_flowTray.Batch = flowTray.Batch;
_flowTray.UpdateTime = DateTime.Now;
_flowTray.Used = 0;
string insertSql = string.Format("INSERT INTO {0} (Barcode,Batch,Used,UpdateTime) OUTPUT INSERTED.Id VALUES ('{1}','{2}','{3}','{4}')",
lineDevice.PrefixTable + "StackTrayFlow",
_flowTray.Barcode,
_flowTray.Batch,
_flowTray.Used,
_flowTray.UpdateTime);
access.ExecuteNonQuery(insertSql);
//int count = access.Insert(_flowTray, StackTrayFlow.BARCODE, StackTrayFlow.BATCH, StackTrayFlow.UPDATE_TIME, StackTrayFlow.USED);
}
private void UpdateUsed(string trayCode, BaseAccess access)
{
string strSQL = string.Format("UPDATE [" + lineDevice.PrefixTable + "StackTrayFlow] SET [Used]=1 WHERE [Barcode]='{0}' AND [Used]=0", trayCode);
access.ExecuteNonQuery(strSQL);
}