1485 lines
65 KiB
C#
1485 lines
65 KiB
C#
using BaseOUDAL;
|
|
using NPOI.SS.UserModel;
|
|
using NPOI.SS.Util;
|
|
using NPOI.XSSF.UserModel;
|
|
using System.Data;
|
|
using System.Text;
|
|
using YLErp.BLL;
|
|
using YLErp.Commons;
|
|
using YLErp.Helpers;
|
|
using YLErp.Model.Enum;
|
|
using YLErp.Models;
|
|
using YLErp.Modules.ClientModule.Models;
|
|
using YLErp.Modules.SystemModule;
|
|
|
|
namespace YLErp.Modules.ClientModule
|
|
{
|
|
public class ClientTemplateImportService : ClientBaseService
|
|
{
|
|
public ClientTemplateImportService(OptUserInfo userInfo) : base(userInfo)
|
|
{
|
|
}
|
|
|
|
|
|
public List<FormEditField> GetAllFormEditFieldToList()
|
|
{
|
|
var clientFields = ClientEditConfigService.GetFlatList(ClientEditConfigService.GetAllFormEditField(), out var col);
|
|
return clientFields.Distinct().Where(a => a != null && !string.IsNullOrEmpty(a.label) && !a.@readonly).ToList();
|
|
}
|
|
|
|
public string[] GetSelectData(Dictionary<string, IEnumerable<SelectItem>> selectDatas, string name)
|
|
{
|
|
return selectDatas.Where(a => a.Key == name).SelectMany(a => a.Value).Select(a => a.Text).ToArray();
|
|
}
|
|
|
|
/// <summary>
|
|
/// 下载客户基本信息模板
|
|
/// </summary>
|
|
public void UpdateClientInfoTemplate()
|
|
{
|
|
var dicPath = OtcAppContext.MapPath(@"\App_Docs\导入模板");
|
|
Directory.CreateDirectory(dicPath);
|
|
var xlsxFilePath = Path.Combine(dicPath, "客户导入_基本信息模板.xlsx");
|
|
if (!File.Exists(xlsxFilePath))
|
|
{
|
|
//var clientFields = GetAllFormEditFieldToList();
|
|
var config = ClientEditConfigService.GetConfig(true);
|
|
var sections = config.sections.Where(a => a.visible && a.fields.Any()).ToList();
|
|
var bytes = ListToExcel(sections, new List<FormEditField>(), config.selects);
|
|
|
|
File.WriteAllBytes(xlsxFilePath, bytes);
|
|
}
|
|
}
|
|
public byte[] ListToExcel(List<FormEditSection> formEditSections, List<FormEditField> fields, Dictionary<string, IEnumerable<SelectItem>> selectDatas)
|
|
{
|
|
var ms = new ExcelHelper.CMemoryStream
|
|
{
|
|
AllowClose = false
|
|
};
|
|
IWorkbook wb = new XSSFWorkbook();
|
|
try
|
|
{
|
|
//创建表
|
|
var sheet = wb.CreateSheet("客户基本信息");
|
|
|
|
//创建字体样式
|
|
var font = wb.CreateFont();
|
|
font.IsBold = true;
|
|
font.FontHeightInPoints = 10;
|
|
//背景颜色
|
|
short[] colourArr = { NPOI.HSSF.Util.HSSFColor.LightOrange.Index, NPOI.HSSF.Util.HSSFColor.LightBlue.Index, NPOI.HSSF.Util.HSSFColor.LightGreen.Index, NPOI.HSSF.Util.HSSFColor.LightTurquoise.Index };
|
|
var startIndex = 0;
|
|
var endIndex = 0;
|
|
var rowHeader = sheet.CreateRow(0);
|
|
for (var j = 0; j < formEditSections.Count; j++)
|
|
{
|
|
//创建标题样式
|
|
var styleHeader = wb.CreateCellStyle();
|
|
styleHeader.SetFont(font);
|
|
styleHeader.Alignment = HorizontalAlignment.Center;
|
|
styleHeader.FillPattern = FillPattern.SolidForeground;
|
|
//styleHeader.FillBackgroundColor = colourArr[j];
|
|
styleHeader.FillForegroundColor = colourArr[j % colourArr.Length];
|
|
//边框
|
|
styleHeader.BorderBottom = BorderStyle.Thin;
|
|
styleHeader.BorderLeft = BorderStyle.Thin;
|
|
styleHeader.BorderRight = BorderStyle.Thin;
|
|
styleHeader.BorderTop = BorderStyle.Thin;
|
|
|
|
var formEditFields = (formEditSections[j].fields ?? Enumerable.Empty<FormEditField>()).Where(a => !string.IsNullOrEmpty(a.label) && !a.@readonly);
|
|
|
|
if (formEditFields != null && formEditFields.Any())
|
|
{
|
|
fields.AddRange(formEditFields);
|
|
}
|
|
var fieldCount = formEditFields.Count();
|
|
endIndex = startIndex + fieldCount - 1;
|
|
|
|
for (var a = startIndex; a <= endIndex; a++)
|
|
{
|
|
var cell = rowHeader.CreateCell(a);
|
|
if (a == startIndex)
|
|
{
|
|
cell.SetCellValue(formEditSections[j].title);
|
|
}
|
|
cell.CellStyle = styleHeader;
|
|
}
|
|
|
|
sheet.AddMergedRegion(new CellRangeAddress(0, 0, startIndex, endIndex));//起始行号，终止行号， 起始列号，终止列号
|
|
startIndex += fieldCount;
|
|
}
|
|
|
|
|
|
// 创建绘图对象
|
|
var p = (XSSFDrawing)sheet.CreateDrawingPatriarch();
|
|
|
|
var helper = new XSSFDataValidationHelper((XSSFSheet)sheet);
|
|
|
|
//创建单元格样式
|
|
var style = wb.CreateCellStyle();
|
|
font.FontHeightInPoints = 10;
|
|
style.SetFont(font);
|
|
style.Alignment = HorizontalAlignment.Center;
|
|
|
|
var row = sheet.CreateRow(1);
|
|
for (var i = 0; i < fields.Count; i++)
|
|
{
|
|
|
|
if (fields[i].@readonly)
|
|
{
|
|
continue;
|
|
}
|
|
var icelltop = row.CreateCell(i);
|
|
icelltop.SetCellValue(fields[i].label);
|
|
|
|
// 获取批注对象
|
|
// (int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2)
|
|
// 前四个参数是坐标点,后四个参数是编辑和显示批注时的大小.
|
|
var comment = (XSSFComment)p.CreateCellComment(new XSSFClientAnchor(0, 0, 0, 0, i + 1, 2, i + 4, 2 + 5));
|
|
// 输入批注信息
|
|
|
|
var IsRequired = $"是否必填：" + (fields[i].required ? "是" : "否");
|
|
switch (fields[i].type)
|
|
{
|
|
case "select":
|
|
var selectData = GetSelectData(selectDatas, fields[i].name);
|
|
if (selectData.Length > 0)
|
|
{
|
|
//if (selectData.Length > 20)
|
|
//{
|
|
// selectData = selectData.Take(20).ToArray();//npoi 下拉框数据过多会导致生成excel数据验证失败
|
|
//}
|
|
//CellRangeAddressList cellRegions = new CellRangeAddressList(1, 1000, i, i);//起始行号，终止行号， 起始列号，终止列号
|
|
//var dropDownConstraint = helper.CreateExplicitListConstraint(selectData);
|
|
//IDataValidation dropDownValidation = helper.CreateValidation(dropDownConstraint, cellRegions);
|
|
//dropDownValidation.CreateErrorBox("错误", "请按右侧下拉箭头选择!");
|
|
//dropDownValidation.ShowErrorBox = true;
|
|
//sheet.AddValidationData(dropDownValidation);
|
|
comment.SetString($"{IsRequired}; 可选下拉框值; {fields[i].placeholder}");
|
|
}
|
|
break;
|
|
case "select-m":
|
|
var selectMData = GetSelectData(selectDatas, fields[i].name);
|
|
var selectMDataStr = string.Join(",", selectMData);
|
|
comment.SetString($"{IsRequired};此单元格可填：【{selectMDataStr}】; 可填多个值以英文逗号‘,’隔开; {fields[i].placeholder}");
|
|
break;
|
|
case "select-sm":
|
|
var selectSMData = GetSelectData(selectDatas, fields[i].name);
|
|
var selectSMDataStr = string.Join(",", selectSMData);
|
|
comment.SetString($"{IsRequired};此单元格可填：【{selectSMDataStr}】; 可填多个值以英文逗号‘,’隔开; {fields[i].placeholder}");
|
|
break;
|
|
case "date":
|
|
comment.SetString($"{IsRequired}; 可填（yyyy-MM-dd , yyyy/MM/dd）日期格式; {fields[i].placeholder}");
|
|
break;
|
|
case "dateStr":
|
|
comment.SetString($"{IsRequired}; 可填（yyyy-MM-dd , yyyy/MM/dd）日期格式或长期有效; {fields[i].placeholder}");
|
|
break;
|
|
case "text":
|
|
comment.SetString($"{IsRequired}; {fields[i].placeholder}");
|
|
break;
|
|
default:
|
|
comment = null;
|
|
break;
|
|
}
|
|
icelltop.CellComment = comment;
|
|
icelltop.CellStyle = style;
|
|
sheet.SetColumnWidth(i, 15 * 256);
|
|
|
|
//启用数字验证
|
|
//if (column.NumberValidation)
|
|
//{
|
|
// var numberValidStart = column.NumberValidStart;
|
|
// var numberValidEnd = column.NumberValidEnd;
|
|
// var numberConstraint = helper.CreateNumericConstraint(ValidationType.INTEGER, OperatorType.BETWEEN, numberValidStart.ToString(), numberValidEnd.ToString());
|
|
// IDataValidation numberValidation = helper.CreateValidation(numberConstraint, cellRegions);
|
|
// numberValidation.CreateErrorBox("错误", $"只能输入数字:{numberValidStart}-{numberValidEnd}");
|
|
// numberValidation.ShowErrorBox = true;
|
|
// sheet.AddValidationData(numberValidation);
|
|
//}
|
|
}
|
|
//
|
|
wb.Write(ms); //写入到excel
|
|
return ms.ToArray();
|
|
}
|
|
finally
|
|
{
|
|
wb.Close();
|
|
if (ms != null)
|
|
{
|
|
ms.AllowClose = true;
|
|
ms.Close();
|
|
ms.Dispose();
|
|
}
|
|
}
|
|
}
|
|
|
|
public void ImportFromExcel(Stream streamIn, out int totalNum, out int successNum)
|
|
{
|
|
totalNum = 0;
|
|
successNum = 0;
|
|
|
|
var rowIndex = 0;
|
|
try
|
|
{
|
|
var ds = Office.ExcelHelper.ReadExcelAsDataSet(streamIn, new[] { 0 }, 0);
|
|
|
|
if (ds.Tables.Count < 1 || ds.Tables[0].Rows.Count < 3)
|
|
{
|
|
throw new ServiceException("读取导入数据失败:数据为空") { Tag = "111" };
|
|
}
|
|
|
|
var table = ds.Tables[0];
|
|
var reader = new DataRowReader(table, 1);
|
|
|
|
rowIndex = 2;
|
|
totalNum = table.Rows.Count - rowIndex;
|
|
|
|
var columnNameList = new List<string>();
|
|
for (var i = 0; i < table.Columns.Count; i++)
|
|
{
|
|
columnNameList.Add(table.Rows[1][i] as string);//获取到DataColumn列对象的列名
|
|
}
|
|
|
|
|
|
foreach (var row in table.Rows.Cast<DataRow>().Skip(2))
|
|
{
|
|
using var trans = BeginTransaction();
|
|
rowIndex++;
|
|
if (row.ItemArray.All(n => string.IsNullOrWhiteSpace(n?.ToString())))
|
|
{
|
|
totalNum--;
|
|
continue;
|
|
}
|
|
reader.SetDataRow(row);
|
|
HandleClientInfo(reader, columnNameList);
|
|
successNum++;
|
|
|
|
trans.Commit();
|
|
}
|
|
}
|
|
catch (ServiceException se)
|
|
{
|
|
if (se.Tag != null)
|
|
{
|
|
throw;
|
|
}
|
|
|
|
throw new ServiceException($"已成功导入{successNum}条;\n第{rowIndex}行,{se.Message}");
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new ServiceException($"已成功导入{successNum}条,\n第{rowIndex}行,发生错误:{ex.Message}", ex);
|
|
}
|
|
}
|
|
|
|
|
|
|
|
public ImportFromExcelForBatchUpdateRes ImportFromExcelForBatchUpdate(Stream streamIn)
|
|
{
|
|
var res = new ImportFromExcelForBatchUpdateRes() { HasError = false };
|
|
var errStrb = new StringBuilder();
|
|
|
|
var rowIndex = 0;
|
|
|
|
var ds = Office.ExcelHelper.ReadExcelAsDataSet(streamIn, new[] { 0 }, 0);
|
|
|
|
if (ds.Tables.Count < 1 || ds.Tables[0].Rows.Count < 3)
|
|
{
|
|
throw new ServiceException("读取导入数据失败:数据为空") { Tag = "111" };
|
|
}
|
|
|
|
var table = ds.Tables[0];
|
|
var reader = new DataRowReader(table, 1);
|
|
|
|
rowIndex = 2;
|
|
res.TotalNum = table.Rows.Count - rowIndex;
|
|
|
|
var columnNameList = new List<string>();
|
|
for (var i = 0; i < table.Columns.Count; i++)
|
|
{
|
|
columnNameList.Add(table.Rows[1][i] as string);//获取到DataColumn列对象的列名
|
|
}
|
|
foreach (var row in table.Rows.Cast<DataRow>().Skip(2))
|
|
{
|
|
try
|
|
{
|
|
rowIndex++;
|
|
if (row.ItemArray.All(n => string.IsNullOrWhiteSpace(n?.ToString())))
|
|
{
|
|
res.TotalNum--;
|
|
continue;
|
|
}
|
|
reader.SetDataRow(row);
|
|
HandleClientInfo(reader, columnNameList, false, false);
|
|
//res.SuccessNum++;
|
|
}
|
|
catch (ServiceException ex)
|
|
{
|
|
res.HasError = true;
|
|
errStrb.Append($"第{rowIndex}行,{ex.Message}，导入失败<br/>");
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
res.HasError = true;
|
|
throw new ServiceException($"已成功导入{res.SuccessNum}条,\n第{rowIndex}行,发生错误:{ex.Message}", ex);
|
|
}
|
|
}
|
|
|
|
|
|
|
|
|
|
if (!res.HasError)
|
|
{
|
|
rowIndex = 2;
|
|
res.TotalNum = table.Rows.Count - rowIndex;
|
|
res.SuccessNum = 0;
|
|
foreach (var row in table.Rows.Cast<DataRow>().Skip(2))
|
|
{
|
|
try
|
|
{
|
|
using var trans = BeginTransaction();
|
|
rowIndex++;
|
|
if (row.ItemArray.All(n => string.IsNullOrWhiteSpace(n?.ToString())))
|
|
{
|
|
res.TotalNum--;
|
|
continue;
|
|
}
|
|
reader.SetDataRow(row);
|
|
HandleClientInfo(reader, columnNameList, false, true);
|
|
res.SuccessNum++;
|
|
trans.Commit();
|
|
}
|
|
catch (ServiceException ex)
|
|
{
|
|
res.HasError = true;
|
|
errStrb.Append($"第{rowIndex}行,{ex.Message}，导入失败<br/>");
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
res.HasError = true;
|
|
throw new ServiceException($"已成功导入{res.SuccessNum}条,\n第{rowIndex}行,发生错误:{ex.Message}", ex);
|
|
}
|
|
}
|
|
}
|
|
res.ErrorMsg = errStrb.ToString();
|
|
return res;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 处理客户导入 按行
|
|
/// </summary>
|
|
/// <param name="reader"></param>
|
|
/// <param name="columns"></param>
|
|
/// <param name="canAdd">是否允许新增客户</param>
|
|
/// <exception cref="ServiceException"></exception>
|
|
public void HandleClientInfo(DataRowReader reader, List<string> columns, bool canAdd = true, bool saveData = true)
|
|
{
|
|
if (columns != null && columns.Count > 0)
|
|
{
|
|
var isUpdate = false;
|
|
var client = new Client();
|
|
var clientSaveService = new ClientSaveService(OptUser);
|
|
var formEditFields = ClientEditConfigService.GetAllFormEditField();
|
|
Client oldClient;
|
|
using (var clientDB = new ClientDBContext())
|
|
{
|
|
for (var i = 0; i < columns.Count; i++)
|
|
{
|
|
var ColumnName = columns[i];
|
|
var formEditField = ClientEditConfigService.GetFormEditFieldByLabel(formEditFields, ColumnName);
|
|
if (formEditField?.name == "Name")
|
|
{
|
|
client.Name = reader.GetString(ColumnName, true);
|
|
break;
|
|
}
|
|
}
|
|
if (string.IsNullOrEmpty(client.Name))
|
|
{
|
|
throw new ServiceException("客户名称必填");
|
|
}
|
|
//为了支持客户修改导入，未填写的字段需要保留已存在的数据，不能被空值覆盖
|
|
oldClient = clientDB.client.Where(a => a.Name == client.Name).FirstOrDefault();
|
|
if (oldClient != null)
|
|
{
|
|
isUpdate = true;
|
|
client = oldClient;
|
|
}
|
|
}
|
|
|
|
if (!isUpdate && !canAdd)
|
|
{
|
|
throw new ServiceException("客户不存在");
|
|
}
|
|
|
|
var clientMetas = new List<ClientMeta>();
|
|
var config = ClientEditConfigService.GetConfig(true);
|
|
|
|
for (var i = 0; i < columns.Count; i++)
|
|
{
|
|
var ColumnName = columns[i];
|
|
if (string.IsNullOrEmpty(ColumnName))
|
|
{
|
|
throw new ServiceException("导入的excel表格列名不能为空");
|
|
}
|
|
|
|
var formEditField = ClientEditConfigService.GetFormEditFieldByLabel(formEditFields, ColumnName);
|
|
if (formEditField == null)
|
|
{
|
|
continue;
|
|
}
|
|
|
|
|
|
if (isUpdate)
|
|
{
|
|
formEditField.required = false;
|
|
}
|
|
|
|
string fieldValue;
|
|
|
|
//if (config.defaults.Any(a => a.Key == formEditField.name))
|
|
//{
|
|
// formEditField.required = false;
|
|
// formEditField.value = config.defaults[formEditField.name];
|
|
//}
|
|
|
|
switch (formEditField.type)
|
|
{
|
|
case "select":
|
|
if (formEditField.label == "机构属性" && (client.ClientType == "产品" || client.ClientType == "自然人"))
|
|
{
|
|
formEditField.required = false;
|
|
}
|
|
var cellValue = reader.GetString(ColumnName, formEditField.required);
|
|
if (string.IsNullOrEmpty(cellValue))
|
|
{
|
|
continue;
|
|
}
|
|
var selectData = GetSelectData(config.selects, formEditField.name);
|
|
if (!selectData.Any(a => a == cellValue))
|
|
{
|
|
|
|
throw new ServiceException($"导入数据不正确，请选择【{ColumnName}】下拉框中的数据");
|
|
}
|
|
fieldValue = ClientEditConfigService.GetSelectValue(formEditField.name, cellValue);
|
|
SetAttributeValue(client, clientMetas, formEditField, fieldValue);
|
|
break;
|
|
case "select-m":
|
|
var cellselsValue = reader.GetString(ColumnName, formEditField.required);
|
|
if (string.IsNullOrEmpty(cellselsValue))
|
|
{
|
|
continue;
|
|
}
|
|
SetAttributeValue(client, clientMetas, formEditField, cellselsValue, config.selects);
|
|
|
|
break;
|
|
case "select-sm":
|
|
var cellselsmValue = reader.GetString(ColumnName, formEditField.required);
|
|
if (string.IsNullOrEmpty(cellselsmValue))
|
|
{
|
|
continue;
|
|
}
|
|
SetAttributeValue(client, clientMetas, formEditField, cellselsmValue, config.selects);
|
|
break;
|
|
case "text":
|
|
var celltxtValue = reader.GetString(ColumnName, formEditField.required);
|
|
if (string.IsNullOrEmpty(celltxtValue))
|
|
{
|
|
continue;
|
|
}
|
|
SetAttributeValue(client, clientMetas, formEditField, celltxtValue);
|
|
break;
|
|
case "date":
|
|
var celldateValue = reader.GetDate(ColumnName, formEditField.required);
|
|
if (celldateValue == null)
|
|
{
|
|
continue;
|
|
}
|
|
SetAttributeValue(client, clientMetas, formEditField, celldateValue?.ToString("yyyy-MM-dd"));
|
|
break;
|
|
case "dateStr":
|
|
var celldateStrValue = reader.GetString(ColumnName, formEditField.required);
|
|
if (string.IsNullOrEmpty(celldateStrValue))
|
|
{
|
|
continue;
|
|
}
|
|
SetAttributeValue(client, clientMetas, formEditField, celldateStrValue);
|
|
break;
|
|
default:
|
|
break;
|
|
}
|
|
|
|
if (!string.IsNullOrEmpty(client.ClientType))
|
|
{
|
|
switch (client.ClientType)
|
|
{
|
|
case "机构":
|
|
client.ProductNumber = null;
|
|
client.AdminRegisteredNum = null;
|
|
client.AdminFullName = null;
|
|
break;
|
|
case "产品":
|
|
client.InstitutionalAttributes = null;
|
|
break;
|
|
case "自然人":
|
|
client.ProductNumber = null;
|
|
client.AdminRegisteredNum = null;
|
|
client.AdminFullName = null;
|
|
client.RegisteredCapital = null;
|
|
client.RegisteredAddress = null;
|
|
break;
|
|
}
|
|
}
|
|
if (!string.IsNullOrEmpty(client.BusinessType))
|
|
{
|
|
var item = DictionaryBLL.GetDictionaryItemByName(client.BusinessType);
|
|
var customerNatures = item == null ? "" : item.ShortName;
|
|
if (customerNatures.IndexOf(",") > 0)
|
|
{
|
|
client.CustomerNature1 = customerNatures.Split(',')[0];
|
|
client.CustomerNature2 = customerNatures.Split(',')[1];
|
|
}
|
|
else
|
|
{
|
|
client.CustomerNature1 = customerNatures;
|
|
}
|
|
}
|
|
if (!saveData)
|
|
{
|
|
clientSaveService.CheckModel(client, oldClient.Name, false);
|
|
}
|
|
}
|
|
if (saveData)
|
|
{
|
|
SaveClientInfo(client, clientMetas);
|
|
}
|
|
}
|
|
}
|
|
public void SetAttributeValue(Client client, List<ClientMeta> clientMetas, FormEditField formEditField, object value)
|
|
{
|
|
if (formEditField.name.Contains("meta_"))
|
|
{
|
|
|
|
var clientMeta = new ClientMeta()
|
|
{
|
|
MetaKey = formEditField.name.Split('_')[1],
|
|
MetaValue = value.ToString(),
|
|
OptDate = DateTime.Now,
|
|
OptId = UserId,
|
|
OptName = UserName
|
|
|
|
};
|
|
clientMetas.Add(clientMeta);
|
|
}
|
|
else
|
|
{
|
|
var propertys = client.GetType().GetProperties();
|
|
foreach (var property in propertys)
|
|
{
|
|
if (formEditField.name == property.Name)
|
|
{
|
|
if (formEditField.name == "LicenseValidTimeStr")
|
|
{
|
|
property.SetValue(client, value.ToString() == "长期有效" ? "30001231" : value.ToString(), null);
|
|
break;
|
|
}
|
|
if (property.PropertyType == typeof(string))
|
|
{
|
|
property.SetValue(client, value.ToString(), null);
|
|
}
|
|
else if (property.PropertyType == typeof(int) || property.PropertyType == typeof(int?))
|
|
{
|
|
property.SetValue(client, Convert.ToInt32(value), null);
|
|
}
|
|
else if (property.PropertyType == typeof(DateTime) || property.PropertyType == typeof(DateTime?))
|
|
{
|
|
property.SetValue(client, Convert.ToDateTime(value), null);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
public void SetAttributeValue(Client client, List<ClientMeta> clientMetas, FormEditField formEditField, string value, Dictionary<string, IEnumerable<SelectItem>> selectDatas)
|
|
{
|
|
if (!string.IsNullOrEmpty(value))
|
|
{
|
|
var texts = new List<string>();
|
|
if (value.Contains(","))
|
|
{
|
|
texts = value.Split(',').ToList();
|
|
}
|
|
else
|
|
{
|
|
texts.Add(value);
|
|
}
|
|
|
|
if (selectDatas[formEditField.name] != null)
|
|
{
|
|
if (!selectDatas[formEditField.name].Any(a => texts.Any(b => b == a.Text)))
|
|
{
|
|
throw new ServiceException($"【{formEditField.label}】填写有误");
|
|
}
|
|
}
|
|
|
|
if (formEditField.name.Contains("meta_"))
|
|
{
|
|
|
|
var clientMeta = new ClientMeta()
|
|
{
|
|
MetaKey = formEditField.name.Split('_')[1],
|
|
MetaValue = value.ToString(),
|
|
OptDate = DateTime.Now,
|
|
OptId = UserId,
|
|
OptName = UserName
|
|
|
|
};
|
|
clientMetas.Add(clientMeta);
|
|
}
|
|
else
|
|
{
|
|
var vals = new List<string>();
|
|
var propertys = client.GetType().GetProperties();
|
|
foreach (var property in propertys)
|
|
{
|
|
if (formEditField.name == property.Name)
|
|
{
|
|
if (selectDatas.TryGetValue(formEditField.name, out var selectData))
|
|
{
|
|
foreach (var item in selectData)
|
|
{
|
|
if (texts.Any(a => a == item.Text))
|
|
{
|
|
vals.Add(item.Value);
|
|
}
|
|
}
|
|
if (property.Name == "TradingInstType")
|
|
{
|
|
var val = vals.Sum(a => Convert.ToInt32(a));
|
|
property.SetValue(client, val, null);
|
|
break;
|
|
}
|
|
else
|
|
{
|
|
var valStr = string.Join(",", vals);
|
|
property.SetValue(client, valStr, null);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
public void SaveClientInfo(Client client, List<ClientMeta> clientMeta = null)
|
|
{
|
|
using var clientDB = new ClientDBContext();
|
|
var isClientProcess = yldb.approvalprocess.Where(a => a.processType == "OpenProcess");//判断有没有设置开户审批流程
|
|
if (isClientProcess == null)
|
|
{
|
|
client.ProcessOrderId = -2;
|
|
client.ProcessStatus = "已开户";
|
|
client.ProcessOptDate = DateTime.Now;
|
|
}
|
|
else
|
|
{
|
|
client.ProcessStatus = "未提交";
|
|
client.ProcessOrderId = 0;
|
|
client.ProcessOptDate = DateTime.Now;
|
|
}
|
|
if (clientMeta != null)
|
|
{
|
|
client.MetaDic = clientMeta.ToDictionary(a => a.MetaKey, b => b.MetaValue);
|
|
}
|
|
|
|
var oldClient = clientDB.client.Where(a => a.Name == client.Name).FirstOrDefault();
|
|
if (oldClient != null)
|
|
{
|
|
client.id = oldClient.id;
|
|
client.Number = oldClient.Number;
|
|
}
|
|
if (PS.Config.IsGuoJun && client.ConfirmBookMode == "单章版" && client.SupProtocolDate == null)
|
|
{
|
|
client.SupProtocolDate = client.ProtocolSignDate;
|
|
}
|
|
|
|
new ClientSaveService(OptUser).SaveData(client, null, out bool blAudit);
|
|
var client_autolog = new ClientAuditLog
|
|
{
|
|
ClientId = client.id,
|
|
OptType = "导入客户基本信息",
|
|
Changes = string.Empty,
|
|
DataType = "00",
|
|
OptId = UserId,
|
|
OptName = UserName,
|
|
OptDate = DateTime.Now
|
|
};
|
|
clientDB.ClientAuditLog.Add(client_autolog);
|
|
clientDB.SaveChanges();
|
|
}
|
|
|
|
/// <summary>
|
|
/// 客户导入kyc信息
|
|
/// </summary>
|
|
/// <param name="streamIn"></param>
|
|
/// <exception cref="ServiceException"></exception>
|
|
public void ImportKYCExcelToClientInfo(Stream streamIn)
|
|
{
|
|
string[] cellAdress = { "D5", "D6", "D7", "D8", "D9", "D10", "D11", "G11", "D12", "D13", "D14", "F16", "F17", "F18", "F19", "F20", "F21", "F22", "F23", "D29", "D30", "D31", "D32", "D33", "D34", "F36", "F37", "F38", "F39", "F40", "F41" };
|
|
string[] cellAdress1 = { "D8", "D9", "D10", "D11", "D12", "D17", "D18", "D24", "D25" };
|
|
string[] cellAdress2 = { "D9", "D11", "D12" };
|
|
string[] cellAdress3 = { "D7", "G7", "D12", "G12", "D17", "G17", "D22", "G22" };
|
|
string[] cellAdress4 = { "F12", "F13", "F14", "F15", "F16", "F17", "F18", "F19" };
|
|
|
|
string[] cols = { "C", "D", "E", "F", "G", "H", "I" };
|
|
int[] rows = { 17, 18, 19, 20 };
|
|
var cellAdress5 = new List<string>();
|
|
foreach (var col in cols)
|
|
{
|
|
foreach (var row in rows)
|
|
{
|
|
cellAdress5.Add(col + row.ToString());
|
|
}
|
|
}
|
|
string[] cellAdress6 = { "A100", "A177", "A199", "D8", "D9" };
|
|
|
|
string[] cellAdress7 = { "D8", "D9" };
|
|
|
|
var sheetDic = new Dictionary<string, string[]>
|
|
{
|
|
{ "基本信息表", cellAdress },
|
|
{ "交易信息", cellAdress1 },
|
|
//sheetDic.Add("财务信息", cellAdress2);
|
|
{ "股东信息", cellAdress3 },
|
|
{ "受益人信息", cellAdress4 },
|
|
{ "授权委托", cellAdress5.ToArray() },
|
|
{ "风险评估", cellAdress6 },
|
|
{ "资信评估", cellAdress7 }
|
|
};
|
|
|
|
var dics = new ExcelHelper().ExcelToDictionary(streamIn, sheetDic);
|
|
var client = new Client
|
|
{
|
|
MetaDic = new Dictionary<string, string>()
|
|
};
|
|
var config = ClientEditConfigService.GetConfig(true);
|
|
if (config.defaults != null)
|
|
{
|
|
foreach (var dic in config.defaults)
|
|
{
|
|
if (dic.Key.Contains("meta_"))
|
|
{
|
|
client.MetaDic.Add(dic.Key.Split('_')[1], dic.Value);
|
|
}
|
|
else
|
|
{
|
|
var propertys = client.GetType().GetProperties();
|
|
foreach (var property in propertys)
|
|
{
|
|
if (dic.Key == property.Name)
|
|
{
|
|
if (property.PropertyType == typeof(string))
|
|
{
|
|
property.SetValue(client, dic.Value.ToString(), null);
|
|
}
|
|
else if (property.PropertyType == typeof(int) || property.PropertyType == typeof(int?))
|
|
{
|
|
var val = string.IsNullOrEmpty(dic.Value) ? 0 : Convert.ToInt32(dic.Value);
|
|
property.SetValue(client, val, null);
|
|
}
|
|
else if (property.PropertyType == typeof(DateTime) || property.PropertyType == typeof(DateTime?))
|
|
{
|
|
property.SetValue(client, Convert.ToDateTime(dic.Value), null);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
var oldClient = DbContext.client.FirstOrDefault(n => n.Name == dics["基本信息表"]["D5"].ToString());
|
|
client.Name = dics["基本信息表"]["D5"].ToString();
|
|
if (string.IsNullOrEmpty(client.Name))
|
|
{
|
|
throw new ServiceException("单位全称必填！");
|
|
}
|
|
if (oldClient?.ApprovalStatus == "审批中")
|
|
{
|
|
throw new ServiceException("审批中客户不可以修改客户信息");
|
|
}
|
|
|
|
//client.LicenseType = "营业执照";
|
|
client.LicenseCode = dics["基本信息表"]["D6"].ToString();
|
|
if (string.IsNullOrEmpty(client.LicenseCode))
|
|
{
|
|
throw new ServiceException("营业执照登记证号必填！");
|
|
}
|
|
client.MetaDic["FoundDate"] = DateTime.TryParse(dics["基本信息表"]["D7"].ToString(), out var FoundDate) ? FoundDate.ToString("yyyy-MM-dd") : null;
|
|
if (string.IsNullOrEmpty(dics["基本信息表"]["D7"].ToString()))
|
|
{
|
|
throw new ServiceException("成立日期必填！");
|
|
}
|
|
|
|
client.ProtocolSignDate = DateTime.Now;
|
|
client.RegisteredCapital = dics["基本信息表"]["D8"].ToString();
|
|
if (string.IsNullOrEmpty(client.RegisteredCapital))
|
|
{
|
|
throw new ServiceException("注册资本(万元)必填！");
|
|
}
|
|
var D9 = dics["基本信息表"]["D9"].ToString();
|
|
if (string.IsNullOrEmpty(D9))
|
|
{
|
|
throw new ServiceException("机构类型必填！");
|
|
}
|
|
client.MetaDic["OrganizationType"] = D9;
|
|
client.ClientType = "机构";
|
|
var D10 = dics["基本信息表"]["D10"].ToString();
|
|
if (string.IsNullOrEmpty(D10))
|
|
{
|
|
throw new ServiceException("性质必填！");
|
|
}
|
|
if (D10.Contains("其他"))
|
|
{
|
|
D10 = "其他";
|
|
}
|
|
client.MetaDic["Nature"] = D10;
|
|
|
|
var D11 = dics["基本信息表"]["D11"].ToString();
|
|
if (string.IsNullOrEmpty(D11))
|
|
{
|
|
throw new ServiceException("投资者类型必填！");
|
|
}
|
|
client.MetaDic["Investor"] = D11;
|
|
|
|
if (D11 == "其他（请备注中填写）")
|
|
{
|
|
var G11 = dics["基本信息表"]["G11"].ToString();
|
|
if (string.IsNullOrEmpty(G11))
|
|
{
|
|
throw new ServiceException("投资者类型选择其他，请填写备注！");
|
|
}
|
|
switch (G11)
|
|
{
|
|
case "风险管理子公司":
|
|
client.BusinessType = "期货公司风险管理子公司";
|
|
break;
|
|
case "私募基金管理人":
|
|
client.BusinessType = "基金管理人";
|
|
break;
|
|
case "私募基金产品":
|
|
client.BusinessType = "金融产品";
|
|
break;
|
|
default:
|
|
client.BusinessType = G11;
|
|
break;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
switch (D11)
|
|
{
|
|
case "证券自营":
|
|
client.BusinessType = "券商";
|
|
break;
|
|
case "保险公司":
|
|
client.BusinessType = "保险";
|
|
break;
|
|
default:
|
|
client.BusinessType = D11;
|
|
break;
|
|
}
|
|
}
|
|
var item = DictionaryBLL.GetDictionaryItemByName(client.BusinessType);
|
|
var customerNatures = item == null ? "" : item.ShortName;
|
|
if (customerNatures.IndexOf(",") > 0)
|
|
{
|
|
client.CustomerNature1 = customerNatures.Split(',')[0];
|
|
client.CustomerNature2 = customerNatures.Split(',')[1];
|
|
}
|
|
else
|
|
{
|
|
client.CustomerNature1 = customerNatures;
|
|
}
|
|
|
|
var D12 = dics["基本信息表"]["D12"].ToString();
|
|
if (string.IsNullOrEmpty(D12))
|
|
{
|
|
throw new ServiceException("经营范围必填！");
|
|
}
|
|
client.MetaDic["ScopeBusiness"] = D12;
|
|
var D13 = dics["基本信息表"]["D13"].ToString();
|
|
if (string.IsNullOrEmpty(D13))
|
|
{
|
|
throw new ServiceException("注册地址必填！");
|
|
}
|
|
client.RegisteredAddress = D13;
|
|
var D14 = dics["基本信息表"]["D14"].ToString();
|
|
if (string.IsNullOrEmpty(D14))
|
|
{
|
|
throw new ServiceException("办公地址必填！");
|
|
}
|
|
client.PostalAddress = D14;
|
|
|
|
var F16 = dics["基本信息表"]["F16"].ToString();
|
|
var F17 = dics["基本信息表"]["F17"].ToString();
|
|
var F18 = dics["基本信息表"]["F18"].ToString();
|
|
var F19 = dics["基本信息表"]["F19"].ToString();
|
|
var F20 = dics["基本信息表"]["F20"].ToString();
|
|
var F21 = dics["基本信息表"]["F21"].ToString();
|
|
var F22 = dics["基本信息表"]["F22"].ToString();
|
|
var F23 = dics["基本信息表"]["F23"].ToString();
|
|
|
|
client.BadFaithRecord = (int)BadFaithRecordEnum.无;
|
|
if (F16 == "是")
|
|
{
|
|
client.BadFaithRecord = (int)BadFaithRecordEnum.中国人民银行征信中心;
|
|
}
|
|
else if (F17 == "是")
|
|
{
|
|
client.BadFaithRecord = (int)BadFaithRecordEnum.最高人民法院失信被执行人名单;
|
|
}
|
|
else if (F18 == "是")
|
|
{
|
|
client.BadFaithRecord = (int)BadFaithRecordEnum.工商行政管理机构;
|
|
}
|
|
else if (F19 == "是")
|
|
{
|
|
client.BadFaithRecord = (int)BadFaithRecordEnum.税务管理机构;
|
|
}
|
|
else if (F20 == "是")
|
|
{
|
|
client.BadFaithRecord = (int)BadFaithRecordEnum.监管机构;
|
|
}
|
|
else if (F21 == "是")
|
|
{
|
|
client.BadFaithRecord = (int)BadFaithRecordEnum.投资者在期货经营机构从事投资活动时产生的违约失信行为记录;
|
|
}
|
|
else if (F22 == "是")
|
|
{
|
|
client.BadFaithRecord = (int)BadFaithRecordEnum.过度维权等不当行为信息;
|
|
}
|
|
else if (F23 == "是")
|
|
{
|
|
client.BadFaithRecord = (int)BadFaithRecordEnum.其他组织;
|
|
}
|
|
|
|
var F36 = dics["基本信息表"]["F36"].ToString();
|
|
if (string.IsNullOrEmpty(F36))
|
|
{
|
|
throw new ServiceException("是否为来自FATF、APG、EAG等国际组织指定的高风险国家或地区 必填！");
|
|
}
|
|
client.MetaDic["IsHighRsk"] = F36 == "是" ? "1" : "0";
|
|
|
|
var F37 = dics["基本信息表"]["F37"].ToString();
|
|
if (string.IsNullOrEmpty(F37))
|
|
{
|
|
throw new ServiceException("资金来源必填！");
|
|
}
|
|
if (F37 == "自有")
|
|
{
|
|
client.FundsSource = 1;
|
|
}
|
|
else if (F37 == "其他")
|
|
{
|
|
client.FundsSource = 2;
|
|
}
|
|
else
|
|
{
|
|
client.FundsSource = null;
|
|
}
|
|
var F38 = dics["基本信息表"]["F38"].ToString();
|
|
if (string.IsNullOrEmpty(F38))
|
|
{
|
|
throw new ServiceException("是否存在实际控制关系必填！");
|
|
}
|
|
client.IsRealControl = F38 == "是" ? 1 : 0;
|
|
var F39 = dics["基本信息表"]["F39"].ToString();
|
|
if (string.IsNullOrEmpty(F39))
|
|
{
|
|
throw new ServiceException("投资期限必填！");
|
|
}
|
|
if (F39 == EnumHelper.GetDescriptionByName(InvestmentTermEnum.partone))
|
|
{
|
|
client.InvestmentTerm = (int)InvestmentTermEnum.partone;
|
|
}
|
|
else if (F39 == EnumHelper.GetDescriptionByName(InvestmentTermEnum.parttwo))
|
|
{
|
|
client.InvestmentTerm = (int)InvestmentTermEnum.parttwo;
|
|
}
|
|
else if (F39 == EnumHelper.GetDescriptionByName(InvestmentTermEnum.partthree))
|
|
{
|
|
client.InvestmentTerm = (int)InvestmentTermEnum.partthree;
|
|
}
|
|
else if (F39 == EnumHelper.GetDescriptionByName(InvestmentTermEnum.partfour))
|
|
{
|
|
client.InvestmentTerm = (int)InvestmentTermEnum.partfour;
|
|
}
|
|
else
|
|
{
|
|
client.InvestmentTerm = null;
|
|
}
|
|
|
|
var F40 = dics["基本信息表"]["F40"].ToString();
|
|
if (string.IsNullOrEmpty(F40))
|
|
{
|
|
throw new ServiceException("投资品种必填！");
|
|
}
|
|
if (F40 == "场外衍生品")
|
|
{
|
|
client.DerivativesInvestmentVarieties = "1,2,3";
|
|
}
|
|
else if (F40 == "其它")
|
|
{
|
|
client.DerivativesInvestmentVarieties = "4";
|
|
}
|
|
else
|
|
{
|
|
client.DerivativesInvestmentVarieties = "";
|
|
}
|
|
|
|
var D41 = dics["基本信息表"]["F41"].ToString();
|
|
if (string.IsNullOrEmpty(D41))
|
|
{
|
|
throw new ServiceException("期望收益必填！");
|
|
}
|
|
client.ExpectedReturn2 = D41.Replace('~', '-');
|
|
var ActualControllerList = new List<string>();
|
|
var shareholder = dics["股东信息"];
|
|
if (!string.IsNullOrEmpty(shareholder["D7"].ToString()) && shareholder["D7"].ToString() != "/")
|
|
{
|
|
ActualControllerList.Add(shareholder["D7"].ToString() + Convert.ToDouble(shareholder["G7"]).OtcFormatPercent());
|
|
}
|
|
if (!string.IsNullOrEmpty(shareholder["D12"].ToString()) && shareholder["D12"].ToString() != "/")
|
|
{
|
|
ActualControllerList.Add(shareholder["D12"].ToString() + Convert.ToDouble(shareholder["G12"]).OtcFormatPercent());
|
|
}
|
|
if (!string.IsNullOrEmpty(shareholder["D17"].ToString()) && shareholder["D17"].ToString() != "/")
|
|
{
|
|
ActualControllerList.Add(shareholder["D17"].ToString() + Convert.ToDouble(shareholder["G17"]).OtcFormatPercent());
|
|
}
|
|
if (!string.IsNullOrEmpty(shareholder["D22"].ToString()) && shareholder["D22"].ToString() != "/")
|
|
{
|
|
ActualControllerList.Add(shareholder["D22"].ToString() + Convert.ToDouble(shareholder["G22"]).OtcFormatPercent());
|
|
}
|
|
client.ActualController = ActualControllerList.Any() ? string.Join(";", ActualControllerList) : "";
|
|
|
|
var ActualBeneficiaryList = new List<string>();
|
|
var beneficiary = dics["受益人信息"];
|
|
if (!string.IsNullOrEmpty(beneficiary["F12"].ToString()) && beneficiary["F12"].ToString() != "/")
|
|
{
|
|
var f13 = beneficiary["F13"]?.ToString().TrimToNull();
|
|
if (f13 != null && !f13.EndsWith("%") && double.TryParse(f13, out var d))
|
|
{
|
|
f13 = d.OtcFormatFlex(2, percent: true);
|
|
}
|
|
ActualBeneficiaryList.Add(beneficiary["F12"].ToString() + f13);
|
|
}
|
|
if (!string.IsNullOrEmpty(beneficiary["F14"].ToString()) && beneficiary["F14"].ToString() != "/")
|
|
{
|
|
if (!string.IsNullOrEmpty(beneficiary["F15"].ToString()) && beneficiary["F15"].ToString() != "/")
|
|
{
|
|
ActualBeneficiaryList.Add(beneficiary["F14"].ToString() + Convert.ToDouble(beneficiary["F15"]).OtcFormatPercent());
|
|
}
|
|
}
|
|
if (!string.IsNullOrEmpty(beneficiary["F16"].ToString()) && beneficiary["F16"].ToString() != "/")
|
|
{
|
|
|
|
if (!string.IsNullOrEmpty(beneficiary["F17"].ToString()) && beneficiary["F17"].ToString() != "/")
|
|
{
|
|
ActualBeneficiaryList.Add(beneficiary["F16"].ToString() + Convert.ToDouble(beneficiary["F17"]).OtcFormatPercent());
|
|
}
|
|
}
|
|
if (!string.IsNullOrEmpty(beneficiary["F18"].ToString()) && beneficiary["F18"].ToString() != "/")
|
|
{
|
|
|
|
if (!string.IsNullOrEmpty(beneficiary["F19"].ToString()) && beneficiary["F19"].ToString() != "/")
|
|
{
|
|
ActualBeneficiaryList.Add(beneficiary["F18"].ToString() + Convert.ToDouble(beneficiary["F19"]).OtcFormatPercent());
|
|
}
|
|
}
|
|
client.ActualBeneficiary = ActualBeneficiaryList.Any() ? string.Join(";", ActualBeneficiaryList) : "";
|
|
|
|
//投资风险调查-投资经验
|
|
switch (dics["风险评估"]["A100"].ToString())
|
|
{
|
|
case "1":
|
|
client.InvestmentExperience = "有限";
|
|
break;
|
|
case "2":
|
|
case "3":
|
|
client.InvestmentExperience = "一般";
|
|
break;
|
|
case "4":
|
|
client.InvestmentExperience = "丰富";
|
|
break;
|
|
case "5":
|
|
client.InvestmentExperience = "非常丰富";
|
|
break;
|
|
}
|
|
|
|
//投资风险调查-可接受损失
|
|
switch (dics["风险评估"]["A177"].ToString())
|
|
{
|
|
case "1":
|
|
client.AcceptableLoss = "10%以内";
|
|
break;
|
|
case "2":
|
|
client.AcceptableLoss = "10%-30%";
|
|
break;
|
|
case "3":
|
|
client.AcceptableLoss = "30%-50%";
|
|
break;
|
|
case "4":
|
|
client.AcceptableLoss = "50%以上";
|
|
break;
|
|
}
|
|
|
|
//投资风险调查-交易目的
|
|
switch (dics["风险评估"]["A199"].ToString())
|
|
{
|
|
|
|
case "2":
|
|
client.TransactionTarget = "投资";
|
|
break;
|
|
case "3":
|
|
client.TransactionTarget = "套保";
|
|
break;
|
|
default:
|
|
client.TransactionTarget = oldClient?.TransactionTarget;
|
|
break;
|
|
}
|
|
|
|
if (client.QuestionnaireScore != null)
|
|
{
|
|
client.QuestionnaireScore = Convert.ToInt32(dics["风险评估"]["D8"]);
|
|
}
|
|
|
|
var D9_risk = dics["风险评估"]["D9"].ToString();
|
|
if (string.IsNullOrEmpty(D9_risk))
|
|
{
|
|
client.AppropriatenessDegree = null;
|
|
}
|
|
|
|
client.IsEvaluate = (client.QuestionnaireScore != null && client.QuestionnaireScore >= 0) ? 1 : 0;
|
|
client.MetaDic["CreditScore"] = dics["资信评估"]["D8"].ToString();
|
|
client.MetaDic["CreditRating"] = dics["资信评估"]["D9"].ToString();
|
|
|
|
var counterPartyType = dics["交易信息"]["D24"].ToString().Replace("-", "").Replace("、", "");
|
|
|
|
var clientLevelList = DbContext.clientlevel.ToList();
|
|
|
|
var levelName = "";
|
|
var noProcess = false;
|
|
if (counterPartyType == CounterPartyTypeEnum.一般机构普通投资者.ToString())
|
|
{
|
|
client.ProperClientClass = "普通";
|
|
if (D9_risk == "C4")
|
|
{
|
|
client.AppropriatenessDegree = (int)AppropriatenessDegreeNewEnum.普通C4类;
|
|
levelName = LevelNameEnum.C4风险承受能力投资者.ToString();
|
|
//client.LevelId = (int)AppropriatenessDegreeNewEnum.C4风险承受能力投资者;
|
|
}
|
|
else if (D9_risk == "C5")
|
|
{
|
|
client.AppropriatenessDegree = (int)AppropriatenessDegreeNewEnum.普通C5类;
|
|
//client.LevelId = (int)AppropriatenessDegreeNewEnum.C5风险承受能力投资者;
|
|
levelName = LevelNameEnum.C5风险承受能力投资者.ToString();
|
|
}
|
|
else
|
|
{
|
|
noProcess = true;
|
|
client.AppropriatenessDegree = oldClient?.AppropriatenessDegree;
|
|
client.LevelId = oldClient?.LevelId;
|
|
//throw new ServiceException($"交易对手类型是一般机构普通投资者时，风险评估中对应的评级必须是C4或C5,当前填写值是{D9_risk}，不符合要求！");
|
|
}
|
|
}
|
|
else if (counterPartyType == CounterPartyTypeEnum.一般机构专业投资者.ToString())
|
|
{
|
|
client.AppropriatenessDegree = (int)AppropriatenessDegreeNewEnum.专业B级;
|
|
//client.LevelId = (int)AppropriatenessDegreeNewEnum.B类专业投资者;
|
|
levelName = LevelNameEnum.B类专业投资者.ToString();
|
|
client.ProperClientClass = "专业";
|
|
}
|
|
else if (counterPartyType == CounterPartyTypeEnum.金融机构同业证券保险等.ToString())
|
|
{
|
|
client.AppropriatenessDegree = (int)AppropriatenessDegreeNewEnum.专业A级;
|
|
//client.LevelId = (int)AppropriatenessDegreeNewEnum.A类专业投资者;
|
|
levelName = LevelNameEnum.A类专业投资者.ToString();
|
|
client.ProperClientClass = "专业";
|
|
}
|
|
else if (counterPartyType == CounterPartyTypeEnum.金融产品.ToString())
|
|
{
|
|
client.ClientType = "产品";
|
|
client.AppropriatenessDegree = (int)AppropriatenessDegreeNewEnum.专业A级;
|
|
//client.LevelId = (int)AppropriatenessDegreeNewEnum.A类专业投资者;
|
|
levelName = LevelNameEnum.A类专业投资者.ToString();
|
|
client.ProperClientClass = "专业";
|
|
}
|
|
|
|
client.ChangeReasonAndEvaluationResults = "无";
|
|
if (!noProcess)
|
|
{
|
|
var clientLevel = clientLevelList.FirstOrDefault(n => n.LevelName == levelName);
|
|
if (clientLevel != null)
|
|
{
|
|
client.LevelId = clientLevel.id;
|
|
}
|
|
}
|
|
|
|
//邮政编码
|
|
client.PostalCode = dics["交易信息"]["D10"].ToString();
|
|
|
|
SaveClientInfo(client);
|
|
|
|
var clientDutyLegalPerson = new ClientDuty
|
|
{
|
|
ContactTypeId = DbContext.contactype.Where(a => a.ContactType == "法人").FirstOrDefault()?.id + "",
|
|
ContactName = dics["基本信息表"]["D29"].ToString()
|
|
};
|
|
if (string.IsNullOrEmpty(clientDutyLegalPerson.ContactName))
|
|
{
|
|
throw new ServiceException("法定代表人必填！");
|
|
}
|
|
var D30 = dics["基本信息表"]["D30"].ToString();
|
|
if (string.IsNullOrEmpty(D30))
|
|
{
|
|
throw new ServiceException("证件类型必填！");
|
|
}
|
|
if (D30 == IdCardTypeEnum.身份证.ToString())
|
|
{
|
|
clientDutyLegalPerson.IdCardType = (int)IdCardTypeEnum.身份证;
|
|
}
|
|
else if (D30 == IdCardTypeEnum.军官证.ToString())
|
|
{
|
|
clientDutyLegalPerson.IdCardType = (int)IdCardTypeEnum.军官证;
|
|
}
|
|
else if (D30 == "回乡证(港澳)")
|
|
{
|
|
clientDutyLegalPerson.IdCardType = (int)IdCardTypeEnum.港澳通行证;
|
|
}
|
|
else if (D30 == IdCardTypeEnum.护照.ToString())
|
|
{
|
|
clientDutyLegalPerson.IdCardType = (int)IdCardTypeEnum.护照;
|
|
}
|
|
else if (D30 == IdCardTypeEnum.台胞证.ToString())
|
|
{
|
|
clientDutyLegalPerson.IdCardType = (int)IdCardTypeEnum.台胞证;
|
|
}
|
|
else
|
|
{
|
|
clientDutyLegalPerson.IdCardType = (int)IdCardTypeEnum.其他;
|
|
}
|
|
clientDutyLegalPerson.IdCardNo = dics["基本信息表"]["D31"].ToString();
|
|
if (string.IsNullOrEmpty(clientDutyLegalPerson.IdCardNo))
|
|
{
|
|
throw new ServiceException("证件号码必填！");
|
|
}
|
|
var IdCardDate = dics["基本信息表"]["D32"].ToString();
|
|
if (string.IsNullOrEmpty(IdCardDate))
|
|
{
|
|
throw new ServiceException("证件有效期必填！");
|
|
}
|
|
else if (IdCardDate.Contains("长期"))
|
|
{
|
|
//为了兼容下面的逻辑;
|
|
IdCardDate = "至2099-12-31";
|
|
}
|
|
else if (!IdCardDate.Contains("至"))
|
|
{
|
|
throw new ServiceException("证件有效期必须为YYYY-MM-DD至YYYY-MM-DD格式！");
|
|
}
|
|
clientDutyLegalPerson.IdCardDate = Convert.ToDateTime(IdCardDate.Split('至')[1]);
|
|
clientDutyLegalPerson.PhoneNumber = dics["基本信息表"]["D33"].ToString();
|
|
if (string.IsNullOrEmpty(clientDutyLegalPerson.PhoneNumber))
|
|
{
|
|
throw new ServiceException("联系电话必填！");
|
|
}
|
|
|
|
var D34 = dics["基本信息表"]["D34"].ToString();
|
|
if (!string.IsNullOrEmpty(D34))
|
|
{
|
|
if (D34.Contains('/'))
|
|
{
|
|
clientDutyLegalPerson.Email = D34.Split('/')[0];
|
|
clientDutyLegalPerson.Fax = D34.Split('/')[1];
|
|
}
|
|
else
|
|
{
|
|
clientDutyLegalPerson.Email = D34;
|
|
}
|
|
}
|
|
clientDutyLegalPerson.IsReceiveEmail = string.IsNullOrEmpty(clientDutyLegalPerson.Email) ? 0 : 1;
|
|
clientDutyLegalPerson.ClientId = client.id;
|
|
var clientDutyLegalPersonId = DbContext.clientduty.Where(a => a.ApprovalOrder < 1 && a.ClientId == clientDutyLegalPerson.ClientId
|
|
&& a.ContactName == clientDutyLegalPerson.ContactName
|
|
&& a.ContactTypeId == clientDutyLegalPerson.ContactTypeId).FirstOrDefault()?.id ?? 0;
|
|
clientDutyLegalPerson.id = clientDutyLegalPersonId;
|
|
new ClientDutyService(OptUser).SaveClientDuty(clientDutyLegalPerson);
|
|
|
|
var clientDuty = new ClientDuty
|
|
{
|
|
ContactTypeId = DbContext.contactype.Where(a => a.ContactType == "约定收件人").FirstOrDefault()?.id + "",
|
|
Address = dics["交易信息"]["D8"].ToString()
|
|
};
|
|
if (string.IsNullOrEmpty(clientDuty.Address))
|
|
{
|
|
throw new ServiceException("通信地址必填！");
|
|
}
|
|
clientDuty.ContactName = dics["交易信息"]["D9"].ToString();
|
|
if (string.IsNullOrEmpty(clientDuty.ContactName))
|
|
{
|
|
throw new ServiceException("联系人必填！");
|
|
}
|
|
|
|
//if (string.IsNullOrEmpty(clientDuty.ContactName))
|
|
//{
|
|
// throw new ServiceException("联系人必填！");
|
|
//}
|
|
clientDuty.PhoneNumber = dics["交易信息"]["D11"].ToString();
|
|
if (string.IsNullOrEmpty(clientDuty.PhoneNumber))
|
|
{
|
|
throw new ServiceException("交易信息中的联系电话必填！");
|
|
}
|
|
clientDuty.Email = dics["交易信息"]["D12"].ToString();
|
|
if (string.IsNullOrEmpty(clientDuty.Email))
|
|
{
|
|
throw new ServiceException("交易信息中的电子邮箱必填！");
|
|
}
|
|
clientDuty.IsReceiveEmail = string.IsNullOrEmpty(clientDuty.Email) ? 0 : 1;
|
|
clientDuty.ClientId = client.id;
|
|
var clientDutyId = DbContext.clientduty.Where(a => a.ApprovalOrder < 1 && a.ClientId == clientDuty.ClientId
|
|
&& a.ContactName == clientDuty.ContactName
|
|
&& a.ContactTypeId == clientDuty.ContactTypeId).FirstOrDefault()?.id ?? 0;
|
|
clientDuty.id = clientDutyId;
|
|
new ClientDutyService(OptUser).SaveClientDuty(clientDuty);
|
|
|
|
var clientBankCard = new ClientBankCard
|
|
{
|
|
ClientName = client.Name,
|
|
Card = dics["交易信息"]["D17"].ToString().Replace(" ", "")
|
|
};
|
|
if (string.IsNullOrEmpty(clientBankCard.Card))
|
|
{
|
|
throw new ServiceException("交易信息中的银行账户账号必填！");
|
|
}
|
|
clientBankCard.Bank = dics["交易信息"]["D18"].ToString();
|
|
if (string.IsNullOrEmpty(clientBankCard.Bank))
|
|
{
|
|
throw new ServiceException("交易信息中的银行账户开户行必填！");
|
|
}
|
|
clientBankCard.ValidState = "Valid";
|
|
clientBankCard.ClientId = client.id;
|
|
clientBankCard.id = DbContext.bankcard.Where(a => a.ApprovalOrder < 1 && a.Bank == clientBankCard.Bank && a.ClientId == clientBankCard.ClientId && a.Card == clientBankCard.Card).FirstOrDefault()?.id ?? 0;
|
|
new ClientBankCardService(OptUser).SaveBankCard(clientBankCard, out client);
|
|
|
|
var authorize = dics["授权委托"];
|
|
var clientDutieList = new List<ClientDuty>();
|
|
foreach (var row in rows)
|
|
{
|
|
var contactName = authorize[$"C{row}"].ToString();
|
|
if (string.IsNullOrEmpty(contactName))
|
|
{
|
|
continue;
|
|
}
|
|
var contactypeList = DbContext.contactype.ToList();
|
|
var contactTypeId = authorize[$"D{row}"].ToString();
|
|
if (string.IsNullOrEmpty(contactTypeId))
|
|
{
|
|
continue;
|
|
}
|
|
//A约定收件人，B交易下达人，C资金调拨人
|
|
var contactTypeDic = new Dictionary<string, string>
|
|
{
|
|
{ "A", contactypeList.Where(a => a.ContactType.Contains("签约代理人")).FirstOrDefault()?.id + "" },
|
|
{ "B", contactypeList.Where(a => a.ContactType.Contains("交易下达人")).FirstOrDefault()?.id + "" },
|
|
{ "C", contactypeList.Where(a => a.ContactType.Contains("资金调拨人")).FirstOrDefault()?.id + "" }
|
|
};
|
|
|
|
var contactTypeVal = new List<string>();
|
|
foreach (var dic in contactTypeDic.Keys)
|
|
{
|
|
if (contactTypeId.Contains(dic))
|
|
{
|
|
contactTypeVal.Add(contactTypeDic[dic]);
|
|
}
|
|
}
|
|
var idCardTypeStr = authorize[$"E{row}"].ToString();
|
|
var idCardType = 0;
|
|
if (idCardTypeStr == IdCardTypeEnum.身份证.ToString())
|
|
{
|
|
idCardType = (int)IdCardTypeEnum.身份证;
|
|
}
|
|
else if (idCardTypeStr == IdCardTypeEnum.军官证.ToString())
|
|
{
|
|
idCardType = (int)IdCardTypeEnum.军官证;
|
|
}
|
|
else if (idCardTypeStr == "回乡证(港澳)")
|
|
{
|
|
idCardType = (int)IdCardTypeEnum.港澳通行证;
|
|
}
|
|
else if (idCardTypeStr == IdCardTypeEnum.护照.ToString())
|
|
{
|
|
idCardType = (int)IdCardTypeEnum.护照;
|
|
}
|
|
else if (idCardTypeStr == IdCardTypeEnum.台胞证.ToString())
|
|
{
|
|
idCardType = (int)IdCardTypeEnum.台胞证;
|
|
}
|
|
else
|
|
{
|
|
idCardType = (int)IdCardTypeEnum.其他;
|
|
}
|
|
|
|
string idCardDateStr = authorize[$"G{row}"]?.ToString()?.Trim();
|
|
var contactTypeIds = contactTypeVal.Any() ? string.Join(",", contactTypeVal) : "";
|
|
|
|
clientDutieList.Add(new ClientDuty
|
|
{
|
|
id = DbContext.clientduty.Where(a => a.ApprovalOrder < 1 && a.ClientId == client.id
|
|
&& a.ContactName == contactName
|
|
&& a.ContactTypeId == contactTypeIds).FirstOrDefault()?.id ?? 0,
|
|
ClientId = client.id,
|
|
ContactName = contactName,
|
|
ContactTypeId = contactTypeIds,
|
|
IdCardType = idCardType,
|
|
IdCardNo = authorize[$"F{row}"].ToString(),
|
|
IdCardDate = (string.IsNullOrEmpty(idCardDateStr) || idCardDateStr == "长期") ? new DateTime(2099, 12, 31) : Convert.ToDateTime(idCardDateStr),
|
|
PhoneNumber = authorize[$"H{row}"].ToString(),
|
|
Email = authorize[$"I{row}"].ToString(),
|
|
IsReceiveEmail = string.IsNullOrEmpty(authorize[$"I{row}"].ToString()) ? 0 : 1
|
|
});
|
|
}
|
|
|
|
if (clientDutieList.Count > 0)
|
|
{
|
|
clientDutieList.ForEach(a =>
|
|
{
|
|
new ClientDutyService(OptUser).SaveClientDuty(a);
|
|
});
|
|
}
|
|
else
|
|
{
|
|
//委托授权中没有联系人时，给法人赋值所有类型
|
|
var contactypeList = DbContext.contactype.ToList();
|
|
|
|
var legalPersonTypeId = contactypeList.Where(a => a.ContactType.Contains("法人")).FirstOrDefault()?.id.ToString();
|
|
var allcontactypeIds = new List<string>
|
|
{
|
|
legalPersonTypeId,
|
|
|
|
contactypeList.Where(a => a.ContactType.Contains("签约代理人")).FirstOrDefault()?.id.ToString(),
|
|
contactypeList.Where(a => a.ContactType.Contains("约定收件人")).FirstOrDefault()?.id.ToString(),
|
|
contactypeList.Where(a => a.ContactType.Contains("交易下达人")).FirstOrDefault()?.id.ToString(),
|
|
contactypeList.Where(a => a.ContactType.Contains("资金调拨人")).FirstOrDefault()?.id.ToString()
|
|
};
|
|
|
|
var legalPerson = DbContext.clientduty.FirstOrDefault(n => n.ApprovalOrder < 1 && n.ClientId == client.id && n.ContactTypeId.Contains(legalPersonTypeId));
|
|
|
|
legalPerson.ContactTypeId = string.Join(",", allcontactypeIds);
|
|
new ClientDutyService(OptUser).SaveClientDuty(legalPerson);
|
|
}
|
|
|
|
}
|
|
}
|
|
}
|