lyfz_repos/铂金版源码/LYFZ.Software.MainBusiness/StatisticalInquiry/FrmStoredReport.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;

namespace LYFZ.Software.MainBusiness.StatisticalInquiry
{
    public class FrmStoredReport : Software.UI.StatisticalInquiry.FrmStoredReport
    {
        public bool IsQueryByEmployee { get; set; } = false;
        public string strEmployeeId { get; set; } = "";
        LYFZ.BLL.BLL_ErpMemberCard memberCardBll = new BLL.BLL_ErpMemberCard();

        public string startTime = "";
        public string endTime = "";
        public FrmStoredReport()
        {
            LYFZ.Software.MainBusiness.DoorCityProcess.PublicCodeClasses.BindGroupStoreName(this.cmbtreevStoreName, StrKeyCode: "qt002");
            this.btnQuery.Click += BtnQuery_Click;
            this.Load += FrmStoredReport_Load;
        }

        private void FrmStoredReport_Load(object sender, EventArgs e)
        {
            DataRow();
        }

        private void BtnQuery_Click(object sender, EventArgs e)
        {
            DataRow();
        }
        /// <summary>
        /// 绑定数据
        /// </summary>
        public void DataRow()
        {
            Dictionary<string, Dictionary<string, string>> tempDicList = new Dictionary<string, Dictionary<string, string>>();
            List<string> PayCategoryList = new List<string>();
            LYFZ.ComponentLibrary.FrmLoadHandling.LoadDoWorkMethod(delegate (object obj, BackgroundWorker backgroundWorker)
            {
                //string startTime = dtpStart.Value.ToString("yyyy-MM-dd");
                //string endTime = dtpEnd.Value.ToString("yyyy-MM-dd");
                DataTable storeData = new DataTable();
                if(!string.IsNullOrEmpty(startTime) && !string.IsNullOrEmpty(endTime))
                {
                    storeData = memberCardBll.GetStoredValueCardData(startTime, endTime,IsQueryByEmployee == true ? strEmployeeId : "");
                }
                else
                {
                    storeData = memberCardBll.GetStoredValueCardData(IsQueryByEmployee == true ? strEmployeeId : "");
                }
                
                DataTable totalStoreData = memberCardBll.GetTotalStoredValueCardData(IsQueryByEmployee == true ? strEmployeeId : "");
                Dictionary<string, Dictionary<string, string>> dicList = new Dictionary<string, Dictionary<string, string>>();
                for (int i = 0; i < storeData.Rows.Count; i++)
                {
                    DataRow dr = storeData.Rows[i];
                    Dictionary<string, string> tempColumDicList = new Dictionary<string, string>();
                    if (dicList.ContainsKey(dr["Mcacl_Number"].ToString()))
                    {
                        tempColumDicList = dicList[dr["Mcacl_Number"].ToString()];
                    }
                    else
                    {
                        dicList.Add(dr["Mcacl_Number"].ToString(), tempColumDicList);
                    }
                    BindData(dr, ref tempColumDicList);
                }

                for (int i = 0; i < totalStoreData.Rows.Count; i++)
                {
                    DataRow dr = totalStoreData.Rows[i];
                    Dictionary<string, string> tempColumDicList = new Dictionary<string, string>();
                    if (dicList.ContainsKey(dr["Mcacl_Number"].ToString()))
                    {
                        tempColumDicList = dicList[dr["Mcacl_Number"].ToString()];
                    }
                    else
                    {
                        dicList.Add(dr["Mcacl_Number"].ToString(), tempColumDicList);
                    }
                    BindTotalData(dr, ref tempColumDicList);
                }

                List<string> McNumber = new List<string>();
                foreach (string key in dicList.Keys)
                {
                    McNumber.Add(key);
                }

                string searchText = txt_MemberText.Text;
                if (!string.IsNullOrEmpty(searchText))
                {
                    searchText = " and (mc_CradNumber like '%" +
                                        searchText + "%' or cus_name like '%" +
                                        searchText + "%' or Cus_Telephone like '%" +
                                        searchText + "%') ";
                }
                else
                {
                    searchText = "";
                }

                searchText += LYFZ.Software.MainBusiness.DoorCityProcess.PublicCodeClasses.GetGroupQueryWhere(this.cmbtreevStoreName, "Mc_DividedShop");

                DataTable memberDataTable = memberCardBll.GetMemberCardDataValue(" Mc_Number in ('" + string.Join("','", McNumber.ToArray()) + "') " + searchText);
                DataTable memberCardTable = memberCardBll.GetMemberPaymentByCard(" Pay_McNumber in ('" + string.Join("','", McNumber.ToArray()) + "') ");
                ///分类
                PayCategoryList = memberCardTable.AsEnumerable().Where(p => !string.IsNullOrEmpty(p.Field<string>("twoClassName"))).Select(p => p.Field<string>("twoClassName")).Distinct().ToList();
                PayCategoryList.Insert(0, "订单前期");
                PayCategoryList.Add("礼服消费");

                for (int i = 0; i < memberDataTable.Rows.Count; i++)
                {
                    if (dicList.ContainsKey(memberDataTable.Rows[i]["Mc_Number"].ToString()))
                    {
                        tempDicList.Add(memberDataTable.Rows[i]["Mc_Number"].ToString(), dicList[memberDataTable.Rows[i]["Mc_Number"].ToString()]);
                        Dictionary<string, string> tempColumDicList = dicList[memberDataTable.Rows[i]["Mc_Number"].ToString()];
                        if (!tempColumDicList.ContainsKey("McNumber"))
                        {
                            tempColumDicList.Add("McNumber", memberDataTable.Rows[i]["Mc_CradNumber"].ToString());
                        }

                        if (!tempColumDicList.ContainsKey("McCardHolder"))
                        {
                            tempColumDicList.Add("McCardHolder", memberDataTable.Rows[i]["Cus_Name"].ToString());
                        }

                        if (!tempColumDicList.ContainsKey("McTelephone"))
                        {
                            tempColumDicList.Add("McTelephone", memberDataTable.Rows[i]["Cus_Telephone"].ToString());
                        }

                        if (!tempColumDicList.ContainsKey("McCardMoney"))
                        {
                            tempColumDicList.Add("McCardMoney", memberDataTable.Rows[i]["Mc_Money"].ToString());
                        }

                        if (!tempColumDicList.ContainsKey("McResidualStoredValue"))
                        {
                            if (Convert.ToDecimal(tempColumDicList["McTotalStoredValue"].ToString()) > 0)
                            {
                                if (tempColumDicList.ContainsKey("McTotalConsumption") && tempColumDicList.ContainsKey("McTotalStoredValue"))
                                {
                                    string strValue = ((-1 * Convert.ToDecimal(tempColumDicList["McTotalConsumption"].ToString()) /
                                                    Convert.ToDecimal(tempColumDicList["McTotalStoredValue"].ToString())) * 100).ToString("0.00") + "%";
                                    tempColumDicList.Add("McResidualStoredValue", strValue);
                                }
                                else
                                {
                                    tempColumDicList.Add("McResidualStoredValue", "");
                                }
                            }
                            else
                            {
                                tempColumDicList.Add("McResidualStoredValue", "");
                            }
                        }

                        foreach(string saleKey in PayCategoryList)
                        {
                            if (!tempColumDicList.ContainsKey(saleKey))
                            {
                                decimal totalAmount = 0;

                                switch (saleKey)
                                {
                                    case "订单前期":
                                        totalAmount = memberCardTable.AsEnumerable().Where(p =>
                                            p.Field<string>("Pay_McNumber") == memberDataTable.Rows[i]["Mc_Number"].ToString() &&
                                            string.IsNullOrEmpty(p.Field<string>("twoClassName")) && p.Field<string>("Pay_Category").IndexOf("后期") == -1)
                                        .Sum(p => p.Field<decimal>("Pay_AmountOf"));
                                        break;
                                    case "礼服消费":
                                        totalAmount = memberCardTable.AsEnumerable().Where(p =>
                                           p.Field<string>("Pay_McNumber") == memberDataTable.Rows[i]["Mc_Number"].ToString() &&
                                           string.IsNullOrEmpty(p.Field<string>("twoClassName")) &&
                                           p.Field<string>("Pay_Category").IndexOf("后期") != -1 &&
                                           p.Field<string>("Pay_ReceivableProject").IndexOf("礼服") > -1)
                                       .Sum(p => p.Field<decimal>("Pay_AmountOf"));

                                        break;
                                    default:
                                        totalAmount = memberCardTable.AsEnumerable().Where(p =>
                                            p.Field<string>("Pay_McNumber") == memberDataTable.Rows[i]["Mc_Number"].ToString() &&
                                            p.Field<string>("twoClassName") == saleKey).Sum(p => p.Field<decimal>("Pay_AmountOf")
                                        );
                                        break;
                                }

                                tempColumDicList.Add(saleKey, totalAmount.ToString());
                            }
                        }

                    }
                    //dicList[memberDataTable.Rows[i]["Mc_Number"].ToString()]
                }
            });
            BindGridControlData(tempDicList, PayCategoryList);
        }
        void BindGridControlData(Dictionary<string, Dictionary<string, string>> dicList,List<string> payCategoryList)
        {
            this.dgv.Rows.Clear();
            decimal mcmoney = 0;
            decimal recharge = 0;
            decimal giftAmount = 0;
            decimal withdrawals = 0;
            decimal consumption = 0;
            decimal totalStoredValue = 0;
            decimal totalConsumption = 0;

            foreach(string strKey in payCategoryList)
            {
                if(!dgv.Columns.Contains(strKey))
                {
                    System.Windows.Forms.DataGridViewTextBoxColumn viewColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
                    viewColumn.Name = strKey;
                    viewColumn.HeaderText = strKey;
                    dgv.Columns.Insert(dgv.Columns.Count - 2, viewColumn);
                }
            }

            foreach (string key in dicList.Keys)
            {
                Dictionary<string, string> columList = dicList[key];
                int rowIndex = this.dgv.Rows.Add();

                this.dgv.Rows[rowIndex].Cells["McCardMoney"].Value = 0;
                //this.dgv.Rows[rowIndex].Cells["McRecharge"].Value = 0;
                this.dgv.Rows[rowIndex].Cells["McGiftAmount"].Value = 0;
                //this.dgv.Rows[rowIndex].Cells["McWithdrawals"].Value = 0;
                //this.dgv.Rows[rowIndex].Cells["McConsumption"].Value = 0;
                this.dgv.Rows[rowIndex].Cells["McTotalStoredValue"].Value = 0;
                this.dgv.Rows[rowIndex].Cells["McTotalConsumption"].Value = 0;
                this.dgv.Rows[rowIndex].Tag = key;
                foreach (string columKey in columList.Keys)
                {
                    if (this.dgv.Columns.Contains(columKey))
                    {
                        this.dgv.Rows[rowIndex].Cells[columKey].Value = columList[columKey];
                    }
                    if (columKey.Equals("McCardMoney"))
                    {
                        mcmoney += Convert.ToDecimal(columList[columKey]);
                    }
                    if (columKey.Equals("McRecharge"))
                    {
                        recharge += Convert.ToDecimal(columList[columKey]);
                    }
                    if (columKey.Equals("McGiftAmount"))
                    {
                        giftAmount += Convert.ToDecimal(columList[columKey]);
                    }
                    if (columKey.Equals("McWithdrawals"))
                    {
                        withdrawals += Convert.ToDecimal(columList[columKey]);
                    }
                    if (columKey.Equals("McConsumption"))
                    {
                        consumption += Convert.ToDecimal(columList[columKey]);
                    }
                    if (columKey.Equals("McTotalStoredValue"))
                    {
                        totalStoredValue += Convert.ToDecimal(columList[columKey]);
                    }
                    if (columKey.Equals("McTotalConsumption"))
                    {
                        totalConsumption += Convert.ToDecimal(columList[columKey]);
                    }
                }


            }

            lab_Message.Text = "  余额：" + mcmoney + "  充值金额：" + recharge + "  赠送金额：" + giftAmount +
                               //"  提现金额：" + withdrawals * -1 + 
                               "  已消费金额：" + consumption * -1 +
                               "  累计充值金额：" + totalStoredValue + "  累计消费金额：" + totalConsumption * -1;
        }
        /// <summary>
        /// 绑定表格数据
        /// </summary>
        /// <param name="dr">数据</param>
        /// <param name="tempColumDicList"></param>
        void BindData(DataRow dr, ref Dictionary<string, string> tempColumDicList)
        {
            string keyData = dr["类型"].ToString();
            decimal valueData = 0;
            string giftData = "McGiftAmount";//"McGiftAmount";
            decimal giftValueData = 0;
            switch (keyData)
            {
                case "充值":
                    keyData = "McRecharge";
                    valueData = Convert.ToDecimal(dr["操作金额"].ToString());
                    giftValueData = Convert.ToDecimal(dr["赠送金额"].ToString());
                    break;
                case "提现":
                    keyData = "McWithdrawals";
                    valueData = -1 * Convert.ToDecimal(dr["操作金额"].ToString());
                    giftValueData = Convert.ToDecimal(dr["赠送金额"].ToString());
                    break;
                case "取现":
                    keyData = "McWithdrawals";
                    valueData = -1 * Convert.ToDecimal(dr["操作金额"].ToString());
                    giftValueData = Convert.ToDecimal(dr["赠送金额"].ToString());
                    break;
                case "删除收款扣回":
                    keyData = "McConsumption";
                    valueData = Convert.ToDecimal(dr["操作金额"].ToString());
                    giftValueData = Convert.ToDecimal(dr["赠送金额"].ToString());
                    break;
                case "订单消费":
                    keyData = "McConsumption";
                    valueData = -1 * Convert.ToDecimal(dr["操作金额"].ToString());
                    giftValueData = Convert.ToDecimal(dr["赠送金额"].ToString());
                    break;
                case "服务消费":
                    keyData = "McConsumption";
                    valueData = -1 * Convert.ToDecimal(dr["操作金额"].ToString());
                    giftValueData = Convert.ToDecimal(dr["赠送金额"].ToString());
                    break;
                case "充值错误扣回":
                    keyData = "McRecharge";
                    valueData = -1 * Convert.ToDecimal(dr["操作金额"].ToString());
                    giftValueData = -1 * Convert.ToDecimal(dr["赠送金额"].ToString());
                    break;
                case "送金额":
                    keyData = "McRecharge";
                    giftValueData = Convert.ToDecimal(dr["操作金额"].ToString());
                    break;
                case "删除储值提现扣回":
                    keyData = "McWithdrawals";
                    valueData = Convert.ToDecimal(dr["操作金额"].ToString());
                    giftValueData = Convert.ToDecimal(dr["赠送金额"].ToString());
                    break;
            }
            ///
            if (tempColumDicList.ContainsKey(keyData))
            {
                tempColumDicList[keyData] = (Convert.ToDecimal(tempColumDicList[keyData]) + Convert.ToDecimal(valueData)).ToString("0.00");
            }
            else
            {
                tempColumDicList.Add(keyData, valueData.ToString("0.00"));
            }

            if (tempColumDicList.ContainsKey(giftData))
            {
                tempColumDicList[giftData] = (Convert.ToDecimal(tempColumDicList[giftData]) + Convert.ToDecimal(giftValueData)).ToString("0.00");
            }
            else
            {
                tempColumDicList.Add(giftData, giftValueData.ToString("0.00"));
            }
        }
        /// <summary>
        /// 绑定表格数据
        /// </summary>
        /// <param name="dr">数据</param>
        /// <param name="tempColumDicList"></param>
        void BindTotalData(DataRow dr, ref Dictionary<string, string> tempColumDicList)
        {
            string keyData = dr["类型"].ToString();
            decimal valueData = 0;
            string giftData = "McTotalStoredValue";// "McTotalGift";
            decimal giftValueData = 0;
            switch (keyData)
            {
                case "充值":
                    keyData = "McTotalStoredValue";
                    valueData = Convert.ToDecimal(dr["操作金额"].ToString());
                    giftValueData = Convert.ToDecimal(dr["赠送金额"].ToString());
                    break;
                case "提现":
                    //keyData = "McTotalWithdrawals";
                    keyData = "McTotalConsumption";
                    valueData = -1 * Convert.ToDecimal(dr["操作金额"].ToString());
                    giftValueData = Convert.ToDecimal(dr["赠送金额"].ToString());
                    break;
                case "取现":
                    //keyData = "McTotalWithdrawals";
                    keyData = "McTotalConsumption";
                    valueData = -1 * Convert.ToDecimal(dr["操作金额"].ToString());
                    giftValueData = Convert.ToDecimal(dr["赠送金额"].ToString());
                    break;
                case "删除收款扣回":
                    keyData = "McTotalConsumption";
                    valueData = Convert.ToDecimal(dr["操作金额"].ToString());
                    giftValueData = Convert.ToDecimal(dr["赠送金额"].ToString());
                    break;
                case "订单消费":
                    keyData = "McTotalConsumption";
                    valueData = -1 * Convert.ToDecimal(dr["操作金额"].ToString());
                    giftValueData = Convert.ToDecimal(dr["赠送金额"].ToString());
                    break;
                case "服务消费":
                    keyData = "McTotalConsumption";
                    valueData = -1 * Convert.ToDecimal(dr["操作金额"].ToString());
                    giftValueData = Convert.ToDecimal(dr["赠送金额"].ToString());
                    break;
                case "充值错误扣回":
                    keyData = "McTotalStoredValue";
                    valueData = -1 * Convert.ToDecimal(dr["操作金额"].ToString());
                    giftValueData = -1 * Convert.ToDecimal(dr["赠送金额"].ToString());
                    break;
                case "送金额":
                    //keyData = "McTotalGift";
                    keyData = "McTotalStoredValue";
                    giftValueData = Convert.ToDecimal(dr["赠送金额"].ToString());
                    break;
                case "删除储值提现扣回":
                    keyData = "McTotalConsumption";
                    valueData = Convert.ToDecimal(dr["操作金额"].ToString());
                    giftValueData = Convert.ToDecimal(dr["赠送金额"].ToString());
                    break;
            }
            ///
            if (tempColumDicList.ContainsKey(keyData))
            {
                tempColumDicList[keyData] = (Convert.ToDecimal(tempColumDicList[keyData]) + Convert.ToDecimal(valueData)).ToString("0.00");
            }
            else
            {
                tempColumDicList.Add(keyData, valueData.ToString("0.00"));
            }

            if (tempColumDicList.ContainsKey(giftData))
            {
                tempColumDicList[giftData] = (Convert.ToDecimal(tempColumDicList[giftData]) + Convert.ToDecimal(giftValueData)).ToString("0.00");
            }
            else
            {
                tempColumDicList.Add(giftData, giftValueData.ToString("0.00"));
            }
        }

    }
}