SZRXYS/BaseCallCent.../calloutmanage/ajax/callplaninto...

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using HySoft.Common;
using System.Diagnostics;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using HySoft.DBUtility;
using System.Data.OleDb;

namespace HySoft.BaseCallCenter.Web.calloutmanage.ajax
{
    /// <summary>
    /// callplanintophone 的摘要说明
    /// </summary>
    public class callplanintophone : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";
            string action = CommonRequest.GetQueryString("action");
            switch (action)
            {
                case "getfieldlist":
                    getfieldlist(context);
                    break;
                case "excelinto":
                    ExcelInto(context);
                    break;
                case "excelintodata":
                    context.Response.Write(insertdata(context));
                    break;
            }
        }

        private string insertdata(HttpContext context)
        {
            string res = "";
            string type = "0";
            int i = 1;
            try
            {
                string filepath = context.Request.Form["txtFilePath"].ToString();
                string columntofield = context.Request.Form["txtColumnToField"].ToString();
                string taskid = context.Request.Params["taskid"];

                #region 将文件中的数据导入数据库

                try
                {
                    //************************将文件中的数据导入数据库*************************//
                    DataSet dt = ReadEcxel(filepath, 0);
                    int rcount = 0;
                    int ccount = 0;
                    try
                    {
                        if (dt != null && dt.Tables[0].Rows.Count != 0)
                        {
                            rcount = dt.Tables[0].Rows.Count;
                            ccount = dt.Tables[0].Columns.Count;
                            List<string> lstsql = new List<string>();
                            foreach (DataRow dr in dt.Tables[0].Rows)
                            {
                                string[] arrfield = columntofield.Split(';');
                                string sqlfield="";
                                string sqlvalue = "";

                                foreach (string fieldinfo in arrfield)
                                {
                                    if (fieldinfo.Trim() != "")
                                    {
                                        string field = fieldinfo.Split(':')[0];
                                        string column = fieldinfo.Split(':')[1];
                                        sqlfield += field + ",";
                                        sqlvalue += "'" + dr[column].ToString().Trim().Replace(",", "").Replace("'", "") + "',";
                                    }
                                }
                                sqlfield += "#@!";
                                sqlvalue += "#@!";
                                sqlfield = sqlfield.Replace(",#@!", "").Replace("#@!", "");
                                sqlvalue = sqlvalue.Replace(",#@!", "").Replace("#@!", "");
                                lstsql.Add("INSERT INTO [T_Call_TaskTelNum]([F_TaskId]," + sqlfield + ")VALUES(" + taskid + "," + sqlvalue + ")");
                            }
                            int c = 0;
                            using (SqlConnection coon = new SqlConnection(DbHelperSQL.connectionString))
                            {
                                c=DBUtility.DbHelperSQL.ExecuteSqlTran(lstsql, coon);
                                type = "1";
                                res = "导入成功,共导入" + c + "条；";
                            }

                            try
                            {
                                DBUtility.DbHelperSQL.ExecuteSql("update T_CTI_Task set y_HMCount=(y_HMCount+" + c + ") where TaskID=" + taskid);
                            }
                            catch { }
                        }
                        else
                        {
                            i++;
                            res += i.ToString() + ".Excel或txt文本中无数据;";
                        }

                    }
                    catch
                    {
                        i++;
                        res += i.ToString() + ".导入出现异常，导入失败;";
                        //File.Delete(filepath); //导入不成功时删除上传的excel文件
                    }
                    finally
                    {
                        dt.Clear();
                        dt.Dispose();
                    }
                    KillProcess("Excel");//杀死已经打开的Excel进程
                }
                catch
                {
                    i++;
                    res += i.ToString() + ".Excel或txt文本格式不正确，系统错误，导入失败;";
                    KillProcess("Excel");//杀死已经打开的Excel进程
                    //File.Delete(filepath); //导入不成功时删除上传的excel文件
                }

                #endregion
            }
            catch(Exception ex)
            {
                res = ex.ToString();
            }
            return "{msg:" + type + ",info:\"" + res + "\"}";
        }

        private void getfieldlist(HttpContext context)
        {
            string strHtml = "";
            string strList = "";

            DataTable dt = new DataTable();
            try
            {
                string sql = " ";
                string taskid = "0";
                try
                {
                    taskid = context.Request.Params["taskid"];
                    if (taskid.Trim() != "")
                    {
                        sql += " and [F_TaskId]=" + taskid + " ";
                    }
                    else
                    {
                        sql += " and [F_TaskId]=0 ";
                    }
                }
                catch
                { }
                BLL.T_Call_PageField bll = new BLL.T_Call_PageField();
                dt = bll.GetList(" 1=1 " + sql + " order by F_Sort ").Tables[0];
                if (dt.Rows.Count > 0)
                {
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        strList += dt.Rows[i]["F_Id"] + ",";
                        strHtml += "<div style=\"float:left; width:120px; height:80px; text-align:center; padding:6px;\">";
                        strHtml += "<div style=\"height: 80px; border: 1px solid #CCCCCC; padding: 0px; text-align: center;\" onmouseover=\"this.style.border='1px solid #3A8BDC'\"";
                        strHtml += "onmouseout=\"this.style.border='1px solid #CCCCCC'\">";
                        strHtml += "<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\"";
                        strHtml += "width=\"100%\" height=\"100%\" style=\" text-align:center;padding: 0px;\">";
                        strHtml += "<tr><td style='background:#cccccc; color:red; height:30px;text-align:center;padding: 0px;'><input type=\"text\" id=\"txt_id_" + dt.Rows[i]["F_Id"].ToString() + "\" value='" + dt.Rows[i]["F_DBFieldlName"].ToString().Trim() + "' name=\"forcount\" style=\"display:none\" />";
                        strHtml += "<span id=\"span_name_" + dt.Rows[i]["F_Id"].ToString() + "\">" + dt.Rows[i]["F_Name"].ToString() + "</span></td></tr>";
                        strHtml += "<tr><td style='height:30px;text-align:center;padding: 0px;'><select id=\"select_column_" + dt.Rows[i]["F_Id"].ToString() + "\" style='width:100px; height:25px;' onfocus='InitExcelColumns(\"" + dt.Rows[i]["F_Id"].ToString() + "\")' ><option value=\"\"></option></select></td></tr></table></div></div>";
                    }

                }
                strList += "!@#";
                strList = strList.Replace(",!@#", "").Replace("!@#", "");
            }
            catch
            {
            }
            finally
            {
                dt.Clear();
                dt.Dispose();
            }


            context.Response.Write(strList + "~" + strHtml);
            context.Response.End();
        }

        #region 杀死Excel进程

        /// <summary>
        /// 杀死Excel进程
        /// </summary>
        /// <param name="processName"></param>
        private void KillProcess(string processName)
        {
            System.Diagnostics.Process myproc = new System.Diagnostics.Process();
            //得到所有打开的进程
            try
            {
                foreach (Process thisproc in Process.GetProcessesByName(processName))
                {
                    if (!thisproc.CloseMainWindow())
                    {
                        thisproc.Kill();
                    }
                }
            }
            catch (Exception Exc)
            {
                throw new Exception("", Exc);
            }
        }
        #endregion

        #region 导入Excel表格
        protected void ExcelInto(HttpContext context)
        {

            string res = "";
            int i = 0;
            string Excelsource = "";
            HttpPostedFile _upfile = null;
            string strClNamelist = "";//列名称
            _upfile = context.Request.Files["txtUpload"];
            string filename = "";
            int rcount = 0;
            int ccount = 0;
            string filetype = "";
            try
            {
                //string fileType = _upfile.ContentType;
                string fileType = Path.GetExtension(_upfile.FileName.ToString().Trim());
                int type = fileType.IndexOf("xls");//application/vnd.ms-excel --//application/vnd
                if (type == -1)
                {
                    type = fileType.IndexOf("xlsx");
                }
                else
                {
                    filetype = "0"; 
                }
                if (type == -1)
                {
                    type = fileType.IndexOf("txt");
                    if (type == -1)
                    {
                    }
                    else
                    {
                        filetype = "1";
                    }
                }
                else
                {
                    filetype = "0";
                }
                if (type == -1)
                {
                    i++;
                    res += i.ToString() + ".请选择正确的Excel或txt文本格式数据;";
                }
                else
                {
                    string filePath = _upfile.FileName.ToString();
                    int start = filePath.LastIndexOf("\\") + 1;
                    int end = filePath.Length;
                    filename = filePath.Substring(start, end - start);
                    if (res == "")
                    {
                        try
                        {
                            KillProcess("Excel");//杀死已经打开的Excel进程
                            if (_upfile.ContentLength > 0)
                            {
                                #region 上传excel文件

                                //*********************上传excel文件**********************//
                                string newFileName = DateTime.Now.ToString("yyyyMMddHHmmssms");
                                newFileName = newFileName + "_" + filename;

                                try
                                {
                                    if (!Directory.Exists(HttpContext.Current.Server.MapPath(HttpContext.Current.Request.ApplicationPath + "\\ExcelData\\"+DateTime.Now.ToString("yyyyMM"))))
                                    {
                                        Directory.CreateDirectory(HttpContext.Current.Server.MapPath(HttpContext.Current.Request.ApplicationPath + "\\ExcelData\\" + DateTime.Now.ToString("yyyyMM")));
                                    }
                                    _upfile.SaveAs(HttpContext.Current.Server.MapPath(HttpContext.Current.Request.ApplicationPath + "\\ExcelData\\"+DateTime.Now.ToString("yyyyMM")+"\\") + newFileName.ToString());
                                    Excelsource = HttpContext.Current.Server.MapPath(HttpContext.Current.Request.ApplicationPath + "\\ExcelData\\"+DateTime.Now.ToString("yyyyMM")+"\\") + newFileName.ToString();

                                }
                                catch
                                {
                                    i++;
                                    res = i.ToString() + ".上传Excel或txt文本数据时失败;";
                                }

                                #endregion

                                if (res == "")
                                {
                                    #region 将文件中的数据导入数据库

                                    try
                                    {

                                        //************************将文件中的数据导入数据库*************************//
                                        DataSet dt = ReadEcxel(Excelsource, 0);
                                        try
                                        {
                                            if (dt != null && dt.Tables[0].Rows.Count != 0)
                                            {
                                                if (res == "")
                                                {
                                                    rcount = dt.Tables[0].Rows.Count;
                                                    ccount = dt.Tables[0].Columns.Count;
                                                    foreach (DataColumn dc in dt.Tables[0].Columns)
                                                    {
                                                        strClNamelist += "|" + dc.ColumnName;
                                                    }
                                                    if (strClNamelist != "")
                                                    {
                                                        strClNamelist = strClNamelist.Substring(1);
                                                    }
                                                    // dt.Tables[0].Columns;
                                                }
                                            }
                                            else
                                            {
                                                i++;
                                                res += i.ToString() + ".Excel或txt文本中无数据;";
                                            }

                                        }
                                        catch(Exception ex)
                                        {
                                            i++;
                                            res += i.ToString() + ".导入出现异常，导入失败;"+ex.Message.ToString();
                                            File.Delete(Excelsource); //导入不成功时删除上传的excel文件
                                        }
                                        finally
                                        {
                                            dt.Clear();
                                            dt.Dispose();
                                        }
                                        KillProcess("Excel");//杀死已经打开的Excel进程
                                    }
                                    catch (Exception ex)
                                    {
                                        i++;
                                        res += i.ToString() + ".Excel或txt文本格式不正确，系统错误，导入失败;" + ex.Message.ToString();
                                        KillProcess("Excel");//杀死已经打开的Excel进程
                                        File.Delete(Excelsource); //导入不成功时删除上传的excel文件
                                    }

                                    #endregion
                                }
                            }
                            else
                            {
                                i++;
                                res += i.ToString() + ".请选择上传文件;";
                            }
                        }
                        catch
                        {
                            i++;
                            res += i.ToString() + ".Excel或txt文本格式不正确，系统错误，导入失败;";
                            KillProcess("Excel");//杀死已经打开的Excel进程
                        }
                    }
                    else
                    {
                        i++;
                        res = i.ToString() + "." + res + ";";
                    }
                }
            }
            catch (Exception ex)
            {
                i++;
                res += i.ToString() + ".错误信息：" + ex.Message + "，导入失败;";
                KillProcess("Excel");//杀死已经打开的Excel进程
                if (File.Exists(Excelsource))
                {
                    File.Delete(Excelsource); //导入不成功时删除上传的excel文件
                }
            }

            if (res != "")
            {
                string ms = "{msg:0,filename:\"" + filename + "\", filepath: \"" + Excelsource.Replace("\\", "\\\\") + "\",arrcolumn:\"" + strClNamelist + "\",filetype:\"" + filetype.Trim() + "\",info:\"" + res.Replace(".", "、").Replace(";", "；") + "\",msbox:\"" + res.Replace(".", "、").Replace(";", "；") + "\"}";
                context.Response.Write(ms);
                //context.Response.Write("{msg:0,msbox:\"" + res + "\"}");
            }
            else
            {
                string info = "本文件有" + rcount.ToString() + "行" + ccount.ToString() + "列数据";
                context.Response.Write("{msg:1,filename:\"" + filename + "\", filepath: \"" + Excelsource.Replace("\\", "\\\\") + "\",arrcolumn:\"" + strClNamelist + "\",filetype:\"" + filetype.Trim() + "\",info:\"" + info + "\"}");
            }


        }
        #endregion

        #region 读取Excel文件,返一个DataTable
        ///   功能:读取Excel文件,返一个DataTable
        ///   日期:2004-10-20
        ///   </summary>
        ///   <param   name="FileName">文件</param>
        ///   <param   name="Rows">第一行是否为列名,一般为0或1</param>
        ///   <returns></returns>

        private DataSet ReadEcxel(string FileName, int Rows)
        {
            return ToDataTable(FileName);
            //string _filePath = FileName;
            //string excelconnstring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + "";
            ////HDR=NO; 读取数据表头（第一行）；HDR=yes 不读取表头
            //excelconnstring += @";Extended Properties=""Excel 8.0;HDR=yes;IMEX=1""";

            //#region 判断excel格式xls或xlsx

            //string ext = Path.GetExtension(_filePath);

            //if (ext == "xlsx")
            //{
            //    excelconnstring = "";
            //}

            //#endregion

            //System.Data.OleDb.OleDbConnection excelconn = new System.Data.OleDb.OleDbConnection(excelconnstring);
            //string sql = "select * from [sheet1$]";
            //System.Data.OleDb.OleDbDataAdapter mycomm = new System.Data.OleDb.OleDbDataAdapter(sql, excelconn);
            //DataSet myds = new DataSet();
            //mycomm.Fill(myds, "DataListTable");

            //return myds;
        }

        /// <summary>
        /// 读取Excel文件到DataSet中
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <returns></returns>
        public DataSet ToDataTable(string filePath)
        {
            string connStr = "";
            string fileType = System.IO.Path.GetExtension(filePath);
            if (string.IsNullOrEmpty(fileType)) return null;

            if (fileType == ".xls")
                connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
            else
                connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
            string sql_F = "Select * FROM [{0}]";

            OleDbConnection conn = null;
            OleDbDataAdapter da = null;
            DataTable dtSheetName = null;

            DataSet ds = new DataSet();
            try
            {
                // 初始化连接，并打开
                conn = new OleDbConnection(connStr);
                conn.Open();

                // 获取数据源的表定义元数据                       
                string SheetName = "";
                dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                // 初始化适配器
                da = new OleDbDataAdapter();
                for (int i = 0; i < dtSheetName.Rows.Count; i++)
                {
                    SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];

                    if (SheetName.Contains("$") && !SheetName.Replace("'", "").EndsWith("$"))
                    {
                        continue;
                    }

                    da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName), conn);
                    da.Fill(ds, "DataListTable");
                    //DataSet dsItem = new DataSet();
                    //da.Fill(dsItem, "DataListTable");

                    //ds.Tables.Add(dsItem.Tables[0].Copy());
                    break;
                }
            }
            catch (Exception ex)
            {
                SysLog.WriteLog(ex);
            }
            finally
            {
                // 关闭连接
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    da.Dispose();
                    conn.Dispose();
                }
            }
            return ds;
        }

        #endregion

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}