private static string GetDateRangeSql(WhereData cp, char _separator, bool _ignoreEmpty = true)
{
var sSql = string.Empty;
var _from = "datediff(day,'{1}',{0}) >=0";
var _to = "datediff(day,'{1}',{0})<=0";
var values = ZConvert.ToString(cp.Value).Split(_separator);
if (values.Length == 1)
{
values = new string[] { values[0], values[0] }
}
;
if (!string.IsNullOrWhiteSpace(values[0]) || !_ignoreEmpty)
{
sSql = string.Format(_from, cp.Column, values[0]);
}
if (!string.IsNullOrWhiteSpace(values[1]) || !_ignoreEmpty)
{
sSql += (sSql.Length > 0 ? " and " : string.Empty) + string.Format(_to, cp.Column, values[1]);
}
return(sSql);
}
}
private static string GetDateRangeSql(WhereData cp, char[] separator, bool ignoreEmpty = true)
{
var sSql = string.Empty;
string fromDateStr;
string toDateStr;
if (APP.DbProvider == DbProviderEnum.Oracle)
{
fromDateStr = "{0} >= TO_DATE('{1}','yyyy-mm-dd')";
toDateStr = "{0} <= TO_DATE('{1} 23:59:59','yyyy-mm-dd hh24:mi:ss')";
}
else
{
fromDateStr = "datediff(day,'{1}',{0}) >=0";
toDateStr = "datediff(day,'{1}',{0})<=0";
}
var values = ZConvert.ToString(cp.Value).Split(separator);
var startDate = values[0].Trim();
var endDate = values.Length == 2 ? values[1].Trim() : startDate;
if (!string.IsNullOrWhiteSpace(startDate) || !ignoreEmpty)
{
sSql = string.Format(fromDateStr, cp.Column, startDate);
}
if (!string.IsNullOrWhiteSpace(endDate) || !ignoreEmpty)
{
sSql += (sSql.Length > 0 ? " AND " : string.Empty) + string.Format(toDateStr, cp.Column, endDate);
}
return(sSql);
}
private static void SetCacheKey(string table, string field, string key)
{
var tableKeys = GetTableKeys(table);
var fieldKeys = GetFieldKeys(tableKeys, field);
tableKeys[field] = ZConvert.ToString(MaxOfAllKey(fieldKeys, key));
ZCache.SetCache(String.Format("currentkey_{0}", table), tableKeys);
}
//日期时间加上N位数字加一
public static string Dateplus(IDbContext db, string table, string field, string datestringFormat, int numberLength)
{
var dbkey = db.Sql(String.Format("select isnull(max({0}),0) from {1}", field, table)).QuerySingle <string>();
var mykey = DateTime.Now.ToString(datestringFormat) + String.Empty.PadLeft(numberLength, '0');
var cachedKeys = GetCacheKey(table, field);
var currentKey = MaxOfAllKey(cachedKeys, ZConvert.ToString(dbkey), mykey);
var key = ZConvert.ToString(currentKey + 1);
SetCacheKey(table, field, key);
return(key);
}
public dynamic GetSystemLog(RequestWrapper request)
{
var page = ZConvert.To <int>(request["page"], 1);
var rows = ZConvert.To <int>(request["rows"], 0);
var logDate = ZConvert.ToString(request["logdate"]);
var list = new List <dynamic>();
var basepath = HttpContext.Current.Server.MapPath("/logs/");
var di = new DirectoryInfo(basepath);
if (!di.Exists)
{
di.Create();
}
string[] s = logDate.Split('到');
string s1 = "1990-01-01";
string s2 = DateTime.Now.Date.ToString();
switch (s.Length)
{
case 1:
if (logDate.Length > 0)
{
s1 = s[0];
s2 = s1;
}
break;
case 2:
s1 = s[0];
s2 = s[1];
break;
}
int t1 = Convert.ToInt32(Convert.ToDateTime(s1).ToString("yyyyMMdd"));
int t2 = Convert.ToInt32(Convert.ToDateTime(s2).ToString("yyyyMMdd"));
foreach (var fi in di.GetFiles().Where(x => (Convert.ToInt32(x.FullName.Replace(basepath, "").Substring(3, 8)) >= t1 && Convert.ToInt32(x.FullName.Replace(basepath, "").Substring(3, 8)) <= t2)))
{
dynamic item = new ExpandoObject();
item.filename = fi.FullName.Replace(basepath, "");
item.size = (fi.Length / 1024).ToString() + " KB";
item.time = fi.CreationTime.ToString();
item.id = item.filename.Replace(".txt", "");
list.Add(item);
}
var result = list.OrderByDescending(x => x.filename).Skip((page - 1) * rows).Take(rows);
return(new { rows = result, total = list.Count() });
}
//最大值加一
public static string Maxplus(IDbContext db, string table, string field)
{
var sqlWhere = " where 1 = 1 ";
var dbkey = APP.DbProvider == DbProviderEnum.SqlServer
? db.Sql(String.Format("select isnull(max({0}),0) from {1} {2}", field, table, sqlWhere)).QuerySingle <string>()
: db.Sql(String.Format("select NVL(max({0}),0) from {1} {2}", field, table, sqlWhere)).QuerySingle <string>();
var cachedKeys = GetCacheKey(table, field);
var currentKey = MaxOfAllKey(cachedKeys, ZConvert.ToString(dbkey));
var key = ZConvert.ToString(currentKey + 1);
SetCacheKey(table, field, key);
return(key);
}
public static string inventory(IDbContext db, string table, string field, ParamQuery pQuery)
{
//var where = pQuery.GetData().WhereSql;
var sqlWhere = " where 1 = 1 ";
if (pQuery != null)
{
sqlWhere += " and " + pQuery.GetData().WhereSql;
}
var dbkey = db.Sql(String.Format("select isnull(max({0}),0) from {1} {2}", field, table, sqlWhere)).QuerySingle <string>();
var cachedKeys = getCacheKey(table, field);
var currentKey = maxOfAllKey(cachedKeys, ZConvert.ToString(dbkey));
var key = ZConvert.ToString(currentKey + 1);
SetCacheKey(table, field, key);
return(key);
}
public virtual void FillData(int x, int y, string field, object value)
{
var row = sheet.GetRow(y) ?? sheet.CreateRow(y);
var cell = row.GetCell(x) ?? row.CreateCell(x);
//if (!field.StartsWith("title_"))
cell.CellStyle = GetDataStyle();
switch ((value ?? string.Empty).GetType().Name.ToLower())
{
case "int32":
case "int64":
case "decimal":
cell.CellStyle.Alignment = HorizontalAlignment.Right;
cell.SetCellValue(ZConvert.To <double>(value, 0));
break;
default:
cell.SetCellValue(ZConvert.ToString(value));
break;
}
}
public static Stream ListToExcel(object list, Dictionary <string, string> titles, bool IsExportAllCol)
{
const int startIndex = 0;
var fields = titles.Keys.ToList();
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet("sheet1");
sheet.DefaultRowHeight = 200 * 20;
var row = sheet.CreateRow(startIndex);
var headStyle = GetHeadStyle(workbook);
EachHelper.EachListHeader(list, (i, name, type) =>
{
if (!fields.Contains(name))
{
if (IsExportAllCol)
{
fields.Add(name);
}
else
{
return;
}
}
var cellIndex = fields.IndexOf(name) + startIndex;
var cell = row.CreateCell(cellIndex);
cell.SetCellValue(titles.ContainsKey(name)?titles[name]:name);
cell.CellStyle = headStyle;
sheet.AutoSizeColumn(cellIndex);
});
EachHelper.EachListRow(list, (rowIndex, dataRow) =>
{
row = sheet.CreateRow(rowIndex + 1);
EachHelper.EachObjectProperty(dataRow, (i, name, value) =>
{
if (!fields.Contains(name))
{
if (IsExportAllCol)
{
fields.Add(name);
}
else
{
return;
}
}
var cellIndex = fields.IndexOf(name) + startIndex;
var dataStyle = GetDataStyle(workbook);
var cell = row.CreateCell(cellIndex);
cell.CellStyle = dataStyle;
switch ((value ?? string.Empty).GetType().Name.ToLower())
{
case "int32":
case "int64":
case "decimal":
dataStyle.Alignment = HorizontalAlignment.RIGHT;
cell.SetCellValue(ZConvert.To <double>(value, 0));
break;
default:
cell.CellStyle.Alignment = HorizontalAlignment.LEFT;
cell.SetCellValue(ZConvert.ToString(value));
break;
}
});
});
var ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook = null;
sheet = null;
row = null;
return(ms);
}