lyfz_repos/铂金版源码/LYFZ.CloudServerData/DAL/ChargeRecord.cs

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

namespace LYFZ.CloudServerData.DAL
{
    public class ChargeRecord : BaseDataOperate<LYFZ.CloudServerData.Model.ChargeRecord>
    {
        public ChargeRecord(LYFZ.CloudServerData.Model.DBConnection dbConn)
        {
            base.DBConn = dbConn;
        }
        /// <summary>
        /// 结算流量
        /// </summary>
        /// <param name="_DateYear"></param>
        /// <param name="_DateMonth"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool ChargeTraffic(int _DateYear, int _DateMonth, ref string msg)
        {
            try
            {
                if (_DateYear > DateTime.Now.Year || (_DateMonth >= DateTime.Now.Month && _DateMonth!=12))
                {
                    msg = "只能执行当月以前的流量结算，不能结当月或未来月份的流量";
                    return false;
                }
                else
                {
                    LYFZ.CloudServerData.DAL.CloudServerCollection serverDal = new CloudServerCollection(this.DBConn);
                    List<LYFZ.CloudServerData.Model.CloudServerCollection> serverModelList = serverDal.GetAllModelList();
                    StringBuilder sqlString = new StringBuilder();

                    foreach (LYFZ.CloudServerData.Model.CloudServerCollection serverModel in serverModelList)
                    {
                        LYFZ.CloudServerData.Model.DBConnection myDbconn = new Model.DBConnection();
                        myDbconn.DataBaseServer =  serverModel.DataBaseServerIP;
                        myDbconn.DataBaseName = "NetworkTrafficStatistics";
                        myDbconn.DataBasePort = serverModel.DataBasePort;
                        myDbconn.DataBaseUser =serverModel.DataBaseUser;
                        myDbconn.DataBasePass = serverModel.DataBasePass;
                        StringBuilder trafficSql = new StringBuilder();
                        trafficSql.Append(String.Format(" select EnterpriseID,sum(FlowDataLength) as FlowDataLength,sum(UserFlowDataLength) as UserFlowDataLength,DateYear,DateMonth "));
                        trafficSql.Append(String.Format(" from dbo.[tb_NetworkTrafficRecord] "));
                        trafficSql.Append(String.Format(" where DateYear={0} and DateMonth={1} ", _DateYear, _DateMonth));
                        trafficSql.Append(String.Format(" group by EnterpriseID,DateYear,DateMonth "));

                        System.Data.DataTable tb = LYFZ.Helper.SQLHelper.Query(trafficSql.ToString(), myDbconn.DBConnectionString).Tables[0];
                        sqlString.Clear();
                        foreach (System.Data.DataRow row in tb.Rows)
                        {
                            string EnterpriseID = row["EnterpriseID"].ToString();
                            string Identifier = String.Format("{0}_{1}_{2}_{3}", serverModel.ID, EnterpriseID, row["DateYear"].ToString(), row["DateMonth"].ToString());

                            sqlString.AppendFormat("if not EXISTS (select [Identifier] from [dbo].[tb_ChargeRecord] where [Identifier]='{0}')\r\n", Identifier);
                            sqlString.AppendFormat("begin \r\n");
                            sqlString.AppendFormat("if EXISTS (select [EnterpriseID] from [dbo].[tb_Enterprise] where [EnterpriseID]='{0}')\r\n", EnterpriseID);
                            sqlString.AppendFormat("begin \r\n");
                            sqlString.AppendFormat("INSERT INTO [dbo].[tb_ChargeRecord] ([ID],[Identifier],[ServerID],[EID],[FlowDataLength],[UserFlowDataLength],[status],[ChargeDateTime],[StatisticsTime]) VALUES({0},'{1}',{2},'{3}',{4},{5},{6},'{7}-{8}',{9}) \r\n"
                                , LYFZ.WinAPI.CustomPublicMethod.GenerateId(), Identifier, serverModel.ID, EnterpriseID, row["FlowDataLength"].ToString(), row["UserFlowDataLength"].ToString(), 1, row["DateYear"].ToString(), row["DateMonth"].ToString(), DateTime.Now.ToJavaScriptTimeStamp());
                            sqlString.AppendFormat(" update tb_Enterprise set [Balance]=[Balance]-({0}/1024.0) where [EnterpriseID]='{1}' \r\n", row["UserFlowDataLength"].ToString(), EnterpriseID);
                            sqlString.AppendFormat("end \r\n");
                            sqlString.AppendFormat("end \r\n");
                            // sqlList.AppendFormat("GO \r\n");
                        }
                        if (sqlString.ToString().Trim().Length > 0)
                        {
                            LYFZ.Helper.SQLHelper.ExecuteSql(sqlString.ToString(), this.DBConn.DBConnectionString);
                        }
                        sqlString.Clear();
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                msg = "流量结算出错："+ex.Message;
                return false;
            }
        }

        public DataTable SearchRechargeRecordData(string searchText, string searService, string mouthText, int pageIndx, int pageSize)
        {
            string sql = @" SELECT top " + (pageIndx * pageSize) + @" [tb_ChargeRecord].*,EnterpriseName,CloudServerName
                             FROM [tb_ChargeRecord] inner join tb_Enterprise on [tb_ChargeRecord].EID = tb_Enterprise.EnterpriseID 
                                   inner join tb_CloudServerCollection on tb_CloudServerCollection.ID = ServerID
                            ";


            string where = "";
            if (!string.IsNullOrEmpty(searchText))
            {
                where += "( EID like '%" + searchText + "%' or EnterpriseName like '%" + searchText + "%' )";
            }
            if (!string.IsNullOrEmpty(searService))
            {
                if (!string.IsNullOrEmpty(where))
                {
                    where += " and ";
                }
                where += " and [ServerID] = '" + searService + "' ";
            }

            if (!string.IsNullOrEmpty(mouthText))
            {
                if (!string.IsNullOrEmpty(where))
                {
                    where += " and ";
                }
                where += " and [ChargeDateTime] = '" + mouthText + "' ";
            }
            if (!string.IsNullOrEmpty(where))
            {
                sql = sql + " where " + where;
            }
            DataTable dt = LYFZ.Helper.SQLHelper.Query(sql, base.DBConn.DBConnectionString).Tables[0];
            DataTable jsonDt = dt.AsEnumerable().Skip((pageIndx - 1) * pageSize).Take(pageSize).CopyToDataTable();
            return jsonDt;
        }
    }
}