lyfz_repos/铂金版源码/LYFZ.Software.MainBusiness/StatisticalEmployeeWork/WorkStatisticalStoreOpenSingle.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace LYFZ.Software.MainBusiness.StatisticalEmployeeWork
{
    public partial class WorkStatisticalStoreOpenSingle : LYFZ.Software.UI.StatisticalEmployeeWork.WorkStatisticalStoreOpenSingle
    {
        LYFZ.BLL.BLL_ErpOrder orbll = new BLL.BLL_ErpOrder();
        public WorkStatisticalStoreOpenSingle()
        {
            this.Load += WorkStatisticalStoreOpenSingle_Load;
            this.Shown += WorkStatisticalStoreOpenSingle_Shown;
            this.dtDataTimeStart.Leave += dtDataTimeStart_Leave;
            this.btnQuery.Click += btnQuery_Click;
            this.btnExport.Click += btnExport_Click;
            this.btnClose.Click += btnClose_Click;
        }
        /// <summary>
        /// 窗体加载事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void WorkStatisticalStoreOpenSingle_Load(object sender, EventArgs e)
        {
            this.dtDataTimeStart.DateValue = SDateTime.Now.ToString("yyyy-MM") + "-01";
            this.dtDataTimeEnd.DateValue = Convert.ToDateTime(this.dtDataTimeStart.DateValue).AddMonths(1).AddSeconds(-1).ToString("yyyy-MM-dd");
        }
        /// <summary>
        /// 窗体加载事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void WorkStatisticalStoreOpenSingle_Shown(object sender, EventArgs e)
        {
            LYFZ.Software.MainBusiness.DoorCityProcess.PublicCodeClasses.BindComboBoxTreeView_DepartmentAndEmployee(this.cmbtreevperson, true, IsFirstNodeName: "全部", IsShowResign: false);
            cmbtreevperson.IsNodeMouseClick = true;
            cmbtreevperson.IsSelectParentNode = true;
            
            this.cmbtreevperson.TextFindTag("全部");
            LYFZ.Software.MainBusiness.DoorCityProcess.PublicCodeClasses.BindComboxTreeView(this.cmbtreevStatisticalType, "0:按订单套系统计,1:按订单收款统计", IsSplit: true);
            this.cmbtreevStatisticalType.TagFindText("0");
            this.btnQuery_Click(this, null);

            bool b = LYFZ.Software.MainBusiness.VersionControl.StaticVersion.UIFunctionVersion(VersionControl.VersionFunctionEnum.工作统计);
            if (!b)
            {
                this.Close();
            }
        }
        /// <summary>
        /// 开始日期离开事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void dtDataTimeStart_Leave(object sender, EventArgs e)
        {
            this.dtDataTimeEnd.DateValue = Convert.ToDateTime(this.dtDataTimeStart.DateValue).AddMonths(1).AddSeconds(-1).ToString("yyyy-MM-dd");
        }

        /// <summary>
        /// 获取员工编号
        /// </summary>
        /// <returns></returns>
        string GetEmployeeDataInfo()
        {
            string strWhere = "";
            TreeNode tBode = this.cmbtreevperson.SelectedNode;
            if (tBode.Name == "StoreName")
            {
                if (tBode.Nodes.Count > 0)
                {
                    foreach (TreeNode node in tBode.Nodes)
                    {
                        if (node.Nodes.Count > 0)
                        {
                            foreach (TreeNode userNode in node.Nodes)
                            {
                                
                                if (strWhere != "")
                                {
                                    strWhere += " or ";
                                }
                                strWhere += " Ord_OrderPersonNumber like '%" + userNode.Tag + "%'";
                            }
                        }
                    }
                }
            }
            else if (tBode.Name == "Department")
            {
                if (tBode.Nodes.Count > 0)
                {
                    foreach (TreeNode userNode in tBode.Nodes)
                    {
                        
                        if (strWhere != "")
                        {
                            strWhere += " or ";
                        }
                        strWhere += " Ord_OrderPersonNumber like '%" + userNode.Tag + "%'";
                    }
                }
            }
            else if (tBode.Name == "User")
            {
                
                if (strWhere != "")
                {
                    strWhere += " or ";
                }
                strWhere += " Ord_OrderPersonNumber like '%" + tBode.Tag + "%'";
                //strWhere +=  tBode.Tag ;
            }
            if (string.IsNullOrEmpty(strWhere))
            {
                strWhere = " Ord_OrderPersonNumber = '-1'";
            }
            //strDepartment = "'" + string.Join("','", department.ToArray()) + "'";
            return strWhere;
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void btnQuery_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(this.dtDataTimeStart.DateValue.Trim()) && string.IsNullOrEmpty(this.dtDataTimeEnd.DateValue.Trim()))
            { MessageBoxCustom.Show("日期段不能为空"); return; }
            if (Convert.ToDateTime(this.dtDataTimeEnd.DateValue) < Convert.ToDateTime(this.dtDataTimeStart.DateValue))
            { MessageBoxCustom.Show("开始日期不能大于结束日期"); return; }
            if (string.IsNullOrEmpty(this.cmbtreevperson.Text.Trim()))
            { MessageBoxCustom.Show("门市不能为空！"); return; }
            string StrWhere = "";

            DataTable dt = null;
            string StrStatisticalText = "";
            if (this.cmbtreevStatisticalType.Tag.ToString().Trim() == "0")
            {
                #region 按订单套系统计
                StrWhere += "Ord_Class = '1'";
                StrWhere += LYFZ.Software.MainBusiness.DoorCityProcess.PublicCodeClasses.GetDateTime("Ord_CreateDateTime", this.dtDataTimeStart.DateValue.Trim(), this.dtDataTimeEnd.DateValue.Trim());
                if (this.cmbtreevperson.Text.Trim() != "全部")
                { StrWhere += " And ("+ GetEmployeeDataInfo() + ")"; }
                else
                { StrWhere += " And Ord_OrderPersonNumber != ''"; }
                string StrTableName = "tb_ErpOrder Left Join tempTB_AggregationCustomer on Ord_Number = GP_OrderNumber Left Join dbo.BView_OrderAmount on Order_Number = Ord_Number Left Join OrderPhotographyData on Ordpg_Number = Ord_Number";
                string StrColumnName = "Ord_SinceOrderNumber AS 订单号,"+
                "dbo.fn_CheckOrderType(Ord_Type) AS 订单类型,"+
                "Cus_Name AS 客户姓名,"+
                "Cus_Sex_cs AS 客户性别,"+
                "Cus_Telephone AS 客户电话,"+
                "Ord_OrderPersonName AS 接单人,"+
                "Ord_SeriesName AS 套系名称,"+
                "Ord_SeriesPrice AS 套系价格,"+
                "dbo.fn_CheckDateTime(Ord_CreateDateTime) AS 订单日期,"+
                //"stuff((select ','+ Ords_SightsName from tb_ErpOrderDigitalStatus where Ords_OrdNumber = Ord_Number for xml path('')),1,1,'') AS 拍摄名称,"+
                "Ords_SightsName as 拍摄名称," +
                "isnull(收款金额,0) AS 收款金额,(套系金额 + isnull(加挑金额,0) - isnull(收款金额,0)) AS 欠款 ";

                string sql = @"with   OrderPhotographyData as (

                                        select distinct

                                            Ordpg_Number,
							                STUFF(
                                                            (
                                                                  SELECT ',' + Ordpg_Sights
                                                                  FROM[dbo].tb_ErpOrdersPhotography
                                                                  WHERE Ordpg_Number = A.Ordpg_Number
                                                                  FOR XML PATH('')
                                                            )
                                                            ,1,1,''
                                                      )AS Ords_SightsName

                                        from
                                            tb_ErpOrdersPhotography as A
                                        group by Ordpg_Number
                                ) " + " select "+ StrColumnName + " from "+ StrTableName +" where "+ StrWhere;
                //dt = orbll.GetView_Custom(StrTableName, StrWhere: StrWhere, ShowColumnName: StrColumnName).Tables[0];
                dt = orbll.GetView_Custom(sql).Tables[0];
                if (dt.Rows.Count > 0)
                {
                    dt = dt.AsEnumerable().OrderByDescending(p => p.Field<string>("订单日期")).CopyToDataTable();
                }
                this.dgvData.DataSource(dt, strHideField: "");
                this.dgvData.Columns["客户电话"].Width = 180;
                this.dgvData.Columns["拍摄名称"].Width = 200;
                this.dgvData.Columns["订单日期"].Width = 110;
                this.dgvData.FillLastColumn(isAddNullColumns: true);

                StrStatisticalText = "总数量：" + dt.Rows.Count;
                DataRow[] dtRow1 = dt.Select("订单类型 = '婚纱订单'");
                if (dtRow1.Length > 0)
                { StrStatisticalText += "    婚纱订单：" + dtRow1.Length; }
                DataRow[] dtRow2 = dt.Select("订单类型 = '儿童订单'");
                if (dtRow2.Length > 0)
                { StrStatisticalText += "    儿童订单：" + dtRow2.Length; }
                DataRow[] dtRow3 = dt.Select("订单类型 = '写真订单'");
                if (dtRow3.Length > 0)
                { StrStatisticalText += "    写真订单：" + dtRow3.Length; }
               
                #endregion
            }
            else
            {
                #region 按订单收款统计
                StrWhere += "Pay_Type = '0'";
                StrWhere += LYFZ.Software.MainBusiness.DoorCityProcess.PublicCodeClasses.GetDateTime("Pay_CreateDatetime", this.dtDataTimeStart.DateValue.Trim(), this.dtDataTimeEnd.DateValue.Trim());
                if (this.cmbtreevperson.Text.Trim() != "全部")
                { StrWhere += " And Pay_OpenSingle like '%" + this.cmbtreevperson.Tag.ToString().Trim() + "%'"; }
                else
                { StrWhere += " And Pay_OpenSingle != ''"; }
                string StrTableName = "tb_ErpPayment Left Join dbo.tb_ErpOrder on Ord_Number = Pay_OrdNumber Left Join tempTB_AggregationCustomer on Ord_Number = GP_OrderNumber " +
                    "Left Join dbo.BView_OrderAmount on Order_Number = Ord_Number Left Join dbo.tb_ErpSystemCategory on Sc_ClassCode = Pay_PaymentMethod";
                string StrColumnName = "Ord_SinceOrderNumber AS 订单号," +
                    "dbo.fn_CheckOrderType(Ord_Type) AS 订单类型," +
                    "Cus_Name AS 客户姓名," +
                    "dbo.fn_CheckUserIDGetUserName(Pay_OpenSingle) AS 接单人," +
                    "Ord_SeriesName AS 套系名称," +
                    "Ord_SeriesPrice AS 套系价格," +
                    "dbo.fn_CheckDateTime(Ord_CreateDateTime) AS 订单日期," +
                    "dbo.fn_CheckDateTime(Pay_CreateDatetime) AS 收款时间," +
                    "Sc_ClassName AS 付款方式," +
                    "Pay_AmountOf AS 收款金额," +
                    "Pay_DiscountAmount AS 优惠金额," +
                    "(套系金额 + isnull(加挑金额,0) - isnull(收款金额,0)) AS 欠款," +
                    "case when Pay_Category = '后期收款' then '后期加挑' + (case when Pay_ShootingName != '' then '('+Pay_ShootingName+')' else Pay_ShootingName end) else Pay_Category end AS 收款类别," +
                    "(case (len(rtrim(ltrim(Pay_OpenSingle))) - len(rtrim(ltrim(replace(Pay_OpenSingle,',',''))))) " +
                    "	when 0 then (Pay_AmountOf - Pay_DiscountAmount) else " +
                    "	cast(((Pay_AmountOf - Pay_DiscountAmount) / ((select Cp_Proportion from tb_ErpCommissionPercentage where Cp_ProportionCode='MainStoreProportion') + (select Cp_Proportion from tb_ErpCommissionPercentage where Cp_ProportionCode='DeputyStoreProportion') * (len(rtrim(ltrim(Pay_OpenSingle))) - len(rtrim(ltrim(replace(Pay_OpenSingle,',','')))))) ) * (select Cp_Proportion from tb_ErpCommissionPercentage where Cp_ProportionCode='MainStoreProportion') as   numeric(9,2)) " +
                    " end) as '主门市金额' " +
                    ",(case (len(rtrim(ltrim(Pay_OpenSingle))) - len(rtrim(ltrim(replace(Pay_OpenSingle,',',''))))) " +
                    "	when 0 then 0 else " +
                    "	cast(((Pay_AmountOf - Pay_DiscountAmount) / ((select Cp_Proportion from tb_ErpCommissionPercentage where Cp_ProportionCode='MainStoreProportion') + (select Cp_Proportion from tb_ErpCommissionPercentage where Cp_ProportionCode='DeputyStoreProportion') * (len(rtrim(ltrim(Pay_OpenSingle))) - len(rtrim(ltrim(replace(Pay_OpenSingle,',','')))))) ) * (select Cp_Proportion from tb_ErpCommissionPercentage where Cp_ProportionCode='DeputyStoreProportion') as   numeric(9,2)) " +
                    " end) as '副门市金额'," +
                    "Pay_OpenSingle";
                dt = orbll.GetView_Custom(StrTableName, StrWhere: StrWhere, ShowColumnName: StrColumnName).Tables[0];
                if (dt.Rows.Count > 0)
                {
                    dt = dt.AsEnumerable().OrderByDescending(p => p.Field<string>("订单日期")).CopyToDataTable();
                }
                DataTable NewTable = new DataTable();
                for (int i = 0; i < dt.Columns.Count - 3; i++)
                { NewTable.Columns.Add(dt.Columns[i].ColumnName.Trim(), typeof(string)); }
                NewTable.Columns.Add("门市业绩", typeof(string));

                decimal TotalPerformance = 0;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow newRow = NewTable.NewRow();
                    for (int j = 0; j < dt.Columns.Count - 3; j++)
                    {
                        newRow[dt.Columns[j].ColumnName.Trim()] = dt.Rows[i][j].ToString().Trim();
                    }
                    string strPerformance = "0";
                    if (this.cmbtreevperson.Text.Trim() != "全部")
                    {
                        string[] StrArray = dt.Rows[i]["Pay_OpenSingle"].ToString().Trim().Split(',');
                        for (int j = 0; j < StrArray.Length; j++)
                        {
                            if (!string.IsNullOrEmpty(StrArray[j].Trim()))
                            {
                                if (StrArray[j].Trim() == this.cmbtreevperson.Tag.ToString().Trim())
                                {
                                    if (j == 0)
                                    { strPerformance = dt.Rows[i]["主门市金额"].ToString().Trim(); }
                                    else
                                    { strPerformance = dt.Rows[i]["副门市金额"].ToString().Trim(); }
                                }
                            }
                        }
                    }
                    else
                    { strPerformance = dt.Rows[i]["收款金额"].ToString().Trim(); }
                    newRow["门市业绩"] = strPerformance;
                    TotalPerformance += Convert.ToDecimal(strPerformance);
                    NewTable.Rows.Add(newRow);
                }

                this.dgvData.DataSource(NewTable, strHideField: "");
                this.dgvData.Columns["订单日期"].Width = 110;
                this.dgvData.Columns["收款时间"].Width = 110;
                this.dgvData.Columns["收款类别"].Width = 180;
                this.dgvData.FillLastColumn(isAddNullColumns: true);

                StrStatisticalText = "总业绩：" + TotalPerformance.ToString("0.00").Trim();
                
                #endregion
            }
            // 套系价格列求和;
            try
            {
                double sumObject = Convert.ToDouble(dt.Compute("sum(套系价格)", "true"));
                StrStatisticalText += string.Format("   套系总价:{0:N2},   套系平均价:{1:N2}", sumObject, sumObject / dt.Rows.Count);
            }
            catch { }

            this.txtStatisticalText.Text = StrStatisticalText;
        }
        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void btnExport_Click(object sender, EventArgs e)
        {
            this.dgvData.ExportDataTable(this.UcNavigationTool.LabTitle);
        }
        /// <summary>
        /// 关闭
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void btnClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}