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 importsexce2sales(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("CustName", typeof(string));
batable.Columns.Add("MatNumber", typeof(string));
batable.Columns.Add("SalesOrg", typeof(string));
batable.Columns.Add("InvoiceDocNr", typeof(double));
batable.Columns.Add("OutboundDelivery", typeof(double));
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(double));
batable.Columns.Add("KeyAccNr", typeof(double));
batable.Columns.Add("InvoiceDate", typeof(DateTime));
batable.Columns.Add("MatGroup", typeof(double));
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(double));
batable.Columns.Add("Netvalue", typeof(double));
batable.Columns.Add("CondValue", typeof(double));
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 < 6; rowid++)
{
if (rowid < sourceData.Rows.Count)
{
#region
for (int columid = 0; columid < sourceData.Columns.Count; columid++)
{
#region
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
}
#endregion
}// colum
#endregion
}
}// 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());
if (Utils.chageExceldatetoData(sourceData.Rows[rowixd][DeliveryDateid].ToString().Trim()) == new DateTime(00001, 01, 01))
{
MessageBox.Show("Định dạng ngày tháng của dòng " + rowixd.ToString() + " cột " + DeliveryDateid.ToString() + " sai, must be dd.MM.yyyy", "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
else
{
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());
if (Utils.chageExceldatetoData(sourceData.Rows[rowixd][InvoiceDateid].ToString()) == new DateTime(00001, 01, 01))
{
MessageBox.Show("Định dạng ngày tháng của dòng " + rowixd.ToString() + " cột " + InvoiceDateid.ToString() + " sai, must be dd.MM.yyyy", "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
else
{
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();
}
}
}