/// <summary>
/// 得到字符串的拼音
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public List <string> GetPinYin(string data)
{
data = data.Trim();
StringBuilder select = new StringBuilder(64);
StringBuilder from = new StringBuilder(64);
WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder();
OrderBySqlClauseBuilder orderByBuilder = new OrderBySqlClauseBuilder();
for (int i = 0; i < data.Length; i++)
{
char ch = data[i];
if (ch < ' ' || ch > 'z')
{
string strAlias = "P" + i.ToString();
select.AppendWithSplitChars(strAlias + ".PinYin", ", ");
from.AppendWithSplitChars("SC.PinYin " + strAlias, ", ");
wBuilder.AppendItem(strAlias + ".HZ", ch.ToString());
orderByBuilder.AppendItem(strAlias + ".Weight", FieldSortDirection.Ascending);
}
}
List <string> result = new List <string>();
if (select.Length > 0)
{
string sql = string.Format("SELECT {0} FROM {1} WHERE {2} ORDER BY {3}",
select.ToString(), from.ToString(),
wBuilder.ToSqlString(TSqlBuilder.Instance),
orderByBuilder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
foreach (DataRow row in table.Rows)
{
result.Add(DataRowToPinYin(data, row));
}
}
else
{
result.Add(DataRowToPinYin(data, null));
}
return(result);
}
/// <summary>
/// 获得指定ID的副本 和 它之前的一切副本 按照时间正排序
/// </summary>
/// <param name="id">id</param>
/// <returns>MaterialList</returns>
public MaterialList GetPreMaterialsCopyVersion(string id)
{
ExceptionHelper.CheckStringIsNullOrEmpty(id, "id");
WhereSqlClauseBuilder whereSqlClause = new WhereSqlClauseBuilder();
whereSqlClause.AppendItem("PARENT_ID",
string.Format("(SELECT PARENT_ID FROM WF.MATERIAL WHERE ID = {0})", TSqlBuilder.Instance.CheckQuotationMark(id, true)), "=", true);
whereSqlClause.AppendItem("MODIFY_TIME",
string.Format("(SELECT MODIFY_TIME FROM WF.MATERIAL WHERE ID = {0})", TSqlBuilder.Instance.CheckQuotationMark(id, true)), "<=", true);
whereSqlClause.AppendTenantCode(typeof(Material));
OrderBySqlClauseBuilder orderBySqlClause = new OrderBySqlClauseBuilder();
orderBySqlClause.AppendItem("MODIFY_TIME", FieldSortDirection.Ascending);
string sql = string.Format(@"SELECT * FROM WF.MATERIAL
WHERE {0} AND VERSION_TYPE = {1} ORDER BY {2}" ,
whereSqlClause.ToSqlString(TSqlBuilder.Instance),
Convert.ToInt16(MaterialVersionType.CopyVersion),
orderBySqlClause.ToSqlString(TSqlBuilder.Instance));
MaterialList materials = new MaterialList();
using (DbContext dbi = DbHelper.GetDBContext(GetConnectionName()))
{
using (IDataReader dr = DbHelper.RunSqlReturnDR(sql, GetConnectionName()))
{
while (dr.Read())
{
Material material = new Material();
ORMapping.DataReaderToObject(dr, material);
materials.Add(material);
}
}
}
return(materials);
}
/// <summary>
/// 由资源ID查询
/// </summary>
/// <param name="resourceIDs">资源ID</param>
/// <returns>查询结果</returns>
public MaterialList LoadMaterialsByResourceID(params string[] resourceIDs)
{
ExceptionHelper.TrueThrow <ArgumentNullException>(resourceIDs == null, "resourceIDs");
MaterialList result = new MaterialList();
if (resourceIDs.Length != 0)
{
InSqlClauseBuilder inBuilder = new InSqlClauseBuilder("RESOURCE_ID");
inBuilder.AppendItem(resourceIDs);
OrderBySqlClauseBuilder orderClause = new OrderBySqlClauseBuilder();
orderClause.AppendItem("SORT_ID", FieldSortDirection.Ascending);
string sql = string.Format("SELECT * FROM WF.MATERIAL WHERE {0} AND VERSION_TYPE = '{1}' ORDER BY {2}",
inBuilder.AppendTenantCodeSqlClause(typeof(Material)).ToSqlString(TSqlBuilder.Instance),
Convert.ToInt32(MaterialVersionType.Normal).ToString(),
orderClause.ToSqlString(TSqlBuilder.Instance));
using (DbContext dbi = DbHelper.GetDBContext(GetConnectionName()))
{
using (IDataReader dr = DbHelper.RunSqlReturnDR(sql, GetConnectionName()))
{
while (dr.Read())
{
Material material = new Material();
ORMapping.DataReaderToObject(dr, material);
result.Add(material);
}
}
}
DecorateMaterials(result);
}
return(result);
}
/// <summary>
/// 获得指定ID的副本 和 它之前的一切副本 按照时间正排序
/// </summary>
/// <param name="id">id</param>
/// <returns>MaterialList</returns>
public MaterialList GetPreMaterialsCopyVersion(string id)
{
ExceptionHelper.CheckStringIsNullOrEmpty(id, "id");
WhereSqlClauseBuilder whereSqlClause = new WhereSqlClauseBuilder();
whereSqlClause.AppendItem("PARENT_ID",
string.Format("(SELECT PARENT_ID FROM WF.MATERIAL WHERE ID = {0})", TSqlBuilder.Instance.CheckQuotationMark(id, true)), "=", true);
whereSqlClause.AppendItem("MODIFY_TIME",
string.Format("(SELECT MODIFY_TIME FROM WF.MATERIAL WHERE ID = {0})", TSqlBuilder.Instance.CheckQuotationMark(id, true)), "<=", true);
whereSqlClause.AppendTenantCode(typeof(Material));
OrderBySqlClauseBuilder orderBySqlClause = new OrderBySqlClauseBuilder();
orderBySqlClause.AppendItem("MODIFY_TIME", FieldSortDirection.Ascending);
string sql = string.Format(@"SELECT * FROM WF.MATERIAL
WHERE {0} AND VERSION_TYPE = {1} ORDER BY {2}",
whereSqlClause.ToSqlString(TSqlBuilder.Instance),
Convert.ToInt16(MaterialVersionType.CopyVersion),
orderBySqlClause.ToSqlString(TSqlBuilder.Instance));
MaterialList materials = new MaterialList();
using (DbContext dbi = DbHelper.GetDBContext(GetConnectionName()))
{
using (IDataReader dr = DbHelper.RunSqlReturnDR(sql, GetConnectionName()))
{
while (dr.Read())
{
Material material = new Material();
ORMapping.DataReaderToObject(dr, material);
materials.Add(material);
}
}
}
return materials;
}
/// <summary>
/// 由资源ID查询
/// </summary>
/// <param name="resourceIDs">资源ID</param>
/// <returns>查询结果</returns>
public MaterialList LoadMaterialsByResourceID(params string[] resourceIDs)
{
ExceptionHelper.TrueThrow<ArgumentNullException>(resourceIDs == null, "resourceIDs");
MaterialList result = new MaterialList();
if (resourceIDs.Length != 0)
{
InSqlClauseBuilder inBuilder = new InSqlClauseBuilder("RESOURCE_ID");
inBuilder.AppendItem(resourceIDs);
OrderBySqlClauseBuilder orderClause = new OrderBySqlClauseBuilder();
orderClause.AppendItem("SORT_ID", FieldSortDirection.Ascending);
string sql = string.Format("SELECT * FROM WF.MATERIAL WHERE {0} AND VERSION_TYPE = '{1}' ORDER BY {2}",
inBuilder.AppendTenantCodeSqlClause(typeof(Material)).ToSqlString(TSqlBuilder.Instance),
Convert.ToInt32(MaterialVersionType.Normal).ToString(),
orderClause.ToSqlString(TSqlBuilder.Instance));
using (DbContext dbi = DbHelper.GetDBContext(GetConnectionName()))
{
using (IDataReader dr = DbHelper.RunSqlReturnDR(sql, GetConnectionName()))
{
while (dr.Read())
{
Material material = new Material();
ORMapping.DataReaderToObject(dr, material);
result.Add(material);
}
}
}
DecorateMaterials(result);
}
return result;
}
/// <summary>
/// 得到字符串的拼音
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public List<string> GetPinYin(string data)
{
data = data.Trim();
StringBuilder select = new StringBuilder(64);
StringBuilder from = new StringBuilder(64);
WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder();
OrderBySqlClauseBuilder orderByBuilder = new OrderBySqlClauseBuilder();
for (int i = 0; i < data.Length; i++)
{
char ch = data[i];
if (ch < ' ' || ch > 'z')
{
string strAlias = "P" + i.ToString();
select.AppendWithSplitChars(strAlias + ".PinYin", ", ");
from.AppendWithSplitChars("SC.PinYin " + strAlias, ", ");
wBuilder.AppendItem(strAlias + ".HZ", ch.ToString());
orderByBuilder.AppendItem(strAlias + ".Weight", FieldSortDirection.Ascending);
}
}
List<string> result = new List<string>();
if (select.Length > 0)
{
string sql = string.Format("SELECT {0} FROM {1} WHERE {2} ORDER BY {3}",
select.ToString(), from.ToString(),
wBuilder.ToSqlString(TSqlBuilder.Instance),
orderByBuilder.ToSqlString(TSqlBuilder.Instance));
DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0];
foreach (DataRow row in table.Rows)
result.Add(DataRowToPinYin(data, row));
}
else
result.Add(DataRowToPinYin(data, null));
return result;
}