摘要:使用key word 搜尋篩選的方式
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace GlobalReport.VesselSchedule
{
public partial class BookingMain : Form
{
public BookingMain()
{
InitializeComponent();
comboBox2.SelectedIndex = 0;
Set_Station();
}
public void Set_Station()
{
string[] sa = GlobalParam.BookingLoginUser.StationData.Split(',');
comboBox1.Items.Clear();
for (int i = 0; i < sa.Length; i++)
{
comboBox1.Items.Add(sa[i]);
}
if (comboBox1.Items.Count > 0) comboBox1.SelectedIndex = 0;
}
private string DateToShow(string sDate)
{
if (sDate.Length == 8)
{
return sDate.Substring(4, 2) + "/" + sDate.Substring(6, 2);// +"-" + sDate.Substring(0, 4);
}
else
{
return "";
}
}
private DataSet Last_DataSet = null;
public void Load_Data(string data_stn)
{
if (data_stn == "") return;
//GlobalParam.BookingLoginUser.
string sCond_line = " ";
if (comboBox2.SelectedIndex == 0) sCond_line = " ";
if (comboBox2.SelectedIndex > 0 && comboBox2.SelectedIndex <= 10) sCond_line = " AND LINE_CD='"
+ comboBox2.SelectedIndex.ToString() + "' ";
if (comboBox2.SelectedIndex == 11) sCond_line = " AND LINE_CD='A' ";
// 有可能出現 VOY_NO , DATA_STN 不是KEY 的狀況, 比如不同 PORT , (KHH / KEL) 如果用LEFT JOIN 會變成2筆資料, 但是實際BK 的資料可能會對不上主檔
WebServiceEncodeLib ws = new WebServiceEncodeLib();
string sSQL = //"SELECT M.VOY_NO,M.LINE_CD,M.VESSEL,M.VSLNAME,M.VOYAGE,M.CARRIER,M.CARR_NAME, "
//+ " M.LINE_NAME ,D.PORT,D.PORTNAME,D.CRTY,D.CLS_DATE,D.CFS_CLS_DATE,D.DOC_CLS_DATE, "
//+ " D.ETD_DATE,M.VSL_CD,M.DATA_STN,(SELECT TEU FROM GBL_SC_SPACE S WHERE S.DATA_STN=M.DATA_STN AND S.VOY_NO=M.VOY_NO AND S.POL_CD=D.PORT)TEU, "
//+ " (SELECT SUM(C20+C40*2+C40HQ*2.25+C45*2.532) FROM GBL_SC_BK S WHERE S.DATA_STN=M.DATA_STN AND S.VOY_NO=M.VOY_NO AND S.POL_CD=D.PORT)PRE_BK "
//+ " FROM SEMSKD M LEFT JOIN SEMSKDB D ON (M.VOY_NO=D.VOY_NO AND M.DATA_STN = D.DATA_STN) "
" SELECT * FROM GBL_VIEW_BK02 "
+ " WHERE DEP='OE' " + sCond_line + " AND ETD_DATE > '" + DateTime.Now.ToString("yyyyMMdd") + "' AND DATA_STN ='"
+ data_stn + "' ORDER BY CARRIER , ETD_DATE ";
string sSQL_PRE_BK = "SELECT GBL_SC_BK.*,(C20+C40*2+C40HQ*2.25+C45*2.532)TEU FROM GBL_SC_BK WHERE (VOY_NO , DATA_STN , POL_CD) IN "
+ "( SELECT M.VOY_NO , M.DATA_STN,D.PORT FROM SEMSKD M LEFT JOIN SEMSKDB D ON (M.VOY_NO=D.VOY_NO AND M.DATA_STN = D.DATA_STN) "
+ " WHERE M.DEP='OE' " + sCond_line + " AND D.ETD_DATE > '" + DateTime.Now.ToString("yyyyMMdd")
+ "' AND M.DATA_STN =" + GlobalLib.quotoStr(data_stn) + " ) ";
string sSQL_TRUE_BK = //"SELECT B.SHIP_NAME,B.CREATE_USER,B.CREATE_DATE,B.CREATE_TIME ,B.SALES_NO,B.CNEE_NM,B.CBM,B.DATA_STN,B.VOY_NO,B.POL_CD,B.AGENT, "
//+ " (select sum(decode(get_cnt_type(CNT_TYPE),'20',CNT_QTY ,'40',2*CNT_QTY,'40HQ',2.25*CNT_QTY,'45',2.532*CNT_QTY)) from SECNTQTY S where S.BL_NO = B.JOB_NO)STEU FROM BKMSHNT B "
"SELECT * FROM GBL_VIEW_BK "
+ " WHERE (VOY_NO, DATA_STN , POL_CD ) IN ( SELECT M.VOY_NO , M.DATA_STN,D.PORT FROM SEMSKD M LEFT JOIN SEMSKDB D ON (M.VOY_NO=D.VOY_NO AND M.DATA_STN = D.DATA_STN) "
+ " WHERE M.DEP='OE' " + sCond_line + " AND D.ETD_DATE > '" + DateTime.Now.ToString("yyyyMMdd")
+ "' AND M.DATA_STN =" + GlobalLib.quotoStr(data_stn) + " ) ";
DataSet ds = ws.getDataSetXml(sSQL);
DataSet ds_PRE_BK = ws.getDataSetXml(sSQL_PRE_BK);
DataSet ds_TRUE_BK = ws.getDataSetXml(sSQL_TRUE_BK);
ds.Tables[0].TableName = "Master";
ds_PRE_BK.Tables[0].TableName = "PRE_BK";
ds_TRUE_BK.Tables[0].TableName = "TRUE_BK";
ds.Merge(ds_PRE_BK);
DataRelation r = new DataRelation("Tablet-BK Level", new DataColumn[] { ds.Tables["Master"].Columns["VOY_NO"], ds.Tables["Master"].Columns["DATA_STN"], ds.Tables["Master"].Columns["PORT"] }
, new DataColumn[] { ds.Tables["PRE_BK"].Columns["VOY_NO"], ds.Tables["PRE_BK"].Columns["DATA_STN"], ds.Tables["PRE_BK"].Columns["POL_CD"] });
ds.Relations.Add(r);
ds.Merge(ds_TRUE_BK);
DataRelation rr = new DataRelation("Prolink-BK Level", new DataColumn[] { ds.Tables["Master"].Columns["VOY_NO"], ds.Tables["Master"].Columns["DATA_STN"], ds.Tables["Master"].Columns["PORT"] }
, new DataColumn[] { ds.Tables["TRUE_BK"].Columns["VOY_NO"], ds.Tables["TRUE_BK"].Columns["DATA_STN"], ds.Tables["TRUE_BK"].Columns["POL_CD"] });
ds.Relations.Add(rr);
foreach (DataRow item in ds.Tables[0].Rows)
{
item["CLS_DATE"] = DateToShow(item["CLS_DATE"].ToString());
item["CFS_CLS_DATE"] = DateToShow(item["CFS_CLS_DATE"].ToString());
item["DOC_CLS_DATE"] = DateToShow(item["DOC_CLS_DATE"].ToString());
item["ETD_DATE"] = DateToShow(item["ETD_DATE"].ToString());
}
ds.AcceptChanges();
Last_DataSet = ds.Copy();
SetDataSetToGrid(ds);
gridColumn2.OptionsColumn.ReadOnly = ! GlobalParam.BookingLoginUser.CanSetTotalContainer;
//gridView1.men
}
private void comboBox1_TextChanged(object sender, EventArgs e)
{
Load_Data(comboBox1.Text);
}
private void BookingMain_Load(object sender, EventArgs e)
{
// TODO: 這行程式碼會將資料載入 '新增_Microsoft_Access_資料庫DataSet.VesselM' 資料表。您可以視需要進行移動或移除。
//this.vesselMTableAdapter.Fill(this.新增_Microsoft_Access_資料庫DataSet.VesselM);
}
private void SetDataSetToGrid(DataSet ds)
{
gridControl1.DataSource = ds.Tables[0];
gridView1.ExpandAllGroups();
}
private void textEdit1_KeyDown(object sender, KeyEventArgs e)
{
if (e.KeyCode == Keys.Enter)
{
//先確定需要搜尋
string keyword = textEdit1.Text.Trim();
if (keyword == "")
{
SetDataSetToGrid(Last_DataSet);
return;
}
//將當初展開的資料集, 複製一份當作臨時資料集
DataSet ds = Last_DataSet.Copy();
// 在臨時資料集中, 把沒關鍵字的資料刪除掉
string[] sa = textEdit1.Text.Trim().Split(' '); //用空白切字串
//// OR 查詢的作法
//bool bDel = true;
//foreach (DataRow item in ds.Tables[0].Rows)
//{
// bDel = true;
// for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
// {
// for (int j = 0; j < sa.Length; j++)
// {
// if (sa[j].Trim() == "") continue;
// if (item[i].ToString().IndexOf(sa[j]) >= 0) bDel = false;
// }
// }
// if (bDel) item.Delete();
//}
//ds.AcceptChanges();
//SetDataSetToGrid(ds);
// AND 查詢的作法
bool bFind = true;
foreach (DataRow item in ds.Tables[0].Rows)
{
string s = "";
//以一個row 為單位, 要這個row 同時有查詢字串才算
for (int i = 0; i < ds.Tables[0].Columns.Count; i++) s = s + " " + item[i].ToString();
bFind = true;
for (int j = 0; j < sa.Length; j++)
{
if (sa[j].Trim() == "") continue;
if (s.IndexOf(sa[j]) < 0 )
bFind = false;
}
if (!bFind) item.Delete();
}
ds.AcceptChanges();
SetDataSetToGrid(ds);
}
}
private void gridView1_CustomDrawCell(object sender, DevExpress.XtraGrid.Views.Base.RowCellCustomDrawEventArgs e)
{
if (textEdit1.Text != "")
{
string[] sa = textEdit1.Text.Trim().Split(' ');
for (int i = 0; i < sa.Length; i++)
{
if (sa[i] != "")
{
if (e.DisplayText.IndexOf(sa[i]) >= 0)
{
e.Appearance.BackColor = Color.Pink;
}
}
}
}
}
private void panel1_Paint(object sender, PaintEventArgs e)
{
}
private void comboBox2_TextChanged(object sender, EventArgs e)
{
Load_Data(comboBox1.Text);
}
///
/// 呼叫新增Booking 畫面
///
///
///
private void simpleButton1_Click(object sender, EventArgs e)
{
if (gridView1.FocusedRowHandle < 0) return;
string VOY_NO = gridView1.GetDataRow(gridView1.FocusedRowHandle)["VOY_NO"].ToString();
string DATA_STN = gridView1.GetDataRow(gridView1.FocusedRowHandle)["DATA_STN"].ToString();
string PORT = gridView1.GetDataRow(gridView1.FocusedRowHandle)["PORT"].ToString();
string ETD_DATE = gridView1.GetDataRow(gridView1.FocusedRowHandle)["ETD_DATE"].ToString();
BookingAddNew f = new BookingAddNew();
f.setData(VOY_NO, DATA_STN, GlobalParam.BookingLoginUser.UserAccount, PORT);
f.tbCust.AutoCompleteCustomSource.Clear();
foreach (DataRow item in Last_DataSet.Tables["PRE_BK"].Rows)
{
if (f.tbCust.AutoCompleteCustomSource.IndexOf(item["CUST"].ToString())==-1)
f.tbCust.AutoCompleteCustomSource.Add( item["CUST"].ToString());
}
f.tbCust.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
f.tbCust.AutoCompleteSource = AutoCompleteSource.CustomSource;
f.tbSales.AutoCompleteCustomSource.Clear();
foreach (DataRow item in Last_DataSet.Tables["PRE_BK"].Rows)
{
if (f.tbSales.AutoCompleteCustomSource.IndexOf(item["SALES"].ToString()) == -1)
f.tbSales.AutoCompleteCustomSource.Add(item["SALES"].ToString());
}
f.tbSales.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
f.tbSales.AutoCompleteSource = AutoCompleteSource.CustomSource;
f.ShowDialog();
//判斷是新增或是取消, 如果新增就要刷新
if (f.NeedReload) Load_Data(comboBox1.Text);
}
private void gridView1_CellValueChanged(object sender, DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs e)
{
//
}
private void labelControl2_Click(object sender, EventArgs e)
{
}
}
}