public DataResult <dynamic> TryGetContactInfo(dynamic model)
{
var data = new List <CoreResumeSummary>();
using (var db = new BadoucaiAliyunDBEntities())
{
var sb = new StringBuilder();
sb.Append($"SELECT * FROM \"Core_Resume_Summary\" WHERE substring(\"Cellphone\"::VARCHAR(32),8,4) = '{model.CellphonePart}' AND \"UpdateTime\" > '1970-01-01 08:00:00' ");
if (!string.IsNullOrEmpty((string)model.Birthday))
{
sb.Append($"AND \"Birthday\" = '{((string)model.Birthday).Remove(((string)model.Birthday).LastIndexOf("-", StringComparison.Ordinal))}-01' ");
}
if (!string.IsNullOrEmpty((string)model.Degree))
{
sb.Append($"AND \"Degree\" = '{degreeDic[(string)model.Degree]}' ");
}
if (!string.IsNullOrEmpty((string)model.Gender))
{
var gender = model.Gender == "男" ? "M" : "F";
sb.Append($"AND \"Gender\" = '{gender}' ");
}
sb.Append(";");
var list = db.Database.SqlQuery <CoreResumeSummary>(sb.ToString()).ToList();
using (var aif = new AIFDBEntities())
{
foreach (var item in list)
{
var address = aif.BaseAreaBDC.AsNoTracking().FirstOrDefault(f => f.Id == item.CurrentResidence);
if (address == null)
{
continue;
}
if (string.IsNullOrEmpty((string)model.CurrentResidence) || ((string)model.CurrentResidence).Contains(address.Name))
{
data.Add(item);
}
}
}
}
return(new DataResult <dynamic>(data.Take(5)));
}
public void StatisticsByArea()
{
var cityDictionary = new Dictionary <int, string>
{
#region 市
{ 90287536, "北京市" },
{ 90487536, "上海市" },
{ 90617636, "广州市" },
{ 90617836, "深圳市" },
{ 90517636, "合肥市" },
{ 90677536, "重庆市" },
{ 90527636, "福州市" },
{ 90797636, "兰州市" },
{ 90697636, "贵阳市" },
{ 90637636, "海口市" },
{ 90307636, "石家庄市" },
{ 90587636, "郑州市" },
{ 90407636, "哈尔滨市" },
{ 90597636, "武汉市" },
{ 90607636, "长沙市" },
{ 90397636, "长春市" },
{ 90497636, "南京市" },
{ 90537636, "南昌市" },
{ 90387636, "沈阳市" },
{ 90807636, "西宁市" },
{ 90547636, "济南市" },
{ 90317636, "太原市" },
{ 90787636, "西安市" },
{ 90687636, "成都市" },
{ 90297536, "天津市" },
{ 90707636, "昆明市" },
{ 90507636, "杭州市" },
{ 90627636, "南宁市" },
{ 90327636, "呼和浩特市" },
{ 90817636, "银川市" },
{ 90717636, "拉萨市" },
{ 90827636, "乌鲁木齐市" },
{ 90997536, "澳门特别行政区" },
{ 90987536, "香港特别行政区" },
#endregion
};
var provinceDictionary = new Dictionary <int, string>
{
#region 省
{ 90617536, "广东省" },
{ 90517536, "安徽省" },
{ 90527536, "福建省" },
{ 90797536, "甘肃省" },
{ 90697536, "贵州省" },
{ 90637536, "海南省" },
{ 90307536, "河北省" },
{ 90587536, "河南省" },
{ 90407536, "黑龙江省" },
{ 90597536, "湖北省" },
{ 90607536, "湖南省" },
{ 90397536, "吉林省" },
{ 90497536, "江苏省" },
{ 90537536, "江西省" },
{ 90387536, "辽宁省" },
{ 90807536, "青海省" },
{ 90547536, "山东省" },
{ 90317536, "山西省" },
{ 90787536, "陕西省" },
{ 90687536, "四川省" },
{ 90707536, "云南省" },
{ 90507536, "浙江省" },
{ 90627536, "广西壮族自治区" },
{ 90327536, "内蒙古自治区" },
{ 90817536, "宁夏回族自治区" },
{ 90717536, "西藏自治区" },
{ 90827536, "新疆维吾尔自治区" }
#endregion
};
var sb = new StringBuilder();
foreach (var area in cityDictionary)
{
var count = 0;
using (var bdb = new BadoucaiAliyunDBEntities())
{
bdb.Database.CommandTimeout = 600;
count += bdb.CoreResumeSummary.AsNoTracking().Count(c => c.CurrentResidence == area.Key);
using (var adb = new AIFDBEntities())
{
var areaList = adb.BaseAreaBDC.AsNoTracking().Where(w => w.PId == area.Key).ToList();
if (areaList.Count == 1)
{
var pid = areaList[0].Id;
areaList = adb.BaseAreaBDC.AsNoTracking().Where(w => w.PId == pid).ToList();
}
count += areaList.Sum(item => bdb.CoreResumeSummary.AsNoTracking().Count(c => c.CurrentResidence == item.Id));
}
}
sb.AppendLine($"{area.Value}\t{count}");
}
foreach (var province in provinceDictionary)
{
var count = 0;
using (var adb = new AIFDBEntities())
{
var cityList = adb.BaseAreaBDC.AsNoTracking().Where(w => w.PId == province.Key).ToList();
if (cityList.Count == 1)
{
var pid = cityList[0].Id;
cityList = adb.BaseAreaBDC.AsNoTracking().Where(w => w.PId == pid).ToList();
}
foreach (var area in cityList)
{
using (var bdb = new BadoucaiAliyunDBEntities())
{
count += bdb.CoreResumeSummary.AsNoTracking().Count(c => c.CurrentResidence == area.Id);
var areaList = adb.BaseAreaBDC.AsNoTracking().Where(w => w.PId == area.Id).ToList();
if (areaList.Count == 1)
{
var pid = areaList[0].Id;
areaList = adb.BaseAreaBDC.AsNoTracking().Where(w => w.PId == pid).ToList();
}
count += areaList.Sum(item => bdb.CoreResumeSummary.AsNoTracking().Count(c => c.CurrentResidence == item.Id));
}
}
}
sb.AppendLine($"{province.Value}\t{count}");
}
File.AppendAllText(@"D:\统计.txt", sb.ToString());
}
