lyfz_repos/铂金版源码/LYFZ.Software.MainBusiness/StatisticalInquiry/FrmArrearsOfDayReport.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.StatisticalInquiry
{
    public partial class FrmArrearsOfDayReport : LYFZ.Software.UI.StatisticalInquiry.FrmArrearsOfDayReport
    {
        LYFZ.BLL.BLL_ErpOrder orderBll = new BLL.BLL_ErpOrder();
        public FrmArrearsOfDayReport()
        {
            this.btn_Search.Click+=btn_Search_Click;
        }
        public void btn_Search_Click(object sender, EventArgs e)
        {
            BindDataRow();
        }

        public void BindDataRow()
        {
            string sql = @"select t1.*  from (select sum(ord_seriesprice) as 业绩款,Cus_Name as 客户名称,Cus_Telephone as 联系电话,Company_Name as 门店名称,ord_number 订单号,Ord_CreateDateTime as 时间,付款, (sum(ord_seriesprice)-付款) as 欠款 from
                         (SELECT     CASE WHEN (Ord_SeriesPrice) IS NULL THEN 0 ELSE (Ord_SeriesPrice) END AS Ord_SeriesPrice,Ord_DividedShop ,tb_ErpOrder.Ord_Number,convert(datetime,Ord_CreateDateTime) as Ord_CreateDateTime,
                           isnull(( SELECT     sum(CASE WHEN (Pay_AmountOf) IS NULL THEN 0 ELSE (Pay_AmountOf) END) AS Pay_AmountOf
                                                      FROM          dbo.tb_ErpPayment where Pay_DividedShop= Ord_DividedShop and Pay_OrdNumber=Ord_Number
                                                                      and  Pay_CreateDatetime >='" + DateTime.Now.ToString("yyyy-MM-dd") + @" 00:00:00' and Pay_CreateDatetime <='" + DateTime.Now.ToString("yyyy-MM-dd") + @" 23:59:59' ),0) as 付款
                                                 FROM         dbo.tb_ErpOrder
                                                 WHERE     (Ord_Class = '1') 
                         union                       
                  
                         SELECT     CASE WHEN (Plu_Amount) IS NULL THEN 0  else(Plu_Amount) END AS Plu_Amount,Plu_DividedShop,Plu_OrdNumber,convert(datetime,Plu_CreateTime) as Ord_CreateDateTime,
                            isnull(( SELECT     sum( CASE WHEN (Pay_AmountOf) IS NULL THEN 0 ELSE (Pay_AmountOf) END) AS Pay_AmountOf
                                                      FROM          dbo.tb_ErpPayment where Pay_DividedShop= Plu_DividedShop and Pay_OrdNumber=Plu_OrdNumber
                                                                      and  Pay_CreateDatetime >='" + DateTime.Now.ToString("yyyy-MM-dd") + @" 00:00:00' and Pay_CreateDatetime <='" + DateTime.Now.ToString("yyyy-MM-dd") + @" 23:59:59' ),0) as 付款
                                                      FROM          dbo.tb_ErpPlusPickItems   
                         union      
                         SELECT     CASE WHEN (Tsorder_Money) IS NULL THEN 0 ELSE (Tsorder_Money) END AS Tsorder_Money,Tsorder_DividedShop ,Tsorder_Name,convert(datetime,Tsorder_CreateDatetime) as Ord_CreateDateTime,
                         isnull(( SELECT     sum(CASE WHEN (Pay_AmountOf) IS NULL THEN 0 ELSE (Pay_AmountOf) END) AS Pay_AmountOf
                                                      FROM          dbo.tb_ErpPayment where Pay_DividedShop= Tsorder_DividedShop and Pay_OrdNumber=Tsorder_Number
                                                                        and  Pay_CreateDatetime >='" + DateTime.Now.ToString("yyyy-MM-dd") + @" 00:00:00' and Pay_CreateDatetime <='" + DateTime.Now.ToString("yyyy-MM-dd") + @" 23:59:59' ),0) as 付款
                                                      FROM          dbo.tb_ErpTwoSalesOrder
                         union                                 
                         SELECT     CASE WHEN (Dsro_Amount) IS NULL THEN 0 ELSE (Dsro_Amount) END AS Dsro_Amount,Dsro_DividedShop,Dsro_Number,Dsro_CreateDateTime,
                         isnull(( SELECT     sum(CASE WHEN (Pay_AmountOf) IS NULL THEN 0 ELSE (Pay_AmountOf) END) AS Pay_AmountOf
                                                      FROM          dbo.tb_ErpPayment where Pay_DividedShop= Dsro_DividedShop and Pay_OrdNumber=Dsro_Number
                                                                    and  Pay_CreateDatetime >='" + DateTime.Now.ToString("yyyy-MM-dd") + @" 00:00:00' and Pay_CreateDatetime <='" + DateTime.Now.ToString("yyyy-MM-dd") + @" 23:59:59' ),0) as 付款
                                                      FROM          dbo.tb_ErpDressSaleRentalOrder
                         ) as t inner join tempTB_AggregationCustomer  on t.Ord_Number = GP_OrderNumber inner join tb_ErpCompanyInfo on Ord_DividedShop = Company_DividedShop
                         group by ord_dividedshop,ord_number,Ord_CreateDateTime,Cus_Name,Cus_Telephone, Company_Name ,付款
                          ) as t1   ";
            sql += " where 时间 >= '" + DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00'  and 时间 <= '" + DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59' and t1.欠款>0 ";
          

            DataTable dtBll = orderBll.GetView_Custom(sql).Tables[0];

            foreach(DataRow dr in dtBll.Rows)
            {
                int rowIndex = this.dgv.Rows.Add();
                foreach (DataColumn columName in dtBll.Columns)
                {
                    this.dgv.Rows[rowIndex].Cells[columName.ColumnName].Value = dr[columName.ColumnName].ToString();
                }
            }
        }

        public void BindDataRow2(List<string> numbers)
        {
            if (numbers.Count > 0)
            {
                string sql = @"select t1.*  from (select sum(ord_seriesprice) as 业绩款,Cus_Name as 客户名称,Cus_Telephone as 联系电话,Company_Name as 门店名称,ord_dividedshop 门店编号,ord_number 订单号,Ord_CreateDateTime as 时间,付款, (sum(ord_seriesprice)-付款) as 欠款 from
                         (SELECT     CASE WHEN (Ord_SeriesPrice) IS NULL THEN 0 ELSE (Ord_SeriesPrice) END AS Ord_SeriesPrice,Ord_DividedShop ,tb_ErpOrder.Ord_Number,convert(datetime,Ord_CreateDateTime) as Ord_CreateDateTime,
                           isnull(( SELECT     sum(CASE WHEN (Pay_AmountOf) IS NULL THEN 0 ELSE (Pay_AmountOf) END) AS Pay_AmountOf
                                                      FROM          dbo.tb_ErpPayment where Pay_DividedShop= Ord_DividedShop and Pay_OrdNumber=Ord_Number
                                                                      and  Pay_CreateDatetime >='" + DateTime.Now.ToString("yyyy-MM-dd") + @" 00:00:00' and Pay_CreateDatetime <='" + DateTime.Now.ToString("yyyy-MM-dd") + @" 23:59:59' ),0) as 付款
                                                 FROM         dbo.tb_ErpOrder
                                                 WHERE     (Ord_Class = '1') 
                         union                       
                  
                         SELECT     CASE WHEN (Plu_Amount) IS NULL THEN 0  else(Plu_Amount) END AS Plu_Amount,Plu_DividedShop,Plu_OrdNumber,convert(datetime,Plu_CreateTime) as Ord_CreateDateTime,
                            isnull(( SELECT     sum( CASE WHEN (Pay_AmountOf) IS NULL THEN 0 ELSE (Pay_AmountOf) END) AS Pay_AmountOf
                                                      FROM          dbo.tb_ErpPayment where Pay_DividedShop= Plu_DividedShop and Pay_OrdNumber=Plu_OrdNumber
                                                                      and  Pay_CreateDatetime >='" + DateTime.Now.ToString("yyyy-MM-dd") + @" 00:00:00' and Pay_CreateDatetime <='" + DateTime.Now.ToString("yyyy-MM-dd") + @" 23:59:59' ),0) as 付款
                                                      FROM          dbo.tb_ErpPlusPickItems   
                         union      
                         SELECT     CASE WHEN (Tsorder_Money) IS NULL THEN 0 ELSE (Tsorder_Money) END AS Tsorder_Money,Tsorder_DividedShop ,Tsorder_Name,convert(datetime,Tsorder_CreateDatetime) as Ord_CreateDateTime,
                         isnull(( SELECT     sum(CASE WHEN (Pay_AmountOf) IS NULL THEN 0 ELSE (Pay_AmountOf) END) AS Pay_AmountOf
                                                      FROM          dbo.tb_ErpPayment where Pay_DividedShop= Tsorder_DividedShop and Pay_OrdNumber=Tsorder_Number
                                                                        and  Pay_CreateDatetime >='" + DateTime.Now.ToString("yyyy-MM-dd") + @" 00:00:00' and Pay_CreateDatetime <='" + DateTime.Now.ToString("yyyy-MM-dd") + @" 23:59:59' ),0) as 付款
                                                      FROM          dbo.tb_ErpTwoSalesOrder
                         union                                 
                         SELECT     CASE WHEN (Dsro_Amount) IS NULL THEN 0 ELSE (Dsro_Amount) END AS Dsro_Amount,Dsro_DividedShop,Dsro_Number,Dsro_CreateDateTime,
                         isnull(( SELECT     sum(CASE WHEN (Pay_AmountOf) IS NULL THEN 0 ELSE (Pay_AmountOf) END) AS Pay_AmountOf
                                                      FROM          dbo.tb_ErpPayment where Pay_DividedShop= Dsro_DividedShop and Pay_OrdNumber=Dsro_Number
                                                                    and  Pay_CreateDatetime >='" + DateTime.Now.ToString("yyyy-MM-dd") + @" 00:00:00' and Pay_CreateDatetime <='" + DateTime.Now.ToString("yyyy-MM-dd") + @" 23:59:59' ),0) as 付款
                                                      FROM          dbo.tb_ErpDressSaleRentalOrder
                         ) as t inner join tempTB_AggregationCustomer  on t.Ord_Number = GP_OrderNumber inner join tb_ErpCompanyInfo on Ord_DividedShop = Company_DividedShop
                         group by ord_dividedshop,ord_number,Ord_CreateDateTime,Cus_Name,Cus_Telephone, Company_Name ,付款
                          ) as t1   ";
                sql += " where 订单号 in ('" + string.Join("','", numbers.ToArray()) + "')";


                DataTable dtBll = orderBll.GetView_Custom(sql).Tables[0];

                foreach (DataRow dr in dtBll.Rows)
                {
                    int rowIndex = this.dgv.Rows.Add();
                    foreach (DataColumn columName in dtBll.Columns)
                    {
                        this.dgv.Rows[rowIndex].Cells[columName.ColumnName].Value = dr[columName.ColumnName].ToString();
                    }
                }
            }
        }
    }
}