private static string GetSearchSql(FormItemSearch search)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
string sql = "SELECT ID, CreateDate, CheckStatus, CheckDate, IsBack, SN, Remark, Email, Phone, Mobile, IDCard, CheckInDate, IsProcess, ProcessRemark, ProcessTime FROM FormItem I WHERE {0} ORDER BY {1}";
List <string> where = new List <string>();
where.Add("FormID = " + search.FormID);
where.Add("IsTemp = 0");
if (!string.IsNullOrWhiteSpace(search.Key))
{
string key = $"N'%{ search.Key.Replace("'", "''") }%'";
List <string> orSql = new List <string>();
orSql.Add($"Exists(Select 1 From FieldValue Where FormItemID = I.ID AND Value Like { key })");
string query = $"Select UserNo From UserFlag Where Flag Like { key } AND UserNo <> '' AND SiteID = (Select SiteID From Form Where ID = { search.FormID })";
IEnumerable <string> userNo = db.GetDataTable(query).AsEnumerable().Select(dr => $"N'{ dr["UserNo"].ToString().Trim() }'");
if (userNo?.Count() > 0)
{
orSql.Add(string.Format("Email In ({0}) OR Mobile In ({0}) OR Phone In ({0}) OR IDCard In ({0})", string.Join(", ", userNo)));
}
where.Add($"({ string.Join(" OR ", orSql) })");
}
if (search.CheckStatusList?.Length > 0)
{
where.Add($"CheckStatus IN ({ string.Join(", ", search.CheckStatusList.Select(s => (int)s)) })");
}
if (search.FillModes?.Count() == 1)
{
where.Add($"IsBack = { search.FillModes[0] }");
}
if (search.Start != null)
{
where.Add($"CreateDate >= '{ search.Start.ToString("yyyy/MM/dd HH:mm") }'");
}
if (search.End != null)
{
where.Add($"CreateDate <= '{ search.End.ToString("yyyy/MM/dd HH:mm") }'");
}
string[] allowOrders = { "CheckStatus", "CreateDate", "CreateDate Desc" };
string order = allowOrders.Contains(search.Order) ? search.Order : "CheckStatus";
return(string.Format(sql, string.Join(" AND ", where), order));
}
public static IEnumerable <FormItem> GetItems(FormItemSearch search)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetDataTable(GetSearchSql(search));
List <FormItem> items = new List <FormItem>();
foreach (DataRow dr in datas.Rows)
{
items.Add(new FormItem {
ID = (long)dr["ID"], FormID = search.FormID, CreateDate = (DateTime)dr["CreateDate"], CheckStatus = (byte)dr["CheckStatus"], CheckDate = dr["CheckDate"] as DateTime?,
IsBack = (bool)dr["IsBack"], SN = dr["SN"] as int?, Remark = dr["Remark"].ToString().Trim(), Email = dr["Email"].ToString().Trim(), Phone = dr["Phone"].ToString().Trim(),
Mobile = dr["Mobile"].ToString().Trim(), IDCard = dr["IDCard"].ToString().Trim(), CheckInDate = dr["CheckInDate"] as DateTime?
});
}
return(items);
}
public static List <string> GetItemsAll(FormItemSearch search)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetDataTable(GetSearchSql(search));
List <string> items = new List <string>();
if (datas == null || datas.Rows.Count == 0)
{
return(items);
}
if (datas != null)
{
foreach (DataRow dr in datas.Rows)
{
items.Add(dr["ID"].ToString());
}
}
return(items);
}
public static IEnumerable <FormItem> GetItems(FormItemSearch search, int pageSize, int pageIndex, out int recordCount)
{
SQLData.Database db = new SQLData.Database(WebInfo.Conn);
DataTable datas = db.GetPageData(GetSearchSql(search), pageSize, pageIndex, out recordCount);
List <FormItem> items = new List <FormItem>();
if (datas == null || datas.Rows.Count == 0)
{
return(items);
}
foreach (DataRow dr in datas.Rows)
{
items.Add(new FormItem {
ID = (long)dr["ID"], FormID = search.FormID, CreateDate = (DateTime)dr["CreateDate"], CheckStatus = (byte)dr["CheckStatus"], CheckDate = dr["CheckDate"] as DateTime?,
IsBack = (bool)dr["IsBack"], SN = dr["SN"] as int?, Remark = dr["Remark"].ToString().Trim(), Email = dr["Email"].ToString().Trim(), Phone = dr["Phone"].ToString().Trim(),
Mobile = dr["Mobile"].ToString().Trim(), IDCard = dr["IDCard"].ToString().Trim(), CheckInDate = dr["CheckInDate"] as DateTime?,
IsProcess = dr["IsProcess"] == DBNull.Value? false: (bool)dr["IsProcess"], ProcessRemark = dr["ProcessRemark"].ToString(), ProcessTime = dr["ProcessTime"] as DateTime?
});
}
return(items);
}