//新建根物料模板
public string CreateTemplet(string templetName, string creater)
{
string tmpId = "";
int result = 0;
string sql = null;
DbConnection connection = DbUtilities.GetConnection();
DbTransaction transaction = connection.BeginTransaction();
using (DbCommand command = connection.CreateCommand())
{
command.Transaction = transaction;
//检查模板名称是否重复
/* 李爱军要求不检查重复 20180930
* sql = $"SELECT COUNT(*) FROM TmpInfo WHERE TmpNm = '{templetName}' ";
* command.CommandText = sql;
* try
* {
* result = Convert.ToInt32(command.ExecuteScalar());
* }
* catch (Exception e)
* {
* log.Error(string.Format($"Select from table TmpInfo error!\nsql[{sql}]\nError[{e.StackTrace}]"));
* connection.Close();
* throw;
* }
*
* if (result > 0)
* {
* log.Error(string.Format($"模板名称重复!Name=[{templetName}]\nsql[{sql}]\n"));
* connection.Close();
* throw new Exception("模板名称重复!");
* }
*/
//登记模板信息
SeqNo seqNo = new SeqNo();
tmpId = seqNo.GetBaseSeqNo();
sql = $"INSERT INTO TmpInfo (TmpId, TmpNm, Root, LockCount, CrtDate, Crter, EditLock, DCM) VALUES ({tmpId}, '{templetName}', '1', 0, '{DateTime.Now}', '{creater}', 0, 0) ";
command.CommandText = sql;
try
{
result = command.ExecuteNonQuery();
}
catch (Exception e)
{
log.Error(string.Format($"Insert table TmpInfo error!\nsql[{sql}]\nError[{e.StackTrace}]"));
transaction.Rollback();
connection.Close();
throw;
}
if (result == 0)
{
log.Error(string.Format($"插入TmpInfo记录数为0\nsql[{sql}]\n"));
transaction.Rollback();
connection.Close();
throw new Exception("插入TmpInfo记录数为0!");
}
//登记根物料模板表Relation,TmpId='99',旧系统为'root'
sql = $"INSERT INTO Relation (pid, TmpId, CTmpId, CTmpNm, CTmpNum, LockFlag, CrtDate, Crter, rlSeqNo) VALUES (99, 99, {tmpId}, '{templetName}',0, 0, '{DateTime.Now}', '{creater}', 0) ";
command.CommandText = sql;
try
{
result = command.ExecuteNonQuery();
}
catch (Exception e)
{
log.Error(string.Format($"Insert table Relation error!\nsql[{sql}]\nError[{e.StackTrace}]"));
transaction.Rollback();
connection.Close();
throw;
}
if (result == 0)
{
log.Error(string.Format($"Insert table Relation 0 record!\nsql[{sql}]\n"));
transaction.Rollback();
connection.Close();
throw new Exception("Insert table Relation 0 record!");
}
}
transaction.Commit();
connection.Close();
return(tmpId);
}
//新建物料信息(非根物料节点,无参考模板,上送父模板ID和新模板名称)
public string CreateTemplet(long parentTempletId, string templetName, long pId, string creater)
{
int result = 0;
int rlSeqNo = -1;
string sql = null;
string tmpId = "";
DbConnection connection = DbUtilities.GetConnection();
DbTransaction transaction = connection.BeginTransaction();
using (DbCommand command = connection.CreateCommand())
{
command.Transaction = transaction;
//检查有无重复模板名称
/* 李爱军要求不检查重复 20180930
* sql = $"SELECT COUNT(*) FROM TmpInfo WHERE TmpNm = '{templetName}' ";
* command.CommandText = sql;
* try
* {
* result = Convert.ToInt32( command.ExecuteScalar());
* }
* catch (Exception e)
* {
* log.Error(string.Format($"Select from table TmpInfo error!\nsql[{sql}]\nError[{e.StackTrace}]"));
* connection.Close();
* throw;
* }
*
* if (result > 0)
* {
* log.Error(string.Format($"模板名称重复!Name=[{templetName}]\nsql[{sql}]\n"));
* connection.Close();
* throw new Exception("模板名称重复!");
* }
*/
//检查父模板是否存在
sql = $"SELECT COUNT(*) FROM TmpInfo WHERE TmpId = '{parentTempletId}' ";
command.CommandText = sql;
try
{
result = Convert.ToInt32(command.ExecuteScalar());
}
catch (Exception e)
{
log.Error(string.Format($"Select from table TmpInfo error!\nsql[{sql}]\nError[{e.StackTrace}]"));
connection.Close();
throw;
}
if (result != 1)
{
log.Error(string.Format($"父模板不存在!TmpId=[{parentTempletId}]\nsql[{sql}]\n"));
connection.Close();
throw new Exception("父模板不存在!");
}
//登记新模板信息
SeqNo seqNo = new SeqNo();
tmpId = seqNo.GetBaseSeqNo();
sql = $"INSERT INTO TmpInfo (TmpId, TmpNm, Root, LockCount, CrtDate, Crter, EditLock, DCM) VALUES ({tmpId}, '{templetName}', '0', 0, '{DateTime.Now}', '{creater}', 0, 0) ";
command.CommandText = sql;
try
{
result = command.ExecuteNonQuery();
}
catch (Exception e)
{
log.Error(string.Format($"Insert table TmpInfo error!\nsql[{sql}]\nError[{e.StackTrace}]"));
transaction.Rollback();
connection.Close();
throw;
}
if (result == 0)
{
log.Error(string.Format($"插入TmpInfo记录数为0\nsql[{sql}]\n"));
transaction.Rollback();
connection.Close();
throw new Exception("插入TmpInfo记录数为0!");
}
//登记父子模板关系表Relation
//获取Relation.rlSeqNo的值
if ("MySQL" == dbType)
{
sql = $"SELECT IFNULL(MAX(rlSeqNo),-1) FROM RELATION WHERE TmpId = '{parentTempletId}' and CTmpId = '{tmpId}'";
}
else
{
sql = $"SELECT ISNULL(MAX(rlSeqNo),-1) FROM RELATION WHERE TmpId = '{parentTempletId}' and CTmpId = '{tmpId}'";
}
command.CommandText = sql;
try
{
rlSeqNo = Convert.ToInt32(command.ExecuteScalar());
}
catch (Exception e)
{
log.Error(string.Format($"Select Table Relation error!\nsql[{sql}]\nError[{e.StackTrace}]"));
connection.Close();
throw;
}
rlSeqNo++;
//Insert Relation
sql = $"INSERT INTO RELATION (pid,TmpId, CTmpId, CTmpNm, CTmpNum, LockFlag, CrtDate, Crter, rlSeqNo) VALUES ({pId},{parentTempletId}, {tmpId}, '{templetName}',0, 0, '{DateTime.Now}', '{creater}',{rlSeqNo})";
command.CommandText = sql;
try
{
result = command.ExecuteNonQuery();
}
catch (Exception e)
{
log.Error(string.Format($"Insert table Relation error!\nsql[{sql}]\nError[{e.StackTrace}]"));
transaction.Rollback();
connection.Close();
throw;
}
if (result == 0)
{
log.Error(string.Format($"插入Relation表记录数为0\nsql[{sql}]\n"));
transaction.Rollback();
connection.Close();
throw new Exception("插入Relation表记录数为0!");
}
//登记数据库表AttrPass
sql = $"INSERT INTO AttrPass (TmpId, CTmpId, CAttrId, CAttrValue, ValueTp, PAttrId, Gt, Lt, Eq, Excld, Gteq, Lteq, CrtDate, Crter, rlSeqNo) VALUES ({parentTempletId},{tmpId},'_danyl', 0, '0', '0', '', '', '', '', '', '', '{DateTime.Now}','{creater}',{rlSeqNo})";
command.CommandText = sql;
try
{
result = command.ExecuteNonQuery();
}
catch (Exception e)
{
log.Error(string.Format($"Insert table AttrPass error!\nsql[{sql}]\nError[{e.StackTrace}]"));
transaction.Rollback();
connection.Close();
throw;
}
if (result == 0)
{
log.Error(string.Format($"Insert table Attrpass 0 record\nsql[{sql}]\n"));
transaction.Rollback();
connection.Close();
throw new Exception("Insert table Attrpass 0 record!");
}
transaction.Commit();
}
connection.Close();
return(tmpId);
}