private ImportObject GetImportObject(int row, Worksheet ws, out ErrorObject errorObject, string sizeColumn, string sizeName)
{
ImportObject obj = new ImportObject();
StringBuilder errorMsg = new StringBuilder();
// Type
Range range = ws.get_Range("A" + row, "A" + row);
string name = range.Value2 != null ? range.Value2.ToString() : "";
if (name.Length > 500)
{
errorMsg.Append("Loại tối đa 500 kí tự!!");
}
else
{
obj.TypeName = name;
}
// Name
range = ws.get_Range("C" + row, "C" + row);
name = range.Value2 != null ? range.Value2.ToString() : "";
if (name.Length > 500)
{
errorMsg.Append("Tên tối đa 500 kí tự!!");
}
else
{
obj.ProductName = obj.TypeName + name;
}
// Color
range = ws.get_Range("F" + row, "F" + row);
name = range.Value2 != null ? range.Value2.ToString() : "";
if (name.Length > 500)
{
errorMsg.Append("Màu sắc tối đa 500 kí tự!!");
}
else
{
obj.ColorName = name;
}
// Price
range = ws.get_Range("C" + row, "C" + row);
name = range.Value2 != null ? range.Value2.ToString() : "";
int value = 0;
if (name == string.Empty || !Int32.TryParse(name, out value) || value <= 0)
{
errorMsg.Append("Giá bán phải là số >= 0, tối đa " + Int32.MaxValue + " !!");
}
else
{
obj.Price = value;
}
// Mass Price
range = ws.get_Range("D" + row, "D" + row);
name = range.Value2 != null ? range.Value2.ToString() : "";
value = 0;
if (name == string.Empty || !Int32.TryParse(name, out value) || value <= 0)
{
errorMsg.Append("Giá bán phải là số >= 0, tối đa " + Int32.MaxValue + " !!");
}
else
{
obj.MassPrice = value;
}
// Position
range = ws.get_Range("E" + row, "E" + row);
name = range.Value2 != null ? range.Value2.ToString() : "";
if (name.Length > 500)
{
errorMsg.Append("Màu sắc tối đa 500 kí tự!!");
}
else
{
obj.Position = name;
}
// Description
range = ws.get_Range("O" + row, "O" + row);
name = range.Value2 != null ? range.Value2.ToString() : "";
if (name.Length > 500)
{
errorMsg.Append("Màu sắc tối đa 500 kí tự!!");
}
else
{
obj.Position = name;
}
// Size
obj.SizeName = sizeName;
// Số lượng
range = ws.get_Range(sizeColumn + row, sizeColumn + row);
name = range.Value2 != null ? range.Value2.ToString() : "";
value = 0;
if (!Int32.TryParse(name, out value) || value < 0)
{
errorMsg.Append("Số lượng phải là số > 0, tối đa " + Int32.MaxValue + " !!");
}
else
{
if (value == 0)
{
errorObject = null;
return null;
}
obj.Quantity = value;
}
if (errorMsg.Length == 0)
{
errorObject = null;
}
else
{
errorObject = new ErrorObject{ErrorMessage = errorMsg.ToString(), RowNumber = row};
}
return obj;
}
private void importToDB(ImportObject obj, string stockInId)
{
object id = dal.GetSingleValue("Select type_id from product_type where type_name = '" + obj.TypeName.Replace("'" , "''") + "'");
if (id == null || id.ToString() == string.Empty)
{
if (obj.TypeName != string.Empty)
{
id = dal.GetSingleValue("Select max(type_id) from product_type ");
if (id == null || id.ToString() == string.Empty)
{
id = 1;
}
else
{
id = Convert.ToInt32(id.ToString()) + 1;
}
dal.ExecuteQuery("insert into product_type(type_id, type_name) values (" + id.ToString() + ", N'" + obj.TypeName.Replace("'", "''") + "')");
}
else
{
id = dal.GetSingleValue("Select type_id from product_type where type_id = 0");
if (id == null || id.ToString() == string.Empty)
{
id = 0;
dal.ExecuteQuery("insert into product_type(type_id, type_name) values (" + id.ToString() + ", 'Không xác định'" + "')");
}
}
}
obj.TypeId = Convert.ToInt32(id.ToString());
id = dal.GetSingleValue("Select color_id from product_color where color_name = '" + obj.ColorName.Replace("'", "''") + "'");
if (id == null || id.ToString() == string.Empty)
{
if (obj.ColorName != string.Empty)
{
id = dal.GetSingleValue("Select max(color_id) from product_color ");
if (id == null || id.ToString() == string.Empty)
{
id = 1;
}
else
{
id = Convert.ToInt32(id.ToString()) + 1;
}
dal.ExecuteQuery("insert into product_color(Color_id, Color_name) values (" + id.ToString() + ", N'" + obj.ColorName.Replace("'", "''") + "')");
}
else
{
id = dal.GetSingleValue("Select Color_id from product_Color where Color_id = 0");
if (id == null || id.ToString() == string.Empty)
{
id = 0;
dal.ExecuteQuery("insert into product_Color(Color_id, Color_name) values (" + id.ToString() + ", 'Không xác định'" + "')");
}
}
}
obj.ColorId = Convert.ToInt32(id.ToString());
id = dal.GetSingleValue("Select size_id from product_size where size_name = '" + obj.SizeName.Replace("'", "''") + "'");
if (id == null || id.ToString() == string.Empty)
{
if (obj.ColorName != string.Empty)
{
id = dal.GetSingleValue("Select max(size_id) from product_size ");
if (id == null || id.ToString() == string.Empty)
{
id = 1;
}
else
{
id = Convert.ToInt32(id.ToString()) + 1;
}
dal.ExecuteQuery("insert into product_size(size_id, size_name) values (" + id.ToString() + ", N'" + obj.SizeName.Replace("'", "''") + "')");
}
else
{
id = dal.GetSingleValue("Select size_id from product_size where size_id = 0");
if (id == null || id.ToString() == string.Empty)
{
id = 0;
dal.ExecuteQuery("insert into product_size(size_id, size_name) values (" + id.ToString() + ", 'Không xác định'" + "')");
}
}
}
obj.SizeId = Convert.ToInt32(id.ToString());
// product master
id = dal.GetSingleValue("Select product_master_id from product_master where size_id = "
+ obj.SizeId + " and color_id = " + obj.ColorId + " and type_id = " + obj.TypeId
+ " and product_name = '" + obj.ProductName + "'");
if (id == null || id.ToString() == string.Empty)
{
id = dal.GetSingleValue("Select max(product_master_id) from product_master ");
if (id == null)
{
id = 1;
}
else
{
id = Convert.ToInt32(id.ToString()) + 1;
}
dal.ExecuteQuery("insert into product_master(product_master_id, product_name, size_id, color_id, type_id, create_date) values ('"
+ string.Format("{0:0000000000000}", Convert.ToInt64(id.ToString()))
+ "', '" + obj.ProductName.Replace("'", "''")
+ "', " + obj.SizeId + ", " + obj.ColorId + ", " + obj.TypeId + ", '" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "')");
obj.ProductMasterId = string.Format("{0:0000000000000}", Convert.ToInt64(id.ToString()));
}
else
{
obj.ProductMasterId = id.ToString();
}
// product
string dateStr = obj.ProductMasterId.Substring(5) + buildProductId();
id = dal.GetSingleValue("Select max(product_id) from product where product_id >= " + dateStr + "00");
if (id == null || id.ToString() == string.Empty)
{
id = dateStr + "01";
}
else
{
id = Convert.ToInt64(id.ToString()) + 1;
}
dal.ExecuteQuery("insert into product(product_id, product_master_id, quantity, price, whole_sale_price, create_date) values ('"
+ string.Format("{0:00000000000}", Convert.ToInt64(id.ToString()))
+ "', '" + obj.ProductMasterId
+ "', " + obj.Quantity + ", " + obj.Price + ", " + obj.MassPrice + ", '" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "')");
obj.ProductId = string.Format("{0:00000000000}", Convert.ToInt64(id.ToString()));
// dept-price
id = dal.GetSingleValue("Select product_master_id from department_price where product_master_id = '" + obj.ProductMasterId + "'");
if (id == null || id.ToString() == string.Empty)
{
dal.ExecuteQuery("insert into department_price(department_id, product_master_id, price,whole_sale_price) values ("
+ "0, '" + obj.ProductMasterId + "', " + obj.Price + ", " + obj.MassPrice + ")");
}
else
{
if (obj.Price > 0)
{
dal.ExecuteQuery("update department_price set price = " + obj.Price
+ " where product_master_id = '" + obj.ProductMasterId + "'");
}
if (obj.MassPrice > 0)
{
dal.ExecuteQuery("update department_price set whole_sale_price = " + obj.MassPrice
+ " where product_master_id = '" + obj.ProductMasterId + "'");
}
}
// stock_in_detail
dal.ExecuteQuery("insert into stock_in_detail(stock_in_id, product_id, quantity, price, create_date) values ('"
+ stockInId + "', '"
+ obj.ProductId
+ "', " + obj.Quantity
+ ", " + obj.Price + ", '" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "')");
// stock
id = dal.GetSingleValue("Select max(stock_id) from stock");
if (id == null || id.ToString() == string.Empty)
{
id = 1;
}
else
{
id = Convert.ToInt64(id.ToString()) + 1;
}
dal.ExecuteQuery("insert into stock(stock_id, product_id, product_master_id, quantity, good_quantity, description, create_date) values ("
+ id + ", '"
+ obj.ProductId
+ "', '" + obj.ProductMasterId
+ "', " + obj.Quantity + ", " + obj.Quantity + ", '" + obj.Description + "', '" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "')");
}
private void importToDB(ImportObject obj, string stockInId, Dictionary<long, int> stockOutIdMap, out ErrorObject errorObject)
{
StringBuilder errorMsg = new StringBuilder();
object id = dal.GetSingleValue("Select type_id from product_type where type_name = '" + obj.TypeName.Replace("'" , "''") + "'");
if (id == null || id.ToString() == string.Empty)
{
errorMsg.Append("Không tồn tại loại!!");
} else
{
obj.TypeId = Convert.ToInt32(id.ToString());
}
id = dal.GetSingleValue("Select color_id from product_color where color_name = '" + obj.ColorName.Replace("'", "''") + "'");
if (id == null || id.ToString() == string.Empty)
{
errorMsg.Append("Không tồn tại màu sắc!!");
} else
{
obj.ColorId = Convert.ToInt32(id.ToString());
}
id = dal.GetSingleValue("Select size_id from product_size where size_name = '" + obj.SizeName.Replace("'", "''").Trim() + "'");
if (id == null || id.ToString() == string.Empty)
{
errorMsg.Append("Không tồn tại kích cỡ!!");
} else
{
obj.SizeId = Convert.ToInt32(id.ToString());
}
// product master
if (errorMsg.Length == 0)
{
string sqlPM = "Select product_master_id from product_master where size_id = "
+ obj.SizeId + " and color_id = " + obj.ColorId + " and type_id = " + obj.TypeId
+ " and product_name = '" + obj.ProductName + "'";
id = dal.GetSingleValue(sqlPM);
if (id == null || id.ToString() == string.Empty)
{
errorMsg.Append("Không tồn tại sản phẩm!!");
}
else
{
obj.ProductMasterId = id.ToString();
}
// stock
if (obj.ProductMasterId != null)
{
IList<IList<object>> updateList = new List<IList<object>>();
string sqlSl =
"select stock_id, product_id, quantity, good_quantity from stock where product_master_id = '" +
obj.ProductMasterId + "' order by create_date desc ";
IList<IList<object>> listStock =
dal.GetListValue(sqlSl, 4);
if (listStock != null && listStock.Count > 0)
{
long quantity = obj.Quantity;
foreach (IList<object> list in listStock)
{
if(quantity == 0 )
{
break;
}
long goodQty = Int64.Parse(list[3].ToString());
long stockQty = Int64.Parse(list[2].ToString());
if(goodQty == 0)
{
continue;
}
if (quantity >= goodQty)
{
list[3] = 0;
quantity -= goodQty;
stockQty -= goodQty;
if (stockQty < 0)
{
stockQty = 0;
}
list[2] = stockQty;
}
else
{
list[3] = goodQty - quantity;
stockQty -= quantity;
quantity = 0;
if (stockQty < 0)
{
stockQty = 0;
}
list[2] = stockQty;
}
updateList.Add(list);
if (quantity == 0)
{
break;
}
}
if (quantity > 0)
{
// error
}
else if (updateList.Count > 0)
{
foreach (IList<object> list in updateList)
{
string strUpdateStock = "update stock set quantity = " + list[2].ToString() +
", good_quantity = " + list[3].ToString()
+ " where stock_id = " + list[0].ToString() + "";
dal.ExecuteQuery(strUpdateStock);
int stockOutId = 0;
stockOutIdMap.TryGetValue(obj.deptId, out stockOutId);
if (stockOutId == 0)
{
stockOutId = GetStockOutId(obj.deptId);
stockOutIdMap[obj.deptId] = stockOutId;
}
id = dal.GetSingleValue("Select max(stock_out_detail_id) from stock_out_detail");
if (id == null || id.ToString() == string.Empty)
{
id = 1;
}
else
{
id = Convert.ToInt64(id.ToString()) + 1;
}
dal.ExecuteQuery("insert into stock_out_detail(stock_out_detail_id, stockout_id, product_id, product_master_id, quantity, good_quantity, create_date,update_date,create_id,update_id,exclusive_key,defect_status_id,description) values ("
+ id
+ ", " + stockOutId
+ ", '" + list[1]
+ "', '" + obj.ProductMasterId
+ "', " + obj.Quantity + ", " + obj.Quantity + ", '" +
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" +
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") +
"','admin','admin',1,0,'Export goods')");
}
}
}
}
}
if (errorMsg.Length == 0)
{
errorObject = null;
}
else
{
errorObject = new ErrorObject { ErrorMessage = errorMsg.ToString(), RowNumber = obj.Row };
}
}
