/// <summary>
/// 导入
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void ImprnBut_Click(object sender, EventArgs e)
{
try
{
ImprnBut.Enabled = false;
DateTime start = DateTime.Now;
if (lbOpposite.Items.Count > 0)
{
var keyValuePairs = new List <KeyValuePair <string, string> >();
foreach (var item in lbOpposite.Items)
{
var tempArray = item.ToString().Split('-');
keyValuePairs.Add(new KeyValuePair <string, string>(tempArray[0], tempArray[1]));
}
if (keyValuePairs.Count > 0)
{
var listField = new List <string>();
var listValue = new List <string>();
var listCommandText = new List <string>();
List <ZtoUserEntity> userList = null;
if (ckUseDefaultSendMan.Checked)
{
var userManager = new ZtoUserManager(BillPrintHelper.DbHelper);
// 获取默认发件人
userList = userManager.GetList <ZtoUserEntity>(new KeyValuePair <string, object>(ZtoUserEntity.FieldIsDefault, 1), new KeyValuePair <string, object>(ZtoUserEntity.FieldIssendorreceive, 1));
}
foreach (DataRow data in _execldt.Rows)
{
foreach (var keyValuePair in keyValuePairs)
{
// Excel每一列的值
var value = data[keyValuePair.Key].ToString();
// Excel的值要特殊处理一些符号（不然插入数据库有问题的）
if (!string.IsNullOrEmpty(value))
{
value = value.Replace("'", "").Replace("\"", "").Replace("=", "").Replace(" ", "");
}
// 获取到实际的数据库列名称
var dataField = BillPrintHelper.GetFieldByName(keyValuePair.Value);
listField.Add(dataField);
//if (dataField == ZtoPrintBillEntity.FieldOrderNumber)
//{
// if (string.IsNullOrEmpty(value))
// {
// value = Guid.NewGuid().ToString("N");
// }
//}
listValue.Add(value);
}
// 使用默认发件人
if (ckUseDefaultSendMan.Checked)
{
if (userList.Any())
{
var userEntity = userList.FirstOrDefault();
listField.Add(ZtoPrintBillEntity.FieldSendMan);
listValue.Add(userEntity.Realname);
if (!string.IsNullOrEmpty(userEntity.Mobile) && string.IsNullOrEmpty(userEntity.TelePhone))
{
listField.Add(ZtoPrintBillEntity.FieldSendPhone);
listValue.Add(userEntity.Mobile);
}
if (string.IsNullOrEmpty(userEntity.Mobile) && !string.IsNullOrEmpty(userEntity.TelePhone))
{
listField.Add(ZtoPrintBillEntity.FieldSendPhone);
listValue.Add(userEntity.TelePhone);
}
if (!string.IsNullOrEmpty(userEntity.Mobile) && !string.IsNullOrEmpty(userEntity.TelePhone))
{
listField.Add(ZtoPrintBillEntity.FieldSendPhone);
listValue.Add(userEntity.Mobile);
}
listField.Add(ZtoPrintBillEntity.FieldSendProvince);
listValue.Add(userEntity.Province);
listField.Add(ZtoPrintBillEntity.FieldSendCity);
listValue.Add(userEntity.City);
listField.Add(ZtoPrintBillEntity.FieldSendCounty);
listValue.Add(userEntity.County);
listField.Add(ZtoPrintBillEntity.FieldSendDeparture);
listValue.Add(userEntity.Province);
listField.Add(ZtoPrintBillEntity.FieldSendAddress);
listValue.Add(userEntity.Address);
listField.Add(ZtoPrintBillEntity.FieldSendPostcode);
listValue.Add(userEntity.Postcode);
listField.Add(ZtoPrintBillEntity.FieldSendDate);
listValue.Add(DateTime.Now.ToString(BaseSystemInfo.DateFormat));
listField.Add(ZtoPrintBillEntity.FieldCreateOn);
listValue.Add(DateTime.Now.ToString(BaseSystemInfo.DateTimeFormat));
}
}
// 如果没有导入订单号这个字段就要手动导入了，因为这个很重要
if (listField.All(p => p != ZtoPrintBillEntity.FieldOrderNumber))
{
listField.Add(ZtoPrintBillEntity.FieldOrderNumber);
listValue.Add(Guid.NewGuid().ToString("N"));
}
// 到付
if (listField.All(p => p != ZtoPrintBillEntity.FieldToPayMent))
{
listField.Add(ZtoPrintBillEntity.FieldToPayMent);
listValue.Add("0");
}
// 代收
if (listField.All(p => p != ZtoPrintBillEntity.FieldGoodsPayMent))
{
listField.Add(ZtoPrintBillEntity.FieldGoodsPayMent);
listValue.Add("0");
}
string tempCommandText = string.Format("INSERT INTO {0} ({1}) VALUES({2})", ZtoPrintBillEntity.TableName, string.Join(",", listField), "'" + string.Join("','", listValue) + "'");
listCommandText.Add(tempCommandText);
listField.Clear();
listValue.Clear();
}
if (listCommandText.Count > 0)
{
var resultCount = 0;
// 用事务插入最快还有删除
using (IDbHelper dbHelper = DbHelperFactory.GetHelper(CurrentDbType.SQLite, BillPrintHelper.BillPrintConnectionString))
{
dbHelper.BeginTransaction();
foreach (var commandText in listCommandText)
{
try
{
resultCount = resultCount + (dbHelper.ExecuteNonQuery(commandText));
}
catch (Exception ex)
{
dbHelper.RollbackTransaction();
}
}
dbHelper.CommitTransaction();
}
TimeSpan ts = DateTime.Now - start;
XtraMessageBox.Show(string.Format("成功导入{0}条，耗时:{1}", resultCount, string.Format("{0}分{1}秒", ts.Minutes, ts.Seconds)), AppMessage.MSG0000, MessageBoxButtons.OK, MessageBoxIcon.Information);
Close();
}
else
{
XtraMessageBox.Show("导入失败", AppMessage.MSG0000, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
else
{
XtraMessageBox.Show("请从左边选择对应的导入关系列", AppMessage.MSG0000, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception exception)
{
XtraMessageBox.Show(exception.Message, AppMessage.MSG0000);
}
finally
{
ImprnBut.Enabled = true;
}
}