N_XLJC--MX/CPML_TJDRInfo.aspx.cs

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;
using GThinking.N_MISPRO;
using GThinking.N_MISPRO.Data;
using GThinking.N_MODEL.Page;
using GThinking.N_WORKFLOW;
using System.Data.Common;
using GThinking.N_MODEL.Dialog;
using GThinking.N_PUBLIC;
using GThinking.N_PUBLIC2;
using GThinking.N_MISPRO.Utility;
using System.IO;
using System.Text;
using GThinking.N_CONTROL;
using System.Data.OleDb;

namespace GThinking.N_XLJC
{
    public partial class CPML_TJDRInfo : SingleCardBase
    {
        CPML_TJDR entity = new CPML_TJDR();
        protected void Page_Load(object sender, EventArgs e)
        {
            this.UMID = 101421007;
            this.UMName = "导入适用型号电压";
            base.CurMaster = Master;
            base.CurEntity = entity;
            this.PageHeight = 180;
            this.PageWidth = 420;
            Master.EnableAttachBtn = false;
            Master.EnableCancelBtn = false;
            Master.EnableCopyBtn = false;
            Master.EnableDeleteBtn = false;
            Master.EnableEditBtn = false;
            Master.EnableInsertBtn = false;
            Master.EnablePrintBtn = false;
            Master.EnableRefreshBtn = false;
            Master.EnableSaveBtn = false;
            fupdPost.FileSaveType = gtAsyncFileUpload.DFileSaveType.SaveToPath;
            fupdPost.Filter = "XLS";
            base.InitPage();
        }
        protected void btnQD_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(fupdPost.FilePath.Trim()))
            {
                ShowMessage("请选择需要导入的文件！");
                return;
            }
            try
            {
                try
                {
                    string sPath = fupdPost.FilePath;
                    string sColumns = "产品目录名称,型号,电压等级";
                    string sMessage = "";
                    DataSet aDataExcel = TExcelData.ReadExcel(sPath.Trim(), "sheet1", sColumns, false, ref sMessage);
                    if (!sMessage.Trim().Equals(""))
                    {
                        throw new Exception("导入文件的内容格式不正确，请按帮助文档中要求的内容格式调整导入文件！");
                    }
                    TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
                    TSQL sSQL = new TSQL(dberp);
                    dberp.BeginTrans();
                    string sDRXXMessage = string.Empty;
                    try
                    {
                        for (int i = 0; i < aDataExcel.Tables[0].Rows.Count; i++)
                        {
                            int iNewID = CMISproDataBase.NewID("DBERP", "XLXSBJTJXHML");
                            string sCPMLMC = aDataExcel.Tables[0].Rows[i][0].ToString().Trim();
                            string sCPMLBH = GetCPMLID(sCPMLMC);
                            string sXLXHID = aDataExcel.Tables[0].Rows[i][1].ToString().Trim();
                            string sXLDYDJ = aDataExcel.Tables[0].Rows[i][2].ToString().Trim();
                            if (!CMISproDataBase.ExistsRecord("DBERP", "select 1  from XLXSCPML where XLXSCPML_CPMLID='" + sCPMLBH + "' and isnull(XLXSCPML_CPMLLX,' ')='N' "))
                            {
                                sDRXXMessage += "第" + (i + 1) + "行数据未成功导入，原因：单一产品目录【" + sCPMLMC + "】不存在。\r\n";
                                continue;
                            }
                            if (!CMISproDataBase.ExistsRecord("DBERP", "select 1  from XLXSCPML where XLXSCPML_CPMLID='" + sCPMLBH + "' and isnull(XLXSCPML_CPMLLX,' ')='N' and isnull(XLXSCPML_TJY,'N') = 'Y'"))
                            {
                                sDRXXMessage += "第" + (i + 1) + "行数据未成功导入，原因：单一产品目录【" + sCPMLMC + "】调价未勾选。\r\n";
                                continue;
                            }
                            string ExistsCPMLID = GetCPMLID(sXLXHID, sXLDYDJ);
                            if (!ExistsCPMLID.Trim().Equals(""))
                            {
                                sDRXXMessage += "第" + (i + 1) + "行数据未成功导入，原因：适用型号【" + sXLXHID + "】电压等级【" + sXLDYDJ + "】已在单一产品目录【" + ExistsCPMLID + "】中，不允许重复导入。\r\n";
                                continue;
                            }
                            sSQL.Clear();
                            sSQL.Add(" insert into XLXSBJTJXHML(XLXSBJTJXHML_XLXHID,XLXSBJTJXHML_DYDJID,XLXSBJTJXHML_CPMLID,XLXSBJTJXHML_WHR,XLXSBJTJXHML_WHRID,XLXSBJTJXHML_WHSJ)");
                            sSQL.Add("                 values(:XLXSBJTJXHML_XLXHID,:XLXSBJTJXHML_DYDJID,:XLXSBJTJXHML_CPMLID,:XLXSBJTJXHML_WHR,:XLXSBJTJXHML_WHRID,:XLXSBJTJXHML_WHSJ)");
                            sSQL.ParamByName("XLXSBJTJXHML_CPMLID").Value = sCPMLBH;
                            sSQL.ParamByName("XLXSBJTJXHML_XLXHID").Value = sXLXHID;
                            sSQL.ParamByName("XLXSBJTJXHML_DYDJID").Value = sXLDYDJ;
                            sSQL.ParamByName("XLXSBJTJXHML_WHR").Value = tSession.UserName;
                            sSQL.ParamByName("XLXSBJTJXHML_WHRID").Value = tSession.UserId;
                            sSQL.ParamByName("XLXSBJTJXHML_WHSJ").Value = CMISproConvert.ConvertTime(DateTime.Now);
                            dberp.ExecSQL(sSQL);
                        }
                        dberp.CommitTrans();
                        if (!sDRXXMessage.ToString().Trim().Equals(""))
                        {
                            ShowMessage("导入完成！\r\n存在如下数据未成功导入：\r\n" + sDRXXMessage.ToString());
                        }
                        else
                        {
                            ShowMessage("导入成功！");
                        }
                    }
                    catch (Exception ex)
                    {
                        dberp.RollbackTrans();
                        throw new Exception("导入失败，错误信息：" + ex.Message);
                    }
                }
                catch (Exception ex)
                {
                    ShowMessage("导入失败，错误信息：" + ex.Message);
                    return;
                }
                System.IO.File.Delete(fupdPost.FilePath.Trim());
            }
            catch (Exception ex)
            {
                ShowMessage("导入失败，错误信息：" + ex.Message);
                return;
            }
            CloseWindowNoQuery();
        }
        public string GetCPMLID(string sXLXHID, string sDYDJID)
        {
            TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
            TSQL SQL = new TSQL(dberp);
            SQL.Clear();
            SQL.Add("  select XLXSBJTJXHML_CPMLID from XLXSBJTJXHML where XLXSBJTJXHML_XLXHID='" + sXLXHID + "' and XLXSBJTJXHML_DYDJID='" + sDYDJID + "' ");
            DataSet ds = dberp.OpenDataSet(SQL);
            if (ds.Tables[0].Rows.Count > 0)
                return ds.Tables[0].Rows[0][0].ToString().Trim();
            else
                return "";
        }

        public string GetCPMLID(string sCPMLMC)
        {
            TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
            TSQL SQL = new TSQL(dberp);
            SQL.Clear();
            SQL.Add("  select XLXSCPML_CPMLID from XLXSCPML where XLXSCPML_CPMLMC=:pCPMLMC ");
            SQL.ParamByName("pCPMLMC").Value = sCPMLMC;
            DataSet ds = dberp.OpenDataSet(SQL);
            if (ds.Tables[0].Rows.Count > 0)
                return ds.Tables[0].Rows[0][0].ToString().Trim();
            else
                return "";
        }
        protected void btnQX_Click(object sender, EventArgs e)
        {
            CloseWindowNoQuery();
        }

    }
}