erp/SiteCore/Handler/sync.orderReport.cs

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<string> lw = new List<string>();
            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<string> lw = new List<string>();
            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<string> lw2 = new List<string>();

                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<object, Dictionary<String, object>> mapweek = new Dictionary<object, Dictionary<String, object>>();
                foreach (DataRow dr3 in dt3.Rows)
                {
                    Dictionary<String, object> map2 = new Dictionary<String, object>();
                    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<object, Dictionary<String, object>> mapDay = new Dictionary<object, Dictionary<String, object>>();
                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<String, object> map2 = new Dictionary<String, object>();
                    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<object, Dictionary<String, object>> map1 = new Dictionary<object, Dictionary<String, object>>();
                foreach (DataRow dr2 in dt2.Rows)
                {
                    Dictionary<String, object> map2 = new Dictionary<String, object>();
                    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<String, object> map3 = map1[dr["did"]];
                        Dictionary<String, object> map4 = mapweek[dr["did"]];
                        Dictionary<String, object> 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<object, Dictionary<String, object>> map1 = new Dictionary<object, Dictionary<String, object>>();
                foreach (DataRow dr2 in dt2.Rows)
                {
                    Dictionary<String, object> map2 = new Dictionary<String, object>();
                    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<object, Dictionary<String, object>> mapweek = new Dictionary<object, Dictionary<String, object>>();
                foreach (DataRow dr3 in dt3.Rows)
                {
                    Dictionary<String, object> map2 = new Dictionary<String, object>();
                    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<object, Dictionary<String, object>> mapDay = new Dictionary<object, Dictionary<String, object>>();
                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<String, object> map2 = new Dictionary<String, object>();
                    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<String, object> map3 = new Dictionary<string, object>();
                        Dictionary<String, object> map4 = new Dictionary<string, object>();
                        Dictionary<String, object> map5 = new Dictionary<string, object>();
                        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));

            string sql = string.Format("SELECT COUNT ( * ) AS allorder, PlaceUserName  FROM view_ErpTradeCell WHERE FinishPlaceTime BETWEEN '{0}' AND '{1}' GROUP BY PlaceUserName", firstDay, lastDay);

            DataTable dataTable = DbHelper.DbConn.ExecuteDataset(sql).Tables[0];
            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;
            foreach (DataRow row in dataTable.Rows)
            {

                DataRow dr = dt.NewRow();
                dr[0] = row["PlaceUserName"];
                dr[1] = row["allorder"];
                dt.Rows.Add(dr);
                dr_he[1] = Convert.ToDecimal(dr_he[1]) + Convert.ToDecimal(dr[1]);
            }
            dt.Rows.Add(dr_he);
            DataView dv = new DataView(dt);
            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, SUM(CASE WHEN TextResult in ('联系不上客户','待处理') THEN 1 ELSE 0 END) AS InProgress, SUM(CASE WHEN TextResult in ('联系不上客户','待处理') THEN 0 ELSE 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  ,COUNT ( CASE WHEN a.IsBad > 0 THEN 1 END ) AS isBad,COUNT ( CASE WHEN a.IsComplain > 0 THEN 1 END ) AS isComplain  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)));
            dt.Columns.Add(new DataColumn("isBad", typeof(string)));
            dt.Columns.Add(new DataColumn("isComplain", 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;
            dr_he[10] = 0;
            dr_he[11] = 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"];
                dr[10] = row["isBad"];
                dr[11] = row["isComplain"];
                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]);
                dr_he[10] = Convert.ToDecimal(dr_he[10]) + Convert.ToDecimal(dr[10]);
                dr_he[11] = Convert.ToDecimal(dr_he[11]) + Convert.ToDecimal(dr[11]);
                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 o.AfterSaleReason, COUNT ( * ) AS total ,COUNT ( CASE WHEN a.IsBad > 0 THEN 1 END ) AS isBad,COUNT ( CASE WHEN a.IsComplain > 0 THEN 1 END ) AS isComplain  FROM CE_ErpTradeCell o LEFT JOIN Ce_ErpTradeAfterSaleExtend a ON o.ctid = a.tid  WHERE o.AfterSaleState >= 1  {0} GROUP BY o.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("AfterSaleReason4", typeof(string)));
            dt.Columns.Add(new DataColumn("total", typeof(string)));
            dt.Columns.Add(new DataColumn("rate", typeof(string)));
            dt.Columns.Add(new DataColumn("isBad", typeof(string)));
            dt.Columns.Add(new DataColumn("isComplain", 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;
            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];
                }
                if (reason_list.Length > 3)
                {
                    dr[3] = reason_list[3];
                }
                dr[4] = row["total"];
                dr[5] = 0;
                dr[6] = row["isBad"];
                dr[7] = row["isComplain"];

                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;
            DataView dv = new DataView(dt);
            //dv.Sort = "AllOrder desc";
            DataTable dtNew = dv.ToTable();
            writeGridDataTableJson(dtNew.Rows.Count, dtNew);

        }
        public void get_erp_aftersale_product_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 o.AfterSaleReason,p.PType, COUNT ( * ) AS total ,COUNT ( CASE WHEN a.IsBad > 0 THEN 1 END ) AS isBad,COUNT ( CASE WHEN a.IsComplain > 0 THEN 1 END ) AS isComplain  FROM CE_ErpTradeCell o LEFT JOIN CE_ErpProduct p ON o.ProductId = p.id LEFT JOIN Ce_ErpTradeAfterSaleExtend a ON o.ctid = a.tid  WHERE o.AfterSaleState >= 1  {0} GROUP BY o.AfterSaleReason,p.PType", sqlDate);
            DataTable dtafter = DbHelper.DbConn.ExecuteDataset(sql.ToString()).Tables[0];

            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("PType", 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("AfterSaleReason4", typeof(string)));
            dt.Columns.Add(new DataColumn("total", typeof(string)));
            dt.Columns.Add(new DataColumn("rate", typeof(string)));
            dt.Columns.Add(new DataColumn("isBad", typeof(string)));
            dt.Columns.Add(new DataColumn("isComplain", 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;
            foreach (DataRow row in dtafter.Rows)
            {
                string reason = row["AfterSaleReason"].ToString();
                string productName = row["PType"].ToString();
                string[] reason_list = reason.Split(new char[] { '|' });
                DataRow dr = dt.NewRow();
                dr[0] = productName;
                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];
                }
                if (reason_list.Length > 3)
                {
                    dr[4] = reason_list[3];
                }
                dr[5] = row["total"];
                dr[6] = 0;
                dr[7] = row["isBad"];
                dr[8] = row["isComplain"];

                dt.Rows.Add(dr);
                dr_he[5] = Convert.ToDecimal(dr_he[5]) + Convert.ToDecimal(dr[5]);
            }
            foreach (DataRow row in dt.Rows)
            {
                row[6] = Math.Round(Math.Round(Convert.ToDecimal(row[5]) / Convert.ToDecimal(dr_he[5]), 4) * 100, 2);
            }
            dr_he[6] = 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,COUNT ( CASE WHEN a.IsBad > 0 THEN 1 END ) AS isBad,COUNT ( CASE WHEN a.IsComplain > 0 THEN 1 END ) AS isComplain  FROM CE_ErpTradeCell o LEFT JOIN Ce_ErpTradeCellExtend e ON o.ctid = e.ctid LEFT JOIN CE_ErpShop s ON o.ShopId = s.id LEFT JOIN Ce_ErpTradeAfterSaleExtend a ON o.ctid = a.tid   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)));
            dt.Columns.Add(new DataColumn("isBad", typeof(string)));
            dt.Columns.Add(new DataColumn("isComplain", 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;
            dr_he[6] = 0;
            dr_he[7] = 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"];
                dr[6] = row["isBad"];
                dr[7] = row["isComplain"];
                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]);
                dr_he[5] = Convert.ToDecimal(dr_he[5]) + Convert.ToDecimal(dr[5]);
                dr_he[6] = Convert.ToDecimal(dr_he[6]) + Convert.ToDecimal(dr[6]);
                dr_he[7] = Convert.ToDecimal(dr_he[7]) + Convert.ToDecimal(dr[7]);
                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 ,COUNT ( CASE WHEN a.IsBad > 0 THEN 1 END ) AS isBad,COUNT ( CASE WHEN a.IsComplain > 0 THEN 1 END ) AS isComplain  FROM CE_ErpTradeCell o LEFT JOIN CE_ErpProduct p ON o.ProductId = p.ID LEFT JOIN Ce_ErpTradeAfterSaleExtend a ON o.ctid = a.tid   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)));
            dt.Columns.Add(new DataColumn("isBad", typeof(string)));
            dt.Columns.Add(new DataColumn("isComplain", 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;
            dr_he[6] = 0;
            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;
                dr[5] = row["isBad"];
                dr[6] = row["isComplain"];
                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]);
                dr_he[5] = Convert.ToDecimal(dr_he[5]) + Convert.ToDecimal(dr[5]);
                dr_he[6] = Convert.ToDecimal(dr_he[6]) + Convert.ToDecimal(dr[6]);
                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, u.Name,o.AfterSaleResponsible, COUNT ( * ) AS AllOrder, COUNT ( CASE WHEN o.AfterSaleState >= 1 THEN 1 END ) AS aftersaleOrder, COUNT ( CASE WHEN a.IsBad > 0 THEN 1 END ) AS isBad, COUNT ( CASE WHEN a.IsComplain > 0 THEN 1 END ) AS isComplain  FROM CE_ErpTradeResponsible r LEFT JOIN CE_ErpTradeCell o ON o.ctid  = r.tid LEFT JOIN view_ErpUser u ON r.UserId = u.ID LEFT JOIN Ce_ErpTradeAfterSaleExtend a ON o.ctid = a.tid  WHERE 1 = 1  AND r.type = 0  AND r.VerifyState > - 1 AND CHARINDEX( '客户问题', o.AfterSaleReason ) = 0 AND CHARINDEX( '系统问题', o.AfterSaleReason ) = 0  {0}  GROUP BY u.OrgName, o.AfterSaleResponsible,u.Name", 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)));
            dt.Columns.Add(new DataColumn("Name", typeof(string)));
            dt.Columns.Add(new DataColumn("isBad", typeof(string)));
            dt.Columns.Add(new DataColumn("isComplain", 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;
            dr_he[6] = 0;
            dr_he[7] = 0;
            foreach (DataRow row in dtafter.Rows)
            {
                string name = row["Name"].ToString();
                string afterSaleResponsible = row["AfterSaleResponsible"].ToString();
                if (string.IsNullOrEmpty(afterSaleResponsible)) { continue; }
                if (afterSaleResponsible.Contains("设计师:" + name))
                {
                    DataRow dr = dt.NewRow();
                    dr[0] = row["OrgName"];
                    dr[1] = row["aftersaleOrder"];
                    dr[2] = row["AllOrder"];
                    dr[4] = 0;
                    dr[5] = row["Name"];
                    dr[6] = row["isBad"];
                    dr[7] = row["isComplain"];
                    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]);
                    dr_he[6] = Convert.ToDecimal(dr_he[6]) + Convert.ToDecimal(dr[6]);
                    dr_he[7] = Convert.ToDecimal(dr_he[7]) + Convert.ToDecimal(dr[7]);
                    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 = Convert.ToDecimal(dr_he[2]) == 0 ? 0 : 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, u.Name,o.AfterSaleResponsible, COUNT ( * ) AS AllOrder, COUNT ( CASE WHEN o.AfterSaleState >= 1 THEN 1 END ) AS aftersaleOrder, COUNT ( CASE WHEN a.IsBad > 0 THEN 1 END ) AS isBad, COUNT ( CASE WHEN a.IsComplain > 0 THEN 1 END ) AS isComplain FROM CE_ErpTradeResponsible r LEFT JOIN CE_ErpTradeCell o ON o.ctid  = r.tid LEFT JOIN view_ErpUser u ON r.UserId = u.ID LEFT JOIN Ce_ErpTradeAfterSaleExtend a ON o.ctid = a.tid WHERE 1 = 1    AND r.type = 0  AND r.VerifyState > - 1 AND CHARINDEX( '客户问题', o.AfterSaleReason ) = 0 AND CHARINDEX( '系统问题', o.AfterSaleReason ) = 0 {0} GROUP BY u.OrgName, o.AfterSaleResponsible,u.Name", 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)));
            dt.Columns.Add(new DataColumn("Name", typeof(string)));
            dt.Columns.Add(new DataColumn("isBad", typeof(string)));
            dt.Columns.Add(new DataColumn("isComplain", 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] = 100;
            dr_he[6] = 0;
            dr_he[7] = 0;
            foreach (DataRow row in dtafter.Rows)
            {
                string name = row["Name"].ToString();
                string afterSaleResponsible = row["AfterSaleResponsible"].ToString();
                if (string.IsNullOrEmpty(afterSaleResponsible)) { continue; }
                if (afterSaleResponsible.Contains("客服:" + name))
                {
                    DataRow dr = dt.NewRow();
                    dr[0] = row["OrgName"];
                    dr[1] = row["aftersaleOrder"];
                    dr[2] = row["AllOrder"];
                    dr[4] = 0;
                    dr[5] = row["Name"];
                    dr[6] = row["isBad"];
                    dr[7] = row["isComplain"];
                    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]);
                    dr_he[6] = Convert.ToDecimal(dr_he[6]) + Convert.ToDecimal(dr[6]);
                    dr_he[7] = Convert.ToDecimal(dr_he[7]) + Convert.ToDecimal(dr[7]);
                    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 = Convert.ToDecimal(dr_he[2]) == 0 ? 0 : 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("AfterSaleReason4", 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;
            dr_he[6] = 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];
                }
                if (reason_list.Length > 3)
                {
                    dr[4] = reason_list[3];
                }
                dr[5] = row["AllOrder"];
                dr[6] = 0;

                dt.Rows.Add(dr);
                dr_he[5] = Convert.ToDecimal(dr_he[5]) + Convert.ToDecimal(dr[5]);
            }
            foreach (DataRow row in dt.Rows)
            {
                row[6] = Math.Round(Math.Round(Convert.ToDecimal(row[5]) / Convert.ToDecimal(dr_he[5]), 4) * 100, 2);
            }
            dr_he[6] = 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_sup_product_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  ,p.PType,o.AfterSaleReason, COUNT ( * ) AS AllOrder  FROM CE_ErpTradeCell o LEFT JOIN CE_ErpSupplier s ON s.ID = o.SupplierId LEFT JOIN CE_ErpProduct p ON o.ProductId = p.id   WHERE 1 = 1 AND o.AfterSaleState >= 1  AND o.AfterSaleResSupId > 0  {0}  GROUP BY o.AfterSaleReason, s.ComName,p.PType   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("AfterSaleReason4", typeof(string)));
            dt.Columns.Add(new DataColumn("AllOrder", typeof(string)));
            dt.Columns.Add(new DataColumn("rate", typeof(string)));
            dt.Columns.Add(new DataColumn("PType", 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;
            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];
                }
                if (reason_list.Length > 3)
                {
                    dr[4] = reason_list[3];
                }
                dr[5] = row["AllOrder"];
                dr[6] = 0;
                dr[7] = row["PType"];

                dt.Rows.Add(dr);
                dr_he[5] = Convert.ToDecimal(dr_he[5]) + Convert.ToDecimal(dr[5]);
            }
            foreach (DataRow row in dt.Rows)
            {
                row[6] = Math.Round(Math.Round(Convert.ToDecimal(row[5]) / Convert.ToDecimal(dr_he[5]), 4) * 100, 2);
            }
            dr_he[6] = 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<string> lw = new List<string>();
            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);
        }
    }
}