private void btnUpdate_Click(object sender, EventArgs e)
{
string test = "A+B-(C-D)+Abs(E)";
if (FormulaC.IsValid(test))
{
string x = FormulaC.GetFormula(test);
}
}
private bool CheckInput()
{
try
{
if (FormulaC.IsValid(txtSalF.Text.Trim()) == false)
{
return(false);
}
string s = FormulaC.GetFormula(txtSalF.Text.Trim());
return(true);
}
catch (Exception ex)
{
return(false);
}
}
private bool InsertNewTemplate(ref int tmpid)
{
try
{
List <COLINFO> lRight = lbxColsRight.Items.Cast <COLINFO>().ToList();
var x = lRight.SingleOrDefault(a => a.CI_NAME.IndexOf(txtNameCol.Text.Trim() + "]") == 1);
string s = lRight.Where(a => a.CI_NAME.IndexOf(txtNameCol.Text.Trim() + "]") == 1).SingleOrDefault().ToString();
//模板相关信息
TEMPLATE_INFO ti = new TEMPLATE_INFO
{
TI_NAME = txtTempName.Text.Trim(),
TI_ISVALID = true,
TI_COLSINORDER = String.Join(",", lRight.Select(o => o.CI_COLNAME)),
TI_COLHEADERINORDER = String.Join(",", lRight.Select(o => o.CI_NAME.Substring(o.CI_NAME.IndexOf("]") + 1))),
TI_SALF = FormulaC.GetFormula(txtSalF.Text.Trim()),
TI_SALFRAW = txtSalF.Text.Trim(),
TI_ANNUCOL = txtYearAnnu.Text.Trim(),
TI_VERFYFRAW = txtVerify.Text.Trim(),
TI_HEARDERROW = cbxHeaderRow.SelectedIndex + 1,
TI_DATAROW = cbxDataRow.SelectedIndex + 1,
TI_COLSCOUNT = lbxColsRight.Items.Count,
TI_NAMECOL = txtNameCol.Text.Trim(),
TI_BONUSCOL = txtBonusCol.Text.Trim(),
TI_MONTHCOL = txtMonthCol.Text.Trim(),
TI_REALCOL = txtRealCol.Text.Trim()
};
if (txtVerify.Text.Trim() != string.Empty)
{
ti.TI_VERFYF = FormulaC.GetFormula(txtVerify.Text.Trim());
}
db.TEMPLATE_INFOs.InsertOnSubmit(ti);
db.SubmitChanges();
tmpid = ti.TI_ID;
//模板列信息
int idex = 0;
foreach (var i in lbxColsRight.Items)
{
idex++;
//insert tmplate cols
COLINFO ci = (COLINFO)i;
TEMPLATE_COLUMN tc = new TEMPLATE_COLUMN
{
TC_CIID = ci.CI_ID,
TC_TIID = ti.TI_ID,
TC_ORDER = idex,
};
db.TEMPLATE_COLUMNs.InsertOnSubmit(tc);
}
//update agentcurrcy set tmpID to new ID
db.SubmitChanges();
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, this.funName, MessageBoxButtons.OK);
return(false);
}
}
private void btnImport_Click(object sender, EventArgs e)
{
btnImport.Enabled = false; //防止误操作
dgvSals.Rows.Clear(); //清空原数据
//检查数据是否填写
if (checkInput() == false)
{
MessageBox.Show("请检查输入项", this.funName, MessageBoxButtons.OK);
btnImport.Enabled = true;
return;
}
if (cbxAgentCs.SelectedItem == null)
{
MessageBox.Show("请选择国别", this.funName, MessageBoxButtons.OK);
btnImport.Enabled = true;
return;
}
//参数缓存保存至文件
TmpData td = new TmpData
{
Title1 = txtYearTitle1.Text.Trim(),
Title2 = txtYearTitle2.Text.Trim(),
Annu = txtYearAnnu.Text.Trim(),
Rate1 = txtERate1.Text.Trim(),
Rate2 = txtERate2.Text.Trim(),
Date1 = dtDeclare.Value,
Date2 = dtTableOn.Value
};
SaveToFile(td);
//获取模板
int tmpID = (int)db.AGENT_COUNTRies.SingleOrDefault(a => a.AC_ID == ((AGENT_COUNTRY)cbxAgentCs.SelectedItem).AC_ID).AC_TIID;
TEMPLATE_INFO ti = db.TEMPLATE_INFOs.SingleOrDefault(a => a.TI_ID == tmpID);
//获取年金限额
decimal YearAnnuLimit = Convert.ToDecimal(txtYearAnnu.Text.Trim());
ExcelHelper excel = new ExcelHelper();
try
{
excel.OpenFile(txtDir.Text, 1, true);
int rowCount = excel.GetDataRowsCount((int)ti.TI_DATAROW);
//检查列数是否一致
if (excel.GetColumnsCount() != ti.TI_COLSCOUNT)
{
MessageBox.Show("文件输入项与模板列数不一致，请检查后再导入");
btnImport.Enabled = true;
return;
}
//检查表头是否一致
if (!cbxNeedCheck.Checked)
{
if (TemplateUtil.CheckTitle(tmpID, excel.GetHeader((int)ti.TI_HEARDERROW)) == false)
{
MessageBox.Show("导入文件表头与模板不一致，请检查", this.funName, MessageBoxButtons.OK);
btnImport.Enabled = true;
return;
}
}
//获取EXCEL薪资数据,姓名为KEY,各月数据LIST
Dictionary <string, List <List <string> > > lists = excel.GetRows((int)ti.TI_DATAROW, rowCount, SysUtil.GetExcelColumnIntValue(ti.TI_NAMECOL));
//检查纳税人是否存在
List <string> namenotexsist = new List <string>();
foreach (var name in lists.Keys)
{
if (namenotexsist.Contains(name) == false)
{
if (db.TAX_PLAYERs.SingleOrDefault(a => a.TP_NAME == name && a.TP_ACID == this.ACID) == null)
{
namenotexsist.Add(name);
}
}
}
if (namenotexsist.Count > 0)
{
MessageBox.Show("以下员工信息尚不存在：" + string.Join <string>(";", namenotexsist), this.funName);
btnImport.Enabled = true;
return;
}
Names = lists.Keys.ToList(); //需要导入的已纳税人姓名,下述按姓名
TaxAlready = new Dictionary <string, decimal>(); //已纳税数据缓存
decimal rate2 = Convert.ToDecimal(txtERate2.Text); //美元人民币汇率
foreach (var item in lists) //每个纳税人
{
string name = item.Key;
var value = item.Value;
foreach (var v in value) //每个纳税人,每月记录
{
var newv = v.ToList(); //每个纳税人,每月每项数据
string sBonus = v[SysUtil.GetExcelColumnIntValue(ti.TI_BONUSCOL) - 1]; //年度奖金
decimal annu = Convert.ToDecimal(v[SysUtil.GetExcelColumnIntValue(ti.TI_ANNUCOL) - 1]); //企业年金
decimal fortax = FormulaC.GetCal(ti.TI_SALF, v); //计算税前
if (Math.Abs(annu) > Math.Abs(YearAnnuLimit)) //企业年金与年金限额比较
{
fortax += (Math.Abs(annu) - Math.Abs(YearAnnuLimit));
}
newv.Insert(0, name); //姓名
newv.Insert(1, v[SysUtil.GetExcelColumnIntValue(ti.TI_MONTHCOL) - 1]); //月份
newv.Insert(2, fortax.ToString()); //税前
newv.Insert(3, v[SysUtil.GetExcelColumnIntValue(ti.TI_REALCOL) - 1]); //实发美元
newv.Insert(4, (Convert.ToDecimal(newv[3]) * rate2).ToString("")); //实发RMB
newv.Insert(5, ""); //已纳税美元
newv.Insert(6, ""); //已纳税RMB
newv.Insert(7, v[SysUtil.GetExcelColumnIntValue(ti.TI_BONUSCOL) - 1]); //奖金
object[] oA = new object[] { false }; //首项为是否选中
object[] oB = oA.Concat(newv.ToArray()).ToArray();
dgvSals.Rows.Add(oB.ToArray()); //加入数据表显示
}
}
//显示数据统计信息
UpdateInfo();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Close();
btnImport.Enabled = true;
}
}