public Dictionary <string, object> ImportTaxBusiness(string filePath, string importOrgCode, string importOrgName, DateTime dateMonth, string userId)
{
Dictionary <string, object> result = new Dictionary <string, object>();
List <ExcelDataRow> rows = new List <ExcelDataRow>();
List <ImportTaxBusiness> list = new List <ImportTaxBusiness>();
try
{
init(filePath);
rows = ExcelConverter.Convert <ImportTaxBusiness>(sheet, HeaderRow, 1);
foreach (var row in rows)
{
list.Add(hardCode(row));//将excel数据转换为List对象
}
result["message"] = db.createTaxBusiness(list, dateMonth, userId, importOrgCode, importOrgName);
if (result["message"].ToString() == "")
{
result["code"] = 2000;
}
else
{
result["code"] = -1;
}
}
catch (Exception e)
{
result["code"] = -1;
result["message"] = e.Message;
}
return(result);
}
public Dictionary <string, object> importdata(string filePath, string orgCode, string orgName, DateTime dateMonth, string userId)
{
Dictionary <string, object> result = new Dictionary <string, object>();
List <ExcelDataRow> rows = new List <ExcelDataRow>();
List <importService> list = new List <importService>();
init(filePath);
rows = ExcelConverter.Convert <importService>(sheet, HeaderRow, 1);
foreach (ExcelDataRow row in rows)
{
list.Add(HardCode(row));
}
try
{
string msg = dB.createImportData(list, orgCode, orgName, dateMonth, userId);
if (!string.IsNullOrEmpty(msg))
{
result["code"] = -1;
result["message"] = msg;
}
else
{
result["code"] = 2000;
result["message"] = "成功";
}
}
catch (Exception e)
{
result["code"] = -1;
result["message"] = e.Message;
}
return(result);
}
public void Convert_DtoWithNoTags_ShouldCreateExcelWith3Sheets()
{
// Arrange
var exportDto = new ExportDto(
new List <ExportTagDto>(),
_usedFilterDto);
// Act
var xlStream = _dut.Convert(exportDto);
// Assert
var workbook = AssertWorkbookFromStream(xlStream, _expected3Sheets);
AssertFiltersSheet(workbook.Worksheets.Worksheet(_filtersSheet), exportDto.UsedFilter);
AssertSheetExists(workbook, _historySheet, false);
}
public Dictionary <string, object> ImportOneTimeBonus(string filePath, string importOrgCode, string importOrgName, DateTime dateMonth, string userId)
{
Dictionary <string, object> r = new Dictionary <string, object>(); //导入结果
List <ExcelDataRow> rows = new List <ExcelDataRow>(); //导入数据转成List集合
List <ImportOneTimeBonus> list = new List <ImportOneTimeBonus>(); //一次性奖金数据集合
// DataTable taxSalary = null;//部门工资表
try
{
Init(filePath);//初始化数据
//taxSalary = taxSalarydb.getTaxSalaryInfo(importOrgCode, dateMonth);
rows = ExcelConverter.Convert <ImportOneTimeBonus>(Sheet, HeaderRow, 1);
foreach (var item in rows)
{
list.Add(HardCode(item));//将excel数据转换为List对象
}
//foreach (var bonus in list)
//{
// DataRow[] taxRows = taxSalary.Select("S_WorkerCode = '" + bonus.S_WorkerCode + "'");
// if (taxRows != null && taxRows.Length == 1)
// {
// bonus.S_OrgName = taxRows[0]["S_OrgName"].ToString();
// bonus.S_OrgCode = taxRows[0]["S_OrgCode"].ToString();
// }
//}
r["message"] = db.createOneTimeBonus(list, dateMonth, userId, importOrgCode, importOrgName);
if (!string.IsNullOrEmpty(r["message"].ToString()))
{
r["code"] = -1;
}
else
{
r["code"] = 2000;
}
}
catch (Exception ex)
{
r["code"] = -1;
r["message"] = ex.Message;
return(r);
}
return(r);
}
static void Main(string[] args)
{
string path = @"D:\VisualStudioProjects\UntitledOfficeConverter\TestFiles\sven.docx";
using (var wrd = new WordConverter())
{
wrd.Convert(path);
}
path = @"D:\VisualStudioProjects\UntitledOfficeConverter\TestFiles\a.pptx";
using (var wrd = new PowerPointConverter())
{
wrd.Convert(path);
}
path = @"D:\VisualStudioProjects\UntitledOfficeConverter\TestFiles\hist.xlsx";
using (var wrd = new ExcelConverter())
{
wrd.Convert(path);
}
}
/// <summary>
/// 验证导入奖金
/// </summary>
/// <param name="filePath">导入文件路径</param>
/// <param name="importOrgCode">导入部门编码</param>
/// <param name="dateMonth">导入月份</param>
/// <returns></returns>
public Dictionary <string, object> ValidateOneTimeBonus(string filePath, string importOrgCode, DateTime dateMonth)
{
Dictionary <string, object> r = new Dictionary <string, object>(); //导入结果
//string validateSalary = "";//检验导入的工资合法性
string validateIdNum = ""; //检验导入的身份证号
string validateBonus = ""; //检验导入的调整合法性
string validateOnce = ""; //检验唯一性
string validateMsg = ""; //验证信息
List <ExcelDataRow> rows = new List <ExcelDataRow>(); //导入数据转成List集合
List <ImportOneTimeBonus> list = new List <ImportOneTimeBonus>(); //一次性奖金数据集合
//DataTable taxSalary = null;//工资表
DataTable onetimeBonus = null; //一次性奖金表
try
{
Init(filePath);//初始化数据
//taxSalary = taxSalarydb.getTaxSalaryInfo(importOrgCode, dateMonth);
//taxSalary = taxSalarydb.getTaxSalaryByYear(dateMonth);
onetimeBonus = db.validateOnetimeTaxByWorkDate(dateMonth);
string msg = validateTemp(HeaderRow);//检验模板是否匹配
if (msg == "")
{
rows = ExcelConverter.Convert <ImportOneTimeBonus>(Sheet, HeaderRow, 1);
foreach (var item in rows)
{
list.Add(HardCode(item));//将excel数据转换为List对象
}
#region 验证是否允许导入
foreach (var bonus in list)
{
//DataRow[] taxRows = taxSalary.Select("S_WorkerCode = '" + bonus.S_WorkerCode + "'");
//if (taxRows == null || taxRows.Length == 0)
//{
// validateBonus += "【" + bonus.S_WorkerCode + ":" + bonus.S_WorkerName + "】";
//}
Regex reg = new Regex(@"^[1-9]\d{5}(18|19|([23]\d))\d{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)\d{3}[0-9Xx]$");
if (bonus.IdTypeName.Contains("身份证"))
{
if (!reg.IsMatch(bonus.IdNumber.ToString()))
{
validateIdNum += "【" + bonus.S_WorkerCode + ":" + bonus.S_WorkerName + "】";
}
}
//else if (taxRows.Length != 1)
//{
// validateSalary += "【" + bonus.S_WorkerCode + ":" + bonus.S_WorkerName + "】";
//}
DataRow[] onceRows = onetimeBonus.Select("S_WorkerCode = '" + bonus.S_WorkerCode + "'");
if (onceRows.Length != 0)
{
validateOnce += "【" + bonus.S_WorkerCode + ":" + bonus.S_WorkerName + "】";
}
}
//if (!string.IsNullOrEmpty(validateBonus))
//{
// validateBonus = "不存在" + validateBonus + "工资信息！";
// validateMsg += validateBonus;
//}
//if (!string.IsNullOrEmpty(validateSalary))
//{
// validateSalary = validateSalary + "工资信息重复！";
// validateMsg += validateSalary;
//}
if (!string.IsNullOrEmpty(validateIdNum))
{
validateIdNum = validateBonus + "身份证号不正确！";
validateMsg += validateIdNum;
}
if (!string.IsNullOrEmpty(validateOnce))
{
validateOnce = validateOnce + "已存在一次性奖金记录，无法重复导入！";
validateMsg += validateOnce;
}
if (!string.IsNullOrEmpty(validateMsg))
{
validateMsg = "全年一次性奖金导入失败，" + validateMsg;
r["code"] = -1;
r["message"] = validateMsg;
return(r);
}
else
{
r["code"] = 2000;
r["message"] = "验证通过";
r["item"] = filePath;
}
#endregion
}
else
{
r["code"] = -1;
r["message"] = msg;
return(r);
}
}
catch (Exception ex)
{
r["code"] = -1;
r["message"] = ex.Message;
}
r["item"] = filePath;
return(r);
}
/// <summary>
/// 验证导入调整
/// </summary>
/// <param name="filePath">导入文件路径</param>
/// <param name="importOrgCode">导入部门编码</param>
/// <param name="dateMonth">导入月份</param>
/// <returns></returns>
public Dictionary <string, object> ValidateTaxAdjust(string filePath, string importOrgCode, DateTime dateMonth)
{
Dictionary <string, object> r = new Dictionary <string, object>(); //导入结果
string validateSalary = ""; //检验导入的工资合法性
string validateAdjust = ""; //检验导入的调整合法性
string validateMsg = ""; //验证信息
string errorMsg = ""; //错误行数信息
List <ExcelDataRow> rows = new List <ExcelDataRow>(); //导入数据转成List集合
List <ImportTaxAdjust> list = new List <ImportTaxAdjust>(); //个税调整数据集合
DataTable taxSalary = null; //部门工资表
try
{
Init(filePath); //初始化数据
taxSalary = taxSalarydb.getTaxSalaryInfo(importOrgCode, dateMonth);
string msg = validateTemp(HeaderRow); //检验模板是否匹配
if (msg == "")
{
rows = ExcelConverter.Convert <ImportTaxAdjust>(Sheet, HeaderRow, 1);
foreach (var item in rows)
{
list.Add(HardCode(item));//将excel数据转换为List对象
errorMsg = "第" + (list.Count() + 1) + "条数据有误,转换失败！";
}
errorMsg = "";
#region 验证是否允许导入
foreach (var adjust in list)
{
if (!string.IsNullOrEmpty(adjust.S_WorkerCode))
{
DataRow[] taxRows = taxSalary.Select("S_WorkerCode = '" + adjust.S_WorkerCode + "'");
if (taxRows == null || taxRows.Length == 0)
{
validateAdjust += "【" + adjust.S_WorkerCode + ":" + adjust.S_WorkerName + "】";
}
else if (taxRows.Length != 1)
{
validateSalary += "【" + adjust.S_WorkerCode + ":" + adjust.S_WorkerName + "】";
}
}
else
{
r["code"] = -1;
r["message"] = adjust.S_WorkerName + "工号不能为空！";
return(r);
}
}
if (!string.IsNullOrEmpty(validateAdjust))
{
validateAdjust = "不存在" + validateAdjust + "工资信息！";
validateMsg += validateAdjust;
}
if (!string.IsNullOrEmpty(validateSalary))
{
validateSalary = validateSalary + "工资信息重复！";
validateMsg += validateSalary;
}
if (!string.IsNullOrEmpty(validateMsg))
{
validateMsg = "调整导入失败，" + dateMonth.ToString("yyyy-MM") + "，" + validateMsg;
r["code"] = -1;
r["message"] = validateMsg;
return(r);
}
else
{
r["code"] = 2000;
r["message"] = "验证通过";
r["item"] = filePath;
}
#endregion
}
else
{
r["code"] = -1;
r["message"] = msg;
return(r);
}
}
catch (Exception ex)
{
r["code"] = -1;
r["message"] = ex.Message + errorMsg;
}
r["item"] = filePath;
return(r);
}
public Dictionary <string, object> validateExcel(string filePath, string importOrgCode, string importOrgName, DateTime dateMonth)
{
Dictionary <string, object> result = new Dictionary <string, object>();
string workcoderepeat = ""; //验证导入excel内是否有重复人员
string Idrepeat = ""; //验证导入excel内是否有重复身份账号
List <ExcelDataRow> rows = new List <ExcelDataRow>();
List <ImportTaxBusiness> list = new List <ImportTaxBusiness>();
string hardCodeMsg = "";
try
{
init(filePath);
hardCodeMsg = validateHeader(HeaderRow);
if (!string.IsNullOrEmpty(hardCodeMsg))
{
result["code"] = -1;
result["message"] = hardCodeMsg;
return(result);
}
rows = ExcelConverter.Convert <ImportTaxBusiness>(sheet, HeaderRow, 1);
foreach (var row in rows)
{
list.Add(hardCode(row));
hardCodeMsg = "第" + list.Count() + 1 + "条数据错误!";
}
var workcode = list.GroupBy(t => t.WorkNumber).Where(t => t.Count() > 1).ToList();
foreach (var item in workcode)
{
workcoderepeat += "【" + item.Key + "】";
}
if (!string.IsNullOrEmpty(workcoderepeat))
{
workcoderepeat = "员工编号：" + workcoderepeat + "金税人员信息重复，请确认导入信息";
result["code"] = -1;
result["message"] = "导入失败！" + workcoderepeat;
return(result);
}
var idNumber = list.GroupBy(t => t.IDNumber).Where(t => t.Count() > 1).ToList();
foreach (var item in idNumber)
{
Idrepeat += "【" + item.Key + "】";
}
if (!string.IsNullOrEmpty(Idrepeat))
{
Idrepeat = "员工身份证号：" + Idrepeat + "金税人员信息重复，请确认导入信息";
result["code"] = -1;
result["message"] = "导入失败！" + Idrepeat;
return(result);
}
//验证数据库中本月是否存在重复人员的金税信息
//DataTable dt = db.judgeTaxInfo( dateMonth);
//foreach (var item in list)
//{
// DataRow[] dr = dt.Select("S_WorkNumber='" + item.WorkNumber + "'");
// if (dr.Count() > 0)
// {
// result["code"] = -1;
// result["message"] = "金税人员重复，请检查工号为" + dr[0]["S_WorkNumber"].ToString() + "姓名为" + dr[0]["S_WorkName"].ToString() + "的员工信息";
// return result;
// }
// DataRow[] du = dt.Select("IDNumber='" + item.IDNumber + "'");
// if (du.Count() > 0)
// {
// result["code"] = -1;
// result["message"] = "金税人员重复，请检查身份证号为" + du[0]["IDNumber"].ToString() + "姓名为" + du[0]["S_WorkName"].ToString() + "的员工信息";
// return result;
// }
//}
result["code"] = 2000;
result["item"] = filePath;
}
catch (Exception e)
{
result["code"] = -1;
result["message"] = e.Message + hardCodeMsg;
}
return(result);
}
public Dictionary <string, object> validate(string filepath, string orgCode, string orgName, DateTime dateMonth)
{
Dictionary <string, object> result = new Dictionary <string, object>();
string errorMsg = "";
string coderepeat = "";
string idrepeat = "";
string headerMsg = "";
List <ExcelDataRow> rows = new List <ExcelDataRow>();
List <importService> list = new List <importService>();
try
{
init(filepath);
headerMsg = validateHeader(HeaderRow);
if (!string.IsNullOrEmpty(headerMsg))
{
result["code"] = -1;
result["message"] = headerMsg;
return(result);
}
rows = ExcelConverter.Convert <importService>(sheet, HeaderRow, 1);
foreach (ExcelDataRow row in rows)
{
list.Add(HardCode(row));
errorMsg = "第" + (list.Count() + 1) + "条数据有误!";
}
errorMsg = "";
#region 验证必填项是否为空
foreach (importService item in list)
{
if (item.IDtype == "" || item.IDNumber == "" || item.IncomeItem == "" || item.Income == "")
{
errorMsg += "工号为【" + item.WorkerCode + "】姓名为【" + item.WorkerName + "】的员工必填项为空,请仔细检查数据表!";
}
}
if (!string.IsNullOrEmpty(errorMsg))
{
result["code"] = -1;
result["message"] = errorMsg;
return(result);
}
errorMsg = "";
#endregion
//#region 工号验证重复
//var workcode = list.GroupBy(t => t.WorkerCode).Where(t => t.Count() > 1).ToList();
//foreach (var item in workcode)
//{
// coderepeat = "【" + item.Key + "】";
//}
//if (!string.IsNullOrEmpty(coderepeat))
//{
// result["code"] = -1;
// result["message"] = "工号" + coderepeat + "重复！";
// return result;
//}
//#endregion
#region 身份证规则验证
Regex reg = new Regex(@"^[1-9]\d{5}(18|19|([23]\d))\d{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)\d{3}[0-9Xx]$");
foreach (var item in list)
{
if (item.IDtype.Contains("居民身份证"))
{
if (!reg.IsMatch(item.IDNumber))
{
idrepeat += "姓名【" + item.WorkerName + "】" + "身份证号为【" + item.IDNumber + "】身份证格式不正确!";
}
}
}
if (!string.IsNullOrEmpty(idrepeat))
{
result["code"] = -1;
result["message"] = idrepeat;
return(result);
}
#endregion
}
catch (Exception e)
{
result["code"] = -1;
result["message"] = e.Message + errorMsg;
}
result["code"] = 2000;
result["message"] = "成功";
result["item"] = filepath;
return(result);
}
public Dictionary <string, object> ImportTaxSalary(string filePath, string userId, string importModel, string taxOffice)
{
Dictionary <string, object> r = new Dictionary <string, object>(); //导入结果
List <ExcelDataRow> rows = new List <ExcelDataRow>(); //导入数据转成List集合
List <ImportTaxSalary1> list1 = new List <ImportTaxSalary1>(); //模板一数据集合
List <ImportTaxSalary2> list2 = new List <ImportTaxSalary2>(); //模板二数据集合
try
{
Init(filePath);//初始化数据
if (importModel == "样表一")
{
rows = ExcelConverter.Convert <ImportTaxSalary1>(Sheet, HeaderRow, 1);
foreach (var item in rows)
{
list1.Add(HardCode1(item));//将excel数据转换为List对象
}
string guid = Guid.NewGuid().ToString();
string result = db.createTaxSalary1(list1, userId, guid);
if (result == "")
{
//调用（全年平均核算）计算存储过程
string salary = db.SalaryTaxCalculator(guid, taxOffice);
string[] arr = salary.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
r["YeaTax"] = arr[0].ToString();
r["MonthTax"] = arr[1].ToString();
r["message"] = "成功";
r["code"] = 2000;
}
else
{
r["YeaTax"] = "";
r["MonthTax"] = "";
r["message"] = "成功";
r["code"] = 2000;
}
}
else if (importModel == "样表二")
{
rows = ExcelConverter.Convert <ImportTaxSalary2>(Sheet, HeaderRow, 1);
foreach (var item in rows)
{
list2.Add(HardCode2(item));//将excel数据转换为List对象
}
string guid = Guid.NewGuid().ToString();
string result = db.createTaxSalary2(list2, userId, guid);
if (result == "")
{
//调用（全年平均核算）计算存储过程
string salary = db.SalaryTaxCalculator(guid, taxOffice);
string[] arr = salary.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
r["YeaTax"] = arr[0].ToString();
r["MonthTax"] = arr[1].ToString();
r["message"] = "成功";
r["code"] = 2000;
}
else
{
r["YeaTax"] = "";
r["MonthTax"] = "";
r["message"] = "成功";
r["code"] = 2000;
}
}
}
catch (Exception ex)
{
r["SalaryTax"] = 0;
r["code"] = -1;
r["message"] = ex.Message;
return(r);
}
return(r);
}