public void inputbasepricelistdetail(object obj)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string);
datainportF inf = (datainportF)obj;
string filename = inf.filename;
// string filename = theDialog.FileName.ToString();
// string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
dc.ExecuteCommand("DELETE FROM tbl_KAbaseprice");
// dc.tblFBL5Nnewthisperiods.DeleteAllOnSubmit(rsthisperiod);
dc.CommandTimeout = 0;
dc.SubmitChanges();
ExcelProvider ExcelProvide = new ExcelProvider();
//#endregion
System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename);
DataTable batable = new DataTable();
batable.Columns.Add("PriceList", typeof(string));
batable.Columns.Add("Material", typeof(string));
batable.Columns.Add("MaterialNAme", typeof(string));
batable.Columns.Add("Amount", typeof(double));
batable.Columns.Add("Unit", typeof(string));
batable.Columns.Add("UoM", typeof(string));
batable.Columns.Add("Valid_From", typeof(DateTime));
batable.Columns.Add("Valid_to", typeof(DateTime));
string Pricelist = "";
int columpricelist = 0;
int columpmaterial = 0;
int columname = 0;
int columpamount = 0;
int columunit = 0;
int columUoM = 0;
int columValid_From = 0;
int columValid_to = 0;
int headindex = 0;
for (int rowid = 0; rowid < sourceData.Rows.Count; rowid++)
{
headindex = 1;
for (int columid = 0; columid < sourceData.Columns.Count; columid++)
{
string value = sourceData.Rows[rowid][columid].ToString();
if (value != null)
{
#region setcolum
if (value.Trim() == "CnTy")
{
columpricelist = columid;
headindex = 0;
}
if (value.Trim() == "Material")
{
if (columname == 0)
{
columpmaterial = columid;
headindex = 0;
}
}
if (value.Trim() == "Material")
{
if (columpmaterial != 0)
{
columname = columid;
headindex = 0;
}
}
if (value.Trim() == "Amount")
{
columpamount = columid;
headindex = 0;
}
if (value.Trim() == "Unit")
{
columunit = columid;
headindex = 0;
}
if (value.Trim() == "UoM")
{
columUoM = columid;
headindex = 0;
}
if (value.Trim() == "Valid From")
{
columValid_From = columid;
headindex = 0;
}
if (value.Trim() == "Valid to")
{
columValid_to = columid;
headindex = 0;
}
#endregion
// view basetable
}
//------------
} // colum
#region setvalue of pricelist
// string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
string valuepricelist = sourceData.Rows[rowid][columpricelist].ToString();
if (headindex != 0 && valuepricelist != "" && valuepricelist != "YPR0")
{
Pricelist = valuepricelist;
}
if (headindex != 0 && valuepricelist == "YPR0")
{
DataRow dr = batable.NewRow();
dr["PriceList"] = Pricelist.Trim();
// dr["Material"] = Utils.GetValueOfCellInExcel(worksheet, rowid, columpmaterial);
dr["Material"] = sourceData.Rows[rowid][columpmaterial].ToString().Trim();
dr["MaterialNAme"] = sourceData.Rows[rowid][columname].ToString().Trim(); //Utils.GetValueOfCellInExcel(worksheet, rowid, columname);
dr["Amount"] = double.Parse(sourceData.Rows[rowid][columpamount].ToString()); // Utils.GetValueOfCellInExcel(worksheet, rowid, columpamount);
dr["Unit"] = sourceData.Rows[rowid][columunit].ToString().Trim(); // Utils.GetValueOfCellInExcel(worksheet, rowid, columunit);
dr["UoM"] = sourceData.Rows[rowid][columUoM].ToString().Trim(); // Utils.GetValueOfCellInExcel(worksheet, rowid, columUoM);
dr["Valid_From"] = Utils.chageExceldatetoData(sourceData.Rows[rowid][columValid_From].ToString()); // Utils.GetValueOfCellInExcel(worksheet, rowid, columValid_From);
dr["Valid_to"] = Utils.chageExceldatetoData(sourceData.Rows[rowid][columValid_to].ToString()); // Utils.GetValueOfCellInExcel(worksheet, rowid, columValid_to);
batable.Rows.Add(dr);
}
#endregion
}// row
//conpy to server
string destConnString = Utils.getConnectionstr();
//adapter.FillSchema(sourceData, SchemaType.Source);
//sourceData.Columns["Posting Date"].DataType = typeof(DateTime);
//sourceData.Columns["Invoice Doc Nr"].DataType = typeof(float);
//sourceData.Columns["Billed Qty"].DataType = typeof(float);
//sourceData.Columns["Cond Value"].DataType = typeof(float);
//sourceData.Columns["Sales Org"].DataType = typeof(string);
//sourceData.Columns["Cust Name"].DataType = typeof(string);
//sourceData.Columns["Outbound Delivery"].DataType = typeof(string);
//sourceData.Columns["Mat Group"].DataType = typeof(string);
//sourceData.Columns["Mat Group Text"].DataType = typeof(string);
//sourceData.Columns["UoM"].DataType = typeof(string);
//---------------fill data
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
bulkCopy.DestinationTableName = "tbl_KAbaseprice";
// Write from the source to the destination.
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.ColumnMappings.Add("PriceList", "PriceList");
bulkCopy.ColumnMappings.Add("Material", "Material");
bulkCopy.ColumnMappings.Add("MaterialNAme", "MaterialNAme");
bulkCopy.ColumnMappings.Add("Amount", "Amount");
bulkCopy.ColumnMappings.Add("Unit", "Unit");
bulkCopy.ColumnMappings.Add("UoM", "UoM");
bulkCopy.ColumnMappings.Add("Valid_From", "[Valid From]");
bulkCopy.ColumnMappings.Add("Valid_to", "[Valid to]");
//bulkCopy.ColumnMappings.Add("Valid_to", "Valid_to");
//bulkCopy.ColumnMappings.Add("Valid_to", "Valid_to");
//bulkCopy.ColumnMappings.Add("Valid_to", "Valid_to");
//bulkCopy.ColumnMappings.Add("Valid_to", "Valid_to");
try
{
bulkCopy.WriteToServer(batable);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
//copy to server
// string connection_string = Utils.getConnectionstr();
// LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
// var typeffmain = typeof(tbl_KAbaseprice);
// var typeffsub = typeof(tbl_KAbaseprice);
// VInputchange inputcdata1 = new VInputchange("", "Base price list", dc, "tbl_KAbaseprice", "tbl_KAbaseprice", typeffmain, typeffsub, "id", "id", "");
// inputcdata1.ShowDialog();
// View.Viewdatatable TB = new View.Viewdatatable(batable, "lIST DATA");
// TB.ShowDialog();
// }
}
private void importsexcel(object obj)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string);
Product md = new Product();
bool kq = md.Deleteprodctlist();
datainportF inf = (datainportF)obj;
string filename = inf.filename;
string connectionString = "";
if (filename.Contains(".xlsx") || filename.Contains(".XLSX"))
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";" + "Extended Properties=Excel 12.0;";
}
else
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " + filename + ";" + "Extended Properties=Excel 8.0;";
}
//------
//---------------fill data
System.Data.DataTable sourceData = new System.Data.DataTable();
using (OleDbConnection conn =
new OleDbConnection(connectionString))
{
try
{
conn.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Open conext !", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
// Get the data from the source table as a SqlDataReader.
OleDbCommand command = new OleDbCommand(
@"SELECT MatNumber, MatText ,UoM,Pcrate,Litter,UnitSize,PackUnit,
Ucrate FROM [Sheet1$]
WHERE ( MatNumber is not null ) ", conn); // AND ISNUMERIC (MatNumber)
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
// adapter.FillSchema(sourceData, SchemaType.Source);
// sourceData.Columns["Posting Date"].DataType = typeof(DateTime);
//sourceData.Columns["Invoice Doc Nr"].DataType = typeof(float);
//sourceData.Columns["Billed Qty"].DataType = typeof(float);
//sourceData.Columns["Cond Value"].DataType = typeof(float);
//sourceData.Columns["Sales Org"].DataType = typeof(string);
//sourceData.Columns["Cust Name"].DataType = typeof(string);
//sourceData.Columns["Outbound Delivery"].DataType = typeof(string);
//sourceData.Columns["Mat Group"].DataType = typeof(string);
//sourceData.Columns["Mat Group Text"].DataType = typeof(string);
//sourceData.Columns["UoM"].DataType = typeof(string);
try
{
adapter.Fill(sourceData);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Fill !", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
conn.Close();
}
// Utils util = new Utils();
string destConnString = Utils.getConnectionstr();
//---------------fill data
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
//@"SELECT MatNumber, MatText ,UoM,Pcrate,
// Ucrate FROM [Sheet1$]
// WHERE ( MatNumber is not null ) AND ISNUMERIC (MatNumber)", conn); //
bulkCopy.DestinationTableName = "tbl_kaProductlist";
// Write from the source to the destination.
bulkCopy.ColumnMappings.Add("MatNumber", "MatNumber");
bulkCopy.ColumnMappings.Add("MatText", "MatText");
bulkCopy.ColumnMappings.Add("UoM", "UoM");
bulkCopy.ColumnMappings.Add("Pcrate", "Pcrate");
bulkCopy.ColumnMappings.Add("Ucrate", "Ucrate");
bulkCopy.ColumnMappings.Add("PackUnit", "PackUnit");
bulkCopy.ColumnMappings.Add("Litter", "Litter");
bulkCopy.ColumnMappings.Add("UnitSize", "UnitSize");
// ,
#region tìm id
// "Account"
// "Assignment"
// "Posting Date"
// "Document Type"
// "Document Number"
// "Business Area"
// "Amount in local currency"
#endregion
try
{
bulkCopy.WriteToServer(sourceData);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
}
private void importsexcel2(object obj)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string);
Salesinput_ctrl md = new Salesinput_ctrl();
bool kq = md.deleteedlp();
//if (!kq)
//{
// MessageBox.Show("Không xóa được bảng Edlpinput!", "Thông báo ", MessageBoxButtons.OK, MessageBoxIcon.Information);
//}
datainportF inf = (datainportF)obj;
string filename = inf.filename;
string connectionString = "";
if (filename.Contains(".xlsx") || filename.Contains(".XLSX"))
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";" + "Extended Properties=Excel 12.0;";
}
else
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " + filename + ";" + "Extended Properties=Excel 8.0;";
}
//------
//---------------fill data
ExcelProvider ExcelProvide = new ExcelProvider();
//#endregion
System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename);
// Sales Group Sales Group desc Sales Off Sales Office Desc
System.Data.DataTable batable = new System.Data.DataTable();
// batable.Columns.Add("Soldto", typeof(double));
batable.Columns.Add("ContractNo", typeof(string));
batable.Columns.Add("BatchNo", typeof(double));
batable.Columns.Add("PayType", typeof(string));
batable.Columns.Add("PaidRequestAmt", typeof(double));
batable.Columns.Add("PaidNote", typeof(string));
batable.Columns.Add("PaymentDoc", typeof(string));
// PaymentDoc
string username = Utils.getusername();
batable.Columns.Add("Username", typeof(string));
batable.Columns["Username"].DefaultValue = username;
batable.Columns.Add("status", typeof(Boolean));
batable.Columns["status"].DefaultValue = false;
#region setcolum
int PaymentDocid = -1;
int ContractNoid = -1;
int BatchNoid = -1;
int PayTypeid = -1;
int PaidRequestAmtid = -1;
int PaidNoteid = -1;
// View.Viewdatatable vi1 = new View.Viewdatatable(sourceData, "Test");
// vi1.ShowDialog();
// int headindex = -2;
for (int rowid = 0; rowid < 3; rowid++)
{
// headindex = 1;
for (int columid = 0; columid < sourceData.Columns.Count; columid++)
{
string value = sourceData.Rows[rowid][columid].ToString();
// MessageBox.Show(value +":"+ rowid);
if (value != null)
{
#region setcolum
if (value.Trim() == "ContractNo")
{
ContractNoid = columid;
// headindex = rowid;
}
if (value.Trim() == ("BatchNo"))
{
BatchNoid = columid;
// headindex = rowid;
}
if (value.Trim() == ("PayType"))
{
PayTypeid = columid;
// headindex = rowid;
}
if (value.Trim() == ("PaidRequestAmt"))
{
PaidRequestAmtid = columid;
// headindex = rowid;
}
if (value.Trim() == "PaidNote")
{
PaidNoteid = columid;
// headindex = rowid;
}
if (value.Trim() == "PaymentDoc")
{
PaymentDocid = columid;
// headindex = rowid;
}
#endregion
}
} // colum
} // roww off heatder
#endregion
if (ContractNoid == -1)
{
MessageBox.Show("Please check ContractNo colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (BatchNoid == -1)
{
MessageBox.Show("Please check BatchNo colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (PayTypeid == -1)
{
MessageBox.Show("Please check PayType colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (PaidRequestAmtid == -1)
{
MessageBox.Show("Please check PaidRequestAmt colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (PaidNoteid == -1)
{
MessageBox.Show("Please check PaidNote colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (PaymentDocid == -1)
{
MessageBox.Show("Please check PaymentDoc colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++)
{
#region setvalue of massconfirm
// string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
string PaidRequestAmt = sourceData.Rows[rowixd][PaidRequestAmtid].ToString();
if (PaidRequestAmt != "" && Utils.IsValidnumber(PaidRequestAmt))
{
DataRow dr = batable.NewRow();
dr["ContractNo"] = sourceData.Rows[rowixd][ContractNoid].ToString().Trim();
dr["BatchNo"] = double.Parse(sourceData.Rows[rowixd][BatchNoid].ToString().Trim());
dr["PayType"] = sourceData.Rows[rowixd][PayTypeid].ToString().Trim();
dr["PaidRequestAmt"] = double.Parse(sourceData.Rows[rowixd][PaidRequestAmtid].ToString().Trim());
dr["PaidNote"] = sourceData.Rows[rowixd][PaidNoteid].ToString().Trim();
dr["PaymentDoc"] = sourceData.Rows[rowixd][PaymentDocid].ToString().Trim();
batable.Rows.Add(dr);
}
#endregion
}// row
string destConnString = Utils.getConnectionstr();
//---------------fill data
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.DestinationTableName = "tbl_MassConfirmTemp";
// Write from the source to the destination.
bulkCopy.ColumnMappings.Add("ContractNo", "ContractNo");
bulkCopy.ColumnMappings.Add("BatchNo", "BatchNo");
bulkCopy.ColumnMappings.Add("PayType", "PayType");
bulkCopy.ColumnMappings.Add("PaidRequestAmt", "PaidRequestAmt");
bulkCopy.ColumnMappings.Add("PaidNote", "PaidNote");
bulkCopy.ColumnMappings.Add("PaymentDoc", "PaymentDoc");
bulkCopy.ColumnMappings.Add("Username", "Username");
bulkCopy.ColumnMappings.Add("status", "status");
try
{
bulkCopy.WriteToServer(batable);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
}
private void importsexcel2(object obj)
{
// List<tblFBL5N> fbl5n_ctrllist = new List<tblFBL5N>();
edlpinput_ctrl md = new edlpinput_ctrl();
bool kq = md.deleteedlp();
datainportF inf = (datainportF)obj;
string filename = inf.filename;
// ExcelProvider ExcelProvide = new ExcelProvider();
//#endregion
System.Data.DataTable sourceData = ExcelProvider.GetDataFromExcel(filename);
System.Data.DataTable batable = new System.Data.DataTable();
batable.Columns.Add("Soldto", typeof(double));
batable.Columns.Add("SalesOrg", typeof(string));
batable.Columns.Add("CustName", typeof(string));
batable.Columns.Add("InvoiceDocNr", typeof(double));
batable.Columns.Add("OutboundDelivery", typeof(string));
batable.Columns.Add("MatNumber", typeof(string));
batable.Columns.Add("MatText", typeof(string));
batable.Columns.Add("BilledQty", typeof(double));
batable.Columns.Add("CondValue", typeof(double));
batable.Columns.Add("MatGroup", typeof(string));
batable.Columns.Add("MatGroupText", typeof(string));
batable.Columns.Add("UoM", typeof(string));
int Soldto = -1;
int SalesOrg = -1;
int CustName = -1;
int InvoiceDocNr = -1;
int OutboundDelivery = -1;
int MatNumber = -1;
int MatText = -1;
int BilledQty = -1;
int CondValue = -1;
int MatGroup = -1;
int MatGroupText = -1;
int UoM = -1;
int rowseet = sourceData.Rows.Count;
if (rowseet > 5)
{
rowseet = 5;
}
for (int rowid = 0; rowid < rowseet; rowid++)
{
// headindex = 1;
for (int columid = 0; columid < sourceData.Columns.Count; columid++)
{
#region
string value = sourceData.Rows[rowid][columid].ToString();
//OleDbCommand command = new OleDbCommand(
// @"SELECT [], [],[],[],
// [], [], [],[],
// [], [], [], [] FROM [Sheet1$]
// WHERE (([Invoice Doc Nr] is not null ) AND ([Sales Org]<> ''))", conn);
if (value != null && value != "")
{
// #region setcolum
if (value.Trim() == ("Sold-to"))
{
Soldto = columid;
// headindex = rowid;
}
if (value.Trim() == ("Sales Org"))
{
SalesOrg = columid;
// headindex = 0;
}
if (value.Trim() == ("Cust Name"))
{
CustName = columid;
// headindex = 0;
}
if (value.Trim() == ("Invoice Doc Nr"))
{
InvoiceDocNr = columid;
// headindex = 0;
}
if (value.Trim() == ("Outbound Delivery"))
{
OutboundDelivery = columid;
}
if (value.Trim() == ("Mat Number"))
{
MatNumber = columid;
}
if (value.Trim() == ("Mat Text"))
{
MatText = columid;
}
if (value.Trim() == ("Billed Qty"))
{
BilledQty = columid;
}
if (value.Trim() == ("Cond Value"))
{
CondValue = columid;
}
if (value.Trim() == ("Mat Group"))
{
MatGroup = columid;
}
if (value.Trim() == ("Mat Group Text"))
{
MatGroupText = columid;
}
if (value.Trim() == ("UoM"))
{
UoM = columid;
}
}
#endregion
} // colum
} // roww off heatder
#region
if (Soldto == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Sold to ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (SalesOrg == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Sales Org", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (CustName == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Cust Name", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (InvoiceDocNr == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Invoice Doc Nr ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (OutboundDelivery == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Outbound Delivery", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (MatNumber == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Mat Number ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (MatText == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Mat Text", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (BilledQty == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Billed Qty ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (CondValue == -1)
{
MessageBox.Show("Dữ liệu thiếu cột CondValue ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (MatGroup == -1)
{
MessageBox.Show("Dữ liệu thiếu cột MatGroup", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (MatGroupText == -1)
{
MessageBox.Show("Dữ liệu thiếu cột MatGroupText", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (UoM == -1)
{
MessageBox.Show("Dữ liệu thiếu cột UoM", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
#endregion
for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++)
{
#region
// string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
string InvoiceDocNrv = sourceData.Rows[rowixd][InvoiceDocNr].ToString();
if (InvoiceDocNrv != "" && Utils.IsValidnumber(InvoiceDocNrv))
{
if (double.Parse(InvoiceDocNrv) > 0)
{
DataRow dr = batable.NewRow();
dr["Soldto"] = double.Parse(sourceData.Rows[rowixd][Soldto].ToString());
dr["SalesOrg"] = sourceData.Rows[rowixd][SalesOrg].ToString().Trim();
dr["CustName"] = sourceData.Rows[rowixd][CustName].ToString().Trim();
if (Utils.IsValidnumber(sourceData.Rows[rowixd][InvoiceDocNr].ToString()))
{
dr["InvoiceDocNr"] = double.Parse(sourceData.Rows[rowixd][InvoiceDocNr].ToString());
}
dr["InvoiceDocNr"] = double.Parse(sourceData.Rows[rowixd][InvoiceDocNr].ToString());
dr["OutboundDelivery"] = sourceData.Rows[rowixd][OutboundDelivery].ToString().Trim();
dr["MatNumber"] = sourceData.Rows[rowixd][MatNumber].ToString().Trim();
dr["MatText"] = sourceData.Rows[rowixd][MatText].ToString().Trim();
if (Utils.IsValidnumber(sourceData.Rows[rowixd][BilledQty].ToString()))
{
dr["BilledQty"] = double.Parse(sourceData.Rows[rowixd][BilledQty].ToString());
}
if (Utils.IsValidnumber(sourceData.Rows[rowixd][CondValue].ToString()))
{
dr["CondValue"] = double.Parse(sourceData.Rows[rowixd][CondValue].ToString());
}
dr["MatGroup"] = sourceData.Rows[rowixd][MatGroup].ToString().Trim();
dr["MatGroupText"] = sourceData.Rows[rowixd][MatGroupText].ToString().Trim();
dr["UoM"] = sourceData.Rows[rowixd][UoM].ToString().Trim();
batable.Rows.Add(dr);
}
}
#endregion
}
// Utils util = new Utils();
string destConnString = Utils.getConnectionstr();
//---------------fill data
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.DestinationTableName = "tblEDLP";
// Write from the source to the destination.
bulkCopy.ColumnMappings.Add("[Soldto]", "[Sold-to]");
bulkCopy.ColumnMappings.Add("[SalesOrg]", "[Sales Org]");
bulkCopy.ColumnMappings.Add("[CustName]", "[Cust Name]");
bulkCopy.ColumnMappings.Add("[InvoiceDocNr]", "[Invoice Doc Nr]");
bulkCopy.ColumnMappings.Add("[OutboundDelivery]", "[Outbound Delivery]");
bulkCopy.ColumnMappings.Add("[MatNumber]", "[Mat Number]");
bulkCopy.ColumnMappings.Add("[MatText]", "[Mat Text]");
bulkCopy.ColumnMappings.Add("[BilledQty]", "[Billed Qty]");
bulkCopy.ColumnMappings.Add("[CondValue]", "[Cond Value]");
bulkCopy.ColumnMappings.Add("[MatGroup]", "[Mat Group]");
bulkCopy.ColumnMappings.Add("[MatGroupText]", "[Mat Group Text]");
bulkCopy.ColumnMappings.Add("[UoM]", "[UoM]");
try
{
bulkCopy.WriteToServer(batable);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
}
private void importsexcel2(object obj)
{
string connection_string = Utils.getConnectionstr();
var db = new LinqtoSQLDataContext(connection_string);
db.ExecuteCommand("DELETE FROM tbl_KaCustomer");
// dc.tblFBL5Nnewthisperiods.DeleteAllOnSubmit(rsthisperiod);
db.SubmitChanges();
datainportF inf = (datainportF)obj;
string filename = inf.filename;
string connectionString = "";
if (filename.Contains(".xlsx") || filename.Contains(".XLSX"))
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";" + "Extended Properties=Excel 12.0;";
}
else
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " + filename + ";" + "Extended Properties=Excel 8.0;";
}
//---------------fill data
System.Data.DataTable sourceData = new System.Data.DataTable();
using (OleDbConnection conn =
new OleDbConnection(connectionString))
{
try
{
conn.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Fill", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
// Get the data from the source table as a SqlDataReader.
// Customer Vendor SalesOrg FullName TradingName Street District City Telephone1 Telephone2 FaxNumber VATregistrationNo Indirect CustomerGroup SALORG_CTR
OleDbCommand command = new OleDbCommand(
@"SELECT Customer,
SalesOrg,
FullName,
TradingName ,
Street,
District,
City , Telephone1, VATregistrationNo, Indirect,
SALORG_CTR FROM [Sheet1$]
WHERE (Customer IS NOT NULL)", conn);
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
try
{
adapter.Fill(sourceData);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Fill", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
conn.Close();
}
/// Utils util = new Utils();
string destConnString = Utils.getConnectionstr();
// sourceData.Columns.Add("SapCode");
// sourceData.Columns["SapCode"].DefaultValue = true;
//---------------fill data
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
// @"SELECT BU, CCDescription,
// CConsumption, CentralOrBlk, CHN ,
// City, CreatedOn, CTDescription ,
// Customer, District, FullNameN ,
// KAGROUP, KANAME, KeyAcc ,
// OrBlk, PaymentTerms, PriceList ,
// ReconciliationAcct, Region, SalesDistrict ,
// SalesOrg, Street, Telephone1 ,
// UPDDAT, VATregistrationNo FROM [Sheet1$]
//
// Vendor Telephone2 FaxNumber
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.DestinationTableName = "tbl_KaCustomer";
// Write from the source to the destination.
bulkCopy.ColumnMappings.Add("Customer", "Customer");
bulkCopy.ColumnMappings.Add("SalesOrg", "Region");
bulkCopy.ColumnMappings.Add("FullName", "FullNameN");
bulkCopy.ColumnMappings.Add("TradingName", "KANAME");
bulkCopy.ColumnMappings.Add("Street", "Street");
bulkCopy.ColumnMappings.Add("District", "District");
bulkCopy.ColumnMappings.Add("City", "City");
bulkCopy.ColumnMappings.Add("Telephone1", "Telephone1");
bulkCopy.ColumnMappings.Add("VATregistrationNo", "VATregistrationNo");
// bulkCopy.ColumnMappings.Add("CustomerGroup", "CustomerGroup");
bulkCopy.ColumnMappings.Add("Indirect", "indirectCode");
bulkCopy.ColumnMappings.Add("SALORG_CTR", "SALORG_CTR");
//bulkCopy.ColumnMappings.Add("BU", "BU");
//bulkCopy.ColumnMappings.Add("CCDescription", "CCDescription");
//bulkCopy.ColumnMappings.Add("CConsumption", "CConsumption");
//bulkCopy.ColumnMappings.Add("CentralOrBlk", "CentralOrBlk");
//bulkCopy.ColumnMappings.Add("CHN", "CHN");
//bulkCopy.ColumnMappings.Add("CreatedOn", "CreatedOn");
//bulkCopy.ColumnMappings.Add("CTDescription", "CTDescription");
//bulkCopy.ColumnMappings.Add("KAGROUP", "KAGROUP");
//bulkCopy.ColumnMappings.Add("KeyAcc", "KeyAcc");
//bulkCopy.ColumnMappings.Add("OrBlk", "OrBlk");
//bulkCopy.ColumnMappings.Add("PaymentTerms", "PaymentTerms");
//bulkCopy.ColumnMappings.Add("PriceList", "PriceList");
//bulkCopy.ColumnMappings.Add("ReconciliationAcct", "ReconciliationAcct");
//bulkCopy.ColumnMappings.Add("SalesDistrict", "SalesDistrict");
//bulkCopy.ColumnMappings.Add("UPDDAT", "UPDDAT");
try
{
bulkCopy.WriteToServer(sourceData);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
// Thread.CurrentThread.Abort();
}
private void importsexceltblNKACustomer(object obj)
{
// List<tblFBL5N> fbl5n_ctrllist = new List<tblFBL5N>();
// fbl5n_ctrl md = new fbl5n_ctrl();
// bool kq = md.deleteFbl5n();
datainportF inf = (datainportF)obj;
string filename = inf.filename;
// ExcelProvider ExcelProvide = new ExcelProvider();
//#endregion
System.Data.DataTable sourceData = ExcelProvider.GetDataFromExcel(filename);
System.Data.DataTable batable = new System.Data.DataTable();
batable.Columns.Add("No", typeof(double));
batable.Columns.Add("Customer", typeof(double));
batable.Columns.Add("SOrg", typeof(string));
batable.Columns.Add("Area", typeof(string));
batable.Columns.Add("Region", typeof(string));
batable.Columns.Add("Group", typeof(string));
batable.Columns.Add("GroupLetter", typeof(string));
batable.Columns.Add("Branch", typeof(string));
batable.Columns.Add("IncoT", typeof(string));
batable.Columns.Add("KeyAcc", typeof(string));
batable.Columns.Add("Pt", typeof(string));
batable.Columns.Add("Plant", typeof(string));
batable.Columns.Add("ASM", typeof(string));
batable.Columns.Add("Name", typeof(string));
batable.Columns.Add("Nameinletter", typeof(string));
batable.Columns.Add("NameAccountofCustomer", typeof(string));
batable.Columns.Add("Mail", typeof(string));
batable.Columns.Add("Phone", typeof(string));
batable.Columns.Add("Address", typeof(string));
batable.Columns.Add("Account", typeof(string));
int Noid = -1;
int Customerid = -1;
int SOrgid = -1;
int Areaid = -1;
int Regionid = -1;
int Groupid = -1;
int GroupLetterid = -1;
int Branchid = -1;
int IncoTid = -1;
int KeyAccid = -1;
int Ptid = -1;
int Plantid = -1;
int ASMid = -1;
int Nameid = -1;
int Nameinletterid = -1;
int NameAccountofCustomerid = -1;
int Mailid = -1;
int Phoneid = -1;
int Addressid = -1;
int Accountid = -1;
for (int rowid = 0; rowid < 3; rowid++)
{
// headindex = 1;
for (int columid = 0; columid < sourceData.Columns.Count; columid++)
{
#region
//
//
//
string value = sourceData.Rows[rowid][columid].ToString();
// MessageBox.Show(value +":"+ rowid);
if (value != null && value != "")
{
// #region setcolum
if (value.Trim() == ("No"))
{
Noid = columid;
// headindex = rowid;
}
if (value.Trim() == ("Customer"))
{
Customerid = columid;
// headindex = 0;
}
if (value.Trim() == ("SOrg."))
{
SOrgid = columid;
// headindex = 0;
}
if (value.Trim() == ("Area"))
{
Areaid = columid;//
}
if (value.Trim() == ("Region"))
{
Regionid = columid;//
}
if (value.Trim() == ("Group"))
{
Groupid = columid;//
}
if (value.Trim() == ("Group Letter"))
{
GroupLetterid = columid;//
}
if (value.Trim() == ("Branch"))
{
Branchid = columid;//
}
if (value.Trim() == ("IncoT"))
{
IncoTid = columid;//
}
if (value.Trim() == ("KeyAcc"))
{
KeyAccid = columid;//
}
if (value.Trim() == ("Pt"))
{
Ptid = columid;//
}
if (value.Trim() == ("Plant"))
{
Plantid = columid;//
}
if (value.Trim() == ("ASM"))
{
ASMid = columid;//
}
if (value.Trim() == ("Name"))
{
Nameid = columid;//
}
if (value.Trim() == ("Name in letter"))
{
Nameinletterid = columid;//
}
if (value.Trim() == ("Name's Account of Customer"))
{
NameAccountofCustomerid = columid;//
}
if (value.Trim() == ("Mail"))
{
Mailid = columid;//
}
if (value.Trim() == ("Phone"))
{
Phoneid = columid;//
}
if (value.Trim() == ("Address"))
{
Addressid = columid;//
}
if (value.Trim() == ("Account"))
{
Accountid = columid;//
}
}
#endregion
} // colum
} // roww off heatder
if (Noid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột No", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Customerid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Customer", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (SOrgid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột SOrg.", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Areaid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Area", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Regionid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Region", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Groupid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Group", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (GroupLetterid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Group Letter", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Branchid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Branch", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (IncoTid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột IncoT", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (KeyAccid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột KeyAcc", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Ptid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Pt", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Plantid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Plant", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (ASMid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột ASM", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Nameid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Name", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Nameinletterid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Name in letter", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (NameAccountofCustomerid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Name 's Account of Customer", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Mailid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Mail", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Phoneid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Phone", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Addressid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Address", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Accountid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Account", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++)
{
#region
// string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
string Customer = sourceData.Rows[rowixd][Customerid].ToString();
if (Customer != "" && Utils.IsValidnumber(Customer))
{
if (double.Parse(Customer) > 0)
{
DataRow dr = batable.NewRow();
try
{
dr["No"] = double.Parse(sourceData.Rows[rowixd][Noid].ToString());//.Trim
}
catch (Exception)
{
MessageBox.Show("Dữ liệu thiếu cột No dòng: " + rowixd.ToString() + "cột: " + Noid.ToString() + " bị lỗi", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
dr["Customer"] = double.Parse(sourceData.Rows[rowixd][Customerid].ToString());//.Trim();
dr["SOrg"] = sourceData.Rows[rowixd][SOrgid].ToString().Trim();
dr["Area"] = sourceData.Rows[rowixd][Areaid].ToString().Trim();
dr["Region"] = sourceData.Rows[rowixd][Regionid].ToString().Trim();
dr["Group"] = sourceData.Rows[rowixd][Groupid].ToString().Trim();
dr["GroupLetter"] = sourceData.Rows[rowixd][GroupLetterid].ToString().Trim();
dr["Branch"] = sourceData.Rows[rowixd][Branchid].ToString().Trim();
dr["IncoT"] = sourceData.Rows[rowixd][IncoTid].ToString().Trim();
dr["KeyAcc"] = sourceData.Rows[rowixd][KeyAccid].ToString().Trim();
dr["Pt"] = sourceData.Rows[rowixd][Ptid].ToString().Trim();
dr["Plant"] = sourceData.Rows[rowixd][Plantid].ToString().Trim();
dr["ASM"] = sourceData.Rows[rowixd][ASMid].ToString().Trim();
dr["Name"] = sourceData.Rows[rowixd][Nameid].ToString().Trim();
dr["Nameinletter"] = sourceData.Rows[rowixd][Nameinletterid].ToString().Trim();
dr["NameAccountofCustomer"] = sourceData.Rows[rowixd][NameAccountofCustomerid].ToString().Trim();
dr["Mail"] = sourceData.Rows[rowixd][Mailid].ToString().Trim();
dr["Phone"] = sourceData.Rows[rowixd][Phoneid].ToString().Trim();
dr["Address"] = sourceData.Rows[rowixd][Addressid].ToString().Trim();
dr["Account"] = sourceData.Rows[rowixd][Accountid].ToString().Trim();
batable.Rows.Add(dr);
}
}
#endregion
}
// Utils util = new Utils();
string destConnString = Utils.getConnectionstr();
//---------------fill data
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
bulkCopy.DestinationTableName = "tblNKACustomer";
// Write from the source to the destination.
bulkCopy.ColumnMappings.Add("No", "[No]");
bulkCopy.ColumnMappings.Add("Customer", "[Customer]");
bulkCopy.ColumnMappings.Add("SOrg", "[SOrg]");
bulkCopy.ColumnMappings.Add("Area", "[Area]");
bulkCopy.ColumnMappings.Add("Region", "[Region]");
bulkCopy.ColumnMappings.Add("Group", "[Group]");
bulkCopy.ColumnMappings.Add("GroupLetter", "[Group Letter]");
bulkCopy.ColumnMappings.Add("Branch", "[Branch]");
bulkCopy.ColumnMappings.Add("IncoT", "[IncoT]");
bulkCopy.ColumnMappings.Add("KeyAcc", "[KeyAcc]");
bulkCopy.ColumnMappings.Add("Pt", "[Pt]");
bulkCopy.ColumnMappings.Add("Plant", "[Plant]");
bulkCopy.ColumnMappings.Add("ASM", "[ASM]");
bulkCopy.ColumnMappings.Add("Name", "[Name]");
bulkCopy.ColumnMappings.Add("Nameinletter", "[Name in letter]");
bulkCopy.ColumnMappings.Add("NameAccountofCustomer", "[Name's Account of Customer]");
bulkCopy.ColumnMappings.Add("Mail", "[Mail]");
bulkCopy.ColumnMappings.Add("Phone", "[Phone]");
bulkCopy.ColumnMappings.Add("Address", "[Address]");
bulkCopy.ColumnMappings.Add("Account", "[Account]");
try
{
bulkCopy.WriteToServer(batable);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
}
private void importsexcel(object obj)
{
// List<tblFBL5N> fbl5n_ctrllist = new List<tblFBL5N>();
fbl5n_ctrl md = new fbl5n_ctrl();
bool kq = md.deleteFbl5n();
datainportF inf = (datainportF)obj;
string filename = inf.filename;
// ExcelProvider ExcelProvide = new ExcelProvider();
//#endregion
System.Data.DataTable sourceData = ExcelProvider.GetDataFromExcel(filename);
System.Data.DataTable batable = new System.Data.DataTable();
batable.Columns.Add("Account", typeof(double));
batable.Columns.Add("Assignment", typeof(string));
batable.Columns.Add("PostingDate", typeof(DateTime));
// PF Inv Date
batable.Columns.Add("PFInvDate", typeof(DateTime));
// PF Inv Number
batable.Columns.Add("PFInvNumber", typeof(string));
// Invoice Registration No.
batable.Columns.Add("InvoiceRegistration", typeof(string));
batable.Columns.Add("DocumentType", typeof(string));
batable.Columns.Add("BusinessArea", typeof(string));
batable.Columns.Add("DocumentNumber", typeof(double));
batable.Columns.Add("Amountinlocalcurrency", typeof(double));
batable.Columns.Add("Deposit", typeof(double));
int InvoiceRegistrationid = -1;
int PFInvDateid = -1;
int PFInvNumberid = -1;
int Depositid = -1;
int Accountid = -1;
int Assignmentid = -1;
int PostingDateid = -1;
int DocumentTypeid = -1;
int DocumentNumberid = -1;
int BusinessAreaid = -1;
int Amountinlocalcurrencyid = -1;
int rowseet = sourceData.Rows.Count;
if (rowseet > 20)
{
rowseet = 20;
}
for (int rowid = 0; rowid < rowseet; rowid++)
{
// headindex = 1;
for (int columid = 0; columid < sourceData.Columns.Count; columid++)
{
#region
string value = "";
try
{
value = sourceData.Rows[rowid][columid].ToString();
}
catch (Exception)
{
value = "";
// throw;
}
// MessageBox.Show(value +":"+ rowid);
if (value != null && value != "")
{
// #region setcolum
if (value.Trim() == ("Account")) //Account
{
Accountid = columid;
// headindex = rowid;
}
if (value.Trim() == ("Assignment"))
{
Assignmentid = columid;
// headindex = 0;
}
if (value.Trim() == ("Posting Date"))
{
PostingDateid = columid;
// headindex = 0;
}
if (value.Trim() == ("Deposit"))
{
Depositid = columid;
// headindex = 0;
}
if (value.Trim() == ("Document Type"))
{
DocumentTypeid = columid;
}
if (value.Trim() == ("Document Number"))
{
DocumentNumberid = columid;
}
if (value.Trim() == ("Amount in local currency"))
{
Amountinlocalcurrencyid = columid;
}
if (value.Trim() == ("Business Area"))
{
BusinessAreaid = columid;
}
//// PF Inv Date
// batable.Columns.Add("PFInvDate", typeof(DateTime));
if (value.Trim() == ("PF Inv Date"))
{
PFInvDateid = columid;
}
if (value.Trim() == ("PF Inv Number"))
{
PFInvNumberid = columid;
}
if (value.Trim() == ("Invoice Registration No."))
{
InvoiceRegistrationid = columid;
}
}
#endregion
} // colum
} // roww off heatder
if (Accountid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Account", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (InvoiceRegistrationid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Invoice Registration No.", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Depositid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Deposit", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Assignmentid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Assignment", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (DocumentNumberid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Document Number", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (DocumentTypeid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Document Type", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (BusinessAreaid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Business Area", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Amountinlocalcurrencyid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Amount in local currencyid", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (PostingDateid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Posting Date", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (PFInvDateid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột PF Inv Date", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (PFInvNumberid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột PF Inv Number", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++)
{
#region
// string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
string Account = sourceData.Rows[rowixd][Accountid].ToString();
if (Account != "" && Utils.IsValidnumber(Account) && sourceData.Rows[rowixd][DocumentTypeid].ToString().Trim() != "")
{
if (double.Parse(Account) > 0)
{
DataRow dr = batable.NewRow();
dr["Account"] = double.Parse(sourceData.Rows[rowixd][Accountid].ToString());//.Trim();
dr["Assignment"] = sourceData.Rows[rowixd][Assignmentid].ToString().Truncate(225).Trim();
dr["PFInvNumber"] = sourceData.Rows[rowixd][PFInvNumberid].ToString().Truncate(50).Trim();
dr["InvoiceRegistration"] = sourceData.Rows[rowixd][InvoiceRegistrationid].ToString().Truncate(50).Trim();
try
{
dr["PostingDate"] = Utils.chageExceldatetoData(sourceData.Rows[rowixd][PostingDateid].ToString());
}
catch (Exception)
{
dr["PostingDate"] = "";
}
try
{
dr["PFInvDate"] = Utils.chageExceldatetoData(sourceData.Rows[rowixd][PFInvDateid].ToString());
}
catch (Exception)
{
dr["PFInvDate"] = "";
}
dr["DocumentType"] = sourceData.Rows[rowixd][DocumentTypeid].ToString().Truncate(225).Trim();
if (Utils.IsValidnumber(sourceData.Rows[rowixd][DocumentNumberid].ToString()))
{
dr["DocumentNumber"] = double.Parse(sourceData.Rows[rowixd][DocumentNumberid].ToString());//.Trim();
}
else
{
dr["DocumentNumber"] = 0;
}
if (Utils.IsValidnumber(sourceData.Rows[rowixd][Amountinlocalcurrencyid].ToString()))
{
dr["Amountinlocalcurrency"] = double.Parse(sourceData.Rows[rowixd][Amountinlocalcurrencyid].ToString());//.Trim();
}
else
{
dr["Amountinlocalcurrency"] = 0;
}
dr["BusinessArea"] = sourceData.Rows[rowixd][BusinessAreaid].ToString().Truncate(225).Trim();
if (Utils.IsValidnumber(sourceData.Rows[rowixd][Depositid].ToString().Trim()))
{
dr["Deposit"] = double.Parse(sourceData.Rows[rowixd][Depositid].ToString().Trim());
}
else
{
dr["Deposit"] = 0;
}
batable.Rows.Add(dr);
}
}
#endregion
}
// Utils util = new Utils();
string destConnString = Utils.getConnectionstr();
//---------------fill data
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.DestinationTableName = "tblFBL5N";
// Write from the source to the destination.
bulkCopy.ColumnMappings.Add("[Account]", "[Account]");
bulkCopy.ColumnMappings.Add("[Assignment]", "[Assignment]");
bulkCopy.ColumnMappings.Add("[PostingDate]", "[Posting Date]");
bulkCopy.ColumnMappings.Add("[DocumentType]", "[Document Type]");
bulkCopy.ColumnMappings.Add("[DocumentNumber]", "[Document Number]");
bulkCopy.ColumnMappings.Add("[BusinessArea]", "[Business Area]");
bulkCopy.ColumnMappings.Add("[Amountinlocalcurrency]", "[Amount in local currency]");
bulkCopy.ColumnMappings.Add("[Deposit]", "[Deposit]");
bulkCopy.ColumnMappings.Add("[PFInvDate]", "[PFInvDate]");
bulkCopy.ColumnMappings.Add("[PFInvNumber]", "[PFInvNumber]");
bulkCopy.ColumnMappings.Add("[InvoiceRegistration]", "[InvoiceRegistration]");
// Invoice Registration No.
// batable.Columns.Add("InvoiceRegistration", typeof(string));
//// PF Inv Number
//batable.Columns.Add("PFInvNumber", typeof(string));
//PFInvNumberid = columid;
try
{
bulkCopy.WriteToServer(batable);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
}
public void importShiptolist(object obj)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string);
datainportF inf = (datainportF)obj;
string filename = inf.filename;
// string filename = theDialog.FileName.ToString();
// string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
dc.ExecuteCommand("DELETE FROM tbl_MKT_Soldtocode");
// dc.tblFBL5Nnewthisperiods.DeleteAllOnSubmit(rsthisperiod);
dc.CommandTimeout = 0;
dc.SubmitChanges();
// Customer Sales Organization Name 1 House num &Street Street 4 City Telephone 1 Sales Office Delivering Plant Terms of Payment Price List Key Account No Sales district Created on Created by VAT Registration No.Central order block Order block for sales area
//
// SoldtoCode Shiptocode SalesOrg FullNameN Street District City Telephone1
ExcelProvider ExcelProvide = new ExcelProvider();
//#endregion
System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename);
System.Data.DataTable batable = new System.Data.DataTable();
batable.Columns.Add("SoldtoCode", typeof(string));
batable.Columns.Add("Shiptocode", typeof(string));
batable.Columns.Add("Soldtype", typeof(Boolean));
batable.Columns.Add("FullNameN", typeof(string));
batable.Columns.Add("Telephone1", typeof(string));
//batable.Columns.Add("Note", typeof(string));
batable.Columns.Add("District", typeof(string));
batable.Columns.Add("SalesOrg", typeof(string));
batable.Columns.Add("Street", typeof(string));
batable.Columns.Add("City", typeof(string));
// batable.Columns.Add("Region", typeof(string));
// batable.Columns.Add("PaymentTerms", typeof(string));
// batable.Columns.Add("PriceList", typeof(string));
// batable.Columns.Add("KeyAcc", typeof(float));
// batable.Columns.Add("SalesDistrict", typeof(string));
batable.Columns.Add("Createdon", typeof(DateTime));
batable.Columns.Add("Createby", typeof(string));
// batable.Columns.Add("VATregistrationNo", typeof(string));
//dr[""] = sourceData.Rows[rowixd][Customerid].ToString().Trim();
//dr["[]"] = true;
int SoldtoCodeid = 0;
int FullNameNid = 0;
int Telephone1id = 0;
int Shiptocodeid = 0;
int Districtid = 0;
int SalesOrgid = 0;
int Streetid = 0;
int Cityid = 0;
// int Regionid = 0;
// int PaymentTermsid = 0;
// int PriceListid = 0;
// int KeyAccid = 0;
// int SalesDistrictid = 0;
// int VATregistrationNoid = 0;
// View.Viewdatatable vi1 = new View.Viewdatatable(sourceData, "Test");
// vi1.ShowDialog();
int headindex = -2;
for (int rowid = 0; rowid < 3; rowid++)
{
// headindex = 1;
for (int columid = 0; columid < sourceData.Columns.Count; columid++)
{
string value = sourceData.Rows[rowid][columid].ToString();
// MessageBox.Show(value +":"+ rowid);
if (value != null)
{
#region setcolum
if (value.Trim().Contains("SoldtoCode"))
{
SoldtoCodeid = columid;
headindex = rowid;
}
if (value.Trim().Contains("FullNameN") && headindex == rowid)
{
FullNameNid = columid;
// headindex = 0;
}
if (value.Trim().Contains("Telephone1") && headindex == rowid)
{
Telephone1id = columid;
// headindex = 0;
}
if (value.Trim().Contains("Shiptocode") && headindex == rowid)
{
Shiptocodeid = columid;
}
if (value.Trim().Contains("District") && headindex == rowid)
{
//if (columid != SalesDistrictid)
//{
Districtid = columid;
//}
}
if (value.Trim().Contains("SalesOrg") && headindex == rowid)
{
SalesOrgid = columid;
}
if (value.Trim().Contains("Street") && headindex == rowid)
{
Streetid = columid;
// headindex = 0;
}
if (value.Trim().Contains("City") && headindex == rowid)
{
Cityid = columid;
}
//if (value.Trim().Contains("Region") && headindex == rowid)
//{
// Regionid = columid;
//}
//if (value.Trim().Contains("PaymentTerms") && headindex == rowid)
//{
// PaymentTermsid = columid;
//}
//if (value.Trim().Contains("PriceList") && headindex == rowid)
//{
// PriceListid = columid;
//}
//if (value.Trim().Contains("KeyAcc") && headindex == rowid)
//{
// KeyAccid = columid;
//}
//if (value.Trim().Contains("VATregistrationNo") && headindex == rowid)
//{
// VATregistrationNoid = columid;
//}
#endregion
}
} // colum
} // roww off heatder
if (headindex == -2)
{
MessageBox.Show("File excel không đúng format !", "Thông báo ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++)
{
#region setvalue of pricelist
// string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
string customer = sourceData.Rows[rowixd][SoldtoCodeid].ToString();
if (customer != "" && Utils.IsValidnumber(customer))
{
DataRow dr = batable.NewRow();
dr["SoldtoCode"] = sourceData.Rows[rowixd][SoldtoCodeid].ToString().Trim();
dr["Shiptocode"] = sourceData.Rows[rowixd][Shiptocodeid].ToString().Trim();
if (sourceData.Rows[rowixd][SoldtoCodeid].ToString().Trim() == sourceData.Rows[rowixd][Shiptocodeid].ToString().Trim())
{
dr["Soldtype"] = true;
}
else
{
dr["Soldtype"] = false;
}
dr["FullNameN"] = sourceData.Rows[rowixd][FullNameNid].ToString().Truncate(225);
dr["Telephone1"] = sourceData.Rows[rowixd][Telephone1id].ToString().Truncate(50);
dr["District"] = sourceData.Rows[rowixd][Districtid].ToString().Truncate(50);
dr["SalesOrg"] = sourceData.Rows[rowixd][SalesOrgid].ToString().Truncate(50);
dr["Street"] = sourceData.Rows[rowixd][Streetid].ToString().Truncate(225);
dr["City"] = sourceData.Rows[rowixd][Cityid].ToString().Truncate(50);
//dr["Region"] = sourceData.Rows[rowixd][Regionid].ToString().Trim();
//dr["PaymentTerms"] = sourceData.Rows[rowixd][PaymentTermsid].ToString().Trim();
//dr["PriceList"] = sourceData.Rows[rowixd][PriceListid].ToString().Trim();
//dr["KeyAcc"] = sourceData.Rows[rowixd][KeyAccid].ToString().Trim();
//dr["SalesDistrict"] = sourceData.Rows[rowixd][SalesDistrictid].ToString().Trim();
//dr["VATregistrationNo"] = sourceData.Rows[rowixd][VATregistrationNoid].ToString().Trim();
dr["Createdon"] = DateTime.Today;// Utils.GetValueOfCellInExcel(worksheet, rowid, columValid_to);
dr["Createby"] = Model.Username.getUsername();
batable.Rows.Add(dr);
}
#endregion
}// row
//conpy to server
string destConnString = Utils.getConnectionstr();
//adapter.FillSchema(sourceData, SchemaType.Source);
//sourceData.Columns["Posting Date"].DataType = typeof(DateTime);
//batable.Columns.Add("", typeof(string));
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
bulkCopy.DestinationTableName = "tbl_MKT_Soldtocode";
// Write from the source to the destination.
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.ColumnMappings.Add("SoldtoCode", "Customer");
bulkCopy.ColumnMappings.Add("Shiptocode", "ShiptoCode");
bulkCopy.ColumnMappings.Add("Soldtype", "Soldtype");
bulkCopy.ColumnMappings.Add("FullNameN", "FullNameN");
bulkCopy.ColumnMappings.Add("Telephone1", "Telephone1");
bulkCopy.ColumnMappings.Add("District", "District");
bulkCopy.ColumnMappings.Add("SalesOrg", "SalesOrg");
bulkCopy.ColumnMappings.Add("Street", "Street");
bulkCopy.ColumnMappings.Add("City", "City");
//bulkCopy.ColumnMappings.Add("Region", "Region");
//bulkCopy.ColumnMappings.Add("PaymentTerms", "PaymentTerms");
//bulkCopy.ColumnMappings.Add("PriceList", "PriceList");
//bulkCopy.ColumnMappings.Add("KeyAcc", "KeyAcc");
//bulkCopy.ColumnMappings.Add("SalesDistrict", "SalesDistrict");
bulkCopy.ColumnMappings.Add("Createdon", "Createdon");
bulkCopy.ColumnMappings.Add("Createby", "Createby");
// bulkCopy.ColumnMappings.Add("VATregistrationNo", "VATregistrationNo");
try
{
bulkCopy.WriteToServer(batable);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
//copy to server
// string connection_string = Utils.getConnectionstr();
// LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
// var typeffmain = typeof(tbl_KAbaseprice);
// var typeffsub = typeof(tbl_KAbaseprice);
// VInputchange inputcdata1 = new VInputchange("", "Base price list", dc, "tbl_KAbaseprice", "tbl_KAbaseprice", typeffmain, typeffsub, "id", "id", "");
// inputcdata1.ShowDialog();
// View.Viewdatatable TB = new View.Viewdatatable(batable, "lIST DATA");
// TB.ShowDialog();
// }
}
private void importRemarksexcel(object obj)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string);
// List<tblFBL5N> fbl5n_ctrllist = new List<tblFBL5N>();
Remarks Rm = new Remarks();
bool kq = Rm.deleteallremarks();
datainportF inf = (datainportF)obj;
string filename = inf.filename;
#region new by datatable
// ExcelProvider ExcelProvide = new ExcelProvider();
//#endregion
System.Data.DataTable sourceData = ExcelProvider.GetDataFromExcel(filename);
System.Data.DataTable batable = new System.Data.DataTable();
batable.Columns.Add("DocumentNo", typeof(double));
batable.Columns.Add("Customer", typeof(double));
batable.Columns.Add("Remark", typeof(string));
int DocumentNoid = -1;
int Customerid = -1;
int Remarkid = -1;
int rowseet = sourceData.Rows.Count;
if (rowseet > 5)
{
rowseet = 5;
}
for (int rowid = 0; rowid < rowseet; rowid++)
{
// headindex = 1;
for (int columid = 0; columid < sourceData.Columns.Count; columid++)
{
#region
string value = sourceData.Rows[rowid][columid].ToString();
if (value != null && value != "")
{
// #region setcolum
if (value.Trim() == ("DocumentNo") && DocumentNoid == -1)
{
DocumentNoid = columid;
// headindex = rowid;
}
if (value.Trim() == ("Customer") && Customerid == -1)
{
Customerid = columid;
// headindex = 0;
}
if (value.Trim() == ("Remark") && Remarkid == -1)
{
Remarkid = columid;
// headindex = 0;
}
}
#endregion
} // colum
} // roww off heatder
if (DocumentNoid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột DocumentNoid", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Customerid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Customer", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Remarkid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Remark", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++)
{
#region
// string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
string DocumentNoVal = sourceData.Rows[rowixd][DocumentNoid].ToString();
if (DocumentNoVal != "" && Utils.IsValidnumber(DocumentNoVal))
{
if (double.Parse(DocumentNoVal) > 0)
{
DataRow dr = batable.NewRow();
try
{
dr["Remark"] = sourceData.Rows[rowixd][Remarkid].ToString().Truncate(225).Trim();
dr["DocumentNo"] = double.Parse(sourceData.Rows[rowixd][DocumentNoid].ToString());
dr["Customer"] = double.Parse(sourceData.Rows[rowixd][Customerid].ToString());
}
catch (Exception)
{
MessageBox.Show("Data có đủ cột nhưng dữ liệu ở dưới bị lost, please check", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
batable.Rows.Add(dr);
}
}
#endregion
}
#endregion
// Utils util = new Utils();
string destConnString = Utils.getConnectionstr();
//---------------fill data
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.DestinationTableName = "tbl_Remark";
// Write from the source to the destination.
bulkCopy.ColumnMappings.Add("[DocumentNo]", "[DocumentNo]");
bulkCopy.ColumnMappings.Add("[Customer]", "[Customer]");
bulkCopy.ColumnMappings.Add("[Remark]", "[Remark]");
try
{
bulkCopy.WriteToServer(batable);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
}
public void Inputapprovalpaymentcode(object obj)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string);
datainportF inf = (datainportF)obj;
string filename = inf.filename;
string username = inf.username;
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
dc.ExecuteCommand("DELETE FROM tbl_MKT_Payment_AprovalTMP where tbl_MKT_Payment_AprovalTMP.username = '******'");
// dc.tblFBL5Nnewthisperiods.DeleteAllOnSubmit(rsthisperiod);
dc.CommandTimeout = 0;
dc.SubmitChanges();
ExcelProvider ExcelProvide = new ExcelProvider();
//#endregion
System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename);
System.Data.DataTable batable = new System.Data.DataTable();
batable.Columns.Add("Customercode", typeof(string));
batable.Columns.Add("CustomerName", typeof(string));
batable.Columns.Add("CustomerAddress", typeof(string));
batable.Columns.Add("AprovalBudget", typeof(float));
batable.Columns.Add("username", typeof(string));
int Customercodeid = 0;
int CustomerNameid = 0;
int CustomerAddressid = 0;
int AprovalBudgetid = 0;
int rowheadindex = -2;
for (int rowid = 0; rowid < 3; rowid++)
{
// headindex = 1;
for (int columid = 0; columid < sourceData.Columns.Count; columid++)
{
string value = sourceData.Rows[rowid][columid].ToString();
// MessageBox.Show(value +":"+ rowid);
if (value != null)
{
#region setcolum
if (value.Trim().Contains("Customercode"))
{
Customercodeid = columid;
rowheadindex = rowid;
// headindex = 0;
}
if (value.Trim().Contains("CustomerName"))
{
CustomerNameid = columid;
// headindex = 0;
}
if (value.Trim().Contains("CustomerAddress"))
{
CustomerAddressid = columid;
}
if (value.Trim().Contains("AprovalBudget"))
{
AprovalBudgetid = columid;
}
#endregion
}
} // colum
} // roww off heatder
for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++)
{
#region setvalue of pricelist
// string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
string Customercode = sourceData.Rows[rowixd][Customercodeid].ToString();
if (Customercode != "" && rowheadindex != rowixd)
{
DataRow dr = batable.NewRow();
dr["Customercode"] = sourceData.Rows[rowixd][Customercodeid].ToString().Trim();
if (Utils.IsValidnumber(sourceData.Rows[rowixd][AprovalBudgetid].ToString()))
{
dr["AprovalBudget"] = double.Parse(sourceData.Rows[rowixd][AprovalBudgetid].ToString());
}
else
{
dr["AprovalBudget"] = 0;
}
dr["CustomerName"] = sourceData.Rows[rowixd][CustomerNameid].ToString().Trim();
dr["CustomerAddress"] = sourceData.Rows[rowixd][CustomerAddressid].ToString().Trim();
//dr["Description"] = sourceData.Rows[rowixd][Descriptionid].ToString().Trim();
//dr["UNIT"] = sourceData.Rows[rowixd][UNITid].ToString().Trim();
dr["username"] = username;
batable.Rows.Add(dr);
}
#endregion
}// row
//conpy to server
string destConnString = Utils.getConnectionstr();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
//batable.Columns.Add("SAPCODE", typeof(string));
//batable.Columns.Add("ITEMCode", typeof(string));
//batable.Columns.Add("MATERIAL", typeof(string));
//batable.Columns.Add("Description", typeof(string));
//batable.Columns.Add("UNIT", typeof(string));
//batable.Columns.Add("ENDSTOCK", typeof(float));
//batable.Columns.Add("Store_code", typeof(string));
bulkCopy.DestinationTableName = "tbl_MKT_Payment_AprovalTMP";
// Write from the source to the destination.
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.ColumnMappings.Add("Customercode", "[Customercode]");
bulkCopy.ColumnMappings.Add("AprovalBudget", "[AprovalBudget]");
bulkCopy.ColumnMappings.Add("CustomerName", "[CustomerName]");
bulkCopy.ColumnMappings.Add("CustomerAddress", "[CustomerAddress]");
bulkCopy.ColumnMappings.Add("username", "[username]");
try
{
bulkCopy.WriteToServer(batable);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
}
public void Addnewproduct(object obj)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string);
datainportF inf = (datainportF)obj;
string filename = inf.filename;
string storelocation = inf.storelocation;
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
string Username = Utils.getusername();
dc.ExecuteCommand("DELETE FROM tbl_MKT_StockendTMP where tbl_MKT_StockendTMP.Username = '******'");
dc.CommandTimeout = 0;
dc.SubmitChanges();
ExcelProvider ExcelProvide = new ExcelProvider();
//#endregion
System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename);
System.Data.DataTable batable = new System.Data.DataTable();
// batable.Columns.Add("Delivery_No", typeof(float));
batable.Columns.Add("SAPCODE", typeof(string));
batable.Columns.Add("ITEMCode", typeof(string));
batable.Columns.Add("MATERIAL", typeof(string));
batable.Columns.Add("Description", typeof(string));
batable.Columns.Add("UNIT", typeof(string));
batable.Columns.Add("ENDSTOCK", typeof(float));
batable.Columns.Add("Username", typeof(string));
batable.Columns.Add("Store_code", typeof(string));
int SAPCODEid = 0;
int ITEMCodeid = 0;
int MATERIALid = 0;
int Descriptionid = 0;
int UNITid = 0;
int ENDSTOCKid = 0;
int rowheadindex = -2;
for (int rowid = 0; rowid < 3; rowid++)
{
// headindex = 1;
for (int columid = 0; columid < sourceData.Columns.Count; columid++)
{
string value = sourceData.Rows[rowid][columid].ToString();
// MessageBox.Show(value +":"+ rowid);
if (value != null)
{
#region setcolum
if (value.Trim().Contains("ITEMCode"))
{
ITEMCodeid = columid;
rowheadindex = rowid;
// headindex = 0;
}
if (value.Trim().Contains("SAPCODE"))
{
SAPCODEid = columid;
// headindex = 0;
}
if (value.Trim().Contains("MATERIAL"))
{
MATERIALid = columid;
}
if (value.Trim().Contains("Description"))
{
Descriptionid = columid;
}
if (value.Trim().Contains("UNIT"))
{
UNITid = columid;
}
if (value.Trim().Contains("ENDSTOCK"))
{
ENDSTOCKid = columid;
// headindex = 0;
}
#endregion
}
} // colum
} // roww off heatder
for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++)
{
#region setvalue of pricelist
// string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
string Itemcode = sourceData.Rows[rowixd][ITEMCodeid].ToString();
if (Itemcode != "" && rowheadindex != rowixd)
{
DataRow dr = batable.NewRow();
dr["SAPCODE"] = sourceData.Rows[rowixd][SAPCODEid].ToString().Trim();
if (Utils.IsValidnumber(sourceData.Rows[rowixd][ENDSTOCKid].ToString()))
{
dr["ENDSTOCK"] = double.Parse(sourceData.Rows[rowixd][ENDSTOCKid].ToString());
}
else
{
dr["ENDSTOCK"] = 0;
}
dr["ITEMCode"] = sourceData.Rows[rowixd][ITEMCodeid].ToString().Trim();
dr["MATERIAL"] = sourceData.Rows[rowixd][MATERIALid].ToString().Trim();
dr["Description"] = sourceData.Rows[rowixd][Descriptionid].ToString().Trim();
dr["UNIT"] = sourceData.Rows[rowixd][UNITid].ToString().Trim();
dr["Store_code"] = storelocation;
dr["Username"] = Username;
batable.Rows.Add(dr);
}
#endregion
}// row
//conpy to server
string destConnString = Utils.getConnectionstr();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
//batable.Columns.Add("SAPCODE", typeof(string));
//batable.Columns.Add("ITEMCode", typeof(string));
//batable.Columns.Add("MATERIAL", typeof(string));
//batable.Columns.Add("Description", typeof(string));
//batable.Columns.Add("UNIT", typeof(string));
//batable.Columns.Add("ENDSTOCK", typeof(float));
//batable.Columns.Add("Store_code", typeof(string));
bulkCopy.DestinationTableName = "tbl_MKT_StockendTMP";
// Write from the source to the destination.
bulkCopy.BulkCopyTimeout = 0;
// bulkCopy.ColumnMappings.Add("Delivery_No", "Delivery_No");
bulkCopy.ColumnMappings.Add("SAPCODE", "[SAP_CODE]");
bulkCopy.ColumnMappings.Add("ITEMCode", "[ITEM_Code]");
bulkCopy.ColumnMappings.Add("MATERIAL", "[MATERIAL]");
bulkCopy.ColumnMappings.Add("Description", "[Description]");
bulkCopy.ColumnMappings.Add("ENDSTOCK", "[END_STOCK]");
bulkCopy.ColumnMappings.Add("UNIT", "[UNIT]");
bulkCopy.ColumnMappings.Add("Store_code", "[Store_code]");
bulkCopy.ColumnMappings.Add("Username", "Username");
try
{
bulkCopy.WriteToServer(batable);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
}
public void InputBeginstoredetailwithlocation(object obj)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string);
datainportF inf = (datainportF)obj;
string filename = inf.filename;
string storelocation = inf.storelocation;
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
dc.ExecuteCommand("DELETE FROM tbl_MKT_Stockendlocationdetail where tbl_MKT_Stockendlocationdetail.Store_code = '" + storelocation + "'" + "and tbl_MKT_Stockendlocationdetail.[Doctype] = 'Begin'");
// dc.tblFBL5Nnewthisperiods.DeleteAllOnSubmit(rsthisperiod);
dc.CommandTimeout = 0;
dc.SubmitChanges();
ExcelProvider ExcelProvide = new ExcelProvider();
//#endregion
System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename);
System.Data.DataTable batable = new System.Data.DataTable();
// batable.Columns.Add("Delivery_No", typeof(float));
batable.Columns.Add("location", typeof(string));
batable.Columns.Add("SAPCODE", typeof(string));
batable.Columns.Add("ITEMCode", typeof(string));
batable.Columns.Add("MATERIAL", typeof(string));
batable.Columns.Add("Description", typeof(string));
batable.Columns.Add("UNIT", typeof(string));
batable.Columns.Add("ENDSTOCK", typeof(float));
batable.Columns.Add("Store_code", typeof(string));
batable.Columns.Add("Doctype", typeof(string));
batable.Columns.Add("Createdate", typeof(DateTime));
// [location]
//,[SAP_CODE]
//,[ITEM_Code]
//,[MATERIAL]
//,[Description]
//,[UNIT]
//,[END_STOCK]
//,[id]
//,[Store_code]
//,[Doctype]
//,[DocNumber]
int Store_codeid = 0;
int locationid = 0;
int SAPCODEid = 0;
int ITEMCodeid = 0;
int MATERIALid = 0;
int Descriptionid = 0;
int UNITid = 0;
int ENDSTOCKid = 0;
int rowheadindex = -2;
for (int rowid = 0; rowid < 3; rowid++)
{
// headindex = 1;
for (int columid = 0; columid < sourceData.Columns.Count; columid++)
{
string value = sourceData.Rows[rowid][columid].ToString();
// MessageBox.Show(value +":"+ rowid);
if (value != null)
{
#region setcolum
if (value.Trim().Contains("Storage Location"))
{
locationid = columid;
rowheadindex = rowid;
// headindex = 0;
}
if (value.Trim().Contains("Plant"))
{
Store_codeid = columid;
}
if (value.Trim().Contains("Material") && !value.Trim().Contains("Material Description") && !value.Trim().Contains("Description in Vietnamese"))
{
SAPCODEid = columid;
ITEMCodeid = columid;
// rowheadindex = rowid;
// headindex = 0;
}
//if (value.Trim().Contains("Material"))
//{
// SAPCODEid = columid;
// // headindex = 0;
//}
if (value.Trim().Contains("Material Description"))
{
MATERIALid = columid;
}
if (value.Trim().Contains("Description in Vietnamese"))
{
Descriptionid = columid;
}
if (value.Trim().Contains("Base Unit of Measure"))
{
UNITid = columid;
}
if (value.Trim() == ("Unrestricted"))
{
ENDSTOCKid = columid;
// headindex = 0;
}
#endregion
}
} // colum
} // roww off heatder
for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++)
{
#region setvalue of pricelist
// string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
string Itemcode = sourceData.Rows[rowixd][ITEMCodeid].ToString();
if (Itemcode != "" && rowheadindex != rowixd && sourceData.Rows[rowixd][Store_codeid].ToString().Trim() == storelocation) // chỉ lấy giá trị của kho cần lấy
{
DataRow dr = batable.NewRow();
dr["location"] = sourceData.Rows[rowixd][locationid].ToString().Trim().Truncate(50);
dr["SAPCODE"] = sourceData.Rows[rowixd][SAPCODEid].ToString().Trim();
if (Utils.IsValidnumber(sourceData.Rows[rowixd][ENDSTOCKid].ToString()))
{
dr["ENDSTOCK"] = double.Parse(sourceData.Rows[rowixd][ENDSTOCKid].ToString());
}
else
{
dr["ENDSTOCK"] = 0;
}
dr["ITEMCode"] = sourceData.Rows[rowixd][ITEMCodeid].ToString().Trim();
dr["MATERIAL"] = sourceData.Rows[rowixd][MATERIALid].ToString().Trim();
dr["Description"] = sourceData.Rows[rowixd][Descriptionid].ToString().Trim();
dr["UNIT"] = sourceData.Rows[rowixd][UNITid].ToString().Trim();
dr["Store_code"] = storelocation;
dr["Doctype"] = "Begin";
dr["Createdate"] = DateTime.Today;
batable.Rows.Add(dr);
}
#endregion
}// row
//conpy to server
string destConnString = Utils.getConnectionstr();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
//batable.Columns.Add("SAPCODE", typeof(string));
//batable.Columns.Add("ITEMCode", typeof(string));
//batable.Columns.Add("MATERIAL", typeof(string));
//batable.Columns.Add("Description", typeof(string));
//batable.Columns.Add("UNIT", typeof(string));
//batable.Columns.Add("ENDSTOCK", typeof(float));
//batable.Columns.Add("Store_code", typeof(string));
bulkCopy.DestinationTableName = "tbl_MKT_Stockendlocationdetail";
// Write from the source to the destination.
bulkCopy.BulkCopyTimeout = 0;
// bulkCopy.ColumnMappings.Add("Delivery_No", "Delivery_No");
bulkCopy.ColumnMappings.Add("location", "[location]");
bulkCopy.ColumnMappings.Add("SAPCODE", "[SAP_CODE]");
bulkCopy.ColumnMappings.Add("ITEMCode", "[ITEM_Code]");
bulkCopy.ColumnMappings.Add("MATERIAL", "[MATERIAL]");
bulkCopy.ColumnMappings.Add("Description", "[Description]");
bulkCopy.ColumnMappings.Add("ENDSTOCK", "[END_STOCK]");
bulkCopy.ColumnMappings.Add("UNIT", "[UNIT]");
bulkCopy.ColumnMappings.Add("Store_code", "[Store_code]");
bulkCopy.ColumnMappings.Add("Doctype", "[Doctype]");
bulkCopy.ColumnMappings.Add("Createdate", "[Createdate]");
// dr["Store_code"] = storelocation;
// dr["Doctype"] = "Begin";
try
{
bulkCopy.WriteToServer(batable);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
}
private void importsexcel(object obj)
{
// List<tblFBL5N> fbl5n_ctrllist = new List<tblFBL5N>();
vat_ctrl md = new vat_ctrl();
bool kq = md.deleteallvat();
datainportF inf = (datainportF)obj;
string filename = inf.filename;
ExcelProvider ExcelProvide = new ExcelProvider();
//#endregion
System.Data.DataTable sourceData = ExcelProvider.GetDataFromExcel(filename);
System.Data.DataTable batable = new System.Data.DataTable();
//bulkCopy.ColumnMappings.Add("[InvoiceRegistrationNumber]", "[Invoice Registration Number]");
//bulkCopy.ColumnMappings.Add("[InvoiceNumber]", "[Invoice Number]");
//bulkCopy.ColumnMappings.Add("[SAPDeliveryNumber]", "[SAP Delivery Number]");
//bulkCopy.ColumnMappings.Add("[SAPInvoiceNumber]", "[SAP Invoice Number]");
//bulkCopy.ColumnMappings.Add("[ProFormaDate]", "[Pro Forma Date]");
//bulkCopy.ColumnMappings.Add("[InvoiceAmountBeforeVAT]", "[Invoice Amount Before VAT]");//-sua am
//bulkCopy.ColumnMappings.Add("[VATAmount]", "[VAT Amount]");
//bulkCopy.ColumnMappings.Add("[CustomerNumber]", "[Customer Number]");
//bulkCopy.ColumnMappings.Add("[CustomerName]", "[Customer Name]");
//bulkCopy.ColumnMappings.Add("[StreetAddress]", "[Street Address]");
batable.Columns.Add("InvoiceRegistrationNumber", typeof(string));
batable.Columns.Add("InvoiceNumber", typeof(double));
batable.Columns.Add("SAPDeliveryNumber", typeof(double));
batable.Columns.Add("SAPInvoiceNumber", typeof(double));
batable.Columns.Add("ProFormaDate", typeof(DateTime));
batable.Columns.Add("InvoiceAmountBeforeVAT", typeof(double));
batable.Columns.Add("VATAmount", typeof(double));
batable.Columns.Add("CustomerNumber", typeof(double));
batable.Columns.Add("CustomerName", typeof(string));
batable.Columns.Add("StreetAddress", typeof(string));
// int InvoiceRegistrationNumber = -1;
int InvoiceNumber = -1;
int SAPDeliveryNumber = -1;
int SAPInvoiceNumber = -1;
// int ProFormaDate = -1;
int InvoiceAmountBeforeVAT = -1;
int VATAmount = -1;
int CustomerNumber = -1;
int CustomerName = -1;
int StreetAddress = -1;
// int CustomerNumber = -1;
int rowseet = sourceData.Rows.Count;
if (rowseet > 5)
{
rowseet = 5;
}
for (int rowid = 0; rowid < rowseet; rowid++)
{
// headindex = 1;
for (int columid = 0; columid < sourceData.Columns.Count; columid++)
{
#region
string value = sourceData.Rows[rowid][columid].ToString();
// MessageBox.Show(value +":"+ rowid);
//OleDbCommand command = new OleDbCommand(
// @"SELECT [Invoice Registration Number],[Invoice Number],[SAP Delivery Number],[SAP Invoice Number],
// [Pro Forma Date],[Customer Number],[Customer Name],[Invoice Amount Before VAT],[ VAT Amount],
// [Street Address] FROM [Sheet1$]
// WHERE ([SAP Invoice Number] is not null ) ", conn);
if (value != null && value != "")
{
// #region setcolum
//if (value.Trim() == ("Invoice Registration Number"))
//{
// InvoiceRegistrationNumber = columid;
// // headindex = rowid;
//}
if (value.Trim() == ("Invoice Number"))
{
InvoiceNumber = columid;
// headindex = 0;
}
if (value.Trim() == ("SAP Invoice Number"))
{
SAPInvoiceNumber = columid;
// headindex = 0;
}
if (value.Trim() == ("SAP Delivery Number"))
{
SAPDeliveryNumber = columid;
// headindex = 0;
}
if (value.Trim() == ("Customer Name"))
{
CustomerName = columid;
}
//if (value.Trim()==("Pro Forma Date"))
//{
// ProFormaDate = columid;
//}
if (value.Trim() == ("Customer Number"))
{
CustomerNumber = columid;
}
if (value.Trim() == ("Invoice Amount Before VAT"))
{
InvoiceAmountBeforeVAT = columid;
}
if (value.Trim() == ("VAT Amount"))
{
VATAmount = columid;
}
if (value.Trim() == ("Street Address"))
{
StreetAddress = columid;
}
}
#endregion
} // colum
} // roww off heatder
//if (InvoiceRegistrationNumber == -1)
//{
// MessageBox.Show("Dữ liệu thiếu cột Invoice Registration Number", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
// return;
//}
if (InvoiceNumber == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Invoice Number", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (SAPInvoiceNumber == -1)
{
MessageBox.Show("Dữ liệu thiếu cột SAP invoice Number", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (SAPDeliveryNumber == -1)
{
MessageBox.Show("Dữ liệu thiếu cột SAP Delivery Number", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//if (ProFormaDate == -1)
//{
// MessageBox.Show("Dữ liệu thiếu cột Pro Forma Date ngày hóa đơn in", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
// return;
//}
if (InvoiceAmountBeforeVAT == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Invoice Amount Before VAT", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (VATAmount == -1)
{
MessageBox.Show("Dữ liệu thiếu cột VAT Amount", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (CustomerNumber == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Customer Number", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (CustomerName == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Customer Name", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (StreetAddress == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Street Address", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++)
{
#region
// string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
string SAPInvoiceNumberv = sourceData.Rows[rowixd][SAPInvoiceNumber].ToString();
if (SAPInvoiceNumberv != "" && Utils.IsValidnumber(SAPInvoiceNumberv))
{
if (double.Parse(SAPInvoiceNumberv) > 0)
{
DataRow dr = batable.NewRow();
// dr["InvoiceRegistrationNumber"] = sourceData.Rows[rowixd][InvoiceRegistrationNumber].ToString().Truncate(225).Trim();
if (sourceData.Rows[rowixd][InvoiceNumber].ToString() != "" && Utils.IsValidnumber(sourceData.Rows[rowixd][InvoiceNumber].ToString()))
{
dr["InvoiceNumber"] = double.Parse(sourceData.Rows[rowixd][InvoiceNumber].ToString());
}
try
{
dr["SAPDeliveryNumber"] = double.Parse(sourceData.Rows[rowixd][SAPDeliveryNumber].ToString());
dr["SAPInvoiceNumber"] = double.Parse(sourceData.Rows[rowixd][SAPInvoiceNumber].ToString());
//try
//{
// dr["ProFormaDate"] = Utils.chageExceldatetoData(sourceData.Rows[rowixd][ProFormaDate].ToString());
//}
//catch (Exception)
//{
// dr["ProFormaDate"] = "";
//}
dr["InvoiceAmountBeforeVAT"] = double.Parse(sourceData.Rows[rowixd][InvoiceAmountBeforeVAT].ToString());//.Trim();
dr["VATAmount"] = double.Parse(sourceData.Rows[rowixd][VATAmount].ToString());
dr["CustomerNumber"] = double.Parse(sourceData.Rows[rowixd][CustomerNumber].ToString());//.Trim();
dr["CustomerName"] = sourceData.Rows[rowixd][CustomerName].ToString().Truncate(225).Trim();
dr["StreetAddress"] = sourceData.Rows[rowixd][StreetAddress].ToString().Truncate(225).Trim();
}
catch (Exception)
{
MessageBox.Show("Data có đủ cột nhưng dữ liệu ở dưới bị lost, please check", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
batable.Rows.Add(dr);
}
}
#endregion
}
// Utils util = new Utils();
string destConnString = Utils.getConnectionstr();
//---------------fill data
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.DestinationTableName = "tblVat";
// Write from the source to the destination.
// bulkCopy.ColumnMappings.Add("[InvoiceRegistrationNumber]", "[Invoice Registration Number]");
bulkCopy.ColumnMappings.Add("[InvoiceNumber]", "[Invoice Number]");
bulkCopy.ColumnMappings.Add("[SAPDeliveryNumber]", "[SAP Delivery Number]");
bulkCopy.ColumnMappings.Add("[SAPInvoiceNumber]", "[SAP Invoice Number]");
// bulkCopy.ColumnMappings.Add("[ProFormaDate]", "[Pro Forma Date]");
bulkCopy.ColumnMappings.Add("[InvoiceAmountBeforeVAT]", "[Invoice Amount Before VAT]");//-sua am
bulkCopy.ColumnMappings.Add("[VATAmount]", "[VAT Amount]");
bulkCopy.ColumnMappings.Add("[CustomerNumber]", "[Customer Number]");
bulkCopy.ColumnMappings.Add("[CustomerName]", "[Customer Name]");
bulkCopy.ColumnMappings.Add("[StreetAddress]", "[Street Address]");
try
{
bulkCopy.WriteToServer(batable);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
}
private void importFuctionexcel(object obj)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string);
fuctionprog Rm = new fuctionprog();
bool kq = Rm.deleteallfction();
datainportF inf = (datainportF)obj;
string filename = inf.filename;
string connectionString = "";
if (filename.Contains(".xlsx") || filename.Contains(".XLSX"))
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";" + "Extended Properties=Excel 12.0;";
}
else
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " + filename + ";" + "Extended Properties=Excel 8.0;";
}
//------
//---------------fill data
System.Data.DataTable sourceData = new System.Data.DataTable();
using (OleDbConnection conn =
new OleDbConnection(connectionString))
{
try
{
conn.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Open conext !", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
// Get the data from the source table as a SqlDataReader.
OleDbCommand command = new OleDbCommand(
@"SELECT Code, Description,Example,Explan
FROM [Sheet1$]
WHERE ( Code is not null ) ", conn);
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
// adapter.FillSchema(sourceData, SchemaType.Source);
// sourceData.Columns["Posting Date"].DataType = typeof(DateTime);
//sourceData.Columns["Invoice Doc Nr"].DataType = typeof(float);
//sourceData.Columns["Billed Qty"].DataType = typeof(float);
//sourceData.Columns["Cond Value"].DataType = typeof(float);
//sourceData.Columns["Sales Org"].DataType = typeof(string);
//sourceData.Columns["Cust Name"].DataType = typeof(string);
//sourceData.Columns["Outbound Delivery"].DataType = typeof(string);
//sourceData.Columns["Mat Group"].DataType = typeof(string);
//sourceData.Columns["Mat Group Text"].DataType = typeof(string);
//sourceData.Columns["UoM"].DataType = typeof(string);
try
{
adapter.Fill(sourceData);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Fill !", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
conn.Close();
}
// Utils util = new Utils();
string destConnString = Utils.getConnectionstr();
//---------------fill data
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
//@"SELECT Code, Description,Example,Explan
// FROM [Sheet1$]
// WHERE ( Code is not null ) ", conn);
bulkCopy.DestinationTableName = "tbl_Kafuctionlist";
// Write from the source to the destination.
bulkCopy.ColumnMappings.Add("Code", "Code");
bulkCopy.ColumnMappings.Add("Description", "Description");
bulkCopy.ColumnMappings.Add("Explan", "Explan");
bulkCopy.ColumnMappings.Add("Example", "Example");
#region tìm id
#endregion
try
{
bulkCopy.WriteToServer(sourceData);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
}
public void importsexceltoPricingcheck(object obj)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string);
datainportF inf = (datainportF)obj;
string filename = inf.filename;
// string filename = theDialog.FileName.ToString();
// string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
dc.ExecuteCommand("DELETE FROM tbl_ka_prCustomer");
// dc.tblFBL5Nnewthisperiods.DeleteAllOnSubmit(rsthisperiod);
dc.CommandTimeout = 0;
dc.SubmitChanges();
// Customer Sales Organization Name 1 House num &Street Street 4 City Telephone 1 Sales Office Delivering Plant Terms of Payment Price List Key Account No Sales district Created on Created by VAT Registration No.Central order block Order block for sales area
//
ExcelProvider ExcelProvide = new ExcelProvider();
//#endregion
System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename);
System.Data.DataTable batable = new System.Data.DataTable();
batable.Columns.Add("Customer", typeof(double));
batable.Columns.Add("SalesOrg", typeof(string));
batable.Columns.Add("Name", typeof(string));
batable.Columns.Add("House", typeof(string));
batable.Columns.Add("Street", typeof(string));
batable.Columns.Add("City", typeof(string));
batable.Columns.Add("Telephone", typeof(string));
batable.Columns.Add("Salesoff", typeof(string));
batable.Columns.Add("Plant", typeof(string));
batable.Columns.Add("Termpayment", typeof(string));
batable.Columns.Add("Pricelist", typeof(string));
batable.Columns.Add("KeyAccount", typeof(double));
batable.Columns.Add("SalesDist", typeof(string));
batable.Columns.Add("Createdon", typeof(DateTime));
batable.Columns.Add("Createby", typeof(string));
batable.Columns.Add("VATregistno", typeof(string));
batable.Columns.Add("orderblock", typeof(string));
batable.Columns.Add("salesblock", typeof(string));
int Customerid = 0;
int Salesogid = 0;
int Nameid = 0;
int Houseid = 0;
int Streetid = 0;
int Cityid = 0;
int telephoneid = 0;
int salesofficeid = 0;
int Deliveringid = 0;
int Termsid = 0;
int Priceid = 0;
int Keyid = 0;
int Salesdistid = 0;
int Createdonid = 0;
int Createdbyid = 0;
int VATid = 0;
int orderblockid = 0;
int salesblockid = 0;
// View.Viewdatatable vi1 = new View.Viewdatatable(sourceData, "Test");
// vi1.ShowDialog();
int headindex = -2;
for (int rowid = 0; rowid < 3; rowid++)
{
// headindex = 1;
for (int columid = 0; columid < sourceData.Columns.Count; columid++)
{
string value = sourceData.Rows[rowid][columid].ToString();
// MessageBox.Show(value +":"+ rowid);
if (value != null)
{
#region setcolum
if (value.Trim().Contains("Customer"))
{
Customerid = columid;
headindex = rowid;
}
if (value.Trim().Contains("Sales Organization") && headindex == rowid)
{
Salesogid = columid;
// headindex = 0;
}
if (value.Trim().Contains("Name 1") && headindex == rowid)
{
Nameid = columid;
// headindex = 0;
}
if (value.Trim().Contains("House num & Street") && headindex == rowid)
{
Houseid = columid;
}
if (value.Trim().Contains("Street 4") && headindex == rowid)
{
Streetid = columid;
}
if (value.Trim().Contains("City") && headindex == rowid)
{
Cityid = columid;
}
if (value.Trim().Contains("Telephone 1") && headindex == rowid)
{
telephoneid = columid;
// headindex = 0;
}
if (value.Trim().Contains("Sales Office") && headindex == rowid)
{
salesofficeid = columid;
}
if (value.Trim().Contains("Delivering Plant") && headindex == rowid)
{
Deliveringid = columid;
}
if (value.Trim().Contains("Terms of Payment") && headindex == 0)
{
Termsid = columid;
headindex = 0;
}
if (value.Trim().Contains("Price List") && headindex == rowid)
{
Priceid = columid;
}
if (value.Trim().Contains("Key Account No") && headindex == rowid)
{
Keyid = columid;
}
if (value.Trim().Contains("Sales district") && headindex == rowid)
{
Salesdistid = columid;
}
if (value.Trim().Contains("Created on") && headindex == rowid)
{
Createdonid = columid;
}
if (value.Trim().Contains("Created by") && headindex == rowid)
{
Createdbyid = columid;
}
if (value.Trim().Contains("VAT Registration No") && headindex == rowid)
{
VATid = columid;
}
if (value.Trim().Contains("Central order block") && headindex == rowid)
{
orderblockid = columid;
}
if (value.Trim().Contains("Order block for sales area") && headindex == rowid)
{
salesblockid = columid;
}
#endregion
}
} // colum
} // roww off heatder
for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++)
{
#region setvalue of pricelist
// string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
string customer = sourceData.Rows[rowixd][Customerid].ToString();
if (customer != "" && Utils.IsValidnumber(customer))
{
DataRow dr = batable.NewRow();
dr["Customer"] = double.Parse(sourceData.Rows[rowixd][Customerid].ToString());
dr["SalesOrg"] = sourceData.Rows[rowixd][Salesogid].ToString().Trim();
dr["Name"] = sourceData.Rows[rowixd][Nameid].ToString().Trim();
dr["House"] = sourceData.Rows[rowixd][Houseid].ToString().Trim();
dr["Street"] = sourceData.Rows[rowixd][Streetid].ToString().Trim();
dr["City"] = sourceData.Rows[rowixd][Cityid].ToString().Trim();
dr["Telephone"] = sourceData.Rows[rowixd][telephoneid].ToString().Trim();
dr["Salesoff"] = sourceData.Rows[rowixd][salesofficeid].ToString().Trim();
dr["Plant"] = sourceData.Rows[rowixd][Deliveringid].ToString().Trim();
dr["Termpayment"] = sourceData.Rows[rowixd][Termsid].ToString().Trim();
dr["Pricelist"] = sourceData.Rows[rowixd][Priceid].ToString().Trim();
dr["KeyAccount"] = double.Parse(sourceData.Rows[rowixd][Keyid].ToString());//.Trim();
dr["SalesDist"] = sourceData.Rows[rowixd][Salesdistid].ToString().Trim();
dr["Createby"] = sourceData.Rows[rowixd][Createdbyid].ToString().Trim();
dr["VATregistno"] = sourceData.Rows[rowixd][VATid].ToString().Trim();
dr["orderblock"] = sourceData.Rows[rowixd][orderblockid].ToString().Trim();
dr["salesblock"] = sourceData.Rows[rowixd][salesblockid].ToString().Trim();
dr["Createdon"] = Utils.chageExceldatetoData(sourceData.Rows[rowixd][Createdonid].ToString());// Utils.GetValueOfCellInExcel(worksheet, rowid, columValid_to);
batable.Rows.Add(dr);
}
#endregion
}// row
//conpy to server
string destConnString = Utils.getConnectionstr();
//adapter.FillSchema(sourceData, SchemaType.Source);
//sourceData.Columns["Posting Date"].DataType = typeof(DateTime);
//batable.Columns.Add("", typeof(string));
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
bulkCopy.DestinationTableName = "tbl_ka_prCustomer";
// Write from the source to the destination.
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.ColumnMappings.Add("Customer", "Customer");
bulkCopy.ColumnMappings.Add("SalesOrg", "SalesOrg");
bulkCopy.ColumnMappings.Add("Name", "Name");
bulkCopy.ColumnMappings.Add("House", "House");
bulkCopy.ColumnMappings.Add("Street", "Street");
bulkCopy.ColumnMappings.Add("City", "City");
bulkCopy.ColumnMappings.Add("Telephone", "Telephone");
bulkCopy.ColumnMappings.Add("Salesoff", "SalesOff");
bulkCopy.ColumnMappings.Add("Plant", "Plant");
bulkCopy.ColumnMappings.Add("Termpayment", "TermPayment");
bulkCopy.ColumnMappings.Add("Pricelist", "PriceList");
bulkCopy.ColumnMappings.Add("KeyAccount", "KeyAccount");
bulkCopy.ColumnMappings.Add("SalesDist", "Salesdistrict");
bulkCopy.ColumnMappings.Add("Createdon", "Createdon");
bulkCopy.ColumnMappings.Add("Createby", "Createdby");
bulkCopy.ColumnMappings.Add("VATregistno", "VATRegistration");
bulkCopy.ColumnMappings.Add("orderblock", "blockOrder");
bulkCopy.ColumnMappings.Add("salesblock", "blockArea");
try
{
bulkCopy.WriteToServer(batable);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
//copy to server
// string connection_string = Utils.getConnectionstr();
// LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
// var typeffmain = typeof(tbl_KAbaseprice);
// var typeffsub = typeof(tbl_KAbaseprice);
// VInputchange inputcdata1 = new VInputchange("", "Base price list", dc, "tbl_KAbaseprice", "tbl_KAbaseprice", typeffmain, typeffsub, "id", "id", "");
// inputcdata1.ShowDialog();
// View.Viewdatatable TB = new View.Viewdatatable(batable, "lIST DATA");
// TB.ShowDialog();
// }
}
private void importFreglassessexcel(object obj)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string);
fREEGALSSES_CTRL Rm = new fREEGALSSES_CTRL();
string username = Utils.getusername();
bool kq = Rm.deleteallFreglassesBEgin();
datainportF inf = (datainportF)obj;
string filename = inf.filename;
//OleDbCommand command = new OleDbCommand(
// @"SELECT [CUSTOMER], [SALORG],[COLAMT],[PERNO]
// FROM [Sheet1$]
// WHERE ( [CUSTOMER] is not null ) ", conn);
// ExcelProvider ExcelProvide = new ExcelProvider();
//#endregion
System.Data.DataTable sourceData = ExcelProvider.GetDataFromExcel(filename);
System.Data.DataTable batable = new System.Data.DataTable();
batable.Columns.Add("CUSTOMER", typeof(double));
batable.Columns.Add("SALORG", typeof(string));
// batable.Columns.Add("PERNO", typeof(string));
batable.Columns.Add("Freeglass3years", typeof(int));
batable.Columns.Add("Freeglass3phantram", typeof(int));
batable.Columns.Add("AmountFreeglassesValue", typeof(double));
batable.Columns.Add("TypeDoc", typeof(string));
batable.Columns.Add("userupdate", typeof(string));
batable.Columns.Add("Posting_Date", typeof(DateTime));
// CUSTOMER SALORG Freeglass3years Freeglass3phantram AmountFreeglassesValue
int CUSTOMERid = -1;
int SALORGid = -1;
int Freeglass3yearsid = -1;
int Freeglass3phantramid = -1;
int AmountFreeglassesValueid = -1;
int rowseet = sourceData.Rows.Count;
if (rowseet > 5)
{
rowseet = 5;
}
for (int rowid = 0; rowid < rowseet; rowid++)
{
// headindex = 1;
for (int columid = 0; columid < sourceData.Columns.Count; columid++)
{
#region
string value = sourceData.Rows[rowid][columid].ToString();
// MessageBox.Show(value +":"+ rowid);
if (value != null && value != "")
{
// #region setcolum
if (value.Trim() == ("CUSTOMER"))
{
CUSTOMERid = columid;
// headindex = rowid;
}
if (value.Trim() == ("SALORG"))
{
SALORGid = columid;
// headindex = 0;
}
if (value.Trim() == ("Freeglass3years"))
{
Freeglass3yearsid = columid;
// headindex = 0;
}
if (value.Trim() == ("Freeglass3phantram"))
{
Freeglass3phantramid = columid;
}
if (value.Trim() == ("AmountFreeglassesValue"))
{
AmountFreeglassesValueid = columid;
}
}
#endregion
} // colum
} // roww off heatder
if (CUSTOMERid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột CUSTOMER", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (SALORGid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột SALORG", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Freeglass3yearsid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Freeglass3years", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Freeglass3phantramid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Freeglass3phantram", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (AmountFreeglassesValueid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột AmountFreeglassesValue", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++)
{
#region
// string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
string CUSTOMERvl = sourceData.Rows[rowixd][CUSTOMERid].ToString();
if (CUSTOMERvl != "" && Utils.IsValidnumber(CUSTOMERvl))
{
if (double.Parse(CUSTOMERvl) > 0)
{
DataRow dr = batable.NewRow();
dr["CUSTOMER"] = double.Parse(sourceData.Rows[rowixd][CUSTOMERid].ToString());//.Trim();
dr["SALORG"] = sourceData.Rows[rowixd][SALORGid].ToString().Trim();
if (sourceData.Rows[rowixd][Freeglass3yearsid].ToString() != "")
{
dr["Freeglass3years"] = int.Parse(sourceData.Rows[rowixd][Freeglass3yearsid].ToString().Trim());
}
else
{
dr["Freeglass3years"] = 0;
}
//----------------
if (sourceData.Rows[rowixd][Freeglass3phantramid].ToString() != "")
{
dr["Freeglass3phantram"] = int.Parse(sourceData.Rows[rowixd][Freeglass3phantramid].ToString().Trim());
}
else
{
dr["Freeglass3phantram"] = 0;
}
///----
if (sourceData.Rows[rowixd][Freeglass3yearsid].ToString() != "")
{
dr["AmountFreeglassesValue"] = int.Parse(sourceData.Rows[rowixd][AmountFreeglassesValueid].ToString().Trim());
}
else
{
dr["AmountFreeglassesValue"] = 0;
}
dr["TypeDoc"] = "Begin";//.Trim();//sourceData.Rows[rowixd][COLAMTid].ToString().Trim();
dr["userupdate"] = username;
dr["Posting_Date"] = DateTime.Today;
batable.Rows.Add(dr);
}
}
#endregion
// Utils util = new Utils();
//---------------fill data
}
string destConnString = Utils.getConnectionstr();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
bulkCopy.DestinationTableName = "tblFBL5NNewCol3year";
// Write from the source to the destination.
bulkCopy.ColumnMappings.Add("CUSTOMER", "[Customer code]");
bulkCopy.ColumnMappings.Add("SALORG", "[Region]");
bulkCopy.ColumnMappings.Add("Freeglass3years", "[Freeglass3years]");
bulkCopy.ColumnMappings.Add("Freeglass3phantram", "[Freeglass3phantram]");
bulkCopy.ColumnMappings.Add("AmountFreeglassesValue", "[AmountFreeglassesValue]");
bulkCopy.ColumnMappings.Add("TypeDoc", "TypeDoc");
bulkCopy.ColumnMappings.Add("userupdate", "[userupdate]");
bulkCopy.ColumnMappings.Add("Posting_Date", "[Posting Date]");
try
{
bulkCopy.WriteToServer(batable);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
}
private void importsexcel2(object obj)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string);
Salesinput_ctrl md = new Salesinput_ctrl();
bool kq = md.deleteedlp();
//if (!kq)
//{
// MessageBox.Show("Không xóa được bảng Edlpinput!", "Thông báo ", MessageBoxButtons.OK, MessageBoxIcon.Information);
//}
datainportF inf = (datainportF)obj;
string filename = inf.filename;
string connectionString = "";
if (filename.Contains(".xlsx") || filename.Contains(".XLSX"))
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";" + "Extended Properties=Excel 12.0;";
}
else
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " + filename + ";" + "Extended Properties=Excel 8.0;";
}
//------
//---------------fill data
ExcelProvider ExcelProvide = new ExcelProvider();
//#endregion
System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename);
// Sales Group Sales Group desc Sales Off Sales Office Desc
System.Data.DataTable batable = new System.Data.DataTable();
batable.Columns.Add("Soldto", typeof(float));
batable.Columns.Add("CustName", typeof(string));
batable.Columns.Add("MatNumber", typeof(string));
batable.Columns.Add("SalesOrg", typeof(string));
batable.Columns.Add("InvoiceDocNr", typeof(float));
batable.Columns.Add("OutboundDelivery", typeof(float));
batable.Columns.Add("DeliveryDate", typeof(DateTime));
batable.Columns.Add("SalesDistrict", typeof(string));
batable.Columns.Add("SalesDistrictdesc", typeof(string));
// batable.Columns.Add("SalesGroup", typeof(string));
// batable.Columns.Add("SalesOff", typeof(string));
// batable.Columns.Add("SalesOfficeDesc", typeof(float));
batable.Columns.Add("KeyAccNr", typeof(float));
batable.Columns.Add("InvoiceDate", typeof(DateTime));
batable.Columns.Add("MatGroup", typeof(float));
batable.Columns.Add("MatGroupText", typeof(string));
batable.Columns.Add("MatText", typeof(string));
batable.Columns.Add("CondType", typeof(string));
// batable.Columns.Add("CondTypedesc", typeof(string));
batable.Columns.Add("BilledQty", typeof(float));
batable.Columns.Add("Netvalue", typeof(float));
batable.Columns.Add("CondValue", typeof(float));
batable.Columns.Add("Currency", typeof(string));
// batable.Columns.Add("Username", typeof(string));
batable.Columns.Add("UoM", typeof(string));
string username = Utils.getusername();
batable.Columns.Add("Username");
batable.Columns["Username"].DefaultValue = username;
#region setcolum
int Soldtoid = -1;
int CustNameid = -1;
int MatNumberid = -1;
int SalesOrgid = -1;
int InvoiceDocNrid = -1;
int OutboundDeliveryid = -1;
int DeliveryDateid = -1;
int SalesDistrictid = -1;
int SalesDistrictdescid = -1;
int KeyAccNrid = -1;
int InvoiceDateid = -1;
int MatGroupid = -1;
int MatGroupTextid = -1;
int MatTextid = -1;
int BilledQtyid = -1;
int Netvalueid = -1;
int CondValueid = -1;
int Currencyid = -1;
int UoMid = -1;
int CondTypeid = -1;
// View.Viewdatatable vi1 = new View.Viewdatatable(sourceData, "Test");
// vi1.ShowDialog();
// int headindex = -2;
for (int rowid = 0; rowid < 3; rowid++)
{
// headindex = 1;
for (int columid = 0; columid < sourceData.Columns.Count; columid++)
{
string value = sourceData.Rows[rowid][columid].ToString();
// MessageBox.Show(value +":"+ rowid);
if (value != null)
{
#region setcolum
if (value.Trim() == "Sold-to")
{
Soldtoid = columid;
// headindex = rowid;
}
if (value.Trim() == ("Cust Name"))
{
CustNameid = columid;
// headindex = rowid;
}
if (value.Trim() == ("Cond Type"))
{
CondTypeid = columid;
// headindex = rowid;
}
if (value.Trim() == ("Mat Number"))
{
MatNumberid = columid;
// headindex = rowid;
}
if (value.Trim() == "Sales Org")
{
SalesOrgid = columid;
// headindex = rowid;
}
if (value.Trim() == "Invoice Doc Nr")
{
InvoiceDocNrid = columid;
// headindex = rowid;
}
if (value.Trim() == "Outbound Delivery")
{
OutboundDeliveryid = columid;
// headindex = rowid;
}
if (value.Trim() == "Delivery Date")
{
DeliveryDateid = columid;
// headindex = rowid;
}
if (value.Trim() == "Sales District")
{
SalesDistrictid = columid;
// headindex = rowid;
}
if (value.Trim() == "Sales District desc")
{
SalesDistrictdescid = columid;
// headindex = rowid;
}
if (value.Trim() == "Key Acc Nr")
{
KeyAccNrid = columid;
// headindex = rowid;
}
if (value.Trim() == "Mat Group")
{
MatGroupid = columid;
// headindex = rowid;
}
if (value.Trim() == "Mat Group Text")
{
MatGroupTextid = columid;
// headindex = rowid;
}
if (value.Trim() == "Mat Text")
{
MatTextid = columid;
// headindex = rowid;
}
if (value.Trim() == "UoM")
{
UoMid = columid;
// headindex = rowid;
}
if (value.Trim() == "Billed Qty")
{
BilledQtyid = columid;
// headindex = rowid;
}
if (value.Trim() == "Net value")
{
Netvalueid = columid;
// headindex = rowid;
}
if (value.Trim().Contains("Cond Value"))
{
CondValueid = columid;
// headindex = rowid;
}
if (value.Trim() == "Invoice Date")
{
InvoiceDateid = columid;
// headindex = rowid;
}
if (value.Trim() == "Currency")
{
Currencyid = columid;
// headindex = rowid;
}
#endregion
}
} // colum
} // roww off heatder
#endregion
if (Soldtoid == -1)
{
MessageBox.Show("Please check sold-to colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (CondTypeid == -1)
{
MessageBox.Show("Please check Cond Type colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (CustNameid == -1)
{
MessageBox.Show("Please check Customer colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (MatNumberid == -1)
{
MessageBox.Show("Please check MatNumber colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (SalesOrgid == -1)
{
MessageBox.Show("Please check SalesOrg colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (InvoiceDocNrid == -1)
{
MessageBox.Show("Please check Invoice Doc Nr colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (OutboundDeliveryid == -1)
{
MessageBox.Show("Please check Outbound Delivery colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (DeliveryDateid == -1)
{
MessageBox.Show("Please check Delivery Date colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (SalesDistrictid == -1)
{
MessageBox.Show("Please check Sales District colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (SalesDistrictdescid == -1)
{
MessageBox.Show("Please check Sales District desc colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (KeyAccNrid == -1)
{
MessageBox.Show("Please check KeyAcc Nr colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (InvoiceDateid == -1)
{
MessageBox.Show("Please check Invoice Date colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (MatGroupid == -1)
{
MessageBox.Show("Please check MatGroup colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (MatGroupTextid == -1)
{
MessageBox.Show("Please check MatGroupText colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (BilledQtyid == -1)
{
MessageBox.Show("Please check Billed Qty colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Netvalueid == -1)
{
MessageBox.Show("Please check Netvalue colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (CondValueid == -1)
{
MessageBox.Show("Please check CondValue colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Currencyid == -1)
{
MessageBox.Show("Please check Currency colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (UoMid == -1)
{
MessageBox.Show("Please check UoM colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++)
{
#region setvalue of pricelist
// string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
string customer = sourceData.Rows[rowixd][Soldtoid].ToString();
if (customer != "" && Utils.IsValidnumber(customer))
{
DataRow dr = batable.NewRow();
dr["Soldto"] = double.Parse(sourceData.Rows[rowixd][Soldtoid].ToString());
dr["CustName"] = sourceData.Rows[rowixd][CustNameid].ToString().Trim();
dr["MatNumber"] = sourceData.Rows[rowixd][MatNumberid].ToString().Trim();
dr["SalesOrg"] = sourceData.Rows[rowixd][SalesOrgid].ToString().Trim();
dr["InvoiceDocNr"] = double.Parse(sourceData.Rows[rowixd][InvoiceDocNrid].ToString().Trim());
dr["OutboundDelivery"] = double.Parse(sourceData.Rows[rowixd][OutboundDeliveryid].ToString().Trim());
dr["DeliveryDate"] = Utils.chageExceldatetoData(sourceData.Rows[rowixd][DeliveryDateid].ToString().Trim());
dr["SalesDistrict"] = sourceData.Rows[rowixd][SalesDistrictid].ToString().Trim();
dr["CondType"] = sourceData.Rows[rowixd][CondTypeid].ToString().Trim();
dr["SalesDistrictdesc"] = sourceData.Rows[rowixd][SalesDistrictdescid].ToString().Trim();
if (sourceData.Rows[rowixd][KeyAccNrid].ToString() != "" && sourceData.Rows[rowixd][KeyAccNrid] != null)
{
dr["KeyAccNr"] = double.Parse(sourceData.Rows[rowixd][KeyAccNrid].ToString().Trim());
}
dr["MatGroup"] = double.Parse(sourceData.Rows[rowixd][MatGroupid].ToString().Trim());
dr["MatGroupText"] = sourceData.Rows[rowixd][MatGroupTextid].ToString();//.Trim();
dr["MatText"] = sourceData.Rows[rowixd][MatTextid].ToString().Trim();
dr["BilledQty"] = double.Parse(sourceData.Rows[rowixd][BilledQtyid].ToString().Trim());
dr["Netvalue"] = double.Parse(sourceData.Rows[rowixd][Netvalueid].ToString().Trim());
dr["Currency"] = sourceData.Rows[rowixd][Currencyid].ToString().Trim();
dr["CondValue"] = double.Parse(sourceData.Rows[rowixd][CondValueid].ToString().Trim());
dr["InvoiceDate"] = Utils.chageExceldatetoData(sourceData.Rows[rowixd][InvoiceDateid].ToString());// Utils.GetValueOfCellInExcel(worksheet, rowid, columValid_to);
dr["UoM"] = sourceData.Rows[rowixd][UoMid].ToString().Trim();
// dr["Username"] = Utils.chageExceldatetoData(sourceData.Rows[rowixd][Createdonid].ToString());// Utils.GetValueOfCellInExcel(worksheet, rowid, columValid_to);
batable.Rows.Add(dr);
}
#endregion
}// row
string destConnString = Utils.getConnectionstr();
//---------------fill data
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.DestinationTableName = "tbl_kasalesTemp";
// Write from the source to the destination.
bulkCopy.ColumnMappings.Add("Soldto", "[Sold-to]");
bulkCopy.ColumnMappings.Add("[CustName]", "[Cust Name]");
bulkCopy.ColumnMappings.Add("[MatNumber]", "[Mat Number]");
bulkCopy.ColumnMappings.Add("[SalesOrg]", "[Sales Org]");
bulkCopy.ColumnMappings.Add("[InvoiceDocNr]", "[Invoice Doc Nr]");
bulkCopy.ColumnMappings.Add("[OutboundDelivery]", "[Outbound Delivery]");
bulkCopy.ColumnMappings.Add("[DeliveryDate]", "[Delivery Date]");
bulkCopy.ColumnMappings.Add("[SalesDistrict]", "[Sales District]");
bulkCopy.ColumnMappings.Add("[SalesDistrictdesc]", "[Sales District desc]");
// bulkCopy.ColumnMappings.Add("[SalesGroup]", "[Sales Group]");
//// bulkCopy.ColumnMappings.Add("[SalesOff]", "[Sales Off]");
// bulkCopy.ColumnMappings.Add("[SalesOfficeDesc]", "[Sales Office Desc]");
bulkCopy.ColumnMappings.Add("[KeyAccNr]", "[Key Acc Nr]");
bulkCopy.ColumnMappings.Add("[InvoiceDate]", "[Invoice Date]");
bulkCopy.ColumnMappings.Add("[MatGroup]", "[Mat Group]");
bulkCopy.ColumnMappings.Add("[MatGroupText]", "[Mat Group Text]");
bulkCopy.ColumnMappings.Add("[MatText]", "[Mat Text]");
bulkCopy.ColumnMappings.Add("[CondType]", "[Cond Type]");
// bulkCopy.ColumnMappings.Add("[CondTypedesc]", "[Cond Type desc]");
bulkCopy.ColumnMappings.Add("[BilledQty]", "[EC]");
bulkCopy.ColumnMappings.Add("[Netvalue]", "[NSR]");
bulkCopy.ColumnMappings.Add("[CondValue]", "[GSR]");
bulkCopy.ColumnMappings.Add("[Currency]", "[Currency]");
bulkCopy.ColumnMappings.Add("[Username]", "[Username]");
// bulkCopy.ColumnMappings.Add("[SalesGroupdesc]", "[Sales Group desc]");
bulkCopy.ColumnMappings.Add("[UoM]", "[UoM]");
// bulkCopy.ColumnMappings.Add("[UoM]", "[UoM]");
try
{
bulkCopy.WriteToServer(batable);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
}
