private void auto()
{
qg_label1.Text = pro3.Substring(0, 4) + "年" + pro3.Substring(4, 2) + "月";
WaitFormService.Show();
WaitFormService.SetText("正在查询工作票信息,请稍候……");
grid_dayworksql.DataSource = dt_daywork;
WaitFormService.Close();
}
/// 生成一个凭证的汇总表
/// </summary>
/// <param name="pro3begin">开始月份</param>
/// <param name="pro3end">结束月份</param>
/// <param name="pzsubid">查询的财务科目ID</param>
/// <param name="monthjytt">为为表示查询一般的业务流程，为1表示是为了扎帐进行查询，这时accountacc的查询凭证信息隐藏模式应该为3</param>
/// <param name="wfilialeid">是“企业”还是“学校”</param>
/// <returns></returns>
private DataTable customercodingsubbillother(string pro3begin, string pro3end, string pzsubid, int monthjytt, string wfilialeid)
{
DataTable dt_subbill = null;
WaitFormService.Show();
WaitFormService.SetText("正在查询凭证信息,请稍候…………");
try
{
DataTable dt;
DataRow dt_row = null;//增加拷贝记录时用到
accountacc.accountacc_one accone = new accountacc.accountacc_one();
string subfialtemp1 = "";
if (monthjytt == 1)
{
subfialtemp1 = "3";
}
else
{
subfialtemp1 = "5";
}
DataTable dt_subbillx1 = accone.accountaccone(pro3begin, pro3end, "", pzsubid, "", "", "", "", subfialtemp1, "", "", "", "1");
dt_subbill = dt_subbillx1.DefaultView.ToTable(true, "ID", "票据号", "凭证号", "附件数", "日期", "月份", "状态ID", "状态", "类型ID", "凭证类型", "属性ID", "属性中文"
, "主管ID", "财务主管", "制单ID", "制单", "审核ID", "审核", "记帐ID", "记帐", "分ID"
, "引用属性", "公司ID", "员工ID", "产品ID"
, "摘要", "科目ID", "反科目ID", "次科目借贷", "借贷方向", "数量");
dt_subbill.Clear();//清除表中的所有记录
DataColumn dc1 = new DataColumn("科目名称", Type.GetType("System.String"));
dt_subbill.Columns.Add(dc1);
dc1 = new DataColumn("借方金额", Type.GetType("System.Decimal"));
dt_subbill.Columns.Add(dc1);
dc1 = new DataColumn("贷方金额", Type.GetType("System.Decimal"));
dt_subbill.Columns.Add(dc1);
dc1 = new DataColumn("排序", Type.GetType("System.Int32"));
dc1.DefaultValue = 1;
dt_subbill.Columns.Add(dc1);
DataRow[] row_1 = dt_subbillx1.Select("借贷方向=" + 1);
for (int i = 0; i < row_1.Count(); i++)
{
dt_row = dt_subbill.NewRow();
dt_subbill.Rows.Add(dt_row);
}
}
catch (Exception ex)
{
ex.errormess();
}
WaitFormService.Close();
return(dt_subbill);
}
private void customerwcodingsub_Shown(object sender, EventArgs e)
{
WaitFormService.Show();
wcoding.wcodingfinance wcoding = new newfeixiang.wcoding.wcodingfinance();
DataTable dt = wcoding.wcodingfinancetreesql4(begin_class.allmax, "1", 1, "");
WaitFormService.Close();
qg_grid_tree1.DataSource = dt;
}
private void ExecWaitForm()
{
try
{
WaitFormService.Show();
}
catch (Exception ex)
{
WaitFormService.Close();
}
}
private void ExecWaitForm()
{
try
{
WaitFormService.Show();
}
catch (Exception ex)
{
Log4NetHelper.WriteErrorLog(ex.Message);
}
}
private void qg_dy_Click(object sender, EventArgs e)
{
try
{
//string dy_title = "我是标题";
//string dy_month = "201612";
Boolean findjytt = false;
Control con_Form = this.Parent;
foreach (Control trol in con_Form.Controls)
{
if (trol is qg_grid || trol is qg_grid_tree)
{
findjytt = true;
//dt = (DataTable)(((qg_grid)trol).DataSource);
TestDelegate d = Test;
//other other = new other();
//string ly = "数据正在导出，请稍候……";
//other.winwait((qg_grid)trol, ly);
WaitFormService.Show();
WaitFormService.SetText("正在做导出Excel的准备,请稍候……");
d.BeginInvoke((DataGridView)trol, dy_title, dy_month, null, null);
//sqlconn.Cs_Datatable.ExportExcel((qg_grid)trol, dy_title, dy_month);
Thread.Sleep(3000);
WaitFormService.Close();
//other.winwait((qg_grid)trol, "");
break;
}
}
if (!findjytt)
{
MessageBox.Show("没有在本表单查询到需要导出的数据表！");
}
}
catch (Exception ex)
{
ex.errormess();
}
}
private void qg_button1_Click(object sender, EventArgs e)
{
try
{
DataTable dt_all = a_sqlconn.dt_excel.GetDataFromExcelByCom();
if (dt_all == null)
{
return;
}
WaitFormService.Show();
WaitFormService.SetText("正在将图书目录的数据内容格式统一化！");
//将所有内容清除空格，全角转半角
#region
for (int i = 0; i < dt_all.Rows.Count; i++)
{
for (int j = 0; j < dt_all.Columns.Count; j++)
{
//使用正则表达式.你能使用Regex.Replace方法, 它将所有匹配的替换为指定的字符
//.在这个例子中,使用正则表达式匹配符"\s",它将匹配任何空格包含在这个字符串里C#空格, tab字符, 换行符和新行(newline).
dt_all.Rows[i][j] = Regex.Replace(dt_all.Rows[i][j].ToString(), @"\s", "").To_X_DBC();
//最后再将全角转换为半角
}
}
#endregion
//将所有内容清除空格，全角转半角
WaitFormService.Close();
DataColumn dc1 = new DataColumn("删除记号", Type.GetType("System.Int32"));
dc1.DefaultValue = 0;
dt_all.Columns.Add(dc1);
#region 除有“ISBN号”的行之前的行
foreach (DataRow dr in dt_all.Rows)
{
bool deletejytt = true;
for (int i = 0; i < dt_all.Columns.Count; i++)
{
if (dr[i].ToString() == "ISBN号")
{
deletejytt = false;
}
}
if (deletejytt)
{
dr["删除记号"] = 1;
}
else
{
break;
}
}
DataRow[] row_update = dt_all.Select("删除记号=1");
for (int i = 0; i < row_update.Count(); i++)
{
dt_all.Rows.Remove(row_update[i]);
}
dt_all.Columns.Remove("删除记号");
if (dt_all.Rows.Count <= 0)
{
MessageBox.Show("导入的图书目录表不正常，没有“ISBN号”列，无法导出图书信息！");
return;
}
#endregion 除有“ISBN号”的行之前的行
//将标头整理为有“ISBN号”行的正常标头，同时删除没有记录的column
#region
DataTable dt_temp = new DataTable("Datas");
DataColumn dc = null;
dc = dt_temp.Columns.Add("删除记录", Type.GetType("System.String"));
DataRow dt_NO_1 = dt_all.Rows[0];
//确保“图书目录条目”没有重复
for (int i = 0; i < dt_all.Columns.Count; i++)
{
string nametemp1 = dt_NO_1[i].ToString().Trim();
if (nametemp1.Trim() == "")
{
continue;
}
for (int j = i + 1; j < dt_all.Columns.Count; j++)
{
string nametemp2 = dt_NO_1[j].ToString().Trim();
if (nametemp1.Trim() == nametemp2.Trim())
{
MessageBox.Show("有重复的图书目录条目：“" + nametemp1.Trim() + "”!\n\r 请先确保图书目录条目不重复！");
return;
}
}
}
//确保“图书目录条目”没有重复
//如果ISBN号列正常，则将column的名称正常化，否则将column删除
for (int i = 0; i < dt_all.Columns.Count; i++)
{
if (dt_NO_1[i].ToString().Trim() != "")
{
dt_all.Columns[i].ColumnName = dt_NO_1[i].ToString().Trim();
}
else
{
DataRow dt_temp_newRow = dt_temp.NewRow();
dt_temp_newRow["删除记录"] = dt_all.Columns[i].ColumnName.ToString().Trim();
dt_temp.Rows.Add(dt_temp_newRow);
}
}
foreach (DataRow dr in dt_temp.Rows)
{
string str = dr["删除记录"].ToString().Trim();
dt_all.Columns.Remove(str);
}
dt_all.Rows.RemoveAt(0);
#endregion
//如果图书目录表中的表头多次出现，则删除之
#region
dc1 = new DataColumn("删除记号", Type.GetType("System.Int32"));
dc1.DefaultValue = 0;
dt_all.Columns.Add(dc1);
foreach (DataRow dr in dt_all.Rows)
{
if (dr[0].ToString().Trim() == dt_all.Columns[0].ColumnName.ToString().Trim() &&
dr[1].ToString().Trim() == dt_all.Columns[1].ColumnName.ToString().Trim()
)
{
dr["删除记号"] = 1;
}
}
row_update = dt_all.Select("删除记号=1");
for (int i = 0; i < row_update.Count(); i++)
{
dt_all.Rows.Remove(row_update[i]);
}
dt_all.Columns.Remove("删除记号");
#endregion
//删除“ISBN号”为空的行
#region
row_update = dt_all.Select("ISBN号='' or (ISBN号 like '%合%' and ISBN号 like '%计%')");
for (int i = 0; i < row_update.Count(); i++)
{
dt_all.Rows.Remove(row_update[i]);
}
#endregion
//判断是否有重名的ISBN号 记录
#region
bool cfjytttemp1 = false;//是否有重复的姓名，false为没有重复
string cfstringalltemp1 = "";
int cfcounttemp1 = 0;
var groupByResult = dt_all.Rows.Cast <DataRow>().GroupBy <DataRow, string>(dr => dr["ISBN号"].ToString());
foreach (var rows in groupByResult)
{
if (rows.Count() > 1)
{
//以“姓名”为筛选条件的数据存在多条
cfjytttemp1 = true;
DataTable dttemp = rows.CopyToDataTable <DataRow>();
cfstringalltemp1 = cfstringalltemp1 + "\n\r " + dttemp.Rows[0]["ISBN号"].ToString();
cfcounttemp1 = cfcounttemp1 + 1;
}
}
if (cfjytttemp1)
{
MessageBox.Show("存在 " + cfcounttemp1.ToString() + " 条相同ISBN号的记录!请确保没有重复ISBN号，然后再导出图书目录表！\n\r " + cfstringalltemp1.Trim());
return;
}
#endregion
//判断是否有重名的姓名 记录
qg_grid1.DataSource = dt_all;
qg_grid1.AutoGenerateColumns = true;
}
catch (Exception ex)
{
ex.errormess();
}
}
private void qg_button2_Click(object sender, EventArgs e)
{
try
{
if (qg_grid1.DataSource == null)
{
MessageBox.Show("尚未从excel表中导入图书目录信息！");
return;
}
DataTable dt_all1 = (DataTable)qg_grid1.DataSource;
DataTable dt_all_copy = dt_all1.Copy();
if (dt_all_copy.Rows.Count <= 0)
{
MessageBox.Show("本次图书目录信息记录数为0，无法导入！");
return;
}
DialogResult err = MessageBox.Show("确认将本次图书目录信息提交至后台数据库吗？", "提示", MessageBoxButtons.OKCancel);
if (err == DialogResult.Cancel)
{
return;
}
WaitFormService.Show();
string sqlstring;
DataTable dt_temp;
//保存出版社信息
#region
//如果“出版社”为空，则取上一行的“出版社”
string bmname = dt_all_copy.Rows[0]["出版社"].ToString();
foreach (DataRow dr in dt_all_copy.Rows)
{
WaitFormService.SetText("正在整理出版社！\n\r " + bmname.Trim());
if (dr["出版社"].ToString().Trim() == "")
{
dr["出版社"] = bmname;
}
bmname = dr["出版社"].ToString().Trim();
}
DataColumn dc1 = new DataColumn("出版社ID", Type.GetType("System.Int64"));
dc1.DefaultValue = 0;
dt_all_copy.Columns.Add(dc1);
DataView dataView = dt_all_copy.DefaultView;
DataTable dt_Distinct = dataView.ToTable(true, "出版社");//注：其中ToTable（）的第一个参数为是否distinct
for (int i = 0; i < dt_Distinct.Rows.Count; i++)
{
string bmnametemp1 = dt_Distinct.Rows[i]["出版社"].ToString().Trim();
string namepytemp1 = MyPinYin.GetFirst(bmnametemp1);//
sqlstring = "select ID from press where 出版社=ltrim(rtrim('" + bmnametemp1.Trim() + "'))";
dt_temp = return_select(sqlstring);
if (dt_temp.Rows.Count <= 0)
{
sqlstring = "insert into press(出版社,拼音) values (ltrim(rtrim('" + bmnametemp1.Trim() + "')),ltrim(rtrim('" + namepytemp1.Trim() + "')))";
insert_update_delete(sqlstring);
sqlstring = "select ID from press where 出版社=ltrim(rtrim('" + bmnametemp1.Trim() + "'))";
dt_temp = return_select(sqlstring);
}
string bmid = dt_temp.Rows[0]["ID"].ToString();
DataRow[] row_update = dt_all_copy.Select("出版社='" + bmnametemp1.Trim() + "'");
for (int ii = 0; ii < row_update.Count(); ii++)
{
row_update[ii]["出版社ID"] = bmid.ToInt();
}
}
#endregion
//保存出版社信息
//保存图书类别至types
#region
dc1 = new DataColumn("类别ID", Type.GetType("System.Int64"));
dc1.DefaultValue = 0;
dt_all_copy.Columns.Add(dc1);
foreach (DataRow dr in dt_all_copy.Rows)
{
string pernametemp1 = dr["图书类别"].ToString().Trim();
string namepytemp1 = MyPinYin.GetFirst(pernametemp1);// ChineseToPinYin.ToPinYin_one(pernametemp1);
WaitFormService.SetText("正在整理图书类别信息！\n\r" + pernametemp1.Trim());
sqlstring = "select ID from types where 图书类别=ltrim(rtrim('" + pernametemp1.Trim() + "'))";
dt_temp = return_select(sqlstring);
if (dt_temp.Rows.Count <= 0)
{
sqlstring = "insert into types(图书类别,拼音) values (ltrim(rtrim('" + pernametemp1.Trim() + "')),ltrim(rtrim('" + namepytemp1.Trim() + "')))";
insert_update_delete(sqlstring);
sqlstring = "select ID from types where 图书类别=ltrim(rtrim('" + pernametemp1.Trim() + "'))";
dt_temp = return_select(sqlstring);
}
string perid = dt_temp.Rows[0]["ID"].ToString();
DataRow[] row_update = dt_all_copy.Select("图书类别='" + pernametemp1.Trim() + "'");
for (int ii = 0; ii < row_update.Count(); ii++)
{
row_update[ii]["类别ID"] = perid.ToInt();
}
}
#endregion
//保存图书类别至types
////保存图书信息
//#region
//for (int i = 0; i < dt_all_copy.Columns.Count; i++)
//{
// string column_name = dt_all_copy.Columns[i].ColumnName.ToString();
// WaitFormService.SetText("正在处理图书目录条目！\n\r" + column_name.Trim());
// //MessageBox.Show(column_name.Trim() + " 类型：" + dt_all_copy.Columns[i].DataType.ToString());
// sqlstring = "select ID from Subject_name where 条目名称=ltrim(rtrim('" + column_name.Trim() + "')) and 删除=0";
// dt_temp = return_select(sqlstring);
// if (dt_temp.Rows.Count <= 0)
// {
// sqlstring = "insert into subject_name(条目名称) values (ltrim(rtrim('" + column_name.Trim() + "')))";
// insert_update_delete(sqlstring);
// sqlstring = "select ID from Subject_name where 条目名称=ltrim(rtrim('" + column_name.Trim() + "')) and 删除=0";
// dt_temp = return_select(sqlstring);
// }
// string jectid = dt_temp.Rows[0]["ID"].ToString();
// //判断column的列datatype,只分为string和decimal
// object test = dt_all_copy.Compute("max(" + column_name.Trim() + ")", "");
// int column_type = 1;//为0表示是数字，为1表示是string
// if (test.ToString().IsInt() || test.ToString().IsDecimal())
// {
// column_type = 0;
// }
// //判断column的列datatype,只分为string和decimal
// sqlstring = "select ID from subject_mon where 月份='" + sql_month.Trim() + "' and 条目ID=" + jectid.Trim();
// dt_temp = return_select(sqlstring);
// if (dt_temp.Rows.Count <= 0)
// {
// sqlstring = "insert into subject_mon(月份,条目ID,排序,类型) values ('" + sql_month.Trim() + "'," + jectid.Trim() + "," + i.ToString() + "," + column_type + ")";
// insert_update_delete(sqlstring);
// }
//}
//#endregion
////保存工资条目信息
//将工资信息拷贝至后台数据库
#region
for (int i = 0; i < dt_all_copy.Rows.Count; i++)
{
string pernametemp1 = dt_all_copy.Rows[i]["书名"].ToString();
string bmidtempid = dt_all_copy.Rows[i]["出版社ID"].ToString();
string peridtempid = dt_all_copy.Rows[i]["类别ID"].ToString();
WaitFormService.SetText("正在拷贝第" + i.ToString() + "条图书记录/共" + dt_all_copy.Rows.Count.ToString() + "条记录！\n\r" + " 员工姓名：" + pernametemp1.ToString());
string lr1 = ""; //ISBN号
string lr2 = ""; //书名
string lr2_px = ""; //书名拼音
decimal lr3 = 0; //单价
string lr4 = ""; //出版社ID
DateTime lr5 = DateTime.Now; //出版日期
string lr6 = ""; //类别ID
int lr7 = 0; //册数
for (int j = 0; j < dt_all_copy.Columns.Count; j++)
{
string columnnametemp1 = dt_all_copy.Columns[j].ToString();
//if (columnnametemp1.Trim() == "出版社"
// || columnnametemp1.Trim() == "图书类别"
// || columnnametemp1.Trim() == "出版社ID"
// || columnnametemp1.Trim() == "类别ID"
// ) { continue; }
string dt_all_copy_one_z = dt_all_copy.Rows[i][j].ToString().Trim();
if (columnnametemp1.Trim() == "ISBN号")
{
lr1 = dt_all_copy_one_z;
}
if (columnnametemp1.Trim() == "书名")
{
lr2 = dt_all_copy_one_z;
lr2_px = MyPinYin.GetFirst(dt_all_copy_one_z);;
}
if (columnnametemp1.Trim() == "单价" && dt_all_copy_one_z != "" && dt_all_copy_one_z.IsDecimal())
{
lr3 = dt_all_copy_one_z.ToDecimal();
}
if (columnnametemp1.Trim() == "出版社ID")
{
lr4 = dt_all_copy_one_z;
}
if (columnnametemp1.Trim() == "出版日期" && dt_all_copy_one_z != "")
{
lr5 = Convert.ToDateTime(dt_all_copy_one_z);
}
if (columnnametemp1.Trim() == "类别ID")
{
lr6 = dt_all_copy_one_z;
}
if (columnnametemp1.Trim() == "册数" && dt_all_copy_one_z != "" && dt_all_copy_one_z.IsInt())
{
lr7 = dt_all_copy_one_z.ToInt();
}
//if (dt_all_copy_one_z.Trim() == "" || (dt_all_copy_one_z.IsDecimal() && dt_all_copy_one_z.ToDecimal() == 0)) { continue; }
//sqlstring = "select ID from press where 出版社=ltrim(rtrim('" + columnnametemp1.Trim() + "')) and 删除=0";
//dt_temp = return_select(sqlstring);
//if (dt_temp.Rows.Count <= 0)
//{
// MessageBox.Show("没有在后台数据库中查询到：" + columnnametemp1.Trim() + "的条目记录，请重新导入工资信息，或与系统管理员联系！");
// return;
//}
//string jectidtemp1 = dt_temp.Rows[0]["ID"].ToString();
//sqlstring = "select * from Subject_mon where 月份='" + sql_month.Trim() + "' and 条目ID=" + jectidtemp1.Trim();
//dt_temp = return_select(sqlstring);
//if (dt_temp.Rows.Count <= 0)
//{
// MessageBox.Show("没有在后台数据库中查询到：" + sql_month.Trim() + "月份的" + columnnametemp1.Trim() + "的条目记录，请重新导入工资信息，或与系统管理员联系！");
// return;
//}
//int ject_typetemp1 = dt_temp.Rows[0]["类型"].ToString().ToInt();
////如果单元格里是string,就用permoney_lr中的ID代替单元格里的内容
//if (ject_typetemp1 == 1)
//{
// sqlstring = "select ID from permoney_lr where 文字内容=ltrim(rtrim('" + dt_all_copy_one_z.Trim() + "'))";
// dt_temp = return_select(sqlstring);
// if (dt_temp.Rows.Count <= 0)
// {
// sqlstring = "insert into permoney_lr(文字内容) values (ltrim(rtrim('" + dt_all_copy_one_z.Trim() + "')))";
// insert_update_delete(sqlstring);
// sqlstring = "select ID from permoney_lr where 文字内容=ltrim(rtrim('" + dt_all_copy_one_z.Trim() + "'))";
// dt_temp = return_select(sqlstring);
// }
// dt_all_copy_one_z = dt_temp.Rows[0]["ID"].ToString();
//}
////MessageBox.Show(pernametemp1+" "+columnnametemp1 + " " + ject_typetemp1.ToString()+" "+ dt_all_copy_one_z);
//sqlstring = "insert into permoney(部门ID,员工ID,条目ID,金额,月份,排序) values ('" + bmidtempid + "','" + peridtempid + "','" + jectidtemp1 + "'," + dt_all_copy_one_z.ToDecimal().ToString() + ",'" + sql_month.Trim() + "'," + i.ToString() + ")";
//insert_update_delete(sqlstring);
}
sqlstring = "insert into booksall(ISBN号,书名,拼音,单价,出版社ID,出版日期,类别ID,册数"
+ ") values ("
+ "'" + lr1 + "','" + lr2 + "','" + lr2_px + "'," + lr3 + "," + lr4 + ",(convert(varchar(10),'" + lr5.ToShortDateString().ToString() + "',120))," + lr6 + "," + lr7 + ")";
insert_update_delete(sqlstring);
}
#endregion
//将工资信息拷贝至后台数据库
WaitFormService.Close();
MessageBox.Show("图书目录已经导入至后台数据库！");
}
catch (Exception ex)
{
ex.errormess();
}
}
/// 生成一个凭证的汇总表
/// </summary>
/// <param name="pro3begin">开始月份</param>
/// <param name="pro3end">结束月份</param>
/// <param name="pzlx">凭证类型"收入凭证""支出凭证""转帐凭证"</param>
/// <param name="pzsubid">查询的财务科目ID</param>
/// <param name="pzzt">凭证状态,为""表示全部,1=“已制单” 2=“已审核” 4=“帐务主管已审” 5=“已记帐” 9=“作废”</param>
/// <param name="pjxh">票据号</param>
/// <param name="ph1">票号</param>
/// <param name="subfial">
/// 为1表示不隐藏详细的凭证信息，
/// 为2表示隐藏详细的凭证信息,
/// 为3表示隐藏主凭证信息，
/// 为4表示查询的是该科目对应的科目信息,
/// 为5时与4的功能相同，
/// 不同之处是需要将所在科目的借方贷方金额互换（为了查询科目帐本需要）
/// </param>
/// <param name="accidall">凭证总ID</param>
/// <param name="accid">凭证ID</param>
/// <param name="pchall">凭证的批次号</param>
/// <param name="wfilialeid">是“企业”还是“学校”</param>
/// <param name="subsx">指代表的凭证的属性ID(表accounttype)</param>
/// <returns></returns>
#region
public DataTable accountaccone(string pro3begin, string pro3end, string pzlx, string pzsubid
, string pzzt, string pjxh, string ph1, string subsx, string subfial
, string accidall, string accid, string pchall, string wfilialeid)
{
WaitFormService.Show();
WaitFormService.SetText("正在查询凭证信息,请稍候…………");
DataTable dt_accountacc = null;
try
{
string sqlstring;
DataTable dt;
DataRow dt_row = null;//增加拷贝记录时用到
//如果查询的是全部的科目，则直接显示
if ((pzsubid == "") && subfial.ToInt() > 2)
{
subfial = "0";
}
//科目的编码
string pzsumbm = "";
if (pzsubid != "")
{
sqlstring = "select 编码 from customersub where ID='" + pzsubid + "'";
dt = return_select(sqlstring);
if (dt.Rows.Count > 0)
{
pzsumbm = dt.Rows[0]["编码"].ToString();
}
}
//科目的编码
sqlstring = "select ISNULL(分公司ID,'0') 分公司ID,分公司名称=ISNULL((select 分公司名称 from wfiliale where ID=accacc.分公司ID),'')"
+ ",ID,凭证票据号 票据号,凭证票号 凭证号,附件数"
+ ",ISNULL(原料采购ID,'0') 原料采购ID"
+ ",convert(varchar(10),日期,120) 日期,月份"
+ ",状态 状态ID,状态=(case when 状态=0 then '待制单' when 状态=1 then '制单' when 状态=3 then '已审核' when 状态=4 then '已记帐' when 状态=5 then '主管已审' when 状态=9 then '作废' else '' end)"
+ ",类型ID,凭证类型=ISNULL((select 凭证类型 from wcodingtypepz where iD=accacc.类型ID),'')"
+ ",属性ID,属性中文=ISNULL((select 中文解释 from accounttype where 凭证属性=accacc.属性ID),'')"
+ ",主管ID,财务主管=ISNULL((select 姓名 from person where ID=accacc.主管ID),'')"
+ ",制单ID,制单=ISNULL((select 姓名 from person where ID=accacc.制单ID),'')"
+ ",审核ID,审核=ISNULL((select 姓名 from person where ID=accacc.审核ID),'')"
+ ",记帐ID,记帐=ISNULL((select 姓名 from person where ID=accacc.记帐ID),'')"
+ " from accountacc accacc where ID is not null";
if (wfilialeid != "")
{
sqlstring = sqlstring + " and 分公司ID in (" + wfilialeid + ")";
}
if (pro3begin != "")
{
sqlstring = sqlstring + " and 月份>='" + pro3begin + "'";
}
if (pro3end != "")
{
sqlstring = sqlstring + " and 月份<='" + pro3end + "'";
}
if (pzlx != "")
{
sqlstring = sqlstring + " and 类型ID in (" + pzlx + "'";
}
if (pzsumbm != "")
{
int bmlen = pzsumbm.Trim().Length;
sqlstring = sqlstring + " and ID in (select 凭证总ID from account where 科目ID in (select ID from customersub where substring(LTRIM(RTRIM(编码)),1," + bmlen.ToString() + ")='" + pzsumbm.Trim() + "') and 属性=23)";
}
if (pzzt != "")
{
sqlstring = sqlstring + " and 状态 in (" + pzzt.Trim() + ")";
}
if (pjxh != "")
{
sqlstring = sqlstring + " and 凭证票据号 in (" + pjxh + ")";
}
if (ph1 != "")
{
sqlstring = sqlstring + " and 票号 in (" + ph1 + ")";
}
if (accidall != "")
{
sqlstring = sqlstring + " and ID in (select 凭证总ID from account where ID='" + accidall.Trim() + "')";
}
if (accid != "")
{
sqlstring = sqlstring + " and ID in (" + accid.Trim() + ")";
}
if (pchall != "")
{
sqlstring = sqlstring + " and 凭证批次号 in (" + pchall.Trim() + ")";
}
if (subsx != "")
{
sqlstring = sqlstring + " and 属性ID in (" + subsx.Trim() + ")";
}
DataTable dt_accountaccx1 = return_select(sqlstring);
DataColumn dc1 = new DataColumn("分ID", Type.GetType("System.Int64"));
dt_accountaccx1.Columns.Add(dc1);
dc1 = new DataColumn("摘要", Type.GetType("System.String"));
dt_accountaccx1.Columns.Add(dc1);
dc1 = new DataColumn("次科目借贷", Type.GetType("System.Int32"));
dc1.DefaultValue = 0;
dt_accountaccx1.Columns.Add(dc1);
dc1 = new DataColumn("科目编码", Type.GetType("System.String"));
dt_accountaccx1.Columns.Add(dc1);
dc1 = new DataColumn("科目ID", Type.GetType("System.Int64"));
dt_accountaccx1.Columns.Add(dc1);
dc1 = new DataColumn("借方科目", Type.GetType("System.String"));
dt_accountaccx1.Columns.Add(dc1);
dc1 = new DataColumn("贷方科目", Type.GetType("System.String"));
dt_accountaccx1.Columns.Add(dc1);
dc1 = new DataColumn("借方金额", Type.GetType("System.Decimal"));
dc1.DefaultValue = 0;
dt_accountaccx1.Columns.Add(dc1);
dc1 = new DataColumn("贷方金额", Type.GetType("System.Decimal"));
dc1.DefaultValue = 0;
dt_accountaccx1.Columns.Add(dc1);
dc1 = new DataColumn("借贷方向", Type.GetType("System.Int32"));
dc1.DefaultValue = 0;
dt_accountaccx1.Columns.Add(dc1);
dc1 = new DataColumn("引用属性", Type.GetType("System.Int32"));
dc1.DefaultValue = 0;
dt_accountaccx1.Columns.Add(dc1);
dc1 = new DataColumn("公司ID", Type.GetType("System.Int64"));
dt_accountaccx1.Columns.Add(dc1);
dc1 = new DataColumn("员工ID", Type.GetType("System.Int64"));
dt_accountaccx1.Columns.Add(dc1);
dc1 = new DataColumn("资产ID", Type.GetType("System.Int64"));
dt_accountaccx1.Columns.Add(dc1);
dc1 = new DataColumn("产品ID", Type.GetType("System.Int64"));
dt_accountaccx1.Columns.Add(dc1);
dc1 = new DataColumn("大类ID", Type.GetType("System.Int64"));
dt_accountaccx1.Columns.Add(dc1);
dc1 = new DataColumn("排序", Type.GetType("System.Int32"));
dc1.DefaultValue = 1;
dt_accountaccx1.Columns.Add(dc1);
//dc1 = new DataColumn("临时金额", Type.GetType("System.Decimal"));
//dc1.DefaultValue = 0;
//dt_accountaccx1.Columns.Add(dc1);
//dc1 = new DataColumn("临时科目", Type.GetType("System.Int64"));
//dt_accountaccx1.Columns.Add(dc1);
dc1 = new DataColumn("数量", Type.GetType("System.Decimal"));
dc1.DefaultValue = 0;
dt_accountaccx1.Columns.Add(dc1);
dc1 = new DataColumn("反科目ID", Type.GetType("System.String"));
dt_accountaccx1.Columns.Add(dc1);
dc1 = new DataColumn("凭证总排序", Type.GetType("System.Int64"));
dt_accountaccx1.Columns.Add(dc1);
DataView dataView = dt_accountaccx1.DefaultView;
DataTable dt_Distinct = dataView.ToTable(true, "ID", "属性ID", "类型ID", "状态ID", "月份");//注：其中ToTable（）的第一个参数为是否distinct
for (int i = 0; i < dt_Distinct.Rows.Count; i++)
{
string dt_dis_id = dt_Distinct.Rows[i]["ID"].ToString();
string dt_dis_sxid = dt_Distinct.Rows[i]["属性ID"].ToString();
string dt_dis_typeid = dt_Distinct.Rows[i]["类型ID"].ToString();
string dt_dis_ztid = dt_Distinct.Rows[i]["状态ID"].ToString();
string dt_dis_pro3 = dt_Distinct.Rows[i]["月份"].ToString();
sqlstring = "select ID,摘要,科目ID,借方科目=(case when 借贷方向=1 then 科目名称 else '' end),贷方科目=(case when 借贷方向=2 then 科目名称 else '' end)"
+ ",借方金额=(case when 借贷方向=1 then 金额 else 0 end),贷方金额=(case when 借贷方向=2 then 金额 else 0 end),借贷方向,引用属性,公司ID,员工ID,资产ID,产品ID,大类ID"
+ ",次科目借贷=(select 借贷方向 from wcodingtypesub where ID=(select 次科目ID from customersub where ID=temp.科目ID))"
+ ",数量"
+ ",科目编码"
+ " from ("
+ "select ID,摘要,金额,借贷方向,科目ID,科目编码,LTRIM(RTRIM(科目编码))+' '+LTRIM(RTRIM(名称)) 科目名称,引用属性,公司ID,员工ID,资产ID,产品ID,大类ID,数量"
+ " from ("
+ "select ID,ISNULL(原因,'') 摘要,金额,借贷方向,ISNULL(数量,0) 数量"
+ ",科目ID,科目编码=ISNULL((select 编码 from customersub where ID=acc.科目ID),'')"
+ ",名称=ISNULL((select "
+ "(case when 引用属性=0 then 名称"
+ " when 引用属性=1 then (select 公司名称 from customer where ID=cus.公司ID)"
+ " when 引用属性=2 then (select 姓名 from person where ID=cus.员工ID)"
+ " when 引用属性=3 then (select 类型名称 from wcodingtypegdzc where ID=cus.资产ID)"
+ " when 引用属性=4 then (select 产品名称 from pproduct where ID=cus.产品ID)"
+ " when 引用属性=5 then (select 大类 from types1 where ID=cus.大类ID)"
+ " else '' end)"
+ " from customersub cus where ID=acc.科目ID),'')"
+ ",引用属性,ISNULL(公司ID,'0') 公司ID,ISNULL(经手人,'0') 员工ID,ISNULL(资产ID,'0') 资产ID,ISNULL(产品ID,'0') 产品ID,ISNULL(大类ID,'0') 大类ID"
+ " from account acc where 凭证总ID='" + dt_dis_id + "' and 属性=23"
+ ") temp"
+ ") temp";
dt = return_select(sqlstring);
foreach (DataRow row in dt.Rows)
{
dt_row = dt_accountaccx1.NewRow();
dt_row["ID"] = dt_dis_id;
dt_row["月份"] = dt_dis_pro3;
dt_row["属性ID"] = dt_dis_sxid;
dt_row["类型ID"] = dt_dis_typeid;
dt_row["状态ID"] = dt_dis_ztid;
dt_row["分ID"] = row["ID"];
dt_row["摘要"] = row["摘要"];
dt_row["次科目借贷"] = row["次科目借贷"];
dt_row["科目ID"] = row["科目ID"];
dt_row["借方科目"] = row["借方科目"];
dt_row["贷方科目"] = row["贷方科目"];
dt_row["借方金额"] = row["借方金额"];
dt_row["贷方金额"] = row["贷方金额"];
dt_row["借贷方向"] = row["借贷方向"];
dt_row["引用属性"] = row["引用属性"];
dt_row["公司ID"] = row["公司ID"];
dt_row["员工ID"] = row["员工ID"];
dt_row["资产ID"] = row["资产ID"];
dt_row["产品ID"] = row["产品ID"];
dt_row["大类ID"] = row["大类ID"];
dt_row["数量"] = row["数量"];
dt_row["排序"] = 2;
dt_row["科目编码"] = row["科目编码"];
dt_accountaccx1.Rows.Add(dt_row);
}
}
#region &&对应的借贷方向的反科目ID
for (int i = 0; i < dt_accountaccx1.Rows.Count; i++)
{
string accidtempid = dt_accountaccx1.Rows[i]["ID"].ToString();
int jdfx1 = dt_accountaccx1.Rows[i]["借贷方向"].ToString().ToInt();
int jdfx_f = 0;
if (jdfx1 == 1)
{
jdfx_f = 2;
}
if (jdfx1 == 2)
{
jdfx_f = 1;
}
sqlstring = "select distinct 科目ID from account where 属性 = 23 and 凭证总ID = '" + accidtempid + "' and 借贷方向 = " + jdfx_f.ToString();
dt = return_select(sqlstring);
string km_f_id_all = "";
for (int j = 0; j < dt.Rows.Count; j++)
{
string accid_f_tempid = dt.Rows[j]["科目ID"].ToString();
if (km_f_id_all.NotIsNullOrEmpty())
{
km_f_id_all = km_f_id_all + ",";
}
km_f_id_all = km_f_id_all.Trim() + accid_f_tempid.Trim();
}
dt_accountaccx1.Rows[i]["反科目ID"] = km_f_id_all.Trim();
}
#endregion
#region 将表“编码加名称”装到表本表的借方科目或贷方科目名称中
dataView = dt_accountaccx1.DefaultView;
dt_Distinct = dataView.ToTable(true, "科目ID"); //注：其中ToTable（）的第一个参数为是否distinct
for (int i = 0; i < dt_Distinct.Rows.Count; i++)
{
string subidtempid = dt_Distinct.Rows[i]["科目ID"].ToString();
if (subidtempid.Trim() == "")
{
continue;
}
DataRow[] rows = begin_class.dt_cw_prg_all.Select("ID=" + subidtempid);
string bmtemp1 = "";
if (rows.Count() > 0)
{
bmtemp1 = rows[0]["编码加名称"].ToString();
}
DataRow[] row_1 = dt_accountaccx1.Select("科目ID=" + subidtempid + " and 借贷方向=1");
for (int j = 0; j < row_1.Count(); j++)
{
row_1[j]["借方科目"] = bmtemp1;
}
DataRow[] row_2 = dt_accountaccx1.Select("科目ID=" + subidtempid + " and 借贷方向=2");
for (int j = 0; j < row_2.Count(); j++)
{
row_2[j]["贷方科目"] = bmtemp1;
}
}
#endregion
#region 将排序=2的分记录的借方金额和贷方金额汇总后拷到排序=1的总记录中
var query = from t in dt_accountaccx1.AsEnumerable()
where t.Field <int>("排序") == 2
group t by t["ID"] into m
select new
{
ID = m.Key,
借方金额 = m.Sum(n => n.Field <decimal>("借方金额")),
贷方金额 = m.Sum(n => n.Field <decimal>("贷方金额"))
};
dt = Cs_Datatable.ToDataTable(query.ToList());
for (int i = 0; i < dt.Rows.Count; i++)
{
string accidtempid = dt.Rows[i]["ID"].ToString();
double je1temp1 = dt.Rows[i]["借方金额"].ToString().ToDouble();
double je1temp2 = dt.Rows[i]["贷方金额"].ToString().ToDouble();
if (accidtempid.Trim() == "")
{
continue;
}
DataRow[] row_3 = dt_accountaccx1.Select("ID=" + accidtempid + " and 排序=1");
for (int j = 0; j < row_3.Count(); j++)
{
row_3[j]["借方金额"] = je1temp1; row_3[j]["贷方金额"] = je1temp2;
}
}
#endregion
#region 如果查询的某一单一科目，则可能该科目的摘要为空，需要将该凭证的摘要拷贝
if (subfial == "3" || subfial == "4" || subfial == "5")
{
DataRow[] row_4 = dt_accountaccx1.Select("排序=2 and 摘要=''");
for (int i = 0; i < row_4.Count(); i++)
{
string accallidtempid = row_4[i]["ID"].ToString();
string subidtempid = row_4[i]["科目ID"].ToString();
DataRow[] row_5 = dt_accountaccx1.Select("ID=" + accallidtempid + " and 排序=2 and 摘要 not in ('')");
dt = row_5.CopyToDataTable();
dt_Distinct = dt.DefaultView.ToTable(true, "摘要");
string zyalltemp1 = "";
for (int j = 0; j < dt_Distinct.Rows.Count; j++)
{
if (zyalltemp1.NotIsNullOrEmpty())
{
zyalltemp1 = zyalltemp1.Trim() + "、";
}
zyalltemp1 = zyalltemp1.Trim() + row_5[j]["摘要"].ToString().Trim();
}
if (zyalltemp1.Trim().Length > 41)
{
zyalltemp1 = zyalltemp1.Substring(0, 40) + "……";
}
row_4[i]["摘要"] = zyalltemp1;
}
}
#endregion
//&&怪异的“5”，月底科目结帐时用，这时需要选出每个凭证中查询的科目的对应的其它的科目记录，但必须将其它的科目记录的科目ID替换成本科凭证中查询的科目ID，然后再将借方金额和贷方金额互换，然后再将借贷方向（1和2）互换
//查询某一科目的信息时，比如查询“现金”科目时
//比如：借方 贷方 借方金额 贷方金额
// 现金 1100
// 应付帐款 500
// 应付帐款 600
//就需要显示“应付帐款”的科目信息
#region
if (subfial == "3" || subfial == "5")
{
DataRow[] row_6 = dt_accountaccx1.Select("排序=1");
for (int i = 0; i < row_6.Count(); i++)
{
string accidalltempid = row_6[i]["ID"].ToString();
dt = dt_accountaccx1.Select("排序=2 and ID=" + accidalltempid + " and 科目编码 like '" + pzsumbm + "%'").CopyToDataTable();
dt_Distinct = dt.DefaultView.ToTable(true, "科目ID", "次科目借贷");
for (int j = 0; j < dt_Distinct.Rows.Count; j++)
{
string subidtempid = dt_Distinct.Rows[j]["科目ID"].ToString();
string kmjdtemp1 = dt_Distinct.Rows[j]["次科目借贷"].ToString();
DataRow[] row_7 = dt_accountaccx1.Select("排序=2 and ID=" + accidalltempid + " and 科目ID=" + subidtempid);
int jdfxtemp1 = row_7[0]["借贷方向"].ToString().ToInt();
//本科目在本张凭证中所在“借贷方向”的数目
DataRow[] row_8 = dt_accountaccx1.Select("排序=2 and ID=" + accidalltempid + " and 借贷方向=" + jdfxtemp1.ToString());
int jeuntemp1 = row_8.Count();
row_8 = dt_accountaccx1.Select("排序=2 and ID=" + accidalltempid + " and 借贷方向=" + (3 - jdfxtemp1).ToString());
int jeuntemp2 = row_8.Count();
if (jeuntemp1 == 0 || jeuntemp2 == 0)
{
continue;
}
//如果本科目为一对多的“一”的这一边，则需要将“一”边本科目ID，全部复制到“多”的一边的科目中，然后将“一”边的科目ID改为“0”（避免根据科目ID拷贝记录时两边都拷贝上）
if (jeuntemp2 > 1)
{
DataRow[] row_9 = dt_accountaccx1.Select("排序=2 and ID=" + accidalltempid + " and 科目ID=" + subidtempid + " and 借贷方向=" + jdfxtemp1.ToString());
row_9[0]["科目ID"] = 0;
int yysxtemp1 = row_9[0]["引用属性"].ToString().ToInt();
string cusidtempid = row_9[0]["公司ID"].ToString();
string peridtempid = row_9[0]["员工ID"].ToString();
DataRow[] row_10 = dt_accountaccx1.Select("排序=2 and ID=" + accidalltempid + " and 借贷方向=" + (3 - jdfxtemp1).ToString());
for (int k = 0; k < row_10.Count(); k++)
{
row_10[k]["次科目借贷"] = kmjdtemp1;
row_10[k]["借贷方向"] = jdfxtemp1;
row_10[k]["反科目ID"] = row_10[k]["科目ID"];
row_10[k]["科目ID"] = subidtempid;
Decimal decimaltemp1 = row_10[k]["借方金额"].ToString().ToDecimal();
row_10[k]["借方金额"] = row_10[k]["贷方金额"];
row_10[k]["贷方金额"] = decimaltemp1;
string kmidtempid = row_10[k]["借方科目"].ToString();
row_10[k]["借方科目"] = row_10[k]["贷方科目"];
row_10[k]["贷方科目"] = kmidtempid;
if (yysxtemp1 > 0)
{
row_10[k]["引用属性"] = yysxtemp1;
row_10[k]["公司ID"] = cusidtempid;
row_10[k]["员工ID"] = peridtempid;
}
}
}
else//如果本科目为多的一边（包含一对一），只需要将“一”边的科目名称拷贝，其它不变
{
DataRow[] row_11 = dt_accountaccx1.Select("排序=2 and ID=" + accidalltempid + " and 借贷方向=" + (3 - jdfxtemp1).ToString());
string kmnametemp1 = "";
if ((3 - jdfxtemp1) == 1)
{
kmnametemp1 = row_11[0]["借方科目"].ToString();
}
if ((3 - jdfxtemp1) == 2)
{
kmnametemp1 = row_11[0]["贷方科目"].ToString();
}
DataRow[] row_12 = dt_accountaccx1.Select("排序=2 and ID=" + accidalltempid + " and 科目ID=" + subidtempid + " and 借贷方向=" + jdfxtemp1.ToString());
for (int k = 0; k < row_12.Count(); k++)
{
if (jdfxtemp1 == 1)
{
row_12[k]["借方科目"] = kmnametemp1;
}
if (jdfxtemp1 == 2)
{
row_12[k]["贷方科目"] = kmnametemp1;
}
}
}
}
}
}
#endregion
// 拷贝主主凭证信息
# region
if (subfial == "3" || subfial == "4" || subfial == "5")
{
DataRow[] row_13 = dt_accountaccx1.Select("排序=1");
for (int i = 0; i < row_13.Count(); i++)
{
string accidalltempid = row_13[i]["ID"].ToString();
string pjhtemp1 = row_13[i]["票据号"].ToString();
string datatemp1 = row_13[i]["日期"].ToString();
string pzhtemp1 = row_13[i]["凭证号"].ToString();
string pztypetemp1 = row_13[i]["凭证类型"].ToString();
DataRow[] row_14 = dt_accountaccx1.Select("排序=2 and ID=" + accidalltempid);
for (int j = 0; j < row_14.Count(); j++)
{
row_14[j]["票据号"] = pjhtemp1;
row_14[j]["日期"] = datatemp1;
row_14[j]["凭证号"] = pzhtemp1;
row_14[j]["凭证类型"] = pztypetemp1;
}
}
}
#endregion
//如果为选定的一个科目，则删除掉其余多余的科目
#region
DataTable dt_accountaccx2;
if (subfial == "3" || subfial == "5")
{
dt_accountaccx2 = dt_accountaccx1.Select("科目编码 like '" + pzsumbm + "%'").CopyToDataTable();
}
else if (subfial == "4")
{
dt_accountaccx2 = dt_accountaccx1.Select("科目编码 not like '" + pzsumbm + "%'").CopyToDataTable();
}
else
{
dt_accountaccx2 = dt_accountaccx1.Copy();
}
//删除掉详细的凭证信息
if (subfial == "2")
{
DataRow[] row_15 = dt_accountaccx2.Select("排序=2");
for (int i = 0; i < row_15.Count(); i++)
{
dt_accountaccx2.Rows.Remove(row_15[i]);
}
}
#endregion
//如果“辅助核算”为1表示需要按公司ID或员工ID进行分类
#region
dt_Distinct = dt_accountaccx2.DefaultView.ToTable(true, "科目ID");
for (int i = 0; i < dt_Distinct.Rows.Count; i++)
{
string subidtempid = dt_Distinct.Rows[i]["科目ID"].ToString();
if (subidtempid.Trim() == "")
{
continue;
}
sqlstring = "select 辅助核算 from customersub where ID='" + subidtempid + "'";
dt = return_select(sqlstring);
int fzhstemp1 = dt.Rows[0]["辅助核算"].ToString().ToInt();
if (fzhstemp1 == 0)
{
DataRow[] row_16 = dt_accountaccx2.Select("科目ID=" + subidtempid);
for (int j = 0; j < row_16.Count(); j++)
{
row_16[j]["引用属性"] = 0;
row_16[j]["公司ID"] = 0;
row_16[j]["员工ID"] = 0;
}
}
}
#endregion
#region
dt_Distinct = dt_accountaccx2.DefaultView.ToTable(true, "类型ID", "ID");
for (int i = 0; i < dt_Distinct.Rows.Count; i++)
{
string typeidtempid = dt_Distinct.Rows[i]["类型ID"].ToString();
string accidalltempid = dt_Distinct.Rows[i]["ID"].ToString();
dt_row = dt_accountaccx2.NewRow();
dt_row["类型ID"] = typeidtempid;
dt_row["ID"] = accidalltempid;
dt_row["排序"] = 3;
dt_accountaccx2.Rows.Add(dt_row);
}
DataRow[] row_17 = dt_accountaccx2.Select("排序=1");
for (int i = 0; i < row_17.Count(); i++)
{
string accidalltempid = row_17[i]["ID"].ToString();
string wfiliaidtempid = row_17[i]["分公司ID"].ToString();
DataRow[] row_18 = dt_accountaccx2.Select("ID=" + accidalltempid);
for (int j = 0; j < row_18.Count(); j++)
{
row_18[j]["分公司ID"] = wfiliaidtempid;
}
}
#endregion
#region
dt_accountaccx2.DefaultView.Sort = "分公司ID,类型ID,ID,排序,凭证号";
dt_accountacc = dt_accountaccx2.DefaultView.ToTable();
string idtempidxx = "";
string zytemp1xx = "";
//int countix = 1;
DataRow[] row_19 = dt_accountacc.Select("ID>0", "分公司ID,类型ID,ID,排序,凭证号");
for (int i = 0; i < row_19.Count(); i++)
{
string idtempid = row_19[i]["ID"].ToString();
string zytemp1 = row_19[i]["摘要"].ToString();
if (idtempid.Trim() == idtempidxx.Trim() && zytemp1.Trim() == zytemp1xx.Trim())
{
row_19[i]["摘要"] = "";
}
//row_19[i]["凭证总排序"] = countix;
//if (idtempid.Trim()!=idtempidxx.Trim())
//{ countix = countix + 1; }
idtempidxx = idtempid;
zytemp1xx = zytemp1;
}
#endregion
#region
dt_Distinct = dt_accountacc.DefaultView.ToTable(true, "ID");//注：其中ToTable（）的第一个参数为是否distinct
int countix = 1;
for (int i = 0; i < dt_Distinct.Rows.Count; i++)
{
string accallidtempid = dt_Distinct.Rows[i]["ID"].ToString();
DataRow[] row_20 = dt_accountacc.Select("ID=" + accallidtempid, "");
for (int j = 0; j < row_20.Count(); j++)
{
row_20[j]["凭证总排序"] = countix;
}
countix = countix + 1;
}
#endregion
}
public static DataTable GetDataFromExcelByCom(bool hasTitle = false)
{
OpenFileDialog openFile = new OpenFileDialog();
openFile.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
openFile.Multiselect = false;
if (openFile.ShowDialog() == DialogResult.Cancel)
{
return(null);
}
var excelFilePath = openFile.FileName;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Sheets sheets;
object oMissiong = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
DataTable dt = new DataTable();
try
{
WaitFormService.Show();
if (app == null)
{
return(null);
}
workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
sheets = workbook.Worksheets;
//将数据读入到DataTable中
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);//读取第一张表
if (worksheet == null)
{
return(null);
}
int iRowCount = worksheet.UsedRange.Rows.Count;
int iColCount = worksheet.UsedRange.Columns.Count;
//生成列头
for (int i = 0; i < iColCount; i++)
{
var name = "column" + i;
if (hasTitle)
{
var txt = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]).Text.ToString();
if (!string.IsNullOrWhiteSpace(txt))
{
name = txt;
}
}
while (dt.Columns.Contains(name))
{
name = name + "_1"; //重复行名称会报错。
}
dt.Columns.Add(new DataColumn(name, typeof(string)));
}
//生成行数据
Microsoft.Office.Interop.Excel.Range range;
int rowIdx = hasTitle ? 2 : 1;
for (int iRow = rowIdx; iRow <= iRowCount; iRow++)
{
WaitFormService.SetText("正在录入第" + iRow.ToString() + "条的数据/共" + iRowCount.ToString() + "条记录！");
DataRow dr = dt.NewRow();
for (int iCol = 1; iCol <= iColCount; iCol++)
{
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow, iCol];
dr[iCol - 1] = (range.Value2 == null) ? "" : range.Text.ToString();
}
dt.Rows.Add(dr);
}
WaitFormService.Close();
return(dt);
}
catch { return(null); }
finally
{
workbook.Close(false, oMissiong, oMissiong);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
app.Workbooks.Close();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
}
private void qg_button2_Click(object sender, EventArgs e)
{
try
{
if (qg_grid1.DataSource == null)
{
MessageBox.Show("尚未从excel表中导入工资信息！");
return;
}
DataTable dt_all1 = (DataTable)qg_grid1.DataSource;
DataTable dt_all_copy = dt_all1.Copy();
if (dt_all_copy.Rows.Count <= 0)
{
MessageBox.Show("本次工资信息记录数为0，无法导入！");
return;
}
DialogResult err = MessageBox.Show("确认将本次工资信息提交至后台数据库吗？", "提示", MessageBoxButtons.OKCancel);
if (err == DialogResult.Cancel)
{
return;
}
wage_month form_mon = new wage_month();
form_mon.Owner = this;//重要的一步，主要是使Form2的Owner指针指向Form1
form_mon.ShowDialog();
//如果“选择月份”窗体 点击 的不是确定键，则退出
if (form_mon.DialogResult != DialogResult.OK)
{
return;
}
string sqlstring;
DataTable dt_temp;
WaitFormService.Show();
//如果需要删除当月已经在后台数据库中的工资信息
if (delete_old_jytt)
{
sqlstring = "delete from Subject_mon where 月份='" + sql_month.Trim() + "'";
sqlstring = sqlstring + "\n\r" + "delete from permoney where 月份='" + sql_month + "'";
insert_update_delete(sqlstring);
}
//保存月份
sqlstring = "select ID from allmax where months=ltrim(rtrim('" + sql_month.Trim() + "'))";
dt_temp = return_select(sqlstring);
if (dt_temp.Rows.Count <= 0)
{
sqlstring = "insert into allmax(months) values (ltrim(rtrim('" + sql_month.Trim() + "')))";
insert_update_delete(sqlstring);
}
//保存月份
//保存部门信息
#region
//如果“部门名称”为空，则取上一行的“部门名称”
string bmname = dt_all_copy.Rows[0]["部门名称"].ToString();
foreach (DataRow dr in dt_all_copy.Rows)
{
WaitFormService.SetText("正在整理部门名称！\n\r " + bmname.Trim());
if (dr["部门名称"].ToString().Trim() == "")
{
dr["部门名称"] = bmname;
}
bmname = dr["部门名称"].ToString().Trim();
}
DataColumn dc1 = new DataColumn("部门ID", Type.GetType("System.Int64"));
dc1.DefaultValue = 0;
dt_all_copy.Columns.Add(dc1);
DataView dataView = dt_all_copy.DefaultView;
DataTable dt_Distinct = dataView.ToTable(true, "部门名称");//注：其中ToTable（）的第一个参数为是否distinct
for (int i = 0; i < dt_Distinct.Rows.Count; i++)
{
string bmnametemp1 = dt_Distinct.Rows[i]["部门名称"].ToString().Trim();
sqlstring = "select ID from z_fcname where 部门名称=ltrim(rtrim('" + bmnametemp1.Trim() + "')) and 删除=0";
dt_temp = return_select(sqlstring);
if (dt_temp.Rows.Count <= 0)
{
sqlstring = "insert into z_fcname(部门名称) values (ltrim(rtrim('" + bmnametemp1.Trim() + "')))";
insert_update_delete(sqlstring);
sqlstring = "select ID from z_fcname where 部门名称=ltrim(rtrim('" + bmnametemp1.Trim() + "')) and 删除=0";
dt_temp = return_select(sqlstring);
}
string bmid = dt_temp.Rows[0]["ID"].ToString();
DataRow[] row_update = dt_all_copy.Select("部门名称='" + bmnametemp1.Trim() + "'");
for (int ii = 0; ii < row_update.Count(); ii++)
{
row_update[ii]["部门ID"] = bmid.ToInt();
}
}
#endregion
//保存部门信息
//保存员工姓名至person
#region
dc1 = new DataColumn("员工ID", Type.GetType("System.Int64"));
dc1.DefaultValue = 0;
dt_all_copy.Columns.Add(dc1);
foreach (DataRow dr in dt_all_copy.Rows)
{
string pernametemp1 = dr["姓名"].ToString().Trim();
string namepytemp1 = MyPinYin.GetFirst(pernametemp1);// ChineseToPinYin.ToPinYin_one(pernametemp1);
WaitFormService.SetText("正在整理员工姓名信息！\n\r" + pernametemp1.Trim());
sqlstring = "select ID from person where 姓名=ltrim(rtrim('" + pernametemp1.Trim() + "'))";
dt_temp = return_select(sqlstring);
if (dt_temp.Rows.Count <= 0)
{
sqlstring = "insert into person(姓名,拼音) values (ltrim(rtrim('" + pernametemp1.Trim() + "')),ltrim(rtrim('" + namepytemp1.Trim() + "')))";
insert_update_delete(sqlstring);
sqlstring = "select ID from person where 姓名=ltrim(rtrim('" + pernametemp1.Trim() + "'))";
dt_temp = return_select(sqlstring);
}
string perid = dt_temp.Rows[0]["ID"].ToString();
DataRow[] row_update = dt_all_copy.Select("姓名='" + pernametemp1.Trim() + "'");
for (int ii = 0; ii < row_update.Count(); ii++)
{
row_update[ii]["员工ID"] = perid.ToInt();
}
}
#endregion
//保存员工姓名至person
//保存工资条目信息
#region
for (int i = 0; i < dt_all_copy.Columns.Count; i++)
{
string column_name = dt_all_copy.Columns[i].ColumnName.ToString();
WaitFormService.SetText("正在处理工资条目！\n\r" + column_name.Trim());
//MessageBox.Show(column_name.Trim() + " 类型：" + dt_all_copy.Columns[i].DataType.ToString());
sqlstring = "select ID from Subject_name where 条目名称=ltrim(rtrim('" + column_name.Trim() + "')) and 删除=0";
dt_temp = return_select(sqlstring);
if (dt_temp.Rows.Count <= 0)
{
sqlstring = "insert into subject_name(条目名称) values (ltrim(rtrim('" + column_name.Trim() + "')))";
insert_update_delete(sqlstring);
sqlstring = "select ID from Subject_name where 条目名称=ltrim(rtrim('" + column_name.Trim() + "')) and 删除=0";
dt_temp = return_select(sqlstring);
}
string jectid = dt_temp.Rows[0]["ID"].ToString();
//判断column的列datatype,只分为string和decimal
object test = dt_all_copy.Compute("max(" + column_name.Trim() + ")", "");
int column_type = 1;//为0表示是数字，为1表示是string
if (test.ToString().IsInt() || test.ToString().IsDecimal())
{
column_type = 0;
}
//判断column的列datatype,只分为string和decimal
sqlstring = "select ID from subject_mon where 月份='" + sql_month.Trim() + "' and 条目ID=" + jectid.Trim();
dt_temp = return_select(sqlstring);
if (dt_temp.Rows.Count <= 0)
{
sqlstring = "insert into subject_mon(月份,条目ID,排序,类型) values ('" + sql_month.Trim() + "'," + jectid.Trim() + "," + i.ToString() + "," + column_type + ")";
insert_update_delete(sqlstring);
}
}
#endregion
//保存工资条目信息
//将工资信息拷贝至后台数据库
#region
for (int i = 0; i < dt_all_copy.Rows.Count; i++)
{
string pernametemp1 = dt_all_copy.Rows[i]["姓名"].ToString();
string bmidtempid = dt_all_copy.Rows[i]["部门ID"].ToString();
string peridtempid = dt_all_copy.Rows[i]["员工ID"].ToString();
WaitFormService.SetText("正在拷贝第" + i.ToString() + "条工资记录/共" + dt_all_copy.Rows.Count.ToString() + "条记录！\n\r" + " 员工姓名：" + pernametemp1.ToString());
for (int j = 0; j < dt_all_copy.Columns.Count; j++)
{
string columnnametemp1 = dt_all_copy.Columns[j].ToString();
if (columnnametemp1.Trim() == "姓名" ||
columnnametemp1.Trim() == "部门名称" ||
columnnametemp1.Trim() == "部门ID" ||
columnnametemp1.Trim() == "员工ID"
)
{
continue;
}
string dt_all_copy_one_z = dt_all_copy.Rows[i][j].ToString().Trim();
if (dt_all_copy_one_z.Trim() == "" || (dt_all_copy_one_z.IsDecimal() && dt_all_copy_one_z.ToDecimal() == 0))
{
continue;
}
sqlstring = "select ID from Subject_name where 条目名称=ltrim(rtrim('" + columnnametemp1.Trim() + "')) and 删除=0";
dt_temp = return_select(sqlstring);
if (dt_temp.Rows.Count <= 0)
{
MessageBox.Show("没有在后台数据库中查询到：" + columnnametemp1.Trim() + "的条目记录，请重新导入工资信息，或与系统管理员联系！");
return;
}
string jectidtemp1 = dt_temp.Rows[0]["ID"].ToString();
sqlstring = "select * from Subject_mon where 月份='" + sql_month.Trim() + "' and 条目ID=" + jectidtemp1.Trim();
dt_temp = return_select(sqlstring);
if (dt_temp.Rows.Count <= 0)
{
MessageBox.Show("没有在后台数据库中查询到：" + sql_month.Trim() + "月份的" + columnnametemp1.Trim() + "的条目记录，请重新导入工资信息，或与系统管理员联系！");
return;
}
int ject_typetemp1 = dt_temp.Rows[0]["类型"].ToString().ToInt();
//如果单元格里是string,就用permoney_lr中的ID代替单元格里的内容
if (ject_typetemp1 == 1)
{
sqlstring = "select ID from permoney_lr where 文字内容=ltrim(rtrim('" + dt_all_copy_one_z.Trim() + "'))";
dt_temp = return_select(sqlstring);
if (dt_temp.Rows.Count <= 0)
{
sqlstring = "insert into permoney_lr(文字内容) values (ltrim(rtrim('" + dt_all_copy_one_z.Trim() + "')))";
insert_update_delete(sqlstring);
sqlstring = "select ID from permoney_lr where 文字内容=ltrim(rtrim('" + dt_all_copy_one_z.Trim() + "'))";
dt_temp = return_select(sqlstring);
}
dt_all_copy_one_z = dt_temp.Rows[0]["ID"].ToString();
}
//MessageBox.Show(pernametemp1+" "+columnnametemp1 + " " + ject_typetemp1.ToString()+" "+ dt_all_copy_one_z);
sqlstring = "insert into permoney(部门ID,员工ID,条目ID,金额,月份,排序) values ('" + bmidtempid + "','" + peridtempid + "','" + jectidtemp1 + "'," + dt_all_copy_one_z.ToDecimal().ToString() + ",'" + sql_month.Trim() + "'," + i.ToString() + ")";
insert_update_delete(sqlstring);
}
}
#endregion
//将工资信息拷贝至后台数据库
WaitFormService.Close();
MessageBox.Show(sql_month.Trim() + "月份的员工工资已经导入至后台数据库！");
}
catch (Exception ex)
{
ex.errormess();
}
}