private void ImportAccvochToU8(Accvouch accvouch, List <string> infos)
{
Dictionary <string, string> values = new Dictionary <string, string>();
values.Add("iperiod", accvouch.iperiod);
values.Add("csign", accvouch.csign);
values.Add("isignseq", accvouch.isignseq);
values.Add("ino_id", accvouch.ino_id.ToString());
values.Add("inid", accvouch.inid.ToString());
values.Add("dbill_date", accvouch.dbill_date.ToString());
values.Add("idoc", accvouch.idoc.ToString());
values.Add("ibook", accvouch.ibook);
values.Add("cdigest", accvouch.cdigest);
values.Add("ccode", accvouch.ccode);
values.Add("md", accvouch.md.ToString());
values.Add("mc", accvouch.mc.ToString());
values.Add("md_f", accvouch.md_f.ToString());
values.Add("mc_f", accvouch.mc_f.ToString());
values.Add("nfrat", accvouch.nfrat.ToString());
values.Add("nd_s", accvouch.nd_s.ToString());
values.Add("nc_s", accvouch.nc_s.ToString());
if (accvouch.csettle != null && accvouch.csettle.ToString().Length > 0)
{
values.Add("csettle", accvouch.csettle);
}
if (accvouch.cdept_id != null && accvouch.cdept_id.Length > 0)
{
values.Add("cdept_id", accvouch.cdept_id);
}
if (accvouch.csup_id != null && accvouch.csup_id.Length > 0)
{
values.Add("csup_id", accvouch.csup_id);
}
values.Add("ccode_equal", accvouch.ccode_equal);
values.Add("iyear", accvouch.iyear);
values.Add("iYPeriod", accvouch.iYPeriod);
if (accvouch.citem_id != null && accvouch.citem_id.Length > 0)
{
values.Add("citem_id", accvouch.citem_id);
}
if (accvouch.citem_class != null && accvouch.citem_class.Length > 0)
{
values.Add("citem_class", accvouch.citem_class);
}
values.Add("cbill", accvouch.cbill);
if (accvouch.cname != null && accvouch.cname.Length > 0)
{
values.Add("cname", accvouch.cname);
}
if (accvouch.ccus_id != null && accvouch.ccus_id.Length > 0)
{
values.Add("ccus_id", accvouch.ccus_id);
}
if (accvouch.cperson_id != null && accvouch.cperson_id.Length > 0)
{
values.Add("cperson_id", accvouch.cperson_id);
}
values.Add("cDefine1", accvouch.cDefine1);
SqlHelper.Insert(LoginSettingInfo.SqlConnectionString, "GL_accvouch", values);
}
/// <summary>
/// 导入凭证
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public void ImportAccvouchByExcel(string filePath, List <string> infos)
{
try
{
DataSet ds = ExcelHelper.QueryByODBC(filePath, "Sheet1");
if (ds.Tables.Count > 0)
{
DataTable dt = ds.Tables[0];
string curId = string.Empty;
for (int i = 0; i < dt.Rows.Count; ++i)
{
if (dt.Rows[i]["凭证ID"].ToString() != curId)
{
curId = dt.Rows[i]["凭证ID"].ToString();
if (Accvouch.Find(curId))
{
infos.Add("凭证ID：" + curId + "导入失败！错误提示：该凭证ID已导入");
continue;
}
//判断ERP帐套号与U8账号是否对应
if (!AccountArchives.FindByVesselCode(dt.Rows[i]["ERP帐套号"].ToString()))
{
infos.Add("凭证ID：" + curId + "导入失败! " + "错误提示：对应的ERP帐套号与U8帐套号不匹配");
continue;
}
ImportAccvoch(dt.Select("凭证ID=" + "'" + curId + "'"), filePath, infos);
}
}
}
else
{
infos.Add("读取Excel文件失败,请检查该文件是否已打开或表Sheet1是否存在!");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + ",凭证导入失败!");
infos.Add(ex.Message);
}
}
private void ImportAccvoch(DataRow[] dataRow, string filePath, List <string> infos)
{
string id = string.Empty;
try
{
int ino_id = 0;
Dictionary <string, string> keys = new Dictionary <string, string>();
keys.Add("csign", dataRow[0]["凭证类别"].ToString());
keys.Add("ibook", "0");
DataSet ds = SqlHelper.Query(LoginSettingInfo.SqlConnectionString, "GL_accvouch", "ino_id", keys, "ino_id");
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
ino_id = 1;
}
else
{
DataTable dt = ds.Tables[0];
ino_id = int.Parse(dt.Rows[dt.Rows.Count - 1]["ino_id"].ToString()) + 1;
}
int count = dataRow.Length;
for (int i = 0; i < count; ++i)
{
Accvouch accvouch = new Accvouch();
accvouch.ino_id = ino_id;
accvouch.cDefine1 = dataRow[i]["凭证ID"].ToString();
id = accvouch.cDefine1;
accvouch.csign = dataRow[i]["凭证类别"].ToString();
accvouch.cdigest = dataRow[i]["摘要"].ToString();
accvouch.ccode = CodeArchives.FindByCode(dataRow[i]["科目编码"].ToString());
if (dataRow[i]["借方金额"] != null && dataRow[i]["借方金额"].ToString().Length > 0)
{
accvouch.md = double.Parse(dataRow[i]["借方金额"].ToString());
}
if (dataRow[i]["贷方金额"] != null && dataRow[i]["贷方金额"].ToString().Length > 0)
{
accvouch.mc = double.Parse(dataRow[i]["贷方金额"].ToString());
}
if (dataRow[i]["所附单据数"] != null && dataRow[i]["所附单据数"].ToString().Length > 0)
{
accvouch.idoc = int.Parse(dataRow[i]["所附单据数"].ToString());
}
if (dataRow[i]["结算方式编码"] != null && dataRow[i]["结算方式编码"].ToString().Length > 0)
{
accvouch.csettle = dataRow[i]["结算方式编码"].ToString();
}
if (dataRow[i]["部门编码"] != null && dataRow[i]["部门编码"].ToString().Length > 0)
{
accvouch.cdept_id = DepartmentArchives.FindByDepartmentCode(dataRow[i]["部门编码"].ToString());
}
if (dataRow[i]["职员编码"] != null && dataRow[i]["职员编码"].ToString().Length > 0)
{
accvouch.cperson_id = dataRow[i]["职员编码"].ToString();
}
if (dataRow[i]["客户编码"] != null && dataRow[i]["客户编码"].ToString().Length > 0)
{
accvouch.ccus_id = CustomerArchives.FindByCustomerCode(dataRow[i]["客户编码"].ToString());
}
if (dataRow[i]["供应商编码"] != null && dataRow[i]["供应商编码"].ToString().Length > 0)
{
accvouch.csup_id = VendorArchives.FindByVendorCode(dataRow[i]["供应商编码"].ToString());
}
if (dataRow[i]["项目大类编码"] != null && dataRow[i]["项目大类编码"].ToString().Length > 0)
{
accvouch.citem_class = dataRow[i]["项目大类编码"].ToString();
}
if (dataRow[i]["项目编码"] != null && dataRow[i]["项目编码"].ToString().Length > 0)
{
accvouch.citem_id = dataRow[i]["项目编码"].ToString();
}
if (dataRow[i]["业务员"] != null && dataRow[i]["业务员"].ToString().Length > 0)
{
accvouch.cname = dataRow[i]["业务员"].ToString();
}
accvouch.dbill_date = DateTime.Parse(LoginInfo.LoginDate.ToShortDateString());
accvouch.cbill = LoginInfo.UserName;
accvouch.inid = i + 1;
ImportAccvochToU8(accvouch, infos);
}
}
catch (Exception ex)
{
infos.Add("凭证ID：" + id + "导入失败!" + "错误提示：" + ex.Message);
Dictionary <string, string> wheres = new Dictionary <string, string>();
wheres.Add("cDefine1", id);
SqlHelper.Delete(LoginSettingInfo.SqlConnectionString, "GL_accvouch", wheres);
Dictionary <string, string> values = new Dictionary <string, string>();
values.Add("备注", ex.Message);
wheres.Clear();
wheres.Add("凭证ID", id);
ExcelHelper.UpdateByODBC(filePath, "Sheet1", values, wheres);
}
}
