/// <summary>
/// 根据单据性质和单号，查询相应预警SQL的结果
/// </summary>
/// <param name="BillType">单据性质</param>
/// <param name="BillNumber">单号</param>
/// <returns>暂时返回的是一个SqlReader形式的结果集</returns>
public ActionResult TestSQL(string BillType, string BillNumber)
{
// DataSubscriptions sub = db.DataSubscriptions.Find(14);
DataSubscriptions sub = db.DataSubscriptions.Where(p => p.BillType.Equals(BillType) && p.BillCode.Equals(BillNumber)).Single();
string ConnString = base.ConnectionString;
SqlConnection myConn = new SqlConnection(ConnString);
SqlCommand myCommand = new SqlCommand(sub.SQL, myConn);
try
{
myConn.Open();
SqlDataReader reader = myCommand.ExecuteReader();
while (reader.Read())
{
string str = reader["Name"].ToString();
}
}
catch (System.Exception ex)
{
return(null);
}
finally
{
if (myConn.State == ConnectionState.Open)
{
myConn.Close();
}
}
return(View());
}
public ActionResult Edit(DataSubscriptions dataSubscriptions)
{
if (ModelState.IsValid)
{
db.Entry(dataSubscriptions).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(dataSubscriptions));
}
public ActionResult DeleteConfirmed(int id)
{
DataSubscriptions dataSubscriptions = db.DataSubscriptions.Find(id);
List <BillStaffMapping> tmp = db.BillStaffMappings.Where(p => p.BillType == dataSubscriptions.BillType && p.BillNumber == dataSubscriptions.BillCode).ToList();
db.BillStaffMappings.RemoveRange(tmp);
db.DataSubscriptions.Remove(dataSubscriptions);
db.SaveChanges();
return(RedirectToAction("Index"));
}
/**
* 参数中如果是[Bind(Include="") DataSubscriptions dataSubscriptions)的形式，含义其实是绑定某些视图中的值到模型中，并将这些值传入到控制器当中
* */
public ActionResult Create(DataSubscriptions dataSubscriptions)
{
/**
* 要注意的是：ModelState.IsValid的真假，是和model相关的，model中对属性的要求（非空、长度等等）必须和View中传过来一致
* 如果model中定义某个属性非空，但是在View中绑定的model中压根没有这个属性的值，ModelState.IsValid一定是为假的!
* */
dataSubscriptions.BillCode = "9999999999";
dataSubscriptions.BillType = "0008";
dataSubscriptions.NextSendDate = DateTime.Now;
if (ModelState.IsValid)
{
db.DataSubscriptions.Add(dataSubscriptions);
db.SaveChanges();
HttpPostedFileBase file = Request.Files["file"];
if (file != null)
{
string fileName = Path.Combine(Request.MapPath("~/files/tmp"), Path.GetFileName(file.FileName));
file.SaveAs(fileName);
using (ExcelPackage package = new ExcelPackage(new FileInfo(fileName)))
{
//注意！所有的起始位置都是1！所有的结束位置都是集合的元素长度值
ExcelWorksheet sheet = package.Workbook.Worksheets[1];
int colCount = sheet.Dimension.End.Column;
int rowCount = sheet.Dimension.End.Row;
for (ushort i = 2; i <= rowCount; i++)
{
BillStaffMapping mapping = new BillStaffMapping
{
BillType = dataSubscriptions.BillType,
BillNumber = dataSubscriptions.BillCode,
StaffNumber = sheet.GetValue(i, 1).ToString(), //从外部Excel表格获取员工工号
TelPhone = sheet.GetValue(i, 3).ToString(),
Email = sheet.GetValue(i, 4).ToString()
};
db.BillStaffMappings.Add(mapping);
db.SaveChanges();
}
if (!System.IO.File.Exists(fileName))
{
System.IO.File.Delete(fileName);
}
}
}
// this.UploadFile(Request["Receiver"]);
return(RedirectToAction("Index"));
}
return(View(dataSubscriptions));
}
// GET: DataSubscriptions/Details/5
public ActionResult Details(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
DataSubscriptions dataSubscriptions = db.DataSubscriptions.Find(id);
if (dataSubscriptions == null)
{
return(HttpNotFound());
}
return(View(dataSubscriptions));
}
