protected void Page_Load(object sender, EventArgs e)
{
string skeSql = "select * from ({0}) as a where rowno >= {1} and rowno <= {2} {3}";
string corSql = "select id, name, code, specification, number, totalprice, taxrate, location, startusingtime, buyingtime, status, usingman, remark, " +
"row_number() over ({1}) as rowno " +
"from uds_assetmanagement {0}";
string countSkeSql = "select count(id) from uds_assetmanagement {0}";
string method = Request.Params["m"];
switch (method)
{
case "q":
#region 查询
try
{
string startIndex = Request.Params["startIndex"];
string orderby = Request.Params["orderby"];
string order = Request.Params["order"];
string rows = Request.Params["rows"];
string type = Request.Params["type"];
dynamic queryStyle = JValue.Parse(HttpUtility.UrlDecode(Request.Params["qs"]));
string[] fields = (queryStyle.fields as JArray).Select(x => (string)x).ToArray();
string[] values = (queryStyle.values as JArray).Select(x => (string)x).ToArray();
string wheresql = "";
if (0 != fields.Length)
{
wheresql = "where";
for (int i = 0; i < fields.Length; i++)
{
if (i != fields.Length - 1)
{
if (IsNumeric(values[i]))
{
wheresql = wheresql + " " + fields[i] + " = " + values[i] + " and";
}
else
{
wheresql = wheresql + " " + fields[i] + " like '" + values[i] + "%' and";
}
}
else
{
if (IsNumeric(values[i]))
{
wheresql = wheresql + " " + fields[i] + " = " + values[i];
}
else
{
wheresql = wheresql + " " + fields[i] + " like '" + values[i] + "%'";
}
}
}
}
string orderSql = "";
if (!string.IsNullOrWhiteSpace(orderby))
{
orderSql = "order by " + orderby;
if (!string.IsNullOrWhiteSpace(order))
{
orderSql = orderSql + " " + order;
}
else
{
orderSql = orderSql + " asc";
}
}
string innerSql = string.Format(corSql, wheresql, orderSql);
string sql = string.Format(skeSql, innerSql, startIndex, int.Parse(rows) + int.Parse(startIndex) - 1, orderSql);
string countSql = string.Format(countSkeSql, wheresql);
PageRecords pr = new PageRecords();
pr.Order = order;
pr.Orderby = orderby;
pr.Rows = int.Parse(rows);
pr.Fields = fields;
pr.Values = values;
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString))
{
SqlCommand countComm = conn.CreateCommand();
countComm.Connection = conn;
SqlCommand qComm = conn.CreateCommand();
qComm.Connection = conn;
countComm.CommandText = countSql;
qComm.CommandText = sql;
conn.Open();
int total = int.Parse(countComm.ExecuteScalar().ToString());
pr.TotalRows = total;
IDataReader reader = qComm.ExecuteReader();
List <UDSAssetManagement> retValue = new List <UDSAssetManagement>();
while (reader.Read())
{
UDSAssetManagement ua = new UDSAssetManagement();
ua.Id = reader.GetInt32(0);
ua.Name = reader.GetString(1);
ua.Code = reader.GetString(2);
ua.Specification = reader.GetString(3);
ua.Number = reader.GetInt32(4);
ua.TotalPrice = reader.GetDecimal(5);
ua.TaxRate = reader.GetDecimal(6);
ua.Location = reader.GetString(7);
if (reader.IsDBNull(8))
{
ua.StartUsingTime = null;
}
else
{
ua.StartUsingTime = reader.GetDateTime(8);
}
if (reader.IsDBNull(9))
{
ua.BuyingTime = null;
}
else
{
ua.BuyingTime = reader.GetDateTime(9);
}
ua.Status = reader.GetInt32(10);
ua.UsingMan = reader.IsDBNull(11) ? "" : reader.GetString(11);
ua.Remark = reader.IsDBNull(12) ? "" : reader.GetString(12);
retValue.Add(ua);
}
pr.Records = retValue;
reader.Close();
conn.Close();
}
Response.ContentType = "application/json";
Response.Write(JsonConvert.SerializeObject(pr,
Formatting.Indented,
new IsoDateTimeConverter()
{
DateTimeFormat = "yyyy年MM月dd日"
}));
}
catch (Exception eX)
{
Response.StatusCode = 400;
Response.Write(eX.Message);
}
finally
{
Response.End();
}
#endregion
break;
case "s":
#region 保存
SqlConnection saveconn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString);
SqlTransaction trans = null;
try
{
UDSAssetManagement asset = JsonConvert.DeserializeObject <UDSAssetManagement>(Request.Params["target"]);
string sql = "insert into uds_assetmanagement" +
"(name,code,specification,number,totalprice,taxrate,location,startusingtime,buyingtime,status,usingman,remark) " +
"values('{0}', '{1}', '{2}', {3}, {4}, {5}, '{6}', {7}, {8}, {9}, '{10}', '{11}');SELECT SCOPE_IDENTITY();";
sql = string.Format(sql, asset.Name, asset.Code, asset.Specification, asset.Number, asset.TotalPrice,
asset.TaxRate, asset.Location,
asset.StartUsingTime.HasValue ? "'" + asset.StartUsingTime.Value.ToString("yyyy-MM-dd") + "'" : "null",
asset.BuyingTime.HasValue ? "'" + asset.BuyingTime.Value.ToString("yyyy-MM-dd") + "'" : "null",
asset.Status,
asset.UsingMan,
asset.Remark);
SqlCommand comm = saveconn.CreateCommand();
comm.Connection = saveconn;
comm.CommandText = sql;
saveconn.Open();
trans = saveconn.BeginTransaction();
comm.Transaction = trans;
int newid = int.Parse(comm.ExecuteScalar().ToString());
trans.Commit();
saveconn.Close();
Response.Write(newid.ToString());
}
catch (Exception eX)
{
if (null != trans)
{
trans.Rollback();
}
Response.StatusCode = 400;
Response.Write(eX.Message);
}
finally
{
if (ConnectionState.Open == saveconn.State)
{
saveconn.Close();
}
Response.End();
}
#endregion
break;
case "u":
#region 更新
SqlConnection updconn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString);
SqlTransaction updtrans = null;
try
{
UDSAssetManagement asset = JsonConvert.DeserializeObject <UDSAssetManagement>(Request.Params["target"]);
string sql = "update uds_assetmanagement" +
" set name = '{0}', code = '{1}', specification = '{2}', number = {3}, " +
"totalprice = {4}, taxrate = {5}, location = '{6}', startusingtime = {7}, buyingtime = {8}, " +
"status = {9}, usingman = '{10}', remark = '{11}' " +
"where id = {12}";
sql = string.Format(sql, asset.Name, asset.Code, asset.Specification, asset.Number, asset.TotalPrice,
asset.TaxRate, asset.Location,
asset.StartUsingTime.HasValue ? "'" + asset.StartUsingTime.Value.ToString("yyyy-MM-dd") + "'" : "null",
asset.BuyingTime.HasValue ? "'" + asset.BuyingTime.Value.ToString("yyyy-MM-dd") + "'" : "null",
asset.Status,
asset.UsingMan,
asset.Remark,
asset.Id);
SqlCommand comm = updconn.CreateCommand();
comm.Connection = updconn;
comm.CommandText = sql;
updconn.Open();
updtrans = updconn.BeginTransaction();
comm.Transaction = updtrans;
comm.ExecuteNonQuery();
updtrans.Commit();
updconn.Close();
}
catch (Exception eX)
{
if (null != updtrans)
{
updtrans.Rollback();
}
Response.StatusCode = 400;
Response.Write(eX.Message);
}
finally
{
if (ConnectionState.Open == updconn.State)
{
updconn.Close();
}
Response.End();
}
#endregion
break;
case "d":
#region 除
string amid = Request.Params["amid"];
SqlConnection delconn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString);
SqlTransaction deltrans = null;
try
{
if (!string.IsNullOrWhiteSpace(amid))
{
string delSql = "delete from uds_assetmanagement where id = {0}";
SqlCommand comm = delconn.CreateCommand();
comm.Connection = delconn;
comm.CommandText = string.Format(delSql, amid);
delconn.Open();
deltrans = delconn.BeginTransaction();
comm.Transaction = deltrans;
comm.ExecuteNonQuery();
deltrans.Commit();
delconn.Close();
}
}
catch (Exception eX)
{
if (null != deltrans)
{
deltrans.Rollback();
}
Response.StatusCode = 400;
Response.Write(eX.Message);
}
finally
{
if (ConnectionState.Open == delconn.State)
{
delconn.Close();
}
Response.End();
}
#endregion
break;
}
}
protected void Page_Load(object sender, EventArgs e)
{
string UserID = null == Request.Cookies["UserID"] ? "" : Request.Cookies["UserID"].Value;
switch (Request.HttpMethod)
{
case "GET":
string method = Request.Params["m"];
if (!string.IsNullOrWhiteSpace(method))
{
try
{
switch (method)
{
case "uuid":
string newuuid = Guid.NewGuid().ToString();
string dir = Path.Combine(Server.MapPath("~/App_Browsers"), newuuid);
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
Response.Write(newuuid);
break;
default:
Response.StatusCode = 400;
Response.Write("错误的请求");
break;
}
}
catch (Exception eX)
{
Response.StatusCode = 400;
Response.Write(eX.Message);
}
finally
{
Response.End();
}
}
else
{
string startIndex = Request.Params["startIndex"];
string orderby = Request.Params["orderby"];
string order = Request.Params["order"];
string rows = Request.Params["rows"];
string type = Request.Params["type"];
if (string.IsNullOrEmpty(startIndex))
{
startIndex = "1";
}
if (string.IsNullOrEmpty(rows))
{
rows = "10";
}
int rowstart = int.Parse(startIndex);
int rowend = int.Parse(rows) + rowstart - 1;
string sqlTemplate = "select * from " +
"(select bulletinid, subject, contents, createtime, sendtime, " +
"(select count(*) from uds_bulletinreadlist t where t.bulletinid = b.bulletinid {2}) as readcount," +
"ROW_NUMBER() over (order by sendtime desc) as rowno " +
"from uds_bulletin b) as A where rowno >= {0} and rowno <= {1} {3} order by sendtime desc";
string countTemplate = "select count(*) from uds_bulletin {0}";
string sql = "";
string countsql = "";
switch (type)
{
case "1":
//获取全部公告
sql = string.Format(sqlTemplate, rowstart, rowend, "", "");
countsql = string.Format(countTemplate, "");
break;
case "2":
//获取当前用户的全部未读公告
sql = string.Format(sqlTemplate, rowstart, rowend,
"and t.staffid = '" + UserID + "'", " and readcount = 0");
countsql = string.Format(countTemplate,
"where (bulletinid not in (select bulletinid from uds_bulletinreadlist where staffid = '" + UserID + "'))");
break;
case "3":
sql = string.Format(sqlTemplate, rowstart, rowend,
"and t.staffid = '" + UserID + "'", "");
//获取当前用户的全部公告
countsql = string.Format(countTemplate, "");
break;
case "5":
sql = "select bulletinid, subject, contents, createtime, sendtime, " +
"(select count(*) from uds_bulletinreadlist t where t.bulletinid = b.bulletinid) as readcount " +
"from uds_bulletin b where (bulletinid not in (select bulletinid from uds_bulletinreadlist where staffid = '" + UserID + "')) " +
"order by sendtime desc";
break;
}
switch (type)
{
case "1":
case "2":
case "3":
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString))
{
SqlCommand comm = new SqlCommand(string.Format(sql, rowstart, rowend), con);
SqlDataReader reader = null;
try
{
con.Open();
reader = comm.ExecuteReader();
IList <UDSBulletin> bulletins = new List <UDSBulletin>();
while (reader.Read())
{
UDSBulletin buletting = new UDSBulletin()
{
Bulletinid = reader.GetInt32(0),
Subject = reader.GetString(1),
Contents = reader.GetString(2),
Createtime = reader.GetDateTime(3).ToString(),
Sendtime = reader.GetDateTime(4).ToString(),
Readcount = reader.GetInt32(5)
};
bulletins.Add(buletting);
}
reader.Close();
comm.CommandText = countsql;
int totalCount = int.Parse(comm.ExecuteScalar().ToString());
con.Close();
PageRecords r = new PageRecords();
r.Order = order;
r.Orderby = orderby;
r.Rows = int.Parse(rows);
r.TotalRows = totalCount;
r.Records = bulletins;
var jsonSer = new Newtonsoft.Json.JsonSerializer();
StringWriter sw = new StringWriter();
using (JsonWriter jw = new JsonTextWriter(sw))
{
jw.Formatting = Formatting.Indented;
jsonSer.Serialize(jw, r);
}
Response.ContentType = "application/json";
Response.Write(sw.ToString());
sw.Close();
}
catch (Exception eX)
{
if (null != reader && !reader.IsClosed)
{
reader.Close();
}
if (System.Data.ConnectionState.Open == con.State)
{
con.Close();
}
Response.StatusCode = 400;
Response.ContentType = "text/html";
Response.Write(eX.Message);
}
finally
{
Response.End();
}
}
break;
case "4":
//获取指定ID的记录
string id = Request.Params["id"];
sql = string.Format("select bulletinid, subject, contents, createtime, sendtime from uds_bulletin where bulletinid = {0}", id);
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString))
{
SqlCommand comm = new SqlCommand(sql, con);
SqlDataReader reader = null;
SqlDataReader attachReader = null;
try
{
con.Open();
reader = comm.ExecuteReader();
UDSBulletin buletting = new UDSBulletin();
if (reader.Read())
{
buletting.Bulletinid = reader.GetInt32(0);
buletting.Subject = reader.GetString(1);
buletting.Contents = reader.GetString(2);
buletting.Createtime = reader.GetDateTime(3).ToString();
buletting.Sendtime = reader.GetDateTime(4).ToString();
reader.Close();
sql = string.Format(
"select bulletinid, attachmentid, attachmentname, attachmentpath from UDS_BulletinAttachment where bulletinid = {0}",
id);
comm.CommandText = sql;
attachReader = comm.ExecuteReader();
buletting.Attaches = new List <UDSBulletinAttaches>();
while (attachReader.Read())
{
UDSBulletinAttaches a = new UDSBulletinAttaches()
{
Bulletinid = attachReader.GetInt32(0),
Attachmentid = attachReader.GetInt32(1),
Attachmentname = attachReader.GetString(2),
Attachmentpath = attachReader.GetString(3)
};
buletting.Attaches.Add(a);
}
attachReader.Close();
}
else
{
reader.Close();
con.Close();
buletting = new UDSBulletin();
buletting.Attaches = new List <UDSBulletinAttaches>();
}
var jsonSer = new Newtonsoft.Json.JsonSerializer();
StringWriter sw = new StringWriter();
using (JsonWriter jw = new JsonTextWriter(sw))
{
jw.Formatting = Formatting.Indented;
jsonSer.Serialize(jw, buletting);
}
Response.ContentType = "application/json";
Response.Write(sw.ToString());
sw.Close();
}
catch (Exception eX)
{
if (null != reader)
{
reader.Close();
}
if (null != attachReader)
{
attachReader.Close();
}
Response.StatusCode = 400;
Response.ContentType = "text/html";
Response.Write(eX.Message);
}
finally
{
if (ConnectionState.Open == con.State)
{
con.Close();
}
Response.End();
}
}
break;
case "5":
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString))
{
SqlCommand comm = new SqlCommand(sql, con);
SqlDataReader reader = null;
try
{
con.Open();
reader = comm.ExecuteReader();
IList <UDSBulletin> bulletins = new List <UDSBulletin>();
while (reader.Read())
{
UDSBulletin buletting = new UDSBulletin()
{
Bulletinid = reader.GetInt32(0),
Subject = reader.GetString(1),
Contents = reader.GetString(2),
Createtime = reader.GetDateTime(3).ToString(),
Sendtime = reader.GetDateTime(4).ToString(),
Readcount = reader.GetInt32(5)
};
bulletins.Add(buletting);
}
reader.Close();
con.Close();
var jsonSer = new Newtonsoft.Json.JsonSerializer();
StringWriter sw = new StringWriter();
using (JsonWriter jw = new JsonTextWriter(sw))
{
jw.Formatting = Formatting.Indented;
jsonSer.Serialize(jw, bulletins);
}
Response.ContentType = "application/json";
Response.Write(sw.ToString());
sw.Close();
}
catch (Exception eX)
{
if (null != reader)
{
reader.Close();
}
Response.StatusCode = 400;
Response.Write(eX.Message);
}
finally
{
if (ConnectionState.Open == con.State)
{
con.Close();
}
Response.End();
}
}
break;
}
}
break;
case "POST":
string insertBulleting = "insert into UDS_Bulletin(subject, contents, createtime, sendtime) values('{0}', '', getDate(), getDate());SELECT SCOPE_IDENTITY();";
string insertAttache = "insert into UDS_BulletinAttachment(bulletinid, attachmentname, attachmentpath) values('{0}', '{1}', '{2}')";
string title = Request.Params["t"];
string uuid = Request.Params["uuid"];
string fileDir = Path.Combine(Server.MapPath("~/App_Browsers"), uuid);
string[] files = Directory.GetFiles(fileDir);
if (string.IsNullOrWhiteSpace(uuid))
{
Response.StatusCode = 400;
Response.Write("错误的公告发布，请刷新页面后重新尝试");
Response.End();
}
else
{
string exeSql = string.Format(insertBulleting, title);
SqlTransaction trans = null;
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString))
{
try
{
con.Open();
trans = con.BeginTransaction();
SqlCommand comm = con.CreateCommand();
comm.CommandText = exeSql;
comm.Transaction = trans;
int id = int.Parse(comm.ExecuteScalar().ToString());
foreach (string s in files)
{
exeSql = string.Format(insertAttache, id, Path.GetFileName(s), s);
comm.CommandText = exeSql;
comm.ExecuteNonQuery();
}
trans.Commit();
con.Close();
Response.Write("公告发布成功");
Response.End();
}
catch (Exception eX)
{
if (null != trans)
{
trans.Rollback();
}
con.Close();
Response.StatusCode = 400;
Response.Write(eX.Message);
Response.End();
}
}
}
break;
case "PUT":
break;
case "HEAD":
break;
case "DELETE":
break;
}
}