using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using LYFZ.BLL;
using LYFZ.Model;
using LYFZ.DAL;
using System.Text.RegularExpressions;
using LYFZ.ComponentLibrary;
using System.IO;
using LYFZ.Software.MainBusiness.Customers;
using System.Collections;

namespace LYFZ.Software.MainBusiness.HospitalTrackingSystem
{
    public partial class ImportExcel : LYFZ.Software.UI.HospitalTrackingSystem.ImportExcel
    {
        LYFZ.BLL.BLL_ErpOrder orbll = new BLL.BLL_ErpOrder();
        BLL.BLL_ErpHospitalClient hcbll = new BLL.BLL_ErpHospitalClient();
        DataTable newDt = new DataTable();
        public ImportExcel()
        {
            this.btnOpen.Click += btnOpen_Click;
            this.btnSave.Click += btnSave_Click;
            this.btnCancel.Click += btnCancel_Click;
        }
        /// <summary>
        /// 是否保存
        /// </summary>
        public bool IsSaveed = false;
        string StrUserID = LYFZ.Software.MainBusiness.CommonLogical.SuccessfulLogin.LoginUserModel.User_EmployeeID;
        /// <summary>
        /// 取消按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void btnCancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        /// <summary>
        /// 保存按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void btnSave_Click(object sender, EventArgs e)
        {
            if (this.dgvData.Rows.Count > 0)
            {
                if (MessageBoxCustom.Show("你确定要导入吗？", "温馨提示", MessageBoxButtons.YesNo) == DialogResult.No)
                { return; }

                List<Helper.CommandInfo> clist = new List<Helper.CommandInfo>();
                try
                {
                    LYFZ.ComponentLibrary.FrmLoadHandling.LoadDoWorkMethod(delegate(object obj, BackgroundWorker backgroundWorker)
                    {
                       
                        //foreach(DataGridViewColumn dataColumn in this.dgvData.Columns)
                        //{
                        //    newDt.Columns.Add(dataColumn.Name);
                        //}
                        //for (int i = 0; i < this.dgvData.Rows.Count; i++)
                        //{
                        //    DataRow newDr = newDt.NewRow();
                        //    foreach (DataGridViewColumn dataColumn in this.dgvData.Columns)
                        //    {
                        //        newDr[dataColumn.Name] = this.dgvData.Rows[i].Cells[dataColumn.Name].Value;
                        //    }
                        //    newDt.Rows.Add(newDr);
                        //}
                        for (int i = 0; i < newDt.Rows.Count; i++)
                        {
                            backgroundWorker.ReportProgress(0, "正在检测第" + (i + 1) + "条，共" + this.dgvData.Rows.Count + "条");
                            LYFZ.Model.Model_ErpHospitalClient model = new Model_ErpHospitalClient();
                            model.Hct_DividedShop = LYFZ.Software.MainBusiness.CommonLogical.SuccessfulLogin.GetLocalCompanyInfoID();
                            model.Hct_Oddnumber = this.GenerationNumber(i);
                            try
                            {
                                model.Hct_CustomerName = newDt.Rows[i]["客户姓名"].ToString().Trim();
                                if (newDt.Rows[i]["宝宝生日/预产期"].ToString().Trim().Length > 0)
                                { model.Hct_BabyBirthday = Convert.ToDateTime(newDt.Rows[i]["宝宝生日/预产期"]).ToString("yyyy-MM-dd"); }
                                if (newDt.Rows[i]["生日类型"].ToString().Trim() == "宝宝生日")
                                {
                                    model.Hct_BirthdayType = 0;
                                    model.Hct_BabyName = newDt.Rows[i]["宝宝姓名"].ToString().Trim();
                                    model.Hct_BabySex = newDt.Rows[i]["宝宝性别"].ToString().Trim();
                                    model.Hct_AreLunar = newDt.Rows[i]["生日是否农历"].ToString().Trim() == "农历" ? true : false;
                                }
                                else if (newDt.Rows[i]["生日类型"].ToString().Trim() == "预产期")
                                {
                                    model.Hct_BirthdayType = 1;
                                    model.Hct_AreLunar = false;
                                    model.Hct_PregnantDay = 0;
                                    if (!string.IsNullOrEmpty(model.Hct_BabyBirthday) && string.IsNullOrEmpty(model.Hct_PregnantDay.ToString().Trim()))
                                    {
                                        int IntDay = Convert.ToInt32("-" + (40 * 7));
                                        model.Hct_PregnantDay = ((SDateTime.Now - Convert.ToDateTime(model.Hct_BabyBirthday).AddDays(IntDay)).Days / 7);
                                    }
                                }
                            }
                            catch (Exception ex)
                            {
                                MessageBoxCustom.Show("来单批号为：" + newDt.Rows[i]["来单批号"].ToString().Trim() + "；出错原因：" + ex.Message.Trim());
                                return;
                            }
                            model.Hct_MessageSingle = "否";
                            model.Hct_CustomerOrders = 0;
                            model.Hct_LostRemark = "";
                            model.Hct_OrderNumber = "";
                            model.Hct_BabyFeeding = newDt.Rows[i]["喂养方式"].ToString_s().Trim();
                            model.Hct_CustomerRegions = newDt.Rows[i]["客户区域"].ToString_s().Trim();
                            model.Hct_CustomerGroups = newDt.Rows[i]["客户类别"].ToString_s().Trim();
                            model.Hct_DealStatus = "未成交";
                            model.Hct_SingleHospital = newDt.Rows[i]["来单医院"].ToString_s().Trim();
                            model.Hct_MyselfMobile = newDt.Rows[i]["本人手机"].ToString_s().Trim();
                            model.Hct_HusbandMobile = newDt.Rows[i]["老公手机"].ToString_s().Trim();
                            model.Hct_FamilyPhone = newDt.Rows[i]["自家电话"].ToString_s().Trim();
                            model.Hct_Husbandphone = newDt.Rows[i]["婆家电话"].ToString_s().Trim();
                            model.Hct_Maidenphone = newDt.Rows[i]["娘家电话"].ToString_s().Trim();
                            model.Hct_QQ = newDt.Rows[i]["客户QQ"].ToString_s().Trim();
                            model.Hct_Retail = newDt.Rows[i]["门市"].ToString_s().Trim();
                            model.Hct_Remarks = newDt.Rows[i]["来单备注"].ToString_s().Trim();
                            model.Hct_HomeAddress = newDt.Rows[i]["自家地址"].ToString_s().Trim();
                            model.Hct_HomePostcode = newDt.Rows[i]["自家邮编"].ToString_s().Trim();
                            model.Hct_HusbandAddress = newDt.Rows[i]["婆家地址"].ToString_s().Trim();
                            model.Hct_HusbandPostcode = newDt.Rows[i]["婆家邮编"].ToString_s().Trim();
                            model.Hct_MaidenAddress = newDt.Rows[i]["娘家地址"].ToString_s().Trim();
                            model.Hct_MaidenPostcode = newDt.Rows[i]["娘家邮编"].ToString_s();
                            if (!string.IsNullOrEmpty(newDt.Rows[i]["来单日期"].ToString_s().Trim()))
                            { model.Hct_SingleDatetime = Convert.ToDateTime(newDt.Rows[i]["来单日期"]); }
                            model.Hcr_BusinessType = newDt.Rows[i]["业务种类"].ToString_s().Trim();
                            model.Hct_BusinessIntention = newDt.Rows[i]["业务意向"].ToString_s().Trim();
                            model.Hct_CreateDatetime = SDateTime.Now;
                            model.Hct_CreateName = this.StrUserID;
                            model.Hct_ImportedOrderNumber = newDt.Rows[i]["来单批号"].ToString_s().Trim();
                            clist.Add(hcbll.GetAddCommandInfo(model));
                        }
                    });
                }
                catch (Exception ex)
                {
                    MessageBoxCustom.Show(ex.Message.ToString());
                    return;
                }

                if (clist.Count > 0)
                {
                    int IsExecuteSqlTran = 0;
                    try
                    {
                        LYFZ.ComponentLibrary.FrmLoadHandling.LoadDoWorkMethod(delegate(object obj, BackgroundWorker backgroundWorker)
                        {
                            backgroundWorker.ReportProgress(0, "保存正在提交中，请稍等...");
                            IsExecuteSqlTran = LYFZ.BLL.BaseBllOperate.ExecuteSqlTran(clist);
                        });
                    }
                    catch (Exception ex) { MessageBoxCustom.Show(ex.Message.ToString()); }
                    if (IsExecuteSqlTran > 0)
                    {
                        this.IsSaveed = true;
                        MessageBoxCustom.Show("导入成功！");
                        this.dgvData.Rows.Clear();
                        this.Close();
                    }
                    else
                    { MessageBoxCustom.Show("导入失败！"); }
                }
            }
            #region
            //Customer Cus = new Customer();
            //bool ok = true;
            //System.Data.DataTable dt = orbll.GetView_Custom("tb_ErpHospitalClient", StrWhere: "Hct_MyselfMobile in (" + StrDianhua("本人手机") + ")", ShowColumnName: "ID,Hct_MyselfMobile,Hct_HusbandMobile").Tables[0];
            //LYFZ.BLL.BLL_ErpHospitalClient ambll = new BLL.BLL_ErpHospitalClient();
            //DateTime strTime = SDateTime.Now;
            //string strUserID = LYFZ.Software.MainBusiness.CommonLogical.SuccessfulLogin.LoginUserModel.User_EmployeeID;
            //if (dgvData.Rows.Count > 0)
            //{
            //    List<string> Gmlist = new List<string>();
            //    Gmlist = new List<string>();
            //    List<Helper.CommandInfo> clist = new List<Helper.CommandInfo>();
            //    IntentionsEntered frm = new IntentionsEntered();
            //    if (dgvData.Columns[0].HeaderText.Trim() == "客户姓名" && dgvData.Columns[1].HeaderText.Trim() == "宝宝姓名" && dgvData.Columns[2].HeaderText.Trim() == "宝宝性别" && dgvData.Columns[3].HeaderText.Trim() == "生日类型"
            //        && dgvData.Columns[4].HeaderText.Trim() == "宝宝生日/预产期" && dgvData.Columns[5].HeaderText.Trim() == "是否农历" && dgvData.Columns[6].HeaderText.Trim() == "本人手机" && dgvData.Columns[7].HeaderText.Trim() == "老公手机" &&
            //        dgvData.Columns[8].HeaderText.Trim() == "客户区域" && dgvData.Columns[9].HeaderText.Trim() == "来单医院")
            //    {
            //        for (int i = 0; i < dgvData.Rows.Count; i++)
            //        {

            //            Model.Model_ErpHospitalClient model = new Model.Model_ErpHospitalClient();
            //            model.Hct_Oddnumber = frm.GenerationNumber();
            //            if (dgvData.Rows[i].Cells["客户姓名"].Value.ToString().Trim() != "")
            //            { model.Hct_CustomerName = dgvData.Rows[i].Cells["客户姓名"].Value.ToString().Trim(); }
            //            else { ok = false; }

            //            if (dgvData.Rows[i].Cells["宝宝姓名"].Value.ToString().Trim() != "")
            //            { model.Hct_BabyName = dgvData.Rows[i].Cells["宝宝姓名"].Value.ToString().Trim(); }
            //            else { ok = false; }

            //            if (dgvData.Rows[i].Cells["宝宝性别"].Value.ToString().Trim() != "男")
            //            {
            //                if (dgvData.Rows[i].Cells["宝宝性别"].Value.ToString().Trim() != "女")
            //                { ok = false; }
            //                else { model.Hct_BabySex = dgvData.Rows[i].Cells["宝宝性别"].Value.ToString().Trim(); }
            //            }
            //            else { model.Hct_BabySex = dgvData.Rows[i].Cells["宝宝性别"].Value.ToString().Trim(); }

            //            if (dgvData.Rows[i].Cells["生日类型"].Value.ToString().Trim() == "宝宝生日")
            //            {
            //                model.Hct_BirthdayType = 0;
            //            }
            //            else { model.Hct_BirthdayType = 1; }

            //            string Strqq = dgvData.Rows[i].Cells["宝宝生日/预产期"].Value.ToString().Trim();
            //            string Straa = dgvData.Rows[i].Cells["是否农历"].Value.ToString().Trim();
            //            if (Strqq.Length != 10)
            //            {
            //                ok = false;
            //            }
            //            if (Strqq != "")
            //            {
            //                LYFZ.ComponentLibrary.MaskedTextDateEx maskDate = new ComponentLibrary.MaskedTextDateEx();
            //                LYFZ.ComponentLibrary.CheckBoxEx chk = new ComponentLibrary.CheckBoxEx();
            //                if (Straa == "农历")
            //                { chk.Checked = true; }
            //                maskDate.Text = Strqq;
            //                if (maskDate.CheckDateValue(maskDate, chk) == 2)
            //                { ok = false; }
            //                else { model.Hct_BabyBirthday = Strqq; }
            //            }
            //            if (dgvData.Rows[i].Cells["是否农历"].Value.ToString().Trim() == "农历")
            //            {
            //                model.Hct_AreLunar = true;
            //            }

            //            else { model.Hct_AreLunar = false; }

            //            string Strzas = dgvData.Rows[i].Cells["本人手机"].Value.ToString().Trim();
            //            string Strgas = dgvData.Rows[i].Cells["老公手机"].Value.ToString().Trim();

            //            if (dgvData.Rows[i].Cells["本人手机"].Value.ToString().Trim() != "" && dgvData.Rows[i].Cells["本人手机"].Value.ToString().Length == 11)
            //            {
            //                DataRow[] row = dt.Select(" Hct_MyselfMobile='" + dgvData.Rows[i].Cells["本人手机"].Value.ToString().Trim() + "'");
            //                if (dt.Rows.Count > 0)
            //                {
            //                    ok = false;
            //                }
            //                else { model.Hct_MyselfMobile = dgvData.Rows[i].Cells["本人手机"].Value.ToString().Trim(); }
            //            }
            //            else { ok = false; }
            //            model.Hct_HusbandMobile = dgvData.Rows[i].Cells["老公手机"].Value.ToString().Trim();
            //            model.Hct_CustomerRegions = dgvData.Rows[i].Cells["客户区域"].Value.ToString().Trim();
            //            model.Hct_SingleHospital = dgvData.Rows[i].Cells["来单医院"].Value.ToString().Trim();
            //            model.Hct_CreateDatetime = strTime;
            //            model.Hct_CreateName = strUserID;
            //            clist.Add(ambll.GetAddCommandInfo(model));
            //        }
            //        if (clist.Count > 0)
            //        {
            //            MessageBoxCustom.Show("导入成功！");
            //            this.DialogResult = DialogResult.OK;
            //            if (ok == false)
            //            {
            //                Cus.DataToExcel(dgvData, "医院跟踪系统", Url);
            //                MessageBoxCustom.Show(Url);
            //                return;
            //            }
            //        }
            //        else { MessageBoxCustom.Show("导入失败！"); }
            //    }
            //    else { MessageBoxCustom.Show("请导入正确的Excel模板！"); }
            //}
            //else
            //{
            //    MessageBoxCustom.Show("Excel没有数据，请添加后再导入！");
            //}            
            #endregion
        }
        string StringPath = "";
        char[] StrListChar = ",./;'*-+`~!#$%^&<>?|:".ToCharArray();
        string[] StrColumnsName = "来单批号,门市,客户类别,业务意向,来单医院,客户区域,业务种类,客户姓名,客户QQ,本人手机,老公手机,生日类型,宝宝姓名,宝宝性别,喂养方式,生日是否农历,宝宝生日/预产期,怀孕周数,自家地址,自家邮编,自家电话,婆家地址,婆家邮编,婆家电话,娘家地址,娘家邮编,娘家电话,来单日期,来单备注".Split(',');
        /// <summary>
        /// 打开Excel按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void btnOpen_Click(object sender, EventArgs e)
        {
            try
            {
                openFileDialog1.Filter = "Microsoft Excel files(*.xls)|*.xls;*.xlsx";//过滤一下，只要表格格式的
                DialogResult dres = openFileDialog1.ShowDialog();
                if (DialogResult.OK.Equals(dres))
                {
                    System.Data.DataTable tblMember = LYFZ.WinAPI.CustomPublicMethod.GetExcelDatatable(openFileDialog1.FileName); //ExcelToDataTable(openFileDialog1.FileName, "Sheet1");
                    if (tblMember != null)
                    {
                        for (int i = 0; i < StrColumnsName.Length; i++)
                        {
                            if (tblMember.Columns[StrColumnsName[i].Trim()] == null)
                            {
                                MessageBoxCustom.Show("所打开的医院跟踪系统导入模板格式不正确！"); return;
                            }
                        }
                        try
                        {
                            LYFZ.ComponentLibrary.FrmLoadHandling.LoadDoWorkMethod(delegate(object obj, BackgroundWorker backgroundWorker)
                            {
                                backgroundWorker.ReportProgress(0, "正在获取数据...");
                                DataTable tbl = orbll.GetView_Custom("tb_ErpHospitalClient", StrWhere: "", ShowColumnName: "*").Tables[0];
                                Hashtable htData = new Hashtable();

                                #region 数据检查
                                for (int i = 0; i < tblMember.Rows.Count; i++)
                                {
                                    backgroundWorker.ReportProgress(0, "正在检测第" + (i + 1) + "条，共" + tblMember.Rows.Count + "条");
                                    string StrMages = "";
                                    if (tblMember.Rows[i]["来单批号"] == null || string.IsNullOrEmpty(tblMember.Rows[i]["来单批号"].ToString().Trim()))
                                    { StrMages += "来单批号为空；"; }
                                    else if (tblMember.Rows[i]["来单批号"].ToString().Trim().Length > 20)
                                    { StrMages += "来单批号长度超过20；"; }
                                    if (!string.IsNullOrEmpty(tblMember.Rows[i]["来单批号"].ToString().Trim()))
                                    {
                                        DataRow[] dtHt = tbl.Select("Hct_ImportedOrderNumber = '" + tblMember.Rows[i]["来单批号"].ToString().Trim() + "'");
                                        if (dtHt.Length > 0)
                                        { StrMages += "来单批号数据库已存在；"; }

                                        DataRow[] dtMember = tblMember.Select("来单批号 = '" + tblMember.Rows[i]["来单批号"].ToString().Trim() + "'");
                                        if (dtMember.Length > 1)
                                        { StrMages += "导入数据源中来单批号有重复；"; }
                                    }
                                    if (tblMember.Rows[i]["门市"].ToString().Trim().Length > 15)
                                    { StrMages += "门市长度超过15；"; }
                                    if (tblMember.Rows[i]["客户类别"].ToString().Trim().Length > 20)
                                    { StrMages += "客户类别长度超过20；"; }
                                    if (tblMember.Rows[i]["业务意向"].ToString().Trim().Length > 8)
                                    { StrMages += "业务意向长度超过8；"; }
                                    if (tblMember.Rows[i]["来单医院"].ToString().Trim().Length > 200)
                                    { StrMages += "来单医院长度超过200；"; }
                                    if (tblMember.Rows[i]["客户区域"].ToString().Trim().Length > 20)
                                    { StrMages += "客户区域长度超过20；"; }
                                    if (tblMember.Rows[i]["业务种类"].ToString().Trim().Length > 8)
                                    { StrMages += "业务种类长度超过8；"; }
                                    if (string.IsNullOrEmpty(tblMember.Rows[i]["客户姓名"].ToString().Trim()))
                                    { StrMages += "客户姓名为空；"; }
                                    else if (tblMember.Rows[i]["客户姓名"].ToString().Trim().Length > 20)
                                    { StrMages += "客户姓名长度超过20；"; }
                                    if (tblMember.Rows[i]["客户QQ"].ToString().Trim().Length > 15)
                                    { StrMages += "客户QQ长度超过15；"; }
                                    if (!string.IsNullOrEmpty(tblMember.Rows[i]["本人手机"].ToString().Trim()))
                                    {
                                        if (tblMember.Rows[i]["本人手机"].ToString().Trim().Length != 11)
                                        { StrMages += "本人手机长度错误；"; }
                                    }
                                    if (!string.IsNullOrEmpty(tblMember.Rows[i]["老公手机"].ToString().Trim()))
                                    {
                                        if (tblMember.Rows[i]["老公手机"].ToString().Trim().Length != 11)
                                        { StrMages += "老公手机长度错误；"; }
                                    }
                                    if (string.IsNullOrEmpty(tblMember.Rows[i]["生日类型"].ToString().Trim()))
                                    { StrMages += "生日类型为空；"; }
                                    else if (tblMember.Rows[i]["生日类型"].ToString().Trim().Length > 10)
                                    { StrMages += "生日类型长度超过10；"; }

                                    if (!string.IsNullOrEmpty(tblMember.Rows[i]["生日类型"].ToString().Trim()))
                                    {
                                        if (tblMember.Rows[i]["生日类型"].ToString().Trim() == "宝宝生日")
                                        {
                                            if (!string.IsNullOrEmpty(tblMember.Rows[i]["宝宝姓名"].ToString().Trim()))
                                            {
                                                if (tblMember.Rows[i]["宝宝姓名"].ToString().Trim().Length > 20)
                                                { StrMages += "宝宝姓名长度超过20；"; }
                                                if (string.IsNullOrEmpty(tblMember.Rows[i]["宝宝性别"].ToString().Trim()))
                                                { StrMages += "宝宝性别为空；"; }
                                                else if (tblMember.Rows[i]["宝宝性别"].ToString().Trim().Length > 2)
                                                { StrMages += "宝宝性别长度超过2；"; }
                                                if (tblMember.Rows[i]["宝宝性别"].ToString().Trim() != "男" && tblMember.Rows[i]["宝宝性别"].ToString().Trim() != "女")
                                                { StrMages += "宝宝性别输入错误；"; }
                                            }
                                            if (tblMember.Rows[i]["喂养方式"].ToString().Trim().Length > 20)
                                            { StrMages += "喂养方式长度超过20；"; }
                                            if (!string.IsNullOrEmpty(tblMember.Rows[i]["宝宝生日/预产期"].ToString().Trim()))
                                            {
                                                if (!string.IsNullOrEmpty(LYFZ.Command.Command_Validate.DateTimeToString(tblMember.Rows[i]["宝宝生日/预产期"])))
                                                {
                                                    if (tblMember.Rows[i]["生日是否农历"].ToString().Trim().Length > 2)
                                                    { StrMages += "生日是否农历长度超过2；"; }
                                                    if (!string.IsNullOrEmpty(tblMember.Rows[i]["生日是否农历"].ToString().Trim()))
                                                    {
                                                        if (tblMember.Rows[i]["生日是否农历"].ToString().Trim() != "公历" && tblMember.Rows[i]["生日是否农历"].ToString().Trim() != "公历")
                                                        { StrMages += "生日是否农历输入错误；"; }
                                                    }
                                                }
                                                else
                                                { StrMages += "宝宝生日格式错误；"; }
                                            }
                                        }
                                        else if (tblMember.Rows[i]["生日类型"].ToString().Trim() == "预产期")
                                        {
                                            if (!string.IsNullOrEmpty(tblMember.Rows[i]["宝宝生日/预产期"].ToString().Trim()))
                                            {
                                                if (!string.IsNullOrEmpty(LYFZ.Command.Command_Validate.DateTimeToString(tblMember.Rows[i]["宝宝生日/预产期"])))
                                                { }
                                                else
                                                { StrMages += "预产期格式错误；"; }
                                            }

                                            if (!string.IsNullOrEmpty(tblMember.Rows[i]["怀孕周数"].ToString().Trim()))
                                            {
                                                try { Convert.ToInt32(tblMember.Rows[i]["怀孕周数"].ToString().Trim()); }
                                                catch
                                                { StrMages += "怀孕周数格式错误；"; }
                                            }
                                        }
                                        else
                                        { StrMages += "生日类型输入错误；"; }
                                    }

                                    if (tblMember.Rows[i]["自家地址"].ToString().Trim().Length > 250)
                                    { StrMages += "自家地址长度超过250；"; }
                                    if (!string.IsNullOrEmpty(tblMember.Rows[i]["自家邮编"].ToString().Trim()))
                                    {
                                        if (tblMember.Rows[i]["自家邮编"].ToString().Trim().Length != 6)
                                        { StrMages += "自家邮编长度超过不正确；"; }
                                    }
                                    if (tblMember.Rows[i]["自家电话"].ToString().Trim().Length > 13)
                                    { StrMages += "自家电话长度超过13；"; }
                                    if (tblMember.Rows[i]["婆家地址"].ToString().Trim().Length > 250)
                                    { StrMages += "婆家地址长度超过250；"; }
                                    if (!string.IsNullOrEmpty(tblMember.Rows[i]["婆家邮编"].ToString().Trim()))
                                    {
                                        if (tblMember.Rows[i]["婆家邮编"].ToString().Trim().Length != 6)
                                        { StrMages += "婆家邮编长度超过不正确；"; }
                                    }
                                    if (tblMember.Rows[i]["婆家电话"].ToString().Trim().Length > 13)
                                    { StrMages += "婆家电话长度超过13；"; }
                                    if (tblMember.Rows[i]["娘家地址"].ToString().Trim().Length > 250)
                                    { StrMages += "娘家地址长度超过250；"; }
                                    if (!string.IsNullOrEmpty(tblMember.Rows[i]["娘家邮编"].ToString().Trim()))
                                    {
                                        if (tblMember.Rows[i]["娘家邮编"].ToString().Trim().Length != 6)
                                        { StrMages += "娘家邮编长度超过不正确；"; }
                                    }
                                    if (tblMember.Rows[i]["娘家电话"].ToString().Trim().Length > 13)
                                    { StrMages += "娘家电话长度超过13；"; }
                                    if (!string.IsNullOrEmpty(tblMember.Rows[i]["来单日期"].ToString().Trim()))
                                    {
                                        if (string.IsNullOrEmpty(LYFZ.Command.Command_Validate.DateTimeToString(tblMember.Rows[i]["来单日期"])))
                                        { StrMages += "来单日期格式错误；"; }
                                    }
                                    if (tblMember.Rows[i]["来单备注"].ToString().Trim().Length > 700)
                                    { StrMages += "来单备注长度超过700；"; }

                                    if (!string.IsNullOrEmpty(StrMages))
                                    { htData[tblMember.Rows[i]] = StrMages; }
                                }
                                #endregion

                                backgroundWorker.ReportProgress(0, "正在处理数据...");
                                DataTable NewTable = new DataTable();
                                for (int i = 0; i <= tblMember.Columns.Count; i++)
                                {
                                    if (i == tblMember.Columns.Count)
                                    { NewTable.Columns.Add("错误说明", typeof(string)); }
                                    else
                                    { NewTable.Columns.Add(tblMember.Columns[i].ColumnName.Trim(), typeof(string)); }
                                }

                                foreach (DictionaryEntry item in htData)
                                {
                                    try
                                    {
                                        DataRow dtRow = (DataRow)item.Key;

                                        DataRow newRow = NewTable.NewRow();
                                        for (int j = 0; j < dtRow.Table.Columns.Count; j++)
                                        {
                                            switch (dtRow.Table.Columns[j].ColumnName.Trim())
                                            {
                                                //case "宝宝生日/预产期":
                                                //case "来单日期":
                                                //    if (!string.IsNullOrEmpty(dtRow[dtRow.Table.Columns[j].ColumnName.Trim()].ToString().Trim()))
                                                //    { newRow[dtRow.Table.Columns[j].ColumnName.Trim()] = Convert.ToDateTime(dtRow[dtRow.Table.Columns[j].ColumnName.Trim()]).ToString("yyyy-MM-dd"); }
                                                //    else
                                                //    { newRow[dtRow.Table.Columns[j].ColumnName.Trim()] = ""; }
                                                //    break;
                                                default:
                                                    newRow[dtRow.Table.Columns[j].ColumnName.Trim()] = dtRow[dtRow.Table.Columns[j].ColumnName.Trim()].ToString().Trim();
                                                    break;
                                            }
                                        }
                                        newRow["错误说明"] = item.Value.ToString().Trim();
                                        NewTable.Rows.Add(newRow);
                                        tblMember.Rows.Remove(dtRow);
                                    }
                                    catch
                                    { }
                                }
                                try
                                {
                                    this.Invoke(new LYFZ.Software.MainBusiness.DoorCityProcess.PublicCodeClasses.UpdateControl(delegate()
                                    {
                                        this.dgvData.DataSource(tblMember, DataSourceTopCount: 950);
                                        newDt = tblMember;
                                    }));
                                }
                                catch
                                { }
                                if (NewTable.Rows.Count > 0)
                                {
                                    StringPath = System.IO.Path.GetDirectoryName(openFileDialog1.FileName) + "\\" + System.IO.Path.GetFileNameWithoutExtension(openFileDialog1.FileName);
                                    string StrFilePath = StringPath + "_有问题数据.xls"; //+ System.IO.Path.GetExtension(openFileDialog1.FileName);

                                    backgroundWorker.ReportProgress(0, "正在导出有问题数据...");
                                    //bool IsTrue = LYFZ.Command.Command_ExportExcel.DataTable2Sheet(StrFilePath, NewTable, "Sheet1", IsCoveredExistingFile: true);
                                    bool IsTrue = LYFZ.WinAPI.CustomPublicMethod.DataGridViewToExcel(StrFilePath, NewTable, null, backgroundWorker);
                                    if (IsTrue)
                                    {
                                        try
                                        {
                                            this.Invoke(new LYFZ.Software.MainBusiness.DoorCityProcess.PublicCodeClasses.UpdateControl(delegate()
                                            { MessageBoxCustom.Show("检查数据完毕！\r\n要导入的Excel中有问题数据已挑出另存于：\r\n" + StrFilePath); }));
                                        }
                                        catch
                                        { }
                                    }
                                }
                            });
                        }
                        catch
                        { }

                        //dgvExcel.DataSource(dt);
                        //for (int i = 0; i < this.dgvExcel.Columns.Count; i++)
                        //{ this.dgvExcel.Columns[i].SortMode = DataGridViewColumnSortMode.NotSortable; }
                    }
                }
            }
            catch (Exception ex)
            { MessageBoxCustom.Show(ex.Message); }
        }
        /// <summary>
        /// 生成来单编号
        /// </summary>
        public string GenerationNumber(int IndexCount = 0)
        {
            string StrNumber = "";
            DataTable dt = orbll.GetView_Custom("tb_ErpHospitalClient", filedOrder: "Hct_Oddnumber desc", ShowColumnName: "Hct_Oddnumber", TopCount: 1).Tables[0];
            if (dt.Rows.Count > 0)
            {
                string[] Strshu = dt.Rows[0]["Hct_Oddnumber"].ToString().Split('-');
                if (Convert.ToInt32(Strshu[0].ToString()) >= Convert.ToInt32(SDateTime.Now.ToString("yyyyMMdd")))
                { StrNumber = Strshu[0].ToString() + "-" + (Convert.ToInt32(Strshu[1]) + 1 + IndexCount).ToString("d4"); }
                else
                { StrNumber = SDateTime.Now.ToString("yyyyMMdd") + "-" + (1 + IndexCount).ToString("d4"); }
            }
            else
            { StrNumber = SDateTime.Now.ToString("yyyyMMdd") + "-" + (1 + IndexCount).ToString("d4"); ; }
            return StrNumber;
        }
        /// <summary>
        /// /
        /// </summary>
        /// <param name="lie"></param>
        /// <returns></returns>
        public string StrDianhua(string lie)
        {
            string str = "";
            for (int i = 0; i < dgvData.Rows.Count; i++)
            {
                str += "'" + dgvData.Rows[i].Cells[lie].Value.ToString().Trim() + "',";
            }
            str = str.Substring(0, str.Length - 1);
            return str;
        }
    }
}