public void Delect(eSaleModel.Order AllData)
{
string SqlData = "Delect ";
string Where = "a.OrderID = " + AllData;
DoData(SqlData, Where, AllData);
}
public ActionResult GetUpdateDialog(int id)
{
eSalesService.Order2Service orderService = new eSalesService.Order2Service(this.GetDBConnectionString());
eSalesService.EmpService empService = new eSalesService.EmpService(this.GetDBConnectionString());
eSalesService.CusService cusService = new eSalesService.CusService(this.GetDBConnectionString());
eSalesService.ShipService shipService = new eSalesService.ShipService(this.GetDBConnectionString());
eSaleModel.Order orderData = new eSaleModel.Order();
var data = orderService.GetOrderById(id);
ViewBag.OrderId = data.OrderId;
ViewBag.OrderDate = data.OrderDate;
ViewBag.RequiredDate = data.RequiredDate;
ViewBag.ShippedDate = data.ShippedDate;
ViewBag.Freight = data.Freight;
ViewBag.ShipCountry = data.ShipCountry;
ViewBag.ShipCity = data.ShipCity;
ViewBag.ShipRegion = data.ShipRegion;
ViewBag.ShipPostalCode = data.ShipPostalCode;
ViewBag.ShipAddress = data.ShipAddress;
ViewBag.ShipName = data.ShipAddress;
ViewBag.EmpId = data.EmpId;
ViewBag.CustId = data.CustId;
ViewBag.ShipperId = data.ShipperId;
//ViewBag.OrderId = "updateProductGrid" + id;
//ViewBag.EmpNameData = new SelectList(empService.GetEmpNameData(), "Value", "Text", orderData.EmpId);
//ViewBag.CustNameData = new SelectList(cusService.GetCusNameData(), "Value", "Text", orderData.CustId);
//ViewBag.ShipperNameData = new SelectList(shipService.GetShipperNameData(), "Value", "Text", orderData.ShipperId);
return(PartialView());
}
public List <eSaleModel.Order> DoData(string SqlData, string Where, eSaleModel.Order AllData)
{
DataTable dt = new DataTable();
string Sql = @SqlData + "from Sales.Orders a inner join Sales.Customers b " +
"on b.CustomerID = a.CustomerID " +
"inner join HR.Employees c " +
"on c.EmployeeID = a.EmployeeID " +
"inner join Sales.Shippers d " +
"on d.ShipperID = a.ShipperID " +
Where;
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBconn"].ConnectionString);
using (conn)
{
conn.Open();
SqlCommand cmd = new SqlCommand(Sql, conn);
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
List <eSaleModel.Order> Data = new List <eSaleModel.Order>();
Data = MapOrderDataToList(dt);
return(Data);
}
/// <summary>
/// 依篩選條件取得訂單資料
/// </summary>
/// <param name="condition"></param>
/// <returns></returns>
public DataTable GetOrderByCondition(eSaleModel.Order condition)
{
DataTable dt = new DataTable();
string sql = @"SELECT * FROM Sales.Orders AS a JOIN Sales.Customers AS b ON a.CustomerID =b.CustomerID WHERE
(CAST(OrderId as CHAR) LIKE @OrderId OR @OrderId IS NULL) AND
(b.CompanyName LIKE @CustName OR @CustName IS NULL ) AND
(EmployeeId = @EmpId OR @EmpId = 0 ) AND
(ShipperId=@ShipperId OR @ShipperId = 0 ) AND
(OrderDate=@OrderDate OR @OrderDate = '1780-01-01' ) AND
(RequiredDate=@RequiredDate OR @RequiredDate = '1780-01-01' ) AND
(ShippedDate=@ShippedDate OR @ShippedDate = '1780-01-01' ) ";
using (SqlConnection conn = new SqlConnection(this.DbConn))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@OrderId", condition.O_OrderId == null ? string.Empty : condition.O_OrderId));
cmd.Parameters.Add(new SqlParameter("@CustName", condition.CustName == null ? string.Empty : condition.CustName));
cmd.Parameters.Add(new SqlParameter("@EmpId", condition.EmpId == null ? 0 : condition.EmpId));
cmd.Parameters.Add(new SqlParameter("@ShipperId", condition.ShipperId == null ? 0 : condition.ShipperId));
cmd.Parameters.Add(new SqlParameter("@OrderDate", condition.OrderDate == null ? Convert.ToDateTime("1780-01-01") : condition.OrderDate));
cmd.Parameters.Add(new SqlParameter("@RequiredDate", condition.RequiredDate == null ? Convert.ToDateTime("1780-01-01") : condition.RequiredDate));
cmd.Parameters.Add(new SqlParameter("@ShippedDate", condition.ShippedDate == null ? Convert.ToDateTime("1780-01-01") : condition.ShippedDate));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(dt);
}
public JsonResult InsertOrder(eSaleModel.Order orderData)
{
var orderService = new eSalesService.OrderService(this.GetDBConnectionString());
try
{
var result = new eSaleModel.Order();
result.CustId = Int32.Parse(orderData.CustName);
result.EmpId = Int32.Parse(orderData.EmpName);
result.OrderDate = orderData.OrderDate;
result.RequiredDate = orderData.RequiredDate;
result.ShippedDate = orderData.ShippedDate;
result.ShipperId = Int32.Parse(orderData.ShipperName);
result.Freight = orderData.Freight;
result.ShipCountry = orderData.ShipCountry;
result.ShipCity = orderData.ShipCity;
result.ShipRegion = orderData.ShipRegion;
result.ShipPostalCode = orderData.ShipPostalCode;
result.ShipAddress = orderData.ShipAddress;
var error = new eSaleModel.ViewModel.ErrorMsg();
error.Orderid = orderService.InsertOrder(result);
error.State = true;
return(this.Json(error, JsonRequestBehavior.AllowGet));
}catch (Exception e)
{
var error = new eSaleModel.ViewModel.ErrorMsg();
error.Describe = "尚未填寫完成";
error.State = false;
return(this.Json(error, JsonRequestBehavior.AllowGet));
}
}
public List <eSaleModel.Order> GetOrderByCondition(eSaleModel.Order condition)
{
List <eSaleModel.Order> result = new List <eSaleModel.Order>();
eSaleDao.OrderDao orderDao = new eSaleDao.OrderDao(this.DbConn);
var datalist = orderDao.GetOrderByCondition(condition);
return(this.MapOrderStore(datalist));
}
public JsonResult GetOrderById(int id)
{
var result = new eSaleModel.Order();
var orderService = new eSalesService.OrderService(this.GetDBConnectionString());
result = orderService.GetOrderById(id);
return(this.Json(result, JsonRequestBehavior.AllowGet));
}
public JsonResult GetOrder()
{
var result = new eSaleModel.Order();
var orderService = new eSalesService.OrderService(this.GetDBConnectionString());
var store = new eSaleModel.Store();
result.OrderId = 0;
result.CustName = "%" + null + "%";
result.EmpId = 0;
result.ShipperId = 0;
result.OrderDate = null;
result.RequiredDate = null;
result.ShippedDate = null;
store.identifier = "OrderId";
store.items = orderService.GetOrderByCondition(result);
return(this.Json(store, JsonRequestBehavior.AllowGet));
}
public JsonResult InsertOrderDetail(eSaleModel.ViewModel.ProductDetailWithId data)
{
var orderService = new eSalesService.OrderService(this.GetDBConnectionString());
eSaleModel.ViewModel.SearchOrderGrid searchOrderGrid = new eSaleModel.ViewModel.SearchOrderGrid();
eSaleModel.Order orderData = new eSaleModel.Order();
try
{
eSaleModel.OrderDetails orderDetail = new eSaleModel.OrderDetails();
orderDetail.OrderId = data.id;
orderDetail.ProductId = Convert.ToInt16(data.items[0].ProductName[0]);
orderDetail.UnitPrice = data.items[0].UnitPrice[0];
orderDetail.Qty = data.items[0].Qty[0];
orderService.InsertOrderDetail(orderDetail);
if (data.items.Count > 1)
{
for (int i = 1; i < data.items.Count; i++)
{
var orderService2 = new eSalesService.OrderService(this.GetDBConnectionString());
eSaleModel.OrderDetails orderDetail2 = new eSaleModel.OrderDetails();
orderDetail2.OrderId = data.id;
orderDetail2.ProductId = Convert.ToInt16(data.items[0]._S._arrayOfAllItems[i].ProductName[0]);
orderDetail2.UnitPrice = data.items[0]._S._arrayOfAllItems[i].UnitPrice[0];
orderDetail2.Qty = data.items[0]._S._arrayOfAllItems[i].Qty[0];
orderService2.InsertOrderDetail(orderDetail2);
}
}
searchOrderGrid.State = true;
searchOrderGrid.Order = orderService.GetOrderById(data.id);
return(this.Json(searchOrderGrid));
}
catch (Exception e)
{
var error = new eSaleModel.ViewModel.ErrorMsg();
error.Orderid = data.id;
error.State = false;
return(this.Json(error));
}
}
public JsonResult GetOrderByCondition(eSaleModel.Order condition)
{
var result = new eSaleModel.Order();
var orderService = new eSalesService.Order2Service(this.GetDBConnectionString());
var store = new eSaleModel.Store();
result.O_OrderId = "%" + Convert.ToString(condition.OrderId) + "%";
result.CustName = "%" + (condition.CustName) + "%";
result.EmpId = condition.EmpName == null ? 0 : Int32.Parse(condition.EmpName);
result.ShipperId = condition.ShipperName == null ? 0 : Int32.Parse(condition.ShipperName);
result.OrderDate = condition.OrderDate;
result.RequiredDate = condition.RequiredDate;
result.ShippedDate = condition.ShippedDate;
var data = orderService.GetOrderByCondition(result);
return(this.Json(data, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 修改訂單資料
/// </summary>
/// <param name="order"></param>
/// <returns></returns>
public int UpdateOrder(eSaleModel.Order order)
{
string sql = @" Update Sales.Orders SET
CustomerID=@CustomerID,
EmployeeID=@EmployeeID,
orderdate=@OrderDate,
requireddate=@Requireddate,
shippeddate=@Shippeddate,
shipperid=@Shipperid,
freight=@Freight,
shipname=@Shipname,
shipaddress=@Shipaddress,
shipcity=@Shipcity,
shipregion=@Shipregion,
shippostalcode=@Shippostalcode,
shipcountry=@Shipcountry
WHERE OrderId=@OrderId ";
int orderId;
using (SqlConnection conn = new SqlConnection(this.DbConn))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@CustomerID", order.CustId));
cmd.Parameters.Add(new SqlParameter("@EmployeeID", order.EmpId));
cmd.Parameters.Add(new SqlParameter("@OrderDate", order.OrderDate));
cmd.Parameters.Add(new SqlParameter("@RequireDdate", order.RequiredDate));
cmd.Parameters.Add(new SqlParameter("@ShippedDate", order.ShippedDate));
cmd.Parameters.Add(new SqlParameter("@ShipperId", order.ShipperId));
cmd.Parameters.Add(new SqlParameter("@Freight", order.Freight));
cmd.Parameters.Add(new SqlParameter("@Shipname", order.ShipName));
cmd.Parameters.Add(new SqlParameter("@ShipAddress", order.ShipAddress));
cmd.Parameters.Add(new SqlParameter("@ShipCity", order.ShipCity));
cmd.Parameters.Add(new SqlParameter("@ShipRegion", order.ShipRegion == null ? "" : order.ShipRegion));
cmd.Parameters.Add(new SqlParameter("@ShipPostalCode", order.ShipPostalCode));
cmd.Parameters.Add(new SqlParameter("@ShipCountry", order.ShipCountry));
cmd.Parameters.Add(new SqlParameter("@OrderId", order.OrderId));
Console.WriteLine(cmd);
var aa = cmd.ExecuteNonQuery();
return(aa);
}
}
/// <summary>
/// 新增訂單資料
/// </summary>
/// <param name="order"></param>
/// <returns></returns>
public int InsertOrder(eSaleModel.Order order)
{
string sql = @" Insert INTO Sales.Orders
(
CustomerID,EmployeeID,orderdate,requireddate,shippeddate,shipperid,freight,
shipname,shipaddress,shipcity,shipregion,shippostalcode,shipcountry
)
OUTPUT INSERTED.OrderID
VALUES
(
@CustomerID,@EmployeeID,@OrderDate,@Requireddate,@Shippeddate,@Shipperid,@Freight,
@Shipname,@Shipaddress,@Shipcity,@Shipregion,@Shippostalcode,@Shipcountry
)
" ;
int orderId;
using (SqlConnection conn = new SqlConnection(this.DbConn))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@CustomerID", order.CustId));
cmd.Parameters.Add(new SqlParameter("@EmployeeID", order.EmpId));
cmd.Parameters.Add(new SqlParameter("@OrderDate", order.OrderDate));
cmd.Parameters.Add(new SqlParameter("@RequireDdate", order.RequiredDate));
cmd.Parameters.Add(new SqlParameter("@ShippedDate", order.ShippedDate));
cmd.Parameters.Add(new SqlParameter("@ShipperId", order.ShipperId));
cmd.Parameters.Add(new SqlParameter("@Freight", order.Freight));
cmd.Parameters.Add(new SqlParameter("@Shipname", "aa"));
cmd.Parameters.Add(new SqlParameter("@ShipAddress", order.ShipAddress));
cmd.Parameters.Add(new SqlParameter("@ShipCity", order.ShipCity));
cmd.Parameters.Add(new SqlParameter("@ShipRegion", order.ShipRegion));
cmd.Parameters.Add(new SqlParameter("@ShipPostalCode", order.ShipPostalCode));
cmd.Parameters.Add(new SqlParameter("@ShipCountry", order.ShipCountry));
Console.WriteLine(cmd);
object aa = cmd.ExecuteScalar();
orderId = Convert.ToInt32(aa);
conn.Close();
}
return(orderId);
}
/// <summary>
/// 取得DB連線
/// </summary>
/// <returns></returns>
public List <eSaleModel.Order> Inquire(eSaleModel.Order AllData)
{
string SqlData = "Select OrderID, CustName, OrderDate, ShippedDate ";
string Where = "Where ";
if (AllData.OrderID != null)
{
Where += "a.OrderID = " + AllData.OrderID + " and ";
}
if (AllData.CustName != null)
{
Where += "CustName = 'Customer IBVRG'" + " and ";
}
if (!AllData.EmpName.Equals("null"))
{
Where += "a.EmployeeID = " + AllData.EmpName + " and ";
}
if (!AllData.CpyName.Equals("null"))
{
Where += "d.ShipperID = " + AllData.CpyName + " and ";
}
if (AllData.OrderDate != null)
{
Where += "OrderDate = '" + AllData.OrderDate.Substring(0, 10) + "' and ";
}
if (AllData.RequiredDate != null)
{
Where += "RequiredDate = '" + AllData.RequiredDate.Substring(0, 10) + "' and ";
}
if (AllData.ShippedDate != null)
{
Where += "ShippedDate = '" + AllData.ShippedDate.Substring(0, 10) + "' and ";
}
if (Where.Equals("Where "))
{
Where = "";
}
List <eSaleModel.Order> Data = DoData(SqlData, (Where.Substring(0, Where.Length - 5)), AllData);
return(Data);
}
public JsonResult UpdateOrder(eSaleModel.Order orderData)
{
var orderService = new eSalesService.OrderService(this.GetDBConnectionString());
try
{
var result = new eSaleModel.Order();
result.OrderId = orderData.OrderId;
result.CustId = Int32.Parse(orderData.CustName);
result.EmpId = Int32.Parse(orderData.EmpName);
result.OrderDate = orderData.OrderDate;
result.RequiredDate = orderData.RequiredDate;
result.ShippedDate = orderData.ShippedDate;
result.ShipperId = Int32.Parse(orderData.ShipperName);
result.Freight = orderData.Freight;
result.ShipCountry = orderData.ShipCountry;
result.ShipCity = orderData.ShipCity;
result.ShipRegion = orderData.ShipRegion;
result.ShipPostalCode = orderData.ShipPostalCode;
result.ShipAddress = orderData.ShipAddress;
result.ShipName = orderData.ShipName;
var error = new eSaleModel.ViewModel.ErrorMsg();
error.State = true;
error.Orderid = orderService.UpdateOrder(result);
//var aa = orderService.UpdateOrder(result);
return(this.Json(error, JsonRequestBehavior.AllowGet));
}
catch (Exception e)
{
var error = new eSaleModel.ViewModel.ErrorMsg();
//var msg = Convert.ToString(e);
//var start = msg.IndexOf("參數化查詢");
//var end = msg.IndexOf("必須有參數");
//error.Describe = msg.Substring(start+8,end-start-9);
error.Describe = "尚未填寫完成";
error.State = false;
return(this.Json(error, JsonRequestBehavior.AllowGet));
}
}
/// <summary>
/// 新增訂單資料
/// </summary>
/// <param name="data">訂單資料</param>
/// <returns></returns>
public int UpdateOrder(eSaleModel.Order data)
{
eSaleDao.OrderDao orderDao = new eSaleDao.OrderDao(this.DbConn);
return(orderDao.UpdateOrder(data));
}
