/*
* public void AddProductFromexcel(Detail_Product product, DateTime dateTime, int amount_init, int capital)
* {
* db.Detail_Product.Add(product);
* Input_Form input = new Input_Form();
* input.ID_Product = product.ID_Product;
* input.ID_Sup = product.ID_Supplier;
* input.Input_Date = dateTime;
* input.Amount = amount_init;
* //Tao id tự động cho Input
* ObservableCollection<Input_Form> Input = new ObservableCollection<Input_Form>(db.Input_Form);
* int count = Input.Count();
* string s1 = Input[count - 1].ID_Input;
* int s2 = Convert.ToInt32(s1.Remove(0, 2));
*
* if (s2 + 1 < 10)
* input.ID_Input = "Ip00" + (s2 + 1).ToString();
* else
* input.ID_Input = "Ip0" + (s2 + 1).ToString();
* db.Input_Form.Add(input);
* db.Detail_Product.Add(product);
* Type_product type = db.Type_product.FirstOrDefault(x => x.ID == product.ID_TypeProduct);
* type.Num_Of_Product += product.Amount_Current;
*
* db.SaveChanges();
* }*/
#region Product
public void AddProduct1(bool isEdit, string ID, string typeName, string Name_sup, DateTime dateTime, string Name, int Price, int amount_init, string descr, string img_Path)
{
SqlParameter id = new SqlParameter("@ID", ID);
string query = "FindIDType";
DataTable dataTable;
SqlParameter ID_type = new SqlParameter("@ID_type", DBMain.FindOneValue(query, CommandType.StoredProcedure, new SqlParameter("@NameType", typeName)));
//Find id sup
query = "Select * from Supplier where Name_Sup='" + Name_sup + "'";
dataTable = DBMain.ExecuteQueryDataSet(query, CommandType.Text);
SqlParameter ID_sup = new SqlParameter("@ID_sup", dataTable.Rows[0][2].ToString());
SqlParameter namepro = new SqlParameter("@name", Name);
SqlParameter price = new SqlParameter("@Org_price", Price);
SqlParameter descrip = new SqlParameter("@Descrp", descr);
SqlParameter Image = new SqlParameter("@Image_path", img_Path);
SqlParameter amount = new SqlParameter("@Amount", amount_init);
List <SqlParameter> parameters_product = new List <SqlParameter>();
parameters_product.Add(id);
parameters_product.Add(ID_sup);
parameters_product.Add(ID_type);
parameters_product.Add(namepro);
parameters_product.Add(price);
parameters_product.Add(descrip);
parameters_product.Add(Image);
// Nếu sửa
#region Edit
if (isEdit)
{
SqlParameter Input_date = new SqlParameter("@Input_date", dateTime);
query = "FindInputID";
SqlParameter ID_in = new SqlParameter("@ID", DBMain.FindOneValue(query, CommandType.StoredProcedure, new SqlParameter("@ID_Pro", ID)));
string ID_input = DBMain.FindOneValue(query, CommandType.StoredProcedure, new SqlParameter("@ID_Pro", ID));
query = "select * from Input_Form where ID_Input= '" + ID_input + "'";
//tăng số lượng sp lên
DataTable b = DBMain.ExecuteQueryDataSet(query, CommandType.Text);
int num = Int32.Parse(b.Rows[0][4].ToString());
num += amount_init;
amount = new SqlParameter("@Amount", num);
//Update vào input_form
List <SqlParameter> parameters_Input = new List <SqlParameter>();
parameters_Input.Add(ID_in);
parameters_Input.Add(ID_sup);
parameters_Input.Add(ID_type);
parameters_Input.Add(amount);
parameters_Input.Add(Input_date);
DBMain.MyExecuteNonQuery("Update_Input_Form", CommandType.StoredProcedure, parameters_Input);
//update Sản phẩm
parameters_product.Add(amount);
DBMain.MyExecuteNonQuery("Update_Detail_Product", CommandType.StoredProcedure, parameters_product);
DataTable oldProduct = DBMain.ExecuteQueryDataSet("Select * form Detail_Product where ID='" + ID + "'", CommandType.Text);
DataTable oldType = DBMain.ExecuteQueryDataSet("Select * form Type_Product where Type_Product='" + typeName + "'", CommandType.Text);
if (oldProduct.Rows[0][1].ToString() != oldType.Rows[0][0].ToString()) // Nếu có thay đổi mã sản phẩm
{
// Tăng mã mới
int t = Int32.Parse(oldType.Rows[0][2].ToString()) + amount_init;
SqlParameter NumofType = new SqlParameter("@Number", t);
List <SqlParameter> parameters_type = new List <SqlParameter>();
parameters_type.Add(ID_type);
parameters_type.Add(new SqlParameter("@Name", typeName));
parameters_type.Add(NumofType);
DBMain.MyExecuteNonQuery("Update_Type_Product", CommandType.StoredProcedure, parameters_type);
int oldAmount = int.Parse(oldType.Rows[0][2].ToString()) + amount_init; // Giảm mã cũ
NumofType = new SqlParameter("@Number", oldAmount);
SqlParameter oldtype_id = new SqlParameter("@ID", oldProduct.Rows[0][1].ToString());
List <SqlParameter> parameters_oldtype = new List <SqlParameter>();
parameters_oldtype.Add(oldtype_id);
parameters_oldtype.Add(new SqlParameter("@Name", oldType.Rows[0][1].ToString()));
parameters_oldtype.Add(NumofType);
DBMain.MyExecuteNonQuery("Update_Type_Product", CommandType.StoredProcedure, parameters_type);
}
}
#endregion
// Nếu thêm
#region Add
else // Nếu thêm
{
//THÊM SẢN PHẨM VÀO BẢNG Detail_product
query = "AddnewProduct";
parameters_product.Add(amount);
DBMain.MyExecuteNonQuery(query, CommandType.StoredProcedure, parameters_product);
//tự động tạo ID cho Input_form
DataTable Input_f = DBMain.ExecuteQueryDataSet("Select * from Input_Form", CommandType.Text);
string id_previous = Input_f.Rows[Input_f.Rows.Count - 1][0].ToString();
string s1 = id_previous;
int s2 = Convert.ToInt32(s1.Remove(0, 2));
string newID;
if (s2 + 1 < 10)
{
newID = "Ip00" + (s2 + 1).ToString();
}
else
{
newID = "Ip0" + (s2 + 1).ToString();
}
SqlParameter ID_in = new SqlParameter("@ID", newID);
SqlParameter Input_date = new SqlParameter("@Input_date", dateTime);
//Thêm vào input_form
query = "AddnewInput_Form";
List <SqlParameter> parameters_Input = new List <SqlParameter>();
parameters_Input.Add(ID_in);
parameters_Input.Add(new SqlParameter("@ID_pro", ID));
parameters_Input.Add(ID_sup);
// parameters_Input.Add(ID_type);
parameters_Input.Add(amount);
parameters_Input.Add(Input_date);
DBMain.MyExecuteNonQuery(query, CommandType.StoredProcedure, parameters_Input);
//Type_product
string t = DBMain.FindOneValue("FindIDType", CommandType.StoredProcedure, new SqlParameter("@NameType", typeName));
query = "select * from Type_Product where ID= '" + t + "'";
DataTable a = DBMain.ExecuteQueryDataSet(query, CommandType.Text);
int number = Int32.Parse(a.Rows[0][2].ToString()) + amount_init;
SqlParameter Number = new SqlParameter("@Number", number);
List <SqlParameter> parameters_Type = new List <SqlParameter>();
parameters_Type.Add(new SqlParameter("@ID", a.Rows[0][0]));
parameters_Type.Add(new SqlParameter("@Name", typeName));
parameters_Type.Add(Number);
DBMain.MyExecuteNonQuery("Update_Type_Product", CommandType.StoredProcedure, parameters_Type);
}
#endregion
//db.SaveChanges();
}