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

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Data.SqlClient;
using HySoft.DBUtility;
using System.Text;

namespace HySoft.BaseCallCenter.Web.calloutmanage
{
    public partial class callplanintophone : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                if (!string.IsNullOrEmpty(Request.QueryString["taskid"]))
                {
                    txtId.Value = Request.QueryString["taskid"].Trim();
                }
            }
        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            string res = "";
            int i = 0;
            int count = 0;
            System.Text.StringBuilder strBuilder = null;
            string strSelName = "";//计划选择的客户资料项

            if (this.txtFilePath.Value != "")
            {
                if (txtId.Value != "")
                {
                    DataTable dtPlan = new BLL.T_Call_PageField().GetList(" TaskId=" + txtId.Value).Tables[0];

                    if (dtPlan != null && dtPlan.Rows.Count > 0)
                    {
                        foreach (DataRow dr in dtPlan.Rows)
                        {
                            strSelName += "," + dr["F_DBFieldlName"];
                        }
                    }
                    if (strSelName != "")
                    {
                        strSelName = strSelName.Substring(1);
                    }


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

                        try
                        {
                            //************************将文件中的数据导入数据库*************************//
                            DataSet dt = ReadEcxel(this.txtFilePath.Value, 0);
                            try
                            {
                                if (dt != null && dt.Tables[0].Rows.Count != 0)
                                {
                                    strBuilder = new System.Text.StringBuilder();//用于如果导入失败，删除已经导入过的
                                    DataTable dtInputData = dt.Tables[0];//要导入的电话号码
                                    List<string> lstsql = new List<string>();
                                    int ilns = dt.Tables[0].Columns.Count;
                                    #region MyRegion
                                    using (SqlConnection coon = new SqlConnection(DbHelperSQL.connectionString))
                                    {
                                        try
                                        {
                                            coon.Open();
                                            #region MyRegion
                                            if (dtInputData.Rows.Count > 0)
                                            {
                                                string sql = "";
                                                int mmcount = 0;
                                                string strValue = "";//选择exce列的值
                                                string[] ArryClm = strSelName.Split(',');

                                                SqlCommand cmd = new SqlCommand();
                                                
                                                foreach (DataRow dr in dtInputData.Rows)
                                                {
                                                    sql = "";
                                                    strValue = "";
                                                    #region MyRegion
                                                    //是否存在此电话号码;
                                                    sql = "select count(1) from T_Call_TaskTelNum where TelNmb='" + dr[Request.Form["slTelNmb"].ToString()].ToString() + "'";
                                                    cmd = new SqlCommand(sql, coon);
                                                    try
                                                    {
                                                        mmcount = int.Parse(cmd.ExecuteScalar().ToString());
                                                    }
                                                    catch { }

                                                    lstsql.Clear();
                                                    strValue = "";
                                                    string ClientName = "";
                                                    if (mmcount == 0)
                                                    {
                                                        //获取选择对应的列信息
                                                        foreach (string str in ArryClm)
                                                        {

                                                            if (Request.Form["sl" + str] != null && Request.Form["sl" + str].ToString() != "空" && Request.Form["sl" + str].ToString() != "")
                                                            {
                                                                strValue += ",'" + dr[Request.Form["sl" + str].ToString()] + "'"; //
                                                                if (str == "ClientName")
                                                                {
                                                                    ClientName = dr[Request.Form["sl" + str].ToString()].ToString();
                                                                }
                                                            }
                                                            else
                                                            {
                                                                strValue += ",''"; //没有选择为空
                                                            }

                                                        }
                                                        if (strValue != "")
                                                        {
                                                            strValue = strValue.Substring(1);//sql  value后边的值



                                                            lstsql.Add("INSERT INTO T_Call_TaskTelNum (" + strSelName + ",FPBZ) values(" + strValue + ",1)");//电话表
                                                            //lstsql.Add("INSERT INTO T_CTI_CallHistory ([TelNum],[State],[y_TaskId],[y_TelNmbTableName],[y_TelNmbId],[AddTime],y_CallSource) values('" + dr[Request.Form["slTelNmb"].ToString()].ToString() + "',0," + this.txtId.Value + ",'T_Call_TaskTelNum',@@IDENTITY,getdate()," + callSource + ")");
                                                        }
                                                        else
                                                        {
                                                            i++;
                                                            res += i.ToString() + "没有选择对应的导入列;\n";
                                                        }

                                                        try
                                                        {
                                                            if (DBUtility.DbHelperSQL.ExecuteSqlTran(lstsql, coon) > 0)
                                                            {
                                                                count += 1;//导入成功，+1
                                                            }

                                                        }
                                                        catch (Exception ex)
                                                        {//导入失败
                                                            strBuilder.Append(dr[Request.Form["slTelNmb"].ToString()].ToString().Trim() + "、");
                                                        }
                                                    }
                                                    else
                                                    {
                                                        sql = "select top 1 F_Id from T_Call_TaskTelNum where TelNmb='" + dr[Request.Form["slTelNmb"].ToString()].ToString() + "'";
                                                        string telId = DbHelperSQL.GetSingle(sql).ToString();


                                                        //获取选择对应的列信息
                                                        foreach (string str in ArryClm)
                                                        {
                                                            if (Request.Form["sl" + str] != null && Request.Form["sl" + str].ToString() != "空" && Request.Form["sl" + str].ToString() != "")
                                                            {
                                                                strValue += "," + str + "='" + dr[Request.Form["sl" + str].ToString()] + "'"; //
                                                                if (str == "ClientName")
                                                                {
                                                                    ClientName = dr[Request.Form["sl" + str].ToString()].ToString();
                                                                }
                                                            }
                                                            else
                                                            {
                                                                // strValue += "," + str + "=''"; //没有选择为空
                                                            }

                                                        }
                                                        if (strValue != "")
                                                        {
                                                            lstsql.Add(" update T_Call_TaskTelNum set FPBZ=1" + strValue + "  where Id=" + telId);//电话表
                                                            //lstsql.Add("INSERT INTO " + strTbJGName + "([TelNmb],[Pid],[bdbz],[AddDate],[TbName],[TbId]) values('" + dr[Request.Form["slTelNmb"].ToString()].ToString() + "'," + this.hdPlanid.Value + ",0,getdate(),'" + strTbName + "'," + telId + ")");
                                                            //lstsql.Add("INSERT INTO T_CTI_CallHistory ([TelNum],[State],[y_TaskId],[y_TelNmbTableName],[y_TelNmbId],y_CallSource) values('" + dr[Request.Form["slTelNmb"].ToString()].ToString() + "',0," + this.txtId.Value + ",'T_Call_TaskTelNum'," + telId + "," + callSource + ")");

                                                        }
                                                        else
                                                        {
                                                            i++;
                                                            res += i.ToString() + "没有选择对应的导入列;\n";
                                                        }

                                                        try
                                                        {
                                                            if (DBUtility.DbHelperSQL.ExecuteSqlTran(lstsql, coon) > 0)
                                                            {
                                                                count += 1;//导入成功，+1
                                                            }
                                                        }
                                                        catch (Exception ex)
                                                        {//导入失败
                                                            strBuilder.Append(dr[Request.Form["slTelNmb"].ToString()].ToString().Trim() + "、");
                                                        }

                                                    }


                                                    #endregion
                                                }
                                            }
                                            else
                                            {
                                                res += "该文档的内容为空";
                                            }
                                            #endregion
                                        }
                                        catch
                                        {
                                            if (coon.State == ConnectionState.Open)
                                            {
                                                coon.Close();
                                            }
                                            i++;
                                            res += i.ToString() + ".导入出现异常，共导入" + count + "条数据，请从第" + (count + 2).ToString() + "行开始导入;\n";
                                            if (strBuilder.Length > 0)
                                            {
                                                res += "失败的号码为：" + strBuilder.ToString() + "\n";
                                            }
                                        }
                                        finally
                                        {
                                            if (coon.State == ConnectionState.Open)
                                            {
                                                coon.Close();
                                            }
                                        }
                                    }//****************using*********

                                    #endregion
                                }
                                else
                                {
                                    i++;
                                    res += i.ToString() + ".Excel中无数据;\n";
                                }

                            }
                            catch
                            {
                                i++;
                                res += i.ToString() + ".导入出现异常，导入失败;\n";
                                File.Delete(this.txtFilePath.Value); //导入不成功时删除上传的excel文件
                            }
                            finally
                            {
                                dt.Clear();
                                dt.Dispose();
                            }
                            KillProcess("Excel");//杀死已经打开的Excel进程
                            if (File.Exists(this.txtFilePath.Value))
                            {
                                File.Delete(this.txtFilePath.Value);
                            }
                        }
                        catch
                        {
                            i++;
                            res += i.ToString() + ".导入出现异常，导入失败;\n";
                            if (File.Exists(this.txtFilePath.Value))
                            {
                                File.Delete(this.txtFilePath.Value);
                            }
                        }


                        #endregion
                    }
                }


            }

            try
            {
                DBUtility.DbHelperSQL.ExecuteSql("update T_CTI_Task set y_HMCount=(y_HMCount+" + count + ") where TaskID=" + txtId.Value);
            }
            catch { }
            ClientScript.RegisterClientScriptBlock(this.GetType(), "", "<script>SaveInfo('success','导入成功,共导入" + count + "条；" + res + "')</script>");
        }


        #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文件,返一个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)
        {
            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""";
            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;
        }
        #endregion
    }
}