/// <summary>
/// 查询需要进行复审修改的信息
/// </summary>
/// <param name="projectId"></param>
/// <param name="shopId"></param>
/// <param name="subjectId"></param>
/// <returns></returns>
public List <RecheckDto> GetRecheckInfo(string projectId, string shopId, string subjectId, bool?passRecheck, bool?agreeCheck)
{
if (projectId == null)
{
projectId = "";
}
if (shopId == null)
{
shopId = "";
}
if (subjectId == null)
{
subjectId = "";
}
SqlParameter[] para = new SqlParameter[] { new SqlParameter("@ProjectId", projectId)
, new SqlParameter("@ShopId", shopId)
, new SqlParameter("@SubjectId", subjectId) };
Type t = typeof(RecheckDto);
string sql = @"SELECT A.*, B.ShopCode,B.ShopName,C.SubjectCode,C.[CheckPoint],C.OrderNO
, D.AccountName AS RecheckUserName,ISNULL(E.AccountName, '') AS AgreeUserName
, ISNULL(F.AccountName, '') AS LastConfirmUserName,X.PhotoScore,X.Remark
, (SELECT TOP 1 HiddenName FROM HiddenColumn WHERE HiddenCodeGroup = '仲裁结果' AND HiddenCode = A.LastConfirmCheck) AS LastConfirmCheckName
FROM Recheck A INNER JOIN Shop B ON A.ShopId = B.ShopId
INNER JOIN[Subject] C ON A.ProjectId = C.ProjectId AND A.SubjectId = C.SubjectId
INNER JOIN UserInfo D ON A.RecheckUserId = D.Id
INNER JOIN Answer X ON A.ProjectId = X.ProjectId AND A.ShopId = X.ShopId AND A.SubjectId = X.SubjectId
LEFT JOIN UserInfo E ON A.AgreeUserId = E.Id
LEFT JOIN UserInfo F ON A.LastConfirmUserId = F.Id
WHERE A.ProjectId = @ProjectId";
if (!string.IsNullOrEmpty(shopId))
{
sql += " AND A.ShopId = @ShopId";
}
if (!string.IsNullOrEmpty(subjectId))
{
sql += " AND A.SubjectId = @SubjectId";
}
if (agreeCheck.HasValue)
{
para = para.Concat(new SqlParameter[] { new SqlParameter("@AgreeCheck", agreeCheck) }).ToArray();
sql += " AND A.AgreeCheck = @AgreeCheck";
}
if (passRecheck.HasValue)
{
para = para.Concat(new SqlParameter[] { new SqlParameter("@PassRecheck", passRecheck) }).ToArray();
sql += " AND A.PassRecheck = @PassRecheck";
}
return(db.Database.SqlQuery(t, sql, para).Cast <RecheckDto>().ToList());
}
public List <DMFItem> DMFItemSearch(string dmfItemId, string dmfItemName, string dmfItemNameEn, bool?expenseAccountChk, bool?publishChk)
{
if (dmfItemId == null)
{
dmfItemId = "";
}
if (dmfItemName == null)
{
dmfItemName = "";
}
if (dmfItemNameEn == null)
{
dmfItemNameEn = "";
}
SqlParameter[] para = new SqlParameter[] { new SqlParameter("@DMFItemId", dmfItemId),
new SqlParameter("@DMFItemName", dmfItemName),
new SqlParameter("@DMFItemNameEn", dmfItemNameEn) };
Type t = typeof(DMFItem);
string sql = "";
sql = @"SELECT A.*
FROM DMFItem A
WHERE 1=1";
if (!string.IsNullOrEmpty(dmfItemId))
{
sql += " AND DMFItemId = @DMFItemId";
}
if (!string.IsNullOrEmpty(dmfItemName))
{
sql += " AND DMFItemName = @DMFItemName";
}
if (!string.IsNullOrEmpty(dmfItemNameEn))
{
sql += " AND DMFItemNameEn = @DMFItemNameEn";
}
if (expenseAccountChk.HasValue)
{
para = para.Concat(new SqlParameter[] { new SqlParameter("@ExpenseAccountChk", expenseAccountChk) }).ToArray();
sql += " AND ExpenseAccountChk = @ExpenseAccountChk";
}
if (publishChk.HasValue)
{
para = para.Concat(new SqlParameter[] { new SqlParameter("@PublishChk", publishChk) }).ToArray();
sql += " AND PublishChk = @PublishChk";
}
return(db.Database.SqlQuery(t, sql, para).Cast <DMFItem>().ToList());
}
public override int Insert <T>(IEnumerable <T> list)
{
if (list == null || list.Count() == 0)
{
throw new ArgumentNullException("Insert<T>(IEnumerable<T> list) list is not null.");
}
DbParameter[] parmsAll = new SqlParameter[0];
DbParameter[] parms = null;
StringBuilder scriptBlock = new StringBuilder();
scriptBlock.AppendLine(SqlServerLanguage <T> .InsertBatchScript);
int index = 0;
foreach (T item in list)
{
this.ConvertBatchParameters <T>(item, ref parms, ref scriptBlock, index++);
parmsAll = parmsAll.Concat(parms).ToArray();
}
int result = this.ExecuteNonQuery(scriptBlock.ToString(), parmsAll);
if (!string.IsNullOrEmpty(_mongoConnectionstr))
{
new MGServer <T>(_mongoConnectionstr, this.Connection.Database).insertList(list.ToList());
}
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="projectId"></param>
/// <param name="checkTypeId"></param>
/// <param name="addCheck"></param>
/// <param name="photoName"></param>
/// <returns></returns>
public List <PhotoList> GetPhotoList(string projectId, string checkTypeId, string photoId, string addCheck, string photoName, bool?useChk)
{
if (checkTypeId == null)
{
checkTypeId = "";
}
if (photoId == null)
{
photoId = "";
}
if (addCheck == null)
{
addCheck = "";
}
if (photoName == null)
{
photoName = "";
}
SqlParameter[] para = new SqlParameter[] { new SqlParameter("@ProjectId", projectId)
, new SqlParameter("@CheckTypeId", checkTypeId)
, new SqlParameter("@PhotoId", photoId)
, new SqlParameter("@AddCheck", addCheck)
, new SqlParameter("@PhotoName", photoName) };
Type t = typeof(PhotoList);
string sql = "";
sql = @"SELECT *
FROM [PhotoList]
WHERE ProjectId = @ProjectId
";
if (!string.IsNullOrEmpty(checkTypeId))
{
sql += " AND CheckTypeId = @CheckTypeId";
}
if (!string.IsNullOrEmpty(photoId))
{
sql += " AND PhotoId = @PhotoId";
}
if (!string.IsNullOrEmpty(addCheck))
{
sql += " AND AddCheck = @AddCheck";
}
if (!string.IsNullOrEmpty(photoName))
{
sql += " AND PhotoName = @PhotoName";
}
if (useChk != null)
{
para = para.Concat(new SqlParameter[] { new SqlParameter("@UseChk", useChk) }).ToArray();
sql += " AND UseChk = @UseChk";
}
return(db.Database.SqlQuery(t, sql, para).Cast <PhotoList>().ToList());
}
public List <EventTypeDto> EventTypeSearch(string eventTypeId, string eventTypeName, string eventTypeNameEn, bool?showStatus)
{
if (eventTypeId == null)
{
eventTypeId = "";
}
if (eventTypeName == null)
{
eventTypeName = "";
}
if (eventTypeNameEn == null)
{
eventTypeNameEn = "";
}
SqlParameter[] para = new SqlParameter[] { new SqlParameter("@EventTypeId", eventTypeId),
new SqlParameter("@EventTypeName", eventTypeName),
new SqlParameter("@EventTypeNameEn", eventTypeNameEn) };
Type t = typeof(EventTypeDto);
string sql = "";
sql = @"SELECT A.*,B.HiddenCodeName AS EventModeName,B.HiddenCodeNameEn AS EventModeNameEn,C.AreaCode,C.AreaName,C.AreaNameEn
FROM EventType A INNER JOIN HiddenCode B ON A.EventMode = B.HiddenCodeId AND B.HiddenCodeGroup = 'EventMode'
LEFT JOIN Area C ON A.AreaId = C.AreaId
WHERE 1=1";
if (!string.IsNullOrEmpty(eventTypeId))
{
sql += " AND EventTypeId = @EventTypeId";
}
if (!string.IsNullOrEmpty(eventTypeName))
{
sql += " AND EventTypeName = @EventTypeName";
}
if (!string.IsNullOrEmpty(eventTypeNameEn))
{
sql += " AND EventTypeNameEn = @EventTypeNameEn";
}
if (showStatus.HasValue)
{
para = para.Concat(new SqlParameter[] { new SqlParameter("@ShowStatus", showStatus) }).ToArray();
sql += " AND ShowStatus = @ShowStatus";
}
return(db.Database.SqlQuery(t, sql, para).Cast <EventTypeDto>().ToList());
}
/// <summary>
///
/// </summary>
/// <param name="projectId"></param>
/// <param name="checkTypeId"></param>
/// <param name="checkTypeName"></param>
/// <returns></returns>
public List <CheckType> GetCheckType(string projectId, string checkTypeId, string checkTypeName, bool?useChk)
{
if (projectId == null)
{
projectId = "";
}
if (checkTypeId == null)
{
checkTypeId = "";
}
if (checkTypeName == null)
{
checkTypeName = "";
}
SqlParameter[] para = new SqlParameter[] { new SqlParameter("@ProjectId", projectId)
, new SqlParameter("@CheckTypeId", checkTypeId)
, new SqlParameter("@CheckTypeName", checkTypeName) };
Type t = typeof(CheckType);
string sql = "";
sql = @"SELECT *
FROM [CheckType]
WHERE 1=1 ";
if (!string.IsNullOrEmpty(projectId))
{
sql += " AND ProjectId = @ProjectId";
}
if (!string.IsNullOrEmpty(checkTypeId))
{
sql += " AND CheckTypeId = @CheckTypeId";
}
if (!string.IsNullOrEmpty(checkTypeName))
{
sql += " AND CheckTypeName = @CheckTypeName";
}
if (useChk != null)
{
para = para.Concat(new SqlParameter[] { new SqlParameter("@UseChk", useChk) }).ToArray();
sql += " AND UseChk = @UseChk";
}
return(db.Database.SqlQuery(t, sql, para).Cast <CheckType>().ToList());
}