/// <summary>
/// 已纳税人导入方式
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnImport_Click(object sender, EventArgs e)
{
if (checkItemsInFile())
{
ExcelHelper excel = new ExcelHelper();
try
{
excel.OpenFile(txtDir.Text, 1, true);
int rows = excel.GetRowsCount();
int i = 2;
for (; i <= rows; i++) //check first
{
List <string> sl = excel.GetValue(i, 1, i, 5);
int acid = SysUtil.GetAgentCID(sl[0], sl[1]);
if (acid == -1)
{
MessageBox.Show(sl[1] + "国别记录不存在，请检查", this.funName);
//excel.Close();
return;
}
if (db.TAX_PLAYERs.SingleOrDefault(a => (a.TP_ACID == acid) && (a.TP_NAME == sl[2].Replace(" ", string.Empty))) != null)
{
MessageBox.Show(sl[2] + "此纳税人已存在，请检查", this.funName);
//excel.Close();
return;
}
}
i = 2;
for (; i <= rows; i++)
{
List <string> sl = excel.GetValue(i, 1, i, 5);
TAX_PLAYER tp = new TAX_PLAYER
{
TP_ACID = SysUtil.GetAgentCID(sl[0], sl[1]),
TP_AIID = SysUtil.GetAgentID(sl[0]),
TP_NAME = sl[2].Replace(" ", string.Empty),
TP_ENNAME = sl[3],
TP_IDNUMBER = sl[4],
};
db.TAX_PLAYERs.InsertOnSubmit(tp);
}
db.SubmitChanges();
//excel.Close();
loadPlayers();
MessageBox.Show("导入成功,导入记录" + (rows - 1) + "条", this.funName);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Close();
}
}
}
/* Modified by cyq 20160331 */
/************ Begin *********/
///// <summary>
///// 将报表3数据写入word文件
///// </summary>
///// <param name="ri"></param>
///// <param name="ai"></param>
///// <param name="tp"></param>
//public void WriteData( REPORT_INFO ri, AGENT_INFO ai,TAX_PLAYER tp)
//{
// object replacetext;
// //replacetext = ai.AI_NAME;
// FindAndReplace("<Y>", ri.RI_TABLETIME.ToString("yyyy"));
// FindAndReplace("<M>", ri.RI_TABLETIME.ToString("MM"));
// FindAndReplace("<D>", ri.RI_TABLETIME.ToString("dd"));
// FindAndReplace("<YEAR>", ri.RI_AYNAME);
// FindAndReplace("<EM>", ai.AI_NAME);
// FindAndReplace("<TAXID>", ai.AI_TAXID);
// FindAndReplace("<INDUS>", ai.AI_INDUSTRY);
// FindAndReplace("<ADDR>", ai.AI_ADDRESS);
// FindAndReplace("<POSTCODE>", ai.AI_POSTCODE);
// FindAndReplace("<TEL>", ai.AI_TEL);
// FindAndReplace("<WIT>", "0");
// FindAndReplace("<OUT>", ri.RI_SUMS.ToString("N2"));
// FindAndReplace("<TOT>", ri.RI_SUMS.ToString("N2"));
// FindAndReplace("<SAS>", ri.RI_SUMTAXSALARY.ToString("N2"));
// FindAndReplace("<TAX>", ri.RI_SUMTAXRMB.ToString("N2"));
// FindAndReplace("<PRE>", ((ri.RI_NEEDPACKREAL < 0) ? decimal.Zero : (decimal)ri.RI_NEEDPACKREAL).ToString("N2"));//已扣缴税额
// if (ri.RI_SUMTAXRMB < ri.RI_SUMTAXALREADYRMB + ri.RI_USEALL)
// {
// FindAndReplace("<CRE>", (ri.RI_SUMTAXRMB).ToString("N2")); //TODO
// }
// else
// {
// FindAndReplace("<CRE>", ((decimal)(ri.RI_SUMTAXALREADYRMB + ri.RI_USEALL)).ToString("N2")); //TODO
// }
// FindAndReplace("<NAME>", tp.TP_NAME);
// string sHolder = "!\"#$%&*+=|{}[]`~_/";
// object findtext;
// for (int i = 0; i < sHolder.Length; i++)
// {
// replacetext = tp.TP_IDNUMBER.Substring(i, 1);
// findtext = sHolder.Substring(i, 1);
// FindAndReplace(findtext, replacetext);
// }
// aDoc.Save();
//}
/// <summary>
/// 将报表3数据写入word文件
/// </summary>
/// <param name="ri"></param>
/// <param name="ai"></param>
/// <param name="tp"></param>
public void WriteData(REPORT_INFO ri, AGENT_INFO ai, TAX_PLAYER tp, decimal internalTotal, decimal externalTotal)
{
object replacetext;
//replacetext = ai.AI_NAME;
FindAndReplace("<Y>", ri.RI_TABLETIME.ToString("yyyy"));
FindAndReplace("<M>", ri.RI_TABLETIME.ToString("MM"));
FindAndReplace("<D>", ri.RI_TABLETIME.ToString("dd"));
FindAndReplace("<YEAR>", ri.RI_AYNAME);
FindAndReplace("<EM>", ai.AI_NAME);
FindAndReplace("<TAXID>", ai.AI_TAXID);
FindAndReplace("<INDUS>", ai.AI_INDUSTRY);
FindAndReplace("<ADDR>", ai.AI_ADDRESS);
FindAndReplace("<POSTCODE>", ai.AI_POSTCODE);
FindAndReplace("<TEL>", ai.AI_TEL);
/* Modified by cyq 20160331 */
/************ Begin *********/
//FindAndReplace("<WIT>", "0");
//FindAndReplace("<OUT>", ri.RI_SUMS.ToString("N2"));
FindAndReplace("<WIT>", internalTotal.ToString("N2"));
//FindAndReplace("<OUT>", externalTotal.ToString("N2"));
FindAndReplace("<OUT>", (ri.RI_SUMS - internalTotal).ToString("N2"));
/************ End *********/
FindAndReplace("<TOT>", ri.RI_SUMS.ToString("N2"));
FindAndReplace("<SAS>", ri.RI_SUMTAXSALARY.ToString("N2"));
FindAndReplace("<TAX>", ri.RI_SUMTAXRMB.ToString("N2"));
FindAndReplace("<PRE>", ((ri.RI_NEEDPACKREAL < 0) ? decimal.Zero : (decimal)ri.RI_NEEDPACKREAL).ToString("N2"));//已扣缴税额
if (ri.RI_SUMTAXRMB < ri.RI_SUMTAXALREADYRMB + ri.RI_USEALL)
{
FindAndReplace("<CRE>", (ri.RI_SUMTAXRMB).ToString("N2")); //TODO
}
else
{
FindAndReplace("<CRE>", ((decimal)(ri.RI_SUMTAXALREADYRMB + ri.RI_USEALL)).ToString("N2")); //TODO
}
FindAndReplace("<NAME>", tp.TP_NAME);
string sHolder = "!\"#$%&*+=|{}[]`~_/";
object findtext;
for (int i = 0; i < sHolder.Length; i++)
{
replacetext = tp.TP_IDNUMBER.Substring(i, 1);
findtext = sHolder.Substring(i, 1);
FindAndReplace(findtext, replacetext);
}
aDoc.Save();
}
private void frmPlayerAssitant_Load(object sender, EventArgs e)
{
loadAgents();
this.Text = funName;
if (this.Flag == "UPDATE" && this.TaxPlayerID != null)
{
btnUpdate.Text = "更改";
TAX_PLAYER tp = db.TAX_PLAYERs.Single(a => a.TP_ID == this.TaxPlayerID);
if (tp != null)
{
txtName.Text = tp.TP_NAME;
txtEnName.Text = tp.TP_ENNAME;
txtID.Text = tp.TP_IDNUMBER;
cbxAgents.SelectedValue = tp.TP_AIID;
}
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
if (checkInput() != true)
{
MessageBox.Show("请检查输入项", this.funName, MessageBoxButtons.OK);
return;
}
if (this.Flag == "NEW") //新建
{
TAX_PLAYER t = db.TAX_PLAYERs.SingleOrDefault(a => (a.TP_ACID == ((AGENT_COUNTRY)cbxAgentCs.SelectedItem).AC_ID) && (a.TP_NAME == txtName.Text.Trim()));
if (t != null)
{
MessageBox.Show(this.funName, "此人已在该国别中存在，请检查", MessageBoxButtons.OK);
return;
}
TAX_PLAYER tp = new TAX_PLAYER
{
TP_NAME = txtName.Text.Trim(),
TP_ENNAME = txtEnName.Text.Trim(),
TP_IDNUMBER = txtID.Text.Trim(),
TP_AIID = (int)cbxAgents.SelectedValue,
TP_ACID = ((AGENT_COUNTRY)cbxAgentCs.SelectedItem).AC_ID
};
db.TAX_PLAYERs.InsertOnSubmit(tp);
}
else if (this.Flag == "UPDATE") //修改
{
TAX_PLAYER tp = db.TAX_PLAYERs.Single(a => a.TP_ID == this.TaxPlayerID);
if (tp != null)
{
tp.TP_NAME = txtName.Text.Trim();
tp.TP_ENNAME = txtEnName.Text.Trim();
tp.TP_IDNUMBER = txtID.Text.Trim();
tp.TP_AIID = (int)cbxAgents.SelectedValue;
tp.TP_ACID = ((AGENT_COUNTRY)cbxAgentCs.SelectedItem).AC_ID;
}
}
db.SubmitChanges();
this.DialogResult = System.Windows.Forms.DialogResult.OK;
}
/// <summary>
/// 报表导出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnEm_Click(object sender, EventArgs e)
{
//*** Modified by CYQ on 2018-07-26 ***//
// 根据财通嘉鑫要求，增加有关无身份证号的报税申请人处理
//*** Begin ***//
TAX_PLAYER currTP = db.TAX_PLAYERs.SingleOrDefault(ai => ai.TP_ID == RW1.Base.RI_TPID);
#region 用户输入无身份证号的报税申请人的身份证号
string id = string.Empty; // 申报人身份证号
if (string.IsNullOrEmpty(currTP.TP_IDNUMBER))
{
string s = "请输入申报人身份证号";
if (InputDialog.ShowInputDialog(ref s) == DialogResult.OK)
{
id = s.Trim();
}
}
else
{
id = currTP.TP_IDNUMBER;
}
if (id != string.Empty)
{
currTP.TP_IDNUMBER = id;
db.SubmitChanges();
}
else
{
MessageBox.Show("未获取到有效的身份证号信息，请重新输入", "消息",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
#endregion
//*** End ***//
fbdPath.Description = "将导出文件保存到...";
DialogResult result = fbdPath.ShowDialog();
if (result != System.Windows.Forms.DialogResult.OK)
{
return;
}
Assembly assembly = Assembly.GetExecutingAssembly();
Assembly asm = Assembly.GetExecutingAssembly();
string file = string.Format("{0}.Resources.ReportTemplate.xlsx", asm.GetName().Name);
Stream fileStream = asm.GetManifestResourceStream(file);
string filename = fbdPath.SelectedPath + string.Format("\\{0}-{1}_{2}.xlsx", RW1.ACNAME, RW1.TPNAME, DateTime.Now.ToString("yyyyMMddHHmm"));
SaveStreamToFile(filename, fileStream);
ExcelHelper excel = new ExcelHelper();
//报表一
excel.OpenFile(filename, 1);
excel.SetValue(1, 2, string.Format("{0}-{1}{2}工资单", RW1.ACNAME, RW1.TPNAME, RW1.Base.RI_YEARTITLE1));
excel.SetValue(4, 6, RW1.Base.RI_CURRENTNAME);
var data = RW1.Details.OrderBy(a => a.R1_MONTH).ToList();
//load data;
int i = 0;
foreach (var d in data)
{
string[] tmp = new string[] { d.R1_MONTH, d.R1_FORTAX.ToString(), d.R1_REALDOLLOR.ToString(), d.R1_REALRMB.ToString(), d.R1_TAXALREADY.ToString(), d.R1_TAXALREADYRMB.ToString(), d.R1_SALARYRMB.ToString() };
excel.SetValue(5 + i, 2, tmp);
i++;
}
excel.SetValue(17, 3, RW1.Base.RI_BONUS.ToString());
excel.SetValue(18, 2, RW1.Base.RI_NOTE1);
excel.SetValue(19, 2, RW1.Base.RI_NOTE2);
/* Modified by cyq 20160331 */
/************ Begin *********/
excel.SetFontNameSize(21, 2, "宋体", 11);
excel.SetFontNameSize(21, 4, "宋体", 11);
excel.SetFontNameSize(23, 2, "宋体", 11);
excel.SetFontNameSize(23, 4, "宋体", 11);
excel.SetValue(21, 2, "编制日期： " + RW1.Base.RI_TABLETIME.ToLongDateString().ToString());
excel.SetValue(21, 4, "复核日期： " + RW1.Base.RI_TABLETIME.AddDays(2).ToLongDateString().ToString());
excel.SetValue(23, 2, "编制人： " + txtScheduler.Text.Trim());
excel.SetValue(23, 4, "复核人： " + txtReviewer.Text.Trim());
/************ End *********/
//报表二
excel.ChangeSheet(2);
excel.SetValue(3, 1, string.Format("{0}--工资个税 {1}", RW2.TPNAME, RW2.Base.RI_YEARTITLE2));
var data2 = RW2.Details.OrderBy(a => a.R2_MONTH).ToList();
i = 0;
foreach (var d in data2)
{
string[] tmp = new string[] { d.R2_MONTH, d.R2_SALARY.ToString(), d.R2_MINUS.ToString(), d.R2_TAXSALARY.ToString(), string.Format("{0}%", d.R2_TAXRATE), d.R2_QUICK.ToString(), d.R2_NEED.ToString() };
excel.SetValue(7 + i, 1, tmp);
i++;
}
excel.SetValue(19, 2, RW2.Base.RI_BONUS.ToString());
excel.SetValue(19, 4, RW2.Base.RI_BONUS.ToString());
TAX_RATE tr = SysUtil.GetTaxRate((int)RW2.Base.RI_BONUSTAXRATEID);
excel.SetValue(19, 5, string.Format("{0}%", tr.TR_RATE));
excel.SetValue(19, 6, tr.TR_QUICH.ToString());
excel.SetValue(19, 7, RW2.Base.RI_BONUSTAX.ToString());
excel.SetValue(20, 2, RW2.Base.RI_SUMS.ToString());
excel.SetValue(20, 4, RW2.Base.RI_SUMTAXSALARY.ToString());
excel.SetValue(20, 7, RW2.Base.RI_SUMTAXRMB.ToString());
excel.SetValue(20, 8, RW2.Base.RI_SUMTAXALREADYRMB.ToString());
// Modified by CYQ on 2018-09-10
// 根据财通嘉鑫要求，将“本年已缴/以前留抵”拆分为“境内已纳税额”和“以前留抵税额”
// Begin
//excel.SetValue(20, 9, RW2.Base.RI_USEALL.ToString());
excel.SetValue(20, 9, (RW2.Base.RI_USE2).ToString()); // 境内已纳税额
excel.SetValue(20, 10, (RW2.Base.RI_USEALL - RW2.Base.RI_USE2).ToString()); // 以前留抵税额
//*** Modified by CYQ on 2018-07-13 ***//
//
//*** Begin ***//
//excel.SetValue(20, 10, RW2.Base.RI_NEEDPACKREAL.ToString());
//if (RW2.Base.RI_NEEDPACKREAL.HasValue &&
// RW2.Base.RI_NEEDPACKREAL.Value > 0)
//{
// excel.SetValue(20, 10, RW2.Base.RI_NEEDPACKREAL.ToString());
//} // end if
//if (RW2.Base.RI_NEEDPACKREAL.HasValue)
//{
// if (RW2.Base.RI_NEEDPACKREAL.Value > 0)
// {
// excel.SetValue(20, 10, RW2.Base.RI_NEEDPACKREAL.ToString());
// } // end if
// else
// {
// excel.SetValue(20, 10, "0.00");
// } // end else
//}
if (RW2.Base.RI_NEEDPACKREAL.HasValue)
{
if (RW2.Base.RI_NEEDPACKREAL.Value > 0)
{
excel.SetValue(20, 11, RW2.Base.RI_NEEDPACKREAL.ToString());
} // end if
else
{
excel.SetValue(20, 11, "0.00");
} // end else
}
// End
//*** End ***//
excel.SetValue(22, 1, RW2.Base.RI_NOTE1);
excel.SetValue(23, 1, string.Format(" {0}", RW2.Base.RI_NOTE2));
/* Modified by cyq 20160331 */
/************ Begin *********/
excel.SetFontNameSize(25, 2, "宋体", 11);
excel.SetFontNameSize(25, 4, "宋体", 11);
excel.SetFontNameSize(27, 2, "宋体", 11);
excel.SetFontNameSize(27, 4, "宋体", 11);
excel.SetValue(25, 2, "编制日期： " + RW1.Base.RI_TABLETIME.ToLongDateString().ToString());
excel.SetValue(25, 4, "复核日期： " + RW1.Base.RI_TABLETIME.AddDays(2).ToLongDateString().ToString());
excel.SetValue(27, 2, "编制人： " + txtScheduler.Text.Trim());
excel.SetValue(27, 4, "复核人： " + txtReviewer.Text.Trim());
/************ End *********/
excel.Close();
//生成报表三
if (RW2.Base.RI_MOUNTHCOUNT == 12)
{
filename = fbdPath.SelectedPath + string.Format("\\{0}-{1}_{2}.doc", RW1.ACNAME, RW1.TPNAME, DateTime.Now.ToString("yyyyMMddHHmm"));
File.WriteAllBytes(filename, TaxDemo.Properties.Resources.Template3);
AGENT_INFO ai = SysUtil.GetAgentInfo(RW1.Base.RI_ACID);
TAX_PLAYER tp = SysUtil.GetTaxPlayer(RW1.Base.RI_TPID);
WordUtil word = new WordUtil();
{
word.Open(filename);
/* Modified by cyq 20160331 */
/************ Begin *********/
word.WriteData(RW1.Base, ai, tp, InternalTotal, ExternalTotal);
/************ End *********/
word.Close();
}
}
MessageBox.Show("导出成功", this.funName);
if (cbxOpen.Checked)
{
Process.Start(fbdPath.SelectedPath);
}
}
