/// <summary>
/// 获取用户的总账科目
/// </summary>
/// <param name="accGrp">科目分组标识</param>
/// <returns></returns>
public string GetUserLedgerAccount(string accGrp = null)
{
string strFormatter = "{{\"text\":\"{0}\",\"value\":\"{1}\",\"Group\":\"{2}\",\"Code\":\"{3}\"}},";
StringBuilder strJson = new StringBuilder("[ ");
List <T_GeneralLedgerAccount> accs =
new AccountSvc().GetUserLedgerAccounts(Session["CurrentCompany"].ToString());
if (!string.IsNullOrEmpty(accGrp))
{
IEnumerable <int> accGrps = accGrp.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Cast <int>();
accs = accs.Where(i => accGrps.Contains(i.AccGroup)).ToList();
}
foreach (T_GeneralLedgerAccount item in accs)
{
strJson.AppendFormat(strFormatter, item.Name, item.LA_GUID, item.AccGroup, item.AccCode);
}
strJson.Remove(strJson.Length - 1, 1);
strJson.Append("]");
return(strJson.ToString());
}
/// <summary>
/// 批量导入excel数据
/// </summary>
/// 20180514修改对应新的职工薪酬模板
public ActionResult Upexcel(FormCollection from)
{
ExceResult res = new ExceResult();
res.success = true;
HttpPostedFileBase file = Request.Files["upload"];
string result = string.Empty;
string strVouchID = Guid.NewGuid().ToString();
if (file == null || file.ContentLength <= 0)
{
res.success = false;
res.msg = "无有效文件";
}
else
{
try
{
List <T_DetailedAccount> rec = new AccountSvc().GetLAccountByName("职工薪酬", Session["CurrentCompanyGuid"].ToString());
Workbook workbook = new Workbook(file.InputStream);
Cells cells = workbook.Worksheets[0].Cells;
DataTable tab = cells.ExportDataTable(0, 0, cells.Rows.Count, cells.MaxDisplayRange.ColumnCount);
int rowsnum = tab.Rows.Count;
if (rowsnum == 0)
{
res.success = false;
result = "Excel表为空!请重新导入！"; //当Excel表为空时，对用户进行提示
}
//数据表一共多少行！
DataRow[] dr = tab.Select();
T_GeneralLedgerAccount gen = new AccountSvc().GetLAByName("银行存款", Session["CurrentCompanyGuid"].ToString());
string RpTypeID = string.Empty;
if (gen != null)
{
RpTypeID = gen.LA_GUID;
}
//按行进行数据存储操作！
for (int i = 3; i < dr.Length; i++)
{
T_WageCost WageCostRecord = new T_WageCost();
WageCostRecord.W_GUID = Guid.NewGuid().ToString();
WageCostRecord.C_GUID = Session["CurrentCompanyGuid"].ToString();
WageCostRecord.State = "未付";
WageCostRecord.PayType = RpTypeID;
WageCostRecord.Date = Convert.ToDateTime(dr[i][1].ToString());
WageCostRecord.Profit_Name = dr[i][2].ToString();
WageCostRecord.Employee = dr[i][0].ToString();
WageCostRecord.Cash = Convert.ToDecimal(GetValue(dr[i][4].ToString())) + Convert.ToDecimal(GetValue(dr[i][5].ToString())) - Convert.ToDecimal(GetValue(dr[i][6].ToString()));
WageCostRecord.Total = Convert.ToDecimal(GetValue(dr[i][23].ToString()));
WageCostRecord.PersonalTaxes = Convert.ToDecimal(GetValue(dr[i][12].ToString()));
WageCostRecord.BonusAllowance = Convert.ToDecimal(GetValue(dr[i][7].ToString())) + Convert.ToDecimal(GetValue(dr[i][8].ToString())) + Convert.ToDecimal(GetValue(dr[i][9].ToString()));
WageCostRecord.EmployeeWelfare = Convert.ToDecimal(GetValue(dr[i][10].ToString()));
WageCostRecord.SocialSecurity = Convert.ToDecimal(GetValue(dr[i][16].ToString()));
WageCostRecord.HousingProvident = Convert.ToDecimal(GetValue(dr[i][19].ToString()));
WageCostRecord.NonCurrency = Convert.ToDecimal(GetValue(dr[i][22].ToString()));
WageCostRecord.StaffEducation = Convert.ToDecimal(GetValue(dr[i][21].ToString()));
WageCostRecord.TradeUnion = Convert.ToDecimal(GetValue(dr[i][20].ToString()));
WageCostRecord.DismissWelfare = Convert.ToDecimal(GetValue(dr[i][11].ToString()));
T_IERecord Record = new T_IERecord();
Record.Creator = base.userData.LoginFullName;
Record.C_GUID = Session["CurrentCompanyGuid"].ToString();
Record.IE_GUID = WageCostRecord.W_GUID;
Record.RPer = "e5e38321-4549-4c24-9f3a-3cd70ee1d591";
Record.AffirmDate = WageCostRecord.Date;
Record.Date = WageCostRecord.Date;
Record.SumAmount = WageCostRecord.Total;
Record.InvType = WageCostRecord.Profit_Name;
if (WageCostRecord.Profit_Name == "营业成本")
{
WageCostRecord.Profit_Name = "主营业务成本";
}
var query = rec.Where(p => p.ParentName == WageCostRecord.Profit_Name);
if (query.Any())
{
Record.IEGroup = query.FirstOrDefault().DA_GUID;//工资类别guid
}
Record.Remark = WageCostRecord.Employee;
Record.Currency = dr[i][3].ToString();
Record.Amount = WageCostRecord.Total - WageCostRecord.PersonalTaxes;
Record.CreateDate = GetNowDate();
Record.Profit_Name = WageCostRecord.Profit_Name;
Record.TaxationAmount = 0;
Record.TaxationType = "";
Record.IE_Flag = "SA";
Record.State = "应付";
if (WageCostRecord.Date.CompareTo(Convert.ToDateTime(GetNowDate())) > 0)
{
result = "导入失败，时间错误";
break;
}
if (WageCostRecord.Date == null)
{
result = "导入失败,excel表第二列第" + dr[i] + "行时间没有填写";
break;
}
if (WageCostRecord.Employee == null)
{
result = "导入失败,excel表第一列第" + dr[i] + "行姓名没有填写";
break;
}
try
{
string currency = (new CurrencySvc().GetCurrency(dr[i][3].ToString())).ToString();
WageCostRecord.Currency = currency;
}
catch (Exception)
{
result = "导入失败，无此货币";
break;
}
if (dr[i][2].ToString() != "营业成本" && dr[i][2].ToString() != "销售费用" && dr[i][2].ToString() != "管理费用")
{
result = "导入失败，类别错误";
break;
}
bool TorF = new IESvc().UpdWageCost(WageCostRecord, strVouchID);
if (TorF)
{
DateTime EditThreshold = DateTime.Parse(Session["EditThreshold"].ToString());
if (Record.Date <= DateTime.Now && Record.Date >= EditThreshold)
{
bool ExpR = new IESvc().UpdExpenseInfo(Record, strVouchID);
if (ExpR)
{
result = "导入成功！";
}
else
{
result = General.Resource.Common.Failed;
}
}
else
{
result = FMS.Resource.Finance.Finance.DateError;
}
}
else
{
result = "导入失败！";
}
}
}
catch (Exception)
{
result = "导入失败，请检查EXCEL格式是否错误！";
}
}
JsonResult json = new JsonResult();
json.Data = result;
return(json);
}
public string UpdWageCostsRecord(List <T_WageCost> payList, string addstyle)
{
bool result = false;
string msg = string.Empty;
string strVouchID = Guid.NewGuid().ToString();
List <T_DetailedAccount> rec = new AccountSvc().GetLAccountByName("职工薪酬", Session["CurrentCompanyGuid"].ToString());
foreach (T_WageCost wag in payList)
{
var query = rec.Where(p => p.ParentName == wag.Profit_Name);
wag.C_GUID = Session["CurrentCompanyGuid"].ToString();
T_IERecord Record = new T_IERecord();
Record.Creator = base.userData.LoginFullName;
Record.C_GUID = Session["CurrentCompanyGuid"].ToString();
Record.IE_GUID = wag.W_GUID;
Record.RPer = "e5e38321-4549-4c24-9f3a-3cd70ee1d591";
Record.AffirmDate = wag.Date;
Record.Date = wag.Date;
Record.SumAmount = wag.Total;
Record.InvType = wag.InvType;
if (query.Any())
{
Record.IEGroup = query.FirstOrDefault().DA_GUID;
}
Record.Remark = wag.Employee;
Record.Currency = wag.Currency;
Record.Amount = wag.Total - wag.PersonalTaxes;
Record.CreateDate = GetNowDate();
Record.Profit_Name = wag.Profit_Name;
Record.TaxationAmount = 0;
Record.TaxationType = "";
Record.IE_Flag = "SA";
//T_GeneralLedgerAccount rec = new AccountSvc().GetLAccount(Record.IEGroup, Session["CurrentCompanyGuid"].ToString(), Record.Profit_Name);
//Record.IEGroup = rec.Where(p => p.ParentName == wag.Profit_Name).FirstOrDefault().DA_GUID;
if (addstyle == "1")
{
Record.State = "已付";
}
else
{
Record.State = "应付";
}
result = new IESvc().UpdWageCost(wag, strVouchID);
if (result)
{
DateTime EditThreshold = DateTime.Parse(Session["EditThreshold"].ToString());
if (Record.Date <= DateTime.Now && Record.Date >= EditThreshold)
{
new IESvc().UpdExpenseInfo(Record, strVouchID);
if (result)
{
msg = General.Resource.Common.Success;
}
else
{
msg = General.Resource.Common.Failed;
}
}
else
{
result = false;
msg = FMS.Resource.Finance.Finance.DateError;
}
}
else
{
msg = General.Resource.Common.Failed;
}
}
return(string.Format("{{\"Result\":{0},\"Msg\":\"{1}\"}}"
, result.ToString().ToLower(), msg));
}