public List <CardCommon> GetApprovalList()
{
var card = new List <CardCommon>();
string sql = "sproc_agent_cardMgmt @flag ='l'";
var dUser = DAO.ExecuteDataTable(sql);
if (dUser != null)
{
foreach (DataRow dr in dUser.Rows)
{
CardCommon cardCommon = new CardCommon();
cardCommon.RequestId = dr["req_id"].ToString();
cardCommon.UserName = dr["user_name"].ToString();
cardCommon.MobileNo = dr["user_mobile_no"].ToString();
cardCommon.Email = dr["user_email"].ToString();
cardCommon.CardType = dr["card_type"].ToString();
cardCommon.RequestStatus = dr["request_status"].ToString();
cardCommon.CreatedLocalDate = dr["created_local_date"].ToString();
cardCommon.Amount = dr["requested_amount"].ToString();
//cardCommon.CreatedBy = dr["created_by"].ToString();
//cardCommon.CreatedBy = dr["created_ip"].ToString();
//cardCommon.UpdatedLocalDate = dr["updated_local_date"].ToString();
//cardCommon.UpdatedUTCDate = dr["updated_UTC_Date"].ToString();
//cardCommon.UpdatedBy = dr["updated_by"].ToString();
//cardCommon.UpdatedIp = dr["updated_ip"].ToString();
card.Add(cardCommon);
}
}
return(card);
}
public List <CardCommon> GetCardList(string UserId)
{
var card = new List <CardCommon>();
string sql = "sproc_agent_cardMgmt @flag ='s',@user_id= " + DAO.FilterString(UserId);
var dUser = DAO.ExecuteDataTable(sql);
if (dUser != null)
{
foreach (DataRow dr in dUser.Rows)
{
CardCommon cardCommon = new CardCommon();
cardCommon.AgentId = dr["agent_id"].ToString();
cardCommon.AgentName = dr["agent_name"].ToString();
cardCommon.UserId = dr["user_id"].ToString();
cardCommon.Email = dr["user_email"].ToString();
cardCommon.MobileNo = dr["user_mobile_no"].ToString();
cardCommon.CardId = dr["card_id"].ToString();
cardCommon.CardNo = dr["card_no"].ToString();
cardCommon.CardType = dr["card_type"].ToString();
cardCommon.IssueDate = dr["card_issued_date"].ToString();
cardCommon.ExpiryDate = dr["card_expiry_date"].ToString();
cardCommon.Status = dr["is_active"].ToString();
cardCommon.FullName = dr["full_name"].ToString();
cardCommon.Amount = dr["available_balance"].ToString();
cardCommon.IsReceived = dr["is_transfer"].ToString();
cardCommon.ReceivedFrom = dr["transfer_to"].ToString();
card.Add(cardCommon);
}
}
return(card);
}
public ActionResult CardUpdate(CardModel cardModel)
{
ModelState.Remove("Email");
ModelState.Remove("MobileNo");
ModelState.Remove("IssueDate");
ModelState.Remove("Amount");
CommonDbResponse dbResponse = new CommonDbResponse();
if (ModelState.IsValid)
{
//cardModel.ActionUser = Session["UserName"].ToString();
CardModel card = new CardModel();
card.UserId = cardModel.UserId.DecryptParameter();
card.AgentId = cardModel.AgentId.DecryptParameter();
card.CardNo = cardModel.CardNo.DecryptParameter();
card.CardType = cardModel.CardType;
//cardModel.UserName = Session["CardForUser"].ToString();
CardCommon cardCommon = card.MapObject <CardCommon>();
dbResponse = _card.UpdateCard(cardCommon);
//if (dbResponse.Code == 0)
//{
// dbResponse.SetMessageInTempData(this);
// return RedirectToAction("Index", new { UserId = cardModel.UserId, AgentId = cardModel.AgentId });
//}
dbResponse.SetMessageInTempData(this);
return(RedirectToAction("Index", new { UserId = cardModel.UserId, AgentId = cardModel.AgentId }));
}
dbResponse = new CommonDbResponse {
Code = ResponseCode.Failed, Message = "Invalid Card Info"
};
dbResponse.SetMessageInTempData(this);
return(RedirectToAction("Index", new { UserId = cardModel.UserId, AgentId = cardModel.AgentId }));
}
public CommonDbResponse EnableDisableCard(CardCommon cardCommon)
{
string sql = "sproc_agent_cardMgmt @flag = 'e'";
sql += ", @user_id=" + DAO.FilterString(cardCommon.UserId);
sql += ", @card_no=" + DAO.FilterString(cardCommon.CardNo);
return(DAO.ParseCommonDbResponse(sql));
}
public ActionResult RequestCard(CardModel cardModels)
{
int ReqAmount = 0;
float AvaBalnce = 0;
cardModels.Balance = Session["Balance"].ToString();
//CardModel cardModel = new CardModel();
var cardType = _card.GetCardType();
foreach (var item in cardType.Where(kvp => kvp.Value.ToUpper() == "VIRTUAL CARD").ToList())
{
cardType.Remove(item.Key);
}
cardModels.CardTypeList = ApplicationUtilities.SetDDLValue(cardType as Dictionary <string, string>, "", "--Card Type--");
if (int.TryParse(cardModels.Amount, out ReqAmount))
{
if (ReqAmount < 100) //|| ReqAmount>1000
{
ModelState.AddModelError("Amount", "Amount should be at least RS. 100");
return(View(cardModels));
}
}
else
{
ModelState.AddModelError("Amount", "Invalid Requested amount.");
return(View(cardModels));
}
if (float.TryParse(cardModels.Balance, out AvaBalnce))
{
if (ReqAmount > AvaBalnce)
{
ModelState.AddModelError("Amount", "Requested amount greater then balance");
return(View(cardModels));
}
}
cardModels.UserId = Session["UserId"].ToString();
cardModels.AgentId = Session["AgentId"].ToString();
cardModels.UserName = Session["UserName"].ToString();
//cardModels.UserName = Session["FullName"].ToString();
WalletUserInfo walletUser = _walletUserBusiness.UserInfo(cardModels.UserId);
cardModels.Email = walletUser.Email;
cardModels.MobileNo = walletUser.MobileNo;
cardModels.ActionUser = cardModels.UserName;
cardModels.CreatedIp = ApplicationUtilities.GetIP();
CardCommon cardCommon = cardModels.MapObject <CardCommon>();
//CommonDbResponse dbResponse = _card.CardApproval(cardCommon);
CommonDbResponse dbResponse = _card.RequestCard(cardCommon);
if (dbResponse.Code == 0)
{
//dbResponse.SetMessageInTempData(this, "Index");
return(RedirectToAction("Index"));
}
dbResponse.SetMessageInTempData(this, "Index");
return(View(cardModels));
}
public CommonDbResponse CardBalance(CardCommon cardCommon)
{
string sql = "sproc_agent_cardMgmt";
sql += " @flag =" + DAO.FilterString(cardCommon.Type);
sql += ", @card_id=" + DAO.FilterString(cardCommon.CardNo);
sql += ", @requested_amount=" + DAO.FilterString(cardCommon.Amount);
return(DAO.ParseCommonDbResponse(sql));
}
public CommonDbResponse CardUser(CardCommon cardCommon)
{
string sql = "sproc_agent_cardMgmt";
sql += " @flag =" + DAO.FilterString(cardCommon.Type);
sql += ", @card_id=" + DAO.FilterString(cardCommon.CardNo);
sql += ", @transfer_to_mobile=" + DAO.FilterString(cardCommon.MobileNo);
//sql += string.IsNullOrEmpty(cardCommon.MobileNo) ? "" : ", @transfer_to_mobile=" + DAO.FilterString(cardCommon.MobileNo);
sql += ", @action_user=" + DAO.FilterString(cardCommon.ActionUser);
return(DAO.ParseCommonDbResponse(sql));
}
public CommonDbResponse InsertCard(CardCommon cardCommon)
{
string sql = "sproc_agent_cardMgmt @flag = 'i'";
sql += ", @agent_id=" + DAO.FilterString(cardCommon.AgentId);
sql += ", @user_id=" + DAO.FilterString(cardCommon.UserId);
sql += ", @user_name=" + DAO.FilterString(cardCommon.UserName);
sql += ", @card_type=" + DAO.FilterString(cardCommon.CardType);
sql += ", @action_user=" + DAO.FilterString(cardCommon.ActionUser);
return(DAO.ParseCommonDbResponse(sql));
}
public CommonDbResponse RequestCard(CardCommon cardCommon)
{
string sql = "sproc_agent_cardMgmt @flag = 'r'";
sql += ", @user_id=" + DAO.FilterString(cardCommon.UserId);
sql += ", @user_name=" + DAO.FilterString(cardCommon.UserName);
sql += ", @user_mobile_no=" + DAO.FilterString(cardCommon.MobileNo);
sql += ", @user_email=" + DAO.FilterString(cardCommon.Email);
sql += ", @action_user="******", @Created_ip=" + DAO.FilterString(cardCommon.CreatedIp);
sql += ", @card_type=" + DAO.FilterString(cardCommon.CardType);
sql += ", @requested_amount=" + DAO.FilterString(cardCommon.Amount);
return(DAO.ParseCommonDbResponse(sql));
}
public ActionResult CardBalance(CardModel cardModel)
{
int ReqAmount = 0;
float AvaBalnce = 0;
cardModel.Balance = Session["Balance"].ToString();
var cardType = _card.GetCardType();
foreach (var item in cardType.Where(kvp => kvp.Value.ToUpper() == "VIRTUAL CARD" || kvp.Value.ToUpper() == "GIFT CARD").ToList())
{
cardType.Remove(item.Key);
}
cardModel.CardTypeList = ApplicationUtilities.SetDDLValue(cardType as Dictionary <string, string>, "", "--Card Type--");
if (string.IsNullOrEmpty(cardModel.CardNo))
{
ModelState.AddModelError("CardNo", "Card Number Required!");
//return RedirectToAction("CardBalance");
return(View("CardBalance", cardModel));
}
if (int.TryParse(cardModel.Amount, out ReqAmount) && float.TryParse(cardModel.Balance, out AvaBalnce))
{
if (ReqAmount > AvaBalnce && cardModel.Type.ToLower() == "ad")
{
ModelState.AddModelError("Amount", "Requested amount greater then balance");
return(View("CardBalance", cardModel));
}
string userid = Session["UserId"].ToString();
if (string.IsNullOrEmpty(userid))
{
return(RedirectToAction("Index", "Home"));
}
var cardCommonList = _card.GetCardList(userid).Where(x => x.CardId == cardModel.CardNo);//MapObjects<CardModel>();
float AvailableCardBalance = 0;
if (float.TryParse(cardCommonList.FirstOrDefault().Amount, out AvailableCardBalance))
{
if (ReqAmount > AvailableCardBalance && cardModel.Type.ToLower() == "rb")
{
ModelState.AddModelError("Amount", "Requested amount greater then balance");
return(View("CardBalance", cardModel));
}
}
}
CardCommon cardCommon = cardModel.MapObject <CardCommon>();
//CommonDbResponse dbResponse = _card.CardApproval(cardCommon);
CommonDbResponse dbResponse = _card.CardBalance(cardCommon);
dbResponse.SetMessageInTempData(this, "Index");
return(RedirectToAction("Index"));
}
public CommonDbResponse CardApproval(CardCommon cardCommon)
{
string sql = "sproc_agent_cardMgmt @flag = 'a'";
sql += ", @user_id=" + DAO.FilterString(cardCommon.UserId);
sql += ", @user_name=" + DAO.FilterString(cardCommon.UserName);
sql += ", @agent_id=" + DAO.FilterString(cardCommon.AgentId);
sql += ", @card_type=" + DAO.FilterString(cardCommon.CardType);
//sql += ", @is_active=" + DAO.FilterString(cardCommon.Status);
sql += ", @action_user="******", @Created_ip=" + DAO.FilterString(cardCommon.CreatedIp);
sql += ", @req_id=" + DAO.FilterString(cardCommon.RequestId);
sql += ", @req_status=" + DAO.FilterString(cardCommon.RequestStatus);
return(DAO.ParseCommonDbResponse(sql));
}
public static List <CardInfo> GenCardList(int[] serverNums = null)
{
List <CardInfo> cardList;
// 从服务器发过来的牌，0~53
int[] _serverNums = { 47, 38, 10, 36, 12, 15, 35, 29, 32, 24, 0, 7, 21, 53, 8, 20, 27, 34, 23, 30 };
if (null != serverNums)
{
_serverNums = serverNums;
}
List <int> serverCards = new List <int>(_serverNums);
// 转换成我当前的手牌
cardList = CardCommon.GetCardListSorted(serverCards);
return(cardList);
}
public JsonResult CardEnable(string userid, string agentid, string cardno)
{
var data = new CommonDbResponse();
bool valid = true;
if (!String.IsNullOrEmpty(userid) || !String.IsNullOrEmpty(agentid))
{
userid = userid.DecryptParameter();
agentid = agentid.DecryptParameter();
if (string.IsNullOrEmpty(userid) || string.IsNullOrEmpty(agentid))
{
data = new CommonDbResponse {
Code = ResponseCode.Failed, Message = "Invalid User."
};
valid = false;
}
}
if (!String.IsNullOrEmpty(cardno))
{
cardno = cardno.DecryptParameter();
if (string.IsNullOrEmpty(cardno))
{
data = new CommonDbResponse {
Code = ResponseCode.Failed, Message = "Invalid Card"
};
valid = false;
}
}
if (valid)
{
CardCommon cardCommon = new CardCommon();
cardCommon.UserId = userid;
cardCommon.AgentId = agentid;
cardCommon.CardNo = cardno;
data = _card.EnableDisableCard(cardCommon);
if (data.ErrorCode == 0)
{
data.Message = "Successfully Enabled Card";
}
}
data.SetMessageInTempData(this);
return(Json(data));
}
public ActionResult AddCard(CardModel cardModel)
{
ModelState.Remove("Amount");
ModelState.Remove("CardNo");
if (ModelState.IsValid)
{
cardModel.ActionUser = Session["UserName"].ToString();
cardModel.UserId = cardModel.UserId.DecryptParameter();
cardModel.AgentId = cardModel.AgentId.DecryptParameter();
cardModel.UserName = Session["CardForUser"].ToString();
CardCommon cardCommon = cardModel.MapObject <CardCommon>();
CommonDbResponse dbResponse = _card.InsertCard(cardCommon);
if (dbResponse.Code == 0)
{
dbResponse.SetMessageInTempData(this);
return(RedirectToAction("Index", new { UserId = cardModel.UserId.EncryptParameter(), AgentId = cardModel.AgentId.EncryptParameter() }));
}
}
return(View(cardModel));
}
public ActionResult CardUser(CardModel cardModel)
{
cardModel.Balance = Session["Balance"].ToString();
var cardType = _card.GetCardType();
foreach (var item in cardType.Where(kvp => kvp.Value.ToUpper() == "VIRTUAL CARD" || kvp.Value.ToUpper() == "PREPAID CARD").ToList())
{
cardType.Remove(item.Key);
}
cardModel.CardTypeList = ApplicationUtilities.SetDDLValue(cardType as Dictionary <string, string>, cardModel.CardType, "--Card Type--");
if (cardModel.Type == "re")
{
ModelState.Remove("MobileNo");
}
if (string.IsNullOrEmpty(cardModel.CardNo))
{
ModelState.AddModelError("CardNo", "Card Number Required!");
return(View("CardUser", cardModel));
}
else
{
IWalletUserBusiness _walletUserBusiness = new WalletUserBusiness();
var userinfo = _walletUserBusiness.UserInfo(cardModel.MobileNo);
if (string.IsNullOrEmpty(userinfo.UserId))
{
ModelState.AddModelError("MobileNo", "Invalid Mobile Number");
return(View("CardUser", cardModel));
}
}
cardModel.ActionUser = Session["UserId"].ToString();
CardCommon cardCommon = cardModel.MapObject <CardCommon>();
//CommonDbResponse dbResponse = _card.CardApproval(cardCommon);
CommonDbResponse dbResponse = _card.CardUser(cardCommon);
dbResponse.SetMessageInTempData(this, "Index");
return(RedirectToAction("Index"));
}
public ActionResult CardApprovalDetail(CardModel cardModel, string btnApprove)
{
if (btnApprove.ToUpper() == "REJECT")
{
cardModel.RequestStatus = "Rejected";
}
if (btnApprove.ToUpper() == "APPROVE")
{
cardModel.RequestStatus = "Approved";
}
//Get user info from userName
WalletUserInfo walletUser = _walletUserBusiness.UserInfo(cardModel.UserName);
cardModel.UserId = walletUser.UserId;
cardModel.AgentId = walletUser.AgentId;
cardModel.ActionUser = Session["UserId"].ToString();
cardModel.CreatedIp = ApplicationUtilities.GetIP();
cardModel.CardType = _card.GetCardType().FirstOrDefault(x => x.Value == cardModel.CardType).Key;
CardCommon cardCommon = cardModel.MapObject <CardCommon>();
CommonDbResponse dbResponse = _card.CardApproval(cardCommon);
dbResponse.SetMessageInTempData(this);
return(RedirectToAction("CardApprovalList"));
}
/// <summary>
/// 社保专员提取报增数据
/// </summary>
/// <param name="SysEntities">数据访问的上下文</param>
/// <param name="page">页码</param>
/// <param name="rows">行数</param>
/// <returns></returns>
public List <EmployeeGoonPaymentView> GetEmployeeGoonPaymentExcelList(SysEntities db, int page, int rows, string search, ref int count)
{
using (var ent = new SysEntities())
{
try
{
#region 查询条件
var emp = ent.Employee.Where(a => true);
var empGoon = ent.EmployeeGoonPayment.Where(a => true);
var com = ent.CRM_Company.Where(a => true);
var city = ent.City.Where(a => true);
Dictionary <string, string> queryDic = ValueConvert.StringToDictionary(search.GetString());
if (queryDic != null && queryDic.Count > 0)
{
if (queryDic.ContainsKey("Name") && !string.IsNullOrWhiteSpace(queryDic["Name"]))
{
string str = queryDic["Name"];
emp = emp.Where(a => a.Name.Contains(str));
}
if (queryDic.ContainsKey("CertificateNumber") && !string.IsNullOrWhiteSpace(queryDic["CertificateNumber"]))
{
string[] cardIdList = queryDic["CertificateNumber"].Split(Convert.ToChar(10));
List <string> cardList = new List <string>();
for (int i = 0; i < cardIdList.Length; i++)
{
cardList.Add(cardIdList[i]);
cardList.Add(CardCommon.CardIDTo15(cardIdList[i]));
cardList.Add(CardCommon.CardIDTo18(cardIdList[i]));
}
cardList = cardList.Distinct().ToList();
emp = emp.Where(o => cardList.Contains(o.CertificateNumber));
}
if (queryDic.ContainsKey("State") && !string.IsNullOrWhiteSpace(queryDic["State"]))
{
string str = queryDic["State"];
string[] states = str.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
empGoon = empGoon.Where(a => states.Contains(a.State));
}
if (queryDic.ContainsKey("InsuranceKinds") && !string.IsNullOrWhiteSpace(queryDic["InsuranceKinds"]))
{
string str = queryDic["InsuranceKinds"];
int?[] Ids = Array.ConvertAll <string, int?>(str.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), delegate(string s) { return(int.Parse(s)); });
empGoon = empGoon.Where(a => Ids.Contains(a.EmployeeAdd.InsuranceKindId));
}
if (queryDic.ContainsKey("YearMonth") && !string.IsNullOrWhiteSpace(queryDic["YearMonth"]))
{
int str = Convert.ToInt32(queryDic["YearMonth"]);
empGoon = empGoon.Where(a => a.YearMonth == str);
}
if (queryDic.ContainsKey("CompanyName") && !string.IsNullOrWhiteSpace(queryDic["CompanyName"]))
{
string str = queryDic["CompanyName"];
com = com.Where(a => a.CompanyName.Contains(str));
}
if (queryDic.ContainsKey("CityId") && !string.IsNullOrWhiteSpace(queryDic["CityId"]))
{
string str = queryDic["CityId"];
city = city.Where(a => a.Id == str);
}
}
var list = (from a in emp
join r in ent.CompanyEmployeeRelation on a.Id equals r.EmployeeId
join b in empGoon on r.Id equals b.EmployeeAdd.CompanyEmployeeRelationId
join c in com on r.CompanyId equals c.ID
//join d in ent.InsuranceKind on b.InsuranceKindId equals d.Id
join e in city on r.CityId equals e.Id
join f in ent.PoliceAccountNature on b.EmployeeAdd.PoliceAccountNatureId equals f.Id
select new EmployeeGoonPaymentView()
{
CompanyEmployeeRelationId = b.EmployeeAdd.CompanyEmployeeRelationId,
CompanyId = r.CompanyId,
CompanyName = c.CompanyName,
Name = a.Name,
CertificateNumber = a.CertificateNumber,
City = e.Name,
// CityID = r.CityId,
PoliceAccountNatureName = f.Name,
YearMonth = (int)b.YearMonth,
CompanyCode = c.CompanyCode,
Station = r.Station
}).Distinct();
count = 0;
if (list != null && list.Count() >= 1)
{
count = list.Count();
if (page > -1)
{
list = list.OrderBy(a => a.CompanyId).ThenBy(a => a.CompanyEmployeeRelationId).Skip((page - 1) * rows).Take(rows);
}
}
List <EmployeeGoonPaymentView> appList = list.ToList();
//List<EmployeeAddView> appList1 = uuuu.ToList();
//将险种进行查询补充,将页面需要展示人员的新增险种信息全部查询并放入系统内存中;
var temp = (from b in empGoon
// join f in appList1 on b.Id equals f.Id
// join c in list on b.CompanyEmployeeRelationId equals c.CompanyEmployeeRelationId
join d in ent.PoliceInsurance on b.EmployeeAdd.PoliceInsuranceId equals d.Id
join e in ent.PoliceOperation on b.EmployeeAdd.PoliceOperationId equals e.Id
select new
{
b.Id,
b.EmployeeAdd.CompanyEmployeeRelationId,
b.EmployeeAdd.InsuranceKindId,
b.EmployeeAdd.PoliceInsuranceId,
b.YearMonth,
b.EmployeeAdd.InsuranceCode,
b.EmployeeAdd.Wage,
d.Name,
b.State,
b.StartTime,
b.EndTime,
PoliceOperationName = e.Name,
b.InsuranceMonth,
b.EmployeeAdd.IsIndependentAccount,
b.CreateTime,
PoliceAccountNaturename = b.EmployeeAdd.PoliceAccountNature.Name
}).ToList();
int comMark = 1;
int empMark = 2;
appList = (from a in appList
join bb1 in temp.Where(a => a.InsuranceKindId == 1) on a.CompanyEmployeeRelationId equals bb1.CompanyEmployeeRelationId into bt1
from b1 in bt1.DefaultIfEmpty()
join bb2 in temp.Where(a => a.InsuranceKindId == 2) on a.CompanyEmployeeRelationId equals bb2.CompanyEmployeeRelationId into bt2
from b2 in bt2.DefaultIfEmpty()
join bb3 in temp.Where(a => a.InsuranceKindId == 3) on a.CompanyEmployeeRelationId equals bb3.CompanyEmployeeRelationId into bt3
from b3 in bt3.DefaultIfEmpty()
join bb4 in temp.Where(a => a.InsuranceKindId == 4) on a.CompanyEmployeeRelationId equals bb4.CompanyEmployeeRelationId into bt4
from b4 in bt4.DefaultIfEmpty()
join bb5 in temp.Where(a => a.InsuranceKindId == 5) on a.CompanyEmployeeRelationId equals bb5.CompanyEmployeeRelationId into bt5
from b5 in bt5.DefaultIfEmpty()
join bb6 in temp.Where(a => a.InsuranceKindId == 6) on a.CompanyEmployeeRelationId equals bb6.CompanyEmployeeRelationId into bt6
from b6 in bt6.DefaultIfEmpty()
join bb7 in temp.Where(a => a.InsuranceKindId == 7) on a.CompanyEmployeeRelationId equals bb7.CompanyEmployeeRelationId into bt7
from b7 in bt7.DefaultIfEmpty()
join bb8 in temp.Where(a => a.InsuranceKindId == 8) on a.CompanyEmployeeRelationId equals bb8.CompanyEmployeeRelationId into bt8
from b8 in bt8.DefaultIfEmpty()
select new EmployeeGoonPaymentView()
{
CompanyEmployeeRelationId = a.CompanyEmployeeRelationId,
CompanyCode = a.CompanyCode,
CompanyName = a.CompanyName,
Name = a.Name,
CertificateNumber = a.CertificateNumber,
City = a.City,
PoliceAccountNatureName = a.PoliceAccountNatureName,
Station = a.Station,
AddIds = (b1 == null ? "" : b1.Id.ToString() + ",")
+ (b2 == null ? "" : b2.Id.ToString() + ",")
+ (b3 == null ? "" : b3.Id.ToString() + ",")
+ (b4 == null ? "" : b4.Id.ToString() + ",")
+ (b5 == null ? "" : b5.Id.ToString() + ",")
+ (b6 == null ? "" : b6.Id.ToString() + ",")
+ (b7 == null ? "" : b7.Id.ToString() + ",")
+ (b8 == null ? "" : b8.Id.ToString()),
InsuranceCode_1 = b1 == null ? null : b1.InsuranceCode,
CompanyNumber_1 = b1 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b1.PoliceInsuranceId, (decimal)b1.Wage, comMark),
CompanyPercent_1 = b1 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b1.PoliceInsuranceId, (decimal)b1.Wage, comMark),
EmployeeNumber_1 = b1 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b1.PoliceInsuranceId, (decimal)b1.Wage, empMark),
EmployeePercent_1 = b1 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b1.PoliceInsuranceId, (decimal)b1.Wage, empMark),
State_1 = b1 == null ? null : b1.State,
PoliceInsuranceName_1 = b1 == null ? null : b1.Name,
YearMonth_1 = b1 == null ? null : b1.YearMonth,
StartTime_1 = b1 == null ? null : b1.StartTime,
EndTime_1 = b1 == null ? null : b1.EndTime,
Wage_1 = b1 == null ? null : b1.Wage,
PoliceOperationName_1 = b1 == null ? null : b1.PoliceOperationName,
InsuranceMonth_1 = b1 == null ? null : b1.InsuranceMonth,
IsIndependentAccount_1 = b1 == null ? null : b1.IsIndependentAccount,
CreateTime_1 = b1 == null ? null : b1.CreateTime,
PoliceAccountNatureName_1 = b1 == null ? null : b1.PoliceAccountNaturename,
InsuranceCode_2 = b2 == null ? null : b2.InsuranceCode,
CompanyNumber_2 = b2 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b2.PoliceInsuranceId, (decimal)b2.Wage, comMark),
CompanyPercent_2 = b2 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b2.PoliceInsuranceId, (decimal)b2.Wage, comMark),
EmployeeNumber_2 = b2 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b2.PoliceInsuranceId, (decimal)b2.Wage, empMark),
EmployeePercent_2 = b2 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b2.PoliceInsuranceId, (decimal)b2.Wage, empMark),
State_2 = b2 == null ? null : b2.State,
PoliceInsuranceName_2 = b2 == null ? null : b2.Name,
YearMonth_2 = b2 == null ? null : b2.YearMonth,
StartTime_2 = b2 == null ? null : b2.StartTime,
EndTime_2 = b2 == null ? null : b2.EndTime,
Wage_2 = b2 == null ? null : b2.Wage,
PoliceOperationName_2 = b2 == null ? null : b2.PoliceOperationName,
InsuranceMonth_2 = b2 == null ? null : b2.InsuranceMonth,
IsIndependentAccount_2 = b2 == null ? null : b2.IsIndependentAccount,
CreateTime_2 = b2 == null ? null : b2.CreateTime,
PoliceAccountNatureName_2 = b2 == null ? null : b2.PoliceAccountNaturename,
InsuranceCode_3 = b3 == null ? null : b3.InsuranceCode,
CompanyNumber_3 = b3 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b3.PoliceInsuranceId, (decimal)b3.Wage, comMark),
CompanyPercent_3 = b3 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b3.PoliceInsuranceId, (decimal)b3.Wage, comMark),
EmployeeNumber_3 = b3 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b3.PoliceInsuranceId, (decimal)b3.Wage, empMark),
EmployeePercent_3 = b3 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b3.PoliceInsuranceId, (decimal)b3.Wage, empMark),
State_3 = b3 == null ? null : b3.State,
PoliceInsuranceName_3 = b3 == null ? null : b3.Name,
YearMonth_3 = b3 == null ? null : b3.YearMonth,
StartTime_3 = b3 == null ? null : b3.StartTime,
EndTime_3 = b3 == null ? null : b3.EndTime,
Wage_3 = b3 == null ? null : b3.Wage,
PoliceOperationName_3 = b3 == null ? null : b3.PoliceOperationName,
InsuranceMonth_3 = b3 == null ? null : b3.InsuranceMonth,
IsIndependentAccount_3 = b3 == null ? null : b3.IsIndependentAccount,
CreateTime_3 = b3 == null ? null : b3.CreateTime,
PoliceAccountNatureName_3 = b3 == null ? null : b3.PoliceAccountNaturename,
InsuranceCode_4 = b4 == null ? null : b4.InsuranceCode,
CompanyNumber_4 = b4 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b4.PoliceInsuranceId, (decimal)b4.Wage, comMark),
CompanyPercent_4 = b4 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b4.PoliceInsuranceId, (decimal)b4.Wage, comMark),
EmployeeNumber_4 = b4 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b4.PoliceInsuranceId, (decimal)b4.Wage, empMark),
EmployeePercent_4 = b4 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b4.PoliceInsuranceId, (decimal)b4.Wage, empMark),
State_4 = b4 == null ? null : b4.State,
PoliceInsuranceName_4 = b4 == null ? null : b4.Name,
YearMonth_4 = b4 == null ? null : b4.YearMonth,
StartTime_4 = b4 == null ? null : b4.StartTime,
EndTime_4 = b4 == null ? null : b4.EndTime,
Wage_4 = b4 == null ? null : b4.Wage,
PoliceOperationName_4 = b4 == null ? null : b4.PoliceOperationName,
InsuranceMonth_4 = b4 == null ? null : b4.InsuranceMonth,
IsIndependentAccount_4 = b4 == null ? null : b4.IsIndependentAccount,
CreateTime_4 = b4 == null ? null : b4.CreateTime,
PoliceAccountNatureName_4 = b4 == null ? null : b4.PoliceAccountNaturename,
InsuranceCode_5 = b5 == null ? null : b5.InsuranceCode,
CompanyNumber_5 = b5 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b5.PoliceInsuranceId, (decimal)b5.Wage, comMark),
CompanyPercent_5 = b5 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b5.PoliceInsuranceId, (decimal)b5.Wage, comMark),
EmployeeNumber_5 = b5 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b5.PoliceInsuranceId, (decimal)b5.Wage, empMark),
EmployeePercent_5 = b5 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b5.PoliceInsuranceId, (decimal)b5.Wage, empMark),
State_5 = b5 == null ? null : b5.State,
PoliceInsuranceName_5 = b5 == null ? null : b5.Name,
YearMonth_5 = b5 == null ? null : b5.YearMonth,
StartTime_5 = b5 == null ? null : b5.StartTime,
EndTime_5 = b5 == null ? null : b5.EndTime,
Wage_5 = b5 == null ? null : b5.Wage,
PoliceOperationName_5 = b5 == null ? null : b5.PoliceOperationName,
InsuranceMonth_5 = b5 == null ? null : b5.InsuranceMonth,
IsIndependentAccount_5 = b5 == null ? null : b5.IsIndependentAccount,
CreateTime_5 = b5 == null ? null : b5.CreateTime,
PoliceAccountNatureName_5 = b5 == null ? null : b5.PoliceAccountNaturename,
InsuranceCode_6 = b6 == null ? null : b6.InsuranceCode,
CompanyNumber_6 = b6 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b6.PoliceInsuranceId, (decimal)b6.Wage, comMark),
CompanyPercent_6 = b6 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b6.PoliceInsuranceId, (decimal)b6.Wage, comMark),
EmployeeNumber_6 = b6 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b6.PoliceInsuranceId, (decimal)b6.Wage, empMark),
EmployeePercent_6 = b6 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b6.PoliceInsuranceId, (decimal)b6.Wage, empMark),
State_6 = b6 == null ? null : b6.State,
PoliceInsuranceName_6 = b6 == null ? null : b6.Name,
YearMonth_6 = b6 == null ? null : b6.YearMonth,
StartTime_6 = b6 == null ? null : b6.StartTime,
EndTime_6 = b6 == null ? null : b6.EndTime,
Wage_6 = b6 == null ? null : b6.Wage,
PoliceOperationName_6 = b6 == null ? null : b6.PoliceOperationName,
InsuranceMonth_6 = b6 == null ? null : b6.InsuranceMonth,
IsIndependentAccount_6 = b6 == null ? null : b6.IsIndependentAccount,
CreateTime_6 = b6 == null ? null : b6.CreateTime,
PoliceAccountNatureName_6 = b6 == null ? null : b6.PoliceAccountNaturename,
InsuranceCode_7 = b7 == null ? null : b7.InsuranceCode,
CompanyNumber_7 = b7 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b7.PoliceInsuranceId, (decimal)b7.Wage, comMark),
CompanyPercent_7 = b7 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b7.PoliceInsuranceId, (decimal)b7.Wage, comMark),
EmployeeNumber_7 = b7 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b7.PoliceInsuranceId, (decimal)b7.Wage, empMark),
EmployeePercent_7 = b7 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b7.PoliceInsuranceId, (decimal)b7.Wage, empMark),
State_7 = b7 == null ? null : b7.State,
PoliceInsuranceName_7 = b7 == null ? null : b7.Name,
YearMonth_7 = b7 == null ? null : b7.YearMonth,
StartTime_7 = b7 == null ? null : b7.StartTime,
EndTime_7 = b7 == null ? null : b7.EndTime,
Wage_7 = b7 == null ? null : b7.Wage,
PoliceOperationName_7 = b7 == null ? null : b7.PoliceOperationName,
InsuranceMonth_7 = b7 == null ? null : b7.InsuranceMonth,
IsIndependentAccount_7 = b7 == null ? null : b7.IsIndependentAccount,
CreateTime_7 = b7 == null ? null : b7.CreateTime,
PoliceAccountNatureName_7 = b7 == null ? null : b7.PoliceAccountNaturename,
InsuranceCode_8 = b8 == null ? null : b8.InsuranceCode,
CompanyNumber_8 = b8 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b8.PoliceInsuranceId, (decimal)b8.Wage, comMark),
CompanyPercent_8 = b8 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b8.PoliceInsuranceId, (decimal)b8.Wage, comMark),
EmployeeNumber_8 = b8 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_Jishu(db, (int)b8.PoliceInsuranceId, (decimal)b8.Wage, empMark),
EmployeePercent_8 = b8 == null ? null : (decimal?)Langben.DAL.EmployeeAddRepository.Get_BILI(db, (int)b8.PoliceInsuranceId, (decimal)b8.Wage, empMark),
State_8 = b8 == null ? null : b8.State,
PoliceInsuranceName_8 = b8 == null ? null : b8.Name,
YearMonth_8 = b8 == null ? null : b8.YearMonth,
StartTime_8 = b8 == null ? null : b8.StartTime,
EndTime_8 = b8 == null ? null : b8.EndTime,
Wage_8 = b8 == null ? null : b8.Wage,
PoliceOperationName_8 = b8 == null ? null : b8.PoliceOperationName,
InsuranceMonth_8 = b8 == null ? null : b8.InsuranceMonth,
IsIndependentAccount_8 = b8 == null ? null : b8.IsIndependentAccount,
CreateTime_8 = b8 == null ? null : b8.CreateTime,
PoliceAccountNatureName_8 = b8 == null ? null : b8.PoliceAccountNaturename,
}).ToList();
#endregion
return(appList);
}
catch (Exception e)
{
throw e;
}
}
}
public CommonDbResponse UpdateCard(CardCommon cardCommon)
{
return(_repo.UpdateCard(cardCommon));
}
public CommonDbResponse InsertCard(CardCommon cardCommon)
{
return(_repo.InsertCard(cardCommon));
}
public CommonDbResponse RequestCard(CardCommon cardCommon)
{
return(_repo.RequestCard(cardCommon));
}
public CommonDbResponse EnableDisableCard(CardCommon cardCommon)
{
return(_repo.EnableDisableCard(cardCommon));
}
public CommonDbResponse CardBalance(CardCommon cardCommon)
{
return(_repo.CardBalance(cardCommon));
}
public CommonDbResponse CardApproval(CardCommon cardCommon)
{
return(_repo.CardApproval(cardCommon));
}
public CommonDbResponse CardUser(CardCommon cardCommon)
{
return(_repo.CardUser(cardCommon));
}
/// <summary>
/// 查询员工列表
/// </summary>
/// <param name="db"></param>
/// <param name="page"></param>
/// <param name="rows"></param>
/// <param name="sort"></param>
/// <param name="search"></param>
/// <param name="count"></param>
/// <returns></returns>
public IQueryable <EmployeeInfo> GetEmployeeList(SysEntities db, string search)
{
var query = from employee in db.Employee
join relation in db.CompanyEmployeeRelation
on employee.Id equals relation.EmployeeId into lg1
from relation in lg1.DefaultIfEmpty()
join branch in db.CRM_CompanyToBranch on relation.CompanyId equals branch.CRM_Company_ID into lg2
from branch in lg2.DefaultIfEmpty()
select new EmployeeInfo()
{
empId = employee.Id,
Empname = employee.Name,
CertificateType = employee.CertificateType,
CertificateNumber = employee.CertificateNumber,
Sex = employee.Sex,
AccountType = employee.AccountType,
CreateTime = employee.CreateTime,
zrkf = branch.UserID_ZR,
ygkf = branch.UserID_YG
};
Dictionary <string, string> queryDic = ValueConvert.StringToDictionary(search.GetString());
if (queryDic != null && queryDic.Count > 0)
{
if (queryDic.ContainsKey("Name") && !string.IsNullOrWhiteSpace(queryDic["Name"]))
{
string str = queryDic["Name"];
query = query.Where(a => a.Empname.Contains(str));
}
if (queryDic.ContainsKey("CertificateNumber") && !string.IsNullOrWhiteSpace(queryDic["CertificateNumber"]))
{
string str = queryDic["CertificateNumber"];
string[] numList = str.Split(new char[] { '\n' }, StringSplitOptions.RemoveEmptyEntries);
List <string> CARDLIST = new List <string>();
for (int i = 0; i < numList.Length; i++)
{
numList[i] = numList[i].TrimEnd(',');
CARDLIST.Add(numList[i]);
CARDLIST.Add(CardCommon.CardIDTo15(numList[i]));
CARDLIST.Add(CardCommon.CardIDTo18(numList[i]));
}
CARDLIST = CARDLIST.Distinct().ToList();
query = query.Where(o => CARDLIST.Contains(o.CertificateNumber));
}
}
return(query.OrderByDescending(o => o.CreateTime));
//获取员工姓名查询员工信息
//IQueryable<Employee> employee = db.Employee.Where(o => true);
//Dictionary<string, string> queryDic = ValueConvert.StringToDictionary(search.GetString());
//if (queryDic != null && queryDic.Count > 0)
//{
// if (queryDic.ContainsKey("Name") && !string.IsNullOrWhiteSpace(queryDic["Name"]))
// {
// string str = queryDic["Name"];
// employee = employee.Where(a => a.Name.Contains(str));
// }
// if (queryDic.ContainsKey("CertificateNumber") && !string.IsNullOrWhiteSpace(queryDic["CertificateNumber"]))
// {
// string str = queryDic["CertificateNumber"];
// string[] numList = str.Split(new char[] { '\n' }, StringSplitOptions.RemoveEmptyEntries);
// List<string> CARDLIST = new List<string>();
// for (int i = 0; i < numList.Length; i++)
// {
// numList[i] = numList[i].TrimEnd(',');
// CARDLIST.Add(numList[i]);
// CARDLIST.Add(CardCommon.CardIDTo15(numList[i]));
// CARDLIST.Add(CardCommon.CardIDTo18(numList[i]));
// }
// CARDLIST = CARDLIST.Distinct().ToList();
// employee = employee.Where(o => CARDLIST.Contains(o.CertificateNumber));
// }
//}
//return employee;
}
/// <summary>
/// 获取费用中间表中数据
/// </summary>
/// <param name="db"></param>
/// <param name="yearMonth">时间</param>
/// <param name="companyId">公司编号</param>
/// <param name="insuranceId">种类</param>
/// <param name="certificate">身份证号</param>
/// <param name="name">姓名</param>
/// <returns></returns>
public IQueryable <EmployeeMiddleShow> GetData(SysEntities db, int yearMonth, List <int> companyId, List <string> cityId, int insuranceId, string certificate, string name)
{
var query = from em in db.EmployeeMiddle
join cer in db.CompanyEmployeeRelation on em.CompanyEmployeeRelationId equals cer.Id
join e in db.Employee on cer.EmployeeId equals e.Id
join c in db.CRM_Company on cer.CompanyId equals c.ID
select new EmployeeMiddleShow
{
EmployeeId = e.Id,
EmployeeName = e.Name,
CardId = e.CertificateNumber,
CompanyId = c.ID,
CompanyName = c.CompanyName,
CompanyEmployeeRelationId = em.CompanyEmployeeRelationId,
CityId = em.CityId,
InsuranceKindId = em.InsuranceKindId,
Id = em.Id,
PaymentBetween = em.PaymentBetween,
PaymentStyle = em.PaymentStyle,
CompanyBasePayment = em.CompanyBasePayment,
CompanyPayment = em.CompanyPayment,
EmployeeBasePayment = em.EmployeeBasePayment,
EmployeePayment = em.EmployeePayment,
PaymentMonth = em.PaymentMonth,
StartDate = em.StartDate,
EndedDate = em.EndedDate,
UseBetween = em.UseBetween,
Remark = em.Remark,
State = em.State,
CreateTime = em.CreateTime,
CreatePerson = em.CreatePerson,
UpdateTime = em.UpdateTime,
UpdatePerson = em.UpdatePerson
};
if (yearMonth != 0)
{
query = query.Where(u => u.EndedDate >= yearMonth && u.StartDate <= yearMonth);
}
if (companyId != null)
{
query = query.Where(u => companyId.Contains(u.CompanyId));
}
if (cityId != null)
{
query = query.Where(u => cityId.Contains(u.CityId));
}
if (insuranceId != 0)
{
query = query.Where(u => u.InsuranceKindId == insuranceId);
}
if (!string.IsNullOrEmpty(certificate))
{
// 身份证号验证
List <string> cardList = new List <string>();
string[] certificateList = certificate.Split(Convert.ToChar(10));
for (int i = 0; i < certificateList.Length; i++)
{
cardList.Add(certificateList[i]);
cardList.Add(CardCommon.CardIDTo15(certificateList[i]));
cardList.Add(CardCommon.CardIDTo18(certificateList[i]));
}
cardList = cardList.Distinct().ToList();
query = query.Where(u => cardList.Contains(u.CardId));
}
if (!string.IsNullOrEmpty(name))
{
// 名称验证
string[] nameList = name.Split(Convert.ToChar(10));
query = query.Where(u => nameList.Contains(u.EmployeeName));
}
return(query.OrderBy(o => o.EmployeeId));
}
/// <summary>
/// 查询待责任客服审核列表
/// </summary>
/// <param name="SysEntities">数据访问的上下文</param>
/// <param name="page">页码</param>
/// <param name="rows">行数</param>
/// <returns></returns>
public List <EmployeeApprove> GetApproveList(SysEntities db, string CertificateNumber, int page, int rows, string search, out int count)
{
using (var ent = new SysEntities())
{
try
{
var emp = ent.Employee.Where(a => true);
var empGoon = ent.EmployeeGoonPayment.Where(a => true);
var com = ent.CRM_Company.Where(a => true);
var city = ent.City.Where(a => true);
Dictionary <string, string> queryDic = ValueConvert.StringToDictionary(search.GetString());
if (queryDic != null && queryDic.Count > 0)
{
if (queryDic.ContainsKey("Name") && !string.IsNullOrWhiteSpace(queryDic["Name"]))
{
string str = queryDic["Name"];
emp = emp.Where(a => a.Name.Contains(str));
}
if (!string.IsNullOrEmpty(CertificateNumber))
{
string[] cardIdList = CertificateNumber.Split(Convert.ToChar(10));
List <string> cardList = new List <string>();
for (int i = 0; i < cardIdList.Length; i++)
{
cardList.Add(cardIdList[i]);
cardList.Add(CardCommon.CardIDTo15(cardIdList[i]));
cardList.Add(CardCommon.CardIDTo18(cardIdList[i]));
}
cardList = cardList.Distinct().ToList();
emp = emp.Where(o => cardList.Contains(o.CertificateNumber));
}
if (queryDic.ContainsKey("State") && !string.IsNullOrWhiteSpace(queryDic["State"]))
{
string str = queryDic["State"];
string[] states = str.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
empGoon = empGoon.Where(a => states.Contains(a.State));
}
if (queryDic.ContainsKey("InsuranceKinds") && !string.IsNullOrWhiteSpace(queryDic["InsuranceKinds"]))
{
string str = queryDic["InsuranceKinds"];
int?[] Ids = Array.ConvertAll <string, int?>(str.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), delegate(string s) { return(int.Parse(s)); });
empGoon = empGoon.Where(a => Ids.Contains(a.EmployeeAdd.InsuranceKindId));
}
if (queryDic.ContainsKey("YearMonth") && !string.IsNullOrWhiteSpace(queryDic["YearMonth"]))
{
int str = Convert.ToInt32(queryDic["YearMonth"]);
empGoon = empGoon.Where(a => a.YearMonth == str);
}
if (queryDic.ContainsKey("CompanyName") && !string.IsNullOrWhiteSpace(queryDic["CompanyName"]))
{
string str = queryDic["CompanyName"];
com = com.Where(a => a.CompanyName.Contains(str));
}
if (queryDic.ContainsKey("CityId") && !string.IsNullOrWhiteSpace(queryDic["CityId"]))
{
string str = queryDic["CityId"];
city = city.Where(a => a.Id == str);
}
}
var list = (from a in emp
join r in ent.CompanyEmployeeRelation on a.Id equals r.EmployeeId
join b in empGoon on r.Id equals b.EmployeeAdd.CompanyEmployeeRelationId
join c in com on r.CompanyId equals c.ID
//join d in ent.InsuranceKind on b.InsuranceKindId equals d.Id
join e in city on r.CityId equals e.Id
join f in ent.PoliceAccountNature on b.EmployeeAdd.PoliceAccountNatureId equals f.Id
select new EmployeeApprove()
{
CompanyEmployeeRelationId = b.EmployeeAdd.CompanyEmployeeRelationId,
CompanyId = r.CompanyId,
CompanyName = c.CompanyName,
Name = a.Name,
CertificateNumber = a.CertificateNumber,
City = e.Name,
CityID = r.CityId,
PoliceAccountNature = f.Name,
YearMonth = b.YearMonth
}).Distinct();
count = 0;
if (list != null && list.Count() >= 1)
{
count = list.Count();
if (page > -1)
{
list = list.OrderBy(a => a.CompanyId).ThenBy(a => a.CompanyEmployeeRelationId).Skip((page - 1) * rows).Take(rows);
}
}
List <EmployeeApprove> appList = list.ToList();
//将险种进行查询补充,将页面需要展示人员的补缴险种信息全部查询并放入系统内存中;
var temp = (from a in empGoon
join b in list on a.EmployeeAdd.CompanyEmployeeRelationId equals b.CompanyEmployeeRelationId
//join d in ent.InsuranceKind on a.InsuranceKindId equals d.Id
where a.YearMonth == b.YearMonth
select new
{
a.EmployeeAdd.CompanyEmployeeRelationId,
a.EmployeeAdd.InsuranceKindId,
a.YearMonth,
a.Id
}).ToList();
for (int i = 0; i < appList.Count(); i++)
{
int?Id = appList[i].CompanyEmployeeRelationId;
int?YearMonth = appList[i].YearMonth;
if (Id != null)
{
var kinds = temp.Where(a => a.CompanyEmployeeRelationId == Id && a.YearMonth == YearMonth).ToList();
//var kinds = temp.Where(a => a.CompanyEmployeeRelationId == Id).ToList();
string kindsName = "";
string addIds = "";
foreach (var a in kinds)
{
kindsName += ((Common.EmployeeAdd_InsuranceKindId)a.InsuranceKindId + ",");
addIds += (a.Id + ",");
}
kindsName = kindsName.Substring(0, kindsName.Length - 1);
appList[i].InsuranceKinds = kindsName;
appList[i].AddIds = addIds;
}
}
return(appList);
}
catch (Exception e)
{
throw e;
}
}
}
private List <EmployeeAddExcle> CheckImportEmployeeAdd(DataTable table, ref string message)
{
List <EmployeeAddExcle> employeeList = new List <EmployeeAddExcle>();
#region 数据转换
foreach (DataRow dr in table.Rows)
{
EmployeeAddExcle employee = new EmployeeAddExcle();
employee.CompanyName = dr["单位"].ToString().Trim();
employee.City = dr["社保缴纳地"].ToString().Trim();
employee.Empname = dr["姓名"].ToString().Trim();
employee.CertificateNumber = dr["证件号码"].ToString().Trim();
employee.CertificateType = dr["证件类型"].ToString().Trim();
employee.Station = dr["岗位"].ToString().Trim();
employee.PoliceAccountNatureName = dr["户口性质"].ToString().Trim();
employee.AccountType = dr["户口类型"].ToString().Trim();
employee.MobilePhone = dr["移动电话"].ToString().Trim();
employee.Telephone = dr["固定电话"].ToString().Trim();
employee.Address = dr["联系地址"].ToString().Trim();
employee.Email = dr["邮箱"].ToString().Trim();
employee.AccountName = dr["开户名称"].ToString().Trim();
employee.BranchBank = dr["支行名称"].ToString().Trim();
employee.Bank = dr["开户银行"].ToString().Trim();
employee.Account = dr["账号"].ToString().Trim();
employeeList.Add(employee);
}
#endregion
string search = string.Empty;
StringBuilder errMessageAll = new StringBuilder();
string DataForm = "证件号:{0},姓名:{1},的异常信息为:{2}\r\n";
Regex r = new Regex(@"(13[0-9]|14[5|7]|15[0|1|2|3|5|6|7|8|9]|18[0-9]|170)\d{8}$");
foreach (EmployeeAddExcle item in employeeList)
{
StringBuilder errMessage = new StringBuilder();
#region 基础信息赋值
if (string.IsNullOrEmpty(item.CertificateNumber))
{
errMessage.Append("证件号码为空;\r\n");
}
else if (!CardCommon.CheckCardID18(item.CertificateNumber))
{
errMessage.Append("身份证号不合法：" + item.CertificateNumber + ";\r\n");
}
if (string.IsNullOrEmpty(item.Empname))
{
errMessage.Append("姓名为空;\r\n");
}
if (string.IsNullOrEmpty(item.CompanyName))
{
errMessage.Append("单位为空;\r\n");
}
else
{
item.CompanyId = getCompanyIdByExcel(item, ref errMessage);
}
if (string.IsNullOrEmpty(item.City))
{
errMessage.Append("社保缴纳地为空;\r\n");
}
else
{
item.City = getCityIdByExcel(item, ref errMessage);
}
if (string.IsNullOrEmpty(item.CertificateType))
{
errMessage.Append("证件类型为空;\r\n");
}
else
{
CertificateType certificateType = new CertificateType();
if (!Enum.TryParse <CertificateType>(item.CertificateType, out certificateType))
{
errMessage.Append("证件类型不正确;\r\n");
}
}
if (string.IsNullOrEmpty(item.Station))
{
errMessage.Append("岗位为空;\r\n");
}
if (string.IsNullOrEmpty(item.PoliceAccountNatureName))
{
errMessage.Append("户口性质为空;\r\n");
}
else
{
item.PoliceAccountNature = getPoliceAccountNatureIdByExcel(item, ref errMessage);
}
if (string.IsNullOrEmpty(item.MobilePhone) && string.IsNullOrEmpty(item.Telephone))
{
errMessage.Append("固定电话或者移动电话至少填一项;\r\n");
}
else
{
if (!string.IsNullOrEmpty(item.MobilePhone))
{
if (!Regex.IsMatch(item.MobilePhone, @"(13[0-9]|14[5|7]|15[0|1|2|3|5|6|7|8|9]|18[0-9]|170)\d{8}$"))
{
errMessage.Append("移动电话格式不正确;\r\n");
}
;
}
if (!string.IsNullOrEmpty(item.Telephone))
{
if (!Regex.IsMatch(item.Telephone, @"(0[0-9]{2,3})-([2-9][0-9]{6,7})$"))
{
errMessage.Append("固定电话格式不正确;\r\n");
}
;
}
}
if (!string.IsNullOrEmpty(item.Email))
{
if (!Regex.IsMatch(item.Email, @"(\w)+(\.\w+)*@(\w)+((\.\w+)+)"))
{
errMessage.Append("邮箱格式不正确;\r\n");
}
;
}
if (!(string.IsNullOrEmpty(item.AccountName) && string.IsNullOrEmpty(item.Bank) && string.IsNullOrEmpty(item.BranchBank) && string.IsNullOrEmpty(item.Account)))
{
if (string.IsNullOrEmpty(item.AccountName) || string.IsNullOrEmpty(item.Bank) || string.IsNullOrEmpty(item.BranchBank) || string.IsNullOrEmpty(item.Account))
{
errMessage.Append("请完善银行信息;\r\n");
}
}
#endregion
if (!string.IsNullOrWhiteSpace(errMessage.ToString()))
{
errMessageAll.Append(string.Format(DataForm, item.CertificateNumber, item.Empname, errMessage.ToString()));
}
}
message = errMessageAll.ToString();
return(employeeList);
}
