Last Updated: February 25, 2016
·
903
· vitochung

DevExpress - edit multi-table in one form

DataSet ds = new DataSet();
string tablename;

protected override void InitForm()
{
    CommObjects.SetOptionComboBox(leStp, 448);
    CommonFuncs.RemoveKeyDownEvent(leStp);
    leStp.ItemIndex = 0;

    PfoInfo.SetLookupEditPair(lePno, lePnoName);
    CommonFuncs.RemoveKeyDownEvent(lePno);
    CommonFuncs.RemoveKeyDownEvent(lePnoName);
    lePno.ItemIndex = 0;

    deDate.Enabled = false;

    foreach (Control c in gpQuery.Controls)
    {
        BaseFuncs.SetControlProperty(c);
    }

    foreach (Control c in pnlData.Controls)
    {
        BaseFuncs.SetControlProperty(c);
    }
    btnNew.Visibility = DevExpress.XtraBars.BarItemVisibility.Never;
    btnDelete.Visibility = DevExpress.XtraBars.BarItemVisibility.Never;
    btnQuery.Visibility = DevExpress.XtraBars.BarItemVisibility.Never;
    btnTool.Visibility = DevExpress.XtraBars.BarItemVisibility.Never;

    //ds.Tables.Add(AppFuncs.GetTable("cash_balance"));
    //ds.Tables.Add(AppFuncs.GetTable("td_balance"));
    //ds.Tables.Add(AppFuncs.GetTable("stb_balance"));
}

private void leStp_EditValueChanged(object sender, EventArgs e)
{
    if (CommonFuncs.ConvertEditValue<int>(leStp) == 1)  //活存
    {
        tablename = "cash_balance";
    }
    else if (CommonFuncs.ConvertEditValue<int>(leStp) == 2)  //定存
    {
        tablename = "td_balance";
    }
    else if (CommonFuncs.ConvertEditValue<int>(leStp) == 3)  //短票RP
    {
        tablename = "stb_balance";
    }
    else
    {
        ShowMessage("商品類別有誤");
        return;
    }

    ceAccuInt.DataBindings.Clear();
    ceIntAfadj.DataBindings.Clear();
    ceAccuTax.DataBindings.Clear();
    ceTaxAfadj.DataBindings.Clear();

    if (CommonFuncs.ConvertEditValue<int>(leStp) == 1)  //活存
    {
        lbAccuTax.Visible = false;
        ceAccuTax.Visible = false;
        lbTaxAfadj.Visible = false;
        ceTaxAfadj.Visible = false;
    }
    else
    {
        lbAccuTax.Visible = true;
        ceAccuTax.Visible = true;
        lbTaxAfadj.Visible = true;
        ceTaxAfadj.Visible = true;
    }

    gvMain.Columns.Clear();
    bsMain.DataSource = null;
}

private void setCalsField()
{
    //增加setCals欄位
    if (CommonFuncs.ConvertEditValue<int>(leStp) == 1)  //活存
    {
        DevExpress.XtraGrid.Columns.GridColumn colcontractno = new DevExpress.XtraGrid.Columns.GridColumn();
        colcontractno.FieldName = "@contractno";
        colcontractno.Name = "colcontractno";
        colcontractno.UnboundType = DevExpress.Data.UnboundColumnType.String;
        colcontractno.Visible = true;
        if (gvMain.Columns.ColumnByName("colcontractno") == null) gvMain.Columns.Add(colcontractno);

        DevExpress.XtraGrid.Columns.GridColumn colcbrief = new DevExpress.XtraGrid.Columns.GridColumn();
        colcbrief.FieldName = "@cbrief";
        colcbrief.Name = "colcbrief";
        colcbrief.UnboundType = DevExpress.Data.UnboundColumnType.String;
        colcbrief.Visible = true;
        if (gvMain.Columns.ColumnByName("colcbrief") == null) gvMain.Columns.Add(colcbrief);

        DevExpress.XtraGrid.Columns.GridColumn colbankbrief = new DevExpress.XtraGrid.Columns.GridColumn();
        colbankbrief.FieldName = "@bankbrief";
        colbankbrief.Name = "colbankbrief";
        colbankbrief.UnboundType = DevExpress.Data.UnboundColumnType.String;
        colbankbrief.Visible = true;
        if (gvMain.Columns.ColumnByName("colbankbrief") == null) gvMain.Columns.Add(colbankbrief);
    }
    else if (CommonFuncs.ConvertEditValue<int>(leStp) == 2)  //定存
    {
        DevExpress.XtraGrid.Columns.GridColumn colcontractno = new DevExpress.XtraGrid.Columns.GridColumn();
        colcontractno.FieldName = "@contractno";
        colcontractno.Name = "colcontractno";
        colcontractno.UnboundType = DevExpress.Data.UnboundColumnType.String;
        colcontractno.Visible = true;
        if (gvMain.Columns.ColumnByName("colcontractno") == null) gvMain.Columns.Add(colcontractno);

        DevExpress.XtraGrid.Columns.GridColumn colcbrief = new DevExpress.XtraGrid.Columns.GridColumn();
        colcbrief.FieldName = "@cbrief";
        colcbrief.Name = "colcbrief";
        colcbrief.UnboundType = DevExpress.Data.UnboundColumnType.String;
        colcbrief.Visible = true;
        if (gvMain.Columns.ColumnByName("colcbrief") == null) gvMain.Columns.Add(colcbrief);

        DevExpress.XtraGrid.Columns.GridColumn colbrokerbrief = new DevExpress.XtraGrid.Columns.GridColumn();
        colbrokerbrief.FieldName = "@brokerbrief";
        colbrokerbrief.Name = "colbrokerbrief";
        colbrokerbrief.UnboundType = DevExpress.Data.UnboundColumnType.String;
        colbrokerbrief.Visible = true;
        if (gvMain.Columns.ColumnByName("colbrokerbrief") == null) gvMain.Columns.Add(colbrokerbrief);

    }
    else if (CommonFuncs.ConvertEditValue<int>(leStp) == 3)  //短票RP
    {
        DevExpress.XtraGrid.Columns.GridColumn colcontractno = new DevExpress.XtraGrid.Columns.GridColumn();
        colcontractno.FieldName = "@contractno";
        colcontractno.Name = "colcontractno";
        colcontractno.UnboundType = DevExpress.Data.UnboundColumnType.String;
        colcontractno.Visible = true;
        if (gvMain.Columns.ColumnByName("colcontractno") == null) gvMain.Columns.Add(colcontractno);

        DevExpress.XtraGrid.Columns.GridColumn colcbrief = new DevExpress.XtraGrid.Columns.GridColumn();
        colcbrief.FieldName = "@cbrief";
        colcbrief.Name = "colcbrief";
        colcbrief.UnboundType = DevExpress.Data.UnboundColumnType.String;
        colcbrief.Visible = true;
        if (gvMain.Columns.ColumnByName("colcbrief") == null) gvMain.Columns.Add(colcbrief);

        DevExpress.XtraGrid.Columns.GridColumn colvouno = new DevExpress.XtraGrid.Columns.GridColumn();
        colvouno.FieldName = "@vouno";
        colvouno.Name = "colvouno";
        colvouno.UnboundType = DevExpress.Data.UnboundColumnType.String;
        colvouno.Visible = true;
        if (gvMain.Columns.ColumnByName("colvouno") == null) gvMain.Columns.Add(colvouno);

        DevExpress.XtraGrid.Columns.GridColumn colvounobrief = new DevExpress.XtraGrid.Columns.GridColumn();
        colvounobrief.FieldName = "@vounobrief";
        colvounobrief.Name = "colvounobrief";
        colvounobrief.UnboundType = DevExpress.Data.UnboundColumnType.String;
        colvounobrief.Visible = true;
        if (gvMain.Columns.ColumnByName("colvounobrief") == null) gvMain.Columns.Add(colvounobrief);

    }
}
private void sortField(params string[] colname)
{
    //欄位排序
    for (int i = 0; i < colname.Length; i++)
    {
        string tmp = colname.GetValue(i).ToString();
        if (tmp.Contains("@")) tmp = tmp + "$";
        gvMain.Columns[tmp].VisibleIndex = i;
        gvMain.Columns[tmp].AbsoluteIndex = i;
    }

    //隱藏部分欄位
    for (int i = colname.Length; i < gvMain.Columns.Count; i++)
    {
        gvMain.Columns[i].Visible = false;
    }
}
private void setFiledName(params string[] columnCaption)
{
    //設定欄位名稱
    for (int i = 0; i < columnCaption.Length; i++)
    {
        gvMain.Columns[i].Caption = columnCaption[i];
    }
}

private void lePno_EditValueChanged(object sender, EventArgs e)
{
    //交易日期預設為契約營業日
    DateTime NextDate; ProcessType NexttTp;
    DateTime excDate; ProcessType proctp;
    CommonUtility.PfoInfo.GetStatus(CommonFuncs.ConvertEditValue<int>(lePno), out NextDate, out NexttTp, out excDate, out proctp);

    deDate.EditValue = excDate;
}

private void sbQuery_Click(object sender, EventArgs e)
{
    ceAccuInt.DataBindings.Clear();
    ceIntAfadj.DataBindings.Clear();
    ceAccuTax.DataBindings.Clear();
    ceTaxAfadj.DataBindings.Clear();
    gvMain.Columns.Clear();

    //設定DataSet
    if (!ds.Tables.Contains(tablename)) ds.Tables.Add(AppFuncs.GetTable(tablename));
    this.bsMain.DataSource = ds;    //runtime error
    this.bsMain.DataMember = tablename;

    //增加setCals欄位
    setCalsField();

    if (CommonFuncs.ConvertEditValue<int>(leStp) == 1)  //活存
    {
        //設定畫面欄位binding
        //ceAccuInt.DataBindings.Add("EditValue", bsMain, "accu_int", true);
        ceIntAfadj.DataBindings.Add("EditValue", bsMain, "accu_int", true);


        //設定MainBinding
        SetMainTable(tablename, bsMain);
        grdData.DataSource = bsMain;

        //設定setCals欄位
        SetCalc("@contractno", "pno", PfoInfo.GetLookup(false));
        SetCalc("@cbrief", "pno", PfoInfo.GetLookup(true));
        SetCalc("@bankbrief", "bank", CommObjects.GetLookupFunction("bank", AppFuncs.GetTable("cmn_broker"), "broker", "cbrief"));

        //欄位排序
        sortField("@contractno", "@cbrief", "bank", "@bankbrief", "bankacc", "ccy", "amt", "accu_int", "int");

        //設定欄位名稱
        setFiledName("客戶代碼", "客戶名稱", "銀行代碼", "銀行簡稱", "銀行帳號", "幣別", "餘額", "累計利息", "前期利息");
    }
    else if (CommonFuncs.ConvertEditValue<int>(leStp) == 2) //定存
    {
        //設定畫面欄位binding
        //ceAccuInt.DataBindings.Add("EditValue", bsMain, "accu_int", true);
        ceIntAfadj.DataBindings.Add("EditValue", bsMain, "accu_int", true);
        //ceAccuTax.DataBindings.Add("EditValue", bsMain, "accu_tax", true);
        ceTaxAfadj.DataBindings.Add("EditValue", bsMain, "accu_tax", true);

        //設定MainBinding
        SetMainTable(tablename, bsMain);
        grdData.DataSource = bsMain;

        //設定setCals欄位
        SetCalc("@contractno", "pno", PfoInfo.GetLookup(false));
        SetCalc("@cbrief", "pno", PfoInfo.GetLookup(true));
        SetCalc("@brokerbrief", "broker", CommObjects.Broker.GetLookupFunction("broker", "cbrief"));

        //欄位排序
        sortField("@contractno", "@cbrief", "tradeno_buy", "broker", "@brokerbrief", "start_date", "due_date", "ccy", "int_rate", "amt", "accu_int", "int", "accu_tax", "tax");

        //設定欄位名稱
        setFiledName("客戶代碼", "客戶名稱", "買進序號", "往來對象代碼", "往來對象簡稱", "交易日", "到期日", "幣別", "利率", "金額", "累計利息", "前期利息", "累計稅款", "前期稅款");
    }
    else if (CommonFuncs.ConvertEditValue<int>(leStp) == 3) //短票RP
    {
        //設定畫面欄位binding
        //ceAccuInt.DataBindings.Add("EditValue", bsMain, "accu_int", true);
        ceIntAfadj.DataBindings.Add("EditValue", bsMain, "accu_int", true);
        //ceAccuTax.DataBindings.Add("EditValue", bsMain, "accu_tax", true);
        ceTaxAfadj.DataBindings.Add("EditValue", bsMain, "accu_tax", true);

        //設定MainBinding
        SetMainTable(tablename, bsMain);
        grdData.DataSource = bsMain;

        //設定setCals欄位
        SetCalc("@contractno", "pno", PfoInfo.GetLookup(false));
        SetCalc("@cbrief", "pno", PfoInfo.GetLookup(true));
        SetCalc("@vouno", "code", CommObjects.GetLookupFunction("code", AppFuncs.GetTable("cmn_stb"), "code", "vouno"));
        SetCalc("@vounobrief", "code", CommObjects.GetLookupFunction("code", AppFuncs.GetTable("cmn_stb"), "code", "cbrief"));

        //欄位排序
        sortField("@contractno", "@cbrief", "tradeno_buy", "@vouno", "@vounobrief", "due_date", "ccy", "int_rate", "amt", "accu_int", "int", "accu_tax", "tax");

        //設定欄位名稱
        setFiledName("客戶代碼", "客戶名稱", "買進序號", "票券代號", "票券簡稱", "到期日", "幣別", "利率", "金額", "累計利息", "前期利息", "累計稅款", "前期稅款");
    }
    //OpenTable();
    //OpenTableBy(string.Format("SELECT * FROM {0} WHERE pno={1} AND date='{2}'",
    //    tablename, CommonFuncs.ConvertEditValue<int>(lePno), CommonFuncs.ConvertEditValue<DateTime>(deDate).ToShortDateString()));

    OpenTableBy(string.Format("SELECT * FROM {0} WHERE pno={1} ",
      tablename, CommonFuncs.ConvertEditValue<int>(lePno)));

    MainGridView.BestFitColumns();
    deDate.Enabled = false;
}