using Aspose.Cells;
using Microsoft.AspNetCore.Mvc.RazorPages;
using OASystem.API.OAMethodLib;
using OASystem.Domain.AesEncryption;
using OASystem.Domain.Dtos.CRM;
using OASystem.Domain.Entities.Customer;
using OASystem.Domain.ViewModels.CRM;
using OASystem.Infrastructure.Repositories.CRM;
using System.Collections;
using System.Data;
using System.Diagnostics;
using System.Linq;
using OASystem.Domain.Attributes;
using EyeSoft.Extensions;
namespace OASystem.API.Controllers
{
/// <summary>
/// 市场客户资料
/// </summary>
[Route("api/[controller]/[action]")]
public class MarketCustomerResourcesController : ControllerBase
{
private readonly NewClientDataRepository _clientDataRepository;
private readonly SqlSugarClient _sqlSugar;
/// <summary>
/// 初始化
/// </summary>
public MarketCustomerResourcesController(NewClientDataRepository clientDataRepository, SqlSugarClient sqlSugar)
{
this._clientDataRepository = clientDataRepository;
_sqlSugar = sqlSugar;
}
/// <summary>
/// 客户资料数据
/// 基础数据
/// </summary>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> MarketCustomerInit(MarketCustomerInitDto dto)
{
JsonView jw = new JsonView();
try
{
Result resultData = await _clientDataRepository._Init(dto);
if (resultData.Code == 0)
{
jw = JsonView(true, "查询成功!", resultData.Data);
}
else
{
jw = JsonView(false, resultData.Msg);
}
}
catch (Exception)
{
jw = JsonView(false, "程序错误！");
}
return Ok(jw);
}
/// <summary>
/// 查询客户资料数据
/// </summary>
/// <returns></returns>
[HttpPost]
[ApiLog("Crm_NewClientData", OperationEnum.List)]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> QueryNewClientData(NewClientDataQueryDto dto)
{
#region 参数验证
if (dto.OperationUserId < 0)
return Ok(JsonView(false, "请传入有效的OperationUserId参数！"));
if (dto.PortType < 0)
return Ok(JsonView(false, "请传入有效的PortType参数！"));
#endregion
JsonView jw = new JsonView();
try
{
Result resultData = await _clientDataRepository.QueryNewClientData(dto);
if (resultData.Code == 0)
{
#region 客户资料表操作记录
//await GeneralMethod.NewClientOperationRecord(dto.PortType, OperationEnum.NoOperation, dto.OperationUserId, 0, "");
#endregion
jw = JsonView(true, resultData.Msg, resultData.Data);
}
else
{
jw = JsonView(false, resultData.Msg);
}
}
catch (Exception)
{
jw = JsonView(false, "程序错误！");
}
return Ok(jw);
}
/// <summary>
/// 客户资料数据
/// Details
/// </summary>
/// <returns></returns>
[HttpPost]
[ApiLog("Crm_NewClientData", OperationEnum.Details)]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostNewClientDataDetails(NewClientDataDetailsDto dto)
{
#region 参数验证
if (dto.Id < 0)
return Ok(JsonView(false, "请传入有效的Id参数！"));
if (dto.UserId < 0)
return Ok(JsonView(false, "请传入有效的UserId参数！"));
if (dto.PortType < 0)
return Ok(JsonView(false, "请传入有效的PortType参数！"));
#endregion
JsonView jw = new JsonView();
try
{
Result resultData = await _clientDataRepository._Details(dto.PortType, dto.Id);
if (resultData.Code == 0)
{
#region 客户资料表操作记录
//await GeneralMethod.NewClientOperationRecord(dto.PortType, OperationEnum.Details, dto.UserId, dto.Id, "");
#endregion
jw = JsonView(true, "查询成功!", resultData.Data);
}
else
{
jw = JsonView(false, resultData.Msg);
}
}
catch (Exception)
{
jw = JsonView(false, "程序错误！");
}
return Ok(jw);
}
/// <summary>
/// 客户资料操作（Status:1.新增,2.修改）
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ApiLog("Crm_NewClientData", OperationEnum.NoOperation)]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> NewClientOp(NewClientOpDto dto)
{
#region 参数验证
if (dto.CreateUserId < 0)
{
return Ok(JsonView(false, "请传入有效的CreateUserId参数！"));
}
if (dto.PortType < 0)
{
return Ok(JsonView(false, "请传入有效的PortType参数！"));
}
#endregion
try
{
Domain.Result result = await _clientDataRepository.NewClientOp(dto);
if (result.Code != 0)
{
return Ok(JsonView(false, result.Msg));
}
#region 客户资料操作记录
//OperationEnum operationEnum = OperationEnum.NoOperation;
//if (dto.Status == 1)
//{
// operationEnum = OperationEnum.Add;
// dto.Id = Convert.ToInt32(result.Data);
//}
//else if (dto.Status == 2) operationEnum = OperationEnum.Edit;
//await GeneralMethod.NewClientOperationRecord(dto.PortType, operationEnum, dto.CreateUserId, dto.Id, "");
#endregion
return Ok(JsonView(true, result.Msg + "Id:" + dto.Id));
}
catch (Exception ex)
{
return Ok(JsonView(false, "程序错误！Msg:" + ex.Message));
}
}
/// <summary>
/// 新客户资料操作（删除）
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ApiLog("Crm_NewClientData", OperationEnum.Del)]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> NewClientDel(DelBaseDto dto)
{
#region 参数验证
if (dto.Id < 0)
{
return Ok(JsonView(false, "请传入有效的Id参数！"));
}
if (dto.DeleteUserId < 0)
{
return Ok(JsonView(false, "请传入有效的DeleteUserId参数！"));
}
if (dto.PortType < 0)
{
return Ok(JsonView(false, "请传入有效的PortType参数！"));
}
#endregion
var res = await _clientDataRepository.DelNewClientData(dto);
if (res.Code != 0)
{
return Ok(JsonView(false, "删除失败"));
}
#region 客户资料表操作记录
//await GeneralMethod.NewClientOperationRecord(dto.PortType, OperationEnum.Del, dto.DeleteUserId, dto.Id, "");
#endregion
return Ok(JsonView(true, "删除成功！"));
}
/// <summary>
/// 获取下拉列表数据和单条数据信息
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ApiLog("Crm_NewClientData", OperationEnum.Details)]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> QuerySelectAndSingleData(QuerySingleDto dto)
{
JsonView jw = new JsonView();
var result = await _clientDataRepository.QuerySelectAndSingleData(dto);
if (result.Code == 0)
{
#region 客户资料表操作记录
//await GeneralMethod.NewClientOperationRecord(dto.PortType, OperationEnum.Details, dto.UserId, dto.Id, "");
#endregion
jw = JsonView(true, result.Msg, result.Data);
}
else
{
jw = JsonView(false, result.Msg);
}
return Ok(jw);
}
/// <summary>
/// 获取现有负责人
/// </summary>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> QueryUserSelect()
{
Result resTable = _clientDataRepository.QueryUserSelect();
return Ok(JsonView(true, resTable.Msg, resTable.Data));
}
/// <summary>
/// 获取出团数据
/// </summary>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> QueryNumberGroups()
{
var result = await _clientDataRepository.QueryNumberGroups();
if (result.Code != 0)
{
return Ok(JsonView(false, result.Msg));
}
return Ok(JsonView(true, result.Msg, result.Data));
}
/// <summary>
/// 新客户资料操作
/// 批量分配
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ApiLog("Crm_NewClientData", OperationEnum.BatchAssignment)]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> PostBatchAssignment(BatchAssignmentDto dto)
{
#region 参数验证
if (dto.UserId < 0)
{
return Ok(JsonView(false, "请传入有效的UserId参数！"));
}
if (dto.PortType < 0)
{
return Ok(JsonView(false, "请传入有效的PortType参数！"));
}
#endregion
var res = await _clientDataRepository._BatchAssignment(dto);
if (res.Code != 0)
{
return Ok(JsonView(false, res.Msg));
}
return Ok(JsonView(true, "操作成功！"));
}
private Dictionary<int, List<int>> MCRSetting()
{
Dictionary<int, List<int>> keyValuePairs = new Dictionary<int, List<int>>();
keyValuePairs.Add(419, new List<int>() //四川
{
376,377,378,381,382,387,388,389,390,753,754,1296,1303
});
keyValuePairs.Add(420, new List<int>() //云南
{
407,408,409,410,449,451,452,453,567,754,1297,1304
});
keyValuePairs.Add(421, new List<int>() { 424, 425, 426, 427, 428, 429, 754,1298, 1305,1307,1306 }); // 贵州
keyValuePairs.Add(422, new List<int>() { 415, 416, 754,1317,1316,1315, 455,1321, 1320, 1319, 1318 }); // 西藏
keyValuePairs.Add(423, new List<int>() { 417, 418, 454, 456, 754, 1313, 1314 }); // 重庆
keyValuePairs.Add(578, new List<int>() { 581, 582, 583, 754,1299,1300 }); // 青海
keyValuePairs.Add(605, new List<int>() { 588, 589, 590, 591, 592, 593, 754,1301,1310,1309,1308 }); // 陕西
keyValuePairs.Add(606, new List<int>() { 597, 598, 599, 600, 601, 602, 603, 604, 754,1324,1323,1322 }); // 宁夏
keyValuePairs.Add(625, new List<int>() { 617, 618, 619, 620, 621, 622, 622, 623, 624, 754,1302,1312,1311 }); // 甘肃
keyValuePairs.Add(634, new List<int>() { 630, 631, 632, 633, 754 }); // 新疆
return keyValuePairs;
}
private Dictionary<int, List<int>> MCRSecondSetting()
{
Dictionary<int, List<int>> keyValuePairs = new Dictionary<int, List<int>>();
// 四川
keyValuePairs.Add(349, new List<int>() { 376, 378, 381, 387, 389, 1296 }); // 四川省级
keyValuePairs.Add(350, new List<int>() { 377, 382, 388, 390, 1303 }); // 成都市级
keyValuePairs.Add(348, new List<int>() { 387 }); // 四川地市州 - Explicitly assigning to ensure all source2 IDs are used
keyValuePairs.Add(701, new List<int>() { 388 }); // 成都区市县
// 云南
keyValuePairs.Add(399, new List<int>() { 407, 408, 409, 410, 451 }); // 云南省级
keyValuePairs.Add(400, new List<int>() { 449, 452, 453, 1297, 1304 }); // 昆明市级
keyValuePairs.Add(450, new List<int>() { 410 }); // 云南地市州
keyValuePairs.Add(566, new List<int>() { 567 }); // 昆明区市县
// 贵州
keyValuePairs.Add(401, new List<int>() { 424, 426, 427, 428, 1298 }); // 贵州省级
keyValuePairs.Add(402, new List<int>() { 425, 429, 1306, 1307, 1305 }); // 贵阳市级
keyValuePairs.Add(414, new List<int>() { 428 }); // 贵州地市州
//keyValuePairs.Add(1305, new List<int>() { /* Add relevant IDs if available */ }); // 贵阳区市县 - Placeholder for now
// 重庆
keyValuePairs.Add(403, new List<int>() { 417, 418, 454, 456, 1314 }); // 重庆市级
keyValuePairs.Add(404, new List<int>() { 1313 }); // 重庆区县 - No direct match in source1
// 西藏
keyValuePairs.Add(405, new List<int>() { 415, 1317, 1316, 1315 }); // 西藏自治区
keyValuePairs.Add(406, new List<int>() { 455, 1321, 1320, 1319, 1318 }); // 拉萨市级
keyValuePairs.Add(413, new List<int>() { 416 }); // 西藏地市州
// 青海
keyValuePairs.Add(579, new List<int>() { 581, 582, 583, 1300 }); // 青海省级
keyValuePairs.Add(580, new List<int>() { 1299 }); // 青海市州县 (Includes 地市州)
// 陕西
keyValuePairs.Add(585, new List<int>() { 588, 589, 590, 591, 1301 }); // 陕西省级
keyValuePairs.Add(586, new List<int>() { 589 }); // 陕西地市州
keyValuePairs.Add(587, new List<int>() { 592, 593, 1309, 1310 , 1308 }); // 西安市级
//keyValuePairs.Add(1308, new List<int>() { /* Add relevant IDs if available */ }); // 西安区市县 - Placeholder
// 宁夏
keyValuePairs.Add(594, new List<int>() { 597, 598, 599, 600, 601, 1322 }); // 宁夏自治区 (Includes 地市州 and 区级)
keyValuePairs.Add(595, new List<int>() { 597 }); // 宁夏地市州
keyValuePairs.Add(596, new List<int>() { 598, 599, 600, 603, 604, 602, 1323, 1324 }); // 银川市级 (Includes 区级)
//keyValuePairs.Add(602, new List<int>()); // 银川区市县 - No direct match, but could be considered covered by "区级"
// 甘肃
keyValuePairs.Add(614, new List<int>() { 617, 618, 619, 620, 621, 1302 }); // 甘肃省级
keyValuePairs.Add(615, new List<int>() { 618 }); // 甘肃地市州
keyValuePairs.Add(616, new List<int>() { 622, 623, 624, 1312, 1311 }); // 兰州市级
//keyValuePairs.Add(1311, new List<int>() { /* Add relevant IDs if available */ }); // 兰州区市县 - Placeholder
// 新疆
keyValuePairs.Add(627, new List<int>() { 630, 631, 632 , 1327,1326 ,1325 }); // 新疆自治区级
keyValuePairs.Add(628, new List<int>() { 1326 }); // 新疆地市州 - No direct match
keyValuePairs.Add(629, new List<int>() { 633,1331,1330,1329 ,1328 }); // 乌鲁木齐市级
// 中央直属企业 (Special case)
keyValuePairs.Add(351, new List<int>() { 754 }); // Using "未分级" for 中央直属企业
return keyValuePairs;
}
private Dictionary<int, List<int>> MCRLvSetting()
{
var keyValuePairs = new Dictionary<int, List<int>>();
// 四川 (419)
keyValuePairs.Add(419, new List<int>() { 348, 349, 350, 701 }); // Includes all Sichuan-related IDs from source2
// 云南 (420)
keyValuePairs.Add(420, new List<int>() { 399, 400, 450, 566 }); // Includes all Yunnan-related IDs
// 贵州 (421)
keyValuePairs.Add(421, new List<int>() { 401, 402, 414 });
// 西藏 (422)
keyValuePairs.Add(422, new List<int>() { 405, 406, 413 });
// 重庆 (423)
keyValuePairs.Add(423, new List<int>() { 403, 404 });
// 青海 (578)
keyValuePairs.Add(578, new List<int>() { 579, 580, 626 }); // Includes 市州县 and 西宁市级
// 陕西 (605)
keyValuePairs.Add(605, new List<int>() { 585, 586, 587 });
// 宁夏 (606)
keyValuePairs.Add(606, new List<int>() { 594, 595, 596 });
// 甘肃 (625)
keyValuePairs.Add(625, new List<int>() { 614, 615, 616 });
// 新疆 (634)
keyValuePairs.Add(634, new List<int>() { 627, 628, 629 });
// 未分级 (351) - You'll likely want to remove this as it's no longer a primary key.
// keyValuePairs.Add(351, new List<int>()); //Remove this line.
return keyValuePairs;
}
[HttpPost]
public IActionResult QueryClientType(QueryClientTypeDto Dto)
{
var jw = JsonView(true,"获取成功！");
var dic_lv = MCRLvSetting();
var keyValuePairs = MCRSetting();
ArrayList arr = new ArrayList();
ArrayList lvArr = new ArrayList();
var ids = new List<int>();
var lvids = new List<int>();
foreach (var item in Dto.SetDataIdArr)
{
if (keyValuePairs.Keys.Contains(item))
{
ids.AddRange(keyValuePairs[item]);
}
if (dic_lv.Keys.Contains(item))
{
lvids.AddRange(dic_lv[item]);
}
}
arr.AddRange(_sqlSugar.Queryable<Sys_SetData>()
.Where(u => ids.Contains(u.Id) && u.IsDel == 0)
.Select(x => new { x.Id, x.Name , x.Remark })
.ToList());
if (arr.Count == 0)
{
arr.AddRange(_sqlSugar.Queryable<Sys_SetData>()
.Where(u => u.STid == 37 && u.IsDel == 0)
.Select(x => new { x.Id, x.Name, x.Remark }).ToList());
}
// 创建比较器实例
IComparer remakeComparer = new RemakeComparer();
// 使用ArrayList.Sort方法和自定义比较器对arr进行排序
arr.Sort(remakeComparer);
var rangelv = _sqlSugar.Queryable<Sys_SetData>()
.Where(u => lvids.Contains(u.Id) && u.IsDel == 0)
.Select(x => new { x.Id, x.Name })
.ToList();
lvArr.AddRange(rangelv);
if (lvArr.Count == 0)
{
lvArr.AddRange(_sqlSugar.Queryable<Sys_SetData>()
.Where(u => u.STid == 33 && u.IsDel == 0)
.Select(x => new { x.Id, x.Name}).ToList());
}
jw.Data = new
{
TypeArr = arr,
LvArr = lvArr,
};
return Ok(jw);
}
[HttpPost]
public IActionResult QueryClientTypeSecond(QueryClientTypeDto Dto)
{
var jw = JsonView(true, "获取成功！");
var keyValuePairs = MCRSecondSetting();
ArrayList arr = new ArrayList();
var ids = new List<int>();
foreach (var item in Dto.SetDataIdArr)
{
if (keyValuePairs.Keys.Contains(item))
{
ids.AddRange(keyValuePairs[item]);
}
}
arr.AddRange(_sqlSugar.Queryable<Sys_SetData>()
.Where(u => ids.Contains(u.Id) && u.IsDel == 0)
.Select(x => new { x.Id, x.Name, x.Remark })
.ToList());
if (arr.Count == 0)
{
arr.AddRange(_sqlSugar.Queryable<Sys_SetData>()
.Where(u => u.STid == 37 && u.IsDel == 0)
.Select(x => new { x.Id, x.Name, x.Remark }).ToList());
}
// 创建比较器实例
IComparer remakeComparer = new RemakeComparer();
// 使用ArrayList.Sort方法和自定义比较器对arr进行排序
arr.Sort(remakeComparer);
jw.Data = arr;
return Ok(jw);
}
/// <summary>
/// 客户资料数据 excel 下载
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ApiLog("Crm_NewClientData", OperationEnum.Download)]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> NewClientDataExcelDownload(NewClientDataExcelDownloadDto dto)
{
#region 参数验证
if (dto.OperationUserId < 0) return Ok(JsonView(false, "请传入有效的OperationUserId参数！"));
if (dto.PortType < 0) return Ok(JsonView(false, "请传入有效的PortType参数！"));
if (dto.PageId < 0) return Ok(JsonView(false, "请传入有效的PageId参数！"));
#endregion
#region 页面操作权限验证
var pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(dto.OperationUserId, dto.PageId);
if (pageFunAuthView.FilesDownloadAuth == 0) return Ok(JsonView(false, "您没有文件下载权!"));
#endregion
var dt = await _clientDataRepository.NewClientDataExcelDownload(dto);
if (dt != null)
{
string tempPath = (AppSettingsHelper.Get("ExcelBasePath") + "Template/公司客户资料导出模板.xlsx");
var designer = new WorkbookDesigner();
designer.Workbook = new Workbook(tempPath);
designer.SetDataSource("NCDDT", dt);
designer.Process();
//文件名
string fileName = $"公司客户资料{DateTime.Now.ToString("yyyyMMddHHmmss")}.xls";
designer.Workbook.Save(AppSettingsHelper.Get("ExcelBasePath") + "NewClientDataExcelDownload/" + fileName);
string url = AppSettingsHelper.Get("ExcelBaseUrl") + "Office/Excel/NewClientDataExcelDownload/" + fileName;
#region 客户资料表操作记录
var respose = JsonView(true, "成功", url);
//var paramData = new
//{
// APIName = @"MarketCustomerResources/NewClientDataExcelDownload",
// RequestParam = dto,
// ResposeParam = respose
//};
//await GeneralMethod.NewClientOperationRecord(dto.PortType, OperationEnum.Download, dto.OperationUserId, 0, JsonConvert.SerializeObject(paramData));
#endregion
return Ok(respose);
}
return Ok(JsonView(false));
}
/// <summary>
/// 客户资料数据 历史记录 Init
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> NewClientDataRecordInit(NewClientDataRecordInitDto dto)
{
#region 参数验证
if (dto.UserId < 0) return Ok(JsonView(false, MsgTips.UserId));
if (!SharingStaticData.PortTypes.Contains(dto.PortType)) return Ok(JsonView(false, MsgTips.Port));
if (dto.PageId < 0) return Ok(JsonView(false, MsgTips.PageId));
#endregion
#region 页面操作权限验证
var pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(dto.UserId, dto.PageId);
if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限!"));
#endregion
var enumfiltrates = new string[] { "NoOperation", "Audit", "Login" };
var operations = EnumHelper.GetEnumDescriptions(typeof(OperationEnum), enumfiltrates);
var operation1 = new List<dynamic>();
foreach (var item in operations)
{
operation1.Add(new { value = item.Key, text = item.Value });
}
object userDatas = Array.Empty<object>();
if (dto.UserId == 21)
{
var userIds = await _sqlSugar.Queryable<Crm_ClientDataAndUser>().Where(x => x.IsDel == 0).Select(x => x.usersId).Distinct().ToListAsync();
if (userIds.Any())
{
userDatas = await _sqlSugar.Queryable<Sys_Users>().Where(x => x.IsDel == 0 && userIds.Contains(x.Id)).Select(x => new { value = x.Id, text = x.CnName }).ToListAsync();
}
}
var data = new {
operations = operation1,
userDatas = userDatas
};
return Ok(JsonView(data));
}
/// <summary>
/// 客户资料数据 历史记录
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> NewClientDataRecord(NewClientDataRecordDto dto)
{
#region 参数验证
if (dto.UserId < 0) return Ok(JsonView(false, MsgTips.UserId));
if (!SharingStaticData.PortTypes.Contains(dto.PortType)) return Ok(JsonView(false, MsgTips.Port));
if (dto.PageId < 0) return Ok(JsonView(false, MsgTips.PageId));
#endregion
#region 页面操作权限验证
var pageFunAuthView = await GeneralMethod.PostUserPageFuncDatas(dto.UserId, dto.PageId);
if (pageFunAuthView.CheckAuth == 0) return Ok(JsonView(false, "您没有查看权限!"));
#endregion
return Ok(await _clientDataRepository.NewClientDataRecord(dto));
}
#region 修改数据
//[HttpPost]
//public IActionResult SynchronizationData()
//{
// var keyValuePairs = new Dictionary<int, List<string>>
// {
// { 5, new List<string> { "省级部门", "省级单位" } },
// { 10, new List<string> { "市州" } },
// { 15, new List<string> { "省属国企" } },
// { 20, new List<string> { "省属学校", "省属高校" } },
// { 25, new List<string> { "省级行业协会", "省级社团", "省级联合会" , "省属行业协会", "民营企业", "地方国企" } },
// { 30, new List<string> { "市级部门", "市级单位" } },
// { 35, new List<string> { "区市县" } },
// { 40, new List<string> { "市级国企", "市属国企" } },
// { 45, new List<string> { "市属学校", "市属高校" } },
// { 50, new List<string> { "市级行业协会", "市级社团", "市级联合会" } }
// };
// var setting = MCRSetting();
// var arr = new List<List<Sys_SetData>>();
// foreach (var item in setting.Keys)
// {
// var setDataList_DB = _sqlSugar.Queryable<Sys_SetData>()
// .Where(x => x.IsDel == 0 && setting[item].Contains(x.Id))
// .ToList();
// foreach (var setData in setDataList_DB)
// {
// foreach (var lv in keyValuePairs.Keys)
// {
// var islv = keyValuePairs[lv].Where(x => setData.Name.Contains(x)).Count() > 0;
// if (islv)
// {
// setData.Remark = lv.ToString();
// }
// }
// }
// arr.Add(setDataList_DB);
// }
// _sqlSugar.Updateable(arr.SelectMany(x => x).ToList()).UpdateColumns(x => new
// {
// x.Remark
// }).ExecuteCommand();
// return Ok(arr);
//}
[HttpPost]
public IActionResult QueryData()
{
var db = _sqlSugar.Queryable<Crm_NewClientData>()
.Where(x => x.IsDel == 0)
.Select(x => new Crm_NewClientData
{
Id = x.Id,
Client = x.Client,
Category = x.Category,
Location = x.Location,
}).ToList();
var count = 0;
var updateid = new List<int>();
foreach (var item in db)
{
EncryptionProcessor.DecryptProperties(item);
if (!string.IsNullOrWhiteSpace(item.Location) && item.Location.Contains("重庆") && item.Category == 0)
{
count++;
//if (!string.IsNullOrWhiteSpace(item.Client) && (item.Client.Contains("大学") || item.Client.Contains("学院")))
{
updateid.Add(item.Id);
}
}
}
var updateCount = _sqlSugar.Updateable<Crm_NewClientData>().Where(x => updateid.Contains(x.Id))
.SetColumns(x => new Crm_NewClientData { Category = 456 }).ExecuteCommand();
return Ok( new
{
notCount = count,
updateCount = updateCount
});
}
[HttpPost]
public IActionResult InsertData()
{
string sql = $@"SELECT * FROM [dbo].[Crm_NewClientData] WHERE ID IN (
--所有四川省域的
select Id from [dbo].[Crm_NewClientData] where ISDEL = 0 AND Lvlid IN (
select S.Id from Sys_SetData S
where STid = 33 and isdel = 0 and
(Name like '%四%川%' or Name like '%成%都%')
)
) AND IsDel = 0
AND ID IN (
SELECT NewClientDataId FROM [dbo].[Crm_ClientDataAndUser]
WHERE NewClientDataId IN (
--所有单独数据
SELECT NewClientDataId FROM [dbo].[Crm_ClientDataAndUser]
WHERE ISDEL = 0
GROUP BY NewClientDataId
HAVING COUNT(NewClientDataId) = 1
) AND IsDel = 0 AND UsersId = 21
)";
var clientList = _sqlSugar.SqlQueryable<Crm_NewClientData>(sql).Select(x => x.Id).ToList().Select(x => new Crm_ClientDataAndUser
{
NewClientDataId = x,
usersId = 330,
CreateTime = DateTime.Now,
CreateUserId = 235,
IsDel = 0,
}).ToList();
var insertCount = _sqlSugar.Insertable(clientList).ExecuteCommand();
return Ok(insertCount);
}
[HttpPost]
public IActionResult ExportData()
{
var soure = _sqlSugar.Queryable<Crm_NewClientData>()
.InnerJoin<Crm_ClientDataAndUser>((a, b) => b.NewClientDataId == a.Id && b.IsDel == 0)
.Where((a, b) => a.IsDel == 0 && b.usersId == 95)
.Select((a, b) => a)
.ToList();
foreach (var item in soure)
{
EncryptionProcessor.DecryptProperties(item);
}
// 创建WorkbookDesigner对象
WorkbookDesigner designer = new WorkbookDesigner();
// 加载模板文件（包含智能标记）
designer.Workbook = new Workbook("C:\\Users\\PC\\Desktop\\111.xlsx");
// 设置数据源，这里假设你的List集合名为listData
designer.SetDataSource("ListData", soure);
// 处理智能标记以将数据填充到工作表中
designer.Process();
// 保存Excel文件
designer.Workbook.Save("C:\\Users\\PC\\Desktop\\111000.xlsx");
return Ok(1);
}
[HttpPost]
public IActionResult DeleteData()
{
var soure = _sqlSugar.Queryable<Crm_NewClientData>()
.InnerJoin<Crm_ClientDataAndUser>((a, b) => b.NewClientDataId == a.Id && b.IsDel == 0)
.Where((a, b) => a.IsDel == 0 && b.usersId == 330)
.Select((a, b) => b.Id)
.ToList();
var count = _sqlSugar.Updateable<Crm_ClientDataAndUser>()
.Where(u => soure.Contains(u.Id) && u.IsDel == 0)
.SetColumns(x => new Crm_ClientDataAndUser
{
DeleteTime = DateTime.Now.ToString(),
DeleteUserId = 235,
IsDel = 1
}).ExecuteCommand();
return Ok(count);
}
[HttpPost]
public IActionResult ReassignCustomers()
{
var user_Client = _sqlSugar.Queryable<Crm_ClientDataAndUser>()
.Where(x => x.usersId == 302 && x.IsDel == 0)
.Select(x=>new
{
x.Id,
x.NewClientDataId
})
.ToList();
var ids = user_Client.Select(x=>x.NewClientDataId).ToList();
string setDataSql = $"select * from Sys_SetData where STid = 33 and isdel = 0 and (Name like '%重庆%') ";
var setDataids = _sqlSugar.SqlQueryable<Sys_SetData>(setDataSql).Select(x => x.Id).ToList();
var clients = _sqlSugar.Queryable<Crm_NewClientData>().Where(x => ids.Contains(x.Id) && setDataids.Contains(x.Lvlid) && x.IsDel == 0).ToList();
var clients_ids = clients.Select(x=>x.Id).ToList();
int updateCount = 0;
_sqlSugar.BeginTran();
if (clients.Count == 223)
{
updateCount = _sqlSugar.Updateable<Crm_ClientDataAndUser>()
.Where(x => x.usersId == 302 && clients_ids.Contains(x.NewClientDataId) && x.IsDel == 0)
.SetColumns( x => new Crm_ClientDataAndUser
{
usersId = 21
})
.ExecuteCommand();
}
if (updateCount == clients.Count)
{
_sqlSugar.CommitTran();
}
else
{
_sqlSugar.RollbackTran();
}
return Ok( new
{
count = clients.Count,
updateCount,
});
}
[HttpPost]
public IActionResult InsertDataJiang()
{
var list_DB = _sqlSugar.Queryable<Crm_NewClientData>().Where(x => x.IsDel == 0).ToList();
foreach (var item in list_DB)
{
EncryptionProcessor.DecryptProperties(item);
}
list_DB = list_DB.Where(x => !string.IsNullOrWhiteSpace(x.Client) &&(x.Client.Contains("学院") || x.Client.Contains("大学"))).ToList();
var arr = list_DB.Select(x => x.Id).ToList().Select(x => new Crm_ClientDataAndUser
{
NewClientDataId = x,
usersId = 327,
CreateTime = DateTime.Now,
CreateUserId = 235,
IsDel = 0,
}).ToList();
var insertCount = _sqlSugar.Insertable(arr).ExecuteCommand();
return Ok(insertCount);
}
#endregion
/// <summary>
/// 客户资料 操作记录记录查询
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
[HttpGet]
[ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
public async Task<IActionResult> NewClientDataOperatingRrecord(int userId)
{
var datas = await _sqlSugar.Queryable<Crm_TableOperationRecord, Crm_NewClientData, Sys_Users>((tor, ncd, u) =>
new JoinQueryInfos(
JoinType.Left, tor.DataId == ncd.Id,
JoinType.Left, tor.CreateUserId == u.Id
))
.Where((tor, ncd, u) => tor.TableName == "Crm_NewClientData" && tor.CreateUserId == userId && (int)tor.OperationItem > 1)
.Select((tor, ncd, u) => new
{
tor.PortType,
tor.OperationItem,
tor.DataId,
ncd.Client,
u.CnName,
tor.CreateTime
}).ToListAsync();
var view = new List<dynamic>();
if (datas.Any())
{
datas = datas.OrderByDescending(x => x.CreateTime).ToList();
foreach (var item in datas)
{
var text = item.OperationItem.GetDescription();
view.Add(new
{
Client = AesEncryptionHelper.Decrypt(item.Client),
item.PortType,
item.OperationItem,
text = text,
item.DataId,
item.CnName,
item.CreateTime
});
}
}
return Ok(JsonView(view));
}
}
}