// pro3begin和pro3end表示月份 // pro3monthbegin和pro3monthend表示工资月份 // cxfs1为1表示按月份查询,为2表示按工资月份查询 //ph1表示票号 //workid表示车间ID //perid表示员工ID //pprid表示产品ID //proid表示月产品ID //proce1表示系统工序号 //proceid表示工序ID //waresbid表示设备ID //data1表示日期 //data2表示结束日期 //ppridall表示所查询的所有产品的ID public static DataTable dayworksql_form(string pro3begin, string pro3end, string pro3monthbegin, string pro3monthend, DateTime data1, DateTime data2, int cxfs1 , int ph1, string workid, string perid, string pprid, string proid, int proce1, string proceid, string waresbid) { DataTable dt = null; dt = daywork_one.dayworksql(pro3begin, pro3end, pro3monthbegin, pro3monthend, data1, data2, cxfs1 , ph1, workid, perid, pprid, proid, proce1, proceid, waresbid); var query = from t in dt.AsEnumerable() group t by new { t1 = t.Field <Int64>("月产品ID"), t2 = t.Field <decimal>("系统工序号"), t3 = t.Field <string>("工序号"), t4 = t.Field <Int64>("姓名ID"), t5 = t.Field <string>("月份") } into m select new { 月产品ID = m.Key.t1, 系统工序号 = m.Key.t2, 工序号 = m.Key.t3, 姓名ID = m.Key.t4, 月份 = m.Key.t5, 完工数 = m.Sum(n => n.Field <decimal>("完工数")), 工废 = m.Sum(n => n.Field <decimal>("工废")), 料废 = m.Sum(n => n.Field <decimal>("料废")), 机废 = m.Sum(n => n.Field <decimal>("机废")), 调试废品 = m.Sum(n => n.Field <decimal>("调试废品")), 合格数 = m.Sum(n => n.Field <decimal>("合格数")), 工时 = m.Sum(n => n.Field <decimal>("工时")), 班次 = "班次:" + m.Count().ToString() + "个" //house = m.First().Field<string>("house"), //rowcount = m.Count() }; DataTable dtx = Cs_Datatable.ToDataTable(query.ToList()); DataRow dr2 = null; foreach (DataRow row in dtx.Rows) { dr2 = dt.NewRow(); dr2["ID"] = 0; dr2["月份ID"] = row["月份"]; dr2["月产品ID"] = row["月产品ID"]; dr2["系统工序号"] = row["系统工序号"]; dr2["姓名ID"] = row["姓名ID"]; dr2["完工数"] = row["完工数"]; dr2["工废"] = row["工废"]; dr2["料废"] = row["料废"]; dr2["机废"] = row["机废"]; dr2["调试废品"] = row["调试废品"]; dr2["合格数"] = row["合格数"]; dr2["工时"] = row["工时"]; dr2["日期"] = row["班次"]; dr2["总排序"] = 2; dr2["规格"] = "合计:"; dt.Rows.Add(dr2); } DataView dataView = dt.DefaultView; DataTable dt_Distinct = dataView.ToTable(true, "月份ID", "月产品ID", "系统工序号", "姓名ID");//注:其中ToTable()的第一个参数为是否DISTINCT foreach (DataRow row in dt_Distinct.Rows) { dr2 = dt.NewRow(); dr2["月份ID"] = row["月份ID"]; dr2["月产品ID"] = row["月产品ID"]; dr2["系统工序号"] = row["系统工序号"]; dr2["姓名ID"] = row["姓名ID"]; dr2["总排序"] = 3; dt.Rows.Add(dr2); } dt.DefaultView.Sort = "月份ID,月产品ID,系统工序号,姓名ID,总排序"; return(dt); }
public DataTable bookshz_all_cbs(string cbsid) { DataTable dt_permoney_bm = null; try { DataTable dt_permoney = bookshz_all(cbsid, ""); DataColumn dc1 = new DataColumn("排序", Type.GetType("System.Int32")); dc1.DefaultValue = 2; dt_permoney.Columns.Add(dc1); //dc1 = new DataColumn("ID", Type.GetType("System.Int64")); //dt_permoney.Columns.Add(dc1); dc1 = new DataColumn("上级ID", Type.GetType("System.Int64")); dt_permoney.Columns.Add(dc1); dc1 = new DataColumn("编码", Type.GetType("System.String")); dt_permoney.Columns.Add(dc1); dc1 = new DataColumn("展开", Type.GetType("System.Int32")); dc1.DefaultValue = 0; dt_permoney.Columns.Add(dc1); dc1 = new DataColumn("显示", Type.GetType("System.Int32")); dc1.DefaultValue = 0; dt_permoney.Columns.Add(dc1); dc1 = new DataColumn("金额排序", Type.GetType("System.Int32")); dc1.DefaultValue = 0; dt_permoney.Columns.Add(dc1); //相当于select 员工ID,月份,sum(工种) 工种,sum(基本工资) group by 员工ID,月份 #region var query = from t in dt_permoney.AsEnumerable() group t by new { t1 = t.Field <Int64>("出版社ID"), t2 = t.Field <string>("出版社") } into m select new { 出版社ID = m.Key.t1, 出版社 = m.Key.t2, 金额 = m.Sum(n => n.Field <decimal>("金额")), 册数 = m.Sum(n => n.Field <decimal>("册数")), }; DataTable dtNamex1 = Cs_Datatable.ToDataTable(query.ToList()); DataView dtName_dv = dtNamex1.DefaultView; dtName_dv.Sort = "金额 desc"; DataTable dtName = dtName_dv.ToTable(); dc1 = new DataColumn("金额排序", Type.GetType("System.Int32")); dc1.DefaultValue = 0; dtName.Columns.Add(dc1); for (int i = 0; i < dtName.Rows.Count; i++) { dtName.Rows[i]["金额排序"] = i + 1; } //将出版社的金额进行合计,最后放到总表的第一行,后面要用 decimal moneyallall = dtName.Compute("sum(金额)", "").ToString().ToDecimal(); int csallall = dtName.Compute("sum(册数)", "").ToString().ToInt(); DataTable dtResult = dt_permoney.Clone();//用来将所有汇总后的数据表 //DataTable dtName = dt_permoney.DefaultView.ToTable(true, "出版社ID", "出版社"); for (int i = 0; i < dtName.Rows.Count; i++) { int px_money = dtName.Rows[i]["金额排序"].ToString().ToInt(); string bmidtempid = dtName.Rows[i][0].ToString().Trim(); DataRow drtemp1 = dt_permoney.NewRow(); drtemp1["出版社ID"] = bmidtempid; drtemp1["排序"] = 3; drtemp1["ID"] = 0; drtemp1["金额排序"] = px_money; dt_permoney.Rows.Add(drtemp1); DataRow[] rows = dt_permoney.Select("出版社ID='" + bmidtempid + "'", "金额 desc"); //temp用来存储筛选出来的数据 DataTable temp = dtResult.Clone(); foreach (DataRow row in rows) { //LogTextHelper.Info("\r\n书名:" + row["书名"].ToString()+" 金额:"+row["金额"].ToString()); temp.Rows.Add(row.ItemArray); //顺便将员工列的“上级ID”和“编码”定下来 row["上级ID"] = bmidtempid; row["编码"] = bmidtempid.Trim() + "-" + row["ID"].ToString().Trim(); row["金额排序"] = px_money; //顺便将员工列的“上级ID”和“编码”定下来 } DataRow dr = dtResult.NewRow(); dr["ID"] = dtName.Rows[i][0].ToString(); dr["编码"] = dtName.Rows[i][0].ToString(); dr["出版社ID"] = dtName.Rows[i][0].ToString(); dr["出版社"] = "● " + dtName.Rows[i][1].ToString(); dr["排序"] = 1; dr["展开"] = 0; dr["显示"] = 1; dr["金额排序"] = px_money; for (int j = 0; j < temp.Columns.Count; j++) { string columnnametemp1 = temp.Columns[j].ColumnName.ToString().Trim(); //if (columnnametemp1 != "出版社ID" // && columnnametemp1 != "出版社" // && columnnametemp1 != "员工ID" // && columnnametemp1 != "姓名" // && columnnametemp1 != "月份" // && columnnametemp1 != "上级ID" // && columnnametemp1 != "编码" // && (temp.Columns[j].DataType.FullName == "System.Decimal") // ) if (columnnametemp1 == "册数" || columnnametemp1.Trim() == "金额") { dr[columnnametemp1] = temp.Compute("sum(" + columnnametemp1.Trim() + ")", ""); } } //dr["工种"] = temp.Compute("max(工种)", ""); dtResult.Rows.Add(dr); } #endregion //相当于select 员工ID,月份,sum(工种) 工种,sum(基本工资) group by 员工ID,月份 //员工工资列的“部门名称”设为“” for (int i = 0; i < dt_permoney.Rows.Count; i++) { dt_permoney.Rows[i]["出版社"] = ""; //添加数据行 } //员工工资列的“部门名称”设为“” //将部门合计的金额列加入到总表中 for (int i = 0; i < dtResult.Rows.Count; i++) { dt_permoney.Rows.Add(dtResult.Rows[i].ItemArray); //添加数据行 } //将部门合计的金额列加入到总表中 //LogTextHelper.Info("\r\n执行前列数:" +dt_permoney.Rows.Count.ToString()); //将总合计金额放到表头 DataRow dr_all = dt_permoney.NewRow(); dr_all["出版社"] = "合计"; dr_all["金额"] = moneyallall; dr_all["册数"] = csallall; dr_all["金额排序"] = 0; dr_all["显示"] = 1; dr_all["排序"] = 1; dr_all["编码"] = "-1"; dr_all["ID"] = "0"; dt_permoney.Rows.Add(dr_all); dr_all = dt_permoney.NewRow(); dr_all["金额排序"] = 0; dr_all["显示"] = 1; dr_all["排序"] = 3; dr_all["编码"] = "-1"; dr_all["ID"] = "0"; dt_permoney.Rows.Add(dr_all); //将总合计金额放到表头 //LogTextHelper.Info("\r\n执行后:" + dt_permoney.Rows.Count.ToString()); //排序 DataView dv = dt_permoney.DefaultView; dv.Sort = "金额排序,出版社ID,排序,金额 desc"; //dt_permoney_bm = dv.ToTable().DefaultView.ToTable(true, "ID", "出版社", "册数", "金额", "ISBN号", "书名","上级ID","编码","展开","显示","排序"); dt_permoney_bm = dv.ToTable(); //LogTextHelper.Info("\r\n排序后:" + dt_permoney_bm.Rows.Count.ToString()); DataRow[] rows1 = dt_permoney_bm.Select("排序=2"); for (int i = 0; i < rows1.Count(); i++) { rows1[i]["出版社"] = " " + rows1[i]["书名"].ToString() + "(ISBN号:" + rows1[i]["ISBN号"].ToString() + ")"; } dt_permoney_bm.Columns["出版社"].SetOrdinal(1); dt_permoney_bm.Columns["册数"].SetOrdinal(2); dt_permoney_bm.Columns["金额"].SetOrdinal(3); dt_permoney_bm.Columns["ISBN号"].SetOrdinal(4); dt_permoney_bm.Columns["书名"].SetOrdinal(5); //排序 } catch (Exception ex) { ex.errormess(); } return(dt_permoney_bm); }
/// 生成一个凭证的汇总表 /// </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 }