public DeliveryResutl Statistic(string customer, DateTime sStart, DateTime sEnd, string specifications, string goodname)
{
DeliveryResutl result = new DeliveryResutl();
string strSql1 = "SELECT SUM(b.lenght*b.discnum), SUM(b.discnum),SUM(b.totalprice),SUM(b.weight) FROM ct_deliverynote a,ct_deliveryitem b where a.noteid=b.noteid and a.deliverdate>='" + sStart.ToString() + "' and a.deliverdate<='" + sEnd.ToString() + "'";
if (customer.Length > 0)
{
strSql1 += " and a.customer = '" + customer + "'";
}
if (goodname.Length > 0)
{
strSql1 += " and a.goodname = '" + goodname + "'";
}
if (specifications.Length > 0)
{
strSql1 += " and b.specifications = '" + specifications + "'";
}
DataTable dtSum = DMContext.TSqlCommand(strSql1).ToDataTable();
if (dtSum != null)
{
if (dtSum.Rows.Count > 0)
{
result.TotalLength = ControlHelper.Object2Int(dtSum.Rows[0][0]);
result.TotalDisc = ControlHelper.Object2Int(dtSum.Rows[0][1]);
result.TotalPrice = ControlHelper.Object2Double(dtSum.Rows[0][2]);
result.TotalWeight = ControlHelper.Object2Double(dtSum.Rows[0][3]);
}
}
return(result);
}
private void createOrAlterTable(string tableName, string Name, List <FieldInfo> fields, Dictionary <int, string> dataTypeToSqlMap, bool hasProject = false, bool hasCategory = false)
{
var context = new DMContext();
var exists = context.Database.SqlQueryForDataTatable(string.Format(@"SELECT TOP 1 1 FROM SYSOBJECTS WHERE xtype='u' AND name = '{0}'", tableName));
if (exists.Rows.Count == 1)
{
// 如果表没有数据直接删除表重建
var tableSource = context.Database.SqlQueryForDataTatable(string.Format(@"SELECT TOP 1 1 FROM dbo.[{0}]", tableName));
if (tableSource.Rows.Count == 0)
{
dropTable(context, tableName);
createTable(context, Name, tableName, fields, dataTypeToSqlMap, hasProject, hasCategory);
}
else
{
alterTable(context, tableName, fields, dataTypeToSqlMap);
}
}
else
{
createTable(context, Name, tableName, fields, dataTypeToSqlMap, hasProject, hasCategory);
}
}
public void UpdateFieldData(int ID, string FondsNumber, string FileNumber, int UserID, List <FieldInfo> Fields)
{
var paramList = new List <SqlParameter>();
var sbSqlFields = new StringBuilder();
foreach (var field in Fields)
{
sbSqlFields.AppendFormat("[_f{0}] = @_f{0},", field.ID);
paramList.Add(new SqlParameter(string.Format("@_f{0}", field.ID), field.Value));
}
sbSqlFields.Append("[ModifyUser] = @ModifyUser,[ModifyDate] = @ModifyDate");
paramList.Add(new SqlParameter("@ModifyUser", UserID));
paramList.Add(new SqlParameter("@ModifyDate", DateTime.Now));
paramList.Add(new SqlParameter("@ID", ID));
var sql = string.Format(@"
UPDATE [dbo].[FileDoc_{0}_{1}]
SET {2}
WHERE ID = @ID", FondsNumber, FileNumber, sbSqlFields.ToString());
var context = new DMContext();
context.Database.ExecuteSqlCommand(sql, paramList.ToArray());
}
public List <QualityTrackingEntity> Query(DateTime sStart, DateTime sEnd, string batch, string target, string type, string decision)
{
string strSql = "select * from ct_qualitytracking where qtdate>='" + sStart.ToString() + "' and qtdate<='" + sEnd.ToString() + "'";
if (batch.Length > 0)
{
strSql = strSql + " and batch = '" + batch + "'";
}
if (target.Length > 0)
{
strSql = strSql + " and target ='" + target + "'";
}
if (type.Length > 0)
{
strSql = strSql + " and type ='" + type + "'";
}
if (decision.Length > 0)
{
strSql = strSql + " and decision ='" + decision + "'";
}
strSql = strSql + " order by qtid";
//CSpec<QualityTrackingEntity> order = new CSpec<QualityTrackingEntity>();
//order.And(p => p.qtdate.Asc());
//Spec<QualityTrackingEntity> where = new Spec<QualityTrackingEntity>();
//where.And(p => p.qtdate.Between(sStart, sEnd));
//return base.DMSelectList(1000, where.Exp, order.Exp);
return(DMContext.TSqlCommand(strSql).ToList <QualityTrackingEntity>());
}
public int AddFieldData(string FondsNumber, string FileNumber, int UserID, FileDataInfo Info, string NodeID = "", string dept = "")
{
var paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@FondsNumber", FondsNumber));
paramList.Add(new SqlParameter("@NodeID", NodeID));
var sbSql = new StringBuilder("@FondsNumber,@NodeID,");
var sbSqlFields = new StringBuilder("[FondsNumber],[NodeID],");
foreach (var field in Info.Fields)
{
sbSql.AppendFormat("@_f{0},", field.ID);
sbSqlFields.AppendFormat("[_f{0}],", field.ID);
if (field.NotNull && field.Value == null)
{
// 默认值
paramList.Add(new SqlParameter(string.Format("@_f{0}", field.ID), field.GetDefaultValue()));
}
else
{
paramList.Add(new SqlParameter(string.Format("@_f{0}", field.ID), field.Value));
}
}
sbSql.Append("@Dept,@CreateUser");
sbSqlFields.Append("[Dept],[CreateUser]");
paramList.Add(new SqlParameter("@Dept", dept));
paramList.Add(new SqlParameter("@CreateUser", UserID));
paramList.Add(new SqlParameter("@ID", 0)
{
Direction = System.Data.ParameterDirection.Output
});
var sql = string.Format(@"
INSERT INTO [dbo].[FileDoc_{0}_{1}]({2}) VALUES({3})
SET @ID = @@IDENTITY ", FondsNumber, FileNumber, sbSqlFields.ToString(), sbSql.ToString());
var context = new DMContext();
context.Database.ExecuteSqlCommand(sql, paramList.ToArray());
var id = Convert.ToInt32(paramList.Last().Value);
foreach (var attachID in Info.AttachIDs)
{
_IObjectAttachService.Add(new ObjectAttachEntity()
{
ObjectKey = string.Format("FileDoc_{0}_{1}", FondsNumber, FileNumber),
ObjectID = id,
AttachID = attachID
});
}
return(id);
}
static void CleanupDB()
{
using (var context = new DMContext())
{
context.Database.ExecuteSqlCommand("delete from Factors");
context.Database.ExecuteSqlCommand("delete from Categories");
context.SaveChanges();
}
}
public DocumentController(
IHostingEnvironment appEnvironment,
DMContext context)
{
_appEnvironment = appEnvironment;
_doc = new DocumentService(context);
_categories = new CategoryService(context);
_userService = new UserService(context);
_emailService = new EmailService(context, _appEnvironment.WebRootPath);
}
public String Delete(int noteid, bool isdecrease)
{
String bErr = "";
CustomerEntity customer = null;
if (isdecrease)
{
using (var repo = DMRepository.Get())
{
DeliveryNote note = repo.Get <DeliveryNote>(p => p.noteid == noteid);
if (note == null)
{
return("当前送货单不存在");
}
Spec <CustomerEntity> where = new Spec <CustomerEntity>();
where.And(p => p.customer == note.customer);
if (note.sdate.Year <= 1)
{
where.And(p => p.cyear == DateTime.Now.Year);
}
else
{
where.And(p => p.cyear == note.sdate.Year);
}
customer = repo.Get <CustomerEntity>(where.Exp);
if (customer == null)
{
return("系统中没有此客户信息");
}
if (customer.sequence != note.deliverid)
{
return("当前送货单已归档，不能删除");
}
customer.sequence = customer.sequence - 1;
}
}
using (var dmt = DMContext.GetTransaction())
{
var trans = dmt.BeginTransaction();
if (customer != null & isdecrease)
{
DMContext.Update <CustomerEntity>(customer, p => p.cid == customer.cid, null, trans);
}
DMContext.Delete <DeliveryItem>(p => p.noteid == noteid, trans);
DMContext.Delete <DeliveryNote>(p => p.noteid == noteid, trans);
trans.Commit();
}
return(bErr);
}
public void SetFileArchived(string FondsNumber, string FileNumber, string Ids)
{
var sql = string.Format(@"
UPDATE [dbo].[FileDoc_{0}_{1}]
SET [Status] = 2
WHERE ID IN ({2})", FondsNumber, FileNumber, Ids.Trim(','));
var context = new DMContext();
context.Database.ExecuteSqlCommand(sql);
}
public bool FillTaskGroupRecord(DMContext dMContext, TaskGroupRecord taskGroupRecord, CreateTaskGroupRecordViewModel createTaskGroupRecordViewModel)
{
var presentGroupMembers = createTaskGroupRecordViewModel.PresentGroupMembersIds.Select(groupMemberId =>
dMContext.GroupMembers.SingleOrDefault(gm => gm.Id == groupMemberId)
);
var presentGroupMembersList = presentGroupMembers.OrderBy(gm => gm.Score).ToList();
if (presentGroupMembers.Contains(null))
{
return(false);
}
taskGroupRecord.PresentGroupMembers = presentGroupMembers.Select(gm => {
var pgm = new PresentGroupMember();
pgm.GroupMember = gm;
pgm.TaskGroupRecord = taskGroupRecord;
return(pgm);
}).ToHashSet();
var links = new HashSet <TaskGroupMemberLink>();
var tasksList = taskGroupRecord.TaskGroup.Tasks.OrderByDescending(t => t.Bounty).ToList();
while (tasksList.Count() > 0)
{
var heaviestTask = tasksList[0];
tasksList.RemoveAt(0);
var lowestScoringMember = presentGroupMembersList.FirstOrDefault();
if (lowestScoringMember == null)
{
presentGroupMembersList = presentGroupMembers.OrderBy(gm => gm.Score + CalculateGainedScore(gm, links)).ToList();
lowestScoringMember = presentGroupMembersList[0];
}
presentGroupMembersList.RemoveAt(0);
var link = new TaskGroupMemberLink();
link.Task = heaviestTask;
link.GroupMember = lowestScoringMember;
link.TaskGroupRecord = taskGroupRecord;
links.Add(link);
}
taskGroupRecord.TaskGroupMemberLinks = links;
return(true);
}
public void DeleteFieldData(string IDs, string FondsNumber, string FileNumber, int UserID)
{
var paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ModifyUser", UserID));
paramList.Add(new SqlParameter("@ModifyDate", DateTime.Now));
var sql = string.Format(@"
UPDATE [dbo].[FileDoc_{0}_{1}]
SET [IsDelete] = 1,[ModifyUser] = @ModifyUser,[ModifyDate] = @ModifyDate
WHERE ID IN ({2})", FondsNumber, FileNumber, IDs.Trim(','));
var context = new DMContext();
context.Database.ExecuteSqlCommand(sql, paramList.ToArray());
}
public void Generate(string Fonds, string FileNumer)
{
var k = string.Format("BusinessSystem.{0}.Structure.Fonds.{1}.FileLibrary.{2}.Field.", ConstValue.BusinessKey, Fonds, FileNumer);
var fields = _IFieldService.GetFields(k, "");
var dataTypes = _IEnum.GetEnumInfo(ConstValue.BusinessKey, "FieldType");
var dataTypeToSqlMap = new Dictionary <int, string>();
foreach (var item in dataTypes.Items)
{
dataTypeToSqlMap.Add(int.Parse(item.Key), item.Tags["sqldatatype"]);
}
var sbSql = new StringBuilder();
foreach (var field in fields)
{
sbSql.AppendFormat("_f{0} {1}{2} {3},",
field.ID, dataTypeToSqlMap[field.DataType],
field.Length > 0 ? string.Format("({0})", field.Length) : "",
field.NotNull ? "NOT NULL" : "");
}
var sql = string.Format(@"
CREATE TABLE [dbo].[FileDoc_{0}_{1}](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FondsNumber] [nvarchar](20) NOT NULL,
[NodeID] [nvarchar](50) NULL,
{2}
[Status] [int] NOT NULL DEFAULT(1),
[Dept] [nvarchar](50) NULL,
[CreateUser] [int] NOT NULL DEFAULT(0),
[CreateDate] [datetime] NOT NULL DEFAULT(GETDATE()),
[ModifyUser] [int] NULL,
[ModifyDate] [datetime] NULL,
[IsDelete] [bit] NOT NULL DEFAULT(0),
CONSTRAINT [PK_FileDoc_{0}_{1}] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]", Fonds, FileNumer, sbSql.ToString());
var context = new DMContext();
context.Database.ExecuteSqlCommand(sql);
}
public void Update(DeliveryNote note)
{
using (var dmt = DMContext.GetTransaction())
{
int noteid = note.noteid;
var trans = dmt.BeginTransaction();
DMContext.Delete <DeliveryItem>(p => p.noteid == noteid, trans);
DMContext.Update <DeliveryNote>(note, p => p.noteid == noteid, null, trans);
if (note.items != null)
{
foreach (DeliveryItem item in note.items)
{
item.noteid = note.noteid;
DMContext.Insert(item, null, trans);
}
}
trans.Commit();
}
}
static void WriteToDB(int numberOfFactors)
{
var stopWatch = new Stopwatch();
var category = CreateCategory();
var factors = new List <Factor>(numberOfFactors);
for (int iterator = 1; iterator < numberOfFactors; iterator++)
{
factors.Add(CreateFactor(category, iterator));
}
using (var context = new DMContext())
{
stopWatch.Start();
context.Categories.Add(category);
context.Factors.AddRange(factors);
context.SaveChanges();
stopWatch.Stop();
}
Console.WriteLine("Writing of {0} factors to DB took {1} ms", numberOfFactors, stopWatch.ElapsedMilliseconds);
}
static void ReadFromDB()
{
var stopWatch = new Stopwatch();
List <Factor> factors;
ICollection <FieldValue> fields;
using (var context = new DMContext())
{
stopWatch.Start();
factors = context.Factors.Include("Category").ToList();
foreach (var factor in factors)
{
fields = factor.Fields;
}
stopWatch.Stop();
}
var numberOfFactors = factors.Count;
Console.WriteLine("Reading of {0} factors from DB took {1} ms", numberOfFactors, stopWatch.ElapsedMilliseconds);
//factors.Take(10).ToList().ForEach(PrintFactor);
}
static void QueryDB()
{
var stopWatch = new Stopwatch();
List <Factor> factors;
using (var context = new DMContext())
{
stopWatch.Start();
var fieldId = context.Factors.First().Category.Fields.First(f => f.Name == "Val1").Id;
factors = context.Factors
.Include("Category")
.ToList() // Because of xml deserialization we cant do Linq to entity
.Where(f => f.Fields.Any(field => field.Id == fieldId && (field as FieldValue <int>).Value < 10))
.ToList();
stopWatch.Stop();
}
var numberOfFactors = factors.Count;
Console.WriteLine("Linq query of {0} factors took {1} ms", numberOfFactors, stopWatch.ElapsedMilliseconds);
//factors.ForEach(PrintFactor);
}
private void dropTable(DMContext context, string tableName)
{
context.Database.ExecuteSqlCommand(string.Format(@"DROP TABLE dbo.[{0}]", tableName));
}
public TaskGroupController(DMContext dMContext) : base(dMContext)
{
}
public UserService(DMContext context)
{
_context = context;
}
public TableSource GetFileData(string FondsNumber, string FileNumber, PageQueryParam Param, List <Condition> Conditions)
{
var sbWhereSql = new StringBuilder("WHERE [IsDelete] = 0");
var sqlParams = new List <SqlParameter>();
var orderStr = "[ID]";
if (!string.IsNullOrEmpty(Param.OrderFiled))
{
orderStr = string.Format("[{0}] {1}", Param.OrderFiled, Param.IsDesc ? "DESC" : "");
}
foreach (DictionaryEntry filter in Param.FilterCondtion)
{
var val = filter.Value.ToString();
if (string.IsNullOrEmpty(val))
{
continue;
}
switch (filter.Key.ToString())
{
case "IDs":
{
if (val != "0")
{
sbWhereSql.AppendFormat(" AND [ID] IN ({0})", val);
}
break;
}
case "NodeID":
{
sbWhereSql.Append(" AND [NodeID] = @NodeID");
sqlParams.Add(new SqlParameter("@NodeID", val));
break;
}
case "Status":
{
var intVal = int.Parse(val);
if (intVal > 0)
{
sbWhereSql.Append(" AND [Status] = @Status");
sqlParams.Add(new SqlParameter("@Status", intVal));
}
break;
}
case "Dept":
{
if (val != "0")
{
sbWhereSql.Append(" AND [Dept] = @Dept");
sqlParams.Add(new SqlParameter("@Dept", val));
}
break;
}
default:
break;
}
}
var wheresql = Conditions == null ? string.Empty : Conditions.ToQuerySql(sqlParams);
if (!string.IsNullOrEmpty(wheresql))
{
sbWhereSql.Append(string.Format(" AND {0}", wheresql));
}
if (Param.PageSize > 0)
{
var sql = string.Format(@"
SELECT @AllCount = COUNT(1) FROM [dbo].[FileDoc_{0}_{1}] {4}
SELECT TOP {2} *
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY {5}) AS ROWNUMBER,*
FROM [dbo].[FileDoc_{0}_{1}] WITH(NOLOCK)
{4}
) A
WHERE ROWNUMBER > {3}
ORDER BY {5}", FondsNumber, FileNumber, Param.PageSize, Param.PageSize * (Param.PageIndex - 1), sbWhereSql.ToString(), orderStr);
var paramAllCount = new SqlParameter("@AllCount", 0)
{
Direction = ParameterDirection.Output
};
sqlParams.Add(paramAllCount);
var context = new DMContext();
var table = context.Database.SqlQueryForDataTatable(sql, sqlParams.ToArray());
return(new TableSource(table, Convert.ToInt32(paramAllCount.Value), Param.PageSize));
}
else
{
var sql = string.Format(@"
SELECT *
FROM [dbo].[FileDoc_{0}_{1}] WITH(NOLOCK)
{2}
ORDER BY {3}", FondsNumber, FileNumber, sbWhereSql.ToString(), orderStr);
var context = new DMContext();
var table = context.Database.SqlQueryForDataTatable(sql, sqlParams.ToArray());
return(new TableSource(table, 0, 0));
}
}
public AuthController(SignInManager <Person> signInManager, UserManager <Person> userManager, IConfiguration configuration, DMContext dMContext) : base(dMContext)
{
this.signInManager = signInManager;
this.userManager = userManager;
this.configuration = configuration;
}
public InvitationController(DMContext dMContext) : base(dMContext)
{
}
public void SetFileArchived(string FondsNumber, string FileNumber, PageQueryParam Param, List <Condition> Conditions)
{
var sbWhereSql = new StringBuilder("WHERE [IsDelete] = 0");
var sqlParams = new List <SqlParameter>();
foreach (DictionaryEntry filter in Param.FilterCondtion)
{
var val = filter.Value.ToString();
if (string.IsNullOrEmpty(val))
{
continue;
}
switch (filter.Key.ToString())
{
case "IDs":
{
if (val != "0")
{
sbWhereSql.AppendFormat(" AND [ID] IN ({0})", val);
}
break;
}
case "NodeID":
{
sbWhereSql.Append(" AND [NodeID] = @NodeID");
sqlParams.Add(new SqlParameter("@NodeID", val));
break;
}
case "Status":
{
var intVal = int.Parse(val);
if (intVal > 0)
{
sbWhereSql.Append(" AND [Status] = @Status");
sqlParams.Add(new SqlParameter("@Status", intVal));
}
break;
}
case "Dept":
{
if (val != "0")
{
sbWhereSql.Append(" AND [Dept] = @Dept");
sqlParams.Add(new SqlParameter("@Dept", val));
}
break;
}
default:
break;
}
}
var wheresql = Conditions == null ? string.Empty : Conditions.ToQuerySql(sqlParams);
if (!string.IsNullOrEmpty(wheresql))
{
sbWhereSql.Append(string.Format(" AND {0}", wheresql));
}
var sql = string.Format(@"
UPDATE [dbo].[FileDoc_{0}_{1}]
SET [Status] = 2
{2}", FondsNumber, FileNumber, sbWhereSql.ToString());
var context = new DMContext();
context.Database.ExecuteSqlCommand(sql, sqlParams.ToArray());
}
public BookService()
{
_DMContext = new DMContext();
_DBBook = new BaseRepository <BookEntity>(_DMContext);
_DBBookItem = new BaseRepository <BookItemEntity>(_DMContext);
}
private void createTable(DMContext context, string Name, string tableName, List <FieldInfo> fields, Dictionary <int, string> dataTypeToSqlMap, bool hasProject = false, bool hasCategory = false)
{
var sbSql = new StringBuilder();
if (Name == "File")
{
sbSql.Append(@"[NodeID] [nvarchar](50) NULL,
[RefID] [INT] NULL,
[FileNumber] [INT] NULL,
[FileID] [INT] NULL,");
}
else if (Name == "Project")
{
}
else
{
sbSql.Append(@"[NodeID] [nvarchar](50) NULL,
[FileNumber] [INT] NULL,
[AccessLevel] [INT] NOT NULL DEFAULT(1),
[Copies] [INT] NOT NULL DEFAULT(1),");
if (hasProject)
{
sbSql.Append(@"[ProjectID] [INT] NOT NULL DEFAULT(0),");
}
if (hasCategory)
{
sbSql.Append(@"[Category] [NVARCHAR](50) NULL,");
}
}
foreach (var field in fields)
{
var defaultStr = "";
if (field.DataType == 1 || field.DataType == 5)
{
defaultStr = string.Format("DEFAULT({0}) ", field.GetDefaultValue());
}
else
{
defaultStr = string.Format("DEFAULT('{0}') ", field.GetDefaultValue());
}
sbSql.AppendFormat("_f{0} {1}{2} {3},",
field.ID, dataTypeToSqlMap[field.DataType],
field.Length > 0 ? string.Format("({0})", field.Length) : "",
field.NotNull ? string.Format("NOT NULL {0} ", defaultStr) : "");
}
var sql = string.Format(@"
CREATE TABLE [dbo].[{0}](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FondsNumber] [nvarchar](20) NOT NULL,
{1}
[Status] [int] NOT NULL DEFAULT(1),
[CreateUser] [int] NOT NULL DEFAULT(0),
[CreateDate] [datetime] NOT NULL DEFAULT(GETDATE()),
[ModifyUser] [int] NULL,
[ModifyDate] [datetime] NULL,
[IsDelete] [bit] NOT NULL DEFAULT(0),
CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]", tableName, sbSql.ToString());
context.Database.ExecuteSqlCommand(sql);
}
public DocumentService(DMContext db, IHostingEnvironment appEnvironment)
{
_context = db;
_appEnvironment = appEnvironment;
}
private void alterTable(DMContext context, string tableName, List <FieldInfo> fields, Dictionary <int, string> dataTypeToSqlMap)
{
var result = new StringBuilder();
var columnSql = string.Format(@"SELECT CONVERT(INT,SUBSTRING(name,3,2),0) AS name FROM SYSCOLUMNS C
WHERE C.name like '_f%' AND C.name <> 'FondsNumber'
AND EXISTS(SELECT TOP 1 1 FROM SYSOBJECTS T WHERE C.id = T.id AND T.xtype='u' AND T.name = '{0}' )", tableName);
var columns = context.Database.SqlQueryForDataTatable(columnSql);
var fieldIDs = string.Join(",", fields.Select(f => f.ID));
// 需要被删除的字段
var dropCols = columns.Select(string.Format("name NOT IN ({0})", fieldIDs));
// 删除列
if (dropCols.Length > 0)
{
var cols = string.Join(",", dropCols.Select(c => "_f" + c[0].ToString()));
var sqlDropConSql = string.Format(@"SELECT B.name FROM SYSOBJECTS B JOIN SYSCOLUMNS A ON B.id = A.cdefault
WHERE A.id = OBJECT_ID('{0}')
AND A.name IN ('{1}')", tableName, string.Join("','", dropCols.Select(c => "_f" + c[0].ToString())));
var cons = context.Database.SqlQueryForDataTatable(sqlDropConSql);
if (cons.Rows.Count > 0)
{
var conStrs = cons.Select().Select(c => c[0].ToString());
result.AppendFormat(@"ALTER TABLE {0} DROP CONSTRAINT {1}", tableName, string.Join(",", conStrs));
}
result.AppendFormat(@"
ALTER TABLE {0} DROP COLUMN {1}", tableName, string.Join(",", dropCols.Select(c => "_f" + c[0].ToString())));
}
var existsCols = columns.Select(string.Format("name IN ({0})", fieldIDs)).Select(r => r[0].ToString());
// 需要新增的字段
var newCols = fields.Where(f => !existsCols.Contains(f.ID));
if (newCols.Count() > 0)
{
result.AppendFormat(@"
ALTER TABLE {0} ADD ", tableName);
foreach (var field in newCols)
{
var defaultStr = "";
if (field.DataType == 1 || field.DataType == 5)
{
defaultStr = string.Format("DEFAULT({0}) ", field.GetDefaultValue());
}
else
{
defaultStr = string.Format("DEFAULT('{0}') ", field.GetDefaultValue());
}
result.AppendFormat("_f{0} {1}{2} {3},",
field.ID, dataTypeToSqlMap[field.DataType],
field.Length > 0 ? string.Format("({0})", field.Length) : "",
field.NotNull ? string.Format("NOT NULL {0}", defaultStr) : "");
}
}
if (dropCols.Length > 0 || newCols.Count() > 0)
{
context.Database.ExecuteSqlCommand(result.ToString().TrimEnd(','));
}
}
public BorrowService()
{
_DMContext = new DMContext();
_DBArchive = new BaseRepository <ArchiveBorrowEntity>(_DMContext);
_DBArchiveItem = new BaseRepository <ArchiveBorrowItemEntity>(_DMContext);
}
public TaskGroupRecordController(DMContext dMContext, ITaskGroupRecordLogic taskGroupRecordLogic) : base(dMContext)
{
this.taskGroupRecordLogic = taskGroupRecordLogic;
}
/// <summary>
/// 批量更新
/// </summary>
/// <param name="FondsNumber"></param>
/// <param name="FileNumber"></param>
/// <param name="Info"></param>
public void BatchUpdate(string FondsNumber, string FileNumber, BatchUpdateInfo Info)
{
if (Info.Field == null || string.IsNullOrEmpty(Info.Field.ID))
{
throw new Exception("更新字段不能为空");
}
var paramList = new List <SqlParameter>();
if (Info.Field.DataType == (int)FieldDataTypeEnum.字符)
{
var valueSB = new StringBuilder();
var index = 1;
var expressions = new StringBuilder();
var identityExp = new StringBuilder();
foreach (var v in Info.Expressions)
{
if (v.Value != null)
{
// 字符串
paramList.Add(new SqlParameter("@Value" + index, v.Value));
expressions.AppendFormat("@Value{0} + ", index);
}
else if (v.Field != null && !string.IsNullOrEmpty(v.Field.ID))
{
// 字段
if (v.Field.DataType == (int)FieldDataTypeEnum.字符)
{
expressions.AppendFormat("[_f{0}] + ", v.Field.ID);
}
else if (v.Field.DataType == (int)FieldDataTypeEnum.日期)
{
expressions.AppendFormat("CONVERT(NVARCHAR(100),[_f{0}],111) + ", v.Field.ID);
}
else
{
expressions.AppendFormat("CONVERT(NVARCHAR(100),[_f{0}],0) + ", v.Field.ID);
}
}
else if (v.IdentityLength > 0)
{
// 自增加编号
identityExp.AppendFormat("@Identity{0} = @Identity{0} + 1 , ", index);
expressions.AppendFormat("CONVERT(NVARCHAR({1}), dbo.PadLeft(@Identity{0}, '{2}', {1}),0) + ", index, v.IdentityLength, v.IdentityFill);
paramList.Add(new SqlParameter("@Identity" + index, v.IdentityStart));
}
index++;
}
var expressionsStr = expressions.ToString().Trim().TrimEnd('+');
// 节点条件
var sbWhereSql = new StringBuilder("WHERE NodeID = @NodeID");
paramList.Add(new SqlParameter("@NodeID", Info.NodeID));
// 部门条件
if (!string.IsNullOrEmpty(Info.Dept) && Info.Dept != "0")
{
sbWhereSql.Append(" AND Dept = @Dept ");
paramList.Add(new SqlParameter("@Dept", Info.Dept));
}
if (!string.IsNullOrEmpty(Info.UpdateIDs))
{
sbWhereSql.AppendFormat(" AND [ID] IN ({0})", Info.UpdateIDs.Trim(','));
}
else if (Info.Conditions != null)
{
var wheresql = Info.Conditions.ToQuerySql(paramList);
if (!string.IsNullOrEmpty(wheresql))
{
sbWhereSql.AppendFormat(" ADN {0}", wheresql);
}
}
var sql = "";
if (!string.IsNullOrEmpty(Info.ReplaceValue))
{
// 字段的查找替换
sql = string.Format(@"
UPDATE [dbo].[FileDoc_{0}_{1}]
SET {4} [ModifyUser] = @ModifyUser,[ModifyDate] = @ModifyDate, [_f{2}] = REPLACE([_f{2}],@ReplaceValue,{3})
{5}", FondsNumber, FileNumber, Info.Field.ID, expressionsStr, identityExp, sbWhereSql);
paramList.Add(new SqlParameter("@ReplaceValue", Info.ReplaceValue));
}
else if (Info.PartLength > 0)
{
// 字段的区域更新
sql = string.Format(@"
UPDATE [dbo].[FileDoc_{0}_{1}]
SET {4} [ModifyUser] = @ModifyUser,[ModifyDate] = @ModifyDate, [_f{2}] = dbo.ReplacePart([_f{2}],@PartStart,@PartLength,{3})
{5}", FondsNumber, FileNumber, Info.Field.ID, expressionsStr, identityExp, sbWhereSql);
paramList.Add(new SqlParameter("@PartStart", Info.PartStart));
paramList.Add(new SqlParameter("@PartLength", Info.PartLength));
}
else
{
sql = string.Format(@"
UPDATE [dbo].[FileDoc_{0}_{1}]
SET {4} [ModifyUser] = @ModifyUser,[ModifyDate] = @ModifyDate, [_f{2}] = {3}
{5}", FondsNumber, FileNumber, Info.Field.ID, expressionsStr, identityExp, sbWhereSql);
}
paramList.Add(new SqlParameter("@ModifyUser", Info.UpdateUser));
paramList.Add(new SqlParameter("@ModifyDate", DateTime.Now));
var context = new DMContext();
context.Database.ExecuteSqlCommand(sql, paramList.ToArray());
}
else
{
paramList.Add(new SqlParameter("@Value", Info.Expressions.First().Value));
// 节点条件
var sbWhereSql = new StringBuilder("WHERE NodeID = @NodeID");
paramList.Add(new SqlParameter("@NodeID", Info.NodeID));
// 部门条件
if (!string.IsNullOrEmpty(Info.Dept) && Info.Dept != "0")
{
sbWhereSql.Append(" AND Dept = @Dept ");
paramList.Add(new SqlParameter("@Dept", Info.Dept));
}
if (!string.IsNullOrEmpty(Info.UpdateIDs))
{
sbWhereSql.AppendFormat(" AND [ID] IN ({0})", Info.UpdateIDs.Trim(','));
}
else if (Info.Conditions != null)
{
var wheresql = Info.Conditions.ToQuerySql(paramList);
if (!string.IsNullOrEmpty(wheresql))
{
sbWhereSql.AppendFormat(" ADN {0}", wheresql);
}
}
var sql = string.Format(@"
UPDATE [dbo].[FileDoc_{0}_{1}]
SET [ModifyUser] = @ModifyUser,[ModifyDate] = @ModifyDate,[_f{2}] = @Value
{3}", FondsNumber, FileNumber, Info.Field.ID, sbWhereSql);
paramList.Add(new SqlParameter("@ModifyUser", Info.UpdateUser));
paramList.Add(new SqlParameter("@ModifyDate", DateTime.Now));
var context = new DMContext();
context.Database.ExecuteSqlCommand(sql, paramList.ToArray());
}
}