/// <summary>
/// 供应商客服提取报增信息
/// </summary>
/// <param name="getParam"></param>
/// <returns></returns>
public Common.ClientResult.UrlResult SupplierExport([FromBody] GetDataParam getParam)
{
FileStream file = new FileStream(System.Web.HttpContext.Current.Server.MapPath("../../Template/Excel/供应商客服报增数据提取模板.xls"), FileMode.Open, FileAccess.Read);
HSSFWorkbook workbook = new HSSFWorkbook(file);
//ICellStyle cellStyle = workbook.CreateCellStyle();
//cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
//cellStyle.FillPattern = FillPattern.SolidForeground;
ICellStyle style = workbook.CreateCellStyle();
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
style.FillPattern = FillPattern.SolidForeground;
try
{
string search = getParam.search;
int total = 0;
Dictionary <string, string> queryDic = ValueConvert.StringToDictionary(search.GetString());
if (queryDic != null && queryDic.Count > 0)
{
if (queryDic.ContainsKey("CollectState") && !string.IsNullOrWhiteSpace(queryDic["CollectState"]))
{
string str = queryDic["CollectState"];
if (str.Equals(Common.CollectState.未提取.ToString()))
{
string state = Common.EmployeeAdd_State.待供应商客服提取.ToString();
search += "State&" + state + "^";
}
else if (str.Equals(Common.CollectState.已提取.ToString()))
{
string state = Common.EmployeeAdd_State.已发送供应商.ToString();
search += "State&" + state + "^";
}
}
}
search += "UserID_Supplier&" + LoginInfo.UserID + "^";
//search += "BranchID&" + LoginInfo.BranchID + "^";
//search += "BranchName&" + LoginInfo.BranchName + "^";
List <SupplierAddView> queryData = m_BLL.GetEmployeeAddExcelListBySupplier(search).OrderBy(c => c.CertificateNumber).ThenBy(c => c.CityID).ThenBy(c => c.OperationTime).ToList();
if (queryData.Count == 0)
{
var data = new Common.ClientResult.UrlResult
{
Code = ClientCode.FindNull,
Message = "没有符合条件的数据",
URL = ""
};
return(data);
}
using (MemoryStream ms = new MemoryStream())
{
#region 生成Excel
string ids = string.Empty;
int[] intArray = new int[queryData.Count];
//员工社保一览
ISheet sheet = workbook.GetSheetAt(0);
sheet.SetActiveCell(0, 0);
int rowNum = 1;
string tmpCertificateNumber = "";
string tmpCity = "";
string tmpYearMonth = "";
string strRemark = "";
IRow currentRow = null;
#region 数据输出
int i = 0;
foreach (var query in queryData)
{
intArray[i] = query.EmployeeAddId ?? 0;
i++;
if (tmpCertificateNumber != query.CertificateNumber || tmpCity != query.City ||
(tmpYearMonth != Convert.ToDateTime(query.OperationTime).ToString("yyyyMM") && query.InsuranceKindId != (int)Common.EmployeeAdd_InsuranceKindId.公积金))
{
//保存临时数据
tmpCertificateNumber = query.CertificateNumber;
tmpCity = query.City;
tmpYearMonth = Convert.ToDateTime(query.OperationTime).ToString("yyyyMM");
if (currentRow != null)
{
currentRow.GetCell(19).SetCellValue(strRemark); // 备注
strRemark = "";
}
rowNum++;
currentRow = sheet.CopyRow(rowNum + 1, rowNum);
currentRow.GetCell(0).SetCellValue(rowNum - 1); // 序号
currentRow.GetCell(1).SetCellValue(query.CustomerName); // 客服姓名
currentRow.GetCell(2).SetCellValue(query.City); // 缴费地
currentRow.GetCell(3).SetCellValue(query.BranchName); // 分公司名称
currentRow.GetCell(4).SetCellValue(query.CompanyName); // 客户单位名称
currentRow.GetCell(5).SetCellValue(query.EmployeeName); // 员工姓名
currentRow.GetCell(6).SetCellValue(query.CertificateNumber); // 身份证号码
currentRow.GetCell(7).SetCellValue(query.Telephone); // 联系电话
if (query.InsuranceKindId != (int)Common.EmployeeAdd_InsuranceKindId.公积金)
{
currentRow.GetCell(8).SetCellValue(tmpYearMonth); // 缴费开始时间
}
}
int cellNum = 0;
switch (Convert.ToInt32(query.InsuranceKindId))
{
case (int)Common.EmployeeAdd_InsuranceKindId.公积金:
currentRow.GetCell(15).SetCellValue(Convert.ToDateTime(query.OperationTime).ToString("yyyyMM")); // 公积金缴费开始时间
currentRow.GetCell(17).SetCellValue((query.EmployeePercent * 100).ToString() + "%"); // 公积金比例（个人）
currentRow.GetCell(18).SetCellValue((query.CompanyPercent * 100).ToString() + "%"); // 公积金比例（单位）
cellNum = 16;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.养老:
cellNum = 9;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.医疗:
cellNum = 10;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.失业:
cellNum = 11;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.工伤:
cellNum = 12;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.生育:
cellNum = 13;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.大病:
cellNum = 14;
break;
default:
break;
}
currentRow.GetCell(cellNum).SetCellValue(query.CompanyNumber.ToString()); // 基数
if (query.State == "1")
{
currentRow.GetCell(cellNum).CellStyle = style;
}
if (!string.IsNullOrEmpty(query.SupplierRemark))
{
strRemark += query.SupplierRemark + " ";
}
}
if (!string.IsNullOrEmpty(strRemark))
{
currentRow.GetCell(19).SetCellValue(strRemark); // 备注
}
#endregion
#endregion
bool rst = m_BLL.ChangeStatus(intArray, Common.EmployeeAdd_State.待供应商客服提取.ToString(), EmployeeAdd_State.已发送供应商.ToString(), LoginInfo.LoginName);
if (rst)
{
string fileName = "供应商客服导出_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
//string fileName = name + "供应商导出.xls";
string urlPath = "DataExport\\" + fileName; // 文件下载的URL地址，供给前台下载
string filePath = System.Web.HttpContext.Current.Server.MapPath("\\") + urlPath; // 文件路径
file = new FileStream(filePath, FileMode.Create);
workbook.Write(file);
file.Close();
string Message = "已成功提取报增信息";
return(new Common.ClientResult.UrlResult
{
Code = ClientCode.Succeed,
Message = "已成功提取报增信息",
URL = urlPath
});
}
else
{
return(new Common.ClientResult.UrlResult
{
Code = ClientCode.Succeed,
Message = "报增信息提取失败",
URL = ""
});
}
}
}
//catch (Exception e)
//{
// file.Close();
// return new Common.ClientResult.UrlResult
// {
// Code = ClientCode.Fail,
// Message = e.Message
// };
//}
catch (DbEntityValidationException ex)
{
string errmsg = "";
foreach (var errors in ex.EntityValidationErrors)
{
foreach (var item in errors.ValidationErrors)
{
errmsg += item.ErrorMessage + item.PropertyName;
}
}
return(null);
}
}
/// <summary>
/// 供应商客服提取报减信息
/// </summary>
/// <param name="getParam"></param>
/// <returns></returns>
public Common.ClientResult.UrlResult SupplierExport([FromBody] GetDataParam getParam)
{
FileStream file = new FileStream(System.Web.HttpContext.Current.Server.MapPath("../../Template/Excel/供应商客服报减数据提取模版.xls"), FileMode.Open, FileAccess.Read);
HSSFWorkbook workbook = new HSSFWorkbook(file);
try
{
string search = getParam.search;
int getid = 0;
int total = 0;
Dictionary <string, string> queryDic = ValueConvert.StringToDictionary(search.GetString());
if (queryDic != null && queryDic.Count > 0)
{
if (queryDic.ContainsKey("CollectState") && !string.IsNullOrWhiteSpace(queryDic["CollectState"]))
{
string str = queryDic["CollectState"];
if (str.Contains(Common.CollectState.未提取.ToString()))
{
string state = Common.EmployeeAdd_State.待供应商客服提取.ToString();
search += "State&" + state + "^";
}
else
{
string state = Common.EmployeeAdd_State.已发送供应商.ToString();
search += "State&" + state + "^";
}
}
}
search += "UserID_Supplier&" + LoginInfo.UserID + "^";
List <SupplierAddView> queryData = m_BLL.GetEmployeeStopExcelListBySupplier(search).OrderBy(c => c.CertificateNumber).ThenBy(c => c.CityID).ThenBy(c => c.OperationTime).ToList();
if (queryData.Count == 0)
{
var data = new Common.ClientResult.UrlResult
{
Code = ClientCode.FindNull,
Message = "没有符合条件的数据",
URL = ""
};
return(data);
}
//string excelName = "供应商导出" + DateTime.Now.ToString();
using (MemoryStream ms = new MemoryStream())
{
string ids = string.Empty;
int[] intArray = new int[queryData.Count];
//员工社保一览
ISheet sheet = workbook.GetSheetAt(0);
sheet.SetActiveCell(0, 0);
int rowNum = 0;
ICellStyle style = workbook.CreateCellStyle();
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
style.FillPattern = FillPattern.SolidForeground;
string tmpCertificateNumber = "";
string tmpCity = "";
string tmpYearMonth = "";
string strRemark = "";
IRow currentRow = null;
#region 数据输出
int i = 0;
foreach (var query in queryData)
{
intArray[i] = query.EmployeeAddId ?? 0;
i++;
if (tmpCertificateNumber != query.CertificateNumber || tmpCity != query.City ||
(tmpYearMonth != Convert.ToDateTime(query.OperationTime).ToString("yyyyMM") && query.InsuranceKindId != (int)Common.EmployeeAdd_InsuranceKindId.公积金))
{
//保存临时数据
tmpCertificateNumber = query.CertificateNumber;
tmpCity = query.City;
tmpYearMonth = Convert.ToDateTime(query.OperationTime).ToString("yyyyMM");
if (currentRow != null)
{
currentRow.GetCell(10).SetCellValue(strRemark); // 备注
strRemark = "";
}
rowNum++;
currentRow = sheet.CopyRow(rowNum + 1, rowNum);
currentRow.GetCell(0).SetCellValue(rowNum); // 序号
currentRow.GetCell(1).SetCellValue(query.CustomerName); // 客服姓名
currentRow.GetCell(2).SetCellValue(query.City); // 缴费地
currentRow.GetCell(3).SetCellValue(query.BranchName); // 分公司名称
currentRow.GetCell(4).SetCellValue(query.CompanyName); // 客户单位名称
currentRow.GetCell(5).SetCellValue(query.EmployeeName); // 员工姓名
currentRow.GetCell(6).SetCellValue(query.CertificateNumber); // 身份证号码
currentRow.GetCell(7).SetCellValue(query.Telephone); // 联系电话
if (query.InsuranceKindId != (int)Common.EmployeeAdd_InsuranceKindId.公积金)
{
currentRow.GetCell(8).SetCellValue(tmpYearMonth); // 缴费开始时间
}
else
{
currentRow.GetCell(9).SetCellValue(tmpYearMonth); // 缴费开始时间
}
}
int cellNum = 0;
switch (Convert.ToInt32(query.InsuranceKindId))
{
case (int)Common.EmployeeAdd_InsuranceKindId.公积金:
cellNum = 9;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.养老:
cellNum = 8;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.医疗:
cellNum = 8;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.失业:
cellNum = 8;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.工伤:
cellNum = 8;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.生育:
cellNum = 8;
break;
case (int)Common.EmployeeAdd_InsuranceKindId.大病:
cellNum = 8;
break;
default:
break;
}
if (query.State == "1")
{
currentRow.GetCell(cellNum).CellStyle = style;
}
if (!string.IsNullOrEmpty(query.Remark))
{
strRemark += query.Remark + Convert.ToChar(10);
}
ids += query.EmployeeAddId + ",";
}
#endregion
var results = 0;//返回的结果
if (queryDic["CollectState"].Equals(Common.CollectState.未提取.ToString()))
{
int[] ApprovedId;
string[] strArray = ids.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
ApprovedId = Array.ConvertAll <string, int>(strArray, s => int.Parse(s));
string returnValue = string.Empty;
if (ApprovedId != null && ApprovedId.Length > 0)
{
if (m_BLL.EmployeeStopPaymentApproved(ref validationErrors, ApprovedId, Common.EmployeeStopPayment_State.待供应商客服提取.ToString(), Common.EmployeeStopPayment_State.已发送供应商.ToString(), null, LoginInfo.LoginName))
{
results = ApprovedId.Count();
LogClassModels.WriteServiceLog("供应商客服已提取" + "，信息的Id为" + string.Join(",", ApprovedId), "消息"
);//回退成功，写入日志
}
else
{
if (validationErrors != null && validationErrors.Count > 0)
{
validationErrors.All(a =>
{
returnValue += a.ErrorMessage;
return(true);
});
}
LogClassModels.WriteServiceLog("供应商客服提取失败" + "，信息的Id为" + string.Join(",", ApprovedId) + "," + returnValue, "消息"
);//回退失败，写入日志
}
}
}
string fileName = "供应商客服导出_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
string urlPath = "DataExport\\" + fileName; // 文件下载的URL地址，供给前台下载
string filePath = System.Web.HttpContext.Current.Server.MapPath("\\") + urlPath; // 文件路径
file = new FileStream(filePath, FileMode.Create);
workbook.Write(file);
file.Close();
string Message = queryDic["CollectState"].Equals(Common.CollectState.未提取.ToString()) ? "已成功提取报减信息" : "导出成功";
if (queryData.Count == 0)
{
//ActionResult result =
var data = new Common.ClientResult.UrlResult
{
Code = ClientCode.FindNull,
Message = "没有符合条件的数据",
URL = urlPath
};
return(data);
}
return(new Common.ClientResult.UrlResult
{
Code = ClientCode.Succeed,
Message = Message,
URL = urlPath
});
}
}
catch (Exception e)
{
file.Close();
return(new Common.ClientResult.UrlResult
{
Code = ClientCode.Fail,
Message = e.Message
});
}
}
