using BizCom; using Newtonsoft.Json; using NHibernate.Mapping; using NPOI.SS.Formula.Functions; using SQLData; using System; using System.Collections; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Text; using static NHibernate.Linq.Visitors.LeftJoinDetector; namespace SiteCore.Handler { public partial class sync { //饼状图 public void get_time_type_order_count() { string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); StringBuilder sql = new StringBuilder(); sql.AppendFormat("select Count(tid) as count from view_ErpTradeCell where wechatTag=1 and DATEDIFF(s,pay_time,CONVERT(datetime, '{0}',20))<0 and DATEDIFF(s, pay_time,CONVERT(datetime,'{1}',20))>0;", date1, date2); sql.AppendFormat("select Count(tid) as count from view_ErpTradeCell where IsSample=1 and DATEDIFF(s,pay_time,CONVERT(datetime, '{0}',20))<0 and DATEDIFF(s, pay_time,CONVERT(datetime,'{1}',20))>0;", date1, date2); sql.AppendFormat("select Count(tid) as count from view_ErpTradeCell where IsRefund<=1 and IsSample=0 and DATEDIFF(S,pay_time,CONVERT(datetime, '{0}',20))<0 and DATEDIFF(s, pay_time,CONVERT(datetime,'{1}',20))>0;", date1, date2); DataSet ds = CeErpPost.ExecuteDataset(sql.ToString()); var res_obj = new { wechat = Convert.ToInt32(ds.Tables[0].Rows[0]["count"]), sample = Convert.ToInt32(ds.Tables[1].Rows[0]["count"]), nomal = Convert.ToInt32(ds.Tables[2].Rows[0]["count"]) }; string ro_json = JsonConvert.SerializeObject(res_obj); ReturnSuccess(ro_json); } public void get_report_time_order_count() { string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); StringBuilder sql = new StringBuilder(); sql.AppendFormat("SELECT count(view_ErpTradeCell.tid) AS count, DAY(view_ErpTradeCell.pay_time) AS ctime FROM view_ErpTradeCell WHERE DATEDIFF(d, CONVERT(datetime, '{0}',20), pay_time) >= 0 and DATEDIFF(d, CONVERT(datetime, '{1}',20), pay_time) <= 0 GROUP BY DAY(view_ErpTradeCell.pay_time) order by ctime ; ", date1, date2); //DataSet ds = CeErpPost.ExecuteDataset(sql.ToString()); DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; returnSuccess(Utils.Serialization.JsonString.DataTable2MiniAjaxJson(dt)); } public void get_report_province_order_count() { string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); StringBuilder sql = new StringBuilder(); sql.AppendFormat("SELECT count(tid) AS count, receiver_state AS province FROM view_ErpTradeCell WHERE DATEDIFF(d, CONVERT(datetime, '{0}',20), pay_time) >= 0 and DATEDIFF(d, CONVERT(datetime, '{1}',20), pay_time) <= 0 GROUP BY view_ErpTradeCell.receiver_state; ", date1, date2); //DataSet ds = CeErpPost.ExecuteDataset(sql.ToString()); DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; returnSuccess(Utils.Serialization.JsonString.DataTable2MiniAjaxJson(dt)); } public void get_report_product_order_count() { string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); StringBuilder sql = new StringBuilder(); sql.AppendFormat("SELECT ProductName AS pname,count(tid) AS count,SUM(payment) as amount FROM view_ErpTradeCell WHERE IsSample!=1 and IsSample!=2 and DATEDIFF(d, CONVERT(datetime, '{0}',20), pay_time) >= 0 and DATEDIFF(d, CONVERT(datetime, '{1}',20), pay_time) <= 0 GROUP BY view_ErpTradeCell.ProductName order by count desc; ", date1, date2); //DataSet ds = CeErpPost.ExecuteDataset(sql.ToString()); DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; //returnSuccess(Utils.Serialization.JsonString.DataTable2MiniAjaxJson(dt)); writeGridDataTableJson(dt.Rows.Count, dt); } public void get_report_shop_order_count() { string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); StringBuilder sql = new StringBuilder(); string shops = CurrentUser.User.pemShop; DataTable dt = null; if ("".Equals(shops)) { dt = new DataTable(); } else { sql.AppendFormat("SELECT seller_nick AS sname ,count(tid) AS count,SUM(payment) as amount FROM view_ErpTradeCell WHERE IsSample!=1 and IsSample!=2 and DATEDIFF(d, CONVERT(datetime, '{0}',20), pay_time) >= 0 and DATEDIFF(d, CONVERT(datetime, '{1}',20), pay_time) <= 0 and shopId in ({2}) GROUP BY view_ErpTradeCell.seller_nick order by count desc; ", date1, date2, shops); dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; } //DataSet ds = CeErpPost.ExecuteDataset(sql.ToString()); //returnSuccess(Utils.Serialization.JsonString.DataTable2MiniAjaxJson(dt)); writeGridDataTableJson(dt.Rows.Count, dt); } public void get_report_shop_month_order_count() { string sname = GetPostString("sname"); int year = DateTime.Now.Year; StringBuilder sql = new StringBuilder(); sql.AppendFormat("SELECT count(view_ErpTradeCell.tid) AS count, month(view_ErpTradeCell.pay_time) AS ctime FROM view_ErpTradeCell WHERE seller_nick='{0}' and year(pay_time)={1} GROUP BY DAY(view_ErpTradeCell.pay_time) order by ctime ; ", sname, year); //DataSet ds = CeErpPost.ExecuteDataset(sql.ToString()); DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; returnSuccess(Utils.Serialization.JsonString.DataTable2MiniAjaxJson(dt)); } public void get_report_shop_month_count() { string seller_nick = GetPostString("seller_nick"); StringBuilder sql = new StringBuilder(); sql.AppendFormat("SELECT count(view_ErpTradeCell.tid) AS count, month(view_ErpTradeCell.pay_time) AS ctime FROM view_ErpTradeCell WHERE YEAR(pay_time)=YEAR(GETDATE()) and seller_nick='{0}' and IsSample!=1 and IsSample!=2 GROUP BY month(view_ErpTradeCell.pay_time) order by ctime ; ", seller_nick); //DataSet ds = CeErpPost.ExecuteDataset(sql.ToString()); DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; returnSuccess(Utils.Serialization.JsonString.DataTable2MiniAjaxJson(dt)); } public void get_report_product_month_count() { string ProductName = GetPostString("ProductName"); StringBuilder sql = new StringBuilder(); sql.AppendFormat("SELECT count(view_ErpTradeCell.tid) AS count, month(view_ErpTradeCell.pay_time) AS ctime FROM view_ErpTradeCell WHERE YEAR(pay_time)=YEAR(GETDATE()) and ProductName='{0}' and IsSample!=1 and IsSample!=2 GROUP BY month(view_ErpTradeCell.pay_time) order by ctime ; ", ProductName); //DataSet ds = CeErpPost.ExecuteDataset(sql.ToString()); DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; returnSuccess(Utils.Serialization.JsonString.DataTable2MiniAjaxJson(dt)); } //设计汇总 public void get_erp_designer_gather() { int myOrg = CurrentUser.UserPost.OrgID; int org = GetPostInt("org"); if (myOrg != 1 && myOrg != 29 && myOrg != 10 && myOrg != 6) // { org = myOrg; } string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); int sample = -1; string sstr = GetPostString("order_area"); if (sstr.Length > 0) { sample = Convert.ToInt32(sstr); } if (date1.Length <= 0) date1 = DateTime.Now.ToString(); if (date2.Length <= 0) date2 = DateTime.Now.ToString(); string username = GetPostString("designer"); StringBuilder sql = new StringBuilder(); string reslist = ""; sql.AppendFormat("select ID,Name from view_ErpUser where OrgID={0} and isOpen=1;", org); DataTable dtdesign = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; foreach (DataRow dr in dtdesign.Rows) { SqlParameter[] sqlParameter ={ new SqlParameter("@orgid", SqlDbType.Int,4), new SqlParameter("@sDisDate", SqlDbType.DateTime, 20), new SqlParameter("@eDisDate", SqlDbType.DateTime, 20), new SqlParameter("@isSample", SqlDbType.Int, 4), new SqlParameter("@designid", SqlDbType.Int, 4), new SqlParameter("@res", SqlDbType.VarChar, 4000) }; sqlParameter[0].Value = org; sqlParameter[1].Value = date1; sqlParameter[2].Value = date2; sqlParameter[3].Value = sample; sqlParameter[4].Value = Convert.ToInt32(dr["ID"]); sqlParameter[5].Direction = ParameterDirection.Output; //CeErpTradeCell.ExecuteNonQueryStore("sp_getdesigngather", sqlParameter); DbHelper.DbConn.ExecuteNonQuery(CommandType.StoredProcedure, "sp_getdesigngather", sqlParameter); string res = sqlParameter[5].Value.ToString(); reslist += (dr["Name"].ToString() + "," + res + "|"); } DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("DesignerName", typeof(string))); dt.Columns.Add(new DataColumn("Amount", typeof(decimal))); dt.Columns.Add(new DataColumn("AllOrder", typeof(int))); dt.Columns.Add(new DataColumn("Average", typeof(decimal))); dt.Columns.Add(new DataColumn("WaitDesign", typeof(int))); dt.Columns.Add(new DataColumn("Designing", typeof(int))); dt.Columns.Add(new DataColumn("DesignFinish", typeof(int))); dt.Columns.Add(new DataColumn("RefundCount", typeof(int))); dt.Columns.Add(new DataColumn("FinalizationRate", typeof(decimal))); DataRow dr_he = dt.NewRow(); dr_he[0] = "合计"; dr_he[1] = 0; dr_he[2] = 0; dr_he[3] = 0; dr_he[4] = 0; dr_he[5] = 0; dr_he[6] = 0; dr_he[7] = 0; dr_he[8] = 0; string[] desList = reslist.Split('|'); foreach (string onedes in desList) { if (onedes.Length > 0) { string[] vlList = onedes.Split(','); DataRow dr = dt.NewRow(); dr[0] = vlList[0]; dr[1] = Convert.ToDecimal(vlList[1]); dr[2] = Convert.ToInt32(vlList[2]); dr[4] = Convert.ToInt32(vlList[3]); dr[5] = Convert.ToInt32(vlList[4]); dr[6] = Convert.ToInt32(vlList[5]); dr[7] = Convert.ToInt32(vlList[6]); decimal avg = 0; if (vlList[2].ToString() != "0") { avg = (decimal)(Convert.ToDecimal(vlList[1]) / Convert.ToDecimal(vlList[2])); } decimal avgxxx = avg * 100; int avgxxx2 = (int)avgxxx; decimal avgxxx3 = (decimal)avgxxx2 / 100; string savg = Convert.ToString(avgxxx3); dr[3] = savg; decimal rate = 0; if (vlList[2].ToString() != "0") { rate = (decimal)(Convert.ToDecimal(vlList[5]) / Convert.ToDecimal(vlList[2])); } decimal xxx = rate * 10000; int xxx2 = (int)xxx; decimal xxx3 = (decimal)xxx2 / 100; string brate = Convert.ToString(xxx3); dr[8] = brate; dt.Rows.Add(dr); dr_he[1] = Convert.ToDecimal(dr_he[1]) + Convert.ToDecimal(vlList[1]); dr_he[2] = Convert.ToDecimal(dr_he[2]) + Convert.ToDecimal(vlList[2]); dr_he[3] = Convert.ToDecimal(dr_he[3]) + avgxxx3; dr_he[4] = Convert.ToDecimal(dr_he[4]) + Convert.ToDecimal(vlList[3]); dr_he[5] = Convert.ToDecimal(dr_he[5]) + Convert.ToDecimal(vlList[4]); dr_he[6] = Convert.ToDecimal(dr_he[6]) + Convert.ToDecimal(vlList[5]); dr_he[7] = Convert.ToDecimal(dr_he[7]) + Convert.ToDecimal(vlList[6]); dr_he[8] = Convert.ToDecimal(dr_he[8]) + Convert.ToDecimal(brate); } } decimal rrr = Convert.ToDecimal(dr_he[8]) / desList.Length * 100; int rrr2 = (int)rrr; decimal rrr3 = (decimal)rrr2 / 100; dr_he[8] = rrr3.ToString(); dt.Rows.Add(dr_he); DataView dv = new DataView(dt); dv.Sort = "AllOrder asc"; writeGridDataViewJson(dt.Rows.Count, dv); } public DataTable getWeekDesignRate(string startdatetime) { return null; } public void get_erp_customer_gather() { int org = GetPostInt("org"); int myOrg = CurrentUser.UserPost.OrgID; if (myOrg != 1 && myOrg != 29 && myOrg != 10 && myOrg != 6) // { org = myOrg; } string sstr = GetPostString("order_area"); int sample = -1; if (sstr.Length > 0) { sample = Convert.ToInt32(sstr); } string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); //int sample = GetPostInt("order_area"); //int shopid = GetPostInt("shopid"); if (date1.Length <= 0) date1 = "1990-01-01"; if (date2.Length <= 0) date2 = "2058-01-01"; StringBuilder sql = new StringBuilder(); string reslist = ""; sql.AppendFormat("select ID,Name from view_ErpUser where OrgID={0} and (PostCode='CustomerService' or PostCode='Director' or PostCode='CustomerMaster' or PostCode='CustomerMr') and isOpen=1;", org); DataTable dtdesign = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; foreach (DataRow dr in dtdesign.Rows) { SqlParameter[] sqlParameter ={ new SqlParameter("@orgid", SqlDbType.Int,4), new SqlParameter("@sDisDate", SqlDbType.DateTime, 20), new SqlParameter("@eDisDate", SqlDbType.DateTime, 20), new SqlParameter("@isSample", SqlDbType.Int,4), new SqlParameter("@customerid", SqlDbType.Int,4), new SqlParameter("@res", SqlDbType.VarChar, 4000) }; sqlParameter[0].Value = org; sqlParameter[1].Value = date1; sqlParameter[2].Value = date2; sqlParameter[3].Value = sample; sqlParameter[4].Value = Convert.ToInt32(dr["ID"]); sqlParameter[5].Direction = ParameterDirection.Output; //CeErpTradeCell.ExecuteNonQueryStore("sp_getdesigngather", sqlParameter); DbHelper.DbConn.ExecuteNonQuery(CommandType.StoredProcedure, "sp_customergather", sqlParameter); string res = sqlParameter[5].Value.ToString(); reslist += (dr["Name"].ToString() + "," + res + "|"); } DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("CusName", typeof(string))); dt.Columns.Add(new DataColumn("Amount", typeof(string))); dt.Columns.Add(new DataColumn("AllOrder", typeof(string))); dt.Columns.Add(new DataColumn("Refund", typeof(string))); dt.Columns.Add(new DataColumn("RefundSum", typeof(string))); DataRow dr_he = dt.NewRow(); dr_he[0] = "合计"; dr_he[1] = 0; dr_he[2] = 0; dr_he[3] = 0; dr_he[4] = 0; string[] desList = reslist.Split('|'); foreach (string onedes in desList) { if (onedes.Length > 0) { string[] vlList = onedes.Split(','); DataRow dr = dt.NewRow(); dr[0] = vlList[0]; dr[1] = vlList[1]; dr[2] = vlList[2]; dr[3] = vlList[3]; dr[4] = vlList[4]; dt.Rows.Add(dr); dr_he[1] = Convert.ToDecimal(dr_he[1]) + Convert.ToDecimal(vlList[1]); dr_he[2] = Convert.ToInt32(dr_he[2]) + Convert.ToInt32(vlList[2]); dr_he[3] = Convert.ToInt32(dr_he[3]) + Convert.ToInt32(vlList[3]); dr_he[4] = Convert.ToDecimal(dr_he[4]) + Convert.ToDecimal(vlList[4]); } } dt.Rows.Add(dr_he); DataView dv = new DataView(dt); //dv.Sort = "AllOrder desc"; writeGridDataViewJson(dt.Rows.Count, dv); } public void get_erp_designer_gatherNew() { DataStruct dStruct = GetPostStruct(); List lw = new List(); int st = GetInt("st"); string tid = GetPostString("ctid"); string org = GetPostString("org"); string sstr = GetPostString("order_area"); string username = GetPostString("designer"); string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); string dateType = GetPostString("dateType"); DateTime firstDay = commonHelper.FirstDayOfMonth(Convert.ToDateTime(date1));//月 DateTime lastDay = commonHelper.LastDayOfMonth(Convert.ToDateTime(date1));//月 // 获取所在周的开始日期 DateTime firstweek = Convert.ToDateTime(date1).AddDays(-(int)Convert.ToDateTime(date1).DayOfWeek);//周 // 获取所在周的结束日期 DateTime lastweek = firstweek.AddDays(6);//周 /* DateTime firstweek = commonHelper.GetDateTimeWeekFirstDayMon(Convert.ToDateTime(date1));//周 DateTime lastweek = commonHelper.GetDateTimeWeekLastDaySun(Convert.ToDateTime(date1));//周*/ DateTime currentDate = DateTime.Parse(date1); // 获取指定日期的开始时间 DateTime startTime = new DateTime(currentDate.Year, currentDate.Month, currentDate.Day, 0, 0, 0);//日 // 获取指定日期的结束时间（即下一天的开始时间，减去1秒） DateTime endTime = new DateTime(currentDate.Year, currentDate.Month, currentDate.Day, 23, 59, 59).AddSeconds(-1);//日 DateTime RateStart = startTime; DateTime RateendTime = endTime; if (dateType.Length > 0) { currentDate = DateTime.Now; RateStart = new DateTime(currentDate.Year, currentDate.Month, currentDate.Day, 0, 0, 0);//日 RateendTime = new DateTime(currentDate.Year, currentDate.Month, currentDate.Day, 0, 0, 0);//日 } /* dStruct.Order = "HandleTime desc";*/ DateTime userDatetime1 = DateTime.Parse(date1); DateTime userDatetime2 = DateTime.Parse(date2); if (date1.Equals(date2)) { userDatetime1 = startTime; userDatetime2 = endTime; } StringBuilder sql = new StringBuilder(); if (username.Length > 0 && org.Length > 0) { sql.AppendFormat("\t\tSELECT\r\n A.DesignUserId,\r\n B.OrgID,\r\n B.Name AS DesignerName,\r\n SUM(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{3}' AND '{4}' THEN A.payment ELSE 0 END) AS Amount,\r\n\t\t COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{3}' AND '{4}' AND A.IsRefund ='2' THEN 1 END) AS RefundCount,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{3}' AND '{4}' THEN 1 END) AS AllOrder,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{3}' AND '{4}' AND A.OrderState ='3' THEN 1 END) AS WaitDesign,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{3}' AND '{4}' AND A.OrderState ='4' THEN 1 END) AS Designing,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{3}' AND '{4}' AND A.OrderState >='5' THEN 1 END) AS DesignFinish,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) = CONVERT(date, A.FinishDesignTime) AND CONVERT(date, A.WaitDesignTime) \t\t BETWEEN '{6}' AND '{7}' THEN 1 END) AS FinalizationRate,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{6}' AND '{7}' THEN 1 END) AS FinalizationRateSum\r\nFROM\r\n dbo.CE_ErpTradeCell AS A\r\n LEFT OUTER JOIN dbo.view_ErpUser AS B ON A.DesignUserId = B.ID\r\nWHERE\r\n B.OrgID = '{2}' AND b.Name like '{5}%' and a.ctid NOT LIKE 'S%' and b.state = '0'\r\nGROUP BY\r\n A.DesignUserId, B.OrgID, B.Name ORDER BY\r\nAmount DESC;", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"), org, userDatetime1.ToString("yyyy-MM-dd HH:mm:ss"), userDatetime2.ToString("yyyy-MM-dd HH:mm:ss"), username, RateStart.ToString("yyyy-MM-dd HH:mm:ss"), RateendTime.ToString("yyyy-MM-dd HH:mm:ss")); } else if (org.Length > 0 && username.Length == 0 && sstr.Length == 0) { sql.AppendFormat("\t\tSELECT\r\n A.DesignUserId,\r\n B.OrgID,\r\n B.Name AS DesignerName,\r\n SUM(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{3}' AND '{4}' THEN A.payment ELSE 0 END) AS Amount,\r\n\t\t COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{3}' AND '{4}' AND A.IsRefund ='2' THEN 1 END) AS RefundCount,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{3}' AND '{4}' THEN 1 END) AS AllOrder,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{3}' AND '{4}' AND A.OrderState ='3' THEN 1 END) AS WaitDesign,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{3}' AND '{4}' AND A.OrderState ='4' THEN 1 END) AS Designing,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{3}' AND '{4}' AND A.OrderState >='5' THEN 1 END) AS DesignFinish,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) = CONVERT(date, A.FinishDesignTime) AND CONVERT(date, A.WaitDesignTime) \t\t BETWEEN '{5}' AND '{6}' THEN 1 END) AS FinalizationRate,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{5}' AND '{6}' THEN 1 END) AS FinalizationRateSum\r\nFROM\r\n dbo.CE_ErpTradeCell AS A\r\n LEFT OUTER JOIN dbo.view_ErpUser AS B ON A.DesignUserId = B.ID\r\nWHERE\r\n B.OrgID = '{2}' and a.ctid NOT LIKE 'S%' and b.state = '0'\r\nGROUP BY\r\n A.DesignUserId, B.OrgID, B.Name ORDER BY\r\nAmount DESC;", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"), org, userDatetime1.ToString("yyyy-MM-dd HH:mm:ss"), userDatetime2.ToString("yyyy-MM-dd HH:mm:ss"), RateStart.ToString("yyyy-MM-dd HH:mm:ss"), RateendTime.ToString("yyyy-MM-dd HH:mm:ss")); } else if (org.Length > 0 && sstr.Length > 0) sql.AppendFormat("\t\tSELECT\r\n A.DesignUserId,\r\n B.OrgID,\r\n B.Name AS DesignerName,\r\n SUM(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{3}' AND '{4}' THEN A.payment ELSE 0 END) AS Amount,\r\n\t\t COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{3}' AND '{4}' AND A.IsRefund ='2' THEN 1 END) AS RefundCount,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{3}' AND '{4}' THEN 1 END) AS AllOrder,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{3}' AND '{4}' AND A.OrderState ='3' THEN 1 END) AS WaitDesign,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{3}' AND '{4}' AND A.OrderState ='4' THEN 1 END) AS Designing,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{3}' AND '{4}' AND A.OrderState >='5' THEN 1 END) AS DesignFinish,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) = CONVERT(date, A.FinishDesignTime) AND CONVERT(date, A.WaitDesignTime) \t\t BETWEEN '{6}' AND '{7}' THEN 1 END) AS FinalizationRate,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{6}' AND '{7}' THEN 1 END) AS FinalizationRateSum\r\nFROM\r\n dbo.CE_ErpTradeCell AS A\r\n LEFT OUTER JOIN dbo.view_ErpUser AS B ON A.DesignUserId = B.ID\r\nWHERE\r\n B.OrgID = '{2}' and A.IsSample= '{5}' and a.ctid NOT LIKE 'S%' and b.state = '0'\r\nGROUP BY\r\n A.DesignUserId, B.OrgID, B.Name ORDER BY\r\nAmount DESC;", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"), org, userDatetime1.ToString("yyyy-MM-dd HH:mm:ss"), userDatetime2.ToString("yyyy-MM-dd HH:mm:ss"), sstr, RateStart.ToString("yyyy-MM-dd HH:mm:ss"), RateendTime.ToString("yyyy-MM-dd HH:mm:ss")); { } DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; writeGridDataTableJson(dStruct.TotalCount, dt); } public void get_erp_designer_gather_rate2() { /* newDesignerRate();*/ DataStruct dStruct = GetPostStruct(); List lw = new List(); int st = GetInt("st"); string tid = GetPostString("ctid"); string org = GetPostString("org"); string sstr = GetPostString("order_area"); string username = GetPostString("designer"); string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); string dateType = GetPostString("dateType"); DateTime currentDate = DateTime.Parse(date1); // 获取指定日期的开始时间 DateTime startTime = new DateTime(currentDate.Year, currentDate.Month, currentDate.Day, 0, 0, 0);//日 DateTime currentTime = startTime; DateTime currentDate2 = DateTime.Parse(date2); // 获取指定日期的结束时间（即下一天的开始时间，减去1秒） DateTime endTime = new DateTime(currentDate2.Year, currentDate2.Month, currentDate2.Day, 23, 59, 59).AddSeconds(-1);//日 TimeSpan diff = endTime - startTime; int daysDiff = diff.Days + 1; string currentTimeString = currentTime.ToString("yyyy-MM-dd HH:mm:ss"); int daysToMonday = ((int)startTime.DayOfWeek - (int)DayOfWeek.Monday + 7) % 7; int daysToSunday = 6 - ((int)startTime.DayOfWeek - (int)DayOfWeek.Monday + 7) % 7; // 使用 AddDays 和 DateProperties 方法获取周一的第一秒和周日的最后一秒 DateTime firstweek = startTime.AddDays(-daysToMonday).Date; DateTime lastweek = startTime.AddDays(daysToSunday).Date.Add(new TimeSpan(23, 59, 59)); /* DateTime firstweek = Convert.ToDateTime(currentTimeString).AddDays(-(int)Convert.ToDateTime(currentTimeString).DayOfWeek);//周 DateTime lastweek = firstweek.AddDays(6);//周*/ DateTime firstDay = commonHelper.FirstDayOfMonth(Convert.ToDateTime(currentTimeString));//月 DateTime lastDay = commonHelper.LastDayOfMonth(Convert.ToDateTime(currentTimeString));//月 if (!org.Equals("") && username.Equals("")) { StringBuilder sql1 = new StringBuilder(); sql1.AppendFormat("SELECT a.did,a.name,sum(a.Refund) AS Refunds ,SUM ( CAST ( a.Amount AS DECIMAL ( 10, 2 ) ) ) AS Amount, SUM(CAST(onFinalizationRate AS decimal(10, 4))) AS totalAmount,SUM ( CAST ( a.AllOrder AS DECIMAL ( 10, 2 ) ) ) AS AllOrder, SUM ( CAST ( a.FinalizationRate AS DECIMAL ( 10, 2 ) ) ) AS FinalizationRate, SUM ( CAST ( a.splitSum AS DECIMAL ( 10, 2 ) ) ) AS splitSum, SUM ( CAST ( a.csplitum AS DECIMAL ( 10, 2 ) ) ) AS csplitum,SUM(CAST(onRefund AS decimal(10, 4))) AS toRefund,(SELECT COUNT(CONVERT(date, ctime)) AS days FROM [dbo].[Ce_ErpDesignerFinishRateNew] WHERE did = a.did and AllOrder > 0 and ctime BETWEEN '{0}' AND '{1}')AS msum FROM [dbo].[Ce_ErpDesignerFinishRateNew] as a LEFT JOIN view_ErpUser u ON a.did = u.id WHERE ctime BETWEEN '{0}' AND '{1}' AND u.OrgID = '{2}' GROUP BY a.did,a.name", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"), org); DataTable dt = null; dt = DbHelper.DbConn.ExecuteDataset(sql1.ToString()).Tables[0]; DataStruct dStruct2 = GetPostStruct(); List lw2 = new List(); StringBuilder sql = new StringBuilder(); sql.AppendFormat("SELECT A.DesignUserId AS did,SUM ( CASE WHEN A.FinishDesignTime IS NOT NULL THEN A.payment ELSE 0 END ) AS designPayment,SUM ( A.payment ) AS TotalPayment,SUM (A.RefundFee ) AS refundPayment,COUNT( A.WaitDesignTime) AS waitSum, \r\n COUNT(DISTINCT CASE WHEN ctid NOT LIKE 'C%' AND ctid NOT LIKE 'S%' THEN tid ELSE NULL END) +\r\n COUNT(DISTINCT CASE WHEN ctid LIKE 'C%' AND ctid NOT LIKE 'S%' THEN tid ELSE NULL END) AS sumnum,\r\n\t COUNT(DISTINCT CASE WHEN ctid NOT LIKE 'C%' AND ctid NOT LIKE 'S%' AND OrderState = '3' THEN tid ELSE NULL END) +\r\n COUNT(DISTINCT CASE WHEN ctid LIKE 'C%' AND ctid NOT LIKE 'S%' AND OrderState = '3' THEN tid ELSE NULL END) AS WaitDesign, COUNT(DISTINCT CASE WHEN ctid NOT LIKE 'C%' AND ctid NOT LIKE 'S%' AND OrderState = '4' THEN tid ELSE NULL END) +\r\n COUNT(DISTINCT CASE WHEN ctid LIKE 'C%' AND ctid NOT LIKE 'S%' AND OrderState = '4' THEN tid ELSE NULL END) AS Designing,\r\n COUNT(DISTINCT CASE WHEN ctid NOT LIKE 'C%' AND ctid NOT LIKE 'S%' AND OrderState > '4' THEN tid ELSE NULL END) +\r\n COUNT(DISTINCT CASE WHEN ctid LIKE 'C%' AND ctid NOT LIKE 'S%' AND OrderState > '4' THEN tid ELSE NULL END) AS DesignFinish\r\n\t\t\t\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t\t\t\t\tdbo.CE_ErpTradeCell AS A\r\n\t\t\t\t\t\t\t\t\t\t\tLEFT OUTER JOIN dbo.view_ErpUser AS B ON A.DesignUserId = B.ID \r\n\t\t\t\t\t\t\t\t\t\tWHERE\r\n\t\t\t\t\t\t\t\t\t\t\tA.WaitDesignTime BETWEEN '{0}' \r\n\t\t\t\t\t\t\t\t\t\t\tAND '{1}' \r\n\t\t\t\t\t\t\t\t\t\t\tAND b.orgid = '{2}' \r\n\t\t\t\t\t\t\t\t\t\t\tAND a.IsSample!= 2 \r\n\t\t\t\t\t\t\t\t\tGROUP BY\r\n\tDesignUserId ", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"), org); DataTable dt2 = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; //获取月周定稿 StringBuilder sql3 = new StringBuilder(); /* sql3.AppendFormat("SELECT a.did,b.name, SUM(CAST(a.onFinalizationRate AS decimal(10, 4))) AS totalAmount,(SELECT COUNT(CONVERT(date, ctime)) AS days\r\nFROM [dbo].[Ce_ErpDesignerFinishRateNew]\r\nWHERE did = a.did and AllOrder > 0 and ctime BETWEEN '{0}' AND '{1}')AS msum FROM [dbo].[Ce_ErpDesignerFinishRateNew] as a LEFT JOIN CE_ErpUser as b on a.did = b.id WHERE a.ctime BETWEEN '{0}' AND '{1}' and a.orgid = '{2}' GROUP BY a.did,b.name", firstweek.ToString("yyyy-MM-dd HH:mm:ss"), lastweek.ToString("yyyy-MM-dd HH:mm:ss"), org);*/ sql3.AppendFormat("SELECT A.DesignUserId AS did, COUNT(CASE WHEN a.WaitDesignTime is not null THEN 1 END) AS WaitDesign, COUNT(CASE WHEN A.OrderState >= '5' and a.IsRefund !='2' and a.FinishDesignTime BETWEEN '{0}' AND '{1}' THEN 1 END) AS DesignFinish FROM dbo.CE_ErpTradeCell AS A LEFT OUTER JOIN dbo.view_ErpUser AS B ON A.DesignUserId = B.ID where A.WaitDesignTime BETWEEN '{0}' AND '{1}' and b.orgid = '{2}' and a.IsSample!=2 and a.ctid not like '%S%' GROUP BY DesignUserId ", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"), org); DataTable dt3 = DbHelper.DbConn.ExecuteDataset(sql3.ToString()).Tables[0]; string sqlFinish = string.Format("SELECT A.DesignUserId AS did, COUNT ( CASE WHEN a.FinishDesignTime BETWEEN '{0}' AND '{1}' THEN 1 END ) AS DesignFinish FROM dbo.CE_ErpTradeCell AS A LEFT OUTER JOIN dbo.view_ErpUser AS B ON A.DesignUserId = B.ID WHERE A.FinishDesignTime BETWEEN '{0}' AND '{1}' AND b.orgid = '{2}' AND a.IsSample!= 2 AND a.ctid NOT LIKE 'S_%' GROUP BY DesignUserId", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"), org); DataTable finishDesign = DbHelper.DbConn.ExecuteDataset(sqlFinish).Tables[0]; string sqlRefund = string.Format("SELECT A.DesignUserId AS did, COUNT ( DISTINCT A.tid ) AS refund FROM dbo.CE_ErpTradeRefund AS C LEFT OUTER JOIN dbo.CE_ErpTradeCell AS A ON c.tid= A.tid LEFT OUTER JOIN dbo.view_ErpUser AS B ON A.DesignUserId = B.ID WHERE A.WaitDesignTime BETWEEN '{0}' AND '{1}' AND b.orgid = '{2}' AND a.IsSample!= 2 AND a.ctid NOT LIKE '%S%' AND A.IsRefund > 0 AND ( C.created < A.FinishDesignTime OR A.FinishDesignTime IS NULL ) GROUP BY A.DesignUserId", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"), org); DataTable refundDesign = DbHelper.DbConn.ExecuteDataset(sqlRefund).Tables[0]; Dictionary> mapweek = new Dictionary>(); foreach (DataRow dr3 in dt3.Rows) { Dictionary map2 = new Dictionary(); double roundedResult = 0.00; double num1 = 0.00; double num2 = 0.00; if (!Convert.IsDBNull(dr3["WaitDesign"])) { num1 = Convert.ToDouble(dr3["DesignFinish"]); num2 = Convert.ToDouble(dr3["WaitDesign"]); /* double num1 = Convert.ToDouble(dr3["totalAmount"]); double num2 = Convert.ToDouble(dr3["msum"]);*/ double result = (double)num1 / num2; if (double.IsNaN(result)) { result = 0; } roundedResult = Math.Round(result * 100, 2); } map2.Add("DesignFinish", num1); map2.Add("WaitDesign", num2); map2.Add("AvgWeekRate", roundedResult); mapweek.Add(dr3["did"], map2); } Dictionary> mapDay = new Dictionary>(); StringBuilder sql4 = new StringBuilder(); sql4.AppendFormat("SELECT a.did,b.name, SUM(CAST(a.onFinalizationRate AS decimal(10, 4))) AS totalAmount,(SELECT COUNT(CONVERT(date, ctime)) AS days FROM [dbo].[Ce_ErpDesignerFinishRateNew] WHERE did = a.did and AllOrder > 0 and ctime BETWEEN '{0}' AND '{1}')AS msum FROM [dbo].[Ce_ErpDesignerFinishRateNew] as a LEFT JOIN CE_ErpUser as b on a.did = b.id WHERE a.ctime BETWEEN '{0}' AND '{1}' and a.orgid = '{2}' GROUP BY a.did,b.name", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"), org); DataTable dt4 = null; dt4 = DbHelper.DbConn.ExecuteDataset(sql4.ToString()).Tables[0]; foreach (DataRow dr4 in dt4.Rows) { Dictionary map2 = new Dictionary(); double roundedResult = 0.00; double num1 = 0.00; double num2 = 0.00; if (!Convert.IsDBNull(dr4["totalAmount"])) { num1 = Convert.ToDouble(dr4["totalAmount"]); num2 = Convert.ToDouble(dr4["msum"]); double result = (double)num1 / num2; if (double.IsNaN(result)) { result = 0; } roundedResult = Math.Round(result * 100, 2); } map2.Add("totalAmount", num1); map2.Add("msum", num2); map2.Add("AvgRate", roundedResult); mapDay.Add(dr4["did"], map2); } dt.Columns.Add("sumPlyAmount", typeof(String)); dt.Columns.Add("WaitDesign", typeof(int)); dt.Columns.Add("Designing", typeof(int)); dt.Columns.Add("DesignFinish", typeof(int)); dt.Columns.Add("sumNum", typeof(int)); dt.Columns.Add("onFinalizationRateSum", typeof(String));// dt.Columns.Add("onAvgWeekRate", typeof(String)); dt.Columns.Add("refundPayment", typeof(String)); dt.Columns.Add("designPayment", typeof(String)); dt.Columns.Add("onAvgRate", typeof(String)); dt.Columns.Add("toRefundDesign", typeof(String)); dt.Columns.Add("refund", typeof(int)); dt.Columns.Add("waitSum", typeof(int)); dt.Columns.Add("designTime", typeof(int)); Dictionary> map1 = new Dictionary>(); foreach (DataRow dr2 in dt2.Rows) { Dictionary map2 = new Dictionary(); map2.Add("TotalPayment", dr2["TotalPayment"]); map2.Add("WaitDesign", dr2["WaitDesign"]); map2.Add("Designing", dr2["Designing"]); map2.Add("DesignFinish", dr2["DesignFinish"]); map2.Add("sumNum", dr2["sumnum"]); map2.Add("waitSum", dr2["waitSum"]); map2.Add("refundPayment", dr2["refundPayment"]); map2.Add("designPayment", dr2["designPayment"]); map2.Add("designTime", 0); map2.Add("refund", 0); foreach (DataRow row in finishDesign.Rows) { if (row["did"].ToString() == dr2["did"].ToString()) { map2["designTime"] = row["DesignFinish"]; } } foreach (DataRow row in refundDesign.Rows) { if (row["did"].ToString() == dr2["did"].ToString()) { map2["refund"] = row["refund"]; } } map1.Add(dr2["did"], map2); } double sumRefund = 0; double sumPlyAmount = 0; double sumRefundPayment = 0; double sumDesignPayment = 0; double sumAmount = 0; int sumWaitDesign = 0; int sumDesigning = 0; int sumDesignFinish = 0; int sumNum = 0; int sumAllOrder = 0; int sumFinalizationRate = 0; int sumWeekDesign = 0; int sumWeekDesignFinish = 0; int sumMothDesign = 0; int sumMothDesignFinish = 0; double onFinalizationRateSum = 0; double toRefundDesign = 0; double onAvgWeekRate = 0; double onAvgRate = 0; int dif = 0; int waitSum = 0; int designTime = 0; foreach (DataRow dr in dt.Rows) { try { Dictionary map3 = map1[dr["did"]]; Dictionary map4 = mapweek[dr["did"]]; Dictionary map5 = mapDay[dr["did"]]; if (!Convert.IsDBNull(dr["totalAmount"])) { double num1 = Convert.ToDouble(dr["totalAmount"]); double num2 = Convert.ToDouble(dr["toRefund"]); double result = (double)num1 / Convert.ToDouble(dr["msum"]); double result2 = (double)num2 / Convert.ToDouble(dr["msum"]); if (double.IsNaN(result)) { result = 0; } if (double.IsNaN(result2)) { result2 = 0; } double roundedResult = Math.Round(result * 100, 2); dr["onFinalizationRateSum"] = roundedResult; double roundedResult2 = Math.Round(result2 * 100, 2); dr["toRefundDesign"] = roundedResult2; onFinalizationRateSum += roundedResult; toRefundDesign += roundedResult2; } else { dr["onFinalizationRateSum"] = "0.00"; } dr["AllOrder"] = Convert.ToDouble(dr["AllOrder"]) + Convert.ToDouble(dr["csplitum"]); dr["FinalizationRate"] = Convert.ToDouble(dr["FinalizationRate"]) + Convert.ToDouble(dr["splitSum"]); dr["refund"] = map3["refund"]; dr["sumPlyAmount"] = map3["TotalPayment"]; dr["WaitDesign"] = map3["WaitDesign"]; dr["refundPayment"] = map3["refundPayment"]; dr["designPayment"] = map3["designPayment"]; dr["Designing"] = map3["Designing"]; dr["DesignFinish"] = map3["DesignFinish"]; dr["sumNum"] = map3["sumNum"]; dr["onAvgWeekRate"] = map4["AvgWeekRate"]; dr["onAvgRate"] = map5["AvgRate"]; dr["waitSum"] = map3["waitSum"]; dr["designTime"] = map3["designTime"]; dr["onFinalizationRateSum"] = Math.Round(Convert.ToDouble(dr["FinalizationRate"]) / Convert.ToDouble(dr["AllOrder"]) * 100, 2); sumRefund += Convert.ToDouble(dr["refund"]); sumPlyAmount += Convert.ToDouble(dr["sumPlyAmount"]); sumDesignPayment += Convert.ToDouble(dr["designPayment"]); sumAmount += Convert.ToDouble(dr["Amount"]); sumRefundPayment += Convert.ToDouble(dr["refundPayment"]); sumWaitDesign += Convert.ToInt32(dr["WaitDesign"]); sumDesigning += Convert.ToInt32(dr["Designing"]); sumDesignFinish += Convert.ToInt32(dr["DesignFinish"]); sumNum += Convert.ToInt32(dr["sumNum"]); sumAllOrder += Convert.ToInt32(dr["AllOrder"]); sumFinalizationRate += Convert.ToInt32(dr["FinalizationRate"]); waitSum += Convert.ToInt32(dr["waitSum"]); designTime += Convert.ToInt32(dr["designTime"]); if (sumNum > 0) { dif++; } sumWeekDesign += Convert.ToInt32(map4["WaitDesign"]); sumWeekDesignFinish += Convert.ToInt32(map4["DesignFinish"]); sumMothDesign += Convert.ToInt32(map5["msum"]); sumMothDesignFinish += Convert.ToInt32(map5["totalAmount"]); onAvgWeekRate += Convert.ToDouble(map4["AvgWeekRate"]); onAvgRate += Convert.ToDouble(map5["AvgRate"]); } catch (Exception ex) { dr["sumPlyAmount"] = 0; dr["WaitDesign"] = 0; dr["Designing"] = 0; dr["DesignFinish"] = 0; dr["sumNum"] = 0; dr["waitSum"] = 0; dr["designTime"] = 0; dr["onAvgWeekRate"] = "0.00"; dr["onAvgRate"] = "0.00"; } } DataRow sumRow = dt.NewRow(); sumRow["AllOrder"] = sumAllOrder; sumRow["FinalizationRate"] = sumFinalizationRate; sumRow["refund"] = sumRefund; sumRow["sumPlyAmount"] = sumPlyAmount; sumRow["WaitDesign"] = sumWaitDesign; sumRow["designPayment"] = sumDesignPayment; sumRow["Designing"] = sumDesigning; sumRow["refundPayment"] = sumRefundPayment; sumRow["Amount"] = sumAmount; sumRow["DesignFinish"] = sumDesignFinish; sumRow["sumNum"] = sumNum; sumRow["waitSum"] = waitSum; sumRow["designTime"] = designTime; sumRow["onFinalizationRateSum"] = Math.Round(Convert.ToDouble(sumFinalizationRate) / sumAllOrder * 100, 2); sumRow["toRefundDesign"] = Math.Round(toRefundDesign / dif, 2); sumRow["onAvgWeekRate"] = Math.Round(Convert.ToDouble(sumWeekDesignFinish) / sumWeekDesign * 100, 2); sumRow["onAvgRate"] = Math.Round(Convert.ToDouble(sumMothDesignFinish) / sumMothDesign * 100, 2); dt.Rows.Add(sumRow); writeGridDataTableJson(dStruct.TotalCount, dt); } else if (!username.Equals("")) { StringBuilder sql1 = new StringBuilder(); sql1.AppendFormat("SELECT did,name,refund,onRefund,Amount,AllOrder,FinalizationRate,splitSum,csplitum, CONVERT(date, ctime) AS days, onFinalizationRate FROM [dbo].[Ce_ErpDesignerFinishRateNew]\r\nWHERE ctime BETWEEN '{0}' AND '{1}' AND name = '{2}' GROUP BY did,AllOrder, FinalizationRate,Amount, splitSum, csplitum, CONVERT(date, ctime),onFinalizationRate,name,refund,onRefund ORDER BY days ASC ", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"), username); DataTable dt = null; dt = DbHelper.DbConn.ExecuteDataset(sql1.ToString()).Tables[0]; StringBuilder sql = new StringBuilder(); sql.AppendFormat("SELECT\r\n A.DesignUserId AS did, SUM ( CASE WHEN A.FinishDesignTime IS NOT NULL THEN A.payment ELSE 0 END ) AS designPayment,COUNT( A.WaitDesignTime) AS waitSum,SUM ( A.RefundFee ) AS refundPayment, CONVERT(date, A.WaitDesignTime) AS days,\r\n SUM(A.payment) AS TotalPayment,\r\n COUNT(DISTINCT CASE WHEN ctid NOT LIKE 'C%' AND ctid NOT LIKE 'S%' AND A.IsSample != 2 THEN tid ELSE NULL END) +\r\n COUNT(DISTINCT CASE WHEN ctid LIKE 'C%' AND ctid NOT LIKE 'S%' AND A.IsSample != 2 THEN tid ELSE NULL END) AS sumnum,\r\n COUNT(DISTINCT CASE WHEN ctid NOT LIKE 'C%' AND ctid NOT LIKE 'S%' AND OrderState = '3' AND A.IsSample != 2 THEN tid ELSE NULL END) +\r\n COUNT(DISTINCT CASE WHEN ctid LIKE 'C%' AND ctid NOT LIKE 'S%' AND OrderState = '3' AND A.IsSample != 2 THEN tid ELSE NULL END) AS WaitDesign,\r\n COUNT(DISTINCT CASE WHEN ctid NOT LIKE 'C%' AND ctid NOT LIKE 'S%' AND OrderState = '4' AND A.IsSample != 2 THEN tid ELSE NULL END) +\r\n COUNT(DISTINCT CASE WHEN ctid LIKE 'C%' AND ctid NOT LIKE 'S%' AND OrderState = '4' AND A.IsSample != 2 THEN tid ELSE NULL END) AS Designing,\r\n COUNT(DISTINCT CASE WHEN ctid NOT LIKE 'C%' AND ctid NOT LIKE 'S%' AND OrderState > '4' AND A.IsSample != 2 THEN tid ELSE NULL END) +\r\n COUNT(DISTINCT CASE WHEN ctid LIKE 'C%' AND ctid NOT LIKE 'S%' AND OrderState > '4' AND A.IsSample != 2 THEN tid ELSE NULL END) AS DesignFinish\r\nFROM\r\n dbo.CE_ErpTradeCell AS A\r\nLEFT OUTER JOIN\r\n dbo.view_ErpUser AS B ON A.DesignUserId = B.ID\r\nWHERE\r\n A.WaitDesignTime BETWEEN '{0}' AND '{1}'\r\n AND B.name = '{2}'\r\nGROUP BY\r\n A.DesignUserId, CONVERT(date, A.WaitDesignTime)\r\nORDER BY\r\n days ASC", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"), username); DataTable dt2 = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; Dictionary> map1 = new Dictionary>(); foreach (DataRow dr2 in dt2.Rows) { Dictionary map2 = new Dictionary(); map2.Add("TotalPayment", dr2["TotalPayment"]); map2.Add("refundPayment", dr2["refundPayment"]); map2.Add("designPayment", dr2["designPayment"]); map2.Add("WaitDesign", dr2["WaitDesign"]); map2.Add("Designing", dr2["Designing"]); map2.Add("DesignFinish", dr2["DesignFinish"]); map2.Add("sumNum", dr2["sumnum"]); map2.Add("waitSum", dr2["waitSum"]); map1.Add(dr2["days"], map2); } StringBuilder sql3 = new StringBuilder(); sql3.AppendFormat("SELECT A.DesignUserId AS did,CONVERT ( DATE, A.WaitDesignTime ) AS days, COUNT(CASE WHEN a.WaitDesignTime is not null THEN 1 END) AS WaitDesign,\r\n COUNT(CASE WHEN A.OrderState >= '5' and a.IsRefund !='2' and a.FinishDesignTime BETWEEN '{0}' AND '{1}' THEN 1 END) AS DesignFinish\r\n FROM\r\n dbo.CE_ErpTradeCell AS A \r\n LEFT OUTER JOIN dbo.view_ErpUser AS B ON A.DesignUserId = B.ID where A.WaitDesignTime BETWEEN '{0}' AND '{1}'\t and b.name = '{2}'\r\n and a.IsSample!=2 and a.ctid not like '%S%' GROUP BY\r\n DesignUserId, CONVERT ( DATE, A.WaitDesignTime ) ", firstweek.ToString("yyyy-MM-dd HH:mm:ss"), lastweek.ToString("yyyy-MM-dd HH:mm:ss"), username); DataTable dt3 = DbHelper.DbConn.ExecuteDataset(sql3.ToString()).Tables[0]; string finishSql = string.Format("SELECT A.DesignUserId AS did, CONVERT ( DATE, A.FinishDesignTime ) AS days, COUNT ( CASE WHEN a.FinishDesignTime BETWEEN '{0}' AND '{1}' THEN 1 END ) AS DesignFinish FROM dbo.CE_ErpTradeCell AS A LEFT OUTER JOIN dbo.view_ErpUser AS B ON A.DesignUserId = B.ID WHERE A.FinishDesignTime BETWEEN '{0}' AND '{1}' AND b.name = '{2}' AND a.IsSample!= 2 AND a.ctid NOT LIKE 'S%'GROUP BY DesignUserId, CONVERT ( DATE, A.FinishDesignTime )", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"), username); DataTable finishDesign = DbHelper.DbConn.ExecuteDataset(finishSql).Tables[0]; string refundSql = string.Format("SELECT A.DesignUserId AS did, CONVERT ( DATE, C.created ) AS days, COUNT ( * ) AS refund FROM dbo.CE_ErpTradeRefund AS C LEFT OUTER JOIN dbo.CE_ErpTradeCell AS A ON A.tid = c.tid LEFT OUTER JOIN dbo.view_ErpUser AS B ON A.DesignUserId = B.ID WHERE A.WaitDesignTime BETWEEN '{0}' AND '{1}' AND b.name = '{2}' AND a.IsSample!= 2 AND a.ctid NOT LIKE 'S%' AND a.IsRefund > 0 AND ( C.created < A.FinishDesignTime OR A.FinishDesignTime IS NULL ) GROUP BY DesignUserId, CONVERT ( DATE, C.created )", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"), username); DataTable refundDesign = DbHelper.DbConn.ExecuteDataset(refundSql).Tables[0]; Dictionary> mapweek = new Dictionary>(); foreach (DataRow dr3 in dt3.Rows) { Dictionary map2 = new Dictionary(); double roundedResult = 0.00; double num1 = 0.00; double num2 = 0.00; if (!Convert.IsDBNull(dr3["WaitDesign"])) { num1 = Convert.ToDouble(dr3["DesignFinish"]); num2 = Convert.ToDouble(dr3["WaitDesign"]); /* double num1 = Convert.ToDouble(dr3["totalAmount"]); double num2 = Convert.ToDouble(dr3["msum"]);*/ double result = (double)num1 / num2; if (double.IsNaN(result)) { result = 0; } roundedResult = Math.Round(result * 100, 2); } map2.Add("DesignFinish", num1); map2.Add("WaitDesign", num2); map2.Add("AvgWeekRate", roundedResult); mapweek.Add(dr3["days"], map2); } Dictionary> mapDay = new Dictionary>(); StringBuilder sql4 = new StringBuilder(); string orgId = ""; if (org.Length > 0) { orgId = " AND a.orgid = " + org; } sql4.AppendFormat("SELECT a.did,b.name, CONVERT ( DATE, A.ctime ) AS days,SUM(CAST(a.onFinalizationRate AS decimal(10, 4))) AS totalAmount,(SELECT COUNT(CONVERT(date, ctime)) AS days\r\nFROM [dbo].[Ce_ErpDesignerFinishRateNew]\r\nWHERE did = a.did and AllOrder+csplitum > 0 and ctime BETWEEN '{0}' AND '{1}')AS msum FROM [dbo].[Ce_ErpDesignerFinishRateNew] as a LEFT JOIN CE_ErpUser as b on a.did = b.id WHERE a.ctime BETWEEN '{0}' AND '{1}' and a.name = '{2}' " + orgId + " GROUP BY a.did,b.name,CONVERT ( DATE, A.ctime )", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"), username); DataTable dt4 = null; dt4 = DbHelper.DbConn.ExecuteDataset(sql4.ToString()).Tables[0]; foreach (DataRow dr4 in dt4.Rows) { Dictionary map2 = new Dictionary(); double roundedResult = 0.00; double num1 = 0.00; double num2 = 0.00; if (!Convert.IsDBNull(dr4["totalAmount"])) { num1 = Convert.ToDouble(dr4["totalAmount"]); num2 = Convert.ToDouble(dr4["msum"]); double result = (double)num1 / num2; if (double.IsNaN(result)) { result = 0; } roundedResult = Math.Round(result * 100, 2); } map2.Add("totalAmount", num1); map2.Add("msum", num2); map2.Add("AvgRate", roundedResult); map2.Add("designTime", 0); map2.Add("refund", 0); foreach (DataRow row in finishDesign.Rows) { if (dr4["days"].ToString() == row["days"].ToString()) { map2["designTime"] = row["DesignFinish"]; } } foreach (DataRow row in refundDesign.Rows) { if (dr4["days"].ToString() == row["days"].ToString()) { map2["refund"] = row["refund"]; } } mapDay.Add(dr4["days"], map2); } dt.Columns.Add("sumPlyAmount", typeof(String)); dt.Columns.Add("WaitDesign", typeof(int)); dt.Columns.Add("Designing", typeof(int)); dt.Columns.Add("DesignFinish", typeof(int)); dt.Columns.Add("sumNum", typeof(int)); dt.Columns.Add("onFinalizationRateSum", typeof(String));// dt.Columns.Add("onAvgWeekRate", typeof(String)); dt.Columns.Add("onAvgRate", typeof(String)); dt.Columns.Add("toRefundDesign", typeof(String)); dt.Columns.Add("waitSum", typeof(int)); dt.Columns.Add("refundPayment", typeof(String)); dt.Columns.Add("designPayment", typeof(String)); dt.Columns.Add("designTime", typeof(String)); double sumRefund = 0; double sumPlyAmount = 0; double sumRefundPayment = 0; double sumDesignPayment = 0; double sumAmount = 0; int sumWaitDesign = 0; int sumDesigning = 0; int sumDesignFinish = 0; int sumNum = 0; int sumAllOrder = 0; int sumFinalizationRate = 0; int sumWeekDesign = 0; int sumWeekDesignFinish = 0; int sumMothDesign = 0; int sumMothDesignFinish = 0; double onFinalizationRateSum = 0; double toRefundDesign = 0; double onAvgWeekRate = 0; double onAvgRate = 0; int dif = 0; int waitSum = 0; int designTime = 0; foreach (DataRow dr in dt.Rows) { try { Dictionary map3 = new Dictionary(); Dictionary map4 = new Dictionary(); Dictionary map5 = new Dictionary(); if (map1.ContainsKey(dr["days"])) { map3 = map1[dr["days"]]; } if (mapweek.ContainsKey(dr["days"])) { map4 = mapweek[dr["days"]]; } if (mapDay.ContainsKey(dr["days"])) { map5 = mapDay[dr["days"]]; } dr["AllOrder"] = Convert.ToDouble(dr["AllOrder"]) + Convert.ToDouble(dr["csplitum"]); dr["FinalizationRate"] = Convert.ToDouble(dr["FinalizationRate"]) + Convert.ToDouble(dr["splitSum"]); if (!Convert.IsDBNull(dr["onFinalizationRate"])) { double num1 = Convert.ToDouble(dr["onFinalizationRate"]); double result = Convert.ToDouble(dr["FinalizationRate"]) / Convert.ToDouble(dr["AllOrder"]); if (double.IsNaN(result)) { result = 0; } double roundedResult = Math.Round(result * 100, 2); dr["onFinalizationRateSum"] = roundedResult; onFinalizationRateSum += roundedResult; } else { dr["onFinalizationRateSum"] = "0.00"; } double roundedResults = Math.Round(Convert.ToDouble(dr["onRefund"]) * 100, 2); toRefundDesign += roundedResults; dr["toRefundDesign"] = roundedResults; dr["sumPlyAmount"] = map3.ContainsKey("TotalPayment") ? map3["TotalPayment"] : 0; dr["refundPayment"] = map3.ContainsKey("refundPayment") ? map3["refundPayment"] : 0; dr["WaitDesign"] = map3.ContainsKey("WaitDesign") ? map3["WaitDesign"] : 0; dr["designPayment"] = map3.ContainsKey("designPayment") ? map3["designPayment"] : 0; dr["Designing"] = map3.ContainsKey("Designing") ? map3["Designing"] : 0; dr["DesignFinish"] = map3.ContainsKey("DesignFinish") ? map3["DesignFinish"] : 0; dr["sumNum"] = map3.ContainsKey("sumNum") ? map3["sumNum"] : 0; dr["onAvgWeekRate"] = map4.ContainsKey("AvgWeekRate") ? map4["AvgWeekRate"] : 0; dr["onAvgRate"] = map5.ContainsKey("AvgRate") ? map5["AvgRate"] : 0; dr["waitSum"] = map3.ContainsKey("waitSum") ? map3["waitSum"] : 0; dr["designTime"] = map5.ContainsKey("designTime") ? map5["designTime"] : 0; dr["refund"] = map5.ContainsKey("refund") ? map5["refund"] : 0; sumRefund += Convert.ToDouble(dr["refund"]); sumPlyAmount += Convert.ToDouble(dr["sumPlyAmount"]); sumRefundPayment += Convert.ToDouble(dr["refundPayment"]); sumDesignPayment += Convert.ToDouble(dr["designPayment"]); sumAmount += Convert.ToDouble(dr["Amount"]); sumWaitDesign += Convert.ToInt32(dr["WaitDesign"]); sumDesigning += Convert.ToInt32(dr["Designing"]); sumDesignFinish += Convert.ToInt32(dr["DesignFinish"]); sumNum += Convert.ToInt32(dr["sumNum"]); waitSum += Convert.ToInt32(dr["waitSum"]); designTime += Convert.ToInt32(dr["designTime"]); sumAllOrder += Convert.ToInt32(dr["AllOrder"]); sumFinalizationRate += Convert.ToInt32(dr["FinalizationRate"]); if (sumNum > 0) { dif++; } sumWeekDesign += Convert.ToInt32(map4.ContainsKey("WaitDesign") ? map4["WaitDesign"] : 0); sumWeekDesignFinish += Convert.ToInt32(map4.ContainsKey("DesignFinish") ? map4["DesignFinish"] : 0); sumMothDesign += Convert.ToInt32(map5["msum"]); sumMothDesignFinish += Convert.ToInt32(map5["totalAmount"]); onAvgWeekRate += Convert.ToDouble(map4.ContainsKey("AvgWeekRate") ? map4["AvgWeekRate"] : 0); onAvgRate += Convert.ToDouble(map5.ContainsKey("AvgRate") ? map5["AvgRate"] : 0); } catch (Exception ex) { dr["sumPlyAmount"] = 0; dr["WaitDesign"] = 0; dr["Designing"] = 0; dr["DesignFinish"] = 0; dr["sumNum"] = 0; dr["waitSum"] = 0; dr["designTime"] = 0; dr["onAvgWeekRate"] = "0.00%"; dr["onAvgRate"] = "0.00%"; } } DataRow sumRow = dt.NewRow(); sumRow["refund"] = sumRefund; sumRow["sumPlyAmount"] = sumPlyAmount; sumRow["WaitDesign"] = sumWaitDesign; sumRow["designPayment"] = sumDesignPayment; sumRow["Designing"] = sumDesigning; sumRow["refundPayment"] = sumRefundPayment; sumRow["Amount"] = sumAmount; sumRow["DesignFinish"] = sumDesignFinish; sumRow["sumNum"] = sumNum; sumRow["waitSum"] = waitSum; sumRow["designTime"] = designTime; sumRow["AllOrder"] = sumAllOrder; sumRow["FinalizationRate"] = sumFinalizationRate; sumRow["onFinalizationRateSum"] = Math.Round(Convert.ToDouble(sumFinalizationRate) / sumAllOrder * 100, 2); sumRow["toRefundDesign"] = Math.Round(toRefundDesign / dif, 2); sumRow["onAvgWeekRate"] = Math.Round(Convert.ToDouble(sumWeekDesign) / sumWeekDesignFinish * 100, 2); sumRow["onAvgRate"] = Math.Round(Convert.ToDouble(sumMothDesign) / sumMothDesignFinish * 100, 2); dt.Rows.Add(sumRow); writeGridDataTableJson(dStruct.TotalCount, dt); } } public void new_Designer_Rate() { String date1 = DateTime.Now.ToString(); /* if (date1 ==null) { date1 = DateTime.Now.ToString(); }*/ DateTime currentDate = DateTime.Parse(date1); // 获取指定日期的开始时间 DateTime startTime = new DateTime(currentDate.Year, currentDate.Month, currentDate.Day, 0, 0, 0);//日 // 获取指定日期的结束时间（即下一天的开始时间，减去1秒） DateTime endTime = new DateTime(currentDate.Year, currentDate.Month, currentDate.Day, 23, 59, 59).AddSeconds(-1);//日 DateTime now = DateTime.Now; // 获取当前日期和时间 DateTime dateOnly = now.Date; // 限制日期为年月日 StringBuilder sql = new StringBuilder(); CeErpDesignerFinishRateNew.Del(startTime.ToString("yyyy-MM-dd HH:mm:ss")); sql.AppendFormat("SELECT A.DesignUserId, B.OrgID, B.Name AS DesignerName, COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{0}' AND '{1}' and A.IsRefund ='2' THEN 1 END) AS refund, SUM(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{0}' AND '{1}' THEN A.payment ELSE 0 END) AS Amount,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{0}' AND '{1}' and A.ctid NOT LIKE 'C%' and A.ctid NOT LIKE 'S%' THEN 1 END) AS AllOrder,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{0}' AND '{1}' AND A.OrderState ='3' THEN 1 END) AS WaitDesign,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{0}' AND '{1}' AND A.OrderState ='4' THEN 1 END) AS Designing,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) BETWEEN '{0}' AND '{1}' AND A.OrderState >='5' THEN 1 END) AS DesignFinish,\r\n COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) = CONVERT(date, A.FinishDesignTime) AND CONVERT(date, A.WaitDesignTime) BETWEEN '{0}' AND '{1}' and A.ctid NOT LIKE 'C%' and A.ctid NOT LIKE 'S%' THEN 1 END) AS FinalizationRate,\r\n (SELECT COUNT(*) FROM CE_ErpTradeCell WHERE ctid LIKE 'S%' AND WaitDesignTime BETWEEN '{0}' AND '{1}' and DesignUserId =A.DesignUserId) AS afterSaleSum,\r\n\t\t(SELECT COUNT(tid) \r\n FROM CE_ErpTradeCell \r\n WHERE ctid LIKE 'C%' \r\n AND WaitDesignTime BETWEEN '{0}' AND '{1}' \r\n AND DesignUserId = A.DesignUserId) AS resolutionSum,\r\n\t\t \t(SELECT COUNT(DISTINCT tid) \r\n FROM CE_ErpTradeCell \r\n WHERE ctid LIKE 'C%' \r\n AND WaitDesignTime BETWEEN '{0}' AND '{1}' \r\n AND DesignUserId = A.DesignUserId and FinishDesignTime BETWEEN '{0}' AND '{1}') AS splitSum,\r\n\t\t \t \t(SELECT COUNT(DISTINCT tid) \r\n FROM CE_ErpTradeCell \r\n WHERE ctid LIKE 'C%' \r\n AND WaitDesignTime BETWEEN '{0}' AND '{1}' \r\n AND DesignUserId = A.DesignUserId ) AS splitSum2 ,COUNT(CASE WHEN CONVERT(date, A.WaitDesignTime) = CONVERT(date, A.FinishDesignTime) AND CONVERT(date, A.WaitDesignTime) BETWEEN '{0}' AND '{1}' and A.ctid LIKE 'C%' THEN 1 END) AS FinalizationRate2 \r\nFROM\r\n dbo.CE_ErpTradeCell AS A\r\n LEFT OUTER JOIN dbo.view_ErpUser AS B ON A.DesignUserId = B.ID\r\nWHERE\r\n b.state = '0' and a.IsSample!=2\r\nGROUP BY\r\n A.DesignUserId, B.OrgID, B.Name\r\nORDER BY\r\n Amount DESC;", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss")); DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; foreach (DataRow dr in dt.Rows) { CeErpDesignerFinishRateNew ceErpDesignerFinishRateNew = new CeErpDesignerFinishRateNew(); if (!Convert.IsDBNull(dr["DesignUserId"])) { ceErpDesignerFinishRateNew.did = Convert.ToInt32(dr["DesignUserId"] == null ? 0 : dr["DesignUserId"]); } if (!Convert.IsDBNull(dr["OrgID"])) { ceErpDesignerFinishRateNew.orgid = Convert.ToInt32(dr["OrgID"] == null ? 0 : dr["OrgID"]); } if (!Convert.IsDBNull(dr["DesignerName"])) { ceErpDesignerFinishRateNew.name = dr["DesignerName"].ToString() == null ? "" : dr["DesignerName"].ToString(); } if (!Convert.IsDBNull(dr["Amount"])) { ceErpDesignerFinishRateNew.Amount = dr["Amount"].ToString() == null ? "" : dr["Amount"].ToString(); } if (!Convert.IsDBNull(dr["AllOrder"])) { ceErpDesignerFinishRateNew.AllOrder = Convert.ToInt32(dr["AllOrder"] == null ? 0 : dr["AllOrder"]); } if (!Convert.IsDBNull(dr["FinalizationRate"])) { ceErpDesignerFinishRateNew.FinalizationRate = Convert.ToInt32(dr["FinalizationRate"] == null ? 0 : dr["FinalizationRate"]); } if (!Convert.IsDBNull(dr["afterSaleSum"])) { ceErpDesignerFinishRateNew.afterSaleSum = Convert.ToInt32(dr["afterSaleSum"] == null ? 0 : dr["afterSaleSum"]); } if (!Convert.IsDBNull(dr["resolutionSum"])) { ceErpDesignerFinishRateNew.resolutionSum = Convert.ToInt32(dr["resolutionSum"] == null ? 0 : dr["resolutionSum"]); } if (!Convert.IsDBNull(dr["splitSum"])) { ceErpDesignerFinishRateNew.splitSum = Convert.ToInt32(dr["splitSum"] == null ? 0 : dr["splitSum"]); } if (!Convert.IsDBNull(dr["splitSum2"])) { ceErpDesignerFinishRateNew.csplitum = Convert.ToInt32(dr["splitSum2"] == null ? 0 : dr["splitSum2"]); } if (!Convert.IsDBNull(dr["FinalizationRate2"])) { ceErpDesignerFinishRateNew.FinalizationRate2 = Convert.ToInt32(dr["FinalizationRate2"] == null ? 0 : dr["FinalizationRate2"]); } if (!Convert.IsDBNull(dr["AllOrder"]) && !Convert.IsDBNull(dr["FinalizationRate"]) && !Convert.IsDBNull(dr["splitSum2"]) && !Convert.IsDBNull(dr["splitSum"])) { try { int num1 = Convert.ToInt32(dr["FinalizationRate"]) + Convert.ToInt32(dr["splitSum"]); int num2 = Convert.ToInt32(dr["AllOrder"]) + Convert.ToInt32(dr["splitSum2"]); if (num2 != 0) { double result = (double)num1 / num2; if (double.IsNaN(result)) { result = 0; } double roundedResult = Math.Round(result, 4); ceErpDesignerFinishRateNew.onFinalizationRate = roundedResult.ToString(); int num3 = Convert.ToInt32(dr["refund"]); double result2 = (double)num3 / num2; if (double.IsNaN(result2)) { result2 = 0; } double roundedResult2 = Math.Round(result2, 4); ceErpDesignerFinishRateNew.onRefund = roundedResult2.ToString(); } else { ceErpDesignerFinishRateNew.onFinalizationRate = "0.0000"; ceErpDesignerFinishRateNew.onRefund = "0.0000"; } } catch (Exception ex) { ceErpDesignerFinishRateNew.onFinalizationRate = "0"; } } ceErpDesignerFinishRateNew.ctime = startTime; ceErpDesignerFinishRateNew.Createtime = now; if (!Convert.IsDBNull(dr["refund"])) { ceErpDesignerFinishRateNew.refund = Convert.ToInt32(dr["refund"] == null ? 0 : dr["refund"]); } ceErpDesignerFinishRateNew.Create(); } returnSuccessMsg("获取成功"); return; } public void get_erp_designer_gather_rate() { int myOrg = CurrentUser.UserPost.OrgID; string org = GetPostString("org"); if (org == "") { returnSuccessMsg(""); return; org = "0"; } string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); int sample = -1; string sstr = GetPostString("order_area"); if (sstr.Length > 0) { sample = Convert.ToInt32(sstr); } string username = GetPostString("designer"); if (date1.Length <= 0) { date1 = DateTime.Now.ToString(); date2 = DateTime.Now.ToString(); } DateTime firstDay = commonHelper.FirstDayOfMonth(Convert.ToDateTime(date1)); firstDay.ToString("yyyy-MM-dd HH:mm:ss"); DateTime lastDay = commonHelper.LastDayOfMonth(Convert.ToDateTime(date1)); lastDay.ToString("yyyy-MM-dd HH:mm:ss"); DateTime firstweek = commonHelper.GetDateTimeWeekFirstDayMon(Convert.ToDateTime(date1)); DateTime lastweek = commonHelper.GetDateTimeWeekLastDaySun(Convert.ToDateTime(date1)); if (date1.Length > 0 && DateTime.Now.ToShortDateString() != Convert.ToDateTime(date1).ToShortDateString()) { string orgStr = ""; if (org.Length > 0) { orgStr = " and OrgId=" + org; } if (username.Length > 0) { orgStr = " and Name='" + username + "'"; } StringBuilder sql = new StringBuilder(); sql.AppendFormat("select * from view_ErpDesignerFinishRate where datediff(day,'{0}',CTime)>=0 and datediff(day,'{1}',CTime)<=0" + orgStr, date1, date2); DataTable dataDt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("DesignerName", typeof(string))); dt.Columns.Add(new DataColumn("Amount", typeof(string))); dt.Columns.Add(new DataColumn("AllOrder", typeof(string))); dt.Columns.Add(new DataColumn("WaitDesign", typeof(string))); dt.Columns.Add(new DataColumn("Designing", typeof(string))); dt.Columns.Add(new DataColumn("DesignFinish", typeof(string))); dt.Columns.Add(new DataColumn("FinalizationRate", typeof(string))); dt.Columns.Add(new DataColumn("PayPrice", typeof(string))); dt.Columns.Add(new DataColumn("AvgRate", typeof(string))); dt.Columns.Add(new DataColumn("Ctime", typeof(string))); dt.Columns.Add(new DataColumn("AvgWeekRate", typeof(string))); DataRow dr_he = dt.NewRow(); dr_he[0] = "合计"; dr_he[1] = 0; dr_he[2] = 0; dr_he[3] = 0; dr_he[4] = 0; dr_he[5] = 0; dr_he[6] = 0; dr_he[7] = 0; dr_he[8] = 0; dr_he[9] = null; dr_he[10] = 0; int ratenotZero = 0; foreach (DataRow onedes in dataDt.Rows) { DataRow dr = dt.NewRow(); dr[0] = onedes["Name"].ToString(); dr[1] = onedes["Amount"].ToString(); dr[2] = onedes["AllOrder"].ToString(); dr[3] = onedes["WaitDesign"].ToString(); dr[4] = onedes["Designing"].ToString(); dr[5] = onedes["DesignFinish"].ToString(); dr[9] = onedes["CTime"].ToString(); decimal rate = 0; if (onedes["AllOrder"].ToString() != "0") { rate = (decimal)(Convert.ToDecimal(onedes["DesignFinish"]) / Convert.ToDecimal(onedes["AllOrder"])); } decimal xxx = rate * 10000; int xxx2 = (int)xxx; decimal xxx3 = (decimal)xxx2 / 100; string brate = Convert.ToString(xxx3); //dr[6] = String.Format("{0:F}", rate); dr[6] = brate + "%"; int payPrice = 0; if (rate >= (decimal)0.80 && Convert.ToInt32(onedes["AllOrder"]) >= 45) { payPrice = 30; } else if (rate >= (decimal)0.70 && Convert.ToInt32(onedes["AllOrder"]) >= 35) { payPrice = 20; } else if (rate >= (decimal)0.65 && Convert.ToInt32(onedes["AllOrder"]) >= 25) { payPrice = 10; } dr[7] = payPrice; StringBuilder sql_pj = new StringBuilder(); sql_pj.AppendFormat("select sum(AllOrder) as total,sum(DesignFinish) as finish from view_ErpDesignerFinishRate where datediff(day,'{0}',CTime)>=0 and datediff(day,'{1}',CTime)<=0 and Name='{2}'", firstDay.ToString("yyyy-MM-dd HH:mm:ss"), lastDay.ToString("yyyy-MM-dd HH:mm:ss"), onedes["Name"].ToString()); DataTable dataDt_pg = DbHelper.DbConn.ExecuteDataset(sql_pj.ToString()).Tables[0]; int total = 0; int finish = 0; if (dataDt_pg.Rows[0]["total"].ToString() != "") { total = Convert.ToInt32(dataDt_pg.Rows[0]["total"]); finish = Convert.ToInt32(dataDt_pg.Rows[0]["finish"]); } decimal pj_dg = 0; if (total > 0) { pj_dg = Convert.ToDecimal(finish) / total; } int intpjdg = (int)(pj_dg * 10000); decimal needPjDg = (decimal)intpjdg / 100; dr[8] = needPjDg.ToString() + "%"; StringBuilder sql_pjw = new StringBuilder(); sql_pjw.AppendFormat("select sum(AllOrder) as total,sum(DesignFinish) as finish from view_ErpDesignerFinishRate where datediff(day,'{0}',CTime)>=0 and datediff(day,'{1}',CTime)<=0 and Name='{2}'", firstweek.ToString("yyyy-MM-dd HH:mm:ss"), lastweek.ToString("yyyy-MM-dd HH:mm:ss"), onedes["Name"].ToString()); DataTable dataDt_pgw = DbHelper.DbConn.ExecuteDataset(sql_pjw.ToString()).Tables[0]; int totalw = 0; int finishw = 0; if (dataDt_pgw.Rows[0]["total"].ToString() != "") { totalw = Convert.ToInt32(dataDt_pgw.Rows[0]["total"]); finishw = Convert.ToInt32(dataDt_pgw.Rows[0]["finish"]); } decimal pj_dgw = 0; if (totalw > 0) { pj_dgw = Convert.ToDecimal(finishw) / totalw; } int intpjdgw = (int)(pj_dgw * 10000); decimal needPjDgw = (decimal)intpjdgw / 100; dr[10] = needPjDgw.ToString() + "%"; dt.Rows.Add(dr); dr_he[1] = Convert.ToDecimal(dr_he[1]) + Convert.ToDecimal(onedes["Amount"]); dr_he[2] = Convert.ToDecimal(dr_he[2]) + Convert.ToDecimal(onedes["AllOrder"]); dr_he[3] = Convert.ToDecimal(dr_he[3]) + Convert.ToDecimal(onedes["WaitDesign"]); dr_he[4] = Convert.ToDecimal(dr_he[4]) + Convert.ToDecimal(onedes["Designing"]); dr_he[5] = Convert.ToDecimal(dr_he[5]) + Convert.ToDecimal(onedes["DesignFinish"]); dr_he[6] = Convert.ToDecimal(dr_he[6]) + Convert.ToDecimal(brate); dr_he[7] = Convert.ToDecimal(dr_he[7]) + Convert.ToDecimal(payPrice); dr_he[8] = Convert.ToDecimal(dr_he[8]) + Convert.ToDecimal(needPjDg); dr_he[10] = Convert.ToDecimal(dr_he[10]) + Convert.ToDecimal(needPjDgw); if (needPjDg > 0) { ratenotZero++; } } decimal ddd = Convert.ToDecimal(dr_he[5]) / Convert.ToDecimal(dr_he[2]) * 10000; int ddd22 = (int)ddd; decimal dddd3 = (decimal)ddd22 / 100; dr_he[6] = dddd3.ToString() + "%"; decimal rrr = Convert.ToDecimal(dr_he[8]) / ratenotZero * 100; int rrr2 = (int)rrr; decimal rrr3 = (decimal)rrr2 / 100; dr_he[8] = Convert.ToString(rrr3) + "%"; decimal rrrw = Convert.ToDecimal(dr_he[10]) / ratenotZero * 100; int rrr2w = (int)rrrw; decimal rrr3w = (decimal)rrr2w / 100; dr_he[10] = Convert.ToString(rrr3w) + "%"; dt.Rows.Add(dr_he); DataView dv = new DataView(dt); //dv.Sort = "AllOrder desc"; DataTable dtNew = dv.ToTable(); writeGridDataTableJson(dtNew.Rows.Count, dtNew); } else { if (date1.Length <= 0) date1 = DateTime.Now.Date.ToString(); if (date2.Length <= 0) date2 = DateTime.Now.Date.ToString(); SqlParameter[] sqlParameter ={ new SqlParameter("@orgid", SqlDbType.Int,4), new SqlParameter("@sDisDate", SqlDbType.DateTime, 20), new SqlParameter("@eDisDate", SqlDbType.DateTime, 20), new SqlParameter("@isSample", SqlDbType.Int, 4), new SqlParameter("@username", SqlDbType.NVarChar, 100), new SqlParameter("@res", SqlDbType.VarChar, 4000) }; sqlParameter[0].Value = org; sqlParameter[1].Value = date1; sqlParameter[2].Value = date2; sqlParameter[3].Value = sample; sqlParameter[4].Value = username; sqlParameter[5].Direction = ParameterDirection.Output; //CeErpTradeCell.ExecuteNonQueryStore("sp_getdesigngather", sqlParameter); DbHelper.DbConn.ExecuteNonQuery(CommandType.StoredProcedure, "sp_getdesigngather_norefund", sqlParameter); string res = sqlParameter[5].Value.ToString(); DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("DesignerName", typeof(string))); dt.Columns.Add(new DataColumn("Amount", typeof(string))); dt.Columns.Add(new DataColumn("AllOrder", typeof(string))); dt.Columns.Add(new DataColumn("WaitDesign", typeof(string))); dt.Columns.Add(new DataColumn("Designing", typeof(string))); dt.Columns.Add(new DataColumn("DesignFinish", typeof(string))); dt.Columns.Add(new DataColumn("FinalizationRate", typeof(string))); dt.Columns.Add(new DataColumn("PayPrice", typeof(string))); dt.Columns.Add(new DataColumn("AvgRate", typeof(string))); dt.Columns.Add(new DataColumn("Ctime", typeof(string))); dt.Columns.Add(new DataColumn("AvgWeekRate", typeof(string))); DataRow dr_he = dt.NewRow(); dr_he[0] = "合计"; dr_he[1] = 0; dr_he[2] = 0; dr_he[3] = 0; dr_he[4] = 0; dr_he[5] = 0; dr_he[6] = 0; dr_he[7] = 0; dr_he[8] = 0; dr_he[9] = null; dr_he[10] = 0; string[] desList = res.Split('|'); int ratenotZero = 0; foreach (string onedes in desList) { if (onedes.Length > 0) { string[] vlList = onedes.Split(','); DataRow dr = dt.NewRow(); dr[0] = vlList[0]; dr[1] = vlList[1]; dr[2] = vlList[2]; dr[3] = vlList[3]; dr[4] = vlList[4]; dr[5] = vlList[5]; dr[9] = DateTime.Now.ToString(); decimal rate = 0; if (vlList[2].ToString() != "0") { rate = (decimal)(Convert.ToDecimal(vlList[5]) / Convert.ToDecimal(vlList[2])); } decimal xxx = rate * 10000; int xxx2 = (int)xxx; decimal xxx3 = (decimal)xxx2 / 100; string brate = Convert.ToString(xxx3); //dr[6] = String.Format("{0:F}", rate); dr[6] = brate + "%"; int payPrice = 0; if (rate >= (decimal)0.80 && Convert.ToInt32(vlList[2]) >= 45) { payPrice = 30; } else if (rate >= (decimal)0.70 && Convert.ToInt32(vlList[2]) >= 35) { payPrice = 20; } else if (rate >= (decimal)0.65 && Convert.ToInt32(vlList[2]) >= 25) { payPrice = 10; } dr[7] = payPrice; StringBuilder sql_pj = new StringBuilder(); sql_pj.AppendFormat("select sum(AllOrder) as total,sum(DesignFinish) as finish from view_ErpDesignerFinishRate where datediff(day,'{0}',CTime)>=0 and datediff(day,'{1}',CTime)<=0 and Name='{2}'", firstDay.ToString("yyyy-MM-dd HH:mm:ss"), lastDay.ToString("yyyy-MM-dd HH:mm:ss"), vlList[0].ToString()); DataTable dataDt_pg = DbHelper.DbConn.ExecuteDataset(sql_pj.ToString()).Tables[0]; int total = 0; int finish = 0; if (dataDt_pg.Rows[0]["total"].ToString() != "") { total = Convert.ToInt32(dataDt_pg.Rows[0]["total"]); finish = Convert.ToInt32(dataDt_pg.Rows[0]["finish"]); } decimal pj_dg = 0; if (total > 0) { pj_dg = Convert.ToDecimal(finish) / total; } int intpjdg = (int)(pj_dg * 10000); decimal needPjDg = (decimal)intpjdg / 100; dr[8] = needPjDg.ToString() + "%"; StringBuilder sql_pjw = new StringBuilder(); sql_pjw.AppendFormat("select sum(AllOrder) as total,sum(DesignFinish) as finish from view_ErpDesignerFinishRate where datediff(day,'{0}',CTime)>=0 and datediff(day,'{1}',CTime)<=0 and Name='{2}'", firstweek.ToString("yyyy-MM-dd HH:mm:ss"), lastweek.ToString("yyyy-MM-dd HH:mm:ss"), vlList[0].ToString()); DataTable dataDt_pgw = DbHelper.DbConn.ExecuteDataset(sql_pjw.ToString()).Tables[0]; int totalw = 0; int finishw = 0; if (dataDt_pgw.Rows[0]["total"].ToString() != "") { totalw = Convert.ToInt32(dataDt_pgw.Rows[0]["total"]); finishw = Convert.ToInt32(dataDt_pgw.Rows[0]["finish"]); } decimal pj_dgw = 0; if (totalw > 0) { pj_dgw = Convert.ToDecimal(finishw) / totalw; } int intpjdgw = (int)(pj_dgw * 10000); decimal needPjDgw = (decimal)intpjdgw / 100; dr[10] = needPjDgw.ToString() + "%"; dt.Rows.Add(dr); dr_he[1] = Convert.ToDecimal(dr_he[1]) + Convert.ToDecimal(vlList[1]); dr_he[2] = Convert.ToDecimal(dr_he[2]) + Convert.ToDecimal(vlList[2]); dr_he[3] = Convert.ToDecimal(dr_he[3]) + Convert.ToDecimal(vlList[3]); dr_he[4] = Convert.ToDecimal(dr_he[4]) + Convert.ToDecimal(vlList[4]); dr_he[5] = Convert.ToDecimal(dr_he[5]) + Convert.ToDecimal(vlList[5]); dr_he[6] = Convert.ToDecimal(dr_he[6]) + Convert.ToDecimal(brate); dr_he[7] = Convert.ToDecimal(dr_he[7]) + Convert.ToDecimal(payPrice); dr_he[8] = Convert.ToDecimal(dr_he[8]) + Convert.ToDecimal(needPjDg); dr_he[10] = Convert.ToDecimal(dr_he[10]) + Convert.ToDecimal(needPjDgw); if (needPjDg > 0) { ratenotZero++; } } } decimal ddd = Convert.ToDecimal(dr_he[2]) == 0 ? Convert.ToDecimal(dr_he[5]) / 10000 : Convert.ToDecimal(dr_he[5]) / Convert.ToDecimal(dr_he[2]) * 10000; int ddd22 = (int)ddd; decimal dddd3 = (decimal)ddd22 / 100; dr_he[6] = dddd3.ToString() + "%"; try { if (ratenotZero == 0) { dr_he[8] = "0%"; dr_he[10] = "0%"; } else { decimal rrr = Convert.ToDecimal(dr_he[8]) / ratenotZero * 100; int rrr2 = (int)rrr; decimal rrr3 = (decimal)rrr2 / 100; dr_he[8] = Convert.ToString(rrr3) + "%"; //周平均定稿率合计平均 decimal rrrw = Convert.ToDecimal(dr_he[10]) / ratenotZero * 100; int rrr2w = (int)rrrw; decimal rrr3w = (decimal)rrr2w / 100; dr_he[10] = Convert.ToString(rrr3w) + "%"; } } catch (Exception ex) { } dt.Rows.Add(dr_he); DataView dv = new DataView(dt); //dv.Sort = "AllOrder desc"; DataTable dtNew = dv.ToTable(); writeGridDataTableJson(dtNew.Rows.Count, dtNew); } } public void get_erp_designer_gather_refund() { int myOrg = CurrentUser.UserPost.OrgID; int org = GetPostInt("org"); if (myOrg != 1 && myOrg != 29 && myOrg != 10 && myOrg != 6) //1是总经办，管理员 组织部 技术部 财务部 { org = myOrg; } string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); int sample = -1; string sstr = GetPostString("order_area"); if (sstr.Length > 0) { sample = Convert.ToInt32(sstr); } if (date1.Length <= 0) date1 = "1990-01-01"; if (date2.Length <= 0) date2 = "2058-01-01"; string username = GetPostString("designer"); SqlParameter[] sqlParameter ={ new SqlParameter("@orgid", SqlDbType.Int,4), new SqlParameter("@sDisDate", SqlDbType.DateTime, 20), new SqlParameter("@eDisDate", SqlDbType.DateTime, 20), new SqlParameter("@isSample", SqlDbType.Int, 4), new SqlParameter("@username", SqlDbType.NVarChar, 100), new SqlParameter("@res", SqlDbType.VarChar, 4000) }; sqlParameter[0].Value = org; sqlParameter[1].Value = date1; sqlParameter[2].Value = date2; sqlParameter[3].Value = sample; sqlParameter[4].Value = username; sqlParameter[5].Direction = ParameterDirection.Output; //CeErpTradeCell.ExecuteNonQueryStore("sp_getdesigngather", sqlParameter); DbHelper.DbConn.ExecuteNonQuery(CommandType.StoredProcedure, "sp_getdesigngather_refund", sqlParameter); string res = sqlParameter[5].Value.ToString(); DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("DesignerName", typeof(string))); dt.Columns.Add(new DataColumn("Amount", typeof(string))); dt.Columns.Add(new DataColumn("AllOrder", typeof(string))); dt.Columns.Add(new DataColumn("RefundCount", typeof(string))); dt.Columns.Add(new DataColumn("FinalizationRate", typeof(string))); string[] desList = res.Split('|'); foreach (string onedes in desList) { if (onedes.Length > 0) { string[] vlList = onedes.Split(','); DataRow dr = dt.NewRow(); dr[0] = vlList[0]; dr[1] = vlList[1]; dr[2] = vlList[2]; dr[3] = vlList[3]; decimal rate = 0; if (vlList[2].ToString() != "0") { rate = Convert.ToDecimal(vlList[3]) / Convert.ToDecimal(vlList[2]); } rate = Convert.ToInt32(rate * 100); rate = rate / 100; dr[4] = rate; dt.Rows.Add(dr); } } DataView dv = new DataView(dt); dv.Sort = "AllOrder desc"; writeGridDataViewJson(dt.Rows.Count, dv); } public void get_erp_place_gather() { string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); if (date1.Length <= 0) { date1 = DateTime.Now.ToString(); date2 = DateTime.Now.ToString(); } //DateTime firstDay = commonHelper.FirstDayOfMonth(Convert.ToDateTime(date1)); //DateTime lastDay = commonHelper.LastDayOfMonth(Convert.ToDateTime(date1)); //if (date1.Length > 0 && DateTime.Now.ToShortDateString() != Convert.ToDateTime(date1).ToShortDateString()) //{ // string orgStr = ""; // if (org.Length > 0) // { // orgStr = " and OrgId=" + org; // } // StringBuilder sql = new StringBuilder(); // sql.AppendFormat("select * from view_ErpDesignerFinishRate where datediff(day,'{0}',CTime)>=0 and datediff(day,'{1}',CTime)<=0" + orgStr, date1, date2); // DataTable dataDt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; // DataTable dt = new DataTable(); // dt.Columns.Add(new DataColumn("DesignerName", typeof(string))); // dt.Columns.Add(new DataColumn("Amount", typeof(string))); // dt.Columns.Add(new DataColumn("AllOrder", typeof(string))); // dt.Columns.Add(new DataColumn("WaitDesign", typeof(string))); // dt.Columns.Add(new DataColumn("Designing", typeof(string))); // dt.Columns.Add(new DataColumn("DesignFinish", typeof(string))); // dt.Columns.Add(new DataColumn("FinalizationRate", typeof(string))); // dt.Columns.Add(new DataColumn("PayPrice", typeof(string))); // dt.Columns.Add(new DataColumn("AvgRate", typeof(string))); // dt.Columns.Add(new DataColumn("Ctime", typeof(string))); // DataRow dr_he = dt.NewRow(); // dr_he[0] = "合计"; // dr_he[1] = 0; // dr_he[2] = 0; // dr_he[3] = 0; // dr_he[4] = 0; // dr_he[5] = 0; // dr_he[6] = 0; // dr_he[7] = 0; // dr_he[8] = 0; // dr_he[9] = null; // foreach (DataRow onedes in dataDt.Rows) // { // DataRow dr = dt.NewRow(); // dr[0] = onedes["Name"].ToString(); // dr[1] = onedes["Amount"].ToString(); // dr[2] = onedes["AllOrder"].ToString(); // dr[3] = onedes["WaitDesign"].ToString(); // dr[4] = onedes["Designing"].ToString(); // dr[5] = onedes["DesignFinish"].ToString(); // dr[9] = onedes["CTime"].ToString(); // decimal rate = 0; // if (onedes["AllOrder"].ToString() != "0") // { // rate = (decimal)(Convert.ToDecimal(onedes["DesignFinish"]) / Convert.ToDecimal(onedes["AllOrder"])); // } // dr_he[1] = Convert.ToDecimal(dr_he[1]) + Convert.ToDecimal(onedes["Amount"]); // dr_he[2] = Convert.ToDecimal(dr_he[2]) + Convert.ToDecimal(onedes["AllOrder"]); // dr_he[3] = Convert.ToDecimal(dr_he[3]) + Convert.ToDecimal(onedes["WaitDesign"]); // dr_he[4] = Convert.ToDecimal(dr_he[4]) + Convert.ToDecimal(onedes["Designing"]); // dr_he[5] = Convert.ToDecimal(dr_he[5]) + Convert.ToDecimal(onedes["DesignFinish"]); // dr_he[6] = Convert.ToDecimal(dr_he[6]) + Convert.ToDecimal(brate); // dr_he[7] = Convert.ToDecimal(dr_he[7]) + Convert.ToDecimal(payPrice); // dr_he[8] = Convert.ToDecimal(dr_he[8]) + Convert.ToDecimal(needPjDg); // } // dt.Rows.Add(dr_he); // DataView dv = new DataView(dt); // //dv.Sort = "AllOrder desc"; // DataTable dtNew = dv.ToTable(); // writeGridDataTableJson(dtNew.Rows.Count, dtNew); //} //else //{ if (date1.Length <= 0) date1 = DateTime.Now.Date.ToString(); if (date2.Length <= 0) date2 = DateTime.Now.Date.ToString(); SqlParameter[] sqlParameter ={ new SqlParameter("@sDisDate", SqlDbType.DateTime, 20), new SqlParameter("@eDisDate", SqlDbType.DateTime, 20), new SqlParameter("@res", SqlDbType.VarChar, 4000) }; sqlParameter[0].Value = date1; sqlParameter[1].Value = date2; sqlParameter[2].Direction = ParameterDirection.Output; //CeErpTradeCell.ExecuteNonQueryStore("sp_getdesigngather", sqlParameter); DbHelper.DbConn.ExecuteNonQuery(CommandType.StoredProcedure, "sp_getplacegather", sqlParameter); string res = sqlParameter[2].Value.ToString(); DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("DesignerName", typeof(string))); dt.Columns.Add(new DataColumn("AllOrder", typeof(string))); DataRow dr_he = dt.NewRow(); dr_he[0] = "合计"; dr_he[1] = 0; string[] desList = res.Split('|'); foreach (string onedes in desList) { if (onedes.Length > 0) { string[] vlList = onedes.Split(','); DataRow dr = dt.NewRow(); dr[0] = vlList[0]; dr[1] = vlList[1]; dt.Rows.Add(dr); dr_he[1] = Convert.ToDecimal(dr_he[1]) + Convert.ToDecimal(vlList[1]); } } dt.Rows.Add(dr_he); DataView dv = new DataView(dt); //dv.Sort = "AllOrder desc"; DataTable dtNew = dv.ToTable(); writeGridDataTableJson(dtNew.Rows.Count, dtNew); //} } public void get_erp_aftersale_gather() { string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); string pay_date1 = GetPostString("pay_date1"); string pay_date2 = GetPostString("pay_date2"); if (date1.Length <= 0 && pay_date1.Length <= 0) { date1 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).ToString("yyyy-MM-dd HH:mm:ss"); date2 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59).ToString("yyyy-MM-dd HH:mm:ss"); } StringBuilder sql = new StringBuilder(); sql.AppendFormat("SELECT o.AfterSaleUserId AS UserId, u.name AS UserName, COUNT(*) AS Total, COUNT(CASE WHEN o.AfterSaleState IN (2, 3) THEN 1 END) AS InProgress, COUNT(CASE WHEN o.AfterSaleState >= 4 THEN 1 END) AS Completed,COUNT ( CASE WHEN o.AfterSaleBackReason != '' THEN 1 END ) AS back, SUM ( a.ChangeNum ) AS ChangeNum ,SUM ( a.ReprintOut ) AS ReprintOut,SUM ( a.PreTimeOut ) AS PreTimeOut,SUM ( a.DisagreeTimeOut ) AS DisagreeTime FROM CE_ErpTradeCell o LEFT JOIN Ce_ErpTradeAfterSaleExtend a ON a.tid = o.ctid INNER JOIN CE_ErpUser u ON o.AfterSaleUserId = u.ID WHERE o.AfterSaleState >= 1 "); if (date1.Length > 0) { sql.AppendFormat(" AND o.HandleTime >= '{0}' AND o.HandleTime < '{1}'", date1, date2); } if (pay_date1.Length > 0) { sql.AppendFormat(" AND o.pay_time >= '{0}' AND o.pay_time < '{1}'", pay_date1, pay_date2); } sql.AppendFormat(" GROUP BY o.AfterSaleUserId, u.name"); DataTable dtafter = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("DesignerName", typeof(string))); dt.Columns.Add(new DataColumn("AllOrder", typeof(string))); dt.Columns.Add(new DataColumn("Handling", typeof(string))); dt.Columns.Add(new DataColumn("Finish", typeof(string))); dt.Columns.Add(new DataColumn("FinishRate", typeof(string))); dt.Columns.Add(new DataColumn("BackRate", typeof(string))); dt.Columns.Add(new DataColumn("ChangeNum", typeof(string))); dt.Columns.Add(new DataColumn("DisagreeTime", typeof(string))); dt.Columns.Add(new DataColumn("ReprintOut", typeof(string))); dt.Columns.Add(new DataColumn("PreTimeOut", typeof(string))); DataRow dr_he = dt.NewRow(); dr_he[0] = "合计"; dr_he[1] = 0; dr_he[2] = 0; dr_he[3] = 0; dr_he[4] = 0; dr_he[5] = 0; dr_he[6] = 0; dr_he[7] = 0; dr_he[8] = 0; dr_he[9] = 0; int total = 0; foreach (DataRow row in dtafter.Rows) { DataRow dr = dt.NewRow(); dr[0] = row["UserName"]; dr[1] = row["Total"]; dr[2] = row["InProgress"]; dr[3] = row["Completed"]; dr[6] = row["ChangeNum"]; dr[7] = row["DisagreeTime"]; dr[8] = row["ReprintOut"]; dr[9] = row["PreTimeOut"]; decimal rate = 0; if (dr[1].ToString() != "0") { rate = Convert.ToDecimal(dr[3]) / Convert.ToDecimal(dr[1]); } rate = Convert.ToInt32(rate * 100); dr[4] = rate; decimal backrate = 0; if (dr[1].ToString() != "0") { backrate = Convert.ToDecimal(row["back"]) / Convert.ToDecimal(dr[1]); } backrate = Convert.ToInt32(backrate * 100); dr[5] = backrate; dt.Rows.Add(dr); dr_he[1] = Convert.ToDecimal(dr_he[1]) + Convert.ToDecimal(dr[1]); dr_he[2] = Convert.ToDecimal(dr_he[2]) + Convert.ToDecimal(dr[2]); dr_he[3] = Convert.ToDecimal(dr_he[3]) + Convert.ToDecimal(dr[3]); dr_he[6] = Convert.ToDecimal(dr_he[6]) + Convert.ToDecimal(dr[6]); dr_he[7] = Convert.ToDecimal(dr_he[7]) + Convert.ToDecimal(dr[7]); dr_he[8] = Convert.ToDecimal(dr_he[8]) + Convert.ToDecimal(dr[8]); dr_he[9] = Convert.ToDecimal(dr_he[9]) + Convert.ToDecimal(dr[9]); total += Convert.ToInt32(row["back"]); } decimal pgrate = 0; decimal pbackgrate = 0; if (dr_he[1].ToString() != "0") { pbackgrate = Convert.ToDecimal(total) / Convert.ToDecimal(dr_he[1]); pgrate = Convert.ToDecimal(dr_he[3]) / Convert.ToDecimal(dr_he[1]); } dr_he[4] = Convert.ToInt32(pgrate * 100); dr_he[5] = Convert.ToInt32(pbackgrate * 100); dt.Rows.Add(dr_he); DataView dv = new DataView(dt); //dv.Sort = "AllOrder desc"; DataTable dtNew = dv.ToTable(); writeGridDataTableJson(dtNew.Rows.Count, dtNew); } public void get_erp_aftersale_duty_gather() { string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); string pay_date1 = GetPostString("pay_date1"); string pay_date2 = GetPostString("pay_date2"); if (date1.Length <= 0 && pay_date1.Length <= 0) { date1 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).ToString("yyyy-MM-dd HH:mm:ss"); date2 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59).ToString("yyyy-MM-dd HH:mm:ss"); } StringBuilder sqlDate = new StringBuilder(); StringBuilder sqlDate1 = new StringBuilder(); if (date1.Length > 0) { sqlDate.AppendFormat(" AND o.HandleTime >= '{0}' AND o.HandleTime < '{1}'", date1, date2); sqlDate1.AppendFormat(" AND HandleTime >= '{0}' AND HandleTime < '{1}'", date1, date2); } if (pay_date1.Length > 0) { sqlDate.AppendFormat(" AND o.pay_time >= '{0}' AND o.pay_time < '{1}'", pay_date1, pay_date2); sqlDate1.AppendFormat(" AND pay_time >= '{0}' AND pay_time < '{1}'", pay_date1, pay_date2); } StringBuilder sql = new StringBuilder(); sql.AppendFormat("WITH AllOrders AS ( SELECT DesignUserId AS UserId, COUNT ( * ) AS OrderCount FROM CE_ErpTradeCell WHERE OrderState > 6 {0} GROUP BY DesignUserId UNION ALL SELECT CustomerUserId AS UserId, COUNT ( * ) AS OrderCount FROM CE_ErpTradeCell WHERE OrderState > 6 {0} GROUP BY CustomerUserId ), CombinedOrders AS ( SELECT UserId, SUM ( OrderCount ) AS allOrder FROM AllOrders GROUP BY UserId ) SELECT r.UserId, u.name AS dutyName, COUNT ( * ) AS afterSaleOrder, SUM ( o.AfterSalePayment ) AS money, COALESCE ( c.allOrder, 0 ) AS allOrder FROM CE_ErpTradeResponsible r INNER JOIN CE_ErpUser u ON r.UserId = u.ID INNER JOIN CE_ErpTradeCell o ON o.ctid = r.tid LEFT JOIN CombinedOrders c ON r.UserId = c.UserId WHERE r.VerifyState > - 1 AND r.type = 0 AND o.AfterSaleResSupId = 0 {1} GROUP BY r.UserId, c.allOrder,u.name", sqlDate1, sqlDate); DataTable dtafter = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("dutyName", typeof(string))); dt.Columns.Add(new DataColumn("afterSaleOrder", typeof(string))); dt.Columns.Add(new DataColumn("money", typeof(string))); dt.Columns.Add(new DataColumn("allOrder", typeof(string))); dt.Columns.Add(new DataColumn("rate", typeof(string))); DataRow dr_he = dt.NewRow(); dr_he[0] = "合计"; dr_he[1] = 0; dr_he[2] = 0; dr_he[3] = 0; dr_he[4] = 0; foreach (DataRow row in dtafter.Rows) { DataRow dr = dt.NewRow(); dr[0] = row["dutyName"]; dr[1] = row["afterSaleOrder"]; dr[2] = row["money"]; dr[3] = row["allOrder"]; decimal rate = 0; if (dr[3].ToString() != "0") { rate = Convert.ToDecimal(dr[1]) / Convert.ToDecimal(dr[3]); } rate = Convert.ToInt32(rate * 100); dr[4] = rate; dt.Rows.Add(dr); dr_he[1] = Convert.ToDecimal(dr_he[1]) + Convert.ToDecimal(dr[1]); dr_he[2] = Convert.ToDecimal(dr_he[2]) + Convert.ToDecimal(dr[2]); dr_he[3] = Convert.ToDecimal(dr_he[3]) + Convert.ToDecimal(dr[3]); } decimal pgrate = 0; if (dr_he[3].ToString() != "0") { pgrate = Convert.ToDecimal(dr_he[1]) / Convert.ToDecimal(dr_he[3]); } pgrate = Convert.ToInt32(pgrate * 100); dr_he[4] = pgrate; dt.Rows.Add(dr_he); DataView dv = new DataView(dt); //dv.Sort = "AllOrder desc"; DataTable dtNew = dv.ToTable(); writeGridDataTableJson(dtNew.Rows.Count, dtNew); } public void get_erp_aftersale_dutys_gather() { string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); string pay_date1 = GetPostString("pay_date1"); string pay_date2 = GetPostString("pay_date2"); if (date1.Length <= 0 && pay_date1.Length <= 0) { date1 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).ToString("yyyy-MM-dd HH:mm:ss"); date2 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59).ToString("yyyy-MM-dd HH:mm:ss"); } StringBuilder sqlDate = new StringBuilder(); StringBuilder sqlDate1 = new StringBuilder(); if (date1.Length > 0) { sqlDate.AppendFormat(" AND o.HandleTime >= '{0}' AND o.HandleTime < '{1}'", date1, date2); sqlDate1.AppendFormat(" AND HandleTime >= '{0}' AND HandleTime < '{1}'", date1, date2); } if (pay_date1.Length > 0) { sqlDate.AppendFormat(" AND o.pay_time >= '{0}' AND o.pay_time < '{1}'", pay_date1, pay_date2); sqlDate1.AppendFormat(" AND pay_time >= '{0}' AND pay_time < '{1}'", pay_date1, pay_date2); } StringBuilder sql = new StringBuilder(); sql.AppendFormat("WITH AllOrders AS ( SELECT DesignUserId AS UserId, COUNT ( * ) AS OrderCount FROM CE_ErpTradeCell WHERE OrderState > 6 {0} GROUP BY DesignUserId UNION ALL SELECT CustomerUserId AS UserId, COUNT ( * ) AS OrderCount FROM CE_ErpTradeCell WHERE OrderState > 6 {0} GROUP BY CustomerUserId ), CombinedOrders AS ( SELECT UserId, SUM ( OrderCount ) AS allOrder FROM AllOrders GROUP BY UserId ) SELECT u.OrgID, u.OrgName as dutysName, COUNT ( * ) AS afterSaleOrder, SUM ( o.AfterSalePayment ) AS money,COALESCE ( SUM ( c.allOrder ), 0 ) AS allOrder FROM CE_ErpTradeResponsible r INNER JOIN view_ErpUser u ON r.UserId = u.ID INNER JOIN CE_ErpTradeCell o ON o.ctid = r.tid LEFT JOIN CombinedOrders c ON r.UserId = c.UserId WHERE r.VerifyState > - 1 AND r.type = 0 AND o.AfterSaleResSupId = 0 {1} GROUP BY u.OrgID,u.OrgName", sqlDate1, sqlDate); DataTable dtafter = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("dutysName", typeof(string))); dt.Columns.Add(new DataColumn("afterSaleOrder", typeof(string))); dt.Columns.Add(new DataColumn("money", typeof(string))); dt.Columns.Add(new DataColumn("allOrder", typeof(string))); dt.Columns.Add(new DataColumn("rate", typeof(string))); DataRow dr_he = dt.NewRow(); dr_he[0] = "合计"; dr_he[1] = 0; dr_he[2] = 0; dr_he[3] = 0; dr_he[4] = 0; foreach (DataRow row in dtafter.Rows) { DataRow dr = dt.NewRow(); dr[0] = row["dutysName"]; dr[1] = row["afterSaleOrder"]; dr[2] = row["money"]; dr[3] = row["allOrder"]; decimal rate = 0; if (dr[3].ToString() != "0") { rate = Convert.ToDecimal(dr[1]) / Convert.ToDecimal(dr[3]); } rate = Convert.ToInt32(rate * 100); dr[4] = rate; dt.Rows.Add(dr); dr_he[1] = Convert.ToDecimal(dr_he[1]) + Convert.ToDecimal(dr[1]); dr_he[2] = Convert.ToDecimal(dr_he[2]) + Convert.ToDecimal(dr[2]); dr_he[3] = Convert.ToDecimal(dr_he[3]) + Convert.ToDecimal(dr[3]); } decimal pgrate = 0; if (dr_he[3].ToString() != "0") { pgrate = Convert.ToDecimal(dr_he[1]) / Convert.ToDecimal(dr_he[3]); } pgrate = Convert.ToInt32(pgrate * 100); dr_he[4] = pgrate; dt.Rows.Add(dr_he); DataView dv = new DataView(dt); //dv.Sort = "AllOrder desc"; DataTable dtNew = dv.ToTable(); writeGridDataTableJson(dtNew.Rows.Count, dtNew); } public void get_erp_aftersale_dutyce_gather() { string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); string pay_date1 = GetPostString("pay_date1"); string pay_date2 = GetPostString("pay_date2"); if (date1.Length <= 0 && pay_date1.Length <= 0) { pay_date1 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).ToString("yyyy-MM-dd HH:mm:ss"); pay_date2 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59).ToString("yyyy-MM-dd HH:mm:ss"); } StringBuilder sql = new StringBuilder(); StringBuilder sqlDate = new StringBuilder(); if (date1.Length > 0) { sqlDate.AppendFormat("and o.HandleTime >= '{0}' AND o.HandleTime < '{1}'", date1, date2); } if (pay_date1.Length > 0) { sqlDate.AppendFormat(" and o.pay_time >= '{0}' AND o.pay_time < '{1}'", pay_date1, pay_date2); } sql.AppendFormat("SELECT AfterSaleReason, COUNT ( * ) AS total FROM ( SELECT SUBSTRING( o.AfterSaleReason, 1, CHARINDEX( '|', o.AfterSaleReason ) - 1 ) AS AfterSaleReason FROM CE_ErpTradeCell o INNER JOIN CE_ErpUser u ON o.AfterSaleUserId = u.ID WHERE o.AfterSaleState >= 1 AND o.AfterSaleReason != '' {0} ) AS a GROUP BY AfterSaleReason", sqlDate); DataTable dtafter = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("AfterSaleReason", typeof(string))); dt.Columns.Add(new DataColumn("total", typeof(string))); dt.Columns.Add(new DataColumn("all", typeof(string))); dt.Columns.Add(new DataColumn("rate", typeof(string))); decimal total = 0; foreach (DataRow row in dtafter.Rows) { DataRow dr = dt.NewRow(); dr[0] = row["AfterSaleReason"]; dr[1] = row["total"]; dr[2] = 0; dr[3] = 0; dt.Rows.Add(dr); total += Convert.ToDecimal(dr[1]); } foreach (DataRow row in dt.Rows) { row[2] = total; row[3] = Math.Round(Math.Round(Convert.ToDecimal(row[1]) / total, 4) * 100, 2); } DataView dv = new DataView(dt); //dv.Sort = "AllOrder desc"; DataTable dtNew = dv.ToTable(); writeGridDataTableJson(dtNew.Rows.Count, dtNew); } public void get_erp_aftersale_reason_gather() { string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); string pay_date1 = GetPostString("pay_date1"); string pay_date2 = GetPostString("pay_date2"); if (date1.Length <= 0 && pay_date1.Length <= 0) { pay_date1 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).ToString("yyyy-MM-dd HH:mm:ss"); pay_date2 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59).ToString("yyyy-MM-dd HH:mm:ss"); } StringBuilder sql = new StringBuilder(); StringBuilder sqlDate = new StringBuilder(); if (date1.Length > 0) { sqlDate.AppendFormat("and o.HandleTime >= '{0}' AND o.HandleTime < '{1}'", date1, date2); } if (pay_date1.Length > 0) { sqlDate.AppendFormat("and o.pay_time >= '{0}' AND o.pay_time < '{1}'", pay_date1, pay_date2); } sql.AppendFormat("SELECT AfterSaleReason, COUNT ( * ) AS total FROM CE_ErpTradeCell o WHERE o.AfterSaleState >= 1 {0} GROUP BY AfterSaleReason", sqlDate); DataTable dtafter = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("AfterSaleReason1", typeof(string))); dt.Columns.Add(new DataColumn("AfterSaleReason2", typeof(string))); dt.Columns.Add(new DataColumn("AfterSaleReason3", typeof(string))); dt.Columns.Add(new DataColumn("total", typeof(string))); dt.Columns.Add(new DataColumn("rate", typeof(string))); DataRow dr_he = dt.NewRow(); dr_he[0] = "合计"; dr_he[1] = 0; dr_he[2] = 0; dr_he[3] = 0; dr_he[4] = 0; foreach (DataRow row in dtafter.Rows) { string reason = row["AfterSaleReason"].ToString(); string[] reason_list = reason.Split(new char[] { '|' }); DataRow dr = dt.NewRow(); if (reason_list.Length > 0) { dr[0] = reason_list[0]; } if (reason_list.Length > 1) { dr[1] = reason_list[1]; } if (reason_list.Length > 2) { dr[2] = reason_list[2]; } dr[3] = row["total"]; dr[4] = 0; dt.Rows.Add(dr); dr_he[3] = Convert.ToDecimal(dr_he[3]) + Convert.ToDecimal(dr[3]); } foreach (DataRow row in dt.Rows) { row[4] = Math.Round(Math.Round(Convert.ToDecimal(row[3]) / Convert.ToDecimal(dr_he[3]), 4) * 100, 2); } dr_he[4] = 100; DataView dv = new DataView(dt); //dv.Sort = "AllOrder desc"; DataTable dtNew = dv.ToTable(); writeGridDataTableJson(dtNew.Rows.Count, dtNew); } public void get_erp_aftersale_back_gather() { string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); string pay_date1 = GetPostString("pay_date1"); string pay_date2 = GetPostString("pay_date2"); if (date1.Length <= 0 && pay_date1.Length <= 0) { pay_date1 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).ToString("yyyy-MM-dd HH:mm:ss"); pay_date2 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59).ToString("yyyy-MM-dd HH:mm:ss"); } StringBuilder sql = new StringBuilder(); StringBuilder sqlDate = new StringBuilder(); if (date1.Length > 0) { sqlDate.AppendFormat("and o.HandleTime >= '{0}' AND o.HandleTime < '{1}'", date1, date2); } if (pay_date1.Length > 0) { sqlDate.AppendFormat("and o.pay_time >= '{0}' AND o.pay_time < '{1}'", pay_date1, pay_date2); } sql.AppendFormat("SELECT o.AfterSaleUserId, u.name AS AftersaleName, SUM ( r.BackNum ) AS backNum, SUM ( r.BackNum + 1 ) AS AllOrder FROM ( SELECT tid, MAX ( BackNum ) AS BackNum FROM CE_ErpTradeResponsible e WHERE e.VerifyState > - 1 GROUP BY tid ) r INNER JOIN CE_ErpTradeCell o ON o.ctid = r.tid INNER JOIN CE_ErpUser u ON u.id = o.AfterSaleUserId WHERE BackNum IS NOT NULL {0} GROUP BY o.AfterSaleUserId, u.name", sqlDate); DataTable dtafter = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("AftersaleName", typeof(string))); dt.Columns.Add(new DataColumn("backNum", typeof(string))); dt.Columns.Add(new DataColumn("AllOrder", typeof(string))); dt.Columns.Add(new DataColumn("backRate", typeof(string))); DataRow dr_he = dt.NewRow(); dr_he[0] = "合计"; dr_he[1] = 0; dr_he[2] = 0; dr_he[3] = 0; foreach (DataRow row in dtafter.Rows) { DataRow dr = dt.NewRow(); dr[0] = row["AftersaleName"]; dr[1] = row["backNum"]; dr[2] = row["AllOrder"]; decimal rate = 0; if (dr[2].ToString() != "0") { rate = Convert.ToDecimal(dr[1]) / Convert.ToDecimal(dr[2]); } rate = Convert.ToInt32(rate * 100); dr[3] = rate; dt.Rows.Add(dr); dr_he[1] = Convert.ToDecimal(dr_he[1]) + Convert.ToDecimal(dr[1]); dr_he[2] = Convert.ToDecimal(dr_he[2]) + Convert.ToDecimal(dr[2]); } decimal pgrate = 0; if (dr_he[2].ToString() != "0") { pgrate = Convert.ToDecimal(dr_he[1]) / Convert.ToDecimal(dr_he[2]); } pgrate = Convert.ToInt32(pgrate * 100); dr_he[3] = pgrate; dt.Rows.Add(dr_he); DataView dv = new DataView(dt); //dv.Sort = "AllOrder desc"; DataTable dtNew = dv.ToTable(); writeGridDataTableJson(dtNew.Rows.Count, dtNew); } public void get_erp_aftersale_shop_gather() { string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); string pay_date1 = GetPostString("pay_date1"); string pay_date2 = GetPostString("pay_date2"); if (date1.Length <= 0 && pay_date1.Length <= 0) { pay_date1 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).ToString("yyyy-MM-dd HH:mm:ss"); pay_date2 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59).ToString("yyyy-MM-dd HH:mm:ss"); } StringBuilder sql = new StringBuilder(); StringBuilder sqlDate = new StringBuilder(); if (date1.Length > 0) { sqlDate.AppendFormat("and o.HandleTime >= '{0}' AND o.HandleTime < '{1}'", date1, date2); } if (pay_date1.Length > 0) { sqlDate.AppendFormat("and o.pay_time >= '{0}' AND o.pay_time < '{1}'", pay_date1, pay_date2); } sql.AppendFormat("SELECT s.ShopName , COUNT ( * ) AS AllOrder, COUNT ( CASE WHEN o.AfterSaleState >= 1 THEN 1 END ) AS aftersaleOrder,COUNT ( CASE WHEN e.CheckOut > 0 THEN 1 END ) AS checkout FROM CE_ErpTradeCell o LEFT JOIN Ce_ErpTradeCellExtend e ON o.ctid = e.ctid LEFT JOIN CE_ErpShop s ON o.ShopId = s.id WHERE 1=1 {0} GROUP BY s.ShopName", sqlDate); DataTable dtafter = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; int totalAfter = 0; DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("shopName", typeof(string))); dt.Columns.Add(new DataColumn("aftersaleOrder", typeof(string))); dt.Columns.Add(new DataColumn("AllOrder", typeof(string))); dt.Columns.Add(new DataColumn("rate", typeof(string))); dt.Columns.Add(new DataColumn("rateProp", typeof(string))); dt.Columns.Add(new DataColumn("checkout", typeof(string))); DataRow dr_he = dt.NewRow(); dr_he[0] = "合计"; dr_he[1] = 0; dr_he[2] = 0; dr_he[3] = 0; dr_he[4] = 100; dr_he[5] = 0; foreach (DataRow row in dtafter.Rows) { DataRow dr = dt.NewRow(); dr[0] = row["shopName"]; dr[1] = row["aftersaleOrder"]; dr[2] = row["AllOrder"]; dr[4] = 0; dr[5] = row["checkout"]; decimal rate = 0; if (dr[1].ToString() != "0") { rate = Math.Round(Convert.ToDecimal(dr[1]) / Convert.ToDecimal(dr[2]), 4); } dr[3] = Math.Round(rate * 100, 2); dt.Rows.Add(dr); dr_he[1] = Convert.ToDecimal(dr_he[1]) + Convert.ToDecimal(dr[1]); dr_he[2] = Convert.ToDecimal(dr_he[2]) + Convert.ToDecimal(dr[2]); totalAfter += Convert.ToInt32(dr[1]); } foreach (DataRow row in dt.Rows) { if (Convert.ToDecimal(row[1]) > 0) { row[4] = Math.Round(Math.Round(Convert.ToDecimal(row[1]) / Convert.ToDecimal(totalAfter), 4) * 100, 2); } } decimal chushu = Convert.ToDecimal(dr_he[2]) == 0 ? 1 : Convert.ToDecimal(dr_he[2]); decimal pgrate = Math.Round(Convert.ToDecimal(dr_he[1]) / Convert.ToDecimal(chushu), 4); dr_he[3] = Math.Round(pgrate * 100, 2); dt.Rows.Add(dr_he); DataView dv = new DataView(dt); //dv.Sort = "AllOrder desc"; DataTable dtNew = dv.ToTable(); writeGridDataTableJson(dtNew.Rows.Count, dtNew); } public void get_erp_aftersale_goods_gather() { string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); string pay_date1 = GetPostString("pay_date1"); string pay_date2 = GetPostString("pay_date2"); if (date1.Length <= 0 && pay_date1.Length <= 0) { pay_date1 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).ToString("yyyy-MM-dd HH:mm:ss"); pay_date2 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59).ToString("yyyy-MM-dd HH:mm:ss"); } StringBuilder sql = new StringBuilder(); StringBuilder sqlDate = new StringBuilder(); if (date1.Length > 0) { sqlDate.AppendFormat("and o.HandleTime >= '{0}' AND o.HandleTime < '{1}'", date1, date2); } if (pay_date1.Length > 0) { sqlDate.AppendFormat("and o.pay_time >= '{0}' AND o.pay_time < '{1}'", pay_date1, pay_date2); } sql.AppendFormat("SELECT p.PType AS goodsName, COUNT ( * ) AS AllOrder, COUNT ( CASE WHEN o.AfterSaleState >= 1 THEN 1 END ) AS aftersaleOrder FROM CE_ErpTradeCell o LEFT JOIN CE_ErpProduct p ON o.ProductId = p.ID WHERE 1=1 {0} GROUP BY p.PType", sqlDate); DataTable dtafter = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; int totalAfter = 0; DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("goodsName", typeof(string))); dt.Columns.Add(new DataColumn("aftersaleOrder", typeof(string))); dt.Columns.Add(new DataColumn("AllOrder", typeof(string))); dt.Columns.Add(new DataColumn("rate", typeof(string))); dt.Columns.Add(new DataColumn("rateProp", typeof(string))); DataRow dr_he = dt.NewRow(); dr_he[0] = "合计"; dr_he[1] = 0; dr_he[2] = 0; dr_he[3] = 0; dr_he[4] = 100; foreach (DataRow row in dtafter.Rows) { DataRow dr = dt.NewRow(); dr[0] = row["goodsName"]; dr[1] = row["aftersaleOrder"]; dr[2] = row["AllOrder"]; dr[4] = 0; decimal rate = 0; if (dr[1].ToString() != "0") { rate = Math.Round(Convert.ToDecimal(dr[1]) / Convert.ToDecimal(dr[2]), 4); } dr[3] = Math.Round(rate * 100, 2); dt.Rows.Add(dr); dr_he[1] = Convert.ToDecimal(dr_he[1]) + Convert.ToDecimal(dr[1]); dr_he[2] = Convert.ToDecimal(dr_he[2]) + Convert.ToDecimal(dr[2]); totalAfter += Convert.ToInt32(dr[1]); } foreach (DataRow row in dt.Rows) { if (Convert.ToDecimal(row[1]) > 0) { row[4] = Math.Round(Math.Round(Convert.ToDecimal(row[1]) / Convert.ToDecimal(totalAfter), 4) * 100, 2); } } decimal pgrate = Math.Round(Convert.ToDecimal(dr_he[1]) / Convert.ToDecimal(dr_he[2]), 4); dr_he[3] = Math.Round(pgrate * 100, 2); dt.Rows.Add(dr_he); DataView dv = new DataView(dt); //dv.Sort = "AllOrder desc"; DataTable dtNew = dv.ToTable(); writeGridDataTableJson(dtNew.Rows.Count, dtNew); } public void get_erp_aftersale_org_design_gather() { string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); string pay_date1 = GetPostString("pay_date1"); string pay_date2 = GetPostString("pay_date2"); if (date1.Length <= 0 && pay_date1.Length <= 0) { pay_date1 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).ToString("yyyy-MM-dd HH:mm:ss"); pay_date2 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59).ToString("yyyy-MM-dd HH:mm:ss"); } StringBuilder sql = new StringBuilder(); StringBuilder sqlDate = new StringBuilder(); if (date1.Length > 0) { sqlDate.AppendFormat("and o.HandleTime >= '{0}' AND o.HandleTime < '{1}'", date1, date2); } if (pay_date1.Length > 0) { sqlDate.AppendFormat("and o.pay_time >= '{0}' AND o.pay_time < '{1}'", pay_date1, pay_date2); } sql.AppendFormat("SELECT u.OrgName, COUNT ( * ) AS AllOrder, COUNT ( CASE WHEN o.AfterSaleState >= 1 THEN 1 END ) AS aftersaleOrder FROM CE_ErpTradeCell o LEFT JOIN view_ErpUser u ON o.DesignUserId = u.ID WHERE 1=1 {0} GROUP BY u.OrgName", sqlDate); DataTable dtafter = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; int totalAfter = 0; DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("OrgName", typeof(string))); dt.Columns.Add(new DataColumn("aftersaleOrder", typeof(string))); dt.Columns.Add(new DataColumn("AllOrder", typeof(string))); dt.Columns.Add(new DataColumn("rate", typeof(string))); dt.Columns.Add(new DataColumn("rateProp", typeof(string))); DataRow dr_he = dt.NewRow(); dr_he[0] = "合计"; dr_he[1] = 0; dr_he[2] = 0; dr_he[3] = 0; dr_he[4] = 100; foreach (DataRow row in dtafter.Rows) { DataRow dr = dt.NewRow(); dr[0] = row["OrgName"]; dr[1] = row["aftersaleOrder"]; dr[2] = row["AllOrder"]; dr[4] = 0; decimal rate = 0; if (dr[1].ToString() != "0") { rate = Math.Round(Convert.ToDecimal(dr[1]) / Convert.ToDecimal(dr[2]), 4); } dr[3] = Math.Round(rate * 100, 2); dt.Rows.Add(dr); dr_he[1] = Convert.ToDecimal(dr_he[1]) + Convert.ToDecimal(dr[1]); dr_he[2] = Convert.ToDecimal(dr_he[2]) + Convert.ToDecimal(dr[2]); totalAfter += Convert.ToInt32(dr[1]); } foreach (DataRow row in dt.Rows) { if (Convert.ToDecimal(row[1]) > 0) { row[4] = Math.Round(Math.Round(Convert.ToDecimal(row[1]) / Convert.ToDecimal(totalAfter), 4) * 100, 2); } } decimal chushu = Convert.ToDecimal(dr_he[2]) == 0 ? 1 : Convert.ToDecimal(dr_he[2]); decimal pgrate = Math.Round(Convert.ToDecimal(dr_he[1]) / Convert.ToDecimal(dr_he[2]), 4); dr_he[3] = Math.Round(pgrate * 100, 2); dt.Rows.Add(dr_he); DataView dv = new DataView(dt); //dv.Sort = "AllOrder desc"; DataTable dtNew = dv.ToTable(); writeGridDataTableJson(dtNew.Rows.Count, dtNew); } public void get_erp_aftersale_org_custom_gather() { string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); string pay_date1 = GetPostString("pay_date1"); string pay_date2 = GetPostString("pay_date2"); if (date1.Length <= 0 && pay_date1.Length <= 0) { pay_date1 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).ToString("yyyy-MM-dd HH:mm:ss"); pay_date2 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59).ToString("yyyy-MM-dd HH:mm:ss"); } StringBuilder sql = new StringBuilder(); StringBuilder sqlDate = new StringBuilder(); if (date1.Length > 0) { sqlDate.AppendFormat("and o.HandleTime >= '{0}' AND o.HandleTime < '{1}'", date1, date2); } if (pay_date1.Length > 0) { sqlDate.AppendFormat("and o.pay_time >= '{0}' AND o.pay_time < '{1}'", pay_date1, pay_date2); } sql.AppendFormat("SELECT u.OrgName, COUNT ( * ) AS AllOrder, COUNT ( CASE WHEN o.AfterSaleState >= 1 THEN 1 END ) AS aftersaleOrder FROM CE_ErpTradeCell o LEFT JOIN view_ErpUser u ON o.CustomerUserId = u.ID WHERE 1=1 {0} GROUP BY u.OrgName", sqlDate); DataTable dtafter = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; int totalAfter = 0; DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("OrgName", typeof(string))); dt.Columns.Add(new DataColumn("aftersaleOrder", typeof(string))); dt.Columns.Add(new DataColumn("AllOrder", typeof(string))); dt.Columns.Add(new DataColumn("rate", typeof(string))); dt.Columns.Add(new DataColumn("rateProp", typeof(string))); DataRow dr_he = dt.NewRow(); dr_he[0] = "合计"; dr_he[1] = 0; dr_he[2] = 0; dr_he[3] = 0; dr_he[4] = 100; foreach (DataRow row in dtafter.Rows) { DataRow dr = dt.NewRow(); dr[0] = row["OrgName"]; dr[1] = row["aftersaleOrder"]; dr[2] = row["AllOrder"]; dr[4] = 0; decimal rate = 0; if (dr[1].ToString() != "0") { rate = Math.Round(Convert.ToDecimal(dr[1]) / Convert.ToDecimal(dr[2]), 4); } dr[3] = Math.Round(rate * 100, 2); dt.Rows.Add(dr); dr_he[1] = Convert.ToDecimal(dr_he[1]) + Convert.ToDecimal(dr[1]); dr_he[2] = Convert.ToDecimal(dr_he[2]) + Convert.ToDecimal(dr[2]); totalAfter += Convert.ToInt32(dr[1]); } foreach (DataRow row in dt.Rows) { if (Convert.ToDecimal(row[1]) > 0) { row[4] = Math.Round(Math.Round(Convert.ToDecimal(row[1]) / Convert.ToDecimal(totalAfter), 4) * 100, 2); } } decimal pgrate = Math.Round(Convert.ToDecimal(dr_he[1]) / Convert.ToDecimal(dr_he[2]), 4); dr_he[3] = Math.Round(pgrate * 100, 2); dt.Rows.Add(dr_he); DataView dv = new DataView(dt); //dv.Sort = "AllOrder desc"; DataTable dtNew = dv.ToTable(); writeGridDataTableJson(dtNew.Rows.Count, dtNew); } public void get_erp_aftersale_sup_goods_gather() { string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); string pay_date1 = GetPostString("pay_date1"); string pay_date2 = GetPostString("pay_date2"); if (date1.Length <= 0 && pay_date1.Length <= 0) { pay_date1 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).ToString("yyyy-MM-dd HH:mm:ss"); pay_date2 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59).ToString("yyyy-MM-dd HH:mm:ss"); } StringBuilder sql = new StringBuilder(); StringBuilder sqlDate = new StringBuilder(); if (date1.Length > 0) { sqlDate.AppendFormat("and o.HandleTime >= '{0}' AND o.HandleTime < '{1}'", date1, date2); } if (pay_date1.Length > 0) { sqlDate.AppendFormat("and o.pay_time >= '{0}' AND o.pay_time < '{1}'", pay_date1, pay_date2); } sql.AppendFormat("SELECT p.PType AS goodsName,s.ComName , COUNT ( * ) AS AllOrder, COUNT ( CASE WHEN o.AfterSaleState >= 1 THEN 1 END ) AS aftersaleOrder FROM CE_ErpTradeCell o LEFT JOIN CE_ErpProduct p ON o.ProductId = p.ID LEFT JOIN CE_ErpSupplier s ON s.ID = o.SupplierId WHERE 1=1 {0} GROUP BY p.PType, s.ComName ORDER BY p.PType DESC", sqlDate); DataTable dtafter = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("goodsName", typeof(string))); dt.Columns.Add(new DataColumn("ComName", typeof(string))); dt.Columns.Add(new DataColumn("aftersaleOrder", typeof(string))); dt.Columns.Add(new DataColumn("AllOrder", typeof(string))); dt.Columns.Add(new DataColumn("rate", typeof(string))); DataRow dr_he = dt.NewRow(); dr_he[0] = "合计"; dr_he[1] = 0; dr_he[2] = 0; dr_he[3] = 0; dr_he[4] = 0; foreach (DataRow row in dtafter.Rows) { DataRow dr = dt.NewRow(); dr[0] = row["goodsName"]; dr[1] = row["ComName"]; dr[2] = row["aftersaleOrder"]; dr[3] = row["AllOrder"]; decimal rate = 0; if (dr[2].ToString() != "0") { rate = Math.Round(Convert.ToDecimal(dr[2]) / Convert.ToDecimal(dr[3]), 4); } dr[4] = Math.Round(rate * 100, 2); dt.Rows.Add(dr); dr_he[2] = Convert.ToDecimal(dr_he[2]) + Convert.ToDecimal(dr[2]); dr_he[3] = Convert.ToDecimal(dr_he[3]) + Convert.ToDecimal(dr[3]); } decimal pgrate = Math.Round(Convert.ToDecimal(dr_he[2]) / Convert.ToDecimal(dr_he[3]), 4); dr_he[4] = Math.Round(pgrate * 100, 2); dt.Rows.Add(dr_he); DataView dv = new DataView(dt); //dv.Sort = "AllOrder desc"; DataTable dtNew = dv.ToTable(); writeGridDataTableJson(dtNew.Rows.Count, dtNew); } public void get_erp_aftersale_sup_gather() { string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); string pay_date1 = GetPostString("pay_date1"); string pay_date2 = GetPostString("pay_date2"); if (date1.Length <= 0 && pay_date1.Length <= 0) { pay_date1 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).ToString("yyyy-MM-dd HH:mm:ss"); pay_date2 = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59).ToString("yyyy-MM-dd HH:mm:ss"); } StringBuilder sql = new StringBuilder(); StringBuilder sqlDate = new StringBuilder(); if (date1.Length > 0) { sqlDate.AppendFormat("and o.HandleTime >= '{0}' AND o.HandleTime < '{1}'", date1, date2); } if (pay_date1.Length > 0) { sqlDate.AppendFormat("and o.pay_time >= '{0}' AND o.pay_time < '{1}'", pay_date1, pay_date2); } sql.AppendFormat("SELECT s.ComName ,o.AfterSaleReason, COUNT ( * ) AS AllOrder FROM CE_ErpTradeCell o LEFT JOIN CE_ErpSupplier s ON s.ID = o.SupplierId WHERE 1 = 1 AND o.AfterSaleState >= 1 AND o.AfterSaleResSupId > 0 {0} GROUP BY o.AfterSaleReason, s.ComName ORDER BY s.ComName DESC", sqlDate); DataTable dtafter = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("ComName", typeof(string))); dt.Columns.Add(new DataColumn("AfterSaleReason1", typeof(string))); dt.Columns.Add(new DataColumn("AfterSaleReason2", typeof(string))); dt.Columns.Add(new DataColumn("AfterSaleReason3", typeof(string))); dt.Columns.Add(new DataColumn("AllOrder", typeof(string))); dt.Columns.Add(new DataColumn("rate", typeof(string))); DataRow dr_he = dt.NewRow(); dr_he[0] = "合计"; dr_he[1] = 0; dr_he[2] = 0; dr_he[3] = 0; dr_he[4] = 0; dr_he[5] = 0; foreach (DataRow row in dtafter.Rows) { string reason = row["AfterSaleReason"].ToString(); string[] reason_list = reason.Split(new char[] { '|' }); DataRow dr = dt.NewRow(); dr[0] = row["ComName"]; if (reason_list.Length > 0) { dr[1] = reason_list[0]; } if (reason_list.Length > 1) { dr[2] = reason_list[1]; } if (reason_list.Length > 2) { dr[3] = reason_list[2]; } dr[4] = row["AllOrder"]; dr[5] = 0; dt.Rows.Add(dr); dr_he[4] = Convert.ToDecimal(dr_he[4]) + Convert.ToDecimal(dr[4]); } foreach (DataRow row in dt.Rows) { row[5] = Math.Round(Math.Round(Convert.ToDecimal(row[4]) / Convert.ToDecimal(dr_he[4]), 4) * 100, 2); } dr_he[5] = 100; dt.Rows.Add(dr_he); DataView dv = new DataView(dt); //dv.Sort = "AllOrder desc"; DataTable dtNew = dv.ToTable(); writeGridDataTableJson(dtNew.Rows.Count, dtNew); } //select CustomerUserId, COUNT(ctid) as counta,SUM(isDianziOrder) as dianzi,CONVERT(varchar, CAST(CAST(CAST(sum(isDianziOrder)as float)/CAST(count(ctid) as float) as float)*10000/100 as decimal (9,2)))+'%' as per from CE_ErpTradeCell where IsOldCustomer=1 group by CustomerUserId order by CustomerUserId asc; public void get_customer_oldcustomer() { string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); string orgid = GetPostString("org"); if (orgid == "") { orgid = "0"; } if (date1.Length <= 0) { date1 = commonHelper.FirstDayOfMonth(DateTime.Now).ToString(); date2 = commonHelper.LastDayOfMonth(DateTime.Now).ToString(); } StringBuilder sql = new StringBuilder(); sql.AppendFormat("select CustomerUserId,CustomerTb, COUNT(ctid) as ocount,SUM(IsOldCustomerMore) as omore,CONVERT(varchar, CAST(CAST(CAST(sum(IsOldCustomerMore)as float)/CAST(count(ctid) as float) as float)*10000/100 as decimal (9,2)))+'%' as per from view_ErpTradeCell where IsOldCustomer=1 and datediff(day,'{0}',pay_time)>=0 and datediff(day,'{1}',pay_time)<=0 and CusOrgID={2} group by CustomerUserId,CustomerTb", date1, date2, Convert.ToInt32(orgid)); //DataSet ds = CeErpPost.ExecuteDataset(sql.ToString()); DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; //returnSuccess(Utils.Serialization.JsonString.DataTable2MiniAjaxJson(dt)); writeGridDataTableJson(dt.Rows.Count, dt); } public void get_report_customer_order() { DataStruct dStruct = GetPostStruct(); List lw = new List(); string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); string dw = GetDateMinuteWhere("pay_time", date1, date2); if (dw.Length > 0) lw.Add(dw); string sellernick = GetPostString("seller_nick"); string isOld = GetPostString("isOld"); if (sellernick.Length > 0) { lw.Add(string.Format("seller_nick like '%{0}%'", sellernick)); } if (isOld.Length > 0) { lw.Add(string.Format("IsOldCustomer={0}", Convert.ToInt32(isOld))); } lw.Add(string.Format("IsSample=0")); lw.Add(string.Format("IsGift=0")); dStruct.Order = "pay_time desc"; dStruct.MainWhere = string.Join(" and ", lw.ToArray()); DataTable dt = null; dt = WebCache.GetData("view_erptradecell", dStruct); writeGridDataTableJson(dStruct.TotalCount, dt); } public void get_customer_info() { string buyernick = GetPostString("buyer_nick"); StringBuilder sql = new StringBuilder(); sql.AppendFormat("SELECT * FROM CE_ErpCustomer WHERE buyer_nick='{0}'; ", buyernick); //DataSet ds = CeErpPost.ExecuteDataset(sql.ToString()); DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; returnSuccess(Utils.Serialization.JsonString.DataTable2MiniAjaxJson(dt)); } public void get_old_customer_per() { string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); string sellernick = GetPostString("seller_nick"); string sqStr = ""; if (sellernick.Length > 0) { sqStr += " and seller_nick='" + sellernick + "' "; } string isOld = GetPostString("isOld"); if (isOld.Length > 0) { sqStr += " and IsOldCustomer=" + isOld + " "; } StringBuilder sql = new StringBuilder(); sql.AppendFormat("select Count(tid) as total,isnull(SUM(IsOldCustomer),0) as old from view_ErpTradeCell where IsSample=0 and IsGift=0 and DATEDIFF(s,pay_time,CONVERT(datetime, '{0}',20))<0 and DATEDIFF(s, pay_time,CONVERT(datetime,'{1}',20))>0 {2};", date1, date2, sqStr); DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; var res_obj = new { total = Convert.ToInt32(dt.Rows[0]["total"]), old = Convert.ToInt32(dt.Rows[0]["old"]) }; string ro_json = JsonConvert.SerializeObject(res_obj); ReturnSuccess(ro_json); } public void get_report_shop_product_count() { string sellernick = GetPostString("seller_nick"); string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); StringBuilder sql = new StringBuilder(); if (sellernick.Length > 0) { sql.AppendFormat("SELECT ProductName AS pname,count(id) AS count,SUM(price) as amount FROM view_ErpBill WHERE (state=1 or state=2) and seller_nick='{2}' and DATEDIFF(d, CONVERT(datetime, '{0}',20), executeTime) >= 0 and DATEDIFF(d, CONVERT(datetime, '{1}',20), executeTime) <= 0 GROUP BY view_ErpBill.ProductName order by count desc; ", date1, date2, sellernick); } else sql.AppendFormat("SELECT ProductName AS pname,count(id) AS count,SUM(price) as amount FROM view_ErpBill WHERE (state=1 or state=2) and DATEDIFF(d, CONVERT(datetime, '{0}',20), executeTime) >= 0 and DATEDIFF(d, CONVERT(datetime, '{1}',20), executeTime) <= 0 GROUP BY view_ErpBill.ProductName order by count desc; ", date1, date2); //DataSet ds = CeErpPost.ExecuteDataset(sql.ToString()); DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; //returnSuccess(Utils.Serialization.JsonString.DataTable2MiniAjaxJson(dt)); writeGridDataTableJson(dt.Rows.Count, dt); } public void get_customer_shop_payment_count() { string sellernick = GetPostString("seller_nick"); string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); StringBuilder sql = new StringBuilder(); string shopStr = ""; if (sellernick.Length > 0) { shopStr = " and seller_nick='" + sellernick + "'"; } sql.AppendFormat(" select a.ID, a.Name, ISNULL(b.count_40,0) as c_40,ISNULL(c.count_80,0) as c_80,ISNULL(d.count_100,0) as c_100 ,ISNULL(e.count_150,0) as c_150,ISNULL(f.count_200,0) as c_200,ISNULL(g.count_500,0) " + "as c_500 ,ISNULL(h.count_1000,0) as c_1000 from(select ID, Name from view_ErpUser where (PostCode= 'CustomerService' or PostCode = 'Director') and isOpen = 1) as a " + "left join(select CustomerUserId, count(0) as count_40 from view_ErpTradeCell where payment <= 40 {2} " + "and DATEDIFF(DAY, pay_time, '{0}') <= 0 and DATEDIFF(DAY, pay_time, '{1}') >= 0 group by CustomerUserId) as b on a.ID = b.CustomerUserId " + "left join(select CustomerUserId, count(0) as count_80 from view_ErpTradeCell where (payment between 50 and 79.99) {2} " + "and DATEDIFF(DAY, pay_time, '{0}') <= 0 and DATEDIFF(DAY, pay_time, '{1}') >= 0 group by CustomerUserId) as c on a.ID = c.CustomerUserId " + "left join(select CustomerUserId, count(0) as count_100 from view_ErpTradeCell where (payment between 80 and 99.99) {2} " + "and DATEDIFF(DAY, pay_time,'{0}')<= 0 and DATEDIFF(DAY, pay_time,'{1}')>= 0 group by CustomerUserId) as d on a.ID = d.CustomerUserId " + "left join(select CustomerUserId, count(0) as count_150 from view_ErpTradeCell where (payment between 100 and 149.99) {2} " + "and DATEDIFF(DAY, pay_time,'{0}')<= 0 and DATEDIFF(DAY, pay_time,'{1}')>= 0 group by CustomerUserId) as e on a.ID = e.CustomerUserId " + "left join(select CustomerUserId, count(0) as count_200 from view_ErpTradeCell where (payment between 150 and 199.99) {2} " + "and DATEDIFF(DAY, pay_time,'{0}')<= 0 and DATEDIFF(DAY, pay_time,'{1}')>= 0 group by CustomerUserId) as f on a.ID = f.CustomerUserId " + "left join(select CustomerUserId, count(0) as count_500 from view_ErpTradeCell where (payment between 200 and 499.99) {2} " + "and DATEDIFF(DAY, pay_time,'{0}')<= 0 and DATEDIFF(DAY, pay_time,'{1}')>= 0 group by CustomerUserId) as g on a.ID = g.CustomerUserId " + "left join(select CustomerUserId, count(0) as count_1000 from view_ErpTradeCell where payment >= 500 {2} " + "and DATEDIFF(DAY, pay_time,'{0}')<= 0 and DATEDIFF(DAY, pay_time,'{1}')>= 0 group by CustomerUserId) as h on a.ID = h.CustomerUserId " + " where (count_40>0 or count_80>0 or count_100>0 or count_150>0 or count_200>0 or count_500>0 or count_1000>0) order by c_40 desc, c_80 desc", date1, date2, shopStr); //DataSet ds = CeErpPost.ExecuteDataset(sql.ToString()); DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; //returnSuccess(Utils.Serialization.JsonString.DataTable2MiniAjaxJson(dt)); writeGridDataTableJson(dt.Rows.Count, dt); } public void get_bag_shop_count() { string sellernick = GetPostString("seller_nick"); string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); StringBuilder sql = new StringBuilder(); string shopStr = ""; if (sellernick.Length > 0) { shopStr = " and seller_nick='" + sellernick + "'"; } sql.AppendFormat(" select bagName, count(oid) as bcount,sum(price) as bamount from view_erptradeorder where isBagOrder=1 and DATEDIFF(DAY, pay_time,'{0}')<= 0 and DATEDIFF(DAY, pay_time,'{1}')>= 0 and (refund_status='NO_REFUND' or refund_status='REFUNDING') {2} group by bagName order by bcount desc; ", date1, date2, shopStr); //DataSet ds = CeErpPost.ExecuteDataset(sql.ToString()); DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; //returnSuccess(Utils.Serialization.JsonString.DataTable2MiniAjaxJson(dt)); writeGridDataTableJson(dt.Rows.Count, dt); } public void get_customer_addwechat_persent() { string sellernick = GetPostString("seller_nick"); string date1 = GetPostString("date1"); string date2 = GetPostString("date2"); StringBuilder sql = new StringBuilder(); string shopStr = ""; if (sellernick.Length > 0) { shopStr = " and seller_nick='" + sellernick + "'"; } sql.AppendFormat(" select a.ID, a.Name, ISNULL(b.total,0) as total,ISNULL(c.addcount,0) as addcount from(select ID, Name from view_ErpUser where (PostCode= 'CustomerService' or PostCode = 'Director' or PostCode = 'CustomerMaster') and isOpen = 1) as a " + "left join(select CustomerUserId, count(0) as total from view_ErpTradeCell where IsOldCustomer=0 and IsSample=0 and isaddwechat<2 and DATEDIFF(DAY, pay_time, '{0}') <= 0 and DATEDIFF(DAY, pay_time, '{1}') >= 0 {2} group by CustomerUserId) as b on a.ID = b.CustomerUserId " + "left join(select CustomerUserId, count(0) as addcount from view_ErpTradeCell where isaddwechat=1 and DATEDIFF(DAY, pay_time, '{0}') <= 0 and DATEDIFF(DAY, pay_time, '{1}') >= 0 {2} group by CustomerUserId) as c on a.ID = c.CustomerUserId where total>0 order by total desc", date1, date2, shopStr); //DataSet ds = CeErpPost.ExecuteDataset(sql.ToString()); DataTable dt = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; //returnSuccess(Utils.Serialization.JsonString.DataTable2MiniAjaxJson(dt)); writeGridDataTableJson(dt.Rows.Count, dt); } public void get_cuntomer_time_out_gather() { string pay_date1 = GetPostString("pay_date1"); string pay_date2 = GetPostString("pay_date2"); string orgid = GetPostString("orgid"); StringBuilder sql = new StringBuilder(); StringBuilder sqlDate = new StringBuilder(); if (pay_date1.Length > 0) { sqlDate.AppendFormat(" AND o.pay_time >= '{0}' ", pay_date1); } if (pay_date2.Length > 0) { sqlDate.AppendFormat(" AND o.pay_time < '{0}'", pay_date2); } if (orgid.Length > 0) { sqlDate.AppendFormat(" and u.OrgId={0}", orgid); } sql.AppendFormat("SELECT u.Name,u.id, u.OrgName, COUNT ( o.ctid ) AS number FROM CE_ErpTradeCell o LEFT JOIN CE_ErpTrade t ON t.tid = o.tid LEFT JOIN view_ErpUser u ON u.ID = o.CustomerUserId WHERE o.CustomerUserId > 0 AND t.create_time > 0 AND t.pay_time > 0 AND datediff( HOUR, t.pay_time,t.create_time ) > 1 {0} GROUP BY u.id,u.Name, u.OrgName ORDER BY number DESC", sqlDate); DataTable dtafter = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; writeGridDataTableJson(dtafter.Rows.Count, dtafter); } public void get_design_time_out_gather() { string pay_date1 = GetPostString("pay_date1"); string pay_date2 = GetPostString("pay_date2"); string orgid = GetPostString("orgid"); StringBuilder sql = new StringBuilder(); StringBuilder sqlDate = new StringBuilder(); if (pay_date1.Length > 0) { sqlDate.AppendFormat(" and o.WaitDesignTime >= '{0}' ", pay_date1); } if (pay_date2.Length > 0) { sqlDate.AppendFormat(" AND o.WaitDesignTime < '{0}'", pay_date2); } if (orgid.Length > 0) { sqlDate.AppendFormat(" and u.OrgId={0}", orgid); } sql.AppendFormat("SELECT u.Name,u.id, u.OrgName, COUNT ( o.ctid ) AS number FROM CE_ErpTradeCell o LEFT JOIN view_ErpUser u ON u.ID = o.DesignUserId WHERE o.DesignUserId > 0 AND o.WaitDesignTime > 0 AND o.FinishDesignTime > 0 AND datediff( HOUR, o.WaitDesignTime, o.FinishDesignTime ) > 24 {0} GROUP BY u.id,u.Name, u.OrgName ORDER BY number DESC", sqlDate); DataTable dtafter = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0]; writeGridDataTableJson(dtafter.Rows.Count, dtafter); } } }