salelistctrl.ascx.cs

CRM管理系统 CRM管理系统

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Contact_Ctrls_SaleListCtrl : PageBaseUserCtrl
{
    public WYX.Dataport.Dataport dp = new WYX.Dataport.Dataport();
    public string strWhere = "";//条件
    private int PageSize = 15;

    public bool IsEdit
    {
        get { return _IsEdit; }
        set
        {
            isEdit = value ? "true" : "";
            _IsEdit = value;
        }
    }
    private string isEdit;
    private bool _IsEdit;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (CurrentUser == null)
            return;

        ddlDatetime.Attributes.Add("onchange", "SetSearchTime();");
        if (!IsPostBack)
        {
            SetData();
        }
    }

    #region 初始化页面
    /// <summary>
    /// 初始化页面
    /// </summary>
    private void SetData()
    {
        BindWin();
        BindProject();
        BindPerson();
        BindCustomer();
        ddlDatetime.SelectedIndex = 2;

        SetPagerAndGvConList();
    }

    /// <summary>
    /// 设置Pager 和 GridView
    /// </summary>
    private void SetPagerAndGvConList()
    {
        strWhere = GetSqlWhere();
        this.gvConList.PageSize = PageSize;
        BindGvConList();
    }
    #endregion

    #region 读取DropDownList数据源
    /// <summary>
    /// 邦定赢面
    /// </summary>
    private void BindWin()
    {
        this.BindDictionary(ddlWin, "113");
        ddlWin.Items.Insert(0, "--赢面--");
    }
        
    /// <summary>
    /// 邦定项目
    /// </summary>
    private void BindProject()
    {
        //this.BindDictionary(ddlProject, "103");
        string sql = "SELECT * FROM dictionary where parent_id = 103 order by dict_name";
        DataTable dt = null;
        try
        {
            dt = dp.GetTableResult(sql);
        }
        catch
        {
            dt = null;
        }
        if (dt == null || dt.Rows.Count == 0)
        {
            this.ddlProject.Items.Insert(0, new ListItem("--相关项目--"));
            return;
        }

        this.ddlProject.DataSource = dt;
        this.ddlProject.DataTextField = "dict_name";
        this.ddlProject.DataValueField = "dict_code";
        this.ddlProject.DataBind();
        ddlProject.Items.Insert(0, "--相关项目--");
    }

    /// <summary>
    /// 邦定客户
    /// </summary>
    private void BindCustomer()
    {
        string sql = "SELECT code, name FROM ITSV_CustomerManager where is_delete = 0 order by name";
        DataTable dt = null;
        try
        {
            dt = dp.GetTableResult(sql);
        }
        catch
        {
            dt = null;
        }
        if (dt == null || dt.Rows.Count == 0)
        {
            this.ddlCustomer.Items.Insert(0, new ListItem("--客户名称--"));
            return;
        }

        this.ddlCustomer.DataSource = dt;
        this.ddlCustomer.DataTextField = "name";
        this.ddlCustomer.DataValueField = "code";
        this.ddlCustomer.DataBind();
        this.ddlCustomer.Items.Insert(0, new ListItem("--客户名称--"));
    }

    /// <summary>
    /// 邦定我方人员
    /// </summary>
    private void BindPerson()
    {
        string sql = "SELECT account, user_id, name FROM Users WHERE (inuse = 1) and account = '" + CurrentUser.account + "'";
        if (this.GetSystemRole(CurrentUser.account) || !IsEdit)
        {
            sql = "SELECT account, user_id, name FROM Users WHERE (inuse = 1)";
        }

        DataTable dt = null;
        try
        {
            dt = dp.GetTableResult(sql);
        }
        catch
        {
            dt = null;
        }
        if (dt == null || dt.Rows.Count == 0)
        {
            this.ddlUser.Items.Insert(0, new ListItem("--我方人员--"));
            return;
        }

        this.ddlUser.DataSource = dt;
        this.ddlUser.DataTextField = "name";
        this.ddlUser.DataValueField = "user_id";
        this.ddlUser.DataBind();
        this.ddlUser.Items.Insert(0, new ListItem("--我方人员--"));
    }
    #endregion

    #region 查询条件
    /// <summary>
    /// 查询条件
    /// </summary>
    /// <returns></returns>
    private string GetSqlWhere()
    {
        System.Text.StringBuilder sbWhere = new System.Text.StringBuilder();
        if (GetSystemRole(CurrentUser.account) || !_IsEdit)
            sbWhere.Append(@" Sale.code in (select max(a.code) as code  from ITSV_sale a inner join ITSV_CustomerContract  b on a.contract_id = b.code group by b.project)");
        else
            sbWhere.Append(@" Sale.code in (select max(a.code) as code  from ITSV_sale a inner join ITSV_CustomerContract  b on a.contract_id = b.code where b.contract_person = " + CurrentUser.user_id + " group by b.project)");
        
        if (ddlWin.SelectedItem != null && ddlWin.SelectedIndex > 0)
        {
            sbWhere.Append(" and Sale.Win = " + ddlWin.SelectedValue + "");
        }

        if (ddlProject.SelectedItem != null && ddlProject.SelectedIndex > 0)
        {
            sbWhere.Append(" and con.project = '" + ddlProject.SelectedValue + "'");
        }

        if (ddlCustomer.SelectedItem != null && ddlCustomer.SelectedIndex > 0)
        {
            sbWhere.Append(" and con.customer_id = '" + ddlCustomer.SelectedValue + "'");
        }

        if (ddlUser.SelectedItem != null && ddlUser.SelectedIndex > 0)
        {
            sbWhere.Append(" and users.user_id = " + ddlUser.SelectedValue + "");
        }
        else
        {
            sbWhere.Append(" and users.user_id in (");
            for (int i = 1; i < ddlUser.Items.Count; i++)
            {
                if (i == ddlUser.Items.Count - 1)
                {
                    ListItem item = ddlUser.Items[i];
                    sbWhere.Append(item.Value);
                }
                else
                {
                    ListItem item = ddlUser.Items[i];
                    sbWhere.Append(item.Value + ",");
                }
            }
            sbWhere.Append(")");
        }

        if (txtBegin.Text.Trim().Length > 0 && IsDate(txtBegin.Text.Trim()))
        {
            sbWhere.Append(" and sale.date_time  >= '" + txtBegin.Text.Trim() + "'");
        }

        if (txtEnd.Text.Trim().Length > 0 && IsDate(txtEnd.Text.Trim()))
        {
            sbWhere.Append(" and sale.date_time  < '" + DateTime.Parse(txtEnd.Text.Trim()).AddDays(1).ToShortDateString() + "'");
        }

        if (ddlOperator.SelectedItem != null && IsNuberic(txtMoney.Text.Trim()))
        {
            sbWhere.Append(" and sale.total_sale_money " + ddlOperator.SelectedValue + " '" + txtMoney.Text.Trim() + "'");
        }

        return sbWhere.ToString();
    }
    #endregion

    #region 数据读取
    /// <summary>
    /// 金额统计
    /// </summary>
    /// <param name="sqlWhere"></param>
    /// <returns></returns>
    private DataRow GetSumMoney(string sqlWhere)
    {
        string sql = @"SELECT 
                    isnull(sum(sale.total_sale_money),0),isnull(sum(sale.other_sale_money),0)
                    FROM ITSV_sale sale
                    left join ITSV_CustomerContract con on sale.Contract_id = con.code
                    left join ITSV_CustomerManager customer on con.customer_id = customer.code
                    left join users on con.contract_person = users.user_id
                    left join dictionary dict1 on con.project = dict1.dict_code
                    left join dictionary dict2 on sale.win = dict2.dict_code";
        sql += " Where " + sqlWhere;
        DataRow row = null;
        try { row = dp.GetRowResult(sql); }
        catch { row = null; }
        return row;
    }

    /// <summary>
    /// 读取本项目销售预测的次数
    /// </summary>
    /// <param name="project"></param>
    /// <returns></returns>
    private int GetSaleCount(int project)
    {
        string sql = @"SELECT count(*)
                    FROM ITSV_sale s
                    left join ITSV_CustomerContract c on s.contract_id = c.code
                    where project = " + project;
        string count = dp.GetRowResultID(sql);
        if (count != null && IsInt(count))
            return int.Parse(count);
        else
            return 0;
    }
    #endregion

    #region 邦定Gridview
    /// <summary>
    /// 邦定Gridview
    /// </summary>
    private void BindGvConList()
    {
        string sql = @"SELECT 
                    sale.code,sale.win,sale.win_data,sale.date_time,sale.total_sale_money,sale.other_sale_money,
                    con.customer_id,con.customer_person_id,con.title,con.project,con.contract_person,
                    customer.name as customerName,
                    users.name as UserName,
                    dict1.dict_name as projectName,dict2.dict_name as winName
                    FROM ITSV_sale sale
                    left join ITSV_CustomerContract con on sale.Contract_id = con.code
                    left join ITSV_CustomerManager customer on con.customer_id = customer.code
                    left join users on con.contract_person = users.user_id
                    left join dictionary dict1 on con.project = dict1.dict_code
                    left join dictionary dict2 on sale.win = dict2.dict_code ";
        
        sql += " Where " + strWhere;
        sql += " Order by sale.code desc";

        DataSet ds = null;
        try { ds = dp.GetDataSet(sql); }
        catch { ds = null; }

        if (ds == null)
        {
            return;
        }
        DataTable dt = ds.Tables[0];
        ViewState["dvcount"] = dt.Rows.Count;
        if (dt.Rows.Count == 0)
        {
            dt = GvAddEmptyRow(dt);
            gvConList.DataSource = dt;
            gvConList.DataBind();
            return;
        }

        gvConList.DataSource = dt;
        gvConList.DataKeyNames = new string[2] { "code","project" };
        gvConList.DataBind();
    }

    /// <summary>
    /// 增加一个空行来显示表头
    /// </summary>
    /// <param name="ds"></param>
    /// <returns></returns>
    private DataTable GvAddEmptyRow(DataTable dt)
    {
        DataRow newRow = dt.NewRow();
        dt.Rows.Add(newRow);
        return dt;
    }
    #endregion

    #region 事件
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        SetPagerAndGvConList();
    }
    protected void gvConList_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        this.gvConList.PageIndex = e.NewPageIndex;
        SetPagerAndGvConList();
    }
    protected void gvConList_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            //当鼠标停留时更改背景色
            e.Row.Attributes.Add("onmouseover", "c=this.style.backgroundColor;this.style.backgroundColor='#ededFF'");
            //当鼠标移开时还原背景色
            e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor=c");

            int code = 0;
            int project = 0;
            LinkButton lbtnTitle = (LinkButton)e.Row.FindControl("lbtnTitle");
            LinkButton lbtnOldData = (LinkButton)e.Row.FindControl("lbtnOldData");
            Label lblSaleCount = (Label)e.Row.FindControl("lblSaleCount");

            try
            {
                code = Convert.ToInt32(gvConList.DataKeys[e.Row.RowIndex].Values[0].ToString());
                project = Convert.ToInt32(gvConList.DataKeys[e.Row.RowIndex].Values[1].ToString());
            }
            catch
            {
                lbtnTitle.Visible = false;
                lbtnOldData.Visible = false;
                return;
            }

            this.ShowModalWindow(lbtnTitle, "onclick", "SaleShow.aspx?isedit=" + isEdit + "&code=" + code, 750, 600);
            lbtnOldData.Attributes.Add("onclick", "window.open('SaleExportByProject.aspx?project=" + project + "','_black','width=750,height=500,scrollbars=yes');");
            if (lblSaleCount != null)
            {
                int count = GetSaleCount(project);
                lblSaleCount.Text = "(" + count.ToString() + ")";
            }
        }

        if (e.Row.RowType == DataControlRowType.Pager)
        {
            TextBox tb = (TextBox)e.Row.FindControl("txtPages");
            tb.Text = (gvConList.PageIndex + 1) + "";
            int count = (int)ViewState["dvcount"];
            Label lb = (Label)e.Row.FindControl("lbCount");
            lb.Text = count.ToString();
        }
        if (e.Row.RowType == DataControlRowType.Footer)
        {
            e.Row.Cells[0].ColumnSpan = gvConList.Columns.Count;
            for (int i = 1; i < e.Row.Cells.Count; i++)
            {
                e.Row.Cells[i].Visible = false;
            }

            DataRow row = GetSumMoney(strWhere);
            if (row != null)
            {
                Label label1 = (Label)e.Row.FindControl("lblMoney1");
                Label label2 = (Label)e.Row.FindControl("lblMoney2");
                if (label1 != null)
                    label1.Text = string.Format("{0:c}",row[0]);
                if (label2 != null)
                    label2.Text = string.Format("{0:c}",row[1]);
            }
        }
    }
    protected void gvConList_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "Click")
        {
            TextBox tb = (TextBox)gvConList.BottomPagerRow.FindControl("txtPages");
            if (tb == null || tb.Text.Trim().Length == 0 || !IsInt(tb.Text.Trim()))
            {
                return;
            }

            int page = Convert.ToInt32(tb.Text.Trim());

            if (page > gvConList.PageCount)
            {
                page = gvConList.PageCount;
            }
            if (page < 1)
            {
                page = 1;
            }

            this.gvConList.PageIndex = page - 1;
            SetPagerAndGvConList();
        }
    }
    #endregion

    protected void btnClear_Click(object sender, EventArgs e)
    {
        this.txtMoney.Text = "";
        this.ddlCustomer.SelectedIndex = 0;
        this.ddlDatetime.SelectedIndex = 2;
        this.ddlProject.SelectedIndex = 0;
        this.ddlUser.SelectedIndex = 0;
        this.ddlWin.SelectedIndex = 0;
        txtBegin.Text = "";
        txtEnd.Text = "";
        SetPagerAndGvConList();
    }
}