public bool ParseData(string[] data)
{
if (data.Length != 4)
{
return(false);
}
Regex regex = new Regex("^[a-zA-Z0-9_]+\\.[bmp]{1}");
if (!regex.IsMatch(data[0]))
{
return(false);
}
if (!int.TryParse(data[1], out int p1))
{
return(false);
}
if (!int.TryParse(data[2], out int p2))
{
return(false);
}
if (!int.TryParse(data[3], out int p3))
{
return(false);
}
this.data = new Model.Data(data[0], p1, p2, p3);
return(true);
}
public void DropDownListMakeCpy(List <Model.Data> Drop, Model.Data Data)
{
List <SelectListItem> CpyName = new List <SelectListItem>();
if (Data.CpyName == null)
{
Data.CpyName = "無";
Data.ShipperID = "0";
}
CpyName.Add(new SelectListItem()
{
Text = Data.CpyName.Replace("Shipper ", ""),
Value = Data.ShipperID
});
for (int i = 0; i < Drop.Count; i++)
{
string re = Drop[i].CpyName.Replace("Shipper ", "");
CpyName.Add(new SelectListItem()
{
Text = re,
Value = Drop[i].ShipperID
});
}
ViewBag.CpyName = CpyName;
}
// GET: Update
public ActionResult Index(Model.Data Data)
{
ViewBag.Data = Data;
DropDownListMakeEmp();
DropDOwnListMakeCpy();
return(View());
}
public static void UpdateData(Model.Data Data)
{
string query =
"BEGIN " +
"data_pkg.update_data('" + Data.Id + "', '" + Data.Data_Name + "', '" + Data.Data_Value + "'); " +
"END;";
DB_Handler.ExecuteQuery(query);
}
public string Insert(Model.Data Data)
{
string sql = "insert into Sales.Orders(";
string field = "CustomerID,EmployeeID,OrderDate,RequiredDate,ShipperID,ShipName";
string value = "'" + Data.CustomerID + "','" + Data.EmployeeID + "','" + Data.OrderDate + "','" + Data.RequiredDate + "','" + Data.ShipperID + "','無'";
string NewOrderId = "";
if (Data.ShippedDate != null)
{
field += ",ShippedDate";
value += ",'" + Data.ShippedDate + "'";
}
if (Data.Freight != null)
{
field += ",Freight";
value += ",'" + Data.Freight + "'";
}
if (Data.ShipAddress != null)
{
field += ",ShipAddress";
value += ",'" + Data.ShipAddress + "'";
}
if (Data.ShipCity != null)
{
field += ",ShipCity";
value += ",'" + Data.ShipCity + "'";
}
if (Data.ShipPostalCode != null)
{
field += ",ShipPostalCode";
value += ",'" + Data.ShipPostalCode + "'";
}
if (Data.ShipCountry != null)
{
field += ",ShipCountry";
value += ",'" + Data.ShipCountry + "'";
}
sql += field + ") values(" + value + ")";
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBconn"].ConnectionString);
DataTable dt = new DataTable();
using (conn)
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
sql = "select top 1 OrderID from Sales.Orders order by OrderID desc";
cmd = new SqlCommand(sql, conn);
sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
NewOrderId = dt.Rows[0]["OrderId"].ToString();
conn.Close();
}
return(NewOrderId);
}
// GET: Inquire
public ActionResult DoAndShow(Model.Data Data)
{
Service.SQL_Inquire SI = new Service.SQL_Inquire();
List <Model.Data> GetData = new List <Model.Data>();
//查詢
GetData = SI.GetData(Data);
ViewBag.GetData = GetData;
return(View());
}
public static Model.Data RowToEntity(DataRow row)
{
Model.Data Data = new Model.Data
{
Id = Int32.Parse(row["Id"].ToString()),
Data_Value = Int32.Parse(row["Data_Value"].ToString()),
Data_Name = row["Data_Name"].ToString(),
};
return(Data);
}
public void DataTest()
{
Model.Data model = new Model.Data()
{
ProjectName = "Project1",
WinCom = "WinCom1",
Money = 15,
Time = DateTime.Now
};
Assert.IsTrue(DAL.Data.Add(model));
}
public void Product(Model.Data Data)
{
string sql = "insert into Sales.OrderDetails(OrderId, ProductID, UnitPrice, Qty, Discount) values('" + Data.OrderId + "','" + Data.ProductID + "','" + Data.UnitPrice + "','" + Data.Qty + "','0.000')";
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBconn"].ConnectionString);
using (conn)
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
conn.Close();
}
}
public void Update(Model.Data Data)
{
string sql = "update Sales.Orders set CustomerID = '" + Data.CustomerID + "' , EmployeeID = '" + Data.EmployeeID + "' ,OrderDate = '" + Data.OrderDate + "' , RequiredDate = '" + Data.RequiredDate + "' , ShippedDate = '" + Data.ShippedDate + "' , ShipperID = '" + Data.ShipperID + "' , Freight = '" + "100" + "' , ShipAddress = '" + Data.ShipAddress + "' , ShipCity = '" + Data.ShipCity + "' , ShipRegion = '" + Data.ShipRegion + "' , ShipPostalCode = '" + Data.ShipPostalCode + "' , ShipCountry = '" + Data.ShipCountry + "' where OrderId = '" + Data.OrderId + "'";
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBconn"].ConnectionString);
using (conn)
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
conn.Close();
}
}
// GET: Inquire
public ActionResult Index(Model.Data Data, string ID)
{
Service.SQL_Inquire SI = new Service.SQL_Inquire();
List <Model.Data> GetData = new List <Model.Data>();
GetData = SI.GetData(Data);
if (GetData.Count < 1)
{
return(RedirectToAction("Non", "Inquire"));
}
ViewBag.GetData = GetData;
ViewBag.Data = Data;
return(View());
}
public List <Model.Data> GetData(Model.Data Data)
{
string where = "where ";
//整理空值
if (Data.CodeId != "null")
{
where += "CodeId = '" + Data.CodeId + "' and ";
}
if (Data.CompanyName != null)
{
where += "CompanyName = '" + Data.CompanyName + "' and ";
}
if (Data.CustomerID != null)
{
where += "CustomerID = '" + Data.CustomerID + "' and ";
}
if (Data.ContactName != null)
{
where += "ContactName = '" + Data.ContactName + "' and ";
}
if (where == "where ")
{
where = "where CodeType = 'TITLE'";
}
else
{
where += " CodeType = 'TITLE'";
}
string sql = "select CustomerID, CompanyName, ContactName, (CodeId+'-'+CodeVal) 'ContactTitle-CodeTable.CodeVal' from dbo.CodeTable a join sales.customers b on a.CodeId = b.ContactTitle " + where;
DataTable dt = new DataTable();
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();
}
return(FillData(dt));
}
// GET: Delete
public ActionResult Index(Model.Data Data, string ID)
{
Service.SQL_Delete SD = new Service.SQL_Delete();
SD.Delete(ID);
return(RedirectToAction("Index", "Inquire",
new
{
OrderId = Data.OrderId,
CustName = Data.CustName,
EmployeeID = Data.EmployeeID,
EmpName = Data.EmpName,
ShipperID = Data.ShipperID,
CpyName = Data.CpyName,
OrderDate = Data.OrderDate,
RequiredDate = Data.RequiredDate,
ShippedDate = Data.ShippedDate
}));
}
public void DropDownListMakeEmp(List <Model.Data> Drop, Model.Data Data)
{
List <SelectListItem> EmpName = new List <SelectListItem>();
EmpName.Add(new SelectListItem()
{
Text = Data.EmpName,
Value = Data.EmployeeID
});
for (int i = 0; i < Drop.Count; i++)
{
EmpName.Add(new SelectListItem()
{
Text = Drop[i].EmpName,
Value = Drop[i].EmployeeID
});
}
ViewBag.EmpName = EmpName;
}
public void DropDownListMakeCust(List <Model.Data> Drop, Model.Data Data)
{
List <SelectListItem> Cust = new List <SelectListItem>();
Cust.Add(new SelectListItem()
{
Text = Data.CustName.Replace("Customer ", ""),
Value = Data.CustomerID
});
for (int i = 0; i < Drop.Count; i++)
{
string re = Drop[i].CustName.Replace("Customer ", "");
Cust.Add(new SelectListItem()
{
Text = re,
Value = Drop[i].CustomerID
});
}
ViewBag.Cust = Cust;
}
// GET: Update
public ActionResult Index(Model.Data Data)
{
ViewBag.Data = Data;
Service.SQL_Inquire_Cpy SIC = new Service.SQL_Inquire_Cpy();
Service.SQL_Inquire_Emp SIE = new Service.SQL_Inquire_Emp();
Service.SQL_Inquire_Cust SICU = new Service.SQL_Inquire_Cust();
Service.SQL_Inquire_Pdt SIP = new Service.SQL_Inquire_Pdt();
List <Model.Data> Drop = new List <Model.Data>();
Drop = SIC.GetData(Data.ShipperID);
DropDownListMakeCpy(Drop, Data);
Drop = new List <Model.Data>();
Drop = SIE.GetData(Data.EmployeeID);
DropDownListMakeEmp(Drop, Data);
Drop = new List <Model.Data>();
Drop = SICU.GetData(Data.CustName);
DropDownListMakeCust(Drop, Data);
Drop = new List <Model.Data>();
Drop = SIP.GetData("");
DropDownListMakePdt(Drop);
string[] Price = new string[(Drop.Count + 1)];
for (int i = 1; i < Price.Length; i++)
{
Price[i] = Drop[i - 1].UnitPrice.ToString();
}
ViewBag.Price = Price;
Drop = new List <Model.Data>();
Drop = SIP.GetData(Data.OrderId);
ViewBag.result = Drop;
return(View());
}
// GET: Delete
public ActionResult Index(Model.Data Data)
{
Service.SQL_Delete SD = new Service.SQL_Delete();
SD.Delete(Data.OrderId);
if (Data.EmpName == null)
{
Data.EmpName = "null";
}
if (Data.CpyName == null)
{
Data.CpyName = "null";
}
return(RedirectToAction("Index", "Inquire",
new {
OrderId = Data.OrderId,
CustName = Data.CustName,
EmpName = Data.EmpName,
CpyName = Data.CpyName,
OrderDate = Data.OrderDate,
RequiredDate = Data.RequiredDate,
ShippedDate = Data.ShippedDate
}));
}
public ActionResult Update(Model.Data Data, string ID, string submit, string[] Pdt, string[] UnitPrice, string[] Qty)
{
if (submit == "true")
{
submit = "存檔";
}
else
{
submit = "刪除本筆訂單";
}
switch (submit)
{
case "存檔":
Service.SQL_Inquire SI = new Service.SQL_Inquire();
Service.SQL_Update SU = new Service.SQL_Update();
Service.SQL_Insert SIt = new Service.SQL_Insert();
List <Model.Data> LData = new List <Model.Data>();
Model.Data NData = new Model.Data();
NData.CustomerID = Data.CustName;
NData.EmployeeID = "null";
NData.ShipperID = "null";
LData = SI.GetData(NData);
Data.CustName = LData[0].CustName;
Data.CustomerID = LData[0].CustomerID;
NData = new Model.Data();
NData.EmployeeID = Data.EmpName;
NData.ShipperID = "null";
LData = SI.GetData(NData);
Data.EmpName = LData[0].EmpName;
Data.EmployeeID = LData[0].EmployeeID;
NData = new Model.Data();
NData.ShipperID = Data.CpyName;
NData.EmployeeID = "null";
LData = SI.GetData(NData);
Data.CpyName = LData[0].CpyName;
Data.ShipperID = LData[0].ShipperID;
Data.OrderId = ID;
SU.Update(Data);
SU.Delete(Data.OrderId);
if (Pdt != null)
{
for (int i = 0; i < Pdt.Length; i++)
{
Data.ProductID = Pdt[i];
Data.UnitPrice = UnitPrice[i];
Data.Qty = Qty[i];
SIt.Product(Data);
}
}
return(RedirectToAction("index", "Inquire", new { OrderId = ID, ShipperID = "null", EmployeeID = "null" }));
case "刪除本筆訂單":
Service.SQL_Delete SD = new Service.SQL_Delete();
SD.Delete(ID);
return(RedirectToAction("Index", "Inquire", new { OrderId = Data.OrderId }));
}
return(null);
}
public ActionResult Insert(Model.Data Data, string[] Pdt, string[] UnitPrice, string[] Qty)
{
Service.SQL_Inquire SI = new Service.SQL_Inquire();
Service.SQL_Insert SIt = new Service.SQL_Insert();
List <Model.Data> LData = new List <Model.Data>();
Model.Data NData = new Model.Data();
NData.CustomerID = Data.CustName;
NData.EmployeeID = "null";
NData.ShipperID = "null";
LData = SI.GetData(NData);
Data.CustName = LData[0].CustName;
Data.CustomerID = LData[0].CustomerID;
NData = new Model.Data();
NData.EmployeeID = Data.EmpName;
NData.ShipperID = "null";
LData = SI.GetData(NData);
Data.EmpName = LData[0].EmpName;
Data.EmployeeID = LData[0].EmployeeID;
NData = new Model.Data();
NData.ShipperID = Data.CpyName;
NData.EmployeeID = "null";
LData = SI.GetData(NData);
if (Data.CpyName == "null")
{
Data.CpyName = "無";
Data.ShipperID = "";
}
else
{
Data.CpyName = LData[0].CpyName;
Data.ShipperID = LData[0].ShipperID;
}
if (Data.ShipCountry == null)
{
Data.ShipCountry = "無";
}
if (Data.Freight == null)
{
Data.Freight = "0";
}
Data.Freight = "100";
if (Data.ShipAddress == null)
{
Data.ShipAddress = "無";
}
if (Data.ShipCity == null)
{
Data.ShipCity = "無";
}
Data.OrderId = SIt.Insert(Data);
if (Pdt != null)
{
for (int i = 0; i < Pdt.Length; i++)
{
Data.ProductID = Pdt[i];
Data.UnitPrice = UnitPrice[i];
Data.Qty = Qty[i];
SIt.Product(Data);
}
}
return(RedirectToAction("index", "Inquire", new { OrderId = Data.OrderId, ShipperID = "null", EmployeeID = "null" }));
}
public void Do(Model.Data Data)
{
}
public ActionResult Do(Model.Data Data)
{
return(null);
}
private void CrateData(Model.Data data)
{
throw new NotImplementedException();
}
public List <Model.Data> GetData(Model.Data Data)
{
string sql = "Select * ";
string Where = "Where";
if (Data.OrderId != null)
{
Where += " a.OrderID = " + Data.OrderId;
}
if (Data.CustName != null)
{
Where += " and CustName = 'Customer IBVRG'";
}
if (!Data.EmpName.Equals("null"))
{
Where += " and a.EmployeeID = " + Data.EmpName;
}
if (!Data.CpyName.Equals("null"))
{
Where += " and d.ShipperID = " + Data.CpyName;
}
if (Data.OrderDate != null)
{
Where += " and OrderDate = '" + Data.OrderDate;
}
if (Data.RequiredDate != null)
{
Where += " and RequiredDate = '" + Data.RequiredDate;
}
if (Data.ShippedDate != null)
{
Where += " and ShippedDate = '" + Data.ShippedDate.Substring(0, 10) + "'";
}
if (Where.Equals("Where "))
{
Where = "";
}
if ((Where.IndexOf("Where and")) > -1)
{
Where = Where.Replace("Where and", "Where ");
}
DataTable dt = new DataTable();
sql = sql + "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 <Model.Data> AddData = new List <Model.Data>();
AddData = SearchData(dt);
return(AddData);
}
