1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066 |
- 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>
- /// 客户资料数据 分权限导出 excel 下载
- /// </summary>
- /// <param name="dto"></param>
- /// <returns></returns>
- [HttpPost]
- [ApiLog("Crm_NewClientData", OperationEnum.Download)]
- [ProducesResponseType(typeof(JsonView), StatusCodes.Status200OK)]
- public async Task<IActionResult> NewClientDataAuthorityExcelDownload(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));
- }
- }
- }
|