DS7/DSWeb/ParameterSet/ExchangeRateListGridSource.aspx.cs

using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Text;
using DSWeb.EntityDA;
using DSWeb.Models;
using System.Xml;
using System.Text.RegularExpressions;

namespace DSWeb.FeeCodes
{
    public partial class ExchangeRateListGridSource : System.Web.UI.Page
    {
        //private int iCurrentPage;
        //private int iShowPage;
        //private string strHandle;//值为"page"获取分页状态值数组 值为"list"获取日志显示JSON数据 值为"columns"获取日志显示记录 值为"rename"更新列标题
        //public string strUserID = "";//"04eaeac5-f271-45d1-b17b-ad4ccbab63d0";//用户GID
        //private string strType = "10";//Grid模块号
        //private string strOldHeader;
        //private string strNewHeader;

        private string strHandle;//操作方式
        private string strCondition;//条件查询
        private string strUserID;
        private string strCompanyID;//公司GID
        private string strShowName;//用户显示名
        private string strDeptName;//部门名称
        private int iCurrentPage;//当前页数
        private int iShowPage;//显示最大页数
        private string strSearch;
        private string strExchangeRateID;//汇率GID

        protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["USERID"] != null)
            {
                strUserID = Session["USERID"].ToString();
            }
            if (Session["SHOWNAME"] != null)
            {
                strShowName = Session["SHOWNAME"].ToString();
            }
            if (Session["COMPANYID"] != null)
            {
                strCompanyID = Session["COMPANYID"].ToString();
            }
            if (Session["DEPTNAME"] != null)
            {
                strDeptName = Session["DEPTNAME"].ToString();
            }

            if (Request.QueryString["handle"] != null)
            {
                strHandle = Request.QueryString["handle"].ToString().Trim().ToLower();
            }

            if (Request.QueryString["condition"] != null)
            {
                strCondition = Request.QueryString["condition"].ToString();
            }

            if (Request.QueryString["cur_page"] != null)
            {
                iCurrentPage = int.Parse(Request.QueryString["cur_page"].ToString().Trim());
            }
            else
            {
                iCurrentPage = 0;
            }
            if (Request.QueryString["show_page"] != null)
            {
                iShowPage = int.Parse(Request.QueryString["show_page"].ToString().Trim());
            }
            else
            {
                iShowPage = 0;
            }

            if (Request.QueryString["exchangeid"] != null)
            {
                strExchangeRateID = Request.QueryString["exchangeid"].ToString();
            }

            if (strHandle != null)
            {
                if (strHandle == "ratelist")
                {
                    Response.Write(GetRateList());
                }

                if (strHandle == "ratelistpage")
                {
                    Response.Write(GetRateListPageCount());
                }

                if (strHandle == "deleterate" && strExchangeRateID != null)
                {
                    Response.Write(DeleteExchangeRateByID(strExchangeRateID));
                }
            }
        }

        #region 删除汇率信息
        /// <summary>
        /// 删除汇率信息
        /// </summary>
        /// <param name="tempExchangeRateID">汇率GID</param>
        /// <returns>值1表示删除成功 值为-1表示删除失败 值-2表示删除失败(要删除的汇率不存在)</returns>
        private int DeleteExchangeRateByID(string tempExchangeRateID)
        {
            int iResult = 0;

            ExchangeRateDA exchangeRateDA = new ExchangeRateDA();
            ExchangeRateEntity exchangeRateEntity = new ExchangeRateEntity();

            exchangeRateEntity = exchangeRateDA.GetExchangeRateByID(tempExchangeRateID);

            if (exchangeRateEntity != null)
            {
                if (exchangeRateEntity.GID != null)
                {
                    iResult = exchangeRateDA.DeleteExchangeRate(tempExchangeRateID,strUserID);
                }
                else
                {
                    iResult = -2;
                }
            }
            else
            {
                iResult = -2;
            }

            return iResult;
        }
        #endregion

        #region  获取汇率信息列表
        /// <summary>
        /// 获取汇率信息列表
        /// </summary>
        /// <returns>返回JSON数据信息</returns>
        private string GetRateList()
        {
            ExchangeRateDA exchangeRateDA = new ExchangeRateDA();
            DataTable sourceTable;

            string strCondition = "";
            string strTopInclude = "";
            string strTopNotInclude = "";

            if (strSearch != null)
            {
                if (!strSearch.Trim().Equals(""))
                {
                    string tempSearch = strSearch;
                    tempSearch = tempSearch.Replace("{", "");
                    tempSearch = tempSearch.Replace("}", "");
                    tempSearch = tempSearch.Replace("[", "");
                    tempSearch = tempSearch.Replace("]", "");

                    string[] searchArg = tempSearch.Split(new char[] { ',' });

                    for (int i = 0; i < searchArg.Length; i++)
                    {
                        string[] strArg = searchArg[i].Split(new char[] { ':' });

                        if (!strArg[1].Replace("\"", "").Trim().Equals(""))
                        {
                            switch (strArg[0].Replace("\"", ""))
                            {
                                case "btime"://Customer
                                    strCondition += String.Format(" AND convert(char(10),A.SETTLETIME,120) >= '{0}' ", strArg[1].Replace("\"", ""));
                                    break;
                                case "etime"://BillNO
                                    strCondition += String.Format(" AND convert(char(10),A.SETTLETIME,120) <= '{0}' ", strArg[1].Replace("\"", ""));
                                    break;
                                case "cus"://ETD BeginDate
                                    strCondition += String.Format(" AND A.CUSTOMERNAME LIKE  '%{0}%' ", strArg[1].Replace("\"", ""));
                                    break;
                                case "no"://ETD EndDate
                                    strCondition += String.Format(" AND A.BILLNO LIKE '%{0}%'", strArg[1].Replace("\"", ""));
                                    break;
                                default:
                                    break;
                            }
                        }
                    }
                }
            }

            string strSql = "";
            
            if (iCurrentPage > 0 && iShowPage > 0)
            {
                if (iCurrentPage == 1)
                {
                    strSql = String.Format(" SELECT {0} A.GID,B.CODENAME,B.DESCRIPTION,A.VALUE,A.TYPE,CONVERT(varchar(11),A.STARTTIME,120) AS STARTTIME,CONVERT(varchar(11),A.ENDTIME,120) AS ENDTIME,D.NAME AS COMPANYNAME,C.SHOWNAME,A.CREATETIME "
                              + " FROM currency_exchange AS A INNER JOIN code_currency AS B ON A.CURRENCYID = B.GID LEFT JOIN [user] AS C ON A.CREATEUSER = C.GID LEFT JOIN [company] as D ON A.COMPANYID = D.GID WHERE 1 > 0 " 
                              + strCondition + " AND ISNULL(A.ISDELETE,0) <> 1 AND A.COMPANYID = '"+strCompanyID+"' ORDER BY A.CREATETIME DESC ", "top " + iShowPage.ToString());
                }
                else
                {
                    strTopNotInclude = "top " + (iShowPage * (iCurrentPage - 1)).ToString();//RowCount*PageNum
                    strTopInclude = "top " + iShowPage.ToString();


                    strSql = String.Format(" SELECT {0} A.GID,B.CODENAME,B.DESCRIPTION,A.VALUE,A.TYPE,CONVERT(varchar(11),A.STARTTIME,120) AS STARTTIME,CONVERT(varchar(11),A.ENDTIME,120) AS ENDTIME,D.NAME AS COMPANYNAME,C.SHOWNAME,A.CREATETIME "
                       + " FROM currency_exchange AS A INNER JOIN code_currency AS B ON A.CURRENCYID = B.GID LEFT JOIN [user] AS C ON A.CREATEUSER = C.GID LEFT JOIN [company] as D ON A.COMPANYID = D.GID WHERE A.GID NOT IN "
                       + " (SELECT {1} GID FROM currency_exchange WHERE 1 > 0  " + strCondition + " AND ISNULL(ISDELETE,0) <> 1 AND COMPANYID = '" + strCompanyID + "' ORDER BY CREATETIME DESC ) " + strCondition
                       + " AND ISNULL(A.ISDELETE,0) <> 1 AND A.COMPANYID = '" + strCompanyID + "' ORDER BY A.CREATETIME DESC ", strTopInclude, strTopNotInclude);
                }
            }
            else
            {
                strSql = " SELECT A.GID,B.CODENAME,B.DESCRIPTION,A.VALUE,A.TYPE,CONVERT(varchar(11),A.STARTTIME,120) AS STARTTIME,CONVERT(varchar(11),A.ENDTIME,120) AS ENDTIME,D.NAME AS COMPANYNAME,C.SHOWNAME,A.CREATETIME "
                       + " FROM currency_exchange AS A INNER JOIN code_currency AS B ON A.CURRENCYID = B.GID LEFT JOIN [user] AS C ON A.CREATEUSER = C.GID LEFT JOIN [company] as D ON A.COMPANYID = D.GID WHERE 1 > 0 "
                       + strCondition + " AND ISNULL(A.ISDELETE,0) <> 1 AND A.COMPANYID = '" + strCompanyID + "' ORDER BY A.CREATETIME DESC ";
            }

            sourceTable = exchangeRateDA.GetExcuteSql(strSql).Tables[0];

            StringBuilder sourceBuilder = new StringBuilder();
            sourceBuilder.Append("{");
            sourceBuilder.Append("rows:[");
            for (int i = 0; i < sourceTable.Rows.Count; i++)
            {
                sourceBuilder.Append("{id:\"" + sourceTable.Rows[i][0].ToString() + "\",");
                sourceBuilder.Append("data:[");
                sourceBuilder.Append("\"0\",");
                for (int j = 1; j < sourceTable.Columns.Count; j++)
                {
                    if (j == sourceTable.Columns.Count - 1)
                    {
                        sourceBuilder.Append("\"" + sourceTable.Rows[i][j].ToString() + "\"");
                    }
                    else
                    {
                        sourceBuilder.Append("\"" + sourceTable.Rows[i][j].ToString() + "\",");
                    }
                }
                if (i == sourceTable.Rows.Count - 1)
                {
                    sourceBuilder.Append("]}");
                }
                else
                {
                    sourceBuilder.Append("]},");
                }

            }
            sourceBuilder.Append("]");
            sourceBuilder.Append("}");

            return sourceBuilder.ToString();
        }
        #endregion

        #region 获取汇率信息列表总页数
        /// <summary>
        /// 获取汇率信息列表总页数
        /// </summary>
        /// <returns>返回总页数</returns>
        private string GetRateListPageCount()
        {
            ExchangeRateDA exchangeRateDA = new ExchangeRateDA();
            string strSql = "SELECT COUNT(*) FROM currency_exchange as A WHERE ISNULL(ISDELETE,0) <> 1 ";

            string strCondition = "";
            if (strSearch != null)
            {
                if (!strSearch.Trim().Equals(""))
                {
                    string tempSearch = strSearch;
                    tempSearch = tempSearch.Replace("{", "");
                    tempSearch = tempSearch.Replace("}", "");
                    tempSearch = tempSearch.Replace("[", "");
                    tempSearch = tempSearch.Replace("]", "");

                    string[] searchArg = tempSearch.Split(new char[] { ',' });

                    for (int i = 0; i < searchArg.Length; i++)
                    {
                        string[] strArg = searchArg[i].Split(new char[] { ':' });

                        if (!strArg[1].Replace("\"", "").Trim().Equals(""))
                        {
                            switch (strArg[0].Replace("\"", ""))
                            {
                                case "btime"://Customer
                                    strCondition += String.Format(" AND convert(char(10),SETTLETIME,120) >= '{0}' ", strArg[1].Replace("\"", ""));
                                    break;
                                case "etime"://BillNO
                                    strCondition += String.Format(" AND convert(char(10),SETTLETIME,120) <= '{0}' ", strArg[1].Replace("\"", ""));
                                    break;
                                case "cus"://ETD BeginDate
                                    strCondition += String.Format(" AND CUSTOMERNAME LIKE  '%{0}%' ", strArg[1].Replace("\"", ""));
                                    break;
                                case "no"://ETD EndDate
                                    strCondition += String.Format(" AND BILLNO LIKE '%{0}%'", strArg[1].Replace("\"", ""));
                                    break;
                                default:
                                    break;
                            }
                        }
                    }

                    strSql += strCondition;
                }
            }

            int iTotal = int.Parse(exchangeRateDA.GetExcuteSql(strSql).Tables[0].Rows[0][0].ToString());

            return iTotal.ToString();
        }
        #endregion
    }
}