/// <summary>
/// EXCEL文件导入
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
private bool Excel2DB(DataTable dt)
{
bool retVal = false;
string pKey = ""; //alcon 单号
int fInterID = 0; //内联ID,外键
int fEntryID = 0;
int successCount = 0;
string fBillNo = "";//金蝶收货单号
HuaLiQin.DAL.t_ICItem dalICItem = new DAL.t_ICItem();
HuaLiQin.DAL.PoInStock dalPOInStock = new DAL.PoInStock();
HuaLiQin.DAL.PoInStockEntry dalPOInStockEntry = new DAL.PoInStockEntry();
HuaLiQin.DAL.t_AuxItem dalAuxItem = new t_AuxItem();
HuaLiQin.Model.t_ICItem mICItem = new Model.t_ICItem();
for (int i = 0; i < dt.Rows.Count; i++)
{
//alcon 单号
string fAlconBillNo = dt.Rows[i][0].ToString();
//单据日期
//DateTime fdate = DateTime.Parse(dt.Rows[i][3].ToString().Substring(0, 4) + "-" + dt.Rows[i][3].ToString().Substring(4, 2) + "-" + dt.Rows[i][3].ToString().Substring(6));
DateTime fdate = System.DateTime.Now;
//Alcon产品代码
string fAlconItemID = dt.Rows[i][8].ToString();
//数量
decimal fQty = decimal.Parse(dt.Rows[i][4].ToString());
//有效期至
DateTime fPeriodDate = DateTime.Parse(dt.Rows[i][6].ToString().Substring(0, 4) + "-" + dt.Rows[i][6].ToString().Substring(4, 2) + "-" + dt.Rows[i][6].ToString().Substring(6));
//批号
string fBatchNo = dt.Rows[i][3].ToString();
//价格
decimal fPrice = 0;
//Alcon行号
string fEntrySelfP0386 = "";
//仓库Id
int fStockID = 0;
switch (dt.Rows[i][7].ToString())
{
case "CSW":
fStockID = (int)StockName.CSW;
break;
case "RET":
fStockID = (int)StockName.RET;
break;
case "EPD":
fStockID = (int)StockName.EPD;
break;
case "JWI":
fStockID = (int)StockName.JWI;
break;
case "JQU":
fStockID = (int)StockName.JQU;
break;
case "JDA":
fStockID = (int)StockName.JDA;
break;
default:
fStockID = (int)StockName.CSW;
break;
}
//注册证号
int fAuxPropID = dalAuxItem.getAuxPropIDByKey(dt.Rows[i][9].ToString());
if (fAuxPropID == 0)
{
DesktopAlert.Show("<h2>第" + (i + 1).ToString() + "行的注册证号无效！" + "</h2>");
}
if (dalICItem.Exists(fAlconItemID) == true)
{
if (pKey != fAlconBillNo)
{
pKey = fAlconBillNo;
//金蝶收货单号
if (string.IsNullOrEmpty(dalPOInStock.GetMaxFBillNo()))
{
fBillNo = "DA000001";
}
else
{
fBillNo = "DA" + (int.Parse(dalPOInStock.GetMaxFBillNo().Substring(2)) + 1).ToString().PadLeft(6, '0');
}
//内联编号
if (string.IsNullOrEmpty(dalPOInStock.GetMaxFInterID()))
{
fInterID = 1142;
}
else
{
fInterID = int.Parse(dalPOInStock.GetMaxFInterID()) + 1;
}
//写master表
if (dalPOInStock.InsertPoInStock(fInterID, fBillNo, fdate, pKey) == true)
{
fEntryID = 1;
}
}
//写Detail表
//在ICItem得到商品信息
mICItem = dalICItem.GetModel(fAlconItemID);
//金蝶商品编号
int fItemID = mICItem.FItemID;
//单位编号
int fUnitID = mICItem.FUnitID;//单位ID
//有效期
int fKFPeriod = (int)mICItem.FKFPeriod;
//decimal fKFPeriod = DBNull.Value;
DateTime fKFDate = fPeriodDate.AddDays(-fKFPeriod);
if (dalPOInStockEntry.InsertPoInStockEntry(fInterID, fEntryID, fItemID, fQty, fPrice, fQty * fPrice, fUnitID, fPrice, fQty, fKFDate, fKFPeriod, fStockID, fPeriodDate, fEntrySelfP0386, fQty, fQty, fBatchNo, 1, fAuxPropID) == true)
{
fEntryID++;
successCount++;
}
else
{
DesktopAlert.Show("!!!");
}
}
else
{
//MessageBox.Show(fAlconItemID + "产品编号不存在！");
DesktopAlert.Show("<h2>" + "产品编号不存在！" + "</h2>");
dataGridView1.Rows[i].Selected = true;
}
}
//MessageBox.Show("总共有 " + dt.Rows.Count.ToString() + " 条记录,导入失败 " + (dt.Rows.Count - successCount).ToString() + " 条！");
DesktopAlert.Show("<h2>" + "总共有 " + dt.Rows.Count.ToString() + " 条记录,导入失败 " + (dt.Rows.Count - successCount).ToString() + " 条！" + "</h2>");
return(retVal);
}
/// <summary>
/// TXT文件导入
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
private bool Text2DB(DataTable dt)
{
bool retVal = true;
string pKey = ""; //alcon 单号
int fInterID = 0; //内联ID,外键
int fEntryID = 0;
int successCount = 0;
string fBillNo = "";//金蝶收货单号
HuaLiQin.DAL.t_ICItem dalICItem = new DAL.t_ICItem();
HuaLiQin.DAL.PoInStock dalPOInStock = new DAL.PoInStock();
HuaLiQin.DAL.PoInStockEntry dalPOInStockEntry = new DAL.PoInStockEntry();
HuaLiQin.Model.t_ICItem mICItem = new Model.t_ICItem();
for (int i = 0; i < dt.Rows.Count; i++)
{
//alcon 单号
string fAlconBillNo = dt.Rows[i][0].ToString();
//单据日期
DateTime fdate = DateTime.Parse(dt.Rows[i][3].ToString().Substring(0, 4) + "-" + dt.Rows[i][3].ToString().Substring(4, 2) + "-" + dt.Rows[i][3].ToString().Substring(6));
//Alcon产品代码
string fAlconItemID = dt.Rows[i][15].ToString();
//数量
decimal fQty = decimal.Parse(dt.Rows[i][16].ToString());
//价格
decimal fPrice = 0;
//Alcon行号
string fEntrySelfP0386 = dt.Rows[i][14].ToString();
//仓库Id 武汉 alcon:1165
int fStockID = 0;
switch (dt.Rows[i][18].ToString())
{
case "CSW":
fStockID = (int)StockName.CSW;
break;
case "RET":
fStockID = (int)StockName.RET;
break;
case "EPD":
fStockID = (int)StockName.EPD;
break;
case "JWI":
fStockID = (int)StockName.JWI;
break;
case "JQU":
fStockID = (int)StockName.JQU;
break;
case "JDA":
fStockID = (int)StockName.JDA;
break;
default:
fStockID = (int)StockName.CSW;
break;
}
if (dalICItem.Exists(fAlconItemID) == true)
{
if (pKey != fAlconBillNo)
{
pKey = fAlconBillNo;
//金蝶收货单号
string MaxBillNo = dalPOInStock.GetMaxFBillNo();
if (string.IsNullOrEmpty(MaxBillNo))
{
fBillNo = "DR000001";
}
else
{
fBillNo = "DR" + (int.Parse(MaxBillNo.Substring(2)) + 1).ToString().PadLeft(6, '0');
}
//内联编号
string MaxFInterID = dalPOInStock.GetMaxFInterID();
if (string.IsNullOrEmpty(MaxFInterID))
{
fInterID = 1142;
}
else
{
fInterID = int.Parse(MaxFInterID) + 1;
}
//写master表
if (dalPOInStock.InsertPoInStock(fInterID, fBillNo, fdate, pKey) == true)
{
fEntryID = 1;
}
}
//写Detail表
//在ICItem得到商品信息
mICItem = dalICItem.GetModel(fAlconItemID);
//金蝶商品编号
int fItemID = mICItem.FItemID;
//单位编号
int fUnitID = mICItem.FUnitID;//单位ID
//有效期
//decimal fKFPeriod = mICItem.FKFPeriod;
//decimal fKFPeriod = DBNull.Value;
if (dalPOInStockEntry.InsertPoInStockEntry(fInterID, fEntryID, fItemID, fQty, fPrice, fQty * fPrice, fUnitID, fPrice, fQty, new DateTime(2200, 12, 31), 0, fStockID, new DateTime(2200, 12, 31), fEntrySelfP0386, fQty, fQty, "", 0, 0) == true)
{
fEntryID++;
successCount++;
}
}
else
{
//MessageBox.Show(fAlconItemID + "产品编号不存在！");
DesktopAlert.Show("<h2>" + fAlconItemID + "产品编号不存在！" + "</h2>");
dataGridView1.Rows[i].Selected = true;
}
}
MessageBox.Show("总共有 " + dt.Rows.Count.ToString() + " 条记录," + "导入失败 " + (dt.Rows.Count - successCount).ToString() + " 条！");
//DesktopAlert.Show("<h2>" + "总共有 " + dt.Rows.Count.ToString() + " 条记录," + "导入失败 " + (dt.Rows.Count - successCount).ToString() + " 条！" + "</h2>");
return(retVal);
}