Reset() public method

public Reset ( ) : void
return void
        protected void btnLoad_Click(object sender, EventArgs e)
        {
            GridWeb1.WorkSheets.Clear();

            // Connect database
            System.Data.OleDb.OleDbConnection oleDbConnection1 = new OleDbConnection();
            System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1 = new OleDbDataAdapter();
            System.Data.OleDb.OleDbCommand oleDbSelectCommand1 = new OleDbCommand();
            string path = (this.Master as Site).GetDataDir();
            oleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + "\\Worksheets\\Database\\Northwind.mdb";
            oleDbSelectCommand1.Connection = oleDbConnection1;
            oleDbDataAdapter1.SelectCommand = oleDbSelectCommand1;

            DataTable dataTable1 = new DataTable();
            dataTable1.Reset();

            try
            {
                oleDbSelectCommand1.CommandText = "SELECT * FROM Products";
                oleDbDataAdapter1.Fill(dataTable1);
                
                // Import data from database to grid web
                GridWeb1.WorkSheets.ImportDataView(dataTable1.DefaultView, null, null);
            }
            finally
            {
                // Close connection
                oleDbConnection1.Close();
            }
        }
 public void GetOrderUserWise()
 {
     try
     {
         if (Session["userID"] != null)
         {
             System.Data.DataTable ds = new System.Data.DataTable();
             ds.Reset();
             string customerID = Session["userID"].ToString();
             ds = config.GetAllOrderUserWise(customerID);
             if (ds.Rows.Count > 0)
             {
                 grdOrderlist.DataSource = ds;
                 grdOrderlist.DataBind();
             }
             else
             {
                 grdOrderlist.DataSource = null;
                 grdOrderlist.DataBind();
             }
         }
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }
Beispiel #3
0
        protected void btnBuscar_Click(object sender, EventArgs e)
        {
            try
            {
                gv.DataSource = null;
                gv.DataBind();

                gv.Visible = true;

                if (Tabla != null)
                {
                    Tabla.Reset();
                }

                ArmarCabecera(ucEstado.Text);

                //Tabla.Rows.Add(Tabla.NewRow());
                //AgregarTotales();

                //Tabla.Rows.Add(Tabla.NewRow());
                //AgregarPorcentajes();
            }
            catch (Exception ex)
            {
                FormsHelper.MsgError(lblMsg, ex);
            }
        }
Beispiel #4
0
 private void getproduct()
 {
     try
     {
         Config config            = new Config();
         System.Data.DataTable ds = new System.Data.DataTable();
         ds.Reset();
         ds = config.get_Product_Category(Request.QueryString["id"].ToString());
         if (ds.Rows.Count > 0)
         {
             if (ds.Rows.Count > 0)
             {
                 PagedDataSource pageds = new PagedDataSource();
                 DataView        dv     = new DataView(ds);
                 pageds.DataSource  = dv;
                 pageds.AllowPaging = true;
                 pageds.PageSize    = 9;
                 if (ViewState["PageNumber"] != null)
                 {
                     pageds.CurrentPageIndex = Convert.ToInt32(ViewState["PageNumber"]);
                 }
                 else
                 {
                     pageds.CurrentPageIndex = 0;
                 }
                 if (pageds.PageCount > 1)
                 {
                     rptPaging.Visible = true;
                     ArrayList pages = new ArrayList();
                     for (int i = 0; i < pageds.PageCount; i++)
                     {
                         pages.Add((i + 1).ToString());
                     }
                     rptPaging.DataSource = pages;
                     rptPaging.DataBind();
                 }
                 else
                 {
                     rptPaging.Visible = false;
                 }
                 //rptUserData.DataSource = pageds;
                 //rptUserData.DataBind();
                 rpt_Product.DataSource = pageds;
                 rpt_Product.DataBind();
             }
         }
         else
         {
             rpt_Product.DataSource = ds;
             rpt_Product.DataBind();
         }
     }
     catch (Exception ex)
     {
         throw ex;
     }
     //
 }
Beispiel #5
0
        static void CrearTablaDatos()
        {
            tablaDatos.Reset();

            tablaDatos.Columns.Add("Pago", typeof(int));
            tablaDatos.Columns.Add("Fecha", typeof(string));
            tablaDatos.Columns.Add("Cuota", typeof(double));
            tablaDatos.Columns.Add("Capital", typeof(double));
            tablaDatos.Columns.Add("Interes", typeof(double));
            tablaDatos.Columns.Add("Balance", typeof(double));
        }
Beispiel #6
0
        private void btnImport_Click(object sender, EventArgs e)
        {
            System.Data.DataTable dt = new System.Data.DataTable("datable");
            DataSet dsSource         = new DataSet("dataSet");

            dt.Reset();

            Microsoft.Office.Interop.Excel.Workbook    ExWorkbook;
            Microsoft.Office.Interop.Excel.Worksheet   ExWorksheet;
            Microsoft.Office.Interop.Excel.Range       ExRange;
            Microsoft.Office.Interop.Excel.Application Exobj = new Microsoft.Office.Interop.Excel.Application();

            openFileDialog1.Filter = "Excel Files |*.xls;*.xlsx;*.xlsm";
            DialogResult result = openFileDialog1.ShowDialog();

            if (result == DialogResult.OK)//test result
            {
                ExWorkbook  = Exobj.Workbooks.Open(openFileDialog1.FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                ExWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)ExWorkbook.Sheets.get_Item(1);
                ExRange     = ExWorksheet.UsedRange;

                for (int Cnum = 1; Cnum <= ExRange.Columns.Count; Cnum++)
                {
                    dt.Columns.Add(new DataColumn((ExRange.Cells[1, Cnum] as Microsoft.Office.Interop.Excel.Range).Value2.ToString()));
                }
                dt.AcceptChanges();

                string[] columnNames = new string[dt.Columns.Count];
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    columnNames[0] = dt.Columns[i].ColumnName;
                }
                for (int Rnum = 2; Rnum <= ExRange.Rows.Count; Rnum++)
                {
                    DataRow dr = dt.NewRow();
                    for (int Cnum = 1; Cnum <= ExRange.Columns.Count; Cnum++)
                    {
                        if ((ExRange.Cells[Rnum, Cnum] as Microsoft.Office.Interop.Excel.Range).Value2 != null)
                        {
                            dr[Cnum - 1] = (ExRange.Cells[Rnum, Cnum] as Microsoft.Office.Interop.Excel.Range).Value2.ToString();
                        }
                        dt.Rows.Add(dr);
                        dt.AcceptChanges();
                    }
                }

                ExWorkbook.Close(true, Missing.Value, Missing.Value);
                Exobj.Quit();
                dataGridView1.DataSource = dt;
            }
        }
Beispiel #7
0
 private void load()
 {
     try
     {
         System.Data.DataTable table = tblTipoNumeracao.Get((HzConexao)HttpContext.Current.Session["connection"], new ListCampos());
         HzlibWEB.Objetos.LoadCombo(ddllista, table, "cmpDcTipoNumeracao", "cmpCoTipoNumeracao", "cmpDcTipoNumeracao", "--- Selecione o tipo ---", true);
         table.Reset();
         table = tblFuncionario.Get((HzConexao)HttpContext.Current.Session["connection"], new ListCampos());
         HzlibWEB.Objetos.LoadCombo(ddlautor, table, "cmpNoFuncionario", "cmpCoFuncionario", "cmpNoFuncionario", "--- Selecione o autor ---", true);
     }
     catch
     {
     }
 }
 /// <summary>
 /// 记录重复对象并将坐标保存到主视图DataGrid中
 /// </summary>
 /// <param name="Name">元素名称</param>
 /// <param name="PointData">点坐标</param>
 /// <param name="RefObj">操作对象</param>
 /// <param name="IgRepeat">是否过滤重复数据</param>
 /// <returns></returns>
 private bool WriteObjectToDataGrid(string Name, object[] PointData, Reference RefObj, bool IgRepeat)
 {
     try
     {
         double[] xyz            = new double[3];
         int      keepValuePoint = Convert.ToInt16(2);
         if (!datatable.IsInitialized)
         {
             datatable.Reset();
         }
         DataRow = datatable.NewRow();
         datatable.Rows.Add(DataRow);
         xyz[0] = Math.Round(Convert.ToDouble(PointData[0]), keepValuePoint);
         xyz[1] = Math.Round(Convert.ToDouble(PointData[1]), keepValuePoint);
         xyz[2] = Math.Round(Convert.ToDouble(PointData[2]), keepValuePoint);
         if (RxDataOprator.DoRepeatCheck(xyz, datatable, Convert.ToInt16(this.MinDistance.Text))) //True 为重复值
         {
             GetRepeatRef.SetValue(RefObj, RepeatNum);                                            //记录重复对象
             RepeatNum += 1;
             if (IgRepeat)
             {
                 datatable.Rows[datatable.Rows.Count - 1].Delete();
                 return(true);
             }
         }
         DataRow["序号"]  = datatable.Rows.Count;
         DataRow["名称"]  = Name;//Convert.ToDouble(PointCoord[0]), Convert.ToDouble(PointData[1]), Convert.ToDouble(PointData[2])
         DataRow["X坐标"] = xyz[0];
         DataRow["Y坐标"] = xyz[1];
         DataRow["Z坐标"] = xyz[2];
         DataRow["RX"]  = Math.Round(Convert.ToDouble(PointData[3]), keepValuePoint);
         DataRow["RY"]  = Math.Round(Convert.ToDouble(PointData[4]), keepValuePoint);
         DataRow["RZ"]  = Math.Round(Convert.ToDouble(PointData[5]), keepValuePoint);
         datatable.Rows.Add(DataRow);
         //dataview = new DataView(datatable);
         return(true);
     }
     catch (System.Exception)
     {
         //throw;
         return(false);
     }
 }
Beispiel #9
0
        //Cette fonction créée le tableau de loi normal
        //à partir du tableau donnée par le prof
        //

        public void ImportTable()
        {
            System.Data.DataTable dt = new System.Data.DataTable("dataTable");
            DataSet dsSource         = new DataSet("dataSet");

            dt.Reset();

            Excel.Workbook    ExWorkbook;
            Excel.Worksheet   ExWorksheet;
            Excel.Range       ExRange;
            Excel.Application ExObj = new Excel.Application();


            ExWorkbook  = ExObj.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + "\\table_normale.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            ExWorksheet = (Excel.Worksheet)ExWorkbook.Sheets.get_Item(1);
            ExRange     = ExWorksheet.UsedRange;

            for (int Cnum = 1; Cnum <= ExRange.Columns.Count; Cnum++)
            {
                dt.Columns.Add(new DataColumn((ExRange.Cells[1, Cnum] as Excel.Range).Value2.ToString()));
            }
            dt.AcceptChanges();

            for (int Rnum = 1; Rnum <= ExRange.Rows.Count; Rnum++)
            {
                DataRow dr = dt.NewRow();
                for (int Cnum = 1; Cnum <= ExRange.Columns.Count; Cnum++)
                {
                    if ((ExRange.Cells[Rnum, Cnum] as Excel.Range).Value2 != null)
                    {
                        dr[Cnum - 1] = (ExRange.Cells[Rnum, Cnum] as Excel.Range).Value2.ToString();
                    }
                }
                dt.Rows.Add(dr);
                dt.AcceptChanges();
            }
            ExWorkbook.Close(true, Missing.Value, Missing.Value);
            ExObj.Quit();

            DGV_Table.DataSource = dt;
        }
Beispiel #10
0
        /// <summary>
        /// 初始化列,
        /// </summary>
        public void InitColumns()
        {
            dt.Clear();
            dt = new DataTable();
           // dt.Columns.Add();
            //需要隐藏列时在这写代码
            int selectType = 0; 
            string orgCode = null;
            string lineCode = null;
            if (btSelectList.EditValue != null)
            {
                selectType = Convert.ToInt32(btSelectList.EditValue);
            }
            if (btGdsList.EditValue != null)
            {
                orgCode = btGdsList.EditValue.ToString();
                if (orgCode=="0"&&selectType == 1)
                {
                    selectType = 0;
                }
            }
            else
            {
                selectType = 0;
            }
            if (btXlList.EditValue!=null)
            {
                lineCode = btXlList.EditValue.ToString();
                if (lineCode=="0"&& selectType == 2)
                {
                    selectType = 1;
                }
            }
            else if (btGdsList.EditValue!=null)
            {
                selectType = 1;
            }
            Hashtable ht =new Hashtable();
            ht.Add("selectType", selectType);
            ht.Add("lineCode", lineCode);
            ht.Add("orgCode", orgCode);
            gtsbList = Client.ClientHelper.PlatformSqlMap.GetList("GetPS_gtsbName", ht);
            foreach (string str in gtsbListAll)
            {
                if (string.IsNullOrEmpty(str))
                {
                    continue;
                }
                if (!string.IsNullOrEmpty(str)&&gtsbList.Contains(str)&& (gridView1.Columns.ColumnByName(str)==null))
                {
                    GridColumn gc = new GridColumn();
                    gc.Caption = str;
                    gc.Name = str;
                    gc.FieldName = str;
                    //gc.Visible = true;
                    gridView1.Columns.Add(gc);
                    gridView1.Columns[str].Visible = true;
                }
                else if (!string.IsNullOrEmpty(str) && !gtsbList.Contains(str))
                {
                    if (gridView1.Columns.ColumnByName(str)!=null)
                    {
                        gridView1.Columns.Remove(gridView1.Columns.ColumnByName(str));
                    }
                }

            }
            dt.Reset();
            foreach (GridColumn col in gridView1.Columns)
            {
                DataColumn dc = new DataColumn();
                dc.Caption = col.GetCaption();
                dc.ColumnName = col.FieldName;                
                dt.Columns.Add(dc);
            }

            //hideColumn("ParentID");
            //hideColumn("gzrjID");
        }
Beispiel #11
0
        private void ImportFromExcell()
        {
            Microsoft.Office.Interop.Excel.Range excellRange;
            ExcellSctructure excelStructure = new ExcellSctructure();
            OpenFileDialog   excellImport   = new OpenFileDialog();
            DataTable        dTable         = new DataTable("dataTableExcell");
            DataSet          dsSource       = new DataSet("dataSetExcell");

            dTable.Reset();
            try
            {
                Employees.Clear();
                excelStructure.SetElements();

                excellImport.Title            = "Select file excell";
                excellImport.Filter           = "Excel Sheet(*.xlsx)|*.xlsx|All Files(*.*)|*.*";
                excellImport.FilterIndex      = 1;
                excellImport.RestoreDirectory = true;

                if (excellImport.ShowDialog() == DialogResult.OK)
                {
                    excelStructure.XlWorkBook = excelStructure.XlExcel.Workbooks.Open(excellImport.FileName, excelStructure.MisValue, excelStructure.MisValue,
                                                                                      excelStructure.MisValue, excelStructure.MisValue, excelStructure.MisValue, excelStructure.MisValue, excelStructure.MisValue, excelStructure.MisValue,
                                                                                      excelStructure.MisValue, excelStructure.MisValue, excelStructure.MisValue, excelStructure.MisValue, excelStructure.MisValue, excelStructure.MisValue);
                    excelStructure.XlWorkSheet = excelStructure.XlWorkBook.Sheets.get_Item(1);
                    excellRange = excelStructure.XlWorkSheet.UsedRange;
                    var employeeProperties = typeof(Employee).GetProperties();
                    var fiedCountEmployee  = employeeProperties.Length;
                    var columnCountExcell  = excellRange.Columns.Count;

                    List <string> columnList = new List <string>();
                    for (int columnNum = 1; columnNum <= excellRange.Columns.Count; columnNum++)
                    {
                        dTable.Columns.Add(new DataColumn((excellRange.Cells[1, columnNum] as Microsoft.Office.Interop.Excel.Range).Value2.ToString()));
                    }
                    dTable.AcceptChanges();

                    for (int i = 0; i < dTable.Columns.Count; i++)
                    {
                        columnList.Add(dTable.Columns[i].ColumnName);
                    }

                    //Check format
                    try
                    {
                        bool wrongFormat = false;

                        wrongFormat = fiedCountEmployee != columnCountExcell;
                        foreach (var item in columnList)
                        {
                            if (employeeProperties.FirstOrDefault(zx => zx.Name == item) == null)
                            {
                                wrongFormat = false;
                                break;
                            }
                        }

                        if (wrongFormat)
                        {
                            throw new Exception("We have the wrong format of a file");
                        }
                        ;
                    }
                    catch
                    {
                        throw;
                    }

                    for (int rowNumber = 2; rowNumber <= excellRange.Rows.Count; rowNumber++)
                    {
                        DataRow dRow = dTable.NewRow();
                        for (int columnNumber = 1; columnNumber <= excellRange.Columns.Count; columnNumber++)
                        {
                            if ((excellRange.Cells[rowNumber, columnNumber] as Microsoft.Office.Interop.Excel.Range).Value2 != null)
                            {
                                dRow[columnNumber - 1] = (excellRange.Cells[rowNumber, columnNumber] as Microsoft.Office.Interop.Excel.Range).Value2.ToString();
                            }
                        }
                        dTable.Rows.Add(dRow);
                        dTable.AcceptChanges();
                    }
                    excelStructure.XlWorkBook.Close(true, excelStructure.MisValue, excelStructure.MisValue);
                    excelStructure.XlExcel.Quit();
                    for (int i = 0; i < dTable.Rows.Count; i++)
                    {
                        var tableRow           = dTable.Rows[i];
                        FixedTimeEmployee fixT = new FixedTimeEmployee();
                        for (int j = 0; j < tableRow.ItemArray.Length; j++)
                        {
                            var currentValue = tableRow.ItemArray[j];
                            var columnNAme   = columnList[j];

                            if (employeeProperties.FirstOrDefault(zx => zx.Name == columnNAme).GetValue(fixT) is decimal)
                            {
                                employeeProperties.FirstOrDefault(zx => zx.Name == columnNAme).SetValue(fixT, Decimal.Parse(currentValue.ToString()));
                            }
                            else
                            {
                                employeeProperties.FirstOrDefault(zx => zx.Name == columnNAme).SetValue(fixT, currentValue);
                            }
                        }
                        Employees.Add(fixT);
                    }
                }
                else
                {
                    releaseObject(excellImport);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                releaseObject(excelStructure.XlWorkSheet);
                releaseObject(excelStructure.XlWorkBook);
                releaseObject(excelStructure.XlExcel);
                releaseObject(excellImport);
            }
        }
    private static void CreateApplicationUserTable( string strType )
    {
        objTableUserMaint = new DataTable("ApplicationUser");
        objTableUserMaint.Reset();
        objTableUserMaint.Columns.Add("ApplicationUser_ID", typeof(int));
        objTableUserMaint.Columns.Add("UserDomain", typeof(string));
        objTableUserMaint.Columns.Add("Username", typeof(string));
        objTableUserMaint.Columns.Add("FirstName", typeof(string));
        objTableUserMaint.Columns.Add("LastName", typeof(string));
        objTableUserMaint.Columns.Add("Phone", typeof(string));
        objTableUserMaint.Columns.Add("Email", typeof(string));
        objTableUserMaint.Columns.Add("Division_ID", typeof(int));
        objTableUserMaint.Columns.Add("DateCreated", typeof(DateTime));
        objTableUserMaint.Columns.Add("DateUpdated", typeof(DateTime));
        objTableUserMaint.Columns.Add("UserRole_ID", typeof(int));
        objTableUserMaint.Columns.Add("NewUser", typeof(int));
        objTableUserMaint.Columns.Add("ReportDate", typeof(DateTime));
        objTableUserMaint.Columns.Add("TerminatedFlag", typeof(bool));

        if( strType.Equals( "Inactive" ) )
            objTableUserMaint.Columns.Add("InactiveDomain", typeof(string));
        else
            objTableUserMaint.Columns.Add("NewDomain", typeof(string));

        objTableUserMaint.Columns.Add("ExcludeFlag", typeof(bool));
    }
Beispiel #13
0
    public void GetUserOrderDetail()
    {
        try
        {
            if (Request.QueryString["UserID"] != null && Request.QueryString["OrderID"] != null)
            {
                System.Data.DataTable ds = new System.Data.DataTable();
                ds.Reset();
                ds = conf.GetAllOrderUserOrderWise(Request.QueryString["UserID"].ToString(), Request.QueryString["OrderID"].ToString());
                if (ds.Rows.Count > 0)
                {
                    if (ds.Rows.Count > 0)
                    {
                        lblCustomername.Text = ds.Rows[0]["CustomerName"].ToString();
                        lblorderDate.Text    = ds.Rows[0]["OrderDate"].ToString();
                        lblemail.Text        = ds.Rows[0]["EmailAddress"].ToString();
                        lblphone.Text        = ds.Rows[0]["phonenumber"].ToString();
                        lbladdress.Text      = ds.Rows[0]["Address1"].ToString();
                        // lblcountry.Text = ds.Rows[0]["Countryname"].ToString();
                        lblstate.Text = ds.Rows[0]["state"].ToString();
                        DateTime dt = Convert.ToDateTime(ds.Rows[0]["OrderDate"]);
                        lblorderDate.Text = dt.ToString("dd/MM/yyyy");
                        lblorderno.Text   = ds.Rows[0]["OrderNo"].ToString();
                        lblsubtotal.Text  = ds.Rows[0]["OrderTotalAmount"].ToString();
                        lblTotal.Text     = ds.Rows[0]["OrderTotalAmount"].ToString();
                        string ordernote = ds.Rows[0]["note"].ToString();
                        if (ordernote != "")
                        {
                            divnote.Visible = true;
                            string note = ordernote.Replace("br /", Environment.NewLine);
                            txtnote.Text = note;
                        }
                        else
                        {
                            divnote.Visible = false;
                        }

                        System.Data.DataTable ds1 = new System.Data.DataTable();
                        ds1 = conf.GetProductdetailwithorderid(Request.QueryString["OrderID"].ToString());
                        grduserorder.DataSource = ds1;
                        grduserorder.DataBind();
                        for (int i = 0; i < grduserorder.Rows.Count; i++)
                        {
                            Label   lblprice    = (Label)grduserorder.Rows[i].FindControl("lblprice");
                            Label   lblprototal = (Label)grduserorder.Rows[i].FindControl("lbltotal");
                            Label   lblqty      = (Label)grduserorder.Rows[i].FindControl("lblqty");
                            int     qty         = Convert.ToInt32(lblqty.Text);
                            decimal subtotal    = Convert.ToDecimal(lblprice.Text) * Convert.ToDecimal(lblqty.Text);
                            lblqty.Text      = qty.ToString();
                            lblprototal.Text = subtotal.ToString("0.00");
                        }
                    }
                }
                System.Data.DataSet userdt = new System.Data.DataSet();
                userdt.Reset();
                userdt = conf.GetUserDetailByID(Request.QueryString["UserID"].ToString());
                if (userdt.Tables[0].Rows.Count > 0)
                {
                    lblvacode.Text    = userdt.Tables[0].Rows[0]["VACode"].ToString();
                    lblvaname.Text    = userdt.Tables[0].Rows[0]["Name"].ToString();
                    lblvaemail.Text   = userdt.Tables[0].Rows[0]["EmailAddress"].ToString();
                    lblvaphone.Text   = userdt.Tables[0].Rows[0]["MobileNo"].ToString();
                    lblvaaddress.Text = userdt.Tables[0].Rows[0]["Address1"].ToString();
                }
            }
            else
            {
                Response.Redirect("User_Login.aspx");
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
Beispiel #14
0
        //same as above, but selects an entry after it's done (used for add to list, and update on list buttons)
        private void FilterGirls(DataTable dt, ref DataTable tempDT, ref ListBox lb, string sFilter, string Name)
        {
            tempDT.Reset();
            tempDT = dt.Clone();

            for (int x = 0; x < dt.Rows.Count; x++) tempDT.ImportRow(dt.Rows[x]);

            tempDT.Columns.Add("ID", typeof(int));

            for (int x = 0; x < tempDT.Rows.Count; x++)
            {
                tempDT.Rows[x][3] = x;
            }

            DataView v = tempDT.DefaultView;
            v.RowFilter = "TypeFilter = '" + sFilter + "'";
            v.Sort = "Name ASC";
            tempDT = v.ToTable();

            lb.Items.Clear();

            for (int x = 0; x < tempDT.Rows.Count; x++)
            {
                lb.Items.Add(tempDT.Rows[x][0].ToString());
            }

            lb.SelectedItem = Name;
        }
        public void addNewOrderHistData(SqlLib lib, IDb db, object obj, ref DataTable outDt)
        {
            string sql = "";
            string localID = "";

            bool ret = false;

            try
            {
                OrderInfo li = new OrderInfo();

                ret = DataHandle.CompareObjectType(obj, li);

                if (ret)
                {
                    //get the correct list.
                    li = (OrderInfo)obj;
                }
                else
                {
                    return;
                }

                if (outDt == null)
                {
                    throw new NullReferenceException("outDt is null");
                }


                DataTable localDt = new DataTable();

                //只取一条记录,以便得到表结构,如果一条都没有,那么会有什么结果?能够取出表结构。
                sql = lib.GetSql("SqlOrderInfo/OrderInfo", "GetOrderHistStructInfo");
                localDt = db.GetTable(sql);

                //新增加的记录,在oracle中需要取到sequence编号
                sql = lib.GetSql("SqlOrderInfo/OrderInfo", "GetNewOrderHistId");
                DataTable dt = new DataTable();
                dt = db.GetTable(sql);
                if (dt.Rows.Count > 0)
                {
                    localID = dt.Rows[0][0].ToString();
                    dt.Reset();
                }

                if (string.IsNullOrEmpty(localID))
                {
                    return;
                }

                DataRow dr = localDt.NewRow();

                dr["orderid"] = DataHandle.EmptyString2DBNull(localID);
                dr["contactid"] = DataHandle.EmptyString2DBNull(li.CONTACTID);
                dr["addressid"] = DataHandle.EmptyString2DBNull(li.ADDRESSID);

                dr["ACCDT"] = DataHandle.EmptyString2DBNull(li.ACCDT);
                dr["AMORTISATION"] = DataHandle.EmptyString2DBNull(li.AMORTISATION);
                dr["BENEFICIARIES"] = DataHandle.EmptyString2DBNull(li.BENEFICIARIES);
                dr["BILL"] = DataHandle.EmptyString2DBNull(li.BILL);
                dr["BILLDEMONDDSC"] = DataHandle.EmptyString2DBNull(li.BILLDEMONDDSC);
                dr["BILLDEMONDED"] = DataHandle.EmptyString2DBNull(li.BILLDEMONDED);
                dr["BILLTITLE"] = DataHandle.EmptyString2DBNull(li.BILLTITLE);
                dr["CARDID"] = DataHandle.EmptyString2DBNull(li.CARDID);
                dr["CARDRIGHTNUM"] = DataHandle.EmptyString2DBNull(li.CARDRIGHTNUM);
                dr["CLEARFEE"] = DataHandle.EmptyString2DBNull(li.CLEARFEE);

                dr["CONFIRM"] = DataHandle.EmptyString2DBNull(li.CONFIRM);
                dr["CONSIGNEE"] = DataHandle.EmptyString2DBNull(li.CONSIGNEE);
                dr["CONSIGNPHN"] = DataHandle.EmptyString2DBNull(li.CONSIGNPHN);
                dr["CRUSR"] = DataHandle.EmptyString2DBNull(li.CRUSR);
                dr["DEMONDDT"] = DataHandle.EmptyString2DBNull(li.DEMONDDT);
                dr["DISCOUNT"] = DataHandle.EmptyString2DBNull(li.DISCOUNT);
                dr["FBDT"] = DataHandle.EmptyString2DBNull(li.FBDT);
                dr["GRPID"] = DataHandle.EmptyString2DBNull(li.GRPID);
                dr["HEALTHINTRO"] = DataHandle.EmptyString2DBNull(li.HEALTHINTRO);
                dr["INSURANCEID"] = DataHandle.EmptyString2DBNull(li.INSURANCEID);

                dr["INSURANT"] = DataHandle.EmptyString2DBNull(li.INSURANT);
                dr["MAILPRICE"] = DataHandle.EmptyString2DBNull(li.MAILPRICE);
                dr["MDDT"] = DataHandle.EmptyString2DBNull(li.MDDT);
                dr["MDUSR"] = DataHandle.EmptyString2DBNull(li.MDUSR);
                dr["MONITORRECORDER"] = DataHandle.EmptyString2DBNull(li.MONITORRECORDER);
                dr["NOTE"] = DataHandle.EmptyString2DBNull(li.NOTE);
                dr["NOWMONEY"] = DataHandle.EmptyString2DBNull(li.NOWMONEY);
                dr["POLICYHOLDER"] = DataHandle.EmptyString2DBNull(li.POLICYHOLDER);
                dr["POSTFEE"] = DataHandle.EmptyString2DBNull(li.POSTFEE);
                dr["PRODPRICE"] = DataHandle.EmptyString2DBNull(li.PRODPRICE);

                dr["PRODUCTINTRO"] = DataHandle.EmptyString2DBNull(li.PRODUCTINTRO);
                dr["RESULT"] = DataHandle.EmptyString2DBNull(li.RESULT);
                dr["SENDDT"] = DataHandle.EmptyString2DBNull(li.SENDDT);
                dr["SPECIALDSC"] = DataHandle.EmptyString2DBNull(li.SPECIALDSC);
                dr["STATUS"] = DataHandle.EmptyString2DBNull(li.STATUS);
                dr["TOTALPRICE"] = DataHandle.EmptyString2DBNull(li.TOTALPRICE);
                dr["URGENT"] = DataHandle.EmptyString2DBNull(li.URGENT);
                dr["ordertype"] = DataHandle.EmptyString2DBNull(li.ordertype);
                dr["paytype"] = DataHandle.EmptyString2DBNull(li.paytype);
                dr["mailtype"] = DataHandle.EmptyString2DBNull(li.mailtype);
                dr["CRDT"] = DataHandle.EmptyString2DBNull(li.crdt);

                localDt.Rows.Add(dr);

                outDt.Merge(localDt, true);
            }
            catch (NullReferenceException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in addNewOrderHistData" + ex.Message);
            }
            catch (ArrayTypeMismatchException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in addNewOrderHistData" + ex.Message);
            }
            catch (Exception ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in addNewOrderHistData" + ex.Message);
                throw;
            }
        }
        // Handles the "import" button click event and load data from a dataview object.
        protected void Button1_Click(object sender, EventArgs e)
        {
            // ExStart:ImportDataView
            // Connect database
            System.Data.OleDb.OleDbConnection oleDbConnection1 = new OleDbConnection();
            System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1 = new OleDbDataAdapter();
            System.Data.OleDb.OleDbCommand oleDbSelectCommand1 = new OleDbCommand();
            string path = (this.Master as Site).GetDataDir();
            oleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + "\\Worksheets\\Database\\Northwind.mdb";
            oleDbSelectCommand1.Connection = oleDbConnection1;
            oleDbDataAdapter1.SelectCommand = oleDbSelectCommand1;

            DataTable dataTable1 = new DataTable();
            dataTable1.Reset();

            // Queries database.
            try
            {
                oleDbSelectCommand1.CommandText = "SELECT CategoryID, CategoryName, Description FROM Categories";
                oleDbDataAdapter1.Fill(dataTable1);
            }
            catch
            {           
            }
            finally
            {
                oleDbConnection1.Close();
            }
                        
            // Imports data from dataview object.
            dataTable1.TableName = "Categories";
            GridWeb1.WorkSheets.Clear();
            GridWeb1.WorkSheets.ImportDataView(dataTable1.DefaultView, null, null);

            // Imports data from dataview object with sheet name and position specified.
            GridWeb1.WorkSheets.ImportDataView(dataTable1.DefaultView, null, null, "SpecifiedName&Position", 2, 1);
            // ExEnd:ImportDataView

            // Resize cells
            GridCells cells = GridWeb1.WorkSheets[0].Cells;
            // Sets column width.
            cells.SetColumnWidth(0, 10);
            cells.SetColumnWidth(1, 10);
            cells.SetColumnWidth(2, 30);
            cells.SetRowHeight(2, 30);
            GridCells cellsb = GridWeb1.WorkSheets[1].Cells;
            cellsb.SetColumnWidth(1, 10);
            cellsb.SetColumnWidth(2, 10);
            cellsb.SetColumnWidth(3, 30);
            cellsb.SetRowHeight(4, 30);

            // Add style to cells
            GridTableItemStyle style = new GridTableItemStyle();
            style.HorizontalAlign = HorizontalAlign.Center;
            style.BorderStyle = BorderStyle.Solid;
            style.BorderColor = Color.Black;
            style.BorderWidth = 1;

            for (int i = 1; i <= cells.MaxRow; i++)
            {
                for (int j = 0; j <= cells.MaxColumn; j++)
                {
                    GridCell cell = cells[i, j];
                    cell.CopyStyle(style);

                }
            }
            for (int i = 3; i <= cellsb.MaxRow; i++)
            {
                for (int j = 1; j <= cellsb.MaxColumn; j++)
                {
                    GridCell cell = cellsb[i, j];
                    cell.CopyStyle(style);

                }
            }
        }
Beispiel #17
0
        //Click event for Add Databases button
        //Opens an OpenFileDialog for user to browse for merge-able database files,
        //verifies them, and adds them to the list
        private void btn_AddDatabases_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.InitialDirectory = mvars.defaultDBPath;
            ofd.Title = "Select Databases to Merge";
            ofd.Filter = "SecondSight Partial Databases (*.ssp)|*.ssp|All Files (*.*)|*.*";
            ofd.CheckFileExists = true;
            ofd.Multiselect = true;
            ofd.DefaultExt = ".ssp";

            //Process selected files if user didn't cancel
            if( ofd.ShowDialog() == DialogResult.OK ) {
                string errmsg = ""; //Holds composite error message
                Cursor.Current = Cursors.WaitCursor;

                //For each file selected, open the database, grab the DB info, check DB info against
                //master DB info and exclude if they don't match (display error message after batch processing)
                //check for mergeDB-specific info (the label and the assigned min/max SKUs) and include it in the
                //list box entry if available
                foreach (string fname in ofd.FileNames) {
                    SSDataBase ssdb = new SSDataBase();
                    DataTable dt = new DataTable();
                    string[] info = new string[3];

                    if (fname != mvars.Masterdb.MyPath) {
                        try {
                            ssdb.OpenDB(fname);
                            ssdb.GetTable(dt, SSTable.DBInfo);
                            info[0] = dt.Rows[0][0].ToString();
                            info[1] = dt.Rows[0][1].ToString();
                            info[2] = String.Format("{0:MM/dd/yyyy}", dt.Rows[0][2]);
                        } catch { //An exception will be thrown if something
                            info[0] = info[1] = info[2] = "Unknown";
                        }

                        //If the database info doesn't match, compile an error message,
                        //otherwise include the info in the listbox and mvars
                        if( (info[0] != mvars.Masterdbname) ||
                            (info[1] != mvars.Masterdblocation) ||
                            (info[2] != mvars.Masterdbdate)) {
                            errmsg += System.IO.Path.GetFileName(fname) + ": " + info[0] + " (" + info[1] + "), Created on " + info[2] + "\n";
                        } else {
                            mvars.mergeDBs.Add(ssdb);
                            string ts = info[0] + " (" + info[1] + ")"; //Compile the listbox item string
                            try {
                                dt.Reset();
                                ssdb.GetTable(dt, SSTable.MergeInfo);
                                ts += " - For " + dt.Rows[0][0].ToString() + " - SKU Assignment: " + Convert.ToInt16(dt.Rows[0][1]) +
                                    " to " + Convert.ToInt16(dt.Rows[0][2]);
                            }
                            catch {
                                ts += " - No additional information";
                            }

                            lbox_DatabasesToMerge.Items.Add(ts);
                        }
                        try {ssdb.CloseDB();} catch{} //Attempt to close the current merge database
                    }
                }

                Cursor.Current = Cursors.Default;

                //Display the error message if any databases were excluded
                if(errmsg.Length > 0) {
                    errmsg = "The following files you selected are either not valid SecondSight database files or are " +
                        "SecondSight databases that do not match the master database.\n\n" + errmsg + "\n\nThese files " +
                        "will not be included in the merge.";
                    MessageBox.Show(errmsg, "Some Files Invalid", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                //else {
                //    MessageBox.Show("The selected databases were successfully merged into the selected master.", "Merge Successful",
                //        MessageBoxButtons.OK, MessageBoxIcon.Information);
                //}
            }
        }
Beispiel #18
0
        private void buildDataTable(List<List<String>> polarData, int rows, int cols)
        {
            //
            // Copies the data in the array polarData into a data table and assigns
            // it to a DataGridView
            // rows and cols are the size of the data
            //
            int rowStart;               // starting row in polarData
            int rowCount, colCount;     // row and column count variables
            int thisRow, thisCol;       // to save some repeated computations
            double dnumber;             // used for converting speed to double with 10ths only
            int inumber;                // used for converting speed to double

            try
            {
                dt.Dispose();
            }
            catch (Exception)
            {
               // dt did not exist, do nothing
            }

            // create a new instance of the table
            dt = new DataTable("polarDataTable");

            // reset and clear the datatable
            dt.Reset();

            // get the first valid row of the polarData
            rowStart = firstValidRow(polarData);

            // This just adds the columns to the table.  The data is added below.
            for (colCount = 1; colCount < cols; colCount++)
            {
                thisCol = colCount - 1;

                // add the col and set the ordinal
                dt.Columns.Add(polarData[rowStart][colCount]);
                dt.Columns[thisCol].SetOrdinal(thisCol);
                // column names are the first usable row of polarData
                dt.Columns[thisCol].ColumnName = polarData[rowStart][colCount];
            }

            // Add the rows only.  The data is added below
            for (rowCount = 0; rowCount < rows - rowStart; rowCount++)
            {
                // add the rows using the row index
                dt.Rows.Add(rowCount);
            }

            // Create columns names.  Should be in the first usable row of polarData
            // The first colulmn in polarData isthe angle and used for row headings but are NOT included in the datatable
            for (colCount = 1; colCount < cols; colCount++)
            {
                // save some repeated calculations.  this is the index into the datatable.
                thisCol = colCount - 1;

                // Populate the Row getting the data.  There is no Row name in the DataTable Class
                for (rowCount = rowStart; rowCount < rows; rowCount++)
                {
                    // so we don't have to compute this all the time.  this is the index into the datatable
                    thisRow = rowCount - rowStart;
                    try
                    {
                        // limit to one decimal point...who needs data in hundreths?
                        dnumber = Convert.ToDouble(polarData[rowCount][colCount]);
                        inumber = Convert.ToInt32(dnumber * 10.0);
                        dnumber = inumber / 10.0;
                    }
                    // the data was not convertable to a decimal number, ie it was text
                    catch (FormatException)
                    {
                        MessageBox.Show("Data Row Insertion Error: " + polarData[rowCount][colCount] + ". Data ignored.");
                        dnumber = 0;
                    }

                    // assign the value
                    dt.Rows[thisRow][thisCol] = dnumber;

                }  // rows
            } // columns

            // delete the first row tha the column names came from
            dt.Rows.RemoveAt(0);

            // printTable(dt);

            // display the Grid
            displayGrid(dt, dt.Rows.Count, dt.Columns.Count);
        }
Beispiel #19
0
        /// <summary>
        /// Loads the date format.
        /// </summary>
        /// <param name="dropdownList">The dropdown list.</param>
        /// <param name="listName">Name of the list.</param>
        private void LoadDateFormat(DropDownList dropdownList, string listName)
        {
            DataRow dtRow;

            string strCamlQuery = "<OrderBy><FieldRef Name=\"Title\"/></OrderBy>";
            dtListValues.Reset();
            dtListValues = ((MOSSServiceManager)objMossController).ReadList(strCurrSiteUrl, listName, strCamlQuery);
            dropdownList.Items.Clear();
            if(dtListValues != null)
            {
                for(int intIndex = 0; intIndex < dtListValues.Rows.Count; intIndex++)
                {
                    dtRow = dtListValues.Rows[intIndex];
                    dropdownList.Items.Add(dtRow["Title"].ToString());
                }
            }
            if(string.IsNullOrEmpty(PortalConfiguration.GetInstance().GetKey(listName).ToString()))
            {
                strCamlQuery = "<OrderBy><FieldRef Name=\"Title\"/></OrderBy><Where><Eq>" + "<FieldRef Name=\"Default_x0020_Format\" /><Value Type=\"Choice\">TRUE</Value></Eq></Where>";
                dtListValues.Reset();
                dtListValues = ((MOSSServiceManager)objMossController).ReadList(strCurrSiteUrl, listName, strCamlQuery);
                if(dtListValues != null)
                {
                    for(int intIndex = 0; intIndex < dtListValues.Rows.Count; intIndex++)
                    {
                        dtRow = dtListValues.Rows[intIndex];
                        for(int intListDate = 0; intListDate < dropdownList.Items.Count; intListDate++)
                        {
                            if(string.Equals(dropdownList.Items[intListDate].Text.ToString(), dtRow["Title"].ToString()))
                            {
                                dropdownList.Items[intListDate].Selected = true;
                            }
                        }
                    }
                }
            }
            else
            {
                for(int intListDate = 0; intListDate < dropdownList.Items.Count; intListDate++)
                {
                    if(string.Equals(dropdownList.Items[intListDate].Text.ToString(), PortalConfiguration.GetInstance().GetKey(listName).ToString()))
                    {
                        dropdownList.Items[intListDate].Selected = true;
                    }
                }
            }
        }
        private void btSearch_Click(object sender, EventArgs e)
        {
            switch(id_Search)
            {
                // Những thao tác này không cần nhấn nút tìm vì viết trên mỗi sự kiện của từng coponents
                case 0:
                {
                    // Do use bdSource khi nấn nút search thì datagridview bị thay đổi do dt.reset nên textbox bị xóa trắng.
                    // Gán em txtbox trước khi reset table
                    pDTO.MaPhong = txt_MaPhong.Text;
                    dt.Reset();

                    dt = ConvertPhongDTOtoDataTable(new QLKS_BUS_WebserviceSoapClient().searchPhongById(pDTO.MaPhong));
                    bdSource.DataSource = dt;
                    dataGrid_DMP.DataSource = bdSource;
                    break;
                }
                case 1: break;
                case 2: break;
                case 3:
                {
                    dt.Reset();
                    pDTO.MaPhong = txt_MaPhong.Text;
                    pDTO.MaLoaiPhong = cmb_LoaiPhong.Text;
                    pDTO.TinhTrang = rdb_Trong.Checked == true ? 0 : 1;
                    dt = ConvertPhongDTOtoDataTable(new QLKS_BUS_WebserviceSoapClient().searchPhongByTuaLuaHotDua(pDTO.MaLoaiPhong, pDTO.TinhTrang));
                    bdSource.DataSource = dt;
                    dataGrid_DMP.DataSource = bdSource;
                    break;
                }

            }
        }
Beispiel #21
0
        private void readFile_Click(object sender, EventArgs e)
        {
            dataGridView1.DataSource = null;
            dataGridView1.Rows.Clear();
            dataGridView1.Refresh();
            dataGridView1.Visible = true;
            dtexcel.Reset();
            dtexcel.Clear();
            dtexcel = new System.Data.DataTable();

            List <string> names      = new List <string>();
            List <double> prices     = new List <double>();
            string        outputText = "";
            string        conn       = string.Empty;

            if (Path.GetExtension(this.filePath).CompareTo(".xls") == 0)
            {
                conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.filePath + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';";                 //for below excel 2007
            }
            else
            {
                conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + this.filePath + ";Extended Properties='Excel 12.0;HDR=NO';";                 //for above excel 2007
            }
            using (con = new OleDbConnection(conn))
            {
                try
                {
                    con.Open();
                    System.Data.DataTable dtSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                    string Sheet1 = dtSchema.Rows[0].Field <string>("TABLE_NAME");
                    Console.WriteLine(Sheet1);
                    command = new OleDbCommand(@"SELECT * FROM [" + Sheet1 + "]", con);                     //here we read data from sheet1

                    using (OleDbDataReader dr = command.ExecuteReader())
                    {
                        readConfLists();
                        double        total              = 0;
                        double        oldTotal           = 0;
                        double        newPrice           = 0.0;
                        string        cardHolderName     = "";
                        double        cardHolderTotal    = 0;
                        double        cardHolderNumber   = 0;
                        double        oldCardHolderTotal = 0;
                        double        price              = 0;
                        List <string> unknownStores      = new List <string>();

                        //TODO: add option to display in string or double (totals)
                        dtexcel.Columns.Add("Card Number", typeof(double));
                        dtexcel.Columns.Add("Cardholder", typeof(string));
                        dtexcel.Columns.Add("Quarter", typeof(double));
                        dtexcel.Columns.Add("Old Total", typeof(double));
                        dtexcel.Columns.Add("New Total", typeof(double));
                        dtexcel.Columns.Add("Diff", typeof(double));
                        while (dr.Read())
                        {
                            var name = dr.GetValue(0);
                            if (name != DBNull.Value)
                            {
                                //TODO: Add checking if not in masterDictionary, prompt? open config? show name of ones not found?
                                //TODO: add another masterList for ignored items
                                //TODO: Major bug, need to open and save again the journal details file
                                //TODO: Fix rogue exceptions
                                string nameToCheck  = (name as string).Trim();
                                string priceToCheck = dr[4] as string;
                                names.Add(nameToCheck);
                                if (masterDictionary.ContainsKey(nameToCheck))
                                {
                                    if (!Regex.IsMatch(priceToCheck, @"^[a-zA-Z: _]+$"))
                                    {
                                        price = Math.Round(Convert.ToDouble(Regex.Replace(priceToCheck, "[^0-9.]", "")), 2, MidpointRounding.AwayFromZero);

                                        prices.Add(price);
                                        newPrice            = Math.Round(price + (prices[prices.Count - 1] * masterDictionary[nameToCheck]), 2, MidpointRounding.AwayFromZero);
                                        oldCardHolderTotal += price;
                                        cardHolderTotal    += newPrice;
                                        total    += newPrice;
                                        oldTotal += price;
                                        Console.WriteLine(nameToCheck + ", old:" + price + " new: " + newPrice);
                                    }
                                    else
                                    {
                                        //Console.WriteLine("Not a price.");
                                    }
                                }
                                else if (ignoreList.Contains(nameToCheck) || nameToCheck.Equals(""))                                 // Name is in ignore list
                                {
                                    //ignore
                                }
                                else if (nameToCheck.Equals("Credit Card No.:"))                                 // Name is equal to total (need to total and reset some values), get name of person (save it somewhere)
                                {
                                    cardHolderName = dr[3] as string;
                                    cardHolderName = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(cardHolderName.ToLower());
                                    string[] tempName         = cardHolderName.Split(' ');
                                    string   tempConcatenated = "";
                                    for (int i = 0; i < tempName.Length; ++i)
                                    {
                                        if (tempName[i].Length == 1)
                                        {
                                            tempName[i] += ".";
                                        }
                                        tempConcatenated += tempName[i] + " ";
                                    }
                                    cardHolderName = tempConcatenated.Trim();
                                    Console.WriteLine("Card Holder: " + cardHolderName);
                                    string temp   = dr[1] as string;
                                    string substr = temp.Substring(temp.Length - 6);
                                    cardHolderNumber = Convert.ToDouble(substr);
                                }
                                else if (nameToCheck.Equals("Total:"))
                                {
                                    Console.Write(cardHolderName + ", total: ");
                                    Console.WriteLine(cardHolderTotal);

                                    DataRow dataRow = dtexcel.NewRow();
                                    dataRow["Card Number"] = cardHolderNumber;
                                    dataRow["Cardholder"]  = cardHolderName;
                                    dataRow["Old Total"]   = Math.Round(oldCardHolderTotal, 2, MidpointRounding.AwayFromZero);
                                    dataRow["New Total"]   = Math.Round(cardHolderTotal, 2, MidpointRounding.AwayFromZero);
                                    dataRow["Diff"]        = Math.Round(cardHolderTotal - oldCardHolderTotal, 2, MidpointRounding.AwayFromZero);
                                    dataRow["Quarter"]     = Math.Ceiling(Math.Round(cardHolderTotal / 4, 2, MidpointRounding.AwayFromZero));
                                    dtexcel.Rows.Add(dataRow);

                                    cardHolderTotal    = 0;
                                    oldCardHolderTotal = 0;
                                }
                                else                                 // Name is new and should prompt XXX and must be added to master List
                                {
                                    if (!unknownStores.Contains(nameToCheck))
                                    {
                                        unknownStores.Add(nameToCheck);
                                    }
                                    Console.WriteLine(nameToCheck + " is not in master list.");
                                }
                            }
                            else
                            {
                                //Console.WriteLine("Name is null.");
                            }
                        }

                        if (unknownStores.Count > 0)
                        {
                            string temp = "";
                            System.Data.DataTable missingShopsDt = new System.Data.DataTable();
                            missingShopsDt.Columns.Add("Missing Shops", typeof(string));
                            DataRow missRow = missingShopsDt.NewRow();
                            foreach (string store in unknownStores)
                            {
                                temp += store + "\n";
                                missRow["Missing Shops"] = store;
                                missingShopsDt.Rows.Add(missRow);

                                ConfigSetup configSetup = new ConfigSetup(temp);
                                configSetup.Show();
                            }
                            dataGridView1.DataSource = missingShopsDt;
                        }
                        else
                        {
                            outputText += "Old Total: " + oldTotal.ToString("C2", CultureInfo.CreateSpecificCulture("en-PH")) + "\n";
                            outputText += "Total: " + total.ToString("C2", CultureInfo.CreateSpecificCulture("en-PH")) + "\n";
                            Console.WriteLine("Old Total: " + oldTotal);
                            Console.WriteLine("Total: " + total);
                            output.Text = outputText;
                            DataRow dataRow = dtexcel.NewRow();
                            //dataRow["Cardholder"] = "";
                            dataRow["Old Total"] = Math.Round(oldTotal, 2, MidpointRounding.AwayFromZero);
                            dataRow["New Total"] = Math.Round(total, 2, MidpointRounding.AwayFromZero);
                            dataRow["Diff"]      = Math.Round(total - oldTotal, 2, MidpointRounding.AwayFromZero);
                            //dataRow["Quarter"] = Math.Round(total / 4, 2, MidpointRounding.AwayFromZero);
                            dtexcel.Rows.Add(dataRow);
                            dataGridView1.DataSource = dtexcel;
                            dataGridView1.Columns["Quarter"].DefaultCellStyle.Format           = "C";
                            dataGridView1.Columns["Quarter"].DefaultCellStyle.FormatProvider   = System.Globalization.CultureInfo.GetCultureInfo("en-PH");
                            dataGridView1.Columns["Old Total"].DefaultCellStyle.Format         = "C";
                            dataGridView1.Columns["Old Total"].DefaultCellStyle.FormatProvider = System.Globalization.CultureInfo.GetCultureInfo("en-PH");
                            dataGridView1.Columns["New Total"].DefaultCellStyle.Format         = "C";
                            dataGridView1.Columns["New Total"].DefaultCellStyle.FormatProvider = System.Globalization.CultureInfo.GetCultureInfo("en-PH");
                            dataGridView1.Columns["Diff"].DefaultCellStyle.Format         = "C";
                            dataGridView1.Columns["Diff"].DefaultCellStyle.FormatProvider = System.Globalization.CultureInfo.GetCultureInfo("en-PH");
                        }
                    }
                }
                catch
                {
                    MessageBox.Show("Drag Journal details file to program before clicking read file.\nTry opening and saving the excel file first and try again.");
                    Console.WriteLine("Wala");
                }
            }
        }
        public void addNewOrderDetData(SqlLib lib, IDb db, object obj, ref DataTable outOrderDetDt)
        {
            string sql = "";
            string localID = "";
            bool ret = false;
            try
            {
                OrderDetInfo li = new OrderDetInfo();
                ret = DataHandle.CompareObjectType(obj, li);
                if (ret)
                {
                    li = (OrderDetInfo)obj;
                }
                else
                {
                    return;
                }
                if (outOrderDetDt == null)
                {
                    throw new NullReferenceException("outOrderDetDt is null");
                }
                DataTable localDt = new DataTable();
                //只取一条记录,以便得到表结构,如果一条都没有,那么会有什么结果?能够取出表结构。
                sql = lib.GetSql("SqlOrderDetInfo/OrderDetInfo", "GetOrderDetStructInfo");
                localDt = db.GetTable(sql);

                //新增加的记录,在oracle中需要取到sequence编号
                sql = lib.GetSql("SqlOrderDetInfo/OrderDetInfo", "GetNewOrderDetId");
                DataTable dt = new DataTable();
                dt = db.GetTable(sql);
                if (dt.Rows.Count > 0)
                {
                    localID = dt.Rows[0][0].ToString();
                    dt.Reset();
                }
                if (string.IsNullOrEmpty(localID))
                {
                    return;
                }
                DataRow dr = localDt.NewRow();
                dr["ORDERDETID"] = DataHandle.EmptyString2DBNull(localID);
                dr["ACCOUNTINGCOST"] = DataHandle.EmptyString2DBNull(li.ACCOUNTINGCOST);
                dr["BACKDT"] = DataHandle.EmptyString2DBNull(li.BACKDT);
                dr["BACKMONEY"] = DataHandle.EmptyString2DBNull(li.BACKMONEY);
                dr["BREASON"] = DataHandle.EmptyString2DBNull(li.BREASON);
                dr["CARDRIGHTNUM"] = DataHandle.EmptyString2DBNull(li.CARDRIGHTNUM);
                dr["CLEARFEE"] = DataHandle.EmptyString2DBNull(li.CLEARFEE);
                dr["CONTACTID"] = DataHandle.EmptyString2DBNull(li.CONTACTID);
                dr["CRDT"] = DataHandle.EmptyString2DBNull(li.CRDT);
                dr["FBDT"] = DataHandle.EmptyString2DBNull(li.FBDT);
                dr["FEEDBACK"] = DataHandle.EmptyString2DBNull(li.FEEDBACK);
                dr["FREIGHT"] = DataHandle.EmptyString2DBNull(li.FREIGHT);
                dr["GOODSBACK"] = DataHandle.EmptyString2DBNull(li.GOODSBACK);
                dr["ISREFUND"] = DataHandle.EmptyString2DBNull(li.ISREFUND);
                dr["ORDERID"] = DataHandle.EmptyString2DBNull(li.ORDERID);
                dr["PAYMENT"] = DataHandle.EmptyString2DBNull(li.PAYMENT);
                dr["POSTFEE"] = DataHandle.EmptyString2DBNull(li.POSTFEE);
                dr["PRODBANKID"] = DataHandle.EmptyString2DBNull(li.PRODBANKID);
                dr["PRODDETID"] = DataHandle.EmptyString2DBNull(li.PRODDETID);
                dr["PRODNUM"] = DataHandle.EmptyString2DBNull(li.PRODNUM);
                dr["PRODUCTTYPE1"] = DataHandle.EmptyString2DBNull(li.PRODUCTTYPE1);
                dr["RECKONING"] = DataHandle.EmptyString2DBNull(li.RECKONING);
                dr["RECKONINGDT"] = DataHandle.EmptyString2DBNull(li.RECKONINGDT);
                dr["REFUNDDT"] = DataHandle.EmptyString2DBNull(li.REFUNDDT);
                dr["SOLDWITH"] = DataHandle.EmptyString2DBNull(li.SOLDWITH);
                dr["STATUS"] = DataHandle.EmptyString2DBNull(li.STATUS);
                dr["UPRICE"] = DataHandle.EmptyString2DBNull(li.UPRICE);

                localDt.Rows.Add(dr);
                outOrderDetDt.Merge(localDt, true);
            }
            catch (NullReferenceException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in addNewOrderDetData" + ex.Message);
            }
            catch (ArrayTypeMismatchException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in addNewOrderDetData" + ex.Message);
            }
            catch (Exception ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in addNewOrderDetData" + ex.Message);
                throw;
            }
        }
        public void addNewInsuranceUsrData(SqlLib lib, IDb db, object obj, ref DataTable outInsuranceUsrDt)
        {
            string sql = "";
            string localID = "";
            bool ret = false;
            try
            {
                InsuranceUsrInfo li = new InsuranceUsrInfo();
                ret = DataHandle.CompareObjectType(obj, li);
                if (ret)
                {
                    li = (InsuranceUsrInfo)obj;
                }
                else
                {
                    return;
                }
                if (outInsuranceUsrDt == null)
                {
                    throw new NullReferenceException("outInsuranceUsrDt is null");
                }
                DataTable localdt = new DataTable();
                sql = lib.GetSql("SqlInsuranceUsrInfo/InsuranceUsrInfo", "GetInsuranceUsrStructInfo");
                localdt = db.GetTable(sql);

                sql = lib.GetSql("SqlInsuranceUsrInfo/InsuranceUsrInfo", "GetNewInsuranceUsrId");
                DataTable dt = new DataTable();
                dt = db.GetTable(sql);
                if (dt.Rows.Count > 0)
                {
                    localID = dt.Rows[0][0].ToString();
                    dt.Reset();
                }
                if (string.IsNullOrEmpty(localID))
                {
                    return;
                }
                DataRow dr = localdt.NewRow();
                dr["INSURANCE_USRID"] = DataHandle.EmptyString2DBNull(localID);
                dr["Proportion"] = DataHandle.EmptyString2DBNull(li.Proportion);
                dr["CRDT"] = DataHandle.EmptyString2DBNull(li.CRDT);
                dr["CRUSR"] = DataHandle.EmptyString2DBNull(li.CRUSR);
                dr["DSC"] = DataHandle.EmptyString2DBNull(li.DSC);
                dr["IDCARDNO"] = DataHandle.EmptyString2DBNull(li.IDCARDNO);
                dr["IDCARDTYPE"] = DataHandle.EmptyString2DBNull(li.IDCARDTYPE);
                dr["IDPERIOD"] = DataHandle.EmptyString2DBNull(li.IDPERIOD);
                dr["MDDT"] = DataHandle.EmptyString2DBNull(li.MDDT);
                dr["MDUSR"] = DataHandle.EmptyString2DBNull(li.MDUSR);
                dr["NAME"] = DataHandle.EmptyString2DBNull(li.NAME);
                dr["POST"] = DataHandle.EmptyString2DBNull(li.POST);
                dr["PROFESSION"] = DataHandle.EmptyString2DBNull(li.PROFESSION);
                dr["SEX"] = DataHandle.EmptyString2DBNull(li.SEX);
                localdt.Rows.Add(dr);
                outInsuranceUsrDt.Merge(localdt, true);
            }
            catch (NullReferenceException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in addNewInsuranceUsrData" + ex.Message);
            }
            catch (ArrayTypeMismatchException ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in addNewInsuranceUsrData" + ex.Message);
            }
            catch (Exception ex)
            {
                CTrace.WriteLine(CTrace.TraceLevel.Fail, "in addNewInsuranceUsrData" + ex.Message);
                throw;
            }
        }
        /// <summary>
        /// Reads the DefaultPreferences List to fill values for dropdowns Display, DepthUnits, Recordsperpage.
        /// </summary>
        private void ReadDefaultPrefList()
        {
            string strDisplay = string.Empty;
            string strDepthUnits = string.Empty;
            string strRecordsPerpage = string.Empty;
            string strDisplayDefault = string.Empty;
            string strDepthUnitsDefault = string.Empty;
            string strRecordsPerpageDefault = string.Empty;
            //Dream 3.0 code
            //start
            string strPressureUnits = string.Empty;
            string strTemperatureUnits = string.Empty;
            string strPressureUnitDefault = string.Empty;
            string strTemperatureUnitDefault = string.Empty;
            string[] arrPressureUnits = new string[3];
            string[] arrTemperatureUnits = new string[2];
            //end
            string[] arrDisplayVals = new string[2];
            string[] arrDepthUnitVals = new string[2];
            string[] arrRecordsPerPageVals = new string[2];
            int index = 0;
            dtListValues = new DataTable();

            try
            {
                //Read 'Default Preferences' list and set the field values
                dtListValues.Reset();
                dtListValues = ((MOSSServiceManager)objMossController).ReadList(strCurrSiteUrl, DEFAULTPREFERENCESLIST, string.Empty);
                if(dtListValues != null)
                {
                    foreach(DataRow dtRow in dtListValues.Rows)
                    {
                        if(string.Equals(dtRow["Title"].ToString(), DISPLAY))
                        {
                            strDisplayDefault = dtRow["Default_x0020_Value"].ToString();
                            strDisplay = dtRow["Values"].ToString();
                            arrDisplayVals = strDisplay.Split(',');
                        }
                        else if(string.Equals(dtRow["Title"].ToString(), DEPTHUNIT))
                        {
                            strDepthUnitsDefault = dtRow["Default_x0020_Value"].ToString();
                            strDepthUnits = dtRow["Values"].ToString();
                            arrDepthUnitVals = strDepthUnits.Split(',');
                        }
                        else if(string.Equals(dtRow["Title"].ToString(), RECORDSPERPAGE))
                        {
                            strRecordsPerpageDefault = dtRow["Default_x0020_Value"].ToString();
                            strRecordsPerpage = dtRow["Values"].ToString();
                            arrRecordsPerPageVals = strRecordsPerpage.Split(',');
                        }
                        //Dream 3.0 code
                        //start
                        else if(string.Equals(dtRow["Title"].ToString(), PRESSUREUNIT))
                        {
                            strPressureUnitDefault = dtRow["Default_x0020_Value"].ToString();
                            strPressureUnits = dtRow["Values"].ToString();
                            arrPressureUnits = strPressureUnits.Split(',');
                        }
                        else if(string.Equals(dtRow["Title"].ToString(), TEMPERATUREUNIT))
                        {
                            strTemperatureUnitDefault = dtRow["Default_x0020_Value"].ToString();
                            strTemperatureUnits = dtRow["Values"].ToString();
                            arrTemperatureUnits = strTemperatureUnits.Split(',');
                        }
                        //end
                    }
                    cboDisplay.Items.Clear();
                    for(index = 0; index < arrDisplayVals.Length; index++)
                    {
                        cboDisplay.Items.Add(arrDisplayVals[index]);
                    }
                    cboDisplay.SelectedValue = strDisplayDefault;
                    cboDepthUnits.Items.Clear();
                    for(index = 0; index < arrDepthUnitVals.Length; index++)
                    {
                        cboDepthUnits.Items.Add(arrDepthUnitVals[index]);
                    }
                    cboDepthUnits.SelectedValue = strDepthUnitsDefault;
                    cboRecordsPerPage.Items.Clear();
                    for(index = 0; index < arrRecordsPerPageVals.Length; index++)
                    {
                        cboRecordsPerPage.Items.Add(arrRecordsPerPageVals[index]);
                    }
                    cboRecordsPerPage.SelectedValue = strRecordsPerpageDefault;
                    //Dream 3.0 code
                    //start
                    cboPressureUnits.Items.Clear();
                    for(index = 0; index < arrPressureUnits.Length; index++)
                    {
                        cboPressureUnits.Items.Add(arrPressureUnits[index]);
                    }
                    cboPressureUnits.SelectedValue = strPressureUnitDefault;
                    cboTemperatureUnits.Items.Clear();
                    for(index = 0; index < arrTemperatureUnits.Length; index++)
                    {
                        cboTemperatureUnits.Items.Add(arrTemperatureUnits[index]);
                    }
                    cboTemperatureUnits.SelectedValue = strTemperatureUnitDefault;
                    //end
                }
            }
            finally
            {
                if(dtListValues != null)
                    dtListValues.Dispose();
            }
        }
Beispiel #25
0
        //this is filtering function that previous event uses, I'll probably upgrade it to filter everything needed
        private void FilterGirls(DataTable dt, ref DataTable tempDT, ref ListBox lb, string sFilter)
        {
            tempDT.Reset();											//resets temp DataTable
            tempDT = dt.Clone();										//copies the structure from original DataTable

            for (int x = 0; x < dt.Rows.Count; x++) tempDT.ImportRow(dt.Rows[x]);	//this copies content from original DataTable
            tempDT.Columns.Add("ID", typeof(int));					//adds a column to temp DataTable, this is where link with original DataTable will be stored
            for (int x = 0; x < tempDT.Rows.Count; x++)				//at this moment temp DataTable is still exact copy of original, so we fill new ID column with number that represents current row number
            {														//after we filter it they will remain with current values that will tell us what line in original DataTable needs to be updated
                tempDT.Rows[x][3] = x;
            }
            DataView v = tempDT.DefaultView;							//rest is almost the same as with normal sort, create DataView from our DataTable, this time temp DataTable that will get gutted :P, so our original doesn't loose any data
            v.RowFilter = "TypeFilter = '" + sFilter + "'";			//this is the only real change from normal sort, we simply say by which column it needs to filter and by what value, At this moment I've adapted other functions to put type in TypeFilter, actual value it filters comes from where this function is called
            v.Sort = "Name ASC";										//sort this DataView in ascending order using "Name" column as key
            tempDT = v.ToTable();									//apply this sorted view to our original DataTable

            lb.Items.Clear();										//empty listbBox from entries it has

            for (int x = 0; x < tempDT.Rows.Count; x++)				//go through all records in DataTable and add names to listBox so our index sync works again
            {
                lb.Items.Add(tempDT.Rows[x][0].ToString());
            }
        }
Beispiel #26
0
        protected void gerarXML()
        {
            try
            {
                string nomePasta = DateTime.Today.ToString("dd-MM-yyyy");

                System.IO.Directory.CreateDirectory(nomePasta);

                DataTable tabelas = new DataTable();

                if (this.tipoBanco == "SQL Server")
                {
                    tabelas = this.objCon.getResult(this.objCon.SQLTablesSQLServer);
                }
                else if (this.tipoBanco == "Firebird")
                {
                    tabelas = this.objCon.getResult(this.objCon.SQLTablesFirebird);
                }

                XmlTextWriter xml;

                int qtdTabelas = tabelas.Rows.Count;

                for (int posicaoTabela = 0; posicaoTabela < qtdTabelas; posicaoTabela++)
                {
                    string nomeTabela = tabelas.Rows[posicaoTabela][0].ToString().Trim();

                    if (nomeTabela.IndexOf(" ") == -1)
                    {
                        string SQLRows = "SELECT * FROM " + nomeTabela;

                        DataTable tableQtdRegistros = new DataTable();
                        tableQtdRegistros = this.objCon.getResult(SQLRows);

                        int qtdRegistros = tableQtdRegistros.Rows.Count;

                        xml = new XmlTextWriter(nomePasta + "/" + nomeTabela + ".xml", Encoding.UTF8);
                        xml.WriteStartDocument();
                        xml.Formatting = Formatting.Indented;
                        xml.WriteStartElement("efiscRequest");
                        xml.WriteStartElement("importRequest");

                        for (int linha = 0; linha < qtdRegistros; linha++)
                        {
                            xml.WriteStartElement(nomeTabela.ToLower());

                            string SQLColumn = "";

                            if (this.tipoBanco == "SQL Server")
                            {
                                SQLColumn = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + nomeTabela + "'";
                            }
                            else if (this.tipoBanco == "Firebird")
                            {
                                SQLColumn = "SELECT DISTINCT TRIM(RDB$RELATION_FIELDS.RDB$FIELD_NAME) FROM RDB$RELATION_FIELDS, RDB$FIELDS WHERE ( RDB$RELATION_FIELDS.RDB$FIELD_SOURCE = RDB$FIELDS.RDB$FIELD_NAME ) AND RDB$RELATION_FIELDS.RDB$RELATION_NAME = '" + nomeTabela + "' AND ( RDB$FIELDS.RDB$SYSTEM_FLAG <> 1 )";
                            }

                            DataTable colunasNomes = new DataTable();
                            colunasNomes = this.objCon.getResult(SQLColumn);

                            int qtdColunas = colunasNomes.Rows.Count;

                            for (int coluna = 0; coluna < qtdColunas; coluna++)
                            {
                                string valor = tableQtdRegistros.Rows[linha][coluna].ToString();

                                string elemento = colunasNomes.Rows[coluna][0].ToString().ToLower();

                                if (valor != "" || valor == null)
                                {
                                    xml.WriteElementString(elemento, valor);
                                }
                            }

                            xml.WriteEndElement();
                            colunasNomes.Clear();
                        }
                        tableQtdRegistros.Clear();
                        tableQtdRegistros.Reset();
                        xml.WriteEndElement();
                        xml.WriteEndElement();
                        xml.Flush();
                        xml.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Erro ao gerar xml!" + ex.ToString());
            }
            finally
            {
               this.objCon.close();
            }
        }
Beispiel #27
0
        private void ImportData()
        {
            type = Int32.Parse(textBox1.Text);

            _flag_add_fist_to_data = 0;

            string filePath = openFileDialog1.FileName;
            string filename = openFileDialog1.SafeFileName;
            string extension = Path.GetExtension(filePath);
            string conStr, sheetName;

            string connectionString = "data source=localhost; initial catalog=dmsreport; persist security info=True; Integrated Security=SSPI;";

            conStr = string.Empty;
            switch (extension)
            {
                case ".xls": //Excel 97-03
                    conStr = string.Format(Excel03ConString, filePath, "YES");
                    break;

                case ".xlsx": //Excel 07
                    conStr = string.Format(Excel07ConString, filePath, "YES");
                    break;
            }
            
            //Read Data 
            DataTable dt = new DataTable();
            using (OleDbConnection con = new OleDbConnection(conStr))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    using (OleDbDataAdapter oda = new OleDbDataAdapter())
                    {
                        con.Open();
                        DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                        for (int i_sheet = 0; i_sheet < dtExcelSchema.Rows.Count; i_sheet++)
                        {
                            sheetName = dtExcelSchema.Rows[i_sheet].Field<string>("TABLE_NAME");
                            cmd.CommandText = "SELECT * From [" + sheetName + "]";
                            cmd.Connection = con;
                            oda.SelectCommand = cmd;
                            oda.Fill(dt);
                            checkType();
                            switch (type)
                            {
                                case 11: //SAN_LUONG_DOANH_SO_BAN_HANG_THEO_NVBH_KH_SP
                                    using (SqlConnection connection = new SqlConnection(connectionString))
                                    {
                                        DataColumn Col = dt.Columns.Add("", typeof(System.String));
                                        Col.SetOrdinal(6);

                                        foreach (DataRow row in dt.Rows)
                                        {
                                            int num;

                                            bool isNum = Int32.TryParse(row[7].ToString(), out num);

                                            if (!isNum)
                                            {
                                                row[6] = row[7];
                                                row[7] = "";
                                            }
                                        }

                                        for (int i = 4; i < dt.Rows.Count; i++)
                                        {
                                            DataRow row = dt.Rows[i];
                                            DataRow prevRow = dt.Rows[i - 1];
                                            string tprevRow = prevRow[6].ToString();
                                            if (row[6].ToString() == "")
                                            {
                                                row[6] = tprevRow;
                                            }
                                        }
                                        for (int i = 3; i < dt.Rows.Count; i++)
                                        {
                                            DataRow row = dt.Rows[i];
                                            if (row[dt.Columns.Count - 1].ToString() == "")
                                            {
                                                row.Delete();
                                                dt.AcceptChanges();
                                            }
                                        }
                                        connection.Open();
                                        try
                                        {
                                            string month_report = filename.Substring(4, 2);
                                            string year_report = filename.Substring(0, 4);

                                            SqlCommand cmdCheck = new SqlCommand("DELETE FROM [dbo].[SAN_LUONG_DOANH_SO_BAN_HANG_THEO_NVBH_KH_SP] WHERE month_report = " + month_report + " AND year_report = " + year_report);
                                            cmdCheck.Connection = connection;
                                            cmdCheck.ExecuteNonQuery();

                                            for (int i = 3; i < dt.Rows.Count; i++)
                                            {
                                                if (i == dt.Rows.Count)
                                                {
                                                    break;
                                                }
                                                SqlCommand cmdSql = new SqlCommand("INSERT INTO [dbo].[SAN_LUONG_DOANH_SO_BAN_HANG_THEO_NVBH_KH_SP] (code_sup, sup, code_saler, saler, code_customer, customer,  address, code_product, product, label, type_goods, type_goods_child, encapsulation, taste, num, price, total, date_add, month_report, year_report) VALUES ('" + dt.Rows[i][0] + "', N'" + dt.Rows[i][1] + "', '" + dt.Rows[i][2] + "', N'" + dt.Rows[i][3] + "', '" + dt.Rows[i][4] + "', N'" + dt.Rows[i][5] + "', N'" + dt.Rows[i][6] + "', '" + dt.Rows[i][7] + "', N'" + dt.Rows[i][8] + "', '" + dt.Rows[i][9] + "', '" + dt.Rows[i][10] + "', '" + dt.Rows[i][11] + "', '" + dt.Rows[i][12] + "', '" + dt.Rows[i][13] + "', '" + dt.Rows[i][14] + "', '" + dt.Rows[i][15] + "', '" + dt.Rows[i][16] + "', '" + DateTime.Now + "', '" + filename.Substring(4, 2) + "', '" + filename.Substring(0, 4) + "')");
                                                cmdSql.Connection = connection;
                                                cmdSql.ExecuteNonQuery();
                                            }
                                        }
                                        catch (Exception ex)
                                        {
                                            Console.WriteLine(ex.Message);
                                        }
                                        finally
                                        {
                                            connection.Close();
                                        }

                                    }
                                    break;

                                case 12:
                                    using (SqlConnection connection = new SqlConnection(connectionString))
                                    {
                                        for (int i = 3; i < dt.Rows.Count; i++)
                                        {
                                            DataRow row = dt.Rows[i];
                                            if (row[7].ToString() == "")
                                            {
                                                row.Delete();
                                                dt.AcceptChanges();
                                            }
                                        }

                                        for (int i = dt.Rows.Count - 1; i > 3; i--)
                                        {
                                            DataRow row = dt.Rows[i];
                                            if (row[3].ToString() == "")
                                            {
                                                row.Delete();
                                                dt.AcceptChanges();
                                            }
                                        }

                                        string strTemp = dt.Rows[2][15].ToString();

                                        for (int i = 3; i < dt.Rows.Count; i++)
                                        {
                                            DataRow row = dt.Rows[i];
                                            row[15] = strTemp;                                            
                                        }

                                        connection.Open();
                                        try
                                        {
                                            string month_report = filename.Substring(4, 2);
                                            string year_report = filename.Substring(0, 4);

                                            SqlCommand cmdCheck = new SqlCommand("DELETE FROM [dbo].[DOANH_SO_SAN_LUONG_NVBH] WHERE month_report = " + month_report + " AND year_report = " + year_report);
                                            cmdCheck.Connection = connection;
                                            cmdCheck.ExecuteNonQuery();

                                            for (int i = 3; i < dt.Rows.Count; i++)
                                            {
                                                if (i == dt.Rows.Count)
                                                {
                                                    break;
                                                }
                                                SqlCommand cmdSql = new SqlCommand("INSERT INTO [dbo].[DOANH_SO_SAN_LUONG_NVBH] (code_sup, sup, code_saler, saler, code_place, place, code_product, product, convert_to_box, num, money_sale, total_weight, net_weight, volume, supplier, date_add, month_report, year_report) VALUES ('" + dt.Rows[i][1] + "', N'" + dt.Rows[i][2] + "', '" + dt.Rows[i][3] + "', N'" + dt.Rows[i][4] + "', '" + dt.Rows[i][5] + "', N'" + dt.Rows[i][6] + "', '" + dt.Rows[i][7] + "', N'" + dt.Rows[i][8] + "', '" + dt.Rows[i][9] + "', '" + dt.Rows[i][10] + "', '" + dt.Rows[i][11] + "', '" + dt.Rows[i][12] + "', '" + dt.Rows[i][13] + "', '" + dt.Rows[i][14] + "', N'" + dt.Rows[i][15] + "', '" + DateTime.Now + "', '" + filename.Substring(4, 2) + "', '" + filename.Substring(0, 4) + "')");
                                                cmdSql.Connection = connection;
                                                cmdSql.ExecuteNonQuery();
                                            }
                                        }
                                        catch (Exception ex)
                                        {
                                            Console.WriteLine(ex.Message);
                                        }
                                        finally
                                        {
                                            connection.Close();
                                        }

                                    }
                                    break;
                                case 31:
                                    if (dt.Columns.Count == 31) // TONG HOP NXT NPP
                                    {
                                        using (SqlConnection connection = new SqlConnection(connectionString))
                                        {
                                            connection.Open();
                                            try
                                            {
                                                string month_report = filename.Substring(4, 2);
                                                string year_report = filename.Substring(0, 4);

                                                SqlCommand cmdCheck = new SqlCommand("DELETE FROM [dbo].[TONG_HOP_NXT_NPP] WHERE month_report = " + month_report + " AND year_report = " + year_report);
                                                cmdCheck.Connection = connection;
                                                cmdCheck.ExecuteNonQuery();

                                                for (int i = 6; i < dt.Rows.Count; i++)
                                                {
                                                    SqlCommand cmdSql = new SqlCommand("INSERT INTO TONG_HOP_NXT_NPP (code_stock, name_stock, code_supplier, name_supplier, date_from, date_to, code_product, name_product, type_goods, specification, unit, price, opening_stock, import_company, import_return_product_sale, import_return_product_promotion, import_configure, import_stock_vansale, import_total, export_sale, export_product_promotion, export_configure, export_vansale, export_vansale_product_sale, export_vansale_product_promotion, export_vansale_product_return, export_vansale_product_stock, export_total, closing_stock, total_money, date_add, month_report, year_report) VALUES ('" + dt.Rows[i][1] + "', N'" + dt.Rows[i][2] + "', '" + dt.Rows[i][3] + "', N'" + dt.Rows[i][4] + "', '" + dt.Rows[i][5] + "', '" + dt.Rows[i][6] + "', N'" + dt.Rows[i][7] + "', N'" + dt.Rows[i][8] + "', N'" + dt.Rows[i][9] + "', '" + dt.Rows[i][10] + "', N'" + dt.Rows[i][11] + "', '" + dt.Rows[i][12] + "', '" + dt.Rows[i][13] + "', '" + dt.Rows[i][14] + "', '" + dt.Rows[i][15] + "', '" + dt.Rows[i][16] + "', '" + dt.Rows[i][17] + "', '" + dt.Rows[i][18] + "', N'" + dt.Rows[i][19] + "', '" + dt.Rows[i][20] + "', N'" + dt.Rows[i][21] + "', '" + dt.Rows[i][22] + "', '" + dt.Rows[i][23] + "', '" + dt.Rows[i][24] + "', '" + dt.Rows[i][25] + "', '" + dt.Rows[i][26] + "', '" + dt.Rows[i][27] + "', '" + dt.Rows[i][28] + "', '" + dt.Rows[i][29] + "', '" + dt.Rows[i][30] + "', '" + DateTime.Now + "', '" + filename.Substring(4, 2) + "', '" + filename.Substring(0, 4) + "')");
                                                    cmdSql.Connection = connection;
                                                    cmdSql.ExecuteNonQuery();
                                                }
                                            }
                                            catch (Exception ex)
                                            {
                                                Console.WriteLine(ex.Message);
                                            }
                                            finally
                                            {
                                                connection.Close();
                                            }

                                        }
                                    }
                                    else if (dt.Columns.Count == 14 ) // KHO CONG TY
                                    {
                                        using (SqlConnection connection = new SqlConnection(connectionString))
                                        {
                                            connection.Open();
                                            try
                                            {
                                                string month_report = filename.Substring(4, 2);
                                                string year_report = filename.Substring(0, 4);

                                                SqlCommand cmdCheck = new SqlCommand("DELETE FROM [dbo].[KHO_CONG_TY] WHERE month_report = " + month_report + " AND year_report = " + year_report);
                                                cmdCheck.Connection = connection;
                                                cmdCheck.ExecuteNonQuery();

                                                for (int i = 4; i < dt.Rows.Count; i++)
                                                {
                                                    if (i == dt.Rows.Count - 1)
                                                    {
                                                        break;
                                                    }
                                                    SqlCommand cmdSql = new SqlCommand("INSERT INTO KHO_CONG_TY (code_product, name_product, type_goods, specification, unit, price, opening_stock, import_company, import_total, export_company, export_total, closing_stock, total_money, date_add, month_report, year_report) VALUES ('" + dt.Rows[i][1] + "', N'" + dt.Rows[i][2] + "', N'" + dt.Rows[i][3] + "', '" + dt.Rows[i][4] + "', N'" + dt.Rows[i][5] + "', '" + dt.Rows[i][6] + "', '" + dt.Rows[i][7] + "', '" + dt.Rows[i][8] + "', '" + dt.Rows[i][9] + "', '" + dt.Rows[i][10] + "', '" + dt.Rows[i][11] + "', '" + dt.Rows[i][12] + "', '" + dt.Rows[i][13] + "', '" + DateTime.Now + "', '" + filename.Substring(4, 2) + "', '" + filename.Substring(0, 4) + "')");
                                                    cmdSql.Connection = connection;
                                                    cmdSql.ExecuteNonQuery();
                                                }
                                            }
                                            catch (Exception ex)
                                            {
                                                Console.WriteLine(ex.Message);
                                            }
                                            finally
                                            {
                                                connection.Close();
                                            }

                                        }
                                    }
                                    else if (dt.Columns.Count == 25) // TONG HOP NPP & TUNG NHA PP 
                                    {
                                        if (sheetName.Contains("TỔNG HỢP"))
                                        {
                                            using (SqlConnection connection = new SqlConnection(connectionString))
                                            {
                                                connection.Open();
                                                try
                                                {
                                                    string month_report = filename.Substring(4, 2);
                                                    string year_report = filename.Substring(0, 4);

                                                    SqlCommand cmdCheck = new SqlCommand("DELETE FROM [dbo].[TONG_HOP_NPP] WHERE month_report = " + month_report + " AND year_report = " + year_report);
                                                    cmdCheck.Connection = connection;
                                                    cmdCheck.ExecuteNonQuery();

                                                    for (int i = 6; i < dt.Rows.Count - 1; i++)
                                                    {
                                                        if (i == dt.Rows.Count - 1)
                                                        {
                                                            break;
                                                        }
                                                        SqlCommand cmdSql = new SqlCommand("INSERT INTO TONG_HOP_NPP (code_product, name_product, type_goods, specification, unit, price, opening_stock, import_company, import_return_product_sale, import_return_product_promotion, import_configure, import_stock_vansale, import_total, export_sale, export_product_promotion, export_configure, export_vansale, export_vansale_product_sale, export_vansale_product_promotion, export_vansale_product_return, export_vansale_product_stock, export_total, closing_stock, total_money, date_add, month_report, year_report) VALUES ('" + dt.Rows[i][1] + "', N'" + dt.Rows[i][2] + "', N'" + dt.Rows[i][3] + "', N'" + dt.Rows[i][4] + "', '" + dt.Rows[i][5] + "', '" + dt.Rows[i][6] + "', '" + dt.Rows[i][7] + "', '" + dt.Rows[i][8] + "', '" + dt.Rows[i][9] + "', '" + dt.Rows[i][10] + "', '" + dt.Rows[i][11] + "', '" + dt.Rows[i][12] + "', '" + dt.Rows[i][13] + "', '" + dt.Rows[i][14] + "', '" + dt.Rows[i][15] + "', '" + dt.Rows[i][16] + "', '" + dt.Rows[i][17] + "', '" + dt.Rows[i][18] + "', '" + dt.Rows[i][19] + "', '" + dt.Rows[i][20] + "', '" + dt.Rows[i][21] + "', '" + dt.Rows[i][22] + "', '" + dt.Rows[i][23] + "', '" + dt.Rows[i][24] + "', '" + DateTime.Now + "', '" + filename.Substring(4, 2) + "', '" + filename.Substring(0, 4) + "')");
                                                        cmdSql.Connection = connection;
                                                        cmdSql.ExecuteNonQuery();
                                                    }
                                                }
                                                catch (Exception ex)
                                                {
                                                    Console.WriteLine(ex.Message);
                                                }
                                                finally
                                                {
                                                    connection.Close();
                                                }

                                            }
                                        }
                                        else
                                        {
                                            for (int i = 6; i < dt.Rows.Count; i++ )
                                            {
                                                dt.Rows[i][0] = sheetName;
                                            }

                                            using (SqlConnection connection = new SqlConnection(connectionString))
                                            {
                                                connection.Open();
                                                try
                                                {
                                                    if (_flag_add_fist_to_data == 0)
                                                    {
                                                        string month_report = filename.Substring(4, 2);
                                                        string year_report = filename.Substring(0, 4);

                                                        SqlCommand cmdCheck = new SqlCommand("DELETE FROM [dbo].[TONG_HOP_TUNG_NPP] WHERE month_report = " + month_report + " AND year_report = " + year_report);
                                                        cmdCheck.Connection = connection;
                                                        cmdCheck.ExecuteNonQuery();
                                                        _flag_add_fist_to_data = 1;
                                                    }

                                                    for (int i = 6; i < dt.Rows.Count; i++)
                                                    {
                                                        if(i == dt.Rows.Count - 1)
                                                        {
                                                            break;
                                                        }
                                                        string _strTemp = dt.Rows[i][0].ToString();

                                                        var charsToRemove = new string[] { "$", "'" };
                                                        
                                                        foreach (var c in charsToRemove)
                                                        {
                                                            _strTemp = _strTemp.Replace(c, string.Empty);
                                                        }
                                                        SqlCommand cmdSql = new SqlCommand("INSERT INTO TONG_HOP_TUNG_NPP (name_supplier, code_product, name_product, type_goods, specification, unit, price, opening_stock, import_company, import_return_product_sale, import_return_product_promotion, import_configure, import_stock_vansale, import_total, export_sale, export_product_promotion, export_configure, export_vansale, export_vansale_product_sale, export_vansale_product_promotion, export_vansale_product_return, export_vansale_product_stock, export_total, closing_stock, total_money, date_add, month_report, year_report) VALUES ( N'" + _strTemp + "', '" + dt.Rows[i][1] + "', N'" + dt.Rows[i][2] + "', N'" + dt.Rows[i][3] + "', N'" + dt.Rows[i][4] + "', N'" + dt.Rows[i][5] + "', '" + dt.Rows[i][6] + "', '" + dt.Rows[i][7] + "', '" + dt.Rows[i][8] + "', '" + dt.Rows[i][9] + "', '" + dt.Rows[i][10] + "', '" + dt.Rows[i][11] + "', '" + dt.Rows[i][12] + "', '" + dt.Rows[i][13] + "', '" + dt.Rows[i][14] + "', '" + dt.Rows[i][15] + "', '" + dt.Rows[i][16] + "', '" + dt.Rows[i][17] + "', '" + dt.Rows[i][18] + "', '" + dt.Rows[i][19] + "', '" + dt.Rows[i][20] + "', '" + dt.Rows[i][21] + "', '" + dt.Rows[i][22] + "', '" + dt.Rows[i][23] + "', '" + dt.Rows[i][24] + "', '" + DateTime.Now + "', '" + filename.Substring(4, 2) + "', '" + filename.Substring(0, 4) + "')");
                                                        cmdSql.Connection = connection;
                                                        cmdSql.ExecuteNonQuery();
                                                    }
                                                }
                                                catch (Exception ex)
                                                {
                                                    Console.WriteLine(ex.Message);
                                                }
                                                finally
                                                {
                                                    connection.Close();
                                                }

                                            }
                                        }
    
                                    }
                                    break;
                                default: MessageBox.Show("Please choose file type");
                                    break;
                            }
                            dt.Reset();
                            con.Close();
                        }
                    }
                }
            }
            
        }
 private static void CreateUserDomainTable( DataTable objUserDomain )
 {
     objUserDomain.Reset();
     objUserDomain.Columns.Add("Username", typeof(string));
     objUserDomain.Columns.Add("Domain", typeof(string));
     objUserDomain.Columns.Add("FirstName", typeof(string));
     objUserDomain.Columns.Add("LastName", typeof(string));
 }
        private void setGridRow_grd12_v2()
        {
            string sqlquery_Q12_1_lbl = "SELECT * FROM [ISBEPI_DEV].[dbo].[Program_Director_Survey_Q12toQ16_Education_And_Experience] WHERE Schd_ID ='" + hfSchdID.Value + "'";
            DataTable dt_Q12_1_Education = DBHelper.GetDataTable(sqlquery_Q12_1_lbl);
            DataTable dt = new DataTable();
            DataRow row;
            dt.Columns.Add("Q12_1");
            dt.Columns.Add("Q12_2");
            dt.Columns.Add("Q12_3");
            dt.Columns.Add("Q13");
            dt.Columns.Add("Q14");
            dt.Columns.Add("Q15");
            dt.Columns.Add("Q16");
            dt.Columns.Add("Q17");
            dt.Columns.Add("Q18");
            
            for (int x = 0; x < dt_Q12_1_Education.Rows.Count; x++)
            {
                row = dt.NewRow();
                switch (dt_Q12_1_Education.Rows[x]["PD_EDUCATIONANDEXPERIENCE_Q12_1"].ToString().Trim())
                {
                    case "1":
                        {
                           row["Q12_1"] ="Program Director";
                        }
                        break;
                    case "2":
                        {
                            row["Q12_1"] = "Executive Director";
                        }
                        break;
                    case "3":
                        {
                            row["Q12_1"] = "Deputy Director";
                        }
                        break;
                    case "4":
                        {
                            row["Q12_1"] = "Assistant Director";
                        }
                        break;
                    case "5":
                        {
                            row["Q12_1"] = "Program Coordinator";
                        }
                        break;
                    case "6":
                        {
                            row["Q12_1"] = "Pogram Manager";
                        }
                        break;
                    case "7":
                        {
                            row["Q12_1"] = "Parent Educator Supervisor";
                        }
                        break;
                    case "8":
                        {
                            row["Q12_1"] = "Accountant";
                        }
                        break;
                    case "9":
                        {
                            row["Q12_1"] = "Other";
                        }
                        break;
                    default:
                        {
                            row["Q12_1"] = "N/A";
                            break;
                        }
                }


                switch (dt_Q12_1_Education.Rows[x]["PD_EDUCATIONANDEXPERIENCE_Q12_2"].ToString().Trim())
                {
                    case "1":
                        {
                            row["Q12_2"] = "Yes";
                            break;
                        }
                    case "2":
                        {
                            row["Q12_2"] = "No";
                            break;
                        }
                    default:
                        {
                            row["Q12_2"] = "N/A";
                            break;
                        }
                }



                switch (dt_Q12_1_Education.Rows[x]["PD_EDUCATIONANDEXPERIENCE_Q12_3"].ToString().Trim())
                {
                    case "1":
                        {
                            row["Q12_3"] = "Yes";
                            break;
                        }
                    case "2":
                        {
                            row["Q12_3"] = "No";
                            break;
                        }
                    default:
                        {
                            row["Q12_3"] = "N/A";
                            break;
                        }
                }


                switch (dt_Q12_1_Education.Rows[x]["PD_EDUCATION_Q13"].ToString().Trim())
                {
                    case "1":
                        {
                            row["Q13"] = "Yes";
                            break;
                        }
                    case "2":
                        {
                            row["Q13"] = "No";
                            break;
                        }
                    default:
                        {
                            row["Q13"] = "N/A";
                            break;
                        }
                }


                switch (dt_Q12_1_Education.Rows[x]["PD_EDUCATION_Q14"].ToString().Trim())
                {
                    case "1":
                        {
                            row["Q14"] = "Yes";
                            break;
                        }
                    case "2":
                        {
                            row["Q14"] = "No";
                            break;
                        }
                    default:
                        {
                            row["Q14"] = "N/A";
                            break;
                        }
                }


                switch (dt_Q12_1_Education.Rows[x]["PD_EDUCATION_Q15"].ToString().Trim())
                {
                    case "1":
                        {
                            row["Q15"] = "Yes";
                            break;
                        }
                    case "2":
                        {
                            row["Q15"] = "No";
                            break;
                        }
                    default:
                        {
                            row["Q15"] = "N/A";
                            break;
                        }
                }


                switch (dt_Q12_1_Education.Rows[x]["PD_EDUCATION_Q16"].ToString().Trim())
                {
                    case "1":
                        {
                            row["Q16"] = "Yes";
                            break;
                        }
                    case "2":
                        {
                            row["Q16"] = "No";
                            break;
                        }
                    default:
                        {
                            row["Q16"] = "N/A";
                            break;
                        }
                }

                switch (dt_Q12_1_Education.Rows[x]["PD_EDUCATION_Q17"].ToString().Trim())
                {
                    case "1":
                        {
                            row["Q17"] = "Less than 1 year";
                            break;
                        }
                    case "2":
                        {
                            row["Q17"] = "At least 1 year";
                            break;
                        }
                    case "3":
                        {
                            row["Q17"] = "At least 3 years";
                            break;
                        }
                    case "4":
                        {
                            row["Q17"] = "At least 5 years";
                            break;
                        }
                    case "5":
                        {
                            row["Q17"] = "More than 7 years";
                            break;
                        }
                    default:
                        {
                            row["Q17"] = "N/A";
                            break;
                        }
                }

                switch (dt_Q12_1_Education.Rows[x]["PD_EDUCATION_Q18"].ToString().Trim())
                {
                    case "1":
                        {
                            row["Q18"] = "Less than 1 year";
                            break;
                        }
                    case "2":
                        {
                            row["Q18"] = "At least 1 year";
                            break;
                        }
                    case "3":
                        {
                            row["Q18"] = "At least 3 years";
                            break;
                        }
                    case "4":
                        {
                            row["Q18"] = "At least 5 years";
                            break;
                        }
                    default:
                        {
                            row["Q18"] = "N/A";
                            break;
                        }
                }
                dt.Rows.Add(row);
            }
           
            DataTable dtGrid = new DataTable();
            dtGrid = dt;            
            grdQ12_V2.DataSource = dtGrid;
            grdQ12_V2.DataBind();
            dt.Reset();
        }
        public List<Dimension> GetDimensionsOf(string tablename)
        {
            // the tempTable is used to store the Database-results
            DataTable tempTable = new DataTable();
            DataTable tempTable2 = new DataTable();
            // this list will be filled and returned
            List<Dimension> dimensions = new List<Dimension>();

            try
            {
                Open();
                // see http://swp.offis.uni-oldenburg.de:8082/display/MPMDoku/Metadaten-Repository for sql-explanation
                MySqlCommand getMetaDataSQL = new MySqlCommand(
                    "SELECT constraint_name as FromConstraint, table_name as FromTable, column_name as FromColumn, constraint_name as ToConstraint, " +
                    "referenced_table_name as ToTable, referenced_column_name as ToColumn " +
                    "FROM information_schema.key_column_usage " +
                    "WHERE constraint_schema = '" + DBWorker.getParams().Database + "' AND constraint_name != 'PRIMARY' AND table_name = '" + tablename + "' AND referenced_table_name != 'CASE';", Connection);

                MySqlDataAdapter metaDataAdapter = new MySqlDataAdapter(getMetaDataSQL);
                // Fill the temporally used Table
                metaDataAdapter.Fill(tempTable);

                // every Database-row is a dimension
                foreach (DataRow row in tempTable.Rows)
                {
                    // create a dimension-object (see MetaWorker.Dimension)
                    Dimension d = new Dimension(row["FromColumn"].ToString(), row["FromConstraint"].ToString(),
                        row["FromTable"].ToString(), row["FromColumn"].ToString(),
                        row["ToConstraint"].ToString(), row["ToTable"].ToString(), row["ToColumn"].ToString());

                    // Now get the Data than can be filtered later:
                    MySqlCommand getContentSQL = new MySqlCommand("SELECT * FROM `" + row["ToTable"].ToString() + "` LIMIT 100", Connection);

                    /*
                     * This SQL-Command seems pretty unsafe, why so? Well, Oracle lets you use parameters for the WHERE-part of the query,
                     * however you can't do the same thing for the FROM-part. God knows why.
                     */

                    MySqlDataAdapter contentAdapter = new MySqlDataAdapter(getContentSQL);
                    // Fill the temporally used Table
                    contentAdapter.Fill(tempTable2);
                    // create DimensionContent-Objects and add them to the current dimension
                    foreach (DataRow row2 in tempTable2.Rows)
                    {
                        string desc = "";
                        if (row2.ItemArray.Count() > 2)
                            desc = row2[2].ToString();
                        d.DimensionContentsList.Add(new DimensionContent(row2[0].ToString(), row2[1].ToString(), desc));
                    }

                    // save the DimensionColumnNames for generated DB-querys
                    d.DimensionColumnNames = new DimensionColumnNames(tempTable2.Columns[0].ColumnName,
                        tempTable2.Columns[1].ColumnName, tempTable2.Columns[2].ColumnName);

                    tempTable2.Reset();
                    // now recursively find all sub-ListOfDimensions of this Table and add them to the current dimension
                    d.AddDimensionLevel(GetDimensionsOf(row["ToTable"].ToString()));

                    // add the current dimension to the list that will be returned eventually
                    dimensions.Add(d);
                }

                return dimensions;

            }
            finally
            {
                Close();
            }
        }
Beispiel #31
0
        private void btnStartRun_Click(object sender, EventArgs e)
        {
            btnStartRun.Text = "Scan Started...";
            this.Update();
            // Adjust Chart area to scan area
            chartSpectrum.ChartAreas[0].AxisX.ScaleView.Zoomable = true;
            chartSpectrum.ChartAreas[0].AxisX.Minimum            = Convert.ToInt32(numericUpDownMonoScanStartWL.Value);
            chartSpectrum.ChartAreas[0].AxisX.Maximum            = Convert.ToInt32(numericUpDownMonoScanEndWL.Value);
            chartSpectrum.ChartAreas[0].AxisY.Minimum            = -100;

            // reset variables to remove data from previous rounds
            saturationevents = 0;                                                                       // reset saturationevents from previous runs
            SpectrumTable.Reset();                                                                      // Clear datatable from data of previous runs
            chartSpectrum.Series.Clear();                                                               // Clear chart from data of previous runs

            // read variables from interface
            integrationTime = Convert.ToInt32(numericUpDownSpectrometerIntegrationTime.Value * 1000);    // read integration time from input field
            wrapper.setIntegrationTime(selectedSpectrometer, integrationTime);                           // apply integration time
            lblStatus.Text = wrapper.getFirmwareModel(selectedSpectrometer) + " Spectrometer selected."; // display selected spectrometer

            wrapper.setCorrectForElectricalDark(selectedSpectrometer, Convert.ToInt32(checkBoxElectricDarkCorrection.Checked));
            wrapper.setCorrectForDetectorNonlinearity(selectedSpectrometer, Convert.ToInt32(checkBoxNonLinearityCorrection.Checked));
            wrapper.setScansToAverage(selectedSpectrometer, Convert.ToInt32(numericUpDownSpectrometerScansToAverage.Value));
            ///lblStatus.Text = Convert.ToString(Convert.ToInt32(checkBoxNonLinearityCorrection.Checked));


            // get spectrum from spectrometer
            wavelengthArray = (double[])wrapper.getWavelengths(selectedSpectrometer);                    // get wavelenthts from spectrometer
            spectrumArray   = (double[])wrapper.getSpectrum(selectedSpectrometer);                       // get spectrum from spectrometer
            //numberOfPixels = spectrumArray.GetLength(0);                                                 // get spectrum length

            // fill Datatable start
            SpectrumTable.Columns.Add("Wavelength");                                                     // add first column for wavelengths

            // insert first row for integration time data START
            row = SpectrumTable.NewRow();
            row["Wavelength"] = "integrationTime in ms";
            SpectrumTable.Rows.Add(row);
            // insert first row for integration time data END

            // add wavelength values in first column START
            for (int i = 0; i < spectrumArray.GetLength(0); i++)
            {
                row = SpectrumTable.NewRow();
                row["Wavelength"] = wavelengthArray[i];
                //row["Wavelength"] = Math.Round(wavelengthArray[i], 2);
                SpectrumTable.Rows.Add(row);
            }
            // add wavelength values in first column END

            // add spectral data columns to datatable START
            for (decimal k = numericUpDownMonoScanStartWL.Value; k <= numericUpDownMonoScanEndWL.Value; k = k + numericUpDownMonoScanInterval.Value)
            {
                myMonoScan.setPositionNM(Convert.ToInt32(k));


                System.Threading.Thread.Sleep(millisecondsTimeout: integrationTime / 1000);


                // while(myMonoScan.positionReached == 0)
                // { System.Threading.Thread.Sleep(millisecondsTimeout: 5); }

                spectrumArray = (double[])wrapper.getSpectrum(selectedSpectrometer);

                int specmax       = wrapper.getMaximumIntensity(selectedSpectrometer);
                int specpercentil = Convert.ToInt32(specmax - (specmax * 0.05));
                if (spectrumArray.Max() > specpercentil)
                {
                    saturationevents = saturationevents + 1;
                }
                lblStatus.Text = saturationevents.ToString() + " Saturation events occured during scan.";

                SpectrumTable.Columns.Add(k.ToString());
                SpectrumTable.Rows[0][k.ToString()] = numericUpDownSpectrometerIntegrationTime.Value;
                SpectrumTable.AcceptChanges();

                for (int i = 0; i < spectrumArray.GetLength(0); i++)
                {
                    SpectrumTable.Rows[i + 1][k.ToString()] = spectrumArray[i];
                    SpectrumTable.AcceptChanges();
                }

                // add spectral data into series for chart START
                chartSpectrum.Series.Add(k.ToString());
                chartSpectrum.Series[k.ToString()].ChartType = SeriesChartType.Line;
                for (int l = 1; l < spectrumArray.GetLength(0); l++)
                {
                    chartSpectrum.Series[k.ToString()].Points.AddXY(Math.Round(wavelengthArray[l], 0), spectrumArray[l]);
                }
                this.Update();                                                  // Update Interface to dispaly chart
                // add spectral data into series for chart END
            }
            // add spectral data columns to datatable END

            // give warning when saturations occured during run
            if (saturationevents > 0)
            {
                MessageBox.Show("There have been " + saturationevents.ToString() + " scans that were saturated!");
            }


            //Write Datatable to csv file with comma separator
            if (saveFileDialogMonoSeq.ShowDialog() == System.Windows.Forms.DialogResult.OK) // write Datatable to file when dialog was accepted
            {
                StringBuilder        spectrumString = new StringBuilder();
                IEnumerable <string> columnNames    = SpectrumTable.Columns.Cast <DataColumn>().Select(column => column.ColumnName);
                spectrumString.AppendLine(string.Join(",", columnNames));
                foreach (DataRow row in SpectrumTable.Rows)
                {
                    IEnumerable <string> fields = row.ItemArray.Select(field => field.ToString());
                    spectrumString.AppendLine(string.Join(",", fields));
                }
                File.WriteAllText(saveFileDialogMonoSeq.FileName, spectrumString.ToString());
                // Write Datatable end

                // Write Metadata to txt file, same name as csv but  with *.ini extension
                StringBuilder metadataString = new StringBuilder();
                metadataString.AppendLine("Timestamp, " + DateTime.Now.ToString());
                metadataString.AppendLine("Spectrometer, " + wrapper.getFirmwareModel(selectedSpectrometer));
                metadataString.AppendLine("Scans to Average, " + wrapper.getScansToAverage(selectedSpectrometer).ToString());
                metadataString.AppendLine("Boxcar, " + wrapper.getBoxcarWidth(selectedSpectrometer).ToString());
                metadataString.AppendLine("EDC, " + wrapper.getCorrectForElectricalDark(selectedSpectrometer).ToString());
                metadataString.AppendLine("NLC, " + wrapper.getCorrectForDetectorNonlinearity(selectedSpectrometer).ToString());
                metadataString.AppendLine("Itegration Time (as milliseconds), " + (wrapper.getIntegrationTime(selectedSpectrometer) / 1000).ToString());
                metadataString.AppendLine("Saturationevents, " + saturationevents.ToString());
                metadataString.AppendLine("MonoScan Start, " + numericUpDownMonoScanStartWL.Value.ToString());
                metadataString.AppendLine("MonoScan END, " + numericUpDownMonoScanEndWL.Value.ToString());
                metadataString.AppendLine("MonoScan Interval, " + numericUpDownMonoScanInterval.Value.ToString());
                File.WriteAllText(Path.ChangeExtension(saveFileDialogMonoSeq.FileName, ".txt"), metadataString.ToString());
                // Write Metadata End
            }
            btnStartRun.Text = "Start";
            this.Update();
        }                                                                       // main routine in in here
        // "Mã Khách Hàng","Tên Khách Hàng","Số CMND","Loại Khách Hàng","Địa Chỉ","Nhiều điều kiện"};
        private void btSearch_Click(object sender, EventArgs e)
        {
            switch (id_Search)
            {
                case 0:
                    {
                        kDTO.MaKhachHang = txt_MaKH.Text;
                        dt.Reset();
                        if (chk_searchKHDD.Checked == false)
                            dt = ConvertKhachHangDTOArrayToDataTable(new QLKS_BUS_WebserviceSoapClient().getKhachHangById(kDTO.MaKhachHang));
                        else
                            dt =ConvertKhachHangDTOArrayToDataTable(new QLKS_BUS_WebserviceSoapClient().getListKhachHangDaiDienById(kDTO.MaKhachHang));
                        //MessageBox.Show(kBUS.getstrSQL());
                        break;
                    }
                case 1:
                    {
                        //kDTO.TenKhachHang = ConvertUniCodeToANSI(txt_TenKH.Text);
                        //dt.Reset();
                        //dt = kBUS.getListKhachHangByName("L");
                        //MessageBox.Show(kBUS.getstrSQL());
                        break;
                    }
                case 2:
                    {
                        kDTO.CMND = txt_CMND.Text;
                        dt.Reset();
                        dt = ConvertKhachHangDTOArrayToDataTable(new QLKS_BUS_WebserviceSoapClient().getKhachHangByCMND(kDTO.CMND));
                        break;

                    }
                case 3:
                    {
                        kDTO.MaLoaiKH = cmb_MaLoaiKH.SelectedValue.ToString();
                        dt.Reset();
                        dt = ConvertKhachHangDTOArrayToDataTable(new QLKS_BUS_WebserviceSoapClient().getKhachHangByKind(kDTO.MaLoaiKH));
                        break;

                    }
                case 4:
                    {
                        kDTO.DiaChi = txt_DiaChi.Text;
                        dt.Reset();
                        dt = ConvertKhachHangDTOArrayToDataTable(new QLKS_BUS_WebserviceSoapClient().getKhachHangByAddress(kDTO.DiaChi));
                        break;

                    }
                case 5:
                    {
                        kDTO.TenKhachHang = txt_TenKH.Text;
                        kDTO.MaLoaiKH = cmb_MaLoaiKH.Text;
                        kDTO.DiaChi = txt_DiaChi.Text;
                        dt.Reset();
                        dt = ConvertKhachHangDTOArrayToDataTable(new QLKS_BUS_WebserviceSoapClient().getKhachHangByMulti(kDTO.TenKhachHang, kDTO.MaLoaiKH, kDTO.DiaChi));
                        break;
                    }
            }// end switch
            // Gôm chung lại thay vì mỗi case phải thực hiện 2 lệnh này
             //MessageBox.Show(kDTO.MaLoaiKH);
             bdSource.DataSource = dt;
             dataGridKH.DataSource = bdSource;
        }
Beispiel #33
0
		public void ClearReset () //To test Clear and Reset methods
		{
			DataTable table = new DataTable ("table");
			DataTable table1 = new DataTable ("table1");

			DataSet set = new DataSet ();
			set.Tables.Add (table);
			set.Tables.Add (table1);

			table.Columns.Add ("Id", typeof (int));
			table.Columns.Add ("Name", typeof (string));
			table.Constraints.Add (new UniqueConstraint ("UK1", table.Columns [0]));
			table.CaseSensitive = false;

			table1.Columns.Add ("Id", typeof (int));
			table1.Columns.Add ("Name", typeof (string));

			DataRelation dr = new DataRelation ("DR", table.Columns[0], table1.Columns[0]);
			set.Relations.Add (dr);

			DataRow row = table.NewRow ();
			row ["Id"] = 147;
			row ["name"] = "Roopa";
			table.Rows.Add (row);

			row = table.NewRow ();
			row ["Id"] = 47;
			row ["Name"] = "roopa";
			table.Rows.Add (row);

			Assert.AreEqual (2, table.Rows.Count);
			Assert.AreEqual (1, table.ChildRelations.Count);
			try {
				table.Reset ();
				Assert.Fail ("#A01, should have thrown ArgumentException");
			} catch (ArgumentException) {
			}

			Assert.AreEqual (0, table.Rows.Count, "#CT01");
			Assert.AreEqual (0, table.ChildRelations.Count, "#CT02");
			Assert.AreEqual (0, table.ParentRelations.Count, "#CT03");
			Assert.AreEqual (0, table.Constraints.Count, "#CT04");

			table1.Reset ();
			Assert.AreEqual (0, table1.Rows.Count, "#A05");
			Assert.AreEqual (0, table1.Constraints.Count, "#A06");
			Assert.AreEqual (0, table1.ParentRelations.Count, "#A07");
		
			// clear test
			table.Clear ();
			Assert.AreEqual (0, table.Rows.Count, "#A08");
			Assert.AreEqual (0, table.Constraints.Count, "#A09");
			Assert.AreEqual (0, table.ChildRelations.Count, "#A10");
		}
        /// <summary>
        /// UpdateAfterOpenDB - Updates the controls and other important variables after a database is opened.
        /// </summary>
        private void UpdateAfterOpenDB()
        {
            DataTable dt = new DataTable();
            Mydb.GetCurrentInventory();  //Load the current inventory into the datatable used by View and Search
            dt_V_DispensedTable.Clear();
            Mydb.GetTable(dt_V_DispensedTable, SSTable.Dispensed); //Load the dispensed inventory into the datatable used by View
            Mydb.GetDBInfo(dt); //Load the database info

            GuiPrefs.OpenDBPath = Mydb.MyPath;
            if(dt.Rows.Count > 0)
            {
                //Set the database info to corresponding member variables
                GuiPrefs.OpenDBName = dt.Rows[0][0].ToString();
                GuiPrefs.OpenDBLoc = dt.Rows[0][1].ToString();

                string codb = String.Format("Database: {0} ({1})", GuiPrefs.OpenDBName, GuiPrefs.OpenDBLoc);
                lb_Add_CurrentOpenDB.Text = codb;
                lb_S_CurrentOpenDB.Text = codb;
                lb_D_CurrentOpenDB.Text = codb;
                lb_V_CurrentOpenDB.Text = codb;
                lb_R_CurrentOpenDB.Text = codb;
            }

            //Check for merge info and alter display if necessary
            dt.Reset();
            dt_Add_MergeTable.Clear();
            try {
                Mydb.GetTable(dt, SSTable.MergeInfo);
                tb_Add_MinSKU.Text = dt.Rows[0][1].ToString();
                tb_Add_MaxSKU.Text = dt.Rows[0][2].ToString();
                Mydb.GetTable(dt_Add_MergeTable, SSTable.MergeItems);
                bs_Add_InventorySource.DataSource = dt_Add_MergeTable;
                Enable_AllControls(false);
            } catch { //If there's an exception, it's not a merge db
                bs_Add_InventorySource.DataSource = Mydb.InvResults;
                tb_Add_MinSKU.Clear();
                tb_Add_MaxSKU.Clear();
                Enable_AllControls(true);
            }

            Enable_MenuItems(true);
            ResetOps(); //Reset number of operations performed since last auto backup
            ConfigureAutoBackupTimer(); //Configures (and resets) the timer for auto backups
        }