public string UpdWcRecord(string id, decimal total, string Currency, string name, string RPType, string Profit_Name)
{
bool result = false;
ExceResult res = new ExceResult();
string msg = string.Empty;
T_WageCost wag = new T_WageCost();
wag.PayType = RPType;
wag.C_GUID = Session["CurrentCompanyGuid"].ToString();
wag.W_GUID = id;
wag.Total = total;
wag.Currency = Currency;
wag.Employee = name;
wag.Date = DateTime.Now;
wag.State = "未付";
wag.Profit_Name = Profit_Name;
wag.SalaryType = 1;
string strVouchID = Guid.NewGuid().ToString();
result = new IESvc().UpdWageCost(wag, strVouchID);
if (result)
{
res.success = true;
msg = General.Resource.Common.Success;
}
else
{
res.success = false;
msg = General.Resource.Common.Failed;
}
return(JsonConvert.SerializeObject(res));
}
public bool UpdWageCost(T_WageCost rec)
{
DBHelper dh = new DBHelper();
dh.BeginTran();
try
{
dh.strCmd = "SP_UpdWageCost";
dh.AddPare("@W_GUID", SqlDbType.NVarChar, 40, rec.W_GUID);
dh.AddPare("@C_GUID", SqlDbType.NVarChar, 40, rec.C_GUID);
dh.AddPare("@Employee", SqlDbType.NVarChar, 40, rec.Employee);
dh.AddPare("@Cash", SqlDbType.Decimal, 0, rec.Cash);
dh.AddPare("@PersonalTaxes", SqlDbType.Decimal, 0, rec.PersonalTaxes);
dh.AddPare("@SocialSecurity", SqlDbType.Decimal, 0, rec.SocialSecurity);
dh.AddPare("@Total", SqlDbType.Decimal, 0, rec.Total);
if (!rec.Date.Equals(DateTime.MinValue))
{
dh.AddPare("@Date", SqlDbType.DateTime, 0, rec.Date);
}
dh.NonQuery();
dh.CleanPara();
dh.CommitTran();
return(true);
}
catch
{
dh.RollBackTran();
return(false);
}
}
public string UpdWageCostsRecord(string id, string rper, decimal money, decimal money1, decimal money2, decimal money3, string invtype, string remark, string currency, string addstyle, DateTime date)
{
bool result = false;
string msg = string.Empty;
T_IERecord Record = new T_IERecord();
Record.Creator = base.userData.LoginFullName;
Record.C_GUID = Session["CurrentCompany"].ToString();
Record.IE_GUID = id;
Record.RPer = rper;
Record.AffirmDate = date;
Record.Date = date;
Record.SumAmount = money;
Record.InvType = invtype;
Record.IEGroup = "1544d862-b1ab-42b8-9e97-9c2e1704665c";//工资类别guid
Record.Remark = remark;
Record.Currency = currency;
Record.CreateDate = DateTime.Now;
Record.TaxationAmount = 0;
Record.TaxationType = "";
Record.State = "应付";
Record.Profit_GUID = "51BFDD3E-2253-4FBF-A946-19C18C25C6FC";
T_WageCost WageCostRecord = new T_WageCost();
WageCostRecord.W_GUID = id;
WageCostRecord.C_GUID = Session["CurrentCompany"].ToString();
WageCostRecord.Date = date;
WageCostRecord.Employee = remark;
WageCostRecord.Cash = money1;
WageCostRecord.PersonalTaxes = money2;
WageCostRecord.SocialSecurity = money3;
WageCostRecord.Total = money1 + money2 + money3;
new IESvc().UpdWageCost(WageCostRecord);
DateTime EditThreshold = DateTime.Parse(Session["EditThreshold"].ToString());
if (Record.Date <= DateTime.Now && Record.Date >= EditThreshold)
{
result = new IESvc().UpdExpenseRecord(Record);
if (result)
{
msg = General.Resource.Common.Success;
}
else
{
msg = General.Resource.Common.Failed;
}
}
else
{
result = false;
msg = FMS.Resource.Finance.Finance.DateError;
}
return(string.Format("{{\"Result\":{0},\"Msg\":\"{1}\"}}"
, result.ToString().ToLower(), msg));
}
/// <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);
}
/// <summary>
/// 批量导入excel数据
/// </summary>
public ActionResult Upexcel(FormCollection from)
{
HttpPostedFileBase file = Request.Files["upload"];
string result = string.Empty;
if (file == null || file.ContentLength <= 0)
{
}
else
{
try
{
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)
{
result = "Excel表为空!请重新导入！"; //当Excel表为空时，对用户进行提示
}
//数据表一共多少行！
DataRow[] dr = tab.Select();
//按行进行数据存储操作！
for (int i = 1; i < dr.Length; i++)
{
T_WageCost WageCostRecord = new T_WageCost();
WageCostRecord.W_GUID = Guid.NewGuid().ToString();
WageCostRecord.C_GUID = Session["CurrentCompany"].ToString();
WageCostRecord.Date = Convert.ToDateTime(dr[i][1].ToString());
WageCostRecord.Employee = dr[i][0].ToString();;
WageCostRecord.Cash = Convert.ToDecimal(dr[i][2].ToString());
WageCostRecord.PersonalTaxes = Convert.ToDecimal(dr[i][3].ToString());
WageCostRecord.SocialSecurity = Convert.ToDecimal(dr[i][4].ToString());
WageCostRecord.Total = Convert.ToDecimal(dr[i][2].ToString()) + Convert.ToDecimal(dr[i][3].ToString()) + Convert.ToDecimal(dr[i][4].ToString());;
new IESvc().UpdWageCost(WageCostRecord);
//RPer,B_Guid,BA_Guid数据需要比对！
string rper = "b73f1802-4ba4-4873-b423-86ea3d9b723f";
T_IERecord record = new T_IERecord();
record.IE_GUID = Guid.NewGuid().ToString();
record.RPer = rper;
DateTime dt;
DateTimeFormatInfo dtFormat = new DateTimeFormatInfo();
dtFormat.ShortDatePattern = "yyyy/MM/dd";
dt = Convert.ToDateTime(dr[i][1].ToString(), dtFormat);
record.AffirmDate = dt;
record.Date = Convert.ToDateTime(dr[i][1].ToString());
record.State = "应付";
record.SumAmount = Convert.ToDecimal(dr[i][2].ToString()) + Convert.ToDecimal(dr[i][3].ToString()) + Convert.ToDecimal(dr[i][4].ToString());
record.C_GUID = Session["CurrentCompany"].ToString();
record.Creator = base.userData.LoginFullName;
record.CreateDate = DateTime.Now;
record.Currency = dr[i][6].ToString();
record.InvType = dr[i][7].ToString();
record.IEGroup = "1544d862-b1ab-42b8-9e97-9c2e1704665c";
record.Remark = dr[i][0].ToString();
record.TaxationAmount = 0;
record.TaxationType = "";
bool TorF = new IESvc().UpdExpenseRecord(record);
if (TorF)
{
result = "导入成功！";
}
else
{
result = "导入失败！";
}
}
}
catch (Exception)
{
result = "导入失败，请检查EXCEL格式是否错误！";
}
}
JsonResult json = new JsonResult();
json.Data = result;
return(json);
}