private void SetCustomerDg2()
{
// Get customers that has no sales assign to from ForecastOrder table
DataTable dt = ForecastOrderBLL.getCustomerWithNoSalesFo();
if (dt.Rows.Count != 0)
{
dgCustomerData2.ItemsSource = dt.DefaultView;
// Set sales dropdown in datagrid
dt = SalesBLL.getSalesAll();
var Sales = new List <string>();
Sales.Add("==請選擇業務==");
foreach (DataRow dr in dt.Rows)
{
Sales.Add(dr[0].ToString() + "-" + dr[1].ToString());
}
dgCbSales2.ItemsSource = Sales;
btnSave2.IsEnabled = true;
}
else
{
btnSave2.IsEnabled = false;
}
dt = null;
}
private void btnSave2_Click(object sender, RoutedEventArgs e)
{
// Update ForecastOrder set SalesId and CustomerId
string customerId = "";
string customerName = "";
string[] sales = null;
string salesId = "";
CustomerBEL objBEL = new CustomerBEL();
for (int i = 0; i < dgCustomerData2.Items.Count; i++)
{
DataGridCell cell0 = FunctionClass.GetCell(dgCustomerData2, i, 0);
DataGridCell cell1 = FunctionClass.GetCell(dgCustomerData2, i, 1);
DataGridCell cell2 = FunctionClass.GetCell(dgCustomerData2, i, 2);
TextBlock tb0 = cell0.Content as TextBlock;
TextBlock tb1 = cell1.Content as TextBlock;
ComboBox cb = cell2.Content as ComboBox;
customerId = tb0.Text.Trim();
customerName = tb1.Text.Trim();
if (cb.SelectedValue != null)
{
sales = cb.SelectedValue.ToString().Split('-');
salesId = sales[0].ToString().Trim();
ForecastOrderBLL.updateForecastOrder(salesId, customerId, customerName);
objBEL.Id = customerId;
objBEL.Name = customerName;
objBEL.CountryId = "";
objBEL.IsEnable = "1";
objBEL.SalesId = salesId;
CustomerBLL.insertCustomer(objBEL);
}
}
}
// insert using SqlBulkCopy
private void ImportExcel(string file, string type)
{
DataTable dt = new DataTable();
string tableName = "";
if (type == "po")
{
tableName = "tmpPurchaseOrder";
dt = GetPurchaseOrderExcel(file);
PurchaseOrderBLL.deleteAllTmpPurchaseOrder();
}
else
{
tableName = "tmpForecastOrder";
dt = GetForecastOrderExcel(file);
ForecastOrderBLL.deleteAllTmpForecastOrder();
}
if (dt.Rows.Count > 0)
{
try
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
{
con.Open();
using (SqlBulkCopy copy = new SqlBulkCopy(con))
{
int columnCount = dt.Columns.Count;
for (int i = 0; i < columnCount; i++)
{
copy.ColumnMappings.Add(i, i);
}
copy.DestinationTableName = tableName;
copy.WriteToServer(dt);
}
con.Close();
}
System.Windows.Forms.MessageBox.Show("資料匯入完畢，共匯入 " + dt.Rows.Count + " 筆資料", "Message");
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("檔案匯入資料庫失敗", "Message");
}
finally
{
dt = null;
}
}
else
{
}
if (type == "po")
{
// This T-SQL do :
// 1.Copy tmp data to real table, tmpPurchaseOrder => PurchaseOrder
// 2.Insert new customer, sales, product
PurchaseOrderBLL.updateBasicDataByPo();
SetCustomerDg1();
}
else
{
// This T-SQL do :
// 1.copy tmp data to real table, tmpForecastOrder => ForecastOrder
// 2.insert new customer, sales
ForecastOrderBLL.updateBasicDataByFo();
SetCustomerDg2();
}
}
