private void Show() { var comModel = new Dal.BaseInfo.TB_CompanyService().GetModel(Convert.ToInt32(ddlCompany.Text)); lblCompanyName.Text = comModel.ComName; lblSimpName.Text = comModel.ComSimpName; lblDate.Text = ddlYear.Text + ddlMonth.Text; lblAE.Text = ddlUser.SelectedItem.Text; propertyList = this.FIN_PropertyService.GetListArray_SpecCost(ddlYear.Text + ddlMonth.Text, ddlCompany.Text, ddlUser.Text); //DateTime fristDate = Convert.ToDateTime(ddlYear.Text+"-"+ddlMonth.Text+"-1"); //DateTime fristDate_1 = Convert.ToDateTime(ddlYear.Text + "-1-1"); //DateTime endDate = Convert.ToDateTime(ddlYear.Text + "-" + ddlMonth.Text + "-" + DateTime.DaysInMonth(fristDate.Year, fristDate.Month)); //显示系统值 var sql = string.Format(@"---显示=右面界面 加班记录管理中 日期从XX---YY(财年跨度) ,某一AE 的加班费 合计数 select isnull(sum(Total),0) from tb_OverTime where year(formTime)={0} and MONTH(formTime)<={1} and guestDai ='{2}' and tb_OverTime.id in (select allE_id from tb_EForm where proId in ( select pro_Id from A_ProInfo where pro_Type='加班单') and state='通过')", ddlYear.Text, ddlMonth.Text, ddlUser.SelectedItem.Text); ViewState["OverTime"] = DBHelp.ExeScalar(sql); sql = string.Format(@"---显示=右面界面 加班记录管理中 日期从XX---YY(财年跨度) ,某一AE 的加班费 合计数 select isnull(sum(Total),0) from tb_OverTime where year(formTime)={0} and MONTH(formTime)={1} and guestDai ='{2}' and tb_OverTime.id in (select allE_id from tb_EForm where proId in ( select pro_Id from A_ProInfo where pro_Type='加班单') and state='通过')", ddlYear.Text, ddlMonth.Text, ddlUser.SelectedItem.Text); ViewState["OverTimeMonth"] = DBHelp.ExeScalar(sql); GetGoodTotal(); lblYear.Text = string.Format("{0}-01", ddlYear.Text); lblMonth.Text = string.Format("{0}-{1}", ddlYear.Text, ddlMonth.Text); }
private void Show() { var comModel = new Dal.BaseInfo.TB_CompanyService().GetModel(Convert.ToInt32(ddlCompany.Text)); lblCompanyName.Text = comModel.ComName; lblSimpName.Text = comModel.ComSimpName; lblDate.Text = ddlYear.Text + ddlMonth.Text; propertyList = this.FIN_PropertyService.GetListArray_CommCost(ddlYear.Text + ddlMonth.Text, ddlCompany.Text); string poName = string.Format("{0}年{1}行政经费", ddlYear.Text, comModel.ComSimpName); //显示系统值 #region 邮寄费 var sql = string.Format(@"---项目中预期报销单的邮寄费合计 select isnull(SUM(Tb_DispatchList.PostTotal),0) from tb_Post left join Tb_DispatchList ON tb_Post.ID=Tb_DispatchList.Post_Id where AppName in (select id from tb_User where CompanyCode='{0}') and tb_Post.poName='{1}' and tb_Post.id in (select allE_id from tb_EForm where state='通过' and proid in (select pro_Id from A_ProInfo where pro_Type='邮寄文档快递表') )", comModel.ComCode, poName); var DispatchList = DBHelp.ExeScalar(sql); if (ddlMonth.Text != "12") { sql = string.Format(@"---项目中预期报销单的邮寄费合计 select isnull(SUM(Tb_DispatchList.PostTotal),0) from tb_Post left join Tb_DispatchList ON tb_Post.ID=Tb_DispatchList.Post_Id where year(AppTime)={0} and MONTH(AppTime)={1} and AppName in (select id from tb_User where CompanyCode='{2}') and tb_Post.poName='{3}' and tb_Post.id in (select allE_id from tb_EForm where state='通过' and proid in (select pro_Id from A_ProInfo where pro_Type='邮寄文档快递表') )", ddlYear.Text, ddlMonth.Text, comModel.ComCode, poName); } else { sql = string.Format(@"---项目中预期报销单的邮寄费合计 select isnull(SUM(Tb_DispatchList.PostTotal),0) from tb_Post left join Tb_DispatchList ON tb_Post.ID=Tb_DispatchList.Post_Id where AppTime>='{0} 00:00:00' and AppName in (select id from tb_User where CompanyCode='{1}') and tb_Post.poName='{2}' and tb_Post.id in (select allE_id from tb_EForm where state='通过' and proid in (select pro_Id from A_ProInfo where pro_Type='邮寄文档快递表') )", ddlYear.Text + "-12-1", comModel.ComCode, poName); } var DispatchList_Month = DBHelp.ExeScalar(sql); #endregion #region 办公用品费 sql = string.Format(@"--小类办公用品费 中的所有合计 select isnull(sum(goodTotal),0) FROM JXC_REPORT left join CG_POOrder on JXC_REPORT.PONo=CG_POOrder.PONo and CG_POOrder.IFZhui=0 and CG_POOrder.Status='通过' where GoodTypeSmName ='办公用品费' and GoodTypeName ='杂费' and CG_POOrder.AppName in (select id from tb_User where CompanyCode='{0}') and CG_POOrder.poName='{1}'", comModel.ComCode, poName); var JXCREPORT = DBHelp.ExeScalar(sql); if (ddlMonth.Text != "12") { sql = string.Format(@"--小类办公用品费 中的所有合计 select isnull(sum(goodTotal),0) FROM JXC_REPORT left join CG_POOrder on JXC_REPORT.PONo=CG_POOrder.PONo and CG_POOrder.IFZhui=0 and CG_POOrder.Status='通过' where GoodTypeSmName ='办公用品费' and GoodTypeName ='杂费' and year(RuTime)={0} and MONTH(RuTime)={1} and CG_POOrder.AppName in (select id from tb_User where CompanyCode='{2}') and CG_POOrder.poName='{3}'", ddlYear.Text, ddlMonth.Text, comModel.ComCode, poName); } else { sql = string.Format(@"--小类办公用品费 中的所有合计 select isnull(sum(goodTotal),0) FROM JXC_REPORT left join CG_POOrder on JXC_REPORT.PONo=CG_POOrder.PONo and CG_POOrder.IFZhui=0 and CG_POOrder.Status='通过' where GoodTypeSmName ='办公用品费' and GoodTypeName ='杂费' and RuTime>='{0} 00:00:00' and CG_POOrder.AppName in (select id from tb_User where CompanyCode='{1}') and CG_POOrder.poName='{2}'", ddlYear.Text + "-12-1", comModel.ComCode, poName); } var JXCREPORT_Month = DBHelp.ExeScalar(sql); #endregion sql = string.Format(@"--项目中除去黄色框的内容的其他成本合计(该项目销售明细报表中除去黄色框的其他总成本+总损失差额) select isnull(sum(goodTotal),0) as totoal FROM JXC_REPORT left join CG_POOrder on JXC_REPORT.PONo=CG_POOrder.PONo and CG_POOrder.IFZhui=0 and CG_POOrder.Status='通过' where CG_POOrder.AppName in (select id from tb_User where CompanyCode='{0}') and CG_POOrder.poName='{1}' ", comModel.ComCode, poName); var JXCREPORT_Cha = (decimal)DBHelp.ExeScalar(sql); if (ddlMonth.Text != "12") { sql = string.Format(@"--行政经费的总额 扣除如下 黄色部分的总额 select isnull(sum(goodTotal),0) as totoal FROM JXC_REPORT left join CG_POOrder on JXC_REPORT.PONo=CG_POOrder.PONo and CG_POOrder.IFZhui=0 and CG_POOrder.Status='通过' where year(RuTime)={0} and MONTH(RuTime)<={1} and CG_POOrder.AppName in (select id from tb_User where CompanyCode='{2}') and CG_POOrder.poName='{3}' ", ddlYear.Text, ddlMonth.Text, comModel.ComCode, poName); } else { sql = string.Format(@"--行政经费的总额 扣除如下 黄色部分的总额 select isnull(sum(goodTotal),0) as totoal FROM JXC_REPORT left join CG_POOrder on JXC_REPORT.PONo=CG_POOrder.PONo and CG_POOrder.IFZhui=0 and CG_POOrder.Status='通过' where RuTime>='{0} 00:00:00' and CG_POOrder.AppName in (select id from tb_User where CompanyCode='{1}') and CG_POOrder.poName='{2}' ", ddlYear.Text + "-12-1", comModel.ComCode, poName); } var JXCREPORT_Cha_Month = (decimal)DBHelp.ExeScalar(sql); Sell_OrderOutHouseService sellOutSer = new Sell_OrderOutHouseService(); string where = string.Format(" and Sell_OrderOutHouse.CreateUserId in (select id from tb_User where CompanyCode='{0}') and poName='{1}'", comModel.ComCode, poName); Dictionary <string, decimal> GetAllTotal = sellOutSer.GetAllTotal_ChengBen(where); if (ddlMonth.Text != "12") { where = string.Format(" and year(RuTime)={0} and MONTH(RuTime)={1} and Sell_OrderOutHouse.CreateUserId in (select id from tb_User where CompanyCode='{2}') and poName='{3}'", ddlYear.Text, ddlMonth.Text, comModel.ComCode, poName); } else { where = string.Format(" and RuTime>='{0} 00:00:00' and Sell_OrderOutHouse.CreateUserId in (select id from tb_User where CompanyCode='{1}') and poName='{2}'", ddlYear.Text + "-12-1", comModel.ComCode, poName); } Dictionary <string, decimal> GetAllTotal_Month = sellOutSer.GetAllTotal_ChengBen(where); foreach (var p in propertyList) { if (p.CostType == "均摊邮费") { p.XiShu_Value = Convert.ToDecimal(DispatchList); p.CurrentMonth_Value = Convert.ToDecimal(DispatchList_Month); } if (p.CostType == "办公用品费") { p.XiShu_Value = Convert.ToDecimal(JXCREPORT); p.CurrentMonth_Value = Convert.ToDecimal(JXCREPORT_Month); } #region 系统值 if (p.CostType == "资质费用" && GetAllTotal.ContainsKey("17110")) { p.XiShu_Value = GetAllTotal["17110"]; } else if (p.CostType == "各类证照" && GetAllTotal.ContainsKey("17112")) { p.XiShu_Value = GetAllTotal["17112"]; } else if (p.CostType == "汽车维修费" && GetAllTotal.ContainsKey("16616")) { p.XiShu_Value = GetAllTotal["16616"]; } else if (p.CostType == "汽车保险" && GetAllTotal.ContainsKey("17109")) { p.XiShu_Value = GetAllTotal["17109"]; } else if (p.CostType == "公务汽油费" && GetAllTotal.ContainsKey("14346")) { p.XiShu_Value = GetAllTotal["14346"]; } else if (p.CostType == "律师费" && GetAllTotal.ContainsKey("17111")) { p.XiShu_Value = GetAllTotal["17111"]; } else if (p.CostType == "内部装修改建费" && GetAllTotal.ContainsKey("14350")) { p.XiShu_Value = GetAllTotal["14350"]; } else if (p.CostType == "软件开发费" && GetAllTotal.ContainsKey("14396")) { p.XiShu_Value = GetAllTotal["14396"]; } else if (p.CostType == "公务费" && GetAllTotal.ContainsKey("14423")) { p.XiShu_Value = GetAllTotal["14423"]; } else if (p.CostType == "交通费" && GetAllTotal.ContainsKey("15317")) { p.XiShu_Value = GetAllTotal["15317"]; } else if (p.CostType == "水费" && GetAllTotal.ContainsKey("14348")) { p.XiShu_Value = GetAllTotal["14348"]; } else if (p.CostType == "电费" && GetAllTotal.ContainsKey("14349")) { p.XiShu_Value = GetAllTotal["14349"]; } else if (p.CostType == "物业管理费" && GetAllTotal.ContainsKey("17108")) { p.XiShu_Value = GetAllTotal["17108"]; } else if (p.CostType == "设备投资费" && GetAllTotal.ContainsKey("17158")) { p.XiShu_Value = GetAllTotal["17158"]; } else if (p.CostType == "电信费用" && GetAllTotal.ContainsKey("17167")) { p.XiShu_Value = GetAllTotal["17167"]; } #endregion #region 当月值 if (p.CostType == "资质费用" && GetAllTotal_Month.ContainsKey("17110")) { p.CurrentMonth_Value = GetAllTotal_Month["17110"]; } else if (p.CostType == "各类证照" && GetAllTotal_Month.ContainsKey("17112")) { p.CurrentMonth_Value = GetAllTotal_Month["17112"]; } else if (p.CostType == "汽车维修费" && GetAllTotal_Month.ContainsKey("16616")) { p.CurrentMonth_Value = GetAllTotal_Month["16616"]; } else if (p.CostType == "汽车保险" && GetAllTotal_Month.ContainsKey("17109")) { p.CurrentMonth_Value = GetAllTotal_Month["17109"]; } else if (p.CostType == "公务汽油费" && GetAllTotal_Month.ContainsKey("14346")) { p.CurrentMonth_Value = GetAllTotal_Month["14346"]; } else if (p.CostType == "律师费" && GetAllTotal_Month.ContainsKey("17111")) { p.CurrentMonth_Value = GetAllTotal_Month["17111"]; } else if (p.CostType == "内部装修改建费" && GetAllTotal_Month.ContainsKey("14350")) { p.CurrentMonth_Value = GetAllTotal_Month["14350"]; } else if (p.CostType == "软件开发费" && GetAllTotal_Month.ContainsKey("14396")) { p.CurrentMonth_Value = GetAllTotal_Month["14396"]; } else if (p.CostType == "公务费" && GetAllTotal_Month.ContainsKey("14423")) { p.CurrentMonth_Value = GetAllTotal_Month["14423"]; } else if (p.CostType == "交通费" && GetAllTotal_Month.ContainsKey("15317")) { p.CurrentMonth_Value = GetAllTotal_Month["15317"]; } else if (p.CostType == "水费" && GetAllTotal_Month.ContainsKey("14348")) { p.CurrentMonth_Value = GetAllTotal_Month["14348"]; } else if (p.CostType == "电费" && GetAllTotal_Month.ContainsKey("14349")) { p.CurrentMonth_Value = GetAllTotal_Month["14349"]; } else if (p.CostType == "物业管理费" && GetAllTotal_Month.ContainsKey("17108")) { p.CurrentMonth_Value = GetAllTotal_Month["17108"]; } else if (p.CostType == "设备投资费" && GetAllTotal_Month.ContainsKey("17158")) { p.CurrentMonth_Value = GetAllTotal_Month["17158"]; } else if (p.CostType == "电信费用" && GetAllTotal_Month.ContainsKey("17167")) { p.CurrentMonth_Value = GetAllTotal_Month["17167"]; } #endregion #region 商品编码 if (p.CostType == "资质费用") { p.Code_Value = "17110"; } else if (p.CostType == "各类证照") { p.Code_Value = "17112"; } else if (p.CostType == "汽车维修费") { p.Code_Value = "16616"; } else if (p.CostType == "汽车保险") { p.Code_Value = "17109"; } else if (p.CostType == "公务汽油费") { p.Code_Value = "14346"; } else if (p.CostType == "律师费") { p.Code_Value = "17111"; } else if (p.CostType == "内部装修改建费") { p.Code_Value = "14350"; } else if (p.CostType == "软件开发费") { p.Code_Value = "14396"; } else if (p.CostType == "公务费") { p.Code_Value = "14423"; } else if (p.CostType == "交通费") { p.Code_Value = "15317"; } else if (p.CostType == "水费") { p.Code_Value = "14348"; } else if (p.CostType == "电费") { p.Code_Value = "14349"; } else if (p.CostType == "物业管理费") { p.Code_Value = "17108"; } else if (p.CostType == "设备投资费") { p.Code_Value = "17158"; } else if (p.CostType == "电信费用") { p.Code_Value = "17167"; } #endregion } var other_M = propertyList.Find(t => t.CostType == "均摊杂费"); if (other_M != null) { var others = JXCREPORT_Cha - propertyList.Sum(t => t.XiShu_Value); other_M.XiShu_Value = others; other_M.CurrentMonth_Value = JXCREPORT_Cha_Month - propertyList.Sum(t => t.CurrentMonth_Value); } }