コード例 #1
0
ファイル: Sample018.aspx.cs プロジェクト: tony80842/NewRepo3
        protected void DbInit()
        {
            DataTable dt = new DataTable(), dt2 = new DataTable();

            ErrorGV.DataSource = null;
            ErrorGV.DataBind();
            SamGV.DataSource = null;
            SamGV.DataBind();
            try
            {
                using (SqlConnection Conn = new SqlConnection(strConnectString))
                {
                    SqlDataAdapter myAdapter = new SqlDataAdapter(selectsql("CheckData").ToString(), Conn);
                    myAdapter.Fill(dt);    //---- 這時候執行SQL指令。取出資料,放進 DataSet。
                }
                #region query 使用 In
                //        using (SqlConnection conn1 = new SqlConnection(strConnectString))
                //        {
                //            SqlCommand command1 = conn1.CreateCommand();
                //            SqlTransaction transaction1;
                //            conn1.Open();
                //            transaction1 = conn1.BeginTransaction("createExcelImport");
                //            try
                //            {
                //                command1.Connection = conn1;
                //                command1.Transaction = transaction1;

                //                #region 匯入
                //                string[] parameters = SamArray.Select((s, i) => "@sam_nbr" + i.ToString()).ToArray();
                //                command1.CommandText = string.Format(@"SELECT          a.receipt_date AS 發版日期,
                //                    b.cus_name AS 客戶名稱, a.cus_style_no AS 款號,
                //                    a.sam_nbr AS 打樣單號, dbo.F_DateToNull(a.samc_fin_date) AS 打版完成日期,
                //                    a.samc_remark60 AS 備註, a.plan_fin_date AS 預計完成日,
                //                    a.online_date AS 上線日期, a.samc_plan_fin_date AS 打版預計完成日,
                //                    a.plan_fin_date AS 預計完日, dbo.F_DateToNull(a.last_date) AS 需求日
                //                    ,dbo.F_DateToNull(a.samc_fin_date) 打版完成日
                //from samc_reqm a left join bas_cus_master b on a.site=b.site and a.cus_id=b.cus_id
                //                     where sam_nbr in ( {0} ) and a.site='GGF'
                //                     ", string.Join(",", parameters));
                //                command1.Parameters.Add("@samc_fin_date", SqlDbType.DateTime).Value = DateRangeTB.Text;
                //                for (int i = 0; i < SamArray.Length; i++)
                //                    command1.Parameters.AddWithValue(parameters[i], SamArray[i]);
                //                //command1.Parameters.Add("@sam_nbr", SqlDbType.DateTime).Value = DateRangeTB.Text;
                //                command1.ExecuteNonQuery();
                //                SqlDataReader dr = command1.ExecuteReader(CommandBehavior.CloseConnection);
                //                dt.Load(dr);
                //                #endregion
                //                //transaction1.Commit();
                //            }
                //            catch (Exception ex)
                //            {
                //                Log.ErrorLog(ex, "上傳失敗", StrProgram);
                //                transaction1.Rollback();
                //                throw;
                //            }
                //            finally
                //            {
                //                conn1.Close();
                //                transaction1.Dispose();
                //            }
                //        }
                #endregion
                if (dt.Rows.Count > 0)
                {
                    DataView dv = new DataView(dt);
                    dv.RowFilter       = " 打樣單狀態 = '無資料' or 打版完成日 = '無打版完成日'";
                    ErrorGV.DataSource = dv.ToTable();
                    ErrorGV.DataBind();
                    dv.RowFilter = String.Empty;
                    dv.RowFilter = " 打樣單狀態 = '有打樣單' and 打版完成日 <> '無打版完成日' ";

                    dt2          = dv.ToTable();
                    dv.RowFilter = String.Empty;
                    DataTable dt4 = new DataTable();
                    dv.RowFilter = "打版完成日 = '無打版完成日' and 打樣單狀態 = '有打樣單' ";
                    dt4          = dv.ToTable();
                    if (dt4.Rows.Count > 0)
                    {
                        SystemFunction systemFunction = new SystemFunction();
                        StringBuilder  sb             = new StringBuilder();
                        sb.Append(@"<h1><strong>樣品主副料到齊,未收到版套</strong></h1>
                                                <table style='width: 498px; height: 180px;' border='1'>
                                                <tbody>
                                                <tr style='height: 52px;'>
                                                <td style='width: 136px; height: 52px; text-align: center;'>
                                                <h1><strong>打樣單號</strong></h1>
                                                </td>
                                                <td style='width: 143px; height: 52px; text-align: center;'>
                                                <h1><strong>款號</strong></h1>
                                                </td>
                                                <td style='width: 130px; height: 52px;'>
                                                <h1 style='text-align: center;'><strong>客戶代號</strong></h1>
                                                </td>
                                                </tr>");
                        foreach (DataRow item in dt4.Rows)
                        {
                            sb.AppendFormat(@"<tr style='height: 53.4688px;'>
                                                        <td style='width: 136px; height: 53.4688px; text-align: center;'><strong>{0}</strong></td>
                                                        <td style='width: 143px; height: 53.4688px; text-align: center;'><strong>{1}</strong></td>
                                                        <td style='width: 130px; height: 53.4688px; text-align: center;'><strong>{2}</strong></td>
                                                        </tr>", item["打樣單號"].ToString(), item["款號"].ToString(), item["客戶代號"].ToString());
                        }
                        sb.Append("</tbody></ table > ");
                        systemFunction.SendMail("[email protected];[email protected];", "樣品主副料到期,未收到版套", sb.ToString());
                    }
                    if (dt2.Rows.Count > 0)
                    {
                        if (UpDate(dt2))
                        {
                            using (SqlConnection Conn = new SqlConnection(strConnectString))
                            {
                                DataTable      dt3       = new DataTable();
                                SqlDataAdapter myAdapter = new SqlDataAdapter(selectsql("Search", dt2).ToString(), Conn);
                                myAdapter.Fill(dt3);    //---- 這時候執行SQL指令。取出資料,放進 DataSet。
                                SamGV.DataSource = dt3;
                                SamGV.DataBind();
                            }
                        }
                    }
                    //TESTGV.DataBind();
                }
                else
                {
                    F_ErrorShow("搜尋不到資料");
                }
            }
            catch (Exception ex)
            {
                Log.ErrorLog(ex, "資料搜尋異常", StrProgram);
                F_ErrorShow("資料搜尋異常");
                //throw;
            }
        }