public void auto()
{
try
{
string sqlstring = "select distinct 月份 from daywork order by 月份 desc";
DataTable dt_mon = return_select(sqlstring);
monaaa.DataSource = dt_mon;
monaaa.DisplayMember = "月份";
if (dt_mon.Rows.Count > 0)
{
monaaa.flag = false; monaaa.SelectedIndex = 0;
}
DataRow[] rows_km = begin_class.dt_cw_prg_all.Select("分公司ID=" + Form_wfiliale);
DataTable dt_km = Cs_Datatable.Row_To_Table(rows_km);
kmaaa.DataSource = dt_km;
kmaaa.DisplayMember = "编码加名称";
kmaaa.ValueMember = "ID";
sqlstring = "select 分公司名称 from wfiliale where ID='" + Form_wfiliale + "'";
DataTable dt = return_select(sqlstring);
string wfilialenametemp1 = dt.Rows[0]["分公司名称"].ToString();
qg_bt_label1.Text = qg_bt_label1.Text.Trim() + "(" + wfilialenametemp1.Trim() + ")";
}
catch (Exception ex)
{
ex.errormess();
}
}
static void Test(DataGridView DGV, string dy_title, string dy_month)
{
Cs_Datatable.ExportExcel(DGV, dy_title, dy_month);
}
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);
}
// 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);
}
/// 生成一个凭证的汇总表
/// </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
}
