private void button1_Click(object sender, EventArgs e)
{
ICStockBillService bll = new ICStockBillService();
ICStockBillEntryService bllEntry = new ICStockBillEntryService();
int retval = 0;
if (dateTimeInput1.Value.ToString() != "0001/1/1 0:00:00" && dateTimeInput2.Value.ToString() != "0001/1/1 0:00:00")
{
//Get Max finterId for this period
//maxid = bll.GetMaxFInterId(dateTimeInput1.Value, dateTimeInput2.Value);
//Get Min FInterId For this Period
//minid = bll.GetMinFInterId(dateTimeInput1.Value, dateTimeInput2.Value);
//数据有效
//if (minid > 0 && maxid > 0)
//{
//Delete ICStockbill and ICStockBillEntry
retval = bll.Delete(ConnectionName, dateTimeInput1.Value.ToShortDateString(), dateTimeInput2.Value.ToShortDateString(), TranType);
MessageBox.Show(retval > 0 ? "共有 " + retval + " 记录被删除。" : "没有删除任何记录!");
//}
//else
//{
/// MessageBox.Show("请仔细检查起止时间输入是否正确！");
//}
}
else
{
MessageBox.Show("请输入起始时间和结束时间！");
}
}
/// <summary>
///
/// </summary>
/// <param name="dt"></param>
private int checkData(DataTable dt)
{
int ierrData = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
string productName = dt.Rows[i]["商品名称"].ToString();
string productCode = dt.Rows[i]["商品长代码"].ToString();
ICStockBillService bStockBill = new ICStockBillService();
ICStockBillEntryService bStockBillEntry = new ICStockBillEntryService();
//单位编号
string strUnit = dt.Rows[i]["单位"].ToString();
if (bStockBillEntry.GetUnitID(sConnectionString, strUnit) == 0)
{
dataGridView1.Rows[i].Cells["单位"].Style.BackColor = Color.Red;
ierrData++;
}
if (bStockBillEntry.checkProductID(sConnectionString, productCode, productName) == false)
{
dataGridView1.Rows[i].Cells["商品长代码"].Style.BackColor = Color.Red;
ierrData++;
}
}
return(ierrData);
}
/// <summary>
/// 宜昌襄樊分公司导入
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
private bool Excel2DB1(DataTable dt)
{
string fBillNo = ""; // 主键列
//int fInterID = 0;//内联ID,外键列
int fEntryID = 0; //明细表记录行号
int successCount = 0; //成功完成导入的个数
int iSuccessBillCount = 0;
//string fBillNo = "";//金蝶收货单号
bool retVal = false;
ICStockBillService bICStockBill = new ICStockBillService();
ICStockBillEntryService bICStockBillEntry = new ICStockBillEntryService();
for (int i = 0; i < dt.Rows.Count; i++)
{
ICStockBill mICStockBill = new ICStockBill();
//商品代码
int fItemID = bICStockBillEntry.GetfItemId(sConnectionString, dt.Rows[i]["商品长代码"].ToString());
if (fItemID > 0)
{
if (fBillNo != dt.Rows[i]["单据编号"].ToString())
{
//单据号
mICStockBill.FBillNo = dt.Rows[i]["单据编号"].ToString();
fBillNo = mICStockBill.FBillNo;
//内联编号
///fInterID = bICStockBill.GetMaxFInterID(sConnectionString);
bICStockBill.UpdateFInterID(sConnectionString);
mICStockBill.FInterID = bICStockBill.GetMaxFInterID(sConnectionString);
//fROB
mICStockBill.FROB = decimal.Parse(dt.Rows[i]["实收数量"].ToString()) > 0 ? 1 : -1;
//日期
mICStockBill.FDate = DateTime.Parse(dt.Rows[i]["日期"].ToString());
//单据类型
mICStockBill.FTranType = 1;
if (SubCompany.ToLower() == "yichang")
{
mICStockBill.FSupplyID = 4478;//武汉友谊
mICStockBill.FFManagerID = 4175;
mICStockBill.FSManagerID = 4174;
mICStockBill.FBillerID = 16394;
}
else if (SubCompany.ToLower() == "xiangfan") //
{
mICStockBill.FSupplyID = 198; //武汉友谊
mICStockBill.FFManagerID = 4128;
mICStockBill.FSManagerID = 4128;
mICStockBill.FBillerID = 16394;
}
else //jingzhou
{
mICStockBill.FSupplyID = 198;//武汉友谊
mICStockBill.FFManagerID = 4395;
mICStockBill.FSManagerID = 4395;
mICStockBill.FBillerID = 16394;
}
///if (InsertStockBill(sConnectionString, fInterID, fBillNo, fdate, frob) == true && bStockBill.UpdateFInterID(sConnectionString) == true && bStockBill.UpdateFBillNo(sConnectionString,iBillNo + 1, "WIN+" + iBillNo.ToString().PadLeft(6, '0'), 1) == true)
try
{
retVal = bICStockBill.Add(sConnectionString, mICStockBill);
//bICStockBill.UpdateFInterID(sConnectionString);
}
catch (Exception ex)
{
MessageBoxEx.Show(this, "导入主表数据失败:" + ex.Message, "系统信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (retVal == true)
{
fEntryID = 1;
iSuccessBillCount++;
}
}
}
ICStockBillEntry mICStockBillEntry = new ICStockBillEntry();
//SetPropertyDefaultValue4Detail(mICStockBillEntry);
///内联ID
mICStockBillEntry.FInterID = bICStockBill.GetMaxFInterID(sConnectionString);
//数量
mICStockBillEntry.FQty = decimal.Parse(dt.Rows[i]["基本单位实收数量"].ToString());
//金额
mICStockBillEntry.FAmount = decimal.Parse(dt.Rows[i]["金额"].ToString());
//价格
mICStockBillEntry.FPrice = mICStockBillEntry.FAmount / mICStockBillEntry.FQty;
//仓库ID
mICStockBillEntry.FDCStockID = bICStockBill.GetStockId(sConnectionString, dt.Rows[i]["收料仓库"].ToString());
//批号
mICStockBillEntry.FBatchNo = dt.Rows[i]["批号"].ToString();
//单位编号
mICStockBillEntry.FUnitID = bICStockBillEntry.GetUnitID(sConnectionString, dt.Rows[i]["单位"].ToString());//单位ID
//商品编号
mICStockBillEntry.FItemID = fItemID;
//输入单价
mICStockBillEntry.FAuxPrice = decimal.Parse(dt.Rows[i]["单价"].ToString());
//输入数量
mICStockBillEntry.FAuxQty = decimal.Parse(dt.Rows[i]["实收数量"].ToString());
//条目序号
mICStockBillEntry.FEntryID = fEntryID;
//销售单价
mICStockBillEntry.FConsignPrice = 0;
//销售金额
mICStockBillEntry.FConsignAmount = 0;
///mICStockBillEntry.FEntrySelfB0159 = 0;
///mICStockBillEntry.FEntrySelfB0161 = "";
///mICStockBillEntry.FEntrySelfB0162 = 0;
///mICStockBillEntry.FEntrySelfB0163 = 0;
///mICStockBillEntry.FEntrySelfB0166 = "";
///mICStockBillEntry.FEntrySelfB0167 = "";
///mICStockBillEntry.FEntrySelfB0168 = "";
///mICStockBillEntry.FEntrySelfB0169 = "";
///mICStockBillEntry.FEntrySelfB0170 = 0;
///mICStockBillEntry.FEntrySelfB0172 = 0;
///mICStockBillEntry.FEntrySelfB0171 = "";
///mICStockBillEntry.FEntrySelfB0163 = 0;
///mICStockBillEntry.FEntrySelfB0173 = 0;
///mICStockBillEntry.FEntrySelfB0174 = 0;
///mICStockBillEntry.FEntrySelfB0158 = "";
///mICStockBillEntry.FEntrySelfB0163 = 0;
///mICStockBillEntry.FEntrySelfB0159 = 0;
//写Detail表
try
{
retVal = bICStockBillEntry.Add(sConnectionString, mICStockBillEntry);
}
catch (Exception ex)
{
MessageBoxEx.Show(this, "导入明细表数据失败:" + ex.Message, "系统信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (retVal == true)
{
fEntryID++;
successCount++;
}
}
}
else
{
MessageBox.Show(dt.Rows[i]["商品长代码"].ToString() + "商品编号不存在！");
dataGridView1.Rows[i].Selected = true;
}
}
MessageBox.Show("总共有 " + dt.Rows.Count.ToString() + " 条记录," + "导入失败 " + (dt.Rows.Count - successCount).ToString() + " 条！");
return(retVal);
}
/// <summary>
/// 执行导入
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
private void Excel2DB1(DataTable dt)
{
int successCount = 0; //成功完成导入的个数
string fBillNo = ""; // 主键列
//int fInterID = 0;//内联ID,外键列
int fEntryID = 0; //明细表记录行号
ICStockBillService bICStockBill = new ICStockBillService();
ICStockBillEntryService bICStockBillEntry = new ICStockBillEntryService();
for (int i = 0; i < dt.Rows.Count; i++)
{
ICStockBill mICStockBill = new ICStockBill();
//产品代码
int fItemID = bICStockBillEntry.GetfItemId(sConnectionString, dt.Rows[i]["产品长代码"].ToString());
if (fItemID > 0)
{
if (fBillNo != dt.Rows[i]["单据编号"].ToString())
{
//单据号
mICStockBill.FBillNo = dt.Rows[i]["单据编号"].ToString();
fBillNo = mICStockBill.FBillNo;
//内联编号
bICStockBill.UpdateFInterID(sConnectionString);
mICStockBill.FInterID = bICStockBill.GetMaxFInterID(sConnectionString);
//单据日期
mICStockBill.FDate = DateTime.Parse(dt.Rows[i]["日期"].ToString());
//红蓝字
//mICStockBill.FROB = decimal.Parse(dt.Rows[i]["销售单价"].ToString()) > 0 ? 1 : -1;
mICStockBill.FROB = decimal.Parse(dt.Rows[i]["基本单位实发数量"].ToString()) > 0 ? 1 : -1;//2015
//客户ID
mICStockBill.FSupplyID = bICStockBillEntry.GetSupplyID(sConnectionString, dt.Rows[i]["购货单位代码"].ToString());
//订单号
mICStockBill.FHeadSelfB0146 = dt.Rows[i]["订单号"].ToString();
//进货单号
///mICStockBill.FHeadSelfB0148 = dt.Rows[i]["进货单号A"].ToString();
//验收单号
///mICStockBill.FHeadSelfB0149 = dt.Rows[i]["验收单号A"].ToString();
//订单日期
///mICStockBill.FHeadSelfB0150 = dt.Rows[i]["订单日期A"].ToString();
//交货日期
///mICStockBill.FHeadSelfB0151 = dt.Rows[i]["交货日期A"].ToString();
//审核日期A
///mICStockBill.FHeadSelfB0152 = dt.Rows[i]["审核日期A"].ToString();
//系统
mICStockBill.FTranType = 21;
switch (this.SubCompany.ToLower())
{
case "wuhan":
mICStockBill.FFManagerID = 429;
mICStockBill.FSManagerID = 429;
mICStockBill.FBillerID = 16394;
break;
case "yichang":
mICStockBill.FFManagerID = 4175;
mICStockBill.FSManagerID = 4174;
mICStockBill.FBillerID = 16394; //yachang
break;
case "xiangfan":
mICStockBill.FFManagerID = 4128;
mICStockBill.FSManagerID = 4128;
mICStockBill.FBillerID = 16394; //xiangfan
break;
case "jingzhou":
mICStockBill.FFManagerID = 4395;
mICStockBill.FSManagerID = 4395;
mICStockBill.FBillerID = 16394; //jingzhou
break;
default:
break;
}
try
{
retVal = bICStockBill.Add(sConnectionString, mICStockBill);
}
catch (Exception ex)
{
MessageBoxEx.Show(this, "导入主表数据失败:" + ex.Message, "系统信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (retVal == true)
{
fEntryID = 1;
}
}
}
ICStockBillEntry mICStockBillEntry = new ICStockBillEntry();
///内联ID
mICStockBillEntry.FInterID = bICStockBill.GetMaxFInterID(sConnectionString);
///产品编号
mICStockBillEntry.FItemID = bICStockBillEntry.GetfItemId(sConnectionString, dt.Rows[i]["产品长代码"].ToString());
///实发数量
mICStockBillEntry.FQty = decimal.Parse(dt.Rows[i]["基本单位实发数量"].ToString());
///销售单价
mICStockBillEntry.FPrice = 0;
///销售金额
mICStockBillEntry.FAmount = 0;
///批号
mICStockBillEntry.FBatchNo = dt.Rows[i]["批号"].ToString();
///单位
mICStockBillEntry.FUnitID = bICStockBillEntry.GetUnitID(sConnectionString, dt.Rows[i]["单位"].ToString());
///行号
mICStockBillEntry.FEntryID = fEntryID;
//F件数
///mICStockBillEntry.FEntrySelfB0158 = Decimal.Parse(dt.Rows[i]["F未税价"].ToString());
///含量
///mICStockBillEntry.FEntrySelfB0159 = dt.Rows[i]["F含量"].ToString();
///细数
///mICStockBillEntry.FEntrySelfB0160 = Decimal.Parse(dt.Rows[i]["F细数"].ToString());
//赠品
///mICStockBillEntry.FEntrySelfB0161 = dt.Rows[i]["F赠品"].ToString();
///税率
///mICStockBillEntry.FEntrySelfB0162 = dt.Rows[i]["F税率"].ToString();
///未税金额
///mICStockBillEntry.FEntrySelfB0163 = Decimal.Parse(dt.Rows[i]["F未税金额"].ToString());
///介税合计
///mICStockBillEntry.FEntrySelfB0164 = Decimal.Parse(dt.Rows[i]["F价税合计"].ToString());
///保质日期
///mICStockBillEntry.FEntrySelfB0165 = dt.Rows[i]["F保质日期"].ToString();
///生产日期
///mICStockBillEntry.FEntrySelfB0166 = dt.Rows[i]["F生产日期"].ToString();
///单位
///mICStockBillEntry.FEntrySelfB0167 = dt.Rows[i]["F单位"].ToString();
///规格
///mICStockBillEntry.FEntrySelfB0168 = dt.Rows[i]["F规格"].ToString();
///商品
///mICStockBillEntry.FEntrySelfB0169 = dt.Rows[i]["F商品"].ToString();
///包装
///mICStockBillEntry.FEntrySelfB0170 = dt.Rows[i]["F包装"].ToString();
///箱数
///mICStockBillEntry.FEntrySelfB0171 = Decimal.Parse(dt.Rows[i]["F箱数"].ToString());
///零头
///mICStockBillEntry.FEntrySelfB0172 = dt.Rows[i]["F零头"].ToString();
///件数
///mICStockBillEntry.FEntrySelfB0173 = Decimal.Parse(dt.Rows[i]["F税额"].ToString());
//售价
///mICStockBillEntry.FEntrySelfB0174 = Decimal.Parse(dt.Rows[i]["F售价"].ToString());
//系统
mICStockBillEntry.FBrNo = "0";
//实发单价
mICStockBillEntry.FAuxPrice = 0;
//实发数量
mICStockBillEntry.FAuxQty = decimal.Parse(dt.Rows[i]["实发数量"].ToString());
//销售单价
mICStockBillEntry.FConsignPrice = decimal.Parse(dt.Rows[i]["销售单价"].ToString());
//销售金额
mICStockBillEntry.FConsignAmount = decimal.Parse(dt.Rows[i]["销售金额"].ToString());
//发货仓库
mICStockBillEntry.FDCStockID = bICStockBill.GetStockId(sConnectionString, dt.Rows[i]["发货仓库"].ToString());
//如果销售商编号在列表中也有记录
///Raysoft.Common.Excel2DataTable common = new Excel2DataTable();
///if (common.bIsSpecialCustom(dt.Rows[i]["购货单位代码"].ToString()) == false)
///{
/// //价格取列表中的价格
/// mICStockBillEntry.FPrice = bICStockBillEntry.getSpecialUnitPriceByLongCode(sConnectionString, dt.Rows[i]["产品长代码"].ToString());
/// //金额等于价格X数量
/// mICStockBillEntry.FAmount = mICStockBillEntry.FPrice * mICStockBillEntry.FQty;
///}
///这次交付的版本不包括启用大客户价格
//mICStockBillEntry.FAmount = mICStockBillEntry.FPrice * mICStockBillEntry.FQty;
//写Detail表
try
{
retVal = bICStockBillEntry.Add(sConnectionString, mICStockBillEntry);
}
catch (Exception ex)
{
MessageBoxEx.Show(this, "导入明细表数据失败:" + ex.Message, "系统信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (retVal == true)
{
fEntryID++;
successCount++;
}
}
}
else
{
MessageBox.Show(dt.Rows[i]["产品长代码"].ToString() + "产品编号不存在！");
dataGridView1.Rows[i].Selected = true;
}
}
MessageBox.Show(String.Format("总共有 {0} 条记录,导入失败 {1} 条！", dt.Rows.Count, dt.Rows.Count - successCount));
}